AnsweredAssumed Answered

Edit "Team Member Organizer" Query to show "Parent Task Name" field?

Question asked by Debz_MacArthur on Jul 12, 2013
Latest reply on Jul 25, 2013 by Debz_MacArthur
I would like to edit the Team Member Portlet to show parent task name. This portlet retrieves it's data via a query: Team Member Organizer

Has anybody edited this to show parent task name and if so can you let me know how?

The current NSQL is as follows:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:assignment:a.prid:assign_int_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:inv.id:project_int_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:inv.code:project_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:inv.name:project_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:@UPPER@(inv.odf_object_code):investment_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:r.full_name:res_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:r.id:res_int_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:t.prid:task_int_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:t.prwbssequence:wbs_sequence@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:t.prname:task_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:t.prpriority:task_priority@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:v.name:task_status@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:@NVL@(a.prstart,t.prstart):assign_start_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:@DBUSER@.COP_CALC_FINISH_TIME_FCT(@NVL@(a.prfinish,t.prfinish)):assign_finish_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:t.prismilestone:is_milestone@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:assignment:
CASE WHEN t.prguidelines IS NULL AND s.prguidelines IS NOT NULL THEN 1
WHEN t.prguidelines IS NOT NULL AND s.prguidelines IS NOT NULL THEN 1
WHEN t.prguidelines IS NOT NULL AND CHARINDEX('http',@LOWER@(t.prguidelines)) > 0 THEN 1
ELSE 0 END:guideline@,


@SELECT:METRIC:USER_DEF:IMPLIED:tsk_d.days_late:days_late@,
@SELECT:METRIC:USER_DEF:IMPLIED:tsk_d.days_late:days_late_sl@,
@SELECT:METRIC:USER_DEF:IMPLIED:tsk_d.days_late_pct:days_late_pct@,
@SELECT:METRIC:USER_DEF:IMPLIED:tsk_d.days_late_pct:days_late_pct_sl@,


@SELECT:METRIC:USER_DEF:IMPLIED:SUM(@NVL@(a.prestsum,0)/3600):etc_hrs@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(@NVL@(a.practsum,0)/3600):actual_hrs@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(@NVL@(a.prpendactsum,0)/3600):pending_actual_hrs@
FROM PRASSIGNMENT a
INNER JOIN PRTASK t
ON a.prtaskid = t.prid
INNER JOIN srm_resources r
ON a.prresourceid = r.id
AND r.resource_type = 0
INNER JOIN inv_investments inv
ON t.prprojectid = inv.id
INNER JOIN inv_projects prj
ON inv.id = prj.prid
INNER JOIN COP_TSK_DAYS_LATE_V tsk_d
ON t.prid = tsk_d.task_id
LEFT OUTER JOIN PRJ_BASELINE_DETAILS det
ON t.prid = det.object_id
AND det.object_type = 'TASK'
AND det.is_current = 1
LEFT OUTER JOIN cmn_lookups_v v
ON t.prstatus = v.lookup_code
AND v.lookup_type = 'prTaskStatus'
AND v.language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN prsite s ON s.prguidelines IS NOT NULL



WHERE ((t.prstatus IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:t.prstatus:TASK_STATUS@))
AND @NVL@(@WHERE:PARAM:XML:INTEGER:/data/id/@value@,t.prprojectid) = t.prprojectid
AND @WHERE:PARAM:USER_DEF:INTEGER:inv.is_active:ACTIVE_FILTER@
AND ((prj.is_template IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:prj.is_template:TEMPLATE_FILTER@))
AND ((prj.is_program IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:prj.is_program:PROGRAM_FILTER@))
AND @WHERE:SECURITY:PROJECT:INV.ID@
AND @FILTER@
GROUP BY a.prid, inv.id, inv.code, inv.name, r.full_name, r.id,
t.prid, t.prwbssequence, t.prname, @UPPER@(inv.odf_object_code),
t.prpriority, v.name, t.prstart, t.prfinish, a.prstart, a.prfinish,
t.prismilestone, s.prguidelines, t.prguidelines,
det.finish_date, det.start_date, tsk_d.days_late, tsk_d.days_late_pct

Thanks,

Debs :wink:

Outcomes