CA_EXPENSE_VW

(SQL View)
 Index Back

Recent Expenses


SELECT DISTINCT CA.CONTRACT_NUM , EX.EMPLID , ( SELECT FIRST_NAME %Concat ' ' %Concat LAST_NAME FROM PS_PERSONAL_DATA PERS WHERE PERS.EMPLID = EX.EMPLID), PROJ_RES.SHEET_ID , SUM(PROJ_RES.RESOURCE_AMOUNT) , PROJ_RES.CURRENCY_CD , PROJ_RES.ACCOUNTING_DT , EX.SHEET_NAME , ( SELECT DESCR FROM PS_EX_PURPOSE_TBL WHERE SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE RECNAME = 'EX_PURPOSE_TBL' AND SETCNTRLVALUE = PROJ_RES.BUSINESS_UNIT_GL AND BUSINESS_PURPOSE = EX.BUSINESS_PURPOSE)), PROJ_RES.BUSINESS_UNIT, PROJ_RES.PROJECT_ID, ( SELECT DESCR FROM PS_PROJECT WHERE BUSINESS_UNIT = PROJ_RES.BUSINESS_UNIT AND PROJECT_ID = PROJ_RES.PROJECT_ID), %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_EX_SHEET_HDR EX, 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.SHEET_ID = EX.SHEET_ID AND PROJ_RES.SYSTEM_SOURCE = 'BEX' 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, EX.EMPLID, PROJ_RES.SHEET_ID, PROJ_RES.CURRENCY_CD, PROJ_RES.ACCOUNTING_DT, EX.SHEET_NAME,PROJ_RES.BUSINESS_UNIT_GL, EX.BUSINESS_PURPOSE, PROJ_RES.BUSINESS_UNIT, PROJ_RES.PROJECT_ID, 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
5 TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount
6 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
7 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
8 SHEET_NAME Character(30) VARCHAR2(30) NOT NULL Report Description
9 BUS_PURP_DESCR Character(30) VARCHAR2(30) NOT NULL Business Purpose
10 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
11 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
13 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
14 DISPLAY_CURRENCY Character(3) VARCHAR2(3) NOT NULL Display Currency