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 ,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

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
, 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