ORACLE GL Technical

MOAC

API List
Oracle applications - Key Flex Field Structures & Table Details.

GL_INTERFACE/GL IMPORT

Oracle General Ledger Journal Import Process Overview
GL INTERFACE
The GL_INTERFACE Table
 Required NULL Columns in the GL_INTERFACE Table
GL:Using custom GL_INTERFACE tables for Journal Import
How to run GL Journal Import program through PL SQL?
Complete GL Interface – PL/SQL Procedure
Oracle API for GL Interface Package Header
Oracle API for GL Interface Package Body

GL_INTERFACE Error Description

GL Interface Error Code
GL INTERFACE ERROR CODE TABLE

SELECT
aa.status error_code,
bb.meaning ERROR,
bb.DESCRIPTION Error_Desc
FROM
apps.gl_interface aa,
apps.fnd_lookups bb
WHERE aa.status=bb.lookup_code
AND accounting_date BETWEEN '01-DEC-17' AND '31-DEC-17'
AND bb.LOOKUP_TYPE='PSP_SUSP_AC_ERRORS'

SELECT meaning,Lookup_code FROM  fnd_lookups where lookup_type='PSP_SUSP_AC_ERRORS'
Where Lookup Code = GL_INTERAFCE.Status

Create Code Combination

Get Legal Entity

Tax Query

 

 GL Period Query

SELECT gl_led.ledger_id, gl_led.NAME ledger_name, hou.organization_id,
       hou.NAME org_name, gps.period_set_name, gp.period_name, gp.start_date,
       gp.end_date, gp.period_num, gp.period_type, gprs.closing_status
  FROM gl_ledgers gl_led,
       hr_operating_units hou,
       gl_period_sets gps,
       gl_periods gp,
       gl_period_statuses gprs
 WHERE 1 = 1
   AND hou.organization_id = fnd_profile.VALUE ('ORG_ID')
   AND hou.set_of_books_id = gl_led.ledger_id
   AND gl_led.period_set_name = gps.period_set_name
   AND gps.period_set_name = gp.period_set_name
   AND gp.adjustment_period_flag = 'N'
   AND gp.period_name = gprs.period_name
   AND gprs.set_of_books_id = gl_led.ledger_id
   AND gprs.set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID')
   AND gprs.application_id = 101
   AND gprs.closing_status IN ('O', 'F')

Chart of Accounts

select * From FND_ID_FLEX_STRUCTURES_VL  where id_flex_code='GL#' ;

Get the FLEX_VALUE_SET_ID

SELECT ffvs.flex_value_set_id, ffvs.flex_value_set_name, ffvs.description,
fifs.id_flex_code, fifs.id_flex_num, fifs.application_column_name,
fifs.segment_name, hou.organization_id, hou.NAME
FROM fnd_id_flex_segments fifs,
fnd_flex_value_sets ffvs,
gl_ledgers gl_led,
hr_operating_units hou
WHERE 1 = 1
AND hou.organization_id = fnd_profile.VALUE ('ORG_ID')
AND hou.set_of_books_id = gl_led.ledger_id
AND fifs.id_flex_num = gl_led.chart_of_accounts_id
AND fifs.flex_value_set_id = ffvs.flex_value_set_id
AND fifs.application_id = 101
AND fifs.id_flex_code = 'GL#'
AND fifs.application_column_name = 'SEGMENT5';

Qualifier attached to each of the segments of KFF

Background:
Provide Flexfield details to the following SQL Query.
It shows which qualifier is attached to the each of the segments of that KFF.

SQL Query:

fnd_flex_values_vl table in compiled_value_attributes column

Problem with substring (fnd_flex_values_vl table in compiled_value_attributes )

GET Natural Account
Oracle APPS Query for Natural Accoun in the Chart of account
Quick overview of the structure of chart of accounts
SELECT fsa.application_column_name, fsa.segment_attribute_type, fsa.attribute_value, ffvs.flex_value_set_id, ffvs.flex_value_set_name,
       fifs.id_flex_structure_code, fifst.description
  FROM fnd_segment_attribute_values fsa,
       fnd_id_flex_segments ifs,
       fnd_flex_value_sets ffvs,
       fnd_id_flex_structures fifs,
       fnd_id_flex_structures_tl fifst
 WHERE 1 = 1
   --
   AND fsa.attribute_value = 'Y'
   AND fsa.id_flex_code = 'GL#'
   AND fsa.application_id = 101
   AND fsa.segment_attribute_type = 'GL_ACCOUNT'
   AND fsa.id_flex_num = :p_chart_of_account_id
   --
   AND fsa.application_id = ifs.application_id
   AND fsa.id_flex_code = ifs.id_flex_code
   AND fsa.id_flex_num = ifs.id_flex_num
   AND fsa.application_column_name = ifs.application_column_name
   AND ifs.flex_value_set_id = ffvs.flex_value_set_id
   --
   AND fifs.id_flex_code = 'GL#'
   AND fifs.application_id = 101
   AND fifs.application_id = ifs.application_id
   AND fifs.id_flex_code = ifs.id_flex_code
   AND fifs.id_flex_num = ifs.id_flex_num
   --
   AND fifs.application_id = fifst.application_id
   AND fifs.id_flex_code = fifst.id_flex_code
   AND fifs.id_flex_num = fifst.id_flex_num
   AND fifst.LANGUAGE = USERENV ('LANG')

Standard Query To Get Account Description


SELECT DISTINCT fifs.id_flex_structure_code,
fsav.application_column_name,
ffsg.segment_name,
DECODE (fsav.segment_attribute_type,
'FA_COST_CTR', 'Cost Center Segment',
'GL_ACCOUNT', 'Natural Account Segment',
'GL_BALANCING', 'Balancing Segment',
'GL_INTERCOMPANY', 'Intercompany Segment',
'GL_SECONDARY_TRACKING','Secondary Tracking Segment',
'GL_MANAGEMENT', 'Management Segment',
fsav.segment_attribute_type)
FROM fnd_segment_attribute_values fsav,
fnd_id_flex_structures fifs,
fnd_id_flex_segments ffsgWHERE UPPER(fifs.id_flex_structure_code) = UPPER('&Flexfield_Code')
AND fsav.attribute_value = 'Y'
AND segment_attribute_type NOT IN ('GL_GLOBAL', 'GL_LEDGER')
AND fsav.ID_FLEX_NUM = fifs.ID_FLEX_NUM
AND ffsg.ID_FLEX_NUM = fifs.ID_FLEX_NUM
AND ffsg.application_column_name = fsav.application_column_name
ORDER BY application_column_name;

Get the Flexfield_Code using below navigation (Accounting Flexfield)
GL > Setup > Financials > Flexfields > Key > Segments


Can Any body explain me how to use the following user_exits in apps reports which have flex fields as display/query columns.
 

Calling in Report

function CF_FLEX_DESCFormula return Char is
begin
  srw.reference(:H_STRUCT_NUM);
  srw.reference(:C_ACCT_FLEX2);
  srw.user_exit('FND FLEXIDVAL CODE="GL#"
                  NUM=":H_STRUCT_NUM"
                 APPL_SHORT_NAME="SQLGL"
                 DATA=":C_ACCT_FLEX2"
                 DESCRIPTION=":CF_FLEX_DESC"
                 DISPLAY="ALL"
                 IDISPLAY="ALL"');
  RETURN(:CF_FLEX_DESC);
---RETURN NULL;
end;

Account Segments Description

Oracle: Description for GL_CODE_COMBINATIONS Segments
Description /Name of the GL Code Combination Segments
Query to get GL Account and Account Segment Description

Journal Approval

Journal Balances Query

  
SELECT cc.segment2,
nvl(sum(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),0) "Actual Balance",
nvl(sum(balb.begin_balance_dr + balb.period_net_dr - balb.begin_balance_cr - balb.period_net_cr),0) "Budget Balance"
FROM gl_balances bal, gl_balances balb, gl_code_combinations cc
WHERE cc.code_combination_id = bal.code_combination_id
AND balb.code_combination_id = cc.code_combination_id
AND bal.set_of_books_id = <'your SOB id'>
AND balb.set_of_books_id = <'your SOB id'>
AND bal.period_name = <'your actual period name'>
AND balb.period_name = <'your budget period_name'>
AND bal.actual_flag = 'A'
AND balb.actual_flag = 'B'
AND bal.currency_code = <'you SOB currency code'>
AND balb.currency_code = <'your SOB currency code'>
GROUP by cc.segment2

GL Funds check functionality (gl_funds_available_pkg.calc_funds)
-----------------------------------
(SELECT gld.ledger_id, gsv.code_combination_id, gld.currency_code,
        gp.period_set_name, gp.period_num, gp.period_name, gp.period_year,
        gp.period_type, gsv.segment3,
       
        ---------(Budget Balance)-----------------
        bein_right_bedget_rep_xml_pkg.f_get_fund_inquiry
                                     (gld.ledger_id,
                                      gsv.code_combination_id,
                                      gp.period_set_name,
                                      gp.period_year,
                                      gp.period_name,
                                      'YTDE',
                                      'B'
                                     ) budget_balance,
       
---------(Actual Balance)-----------------
        bein_right_bedget_rep_xml_pkg.f_get_fund_inquiry
                                     (gld.ledger_id,
                                      gsv.code_combination_id,
                                      gp.period_set_name,
                                      gp.period_year,
                                      gp.period_name,
                                      'YTDE',
                                      'A'
                                     ) actual_balance
   ---------
 FROM   gl_periods gp,
        gl_ledgers gld,
        hr_operating_units hou,
        gl_summary_combinations_v gsv
  WHERE 1 = 1
    AND (    gp.period_set_name = gld.period_set_name
         AND gp.adjustment_period_flag != 'Y'
         AND gp.period_year = :p_period_year
        )
    AND hou.set_of_books_id = gld.ledger_id
    AND (    gsv.chart_of_accounts_id = gld.chart_of_accounts_id
         AND gsv.account_type = 'A'
        )
    AND hou.organization_id = NVL (:p_org_id, hou.organization_id)
-------------------------------------------------------------               AND gsv.segment3 = 'B00098'
    AND (code_combination_id IN (
               SELECT code_combination_id
                 FROM gl_code_combinations
                WHERE 1 = 1
-------------------------------------------------------------              AND segment3 = 'B00098'
                      AND chart_of_accounts_id = gld.chart_of_accounts_id)
        ))
-----------------------------------

 
Standard Query To Get Account Description
Description /Name of the GL Code Combination Segments

SELECT gcc.concatenated_segments ACCOUNT,
gl_flexfields_pkg.get_concat_description
(gcc.chart_of_accounts_id,
gcc.code_combination_id
) description
FROM gl_code_combinations_kfv gcc
 

No comments:

Post a Comment

Subscribe to: Posts (Atom)