FSQL 5

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

SELECT          DISTINCT CEL1.Meaning   ORIGIN ,

            CEL.Meaning  TRX_TYPE  ,

            to_char(CR.RECEIPT_NUMBER) DOC_NUMBER,

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

            decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS, 

            0  BANK,

                        'Line ' || GJL.je_Line_num ||',' ||GJH.Name||','||GJB.NAME STATEMENT_NUMBER,

        GJH.NAME JOURNAL_NAME,

        GJB.NAME BATCH_NAME

FROM

              GL_JE_LINES GJL

            , GL_JE_HEADERS GJH

            , GL_JE_BATCHES  GJB

            , 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

            , CE_INTERNAL_BANK_ACCTS_V ACCT

            , GL_IMPORT_REFERENCES GLIR

            , CE_LOOKUPS CEL

            , CE_LOOKUPS CEL1

WHERE   GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

AND    GJH.JE_SOURCE = 'Receivables'

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 = 222                                   

   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 GJB.JE_BATCH_ID = GJH.JE_BATCH_ID

AND GJB.STATUS = 'P'  

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     CTTM.PMT_RCT_METHOD(+) = TO_CHAR(CR.RECEIPT_METHOD_ID)

AND     CTTM.MAPPING_TYPE(+)='RECEIPT'

and    nvl(CTTM.active_flag (+), 'Y') = 'Y'

AND     crh.status in ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')  

and    crh.current_record_flag = 'Y'  

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    GJL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID

AND     ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID

--AND  GJL.EFFECTIVE_DATE BETWEEN  :GC_FROM_DATE AND :GC_TO_DATE 

AND    XAL.ACCOUNTING_DATE BETWEEN  :GC_FROM_DATE AND :GC_TO_DATE

AND     CEL.lookup_type (+)= 'CE_TRX_TYPE' 

AND     CEL.lookup_code (+)= CTTM.TRX_TYPE

AND     CEL1.lookup_type = 'CE_UNRECON_SOURCE'

AND     CEL1.lookup_code = 'AR'