Clarity

Expand all | Collapse all

Resource weekly ETC query

  • 1.  Resource weekly ETC query

    Posted Sep 20, 2013 07:07 AM
    Hi All,

    I need your urgent help on resource weekly etc query. I have created a query which is giving me correct result for one project but for other project data is not displaying properly. I have tried everything but couldn't get it working properly. We have one requirement where project managers need to see etc hours in MSP should be same to the query result. I tried to resliced time slices, ran time slice job, ran datamart job for resource portlet.

    I also used weekly, daily and system slices, searched everywhere in the community but no success so far.

    Hope someone help me..

    Regards,


  • 2.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 07:43 AM
    Perhaps you should post your query. :wacko:


  • 3.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 08:16 AM
    Hi Dave, Thanks for your reply, here is my query ---

    select
    inv.code,inv.name ,
    prj.slice_date,TRUNC(prj.slice_date, 'IW') WeekStartDay, -- Week Start Date
    nvl(round(prj.slice,2),0) sday,
    prj.slice_request_id ,
    pt.prname,
    prt.prresourceid,
    srm1.last_name ||','|| srm1.first_name,
    srm2.last_name
    from
    prassignment pra,
    prtask pt, inv_investments inv,
    prj_blb_slices prj,
    prteam prt, srm_resources srm1,
    srm_resources srm2
    where
    pra.prtaskid = pt.prid
    and pt.prprojectid = inv.id
    and prj.prj_object_id = pra.prid
    and prt.prprojectid = inv.id
    and prt.prresourceid = pra.prresourceid
    and srm1.id = prt.prresourceid
    and srm2.id = prt.prroleid(+)
    and pt.prprojectid in (5148508,5419158)
    and prj.slice_date <=to_date('12/31/2013','MM/DD/YYYY')
    and prj.slice_request_id = 3 -- DAILYRESOURCEESTCURVE
    --group by inv.code,inv.name,srm.full_name,prj.slice_date,prj.slice_request_id ,pt.prname
    order by prj.slice_date


  • 4.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 08:54 AM
    Well your query does not initially look wrong - is there actually any data in the slice table for the assignments over the time-period that you are looking for?


  • 5.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 09:21 AM
    Yes, as i said earlier, for one project this query is working fine. one quick question though, i need to see weekly est curve data (DAILYRESOURCEESTCURVE) for this year i.e 01/01/2013 to 31/12/2013, what value should i put in start date of slice and number of periods ? any suggestion ??


  • 6.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 09:33 AM
    ...is there actually any data in the slice table for the assignments over the time-period that you are looking for FOR THE PROJECT THAT YOUR QUERY IS NOT WORKING FOR?


  • 7.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 09:37 AM
    Yes, there is data but the data is not matching with ETC value. i need to see weekly est curve data (DAILYRESOURCEESTCURVE) for this year i.e 01/01/2013 to 31/12/2013, what value should i put in start date of slice and number of periods ? any suggestion ??


  • 8.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 09:45 AM
    Well you just need to apply the same date ranges to the slice tables...

    so instead of this;

    and prj.slice_date <=to_date('12/31/2013','MM/DD/YYYY')

    this

    and prj.slice_date between to_date('01/01/2013','MM/DD/YYYY') and to_date('12/31/2013','MM/DD/YYYY')

    --

    But you should also check the setup of your DAILYRESOURCEESTCURVE slice to ensure that it covers that whole period (I suspect it will not!) - you might have better luck with WEEKLY slices (WEEKLYRESOURCEESTCURVE, WEEKLY_INVESTMENT_EAC)

    Also worth noting that if you are posting actuals then ETCs become 0 (no data in the slice) for dates before the last posted actuals for a resource; so you would have to pick up an ACTUALS slice as well (to get the EAC rather than ETC).


  • 9.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 09:52 AM
    thats right Dave, we are not using timesheets so ETC can not be zero untill unless we make it zero using MSP, i have also setup DAILYRESOURCEESTCURVE start date 01/06/2012 and number of periods are 700 so i assume it should cover date range .. but somehow its not :(


  • 10.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 10:04 AM
    '01/06/2012' + 700 = '12/06/2013' which is not > '12/31/2013'

    (assuming 'mm/dd/yyyy' of course, but that is what you are using earlier)

    --

    I am still unclear though; I am not sure whether you are saying ;

    1.your "query is wrong" - the data in the slices is present and correct just not pulled out correctly in your query
    2.your slice data is missing - there is missing data in the DAILY slices
    3.your slice data is wrong - you have looked at the slice data (outside of your query, just direct on the database) and it it is incorrect

    I was suggesting things to help you diagnose (1) or (2), but you now seem to be implying (3)

    (3) is hard to diagnose ; you need to pick a small sample (one week say) where you think the data is wrong, then example that in detail in the slice data compared to what MSP is telling you to try to work out whether the data is really incorrect or whether it is just your understanding of the data (e.g. ETC vs EAC) which is incorrect. Hopefully the latter and then you can just go back and fix your original query.


  • 11.  RE: Resource weekly ETC query

    Posted Sep 20, 2013 10:09 AM
    thanks Dave, I'll more work on this, will update soon .. much appreciated ..


  • 12.  RE: Resource weekly ETC query

    Posted Sep 23, 2013 08:43 AM
      |   view attached
    Hi Svoda,

    One thing you might want to check is running a variance query between your '5 - MONTHLYRESOURCEESTCURVE' and the '3 - DAILYRESOURCEESTCURVE' for just one month, at the project level. I have attached a simple pl/sql minus query that compares the two slice tables, perhaps missing slice data is your issue. The other piece of it may be needing to view the results in a more developed query, viewing the slice ETCs from the start of the week thru the end, perhaps ETCs are not being rolled up correctly the way you think they should be? If you would like to see a generic weekly slice query layout I use, just let me know, I can send one up to you.

    Hope this helps

    Jonathan P. Thompson

    Attachment(s)