Oracle EBS R12: Query for Inventory AP Reconciliation SLA to GL

The query provided in this post will provide you with the complete list of transactions that sum up to make your AP Accrual Balance in General Ledger. You may amend the query to run for whatever date range/GL Accrual Account you require, and any other columns you see fit:

NOTE: In order to get the most Accurate result when reconciling, please make sure that ALL transactions from inventory and payables modules have been Create Accounted, Transferred and Posted to GL.

SELECT ’32’ AS internal_code, ‘INV’ AS internal_module,
‘RCV-Inventory ‘ AS internal_tran_type,
rt.transaction_type AS tran_type,
rrsl.set_of_books_id AS set_of_books_id,
ood.operating_unit AS org_id,
hou.NAME AS OU_NAME,
rt.organization_id AS organization_id,
rt.transaction_id AS doc_id,
null AS doc_no,
to_char(nvl(rt.primary_quantity,0)) AS doc_no_display,
rt.transaction_date AS doc_date, rt.amount AS amount,
rt.vendor_id AS party_id, rt.vendor_site_id AS party_site_id,
asa.SEGMENT1 AS party_no, asa.VENDOR_NAME AS party_name,
gjh.currency_code AS gl_currency, gjh.period_name AS gl_period_name,
gjh.je_header_id AS gl_header_id, gjh.je_category AS gl_category,
gjh.je_source AS gl_source,
gjh.doc_sequence_value AS gl_doc_no,
gjh.status AS gl_jv_status, gjh.default_effective_date AS gl_date,
gcc.code_combination_id AS ccid, gcc.segment1 AS segment1,
gcc.segment2 AS segment2, gcc.segment3 AS segment3,
gcc.segment4 AS segment4, gcc.segment5 AS segment5,
gcc.segment6 AS segment6, gcc.segment7 AS segment7,
gjl.je_line_num AS gl_line_no,
NVL (gjl.accounted_dr, 0) AS gl_acc_dr,
NVL (gjl.accounted_cr, 0) AS gl_acc_cr,
NVL (xal.accounted_dr, 0) AS xla_acc_dr,
NVL (xal.accounted_cr, 0) AS xla_acc_cr
FROM org_organization_definitions ood,
xla_ae_headers xah,
hr_organization_units hou,
xla_ae_lines xal,
rcv_transactions rt,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations_kfv gcc,
rcv_receiving_sub_ledger rrsl,
ap_suppliers asa
WHERE xah.ae_header_id = xdl.ae_header_id
AND xah.ae_header_id = xal.ae_header_id
and asa.VENDOR_ID = rt.VENDOR_ID
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = ‘RCV_RECEIVING_SUB_LEDGER’
AND xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id
AND rrsl.rcv_transaction_id = rt.transaction_id
AND rt.organization_id = ood.organization_id
and hou.ORGANIZATION_ID = ood.OPERATING_UNIT
AND rrsl.set_of_books_id = ood.SET_OF_BOOKS_ID
AND rrsl.set_of_books_id = xah.ledger_id
AND xah.ledger_id = xal.ledger_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.ledger_id = xal.ledger_id
AND xah.application_id = xal.application_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND gir.je_header_id = gjh.je_header_id
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjh.ledger_id = gjl.ledger_id
AND gjl.code_combination_id = gcc.code_combination_id
and gcc.SEGMENT4 =’22399001′  —-Your Inventory AP Accrual Account
UNION ALL
SELECT   ’10’ AS internal_code,
‘AP’ AS internal_module,
‘Invoices’ AS internal_tran_type,
ai.invoice_type_lookup_code AS tran_type,
ai.set_of_books_id AS set_of_books_id, ai.org_id AS org_id,
hou.NAME as OU_NAME,
-999 AS organization_id, ai.invoice_id AS doc_id,
to_char(ai.doc_sequence_value) AS doc_no,
ai.invoice_num || ‘ – ‘ || ai.doc_sequence_value AS doc_no_display,
ai.invoice_date AS doc_date, NVL (ai.invoice_amount, 0) AS amount,

—    decode(nvl(gjl.accounted_dr,0),0,abs(nvl(aid.amount,0)),(abs(nvl(aid.amount,0)) * -1)) as amount ,
ai.vendor_id AS party_id, ai.vendor_site_id AS party_site_id,
asp.segment1 AS party_no, asp.vendor_name AS party_name,

— mst xdl.source_distribution_id_num_1 as invoice_distribution_id ,
— mst     xdl.source_distribution_type as source_distribution_type,
gjh.currency_code AS gl_currency, gjh.period_name AS gl_period_name,
gjh.je_header_id AS gl_header_id, gjh.je_category AS gl_category,
gjh.je_source AS gl_source, gjh.doc_sequence_value AS gl_doc_no,
gjh.status AS gl_jv_status, gjh.default_effective_date AS gl_date,
gcc.code_combination_id AS ccid, gcc.segment1 AS segment1,
gcc.segment2 AS segment2, gcc.segment3 AS segment3,
gcc.segment4 AS segment4, gcc.segment5 AS segment5,
gcc.segment6 AS segment6, gcc.segment7 AS segment7,
gjl.je_line_num AS gl_line_no,
NVL (gjl.accounted_dr, 0) AS gl_acc_dr,
NVL (gjl.accounted_cr, 0) AS gl_acc_cr,
NVL (xal.accounted_dr, 0) AS xla_acc_dr,
NVL (xal.accounted_cr, 0) AS xla_acc_cr
FROM xla.xla_transaction_entities xte,
ap_invoices_all ai,
xla_events xe,
xla_ae_lines xal,
xla_ae_headers xah,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
ap_supplier_sites_all ass,
ap_suppliers asp,
hr_operating_units hou,
xle_entity_profiles xep
WHERE xte.source_id_int_1 = ai.invoice_id
AND xte.ledger_id = ai.set_of_books_id
AND ai.vendor_id = asp.vendor_id
AND asp.vendor_id = ass.vendor_id
AND ass.vendor_site_id = ai.vendor_site_id
AND gcc.code_combination_id = xal.code_combination_id
AND xal.ae_header_id = xah.ae_header_id
AND xte.ledger_id = xal.ledger_id
AND xte.entity_id = xe.entity_id
AND xah.event_id = xe.event_id
AND xte.ledger_id = xah.ledger_id
AND xe.application_id = xte.application_id
AND ai.org_id = hou.organization_id
AND xep.legal_entity_id = hou.default_legal_context_id
AND xte.application_id = 200
—-  and ai.invoice_id = 1 —854336
AND xte.entity_code = ‘AP_INVOICES’
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND xte.entity_code = ‘AP_INVOICES’
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gir.je_header_id = gjh.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjh.ledger_id = gjl.ledger_id
AND xte.ledger_id = gjh.ledger_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gjl.code_combination_id = xal.code_combination_id
and gcc.segment4 = ‘22399001’  —-Your Inventory AP Accrual Account
–and gjh.status            = ‘p’
— and gjh.doc_sequence_value = ‘1411000105’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s