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; 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; 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; ;