View: PA_PWP_SUPPLIER_INV_SUMMARY_V

Product:	PA - Projects
Description:	This view displays supplier wise invoice amount for the project.
Implementation/DBA Data:	APPS.PA_PWP_SUPPLIER_INV_SUMMARY_V

View Text

SELECT HDR.VENDOR_ID VENDOR_ID
, HDR.SUPPLIER_NUM SUPPLIER_NUM
, HDR.SUPPLIER_NAME SUPPLIER_NAME
, NULL SUPPLIER_SITE
, DTL.PROJECT_ID PROJECT_ID
, DTL.PROJFUNC_CURRENCY_CODE PROJFUNC_CURRENCY_CODE
, DTL.PROJ_CURRENCY_CODE PROJ_CURRENCY_CODE
, NULL INVOICE_CURRENCY
, (SUM(PROJFUNC_INVOICE_AMOUNT) ) PROJFUNC_INVOICE_AMOUNT
, SUM(PROJFUNC_INV_PAID_AMOUNT) PROJFUNC_INV_PAID_AMOUNT
, SUM(PROJFUNC_INV_UNPAID_AMOUNT) PROJFUNC_INV_UNPAID_AMOUNT
, SUM(PROJFUNC_RETAINED_AMOUNT) PROJFUNC_RETAINED_AMOUNT
, SUM(PROJFUNC_DISCOUNT_AMOUNT) PROJFUNC_DISCOUNT_AMOUNT
, (SUM(PROJ_INVOICE_AMOUNT) ) PROJ_INVOICE_AMOUNT
, SUM(PROJ_INV_PAID_AMOUNT) PROJ_INV_PAID_AMOUNT
, SUM(PROJ_INV_UNPAID_AMOUNT) PROJ_INV_UNPAID_AMOUNT
, SUM(PROJ_RETAINED_AMOUNT) PROJ_RETAINED_AMOUNT
, SUM(PROJ_DISCOUNT_AMOUNT) PROJ_DISCOUNT_AMOUNT
, (SUM(PROJINV_TOT_AMOUNT) ) INVOICE_AMOUNT
, SUM(AMOUNT_PAID) AMOUNT_PAID
, SUM(AMOUNT_UNPAID) AMOUNT_UNPAID
, SUM(RETAINED_AMOUNT) RETAINED_AMOUNT
, SUM(DISCOUNT_AMOUNT) DISCOUNT_AMOUNT
, (SELECT SUM(PROJFUNC_INVOICE_AMOUNT)
FROM PA_PWP_AP_INV_DTL DTL1
, PA_PWP_AP_INV_HDR HDR1
WHERE HDR1.PA_PWP_AP_HDR_ID = DTL1.PA_PWP_AP_HDR_ID
AND HDR1.HOLD_FLAG = 'Y'
AND HDR1.VENDOR_ID = HDR.VENDOR_ID
AND HDR1.PROJECT_ID = HDR.PROJECT_ID
AND DTL1.PROJFUNC_CURRENCY_CODE = DTL.PROJFUNC_CURRENCY_CODE) PROJFUNC_INVAMT_ONHOLD
, (SELECT SUM(PROJ_INVOICE_AMOUNT)
FROM PA_PWP_AP_INV_DTL DTL1
, PA_PWP_AP_INV_HDR HDR1
WHERE HDR1.PA_PWP_AP_HDR_ID = DTL1.PA_PWP_AP_HDR_ID
AND HDR1.HOLD_FLAG = 'Y'
AND HDR1.VENDOR_ID = HDR.VENDOR_ID
AND HDR1.PROJECT_ID = HDR.PROJECT_ID
AND DTL1.PROJ_CURRENCY_CODE = DTL.PROJ_CURRENCY_CODE) PROJ_INVAMT_ONHOLD
, (SELECT SUM(PROJFUNC_INVOICE_AMOUNT)
FROM PA_PWP_AP_INV_DTL DTL1
, PA_PWP_AP_INV_HDR HDR1
WHERE HDR1.PA_PWP_AP_HDR_ID = DTL1.PA_PWP_AP_HDR_ID
AND HDR1.VENDOR_ID = HDR.VENDOR_ID
AND HDR1.PROJECT_ID = HDR.PROJECT_ID
AND DTL1.PROJFUNC_CURRENCY_CODE = DTL.PROJFUNC_CURRENCY_CODE
AND EXISTS (SELECT 1
FROM GL_PERIOD_STATUSES GLP
, PA_PROJECTS_ALL PROJ
, PA_IMPLEMENTATIONS_ALL IMP
WHERE APPLICATION_ID = 101
AND PROJ.PROJECT_ID = HDR.PROJECT_ID
AND PROJ.ORG_ID = IMP.ORG_ID
AND IMP.SET_OF_BOOKS_ID = GLP.SET_OF_BOOKS_ID
AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GLP.CLOSING_STATUS = 'O' GROUP BY APPLICATION_ID
, GLP.ADJUSTMENT_PERIOD_FLAG
, GLP.CLOSING_STATUS HAVING HDR1.INVOICE_DATE BETWEEN MAX(GLP.START_DATE)
AND MAX(GLP.END_DATE) )) PROJFUNC_CURR_PER_INV
, SUM(PROJFUNC_CUR_PER_INV_PAID) PROJFUNC_CURR_PER_PAY
, (SELECT SUM(PROJ_INVOICE_AMOUNT)
FROM PA_PWP_AP_INV_DTL DTL1
, PA_PWP_AP_INV_HDR HDR1
WHERE HDR1.PA_PWP_AP_HDR_ID = DTL1.PA_PWP_AP_HDR_ID
AND HDR1.PROJECT_ID = HDR.PROJECT_ID
AND HDR1.VENDOR_ID = HDR.VENDOR_ID
AND DTL1.PROJ_CURRENCY_CODE = DTL.PROJ_CURRENCY_CODE
AND EXISTS (SELECT 1
FROM GL_PERIOD_STATUSES GLP
, PA_PROJECTS_ALL PROJ
, PA_IMPLEMENTATIONS_ALL IMP
WHERE APPLICATION_ID = 101
AND PROJ.PROJECT_ID = HDR.PROJECT_ID
AND PROJ.ORG_ID = IMP.ORG_ID
AND IMP.SET_OF_BOOKS_ID = GLP.SET_OF_BOOKS_ID
AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GLP.CLOSING_STATUS = 'O' GROUP BY APPLICATION_ID
, GLP.ADJUSTMENT_PERIOD_FLAG
, GLP.CLOSING_STATUS HAVING HDR1.INVOICE_DATE BETWEEN MAX(GLP.START_DATE)
AND MAX(GLP.END_DATE) )) PROJ_CURR_PER_INV
, SUM(PROJ_CUR_PER_INV_PAID) PROJ_CURR_PER_PAY
, SUM(PROJFUNC_RTAX_AMOUNT) PROJFUNC_RECOVERABLE_TAX
, SUM(PROJ_RTAX_AMOUNT) PROJ_RECOVERABLE_TAX
, SUM(ACCT_RTAX_AMOUNT) ACCT_RECOVERABLE_TAX
, SUM(RTAX_AMOUNT) RECOVERABLE_TAX
FROM PA_PWP_AP_INV_HDR HDR
, PA_PWP_AP_INV_DTL DTL
WHERE HDR.PA_PWP_AP_HDR_ID = DTL.PA_PWP_AP_HDR_ID GROUP BY HDR.VENDOR_ID
, HDR.SUPPLIER_NAME
, HDR.SUPPLIER_NUM
, DTL.PROJECT_ID
, DTL.PROJFUNC_CURRENCY_CODE
, DTL.PROJ_CURRENCY_CODE
, HDR.PROJECT_ID

Columns