eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_PWP_SUPPLIER_INV_SUMMARY_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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