-- add simulation report column
alter table DT823 add SIMULATION_REPORT number(5,0) DEFAULT 0;
update DT823 set SIMULATION_REPORT = DEFAULT;

-- add report type to portfolio status report
alter table DT090 add REPORT_TYPE number(5,0) DEFAULT 1;
alter table DT090 add SIM_DATE_FROM date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');
alter table DT090 add SIM_DATE_TO date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');

-- simulation DIs for project per portfolio status report table
alter table DT093 add PR_PRIO number(5,0) DEFAULT 0;
alter table DT093 add REQUESTED_START date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');
alter table DT093 add REQUESTED_END date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');
alter table DT093 add START_SIMU date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');
alter table DT093 add END_SIMU date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');
alter table DT093 add COLOR_ID_REM varchar2(8 char);
alter table DT093 add COLOR_ID_ACT varchar2(8 char);
alter table DT093 add CALC_START date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');
alter table DT093 add CALC_END date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');
alter table DT093 add ACT_START date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');
alter table DT093 add LAST_LOAD_DATE date DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY');

-- color columns per project
alter table DT858 add COLOR_ID_REM varchar2(8 char);
alter table DT858 add COLOR_ID_ACT varchar2(8 char);

update DT858 set COLOR_ID_ACT = '000127' where COLOR_ID_ACT is null;

-- ole for overload per project
alter table DT093 add OLE_OVERLOAD varchar2(20 char);

-- ole for overload per resource
alter table DT467 add OLE_SIM_OVERLOAD varchar2(20 char);

-- activate the portfolio report primary key to be able to reference this
alter table DT090 enable constraint "PID090DT090";

-- new simulation table and data basis as views
CREATE TABLE "PORTFOLIO_SIM" 
   (	"UUID" RAW(16) DEFAULT SYS_GUID(), 
	"STATUSREPORT_ID" VARCHAR2(10 CHAR), 
	"PORTFOLIO_ID" VARCHAR2(18 CHAR), 
	"TYPE" NUMBER(5,0) DEFAULT 0, 
	"OS_CREATED_ON" DATE DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY'), 
	"OS_MODIFIED_ON" DATE DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY'), 
	"OS_RWDU" NUMBER(5,0) DEFAULT 0, 
	"OS_CREATED_BY" VARCHAR2(20 CHAR), 
	"OS_MODIFIED_BY" VARCHAR2(20 CHAR), 
	"OS_LICENSE" VARCHAR2(4 CHAR), 
	"OS_OPC" VARCHAR2(8 CHAR), 
	"OS_OWNER" NUMBER(5,0) DEFAULT 0, 
	"OS_GROUP" NUMBER(5,0) DEFAULT 0, 
	"OS_ANY" NUMBER(5,0) DEFAULT 0, 
	"TIME_PERIOD" DATE DEFAULT TO_DATE('01.01.1970','DD.MM.YYYY'), 
	"RESOURCE" VARCHAR2(20 CHAR), 
	"PR_ID" VARCHAR2(18 CHAR), 
	"VALUE" NUMBER DEFAULT 0, 
	"OVERALL_ORDER" NUMBER(10,0) DEFAULT 0, 
	"PR_TYPE" VARCHAR2(18 CHAR), 
	"MAIN_PR_ID" VARCHAR2(18 CHAR), 
	"PR_REPORT_ID" VARCHAR2(10 CHAR), 
	"PR_FUNC_ID" VARCHAR2(18 CHAR), 
	 CONSTRAINT "DT095UUID_NOT_NULL" CHECK ("UUID" is not null) ENABLE, 
	 CONSTRAINT "PID095DT095" PRIMARY KEY ("UUID"), 
	 CONSTRAINT "FKDT095NULL" FOREIGN KEY ("STATUSREPORT_ID", "PORTFOLIO_ID")
	  REFERENCES "DT090" ("STATUSREPORT_ID", "PORTFOLIO_ID") ON DELETE CASCADE DEFERRABLE ENABLE
   );

  CREATE INDEX "IDX_DT095NULL" ON "PORTFOLIO_SIM" ("STATUSREPORT_ID", "PORTFOLIO_ID");
  

  CREATE OR REPLACE VIEW "V_RES_CAPA" ("SUM_TYPE", "TIME_PERIOD", "RES_ID", "SUMM_AVAIL_CAP", "SUMM_PLANNED",
  "SUMM_UTILIZATION", "SUMM_BASE_LOAD_W3", "SUMM_VALUE_4", "SUMM_VALUE_5", "SUMM_USED", "SUMM_PLANNED_PRC1", 
  "SUMM_PLANNED_PRC2", "SUMM_PLANNED_PRC3", "SUMM_PLANNED_PRC4", "SUMM_PLANNED_PRC5") AS 
  SELECT
  sum_type,
  time_period,
  res_id,
  summ_avail_cap,
  summ_planned,
  CASE WHEN nvl(summ_avail_cap, 0) > 0
    THEN (summ_planned / summ_avail_cap) * 100
    ELSE 0
  END summ_utilization,
  summ_base_load_w3,
  summ_value_4,
  summ_value_5,
  summ_used,
  summ_planned_prc1,
  summ_planned_prc2,
  summ_planned_prc3,
  summ_planned_prc4,
  summ_planned_prc5
FROM (
 SELECT
    'M' sum_type,
    TRUNC(DI001327, 'MONTH') time_period,
    DI001326 res_id,
    SUM(DI002454) summ_avail_cap,
    SUM(DI002483) summ_planned,
    SUM(DI002640) summ_base_load_w3,
    SUM(DI002641) summ_value_4,
    SUM(DI002642) summ_value_5,
    SUM(DI002464) summ_used,
    SUM(DI002484) summ_planned_prc1,
    SUM(DI002485) summ_planned_prc2,
    SUM(DI002486) summ_planned_prc3,
    SUM(DI002487) summ_planned_prc4,
    SUM(DI002488) summ_planned_prc5
  FROM dt468
  GROUP BY TRUNC(DI001327, 'MONTH'), DI001326
  UNION
  SELECT
    'W' sum_type,
    TRUNC(DI001327, 'IW') time_period,
    DI001326 res_id,
    SUM(DI002454) summ_avail_cap,
    SUM(DI002483) summ_planned,
    SUM(DI002640) summ_base_load_w3,
    SUM(DI002641) summ_value_4,
    SUM(DI002642) summ_value_5,
    SUM(DI002464) summ_used,
    SUM(DI002484) summ_planned_prc1,
    SUM(DI002485) summ_planned_prc2,
    SUM(DI002486) summ_planned_prc3,
    SUM(DI002487) summ_planned_prc4,
    SUM(DI002488) summ_planned_prc5
  FROM dt468
  GROUP BY TRUNC(DI001327, 'IW'), DI001326
);


CREATE OR REPLACE VIEW "V_RES_PR_LOAD_PORTFOLIO" ("TIME_PERIOD", "RES_ID", "PARENT_RES_ID", "PF_ID", "PR_ID", "PR_REPORT_ID", "MAIN_PR_ID", "PARENT_PR_ID", "PR_PRIO", "PR_REQ_END", "PR_CALC_END", "PR_FUNC_ID", "PR_TYPE", "PR_CODE", "REMAINING", "ACTUAL", "AVAILABILITY", "BASELOAD", "IS_EXTERNAL") AS 
  SELECT
  res_pr_pf_load.time_period,
  res_pr_pf_load.res_id,
  parent_res.parent_res_id,
  res_pr_pf_load.pf_id,
  res_pr_pf_load.pr_id,
  pr_report.pr_report_id,
  pr_report.main_pr_id,
  pr_report.parent_pr_id,
  pr.pr_prio,
  pr_report.pr_req_end,
  pr_report.pr_calc_end,
  pr_report.pr_func_id,
  pr_report.pr_type,
  pr_report.pr_code,
  res_pr_pf_load.remaining,
  res_pr_pf_load.actual,
  res_cap.summ_avail_cap availability,
  res_cap.summ_base_load_w3 baseload,
  res_pr_pf_load.is_external  
FROM(
  WITH
  portfolio_pr_reports AS (
    SELECT
      DI023102 pf_id,
      DI023101 pr_id,
      -- use assigned monthly reported, or the automatic report for simulation as fallback
      nvl(DI062360, sim_reports.pr_report_id) pr_report_id 
    FROM DT858
    LEFT JOIN (SELECT
               DI008413 pr_id,
               max(DI008416) pr_report_id
               FROM DT823
               where simulation_report = 1
               group by DI008413
               ) sim_reports on (DT858.DI023101 = sim_reports.pr_id)    
  ),
  external_projects AS (
    SELECT
      pf.DI028667 pf_id,
      pf_pr.DI023102 other_pf_id,
      pf_pr.DI023101 pr_id,
      pf_pr.DI062360 pr_report_id
    FROM DT870 pf
    LEFT JOIN
    DT858 pf_pr ON NOT EXISTS(
      SELECT 1
      FROM DT858 pf_pr_sub
      WHERE DI023102 = pf.DI028667
      AND pf_pr.DI023101 = pf_pr_sub.DI023101
    ) 
    JOIN DT461 on (DT461.DI001001=pf_pr.DI023101)
    where DI001042 = 1
    UNION
    SELECT
      pf.DI028667 pf_id,
      '-' other_pf_id,
      pr_report.pr_id pr_id,
      pr_report.report_id report_id
    FROM DT870 pf
    LEFT JOIN (
      SELECT DI008413 pr_id,
             DI008416 report_id
      FROM DT823
      WHERE NOT EXISTS(
        SELECT 1
        FROM DT858
        WHERE DI023101 = DI008413
          and DI062360 = DI008416
      )
      AND DT823.SIMULATION_REPORT = 0    
      AND DI008413 != '-'
      AND DT823.DI029179 = 1
    ) pr_report ON 1=1
    JOIN DT461 on (DT461.DI001001=pr_report.pr_id)
    where DI001042 = 1
  ),
  res_pr_load AS (
    SELECT
      TRUNC(load.DI008523, 'MONTH') time_period,
      res.DI001218 res_id,
      load.DI008516 pr_id,
      report.DI008416 pr_report_id,
      SUM(load.DI008664) remaining,
      SUM(load.DI008662) actual
    FROM DT467 res
    JOIN DT827 load ON (res.DI001218 = load.DI008518)
    JOIN DT823 report on (load.DI008516 = report.DI008413
                      and load.DI008535 = report.DI008416)
    GROUP BY TRUNC(load.DI008523, 'MONTH'), res.DI001218, load.DI008516, report.DI008416
    )
  SELECT
    res_pr_load.time_period,
    res_pr_load.res_id,
    portfolio_pr_reports.pf_id,
    res_pr_load.pr_id pr_id,
    res_pr_load.pr_report_id pr_report_id,
    res_pr_load.remaining,
    res_pr_load.actual,
    0 is_external
  FROM res_pr_load
  LEFT JOIN portfolio_pr_reports ON (res_pr_load.pr_id = portfolio_pr_reports.pr_id
                         and res_pr_load.pr_report_id = portfolio_pr_reports.pr_report_id)
  UNION
  SELECT
    res_pr_load.time_period,
    res_pr_load.res_id,
    external_projects.pf_id,
    NULL pr_id,
    NULL pr_report_id,
    SUM(res_pr_load.remaining),
    SUM(res_pr_load.actual),
    1 is_external
  FROM res_pr_load
  JOIN external_projects ON res_pr_load.pr_id = external_projects.pr_id
                 and res_pr_load.pr_report_id = external_projects.pr_report_id
  GROUP BY res_pr_load.time_period, res_pr_load.res_id, external_projects.pf_id
) res_pr_pf_load
LEFT JOIN (
  SELECT
    DI008413 pr_id,
    DI008416 pr_report_id,
    DI008578 main_pr_id,
    DI008414 parent_pr_id,
    DI008589 pr_req_end,
    DI008591 pr_calc_end,
    DI057531 pr_func_id,
    DI062292 pr_type,
    DI029164 pr_code
  FROM DT823
) pr_report ON (res_pr_pf_load.pr_id = pr_report.pr_id
            and res_pr_pf_load.pr_report_id = pr_report.pr_report_id)
LEFT JOIN (
   SELECT
     Di001001 pr_id,
     DI001015 pr_prio
   FROM DT461
) pr on (res_pr_pf_load.pr_id = pr.pr_id) 
join (SELECT
      res.DI001218 parent_res_id,
      NVL(sub_res.DI001218, res.DI001218) child_res
      FROM DT467 res
      LEFT JOIN DT467 sub_res ON res.DI000270 IS NOT NULL AND sub_res.DI000270 LIKE (res.DI000270 || '%')
      WHERE res.DI003414 = '2') parent_res on (parent_res.child_res = res_pr_pf_load.res_id)      
JOIN V_RES_CAPA res_cap ON res_pr_pf_load.time_period = res_cap.time_period
AND res_pr_pf_load.res_id = res_cap.res_id AND res_cap.sum_type = 'M';


CREATE OR REPLACE VIEW "V_RES_PR_LOAD_SIMULATION" ("PF_ID", "PR_ID", "MAIN_PR_ID", "PARENT_PR_ID", "PR_FUNC_ID",
"PR_REPORT_ID", "PR_PRIO", "RES_ID", "TIME_PERIOD", "PR_REQ_END", "PR_CALC_END", "PR_TYPE", "REMAINING", "ACTUAL", "AVAILABILITY", "BASELOAD") AS
  select
pf_id,
pr_id,
main_pr_id,
parent_pr_id,
pr_func_id,
pr_report_id,
pr_prio,
parent_res_id as res_id,
time_period,
pr_req_end,
pr_calc_end,
pr_type,
sum(remaining) remaining,
sum(actual) actual,
sum(availability) "availability",
sum(baseload) baseload
from
v_res_pr_load_portfolio v_res
group by pf_id, pr_id, main_pr_id, parent_pr_id, pr_func_id, pr_report_id, pr_prio, 
         parent_res_id, time_period, pr_req_end, pr_calc_end, pr_type;
