ORACLE EBS R12: QUERY FOR SLA-GL RECONCILIATION OF PAYABLES ACCOUNTS (LIABILITY, PREPAYMENTS ETC.)

The Query provided in this post can be used to reconcile a Payables control Account to GL. This may be a liability account or a prepayment account:

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   xep.name legal_entity,
gjh.name jv_name,
xl.meaning,
gjl.je_line_num,
gjl.accounted_dr,
gjl.accounted_cr,
gl.segment4 natural_account,
hou.name operating_unt,
hou.organization_id,
xlate.entity_code,
pov.vendor_name,
ap.invoice_num,
ap.invoice_date,
xlal.accounting_date,
xlal.accounted_dr,
xlal.accounted_cr,
xlal.code_combination_id,
gl.segment1
|| decode(gl.segment2,null,null,’.’)
|| gl.segment2
|| decode(gl.segment3,null,null,’.’)
|| gl.segment3
|| decode(gl.segment4,null,null,’.’)
|| gl.segment4
|| decode(gl.segment5,null,null,’.’)
|| gl.segment5
|| decode(gl.segment6,null,null,’.’)
|| gl.segment6
|| decode(gl.segment7,null,null,’.’)
|| gl.segment7
|| decode(gl.segment8,null,null,’.’)
|| gl.segment8 gl_acct
from gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla.xla_transaction_entities xlate,
ap_invoices_all ap,
xla_events xlae,
xla_ae_lines xlal,
xla_ae_headers xlah,
gl_code_combinations gl,
po_vendors pov,
hr_operating_units hou,
xle_entity_profiles xep,
xla_lookups xl
where 1=1
and xlate.application_id = 200
and xlate.source_id_int_1 = ap.invoice_id
and pov.vendor_id = ap.vendor_id
and gl.code_combination_id = xlal.code_combination_id
and xlal.ae_header_id = xlah.ae_header_id
and xlate.entity_id = xlae.entity_id
and xlah.event_id = xlae.event_id
and xlae.application_id = xlate.application_id
and xlate.entity_code = ‘AP_INVOICES’
and ap.org_id = hou.organization_id
and xlal.gl_sl_link_id = gir.gl_sl_link_id
and xlal.gl_sl_link_table = gir.gl_sl_link_table
and gjh.je_header_id = gir.je_header_id
and gjh.je_header_id = gjl.je_header_id
and gir.je_line_num = gjl.je_line_num
and xep.legal_entity_id = hou.default_legal_context_id
and xlal.accounting_class_code = xl.lookup_code
—     and ap.invoice_id = :p_invoice_id
and gl.CODE_COMBINATION_ID in (select gcc.CODE_COMBINATION_ID from  gl_code_combinations gcc where gcc.SEGMENT4 in (‘22301001′) )  — Your required Recon Accounts (Lia/Prepay)
—     and trunc (xlal.accounting_date) between :p_from_date and :p_to_date  —-accounting date parameters—-
—     and hou.organization_id = :org_id     ——operating unit——
union all
select   xep.name legal_entity,
gjh.name jv_name,
xl.meaning,
gjl.je_line_num,
gjl.accounted_dr,
gjl.accounted_cr,
gl.segment4 natural_account,
hou.name operating_unit,
hou.organization_id,
xlate.entity_code,
pov.vendor_name,
to_char (aca.check_number),
aca.check_date,
xlal.accounting_date,
xlal.accounted_dr,
xlal.accounted_cr,
xlal.code_combination_id,
gl.segment1
|| decode(gl.segment2,null,null,’.’)
|| gl.segment2
|| decode(gl.segment3,null,null,’.’)
|| gl.segment3
|| decode(gl.segment4,null,null,’.’)
|| gl.segment4
|| decode(gl.segment5,null,null,’.’)
|| gl.segment5
|| decode(gl.segment6,null,null,’.’)
|| gl.segment6
|| decode(gl.segment7,null,null,’.’)
|| gl.segment7
|| decode(gl.segment8,null,null,’.’)
|| gl.segment8 gl_acct
from gl_import_references gir,
xla.xla_transaction_entities xlate,
ap_checks_all aca,
xla_events xlae,
xla_ae_lines xlal,
xla_ae_headers xlah,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gl,
po_vendors pov,
hr_operating_units hou,
xle_entity_profiles xep,
xla_lookups xl
where 1=1
and xlate.application_id = 200
and xlate.source_id_int_1 = aca.check_id
and pov.vendor_id = aca.vendor_id
and gl.code_combination_id = xlal.code_combination_id
and xlal.ae_header_id = xlah.ae_header_id
and xlate.entity_id = xlae.entity_id
and xlah.event_id = xlae.event_id
and xlae.application_id = xlate.application_id
and xlate.entity_code = ‘AP_PAYMENTS’
and hou.organization_id = aca.org_id
and hou.default_legal_context_id = xep.legal_entity_id
and xlal.gl_sl_link_id = gir.gl_sl_link_id
and xlal.gl_sl_link_table = gir.gl_sl_link_table
and gjh.je_header_id = gir.je_header_id
and gjh.je_header_id = gjl.je_header_id
and gir.je_line_num = gjl.je_line_num
and xlal.accounting_class_code = xl.lookup_code
and gl.CODE_COMBINATION_ID in (select gcc.CODE_COMBINATION_ID from  gl_code_combinations gcc where gcc.SEGMENT4 in (‘22301001′) ) — Your required Recon Accounts (Lia/Prepay)
/*and aca.check_id = (
select aipa.CHECK_ID
from ap_invoice_payments_all aipa
where aipa.INVOICE_ID = :p_invoice_id
and NVL(aipa.REVERSAL_FLAG,’N’) = ‘N’
)*/
—     and trunc (xlal.accounting_date) between :p_from_date and :p_to_date  —-accounting date parameters—-
—     and hou.organization_id = :org_id     ——operating unit——
order by 2,4