[Home] [Help]
PACKAGE BODY: APPS.AMW_PROJECT_EVENT_PVT
Source
1 PACKAGE BODY AMW_PROJECT_EVENT_PVT AS
2 /* $Header: amwvpjeb.pls 120.2.12000000.2 2007/08/01 09:12:25 srbalasu ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_PROJECT_EVENT_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 g_pkg_name CONSTANT VARCHAR2 (30) := 'AMW_PROJECT_EVENT_PVT';
16 g_file_name CONSTANT VARCHAR2 (12) := 'amwvpceb.pls';
17 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
18 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
19
20 FUNCTION Scope_Update
21 ( p_subscription_guid in raw,
22 p_event in out NOCOPY wf_event_t
23 ) return VARCHAR2
24 IS
25 CURSOR c_new_org(c_audit_proj_id NUMBER) IS
26 SELECT organization_id
27 FROM AMW_AUDIT_SCOPE_ORGANIZATIONS
28 WHERE audit_project_id = c_audit_proj_id
29 AND unmitigated_risks IS NULL;
30
31 CURSOR c_proj_org(c_audit_proj_id NUMBER) IS
32 SELECT organization_id
33 FROM AMW_AUDIT_SCOPE_ORGANIZATIONS
34 WHERE audit_project_id = c_audit_proj_id;
35
36
37 CURSOR c_new_proc(c_audit_proj_id NUMBER) IS
38 SELECT organization_id, process_id
39 FROM AMW_AUDIT_SCOPE_PROCESSES
40 WHERE audit_project_id = c_audit_proj_id
41 AND unmitigated_risks IS NULL;
42
43 CURSOR c_org_proc(c_audit_proj_id NUMBER, c_org_id NUMBER) IS
44 SELECT process_id
45 FROM AMW_AUDIT_SCOPE_PROCESSES
46 WHERE audit_project_id = c_audit_proj_id
47 AND organization_id = c_org_id;
48
49 l_audit_proj_id NUMBER;
50 l_org_id NUMBER;
51 l_mode VARCHAR2(30);
52 BEGIN
53
54 SAVEPOINT Scope_Update_Event;
55
56 l_audit_proj_id := p_event.GetValueForParameter('AUDIT_PROJECT_ID');
57 l_mode := p_event.GetValueForParameter('MODE');
58
59 IF l_mode = 'AddToScope' THEN
60 -- to support org hierarchy, need to update the org denorm
61 -- for all the orgs in the engagement.
62 FOR org_rec IN c_proj_org(l_audit_proj_id) LOOP
63 update_org_summary_table (
64 p_audit_project_id => l_audit_proj_id,
65 p_org_id => org_rec.organization_id);
66 END LOOP;
67
68 -- populate the denorm table for new processes added into scope
69 -- AMW_AUDIT_SCOPE_PROCESSES
70 -- do not need to update the prcess denorm for the existing
71 -- org.
72 FOR proc_rec IN c_new_proc(l_audit_proj_id) LOOP
73
74 update_proc_summary_table (
75 p_audit_project_id => l_audit_proj_id,
76 p_org_id => proc_rec.organization_id,
77 p_proc_id => proc_rec.process_id);
78 END LOOP;
79
80 ELSIF l_mode = 'ManageProc' THEN
81 l_org_id := p_event.GetValueForParameter('ORGANIZATION_ID');
82 update_org_summary_table (
83 p_audit_project_id => l_audit_proj_id,
84 p_org_id => l_org_id);
85
86 FOR proc_rec IN c_org_proc(l_audit_proj_id, l_org_id) LOOP
87 update_proc_summary_table (
88 p_audit_project_id => l_audit_proj_id,
89 p_org_id => l_org_id,
90 p_proc_id => proc_rec.process_id);
91 END LOOP;
92 ELSIF l_mode = 'RemoveFromScope' THEN
93 -- to support org hierarchy, need to update the org denorm
94 -- for all the orgs in the certification
95 FOR org_rec IN c_proj_org(l_audit_proj_id) LOOP
96 update_org_summary_table (
97 p_audit_project_id => l_audit_proj_id,
98 p_org_id => org_rec.organization_id);
99 END LOOP;
100 END IF;
101
102 Return 'SUCCESS';
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 ROLLBACK TO Scope_Update_Event;
107
108 FND_MESSAGE.SET_NAME( 'AMW', 'GENERIC_MESSAGE' );
109 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
110 FND_MSG_PUB.ADD;
111
112 WF_CORE.CONTEXT('AMW_PROJECT_EVENT_PVT', 'SCOPE_UPDATE', p_event.getEventName(), p_subscription_guid);
113 WF_EVENT.setErrorInfo(p_event, 'ERROR');
114
115 RETURN 'ERROR';
116
117 END Scope_Update;
118
119
120 FUNCTION Evaluation_Update
121 ( p_subscription_guid in raw,
122 p_event in out NOCOPY wf_event_t
123 ) return VARCHAR2
124 IS
125 CURSOR c_opinion_rec (c_opinion_log_id NUMBER) IS
126 SELECT opinion_id, object_name, audit_result_code,
127 pk1_value, pk2_value, pk3_value, pk4_value, pk5_value,
128 pk6_value, pk7_value, pk8_value
129 FROM amw_opinions_log_v
130 WHERE opinion_log_id = c_opinion_log_id;
131
132 CURSOR c_prev_opinion_rec (c_opinion_id NUMBER, c_opinion_log_id NUMBER) IS
133 SELECT audit_result_code
134 FROM amw_opinions_log_v
135 WHERE opinion_log_id = (SELECT max(opinion_log_id)
136 FROM amw_opinions_log_v
137 WHERE opinion_id = c_opinion_id
138 AND opinion_log_id < c_opinion_log_id);
139
140 l_opin_log_id NUMBER;
141 l_opin_id NUMBER;
142 l_obj_name VARCHAR2(200);
143 l_new_eval VARCHAR2(200);
144 l_prev_eval VARCHAR2(200);
145 l_pk1 NUMBER;
146 l_pk2 NUMBER;
147 l_pk3 NUMBER;
148 l_pk4 NUMBER;
149 l_pk5 NUMBER;
150 l_pk6 NUMBER;
151 l_pk7 NUMBER;
152 l_pk8 NUMBER;
153
154 l_evaluated_diff NUMBER;
155 l_ineff_diff NUMBER;
156 BEGIN
157
158 SAVEPOINT Evaluation_Update_Event;
159
160 l_opin_log_id := p_event.GetValueForParameter('OPINION_LOG_ID');
161
162 OPEN c_opinion_rec(l_opin_log_id);
163 FETCH c_opinion_rec INTO l_opin_id, l_obj_name, l_new_eval, l_pk1,
164 l_pk2, l_pk3, l_pk4, l_pk5, l_pk6, l_pk7, l_pk8;
165 CLOSE c_opinion_rec;
166
167 OPEN c_prev_opinion_rec(l_opin_id, l_opin_log_id);
168 FETCH c_prev_opinion_rec INTO l_prev_eval;
169 CLOSE c_prev_opinion_rec;
170
171
172 select decode(l_prev_eval, null, 1, 0)
173 into l_evaluated_diff
174 from dual;
175
176 select decode(l_new_eval,
177 l_prev_eval, 0,
178 'EFFECTIVE', decode(l_prev_eval, null, 0, -1),
179 decode(l_prev_eval, 'EFFECTIVE', 1, null, 1, 0))
180 into l_ineff_diff
181 from dual;
182
183 IF l_obj_name = 'AMW_ORGANIZATION' THEN
184 UPDATE amw_audit_scope_organizations
185 SET last_update_date = sysdate,
186 last_updated_by = fnd_global.user_id,
187 last_update_login = fnd_global.conc_login_id,
188 evaluation_opinion_id = l_opin_id
189 WHERE organization_id = l_pk1
190 AND audit_project_id = l_pk2;
191
192 UPDATE amw_audit_scope_organizations
193 SET last_update_date = sysdate,
194 last_updated_by = fnd_global.user_id,
195 last_update_login = fnd_global.conc_login_id,
196 sub_orgs_evaluated = least(sub_orgs_evaluated+l_evaluated_diff,total_sub_orgs),
197 ineffective_sub_orgs =
198 least(greatest(0,ineffective_sub_orgs+l_ineff_diff),sub_orgs_evaluated+l_evaluated_diff,total_sub_orgs)
199 WHERE organization_id IN (
200 SELECT parent_object_id
201 FROM amw_entity_hierarchies
202 START WITH entity_type = 'PROJECT'
203 AND entity_id = l_pk2
204 AND object_type = 'ORG'
205 AND object_id = l_pk1
206 CONNECT BY entity_type = PRIOR entity_type
207 AND entity_id = PRIOR entity_id
208 AND object_type = PRIOR object_type
209 AND object_id = PRIOR parent_object_id)
210 AND audit_project_id = l_pk2;
211
212 ELSIF l_obj_name = 'AMW_ORG_PROCESS' THEN
213 UPDATE amw_audit_scope_processes
214 SET last_update_date = sysdate,
215 last_updated_by = fnd_global.user_id,
216 last_update_login = fnd_global.conc_login_id,
217 evaluation_opinion_id = l_opin_id
218 WHERE process_id = l_pk1
219 AND audit_project_id = l_pk2
220 AND organization_id = l_pk3;
221
222 UPDATE amw_audit_scope_organizations
223 SET last_update_date = sysdate,
224 last_updated_by = fnd_global.user_id,
225 last_update_login = fnd_global.conc_login_id,
226 processes_evaluated = least(processes_evaluated+l_evaluated_diff,total_processes),
227 ineffective_processes =
228 least(greatest(0,ineffective_processes+l_ineff_diff),processes_evaluated+l_evaluated_diff,total_processes)
229 WHERE audit_project_id = l_pk2
230 AND organization_id = l_pk3;
231 ELSIF l_obj_name = 'AMW_ORG_PROCESS_RISK' THEN
232 UPDATE amw_audit_scope_organizations
233 SET last_update_date = sysdate,
234 last_updated_by = fnd_global.user_id,
235 last_update_login = fnd_global.conc_login_id,
236 risks_evaluated =
237 least(risks_evaluated+l_evaluated_diff,total_risks),
238 unmitigated_risks =
239 least(greatest(0,unmitigated_risks+l_ineff_diff),risks_evaluated+l_evaluated_diff,total_risks)
240 WHERE audit_project_id = l_pk2
241 AND organization_id = l_pk3;
242
243 UPDATE amw_audit_scope_processes
244 SET last_update_date = sysdate,
245 last_updated_by = fnd_global.user_id,
246 last_update_login = fnd_global.conc_login_id,
247 risks_evaluated =
248 least(risks_evaluated+l_evaluated_diff,total_risks),
249 unmitigated_risks =
250 least(greatest(0,unmitigated_risks+l_ineff_diff),risks_evaluated+l_evaluated_diff,total_risks)
251 WHERE audit_project_id = l_pk2
252 AND organization_id = l_pk3
253 AND process_id IN (SELECT process_id
254 FROM amw_execution_scope
255 START WITH process_id = l_pk4
256 AND organization_id = l_pk3
257 AND entity_id = l_pk2
258 AND entity_type = 'PROJECT'
259 CONNECT BY PRIOR parent_process_id = process_id
260 AND organization_id = PRIOR organization_id
261 AND entity_id = PRIOR entity_id
262 AND entity_type = PRIOR entity_type);
263
264 ELSIF l_obj_name = 'AMW_ORG_CONTROL' THEN
265
266 UPDATE amw_audit_scope_organizations
267 SET last_update_date = sysdate,
268 last_updated_by = fnd_global.user_id,
269 last_update_login = fnd_global.conc_login_id,
270 controls_evaluated =
271 least(controls_evaluated+l_evaluated_diff,total_controls),
272 ineffective_controls =
273 least(greatest(0,ineffective_controls+l_ineff_diff),controls_evaluated+l_evaluated_diff,total_controls)
274 WHERE audit_project_id = l_pk2
275 AND organization_id = l_pk3;
276
277 UPDATE amw_audit_scope_processes
278 SET last_update_date = sysdate,
279 last_updated_by = fnd_global.user_id,
280 last_update_login = fnd_global.conc_login_id,
281 controls_evaluated =
282 least(controls_evaluated+l_evaluated_diff,total_controls),
283 ineffective_controls =
284 least(greatest(0,ineffective_controls+l_ineff_diff),controls_evaluated+l_evaluated_diff,total_controls)
285 WHERE audit_project_id = l_pk2
286 AND organization_id = l_pk3
287 AND process_id IN (SELECT process_id
288 FROM amw_execution_scope
289 START WITH process_id IN ( SELECT pk3
290 FROM amw_control_associations
291 WHERE object_type = 'PROJECT'
292 AND control_id = l_pk1
293 AND pk1 = l_pk2 --audit_project_id
294 AND pk2 = l_pk3 --organization_id
295 )
296 AND organization_id = l_pk3
297 AND entity_id = l_pk2
298 AND entity_type = 'PROJECT'
299 CONNECT BY PRIOR parent_process_id = process_id
300 AND organization_id = PRIOR organization_id
301 AND entity_id = PRIOR entity_id
302 AND entity_type = PRIOR entity_type);
303 END IF;
304
305 -- somehow the change was not being committed to db without
306 -- the following commit. so we temporarily put commit here, and we
307 -- still need to invetigate why the transaction was not committed
308 -- automatically.
309 commit;
310
311 Return 'SUCCESS';
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 ROLLBACK TO Evaluation_Update_Event;
316
317 FND_MESSAGE.SET_NAME( 'AMW', 'GENERIC_MESSAGE' );
318 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
319 FND_MSG_PUB.ADD;
320
321 WF_CORE.CONTEXT('AMW_PROJECT_EVENT_PVT', 'EVALUATION_UPDATE', p_event.getEventName(), p_subscription_guid);
322 WF_EVENT.setErrorInfo(p_event, 'ERROR');
323
324 RETURN 'ERROR';
325
326 END Evaluation_Update;
327
328
329 PROCEDURE update_org_summary_table (
330 p_audit_project_id IN NUMBER,
331 p_org_id IN NUMBER
332 ) IS
333
334 CURSOR get_total_sub_orgs IS
335 SELECT count(distinct object_id)
336 FROM amw_entity_hierarchies hier
337 START WITH entity_id = p_audit_project_id
338 AND entity_type = 'PROJECT'
339 AND object_type = 'ORG'
340 AND parent_object_id = p_org_id
341 CONNECT BY entity_type = PRIOR entity_type
342 AND entity_id = PRIOR entity_id
343 AND object_type = PRIOR object_type
344 AND parent_object_id = PRIOR object_id;
345
346 CURSOR get_sub_orgs_evaluated IS
347 SELECT count(pk1_value)
348 FROM amw_opinions_v
349 WHERE opinion_type_code = 'EVALUATION'
350 AND object_name = 'AMW_ORGANIZATION'
351 AND pk2_value = p_audit_project_id
352 AND pk1_value IN (
353 SELECT object_id
354 FROM amw_entity_hierarchies
355 START WITH entity_id = p_audit_project_id
356 AND entity_type = 'PROJECT'
357 AND object_type = 'ORG'
358 AND parent_object_id = p_org_id
359 CONNECT BY entity_type = PRIOR entity_type
360 AND entity_id = PRIOR entity_id
361 AND object_type = PRIOR object_type
365 SELECT count(pk1_value)
362 AND parent_object_id = PRIOR object_id);
363
364 CURSOR get_ineff_sub_orgs IS
366 FROM amw_opinions_v
367 WHERE opinion_type_code = 'EVALUATION'
368 AND object_name = 'AMW_ORGANIZATION'
369 AND audit_result_code <> 'EFFECTIVE'
370 AND pk2_value = p_audit_project_id
371 AND pk1_value IN (
372 SELECT object_id
373 FROM amw_entity_hierarchies
374 START WITH entity_id = p_audit_project_id
375 AND entity_type = 'PROJECT'
376 AND object_type = 'ORG'
377 AND parent_object_id = p_org_id
378 CONNECT BY entity_type = PRIOR entity_type
379 AND entity_id = PRIOR entity_id
380 AND object_type = PRIOR object_type
381 AND parent_object_id = PRIOR object_id);
382
383 CURSOR get_total_processes IS
384 SELECT count(distinct process_id)
385 FROM amw_execution_scope
386 WHERE entity_type = 'PROJECT'
387 AND entity_id = p_audit_project_id
388 AND organization_id = p_org_id;
389
390 CURSOR get_processes_evaluated IS
391 SELECT count(pk1_value)
392 FROM amw_opinions_v opin
393 WHERE opinion_type_code = 'EVALUATION'
394 AND object_name = 'AMW_ORG_PROCESS'
395 AND pk2_value = p_audit_project_id
396 AND pk3_value = p_org_id
397 AND exists (select 'Y' from amw_execution_scope scope
398 where scope.entity_type='PROJECT'
399 and scope.entity_id=p_audit_project_id
400 and scope.organization_id=opin.pk3_value
401 and scope.process_id=opin.pk1_value);
402
403 CURSOR get_ineff_processes IS
404 SELECT count(pk1_value)
405 FROM amw_opinions_v opin
406 WHERE opinion_type_code = 'EVALUATION'
407 AND object_name = 'AMW_ORG_PROCESS'
408 AND audit_result_code <> 'EFFECTIVE'
409 AND pk2_value = p_audit_project_id
410 AND pk3_value = p_org_id
411 AND exists (select 'Y' from amw_execution_scope scope
412 where scope.entity_type='PROJECT'
413 and scope.entity_id=p_audit_project_id
414 and scope.organization_id=opin.pk3_value
415 and scope.process_id=opin.pk1_value);
416
417 CURSOR get_total_risks IS
418 SELECT count(risk_id)
419 FROM amw_risk_associations
420 WHERE object_type = 'PROJECT'
421 AND pk1 = p_audit_project_id
422 AND pk2 = p_org_id;
423
424 CURSOR get_risks_evaluated IS
425 SELECT count(opin.pk1_value)
426 FROM amw_risk_associations assoc, amw_opinions_v opin
427 WHERE assoc.object_type = 'PROJECT'
428 AND assoc.pk1 = p_audit_project_id
429 AND assoc.pk2 = p_org_id
430 AND opin.opinion_type_code = 'EVALUATION'
431 AND opin.object_name = 'AMW_ORG_PROCESS_RISK'
432 AND opin.pk1_value = assoc.risk_id
433 AND opin.pk2_value = assoc.pk1
434 AND opin.pk3_value = assoc.pk2;
435
436 CURSOR get_unmitigated_risks IS
437 SELECT count(opin.pk1_value)
438 FROM amw_risk_associations assoc, amw_opinions_v opin
439 WHERE assoc.object_type = 'PROJECT'
440 AND assoc.pk1 = p_audit_project_id
441 AND assoc.pk2 = p_org_id
442 AND opin.opinion_type_code = 'EVALUATION'
443 AND opin.object_name = 'AMW_ORG_PROCESS_RISK'
444 AND opin.pk1_value = assoc.risk_id
445 AND opin.pk2_value = assoc.pk1
446 AND opin.pk3_value = assoc.pk2
447 AND opin.audit_result_code <> 'EFFECTIVE';
448
449 CURSOR get_total_controls IS
450 SELECT count(distinct control_id)
451 FROM amw_control_associations
452 WHERE object_type = 'PROJECT'
453 AND pk1 = p_audit_project_id
454 AND pk2 = p_org_id;
455
456 CURSOR get_controls_evaluated IS
457 SELECT count(pk1_value)
458 FROM amw_opinions_v
459 WHERE opinion_type_code = 'EVALUATION'
460 AND object_name = 'AMW_ORG_CONTROL'
461 AND pk2_value = p_audit_project_id
462 AND pk3_value = p_org_id
463 AND exists (select 'Y' FROM amw_control_associations
464 WHERE object_type = 'PROJECT'
465 AND pk1 = p_audit_project_id
466 AND pk2 = p_org_id
467 AND control_id = pk1_value);
468
469 CURSOR get_ineff_controls IS
470 SELECT count(pk1_value)
471 FROM amw_opinions_v
472 WHERE opinion_type_code = 'EVALUATION'
473 AND object_name = 'AMW_ORG_CONTROL'
474 AND audit_result_code <> 'EFFECTIVE'
475 AND pk2_value = p_audit_project_id
476 AND pk3_value = p_org_id
477 AND exists (select 'Y' FROM amw_control_associations
478 WHERE object_type = 'PROJECT'
479 AND pk1 = p_audit_project_id
480 AND pk2 = p_org_id
481 AND control_id = pk1_value);
482
483 l_ineff_sub_orgs NUMBER;
484 l_evaluated_sub_orgs NUMBER;
485 l_total_sub_orgs NUMBER;
486
487 l_ineff_processes NUMBER;
488 l_evaluated_processes NUMBER;
489 l_total_processes NUMBER;
490
491 l_unmitigated_risks NUMBER;
492 l_evaluated_risks NUMBER;
493 l_total_risks NUMBER;
494
498
495 l_ineff_controls NUMBER;
496 l_evaluated_controls NUMBER;
497 l_total_controls NUMBER;
499 l_open_findings NUMBER;
500
501
502 BEGIN
503
504 OPEN get_total_sub_orgs;
505 FETCH get_total_sub_orgs INTO l_total_sub_orgs;
506 CLOSE get_total_sub_orgs;
507
508 OPEN get_sub_orgs_evaluated;
509 FETCH get_sub_orgs_evaluated INTO l_evaluated_sub_orgs;
510 CLOSE get_sub_orgs_evaluated;
511
512 OPEN get_ineff_sub_orgs;
513 FETCH get_ineff_sub_orgs INTO l_ineff_sub_orgs;
514 CLOSE get_ineff_sub_orgs;
515
516 OPEN get_total_processes;
517 FETCH get_total_processes INTO l_total_processes;
518 CLOSE get_total_processes;
519
520 OPEN get_ineff_processes;
521 FETCH get_ineff_processes INTO l_ineff_processes;
522 CLOSE get_ineff_processes;
523
524 OPEN get_processes_evaluated;
525 FETCH get_processes_evaluated INTO l_evaluated_processes;
526 CLOSE get_processes_evaluated;
527
528 OPEN get_unmitigated_risks;
529 FETCH get_unmitigated_risks into l_unmitigated_risks;
530 CLOSE get_unmitigated_risks;
531
532 OPEN get_risks_evaluated;
533 FETCH get_risks_evaluated into l_evaluated_risks;
534 CLOSE get_risks_evaluated;
535
536 OPEN get_total_risks;
537 FETCH get_total_risks into l_total_risks;
538 CLOSE get_total_risks;
539
540 OPEN get_ineff_controls;
541 FETCH get_ineff_controls into l_ineff_controls;
542 CLOSE get_ineff_controls;
543
544 OPEN get_controls_evaluated;
545 FETCH get_controls_evaluated into l_evaluated_controls;
546 CLOSE get_controls_evaluated;
547
548 OPEN get_total_controls;
549 FETCH get_total_controls into l_total_controls;
550 CLOSE get_total_controls;
551
552
553 l_open_findings := amw_findings_pkg.calculate_open_findings
554 ('AMW_PROJ_FINDING',
555 'PROJ_ORG', p_org_id,
556 'PROJECT', p_audit_project_id,
557 null, null,
558 null, null, null, null);
559
560 UPDATE amw_audit_scope_organizations
561 SET sub_orgs_evaluated = l_evaluated_sub_orgs,
562 ineffective_sub_orgs = l_ineff_sub_orgs,
563 total_sub_orgs = l_total_sub_orgs,
564 processes_evaluated = l_evaluated_processes,
565 ineffective_processes = l_ineff_processes,
566 total_processes = l_total_processes,
567 unmitigated_risks = l_unmitigated_risks,
568 risks_evaluated = l_evaluated_risks,
569 total_risks = l_total_risks,
570 ineffective_controls = l_ineff_controls,
571 controls_evaluated = l_evaluated_controls,
572 total_controls = l_total_controls,
573 open_findings = l_open_findings,
574 last_update_date = SYSDATE,
575 last_updated_by = G_USER_ID,
576 last_update_login = G_LOGIN_ID
577 WHERE audit_project_id = p_audit_project_id
578 AND organization_id = p_org_id;
579
580 IF (SQL%NOTFOUND) THEN
581 INSERT INTO amw_audit_scope_organizations (
582 audit_project_id,
583 subsidiary_vs,
584 subsidiary_code,
585 lob_vs,
586 lob_code,
587 organization_id,
588 sub_orgs_evaluated,
589 ineffective_sub_orgs,
590 total_sub_orgs,
591 processes_evaluated,
592 ineffective_processes,
593 total_processes,
594 risks_evaluated,
595 unmitigated_risks,
596 total_risks,
597 controls_evaluated,
598 ineffective_controls,
599 total_controls,
600 open_findings,
601 created_by,
602 creation_date,
603 last_updated_by,
604 last_update_date,
605 last_update_login,
606 object_version_number)
607 SELECT p_audit_project_id,
608 subsidiary_valueset,
609 company_code,
610 lob_valueset,
611 lob_code,
612 p_org_id,
613 l_evaluated_sub_orgs,
614 l_ineff_sub_orgs,
615 l_total_sub_orgs,
616 l_evaluated_processes,
617 l_ineff_processes,
618 l_total_processes,
619 l_evaluated_risks,
620 l_unmitigated_risks,
621 l_total_risks,
622 l_evaluated_controls,
623 l_ineff_controls,
624 l_total_controls,
625 l_open_findings,
626 g_user_id,
627 sysdate,
628 g_user_id,
629 sysdate,
630 g_login_id,
631 1
632 FROM amw_audit_units_v
633 WHERE organization_id = p_org_id;
634 END IF;
635
636 EXCEPTION
637 WHEN NO_DATA_FOUND THEN
638 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in update_org_summary_table'
639 || SUBSTR (SQLERRM, 1, 100), 1, 200));
640
641 WHEN OTHERS THEN
642 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in update_org_summary_table'
643 || SUBSTR (SQLERRM, 1, 100), 1, 200));
644 END update_org_summary_table;
645
646
647 PROCEDURE update_proc_summary_table (
648 p_audit_project_id IN NUMBER,
649 p_org_id IN NUMBER,
650 p_proc_id IN NUMBER
651 ) IS
652
653 CURSOR get_total_risks IS
654 SELECT count(risk_id)
658 AND pk2 = p_org_id
655 FROM amw_risk_associations
656 WHERE object_type = 'PROJECT'
657 AND pk1 = p_audit_project_id
659 AND pk3 IN (SELECT process_id
660 FROM amw_execution_scope
661 START WITH process_id = p_proc_id
662 AND organization_id = p_org_id
663 AND entity_id = p_audit_project_id
664 AND entity_type = 'PROJECT'
665 CONNECT BY PRIOR process_id = parent_process_id
666 AND organization_id = PRIOR organization_id
667 AND entity_id = PRIOR entity_id
668 AND entity_type = PRIOR entity_type);
669
670 CURSOR get_risks_evaluated IS
671 SELECT count(pk1_value)
672 FROM amw_opinions_v
673 WHERE opinion_type_code = 'EVALUATION'
674 AND object_name = 'AMW_ORG_PROCESS_RISK'
675 AND pk2_value = p_audit_project_id
676 AND pk3_value = p_org_id
677 AND pk4_value IN (SELECT process_id
678 FROM amw_execution_scope
679 START WITH process_id = p_proc_id
680 AND organization_id = p_org_id
681 AND entity_id = p_audit_project_id
682 AND entity_type = 'PROJECT'
683 CONNECT BY PRIOR process_id = parent_process_id
684 AND organization_id = PRIOR organization_id
685 AND entity_id = PRIOR entity_id
686 AND entity_type = PRIOR entity_type)
687 AND exists (select 'Y' FROM amw_risk_associations
688 WHERE object_type = 'PROJECT'
689 AND pk1 = p_audit_project_id
690 AND pk2 = p_org_id
691 AND pk3 = pk4_value
692 AND risk_id = pk1_value);
693
694 CURSOR get_unmitigated_risks IS
695 SELECT count(pk1_value)
696 FROM amw_opinions_v
697 WHERE opinion_type_code = 'EVALUATION'
698 AND object_name = 'AMW_ORG_PROCESS_RISK'
699 AND audit_result_code <> 'EFFECTIVE'
700 AND pk2_value = p_audit_project_id
701 AND pk3_value = p_org_id
702 AND pk4_value IN (SELECT process_id
703 FROM amw_execution_scope
704 START WITH process_id = p_proc_id
705 AND organization_id = p_org_id
706 AND entity_id = p_audit_project_id
707 AND entity_type = 'PROJECT'
708 CONNECT BY PRIOR process_id = parent_process_id
709 AND organization_id = PRIOR organization_id
710 AND entity_id = PRIOR entity_id
711 AND entity_type = PRIOR entity_type)
712 AND exists (select 'Y' FROM amw_risk_associations
713 WHERE object_type = 'PROJECT'
714 AND pk1 = p_audit_project_id
715 AND pk2 = p_org_id
716 AND pk3 = pk4_value
717 AND risk_id = pk1_value);
718
719 CURSOR get_total_controls IS
720 SELECT count(distinct control_id)
721 FROM amw_control_associations
722 WHERE object_type = 'PROJECT'
723 AND pk1 = p_audit_project_id
724 AND pk2 = p_org_id
725 AND pk3 IN (SELECT process_id
726 FROM amw_execution_scope
727 START WITH process_id = p_proc_id
728 AND organization_id = p_org_id
729 AND entity_id = p_audit_project_id
730 AND entity_type = 'PROJECT'
731 CONNECT BY PRIOR process_id = parent_process_id
732 AND organization_id = PRIOR organization_id
733 AND entity_id = PRIOR entity_id
734 AND entity_type = PRIOR entity_type);
735
736 CURSOR get_controls_evaluated IS
737 SELECT count(distinct opin.pk1_value)
738 FROM amw_control_associations assoc, amw_opinions_v opin
739 WHERE assoc.object_type = 'PROJECT'
740 AND assoc.pk1 = p_audit_project_id
741 AND assoc.pk2 = p_org_id
742 AND assoc.pk3 IN (SELECT process_id
743 FROM amw_execution_scope
744 START WITH process_id = p_proc_id
745 AND organization_id = p_org_id
746 AND entity_id = p_audit_project_id
747 AND entity_type = 'PROJECT'
748 CONNECT BY PRIOR process_id = parent_process_id
749 AND organization_id = PRIOR organization_id
750 AND entity_id = PRIOR entity_id
751 AND entity_type = PRIOR entity_type)
752 AND opin.opinion_type_code = 'EVALUATION'
753 AND opin.object_name = 'AMW_ORG_CONTROL'
754 AND opin.pk1_value = assoc.control_id
755 AND opin.pk2_value = p_audit_project_id
756 AND opin.pk3_value = p_org_id;
757
758 CURSOR get_ineff_controls IS
759 SELECT count(distinct opin.pk1_value)
760 FROM amw_control_associations assoc, amw_opinions_v opin
761 WHERE assoc.object_type = 'PROJECT'
762 AND assoc.pk1 = p_audit_project_id
763 AND assoc.pk2 = p_org_id
764 AND assoc.pk3 IN (SELECT process_id
765 FROM amw_execution_scope
766 START WITH process_id = p_proc_id
767 AND organization_id = p_org_id
768 AND entity_id = p_audit_project_id
769 AND entity_type = 'PROJECT'
770 CONNECT BY PRIOR process_id = parent_process_id
771 AND organization_id = PRIOR organization_id
772 AND entity_id = PRIOR entity_id
773 AND entity_type = PRIOR entity_type)
774 AND opin.opinion_type_code = 'EVALUATION'
775 AND opin.object_name = 'AMW_ORG_CONTROL'
776 AND opin.audit_result_code <> 'EFFECTIVE'
777 AND opin.pk1_value = assoc.control_id
781 l_unmitigated_risks NUMBER;
778 AND opin.pk2_value = p_audit_project_id
779 AND opin.pk3_value = p_org_id;
780
782 l_evaluated_risks NUMBER;
783 l_total_risks NUMBER;
784
785 l_ineff_controls NUMBER;
786 l_evaluated_controls NUMBER;
787 l_total_controls NUMBER;
788
789 l_open_findings NUMBER;
790
791
792 BEGIN
793
794 OPEN get_unmitigated_risks;
795 FETCH get_unmitigated_risks into l_unmitigated_risks;
796 CLOSE get_unmitigated_risks;
797
798 OPEN get_risks_evaluated;
799 FETCH get_risks_evaluated into l_evaluated_risks;
800 CLOSE get_risks_evaluated;
801
802 OPEN get_total_risks;
803 FETCH get_total_risks into l_total_risks;
804 CLOSE get_total_risks;
805
806 OPEN get_ineff_controls;
807 FETCH get_ineff_controls into l_ineff_controls;
808 CLOSE get_ineff_controls;
809
810 OPEN get_controls_evaluated;
811 FETCH get_controls_evaluated into l_evaluated_controls;
812 CLOSE get_controls_evaluated;
813
814 OPEN get_total_controls;
815 FETCH get_total_controls into l_total_controls;
816 CLOSE get_total_controls;
817
818
819 l_open_findings := amw_findings_pkg.calculate_open_findings
820 ('AMW_PROJ_FINDING',
821 'PROJ_ORG_PROC', p_proc_id,
822 'PROJ_ORG', p_org_id,
823 'PROJECT', p_audit_project_id,
824 null, null,
825 null, null);
826
827 UPDATE amw_audit_scope_processes
828 SET unmitigated_risks = l_unmitigated_risks,
829 risks_evaluated = l_evaluated_risks,
830 total_risks = l_total_risks,
831 ineffective_controls = l_ineff_controls,
832 controls_evaluated = l_evaluated_controls,
833 total_controls = l_total_controls,
834 open_findings = l_open_findings,
835 last_update_date = SYSDATE,
836 last_updated_by = G_USER_ID,
837 last_update_login = G_LOGIN_ID
838 WHERE audit_project_id = p_audit_project_id
839 AND organization_id = p_org_id
840 AND process_id = p_proc_id;
841
842 IF (SQL%NOTFOUND) THEN
843 INSERT INTO amw_audit_scope_processes (
844 audit_project_id,
845 organization_id,
846 process_id,
847 risks_evaluated,
848 unmitigated_risks,
849 total_risks,
850 controls_evaluated,
851 ineffective_controls,
852 total_controls,
853 open_findings,
854 created_by,
855 creation_date,
856 last_updated_by,
857 last_update_date,
858 last_update_login,
859 object_version_number)
860 SELECT p_audit_project_id,
861 p_org_id,
862 p_proc_id,
863 l_evaluated_risks,
864 l_unmitigated_risks,
865 l_total_risks,
866 l_evaluated_controls,
867 l_ineff_controls,
868 l_total_controls,
869 l_open_findings,
870 g_user_id,
871 sysdate,
872 g_user_id,
873 sysdate,
874 g_login_id,
875 1
876 FROM dual;
877 END IF;
878
879 EXCEPTION
880 WHEN NO_DATA_FOUND THEN
881 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in update_proc_summary_table'
882 || SUBSTR (SQLERRM, 1, 100), 1, 200));
883
884 WHEN OTHERS THEN
885 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in update_proc_summary_table'
886 || SUBSTR (SQLERRM, 1, 100), 1, 200));
887 END update_proc_summary_table;
888
889
890 PROCEDURE Synchronize_Eng_Denorm_Tables(
891 errbuf OUT NOCOPY VARCHAR2,
892 retcode OUT NOCOPY VARCHAR2,
893 p_audit_project_id IN NUMBER
894 )
895 IS
896
897 CURSOR c_engagements IS
898 Select audit_project_id
899 from AMW_AUDIT_PROJECTS
900 where AUDIT_PROJECT_STATUS = 'ACTI';
901
902 CURSOR c_scope_org(l_audit_project_id NUMBER) IS
903 SELECT organization_id
904 FROM AMW_AUDIT_SCOPE_ORGANIZATIONS
905 WHERE audit_project_id = l_audit_project_id;
906
907 CURSOR c_org_proc(l_audit_project_id NUMBER, l_org_id NUMBER) IS
908 SELECT process_id
909 FROM AMW_AUDIT_SCOPE_PROCESSES
910 WHERE audit_project_id = l_audit_project_id
911 AND organization_id = l_org_id;
912
913 eng_rec c_engagements%rowtype;
914 org_rec c_scope_org%rowtype;
915 proc_rec c_org_proc%rowtype;
916
917
918 BEGIN
919 fnd_file.put_line (fnd_file.LOG,'Audit_Project_Id :' || p_audit_project_id);
920
921 IF p_audit_project_id IS NOT NULL THEN
922 FOR org_rec IN c_scope_org(p_audit_project_id) LOOP
923 update_org_summary_table(p_audit_project_id,org_rec.organization_id);
924
925 FOR proc_rec IN c_org_proc(p_audit_project_id,org_rec.organization_id) LOOP
926 update_proc_summary_table(p_audit_project_id,org_rec.organization_id,proc_rec.process_id);
927 END LOOP;
928 END LOOP;
929 ELSE
930 FOR eng_rec IN c_engagements LOOP
931 FOR org_rec IN c_scope_org(eng_rec.audit_project_id) LOOP
932 update_org_summary_table(eng_rec.audit_project_id,org_rec.organization_id);
933
934 FOR proc_rec IN c_org_proc(eng_rec.audit_project_id,org_rec.organization_id) LOOP
935 update_proc_summary_table(eng_rec.audit_project_id,org_rec.organization_id,proc_rec.process_id);
936 END LOOP;
937 END LOOP;
938 END LOOP;
939 END IF;
940
941 COMMIT;
945 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Synchronize_Eng_Denorm_Tables'
942
943 EXCEPTION
944 WHEN OTHERS THEN
946 || SUBSTR (SQLERRM, 1, 100), 1, 200));
947 errbuf := SQLERRM;
948 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
949
950 END Synchronize_Eng_Denorm_Tables;
951
952
953 FUNCTION Update_Eng_Sign_Off_Status
954 ( p_subscription_guid in raw,
955 p_event in out NOCOPY wf_event_t
956 ) return VARCHAR2
957 IS
958
959 l_key varchar2(240) := p_event.GetEventKey();
960 l_change_id NUMBER;
961 l_approval_status_code NUMBER;
962 l_workflow_route_status varchar2(240);
963
964 l_audit_project_id NUMBER;
965 l_sign_off_status varchar2(30);
966 l_change_mgmt_type_code varchar2(30);
967
968 BEGIN
969
970
971 l_change_id := p_event.GetValueForParameter('ChangeId');
972 l_approval_status_code := p_event.GetValueForParameter('NewApprovalStatusCode');
973 l_workflow_route_status := p_event.GetValueForParameter('WorkflowRouteStatus');
974
975 IF l_change_id IS NOT NULL THEN
976 select change_mgmt_type_code into l_change_mgmt_type_code
977 from eng_engineering_changes
978 where change_id = l_change_id
979 and organization_id = -1
980 and rownum < 2;
981
982 select pk1_value into l_audit_project_id
983 from eng_change_subjects_v
984 where entity_name = 'PROJECT'
985 and change_id = l_change_id;
986 END IF;
987
988 /* Check for change_mgmt_type_code before updating status */
989 IF l_change_mgmt_type_code = 'AMW_SIGNOFF_REQUESTS' THEN
990 IF l_approval_status_code IS NOT NULL THEN
991 IF l_approval_status_code = 1 THEN
992 l_sign_off_status := 'NOT_SUBMITTED';
993 ELSIF l_approval_status_code = 3 THEN
994 l_sign_off_status := 'PENDING_APPROVAL';
995 ELSIF l_approval_status_code = 4 THEN
996 l_sign_off_status := 'REJECTED';
997 ELSIF l_approval_status_code = 5 THEN
998 l_sign_off_status := 'APPROVED';
999 ELSE
1000 l_sign_off_status := 'NOT_COMPLETED';
1001 END IF;
1002 END IF;
1003
1004
1005 IF l_audit_project_id IS NOT NULL THEN
1006
1007 /* Update the signOffStatus. */
1008 UPDATE AMW_AUDIT_PROJECTS
1009 SET sign_off_status = l_sign_off_status
1010 WHERE AUDIT_PROJECT_ID = l_audit_project_id;
1011
1012 /* update the Engagement status */
1013 IF l_sign_off_status = 'APPROVED' THEN
1014 UPDATE AMW_AUDIT_PROJECTS
1015 SET audit_project_status = 'SIGN'
1016 WHERE AUDIT_PROJECT_ID = l_audit_project_id
1017 AND AUDIT_PROJECT_STATUS = 'ACTI';
1018 END IF;
1019
1020 END IF;
1021 END IF; -- end of l_change_mgmt_type_code
1022
1023 return 'SUCCESS';
1024
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 WF_CORE.CONTEXT('AMW_PROJECT_EVENT_PVT', 'Update_Eng_Sign_Off_Status', p_event.getEventName(), p_subscription_guid);
1028 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1029 RETURN 'ERROR';
1030
1031 END Update_Eng_Sign_Off_Status;
1032
1033
1034 procedure UPDATE_SIGNOFF_STATUS(
1035 p_change_id in number
1036 ,p_base_change_mgmt_type_code in varchar2
1037 ,p_new_approval_status_code in varchar2
1038 ,p_workflow_status_code in varchar2
1039 ,x_return_status out nocopy varchar2
1040 ,x_msg_count out nocopy number
1041 ,x_msg_data out nocopy varchar2
1042 )
1043 is
1044
1045 l_audit_project_id NUMBER;
1046 l_sign_off_status varchar2(30);
1047
1048 begin
1049 x_return_status := fnd_api.g_ret_sts_success;
1050
1051
1052 IF p_change_id IS NOT NULL THEN
1053 select pk1_value into l_audit_project_id
1054 from eng_change_subjects_v
1055 where entity_name = 'PROJECT'
1056 and change_id = p_change_id;
1057 END IF;
1058
1059 IF p_new_approval_status_code IS NOT NULL THEN
1060 IF p_new_approval_status_code = 1 THEN
1061 l_sign_off_status := 'NOT_SUBMITTED';
1062 ELSIF p_new_approval_status_code = 3 THEN
1063 l_sign_off_status := 'PENDING_APPROVAL';
1064 ELSIF p_new_approval_status_code = 4 THEN
1065 l_sign_off_status := 'REJECTED';
1066 ELSIF p_new_approval_status_code = 5 THEN
1067 l_sign_off_status := 'APPROVED';
1068 ELSE
1069 l_sign_off_status := 'NOT_COMPLETED';
1070 END IF;
1071 END IF;
1072
1073
1074 IF l_audit_project_id IS NOT NULL THEN
1075
1076 /* Update the signOffStatus. */
1077 UPDATE AMW_AUDIT_PROJECTS
1078 SET sign_off_status = l_sign_off_status
1079 WHERE AUDIT_PROJECT_ID = l_audit_project_id;
1080
1081 /* update the Engagement status */
1082 IF l_sign_off_status = 'APPROVED' THEN
1083 UPDATE AMW_AUDIT_PROJECTS
1084 SET audit_project_status = 'SIGN'
1085 WHERE AUDIT_PROJECT_ID = l_audit_project_id
1086 AND AUDIT_PROJECT_STATUS = 'ACTI';
1087 END IF;
1088
1089 END IF;
1090
1091
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 x_return_status := fnd_api.g_ret_sts_unexp_error;
1095 -- Standard call to get message count and if count=1, get the message
1096 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1097 p_count => x_msg_count,
1098 p_data => x_msg_data);
1099 end UPDATE_SIGNOFF_STATUS;
1100
1101
1102 END AMW_PROJECT_EVENT_PVT;
1103
1104
1105