FSQL 4

SQL QUERY FOR UNRECONCILED  PAYROLL  TRX IN ORACLE CLOUD FUSION R13

SELECT   DISTINCT 'PAYROLL'   ORIGIN ,

            CTTM.TRX_TYPE  TRX_TYPE,

            to_char(CE_PR.PAYROLL_REL_ACTION_ID) DOC_NUMBER,

            TO_CHAR(NVL(CE_PR.CLEARED_DATE, CE_PR.PAYMENT_DATE),'YYYY-MM-DD') TRX_DATE,      

            NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR,0) BOOKS,

            NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0)   ACCOUNTED_BOOKS,

             0  BANK,

             CE_PR.CHECK_NUMBER  STATEMENT_NUMBER

FROM   PAY_CE_TRANSACTIONS CE_PR,

       CE_BANK_ACCOUNTS_PAY_V            CE_BA,

   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_TRX_TYPE_MAPPING        CTTM

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 XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID

   and xah.application_id = 211

   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

     )

AND    CTTM.PAY_PAYMENT_TYPE_ID(+) = CE_PR.PAYMENT_TYPE_ID                  

AND    &p_where_pay