RF_SO_ASSGN_VW2 (SQL View)	Index Back
SO Assignment View This is a view on the service order assignment table for open service orders where no one is assigned.

RF_SO_ASSGN_VW2

(SQL View)

SO Assignment View

This is a view on the service order assignment table for open service orders where no one is assigned.

SELECT A.BUSINESS_UNIT , A.SO_ID , A.SO_STATUS , A.SO_PRIORITY , 0 , ' ' , ' ' , ' ' , A.START_DT , A.START_TIME , A.END_DT , A.END_TIME , A.PROVIDER_GRP_ID , A.PERSON_ID , D.SETID FROM PS_RF_SO_HDR A , PS_RF_PROVIDER_GRP D WHERE A.SO_STATUS = 'O' AND A.PROVIDER_GRP_ID = D.PROVIDER_GRP_ID AND D.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC E WHERE E.SETCNTRLVALUE = A.BUSINESS_UNIT AND E.RECNAME = 'RF_PROVIDER_GRP') AND NOT EXISTS ( SELECT 'X' FROM PS_RF_SOLN_ASSIGN B WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.SO_ID = A.SO_ID AND B.PROVIDER_GRP_ID = A.PROVIDER_GRP_ID AND B.PERSON_ID = A.PERSON_ID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

2 SO_ID Character(10) VARCHAR2(10) NOT NULL Service Order ID

3 SO_STATUS Character(1) VARCHAR2(1) NOT NULL This field is used to store the Service Order Header Status.
C=Complete
D=Closed
O=Open
X=Canceled

4 SO_PRIORITY Character(1) VARCHAR2(1) NOT NULL The priority of the service order.
H=High
L=Low
N=Normal
U=Urgent

5 SO_LINE Number(3,0) SMALLINT NOT NULL Stores the Service Order line number.

6 ACTIVITY_CODE Character(8) VARCHAR2(8) NOT NULL Activity Code which identifies the specific Activities which will be performed as part of Service Orders.

7 SOACTIVITY_STATUS Character(3) VARCHAR2(3) NOT NULL Service Order Activity (line) Status.
CAN=Canceled
CLO=Closed
COM=Completed
OAC=Accepted
OER=En Route
OHA=Hold Assignment
OHC=Hold Customer
OHP=Hold Part
OOS=On Site
OPA=Assigned
ORJ=Rejected

8 SOACT_PRIORITY Character(3) VARCHAR2(3) NOT NULL The priority of the service order activity (a.k.a. line).
HIH=High
LOW=Low
NOR=Normal
URG=Urgent

9 START_DT Date(10) DATE Start date field to store start date value.

10 START_TIME Time(15) TIMESTAMP Start of Shift

11 END_DT Date(10) DATE This field stores the end date values.

12 END_TIME Time(15) TIMESTAMP End of Shift

13 PROVIDER_GRP_ID Character(10) VARCHAR2(10) NOT NULL Identification number for the provider group.
Prompt Table: RF_PROVGRP_VW2

14 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID
Prompt Table: RF_GRP_MEM_VW

15 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	SO_ID	Character(10)	VARCHAR2(10) NOT NULL	Service Order ID
3	SO_STATUS	Character(1)	VARCHAR2(1) NOT NULL	This field is used to store the Service Order Header Status. C=Complete D=Closed O=Open X=Canceled
4	SO_PRIORITY	Character(1)	VARCHAR2(1) NOT NULL	The priority of the service order. H=High L=Low N=Normal U=Urgent
5	SO_LINE	Number(3,0)	SMALLINT NOT NULL	Stores the Service Order line number.
6	ACTIVITY_CODE	Character(8)	VARCHAR2(8) NOT NULL	Activity Code which identifies the specific Activities which will be performed as part of Service Orders.
7	SOACTIVITY_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Service Order Activity (line) Status. CAN=Canceled CLO=Closed COM=Completed OAC=Accepted OER=En Route OHA=Hold Assignment OHC=Hold Customer OHP=Hold Part OOS=On Site OPA=Assigned ORJ=Rejected
8	SOACT_PRIORITY	Character(3)	VARCHAR2(3) NOT NULL	The priority of the service order activity (a.k.a. line). HIH=High LOW=Low NOR=Normal URG=Urgent
9	START_DT	Date(10)	DATE	Start date field to store start date value.
10	START_TIME	Time(15)	TIMESTAMP	Start of Shift
11	END_DT	Date(10)	DATE	This field stores the end date values.
12	END_TIME	Time(15)	TIMESTAMP	End of Shift
13	PROVIDER_GRP_ID	Character(10)	VARCHAR2(10) NOT NULL	Identification number for the provider group. Prompt Table: RF_PROVGRP_VW2
14	PERSON_ID	Character(15)	VARCHAR2(15) NOT NULL	Person ID Prompt Table: RF_GRP_MEM_VW
15	SETID	Character(5)	VARCHAR2(5) NOT NULL	This field is used to store the value of SetID on various setup tables.