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