CREATE OR REPLACE PROCEDURE SIG_ACCRUAL(P_ERRBUF OUT VARCHAR2,P_RETCODE OUT VARCHAR2,PSEG IN VARCHAR2,PFROM IN DATE,PTO IN DATE)
AS
----New Deveoped by Ramesh on 25Aug14
--DECLARE
CURSOR CUR1 IS
SELECT OPERATING_UNIT,UNIT_NAME,
ORGANIZATION_ID,ORG_CODE,ORG_NAME,
RECEIPT_NUM,
VENDOR_CODE,
VENDOR_NAME,
SUM(DEBIT_AMOUNT)TOTAL_DEBIT,
SUM(CREDIT_AMOUNT)TOTAL_CREDIT,
(SUM(DEBIT_AMOUNT) - SUM(CREDIT_AMOUNT)) NET
FROM
(
------Receipts For Cost Managements
SELECT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,
ASP.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
AP_SUPPLIERS ASP,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
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 XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.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.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.VENDOR_ID = ASP.VENDOR_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving'
AND GJH.JE_SOURCE = 'Cost Management'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---For Sales return Cost Management
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS
WHERE
GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_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 XAL.APPLICATION_ID = XAH.APPLICATION_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND xah.event_id = xdl.event_id
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.VENDOR_ID = APS.VENDOR_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Receiving'
AND GJH.JE_SOURCE = 'Cost Management'
AND GJL.STATUS = 'P'
--AND GJL.GL_SL_LINK_TABLE IN ('XLAJEL','RSL')
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---------Correction transactions for Cost Management
SELECT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,
ASP.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
AP_SUPPLIERS ASP,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
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 XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.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.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.VENDOR_ID = ASP.VENDOR_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_TYPE = 'CORRECT'
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving'
AND GJH.JE_SOURCE = 'Cost Management'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---- Receipts for Purchase India
SELECT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
PV.SEGMENT1 VENDOR_CODE,
PV.VENDOR_NAME VENDOR_NAME,
--NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(DECODE(GJL.ACCOUNTED_DR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) DEBIT_AMOUNT,
--NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
--NVL(DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE)),0) CREDIT_AMOUNT
NVL(DECODE(GJL.ACCOUNTED_CR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) CREDIT_AMOUNT
FROM
RCV_TRANSACTIONS RT, ---A
GL_JE_LINES GJL, ---- B
GL_CODE_COMBINATIONS IGCCV, -----C
GL_JE_HEADERS GJH, --- D
RCV_SHIPMENT_HEADERS RSH, --- CC
APPS.JAI_RCV_LINE_TAXES JRT,
ORG_ORGANIZATION_DEFINITIONS OOD, --- DD
AP_SUPPLIERS PV, ---- EE
HR_OPERATING_UNITS HOU
WHERE RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_ID = JRT.TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRT.VENDOR_SITE_ID IS NULL
AND (JRT.THIRD_PARTY_FLAG = 'N' OR JRT.THIRD_PARTY_FLAG IS NULL)
AND JRT.TAX_AMOUNT != 0
AND JRT.VENDOR_ID = PV.VENDOR_ID
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND RT.TRANSACTION_ID = TO_NUMBER(GJL.REFERENCE_5)
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving India'
AND GJH.JE_SOURCE = 'Purchasing India'
AND GJL.REFERENCE_4 = 'rcv_transactions'
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND GJL.CODE_COMBINATION_ID = IGCCV.CODE_COMBINATION_ID
AND IGCCV.SEGMENT5 = '1297'
AND IGCCV.SEGMENT2 = PSEG --= '11'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---- Receipts for Third party (Purchase India)
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,
--JRI.INVOICE_NUM RECEIPT_NUM,
RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
PV.SEGMENT1 VENDOR_CODE,
PV.VENDOR_NAME VENDOR_NAME,
NVL(DECODE(GJL.ACCOUNTED_DR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) DEBIT_AMOUNT,
NVL(DECODE(GJL.ACCOUNTED_CR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) CREDIT_AMOUNT
FROM
RCV_TRANSACTIONS RT, ---A
GL_JE_LINES GJL, ---- B
GL_CODE_COMBINATIONS IGCCV, -----C
GL_JE_HEADERS GJH, --- D
RCV_SHIPMENT_HEADERS RSH, --- CC
APPS.JAI_RCV_LINE_TAXES JRT,
JAI_RCV_TP_INVOICES JRI,
ORG_ORGANIZATION_DEFINITIONS OOD, --- DD
AP_SUPPLIERS PV, ---- EE
HR_OPERATING_UNITS HOU
WHERE RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_ID = JRT.TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRT.SHIPMENT_HEADER_ID = JRI.SHIPMENT_HEADER_ID(+)
AND JRT.VENDOR_ID = JRI.VENDOR_ID(+)
AND JRT.THIRD_PARTY_FLAG = 'Y'
AND JRT.VENDOR_ID = PV.VENDOR_ID
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND RT.TRANSACTION_ID = TO_NUMBER(GJL.REFERENCE_5)
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving India'
AND GJH.JE_SOURCE = 'Purchasing India'
AND GJL.REFERENCE_4 = 'rcv_transactions'
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND GJL.CODE_COMBINATION_ID = IGCCV.CODE_COMBINATION_ID
AND IGCCV.SEGMENT5 = '1297'
AND IGCCV.SEGMENT2 = PSEG --= '11'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---For Sales return Purchase India
SELECT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
PV.SEGMENT1 VENDOR_CODE,
PV.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
-- 0 GL_BAL,
-- NULL FLAG
FROM
RCV_TRANSACTIONS RT, ---A
GL_JE_LINES GJL, ---- B
GL_CODE_COMBINATIONS IGCCV, -----C
GL_JE_HEADERS GJH, --- D
RCV_SHIPMENT_HEADERS RSH, --- CC
ORG_ORGANIZATION_DEFINITIONS OOD, --- DD
AP_SUPPLIERS PV, ---- EE
HR_OPERATING_UNITS HOU
WHERE RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RSH.VENDOR_ID = PV.VENDOR_ID
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND RT.TRANSACTION_ID = TO_NUMBER(GJL.REFERENCE_5)
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving India'
AND GJH.JE_SOURCE = 'Purchasing India'
AND GJL.REFERENCE_4 = 'rcv_transactions'
AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND GJL.CODE_COMBINATION_ID = IGCCV.CODE_COMBINATION_ID
AND IGCCV.SEGMENT5 = '1297'
AND IGCCV.SEGMENT2 = PSEG --= '11'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
-----AP SOURCE
----- GL_SL_LINK_TABLE = 'APECL' REFERENCE_10 <> 'CHARGE'
--Working fine for all but we can't get receipt num
/*SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,NULL ORGANIZATION_ID,
NULL ORG_CODE,NULL ORG_NAME,
AIA.INVOICE_NUM RECEIPT_NUM,NULL TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,ASP.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
---AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AIA,
AP_AE_LINES_ALL AAL,
---RCV_TRANSACTIONS RT,
---RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
---ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS ASP
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
---AND AIDA.INVOICE_ID = AIA.INVOICE_ID
---AND AAL.SOURCE_ID = AIDA.INVOICE_DISTRIBUTION_ID(+)
AND AAL.REFERENCE2 = TO_CHAR(AIA.INVOICE_ID)
---AND AAL.SUBLEDGER_DOC_SEQUENCE_VALUE = AIA.DOC_SEQUENCE_VALUE
AND AAL.ORG_ID = AIA.ORG_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND AIA.VENDOR_ID = ASP.VENDOR_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.REFERENCE_10 NOT IN ('CHARGE')
AND GJL.GL_SL_LINK_TABLE = 'APECL'
AND GJL.STATUS = 'P'
AND TRUNC(EFFECTIVE_DATE) <= :LDATE */
----- GL_SL_LINK_TABLE = 'APECL' REFERENCE_10 <> 'CHARGE'
--Working fine for some orgs but we can get receipt num , issue with 03
SELECT AIDA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,ASP.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AIA,
AP_AE_LINES_ALL AAL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS ASP
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
AND AAL.SOURCE_ID = AIDA.INVOICE_DISTRIBUTION_ID(+)
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID(+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
AND AIDA.ORG_ID = HOU.ORGANIZATION_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID(+)
AND AIA.VENDOR_ID = ASP.VENDOR_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.REFERENCE_10 NOT IN ('CHARGE')
AND GJL.GL_SL_LINK_TABLE = 'APECL'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE ---test
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----- GL_SL_LINK_TABLE = 'APECL' REFERENCE_10 ='CHARGE'
---charges entries are available until 30DEC2013 even in R12 also
SELECT
AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,
NULL TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
---XLA_AE_LINES XAL,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU,
JAI_RCV_TP_INVOICES JRT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD
--AP_INVOICE_DISTRIBU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND TO_NUMBER(GJL.REFERENCE_2) = AIA.INVOICE_ID
AND AIA.INVOICE_ID = JRT.INVOICE_ID(+)
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.GL_SL_LINK_TABLE = 'APECL'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.REFERENCE_10 = 'CHARGE'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
-----GL_SL_LINK_TABLE = 'XLAJEL'
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,--AIA.INVOICE_ID,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,XDL.SOURCE_DISTRIBUTION_ID_NUM_1 TRANSACTION_ID,GJL.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
AP_INVOICES_ALL AIA,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_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 XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIDA.ORG_ID = HOU.ORGANIZATION_ID
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND GCC.SEGMENT5 = '1297'
AND AIDA.INVOICE_ID NOT IN (SELECT NVL(INVOICE_ID,0) FROM APPS.JAI_RCV_TP_INVOICES )
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.GL_SL_LINK_TABLE = 'XLAJEL'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
-----GL_SL_LINK_TABLE = 'XLAJEL' for third party
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,XDL.SOURCE_DISTRIBUTION_ID_NUM_1 TRANSACTION_ID,GJL.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
JAI_RCV_TP_INVOICES JRT,
---RCV_TRANSACTIONS RT,
AP_INVOICES_ALL AIA,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_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 XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.INVOICE_ID = JRT.INVOICE_ID (+)
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIDA.ORG_ID = HOU.ORGANIZATION_ID
AND JRT.VENDOR_ID = APS.VENDOR_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.GL_SL_LINK_TABLE = 'XLAJEL'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----------GL_SL_LINK_TABLE IS NULL AND GJL.REFERENCE_2 IS NULL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,GJL.JE_LINE_NUM TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
---XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALl AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
AP_SUPPLIERS APS,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND GIR.REFERENCE_5 = XAH.ENTITY_ID
AND GIR.REFERENCE_6 = XAH.EVENT_ID
---AND XAL.UPG_TAX_REFERENCE_ID1 = AIA.INVOICE_ID
AND XAl.SOURCE_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.INVOICE_ID NOT IN (SELECT NVL(INVOICE_ID,0) FROM APPS.JAI_RCV_TP_INVOICES )
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG -- '11'
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
AND GJL.REFERENCE_2 IS NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013' ---:LDATE
--AND TRUNC(GJL.EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----------GL_SL_LINK_TABLE IS NULL AND GJL.REFERENCE_2 IS NULL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,GJL.JE_LINE_NUM TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
---XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICES_ALL AIA,
RCV_SHIPMENT_HEADERS RSH,
JAI_RCV_TP_INVOICES JRT,
AP_SUPPLIERS APS,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND GIR.REFERENCE_5 = XAH.ENTITY_ID
AND GIR.REFERENCE_6 = XAH.EVENT_ID
AND XAL.UPG_TAX_REFERENCE_ID1 = AIA.INVOICE_ID
AND AIA.INVOICE_ID = JRT.INVOICE_ID
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
---AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG -- '11'
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
AND GJL.REFERENCE_2 IS NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013' ---:LDATE
--AND TRUNC(GJL.EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
SELECT DISTINCT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,TO_NUMBER(SOURCE_DISTRIBUTION_ID_NUM_1) TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(TO_NUMBER(XDL.UNROUNDED_ACCOUNTED_DR),0) DEBIT_AMOUNT,NVL(TO_NUMBER(XDL.UNROUNDED_ACCOUNTED_CR),0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND GIR.REFERENCE_5 = XAH.ENTITY_ID
AND GIR.REFERENCE_6 = XAH.EVENT_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 = AIDA.INVOICE_DISTRIBUTION_ID(+)
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID(+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND XDL.ALLOC_TO_SOURCE_ID_NUM_1 = AIA.INVOICE_ID
AND XDL.ALLOC_TO_ENTITY_CODE = 'AP_INVOICES'
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GCC.SEGMENT2 NOT IN ('11')
AND GJL.REFERENCE_2 IS NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----GL_SL_LINK_TABLE IS NULL AND GJL.REFERENCE_2 IS NOT NULL
SELECT DISTINCT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,RSH.SHIPMENT_HEADER_ID TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.REFERENCE_2 = TO_CHAR(AIA.INVOICE_ID)
AND AIA.INVOICE_ID = AIDA.INVOICE_ID(+)
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.REFERENCE_2 IS NOT NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013' -- :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,TO_NUMBER(GIR.REFERENCE_8) TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
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 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.INVOICE_ID NOT IN (SELECT NVL(INVOICE_ID,0) FROM JAI_RCV_TP_INVOICES)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
--some discounts are there in XDL table, due to that debits minus credits then we will get correct amt
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
AND TRUNC(EFFECTIVE_DATE) >= '01-DEC-2013' --:LDATE
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,TO_NUMBER(GIR.REFERENCE_8) TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
JAI_RCV_TP_INVOICES JRT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
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 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIA.INVOICE_ID = JRT.INVOICE_ID
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND JRT.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
--some discounts are there in XDL table, due to that debits minus credits then we will get correct amt
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
AND TRUNC(EFFECTIVE_DATE) >= '01-DEC-2013' --:LDATE
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
)
GROUP BY
OPERATING_UNIT,UNIT_NAME,
ORGANIZATION_ID,ORG_CODE,ORG_NAME,
RECEIPT_NUM,
VENDOR_CODE,
VENDOR_NAME ;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit'||CHR(9)||'Unit Name'||CHR(9)||'Organization Id'||CHR(9)||
'Org Code'||CHR(9)||'Org Name'||CHR(9)||'Receipt Num'||CHR(9)||'Vendor Code'||CHR(9)||'Vendor Name'||
CHR(9)||'Total Dr'||CHR(9)||'Total Cr'||CHR(9)||'Net');
FOR I IN CUR1 LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,I.OPERATING_UNIT||CHR(9)||I.UNIT_NAME||CHR(9)||I.ORGANIZATION_ID||CHR(9)||
I.ORG_CODE||CHR(9)||I.ORG_NAME||CHR(9)||I.RECEIPT_NUM||CHR(9)||I.VENDOR_CODE||CHR(9)||I.VENDOR_NAME||CHR(9)||
I.TOTAL_DEBIT||CHR(9)||I.TOTAL_CREDIT||CHR(9)||I.NET);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data For This Organization');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLCODE||','||SQLERRM);
END;
AS
----New Deveoped by Ramesh on 25Aug14
--DECLARE
CURSOR CUR1 IS
SELECT OPERATING_UNIT,UNIT_NAME,
ORGANIZATION_ID,ORG_CODE,ORG_NAME,
RECEIPT_NUM,
VENDOR_CODE,
VENDOR_NAME,
SUM(DEBIT_AMOUNT)TOTAL_DEBIT,
SUM(CREDIT_AMOUNT)TOTAL_CREDIT,
(SUM(DEBIT_AMOUNT) - SUM(CREDIT_AMOUNT)) NET
FROM
(
------Receipts For Cost Managements
SELECT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,
ASP.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
AP_SUPPLIERS ASP,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
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 XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.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.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.VENDOR_ID = ASP.VENDOR_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving'
AND GJH.JE_SOURCE = 'Cost Management'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---For Sales return Cost Management
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS
WHERE
GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_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 XAL.APPLICATION_ID = XAH.APPLICATION_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND xah.event_id = xdl.event_id
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.VENDOR_ID = APS.VENDOR_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Receiving'
AND GJH.JE_SOURCE = 'Cost Management'
AND GJL.STATUS = 'P'
--AND GJL.GL_SL_LINK_TABLE IN ('XLAJEL','RSL')
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---------Correction transactions for Cost Management
SELECT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,
ASP.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
AP_SUPPLIERS ASP,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
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 XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.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.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.VENDOR_ID = ASP.VENDOR_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_TYPE = 'CORRECT'
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving'
AND GJH.JE_SOURCE = 'Cost Management'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---- Receipts for Purchase India
SELECT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
PV.SEGMENT1 VENDOR_CODE,
PV.VENDOR_NAME VENDOR_NAME,
--NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(DECODE(GJL.ACCOUNTED_DR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) DEBIT_AMOUNT,
--NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
--NVL(DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE)),0) CREDIT_AMOUNT
NVL(DECODE(GJL.ACCOUNTED_CR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) CREDIT_AMOUNT
FROM
RCV_TRANSACTIONS RT, ---A
GL_JE_LINES GJL, ---- B
GL_CODE_COMBINATIONS IGCCV, -----C
GL_JE_HEADERS GJH, --- D
RCV_SHIPMENT_HEADERS RSH, --- CC
APPS.JAI_RCV_LINE_TAXES JRT,
ORG_ORGANIZATION_DEFINITIONS OOD, --- DD
AP_SUPPLIERS PV, ---- EE
HR_OPERATING_UNITS HOU
WHERE RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_ID = JRT.TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRT.VENDOR_SITE_ID IS NULL
AND (JRT.THIRD_PARTY_FLAG = 'N' OR JRT.THIRD_PARTY_FLAG IS NULL)
AND JRT.TAX_AMOUNT != 0
AND JRT.VENDOR_ID = PV.VENDOR_ID
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND RT.TRANSACTION_ID = TO_NUMBER(GJL.REFERENCE_5)
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving India'
AND GJH.JE_SOURCE = 'Purchasing India'
AND GJL.REFERENCE_4 = 'rcv_transactions'
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND GJL.CODE_COMBINATION_ID = IGCCV.CODE_COMBINATION_ID
AND IGCCV.SEGMENT5 = '1297'
AND IGCCV.SEGMENT2 = PSEG --= '11'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---- Receipts for Third party (Purchase India)
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,
--JRI.INVOICE_NUM RECEIPT_NUM,
RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
PV.SEGMENT1 VENDOR_CODE,
PV.VENDOR_NAME VENDOR_NAME,
NVL(DECODE(GJL.ACCOUNTED_DR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) DEBIT_AMOUNT,
NVL(DECODE(GJL.ACCOUNTED_CR,NULL,0,DECODE(JRT.CURRENCY,'INR',JRT.TAX_AMOUNT,(JRT.TAX_AMOUNT*RT.CURRENCY_CONVERSION_RATE))),0) CREDIT_AMOUNT
FROM
RCV_TRANSACTIONS RT, ---A
GL_JE_LINES GJL, ---- B
GL_CODE_COMBINATIONS IGCCV, -----C
GL_JE_HEADERS GJH, --- D
RCV_SHIPMENT_HEADERS RSH, --- CC
APPS.JAI_RCV_LINE_TAXES JRT,
JAI_RCV_TP_INVOICES JRI,
ORG_ORGANIZATION_DEFINITIONS OOD, --- DD
AP_SUPPLIERS PV, ---- EE
HR_OPERATING_UNITS HOU
WHERE RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RT.TRANSACTION_ID = JRT.TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRT.SHIPMENT_HEADER_ID = JRI.SHIPMENT_HEADER_ID(+)
AND JRT.VENDOR_ID = JRI.VENDOR_ID(+)
AND JRT.THIRD_PARTY_FLAG = 'Y'
AND JRT.VENDOR_ID = PV.VENDOR_ID
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND RT.TRANSACTION_ID = TO_NUMBER(GJL.REFERENCE_5)
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving India'
AND GJH.JE_SOURCE = 'Purchasing India'
AND GJL.REFERENCE_4 = 'rcv_transactions'
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND GJL.CODE_COMBINATION_ID = IGCCV.CODE_COMBINATION_ID
AND IGCCV.SEGMENT5 = '1297'
AND IGCCV.SEGMENT2 = PSEG --= '11'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
---For Sales return Purchase India
SELECT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
RSH.RECEIPT_NUM RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
PV.SEGMENT1 VENDOR_CODE,
PV.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,
NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
-- 0 GL_BAL,
-- NULL FLAG
FROM
RCV_TRANSACTIONS RT, ---A
GL_JE_LINES GJL, ---- B
GL_CODE_COMBINATIONS IGCCV, -----C
GL_JE_HEADERS GJH, --- D
RCV_SHIPMENT_HEADERS RSH, --- CC
ORG_ORGANIZATION_DEFINITIONS OOD, --- DD
AP_SUPPLIERS PV, ---- EE
HR_OPERATING_UNITS HOU
WHERE RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND RSH.VENDOR_ID = PV.VENDOR_ID
AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID
AND RT.TRANSACTION_ID = TO_NUMBER(GJL.REFERENCE_5)
AND GJL.STATUS = 'P'
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_CATEGORY = 'Receiving India'
AND GJH.JE_SOURCE = 'Purchasing India'
AND GJL.REFERENCE_4 = 'rcv_transactions'
AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND GJL.CODE_COMBINATION_ID = IGCCV.CODE_COMBINATION_ID
AND IGCCV.SEGMENT5 = '1297'
AND IGCCV.SEGMENT2 = PSEG --= '11'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
-----AP SOURCE
----- GL_SL_LINK_TABLE = 'APECL' REFERENCE_10 <> 'CHARGE'
--Working fine for all but we can't get receipt num
/*SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,NULL ORGANIZATION_ID,
NULL ORG_CODE,NULL ORG_NAME,
AIA.INVOICE_NUM RECEIPT_NUM,NULL TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,ASP.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
---AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AIA,
AP_AE_LINES_ALL AAL,
---RCV_TRANSACTIONS RT,
---RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
---ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS ASP
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
---AND AIDA.INVOICE_ID = AIA.INVOICE_ID
---AND AAL.SOURCE_ID = AIDA.INVOICE_DISTRIBUTION_ID(+)
AND AAL.REFERENCE2 = TO_CHAR(AIA.INVOICE_ID)
---AND AAL.SUBLEDGER_DOC_SEQUENCE_VALUE = AIA.DOC_SEQUENCE_VALUE
AND AAL.ORG_ID = AIA.ORG_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND AIA.VENDOR_ID = ASP.VENDOR_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.REFERENCE_10 NOT IN ('CHARGE')
AND GJL.GL_SL_LINK_TABLE = 'APECL'
AND GJL.STATUS = 'P'
AND TRUNC(EFFECTIVE_DATE) <= :LDATE */
----- GL_SL_LINK_TABLE = 'APECL' REFERENCE_10 <> 'CHARGE'
--Working fine for some orgs but we can get receipt num , issue with 03
SELECT AIDA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,RT.TRANSACTION_ID,GJH.JE_HEADER_ID,
ASP.SEGMENT1 VENDOR_CODE,ASP.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AIA,
AP_AE_LINES_ALL AAL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS ASP
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
AND AAL.SOURCE_ID = AIDA.INVOICE_DISTRIBUTION_ID(+)
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID(+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
AND AIDA.ORG_ID = HOU.ORGANIZATION_ID
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID(+)
AND AIA.VENDOR_ID = ASP.VENDOR_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.REFERENCE_10 NOT IN ('CHARGE')
AND GJL.GL_SL_LINK_TABLE = 'APECL'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE ---test
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----- GL_SL_LINK_TABLE = 'APECL' REFERENCE_10 ='CHARGE'
---charges entries are available until 30DEC2013 even in R12 also
SELECT
AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,
NULL TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
---XLA_AE_LINES XAL,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU,
JAI_RCV_TP_INVOICES JRT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD
--AP_INVOICE_DISTRIBU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND TO_NUMBER(GJL.REFERENCE_2) = AIA.INVOICE_ID
AND AIA.INVOICE_ID = JRT.INVOICE_ID(+)
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.GL_SL_LINK_TABLE = 'APECL'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.REFERENCE_10 = 'CHARGE'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
-----GL_SL_LINK_TABLE = 'XLAJEL'
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,--AIA.INVOICE_ID,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,XDL.SOURCE_DISTRIBUTION_ID_NUM_1 TRANSACTION_ID,GJL.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
AP_INVOICES_ALL AIA,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_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 XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIDA.ORG_ID = HOU.ORGANIZATION_ID
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND GCC.SEGMENT5 = '1297'
AND AIDA.INVOICE_ID NOT IN (SELECT NVL(INVOICE_ID,0) FROM APPS.JAI_RCV_TP_INVOICES )
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.GL_SL_LINK_TABLE = 'XLAJEL'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
-----GL_SL_LINK_TABLE = 'XLAJEL' for third party
SELECT DISTINCT HOU.ORGANIZATION_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,
OOD.ORGANIZATION_ID ORGANIZATION_ID,OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,XDL.SOURCE_DISTRIBUTION_ID_NUM_1 TRANSACTION_ID,GJL.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
JAI_RCV_TP_INVOICES JRT,
---RCV_TRANSACTIONS RT,
AP_INVOICES_ALL AIA,
RCV_SHIPMENT_HEADERS RSH,
HR_OPERATING_UNITS HOU,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_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 XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.INVOICE_ID = JRT.INVOICE_ID (+)
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIDA.ORG_ID = HOU.ORGANIZATION_ID
AND JRT.VENDOR_ID = APS.VENDOR_ID
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GJL.GL_SL_LINK_TABLE = 'XLAJEL'
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----------GL_SL_LINK_TABLE IS NULL AND GJL.REFERENCE_2 IS NULL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,GJL.JE_LINE_NUM TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
---XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALl AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
AP_SUPPLIERS APS,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND GIR.REFERENCE_5 = XAH.ENTITY_ID
AND GIR.REFERENCE_6 = XAH.EVENT_ID
---AND XAL.UPG_TAX_REFERENCE_ID1 = AIA.INVOICE_ID
AND XAl.SOURCE_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.INVOICE_ID NOT IN (SELECT NVL(INVOICE_ID,0) FROM APPS.JAI_RCV_TP_INVOICES )
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RT.ORGANIZATION_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG -- '11'
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
AND GJL.REFERENCE_2 IS NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013' ---:LDATE
--AND TRUNC(GJL.EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----------GL_SL_LINK_TABLE IS NULL AND GJL.REFERENCE_2 IS NULL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,GJL.JE_LINE_NUM TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XAL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XAL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
---XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICES_ALL AIA,
RCV_SHIPMENT_HEADERS RSH,
JAI_RCV_TP_INVOICES JRT,
AP_SUPPLIERS APS,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND GIR.REFERENCE_5 = XAH.ENTITY_ID
AND GIR.REFERENCE_6 = XAH.EVENT_ID
AND XAL.UPG_TAX_REFERENCE_ID1 = AIA.INVOICE_ID
AND AIA.INVOICE_ID = JRT.INVOICE_ID
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
---AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG -- '11'
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
AND GJL.REFERENCE_2 IS NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013' ---:LDATE
--AND TRUNC(GJL.EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
SELECT DISTINCT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,TO_NUMBER(SOURCE_DISTRIBUTION_ID_NUM_1) TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(TO_NUMBER(XDL.UNROUNDED_ACCOUNTED_DR),0) DEBIT_AMOUNT,NVL(TO_NUMBER(XDL.UNROUNDED_ACCOUNTED_CR),0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND GIR.REFERENCE_5 = XAH.ENTITY_ID
AND GIR.REFERENCE_6 = XAH.EVENT_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 = AIDA.INVOICE_DISTRIBUTION_ID(+)
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID(+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND XDL.ALLOC_TO_SOURCE_ID_NUM_1 = AIA.INVOICE_ID
AND XDL.ALLOC_TO_ENTITY_CODE = 'AP_INVOICES'
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GCC.SEGMENT2 NOT IN ('11')
AND GJL.REFERENCE_2 IS NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013'
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
----GL_SL_LINK_TABLE IS NULL AND GJL.REFERENCE_2 IS NOT NULL
SELECT DISTINCT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,RSH.SHIPMENT_HEADER_ID TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(GJL.ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(GJL.ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.REFERENCE_2 = TO_CHAR(AIA.INVOICE_ID)
AND AIA.INVOICE_ID = AIDA.INVOICE_ID(+)
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG --= '11'
AND GJL.REFERENCE_2 IS NOT NULL
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
--AND TRUNC(EFFECTIVE_DATE) <= '30-NOV-2013' -- :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,TO_NUMBER(GIR.REFERENCE_8) TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
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 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA.RCV_TRANSACTION_ID = RT.TRANSACTION_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID (+)
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.INVOICE_ID NOT IN (SELECT NVL(INVOICE_ID,0) FROM JAI_RCV_TP_INVOICES)
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
--some discounts are there in XDL table, due to that debits minus credits then we will get correct amt
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
AND TRUNC(EFFECTIVE_DATE) >= '01-DEC-2013' --:LDATE
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
UNION ALL
SELECT AIA.ORG_ID OPERATING_UNIT,HOU.NAME UNIT_NAME,OOD.ORGANIZATION_ID ORGANIZATION_ID,
OOD.ORGANIZATION_CODE ORG_CODE,OOD.ORGANIZATION_NAME ORG_NAME,
NVL(RSH.RECEIPT_NUM,AIA.INVOICE_NUM) RECEIPT_NUM,TO_NUMBER(GIR.REFERENCE_8) TRANSACTION_ID,GJH.JE_HEADER_ID,
APS.SEGMENT1 VENDOR_CODE,APS.VENDOR_NAME VENDOR_NAME,
NVL(XDL.UNROUNDED_ACCOUNTED_DR,0) DEBIT_AMOUNT,NVL(XDL.UNROUNDED_ACCOUNTED_CR,0) CREDIT_AMOUNT
FROM
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_HEADERS GJH,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
JAI_RCV_TP_INVOICES JRT,
RCV_SHIPMENT_HEADERS RSH,
ORG_ORGANIZATION_DEFINITIONS OOD,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS APS,
HR_OPERATING_UNITS HOU
WHERE
GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.REFERENCE_7 = XAL.AE_HEADER_ID
AND GIR.REFERENCE_8 = XAL.AE_LINE_NUM
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 = AIDA.INVOICE_DISTRIBUTION_ID
AND AIA.INVOICE_ID = JRT.INVOICE_ID
AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND JRT.VENDOR_ID = APS.VENDOR_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND GCC.SEGMENT5 = '1297'
AND GCC.SEGMENT2 = PSEG
AND GCC.SEGMENT2 NOT IN ('02','03','04','05','06','08')
--some discounts are there in XDL table, due to that debits minus credits then we will get correct amt
AND GJL.GL_SL_LINK_TABLE IS NULL
AND GJL.STATUS = 'P'
AND TRUNC(EFFECTIVE_DATE) >= '01-DEC-2013' --:LDATE
--AND TRUNC(EFFECTIVE_DATE) <= :LDATE
AND TRUNC(EFFECTIVE_DATE) BETWEEN PFROM AND PTO
)
GROUP BY
OPERATING_UNIT,UNIT_NAME,
ORGANIZATION_ID,ORG_CODE,ORG_NAME,
RECEIPT_NUM,
VENDOR_CODE,
VENDOR_NAME ;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit'||CHR(9)||'Unit Name'||CHR(9)||'Organization Id'||CHR(9)||
'Org Code'||CHR(9)||'Org Name'||CHR(9)||'Receipt Num'||CHR(9)||'Vendor Code'||CHR(9)||'Vendor Name'||
CHR(9)||'Total Dr'||CHR(9)||'Total Cr'||CHR(9)||'Net');
FOR I IN CUR1 LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,I.OPERATING_UNIT||CHR(9)||I.UNIT_NAME||CHR(9)||I.ORGANIZATION_ID||CHR(9)||
I.ORG_CODE||CHR(9)||I.ORG_NAME||CHR(9)||I.RECEIPT_NUM||CHR(9)||I.VENDOR_CODE||CHR(9)||I.VENDOR_NAME||CHR(9)||
I.TOTAL_DEBIT||CHR(9)||I.TOTAL_CREDIT||CHR(9)||I.NET);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data For This Organization');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLCODE||','||SQLERRM);
END;
No comments:
Post a Comment