FSQL 9

SQL QUERY FOR MANUAL JOURNAL IN XLA IN ORACLE CLOUD FUSION R13

SELECT CEL.MEANING ORIGIN   ,

       NULL TRX_TYPE,

       XAH.DESCRIPTION DOC_NUMBER, -- GLH.NAME  DOC_NUMBER,

       TO_CHAR(XAL.ACCOUNTING_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 ' || XAL.AE_LINE_NUM ||',' ||XAH.DESCRIPTION||','||GJB.NAME STATEMENT_NUMBER,

       XAH.DESCRIPTION JOURNAL_NAME,

       GJB.NAME BATCH_NAME

FROM GL_JE_BATCHES GJB,

     GL_IMPORT_REFERENCES GLIR,

     XLA_AE_HEADERS XAH ,

     XLA_AE_LINES XAL,

            GL_LEDGERS  GL, 

     CE_INTERNAL_BANK_ACCTS_V CE,

     CE_LOOKUPS CEL

WHERE

  xal.CODE_COMBINATION_ID = CE.ASSET_CODE_COMBINATION_ID

AND CE.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID

AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE

AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND    XAL.LEDGER_ID = GL.LEDGER_ID

AND    GL.LEDGER_CATEGORY_CODE    = 'PRIMARY'

and xah.application_id = xal.application_id

AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID

and XAL.gl_sl_link_table = GLIR.GL_SL_LINK_table

AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID

AND CEL.lookup_type = 'CE_UNRECON_SOURCE'

AND CEL.lookup_code = 'GL' 

AND GJB.STATUS = 'P'

AND xah.event_type_code = 'MANUAL'