Drilldown from GL, XLA to AP Invoice Transaction

SELECT  APIA.INVOICE_NUM, APIA.set_of_books_id , XLAH.LEDGER_ID XLAH_LEDGER_ID,XLAL.LEDGER_ID XLAL_LEDGER_ID, GLJH.LEDGER_ID GL_LEDGER_ID,


    GLJB.NAME JE_BATCH_NAME,
       GLJB.DESCRIPTION JE_BATCH_DESCRIPTION,
       GLJB.RUNNING_TOTAL_ACCOUNTED_DR JE_BATCH_TOTAL_DR,
       GLJB.RUNNING_TOTAL_ACCOUNTED_CR JE_BATCH_TOTAL_CR,
       GLJB.STATUS JE_BATCH_STATUS,
       GLJB.DEFAULT_EFFECTIVE_DATE JE_BATCH_EFFECTIVE_DATE,
       GLJB.DEFAULT_PERIOD_NAME JE_BATCH_PERIOD_NAME,
       GLJB.CREATION_DATE JE_BATCH_CREATION_DATE,
       
       FDU.USER_NAME JE_BATCH_CREATED_BY,

       GLJH.JE_CATEGORY JE_HEADER_CATEGORY,
       GLJH.JE_SOURCE JE_HEADER_SOURCE,
       GLJH.PERIOD_NAME JE_HEADER_PERIOD_NAME,
       GLJH.NAME JE_HEADER_JOURNAL_NAME,
       GLJH.STATUS JE_HEADER_JOURNAL_STATUS,
       GLJH.CREATION_DATE JE_HEADER_CREATED_DATE,
       GLJH.DESCRIPTION JE_HEADER_DESCRIPTION,
       GLJH.RUNNING_TOTAL_ACCOUNTED_DR JE_HEADER_TOTAL_ACCTD_DR,
       GLJH.RUNNING_TOTAL_ACCOUNTED_CR JE_HEADER_TOTAL_ACCTD_CR,
       
       FDU1.USER_NAME JE_HEADER_CREATED_BY,
       
       GLJL.JE_LINE_NUM JE_LINES_LINE_NUMBER,
       GLJL.LEDGER_ID JE_LINES_LEDGER_ID,
       GLCC.CONCATENATED_SEGMENTS JE_LINES_ACCOUNT,
       GLJL.ENTERED_DR JE_LINES_ENTERED_DR,
       GLJL.ENTERED_CR JE_LINES_ENTERED_CR,
       GLJL.ACCOUNTED_DR JE_LINES_ACCOUNTED_DR,
       GLJL.ACCOUNTED_CR JE_LINES_ACCOUNTED_CR,
       GLJL.DESCRIPTION JE_LINES_DESCRIPTION,
       GLCC1.CONCATENATED_SEGMENTS XLA_LINES_ACCOUNT,
       
       XLAL.ACCOUNTING_CLASS_CODE XLA_LINES_ACCT_CLASS_CODE,
       XLAL.ACCOUNTED_DR XLA_LINES_ACCOUNTED_DR,
       XLAL.ACCOUNTED_CR XLA_LINES_ACCOUNTED_CR,
       XLAL.DESCRIPTION XLA_LINES_DESCRIPTION,
       XLAL.ACCOUNTING_DATE XLA_LINES_ACCOUNTING_DATE,

       XLATE.ENTITY_CODE XLA_TRX_ENTITY_CODE,
       XLATE.SOURCE_ID_INT_1 XLA_TRX_SOURCE_ID_INT_1,
       XLATE.SOURCE_ID_INT_2 XLA_TRX_SOURCE_ID_INT_2,
       XLATE.SOURCE_ID_INT_3 XLA_TRX_SOURCE_ID_INT_3,
       XLATE.SECURITY_ID_INT_1 XLA_TRX_SECURITY_ID_INT_1,
       XLATE.SECURITY_ID_INT_2 XLA_TRX_SECURITY_ID_INT_2,
       XLATE.TRANSACTION_NUMBER XLA_TRX_TRANSACTION_NUMBER
       
  FROM GL_JE_BATCHES GLJB,
       GL_JE_HEADERS GLJH,
       GL_JE_LINES GLJL,

       FND_USER FDU,
       FND_USER FDU1,

       GL_CODE_COMBINATIONS_KFV GLCC,
       GL_CODE_COMBINATIONS_KFV GLCC1,
     
       GL_IMPORT_REFERENCES GIR,

       XLA_AE_LINES XLAL,
       XLA_AE_HEADERS XLAH,
       XLA_EVENTS XLAE,
       XLA.XLA_TRANSACTION_ENTITIES XLATE,
       
       AP_INVOICES_ALL APIA,
        HR_OPERATING_UNITS OU

 WHERE 1=1

AND GLJB.CREATED_BY = FDU.USER_ID
 AND GLJH.CREATED_BY = FDU1.USER_ID
 AND GLJB.JE_BATCH_ID = GLJH.JE_BATCH_ID
 AND GLJH.JE_HEADER_ID = GLJL.JE_HEADER_ID
 AND GLJL.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID

 AND GLJL.JE_HEADER_ID = GIR.JE_HEADER_ID
 AND GLJL.JE_LINE_NUM = GIR.JE_LINE_NUM

 AND GIR.GL_SL_LINK_TABLE = XLAL.GL_SL_LINK_TABLE
 AND GIR.GL_SL_LINK_ID = XLAL.GL_SL_LINK_ID

 AND XLAL.APPLICATION_ID = XLAH.APPLICATION_ID
 AND XLAL.AE_HEADER_ID = XLAH.AE_HEADER_ID
 AND XLAL.CODE_COMBINATION_ID = GLCC1.CODE_COMBINATION_ID

 AND XLAH.APPLICATION_ID = XLAE.APPLICATION_ID
 AND XLAH.EVENT_ID = XLAE.EVENT_ID

 AND XLAE.APPLICATION_ID = XLATE.APPLICATION_ID
 AND XLAE.ENTITY_ID = XLATE.ENTITY_ID
 AND XLATE.SOURCE_ID_INT_1 = APIA.INVOICE_ID

AND GLJH.JE_CATEGORY  IN ('PAYMENTS','PURCHASE INVOICES')



--AND B.DEFAULT_PERIOD_NAME       = '01_APR-2009'

AND GLJH.JE_HEADER_ID = 123150

 ORDER BY GLJH.JE_CATEGORY

Purchase Invoice Technical flow AP , SLA to GL

SELECT    AP_INVOICES_PKG.GET_POSTING_STATUS( AIA.INVOICE_ID) "Accounted Status"     
,APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
             AIA.INVOICE_ID
            ,AIA.INVOICE_AMOUNT
            ,AIA.PAYMENT_STATUS_FLAG
            ,AIA.INVOICE_TYPE_LOOKUP_CODE

            ) Approval_Status -- Invoice Staus
,AIA.*
FROM AP_INVOICES_ALL AIA
WHERE AIA.INVOICE_ID = 221309 
--AND T.INVOICE_NUM = 'TEST-IFTIKHAR'

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

SELECT    AP_INVOICES_PKG.GET_POSTING_STATUS( t.INVOICE_ID) "Accounted Status"      ,T.*
FROM AP_INVOICE_LINES_ALL T
WHERE T.INVOICE_ID = 221309 
--AND T.INVOICE_NUM = 'TEST-IFTIKHAR'

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

SELECT AP_INVOICES_PKG.GET_POSTING_STATUS( D.INVOICE_ID) "Accounting Status" ,accounting_event_id,MATCH_STATUS_FLAG ,D.*
FROM AP_INVOICE_DISTRIBUTIONS_ALL D
WHERE D.INVOICE_ID = 221309

----------------------------------------------------
SELECT *
FROM ap_payment_schedules_all AA
WHERE AA.INVOICE_ID = 221309 

----------------------------------------------------
Please Run the following process :    **** Transfer Journal Entries to GL ***

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


----  Accounting Table (Create Accounting Process ) SLA -------------------------

--Create accounting

-- Following  below entry created  in SLA with master entry
SELECT entity_id, xte.* 
FROM xla.xla_transaction_entities xte
WHERE 1=1
AND xte.entity_code = 'AP_INVOICES'
AND xte.application_id = 200 -- payable
and SOURCE_ID_INT_1 = 221309 --INVOICE_ID (AP_INVOICES_ALL)
and xte.transaction_number = 'IHG-TEST' -- INVOICE_NUM ( AP_INVOICES_ALL)

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

SELECT entity_id, aa.*
FROM XLA_AE_HEADERS aa
WHERE 1=1
AND aa.ENTITY_ID = 3278257 --entity_id (xla_transaction_entities)
--WHERE EVENT_ID = 3347721 -- accounting_event_id (AP_INVOICE_DISTRIBUTIONS_ALL)

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

SELECT *
FROM XLA_AE_LINES INV
where inv.ae_header_id = 4957029 --ae_header_id (XLA_AE_HEADERS)