WITH d AS
  (SELECT TO_DATE(TO_CHAR(?,'dd/MM/YYYY'),'dd/MM/YYYY') AS from_date, --from date parameter retrieved from the filter ex. 02/03/2020
    TO_DATE(TO_CHAR(?,'dd/MM/YYYY'),'dd/MM/YYYY') AS TO_DATE --to date parameter retreived from the filter ex. 31/03/2020
  FROM dual
  )
SELECT
w.WBS_ID,
w.r,
w.PARENT_WBS_ID,
w.OPERATION_CENTER_ID,
w.CAN_ASSIGN_TEAM,
w.LVL,
w.ref_number,
w.WBS_NAME_LEVEL_1,
w.WBS_REF_NUMBER_LEVEL_1,
w.WBS_NAME_LEVEL_2,
w.WBS_REF_NUMBER_LEVEL_2,
w.WBS_NAME_LEVEL_3,
w.WBS_REF_NUMBER_LEVEL_3,
p.DISPLAY_FIRST_DAY,
p.PERIOD,
p.START_DATE,
p.END_DATE,
DECODE (p.PERIOD, 'M', TO_CHAR ( p.DISPLAY_FIRST_DAY, 'YYYYMM'), TO_CHAR ( p.DISPLAY_FIRST_DAY, 'YYYYMMWW')) AS date_order,
UPPER(w.WBS_NAME_LEVEL_1 || ' ' || w.WBS_REF_NUMBER_LEVEL_1 || ' ' || w.WBS_NAME_LEVEL_2 || ' ' || w.WBS_REF_NUMBER_LEVEL_2 || ' ' || w.WBS_NAME_LEVEL_3 || ' ' || w.WBS_REF_NUMBER_LEVEL_3) AS ID_STRING,
p_wbs.sumHrDPerWBS(w.wbs_id, p.start_date, p.end_date) AS SUM_HRD,
TO_CHAR(p.display_first_day,'iw') AS WEEK_OF_YEAR
FROM
  (SELECT DISTINCT TRUNC( from_date + LEVEL -1, 'iw') display_first_day,
    'W' AS period,
    CASE
      WHEN TRUNC( from_date + LEVEL -1, 'iw') < from_date
      THEN from_date
      ELSE TRUNC( from_date + LEVEL -1, 'iw')
    END AS start_date,
    CASE
      WHEN TRUNC( from_date + LEVEL -1, 'iw') + 6 > TO_DATE
      THEN TO_DATE
      ELSE TRUNC( from_date + LEVEL -1, 'iw') + 6
    END AS end_date
  FROM d
    CONNECT BY LEVEL <= TO_DATE - from_date
  UNION
  SELECT DISTINCT TRUNC( from_date + LEVEL -1, 'MM') display_first_day,
    'M' AS period,
    CASE
      WHEN TRUNC( from_date + LEVEL -1, 'MM') < from_date
      THEN from_date
      ELSE TRUNC( from_date + LEVEL -1, 'MM')
    END AS start_date,
    CASE
      WHEN LAST_DAY( from_date + LEVEL -1) > TO_DATE
      THEN TO_DATE
      ELSE LAST_DAY( from_date + LEVEL -1)
    END AS end_date
  FROM d
    CONNECT BY LEVEL <= TO_DATE - from_date
  ORDER BY period,
    display_first_day
  ) p
JOIN
  (SELECT
    ROWNUM AS r,
    CASE
      WHEN LEVEL = 1
      THEN wbs.name
      ELSE '/'
    END AS wbs_name_level_1,
    CASE
      WHEN LEVEL = 1
      THEN wbs.ref_number
      ELSE '/'
    END AS wbs_ref_number_level_1,
    CASE
      WHEN LEVEL = 2
      THEN wbs.name
      ELSE '/'
    END AS wbs_name_level_2,
    CASE
      WHEN LEVEL = 2
      THEN wbs.ref_number
      ELSE '/'
    END AS wbs_ref_number_level_2,
    CASE
      WHEN LEVEL = 3
      THEN wbs.name
      ELSE '/'
    END AS wbs_name_level_3,
    CASE
      WHEN LEVEL = 3
      THEN wbs.ref_number
      ELSE '/'
    END AS wbs_ref_number_level_3,
    wbs.wbs_id,
    parent_wbs_Id,
    wbs.ref_number,
    wbs.operation_center_id ,
    wbs.can_assign_team,
    LEVEL AS LVL
  FROM wbs
  WHERE LEVEL            <=3
  AND operation_center_id = ? -- operation center of the WBS ex. PERRARD (101888)
    START WITH wbs_id  = ? -- WBS we are looking at in WBS activity control page (HESPERANGE residence JOME with ID -> 221799)
    CONNECT BY parent_wbs_id = PRIOR wbs_id
  ORDER SIBLINGS BY ref_number
  ) w
ON 1 = 1
WHERE ((? = 'true' --check box for display of MONTHLY sums ex. 'true'
AND p.period  = 'M'
AND (( ? = 'true' --check box for display of MONTHLY sums for level 1 ex. 'true'
AND w.lvl     = 1 )
OR ( ?  = 'true' --check box for display of MONTHLY sums for level 2 ex. 'true'
AND w.lvl     = 2 )
OR ( ?  = 'true' --check box for display of MONTHLY sums for level 3 ex. 'true'
AND w.lvl     = 3 ) ) )
OR ( ?   = 'true' --check box for display of WEEKLY sums ex. 'false'
AND p.period  = 'W'
AND ((? = 'true' --check box for display of WEEKLY sums for level 1 ex. 'false'
AND w.lvl     = 1 )
OR ( ?  = 'true' --check box for display of WEEKLY sums for level 2 ex. 'false'
AND w.lvl     = 2 )
OR ( ?   = 'true' --check box for display of WEEKLY sums for level 3 ex. 'false'
AND w.lvl     = 3 ))))
AND (? = 'true' OR p_wbs.sumHrDPerWBS(w.wbs_id, p.start_date, p.end_date) != 0) --check box for displaying of empty lines
ORDER BY date_order, r