RC_MGR_BK_ESQ_V

(SQL View)
 Index Back

Manager Backlog Insights

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. This view is used in HR Helpdesk Manager Backlog Insights

SELECT A.CASE_ID , A.BUSINESS_UNIT , (%Sql(RC_BUS_UNIT_DESCR, A.BUSINESS_UNIT)) , E.SETID , A.DISP_TMPL_FAM_CD , E.RC_MANAGER , (%Sql(RC_ASSIGN_NAME, E.RC_MANAGER)) , 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 , (%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)) ,(%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_TYPE_DESCR,B.SETID,A.RC_TYPE)) , (%Sql(RC_DETAIL_DESCR,B.SETID,A.RC_DETAIL)) , (%Sql(RC_CASETYPE_DESCR, E.SETID, A.CASE_TYPE)) , (%Sql(RC_PRIORITY_DESCR, 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 , A.CREATION_DATE , A.ROW_LASTMANT_DTTM , 'PGMGRSEC:' %Concat A.PROVIDER_GRP_ID %Concat E.RC_MANAGER FROM PS_RC_STATUS_TBL B , (((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 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') LEFT OUTER JOIN PS_RF_PROVIDER_GRP E ON ( E.PROVIDER_GRP_ID = A.PROVIDER_GRP_ID AND E.SETID IN (%Sql(RC_SETID_BU, A.BUSINESS_UNIT, 'RF_PROVIDER_GRP'))) LEFT OUTER JOIN PS_RF_GRP_MEMBER PMEM ON (PMEM.PROVIDER_GRP_ID = A.PROVIDER_GRP_ID AND PMEM.STATUS = 'A' AND PMEM.PERSON_ID = E.RC_MANAGER AND PMEM.SETID IN (%Sql(RC_SETID_BU, A.BUSINESS_UNIT, 'RF_GRP_MEMBER'))) WHERE B.STATUS_CATEGORY IN ('O','H') AND 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 RC_BU_DESCR Character(30) VARCHAR2(30) NOT NULL Business Unit descr to be displayed on the case pages.
4 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.
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 MANAGER_NAME Character(30) VARCHAR2(30) NOT NULL Manager Name
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 RC_SHORT_DESCR6 Character(20) VARCHAR2(20) NOT NULL Short Description
15 RC_DESC_SOURCE Character(20) VARCHAR2(20) NOT NULL Used to display short description of Case Source in search results grid on case search page.
16 DAY_RANGE Character(15) VARCHAR2(15) NOT NULL Range of Days for reporting purposes.
17 RC_DESC_SEVERITY Character(20) VARCHAR2(20) NOT NULL Used to display short description of Case Severity in search results grid on case search page.
18 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
19 ESCALATION_FLAG Character(1) VARCHAR2(1) NOT NULL Escalation flag for pivot grids
20 GLOBAL_FLAG Character(1) VARCHAR2(1) NOT NULL Global Case flag for relationship
21 RC_SHORT_DESCR4 Character(20) VARCHAR2(20) NOT NULL Field created for pivot grid analytics
22 RC_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL CTD - Type Description
23 RC_DETAIL_DESCR Character(30) VARCHAR2(30) NOT NULL CTD - Detail Description
24 RC_SHORT_DESCR3 Character(20) VARCHAR2(20) NOT NULL Short Description
25 RC_DESC_PRIORITY Character(20) VARCHAR2(20) NOT NULL Used to display short description of Case Priority in search results grid on case search page.
26 SLA_COLOR Character(6) VARCHAR2(6) NOT NULL SLA Color - can be red, green or yellow depending on how close the case is to missing the required restore time goal per the service level agreement.
27 ASSIGNED_TO Character(15) VARCHAR2(15) NOT NULL This is a field to store assignment information.
28 CREATION_DATE Date(10) DATE Creation date of the case.
29 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
30 CASE_ES_SRCH_SECID Character(100) VARCHAR2(100) NOT NULL Secuirty attribute field for Search Indexes used in Insights Dashboard