SPA_SUP_RC_VW

(SQL View)

SELECT DISTINCT O.ROLEUSER ,W.WO_CONID ,W.BUSINESS_UNIT ,W.TE_TEMPLATE_ID , ' ' , B.DESCRSHORT , PR.NAME FROM PS_SPF_WORDERREC W , PS_BUS_UNIT_TBL_FS B , PS_ROLEXLATOPR O , PSOPRALIAS P , PS_SPB_PERSON_TBL PR WHERE W.WO_STATUS IN ('C','N','L','T') AND W.SERVICE_METHOD = 'R' AND W.BUSINESS_UNIT = B.BUSINESS_UNIT AND EXISTS ( SELECT 'X' FROM PS_SPF_WO_HISTORY WHERE PS_SPF_WO_HISTORY.BUSINESS_UNIT = W.BUSINESS_UNIT AND PS_SPF_WO_HISTORY.WORK_ORDER_ID = W.WORK_ORDER_ID AND PS_SPF_WO_HISTORY.WO_STATUS = 'L') AND P.OPRID IN ( SELECT B.ROLEUSER FROM PS_SPB_SERVICE_PRV A , PS_ROLEXLATOPR B , PS_SPB_OPRALIAS_VW C ,PS_SET_CNTRL_GROUP S WHERE (A.SP_FUNCTION_TYPE = 'PRCT' OR A.SP_FUNCTION_TYPE = 'INDP') AND A.VENDOR_ID = W.VENDOR_ID AND A.LOCATION = W.VNDR_LOC AND C.OPRALIASTYPE = 'SPP' AND C.PERSON_ID = A.PERSON_ID AND B.ROLEUSER = C.OPRID AND S.SETCNTRLVALUE = W.BUSINESS_UNIT AND S.REC_GROUP_ID = 'FS_38' AND S.SETID = A.VENDOR_SETID) AND P.OPRID = O.ROLEUSER AND PR.PERSON_ID = W.WO_CONID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 USERID Character(30) VARCHAR2(30) NOT NULL A user's ID

2 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID

3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT
Prompt Table: SP_BU_SP_NONVW

4 TE_TEMPLATE_ID Character(8) VARCHAR2(8) NOT NULL Used to define a particular type of travel, i.e. company relocation, internal travel, etc. Determines what type of expenses and expenditure limits are associated with that particular type of travel. Established in the Structure Expenses system and chosen on the header panel of cash expense sheets.
Prompt Table: SPA_SP_RTMPL_VW

5 SPA_DATE_RANGE Character(23) VARCHAR2(23) NOT NULL Date Range
Prompt Table: SPA_SP_RC_PD_VW

6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description

7 NAME Character(50) VARCHAR2(50) NOT NULL Name

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	USERID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID
2	PERSON_ID	Character(15)	VARCHAR2(15) NOT NULL	Person ID
3	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT Prompt Table: SP_BU_SP_NONVW
4	TE_TEMPLATE_ID	Character(8)	VARCHAR2(8) NOT NULL	Used to define a particular type of travel, i.e. company relocation, internal travel, etc. Determines what type of expenses and expenditure limits are associated with that particular type of travel. Established in the Structure Expenses system and chosen on the header panel of cash expense sheets. Prompt Table: SPA_SP_RTMPL_VW
5	SPA_DATE_RANGE	Character(23)	VARCHAR2(23) NOT NULL	Date Range Prompt Table: SPA_SP_RC_PD_VW
6	DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Short Description
7	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name