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'
|