scorecard-war/sp_create_replace.sql

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;