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

No comments:

Post a Comment