Link Between AR and GL with XLA tables in R12

AR to GL Link in oracle apps 

Data Flow from AR to XLA to GL

SELECT trx.trx_number,        trx.cust_trx_type_id,        trx.bill_to_customer_id,        trx_dist.amount ar_inv_dist_amount,        trx_dist.code_combination_id ar_inv_dist_ccid,           gcc.segment1AR         || '.'        || gcc.segment2        || '.'        || gcc.segment3        || '.'        || gcc.segment4        || '.'        || gcc.segment5        || '.'        || gcc.segment6        || '.'        || gcc.segment7        || '.'        || gcc.segment8           ar_inv_dist_code_combination,        gjh.name gl_je_header_name,        gjh.description gl_je_header_desc,        gjl.je_line_num,        gjl.code_combination_id gl_je_line_ccid   FROM ar.ra_customer_trx_all trx,        ar.ra_cust_trx_line_gl_dist_all trx_dist,        gl.gl_code_combinations gcc,        gl.gl_ledgers gl,        xla.xla_transaction_entities xte,        xla.xla_ae_headers xah,        xla.xla_ae_lines xal,        xla.xla_distribution_links xdl,        gl.gl_import_references gir,        gl.gl_je_headers gjh,        gl.gl_je_lines gjl  WHERE 1 = 1    -- AND trx.org_id = :P_ORG_ID    -- AND trx.batch_source_id = :P_BATCH_SOURCE_ID    AND trx.customer_trx_id = :P_CUSTOMER_TRX_ID    AND trx.customer_trx_id = trx_dist.customer_trx_id    AND gcc.code_combination_id = trx_dist.code_combination_id    --  AND gl.short_name = :P_LEDGER_SHORT_NAME    AND gl.chart_of_accounts_id = gcc.chart_of_accounts_id    AND xte.ledger_id = gl.ledger_id    AND xte.entity_code = 'TRANSACTIONS'    AND xte.application_id = 222    AND xte.source_id_int_1 = trx.customer_trx_id    AND xah.entity_id = xte.entity_id    AND xal.ae_header_id = xah.ae_header_id    AND xal.accounting_class_code = 'REVENUE'    AND xdl.ae_header_id = xah.ae_header_id    AND xdl.ae_line_num = xal.ae_line_num    AND xdl.source_distribution_id_num_1 = trx_dist.cust_trx_line_gl_dist_id    AND xdl.application_id = 222    AND gir.gl_sl_link_id = xal.gl_sl_link_id    AND gjh.je_header_id = gir.je_header_id    AND gjl.je_header_id = gjh.je_header_id    AND gjl.je_line_num = gir.je_line_num;

Important SQL queries and tables related to Oracle GL

 

Oracle Applications

--sql to find code combinations

 SELECT code_combination_id, enabled_flag
         FROM gl_code_combinations gcc
             ,gl_sets_of_books gsb
        WHERE gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
          AND segment1 = p_segment1  --pass segment details as your acct structure
          AND segment2 = p_segment2
          AND segment3 = p_segment3
          AND segment4 = p_segment4
          AND segment5 = p_segment5
          AND segment6 = p_segment6
           AND set_of_books_id = p_set_of_book_id; --pass set of books id


--SQL to derive gl CATEGORY NAME

 SELECT *
               FROM gl_je_categories gjc
             WHERE UPPER (gjc.user_je_category_name) = UPPER (:p_cat_name);
         
         
            
--SQL to derive  gl SOURCE NAME

  SELECT *
              FROM gl_je_sources gjs
             WHERE UPPER (gjs.user_je_source_name) = UPPER (:p_source_name);

-- sql to derive gl ledger id

SELECT ledger_id
           FROM gl_ledgers
          WHERE UPPER (NAME) = UPPER (:name);


--SQL to check THAT PARTICULAR PERIOD IS OPEN OR NOT BY PASSING PARAMETER AS PERIOD NAME AND LEDGER ID

SELECT 'X'
               FROM apps.gl_period_statuses gps,
                    apps.fnd_application fa,
                    apps.gl_periods glp
              WHERE gps.period_name = glp.period_name
                AND gps.closing_status IN ('O', 'F')
                AND fa.application_short_name = 'SQLGL'
                AND gps.application_id = fa.application_id
                AND glp.period_set_name = 'JLP_GL_CALENDER' --pass period set name
                AND p_transaction_date BETWEEN glp.start_date AND glp.end_date
                AND gps.ledger_id = v_ledger_id; --set of books id

To find balances of all Assets, Liabilities & Shareholders Equity accounts 

SELECT * FROM (
SELECT
GL.GL_CODE_COMBINATIONS.SEGMENT1 LEGAL_ENTITY,
GL.GL_CODE_COMBINATIONS.SEGMENT2 ACCOUNT,
GL.GL_CODE_COMBINATIONS.SEGMENT5 ENTRY_IND,
GL.GL_BALANCES.PERIOD_NAME, GL_CODE_COMBINATIONS.ACCOUNT_TYPE ,
SUM((NVL(BEGIN_BALANCE_DR,0)-
NVL(BEGIN_BALANCE_CR,0))+
(NVL(PERIOD_NET_DR,0)-
NVL(PERIOD_NET_CR,0))) YTD_ACTUAL_AMOUNT
FROM
GL.GL_BALANCES,
GL.GL_CODE_COMBINATIONS
WHERE GL.GL_BALANCES.CODE_COMBINATION_ID=GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
AND GL.GL_CODE_COMBINATIONS.ACCOUNT_TYPE IN ('O','L','A')
AND GL.GL_BALANCES.SET_OF_BOOKS_ID = 1
AND GL.GL_BALANCES.PERIOD_NAME = 'DEC-13'
AND GL.GL_BALANCES.CURRENCY_CODE = 'USD'
AND GL.GL_BALANCES.ACTUAL_FLAG ='A'
GROUP BY SEGMENT1, SEGMENT2, SEGMENT5, PERIOD_NAME , ACCOUNT_TYPE
ORDER BY ACCOUNT_TYPE,SEGMENT1 ) WHERE YTD_ACTUAL_AMOUNT <>0 ;

QUERY TO CHECK PERIODS STATUS (OPEN/CLOSE) FOR ALL MODULES

 SELECT ROWID,

  (SELECT application_short_name

  FROM fnd_application fa
  WHERE fa.application_id = gps.application_id
  ) application,
  (SELECT name
  FROM gl_sets_of_books gsp
  where GSP.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
  ) SETOFBOOK,
  period_name,
  closing_status,
  DECODE (gps.closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never' ) status,
  period_num,
  period_year,
  start_date,
  end_date
from GL_PERIOD_STATUSES GPS
WHERE PERIOD_YEAR = '2018'
ORDER BY period_year DESC,
  period_num DESC