SLA or XLA(AP-GL,RECEIPT-GL - (SubledgerAccounting)

SELECT   DISTINCT
         PS.PERIOD_NAME,
         GJL.REFERENCE_2,
         TO_CHAR (NULL) PO_ORDER_NUMBER,
         TO_CHAR (NULL) "RECEIPT NUMBER",
         TO_DATE (NULL) "RECEIPT DATE",
         TO_CHAR (NULL) "INVOICE NUMBER",
         TO_DATE (NULL) "INVOICE DATE",
         TO_CHAR (NULL) "VENDOR NAME",
         TO_CHAR (NULL) "VENDOR NUMBER",
         JE_SOURCE "SOURCE",
         GJH.LEDGER_ID "SET_OF_BOOKS_ID",
         FND_FLEX_EXT.GET_SEGS ('SQLGL',
                                'GL#',
                                GSCV.CHART_OF_ACCOUNTS_ID,
                                GSCV.CODE_COMBINATION_ID)
            SEGMENT,
         FND_FLEX_EXT.GET_SEGS ('SQLGL',
                                'GL#',
                                GSCV.CHART_OF_ACCOUNTS_ID,
                                GSCV.CODE_COMBINATION_ID)
            SEGMENT2,
         GJH.CURRENCY_CODE,
         GJL.ENTERED_DR,
         GJL.ENTERED_CR,
         TO_NUMBER(DECODE (LR.RELATIONSHIP_TYPE_CODE,
                           'BALANCE', NULL,
                           GJL.ACCOUNTED_DR))
            ACCOUNTED_DR,
         TO_NUMBER(DECODE (LR.RELATIONSHIP_TYPE_CODE,
                           'BALANCE', NULL,
                           GJL.ACCOUNTED_CR))
            ACCOUNTED_CR,
         GJH.JE_HEADER_ID,
         GJL.JE_LINE_NUM,
         GJL.CODE_COMBINATION_ID,
         GJH.DOC_SEQUENCE_VALUE "VOCHER NUMBER",
         GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
         GJL.ATTRIBUTE1 "STAFF NUMBER",
         GJL.ATTRIBUTE2 "PROJECT",
         GJL.ATTRIBUTE3 "CUSTOMER NUM/NAME",
         GJL.DESCRIPTION DESCRIPTION
  FROM   GL_JE_LINES GJL,
         GL_JE_HEADERS GJH,
         GL_SUMMARY_COMBINATIONS_V GSCV,
         GL_LEDGERS,
         GL_PERIOD_STATUSES PS,
         GL_JE_BATCHES B,
         GL_LEDGER_RELATIONSHIPS LR
 WHERE       GSCV.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
         AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
         AND GJL.PERIOD_NAME = PS.PERIOD_NAME
         AND PS.LEDGER_ID = GJL.LEDGER_ID
         AND B.JE_BATCH_ID = GJH.JE_BATCH_ID
         AND GJH.LEDGER_ID = LR.SOURCE_LEDGER_ID
         AND LR.SOURCE_LEDGER_ID = LR.TARGET_LEDGER_ID
         AND B.ACTUAL_FLAG = 'A'
         AND B.STATUS || '' = 'P'
         AND GJH.CURRENCY_CODE != 'STAT'
         AND JE_SOURCE NOT IN
                  ('PAYABLES',
                   'COST MANAGEMENT',
                   'CONSOLIDATION',
                   'RECEIVABLES')
         AND GJH.LEDGER_ID = GL_LEDGERS.LEDGER_ID
         AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
              AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO))
         AND (NVL (GSCV.SEGMENT5, 0) >= :P_ACCOUNT_FROM
              AND (NVL (GSCV.SEGMENT5, 0) <= :P_ACCOUNT_TO))
         AND (NVL (GSCV.SEGMENT1, 0) >= :P_COMPANY_FROM
              AND (NVL (GSCV.SEGMENT1, 0) <= :P_COMPANY_TO))
         AND GJH.LEDGER_ID NOT IN (1014, 1015, 1016)
         AND GJH.CURRENCY_CODE =
               DECODE (
                  :P_CURRENCY_CODE,
                  'INR',
                  'INR',
                  'ALL',
                  GJH.CURRENCY_CODE,
                  'NON INR',
                  DECODE (GJH.CURRENCY_CODE, 'INR', '###', GJH.CURRENCY_CODE)
               )
UNION ALL
SELECT   DISTINCT B.PERIOD_NAME,
                  D.REFERENCE_2,
                  AAA.PO_ORDER_NUMBER,
                  AAA.RCV_RECEIPT_NUM "RECEIPT NUMBER",
                  AAA.TRX_DATE "RECEIPT DATE",
                  AAA.TRX_NUMBER_DISPLAYED "INVOICE NUMBER",
                  AAA.ACCOUNTING_DATE "INVOICE DATE",
                  AAA.THIRD_PARTY_NAME "VENDOR NAME",
                  AAA.THIRD_PARTY_NUMBER "VENDOR NUMBER",
                  B.JE_SOURCE "SOURCE",
                  B.LEDGER_ID "SET_OF_BOOKS_ID",
                  FND_FLEX_EXT.GET_SEGS ('SQLGL',
                                         'GL#',
                                         CCC.CHART_OF_ACCOUNTS_ID,
                                         CCC.CODE_COMBINATION_ID)
                     SEGMENT,
                  FND_FLEX_EXT.GET_SEGS ('SQLGL',
                                         'GL#',
                                         CCC.CHART_OF_ACCOUNTS_ID,
                                         CCC.CODE_COMBINATION_ID)
                     SEGMENT2,
                  AAA.CURRENCY_CODE,
                  AAA.ENTERED_DR,
                  AAA.ENTERED_CR,
                  AAA.ACCOUNTED_DR,
                  AAA.ACCOUNTED_CR,
                  AAA.JE_HEADER_ID,
                  AAA.JE_LINE_NUM,
                  AAA.CODE_COMBINATION_ID,
                  AAA.DOC_SEQUENCE_VALUE "VOCHER NUMBER",
                  AAA.GL_DATE "GL_DATE",
                  AAA.ATTRIBUTE1 "STAFF NUMBER",
                  AAA.ATTRIBUTE2 "PROJECT",
                  AAA.ATTRIBUTE3 "CUSTOMER NUM/NAME",
                  AAA.DESCRIPTION "DESCRIPTION"
  FROM   (SELECT   DISTINCT POH.SEGMENT1 PO_ORDER_NUMBER,
                            RSH.RECEIPT_NUM RCV_RECEIPT_NUM,
                            RCT.TRANSACTION_DATE TRX_DATE,
                            API.INVOICE_NUM TRX_NUMBER_DISPLAYED,
                            RRS.ACCOUNTING_DATE ACCOUNTING_DATE,
                            POV.VENDOR_NAME THIRD_PARTY_NAME,
                            POV.SEGMENT1 THIRD_PARTY_NUMBER,
                            RRS.CURRENCY_CODE,
                            RRS.ENTERED_DR,
                            RRS.ENTERED_CR,
                            RRS.ACCOUNTED_DR,
                            RRS.ACCOUNTED_CR,
                            R.JE_HEADER_ID,
                            R.JE_LINE_NUM,
                            RRS.CODE_COMBINATION_ID,
                            201 APPLICATION_ID,
                            TO_DATE (NULL) GL_DATE,
                            TO_NUMBER (NULL) DOC_SEQUENCE_VALUE,
                            TO_CHAR (NULL) DESCRIPTION,
                            TO_CHAR (NULL) ATTRIBUTE1,
                            TO_CHAR (NULL) ATTRIBUTE2,
                            TO_CHAR (NULL) ATTRIBUTE3
            FROM   PO_HEADERS_ALL POH,
                   PO_LINES_ALL PL,
                   PO_DISTRIBUTIONS_ALL POD,
                   RCV_SHIPMENT_LINES RSL,
                   RCV_SHIPMENT_HEADERS RSH,
                   RCV_TRANSACTIONS RCT,
                   RCV_RECEIVING_SUB_LEDGER RRS,
                   AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
                   AP_INVOICES_ALL API,
                   XLA_AE_HEADERS XAH,
                   XLA_AE_LINES XAL,
                   GL_IMPORT_REFERENCES R,
                   AP_SUPPLIERS POV
           WHERE       1 = 1
                   AND POH.PO_HEADER_ID = PL.PO_HEADER_ID
                   AND POH.PO_HEADER_ID = POD.PO_HEADER_ID
                   AND POH.PO_HEADER_ID = RSL.PO_HEADER_ID(+)
                   AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
                   AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID(+)
                   AND POD.PO_DISTRIBUTION_ID = AIDA.PO_DISTRIBUTION_ID(+)
                   AND API.INVOICE_ID(+) = AIDA.INVOICE_ID
                   AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3
                   AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
                   AND AIDA.ACCOUNTING_EVENT_ID = XAH.EVENT_ID(+)
                   AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID(+)
                   AND XAL.GL_SL_LINK_ID = R.GL_SL_LINK_ID(+)
                   AND R.REFERENCE_8 = XAL.AE_LINE_NUM
                   AND POH.VENDOR_ID = POV.VENDOR_ID
                   AND AIDA.LINE_TYPE_LOOKUP_CODE(+) = 'ACCRUAL'
                   AND RRS.ACCOUNTING_LINE_TYPE = 'ACCRUAL'
                   AND XAL.ACCOUNTING_CLASS_CODE(+) = 'LIABILITY'
                   AND POD.DESTINATION_TYPE_CODE = 'EXPENSE'
                   AND EXISTS
                         (SELECT   1
                            FROM   AP_INVOICE_DISTRIBUTIONS_ALL AID,
                                   AP_INVOICES_ALL AI
                           WHERE   AID.PO_DISTRIBUTION_ID =
                                      POD.PO_DISTRIBUTION_ID
                                   AND AI.INVOICE_ID = AID.INVOICE_ID
                                   AND AI.VENDOR_ID = POV.VENDOR_ID)) AAA,
         GL_JE_HEADERS B,
         GL_JE_LINES D,
         GL_JE_BATCHES E,
         GL_PERIOD_STATUSES PS,
         GL_SUMMARY_COMBINATIONS_V CCC
 WHERE       B.JE_HEADER_ID = AAA.JE_HEADER_ID
         AND D.JE_LINE_NUM = AAA.JE_LINE_NUM
         AND B.JE_HEADER_ID = D.JE_HEADER_ID
         AND D.CODE_COMBINATION_ID = CCC.CODE_COMBINATION_ID
         AND AAA.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
         AND PS.LEDGER_ID = D.LEDGER_ID
         AND B.ACTUAL_FLAG = 'A'
         AND B.STATUS || '' = 'P'
         AND B.CURRENCY_CODE != 'STAT'
         AND JE_SOURCE NOT IN ('PAYABLES', 'PURCHASING', 'CONSOLIDATION')
         AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
              AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO))
         AND (NVL (CCC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
              AND (NVL (CCC.SEGMENT5, 0) <= :P_ACCOUNT_TO))
         AND (NVL (CCC.SEGMENT1, 0) >= :P_COMPANY_FROM
              AND (NVL (CCC.SEGMENT1, 0) <= :P_COMPANY_TO))
         AND B.LEDGER_ID NOT IN (1014, 1015, 1016)
         AND B.CURRENCY_CODE =
               DECODE (
                  :P_CURRENCY_CODE,
                  'INR',
                  'INR',
                  'ALL',
                  B.CURRENCY_CODE,
                  'NON INR',
                  DECODE (B.CURRENCY_CODE, 'INR', '###', B.CURRENCY_CODE)
               )
UNION ALL
SELECT   GPS.PERIOD_NAME,
         L.REFERENCE_2,
         PHA.SEGMENT1 PO_ORDER_NUMBER,
         RSH.RECEIPT_NUM "RECEIPT NUMBER",
         RT.TRANSACTION_DATE "RECEIPT DATE",
         NULL "INVOICE NUMBER",
         NULL "INVOICE DATE",
         PV.VENDOR_NAME "VENDOR NAME",
         PV.SEGMENT1 "VENDOR NUMBER",
         H.JE_SOURCE "SOURCE",
         H.LEDGER_ID "SET_OF_BOOKS_ID",
         GCC.CONCATENATED_SEGMENTS SEGMENT,
         GCC.CONCATENATED_SEGMENTS SEGMENT2,
         PHA.CURRENCY_CODE,
         XAL.ENTERED_DR,
         XAL.ENTERED_CR,
         XAL.ACCOUNTED_DR,
         XAL.ACCOUNTED_CR,
         GIR.JE_HEADER_ID,
         GIR.JE_LINE_NUM,
         RRSL.CODE_COMBINATION_ID,
         TO_NUMBER (NULL) "VOCHER NUMBER",
         TO_DATE (NULL) GL_DATE,
         TO_CHAR (NULL) "STAFF NUMBER",
         TO_CHAR (NULL) "PROJECT",
         TO_CHAR (NULL) "CUSTOMER NUM/NAME",
         RSL.ITEM_DESCRIPTION "DESCRIPTION"
  FROM   GL.GL_JE_HEADERS H,
         GL.GL_JE_LINES L,
         GL_CODE_COMBINATIONS_KFV GCC,
         GL.GL_IMPORT_REFERENCES GIR,
         APPS.XLA_AE_LINES XAL,
         APPS.XLA_DISTRIBUTION_LINKS XDL,
         PO.RCV_RECEIVING_SUB_LEDGER RRSL,
         PO.RCV_TRANSACTIONS RT,
         PO.PO_HEADERS_ALL PHA,
         PO.PO_LINES_ALL PLA,
         PO.PO_LINE_LOCATIONS_ALL PLLA,
         APPS.PO_VENDORS PV,
         RCV_SHIPMENT_HEADERS RSH,
         RCV_SHIPMENT_LINES RSL,
         GL_PERIOD_STATUSES GPS
 WHERE       1 = 1
         AND H.JE_HEADER_ID = L.JE_HEADER_ID
         AND L.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
         AND (NVL (GCC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
              AND (NVL (GCC.SEGMENT5, 0) <= :P_ACCOUNT_TO))
         AND (NVL (GCC.SEGMENT1, 0) >= :P_COMPANY_FROM
              AND (NVL (GCC.SEGMENT1, 0) <= :P_COMPANY_TO))
         AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
         AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
         AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
         AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
         AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
         AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
         AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
         AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
         AND RT.PO_HEADER_ID = PLA.PO_HEADER_ID
         AND RT.PO_LINE_ID = PLA.PO_LINE_ID
         AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID
         AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
         AND PHA.VENDOR_ID = PV.VENDOR_ID
         AND H.JE_SOURCE = 'COST MANAGEMENT'
         AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
         AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
         AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
         AND L.PERIOD_NAME = GPS.PERIOD_NAME
         AND GPS.LEDGER_ID = L.LEDGER_ID
         AND GPS.APPLICATION_ID = 101
         AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
              AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO))
         AND H.LEDGER_ID NOT IN (1014, 1015, 1016)
         AND H.CURRENCY_CODE != 'STAT'
         AND H.CURRENCY_CODE =
               DECODE (
                  :P_CURRENCY_CODE,
                  'INR',
                  'INR',
                  'ALL',
                  H.CURRENCY_CODE,
                  'NON INR',
                  DECODE (H.CURRENCY_CODE, 'INR', '###', H.CURRENCY_CODE)
               )
UNION ALL
SELECT   GPS.PERIOD_NAME,
         GJL.REFERENCE_2,
         (SELECT   DISTINCT PHA.SEGMENT1
            FROM   AP_INVOICE_LINES_ALL AILLA, PO_HEADERS_ALL PHA
           WHERE   AILLA.INVOICE_ID = AIA.INVOICE_ID
                   AND AILLA.PO_HEADER_ID = PHA.PO_HEADER_ID)
            PO_ORDER_NUMBER,
         (SELECT   DISTINCT RSH.RECEIPT_NUM
            FROM   RCV_SHIPMENT_HEADERS RSH,
                   RCV_SHIPMENT_LINES RSL,
                   RCV_TRANSACTIONS RT,
                   AP_INVOICE_LINES_ALL AILA
           WHERE       RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
                   AND RSL.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
                   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
                   AND RT.TRANSACTION_ID =
                         (SELECT   DISTINCT RCV_TRANSACTION_ID
                            FROM   AP_INVOICE_LINES_ALL AILA
                           WHERE       AILA.INVOICE_ID = AIA.INVOICE_ID
                                   AND ROWNUM = 1
                                   AND RCV_TRANSACTION_ID IS NOT NULL))
            "RECEIPT NUMBER",
         (SELECT   DISTINCT RT.TRANSACTION_DATE
            FROM   RCV_SHIPMENT_HEADERS RSH,
                   RCV_SHIPMENT_LINES RSL,
                   RCV_TRANSACTIONS RT,
                   AP_INVOICE_LINES_ALL AILA
           WHERE       RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
                   AND RSL.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
                   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
                   AND RT.TRANSACTION_ID =
                         (SELECT   DISTINCT RCV_TRANSACTION_ID
                            FROM   AP_INVOICE_LINES_ALL AILA
                           WHERE       AILA.INVOICE_ID = AIA.INVOICE_ID
                                   AND ROWNUM = 1
                                   AND RCV_TRANSACTION_ID IS NOT NULL))
            "RECEIPT DATE",
         AIA.INVOICE_NUM "INVOICE NUMBER",
         AIA.GL_DATE "INVOICE DATE",
         APS.VENDOR_NAME "VENDOR NAME",
         APS.SEGMENT1 "VENDOR NUMBER",
         GJH.JE_SOURCE "SOURCE",
         GJH.LEDGER_ID "SET_OF_BOOKS_ID",
         CC.CONCATENATED_SEGMENTS SEGMENT,
         CC.CONCATENATED_SEGMENTS SEGMENT2,
         AIA.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
         XAL.ENTERED_DR,
         XAL.ENTERED_CR,
         XAL.ACCOUNTED_DR,
         XAL.ACCOUNTED_CR,
         GIR.JE_HEADER_ID,
         GIR.JE_LINE_NUM,
         CC.CODE_COMBINATION_ID,
         AIA.DOC_SEQUENCE_VALUE "VOCHER NUMBER",
         AIA.GL_DATE "GL_DATE",
         NULL "STAFF NUMBER",
         NULL "PROJECT",
         NULL "CUSTOMER NUM/NAME",
         XAL.DESCRIPTION "DESCRIPTION"
  FROM   APPS.AP_INVOICES_ALL AIA,
         XLA.XLA_TRANSACTION_ENTITIES XTE,
         APPS.XLA_EVENTS XEV,
         APPS.XLA_AE_HEADERS XAH,
         APPS.XLA_AE_LINES XAL,
         APPS.GL_IMPORT_REFERENCES GIR,
         APPS.GL_JE_HEADERS GJH,
         APPS.GL_JE_LINES GJL,
         APPS.GL_CODE_COMBINATIONS_KFV CC,
         APPS.AP_SUPPLIERS APS,
         GL_PERIOD_STATUSES GPS
 WHERE       AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
         AND XEV.ENTITY_ID = XTE.ENTITY_ID
         AND XAH.ENTITY_ID = XTE.ENTITY_ID
         AND XAH.EVENT_ID = XEV.EVENT_ID
         AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
         AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
         AND GJH.STATUS = 'P'
         AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
         AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
         AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
         AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
         AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
         AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
         AND CC.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID
         AND CC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
         AND AIA.VENDOR_ID = APS.VENDOR_ID
         AND GJH.STATUS = 'P'
         AND GJH.ACTUAL_FLAG = 'A'
         AND GJH.LEDGER_ID NOT IN (1014, 1015, 1016)
         AND GJH.LEDGER_ID = GPS.LEDGER_ID
         AND 101 = GPS.APPLICATION_ID
         AND GPS.SET_OF_BOOKS_ID = GJH.LEDGER_ID
         AND GJH.JE_SOURCE = 'PAYABLES'
         AND GJH.LEDGER_ID NOT IN (1014, 1015, 1016)
         AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
         AND (NVL (XAL.ACCOUNTED_CR, 0) <> 0
              OR NVL (XAL.ACCOUNTED_DR, 0) <> 0)
         AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
         AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
              AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO))
         AND (NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
              AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO))
         AND (NVL (CC.SEGMENT1, 0) >= :P_COMPANY_FROM
              AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO))
         AND AIA.PAYMENT_CURRENCY_CODE =
               DECODE (
                  :P_CURRENCY_CODE,
                  'INR',
                  'INR',
                  'ALL',
                  AIA.PAYMENT_CURRENCY_CODE,
                  'NON INR',
                  DECODE (AIA.PAYMENT_CURRENCY_CODE,
                          'INR', '###',
                          AIA.PAYMENT_CURRENCY_CODE)
               )
UNION ALL
SELECT   GJH.PERIOD_NAME,
         NULL REFERENCE_2,
         NULL PO_ORDER_NUMBER,
         NULL "RECEIPT NUMBER",
         NULL "RECEIPT DATE",
         ENT.TRANSACTION_NUMBER "INVOICE NUMBER",
         AIA.CHECK_DATE "INVOICE DATE",
         AV.VENDOR_NAME "VENDOR NAME",
         AV.SEGMENT1 "VENDOR NUMBER",
         GJH.JE_SOURCE "SOURCE",
         GJH.LEDGER_ID "SET_OF_BOOKS_ID",
         GCCK.CONCATENATED_SEGMENTS SEGMENT,
         GCCK.CONCATENATED_SEGMENTS SEGMENT21,
         AIA.CURRENCY_CODE CURRENCY_CODE,
         AEL.ENTERED_DR,
         AEL.ENTERED_CR,
         AEL.ACCOUNTED_DR,
         AEL.ACCOUNTED_CR,
         GIR.JE_HEADER_ID,
         GIR.JE_LINE_NUM,
         GCCK.CODE_COMBINATION_ID,
         GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER",
         AEH.ACCOUNTING_DATE "GL_DATE",
         NULL "STAFF NUMBER",
         NULL "PROJECT",
         NULL "CUSTOMER NUM/NAME",
         AEL.DESCRIPTION "DESCRIPTION"
  FROM   XLA_AE_HEADERS AEH,
         XLA_AE_LINES AEL,
         XLA_EVENTS XLE,
         XLA_EVENT_TYPES_TL XET,
         XLA_TRANSACTION_ENTITIES ENT,
         GL_LEDGERS GLG,
         GL_PERIODS GLP,
         XLA_SUBLEDGERS XLS,
         GL_CODE_COMBINATIONS_KFV GCCK,
         GL_IMPORT_REFERENCES GIR,
         GL_JE_LINES GJL,
         GL_JE_HEADERS GJH,
         GL_JE_BATCHES GJB,
         AP_CHECKS_ALL AIA,
         GL_PERIOD_STATUSES GPS,
         AP_SUPPLIERS AV
 WHERE       1 = 1
         AND AEH.LEDGER_ID = GLG.LEDGER_ID
         AND AEL.APPLICATION_ID = AEH.APPLICATION_ID
         AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
         AND XLE.APPLICATION_ID = AEH.APPLICATION_ID
         AND XLE.EVENT_ID = AEH.EVENT_ID
         AND XET.APPLICATION_ID = XLE.APPLICATION_ID
         AND XET.EVENT_TYPE_CODE = XLE.EVENT_TYPE_CODE
         AND XET.LANGUAGE = USERENV ('LANG')
         AND ENT.APPLICATION_ID = AEH.APPLICATION_ID
         AND ENT.ENTITY_ID = AEH.ENTITY_ID
         AND GLP.PERIOD_NAME = AEH.PERIOD_NAME
         AND GLP.PERIOD_SET_NAME = GLG.PERIOD_SET_NAME
         AND XLS.APPLICATION_ID = AEH.APPLICATION_ID
         AND GCCK.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
         AND AEH.ACCOUNTING_ENTRY_STATUS_CODE <> 'N'
         AND AEH.APPLICATION_ID = 200
         AND AEH.BALANCE_TYPE_CODE = 'A'
         AND (NVL (AEL.ACCOUNTED_CR, 0) <> 0
              OR NVL (AEL.ACCOUNTED_DR, 0) <> 0)
         AND GIR.GL_SL_LINK_ID(+) = AEL.GL_SL_LINK_ID
         AND GIR.GL_SL_LINK_TABLE(+) = AEL.GL_SL_LINK_TABLE
         AND GJL.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
         AND GJL.JE_LINE_NUM(+) = GIR.JE_LINE_NUM
         AND GJH.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
         AND GJB.JE_BATCH_ID(+) = GIR.JE_BATCH_ID
         AND DECODE (GJH.JE_HEADER_ID, NULL, 'Y', GJH.JE_FROM_SLA_FLAG) IN
                  ('U', 'Y')
         AND ENT.SOURCE_ID_INT_1 = AIA.CHECK_ID
         AND AIA.VENDOR_ID = AV.VENDOR_ID
         AND GJH.JE_SOURCE = 'PAYABLES'
         AND AEH.JE_CATEGORY_NAME = 'PAYMENTS'
         AND GJH.LEDGER_ID = GPS.LEDGER_ID
         AND 101 = GPS.APPLICATION_ID
         AND GPS.SET_OF_BOOKS_ID = GJH.LEDGER_ID
         AND GJH.LEDGER_ID NOT IN (1014, 1015, 1016)
         AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
         AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
              AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO))
         AND (NVL (GCCK.SEGMENT5, 0) >= :P_ACCOUNT_FROM
              AND (NVL (GCCK.SEGMENT5, 0) <= :P_ACCOUNT_TO))
         AND (NVL (GCCK.SEGMENT1, 0) >= :P_COMPANY_FROM
              AND (NVL (GCCK.SEGMENT1, 0) <= :P_COMPANY_TO))
         AND AIA.CURRENCY_CODE =
               DECODE (
                  :P_CURRENCY_CODE,
                  'INR',
                  'INR',
                  'ALL',
                  AIA.CURRENCY_CODE,
                  'NON INR',
                  DECODE (AIA.CURRENCY_CODE, 'INR', '###', AIA.CURRENCY_CODE)
               )
UNION ALL
SELECT   GJH.PERIOD_NAME,
         NULL REFERENCE_2,
         NULL PO_ORDER_NUMBER,
         CASE
            WHEN (GJH.JE_CATEGORY IN ('RECEIPTS', 'MISC RECEIPTS'))
            THEN
               XTE.TRANSACTION_NUMBER
            ELSE
               NULL
         END
            "RECEIPT NUMBER",
         CASE
            WHEN (GJH.JE_CATEGORY IN ('RECEIPTS', 'MISC RECEIPTS'))
            THEN
               XAH.ACCOUNTING_DATE
            ELSE
               NULL
         END
            "RECEIPT DATE",
         CASE
            WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS', 'MISC RECEIPTS'))
            THEN
               XTE.TRANSACTION_NUMBER
            ELSE
               NULL
         END
            "INVOICE NUMBER",
         CASE
            WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS', 'MISC RECEIPTS'))
            THEN
               XAH.ACCOUNTING_DATE
            ELSE
               NULL
         END
            "INVOICE DATE",
         CASE
            WHEN XAL.PARTY_TYPE_CODE = 'S'
            THEN
               (SELECT      APS.SEGMENT1
                         || '|'
                         || APS.VENDOR_NAME
                         || '|'
                         || HZP.JGZZ_FISCAL_CODE
                         || '|'
                         || HZP.TAX_REFERENCE
                         || '|'
                         || HPS.PARTY_SITE_NUMBER
                         || '|'
                         || HPS.PARTY_SITE_NAME
                         || '|'
                         || NULL
                  FROM   AP_SUPPLIERS APS,
                         AP_SUPPLIER_SITES_ALL APSS,
                         HZ_PARTIES HZP,
                         HZ_PARTY_SITES HPS,
                         XLA_AE_LINES AEL2
                 WHERE       APS.VENDOR_ID = AEL2.PARTY_ID
                         AND HZP.PARTY_ID = APS.PARTY_ID
                         AND APSS.VENDOR_SITE_ID(+) = AEL2.PARTY_SITE_ID
                         AND HPS.PARTY_SITE_ID(+) = APSS.PARTY_SITE_ID
                         AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
                         AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
                         AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM)
            WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL)
            THEN
               (SELECT                           
                       HZP.PARTY_NAME
                  FROM   HZ_CUST_ACCOUNTS HCA,
                         HZ_CUST_ACCT_SITES_ALL HCAS,
                         HZ_CUST_SITE_USES_ALL HZCU,
                         HZ_PARTIES HZP,
                         HZ_PARTY_SITES HPS,
                         XLA_AE_LINES AEL2
                 WHERE       HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
                         AND HZP.PARTY_ID = HCA.PARTY_ID
                         AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
                         AND HCAS.CUST_ACCT_SITE_ID(+) =
                               HZCU.CUST_ACCT_SITE_ID
                         AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
                         AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
                         AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
                         AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM)
            ELSE
               NULL
         END
            "VENDOR NAME",
         CASE
            WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL)
            THEN
               (SELECT   HCA.ACCOUNT_NUMBER
                  FROM   HZ_CUST_ACCOUNTS HCA,
                         HZ_CUST_ACCT_SITES_ALL HCAS,
                         HZ_CUST_SITE_USES_ALL HZCU,
                         HZ_PARTIES HZP,
                         HZ_PARTY_SITES HPS,
                         XLA_AE_LINES AEL2
                 WHERE       HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
                         AND HZP.PARTY_ID = HCA.PARTY_ID
                         AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
                         AND HCAS.CUST_ACCT_SITE_ID(+) =
                               HZCU.CUST_ACCT_SITE_ID
                         AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
                         AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
                         AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
                         AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM)
            ELSE
               NULL
         END
            "VENDOR NUMBER",
         GJH.JE_SOURCE "SOURCE",
         GJH.LEDGER_ID "SET_OF_BOOKS_ID",
         CC.CONCATENATED_SEGMENTS SEGMENT,
         CC.CONCATENATED_SEGMENTS SEGMENT21,
         XAL.CURRENCY_CODE CURRENCY_CODE,
         XAL.ENTERED_DR,
         XAL.ENTERED_CR,
         XAL.ACCOUNTED_DR,
         XAL.ACCOUNTED_CR,
         GIR.JE_HEADER_ID,
         GIR.JE_LINE_NUM,
         CC.CODE_COMBINATION_ID,
         GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER",
         XAH.ACCOUNTING_DATE "GL_DATE",
         NULL "STAFF NUMBER",
         NULL "PROJECT",
         NULL "CUSTOMER NUM/NAME",
         XAH.DESCRIPTION "DESCRIPTION"
  FROM   XLA.XLA_TRANSACTION_ENTITIES XTE,
         APPS.XLA_EVENTS XEV,
         APPS.XLA_AE_HEADERS XAH,
         APPS.XLA_AE_LINES XAL,
         APPS.GL_IMPORT_REFERENCES GIR,
         APPS.GL_JE_HEADERS GJH,
         APPS.GL_JE_LINES GJL,
         APPS.GL_CODE_COMBINATIONS_KFV CC,
         GL_PERIOD_STATUSES GPS
 WHERE       1 = 1                     
         AND XEV.ENTITY_ID = XTE.ENTITY_ID
         AND XAH.ENTITY_ID = XTE.ENTITY_ID
         AND XAH.EVENT_ID = XEV.EVENT_ID
         AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
         AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
         AND GJH.STATUS = 'P'
         AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
         AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
         AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
         AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
         AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
         AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
         AND CC.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID
         AND CC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
         AND GJH.STATUS = 'P'
         AND GJH.ACTUAL_FLAG = 'A'
         AND GJH.LEDGER_ID NOT IN (1014, 1015, 1016)
         AND GJH.LEDGER_ID = GPS.LEDGER_ID
         AND 101 = GPS.APPLICATION_ID
         AND GPS.SET_OF_BOOKS_ID = GJH.LEDGER_ID
         AND GJH.JE_SOURCE = 'RECEIVABLES'
         AND GJH.LEDGER_ID NOT IN (1014, 1015, 1016)
         AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
         AND (NVL (XAL.ACCOUNTED_CR, 0) <> 0
              OR NVL (XAL.ACCOUNTED_DR, 0) <> 0)
         AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
              AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO))
         AND (NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
              AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO))
         AND (NVL (CC.SEGMENT1, 0) >= :P_COMPANY_FROM
              AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO))
         AND XAL.CURRENCY_CODE =
               DECODE (
                  :P_CURRENCY_CODE,
                  'INR',
                  'INR',
                  'ALL',
                  XAL.CURRENCY_CODE,
                  'NON INR',
                  DECODE (XAL.CURRENCY_CODE, 'INR', '###', XAL.CURRENCY_CODE)
               );