CA_INVOICE_VW

(SQL View)
 Index Back

Recent Invoices


SELECT HDR.CONTRACT_NUM , HDR.BUSINESS_UNIT , HDR.INVOICE , SUM(HDR.INVOICE_AMOUNT) , HDR.BI_CURRENCY_CD , HDR.INVOICE_DT , HDR.BILL_TO_CUST_ID , HDR.BILLING_SPECIALIST , HDR.BILLING_AUTHORITY , %DecMult(%DecDiv(SUM(HDR.INVOICE_AMOUNT), RT.RATE_DIV), RT.RATE_MULT) , CA.CURRENCY_CD FROM PS_BI_HDR HDR , PS_CA_CONTR_HDR CA , PS_RT_DFLT_VW RT WHERE HDR.CONTRACT_NUM = CA.CONTRACT_NUM AND RT.RT_TYPE = CA.RT_TYPE AND HDR.BI_CURRENCY_CD = RT.FROM_CUR AND CA.CURRENCY_CD = RT.TO_CUR AND RT.EFFDT = ( SELECT MAX(RT_ED.EFFDT) FROM PS_RT_DFLT_VW RT_ED WHERE RT.FROM_CUR = RT_ED.FROM_CUR AND RT.TO_CUR = RT_ED.TO_CUR AND RT.RT_TYPE = RT_ED.RT_TYPE AND RT_ED.EFFDT <= %CurrentDateIn) AND HDR.BILL_STATUS NOT IN ('TMP', 'TMR') GROUP BY HDR.CONTRACT_NUM, HDR.BUSINESS_UNIT, HDR.INVOICE, HDR.BI_CURRENCY_CD, HDR.INVOICE_DT, HDR.BILL_TO_CUST_ID, HDR.BILLING_SPECIALIST, HDR.BILLING_AUTHORITY, RT.RATE_DIV, RT.RATE_MULT, CA.CURRENCY_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
4 INVOICE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Amount
5 BI_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Billing Currency Code
6 INVOICE_DT Date(10) DATE Invoice Date
7 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
8 BILLING_SPECIALIST Character(8) VARCHAR2(8) NOT NULL Billing Specialist
9 BILLING_AUTHORITY Character(8) VARCHAR2(8) NOT NULL Billing Authority
10 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
11 DISPLAY_CURRENCY Character(3) VARCHAR2(3) NOT NULL Display Currency