create_table.sql 35 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036
  1. create sequence PROVEEDORES_SEQ
  2. maxvalue 9999999999
  3. ;
  4. create sequence USERS_SEQ
  5. maxvalue 9999999999
  6. ;
  7. create sequence KPIS_SEQ
  8. maxvalue 9999999999
  9. ;
  10. create sequence CATEGORYS_SEQ
  11. maxvalue 9999999999
  12. ;
  13. create sequence AREAS_SEQ
  14. maxvalue 9999999999
  15. ;
  16. create sequence EV_DETAIL_SEQ
  17. maxvalue 9999999999
  18. ;
  19. create sequence USERPROVEEDOR_SEQ
  20. maxvalue 9999999999
  21. ;
  22. create sequence NOTIFICATION_SEQ
  23. maxvalue 9999999999
  24. ;
  25. create sequence FILE_SEQ
  26. maxvalue 9999999999
  27. ;
  28. create sequence EVALUACION_SEQ
  29. maxvalue 9999999999
  30. ;
  31. create sequence EVALUACION_PROVEEDOR_SEQ
  32. ;
  33. create sequence EVALUACION_DET_COMMENTS_SEQ
  34. maxvalue 9999999999
  35. ;
  36. create sequence SCORECARD_PROVIDER_SEQ
  37. maxvalue 9999999999
  38. ;
  39. create sequence EVALUACION_DET_SEQ
  40. maxvalue 9999999999
  41. ;
  42. create sequence SCORECARD_PROVIDER_MONTH_SEQ
  43. maxvalue 9999999999
  44. ;
  45. create sequence PLAN_ACCION_SEQ
  46. maxvalue 9999999999
  47. ;
  48. create sequence PLAN_ACCION_SEQ_DET
  49. maxvalue 9999999999
  50. ;
  51. create sequence PROVEEDORES_AGENDA_SEQ
  52. maxvalue 9999999999
  53. ;
  54. create table PROVEEDORES
  55. (
  56. ID NUMBER not null
  57. constraint TEST_PK
  58. primary key,
  59. CONTACTO NVARCHAR2(50),
  60. EMAIL NVARCHAR2(50),
  61. ESTADO NVARCHAR2(50),
  62. EVALUACION_ACTUAL NUMBER(12, 2),
  63. EVALUACION_GLOBAL NUMBER(12, 2),
  64. FECHA_CREACION DATE,
  65. FECHA_ACTUALIZACION DATE,
  66. CREADO_POR NVARCHAR2(50),
  67. TENDENCIA NVARCHAR2(50),
  68. NOMBRE NVARCHAR2(50),
  69. KPI_ID NUMBER
  70. )
  71. ;
  72. create table ARCHIVOS
  73. (
  74. ID NUMBER not null
  75. constraint ARCHIVOS_PK
  76. primary key,
  77. NOMBRE NVARCHAR2(100),
  78. RUTA NVARCHAR2(200),
  79. TIPO_ARCHIVO NVARCHAR2(50),
  80. ENTIDAD NVARCHAR2(50),
  81. ENTIDAD_ID NUMBER
  82. )
  83. ;
  84. create table KPI
  85. (
  86. ID NUMBER not null
  87. constraint KPI_PK
  88. primary key,
  89. NOMBRE NVARCHAR2(100) not null,
  90. CREATED_DATE DATE,
  91. UPDATED_DATE TIMESTAMP(6)
  92. )
  93. ;
  94. create table AREAS
  95. (
  96. ID NUMBER not null
  97. constraint AREAS_PK
  98. primary key,
  99. NOMBRE NVARCHAR2(100),
  100. META NUMBER,
  101. CREATED_DATE DATE,
  102. CREATED_BY NVARCHAR2(50),
  103. UPDATED_BY NVARCHAR2(50),
  104. UPDATED_DATE DATE,
  105. KPI_ID NUMBER not null
  106. constraint AREAS_KPI_FK
  107. references KPI
  108. )
  109. ;
  110. create table CATEGORIAS
  111. (
  112. ID NUMBER not null
  113. constraint CATEGORIAS_PK
  114. primary key,
  115. NOMBRE NVARCHAR2(50),
  116. META_MENSUAL NUMBER,
  117. META_ANUAL NUMBER,
  118. CREATED_DATE DATE,
  119. UPDATED_DATE DATE,
  120. CREATED_BY NVARCHAR2(50),
  121. UPDATED_BY NVARCHAR2(50),
  122. AREAS_ID NUMBER not null
  123. constraint CATEGORIAS_AREAS_FK
  124. references AREAS
  125. )
  126. ;
  127. create table EVALUACION
  128. (
  129. ID NUMBER not null
  130. constraint EVALUACION_PK
  131. primary key,
  132. META NUMBER not null,
  133. CALIFICACION NUMBER(10, 2),
  134. ESTADO NVARCHAR2(50),
  135. YEAR NVARCHAR2(50),
  136. CALIFICACION_ANTERIOR NUMBER(10, 2),
  137. TENDENCIA NVARCHAR2(50),
  138. GLOBAL NUMBER(10, 2),
  139. CREATED_DATE DATE,
  140. UPDATED_DATE DATE,
  141. CREATED_BY NVARCHAR2(50),
  142. UPDATED_BY NVARCHAR2(50),
  143. PROVEEDORES_ID NUMBER not null
  144. constraint EVALUACION_PROVEEDORES_FK
  145. references PROVEEDORES,
  146. AREAS_ID NUMBER not null
  147. constraint EVALUACION_AREAS_FK
  148. references AREAS,
  149. EVALUACION_PROVEEDOR_ID NUMBER
  150. )
  151. ;
  152. create table EVALUACION_DET
  153. (
  154. ID NUMBER not null
  155. constraint EVALUACION_DET_PK
  156. primary key,
  157. COMENTARIO NVARCHAR2(50),
  158. CM_USER_UPDATE NVARCHAR2(50),
  159. VISIBLE_PROVEEDOR CHAR(1 char),
  160. CALIFICACION_ANTERIOR NUMBER(12, 2),
  161. CALIFICACION_ACTUAL NUMBER(12, 2),
  162. CALIFICACION_GLOBAL NUMBER(12, 2),
  163. MES DATE,
  164. CREATED_BY NVARCHAR2(50) default NULL,
  165. UPDATED_BY NVARCHAR2(50) default NULL,
  166. CREATED_DATE DATE,
  167. UPDATED_DATE DATE,
  168. EVALUACION_ID NUMBER not null
  169. constraint EVALUACION_DET_EVALUACION_FK
  170. references EVALUACION,
  171. CATEGORIAS_ID NUMBER not null
  172. constraint EVALUACION_DET_CATEGORIAS_FK
  173. references CATEGORIAS
  174. )
  175. ;
  176. create table EVALUACION_DET_COMMENTARIOS
  177. (
  178. ID NUMBER not null
  179. constraint EVALUACION_DET_COMMENTARIOS_PK
  180. primary key,
  181. COMENTARIO NVARCHAR2(300),
  182. CREATED_BY NVARCHAR2(50),
  183. CREATED_DATE DATE,
  184. UPDATED_BY NVARCHAR2(50),
  185. CM_USUARIO_MODIFICO NVARCHAR2(50),
  186. UPDATED_DATE DATE,
  187. EVALUACION_DET_ID NUMBER not null
  188. constraint EVAL_DET_COMM_FK
  189. references EVALUACION_DET
  190. )
  191. ;
  192. create table PLAN_ACCION
  193. (
  194. ID NUMBER not null
  195. constraint PLAN_ACCION_PK
  196. primary key,
  197. OBJETIVO NVARCHAR2(200),
  198. ESTADO NVARCHAR2(50),
  199. MES DATE,
  200. CREATED_BY NVARCHAR2(50),
  201. CREATED_DATE DATE,
  202. UPDATED_BY NVARCHAR2(50),
  203. UPDATED_DATE DATE,
  204. FECHA_COMPROMISO DATE,
  205. AVANCE_PROVEEDOR NUMBER(12, 2),
  206. AVANCE_CM NUMBER(12, 2),
  207. NOMBRE VARCHAR2(100),
  208. PROVEEDOR_ID NUMBER
  209. constraint PLAN_ACCION_PROVEE__FK
  210. references PROVEEDORES,
  211. TIENE_EVENTO_NUEVO NUMBER default NULL,
  212. MONTH VARCHAR2(25),
  213. YEAR VARCHAR2(25)
  214. )
  215. ;
  216. create table PLAN_ACCION_DET
  217. (
  218. ID NUMBER not null
  219. constraint PLAN_ACCION_DET_PK
  220. primary key,
  221. COMENTARIO NVARCHAR2(300),
  222. CREATED_DATE DATE,
  223. CREATED_BY NVARCHAR2(50),
  224. UPDATED_BY NVARCHAR2(50),
  225. ESTADO NVARCHAR2(50),
  226. PLAN_ACCION_ID NUMBER not null
  227. constraint PLAN_ACCION_DET_PLAN_ACCION_FK
  228. references PLAN_ACCION,
  229. DESCRIPCION VARCHAR2(500),
  230. FECHA_COMPROMISO DATE,
  231. AVANCE NUMBER(12, 2),
  232. NOMBRE VARCHAR2(100)
  233. )
  234. ;
  235. create table PLAN_ACCION_COMMENTARIOS
  236. (
  237. ID NUMBER not null
  238. constraint PLAN_ACCION_COMMENTARIOS_PK
  239. primary key,
  240. COMENTARIO NVARCHAR2(300),
  241. CREATED_BY NVARCHAR2(50),
  242. CREATED_DATE DATE,
  243. UPDATED_BY NVARCHAR2(50),
  244. CM_USUARIO_MODIFICO NVARCHAR2(50),
  245. UPDATED_DATE DATE,
  246. PLAN_ACCION_DET_ID NUMBER not null
  247. constraint PLAN_ACCION_DET_COM_FK
  248. references PLAN_ACCION_DET
  249. )
  250. ;
  251. create table ROLES_PERMISOS
  252. (
  253. ID NUMBER not null
  254. constraint ROLES_PERMISOS_PK
  255. primary key,
  256. ROLE_NAME NVARCHAR2(50),
  257. DESCRIPCION NVARCHAR2(100),
  258. CREATED_DATE DATE
  259. )
  260. ;
  261. create table USERS
  262. (
  263. ID NUMBER not null
  264. constraint USERS_PK
  265. primary key,
  266. USER_NAME NVARCHAR2(50),
  267. PASSWORD NVARCHAR2(200),
  268. USER_TYPE NVARCHAR2(100),
  269. EMAIL NVARCHAR2(100),
  270. STATUS CHAR,
  271. ENABLED CHAR,
  272. FIRST_NAME VARCHAR2(100),
  273. LAST_NAME VARCHAR2(100)
  274. )
  275. ;
  276. create table USERS_PROVEEDORES
  277. (
  278. ID NUMBER not null
  279. constraint USERS_PROVEEDORES_PK
  280. primary key,
  281. CREATED_DATE DATE,
  282. UPDATED_DATE DATE,
  283. PROVEEDORES_ID NUMBER not null
  284. constraint USERS_PROVES_PROVE_FK
  285. references PROVEEDORES,
  286. USERS_ID NUMBER not null
  287. constraint USERS_PROVEEDORES_USERS_FK
  288. references USERS
  289. )
  290. ;
  291. create table USERS_ROLES
  292. (
  293. ID NUMBER not null
  294. constraint USERS_ROLES_PK
  295. primary key,
  296. ROLES_PERMISOS_ID NUMBER not null
  297. constraint USERS_ROLES_ROLES_PERMISOS_FK
  298. references ROLES_PERMISOS,
  299. USERS_ID NUMBER not null
  300. constraint USERS_ROLES_USERS_FK
  301. references USERS
  302. )
  303. ;
  304. create table NOTIFICATION_TEMPLATE
  305. (
  306. ID NUMBER(19) not null
  307. primary key,
  308. CREATED_BY VARCHAR2(30 char),
  309. CREATED_ON TIMESTAMP(6),
  310. UPDATED_BY VARCHAR2(30 char),
  311. UPDATED_ON TIMESTAMP(6),
  312. VERSION NUMBER(19),
  313. BODY LONG,
  314. NAME VARCHAR2(255 char),
  315. SCOPE NUMBER(10),
  316. STATUS NUMBER(5),
  317. SUBJECT VARCHAR2(255 char),
  318. TYPE VARCHAR2(20 char)
  319. )
  320. ;
  321. create table FILE_ITEM
  322. (
  323. ID NUMBER(19) not null
  324. primary key,
  325. BUCKET_NAME VARCHAR2(255 char),
  326. CREATED_BY VARCHAR2(255 char) not null,
  327. CREATED_DATE TIMESTAMP(6) not null,
  328. DESCRIPTION VARCHAR2(255 char),
  329. ENTITY_TYPE VARCHAR2(255 char) not null,
  330. FILE_NAME VARCHAR2(255 char),
  331. FILE_PATH VARCHAR2(255 char),
  332. FILE_SIZE NUMBER(19),
  333. IDENTIFIER NUMBER(19) not null,
  334. IS_PUBLIC NUMBER(1) default NULL,
  335. MIME_TYPE VARCHAR2(255 char),
  336. SCOPE VARCHAR2(255 char) not null
  337. )
  338. ;
  339. create table EVALUACION_PROVEEDOR
  340. (
  341. ID NUMBER(19, 2) not null
  342. primary key,
  343. CALIFICACION NUMBER(19, 2),
  344. CALIFICACION_ANTERIOR NUMBER(19, 2),
  345. CREATED_BY VARCHAR2(255 char),
  346. CREATED_DATE TIMESTAMP(6),
  347. ESTADO VARCHAR2(255 char),
  348. GLOBAL NUMBER(19, 2),
  349. META NUMBER(19, 2) not null,
  350. MONTH VARCHAR2(255 char),
  351. TENDENCIA VARCHAR2(255 char),
  352. UPDATED_BY VARCHAR2(255 char),
  353. UPDATED_DATE TIMESTAMP(6),
  354. YEAR VARCHAR2(255 char),
  355. AREAS_ID NUMBER(19) not null
  356. )
  357. ;
  358. create table SCORECARD_PROVIDER
  359. (
  360. ID NUMBER not null
  361. constraint SCORECARD_PROVIDER_PK
  362. primary key,
  363. NAME NVARCHAR2(100),
  364. LAST_SCORE NUMBER(5, 2),
  365. BEFORE_SCORE NUMBER(5, 2),
  366. TOTAL_AVG NUMBER(5, 2),
  367. TARGET NUMBER(5),
  368. YEAR NVARCHAR2(10),
  369. CREATED_DATE DATE,
  370. UPDATED_DATE DATE,
  371. PROVEEDORES_ID NUMBER not null
  372. constraint SCORECARD_PROVIDER_PROV_FK
  373. references PROVEEDORES,
  374. MONTH VARCHAR2(15),
  375. STATUS VARCHAR2(50)
  376. )
  377. ;
  378. create table SCORECARD_PROVIDER_MONTH
  379. (
  380. ID NUMBER not null
  381. constraint SCORECARD_PROVIDER_MONTH_PK
  382. primary key,
  383. MONTH NVARCHAR2(50),
  384. SCORE NUMBER(5, 2),
  385. BEFORE_SCORE NUMBER(5, 2),
  386. CREATED_DATE DATE,
  387. UPDATED_DATE DATE,
  388. CREATED_BY NVARCHAR2(50),
  389. SCORECARD_PROVIDER_ID NUMBER not null
  390. constraint SCORECARD_MONTH_PROV_FK
  391. references SCORECARD_PROVIDER,
  392. SCORE_CATEGORY NUMBER(5, 2),
  393. BEFORE_SCORE_CATEGORY NUMBER(5, 2),
  394. NOMBRE_CATEGORIA VARCHAR2(100),
  395. NOMBRE_AREA VARCHAR2(100),
  396. ESTADO VARCHAR2(50),
  397. ID_AREA NUMBER,
  398. ID_CATEGORIA NUMBER
  399. )
  400. ;
  401. create table LOG
  402. (
  403. LINE NVARCHAR2(100)
  404. )
  405. ;
  406. create table SCORECARD_PROVIDER_COMMENTS
  407. (
  408. ID NUMBER default NULL not null
  409. constraint SCORECARD_PROVIDER_COMMENTS_PK
  410. primary key,
  411. COMMENTS VARCHAR2(1000),
  412. CREATED_DATE DATE,
  413. CREATED_USER VARCHAR2(50),
  414. SCORECARD_PROVIDER_ID NUMBER
  415. constraint SCORE_PRO_COMMS_FK
  416. references SCORECARD_PROVIDER
  417. )
  418. ;
  419. create table PROVEEDOR_AGENDA
  420. (
  421. ID NUMBER not null
  422. primary key,
  423. TITULO NVARCHAR2(200),
  424. OBJETIVO NVARCHAR2(200),
  425. ES_RELEVANTE NUMBER,
  426. ATENDERA_PROVEEDOR NUMBER,
  427. FECHA_REUNION_INICIO DATE,
  428. FECHA_REUNION_FIN DATE,
  429. CREATED_BY NVARCHAR2(50),
  430. CREATED_DATE DATE,
  431. UPDATED_BY NVARCHAR2(100),
  432. PROVEEDOR_ID NUMBER
  433. constraint PROV_AGENDA_PROV_ID_FK
  434. references PROVEEDORES,
  435. YEAR NUMBER,
  436. ESTADO NVARCHAR2(30),
  437. MINUTA NVARCHAR2(500)
  438. )
  439. ;
  440. create table AUTHORITIES
  441. (
  442. ID NUMBER not null
  443. primary key,
  444. USERNAME NVARCHAR2(50),
  445. AUTHORITY NVARCHAR2(100)
  446. )
  447. ;
  448. create PROCEDURE dashboard_generateScoreYear(pid_evaluacion IN NUMBER,
  449. result_code OUT NUMBER)
  450. AS
  451. paa_id INT := 0;
  452. CURSOR c1 IS
  453. SELECT e.id AS id_evaluacion,
  454. e.meta AS meta_evaluacion,
  455. e.CALIFICACION,
  456. e.CALIFICACION_ANTERIOR,
  457. a.id AS id_area,
  458. a.nombre AS nombre_area,
  459. a.meta meta_area,
  460. a.kpi_id,
  461. k.NOMBRE AS nombre_kpi,
  462. c.id AS id_categoria,
  463. c.nombre AS nombre_categoria
  464. FROM EVALUACION e
  465. JOIN areas a ON e.AREAS_ID = a.ID
  466. JOIN kpi k ON a.KPI_ID = k.id
  467. LEFT JOIN categorias c ON a.id = c.AREAS_ID;
  468. BEGIN
  469. result_code := 0;
  470. FOR custom IN c1
  471. LOOP
  472. FOR i IN 1..11
  473. LOOP
  474. INSERT INTO EVALUACION_DET (ID,
  475. COMENTARIO,
  476. CM_USER_UPDATE,
  477. VISIBLE_PROVEEDOR,
  478. CALIFICACION_ANTERIOR,
  479. CALIFICACION_ACTUAL,
  480. CALIFICACION_GLOBAL,
  481. MES,
  482. CREATED_BY,
  483. UPDATED_BY,
  484. CREATED_DATE,
  485. UPDATED_DATE,
  486. EVALUACION_ID,
  487. CATEGORIAS_ID)
  488. VALUES (EV_DETAIL_SEQ.nextval,
  489. 'inicial',
  490. 'admin',
  491. 's',
  492. 4.00,
  493. 5.00,
  494. 6.00,
  495. TO_DATE('2019-' || i || '-25 19:00:00', 'YYYY-MM-DD HH24:MI:SS'),
  496. 'admin',
  497. 'admin',
  498. current_date,
  499. current_date,
  500. custom.id_evaluacion,
  501. custom.id_categoria);
  502. END LOOP;
  503. END LOOP;
  504. result_code := 0;
  505. -- COMMIT;
  506. EXCEPTION WHEN OTHERS
  507. THEN
  508. result_code := -1;
  509. ROLLBACK;
  510. RAISE;
  511. END;
  512. ;
  513. create PROCEDURE dash_global_by_kpi_month(pid_kpi IN NUMBER,
  514. pmes IN NUMBER,
  515. result_code OUT NUMBER)
  516. AS
  517. BEGIN
  518. result_code := 0;
  519. MERGE INTO evaluacion_det orig
  520. USING (SELECT k.id AS kpi_id,
  521. k.nombre kpi_nombre,
  522. e.id eval_id,
  523. e.CALIFICACION,
  524. e.global,
  525. e.AREAS_ID,
  526. a.nombre area_nombre,
  527. a.meta,
  528. c.ID AS categorias_id,
  529. c.nombre cat_nombre,
  530. c.META_MENSUAL,
  531. (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria,
  532. ed.ID detail_id,
  533. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number,
  534. ed.CALIFICACION_ANTERIOR,
  535. ed.CALIFICACION_ACTUAL,
  536. ed.CALIFICACION_GLOBAL,
  537. ed.mes fecha
  538. FROM EVALUACION_DET ed
  539. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  540. JOIN areas a ON e.AREAS_ID = a.id
  541. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  542. JOIN kpi k ON a.KPI_ID = k.id AND k.id = pid_kpi
  543. WHERE to_number(EXTRACT(MONTH FROM ed.mes), 99) = pmes) detail
  544. ON (orig.id = detail.detail_id)
  545. WHEN MATCHED THEN UPDATE SET orig.CALIFICACION_GLOBAL = detail.global_categoria;
  546. COMMIT;
  547. EXCEPTION
  548. WHEN OTHERS
  549. THEN
  550. result_code := -1;
  551. ROLLBACK;
  552. RAISE;
  553. END;
  554. ;
  555. create PROCEDURE dash_global_by_kpi(pid_kpi IN NUMBER,
  556. result_code OUT NUMBER)
  557. AS
  558. paa_id INT := 0;
  559. BEGIN
  560. result_code := 0;
  561. MERGE INTO evaluacion_det orig
  562. USING (SELECT k.id AS kpi_id,
  563. k.nombre kpi_nombre,
  564. e.id eval_id,
  565. e.CALIFICACION,
  566. e.global,
  567. e.AREAS_ID,
  568. a.nombre area_nombre,
  569. a.meta,
  570. c.ID AS categorias_id,
  571. c.nombre cat_nombre,
  572. c.META_MENSUAL,
  573. (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria,
  574. ed.ID detail_id,
  575. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number,
  576. ed.CALIFICACION_ANTERIOR,
  577. ed.CALIFICACION_ACTUAL,
  578. ed.CALIFICACION_GLOBAL,
  579. ed.mes fecha
  580. FROM EVALUACION_DET ed
  581. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  582. JOIN areas a ON e.AREAS_ID = a.id
  583. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  584. JOIN kpi k ON a.KPI_ID = k.id
  585. AND k.id = pid_kpi) detail
  586. ON (orig.id = detail.detail_id)
  587. WHEN MATCHED THEN UPDATE SET orig.CALIFICACION_GLOBAL = detail.global_categoria;
  588. COMMIT;
  589. EXCEPTION WHEN OTHERS
  590. THEN
  591. result_code := -1;
  592. ROLLBACK;
  593. RAISE;
  594. END;
  595. ;
  596. create PROCEDURE d_eval_global_month(pid_kpi IN NUMBER, pmes IN NUMBER,
  597. result_code OUT NUMBER)
  598. AS
  599. paa_id INT := 0;
  600. BEGIN
  601. result_code := 0;
  602. MERGE INTO evaluacion orig
  603. USING (SELECT k.id id_kpi, e.id id_eval, a.id, avg(ed.CALIFICACION_GLOBAL) AS average
  604. FROM EVALUACION_DET ed
  605. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  606. JOIN areas a ON e.AREAS_ID = a.id
  607. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  608. JOIN kpi k ON a.KPI_ID = k.id
  609. AND k.id = pid_kpi
  610. WHERE to_number(EXTRACT(MONTH FROM ed.mes), 99) = pmes
  611. GROUP BY k.id, e.id, a.id, to_number(EXTRACT(MONTH FROM ed.mes), 99)) detail
  612. ON (orig.id = detail.id_eval)
  613. WHEN MATCHED THEN UPDATE SET orig.GLOBAL = detail.average;
  614. COMMIT;
  615. EXCEPTION WHEN OTHERS
  616. THEN
  617. result_code := -1;
  618. ROLLBACK;
  619. RAISE;
  620. END;
  621. ;
  622. create PROCEDURE get_detail_month(pkpi_id IN NUMBER, pmes IN NUMBER, p_cursor IN OUT SYS_REFCURSOR)
  623. AS
  624. BEGIN
  625. OPEN p_cursor FOR
  626. SELECT k.id AS kpi_id,
  627. k.nombre kpi_nombre,
  628. e.id eval_id,
  629. e.global global_actual,
  630. e.AREAS_ID,
  631. a.nombre area_nombre,
  632. c.ID AS categorias_id,
  633. c.nombre cat_nombre,
  634. c.META_MENSUAL,
  635. (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria,
  636. ed.ID detail_id,
  637. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number,
  638. ed.CALIFICACION_ACTUAL,
  639. ed.CALIFICACION_GLOBAL
  640. FROM EVALUACION_DET ed
  641. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  642. JOIN areas a ON e.AREAS_ID = a.id
  643. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  644. JOIN kpi k ON a.KPI_ID = k.id AND k.id = pkpi_id
  645. WHERE to_number(EXTRACT(MONTH FROM ed.mes), 99) = pmes
  646. ORDER BY k.id, e.id, a.id, c.id, to_number(EXTRACT(MONTH FROM ed.mes), 99);
  647. END;
  648. ;
  649. create PROCEDURE get_detail_global(pkpi_id IN NUMBER, p_cursor IN OUT SYS_REFCURSOR)
  650. AS
  651. BEGIN
  652. OPEN p_cursor FOR
  653. SELECT k.id AS kpi_id,
  654. k.nombre kpi_nombre,
  655. e.id eval_id,
  656. e.global global_actual,
  657. e.AREAS_ID,
  658. a.nombre area_nombre,
  659. c.ID AS categorias_id,
  660. c.nombre cat_nombre,
  661. c.META_MENSUAL,
  662. (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria,
  663. ed.ID detail_id,
  664. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number,
  665. ed.CALIFICACION_ACTUAL,
  666. ed.CALIFICACION_GLOBAL
  667. FROM EVALUACION_DET ed
  668. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  669. JOIN areas a ON e.AREAS_ID = a.id
  670. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  671. JOIN kpi k ON a.KPI_ID = k.id AND k.id = pkpi_id
  672. ORDER BY k.id, e.id, a.id, c.id, to_number(EXTRACT(MONTH FROM ed.mes), 99);
  673. END;
  674. ;
  675. create PROCEDURE get_detail_global_month(pkpi_id IN NUMBER, p_cursor IN OUT SYS_REFCURSOR)
  676. AS
  677. BEGIN
  678. OPEN p_cursor FOR
  679. SELECT kpi_id, mes, avg(promedio_area) AS promedio
  680. FROM (SELECT k.id AS kpi_id,
  681. e.id eval_id,
  682. a.id areas_id,
  683. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes,
  684. avg(ed.CALIFICACION_GLOBAL) promedio_area
  685. FROM EVALUACION_DET ed
  686. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  687. JOIN areas a ON e.AREAS_ID = a.id
  688. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  689. JOIN kpi k ON a.KPI_ID = k.id AND k.id = pkpi_id
  690. GROUP BY k.id, e.id, a.id, to_number(EXTRACT(MONTH FROM ed.mes), 99))
  691. GROUP BY kpi_id, mes
  692. ORDER BY kpi_id, mes;
  693. END;
  694. ;
  695. create PROCEDURE pc_create_up_scorecard(p_provider_id IN PROVEEDORES.ID%TYPE,
  696. p_year IN SCORECARD_PROVIDER.YEAR%TYPE,
  697. p_month IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE)
  698. IS
  699. -- area
  700. --type acursor is ref cursor;
  701. --cur_score_area acursor;
  702. CURSOR cur_score_cate (p_provider_id IN scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  703. p_month IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  704. p_year IN scorecard_PROVIDER.YEAR%TYPE) is
  705. SELECT to_number(EXTRACT(MONTH FROM ed.mes), 99) mes,
  706. to_number(EXTRACT(YEAR FROM ed.mes), 9999) YEAR,
  707. p.NOMBRE,
  708. a.NOMBRE,
  709. c.NOMBRE,
  710. p.id,
  711. a.ID,
  712. c.id,
  713. avg(ed.CALIFICACION_ACTUAL) promedio_area
  714. -- INTO L_MONTH, L_YEAR, L_NAME, L_PROVEEDORES_ID, L_LAST_SCORE
  715. FROM EVALUACION_DET ed
  716. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  717. JOIN areas a ON e.AREAS_ID = a.id
  718. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  719. JOIN kpi k ON a.KPI_ID = k.id
  720. JOIN PROVEEDORES P on e.PROVEEDORES_ID = P.ID
  721. WHERE p.id = p_provider_id
  722. AND to_number(EXTRACT(MONTH FROM ed.mes), 99) = p_month
  723. AND to_number(EXTRACT(year FROM ed.mes), 9999) = p_year
  724. 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),
  725. to_number(EXTRACT(YEAR FROM ed.mes), 9999)
  726. order by mes;
  727. -- -- por area nota total de todas las areas agrupadas por categoria
  728. CURSOR cur_score_area (
  729. p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  730. p_month SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  731. p_year scorecard_PROVIDER.YEAR%TYPE,
  732. p_area_id IN scorecard_PROVIDER.YEAR%TYPE) is
  733. SELECT avg(ed.CALIFICACION_ACTUAL)
  734. FROM EVALUACION_DET ed
  735. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  736. JOIN areas a ON e.AREAS_ID = a.id
  737. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  738. JOIN PROVEEDORES P on e.PROVEEDORES_ID = P.ID
  739. WHERE p.id = p_provider_id
  740. AND to_number(EXTRACT(MONTH FROM ed.mes), 99) = p_month
  741. AND to_number(EXTRACT(year FROM ed.mes), 9999) = p_year
  742. and a.id = p_area_id
  743. --group by ed.id
  744. -- GROUP BY c.id, p.nombre,c.nombre, p.id, to_number(EXTRACT(MONTH FROM ed.mes), 99),
  745. -- to_number(EXTRACT(YEAR FROM ed.mes), 9999)
  746. ;
  747. -- p.NOMBRE,a.NOMBRE,c.nombre catname,
  748. -- p.id,
  749. --global
  750. CURSOR cur_score_global (p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  751. p_month SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  752. p_year scorecard_PROVIDER.YEAR%TYPE) is
  753. select -- mes, YEAR, NOMBRE, provedorId,
  754. -- categoria_id,
  755. avg(promedio_categoria), NOMBRE
  756. -- INTO L_TOTAL_AVG
  757. from (SELECT e.id eval_id,
  758. c.id categoria_id,
  759. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes,
  760. to_number(EXTRACT(YEAR FROM ed.mes), 9999) YEAR,
  761. p.NOMBRE,
  762. p.id provedorId,
  763. avg(ed.CALIFICACION_ACTUAL) promedio_categoria
  764. FROM EVALUACION_DET ed
  765. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  766. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  767. JOIN PROVEEDORES P on e.PROVEEDORES_ID = P.ID
  768. where p.id = p_provider_id
  769. and to_number(EXTRACT(MONTH FROM ed.mes), 99) = p_month
  770. AND to_number(EXTRACT(year FROM ed.mes), 9999) = p_year
  771. GROUP BY e.id, c.id, p.nombre, p.id, to_number(EXTRACT(MONTH FROM ed.mes), 99),
  772. to_number(EXTRACT(YEAR FROM ed.mes), 9999))
  773. group by YEAR, NOMBRE, provedorId; --mes,
  774. CURSOR cur_score_year (
  775. p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  776. p_month IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  777. p_year scorecard_PROVIDER.YEAR%TYPE) is
  778. select p.id, p.LAST_SCORE
  779. from SCORECARD_PROVIDER p
  780. where p.PROVEEDORES_ID = p_provider_id
  781. and p.YEAR = p_year
  782. and p.MONTH = p_month;
  783. CURSOR cur_quiz_comments
  784. (p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  785. p_month SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  786. p_year scorecard_PROVIDER.YEAR%TYPE)
  787. is
  788. select d.id, d.comentario, d.CREATED_DATE, d.CREATED_BY
  789. from EVALUACION_DET_COMMENTARIOS d
  790. join evaluacion_det e on e.id = d.EVALUACION_DET_ID
  791. join evaluacion x on x.id = e.EVALUACION_ID
  792. and x.PROVEEDORES_ID = p_provider_id
  793. and to_number(EXTRACT(MONTH FROM e.mes), 99) = p_month
  794. AND to_number(EXTRACT(year FROM e.mes), 9999) = p_year;
  795. L_NAME scorecard_PROVIDER.NAME%TYPE;
  796. L_LAST_SCORE_AREA SCORECARD_PROVIDER_MONTH.SCORE%TYPE;
  797. L_LAST_SCORE scorecard_PROVIDER.LAST_SCORE%TYPE; -- CATGORIAS
  798. L_BEFORE_SCORE scorecard_PROVIDER.BEFORE_SCORE%TYPE;
  799. L_TOTAL_AVG scorecard_PROVIDER.TOTAL_AVG%TYPE;
  800. L_TARGET scorecard_PROVIDER.TARGET%TYPE := 5;
  801. L_YEAR scorecard_PROVIDER.YEAR%TYPE := p_year;
  802. L_CREATED_DATE scorecard_PROVIDER.CREATED_DATE%TYPE;
  803. L_PROVEEDORES_ID scorecard_PROVIDER.PROVEEDORES_ID%TYPE := p_provider_id;
  804. L_SCORECARD_PROVIDER SCORECARD_PROVIDER.PROVEEDORES_ID%TYPE;
  805. L_MONTH SCORECARD_PROVIDER_MONTH.MONTH%TYPE := p_month;
  806. L_SCORECARD_PROVIDER_ID SCORECARD_PROVIDER.ID%TYPE;
  807. L_NOMBRE_AREA scorecard_PROVIDER_MONTH.NOMBRE_AREA%TYPE;
  808. L_NOMBRE_CATEGORIA scorecard_PROVIDER_MONTH.NOMBRE_CATEGORIA%TYPE;
  809. L_ID_AREA AREAS.ID%TYPE;
  810. L_ID_CATEGORIA CATEGORIAS.ID%TYPE;
  811. L_COMMENT EVALUACION_DET_COMMENTARIOS.COMENTARIO%TYPE;
  812. L_COMMENT_ID EVALUACION_DET_COMMENTARIOS.ID%TYPE;
  813. L_COMMENT_CREATED_DATE EVALUACION_DET_COMMENTARIOS.CREATED_DATE%TYPE;
  814. L_CREATED_BY EVALUACION_DET_COMMENTARIOS.CREATED_BY%TYPE;
  815. --d.comentario,d.CREATED_DATE,x.PROVEEDORES_ID,e.MES
  816. BEGIN
  817. --DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000);
  818. OPEN cur_score_global(p_provider_id, p_month, p_year);
  819. FETCH cur_score_global INTO L_TOTAL_AVG, L_NAME;
  820. CLOSE cur_score_global;
  821. OPEN cur_score_year(p_provider_id, p_month, p_year);
  822. FETCH cur_score_year INTO L_SCORECARD_PROVIDER_ID, L_LAST_SCORE;
  823. CLOSE cur_score_year;
  824. if L_SCORECARD_PROVIDER_ID > 0
  825. then
  826. UPDATE SCORECARD_PROVIDER
  827. SET NAME = L_NAME,
  828. LAST_SCORE = L_TOTAL_AVG, --L_LAST_SCORE,
  829. BEFORE_SCORE = L_BEFORE_SCORE,
  830. TOTAL_AVG = L_TOTAL_AVG,
  831. TARGET = L_TARGET,
  832. YEAR = L_YEAR,
  833. UPDATED_DATE = CURRENT_DATE
  834. WHERE ID = L_SCORECARD_PROVIDER_ID;
  835. else
  836. --NVL(MAX(CodigoImpacto),0)+1
  837. --select scorecard_provider_seq.nextval into L_SCORECARD_PROVIDER_ID from dual;
  838. L_SCORECARD_PROVIDER_ID := scorecard_provider_seq.nextval;
  839. INSERT INTO SCORECARD_PROVIDER (ID,
  840. NAME,
  841. LAST_SCORE,
  842. BEFORE_SCORE,
  843. TOTAL_AVG,
  844. TARGET,
  845. YEAR,
  846. CREATED_DATE,
  847. PROVEEDORES_ID,
  848. STATUS,
  849. MONTH)
  850. --select NVL(MAX(id),0)+1,
  851. values (L_SCORECARD_PROVIDER_ID, --SCORECARD_PROVIDER_SEQ.nextval,
  852. L_NAME, L_TOTAL_AVG, --L_LAST_SCORE,
  853. L_TOTAL_AVG, --L_BEFORE_SCORE,
  854. L_TOTAL_AVG, L_TARGET, p_year, CURRENT_DATE, p_provider_id, 'NO INICIADO', p_month)
  855. --from SCORECARD_PROVIDER
  856. ;
  857. end if;
  858. OPEN cur_score_cate(p_provider_id, p_month, p_year);
  859. LOOP
  860. FETCH cur_score_cate INTO L_MONTH, L_YEAR,
  861. L_NAME, L_NOMBRE_AREA, L_NOMBRE_CATEGORIA, L_PROVEEDORES_ID, L_ID_AREA, L_ID_CATEGORIA,
  862. L_LAST_SCORE;
  863. EXIT WHEN cur_score_cate%NOTFOUND OR cur_score_cate%NOTFOUND IS NULL;
  864. open cur_score_area(p_provider_id, p_month, p_year, L_ID_AREA); --nota agrupada de las categorias al area
  865. FETCH cur_score_area INTO L_LAST_SCORE_AREA;
  866. CLOSE cur_score_area;
  867. --insert into log values ('*****');
  868. INSERT INTO SCORECARD_PROVIDER_MONTH (ID,
  869. MONTH,
  870. SCORE,
  871. BEFORE_SCORE,
  872. SCORE_CATEGORY,
  873. BEFORE_SCORE_CATEGORY,
  874. CREATED_DATE,
  875. SCORECARD_PROVIDER_ID,
  876. ESTADO,
  877. NOMBRE_AREA,
  878. NOMBRE_CATEGORIA,
  879. ID_AREA,
  880. ID_CATEGORIA)
  881. VALUES (scorecard_provider_month_seq.nextval,
  882. L_MONTH,
  883. L_LAST_SCORE_AREA,
  884. L_LAST_SCORE_AREA,
  885. L_LAST_SCORE,
  886. L_LAST_SCORE,
  887. CURRENT_DATE,
  888. L_SCORECARD_PROVIDER_ID,
  889. 'NO INICIADO',
  890. L_NOMBRE_AREA,
  891. L_NOMBRE_CATEGORIA,
  892. L_ID_AREA,
  893. L_ID_CATEGORIA);
  894. END LOOP;
  895. close cur_score_cate;
  896. OPEN cur_quiz_comments(p_provider_id, p_month, p_year);
  897. LOOP
  898. FETCH cur_quiz_comments INTO L_COMMENT_ID, L_COMMENT, L_COMMENT_CREATED_DATE, L_CREATED_BY;
  899. EXIT WHEN cur_quiz_comments%NOTFOUND OR cur_quiz_comments%NOTFOUND IS NULL;
  900. INSERT INTO SCORECARD_PROVIDER_COMMENTS (ID,
  901. COMMENTS,
  902. CREATED_DATE,
  903. CREATED_USER,
  904. SCORECARD_PROVIDER_ID)
  905. VALUES (L_COMMENT_ID, L_COMMENT, L_COMMENT_CREATED_DATE, L_CREATED_BY, L_SCORECARD_PROVIDER_ID);
  906. END LOOP;
  907. CLOSE cur_quiz_comments;
  908. END pc_create_up_scorecard;
  909. ;
  910. create PROCEDURE GET_RESUME_SCORE_AREA(
  911. p_scoreprovider_id IN SCORECARD_PROVIDER.ID%TYPE
  912. , p_cursor IN OUT SYS_REFCURSOR)
  913. IS
  914. BEGIN
  915. open p_cursor for
  916. select s.ID_AREA as idArea, s.nombre_area as name, s.score as score
  917. from scorecard_provider_month s
  918. where scorecard_provider_id = p_scoreprovider_id
  919. group by s.ID_AREA, s.nombre_area, s.score;
  920. END GET_RESUME_SCORE_AREA;
  921. ;
  922. create PROCEDURE GET_RESUME_LASTEST_MONTHS(
  923. p_provider_id IN SCORECARD_PROVIDER.ID%TYPE
  924. , p_cursor IN OUT SYS_REFCURSOR)
  925. IS
  926. BEGIN
  927. open p_cursor for
  928. select p.MONTH as name, (p.TOTAL_AVG / 5 * 100) as score
  929. from scorecard_provider p
  930. where p.proveedores_id = p_provider_id
  931. and p.year = 2019
  932. and p.month > 0
  933. order by p.MONTH;
  934. --and p.status = 'APROBADO';
  935. END GET_RESUME_LASTEST_MONTHS;
  936. ;
  937. create PROCEDURE GET_SCOREPRO_CATEGORY_SCORES(
  938. p_scoreprovider_id IN SCORECARD_PROVIDER.ID%TYPE,
  939. p_area_id IN SCORECARD_PROVIDER_MONTH.ID_AREA%TYPE,
  940. p_cursor IN OUT SYS_REFCURSOR)
  941. IS
  942. BEGIN
  943. open p_cursor for
  944. select s.NOMBRE_CATEGORIA as nombre, s.NOMBRE_CATEGORIA as name, s.SCORE_CATEGORY as score
  945. from scorecard_provider_month s
  946. where s.scorecard_provider_id = p_scoreprovider_id
  947. and s.ID_AREA = p_area_id;
  948. --and p.status = 'APROBADO';
  949. END GET_SCOREPRO_CATEGORY_SCORES;
  950. ;