RC_CASE_ASGN_VW (SQL View)	Index Back
ER Active Assignees View for active assignees for a case.

RC_CASE_ASGN_VW

(SQL View)

ER Active Assignees

View for active assignees for a case.

SELECT B.OPRID , A.CASE_ID , A.BUSINESS_UNIT , B.PERSON_ID ,A.RC_ASSIGNMENT_TYPE FROM PS_RC_CASE_ASSIGN A , PSOPRALIAS B WHERE B.OPRALIASTYPE ='PER' AND ( (A.RC_ASSIGNMENT_TYPE='I' AND A.RC_ER_ASSIGNED_TO = B.PERSON_ID AND A.START_DATE <=%CurrentDateIn AND (A.END_DATE IS NULL OR A.END_DATE >=%CurrentDateIn) ) OR ( A.RC_ASSIGNMENT_TYPE='G' AND A.RC_PROVIDER_GRP_ID = ( SELECT P.PROVIDER_GRP_ID FROM PS_RF_GRP_MEMBER P , PS_SET_CNTRL_REC S WHERE S.SETCNTRLVALUE = A.BUSINESS_UNIT AND S.RECNAME = 'RF_PROVIDER_GRP' AND P.SETID = S.SETID AND P.PROVIDER_GRP_ID = A.RC_PROVIDER_GRP_ID AND P.PERSON_ID = B.PERSON_ID AND P.STATUS = 'A' AND P.PERSON_ID NOT IN( SELECT ASG.RC_ER_ASSIGNED_TO FROM PS_RC_CASE_ASSIGN ASG WHERE ASG.CASE_ID = A.CASE_ID AND ASG.BUSINESS_UNIT = A.BUSINESS_UNIT AND ASG.RC_ASSIGNMENT_TYPE = 'I') ) AND A.START_DATE <=%CurrentDateIn AND (A.END_DATE IS NULL OR A.END_DATE >=%CurrentDateIn) ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

2 CASE_ID Number(15,0) DECIMAL(15) NOT NULL Case ID. This is a unique ID assigned to a case when it is saved. Unique ID is ensured by getting the next value from the auto-numbering table and updating the table.
Default Value: RC_CASE.CASE_ID

3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: RC_CASE.BUSINESS_UNIT

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

5 RC_ASSIGNMENT_TYPE Character(1) VARCHAR2(1) NOT NULL Assignment Type identifies the type of person, or group assigned to the case
G=Group
I=Individual

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	CASE_ID	Number(15,0)	DECIMAL(15) NOT NULL	Case ID. This is a unique ID assigned to a case when it is saved. Unique ID is ensured by getting the next value from the auto-numbering table and updating the table. Default Value: RC_CASE.CASE_ID
3	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: RC_CASE.BUSINESS_UNIT
4	PERSON_ID	Character(15)	VARCHAR2(15) NOT NULL	Person ID
5	RC_ASSIGNMENT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Assignment Type identifies the type of person, or group assigned to the case G=Group I=Individual