SPA_E_EXPAPR_VW

(SQL View)
 Index Back

Expense Pagelet view

Pagelet that fetches Expense Sheets pending for Approval

SELECT O.ROLEUSER ,X.BUSINESS_UNIT ,H.SHEET_ID , H.CREATION_DT ,P1.NAME ,P2.NAME ,SUM(D.AMOUNT_BSE) ,D.CURRENCY_CD_BASE , W.SERVICE_METHOD FROM PS_SPA_SHEET_HDR H , PS_SPA_SHEET_LINE D , PS_SPA_EXP_XREF X , PS_SPB_PERSON_TBL P1 , PS_SPB_PERSON_TBL P2 , PS_SPF_WORDERREC W , PS_ROLEXLATOPR O WHERE X.SHEET_ID = H.SHEET_ID AND H.SHEET_ID = D.SHEET_ID AND H.EXP_HDR_STATUS = 'SUB' AND X.BUSINESS_UNIT = W.BUSINESS_UNIT AND X.WORK_ORDER_ID = W.WORK_ORDER_ID AND P1.PERSON_ID = W.WO_CONID AND P2.PERSON_ID = W.PROVIDER_CONTACT AND (O.ROLEUSER IN ( SELECT C.OPRID FROM PS_SPA_EX_APR_XREF A , PS_EOAW_STEPINST B , PS_EOAW_USERINST C WHERE A.EOAWDEFN_ID = B.EOAWDEFN_ID AND A.EOAWPRCS_ID = B.EOAWPRCS_ID AND A.EOAWTHREAD_ID = B.EOAWTHREAD_ID AND B.EOAWSTEP_INSTANCE = C.EOAWSTEP_INSTANCE AND A.SHEET_ID = H.SHEET_ID)) GROUP BY O.ROLEUSER ,X.BUSINESS_UNIT ,H.SHEET_ID ,H.CREATION_DT ,P1.NAME ,P2.NAME ,D.CURRENCY_CD_BASE , W.SERVICE_METHOD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 USERID Character(30) VARCHAR2(30) NOT NULL A user's ID
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

3 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
4 CREATION_DT Date(10) DATE Creation Date
5 NAME50 Character(50) VARCHAR2(50) NOT NULL name field of length 50
6 NAME50_2 Character(50) VARCHAR2(50) NOT NULL name field of length 50
7 AMOUNT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
8 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
9 SERVICE_METHOD Character(1) VARCHAR2(1) NOT NULL Service Method
D=Deliverable
M=Multi Resource
R=Resource