CREATE VIEW STRUCTURED_SAP_PSP(
UUID,
PARENT_PSP,
EFFORT_ACT,
EFFORT_REM,
EFFORT_PLAN,
EFFORT_ACT_PLANTA,
EFFORT_REM_PLANTA,
EFFORT_PLAN_PLANTA,
COSTS_ACT,
COSTS_REM,
COSTS_PLAN,
COSTS_ACT_PLANTA,
COSTS_REM_PLANTA,
COSTS_PLAN_PLANTA,
BUDGET)
AS
WITH tree AS (
  SELECT
	a.PARENT_PSP,
	a.UUID,
	a.UUID as root_uuid,
	aae.EFFORT_ACT,
	aae.EFFORT_REM,
	aae.EFFORT_PLAN,
	aae.EFFORT_ACT_PLANTA,
	aae.EFFORT_REM_PLANTA,
	aae.EFFORT_PLAN_PLANTA,
	aae.COSTS_ACT,
	aae.COSTS_REM,
	aae.COSTS_PLAN,
	aae.COSTS_ACT_PLANTA,
	aae.COSTS_REM_PLANTA,
	aae.COSTS_PLAN_PLANTA,
	aae.BUDGET,
	0 as level_
  FROM DT963 a
		LEFT JOIN DT975 aae ON aae.SAP_PSP_UUID = a.UUID
  UNION ALL
  SELECT
	b.PARENT_PSP,
	b.UUID,
	tree.root_uuid,
	aae_c.EFFORT_ACT,
	aae_c.EFFORT_REM,
	aae_c.EFFORT_PLAN,
	aae_c.EFFORT_ACT_PLANTA,
	aae_c.EFFORT_REM_PLANTA,
	aae_c.EFFORT_PLAN_PLANTA,
	aae_c.COSTS_ACT,
	aae_c.COSTS_REM,
	aae_c.COSTS_PLAN,
	aae_c.COSTS_ACT_PLANTA,
	aae_c.COSTS_REM_PLANTA,
	aae_c.COSTS_PLAN_PLANTA,
	aae_c.BUDGET,
	level_ + 1
  FROM DT963 b
		INNER JOIN DT975 aae_c ON aae_c.SAP_PSP_UUID = b.UUID
			INNER JOIN tree ON b.PARENT_PSP = tree.UUID
)
SELECT
UUID,
PARENT_PSP,
S.EFFORT_ACT,
S.EFFORT_REM,
S.EFFORT_PLAN,
S.EFFORT_ACT_PLANTA,
S.EFFORT_REM_PLANTA,
S.EFFORT_PLAN_PLANTA,
S.COSTS_ACT,
S.COSTS_REM,
S.COSTS_PLAN,
S.COSTS_ACT_PLANTA,
S.COSTS_REM_PLANTA,
S.COSTS_PLAN_PLANTA,
S.BUDGET
FROM DT963
   INNER JOIN (
             select root_uuid,
                    sum(EFFORT_ACT) AS EFFORT_ACT,
					sum(EFFORT_REM) AS EFFORT_REM,
					sum(EFFORT_PLAN) AS EFFORT_PLAN,
					sum(EFFORT_ACT_PLANTA) AS EFFORT_ACT_PLANTA,
					sum(EFFORT_REM_PLANTA) AS EFFORT_REM_PLANTA,
					sum(EFFORT_PLAN_PLANTA) AS EFFORT_PLAN_PLANTA,
					sum(COSTS_ACT) AS COSTS_ACT,
					sum(COSTS_REM) AS COSTS_REM,
					sum(COSTS_PLAN) AS COSTS_PLAN,
					sum(COSTS_ACT_PLANTA) AS COSTS_ACT_PLANTA,
					sum(COSTS_REM_PLANTA) AS COSTS_REM_PLANTA,
					sum(COSTS_PLAN_PLANTA) AS COSTS_PLAN_PLANTA,
					sum(BUDGET) AS BUDGET
             FROM tree
             GROUP BY root_uuid
             ) AS S
    ON DT963.UUID = S.root_uuid;


CREATE VIEW STRUCTURED_SAP_PSP_REPORT(
UUID,
PR_ID,
REPORT_ID,
PARENT_PSP,
EFFORT_ACT,
EFFORT_REM,
EFFORT_PLAN,
EFFORT_ACT_PLANTA,
EFFORT_REM_PLANTA,
EFFORT_PLAN_PLANTA,
COSTS_ACT,
COSTS_REM,
COSTS_PLAN,
COSTS_ACT_PLANTA,
COSTS_REM_PLANTA,
COSTS_PLAN_PLANTA,
BUDGET)
AS
WITH tree AS (
  SELECT
    a.UUID,
	a.PARENT_PSP,
	a.PSP_UUID,
	a.PSP_UUID as root_uuid,
	a.PR_ID,
	a.PR_ID as root_pr_id,
	a.REPORT_ID,
	a.REPORT_ID as root_report_id,
	aae.EFFORT_ACT,
	aae.EFFORT_REM,
	aae.EFFORT_PLAN,
	aae.EFFORT_ACT_PLANTA,
	aae.EFFORT_REM_PLANTA,
	aae.EFFORT_PLAN_PLANTA,
	aae.COSTS_ACT,
	aae.COSTS_REM,
	aae.COSTS_PLAN,
	aae.COSTS_ACT_PLANTA,
	aae.COSTS_REM_PLANTA,
	aae.COSTS_PLAN_PLANTA,
	aae.BUDGET,
	0 as level_
  FROM DT974 a
		LEFT JOIN DT976 aae ON aae.SAP_PSP_REP_UUID = a.PSP_UUID
							AND aae.PR_ID = a.PR_ID
							AND aae.REPORT_ID = a.REPORT_ID
  UNION ALL
  SELECT
    b.UUID,
	b.PARENT_PSP,
	b.PSP_UUID,
	tree.root_uuid,
	b.PR_ID,
	tree.root_pr_id,
	b.REPORT_ID,
	tree.root_report_id,
	aae_c.EFFORT_ACT,
	aae_c.EFFORT_REM,
	aae_c.EFFORT_PLAN,
	aae_c.EFFORT_ACT_PLANTA,
	aae_c.EFFORT_REM_PLANTA,
	aae_c.EFFORT_PLAN_PLANTA,
	aae_c.COSTS_ACT,
	aae_c.COSTS_REM,
	aae_c.COSTS_PLAN,
	aae_c.COSTS_ACT_PLANTA,
	aae_c.COSTS_REM_PLANTA,
	aae_c.COSTS_PLAN_PLANTA,
	aae_c.BUDGET,
	level_ + 1
  FROM DT974 b
		INNER JOIN DT976 aae_c ON aae_c.SAP_PSP_REP_UUID = b.PSP_UUID
							  AND aae_c.PR_ID = b.PR_ID
							  AND aae_c.REPORT_ID = b.REPORT_ID
			INNER JOIN tree ON b.PARENT_PSP = tree.PSP_UUID
								AND b.PR_ID =  tree.PR_ID
								AND b.REPORT_ID = tree.REPORT_ID
)
SELECT
UUID,
PR_ID,
REPORT_ID,
PARENT_PSP,
S.EFFORT_ACT,
S.EFFORT_REM,
S.EFFORT_PLAN,
S.EFFORT_ACT_PLANTA,
S.EFFORT_REM_PLANTA,
S.EFFORT_PLAN_PLANTA,
S.COSTS_ACT,
S.COSTS_REM,
S.COSTS_PLAN,
S.COSTS_ACT_PLANTA,
S.COSTS_REM_PLANTA,
S.COSTS_PLAN_PLANTA,
S.BUDGET
FROM DT974
   INNER JOIN (
             select root_uuid,
			        root_pr_id,
					root_report_id,
                    sum(EFFORT_ACT) AS EFFORT_ACT,
					sum(EFFORT_REM) AS EFFORT_REM,
					sum(EFFORT_PLAN) AS EFFORT_PLAN,
					sum(EFFORT_ACT_PLANTA) AS EFFORT_ACT_PLANTA,
					sum(EFFORT_REM_PLANTA) AS EFFORT_REM_PLANTA,
					sum(EFFORT_PLAN_PLANTA) AS EFFORT_PLAN_PLANTA,
					sum(COSTS_ACT) AS COSTS_ACT,
					sum(COSTS_REM) AS COSTS_REM,
					sum(COSTS_PLAN) AS COSTS_PLAN,
					sum(COSTS_ACT_PLANTA) AS COSTS_ACT_PLANTA,
					sum(COSTS_REM_PLANTA) AS COSTS_REM_PLANTA,
					sum(COSTS_PLAN_PLANTA) AS COSTS_PLAN_PLANTA,
					sum(BUDGET) AS BUDGET
             FROM tree
             GROUP BY root_uuid, root_pr_id, root_report_id
             ) AS S
    ON DT974.PSP_UUID = S.root_uuid
	AND DT974.PR_ID = S.root_pr_id
	AND DT974.REPORT_ID = S.root_report_id;