scorecard-war/store_procedures.sql

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