General Ledger(GL) to Account Payables(AP)

Collecting the sub ledger AP information (Invoice Information)

Here we collect all the data about the Invoice number from sub-ledger (AP) tables. The most important information and used to link with GL tables is AP_INVOICES_ALL.invoice_id

SELECT  aia.invoice_id -- This will form a link with SLA Tables

       ,aia.invoice_num

       ,aia.invoice_amount

       ,aia.payment_status_flag

       ,aia.doc_category_code

       ,aila.line_type_lookup_code

       ,aila.line_source

       ,aila.match_type

FROM   ap_invoices_all aia

      ,ap_invoice_lines_all aila

WHERE  aia.invoice_id = aila.invoice_id

AND    aia.org_id = aila.org_id

AND    aia.invoice_id = 11844

AND    aia.org_id = 82;

Checking the sub-ledger Tables to General-ledger link tables for GL information

In R12, we have 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

SELECT xte.legal_entity_id

      ,xte.source_id_int_1 Invoice_id

      ,xte.transaction_number

      ,xal.gl_sl_link_id     -- Connects to GL tables

      ,xal.gl_sl_link_table  -- Connects to GL tables

      ,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

      ,xal.party_id

      ,xal.party_site_id

      ,xal.party_type_code   

FROM   xla_transaction_entities xte

      ,xla_events             xev

      ,xla_ae_headers         xah

      ,xla_ae_lines           xal    

WHERE xte.application_id  = 200

AND xte.source_id_int_1   = 11844

AND xte.transaction_number= '1001'

AND xev.entity_id         = xte.entity_id

AND xev.application_id    = xte.application_id

AND 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;

Collecting the data from GL

Below query collects the data from GL.

In the below query, you the see the linking columns in the first five places and others were other 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    = 508000        

AND    glimp.gl_sl_link_table = 'XLAJEL';