RY_SRY_ACE_VW

(SQL View)
 Index Back

SQL View

This View is used for Dialog Survey ACE Report.

SELECT RCTRL.PROBINST ,S.RY_RPT_ID ,S.RY_ORA_EXT_ID ,CASE WHEN S.RY_STATUS_SRVY IS NULL THEN 'No Status' WHEN S.RY_STATUS_SRVY ='C' THEN 'Completed' WHEN S.RY_STATUS_SRVY='O' THEN 'Open' WHEN S.RY_STATUS_SRVY='I' THEN 'Initial' WHEN S.RY_STATUS_SRVY=' ' THEN 'No Status' ELSE S.RY_STATUS_SRVY END AS RY_STATUS_SRVY ,'RY_MDATA.RY_DATA_NAME' ,' ' , CASE WHEN CSE.BUSINESS_UNIT IS NULL THEN 'Nil' WHEN CSE.BUSINESS_UNIT =' ' THEN 'Nil' ELSE CSE.BUSINESS_UNIT END AS BUSINESS_UNIT , CASE WHEN CSE.DISP_TMPL_FAM_CD IS NULL THEN 'No Family' WHEN CSE.DISP_TMPL_FAM_CD =' ' THEN 'No Family' ELSE CSE.DISP_TMPL_FAM_CD END AS DISP_TMPL_FAM_CD , CSE.BO_ID_CUST , CASE WHEN CSE.BO_NAME IS NULL THEN 'No Company' WHEN CSE.BO_NAME =' ' THEN 'No Company' ELSE %Substring(CSE.BO_NAME,1 ,30) END AS BO_NAME ,CSE.ROLE_TYPE_ID_CUST , CSE.BO_ID_CONTACT , %Substring(CSE.NAME ,1 ,30) , CSE.ROLE_TYPE_ID_CNTCT , CASE WHEN CSE.REGION_ID IS NULL THEN 'No Regions' WHEN CSE.REGION_ID = ' ' THEN 'No Regions' ELSE CSE.REGION_ID END AS REGION_ID ,CASE WHEN CSE.LOCATION IS NULL THEN 'No Locales' WHEN CSE.LOCATION = ' ' THEN 'No Locales' ELSE CSE.LOCATION END AS LOCATION ,CASE WHEN CSE.DEPTID IS NULL THEN 'No Dept ID' WHEN CSE.DEPTID =' ' THEN 'No Dept ID' ELSE CSE.DEPTID END AS DEPTID ,CASE WHEN CSE.RC_PRIORITY IS NULL THEN 'Nil' WHEN CSE.RC_PRIORITY=' ' THEN 'Nil' ELSE CSE.RC_PRIORITY END AS RC_PRIORITY ,CSE.ASSIGNED_TO , CASE WHEN CSE.NAME_ASSIGNED_TO IS NULL THEN 'No Assigned To' ELSE %Substring(CSE.NAME_ASSIGNED_TO ,1 ,30) END AS NAME_ASSIGNED_TO ,0 ,0 ,0 ,S.RY_SRVY_SCORE ,( SELECT COUNT(*) FROM PS_RY_RPT_SRVY_VW DLGVW WHERE DLGVW.DIALOG_ID=RCTRL.DIALOG_ID AND S.RY_RPT_ID=RCTRL.RY_RPT_ID AND DLGVW.RY_ORA_EXT_INS_ID = S.RY_ORA_EXT_INS_ID AND DLGVW.RY_SURVEY_SENT = 'Y') AS SURVEY_SENT ,( SELECT COUNT(*) FROM PS_RY_RPT_SRVY_VW DLGVW WHERE RCTRL.RY_RPT_ID = S.RY_RPT_ID AND RCTRL.DIALOG_ID = S.DIALOG_ID AND S.RY_STATUS_SRVY = 'C' AND S.DIALOG_ID = DLGVW.DIALOG_ID AND S.RY_ORA_EXT_INS_ID = DLGVW.RY_ORA_EXT_INS_ID AND DLGVW.RY_SURVEY_SENT = 'Y') AS SURVEY_COMPLETED ,( SELECT COUNT(*) FROM PS_RY_RPT_SRVY_VW DLGVW WHERE RCTRL.RY_RPT_ID = S.RY_RPT_ID AND RCTRL.DIALOG_ID = S.DIALOG_ID AND S.RY_STATUS_SRVY = 'O' AND S.DIALOG_ID = DLGVW.DIALOG_ID AND S.RY_ORA_EXT_INS_ID = DLGVW.RY_ORA_EXT_INS_ID AND DLGVW.RY_SURVEY_SENT = 'Y') AS SURVEY_OPEN FROM PS_RY_RPT_SRVY_DLG S LEFT OUTER JOIN PS_RY_CSE_DTL_VW CSE ON S.RY_ORA_EXT_ID=CSE.CASE_ID,PS_RY_RPT_SRVY_CTL RCTRL, PS_RY_RPT_SRVY_VW DLGVW2 WHERE RCTRL.RY_RPT_ID= S.RY_RPT_ID AND DLGVW2.DIALOG_ID=RCTRL.DIALOG_ID AND S.RY_RPT_ID=RCTRL.RY_RPT_ID AND DLGVW2.RY_ORA_EXT_INS_ID = S.RY_ORA_EXT_INS_ID AND DLGVW2.RY_SURVEY_SENT = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PROBINST Character(20) VARCHAR2(20) NOT NULL Optimization Problem Instance
2 RY_RPT_ID Number(18,0) DECIMAL(18) NOT NULL Report Id for ACR report temporay data
3 RY_ORA_EXT_ID Number(18,0) DECIMAL(18) NOT NULL Survey External Id - external system id for integration
4 RY_DOC_STATUS Character(10) VARCHAR2(10) NOT NULL Temp Dialog Status in Document designer.
5 RY_DATA_NAME Character(80) VARCHAR2(80) NOT NULL Document Name - Dialog Document, OLM
6 LABEL_TEXT Character(254) VARCHAR2(254) NOT NULL Activity Name
7 BUSINESS_UNIT_RC Character(5) VARCHAR2(5) NOT NULL Business Unit field for Case identified in Quality
8 DISP_TMPL_FAM_CD Character(30) VARCHAR2(30) NOT NULL Display Template Family Code
9 BO_ID_CUST Number(31,0) DECIMAL(31) NOT NULL This field stores the Business Object ID for the customer.
10 BO_NAME Character(50) VARCHAR2(50) NOT NULL Name Description of Business Object
11 ROLE_TYPE_ID_CUST Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the customer
12 BO_ID_CONTACT Number(31,0) DECIMAL(31) NOT NULL This field indicates the Business Object ID for the contact.
13 NAME Character(50) VARCHAR2(50) NOT NULL Name
14 ROLE_TYPE_ID_CNTCT Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the contact
15 REGION_ID Character(15) VARCHAR2(15) NOT NULL This field stores the region id value.
16 LOCATION Character(15) VARCHAR2(15) NOT NULL This field stores the Location Code.
17 DEPTID Character(15) VARCHAR2(15) NOT NULL Department
18 RC_PRIORITY Character(5) VARCHAR2(5) NOT NULL Case Priority
19 ASSIGNED_TO Character(15) VARCHAR2(15) NOT NULL This is a field to store assignment information.
20 NAME_ASSIGNED_TO Character(80) VARCHAR2(80) NOT NULL The name of the assigned agent.
21 RY_QUESTION_WEIGHT Signed Number(3,0) DECIMAL(2) NOT NULL Survey Document quesion weight
22 RY_ANSWER_WEIGHT Signed Number(3,0) DECIMAL(2) NOT NULL Survey Document answer weight
23 RY_QUESTION_SCORE Signed Number(19,0) DECIMAL(18) NOT NULL Survey Overall score -- overall running score for a survey instance.
24 RY_SRVY_SCORE Signed Number(19,0) DECIMAL(18) NOT NULL Survey Overall score -- overall running score for a survey instance.
25 RY_SRY_SENT Number(3,0) SMALLINT NOT NULL Count of Survey Sent
26 RY_SRVY_COMPLETED Number(3,0) SMALLINT NOT NULL Count of Survey Completed
27 RY_SRVY_OPEN Number(3,0) SMALLINT NOT NULL Count of Survey Opened