AnsweredAssumed Answered

Help with Query

Question asked by troth101 on Dec 28, 2015
Latest reply on Jan 6, 2016 by troth101

I am trying to figure out a way to pull current ETC and Baseline Usage(current baseline) by Assignment. The desired output being a list that shows project name & ID, Project Role, Employment Type, current ETC, and others. I've hit a bit of a wall, which may just be because of staring at it too long, but when I try to pull the ETC it always gives me the baseline ETC. Is there any simplified way to get the current ETC on assignment?


Here is my current query:


SELECT || '-' || b.base_id || '-' || || '-' || r.hr_id || '-' || r.person_type:dimid@,













         @SELECT:METRIC:USER_DEF:IMPLIED:NVL(SUM(b.hrs)/3600, 0) + NVL(SUM(a.practsum)/3600, 0):eac:AGG@



FROM inv_investments i

JOIN inv_projects ip ON ip.prid = AND ip.is_template = 0 AND ip.is_program = 0

LEFT JOIN prtask t ON t.prprojectid =

LEFT JOIN prassignment a ON a.prtaskid = t.prid

LEFT JOIN prteam tm ON tm.prid = a.team_id

LEFT JOIN srm_resources rol ON = tm.prroleid

LEFT JOIN (SELECT base_id, bd.object_id, bd.usage_sum hrs, base_name

                , b.code base_code, b.is_current , b.description base_desc, b.created_date

           FROM prj_baseline_details bd

           JOIN prj_baselines b ON = bd.baseline_id

           WHERE bd.object_type = 'ASSIGNMENT') b

      ON b.object_id = a.prid

LEFT JOIN (SELECT, r.person_type, pr.prisrole, ocr.hr_id, emp_type

           FROM srm_resources r

           LEFT JOIN prj_resources pr ON pr.prid = --AND pr.prisrole = 0

           LEFT JOIN odf_ca_resource ocr ON =

           LEFT JOIN cmn_lookups_v et ON = r.person_type AND et.language_code = 'en' AND et.lookup_type = 'SRM_RESOURCE_TYPE'         

          ) r ON = a.prresourceid

LEFT JOIN cmn_lookups_v stg ON stg.lookup_code = i.stage_code AND stg.language_code = 'en' AND stg.lookup_type = 'INV_STAGE_TYPE'



AND   @WHERE:PARAM:USER_DEF:INTEGER:i.is_Active:prj_active@

AND   (r.prisrole IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:r.prisrole:isrole@)

AND   (r.person_type IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:r.person_type:res_person_type@)

GROUP BY || '-' || b.base_id || '-' || || '-' || r.hr_id || '-' || r.person_type

     ,,, i.code, rol.full_name,

     , b.base_name, b.base_code, b.is_current

     , b.base_desc, b.created_date

     , r.hr_id, r.emp_type, CASE WHEN r.hr_id IS NULL THEN NULL ELSE END