sp_create_replace.sql 31 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940
  1. CREATE OR REPLACE PROCEDURE dashboard_generateScoreYear(pid_evaluacion IN NUMBER,
  2. result_code OUT NUMBER)
  3. AS
  4. paa_id INT := 0;
  5. CURSOR c1 IS
  6. SELECT e.id AS id_evaluacion,
  7. e.meta AS meta_evaluacion,
  8. e.CALIFICACION,
  9. e.CALIFICACION_ANTERIOR,
  10. a.id AS id_area,
  11. a.nombre AS nombre_area,
  12. a.meta meta_area,
  13. a.kpi_id,
  14. k.NOMBRE AS nombre_kpi,
  15. c.id AS id_categoria,
  16. c.nombre AS nombre_categoria
  17. FROM EVALUACION e
  18. JOIN areas a ON e.AREAS_ID = a.ID
  19. JOIN kpi k ON a.KPI_ID = k.id
  20. LEFT JOIN categorias c ON a.id = c.AREAS_ID;
  21. BEGIN
  22. result_code := 0;
  23. FOR custom IN c1
  24. LOOP
  25. FOR i IN 1..11
  26. LOOP
  27. INSERT INTO EVALUACION_DET (ID,
  28. COMENTARIO,
  29. CM_USER_UPDATE,
  30. VISIBLE_PROVEEDOR,
  31. CALIFICACION_ANTERIOR,
  32. CALIFICACION_ACTUAL,
  33. CALIFICACION_GLOBAL,
  34. MES,
  35. CREATED_BY,
  36. UPDATED_BY,
  37. CREATED_DATE,
  38. UPDATED_DATE,
  39. EVALUACION_ID,
  40. CATEGORIAS_ID)
  41. VALUES (EV_DETAIL_SEQ.nextval,
  42. 'inicial',
  43. 'admin',
  44. 's',
  45. 4.00,
  46. 5.00,
  47. 6.00,
  48. TO_DATE('2019-' || i || '-25 19:00:00', 'YYYY-MM-DD HH24:MI:SS'),
  49. 'admin',
  50. 'admin',
  51. current_date,
  52. current_date,
  53. custom.id_evaluacion,
  54. custom.id_categoria);
  55. END LOOP;
  56. END LOOP;
  57. result_code := 0;
  58. -- COMMIT;
  59. EXCEPTION WHEN OTHERS
  60. THEN
  61. result_code := -1;
  62. ROLLBACK;
  63. RAISE;
  64. END;
  65. ;
  66. CREATE OR REPLACE PROCEDURE dash_global_by_kpi(pid_kpi IN NUMBER,
  67. result_code OUT NUMBER)
  68. AS
  69. paa_id INT := 0;
  70. BEGIN
  71. result_code := 0;
  72. MERGE INTO evaluacion_det orig
  73. USING (SELECT k.id AS kpi_id,
  74. k.nombre kpi_nombre,
  75. e.id eval_id,
  76. e.CALIFICACION,
  77. e.global,
  78. e.AREAS_ID,
  79. a.nombre area_nombre,
  80. a.meta,
  81. c.ID AS categorias_id,
  82. c.nombre cat_nombre,
  83. c.META_MENSUAL,
  84. (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria,
  85. ed.ID detail_id,
  86. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number,
  87. ed.CALIFICACION_ANTERIOR,
  88. ed.CALIFICACION_ACTUAL,
  89. ed.CALIFICACION_GLOBAL,
  90. ed.mes fecha
  91. FROM EVALUACION_DET ed
  92. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  93. JOIN areas a ON e.AREAS_ID = a.id
  94. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  95. JOIN kpi k ON a.KPI_ID = k.id
  96. AND k.id = pid_kpi) detail
  97. ON (orig.id = detail.detail_id)
  98. WHEN MATCHED THEN UPDATE SET orig.CALIFICACION_GLOBAL = detail.global_categoria;
  99. COMMIT;
  100. EXCEPTION WHEN OTHERS
  101. THEN
  102. result_code := -1;
  103. ROLLBACK;
  104. RAISE;
  105. END;
  106. ;
  107. CREATE OR REPLACE PROCEDURE dash_global_by_kpi_month(pid_kpi IN NUMBER,
  108. pmes IN NUMBER,
  109. result_code OUT NUMBER)
  110. AS
  111. BEGIN
  112. result_code := 0;
  113. MERGE INTO evaluacion_det orig
  114. USING (SELECT k.id AS kpi_id,
  115. k.nombre kpi_nombre,
  116. e.id eval_id,
  117. e.CALIFICACION,
  118. e.global,
  119. e.AREAS_ID,
  120. a.nombre area_nombre,
  121. a.meta,
  122. c.ID AS categorias_id,
  123. c.nombre cat_nombre,
  124. c.META_MENSUAL,
  125. (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria,
  126. ed.ID detail_id,
  127. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number,
  128. ed.CALIFICACION_ANTERIOR,
  129. ed.CALIFICACION_ACTUAL,
  130. ed.CALIFICACION_GLOBAL,
  131. ed.mes fecha
  132. FROM EVALUACION_DET ed
  133. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  134. JOIN areas a ON e.AREAS_ID = a.id
  135. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  136. JOIN kpi k ON a.KPI_ID = k.id AND k.id = pid_kpi
  137. WHERE to_number(EXTRACT(MONTH FROM ed.mes), 99) = pmes) detail
  138. ON (orig.id = detail.detail_id)
  139. WHEN MATCHED THEN UPDATE SET orig.CALIFICACION_GLOBAL = detail.global_categoria;
  140. COMMIT;
  141. EXCEPTION
  142. WHEN OTHERS
  143. THEN
  144. result_code := -1;
  145. ROLLBACK;
  146. RAISE;
  147. END;
  148. ;
  149. CREATE OR REPLACE PROCEDURE d_eval_global_month(pid_kpi IN NUMBER, pmes IN NUMBER,
  150. result_code OUT NUMBER)
  151. AS
  152. paa_id INT := 0;
  153. BEGIN
  154. result_code := 0;
  155. MERGE INTO evaluacion orig
  156. USING (SELECT k.id id_kpi, e.id id_eval, a.id, avg(ed.CALIFICACION_GLOBAL) AS average
  157. FROM EVALUACION_DET ed
  158. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  159. JOIN areas a ON e.AREAS_ID = a.id
  160. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  161. JOIN kpi k ON a.KPI_ID = k.id
  162. AND k.id = pid_kpi
  163. WHERE to_number(EXTRACT(MONTH FROM ed.mes), 99) = pmes
  164. GROUP BY k.id, e.id, a.id, to_number(EXTRACT(MONTH FROM ed.mes), 99)) detail
  165. ON (orig.id = detail.id_eval)
  166. WHEN MATCHED THEN UPDATE SET orig.GLOBAL = detail.average;
  167. COMMIT;
  168. EXCEPTION WHEN OTHERS
  169. THEN
  170. result_code := -1;
  171. ROLLBACK;
  172. RAISE;
  173. END;
  174. ;
  175. CREATE OR REPLACE PROCEDURE get_detail_month(pkpi_id IN NUMBER, pmes IN NUMBER, p_cursor IN OUT SYS_REFCURSOR)
  176. AS
  177. BEGIN
  178. OPEN p_cursor FOR
  179. SELECT k.id AS kpi_id,
  180. k.nombre kpi_nombre,
  181. e.id eval_id,
  182. e.global global_actual,
  183. e.AREAS_ID,
  184. a.nombre area_nombre,
  185. c.ID AS categorias_id,
  186. c.nombre cat_nombre,
  187. c.META_MENSUAL,
  188. (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria,
  189. ed.ID detail_id,
  190. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number,
  191. ed.CALIFICACION_ACTUAL,
  192. ed.CALIFICACION_GLOBAL
  193. FROM EVALUACION_DET ed
  194. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  195. JOIN areas a ON e.AREAS_ID = a.id
  196. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  197. JOIN kpi k ON a.KPI_ID = k.id AND k.id = pkpi_id
  198. WHERE to_number(EXTRACT(MONTH FROM ed.mes), 99) = pmes
  199. ORDER BY k.id, e.id, a.id, c.id, to_number(EXTRACT(MONTH FROM ed.mes), 99);
  200. END;
  201. ;
  202. CREATE OR REPLACE PROCEDURE get_detail_global(pkpi_id IN NUMBER, p_cursor IN OUT SYS_REFCURSOR)
  203. AS
  204. BEGIN
  205. OPEN p_cursor FOR
  206. SELECT k.id AS kpi_id,
  207. k.nombre kpi_nombre,
  208. e.id eval_id,
  209. e.global global_actual,
  210. e.AREAS_ID,
  211. a.nombre area_nombre,
  212. c.ID AS categorias_id,
  213. c.nombre cat_nombre,
  214. c.META_MENSUAL,
  215. (ed.CALIFICACION_ACTUAL / c.META_MENSUAL) global_categoria,
  216. ed.ID detail_id,
  217. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes_number,
  218. ed.CALIFICACION_ACTUAL,
  219. ed.CALIFICACION_GLOBAL
  220. FROM EVALUACION_DET ed
  221. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  222. JOIN areas a ON e.AREAS_ID = a.id
  223. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  224. JOIN kpi k ON a.KPI_ID = k.id AND k.id = pkpi_id
  225. ORDER BY k.id, e.id, a.id, c.id, to_number(EXTRACT(MONTH FROM ed.mes), 99);
  226. END;
  227. ;
  228. CREATE OR REPLACE PROCEDURE get_detail_global_month(pkpi_id IN NUMBER, p_cursor IN OUT SYS_REFCURSOR)
  229. AS
  230. BEGIN
  231. OPEN p_cursor FOR
  232. SELECT kpi_id, mes, avg(promedio_area) AS promedio
  233. FROM (SELECT k.id AS kpi_id,
  234. e.id eval_id,
  235. a.id areas_id,
  236. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes,
  237. avg(ed.CALIFICACION_GLOBAL) promedio_area
  238. FROM EVALUACION_DET ed
  239. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  240. JOIN areas a ON e.AREAS_ID = a.id
  241. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  242. JOIN kpi k ON a.KPI_ID = k.id AND k.id = pkpi_id
  243. GROUP BY k.id, e.id, a.id, to_number(EXTRACT(MONTH FROM ed.mes), 99))
  244. GROUP BY kpi_id, mes
  245. ORDER BY kpi_id, mes;
  246. END;
  247. ;
  248. CREATE OR REPLACE PROCEDURE pc_create_up_scorecard(p_provider_id IN INTEGER,
  249. p_year IN INTEGER,
  250. p_month IN INTEGER)
  251. IS
  252. CURSOR cur_score_cate (p_provider_id IN scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  253. p_month IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  254. p_year IN scorecard_PROVIDER.YEAR%TYPE) is
  255. SELECT to_number(EXTRACT(MONTH FROM ed.mes), 99) mes,
  256. to_number(EXTRACT(YEAR FROM ed.mes), 9999) YEAR,
  257. p.NOMBRE,a.NOMBRE,c.NOMBRE,
  258. p.id,
  259. a.ID,c.id,
  260. avg(ed.CALIFICACION_ACTUAL) promedio_area
  261. FROM EVALUACION_DET ed
  262. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  263. JOIN areas a ON e.AREAS_ID = a.id
  264. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  265. JOIN kpi k ON a.KPI_ID = k.id
  266. JOIN PROVEEDORES P on e.PROVEEDORES_ID = P.ID
  267. WHERE p.id = p_provider_id
  268. AND to_number(EXTRACT(MONTH FROM ed.mes), 99) = p_month
  269. AND to_number(EXTRACT(year FROM ed.mes), 9999) = p_year
  270. GROUP BY to_number(EXTRACT(MONTH FROM ed.mes), 99),
  271. to_number(EXTRACT(YEAR FROM ed.mes), 9999),
  272. p.NOMBRE,a.NOMBRE,c.NOMBRE,
  273. p.id,
  274. a.ID,c.id
  275. order by mes;
  276. CURSOR cur_score_area (
  277. p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  278. p_month SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  279. p_year scorecard_PROVIDER.YEAR%TYPE,
  280. p_area_id IN scorecard_PROVIDER.YEAR%TYPE) is
  281. SELECT avg(ed.CALIFICACION_ACTUAL)
  282. FROM EVALUACION_DET ed
  283. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  284. JOIN areas a ON e.AREAS_ID = a.id
  285. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  286. JOIN PROVEEDORES P on e.PROVEEDORES_ID = P.ID
  287. WHERE p.id = p_provider_id
  288. AND to_number(EXTRACT(MONTH FROM ed.mes), 99) = p_month
  289. AND to_number(EXTRACT(year FROM ed.mes), 9999) = p_year
  290. and a.id = p_area_id;
  291. CURSOR cur_score_global (p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  292. p_month SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  293. p_year scorecard_PROVIDER.YEAR%TYPE) is
  294. select avg(promedio_categoria),NOMBRE
  295. from (SELECT e.id eval_id,
  296. c.id categoria_id,
  297. to_number(EXTRACT(MONTH FROM ed.mes), 99) mes,
  298. to_number(EXTRACT(YEAR FROM ed.mes), 9999) YEAR,
  299. p.NOMBRE,
  300. p.id provedorId,
  301. avg(ed.CALIFICACION_ACTUAL) promedio_categoria
  302. FROM EVALUACION_DET ed
  303. JOIN EVALUACION e ON ed.EVALUACION_ID = e.id
  304. JOIN categorias c ON ed.CATEGORIAS_ID = c.id
  305. JOIN PROVEEDORES P on e.PROVEEDORES_ID = P.ID
  306. where p.id = p_provider_id
  307. and to_number(EXTRACT(MONTH FROM ed.mes), 99) = p_month
  308. AND to_number(EXTRACT(year FROM ed.mes), 9999) = p_year
  309. GROUP BY e.id, c.id, p.nombre, p.id, to_number(EXTRACT(MONTH FROM ed.mes), 99),
  310. to_number(EXTRACT(YEAR FROM ed.mes), 9999))
  311. group by YEAR, NOMBRE, provedorId; --mes,
  312. CURSOR cur_score_year (
  313. p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  314. p_month IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  315. p_year scorecard_PROVIDER.YEAR%TYPE) is
  316. select p.id, p.LAST_SCORE
  317. from SCORECARD_PROVIDER p
  318. where p.PROVEEDORES_ID = p_provider_id
  319. and p.YEAR = p_year
  320. and p.MONTH = p_month;
  321. CURSOR cur_evaluaciones (
  322. p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  323. p_month IN SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  324. p_year scorecard_PROVIDER.YEAR%TYPE) is
  325. select ee.EVALUACION_PROVEEDOR_ID
  326. from EVALUACION_DET e,evaluacion ee
  327. where ee.PROVEEDORES_ID = p_provider_id
  328. and ee.YEAR = p_year
  329. and to_number(to_char(e.mes,'MM')) = p_month;
  330. CURSOR cur_quiz_comments
  331. (p_provider_id scorecard_PROVIDER.PROVEEDORES_ID%TYPE,
  332. p_month SCORECARD_PROVIDER_MONTH.MONTH%TYPE,
  333. p_year scorecard_PROVIDER.YEAR%TYPE)
  334. is
  335. select d.id,d.comentario,d.CREATED_DATE,d.CREATED_BY
  336. from EVALUACION_DET_COMMENTARIOS d
  337. join evaluacion_det e on e.id =d.EVALUACION_DET_ID
  338. join evaluacion x on x.id = e.EVALUACION_ID
  339. and x.PROVEEDORES_ID = p_provider_id
  340. and to_number(EXTRACT(MONTH FROM e.mes), 99) = p_month
  341. AND to_number(EXTRACT(year FROM e.mes), 9999) = p_year;
  342. L_NAME scorecard_PROVIDER.NAME%TYPE;
  343. L_LAST_SCORE_AREA SCORECARD_PROVIDER_MONTH.SCORE%TYPE;
  344. L_LAST_SCORE scorecard_PROVIDER.LAST_SCORE%TYPE; -- CATGORIAS
  345. L_BEFORE_SCORE scorecard_PROVIDER.BEFORE_SCORE%TYPE;
  346. L_TOTAL_AVG scorecard_PROVIDER.TOTAL_AVG%TYPE;
  347. L_TARGET scorecard_PROVIDER.TARGET%TYPE:=5;
  348. L_YEAR scorecard_PROVIDER.YEAR%TYPE:=p_year;
  349. L_CREATED_DATE scorecard_PROVIDER.CREATED_DATE%TYPE;
  350. L_PROVEEDORES_ID scorecard_PROVIDER.PROVEEDORES_ID%TYPE:=p_provider_id;
  351. L_SCORECARD_PROVIDER SCORECARD_PROVIDER.PROVEEDORES_ID%TYPE;
  352. L_MONTH SCORECARD_PROVIDER_MONTH.MONTH%TYPE:=p_month;
  353. L_SCORECARD_PROVIDER_ID SCORECARD_PROVIDER.ID%TYPE;
  354. L_NOMBRE_AREA scorecard_PROVIDER_MONTH.NOMBRE_AREA%TYPE;
  355. L_NOMBRE_CATEGORIA scorecard_PROVIDER_MONTH.NOMBRE_CATEGORIA%TYPE;
  356. L_ID_AREA AREAS.ID%TYPE;
  357. L_ID_CATEGORIA CATEGORIAS.ID%TYPE;
  358. L_COMMENT EVALUACION_DET_COMMENTARIOS.COMENTARIO%TYPE;
  359. L_COMMENT_ID EVALUACION_DET_COMMENTARIOS.ID%TYPE;
  360. L_COMMENT_CREATED_DATE EVALUACION_DET_COMMENTARIOS.CREATED_DATE%TYPE;
  361. L_CREATED_BY EVALUACION_DET_COMMENTARIOS.CREATED_BY%TYPE;
  362. L_EVALUACION_PROVEEDOR_ID EVALUACION_PROVEEDOR.ID%TYPE;
  363. BEGIN
  364. OPEN cur_score_global(p_provider_id,p_month,p_year);
  365. FETCH cur_score_global INTO L_TOTAL_AVG,L_NAME;
  366. CLOSE cur_score_global;
  367. OPEN cur_score_year (p_provider_id,p_month,p_year);
  368. FETCH cur_score_year INTO L_SCORECARD_PROVIDER_ID, L_LAST_SCORE;
  369. CLOSE cur_score_year;
  370. if L_SCORECARD_PROVIDER_ID > 0
  371. then
  372. UPDATE SCORECARD_PROVIDER
  373. SET NAME = L_NAME,
  374. LAST_SCORE = L_TOTAL_AVG,
  375. BEFORE_SCORE = L_BEFORE_SCORE,
  376. TOTAL_AVG = L_TOTAL_AVG,
  377. TARGET = L_TARGET,
  378. YEAR = L_YEAR,
  379. UPDATED_DATE = CURRENT_DATE
  380. WHERE ID = L_SCORECARD_PROVIDER_ID;
  381. else
  382. L_SCORECARD_PROVIDER_ID := scorecard_provider_seq.nextval;
  383. INSERT INTO SCORECARD_PROVIDER (ID,
  384. NAME,
  385. LAST_SCORE,
  386. BEFORE_SCORE,
  387. TOTAL_AVG,
  388. TARGET,
  389. YEAR,
  390. CREATED_DATE,
  391. PROVEEDORES_ID,
  392. STATUS,
  393. MONTH)
  394. values( L_SCORECARD_PROVIDER_ID,
  395. L_NAME,
  396. L_TOTAL_AVG,
  397. 0,
  398. L_TOTAL_AVG,--L_LAST_SCORE,
  399. L_TARGET,--L_BEFORE_SCORE,
  400. p_year,
  401. CURRENT_DATE,
  402. p_provider_id,
  403. 'NO INICIADO',
  404. p_month)
  405. ;
  406. end if;
  407. delete SCORECARD_PROVIDER_MONTH where SCORECARD_PROVIDER_ID = L_SCORECARD_PROVIDER_ID;
  408. OPEN cur_score_cate(p_provider_id, p_month, p_year);
  409. LOOP
  410. FETCH cur_score_cate INTO L_MONTH,L_YEAR,
  411. L_NAME,L_NOMBRE_AREA,L_NOMBRE_CATEGORIA,L_PROVEEDORES_ID,L_ID_AREA,L_ID_CATEGORIA,
  412. L_LAST_SCORE;
  413. EXIT WHEN cur_score_cate%NOTFOUND OR cur_score_cate%NOTFOUND IS NULL;
  414. open cur_score_area(p_provider_id, p_month, p_year,L_ID_AREA);--nota agrupada de las categorias al area
  415. FETCH cur_score_area INTO L_LAST_SCORE_AREA;
  416. CLOSE cur_score_area;
  417. INSERT INTO SCORECARD_PROVIDER_MONTH (ID,
  418. MONTH,
  419. SCORE,
  420. BEFORE_SCORE,
  421. SCORE_CATEGORY,
  422. BEFORE_SCORE_CATEGORY,
  423. CREATED_DATE,
  424. SCORECARD_PROVIDER_ID,
  425. ESTADO,
  426. NOMBRE_AREA,
  427. NOMBRE_CATEGORIA,
  428. ID_AREA,ID_CATEGORIA)
  429. VALUES (scorecard_provider_month_seq.nextval,
  430. L_MONTH,
  431. L_LAST_SCORE_AREA,
  432. L_LAST_SCORE_AREA,
  433. L_LAST_SCORE,
  434. L_LAST_SCORE,
  435. CURRENT_DATE,
  436. L_SCORECARD_PROVIDER_ID,
  437. 'NO INICIADO',
  438. L_NOMBRE_AREA,
  439. L_NOMBRE_CATEGORIA,
  440. L_ID_AREA,L_ID_CATEGORIA);
  441. END LOOP;
  442. close cur_score_cate;
  443. delete SCORECARD_PROVIDER_COMMENTS where SCORECARD_PROVIDER_ID = L_SCORECARD_PROVIDER_ID;
  444. OPEN cur_quiz_comments (p_provider_id,p_month,p_year);
  445. LOOP
  446. FETCH cur_quiz_comments INTO L_COMMENT_ID,L_COMMENT,L_COMMENT_CREATED_DATE,L_CREATED_BY;
  447. EXIT WHEN cur_quiz_comments%NOTFOUND OR cur_quiz_comments%NOTFOUND IS NULL;
  448. delete SCORECARD_PROVIDER_COMMENTS where id = L_COMMENT_ID;
  449. INSERT INTO SCORECARD_PROVIDER_COMMENTS (ID,
  450. COMMENTS,
  451. CREATED_DATE,
  452. CREATED_USER,
  453. SCORECARD_PROVIDER_ID)
  454. VALUES (L_COMMENT_ID,
  455. L_COMMENT,
  456. L_COMMENT_CREATED_DATE,
  457. L_CREATED_BY,
  458. L_SCORECARD_PROVIDER_ID
  459. );
  460. END LOOP ;
  461. CLOSE cur_quiz_comments;
  462. OPEN cur_evaluaciones(p_provider_id, p_month, p_year);
  463. LOOP
  464. FETCH cur_evaluaciones INTO L_EVALUACION_PROVEEDOR_ID;
  465. EXIT WHEN cur_evaluaciones%NOTFOUND OR cur_evaluaciones%NOTFOUND IS NULL;
  466. UPDATE EVALUACION_PROVEEDOR SET SCORECARD_ID = L_SCORECARD_PROVIDER_ID,ESTADO='EN PROGRESO',UPDATED_DATE=CURRENT_DATE
  467. where ID=L_EVALUACION_PROVEEDOR_ID and year = p_year AND MONTH = p_month;
  468. END LOOP ;
  469. CLOSE cur_evaluaciones;
  470. END pc_create_up_scorecard
  471. ;
  472. ;
  473. CREATE OR REPLACE PROCEDURE GET_RESUME_SCORE_AREA(
  474. p_scoreprovider_id IN SCORECARD_PROVIDER.ID%TYPE
  475. , p_cursor IN OUT SYS_REFCURSOR)
  476. IS
  477. BEGIN
  478. open p_cursor for
  479. select s.ID_AREA as idArea, s.nombre_area as name, s.score as score
  480. from scorecard_provider_month s
  481. where scorecard_provider_id = p_scoreprovider_id
  482. group by s.ID_AREA, s.nombre_area, s.score;
  483. --
  484. END GET_RESUME_SCORE_AREA;
  485. ;
  486. CREATE OR REPLACE PROCEDURE GET_RESUME_LASTEST_MONTHS(
  487. p_provider_id IN SCORECARD_PROVIDER.ID%TYPE,
  488. p_year in INTEGER,
  489. p_cursor IN OUT SYS_REFCURSOR)
  490. IS
  491. BEGIN
  492. open p_cursor for
  493. select p.MONTH as name, (p.TOTAL_AVG / 5 * 100) as score
  494. from scorecard_provider p
  495. where p.proveedores_id = p_provider_id
  496. and p.year = p_year
  497. and p.month > 0
  498. order by p.MONTH;
  499. END GET_RESUME_LASTEST_MONTHS;
  500. ;
  501. CREATE OR REPLACE PROCEDURE GET_SCOREPRO_CATEGORY_SCORES(
  502. p_scoreprovider_id IN SCORECARD_PROVIDER.ID%TYPE,
  503. p_area_id IN SCORECARD_PROVIDER_MONTH.ID_AREA%TYPE,
  504. p_cursor IN OUT SYS_REFCURSOR)
  505. IS
  506. BEGIN
  507. open p_cursor for
  508. select s.NOMBRE_CATEGORIA as nombre, s.NOMBRE_CATEGORIA as name, s.SCORE_CATEGORY as score
  509. from scorecard_provider_month s
  510. where s.scorecard_provider_id = p_scoreprovider_id
  511. and s.ID_AREA = p_area_id;
  512. --and p.status = 'APROBADO';
  513. END GET_SCOREPRO_CATEGORY_SCORES;
  514. ;
  515. CREATE OR REPLACE PROCEDURE P_GET_TREND_GLOBAL_MONTH(p_providerId IN NUMBER, p_cursor IN OUT SYS_REFCURSOR)
  516. AS
  517. BEGIN
  518. OPEN p_cursor FOR
  519. select p.id,
  520. p.name,
  521. p.LAST_SCORE *100/5 last_score,
  522. p.BEFORE_SCORE,
  523. p.TOTAL_AVG,
  524. p.TARGET,
  525. p.YEAR,
  526. p.created_date,
  527. p.updated_date,
  528. p.PROVEEDORES_ID,
  529. p.MONTH,
  530. p.status,
  531. p.REJECT_REASON
  532. from scorecard_provider p
  533. where p.PROVEEDORES_ID = p_providerId
  534. and status ='PUBLICADO'
  535. order BY TO_NUMBER(p.YEAR), TO_NUMBER(p.MONTH) ASC;
  536. END;
  537. create or replace PROCEDURE P_GET_DETAIL_BY_PROVIDER(
  538. p_provider_id IN NUMBER,
  539. p_year in number,
  540. p_cursor IN OUT SYS_REFCURSOR
  541. )
  542. AS
  543. BEGIN
  544. OPEN p_cursor FOR
  545. select 1 as kpi_id,
  546. m.nombre_area kpi_nombre,
  547. 1 as evalId,
  548. p.TOTAL_AVG global_actual,
  549. m.ID_AREA areas_id,
  550. m.NOMBRE_AREA area_nombre,
  551. m.ID_CATEGORIA categoria_id,
  552. m.NOMBRE_CATEGORIA cat_nombre,
  553. p.TARGET meta,
  554. (m.SCORE_CATEGORY / 5 ) global_categoria,
  555. 1 detail_id,
  556. m.MONTH mes_number,
  557. p.YEAR,
  558. m.SCORE calificacion_actual,
  559. p.LAST_SCORE calificacion_global
  560. from SCORECARD_PROVIDER p
  561. join SCORECARD_PROVIDER_MONTH m on m.SCORECARD_PROVIDER_ID = p.id
  562. where p.PROVEEDORES_ID = p_provider_id
  563. and p.TOTAL_AVG is not null
  564. and p.STATUS = 'PUBLICADO'
  565. order by TO_NUMBER(p.YEAR),TO_NUMBER(m.MONTH),m.ID_AREA,m.ID_CATEGORIA ;
  566. END
  567. ;
  568. ;
  569. CREATE OR REPLACE PROCEDURE P_GET_PROGRESS_ACTION_PLAN(
  570. p_providerId IN NUMBER,
  571. p_year IN NUMBER,
  572. p_cursor IN OUT SYS_REFCURSOR)
  573. AS
  574. BEGIN
  575. OPEN p_cursor FOR
  576. SELECT avg(avance_proveedor) progress from PLAN_ACCION
  577. where PROVEEDOR_ID = p_providerId
  578. AND ESTADO = 'EN PROGRESO';
  579. -- AND YEAR =
  580. --GROUP BY PROVEEDOR_ID;
  581. END;
  582. CREATE OR REPLACE PROCEDURE P_GET_ACTION_PLAN_STATUS(
  583. p_providerId IN NUMBER,
  584. p_year IN NUMBER,
  585. p_cursor IN OUT SYS_REFCURSOR)
  586. AS
  587. BEGIN
  588. OPEN p_cursor FOR
  589. select p.ESTADO,COUNT(*) qty from PLAN_ACCION p
  590. where PROVEEDOR_ID = p_providerId
  591. -- AND YEAR =
  592. group by p.ESTADO;
  593. END;
  594. create OR REPLACE PROCEDURE P_GET_GLOBAL_AVG(
  595. p_providerId IN NUMBER,
  596. p_year IN NUMBER,
  597. p_cursor IN OUT SYS_REFCURSOR)
  598. AS
  599. BEGIN
  600. OPEN p_cursor FOR
  601. select sum(last_score)/count(*) global_avg
  602. from SCORECARD_PROVIDER p
  603. where p.PROVEEDORES_ID =p_providerId
  604. and p.STATUS = 'PUBLICADO'
  605. AND p.year = p_year
  606. ;
  607. END;
  608. ;
  609. CREATE OR REPLACE PROCEDURE P_GET_PEN_QUIZ_USERS( p_cursor IN OUT SYS_REFCURSOR)
  610. as
  611. BEGIN
  612. OPEN p_cursor FOR
  613. select u.USER_NAME,u.USER_TYPE,p.nombre,p.id
  614. from proveedores p
  615. join USERS_PROVEEDORES up
  616. on up.PROVEEDORES_ID = p.id
  617. join users u
  618. on u.id = up.USERS_ID
  619. where UPPER(u.USER_TYPE) in
  620. ('CLIENTE INTERNO')
  621. and not exists(
  622. select 'x' from EVALUACION_PROVEEDOR ep
  623. where ep.CREATED_BY = u.USER_NAME
  624. and ep.year = to_char(add_months(sysdate, -1), 'YYYY')
  625. and ep.month = to_char(add_months(sysdate, -1), 'MM')
  626. )
  627. ;
  628. end;
  629. ;
  630. CREATE OR REPLACE PROCEDURE P_GET_PEN_ACTION_EXECUTE( p_cursor IN OUT SYS_REFCURSOR)
  631. as
  632. BEGIN
  633. OPEN p_cursor FOR
  634. select * from PLAN_ACCION_DET a
  635. where a.ESTADO != 'FINALIZADO'
  636. and a.FECHA_COMPROMISO<=CURRENT_DATE;
  637. end;
  638. ;
  639. CREATE OR REPLACE PROCEDURE P_GET_HISTORIC_SCORE(
  640. p_provider_id in integer,
  641. p_year in integer,
  642. p_cursor IN OUT SYS_REFCURSOR)
  643. as
  644. BEGIN
  645. OPEN p_cursor FOR
  646. select p.year,pm.nombre_area,pm.nombre_categoria,
  647. '5' META, p.last_score Actual, (p.total_avg*100/5)||'%' Global,
  648. nvl(pm.enero,0) ENERO,nvl(pm.FEBRERO,0) Febrero,nvl(pm.marzo,0) Marzo,
  649. nvl(pm.abril,0) Abril,nvl(pm.mayo,0) Mayo,
  650. nvl(pm.junio,0) Junio,nvl(pm.julio,0) Julio,
  651. nvl(pm.AGOSTO,0) Agosto,nvl(pm.SEPTIEMBRE,0) Septiembre,
  652. nvl(pm.OCTUBRE,0) Octubre,nvl(pm.NOVIEMBRE,0) Noviembre,nvl(pm.DICIEMBRE,0) DICIEMBRE
  653. from (
  654. select *
  655. from SCORECARD_PROVIDER_MONTH
  656. PIVOT(
  657. sum(nvl(score,0))
  658. FOR month
  659. IN (
  660. '1' Enero,'2' Febrero,'3' Marzo,'4' Abril,'5' Mayo,'6' Junio,'7' Julio,
  661. '8' Agosto,'9' Septiembre,'10' Octubre,'11' Noviembre,'12' Diciembre
  662. )
  663. )) pm join SCORECARD_PROVIDER p on p.id = pm.scorecard_provider_id
  664. where p.proveedores_id = p_provider_id
  665. and p.year = p_year
  666. order by pm.nombre_area;
  667. end;
  668. ;
  669. CREATE OR REPLACE PROCEDURE P_AVG_PROGRESS_ACTION_PLAN(
  670. p_plan_id in integer)
  671. as
  672. cursor cur_avg (p_plan_id in integer)
  673. is
  674. select avg(d.AVANCE) avg from plan_accion p
  675. join PLAN_ACCION_DET d on p.id = d.PLAN_ACCION_ID
  676. and d.PARENT_ID is not null
  677. where p.id = p_plan_id;
  678. L_TOTAL_AVG NUMBER(5, 2);
  679. BEGIN
  680. OPEN cur_avg(p_plan_id);
  681. FETCH cur_avg INTO L_TOTAL_AVG;
  682. CLOSE cur_avg;
  683. update PLAN_ACCION set AVANCE_PROVEEDOR = L_TOTAL_AVG,AVANCE_CM=L_TOTAL_AVG
  684. WHERE id =p_plan_id;
  685. end;
  686. ;
  687. CREATE OR REPLACE PROCEDURE P_AVG_SCORECARD_PROVIDER(
  688. p_scorecard_provider_id in integer,
  689. p_username in varchar2)
  690. as
  691. cursor cur_avg_area (p_scorecard_provider_id in integer)
  692. is
  693. select nvl(avg(SCORE_CATEGORY),0), ID_AREA
  694. from SCORECARD_PROVIDER_MONTH m
  695. where m.SCORECARD_PROVIDER_ID = p_scorecard_provider_id
  696. group by ID_AREA;
  697. cursor cur_tot_avg (p_scorecard_provider_id in integer)
  698. is
  699. select nvl(avg(SCORE_CATEGORY),0)
  700. from SCORECARD_PROVIDER_MONTH m
  701. where m.SCORECARD_PROVIDER_ID = p_scorecard_provider_id
  702. ;
  703. L_TOTAL_AVG NUMBER(5, 2);
  704. L_TOTAL_AREA_AVG NUMBER(5,2);
  705. L_AREA_ID number(5,2);
  706. BEGIN
  707. OPEN cur_tot_avg(p_scorecard_provider_id);
  708. FETCH cur_tot_avg INTO L_TOTAL_AVG;
  709. CLOSE cur_tot_avg;
  710. UPDATE SCORECARD_PROVIDER SET TOTAL_AVG = L_TOTAL_AVG,LAST_SCORE=L_TOTAL_AVG,
  711. UPDATED_DATE=current_date,UPDATED_BY=p_username
  712. where ID = p_scorecard_provider_id;
  713. OPEN cur_avg_area (p_scorecard_provider_id);
  714. LOOP
  715. FETCH cur_avg_area INTO L_TOTAL_AREA_AVG,L_AREA_ID;
  716. EXIT WHEN cur_avg_area%NOTFOUND OR cur_avg_area%NOTFOUND IS NULL;
  717. update SCORECARD_PROVIDER_MONTH
  718. set score = L_TOTAL_AREA_AVG,UPDATED_DATE=CURRENT_DATE
  719. where SCORECARD_PROVIDER_ID = p_scorecard_provider_id
  720. and ID_AREA = L_AREA_ID;
  721. END LOOP ;
  722. CLOSE cur_avg_area;
  723. end;
  724. ;
  725. CREATE OR REPLACE PROCEDURE P_GET_HISTORIC_SCORE(
  726. p_action_id in integer)
  727. AS
  728. DECLARE
  729. L_TOTAL_AVG NUMBER(5, 2);
  730. L_PARENT_ID NUMBER;
  731. BEGIN
  732. select nvl(avg(avance),0),DISTINCT(PARENT_ID) into L_TOTAL_AVG ,L_PARENT_ID from PLAN_ACCION_DET
  733. where PARENT_ID is NOT null
  734. and id = p_action_id;
  735. UPDATE plan_accion set AVANCE_PROVEEDOR = L_TOTAL_AVG
  736. where id = L_PARENT_ID;
  737. end;
  738. ;
  739. CREATE OR REPLACE PROCEDURE P_PROCESS_AVG_ACTION_PLAN(
  740. p_action_id in integer,
  741. p_username in varchar2)
  742. AS
  743. L_TOTAL_AVG NUMBER(5, 2);
  744. L_PARENT_ID NUMBER;
  745. L_PLAN_ACCION_ID NUMBER;
  746. BEGIN
  747. select PARENT_ID,PLAN_ACCION_ID into L_PARENT_ID,L_PLAN_ACCION_ID
  748. from PLAN_ACCION_DET where id = p_action_id;
  749. select avg(nvl(avance,0)) into L_TOTAL_AVG
  750. from PLAN_ACCION_DET
  751. where PARENT_ID = L_PARENT_ID
  752. group by PARENT_ID ;
  753. UPDATE PLAN_ACCION_DET set AVANCE = L_TOTAL_AVG, UPDATED_BY=p_username
  754. where PLAN_ACCION_ID = L_PLAN_ACCION_ID and PARENT_ID is null;
  755. select PLAN_ACCION_ID,avg(nvl(avance,0)) into L_PARENT_ID, L_TOTAL_AVG
  756. from PLAN_ACCION_DET
  757. where PLAN_ACCION_ID = L_PLAN_ACCION_ID and PARENT_ID is null
  758. group by PLAN_ACCION_ID ;
  759. UPDATE PLAN_ACCION SET AVANCE_PROVEEDOR= L_TOTAL_AVG,AVANCE_CM= L_TOTAL_AVG
  760. where ID = L_PLAN_ACCION_ID;
  761. end;