Fusion Financials : How to link GL data to the subledger data

How to link GL data to the subledger data

---------------

1. ap_invoices_all (pass invoice num) (get invoice_id)
2. ap_invoice_payments_all (pass invoice_id) (get_check_id)
3. ap_checks_all (pass check_id) (get payment_id)

-> iby_payments_all (pass payment_id) ( get  other vendor, bank info)

1. xla_entity_id_mappings (pass entity_code='AP_PAYMENTS') get (TRANSACTION_ID_COL_NAME_1 -> CHECK_ID, SOURCE_ID_COL_NAME_1 -> SOURCE_ID_INT_1)
2. xla_transaction_entities  (pass SOURCE_ID_INT_1 = <check_id>) (get Entity_id, applicaiton_id)
3. xla_events (pass entity_id) (get event_id)
4. xla_ae_headers (pass event_id) (get ae_header_id)
5. xla_ae_lines (pass ae_header_id) (get gl_sl_link_id, gl_sl_link_table)
6. gl_import_references (pass gl_sl_link_id, gl_sl_link_table) (get je_header_id)

--------

• gl_je_lines (je_header_id, je_line_num)                -> gl_import_references (je_header_id, je_line_num)
• gl_import_references (gl_sl_link_table, gl_sl_link_id) -> xla_ae_lines (gl_sl_link_table, gl_sl_link_id)
• xla_ae_lines (applicaiton_id, ae_header_id)            -> xla_ae_headers (application_id, ae_header_id)
• xla_ae_headers (application_id, event_id)              -> xla_events (application_id, event_id)
• xla_events (application_id, entity_id)                 -> xla.xla_transaction_entities (application_id, entity_id)

• xla.xla_transaction_entities (source_id_int_1, etc) after filtering by application_id, entity_code and ledger_id     -> subledger's table(its key columns mentioned in xla_entity_id_mappings) for that ledger_id
• For Ex:
• xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_INVOICES and ledger_id -> ap_invoices_all (invoice_id) for that set_of_books_id.
• xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_PAYMENTS and ledger_id -> ap_checks_all (check_id) for that set_of_books_id.
• xla.xla_transaction_entities (source_id_int_1) filtered by application_id 222, entity_code TRANSACTIONS and ledger_id -> ra_customer_trx_all (customer_trx_id) for that set_of_books_id.

-----
helpful sql joining ap to gl links

select
'X'
     --  aca.check_number,
     --  aipa.invoice_payment_id,
     --  aca.payment_id,
     --  aipa.invoice_id,
     --  aipa.payment_num,
     --  aia.invoice_num,
     --  aia.gl_date,
     --  aia.invoice_amount,
     --  xal.code_combination_id xla_ccid,
     --  xal.ae_header_id,
     --  xal.ae_line_num,
     --  ipa.document_sequence_value
FROM
  ap_invoices_all aia
, ap_checks_all aca
, ap_invoice_payments_all aipa
, iby_payments_all ipa
, xla_transaction_entities xte
, xla_events xe
, xla_ae_headers xah
, xla_ae_lines xal
where 1=1
and aia.invoice_id = aipa.invoice_id
and aipa.check_id  = aca.check_id
and ipa.payment_id = aca.payment_id
and xte.entity_code ='AP_PAYMENTS'
and xte.source_id_int_1 = aca.check_id
and xte.SECURITY_ID_INT_1 = :p_business_unit
and xte.ledger_id = :p_ledger_id
and xte.source_application_id =200
and xte.entity_id = xe.entity_id
and xe.event_id = xah.event_id
and xal.ae_header_id = xah.ae_header_id
and xal.gl_sl_link_table = 'XLAJEL'
and xal.gl_sl_link_id = GIR.GL_SL_LINK_ID
--and aca.check_number='6'