Sunday, 22 January 2012

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')
Posted by at

5 comments:

  1. Sid15 November 2012 at 12:25

    Hello ravi,

    Is this query for GL account analysis deatails,

    jerry

    ReplyDelete
    Replies
    1. ravi15 November 2012 at 21:48

      Hi jerry,

      Thanks for comments.
      Yes..this is account analysis details query for GL account at sublegder level. It will give you payable, receivable, cash management, manual journal entries breaks up report.
      If you hv any further queries, let me know.

      Thanks,
      r@v!

      Delete
  2. Unknown10 December 2012 at 02:43

    This query is really very helpful. Great Work. 10-Stars *********

    ReplyDelete
  3. Unknown17 May 2016 at 08:49

    Very helpful, may want to reverse the DR / CR order in the Manual portion of the query though, or to keep the beancounters happy actually put the DR first and CR next!

    ReplyDelete
  4. Charles C6 October 2016 at 11:25

    Any information regarding performance of this query? Doesn't look like something that could be run quickly without a GL Code combination parameter and limited to 1 GL period at a time.

    ReplyDelete

Subscribe to: Post Comments (Atom)