AR to Gl And XLA ...

/* Formatted on 5/8/2013 12:12:24 PM (QP5 v5.114.809.3010) */
SELECT   GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
         GJH.NAME JV_NAME,
         GJH.JE_CATEGORY,
         GJH.JE_SOURCE,
         GJH.PERIOD_NAME,
         NVL (XAL.ACCOUNTED_CR, 0) GL_CR,
         NVL (XAL.ACCOUNTED_DR, 0) GL_DR,
         GJL.DESCRIPTION JV_LINE_DESCRIPTION,
         XAH.EVENT_TYPE_CODE,
         XAH.DESCRIPTION SLA_DESCRIPTION,
         XAL.AE_LINE_NUM,
         XAL.ACCOUNTING_DATE GL_DATE,
         (SELECT   AC.CUSTOMER_NAME
            FROM   AR_CUSTOMERS AC
           WHERE   AC.CUSTOMER_ID = XAL.PARTY_ID)
            CUSTOMER_NAME,
         (SELECT   ACR.RECEIPT_NUMBER
            FROM   AR_CASH_RECEIPTS_ALL ACR
           WHERE   ACR.DOC_SEQUENCE_ID = XAH.DOC_SEQUENCE_ID
                   AND ACR.DOC_SEQUENCE_VALUE = XAH.DOC_SEQUENCE_VALUE)
            RECEIPT_NUMBER,
         (SELECT   ACR.RECEIPT_DATE
            FROM   AR_CASH_RECEIPTS_ALL ACR
           WHERE   ACR.DOC_SEQUENCE_ID = XAH.DOC_SEQUENCE_ID
                   AND ACR.DOC_SEQUENCE_VALUE = XAH.DOC_SEQUENCE_VALUE)
            RECEIPT_DATE,
         (SELECT   ACR.DOC_SEQUENCE_VALUE
            FROM   AR_CASH_RECEIPTS_ALL ACR
           WHERE   ACR.DOC_SEQUENCE_ID = XAH.DOC_SEQUENCE_ID
                   AND ACR.DOC_SEQUENCE_VALUE = XAH.DOC_SEQUENCE_VALUE)
            VOUCHER_NUMBER,
         (SELECT   ACR.CREATION_DATE
            FROM   AR_CASH_RECEIPTS_ALL ACR
           WHERE   ACR.DOC_SEQUENCE_ID = XAH.DOC_SEQUENCE_ID
                   AND ACR.DOC_SEQUENCE_VALUE = XAH.DOC_SEQUENCE_VALUE)
            VOUCHER_DATE,
         DECODE (XAL.ACCOUNTED_CR, NULL, XAL.ACCOUNTED_DR, 0) RECEIPT,
         DECODE (XAL.ACCOUNTED_DR, NULL, XAL.ACCOUNTED_CR, 0) PAYMENT
  FROM   GL_JE_BATCHES GJB,
         GL_JE_HEADERS GJH,
         GL_JE_LINES GJL,
         GL_CODE_COMBINATIONS GCC,
         GL_IMPORT_REFERENCES GIR,
         XLA_AE_LINES XAL,
         XLA_AE_HEADERS XAH,
         XLA.XLA_TRANSACTION_ENTITIES XTE
 WHERE       GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
         AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
         AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
         AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
         AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
         AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
         AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
         AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
         AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
         AND XTE.ENTITY_ID = XAH.ENTITY_ID
         AND GJL.STATUS = 'P'
         AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
         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.JE_SOURCE = 'Receivables'
UNION ALL
---------------- Manual -----------------------
SELECT   GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
         GJH.NAME JV_NAME,
         GJH.JE_CATEGORY,
         GJH.JE_SOURCE,
         GJH.PERIOD_NAME,
         NVL (GJL.ACCOUNTED_DR, 0) ACCOUNTED_DR,
         NVL (GJL.ACCOUNTED_CR, 0) ACCOUNTED_CR,
         gjl.description jv_line_description,
         '' EVENT_TYPE_CODE,
         '' SLA_DESCRIPTION,
         NULL AE_LINE_NUM,
         GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
         '' VENDOR_NAME,
         '' CHECK_NUMBER,
         NULL CHECK_DATE,
         NULL VOUCHER_NUMBER,
         NULL VOUCHER_DATE,
         NVL (GJL.ACCOUNTED_DR, 0) RECEIPT,
         NVL (GJL.ACCOUNTED_CR, 0) PAYMENT
  FROM   GL_JE_BATCHES GJB,
         GL_JE_HEADERS GJH,
         GL_JE_LINES GJL,
         GL_CODE_COMBINATIONS GCC
 WHERE       GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
         AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
         AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
         AND GJL.STATUS = 'P'
         AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
         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.JE_SOURCE = 'Manual'
UNION ALL
-----ALL OTHER SOURCES OTHER THAN ABOVE----------
SELECT   GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
         GJH.NAME JV_NAME,
         GJH.JE_CATEGORY,
         GJH.JE_SOURCE,
         GJH.PERIOD_NAME,
         NVL (XAL.ACCOUNTED_CR, 0) GL_CR,
         NVL (XAL.ACCOUNTED_DR, 0) GL_DR,
         GJL.DESCRIPTION JV_LINE_DESCRIPTION,
         XAH.EVENT_TYPE_CODE,
         XAH.DESCRIPTION SLA_DESCRIPTION,
         XAL.AE_LINE_NUM,
         XAL.ACCOUNTING_DATE GL_DATE,
         '' VENDOR_NAME,
         '' CHECK_NUMBER,
         NULL CHECK_DATE,
         NULL VOUCHER_NUMBER,
         NULL VOUCHER_DATE,
         DECODE (XAL.ACCOUNTED_CR, NULL, XAL.ACCOUNTED_DR, 0) RECEIPT,
         DECODE (XAL.ACCOUNTED_DR, NULL, XAL.ACCOUNTED_CR, 0) PAYMENT
  FROM   XLA_AE_HEADERS XAH,
         XLA_AE_LINES XAL,
         GL_JE_LINES GJL,
         GL_IMPORT_REFERENCES GIR,
         GL_JE_HEADERS GJH,
         GL_CODE_COMBINATIONS GCC
 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 GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
         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 NOT IN
                  ('Receivables', 'Payables', 'Cash Management')