[Home] [Help]
PACKAGE BODY: APPS.AMW_PROCESS_CERT_SUMMARY
Source
1 PACKAGE BODY AMW_PROCESS_CERT_SUMMARY as
2 /* $Header: amwpcesb.pls 120.5.12000000.6 2007/03/28 21:21:09 npanandi ship $ */
3
4 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
5 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
6
7
8
9 PROCEDURE populate_assoc_opinion (
10 p_certification_id IN NUMBER)
11 IS
12 CURSOR c_obj_opin_type_id (c_obj_name VARCHAR2) IS
13 SELECT aoot.object_opinion_type_id
14 FROM AMW_OBJECT_OPINION_TYPES aoot, AMW_OPINION_TYPES_B aot,
15 FND_OBJECTS fo
16 WHERE aoot.OPINION_TYPE_ID = aot.OPINION_TYPE_ID
17 AND aoot.OBJECT_ID = fo.OBJECT_ID
18 AND aot.opinion_type_code = 'EVALUATION'
19 AND fo.obj_name = c_obj_name;
20
21 l_obj_opinion_type_id NUMBER;
22 BEGIN
23
24 OPEN c_obj_opin_type_id ('AMW_ORG_PROCESS_RISK');
25 FETCH c_obj_opin_type_id INTO l_obj_opinion_type_id;
26 CLOSE c_obj_opin_type_id;
27
28 UPDATE amw_risk_associations assoc
29 SET pk4 = (SELECT max(opinion_log_id)
30 FROM amw_opinions_log opin
31 WHERE opin.object_opinion_type_id = l_obj_opinion_type_id
32 AND opin.pk1_value = assoc.risk_id
33 AND opin.pk3_value = assoc.pk2 -- organization_id
34 AND NVL(opin.pk4_value, -1) =
35 NVL(assoc.pk3, -1)) -- process_id
36 ,last_update_date = sysdate
37 WHERE pk1 = p_certification_id
38 ---04.05.05 npanandi: added object_type below
39 and object_type='BUSIPROC_CERTIFICATION';
40
41
42 OPEN c_obj_opin_type_id ('AMW_ORG_CONTROL');
43 FETCH c_obj_opin_type_id INTO l_obj_opinion_type_id;
44 CLOSE c_obj_opin_type_id;
45
46 UPDATE amw_control_associations assoc
47 SET pk5 = (SELECT max(opinion_log_id)
48 FROM amw_opinions_log opin
49 WHERE opin.object_opinion_type_id = l_obj_opinion_type_id
50 AND opin.pk1_value = assoc.control_id
51 AND opin.pk3_value = assoc.pk2) -- organization_id
52 ,last_update_date = sysdate
53 WHERE pk1 = p_certification_id
54 ---04.05.05 npanandi: added object_type below
55 and object_type='BUSIPROC_CERTIFICATION';
56
57 OPEN c_obj_opin_type_id ('AMW_ORG_AP_CONTROL');
58 FETCH c_obj_opin_type_id INTO l_obj_opinion_type_id;
59 CLOSE c_obj_opin_type_id;
60
61
62 UPDATE amw_ap_associations assoc
63 SET pk4 = (SELECT max(opinion_log_id)
64 FROM amw_opinions_log opin
65 WHERE opin.object_opinion_type_id = l_obj_opinion_type_id
66 AND opin.pk1_value = assoc.pk3 --control_id
67 AND opin.pk3_value = assoc.pk2 -- organization_id
68 AND opin.pk4_value = assoc.audit_procedure_id) -- audit_procedure_id
69 ,last_update_date = sysdate
70 WHERE pk1 = p_certification_id
71 ---04.05.05 npanandi: added object_type below
72 and object_type='BUSIPROC_CERTIFICATION';
73
74
75 END populate_assoc_opinion;
76
77
78 -- ===========================================================================
79 -- Procedure : UPDATE_SUMMARY_TABLE
80 -- Description : Update the various columns in the summary table
81 -- The values for various columns are derived from different cursors
82 -- ===========================================================================
83 PROCEDURE update_summary_table
84 (p_process_id IN NUMBER,
85 p_org_id IN NUMBER,
86 p_certification_id IN NUMBER
87 )
88 IS
89 CURSOR get_certification_opinion
90 IS
91 SELECT opinion.opinion_id
92 FROM amw_opinions_v opinion
93 WHERE opinion.pk3_value = p_org_id
94 AND opinion.pk2_value = p_certification_id
95 AND opinion.pk1_value = p_process_id
96 AND opinion.opinion_type_code = 'CERTIFICATION'
97 AND opinion.object_name = 'AMW_ORG_PROCESS';
98
99 CURSOR get_evaluation_opinion
100 IS
101 SELECT opinion.opinion_id
102 FROM amw_opinions_v opinion
103 WHERE (opinion.authored_date in (SELECT MAX(opinion2.authored_date)
104 FROM amw_opinions_v opinion2
105 WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
106 AND opinion2.pk1_value = opinion.pk1_value
107 AND opinion2.pk3_value = opinion.pk3_value)
108 )
109 AND opinion.pk1_value = p_process_id
110 AND opinion.pk3_value = p_org_id
111 AND opinion.opinion_type_code = 'EVALUATION'
112 AND opinion.object_name = 'AMW_ORG_PROCESS';
113
114 CURSOR get_evaluation_opinion_log
115 IS
116 SELECT opinion.opinion_log_id
117 FROM amw_opinions_log_v opinion
118 WHERE (opinion.authored_date in (SELECT MAX(opinion2.authored_date)
119 FROM amw_opinions opinion2
120 WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
121 AND opinion2.pk1_value = opinion.pk1_value
122 AND opinion2.pk3_value = opinion.pk3_value)
123 )
124 AND opinion.pk1_value = p_process_id
125 AND opinion.pk3_value = p_org_id
126 AND opinion.opinion_type_code = 'EVALUATION'
127 AND opinion.object_name = 'AMW_ORG_PROCESS';
128
129 CURSOR get_unmitigated_risks
130 IS
131 SELECT count(1)
132 FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
133 FROM amw_risk_associations ara, amw_opinions_v aov
134 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
135 AND ara.pk1 = p_certification_id
136 AND ara.pk2 = p_org_id
137 AND ara.pk3 IN (SELECT DISTINCT process_id
138 FROM amw_execution_scope
139 START WITH process_id = p_process_id
140 AND organization_id = p_org_id
141 AND entity_id = p_certification_id
142 ---07.05.2005 npanandi: add entityType, bugfix 4471783
143 and entity_type='BUSIPROC_CERTIFICATION'
144 CONNECT BY PRIOR process_id = parent_process_id
145 AND organization_id = PRIOR organization_id
146 AND entity_id = PRIOR entity_id
147 ---07.05.2005 npanandi: add entityType, bugfix 4471783
148 and entity_type=prior entity_type)
149 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
150 AND aov.opinion_type_code = 'EVALUATION'
151 AND aov.pk3_value = ara.pk2 --org_id
152 AND aov.pk4_value = ara.pk3 --process_id
153 AND aov.pk1_value = ara.risk_id
154 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
155 FROM amw_opinions aov2
156 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
157 AND aov2.pk4_value = aov.pk4_value
158 AND aov2.pk3_value = aov.pk3_value
159 AND aov2.pk1_value = aov.pk1_value)
160 AND aov.audit_result_code <> 'EFFECTIVE');
161
162 CURSOR get_evaluated_risks
163 IS
164 SELECT count(1)
165 FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
166 FROM amw_risk_associations ara, amw_opinions_v aov
167 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
168 AND ara.pk1 = p_certification_id
169 AND ara.pk2 = p_org_id
170 AND ara.pk3 IN (SELECT DISTINCT process_id
171 FROM amw_execution_scope
172 START WITH process_id = p_process_id
173 AND organization_id = p_org_id
174 AND entity_id = p_certification_id
175 ---07.05.2005 npanandi: add entityType, bugfix 4471783
176 and entity_type='BUSIPROC_CERTIFICATION'
177 CONNECT BY PRIOR process_id = parent_process_id
178 AND organization_id = PRIOR organization_id
179 AND entity_id = PRIOR entity_id
180 ---07.05.2005 npanandi: add entityType, bugfix 4471783
181 and entity_type=prior entity_type
182 )
183 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
184 AND aov.opinion_type_code = 'EVALUATION'
185 AND aov.pk3_value = ara.pk2 --org_id
186 AND aov.pk4_value = ara.pk3 --process_id
187 AND aov.pk1_value = ara.risk_id
188 AND aov.audit_result_code IS NOT NULL
189 );
190
191 CURSOR get_total_risks
192 IS
193 SELECT count(DISTINCT ara.risk_id)
194 FROM amw_risk_associations ara
195 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
196 AND ara.pk1 = p_certification_id
197 AND ara.pk2 = p_org_id
198 AND ara.pk3 IN (SELECT DISTINCT process_id
199 FROM amw_execution_scope
200 START WITH process_id = p_process_id
201 AND organization_id = p_org_id
202 AND entity_id = p_certification_id
203 ---07.05.2005 npanandi: add entityType, bugfix 4471783
204 and entity_type='BUSIPROC_CERTIFICATION'
205 CONNECT BY PRIOR process_id = parent_process_id
206 AND organization_id = PRIOR organization_id
207 AND entity_id = PRIOR entity_id
208 ---07.05.2005 npanandi: add entityType, bugfix 4471783
209 and entity_type=prior entity_type
210 );
211
212 --modified by dliao 10.14.2005
213 --remove risk_id
214 CURSOR get_ineffective_controls
215 IS
216 SELECT count(1)
217 --FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id, aca.control_id
218 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
219 FROM amw_control_associations aca,amw_opinions_v aov
220 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
221 AND aca.pk1 = p_certification_id
222 AND aca.pk2 = p_org_id
223 AND aca.pk3 IN (SELECT DISTINCT process_id
224 FROM amw_execution_scope
225 START WITH process_id = p_process_id
226 AND organization_id = p_org_id
227 AND entity_id = p_certification_id
228 ---07.05.2005 npanandi: add entityType, bugfix 4471783
229 and entity_type='BUSIPROC_CERTIFICATION'
230 CONNECT BY PRIOR process_id = parent_process_id
231 AND organization_id = PRIOR organization_id
232 AND entity_id = PRIOR entity_id
233 ---07.05.2005 npanandi: add entityType, bugfix 4471783
234 and entity_type=prior entity_type
235 )
236 AND aov.object_name = 'AMW_ORG_CONTROL'
237 AND aov.opinion_type_code = 'EVALUATION'
238 AND aov.pk3_value = p_org_id
239 AND aov.pk1_value = aca.control_id
240 AND aov.audit_result_code <> 'EFFECTIVE'
241 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
242 FROM amw_opinions aov2
243 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
244 AND aov2.pk3_value = aov.pk3_value
245 AND aov2.pk1_value = aov.pk1_value)
246 )
247 ;
248
249 CURSOR get_evaluated_controls
250 IS
251 SELECT count(1)
252 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
253 FROM amw_control_associations aca,amw_opinions_v aov
254 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
255 AND aca.pk1 = p_certification_id
256 AND aca.pk2 = p_org_id
257 AND aca.pk3 IN (SELECT DISTINCT process_id
258 FROM amw_execution_scope
259 START WITH process_id = p_process_id
260 AND organization_id = p_org_id
261 AND entity_id = p_certification_id
262 ---07.05.2005 npanandi: add entityType, bugfix 4471783
263 and entity_type='BUSIPROC_CERTIFICATION'
264 CONNECT BY PRIOR process_id = parent_process_id
265 AND organization_id = PRIOR organization_id
266 AND entity_id = PRIOR entity_id
267 ---07.05.2005 npanandi: add entityType, bugfix 4471783
268 and entity_type=prior entity_type
269 )
270 AND aov.object_name = 'AMW_ORG_CONTROL'
271 AND aov.opinion_type_code = 'EVALUATION'
272 AND aov.pk3_value = p_org_id
273 AND aov.pk1_value = aca.control_id
274 AND aov.audit_result_code IS NOT NULL);
275
276 CURSOR get_total_controls
277 IS
278 SELECT count(1) from
279 (select DISTINCT aca.pk2 organization_id,aca.pk3 process_id, aca.control_id
280 FROM amw_control_associations aca
281 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
282 AND aca.pk1 = p_certification_id
283 AND aca.pk2 = p_org_id
284 AND aca.pk3 IN (SELECT DISTINCT process_id
285 FROM amw_execution_scope
286 START WITH process_id = p_process_id
287 AND organization_id = p_org_id
288 AND entity_id = p_certification_id
289 ---07.05.2005 npanandi: add entityType, bugfix 4471783
290 and entity_type='BUSIPROC_CERTIFICATION'
291 CONNECT BY PRIOR process_id = parent_process_id
292 AND organization_id = PRIOR organization_id
293 AND entity_id = PRIOR entity_id
294 ---07.05.2005 npanandi: add entityType, bugfix 4471783
295 and entity_type=prior entity_type
296 ));
297
298 CURSOR get_total_org_certified
299 IS
300 SELECT count(distinct processorg.organization_id)
301 FROM amw_execution_scope processorg
302 WHERE processorg.process_id = p_process_id
303 AND processorg.organization_id IN
304 (SELECT object_id
305 FROM amw_entity_hierarchies
306 START WITH parent_object_id = p_org_id
307 AND object_type = 'ORG'
308 AND entity_id = p_certification_id
309 AND entity_type='BUSIPROC_CERTIFICATION'
310 CONNECT BY parent_object_id = PRIOR object_id
311 AND parent_object_type = PRIOR object_type
312 AND entity_id = PRIOR entity_id
313 AND entity_type = PRIOR entity_type)
314 AND processorg.entity_id=p_certification_id
315 AND processorg.entity_type='BUSIPROC_CERTIFICATION';
316
317 CURSOR get_var_total_org_certified
318 IS
319 SELECT count(1)
320 FROM (SELECT distinct procorg.organization_id, procorg.process_id
321 FROM amw_execution_scope scp,
322 amw_process_organization procorg
323 WHERE scp.process_org_rev_id = procorg.process_org_rev_id
324 AND procorg.standard_variation IN
325 (select process_rev_id
326 from amw_process
327 where process_id = p_process_id)
328 AND scp.organization_id IN
329 (SELECT object_id
330 FROM amw_entity_hierarchies
331 START WITH parent_object_id = p_org_id
332 AND object_type = 'ORG'
333 AND entity_id = p_certification_id
334 AND entity_type='BUSIPROC_CERTIFICATION'
335 CONNECT BY parent_object_id = PRIOR object_id
336 AND parent_object_type = PRIOR object_type
337 AND entity_id = PRIOR entity_id
338 AND entity_type = PRIOR entity_type)
339 AND scp.entity_id=p_certification_id
340 AND scp.entity_type='BUSIPROC_CERTIFICATION');
341
342
343 CURSOR get_org_processes_certified
344 IS
345 SELECT count(distinct pk3_value)
346 FROM amw_opinions_v opinion
347 WHERE opinion.pk2_value = p_certification_id
348 AND opinion.pk1_value = p_process_id
349 AND opinion.opinion_type_code = 'CERTIFICATION'
350 AND opinion.object_name = 'AMW_ORG_PROCESS'
351 AND opinion.pk3_value IN
352 (SELECT object_id
353 FROM amw_entity_hierarchies
354 START WITH parent_object_id = p_org_id
355 AND object_type = 'ORG'
356 AND entity_id = p_certification_id
357 AND entity_type='BUSIPROC_CERTIFICATION'
358 CONNECT BY parent_object_id = PRIOR object_id
359 AND parent_object_type = PRIOR object_type
360 AND entity_id = PRIOR entity_id
361 AND entity_type = PRIOR entity_type)
362 AND exists (select 'Y' from amw_execution_scope scope
363 where scope.entity_type='BUSIPROC_CERTIFICATION'
364 and scope.entity_id=p_certification_id
365 and scope.organization_id=opinion.pk3_value
366 and scope.process_id=opinion.pk1_value);
367
368 CURSOR get_var_org_proc_certified
369 IS
370 SELECT count(1)
371 FROM (SELECT distinct opinion.pk1_value, opinion.pk3_value
372 FROM amw_opinions_v opinion,
373 amw_execution_scope scp,
374 amw_process_organization procorg
375 WHERE opinion.pk2_value = p_certification_id
376 AND opinion.pk1_value = scp.process_id
377 AND opinion.pk3_value = scp.organization_id
378 AND scp.entity_type = 'BUSIPROC_CERTIFICATION'
379 AND scp.entity_id = p_certification_id
380 AND scp.process_org_rev_id = procorg.process_org_rev_id
381 AND procorg.standard_variation in
382 (select process_rev_id
383 from amw_process
384 where process_id = p_process_id)
385 AND scp.organization_id IN
386 (SELECT object_id
387 FROM amw_entity_hierarchies
388 START WITH parent_object_id = p_org_id
389 AND object_type = 'ORG'
390 AND entity_id = p_certification_id
391 AND entity_type='BUSIPROC_CERTIFICATION'
392 CONNECT BY parent_object_id = PRIOR object_id
393 AND parent_object_type = PRIOR object_type
394 AND entity_id = PRIOR entity_id
395 AND entity_type = PRIOR entity_type)
396 AND opinion.opinion_type_code = 'CERTIFICATION'
397 AND opinion.object_name = 'AMW_ORG_PROCESS');
398
399
400 CURSOR get_all_sub_processes
401 IS
402 SELECT count(distinct process_id)
403 FROM amw_execution_scope
404 START WITH parent_process_id = p_process_id
405 AND organization_id = p_org_id
406 AND entity_id = p_certification_id
407 ---07.05.2005 npanandi: add entityType, bugfix 4471783
408 and entity_type='BUSIPROC_CERTIFICATION'
409 CONNECT BY PRIOR process_id = parent_process_id
410 AND organization_id = PRIOR organization_id
411 AND entity_id = PRIOR entity_id
412 ---07.05.2005 npanandi: add entityType, bugfix 4471783
413 and entity_type=prior entity_type;
414
415 CURSOR get_certified_sub_processes
416 IS
417 SELECT count(distinct process_id)
418 FROM amw_execution_scope amw_exec
419 WHERE EXISTS (SELECT opinion.opinion_id
420 FROM amw_opinions_v opinion
421 WHERE opinion.pk1_value = amw_exec.process_id
422 AND opinion.pk3_value = p_org_id
423 AND opinion.pk2_value = p_certification_id
424 AND opinion.opinion_type_code = 'CERTIFICATION'
425 AND opinion.object_name = 'AMW_ORG_PROCESS'
426 )
427 START WITH parent_process_id = p_process_id
428 AND organization_id = p_org_id
429 AND entity_id = p_certification_id
430 ---07.05.2005 npanandi: add entityType, bugfix 4471783
431 and entity_type='BUSIPROC_CERTIFICATION'
432 CONNECT BY PRIOR process_id = parent_process_id
433 AND organization_id = PRIOR organization_id
434 AND entity_id = PRIOR entity_id
435 ---07.05.2005 npanandi: add entityType, bugfix 4471783
436 and entity_type=prior entity_type;
437
438 CURSOR get_sub_process_cert_issues
439 IS
440 SELECT count(distinct process_id)
441 FROM amw_execution_scope amw_exec
442 WHERE EXISTS (SELECT opinion.opinion_id
443 FROM amw_opinions_v opinion
444 WHERE opinion.pk1_value = amw_exec.process_id
445 AND opinion.pk3_value = p_org_id
446 AND opinion.pk2_value = p_certification_id
447 AND opinion.opinion_type_code = 'CERTIFICATION'
448 AND opinion.object_name = 'AMW_ORG_PROCESS'
449 AND opinion.audit_result_code <> 'EFFECTIVE'
450 )
451 START WITH parent_process_id = p_process_id
452 AND organization_id = p_org_id
453 AND entity_id = p_certification_id
454 ---07.05.2005 npanandi: add entityType, bugfix 4471783
455 and entity_type='BUSIPROC_CERTIFICATION'
456 CONNECT BY PRIOR process_id = parent_process_id
457 AND organization_id = PRIOR organization_id
458 AND entity_id = PRIOR entity_id
459 ---07.05.2005 npanandi: add entityType, bugfix 4471783
460 and entity_type=prior entity_type;
461
462
463 CURSOR get_org_process_cert_issues
464 IS
465 SELECT count(distinct pk3_value)
466 FROM amw_opinions_v opinion
467 WHERE opinion.pk2_value = p_certification_id
468 AND opinion.pk1_value = p_process_id
469 AND opinion.opinion_type_code = 'CERTIFICATION'
470 AND opinion.object_name = 'AMW_ORG_PROCESS'
471 AND opinion.audit_result_code <> 'EFFECTIVE'
472 AND opinion.pk3_value IN
473 (SELECT object_id
474 FROM amw_entity_hierarchies
475 START WITH parent_object_id = p_org_id
476 AND object_type = 'ORG'
477 AND entity_id = p_certification_id
478 AND entity_type='BUSIPROC_CERTIFICATION'
479 CONNECT BY parent_object_id = PRIOR object_id
480 AND parent_object_type = PRIOR object_type
481 AND entity_id = PRIOR entity_id
482 AND entity_type = PRIOR entity_type)
483 AND exists (select 'Y' from amw_execution_scope scope
484 where scope.entity_type='BUSIPROC_CERTIFICATION'
485 and scope.entity_id=p_certification_id
486 and scope.organization_id=opinion.pk3_value
487 and scope.process_id=opinion.pk1_value);
488
489 CURSOR get_var_org_proc_cert_issues
490 IS
491 SELECT count(1)
492 FROM (SELECT distinct opinion.pk1_value, opinion.pk3_value
493 FROM amw_opinions_v opinion,
494 amw_execution_scope scp,
495 amw_process_organization procorg
496 WHERE opinion.pk2_value = p_certification_id
497 AND opinion.pk1_value = scp.process_id
498 AND opinion.pk3_value = scp.organization_id
499 AND scp.entity_type = 'BUSIPROC_CERTIFICATION'
500 AND scp.entity_id = p_certification_id
501 AND scp.process_org_rev_id = procorg.process_org_rev_id
502 AND procorg.standard_variation in
503 (select process_rev_id
504 from amw_process
505 where process_id = p_process_id)
506 AND scp.organization_id IN
507 (SELECT object_id
508 FROM amw_entity_hierarchies
509 START WITH parent_object_id = p_org_id
510 AND object_type = 'ORG'
511 AND entity_id = p_certification_id
512 AND entity_type='BUSIPROC_CERTIFICATION'
513 CONNECT BY parent_object_id = PRIOR object_id
514 AND parent_object_type = PRIOR object_type
515 AND entity_id = PRIOR entity_id
516 AND entity_type = PRIOR entity_type)
517 AND opinion.opinion_type_code = 'CERTIFICATION'
518 AND opinion.object_name = 'AMW_ORG_PROCESS'
519 AND opinion.audit_result_code <> 'EFFECTIVE');
520
521 l_certification_opinion_id NUMBER;
522 l_evaluation_opinion_id NUMBER;
523 l_evaluation_opinion_log_id NUMBER;
524
525 l_unmitigated_risks NUMBER;
526 l_evaluated_risks NUMBER;
527 l_total_risks NUMBER;
528
529 l_ineffective_controls NUMBER;
530 l_evaluated_controls NUMBER;
531 l_total_controls NUMBER;
532
533 l_global_process VARCHAR2(1);
534 l_global_org_id NUMBER;
535 l_total_org_process_cert NUMBER;
536 l_org_process_cert NUMBER;
537 l_var_total_org_process_cert NUMBER;
538 l_var_org_process_cert NUMBER;
539
540 l_sub_process_certified NUMBER;
541 l_sub_process_total NUMBER;
542 l_open_findings NUMBER;
543 l_open_issues NUMBER;
544
545 l_org_process_cert_issues NUMBER;
546 l_var_org_proc_cert_issues NUMBER;
547 l_sub_process_cert_issues NUMBER;
548
549 BEGIN
550
551 OPEN get_certification_opinion;
552 FETCH get_certification_opinion into l_certification_opinion_id;
553 CLOSE get_certification_opinion;
554
555 OPEN get_evaluation_opinion;
556 FETCH get_evaluation_opinion into l_evaluation_opinion_id;
557 CLOSE get_evaluation_opinion;
558
559 OPEN get_evaluation_opinion_log;
560 FETCH get_evaluation_opinion_log into l_evaluation_opinion_log_id;
561 CLOSE get_evaluation_opinion_log;
562
563 OPEN get_unmitigated_risks;
564 FETCH get_unmitigated_risks into l_unmitigated_risks;
565 CLOSE get_unmitigated_risks;
566
567 OPEN get_evaluated_risks;
568 FETCH get_evaluated_risks into l_evaluated_risks;
569 CLOSE get_evaluated_risks;
570
571 OPEN get_total_risks;
572 FETCH get_total_risks into l_total_risks;
573 CLOSE get_total_risks;
574
575 OPEN get_ineffective_controls;
576 FETCH get_ineffective_controls into l_ineffective_controls;
577 CLOSE get_ineffective_controls;
578
579 OPEN get_evaluated_controls;
580 FETCH get_evaluated_controls into l_evaluated_controls;
581 CLOSE get_evaluated_controls;
582
583 OPEN get_total_controls;
584 FETCH get_total_controls into l_total_controls;
585 CLOSE get_total_controls;
586
587 /*
588 l_global_org_id := fnd_profile.value('AMW_GLOBAL_ORG_ID');
589
590 IF (l_global_org_id IS NOT NULL) AND (l_global_org_id = p_org_id)
591 THEN
592 l_global_process := 'Y';
593
594 OPEN get_total_org_certified(l_global_org_id);
595 FETCH get_total_org_certified into l_total_org_process_cert;
596 CLOSE get_total_org_certified;
597
598 OPEN get_org_processes_certified(l_global_org_id);
599 FETCH get_org_processes_certified into l_org_process_cert;
600 CLOSE get_org_processes_certified;
601
602 OPEN get_var_total_org_certified;
603 FETCH get_var_total_org_certified
604 into l_var_total_org_process_cert;
605 CLOSE get_var_total_org_certified;
606
607 OPEN get_var_org_proc_certified;
608 FETCH get_var_org_proc_certified
609 into l_var_org_process_cert;
610 CLOSE get_var_org_proc_certified;
611
612 l_total_org_process_cert := l_total_org_process_cert +
613 l_var_total_org_process_cert;
614 l_org_process_cert := l_org_process_cert +
615 l_var_org_process_cert;
616 END IF;
617 */
618
619 OPEN get_certified_sub_processes;
620 FETCH get_certified_sub_processes into l_sub_process_certified;
621 CLOSE get_certified_sub_processes;
622
623 OPEN get_all_sub_processes;
624 FETCH get_all_sub_processes into l_sub_process_total;
625 CLOSE get_all_sub_processes;
626
627 OPEN get_sub_process_cert_issues;
628 FETCH get_sub_process_cert_issues into l_sub_process_cert_issues;
629 CLOSE get_sub_process_cert_issues;
630
631 ---04.28.2005 npanandi: commenting below uniform setting to 'Y'
632 ---and setting to 'Y' only if p_org is the profile option Global Org
633 ---l_global_process := 'Y';
634 l_global_org_id := fnd_profile.value('AMW_GLOBAL_ORG_ID');
635 IF (l_global_org_id IS NOT NULL) AND (l_global_org_id = p_org_id) then
636 l_global_process := 'Y';
637 end if;
638
639 OPEN get_total_org_certified;
640 FETCH get_total_org_certified into l_total_org_process_cert;
641 CLOSE get_total_org_certified;
642
643 OPEN get_org_processes_certified;
644 FETCH get_org_processes_certified into l_org_process_cert;
645 CLOSE get_org_processes_certified;
646
647 OPEN get_var_total_org_certified;
648 FETCH get_var_total_org_certified into l_var_total_org_process_cert;
649 CLOSE get_var_total_org_certified;
650
651 OPEN get_var_org_proc_certified;
652 FETCH get_var_org_proc_certified into l_var_org_process_cert;
653 CLOSE get_var_org_proc_certified;
654
655
656 OPEN get_org_process_cert_issues;
657 FETCH get_org_process_cert_issues into l_org_process_cert_issues;
658 CLOSE get_org_process_cert_issues;
659
660 OPEN get_var_org_proc_cert_issues;
661 FETCH get_var_org_proc_cert_issues into l_var_org_proc_cert_issues;
662 CLOSE get_var_org_proc_cert_issues;
663
664 l_total_org_process_cert := l_total_org_process_cert +
665 l_var_total_org_process_cert;
666 l_org_process_cert := l_org_process_cert +
667 l_var_org_process_cert;
668 l_org_process_cert_issues := l_org_process_cert_issues +
669 l_var_org_proc_cert_issues;
670
671 l_open_findings := amw_findings_pkg.calculate_open_findings('AMW_PROJ_FINDING',
672 'PROJ_ORG_PROC',
673 p_process_id,
674 'PROJ_ORG',
675 p_org_id,
676 null, null,
677 null, null,
678 null, null);
679
680 l_open_issues := amw_findings_pkg.calculate_open_findings('AMW_PROC_CERT_ISSUES',
681 'PROCESS',
682 p_process_id,
683 'ORGANIZATION',
684 p_org_id,
685 'CERTIFICATION',
686 p_certification_id,
687 null, null,
688 null, null);
689
690 UPDATE amw_proc_cert_eval_sum
691 SET certification_opinion_id = l_certification_opinion_id,
692 evaluation_opinion_id = l_evaluation_opinion_id,
693 evaluation_opinion_log_id= l_evaluation_opinion_log_id,
694 unmitigated_risks = l_unmitigated_risks,
695 evaluated_risks = l_evaluated_risks,
696 total_risks = l_total_risks,
697 ineffective_controls = l_ineffective_controls,
698 evaluated_controls = l_evaluated_controls,
699 total_controls = l_total_controls,
700 total_org_process_cert = l_total_org_process_cert,
701 global_process = l_global_process,
702 org_process_cert = l_org_process_cert,
703 sub_process_cert = l_sub_process_certified,
704 org_process_cert_issues = l_org_process_cert_issues,
705 sub_process_cert_issues = l_sub_process_cert_issues,
706 total_sub_process_cert = l_sub_process_total,
707 open_findings = l_open_findings,
708 open_issues = l_open_issues,
709 last_update_date = SYSDATE,
710 last_updated_by = G_USER_ID,
711 last_update_login = G_LOGIN_ID,
712 UNMITIGATED_RISKS_PRCNT =
713 decode(l_total_risks, 0, 0, round(l_unmitigated_risks/l_total_risks*100)),
714 INEFFECTIVE_CONTROLS_PRCNT =
715 decode(l_total_controls, 0, 0, round(l_ineffective_controls/l_total_controls*100))
716 WHERE process_id = p_process_id
717 AND certification_id = p_certification_id
718 AND organization_id = p_org_id;
719
720 IF (SQL%NOTFOUND)
721 THEN
722
723 INSERT INTO amw_proc_cert_eval_sum(certification_opinion_id,
724 evaluation_opinion_id,
725 evaluation_opinion_log_id,
726 unmitigated_risks,
727 evaluated_risks,
728 total_risks,
729 ineffective_controls,
730 evaluated_controls,
731 total_controls,
732 total_org_process_cert,
733 global_process,
734 org_process_cert,
735 sub_process_cert,
736 org_process_cert_issues,
737 sub_process_cert_issues,
738 total_sub_process_cert,
739 open_findings,
740 open_issues,
741 certification_id,
742 process_id,
743 organization_id,
744 created_by,
745 creation_date,
746 last_updated_by,
747 last_update_date,
748 last_update_login,
749 UNMITIGATED_RISKS_PRCNT,
750 INEFFECTIVE_CONTROLS_PRCNT)
751 VALUES (l_certification_opinion_id,
752 l_evaluation_opinion_id,
753 l_evaluation_opinion_log_id,
754 l_unmitigated_risks,
755 l_evaluated_risks,
756 l_total_risks,
757 l_ineffective_controls,
758 l_evaluated_controls,
759 l_total_controls,
760 l_total_org_process_cert,
761 l_global_process,
762 l_org_process_cert,
763 l_sub_process_certified,
764 l_org_process_cert_issues,
765 l_sub_process_cert_issues,
766 l_sub_process_total,
767 l_open_findings,
768 l_open_issues,
769 p_certification_id,
770 p_process_id,
771 p_org_id,
772 G_USER_ID,
773 sysdate,
774 G_USER_ID,
775 sysdate,
776 G_LOGIN_ID,
777 decode(l_total_risks, 0, 0, round(l_unmitigated_risks/l_total_risks*100)),
778 decode(l_total_controls, 0, 0, round(l_ineffective_controls/l_total_controls*100)));
779
780 END IF;
781
782 EXCEPTION
783 WHEN NO_DATA_FOUND
784 THEN
785 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in update_summary_table'
786 || SUBSTR (SQLERRM, 1, 100), 1, 200));
787
788 WHEN OTHERS
789 THEN
790 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in update_summary_table'
791 || SUBSTR (SQLERRM, 1, 100), 1, 200));
792
793
794 END update_summary_table;
795
796 -- ===========================================================================
797 -- Procedure : Populate_Summary
798 -- Description : Procedure will be called from concurrent program
799 -- ===========================================================================
800 PROCEDURE populate_summary
801 (p_certification_id IN NUMBER
802 )
803 IS
804
805 -- select all processes in scope for the certification
806 CURSOR get_all_processes
807 IS
808 SELECT DISTINCT process_id, organization_id
809 FROM amw_execution_scope
810 WHERE entity_type = 'BUSIPROC_CERTIFICATION'
811 AND entity_id = p_certification_id
812 AND process_id IS NOT NULL;
813
814 CURSOR get_specific_records
815 IS
816 SELECT last_update_date
817 FROM amw_proc_cert_eval_sum
818 WHERE certification_id = p_certification_id
819 FOR UPDATE NOWAIT;
820
821
822 proc_rec get_all_processes%rowtype;
823
824 BEGIN
825 fnd_file.put_line (fnd_file.LOG, 'p_certification_id='||to_char(p_certification_id));
826 fnd_file.put_line(fnd_file.LOG, 'start timestamp :'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
827 OPEN get_specific_records;
828 CLOSE get_specific_records;
829
830 FOR proc_rec IN get_all_processes LOOP
831
832 update_summary_table(proc_rec.process_id, proc_rec.organization_id, p_certification_id);
833
834 END LOOP;
835
836 populate_assoc_opinion(p_certification_id);
837 fnd_file.put_line(fnd_file.LOG, 'end timestamp :'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
838 COMMIT;
839 EXCEPTION
840 WHEN NO_DATA_FOUND
841 THEN
842 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Populate_Summary'
843 || SUBSTR (SQLERRM, 1, 100), 1, 200));
844
845 WHEN OTHERS
846 THEN
847 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Populate_Summary'
848 || SUBSTR (SQLERRM, 1, 100), 1, 200));
849
850 END populate_summary;
851
852 -- ===========================================================================
853 -- Procedure : POPULATE_ALL_CERT_SUMMARY
854 -- Description : Procedure will be called from concurrent program
855 -- ===========================================================================
856 PROCEDURE POPULATE_ALL_CERT_SUMMARY
857 (x_errbuf OUT NOCOPY VARCHAR2,
858 x_retcode OUT NOCOPY NUMBER,
859 p_certification_id IN NUMBER
860 )
861 IS
862
863 -- select all processes in scope for the certification
864 CURSOR get_all_processes is
865 SELECT distinct CERTIFICATION_ID
866 FROM AMW_CERTIFICATION_VL
867 WHERE OBJECT_TYPE = 'PROCESS'
868 AND certification_status in ('ACTIVE','DRAFT');
869
870 proc_rec get_all_processes%rowtype;
871
872 BEGIN
873 fnd_file.put_line (fnd_file.LOG, 'Certification_Id:'||p_certification_id);
874
875 IF p_certification_id IS NOT NULL
876 THEN
877 Populate_Summary(p_certification_id);
878 ELSE
879 FOR proc_rec in get_all_processes LOOP
880 Populate_Summary(proc_rec.CERTIFICATION_ID);
881 END LOOP;
882 END IF;
883
884 EXCEPTION
885 WHEN NO_DATA_FOUND
886 THEN
887 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Populate_All_Cert_Summary'
888 || SUBSTR (SQLERRM, 1, 100), 1, 200));
889
890 WHEN OTHERS
891 THEN
892 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Populate_All_Cert_Summary'
893 || SUBSTR (SQLERRM, 1, 100), 1, 200));
894
895 END POPULATE_ALL_CERT_SUMMARY;
896
897 -- ===========================================================================
898 -- Procedure : POPULATE_CERT_GENERAL_SUM
899 -- Description : Procedure will be called from concurrent program
900 -- ===========================================================================
901 PROCEDURE POPULATE_CERT_GENERAL_SUM
902 (p_certification_id IN NUMBER,
903 p_start_date IN DATE
904 )
905 IS
906
907 ---04.04.2005 npanandi: changed below query for bug 4253074
908 CURSOR new_risks_added
909 IS
910 /*
911 SELECT count(1)
912 FROM AMW_RISK_ASSOCIATIONS
913 WHERE creation_date >= p_start_date
914 AND object_type = 'PROCESS_ORG'
915 AND pk1 in (SELECT apo.process_organization_id
916 FROM AMW_CURR_APPROVED_REV_ORG_V apo, amw_execution_scope aes
917 WHERE apo.process_id = aes.process_id
918 AND apo.organization_id = aes.organization_id
919 AND aes.entity_type = 'BUSIPROC_CERTIFICATION'
920 AND aes.entity_id = p_certification_id);*/
921 SELECT count(1)
922 from (select distinct ara.risk_id, ara.pk1, ara.pk2
923 FROM AMW_RISK_ASSOCIATIONS ara,
924 amw_execution_scope aes,
925 ---05.24.2005 npanandi: added AmwCertificationB, AmwGlPeriodsV
926 ---in the joins below
927 amw_certification_b acb,
928 amw_gl_periods_v period,
929 amw_audit_units_v aauv --03.28.2007 npanandi: bug 5764832 fix -- added join to
930 --AmwAuditUnitsV to make count consistent with the
931 --'New Risks' added page results
932 ---05.24.2005 npanandi: changed below reference to creationDate
933 ---and added references to ApprovalDate and DeletionApprovalDate resp
934 ---WHERE ara.creation_date >= p_start_date
935 WHERE acb.certification_period_name = period.period_name
936 and acb.certification_period_set_name = period.period_set_name
937 and acb.certification_id = aes.entity_id
938 ----and ara.APPROVAL_DATE <= p_start_date
939 ----05.24.2005 npanandi: p_start_date is the same as period.start_date here
940 and ara.APPROVAL_DATE between period.START_DATE and period.END_DATE
941 and nvl(ara.deletion_approval_date,period.END_DATE) >= period.END_DATE
942 --03.28.2007 npanandi: bug 5764832 fix -- added join to
943 --AmwAuditUnitsV to make count consistent with the 'New Risks'
944 --added page results
945 and aauv.organization_id = ara.pk1
946 AND ara.object_type = 'PROCESS_ORG'
947 and ara.pk1=aes.ORGANIZATION_ID
948 and ara.pk2=aes.PROCESS_ID
949 and aes.ENTITY_TYPE='BUSIPROC_CERTIFICATION'
950 and aes.ENTITY_ID=p_certification_id);
951
952 ---04.04.2005 npanandi: changed below query for bug 4253074
953 CURSOR new_controls_added
954 IS
955 SELECT count(1)
956 FROM (SELECT distinct aca.control_id, aes.organization_id
957 FROM AMW_CONTROL_ASSOCIATIONS aca,
958 AMW_RISK_ASSOCIATIONS ara,
959 AMW_EXECUTION_SCOPE aes,
960 ---05.24.2005 npanandi: added AmwCertificationB, AmwGlPeriodsV
961 ---in the joins below
962 amw_certification_b acb,
963 amw_gl_periods_v period,
964 amw_audit_units_v aauv --03.28.2007 npanandi: bug 5764832 fix -- added join to
965 --AmwAuditUnitsV to make count consistent with the
966 --'New Controls' added page results
967 ---05.24.2005 npanandi: changed below reference to creationDate
968 ---and added references to ApprovalDate and DeletionApprovalDate resp
969 ---WHERE aca.creation_date >= p_start_date
970 WHERE acb.certification_period_name = period.period_name
971 and acb.certification_period_set_name = period.period_set_name
972 and acb.certification_id = aes.entity_id
973 and aca.approval_date between period.START_DATE and period.END_DATE ---<= p_start_date
974 and nvl(aca.deletion_approval_date,period.END_DATE) >= period.END_DATE ---p_start_date
975 AND aca.object_type = 'RISK_ORG'
976 --03.28.2007 npanandi: bug 5764832 fix -- added join to
977 --AmwAuditUnitsV to make count consistent with the 'New Controls'
978 --added page results
979 and aca.pk1 = aauv.organization_id
980 AND aca.pk1 = aes.ORGANIZATION_ID
981 and aca.pk2 = aes.PROCESS_ID
982 and aca.pk3 = ara.RISK_ID
983 AND ara.object_type = 'PROCESS_ORG'
984 AND ara.pk1 = aca.pk1
985 AND ara.pk2 = aca.pk2
986 AND aes.entity_type = 'BUSIPROC_CERTIFICATION'
987 AND aes.entity_id = p_certification_id);
988
989 CURSOR global_proc_not_certified IS
990 SELECT count(1)
991 FROM (SELECT distinct aes.organization_id, aes.process_id
992 FROM AMW_EXECUTION_SCOPE aes
993 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
994 AND aes.entity_id = p_certification_id
995 AND aes.level_id > 3
996 AND aes.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
997 AND not exists (SELECT 'Y'
998 FROM AMW_OPINIONS_V aov
999 WHERE aov.object_name = 'AMW_ORG_PROCESS'
1000 AND aov.opinion_type_code = 'CERTIFICATION'
1001 AND aov.pk3_value = aes.organization_id
1002 AND aov.pk2_value = p_certification_id
1003 AND aov.pk1_value = aes.process_id));
1004 --modified by dliao on 10.06.05, add distinct on select statement.
1005 --suggest to use amw_proc_cert_eval_sum instead of amw_execution_scope because it can sync with ProcCertIssuesVO definition.
1006
1007 CURSOR global_proc_with_issue IS
1008 SELECT count(1)
1009 FROM (SELECT distinct aes.organization_id, aes.process_id
1010 FROM AMW_EXECUTION_SCOPE aes, AMW_OPINIONS_V aov
1011 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1012 AND aes.entity_id = p_certification_id
1013 AND aes.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
1014 AND aes.level_id > 3
1015 AND aov.object_name = 'AMW_ORG_PROCESS'
1016 AND aov.opinion_type_code = 'CERTIFICATION'
1017 AND aov.pk3_value = aes.organization_id
1018 AND aov.pk2_value = p_certification_id
1019 AND aov.pk1_value = aes.process_id
1020 AND aov.audit_result_code <> 'EFFECTIVE');
1021
1022 CURSOR local_proc_not_certified IS
1023 SELECT count(1)
1024 FROM (SELECT distinct aes.organization_id, aes.process_id
1025 FROM AMW_EXECUTION_SCOPE aes
1026 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1027 AND aes.entity_id = p_certification_id
1028 AND aes.level_id > 3
1029 AND aes.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
1030 AND not exists (SELECT 'Y'
1031 FROM AMW_OPINIONS_V aov
1032 WHERE aov.object_name = 'AMW_ORG_PROCESS'
1033 AND aov.opinion_type_code = 'CERTIFICATION'
1034 AND aov.pk3_value = aes.organization_id
1035 AND aov.pk2_value = p_certification_id
1036 AND aov.pk1_value = aes.process_id));
1037
1038 CURSOR local_proc_with_issue IS
1039 SELECT count(1)
1040 FROM (SELECT aes.organization_id, aes.process_id
1041 FROM AMW_EXECUTION_SCOPE aes, AMW_OPINIONS_V aov
1042 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1043 AND aes.entity_id = p_certification_id
1044 AND aes.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
1045 AND aes.level_id > 3
1046 AND aov.object_name = 'AMW_ORG_PROCESS'
1047 AND aov.opinion_type_code = 'CERTIFICATION'
1048 AND aov.pk3_value = aes.organization_id
1049 AND aov.pk2_value = p_certification_id
1050 AND aov.pk1_value = aes.process_id
1051 AND aov.audit_result_code <> 'EFFECTIVE');
1052
1053 CURSOR global_proc_with_ineff_ctrl IS
1054 SELECT count(distinct aes.process_id)
1055 FROM amw_execution_scope aes,amw_opinions_v aov
1056 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1057 AND aes.entity_id = p_certification_id
1058 AND aes.level_id > 3
1059 AND aes.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
1060 AND aov.object_name = 'AMW_ORG_PROCESS'
1061 AND aov.opinion_type_code = 'EVALUATION'
1062 AND aov.pk3_value = aes.organization_id
1063 AND aov.pk1_value = aes.process_id
1064 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
1065 FROM amw_opinions_v aov2
1066 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
1067 AND aov2.pk3_value = aov.pk3_value
1068 AND aov2.pk1_value = aov.pk1_value)
1069 AND aov.audit_result_code <> 'EFFECTIVE';
1070
1071 CURSOR local_proc_with_ineff_ctrl IS
1072 SELECT count(1)
1073 FROM (SELECT distinct aes.organization_id, aes.process_id
1074 FROM amw_execution_scope aes,amw_opinions_v aov
1075 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1076 AND aes.entity_id = p_certification_id
1077 AND aes.level_id > 3
1078 AND aes.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
1079 AND aov.object_name = 'AMW_ORG_PROCESS'
1080 AND aov.opinion_type_code = 'EVALUATION'
1081 AND aov.pk3_value = aes.organization_id
1082 AND aov.pk1_value = aes.process_id
1083 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
1084 FROM amw_opinions_v aov2
1085 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
1086 AND aov2.pk3_value = aov.pk3_value
1087 AND aov2.pk1_value = aov.pk1_value)
1088 AND aov.audit_result_code <> 'EFFECTIVE');
1089
1090
1091 ---04.04.05 npanandi: changed the query below as per
1092 ---AMw.D datamodel
1093 /** CURSOR unmitigated_risks IS
1094 SELECT count(1)
1095 FROM (SELECT distinct aes.organization_id, aes.process_id, ara.risk_id
1096 FROM AMW_EXECUTION_SCOPE aes, AMW_CURR_APPROVED_REV_ORG_V apo,
1097 AMW_RISK_ASSOCIATIONS ara,
1098 AMW_OPINIONS_V aov
1099 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1100 AND aes.entity_id = p_certification_id
1101 AND aes.level_id > 3
1102 AND apo.organization_id = aes.organization_id
1103 AND apo.process_id = aes.process_id
1104 AND ara.object_type = 'PROCESS_ORG'
1105 AND ara.pk1 = apo.process_organization_id
1106 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
1107 AND aov.opinion_type_code = 'EVALUATION'
1108 AND aov.pk3_value = aes.organization_id
1109 AND aov.pk4_value = aes.process_id
1110 AND aov.pk1_value = ara.risk_id
1111 AND aov.authored_date =
1112 (select max(aov2.authored_date)
1113 from AMW_OPINIONS_V aov2
1114 where aov2.object_opinion_type_id = aov.object_opinion_type_id
1115 and aov2.pk4_value = aov.pk4_value
1116 and aov2.pk3_value = aov.pk3_value
1117 and aov2.pk1_value = aov.pk1_value)
1118 AND aov.audit_result_code <> 'EFFECTIVE'); **/
1119 CURSOR unmitigated_risks IS
1120 SELECT count(1)
1121 FROM (SELECT distinct aes.organization_id, aes.process_id, ara.risk_id
1122 FROM AMW_EXECUTION_SCOPE aes,
1123 AMW_RISK_ASSOCIATIONS ara,
1124 AMW_OPINIONS_V aov, amw_audit_units_v aauv /* 03.19.2007 npanandi: bug 5862215 -- only consider those Orgs that are valid*/
1125 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1126 AND aes.entity_id = p_certification_id
1127 AND aes.level_id > 3
1128 ---AND apo.organization_id = aes.organization_id
1129 ---AND apo.process_id = aes.process_id
1130 AND ara.object_type = 'PROCESS_ORG'
1131 AND ara.pk1 = aes.ORGANIZATION_ID
1132 and ara.pk2 = aes.PROCESS_ID
1133 /*03.19.2007 npanandi: bug 5862215 - consider those Orgs only that are valid*/
1134 and ara.pk1 = aauv.organization_id
1135 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
1136 AND aov.opinion_type_code = 'EVALUATION'
1137 AND aov.pk3_value = aes.organization_id
1138 AND aov.pk4_value = aes.process_id
1139 AND aov.pk1_value = ara.risk_id
1140 AND aov.authored_date = (select max(aov2.authored_date)
1141 from AMW_OPINIONS_V aov2
1142 where aov2.object_opinion_type_id = aov.object_opinion_type_id
1143 and aov2.pk4_value = aov.pk4_value
1144 and aov2.pk3_value = aov.pk3_value
1145 and aov2.pk1_value = aov.pk1_value)
1146 AND aov.audit_result_code <> 'EFFECTIVE');
1147
1148 ---04.04.05 npanandi: changed the query below as per
1149 ---AMw.D datamodel
1150 /* CURSOR ineffective_controls IS
1151 SELECT count(1)
1152 FROM (SELECT distinct aes.organization_id, aca.control_id
1153 FROM AMW_EXECUTION_SCOPE aes, AMW_CURR_APPROVED_REV_ORG_V apo,
1154 AMW_RISK_ASSOCIATIONS ara, AMW_CONTROL_ASSOCIATIONS aca,
1155 AMW_OPINIONS_V aov
1156 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1157 AND aes.entity_id = p_certification_id
1158 AND aes.level_id > 3
1159 AND apo.organization_id = aes.organization_id
1160 AND apo.process_id = aes.process_id
1161 AND ara.object_type = 'PROCESS_ORG'
1162 AND ara.pk1 = apo.process_organization_id
1163 AND aca.object_type = 'RISK_ORG'
1164 AND aca.pk1 = ara.risk_association_id
1165 AND aov.object_name = 'AMW_ORG_CONTROL'
1166 AND aov.opinion_type_code = 'EVALUATION'
1167 AND aov.pk3_value = aes.organization_id
1168 AND aov.pk1_value = aca.control_id
1169 AND aov.authored_date =
1170 (select max(aov2.authored_date)
1171 from AMW_OPINIONS_V aov2
1172 where aov2.object_opinion_type_id = aov.object_opinion_type_id
1173 and aov2.pk3_value = aov.pk3_value
1174 and aov2.pk1_value = aov.pk1_value)
1175 AND aov.audit_result_code <> 'EFFECTIVE'); */
1176 CURSOR ineffective_controls IS
1177 SELECT count(1)
1178 FROM (SELECT distinct aes.organization_id, aca.control_id, aes.process_id /** 01/31/2007 npanandi: added processId in distinct **/
1179 FROM AMW_EXECUTION_SCOPE aes, ---AMW_CURR_APPROVED_REV_ORG_V apo,
1180 AMW_RISK_ASSOCIATIONS ara, AMW_CONTROL_ASSOCIATIONS aca,
1181 AMW_OPINIONS_V aov, amw_audit_units_v aauv /* 03.19.2007 npanandi: bug 5862215: consider only those Orgs that are valid*/
1182 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1183 AND aes.entity_id = p_certification_id
1184 AND aes.level_id > 3
1185 AND ara.object_type = 'PROCESS_ORG'
1186 AND ara.pk1 = aes.organization_id
1187 AND ara.pk2 = aes.process_id
1188 /* 03.19.2007 npanandi: bug 5862215: consider only those Orgs that are valid*/
1189 and aauv.organization_id = aes.organization_id
1190 AND aca.object_type = 'RISK_ORG'
1191 AND aca.pk1 = ara.pk1
1192 AND aca.pk2 = ara.pk2
1193 AND aca.pk3 = ara.risk_id
1194 AND aov.object_name = 'AMW_ORG_CONTROL'
1195 AND aov.opinion_type_code = 'EVALUATION'
1196 AND aov.pk3_value = aes.organization_id
1197 AND aov.pk1_value = aca.control_id
1198 AND aov.authored_date = (select max(aov2.authored_date)
1199 from AMW_OPINIONS_V aov2
1200 where aov2.object_opinion_type_id = aov.object_opinion_type_id
1201 and aov2.pk3_value = aov.pk3_value
1202 and aov2.pk1_value = aov.pk1_value)
1203 AND aov.audit_result_code <> 'EFFECTIVE');
1204
1205 CURSOR orgs_pending_in_scope IS
1206 SELECT count(distinct aes.organization_id)
1207 FROM AMW_EXECUTION_SCOPE aes
1208 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1209 AND aes.entity_id = p_certification_id
1210 AND aes.level_id = 4
1211 AND not exists (SELECT 'Y'
1212 FROM AMW_OPINIONS_V aov
1213 WHERE aov.object_name = 'AMW_ORG_PROCESS'
1214 AND aov.opinion_type_code = 'CERTIFICATION'
1215 AND aov.pk3_value = aes.organization_id
1216 AND aov.pk2_value = p_certification_id
1217 AND aov.pk1_value = aes.process_id);
1218
1219 CURSOR orgs_in_scope IS
1220 SELECT count(distinct aes.organization_id)
1221 FROM AMW_EXECUTION_SCOPE aes
1222 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1223 AND aes.entity_id = p_certification_id
1224 AND aes.level_id = 3;
1225
1226 CURSOR orgs_pending_cert IS
1227 SELECT count(distinct aes.organization_id)
1228 FROM AMW_EXECUTION_SCOPE aes
1229 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1230 AND aes.entity_id = p_certification_id
1231 AND aes.level_id = 3
1232 AND not exists (SELECT 'Y'
1233 FROM AMW_OPINIONS_V aov
1234 WHERE aov.object_name = 'AMW_ORGANIZATION'
1235 AND aov.opinion_type_code = 'CERTIFICATION'
1236 AND aov.pk1_value = aes.organization_id
1237 AND aov.pk2_value = p_certification_id);
1238
1239 l_new_risks_added NUMBER;
1240 l_new_controls_added NUMBER;
1241 l_global_proc_not_certified NUMBER;
1242 l_global_proc_with_issue NUMBER;
1243 l_local_proc_not_certified NUMBER;
1244 l_local_proc_with_issue NUMBER;
1245 l_global_proc_with_ineff_ctrl NUMBER;
1246 l_local_proc_with_ineff_ctrl NUMBER;
1247 l_unmitigated_risks NUMBER;
1248 l_ineffective_controls NUMBER;
1249 l_orgs_in_scope NUMBER;
1250 l_orgs_pending_in_scope NUMBER;
1251 l_orgs_pending_cert NUMBER;
1252
1253 BEGIN
1254
1255 fnd_file.put_line (fnd_file.LOG, 'p_certification_id='||to_char(p_certification_id));
1256
1257 fnd_file.put_line(fnd_file.LOG, 'before new_risks_added :'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1258 OPEN new_risks_added;
1259 FETCH new_risks_added INTO l_new_risks_added;
1260 CLOSE new_risks_added;
1261
1262 fnd_file.put_line(fnd_file.LOG, 'before new_controls_added :'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1263 OPEN new_controls_added;
1264 FETCH new_controls_added INTO l_new_controls_added;
1265 CLOSE new_controls_added;
1266
1267 fnd_file.put_line(fnd_file.LOG, 'before global_proc_not_certified:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1268 OPEN global_proc_not_certified;
1269 FETCH global_proc_not_certified INTO l_global_proc_not_certified;
1270 CLOSE global_proc_not_certified;
1271
1272 fnd_file.put_line(fnd_file.LOG, 'before global_proc_with_issue:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1273 OPEN global_proc_with_issue;
1274 FETCH global_proc_with_issue INTO l_global_proc_with_issue;
1275 CLOSE global_proc_with_issue;
1276
1277 fnd_file.put_line(fnd_file.LOG, 'before local_proc_not_certified:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1278 OPEN local_proc_not_certified;
1279 FETCH local_proc_not_certified INTO l_local_proc_not_certified;
1280 CLOSE local_proc_not_certified;
1281
1282 fnd_file.put_line(fnd_file.LOG, 'before local_proc_with_issue:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1283 OPEN local_proc_with_issue;
1284 FETCH local_proc_with_issue INTO l_local_proc_with_issue;
1285 CLOSE local_proc_with_issue;
1286
1287 fnd_file.put_line(fnd_file.LOG, 'before global_proc_with_ineff_ctrl:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1288 OPEN global_proc_with_ineff_ctrl;
1289 FETCH global_proc_with_ineff_ctrl INTO l_global_proc_with_ineff_ctrl;
1290 CLOSE global_proc_with_ineff_ctrl;
1291
1292 fnd_file.put_line(fnd_file.LOG, 'before local_proc_with_ineff_ctrl:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1293 OPEN local_proc_with_ineff_ctrl;
1294 FETCH local_proc_with_ineff_ctrl INTO l_local_proc_with_ineff_ctrl;
1295 CLOSE local_proc_with_ineff_ctrl;
1296
1297 fnd_file.put_line(fnd_file.LOG, 'before unmitigated_risks:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1298 OPEN unmitigated_risks;
1299 FETCH unmitigated_risks INTO l_unmitigated_risks;
1300 CLOSE unmitigated_risks;
1301
1302 fnd_file.put_line(fnd_file.LOG, 'before ineffective_controls:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1303 OPEN ineffective_controls;
1304 FETCH ineffective_controls INTO l_ineffective_controls;
1305 CLOSE ineffective_controls;
1306
1307 fnd_file.put_line(fnd_file.LOG, 'before orgs_pending_in_scop:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1308 OPEN orgs_pending_in_scope;
1309 FETCH orgs_pending_in_scope INTO l_orgs_pending_in_scope;
1310 CLOSE orgs_pending_in_scope;
1311
1312 fnd_file.put_line(fnd_file.LOG, 'before orgs_in_scope:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1313 OPEN orgs_in_scope;
1314 FETCH orgs_in_scope INTO l_orgs_in_scope;
1315 CLOSE orgs_in_scope;
1316 fnd_file.put_line(fnd_file.LOG, 'after orgs_in_scope:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1317
1318
1319 OPEN orgs_pending_cert;
1320 FETCH orgs_pending_cert INTO l_orgs_pending_cert;
1321 CLOSE orgs_pending_cert;
1322 fnd_file.put_line(fnd_file.LOG, 'after orgs_pending_cert:'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1323
1324 UPDATE AMW_CERT_DASHBOARD_SUM
1325 SET NEW_RISKS_ADDED = l_new_risks_added,
1326 NEW_CONTROLS_ADDED = l_new_controls_added,
1327 PROCESSES_NOT_CERT = l_global_proc_not_certified,
1328 PROCESSES_CERT_ISSUES = l_global_proc_with_issue,
1329 ORG_PROCESS_NOT_CERT = l_local_proc_not_certified,
1330 ORG_PROCESS_CERT_ISSUES = l_local_proc_with_issue,
1331 PROC_INEFF_CONTROL = l_global_proc_with_ineff_ctrl,
1332 ORG_PROC_INEFF_CONTROL = l_local_proc_with_ineff_ctrl,
1333 UNMITIGATED_RISKS = l_unmitigated_risks,
1334 INEFFECTIVE_CONTROLS = l_ineffective_controls,
1335 ORGS_IN_SCOPE = l_orgs_in_scope,
1336 ORGS_PENDING_IN_SCOPE = l_orgs_pending_in_scope,
1337 PERIOD_START_DATE = p_start_date,
1338 LAST_UPDATE_DATE = sysdate,
1339 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1340 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
1341 ORGS_PENDING_CERTIFICATION = l_orgs_pending_cert
1342 WHERE certification_id = p_certification_id;
1343
1344 IF (SQL%NOTFOUND) THEN
1345 INSERT INTO AMW_CERT_DASHBOARD_SUM (
1346 CERTIFICATION_ID,
1347 NEW_RISKS_ADDED,
1348 NEW_CONTROLS_ADDED,
1349 PROCESSES_NOT_CERT,
1350 PROCESSES_CERT_ISSUES,
1351 ORG_PROCESS_NOT_CERT,
1352 ORG_PROCESS_CERT_ISSUES,
1353 PROC_INEFF_CONTROL,
1354 ORG_PROC_INEFF_CONTROL,
1355 UNMITIGATED_RISKS,
1356 INEFFECTIVE_CONTROLS,
1357 ORGS_IN_SCOPE,
1358 ORGS_PENDING_IN_SCOPE,
1359 PERIOD_START_DATE,
1360 CREATED_BY,
1361 CREATION_DATE,
1362 LAST_UPDATE_DATE,
1363 LAST_UPDATED_BY,
1364 LAST_UPDATE_LOGIN,
1365 ORGS_PENDING_CERTIFICATION)
1366 SELECT p_certification_id,
1367 l_new_risks_added,
1368 l_new_controls_added,
1369 l_global_proc_not_certified,
1370 l_global_proc_with_issue,
1371 l_local_proc_not_certified,
1372 l_local_proc_with_issue,
1373 l_global_proc_with_ineff_ctrl,
1374 l_local_proc_with_ineff_ctrl,
1375 l_unmitigated_risks,
1376 l_ineffective_controls,
1377 l_orgs_in_scope,
1378 l_orgs_pending_in_scope,
1379 p_start_date,
1380 FND_GLOBAL.USER_ID,
1381 SYSDATE,
1382 SYSDATE,
1383 FND_GLOBAL.USER_ID,
1384 FND_GLOBAL.USER_ID,
1385 l_orgs_pending_cert
1386 FROM DUAL;
1387 END IF;
1388 commit;
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Populate_All_Cert_DetSummary'
1392 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1393
1394 END POPULATE_CERT_GENERAL_SUM;
1395
1396
1397 -- ===========================================================================
1398 -- Procedure : Populate_All_Cert_General_Sum
1399 -- Description : Procedure will be called from concurrent program
1400 -- ===========================================================================
1401 PROCEDURE POPULATE_ALL_CERT_GENERAL_SUM
1402 (errbuf OUT NOCOPY VARCHAR2,
1403 retcode OUT NOCOPY NUMBER,
1404 p_certification_id IN NUMBER
1405 )
1406 IS
1407 -- select all processes in scope for the certification
1408 CURSOR c_cert IS
1409 SELECT cert.CERTIFICATION_ID, period.start_date
1410 FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
1411 WHERE cert.certification_period_name = period.period_name
1412 AND cert.certification_period_set_name = period.period_set_name
1413 AND cert.certification_status in ('ACTIVE','DRAFT')
1414 AND cert.OBJECT_TYPE = 'PROCESS';
1415
1416 CURSOR c_start_date IS
1417 SELECT period.start_date
1418 FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
1419 WHERE cert.certification_period_name = period.period_name
1420 AND cert.certification_period_set_name = period.period_set_name
1421 AND cert.certification_id = p_certification_id;
1422
1423 l_start_date DATE;
1424
1425 BEGIN
1426
1427 fnd_file.put_line (fnd_file.LOG,
1428 'Certification_Id:'||p_certification_id);
1429 IF p_certification_id IS NOT NULL
1430 THEN
1431 OPEN c_start_date;
1432 FETCH c_start_date INTO l_start_date;
1433 CLOSE c_start_date;
1434 Populate_Cert_General_Sum(p_certification_id, l_start_date);
1435 ELSE
1436 FOR cert_rec IN c_cert
1437 LOOP
1438 Populate_Cert_General_Sum(cert_rec.certification_id, cert_rec.start_date);
1439 END LOOP;
1440 END IF;
1441
1442 EXCEPTION
1443 WHEN NO_DATA_FOUND
1444 THEN
1445 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Populate_All_Cert_General_Sum'
1446 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1447 WHEN OTHERS
1448 THEN
1449 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Populate_All_Cert_General_Sum'
1450 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1451 errbuf := SQLERRM;
1452 ---05.24.2005 npanandi: changed retcode to 2 to comply with number datatype
1453 retcode := 2; --FND_API.G_RET_STS_UNEXP_ERROR;
1454 END POPULATE_ALL_CERT_GENERAL_SUM;
1455
1456
1457 /*===========================================================================+
1458 | PROCEDURE |
1459 | Populate_Proc_Cert_Sum |
1460 | |
1461 | DESCRIPTION |
1462 | This procedure is called by the concurrent program. |
1463 | This procedure will call 2 sub requests to synchronize the Business |
1464 | Process Certification Data. |
1465 | |
1466 | |
1467 | ARGUMENTS : IN : |
1468 | p_certification_id : The Certification id
1469 | |
1470 | RETURNS : NONE |
1471 | |
1472 | NOTES |
1473 | |
1474 | |
1475 +===========================================================================*/
1476 PROCEDURE Populate_Proc_Cert_Sum(
1477 errbuf OUT NOCOPY VARCHAR2,
1478 retcode OUT NOCOPY VARCHAR2,
1479 p_certification_id IN NUMBER
1480 )
1481 IS
1482
1483 l_request_id NUMBER;
1484 l_msg VARCHAR2(2000);
1485 l_reqdata VARCHAR2(240);
1486 lx_return_status VARCHAR2(1);
1487 lx_msg_count NUMBER;
1488 lx_msg_data VARCHAR2(2000);
1489
1490 CURSOR get_all_certifications IS
1491 SELECT cert.certification_id
1492 FROM amw_certification_b cert
1493 WHERE cert.certification_status in ('ACTIVE','DRAFT')
1494 AND cert.object_type = 'PROCESS';
1495
1496 BEGIN
1497 fnd_file.put_line (fnd_file.LOG,'Certification Id :' || p_certification_id);
1498
1499 l_reqdata := FND_CONC_GLOBAL.request_data;
1500 IF (l_reqdata is NOT NULL) THEN
1501 return;
1502 END IF;
1503 l_reqdata := 1;
1504
1505 fnd_file.put_line(fnd_file.LOG, 'reset execution scope start...'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1506
1507 --The following section is commented
1508 --In AMW.D and future releases we don't need to rescope everytime the user runs the concurrent program
1509
1510 /*IF p_certification_id IS NOT NULL
1511 THEN
1512 amw_process_cert_scope_pvt.insert_audit_units
1513 (p_certification_id => p_certification_id,
1514 x_return_status => lx_return_status,
1515 x_msg_count => lx_msg_count,
1516 x_msg_data => lx_msg_data);
1517 commit;
1518
1519 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1520 fnd_file.put_line(fnd_file.LOG, 'Problems in insert audit units' || lx_msg_data ||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1521 END IF;
1522
1523 ELSE
1524 FOR each_record IN get_all_certifications
1525 LOOP
1526 amw_process_cert_scope_pvt.insert_audit_units
1527 (p_certification_id => each_record.certification_id,
1528 x_return_status => lx_return_status,
1529 x_msg_count => lx_msg_count,
1530 x_msg_data => lx_msg_data);
1531 commit;
1532
1533 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1534 fnd_file.put_line(fnd_file.LOG, 'Problems in insert audit units' || lx_msg_data || to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1535 END IF;
1536 END LOOP;
1537 END IF;*/
1538
1539
1540 fnd_file.put_line(fnd_file.LOG, 'reset execution scope end...'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1541
1542 /* Sub Request for Dashboard Summary */
1543 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AMW',
1544 'PCDASHSUM',
1545 null,
1546 null,
1547 TRUE,
1548 to_char(p_certification_id));
1549 IF l_request_id = 0 THEN
1550 l_msg:=FND_MESSAGE.GET;
1551 fnd_file.put_line (fnd_file.LOG,l_msg);
1552 ELSE
1553 fnd_file.put_line (fnd_file.LOG,'Submitted Request for Dashboard Summary :' || l_request_id );
1554 END IF;
1555
1556
1557 /* Sub Request for Evaluation Summary for Processes */
1558 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AMW',
1559 'PCEVALSUM',
1560 null,
1561 null,
1562 TRUE,
1563 to_char(p_certification_id));
1564 IF l_request_id = 0 THEN
1565 l_msg:=FND_MESSAGE.GET;
1566 fnd_file.put_line (fnd_file.LOG,l_msg);
1567 ELSE
1568 fnd_file.put_line (fnd_file.LOG,'Submitted Request for Evaluation Summary for processes :' || l_request_id );
1569 END IF;
1570
1571 /* Sub Request for Evaluation Summary for Organizations */
1572 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AMW',
1573 'PCORGSUM',
1574 null,
1575 null,
1576 TRUE,
1577 to_char(p_certification_id));
1578 IF l_request_id = 0 THEN
1579 l_msg:=FND_MESSAGE.GET;
1580 fnd_file.put_line (fnd_file.LOG,l_msg);
1581 ELSE
1582 fnd_file.put_line (fnd_file.LOG,'Submitted Request for Evaluation Summary for organizations :' || l_request_id );
1583 END IF;
1584
1585
1586 FND_CONC_GLOBAL.set_req_globals(conc_status => 'PAUSED',
1587 request_data => l_reqdata);
1588 COMMIT;
1589
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Populate_Proc_Cert_Sum'
1593 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1594 errbuf := SQLERRM;
1595 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
1596
1597 END Populate_Proc_Cert_Sum;
1598
1599
1600 PROCEDURE populate_findings
1601 (p_certification_id IN NUMBER
1602 )
1603 IS
1604
1605 -- select all processes in scope for the certification
1606 CURSOR get_all_processes
1607 IS
1608 SELECT DISTINCT process_id, organization_id
1609 FROM amw_execution_scope
1610 WHERE entity_type = 'BUSIPROC_CERTIFICATION'
1611 AND entity_id = p_certification_id
1612 AND process_id IS NOT NULL;
1613
1614 CURSOR get_all_orgs
1615 IS
1616 SELECT DISTINCT organization_id
1617 FROM amw_execution_scope
1618 WHERE entity_type = 'BUSIPROC_CERTIFICATION'
1619 AND entity_id = p_certification_id
1620 AND level_id = 3;
1621
1622 l_open_findings NUMBER;
1623 l_open_issues NUMBER;
1624
1625
1626 BEGIN
1627 fnd_file.put_line (fnd_file.LOG, 'p_certification_id='||to_char(p_certification_id));
1628 fnd_file.put_line(fnd_file.LOG, 'start timestamp :'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1629
1630
1631 FOR proc_rec IN get_all_processes LOOP
1632 l_open_findings := amw_findings_pkg.calculate_open_findings(
1633 'AMW_PROJ_FINDING',
1634 'PROJ_ORG_PROC',
1635 proc_rec.process_id,
1636 'PROJ_ORG',
1637 proc_rec.organization_id,
1638 null, null,
1639 null, null,
1640 null, null);
1641
1642 l_open_issues := amw_findings_pkg.calculate_open_findings(
1643 'AMW_PROC_CERT_ISSUES',
1644 'PROCESS',
1645 proc_rec.process_id,
1646 'ORGANIZATION',
1647 proc_rec.organization_id,
1648 'CERTIFICATION',
1649 p_certification_id,
1650 null, null,
1651 null, null);
1652
1653 UPDATE amw_proc_cert_eval_sum
1654 SET open_findings = l_open_findings,
1655 open_issues = l_open_issues,
1656 last_update_date = SYSDATE,
1657 last_updated_by = G_USER_ID,
1658 last_update_login = G_LOGIN_ID
1659 WHERE process_id = proc_rec.process_id
1660 AND certification_id = p_certification_id
1661 AND organization_id = proc_rec.organization_id;
1662 END LOOP;
1663
1664 FOR org_rec IN get_all_orgs LOOP
1665 l_open_findings := amw_findings_pkg.calculate_open_findings(
1666 'AMW_PROJ_FINDING',
1667 'PROJ_ORG',
1668 org_rec.organization_id,
1669 null, null,
1670 null, null,
1671 null, null,
1672 null, null);
1673
1674 l_open_issues := amw_findings_pkg.calculate_open_findings(
1675 'AMW_PROC_CERT_ISSUES',
1676 'ORGANIZATION',
1677 org_rec.organization_id,
1678 'CERTIFICATION',
1679 p_certification_id,
1680 null, null,
1681 null, null,
1682 null, null);
1683
1684 UPDATE amw_org_cert_eval_sum
1685 SET open_findings = l_open_findings,
1686 open_issues = l_open_issues,
1687 last_update_date = SYSDATE,
1688 last_updated_by = G_USER_ID,
1689 last_update_login = G_LOGIN_ID
1690 WHERE certification_id = p_certification_id
1691 AND organization_id = org_rec.organization_id;
1692 END LOOP;
1693
1694 fnd_file.put_line(fnd_file.LOG, 'end timestamp :'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
1695 COMMIT;
1696 EXCEPTION
1697 WHEN NO_DATA_FOUND THEN
1698 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Populate_Summary'
1699 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1700 WHEN OTHERS THEN
1701 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Populate_Summary'
1702 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1703
1704 END populate_findings;
1705
1706 PROCEDURE Populate_Proccert_Findings
1707 (x_errbuf OUT NOCOPY VARCHAR2,
1708 x_retcode OUT NOCOPY NUMBER,
1709 p_certification_id IN NUMBER
1710 )
1711 IS
1712
1713 -- select all processes in scope for the certification
1714 CURSOR get_all_processes is
1715 SELECT distinct CERTIFICATION_ID
1716 FROM AMW_CERTIFICATION_VL
1717 WHERE OBJECT_TYPE = 'PROCESS'
1718 AND certification_status in ('ACTIVE','DRAFT');
1719
1720 proc_rec get_all_processes%rowtype;
1721
1722 BEGIN
1723 fnd_file.put_line (fnd_file.LOG, 'Certification_Id:'||p_certification_id);
1724
1725 IF p_certification_id IS NOT NULL
1726 THEN
1727 Populate_Findings(p_certification_id);
1728 ELSE
1729 FOR proc_rec in get_all_processes LOOP
1730 Populate_Findings(proc_rec.CERTIFICATION_ID);
1731 END LOOP;
1732 END IF;
1733
1734 EXCEPTION
1735 WHEN NO_DATA_FOUND
1736 THEN
1737 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Populate_Proccert_Findings'
1738 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1739
1740 WHEN OTHERS
1741 THEN
1742 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Populate_Proccert_Findings'
1743 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1744
1745 END Populate_Proccert_Findings;
1746
1747
1748 END AMW_PROCESS_CERT_SUMMARY;