View: PA_PWP_LINK_STATUS_V

Product:	PA - Projects
Description:	This view gives details of supplier invoices linked to a draft invoice
Implementation/DBA Data:	APPS.PA_PWP_LINK_STATUS_V

View Text

SELECT PWP.PROJECT_ID
, PWP.DRAFT_INVOICE_NUM
, COUNT(DISTINCT PWP.AP_INVOICE_ID) INVOICE_COUNT
, SUM(PROJFUNC_INVOICE_AMOUNT) PFC_AMOUNT
, SUM(PROJ_INVOICE_AMOUNT) PC_AMOUNT
FROM PA_PWP_LINKED_INVOICES PWP
, PA_PWP_AP_INV_DTL INV_DTL
WHERE PWP.AP_INVOICE_ID = INV_DTL.INVOICE_ID
AND INV_DTL.PROJECT_ID = PWP.PROJECT_ID GROUP BY PWP.PROJECT_ID
, PWP.DRAFT_INVOICE_NUM UNION SELECT PDI.PROJECT_ID
, PDI.DRAFT_INVOICE_NUM
, COUNT(DISTINCT EI.DOCUMENT_HEADER_ID) INVOICE_COUNT
, INV_DTL.PFC_AMT PFC_AMOUNT
, INV_DTL.PC_AMT PC_AMOUNT
FROM PA_DRAFT_INVOICES PDI
, PA_DRAFT_INVOICE_ITEMS PDII
, PA_CUST_REV_DIST_LINES CRDL
, PA_EXPENDITURE_ITEMS EI
, (SELECT INVOICE_ID
, PROJECT_ID
, SUM(PROJFUNC_INVOICE_AMOUNT) PFC_AMT
, SUM(PROJ_INVOICE_AMOUNT) PC_AMT
FROM PA_PWP_AP_INV_DTL GROUP BY INVOICE_ID
, PROJECT_ID) INV_DTL
WHERE PDI.PROJECT_ID = PDII.PROJECT_ID
AND PDI.DRAFT_INVOICE_NUM = PDII.DRAFT_INVOICE_NUM
AND PDII.PROJECT_ID = CRDL.PROJECT_ID
AND PDII.DRAFT_INVOICE_NUM = CRDL.DRAFT_INVOICE_NUM
AND PDII.LINE_NUM = CRDL.DRAFT_INVOICE_ITEM_LINE_NUM
AND CRDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND EI.DOCUMENT_HEADER_ID IS NOT NULL
AND EI.SYSTEM_LINKAGE_FUNCTION = 'VI'
AND INV_DTL.PROJECT_ID = EI.PROJECT_ID
AND INV_DTL.INVOICE_ID = EI.DOCUMENT_HEADER_ID GROUP BY PDI.PROJECT_ID
, PDI.DRAFT_INVOICE_NUM
, INV_DTL.PFC_AMT
, INV_DTL.PC_AMT

Columns