Sub Ledger Break Up Query - Oracle Apps R-12

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,
  ASUP.VENDOR_NAME,
  TO_CHAR(ACA.CHECK_NUMBER),
  ACA.CHECK_DATE,
  ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,
  ACA.CREATION_DATE VOUCHER_DATE,
--  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,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,
  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.DOC_SEQUENCE_ID(+)=XAH.DOC_SEQUENCE_ID AND
  ACA.DOC_SEQUENCE_VALUE(+)=XAH.DOC_SEQUENCE_VALUE 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='Payables'
UNION ALL
------ DATA FROM CASH MANAGEMENT -------------------------------- 
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.PERIOD_NAME IN ('APR-11-12','MAY-11-12','JUN-11-12','JUL-11-12') AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Cash Management' AND
  GJH.JE_CATEGORY='Bank Transfers'
UNION ALL
-------------------Data from Receivable --------------------------------
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')