[Home] [Help]
PACKAGE BODY: APPS.AMW_ORG_CERT_EVAL_SUM_PVT
Source
1 PACKAGE BODY AMW_ORG_CERT_EVAL_SUM_PVT AS
2 /* $Header: amwocertb.pls 120.4 2005/11/18 19:49:01 appldev noship $ */
3
4 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
5 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
6
7 G_PKG_NAME CONSTANT VARCHAR2 (30) := 'AMW_ORG_CERT_EVAL_SUM_PVT';
8 G_FILE_NAME CONSTANT VARCHAR2 (15) := 'amwocertb.pls';
9
10
11 PROCEDURE populate_org_cert_summary
12 (
13 x_errbuf OUT NOCOPY VARCHAR2,
14 x_retcode OUT NOCOPY NUMBER,
15 p_certification_id IN NUMBER
16
17 )
18 IS
19
20 l_api_name CONSTANT VARCHAR2(30) := 'populate_org_cert_summary';
21 l_api_version_number CONSTANT NUMBER := 1.0;
22
23 l_return_status VARCHAR2(32767);
24 l_msg_count NUMBER;
25 l_msg_data VARCHAR2(32767);
26
27
28 CURSOR get_all_certifications is
29 SELECT distinct CERTIFICATION_ID
30 FROM AMW_CERTIFICATION_VL
31 WHERE OBJECT_TYPE = 'PROCESS'
32 AND certification_status in ('ACTIVE','DRAFT');
33
34 cert_rec get_all_certifications%rowtype;
35
36 BEGIN
37
38 SAVEPOINT populate_org_summary;
39
40 fnd_file.put_line (fnd_file.LOG, 'Certification_Id:'||p_certification_id);
41
42 IF p_certification_id IS NULL
43 THEN
44 FOR each_rec in get_all_certifications LOOP
45 populate_org_cert_sum_spec (each_rec.certification_id);
46 END LOOP;
47 ELSE
48 populate_org_cert_sum_spec (p_certification_id);
49 END IF;
50
51 EXCEPTION WHEN NO_DATA_FOUND
52 THEN
53 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in populate_org_cert_summary'
54 || SUBSTR (SQLERRM, 1, 100), 1, 200));
55
56 WHEN OTHERS
57 THEN
58 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in populate_org_cert_summary'
59 || SUBSTR (SQLERRM, 1, 100), 1, 200));
60
61 END populate_org_cert_summary
62 ;
63 PROCEDURE populate_org_cert_sum_spec
64 (
65 p_certification_id IN NUMBER
66 )
67 IS
68
69 l_api_name CONSTANT VARCHAR2(30) := 'populate_org_cert_sum_spec';
70 l_api_version_number CONSTANT NUMBER := 1.0;
71
72 l_return_status VARCHAR2(32767);
73 l_msg_count NUMBER;
74 l_msg_data VARCHAR2(32767);
75
76 -- select all organizations in scope for the certification
77 CURSOR get_all_orgs
78 IS
79 SELECT DISTINCT organization_id
80 FROM amw_execution_scope
81 WHERE entity_type = 'BUSIPROC_CERTIFICATION'
82 AND entity_id = p_certification_id
83 AND organization_id is not null;
84
85 proc_rec get_all_orgs%rowtype;
86
87 BEGIN
88 SAVEPOINT populate_org_specific;
89
90 FOR org_rec IN get_all_orgs LOOP
91 populate_summary
92 (
93 p_api_version_number => 1.0 ,
94 p_org_id => org_rec.organization_id,
95 p_certification_id => p_certification_id,
96 x_return_status => l_return_status,
97 x_msg_count => l_msg_count,
98 x_msg_data => l_msg_data
99 );
100 END LOOP;
101
102 fnd_file.put_line(fnd_file.LOG, 'end timestamp :'||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
103
104 EXCEPTION WHEN OTHERS THEN
105 ROLLBACK TO POPULATE_ORG_SPECIFIC;
106 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'populate_org_cert_sum_spec');
107
108 END populate_org_cert_sum_spec;
109
110
111 PROCEDURE populate_summary
112 (
113 p_api_version_number IN NUMBER,
114 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
115 p_commit IN VARCHAR2 := FND_API.g_false,
116 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
117 p_org_id IN NUMBER,
118 p_certification_id IN NUMBER,
119 x_return_status OUT nocopy VARCHAR2,
120 x_msg_count OUT nocopy NUMBER,
121 x_msg_data OUT nocopy VARCHAR2
122 )
123 IS
124
125 CURSOR get_certification_opinion
126 IS
127 SELECT opinion.opinion_id
128 FROM amw_opinions_v opinion
129 WHERE opinion.pk1_value = p_org_id
130 AND opinion.pk2_value = p_certification_id
131 AND opinion.opinion_type_code = 'CERTIFICATION'
132 AND opinion.object_name = 'AMW_ORGANIZATION';
133
134 CURSOR get_evaluation_opinion
135 IS
136 SELECT opinion.opinion_id
137 FROM amw_opinions_v opinion
138 WHERE (opinion.authored_date IN (SELECT MAX(opinion2.authored_date)
139 FROM amw_opinions_v opinion2
140 WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
141 AND opinion2.pk1_value = opinion.pk1_value
142 )
143 )
144 AND opinion.pk1_value = p_org_id
145 AND opinion.opinion_type_code = 'EVALUATION'
146 AND opinion.object_name = 'AMW_ORGANIZATION';
147
148 CURSOR get_evaluation_opinion_log
149 IS
150 SELECT opinion.opinion_log_id
151 FROM amw_opinions_log_v opinion
152 WHERE (opinion.authored_date IN (SELECT MAX(opinion2.authored_date)
153 FROM amw_opinions opinion2
154 WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
155 AND opinion2.pk1_value = opinion.pk1_value)
156 )
157 AND opinion.pk1_value = p_org_id
158 AND opinion.opinion_type_code = 'EVALUATION'
159 AND opinion.object_name = 'AMW_ORGANIZATION';
160
161 CURSOR get_unmitigated_risks
162 IS
163 SELECT count(1)
164 FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
165 FROM amw_risk_associations ara,amw_opinions_v aov
166 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
167 AND ara.pk1 = p_certification_id
168 AND ara.pk2 = p_org_id
169 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
170 AND aov.opinion_type_code = 'EVALUATION'
171 AND aov.pk1_value = ara.risk_id
172 AND aov.pk3_value = p_org_id
173 AND NVL(aov.pk4_value,-1)
174 = NVL(ara.pk3, -1) --process_id
175 AND aov.audit_result_code <> 'EFFECTIVE'
176 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
177 FROM amw_opinions aov2
178 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
179 AND NVL(aov2.pk4_value, -1)
180 = NVL(aov.pk4_value, -1)
181 AND aov2.pk3_value = aov.pk3_value
182 AND aov2.pk1_value = aov.pk1_value)
183
184 );
185
186 CURSOR get_evaluated_risks
187 IS
188 SELECT count(1)
189 FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
190 FROM amw_risk_associations ara,amw_opinions_v aov
191 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
192 AND ara.pk1 = p_certification_id
193 AND ara.pk2 = p_org_id
194 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
195 AND aov.opinion_type_code = 'EVALUATION'
196 AND aov.pk1_value = ara.risk_id
197 AND aov.pk3_value = ara.pk2 --org_id
198 AND NVL(aov.pk4_value, -1)
199 = NVL(ara.pk3, -1) --process_id
200 AND aov.audit_result_code IS NOT NULL
201 );
202
203 CURSOR get_total_risks
204 IS
205 ---07.05.2005 npanandi: added ara.pk3 below for processId --- bugfix for bug 4471783
206 ---10.03.2005 dliao: change count(pk3) to count(1) because of entity_risk
207 SELECT count(1) from (
208 select distinct ara.pk3, ara.risk_id
209 FROM amw_risk_associations ara
210 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
211 AND ara.pk1 = p_certification_id
212 AND ara.pk2 = p_org_id);
213
214 CURSOR get_ineffective_controls
215 IS
216 SELECT count(1)
217 ---07.05.2005 npanandi: changed below query to have a distinct on
218 ---certificationId, organizationId, controlId
219 ---instead of having a distinct on
220 ---certificationId, organizationId, processId, riskId, controlId
221 ---FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id, aca.control_id
222 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
223 FROM amw_control_associations aca,amw_opinions_v aov
224 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
225 AND aca.pk1 = p_certification_id
226 AND aca.pk2 = p_org_id
227 AND aov.object_name = 'AMW_ORG_CONTROL'
228 AND aov.opinion_type_code = 'EVALUATION'
229 AND aov.pk1_value = aca.control_id
230 AND aov.pk3_value = aca.pk2
231 AND aov.audit_result_code <> 'EFFECTIVE'
232 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
233 FROM amw_opinions aov2
234 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
235 AND aov2.pk3_value = aov.pk3_value
236 AND aov2.pk1_value = aov.pk1_value)
237 );
238
239
240 CURSOR get_evaluated_controls
241 IS
242 SELECT count(1)
243 ---07.05.2005 npanandi: changed below query to have a distinct on
244 ---certificationId, organizationId, controlId
245 ---instead of having a distinct on
246 ---certificationId, organizationId, processId, riskId, controlId
247 ---FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id, aca.control_id
248 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
249 FROM amw_control_associations aca,amw_opinions_v aov
250 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
251 AND aca.pk1 = p_certification_id
252 AND aca.pk2 = p_org_id
253 AND aov.object_name = 'AMW_ORG_CONTROL'
254 AND aov.opinion_type_code = 'EVALUATION'
255 AND aov.pk1_value = aca.control_id
256 AND aov.pk3_value = aca.pk2
257 AND aov.audit_result_code IS NOT NULL
258 );
259
260 CURSOR get_total_controls
261 IS
262 SELECT count(DISTINCT aca.control_id)
263 FROM amw_control_associations aca
264 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
265 AND aca.pk1 = p_certification_id
266 AND aca.pk2 = p_org_id;
267
268 CURSOR get_all_processes
269 IS
270 SELECT count(distinct process_id)
271 FROM amw_execution_scope
272 WHERE organization_id = p_org_id
273 AND entity_id = p_certification_id
274 ---07.05.2005 npanandi: added entity_type below, bugfix for bug 4471783
275 AND entity_type = 'BUSIPROC_CERTIFICATION';
276
277 --modified by dliao 11.8.2005
278 --change object_name to 'AMW_ORG_PROCESS' from 'AMW_PROCESS_ORG'
279 CURSOR get_certified_processes
280 IS
281 SELECT count(DISTINCT process_id)
282 FROM amw_execution_scope amw_exec
283 WHERE EXISTS (SELECT opinion.opinion_id
284 FROM amw_opinions_v opinion
285 WHERE opinion.pk1_value = amw_exec.process_id
286 AND opinion.pk3_value = p_org_id
287 AND opinion.pk2_value = p_certification_id
288 AND opinion.opinion_type_code = 'CERTIFICATION'
289 AND opinion.object_name = 'AMW_ORG_PROCESS'
290 );
291
292 --modified by dliao 11.8.2005
293 --change object_name to 'AMW_ORG_PROCESS' from 'AMW_PROCESS_ORG'
294 CURSOR get_proc_cert_issues
295 IS
296 SELECT count(DISTINCT process_id)
297 FROM amw_execution_scope amw_exec
298 WHERE EXISTS (SELECT opinion.opinion_id
299 FROM amw_opinions_v opinion
300 WHERE opinion.pk1_value = amw_exec.process_id
301 AND opinion.pk3_value = p_org_id
302 AND opinion.pk2_value = p_certification_id
303 AND opinion.opinion_type_code = 'CERTIFICATION'
304 AND opinion.object_name = 'AMW_ORG_PROCESS'
305 AND opinion.audit_result_code <> 'EFFECTIVE'
306 );
307
308 --modified by dliao 10.13.2005
309 --pk2_value should be project id when the type code is evaluation
310 --add max() to get the latest evaluation result
311 --add entity_type and entity_id
312 CURSOR get_evaluated_processes
313 IS
314 SELECT count(DISTINCT process_id)
315 FROM amw_execution_scope amw_exec
316 WHERE amw_exec.entity_type = 'BUSIPROC_CERTIFICATION'
317 AND amw_exec.entity_id = p_certification_id
318 AND EXISTS (SELECT opinion.opinion_id
319 FROM amw_opinions_v opinion
320 WHERE opinion.pk1_value = amw_exec.process_id
321 --AND opinion.pk2_value = p_certification_id
322 AND opinion.pk3_value = p_org_id
323 AND opinion.opinion_type_code = 'EVALUATION'
324 AND opinion.object_name = 'AMW_ORG_PROCESS'
325 AND opinion.audit_result_code IS NOT NULL
326 AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
327 FROM amw_opinions aov2
328 WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
329 AND aov2.pk3_value = opinion.pk3_value
330 AND aov2.pk1_value = opinion.pk1_value)
331 );
332
333 --modified by dliao 10.13.2005
334 --pk2_value should be project id when the type code is evaluation
335 --add max() to get the latest evaluation result
336 --add entity_type and entity_id
337 CURSOR get_ineffective_processes
338 IS
339 SELECT count(DISTINCT process_id)
340 FROM amw_execution_scope amw_exec
341 WHERE amw_exec.entity_type = 'BUSIPROC_CERTIFICATION'
342 AND amw_exec.entity_id = p_certification_id
343 AND EXISTS (SELECT opinion.opinion_id
344 FROM amw_opinions_v opinion
345 WHERE opinion.pk1_value = amw_exec.process_id
346 --AND opinion.pk2_value = p_certification_id
347 AND opinion.pk3_value = p_org_id
348 AND opinion.opinion_type_code = 'EVALUATION'
349 AND opinion.object_name = 'AMW_ORG_PROCESS'
350 AND opinion.audit_result_code <> 'EFFECTIVE'
351 AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
352 FROM amw_opinions aov2
353 WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
354 AND aov2.pk3_value = opinion.pk3_value
355 AND aov2.pk1_value = opinion.pk1_value)
356 );
357
358 CURSOR get_certified_sub_orgs
359 IS
360 SELECT count(distinct object_id)
361 FROM amw_entity_hierarchies ent
362 WHERE EXISTS (SELECT opinion.opinion_id
363 FROM amw_opinions_v opinion
364 WHERE opinion.pk1_value = p_org_id
365 AND opinion.pk2_value = p_certification_id
366 AND opinion.opinion_type_code = 'CERTIFICATION'
367 AND opinion.object_name = 'AMW_ORGANIZATION'
368 )
369 START WITH parent_object_id = p_org_id
370 AND parent_object_type = 'ORG'
371 AND entity_id = p_certification_id
372 AND entity_type = 'BUSIPROC_CERTIFICATION'
373 CONNECT BY PRIOR object_id = PRIOR parent_object_id
374 AND object_type = PRIOR parent_object_type
375 AND entity_id = PRIOR entity_id
376 AND entity_type = PRIOR entity_type;
377
378 CURSOR get_sub_org_cert_issues
379 IS
380 SELECT count(distinct object_id)
381 FROM amw_entity_hierarchies ent
382 WHERE EXISTS (SELECT opinion.opinion_id
383 FROM amw_opinions_v opinion
384 WHERE opinion.pk1_value = p_org_id
385 AND opinion.pk2_value = p_certification_id
386 AND opinion.opinion_type_code = 'CERTIFICATION'
387 AND opinion.object_name = 'AMW_ORGANIZATION'
388 AND opinion.audit_result_code <> 'EFFECTIVE'
389 )
390 START WITH parent_object_id = p_org_id
391 AND parent_object_type = 'ORG'
392 AND entity_id = p_certification_id
393 AND entity_type = 'BUSIPROC_CERTIFICATION'
394 CONNECT BY PRIOR object_id = PRIOR parent_object_id
395 AND object_type = PRIOR parent_object_type
396 AND entity_id = PRIOR entity_id
397 AND entity_type = PRIOR entity_type;
398
399 CURSOR get_total_sub_orgs
400 IS
401 SELECT count(distinct object_id)
402 FROM amw_entity_hierarchies ent
403 START WITH parent_object_id = p_org_id
404 AND parent_object_type = 'ORG'
405 AND entity_id = p_certification_id
406 ---07.05.2005 npanandi: add entityType, bugfix 4471783
407 and entity_type='BUSIPROC_CERTIFICATION'
408 CONNECT BY PRIOR object_id = PRIOR parent_object_id
409 AND object_type = PRIOR parent_object_type
410 AND entity_id = PRIOR entity_id
411 ---07.05.2005 npanandi: add entityType, bugfix 4471783
412 and entity_type=prior entity_type;
413
414 CURSOR get_top_org_processes
415 IS
416 SELECT count(distinct aes.process_id)
417 FROM amw_execution_scope aes
418 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
419 AND aes.level_id = 4
420 AND aes.parent_process_id = -1
421 AND aes.entity_id = p_certification_id
422 AND aes.organization_id = p_org_id;
423
424 CURSOR get_top_orgproc_pend_cert
425 IS
426 SELECT count(distinct aes.process_id)
427 FROM amw_execution_scope aes
428 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
429 AND aes.level_id = 4
430 AND aes.parent_process_id = -1 --need to verify if this is -2
431 AND aes.entity_id = p_certification_id
432 AND aes.organization_id = p_org_id
433 AND NOT EXISTS (SELECT 'Y'
434 FROM AMW_OPINIONS_V aov
435 WHERE aov.object_name = 'AMW_ORG_PROCESS'
436 AND aov.opinion_type_code = 'CERTIFICATION'
437 AND aov.pk3_value = aes.organization_id
438 AND aov.pk2_value = p_certification_id
439 AND aov.pk1_value = aes.process_id);
440
441 l_certification_opinion_id NUMBER;
442 l_evaluation_opinion_id NUMBER;
443 l_evaluation_opinion_log_id NUMBER;
444 l_unmitigated_risks NUMBER;
445 l_evaluated_risks NUMBER;
446 l_total_risks NUMBER;
447 l_ineffective_controls NUMBER;
448 l_evaluated_controls NUMBER;
449 l_total_controls NUMBER;
450 l_processes_certified NUMBER;
451 l_processes_total NUMBER;
452 l_sub_orgs NUMBER;
453 l_all_orgs NUMBER;
454 l_open_findings NUMBER;
455 l_open_issues NUMBER;
456 l_top_org_processes NUMBER;
457 l_top_orgproc_pend_cert NUMBER;
458 l_evaluated_processes NUMBER;
459 l_ineffective_processes NUMBER;
460 l_proc_cert_issues NUMBER;
461 l_sub_org_cert_issues NUMBER;
462
463 l_api_name CONSTANT VARCHAR2(30) := 'populate_summary';
464 l_api_version_number CONSTANT NUMBER := 1.0;
465
466 l_return_status VARCHAR2(32767);
467 l_msg_count NUMBER;
468 l_msg_data VARCHAR2(32767);
469
470
471 BEGIN
472
473 SAVEPOINT populate_summ;
474
475 -- Standard call to check for call compatibility.
476 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
477 p_api_version_number,
478 l_api_name,
479 G_PKG_NAME)
480 THEN
481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
482 END IF;
483
484
485 -- Initialize message list if p_init_msg_list is set to TRUE.
486 IF FND_API.to_Boolean( p_init_msg_list )
487 THEN
488 FND_MSG_PUB.initialize;
489 END IF;
490
491 OPEN get_certification_opinion;
492 FETCH get_certification_opinion into l_certification_opinion_id;
493 CLOSE get_certification_opinion;
494
495 OPEN get_evaluation_opinion;
496 FETCH get_evaluation_opinion into l_evaluation_opinion_id;
497 CLOSE get_evaluation_opinion;
498
499 OPEN get_evaluation_opinion_log;
500 FETCH get_evaluation_opinion_log into l_evaluation_opinion_log_id;
501 CLOSE get_evaluation_opinion_log;
502
503 OPEN get_unmitigated_risks;
504 FETCH get_unmitigated_risks into l_unmitigated_risks;
505 CLOSE get_unmitigated_risks;
506
507 OPEN get_evaluated_risks;
508 FETCH get_evaluated_risks into l_evaluated_risks;
509 CLOSE get_evaluated_risks;
510
511 OPEN get_total_risks;
512 FETCH get_total_risks into l_total_risks;
513 CLOSE get_total_risks;
514
515 OPEN get_ineffective_controls;
516 FETCH get_ineffective_controls into l_ineffective_controls;
517 CLOSE get_ineffective_controls;
518
519 OPEN get_evaluated_controls;
520 FETCH get_evaluated_controls into l_evaluated_controls;
521 CLOSE get_evaluated_controls;
522
523 OPEN get_total_controls;
524 FETCH get_total_controls into l_total_controls;
525 CLOSE get_total_controls;
526
527 OPEN get_certified_processes;
528 FETCH get_certified_processes into l_processes_certified;
529 CLOSE get_certified_processes;
530
531 OPEN get_evaluated_processes;
532 FETCH get_evaluated_processes into l_evaluated_processes;
533 CLOSE get_evaluated_processes;
534
535 OPEN get_ineffective_processes;
536 FETCH get_ineffective_processes into l_ineffective_processes;
537 CLOSE get_ineffective_processes;
538
539 OPEN get_all_processes;
540 FETCH get_all_processes into l_processes_total;
541 CLOSE get_all_processes;
542
543 OPEN get_certified_sub_orgs;
544 FETCH get_certified_sub_orgs into l_sub_orgs;
545 CLOSE get_certified_sub_orgs;
546
547 OPEN get_total_sub_orgs;
548 FETCH get_total_sub_orgs into l_all_orgs;
549 CLOSE get_total_sub_orgs;
550
551 OPEN get_top_org_processes;
552 FETCH get_top_org_processes into l_top_org_processes;
553 CLOSE get_top_org_processes;
554
555 OPEN get_top_orgproc_pend_cert;
556 FETCH get_top_orgproc_pend_cert into l_top_orgproc_pend_cert;
557 CLOSE get_top_orgproc_pend_cert;
558
559 OPEN get_sub_org_cert_issues;
560 FETCH get_sub_org_cert_issues into l_sub_org_cert_issues;
561 CLOSE get_sub_org_cert_issues;
562
563 OPEN get_proc_cert_issues;
564 FETCH get_proc_cert_issues into l_proc_cert_issues;
565 CLOSE get_proc_cert_issues;
566
567 l_open_findings := amw_findings_pkg.calculate_open_findings('AMW_PROJ_FINDING',
568 'PROJ_ORG',
569 p_org_id,
570 null, null,
571 null, null,
572 null, null,
573 null, null);
574
575 l_open_issues := amw_findings_pkg.calculate_open_findings('AMW_PROC_CERT_ISSUES',
576 'ORGANIZATION',
577 p_org_id,
578 'CERTIFICATION',
579 p_certification_id,
580 null, null,
581 null, null,
582 null, null);
583 UPDATE AMW_ORG_CERT_EVAL_SUM
584 SET certification_opinion_id = l_certification_opinion_id,
585 evaluation_opinion_id = l_evaluation_opinion_id,
586 evaluation_opinion_log_id= l_evaluation_opinion_log_id,
587 unmitigated_risks = l_unmitigated_risks,
588 evaluated_risks = l_evaluated_risks,
589 total_risks = l_total_risks,
590 ineffective_controls = l_ineffective_controls,
591 evaluated_controls = l_evaluated_controls,
592 total_controls = l_total_controls,
593 processes_certified = l_processes_certified,
594 evaluated_processes = l_evaluated_processes,
595 ineff_processes = l_ineffective_processes,
596 total_processes = l_processes_total,
597 sub_org_cert = l_sub_orgs,
598 total_sub_org = l_all_orgs,
599 top_org_processes = l_top_org_processes,
600 top_org_proc_pending_cert= l_top_orgproc_pend_cert,
601 open_findings = l_open_findings,
602 open_issues = l_open_issues,
603 last_update_date = SYSDATE,
604 last_updated_by = G_USER_ID,
605 last_update_login = G_LOGIN_ID,
606 SUB_ORG_CERT_ISSUES = l_sub_org_cert_issues,
607 PROC_CERT_ISSUES = l_proc_cert_issues,
608 INEFF_PROCESSES_PRCNT =
609 decode(l_processes_total, 0, 0, round(l_ineffective_processes/l_processes_total*100)),
610 UNMITIGATED_RISKS_PRCNT =
611 decode(l_total_risks, 0, 0, round(l_unmitigated_risks/l_total_risks*100)),
612 INEFF_CONTROLS_PRCNT =
613 decode(l_total_controls, 0, 0, round(l_ineffective_controls/l_total_controls*100))
614 WHERE certification_id = p_certification_id
615 AND organization_id = p_org_id;
616
617 IF (SQL%NOTFOUND)
618 THEN
619
620 INSERT INTO AMW_ORG_CERT_EVAL_SUM(certification_opinion_id,
621 evaluation_opinion_id,
622 evaluation_opinion_log_id,
623 unmitigated_risks,
624 evaluated_risks,
625 total_risks,
626 ineffective_controls,
627 evaluated_controls,
628 total_controls,
629 processes_certified,
630 evaluated_processes,
631 ineff_processes,
632 total_processes,
633 sub_org_cert,
634 total_sub_org,
635 top_org_processes,
636 top_org_proc_pending_cert,
637 open_findings,
638 open_issues,
639 certification_id,
640 organization_id,
641 created_by,
642 creation_date,
643 last_updated_by,
644 last_update_date,
645 last_update_login,
646 sub_org_cert_issues,
647 proc_cert_issues,
648 INEFF_PROCESSES_PRCNT,
649 UNMITIGATED_RISKS_PRCNT,
650 INEFF_CONTROLS_PRCNT)
651
652 VALUES (l_certification_opinion_id,
653 l_evaluation_opinion_id,
654 l_evaluation_opinion_log_id,
655 l_unmitigated_risks,
656 l_evaluated_risks,
657 l_total_risks,
658 l_ineffective_controls,
659 l_evaluated_controls,
660 l_total_controls,
661 l_processes_certified,
662 l_evaluated_processes,
663 l_ineffective_processes,
664 l_processes_total,
665 l_sub_orgs,
666 l_all_orgs,
667 l_top_org_processes,
668 l_top_orgproc_pend_cert,
669 l_open_findings,
670 l_open_issues,
671 p_certification_id,
672 p_org_id,
673 G_USER_ID,
674 sysdate,
675 G_USER_ID,
676 sysdate,
677 G_LOGIN_ID,
678 l_sub_org_cert_issues,
679 l_proc_cert_issues,
680 decode(l_processes_total, 0, 0, round(l_ineffective_processes/l_processes_total*100)),
681 decode(l_total_risks, 0, 0, round(l_unmitigated_risks/l_total_risks*100)),
682 decode(l_total_controls, 0, 0, round(l_ineffective_controls/l_total_controls*100)));
683 END IF;
684
685 EXCEPTION WHEN OTHERS
686 THEN
687 ROLLBACK TO populate_summ;
688 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'populate_summary');
689 FND_MSG_PUB.Count_And_Get(
690 p_encoded => FND_API.G_FALSE,
691 p_count => x_msg_count,
692 p_data => x_msg_data);
693 END populate_summary;
694
695 END AMW_ORG_CERT_EVAL_SUM_PVT;