FSQL 3

SQL QUERY FOR GETTING UNRECONCILED LINES FROM RECEIVABLES IN ORACLE CLOUD FUSION R13

                                                          

SELECT          DISTINCT 'RECEIVABLES'   ORIGIN ,

            CTTM.TRX_TYPE  TRX_TYPE,

            to_char(CR.CASH_RECEIPT_ID) DOC_NUMBER,

            TO_CHAR(NVL(CR.DEPOSIT_DATE, CR.RECEIPT_DATE),'YYYY-MM-DD') TRX_DATE,   

            XAL.ENTERED_DR  ENTERED_BOOKS,

            XAL.ACCOUNTED_DR ACCOUNTED_BOOKS,

            0  BANK,

            RECEIPT_NUMBER  STATEMENT_NUMBER

FROM

              GL_JE_LINES GJL

            , GL_JE_HEADERS GJH

    , GL_JE_BATCHES GJB       

            , GL_IMPORT_REFERENCES GLIR

            , AR_DISTRIBUTIONS_ALL ARD

            , XLA_DISTRIBUTION_LINKS XDL

            , XLA_AE_LINES XAL

            , AR_CASH_RECEIPT_HISTORY_ALL CRH

            , AR_CASH_RECEIPTS_ALL CR

            , XLA_AE_HEADERS XAH

            , CE_TRX_TYPE_MAPPING CTTM

WHERE   GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

AND    GJH.JE_SOURCE = 'Receivables'

   AND GJB.JE_BATCH_ID   = GJH.JE_BATCH_ID 

   AND GJB.STATUS = 'P'        

   AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE

AND    XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND    XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID

AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID

   and xah.application_id = 555                                   

   and xah.application_id = xal.application_id

   and xal.gl_sl_link_table = GLIR.GL_SL_LINK_table

   AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID

AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID

AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM

AND    ARD.SOURCE_TABLE = 'CRH'

AND    ARD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID

AND    ARD.LINE_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1

AND    XDL.APPLICATION_ID = 222

AND    XDL.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'

AND    XAL.APPLICATION_ID = 222

AND    XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

AND    XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

AND    XDL.AE_HEADER_ID = XAH.AE_HEADER_ID

AND    CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID

AND    CR.SET_OF_BOOKS_ID = XAH.LEDGER_ID

AND    CRH.EVENT_ID IS NOT NULL

AND    CR.RECON_FLAG = 'N'

AND CTTM.PMT_RCT_METHOD = TO_CHAR(CR.RECEIPT_METHOD_ID)

AND CTTM.MAPPING_TYPE='RECEIPT'  AND gjl.&p_where