Loading

Friday, October 22, 2010

Data Flow between Receiving Transactions (RCV) – Sub ledger Accounting (XLA) – General Ledger (GL)

When I tried to understand the flow of data between PO transactions to GL, I found the below and sharing the same. All the queries used below were tested in R12.1.1 instance. 

Collecting the Receiving data related to PO

SELECT rrsl.rcv_sub_ledger_id -- Connects to SLA tables
      ,rrsl.rcv_transaction_id
      ,rrsl.je_source_name
      ,rrsl.code_combination_id
      ,rrsl.accounted_dr
      ,rrsl.accounted_cr
      ,rrsl.reference1
      ,rrsl.reference2   PO_HEADER_ID
      ,rrsl.reference3   PO_DISTRIBUTION_ID
      ,rt.transaction_id
      ,rt.transaction_type
      ,rt.po_header_id
      ,rt.po_release_id
      ,rt.po_line_id
      ,rt.po_line_location_id
      ,pd.code_combination_id
FROM   rcv_receiving_sub_ledger rrsl
      ,rcv_transactions rt
      ,po_distributions_all pd
WHERE pd.po_distribution_id = rt.PO_DISTRIBUTION_ID
AND   rt.transaction_id = rrsl.rcv_transaction_id
AND   rt.PO_DISTRIBUTION_ID = rrsl.reference3
AND   rt.po_header_id = 500;


Collecting the data from XLA
In R12, we have XLA tables that holds link between any sub ledger data to General ledger data.
The below query gives you the important columns available in the XLA tables and GL linking columns for the above RCV_SUB_LEDGER_ID

SELECT xal.gl_sl_link_id     -- Connects to GL tables
      ,xal.gl_sl_link_table  -- Connects to GL tables
      ,xdl.source_dIstribution_id_num_1 rcv_sub_ledger_id
      ,xdl.line_definition_code
      ,xdl.event_class_code
      ,xdl.event_type_code    
      ,xah.ae_header_id
      ,xah.event_id
      ,xah.entity_id
      ,xah.event_type_code
      ,xah.description
      ,xal.ae_line_num
      ,xal.code_combination_id
      ,xal.accounting_class_code     
FROM   xla_events               xev
      ,xla_ae_headers           xah
      ,xla_ae_lines             xal
      ,XLA_DISTRIBUTION_LINKS   xdl
WHERE xah.application_id    = xev.application_id
AND   xev.event_id          = xah.event_id
AND xal.application_id    = xah.application_id
AND xal.ae_header_id      = xah.ae_header_id
AND xev.application_id    = xdl.application_id
AND xal.ae_line_num       = xdl.ae_line_num
AND xah.ae_header_id      = xdl.ae_header_id
AND xdl.SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
AND xdl.source_dIstribution_id_num_1   = 3002;



Collecting the data from GL
Below query collects the data from GL.
In the below query, you can notice the linking columns in the first five places and others were just important ones.

SELECT glimp.reference_5 ENTITY_ID
      ,glimp.reference_6 EVENT_ID
      ,glimp.reference_7 AE_HEADER_ID
      ,glimp.gl_sl_link_id
      ,glimp.gl_sl_link_table
      ,glb.je_batch_id
      ,glb.name
      ,glb.status
      ,glb.description
      ,glh.je_header_id
      ,glh.je_category
      ,glh.je_source
      ,glh.name
      ,glh.description
      ,glh.running_total_accounted_cr
      ,glh.running_total_accounted_dr
      ,gll.je_line_num
      ,gll.code_combination_id
FROM   gl_import_references glimp
      ,gl_je_batches glb
      ,gl_je_headers glh
      ,gl_je_lines   gll
WHERE  glimp.je_header_id     = glh.je_header_id
AND    glimp.je_line_num      = gll.je_line_num
AND    glimp.je_batch_id      = glb.je_batch_id
AND    glh.je_header_id       = gll.je_header_id
AND    glh.je_batch_id        = glb.je_batch_id
AND    glimp.gl_sl_link_id    = 245133         
AND    glimp.gl_sl_link_table = 'XLAJEL';

1 Responses to “Data Flow between Receiving Transactions (RCV) – Sub ledger Accounting (XLA) – General Ledger (GL)”

Sridevi Koduru said...
August 17, 2014 at 11:17 PM

I am Sridevi Koduru, Senior Oracle Apps Trainer at Oracleappstechnical.com With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K at training@oracleappstechnical.com | sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.