SELECT    @select:dim:user_def:implied:project:rownum:row_no@,
          @select:dim_prop:user_def:implied:project:a.project_name:project_name@,
          @select:dim_prop:user_def:implied:project:a.project_id:project_id@,
          @select:dim_prop:user_def:implied:project:a.project_type:project_type@,
          @select:dim_prop:user_def:implied:project:a.task_name:task_name@,
          @select:dim_prop:user_def:implied:project:a.hours_type:hours_type@,
          @select:dim_prop:user_def:implied:project:a.task_id:task_id@,
          @select:dim_prop:user_def:implied:project:a.charge_codeid:charge_codeid@,
          @select:dim_prop:user_def:implied:project:a.resource_name:resource_name@,
          @select:dim_prop:user_def:implied:project:a.resourceID:resource_id@,
          @select:dim_prop:user_def:implied:project:a.user_name:user_name@,
          @select:dim_prop:user_def:implied:project:a.resource_class:resource_class@,
          @select:dim_prop:user_def:implied:project:a.finance_id:finance_id@,
          @select:dim_prop:user_def:implied:project:a.prom_finance_id:prom_finance_id@,
          @select:dim_prop:user_def:implied:project:a.isp_no:isp_no@,
          @select:dim_prop:user_def:implied:project:a.bt_group:bt_group@,
          @select:dim_prop:user_def:implied:project:a.rcategory:rcategory@,
          @select:dim_prop:user_def:implied:project:a.fund_cat:fund_cat@,
          @select:metric:user_def:implied:a.quantity:quantity@    
FROM 
(
SELECT    p.NAME project_name,
          p.id project_id,
          o.vr1_project_type project_type,
          t.prname task_name,
          hours_type.name hours_type,
          w.task_id task_id,
          c.PREXTERNALID charge_codeid,
          r.full_name resource_name,
          r.unique_name user_name,
          r.id resourceID,
          w.resource_class resource_class,
          sum(w.quantity) quantity,
          o.sun_fpid finance_id,
          o.prom_fpid prom_finance_id,
          o.infra_prj_no isp_no,
          bt_grp.name bt_group,
          fund_cat.name fund_cat,
          rep_cat.name rcategory,
          sum(case when w.sourcemodule = 51 then w.quantity*wv.STDCOST else w.quantity*wv.ACTUALCOST end) total_cost
FROM   ppa_wip w, 
       PPA_POSTINGBATCH B,
       srm_projects p, 
       odf_ca_project o,
       odf_ca_task ot,
       srm_resources r,
       ppa_wip_values wv, 
       prTask t, 
       prTypeCode prt, 
       prChargeCode c, 
       cmn_lookups_v bt_grp,
       cmn_lookups_v rep_cat,
       cmn_lookups_v fund_cat,
       cmn_lookups_v hours_type,
       PRJ_OBS_ASSOCIATIONS assoc
WHERE  w.project_code = p.unique_name
AND    w.BATCHNO=b.BATCHNO
AND    p.id=o.id(+)
AND    p.is_template <> 1
AND    p.is_program <> 1
AND    t.prID= ot.id(+)
AND    w.status = 0
AND    w.resource_code = r.unique_name
AND    wv.currency_type = 'HOME'
AND    w.transno = wv.transno
AND    w.task_id = t.prID
AND    w.charge_code = c.prexternalid
AND    w.input_type = prt.prexternalid

AND    assoc.table_name(+) = 'SRM_RESOURCES'
AND    r.id = assoc.record_id(+)
AND    assoc.unit_id IN (SELECT UNIT_ID 
                         FROM PRJ_OBS_UNITS_FLAT flat, prj_obs_units unit
                         WHERE unit.id = flat.branch_unit_id
                         AND   unit.type_id = 5000004
                         AND   flat.BRANCH_UNIT_ID=(CASE 
                                               WHEN @WHERE:PARAM:USER_DEF:INTEGER:resourceOBS@ IS NULL 
                                               THEN assoc.unit_ID 
                                               ELSE @WHERE:PARAM:USER_DEF:INTEGER:resourceOBS@ 
                                               END)
                        )
AND    o.infra_bt_group =bt_grp.lookup_code(+)
AND    bt_grp.lookup_type(+)='INFRA_BT_GROUP'
AND    bt_grp.language_code(+) = 'en'
AND    o.infra_report_cat = rep_cat.lookup_code(+)
AND    rep_cat.lookup_type(+)='INFRA_REPORT_CAT'
AND    rep_cat.language_code(+) = 'en'
AND    o.infra_fund_cat = fund_cat.lookup_code(+)
AND    fund_cat.lookup_type(+)='INFRA_FUNDING'
AND    fund_cat.language_code(+) = 'en'
AND    ot.is_after_hours = hours_type.lookup_code(+)
AND    hours_type.lookup_type(+)='WORK_HOURS_TYPE'
AND    hours_type.language_code(+) = 'en'

AND    ((w.transdate IS NULL) OR (@WHERE:PARAM:user_def:DATE:trunc(w.transdate):trans_date@))
AND    ((r.person_type IS NULL) OR (@WHERE:PARAM:user_def:string:r.person_type:person_type@))

AND    ((r.id IS NULL) OR (@WHERE:param:user_def:INTEGER:r.id:res_name@))
AND    ((p.id IS NULL) OR (@WHERE:param:user_def:INTEGER:p.id:proj_name@))
AND    ((W.charge_code IS NULL) OR (@WHERE:param:user_def:STRING:UPPER(w.charge_code):charge_code@))

AND    ((ot.Is_after_hours IS NULL) OR (@WHERE:PARAM:USER_DEF:STRING:ot.Is_after_hours:hours_type@))
AND    ((o.infra_fund_cat IS NULL) OR (@WHERE:PARAM:USER_DEF:STRING:o.infra_fund_cat:fund_cat@))
AND    ((o.infra_bt_group IS NULL) OR (@WHERE:PARAM:USER_DEF:STRING:o.infra_bt_group:bt_group@))
AND    ((r.person_type IS NULL) OR (@WHERE:PARAM:USER_DEF:STRING:r.person_type:employee_type@))
AND    ((o.infra_report_cat IS NULL) OR (@WHERE:PARAM:USER_DEF:STRING:o.infra_report_cat:report_cat@))

and    @FILTER@

GROUP BY
          p.NAME,
          p.id,
          o.vr1_project_type,
          o.sun_fpid,
          o.prom_fpid,
          o.infra_prj_no,
          bt_grp.name,
          rep_cat.name,
          fund_cat.name,
          hours_type.name,
          t.prname,
          w.task_id,
          c.PREXTERNALID,
          r.full_name,
          r.id,
          r.unique_name,
          w.resource_class,
          w.transclass,
          w.USER_LOV1,
          w.sourcemodule,
          wv.STDCOST,
          wv.ACTUALCOST          
HAVING    @HAVING_FILTER@
) a