Clarity PPM

Expand all | Collapse all

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

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

    Posted 05-05-2016 10:25 AM

    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



  • 2.  Re: Allocation Portlet giving different result set for Resources filter and Department OBS filter

    Posted 06-03-2016 06:28 PM

    Did you put the OBS Unit in the 'Resource OBS' or the 'Investment OBS' filter field?



  • 3.  Re: Allocation Portlet giving different result set for Resources filter and Department OBS filter

    Posted 06-06-2016 03:29 AM

    Hi Kathryn,

     

    I filter for OBS unit in 'Resource OBS' filter field.

     

    Thanks

    Sravani



  • 4.  Re: Allocation Portlet giving different result set for Resources filter and Department OBS filter

    Posted 06-09-2016 12:07 PM

    I've encountered instances where the end user renamed the requirement to "Doe, John" instead of actually staffing "Doe, John" as a resource to that investment role.  We'd see that in the result set when filtering by OBS (and it appeared to be an actual person), but we wouldn't see that when filtering for the resource.



  • 5.  Re: Allocation Portlet giving different result set for Resources filter and Department OBS filter

    Posted 06-09-2016 03:10 PM

    I have seen the same and it confused me until I figured out what had happened.



  • 6.  Re: Allocation Portlet giving different result set for Resources filter and Department OBS filter

    Posted 06-14-2016 06:37 AM

    Thank You Jason and Michael for your inputs but here my scenario is different.

    eg: Resource A Returns 16 records by filtering with resource and 12 records while filtering with Department OBS.

    Always the Departments OBS filter returns lesser number of records compared to resource filter.

    The missing investments with Department OBS will appear again when I change the input dates in the portlet filter.

     

    What might be the reason for this behavior? And the rocords count keeps on changing periodically in lower environment as well. Is this happening because of any updates by job? If yes which job can make it?



  • 7.  Re: Allocation Portlet giving different result set for Resources filter and Department OBS filter

    Posted 06-14-2016 07:25 AM

    The missing investments with Department OBS will appear again when I change the input dates in the portlet filter.

    - Based on your query, I think this is expected because your dates drives which team members will be picked up, which then drives which investments will be picked up.

     

    Always the Departments OBS filter returns lesser number of records compared to resource filter.

    - Are their resources with missing departments? You can check the delta resources (resources which do not appear when you use department OBS) and compare that with the other resource's data.

     

    Records count keeps on changing periodically in lower environment as well. Is this happening because of any updates by job?

    - The query result has dependency with the allocation slice window. That's one dependency that I can see.

    One more scenario is if it is a new allocation, looking at your query it looks like the allocation will not be able to pick up the record until the record is sliced.

     

    -Sankhadeep



  • 8.  Re: Allocation Portlet giving different result set for Resources filter and Department OBS filter

    Posted 06-17-2016 08:19 AM

    Thank You every one for you inputs.

     

    I just took a trace of portlet and ran the query in Toad.

    For a resource Named Agarwal  all the records are returned but for other resources named Paramkusham no records are found.

    By commenting the line ROWNUM < (select opt_query_limit from odf_ca_opt_system_settings) I could get all the rows for paramkusham as well.

     

    I this something related to Row Limit or sorting order.

    Below line is added additionally in trace query

    select * from (select row_number() over ( order by slice_date_id asc, resource_name  asc, project_name  asc) row_num, count(*) over () num_rows.

    Is this something clarity is adding during executing the portlet?

    I couldn't understand if the issue is due to query or limitation from clarity side?

     

    Please share your inputs.

     

    Here is the query from trace

     

    select * from (select row_number() over ( order by slice_date_id asc, resource_name  asc, project_name  asc) row_num, count(*) over () num_rows, q.* from ( SELECT TM.PRID ID
    , SRMR.ID RESOURCE_ID
    , SRMR.UNIQUE_NAME RESOURCE_CODE
    , SRMR.FULL_NAME RESOURCE_NAME
    , RMGR.ID RESOURCE_MANAGER_ID
    , RMGR.FULL_NAME RESOURCE_MANAGER
    , ROL.ID PRIMARY_ROLE_ID
    , ROL.FULL_NAME PRIMARY_ROLE
    , SRMR.PERSON_TYPE EMPLOYMENT_TYPE_ID
    , ET.NAME EMPLOYMENT_TYPE
    , E.ID EXECUTIVE_ID
    , E.FULL_NAME EXECUTIVE
    , ODFR.OPT_PRIMARY_APP PRIMARY_APP_ID
    , APP.NAME PRIMARY_APP
    , ODFR.OPT_ONSHORE_OFFSHORE ONSHORE_OFFSHORE_ID
    , OO.NAME ONSHORE_OFFSHORE
    , ODFR.OPT_BUILD_LOCATION BUILD_LOCATION
    , INVI.ID PROJECT_ID
    , INVI.CODE PROJECT_CODE
    , INVI.NAME PROJECT_NAME
    , INVI.STATUS PROJECT_STATUS_ID
    , ST.NAME PROJECT_STATUS
    , ODFP.OPT_SUB_TYPE PROJECT_SUB_TYPE_ID
    , SUB.NAME PROJECT_SUB_TYPE
    , TM.PRID TEAM_ID
    , TROL.ID TEAM_ROLE_ID
    , TROL.FULL_NAME TEAM_ROLE
    , TM.PRBOOKING BOOKING_STATUS_ID
    , BS.NAME BOOKING_STATUS
    , AL.SLICE_DATE SLICE_DATE_ID
    , TO_CHAR(AL.SLICE_DATE, 'MM/DD/YYYY') SLICE_DATE
    , 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 = 'MONTHLY' || 'RESOURCEALLOCCURVE')

    WHERE SRMR.IS_ACTIVE = 1
    AND AL.SLICE_DATE BETWEEN TO_DATE('2016-01-01', 'YYYY-MM-DD') AND TO_DATE('2016-12-31', 'YYYY-MM-DD')
    AND (0 = 1 OR (0 = 0 AND TM.PRALLOCSUM>0 ))
    AND (0 = 1 OR (NVL(0, 0) = 0 ))
    AND (5250272 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 = 5250272 AND OBSM.UNIT_MODE = NVL('OBS_UNIT_AND_CHILDREN', 'OBS_UNIT_AND_CHILDREN') ))
    AND (NULL 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 = NULL AND OBSM.UNIT_MODE = NVL('OBS_UNIT_AND_CHILDREN', '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 1=1 and 1=1 and 2 = 2  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 1=1  ORDER BY slice_date_id,resource_name,project_name ) q) q where q.row_num < 50001 order by q.row_num

     

     

     

     

     



  • 9.  Re: Allocation Portlet giving different result set for Resources filter and Department OBS filter

    Posted 06-24-2016 03:45 PM

    Anymore input for sravani_nidamanuri ?