AnsweredAssumed Answered

Availability of the Resource Link to Timesheet Periods - Portlet

Question asked by brenochuba- on Oct 7, 2010
Latest reply on Oct 13, 2010 by douglas.monteiro
Hi,

I want to link the availability of a resource by Filter - Timesheet periods
I already have a NSQL that brinks to me the Resource Name, Actuals, Resource OBS and Timesheet Periods.

What I want to include in the portlet the Avaliabiliy of the resource, so for example:

I have a period of timesheet from 10/01/2010 to 10/15/2010:
Exampleo:
MONTHLY AVALIABILITY: 160
USING THE FILTER: 80

When I click in the Timesheet Period FILTER with this parameter, I want to show for each resource the Availability in this range of timesheet period.

FILTER: TIMESHEET PERIOD

RESOURCE | ACTUALS | AVAILABILITY

What tables, fields and joins I have to use in the NSQL below to aggregate this needed?
SELECT   @select:dim:user_def:implied:timeentry:te.prid:timeentry_id@,
@select:dim_prop:user_def:implied:timeentry:inv.id:investment_int_id@,
@select:dim_prop:user_def:implied:timeentry:inv.code:investment_id@,
@select:dim_prop:user_def:implied:timeentry:inv.name:investment_name@,
@select:dim_prop:user_def:implied:timeentry:inv.odf_object_code:investment_type@,         
@select:dim_prop:user_def:implied:timeentry:UPPER(inv.odf_object_code):investment_type_upper@,
@select:dim_prop:user_def:implied:timeentry:inv.manager_id:investment_manager_int_id@,
@select:dim_prop:user_def:implied:timeentry:u.last_name||', '||u.first_name:investment_manager@,
@select:dim_prop:user_def:implied:timeentry:t.prid:task_int_id@,
@select:dim_prop:user_def:implied:timeentry:t.prexternalid:task_id@,
@select:dim_prop:user_def:implied:timeentry:t.prname:task_name@,
@select:dim_prop:user_def:implied:timeentry:t.prwbssequence:wbs_sequence@,
@select:dim_prop:user_def:implied:timeentry:a.prid:assignment_int_id@,         
@select:dim_prop:user_def:implied:timeentry:r.id:resource_int_id@,
@select:dim_prop:user_def:implied:timeentry:r.last_name ||', '|| r.first_name:resource_name@,
@select:dim_prop:user_def:implied:timeentry:r.person_type:resource_type_id@,
@select:dim_prop:user_def:implied:timeentry:v.name:resource_type@,
@select:dim_prop:user_def:implied:timeentry:ts.prstatus:timesheet_status_id@,
@select:dim_prop:user_def:implied:timeentry:v2.name:timesheet_status@,
@select:dim_prop:user_def:implied:timeentry:tp.prid:time_period_int_id@,
@select:dim_prop:user_def:implied:timeentry:tp.prstart:time_period_start@,
@select:dim_prop:user_def:implied:timeentry:tp.prfinish - 1:time_period_end@,
@select:dim_prop:user_def:boolean:timeentry:DECODE(NVL(note.note_id,0),0,0,1):note_attached@,
@select:metric:user_def:implied:SUM(DECODE(ts.prstatus,4,0,NVL(te.practsum,0)/(CASE WHEN r.resource_type <> 0 THEN 1 ELSE 3600 END))):pending_actuals@,
@select:metric:user_def:implied:SUM(DECODE(ts.prstatus,4,NVL(te.practsum,0)/(CASE WHEN r.resource_type <> 0 THEN 1 ELSE 3600 END),0)):posted_actuals@,
@select:metric:user_def:implied:SUM(DECODE(ts.prstatus,4,0,NVL(te.practsum,0)/(CASE WHEN r.resource_type <> 0 THEN 1 ELSE 3600 END))) 
                                     + SUM(DECODE(ts.prstatus,4,NVL(te.practsum,0)/(CASE WHEN r.resource_type <> 0 THEN 1 ELSE 3600 END),0)):total_actuals@
FROM     inv_investments inv,
prtask t,
cmn_sec_users u,
prassignment a,
srm_resources r,
cmn_lookups_v v,
prtimeentry te,
(SELECT   te.prid entry_id, MAX(tn.prid) note_id
FROM     prtimeentry te, prnote tn 
WHERE    te.prid = tn.prrecordid
AND      tn.prtablename = 'PRTimeEntry'
 GROUP BY te.prid) note,
prtimesheet ts,
cmn_lookups_v v2,
prtimeperiod tp,
prj_obs_associations assoc,
prj_obs_units unit,
prj_obs_units_flat flat,
prj_obs_types type
WHERE    inv.id = t.prprojectid
AND      inv.is_active <> 0
AND      inv.manager_id = u.id(+)
AND      inv.id = DECODE(@WHERE:PARAM:XML:INTEGER:/data/id/@value@,NULL,inv.id,@WHERE:PARAM:XML:INTEGER:/data/id/@value@)
AND      t.prid = a.prtaskid
AND      a.prresourceid = r.id
AND      r.person_type = v.id(+) AND v.lookup_type(+) = 'SRM_RESOURCE_TYPE' and v.language_code(+) = @WHERE:PARAM:LANGUAGE@
AND      a.prid = te.prassignmentid
AND      te.prid = note.entry_id(+)
AND      te.prtimesheetid = ts.prid
AND      ts.prstatus = v2.lookup_enum(+) and v2.lookup_type(+) = 'TIMESHEET_STATUS' AND v2.language_code(+) = @WHERE:PARAM:LANGUAGE@
AND      ts.prtimeperiodid = tp.prid
AND      @WHERE:SECURITY:INV:inv.id@
AND r.id = assoc.record_id
AND assoc.unit_id = flat.unit_id
AND flat.unit_id = unit.id
AND flat.unit_id = flat.branch_unit_id
AND unit.type_id = type.id
AND unit.id in (
                              SELECT UNIT_ID 
                              FROM 
                              PRJ_OBS_UNITS_FLAT flat
                              WHERE FLAT.BRANCH_UNIT_ID=(CASE 
                                                      WHEN @WHERE:PARAM:USER_DEF:INTEGER:obs_unit@ IS NULL
                                                        THEN unit.ID 
                                                      ELSE @WHERE:PARAM:USER_DEF:INTEGER:obs_unit@ 
                                                    END))
AND      @FILTER@
GROUP BY te.prid, inv.id, inv.code, inv.name, inv.odf_object_code, inv.manager_id, u.id, u.last_name||', '||u.first_name, t.prid, t.prexternalid, t.prname, t.prwbssequence, 
a.prid, r.id, r.last_name ||', '|| r.first_name, r.person_type, v.name, ts.prstatus, v2.name, tp.prid, tp.prstart, tp.prfinish - 1, note.note_id
Best Regards,

Breno

Outcomes