[Home] [Help]
PACKAGE BODY: APPS.AMW_PROCCERT_EVENT_PVT
Source
1 PACKAGE BODY AMW_PROCCERT_EVENT_PVT AS
2 /* $Header: amwvpceb.pls 120.11 2005/11/17 20:36:10 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_PROCCERT_EVENT_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 g_pkg_name CONSTANT VARCHAR2 (30) := 'AMW_PROCCERT_EVENT_PVT';
16 g_file_name CONSTANT VARCHAR2 (12) := 'amwvpceb.pls';
17 l_index number;
18
19 PROCEDURE populate_assoc_opinion (
20 p_certification_id IN NUMBER)
21 IS
22 CURSOR c_obj_opin_type_id (c_obj_name VARCHAR2) IS
23 SELECT aoot.object_opinion_type_id
24 FROM AMW_OBJECT_OPINION_TYPES aoot, AMW_OPINION_TYPES_B aot,
25 FND_OBJECTS fo
26 WHERE aoot.OPINION_TYPE_ID = aot.OPINION_TYPE_ID
27 AND aoot.OBJECT_ID = fo.OBJECT_ID
28 AND aot.opinion_type_code = 'EVALUATION'
29 AND fo.obj_name = c_obj_name;
30
31 l_obj_opinion_type_id NUMBER;
32 BEGIN
33
34 OPEN c_obj_opin_type_id ('AMW_ORG_PROCESS_RISK');
35 FETCH c_obj_opin_type_id INTO l_obj_opinion_type_id;
36 CLOSE c_obj_opin_type_id;
37
38 UPDATE amw_risk_associations assoc
39 SET last_update_date = sysdate,
40 last_updated_by = fnd_global.user_id,
41 last_update_login = fnd_global.conc_login_id,
42 pk4 = (SELECT max(opinion_log_id)
43 FROM amw_opinions_log opin
44 WHERE opin.object_opinion_type_id = l_obj_opinion_type_id
45 AND opin.pk1_value = assoc.risk_id
46 AND opin.pk3_value = assoc.pk2 -- organization_id
47 AND NVL(opin.pk4_value, -1)
48 = NVL(assoc.pk3, -1)) -- process_id
49 WHERE pk1 = p_certification_id
50 AND pk4 IS NULL;
51
52
53
54 OPEN c_obj_opin_type_id ('AMW_ORG_CONTROL');
55 FETCH c_obj_opin_type_id INTO l_obj_opinion_type_id;
56 CLOSE c_obj_opin_type_id;
57
58 UPDATE amw_control_associations assoc
59 SET last_update_date = sysdate,
60 last_updated_by = fnd_global.user_id,
61 last_update_login = fnd_global.conc_login_id,
62 pk5 = (SELECT max(opinion_log_id)
63 FROM amw_opinions_log opin
64 WHERE opin.object_opinion_type_id = l_obj_opinion_type_id
65 AND opin.pk1_value = assoc.control_id
66 AND opin.pk3_value = assoc.pk2) -- organization_id
67 WHERE pk1 = p_certification_id
68 AND pk5 IS NULL;
69
70 OPEN c_obj_opin_type_id ('AMW_ORG_AP_CONTROL');
71 FETCH c_obj_opin_type_id INTO l_obj_opinion_type_id;
72 CLOSE c_obj_opin_type_id;
73
74 UPDATE amw_ap_associations assoc
75 SET last_update_date = sysdate,
76 last_updated_by = fnd_global.user_id,
77 last_update_login = fnd_global.conc_login_id,
78 pk4 = (SELECT max(opinion_log_id)
79 FROM amw_opinions_log opin
80 WHERE opin.object_opinion_type_id = l_obj_opinion_type_id
81 AND opin.pk1_value = assoc.pk3
82 AND opin.pk3_value = assoc.pk2 -- organization_id
83 AND opin.pk4_value = assoc.audit_procedure_id) -- control_id
84 WHERE pk1 = p_certification_id
85 AND pk4 IS NULL;
86 END populate_assoc_opinion;
87
88
89 FUNCTION Scope_Update
90 ( p_subscription_guid in raw,
91 p_event in out NOCOPY wf_event_t
92 ) return VARCHAR2
93 IS
94 CURSOR c_new_org(c_cert_id NUMBER) IS
95 SELECT organization_id
96 FROM amw_org_cert_eval_sum
97 WHERE certification_id = c_cert_id
98 AND unmitigated_risks IS NULL;
99
100 CURSOR c_new_proc(c_cert_id NUMBER) IS
101 SELECT organization_id, process_id
102 FROM amw_proc_cert_eval_sum
103 WHERE certification_id = c_cert_id
104 AND unmitigated_risks IS NULL;
105
106 CURSOR c_org_proc(c_cert_id NUMBER, c_org_id NUMBER) IS
107 SELECT process_id
108 FROM amw_proc_cert_eval_sum
109 WHERE certification_id = c_cert_id
110 AND organization_id = c_org_id;
111
112 CURSOR c_start_date(c_cert_id NUMBER) IS
113 SELECT period.start_date
114 FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
115 WHERE cert.certification_period_name = period.period_name
116 AND cert.certification_period_set_name = period.period_set_name
117 AND cert.certification_id = c_cert_id;
118
119 CURSOR c_fch_vs_id IS
120 select flex_value_set_id
121 from fnd_flex_value_sets
122 where flex_value_set_name = 'FCH_ICM_ENTITY_VALUE_SET';
123
124 l_cert_id NUMBER;
125 l_org_id NUMBER;
126 l_mode VARCHAR2(30);
127 l_start_date DATE;
128
129 l_return_status VARCHAR2(30);
130 l_msg_count_char VARCHAR2(2000);
131 l_msg_count NUMBER;
132 l_msg_data VARCHAR2(2000);
133
134 l_fch_vs_id number;
135
136 l_errbuf VARCHAR2(2000);
137 l_retcode NUMBER;
138
139 BEGIN
140
141 SAVEPOINT Scope_Update_Event;
142
143 l_cert_id := p_event.GetValueForParameter('CERTIFICATION_ID');
144 l_mode := p_event.GetValueForParameter('MODE');
145
146 IF l_mode = 'AddToScope' THEN
147 -- to support org hierarchy, need to update the org denorm
148 -- for all the orgs in the certification
149 AMW_ORG_CERT_EVAL_SUM_PVT.populate_org_cert_sum_spec (
150 p_certification_id => l_cert_id);
151
152 -- do not need to update the prcess denorm for the existing
153 -- org.
154 FOR proc_rec IN c_new_proc(l_cert_id) LOOP
155 AMW_PROCESS_CERT_SUMMARY.update_summary_table (
156 p_certification_id => l_cert_id,
157 p_org_id => proc_rec.organization_id,
158 p_process_id => proc_rec.process_id);
159 END LOOP;
160
161 populate_assoc_opinion(l_cert_id);
162
163 ELSIF l_mode = 'ManageProc' THEN
164 l_org_id := p_event.GetValueForParameter('ORGANIZATION_ID');
165 -- only update the passed org, as there is no impact
166 -- on other orgs in the org hierarchy.
167 AMW_ORG_CERT_EVAL_SUM_PVT.populate_summary (
168 p_api_version_number => 1.0,
169 p_org_id => l_org_id,
170 p_certification_id => l_cert_id,
171 x_return_status => l_return_status,
172 x_msg_count => l_msg_count,
173 x_msg_data => l_msg_data);
174
175 -- to support proc hierarchy, need to update the proc denorm
176 -- for all the processes in the certification-organization.
177 FOR proc_rec IN c_org_proc(l_cert_id, l_org_id) LOOP
178 AMW_PROCESS_CERT_SUMMARY.update_summary_table (
179 p_certification_id => l_cert_id,
180 p_org_id => l_org_id,
181 p_process_id => proc_rec.process_id);
182 END LOOP;
183
184 populate_assoc_opinion(l_cert_id);
185
186 ELSIF l_mode = 'RemoveFromScope' THEN
187 -- to support org hierarchy, need to update the org denorm
188 -- for all the orgs in the certification
189 AMW_ORG_CERT_EVAL_SUM_PVT.populate_org_cert_sum_spec (
190 p_certification_id => l_cert_id);
191 END IF;
192
193 OPEN c_start_date(l_cert_id);
194 FETCH c_start_date INTO l_start_date;
195 CLOSE c_start_date;
196 AMW_PROCESS_CERT_SUMMARY.Populate_Cert_General_Sum(
197 l_cert_id, l_start_date);
198
199 OPEN c_fch_vs_id;
200 FETCH c_fch_vs_id into l_fch_vs_id;
201 CLOSE c_fch_vs_id;
202
203 IF fnd_profile.value('AMW_SUBSIDIARY_AUDIT_UNIT') = to_char(l_fch_vs_id) THEN
204 amw_org_cert_aggr_pkg.populate_full_hierarchies(l_errbuf,l_retcode,l_cert_id);
205 END IF;
206
207 commit;
208 Return 'SUCCESS';
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 ROLLBACK TO Scope_Update_Event;
213
214 FND_MESSAGE.SET_NAME( 'AMW', 'GENERIC_MESSAGE' );
215 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
216 FND_MSG_PUB.ADD;
217
218 WF_CORE.CONTEXT('AMW_PROCCERT_EVENT_PVT', 'SCOPE_UPDATE', p_event.getEventName(), p_subscription_guid);
219 WF_EVENT.setErrorInfo(p_event, 'ERROR');
220
221 RETURN 'ERROR';
222
223 END Scope_Update;
224
225
226 FUNCTION Evaluation_Update
227 ( p_subscription_guid in raw,
228 p_event in out NOCOPY wf_event_t
229 ) return VARCHAR2
230 IS
231 CURSOR c_opinion_rec (c_opinion_log_id NUMBER) IS
232 SELECT opinion_id, object_name, audit_result_code,
233 pk1_value, pk2_value, pk3_value, pk4_value, pk5_value,
234 pk6_value, pk7_value, pk8_value
235 FROM amw_opinions_log_v
236 WHERE opinion_log_id = c_opinion_log_id;
237
238 CURSOR c_cert_with_proc (c_proc_id NUMBER, c_org_id NUMBER) IS
239 SELECT cert.certification_id, opin.audit_result_code old_eval
240 FROM amw_certification_b cert, amw_proc_cert_eval_sum psum,
241 amw_opinions_log_v opin
242 WHERE cert.certification_status in ('ACTIVE','DRAFT')
243 AND cert.certification_id = psum.certification_id
244 AND psum.organization_id = c_org_id
245 AND psum.process_id = c_proc_id
246 AND psum.evaluation_opinion_log_id = opin.opinion_log_id(+);
247
248 CURSOR c_cert_with_risk (c_risk_id NUMBER, c_proc_id NUMBER, c_org_id NUMBER) IS
249 SELECT cert.certification_id, opin.audit_result_code old_eval
250 FROM amw_certification_b cert, amw_risk_associations assoc,
251 amw_opinions_log_v opin
252 WHERE cert.certification_status in ('ACTIVE','DRAFT')
253 AND cert.certification_id = assoc.pk1
254 AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
255 AND assoc.risk_id = c_risk_id
256 AND assoc.pk2 = c_org_id
257 AND NVL(assoc.pk3, -1) = NVL(c_proc_id, -1)
258 AND assoc.pk4 = opin.opinion_log_id(+);
259
260 CURSOR c_cert_with_ctrl (c_ctrl_id NUMBER, c_org_id NUMBER) IS
261 SELECT cert.certification_id, opin.audit_result_code old_eval
262 FROM amw_certification_b cert, amw_control_associations assoc,
263 amw_opinions_log_v opin
264 WHERE cert.certification_status in ('ACTIVE','DRAFT')
265 AND cert.certification_id = assoc.pk1
266 AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
267 AND assoc.control_id = c_ctrl_id
268 AND assoc.pk2 = c_org_id
269 AND assoc.pk5 = opin.opinion_log_id(+);
270
271
272
273 CURSOR Get_org_cert(l_cert_id number, l_org_id number) IS
274 SELECT ineff_processes, processes_certified, total_processes, evaluated_processes,unmitigated_risks,
275 evaluated_risks, total_risks, ineffective_controls, evaluated_controls, total_controls
276 FROM amw_org_cert_eval_sum
277 WHERE certification_id = l_cert_id
278 AND organization_id = l_org_id;
279
280 CURSOR Get_Dashboard_Info(l_cert_id number) IS
281 SELECT UNMITIGATED_RISKS, INEFFECTIVE_CONTROLS, PROC_INEFF_CONTROL, ORG_PROC_INEFF_CONTROL
282 FROM amw_cert_dashboard_sum
283 WHERE certification_id = l_cert_id;
284
285 CURSOR Get_proc_cert_info(l_cert_id number, l_org_id number, l_process_id number) IS
286 SELECT ineffective_controls, evaluated_controls, total_controls, unmitigated_risks, evaluated_risks, total_risks
287 FROM amw_proc_cert_eval_sum
288 WHERE certification_id = l_cert_id
289 AND organization_id = l_org_id
290 AND process_id IN (
291 SELECT process_id
292 FROM amw_execution_scope
293 START WITH process_id = l_process_id
294 AND organization_id = l_org_id
295 AND entity_id = l_cert_id
296 AND entity_type = 'BUSIPROC_CERTIFICATION'
297 CONNECT BY process_id = PRIOR parent_process_id
298 AND organization_id = PRIOR organization_id
299 AND entity_id = PRIOR entity_id
300 AND entity_type = PRIOR entity_type);
301
302 CURSOR FCH_Get_cert_info(l_org_id number) IS
303 SELECT certification_id
304 FROM amw_org_cert_eval_sum
305 WHERE organization_id = l_org_id
306 AND certification_id IN (
307 SELECT cert.certification_id
308 FROM amw_certification_b cert
309 WHERE cert.certification_status in ('ACTIVE','DRAFT')
310 AND cert.object_type = 'PROCESS');
311
312 CURSOR c_fch_vs_id IS
313 select flex_value_set_id
314 from fnd_flex_value_sets
315 where flex_value_set_name = 'FCH_ICM_ENTITY_VALUE_SET';
316
317
318 M_org_ineff_proc number;
319 M_org_proc_cert number;
320 M_org_proc_total number;
321 M_org_proc_eval number;
322 M_org_unmitigated_risk number;
323 M_org_risk_eval number;
324 M_org_risk_total number;
325 M_org_ineff_ctrl number;
326 M_org_ctrl_eval number;
327 M_org_ctrl_total number;
328
329
330 M_dashbd_proc_not_cert number;
331 M_dashbd_proc_cert_issue number;
332 M_dashbd_org_proc_not_cert number;
333 M_dashbd_org_proc_cert_issue number;
334 M_dashbd_unmitigated_risk number;
335 M_dashbd_ineff_ctrl number;
336 M_dashbd_proc_ineff_ctrl number;
337 M_dashbd_org_proc_ineff_ctrl number;
338
339 M_proc_ineffective_controls number;
340 M_proc_evaluated_controls number;
341 M_proc_total_controls number;
342 M_proc_unmitigated_risks number;
343 M_proc_evaluated_risks number;
344 M_proc_total_risks number;
345
346 l_fch_vs_id number;
347
348
349
350 l_opin_log_id NUMBER;
351 l_opin_id NUMBER;
352 l_obj_name VARCHAR2(200);
353 l_new_eval VARCHAR2(200);
354 l_pk1 NUMBER;
355 l_pk2 NUMBER;
356 l_pk3 NUMBER;
357 l_pk4 NUMBER;
358 l_pk5 NUMBER;
359 l_pk6 NUMBER;
360 l_pk7 NUMBER;
361 l_pk8 NUMBER;
362
363 l_msg_data VARCHAR2(2000);
364 l_msg_count_char VARCHAR2(2000);
365 l_msg_count NUMBER;
366
367 l_fch_org_id NUMBER;
368
369 BEGIN
370
371 SAVEPOINT Evaluation_Update_Event;
372
373 g_refresh_flag := 'N';
374 l_opin_log_id := p_event.GetValueForParameter('OPINION_LOG_ID');
375
376 OPEN c_opinion_rec(l_opin_log_id);
377 FETCH c_opinion_rec INTO l_opin_id, l_obj_name, l_new_eval, l_pk1,
378 l_pk2, l_pk3, l_pk4, l_pk5, l_pk6, l_pk7, l_pk8;
379 CLOSE c_opinion_rec;
380
381 IF l_obj_name = 'AMW_ORGANIZATION' THEN
382 -- find all the active proc cert that having this org
383 -- update amw_org_cert_eval_sum.evaluation_opinion_log_id
384 UPDATE amw_org_cert_eval_sum
385 SET last_update_date = sysdate,
386 last_updated_by = fnd_global.user_id,
387 last_update_login = fnd_global.conc_login_id,
388 evaluation_opinion_id = l_opin_id,
389 evaluation_opinion_log_id = l_opin_log_id
390 WHERE organization_id = l_pk1
391 AND certification_id IN (
392 SELECT cert.certification_id
393 FROM amw_certification_b cert, amw_execution_scope scope
397 AND scope.entity_id = cert.certification_id
394 WHERE cert.certification_status in ('ACTIVE','DRAFT')
395 AND cert.object_type = 'PROCESS'
396 AND scope.entity_type = 'BUSIPROC_CERTIFICATION'
398 AND scope.level_id = 3
399 AND scope.organization_id = l_pk1);
400
401 -- set organization_id for FCH
402 l_fch_org_id := l_pk1;
403
404 ELSIF l_obj_name = 'AMW_ORG_PROCESS' THEN
405 -- set organization_id for FCH
406 l_fch_org_id := l_pk3;
407
408 -- find all the active proc cert that having this org
409 FOR proc_rec IN c_cert_with_proc(l_pk1, l_pk3) LOOP
410 --get the affected columns
411 OPEN Get_org_cert(proc_rec.certification_id, l_pk3);
412 FETCH Get_org_cert INTO m_org_ineff_proc, m_org_proc_cert, m_org_proc_total,m_org_proc_eval,m_org_unmitigated_risk,
413 m_org_risk_eval,m_org_risk_total,m_org_ineff_ctrl,m_org_ctrl_eval,m_org_ctrl_total;
414 CLOSE Get_org_cert;
415
416 OPEN Get_Dashboard_Info(proc_rec.certification_id);
417 FETCH Get_Dashboard_Info INTO M_dashbd_unmitigated_risk, M_dashbd_ineff_ctrl, M_dashbd_proc_ineff_ctrl, M_dashbd_org_proc_ineff_ctrl;
418 CLOSE Get_Dashboard_Info;
419
420 IF proc_rec.old_eval IS NULL AND l_new_eval <> 'EFFECTIVE' THEN
421 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b ****/
422 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
423 IF NOT ( (m_certification_list.exists(proc_rec.certification_id)) OR (g_refresh_flag = 'Y') OR ((M_org_proc_eval + 1 < M_org_proc_total) AND (M_org_ineff_proc + 1 < M_org_proc_eval))) THEN
424 g_refresh_flag := 'Y';
425 m_certification_list(proc_rec.certification_id) := proc_rec.certification_id;
426 ELSE
427 *****/
428 UPDATE amw_org_cert_eval_sum
429 SET last_update_date = sysdate,
430 last_updated_by = fnd_global.user_id,
431 last_update_login = fnd_global.conc_login_id,
432 evaluated_processes = least(evaluated_processes+1,total_processes),
433 ineff_processes = least(ineff_processes+1,evaluated_processes+1,total_processes),
434 ineff_processes_prcnt = decode(total_processes, 0, 0,
435 round(least(ineff_processes+1,evaluated_processes+1,total_processes)/total_processes*100))
436 WHERE certification_id = proc_rec.certification_id
437 AND organization_id = l_pk3;
438 --END IF;
439 IF l_pk3 = fnd_profile.value('AMW_GLOBAL_ORG_ID') THEN
440 UPDATE amw_cert_dashboard_sum
441 SET last_update_date = sysdate,
442 last_updated_by = fnd_global.user_id,
443 last_update_login = fnd_global.conc_login_id,
444 proc_ineff_control = proc_ineff_control+1
445 WHERE certification_id = proc_rec.certification_id;
446 ELSE
447 UPDATE amw_cert_dashboard_sum
448 SET last_update_date = sysdate,
449 last_updated_by = fnd_global.user_id,
450 last_update_login = fnd_global.conc_login_id,
451 org_proc_ineff_control = org_proc_ineff_control+1
452 WHERE certification_id = proc_rec.certification_id;
453 END IF;
454
455 ELSIF proc_rec.old_eval IS NULL AND l_new_eval = 'EFFECTIVE' THEN
456 /**********IF(M_org_proc_eval + 1 > M_org_proc_total) THEN
457 ********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b ****/
458 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
459 IF NOT ( (m_certification_list.exists(proc_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (M_org_proc_eval + 1 < M_org_proc_total)) THEN
460 G_REFRESH_FLAG := 'Y';
461 m_certification_list(proc_rec.certification_id) := proc_rec.certification_id;
462 ELSE
463 *********/
464 UPDATE amw_org_cert_eval_sum
465 SET last_update_date = sysdate,
466 last_updated_by = fnd_global.user_id,
467 last_update_login = fnd_global.conc_login_id,
468 evaluated_processes = least(evaluated_processes+1, total_processes)
469 WHERE certification_id = proc_rec.certification_id
470 AND organization_id = l_pk3;
471 --END IF;
472
473 ELSIF proc_rec.old_eval = 'EFFECTIVE' AND l_new_eval <> 'EFFECTIVE' THEN
474 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b ****/
475 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
476 IF NOT ( (m_certification_list.exists(proc_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (M_org_ineff_proc + 1 < m_org_proc_eval) ) THEN
477 -- IF(M_org_ineff_proc + 1 > m_org_proc_eval) THEN
478 g_refresh_flag := 'Y';
479 m_certification_list(proc_rec.certification_id) := proc_rec.certification_id;
480 ELSE
481 *****/
482 UPDATE amw_org_cert_eval_sum
483 SET last_update_date = sysdate,
484 last_updated_by = fnd_global.user_id,
485 last_update_login = fnd_global.conc_login_id,
486 ineff_processes = least(ineff_processes+1,evaluated_processes,total_processes),
487 ineff_processes_prcnt = decode(total_processes, 0, 0,
488 round(least(ineff_processes+1,evaluated_processes,total_processes)/total_processes*100))
489 WHERE certification_id = proc_rec.certification_id
490 AND organization_id = l_pk3;
491 --END IF;
492 IF l_pk3 = fnd_profile.value('AMW_GLOBAL_ORG_ID') THEN
493 UPDATE amw_cert_dashboard_sum
494 SET last_update_date = sysdate,
495 last_updated_by = fnd_global.user_id,
496 last_update_login = fnd_global.conc_login_id,
497 proc_ineff_control = proc_ineff_control+1
498 WHERE certification_id = proc_rec.certification_id;
499 ELSE
500 UPDATE amw_cert_dashboard_sum
501 SET last_update_date = sysdate,
502 last_updated_by = fnd_global.user_id,
503 last_update_login = fnd_global.conc_login_id,
504 org_proc_ineff_control = org_proc_ineff_control+1
508 ELSIF proc_rec.old_eval <> 'EFFECTIVE' AND l_new_eval = 'EFFECTIVE' THEN
505 WHERE certification_id = proc_rec.certification_id;
506 END IF;
507
509 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b ****/
510 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
511 IF NOT ( (m_certification_list.exists(proc_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (M_org_ineff_proc - 1 > 0 ) ) THEN
512 -- IF(M_org_ineff_proc - 1 < 0 ) THEN
513 G_REFRESH_FLAG := 'Y';
514 m_certification_list(proc_rec.certification_id) := proc_rec.certification_id;
515 ELSE
516 *************/
517 UPDATE amw_org_cert_eval_sum
518 SET last_update_date = sysdate,
519 last_updated_by = fnd_global.user_id,
520 last_update_login = fnd_global.conc_login_id,
521 ineff_processes = greatest(0,ineff_processes-1),
522 ineff_processes_prcnt = decode(total_processes, 0, 0,
523 round(greatest(0,ineff_processes-1)/total_processes*100))
524 WHERE certification_id = proc_rec.certification_id
525 AND organization_id = l_pk3;
526 --END IF;
527 IF l_pk3 = fnd_profile.value('AMW_GLOBAL_ORG_ID') THEN
528 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b ****/
529 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
530 IF NOT ( (m_certification_list.exists(proc_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (M_dashbd_proc_ineff_ctrl -1 > 0 ) ) THEN
531 g_refresh_flag := 'Y';
532 m_certification_list(proc_rec.certification_id) := proc_rec.certification_id;
533
534 ELSE
535 **********/
536 UPDATE amw_cert_dashboard_sum
537 SET last_update_date = sysdate,
538 last_updated_by = fnd_global.user_id,
539 last_update_login = fnd_global.conc_login_id,
540 proc_ineff_control = greatest(0,proc_ineff_control-1)
541 WHERE certification_id = proc_rec.certification_id;
542 --END IF;
543 ELSE
544 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b ****/
545 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
546 IF NOT ( (m_certification_list.exists(proc_rec.certification_id)) OR (M_dashbd_org_proc_ineff_ctrl -1 > 0 ) OR (g_refresh_flag = 'Y') ) THEN
547 m_certification_list(proc_rec.certification_id) := proc_rec.certification_id;
548 ELSE
549 *******/
550 UPDATE amw_cert_dashboard_sum
551 SET last_update_date = sysdate,
552 last_updated_by = fnd_global.user_id,
553 last_update_login = fnd_global.conc_login_id,
554 org_proc_ineff_control = greatest(0,org_proc_ineff_control-1)
555 WHERE certification_id = proc_rec.certification_id;
556 --END IF;
557 END IF;
558 END IF;
559 END LOOP;
560
561 -- update amw_proc_cert_eval_sum.evaluation_opinion_log_id
562 UPDATE amw_proc_cert_eval_sum
563 SET last_update_date = sysdate,
564 last_updated_by = fnd_global.user_id,
565 last_update_login = fnd_global.conc_login_id,
566 evaluation_opinion_id = l_opin_id,
567 evaluation_opinion_log_id = l_opin_log_id
568 WHERE process_id = l_pk1
569 AND organization_id = l_pk3
570 AND certification_id in (
571 SELECT cert.certification_id
572 FROM amw_certification_b cert, amw_proc_cert_eval_sum psum
573 WHERE cert.certification_status in ('ACTIVE','DRAFT')
574 AND cert.certification_id = psum.certification_id
575 AND psum.organization_id = l_pk3
576 AND psum.process_id = l_pk1);
577
578 ELSIF l_obj_name = 'AMW_ORG_PROCESS_RISK' THEN
579 -- set organization_id for FCH
580 l_fch_org_id := l_pk3;
581
582 -- find all the active proc cert that having this org-proc-risk
583 FOR risk_rec IN c_cert_with_risk(l_pk1, l_pk4, l_pk3) LOOP
584
585 --get the affected columns
586 OPEN Get_org_cert(risk_rec.certification_id, l_pk3);
587 FETCH Get_org_cert INTO m_org_ineff_proc, m_org_proc_cert, m_org_proc_total,m_org_proc_eval,m_org_unmitigated_risk,
588 m_org_risk_eval,m_org_risk_total,m_org_ineff_ctrl,m_org_ctrl_eval,m_org_ctrl_total;
589 CLOSE Get_org_cert;
590
591 OPEN Get_Dashboard_Info(risk_rec.certification_id);
592 FETCH Get_Dashboard_Info INTO M_dashbd_unmitigated_risk, M_dashbd_ineff_ctrl, M_dashbd_proc_ineff_ctrl, M_dashbd_org_proc_ineff_ctrl;
593 CLOSE Get_Dashboard_Info;
594
595 OPEN Get_proc_cert_info(risk_rec.certification_id, l_pk3, l_pk4);
596 FETCH Get_proc_cert_info INTO m_proc_ineffective_controls,m_proc_evaluated_controls ,m_proc_total_controls ,m_proc_unmitigated_risks ,m_proc_evaluated_risks ,m_proc_total_risks;
597 CLOSE Get_proc_cert_info;
598
599
600 IF risk_rec.old_eval IS NULL AND l_new_eval <> 'EFFECTIVE' THEN
601 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b *********/
602 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
603 IF ((m_org_risk_eval + 1 > m_org_risk_total) or (m_org_unmitigated_risk + 1 > m_org_risk_eval)) THEN
604 IF NOT ( (m_certification_list.exists(risk_rec.certification_id)) OR (g_refresh_flag = 'Y') OR ((m_org_risk_eval + 1 < m_org_risk_total) AND (m_org_unmitigated_risk + 1 < m_org_risk_eval)) ) THEN
605 g_refresh_flag := 'Y';
606 m_certification_list(risk_rec.certification_id) := risk_rec.certification_id;
607 ELSE
608 ***********/
609 UPDATE amw_org_cert_eval_sum
610 SET last_update_date = sysdate,
611 last_updated_by = fnd_global.user_id,
612 last_update_login = fnd_global.conc_login_id,
613 evaluated_risks = least(evaluated_risks+1,total_risks),
614 unmitigated_risks = least(unmitigated_risks+1,evaluated_risks+1,total_risks),
615 unmitigated_risks_prcnt = decode(total_risks, 0, 0,
619 --END IF;
616 round(least(unmitigated_risks+1,evaluated_risks+1,total_risks)/total_risks*100))
617 WHERE certification_id = risk_rec.certification_id
618 AND organization_id = l_pk3;
620
621 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b ****/
622 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
623 IF NOT ( (m_certification_list.exists(risk_rec.certification_id)) OR (g_refresh_flag = 'Y') OR ((m_proc_evaluated_risks + 1 < m_proc_total_risks) AND (m_proc_unmitigated_risks + 1 < m_proc_evaluated_risks)) ) THEN
624 -- IF ((m_proc_evaluated_risks + 1 > m_proc_total_risks) or (m_proc_unmitigated_risks + 1 > m_proc_evaluated_risks)) THEN
625 g_refresh_flag := 'Y';
626 m_certification_list(risk_rec.certification_id) := risk_rec.certification_id;
627 ELSE
628 **********/
629 UPDATE amw_proc_cert_eval_sum
630 SET last_update_date = sysdate,
631 last_updated_by = fnd_global.user_id,
632 last_update_login = fnd_global.conc_login_id,
633 evaluated_risks = least(evaluated_risks+1,total_risks),
634 unmitigated_risks = least(unmitigated_risks+1,evaluated_risks+1,total_risks),
635 unmitigated_risks_prcnt = decode(total_risks, 0, 0,
636 round(least(unmitigated_risks+1,evaluated_risks+1,total_risks)/total_risks*100))
637 WHERE certification_id = risk_rec.certification_id
638 AND organization_id = l_pk3
639 AND process_id IN (
640 SELECT process_id
641 FROM amw_execution_scope
642 START WITH process_id = l_pk4
643 AND organization_id = l_pk3
644 AND entity_id = risk_rec.certification_id
645 AND entity_type = 'BUSIPROC_CERTIFICATION'
646 CONNECT BY process_id = PRIOR parent_process_id
647 AND organization_id = PRIOR organization_id
648 AND entity_id = PRIOR entity_id
649 AND entity_type = PRIOR entity_type);
650 --END IF;
651
652 UPDATE amw_cert_dashboard_sum
653 SET last_update_date = sysdate,
654 last_updated_by = fnd_global.user_id,
655 last_update_login = fnd_global.conc_login_id,
656 unmitigated_risks = unmitigated_risks+1
657 WHERE certification_id = risk_rec.certification_id;
658
659 ELSIF risk_rec.old_eval IS NULL AND l_new_eval = 'EFFECTIVE' THEN
660 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b ****/
661 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
662 IF NOT ( (m_certification_list.exists(risk_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_org_risk_eval + 1 < m_org_risk_total) ) THEN
663 ----IF (m_org_risk_eval + 1 > m_org_risk_total) THEN
664 g_refresh_flag := 'Y';
665 m_certification_list(risk_rec.certification_id) := risk_rec.certification_id;
666 ELSE
667 ****/
668 UPDATE amw_org_cert_eval_sum
669 SET last_update_date = sysdate,
670 last_updated_by = fnd_global.user_id,
671 last_update_login = fnd_global.conc_login_id,
672 evaluated_risks = least(evaluated_risks+1,total_risks)
673 WHERE certification_id = risk_rec.certification_id
674 AND organization_id = l_pk3;
675 --END IF;
676 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b ****/
677 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
678 IF NOT ( (m_certification_list.exists(risk_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_proc_evaluated_risks + 1 < m_proc_total_risks) ) THEN
679 ------IF (m_proc_evaluated_risks + 1 > m_proc_total_risks) THEN
680 g_refresh_flag := 'Y';
681 m_certification_list(risk_rec.certification_id) := risk_rec.certification_id;
682 ELSE
683 **************/
684 UPDATE amw_proc_cert_eval_sum
685 SET last_update_date = sysdate,
686 last_updated_by = fnd_global.user_id,
687 last_update_login = fnd_global.conc_login_id,
688 evaluated_risks = least(evaluated_risks+1,total_risks)
689 WHERE certification_id = risk_rec.certification_id
690 AND organization_id = l_pk3
691 AND process_id IN (
692 SELECT process_id
693 FROM amw_execution_scope
694 START WITH process_id = l_pk4
695 AND organization_id = l_pk3
696 AND entity_id = risk_rec.certification_id
697 AND entity_type = 'BUSIPROC_CERTIFICATION'
698 CONNECT BY process_id = PRIOR parent_process_id
699 AND organization_id = PRIOR organization_id
700 AND entity_id = PRIOR entity_id
701 AND entity_type = PRIOR entity_type);
702 --END IF;
703 ELSIF risk_rec.old_eval = 'EFFECTIVE' AND l_new_eval <> 'EFFECTIVE' THEN
704 /***************** If the display format is a/b/c, then a >= 0 and b>= a and c>= b
705 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
706 IF NOT ( (m_certification_list.exists(risk_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_org_unmitigated_risk + 1 < m_org_risk_eval) ) THEN
707 g_refresh_flag := 'Y';
708 m_certification_list(risk_rec.certification_id) := risk_rec.certification_id;
709 ELSE
710 *************/
711 UPDATE amw_org_cert_eval_sum
712 SET last_update_date = sysdate,
713 last_updated_by = fnd_global.user_id,
714 last_update_login = fnd_global.conc_login_id,
715 unmitigated_risks = least(unmitigated_risks+1,evaluated_risks,total_risks),
716 unmitigated_risks_prcnt = decode(total_risks, 0, 0,
717 round(least(unmitigated_risks+1,evaluated_risks,total_risks)/total_risks*100))
718 WHERE certification_id = risk_rec.certification_id
719 AND organization_id = l_pk3;
720 -- END IF;
721
722 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
726 g_refresh_flag := 'Y';
723 **********IF (m_proc_unmitigated_risks + 1 > m_proc_evaluated_risks) THEN ****/
724 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
725 IF NOT ( (m_certification_list.exists(risk_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_proc_unmitigated_risks + 1 < m_proc_evaluated_risks) ) THEN
727 m_certification_list( risk_rec.certification_id) := risk_rec.certification_id;
728 ELSE
729 **********/
730 UPDATE amw_proc_cert_eval_sum
731 SET last_update_date = sysdate,
732 last_updated_by = fnd_global.user_id,
733 last_update_login = fnd_global.conc_login_id,
734 unmitigated_risks = least(unmitigated_risks+1,evaluated_risks,total_risks),
735 unmitigated_risks_prcnt = decode(total_risks, 0, 0,
736 round(least(unmitigated_risks+1,evaluated_risks,total_risks)/total_risks*100))
737 WHERE certification_id = risk_rec.certification_id
738 AND organization_id = l_pk3
739 AND process_id IN (
740 SELECT process_id
741 FROM amw_execution_scope
742 START WITH process_id = l_pk4
743 AND organization_id = l_pk3
744 AND entity_id = risk_rec.certification_id
745 AND entity_type = 'BUSIPROC_CERTIFICATION'
746 CONNECT BY process_id = PRIOR parent_process_id
747 AND organization_id = PRIOR organization_id
748 AND entity_id = PRIOR entity_id
749 AND entity_type = PRIOR entity_type);
750 --END IF;
751 UPDATE amw_cert_dashboard_sum
752 SET last_update_date = sysdate,
753 last_updated_by = fnd_global.user_id,
754 last_update_login = fnd_global.conc_login_id,
755 unmitigated_risks = unmitigated_risks+1
756 WHERE certification_id = risk_rec.certification_id;
757
758 ELSIF risk_rec.old_eval <> 'EFFECTIVE' AND l_new_eval = 'EFFECTIVE' THEN
759 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
760 *****************IF (m_org_unmitigated_risk -1 < 0 ) THEN ************************************/
761 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
762 IF NOT ( (m_certification_list.exists(risk_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_org_unmitigated_risk -1 > 0 ) ) THEN
763 g_refresh_flag := 'Y';
764 m_certification_list(risk_rec.certification_id) := risk_rec.certification_id;
765 ELSE
766 ******************/
767 UPDATE amw_org_cert_eval_sum
768 SET last_update_date = sysdate,
769 last_updated_by = fnd_global.user_id,
770 last_update_login = fnd_global.conc_login_id,
771 unmitigated_risks = greatest(0,unmitigated_risks-1),
772 unmitigated_risks_prcnt = decode(total_risks, 0, 0,
773 round(greatest(0,unmitigated_risks-1)/total_risks*100))
774 WHERE certification_id = risk_rec.certification_id
775 AND organization_id = l_pk3;
776 --END IF;
777
778 /*************** If the display format is a/b/c, then a >= 0 and b>= a and c>= b
779 ****************IF (m_proc_unmitigated_risks - 1 < 0 ) THEN ***********************/
780 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
781 IF NOT ( (m_certification_list.exists(risk_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_proc_unmitigated_risks - 1 > 0 ) ) THEN
782 g_refresh_flag := 'Y';
783 m_certification_list(risk_rec.certification_id) := risk_rec.certification_id;
784 ELSE
785 ************/
786 UPDATE amw_proc_cert_eval_sum
787 SET last_update_date = sysdate,
788 last_updated_by = fnd_global.user_id,
789 last_update_login = fnd_global.conc_login_id,
790 unmitigated_risks = greatest(0,unmitigated_risks-1),
791 unmitigated_risks_prcnt = decode(total_risks, 0, 0,
792 round(greatest(0,unmitigated_risks-1)/total_risks*100))
793 WHERE certification_id = risk_rec.certification_id
794 AND organization_id = l_pk3
795 AND process_id IN (
796 SELECT process_id
797 FROM amw_execution_scope
798 START WITH process_id = l_pk4
799 AND organization_id = l_pk3
800 AND entity_id = risk_rec.certification_id
801 AND entity_type = 'BUSIPROC_CERTIFICATION'
802 CONNECT BY process_id = PRIOR parent_process_id
803 AND organization_id = PRIOR organization_id
804 AND entity_id = PRIOR entity_id
805 AND entity_type = PRIOR entity_type);
806 --END IF;
807
808 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b *********
809 **********IF ((m_dashbd_unmitigated_risk -1 < 0 ) THEN ****************/
810 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
811 IF NOT ( (m_certification_list.exists(risk_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_dashbd_unmitigated_risk -1 > 0 ) ) THEN
812 g_refresh_flag := 'Y';
813 m_certification_list(risk_rec.certification_id) := risk_rec.certification_id;
814 ELSE
815 ************/
816 UPDATE amw_cert_dashboard_sum
817 SET last_update_date = sysdate,
818 last_updated_by = fnd_global.user_id,
819 last_update_login = fnd_global.conc_login_id,
820 unmitigated_risks = greatest(0,unmitigated_risks-1)
821 WHERE certification_id = risk_rec.certification_id;
822 --END IF;
823 END IF;
824 END LOOP;
825
826 -- update amw_risk_association.pk4 with evaluation_opinion_log_id
827 UPDATE amw_risk_associations
828 SET last_update_date = sysdate,
829 last_updated_by = fnd_global.user_id,
830 last_update_login = fnd_global.conc_login_id,
831 pk4 = l_opin_log_id
832 WHERE object_type = 'BUSIPROC_CERTIFICATION'
833 AND risk_id = l_pk1
834 AND pk2 = l_pk3 -- organization_id
835 AND NVL(pk3,-1) = NVL(l_pk4,-1) -- process_id
839 WHERE cert.certification_status in ('ACTIVE','DRAFT')
836 AND pk1 IN (
837 SELECT assoc.pk1
838 FROM amw_certification_b cert, amw_risk_associations assoc
840 AND cert.certification_id = assoc.pk1
841 AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
842 AND assoc.risk_id = l_pk1
843 AND assoc.pk2 = l_pk3 -- organization_id
844 AND NVL(assoc.pk3, -1) = NVL(l_pk4, -1)); -- proccess_id
845
846 ELSIF l_obj_name = 'AMW_ORG_CONTROL' THEN
847 -- set organization_id for FCH
848 l_fch_org_id := l_pk3;
849
850
851 -- find all the active proc cert that having this org-ctrl
852 FOR ctrl_rec IN c_cert_with_ctrl(l_pk1, l_pk3) LOOP
853 --get the affected columns
854 OPEN Get_org_cert(ctrl_rec.certification_id, l_pk3);
855 FETCH Get_org_cert INTO m_org_ineff_proc, m_org_proc_cert, m_org_proc_total,m_org_proc_eval,m_org_unmitigated_risk,
856 m_org_risk_eval,m_org_risk_total,m_org_ineff_ctrl,m_org_ctrl_eval,m_org_ctrl_total;
857 CLOSE Get_org_cert;
858
859 OPEN Get_Dashboard_Info(ctrl_rec.certification_id);
860 FETCH Get_Dashboard_Info INTO M_dashbd_unmitigated_risk, M_dashbd_ineff_ctrl, M_dashbd_proc_ineff_ctrl, M_dashbd_org_proc_ineff_ctrl;
861 CLOSE Get_Dashboard_Info;
862
863
864 OPEN Get_proc_cert_info(ctrl_rec.certification_id, l_pk3, l_pk4);
865 FETCH Get_proc_cert_info INTO m_proc_ineffective_controls,m_proc_evaluated_controls ,m_proc_total_controls ,m_proc_unmitigated_risks ,m_proc_evaluated_risks ,m_proc_total_risks;
866 CLOSE Get_proc_cert_info;
867
868
869 IF ctrl_rec.old_eval IS NULL AND l_new_eval <> 'EFFECTIVE' THEN
870 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
871 ********** IF ((m_org_ctrl_eval + 1 > m_org_ctrl_total) or (m_org_ineff_ctrl + 1 > m_org_ctrl_eval)) THEN ****/
872 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
873 IF NOT ( (m_certification_list.exists(ctrl_rec.certification_id)) OR (g_refresh_flag = 'Y') OR ((m_org_ctrl_eval + 1 < m_org_ctrl_total) AND (m_org_ineff_ctrl + 1 < m_org_ctrl_eval)) ) THEN
874 g_refresh_flag := 'Y';
875 m_certification_list(ctrl_rec.certification_id) := ctrl_rec.certification_id;
876 ELSE
877 **********/
878 UPDATE amw_org_cert_eval_sum
879 SET last_update_date = sysdate,
880 last_updated_by = fnd_global.user_id,
881 last_update_login = fnd_global.conc_login_id,
882 evaluated_controls = least(evaluated_controls+1,total_controls),
883 ineffective_controls = least(ineffective_controls+1,evaluated_controls+1,total_controls),
884 ineff_controls_prcnt = decode(total_controls, 0, 0,
885 round(least(ineffective_controls+1,evaluated_controls+1,total_controls)/total_controls*100))
886 WHERE certification_id = ctrl_rec.certification_id
887 AND organization_id = l_pk3;
888 --END IF;
889
890 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
891 ******** IF ((m_proc_evaluated_controls + 1 > m_proc_total_controls ) or (m_proc_ineffective_controls + 1 > m_proc_evaluated_controls )) THEN ****/
892 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
893 IF NOT ( (m_certification_list.exists(ctrl_rec.certification_id)) OR (g_refresh_flag = 'Y') OR ((m_proc_evaluated_controls + 1 < m_proc_total_controls ) AND (m_proc_ineffective_controls + 1 < m_proc_evaluated_controls )) ) THEN
894 g_refresh_flag := 'Y';
895 m_certification_list(ctrl_rec.certification_id) := ctrl_rec.certification_id;
896 ELSE
897 ************/
898 UPDATE amw_proc_cert_eval_sum
899 SET last_update_date = sysdate,
900 last_updated_by = fnd_global.user_id,
901 last_update_login = fnd_global.conc_login_id,
902 evaluated_controls = least(evaluated_controls+1,total_controls),
903 ineffective_controls = least(ineffective_controls+1,evaluated_controls+1,total_controls),
904 ineffective_controls_prcnt = decode(total_controls, 0, 0,
905 round(least(ineffective_controls+1,evaluated_controls+1,total_controls)/total_controls*100))
906 WHERE certification_id = ctrl_rec.certification_id
907 AND organization_id = l_pk3
908 AND process_id IN (
909 SELECT process_id
910 FROM amw_execution_scope --, amw_control_associations assoc
911 START WITH process_id IN (
912 SELECT pk3
913 FROM amw_control_associations
914 WHERE object_type = 'BUSIPROC_CERTIFICATION'
915 AND control_id = l_pk1
916 AND pk1 = ctrl_rec.certification_id
917 AND pk2 = l_pk3) -- organization_id
918 AND organization_id = l_pk3
919 AND entity_id = ctrl_rec.certification_id
920 AND entity_type = 'BUSIPROC_CERTIFICATION'
921 CONNECT BY process_id = PRIOR parent_process_id
922 AND organization_id = PRIOR organization_id
923 AND entity_id = PRIOR entity_id
924 AND entity_type = PRIOR entity_type);
925 --END IF;
926 UPDATE amw_cert_dashboard_sum
927 SET last_update_date = sysdate,
928 last_updated_by = fnd_global.user_id,
929 last_update_login = fnd_global.conc_login_id,
930 ineffective_controls = ineffective_controls+1
931 WHERE certification_id = ctrl_rec.certification_id;
932
933 ELSIF ctrl_rec.old_eval IS NULL AND l_new_eval = 'EFFECTIVE' THEN
934 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
935 *********IF (m_org_ctrl_eval + 1 > m_org_ctrl_total) THEN *******************/
936 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
937 IF NOT ( (m_certification_list.exists(ctrl_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_org_ctrl_eval + 1 < m_org_ctrl_total) ) THEN
938 g_refresh_flag := 'Y';
939 m_certification_list(ctrl_rec.certification_id) := ctrl_rec.certification_id;
943 SET last_update_date = sysdate,
940 ELSE
941 ***********/
942 UPDATE amw_org_cert_eval_sum
944 last_updated_by = fnd_global.user_id,
945 last_update_login = fnd_global.conc_login_id,
946 evaluated_controls = least(evaluated_controls+1,total_controls)
947 WHERE certification_id = ctrl_rec.certification_id
948 AND organization_id = l_pk3;
949 --END IF;
950
951 --CHECK AGAIN WHY NOT LOOP
952 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
953 ********* IF (m_proc_evaluated_controls + 1 > m_proc_total_controls ) THEN ****/
954 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
955 IF NOT ( (m_certification_list.exists(ctrl_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_proc_evaluated_controls + 1 < m_proc_total_controls ) ) THEN
956 g_refresh_flag := 'Y';
957 m_certification_list(ctrl_rec.certification_id) := ctrl_rec.certification_id;
958 ELSE
959 **************/
960 UPDATE amw_proc_cert_eval_sum
961 SET last_update_date = sysdate,
962 last_updated_by = fnd_global.user_id,
963 last_update_login = fnd_global.conc_login_id,
964 evaluated_controls = least(evaluated_controls+1,total_controls)
965 WHERE certification_id = ctrl_rec.certification_id
966 AND organization_id = l_pk3
967 AND process_id IN (
968 SELECT process_id
969 FROM amw_execution_scope --, amw_control_associations assoc
970 START WITH process_id IN (
971 SELECT pk3
972 FROM amw_control_associations
973 WHERE object_type = 'BUSIPROC_CERTIFICATION'
974 AND control_id = l_pk1
975 AND pk1 = ctrl_rec.certification_id
976 AND pk2 = l_pk3) -- organization_id
977 AND organization_id = l_pk3
978 AND entity_id = ctrl_rec.certification_id
979 AND entity_type = 'BUSIPROC_CERTIFICATION'
980 CONNECT BY process_id = PRIOR parent_process_id
981 AND organization_id = PRIOR organization_id
982 AND entity_id = PRIOR entity_id
983 AND entity_type = PRIOR entity_type);
984 --END IF;
985
986 ELSIF ctrl_rec.old_eval = 'EFFECTIVE' AND l_new_eval <> 'EFFECTIVE' THEN
987 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
988 **********IF (m_org_ineff_ctrl + 1 > m_org_ctrl_eval) THEN *****************/
989 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
990 IF NOT ( (m_certification_list.exists(ctrl_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_org_ineff_ctrl + 1 < m_org_ctrl_eval) ) THEN
991 g_refresh_flag := 'Y';
992 m_certification_list(ctrl_rec.certification_id) := ctrl_rec.certification_id;
993 ELSE
994 *********/
995 UPDATE amw_org_cert_eval_sum
996 SET last_update_date = sysdate,
997 last_updated_by = fnd_global.user_id,
998 last_update_login = fnd_global.conc_login_id,
999 ineffective_controls = least(ineffective_controls+1,evaluated_controls,total_controls),
1000 ineff_controls_prcnt = decode(total_controls, 0, 0,
1001 round(least(ineffective_controls+1,evaluated_controls,total_controls)/total_controls*100))
1002 WHERE certification_id = ctrl_rec.certification_id
1003 AND organization_id = l_pk3;
1004 --END IF;
1005
1006 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1007 ***********IF (m_proc_ineffective_controls + 1 > m_proc_evaluated_controls ) THEN ****/
1008 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1009 IF NOT ( (m_certification_list.exists(ctrl_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_proc_ineffective_controls + 1 < m_proc_evaluated_controls ) ) THEN
1010 g_refresh_flag := 'Y';
1011 m_certification_list(ctrl_rec.certification_id) := ctrl_rec.certification_id;
1012 ELSE
1013 ***********/
1014 UPDATE amw_proc_cert_eval_sum
1015 SET last_update_date = sysdate,
1016 last_updated_by = fnd_global.user_id,
1017 last_update_login = fnd_global.conc_login_id,
1018 ineffective_controls = least(ineffective_controls+1,evaluated_controls,total_controls),
1019 ineffective_controls_prcnt = decode(total_controls, 0, 0,
1020 round(least(ineffective_controls+1,evaluated_controls,total_controls)/total_controls*100))
1021 WHERE certification_id = ctrl_rec.certification_id
1022 AND organization_id = l_pk3
1023 AND process_id IN (
1024 SELECT process_id
1025 FROM amw_execution_scope --, amw_control_associations assoc
1026 START WITH process_id IN (
1027 SELECT pk3
1028 FROM amw_control_associations
1029 WHERE object_type = 'BUSIPROC_CERTIFICATION'
1030 AND control_id = l_pk1
1031 AND pk1 = ctrl_rec.certification_id
1032 AND pk2 = l_pk3) -- organization_id
1033 AND organization_id = l_pk3
1034 AND entity_id = ctrl_rec.certification_id
1035 AND entity_type = 'BUSIPROC_CERTIFICATION'
1036 CONNECT BY process_id = PRIOR parent_process_id
1037 AND organization_id = PRIOR organization_id
1038 AND entity_id = PRIOR entity_id
1039 AND entity_type = PRIOR entity_type);
1040 --END IF;
1041
1042 UPDATE amw_cert_dashboard_sum
1043 SET last_update_date = sysdate,
1044 last_updated_by = fnd_global.user_id,
1045 last_update_login = fnd_global.conc_login_id,
1046 ineffective_controls = ineffective_controls+1
1047 WHERE certification_id = ctrl_rec.certification_id;
1048
1049 ELSIF ctrl_rec.old_eval <> 'EFFECTIVE' AND l_new_eval = 'EFFECTIVE' THEN
1050 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1051 **************IF (m_org_ineff_ctrl -1 < 0 ) THEN *************************/
1055 m_certification_list(ctrl_rec.certification_id) := ctrl_rec.certification_id;
1052 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1053 IF NOT ( (m_certification_list.exists(ctrl_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_org_ineff_ctrl -1 > 0 ) ) THEN
1054 g_refresh_flag := 'Y';
1056 ELSE
1057 ***********/
1058 UPDATE amw_org_cert_eval_sum
1059 SET last_update_date = sysdate,
1060 last_updated_by = fnd_global.user_id,
1061 last_update_login = fnd_global.conc_login_id,
1062 ineffective_controls = greatest(0,ineffective_controls-1),
1063 ineff_controls_prcnt = decode(total_controls, 0, 0,
1064 round(greatest(0,ineffective_controls-1)/total_controls*100))
1065 WHERE certification_id = ctrl_rec.certification_id
1066 AND organization_id = l_pk3;
1067 --END IF;
1068
1069
1070 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1071 **************IF (m_proc_ineffective_controls -1 < 0 ) THEN *******************/
1072 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1073 IF NOT ( (m_certification_list.exists(ctrl_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_proc_ineffective_controls -1 > 0 ) ) THEN
1074 g_refresh_flag := 'Y';
1075 m_certification_list(ctrl_rec.certification_id) := ctrl_rec.certification_id;
1076 ELSE
1077 ************/
1078 UPDATE amw_proc_cert_eval_sum
1079 SET last_update_date = sysdate,
1080 last_updated_by = fnd_global.user_id,
1081 last_update_login = fnd_global.conc_login_id,
1082 ineffective_controls = greatest(0,ineffective_controls-1),
1083 ineffective_controls_prcnt = decode(total_controls, 0, 0,
1084 round(greatest(0,ineffective_controls-1)/total_controls*100))
1085 WHERE certification_id = ctrl_rec.certification_id
1086 AND organization_id = l_pk3
1087 AND process_id IN (
1088 SELECT process_id
1089 FROM amw_execution_scope --, amw_control_associations assoc
1090 START WITH process_id IN (
1091 SELECT pk3
1092 FROM amw_control_associations
1093 WHERE object_type = 'BUSIPROC_CERTIFICATION'
1094 AND control_id = l_pk1
1095 AND pk1 = ctrl_rec.certification_id
1096 AND pk2 = l_pk3) -- organization_id
1097 AND organization_id = l_pk3
1098 AND entity_id = ctrl_rec.certification_id
1099 AND entity_type = 'BUSIPROC_CERTIFICATION'
1100 CONNECT BY process_id = PRIOR parent_process_id
1101 AND organization_id = PRIOR organization_id
1102 AND entity_id = PRIOR entity_id
1103 AND entity_type = PRIOR entity_type);
1104 --END IF;
1105
1106 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1107 **********IF ((m_dashbd_ineff_ctrl -1 < 0 ) THEN ****************************/
1108 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1109 IF NOT ( (m_certification_list.exists(ctrl_rec.certification_id)) OR (g_refresh_flag = 'Y') OR (m_dashbd_ineff_ctrl -1 > 0 ) ) THEN
1110 g_refresh_flag := 'Y';
1111 m_certification_list(ctrl_rec.certification_id) := ctrl_rec.certification_id;
1112 ELSE
1113 *************/
1114 UPDATE amw_cert_dashboard_sum
1115 SET last_update_date = sysdate,
1116 last_updated_by = fnd_global.user_id,
1117 last_update_login = fnd_global.conc_login_id,
1118 ineffective_controls = greatest(0,ineffective_controls-1)
1119 WHERE certification_id = ctrl_rec.certification_id;
1120 --END IF;
1121 END IF;
1122 END LOOP;
1123
1124 -- update amw_contrl_associations.pk5 with evaluation_opinion_log_id
1125 UPDATE amw_control_associations
1126 SET last_update_date = sysdate,
1127 last_updated_by = fnd_global.user_id,
1128 last_update_login = fnd_global.conc_login_id,
1129 pk5 = l_opin_log_id
1130 WHERE object_type = 'BUSIPROC_CERTIFICATION'
1131 AND control_id = l_pk1
1132 AND pk2 = l_pk3 -- organization_id
1133 AND pk1 IN (
1134 SELECT assoc.pk1
1135 FROM amw_certification_b cert, amw_control_associations assoc
1136 WHERE cert.certification_status in ('ACTIVE','DRAFT')
1137 AND cert.certification_id = assoc.pk1
1138 AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
1139 AND assoc.control_id = l_pk1
1140 AND assoc.pk2 = l_pk3); -- organization_id
1141
1142 ELSIF l_obj_name = 'AMW_ORG_AP_CONTROL' THEN
1143 UPDATE amw_ap_associations
1144 SET last_update_date = sysdate,
1145 last_updated_by = fnd_global.user_id,
1146 last_update_login = fnd_global.conc_login_id,
1147 pk4 = l_opin_log_id
1148 WHERE object_type = 'BUSIPROC_CERTIFICATION'
1149 AND audit_procedure_id = l_pk1
1150 AND pk2 = l_pk3 -- organization_id
1151 AND pk3 = l_pk4 -- control_id
1152 AND pk1 IN (
1153 SELECT assoc.pk1
1154 FROM amw_certification_b cert, amw_ap_associations assoc
1155 WHERE cert.certification_status in ('ACTIVE','DRAFT')
1156 AND cert.certification_id = assoc.pk1
1157 AND assoc.object_type = 'BUSIPROC_CERTIFICATION'
1158 AND assoc.audit_procedure_id = l_pk1
1159 AND assoc.pk2 = l_pk3 -- organization_id
1160 AND assoc.pk3 = l_pk4); -- control_id
1161
1162 END IF;
1163
1164
1165 --refresh all of the summary tables amw_cert_dashboard_sum, amw_proc_cert_eval_sum, amw_org_cert_eval_sum
1166 -- IF (G_REFRESH_FLAG = 'Y') THEN
1167 --l_index := m_certification_list.FIRST;
1168 --WHILE l_index <= m_certification_list.LAST LOOP
1169
1170 --AMW_PROCESS_CERT_SUMMARY.POPULATE_ALL_CERT_SUMMARY
1171 --(x_errbuf => l_msg_data,
1172 -- x_retcode => l_msg_count,
1173 -- p_certification_id => m_certification_list(l_index)
1174 --);
1178 -- retcode => l_msg_count,
1175
1176 --AMW_PROCESS_CERT_SUMMARY.Populate_All_Cert_General_Sum
1177 --(errbuf => l_msg_data,
1179 -- p_certification_id => m_certification_list(l_index)
1180 --);
1181
1182 --AMW_ORG_CERT_EVAL_SUM_PVT.populate_org_cert_summary
1183 --(x_errbuf => l_msg_data,
1184 -- x_retcode => l_msg_count,
1185 -- p_certification_id => m_certification_list(l_index)
1186 --);
1187 -- l_index := l_index + 1;
1188 --END LOOP;
1189
1190 --END IF;
1191
1192 OPEN c_fch_vs_id;
1193 FETCH c_fch_vs_id into l_fch_vs_id;
1194 CLOSE c_fch_vs_id;
1195
1196 IF fnd_profile.value('AMW_SUBSIDIARY_AUDIT_UNIT') = to_char(l_fch_vs_id) THEN
1197
1198 FOR FCH_Get_cert_info_rec IN FCH_Get_cert_info(l_fch_org_id) LOOP
1199 amw_org_cert_aggr_pkg.update_org_cert_aggr_rows(FCH_Get_cert_info_rec.certification_id, l_fch_org_id);
1200 END LOOP;
1201
1202
1203 END IF;
1204
1205
1206 -- somehow the change was not being committed to db without
1207 -- the following commit. so we temporarily put commit here, and we
1208 -- still need to invetigate why the transaction was not committed
1209 -- automatically.
1210 commit;
1211 Return 'SUCCESS';
1212
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 ROLLBACK TO Evaluation_Update_Event;
1216
1217 FND_MESSAGE.SET_NAME( 'AMW', 'GENERIC_MESSAGE' );
1218 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
1219 FND_MSG_PUB.ADD;
1220
1221 WF_CORE.CONTEXT('AMW_PROCCERT_EVENT_PVT', 'EVALUATION_UPDATE', p_event.getEventName(), p_subscription_guid);
1222 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1223
1224 RETURN 'ERROR';
1225
1226 END Evaluation_Update;
1227
1228 FUNCTION Certification_Update
1229 ( p_subscription_guid in raw,
1230 p_event in out NOCOPY wf_event_t
1231 ) return VARCHAR2
1232 IS
1233 CURSOR c_opinion_rec (c_opinion_log_id NUMBER) IS
1234 SELECT opinion_id, object_name, audit_result_code,
1235 pk1_value, pk2_value, pk3_value, pk4_value, pk5_value,
1236 pk6_value, pk7_value, pk8_value
1237 FROM amw_opinions_log_v
1238 WHERE opinion_log_id = c_opinion_log_id;
1239
1240 CURSOR c_old_opinion (c_opin_log_id NUMBER) IS
1241 SELECT audit_result_code
1242 FROM amw_opinions_log_v
1243 WHERE opinion_log_id =
1244 (SELECT max(v2.opinion_log_id)
1245 FROM amw_opinions_log_v v1, amw_opinions_log_v v2
1246 WHERE v1.opinion_log_id = c_opin_log_id
1247 AND v1.opinion_id = v2.opinion_id
1248 AND v2.opinion_log_id < c_opin_log_id);
1249
1250 CURSOR c_orgs_pending_in_scope(c_cert_id NUMBER) IS
1251 SELECT count(distinct aes.organization_id)
1252 FROM AMW_EXECUTION_SCOPE aes
1253 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1254 AND aes.entity_id = c_cert_id
1255 AND aes.level_id = 4
1256 AND not exists (SELECT 'Y'
1257 FROM AMW_OPINIONS_V aov
1258 WHERE aov.object_name = 'AMW_ORG_PROCESS'
1259 AND aov.opinion_type_code = 'CERTIFICATION'
1260 AND aov.pk3_value = aes.organization_id
1261 AND aov.pk2_value = c_cert_id
1262 AND aov.pk1_value = aes.process_id);
1263
1264 CURSOR Get_org_cert(l_cert_id number, l_org_id number) IS
1265 SELECT organization_id, sub_org_cert, total_sub_org , sub_org_cert_issues, proc_cert_issues, processes_certified, total_processes
1266 FROM amw_org_cert_eval_sum
1267 WHERE certification_id = l_cert_id
1268 AND organization_id IN (
1269 SELECT parent_object_id
1270 FROM amw_entity_hierarchies
1271 START WITH entity_type = 'BUSIPROC_CERTIFICATION'
1272 AND entity_id = l_org_id
1273 AND object_type = 'ORG'
1274 AND object_id = l_cert_id
1275 CONNECT BY entity_type = PRIOR entity_type
1276 AND entity_id = PRIOR entity_id
1277 AND object_type = PRIOR object_type
1278 AND object_id = PRIOR parent_object_id);
1279
1280
1281
1282
1283 CURSOR Get_Dashboard_Info(l_cert_id number) IS
1284 SELECT orgs_pending_certification, processes_cert_issues, processes_not_cert, org_process_cert_issues, org_process_not_cert
1285 FROM amw_cert_dashboard_sum
1286 WHERE certification_id = l_cert_id;
1287
1288 CURSOR Get_cert_dashboard(l_cert_id number) IS
1289 SELECT processes_not_cert, org_process_not_cert, processes_cert_issues, org_process_cert_issues
1290 FROM amw_cert_dashboard_sum
1291 WHERE certification_id = l_cert_id;
1292
1293 CURSOR c_org_cert(l_cert_id number, l_org_id number) IS
1294 SELECT processes_certified, total_processes, proc_cert_issues
1295 FROM amw_org_cert_eval_sum
1296 WHERE certification_id = l_cert_id
1297 AND organization_id = l_org_id;
1298
1299
1300 CURSOR Get_parent_process(l_cert_id number, l_org_id number, l_process_id number) IS
1301 SELECT certification_id, organization_id, process_id, sub_process_cert, total_sub_process_cert, sub_process_cert_issues
1302 FROM amw_proc_cert_eval_sum
1303 WHERE certification_id = l_cert_id
1304 AND organization_id = l_org_id
1305 AND process_id in (
1306 SELECT parent_process_id
1307 FROM amw_execution_scope
1308 START WITH entity_type = 'BUSIPROC_CERTIFICATION'
1309 AND entity_id = l_cert_id
1310 AND organization_id = l_org_id
1311 AND process_id = l_process_id
1312 CONNECT BY entity_type = PRIOR entity_type
1313 AND entity_id = PRIOR entity_id
1314 AND organization_id = PRIOR organization_id
1315 AND process_id = PRIOR parent_process_id);
1316
1317 CURSOR Get_related_org_proc(l_cert_id number, l_org_id number, l_process_id number) IS
1321 AND (process_id = l_process_id
1318 SELECT certification_id, organization_id, process_id, org_process_cert, org_process_cert_issues, total_org_process_cert
1319 FROM amw_proc_cert_eval_sum
1320 WHERE certification_id = l_cert_id
1322 OR
1323 process_id IN (
1324 SELECT proc.process_id
1325 FROM amw_execution_scope scp,
1326 amw_process_organization procorg,
1327 amw_process proc
1328 WHERE scp.entity_id = l_cert_id
1329 AND scp.entity_type = 'BUSIPROC_CERTIFICATION'
1330 AND scp.organization_id = l_org_id
1331 AND scp.process_id = l_process_id
1332 AND scp.process_org_rev_id = procorg.process_org_rev_id
1333 AND procorg.standard_variation = proc.process_rev_id))
1334 AND organization_id in (
1335 SELECT parent_object_id
1336 FROM amw_entity_hierarchies
1337 START WITH entity_type = 'BUSIPROC_CERTIFICATION'
1338 AND entity_id = l_cert_id
1339 AND object_type = 'ORG'
1340 AND object_id = l_org_id
1341 CONNECT BY entity_type = PRIOR entity_type
1342 AND entity_id = PRIOR entity_id
1343 AND object_type = PRIOR object_type
1344 AND object_id = PRIOR parent_object_id);
1345
1346 CURSOR c_fch_vs_id IS
1347 select flex_value_set_id
1348 from fnd_flex_value_sets
1349 where flex_value_set_name = 'FCH_ICM_ENTITY_VALUE_SET';
1350
1351 M_dashbd_org_pending_cert number;
1352 M_dashbd_org_proc_cert_issues number;
1353 M_dashbd_org_process_not_cert number;
1354 M_dashbd_proc_cert_issues number;
1355 M_dashbd_proc_not_cert number;
1356 M_dashbd_org_proc_not_cert number;
1357
1358 M_org_cert_proc_certified number;
1359 M_org_cert_total_proc number;
1360 M_org_cert_proc_cert_issues number;
1361
1362 l_opin_log_id NUMBER;
1363 l_opin_id NUMBER;
1364 l_obj_name VARCHAR2(200);
1365 l_new_cert VARCHAR2(200);
1366 l_pk1 NUMBER;
1367 l_pk2 NUMBER;
1368 l_pk3 NUMBER;
1369 l_pk4 NUMBER;
1370 l_pk5 NUMBER;
1371 l_pk6 NUMBER;
1372 l_pk7 NUMBER;
1373 l_pk8 NUMBER;
1374 l_old_cert VARCHAR2(200);
1375 l_orgs_pending NUMBER;
1376 l_msg_data VARCHAR2(2000);
1377 l_msg_count_char VARCHAR2(200);
1378 l_msg_count NUMBER;
1379
1380 l_fch_org_id NUMBER;
1381 l_fch_cert_id NUMBER;
1382
1383 l_fch_vs_id NUMBER;
1384
1385
1386 BEGIN
1387
1388 SAVEPOINT Certification_Update_Event;
1389
1390 g_refresh_flag := 'N';
1391 l_opin_log_id := p_event.GetValueForParameter('OPINION_LOG_ID');
1392
1393 OPEN c_opinion_rec(l_opin_log_id);
1394 FETCH c_opinion_rec INTO l_opin_id, l_obj_name, l_new_cert, l_pk1,
1395 l_pk2, l_pk3, l_pk4, l_pk5, l_pk6, l_pk7, l_pk8;
1396 CLOSE c_opinion_rec;
1397
1398
1399 IF l_obj_name = 'AMW_ORGANIZATION' THEN
1400 --set organization_id, certification_id for FCH
1401 l_fch_org_id := l_pk1;
1402 l_fch_cert_id := l_pk2;
1403
1404 OPEN c_old_opinion(l_opin_log_id);
1405 FETCH c_old_opinion INTO l_old_cert;
1406 CLOSE c_old_opinion;
1407
1408 UPDATE amw_org_cert_eval_sum
1409 SET last_update_date = sysdate,
1410 last_updated_by = fnd_global.user_id,
1411 last_update_login = fnd_global.conc_login_id,
1412 certification_opinion_id = l_opin_id
1413 WHERE certification_id = l_pk2
1414 AND organization_id = l_pk1;
1415
1416
1417 OPEN Get_Dashboard_Info(l_pk2);
1418 FETCH Get_Dashboard_Info INTO m_dashbd_org_pending_cert, m_dashbd_proc_cert_issues, m_dashbd_proc_not_cert, m_dashbd_org_proc_cert_issues, m_dashbd_org_process_not_cert;
1419 CLOSE Get_Dashboard_Info;
1420
1421 IF l_old_cert IS NULL THEN
1422 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1423 **********IF(m_dashbd_org_pending_cert - 1 < 0 ) THEN ************************/
1424 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1425 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (m_dashbd_org_pending_cert - 1 > 0 ) ) THEN
1426 g_refresh_flag := 'Y';
1427 m_certification_list(l_pk2) := l_pk2;
1428 goto refresh_all_records;
1429 ELSE
1430 *************/
1431 UPDATE amw_cert_dashboard_sum
1432 SET last_update_date = sysdate,
1433 last_updated_by = fnd_global.user_id,
1434 last_update_login = fnd_global.conc_login_id,
1435 orgs_pending_certification = greatest(0,orgs_pending_certification-1)
1436 WHERE certification_id = l_pk1;
1437 --END IF;
1438
1439 END IF;
1440
1441 --find all of parent organizations
1442 FOR Get_org_cert_Rec in Get_org_cert(l_pk1, l_pk2) LOOP
1443 exit when Get_org_cert %notfound;
1444
1445 IF l_old_cert IS NULL AND l_new_cert = 'EFFECTIVE' THEN
1446
1447 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1448 **********IF(get_org_cert_rec.sub_org_cert + 1 > get_org_cert_rec.total_sub_org) THEN ****/
1449 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1450 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (get_org_cert_rec.sub_org_cert + 1 < get_org_cert_rec.total_sub_org) ) THEN
1451 g_refresh_flag := 'Y';
1452 m_certification_list(l_pk2) := l_pk2;
1453 goto refresh_all_records;
1454 ELSE
1455 ************/
1456 UPDATE amw_org_cert_eval_sum
1457 SET last_update_date = sysdate,
1458 last_updated_by = fnd_global.user_id,
1459 last_update_login = fnd_global.conc_login_id,
1460 sub_org_cert = least(sub_org_cert+1,total_sub_org)
1461 WHERE certification_id = l_pk1
1465
1462 AND organization_id = Get_org_cert_Rec.organization_id;
1463 --END IF;
1464 ELSIF l_old_cert IS NULL AND l_new_cert <> 'EFFECTIVE' THEN
1466 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1467 *********IF((get_org_cert_rec.sub_org_cert + 1 > get_org_cert_rec.total_sub_org) or (get_org_cert_rec.sub_org_cert_issues + 1 > get_org_cert_rec.sub_org_cert ))THEN ****/
1468 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1469 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR ((get_org_cert_rec.sub_org_cert + 1 < get_org_cert_rec.total_sub_org) AND (get_org_cert_rec.sub_org_cert_issues + 1 < get_org_cert_rec.sub_org_cert )) ) THEN
1470 g_refresh_flag := 'Y';
1471 m_certification_list(l_pk2) := l_pk2;
1472 goto refresh_all_records;
1473 ELSE
1474 ***********/
1475 UPDATE amw_org_cert_eval_sum
1476 SET last_update_date = sysdate,
1477 last_updated_by = fnd_global.user_id,
1478 last_update_login = fnd_global.conc_login_id,
1479 sub_org_cert = least(sub_org_cert+1,total_sub_org),
1480 sub_org_cert_issues = least(sub_org_cert_issues+1,sub_org_cert+1,total_sub_org)
1481 WHERE certification_id = l_pk1
1482 AND organization_id = Get_org_cert_Rec.organization_id;
1483 -- END IF;
1484
1485 ELSIF l_old_cert = 'EFFECTIVE' AND l_new_cert <> 'EFFECTIVE' THEN
1486 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1487 *********IF (get_org_cert_rec.sub_org_cert_issues + 1 > get_org_cert_rec.sub_org_cert )THEN ****/
1488 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1489 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (get_org_cert_rec.sub_org_cert_issues + 1 < get_org_cert_rec.sub_org_cert ) ) THEN
1490 g_refresh_flag := 'Y';
1491 m_certification_list(l_pk2) := l_pk2;
1492 goto refresh_all_records;
1493 ELSE
1494 ***********/
1495 UPDATE amw_org_cert_eval_sum
1496 SET last_update_date = sysdate,
1497 last_updated_by = fnd_global.user_id,
1498 last_update_login = fnd_global.conc_login_id,
1499 sub_org_cert_issues = least(sub_org_cert_issues+1,sub_org_cert,total_sub_org)
1500 WHERE certification_id = l_pk1
1501 AND organization_id = Get_org_cert_Rec.organization_id;
1502 --END IF;
1503
1504 ELSIF l_old_cert <> 'EFFECTIVE' AND l_new_cert = 'EFFECTIVE' THEN
1505 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1506 *********IF (get_org_cert_rec.sub_org_cert_issues + 1 > get_org_cert_rec.sub_org_cert )THEN ****/
1507 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1508 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (get_org_cert_rec.sub_org_cert_issues + 1 < get_org_cert_rec.sub_org_cert ) ) THEN
1509 g_refresh_flag := 'Y';
1510 m_certification_list( l_pk2) := l_pk2;
1511 goto refresh_all_records;
1512 ELSE
1513 ************/
1514 UPDATE amw_org_cert_eval_sum
1515 SET last_update_date = sysdate,
1516 last_updated_by = fnd_global.user_id,
1517 last_update_login = fnd_global.conc_login_id,
1518 sub_org_cert_issues = greatest(0,sub_org_cert_issues-1)
1519 WHERE certification_id = l_pk1
1520 AND organization_id = Get_org_cert_Rec.organization_id;
1521 --END IF;
1522 END IF;
1523
1524 END LOOP;
1525
1526 ELSIF l_obj_name = 'AMW_ORG_PROCESS' THEN
1527
1528 --set organization_id, certification_id for FCH
1529 l_fch_org_id := l_pk3;
1530 l_fch_cert_id := l_pk2;
1531
1532
1533 OPEN c_old_opinion(l_opin_log_id);
1534 FETCH c_old_opinion INTO l_old_cert;
1535 CLOSE c_old_opinion;
1536
1537 OPEN c_orgs_pending_in_scope(l_pk2);
1538 FETCH c_orgs_pending_in_scope INTO l_orgs_pending;
1539 CLOSE c_orgs_pending_in_scope;
1540
1541 UPDATE amw_proc_cert_eval_sum
1542 SET last_update_date = sysdate,
1543 last_updated_by = fnd_global.user_id,
1544 last_update_login = fnd_global.conc_login_id,
1545 certification_opinion_id = l_opin_id
1546 WHERE certification_id = l_pk2
1547 AND organization_id = l_pk3
1548 AND process_id = l_pk1;
1549
1550 m_org_cert_proc_certified := 0;
1551 m_org_cert_total_proc := 0;
1552 m_org_cert_proc_cert_issues := 0;
1553 m_dashbd_proc_not_cert := 0;
1554 m_dashbd_org_proc_not_cert := 0;
1555 m_dashbd_proc_cert_issues := 0;
1556 m_dashbd_org_proc_cert_issues := 0;
1557
1558 OPEN c_org_cert(l_pk2, l_pk3);
1559 FETCH c_org_cert INTO m_org_cert_proc_certified, m_org_cert_total_proc, m_org_cert_proc_cert_issues;
1560 CLOSE c_org_cert;
1561
1562 OPEN Get_cert_dashboard(l_pk2);
1563 FETCH Get_cert_dashboard INTO m_dashbd_proc_not_cert,m_dashbd_org_proc_not_cert ,m_dashbd_proc_cert_issues ,m_dashbd_org_proc_cert_issues;
1564 CLOSE Get_cert_dashboard;
1565
1566 IF l_old_cert IS NULL AND l_new_cert = 'EFFECTIVE' THEN
1567 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1568 ********* IF (m_org_cert_proc_certified + 1 > m_org_cert_total_proc)THEN ****/
1569 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1570 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (m_org_cert_proc_certified + 1 < m_org_cert_total_proc) ) THEN
1571 g_refresh_flag := 'Y';
1572 m_certification_list(l_pk2) := l_pk2;
1573 goto refresh_all_records;
1574 ELSE
1575 **********/
1576 UPDATE amw_org_cert_eval_sum
1577 SET last_update_date = sysdate,
1578 last_updated_by = fnd_global.user_id,
1579 last_update_login = fnd_global.conc_login_id,
1580 processes_certified = least(processes_certified+1,total_processes)
1581 WHERE certification_id = l_pk2
1582 AND organization_id = l_pk3;
1583 --END IF;
1584
1585
1589 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1586 IF l_pk3 = fnd_profile.value('AMW_GLOBAL_ORG_ID') THEN
1587 /************ If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1588 ************IF (M_dashbd_proc_not_cert -1 < 0 )THEN *************/
1590 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (M_dashbd_proc_not_cert -1 > 0 ) ) THEN
1591 g_refresh_flag := 'Y';
1592 m_certification_list(l_pk2) := l_pk2;
1593 goto refresh_all_records;
1594 ELSE
1595 *************/
1596 UPDATE amw_cert_dashboard_sum
1597 SET last_update_date = sysdate,
1598 last_updated_by = fnd_global.user_id,
1599 last_update_login = fnd_global.conc_login_id,
1600 orgs_pending_in_scope = l_orgs_pending,
1601 processes_not_cert = greatest(0,processes_not_cert-1)
1602 WHERE certification_id = l_pk2;
1603 --END IF;
1604
1605 ELSE
1606 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1607 ************ IF (M_dashbd_org_proc_not_cert -1 < 0 )THEN ******* *******/
1608 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1609 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (M_dashbd_org_proc_not_cert -1 > 0 ) ) THEN
1610 g_refresh_flag := 'Y';
1611 m_certification_list(l_pk2) := l_pk2;
1612 goto refresh_all_records;
1613 ELSE
1614 ********/
1615 UPDATE amw_cert_dashboard_sum
1616 SET last_update_date = sysdate,
1617 last_updated_by = fnd_global.user_id,
1618 last_update_login = fnd_global.conc_login_id,
1619 orgs_pending_in_scope = l_orgs_pending,
1620 org_process_not_cert = greatest(0,org_process_not_cert-1)
1621 WHERE certification_id = l_pk2;
1622 -- END IF;
1623 END IF;
1624
1625
1626 ELSIF l_old_cert IS NULL AND l_new_cert <> 'EFFECTIVE' THEN
1627 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1628 **********IF (m_org_cert_proc_certified + 1 > m_org_cert_total_proc) or (M_org_cert_proc_cert_issues + 1 > m_org_cert_proc_certified)) THEN ****/
1629 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1630 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR ( (m_org_cert_proc_certified + 1 < m_org_cert_total_proc) AND (M_org_cert_proc_cert_issues + 1 < m_org_cert_proc_certified)) ) THEN
1631 g_refresh_flag := 'Y';
1632 m_certification_list(l_pk2) := l_pk2;
1633 goto refresh_all_records;
1634 ELSE
1635 ***************/
1636 UPDATE amw_org_cert_eval_sum
1637 SET last_update_date = sysdate,
1638 last_updated_by = fnd_global.user_id,
1639 last_update_login = fnd_global.conc_login_id,
1640 processes_certified = least(processes_certified+1,total_processes),
1641 proc_cert_issues = least(proc_cert_issues+1,processes_certified+1,total_processes)
1642 WHERE certification_id = l_pk2
1643 AND organization_id = l_pk3;
1644 --END IF;
1645
1646 IF l_pk3 = fnd_profile.value('AMW_GLOBAL_ORG_ID') THEN
1647 UPDATE amw_cert_dashboard_sum
1648 SET last_update_date = sysdate,
1649 last_updated_by = fnd_global.user_id,
1650 last_update_login = fnd_global.conc_login_id,
1651 orgs_pending_in_scope = l_orgs_pending,
1652 processes_not_cert = greatest(0,processes_not_cert-1),
1653 processes_cert_issues = processes_cert_issues+1
1654 WHERE certification_id = l_pk2;
1655 ELSE
1656
1657 UPDATE amw_cert_dashboard_sum
1658 SET last_update_date = sysdate,
1659 last_updated_by = fnd_global.user_id,
1660 last_update_login = fnd_global.conc_login_id,
1661 orgs_pending_in_scope = l_orgs_pending,
1662 org_process_not_cert = greatest(0,org_process_not_cert-1),
1663 org_process_cert_issues = org_process_cert_issues+1
1664 WHERE certification_id = l_pk2;
1665 END IF;
1666
1667 ELSIF l_old_cert <> 'EFFECTIVE' AND l_new_cert = 'EFFECTIVE' THEN
1668 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1669 *********IF (M_org_cert_proc_cert_issues -1 < 0 ) THEN ***************/
1670 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1671 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (M_org_cert_proc_cert_issues -1 > 0 ) ) THEN
1672 g_refresh_flag := 'Y';
1673 m_certification_list(l_pk2) := l_pk2;
1674 goto refresh_all_records;
1675 ELSE
1676 ***********/
1677 UPDATE amw_org_cert_eval_sum
1678 SET last_update_date = sysdate,
1679 last_updated_by = fnd_global.user_id,
1680 last_update_login = fnd_global.conc_login_id,
1681 proc_cert_issues = greatest(0,proc_cert_issues-1)
1682 WHERE certification_id = l_pk2
1683 AND organization_id = l_pk3;
1684 --END IF;
1685
1686
1687 IF l_pk3 = fnd_profile.value('AMW_GLOBAL_ORG_ID') THEN
1688 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1689 **********IF (M_dashbd_proc_not_cert -1 < 0 )THEN *********************/
1690 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1691 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (M_dashbd_proc_not_cert -1 > 0 ) ) THEN
1692 g_refresh_flag := 'Y';
1693 m_certification_list(l_pk2) := l_pk2;
1694 goto refresh_all_records;
1695 ELSE
1696 *************/
1697 UPDATE amw_cert_dashboard_sum
1698 SET last_update_date = sysdate,
1699 last_updated_by = fnd_global.user_id,
1700 last_update_login = fnd_global.conc_login_id,
1701 orgs_pending_in_scope = l_orgs_pending,
1702 processes_cert_issues = greatest(0,processes_cert_issues-1)
1703 WHERE certification_id = l_pk2;
1704 -- END IF;
1705 ELSE
1709 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (M_dashbd_org_proc_not_cert -1 > 0 ) ) THEN
1706 /***************** If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1707 ***************** IF (M_dashbd_org_proc_not_cert -1 < 0 )THEN *************************/
1708 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1710 g_refresh_flag := 'Y';
1711 m_certification_list(l_pk2) := l_pk2;
1712 goto refresh_all_records;
1713 ELSE
1714 **************/
1715 UPDATE amw_cert_dashboard_sum
1716 SET last_update_date = sysdate,
1717 last_updated_by = fnd_global.user_id,
1718 last_update_login = fnd_global.conc_login_id,
1719 orgs_pending_in_scope = l_orgs_pending,
1720 org_process_cert_issues = greatest(org_process_cert_issues-1,0)
1721 WHERE certification_id = l_pk2;
1722 -- END IF;
1723 END IF;
1724
1725 ELSIF l_old_cert = 'EFFECTIVE' AND l_new_cert <> 'EFFECTIVE' THEN
1726 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1727 ********* IF (M_org_cert_proc_cert_issues + 1 > m_org_cert_proc_certified) THEN ****/
1728 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1729 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (M_org_cert_proc_cert_issues + 1 < m_org_cert_proc_certified) ) THEN
1730 g_refresh_flag := 'Y';
1731 m_certification_list(l_pk2) := l_pk2;
1732 goto refresh_all_records;
1733 ELSE
1734 **************/
1735 UPDATE amw_org_cert_eval_sum
1736 SET last_update_date = sysdate,
1737 last_updated_by = fnd_global.user_id,
1738 last_update_login = fnd_global.conc_login_id,
1739 proc_cert_issues = least(proc_cert_issues+1,processes_certified,total_processes)
1740 WHERE certification_id = l_pk2
1741 AND organization_id = l_pk3;
1742 --END IF;
1743
1744 IF l_pk3 = fnd_profile.value('AMW_GLOBAL_ORG_ID') THEN
1745 UPDATE amw_cert_dashboard_sum
1746 SET last_update_date = sysdate,
1747 last_updated_by = fnd_global.user_id,
1748 last_update_login = fnd_global.conc_login_id,
1749 orgs_pending_in_scope = l_orgs_pending,
1750 processes_cert_issues = processes_cert_issues+1
1751 WHERE certification_id = l_pk2;
1752 ELSE
1753 UPDATE amw_cert_dashboard_sum
1754 SET last_update_date = sysdate,
1755 last_updated_by = fnd_global.user_id,
1756 last_update_login = fnd_global.conc_login_id,
1757 orgs_pending_in_scope = l_orgs_pending,
1758 org_process_cert_issues = org_process_cert_issues+1
1759 WHERE certification_id = l_pk2;
1760 END IF;
1761
1762 END IF;
1763
1764 --find all of parent organizations
1765 FOR Get_parent_process_Rec in Get_parent_process(l_pk2, l_pk3, l_pk1) LOOP
1766 exit when Get_parent_process %notfound;
1767 IF l_old_cert IS NULL AND l_new_cert = 'EFFECTIVE' THEN
1768 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1769 ****** IF (Get_parent_process_Rec.sub_process_cert + 1 > Get_parent_process_Rec.total_sub_process_cert) THEN ****/
1770 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1771 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (Get_parent_process_Rec.sub_process_cert + 1 < Get_parent_process_Rec.total_sub_process_cert) ) THEN
1772 g_refresh_flag := 'Y';
1773 m_certification_list(l_pk2) := l_pk2;
1774 goto refresh_all_records;
1775 ELSE
1776 ************/
1777 UPDATE amw_proc_cert_eval_sum
1778 SET last_update_date = sysdate,
1779 last_updated_by = fnd_global.user_id,
1780 last_update_login = fnd_global.conc_login_id,
1781 sub_process_cert = least(sub_process_cert+1,total_sub_process_cert)
1782 WHERE certification_id = Get_parent_process_Rec.certification_id
1783 AND organization_id = Get_parent_process_Rec.organization_id
1784 AND process_id = Get_parent_process_Rec.process_id;
1785 --END IF;
1786
1787 ELSIF l_old_cert IS NULL AND l_new_cert <> 'EFFECTIVE' THEN
1788 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1789 ****** IF ( (Get_parent_process_Rec.sub_process_cert + 1 > Get_parent_process_Rec.total_sub_process_cert)
1790 ******* OR (Get_parent_process_Rec.sub_process_cert_issues + 1 > Get_parent_process_Rec.sub_process_cert)) THEN ****/
1791 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1792 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR ( (Get_parent_process_Rec.sub_process_cert + 1 < Get_parent_process_Rec.total_sub_process_cert)
1793 AND (Get_parent_process_Rec.sub_process_cert_issues + 1 < Get_parent_process_Rec.sub_process_cert)) ) THEN
1794 g_refresh_flag := 'Y';
1795 m_certification_list(l_pk2) := l_pk2;
1796 goto refresh_all_records;
1797 ELSE
1798 ********/
1799 UPDATE amw_proc_cert_eval_sum
1800 SET last_update_date = sysdate,
1801 last_updated_by = fnd_global.user_id,
1802 last_update_login = fnd_global.conc_login_id,
1803 sub_process_cert = least(sub_process_cert+1,total_sub_process_cert),
1804 sub_process_cert_issues = least(sub_process_cert_issues+1,sub_process_cert+1,total_sub_process_cert)
1805 WHERE certification_id = Get_parent_process_Rec.certification_id
1806 AND organization_id = Get_parent_process_Rec.organization_id
1807 AND process_id = Get_parent_process_Rec.process_id;
1808 --END IF;
1809
1810 ELSIF l_old_cert <> 'EFFECTIVE' AND l_new_cert = 'EFFECTIVE' THEN
1811 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1812 *********IF (Get_parent_process_Rec.sub_process_cert_issues - 1 < 0 ) THEN ****/
1813 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1817 goto refresh_all_records;
1814 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (Get_parent_process_Rec.sub_process_cert_issues - 1 > 0 ) ) THEN
1815 g_refresh_flag := 'Y';
1816 m_certification_list(l_pk2) := l_pk2;
1818 ELSE
1819 ************/
1820 UPDATE amw_proc_cert_eval_sum
1821 SET last_update_date = sysdate,
1822 last_updated_by = fnd_global.user_id,
1823 last_update_login = fnd_global.conc_login_id,
1824 sub_process_cert_issues = greatest(0,sub_process_cert_issues-1)
1825 WHERE certification_id = Get_parent_process_Rec.certification_id
1826 AND organization_id = Get_parent_process_Rec.organization_id
1827 AND process_id = Get_parent_process_Rec.process_id;
1828 --END IF;
1829 ELSIF l_old_cert = 'EFFECTIVE' AND l_new_cert <> 'EFFECTIVE' THEN
1830 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1831 *********IF (Get_parent_process_Rec.sub_process_cert_issues + 1 > Get_parent_process_Rec.sub_process_cert ) THEN ****/
1832 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1833 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (Get_parent_process_Rec.sub_process_cert_issues + 1 < Get_parent_process_Rec.sub_process_cert ) ) THEN
1834 g_refresh_flag := 'Y';
1835 m_certification_list(l_pk2) := l_pk2;
1836 goto refresh_all_records;
1837 ELSE
1838 ***********/
1839 UPDATE amw_proc_cert_eval_sum
1840 SET last_update_date = sysdate,
1841 last_updated_by = fnd_global.user_id,
1842 last_update_login = fnd_global.conc_login_id,
1843 sub_process_cert_issues = least(sub_process_cert_issues+1,sub_process_cert,total_sub_process_cert)
1844 WHERE certification_id = Get_parent_process_Rec.certification_id
1845 AND organization_id = Get_parent_process_Rec.organization_id
1846 AND process_id = Get_parent_process_Rec.process_id;
1847 -- END IF;
1848 END IF;
1849 END LOOP;
1850
1851 --find related org processes
1852 FOR Get_related_org_proc_Rec in Get_related_org_proc(l_pk2, l_pk3, l_pk1) LOOP
1853 exit when Get_related_org_proc %notfound;
1854
1855 IF l_old_cert IS NULL AND l_new_cert = 'EFFECTIVE' THEN
1856 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1857 ********* IF (Get_related_org_proc_Rec.org_process_cert + 1 > Get_related_org_proc_Rec.total_org_process_cert) THEN ****/
1858 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1859 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (Get_related_org_proc_Rec.org_process_cert + 1 < Get_related_org_proc_Rec.total_org_process_cert) ) THEN
1860 g_refresh_flag := 'Y';
1861 m_certification_list(l_pk2) := l_pk2;
1862 goto refresh_all_records;
1863 ELSE
1864 ************/
1865 UPDATE amw_proc_cert_eval_sum
1866 SET last_update_date = sysdate,
1867 last_updated_by = fnd_global.user_id,
1868 last_update_login = fnd_global.conc_login_id,
1869 org_process_cert = least(org_process_cert+1,total_org_process_cert)
1870 WHERE certification_id = Get_related_org_proc_Rec.certification_id
1871 AND process_id = Get_related_org_proc_Rec.process_id
1872 AND organization_id = Get_related_org_proc_Rec.organization_id;
1873 --END IF;
1874
1875
1876 ELSIF l_old_cert IS NULL AND l_new_cert <> 'EFFECTIVE' THEN
1877 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1878 ********* IF ( (Get_related_org_proc_Rec.org_process_cert + 1 > Get_related_org_proc_Rec.total_org_process_cert) or
1879 *******( (Get_related_org_proc_Rec.org_process_cert_issues + 1 > Get_related_org_proc_Rec.org_process_cert) ) THEN ****/
1880 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1881 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR ( (Get_related_org_proc_Rec.org_process_cert + 1 < Get_related_org_proc_Rec.total_org_process_cert)
1882 and (Get_related_org_proc_Rec.org_process_cert_issues + 1 < Get_related_org_proc_Rec.org_process_cert) ) ) THEN
1883 g_refresh_flag := 'Y';
1884 m_certification_list(l_pk2) := l_pk2;
1885 goto refresh_all_records;
1886 ELSE
1887 ***************/
1888 UPDATE amw_proc_cert_eval_sum
1889 SET last_update_date = sysdate,
1890 last_updated_by = fnd_global.user_id,
1891 last_update_login = fnd_global.conc_login_id,
1892 org_process_cert = least(org_process_cert+1,total_org_process_cert),
1893 org_process_cert_issues = least(org_process_cert_issues+1,org_process_cert+1,total_org_process_cert)
1894 WHERE certification_id = Get_related_org_proc_Rec.certification_id
1895 AND process_id = Get_related_org_proc_Rec.process_id
1896 AND organization_id = Get_related_org_proc_Rec.organization_id;
1897 -- END IF;
1898
1899 ELSIF l_old_cert <> 'EFFECTIVE' AND l_new_cert = 'EFFECTIVE' THEN
1900 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1901 ******* IF (Get_related_org_proc_Rec.org_process_cert_issues -1 < 0 ) THEN ********************/
1902 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1903 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (Get_related_org_proc_Rec.org_process_cert_issues -1 > 0 ) ) THEN
1904 g_refresh_flag := 'Y';
1905 m_certification_list(l_pk2) := l_pk2;
1906 goto refresh_all_records;
1907 ELSE
1908 ************/
1909 UPDATE amw_proc_cert_eval_sum
1910 SET last_update_date = sysdate,
1911 last_updated_by = fnd_global.user_id,
1912 last_update_login = fnd_global.conc_login_id,
1913 org_process_cert_issues = greatest(0,org_process_cert_issues-1)
1914 WHERE certification_id = Get_related_org_proc_Rec.certification_id
1915 AND process_id = Get_related_org_proc_Rec.process_id
1916 AND organization_id = Get_related_org_proc_Rec.organization_id;
1917 -- END IF;
1918
1922 /********* remove 'IF' logic now because the refresh logic is disabled temporarily
1919 ELSIF l_old_cert = 'EFFECTIVE' AND l_new_cert <> 'EFFECTIVE' THEN
1920 /********* If the display format is a/b/c, then a >= 0 and b>= a and c>= b
1921 ******* IF (Get_related_org_proc_Rec.org_process_cert_issues + 1 > Get_related_org_proc_Rec.org_process_cert ) THEN ********************/
1923 IF NOT ( (m_certification_list.exists(l_pk2)) OR (g_refresh_flag = 'Y') OR (Get_related_org_proc_Rec.org_process_cert_issues + 1 < Get_related_org_proc_Rec.org_process_cert ) ) THEN
1924 g_refresh_flag := 'Y';
1925 m_certification_list(l_pk2) := l_pk2;
1926 goto refresh_all_records;
1927 ELSE
1928 *************/
1929 UPDATE amw_proc_cert_eval_sum
1930 SET last_update_date = sysdate,
1931 last_updated_by = fnd_global.user_id,
1932 last_update_login = fnd_global.conc_login_id,
1933 org_process_cert_issues = least(org_process_cert_issues+1,org_process_cert,total_org_process_cert)
1934 WHERE certification_id = Get_related_org_proc_Rec.certification_id
1935 AND process_id = Get_related_org_proc_Rec.process_id
1936 AND organization_id = Get_related_org_proc_Rec.organization_id;
1937 --END IF;
1938 END IF;
1939 END LOOP;
1940
1941 END IF;
1942
1943 <<refresh_all_records>>
1944 --refresh all of the summary tables amw_cert_dashboard_sum, amw_proc_cert_eval_sum, amw_org_cert_eval_sum
1945 --IF ((G_REFRESH_FLAG = 'Y') or (G_REFRESH_FLAG = 'y') ) THEN
1946 --l_index := m_certification_list.FIRST;
1947 --WHILE l_index <= m_certification_list.LAST LOOP
1948
1949 --AMW_PROCESS_CERT_SUMMARY.POPULATE_ALL_CERT_SUMMARY
1950 --(x_errbuf => l_msg_data,
1951 -- x_retcode => l_msg_count,
1952 -- p_certification_id => m_certification_list(l_index)
1953 --);
1954
1955
1956 --AMW_PROCESS_CERT_SUMMARY.Populate_All_Cert_General_Sum
1957 --(errbuf => l_msg_data,
1958 -- retcode => l_msg_count,
1959 -- p_certification_id => m_certification_list(l_index)
1960 --);
1961
1962 --AMW_ORG_CERT_EVAL_SUM_PVT.populate_org_cert_summary
1963 --(x_errbuf => l_msg_data,
1964 -- x_retcode => l_msg_count,
1965 -- p_certification_id => m_certification_list(l_index)
1966 --);
1967
1968 --l_index := l_index + 1;
1969 --END LOOP;
1970
1971 --END IF;
1972
1973 OPEN c_fch_vs_id;
1974 FETCH c_fch_vs_id into l_fch_vs_id;
1975 CLOSE c_fch_vs_id;
1976
1977 IF fnd_profile.value('AMW_SUBSIDIARY_AUDIT_UNIT') = to_char(l_fch_vs_id) THEN
1978 amw_org_cert_aggr_pkg.update_org_cert_aggr_rows(l_fch_cert_id, l_fch_org_id);
1979 END IF;
1980
1981 -- somehow the change was not being committed to db without
1982 -- the following commit. so we temporarily put commit here, and we
1983 -- still need to invetigate why the transaction was not committed
1984 -- automatically.
1985 commit;
1986 Return 'SUCCESS';
1987
1988 EXCEPTION
1989 WHEN OTHERS THEN
1990 ROLLBACK TO Certification_Update_Event;
1991
1992 FND_MESSAGE.SET_NAME( 'AMW', 'GENERIC_MESSAGE' );
1993 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
1994 FND_MSG_PUB.ADD;
1995
1996 WF_CORE.CONTEXT('AMW_PROCCERT_EVENT_PVT', 'CERTIFICAITON_UPDATE',
1997 p_event.getEventName(), p_subscription_guid);
1998 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1999
2000 RETURN 'ERROR';
2001
2002 END Certification_Update;
2003
2004 END AMW_PROCCERT_EVENT_PVT;