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')
No comments:
Post a Comment