SELECT DISTINCT PS.PERIOD_NAME,
GJL.REFERENCE_2,
TO_CHAR (NULL) PO_ORDER_NUMBER,
TO_CHAR (NULL) "RECEIPT NUMBER",
TO_DATE (NULL) "RECEIPT DATE",
TO_CHAR (NULL) "INVOICE NUMBER",
TO_DATE (NULL) "INVOICE DATE",
TO_CHAR (NULL) "VENDOR NAME",
TO_CHAR (NULL) "VENDOR NUMBER",
JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT2,
GJH.CURRENCY_CODE,
GJL.ENTERED_DR ,
GJL.ENTERED_CR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_DR )) ACCOUNTED_DR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_CR )) ACCOUNTED_CR,
GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJL.CODE_COMBINATION_ID ,
GJH.DOC_SEQUENCE_VALUE "VOCHER NUMBER",
GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
GJL.ATTRIBUTE1 "STAFF NUMBER",
GJL.ATTRIBUTE2 "PROJECT",
GJL.ATTRIBUTE3 "CUSTOMER NUM/NAME" ,
GJL.DESCRIPTION DESCRIPTION
FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH, GL_SUMMARY_COMBINATIONS_V GSCV, GL_LEDGERS,GL_PERIOD_STATUSES PS,GL_JE_BATCHES B,
GL_LEDGER_RELATIONSHIPS LR
WHERE GSCV.CODE_COMBINATION_ID =GJL.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.PERIOD_NAME = PS.PERIOD_NAME
AND PS.LEDGER_ID = GJL.LEDGER_ID
AND B.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.LEDGER_ID = LR.SOURCE_LEDGER_ID
AND LR.SOURCE_LEDGER_ID = LR.TARGET_LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND GJH.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES','COST MANAGEMENT','CONSOLIDATION','RECEIVABLES')
AND GJH.LEDGER_ID = GL_LEDGERS.LEDGER_ID
--- AND GL_LEDGERS.SUSPENSE_ALLOWED_FLAG = 'P'
AND ( NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND ( NVL (GSCV.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GSCV.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GSCV.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GSCV.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',GJH.CURRENCY_CODE,
'NON INR',DECODE(GJH.CURRENCY_CODE,'INR','###',GJH.CURRENCY_CODE))
UNION ALL
SELECT DISTINCT B.PERIOD_NAME, D.REFERENCE_2, AAA.PO_ORDER_NUMBER, AAA.RCV_RECEIPT_NUM "RECEIPT NUMBER",
AAA.TRX_DATE "RECEIPT DATE", AAA.TRX_NUMBER_DISPLAYED "INVOICE NUMBER",
AAA.ACCOUNTING_DATE "INVOICE DATE", AAA.THIRD_PARTY_NAME "VENDOR NAME",
AAA.THIRD_PARTY_NUMBER "VENDOR NUMBER", B.JE_SOURCE "SOURCE", B.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID) SEGMENT2,
AAA.CURRENCY_CODE, AAA.ENTERED_DR, AAA.ENTERED_CR, AAA.ACCOUNTED_DR,
AAA.ACCOUNTED_CR, AAA.JE_HEADER_ID, AAA.JE_LINE_NUM, AAA.CODE_COMBINATION_ID,
AAA.DOC_SEQUENCE_VALUE "VOCHER NUMBER", AAA.GL_DATE "GL_DATE",
AAA.ATTRIBUTE1 "STAFF NUMBER", AAA.ATTRIBUTE2 "PROJECT",
AAA.ATTRIBUTE3 "CUSTOMER NUM/NAME", AAA.DESCRIPTION "DESCRIPTION"
FROM (SELECT DISTINCT POH.SEGMENT1 PO_ORDER_NUMBER, RSH.RECEIPT_NUM RCV_RECEIPT_NUM,
RCT.TRANSACTION_DATE TRX_DATE, API.INVOICE_NUM TRX_NUMBER_DISPLAYED,
RRS.ACCOUNTING_DATE ACCOUNTING_DATE,
POV.VENDOR_NAME THIRD_PARTY_NAME,
POV.SEGMENT1 THIRD_PARTY_NUMBER, RRS.CURRENCY_CODE,
RRS.ENTERED_DR, RRS.ENTERED_CR, RRS.ACCOUNTED_DR,
RRS.ACCOUNTED_CR, R.JE_HEADER_ID, R.JE_LINE_NUM,
RRS.CODE_COMBINATION_ID, 201 APPLICATION_ID,
TO_DATE (NULL) GL_DATE, TO_NUMBER (NULL) DOC_SEQUENCE_VALUE,
TO_CHAR (NULL) DESCRIPTION, TO_CHAR (NULL) ATTRIBUTE1,
TO_CHAR (NULL) ATTRIBUTE2, TO_CHAR (NULL) ATTRIBUTE3
--POH.ORG_ID
FROM PO_HEADERS_ALL POH, PO_LINES_ALL PL, PO_DISTRIBUTIONS_ALL POD
, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, RCV_TRANSACTIONS RCT, RCV_RECEIVING_SUB_LEDGER RRS
, AP_INVOICE_DISTRIBUTIONS_ALL AIDA,AP_INVOICES_ALL API , XLA_AE_HEADERS XAH , XLA_AE_LINES XAL
,GL_IMPORT_REFERENCES R
,AP_SUPPLIERS POV
WHERE 1=1
AND POH.PO_HEADER_ID=PL.PO_HEADER_ID
AND POH.PO_HEADER_ID=POD.PO_HEADER_ID
AND POH.PO_HEADER_ID= RSL.PO_HEADER_ID(+)
AND RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID=RCT.SHIPMENT_HEADER_ID(+)
AND POD.PO_DISTRIBUTION_ID= AIDA.PO_DISTRIBUTION_ID(+)
AND API.INVOICE_ID(+)=AIDA.INVOICE_ID
AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND AIDA.ACCOUNTING_EVENT_ID=XAH.EVENT_ID(+)
AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID(+)
AND XAL.GL_SL_LINK_ID= R.GL_SL_LINK_ID(+)
AND R.REFERENCE_8=XAL.AE_LINE_NUM
AND POH.VENDOR_ID=POV.VENDOR_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE(+)='ACCRUAL'
AND RRS.ACCOUNTING_LINE_TYPE='ACCRUAL'
AND XAL.ACCOUNTING_CLASS_CODE(+)='LIABILITY'
AND POD.DESTINATION_TYPE_CODE='EXPENSE'
AND EXISTS (SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,AP_INVOICES_ALL AI
WHERE AID.PO_DISTRIBUTION_ID=POD.PO_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AI.VENDOR_ID = POV.VENDOR_ID) ) AAA,
GL_JE_HEADERS B, GL_JE_LINES D,GL_JE_BATCHES E, GL_PERIOD_STATUSES PS,
GL_SUMMARY_COMBINATIONS_V CCC
WHERE B.JE_HEADER_ID = AAA.JE_HEADER_ID
AND D.JE_LINE_NUM = AAA.JE_LINE_NUM
AND B.JE_HEADER_ID=D.JE_HEADER_ID
AND D.CODE_COMBINATION_ID = CCC.CODE_COMBINATION_ID
AND AAA.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
AND PS.LEDGER_ID = D.LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND B.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES', 'PURCHASING','CONSOLIDATION')
--AND AAA.ORG_ID=:P_ORG
AND ( NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND ( NVL (CCC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CCC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND B.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND B.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',B.CURRENCY_CODE,
'NON INR',DECODE(B.CURRENCY_CODE,'INR','###',B.CURRENCY_CODE))
UNION ALL
--PO-RECEIPT-GL
SELECT GPS.PERIOD_NAME,
L.REFERENCE_2,
PHA.SEGMENT1 PO_ORDER_NUMBER,
RSH.RECEIPT_NUM "RECEIPT NUMBER",
RT.TRANSACTION_DATE "RECEIPT DATE",
NULL "INVOICE NUMBER",
NULL "INVOICE DATE",
PV.VENDOR_NAME "VENDOR NAME",
PV.SEGMENT1 "VENDOR NUMBER",
H.JE_SOURCE "SOURCE",
H.LEDGER_ID "SET_OF_BOOKS_ID" ,
GCC.CONCATENATED_SEGMENTS SEGMENT,
GCC.CONCATENATED_SEGMENTS SEGMENT2,
PHA.CURRENCY_CODE,
-- PLA.LINE_NUM,
-- PLLA.SHIPMENT_NUM,
-- PLA.ITEM_DESCRIPTION,
-- PLA.UNIT_PRICE,
-- PLLA.QUANTITY,
-- PLLA.QUANTITY_RECEIVED,
-- PLLA.QUANTITY_BILLED,
-- RT.TRANSACTION_TYPE,
--RT.TRANSACTION_DATE,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,
XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
RRSL.CODE_COMBINATION_ID,
--GPS.APPLICATION_ID,
--
TO_NUMBER (NULL) "VOCHER NUMBER",
TO_DATE (NULL) GL_DATE,
TO_CHAR (NULL) "STAFF NUMBER",
TO_CHAR (NULL) "PROJECT",
TO_CHAR (NULL) "CUSTOMER NUM/NAME" ,
--PHA.ORG_ID,
-- TO_CHAR (NULL) DESCRIPTION
RSL.ITEM_DESCRIPTION "DESCRIPTION"
FROM GL.GL_JE_HEADERS H,
GL.GL_JE_LINES L,
GL_CODE_COMBINATIONS_KFV GCC,
GL.GL_IMPORT_REFERENCES GIR,
APPS.XLA_AE_LINES XAL,
APPS.XLA_DISTRIBUTION_LINKS XDL,
PO.RCV_RECEIVING_SUB_LEDGER RRSL,
PO.RCV_TRANSACTIONS RT,
PO.PO_HEADERS_ALL PHA,
PO.PO_LINES_ALL PLA,
PO.PO_LINE_LOCATIONS_ALL PLLA,
APPS.PO_VENDORS PV,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
GL_PERIOD_STATUSES GPS
WHERE 1=1
AND H.JE_HEADER_ID = L.JE_HEADER_ID
AND L.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND ( NVL (GCC.SEGMENT5, 0) >=:P_ACCOUNT_FROM
AND (NVL (GCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GCC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_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 = RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
AND RT.PO_HEADER_ID = PLA.PO_HEADER_ID
AND RT.PO_LINE_ID = PLA.PO_LINE_ID
AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID
--AND H.LEDGER_ID = 2041--UTSC
AND H.JE_SOURCE = 'COST MANAGEMENT'
AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID =RSL.SHIPMENT_LINE_ID
AND L.PERIOD_NAME = GPS.PERIOD_NAME
AND GPS.LEDGER_ID = L.LEDGER_ID
AND GPS.APPLICATION_ID = 101
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
--AND H.JE_CATEGORY = 'RECEIVING'
--AND H.STATUS = 'P'
--AND H.PERIOD_NAME IN ('MAY-12')
AND H.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND H.CURRENCY_CODE != 'STAT'
AND H.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',H.CURRENCY_CODE,
'NON INR',DECODE(H.CURRENCY_CODE,'INR','###',H.CURRENCY_CODE))
UNION ALL
SELECT
GPS.PERIOD_NAME,
GJL.REFERENCE_2,
(SELECT DISTINCT PHA.SEGMENT1 FROM AP_INVOICE_LINES_ALL AILLA, PO_HEADERS_ALL PHA WHERE AILLA.INVOICE_ID=AIA.INVOICE_ID AND AILLA.PO_HEADER_ID=PHA.PO_HEADER_ID) PO_ORDER_NUMBER,
(SELECT DISTINCT RSH.RECEIPT_NUM
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT, AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL) ) "RECEIPT NUMBER",
(SELECT DISTINCT RT.TRANSACTION_DATE
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT, AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL)) "RECEIPT DATE",
AIA.INVOICE_NUM "INVOICE NUMBER",
AIA.GL_DATE "INVOICE DATE",
APS.VENDOR_NAME "VENDOR NAME",
APS.SEGMENT1 "VENDOR NUMBER",
GJH.JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
CC.CONCATENATED_SEGMENTS SEGMENT,
CC.CONCATENATED_SEGMENTS SEGMENT2,
AIA.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,
XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
CC.CODE_COMBINATION_ID,
--GPS.APPLICATION_ID,
--
AIA.DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,AIA.GL_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,XAL.DESCRIPTION "DESCRIPTION"
FROM APPS.AP_INVOICES_ALL AIA,
XLA.XLA_TRANSACTION_ENTITIES XTE,
APPS.XLA_EVENTS XEV,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_AE_LINES XAL,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GJL,
APPS.GL_CODE_COMBINATIONS_KFV CC,
APPS.AP_SUPPLIERS APS,
GL_PERIOD_STATUSES GPS
WHERE AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
AND XEV.ENTITY_ID= XTE.ENTITY_ID
AND XAH.ENTITY_ID= XTE.ENTITY_ID
AND XAH.EVENT_ID= XEV.EVENT_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
AND GJH.STATUS = 'P'
AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
AND AIA.VENDOR_ID=APS.VENDOR_ID
AND GJH.STATUS='P'
AND GJH.ACTUAL_FLAG='A'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
--AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
--AND CC.SEGMENT1=111
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.JE_SOURCE ='PAYABLES'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)
--AND AIA.ORG_ID=:P_ORG
AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
-- AND 24116=:P_ACCOUNT_FROM
-- AND 24116=:P_ACCOUNT_TO
AND ( NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND AIA.PAYMENT_CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',AIA.PAYMENT_CURRENCY_CODE,
'NON INR',DECODE(AIA.PAYMENT_CURRENCY_CODE,'INR','###',AIA.PAYMENT_CURRENCY_CODE))
UNION ALL
SELECT
GJH.PERIOD_NAME,
NULL REFERENCE_2,
NULL PO_ORDER_NUMBER,
NULL "RECEIPT NUMBER",
NULL "RECEIPT DATE",
ENT.TRANSACTION_NUMBER "INVOICE NUMBER",
AIA.CHECK_DATE "INVOICE DATE",
AV.VENDOR_NAME "VENDOR NAME",
AV.SEGMENT1 "VENDOR NUMBER",
GJH.JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
GCCK.CONCATENATED_SEGMENTS SEGMENT,
GCCK.CONCATENATED_SEGMENTS SEGMENT21,
AIA.CURRENCY_CODE CURRENCY_CODE
,AEL.ENTERED_DR,
AEL.ENTERED_CR,
AEL.ACCOUNTED_DR,
AEL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
GCCK.CODE_COMBINATION_ID
,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,AEH.ACCOUNTING_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,AEL.DESCRIPTION "DESCRIPTION"
FROM
XLA_AE_HEADERS AEH
,XLA_AE_LINES AEL
,XLA_EVENTS XLE
,XLA_EVENT_TYPES_TL XET
,XLA_TRANSACTION_ENTITIES ENT
,GL_LEDGERS GLG
,GL_PERIODS GLP
,XLA_SUBLEDGERS XLS
,GL_CODE_COMBINATIONS_KFV GCCK
,GL_IMPORT_REFERENCES GIR
,GL_JE_LINES GJL
,GL_JE_HEADERS GJH
,GL_JE_BATCHES GJB
--,AP_INVOICE_DISTRIBUTIONS_ALL AID
-- ,AP_INVOICE_LINES_ALL AILA
,AP_CHECKS_ALL AIA
,GL_PERIOD_STATUSES GPS
,AP_SUPPLIERS AV
--,PA_PROJECT_CUSTOMERS_V PPCV
--,PA_PROJECTS_ALL PPA
WHERE 1=1
AND AEH.LEDGER_ID = GLG.LEDGER_ID
AND AEL.APPLICATION_ID = AEH.APPLICATION_ID
AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XLE.APPLICATION_ID = AEH.APPLICATION_ID
AND XLE.EVENT_ID = AEH.EVENT_ID
AND XET.APPLICATION_ID = XLE.APPLICATION_ID
AND XET.EVENT_TYPE_CODE = XLE.EVENT_TYPE_CODE
AND XET.LANGUAGE = USERENV('LANG')
AND ENT.APPLICATION_ID = AEH.APPLICATION_ID
AND ENT.ENTITY_ID = AEH.ENTITY_ID
AND GLP.PERIOD_NAME = AEH.PERIOD_NAME
AND GLP.PERIOD_SET_NAME = GLG.PERIOD_SET_NAME
AND XLS.APPLICATION_ID = AEH.APPLICATION_ID
AND GCCK.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE <> 'N' AND
AEH.APPLICATION_ID = 200 AND AEH.BALANCE_TYPE_CODE = 'A' AND (
NVL(AEL.ACCOUNTED_CR,0) <> 0
OR NVL(AEL.ACCOUNTED_DR,0) <> 0 )
AND GIR.GL_SL_LINK_ID(+) = AEL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE(+) = AEL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM(+) = GIR.JE_LINE_NUM
AND GJH.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
AND GJB.JE_BATCH_ID (+) = GIR.JE_BATCH_ID
AND DECODE(GJH.JE_HEADER_ID,NULL,'Y',GJH.JE_FROM_SLA_FLAG) IN ('U', 'Y')
--AND AEL.ACCOUNTING_CLASS_CODE IN ('ITEM EXPENSE','CASH_CLEARING','LIABILITY','PREPAID_EXPENSE','RTAX','INTRA')
AND ENT.SOURCE_ID_INT_1 = AIA.CHECK_ID
-- AND AIA.DOC_SEQUENCE_VALUE=GIR.SUBLEDGER_DOC_SEQUENCE_VALUE
AND AIA.VENDOR_ID=AV.VENDOR_ID
AND GJH.JE_SOURCE ='PAYABLES'
AND AEH.JE_CATEGORY_NAME='PAYMENTS'
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND AIA.ORG_ID=:P_ORG
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
AND ( NVL (GCCK.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GCCK.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GCCK.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GCCK.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND AIA.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',AIA.CURRENCY_CODE,
'NON INR',DECODE(AIA.CURRENCY_CODE,'INR','###',AIA.CURRENCY_CODE))
UNION ALL
--ADDED BY MANOHAR
SELECT GJH.PERIOD_NAME,
NULL REFERENCE_2,
NULL PO_ORDER_NUMBER,
CASE
WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
XTE.TRANSACTION_NUMBER
ELSE
NULL
END "RECEIPT NUMBER",
CASE
WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
XAH.ACCOUNTING_DATE
ELSE
NULL
END "RECEIPT DATE",
CASE
WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
XTE.TRANSACTION_NUMBER ELSE
NULL
END "INVOICE NUMBER",
CASE
WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
XAH.ACCOUNTING_DATE
ELSE
NULL
END "INVOICE DATE",
CASE
WHEN XAL.PARTY_TYPE_CODE = 'S' THEN
(SELECT APS.SEGMENT1
||'|'||APS.VENDOR_NAME
||'|'||HZP.JGZZ_FISCAL_CODE
||'|'||HZP.TAX_REFERENCE
||'|'||HPS.PARTY_SITE_NUMBER
||'|'||HPS.PARTY_SITE_NAME
||'|'||NULL
FROM AP_SUPPLIERS APS
,AP_SUPPLIER_SITES_ALL APSS
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE APS.VENDOR_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = APS.PARTY_ID
AND APSS.VENDOR_SITE_ID(+) = AEL2.PARTY_SITE_ID
AND HPS.PARTY_SITE_ID(+) = APSS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
(SELECT --HCA.ACCOUNT_NUMBER||'|'||
HZP.PARTY_NAME
--||'|'||HZP.JGZZ_FISCAL_CODE
--||'|'||HZP.TAX_REFERENCE
--||'|'||HPS.PARTY_SITE_NUMBER
--||'|'||HPS.PARTY_SITE_NAME
--||'|'||HZCU.TAX_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_CUST_SITE_USES_ALL HZCU
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
ELSE
NULL
END "VENDOR NAME",
CASE
WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
(SELECT HCA.ACCOUNT_NUMBER
--||'|'|| HZP.PARTY_NAME
--||'|'||HZP.JGZZ_FISCAL_CODE
--||'|'||HZP.TAX_REFERENCE
--||'|'||HPS.PARTY_SITE_NUMBER
--||'|'||HPS.PARTY_SITE_NAME
--||'|'||HZCU.TAX_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_CUST_SITE_USES_ALL HZCU
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
ELSE
NULL
END "VENDOR NUMBER",
GJH.JE_SOURCE"SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
CC.CONCATENATED_SEGMENTS SEGMENT,
CC.CONCATENATED_SEGMENTS SEGMENT21,
XAL.CURRENCY_CODE CURRENCY_CODE,
XAL.ENTERED_DR,XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
CC.CODE_COMBINATION_ID
,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,XAH.ACCOUNTING_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,XAH.DESCRIPTION "DESCRIPTION"
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
APPS.XLA_EVENTS XEV,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_AE_LINES XAL,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GJL,
APPS.GL_CODE_COMBINATIONS_KFV CC,
--APPS.AP_SUPPLIERS APS,
GL_PERIOD_STATUSES GPS
WHERE 1=1--AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
AND XEV.ENTITY_ID= XTE.ENTITY_ID
AND XAH.ENTITY_ID= XTE.ENTITY_ID
AND XAH.EVENT_ID= XEV.EVENT_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
AND GJH.STATUS = 'P'
AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
--AND AIA.VENDOR_ID=APS.VENDOR_ID
AND GJH.STATUS='P'
AND GJH.ACTUAL_FLAG='A'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
--AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
--AND CC.SEGMENT1=111
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.JE_SOURCE ='RECEIVABLES'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND XAL.ENTERED_DR='136547.55'
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)
--AND AIA.ORG_ID=:P_ORG
--AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
-- AND 24116=:P_ACCOUNT_FROM
-- AND 24116=:P_ACCOUNT_TO
AND ( NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
--AND XTE.TRANSACTION_NUMBER='101914 - ZPF TDS APRIL 12'
AND XAL.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',XAL.CURRENCY_CODE,
'NON INR',DECODE(XAL.CURRENCY_CODE,'INR','###',XAL.CURRENCY_CODE));
GJL.REFERENCE_2,
TO_CHAR (NULL) PO_ORDER_NUMBER,
TO_CHAR (NULL) "RECEIPT NUMBER",
TO_DATE (NULL) "RECEIPT DATE",
TO_CHAR (NULL) "INVOICE NUMBER",
TO_DATE (NULL) "INVOICE DATE",
TO_CHAR (NULL) "VENDOR NAME",
TO_CHAR (NULL) "VENDOR NUMBER",
JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT2,
GJH.CURRENCY_CODE,
GJL.ENTERED_DR ,
GJL.ENTERED_CR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_DR )) ACCOUNTED_DR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_CR )) ACCOUNTED_CR,
GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJL.CODE_COMBINATION_ID ,
GJH.DOC_SEQUENCE_VALUE "VOCHER NUMBER",
GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
GJL.ATTRIBUTE1 "STAFF NUMBER",
GJL.ATTRIBUTE2 "PROJECT",
GJL.ATTRIBUTE3 "CUSTOMER NUM/NAME" ,
GJL.DESCRIPTION DESCRIPTION
FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH, GL_SUMMARY_COMBINATIONS_V GSCV, GL_LEDGERS,GL_PERIOD_STATUSES PS,GL_JE_BATCHES B,
GL_LEDGER_RELATIONSHIPS LR
WHERE GSCV.CODE_COMBINATION_ID =GJL.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.PERIOD_NAME = PS.PERIOD_NAME
AND PS.LEDGER_ID = GJL.LEDGER_ID
AND B.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.LEDGER_ID = LR.SOURCE_LEDGER_ID
AND LR.SOURCE_LEDGER_ID = LR.TARGET_LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND GJH.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES','COST MANAGEMENT','CONSOLIDATION','RECEIVABLES')
AND GJH.LEDGER_ID = GL_LEDGERS.LEDGER_ID
--- AND GL_LEDGERS.SUSPENSE_ALLOWED_FLAG = 'P'
AND ( NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND ( NVL (GSCV.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GSCV.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GSCV.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GSCV.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',GJH.CURRENCY_CODE,
'NON INR',DECODE(GJH.CURRENCY_CODE,'INR','###',GJH.CURRENCY_CODE))
UNION ALL
SELECT DISTINCT B.PERIOD_NAME, D.REFERENCE_2, AAA.PO_ORDER_NUMBER, AAA.RCV_RECEIPT_NUM "RECEIPT NUMBER",
AAA.TRX_DATE "RECEIPT DATE", AAA.TRX_NUMBER_DISPLAYED "INVOICE NUMBER",
AAA.ACCOUNTING_DATE "INVOICE DATE", AAA.THIRD_PARTY_NAME "VENDOR NAME",
AAA.THIRD_PARTY_NUMBER "VENDOR NUMBER", B.JE_SOURCE "SOURCE", B.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID) SEGMENT2,
AAA.CURRENCY_CODE, AAA.ENTERED_DR, AAA.ENTERED_CR, AAA.ACCOUNTED_DR,
AAA.ACCOUNTED_CR, AAA.JE_HEADER_ID, AAA.JE_LINE_NUM, AAA.CODE_COMBINATION_ID,
AAA.DOC_SEQUENCE_VALUE "VOCHER NUMBER", AAA.GL_DATE "GL_DATE",
AAA.ATTRIBUTE1 "STAFF NUMBER", AAA.ATTRIBUTE2 "PROJECT",
AAA.ATTRIBUTE3 "CUSTOMER NUM/NAME", AAA.DESCRIPTION "DESCRIPTION"
FROM (SELECT DISTINCT POH.SEGMENT1 PO_ORDER_NUMBER, RSH.RECEIPT_NUM RCV_RECEIPT_NUM,
RCT.TRANSACTION_DATE TRX_DATE, API.INVOICE_NUM TRX_NUMBER_DISPLAYED,
RRS.ACCOUNTING_DATE ACCOUNTING_DATE,
POV.VENDOR_NAME THIRD_PARTY_NAME,
POV.SEGMENT1 THIRD_PARTY_NUMBER, RRS.CURRENCY_CODE,
RRS.ENTERED_DR, RRS.ENTERED_CR, RRS.ACCOUNTED_DR,
RRS.ACCOUNTED_CR, R.JE_HEADER_ID, R.JE_LINE_NUM,
RRS.CODE_COMBINATION_ID, 201 APPLICATION_ID,
TO_DATE (NULL) GL_DATE, TO_NUMBER (NULL) DOC_SEQUENCE_VALUE,
TO_CHAR (NULL) DESCRIPTION, TO_CHAR (NULL) ATTRIBUTE1,
TO_CHAR (NULL) ATTRIBUTE2, TO_CHAR (NULL) ATTRIBUTE3
--POH.ORG_ID
FROM PO_HEADERS_ALL POH, PO_LINES_ALL PL, PO_DISTRIBUTIONS_ALL POD
, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, RCV_TRANSACTIONS RCT, RCV_RECEIVING_SUB_LEDGER RRS
, AP_INVOICE_DISTRIBUTIONS_ALL AIDA,AP_INVOICES_ALL API , XLA_AE_HEADERS XAH , XLA_AE_LINES XAL
,GL_IMPORT_REFERENCES R
,AP_SUPPLIERS POV
WHERE 1=1
AND POH.PO_HEADER_ID=PL.PO_HEADER_ID
AND POH.PO_HEADER_ID=POD.PO_HEADER_ID
AND POH.PO_HEADER_ID= RSL.PO_HEADER_ID(+)
AND RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID=RCT.SHIPMENT_HEADER_ID(+)
AND POD.PO_DISTRIBUTION_ID= AIDA.PO_DISTRIBUTION_ID(+)
AND API.INVOICE_ID(+)=AIDA.INVOICE_ID
AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND AIDA.ACCOUNTING_EVENT_ID=XAH.EVENT_ID(+)
AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID(+)
AND XAL.GL_SL_LINK_ID= R.GL_SL_LINK_ID(+)
AND R.REFERENCE_8=XAL.AE_LINE_NUM
AND POH.VENDOR_ID=POV.VENDOR_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE(+)='ACCRUAL'
AND RRS.ACCOUNTING_LINE_TYPE='ACCRUAL'
AND XAL.ACCOUNTING_CLASS_CODE(+)='LIABILITY'
AND POD.DESTINATION_TYPE_CODE='EXPENSE'
AND EXISTS (SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,AP_INVOICES_ALL AI
WHERE AID.PO_DISTRIBUTION_ID=POD.PO_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AI.VENDOR_ID = POV.VENDOR_ID) ) AAA,
GL_JE_HEADERS B, GL_JE_LINES D,GL_JE_BATCHES E, GL_PERIOD_STATUSES PS,
GL_SUMMARY_COMBINATIONS_V CCC
WHERE B.JE_HEADER_ID = AAA.JE_HEADER_ID
AND D.JE_LINE_NUM = AAA.JE_LINE_NUM
AND B.JE_HEADER_ID=D.JE_HEADER_ID
AND D.CODE_COMBINATION_ID = CCC.CODE_COMBINATION_ID
AND AAA.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
AND PS.LEDGER_ID = D.LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND B.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES', 'PURCHASING','CONSOLIDATION')
--AND AAA.ORG_ID=:P_ORG
AND ( NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND ( NVL (CCC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CCC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND B.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND B.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',B.CURRENCY_CODE,
'NON INR',DECODE(B.CURRENCY_CODE,'INR','###',B.CURRENCY_CODE))
UNION ALL
--PO-RECEIPT-GL
SELECT GPS.PERIOD_NAME,
L.REFERENCE_2,
PHA.SEGMENT1 PO_ORDER_NUMBER,
RSH.RECEIPT_NUM "RECEIPT NUMBER",
RT.TRANSACTION_DATE "RECEIPT DATE",
NULL "INVOICE NUMBER",
NULL "INVOICE DATE",
PV.VENDOR_NAME "VENDOR NAME",
PV.SEGMENT1 "VENDOR NUMBER",
H.JE_SOURCE "SOURCE",
H.LEDGER_ID "SET_OF_BOOKS_ID" ,
GCC.CONCATENATED_SEGMENTS SEGMENT,
GCC.CONCATENATED_SEGMENTS SEGMENT2,
PHA.CURRENCY_CODE,
-- PLA.LINE_NUM,
-- PLLA.SHIPMENT_NUM,
-- PLA.ITEM_DESCRIPTION,
-- PLA.UNIT_PRICE,
-- PLLA.QUANTITY,
-- PLLA.QUANTITY_RECEIVED,
-- PLLA.QUANTITY_BILLED,
-- RT.TRANSACTION_TYPE,
--RT.TRANSACTION_DATE,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,
XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
RRSL.CODE_COMBINATION_ID,
--GPS.APPLICATION_ID,
--
TO_NUMBER (NULL) "VOCHER NUMBER",
TO_DATE (NULL) GL_DATE,
TO_CHAR (NULL) "STAFF NUMBER",
TO_CHAR (NULL) "PROJECT",
TO_CHAR (NULL) "CUSTOMER NUM/NAME" ,
--PHA.ORG_ID,
-- TO_CHAR (NULL) DESCRIPTION
RSL.ITEM_DESCRIPTION "DESCRIPTION"
FROM GL.GL_JE_HEADERS H,
GL.GL_JE_LINES L,
GL_CODE_COMBINATIONS_KFV GCC,
GL.GL_IMPORT_REFERENCES GIR,
APPS.XLA_AE_LINES XAL,
APPS.XLA_DISTRIBUTION_LINKS XDL,
PO.RCV_RECEIVING_SUB_LEDGER RRSL,
PO.RCV_TRANSACTIONS RT,
PO.PO_HEADERS_ALL PHA,
PO.PO_LINES_ALL PLA,
PO.PO_LINE_LOCATIONS_ALL PLLA,
APPS.PO_VENDORS PV,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
GL_PERIOD_STATUSES GPS
WHERE 1=1
AND H.JE_HEADER_ID = L.JE_HEADER_ID
AND L.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND ( NVL (GCC.SEGMENT5, 0) >=:P_ACCOUNT_FROM
AND (NVL (GCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GCC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_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 = RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
AND RT.PO_HEADER_ID = PLA.PO_HEADER_ID
AND RT.PO_LINE_ID = PLA.PO_LINE_ID
AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID
--AND H.LEDGER_ID = 2041--UTSC
AND H.JE_SOURCE = 'COST MANAGEMENT'
AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID =RSL.SHIPMENT_LINE_ID
AND L.PERIOD_NAME = GPS.PERIOD_NAME
AND GPS.LEDGER_ID = L.LEDGER_ID
AND GPS.APPLICATION_ID = 101
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
--AND H.JE_CATEGORY = 'RECEIVING'
--AND H.STATUS = 'P'
--AND H.PERIOD_NAME IN ('MAY-12')
AND H.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND H.CURRENCY_CODE != 'STAT'
AND H.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',H.CURRENCY_CODE,
'NON INR',DECODE(H.CURRENCY_CODE,'INR','###',H.CURRENCY_CODE))
UNION ALL
SELECT
GPS.PERIOD_NAME,
GJL.REFERENCE_2,
(SELECT DISTINCT PHA.SEGMENT1 FROM AP_INVOICE_LINES_ALL AILLA, PO_HEADERS_ALL PHA WHERE AILLA.INVOICE_ID=AIA.INVOICE_ID AND AILLA.PO_HEADER_ID=PHA.PO_HEADER_ID) PO_ORDER_NUMBER,
(SELECT DISTINCT RSH.RECEIPT_NUM
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT, AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL) ) "RECEIPT NUMBER",
(SELECT DISTINCT RT.TRANSACTION_DATE
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT, AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL)) "RECEIPT DATE",
AIA.INVOICE_NUM "INVOICE NUMBER",
AIA.GL_DATE "INVOICE DATE",
APS.VENDOR_NAME "VENDOR NAME",
APS.SEGMENT1 "VENDOR NUMBER",
GJH.JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
CC.CONCATENATED_SEGMENTS SEGMENT,
CC.CONCATENATED_SEGMENTS SEGMENT2,
AIA.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,
XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
CC.CODE_COMBINATION_ID,
--GPS.APPLICATION_ID,
--
AIA.DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,AIA.GL_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,XAL.DESCRIPTION "DESCRIPTION"
FROM APPS.AP_INVOICES_ALL AIA,
XLA.XLA_TRANSACTION_ENTITIES XTE,
APPS.XLA_EVENTS XEV,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_AE_LINES XAL,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GJL,
APPS.GL_CODE_COMBINATIONS_KFV CC,
APPS.AP_SUPPLIERS APS,
GL_PERIOD_STATUSES GPS
WHERE AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
AND XEV.ENTITY_ID= XTE.ENTITY_ID
AND XAH.ENTITY_ID= XTE.ENTITY_ID
AND XAH.EVENT_ID= XEV.EVENT_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
AND GJH.STATUS = 'P'
AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
AND AIA.VENDOR_ID=APS.VENDOR_ID
AND GJH.STATUS='P'
AND GJH.ACTUAL_FLAG='A'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
--AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
--AND CC.SEGMENT1=111
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.JE_SOURCE ='PAYABLES'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)
--AND AIA.ORG_ID=:P_ORG
AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
-- AND 24116=:P_ACCOUNT_FROM
-- AND 24116=:P_ACCOUNT_TO
AND ( NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND AIA.PAYMENT_CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',AIA.PAYMENT_CURRENCY_CODE,
'NON INR',DECODE(AIA.PAYMENT_CURRENCY_CODE,'INR','###',AIA.PAYMENT_CURRENCY_CODE))
UNION ALL
SELECT
GJH.PERIOD_NAME,
NULL REFERENCE_2,
NULL PO_ORDER_NUMBER,
NULL "RECEIPT NUMBER",
NULL "RECEIPT DATE",
ENT.TRANSACTION_NUMBER "INVOICE NUMBER",
AIA.CHECK_DATE "INVOICE DATE",
AV.VENDOR_NAME "VENDOR NAME",
AV.SEGMENT1 "VENDOR NUMBER",
GJH.JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
GCCK.CONCATENATED_SEGMENTS SEGMENT,
GCCK.CONCATENATED_SEGMENTS SEGMENT21,
AIA.CURRENCY_CODE CURRENCY_CODE
,AEL.ENTERED_DR,
AEL.ENTERED_CR,
AEL.ACCOUNTED_DR,
AEL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
GCCK.CODE_COMBINATION_ID
,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,AEH.ACCOUNTING_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,AEL.DESCRIPTION "DESCRIPTION"
FROM
XLA_AE_HEADERS AEH
,XLA_AE_LINES AEL
,XLA_EVENTS XLE
,XLA_EVENT_TYPES_TL XET
,XLA_TRANSACTION_ENTITIES ENT
,GL_LEDGERS GLG
,GL_PERIODS GLP
,XLA_SUBLEDGERS XLS
,GL_CODE_COMBINATIONS_KFV GCCK
,GL_IMPORT_REFERENCES GIR
,GL_JE_LINES GJL
,GL_JE_HEADERS GJH
,GL_JE_BATCHES GJB
--,AP_INVOICE_DISTRIBUTIONS_ALL AID
-- ,AP_INVOICE_LINES_ALL AILA
,AP_CHECKS_ALL AIA
,GL_PERIOD_STATUSES GPS
,AP_SUPPLIERS AV
--,PA_PROJECT_CUSTOMERS_V PPCV
--,PA_PROJECTS_ALL PPA
WHERE 1=1
AND AEH.LEDGER_ID = GLG.LEDGER_ID
AND AEL.APPLICATION_ID = AEH.APPLICATION_ID
AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XLE.APPLICATION_ID = AEH.APPLICATION_ID
AND XLE.EVENT_ID = AEH.EVENT_ID
AND XET.APPLICATION_ID = XLE.APPLICATION_ID
AND XET.EVENT_TYPE_CODE = XLE.EVENT_TYPE_CODE
AND XET.LANGUAGE = USERENV('LANG')
AND ENT.APPLICATION_ID = AEH.APPLICATION_ID
AND ENT.ENTITY_ID = AEH.ENTITY_ID
AND GLP.PERIOD_NAME = AEH.PERIOD_NAME
AND GLP.PERIOD_SET_NAME = GLG.PERIOD_SET_NAME
AND XLS.APPLICATION_ID = AEH.APPLICATION_ID
AND GCCK.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE <> 'N' AND
AEH.APPLICATION_ID = 200 AND AEH.BALANCE_TYPE_CODE = 'A' AND (
NVL(AEL.ACCOUNTED_CR,0) <> 0
OR NVL(AEL.ACCOUNTED_DR,0) <> 0 )
AND GIR.GL_SL_LINK_ID(+) = AEL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE(+) = AEL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM(+) = GIR.JE_LINE_NUM
AND GJH.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
AND GJB.JE_BATCH_ID (+) = GIR.JE_BATCH_ID
AND DECODE(GJH.JE_HEADER_ID,NULL,'Y',GJH.JE_FROM_SLA_FLAG) IN ('U', 'Y')
--AND AEL.ACCOUNTING_CLASS_CODE IN ('ITEM EXPENSE','CASH_CLEARING','LIABILITY','PREPAID_EXPENSE','RTAX','INTRA')
AND ENT.SOURCE_ID_INT_1 = AIA.CHECK_ID
-- AND AIA.DOC_SEQUENCE_VALUE=GIR.SUBLEDGER_DOC_SEQUENCE_VALUE
AND AIA.VENDOR_ID=AV.VENDOR_ID
AND GJH.JE_SOURCE ='PAYABLES'
AND AEH.JE_CATEGORY_NAME='PAYMENTS'
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND AIA.ORG_ID=:P_ORG
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
AND ( NVL (GCCK.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GCCK.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GCCK.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GCCK.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND AIA.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',AIA.CURRENCY_CODE,
'NON INR',DECODE(AIA.CURRENCY_CODE,'INR','###',AIA.CURRENCY_CODE))
UNION ALL
--ADDED BY MANOHAR
SELECT GJH.PERIOD_NAME,
NULL REFERENCE_2,
NULL PO_ORDER_NUMBER,
CASE
WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
XTE.TRANSACTION_NUMBER
ELSE
NULL
END "RECEIPT NUMBER",
CASE
WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
XAH.ACCOUNTING_DATE
ELSE
NULL
END "RECEIPT DATE",
CASE
WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
XTE.TRANSACTION_NUMBER ELSE
NULL
END "INVOICE NUMBER",
CASE
WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
XAH.ACCOUNTING_DATE
ELSE
NULL
END "INVOICE DATE",
CASE
WHEN XAL.PARTY_TYPE_CODE = 'S' THEN
(SELECT APS.SEGMENT1
||'|'||APS.VENDOR_NAME
||'|'||HZP.JGZZ_FISCAL_CODE
||'|'||HZP.TAX_REFERENCE
||'|'||HPS.PARTY_SITE_NUMBER
||'|'||HPS.PARTY_SITE_NAME
||'|'||NULL
FROM AP_SUPPLIERS APS
,AP_SUPPLIER_SITES_ALL APSS
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE APS.VENDOR_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = APS.PARTY_ID
AND APSS.VENDOR_SITE_ID(+) = AEL2.PARTY_SITE_ID
AND HPS.PARTY_SITE_ID(+) = APSS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
(SELECT --HCA.ACCOUNT_NUMBER||'|'||
HZP.PARTY_NAME
--||'|'||HZP.JGZZ_FISCAL_CODE
--||'|'||HZP.TAX_REFERENCE
--||'|'||HPS.PARTY_SITE_NUMBER
--||'|'||HPS.PARTY_SITE_NAME
--||'|'||HZCU.TAX_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_CUST_SITE_USES_ALL HZCU
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
ELSE
NULL
END "VENDOR NAME",
CASE
WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
(SELECT HCA.ACCOUNT_NUMBER
--||'|'|| HZP.PARTY_NAME
--||'|'||HZP.JGZZ_FISCAL_CODE
--||'|'||HZP.TAX_REFERENCE
--||'|'||HPS.PARTY_SITE_NUMBER
--||'|'||HPS.PARTY_SITE_NAME
--||'|'||HZCU.TAX_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_CUST_SITE_USES_ALL HZCU
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
ELSE
NULL
END "VENDOR NUMBER",
GJH.JE_SOURCE"SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
CC.CONCATENATED_SEGMENTS SEGMENT,
CC.CONCATENATED_SEGMENTS SEGMENT21,
XAL.CURRENCY_CODE CURRENCY_CODE,
XAL.ENTERED_DR,XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
CC.CODE_COMBINATION_ID
,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,XAH.ACCOUNTING_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,XAH.DESCRIPTION "DESCRIPTION"
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
APPS.XLA_EVENTS XEV,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_AE_LINES XAL,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GJL,
APPS.GL_CODE_COMBINATIONS_KFV CC,
--APPS.AP_SUPPLIERS APS,
GL_PERIOD_STATUSES GPS
WHERE 1=1--AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
AND XEV.ENTITY_ID= XTE.ENTITY_ID
AND XAH.ENTITY_ID= XTE.ENTITY_ID
AND XAH.EVENT_ID= XEV.EVENT_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
AND GJH.STATUS = 'P'
AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
--AND AIA.VENDOR_ID=APS.VENDOR_ID
AND GJH.STATUS='P'
AND GJH.ACTUAL_FLAG='A'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
--AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
--AND CC.SEGMENT1=111
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.JE_SOURCE ='RECEIVABLES'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND XAL.ENTERED_DR='136547.55'
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)
--AND AIA.ORG_ID=:P_ORG
--AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
-- AND 24116=:P_ACCOUNT_FROM
-- AND 24116=:P_ACCOUNT_TO
AND ( NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
--AND XTE.TRANSACTION_NUMBER='101914 - ZPF TDS APRIL 12'
AND XAL.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',XAL.CURRENCY_CODE,
'NON INR',DECODE(XAL.CURRENCY_CODE,'INR','###',XAL.CURRENCY_CODE));
SLA or XLA(AP-GL,RECEIPT-GL,AR-GL)(SubledgerAccounting)
SELECT DISTINCT PS.PERIOD_NAME,
GJL.REFERENCE_2,
TO_CHAR (NULL) PO_ORDER_NUMBER,
TO_CHAR (NULL) "RECEIPT NUMBER",
TO_DATE (NULL) "RECEIPT DATE",
TO_CHAR (NULL) "INVOICE NUMBER",
TO_DATE (NULL) "INVOICE DATE",
TO_CHAR (NULL) "VENDOR NAME",
TO_CHAR (NULL) "VENDOR NUMBER",
JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT2,
GJH.CURRENCY_CODE,
GJL.ENTERED_DR ,
GJL.ENTERED_CR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_DR )) ACCOUNTED_DR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_CR )) ACCOUNTED_CR,
GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJL.CODE_COMBINATION_ID ,
GJH.DOC_SEQUENCE_VALUE "VOCHER NUMBER",
GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
GJL.ATTRIBUTE1 "STAFF NUMBER",
GJL.ATTRIBUTE2 "PROJECT",
GJL.ATTRIBUTE3 "CUSTOMER NUM/NAME" ,
GJL.DESCRIPTION DESCRIPTION
FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH, GL_SUMMARY_COMBINATIONS_V GSCV, GL_LEDGERS,GL_PERIOD_STATUSES PS,GL_JE_BATCHES B,
GL_LEDGER_RELATIONSHIPS LR
WHERE GSCV.CODE_COMBINATION_ID =GJL.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.PERIOD_NAME = PS.PERIOD_NAME
AND PS.LEDGER_ID = GJL.LEDGER_ID
AND B.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.LEDGER_ID = LR.SOURCE_LEDGER_ID
AND LR.SOURCE_LEDGER_ID = LR.TARGET_LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND GJH.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES','COST MANAGEMENT','CONSOLIDATION','RECEIVABLES')
AND GJH.LEDGER_ID = GL_LEDGERS.LEDGER_ID
--- AND GL_LEDGERS.SUSPENSE_ALLOWED_FLAG = 'P'
AND ( NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND ( NVL (GSCV.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GSCV.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GSCV.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GSCV.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',GJH.CURRENCY_CODE,
'NON INR',DECODE(GJH.CURRENCY_CODE,'INR','###',GJH.CURRENCY_CODE))
UNION ALL
SELECT DISTINCT B.PERIOD_NAME, D.REFERENCE_2, AAA.PO_ORDER_NUMBER, AAA.RCV_RECEIPT_NUM "RECEIPT NUMBER",
AAA.TRX_DATE "RECEIPT DATE", AAA.TRX_NUMBER_DISPLAYED "INVOICE NUMBER",
AAA.ACCOUNTING_DATE "INVOICE DATE", AAA.THIRD_PARTY_NAME "VENDOR NAME",
AAA.THIRD_PARTY_NUMBER "VENDOR NUMBER", B.JE_SOURCE "SOURCE", B.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID) SEGMENT2,
AAA.CURRENCY_CODE, AAA.ENTERED_DR, AAA.ENTERED_CR, AAA.ACCOUNTED_DR,
AAA.ACCOUNTED_CR, AAA.JE_HEADER_ID, AAA.JE_LINE_NUM, AAA.CODE_COMBINATION_ID,
AAA.DOC_SEQUENCE_VALUE "VOCHER NUMBER", AAA.GL_DATE "GL_DATE",
AAA.ATTRIBUTE1 "STAFF NUMBER", AAA.ATTRIBUTE2 "PROJECT",
AAA.ATTRIBUTE3 "CUSTOMER NUM/NAME", AAA.DESCRIPTION "DESCRIPTION"
FROM (SELECT DISTINCT POH.SEGMENT1 PO_ORDER_NUMBER, RSH.RECEIPT_NUM RCV_RECEIPT_NUM,
RCT.TRANSACTION_DATE TRX_DATE, API.INVOICE_NUM TRX_NUMBER_DISPLAYED,
RRS.ACCOUNTING_DATE ACCOUNTING_DATE,
POV.VENDOR_NAME THIRD_PARTY_NAME,
POV.SEGMENT1 THIRD_PARTY_NUMBER, RRS.CURRENCY_CODE,
RRS.ENTERED_DR, RRS.ENTERED_CR, RRS.ACCOUNTED_DR,
RRS.ACCOUNTED_CR, R.JE_HEADER_ID, R.JE_LINE_NUM,
RRS.CODE_COMBINATION_ID, 201 APPLICATION_ID,
TO_DATE (NULL) GL_DATE, TO_NUMBER (NULL) DOC_SEQUENCE_VALUE,
TO_CHAR (NULL) DESCRIPTION, TO_CHAR (NULL) ATTRIBUTE1,
TO_CHAR (NULL) ATTRIBUTE2, TO_CHAR (NULL) ATTRIBUTE3
--POH.ORG_ID
FROM PO_HEADERS_ALL POH, PO_LINES_ALL PL, PO_DISTRIBUTIONS_ALL POD
, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, RCV_TRANSACTIONS RCT, RCV_RECEIVING_SUB_LEDGER RRS
, AP_INVOICE_DISTRIBUTIONS_ALL AIDA,AP_INVOICES_ALL API , XLA_AE_HEADERS XAH , XLA_AE_LINES XAL
,GL_IMPORT_REFERENCES R
,AP_SUPPLIERS POV
WHERE 1=1
AND POH.PO_HEADER_ID=PL.PO_HEADER_ID
AND POH.PO_HEADER_ID=POD.PO_HEADER_ID
AND POH.PO_HEADER_ID= RSL.PO_HEADER_ID(+)
AND RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID=RCT.SHIPMENT_HEADER_ID(+)
AND POD.PO_DISTRIBUTION_ID= AIDA.PO_DISTRIBUTION_ID(+)
AND API.INVOICE_ID(+)=AIDA.INVOICE_ID
AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND AIDA.ACCOUNTING_EVENT_ID=XAH.EVENT_ID(+)
AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID(+)
AND XAL.GL_SL_LINK_ID= R.GL_SL_LINK_ID(+)
AND R.REFERENCE_8=XAL.AE_LINE_NUM
AND POH.VENDOR_ID=POV.VENDOR_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE(+)='ACCRUAL'
AND RRS.ACCOUNTING_LINE_TYPE='ACCRUAL'
AND XAL.ACCOUNTING_CLASS_CODE(+)='LIABILITY'
AND POD.DESTINATION_TYPE_CODE='EXPENSE'
AND EXISTS (SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,AP_INVOICES_ALL AI
WHERE AID.PO_DISTRIBUTION_ID=POD.PO_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AI.VENDOR_ID = POV.VENDOR_ID) ) AAA,
GL_JE_HEADERS B, GL_JE_LINES D,GL_JE_BATCHES E, GL_PERIOD_STATUSES PS,
GL_SUMMARY_COMBINATIONS_V CCC
WHERE B.JE_HEADER_ID = AAA.JE_HEADER_ID
AND D.JE_LINE_NUM = AAA.JE_LINE_NUM
AND B.JE_HEADER_ID=D.JE_HEADER_ID
AND D.CODE_COMBINATION_ID = CCC.CODE_COMBINATION_ID
AND AAA.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
AND PS.LEDGER_ID = D.LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND B.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES', 'PURCHASING','CONSOLIDATION')
--AND AAA.ORG_ID=:P_ORG
AND ( NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND ( NVL (CCC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CCC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND B.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND B.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',B.CURRENCY_CODE,
'NON INR',DECODE(B.CURRENCY_CODE,'INR','###',B.CURRENCY_CODE))
UNION ALL
--PO-RECEIPT-GL
SELECT GPS.PERIOD_NAME,
L.REFERENCE_2,
PHA.SEGMENT1 PO_ORDER_NUMBER,
RSH.RECEIPT_NUM "RECEIPT NUMBER",
RT.TRANSACTION_DATE "RECEIPT DATE",
NULL "INVOICE NUMBER",
NULL "INVOICE DATE",
PV.VENDOR_NAME "VENDOR NAME",
PV.SEGMENT1 "VENDOR NUMBER",
H.JE_SOURCE "SOURCE",
H.LEDGER_ID "SET_OF_BOOKS_ID" ,
GCC.CONCATENATED_SEGMENTS SEGMENT,
GCC.CONCATENATED_SEGMENTS SEGMENT2,
PHA.CURRENCY_CODE,
-- PLA.LINE_NUM,
-- PLLA.SHIPMENT_NUM,
-- PLA.ITEM_DESCRIPTION,
-- PLA.UNIT_PRICE,
-- PLLA.QUANTITY,
-- PLLA.QUANTITY_RECEIVED,
-- PLLA.QUANTITY_BILLED,
-- RT.TRANSACTION_TYPE,
--RT.TRANSACTION_DATE,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,
XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
RRSL.CODE_COMBINATION_ID,
--GPS.APPLICATION_ID,
--
TO_NUMBER (NULL) "VOCHER NUMBER",
TO_DATE (NULL) GL_DATE,
TO_CHAR (NULL) "STAFF NUMBER",
TO_CHAR (NULL) "PROJECT",
TO_CHAR (NULL) "CUSTOMER NUM/NAME" ,
--PHA.ORG_ID,
-- TO_CHAR (NULL) DESCRIPTION
RSL.ITEM_DESCRIPTION "DESCRIPTION"
FROM GL.GL_JE_HEADERS H,
GL.GL_JE_LINES L,
GL_CODE_COMBINATIONS_KFV GCC,
GL.GL_IMPORT_REFERENCES GIR,
APPS.XLA_AE_LINES XAL,
APPS.XLA_DISTRIBUTION_LINKS XDL,
PO.RCV_RECEIVING_SUB_LEDGER RRSL,
PO.RCV_TRANSACTIONS RT,
PO.PO_HEADERS_ALL PHA,
PO.PO_LINES_ALL PLA,
PO.PO_LINE_LOCATIONS_ALL PLLA,
APPS.PO_VENDORS PV,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
GL_PERIOD_STATUSES GPS
WHERE 1=1
AND H.JE_HEADER_ID = L.JE_HEADER_ID
AND L.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND ( NVL (GCC.SEGMENT5, 0) >=:P_ACCOUNT_FROM
AND (NVL (GCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GCC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_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 = RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
AND RT.PO_HEADER_ID = PLA.PO_HEADER_ID
AND RT.PO_LINE_ID = PLA.PO_LINE_ID
AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID
--AND H.LEDGER_ID = 2041--UTSC
AND H.JE_SOURCE = 'COST MANAGEMENT'
AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID =RSL.SHIPMENT_LINE_ID
AND L.PERIOD_NAME = GPS.PERIOD_NAME
AND GPS.LEDGER_ID = L.LEDGER_ID
AND GPS.APPLICATION_ID = 101
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
--AND H.JE_CATEGORY = 'RECEIVING'
--AND H.STATUS = 'P'
--AND H.PERIOD_NAME IN ('MAY-12')
AND H.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND H.CURRENCY_CODE != 'STAT'
AND H.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',H.CURRENCY_CODE,
'NON INR',DECODE(H.CURRENCY_CODE,'INR','###',H.CURRENCY_CODE))
UNION ALL
SELECT
GPS.PERIOD_NAME,
GJL.REFERENCE_2,
(SELECT DISTINCT PHA.SEGMENT1 FROM AP_INVOICE_LINES_ALL AILLA, PO_HEADERS_ALL PHA WHERE AILLA.INVOICE_ID=AIA.INVOICE_ID AND AILLA.PO_HEADER_ID=PHA.PO_HEADER_ID) PO_ORDER_NUMBER,
(SELECT DISTINCT RSH.RECEIPT_NUM
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT, AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL) ) "RECEIPT NUMBER",
(SELECT DISTINCT RT.TRANSACTION_DATE
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT, AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL)) "RECEIPT DATE",
AIA.INVOICE_NUM "INVOICE NUMBER",
AIA.GL_DATE "INVOICE DATE",
APS.VENDOR_NAME "VENDOR NAME",
APS.SEGMENT1 "VENDOR NUMBER",
GJH.JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
CC.CONCATENATED_SEGMENTS SEGMENT,
CC.CONCATENATED_SEGMENTS SEGMENT2,
AIA.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,
XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
CC.CODE_COMBINATION_ID,
--GPS.APPLICATION_ID,
--
AIA.DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,AIA.GL_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,XAL.DESCRIPTION "DESCRIPTION"
FROM APPS.AP_INVOICES_ALL AIA,
XLA.XLA_TRANSACTION_ENTITIES XTE,
APPS.XLA_EVENTS XEV,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_AE_LINES XAL,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GJL,
APPS.GL_CODE_COMBINATIONS_KFV CC,
APPS.AP_SUPPLIERS APS,
GL_PERIOD_STATUSES GPS
WHERE AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
AND XEV.ENTITY_ID= XTE.ENTITY_ID
AND XAH.ENTITY_ID= XTE.ENTITY_ID
AND XAH.EVENT_ID= XEV.EVENT_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
AND GJH.STATUS = 'P'
AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
AND AIA.VENDOR_ID=APS.VENDOR_ID
AND GJH.STATUS='P'
AND GJH.ACTUAL_FLAG='A'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
--AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
--AND CC.SEGMENT1=111
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.JE_SOURCE ='PAYABLES'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)
--AND AIA.ORG_ID=:P_ORG
AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
-- AND 24116=:P_ACCOUNT_FROM
-- AND 24116=:P_ACCOUNT_TO
AND ( NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND AIA.PAYMENT_CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',AIA.PAYMENT_CURRENCY_CODE,
'NON INR',DECODE(AIA.PAYMENT_CURRENCY_CODE,'INR','###',AIA.PAYMENT_CURRENCY_CODE))
UNION ALL
SELECT
GJH.PERIOD_NAME,
NULL REFERENCE_2,
NULL PO_ORDER_NUMBER,
NULL "RECEIPT NUMBER",
NULL "RECEIPT DATE",
ENT.TRANSACTION_NUMBER "INVOICE NUMBER",
AIA.CHECK_DATE "INVOICE DATE",
AV.VENDOR_NAME "VENDOR NAME",
AV.SEGMENT1 "VENDOR NUMBER",
GJH.JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
GCCK.CONCATENATED_SEGMENTS SEGMENT,
GCCK.CONCATENATED_SEGMENTS SEGMENT21,
AIA.CURRENCY_CODE CURRENCY_CODE
,AEL.ENTERED_DR,
AEL.ENTERED_CR,
AEL.ACCOUNTED_DR,
AEL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
GCCK.CODE_COMBINATION_ID
,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,AEH.ACCOUNTING_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,AEL.DESCRIPTION "DESCRIPTION"
FROM
XLA_AE_HEADERS AEH
,XLA_AE_LINES AEL
,XLA_EVENTS XLE
,XLA_EVENT_TYPES_TL XET
,XLA_TRANSACTION_ENTITIES ENT
,GL_LEDGERS GLG
,GL_PERIODS GLP
,XLA_SUBLEDGERS XLS
,GL_CODE_COMBINATIONS_KFV GCCK
,GL_IMPORT_REFERENCES GIR
,GL_JE_LINES GJL
,GL_JE_HEADERS GJH
,GL_JE_BATCHES GJB
--,AP_INVOICE_DISTRIBUTIONS_ALL AID
-- ,AP_INVOICE_LINES_ALL AILA
,AP_CHECKS_ALL AIA
,GL_PERIOD_STATUSES GPS
,AP_SUPPLIERS AV
--,PA_PROJECT_CUSTOMERS_V PPCV
--,PA_PROJECTS_ALL PPA
WHERE 1=1
AND AEH.LEDGER_ID = GLG.LEDGER_ID
AND AEL.APPLICATION_ID = AEH.APPLICATION_ID
AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XLE.APPLICATION_ID = AEH.APPLICATION_ID
AND XLE.EVENT_ID = AEH.EVENT_ID
AND XET.APPLICATION_ID = XLE.APPLICATION_ID
AND XET.EVENT_TYPE_CODE = XLE.EVENT_TYPE_CODE
AND XET.LANGUAGE = USERENV('LANG')
AND ENT.APPLICATION_ID = AEH.APPLICATION_ID
AND ENT.ENTITY_ID = AEH.ENTITY_ID
AND GLP.PERIOD_NAME = AEH.PERIOD_NAME
AND GLP.PERIOD_SET_NAME = GLG.PERIOD_SET_NAME
AND XLS.APPLICATION_ID = AEH.APPLICATION_ID
AND GCCK.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE <> 'N' AND
AEH.APPLICATION_ID = 200 AND AEH.BALANCE_TYPE_CODE = 'A' AND (
NVL(AEL.ACCOUNTED_CR,0) <> 0
OR NVL(AEL.ACCOUNTED_DR,0) <> 0 )
AND GIR.GL_SL_LINK_ID(+) = AEL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE(+) = AEL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM(+) = GIR.JE_LINE_NUM
AND GJH.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
AND GJB.JE_BATCH_ID (+) = GIR.JE_BATCH_ID
AND DECODE(GJH.JE_HEADER_ID,NULL,'Y',GJH.JE_FROM_SLA_FLAG) IN ('U', 'Y')
--AND AEL.ACCOUNTING_CLASS_CODE IN ('ITEM EXPENSE','CASH_CLEARING','LIABILITY','PREPAID_EXPENSE','RTAX','INTRA')
AND ENT.SOURCE_ID_INT_1 = AIA.CHECK_ID
-- AND AIA.DOC_SEQUENCE_VALUE=GIR.SUBLEDGER_DOC_SEQUENCE_VALUE
AND AIA.VENDOR_ID=AV.VENDOR_ID
AND GJH.JE_SOURCE ='PAYABLES'
AND AEH.JE_CATEGORY_NAME='PAYMENTS'
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND AIA.ORG_ID=:P_ORG
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
AND ( NVL (GCCK.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GCCK.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GCCK.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GCCK.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND AIA.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',AIA.CURRENCY_CODE,
'NON INR',DECODE(AIA.CURRENCY_CODE,'INR','###',AIA.CURRENCY_CODE))
UNION ALL
--ADDED BY MANOHAR
SELECT GJH.PERIOD_NAME,
NULL REFERENCE_2,
NULL PO_ORDER_NUMBER,
CASE
WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
XTE.TRANSACTION_NUMBER
ELSE
NULL
END "RECEIPT NUMBER",
CASE
WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
XAH.ACCOUNTING_DATE
ELSE
NULL
END "RECEIPT DATE",
CASE
WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
XTE.TRANSACTION_NUMBER ELSE
NULL
END "INVOICE NUMBER",
CASE
WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
XAH.ACCOUNTING_DATE
ELSE
NULL
END "INVOICE DATE",
CASE
WHEN XAL.PARTY_TYPE_CODE = 'S' THEN
(SELECT APS.SEGMENT1
||'|'||APS.VENDOR_NAME
||'|'||HZP.JGZZ_FISCAL_CODE
||'|'||HZP.TAX_REFERENCE
||'|'||HPS.PARTY_SITE_NUMBER
||'|'||HPS.PARTY_SITE_NAME
||'|'||NULL
FROM AP_SUPPLIERS APS
,AP_SUPPLIER_SITES_ALL APSS
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE APS.VENDOR_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = APS.PARTY_ID
AND APSS.VENDOR_SITE_ID(+) = AEL2.PARTY_SITE_ID
AND HPS.PARTY_SITE_ID(+) = APSS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
(SELECT --HCA.ACCOUNT_NUMBER||'|'||
HZP.PARTY_NAME
--||'|'||HZP.JGZZ_FISCAL_CODE
--||'|'||HZP.TAX_REFERENCE
--||'|'||HPS.PARTY_SITE_NUMBER
--||'|'||HPS.PARTY_SITE_NAME
--||'|'||HZCU.TAX_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_CUST_SITE_USES_ALL HZCU
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
ELSE
NULL
END "VENDOR NAME",
CASE
WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
(SELECT HCA.ACCOUNT_NUMBER
--||'|'|| HZP.PARTY_NAME
--||'|'||HZP.JGZZ_FISCAL_CODE
--||'|'||HZP.TAX_REFERENCE
--||'|'||HPS.PARTY_SITE_NUMBER
--||'|'||HPS.PARTY_SITE_NAME
--||'|'||HZCU.TAX_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_CUST_SITE_USES_ALL HZCU
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
ELSE
NULL
END "VENDOR NUMBER",
GJH.JE_SOURCE"SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
CC.CONCATENATED_SEGMENTS SEGMENT,
CC.CONCATENATED_SEGMENTS SEGMENT21,
XAL.CURRENCY_CODE CURRENCY_CODE,
XAL.ENTERED_DR,XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
CC.CODE_COMBINATION_ID
,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,XAH.ACCOUNTING_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,XAH.DESCRIPTION "DESCRIPTION"
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
APPS.XLA_EVENTS XEV,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_AE_LINES XAL,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GJL,
APPS.GL_CODE_COMBINATIONS_KFV CC,
--APPS.AP_SUPPLIERS APS,
GL_PERIOD_STATUSES GPS
WHERE 1=1--AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
AND XEV.ENTITY_ID= XTE.ENTITY_ID
AND XAH.ENTITY_ID= XTE.ENTITY_ID
AND XAH.EVENT_ID= XEV.EVENT_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
AND GJH.STATUS = 'P'
AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
--AND AIA.VENDOR_ID=APS.VENDOR_ID
AND GJH.STATUS='P'
AND GJH.ACTUAL_FLAG='A'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
--AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
--AND CC.SEGMENT1=111
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.JE_SOURCE ='RECEIVABLES'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND XAL.ENTERED_DR='136547.55'
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)
--AND AIA.ORG_ID=:P_ORG
--AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
-- AND 24116=:P_ACCOUNT_FROM
-- AND 24116=:P_ACCOUNT_TO
AND ( NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
--AND XTE.TRANSACTION_NUMBER='101914 - ZPF TDS APRIL 12'
AND XAL.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',XAL.CURRENCY_CODE,
'NON INR',DECODE(XAL.CURRENCY_CODE,'INR','###',XAL.CURRENCY_CODE));
No comments:
Post a Comment
Please review my topic and update your comments
Note: only a member of this blog may post a comment.