AnsweredAssumed Answered

Allocation Portlet giving different result set for Resources filter and Department OBS filter

Question asked by sravani_nidamanuri on May 5, 2016
Latest reply on Jun 24, 2016 by Chris_Hackett

The 'Custom Weekly Allocation' portlet is returning different number of records by using Resource filter and Department OBS filter. For example when doing a filter for resource X it returns 5 records but when doing a filter with Department OBS the number records returned for resource X is only 2. But in actual we should get 5 Records for resource X. I have verified the status of the projects, allocation between the mentioned slice dates. But could get any clue.

 

Please help me understand the cause for this variation in result set.

 

 

Screenshot for Resource Filter:

 

 

Screenshot for Department OBS Fiter

 

 

Here is the NSQL

 

SELECT @SELECT:DIM:USER_DEF:IMPLIED:TEAM:TM.PRID:ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:SRMR.ID:RESOURCE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:SRMR.UNIQUE_NAME:RESOURCE_CODE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:SRMR.FULL_NAME:RESOURCE_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:RMGR.ID:RESOURCE_MANAGER_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:RMGR.FULL_NAME:RESOURCE_MANAGER@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:ROL.ID:PRIMARY_ROLE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:ROL.FULL_NAME:PRIMARY_ROLE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:SRMR.PERSON_TYPE:EMPLOYMENT_TYPE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:ET.NAME:EMPLOYMENT_TYPE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:E.ID:EXECUTIVE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:E.FULL_NAME:EXECUTIVE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:ODFR.OPT_PRIMARY_APP:PRIMARY_APP_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:APP.NAME:PRIMARY_APP@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:ODFR.OPT_ONSHORE_OFFSHORE:ONSHORE_OFFSHORE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:OO.NAME:ONSHORE_OFFSHORE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:ODFR.OPT_BUILD_LOCATION:BUILD_LOCATION@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:INVI.ID:PROJECT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:INVI.CODE:PROJECT_CODE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:INVI.NAME:PROJECT_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:INVI.STATUS:PROJECT_STATUS_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:ST.NAME:PROJECT_STATUS@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:ODFP.OPT_SUB_TYPE:PROJECT_SUB_TYPE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:SUB.NAME:PROJECT_SUB_TYPE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:TM.PRID:TEAM_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:TROL.ID:TEAM_ROLE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:TROL.FULL_NAME:TEAM_ROLE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:TM.PRBOOKING:BOOKING_STATUS_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TEAM:BS.NAME:BOOKING_STATUS@
, @SELECT:DIM:USER_DEF:IMPLIED:TIMEPERIOD:AL.SLICE_DATE:SLICE_DATE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEPERIOD:TO_CHAR(AL.SLICE_DATE, 'MM/DD/YYYY'):SLICE_DATE@
, @SELECT:METRIC:USER_DEF:IMPLIED:SUM(AL.SLICE):HOURS@

FROM SRM_RESOURCES SRMR
JOIN PRJ_RESOURCES PRJR ON SRMR.ID = PRJR.PRID AND PRJR.PRISROLE = 0
JOIN ODF_CA_RESOURCE ODFR ON SRMR.ID = ODFR.ID
LEFT JOIN SRM_RESOURCES RMGR ON SRMR.MANAGER_ID = RMGR.USER_ID
LEFT JOIN SRM_RESOURCES ROL ON PRJR.PRPRIMARYROLEID = ROL.ID
LEFT JOIN CMN_LOOKUPS_V ET ON SRMR.PERSON_TYPE = ET.ID AND ET.LOOKUP_TYPE = 'SRM_RESOURCE_TYPE' AND ET.LANGUAGE_CODE = 'en'
LEFT JOIN SRM_RESOURCES E ON ODFR.OPT_EXECUTIVE2 = E.USER_ID
LEFT JOIN INV_INVESTMENTS APP ON ODFR.OPT_PRIMARY_APP = APP.ID AND APP.ODF_OBJECT_CODE = 'application'
LEFT JOIN CMN_LOOKUPS_V OO ON ODFR.OPT_ONSHORE_OFFSHORE = OO.LOOKUP_CODE AND OO.LOOKUP_TYPE = 'OPT_ONSHORE_OFFSHORE' AND OO.LANGUAGE_CODE = 'en'
JOIN PRTEAM TM ON SRMR.ID = TM.PRRESOURCEID
LEFT JOIN CMN_LOOKUPS_V BS ON TM.PRBOOKING = BS.LOOKUP_ENUM AND BS.LOOKUP_TYPE = 'BOOKING_STATUS_LIST' AND BS.LANGUAGE_CODE = 'en'
LEFT JOIN SRM_RESOURCES TROL ON TM.PRROLEID = TROL.ID
JOIN INV_INVESTMENTS INVI ON TM.PRPROJECTID = INVI.ID
LEFT JOIN ODF_CA_PROJECT ODFP ON INVI.ID = ODFP.ID
LEFT JOIN CMN_LOOKUPS_V ST ON INVI.STATUS = ST.LOOKUP_ENUM AND ST.LOOKUP_TYPE = 'INVESTMENT_OBJ_STATUS' AND ST.LANGUAGE_CODE = 'en'
LEFT JOIN ODF_CA_OPT_PROJECT_SUBTYPE SUB ON ODFP.OPT_SUB_TYPE = SUB.CODE
JOIN PRJ_BLB_SLICES AL ON TM.PRID = AL.PRJ_OBJECT_ID AND AL.SLICE_REQUEST_ID = (SELECT SR.ID FROM PRJ_BLB_SLICEREQUESTS SR WHERE SR.REQUEST_NAME = @WHERE:PARAM:USER_DEF:STRING:SLICE_BY_STRING@ || 'RESOURCEALLOCCURVE')

WHERE SRMR.IS_ACTIVE = 1
AND AL.SLICE_DATE BETWEEN @WHERE:PARAM:USER_DEF:DATE:START_DATE@ AND @WHERE:PARAM:USER_DEF:DATE:FINISH_DATE@
AND (@WHERE:PARAM:USER_DEF:INTEGER:INCLUDE_ZERO_ALLOC@ = 1 OR (@WHERE:PARAM:USER_DEF:INTEGER:INCLUDE_ZERO_ALLOC@ = 0 AND TM.PRALLOCSUM>0 ))
AND (@WHERE:PARAM:USER_DEF:INTEGER:INCLUDE_ZERO_ALLOC@ = 1 OR (NVL(@WHERE:PARAM:USER_DEF:INTEGER:INCLUDE_ZERO_ALLOC@, 0) = 0 ))
AND (@WHERE:PARAM:USER_DEF:INTEGER:ROBS_ID@ IS NULL OR SRMR.ID IN (SELECT OBSA.RECORD_ID FROM OBS_UNITS_FLAT_BY_MODE OBSM JOIN PRJ_OBS_ASSOCIATIONS OBSA ON OBSM.LINKED_UNIT_ID = OBSA.UNIT_ID AND OBSA.TABLE_NAME = 'SRM_RESOURCES' WHERE OBSM.UNIT_ID = @WHERE:PARAM:USER_DEF:INTEGER:ROBS_ID@ AND OBSM.UNIT_MODE = NVL(@WHERE:PARAM:USER_DEF:STRING:ROBS_MODE@, 'OBS_UNIT_AND_CHILDREN') ))
AND (@WHERE:PARAM:USER_DEF:INTEGER:POBS_ID@ IS NULL OR INVI.ODF_OBJECT_CODE IN (SELECT O.CODE FROM OBS_UNITS_FLAT_BY_MODE OBSM JOIN PRJ_OBS_ASSOCIATIONS OBSA ON OBSM.LINKED_UNIT_ID = OBSA.UNIT_ID JOIN ODF_OBJECTS O ON OBSA.TABLE_NAME = O.OBS_CODE WHERE OBSM.UNIT_ID = @WHERE:PARAM:USER_DEF:INTEGER:POBS_ID@ AND OBSM.UNIT_MODE = NVL(@WHERE:PARAM:USER_DEF:STRING:POBS_MODE@, 'OBS_UNIT_AND_CHILDREN') AND OBSA.RECORD_ID = INVI.ID ))
AND CASE WHEN INVI.STATUS = 1 OR INVI.STATUS= 5  THEN 1 ELSE 0 END = 1
AND TM.PRALLOCSUM IS NOT NULL
AND INVI.IS_ACTIVE       = 1
AND @FILTER@
AND ROWNUM < (select opt_query_limit from odf_ca_opt_system_settings)
GROUP BY SRMR.ID
, SRMR.UNIQUE_NAME
, SRMR.FULL_NAME
, RMGR.ID
, RMGR.FULL_NAME
, ROL.ID
, ROL.FULL_NAME
, SRMR.PERSON_TYPE
, ET.NAME
, E.ID
, E.FULL_NAME
, ODFR.OPT_PRIMARY_APP
, APP.NAME
, ODFR.OPT_ONSHORE_OFFSHORE
, ODFR.OPT_BUILD_LOCATION
, ODFR.OPT_DIVISION
, ODFR.OPT_BUILD_LOCATION
, ODFR.OPT_ONSHORE_OFFSHORE
, OO.NAME
, ODFR.OPT_EXECUTIVE2
, ODFR.OPT_PRIMARY_APP
, INVI.ID
, INVI.CODE
, INVI.NAME
, INVI.STATUS
, ST.NAME
, ODFP.OPT_SUB_TYPE
, SUB.NAME
, TM.PRID
, TROL.ID
, TROL.FULL_NAME
, TM.PRBOOKING
, BS.NAME
, AL.SLICE_DATE

HAVING @HAVING_FILTER@
ORDER BY slice_date_id,resource_name,project_name

Outcomes