AR To GL Via Subledger Links and Query

AR To GL Via Subledger  Links and Query

SELECT 

         ACCOUNT_NUMBER,

         ACCOUNT_NAME,

         TRX.TRX_NUMBER INVOICE_NUMBER,

         GJB.NAME BATCH_NAME,

         GJH.NAME JOURNAL_NAME,
         GJH.RUNNING_TOTAL_ACCOUNTED_DR TOTAL_DEBIT_AMOUNT,
         GJH.RUNNING_TOTAL_ACCOUNTED_CR TOTAL_CREDIT_AMOUNT,
         GIR.REFERENCE_9 INVOICE_AMOUNT, ACCOUNTING_CLASS_CODE
   FROM     

        GL_JE_HEADERS  GJH,
        GL_JE_LINES         GJL,
        GL_JE_BATCHES   GJB,
        GL_IMPORT_REFERENCES GIR,
        XLA_AE_LINES         XAL,
        XLA_AE_HEADERS  XAH,
        XLA_DISTRIBUTION_LINKS LK,
        RA_CUST_TRX_LINE_GL_DIST_ALL GL_DIST,
        RA_CUSTOMER_TRX_ALL TRX,
        HZ_CUST_ACCOUNTS_ALL CUST_ACCT
WHERE 

            GJH.JE_HEADER_ID   =    GJL.JE_HEADER_ID
  AND   GJH.JE_BATCH_ID      =    GJB.JE_BATCH_ID

--AND   GJH.NAME                  =    'APR-10 DEBIT MEMOS USD'
  AND   GJH.JE_HEADER_ID    =   GIR.JE_HEADER_ID
  AND   GJL.JE_LINE_NUM       =   GIR.JE_LINE_NUM
  AND GJB.JE_BATCH_ID         =   GIR.JE_BATCH_ID
  AND JE_SOURCE                  =   'RECEIVABLES'
  AND GJH.STATUS                  =  'P'
  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 LK.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
  AND LK.APPLICATION_ID      =  222
  AND LK.AE_HEADER_ID       =  XAL.AE_HEADER_ID
  AND LK.AE_LINE_NUM         =  XAL.AE_LINE_NUM
  AND LK.AE_HEADER_ID       =  XAH.AE_HEADER_ID
  AND XAL.ACCOUNTING_CLASS_CODE IN ('RECEIVABLE','REVENUE')
  AND GIR.REFERENCE_9 IS NOT NULL
AND GL_DIST.CUST_TRX_LINE_GL_DIST_ID = LK.SOURCE_DISTRIBUTION_ID_NUM_1
  AND GL_DIST.ACCOUNT_SET_FLAG = 'N'
--AND GL_DIST.ACCOUNT_CLASS = 'REV'
  AND GL_DIST.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
  AND TRX.COMPLETE_FLAG = 'Y'
  AND CUST_ACCT.CUST_ACCOUNT_ID = TRX.BILL_TO_CUSTOMER_ID
  AND TRX_NUMBER = '900029562'
  AND GJH.JE_HEADER_ID = 414923
  AND GJB.JE_BATCH_ID = 422909