Notes

SELECT a.period_1 AS period_1,
a.period_2 AS period_2,
a.period_3 AS period_3,
a.period_4 AS period_4,
a.period_5 AS period_5,
a.period_6 AS period_6,
x.master_investment AS master_investment,
x.master_investment_key AS master_investment_key,
x.master_investment_name AS master_investment_name,
x.investment_name AS investment_name,
i.investment_key AS investment_key,
x.detail_level_name AS detail_level_name,
x.resource_name AS resource_name,
x.start_date AS start_date,
x.finish_date AS finish_date,
x.wbs_sequence AS wbs_sequence,
x.wbs_level AS wbs_level,
x.is_milestone AS is_milestone,
x.subproject_order AS subproject_order,
x.subproject_image AS subproject_image,
x.days_late AS days_late,
x.days_late_pct AS days_late_pct,
a.start_date AS report_start_date,
CASE WHEN x.finish_date < a.start_date THEN $P!{daysInPeriod}
WHEN x.start_date <= a.start_date THEN 0
ELSE $P!{dwhDBSchema}.dwh_cal_datediff_fct(x.start_date,a.start_date) END AS start_position,
CASE WHEN x.finish_date < a.start_date THEN 0
WHEN x.finish_date > a.finish_date THEN $P!{daysInPeriod}
WHEN x.start_date <= a.start_date THEN $P!{dwhDBSchema}.dwh_cal_datediff_fct(x.finish_date,a.start_date) + 1
ELSE $P!{dwhDBSchema}.dwh_cal_datediff_fct(x.finish_date,x.start_date) + 1 END AS total_size ,
x.task_status AS task_status
FROM dwh_inv_investment i
INNER JOIN dwh_inv_project p ON i.investment_key = p.investment_key
INNER JOIN (SELECT MAX(CASE WHEN c6.period_row = 1 THEN c6.period_name ELSE NULL END) period_1,
MAX(CASE WHEN c6.period_row = 2 THEN c6.period_name ELSE NULL END) period_2,
MAX(CASE WHEN c6.period_row = 3 THEN c6.period_name ELSE NULL END) period_3,
MAX(CASE WHEN c6.period_row = 4 THEN c6.period_name ELSE NULL END) period_4,
MAX(CASE WHEN c6.period_row = 5 THEN c6.period_name ELSE NULL END) period_5,
MAX(CASE WHEN c6.period_row = 6 THEN c6.period_name ELSE NULL END) period_6,
MIN(c6.period_start_date) start_date,
MAX(c6.period_end_date) finish_date
FROM (SELECT c.period_name,
c.period_start_date,
c.period_end_date,
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY c.period_start_date) period_row
FROM (SELECT DISTINCT
CASE WHEN $P{periodTypeAll} = 'QUARTER' THEN cln.quarter_name
WHEN $P{periodTypeAll} = 'YEAR' THEN cln.year_name
ELSE cln.period_name END AS period_name,
CASE WHEN $P{periodTypeAll} = 'QUARTER' THEN c.quarter_start_date
WHEN $P{periodTypeAll} = 'YEAR' THEN c.year_start_date
ELSE c.period_start_date END AS period_start_date,
CASE WHEN $P{periodTypeAll} = 'QUARTER' THEN c.quarter_end_date
WHEN $P{periodTypeAll} = 'YEAR' THEN c.year_end_date
ELSE c.period_end_date END AS period_end_date
FROM dwh_cmn_period c
INNER JOIN dwh_cmn_period_ln cln ON c.period_key = cln.period_key
AND cln.language_code = $P{periodUserLanguage}
WHERE c.period_start_date BETWEEN $P!{dwhDBSchema}.dwh_cal_startdate_fct($P!{dwhDBSchema}.dwh_cal_trunc_date_fct($P{ganttStartDate}),$P{periodTypeAll})
AND $P!{dwhDBSchema}.dwh_cal_date_add_fct($P!{dwhDBSchema}.dwh_cal_startdate_fct($P!{dwhDBSchema}.dwh_cal_trunc_date_fct($P{ganttStartDate}),$P{periodTypeAll}),$P{periodTypeAll},6)
AND c.period_type_key = CASE WHEN $P{periodTypeAll} = 'WEEK' THEN 'WEEKLY' ELSE 'MONTHLY' END) c) c6) a ON 1=1


INNER JOIN (SELECT 1 AS master_investment,
i.investment_key AS master_investment_key,
i.investment_name AS master_investment_name,
i.investment_key AS investment_key,
i.investment_name AS investment_name,
i.investment_name AS detail_level_name,
NULL AS resource_name,
i.schedule_start AS start_date,
i.schedule_finish AS finish_date,
0 AS wbs_sequence,
0 AS wbs_level,
0 AS is_milestone,
0 AS subproject_order,
0 AS subproject_image,
i.days_late AS days_late,
i.days_late_pct AS days_late_pct,
'' AS task_status
FROM dwh_inv_investment i
INNER JOIN dwh_inv_project p ON i.investment_key = p.investment_key
WHERE i.is_template = 0
AND p.is_program = 0
AND i.investment_key = $P{projectKey_1}

UNION ALL
SELECT 1 AS master_investment,
i.investment_key AS master_investment_key,
i.investment_name AS master_investment_name,
i.investment_key AS investment_key,
i.investment_name AS investment_name,
t.task_name AS detail_level_name,
NULL AS resource_name,
t.start_date AS start_date,
t.finish_date AS finish_date,
t.wbs_sequence AS wbs_sequence,
t.wbs_level AS wbs_level,
t.is_milestone AS is_milestone,
0 AS subproject_order,
0 AS subproject_image ,
$P!{dwhDBSchema}.dwh_start_days_late_fct(t.start_date,t.base_start_date,t.task_status_key) AS days_late,
$P!{dwhDBSchema}.dwh_start_days_late_pct_fct(i.investment_key,t.task_key) AS days_late_pct,
Decode(t.TASK_STATUS_KEY, 0,'Not Started', 1, 'Started' , 2, 'Completed' ) AS task_status
FROM dwh_inv_investment i
INNER JOIN dwh_inv_project p ON i.investment_key = p.investment_key
INNER JOIN dwh_inv_task t ON i.investment_key = t.investment_key
AND (t.is_task = CASE WHEN $P{detailLevel} = 'PHASE' THEN 0 ELSE t.is_task END)
WHERE i.is_template = 0
AND p.is_program = 0
AND i.investment_key = $P{projectKey_1}
AND $P!{dwhDBSchema}.dwh_null_number_fct(t.is_subproject,0) = 0
AND $P{detailLevel} <> 'PROJECT'
AND ($P{includeTasksWithinGanttDateRange} = 0
OR ($P{includeTasksWithinGanttDateRange} = 1
AND t.start_date >= $P!{dwhDBSchema}.dwh_cal_startdate_fct($P!{dwhDBSchema}.dwh_cal_trunc_date_fct($P{ganttStartDate}),$P{periodTypeAll})
AND t.finish_date <= $P!{dwhDBSchema}.dwh_cal_date_add_fct($P!{dwhDBSchema}.dwh_cal_startdate_fct($P!{dwhDBSchema}.dwh_cal_trunc_date_fct($P{ganttStartDate}),$P{periodTypeAll}),$P{periodTypeAll},6)))

UNION ALL
SELECT 1 AS master_investment,
i.investment_key AS master_investment_key,
i.investment_name AS master_investment_name,
i.investment_key AS investment_key,
i.investment_name AS investment_name,
av.resource_name AS detail_level_name,
av.resource_name AS resource_name,
av.start_date AS start_date,
av.finish_date AS finish_date,
t.wbs_sequence AS wbs_sequence,
t.wbs_level AS wbs_level,
t.is_milestone AS is_milestone,
0 AS subproject_order,
0 AS subproject_imag ,
$P!{dwhDBSchema}.dwh_start_days_late_fct(t.start_date,t.base_start_date,t.task_status_key) AS days_late,
$P!{dwhDBSchema}.dwh_start_days_late_pct_fct(i.investment_key,t.task_key) AS days_late_pct,
Decode(t.TASK_STATUS_KEY, 0,'Not Started', 1, 'Started' , 2, 'Completed' ) AS task_status
FROM dwh_inv_investment i
INNER JOIN dwh_inv_project p ON i.investment_key = p.investment_key
INNER JOIN dwh_inv_task t ON i.investment_key = t.investment_key
AND (t.is_task = CASE WHEN $P{detailLevel} = 'PHASE' THEN 0 ELSE t.is_task END)
INNER JOIN dwh_inv_assignment av ON t.task_key = av.task_key
WHERE i.is_template = 0
AND p.is_program = 0
AND i.investment_key = $P{projectKey_1}
AND $P!{dwhDBSchema}.dwh_null_number_fct(t.is_subproject,0) = 0
AND $P{detailLevel} = 'RESOURCE'
AND ($P{includeTasksWithinGanttDateRange} = 0
OR ($P{includeTasksWithinGanttDateRange} = 1
AND t.start_date >= $P!{dwhDBSchema}.dwh_cal_startdate_fct($P!{dwhDBSchema}.dwh_cal_trunc_date_fct($P{ganttStartDate}),$P{periodTypeAll})
AND t.finish_date <= $P!{dwhDBSchema}.dwh_cal_date_add_fct($P!{dwhDBSchema}.dwh_cal_startdate_fct($P!{dwhDBSchema}.dwh_cal_trunc_date_fct($P{ganttStartDate}),$P{periodTypeAll}),$P{periodTypeAll},6)))

) x ON i.investment_key = x.master_investment_key
WHERE i.is_template = 0
AND p.is_program = 0
AND i.investment_key = $P{projectKey_1}
and x.wbs_level < $P{WBS_Level}