VIEW: APPS.PA_PWP_LINK_STATUS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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