FSQL 8

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

SELECT CEL.MEANING ORIGIN                   ,

       NULL TRX_TYPE                  ,

       GLH.NAME  DOC_NUMBER,

       TO_CHAR(GLL.EFFECTIVE_DATE,'YYYY-MM-DD') TRX_DATE                 ,     

            decode(:gc_bank_type, 'FBANK', (NVL(GLL.ENTERED_DR,0)- NVL(GLL.ENTERED_CR ,0)), (NVL(GLL.ACCOUNTED_DR,0)- NVL(GLL.ACCOUNTED_CR ,0))) BOOKS,

       0 BANK                                         ,

        'Line ' || GLL.je_Line_num ||',' ||GLH.Name||','||GJB.NAME STATEMENT_NUMBER,

       GLH.NAME JOURNAL_NAME,

       GJB.NAME BATCH_NAME

FROM GL_JE_HEADERS  GLH,

GL_JE_LINES GLL,

GL_JE_BATCHES GJB,

            GL_LEDGERS  GL, 

CE_INTERNAL_BANK_ACCTS_V CE,

CE_LOOKUPS CEL

WHERE

GLL.CODE_COMBINATION_ID = CE.ASSET_CODE_COMBINATION_ID

AND CE.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID

AND GLL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE

AND GLH.JE_SOURCE in ( 'Manual','Spreadsheet')

AND GJB.JE_BATCH_ID = GLH.JE_BATCH_ID

AND GLL.JE_HEADER_ID = GLH.JE_HEADER_ID

AND    GLL.LEDGER_ID = GL.LEDGER_ID

AND    GL.LEDGER_CATEGORY_CODE    = 'PRIMARY'

AND    CEL.lookup_type = 'CE_UNRECON_SOURCE'

AND    CEL.lookup_code = 'GL'

AND GJB.STATUS = 'P'

AND ((GLH.JE_FROM_SLA_FLAG is NULL) or (GLH.JE_FROM_SLA_FLAG='N'))

AND NOT EXISTS

    (

      SELECT 1

      FROM CE_RECON_HISTORY_ITEMS CRHI

      WHERE CRHI.SOURCE_ID    = GLL.JE_HEADER_ID

      AND CRHI.SOURCE_LINE_ID = GLL.JE_LINE_NUM

      AND CRHI.RECON_SOURCE       = 'ORA_GL'

      AND CRHI.CLEARED_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE

    )