RC_PG_MGR_CLSRE

(SQL View)
 Index Back

Arrival vs closure

.Record for all manager closures

SELECT A.CASE_ID , A.BUSINESS_UNIT , E.SETID , E.RC_MANAGER , A.PROVIDER_GRP_ID ,A.ASSIGNED_TO , CATGRY.RC_SHORT_DESCR , 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 , ( 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 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 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 RC_MANAGER Character(15) VARCHAR2(15) NOT NULL Call Center Manager. Used in call center business unit setup.
5 PROVIDER_GRP_ID Character(10) VARCHAR2(10) NOT NULL Identification number for the provider group.
6 ASSIGNED_TO Character(15) VARCHAR2(15) NOT NULL This is a field to store assignment information.
7 CATEGORY_DESCR Character(30) VARCHAR2(30) NOT NULL Category Description field. Used in Text Tray
8 PROVIDER_GRP_NAME Character(50) VARCHAR2(50) NOT NULL Provider Group Name
9 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.
10 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.
11 RC_SHORT_DESCR Character(20) VARCHAR2(20) NOT NULL Short Description. Used on may CallCenter set-up tables
12 RC_SHORT_DESCR4 Character(20) VARCHAR2(20) NOT NULL Field created for pivot grid analytics
13 ESCALATION_FLAG Character(1) VARCHAR2(1) NOT NULL Escalation flag for pivot grids
14 GLOBAL_FLAG Character(1) VARCHAR2(1) NOT NULL Global Case flag for relationship
15 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
16 BO_NAME_DISPLAY_5 Character(80) VARCHAR2(80) NOT NULL Full name of the Business Object
17 RC_SHORT_DESCR1 Character(20) VARCHAR2(20) NOT NULL Short Description. Used on may CallCenter set-up tables
18 RC_SHORT_DESCR2 Character(20) VARCHAR2(20) NOT NULL Short Description. Used on may CallCenter set-up tables
19 STATUS_CHANGE_DTTM DateTime(26) TIMESTAMP Status change date time. Used for status log.
20 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
21 ROW_ADDEDDATE Character(30) VARCHAR2(30) NOT NULL Field created for row added date in pivots
22 ROW_LAST_UPDATE Character(30) VARCHAR2(30) NOT NULL Field created for row last updated
23 CASE_ARRIVALS Character(10) VARCHAR2(10) NOT NULL Contains the string 'Arrivals' for use with charting.
24 MONTH Character(20) VARCHAR2(20) NOT NULL Used in monthly view in case arrival vs closure pivot
25 QUARTER_VIEW Character(20) VARCHAR2(20) NOT NULL Field created for quarter view in pivots
26 CREATION_DATE Date(10) DATE Creation date of the case.
27 FROM_DATE Date(10) DATE From Date
28 TO_DATE Date(10) DATE To Date
29 RES_FIRST_CNTCT Character(1) VARCHAR2(1) NOT NULL Resolved by First Contact
30 ANNUAL_VIEW Character(6) VARCHAR2(6) NOT NULL Field created for annual view
31 RC_SUMMARY Character(80) VARCHAR2(80) NOT NULL Typically a summary of a long field.