RC_AGT_ESKB_V

(SQL View)
 Index Back

Agent Backlog

All open cases for an Agent for all provider groups. This view is used in HR Helpdesk Agent Backlog Insights.

SELECT A.CASE_ID , A.BUSINESS_UNIT , (%Sql(RC_BUS_UNIT_DESCR, A.BUSINESS_UNIT)) , B.SETID , A.DISP_TMPL_FAM_CD , 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 , %Substring(A.RC_SUMMARY,1,50) , CASE WHEN EMP.BO_NAME_DISPLAY <> ' ' THEN %Substring(EMP.BO_NAME_DISPLAY, 1, 50) END , A.CREATION_DATE , 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, B.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, B.SETID, A.RC_SEVERITY)) , A.SECURE_CASE_FLG , CASE WHEN A.ESCALATION_DTTM IS NOT NULL THEN 'Y' ELSE 'N' END , A.GLOBAL_FLAG , A.ASSIGNED_TO , 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, B.SETID, A.CASE_TYPE)) , (%Sql(RC_PRIORITY_DESCR, B.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 , 'PGSEC:' %Concat A.PROVIDER_GRP_ID %Concat E.SETID %Concat A.ASSIGNED_TO 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')))) WHERE B.STATUS_CATEGORY IN ('O','H') 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 NAME1 Character(50) VARCHAR2(50) NOT NULL Generic 50 character name field for storing names.
7 BO_NAME_DISPLAY_5 Character(80) VARCHAR2(80) NOT NULL Full name of the Business Object
8 RC_SHORT_DESCR5 Character(20) VARCHAR2(20) NOT NULL Short Description
9 CASE_AGE_RANGE Character(30) VARCHAR2(30) NOT NULL Field created for case age range
10 RC_SUMMARY Character(80) VARCHAR2(80) NOT NULL Typically a summary of a long field.
11 BO_NAME_DISPLAY Character(80) VARCHAR2(80) NOT NULL Full name of the Business Object
12 CREATION_DATE Date(10) DATE Creation date of the case.
13 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
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 ASSIGNED_TO Character(15) VARCHAR2(15) NOT NULL This is a field to store assignment information.
22 RC_SHORT_DESCR4 Character(20) VARCHAR2(20) NOT NULL Field created for pivot grid analytics
23 RC_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL CTD - Type Description
24 RC_DETAIL_DESCR Character(30) VARCHAR2(30) NOT NULL CTD - Detail Description
25 RC_SHORT_DESCR3 Character(20) VARCHAR2(20) NOT NULL Short Description
26 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.
27 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.
28 CASE_ES_SRCH_SECID Character(100) VARCHAR2(100) NOT NULL Secuirty attribute field for Search Indexes used in Insights Dashboard