RC_KB_MGR_CLS_V

(SQL View)
 Index Back

Case Closures for Managers

Record for case closures of managers. This data is based on all provider groups in which a manager is responsible. This view is used in RC_KBMGRARCL_VW

SELECT A.CASE_ID , A.BUSINESS_UNIT , E.SETID , A.DISP_TMPL_FAM_CD , E.RC_MANAGER , A.PROVIDER_GRP_ID ,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)) , E.NAME1 , (%Sql(RC_PRIORITY_DESCR, E.SETID, A.RC_PRIORITY)) , (%Sql(RC_SLA_CATEGORY, A.RC_YELLOW_DTTM, A.RC_RED_DTTM, A.RC_PRIORITY, A.RC_REST_DATE)) , (%Sql(RC_SEVERITY_DESCR, E.SETID, A.RC_SEVERITY)) , (%Sql(RC_CASETYPE_DESCR, E.SETID, A.CASE_TYPE)) , CASE WHEN A.ESCALATION_DTTM IS NOT NULL THEN 'Y' ELSE 'N' END , A.GLOBAL_FLAG , A.SECURE_CASE_FLG , (%Sql(RC_ASSIGN_NAME, A.ASSIGNED_TO)) , (%Sql(RC_SOURCE_DESCR, E.SETID, A.RC_SOURCE)) ,B.RC_SHORT_DESCR ,stlog.status_change_dttm ,A.ROW_LASTMANT_DTTM , TO_CHAR(CAST((A.ROW_ADDED_DTTM) AS TIMESTAMP) ,'YYYY-MM-DD-HH24.MI') , TO_CHAR(CAST((A.ROW_LASTMANT_DTTM) AS TIMESTAMP) ,'YYYY-MM-DD-HH24.MI') , 'Closure' ,to_char(stlog.status_change_dttm ,'yyyy')%Concat'-' %Concat to_char(stlog.status_change_dttm ,'MM')%Concat'('%Concat to_char(stlog.status_change_dttm ,'MON')%Concat')' Monthly ,CASE WHEN to_char(stlog.status_change_dttm ,'MON') IN('JAN' ,'FEB' ,'MAR')THEN to_char(stlog.status_change_dttm ,'yyyy')%Concat ':Q1' WHEN to_char(stlog.status_change_dttm ,'MON') IN('APR' ,'MAY' ,'JUN')THEN to_char(stlog.status_change_dttm ,'yyyy')%Concat ':Q2' WHEN to_char(stlog.status_change_dttm ,'MON') IN('JUL' ,'AUG' ,'SEP')THEN to_char(stlog.status_change_dttm ,'yyyy')%Concat ':Q3' WHEN to_char(stlog.status_change_dttm ,'MON') IN('OCT' ,'NOV' ,'DEC')THEN to_char(stlog.status_change_dttm ,'yyyy')%Concat ':Q4' END Quarther ,a.creation_date ,a.closed_date , ( SELECT TRUNC(stlog.status_change_dttm , 'IW')-1 FROM_DATE FROM %SelectDummyTable) ,( SELECT NEXT_DAY(TRUNC(stlog.status_change_dttm ,'IW') ,'SATURDAY') TO_DATE FROM %SelectDummyTable) ,a.RES_FIRST_CNTCT ,to_char(stlog.status_change_dttm ,'yyyy') ,a.rc_summary ,CASE WHEN (%DateDiff(%DatePart(a.creation_date), a.closed_date)) = 0 THEN 1 ELSE (%DateDiff(%DatePart(a.creation_date), a.closed_date)) END ,'PGMACSEC:' %Concat A.PROVIDER_GRP_ID %Concat E.RC_MANAGER FROM PS_RC_STATUS_TBL B , PS_RF_PROVIDER_GRP E , ps_rc_status_log stlog , ((PS_RC_CASE 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'))))) WHERE E.PROVIDER_GRP_ID = A.PROVIDER_GRP_ID AND B.STATUS_CATEGORY IN ('C','D') 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 STLOG.CASE_ID = A.CASE_ID AND STLOG.BUSINESS_UNIT = A.BUSINESS_UNIT AND STLOG.STATUS_CATEGORY = B.STATUS_CATEGORY AND STLOG.status_change_dttm = ( SELECT MAX(SUBST.status_change_dttm) FROM PS_RC_STATUS_LOG SUBST WHERE SUBST.CASE_ID = A.CASE_ID AND SUBST.BUSINESS_UNIT = A.BUSINESS_UNIT AND SUBST.STATUS_CATEGORY = B.STATUS_CATEGORY) 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 RC_BUS_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 DISP_TMPL_FAM_CD Character(30) VARCHAR2(30) NOT NULL Display Template Family Code
5 RC_MANAGER Character(15) VARCHAR2(15) NOT NULL Call Center Manager. Used in call center business unit setup.
6 PROVIDER_GRP_ID Character(10) VARCHAR2(10) NOT NULL Identification number for the provider group.
7 ASSIGNED_TO Character(15) VARCHAR2(15) NOT NULL This is a field to store assignment information.
8 CATEGORY_DESCR Character(30) VARCHAR2(30) NOT NULL Category Description field. Used in Text Tray
9 RC_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL CTD - Type Description
10 RC_DETAIL_DESCR Character(30) VARCHAR2(30) NOT NULL CTD - Detail Description
11 PROVIDER_GRP_NAME Character(50) VARCHAR2(50) NOT NULL Provider Group Name
12 PRIORITY_NAME Character(30) VARCHAR2(30) NOT NULL Holds the names of the priorities. 01/16/03: Set the Format Type to "Mixedcase", from Uppercase.
13 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.
14 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.
15 RC_SHORT_DESCR3 Character(20) VARCHAR2(20) NOT NULL Short Description
16 ESCALATION_FLAG Character(1) VARCHAR2(1) NOT NULL Escalation flag for pivot grids
17 GLOBAL_FLAG Character(1) VARCHAR2(1) NOT NULL Global Case flag for relationship
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 BO_NAME_DISPLAY_5 Character(80) VARCHAR2(80) NOT NULL Full name of the Business Object
20 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.
21 RC_SHORT_DESCR5 Character(20) VARCHAR2(20) NOT NULL Short Description
22 STATUS_CHANGE_DTTM DateTime(26) TIMESTAMP Status change date time. Used for status log.
23 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
24 ROW_ADDEDDATE Character(30) VARCHAR2(30) NOT NULL Field created for row added date in pivots
25 ROW_LAST_UPDATE Character(30) VARCHAR2(30) NOT NULL Field created for row last updated
26 CASE_ARRIVALS Character(10) VARCHAR2(10) NOT NULL Contains the string 'Arrivals' for use with charting.
27 MONTH Character(20) VARCHAR2(20) NOT NULL Used in monthly view in case arrival vs closure pivot
28 QUARTER_VIEW Character(20) VARCHAR2(20) NOT NULL Field created for quarter view in pivots
29 CREATION_DATE Date(10) DATE Creation date of the case.
30 CLOSED_DATE Date(10) DATE Date Closed
31 FROM_DATE Date(10) DATE From Date
32 TO_DATE Date(10) DATE To Date
33 RES_FIRST_CNTCT Character(1) VARCHAR2(1) NOT NULL Resolved by First Contact
34 ANNUAL_VIEW Character(6) VARCHAR2(6) NOT NULL Field created for annual view
35 RC_SUMMARY Character(80) VARCHAR2(80) NOT NULL Typically a summary of a long field.
36 CASE_AGE_NUM Number(5,0) INTEGER NOT NULL Shows Case Age as number
37 CASE_ES_SRCH_SECID Character(100) VARCHAR2(100) NOT NULL Secuirty attribute field for Search Indexes used in Insights Dashboard