CREATE UNIQUE INDEX IDX_DT861_DI023278 ON DT861 (DI023119, DI023062, DI023278);/

CREATE TABLE "PROJECT_STRUCTURE"
  ("MAIN_PROJECT" VARCHAR2(18 CHAR),
   "SUB_PROJECT" VARCHAR2(18 CHAR)
);/

ALTER TABLE "PROJECT_STRUCTURE" ADD PRIMARY KEY ("MAIN_PROJECT", "SUB_PROJECT");/

create or replace PROCEDURE P_INSERT_PROJECT_STRUC(p_project_id VARCHAR2)
IS pragma autonomous_transaction;
BEGIN
      insert into project_structure (
         select * from (
         select p_project_id main_pr, DI001001 sub_pr
          from DT461
         start with di001003 = p_project_id
         connect by prior di001001=di001003
           union
         select p_project_id main_pr, p_project_id sub_pr
          from dual
         )
         where not exists (select 1 from project_structure pr_struct 
                            where pr_struct.main_project=main_pr and pr_struct.sub_project=sub_pr)
         );
     commit;
END;
;/

CREATE OR REPLACE TRIGGER PLANTA_ON_UPDATE_DT461
AFTER UPDATE
   ON DT461
FOR EACH ROW
DECLARE 
    pragma autonomous_transaction;
BEGIN   
    delete from project_structure
        where sub_project in (select DI001001 from DT461 where DI001052 = :new.DI001052)
        or main_project in (select DI001001 from DT461 where DI001052 = :new.DI001052);
    commit;
    
    for rec in (select di001001 from dt461 where di001052=:new.DI001052) loop
        p_insert_project_struc(rec.di001001);
    end loop;
    
END;
;/

CREATE OR REPLACE TRIGGER PLANTA_ON_INSERT_DT461
AFTER INSERT
   ON DT461
FOR EACH ROW
BEGIN
    p_insert_project_struc(:new.DI001001);
END;
;/

CREATE OR REPLACE TRIGGER PLANTA_ON_DELETE_DT461
AFTER DELETE
   ON DT461
FOR EACH ROW
DECLARE pragma autonomous_transaction;
BEGIN
    delete from project_structure where sub_project = :old.DI001001;
    commit;
END;
;/
