create sequence PROVEEDORES_SEQ maxvalue 9999999999 ; create sequence USERS_SEQ maxvalue 9999999999 ; create sequence KPIS_SEQ maxvalue 9999999999 ; create sequence CATEGORYS_SEQ maxvalue 9999999999 ; create sequence AREAS_SEQ maxvalue 9999999999 ; create sequence EV_DETAIL_SEQ maxvalue 9999999999 ; create sequence USERPROVEEDOR_SEQ maxvalue 9999999999 ; create sequence NOTIFICATION_SEQ maxvalue 9999999999 ; create sequence FILE_SEQ maxvalue 9999999999 ; create sequence EVALUACION_SEQ maxvalue 9999999999 ; create sequence EVALUACION_PROVEEDOR_SEQ ; create sequence EVALUACION_DET_COMMENTS_SEQ maxvalue 9999999999 ; create sequence SCORECARD_PROVIDER_SEQ maxvalue 9999999999 ; create sequence EVALUACION_DET_SEQ maxvalue 9999999999 ; create sequence SCORECARD_PROVIDER_MONTH_SEQ maxvalue 9999999999 ; create sequence PLAN_ACCION_SEQ maxvalue 9999999999 ; create sequence PLAN_ACCION_SEQ_DET maxvalue 9999999999 ; create sequence PROVEEDORES_AGENDA_SEQ maxvalue 9999999999 ; create table PROVEEDORES ( ID NUMBER not null constraint TEST_PK primary key, CONTACTO NVARCHAR2(50), EMAIL NVARCHAR2(50), ESTADO NVARCHAR2(50), EVALUACION_ACTUAL NUMBER(12, 2), EVALUACION_GLOBAL NUMBER(12, 2), FECHA_CREACION DATE, FECHA_ACTUALIZACION DATE, CREADO_POR NVARCHAR2(50), TENDENCIA NVARCHAR2(50), NOMBRE NVARCHAR2(50), KPI_ID NUMBER ) ; create table ARCHIVOS ( ID NUMBER not null constraint ARCHIVOS_PK primary key, NOMBRE NVARCHAR2(100), RUTA NVARCHAR2(200), TIPO_ARCHIVO NVARCHAR2(50), ENTIDAD NVARCHAR2(50), ENTIDAD_ID NUMBER ) ; create table KPI ( ID NUMBER not null constraint KPI_PK primary key, NOMBRE NVARCHAR2(100) not null, CREATED_DATE DATE, UPDATED_DATE TIMESTAMP(6) ) ; create table AREAS ( ID NUMBER not null constraint AREAS_PK primary key, NOMBRE NVARCHAR2(100), META NUMBER, CREATED_DATE DATE, CREATED_BY NVARCHAR2(50), UPDATED_BY NVARCHAR2(50), UPDATED_DATE DATE, KPI_ID NUMBER not null constraint AREAS_KPI_FK references KPI ) ; create table CATEGORIAS ( ID NUMBER not null constraint CATEGORIAS_PK primary key, NOMBRE NVARCHAR2(50), META_MENSUAL NUMBER, META_ANUAL NUMBER, CREATED_DATE DATE, UPDATED_DATE DATE, CREATED_BY NVARCHAR2(50), UPDATED_BY NVARCHAR2(50), AREAS_ID NUMBER not null constraint CATEGORIAS_AREAS_FK references AREAS ) ; create table EVALUACION ( ID NUMBER not null constraint EVALUACION_PK primary key, META NUMBER not null, CALIFICACION NUMBER(10, 2), ESTADO NVARCHAR2(50), YEAR NVARCHAR2(50), CALIFICACION_ANTERIOR NUMBER(10, 2), TENDENCIA NVARCHAR2(50), GLOBAL NUMBER(10, 2), CREATED_DATE DATE, UPDATED_DATE DATE, CREATED_BY NVARCHAR2(50), UPDATED_BY NVARCHAR2(50), PROVEEDORES_ID NUMBER not null constraint EVALUACION_PROVEEDORES_FK references PROVEEDORES, AREAS_ID NUMBER not null constraint EVALUACION_AREAS_FK references AREAS, EVALUACION_PROVEEDOR_ID NUMBER ) ; create table EVALUACION_DET ( ID NUMBER not null constraint EVALUACION_DET_PK primary key, COMENTARIO NVARCHAR2(50), CM_USER_UPDATE NVARCHAR2(50), VISIBLE_PROVEEDOR CHAR(1 char), CALIFICACION_ANTERIOR NUMBER(12, 2), CALIFICACION_ACTUAL NUMBER(12, 2), CALIFICACION_GLOBAL NUMBER(12, 2), MES DATE, CREATED_BY NVARCHAR2(50) default NULL, UPDATED_BY NVARCHAR2(50) default NULL, CREATED_DATE DATE, UPDATED_DATE DATE, EVALUACION_ID NUMBER not null constraint EVALUACION_DET_EVALUACION_FK references EVALUACION, CATEGORIAS_ID NUMBER not null constraint EVALUACION_DET_CATEGORIAS_FK references CATEGORIAS ) ; create table EVALUACION_DET_COMMENTARIOS ( ID NUMBER not null constraint EVALUACION_DET_COMMENTARIOS_PK primary key, COMENTARIO NVARCHAR2(300), CREATED_BY NVARCHAR2(50), CREATED_DATE DATE, UPDATED_BY NVARCHAR2(50), CM_USUARIO_MODIFICO NVARCHAR2(50), UPDATED_DATE DATE, EVALUACION_DET_ID NUMBER not null constraint EVAL_DET_COMM_FK references EVALUACION_DET ) ; create table PLAN_ACCION ( ID NUMBER not null constraint PLAN_ACCION_PK primary key, OBJETIVO NVARCHAR2(200), ESTADO NVARCHAR2(50), MES DATE, CREATED_BY NVARCHAR2(50), CREATED_DATE DATE, UPDATED_BY NVARCHAR2(50), UPDATED_DATE DATE, FECHA_COMPROMISO DATE, AVANCE_PROVEEDOR NUMBER(12, 2), AVANCE_CM NUMBER(12, 2), NOMBRE VARCHAR2(100), PROVEEDOR_ID NUMBER constraint PLAN_ACCION_PROVEE__FK references PROVEEDORES, TIENE_EVENTO_NUEVO NUMBER default NULL, MONTH VARCHAR2(25), YEAR VARCHAR2(25) ) ; create table PLAN_ACCION_DET ( ID NUMBER not null constraint PLAN_ACCION_DET_PK primary key, COMENTARIO NVARCHAR2(300), CREATED_DATE DATE, CREATED_BY NVARCHAR2(50), UPDATED_BY NVARCHAR2(50), ESTADO NVARCHAR2(50), PLAN_ACCION_ID NUMBER not null constraint PLAN_ACCION_DET_PLAN_ACCION_FK references PLAN_ACCION, DESCRIPCION VARCHAR2(500), FECHA_COMPROMISO DATE, AVANCE NUMBER(12, 2), NOMBRE VARCHAR2(100) ) ; create table PLAN_ACCION_COMMENTARIOS ( ID NUMBER not null constraint PLAN_ACCION_COMMENTARIOS_PK primary key, COMENTARIO NVARCHAR2(300), CREATED_BY NVARCHAR2(50), CREATED_DATE DATE, UPDATED_BY NVARCHAR2(50), CM_USUARIO_MODIFICO NVARCHAR2(50), UPDATED_DATE DATE, PLAN_ACCION_DET_ID NUMBER not null constraint PLAN_ACCION_DET_COM_FK references PLAN_ACCION_DET ) ; create table ROLES_PERMISOS ( ID NUMBER not null constraint ROLES_PERMISOS_PK primary key, ROLE_NAME NVARCHAR2(50), DESCRIPCION NVARCHAR2(100), CREATED_DATE DATE ) ; create table USERS ( ID NUMBER not null constraint USERS_PK primary key, USER_NAME NVARCHAR2(50), PASSWORD NVARCHAR2(200), USER_TYPE NVARCHAR2(100), EMAIL NVARCHAR2(100), STATUS CHAR, ENABLED CHAR, FIRST_NAME VARCHAR2(100), LAST_NAME VARCHAR2(100) ) ; create table USERS_PROVEEDORES ( ID NUMBER not null constraint USERS_PROVEEDORES_PK primary key, CREATED_DATE DATE, UPDATED_DATE DATE, PROVEEDORES_ID NUMBER not null constraint USERS_PROVES_PROVE_FK references PROVEEDORES, USERS_ID NUMBER not null constraint USERS_PROVEEDORES_USERS_FK references USERS ) ; create table USERS_ROLES ( ID NUMBER not null constraint USERS_ROLES_PK primary key, ROLES_PERMISOS_ID NUMBER not null constraint USERS_ROLES_ROLES_PERMISOS_FK references ROLES_PERMISOS, USERS_ID NUMBER not null constraint USERS_ROLES_USERS_FK references USERS ) ; create table NOTIFICATION_TEMPLATE ( ID NUMBER(19) not null primary key, CREATED_BY VARCHAR2(30 char), CREATED_ON TIMESTAMP(6), UPDATED_BY VARCHAR2(30 char), UPDATED_ON TIMESTAMP(6), VERSION NUMBER(19), BODY LONG, NAME VARCHAR2(255 char), SCOPE NUMBER(10), STATUS NUMBER(5), SUBJECT VARCHAR2(255 char), TYPE VARCHAR2(20 char) ) ; create table FILE_ITEM ( ID NUMBER(19) not null primary key, BUCKET_NAME VARCHAR2(255 char), CREATED_BY VARCHAR2(255 char) not null, CREATED_DATE TIMESTAMP(6) not null, DESCRIPTION VARCHAR2(255 char), ENTITY_TYPE VARCHAR2(255 char) not null, FILE_NAME VARCHAR2(255 char), FILE_PATH VARCHAR2(255 char), FILE_SIZE NUMBER(19), IDENTIFIER NUMBER(19) not null, IS_PUBLIC NUMBER(1) default NULL, MIME_TYPE VARCHAR2(255 char), SCOPE VARCHAR2(255 char) not null ) ; create table EVALUACION_PROVEEDOR ( ID NUMBER(19, 2) not null primary key, CALIFICACION NUMBER(19, 2), CALIFICACION_ANTERIOR NUMBER(19, 2), CREATED_BY VARCHAR2(255 char), CREATED_DATE TIMESTAMP(6), ESTADO VARCHAR2(255 char), GLOBAL NUMBER(19, 2), META NUMBER(19, 2) not null, MONTH VARCHAR2(255 char), TENDENCIA VARCHAR2(255 char), UPDATED_BY VARCHAR2(255 char), UPDATED_DATE TIMESTAMP(6), YEAR VARCHAR2(255 char), AREAS_ID NUMBER(19) not null ) ; create table SCORECARD_PROVIDER ( ID NUMBER not null constraint SCORECARD_PROVIDER_PK primary key, NAME NVARCHAR2(100), LAST_SCORE NUMBER(5, 2), BEFORE_SCORE NUMBER(5, 2), TOTAL_AVG NUMBER(5, 2), TARGET NUMBER(5), YEAR NVARCHAR2(10), CREATED_DATE DATE, UPDATED_DATE DATE, PROVEEDORES_ID NUMBER not null constraint SCORECARD_PROVIDER_PROV_FK references PROVEEDORES, MONTH VARCHAR2(15), STATUS VARCHAR2(50) ) ; create table SCORECARD_PROVIDER_MONTH ( ID NUMBER not null constraint SCORECARD_PROVIDER_MONTH_PK primary key, MONTH NVARCHAR2(50), SCORE NUMBER(5, 2), BEFORE_SCORE NUMBER(5, 2), CREATED_DATE DATE, UPDATED_DATE DATE, CREATED_BY NVARCHAR2(50), SCORECARD_PROVIDER_ID NUMBER not null constraint SCORECARD_MONTH_PROV_FK references SCORECARD_PROVIDER, SCORE_CATEGORY NUMBER(5, 2), BEFORE_SCORE_CATEGORY NUMBER(5, 2), NOMBRE_CATEGORIA VARCHAR2(100), NOMBRE_AREA VARCHAR2(100), ESTADO VARCHAR2(50), ID_AREA NUMBER, ID_CATEGORIA NUMBER ) ; create table LOG ( LINE NVARCHAR2(100) ) ; create table SCORECARD_PROVIDER_COMMENTS ( ID NUMBER default NULL not null constraint SCORECARD_PROVIDER_COMMENTS_PK primary key, COMMENTS VARCHAR2(1000), CREATED_DATE DATE, CREATED_USER VARCHAR2(50), SCORECARD_PROVIDER_ID NUMBER constraint SCORE_PRO_COMMS_FK references SCORECARD_PROVIDER ) ; create table PROVEEDOR_AGENDA ( ID NUMBER not null primary key, TITULO NVARCHAR2(200), OBJETIVO NVARCHAR2(200), ES_RELEVANTE NUMBER, ATENDERA_PROVEEDOR NUMBER, FECHA_REUNION_INICIO DATE, FECHA_REUNION_FIN DATE, CREATED_BY NVARCHAR2(50), CREATED_DATE DATE, UPDATED_BY NVARCHAR2(100), PROVEEDOR_ID NUMBER constraint PROV_AGENDA_PROV_ID_FK references PROVEEDORES, YEAR NUMBER, ESTADO NVARCHAR2(30), MINUTA NVARCHAR2(500) ) ; create table AUTHORITIES ( ID NUMBER not null primary key, USERNAME NVARCHAR2(50), AUTHORITY NVARCHAR2(100) ) ; CREATE OR REPLACE PROCEDURE dashboard_generateScoreYear(pid_evaluacion IN NUMBER, result_code OUT NUMBER) AS paa_id INT := 0; CURSOR c1 IS SELECT e.id AS id_evaluacion, e.meta AS meta_evaluacion, e.CALIFICACION, e.CALIFICACION_ANTERIOR, a.id AS id_area, a.nombre AS nombre_area, a.meta meta_area, a.kpi_id, k.NOMBRE AS nombre_kpi, c.id AS id_categoria, c.nombre AS nombre_categoria FROM EVALUACION e JOIN areas a ON e.AREAS_ID = a.ID JOIN kpi k ON a.KPI_ID = k.id LEFT JOIN categorias c ON a.id = c.AREAS_ID; BEGIN result_code := 0; FOR custom IN c1 LOOP FOR i IN 1..11 LOOP INSERT INTO EVALUACION_DET (ID, COMENTARIO, CM_USER_UPDATE, VISIBLE_PROVEEDOR, CALIFICACION_ANTERIOR, CALIFICACION_ACTUAL, CALIFICACION_GLOBAL, MES, CREATED_BY, UPDATED_BY, CREATED_DATE, UPDATED_DATE, EVALUACION_ID, CATEGORIAS_ID) VALUES (EV_DETAIL_SEQ.nextval, 'inicial', 'admin', 's', 4.00, 5.00, 6.00, TO_DATE('2019-' || i || '-25 19:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'admin', 'admin', current_date, current_date, custom.id_evaluacion, custom.id_categoria); END LOOP; END LOOP; result_code := 0; -- COMMIT; EXCEPTION WHEN OTHERS THEN result_code := -1; ROLLBACK; RAISE; END; ; CREATE OR REPLACE PROCEDURE dash_global_by_kpi(pid_kpi IN NUMBER, result_code OUT NUMBER) AS paa_id INT := 0; BEGIN result_code := 0; MERGE INTO evaluacion_det orig USING (SELECT k.id AS kpi_id, k.nombre kpi_nombre, e.id eval_id, e.CALIFICACION, e.global, e.AREAS_ID, a.nombre area_nombre, a.meta, c.ID AS categorias_id, c.nombre cat_nombre, c.META_MENSUAL, (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria, ed.ID detail_id, to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number, ed.CALIFICACION_ANTERIOR, ed.CALIFICACION_ACTUAL, ed.CALIFICACION_GLOBAL, ed.mes fecha FROM EVALUACION_DET ed JOIN EVALUACION e ON ed.EVALUACION_ID = e.id JOIN areas a ON e.AREAS_ID = a.id JOIN categorias c ON ed.CATEGORIAS_ID = c.id JOIN kpi k ON a.KPI_ID = k.id AND k.id = pid_kpi) detail ON (orig.id = detail.detail_id) WHEN MATCHED THEN UPDATE SET orig.CALIFICACION_GLOBAL = detail.global_categoria; COMMIT; EXCEPTION WHEN OTHERS THEN result_code := -1; ROLLBACK; RAISE; END; ; CREATE OR REPLACE PROCEDURE dash_global_by_kpi_month(pid_kpi IN NUMBER, pmes IN NUMBER, result_code OUT NUMBER) AS BEGIN result_code := 0; MERGE INTO evaluacion_det orig USING (SELECT k.id AS kpi_id, k.nombre kpi_nombre, e.id eval_id, e.CALIFICACION, e.global, e.AREAS_ID, a.nombre area_nombre, a.meta, c.ID AS categorias_id, c.nombre cat_nombre, c.META_MENSUAL, (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria, ed.ID detail_id, to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number, ed.CALIFICACION_ANTERIOR, ed.CALIFICACION_ACTUAL, ed.CALIFICACION_GLOBAL, ed.mes fecha FROM EVALUACION_DET ed JOIN EVALUACION e ON ed.EVALUACION_ID = e.id JOIN areas a ON e.AREAS_ID = a.id JOIN categorias c ON ed.CATEGORIAS_ID = c.id JOIN kpi k ON a.KPI_ID = k.id AND k.id = pid_kpi WHERE to_number(EXTRACT(MONTH FROM ed.mes), 99) = pmes) detail ON (orig.id = detail.detail_id) WHEN MATCHED THEN UPDATE SET orig.CALIFICACION_GLOBAL = detail.global_categoria; COMMIT; EXCEPTION WHEN OTHERS THEN result_code := -1; ROLLBACK; RAISE; END; ; CREATE OR REPLACE PROCEDURE d_eval_global_month(pid_kpi IN NUMBER, pmes IN NUMBER, result_code OUT NUMBER) AS paa_id INT := 0; BEGIN result_code := 0; MERGE INTO evaluacion orig USING (SELECT k.id id_kpi, e.id id_eval, a.id, avg(ed.CALIFICACION_GLOBAL) AS average FROM EVALUACION_DET ed JOIN EVALUACION e ON ed.EVALUACION_ID = e.id JOIN areas a ON e.AREAS_ID = a.id JOIN categorias c ON ed.CATEGORIAS_ID = c.id JOIN kpi k ON a.KPI_ID = k.id AND k.id = pid_kpi WHERE to_number(EXTRACT(MONTH FROM ed.mes), 99) = pmes GROUP BY k.id, e.id, a.id, to_number(EXTRACT(MONTH FROM ed.mes), 99)) detail ON (orig.id = detail.id_eval) WHEN MATCHED THEN UPDATE SET orig.GLOBAL = detail.average; COMMIT; EXCEPTION WHEN OTHERS THEN result_code := -1; ROLLBACK; RAISE; END; ; CREATE OR REPLACE PROCEDURE get_detail_month(pkpi_id IN NUMBER, pmes IN NUMBER, p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR SELECT k.id AS kpi_id, k.nombre kpi_nombre, e.id eval_id, e.global global_actual, e.AREAS_ID, a.nombre area_nombre, c.ID AS categorias_id, c.nombre cat_nombre, c.META_MENSUAL, (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria, ed.ID detail_id, to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number, ed.CALIFICACION_ACTUAL, ed.CALIFICACION_GLOBAL FROM EVALUACION_DET ed JOIN EVALUACION e ON ed.EVALUACION_ID = e.id JOIN areas a ON e.AREAS_ID = a.id JOIN categorias c ON ed.CATEGORIAS_ID = c.id JOIN kpi k ON a.KPI_ID = k.id AND k.id = pkpi_id WHERE to_number(EXTRACT(MONTH FROM ed.mes), 99) = pmes ORDER BY k.id, e.id, a.id, c.id, to_number(EXTRACT(MONTH FROM ed.mes), 99); END; ; CREATE OR REPLACE PROCEDURE get_detail_global(pkpi_id IN NUMBER, p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR SELECT k.id AS kpi_id, k.nombre kpi_nombre, e.id eval_id, e.global global_actual, e.AREAS_ID, a.nombre area_nombre, c.ID AS categorias_id, c.nombre cat_nombre, c.META_MENSUAL, (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria, ed.ID detail_id, to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number, ed.CALIFICACION_ACTUAL, ed.CALIFICACION_GLOBAL FROM EVALUACION_DET ed JOIN EVALUACION e ON ed.EVALUACION_ID = e.id JOIN areas a ON e.AREAS_ID = a.id JOIN categorias c ON ed.CATEGORIAS_ID = c.id JOIN kpi k ON a.KPI_ID = k.id AND k.id = pkpi_id ORDER BY k.id, e.id, a.id, c.id, to_number(EXTRACT(MONTH FROM ed.mes), 99); END; ; CREATE OR REPLACE PROCEDURE get_detail_global_month(pkpi_id IN NUMBER, p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR SELECT kpi_id, mes, avg(promedio_area) AS promedio FROM (SELECT k.id AS kpi_id, e.id eval_id, a.id areas_id, to_number(EXTRACT(MONTH FROM ed.mes), 99) mes, avg(ed.CALIFICACION_GLOBAL) promedio_area FROM EVALUACION_DET ed JOIN EVALUACION e ON ed.EVALUACION_ID = e.id JOIN areas a ON e.AREAS_ID = a.id JOIN categorias c ON ed.CATEGORIAS_ID = c.id JOIN kpi k ON a.KPI_ID = k.id AND k.id = pkpi_id GROUP BY k.id, e.id, a.id, to_number(EXTRACT(MONTH FROM ed.mes), 99)) GROUP BY kpi_id, mes ORDER BY kpi_id, mes; END; ; create OR REPLACE PROCEDURE pc_create_up_scorecard(p_provider_id IN INTEGER, p_year IN INTEGER, p_month IN INTEGER) IS CURSOR cur_score_cate (p_provider_id IN scorecard_PROVIDER.PROVEEDORES_ID%TYPE, p_month IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE, p_year IN scorecard_PROVIDER.YEAR%TYPE) is SELECT to_number(EXTRACT(MONTH FROM ed.mes), 99) mes, to_number(EXTRACT(YEAR FROM ed.mes), 9999) YEAR, p.NOMBRE,a.NOMBRE,c.NOMBRE, p.id, a.ID,c.id, avg(ed.CALIFICACION_ACTUAL) promedio_area FROM EVALUACION_DET ed JOIN EVALUACION e ON ed.EVALUACION_ID = e.id JOIN areas a ON e.AREAS_ID = a.id JOIN categorias c ON ed.CATEGORIAS_ID = c.id JOIN kpi k ON a.KPI_ID = k.id JOIN PROVEEDORES P on e.PROVEEDORES_ID = P.ID WHERE p.id = p_provider_id AND to_number(EXTRACT(MONTH FROM ed.mes), 99) = p_month AND to_number(EXTRACT(year FROM ed.mes), 9999) = p_year GROUP BY to_number(EXTRACT(MONTH FROM ed.mes), 99), to_number(EXTRACT(YEAR FROM ed.mes), 9999), p.NOMBRE,a.NOMBRE,c.NOMBRE, p.id, a.ID,c.id order by mes; CURSOR cur_score_area ( p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE, p_month SCORECARD_PROVIDER_MONTH.MONTH%TYPE, p_year scorecard_PROVIDER.YEAR%TYPE, p_area_id IN scorecard_PROVIDER.YEAR%TYPE) is SELECT avg(ed.CALIFICACION_ACTUAL) FROM EVALUACION_DET ed JOIN EVALUACION e ON ed.EVALUACION_ID = e.id JOIN areas a ON e.AREAS_ID = a.id JOIN categorias c ON ed.CATEGORIAS_ID = c.id JOIN PROVEEDORES P on e.PROVEEDORES_ID = P.ID WHERE p.id = p_provider_id AND to_number(EXTRACT(MONTH FROM ed.mes), 99) = p_month AND to_number(EXTRACT(year FROM ed.mes), 9999) = p_year and a.id = p_area_id; CURSOR cur_score_global (p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE, p_month SCORECARD_PROVIDER_MONTH.MONTH%TYPE, p_year scorecard_PROVIDER.YEAR%TYPE) is select avg(promedio_categoria),NOMBRE from (SELECT e.id eval_id, c.id categoria_id, to_number(EXTRACT(MONTH FROM ed.mes), 99) mes, to_number(EXTRACT(YEAR FROM ed.mes), 9999) YEAR, p.NOMBRE, p.id provedorId, avg(ed.CALIFICACION_ACTUAL) promedio_categoria FROM EVALUACION_DET ed JOIN EVALUACION e ON ed.EVALUACION_ID = e.id JOIN categorias c ON ed.CATEGORIAS_ID = c.id JOIN PROVEEDORES P on e.PROVEEDORES_ID = P.ID where p.id = p_provider_id and to_number(EXTRACT(MONTH FROM ed.mes), 99) = p_month AND to_number(EXTRACT(year FROM ed.mes), 9999) = p_year GROUP BY e.id, c.id, p.nombre, p.id, to_number(EXTRACT(MONTH FROM ed.mes), 99), to_number(EXTRACT(YEAR FROM ed.mes), 9999)) group by YEAR, NOMBRE, provedorId; --mes, CURSOR cur_score_year ( p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE, p_month IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE, p_year scorecard_PROVIDER.YEAR%TYPE) is select p.id, p.LAST_SCORE from SCORECARD_PROVIDER p where p.PROVEEDORES_ID = p_provider_id and p.YEAR = p_year and p.MONTH = p_month; CURSOR cur_quiz_comments (p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE, p_month SCORECARD_PROVIDER_MONTH.MONTH%TYPE, p_year scorecard_PROVIDER.YEAR%TYPE) is select d.id,d.comentario,d.CREATED_DATE,d.CREATED_BY from EVALUACION_DET_COMMENTARIOS d join evaluacion_det e on e.id =d.EVALUACION_DET_ID join evaluacion x on x.id = e.EVALUACION_ID and x.PROVEEDORES_ID = p_provider_id and to_number(EXTRACT(MONTH FROM e.mes), 99) = p_month AND to_number(EXTRACT(year FROM e.mes), 9999) = p_year; L_NAME scorecard_PROVIDER.NAME%TYPE; L_LAST_SCORE_AREA SCORECARD_PROVIDER_MONTH.SCORE%TYPE; L_LAST_SCORE scorecard_PROVIDER.LAST_SCORE%TYPE; -- CATGORIAS L_BEFORE_SCORE scorecard_PROVIDER.BEFORE_SCORE%TYPE; L_TOTAL_AVG scorecard_PROVIDER.TOTAL_AVG%TYPE; L_TARGET scorecard_PROVIDER.TARGET%TYPE:=5; L_YEAR scorecard_PROVIDER.YEAR%TYPE:=p_year; L_CREATED_DATE scorecard_PROVIDER.CREATED_DATE%TYPE; L_PROVEEDORES_ID scorecard_PROVIDER.PROVEEDORES_ID%TYPE:=p_provider_id; L_SCORECARD_PROVIDER SCORECARD_PROVIDER.PROVEEDORES_ID%TYPE; L_MONTH SCORECARD_PROVIDER_MONTH.MONTH%TYPE:=p_month; L_SCORECARD_PROVIDER_ID SCORECARD_PROVIDER.ID%TYPE; L_NOMBRE_AREA scorecard_PROVIDER_MONTH.NOMBRE_AREA%TYPE; L_NOMBRE_CATEGORIA scorecard_PROVIDER_MONTH.NOMBRE_CATEGORIA%TYPE; L_ID_AREA AREAS.ID%TYPE; L_ID_CATEGORIA CATEGORIAS.ID%TYPE; L_COMMENT EVALUACION_DET_COMMENTARIOS.COMENTARIO%TYPE; L_COMMENT_ID EVALUACION_DET_COMMENTARIOS.ID%TYPE; L_COMMENT_CREATED_DATE EVALUACION_DET_COMMENTARIOS.CREATED_DATE%TYPE; L_CREATED_BY EVALUACION_DET_COMMENTARIOS.CREATED_BY%TYPE; BEGIN OPEN cur_score_global(p_provider_id,p_month,p_year); FETCH cur_score_global INTO L_TOTAL_AVG,L_NAME; CLOSE cur_score_global; OPEN cur_score_year (p_provider_id,p_month,p_year); FETCH cur_score_year INTO L_SCORECARD_PROVIDER_ID, L_LAST_SCORE; CLOSE cur_score_year; if L_SCORECARD_PROVIDER_ID > 0 then UPDATE SCORECARD_PROVIDER SET NAME = L_NAME, LAST_SCORE = L_TOTAL_AVG, BEFORE_SCORE = L_BEFORE_SCORE, TOTAL_AVG = L_TOTAL_AVG, TARGET = L_TARGET, YEAR = L_YEAR, UPDATED_DATE = CURRENT_DATE WHERE ID = L_SCORECARD_PROVIDER_ID; else L_SCORECARD_PROVIDER_ID := scorecard_provider_seq.nextval; INSERT INTO SCORECARD_PROVIDER (ID, NAME, LAST_SCORE, BEFORE_SCORE, TOTAL_AVG, TARGET, YEAR, CREATED_DATE, PROVEEDORES_ID, STATUS, MONTH) values( L_SCORECARD_PROVIDER_ID, L_NAME, L_TOTAL_AVG, 0, L_TOTAL_AVG,--L_LAST_SCORE, L_TARGET,--L_BEFORE_SCORE, p_year, CURRENT_DATE, p_provider_id, 'NO INICIADO', p_month) ; end if; OPEN cur_score_cate(p_provider_id, p_month, p_year); LOOP FETCH cur_score_cate INTO L_MONTH,L_YEAR, L_NAME,L_NOMBRE_AREA,L_NOMBRE_CATEGORIA,L_PROVEEDORES_ID,L_ID_AREA,L_ID_CATEGORIA, L_LAST_SCORE; EXIT WHEN cur_score_cate%NOTFOUND OR cur_score_cate%NOTFOUND IS NULL; open cur_score_area(p_provider_id, p_month, p_year,L_ID_AREA);--nota agrupada de las categorias al area FETCH cur_score_area INTO L_LAST_SCORE_AREA; CLOSE cur_score_area; INSERT INTO SCORECARD_PROVIDER_MONTH (ID, MONTH, SCORE, BEFORE_SCORE, SCORE_CATEGORY, BEFORE_SCORE_CATEGORY, CREATED_DATE, SCORECARD_PROVIDER_ID, ESTADO, NOMBRE_AREA, NOMBRE_CATEGORIA, ID_AREA,ID_CATEGORIA) VALUES (scorecard_provider_month_seq.nextval, L_MONTH, L_LAST_SCORE_AREA, L_LAST_SCORE_AREA, L_LAST_SCORE, L_LAST_SCORE, CURRENT_DATE, L_SCORECARD_PROVIDER_ID, 'NO INICIADO', L_NOMBRE_AREA, L_NOMBRE_CATEGORIA, L_ID_AREA,L_ID_CATEGORIA); END LOOP; close cur_score_cate; OPEN cur_quiz_comments (p_provider_id,p_month,p_year); LOOP FETCH cur_quiz_comments INTO L_COMMENT_ID,L_COMMENT,L_COMMENT_CREATED_DATE,L_CREATED_BY; EXIT WHEN cur_quiz_comments%NOTFOUND OR cur_quiz_comments%NOTFOUND IS NULL; INSERT INTO SCORECARD_PROVIDER_COMMENTS (ID, COMMENTS, CREATED_DATE, CREATED_USER, SCORECARD_PROVIDER_ID) VALUES (L_COMMENT_ID, L_COMMENT, L_COMMENT_CREATED_DATE, L_CREATED_BY, L_SCORECARD_PROVIDER_ID ); END LOOP ; CLOSE cur_quiz_comments; END pc_create_up_scorecard; ; CREATE OR REPLACE PROCEDURE GET_RESUME_SCORE_AREA( p_scoreprovider_id IN SCORECARD_PROVIDER.ID%TYPE , p_cursor IN OUT SYS_REFCURSOR) IS BEGIN open p_cursor for select s.ID_AREA as idArea, s.nombre_area as name, s.score as score from scorecard_provider_month s where scorecard_provider_id = p_scoreprovider_id group by s.ID_AREA, s.nombre_area, s.score; -- END GET_RESUME_SCORE_AREA; ; CREATE OR REPLACE PROCEDURE GET_RESUME_LASTEST_MONTHS( p_provider_id IN SCORECARD_PROVIDER.ID%TYPE, p_year in INTEGER, p_cursor IN OUT SYS_REFCURSOR) IS BEGIN open p_cursor for select p.MONTH as name, (p.TOTAL_AVG / 5 * 100) as score from scorecard_provider p where p.proveedores_id = p_provider_id and p.year = p_year and p.month > 0 order by p.MONTH; END GET_RESUME_LASTEST_MONTHS; ; CREATE OR REPLACE PROCEDURE GET_SCOREPRO_CATEGORY_SCORES( p_scoreprovider_id IN SCORECARD_PROVIDER.ID%TYPE, p_area_id IN SCORECARD_PROVIDER_MONTH.ID_AREA%TYPE, p_cursor IN OUT SYS_REFCURSOR) IS BEGIN open p_cursor for select s.NOMBRE_CATEGORIA as nombre, s.NOMBRE_CATEGORIA as name, s.SCORE_CATEGORY as score from scorecard_provider_month s where s.scorecard_provider_id = p_scoreprovider_id and s.ID_AREA = p_area_id; --and p.status = 'APROBADO'; END GET_SCOREPRO_CATEGORY_SCORES; ; create OR REPLACE PROCEDURE P_GET_TREND_GLOBAL_MONTH(p_providerId IN NUMBER, p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR select * from scorecard_provider p where p.PROVEEDORES_ID = p_providerId and status ='PUBLICADO' order BY TO_NUMBER(p.YEAR), TO_NUMBER(p.MONTH) ASC; END; create OR REPLACE PROCEDURE P_GET_DETAIL_BY_PROVIDER( p_provider_id IN NUMBER, p_year in number, p_cursor IN OUT SYS_REFCURSOR ) AS BEGIN OPEN p_cursor FOR select 1 as kpi_id, m.nombre_area kpi_nombre, 1 as evalId, p.TOTAL_AVG global_actual, m.ID_AREA areas_id, m.NOMBRE_AREA area_nombre, m.ID_CATEGORIA categoria_id, m.NOMBRE_CATEGORIA cat_nombre, p.TARGET meta, (m.SCORE_CATEGORY / 5 ) global_categoria, 1 detail_id, m.MONTH mes_number, p.YEAR, m.SCORE calificacion_actual, p.LAST_SCORE calificacion_global from SCORECARD_PROVIDER p join SCORECARD_PROVIDER_MONTH m on m.SCORECARD_PROVIDER_ID = p.id where p.PROVEEDORES_ID = p_provider_id and p.TOTAL_AVG is not null and p.STATUS = 'PUBLICADO' order by TO_NUMBER(p.YEAR),TO_NUMBER(m.MONTH),m.ID_AREA,m.ID_CATEGORIA ; END; CREATE OR REPLACE PROCEDURE P_GET_PROGRESS_ACTION_PLAN( p_providerId IN NUMBER, p_year IN NUMBER, p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR SELECT SUM(AVANCE_CM)/COUNT(*) progress from PLAN_ACCION where PROVEEDOR_ID = p_providerId -- AND ESTADO = 'EN PROGRESO' -- AND YEAR = GROUP BY PROVEEDOR_ID; END; CREATE OR REPLACE PROCEDURE P_GET_ACTION_PLAN_STATUS( p_providerId IN NUMBER, p_year IN NUMBER, p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR select p.ESTADO,COUNT(*) qty from PLAN_ACCION p where PROVEEDOR_ID = p_providerId -- AND YEAR = group by p.ESTADO; END; create OR REPLACE PROCEDURE P_GET_GLOBAL_AVG( p_providerId IN NUMBER, p_year IN NUMBER, p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR select sum(last_score)/count(*) global_avg from SCORECARD_PROVIDER p where p.PROVEEDORES_ID =p_providerId and p.STATUS = 'PUBLICADO' AND p.year = p_year ; END; ; CREATE OR REPLACE PROCEDURE P_GET_PEN_QUIZ_USERS( p_cursor IN OUT SYS_REFCURSOR) as BEGIN OPEN p_cursor FOR select u.USER_NAME,u.USER_TYPE,p.nombre,p.id from proveedores p join USERS_PROVEEDORES up on up.PROVEEDORES_ID = p.id join users u on u.id = up.USERS_ID where UPPER(u.USER_TYPE) in ('CLIENTE INTERNO') and not exists( select 'x' from EVALUACION_PROVEEDOR ep where ep.CREATED_BY = u.USER_NAME and ep.year = to_char(add_months(sysdate, -1), 'YYYY') and ep.month = to_char(add_months(sysdate, -1), 'MM') ) ; end; ; CREATE OR REPLACE PROCEDURE P_GET_PEN_ACTION_EXECUTE( p_cursor IN OUT SYS_REFCURSOR) as BEGIN OPEN p_cursor FOR select * from PLAN_ACCION_DET a where a.ESTADO != 'FINALIZADO' and a.FECHA_COMPROMISO<=CURRENT_DATE; end; ; CREATE OR REPLACE PROCEDURE P_GET_HISTORIC_SCORE( p_provider_id in integer, p_year in integer, p_cursor IN OUT SYS_REFCURSOR) as BEGIN OPEN p_cursor FOR select p.year,pm.nombre_area,pm.nombre_categoria, '5' META, p.last_score Actual, (p.total_avg*100/5)||'%' Global, nvl(pm.enero,0) ENERO,nvl(pm.FEBRERO,0) Febrero,nvl(pm.marzo,0) Marzo, nvl(pm.abril,0) Abril,nvl(pm.mayo,0) Mayo, nvl(pm.junio,0) Junio,nvl(pm.julio,0) Julio, nvl(pm.AGOSTO,0) Agosto,nvl(pm.SEPTIEMBRE,0) Septiembre, nvl(pm.OCTUBRE,0) Octubre,nvl(pm.NOVIEMBRE,0) Noviembre,nvl(pm.DICIEMBRE,0) DICIEMBRE from ( select * from SOCRECARDNEW.SCORECARD_PROVIDER_MONTH PIVOT( sum(nvl(score,0)) FOR month IN ( '1' Enero,'2' Febrero,'3' Marzo,'4' Abril,'5' Mayo,'6' Junio,'7' Julio, '8' Agosto,'9' Septiembre,'10' Octubre,'11' Noviembre,'12' Diciembre ) )) pm join SOCRECARDNEW.SCORECARD_PROVIDER p on p.id = pm.scorecard_provider_id where p.proveedores_id = p_provider_id and p.year = p_year order by pm.nombre_area; end; ;