Clarity

Expand all | Collapse all

Missing Timesheet Query - Resource Manager not updating

  • 1.  Missing Timesheet Query - Resource Manager not updating

    Posted 02-10-2015 09:57 AM

    Hi All,

    We have a missing timesheet portlet where the provider is a query.  I'm not sure why but when we update the resource manager on the Resources Portlet  that change which should take affect immediately is not updating within this Portlet.  I recopied the query and created a new query and new portlet to see if there was any corruption at that level and that was not the case.  Does anyone have any thoughts as to what could be causing the disconnect?

     


    SELECT
    ROW_NUMBER() OVER(ORDER BY SRM_RESOURCES.ID) UniqueID,
    SRM_RESOURCES.ID srmresources_id,
    SRM_RESOURCES.FULL_NAME srmresources_fullname,
    SRM_RESOURCES.DATE_OF_HIRE srmresources_dateofhire,
    SRM_RESOURCES.DATE_OF_TERMINATION srmresources_dateoftermination,
    PRTIMEPERIOD.PRSTART period_startdate,
    PRTIMEPERIOD.PRFINISH-1 period_finishdate,
    (SELECT NBI_DIM_OBS.OBS_TYPE_NAME
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') OBS_TYPE_NAME,

    (SELECT NBI_DIM_OBS.Level2_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') Level2_Name,
    (SELECT NBI_DIM_OBS.Level3_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') Level3_Name,
    (SELECT NBI_DIM_OBS.Level4_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') Level4_Name,
    (SELECT NBI_DIM_OBS.Level5_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') Level5_Name,
    (SELECT NBI_RESOURCE_CURRENT_FACTS.MANAGER_LAST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID)+', '+(SELECT   NBI_RESOURCE_CURRENT_FACTS.MANAGER_FIRST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID) Manager_Name
    FROM  SRM_RESOURCES WITH (NOLOCK), PRTIMEPERIOD WITH (NOLOCK), PRJ_RESOURCES WITH (NOLOCK)

    WHERE PRTIMEPERIOD.PRSTART BETWEEN @param_pr_start AND @param_pr_finish
    AND SRM_RESOURCES.ID = PRJ_RESOURCES.PRID
    AND SRM_RESOURCES.IS_ACTIVE='1' AND SRM_RESOURCES.PERSON_TYPE!='0'
    AND PRJ_RESOURCES.PRISOPEN = '1'
    AND (PRTIMEPERIOD.PRSTART >= SRM_RESOURCES.DATE_OF_HIRE OR PRTIMEPERIOD.PRFINISH >= SRM_RESOURCES.DATE_OF_HIRE OR SRM_RESOURCES.DATE_OF_HIRE is NULL)
    AND (PRTIMEPERIOD.PRSTART < SRM_RESOURCES.DATE_OF_TERMINATION OR SRM_RESOURCES.DATE_OF_TERMINATION is NULL)
    AND (@param_obs is null
    OR EXISTS ( SELECT 1 FROM prj_obs_associations ASSOC, prj_obs_units_flat FLAT
    WHERE srm_resources.id = ASSOC.record_id
    AND ASSOC.table_name = 'SRM_RESOURCES' AND ASSOC.unit_id = FLAT.unit_id AND FLAT.branch_unit_id = @param_obs ))
    AND SRM_RESOURCES.PERSON_TYPE = SRM_RESOURCES.PERSON_TYPE
                AND (
                (     SELECT  COUNT(*)
                      FROM   PRTIMESHEET
                      WHERE PRTIMESHEET.PRTIMEPERIODID = PRTIMEPERIOD.PRID
                      AND PRTIMESHEET.PRRESOURCEID = SRM_RESOURCES.ID) = 0
                OR
                (     SELECT  COUNT(*)
                FROM   PRTIMESHEET
                WHERE PRTIMESHEET.PRTIMEPERIODID = PRTIMEPERIOD.PRID
                    AND PRTIMESHEET.PRRESOURCEID = SRM_RESOURCES.ID
                    AND       PRTIMESHEET.PRSTATUS = 0) > 0
                )
    AND SRM_RESOURCES.ID in (select object_instance_id from odfsec_resource_v2 where user_id = 1)
    AND 1=1
    AND ROW_NUMBER() OVER(ORDER BY SRM_RESOURCES.ID) IN (NULL)
    AND ROW_NUMBER() OVER(ORDER BY SRM_RESOURCES.ID) = @uniqueid
    AND ROW_NUMBER() OVER(ORDER BY SRM_RESOURCES.ID) >= @uniqueid_from
    AND ROW_NUMBER() OVER(ORDER BY SRM_RESOURCES.ID) <= @uniqueid_to
    AND SRM_RESOURCES.ID IN (NULL)
    AND SRM_RESOURCES.ID = @srmresources_id
    AND SRM_RESOURCES.ID >= @srmresources_id_from
    AND SRM_RESOURCES.ID <= @srmresources_id_to
    AND UPPER(SRM_RESOURCES.FULL_NAME) LIKE UPPER(@srmresources_fullname_wildcard) ESCAPE '\'
    AND SRM_RESOURCES.FULL_NAME IN (NULL)
    AND SRM_RESOURCES.FULL_NAME = @srmresources_fullname
    AND SRM_RESOURCES.FULL_NAME >= @srmresources_fullname_from
    AND SRM_RESOURCES.FULL_NAME <= @srmresources_fullname_to
    AND SRM_RESOURCES.DATE_OF_HIRE IN (NULL)
    AND SRM_RESOURCES.DATE_OF_HIRE = @srmresources_dateofhire
    AND SRM_RESOURCES.DATE_OF_HIRE >= @srmresources_dateofhire_from
    AND SRM_RESOURCES.DATE_OF_HIRE <= @srmresources_dateofhire_to
    AND SRM_RESOURCES.DATE_OF_TERMINATION IN (NULL)
    AND SRM_RESOURCES.DATE_OF_TERMINATION = @srmresources_dateoftermination
    AND SRM_RESOURCES.DATE_OF_TERMINATION >= @srmresources_dateoftermination_from
    AND SRM_RESOURCES.DATE_OF_TERMINATION <= @srmresources_dateoftermination_to
    AND PRTIMEPERIOD.PRSTART IN (NULL)
    AND PRTIMEPERIOD.PRSTART = @period_startdate
    AND PRTIMEPERIOD.PRSTART >= @period_startdate_from
    AND PRTIMEPERIOD.PRSTART <= @period_startdate_to
    AND PRTIMEPERIOD.PRFINISH-1 IN (NULL)
    AND PRTIMEPERIOD.PRFINISH-1 = @period_finishdate
    AND PRTIMEPERIOD.PRFINISH-1 >= @period_finishdate_from
    AND PRTIMEPERIOD.PRFINISH-1 <= @period_finishdate_to
    AND UPPER((SELECT NBI_DIM_OBS.OBS_TYPE_NAME
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization')) LIKE UPPER(@obs_type_name_wildcard) ESCAPE '\'
    AND (SELECT NBI_DIM_OBS.OBS_TYPE_NAME
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') IN (NULL)
    AND (SELECT NBI_DIM_OBS.OBS_TYPE_NAME
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') = @obs_type_name
    AND (SELECT NBI_DIM_OBS.OBS_TYPE_NAME
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') >= @obs_type_name_from
    AND (SELECT NBI_DIM_OBS.OBS_TYPE_NAME
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') <= @obs_type_name_to
    AND UPPER((SELECT NBI_DIM_OBS.Level2_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization')) LIKE UPPER(@level2_name_wildcard) ESCAPE '\'
    AND (SELECT NBI_DIM_OBS.Level2_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') IN (NULL)
    AND (SELECT NBI_DIM_OBS.Level2_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') = @level2_name
    AND (SELECT NBI_DIM_OBS.Level2_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') >= @level2_name_from
    AND (SELECT NBI_DIM_OBS.Level2_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') <= @level2_name_to
    AND UPPER((SELECT NBI_DIM_OBS.Level3_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization')) LIKE UPPER(@level3_name_wildcard) ESCAPE '\'
    AND (SELECT NBI_DIM_OBS.Level3_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') IN (NULL)
    AND (SELECT NBI_DIM_OBS.Level3_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') = @level3_name
    AND (SELECT NBI_DIM_OBS.Level3_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') >= @level3_name_from
    AND (SELECT NBI_DIM_OBS.Level3_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') <= @level3_name_to
    AND UPPER((SELECT NBI_DIM_OBS.Level4_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization')) LIKE UPPER(@level4_name_wildcard) ESCAPE '\'
    AND (SELECT NBI_DIM_OBS.Level4_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') IN (NULL)
    AND (SELECT NBI_DIM_OBS.Level4_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') = @level4_name
    AND (SELECT NBI_DIM_OBS.Level4_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') >= @level4_name_from
    AND (SELECT NBI_DIM_OBS.Level4_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') <= @level4_name_to
    AND UPPER((SELECT NBI_DIM_OBS.Level5_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization')) LIKE UPPER(@level5_name_wildcard) ESCAPE '\'
    AND (SELECT NBI_DIM_OBS.Level5_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') IN (NULL)
    AND (SELECT NBI_DIM_OBS.Level5_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') = @level5_name
    AND (SELECT NBI_DIM_OBS.Level5_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') >= @level5_name_from
    AND (SELECT NBI_DIM_OBS.Level5_Name
                      FROM  PRJ_OBS_ASSOCIATIONS WITH (NOLOCK)
                                  JOIN NBI_DIM_OBS WITH (NOLOCK) ON PRJ_OBS_ASSOCIATIONS.UNIT_ID = NBI_DIM_OBS.OBS_UNIT_ID
                      WHERE PRJ_OBS_ASSOCIATIONS.RECORD_ID = SRM_RESOURCES.ID
                                  AND NBI_DIM_OBS.OBS_TYPE_NAME = 'Resource Organization') <= @level5_name_to
    AND UPPER((SELECT NBI_RESOURCE_CURRENT_FACTS.MANAGER_LAST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID)+', '+(SELECT   NBI_RESOURCE_CURRENT_FACTS.MANAGER_FIRST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID)) LIKE UPPER(@manager_name_wildcard) ESCAPE '\'
    AND (SELECT NBI_RESOURCE_CURRENT_FACTS.MANAGER_LAST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID)+', '+(SELECT   NBI_RESOURCE_CURRENT_FACTS.MANAGER_FIRST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID) IN (NULL)
    AND (SELECT NBI_RESOURCE_CURRENT_FACTS.MANAGER_LAST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID)+', '+(SELECT   NBI_RESOURCE_CURRENT_FACTS.MANAGER_FIRST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID) = @manager_name
    AND (SELECT NBI_RESOURCE_CURRENT_FACTS.MANAGER_LAST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID)+', '+(SELECT   NBI_RESOURCE_CURRENT_FACTS.MANAGER_FIRST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID) >= @manager_name_from
    AND (SELECT NBI_RESOURCE_CURRENT_FACTS.MANAGER_LAST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID)+', '+(SELECT   NBI_RESOURCE_CURRENT_FACTS.MANAGER_FIRST_NAME
                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)
                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID) <= @manager_name_to
    AND SRM_RESOURCES.PERSON_TYPE IN (NULL)
    AND SRM_RESOURCES.PERSON_TYPE = @param_resource_type
    AND SRM_RESOURCES.PERSON_TYPE >= @param_resource_type_from
    AND SRM_RESOURCES.PERSON_TYPE <= @param_resource_type_to
    GROUP BY SRM_RESOURCES.ID, SRM_RESOURCES.FULL_NAME, SRM_RESOURCES.DATE_OF_HIRE,
    SRM_RESOURCES.DATE_OF_TERMINATION, PRTIMEPERIOD.PRSTART, PRTIMEPERIOD.PRFINISH
    HAVING   1=1



  • 2.  Re: Missing Timesheet Query - Resource Manager not updating

    Posted 02-10-2015 10:25 AM

    Why are you using this ?

     

    (SELECT NBI_RESOURCE_CURRENT_FACTS.MANAGER_LAST_NAME

                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)

                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID)+', '+(SELECT   NBI_RESOURCE_CURRENT_FACTS.MANAGER_FIRST_NAME

                      FROM  NBI_RESOURCE_CURRENT_FACTS WITH (NOLOCK)

                      WHERE NBI_RESOURCE_CURRENT_FACTS.RESOURCE_ID = SRM_RESOURCES.ID) Manager_Name

     

     

    Can you check by replacing the above with the below ? (convert the NSQL to SQL and tweak it to use only srm_resources and cmn_sec_users) ?

     

    https://communities.ca.com/message/98597777#98597777

     

    NJ



  • 3.  Re: Missing Timesheet Query - Resource Manager not updating

    Posted 02-10-2015 11:43 AM

    As NJ has hinted, you are pulling the details of your resource data from the DATAMART tables and so it will only be as "fresh" as your last datamart extraction - you can get all your required data easily from the live tables.