CA_VOUCHER_VW

(SQL View)
 Index Back

Recent Vouchers


SELECT DISTINCT CA.CONTRACT_NUM , PROJ_RES.BUSINESS_UNIT_AP , PROJ_RES.VOUCHER_ID , SUM(PROJ_RES.RESOURCE_AMOUNT) , PROJ_RES.CURRENCY_CD , PROJ_RES.ACCOUNTING_DT , VCHR.VENDOR_ID , ( SELECT VNDR.NAME1 FROM PS_VENDOR VNDR WHERE VNDR.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = PROJ_RES.BUSINESS_UNIT_AP AND RECNAME = 'VENDOR') AND VNDR.VENDOR_ID = VCHR.VENDOR_ID), VCHR.INVOICE_ID, VCHR.INVOICE_DT, PROJ_RES.BUSINESS_UNIT, PROJ_RES.PROJECT_ID, PROJ.DESCR, %DecMult(%DecDiv(SUM(PROJ_RES.RESOURCE_AMOUNT), RT.RATE_DIV), RT.RATE_MULT), CA.CURRENCY_CD FROM PS_CA_CONTR_HDR CA, PS_CA_DETAIL CA_DTL, PS_CA_DETAIL_PROJ DTL_PROJ, PS_PROJ_RESOURCE PROJ_RES, PS_VOUCHER VCHR, PS_PROJECT PROJ, PS_RT_DFLT_VW RT WHERE CA.CONTRACT_NUM = CA_DTL.CONTRACT_NUM AND CA_DTL.CONTRACT_NUM = DTL_PROJ.CONTRACT_NUM AND CA_DTL.CONTRACT_LINE_NUM = DTL_PROJ.CONTRACT_LINE_NUM AND DTL_PROJ.BUSINESS_UNIT_PC = PROJ_RES.BUSINESS_UNIT AND DTL_PROJ.PROJECT_ID = PROJ_RES.PROJECT_ID AND DTL_PROJ.ACTIVITY_ID = PROJ_RES.ACTIVITY_ID AND PROJ_RES.BUSINESS_UNIT_AP = VCHR.BUSINESS_UNIT AND PROJ_RES.VOUCHER_ID = VCHR.VOUCHER_ID AND PROJ.BUSINESS_UNIT = PROJ_RES.BUSINESS_UNIT AND PROJ.PROJECT_ID = PROJ_RES.PROJECT_ID AND PROJ_RES.SYSTEM_SOURCE = 'BAP' AND RT.RT_TYPE = CA.RT_TYPE AND PROJ_RES.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) GROUP BY CA.CONTRACT_NUM, PROJ_RES.BUSINESS_UNIT_AP, PROJ_RES.VOUCHER_ID, PROJ_RES.CURRENCY_CD, PROJ_RES.ACCOUNTING_DT, VCHR.VENDOR_ID, VCHR.INVOICE_ID, VCHR.INVOICE_DT, PROJ_RES.BUSINESS_UNIT, PROJ_RES.PROJECT_ID, PROJ.DESCR, 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_AP Character(5) VARCHAR2(5) NOT NULL AP Business Unit
3 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
4 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount
5 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
6 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
7 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
8 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
9 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
10 INVOICE_DT Date(10) DATE Invoice Date
11 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
12 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
13 DESCR Character(30) VARCHAR2(30) NOT NULL Description
14 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
15 DISPLAY_CURRENCY Character(3) VARCHAR2(3) NOT NULL Display Currency