scorecard-war/alter_table.sql

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;



-- DROP TRIGGER TRG_ACTION_PLAN_AVG;