ALTER TABLE SCORECARD_PROVIDER ADD REJECT_REASON VARCHAR2(200) NULL;
ALTER TABLE PLAN_ACCION ADD PARENT_ID NUMBER(*) NULL;
ALTER TABLE PLAN_ACCION_DET ADD PARENT_ID number(*) NULL;

ALTER TABLE PLAN_ACCION_DET ADD RESPONSABLE_ID number(*) NULL;

ALTER TABLE PROVEEDORES ADD TARGET number(*) NULL;


ALTER TABLE SCORECARD_PROVIDER ADD CREATED_BY VARCHAR2(25) NULL;
ALTER TABLE SCORECARD_PROVIDER ADD UPDATED_BY VARCHAR2(25) NULL;

ALTER TABLE SCORECARD_PROVIDER_COMMENTS ADD UPDATED_BY VARCHAR2(50) NULL;


cREATE SEQUENCE score_provider_comments_seq
  MINVALUE 1
  MAXVALUE 9999999999
  START WITH 4
  INCREMENT BY 1;

cREATE SEQUENCE score_provider_workflow_seq
  MINVALUE 1
  MAXVALUE 9999999999
  START WITH 4
  INCREMENT BY 1;


CREATE TABLE workflow_log
(
    ID int PRIMARY KEY,
    entity_id int,
    entity_type varchar2(50),
    created_by varchar2(50),
    step varchar2(100),
    comments varchar2(1000),
    created_date date DEFAULT current_timestamp NOT NULL
);


CREATE OR REPLACE TRIGGER TRG_SCORE_EVALUCION_LOG
AFTER INSERT OR UPDATE OR DELETE ON SCORECARD_PROVIDER
FOR EACH ROW
DECLARE
    L_ID NUMBER(15);
BEGIN

    insert into WORKFLOW_LOG(id,ENTITY_ID,ENTITY_TYPE,CREATED_BY,STEP,CREATED_DATE,comments )
    VALUES(score_provider_workflow_seq.nextval,:new.id,'SCORECARD_PROVIDER',:new.UPDATED_BY,:new.STATUS,SYSDATE,:new.REJECT_REASON);


END;


ALTER TABLE EVALUACION_PROVEEDOR ADD SCORECARD_ID int NULL;




CREATE OR REPLACE TRIGGER TRG_ACTION_PLAN_AVG
AFTER INSERT OR UPDATE OR DELETE ON PLAN_ACCION_DET
FOR EACH ROW
DECLARE

    L_PLAN_ACCION_ID NUMBER(15);
    L_PLAN_PARENT_ID NUMBER(15);
    L_PLAN_ID NUMBER(15);
    L_PLAN_ACCION_DET_AVG NUMBER(15,2);
    L_PLAN_ACCION_AVG NUMBER(15,2);
    L_STATUS_PLAN varchar2(100);

  cursor cur_avg_det is
     select avg(AVANCE),PARENT_ID  from PLAN_ACCION_DET d where d.id=:NEW.id
      and PARENT_ID is not null
  group by PARENT_ID ;

  cursor cur_avg_plan is
     select avg(AVANCE),PLAN_ACCION_ID  from PLAN_ACCION_DET d where d.id=:NEW.id
      and PARENT_ID is null
    group by PLAN_ACCION_ID ;

BEGIN


      OPEN cur_avg_det;
       FETCH cur_avg_det INTO L_PLAN_ACCION_DET_AVG,L_PLAN_PARENT_ID;
      CLOSE cur_avg_det;



      UPDATE PLAN_ACCION_DET SET AVANCE = L_PLAN_ACCION_DET_AVG
       WHERE ID =L_PLAN_PARENT_ID;
--         where ID = :new.ID AND PARENT_ID IS NULL;


      OPEN cur_avg_plan;
       FETCH cur_avg_plan INTO L_PLAN_ACCION_AVG,L_PLAN_ID;
      CLOSE cur_avg_plan;

      UPDATE PLAN_ACCION SET AVANCE_PROVEEDOR = L_PLAN_ACCION_AVG
        where ID = L_PLAN_ID;

      IF L_PLAN_ACCION_AVG = 100 THEN
        UPDATE PLAN_ACCION SET ESTADO = 'FINALIZADO'
        where ID = L_PLAN_ID;
      ELSE
        UPDATE PLAN_ACCION SET ESTADO = 'EN PROGRESO'
        where ID = L_PLAN_ID;
      end if;

END;