Query for Checking GL to XLA (AP) Invoice

Post date: May 18, 2011 4:16:9 AM

select distinct gjh.NAME JOURNAL_NAME

,gjb.NAME BATCH_NAME

,gjh.JE_SOURCE SOURCE

,gjh.JE_CATEGORY CATEGORY

,gjh.currency_code

,gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'||gcc.SEGMENT3||'.'||

gcc.SEGMENT4||'.'||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'||

gcc.SEGMENT7||'.'||gcc.SEGMENT8||'.'||gcc.SEGMENT9 ACCOUNT

,NVL(gjl.ENTERED_DR,0) DEBIT_VALAS

,NVL(gjl.ENTERED_CR,0) CREDIT_VALAS

,NVL(gjl.ACCOUNTED_DR,0) DEBIT

,NVL(gjl.ACCOUNTED_CR,0) CREDIT

,xah.EVENT_TYPE_CODE

,xal.ACCOUNTING_CLASS_CODE

,NVL(xal.ENTERED_DR,0) DEBIT_VALAS_XLA

,NVL(xal.ENTERED_CR,0) CREDIT_VALAS_XLA

,NVL(xal.ACCOUNTED_DR,0) DEBIT_XLA

,NVL(xal.ACCOUNTED_CR,0) CREDIT_XLA

,aia.INVOICE_NUM

,aia.INVOICE_AMOUNT

,nvl(aia.EXCHANGE_RATE,1) KURS

,XAL.GL_SL_LINK_ID

from gl_je_headers gjh

,gl_je_lines gjl

,gl_je_batches gjb

,gl_import_references gir

,gl_code_combinations gcc

,xla_ae_lines xal

,xla_ae_headers xah

,ap_invoice_distributions_all aida

,ap_invoices_all aia

where gjh.JE_HEADER_ID = gjl.JE_HEADER_ID

and gjh.JE_BATCH_ID = gjb.JE_BATCH_ID

and gjh.JE_HEADER_ID = gir.JE_HEADER_ID

and gjl.JE_LINE_NUM = gir.JE_LINE_NUM

and gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

and gir.GL_SL_LINK_ID = xal.GL_SL_LINK_ID

and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

and xal.AE_HEADER_ID = xah.AE_HEADER_ID

and xal.APPLICATION_ID = xah.APPLICATION_ID

and xah.EVENT_ID = aida.ACCOUNTING_EVENT_ID

and aida.INVOICE_ID = aia.invoice_id

AND GJH.PERIOD_NAME in ('APR-11')--,'Jan-10','Feb-10','Mar-10','Apr-10','May-10','Jun-10','Jul-10','Aug-10','Oct-10','Nov-10','Dec-10')

AND gcc.segment3 in ('2133969')--,'211112','211113','211114','216112')

AND GJH.ACTUAL_FLAG = 'A'

AND GJH.STATUS = 'P'

AND GCC.ENABLED_FLAG = 'Y'

--and aia.INVOICE_NUM = 'KB10010914'

and (nvl(XAL.ACCOUNTED_DR,0) -nvl( xal.accounted_cr,0)) <> 0

--ORDER BY xah.AE_HEADER_ID, xal.AE_LINE_NUM

Query Above can be reconcile with Payables Posted Invoice Register Report (PPIR) for certain period and account

select *

from gl_interface

where code_combination_id in ('7198','18447')

select * from xla_transaction_entities_upg

where source_id_int_1 = 23493

select * from xla_events where entity_id in (

select entity_id from xla_transaction_entities_upg

where source_id_int_1 = 23493)

select * From xla_ae_headers where event_id in(select event_id from xla_events where entity_id in (

select entity_id from xla_transaction_entities_upg

where source_id_int_1 = 23493))

select * from xla_ae_lines

where ae_header_id in (select ae_header_id From xla_ae_headers where event_id in(select event_id from xla_events where entity_id in (

select entity_id from xla_transaction_entities_upg

where source_id_int_1 = 23493)))

and code_combination_id = 7198

select *

from xla_transaction_entities_upg