RC_PG_AGT_CLS_V

(SQL View)
 Index Back

Arrival vs closure

.View used to find all the closure for helpdesk agent

SELECT A.CASE_ID , A.BUSINESS_UNIT , B.SETID , E.RC_MANAGER , A.PROVIDER_GRP_ID , CATGRY.RC_SHORT_DESCR , E.NAME1 , (%Sql(RC_PRIORITY_DESCR, B.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, B.SETID, A.RC_SEVERITY)) , (%Sql(RC_CASETYPE_DESCR, B.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, B.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.rc_summary , ( 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.ASSIGNED_TO , CASE WHEN EMP.BO_NAME_DISPLAY <> ' ' THEN %Substring(EMP.BO_NAME_DISPLAY, 1, 50) END FROM PS_RC_STATUS_TBL B , ps_rc_status_log stlog , ((((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_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 ('C','D') 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 CATEGORY_DESCR Character(30) VARCHAR2(30) NOT NULL Category Description field. Used in Text Tray
7 PROVIDER_GRP_NAME Character(50) VARCHAR2(50) NOT NULL Provider Group Name
8 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.
9 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.
10 RC_SHORT_DESCR Character(20) VARCHAR2(20) NOT NULL Short Description. Used on may CallCenter set-up tables
11 RC_SHORT_DESCR4 Character(20) VARCHAR2(20) NOT NULL Field created for pivot grid analytics
12 ESCALATION_FLAG Character(1) VARCHAR2(1) NOT NULL Escalation flag for pivot grids
13 GLOBAL_FLAG Character(1) VARCHAR2(1) NOT NULL Global Case flag for relationship
14 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
15 BO_NAME_DISPLAY_5 Character(80) VARCHAR2(80) NOT NULL Full name of the Business Object
16 RC_SHORT_DESCR1 Character(20) VARCHAR2(20) NOT NULL Short Description. Used on may CallCenter set-up tables
17 RC_SHORT_DESCR2 Character(20) VARCHAR2(20) NOT NULL Short Description. Used on may CallCenter set-up tables
18 STATUS_CHANGE_DTTM DateTime(26) TIMESTAMP Status change date time. Used for status log.
19 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
20 ROW_ADDEDDATE Character(30) VARCHAR2(30) NOT NULL Field created for row added date in pivots
21 ROW_LAST_UPDATE Character(30) VARCHAR2(30) NOT NULL Field created for row last updated
22 CASE_ARRIVALS Character(10) VARCHAR2(10) NOT NULL Contains the string 'Arrivals' for use with charting.
23 MONTH Character(20) VARCHAR2(20) NOT NULL Used in monthly view in case arrival vs closure pivot
24 QUARTER_VIEW Character(20) VARCHAR2(20) NOT NULL Field created for quarter view in pivots
25 CREATION_DATE Date(10) DATE Creation date of the case.
26 RC_SUMMARY Character(80) VARCHAR2(80) NOT NULL Typically a summary of a long field.
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 ASSIGNED_TO Character(15) VARCHAR2(15) NOT NULL This is a field to store assignment information.
32 BO_NAME_DISPLAY Character(80) VARCHAR2(80) NOT NULL Full name of the Business Object