AP Payments to GL Link - Select statement linking JE, JE Lines to AP Payments using SLA tables/views

Following SQL statement is used to link the AP Payment/s to Journal Entry and Journal Entry Lines

Parameters:-
  1. P_FROM_DATE - Journal Header Accounted From Date
  2. P_FROM_DATE - Journal Header Accounted To Date
  3. P_CHECK_NUMBER - AP Check Number

SELECT XAH.AE_HEADER_ID,

         XTE.ENTITY_ID,

         GJH.JE_HEADER_ID,

         GJL.JE_LINE_NUM,

         GJH.DESCRIPTION JV_HEADER_DESCRIPTION,

         GJH.NAME JV_NAME,

         GJH.JE_CATEGORY,

         GJH.JE_SOURCE,

         GJH.PERIOD_NAME,

         XE.EVENT_NUMBER,

         NVL (XAL.ACCOUNTED_CR, NULL) GL_CR,

         NVL (XAL.ACCOUNTED_DR, NULL) GL_DR,

         GJL.DESCRIPTION JV_LINE_DESCRIPTION,

         XAH.EVENT_TYPE_CODE,

         XAH.DESCRIPTION SLA_DESCRIPTION,

         XAL.AE_LINE_NUM,

         XAL.ACCOUNTING_DATE GL_DATE,

         ASUP.VENDOR_NAME,

         TO_CHAR (ACA.CHECK_NUMBER) CHECK_NUMBER,

         ACA.CHECK_DATE,

         ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,

         ACA.CREATION_DATE VOUCHER_DATE,

         XAH.EVENT_ID,

         DECODE (XAH.EVENT_TYPE_CODE,

                 'PAYMENT CANCELLED', AMOUNT * NVL (EXCHANGE_RATE, 1),

                 'REFUND RECORDED', XAL.ACCOUNTED_DR,

                 0)

            RECEIPT,

         DECODE (XAH.EVENT_TYPE_CODE,

                 'PAYMENT CREATED', AMOUNT * NVL (EXCHANGE_RATE, 1),

                 0)

            PAYMENT,

         DECODE (XAL.ACCOUNTED_CR, NULL, XAL.ACCOUNTED_DR, NULL) RECEIPT,

         DECODE (XAL.ACCOUNTED_DR, NULL, XAL.ACCOUNTED_CR, NULL) PAYMENT

    FROM XLA_EVENTS XE,

         XLA_AE_HEADERS XAH,

         XLA_AE_LINES XAL,

         XLA_TRANSACTION_ENTITIES XTE,

         GL_JE_LINES GJL,

         GL_IMPORT_REFERENCES GIR,

         GL_JE_HEADERS GJH,

         GL_CODE_COMBINATIONS GCC,

         AP_SUPPLIERS ASUP,

         AP_CHECKS_ALL ACA

   WHERE     XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

         AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

         AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

         AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

         AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

         AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

         AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

         AND ASUP.VENDOR_ID(+) = XAL.PARTY_ID

         AND ACA.CHECK_ID = XTE.SOURCE_ID_INT_1

         AND XTE.ENTITY_ID = XAH.ENTITY_ID

         AND XTE.ENTITY_CODE = 'AP_PAYMENTS'

         AND XAH.EVENT_ID = XE.EVENT_ID

         AND XTE.ENTITY_ID = XE.ENTITY_ID

         AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL (

                                                           :P_FROM_DATE,

                                                           TRUNC (

                                                              GJH.DEFAULT_EFFECTIVE_DATE))

                                                    AND NVL (

                                                           :P_TO_DATE,

                                                           TRUNC (

                                                              GJH.DEFAULT_EFFECTIVE_DATE))

         AND GJH.STATUS = 'P'

         AND GJH.JE_SOURCE = 'Payables'

         AND GJH.JE_CATEGORY = 'Payments'

         AND ACA.CHECK_NUMBER = NVL(:P_CHECK_NUMBER, ACA.CHECK_NUMBER)

ORDER BY GJH.JE_HEADER_ID, GJL.JE_LINE_NUM;