SELECT DM_VW_RESOURCE_DIM.description, decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M1_UTILIZED, '2', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M2_UTILIZED, '3', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M3_UTILIZED, '4', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M4_UTILIZED, '5', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M5_UTILIZED, '6', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M6_UTILIZED, '7', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M7_UTILIZED, 1.0 - DM_PVC_ROLLOFF_RESOURCE.M1_UTILIZED), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M2_UTILIZED, '2', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M3_UTILIZED, '3', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M4_UTILIZED, '4', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M5_UTILIZED, '5', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M6_UTILIZED, '6', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M7_UTILIZED, '7', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M8_UTILIZED, 1.0 - DM_PVC_ROLLOFF_RESOURCE.M2_UTILIZED), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M3_UTILIZED, '2', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M4_UTILIZED, '3', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M5_UTILIZED, '4', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M6_UTILIZED, '5', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M7_UTILIZED, '6', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M8_UTILIZED, '7', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M9_UTILIZED, 1.0 - DM_PVC_ROLLOFF_RESOURCE.M3_UTILIZED), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M4_UTILIZED, '2', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M5_UTILIZED, '3', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M6_UTILIZED, '4', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M7_UTILIZED, '5', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M8_UTILIZED, '6', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M9_UTILIZED, '7', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M10_UTILIZED, 1.0 - DM_PVC_ROLLOFF_RESOURCE.M4_UTILIZED), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M5_UTILIZED, '2', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M6_UTILIZED, '3', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M7_UTILIZED, '4', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M8_UTILIZED, '5', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M9_UTILIZED, '6', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M10_UTILIZED, '7', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M11_UTILIZED, 1.0 - DM_PVC_ROLLOFF_RESOURCE.M5_UTILIZED), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M6_UTILIZED, '2', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M7_UTILIZED, '3', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M8_UTILIZED, '4', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M9_UTILIZED, '5', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M10_UTILIZED, '6', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M11_UTILIZED, '7', 1.0 - DM_PVC_ROLLOFF_RESOURCE.M12_UTILIZED, 1.0 - DM_PVC_ROLLOFF_RESOURCE.M6_UTILIZED), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', DM_PVC_ROLLOFF_RESOURCE.MONTH1, '2', DM_PVC_ROLLOFF_RESOURCE.MONTH2, '3', DM_PVC_ROLLOFF_RESOURCE.MONTH3, '4', DM_PVC_ROLLOFF_RESOURCE.MONTH4, '5', DM_PVC_ROLLOFF_RESOURCE.MONTH5, '6', DM_PVC_ROLLOFF_RESOURCE.MONTH6, '7', DM_PVC_ROLLOFF_RESOURCE.MONTH7, DM_PVC_ROLLOFF_RESOURCE.MONTH1), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', DM_PVC_ROLLOFF_RESOURCE.MONTH2, '2', DM_PVC_ROLLOFF_RESOURCE.MONTH3, '3', DM_PVC_ROLLOFF_RESOURCE.MONTH4, '4', DM_PVC_ROLLOFF_RESOURCE.MONTH5, '5', DM_PVC_ROLLOFF_RESOURCE.MONTH6, '6', DM_PVC_ROLLOFF_RESOURCE.MONTH7, '7', DM_PVC_ROLLOFF_RESOURCE.MONTH8, DM_PVC_ROLLOFF_RESOURCE.MONTH2), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', DM_PVC_ROLLOFF_RESOURCE.MONTH3, '2', DM_PVC_ROLLOFF_RESOURCE.MONTH4, '3', DM_PVC_ROLLOFF_RESOURCE.MONTH5, '4', DM_PVC_ROLLOFF_RESOURCE.MONTH6, '5', DM_PVC_ROLLOFF_RESOURCE.MONTH7, '6', DM_PVC_ROLLOFF_RESOURCE.MONTH8, '7', DM_PVC_ROLLOFF_RESOURCE.MONTH9, DM_PVC_ROLLOFF_RESOURCE.MONTH3), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', DM_PVC_ROLLOFF_RESOURCE.MONTH4, '2', DM_PVC_ROLLOFF_RESOURCE.MONTH5, '3', DM_PVC_ROLLOFF_RESOURCE.MONTH6, '4', DM_PVC_ROLLOFF_RESOURCE.MONTH7, '5', DM_PVC_ROLLOFF_RESOURCE.MONTH8, '6', DM_PVC_ROLLOFF_RESOURCE.MONTH9, '7', DM_PVC_ROLLOFF_RESOURCE.MONTH10, DM_PVC_ROLLOFF_RESOURCE.MONTH4), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', DM_PVC_ROLLOFF_RESOURCE.MONTH5, '2', DM_PVC_ROLLOFF_RESOURCE.MONTH6, '3', DM_PVC_ROLLOFF_RESOURCE.MONTH7, '4', DM_PVC_ROLLOFF_RESOURCE.MONTH8, '5', DM_PVC_ROLLOFF_RESOURCE.MONTH9, '6', DM_PVC_ROLLOFF_RESOURCE.MONTH10, '7', DM_PVC_ROLLOFF_RESOURCE.MONTH11, DM_PVC_ROLLOFF_RESOURCE.MONTH5), decode(substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1), '1', DM_PVC_ROLLOFF_RESOURCE.MONTH6, '2', DM_PVC_ROLLOFF_RESOURCE.MONTH7, '3', DM_PVC_ROLLOFF_RESOURCE.MONTH8, '4', DM_PVC_ROLLOFF_RESOURCE.MONTH9, '5', DM_PVC_ROLLOFF_RESOURCE.MONTH10, '6', DM_PVC_ROLLOFF_RESOURCE.MONTH11, '7', DM_PVC_ROLLOFF_RESOURCE.MONTH12, DM_PVC_ROLLOFF_RESOURCE.MONTH6), DM_VW_RES_ATT_30_39_DIM.descr35, DM_VW_RES_ATT_30_39_DIM.descr36, DM_VW_RES_ATT_40_49_DIM.DESCR48, DM_VW_OBS20BR_DIM.ALT_DESCRIPTION, DM_VW_OBS20BR_DIM.RANK_DESCRIPTION, DM_VW_OBS20BR_DIM.RANK_VALUE, DM_PVC_RES_CUST_DATA_DIM.REGISTERED_NURSE, DM_PVC_RES_CUST_DATA_DIM.ICR_EMP_STAT_COMM, DM_PVC_RES_CUST_DATA_DIM.ICR_JBTITLE_EFF_DATE, DM_VW_RESOURCE_DIM.resource_code, DM_VW_RES_ATT_40_49_DIM.DESCR46, dm_vw_portal_member_res.portfolio_name, DM_VW_RESOURCE_LONG_TEXT.ICR_ADD_COMMENTS, decode(DM_PVC_ROLLOFF_RESWRK.WORK_TYPE,'APR','Approved','REQ','Requested', 'None'), DM_PVC_ROLLOFF_RESWRK.WORK_ID, DM_PVC_ROLLOFF_RESWRK.PROJECT_NAME, DM_PVC_ROLLOFF_RESWRK.START_DATE, DM_PVC_ROLLOFF_RESWRK.FINISH_DATE, DM_PVC_ROLLOFF_RESWRK.PROJECT_MANAGER FROM DM_VW_RESOURCE_DIM, DM_PVC_ROLLOFF_RESOURCE, DM_VW_RES_ATT_30_39_DIM, DM_VW_RES_ATT_40_49_DIM, DM_VW_OBS20BR_DIM, DM_PVC_RES_CUST_DATA_DIM, DM_VW_PORTAL_MEMBER dm_vw_portal_member_res, DM_VW_RESOURCE_LONG_TEXT, DM_PVC_ROLLOFF_RESWRK, PDB_PORTAL_FILTER pdb_portal_filter_res, DM_VW_USER_RESOURCE_BR WHERE ( DM_VW_RESOURCE_DIM.RESOURCE_CODE=DM_VW_USER_RESOURCE_BR.structure_code ) AND ( upper(DM_VW_USER_RESOURCE_BR.user_name)= upper(@Variable('BOUSER')) ) AND ( DM_VW_RESOURCE_DIM.RESOURCE_CODE=pdb_portal_filter_res.structure_code ) AND ( pdb_portal_filter_res.filter_code=dm_vw_portal_member_res.portfolio_code ) AND ( upper(dm_vw_portal_member_res.user_name)= upper(@Variable('BOUSER')) ) AND ( DM_VW_RESOURCE_DIM.RESOURCE_CODE=DM_VW_RES_ATT_30_39_DIM.RESOURCE_CODE ) AND ( DM_VW_RESOURCE_DIM.RESOURCE_ID=DM_VW_OBS20BR_DIM.RESOURCE_ID ) AND ( DM_VW_RESOURCE_DIM.RESOURCE_ID=DM_PVC_RES_CUST_DATA_DIM.RESOURCE_ID ) AND ( DM_VW_RESOURCE_DIM.RESOURCE_ID=DM_VW_RES_ATT_40_49_DIM.RESOURCE_ID ) AND ( DM_VW_RESOURCE_DIM.RESOURCE_ID=DM_PVC_ROLLOFF_RESOURCE.RESOURCE_ID ) AND ( DM_PVC_ROLLOFF_RESOURCE.RESOURCE_ID=DM_PVC_ROLLOFF_RESWRK.RESOURCE_ID ) AND ( DM_VW_RESOURCE_DIM.RESOURCE_ID=DM_VW_RESOURCE_LONG_TEXT.RESOURCE_ID ) AND ( dm_vw_portal_member_res.portfolio_name = (@Prompt ('Select Portfolio','A','Portfolio Objects\Portfolio Name - Resource',MONO,FREE)) ) AND ( 1.0 - DM_PVC_ROLLOFF_RESOURCE.M2_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) > '2' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M3_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) > '3' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M4_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) > '4' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M5_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) > '5' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M6_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) > '6' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M7_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) < '2' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M8_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) < '3' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M9_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) < '4' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M10_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) < '5' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M11_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) < '6' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND 1.0 - DM_PVC_ROLLOFF_RESOURCE.M12_UTILIZED >= case when substr(@prompt('Select Start Period:','A','Resource Roll-Off Report\Start Month Year',Mono,Free,Persistent,,User:2),1,1) < '7' then 0 else @prompt('Remain Avail %:','N',,Mono,Free,Persistent,,User:0)/100.0 end AND DM_VW_OBS20BR_DIM.PRIMARY_IND = 'Y' AND DM_VW_OBS20BR_DIM.ALT_DESCRIPTION In @prompt('Select Role(s):','A','Resource Alternate Structures\ICR-Functional Roles (m:1)',Multi,Free,Persistent,,User:1) )