Clarity

Expand all | Collapse all

Timesheet Query issue

Jump to Best Answer
  • 1.  Timesheet Query issue

    Posted 02-07-2017 12:57 AM

    Hi Guys, I was trying to modify a portlet where the data is recursive, i.e. i see data displaying timesheet data even when the timesheet is adjusted, which is different from the posted one. Given for a project, a resource is charged twice as query returns both posted and adjusted time record. So I tried to alter query and I've paste my query below:

     

    SELECT tp.prid period_int_id
    , tp.prfinish period_ending
    , srm.id res_int_id
    , srm.full_name res_name
    , st.name timesheet_status
    , inv.id proj_int_id
    , inv.odf_object_code task_type
    , inv.name || '/' || tsk.prname proj_task_name
    , rcc.name res_cost_center
    , res_obs.obs_unit_id res_obs_id
    , res_obs.level2_name res_function
    , rol.full_name res_role
    , dep_obs.level3_name res_region
    , dep_obs.level4_name res_country
    , srm.date_of_hire res_hire_date
    , mgr.full_name proj_manager
    , odfp.cost_center proj_cost_center
    , odfp.ge_region proj_region
    , odfp.ge_country proj_country
    , odfp.ge_company_code proj_company_code
    , odfp.ge_cps_number proj_cps_number
    , odfp.ge_exp_prj_code exp_proj_code
    , odfp.ge_local_sap_prj_num local_sap_proj_num
    , odfp.ge_wbs_element proj_wbs_element
    , odfp.ge_asiapac_int_order proj_asiapac_int_order
    , odfp.ge_se_dummy_code proj_se_dummy_code
    , odfp.ge_uk_dummy_code proj_uk_dummy_code
    , odfp.ge_status proj_ge_status
    , odfp.az_ge_task_type ge_task_type
    , proj_obs.obs_unit_id proj_obs_id
    , pacc.id res_class_id
    , pacr.resource_class res_class
    , ppav.numval1 labour_rate
    , SUM(te.practsum/3600) hours_billed
    , ppav.numval1*SUM(te.practsum/3600) proj_billing_charge
    FROM prtimeentry te
    INNER JOIN prtimesheet ts
    ON   CASE
        WHEN ts.prisadjustment=0
        AND ts.prid           = te.prtimesheetid
        THEN 1
        WHEN ts.prisadjustment=1
        AND ts.pradjustedid   =te.prtimesheetid
        THEN 1
      END=1
    INNER JOIN prtimeperiod tp
    ON tp.prid = ts.prtimeperiodid
    INNER JOIN srm_resources srm
    ON srm.id = ts.prresourceid
    INNER JOIN odf_ca_resource odfr
    ON odfr.id = srm.id
    INNER JOIN prj_resources res
    ON res.prid = srm.id
    INNER JOIN pac_mnt_resources pacr
    ON pacr.id = srm.id
    INNER JOIN srm_resources rol
    ON rol.id = res.prprimaryroleid
    INNER JOIN cmn_lookups_v st
    ON st.lookup_type    = 'TIMESHEET_STATUS'
    AND st.language_code = 'en'
    AND st.lookup_enum   = ts.prstatus
    INNER JOIN prassignment asn
    ON asn.prid = te.prassignmentid
    INNER JOIN prtask tsk
    ON tsk.prid = asn.prtaskid
    INNER JOIN inv_investments inv
    ON inv.id = tsk.prprojectid
    INNER JOIN odf_ca_project odfp
    ON odfp.id = inv.id
    INNER JOIN srm_resources mgr
    ON mgr.user_id = inv.manager_id
    LEFT OUTER JOIN pac_fos_resource_class pacc
    ON pacc.resource_class = pacr.resource_class
    LEFT OUTER JOIN ppa_matrixvalues ppav
    ON ppav.value2 = pacr.resource_class
    LEFT OUTER JOIN ppa_matrix ppa
    ON ppa.matrixkey = ppav.matrixkey
    AND ppa.description = 'Labor Rate Matrix'
    LEFT OUTER JOIN cmn_lookups_v rcc
    ON rcc.lookup_type = 'AZ_RES_COST_CENTER'
    AND rcc.language_code = 'en'
    AND rcc.lookup_enum = odfr.az_cost_center
    LEFT OUTER JOIN
    (
    SELECT obsa.record_id
    , obsu.obs_unit_id
    , obsu.level2_name
    FROM prj_obs_associations obsa
    INNER JOIN nbi_dim_obs obsu
    ON obsu.obs_unit_id = obsa.unit_id
    AND obsu.obs_type_name  = 'Delivery and Resource Management'
    WHERE obsa.table_name = 'SRM_RESOURCES'
    ) res_obs
    ON res_obs.record_id = srm.id
    LEFT OUTER JOIN
    (
    SELECT obsa.record_id
    , obsu.obs_unit_id
    , obsu.level3_name
    , obsu.level4_name
    FROM prj_obs_associations obsa
    INNER JOIN nbi_dim_obs obsu
    ON obsu.obs_unit_id = obsa.unit_id
    AND obsu.obs_type_name  = 'AstraZeneca Dept Organisation'
    WHERE obsa.table_name = 'SRM_RESOURCES'
    ) dep_obs
    ON dep_obs.record_id = srm.id
    LEFT OUTER JOIN
    (
    SELECT obsa.record_id
    , obsu.obs_unit_id
    FROM prj_obs_associations obsa
    INNER JOIN nbi_dim_obs obsu
    ON obsu.obs_unit_id = obsa.unit_id
    AND obsu.obs_type_name  = 'AstraZeneca Dept Organisation'
    WHERE obsa.table_name = 'SRM_PROJECTS'
    ) proj_obs
    ON proj_obs.record_id = inv.id
    GROUP BY tp.prid
    , tp.prfinish
    , srm.id
    , srm.full_name
    , st.name
    , inv.odf_object_code
    , inv.id
    , inv.name
    , tsk.prname
    , rcc.name
    , res_obs.obs_unit_id
    , res_obs.level2_name
    , rol.full_name
    , dep_obs.level3_name
    , dep_obs.level4_name
    , srm.date_of_hire
    , mgr.full_name
    , odfp.cost_center
    , odfp.ge_region
    , odfp.ge_country
    , odfp.ge_company_code
    , odfp.ge_cps_number
    , odfp.ge_exp_prj_code
    , odfp.ge_local_sap_prj_num
    , odfp.ge_wbs_element
    , odfp.ge_asiapac_int_order
    , odfp.ge_se_dummy_code
    , odfp.ge_uk_dummy_code
    , odfp.ge_status
    , odfp.az_ge_task_type
    , ppav.numval1
    , proj_obs.obs_unit_id
    , pacc.id
    , pacr.resource_class

     

    I tried to include a case statement in ON clause :

     

    FROM prtimeentry te
    INNER JOIN prtimesheet ts
    ON
      CASE
        WHEN ts.prisadjustment=0
        AND ts.prid           = te.prtimesheetid
        THEN 1
        WHEN ts.prisadjustment=1
        AND ts.pradjustedid   =te.prtimesheetid
        THEN 1
      END=1

     

    I didn't get any error when I try to run the query, but this keeps running and not loading any data.

     

    Can you please help me out of this issue?

     

    Monica



  • 2.  Re: Timesheet Query issue

    Posted 02-07-2017 10:50 AM

    Hi Monica,

     

     

    Your CASE statement is very expensive and seems to return everything so it will take very long time to run, this is why it's spinning.  Do you need only the last posted, and not the adjusted timesheets? In that case try filtering on PRSTATUS=4. When a timesheet was adjusted, the status will be 5 and PRISADJUSTMENT will be 0 and PRADJUSTEDID will be null. These are the ones you don't want if I understood correctly. To get only the last posted timesheets, including the adjustments but not the adjusted ones try something like that:

     

    SELECT *
    FROM prtimeentry te
    INNER JOIN prtimesheet ts
    on ts.prid = te.prtimesheetid
    where PRSTATUS=4

     

    This should filter out the adjusted timesheets and only include the Posted ones. Try it with one resource you know had an adjustment and you will be able to see if that's what you want or tweak it.

     

    Hope this helps -Nika



  • 3.  Re: Timesheet Query issue

    Posted 02-07-2017 11:57 PM

    Hi Monica,

     

    Try this in your from clause, it should get you expected results.

     

    FROM prtimeentry te
    JOIN prtimesheet ts ON
    CASE WHEN ts.prisadjustment=0 Then ts.prid
    WHEN ts.prisadjustment=1 then ts.pradjustedid
    END=te.prtimesheetid

     

    Cheers,

    Gaurav Komawar



  • 4.  Re: Timesheet Query issue

    Posted 02-08-2017 02:59 AM

    @gaurav, I tried with modified Case statement, yet this is not working. I still get posted and adjusted time sheet entry. @Nika It's not that I want only the adjusted time sheet, but I need to choose only the Adjusted time sheet entry where I have both Posted and Adjusted time sheet entry for the same task in a project.



  • 5.  Re: Timesheet Query issue

    Posted 02-08-2017 04:06 AM

    In that case use a virtual table to fetch only latest timeheet against a timeperiod for a user, then join it with your regular prtimesheet table.

    Something like this  (SELECT PT.PRRESOURCEID,
    PT.PRTIMEPERIODID,
    MIN(PT.PRSTATUS) MIN_STATUS,
    MAX(PT.PRMODTIME) LTST_MODTIME
    FROM clarityppm.PRTIMESHEET PT
    GROUP BY PTS2.PRRESOURCEID, PTS2.PRTIMEPERIODID) PTS

    LEFT OUTER JOIN PRTIMESHEET PTS3
    ON PTS.PRRESOURCEID = PTS3.PRRESOURCEID
    AND PTS.PRTIMEPERIODID = PTS3.PRTIMEPERIODID
    AND PTS.LTST_MODTIME = PTS3.PRMODTIME
    AND PTS.MIN_STATUS = PTS3.PRSTATUS



  • 6.  Re: Timesheet Query issue

    Posted 02-08-2017 10:13 AM

    Hi Monica,

     

    The statement I gave you above should do that, if you have two or more timesheets for the same resource and one is Adjusted and the other one is Adjustment (new, updated time), it will take the new updated time.

     

    Thanks -Nika



  • 7.  Re: Timesheet Query issue
    Best Answer

    Posted 02-08-2017 11:06 PM

    Hey guys, thanks for the response.

     

    The below query worked and fixed the issue I had:

     

    SELECT some stuff

    FROM some timesheet tables TS, TE, AS

    WHERE 

    AND NOT EXISTS ( SELECT 1 FROM prtimesheet TS1, prtimentry TE1, prassignment AS1

    WHERE AS1.prtaskid = AS.prtaskid                                 /* same task as the outer select*/

    AND AS1.PRRESOURCEID= AS.PRRESOURCEID      /* same resource as the outer select*/

    AND AS1.PRID = TE1.PRASSIGNMENTID                    

    AND TE1.PRTIMESHEETID = TS1.PRID                         

    AND TS1.PRTIMEPERIODID = TS.PRTIMEPERIODID   /* same time period as the outer select */

    AND TS1.PRRESOURCEID= TS.PRRESOURCEID         /* same resource as the outer select */

    AND TS1.PRID != TS.PRID                                                /* not the same timesheet as the outer select */

    AND TS1.PRSTATUS = 4 )     

     

    This way, when a resource is charged twice with status posted and adjusted, only one entry is taken into count. Thank you!

     

    Monica