FSQL 5

PAYABLES NON-RECONCILED JOURNALS IN ORACLE CLOUD FUSION R13

SELECT          DISTINCT CEL1.Meaning   ORIGIN ,

            CEL.MEANING  TRX_TYPE,

            to_char(AC.CHECK_NUMBER) DOC_NUMBER,

            TO_CHAR(NVL(AC.CLEARED_DATE, AC.CHECK_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_HEADERS GJH,

            GL_JE_LINES GJL,

        GL_JE_BATCHES GJB,

            GL_LEDGERS  GL,

            XLA_AE_HEADERS XAH ,

            XLA_AE_LINES XAL,

            AP_CHECKS_ALL AC,

            XLA_TRANSACTION_ENTITIES TRX,

            XLA_EVENTS XE ,

            CE_INTERNAL_BANK_ACCTS_V ACCT,

            GL_IMPORT_REFERENCES GLIR,

            CE_LOOKUPS CEL,

            CE_LOOKUPS CEL1,

            CE_TRX_TYPE_MAPPING CE_TRX,

            AP_LOOKUP_CODES ap_lookups

WHERE   GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

AND    GJH.JE_SOURCE = 'Payables'

AND    GJL.LEDGER_ID = GL.LEDGER_ID

AND    GL.LEDGER_CATEGORY_CODE    = 'PRIMARY'

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

   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  TRX.SOURCE_ID_INT_1 = AC.CHECK_ID  

AND NVL(TRX.SOURCE_ID_INT_1, -99) = AC.CHECK_ID

AND    XE.ENTITY_ID = TRX.ENTITY_ID

AND    XAH.EVENT_ID = XE.EVENT_ID

AND    TRX.APPLICATION_ID =200

AND    AC.RECON_FLAG = 'N'

AND    ac.status_lookup_code  in ('NEGOTIABLE', 'CLEARED') 

AND    TRX.APPLICATION_ID = XE.APPLICATION_ID

AND    TRX.ENTITY_CODE = 'AP_PAYMENTS' 

AND    XAL.GL_SL_LINK_TABLE IN ('XLAJEL')

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

AND    XE.EVENT_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE

AND     GJL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID

AND     ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID

AND     ac.bank_account_name =ACCT.bank_account_name

AND     CEL1.lookup_type = 'CE_UNRECON_SOURCE'

AND     CEL1.lookup_code = 'AP'

AND     CEL.lookup_type(+) = 'CE_TRX_TYPE'

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

AND     CE_TRX.trx_type =CEL.LOOKUP_CODE(+)

AND     CE_TRX.PMT_RCT_METHOD(+)=ac.payment_method_code