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_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 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 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 PROVEEDORES.ID%TYPE,
                                        p_year        IN SCORECARD_PROVIDER.YEAR%TYPE,
                                        p_month       IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE)
    IS
    -- area
--type acursor is ref cursor;
--cur_score_area  acursor;

    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
               -- INTO           L_MONTH, L_YEAR, L_NAME, L_PROVEEDORES_ID, L_LAST_SCORE
        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 k.id, e.id, a.id, c.id, p.nombre, a.nombre, c.nombre, p.id, to_number(EXTRACT(MONTH FROM ed.mes), 99),
                 to_number(EXTRACT(YEAR FROM ed.mes), 9999)
        order by mes;

    --   -- por area nota total de todas las areas agrupadas por categoria
    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
    --group by ed.id
    --   GROUP BY c.id, p.nombre,c.nombre, p.id, to_number(EXTRACT(MONTH FROM ed.mes), 99),
    --            to_number(EXTRACT(YEAR FROM ed.mes), 9999)
    ;

    --   p.NOMBRE,a.NOMBRE,c.nombre catname,
    --          p.id,

    --global
    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 -- mes, YEAR, NOMBRE, provedorId,
               -- categoria_id,
               avg(promedio_categoria), NOMBRE
               -- INTO L_TOTAL_AVG
        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;

    --d.comentario,d.CREATED_DATE,x.PROVEEDORES_ID,e.MES

BEGIN
    --DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000);

    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, --L_LAST_SCORE,
            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
        --NVL(MAX(CodigoImpacto),0)+1
        --select scorecard_provider_seq.nextval into L_SCORECARD_PROVIDER_ID from dual;
        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)
            --select NVL(MAX(id),0)+1,
        values (L_SCORECARD_PROVIDER_ID, --SCORECARD_PROVIDER_SEQ.nextval,
                L_NAME, L_TOTAL_AVG, --L_LAST_SCORE,
                L_TOTAL_AVG, --L_BEFORE_SCORE,
                L_TOTAL_AVG, L_TARGET, p_year, CURRENT_DATE, p_provider_id, 'NO INICIADO', p_month)
        --from SCORECARD_PROVIDER
        ;
    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 log values ('*****');
        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_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 = 2019
          and p.month > 0
        order by p.MONTH;
    --and p.status = 'APROBADO';


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
    order by p.year,p.month;
    --and status ='PUBLICADO'

  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 categorias_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
  order by p.YEAR,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.year = p_year
    ;

  END;