VIEW: APPS.PA_PWP_SUPPLIER_INVOICE_V

Source

View Text - Preformatted

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 ,DECODE(HDR.PAYMENT_STATUS,'Y','Fully Paid', 'N', 'Not Paid','P','Partially Paid') 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 ,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 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.INVOICE_ID ,HDR.INVOICE_NUM ,HDR.VENDOR_ID ,HDR.SUPPLIER_NAME ,HDR.INVOICE_DATE ,HDR.SUPPLIER_SITE_CODE ,HDR.PAYMENT_STATUS ,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

View Text - HTML Formatted

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
, DECODE(HDR.PAYMENT_STATUS
, 'Y'
, 'FULLY PAID'
, 'N'
, 'NOT PAID'
, 'P'
, 'PARTIALLY PAID') 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
, 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
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.INVOICE_ID
, HDR.INVOICE_NUM
, HDR.VENDOR_ID
, HDR.SUPPLIER_NAME
, HDR.INVOICE_DATE
, HDR.SUPPLIER_SITE_CODE
, HDR.PAYMENT_STATUS
, 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