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