FSQL 6

SQL QUERY FOR UNRECONCILED  PAYROLL  TRX IN ORACLE CLOUD FUSION R13

SELECT   DISTINCT CEL.Meaning   ORIGIN ,

            CEL.MEANING  TRX_TYPE,

            to_char(CE_PR.CHECK_NUMBER) DOC_NUMBER,

            TO_CHAR(NVL(CE_PR.CLEARED_DATE, CE_PR.PAYMENT_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   PAY_CE_TRANSACTIONS CE_PR,

       CE_BANK_ACCOUNTS_PAY_V            CE_BA,

       CE_TRX_TYPE_MAPPING        CTTM,

       --CE_INTERNAL_BANK_ACCTS_V ACCT, 

   GL_IMPORT_REFERENCES GLIR,

   GL_JE_LINES    GJL,

   GL_JE_HEADERS  GJH,

   GL_JE_BATCHES  GJB,

   GL_LEDGERS  GL,

   xla_ae_headers xah,

   XLA_AE_LINES   XAL,

   pay_xla_events pe,

       CE_LOOKUPS CEL

WHERE  CE_PR.RECON_FLAG = 'N'

AND    CE_PR.PAYMENT_STATUS = 'PAID'

AND    CE_BA.BANK_ACCOUNT_ID = CE_PR.PAYER_BANK_ACCOUNT_ID                                     

AND    XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE

AND    CTTM.PAY_PAYMENT_TYPE_ID(+) = CE_PR.PAYMENT_TYPE_ID

and    nvl(CTTM.active_flag (+), 'Y') = 'Y'                 

AND    CE_BA.ASSET_CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID

--AND  CE_BA.ASSET_CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID

--AND  ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID  

AND   CE_BA.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID

AND    CEL.lookup_type = 'CE_UNRECON_SOURCE'

AND    CEL.lookup_code = 'PR'

and XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID

   and xah.application_id = 801

   and xah.application_id = xal.application_id

   and xal.gl_sl_link_table = GLIR.GL_SL_LINK_table

   AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID

AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID

AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM

and GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID

AND GJB.STATUS = 'P'

AND    GJL.LEDGER_ID = GL.LEDGER_ID

AND    GL.LEDGER_CATEGORY_CODE    = 'PRIMARY'

and xah.gl_transfer_status_code = 'Y'

and pe.event_id = xah.event_id

and exists

    (select 1

        from pay_payroll_rel_actions ra,

                pay_action_interlocks int1,

                pay_action_interlocks int2,

                        pay_action_interlocks int3

     where ce_pr.pre_payment_id = ra.pre_payment_id

         and int1.locked_action_id = ra.payroll_rel_action_id

         and int2.locked_action_id = int1.locking_action_id

         and int2.locking_action_id = pe.payroll_rel_action_id

         and int3.locked_action_id = ra.payroll_rel_action_id

         and ce_pr.payroll_rel_action_id = int3.locking_action_id

     )