SDB_CASE_VW (SQL View)	Index Back
Case Information View used to load Case Information to Contact Center Dashboard.

SDB_CASE_VW

(SQL View)

Case Information View

used to load Case Information to Contact Center Dashboard.

SELECT A.CASE_ID , A.BUSINESS_UNIT , A.MARKET , A.AGREEMENT_CODE , A.AGR_RENEWAL_NUM , A.AGREEMENT_LINE , A.ASSIGNED_TO , ' ' , A.BO_ID_CUST , A.BO_ID_CONTACT , A.CASE_SUBTYPE , A.CASE_TYPE , A.CLOSED_DTTM , A.DISP_TMPL_FAM_CD , CASE WHEN A.DISP_TMPL_FAM_CD = 'RC_HELPDESK' THEN 'RH' WHEN A.DISP_TMPL_FAM_CD = 'CRM_HHD' THEN 'RHHR' ELSE 'RC' END , A.PROBLEM_TYPE , A.PRODUCT_ID , A.PROVIDER_GRP_ID , A.RC_CATEGORY , A.RC_DETAIL , A.RC_IMPACT , A.RC_PRIORITY , A.RC_RESP_DATE , A.RC_RESP_IND , A.RC_RESP_TIME , A.RC_RESPMET_DATE , A.RC_RESPMET_TIME , A.RC_REST_DATE , A.RC_REST_IND , A.RC_REST_TIME , A.RC_RESTMET_DATE , A.RC_RESTMET_TIME , A.RC_SEVERITY , A.RC_SOURCE , A.RC_STATUS , C.STATUS_CATEGORY , A.RC_SUMMARY , A.RC_TYPE , A.RC_VERTICAL , A.RES_FIRST_CNTCT , A.ROW_ADDED_DTTM , A.ROW_LASTMANT_DTTM , A.SECURE_CASE_FLG , A.TARGET_CLOSE_DATE , B.CASE_STATUS_SETID , B.CASE_PRIORIT_SETID , B.CASE_CATEGOR_SETID , B.CASE_TYPE_SETID , B.CASE_IMPACT_SETID , B.CASE_PROBTYP_SETID , B.CASE_SEVERIT_SETID , B.CASE_SOURCE_SETID , B.PROVIDER_GRP_SETID , B.PRODUCT_SETID , B.AGREEMENT_SETID , A.RC_RED_DTTM , A.RC_YELLOW_DTTM , A.ESCALATION_COUNT , A.ESCALATION_DTTM , A.SURVEY_SCORE , %DateTimeNull , 0 , 0 , %DateTimeNull , %DateTimeNull , ' ' , ' ' , 0 , A.PRODUCT_GROUP , ' ' FROM PS_RC_CASE A , PS_RBD_BUSET B , PS_RC_STATUS_TBL C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.CASE_STATUS_SETID = C.SETID AND A.RC_STATUS = C.RC_STATUS

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 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.

2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: SP_RC_VR_NONVW

3 MARKET Character(3) VARCHAR2(3) NOT NULL Market

4 AGREEMENT_CODE Character(30) VARCHAR2(30) NOT NULL This field represents the agreement code (name) for an agreement

5 AGR_RENEWAL_NUM Number(4,0) SMALLINT NOT NULL This field represents the agreement renewal number (agreements begin at a value of one and the number is incremented with every renewal)

6 AGREEMENT_LINE Character(10) VARCHAR2(10) NOT NULL This field records the value for the agreement line number

7 ASSIGNED_TO Character(15) VARCHAR2(15) NOT NULL This is a field to store assignment information.
Prompt Table: RC_GRP_MEM_VW
Set Control Field: BUSINESS_UNIT

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

9 BO_ID_CUST Number(31,0) DECIMAL(31) NOT NULL This field stores the Business Object ID for the customer.
Prompt Table: BO

10 BO_ID_CONTACT Number(31,0) DECIMAL(31) NOT NULL This field indicates the Business Object ID for the contact.

11 CASE_SUBTYPE Character(5) VARCHAR2(5) NOT NULL Field to capture the case subtype.
Prompt Table: RC_CASE_SUBTYPE

12 CASE_TYPE Character(5) VARCHAR2(5) NOT NULL Case Type
Prompt Table: RC_CASETYPE_TBL

13 CLOSED_DTTM DateTime(26) TIMESTAMP Date Time stamp for when the case status category was changed to closed.

14 DISP_TMPL_ID Character(30) VARCHAR2(30) NOT NULL Template identifier to define the display template.

15 LIC_PROD_CD Character(18) VARCHAR2(18) NOT NULL Licensed product code for the configurable component.

16 PROBLEM_TYPE Character(8) VARCHAR2(8) NOT NULL Problem Type for Support Cases
Prompt Table: RC_PROBTYPE_TBL
Set Control Field: BUSINESS_UNIT

17 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
Prompt Table: PROD_ITEM

18 PROVIDER_GRP_ID Character(10) VARCHAR2(10) NOT NULL Identification number for the provider group.
Prompt Table: RF_PROVGRP_VW2
Set Control Field: BUSINESS_UNIT

19 RC_CATEGORY Character(5) VARCHAR2(5) NOT NULL CallCenter Category. Used in HD.
Prompt Table: RC_CTD_CAT_F_VW
Set Control Field: BUSINESS_UNIT

20 RC_DETAIL Character(5) VARCHAR2(5) NOT NULL Detail field on HD Case. Used in Quick Code.
Prompt Table: RC_CTD_DET_F_VW

21 RC_IMPACT Character(5) VARCHAR2(5) NOT NULL Case severity
Prompt Table: RC_IMPACT_TBL

22 RC_PRIORITY Character(5) VARCHAR2(5) NOT NULL Case Priority
Prompt Table: RC_PRIORITY_TBL

23 RC_RESP_DATE Date(10) DATE Response date

24 RC_RESP_IND Character(4) VARCHAR2(4) NOT NULL Response Indicator, to indicate different stage of response for SLA.
A=Pending
N=Late
NA=Not Applicable
Y=On-Time

25 RC_RESP_TIME Time(15) TIMESTAMP Response Time

26 RC_RESPMET_DATE Date(10) DATE Response Date Met

27 RC_RESPMET_TIME Time(15) TIMESTAMP Response Time Met

28 RC_REST_DATE Date(10) DATE Restore Date

29 RC_REST_IND Character(4) VARCHAR2(4) NOT NULL Restore Indicator, indicate different stage of restore for SLA
A=Pending
N=Late
NA=Not Applicable
Y=On-Time

30 RC_REST_TIME Time(15) TIMESTAMP Restore Time

31 RC_RESTMET_DATE Date(10) DATE Restore Date Met

32 RC_RESTMET_TIME Time(15) TIMESTAMP Restore Time Met

33 RC_SEVERITY Character(5) VARCHAR2(5) NOT NULL Case severity
Prompt Table: RC_SEVERITY_TBL

34 RC_SOURCE Character(5) VARCHAR2(5) NOT NULL Case Source How this case origianated
Prompt Table: RC_SOURCE_TBL

35 RC_STATUS Character(5) VARCHAR2(5) NOT NULL Case Status
Prompt Table: RC_STATUS_TBL

36 STATUS_CATEGORY Character(1) VARCHAR2(1) NOT NULL Categories for Case Status. These drive processing.
C=Closed
D=Canceled
H=Case is on hold
O=Case is Open

37 RC_SUMMARY Character(80) VARCHAR2(80) NOT NULL Typically a summary of a long field.

38 RC_TYPE Character(5) VARCHAR2(5) NOT NULL Specialty type. Used on HD case.
Prompt Table: RC_CTD_TYP_F_VW
Set Control Field: RC_CATEGORY

39 RC_VERTICAL Character(4) VARCHAR2(4) NOT NULL Specifies the CallCenter type. See translates for valid values.
HD=HelpDesk
SW=Support

40 RES_FIRST_CNTCT Character(1) VARCHAR2(1) NOT NULL Resolved by First Contact
Y/N Table Edit

41 ROW_ADDED_DTTM DateTime(26) TIMESTAMP This is a datetime field for record creation.

42 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.

43 SECURE_CASE_FLG Character(1) VARCHAR2(1) NOT NULL Used in HRHD to Secure the case so that only people in the provider group assigned to the case can access the case.
N=No
Y=Yes

44 TARGET_CLOSE_DATE Date(10) DATE Target close date

45 CASE_STATUS_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

46 CASE_PRIORIT_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

47 CASE_CATEGOR_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

48 CASE_TYPE_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

49 CASE_IMPACT_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

50 CASE_PROBTYP_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

51 CASE_SEVERIT_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

52 CASE_SOURCE_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

53 PROVIDER_GRP_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

54 PRODUCT_SETID Character(5) VARCHAR2(5) NOT NULL This is the field for Dashboard setid recname.

55 AGREEMENT_SETID Character(5) VARCHAR2(5) NOT NULL AGREEMENT_SETID field is added to the PROD_ITEM record. This is because we may select a template from a different SETID than the product definition

56 RC_RED_DTTM DateTime(26) TIMESTAMP The date time that this case will turn Red if it has not yet been resolved.

57 RC_YELLOW_DTTM DateTime(26) TIMESTAMP The date time that this case will turn yellow if it has not yet been resolved.

58 ESCALATION_COUNT Number(3,0) SMALLINT NOT NULL Escalation Count. Used in Case to register the number of times the case got escalated.

59 ESCALATION_DTTM DateTime(26) TIMESTAMP Escalation Date Time. Used in RC_CASE to register the Date Time of Last Escalation.

60 SURVEY_SCORE Number(3,0) SMALLINT NOT NULL Survey Score. This is used in Case Component. To store Survey Score calculated from Branch Script.

61 HOLD_DTTM DateTime(26) TIMESTAMP Last Time Placed on Hold

62 HOLD_COUNT Number(3,0) SMALLINT NOT NULL Number of times the case was placed on hold. Used for the Services Dashboard.

63 HOLD_MINUTES Number(12,0) DECIMAL(12) NOT NULL Total time on hold. Used for the Services Dashboard.

64 CANCEL_DTTM DateTime(26) TIMESTAMP Canceled Date Time

65 REOPEN_DTTM DateTime(26) TIMESTAMP Date Time the case was reopened. Used for the Services Dashboard.

66 PARENT_FLAG Character(1) VARCHAR2(1) NOT NULL Parent Flag

67 CHILD_FLAG Character(1) VARCHAR2(1) NOT NULL Enable Child Status flag for Case Rlationship

68 CHILD_COUNT Number(5,0) INTEGER NOT NULL Number of children this case is the parent to. This does not include multipule levels, just direct children. Used for the Services Dashboard.

69 PRODUCT_GROUP Character(10) VARCHAR2(10) NOT NULL PRoduct Group

70 RY_DIALOG_NAME Character(254) VARCHAR2(254) NOT NULL Field is used to capture a Dialog Name.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	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.
2	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: SP_RC_VR_NONVW
3	MARKET	Character(3)	VARCHAR2(3) NOT NULL	Market
4	AGREEMENT_CODE	Character(30)	VARCHAR2(30) NOT NULL	This field represents the agreement code (name) for an agreement
5	AGR_RENEWAL_NUM	Number(4,0)	SMALLINT NOT NULL	This field represents the agreement renewal number (agreements begin at a value of one and the number is incremented with every renewal)
6	AGREEMENT_LINE	Character(10)	VARCHAR2(10) NOT NULL	This field records the value for the agreement line number
7	ASSIGNED_TO	Character(15)	VARCHAR2(15) NOT NULL	This is a field to store assignment information. Prompt Table: RC_GRP_MEM_VW Set Control Field: BUSINESS_UNIT
8	PERSON_ID	Character(15)	VARCHAR2(15) NOT NULL	Person ID
9	BO_ID_CUST	Number(31,0)	DECIMAL(31) NOT NULL	This field stores the Business Object ID for the customer. Prompt Table: BO
10	BO_ID_CONTACT	Number(31,0)	DECIMAL(31) NOT NULL	This field indicates the Business Object ID for the contact.
11	CASE_SUBTYPE	Character(5)	VARCHAR2(5) NOT NULL	Field to capture the case subtype. Prompt Table: RC_CASE_SUBTYPE
12	CASE_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Case Type Prompt Table: RC_CASETYPE_TBL
13	CLOSED_DTTM	DateTime(26)	TIMESTAMP	Date Time stamp for when the case status category was changed to closed.
14	DISP_TMPL_ID	Character(30)	VARCHAR2(30) NOT NULL	Template identifier to define the display template.
15	LIC_PROD_CD	Character(18)	VARCHAR2(18) NOT NULL	Licensed product code for the configurable component.
16	PROBLEM_TYPE	Character(8)	VARCHAR2(8) NOT NULL	Problem Type for Support Cases Prompt Table: RC_PROBTYPE_TBL Set Control Field: BUSINESS_UNIT
17	PRODUCT_ID	Character(18)	VARCHAR2(18) NOT NULL	Product ID Prompt Table: PROD_ITEM
18	PROVIDER_GRP_ID	Character(10)	VARCHAR2(10) NOT NULL	Identification number for the provider group. Prompt Table: RF_PROVGRP_VW2 Set Control Field: BUSINESS_UNIT
19	RC_CATEGORY	Character(5)	VARCHAR2(5) NOT NULL	CallCenter Category. Used in HD. Prompt Table: RC_CTD_CAT_F_VW Set Control Field: BUSINESS_UNIT
20	RC_DETAIL	Character(5)	VARCHAR2(5) NOT NULL	Detail field on HD Case. Used in Quick Code. Prompt Table: RC_CTD_DET_F_VW
21	RC_IMPACT	Character(5)	VARCHAR2(5) NOT NULL	Case severity Prompt Table: RC_IMPACT_TBL
22	RC_PRIORITY	Character(5)	VARCHAR2(5) NOT NULL	Case Priority Prompt Table: RC_PRIORITY_TBL
23	RC_RESP_DATE	Date(10)	DATE	Response date
24	RC_RESP_IND	Character(4)	VARCHAR2(4) NOT NULL	Response Indicator, to indicate different stage of response for SLA. A=Pending N=Late NA=Not Applicable Y=On-Time
25	RC_RESP_TIME	Time(15)	TIMESTAMP	Response Time
26	RC_RESPMET_DATE	Date(10)	DATE	Response Date Met
27	RC_RESPMET_TIME	Time(15)	TIMESTAMP	Response Time Met
28	RC_REST_DATE	Date(10)	DATE	Restore Date
29	RC_REST_IND	Character(4)	VARCHAR2(4) NOT NULL	Restore Indicator, indicate different stage of restore for SLA A=Pending N=Late NA=Not Applicable Y=On-Time
30	RC_REST_TIME	Time(15)	TIMESTAMP	Restore Time
31	RC_RESTMET_DATE	Date(10)	DATE	Restore Date Met
32	RC_RESTMET_TIME	Time(15)	TIMESTAMP	Restore Time Met
33	RC_SEVERITY	Character(5)	VARCHAR2(5) NOT NULL	Case severity Prompt Table: RC_SEVERITY_TBL
34	RC_SOURCE	Character(5)	VARCHAR2(5) NOT NULL	Case Source How this case origianated Prompt Table: RC_SOURCE_TBL
35	RC_STATUS	Character(5)	VARCHAR2(5) NOT NULL	Case Status Prompt Table: RC_STATUS_TBL
36	STATUS_CATEGORY	Character(1)	VARCHAR2(1) NOT NULL	Categories for Case Status. These drive processing. C=Closed D=Canceled H=Case is on hold O=Case is Open
37	RC_SUMMARY	Character(80)	VARCHAR2(80) NOT NULL	Typically a summary of a long field.
38	RC_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Specialty type. Used on HD case. Prompt Table: RC_CTD_TYP_F_VW Set Control Field: RC_CATEGORY
39	RC_VERTICAL	Character(4)	VARCHAR2(4) NOT NULL	Specifies the CallCenter type. See translates for valid values. HD=HelpDesk SW=Support
40	RES_FIRST_CNTCT	Character(1)	VARCHAR2(1) NOT NULL	Resolved by First Contact Y/N Table Edit
41	ROW_ADDED_DTTM	DateTime(26)	TIMESTAMP	This is a datetime field for record creation.
42	ROW_LASTMANT_DTTM	DateTime(26)	TIMESTAMP	The date and time that the row was last updated.
43	SECURE_CASE_FLG	Character(1)	VARCHAR2(1) NOT NULL	Used in HRHD to Secure the case so that only people in the provider group assigned to the case can access the case. N=No Y=Yes
44	TARGET_CLOSE_DATE	Date(10)	DATE	Target close date
45	CASE_STATUS_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
46	CASE_PRIORIT_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
47	CASE_CATEGOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
48	CASE_TYPE_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
49	CASE_IMPACT_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
50	CASE_PROBTYP_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
51	CASE_SEVERIT_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
52	CASE_SOURCE_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
53	PROVIDER_GRP_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
54	PRODUCT_SETID	Character(5)	VARCHAR2(5) NOT NULL	This is the field for Dashboard setid recname.
55	AGREEMENT_SETID	Character(5)	VARCHAR2(5) NOT NULL	AGREEMENT_SETID field is added to the PROD_ITEM record. This is because we may select a template from a different SETID than the product definition
56	RC_RED_DTTM	DateTime(26)	TIMESTAMP	The date time that this case will turn Red if it has not yet been resolved.
57	RC_YELLOW_DTTM	DateTime(26)	TIMESTAMP	The date time that this case will turn yellow if it has not yet been resolved.
58	ESCALATION_COUNT	Number(3,0)	SMALLINT NOT NULL	Escalation Count. Used in Case to register the number of times the case got escalated.
59	ESCALATION_DTTM	DateTime(26)	TIMESTAMP	Escalation Date Time. Used in RC_CASE to register the Date Time of Last Escalation.
60	SURVEY_SCORE	Number(3,0)	SMALLINT NOT NULL	Survey Score. This is used in Case Component. To store Survey Score calculated from Branch Script.
61	HOLD_DTTM	DateTime(26)	TIMESTAMP	Last Time Placed on Hold
62	HOLD_COUNT	Number(3,0)	SMALLINT NOT NULL	Number of times the case was placed on hold. Used for the Services Dashboard.
63	HOLD_MINUTES	Number(12,0)	DECIMAL(12) NOT NULL	Total time on hold. Used for the Services Dashboard.
64	CANCEL_DTTM	DateTime(26)	TIMESTAMP	Canceled Date Time
65	REOPEN_DTTM	DateTime(26)	TIMESTAMP	Date Time the case was reopened. Used for the Services Dashboard.
66	PARENT_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Parent Flag
67	CHILD_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Enable Child Status flag for Case Rlationship
68	CHILD_COUNT	Number(5,0)	INTEGER NOT NULL	Number of children this case is the parent to. This does not include multipule levels, just direct children. Used for the Services Dashboard.
69	PRODUCT_GROUP	Character(10)	VARCHAR2(10) NOT NULL	PRoduct Group
70	RY_DIALOG_NAME	Character(254)	VARCHAR2(254) NOT NULL	Field is used to capture a Dialog Name.