Oracle GL Main Query for Reconciliation

Step 1:

SELECT  gjh.je_source, gjh.je_category,
       sum(NVL(gjl.accounted_dr,0)) accounted_dr, sum(NVL(gjl.accounted_cr,0)) accounted_cr,
       sum(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) ) net_balance
  FROM gl_je_lines gjl,
       gl_je_headers gjh,
       gl_code_combinations gcc
 WHERE gjl.je_header_id = gjh.je_header_id
   AND gjl.period_name NOT LIKE 'ADJ%' -- Adjustment Period Excluded
   AND EXISTS (
               SELECT 1
                 FROM gl_code_combinations gc
                WHERE gc.segment1 = :P_COMPANY
                  AND gc.segment4 = :P_ACCT_CD AND gc.code_combination_id= gcc.code_combination_id)
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gjl.status = 'P'
   AND TRUNC (gjl.effective_date) <= :p_end_date
Group By  gjh.je_source, gjh.je_category 
Order By 1

Step 2:

  
SELECT  gjh.je_source, gjh.je_category, gjb.NAME AS je_batch_name, gjh.NAME AS je_name,
       gjh.period_name, --TRUNC (gjl.effective_date) AS gl_date,
--     TO_CHAR (gjh.doc_sequence_value) AS doc_number,
--     gjh.currency_code AS gl_currency_code,
--     gcc.concatenated_segments, gjl.description AS naration,
       sum(NVL(gjl.accounted_dr,0)) accounted_dr, sum(NVL(gjl.accounted_cr,0)) accounted_cr,
       sum(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) ) net_balance
  FROM gl_je_lines gjl,
       gl_je_headers gjh,
       gl_code_combinations_kfv gcc,
       gl_je_batches gjb
 WHERE gjl.je_header_id = gjh.je_header_id
   AND gjl.period_name NOT LIKE 'ADJ%' -- Adjustment Period Excluded  
   AND EXISTS (
               SELECT 1
                 FROM gl_code_combinations gc
                WHERE gc.segment1 = :P_COMPANY
                  AND gc.segment4 = :P_ACCT_CD AND    gc.code_combination_id= gcc.code_combination_id)
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjl.status = 'P'
   AND TRUNC (gjl.effective_date) <= :p_end_date
Group By  gjh.je_source, gjh.je_category,gjb.NAME, gjh.NAME,gjh.period_name 
Order By 1 

How to find CoA name and description

SELECT   ID_FLEX_NUM,
                 ID_FLEX_STRUCTURE_CODE,
                 ID_FLEX_STRUCTURE_NAME,
                 DESCRIPTION
  FROM   FND_ID_FLEX_STRUCTURES_VL
 WHERE   ID_FLEX_CODE = 'GL#' AND CREATED_BY <> 1;

How to find Natural Account Segment in a Query

SELECT   APPLICATION_COLUMN_NAME
  FROM   FND_SEGMENT_ATTRIBUTE_VALUES
 WHERE       APPLICATION_ID = 101
         AND ID_FLEX_CODE = 'GL#'
         AND SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
         AND ATTRIBUTE_VALUE = 'Y'









 

How to find Legal Entity Address in Oracle Apps

SELECT    DECODE (hla.address_line_1, NULL, NULL, hla.address_line_1)
       || DECODE (hla.address_line_2, NULL, NULL, ', ' || hla.address_line_2)
       || DECODE (hla.address_line_3, NULL, NULL, ', ' || hla.address_line_3)
       || DECODE (hla.loc_information15,
                  NULL, NULL,
                  ', ' || hla.loc_information15
                 )
       || DECODE (hla.loc_information16,
                  NULL, NULL,
                  ', ' || hla.loc_information16
                 )
       || DECODE (ftv.territory_short_name,
                  NULL, NULL,
                  ', ' || ftv.territory_short_name
                 )
       || DECODE (hla.postal_code, NULL, NULL, ' - ' || hla.postal_code)  LE_Address
  FROM xle_entity_profiles etbp,
       xle_registrations reg,
       hr_locations_all hla,
       fnd_territories_vl ftv
 WHERE etbp.legal_entity_id = reg.source_id
   AND etbp.legal_entity_id = (SELECT   DISTINCT DEFAULT_LEGAL_CONTEXT_ID
                               FROM   HR_OPERATING_UNITS
                              WHERE   NAME = 'XX_OPERATING_UNIT')
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND identifying_flag = 'Y'
   AND hla.location_id = reg.location_id
   AND ftv.territory_code = hla.country;

How to find Legal Entity name in Oracle apps

SELECT   XEP.NAME
  INTO   LEGAL_ENTITY_NAME
  FROM   XLE_ENTITY_PROFILES XEP
 WHERE   LEGAL_ENTITY_ID  =  (SELECT   DISTINCT DEFAULT_LEGAL_CONTEXT_ID
                               FROM   HR_OPERATING_UNITS
                              WHERE   NAME = 'XX_OPERATING_UNIT')

How to find GL Code Combination ID and Description

Find GL Code Combination Id & Description

1)  ---Only in 11i ----

SELECT    gcc.segment1

       || '.'

       || gcc.segment2

       || '.'

       || gcc.segment3

       || '.'

       || gcc.segment4

       || '.'

       || gcc.segment5

          ACCOUNT_CODE,

          a1.description

       || '-'

       || a2.description

       || '-'

       || a3.description

       || '-'

       || a4.description

       || '-'

       || a5.description

          ACCOUNT_DECS

  FROM fnd_flex_values_vl A1,

       fnd_flex_values_vl A2,

       fnd_flex_values_vl A3,

       fnd_flex_values_vl A4,

       fnd_flex_values_vl A5,

       gl_code_combinations gcc

 WHERE     a1.flex_value = gcc.segment1

       AND a1.flex_value_set_id IN

              (SELECT FLEX_VALUE_SET_ID

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT1')

       AND a2.flex_value = gcc.segment2

       AND a2.flex_value_set_id IN

              (SELECT flex_value_set_id

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT2')

       AND a3.flex_value = gcc.segment3

       AND a3.flex_value_set_id IN

              (SELECT flex_value_set_id

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT3')                      

       AND a4.flex_value = gcc.segment4

       AND a4.flex_value_set_id IN

              (SELECT flex_value_set_id

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT4')

       AND a5.flex_value = gcc.segment5

       AND a5.flex_value_set_id IN

              (SELECT flex_value_set_id

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT5');

2)  ---Only in R12----

SELECT gcc.concatenated_segments ACCOUNT_CODE,

       GL_FLEXFIELDS_PKG.get_concat_description (gcc.chart_of_accounts_id,

                                                 gcc.code_combination_id)

          ACCOUNT_DECS

  FROM gl_code_combinations_kfv gcc;

How to find segment and FLEX value set id of General Ledger throw scripts

SELECT
  FLEX_VALUE_SET_ID
, SEGMENT_NUM
, SEGMENT_NAME
, APPLICATION_COLUMN_NAME
FROM FND_ID_FLEX_SEGMENTS_VL
WHERE ID_FLEX_CODE = 'GL#'
AND ENABLED_FLAG='Y'
ORDER BY SEGMENT_NUM



Find the Description
---------------------------

SELECT description||' ('||flex_value||')' Description
  FROM fnd_flex_values_vl
  WHERE flex_value_set_id = 1014548 --example
  AND flex_value = :P_COMPANY;  --example segment1