View: PA_PWP_SUPPLIER_INVOICE_V

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

View Text

SELECT HDR.INVOICE_ID INVOICE_ID
, HDR.INVOICE_NUM INVOICE_NUM
, HDR.VENDOR_ID VENDOR_ID
, HDR.SUPPLIER_NAME SUPPLIER_NAME
, HDR.INVOICE_DATE INVOICE_DATE
, HDR.SUPPLIER_SITE_CODE SUPPLIER_SITE
, LKP.MEANING PAYMENT_STATUS
, HDR.PO_NUMBER PO_NUMBER
, HDR.HOLD_REASON HOLD_REASON
, DTL.PROJECT_ID PROJECT_ID
, DTL.PROJFUNC_CURRENCY_CODE PROJFUNC_CURRENCY_CODE
, DTL.PROJ_CURRENCY_CODE PROJ_CURRENCY_CODE
, DTL.INVOICE_CURRENCY INVOICE_CURRENCY
, HDR.PWP_HOLD_FLAG PWP_HOLD_FLAG
, HDR.DLV_HOLD_FLAG DLV_HOLD_FLAG
, HDR.HOLD_FLAG
, (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
, NVL((SELECT SUM(PROJFUNC_INVOICE_AMOUNT)
FROM PA_PWP_AP_INV_DTL DTL1
WHERE DTL1.INVOICE_ID = HDR.INVOICE_ID
AND DTL1.PROJFUNC_CURRENCY_CODE = DTL.PROJFUNC_CURRENCY_CODE
AND DTL1.PROJECT_ID = HDR.PROJECT_ID
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 HDR.INVOICE_DATE BETWEEN MAX(GLP.START_DATE)
AND MAX(GLP.END_DATE) ))
, 0) PROJFUNC_CURR_PER_INV
, 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
, HDR.INVOICE_TYPE
, HDR.EARLIEST_PAY_DUE_DATE
, HDR.GL_DATE
, HDR.DESCRIPTION
, HDR.PREPAID_AMOUNT
, HDR.EXCHANGE_RATE_TYPE
, HDR.EXCHANGE_RATE
, HDR.EXCHANGE_DATE
FROM PA_PWP_AP_INV_HDR HDR
, PA_PWP_AP_INV_DTL DTL
, PA_LOOKUPS LKP
WHERE HDR.PA_PWP_AP_HDR_ID = DTL.PA_PWP_AP_HDR_ID
AND LKP.LOOKUP_TYPE = 'PA_PWP_PAYMENT_STATUS'
AND LKP.LOOKUP_CODE = HDR.PAYMENT_STATUS GROUP BY HDR.INVOICE_ID
, HDR.INVOICE_NUM
, HDR.VENDOR_ID
, HDR.SUPPLIER_NAME
, HDR.INVOICE_DATE
, HDR.SUPPLIER_SITE_CODE
, LKP.MEANING
, HDR.PO_NUMBER
, HDR.HOLD_REASON
, HDR.PROJECT_ID
, DTL.PROJECT_ID
, DTL.PROJFUNC_CURRENCY_CODE
, DTL.PROJ_CURRENCY_CODE
, DTL.INVOICE_CURRENCY
, HDR.PWP_HOLD_FLAG
, HDR.DLV_HOLD_FLAG
, HDR.HOLD_FLAG
, HDR.INVOICE_TYPE
, HDR.EARLIEST_PAY_DUE_DATE
, HDR.GL_DATE
, HDR.DESCRIPTION
, HDR.PREPAID_AMOUNT
, HDR.EXCHANGE_RATE_TYPE
, HDR.EXCHANGE_RATE
, HDR.EXCHANGE_DATE

Columns