FSQL 7

SQL QUERY FOR EXTERNAL TRANSACTIONS IN ORACLE CLOUD FUSION R13

SELECT CEL.Meaning ORIGIN                              ,

       CEL1.Meaning TRX_TYPE                  ,

       TO_CHAR(EXT.REFERENCE_TEXT) DOC_NUMBER,

       TO_CHAR(EXT.TRANSACTION_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            ,

       CE_EXTERNAL_TRANSACTIONS EXT,

       XLA_TRANSACTION_ENTITIES TRX,

       XLA_EVENTS XE,

       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         = 'Cash Management'

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 = GLIR.GL_SL_LINK_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   = EXT.TRANSACTION_ID

   AND XE.ENTITY_ID          = TRX.ENTITY_ID

   AND XAH.EVENT_ID          = XE.EVENT_ID

   AND TRX.APPLICATION_ID    =260

   AND EXT.STATUS            = 'UNR'    

   AND TRX.APPLICATION_ID    = XE.APPLICATION_ID

   AND TRX.ENTITY_CODE       = 'CE_EXTERNAL'

   AND CEL.lookup_type = 'CE_UNRECON_SOURCE'    AND CEL.Lookup_code = 'XT'                              

   AND CEL1.Lookup_type(+) = 'CE_TRX_TYPE' AND CEL1.lookup_code(+) = EXT.TRANSACTION_TYPE 

   AND XAL.GL_SL_LINK_TABLE IN ('XLAJEL')

   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