[Home] [Help]
PACKAGE BODY: APPS.AMW_SCOPE_PVT
Source
1 PACKAGE BODY AMW_SCOPE_PVT AS
2 /* $Header: amwvscpb.pls 120.15 2008/02/08 14:26:09 adhulipa ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_SCOPE_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 g_pkg_name CONSTANT VARCHAR2 (30) := 'AMW_SCOPE_PVT';
16 g_file_name CONSTANT VARCHAR2 (12) := 'amwvscpb.pls';
17 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
18 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
19
20 PROCEDURE raise_scope_update_event(
21 p_entity_type IN VARCHAR2,
22 p_entity_id IN NUMBER,
23 p_org_id IN NUMBER := NULL,
24 p_mode IN VARCHAR2)
25 IS
26 l_item_key wf_items.ITEM_KEY%TYPE;
27 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
28 BEGIN
29
30 SELECT to_char(amw_scope_event_s.nextval)
31 INTO l_item_key
32 FROM dual;
33
34 wf_event.addParameterToList(
35 p_name => 'MODE',
36 p_value => p_mode,
37 p_parameterlist => l_parameter_list);
38
39 wf_event.addParameterToList(
40 p_name => 'ORGANIZATION_ID',
41 p_value => p_org_id,
42 p_parameterlist => l_parameter_list);
43
44
45 IF p_entity_type = 'BUSIPROC_CERTIFICATION' THEN
46 wf_event.addParameterToList(
47 p_name => 'CERTIFICATION_ID',
48 p_value => p_entity_id,
49 p_parameterlist => l_parameter_list);
50 wf_event.raise(
51 p_event_name => 'oracle.apps.amw.proccert.scope.update',
52 p_event_key => l_item_key,
53 p_parameters => l_parameter_list);
54 ELSIF p_entity_type = 'PROJECT' THEN
55 wf_event.addParameterToList(
56 p_name => 'AUDIT_PROJECT_ID',
57 p_value => p_entity_id,
58 p_parameterlist => l_parameter_list);
59 wf_event.raise(
60 p_event_name => 'oracle.apps.amw.engagement.scope.update',
61 p_event_key => l_item_key,
62 p_parameters => l_parameter_list);
63 END IF;
64
65 END raise_scope_update_event;
66
67 PROCEDURE add_scope
68 (
69 p_api_version_number IN NUMBER := 1.0,
70 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
71 p_commit IN VARCHAR2 := FND_API.g_false,
72 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
73 p_entity_id IN NUMBER,
74 p_entity_type IN VARCHAR2,
75 p_sub_vs IN VARCHAR2,
76 p_lob_vs IN VARCHAR2,
77 p_subsidiary_tbl IN SUB_TBL_TYPE,
78 p_lob_tbl IN LOB_TBL_TYPE,
79 p_org_tbl IN ORG_TBL_TYPE,
80 p_process_tbl IN PROCESS_TBL_TYPE,
81 x_return_status OUT nocopy VARCHAR2,
82 x_msg_count OUT nocopy NUMBER,
83 x_msg_data OUT nocopy VARCHAR2
84 )
85 IS
86
87 l_temp_org_id NUMBER;
88 l_temp_parent_id NUMBER;
89 l_temp_new_parent_id NUMBER;
90 l_org_exists VARCHAR2(1);
91
92 found_parent BOOLEAN;
93 found_sub_parent BOOLEAN;
94 found_org_parent BOOLEAN;
95 is_root_node BOOLEAN;
96
97 l_sub_exists VARCHAR2(1);
98 l_sub_code amw_audit_units_v.company_code%TYPE;
99 l_temp_sub_id NUMBER;
100 l_temp_sub_parent_id NUMBER;
101 l_sub_recursive_parent NUMBER;
102
103 l_lob_exists VARCHAR2(1);
104 l_lob_code amw_audit_units_v.lob_code%TYPE;
105 l_lob_recursive_parent NUMBER;
106 l_temp_lob_id NUMBER;
107 l_temp_lob_parent_id NUMBER;
108 found_lob_parent BOOLEAN;
109
110 l_temp_id NUMBER;
111 hier_name VARCHAR2(32767);
112
113 p_org_new_tbl org_tbl_type;
114 p_sub_new_tbl sub_new_tbl_type;
115 p_lob_new_tbl lob_new_tbl_type;
116
117 l_api_name CONSTANT VARCHAR2(30) := 'add_scope';
118 l_api_version_number CONSTANT NUMBER := 1.0;
119
120 l_return_status VARCHAR2(32767);
121 l_msg_count NUMBER;
122 l_msg_data VARCHAR2(32767);
123
124 CURSOR find_parent_subsidiary(l_organization_id NUMBER) IS
125 SELECT flv.flex_value_id
126 FROM amw_audit_units_v auv,fnd_flex_values flv
127 WHERE auv.subsidiary_valueset = flv.flex_value_set_id
128 AND auv.company_code = flv.flex_value
129 AND organization_id = l_organization_id;
130
131 CURSOR find_parent_lob(l_organization_id NUMBER) IS
132 SELECT flv.flex_value_id
133 FROM amw_audit_units_v auv,fnd_flex_values flv
134 WHERE auv.lob_valueset = flv.flex_value_set_id
135 AND auv.lob_code = flv.flex_value
136 AND organization_id = l_organization_id;
137
138 CURSOR check_lob_exists (l_organization_id NUMBER) IS
139 SELECT 'Y'
140 FROM AMW_AUDIT_UNITS_V
141 --WHERE LOB_valueset = p_LOB_vs
142 WHERE LOB_valueset IS NOT NULL
143 AND organization_id = l_organization_id;
144
145 CURSOR find_parent_recursive (l_child_id NUMBER) IS
146 SELECT nvl( flv2.flex_value_id, -1) parent_id
147 FROM fnd_flex_values_vl flv, FND_FLEX_VALUE_CHILDREN_V fchild,fnd_flex_values_vl flv2
148 WHERE flv.flex_value = fchild.flex_value (+)
149 AND flv.flex_value_set_id = fchild.flex_value_set_id (+)
150 AND flv2.flex_value(+) = fchild.parent_flex_value
151 AND flv2.flex_value_set_id(+) = fchild.flex_value_set_id
152 AND flv.flex_value_id = l_child_id;
153
154 CURSOR get_existing_objects (l_object_type VARCHAR2) IS
155 SELECT distinct object_id
156 FROM AMW_ENTITY_HIERARCHIES
157 WHERE entity_type = p_entity_type
158 AND entity_id = p_entity_id
159 AND delete_flag = 'Y'
160 AND object_type = l_object_type;
161
162 BEGIN
163
164 hier_name := fnd_profile.value('AMW_ORG_SECURITY_HIERARCHY');
165 --hier_name := 'SUHierarchy';
166 SAVEPOINT POPULATE_HIERARCHIES;
167
168 -- Standard call to check for call compatibility.
169 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
170 p_api_version_number,
171 l_api_name,
172 G_PKG_NAME)
173 THEN
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 END IF;
176
177 -- Initialize message list if p_init_msg_list is set to TRUE.
178 IF FND_API.to_Boolean( p_init_msg_list )
179 THEN
180 FND_MSG_PUB.initialize;
181 END IF;
182
183 -- Initialize API return status to SUCCESS
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185
186 -- Whenever there is a change to hierarchy, the system will delete the existing hierarchy and rebuild it.
187 -- To begin with mark all records in the table as DELETED
188 UPDATE AMW_ENTITY_HIERARCHIES
189 SET DELETE_FLAG = 'Y'
190 WHERE ENTITY_ID = p_entity_id
191 AND ENTITY_TYPE = p_entity_type;
192
193 -- Create a new list of organizations
194 -- The new list is existing orgs UNION newly selected orgs
195 generate_organization_list
196 (p_entity_id => p_entity_id,
197 p_entity_type => p_entity_type,
198 p_org_tbl => p_org_tbl,
199 p_org_new_tbl => p_org_new_tbl);
200
201 FOR each_rec IN 1..p_org_tbl.count
202 loop
203 delete from amw_execution_scope where entity_type = p_entity_type
204 and entity_id = p_entity_id and organization_id = p_org_tbl(each_rec).org_id;
205 end loop;
206
207 -- Create a new list of subsidiaries
208 -- Based on the list of organizations, determine only those subsidiaries that have organizations
209 -- with auditable units and are in the hierarchy, mapped to the subsidiaries and
210 -- add object_id into p_subsidiary_tbl
211 generate_subsidiary_list
212 (p_entity_id => p_entity_id,
213 p_entity_type => p_entity_type,
214 p_org_new_tbl => p_org_new_tbl,
215 p_subsidiary_tbl => p_subsidiary_tbl,
216 p_sub_vs => p_sub_vs,
217 p_sub_new_tbl => p_sub_new_tbl);
218
219 -- Create a new list of LOBs
220 -- Based on the list of organizations, determine only those LOBs that have organizations
221 -- with auditable units and are in the hierarchy, mapped to the LOBs and
222 -- add object_id into p_lob_tbl;
223 generate_lob_list
224 (p_entity_id => p_entity_id,
225 p_entity_type => p_entity_type,
226 p_org_new_tbl => p_org_new_tbl,
227 p_subsidiary_tbl => p_subsidiary_tbl,
228 p_sub_vs => p_sub_vs,
229 p_lob_tbl => p_lob_tbl,
230 p_lob_vs => p_lob_vs,
231 p_lob_new_tbl => p_lob_new_tbl);
232
233 --Populate Legal Hierarchy/Management Hierarchy/Custom Hierarchy
234 --Step 1 : Find all relevant organizations
235 --Step 2 : Find all parents(subsidiaries) of such organizations from Step 1
236 --Step 3 : Populate table with object_type = ORGANIZATION and parent_object_type = SUBSIDIARY
237 --Step 4 : Find all parents(LOBs) of such organizations from Step 1
238 --Step 5 : Populate table with object_type = ORGANIZATION and parent_object_type = LINEOFBUSINESS
239 --Step 6 : Find parents of Subsidiaries all the way till the root node
240 --Step 7 : Populate table with Subsidiary hierarchy
241 --Step 8 : Find parents of LOBs all the way till the root node
242 --Step 9 : Populate table with LOB hierarchy
243 --Step 10: Find CustomORGs based on HR hierarchy all the way till the root node
244 --Step 11: Populate selected CustomORGS into table and build hierarchy
245 --Step 12: Call to populate amw_execution_scope with processes/org relation
246 --Step 13: Call to populate association tables with object_type = 'BUSIPROC_CERTIFICATION'
247
248 --Step 1 : Find all relevant organizations
249 -- loop through all organizations in the list
250 FOR each_rec IN 1..p_org_new_tbl.count LOOP
251
252 found_sub_parent := false;
253 is_root_node := false;
254
255 --Step 2 : Find all parents(subsidiaries) of such organizations from Step 1
256 OPEN find_parent_subsidiary(p_org_new_tbl(each_rec).org_id);
257 FETCH find_parent_subsidiary INTO l_temp_sub_parent_id;
258 CLOSE find_parent_subsidiary;
259
260 --Step 3(Condition A): Populate table with object_type = ORGANIZATION and parent_object_type = SUBSIDIARY
261 INSERT INTO AMW_ENTITY_HIERARCHIES
262 (ENTITY_HIERARCHY_ID,
263 ENTITY_TYPE,
264 ENTITY_ID,
265 CREATED_BY,
266 CREATION_DATE,
267 LAST_UPDATE_DATE,
268 LAST_UPDATED_BY,
269 LAST_UPDATE_LOGIN,
270 OBJECT_TYPE,
271 OBJECT_ID,
272 PARENT_OBJECT_TYPE,
273 PARENT_OBJECT_ID,
274 LEVEL_ID)
275 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
276 p_entity_type,
277 p_entity_id,
278 FND_GLOBAL.USER_ID,
279 SYSDATE,
280 SYSDATE,
281 FND_GLOBAL.USER_ID,
282 FND_GLOBAL.USER_ID,
283 'ORGANIZATION',
284 p_org_new_tbl(each_rec).org_id,
285 'SUBSIDIARY',
286 l_temp_sub_parent_id,
287 1
288 FROM dual;
289
290 l_lob_exists := 'N';
291 found_parent := false;
292
293 OPEN check_lob_exists (p_org_new_tbl(each_rec).org_id);
294 FETCH check_lob_exists INTO l_lob_exists;
295 CLOSE check_lob_exists;
296
297 IF ((l_lob_exists IS NOT NULL) AND (l_lob_exists ='Y'))
298 THEN
299 --Step 4 : Find all parents(LOBs) of such organizations from Step 1
300 OPEN find_parent_lob(p_org_new_tbl(each_rec).org_id);
301 FETCH find_parent_lob INTO l_temp_parent_id;
302 CLOSE find_parent_lob;
303
304 --Step 5(Condition A) : Populate table with object_type = ORGANIZATION and parent_object_type = LINEOFBUSINESS
305 INSERT INTO AMW_ENTITY_HIERARCHIES
306 (ENTITY_HIERARCHY_ID,
307 ENTITY_TYPE,
308 ENTITY_ID,
309 CREATED_BY,
310 CREATION_DATE,
311 LAST_UPDATE_DATE,
312 LAST_UPDATED_BY,
313 LAST_UPDATE_LOGIN,
314 OBJECT_TYPE,
315 OBJECT_ID,
316 PARENT_OBJECT_TYPE,
317 PARENT_OBJECT_ID,
318 LEVEL_ID )
319 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
320 p_entity_type,
321 p_entity_id,
322 FND_GLOBAL.USER_ID,
323 SYSDATE,
324 SYSDATE,
325 FND_GLOBAL.USER_ID,
326 FND_GLOBAL.USER_ID,
327 'ORGANIZATION',
328 p_org_new_tbl(each_rec).org_id,
329 'LINEOFBUSINESS',
330 l_temp_parent_id,
331 1
332 FROM dual;
333
334 ELSE
335
336 --Step 5(Condition B) : Populate table with object_type = ORGANIZATION and parent_object_type = DUMMYLOB
337 INSERT INTO AMW_ENTITY_HIERARCHIES
338 (ENTITY_HIERARCHY_ID,
339 ENTITY_TYPE,
340 ENTITY_ID,
341 CREATED_BY,
342 CREATION_DATE,
343 LAST_UPDATE_DATE,
344 LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN,
346 OBJECT_TYPE,
347 OBJECT_ID,
348 PARENT_OBJECT_TYPE,
349 PARENT_OBJECT_ID,
350 LEVEL_ID )
351 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
352 p_entity_type,
353 p_entity_id,
354 FND_GLOBAL.USER_ID,
355 SYSDATE,
356 SYSDATE,
357 FND_GLOBAL.USER_ID,
358 FND_GLOBAL.USER_ID,
359 'ORGANIZATION',
360 p_org_new_tbl(each_rec).org_id,
361 'DUMMYLOB',
362 -999,
363 1
364 FROM dual;
365 END IF;
366
367 END LOOP; -- end of FOR each_rec IN p_org_new_tbl
368
369 --Step 6 : Find parents of Subsidiaries all the way till the root node
370 -- loop through all subsidiaries in the list and populate subsidiary hierarchy
371 FOR each_sub IN 1..p_sub_new_tbl.count LOOP
372
373 l_temp_sub_id := p_sub_new_tbl(each_sub).subsidiary_id;
374 l_temp_sub_parent_id := -1;
375 found_sub_parent := false;
376 is_root_node := false;
377
378 <<OUTERLOOP>>
379 WHILE ((found_sub_parent=false) AND
380 (is_root_node=false))
381 LOOP
382 OPEN find_parent_recursive(l_temp_sub_id);
383 FETCH find_parent_recursive INTO l_temp_sub_parent_id;
384 CLOSE find_parent_recursive;
385
386 IF l_temp_sub_parent_id = -1
387 THEN
388 is_root_node := true;
389 EXIT OUTERLOOP;
390 ELSE
391
392 <<INNERLOOP>>
393 FOR i IN 1..p_sub_new_tbl.count LOOP
394
395 IF (l_temp_sub_parent_id = p_sub_new_tbl(i).subsidiary_id)
396 THEN
397 found_sub_parent := true;
398 EXIT INNERLOOP;
399 END IF;
400 END LOOP INNERLOOP;
401
402 IF found_sub_parent = false
403 THEN
404 l_temp_sub_id := l_temp_sub_parent_id;
405 END IF;
406 END IF;
407
408 END LOOP OUTERLOOP;
409
410 IF found_sub_parent = true
411 THEN
412
413 --Step 7 : Populate table with Subsidiary hierarchy
414 INSERT INTO AMW_ENTITY_HIERARCHIES
415 (ENTITY_HIERARCHY_ID,
416 ENTITY_TYPE,
417 ENTITY_ID,
418 CREATED_BY,
419 CREATION_DATE,
420 LAST_UPDATE_DATE,
421 LAST_UPDATED_BY,
422 LAST_UPDATE_LOGIN,
423 OBJECT_TYPE,
424 OBJECT_ID,
425 PARENT_OBJECT_TYPE,
426 PARENT_OBJECT_ID,
427 LEVEL_ID )
428 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
429 p_entity_type,
430 p_entity_id,
431 FND_GLOBAL.USER_ID,
432 SYSDATE,
433 SYSDATE,
434 FND_GLOBAL.USER_ID,
435 FND_GLOBAL.USER_ID,
436 'SUBSIDIARY',
437 p_sub_new_tbl(each_sub).subsidiary_id,
438 'SUBSIDIARY',
439 l_temp_sub_parent_id,
440 1
441 FROM dual;
442 ELSE
443
444 --Step 7 : Populate table with Subsidiary hierarchy
445 INSERT INTO AMW_ENTITY_HIERARCHIES
446 (ENTITY_HIERARCHY_ID,
447 ENTITY_TYPE,
448 ENTITY_ID,
449 CREATED_BY,
450 CREATION_DATE,
451 LAST_UPDATE_DATE,
452 LAST_UPDATED_BY,
453 LAST_UPDATE_LOGIN,
454 OBJECT_TYPE,
455 OBJECT_ID,
456 PARENT_OBJECT_TYPE,
457 PARENT_OBJECT_ID,
458 LEVEL_ID )
459 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
460 p_entity_type,
461 p_entity_id,
462 FND_GLOBAL.USER_ID,
463 SYSDATE,
464 SYSDATE,
465 FND_GLOBAL.USER_ID,
466 FND_GLOBAL.USER_ID,
467 'SUBSIDIARY',
468 p_sub_new_tbl(each_sub).subsidiary_id,
469 'ROOTNODE',
470 -1,
471 1
472 FROM dual;
473
474 END IF; --found_sub_parent = true = false
475
476 END LOOP;-- end of FOR each_sub IN 1..p_sub_new_tbl
477
478 --Step 8 : Find parents of LOBs all the way till the root node
479 -- loop through all LOBs in the list and populate lob hierarchy
480 FOR each_lob IN 1..p_lob_new_tbl.count LOOP
481
482 l_temp_lob_id := p_lob_new_tbl(each_lob).lob_id;
483 l_temp_lob_parent_id := -1;
484 found_lob_parent := false;
485 is_root_node := false;
486
487 <<MAINLOOP>>
488 WHILE ((found_lob_parent=false) AND
489 (is_root_node=false))
490 LOOP
491 OPEN find_parent_recursive(l_temp_lob_id);
492 FETCH find_parent_recursive INTO l_temp_lob_parent_id;
493 CLOSE find_parent_recursive;
494
495 IF l_temp_lob_parent_id = -1
496 THEN
497 is_root_node := true;
498 EXIT MAINLOOP;
499 ELSE
500
501 <<OTHERLOOP>>
502 FOR i IN 1..p_lob_new_tbl.count
503 LOOP
504 IF l_temp_lob_parent_id = p_lob_new_tbl(i).lob_id
505 THEN
506 found_lob_parent := true;
507 EXIT OTHERLOOP;
508 END IF;
509 END LOOP OTHERLOOP;
510
511 IF found_lob_parent = false
512 THEN
513 l_temp_lob_id := l_temp_lob_parent_id;
514 END IF;
515 END IF;
516
517 END LOOP MAINLOOP;
518
519 IF found_lob_parent = true
520 THEN
521
522 --Step 9 : Populate table with LOB hierarchy
523 INSERT INTO AMW_ENTITY_HIERARCHIES
524 (ENTITY_HIERARCHY_ID,
525 ENTITY_TYPE,
526 ENTITY_ID,
527 CREATED_BY,
528 CREATION_DATE,
529 LAST_UPDATE_DATE,
530 LAST_UPDATED_BY,
531 LAST_UPDATE_LOGIN,
532 OBJECT_TYPE,
533 OBJECT_ID,
534 PARENT_OBJECT_TYPE,
535 PARENT_OBJECT_ID,
536 LEVEL_ID )
537 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
538 p_entity_type,
539 p_entity_id,
540 FND_GLOBAL.USER_ID,
541 SYSDATE,
542 SYSDATE,
543 FND_GLOBAL.USER_ID,
544 FND_GLOBAL.USER_ID,
545 'LINEOFBUSINESS',
546 p_lob_new_tbl(each_lob).lob_id,
547 'LINEOFBUSINESS',
548 l_temp_lob_parent_id,
549 1
550 FROM dual;
551 ELSE
552
553 --Step 9 : Populate table with LOB hierarchy
554 INSERT INTO AMW_ENTITY_HIERARCHIES
555 (ENTITY_HIERARCHY_ID,
556 ENTITY_TYPE,
557 ENTITY_ID,
558 CREATED_BY,
559 CREATION_DATE,
560 LAST_UPDATE_DATE,
561 LAST_UPDATED_BY,
562 LAST_UPDATE_LOGIN,
563 OBJECT_TYPE,
564 OBJECT_ID,
565 PARENT_OBJECT_TYPE,
566 PARENT_OBJECT_ID,
567 LEVEL_ID )
568 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
569 p_entity_type,
570 p_entity_id,
571 FND_GLOBAL.USER_ID,
572 SYSDATE,
573 SYSDATE,
574 FND_GLOBAL.USER_ID,
575 FND_GLOBAL.USER_ID,
576 'LINEOFBUSINESS',
577 p_lob_new_tbl(each_lob).lob_id,
578 'ROOTNODE',
579 -1,
580 1
581 FROM dual;
582
583 END IF; --found_lob_parent = true
584
585 END LOOP;-- end of FOR each_lob IN 1..p_lob_new_tbl
586
587 --Step 11: Populate selected CustomORGS into table and build hierarchy
588 --Note that CUSTOM ORGS will be stored with OBJECT TYPE as 'ORG'
589 populate_custom_hierarchy
590 (
591 p_org_tbl => p_org_new_tbl,
592 p_entity_id => p_entity_id,
593 p_entity_type => p_entity_type
594 );
595
596 -- the following "if" is added to make this procedure
597 -- reusable in finstmt_cert.
598 IF p_entity_type <> 'FINSTMT_CERTIFICATION' THEN
599 --Step 12: Call to populate amw_execution_scope with processes information
600 populate_process_hierarchy
601 (
602 p_entity_type => p_entity_type,
603 p_entity_id => p_entity_id,
604 p_org_tbl => p_org_tbl,
605 p_process_tbl => p_process_tbl,
606 x_return_status => l_return_status,
607 x_msg_count => l_msg_count,
608 x_msg_data => l_msg_data
609 );
610
611 --Step 13: Call to populate denormalized tables and association tables
612 IF p_entity_type = 'PROJECT'
613 THEN
614 populate_proj_denorm_tables
615 (
616 p_audit_project_id => p_entity_id
617 );
618
619 build_project_audit_task
620 (
621 p_api_version_number => 1.0 ,
622 p_audit_project_id => p_entity_id,
623 x_return_status => l_return_status,
624 x_msg_count => l_msg_count,
625 x_msg_data => l_msg_data
626 );
627
628 ELSIF p_entity_type = 'BUSIPROC_CERTIFICATION'
629 THEN
630 populate_denormalized_tables
631 (
632 p_entity_type => p_entity_type,
633 p_entity_id => p_entity_id,
634 p_org_tbl => p_org_new_tbl,
635 p_process_tbl => p_process_tbl,
636 p_mode => 'ADD'
637 );
638
639 populate_association_tables
640 (
641 p_entity_type => p_entity_type,
642 p_entity_id => p_entity_id,
643 x_return_status => l_return_status,
644 x_msg_count => l_msg_count,
645 x_msg_data => l_msg_data
646 );
647
648 END IF;
649
650 --Step 14: Finally remove all the old entries from the table
651 DELETE FROM AMW_ENTITY_HIERARCHIES
652 WHERE DELETE_FLAG = 'Y'
653 AND ENTITY_ID = p_entity_id
654 AND ENTITY_TYPE = p_entity_type;
655
656 raise_scope_update_event(
657 p_entity_type => p_entity_type,
658 p_entity_id => p_entity_id,
659 p_mode => 'AddToScope');
660
661 END IF;
662
663 EXCEPTION WHEN OTHERS THEN
664 ROLLBACK TO POPULATE_HIERARCHIES;
665 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
666
667 FND_MSG_PUB.Add_Exc_Msg('AMW_SCOPE_PVT', 'add_scope');
668 FND_MSG_PUB.Count_And_Get(
669 p_encoded => FND_API.G_FALSE,
670 p_count => x_msg_count,
671 p_data => x_msg_data);
672
673
674 END add_scope;
675
676 PROCEDURE populate_custom_hierarchy
677 (
678 p_org_tbl IN ORG_TBL_TYPE,
679 p_entity_id IN NUMBER,
680 p_entity_type IN VARCHAR2
681 )
682 IS
683
684 l_temp_id NUMBER;
685 l_temp_parent_id NUMBER;
686
687 found_parent BOOLEAN;
688 is_root_org BOOLEAN;
689 hier_name VARCHAR2(32767);
690
691 CURSOR find_parent(l_organization_id NUMBER, hiername VARCHAR2) IS
692 SELECT organization_id_parent
693 FROM per_org_structure_elements
694 WHERE org_structure_version_id =(SELECT org_structure_version_id
695 FROM per_org_structure_versions
696 WHERE organization_structure_id =(SELECT organization_structure_id
697 FROM per_organization_structures
698 WHERE name = hiername)
699 and date_to is null)
700 AND organization_id_child = l_organization_id;
701
702 BEGIN
703
704 hier_name := fnd_profile.value('AMW_ORG_SECURITY_HIERARCHY');
705 --hier_name := 'SUHierarchy';
706
707 -- Step 10: Find CustomORGs based on HR hierarchy all the way till the root node
708 -- loop through all organizations in the list
709 FOR each_rec IN 1..p_org_tbl.count LOOP
710
711 l_temp_id := p_org_tbl(each_rec).org_id;
712 l_temp_parent_id := -9999;
713 found_parent := false;
714 is_root_org := false;
715
716 <<CHILDLOOP>>
717 WHILE ((found_parent=false) AND
718 (is_root_org=false)
719 )
720 LOOP
721
722 OPEN find_parent(l_temp_id, hier_name);
723 FETCH find_parent INTO l_temp_parent_id;
724 IF find_parent%NOTFOUND
725 THEN
726 is_root_org := true;
727 CLOSE find_parent;
728 EXIT CHILDLOOP;
729 END IF;
730 CLOSE find_parent;
731
732
733 <<CHILDLOOP2>>
734 FOR i IN 1..p_org_tbl.count
735 LOOP
736 IF l_temp_parent_id = p_org_tbl(i).org_id
737 THEN
738 found_parent := true;
739 EXIT CHILDLOOP2;
740 END IF;
741 END LOOP CHILDLOOP2;
742
743 IF found_parent = false
744 THEN
745 l_temp_id := l_temp_parent_id;
746 END IF;
747
748 END LOOP CHILDLOOP;
749
750 IF found_parent = TRUE
751 THEN
752
753 --Step 11: Populate selected CustomORGS into table and build hierarchy
754 INSERT INTO AMW_ENTITY_HIERARCHIES
755 (ENTITY_HIERARCHY_ID,
756 ENTITY_TYPE,
757 ENTITY_ID,
758 CREATED_BY,
759 CREATION_DATE,
760 LAST_UPDATE_DATE,
761 LAST_UPDATED_BY,
762 LAST_UPDATE_LOGIN,
763 OBJECT_TYPE,
764 OBJECT_ID,
765 PARENT_OBJECT_TYPE,
766 PARENT_OBJECT_ID,
767 LEVEL_ID )
768 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
769 p_entity_type,
770 p_entity_id,
771 FND_GLOBAL.USER_ID,
772 SYSDATE,
773 SYSDATE,
774 FND_GLOBAL.USER_ID,
775 FND_GLOBAL.USER_ID,
776 'ORG',
777 p_org_tbl(each_rec).org_id,
778 'ORG',
779 l_temp_parent_id,
780 1
781 FROM dual;
782
783 ELSE
784 --Step 11: Populate selected CustomORGS into table and build hierarchy
785 INSERT INTO AMW_ENTITY_HIERARCHIES
786 (ENTITY_HIERARCHY_ID,
787 ENTITY_TYPE,
788 ENTITY_ID,
789 CREATED_BY,
790 CREATION_DATE,
791 LAST_UPDATE_DATE,
792 LAST_UPDATED_BY,
793 LAST_UPDATE_LOGIN,
794 OBJECT_TYPE,
795 OBJECT_ID,
796 PARENT_OBJECT_TYPE,
797 PARENT_OBJECT_ID,
798 LEVEL_ID )
799 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
800 p_entity_type,
801 p_entity_id,
802 FND_GLOBAL.USER_ID,
803 SYSDATE,
804 SYSDATE,
805 FND_GLOBAL.USER_ID,
806 FND_GLOBAL.USER_ID,
807 'ORG',
808 p_org_tbl(each_rec).org_id,
809 'ROOTNODE',
810 -1,
811 1
812 FROM dual;
813 END IF;
814
815 END LOOP;
816
817 END populate_custom_hierarchy;
818
819 PROCEDURE generate_organization_list
820 (
821 p_entity_id IN NUMBER,
822 p_entity_type IN VARCHAR2,
823 p_org_tbl IN ORG_TBL_TYPE,
824 p_org_new_tbl OUT nocopy ORG_TBL_TYPE
825 )
826 IS
827
828 CURSOR get_existing_objects (l_object_type VARCHAR2) IS
829 SELECT distinct object_id
830 FROM AMW_ENTITY_HIERARCHIES
831 WHERE entity_type = p_entity_type
832 AND entity_id = p_entity_id
833 AND delete_flag = 'Y'
834 AND object_type = l_object_type;
835
836 org_exists BOOLEAN;
837 l_position NUMBER;
838
839 BEGIN
840
841 l_position := 1;
842
843 FOR each_org IN get_existing_objects('ORGANIZATION')
844 LOOP
845 EXIT WHEN get_existing_objects%NOTFOUND;
846 org_exists := false;
847
848 <<INLOOP>>
849 FOR i IN 1..p_org_tbl.count LOOP
850 IF (each_org.object_id = p_org_tbl(i).org_id)
851 THEN
852 org_exists := true;
853 EXIT INLOOP;
854 END IF;
855 END LOOP INLOOP;
856
857 IF org_exists = false
858 THEN
859 p_org_new_tbl(l_position).org_id := each_org.object_id;
860 l_position := l_position + 1;
861 END IF;
862 END LOOP;
863
864 FOR i IN 1..p_org_tbl.count LOOP
865 p_org_new_tbl(l_position).org_id := p_org_tbl(i).org_id;
866 l_position := l_position + 1;
867 END LOOP;
868
869 END generate_organization_list;
870
871 PROCEDURE generate_subsidiary_list
872 (
873 p_entity_id IN NUMBER,
874 p_entity_type IN VARCHAR2,
875 p_org_new_tbl IN ORG_TBL_TYPE,
876 p_subsidiary_tbl IN sub_tbl_type,
877 p_sub_vs IN VARCHAR2,
878 p_sub_new_tbl OUT nocopy sub_new_tbl_type
879 )
880 IS
881
882 l_get_subsidiary_query VARCHAR2(32767) :=
883 'SELECT DISTINCT flv.flex_value_id
884 FROM amw_audit_units_v auv,fnd_flex_values flv
885 WHERE auv.subsidiary_valueset = flv.flex_value_set_id
886 AND auv.company_code = flv.flex_value';
887
888
889 l_extra_query VARCHAR2(32767);
890 l_final_query VARCHAR2(32767);
891 l_subsidiary_id NUMBER;
892 l_position NUMBER;
893
894 hier_name VARCHAR2(32767);
895
896 TYPE subcurtype IS REF CURSOR;
897 subs_cursor subcurtype;
898
899 BEGIN
900 l_position := 1;
901
902 IF(p_org_new_tbl.count > 0)
903 THEN
904 l_extra_query := ' AND organization_id IN ( ';
905 END IF;
906
907 FOR i IN 1..p_org_new_tbl.count
908 LOOP
909 l_extra_query := l_extra_query || p_org_new_tbl(i).org_id;
910 IF (i = p_org_new_tbl.count)
911 THEN
912 l_extra_query := l_extra_query || ' )';
913 ELSE
914 l_extra_query := l_extra_query || ', ';
915 END IF;
916 END LOOP;
917
918 l_final_query := l_get_subsidiary_query || l_extra_query;
919
920 OPEN subs_cursor FOR l_final_query;
921 LOOP
922 FETCH subs_cursor INTO l_subsidiary_id;
923 EXIT WHEN subs_cursor%NOTFOUND;
924 p_sub_new_tbl(l_position).subsidiary_id := l_subsidiary_id;
925 l_position := l_position + 1;
926 END LOOP;
927 CLOSE subs_cursor;
928
929 END generate_subsidiary_list;
930
931 PROCEDURE generate_lob_list
932 (
933 p_entity_id IN NUMBER,
934 p_entity_type IN VARCHAR2,
935 p_org_new_tbl IN ORG_TBL_TYPE,
936 p_subsidiary_tbl IN sub_tbl_type,
937 p_sub_vs IN VARCHAR2,
938 p_lob_tbl IN lob_tbl_type,
939 p_lob_vs IN VARCHAR2,
940 p_lob_new_tbl OUT nocopy lob_new_tbl_type
941 )
942 IS
943
944 TYPE lobcurtype IS REF CURSOR;
945 lobs_cursor lobcurtype;
946
947 l_get_lob_query VARCHAR2(32767) :=
948 'SELECT DISTINCT flv.flex_value_id
949 FROM amw_audit_units_v auv,fnd_flex_values flv
950 WHERE auv.lob_valueset = flv.flex_value_set_id
951 AND auv.lob_code = flv.flex_value';
952
953 l_extra_query VARCHAR2(32767);
954 l_final_query VARCHAR2(32767);
955 l_lob_id NUMBER;
956 l_position NUMBER;
957
958 hier_name VARCHAR2(32767);
959
960 BEGIN
961
962 l_position := 1;
963
964
965 IF(p_org_new_tbl.count > 0)
966 THEN
967 l_extra_query := ' AND organization_id IN ( ';
968 END IF;
969
970 FOR i IN 1..p_org_new_tbl.count LOOP
971
972 l_extra_query := l_extra_query || p_org_new_tbl(i).org_id;
973 IF (i = p_org_new_tbl.count)
974 THEN
975 l_extra_query := l_extra_query || ' )';
976 ELSE
977 l_extra_query := l_extra_query || ', ';
978 END IF;
979
980 END LOOP;
981
982 l_final_query := l_get_lob_query || l_extra_query;
983
984 OPEN lobs_cursor FOR l_final_query;
985 LOOP
986 FETCH lobs_cursor INTO l_lob_id;
987
988 EXIT WHEN lobs_cursor%NOTFOUND;
989 p_lob_new_tbl(l_position).lob_id := l_lob_id;
990 l_position := l_position + 1;
991 END LOOP;
992 CLOSE lobs_cursor;
993
994 END generate_lob_list;
995
996 PROCEDURE populate_process_hierarchy
997 (
998 p_api_version_number IN NUMBER := 1.0,
999 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1000 p_commit IN VARCHAR2 := FND_API.g_false,
1001 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1002 p_entity_type IN VARCHAR2,
1003 p_entity_id IN NUMBER,
1004 p_org_tbl IN org_tbl_type,
1005 p_process_tbl IN process_tbl_type,
1006 x_return_status OUT nocopy VARCHAR2,
1007 x_msg_count OUT nocopy NUMBER,
1008 x_msg_data OUT nocopy VARCHAR2
1009 )
1010 IS
1011 CURSOR c_audit_unit(p_org_id NUMBER)
1012 IS
1013 SELECT audit_v.company_code,
1014 audit_v.subsidiary_valueset,
1015 audit_v.lob_code,
1016 audit_v.lob_valueset,
1017 audit_v.organization_id
1018 FROM amw_audit_units_v audit_v
1019 WHERE organization_id = p_org_id;
1020
1021 CURSOR c_sub_lob_exists (l_sub_vs amw_audit_units_v.subsidiary_valueset%TYPE,
1022 l_sub_code amw_audit_units_v.company_code%TYPE,
1023 l_lob_vs amw_audit_units_v.lob_valueset%TYPE,
1024 l_lob_code amw_audit_units_v.lob_code%TYPE)
1025 IS
1026 SELECT 'Y'
1027 FROM amw_audit_units_v
1028 WHERE subsidiary_valueset = l_sub_vs
1029 AND company_code = l_sub_code
1030 AND lob_valueset = l_lob_vs
1031 AND lob_code = l_lob_code;
1032
1033 l_api_name VARCHAR2(150) := 'populate_process_hierarchy';
1034 l_api_version_number CONSTANT NUMBER := 1.0;
1035
1036 TYPE orgprocesstype IS REF CURSOR;
1037 process_cursor orgprocesstype;
1038
1039 l_get_processes_query VARCHAR2(32767) :=
1040 'SELECT org_v.child_process_id as top_process_id,
1041 org_v.child_process_org_rev_id as process_org_rev_id,
1042 org_v.child_organization_id as organization_id,
1043 audit_v.company_code,
1044 audit_v.subsidiary_valueset,
1045 audit_v.lob_code,
1046 audit_v.lob_valueset
1047 FROM amw_curr_app_hierarchy_org_v org_v,amw_audit_units_v audit_v
1048 WHERE org_v.parent_process_id = -2
1049 AND audit_v.organization_id = org_v.child_organization_id
1050 AND audit_v.organization_id =';
1051
1052 l_extra_query VARCHAR2(32767);
1053 l_final_query VARCHAR2(32767);
1054
1055 l_process_id NUMBER;
1056 l_process_org_rev_id NUMBER;
1057 l_organization_id NUMBER;
1058 l_company_code amw_audit_units_v.company_code%TYPE;
1059 l_subsidiary_valueset amw_audit_units_v.subsidiary_valueset%TYPE;
1060 l_lob_code amw_audit_units_v.lob_code%TYPE;
1061 l_lob_valueset amw_audit_units_v.lob_valueset%TYPE;
1062
1063 l_sub_lob_exists VARCHAR2(1);
1064
1065 BEGIN
1066 SAVEPOINT populate_proc_hierarchy;
1067 -- Standard call to check for call compatibility.
1068
1069 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1070 p_api_version_number,
1071 l_api_name,
1072 G_PKG_NAME)
1073 THEN
1074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1075 END IF;
1076
1077 -- Initialize message list if p_init_msg_list is set to TRUE.
1078 IF FND_API.to_Boolean( p_init_msg_list )
1079 THEN
1080 FND_MSG_PUB.initialize;
1081 END IF;
1082
1083 -- Initialize API return status to SUCCESS
1084 x_return_status := FND_API.G_RET_STS_SUCCESS;
1085
1086 /* DELETE FROM AMW_EXECUTION_SCOPE
1087 WHERE entity_id = p_entity_id
1088 AND entity_type = p_entity_type
1089 and level_id < 4;*/
1090
1091 FOR each_rec IN 1..p_org_tbl.count
1092 LOOP
1093 FOR audit_rec IN c_audit_unit(p_org_tbl(each_rec).org_id)
1094 LOOP
1095 INSERT INTO AMW_EXECUTION_SCOPE (
1096 EXECUTION_SCOPE_ID,
1097 ENTITY_TYPE,
1098 ENTITY_ID,
1099 CREATED_BY,
1100 CREATION_DATE,
1101 LAST_UPDATE_DATE,
1102 LAST_UPDATED_BY,
1103 LAST_UPDATE_LOGIN,
1104 SCOPE_CHANGED_STATUS,
1105 LEVEL_ID,
1106 SUBSIDIARY_VS,
1107 SUBSIDIARY_CODE,
1108 LOB_VS,
1109 LOB_CODE,
1110 ORGANIZATION_ID,
1111 PROCESS_ID,
1112 TOP_PROCESS_ID,
1113 PARENT_PROCESS_ID,
1114 PROCESS_ORG_REV_ID,
1115 SCOPE_MODIFIED_DATE)
1116 SELECT amw_execution_scope_s.nextval,
1117 p_entity_type,
1118 p_entity_id,
1119 FND_GLOBAL.USER_ID,
1120 SYSDATE,
1121 SYSDATE,
1122 FND_GLOBAL.USER_ID,
1123 FND_GLOBAL.USER_ID,
1124 'C',
1125 1,
1126 audit_rec.subsidiary_valueset,
1127 audit_rec.company_code,
1128 null,
1129 null,
1130 null,
1131 null,
1132 null,
1133 null,
1134 null,
1135 sysdate
1136 FROM DUAL
1137 WHERE not exists (SELECT 'Y'
1138 FROM AMW_EXECUTION_SCOPE
1139 WHERE entity_type=p_entity_type
1140 AND entity_id= p_entity_id
1141 AND subsidiary_vs = audit_rec.subsidiary_valueset
1142 AND subsidiary_code= audit_rec.company_code
1143 AND level_id=1);
1144
1145 l_company_code := audit_rec.company_code;
1146 l_lob_code := audit_rec.lob_code;
1147 l_sub_lob_exists := 'N';
1148
1149 IF l_company_code IS NOT NULL AND l_lob_code IS NOT NULL
1150 THEN
1151 OPEN c_sub_lob_exists (audit_rec.subsidiary_valueset,l_company_code,audit_rec.lob_valueset,l_lob_code);
1152 FETCH c_sub_lob_exists INTO l_sub_lob_exists;
1153 CLOSE c_sub_lob_exists;
1154 END IF;
1155
1156 IF l_sub_lob_exists IS NOT NULL AND l_sub_lob_exists='Y'
1157 THEN
1158 INSERT INTO AMW_EXECUTION_SCOPE (
1159 EXECUTION_SCOPE_ID,
1160 ENTITY_TYPE,
1161 ENTITY_ID,
1162 CREATED_BY,
1163 CREATION_DATE,
1164 LAST_UPDATE_DATE,
1165 LAST_UPDATED_BY,
1166 LAST_UPDATE_LOGIN,
1167 SCOPE_CHANGED_STATUS,
1168 LEVEL_ID,
1169 SUBSIDIARY_VS,
1170 SUBSIDIARY_CODE,
1171 LOB_VS,
1172 LOB_CODE,
1173 ORGANIZATION_ID,
1174 PROCESS_ID,
1175 TOP_PROCESS_ID,
1176 PARENT_PROCESS_ID,
1177 PROCESS_ORG_REV_ID,
1178 SCOPE_MODIFIED_DATE)
1179 SELECT amw_execution_scope_s.nextval,
1180 p_entity_type,
1181 p_entity_id,
1182 FND_GLOBAL.USER_ID,
1183 SYSDATE,
1184 SYSDATE,
1185 FND_GLOBAL.USER_ID,
1186 FND_GLOBAL.USER_ID,
1187 'C',
1188 2,
1189 audit_rec.subsidiary_valueset,
1190 audit_rec.company_code,
1191 audit_rec.lob_valueset,
1192 audit_rec.lob_code,
1193 null,
1194 null,
1195 null,
1196 null,
1197 null,
1198 SYSDATE
1199 FROM DUAL
1200 WHERE not exists (SELECT 'Y'
1201 FROM AMW_EXECUTION_SCOPE
1202 WHERE entity_type=p_entity_type
1203 AND entity_id= p_entity_id
1204 AND subsidiary_vs = audit_rec.subsidiary_valueset
1205 AND subsidiary_code= audit_rec.company_code
1206 AND lob_vs = audit_rec.lob_valueset
1207 AND lob_code = audit_rec.lob_code
1208 AND level_id=2);
1209
1210 INSERT INTO AMW_EXECUTION_SCOPE (
1211 EXECUTION_SCOPE_ID,
1212 ENTITY_TYPE,
1213 ENTITY_ID,
1214 CREATED_BY,
1215 CREATION_DATE,
1216 LAST_UPDATE_DATE,
1217 LAST_UPDATED_BY,
1218 LAST_UPDATE_LOGIN,
1219 SCOPE_CHANGED_STATUS,
1220 LEVEL_ID,
1221 SUBSIDIARY_VS,
1222 SUBSIDIARY_CODE,
1223 LOB_VS,
1224 LOB_CODE,
1225 ORGANIZATION_ID,
1226 PROCESS_ID,
1227 TOP_PROCESS_ID,
1228 PARENT_PROCESS_ID,
1229 PROCESS_ORG_REV_ID,
1230 SCOPE_MODIFIED_DATE )
1231 SELECT amw_execution_scope_s.nextval,
1232 p_entity_type,
1233 p_entity_id,
1234 FND_GLOBAL.USER_ID,
1235 SYSDATE,
1236 SYSDATE,
1237 FND_GLOBAL.USER_ID,
1238 FND_GLOBAL.USER_ID,
1239 'C',
1240 3,
1241 audit_rec.subsidiary_valueset,
1242 audit_rec.company_code,
1243 audit_rec.lob_valueset,
1244 audit_rec.lob_code,
1245 audit_rec.organization_id,
1246 null,
1247 null,
1248 null,
1249 null,
1250 SYSDATE
1251 FROM DUAL
1252 WHERE NOT EXISTS (SELECT 'Y'
1253 FROM AMW_EXECUTION_SCOPE
1254 WHERE entity_type=p_entity_type
1255 AND entity_id= p_entity_id
1256 AND subsidiary_vs = audit_rec.subsidiary_valueset
1257 AND subsidiary_code= audit_rec.company_code
1258 AND lob_vs = audit_rec.lob_valueset
1259 AND lob_code = audit_rec.lob_code
1260 AND organization_id = audit_rec.organization_id
1261 AND level_id=3);
1262 ELSE
1263 INSERT INTO AMW_EXECUTION_SCOPE (
1264 EXECUTION_SCOPE_ID,
1265 ENTITY_TYPE,
1266 ENTITY_ID,
1267 CREATED_BY,
1268 CREATION_DATE,
1269 LAST_UPDATE_DATE,
1270 LAST_UPDATED_BY,
1271 LAST_UPDATE_LOGIN,
1272 SCOPE_CHANGED_STATUS,
1273 LEVEL_ID,
1274 SUBSIDIARY_VS,
1275 SUBSIDIARY_CODE,
1276 LOB_VS,
1277 LOB_CODE,
1278 ORGANIZATION_ID,
1279 PROCESS_ID,
1280 TOP_PROCESS_ID,
1281 PARENT_PROCESS_ID,
1282 PROCESS_ORG_REV_ID,
1283 SCOPE_MODIFIED_DATE)
1284 SELECT amw_execution_scope_s.nextval,
1285 p_entity_type,
1286 p_entity_id,
1287 FND_GLOBAL.USER_ID,
1288 SYSDATE,
1289 SYSDATE,
1290 FND_GLOBAL.USER_ID,
1291 FND_GLOBAL.USER_ID,
1292 'C',
1293 2,
1294 audit_rec.subsidiary_valueset,
1295 audit_rec.company_code,
1296 '-999',
1297 'AMW_DUMMY_LOBCODE',
1298 null,
1299 null,
1300 null,
1301 null,
1302 null,
1303 SYSDATE
1304 FROM DUAL
1305 WHERE not exists (SELECT 'Y'
1306 FROM AMW_EXECUTION_SCOPE
1307 WHERE entity_type=p_entity_type
1308 AND entity_id= p_entity_id
1309 AND subsidiary_vs = audit_rec.subsidiary_valueset
1310 AND subsidiary_code= audit_rec.company_code
1311 AND lob_vs = '-999'
1312 AND lob_code = 'AMW_DUMMY_LOBCODE'
1313 AND level_id=2);
1314
1315
1316 INSERT INTO AMW_EXECUTION_SCOPE (
1317 EXECUTION_SCOPE_ID,
1318 ENTITY_TYPE,
1319 ENTITY_ID,
1320 CREATED_BY,
1321 CREATION_DATE,
1322 LAST_UPDATE_DATE,
1323 LAST_UPDATED_BY,
1324 LAST_UPDATE_LOGIN,
1325 SCOPE_CHANGED_STATUS,
1326 LEVEL_ID,
1327 SUBSIDIARY_VS,
1328 SUBSIDIARY_CODE,
1329 LOB_VS,
1330 LOB_CODE,
1331 ORGANIZATION_ID,
1332 PROCESS_ID,
1333 TOP_PROCESS_ID,
1334 PARENT_PROCESS_ID,
1335 PROCESS_ORG_REV_ID,
1336 SCOPE_MODIFIED_DATE )
1337 SELECT amw_execution_scope_s.nextval,
1338 p_entity_type,
1339 p_entity_id,
1340 FND_GLOBAL.USER_ID,
1341 SYSDATE,
1342 SYSDATE,
1343 FND_GLOBAL.USER_ID,
1344 FND_GLOBAL.USER_ID,
1345 'C',
1346 3,
1347 audit_rec.subsidiary_valueset,
1348 audit_rec.company_code,
1349 '-999',
1350 'AMW_DUMMY_LOBCODE',
1351 audit_rec.organization_id,
1352 null,
1353 null,
1354 null,
1355 null,
1356 SYSDATE
1357 FROM DUAL
1358 WHERE not exists (SELECT 'Y'
1359 FROM AMW_EXECUTION_SCOPE
1360 WHERE entity_type=p_entity_type
1361 AND entity_id= p_entity_id
1362 AND subsidiary_vs = audit_rec.subsidiary_valueset
1363 AND subsidiary_code= audit_rec.company_code
1364 AND lob_vs = '-999'
1365 AND lob_code = 'AMW_DUMMY_LOBCODE'
1366 AND organization_id = audit_rec.organization_id
1367 AND level_id=3);
1368 END IF;
1369 END LOOP; --audit_rec IN c_audit_unit
1370
1371 IF(p_process_tbl.count > 0)
1372 THEN
1373 l_extra_query := ' AND org_v.child_process_id IN (';
1374 END IF;
1375
1376 FOR i IN 1..p_process_tbl.count LOOP
1377 l_extra_query := l_extra_query || p_process_tbl(i).process_id;
1378 IF (i = p_process_tbl.count)
1379 THEN
1380 l_extra_query := l_extra_query || ' )';
1381 ELSE
1382 l_extra_query := l_extra_query || ', ';
1383 END IF;
1384 END LOOP;
1385
1386 l_final_query := l_get_processes_query || p_org_tbl(each_rec).org_id || l_extra_query;
1387
1388 OPEN process_cursor FOR l_final_query;
1389 LOOP
1390 FETCH process_cursor INTO l_process_id,
1391 l_process_org_rev_id,
1392 l_organization_id,
1393 l_company_code,
1394 l_subsidiary_valueset,
1395 l_lob_code,
1396 l_lob_valueset;
1397 EXIT WHEN process_cursor%NOTFOUND;
1398
1399 INSERT INTO AMW_EXECUTION_SCOPE (
1400 EXECUTION_SCOPE_ID,
1401 ENTITY_TYPE,
1402 ENTITY_ID,
1403 CREATED_BY,
1404 CREATION_DATE,
1405 LAST_UPDATE_DATE,
1406 LAST_UPDATED_BY,
1407 LAST_UPDATE_LOGIN,
1408 SCOPE_CHANGED_STATUS,
1409 LEVEL_ID,
1410 SUBSIDIARY_VS,
1411 SUBSIDIARY_CODE,
1412 LOB_VS,
1413 LOB_CODE,
1414 ORGANIZATION_ID,
1415 PROCESS_ID,
1416 TOP_PROCESS_ID,
1417 PARENT_PROCESS_ID,
1418 PROCESS_ORG_REV_ID,
1419 SCOPE_MODIFIED_DATE)
1420 SELECT amw_execution_scope_s.nextval,
1421 p_entity_type,
1422 p_entity_id,
1423 FND_GLOBAL.USER_ID,
1424 SYSDATE,
1425 SYSDATE,
1426 FND_GLOBAL.USER_ID,
1427 FND_GLOBAL.USER_ID,
1428 'C',
1429 4,
1430 l_subsidiary_valueset,
1431 l_company_code,
1432 l_lob_valueset,
1433 l_lob_code,
1434 l_organization_id,
1435 l_process_id,
1436 l_process_id,
1437 -1,
1438 l_process_org_rev_id,
1439 SYSDATE
1440 FROM DUAL
1441 WHERE not exists (SELECT 'Y'
1442 FROM AMW_EXECUTION_SCOPE
1443 WHERE entity_type=p_entity_type
1444 AND entity_id= p_entity_id
1445 AND subsidiary_vs = l_subsidiary_valueset
1446 AND subsidiary_code= l_company_code
1447 AND lob_vs = l_lob_valueset
1448 AND lob_code = l_lob_code
1449 AND process_id = l_process_id
1450 AND process_org_rev_id = l_process_org_rev_id
1451 AND level_id=4);
1452
1453 -- Insert All the processes in the process Hierarchy using the top_process_id's
1454 Insert_Process(5,
1455 l_process_id,
1456 l_process_id,
1457 l_process_org_rev_id,
1458 l_subsidiary_valueset,
1459 l_company_code,
1460 l_lob_valueset,
1461 l_lob_code,
1462 l_organization_id,
1463 p_entity_type,
1464 p_entity_id);
1465
1466 END LOOP;
1467 CLOSE process_cursor;
1468
1469
1470 END LOOP;--each_rec IN 1..p_org_tbl.count
1471
1472 EXCEPTION WHEN OTHERS THEN
1473 rollback to populate_proc_hierarchy;
1474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1475 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1476 FND_MSG_PUB.Count_And_Get(
1477 p_encoded => FND_API.G_FALSE,
1478 p_count => x_msg_count,
1479 p_data => x_msg_data);
1480
1481 END populate_process_hierarchy;
1482
1483 PROCEDURE Insert_Process
1484 (
1485 p_level_id IN NUMBER,
1486 p_parent_process_id IN NUMBER,
1487 p_top_process_id IN NUMBER,
1488 p_process_org_rev_id IN NUMBER,
1489 p_subsidiary_vs IN VARCHAR2,
1490 p_subsidiary_code IN VARCHAR2,
1491 p_lob_vs IN VARCHAR2,
1492 p_lob_code IN VARCHAR2,
1493 p_organization_id IN NUMBER,
1494 p_entity_type IN VARCHAR2,
1495 p_entity_id IN NUMBER
1496 ) IS
1497 CURSOR c_process IS
1498 SELECT apv.child_process_id process_id, apv.child_process_org_rev_id process_org_rev_id
1499 FROM amw_curr_app_hierarchy_org_v apv
1500 WHERE apv.parent_process_id = p_parent_process_id
1501 and apv.child_organization_id = p_organization_id;
1502 BEGIN
1503 FOR proc_rec IN c_process LOOP
1504 Insert_Process (p_level_id+1,proc_rec.process_id,p_top_process_id,proc_rec.process_org_rev_id, p_subsidiary_vs,
1505 p_subsidiary_code,p_lob_vs,p_lob_code,p_organization_id,p_entity_type,p_entity_id);
1506 INSERT INTO AMW_EXECUTION_SCOPE (
1507 EXECUTION_SCOPE_ID,
1508 ENTITY_TYPE,
1509 ENTITY_ID,
1510 CREATED_BY,
1511 CREATION_DATE,
1512 LAST_UPDATE_DATE,
1513 LAST_UPDATED_BY,
1514 LAST_UPDATE_LOGIN,
1515 SCOPE_CHANGED_STATUS,
1516 LEVEL_ID,
1517 SUBSIDIARY_VS,
1518 SUBSIDIARY_CODE,
1519 LOB_VS,
1520 LOB_CODE,
1521 ORGANIZATION_ID,
1522 PROCESS_ID,
1523 TOP_PROCESS_ID,
1524 PARENT_PROCESS_ID,
1525 PROCESS_ORG_REV_ID,
1526 SCOPE_MODIFIED_DATE)
1527 SELECT amw_execution_scope_s.nextval,
1528 p_entity_type,
1529 p_entity_id,
1530 FND_GLOBAL.USER_ID,
1531 SYSDATE,
1532 SYSDATE,
1533 FND_GLOBAL.USER_ID,
1534 FND_GLOBAL.USER_ID,
1535 'C',
1536 p_level_id,
1537 p_subsidiary_vs,
1538 p_subsidiary_code,
1539 p_lob_vs,
1540 p_lob_code,
1541 p_organization_id,
1542 proc_rec.process_id,
1543 p_top_process_id,
1544 p_parent_process_id,
1545 proc_rec.process_org_rev_id,
1546 SYSDATE
1547 FROM DUAL;
1548 END LOOP;
1549 END Insert_Process;
1550
1551 PROCEDURE build_project_audit_task (
1552 p_api_version_number IN NUMBER,
1553 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1554 p_commit IN VARCHAR2 := FND_API.g_false,
1555 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1556 p_audit_project_id IN NUMBER,
1557 l_ineff_controls IN BOOLEAN := false,
1558 p_source_project_id IN NUMBER := 0,
1559 x_return_status OUT nocopy VARCHAR2,
1560 x_msg_count OUT nocopy NUMBER,
1561 x_msg_data OUT nocopy VARCHAR2
1562 ) IS
1563 CURSOR c_project IS
1564 SELECT 'Y' FROM AMW_AUDIT_PROJECTS
1565 WHERE audit_project_id = p_audit_project_id
1566 FOR UPDATE nowait;
1567
1568 CURSOR c_project_scope_org IS
1569 SELECT organization_id,
1570 process_id,
1571 scope_changed_status
1572 FROM AMW_EXECUTION_SCOPE
1573 WHERE entity_type='PROJECT'
1574 AND entity_id=p_audit_project_id
1575 AND organization_id IS NOT NULL
1576 AND process_id IS NULL
1577 AND SCOPE_CHANGED_STATUS = 'C';
1578
1579 CURSOR c_project_scope IS
1580 SELECT organization_id,
1581 process_id,
1582 scope_changed_status
1583 FROM AMW_EXECUTION_SCOPE
1584 WHERE entity_type='PROJECT'
1585 AND entity_id=p_audit_project_id
1586 AND process_id IS NOT NULL
1587 AND SCOPE_CHANGED_STATUS = 'C';
1588
1589 CURSOR c_ap_attachments IS
1590 SELECT fad.entity_name, fad.pk1_value, fad.pk2_value, fad.pk3_value, fad.pk4_value, fad.pk5_value
1591 FROM fnd_attached_documents fad
1592 WHERE fad.entity_name = 'AMW_PROJECT_AP'
1593 AND fad.pk1_value = p_audit_project_id
1594 AND NOT EXISTS (select 'Y'
1595 from amw_ap_associations ap_assoc
1596 where ap_assoc.object_type='PROJECT'
1597 and ap_assoc.pk1 = fad.pk1_value
1598 and ap_assoc.pk2 = fad.pk2_value
1599 and ap_assoc.pk4 = fad.pk3_value
1600 and ap_assoc.audit_procedure_rev_id = fad.pk4_value);
1601 -- To fetch the new audit procedure id
1602 CURSOR c_apdetails IS
1603 select distinct audit_procedure_rev_id, pk1,pk2, pk4
1604 from amw_ap_associations
1605 where object_type = 'PROJECT_NEW'
1606 and pk1 = p_audit_project_id
1607 and audit_procedure_rev_id is not null;
1608
1609 l_api_name CONSTANT VARCHAR2(30) := 'Build_Audit_Task';
1610 l_api_version_number CONSTANT NUMBER := 1.0;
1611 l_exists VARCHAR2(1);
1612 v_category_id NUMBER;
1613 BEGIN
1614 -- Standard Start of API savepoint
1615 SAVEPOINT BUILD_AUDIT_TASK_PVT;
1616
1617 -- Standard call to check for call compatibility.
1618 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1619 p_api_version_number,
1620 l_api_name,
1621 G_PKG_NAME) THEN
1622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1623 END IF;
1624
1625
1626 -- Initialize message list if p_init_msg_list is set to TRUE.
1627 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1628 FND_MSG_PUB.initialize;
1629 END IF;
1630
1631 -- Initialize API return status to SUCCESS
1632 x_return_status := FND_API.G_RET_STS_SUCCESS;
1633
1634
1635 OPEN c_project;
1636 FETCH c_project INTO l_exists;
1637 CLOSE c_project;
1638
1639
1640 DELETE FROM amw_risk_associations ara
1641 WHERE object_type='PROJECT'
1642 AND pk1 = p_audit_project_id
1643 AND pk3 IS NOT NULL
1644 AND not exists
1645 (select 'Y'
1646 from amw_execution_scope
1647 where entity_type='PROJECT'
1648 and entity_id = p_audit_project_id
1649 and organization_id = ara.pk2
1650 and process_id = ara.pk3);
1651
1652 DELETE FROM amw_control_associations aca
1653 WHERE object_type='PROJECT'
1654 AND pk1 = p_audit_project_id
1655 AND pk3 IS NOT NULL
1656 AND not exists
1657 (select 'Y' from amw_risk_associations ara
1658 where object_type = 'PROJECT'
1659 and ara.pk1 = p_audit_project_id
1660 and ara.pk2 = aca.pk2
1661 and ara.pk3 = aca.pk3
1662 and ara.risk_id = aca.pk4);
1663
1664 DELETE FROM amw_ap_associations apa
1665 WHERE object_type='PROJECT'
1666 AND pk1 = p_audit_project_id
1667 AND not exists
1668 (select 'Y' from amw_control_associations aca
1669 where aca.object_type = 'PROJECT'
1670 and aca.pk1 = p_audit_project_id
1671 and aca.pk2 = apa.pk2
1672 -- and aca.pk3 IS NOT NULL --process
1673 and aca.control_id = apa.pk3)
1674 and pk2 <> -1 and pk3 <> -1;
1675
1676 DELETE FROM amw_ap_associations apa
1677 WHERE object_type='PROJECT'
1678 AND pk1 = p_audit_project_id
1679 AND not exists
1680 (select 'Y' from amw_execution_scope aes
1681 where aes.entity_type = 'PROJECT'
1682 and aes.entity_id = p_audit_project_id
1683 and aes.organization_id = apa.pk2)
1684 and pk3 = -1
1685 and pk2 <> -1 ;
1686
1687 /* Entity risk/control/ap changes begin*/
1688
1689 DELETE FROM amw_risk_associations ara
1690 WHERE object_type='PROJECT'
1691 AND pk1 = p_audit_project_id
1692 AND pk3 IS NULL
1693 AND not exists
1694 (select 'Y'
1695 from amw_execution_scope
1696 where entity_type='PROJECT'
1697 and entity_id = p_audit_project_id
1698 and organization_id = ara.pk2
1699 and process_id IS NULL);
1700
1701 DELETE FROM amw_control_associations aca
1702 WHERE object_type='PROJECT'
1703 AND pk1 = p_audit_project_id
1704 AND pk3 IS NULL
1705 AND not exists
1706 (select 'Y' from amw_risk_associations ara
1707 where object_type = 'PROJECT'
1708 and ara.pk1 = p_audit_project_id
1709 and ara.pk2 = aca.pk2
1710 and ara.pk3 IS NULL
1711 and ara.risk_id = aca.pk4);
1712
1713 DELETE FROM amw_ap_associations apa
1714 WHERE object_type='PROJECT'
1715 AND pk1 = p_audit_project_id
1716 AND not exists
1717 (select 'Y' from amw_control_associations aca
1718 where aca.object_type = 'PROJECT'
1719 and aca.pk1 = p_audit_project_id
1720 and aca.pk2 = apa.pk2
1721 -- and aca.pk3 IS NULL --process
1722 and aca.control_id = apa.pk3)
1723 and pk2 <> -1 and pk3 <> -1;
1724
1725 DELETE FROM amw_ap_associations apa
1726 WHERE object_type='PROJECT'
1727 AND pk1 = p_audit_project_id
1728 AND not exists
1729 (select 'Y' from amw_execution_scope aes
1730 where aes.entity_type = 'PROJECT'
1731 and aes.entity_id = p_audit_project_id
1732 and aes.organization_id = apa.pk2)
1733 and pk3 = -1
1734 and pk2 <> -1 ;
1735 /* Entity risk/control/ap changes end*/
1736
1737 FOR scope_rec IN c_project_scope LOOP
1738
1739 UPDATE AMW_RISK_ASSOCIATIONS ara
1740 SET ara.risk_rev_id = (SELECT risk.risk_rev_id
1741 FROM AMW_RISKS_B risk
1742 WHERE risk.risk_id = ara.risk_id
1743 AND risk.curr_approved_flag = 'Y')
1744 WHERE ara.object_type = 'PROJECT'
1745 AND ara.pk1 = p_audit_project_id
1746 AND ara.pk2 = scope_rec.organization_id
1747 AND ara.pk3 = scope_rec.process_id;
1748
1749 INSERT INTO AMW_RISK_ASSOCIATIONS
1750 (
1751 risk_association_id,
1752 last_update_date,
1753 last_updated_by,
1754 creation_date,
1755 created_by,
1756 last_update_login,
1757 risk_id,
1758 risk_rev_id,
1759 pk1,
1760 pk2,
1761 pk3,
1762 object_type,
1763 object_version_number
1764 )
1765 SELECT AMW_RISK_ASSOCIATIONS_S.nextval,
1766 sysdate,
1767 fnd_global.user_id,
1768 sysdate,
1769 fnd_global.user_id,
1770 fnd_global.user_id,
1771 risk.risk_id,
1772 risk.risk_rev_id,
1773 p_audit_project_id,
1774 scope_rec.organization_id,
1775 scope_rec.process_id,
1776 'PROJECT',
1777 1
1778 FROM amw_risk_associations ara, amw_risks_b risk
1779 WHERE ara.object_type = 'PROCESS_ORG'
1780 AND ara.pk1 = scope_rec.organization_id
1781 AND ara.pk2 = scope_rec.process_id
1782 AND ara.risk_id = risk.risk_id
1783 AND risk.curr_approved_flag = 'Y'
1784 AND ara.approval_date IS NOT NULL
1785 AND ara.deletion_approval_date IS NULL
1786 AND not exists
1787 (select 'Y' from amw_risk_associations ara2
1788 where ara2.object_type = 'PROJECT'
1789 and ara2.pk1 = p_audit_project_id
1790 and ara2.pk2 = scope_rec.organization_id
1791 and ara2.pk3 = scope_rec.process_id
1792 and ara2.risk_id = risk.risk_id
1793 and ara2.risk_rev_id = risk.risk_rev_id
1794 );
1795
1796 UPDATE AMW_CONTROL_ASSOCIATIONS aca
1797 SET aca.control_rev_id = (SELECT control_rev_id
1798 FROM AMW_CONTROLS_B control
1799 WHERE control.control_id = aca.control_id
1800 AND control.curr_approved_flag = 'Y')
1801 WHERE aca.object_type = 'PROJECT'
1802 AND aca.pk1 = p_audit_project_id
1803 AND aca.pk2 = scope_rec.organization_id
1804 AND aca.pk3 = scope_rec.process_id;
1805
1806 INSERT INTO amw_control_associations
1807 (
1808 control_association_id,
1809 last_update_date,
1810 last_updated_by,
1811 creation_date,
1812 created_by,
1813 last_update_login,
1814 control_id,
1815 control_rev_id,
1816 pk1,
1817 pk2,
1818 pk3,
1819 pk4,
1820 object_type,
1821 object_version_number
1822 )
1823 SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
1824 SYSDATE,
1825 FND_GLOBAL.USER_ID,
1826 SYSDATE,
1827 FND_GLOBAL.USER_ID,
1828 FND_GLOBAL.USER_ID,
1829 control.control_id,
1830 control.control_rev_id,
1831 p_audit_project_id,
1832 scope_rec.organization_id,
1833 scope_rec.process_id,
1834 ara.risk_id,
1835 'PROJECT',
1836 1
1837 FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
1838 WHERE aca.object_type = 'RISK_ORG'
1839 AND aca.pk1 = scope_rec.organization_id
1840 AND aca.pk2 = scope_rec.process_id
1841 AND aca.pk3 = ara.risk_id
1842 AND aca.control_id = control.control_id
1843 AND aca.approval_date IS NOT NULL
1844 AND aca.deletion_approval_date IS NULL
1845 AND control.curr_approved_flag = 'Y'
1846 AND ara.object_type = 'PROJECT'
1847 AND ara.pk1 = p_audit_project_id
1848 AND ara.pk2 = scope_rec.organization_id
1849 AND ara.pk3 = scope_rec.process_id
1850 AND not exists
1851 (SELECT 'Y' from amw_control_associations aca2
1852 WHERE aca2.object_type = 'PROJECT'
1853 AND aca2.pk1 = p_audit_project_id
1854 AND aca2.pk2 = scope_rec.organization_id
1855 AND aca2.pk3 = scope_rec.process_id
1856 AND aca2.pk4 = ara.risk_id
1857 AND aca2.control_id = control.control_id
1858 AND aca2.control_rev_id = control.control_rev_id
1859 );
1860
1861
1862 UPDATE AMW_AP_ASSOCIATIONS apa
1863 SET apa.audit_procedure_rev_id = (SELECT audit_procedure_rev_id
1864 FROM amw_audit_procedures_b aapb1
1865 WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
1866 AND aapb1.curr_approved_flag = 'Y')
1867 WHERE apa.object_type = 'PROJECT'
1868 AND apa.pk1 = p_audit_project_id
1869 AND apa.pk2 = scope_rec.organization_id;
1870
1871 INSERT INTO amw_ap_associations (
1872 ap_association_id,
1873 last_update_date,
1874 last_updated_by,
1875 creation_date,
1876 created_by,
1877 last_update_login,
1878 audit_procedure_id,
1879 audit_procedure_rev_id,
1880 pk1,
1881 pk2,
1882 pk3,
1883 pk4,
1884 object_type,
1885 object_version_number)
1886 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
1887 SYSDATE,
1888 FND_GLOBAL.USER_ID,
1889 SYSDATE,
1890 FND_GLOBAL.USER_ID,
1891 FND_GLOBAL.USER_ID,
1892 ttt.audit_procedure_id,
1893 ttt.audit_procedure_rev_id,
1894 p_audit_project_id,
1895 ttt.organization_id,
1896 ttt.control_id,
1897 NVL(ttt.task_id, -1),
1898 'PROJECT_NEW',
1899 1
1900 FROM (SELECT distinct
1901 aapb.audit_procedure_id,
1902 aapb.audit_procedure_rev_id,
1903 apa.pk1 organization_id,
1904 apa.pk3 control_id,
1905 pt2.task_id
1906 FROM amw_ap_associations apa,
1907 amw_audit_procedures_b aapb,
1908 amw_ap_tasks apt,
1909 amw_control_associations aca,
1910 amw_audit_projects_v pp,
1911 amw_audit_tasks_v pt1,
1912 amw_audit_tasks_v pt2
1913 WHERE apa.object_type = 'CTRL_ORG'
1914 AND aca.object_type='PROJECT'
1915 AND aca.pk1 = p_audit_project_id
1916 AND aca.pk2 = scope_rec.organization_id
1917 AND aca.pk3 = scope_rec.process_id
1918 AND apa.pk1 = aca.pk2 -- organization_id
1919 AND apa.pk2 = aca.pk3 -- process_id
1920 AND apa.pk3 = aca.control_id
1921 AND apa.association_creation_date IS NOT NULL
1922 AND apa.deletion_date IS NULL
1923 AND apa.audit_procedure_id = aapb.audit_procedure_id
1924 AND aapb.curr_approved_flag='Y'
1925 AND pp.audit_project_id = p_audit_project_id
1926 AND decode(apt.source_code, 'ICM', pt1.audit_project_id,
1927 pt1.project_id)
1928 = pp.created_from_project_id
1929 AND pt1.task_id = apt.task_id
1930 AND pt1.task_number = pt2.task_number
1931 and apt.audit_procedure_id = apa.audit_procedure_id
1932 AND pt2.audit_project_id = p_audit_project_id) ttt
1933 WHERE NOT EXISTS
1934 (SELECT 'Y' from amw_ap_associations apa2
1935 where apa2.object_type in ('PROJECT','PROJECT_NEW')
1936 AND apa2.pk1 = p_audit_project_id
1937 AND apa2.pk2 = ttt.organization_id
1938 AND apa2.pk3 = ttt.control_id
1939 AND apa2.pk4 = ttt.task_id
1940 AND apa2.audit_procedure_id = ttt.audit_procedure_id
1941 AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
1942 );
1943
1944 INSERT INTO amw_ap_associations (
1945 ap_association_id,
1946 last_update_date,
1947 last_updated_by,
1948 creation_date,
1949 created_by,
1950 last_update_login,
1951 audit_procedure_id,
1952 audit_procedure_rev_id,
1953 pk1,
1954 pk2,
1955 pk3,
1956 pk4,
1957 object_type,
1958 object_version_number)
1959 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
1960 SYSDATE,
1961 FND_GLOBAL.USER_ID,
1962 SYSDATE,
1963 FND_GLOBAL.USER_ID,
1964 FND_GLOBAL.USER_ID,
1965 ttt.audit_procedure_id,
1966 ttt.audit_procedure_rev_id,
1967 p_audit_project_id,
1968 ttt.organization_id,
1969 ttt.control_id,
1970 NVL(ttt.task_id, -1),
1971 'PROJECT_NEW',
1972 1
1973 FROM (SELECT distinct
1974 aapb.audit_procedure_id,
1975 aapb.audit_procedure_rev_id,
1976 apa.pk1 organization_id,
1977 apa.pk3 control_id,
1978 pt2.task_id
1979 FROM amw_ap_associations apa,
1980 amw_audit_procedures_b aapb,
1981 amw_ap_tasks apt,
1982 amw_control_associations aca,
1983 amw_audit_projects_v pp,
1984 amw_template_tasks_v pt1,
1985 amw_audit_tasks_v pt2
1986 WHERE apa.object_type = 'CTRL_ORG'
1987 AND aca.object_type='PROJECT'
1988 AND aca.pk1 = p_audit_project_id
1989 AND aca.pk2 = scope_rec.organization_id
1990 AND aca.pk3 = scope_rec.process_id
1991 AND apa.pk1 = aca.pk2 -- organization_id
1992 AND apa.pk2 = aca.pk3 -- process_id
1993 AND apa.pk3 = aca.control_id
1994 AND apa.association_creation_date IS NOT NULL
1995 AND apa.deletion_date IS NULL
1996 AND apa.audit_procedure_id = aapb.audit_procedure_id
1997 AND aapb.curr_approved_flag='Y'
1998 AND pp.audit_project_id = p_audit_project_id
1999 AND apt.source_code = 'PA'
2000 AND pt1.project_id = pp.created_from_project_id
2001 AND pt1.task_id = apt.task_id
2002 AND pt1.task_number = pt2.task_number
2003 and apt.audit_procedure_id = apa.audit_procedure_id
2004 AND pt2.audit_project_id = p_audit_project_id) ttt
2005 WHERE NOT EXISTS
2006 (SELECT 'Y' from amw_ap_associations apa2
2007 where apa2.object_type in ('PROJECT','PROJECT_NEW')
2008 AND apa2.pk1 = p_audit_project_id
2009 AND apa2.pk2 = ttt.organization_id
2010 AND apa2.pk3 = ttt.control_id
2011 AND apa2.pk4 = ttt.task_id
2012 AND apa2.audit_procedure_id = ttt.audit_procedure_id
2013 AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
2014 );
2015
2016 INSERT INTO amw_ap_associations (
2017 ap_association_id,
2018 last_update_date,
2019 last_updated_by,
2020 creation_date,
2021 created_by,
2022 last_update_login,
2023 audit_procedure_id,
2024 audit_procedure_rev_id,
2025 pk1,
2026 pk2,
2027 pk3,
2028 pk4,
2029 object_type,
2030 object_version_number)
2031 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2032 sysdate,
2033 fnd_global.user_id,
2034 sysdate,
2035 fnd_global.user_id,
2036 fnd_global.user_id,
2037 ttt.audit_procedure_id,
2038 ttt.audit_procedure_rev_id,
2039 p_audit_project_id,
2040 ttt.organization_id,
2041 ttt.control_id,
2042 -1,
2043 'PROJECT_NEW',
2044 1
2045 FROM (SELECT distinct
2046 aapb.audit_procedure_id,
2047 aapb.audit_procedure_rev_Id,
2048 apa.pk1 organization_id,
2049 apa.pk3 control_id
2050 FROM amw_ap_associations apa,
2051 amw_audit_procedures_b aapb,
2052 amw_control_associations aca
2053 WHERE apa.object_type = 'CTRL_ORG'
2054 AND aca.object_type='PROJECT'
2055 AND aca.pk1 = p_audit_project_id
2056 AND aca.pk2 = scope_rec.organization_id
2057 AND aca.pk3 = scope_rec.process_id
2058 AND apa.pk1 = aca.pk2 -- organization_id
2059 AND apa.pk2 = aca.pk3 -- process_id
2060 AND apa.pk3 = aca.control_id
2061 AND apa.association_creation_date IS NOT NULL
2062 AND apa.deletion_date IS NULL
2063 AND apa.audit_procedure_id = aapb.audit_procedure_id
2064 AND aapb.curr_approved_flag='Y') ttt
2065 WHERE NOT EXISTS
2066 (SELECT 'Y' from amw_ap_associations apa2
2067 WHERE apa2.object_type in ('PROJECT','PROJECT_NEW')
2068 AND apa2.pk1 = p_audit_project_id
2069 AND apa2.pk2 = ttt.organization_id
2070 AND apa2.pk3 = ttt.control_id
2071 AND apa2.audit_procedure_id = ttt.audit_procedure_id
2072 AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
2073 );
2074
2075 END LOOP; -- FOR scope_rec IN c_project_scope LOOP
2076
2077 /* Changes for org risk/control/ap */
2078 FOR scope_org_rec IN c_project_scope_org LOOP
2079
2080 UPDATE AMW_RISK_ASSOCIATIONS ara
2081 SET ara.risk_rev_id = (SELECT risk.risk_rev_id
2082 FROM AMW_RISKS_B risk
2083 WHERE risk.risk_id = ara.risk_id
2084 AND risk.curr_approved_flag = 'Y')
2085 WHERE ara.object_type = 'PROJECT'
2086 AND ara.pk1 = p_audit_project_id
2087 AND ara.pk2 = scope_org_rec.organization_id
2088 AND ara.pk3 IS NULL;
2089
2090 INSERT INTO AMW_RISK_ASSOCIATIONS
2091 (
2092 risk_association_id,
2093 last_update_date,
2094 last_updated_by,
2095 creation_date,
2096 created_by,
2097 last_update_login,
2098 risk_id,
2099 risk_rev_id,
2100 pk1,
2101 pk2,
2102 pk3,
2103 object_type,
2104 object_version_number
2105 )
2106 SELECT AMW_RISK_ASSOCIATIONS_S.nextval,
2107 sysdate,
2108 fnd_global.user_id,
2109 sysdate,
2110 fnd_global.user_id,
2111 fnd_global.user_id,
2112 risk.risk_id,
2113 risk.risk_rev_id,
2114 p_audit_project_id,
2115 scope_org_rec.organization_id,
2116 null,
2117 'PROJECT',
2118 1
2119 FROM amw_risk_associations ara, amw_risks_b risk
2120 WHERE ara.object_type = 'ENTITY_RISK'
2121 AND ara.pk1 = scope_org_rec.organization_id
2122 AND ara.pk2 IS NULL
2123 AND ara.risk_id = risk.risk_id
2124 AND risk.curr_approved_flag = 'Y'
2125 AND not exists
2126 (select 'Y' from amw_risk_associations ara2
2127 where ara2.object_type = 'PROJECT'
2128 and ara2.pk1 = p_audit_project_id
2129 and ara2.pk2 = scope_org_rec.organization_id
2130 and ara2.pk3 IS NULL
2131 and ara2.risk_id = risk.risk_id
2132 and ara2.risk_rev_id = risk.risk_rev_id
2133 );
2134
2135 UPDATE AMW_CONTROL_ASSOCIATIONS aca
2136 SET aca.control_rev_id = (SELECT control_rev_id
2137 FROM AMW_CONTROLS_B control
2138 WHERE control.control_id = aca.control_id
2139 AND control.curr_approved_flag = 'Y')
2140 WHERE aca.object_type = 'PROJECT'
2141 AND aca.pk1 = p_audit_project_id
2142 AND aca.pk2 = scope_org_rec.organization_id
2143 AND aca.pk3 IS NULL;
2144
2145 if(not l_ineff_controls) THEN
2146 INSERT INTO amw_control_associations
2147 (
2148 control_association_id,
2149 last_update_date,
2150 last_updated_by,
2151 creation_date,
2152 created_by,
2153 last_update_login,
2154 control_id,
2155 control_rev_id,
2156 pk1,
2157 pk2,
2158 pk3,
2159 pk4,
2160 object_type,
2161 object_version_number
2162 )
2163 SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
2164 SYSDATE,
2165 FND_GLOBAL.USER_ID,
2166 SYSDATE,
2167 FND_GLOBAL.USER_ID,
2168 FND_GLOBAL.USER_ID,
2169 control.control_id,
2170 control.control_rev_id,
2171 p_audit_project_id,
2172 scope_org_rec.organization_id,
2173 null,
2174 ara.risk_id,
2175 'PROJECT',
2176 1
2177 FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
2178 WHERE aca.object_type = 'ENTITY_CONTROL'
2179 AND aca.pk1 = scope_org_rec.organization_id
2180 AND aca.pk2 = ara.risk_id
2181 AND aca.pk3 IS NULL
2182 AND aca.control_id = control.control_id
2183 AND control.curr_approved_flag = 'Y'
2184 AND ara.object_type = 'PROJECT'
2185 AND ara.pk1 = p_audit_project_id
2186 AND ara.pk2 = scope_org_rec.organization_id
2187 AND ara.pk3 IS NULL
2188 AND not exists
2189 (SELECT 'Y' from amw_control_associations aca2
2190 WHERE aca2.object_type = 'PROJECT'
2191 AND aca2.pk1 = p_audit_project_id
2192 AND aca2.pk2 = scope_org_rec.organization_id
2193 AND aca2.pk3 IS NULL
2194 AND aca2.pk4 = ara.risk_id
2195 AND aca2.control_id = control.control_id
2196 AND aca2.control_rev_id = control.control_rev_id
2197 );
2198 ELSE
2199 INSERT INTO amw_control_associations
2200 (
2201 control_association_id,
2202 last_update_date,
2203 last_updated_by,
2204 creation_date,
2205 created_by,
2206 last_update_login,
2207 control_id,
2208 control_rev_id,
2209 pk1,
2210 pk2,
2211 pk3,
2212 pk4,
2213 object_type,
2214 object_version_number
2215 )
2216 SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
2217 SYSDATE,
2218 FND_GLOBAL.USER_ID,
2219 SYSDATE,
2220 FND_GLOBAL.USER_ID,
2221 FND_GLOBAL.USER_ID,
2222 control.control_id,
2223 control.control_rev_id,
2224 p_audit_project_id,
2225 scope_org_rec.organization_id,
2226 null,
2227 ara.risk_id,
2228 'PROJECT',
2229 1
2230 FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
2231 WHERE aca.object_type = 'ENTITY_CONTROL'
2232 AND aca.pk1 = scope_org_rec.organization_id
2233 AND aca.pk2 = ara.risk_id
2234 AND aca.pk3 IS NULL
2235 AND aca.control_id = control.control_id
2236 AND control.curr_approved_flag = 'Y'
2237 AND ara.object_type = 'PROJECT'
2238 AND ara.pk1 = p_audit_project_id
2239 AND ara.pk2 = scope_org_rec.organization_id
2240 AND ara.pk3 IS NULL
2241 AND not exists
2242 (SELECT 'Y' from amw_control_associations aca2
2243 WHERE aca2.object_type = 'PROJECT'
2244 AND aca2.pk1 = p_audit_project_id
2245 AND aca2.pk2 = scope_org_rec.organization_id
2246 AND aca2.pk3 IS NULL
2247 AND aca2.pk4 = ara.risk_id
2248 AND aca2.control_id = control.control_id
2249 AND aca2.control_rev_id = control.control_rev_id
2250 )
2251 and aca.control_id in
2252 (select distinct control_id from amw_control_associations where pk1=p_source_project_id and object_type='PROJECT'
2253 and control_id not in (select pk1_value from amw_opinions_v where pk2_value =p_source_project_id
2254 and audit_result_code ='EFFECTIVE' and
2255 object_name='AMW_ORG_CONTROL') );
2256 END IF;
2257
2258
2259 UPDATE AMW_AP_ASSOCIATIONS apa
2260 SET apa.audit_procedure_rev_id = (SELECT audit_procedure_rev_id
2261 FROM amw_audit_procedures_b aapb1
2262 WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
2263 AND aapb1.curr_approved_flag = 'Y')
2264 WHERE apa.object_type = 'PROJECT'
2265 AND apa.pk1 = p_audit_project_id
2266 AND apa.pk2 = scope_org_rec.organization_id;
2267
2268 INSERT INTO amw_ap_associations (
2269 ap_association_id,
2270 last_update_date,
2271 last_updated_by,
2272 creation_date,
2273 created_by,
2274 last_update_login,
2275 audit_procedure_id,
2276 audit_procedure_rev_id,
2277 pk1,
2278 pk2,
2279 pk3,
2280 pk4,
2281 object_type,
2282 object_version_number)
2283 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2284 SYSDATE,
2285 FND_GLOBAL.USER_ID,
2286 SYSDATE,
2287 FND_GLOBAL.USER_ID,
2288 FND_GLOBAL.USER_ID,
2289 ttt.audit_procedure_id,
2290 ttt.audit_procedure_rev_id,
2291 p_audit_project_id,
2292 ttt.organization_id,
2293 ttt.control_id,
2294 NVL(ttt.task_id, -1),
2295 'PROJECT_NEW',
2296 1
2297 FROM (SELECT distinct
2298 aapb.audit_procedure_id,
2299 aapb.audit_procedure_rev_id,
2300 apa.pk1 organization_id,
2301 apa.pk2 control_id,
2302 pt2.task_id
2303 FROM amw_ap_associations apa,
2304 amw_audit_procedures_b aapb,
2305 amw_ap_tasks apt,
2306 amw_control_associations aca,
2307 amw_audit_projects_v pp,
2308 amw_audit_tasks_v pt1,
2309 amw_audit_tasks_v pt2
2310 WHERE apa.object_type = 'ENTITY_AP'
2311 AND aca.object_type='PROJECT'
2312 AND aca.pk1 = p_audit_project_id
2313 AND aca.pk2 = scope_org_rec.organization_id
2314 AND aca.pk3 IS NULL
2315 AND apa.pk1 = aca.pk2 -- organization_id
2316 AND apa.pk2 = aca.control_id -- Control_id
2317 AND apa.audit_procedure_id = aapb.audit_procedure_id
2318 AND apa.association_creation_date IS NOT NULL
2319 AND aapb.curr_approved_flag='Y'
2320 AND pp.audit_project_id = p_audit_project_id
2321 AND decode(apt.source_code, 'ICM', pt1.audit_project_id,
2322 pt1.project_id)
2323 = pp.created_from_project_id
2324 AND pt1.task_id = apt.task_id
2325 AND pt1.task_number = pt2.task_number
2326 and apt.audit_procedure_id = apa.audit_procedure_id
2327 AND pt2.audit_project_id = p_audit_project_id) ttt
2328 WHERE NOT EXISTS
2329 (SELECT 'Y' from amw_ap_associations apa2
2330 where apa2.object_type in ('PROJECT','PROJECT_NEW')
2331 AND apa2.pk1 = p_audit_project_id
2332 AND apa2.pk2 = ttt.organization_id
2333 AND apa2.pk3 = ttt.control_id
2334 AND apa2.pk4 = ttt.task_id
2335 AND apa2.audit_procedure_id = ttt.audit_procedure_id
2336 AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
2337 );
2338
2339 INSERT INTO amw_ap_associations (
2340 ap_association_id,
2341 last_update_date,
2342 last_updated_by,
2343 creation_date,
2344 created_by,
2345 last_update_login,
2346 audit_procedure_id,
2347 audit_procedure_rev_id,
2348 pk1,
2349 pk2,
2350 pk3,
2351 pk4,
2352 object_type,
2353 object_version_number)
2354 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2355 SYSDATE,
2356 FND_GLOBAL.USER_ID,
2357 SYSDATE,
2358 FND_GLOBAL.USER_ID,
2359 FND_GLOBAL.USER_ID,
2360 ttt.audit_procedure_id,
2361 ttt.audit_procedure_rev_id,
2362 p_audit_project_id,
2363 ttt.organization_id,
2364 ttt.control_id,
2365 NVL(ttt.task_id, -1),
2366 'PROJECT_NEW',
2367 1
2368 FROM (SELECT distinct
2369 aapb.audit_procedure_id,
2370 aapb.audit_procedure_rev_id,
2371 apa.pk1 organization_id,
2372 apa.pk2 control_id,
2373 pt2.task_id
2374 FROM amw_ap_associations apa,
2375 amw_audit_procedures_b aapb,
2376 amw_ap_tasks apt,
2377 amw_control_associations aca,
2378 amw_audit_projects_v pp,
2379 amw_template_tasks_v pt1,
2380 amw_audit_tasks_v pt2
2381 WHERE apa.object_type = 'ENTITY_AP'
2382 AND aca.object_type='PROJECT'
2383 AND aca.pk1 = p_audit_project_id
2384 AND aca.pk2 = scope_org_rec.organization_id
2385 AND aca.pk3 IS NULL
2386 AND apa.pk1 = aca.pk2 -- organization_id
2387 AND apa.pk2 = aca.control_id -- Control_id
2388 AND apa.audit_procedure_id = aapb.audit_procedure_id
2389 AND apa.association_creation_date IS NOT NULL
2390 AND aapb.curr_approved_flag='Y'
2391 AND pp.audit_project_id = p_audit_project_id
2392 AND apt.source_code = 'PA'
2393 AND pt1.project_id = pp.created_from_project_id
2394 AND pt1.task_id = apt.task_id
2395 AND pt1.task_number = pt2.task_number
2396 and apt.audit_procedure_id = apa.audit_procedure_id
2397 AND pt2.audit_project_id = p_audit_project_id) ttt
2398 WHERE NOT EXISTS
2399 (SELECT 'Y' from amw_ap_associations apa2
2400 where apa2.object_type in ('PROJECT','PROJECT_NEW')
2401 AND apa2.pk1 = p_audit_project_id
2402 AND apa2.pk2 = ttt.organization_id
2403 AND apa2.pk3 = ttt.control_id
2404 AND apa2.pk4 = ttt.task_id
2405 AND apa2.audit_procedure_id = ttt.audit_procedure_id
2406 AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
2407 );
2408
2409 INSERT INTO amw_ap_associations (
2410 ap_association_id,
2411 last_update_date,
2412 last_updated_by,
2413 creation_date,
2414 created_by,
2415 last_update_login,
2416 audit_procedure_id,
2417 audit_procedure_rev_id,
2418 pk1,
2419 pk2,
2420 pk3,
2421 pk4,
2422 object_type,
2423 object_version_number)
2424 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2425 sysdate,
2426 fnd_global.user_id,
2427 sysdate,
2428 fnd_global.user_id,
2429 fnd_global.user_id,
2430 ttt.audit_procedure_id,
2431 ttt.audit_procedure_rev_id,
2432 p_audit_project_id,
2433 ttt.organization_id,
2434 ttt.control_id,
2435 -1,
2436 'PROJECT_NEW',
2437 1
2438 FROM (SELECT distinct
2439 aapb.audit_procedure_id,
2440 aapb.audit_procedure_rev_Id,
2441 apa.pk1 organization_id,
2442 apa.pk2 control_id
2443 FROM amw_ap_associations apa,
2444 amw_audit_procedures_b aapb,
2445 amw_control_associations aca
2446 WHERE apa.object_type = 'ENTITY_AP'
2447 AND aca.object_type='PROJECT'
2448 AND aca.pk1 = p_audit_project_id
2449 AND aca.pk2 = scope_org_rec.organization_id
2450 AND aca.pk3 IS NULL
2451 AND apa.pk1 = aca.pk2 -- organization_id
2452 AND apa.pk2 = aca.control_id -- control_id
2453 AND apa.audit_procedure_id = aapb.audit_procedure_id
2454 AND apa.association_creation_date IS NOT NULL
2455 AND aapb.curr_approved_flag='Y') ttt
2456 WHERE NOT EXISTS
2457 (SELECT 'Y' from amw_ap_associations apa2
2458 WHERE apa2.object_type in ('PROJECT','PROJECT_NEW')
2459 AND apa2.pk1 = p_audit_project_id
2460 AND apa2.pk2 = ttt.organization_id
2461 AND apa2.pk3 = ttt.control_id
2462 AND apa2.audit_procedure_id = ttt.audit_procedure_id
2463 AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
2464 );
2465
2466 END LOOP; -- FOR scope_org_rec IN c_project_scope_org LOOP
2467
2468 -- To get the Category of Audit Procedure Working Papers
2469 select category_id into v_category_id
2470 from fnd_document_categories where name = 'AMW_WORK_PAPERS';
2471 -- To copy the attachments
2472 FOR apdetails_rec IN c_apdetails LOOP
2473 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(X_from_entity_name => 'AMW_AUDIT_PRCD',
2474 X_from_pk1_value => apdetails_rec.audit_procedure_rev_id,
2475 X_to_entity_name => 'AMW_PROJECT_AP',
2476 X_to_pk1_value => apdetails_rec.pk1,
2477 X_to_pk2_value => apdetails_rec.pk2,
2478 X_to_pk3_value => apdetails_rec.pk4,
2479 X_to_pk4_value => apdetails_rec.audit_procedure_rev_id,
2480 X_FROM_CATEGORY_ID => v_category_id,
2481 X_TO_CATEGORY_ID => v_category_id);
2482
2483 END LOOP; -- end of FOR aapdetails_rec IN c_apdetails LOOP
2484
2485 -- Change entity_type 'PROJECT_NEW' to 'PROJECT'
2486 UPDATE AMW_AP_ASSOCIATIONS SET object_type='PROJECT' WHERE object_type = 'PROJECT_NEW';
2487 --- Copy Attachment ends here
2488
2489 FOR ap_attachment_rec IN c_ap_attachments LOOP
2490 -- Delete all the attachments for the audit procedure that is not present in the project
2491 fnd_attached_documents2_pkg.delete_attachments(X_entity_name => ap_attachment_rec.entity_name,
2492 X_pk1_value => ap_attachment_rec.pk1_value,
2493 X_pk2_value => ap_attachment_rec.pk2_value,
2494 X_pk3_value => ap_attachment_rec.pk3_value,
2495 X_pk4_value => ap_attachment_rec.pk4_value);
2496 END LOOP; -- end of FOR ap_attachment_rec IN c_ap_attachments LOOP
2497
2498
2499 UPDATE AMW_EXECUTION_SCOPE
2500 SET SCOPE_CHANGED_STATUS = null
2501 WHERE entity_type='PROJECT'
2502 AND entity_id=p_audit_project_id
2503 AND SCOPE_CHANGED_STATUS = 'C';
2504
2505 UPDATE AMW_AUDIT_PROJECTS
2506 SET scope_changed_flag = 'N'
2507 WHERE project_id = p_audit_project_id;
2508
2509
2510 EXCEPTION WHEN OTHERS THEN
2511 rollback to BUILD_AUDIT_TASK_PVT;
2512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2513 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, l_api_name);
2514 FND_MSG_PUB.Count_And_Get(
2515 p_encoded => FND_API.G_FALSE,
2516 p_count => x_msg_count,
2517 p_data => x_msg_data);
2518 END build_project_audit_task;
2519
2520 PROCEDURE populate_denormalized_tables
2521 (
2522 p_entity_type IN VARCHAR2,
2523 p_entity_id IN NUMBER,
2524 p_org_tbl IN org_tbl_type,
2525 p_process_tbl IN process_tbl_type,
2526 p_mode IN VARCHAR2
2527 )
2528 IS
2529
2530 BEGIN
2531
2532 DELETE FROM amw_proc_cert_eval_sum pcert
2533 WHERE certification_id = p_entity_id
2534 AND NOT EXISTS (SELECT 'Y'
2535 FROM amw_execution_scope exec
2536 WHERE exec.entity_id = pcert.certification_id
2537 AND exec.entity_type = p_entity_type
2538 AND exec.process_id = pcert.process_id
2539 AND exec.organization_id = pcert.organization_id
2540 );
2541
2542 INSERT INTO amw_proc_cert_eval_sum(certification_id,
2543 process_id,
2544 organization_id,
2545 process_org_rev_id,
2546 created_by,
2547 creation_date,
2548 last_updated_by,
2549 last_update_date,
2550 last_update_login)
2551 SELECT DISTINCT
2552 entity_id,
2553 process_id,
2554 organization_id,
2555 process_org_rev_id,
2556 fnd_global.user_id,
2557 SYSDATE,
2558 fnd_global.user_id,
2559 SYSDATE,
2560 fnd_global.user_id
2561 FROM amw_execution_scope exec
2562 WHERE NOT EXISTS (SELECT 'Y'
2563 FROM amw_proc_cert_eval_sum pcert
2564 WHERE pcert.certification_id = exec.entity_id
2565 AND pcert.organization_id = exec.organization_id
2566 AND pcert.process_id = exec.process_id)
2567 AND entity_id = p_entity_id
2568 AND entity_type = p_entity_type
2569 AND scope_changed_status = 'C'
2570 AND level_id > 3;
2571
2572 IF p_mode = 'ADD'
2573 THEN
2574
2575 INSERT INTO amw_org_cert_eval_sum(certification_id,
2576 organization_id,
2577 created_by,
2578 creation_date,
2579 last_updated_by,
2580 last_update_date,
2581 last_update_login)
2582 SELECT entity_id,
2583 organization_id,
2584 fnd_global.user_id,
2585 SYSDATE,
2586 fnd_global.user_id,
2587 SYSDATE,
2588 fnd_global.user_id
2589 FROM amw_execution_scope exec
2590 WHERE NOT EXISTS (SELECT 'Y'
2591 FROM amw_org_cert_eval_sum ocert
2592 WHERE ocert.certification_id = exec.entity_id
2593 AND ocert.organization_id = exec.organization_id
2594 )
2595 AND entity_id = p_entity_id
2596 AND entity_type = p_entity_type
2597 AND scope_changed_status = 'C'
2598 AND level_id = 3;
2599 END IF;
2600
2601 END populate_denormalized_tables;
2602
2603 PROCEDURE populate_association_tables
2604 (
2605 p_api_version_number IN NUMBER := 1.0,
2606 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2607 p_commit IN VARCHAR2 := FND_API.g_false,
2608 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2609 p_entity_type IN VARCHAR2,
2610 p_entity_id IN NUMBER,
2611 x_return_status OUT nocopy VARCHAR2,
2612 x_msg_count OUT nocopy NUMBER,
2613 x_msg_data OUT nocopy VARCHAR2
2614 ) IS
2615
2616 CURSOR get_records_in_scope IS
2617 SELECT organization_id,process_id, scope_changed_status
2618 FROM AMW_EXECUTION_SCOPE
2619 WHERE entity_type = p_entity_type
2620 AND entity_id = p_entity_id
2621 AND scope_changed_status = 'C'
2622 AND process_id IS NOT NULL
2623 FOR UPDATE NOWAIT;
2624
2625
2626 CURSOR c_scope_org IS
2627 SELECT organization_id,
2628 process_id,
2629 scope_changed_status
2630 FROM AMW_EXECUTION_SCOPE
2631 WHERE entity_type=p_entity_type
2632 AND entity_id=p_entity_id
2633 AND organization_id IS NOT NULL
2634 AND process_id IS NULL
2635 AND SCOPE_CHANGED_STATUS = 'C';
2636
2637
2638 l_api_name CONSTANT VARCHAR2(30) := 'populate_association_tables';
2639 l_api_version_number CONSTANT NUMBER := 1.0;
2640 l_exists VARCHAR2(1);
2641
2642 BEGIN
2643 -- Standard Start of API savepoint
2644 SAVEPOINT POPULATE_ASSOCIATIONS;
2645
2646 -- Standard call to check for call compatibility.
2647 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2648 p_api_version_number,
2649 l_api_name,
2650 'AMW_SCOPE_PVT') THEN
2651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2652 END IF;
2653
2654
2655 -- Initialize message list if p_init_msg_list is set to TRUE.
2656 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2657 FND_MSG_PUB.initialize;
2658 END IF;
2659
2660 -- Initialize API return status to SUCCESS
2661 x_return_status := FND_API.G_RET_STS_SUCCESS;
2662
2663 DELETE FROM AMW_RISK_ASSOCIATIONS ara
2664 WHERE object_type = p_entity_type
2665 AND pk1 = p_entity_id
2666 AND pk3 IS NOT NULL
2667 AND NOT EXISTS
2668 (SELECT 'Y'
2669 FROM AMW_EXECUTION_SCOPE
2670 WHERE entity_type = p_entity_type
2671 AND entity_id = p_entity_id
2672 AND organization_id = ara.pk2
2673 AND process_id = ara.pk3);
2674
2675 DELETE FROM AMW_CONTROL_ASSOCIATIONS aca
2676 WHERE object_type = p_entity_type
2677 AND pk1 = p_entity_id
2678 AND pk3 IS NOT NULL
2679 AND NOT EXISTS
2680 (SELECT 'Y'
2681 FROM AMW_RISK_ASSOCIATIONS ara
2682 WHERE object_type = p_entity_type
2683 AND ara.pk1 = p_entity_id
2684 AND ara.pk2 = aca.pk2
2685 AND ara.pk3 = aca.pk3
2686 AND ara.risk_id = aca.pk4);
2687
2688
2689
2690 /* Entity risk/control/ap changes begin*/
2691
2692 DELETE FROM amw_risk_associations ara
2693 WHERE object_type=p_entity_type
2694 AND pk1 = p_entity_id
2695 AND pk3 IS NULL
2696 AND not exists
2697 (select 'Y'
2698 from amw_execution_scope
2699 where entity_type=p_entity_type
2700 and entity_id = p_entity_id
2701 and organization_id = ara.pk2
2702 and process_id IS NULL);
2703
2704 DELETE FROM amw_control_associations aca
2705 WHERE object_type=p_entity_type
2706 AND pk1 = p_entity_id
2707 AND pk3 IS NULL
2708 AND not exists
2709 (select 'Y' from amw_risk_associations ara
2710 where object_type = p_entity_type
2711 and ara.pk1 = p_entity_id
2712 and ara.pk2 = aca.pk2
2713 and ara.pk3 IS NULL
2714 and ara.risk_id = aca.pk4);
2715
2716 /* Entity risk/control/ap changes end*/
2717
2718 DELETE FROM AMW_AP_ASSOCIATIONS apa
2719 WHERE object_type = p_entity_type
2720 AND pk1 = p_entity_id
2721 AND NOT EXISTS
2722 (SELECT 'Y' FROM AMW_CONTROL_ASSOCIATIONS aca
2723 WHERE aca.object_type = p_entity_type
2724 AND aca.pk1 = p_entity_id
2725 AND aca.pk2 = apa.pk2
2726 AND aca.control_id = apa.pk3)
2727 and pk2 <> -1 and pk3 <> -1;
2728
2729 DELETE FROM amw_ap_associations apa
2730 WHERE object_type = p_entity_type
2731 AND pk1 = p_entity_id
2732 AND not exists
2733 (select 'Y' from amw_execution_scope aes
2734 where aes.entity_type = p_entity_type
2735 and aes.entity_id = p_entity_id
2736 and aes.organization_id = apa.pk2)
2737 and pk3 = -1
2738 and pk2 <> -1 ;
2739
2740 FOR each_rec IN get_records_in_scope
2741 LOOP
2742 UPDATE amw_risk_associations ara
2743 SET ara.risk_rev_id = (SELECT risk.risk_rev_id
2744 FROM amw_risks_b risk
2745 WHERE risk.risk_id = ara.risk_id
2746 AND risk.curr_approved_flag = 'Y')
2747 WHERE ara.object_type = p_entity_type
2748 AND ara.pk1 = p_entity_id
2749 AND ara.pk2 = each_rec.organization_id
2750 AND ara.pk3 = each_rec.process_id;
2751
2752 INSERT INTO amw_risk_associations (
2753 risk_association_id,
2754 last_update_date,
2755 last_updated_by,
2756 creation_date,
2757 created_by,
2758 last_update_login,
2759 risk_id,
2760 risk_rev_id,
2761 pk1,
2762 pk2,
2763 pk3,
2764 object_type,
2765 object_version_number)
2766 SELECT AMW_RISK_ASSOCIATIONS_S.nextval,
2767 sysdate,
2768 fnd_global.user_id,
2769 sysdate,
2770 fnd_global.user_id,
2771 fnd_global.user_id,
2772 risk.risk_id,
2773 risk.risk_rev_id,
2774 p_entity_id,
2775 ara.pk1,
2776 ara.pk2,
2777 p_entity_type,
2778 1
2779 FROM amw_risk_associations ara, amw_risks_b risk
2780 WHERE ara.object_type = 'PROCESS_ORG'
2781 AND ara.pk1 = each_rec.organization_id
2782 AND ara.pk2 = each_rec.process_id
2783 AND ara.risk_id = risk.risk_id
2784 AND ara.approval_date IS NOT NULL
2785 AND ara.deletion_approval_date IS NULL
2786 AND risk.curr_approved_flag = 'Y'
2787 AND NOT EXISTS
2788 (SELECT 'Y' FROM amw_risk_associations ara2
2789 WHERE ara2.object_type=p_entity_type
2790 AND ara2.pk1 = p_entity_id
2791 AND ara2.pk2 = each_rec.organization_id
2792 AND ara2.pk3 = each_rec.process_id
2793 AND ara2.risk_id = risk.risk_id
2794 AND ara2.risk_rev_id = risk.risk_rev_id
2795 );
2796
2797
2798 UPDATE amw_control_associations aca
2799 SET aca.control_rev_id = (SELECT control_rev_id
2800 FROM amw_controls_b control
2801 WHERE control.control_id = aca.control_id
2802 AND control.curr_approved_flag = 'Y')
2803 WHERE aca.object_type = p_entity_type
2804 AND aca.pk1 = p_entity_id
2805 AND aca.pk2 = each_rec.organization_id
2806 AND aca.pk3 = each_rec.process_id;
2807
2808 INSERT INTO amw_control_associations (
2809 control_association_id,
2810 last_update_date,
2811 last_updated_by,
2812 creation_date,
2813 created_by,
2814 last_update_login,
2815 control_id,
2816 control_rev_id,
2817 pk1,
2818 pk2,
2819 pk3,
2820 pk4,
2821 object_type,
2822 object_version_number)
2823 SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
2824 sysdate,
2825 fnd_global.user_id,
2826 sysdate,
2827 fnd_global.user_id,
2828 fnd_global.user_id,
2829 control.control_id,
2830 control.control_rev_id,
2831 p_entity_id,
2832 each_rec.organization_id,
2833 each_rec.process_id,
2834 ara.risk_id,
2835 p_entity_type,
2836 1
2837 FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
2838 WHERE aca.object_type = 'RISK_ORG'
2839 AND aca.pk1 = each_rec.organization_id
2840 AND aca.pk2 = each_rec.process_id
2841 AND aca.pk3 = ara.risk_id
2842 AND aca.control_id = control.control_id
2843 AND aca.approval_date IS NOT NULL
2844 AND aca.deletion_approval_date IS NULL
2845 AND control.curr_approved_flag = 'Y'
2846 AND ara.object_type = 'PROCESS_ORG'
2847 AND ara.pk1 = each_rec.organization_id
2848 AND ara.pk2 = each_rec.process_id
2849 AND NOT EXISTS
2850 (SELECT 'Y' FROM amw_control_associations aca2
2851 WHERE aca2.object_type = p_entity_type
2852 AND aca2.pk1 = p_entity_id
2853 AND aca2.pk2 = each_rec.organization_id
2854 AND aca2.pk3 = each_rec.process_id
2855 AND aca2.pk4 = ara.risk_id
2856 AND aca2.control_id = control.control_id
2857 AND aca2.control_rev_id = control.control_rev_id
2858 );
2859
2860 UPDATE amw_ap_associations apa
2861 SET apa.audit_procedure_rev_id = (SELECT audit_procedure_rev_id
2862 FROM amw_audit_procedures_b aapb1
2863 WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
2864 AND aapb1.curr_approved_flag = 'Y')
2865 WHERE apa.object_type = p_entity_type
2866 AND apa.pk1 = p_entity_id
2867 AND apa.pk2 = each_rec.organization_id;
2868
2869 INSERT INTO amw_ap_associations (
2870 ap_association_id,
2871 last_update_date,
2872 last_updated_by,
2873 creation_date,
2874 created_by,
2875 last_update_login,
2876 audit_procedure_id,
2877 audit_procedure_rev_id,
2878 pk1,
2879 pk2,
2880 pk3,
2881 object_type,
2882 object_version_number)
2883 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2884 sysdate,
2885 fnd_global.user_id,
2886 sysdate,
2887 fnd_global.user_id,
2888 fnd_global.user_id,
2889 auditproc.audit_procedure_id,
2890 auditproc.audit_procedure_rev_id,
2891 p_entity_id,
2892 auditproc.organization_id,
2893 auditproc.control_id,
2894 p_entity_type,
2895 1
2896 FROM
2897 (SELECT DISTINCT
2898 aapb.audit_procedure_id,
2899 aapb.audit_procedure_rev_id,
2900 apa.pk1 organization_id,
2901 aca.control_id
2902 FROM amw_ap_associations apa,amw_audit_procedures_b aapb,amw_control_associations aca
2903 WHERE apa.object_type = 'CTRL_ORG'
2904 AND apa.pk1 = each_rec.organization_id
2905 AND apa.pk2 = each_rec.process_id
2906 AND apa.pk3 = aca.control_id
2907 AND aca.object_type = 'RISK_ORG'
2908 AND apa.pk1 = aca.pk1 -- organization_id
2909 AND apa.pk2 = aca.pk2 -- process_id
2910 AND apa.audit_procedure_id = aapb.audit_procedure_id
2911 AND aapb.curr_approved_flag='Y'
2912 AND NOT EXISTS
2913 (SELECT 'Y' FROM amw_ap_associations apa2
2914 WHERE apa2.object_type = p_entity_type
2915 AND apa2.pk1 = p_entity_id
2916 AND apa2.pk2 = each_rec.organization_id
2917 AND apa2.pk3 = aca.control_id
2918 AND apa2.audit_procedure_id = aapb.audit_procedure_id
2919 AND apa2.audit_procedure_rev_id = aapb.audit_procedure_rev_id
2920 )
2921 ) auditproc;
2922
2923 END LOOP; -- FOR each_rec IN get_records_in_scope LOOP
2924
2925 /* Changes for org risk/control/ap */
2926 FOR scope_org_rec IN c_scope_org LOOP
2927
2928 UPDATE AMW_RISK_ASSOCIATIONS ara
2929 SET ara.risk_rev_id = (SELECT risk.risk_rev_id
2930 FROM AMW_RISKS_B risk
2931 WHERE risk.risk_id = ara.risk_id
2932 AND risk.curr_approved_flag = 'Y')
2933 WHERE ara.object_type = p_entity_type
2934 AND ara.pk1 = p_entity_id
2935 AND ara.pk2 = scope_org_rec.organization_id
2936 AND ara.pk3 IS NULL;
2937
2938 INSERT INTO AMW_RISK_ASSOCIATIONS
2939 (
2940 risk_association_id,
2941 last_update_date,
2942 last_updated_by,
2943 creation_date,
2944 created_by,
2945 last_update_login,
2946 risk_id,
2947 risk_rev_id,
2948 pk1,
2949 pk2,
2950 pk3,
2951 object_type,
2952 object_version_number
2953 )
2954 SELECT AMW_RISK_ASSOCIATIONS_S.nextval,
2955 sysdate,
2956 fnd_global.user_id,
2957 sysdate,
2958 fnd_global.user_id,
2959 fnd_global.user_id,
2960 risk.risk_id,
2961 risk.risk_rev_id,
2962 p_entity_id,
2963 scope_org_rec.organization_id,
2964 null,
2965 p_entity_type,
2966 1
2967 FROM amw_risk_associations ara, amw_risks_b risk
2968 WHERE ara.object_type = 'ENTITY_RISK'
2969 AND ara.pk1 = scope_org_rec.organization_id
2970 AND ara.pk2 IS NULL
2971 AND ara.risk_id = risk.risk_id
2972 AND risk.curr_approved_flag = 'Y'
2973 AND not exists
2974 (select 'Y' from amw_risk_associations ara2
2975 where ara2.object_type = p_entity_type
2976 and ara2.pk1 = p_entity_id
2977 and ara2.pk2 = scope_org_rec.organization_id
2978 and ara2.pk3 IS NULL
2979 and ara2.risk_id = risk.risk_id
2980 and ara2.risk_rev_id = risk.risk_rev_id
2981 );
2982
2983 UPDATE AMW_CONTROL_ASSOCIATIONS aca
2984 SET aca.control_rev_id =
2985 (SELECT control_rev_id
2986 FROM AMW_CONTROLS_B control
2987 WHERE control.control_id = aca.control_id
2988 AND control.curr_approved_flag = 'Y')
2989 WHERE aca.object_type = p_entity_type
2990 AND aca.pk1 = p_entity_id
2991 AND aca.pk2 = scope_org_rec.organization_id
2992 AND aca.pk3 IS NULL;
2993
2994 INSERT INTO amw_control_associations
2995 (
2996 control_association_id,
2997 last_update_date,
2998 last_updated_by,
2999 creation_date,
3000 created_by,
3001 last_update_login,
3002 control_id,
3003 control_rev_id,
3004 pk1,
3005 pk2,
3006 pk3,
3007 pk4,
3008 object_type,
3009 object_version_number
3010 )
3011 SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
3012 SYSDATE,
3013 FND_GLOBAL.USER_ID,
3014 SYSDATE,
3015 FND_GLOBAL.USER_ID,
3016 FND_GLOBAL.USER_ID,
3017 control.control_id,
3018 control.control_rev_id,
3019 p_entity_id,
3020 scope_org_rec.organization_id,
3021 null,
3022 ara.risk_id,
3023 p_entity_type,
3024 1
3025 FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
3026 WHERE aca.object_type = 'ENTITY_CONTROL'
3027 AND aca.pk1 = scope_org_rec.organization_id
3028 AND aca.pk2 = ara.risk_id
3029 AND aca.pk3 IS NULL
3030 AND aca.control_id = control.control_id
3031 AND control.curr_approved_flag = 'Y'
3032 AND ara.object_type = p_entity_type
3033 AND ara.pk1 = p_entity_id
3034 AND ara.pk2 = scope_org_rec.organization_id
3035 AND ara.pk3 IS NULL
3036 AND not exists
3037 (SELECT 'Y' from amw_control_associations aca2
3038 WHERE aca2.object_type = p_entity_type
3039 AND aca2.pk1 = p_entity_id
3040 AND aca2.pk2 = scope_org_rec.organization_id
3041 AND aca2.pk3 IS NULL
3042 AND aca2.pk4 = ara.risk_id
3043 AND aca2.control_id = control.control_id
3044 AND aca2.control_rev_id = control.control_rev_id
3045 );
3046
3047
3048 UPDATE AMW_AP_ASSOCIATIONS apa
3049 SET apa.audit_procedure_rev_id =
3050 (SELECT audit_procedure_rev_id
3051 FROM amw_audit_procedures_b aapb1
3052 WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
3053 AND aapb1.curr_approved_flag = 'Y')
3054 WHERE apa.object_type = p_entity_type
3055 AND apa.pk1 = p_entity_id
3056 AND apa.pk2 = scope_org_rec.organization_id;
3057
3058
3059 INSERT INTO amw_ap_associations (
3060 ap_association_id,
3061 last_update_date,
3062 last_updated_by,
3063 creation_date,
3064 created_by,
3065 last_update_login,
3066 audit_procedure_id,
3067 audit_procedure_rev_id,
3068 pk1,
3069 pk2,
3070 pk3,
3071 object_type,
3072 object_version_number)
3073 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
3074 sysdate,
3075 fnd_global.user_id,
3076 sysdate,
3077 fnd_global.user_id,
3078 fnd_global.user_id,
3079 auditproc.audit_procedure_id,
3080 auditproc.audit_procedure_rev_id,
3081 p_entity_id,
3082 auditproc.organization_id,
3083 auditproc.control_id,
3084 p_entity_type,
3085 1
3086 FROM
3087 (SELECT DISTINCT
3088 aapb.audit_procedure_id,
3089 aapb.audit_procedure_rev_id,
3090 apa.pk1 organization_id,
3091 aca.control_id
3092 FROM amw_ap_associations apa,amw_audit_procedures_b aapb,
3093 amw_control_associations aca
3094 WHERE apa.object_type = 'ENTITY_AP'
3095 AND aca.object_type = p_entity_type
3096 AND aca.pk1 = p_entity_id
3097 AND aca.pk2 = scope_org_rec.organization_id
3098 AND aca.pk3 IS NULL
3099 AND apa.pk1 = aca.pk2
3100 AND apa.pk2 = aca.control_id
3101 AND apa.association_creation_date IS NOT NULL
3102 AND apa.audit_procedure_id = aapb.audit_procedure_id
3103 AND aapb.curr_approved_flag='Y'
3104 AND NOT EXISTS
3105 (SELECT 'Y' FROM amw_ap_associations apa2
3106 WHERE apa2.object_type = p_entity_type
3107 AND apa2.pk1 = p_entity_id
3108 AND apa2.pk2 = scope_org_rec.organization_id
3109 AND apa2.pk3 = aca.control_id
3110 AND apa2.audit_procedure_id = aapb.audit_procedure_id
3111 AND apa2.audit_procedure_rev_id = aapb.audit_procedure_rev_id
3112 )
3113 ) auditproc;
3114
3115
3116 END LOOP; -- FOR scope_org_rec IN c_project_scope_org LOOP
3117
3118 UPDATE amw_execution_scope
3119 SET SCOPE_CHANGED_STATUS = null
3120 WHERE entity_type = p_entity_type
3121 AND entity_id = p_entity_id
3122 AND SCOPE_CHANGED_STATUS = 'C';
3123
3124 EXCEPTION WHEN OTHERS THEN
3125 ROLLBACK TO POPULATE_ASSOCIATIONS;
3126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3127 FND_MSG_PUB.Add_Exc_Msg('AMW_SCOPE_PVT', l_api_name);
3128 FND_MSG_PUB.Count_And_Get(
3129 p_encoded => FND_API.G_FALSE,
3130 p_count => x_msg_count,
3131 p_data => x_msg_data);
3132 END populate_association_tables;
3133
3134 PROCEDURE populate_scope
3135 (
3136 p_api_version_number IN NUMBER := 1.0,
3137 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3138 p_commit IN VARCHAR2 := FND_API.g_false,
3139 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3140 p_entity_id IN NUMBER,
3141 x_return_status OUT nocopy VARCHAR2,
3142 x_msg_count OUT nocopy NUMBER,
3143 x_msg_data OUT nocopy VARCHAR2
3144 )
3145 IS
3146 CURSOR all_auditable_units
3147 IS
3148 SELECT audit_v.company_code,audit_v.lob_code,audit_v.organization_id
3149 FROM amw_audit_units_v audit_v;
3150
3151 CURSOR get_all_processes(p_org_id NUMBER)
3152 IS
3153 SELECT DISTINCT org_v.child_process_id as process_id
3154 FROM amw_curr_app_hierarchy_org_v org_v,amw_audit_units_v audit_v
3155 WHERE org_v.parent_process_id = -2
3156 AND audit_v.organization_id = org_v.child_organization_id
3157 AND audit_v.organization_id = p_org_id;
3158
3159 l_sub_vs AMW_AUDIT_UNITS_V.subsidiary_valueset%TYPE;
3160 l_lob_vs AMW_AUDIT_UNITS_V.subsidiary_valueset%TYPE;
3161 l_sub_tbl sub_tbl_type;
3162 l_lob_tbl lob_tbl_type;
3163 l_org_tbl org_tbl_type;
3164 l_process_tbl process_tbl_type;
3165
3166 l_api_name CONSTANT VARCHAR2(30) := 'populate_scope';
3167 l_api_version_number CONSTANT NUMBER := 1.0;
3168
3169 l_position NUMBER;
3170 l_temp_proc_id NUMBER;
3171 BEGIN
3172 SAVEPOINT POPULATE_SCOPE;
3173
3174 -- Standard call to check for call compatibility.
3175 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3176 p_api_version_number,
3177 l_api_name,
3178 G_PKG_NAME)
3179 THEN
3180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3181 END IF;
3182
3183 -- Initialize message list if p_init_msg_list is set to TRUE.
3184 IF FND_API.to_Boolean( p_init_msg_list )
3185 THEN
3186 FND_MSG_PUB.initialize;
3187 END IF;
3188
3189 -- Initialize API return status to SUCCESS
3190 x_return_status := FND_API.G_RET_STS_SUCCESS;
3191
3192 l_sub_vs := FND_PROFILE.value('AMW_SUBSIDIARY_AUDIT_UNIT');
3193 l_lob_vs := FND_PROFILE.value('AMW_LOB_AUDIT_UNITS');
3194
3195 l_position := 1;
3196 FOR each_unit IN all_auditable_units
3197 LOOP
3198 l_sub_tbl(l_position).subsidiary_code := each_unit.company_code;
3199 l_lob_tbl(l_position).lob_code := each_unit.lob_code;
3200 l_org_tbl(l_position).org_id := each_unit.organization_id;
3201
3202 l_position := l_position + 1;
3203 END LOOP;
3204
3205 l_position := 1;
3206 FOR i IN 1..l_org_tbl.count
3207 LOOP
3208 OPEN get_all_processes(l_org_tbl(i).org_id);
3209 LOOP
3210 FETCH get_all_processes INTO l_temp_proc_id;
3211 EXIT WHEN get_all_processes%NOTFOUND;
3212
3213 l_process_tbl(l_position).process_id := l_temp_proc_id;
3214 l_position := l_position + 1;
3215 END LOOP;
3216 CLOSE get_all_processes;
3217 END LOOP;
3218
3219 add_scope
3220 (
3221 p_entity_type => 'BUSIPROC_CERTIFICATION',
3222 p_entity_id => p_entity_id,
3223 p_sub_vs => l_sub_vs,
3224 p_lob_vs => l_lob_vs,
3225 p_subsidiary_tbl => l_sub_tbl,
3226 p_lob_tbl => l_lob_tbl,
3227 p_org_tbl => l_org_tbl,
3228 p_process_tbl => l_process_tbl,
3229 x_return_status => x_return_status,
3230 x_msg_count => x_msg_count,
3231 x_msg_data => x_msg_data
3232 );
3233
3234 EXCEPTION WHEN OTHERS
3235 THEN
3236 ROLLBACK TO POPULATE_SCOPE;
3237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3238 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3239 FND_MSG_PUB.Count_And_Get(
3240 p_encoded => FND_API.G_FALSE,
3241 p_count => x_msg_count,
3242 p_data => x_msg_data);
3243
3244 END populate_scope;
3245
3246 PROCEDURE manage_processes
3247 (
3248 p_api_version_number IN NUMBER,
3249 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3250 p_commit IN VARCHAR2 := FND_API.g_false,
3251 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3252 p_entity_type IN VARCHAR2,
3253 p_entity_id IN NUMBER,
3254 p_organization_id IN NUMBER,
3255 p_proc_hier_tbl IN PROC_HIER_TBL_TYPE,
3256 x_return_status OUT nocopy VARCHAR2,
3257 x_msg_count OUT nocopy NUMBER,
3258 x_msg_data OUT nocopy VARCHAR2
3259 )
3260 IS
3261 CURSOR c_scope_org IS
3262 SELECT SUBSIDIARY_VS, SUBSIDIARY_CODE,LOB_VS, LOB_CODE
3263 FROM AMW_EXECUTION_SCOPE
3264 WHERE entity_type = p_entity_type
3265 AND entity_id = p_entity_id
3266 AND organization_id = p_organization_id
3267 FOR UPDATE NOWAIT;
3268
3269 CURSOR get_proc_org_rev_id(p_process_id NUMBER) IS
3270 SELECT aorv.child_process_org_rev_id
3271 FROM amw_curr_app_hierarchy_org_v aorv,amw_execution_scope aes,amw_lookups lk
3272 WHERE aorv.child_organization_id = aes.organization_id(+)
3273 AND aorv.child_process_id = aes.process_id(+)
3274 AND aorv.child_process_id <> -2
3275 AND lk.lookup_type = 'AMW_SCOPE_ENTITY_TYPE'
3276 AND lk.lookup_code = 'PROCESS'
3277 AND aes.entity_type(+) = p_entity_type
3278 AND aes.entity_id(+) = p_entity_id
3279 AND aorv.child_organization_id = p_organization_id
3280 AND aorv.child_process_id = p_process_id;
3281
3282 p_process_tbl process_tbl_type;
3283 p_org_dummy_tbl org_tbl_type;
3284
3285 l_process_org_rev_id NUMBER;
3286
3287 l_subsidiary_vs VARCHAR2(150);
3288 l_sub_code VARCHAR2(150);
3289 l_lob_vs VARCHAR2(150);
3290 l_lob_code VARCHAR2(150);
3291
3292 l_api_name CONSTANT VARCHAR2(30) := 'Manage_Processes';
3293 l_api_version_number CONSTANT NUMBER := 1.0;
3294 l_exists VARCHAR2(1);
3295
3296 l_return_status VARCHAR2(32767);
3297 l_msg_count NUMBER;
3298 l_msg_data VARCHAR2(32767);
3299 BEGIN
3300 -- Standard Start of API savepoint
3301 SAVEPOINT MANAGE_PROCESSES_PVT;
3302
3303 -- Standard call to check for call compatibility.
3304 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3305 p_api_version_number,
3306 l_api_name,
3307 G_PKG_NAME) THEN
3308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3309 END IF;
3310
3311
3312 -- Initialize message list if p_init_msg_list is set to TRUE.
3313 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3314 FND_MSG_PUB.initialize;
3315 END IF;
3316
3317 -- Initialize API return status to SUCCESS
3318 x_return_status := FND_API.G_RET_STS_SUCCESS;
3319
3320 OPEN c_scope_org;
3321 FETCH c_scope_org INTO l_subsidiary_vs, l_sub_code,l_lob_vs, l_lob_code;
3322 CLOSE c_scope_org;
3323
3324 --Step 1: Remove existing processes rows
3325 DELETE FROM amw_execution_scope
3326 WHERE entity_type = p_entity_type
3327 AND entity_id = p_entity_id
3328 AND organization_id = p_organization_id
3329 AND level_id > 3;
3330
3331 --Step 2: Populate new rows
3332 FOR i IN 1..p_proc_hier_tbl.count LOOP
3333
3334 l_process_org_rev_id := null;
3335
3336 OPEN get_proc_org_rev_id(p_proc_hier_tbl(i).process_id);
3337 FETCH get_proc_org_rev_id INTO l_process_org_rev_id;
3338 CLOSE get_proc_org_rev_id;
3339
3340 INSERT INTO AMW_EXECUTION_SCOPE (
3341 EXECUTION_SCOPE_ID,
3342 ENTITY_TYPE,
3343 ENTITY_ID,
3344 CREATED_BY,
3345 CREATION_DATE,
3346 LAST_UPDATE_DATE,
3347 LAST_UPDATED_BY,
3348 LAST_UPDATE_LOGIN,
3349 SCOPE_CHANGED_STATUS,
3350 LEVEL_ID,
3351 SUBSIDIARY_VS,
3352 SUBSIDIARY_CODE,
3353 LOB_VS,
3354 LOB_CODE,
3355 ORGANIZATION_ID,
3356 PROCESS_ID,
3357 TOP_PROCESS_ID,
3358 PARENT_PROCESS_ID,
3359 PROCESS_ORG_REV_ID,
3360 SCOPE_MODIFIED_DATE)
3361 SELECT amw_execution_scope_s.nextval,
3362 p_entity_type,
3363 p_entity_id,
3364 FND_GLOBAL.USER_ID,
3365 SYSDATE,
3366 SYSDATE,
3367 FND_GLOBAL.USER_ID,
3368 FND_GLOBAL.USER_ID,
3369 'C',
3370 p_proc_hier_tbl(i).level_id,
3371 l_subsidiary_vs,
3372 l_sub_code,
3373 l_lob_vs,
3374 l_lob_code,
3375 p_organization_id,
3376 p_proc_hier_tbl(i).process_id,
3377 p_proc_hier_tbl(i).top_process_id,
3378 p_proc_hier_tbl(i).parent_process_id,
3379 l_process_org_rev_id,
3380 SYSDATE
3381 FROM dual where not exists (select 'Y' from amw_execution_scope
3382 where entity_type = p_entity_type and entity_id = p_entity_id
3383 and organization_id = p_organization_id and process_id = p_proc_hier_tbl(i).process_id
3384 and top_process_id = p_proc_hier_tbl(i).top_process_id and parent_process_id = p_proc_hier_tbl(i).parent_process_id
3385 and level_id = p_proc_hier_tbl(i).level_id
3386 and subsidiary_vs = l_subsidiary_vs and subsidiary_code= l_sub_code
3387 and lob_vs = l_lob_vs and lob_code = l_lob_code
3388 and process_org_rev_id = l_process_org_rev_id);
3389
3390 --Move process id to a temporary PLS table
3391 p_process_tbl(i).process_id := p_proc_hier_tbl(i).process_id;
3392 END LOOP;
3393
3394 --Step 2.1: Set the scope_changed_status for the org also.
3395 UPDATE amw_execution_scope
3396 SET scope_changed_status = 'C'
3397 WHERE entity_type = p_entity_type
3398 AND entity_id = p_entity_id
3399 AND organization_id = p_organization_id
3400 AND level_id = 3;
3401
3402 --Step 3: Update rows into denormalized tables
3403 --Step 4: Populate appropriate risks and controls in the association tables
3404 IF p_entity_type = 'PROJECT'
3405 THEN
3406 populate_proj_denorm_tables
3407 (
3408 p_audit_project_id => p_entity_id
3409 );
3410
3411 build_project_audit_task
3412 (
3413 p_api_version_number => 1.0 ,
3414 p_audit_project_id => p_entity_id,
3415 x_return_status => l_return_status,
3416 x_msg_count => l_msg_count,
3417 x_msg_data => l_msg_data
3418 );
3419
3420 ELSIF p_entity_type = 'BUSIPROC_CERTIFICATION'
3421 THEN
3422 populate_denormalized_tables
3423 (
3424 p_entity_type => p_entity_type,
3425 p_entity_id => p_entity_id,
3426 p_org_tbl => p_org_dummy_tbl,
3427 p_process_tbl => p_process_tbl,
3428 p_mode => 'MANAGE'
3429 );
3430
3431 populate_association_tables
3432 (
3433 p_entity_type => p_entity_type,
3434 p_entity_id => p_entity_id,
3435 x_return_status => l_return_status,
3436 x_msg_count => l_msg_count,
3437 x_msg_data => l_msg_data
3438 );
3439
3440 END IF;
3441
3442 raise_scope_update_event(
3443 p_entity_type => p_entity_type,
3444 p_entity_id => p_entity_id,
3445 p_org_id => p_organization_id,
3446 p_mode => 'ManageProc');
3447
3448 EXCEPTION WHEN OTHERS THEN
3449 rollback to MANAGE_PROCESSES_PVT;
3450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3451 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, l_api_name);
3452 FND_MSG_PUB.Count_And_Get(
3453 p_encoded => FND_API.G_FALSE,
3454 p_count => x_msg_count,
3455 p_data => x_msg_data);
3456 END Manage_Processes;
3457
3458 PROCEDURE remove_from_scope
3459 (
3460 p_api_version_number IN NUMBER := 1.0,
3461 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3462 p_commit IN VARCHAR2 := FND_API.g_false,
3463 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3464 p_entity_type IN VARCHAR2,
3465 p_entity_id IN NUMBER,
3466 p_object_id IN NUMBER,
3467 p_object_type IN VARCHAR2,
3468 p_subsidiary_vs IN VARCHAR2,
3469 p_subsidiary_code IN VARCHAR2,
3470 p_LOB_vs IN VARCHAR2,
3471 p_LOB_code IN VARCHAR2,
3472 p_organization_id IN NUMBER,
3473 x_return_status OUT nocopy VARCHAR2,
3474 x_msg_count OUT nocopy NUMBER,
3475 x_msg_data OUT nocopy VARCHAR2
3476 )
3477 IS
3478 CURSOR get_relevant_rows
3479 IS
3480 SELECT 'Y'
3481 FROM AMW_ENTITY_HIERARCHIES
3482 WHERE entity_id = p_entity_id
3483 FOR UPDATE NOWAIT;
3484
3485 CURSOR get_object_type(l_object_id NUMBER, l_entity_id NUMBER)
3486 IS
3487 SELECT object_type
3488 FROM AMW_ENTITY_HIERARCHIES
3489 WHERE object_id = l_object_id
3490 AND entity_id = l_entity_id
3491 AND entity_type = p_entity_type;
3492
3493
3494 l_api_name CONSTANT VARCHAR2(30) := 'remove_from_scope';
3495 l_api_version_number CONSTANT NUMBER := 1.0;
3496
3497 l_exists VARCHAR2(1);
3498 l_object_tbl org_tbl_type;
3499 l_object_type VARCHAR2(32767);
3500
3501 l_return_status VARCHAR2(32767);
3502 l_msg_count NUMBER;
3503 l_msg_data VARCHAR2(32767);
3504
3505 -- To delete all the relevant entries from AMW_EXECUTION_SCOPE TABLE
3506
3507 CURSOR c_audit_unit(p_org_id NUMBER)
3508 IS
3509 SELECT audit_v.company_code,
3510 audit_v.subsidiary_valueset,
3511 audit_v.lob_code,
3512 audit_v.lob_valueset,
3513 audit_v.organization_id
3514 FROM amw_audit_units_v audit_v
3515 WHERE organization_id = p_org_id;
3516
3517 BEGIN
3518
3519 SAVEPOINT REMOVE_FROM_SCOPE;
3520
3521 -- Standard call to check for call compatibility.
3522 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3523 p_api_version_number,
3524 l_api_name,
3525 G_PKG_NAME)
3526 THEN
3527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3528 END IF;
3529
3530 -- Initialize message list if p_init_msg_list is set to TRUE.
3531 IF FND_API.to_Boolean( p_init_msg_list )
3532 THEN
3533 FND_MSG_PUB.initialize;
3534 END IF;
3535
3536 -- Initialize API return status to SUCCESS
3537 x_return_status := FND_API.G_RET_STS_SUCCESS;
3538
3539 --Step 0: Lock relevant rows
3540 OPEN get_relevant_rows;
3541 FETCH get_relevant_rows INTO l_exists;
3542 CLOSE get_relevant_rows;
3543
3544 --Step 1: Delete specified row
3545 DELETE FROM AMW_ENTITY_HIERARCHIES
3546 WHERE object_type = p_object_type
3547 AND object_id = p_object_id
3548 AND entity_type = p_entity_type
3549 AND entity_id = p_entity_id;
3550
3551 --Step 2: Build a stack of child nodes all the way till the leaf node for object_type = 'SUBSIDIARY' or 'LINEOFBUSINESS'
3552 IF (p_object_type = 'SUBSIDIARY' OR p_object_type = 'LINEOFBUSINESS')
3553 THEN
3554
3555 l_object_tbl(1).org_id := p_object_id;
3556 l_object_tbl := find_child_objects(p_entity_type,p_entity_id,p_object_id,p_object_type,l_object_tbl);
3557
3558 --Step 3: Delete parent and child rows as found from stack
3559 FOR each_rec IN 1..l_object_tbl.count
3560 LOOP
3561
3562 DELETE FROM AMW_ENTITY_HIERARCHIES
3563 WHERE object_id = l_object_tbl(each_rec).org_id
3564 AND object_type = p_object_type
3565 AND entity_id = p_entity_id
3566 AND entity_type = p_entity_type;
3567
3568 DELETE FROM AMW_ENTITY_HIERARCHIES
3569 WHERE parent_object_id = l_object_tbl(each_rec).org_id
3570 AND parent_object_type = p_object_type
3571 AND entity_id = p_entity_id
3572 AND entity_type = p_entity_type;
3573
3574 OPEN get_object_type(l_object_tbl(each_rec).org_id, p_entity_id);
3575 FETCH get_object_type INTO l_object_type;
3576 CLOSE get_object_type;
3577
3578 IF ((l_object_type = 'ORGANIZATION') OR
3579 (l_object_type = 'ORG')
3580 )
3581 THEN
3582 remove_orgs_from_scope
3583 (
3584 p_entity_type => p_entity_type,
3585 p_entity_id => p_entity_id,
3586 p_object_id => l_object_tbl(each_rec).org_id,
3587 x_return_status => l_return_status,
3588 x_msg_count => l_msg_count,
3589 x_msg_data => l_msg_data
3590 );
3591 FOR audit_rec IN c_audit_unit(l_object_tbl(each_rec).org_id)
3592 LOOP
3593 If (p_object_type = 'SUBSIDIARY')
3594 THEN
3595 DELETE FROM AMW_EXECUTION_SCOPE WHERE entity_id = p_entity_id
3596 and entity_type = p_entity_type and subsidiary_vs = audit_rec.subsidiary_valueset
3597 and SUBSIDIARY_CODE = audit_rec.company_code;
3598 END IF;
3599
3600 If (p_object_type = 'LINEOFBUSINESS')
3601 THEN
3602 DELETE FROM AMW_EXECUTION_SCOPE WHERE entity_id = p_entity_id
3603 and entity_type = p_entity_type and subsidiary_vs = audit_rec.subsidiary_valueset
3604 and SUBSIDIARY_CODE = audit_rec.company_code
3605 AND NVL(lob_vs, 'AMW_NULL_CODE') = NVL(audit_rec.lob_valueset, NVL(lob_vs, 'AMW_NULL_CODE'))
3606 AND NVL(lob_code, 'AMW_NULL_CODE') = NVL(audit_rec.lob_code, NVL(lob_code, 'AMW_NULL_CODE'))
3607 AND level_id > 1;
3608 END IF;
3609 END LOOP;
3610 END IF;
3611
3612 END LOOP;
3613 --Step 4: Delete all rows from current to leaf for object_type = 'ORGANIZATION' or 'ORG'
3614 ELSE
3615 remove_orgs_from_scope
3616 (
3617 p_entity_type => p_entity_type,
3618 p_entity_id => p_entity_id,
3619 p_object_id => p_object_id,
3620 x_return_status => l_return_status,
3621 x_msg_count => l_msg_count,
3622 x_msg_data => l_msg_data
3623 );
3624 END IF;
3625
3626 --Step 5: Delete all relevant rows in AMW_EXECUTION_SCOPE
3627 DELETE FROM AMW_EXECUTION_SCOPE
3628 WHERE entity_type = p_entity_type
3629 AND entity_id = p_entity_id
3630 AND subsidiary_vs = p_subsidiary_vs
3631 AND subsidiary_code = p_subsidiary_code
3632 AND NVL(lob_vs, 'AMW_NULL_CODE') = NVL(p_lob_vs, NVL(lob_vs, 'AMW_NULL_CODE'))
3633 AND NVL(lob_code, 'AMW_NULL_CODE') = NVL(p_lob_code, NVL(lob_code, 'AMW_NULL_CODE'))
3634 AND NVL(organization_id, -999) = NVL(p_organization_id, NVL(organization_id, -999));
3635
3636 --Step 6: Mark relevant rows in AMW_AUDIT_PROJECTS
3637 IF p_entity_type = 'PROJECT'
3638 THEN
3639 UPDATE amw_audit_projects
3640 SET SCOPE_CHANGED_FLAG = 'Y'
3641 WHERE project_id = p_entity_id;
3642 END IF;
3643
3644 --Step 7: Remove risks and controls in the association tables
3645 populate_association_tables
3646 (
3647 p_entity_type => p_entity_type,
3648 p_entity_id => p_entity_id,
3649 x_return_status => l_return_status,
3650 x_msg_count => l_msg_count,
3651 x_msg_data => l_msg_data
3652 );
3653
3654 raise_scope_update_event(
3655 p_entity_type => p_entity_type,
3656 p_entity_id => p_entity_id,
3657 p_mode => 'RemoveFromScope');
3658
3659 EXCEPTION WHEN OTHERS
3660 THEN
3661 ROLLBACK TO REMOVE_FROM_SCOPE;
3662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3663 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3664 FND_MSG_PUB.Count_And_Get
3665 (
3666 p_encoded => FND_API.G_FALSE,
3667 p_count => x_msg_count,
3668 p_data => x_msg_data
3669 );
3670
3671 END remove_from_scope;
3672
3673 PROCEDURE remove_orgs_from_scope
3674 (
3675 p_api_version_number IN NUMBER := 1.0,
3676 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3677 p_commit IN VARCHAR2 := FND_API.g_false,
3678 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3679 p_entity_type IN VARCHAR2,
3680 p_entity_id IN NUMBER,
3681 p_object_id IN NUMBER,
3682 x_return_status OUT nocopy VARCHAR2,
3683 x_msg_count OUT nocopy NUMBER,
3684 x_msg_data OUT nocopy VARCHAR2
3685 )
3686 IS
3687
3688 l_api_name CONSTANT VARCHAR2(30) := 'remove_orgs_from_scope';
3689 l_api_version_number CONSTANT NUMBER := 1.0;
3690
3691 l_return_status VARCHAR2(32767);
3692 l_msg_count NUMBER;
3693 l_msg_data VARCHAR2(32767);
3694
3695 l_object_tbl org_tbl_type;
3696
3697 BEGIN
3698
3699 SAVEPOINT REMOVE_ORGS_FROM_SCOPE;
3700
3701 -- Standard call to check for call compatibility.
3702 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3703 p_api_version_number,
3704 l_api_name,
3705 G_PKG_NAME)
3706 THEN
3707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3708 END IF;
3709
3710 -- Initialize message list if p_init_msg_list is set to TRUE.
3711 IF FND_API.to_Boolean( p_init_msg_list )
3712 THEN
3713 FND_MSG_PUB.initialize;
3714 END IF;
3715
3716 -- Initialize API return status to SUCCESS
3717 x_return_status := FND_API.G_RET_STS_SUCCESS;
3718
3719 --Step 1: Delete the node 'ORG' AND 'ORGANIZATION' from the hierarchy table
3720 DELETE FROM AMW_ENTITY_HIERARCHIES
3721 WHERE object_id = p_object_id
3722 AND entity_id = p_entity_id
3723 AND entity_type = p_entity_type;
3724
3725 --Step 2: Remove the node from denormalized tables
3726 IF p_entity_type = 'BUSIPROC_CERTIFICATION'
3727 THEN
3728 DELETE FROM AMW_PROC_CERT_EVAL_SUM
3729 WHERE organization_id = p_object_id
3730 AND certification_id = p_entity_id;
3731
3732 DELETE FROM AMW_ORG_CERT_EVAL_SUM
3733 WHERE organization_id = p_object_id
3734 AND certification_id = p_entity_id;
3735 ELSIF p_entity_type = 'PROJECT' THEN
3736 DELETE FROM amw_audit_scope_processes
3737 WHERE organization_id = p_object_id
3738 AND audit_project_id = p_entity_id;
3739
3740 DELETE FROM amw_audit_scope_organizations
3741 WHERE organization_id = p_object_id
3742 AND audit_project_id = p_entity_id;
3743 END IF;
3744
3745 --Step 3: Build a stack of child nodes all the way till the leaf node for object_type = 'ORG'
3746 l_object_tbl(1).org_id := p_object_id;
3747 l_object_tbl := find_child_orgs(p_entity_type,p_entity_id,p_object_id,l_object_tbl);
3748
3749 --Step 4: Delete parent and child rows from hierarchy and denormalized tables as found from stack
3750 FOR each_rec IN 1..l_object_tbl.count
3751 LOOP
3752
3753 DELETE FROM AMW_ENTITY_HIERARCHIES
3754 WHERE object_id = l_object_tbl(each_rec).org_id
3755 AND object_type = 'ORG'
3756 AND parent_object_type = 'ORG'
3757 AND entity_id = p_entity_id
3758 AND entity_type = p_entity_type;
3759
3760 DELETE FROM AMW_ENTITY_HIERARCHIES
3761 WHERE parent_object_id = l_object_tbl(each_rec).org_id
3762 AND parent_object_type = 'ORG'
3763 AND object_type = 'ORG'
3764 AND entity_id = p_entity_id
3765 AND entity_type = p_entity_type;
3766
3767 IF p_entity_type = 'BUSIPROC_CERTIFICATION'
3768 THEN
3769 DELETE FROM AMW_PROC_CERT_EVAL_SUM
3770 WHERE organization_id = l_object_tbl(each_rec).org_id
3771 AND certification_id = p_entity_id;
3772
3773 DELETE FROM AMW_ORG_CERT_EVAL_SUM
3774 WHERE organization_id = l_object_tbl(each_rec).org_id
3775 AND certification_id = p_entity_id;
3776 END IF;
3777
3778 END LOOP;
3779
3780 EXCEPTION WHEN OTHERS
3781 THEN
3782 ROLLBACK TO REMOVE_ORGS_FROM_SCOPE;
3783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3784 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3785 FND_MSG_PUB.Count_And_Get(
3786 p_encoded => FND_API.G_FALSE,
3787 p_count => x_msg_count,
3788 p_data => x_msg_data);
3789
3790 END remove_orgs_from_scope;
3791
3792 FUNCTION find_child_orgs
3793 (
3794 p_entity_type IN VARCHAR2,
3795 p_entity_id IN NUMBER,
3796 p_object_id IN NUMBER,
3797 p_object_tbl IN org_tbl_type
3798 )
3799 RETURN org_tbl_type
3800 IS
3801
3802 CURSOR get_child_orgs(l_entity_id NUMBER, l_object_id NUMBER)
3803 IS
3804 SELECT object_id
3805 FROM AMW_ENTITY_HIERARCHIES
3806 WHERE parent_object_id = l_object_id
3807 AND entity_id = l_entity_id
3808 AND entity_type = p_entity_type
3809 AND object_type = 'ORG'
3810 AND parent_object_type = 'ORG';
3811
3812 l_object_tbl org_tbl_type;
3813
3814 BEGIN
3815
3816 l_object_tbl := p_object_tbl;
3817
3818 FOR each_org in get_child_orgs(p_entity_id, p_object_id)
3819 LOOP
3820 l_object_tbl(l_object_tbl.count + 1).org_id := each_org.object_id;
3821 l_object_tbl := find_child_orgs(p_entity_type,p_entity_id,each_org.object_id,l_object_tbl);
3822 END LOOP;
3823
3824 RETURN l_object_tbl;
3825
3826 END find_child_orgs;
3827
3828 FUNCTION find_child_objects
3829 (
3830 p_entity_type IN VARCHAR2,
3831 p_entity_id IN NUMBER,
3832 p_object_id IN NUMBER,
3833 p_object_type IN VARCHAR2,
3834 p_object_tbl IN org_tbl_type
3835 )
3836 RETURN org_tbl_type
3837 IS
3838
3839 CURSOR get_child_objects(l_entity_id NUMBER, l_object_id NUMBER)
3840 IS
3841 SELECT object_id
3842 FROM AMW_ENTITY_HIERARCHIES
3843 WHERE parent_object_id = l_object_id
3844 AND entity_id = l_entity_id
3845 AND entity_type = p_entity_type
3846 AND parent_object_type = p_object_type;
3847
3848 l_object_tbl org_tbl_type;
3849 BEGIN
3850
3851 l_object_tbl := p_object_tbl;
3852
3853 FOR each_obj in get_child_objects(p_entity_id, p_object_id)
3854 LOOP
3855 l_object_tbl(l_object_tbl.count + 1).org_id := each_obj.object_id;
3856 l_object_tbl := find_child_objects(p_entity_type,p_entity_id,each_obj.object_id,p_object_type,l_object_tbl);
3857 END LOOP;
3858
3859 RETURN l_object_tbl;
3860 END find_child_objects;
3861
3862
3863 function get_assoc_task_id (
3864 p_project_id in number,
3865 p_task_id in number)
3866 return NUMBER is
3867 cursor c_task_id is
3868 select t2.task_id
3869 from pa_projects_all pp,
3870 pa_tasks t1,
3871 pa_tasks t2
3872 where pp.project_id = p_project_id
3873 and t1.project_id = pp.created_from_project_id
3874 and t1.task_id = p_task_id
3875 and t1.task_number = t2.task_number
3876 and t2.project_id = p_project_id;
3877 l_task_id number;
3878 begin
3879 open c_task_id;
3880 fetch c_task_id into l_task_id;
3881 close c_task_id;
3882 return l_task_id;
3883 end;
3884
3885
3886 PROCEDURE populate_proj_denorm_tables (
3887 p_audit_project_id IN NUMBER
3888 ) IS
3889 BEGIN
3890 DELETE FROM amw_audit_scope_organizations
3891 WHERE audit_project_id = p_audit_project_id
3892 AND organization_id NOT IN
3893 (SELECT organization_id
3894 FROM amw_execution_scope
3895 WHERE entity_type = 'PROJECT'
3896 AND entity_id = p_audit_project_id
3897 AND level_id = 3);
3898
3899 INSERT INTO amw_audit_scope_organizations (
3900 audit_project_id,
3901 subsidiary_vs,
3902 subsidiary_code,
3903 lob_vs,
3904 lob_code,
3905 organization_id,
3906 created_by,
3907 creation_date,
3908 last_updated_by,
3909 last_update_date,
3910 last_update_login,
3911 object_version_number)
3912 SELECT distinct p_audit_project_id,
3913 au.subsidiary_valueset,
3914 au.company_code,
3915 au.lob_valueset,
3916 au.lob_code,
3917 au.organization_id,
3918 g_user_id,
3919 sysdate,
3920 g_user_id,
3921 sysdate,
3922 g_login_id,
3923 1
3924 FROM amw_audit_units_v au, amw_execution_scope es
3925 WHERE au.organization_id = es.organization_id
3926 AND es.entity_type = 'PROJECT'
3927 AND es.entity_id = p_audit_project_id
3928 AND es.level_id = 3
3929 AND es.organization_id NOT IN (
3930 SELECT organization_id
3931 FROM amw_audit_scope_organizations
3932 WHERE audit_project_id = p_audit_project_id);
3933
3934 DELETE FROM amw_audit_scope_processes
3935 WHERE audit_project_id = p_audit_project_id
3936 AND (organization_id, process_id) NOT IN
3937 (SELECT organization_id, process_id
3938 FROM amw_execution_scope
3939 WHERE entity_type = 'PROJECT'
3940 AND entity_id = p_audit_project_id
3941 AND process_id IS NOT NULL);
3942
3943 INSERT INTO amw_audit_scope_processes (
3944 audit_project_id,
3945 organization_id,
3946 process_id,
3947 process_org_rev_id,
3948 created_by,
3949 creation_date,
3950 last_updated_by,
3951 last_update_date,
3952 last_update_login,
3953 object_version_number)
3954 SELECT distinct p_audit_project_id,
3955 organization_id,
3956 process_id,
3957 process_org_rev_id,
3958 g_user_id,
3959 sysdate,
3960 g_user_id,
3961 sysdate,
3962 g_login_id,
3963 1
3964 FROM amw_execution_scope
3965 WHERE entity_type = 'PROJECT'
3966 AND entity_id = p_audit_project_id
3967 AND level_id > 3
3968 AND (organization_id, process_id) NOT IN (
3969 SELECT organization_id, process_id
3970 FROM amw_audit_scope_processes
3971 WHERE audit_project_id = p_audit_project_id);
3972
3973
3974 END populate_proj_denorm_tables;
3975
3976
3977 PROCEDURE get_accessible_sub_orgs(
3978 p_user_name IN VARCHAR2,
3979 p_entity_id IN NUMBER,
3980 p_entity_type IN VARCHAR2,
3981 p_org_id IN NUMBER,
3982 px_org_ids IN OUT NOCOPY VARCHAR2)
3983 IS
3984 CURSOR c_sub_orgs IS
3985 SELECT object_id
3986 FROM amw_entity_Hierarchies
3987 WHERE entity_id = p_entity_id
3988 AND entity_type = p_entity_type
3989 AND parent_object_type = 'ORG'
3990 AND parent_object_id = p_org_id;
3991
3992 l_hasAccess VARCHAR2(15);
3993 BEGIN
3994 FOR org_rec IN c_sub_orgs LOOP
3995 l_hasAccess :=FND_DATA_SECURITY.check_function(
3996 p_api_version => 1.0,
3997 p_function => 'AMW_CERTIFY_ORG',
3998 p_object_name => 'AMW_ORGANIZATION',
3999 p_instance_pk1_value => org_rec.object_id,
4000 p_user_name => p_user_name);
4001 IF l_hasAccess = 'T' THEN
4002 px_org_ids := px_org_ids||org_rec.object_id||',';
4003 ELSE
4004 get_accessible_sub_orgs(
4005 p_user_name => p_user_name,
4006 p_entity_id => p_entity_id,
4007 p_entity_type => p_entity_type,
4008 p_org_id => org_rec.object_id,
4009 px_org_ids => px_org_ids);
4010 END IF;
4011 END LOOP;
4012 END get_accessible_sub_orgs;
4013
4014 PROCEDURE get_accessible_root_orgs (
4015 p_entity_id IN NUMBER,
4016 p_entity_type IN VARCHAR2,
4017 x_org_ids OUT NOCOPY VARCHAR2)
4018 IS
4019 CURSOR c_root_orgs IS
4020 SELECT object_id
4021 FROM amw_entity_Hierarchies
4022 WHERE entity_id = p_entity_id
4023 AND entity_type = p_entity_type
4024 AND parent_object_type = 'ROOTNODE'
4025 AND object_type = 'ORG';
4026
4027 l_user_name VARCHAR2(200);
4028 l_hasAccess VARCHAR2(15);
4029 BEGIN
4030 /*04.11.2006 npanandi: bug 5142733 fix -- commenting below if-else
4031 because in R12, only FND_GLOBAL.user_name is allowed.
4032 the rest are deprecated
4033 */
4034 /*IF FND_GLOBAL.party_id IS NOT NULL THEN
4035 l_user_name := 'HZ_PARTY:'||FND_GLOBAL.party_id;
4036 ELSE
4037 */
4038 l_user_name := FND_GLOBAL.user_name;
4039 /*END IF;
4040 */
4041
4042 FOR org_rec IN c_root_orgs LOOP
4043 l_hasAccess :=FND_DATA_SECURITY.check_function(
4044 p_api_version => 1.0,
4045 p_function => 'AMW_CERTIFY_ORG',
4046 p_object_name => 'AMW_ORGANIZATION',
4047 p_instance_pk1_value => org_rec.object_id,
4048 p_user_name => l_user_name);
4049 IF l_hasAccess = 'T' THEN
4050 x_org_ids := x_org_ids||org_rec.object_id||',';
4051 ELSE
4052 get_accessible_sub_orgs(
4053 p_user_name => l_user_name,
4054 p_entity_id => p_entity_id,
4055 p_entity_type => p_entity_type,
4056 p_org_id => org_rec.object_id,
4057 px_org_ids => x_org_ids);
4058 END IF;
4059 END LOOP;
4060
4061 END get_accessible_root_orgs;
4062
4063
4064 FUNCTION Has_Org_Access_in_hier (
4065 p_is_global_owner IN VARCHAR2,
4066 p_org_id IN NUMBER)
4067 RETURN VARCHAR2 IS
4068
4069 l_user_name VARCHAR2(200);
4070 l_hasAccess VARCHAR2(15);
4071
4072 BEGIN
4073 IF p_is_global_owner = 'Y' THEN
4074 -- OR fnd_profile.value('AMW_DATA_SECURITY_SWITCH') <> 'Y' THEN
4075 return 'Y';
4076 ELSE
4077 /*04.11.2006 npanandi: bug 5142733 fix -- commenting below if-else
4078 because in R12, only FND_GLOBAL.user_name is allowed.
4079 the rest are deprecated
4080 */
4081 /*IF FND_GLOBAL.party_id IS NOT NULL THEN
4082 l_user_name := 'HZ_PARTY:'||FND_GLOBAL.party_id;
4083 ELSE*/
4084 l_user_name := FND_GLOBAL.user_name;
4085 /*END IF;
4086 */
4087 l_hasAccess :=FND_DATA_SECURITY.check_function(
4088 p_api_version => 1.0,
4089 p_function => 'AMW_CERTIFY_ORG',
4090 p_object_name => 'AMW_ORGANIZATION',
4091 p_instance_pk1_value => p_org_Id,
4092 p_user_name => l_user_name);
4093 IF l_hasAccess = 'T' THEN
4094 return 'Y';
4095 END IF;
4096 END IF;
4097 return 'N';
4098 END Has_Org_Access_in_hier;
4099
4100
4101 PROCEDURE get_accessible_sub_procs(
4102 p_user_name IN VARCHAR2,
4103 p_entity_id IN NUMBER,
4104 p_entity_type IN VARCHAR2,
4105 p_org_id IN NUMBER,
4106 p_proc_id IN NUMBER,
4107 px_proc_ids IN OUT NOCOPY VARCHAR2)
4108 IS
4109 CURSOR c_sub_procs IS
4110 SELECT process_id
4111 FROM amw_execution_scope
4112 WHERE entity_id = p_entity_id
4113 AND entity_type = p_entity_type
4114 AND organization_id = p_org_id
4115 AND parent_process_id = p_proc_id;
4116
4117 l_hasAccess VARCHAR2(15);
4118 BEGIN
4119 FOR proc_rec IN c_sub_procs LOOP
4120 l_hasAccess :=FND_DATA_SECURITY.check_function(
4121 p_api_version => 1.0,
4122 p_function => 'AMW_CERTIFY_ORG_PROCESS',
4123 p_object_name => 'AMW_PROCESS_ORGANIZATION',
4124 p_instance_pk1_value => p_org_id,
4125 p_instance_pk2_value => proc_rec.process_id,
4126 p_user_name => p_user_name);
4127 IF l_hasAccess = 'T' THEN
4128 px_proc_ids := px_proc_ids||proc_rec.process_id||',';
4129 ELSE
4130 get_accessible_sub_procs(
4131 p_user_name => p_user_name,
4132 p_entity_id => p_entity_id,
4133 p_entity_type => p_entity_type,
4134 p_org_id => p_org_id,
4135 p_proc_id => proc_rec.process_id,
4136 px_proc_ids => px_proc_ids);
4137 END IF;
4138 END LOOP;
4139 END get_accessible_sub_procs;
4140
4141 PROCEDURE get_accessible_root_procs (
4142 p_entity_id IN NUMBER,
4143 p_entity_type IN VARCHAR2,
4144 p_org_id IN NUMBER,
4145 x_proc_ids OUT NOCOPY VARCHAR2)
4146 IS
4147 CURSOR c_root_procs IS
4148 SELECT process_id
4149 FROM amw_execution_scope
4150 WHERE entity_id = p_entity_id
4151 AND entity_type = p_entity_type
4152 AND level_id=4;
4153
4154 l_user_name VARCHAR2(200);
4155 l_hasAccess VARCHAR2(15);
4156 BEGIN
4157 /*04.11.2006 npanandi: bug 5142733 fix -- commenting below if-else
4158 because in R12, only FND_GLOBAL.user_name is allowed.
4159 the rest are deprecated
4160 */
4161 /* IF FND_GLOBAL.party_id IS NOT NULL THEN
4162 l_user_name := 'HZ_PARTY:'||FND_GLOBAL.party_id;
4163 ELSE
4164 */
4165 l_user_name := FND_GLOBAL.user_name;
4166 /*END IF;*/
4167
4168 FOR proc_rec IN c_root_procs LOOP
4169 l_hasAccess :=FND_DATA_SECURITY.check_function(
4170 p_api_version => 1.0,
4171 p_function => 'AMW_CERTIFY_ORG_PROCESS',
4172 p_object_name => 'AMW_PROCESS_ORGANIZATION',
4173 p_instance_pk1_value => p_org_id,
4174 p_instance_pk2_value => proc_rec.process_id,
4175 p_user_name => l_user_name);
4176 IF l_hasAccess = 'T' THEN
4177 x_proc_ids := x_proc_ids||proc_rec.process_id||',';
4178 ELSE
4179 get_accessible_sub_procs(
4180 p_user_name => l_user_name,
4181 p_entity_id => p_entity_id,
4182 p_entity_type => p_entity_type,
4183 p_org_id => p_org_id,
4184 p_proc_id => proc_rec.process_id,
4185 px_proc_ids => x_proc_ids);
4186 END IF;
4187 END LOOP;
4188
4189 END get_accessible_root_procs;
4190
4191 END amw_scope_pvt;
4192