VIEW: APPS.PA_PWP_CUSTOMER_INVOICE_V

Source

View Text - Preformatted

SELECT PWP.DRAFT_INVOICE_NUM , PWP.RA_INVOICE_NUMBER , PWP.CUSTOMER_NAME , PWP.CUSTOMER_NUMBER , PWP.AGREEMENT_NUM , PWP.Bill_THROUGH_DATE , PWP.invoice_class , PWP.DRAFT_INVOICE_NUM_CREDITED CREDIT_OF , PWP.INVOICE_DATE , PWP.INVOICE_STATUS INVOICE_STATUS_M , PWP.PROJFUNC_INVTRANS_RATE_TYPE , PWP.PROJFUNC_INVTRANS_RATE_DATE , PWP.project_id , NVL(PLC.cnt, 0) invoice_count , NVL(PLC.PFC_AMOUNT, 0) PFC_LINK_AP_AMOUNT , NVL(PLC.PC_AMOUNT, 0) PC_LINK_AP_AMOUNT , PWP.INV_CURRENCY_CODE , nvl(PWP.INV_BILL_AMOUNT,0) INVOICE_INV_AMOUNT , nvl(PWP.INV_CP_BILL_AMOUNT,0) INVOICE_CURR_PER_INV_AMOUNT , nvl(PWP.INV_RECIEPT_AMOUNT,0) AR_INV_RECIEPT_AMOUNT , nvl(PWP.INV_CP_RECIEPT_AMOUNT,0) AR_INV_CURR_PER_RECIEPT_AMOUNT , nvl(PWP.INV_OUTSTANDING_AMOUNT,0) AR_OUTSTANDING_AMOUNT , nvl(PWP.INV_ADJUSTMENT_AMOUNT,0) AR_INV_ADJUSTMENT_AMOUNT , nvl(PWP.INV_LINE_AMOUNT,0) INV_LINE_AMOUNT , nvl(PWP.INV_TAX_AMOUNT,0) AR_INV_TAX_AMOUNT , PWP.PROJFUNC_CURRENCY_CODE , decode(PWP.PFC_BILL_AMOUNT,0,0,(PWP.PFC_BILL_AMOUNT / INV_BILL_AMOUNT)) PFC_RATE , nvl(PWP.PFC_BILL_AMOUNT,0) PROJFUNC_BILL_AMOUNT , nvl(PWP.PFC_CP_BILL_AMOUNT,0) PROJFUNC_CURR_PER_BILL_AMOUNT , nvl(PWP.PFC_RECIEPT_AMOUNT,0) AR_PFC_RECIEPT_AMOUNT , nvl(PWP.PFC_CP_RECIEPT_AMOUNT,0) AR_PFC_CURR_PER_RECIEPT_AMOUNT , nvl(PWP.PFC_OUTSTANDING_AMOUNT,0) AR_PFC_OUTSTANDING_AMOUNT , nvl(PWP.PFC_ADJUSTMENT_AMOUNT,0) AR_PFC_ADJUSTMENT_AMOUNT , nvl(PWP.PFC_LINE_AMOUNT,0) PFC_LINE_AMOUNT , nvl(PWP.PFC_TAX_AMOUNT,0) AR_PFC_TAX_AMOUNT , PWP.PROJECT_CURRENCY_CODE , nvl(PC_BILL_AMOUNT,0) PROJ_BILL_AMOUNT , nvl(PC_CP_BILL_AMOUNT,0) PROJ_CURR_PER_BILL_AMOUNT, nvl(PC_RECIEPT_AMOUNT,0) AR_PC_RECIEPT_AMOUNT , nvl(PC_CP_RECIEPT_AMOUNT,0) AR_PC_CURR_PER_RECIEPT_AMOUNT , nvl(PC_OUTSTANDING_AMOUNT,0) AR_PC_OUTSTANDING_AMOUNT , nvl(PC_ADJUSTMENT_AMOUNT,0) AR_PC_ADJUSTMENT_AMOUNT , nvl(PC_LINE_AMOUNT,0) PC_LINE_AMOUNT , nvl(PC_TAX_AMOUNT,0) AR_PC_TAX_AMOUNT, PWP.GL_DATE, PWP.APPROVED_DATE, PWP.RELEASED_DATE, PWP.BILL_TO_ADDRESS, PWP.SHIP_TO_ADDRESS, PWP.BILL_TO_CUST_NAME, PWP.SHIP_TO_CUST_NAME, PWP.BILL_TO_CUST_NUMBER, PWP.SHIP_TO_CUST_NUMBER FROM PA_PWP_CUSTOMER_SUMM PWP , (SELECT pp.project_id , pp.draft_invoice_num , Sum(pp.invoice_count) cnt , sum(pp.pfc_amount) pfc_amount , sum(pp.pc_amount) pc_amount FROM PA_PWP_LINK_STATUS_V pp GROUP BY PROJECT_ID , DRAFT_INVOICE_NUM ) PLC WHERE PWP.project_id = PLC.project_id(+) AND PWP.draft_invoice_num = PLC.draft_invoice_num(+)

View Text - HTML Formatted

SELECT PWP.DRAFT_INVOICE_NUM
, PWP.RA_INVOICE_NUMBER
, PWP.CUSTOMER_NAME
, PWP.CUSTOMER_NUMBER
, PWP.AGREEMENT_NUM
, PWP.BILL_THROUGH_DATE
, PWP.INVOICE_CLASS
, PWP.DRAFT_INVOICE_NUM_CREDITED CREDIT_OF
, PWP.INVOICE_DATE
, PWP.INVOICE_STATUS INVOICE_STATUS_M
, PWP.PROJFUNC_INVTRANS_RATE_TYPE
, PWP.PROJFUNC_INVTRANS_RATE_DATE
, PWP.PROJECT_ID
, NVL(PLC.CNT
, 0) INVOICE_COUNT
, NVL(PLC.PFC_AMOUNT
, 0) PFC_LINK_AP_AMOUNT
, NVL(PLC.PC_AMOUNT
, 0) PC_LINK_AP_AMOUNT
, PWP.INV_CURRENCY_CODE
, NVL(PWP.INV_BILL_AMOUNT
, 0) INVOICE_INV_AMOUNT
, NVL(PWP.INV_CP_BILL_AMOUNT
, 0) INVOICE_CURR_PER_INV_AMOUNT
, NVL(PWP.INV_RECIEPT_AMOUNT
, 0) AR_INV_RECIEPT_AMOUNT
, NVL(PWP.INV_CP_RECIEPT_AMOUNT
, 0) AR_INV_CURR_PER_RECIEPT_AMOUNT
, NVL(PWP.INV_OUTSTANDING_AMOUNT
, 0) AR_OUTSTANDING_AMOUNT
, NVL(PWP.INV_ADJUSTMENT_AMOUNT
, 0) AR_INV_ADJUSTMENT_AMOUNT
, NVL(PWP.INV_LINE_AMOUNT
, 0) INV_LINE_AMOUNT
, NVL(PWP.INV_TAX_AMOUNT
, 0) AR_INV_TAX_AMOUNT
, PWP.PROJFUNC_CURRENCY_CODE
, DECODE(PWP.PFC_BILL_AMOUNT
, 0
, 0
, (PWP.PFC_BILL_AMOUNT / INV_BILL_AMOUNT)) PFC_RATE
, NVL(PWP.PFC_BILL_AMOUNT
, 0) PROJFUNC_BILL_AMOUNT
, NVL(PWP.PFC_CP_BILL_AMOUNT
, 0) PROJFUNC_CURR_PER_BILL_AMOUNT
, NVL(PWP.PFC_RECIEPT_AMOUNT
, 0) AR_PFC_RECIEPT_AMOUNT
, NVL(PWP.PFC_CP_RECIEPT_AMOUNT
, 0) AR_PFC_CURR_PER_RECIEPT_AMOUNT
, NVL(PWP.PFC_OUTSTANDING_AMOUNT
, 0) AR_PFC_OUTSTANDING_AMOUNT
, NVL(PWP.PFC_ADJUSTMENT_AMOUNT
, 0) AR_PFC_ADJUSTMENT_AMOUNT
, NVL(PWP.PFC_LINE_AMOUNT
, 0) PFC_LINE_AMOUNT
, NVL(PWP.PFC_TAX_AMOUNT
, 0) AR_PFC_TAX_AMOUNT
, PWP.PROJECT_CURRENCY_CODE
, NVL(PC_BILL_AMOUNT
, 0) PROJ_BILL_AMOUNT
, NVL(PC_CP_BILL_AMOUNT
, 0) PROJ_CURR_PER_BILL_AMOUNT
, NVL(PC_RECIEPT_AMOUNT
, 0) AR_PC_RECIEPT_AMOUNT
, NVL(PC_CP_RECIEPT_AMOUNT
, 0) AR_PC_CURR_PER_RECIEPT_AMOUNT
, NVL(PC_OUTSTANDING_AMOUNT
, 0) AR_PC_OUTSTANDING_AMOUNT
, NVL(PC_ADJUSTMENT_AMOUNT
, 0) AR_PC_ADJUSTMENT_AMOUNT
, NVL(PC_LINE_AMOUNT
, 0) PC_LINE_AMOUNT
, NVL(PC_TAX_AMOUNT
, 0) AR_PC_TAX_AMOUNT
, PWP.GL_DATE
, PWP.APPROVED_DATE
, PWP.RELEASED_DATE
, PWP.BILL_TO_ADDRESS
, PWP.SHIP_TO_ADDRESS
, PWP.BILL_TO_CUST_NAME
, PWP.SHIP_TO_CUST_NAME
, PWP.BILL_TO_CUST_NUMBER
, PWP.SHIP_TO_CUST_NUMBER
FROM PA_PWP_CUSTOMER_SUMM PWP
, (SELECT PP.PROJECT_ID
, PP.DRAFT_INVOICE_NUM
, SUM(PP.INVOICE_COUNT) CNT
, SUM(PP.PFC_AMOUNT) PFC_AMOUNT
, SUM(PP.PC_AMOUNT) PC_AMOUNT
FROM PA_PWP_LINK_STATUS_V PP GROUP BY PROJECT_ID
, DRAFT_INVOICE_NUM ) PLC
WHERE PWP.PROJECT_ID = PLC.PROJECT_ID(+)
AND PWP.DRAFT_INVOICE_NUM = PLC.DRAFT_INVOICE_NUM(+)