scorecard-war/create_table.sql

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