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   @SELECT:DIM:USER_DEF:IMPLIED:PRJ:i.id || '-' || b.base_id || '-' || rol.id || '-' || r.hr_id || '-' || r.person_type:dimid@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:i.id:prj_db_id@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:i.name:prj_name@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:i.code:prj_id@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:rol.full_name:prj_role@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:stg.name:stage@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:b.base_name:rev_name@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:b.base_code:rev_code@,

         @SELECT:DIM_PROP:USER_DEF:BOOLEAN:PRJ:b.is_current:cur_revision@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:b.base_desc:rev_description@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:b.created_date:rev_created_date@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:r.hr_id:res_hrid@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:CASE WHEN r.hr_id IS NULL THEN NULL ELSE r.id END:res_db_id@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:r.emp_type:emp_type@,

         @SELECT:METRIC:USER_DEF:IMPLIED:SUM(a.prestsum)/3600:prj_prestsum:AGG@,

         @SELECT:METRIC:USER_DEF:IMPLIED:SUM(b.hrs)/3600:etc:AGG@,

         @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 = i.id AND ip.is_template = 0 AND ip.is_program = 0

LEFT JOIN prtask t ON t.prprojectid = i.id

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 rol.id = tm.prroleid

LEFT JOIN (SELECT b.id base_id, bd.object_id, bd.usage_sum hrs, b.name 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 b.id = bd.baseline_id

           WHERE bd.object_type = 'ASSIGNMENT') b

      ON b.object_id = a.prid

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

           FROM srm_resources r

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

           LEFT JOIN odf_ca_resource ocr ON ocr.id = r.id

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

          ) r ON r.id = 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'

WHERE @FILTER@

AND   @WHERE:PARAM:USER_DEF:INTEGER:i.id:prj_id@

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 i.id || '-' || b.base_id || '-' || rol.id || '-' || r.hr_id || '-' || r.person_type

     , i.id, i.name, i.code, rol.full_name, stg.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 r.id END

HAVING @HAVING_FILTER@

Outcomes