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_evaluaciones ( p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE, p_month IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE, p_year scorecard_PROVIDER.YEAR%TYPE) is select ee.EVALUACION_PROVEEDOR_ID from EVALUACION_DET e,evaluacion ee where ee.PROVEEDORES_ID = p_provider_id and ee.YEAR = p_year and to_number(to_char(e.mes,'MM')) = 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; L_EVALUACION_PROVEEDOR_ID EVALUACION_PROVEEDOR.ID%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; delete SCORECARD_PROVIDER_MONTH where SCORECARD_PROVIDER_ID = L_SCORECARD_PROVIDER_ID; 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; delete SCORECARD_PROVIDER_COMMENTS where SCORECARD_PROVIDER_ID = L_SCORECARD_PROVIDER_ID; 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; delete SCORECARD_PROVIDER_COMMENTS where id = L_COMMENT_ID; 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; OPEN cur_evaluaciones(p_provider_id, p_month, p_year); LOOP FETCH cur_evaluaciones INTO L_EVALUACION_PROVEEDOR_ID; EXIT WHEN cur_evaluaciones%NOTFOUND OR cur_evaluaciones%NOTFOUND IS NULL; UPDATE EVALUACION_PROVEEDOR SET SCORECARD_ID = L_SCORECARD_PROVIDER_ID,ESTADO='EN PROGRESO',UPDATED_DATE=CURRENT_DATE where ID=L_EVALUACION_PROVEEDOR_ID and year = p_year AND MONTH = p_month; END LOOP ; CLOSE cur_evaluaciones; 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 p.id, p.name, p.LAST_SCORE *100/5 last_score, p.BEFORE_SCORE, p.TOTAL_AVG, p.TARGET, p.YEAR, p.created_date, p.updated_date, p.PROVEEDORES_ID, p.MONTH, p.status, p.REJECT_REASON 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 avg(avance_proveedor) 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 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 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; ; CREATE OR REPLACE PROCEDURE P_AVG_PROGRESS_ACTION_PLAN( p_plan_id in integer) as cursor cur_avg (p_plan_id in integer) is select avg(d.AVANCE) avg from plan_accion p join PLAN_ACCION_DET d on p.id = d.PLAN_ACCION_ID and d.PARENT_ID is not null where p.id = p_plan_id; L_TOTAL_AVG NUMBER(5, 2); BEGIN OPEN cur_avg(p_plan_id); FETCH cur_avg INTO L_TOTAL_AVG; CLOSE cur_avg; update PLAN_ACCION set AVANCE_PROVEEDOR = L_TOTAL_AVG,AVANCE_CM=L_TOTAL_AVG WHERE id =p_plan_id; end; ; CREATE OR REPLACE PROCEDURE P_AVG_SCORECARD_PROVIDER( p_scorecard_provider_id in integer, p_username in varchar2) as cursor cur_avg_area (p_scorecard_provider_id in integer) is select nvl(avg(SCORE_CATEGORY),0), ID_AREA from SCORECARD_PROVIDER_MONTH m where m.SCORECARD_PROVIDER_ID = p_scorecard_provider_id group by ID_AREA; cursor cur_tot_avg (p_scorecard_provider_id in integer) is select nvl(avg(SCORE_CATEGORY),0) from SCORECARD_PROVIDER_MONTH m where m.SCORECARD_PROVIDER_ID = p_scorecard_provider_id ; L_TOTAL_AVG NUMBER(5, 2); L_TOTAL_AREA_AVG NUMBER(5,2); L_AREA_ID number(5,2); BEGIN OPEN cur_tot_avg(p_scorecard_provider_id); FETCH cur_tot_avg INTO L_TOTAL_AVG; CLOSE cur_tot_avg; UPDATE SCORECARD_PROVIDER SET TOTAL_AVG = L_TOTAL_AVG,LAST_SCORE=L_TOTAL_AVG, UPDATED_DATE=current_date,UPDATED_BY=p_username where ID = p_scorecard_provider_id; OPEN cur_avg_area (p_scorecard_provider_id); LOOP FETCH cur_avg_area INTO L_TOTAL_AREA_AVG,L_AREA_ID; EXIT WHEN cur_avg_area%NOTFOUND OR cur_avg_area%NOTFOUND IS NULL; update SCORECARD_PROVIDER_MONTH set score = L_TOTAL_AREA_AVG,UPDATED_DATE=CURRENT_DATE where SCORECARD_PROVIDER_ID = p_scorecard_provider_id and ID_AREA = L_AREA_ID; END LOOP ; CLOSE cur_avg_area; end; ; CREATE OR REPLACE PROCEDURE P_GET_HISTORIC_SCORE( p_action_id in integer) AS DECLARE L_TOTAL_AVG NUMBER(5, 2); L_PARENT_ID NUMBER; BEGIN select nvl(avg(avance),0),DISTINCT(PARENT_ID) into L_TOTAL_AVG ,L_PARENT_ID from PLAN_ACCION_DET where PARENT_ID is NOT null and id = p_action_id; UPDATE plan_accion set AVANCE_PROVEEDOR = L_TOTAL_AVG where id = L_PARENT_ID; end; ; CREATE OR REPLACE PROCEDURE P_PROCESS_AVG_ACTION_PLAN( p_action_id in integer, p_username in varchar2) AS L_TOTAL_AVG NUMBER(5, 2); L_PARENT_ID NUMBER; L_PLAN_ACCION_ID NUMBER; BEGIN select PARENT_ID,PLAN_ACCION_ID into L_PARENT_ID,L_PLAN_ACCION_ID from PLAN_ACCION_DET where id = p_action_id; select avg(nvl(avance,0)) into L_TOTAL_AVG from PLAN_ACCION_DET where PARENT_ID = L_PARENT_ID group by PARENT_ID ; UPDATE PLAN_ACCION_DET set AVANCE = L_TOTAL_AVG, UPDATED_BY=p_username where PLAN_ACCION_ID = L_PLAN_ACCION_ID and PARENT_ID is null; select PLAN_ACCION_ID,avg(nvl(avance,0)) into L_PARENT_ID, L_TOTAL_AVG from PLAN_ACCION_DET where PLAN_ACCION_ID = L_PLAN_ACCION_ID and PARENT_ID is null group by PLAN_ACCION_ID ; UPDATE PLAN_ACCION SET AVANCE_PROVEEDOR= L_TOTAL_AVG,AVANCE_CM= L_TOTAL_AVG where ID = L_PLAN_ACCION_ID; end;