Project and Portfolio Management Practitioners Forum
cancel

Resource Requests Portlet

Highlighted
MandyR
Trusted Contributor.

Resource Requests Portlet

Hi,

 

There is a OOTB portlet 'Resource Requests' in HP PPM. This portlet displays requests which are not fulfilled 100% i.e. resource request is partially fulfilled.

 

I want to customize this portlet and want to show requests -

1. All resource request which are not fulfilled 100%

2. Additionally requests that are fulfilled 100% and status as softbooked.

 

I am not understanding what need to be modified in the SQL code to achieve this.

Below is the SQL code of this portlet.Please help me on this as it is bit urgent.

 

*****************************************************************************

SELECT rp.resource_pool_name RP, krsc_utils.get_pool_managers_list(rp.resource_pool_id) RPM, sp.staffing_profile_name SP, krsc_utils.get_sp_managers_list(sp.staffing_profile_id) SPM, users.full_name REQUESTED_BY, positions.position_name POSITION, krsc_utils.get_meaning('RSC - Staffing Profile Position Status', positions.status_code) POSITION_STATUS, positions.ask_date DATE_REQUESTED, positions.need_by_date RESPONSE_NEEDED_BY, krsc_utils.get_meaning('RSC - Staffing Profile Parent Containers', sp.container_entity_type_code) CONTAINER_TYPE, KRSC_Utils.Get_SP_Linked_Entity_Name(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_NAME, krsc_utils.get_meaning('RSC - Staffing Profile Status', sp.status_code) STAFFING_PROFILE_STATUS, krsc_utils.get_sp_linked_entity_status(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_STATUS, roles.role_name ROLE, krsc_utils.get_unmet_demand_start_date(positions.position_id) UNMET_DEMAND_START, krsc_utils.get_unmet_demand_finish_date(positions.position_id) UNMET_DEMAND_END, krsc_utils.get_unmet_demand_hours(positions.position_id) TOTAL_UNMET_HOURS, rp.resource_pool_id RP_LINK, sp.staffing_profile_id SP_LINK, positions.position_comment NOTES, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd') UNMET_RANGE_START, ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd') UNMET_RANGE_END, krsc_utils.get_range_unmet_demand_hours(positions.position_id, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd'), ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd')) UNMET_RANGE_HOURS, pm_utils.Find_Full_Names(positions.resources_requested) REQUESTED_RESOURCES

FROM rsc_resource_pools rp, rsc_staffing_profiles sp,  rsc_positions positions, knta_users users, rsc_roles roles
WHERE sp.staffing_profile_id=positions.staffing_profile_id
and sp.status_code not in (4,5,6)
and positions.resource_pool_id  =rp.resource_pool_id (+)
and users.user_id(+)=positions.requested_by
and roles.role_id=positions.role_id
and (krsc_utils.can_access_rsc_pool(rp.resource_pool_id, [SYS.USER_ID])='Y'
        or krsc_utils.can_access_staff_prof(sp.staffing_profile_id, [SYS.USER_ID])='Y')
--This section comes from the filter fields
and rp.resource_pool_id in ([P.RP_ID])
AND EXISTS (SELECT 1 FROM rsc_resource_pool_managers rpm
    WHERE rpm.resource_pool_id = positions.resource_pool_id
         AND rpm.manager_user_id IN ([P.RPM_ID]))
and sp.staffing_profile_id in ([P.SP_ID])
AND EXISTS (SELECT 1 FROM rsc_staff_prof_managers spm
    WHERE spm.staffing_profile_id = positions.staffing_profile_id
         AND spm.manager_user_id IN ([P.SPM_ID]))
and sp.status_code in ([P.SPSTATUS])
and positions.status_code in ([P.POSITIONSTATUS])
and positions.need_by_date >=round(sysdate-[P.RES_PREVIOUS]*7-3, 'ww')
and positions.need_by_date <round(SYSDATE+[P.RES_FUTURE]*7+4, 'ww')
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
and positions.role_id in ([P.ROLE])
--End section from the filter fields
order by rp.resource_pool_name, sp.staffing_profile_nameSELECT rp.resource_pool_name RP, krsc_utils.get_pool_managers_list(rp.resource_pool_id) RPM, sp.staffing_profile_name SP, krsc_utils.get_sp_managers_list(sp.staffing_profile_id) SPM, users.full_name REQUESTED_BY, positions.position_name POSITION, krsc_utils.get_meaning('RSC - Staffing Profile Position Status', positions.status_code) POSITION_STATUS, positions.ask_date DATE_REQUESTED, positions.need_by_date RESPONSE_NEEDED_BY, krsc_utils.get_meaning('RSC - Staffing Profile Parent Containers', sp.container_entity_type_code) CONTAINER_TYPE, KRSC_Utils.Get_SP_Linked_Entity_Name(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_NAME, krsc_utils.get_meaning('RSC - Staffing Profile Status', sp.status_code) STAFFING_PROFILE_STATUS, krsc_utils.get_sp_linked_entity_status(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_STATUS, roles.role_name ROLE, krsc_utils.get_unmet_demand_start_date(positions.position_id) UNMET_DEMAND_START, krsc_utils.get_unmet_demand_finish_date(positions.position_id) UNMET_DEMAND_END, krsc_utils.get_unmet_demand_hours(positions.position_id) TOTAL_UNMET_HOURS, rp.resource_pool_id RP_LINK, sp.staffing_profile_id SP_LINK, positions.position_comment NOTES, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd') UNMET_RANGE_START, ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd') UNMET_RANGE_END, krsc_utils.get_range_unmet_demand_hours(positions.position_id, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd'), ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd')) UNMET_RANGE_HOURS, pm_utils.Find_Full_Names(positions.resources_requested) REQUESTED_RESOURCES
FROM rsc_resource_pools rp, rsc_staffing_profiles sp,  rsc_positions positions, knta_users users, rsc_roles roles
WHERE sp.staffing_profile_id=positions.staffing_profile_id
and sp.status_code not in (4,5,6)
and positions.resource_pool_id  =rp.resource_pool_id (+)
and users.user_id(+)=positions.requested_by
and roles.role_id=positions.role_id
and (krsc_utils.can_access_rsc_pool(rp.resource_pool_id, [SYS.USER_ID])='Y'
        or krsc_utils.can_access_staff_prof(sp.staffing_profile_id, [SYS.USER_ID])='Y')
--This section comes from the filter fields
and rp.resource_pool_id in ([P.RP_ID])
AND EXISTS (SELECT 1 FROM rsc_resource_pool_managers rpm
    WHERE rpm.resource_pool_id = positions.resource_pool_id
         AND rpm.manager_user_id IN ([P.RPM_ID]))
and sp.staffing_profile_id in ([P.SP_ID])
AND EXISTS (SELECT 1 FROM rsc_staff_prof_managers spm
    WHERE spm.staffing_profile_id = positions.staffing_profile_id
         AND spm.manager_user_id IN ([P.SPM_ID]))
and sp.status_code in ([P.SPSTATUS])
and positions.status_code in ([P.POSITIONSTATUS])
and positions.need_by_date >=round(sysdate-[P.RES_PREVIOUS]*7-3, 'ww')
and positions.need_by_date <round(SYSDATE+[P.RES_FUTURE]*7+4, 'ww')
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
and positions.role_id in ([P.ROLE])
--End section from the filter fields
order by rp.resource_pool_name, sp.staffing_profile_nameSELECT rp.resource_pool_name RP, krsc_utils.get_pool_managers_list(rp.resource_pool_id) RPM, sp.staffing_profile_name SP, krsc_utils.get_sp_managers_list(sp.staffing_profile_id) SPM, users.full_name REQUESTED_BY, positions.position_name POSITION, krsc_utils.get_meaning('RSC - Staffing Profile Position Status', positions.status_code) POSITION_STATUS, positions.ask_date DATE_REQUESTED, positions.need_by_date RESPONSE_NEEDED_BY, krsc_utils.get_meaning('RSC - Staffing Profile Parent Containers', sp.container_entity_type_code) CONTAINER_TYPE, KRSC_Utils.Get_SP_Linked_Entity_Name(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_NAME, krsc_utils.get_meaning('RSC - Staffing Profile Status', sp.status_code) STAFFING_PROFILE_STATUS, krsc_utils.get_sp_linked_entity_status(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_STATUS, roles.role_name ROLE, krsc_utils.get_unmet_demand_start_date(positions.position_id) UNMET_DEMAND_START, krsc_utils.get_unmet_demand_finish_date(positions.position_id) UNMET_DEMAND_END, krsc_utils.get_unmet_demand_hours(positions.position_id) TOTAL_UNMET_HOURS, rp.resource_pool_id RP_LINK, sp.staffing_profile_id SP_LINK, positions.position_comment NOTES, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd') UNMET_RANGE_START, ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd') UNMET_RANGE_END, krsc_utils.get_range_unmet_demand_hours(positions.position_id, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd'), ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd')) UNMET_RANGE_HOURS, pm_utils.Find_Full_Names(positions.resources_requested) REQUESTED_RESOURCES
FROM rsc_resource_pools rp, rsc_staffing_profiles sp,  rsc_positions positions, knta_users users, rsc_roles roles
WHERE sp.staffing_profile_id=positions.staffing_profile_id
and sp.status_code not in (4,5,6)
and positions.resource_pool_id  =rp.resource_pool_id (+)
and users.user_id(+)=positions.requested_by
and roles.role_id=positions.role_id
and (krsc_utils.can_access_rsc_pool(rp.resource_pool_id, [SYS.USER_ID])='Y'
        or krsc_utils.can_access_staff_prof(sp.staffing_profile_id, [SYS.USER_ID])='Y')
--This section comes from the filter fields
and rp.resource_pool_id in ([P.RP_ID])
AND EXISTS (SELECT 1 FROM rsc_resource_pool_managers rpm
    WHERE rpm.resource_pool_id = positions.resource_pool_id
         AND rpm.manager_user_id IN ([P.RPM_ID]))
and sp.staffing_profile_id in ([P.SP_ID])
AND EXISTS (SELECT 1 FROM rsc_staff_prof_managers spm
    WHERE spm.staffing_profile_id = positions.staffing_profile_id
         AND spm.manager_user_id IN ([P.SPM_ID]))
and sp.status_code in ([P.SPSTATUS])
and positions.status_code in ([P.POSITIONSTATUS])
and positions.need_by_date >=round(sysdate-[P.RES_PREVIOUS]*7-3, 'ww')
and positions.need_by_date <round(SYSDATE+[P.RES_FUTURE]*7+4, 'ww')
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
and positions.role_id in ([P.ROLE])
--End section from the filter fields
order by rp.resource_pool_name, sp.staffing_profile_nameSELECT rp.resource_pool_name RP, krsc_utils.get_pool_managers_list(rp.resource_pool_id) RPM, sp.staffing_profile_name SP, krsc_utils.get_sp_managers_list(sp.staffing_profile_id) SPM, users.full_name REQUESTED_BY, positions.position_name POSITION, krsc_utils.get_meaning('RSC - Staffing Profile Position Status', positions.status_code) POSITION_STATUS, positions.ask_date DATE_REQUESTED, positions.need_by_date RESPONSE_NEEDED_BY, krsc_utils.get_meaning('RSC - Staffing Profile Parent Containers', sp.container_entity_type_code) CONTAINER_TYPE, KRSC_Utils.Get_SP_Linked_Entity_Name(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_NAME, krsc_utils.get_meaning('RSC - Staffing Profile Status', sp.status_code) STAFFING_PROFILE_STATUS, krsc_utils.get_sp_linked_entity_status(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_STATUS, roles.role_name ROLE, krsc_utils.get_unmet_demand_start_date(positions.position_id) UNMET_DEMAND_START, krsc_utils.get_unmet_demand_finish_date(positions.position_id) UNMET_DEMAND_END, krsc_utils.get_unmet_demand_hours(positions.position_id) TOTAL_UNMET_HOURS, rp.resource_pool_id RP_LINK, sp.staffing_profile_id SP_LINK, positions.position_comment NOTES, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd') UNMET_RANGE_START, ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd') UNMET_RANGE_END, krsc_utils.get_range_unmet_demand_hours(positions.position_id, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd'), ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd')) UNMET_RANGE_HOURS, pm_utils.Find_Full_Names(positions.resources_requested) REQUESTED_RESOURCES
FROM rsc_resource_pools rp, rsc_staffing_profiles sp,  rsc_positions positions, knta_users users, rsc_roles roles
WHERE sp.staffing_profile_id=positions.staffing_profile_id
and sp.status_code not in (4,5,6)
and positions.resource_pool_id  =rp.resource_pool_id (+)
and users.user_id(+)=positions.requested_by
and roles.role_id=positions.role_id
and (krsc_utils.can_access_rsc_pool(rp.resource_pool_id, [SYS.USER_ID])='Y'
        or krsc_utils.can_access_staff_prof(sp.staffing_profile_id, [SYS.USER_ID])='Y')
--This section comes from the filter fields
and rp.resource_pool_id in ([P.RP_ID])
AND EXISTS (SELECT 1 FROM rsc_resource_pool_managers rpm
    WHERE rpm.resource_pool_id = positions.resource_pool_id
         AND rpm.manager_user_id IN ([P.RPM_ID]))
and sp.staffing_profile_id in ([P.SP_ID])
AND EXISTS (SELECT 1 FROM rsc_staff_prof_managers spm
    WHERE spm.staffing_profile_id = positions.staffing_profile_id
         AND spm.manager_user_id IN ([P.SPM_ID]))
and sp.status_code in ([P.SPSTATUS])
and positions.status_code in ([P.POSITIONSTATUS])
and positions.need_by_date >=round(sysdate-[P.RES_PREVIOUS]*7-3, 'ww')
and positions.need_by_date <round(SYSDATE+[P.RES_FUTURE]*7+4, 'ww')
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
and positions.role_id in ([P.ROLE])
--End section from the filter fields
order by rp.resource_pool_name, sp.staffing_profile_nameSELECT rp.resource_pool_name RP, krsc_utils.get_pool_managers_list(rp.resource_pool_id) RPM, sp.staffing_profile_name SP, krsc_utils.get_sp_managers_list(sp.staffing_profile_id) SPM, users.full_name REQUESTED_BY, positions.position_name POSITION, krsc_utils.get_meaning('RSC - Staffing Profile Position Status', positions.status_code) POSITION_STATUS, positions.ask_date DATE_REQUESTED, positions.need_by_date RESPONSE_NEEDED_BY, krsc_utils.get_meaning('RSC - Staffing Profile Parent Containers', sp.container_entity_type_code) CONTAINER_TYPE, KRSC_Utils.Get_SP_Linked_Entity_Name(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_NAME, krsc_utils.get_meaning('RSC - Staffing Profile Status', sp.status_code) STAFFING_PROFILE_STATUS, krsc_utils.get_sp_linked_entity_status(sp.container_entity_type_code, sp.container_entity_id) CONTAINER_STATUS, roles.role_name ROLE, krsc_utils.get_unmet_demand_start_date(positions.position_id) UNMET_DEMAND_START, krsc_utils.get_unmet_demand_finish_date(positions.position_id) UNMET_DEMAND_END, krsc_utils.get_unmet_demand_hours(positions.position_id) TOTAL_UNMET_HOURS, rp.resource_pool_id RP_LINK, sp.staffing_profile_id SP_LINK, positions.position_comment NOTES, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd') UNMET_RANGE_START, ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd') UNMET_RANGE_END, krsc_utils.get_range_unmet_demand_hours(positions.position_id, ROUND (SYSDATE - decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7 - 3, 'd'), ROUND (SYSDATE + decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7 + 4, 'd')) UNMET_RANGE_HOURS, pm_utils.Find_Full_Names(positions.resources_requested) REQUESTED_RESOURCES
FROM rsc_resource_pools rp, rsc_staffing_profiles sp,  rsc_positions positions, knta_users users, rsc_roles roles
WHERE sp.staffing_profile_id=positions.staffing_profile_id
and sp.status_code not in (4,5,6)
and positions.resource_pool_id  =rp.resource_pool_id (+)
and users.user_id(+)=positions.requested_by
and roles.role_id=positions.role_id
and (krsc_utils.can_access_rsc_pool(rp.resource_pool_id, [SYS.USER_ID])='Y'
        or krsc_utils.can_access_staff_prof(sp.staffing_profile_id, [SYS.USER_ID])='Y')
--This section comes from the filter fields
and rp.resource_pool_id in ([P.RP_ID])
AND EXISTS (SELECT 1 FROM rsc_resource_pool_managers rpm
    WHERE rpm.resource_pool_id = positions.resource_pool_id
         AND rpm.manager_user_id IN ([P.RPM_ID]))
and sp.staffing_profile_id in ([P.SP_ID])
AND EXISTS (SELECT 1 FROM rsc_staff_prof_managers spm
    WHERE spm.staffing_profile_id = positions.staffing_profile_id
         AND spm.manager_user_id IN ([P.SPM_ID]))
and sp.status_code in ([P.SPSTATUS])
and positions.status_code in ([P.POSITIONSTATUS])
and positions.need_by_date >=round(sysdate-[P.RES_PREVIOUS]*7-3, 'ww')
and positions.need_by_date <round(SYSDATE+[P.RES_FUTURE]*7+4, 'ww')
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
And ((krsc_utils.get_unmet_demand_start_date(positions.position_id) <round(sysdate+decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_FUTURE]', ' +-.0123456789', ' '))), null, '[P.UNMET_FUTURE]',0)*7+4, 'ww') and krsc_utils.get_unmet_demand_start_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww'))or (krsc_utils.get_unmet_demand_start_date(positions.position_id) < round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww') and krsc_utils.get_unmet_demand_finish_date(positions.position_id)>=round(sysdate-decode(LENGTH(TRIM(TRANSLATE('[P.UNMET_PREVIOUS]', ' +-.0123456789', ' '))), null, '[P.UNMET_PREVIOUS]',0)*7-3, 'ww')))
and positions.role_id in ([P.ROLE])
--End section from the filter fields
order by rp.resource_pool_name, sp.staffing_profile_name

**********************************************************************************

 
Thanks,
Mandy