RC_MGR_BLOG_V

(SQL View)
 Index Back

Team's Open Cases

View contains a list of open cases for a call center manager's team. The call center manager is defined on the provider group page. This table joins cases in a status category of open with the the call center manager that has provider groups assigned to those open cases.

SELECT A.CASE_ID , A.BUSINESS_UNIT , E.SETID , OPRALAS1.OPRID , A.DISP_TMPL_FAM_CD , E.RC_MANAGER , (%Sql(RC_PRIORITY_DESCR, E.SETID, A.RC_PRIORITY)) , E.NAME1 AS PROVIDER_GROUP , (%Sql(RC_ASSIGN_NAME, A.ASSIGNED_TO)) ,B.RC_SHORT_DESCR AS STATUS , CASE WHEN (%DateDiff(%DatePart(A.ROW_ADDED_DTTM), %CurrentDateIn)) = 0 THEN 1 ELSE (%DateDiff(%DatePart(A.ROW_ADDED_DTTM), %CurrentDateIn)) END , CASE WHEN PMEM.PERSON_ID IS NULL AND A.SECURE_CASE_FLG = 'Y' THEN 'Secured' ELSE %Substring(A.RC_SUMMARY,1,50) END , CASE WHEN EMP.BO_NAME_DISPLAY <> ' ' THEN %Substring(EMP.BO_NAME_DISPLAY, 1, 50) END , %DatePart(A.ROW_ADDED_DTTM) , %DatePart(A.ROW_LASTMANT_DTTM) , (%Sql(RC_SLA_CATEGORY, A.RC_YELLOW_DTTM, A.RC_RED_DTTM, A.RC_PRIORITY, A.RC_REST_DATE)) , (%Sql(RC_SOURCE_DESCR, E.SETID, A.RC_SOURCE)) , LOWER(A.PRODUCT_GROUP) ,(%Sql(RC_CASE_AGE,DEFTAGE.RC_CHART_TIME, A.ROW_ADDED_DTTM, DEFTAGE.FROM_DAY, DEFTAGE.TO_DAY, DEFTAGE.CASE_AGE_RANGE)) , (%Sql(RC_SEVERITY_DESCR, E.SETID, A.RC_SEVERITY)) , A.SECURE_CASE_FLG , CASE WHEN A.ESCALATION_DTTM IS NOT NULL THEN 'Y' ELSE 'N' END , A.GLOBAL_FLAG , CATGRY.RC_SHORT_DESCR , (%Sql(RC_CASETYPE_DESCR, E.SETID, A.CASE_TYPE)) , (%Sql(RC_PRIORITY_CAT, E.SETID, A.RC_PRIORITY)) AS PRIORITY , (%Sql(RC_SLA_CATEG_SORT, A.RC_YELLOW_DTTM, A.RC_RED_DTTM, A.RC_PRIORITY, A.RC_REST_DATE)) AS SLA_CONSUMED ,A.ASSIGNED_TO FROM PS_RC_STATUS_TBL B , PS_RF_PROVIDER_GRP E , ((((PS_RC_CASE A LEFT OUTER JOIN PS_BO_NAME EMP ON (A.BO_ID_CUST = EMP.BO_ID AND EMP.PRIMARY_IND = 'Y')) LEFT OUTER JOIN PSOPRALIAS OPRALAS1 ON (1=1 AND OPRALAS1.OPRALIASTYPE = 'PER') LEFT OUTER JOIN PS_RF_GRP_MEMBER PMEM ON (PMEM.PROVIDER_GRP_ID = A.PROVIDER_GRP_ID AND PMEM.PERSON_ID = OPRALAS1.PERSON_ID AND PMEM.SETID IN (%Sql(RC_SETID_BU, A.BUSINESS_UNIT, 'RF_GRP_MEMBER'))) LEFT OUTER JOIN PSOPRALIAS OPRALAS ON (1=1 AND OPRALAS.OPRID = OPRALAS1.OPRID) AND PMEM.STATUS = 'A' ) LEFT OUTER JOIN PS_RC_CATEGORY_TBL CATGRY ON (CATGRY.RC_CATEGORY = A.RC_CATEGORY AND CATGRY.STATUS = 'A' AND CATGRY.SETID IN (%Sql(RC_SETID_BU, A.BUSINESS_UNIT, 'RC_CATEGORY_TBL'))) ) LEFT OUTER JOIN PS_RC_DFLT_CASEAGE DEFTAGE ON A.BUSINESS_UNIT = DEFTAGE.BUSINESS_UNIT AND (%Sql(RC_CASE_AGE,DEFTAGE.RC_CHART_TIME, A.ROW_ADDED_DTTM, DEFTAGE.FROM_DAY, DEFTAGE.TO_DAY, DEFTAGE.CASE_AGE_RANGE)) <> 'NULL') WHERE B.STATUS_CATEGORY IN ('O','H') AND E.PROVIDER_GRP_ID = A.PROVIDER_GRP_ID AND E.SETID = (%Sql(RC_SETID_BU, A.BUSINESS_UNIT, 'RF_PROVIDER_GRP')) AND B.RC_STATUS = A.RC_STATUS AND B.SETID = (%Sql(RC_SETID_BU, A.BUSINESS_UNIT, 'RC_STATUS_TBL')) AND A.DISP_TMPL_FAM_CD = 'CRM_HHD'

# 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
3 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.
4 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
5 DISP_TMPL_FAM_CD Character(30) VARCHAR2(30) NOT NULL Display Template Family Code
6 RC_MANAGER Character(15) VARCHAR2(15) NOT NULL Call Center Manager. Used in call center business unit setup.
7 RC_SHORT_DESCR Character(20) VARCHAR2(20) NOT NULL Short Description. Used on may CallCenter set-up tables
8 NAME1 Character(50) VARCHAR2(50) NOT NULL Generic 50 character name field for storing names.
9 BO_NAME_DISPLAY_5 Character(80) VARCHAR2(80) NOT NULL Full name of the Business Object
10 RC_SHORT_DESCR5 Character(20) VARCHAR2(20) NOT NULL Short Description
11 CASE_AGE_RANGE Character(30) VARCHAR2(30) NOT NULL Field created for case age range
12 RC_SUMMARY Character(80) VARCHAR2(80) NOT NULL Typically a summary of a long field.
13 BO_NAME_DISPLAY Character(80) VARCHAR2(80) NOT NULL Full name of the Business Object
14 ROW_ADDEDDATE Character(30) VARCHAR2(30) NOT NULL Field created for row added date in pivots
15 ROW_LAST_UPDATE Character(30) VARCHAR2(30) NOT NULL Field created for row last updated
16 RC_SHORT_DESCR6 Character(20) VARCHAR2(20) NOT NULL Short Description
17 RC_SHORT_DESCR1 Character(20) VARCHAR2(20) NOT NULL Short Description. Used on may CallCenter set-up tables
18 PRODUCT_GROUP Character(10) VARCHAR2(10) NOT NULL PRoduct Group
19 DAY_RANGE Character(15) VARCHAR2(15) NOT NULL Range of Days for reporting purposes.
20 RC_SHORT_DESCR2 Character(20) VARCHAR2(20) NOT NULL Short Description. Used on may CallCenter set-up tables
21 PB_1 Character(1) VARCHAR2(1) NOT NULL Push Button
N=No
Y=Yes
22 PB_2 Character(1) VARCHAR2(1) NOT NULL Push Button to be used in pages for actions
N=No
Y=Yes
23 PB_3 Character(1) VARCHAR2(1) NOT NULL Push Button
N=No
Y=Yes
24 RC_SHORT_DESCR4 Character(20) VARCHAR2(20) NOT NULL Field created for pivot grid analytics
25 RC_SHORT_DESCR3 Character(20) VARCHAR2(20) NOT NULL Short Description
26 PRIORITY Number(3,0) SMALLINT NOT NULL Priority
27 SLA_CONSUMED Number(3,0) SMALLINT NOT NULL The SLA Consumed is used in Case Assignment History to store the percentage of SLA that has been consumed by each assignee on a case.
28 ASSIGNED_TO Character(15) VARCHAR2(15) NOT NULL This is a field to store assignment information.