[Home] [Help]
PACKAGE BODY: APPS.AMW_WF_HIERARCHY_PKG
Source
1 PACKAGE BODY AMW_WF_HIERARCHY_PKG as
2 /*$Header: amwwfhrb.pls 120.1 2005/09/19 15:25:30 appldev noship $*/
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_WF_HIERARCHY_PKG';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwwfhrb.pls';
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9
10 -- have to define these as global because of recursion.
11 -- make sure to free the memory after use
12
13 type t_parent_name IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
14 v_parent_name t_parent_name;
15 type t_child_name IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
16 v_child_name t_child_name;
17 type t_transition_parent_list IS table of wf_activity_transitions.from_process_activity%type INDEX BY BINARY_INTEGER;
18 v_transition_parent_list t_transition_parent_list;
19 type t_org_parent_id IS table of Amw_Process_Org_Relations.parent_process_id%type INDEX BY BINARY_INTEGER;
20 v_org_parent_id t_org_parent_id;
21 type t_org_child_id IS table of Amw_Process_Org_Relations.child_process_id%type INDEX BY BINARY_INTEGER;
22 v_org_child_id t_org_child_id;
23
24 type t_parent_id IS table of amw_process.process_id%type;
25 v_parent_id t_parent_id;
26 type t_child_id IS table of amw_process.process_id%type;
27 v_child_id t_child_id;
28
29 oldCountProc number := 0;
30 oldCountProcDown number := 0;
31 oldCount number := 0;
32 oldCountDown number := 0;
33
34 child_num number := 0;
35 parent_num number := 0;
36 transition_order number := 0;
37 input_instance_id number := 0;
38
39 org_parent_num number := 0;
40 org_child_num number := 0;
41
42 oldCount1 number := 0;
43
44 ------------------------------------------------------------------------------------------------------------
45 ------------------------------------------------------------------------------------------------------------
46
47
48 procedure write_amw_process (
49 p_process_name IN VARCHAR2,
50 p_SIGNIFICANT_PROCESS_FLAG IN VARCHAR2,
51 p_STANDARD_PROCESS_FLAG IN VARCHAR2,
52 p_APPROVAL_STATUS IN VARCHAR2,
53 p_CERTIFICATION_STATUS IN VARCHAR2,
54 p_PROCESS_OWNER_ID IN NUMBER,
55 p_PROCESS_CATEGORY IN VARCHAR2,
56 p_APPLICATION_OWNER_ID IN NUMBER,
57 p_FINANCE_OWNER_ID IN NUMBER,
58 p_commit in varchar2 := FND_API.G_FALSE,
59 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
60 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
61 x_return_status out nocopy varchar2,
62 x_msg_count out nocopy number,
63 x_msg_data out nocopy varchar2
64 ) is
65
66 L_API_NAME CONSTANT VARCHAR2(30) := 'write_amw_process';
67
68 -- 8.1.7 compatibility issue
69 /*
70 type t_name IS table of amw_process.name%type INDEX BY varchar2(30);
71 v_name t_name;
72
73 type t_amwp_name is table of amw_process.name%type;
74 v_amwp_name t_amwp_name;
75 */
76 -- 8.1.7 compatibility issue
77 /*
78 type t_final_list IS table of amw_process.name%type INDEX BY varchar2(30);
79 v_final_list t_final_list;
80 */
81 type t_forall_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
82 v_forall_list t_forall_list;
83
84 type t_forall_final_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
85 v_forall_final_list t_forall_final_list;
86
87 -- 8.1.7 compatibility issue
88 /*
89 cursor c_amwp_name is
90 select name
91 from amw_process;
92 */
93
94 hash_value NUMBER;
95 v_index NUMBER;
96 xst BOOLEAN;
97 insert_row_cnt NUMBER;
98 l_std_process_flag varchar2(30);
99
100 l_return_status varchar2(10);
101 l_msg_count number;
102 l_msg_data varchar2(4000);
103 l_dummy number;
104 exists_in_final boolean;
105 final_row_cnt number;
106
107
108 begin
109
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111 IF FND_API.to_Boolean( p_init_msg_list ) THEN
112 FND_MSG_PUB.initialize;
113 END IF;
114 IF FND_GLOBAL.User_Id IS NULL THEN
115 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
116 RAISE FND_API.G_EXC_ERROR;
117 END IF;
118
119 select fnd_profile.value('AMW_SET_STD_PROCESS') into l_std_process_flag from dual;
120
121 -- 8.1.7 compatibility issue
122 /*
123 open c_amwp_name;
124 loop
125 fetch c_amwp_name bulk collect into v_amwp_name;
126 exit when c_amwp_name%notfound;
127 end loop;
128 close c_amwp_name;
129
130
131 v_index := v_amwp_name.first;
132 while v_index <= v_amwp_name.last
133 loop
134 -- hash_value := dbms_utility.get_hash_value(v_amwp_name(v_index), 1000, 5625);
135 v_name(v_amwp_name(v_index)) := v_amwp_name(v_index);
136 v_index := v_amwp_name.next(v_index);
137 end loop;
138 */
139 find_hierarchy_children(p_process_name);
140 find_hierarchy_parent(p_process_name);
141
142 insert_row_cnt := 0;
143
144 -- v_final_list is being used to weed out duplicates
145 FOR i IN 1..v_child_name.count LOOP
146 -- 8.1.7 compatibility issue
147 /*
148 --kosriniv xst := v_name.exists(dbms_utility.get_hash_value(v_child_name(i), 1000, 5625));
149 xst := v_name.exists(v_child_name(i));
150 if xst = false then
151 --kosriniv v_final_list(dbms_utility.get_hash_value(v_child_name(i), 1000, 5625)) := v_child_name(i);
152 v_final_list(v_child_name(i)) := v_child_name(i);
153 end if;
154 */
155 begin
156 xst := false;
157 select 1
158 into l_dummy
159 from amw_process
160 where name = v_child_name(i);
161 xst := true;
162 exception
163 when no_data_found then
164 xst := false;
165 when too_many_rows then -- this is of course improbabable, still...
166 xst := true;
167 end;
168 if xst = false then
169 insert_row_cnt := insert_row_cnt + 1;
170 v_forall_list(insert_row_cnt) := v_child_name(i);
171 end if;
172
173 END LOOP;
174
175 FOR j IN 1..v_parent_name.count LOOP
176 -- 8.1.7 compatibility issue
177 /*
178 --kosriniv xst := v_name.exists(dbms_utility.get_hash_value(v_parent_name(j), 1000, 5625));
179 xst := v_name.exists(v_parent_name(j));
180 if (xst = false) AND (v_parent_name(j) <> 'ROOT') then
181 --kosriniv v_final_list(dbms_utility.get_hash_value(v_parent_name(j), 1000, 5625)) := v_parent_name(j);
182 v_final_list(v_parent_name(j)) := v_parent_name(j);
183 end if;
184 */
185 begin
186 xst := false;
187 select 1
188 into l_dummy
189 from amw_process
190 where name = v_parent_name(j);
191 xst := true;
192 exception
193 when no_data_found then
194 xst := false;
195 when too_many_rows then -- this is of course improbabable, still...
196 xst := true;
197 end;
198 if (xst = false) AND (v_parent_name(j) <> 'ROOT') then
199 insert_row_cnt := insert_row_cnt + 1;
200 v_forall_list(insert_row_cnt) := v_parent_name(j);
201 end if;
202
203 END LOOP;
204
205
206 -- added by abedajna
207 -- but now forall list may contain duplicate values. Let's weed those duplicates out.
208 -- just to keep things simple, I'll use a double loop
209 final_row_cnt := 0;
210 FOR i IN 1..v_forall_list.count LOOP
211 exists_in_final := false;
212 FOR j IN 1..v_forall_final_list.count LOOP
213 if v_forall_final_list(j) = v_forall_list(i) then
214 exists_in_final := true;
215 exit;
216 end if;
217 end loop;
218 if exists_in_final = false then
219 final_row_cnt := final_row_cnt + 1;
220 v_forall_final_list(final_row_cnt) := v_forall_list(i);
221 end if;
222 END LOOP;
223
224
225
226 -- 8.1.7 compatibility issue
227 /*
228 v_index := v_final_list.first;
229 insert_row_cnt := 0;
230 while v_index <= v_final_list.last
231 loop
232 insert_row_cnt := insert_row_cnt + 1;
233 v_forall_list(insert_row_cnt) := v_final_list(v_index);
234 v_index := v_final_list.next(v_index);
235 end loop;
236 */
237
238 /* v_index := v_forall_list.first;
239 dbms_output.put_line('PRINTING FORALL LIST');
240 while v_index <= v_forall_list.last
241 loop
242 dbms_output.put_line('index: '||v_index||' value: '||v_forall_list(v_index));
243 v_index := v_forall_list.next(v_index);
244 end loop; */
245
246
247 --FORALL v_ind IN v_forall_list.FIRST..v_forall_list.LAST
248 FORALL v_ind IN 1..final_row_cnt
249 INSERT INTO amw_process( PROCESS_REV_ID,
250 PROCESS_ID,
251 SIGNIFICANT_PROCESS_FLAG,
252 STANDARD_PROCESS_FLAG,
253 APPROVAL_STATUS,
254 CERTIFICATION_STATUS,
255 PROCESS_OWNER_ID,
256 PROCESS_CATEGORY,
257 APPLICATION_OWNER_ID,
258 FINANCE_OWNER_ID,
259 LAST_UPDATE_DATE,
260 LAST_UPDATED_BY,
261 CREATION_DATE,
262 CREATED_BY,
263 LAST_UPDATE_LOGIN,
264 OBJECT_VERSION_NUMBER,
265 ITEM_TYPE,
266 NAME )
267 VALUES
268 (AMW_PROCESS_S.nextval,
269 AMW_PROCESS_S.nextval,
270 p_SIGNIFICANT_PROCESS_FLAG,
271 l_std_process_flag,
272 p_APPROVAL_STATUS,
273 p_CERTIFICATION_STATUS,
274 decode(p_PROCESS_OWNER_ID, -1, to_number(null), p_PROCESS_OWNER_ID),
275 p_PROCESS_CATEGORY,
276 decode(p_APPLICATION_OWNER_ID, -1, to_number(null), p_APPLICATION_OWNER_ID),
277 decode(p_FINANCE_OWNER_ID, -1, to_number(null), p_FINANCE_OWNER_ID),
278 sysdate,
279 G_USER_ID,
280 sysdate,
281 G_USER_ID,
282 G_LOGIN_ID,
283 1,
284 'AUDITMGR',
285 v_forall_final_list(v_ind));
286
287
288 v_parent_name.delete;
289 parent_num := 0;
290 v_child_name.delete;
291 child_num := 0;
292 --v_name.delete;
293 --v_amwp_name.trim;
294
295 synch_hierarchy_amw_process( l_return_status, l_msg_count, l_msg_data);
296
297 exception
298
299 WHEN amw_deadlock_detected THEN
300 ROLLBACK;
301 x_return_status := FND_API.G_RET_STS_ERROR;
302 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
303
304 WHEN FND_API.G_EXC_ERROR THEN
305 ROLLBACK;
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
308
309 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
310 ROLLBACK;
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
313
314 WHEN OTHERS THEN
315 ROLLBACK;
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
317 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
318 THEN
319 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
320 END IF;
321 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
322
323 end write_amw_process;
324
325
326 ------------------------------------------------------------------------------------------------------------
327 ------------------------------------------------------------------------------------------------------------
328
329
330 procedure find_hierarchy_children(p_process_name in varchar2)
331 is
332 cursor c1 (l_name varchar2) is
333 select CHILD_PROCESS_NAME
334 from amw_wf_hierarchy_v
335 where PARENT_PROCESS_NAME=l_name;
336
337 c1_rec c1%rowtype;
338
339 begin
340 for c1_rec in c1(p_process_name) loop
341 exit when c1%notfound;
342 child_num := child_num + 1;
343 v_child_name(child_num) := c1_rec.CHILD_PROCESS_NAME;
344 find_hierarchy_children(p_process_name =>c1_rec.CHILD_PROCESS_NAME);
345 end loop;
346 end find_hierarchy_children;
347
348
349 ------------------------------------------------------------------------------------------------------------
350 ------------------------------------------------------------------------------------------------------------
351
352
353 procedure find_hierarchy_parent(p_process_name in varchar2)
354 is
355 cursor c1 (l_name varchar2) is
356 select PARENT_PROCESS_NAME
357 from amw_wf_hierarchy_v
358 where CHILD_PROCESS_NAME=l_name;
359
360 c1_rec c1%rowtype;
361
362 begin
363 for c1_rec in c1(p_process_name) loop
364 exit when c1%notfound;
365 parent_num := parent_num + 1;
366 v_parent_name(parent_num) := c1_rec.PARENT_PROCESS_NAME;
367 find_hierarchy_parent(p_process_name =>c1_rec.PARENT_PROCESS_NAME);
368 end loop;
369
370 end find_hierarchy_parent;
371
372
373 ------------------------------------------------------------------------------------------------------------
374 ------------------------------------------------------------------------------------------------------------
375
376
380 is
377 procedure synch_hierarchy_amw_process( x_return_status out nocopy varchar2,
378 x_msg_count out nocopy number,
379 x_msg_data out nocopy varchar2)
381 begin
382 x_return_status := 'S';
383 x_msg_count := 0;
384 x_msg_data := null;
385 null;
386 end synch_hierarchy_amw_process;
387
388
389 ------------------------------------------------------------------------------------------------------------
390 ------------------------------------------------------------------------------------------------------------
391
392
393 /*
394 ** procedure synch_hierarchy_amw_process(errbuf OUT NOCOPY VARCHAR2,
395 ** retcode OUT NOCOPY NUMBER)
396 ** is
397 **
398 ** type t_amwp_name is table of amw_process.name%type;
399 ** v_amwp_name t_amwp_name;
400 ** type t_delete_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
401 ** v_delete_list t_delete_list;
402 ** type t_child_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
403 ** v_child_list t_child_list;
404 **
405 ** root_process_name amw_process.name%type;
406 ** v_index NUMBER;
407 ** xst BOOLEAN;
408 ** delete_row_cnt NUMBER;
409 ** conc_status BOOLEAN;
410 **
411 ** cursor c_amwp_name is
412 ** select name
413 ** from amw_process;
414 **
415 ** begin
416 **
417 ** retcode := 0;
418 ** errbuf := null;
419 **
420 ** open c_amwp_name;
421 ** loop
422 ** fetch c_amwp_name bulk collect into v_amwp_name;
423 ** exit when c_amwp_name%notfound;
424 ** end loop;
425 ** close c_amwp_name;
426 **
427 **
428 ** select name
429 ** into root_process_name
430 ** from amw_process
431 ** where process_id = -1;
432 **
433 ** find_hierarchy_children(root_process_name);
434 **
435 ** FOR i IN 1..v_child_name.count LOOP
436 ** v_child_list(dbms_utility.get_hash_value(v_child_name(i), 1000, 5625)) := v_child_name(i);
437 ** END LOOP;
438 **
439 ** delete_row_cnt := 0;
440 ** v_index := v_amwp_name.first;
441 ** while v_index <= v_amwp_name.last
442 ** loop
443 ** xst := v_child_list.exists(dbms_utility.get_hash_value(v_amwp_name(v_index), 1000, 5625));
444 ** if ( (xst = false) AND (root_process_name <> v_amwp_name(v_index)) ) then
445 ** delete_row_cnt := delete_row_cnt + 1;
446 ** v_delete_list(delete_row_cnt) := v_amwp_name(v_index);
447 ** end if;
448 ** v_index := v_amwp_name.next(v_index);
449 ** end loop;
450 **
451 **
452 ** v_index := v_delete_list.first;
453 ** fnd_file.put_line(fnd_file.log, 'PRINTING DELETE LIST');
454 ** while v_index <= v_delete_list.last
455 ** loop
456 ** fnd_file.put_line(fnd_file.log, 'index: '||v_index||' value: '||v_delete_list(v_index));
457 ** v_index := v_delete_list.next(v_index);
458 ** end loop;
459 **
460 **
461 ** FORALL v_ind IN 1..delete_row_cnt
462 ** DELETE from amw_process
463 ** where name = v_delete_list(v_ind);
464 **
465 **
466 ** v_child_name.delete;
467 ** child_num := 0;
468 ** v_child_list.delete;
469 ** v_delete_list.delete;
470 ** v_amwp_name.trim;
471 **
472 ** commit;
473 **
474 ** exception
475 ** when others then
476 ** retcode := 1;
477 ** errbuf := SUBSTR(SQLERRM, 1,240);
478 ** fnd_file.put_line(fnd_file.log,errbuf);
479 ** conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
480 **
481 ** end synch_hierarchy_amw_process;
482 */
483
484 ------------------------------------------------------------------------------------------------------------
485 ------------------------------------------------------------------------------------------------------------
486
487
488 function find_transition_order(p_instance_id in number) return number
489 is
490 begin
491 transition_order := 0;
492 input_instance_id := p_instance_id;
493 if p_instance_id is null then return 0; end if;
494 find_transition_children(p_instance_id => p_instance_id);
495 v_transition_parent_list.delete;
496 return transition_order;
497 end find_transition_order;
498
499
500 ------------------------------------------------------------------------------------------------------------
501 ------------------------------------------------------------------------------------------------------------
502
503
504 procedure find_transition_children(p_instance_id in number)
505 is
506 cursor ct (l_instance_id number) is
507 select to_process_activity
508 from wf_activity_transitions
512
509 where from_process_activity = l_instance_id;
510
511 ct_rec ct%rowtype;
513 begin
514 for ct_rec in ct(p_instance_id) loop
515 -- check for loops. If loops exist, IGNORE the last arrow that leads back to the parent.
516 -- A loop means that P is a child of P, but P as a child of P does not contribute to
517 -- increase the transition children count.
518 exit when ct%notfound or v_transition_parent_list.exists( ct_rec.to_process_activity );
519 if ct_rec.to_process_activity <> input_instance_id then
520 transition_order := transition_order + 1;
521 end if;
522 v_transition_parent_list( ct_rec.to_process_activity ) := ct_rec.to_process_activity;
523 -- dbms_output.put_line('transition_order: '||transition_order||' to_process_activity: '||ct_rec.to_process_activity);
524 find_transition_children(p_instance_id =>ct_rec.to_process_activity);
525 end loop;
526
527 end find_transition_children;
528
529
530 ------------------------------------------------------------------------------------------------------------
531 ------------------------------------------------------------------------------------------------------------
532
533 procedure find_org_hierarchy_parent(p_org_id in number, p_process_id in number)
534 is
535 cursor c10 (l_org_id number, l_process_id number) is
536 select PARENT_PROCESS_ID
537 from Amw_Process_Org_Relations
538 where CHILD_PROCESS_ID=l_process_id
539 and organization_id = l_org_id;
540
541 c10_rec c10%rowtype;
542
543 begin
544 for c10_rec in c10(p_org_id, p_process_id) loop
545 exit when c10%notfound;
546 org_parent_num := org_parent_num + 1;
547 v_org_parent_id(org_parent_num) := c10_rec.PARENT_PROCESS_ID;
548 find_org_hierarchy_parent(p_org_id => p_org_id, p_process_id => c10_rec.PARENT_PROCESS_ID);
549 end loop;
550 end find_org_hierarchy_parent;
551
552
553
554 ------------------------------------------------------------------------------------------------------------
555 ------------------------------------------------------------------------------------------------------------
556
557
558 procedure find_org_hierarchy_children(p_org_id in number, p_process_id in number)
559 is
560 cursor c11 (l_org_id number, l_process_id number) is
561 select CHILD_PROCESS_ID
562 from Amw_Process_Org_Relations
563 where PARENT_PROCESS_ID=l_process_id
564 and organization_id = l_org_id;
565
566 c11_rec c11%rowtype;
567
568 begin
569 for c11_rec in c11(p_org_id, p_process_id) loop
570 exit when c11%notfound;
571 org_child_num := org_child_num + 1;
572 v_org_child_id(org_child_num) := c11_rec.CHILD_PROCESS_ID;
573 find_org_hierarchy_children(p_org_id => p_org_id, p_process_id => c11_rec.CHILD_PROCESS_ID);
574 end loop;
575 end find_org_hierarchy_children;
576
577
578
579 ------------------------------------------------------------------------------------------------------------
580 ------------------------------------------------------------------------------------------------------------
581
582
583
584 procedure reset_process_risk_ctrl_count is
585
586 type rec_amw_counts IS record
587 (process_id amw_process.process_id%type,
588 no_assoc_risks amw_process.risk_count%type,
589 no_assoc_controls amw_process.control_count%type,
590 risk_count amw_process.risk_count%type,
591 control_count amw_process.control_count%type);
592
593
594 v_rec_amw_counts rec_amw_counts;
595 type t_amw_counts IS table of rec_amw_counts index by binary_integer;
596 v_amw_counts t_amw_counts;
597
598 cursor c_populate_amw_counts is
599 select process_id, risk_count, control_count, 0, 0 from amw_process;
600
601 v_index NUMBER;
602 l_risk_count number;
603 l_control_count number;
604 insert_row_cnt number;
605
606 begin
607
608 -- updating every process in amw_process with its risk/ctrl count
609
610 update amw_process ap
611 set risk_count =
612 (select count(ara.risk_id)
613 from amw_risk_associations ara
614 where pk1 = ap.process_id
615 and object_type = 'PROCESS'),
616 control_count =
617 (select count(distinct aca.control_id)
618 from amw_control_associations aca, amw_risk_associations ara
619 where ara.pk1 = ap.process_id
620 and ara.object_type = 'PROCESS'
621 and aca.pk1 = ara.risk_id
622 and aca.object_type = 'RISK');
623
624
625 open c_populate_amw_counts;
626 loop
627 fetch c_populate_amw_counts into v_rec_amw_counts;
628 exit when c_populate_amw_counts%notfound;
629 v_amw_counts(v_rec_amw_counts.process_id) := v_rec_amw_counts;
630 end loop;
631 close c_populate_amw_counts;
635 while v_index <= v_amw_counts.last
632
633
634 v_index := v_amw_counts.first;
636 loop
637
638 select nvl(sum(risk_count), 0), nvl(sum(control_count), 0)
639 into l_risk_count, l_control_count
640 from amw_process amwp, Amw_Proc_Hierarchy_Denorm apdenorm
641 where apdenorm.process_id = v_index
642 and apdenorm.up_down_ind = 'D'
643 and amwp.process_id = apdenorm.parent_child_id;
644
645 v_amw_counts(v_index).risk_count := l_risk_count + v_amw_counts(v_index).no_assoc_risks;
646 v_amw_counts(v_index).control_count := l_control_count + v_amw_counts(v_index).no_assoc_controls;
647
648 v_index := v_amw_counts.next(v_index);
649 end loop;
650
651
652 v_index := v_amw_counts.first;
653 while v_index <= v_amw_counts.last
654 loop
655 update amw_process
656 set risk_count = v_amw_counts(v_index).risk_count,
657 control_count = v_amw_counts(v_index).control_count
658 where process_id = v_index;
659
660 v_index := v_amw_counts.next(v_index);
661 end loop;
662
663 exception
664 when deadlock_detected then
665 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_DEADLOCK_DETECTED');
666 raise amw_deadlock_detected;
667
668 end reset_process_risk_ctrl_count;
669
670
671 ------------------------------------------------------------------------------------------------------------
672 ------------------------------------------------------------------------------------------------------------
673
674
675 procedure reset_proc_org_risk_ctrl_count( p_org_id IN NUMBER) is
676
677 type rec_amw_porg_counts IS record
678 (process_id amw_process_organization.process_id%type,
679 no_assoc_risks amw_process_organization.risk_count%type,
680 no_assoc_controls amw_process_organization.control_count%type,
681 risk_count amw_process_organization.risk_count%type,
682 control_count amw_process_organization.control_count%type);
683
684
685 v_rec_amw_porg_counts rec_amw_porg_counts;
686 type t_amw_porg_counts IS table of rec_amw_porg_counts index by binary_integer;
687 v_amw_porg_counts t_amw_porg_counts;
688
689 cursor c_populate_amw_org_counts(l_org_id number) is
690 select process_id, risk_count, control_count, 0, 0
691 from amw_process_organization
692 where organization_id = l_org_id
693 and end_date is null;
694
695 cursor c_org(p_org_id number) is
696 select distinct organization_id from amw_process_organization where organization_id = nvl(p_org_id, organization_id) and end_date is null;
697
698 l_org number;
699 v_index NUMBER;
700 l_risk_count number;
701 l_control_count number;
702 insert_row_cnt number;
703
704 begin
705 update amw_process_organization apo
706 set risk_count =
707 (select count(ara.risk_id)
708 from amw_risk_associations ara
709 where pk1 = apo.process_organization_id
710 and object_type = 'PROCESS_ORG'),
711 control_count =
712 (select count(distinct aca.control_id)
713 from amw_control_associations aca, amw_risk_associations ara
714 where ara.pk1 = apo.process_organization_id
715 and ara.object_type = 'PROCESS_ORG'
716 and aca.pk1 = ara.risk_association_id
717 and aca.object_type = 'RISK_ORG')
718 where organization_id = nvl(p_org_id, organization_id)
719 and end_date is null;
720
721 open c_org(p_org_id);
722 loop
723 fetch c_org into l_org;
724 exit when c_org%notfound;
725 --dbms_output.put_line('new org: '||l_org);
726 open c_populate_amw_org_counts(l_org);
727 loop
728 fetch c_populate_amw_org_counts into v_rec_amw_porg_counts;
729 exit when c_populate_amw_org_counts%notfound;
730 v_amw_porg_counts(v_rec_amw_porg_counts.process_id) := v_rec_amw_porg_counts;
731 end loop;
732 close c_populate_amw_org_counts;
733
734 v_index := v_amw_porg_counts.first;
735 while v_index <= v_amw_porg_counts.last
736 loop
737
738 select nvl(sum(risk_count), 0), nvl(sum(control_count), 0)
739 into l_risk_count, l_control_count
740 from amw_process_organization amwp, Amw_Org_Hierarchy_Denorm aodenorm
741 where aodenorm.organization_id = l_org
742 and aodenorm.process_id = v_index
743 and aodenorm.up_down_ind = 'D'
744 and amwp.process_id = aodenorm.parent_child_id
745 and amwp.organization_id = l_org
746 and amwp.end_date is null;
747
748 v_amw_porg_counts(v_index).risk_count := l_risk_count + v_amw_porg_counts(v_index).no_assoc_risks;
749 v_amw_porg_counts(v_index).control_count := l_control_count + v_amw_porg_counts(v_index).no_assoc_controls;
750
751 v_index := v_amw_porg_counts.next(v_index);
752 end loop;
753
754
755 v_index := v_amw_porg_counts.first;
756 while v_index <= v_amw_porg_counts.last
757 loop
758
759 update amw_process_organization
760 set risk_count = v_amw_porg_counts(v_index).risk_count,
761 control_count = v_amw_porg_counts(v_index).control_count
762 where process_id = v_index
763 and organization_id = l_org
764 and end_date is null;
765
766 v_index := v_amw_porg_counts.next(v_index);
770
767 end loop;
768
769 v_amw_porg_counts.delete;
771 end loop;
772 close c_org;
773
774 exception
775 when deadlock_detected then
776 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_DEADLOCK_DETECTED');
777 raise amw_deadlock_detected;
778
779 end reset_proc_org_risk_ctrl_count;
780
781
782 ------------------------------------------------------------------------------------------------------------
783 ------------------------------------------------------------------------------------------------------------
784
785 procedure reset_proc_org_risk_ctrl_count is
786 begin
787 reset_proc_org_risk_ctrl_count(p_org_id => null);
788 end reset_proc_org_risk_ctrl_count;
789
790 ------------------------------------------------------------------------------------------------------------
791 ------------------------------------------------------------------------------------------------------------
792
793
794
795 procedure populate_flatlist(p_org_id in number) is
796
797 cursor c11(l_org_id number) is
798 select process_id from amw_process_organization where organization_id = l_org_id and end_date is null;
799
800 type t_apo_id is table of amw_process_organization.process_id%type;
801 v_apo_id t_apo_id;
802
803 v_index number;
804 v_indexDown number;
805 j number := 0;
806 jDown number := 0;
807 oldParentCount number := 0;
808 oldChildCount number := 0;
809 l_dummy number;
810
811
812 begin
813 delete from Amw_Org_Hierarchy_Denorm where organization_id = p_org_id;
814 open c11(p_org_id);
815 loop
816 fetch c11 bulk collect into v_apo_id;
817 exit when c11%notfound;
818 end loop;
819 close c11;
820
821 -- if the tables have been deleted ...
822 oldParentCount := v_org_parent_id.count;
823 if (oldParentCount = 0) then
824 oldCount := 0;
825 end if;
826 oldChildCount := v_org_child_id.count;
827 if (oldChildCount = 0) then
828 oldCountDown := 0;
829 end if;
830
831
832 v_index := v_apo_id.first;
833 while v_index <= v_apo_id.last
834 loop
835
836 -- insert upward hierarchy
837
838 find_org_hierarchy_parent(p_org_id, v_apo_id(v_index));
839 j := oldCount + 1;
840 -- opportunity exists for performance improvement here
841 FOR i IN j..v_org_parent_id.count LOOP
842
843 begin
844 select 1 into l_dummy
845 from Amw_Org_Hierarchy_Denorm
846 where Organization_Id = p_org_id
847 and Process_Id = v_apo_id(v_index)
848 and Parent_Child_Id = v_org_parent_id(i)
849 and Up_Down_Ind = 'U';
850
851 exception
852 when no_data_found then
853 insert into Amw_Org_Hierarchy_Denorm
854 (Organization_Id,
855 Process_Id,
856 Parent_Child_Id,
857 Up_Down_Ind,
858 Last_Update_Date,
859 Last_Updated_By,
860 Creation_Date,
861 Created_By,
862 Last_Update_Login,
863 OBJECT_VERSION_NUMBER
864 )
865 values
866 (p_org_id,
867 v_apo_id(v_index),
868 v_org_parent_id(i),
869 'U',
870 sysdate,
871 G_USER_ID,
872 sysdate,
873 G_USER_ID,
874 G_LOGIN_ID,
875 1);
876 end;
877 END LOOP;
878 oldCount := v_org_parent_id.count;
879
880
881 -- insert downward hierarchy
882
883 find_org_hierarchy_children(p_org_id, v_apo_id(v_index));
884 jDown := oldCountDown + 1;
885 -- opportunity exists for performance improvement here
886 FOR i IN jDown..v_org_child_id.count LOOP
887
888 begin
889 select 1 into l_dummy
890 from Amw_Org_Hierarchy_Denorm
891 where Organization_Id = p_org_id
892 and Process_Id = v_apo_id(v_index)
893 and Parent_Child_Id = v_org_child_id(i)
894 and Up_Down_Ind = 'D';
895
896 exception
897 when no_data_found then
898 insert into Amw_Org_Hierarchy_Denorm
899 (Organization_Id,
900 Process_Id,
901 Parent_Child_Id,
902 Up_Down_Ind,
903 Last_Update_Date,
904 Last_Updated_By,
905 Creation_Date,
906 Created_By,
907 Last_Update_Login,
908 OBJECT_VERSION_NUMBER
909 )
910 values
914 'D',
911 (p_org_id,
912 v_apo_id(v_index),
913 v_org_child_id(i),
915 sysdate,
916 G_USER_ID,
917 sysdate,
918 G_USER_ID,
919 G_LOGIN_ID,
920 1);
921 end;
922 END LOOP;
923 oldCountDown := v_org_child_id.count;
924
925 v_index := v_apo_id.next(v_index);
926 end loop;
927 -- if i delete this table, it cribs when I try to execute this procedure multiple
928 -- times in the same session.
929 -- v_org_parent_id.delete;
930 exception
931 when deadlock_detected then
932 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_DEADLOCK_DETECTED');
933 raise amw_deadlock_detected;
934 end populate_flatlist;
935
936
937 ------------------------------------------------------------------------------------------------------------
938 ------------------------------------------------------------------------------------------------------------
939
940
941 -- get the process hierarchy IN AMW (i.e. the process should exist in amw_process) and
942 -- populate Amw_Process_Org_Relations
943 procedure create_org_relations( p_process_name in varchar2,
944 p_org_id in number,
945 x_return_status out nocopy varchar2,
946 x_msg_count out nocopy number,
947 x_msg_data out nocopy varchar2)
948 is
949 cursor c1 (l_name varchar2) is
950 select child_nondisp_name
951 from amw_process_hierarchy_v
952 where parent_nondisp_name=l_name;
953
954 c1_rec c1%rowtype;
955 parent_id number;
956 child_id number;
957 instance_id number;
958 l_dummy number;
959
960 begin
961 -- much scope for performance improvement lies here. Too many sql's being fired.
962 for c1_rec in c1(p_process_name) loop
963 exit when c1%notfound;
964
965 select process_id into parent_id from amw_process where name = p_process_name;
966 select process_id into child_id from amw_process where name = c1_rec.child_nondisp_name;
967
968 select wpa.instance_id
969 into instance_id
970 from wf_process_activities wpa,
971 wf_activities wa
972 where wpa.process_item_type = 'AUDITMGR'
973 and wpa.process_name = p_process_name
974 and wpa.process_name = wa.name
975 and wa.end_date is null
976 and wa.item_type = 'AUDITMGR'
977 and wpa.process_version = wa.version
978 and wpa.activity_name = c1_rec.child_nondisp_name;
979
980 assoc_process_org_hier(child_id, p_org_id, parent_id, x_return_status, x_msg_count, x_msg_data);
981
982 begin
983
984 select 1
985 into l_dummy
986 from Amw_Process_Org_Relations
987 where ORGANIZATION_ID = p_org_id
988 and PARENT_PROCESS_ID = parent_id
989 and CHILD_PROCESS_ID = child_id;
990
991 exception
992 when no_data_found then
993
994 insert into Amw_Process_Org_Relations
995 (ORGANIZATION_ID,
996 PARENT_PROCESS_ID,
997 CHILD_PROCESS_ID,
998 INSTANCE_ID,
999 EXCEPTION_PRESENT_FLAG,
1000 LAST_UPDATE_DATE,
1001 LAST_UPDATED_BY,
1002 CREATION_DATE,
1003 CREATED_BY,
1004 LAST_UPDATE_LOGIN,
1005 OBJECT_VERSION_NUMBER)
1006 values
1007 (p_org_id,
1008 parent_id,
1009 child_id,
1010 instance_id,
1011 'N',
1012 sysdate,
1013 G_USER_ID,
1014 sysdate,
1015 G_USER_ID,
1016 G_LOGIN_ID,
1017 1);
1018 end;
1019
1020 create_org_relations(c1_rec.child_nondisp_name, p_org_id, x_return_status, x_msg_count, x_msg_data);
1021 end loop;
1022 end create_org_relations;
1023
1024
1025 ------------------------------------------------------------------------------------------------------------
1026 ------------------------------------------------------------------------------------------------------------
1027
1028
1029 procedure assoc_process_org_hier(
1030 p_process_id in Number,
1031 p_org_id in Number,
1032 p_parent_process_id in Number,
1033 x_return_status out nocopy varchar2,
1034 x_msg_count out nocopy number,
1035 x_msg_data out nocopy varchar2)
1036 is
1037 l_apo_type AMW_PROC_ORG_HIERARCHY_PVT.apo_type;
1038
1039 begin
1040 l_apo_type.process_id := p_process_id;
1041 l_apo_type.organization_id := p_org_id;
1042
1043 AMW_PROC_ORG_HIERARCHY_PVT.associate_process_org(
1044 p_apo_type => l_apo_type,
1045 p_process_id => p_process_id,
1046 p_top_process_id => null,
1047 p_organization_id => p_org_id,
1048 p_parent_process_id => p_parent_process_id,
1049 p_mode => 'ASSOCIATE',
1050 x_return_status => x_return_status,
1051 x_msg_count => x_msg_count,
1052 x_msg_data => x_msg_data );
1053
1054 end assoc_process_org_hier;
1055
1056
1057 ------------------------------------------------------------------------------------------------------------
1058 ------------------------------------------------------------------------------------------------------------
1059
1060
1061 procedure assoc_process_rcm_org_hier(
1065 p_batch_id in number := null,
1062 p_process_id in Number,
1063 p_org_id in Number,
1064 p_rcm_assoc in varchar2 := 'N',
1066 p_rcm_org_intf_id in number := null,
1067 p_risk_id in number := null,
1068 p_control_id in number := null,
1069 p_parent_process_id in Number,
1070 x_return_status out nocopy varchar2,
1071 x_msg_count out nocopy number,
1072 x_msg_data out nocopy varchar2)
1073 is
1074 l_apo_type AMW_PROC_ORG_HIERARCHY_PVT.apo_type;
1075
1076 begin
1077 l_apo_type.process_id := p_process_id;
1078 l_apo_type.organization_id := p_org_id;
1079
1080 fnd_file.put_line(fnd_file.LOG, 'INSIDE ASSOC_PROCESS_RCM_ORG_HIER');
1081 fnd_file.put_line(fnd_file.LOG, 'l_apo_type.process_id: '||l_apo_type.process_id);
1082 fnd_file.put_line(fnd_file.LOG, 'l_apo_type.organization_id: '||l_apo_type.organization_id);
1083 fnd_file.put_line(fnd_file.LOG, 'p_process_id: '||p_process_id);
1084 fnd_file.put_line(fnd_file.LOG, 'p_organization_id: '||p_org_id);
1085 fnd_file.put_line(fnd_file.LOG, 'p_parent_process_id: '||p_parent_process_id);
1086 fnd_file.put_line(fnd_file.LOG, '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
1087
1088 AMW_PROC_ORG_HIERARCHY_PVT.associate_process_org(
1089 p_apo_type => l_apo_type,
1090 p_process_id => p_process_id,
1091 p_top_process_id => null,
1092 p_organization_id => p_org_id,
1093 p_parent_process_id => p_parent_process_id,
1094 p_rcm_assoc => p_rcm_assoc,
1095 p_batch_id => p_batch_id,
1096 p_rcm_org_intf_id => p_rcm_org_intf_id,
1097 p_risk_id => p_risk_id,
1098 p_control_id => p_control_id,
1099 p_mode => 'ASSOCIATE',
1100 x_return_status => x_return_status,
1101 x_msg_count => x_msg_count,
1102 x_msg_data => x_msg_data );
1103
1104 ---npanandi added 10/18/2004:
1105 ---bugfix for bug 3841334
1106 ---added below 2 lines to sync up code between main and branch lines
1107 reset_proc_org_risk_ctrl_count(p_org_id);
1108 reset_org_count;
1109 ---npanandi ended fix: 10/18/2004 for bug 3841334
1110 end assoc_process_rcm_org_hier;
1111
1112
1113 ------------------------------------------------------------------------------------------------------------
1114 ------------------------------------------------------------------------------------------------------------
1115
1116 -- note that "associate" function (as opposed to add function) will always add the process
1117 -- directly under the root node.
1118 procedure associate_org_process(
1119 p_process_id in number,
1120 p_org_id in number,
1121 p_commit in varchar2 := FND_API.G_FALSE,
1122 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1123 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1124 x_return_status out nocopy varchar2,
1125 x_msg_count out nocopy number,
1126 x_msg_data out nocopy varchar2) is
1127
1128 L_API_NAME CONSTANT VARCHAR2(30) := 'associate_org_process';
1129
1130 p_name varchar2(100);
1131 l_dummy number;
1132 l_return_status varchar2(100);
1133 l_msg_count number;
1134 l_msg_data varchar2(4000);
1135
1136 begin
1137 x_return_status := FND_API.G_RET_STS_SUCCESS;
1138 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1139 FND_MSG_PUB.initialize;
1140 END IF;
1141 IF FND_GLOBAL.User_Id IS NULL THEN
1142 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1143 RAISE FND_API.G_EXC_ERROR;
1144 END IF;
1145
1146 begin
1147
1148 assoc_process_org_hier(p_process_id, p_org_id, -1, l_return_status, l_msg_count, l_msg_data);
1149
1150 select 1
1151 into l_dummy
1152 from Amw_Process_Org_Relations
1153 where ORGANIZATION_ID = p_org_id
1154 and PARENT_PROCESS_ID = -1
1155 and CHILD_PROCESS_ID = p_process_id;
1156
1157 exception
1158 when no_data_found then
1159
1160
1161 insert into Amw_Process_Org_Relations
1162 (ORGANIZATION_ID,
1163 PARENT_PROCESS_ID,
1164 CHILD_PROCESS_ID,
1165 INSTANCE_ID,
1166 EXCEPTION_PRESENT_FLAG,
1167 LAST_UPDATE_DATE,
1168 LAST_UPDATED_BY,
1169 CREATION_DATE,
1170 CREATED_BY,
1171 LAST_UPDATE_LOGIN,
1172 OBJECT_VERSION_NUMBER)
1173 values
1174 (p_org_id,
1175 -1,
1176 p_process_id,
1177 0,
1178 'N',
1179 sysdate,
1180 G_USER_ID,
1181 sysdate,
1182 G_USER_ID,
1183 G_LOGIN_ID,
1184 1);
1185 end;
1186
1187 select name
1188 into p_name
1189 from amw_process
1190 where process_id = p_process_id;
1191
1192 create_org_relations(p_name, p_org_id, l_return_status, l_msg_count, l_msg_data);
1193
1194 populate_flatlist(p_org_id);
1195 reset_proc_org_risk_ctrl_count(p_org_id);
1196 reset_org_count;
1197
1198 exception
1199 WHEN FND_API.G_EXC_ERROR THEN
1200 ROLLBACK;
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1203
1204 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1205 ROLLBACK;
1206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1207 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1208
1209 WHEN amw_deadlock_detected THEN
1210 ROLLBACK;
1211 x_return_status := FND_API.G_RET_STS_ERROR;
1212 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1213
1214 WHEN OTHERS THEN
1215 ROLLBACK;
1216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1217 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1218 THEN
1219 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1220 END IF;
1221 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1222
1223 end associate_org_process;
1224
1225 --npanandi commented this on 10/18/2004, for syncing between the version
1226 --of this API in branch and main
1227 --changed signature of associate_org_process as given below screws up the association
1228 --hence, commented below, and resorted to earlier signature as above
1229 /**
1230 procedure associate_org_process(
1231 p_process_id in number,
1232 p_org_id in number,
1233 p_rcm_assoc in varchar2 := 'N',
1234 p_batch_id in number := null,
1235 p_rcm_org_intf_id in number := null,
1236 p_risk_id in number := null,
1237 p_control_id in number := null,
1238 p_commit in varchar2 := FND_API.G_FALSE,
1239 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1240 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1241 x_return_status out nocopy varchar2,
1242 x_msg_count out nocopy number,
1243 x_msg_data out nocopy varchar2) is
1244
1245 L_API_NAME CONSTANT VARCHAR2(30) := 'associate_org_process';
1246
1247 p_name varchar2(100);
1248 l_dummy number;
1249 l_return_status varchar2(100);
1250 l_msg_count number;
1251 l_msg_data varchar2(4000);
1252
1253 begin
1254 x_return_status := FND_API.G_RET_STS_SUCCESS;
1255 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1256 FND_MSG_PUB.initialize;
1257 END IF;
1258 IF FND_GLOBAL.User_Id IS NULL THEN
1259 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1260 RAISE FND_API.G_EXC_ERROR;
1261 END IF;
1262
1263 fnd_file.put_line(fnd_file.LOG, 'INSIDE ASSOCIATE_ORG_PROCESS');
1264 fnd_file.put_line(fnd_file.LOG, 'p_process_id: '||p_process_id);
1265 fnd_file.put_line(fnd_file.LOG, 'p_org_id: '||p_org_id);
1266 fnd_file.put_line(fnd_file.LOG, 'p_rcm_assoc: '||p_rcm_assoc);
1267 fnd_file.put_line(fnd_file.LOG, 'p_batch_id: '||p_batch_id);
1268 fnd_file.put_line(fnd_file.LOG, 'p_risk_id: '||p_risk_id);
1269 fnd_file.put_line(fnd_file.LOG, 'p_control_id: '||p_control_id);
1270 fnd_file.put_line(fnd_file.LOG, '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
1271 assoc_process_rcm_org_hier(
1275 p_batch_id => p_batch_id,
1272 p_process_id => p_process_id,
1273 p_org_id => p_org_id,
1274 p_rcm_assoc => p_rcm_assoc,
1276 p_rcm_org_intf_id => p_rcm_org_intf_id,
1277 p_risk_id => p_risk_id,
1278 p_control_id => p_control_id,
1279 p_parent_process_id => -1,
1280 x_return_status => l_return_status,
1281 x_msg_count => l_msg_count,
1282 x_msg_data => l_msg_data);
1283
1284 begin
1285
1286 select 1
1287 into l_dummy
1288 from Amw_Process_Org_Relations
1289 where ORGANIZATION_ID = p_org_id
1290 and PARENT_PROCESS_ID = -1
1291 and CHILD_PROCESS_ID = p_process_id;
1292
1293 exception
1294 when no_data_found then
1295
1296
1297 insert into Amw_Process_Org_Relations
1298 (ORGANIZATION_ID,
1299 PARENT_PROCESS_ID,
1300 CHILD_PROCESS_ID,
1301 INSTANCE_ID,
1302 EXCEPTION_PRESENT_FLAG,
1303 LAST_UPDATE_DATE,
1304 LAST_UPDATED_BY,
1305 CREATION_DATE,
1306 CREATED_BY,
1307 LAST_UPDATE_LOGIN,
1308 OBJECT_VERSION_NUMBER)
1309 values
1310 (p_org_id,
1311 -1,
1312 p_process_id,
1313 0,
1314 'N',
1315 sysdate,
1316 G_USER_ID,
1317 sysdate,
1318 G_USER_ID,
1319 G_LOGIN_ID,
1320 1);
1321 end;
1322
1323 select name
1324 into p_name
1325 from amw_process
1326 where process_id = p_process_id;
1327
1328 create_org_relations(p_name, p_org_id, l_return_status, l_msg_count, l_msg_data);
1329
1330 populate_flatlist(p_org_id);
1331 reset_proc_org_risk_ctrl_count(p_org_id);
1332 reset_org_count;
1333
1334 exception
1335 WHEN FND_API.G_EXC_ERROR THEN
1336 ROLLBACK;
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1339
1340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1341 ROLLBACK;
1342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1343 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1344
1345 WHEN amw_deadlock_detected THEN
1346 ROLLBACK;
1347 x_return_status := FND_API.G_RET_STS_ERROR;
1348 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1349
1350 WHEN OTHERS THEN
1351 ROLLBACK;
1352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1353 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1354 THEN
1355 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1356 END IF;
1357 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1358
1359 end associate_org_process;
1360 **/
1361 ------------------------------------------------------------------------------------------------------------
1362 ------------------------------------------------------------------------------------------------------------
1363
1364
1365 -- p_child_process_id is the process being deleted.
1366 procedure delete_org_relation(
1367 p_parent_process_id in number,
1368 p_child_process_id in number,
1369 p_org_id in number) is
1370
1371 l_exist number := 0;
1372 l_next_level number;
1373 x_return_status varchar2(10);
1374 x_msg_count number;
1375 x_msg_data varchar2(2000);
1376 l_apo_type AMW_PROC_ORG_HIERARCHY_PVT.apo_type;
1377
1378 cursor c1 (l_pid number, p_oid number) is
1379 select CHILD_PROCESS_ID
1380 from Amw_Process_Org_Relations
1381 where PARENT_PROCESS_ID = l_pid
1382 and organization_id = p_oid;
1383
1384
1385 begin
1386 delete from Amw_Process_Org_Relations
1387 where ORGANIZATION_ID = p_org_id
1388 and PARENT_PROCESS_ID = p_parent_process_id
1389 and CHILD_PROCESS_ID = p_child_process_id;
1390
1391 select count(ORGANIZATION_ID)
1392 into l_exist
1393 from Amw_Process_Org_Relations
1394 where ORGANIZATION_ID = p_org_id
1395 and CHILD_PROCESS_ID = p_child_process_id;
1396
1397 IF l_exist = 0 THEN
1398 /* disassociate_process_org(p_process_id => p_child_process_id,
1399 p_org_id => p_org_id,
1400 x_return_status => x_return_status,
1401 x_msg_count => x_msg_count,
1402 x_msg_data => x_msg_data); */
1403
1404
1405 l_apo_type.process_id := p_child_process_id;
1406 l_apo_type.organization_id := p_org_id;
1407
1408
1409 AMW_PROC_ORG_HIERARCHY_PVT.associate_process_org(
1410 p_apo_type => l_apo_type,
1411 p_process_id => p_child_process_id,
1412 p_top_process_id => null,
1413 p_organization_id => p_org_id,
1414 p_parent_process_id => null,
1415 p_mode => 'DISASSOCIATE',
1416 x_return_status => x_return_status,
1417 x_msg_count => x_msg_count,
1418 x_msg_data => x_msg_data );
1419
1420
1421 for c1_rec in c1(p_child_process_id, p_org_id) loop
1422 exit when c1%notfound;
1423 delete_org_relation(
1424 p_parent_process_id => p_child_process_id,
1425 p_child_process_id => c1_rec.CHILD_PROCESS_ID,
1426 p_org_id => p_org_id);
1427 end loop;
1428 END IF;
1429
1430 populate_flatlist(p_org_id);
1434
1431 reset_proc_org_risk_ctrl_count(p_org_id);
1432 reset_org_count;
1433
1435 end delete_org_relation;
1436
1437 ------------------------------------------------------------------------------------------------------------
1438 ------------------------------------------------------------------------------------------------------------
1439
1440
1441 -- get the process hierarchy IN Amw_Process_Org_Relations and fire atomic
1442 -- disassociate api for each.
1443 procedure disassociate_process_org(
1444 p_process_id in number,
1445 p_org_id in number,
1446 p_commit in varchar2 := FND_API.G_FALSE,
1447 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1448 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1449 x_return_status out nocopy varchar2,
1450 x_msg_count out nocopy number,
1451 x_msg_data out nocopy varchar2) is
1452
1453 L_API_NAME CONSTANT VARCHAR2(30) := 'disassociate_process_org';
1454
1455 l_apo_type AMW_PROC_ORG_HIERARCHY_PVT.apo_type;
1456
1457 begin
1458
1459 x_return_status := FND_API.G_RET_STS_SUCCESS;
1460 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1461 FND_MSG_PUB.initialize;
1462 END IF;
1463 IF FND_GLOBAL.User_Id IS NULL THEN
1464 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1465 RAISE FND_API.G_EXC_ERROR;
1466 END IF;
1467
1468 -- first disassociate the process itself, then loop through the org hierarchy
1469 l_apo_type.process_id := p_process_id;
1470 l_apo_type.organization_id := p_org_id;
1471
1472 delete from Amw_Process_Org_Relations
1473 where ORGANIZATION_ID = p_org_id
1474 and CHILD_PROCESS_ID = p_process_id;
1475
1476
1477 AMW_PROC_ORG_HIERARCHY_PVT.associate_process_org(
1478 p_apo_type => l_apo_type,
1479 p_process_id => p_process_id,
1480 p_top_process_id => null,
1481 p_organization_id => p_org_id,
1482 p_parent_process_id => null,
1483 p_mode => 'DISASSOCIATE',
1484 x_return_status => x_return_status,
1485 x_msg_count => x_msg_count,
1486 x_msg_data => x_msg_data );
1487
1488 disassoc_proc_org_hier(p_process_id =>p_process_id, p_org_id => p_org_id);
1489
1490 delete from amw_process_organization
1491 where organization_id = p_org_id
1492 and end_date is not null;
1493
1494 populate_flatlist(p_org_id);
1495 reset_proc_org_risk_ctrl_count(p_org_id);
1496 reset_org_count;
1497
1498 exception
1499 WHEN FND_API.G_EXC_ERROR THEN
1500 ROLLBACK;
1501 x_return_status := FND_API.G_RET_STS_ERROR;
1502 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1503
1504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1505 ROLLBACK;
1506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1507 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1508
1509 WHEN amw_deadlock_detected THEN
1510 ROLLBACK;
1511 x_return_status := FND_API.G_RET_STS_ERROR;
1512 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1513
1514
1515 WHEN OTHERS THEN
1516 ROLLBACK;
1517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1518 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1519 THEN
1520 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1521 END IF;
1522 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1523
1524
1525 end disassociate_process_org;
1526
1527
1528 ------------------------------------------------------------------------------------------------------------
1529 ------------------------------------------------------------------------------------------------------------
1530
1531
1532 procedure disassoc_proc_org_hier(p_process_id in number, p_org_id in number) is
1533
1534 cursor c1 (l_id number, l_oid number) is
1535 select CHILD_PROCESS_ID
1536 from Amw_Process_Org_Relations
1537 where PARENT_PROCESS_ID=l_id
1538 and organization_id = l_oid;
1539
1540 c1_rec c1%rowtype;
1541
1542 l_return_status varchar2(30);
1543 l_msg_count number;
1544 l_msg_data varchar2(4000);
1545
1546 l_apo_type AMW_PROC_ORG_HIERARCHY_PVT.apo_type;
1547
1548 begin
1549 -- loop through the org hierarchy and disassociate the children
1550 for c1_rec in c1(p_process_id, p_org_id) loop
1551 exit when c1%notfound;
1552
1553 delete from Amw_Process_Org_Relations
1554 where ORGANIZATION_ID = p_org_id
1555 and PARENT_PROCESS_ID = p_process_id
1556 and CHILD_PROCESS_ID = c1_rec.CHILD_PROCESS_ID;
1557
1558 l_apo_type.process_id := c1_rec.CHILD_PROCESS_ID;
1559 l_apo_type.organization_id := p_org_id;
1560
1561 AMW_PROC_ORG_HIERARCHY_PVT.associate_process_org(
1562 p_apo_type => l_apo_type,
1563 p_process_id => c1_rec.CHILD_PROCESS_ID,
1564 p_top_process_id => null,
1565 p_organization_id => p_org_id,
1566 p_parent_process_id => null,
1567 p_mode => 'DISASSOCIATE',
1568 x_return_status => l_return_status,
1569 x_msg_count => l_msg_count,
1570 x_msg_data => l_msg_data );
1571
1572 disassoc_proc_org_hier(p_process_id =>c1_rec.CHILD_PROCESS_ID, p_org_id => p_org_id);
1573 end loop;
1574 exception
1575 when deadlock_detected then
1576 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_DEADLOCK_DETECTED');
1577 raise amw_deadlock_detected;
1578
1579 end disassoc_proc_org_hier;
1580
1581
1582 ------------------------------------------------------------------------------------------------------------
1583 ------------------------------------------------------------------------------------------------------------
1584
1585
1586 -- associate: everytime a process is associated (or added), the risks/ctrl/ap/acct associated with that
1587 -- process in the library get associated too. Say a process P is associatd to org O. If I
1588 -- associate the same process P again to org O, the existing association records will be
1589 -- overwritten with the most current information.
1590
1591 -- disassociate: If I "delete" a process, it is not necessarily disassociated.
1592 -- A process will be "disasociated" from an org ONLY if there's no more existance of that
1593 -- process in the hierarchy for that particular org. (Note that a process can exist at
1594 -- multiple leaves).
1595
1596 procedure modify_org_relation (
1597 p_mode in varchar2,
1598 p_parent_process_id in number,
1599 p_child_process_id in number,
1600 p_org_id in number,
1601 p_exception_yes in varchar2,
1602 p_process_owner_party_id in number,
1603 p_commit in varchar2 := FND_API.G_FALSE,
1604 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1605 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1606 x_return_status out nocopy varchar2,
1607 x_msg_count out nocopy number,
1608 x_msg_data out nocopy varchar2
1609 ) is
1610
1611 L_API_NAME CONSTANT VARCHAR2(30) := 'modify_org_relation';
1612 p_name varchar2(100);
1613 l_dummy number;
1614 l_return_status varchar2(100);
1615 l_msg_count number;
1616 l_msg_data varchar2(4000);
1617 l_person_id number;
1618 l_header varchar2(4000);
1619 l_body varchar2(4000);
1620 l_notif_id number;
1621 l_ret_status varchar2(30);
1622 l_parent_disp_name varchar2(100);
1623 l_child_disp_name varchar2(100);
1627 x_return_status := FND_API.G_RET_STS_SUCCESS;
1624 l_org_name varchar2(100);
1625
1626 begin
1628 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1629 FND_MSG_PUB.initialize;
1630 END IF;
1631 IF FND_GLOBAL.User_Id IS NULL THEN
1632 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1633 RAISE FND_API.G_EXC_ERROR;
1634 END IF;
1635
1636 if p_process_owner_party_id <> 0 then
1637
1638 select employee_id
1639 into l_person_id
1640 from AMW_EMPLOYEES_CURRENT_V
1641 where party_id = p_process_owner_party_id;
1642
1643 select watl.display_name
1644 into l_parent_disp_name
1645 from wf_activities_tl watl, wf_activities wa, amw_process ap
1646 where ap.process_id = p_parent_process_id
1647 and ap.name = wa.name
1648 and wa.item_type = 'AUDITMGR'
1649 and wa.end_date is null
1650 and watl.item_type = 'AUDITMGR'
1651 and watl.name = wa.name
1652 and watl.version = wa.version
1653 and watl.language = userenv('LANG');
1654
1655 select watl.display_name
1656 into l_child_disp_name
1657 from wf_activities_tl watl, wf_activities wa, amw_process ap
1658 where ap.process_id = p_child_process_id
1659 and ap.name = wa.name
1660 and wa.item_type = 'AUDITMGR'
1661 and wa.end_date is null
1662 and watl.item_type = 'AUDITMGR'
1663 and watl.name = wa.name
1664 and watl.version = wa.version
1665 and watl.language = userenv('LANG');
1666
1667 select name
1668 into l_org_name
1669 from amw_audit_units_v
1670 where organization_id = p_org_id;
1671
1672 else
1673 l_person_id := 0;
1674 end if;
1675
1676
1677 if (p_mode = 'ADD') then
1678
1679 assoc_process_org_hier(p_child_process_id, p_org_id, p_parent_process_id, l_return_status, l_msg_count, l_msg_data);
1680
1681 begin
1682
1683 select 1
1684 into l_dummy
1685 from Amw_Process_Org_Relations
1686 where ORGANIZATION_ID = p_org_id
1687 and PARENT_PROCESS_ID = p_parent_process_id
1688 and CHILD_PROCESS_ID = p_child_process_id;
1689
1690 exception
1691 when no_data_found then
1692
1693 insert into Amw_Process_Org_Relations
1694 (ORGANIZATION_ID,
1695 PARENT_PROCESS_ID,
1696 CHILD_PROCESS_ID,
1697 INSTANCE_ID,
1698 EXCEPTION_PRESENT_FLAG,
1699 LAST_UPDATE_DATE,
1700 LAST_UPDATED_BY,
1701 CREATION_DATE,
1702 CREATED_BY,
1703 LAST_UPDATE_LOGIN,
1704 OBJECT_VERSION_NUMBER)
1705 values
1706 (p_org_id,
1707 p_parent_process_id,
1708 p_child_process_id,
1709 0,
1710 p_exception_yes,
1711 sysdate,
1712 G_USER_ID,
1713 sysdate,
1714 G_USER_ID,
1715 G_LOGIN_ID,
1716 1);
1717 end;
1718
1719 select name
1720 into p_name
1721 from amw_process
1722 where process_id = p_child_process_id;
1723
1724 create_org_relations(p_name, p_org_id, l_return_status, l_msg_count, l_msg_data);
1725
1726 if l_person_id <> 0 then
1727 fnd_message.set_name('AMW', 'AMW_PROC_ADD_HEAD');
1728 l_header := fnd_message.get;
1729
1730 fnd_message.set_name('AMW', 'AMW_PROC_ADD_BODY');
1731 fnd_message.set_token('CHILD', l_child_disp_name);
1732 fnd_message.set_token('PARENT', l_parent_disp_name);
1733 fnd_message.set_token('ORG', l_org_name);
1734 l_body := fnd_message.get;
1735
1736 AMW_Utility_PVT.send_wf_standalone_message( p_subject => l_header,
1737 p_body => l_body,
1738 p_send_to_person_id => l_person_id,
1739 x_notif_id => l_notif_id,
1740 x_return_status => l_ret_status);
1741 end if;
1742
1743 elsif (p_mode = 'DEL') then
1744 delete_org_relation(p_parent_process_id, p_child_process_id, p_org_id);
1745
1746 if l_person_id <> 0 then
1747 fnd_message.set_name('AMW', 'AMW_PROC_DEL_HEAD');
1748 l_header := fnd_message.get;
1749
1750 fnd_message.set_name('AMW', 'AMW_PROC_DEL_BODY');
1754 l_body := fnd_message.get;
1751 fnd_message.set_token('CHILD', l_child_disp_name);
1752 fnd_message.set_token('PARENT', l_parent_disp_name);
1753 fnd_message.set_token('ORG', l_org_name);
1755
1756 AMW_Utility_PVT.send_wf_standalone_message( p_subject => l_header,
1757 p_body => l_body,
1758 p_send_to_person_id => l_person_id,
1759 x_notif_id => l_notif_id,
1760 x_return_status => l_ret_status);
1761 end if;
1762
1763 end if;
1764
1765 populate_flatlist(p_org_id);
1766 reset_proc_org_risk_ctrl_count(p_org_id);
1767 reset_org_count;
1768
1769 exception
1770 WHEN FND_API.G_EXC_ERROR THEN
1771 ROLLBACK;
1772 x_return_status := FND_API.G_RET_STS_ERROR;
1773 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1774
1775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1776 ROLLBACK;
1777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1778 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1779
1780 WHEN amw_deadlock_detected THEN
1781 ROLLBACK;
1782 x_return_status := FND_API.G_RET_STS_ERROR;
1783 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1784
1785 WHEN OTHERS THEN
1786 ROLLBACK;
1787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1788 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1789 THEN
1790 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1791 END IF;
1792 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1793
1794 end modify_org_relation;
1795
1796
1797 ------------------------------------------------------------------------------------------------------------
1798 ------------------------------------------------------------------------------------------------------------
1799
1800
1801 procedure reset_org_count is
1802 begin
1803
1804 update amw_process amwp
1805 set amwp.org_count = (select count(process_organization_id)
1806 from amw_process_organization amwpo
1807 where amwpo.process_id = amwp.process_id
1808 and amwpo.end_date is null);
1809 exception
1810 when deadlock_detected then
1811 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_DEADLOCK_DETECTED');
1812 raise amw_deadlock_detected;
1813 end reset_org_count;
1814
1815 ------------------------------------------------------------------------------------------------------------
1816 ------------------------------------------------------------------------------------------------------------
1817
1818
1819
1820 /*==============================================================+
1821 | This procedure is a wrapper for synch_hierarchy_amw_process |
1822 | To use as a concurrent program. |
1823 +==============================================================*/
1824
1825
1826 PROCEDURE sync_hier_amw_process_wrap (
1827 errbuf out nocopy varchar2,
1828 retcode out nocopy varchar2 ) IS
1829
1830 x_return_status varchar2(10);
1831 x_msg_count number;
1832 x_msg_data varchar2(4000);
1833 conc_status boolean;
1834 l_msg_index_out number;
1835 BEGIN
1836
1837 retcode :=0;
1838 errbuf :='';
1839 synch_hierarchy_amw_process( x_return_status => x_return_status ,
1840 x_msg_count => x_msg_count ,
1844 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1841 x_msg_data => x_msg_data);
1842
1843 FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status :' || x_return_status || ':'||x_msg_count||':'||x_msg_data);
1845 ROLLBACK;
1846 retcode := 2;
1847 IF x_msg_count <> 1 THEN
1848 FND_MSG_PUB.GET(p_encoded => FND_API.G_FALSE, p_data => x_msg_data, p_msg_index_out => l_msg_index_out);
1849 END IF;
1850 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error: '|| x_msg_data);
1851 errbuf := SUBSTR(x_msg_data,1,1000);
1852 ELSE
1853 COMMIT;
1854 END IF;
1855
1856 EXCEPTION
1857 WHEN OTHERS THEN
1858 ROLLBACK;
1859 RETCODE :=2;
1860 errbuf := SUBSTR(SQLERRM,1,1000);
1861 conc_status :=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error: '|| SQLERRM);
1862 END sync_hier_amw_process_wrap;
1863
1864
1865
1866 /*==============================================================+
1867 | This procedure is a wrapper for reset_process_risk_ctrl_count |
1868 | To use as a concurrent program. |
1869 +==============================================================*/
1870
1871
1872
1873 PROCEDURE reset_process_risk_ctrl_wrap(
1874 errbuf out nocopy varchar2,
1875 retcode out nocopy varchar2
1876 ) IS
1877
1878 conc_status boolean;
1879
1880 BEGIN
1881 retcode :=0;
1882 errbuf :='';
1883 reset_process_risk_ctrl_count;
1884 commit;
1885
1886 EXCEPTION
1887
1888 WHEN OTHERS THEN
1889 rollback;
1890 retcode :=2;
1891 errbuf := SUBSTR(SQLERRM,1,1000);
1892 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error: '|| SQLERRM);
1893
1894 END reset_process_risk_ctrl_wrap;
1895
1896 /*==============================================================+
1897 | This procedure is a wrapper for reset_proc_org_risk_ctrl_count |
1898 | To use as a concurrent program. |
1899 +==============================================================*/
1900
1901
1902 procedure reset_proc_org_risk_ctrl_wrap(
1903 errbuf out nocopy varchar2,
1904 retcode out nocopy varchar2,
1905 p_org_id in number
1906 ) is
1907 conc_status boolean;
1908
1909 begin
1910
1911 retcode :=0;
1912 errbuf :='';
1913 if p_org_id is null then
1914 reset_proc_org_risk_ctrl_count(p_org_id => null);
1915 else
1916 reset_proc_org_risk_ctrl_count(p_org_id => p_org_id);
1917 end if;
1918 commit;
1919 exception
1920 when others then
1921 rollback;
1922 retcode :=2;
1923 errbuf :=SUBSTR(SQLERRM,1,1000);
1924 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error: '|| SQLERRM);
1925
1926 end reset_proc_org_risk_ctrl_wrap;
1927
1928
1929
1930 ------------------------------------------------------------------------------------------------------------
1931 ------------------------------------------------------------------------------------------------------------
1932
1933 /* Refresh Api:
1934 ** Refresh unfortunately is NOT the same as a combination of disassociate and associate.
1935 ** Library:
1936 ** All
1937 ** |---P1
1938 ** |---P2
1939 ** |---P3
1943 ** All
1940 ** |---P4
1941 **
1942 ** Org:
1944 ** |---OP1
1945 ** |---OP2
1946 ** |---OP3
1947 ** |---OP4
1948 **
1949 ** Now if I add P41 to P3 and disassociate and associate, this is how the hierarchy in org will look like:
1950 **
1951 ** Library:
1952 ** All
1953 ** |---P1
1954 ** |---P2
1955 ** |---P3
1956 ** |---P4
1957 ** |---P41
1958 **
1959 ** Org:
1960 ** All
1961 ** |---OP1
1962 ** |---OP2
1963 ** |---OP3
1964 ** |---OP4
1965 ** |---OP41
1966 **
1967 ** instead of
1968 **
1969 ** Org:
1970 ** All
1971 ** |---OP1
1972 ** |---OP2
1973 ** |---OP3
1974 ** |---OP4
1975 ** |---Op41
1976 **
1977 ** Thus I need to write a special refresh api.
1978 ** Note that if P1 is added to P in the library, P needs to be refreshed in any org for P1 to get reflected there.
1979 ** Refreshing P1 will not produce any result as P1 is not there. In short, synchronize the parent.
1980 ** Synchronizing is equivalent to disassociating + re-associating, albeit at the old parent node, rather than under the root.
1981 **
1982 **
1983 ** To refresh in all orgs, pass -1 for org_id
1984 ** To refresh in all orgs without exception, pass -2 for org_id
1985 ** To refresh in a particular org, pass org_id
1986 */
1987
1988 procedure refresh_process_org (
1989 p_process_id in number,
1990 p_org_id in number,
1991 p_commit in varchar2 := FND_API.G_FALSE,
1992 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1993 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1994 x_return_status out nocopy varchar2,
1995 x_msg_count out nocopy number,
1996 x_msg_data out nocopy varchar2
1997 ) is
1998
1999 L_API_NAME CONSTANT VARCHAR2(30) := 'refresh_process_org';
2000
2001 l_return_status varchar2(10);
2002 l_msg_count number;
2003 l_msg_data varchar2(4000);
2004 amw_exception exception;
2005 l_org_id number;
2006
2007 cursor c_all_orgs(l_pid number) is
2008 select distinct organization_id
2009 from amw_process_organization
2010 where process_id = l_pid
2011 and end_date is null;
2012
2013 cursor c_all_exorgs(l_pid number) is
2014 select distinct organization_id
2015 from amw_process_organization
2016 where process_id = l_pid
2017 and end_date is null
2018 and organization_id not in
2019 (select distinct old_pk1 from amw_exceptions_b where ((old_pk2 = l_pid and object_type in ('PROCESS','RISK','CONTROL'))or (object_type = 'PROCESS' and old_pk3 = l_pid)) and old_pk1 is not null)
2020 and organization_id not in
2021 (select distinct new_pk1 from amw_exceptions_b where ((new_pk2 = l_pid and object_type in ('PROCESS','RISK','CONTROL'))or (object_type = 'PROCESS' and new_pk3 = l_pid)) and new_pk1 is not null);
2022
2023
2024 begin
2025 x_return_status := FND_API.G_RET_STS_SUCCESS;
2026 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2027 FND_MSG_PUB.initialize;
2028 END IF;
2029 IF FND_GLOBAL.User_Id IS NULL THEN
2030 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
2031 RAISE FND_API.G_EXC_ERROR;
2032 END IF;
2033
2034 if p_org_id = -1 then
2035
2036 open c_all_orgs(p_process_id);
2037 loop
2038 fetch c_all_orgs into l_org_id;
2039 exit when c_all_orgs%notfound;
2040
2041 refresh_process_per_org (
2042 p_process_id => p_process_id,
2043 p_org_id => l_org_id,
2044 x_return_status => l_return_status,
2045 x_msg_count => l_msg_count,
2046 x_msg_data => l_msg_data );
2047
2048 if l_return_status <> 'S' then
2049 raise amw_exception;
2050 end if;
2051
2052 end loop;
2053 close c_all_orgs;
2054
2055 elsif p_org_id = -2 then
2056
2057 open c_all_exorgs(p_process_id);
2058 loop
2059 fetch c_all_exorgs into l_org_id;
2060 exit when c_all_exorgs%notfound;
2061
2062 refresh_process_per_org (
2063 p_process_id => p_process_id,
2064 p_org_id => l_org_id,
2065 x_return_status => l_return_status,
2066 x_msg_count => l_msg_count,
2067 x_msg_data => l_msg_data );
2068
2069 if l_return_status <> 'S' then
2070 raise amw_exception;
2071 end if;
2072
2073 end loop;
2074 close c_all_exorgs;
2075
2076 else -- p_org_id has an org value then
2077
2078 refresh_process_per_org (
2079 p_process_id => p_process_id,
2080 p_org_id => p_org_id,
2081 x_return_status => l_return_status,
2082 x_msg_count => l_msg_count,
2086 raise amw_exception;
2083 x_msg_data => l_msg_data );
2084
2085 if l_return_status <> 'S' then
2087 end if;
2088
2089 end if;
2090
2091 reset_org_count;
2092
2093
2094 exception
2095 WHEN amw_exception THEN
2096 ROLLBACK;
2097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2098 x_msg_count := l_msg_count;
2099 x_msg_data := l_msg_data;
2100
2101 WHEN FND_API.G_EXC_ERROR THEN
2102 ROLLBACK;
2103 x_return_status := FND_API.G_RET_STS_ERROR;
2104 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2105
2106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2107 ROLLBACK;
2108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2109 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2110
2111 WHEN amw_deadlock_detected THEN
2112 ROLLBACK;
2113 x_return_status := FND_API.G_RET_STS_ERROR;
2114 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2115
2116 WHEN OTHERS THEN
2117 ROLLBACK;
2118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2119 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2120 THEN
2121 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2122 END IF;
2123 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2124
2125 end refresh_process_org;
2126
2127
2128 ------------------------------------------------------------------------------------------------------------
2129
2130
2131 procedure refresh_process_org (
2132 p_process_id in number,
2133 p_org_string in varchar,
2134 p_commit in varchar2 := FND_API.G_FALSE,
2135 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2136 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2137 x_return_status out nocopy varchar2,
2138 x_msg_count out nocopy number,
2139 x_msg_data out nocopy varchar2
2140 ) is
2141
2142 L_API_NAME CONSTANT VARCHAR2(30) := 'refresh_process_org';
2143 l_return_status varchar2(10);
2144 l_msg_count number;
2145 l_msg_data varchar2(4000);
2146 amw_exception exception;
2147 str varchar2(4000);
2148 diff number;
2149 orgstr varchar2(100);
2150 l_org_string varchar2(4000);
2151 orgid number;
2152
2153 begin
2154 x_return_status := FND_API.G_RET_STS_SUCCESS;
2155 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2156 FND_MSG_PUB.initialize;
2157 END IF;
2158 IF FND_GLOBAL.User_Id IS NULL THEN
2159 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
2160 RAISE FND_API.G_EXC_ERROR;
2161 END IF;
2162
2163 l_org_string := p_org_string;
2164 while LENGTH(l_org_string) <> 0 loop
2165 select LTRIM(l_org_string, '1234567890') into str from dual;
2166 diff := LENGTH(l_org_string) - LENGTH(str);
2167 if LENGTH(str) is null then diff := LENGTH(l_org_string); end if;
2168 select SUBSTR(l_org_string, 1, diff) into orgstr from dual;
2169 orgid := to_number(orgstr);
2170
2171 refresh_process_per_org (
2172 p_process_id => p_process_id,
2173 p_org_id => orgid,
2174 x_return_status => l_return_status,
2175 x_msg_count => l_msg_count,
2176 x_msg_data => l_msg_data );
2177
2178 if l_return_status <> 'S' then
2179 raise amw_exception;
2180 end if;
2181
2182 select LTRIM(str, 'x') into l_org_string from dual;
2183 end loop;
2184
2185 reset_org_count;
2186
2187 exception
2188 WHEN amw_exception THEN
2189 ROLLBACK;
2190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2191 x_msg_count := l_msg_count;
2192 x_msg_data := l_msg_data;
2193
2194 WHEN FND_API.G_EXC_ERROR THEN
2195 ROLLBACK;
2196 x_return_status := FND_API.G_RET_STS_ERROR;
2197 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2198
2199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2200 ROLLBACK;
2201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2202 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2203
2204 WHEN amw_deadlock_detected THEN
2205 ROLLBACK;
2206 x_return_status := FND_API.G_RET_STS_ERROR;
2207 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2208
2209 WHEN OTHERS THEN
2210 ROLLBACK;
2211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2212 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2213 THEN
2214 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2215 END IF;
2216 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
2217
2218 end refresh_process_org;
2219
2220
2221 ------------------------------------------------------------------------------------------------------------
2222 ------------------------------------------------------------------------------------------------------------
2223
2224
2225 procedure refresh_process_per_org(
2226 p_process_id in number,
2227 p_org_id in number,
2228 x_return_status out nocopy varchar2,
2229 x_msg_count out nocopy number,
2230 x_msg_data out nocopy varchar2
2231 ) is
2232
2233 l_return_status varchar2(10);
2234 l_msg_count number;
2235 l_msg_data varchar2(4000);
2236 l_parent_pid number;
2237 l_name varchar2(100);
2238 v_index number;
2239 amw_exception exception;
2240
2241 cursor c_parents(l_org number, l_proc number) is
2242 select parent_process_id
2243 from Amw_Process_Org_Relations
2244 where organization_id = p_org_id
2245 and child_process_id = p_process_id;
2246
2247 type t_parent_pid is table of amw_process.process_id%type;
2248 v_parent_pid t_parent_pid;
2249
2250 begin
2251
2252 x_return_status := FND_API.G_RET_STS_SUCCESS;
2253
2254 open c_parents(p_org_id, p_process_id);
2255 loop
2256 fetch c_parents bulk collect into v_parent_pid;
2257 exit when c_parents%notfound;
2258 end loop;
2259 close c_parents;
2260
2261 if v_parent_pid.count = 0 then
2262 return;
2263 end if;
2264
2265 disassociate_process_org(
2266 p_process_id => p_process_id,
2267 p_org_id => p_org_id,
2268 x_return_status => l_return_status,
2269 x_msg_count => l_msg_count,
2270 x_msg_data => l_msg_data);
2271
2272 if l_return_status <> 'S' then
2273 raise amw_exception;
2274 end if;
2275
2276 v_index := v_parent_pid.first;
2277 while v_index <= v_parent_pid.last
2278 loop
2279
2280 assoc_process_org_hier(p_process_id, p_org_id, v_parent_pid(v_index),l_return_status, l_msg_count, l_msg_data);
2281
2282 if l_return_status <> 'S' then
2283 raise amw_exception;
2284 end if;
2285
2286 insert into Amw_Process_Org_Relations
2287 (ORGANIZATION_ID,
2288 PARENT_PROCESS_ID,
2289 CHILD_PROCESS_ID,
2290 INSTANCE_ID,
2291 EXCEPTION_PRESENT_FLAG,
2292 LAST_UPDATE_DATE,
2293 LAST_UPDATED_BY,
2294 CREATION_DATE,
2295 CREATED_BY,
2296 LAST_UPDATE_LOGIN,
2297 OBJECT_VERSION_NUMBER)
2298 values
2299 (p_org_id,
2300 v_parent_pid(v_index),
2301 p_process_id,
2302 0,
2303 'N',
2304 sysdate,
2305 G_USER_ID,
2306 sysdate,
2307 G_USER_ID,
2308 G_LOGIN_ID,
2309 1);
2310
2311 v_index := v_parent_pid.next(v_index);
2312 end loop;
2313
2314
2315 select name
2316 into l_name
2317 from amw_process
2318 where process_id = p_process_id;
2319
2320 create_org_relations(l_name, p_org_id, l_return_status, l_msg_count, l_msg_data);
2321
2322 if l_return_status <> 'S' then
2323 raise amw_exception;
2324 end if;
2325
2326 populate_flatlist(p_org_id);
2327 reset_proc_org_risk_ctrl_count(p_org_id);
2328 v_parent_pid.trim;
2329
2330 exception
2331
2332 WHEN amw_deadlock_detected THEN
2333 ROLLBACK;
2334 x_return_status := FND_API.G_RET_STS_ERROR;
2335 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2336
2337 WHEN amw_exception THEN
2338 ROLLBACK;
2339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2340 x_msg_count := l_msg_count;
2341 x_msg_data := l_msg_data;
2342
2343 WHEN OTHERS THEN
2344 ROLLBACK;
2345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2346 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
2347
2348 end refresh_process_per_org;
2349
2350
2351 ------------------------------------------------------------------------------------------------------------
2352 ------------------------------------------------------------------------------------------------------------
2353
2354 function check_org_children_exist(p_process_id in number) return number
2355 is
2356 l_pname varchar2(30);
2357 l_dummy number;
2358 j number;
2359 begin
2360 select name into l_pname from amw_process where process_id = p_process_id;
2361 find_hierarchy_children(l_pname);
2362 j := oldCount1 + 1;
2363 FOR i IN j..v_child_name.count LOOP
2364 begin
2365 select 1 into l_dummy
2366 from amw_process_organization
2367 where end_date is null
2368 and process_id = (select process_id from amw_process where name = v_child_name(i));
2369 oldCount1 := v_child_name.count;
2370 return 1;
2371 exception
2372 when too_many_rows then
2373 oldCount1 := v_child_name.count;
2374 return 1;
2375 when no_data_found then
2376 null;
2377 end;
2378 END LOOP;
2379 oldCount1 := v_child_name.count;
2380 return 0;
2381 end check_org_children_exist;
2382
2383 ------------------------------------------------------------------------------------------------------------
2384 ------------------------------------------------------------------------------------------------------------
2385
2386 -- Check if current process or any of its children exists in any org. If yes, return 1, else 0
2387 procedure check_org_exist( p_process_id in number,
2388 p_out out nocopy number,
2389 x_return_status out nocopy varchar2,
2390 x_msg_count out nocopy number,
2391 x_msg_data out nocopy varchar2)
2392 is
2393 l_dummy number;
2394 begin
2395 x_return_status := FND_API.G_RET_STS_SUCCESS;
2396
2397 select 1 into l_dummy
2398 from amw_process_organization
2399 where process_id = p_process_id
2400 and end_date is null;
2401
2402 p_out := 1;
2403 exception
2404 when too_many_rows then
2405 p_out := 1;
2406
2407 when no_data_found then
2408 p_out := check_org_children_exist(p_process_id);
2409
2410 WHEN OTHERS THEN
2411 ROLLBACK;
2412 p_out := 0;
2413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2414 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
2415
2416 end check_org_exist;
2417
2418
2419 ------------------------------------------------------------------------------------------------------------
2420 ------------------------------------------------------------------------------------------------------------
2421
2422
2423 -- Check if active certification is going on for this process or any of its children in any org
2424 -- If yes, return 1, else 0
2425
2426 procedure check_cert_exist( p_process_id in number,
2427 p_out out nocopy number,
2428 x_return_status out nocopy varchar2,
2429 x_msg_count out nocopy number,
2430 x_msg_data out nocopy varchar2)
2431 is
2432 begin
2433 x_return_status := FND_API.G_RET_STS_SUCCESS;
2434 p_out := 0;
2435 end check_cert_exist;
2436
2437 ------------------------------------------------------------------------------------------------------------
2438 ------------------------------------------------------------------------------------------------------------
2439
2440
2441 -- Check if active certification is going on for this process or any of its children in a particular org
2442 -- If yes, return 1, else 0
2443
2444 procedure check_cert_exist( p_process_id in number,
2445 p_out out nocopy number,
2446 p_org_id in number,
2447 x_return_status out nocopy varchar2,
2448 x_msg_count out nocopy number,
2449 x_msg_data out nocopy varchar2)
2450 is
2451 begin
2452 x_return_status := FND_API.G_RET_STS_SUCCESS;
2453 p_out := 0;
2454 end check_cert_exist;
2455
2456
2457 ------------------------------------------------------------------------------------------------------------
2461 -- Check if current process or any of its children exists in any org. (1)
2458 ------------------------------------------------------------------------------------------------------------
2459
2460
2462 -- OR
2463 -- Check if active certification is going on for this process or any of its children in any org (2)
2464 -- p_check return values:
2465 -- (1) and (2): 2
2466 -- (1) only: 1
2467 -- (2) only: 2
2468 -- none: 0
2469
2470 procedure check_org_cert_exist( p_process_id in number,
2471 p_check out nocopy number,
2472 x_return_status out nocopy varchar2,
2473 x_msg_count out nocopy number,
2474 x_msg_data out nocopy varchar2)
2475 is
2476
2477 l_return_status varchar2(10);
2478 l_msg_count number;
2479 l_msg_data varchar2(4000);
2480 p_org_out number := 0;
2481 p_cert_out number := 0;
2482 amw_exception exception;
2483
2484 begin
2485 x_return_status := FND_API.G_RET_STS_SUCCESS;
2486
2487 check_org_exist( p_process_id => p_process_id,
2488 p_out => p_org_out,
2489 x_return_status => l_return_status,
2490 x_msg_count => l_msg_count,
2491 x_msg_data => l_msg_data );
2492
2493 if l_return_status <> 'S' then
2494 raise amw_exception;
2495 end if;
2496
2497 check_cert_exist( p_process_id => p_process_id,
2498 p_out => p_cert_out,
2499 x_return_status => l_return_status,
2500 x_msg_count => l_msg_count,
2501 x_msg_data => l_msg_data );
2502
2503 if l_return_status <> 'S' then
2504 raise amw_exception;
2505 end if;
2506
2507 if p_org_out = 0 AND p_cert_out = 0 then
2508 p_check := 0;
2509 elsif p_org_out = 0 AND p_cert_out = 1 then
2510 p_check := 2;
2511 elsif p_org_out = 1 AND p_cert_out = 0 then
2512 p_check := 1;
2513 elsif p_org_out = 1 AND p_cert_out = 1 then
2514 p_check := 2;
2515 end if;
2516
2517 exception
2518 WHEN amw_exception THEN
2519 ROLLBACK;
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521 x_msg_count := l_msg_count;
2522 x_msg_data := l_msg_data;
2523
2524 WHEN OTHERS THEN
2525 ROLLBACK;
2526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2527 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
2528
2529 end check_org_cert_exist;
2530
2531
2532 ------------------------------------------------------------------------------------------------------------
2533 ------------------------------------------------------------------------------------------------------------
2534
2535
2536 function check_org_user_permission(org_id in number) return number is
2537 userId number := FND_GLOBAL.User_Id;
2538 l_dummy number;
2539 begin
2540 if (fnd_profile.value('AMW_ORG_SECURITY_SWITCH') = 'N') OR (fnd_profile.value('AMW_ORG_SECURITY_SWITCH') is null) then
2541 return 1;
2542 end if;
2543
2544 if fnd_profile.value('AMW_ACCESS_ALL_ORGS') = 'Y' then
2545 return 1;
2546 else
2547 l_dummy := isProcessOwner(userId, org_id) + hasOrgAccess(userId, org_id);
2548 if (l_dummy = 1) OR (l_dummy = 2) then
2549 return 1;
2550 else
2551 return 0;
2552 end if;
2553 end if;
2554 end check_org_user_permission;
2555
2556
2557 ------------------------------------------------------------------------------------------------------------
2558 ------------------------------------------------------------------------------------------------------------
2559
2560
2561 function isProcessOwner(p_user_id in number, p_org_id in number) return number is
2562 l_empid number;
2563 l_dummy number;
2564 l_party_id number;
2565 begin
2566 select nvl(employee_id, 0) into l_empid from fnd_user where user_id = p_user_id;
2567
2568 if l_empid = 0 then return 0; end if;
2569
2570 begin
2571 select party_id into l_party_id from amw_employees_current_v where employee_id = l_empid;
2572 exception
2573 when no_data_found then
2574 return 0;
2575 end;
2576
2577 begin
2578 select 1 into l_dummy
2579 from amw_process_organization
2580 where end_date is null
2581 and organization_id = p_org_id
2582 and process_owner_id = l_party_id;
2583 return 1;
2584 exception
2585 when too_many_rows then
2586 return 1;
2587 when no_data_found then
2588 return 0;
2589 end;
2590 end isProcessOwner;
2591
2592
2593 ------------------------------------------------------------------------------------------------------------
2594 ------------------------------------------------------------------------------------------------------------
2595
2596
2597 function hasOrgAccess(p_user_id in number, p_org_id in number) return number is
2598 l_empid number;
2599 l_dummy number;
2600 begin
2601 -- check if the person is a manager of any audit unit. If no, return 0;
2602 -- If yes, check if he's the manager of the given org. If yes, return 1.
2603 -- if no, check if the current org is in the downward hierarchy of any of the orgs the person is a manager of; If yes, return 1, else return 0;
2604
2605 select nvl(employee_id, 0) into l_empid from fnd_user where user_id = p_user_id;
2606
2607 if l_empid = 0 then return 0; end if;
2608
2609 begin
2610 select org_information2
2611 into l_dummy
2612 from hr_organization_information
2613 where organization_id in (select distinct organization_id from amw_audit_units_v)
2614 and org_information_context = 'Organization Name Alias'
2615 and org_information2 = l_empid;
2616 exception
2617 when no_data_found then
2618 return 0;
2619
2620 when too_many_rows then
2621 null;
2622 end;
2623
2624 begin
2625 select 1
2626 into l_dummy
2627 from hr_organization_information
2628 where organization_id = p_org_id
2629 and org_information_context = 'Organization Name Alias'
2630 and org_information2 = l_empid;
2631
2632 return 1;
2633 exception
2634 when no_data_found then
2635 return checkOrgHier(l_empid, p_org_id);
2636 end;
2637
2638 end hasOrgAccess;
2639
2640
2641 ------------------------------------------------------------------------------------------------------------
2642 ------------------------------------------------------------------------------------------------------------
2643
2644 function checkOrgHier(p_emp_id in number, p_org_id in number) return number is
2645 l_dummy number;
2646 l_org number;
2647 hier_name varchar2(30);
2648
2649 cursor c_orgs(l_emp number) is
2650 select hoi.organization_id
2651 from hr_organization_information hoi, amw_audit_units_v aauv
2652 where hoi.organization_id = aauv.organization_id
2653 and hoi.org_information_context = 'Organization Name Alias'
2654 and hoi.org_information2 = l_emp;
2655
2656 begin
2657
2658 hier_name := fnd_profile.value('AMW_ORG_SECURITY_HIERARCHY');
2659
2660 if hier_name is null then return 0; end if;
2661
2662 open c_orgs(p_emp_id);
2663 loop
2664 fetch c_orgs into l_org;
2665 exit when c_orgs%notfound;
2666
2667 begin
2668 select 1 into l_dummy
2669 from dual where p_org_id in
2670 (select organization_id_child
2671 from
2675 where date_to is null and organization_structure_id =
2672 (select organization_id_parent, organization_id_child from per_org_structure_elements
2673 where org_structure_version_id =
2674 (select org_structure_version_id from per_org_structure_versions
2676 (select organization_structure_id from per_organization_structures where name = hier_name)))
2677 start with organization_id_parent = l_org
2678 connect by organization_id_parent = prior organization_id_child);
2679
2680 return 1;
2681
2682 exception
2683 when no_data_found then
2684 null;
2685
2686 when too_many_rows then
2687 return 1;
2688 end;
2689 end loop;
2690 close c_orgs;
2691 return 0;
2692 end checkOrgHier;
2693
2694 ------------------------------------------------------------------------------------------------------------
2695 ------------------------------------------------------------------------------------------------------------
2696 -- abb added
2697 procedure find_amwp_hierarchy_children(p_process_id in number)
2698 is
2699 cursor c1 (l_id number) is
2700 select b2.process_id
2701 from amw_wf_hierarchy_v a, amw_process b1, amw_process b2
2702 where b1.name = a.parent_process_name
2703 and b1.item_type = a.parent_item_type
2704 and b2.name = a.child_process_name
2705 and b2.item_type = a.child_item_type
2706 and b1.process_id = l_id;
2707
2708 c1_rec c1%rowtype;
2709
2710 begin
2711 for c1_rec in c1(p_process_id) loop
2712 exit when c1%notfound;
2713 v_child_id.extend(1);
2714 v_child_id(v_child_id.count) := c1_rec.process_id;
2715 find_amwp_hierarchy_children(p_process_id =>c1_rec.process_id);
2716 end loop;
2717 end find_amwp_hierarchy_children;
2718
2719
2720 ------------------------------------------------------------------------------------------------------------
2721 ------------------------------------------------------------------------------------------------------------
2722
2723 -- abb added
2724 procedure find_amwp_hierarchy_parent(p_process_id in number)
2725 is
2726 cursor c1 (l_id number) is
2727 select b1.process_id
2728 from amw_wf_hierarchy_v a, amw_process b1, amw_process b2
2729 where b1.name = a.parent_process_name
2730 and b1.item_type = a.parent_item_type
2731 and b2.name = a.child_process_name
2732 and b2.item_type = a.child_item_type
2733 and b2.process_id = l_id;
2734
2735 c1_rec c1%rowtype;
2736
2737 begin
2738 for c1_rec in c1(p_process_id) loop
2739 exit when c1%notfound;
2740 v_parent_id.extend(1);
2741 v_parent_id(v_parent_id.count) := c1_rec.process_id;
2742 find_amwp_hierarchy_parent(p_process_id =>c1_rec.process_id);
2743 end loop;
2744 end find_amwp_hierarchy_parent;
2745
2746
2747 ------------------------------------------------------------------------------------------------------------
2748 ------------------------------------------------------------------------------------------------------------
2749 -- abb changed
2750 procedure populate_proc_flatlist is
2751
2752 cursor c12 is
2753 select process_id from amw_process;
2754
2755 type t_ap_id is table of amw_process.process_id%type;
2756 v_ap_id t_ap_id;
2757
2758 v_index number;
2759 v_indexDown number;
2760 j number;
2761 jDown number;
2762 l_process_id number;
2763 l_child_id number;
2764 l_parent_id number;
2765 l_dummy number;
2766 -- there is still some scope of performance improvement. E.g. the denorm data
2767 -- could be stored in a temporary table of records and then bulk inserted into the database.
2768 -- But pre 9i doesn't support table-in-table or index by varchar tables, so
2769 -- the check for duplicates would involve looping through the whole table.
2770 -- for tables of large size, that itself is expensive, and would counter the gains
2771 -- obtained by bulk insert. If further performance problem is reported, we'll
2772 -- try out these tricks.
2773 begin
2774 delete from Amw_Proc_Hierarchy_Denorm;
2775 open c12;
2776 loop
2777 fetch c12 bulk collect into v_ap_id;
2778 exit when c12%notfound;
2779 end loop;
2780 close c12;
2781 v_index := v_ap_id.first;
2782 while v_index <= v_ap_id.last
2783 loop
2784 -- insert upward hierarchy
2785
2786 v_parent_id := t_parent_id();
2787 find_amwp_hierarchy_parent(v_ap_id(v_index));
2788 l_process_id := v_ap_id(v_index);
2789 FOR i IN 1..v_parent_id.count LOOP
2790 l_parent_id := v_parent_id(i);
2791 begin
2792 select 1 into l_dummy
2793 from Amw_Proc_Hierarchy_Denorm
2794 where Process_Id = l_process_id
2795 and Parent_Child_Id = l_parent_id
2796 and Up_Down_Ind = 'U';
2797 exception
2798 when no_data_found then
2799 insert into Amw_Proc_Hierarchy_Denorm
2800 (Process_Id,
2801 Parent_Child_Id,
2802 Up_Down_Ind,
2803 Last_Update_Date,
2804 Last_Updated_By,
2805 Creation_Date,
2806 Created_By,
2807 Last_Update_Login,
2808 OBJECT_VERSION_NUMBER
2809 )
2810 values
2811 (l_process_id,
2812 l_parent_id,
2813 'U',
2814 sysdate,
2815 G_USER_ID,
2816 sysdate,
2817 G_USER_ID,
2818 G_LOGIN_ID,
2819 1);
2820 end;
2821 END LOOP;
2822
2823 -- insert downward hierarchy
2824
2825 v_child_id := t_child_id();
2826 find_amwp_hierarchy_children(v_ap_id(v_index));
2827 FOR i IN 1..v_child_id.count LOOP
2828 l_child_id := v_child_id(i);
2829 begin
2830 select 1 into l_dummy
2831 from Amw_Proc_Hierarchy_Denorm
2832 where Process_Id = l_process_id
2833 and Parent_Child_Id = l_child_id
2834 and Up_Down_Ind = 'D';
2835 exception
2836 when no_data_found then
2837 insert into Amw_Proc_Hierarchy_Denorm
2838 (Process_Id,
2839 Parent_Child_Id,
2840 Up_Down_Ind,
2841 Last_Update_Date,
2842 Last_Updated_By,
2843 Creation_Date,
2844 Created_By,
2845 Last_Update_Login,
2846 OBJECT_VERSION_NUMBER
2847 )
2848 values
2849 (l_process_id,
2850 l_child_id,
2851 'D',
2852 sysdate,
2853 G_USER_ID,
2854 sysdate,
2855 G_USER_ID,
2856 G_LOGIN_ID,
2857 1);
2858 end;
2859 END LOOP;
2860
2861 v_index := v_ap_id.next(v_index);
2862 end loop;
2863 end populate_proc_flatlist;
2864
2865
2866 ------------------------------------------------------------------------------------------------------------
2867 ------------------------------------------------------------------------------------------------------------
2868
2869
2873 is
2870 procedure adhoc_synch_hier_amw_proc ( x_return_status out nocopy varchar2,
2871 x_msg_count out nocopy number,
2872 x_msg_data out nocopy varchar2)
2874
2875 L_API_NAME CONSTANT VARCHAR2(30) := 'synch_hierarchy_amw_process';
2876 p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
2877
2878 type t_amwp_name is table of amw_process.name%type;
2879 v_amwp_name t_amwp_name;
2880 type t_amwp_pid is table of amw_process.process_id%type;
2881 v_amwp_pid t_amwp_pid;
2882
2883 type t_org is table of amw_process_organization.organization_id%type;
2884 v_org t_org;
2885
2886 --type rec_amw IS record
2887 -- (name amw_process.name%type,
2888 -- process_id amw_process.process_id%type);
2889 --v_rec_amw rec_amw;
2890 --type t_amw IS table of rec_amw index by binary_integer;
2891 --v_amw t_amw;
2892
2893 -- 8.1.7 compatibility issue
2894 /*
2895 type t_amwp_name_pid is table of amw_process.process_id%type index by VARCHAR2(30);
2896 v_amwp_name_pid t_amwp_name_pid;
2897 */
2898 -- added by abedajna
2899 /*
2900 type t_wf_name is table of wf_activities.name%type;
2901 v_wf_name t_wf_name;
2902 type t_wf_name_table IS table of t_wf_name index by binary_integer;
2903 v_wf_name_table t_wf_name_table;
2904 */
2905
2906 type t_delete_list IS table of amw_process.name%type index by binary_integer;
2907 v_delete_list t_delete_list;
2908
2909 type t_delete_pid_list IS table of amw_process.process_id%type index by binary_integer;
2910 v_delete_pid_list t_delete_pid_list;
2911
2912 -- 8.1.7 compatibility issue
2913 /*
2914 type t_child_list IS table of amw_process.name%type index by VARCHAR2(30);
2915 v_child_list t_child_list;
2916 */
2917
2918 root_process_name amw_process.name%type;
2919 v_index NUMBER;
2920 v_index1 NUMBER;
2921 xst BOOLEAN;
2922 delete_row_cnt NUMBER;
2923 x_return_status1 varchar2(10);
2924 x_msg_count1 number;
2925 x_msg_data1 varchar2(4000);
2926
2927 cursor c_amwp_name is
2928 select name, process_id
2929 from amw_process;
2930
2931 cursor c_org (p_pid number) is
2932 select organization_id
2933 from amw_process_organization
2934 where process_id = p_pid;
2935
2936 cursor exceptions_to_be_del is
2937 select exception_id
2938 from amw_exceptions_b
2939 where object_type = 'PROCESS_VARIANT_ADD'
2940 and new_pk1 not in
2941 (select process_id from amw_process where standard_process_flag = 'Y');
2942
2943 l_ex_id number;
2944 hvalue number;
2945 v_inner_index number;
2946
2947 amw_processing_exception exception;
2948
2949
2950 begin
2951 x_return_status := FND_API.G_RET_STS_SUCCESS;
2952 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2953 FND_MSG_PUB.initialize;
2954 END IF;
2955 IF FND_GLOBAL.User_Id IS NULL THEN
2956 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
2957 RAISE FND_API.G_EXC_ERROR;
2958 END IF;
2959
2960 -- go through amw_process, see if the process does NOT exist as a child of ALL Process
2961 -- in wf, in that case, delete the row from amw_process. This is to take care of
2962 -- dangling rows in amw_process.
2963 open c_amwp_name;
2964 loop
2965 fetch c_amwp_name bulk collect into v_amwp_name, v_amwp_pid;
2966 exit when c_amwp_name%notfound;
2967 end loop;
2968 close c_amwp_name;
2969
2970 -- 8.1.7 compatibility issue
2971 /*
2972 v_index := v_amwp_name.first;
2973 while v_index <= v_amwp_name.last
2974 loop
2975 --kosriniv v_amwp_name_pid( dbms_utility.get_hash_value(v_amwp_name(v_index), 1000, 5625) ) := v_amwp_pid(v_index);
2976 v_amwp_name_pid( v_amwp_name(v_index) ) := v_amwp_pid(v_index);
2977 v_index := v_amwp_name.next(v_index);
2978 end loop;
2979 */
2980
2981 select name
2982 into root_process_name
2983 from amw_process
2984 where process_id = -1;
2985
2986 find_hierarchy_children(root_process_name);
2987
2988 -- 8.1.7 compatibility issue
2989 /*
2990 FOR i IN 1..v_child_name.count LOOP
2991 --kosriniv v_child_list(dbms_utility.get_hash_value(v_child_name(i), 1000, 5625)) := v_child_name(i);
2992 v_child_list(v_child_name(i)) := v_child_name(i);
2993 END LOOP;
2994 */
2995 -- added by abedajna
2996 /*
2997 FOR i IN 1..v_child_name.count LOOP
2998 hvalue := dbms_utility.get_hash_value(v_child_name(i), 3, POWER(2,15));
2999 if v_wf_name_table.exists(hvalue) then -- entry exists
3000 v_wf_name := v_wf_name_table(hvalue);
3001 v_wf_name.extend(1);
3002 v_wf_name(v_wf_name.count) := v_child_name(i);
3003 v_wf_name_table(hvalue) := v_wf_name;
3004 else
3005 v_wf_name := t_wf_name();
3006 v_wf_name.extend(1);
3007 v_wf_name(1) := v_child_name(i);
3008 v_wf_name_table(hvalue) := v_wf_name;
3009 end if;
3010 end loop;
3011 */
3012
3013 delete_row_cnt := 0;
3014 v_index := v_amwp_name.first;
3015 while v_index <= v_amwp_name.last
3016 loop
3017 /*
3018 -- abedajna: check if the child amwp name exists in v_wf_name_table
3019 hvalue := dbms_utility.get_hash_value(v_amwp_name(v_index), 3, POWER(2,15));
3020 begin
3021 v_wf_name := v_wf_name_table(hvalue);
3022
3023 v_inner_index := v_wf_name.first;
3024 while v_inner_index <= v_wf_name.last
3025 loop
3026 if v_amwp_name(v_index) = v_wf_name(v_inner_index) then
3027 xst := true;
3028 exit;
3029 end if;
3030 v_inner_index := v_wf_name.next(v_inner_index);
3031 end loop;
3035 end;
3032 exception
3033 when no_data_found then
3034 xst := false;
3036 */
3037 xst := false;
3038 v_inner_index := v_child_name.first;
3039 while v_inner_index <= v_child_name.last
3040 loop
3041 if v_amwp_name(v_index) = v_child_name(v_inner_index) then
3042 xst := true;
3043 exit;
3044 end if;
3045 v_inner_index := v_child_name.next(v_inner_index);
3046 end loop;
3047
3048 if ( (xst = false) AND (root_process_name <> v_amwp_name(v_index)) ) then
3049 delete_row_cnt := delete_row_cnt + 1;
3050 v_delete_list(delete_row_cnt) := v_amwp_name(v_index);
3051 end if;
3052 v_index := v_amwp_name.next(v_index);
3053 end loop;
3054
3055 -- this produces PLS-00801: internal error [74301] during compilation!
3056 -- delete risk associations in the risk library.
3057 -- FORALL v_ind IN 1..delete_row_cnt
3058 -- DELETE from amw_risk_associations
3059 -- where pk1 = v_amwp_name_pid( v_delete_list(v_ind) )
3060 -- and object_type = 'PROCESS';
3061
3062 -- delete risk associations in the risk library.
3063 v_index := v_delete_list.first;
3064 while v_index <= v_delete_list.last
3065 loop
3066 -- opportunity for performance improvement exist
3067 select process_id
3068 into v_delete_pid_list(v_index)
3069 from amw_process
3070 where item_type = 'AUDITMGR'
3071 and name = v_delete_list(v_index);
3072 -- v_delete_pid_list(v_index) := v_amwp_name_pid( v_delete_list(v_index) );
3073 v_index := v_delete_list.next(v_index);
3074 end loop;
3075
3076
3077
3078
3079 -- UNIT TESTING BEGIN --
3080 -- v_index := v_delete_list.first;
3081 -- dbms_output.put_line('PRINTING DELETE LIST, NAME');
3082 -- while v_index <= v_delete_list.last
3083 -- loop
3084 -- dbms_output.put_line('index: '||v_index||' value: '||v_delete_list(v_index));
3085 -- v_index := v_delete_list.next(v_index);
3086 -- end loop;
3087
3088 -- v_index := v_delete_pid_list.first;
3089 -- line_number := 11;
3090 -- dbms_output.put_line('PRINTING DELETE LIST, PID');
3091 -- while v_index <= v_delete_pid_list.last
3092 -- loop
3093 -- dbms_output.put_line('index: '||v_index||' value: '||v_delete_pid_list(v_index) );
3094 -- v_index := v_delete_pid_list.next(v_index);
3095 -- end loop;
3096 -- UNIT TESTING END --
3097
3098
3099 -- disassociate these processes from all the orgs they are associated with
3100 v_index := v_delete_pid_list.first;
3101 while v_index <= v_delete_pid_list.last
3102 loop
3103 open c_org( v_delete_pid_list(v_index) );
3104 loop
3105 fetch c_org bulk collect into v_org;
3106 exit when c_org%notfound;
3107 end loop;
3108 close c_org;
3109
3110 v_index1 := v_org.first;
3111 while v_index1 <= v_org.last
3112 loop
3113 disassociate_process_org(p_process_id => v_delete_pid_list(v_index),
3114 p_org_id => v_org(v_index1),
3115 x_return_status => x_return_status1,
3116 x_msg_count => x_msg_count1,
3117 x_msg_data => x_msg_data1);
3118
3119 -- kosriniv begin :- Check the return status, and if error then raise the amw_processing_exception to handle it.
3120 IF x_return_status1 <> FND_API.G_RET_STS_SUCCESS THEN
3121 RAISE amw_processing_exception;
3122 END IF;
3123 -- kosriniv end
3124 v_index1 := v_org.next(v_index1);
3125 end loop;
3126 v_index := v_delete_pid_list.next(v_index);
3127 end loop;
3128
3129
3130 FORALL v_ind IN 1..delete_row_cnt
3131 DELETE from amw_risk_associations
3132 where pk1 = v_delete_pid_list(v_ind)
3133 and object_type = 'PROCESS';
3134
3135 FORALL v_ind IN 1..delete_row_cnt
3136 DELETE from amw_objective_associations
3137 where pk1 = v_delete_pid_list(v_ind)
3138 and object_type = 'PROCESS';
3139
3140 -- delete from amw_process
3141 FORALL v_ind IN 1..delete_row_cnt
3142 DELETE from amw_process
3143 where name = v_delete_list(v_ind);
3144
3145 -- endate these processes in wf_activities and call wf purge api's to delete them
3146 FORALL v_ind IN 1..delete_row_cnt
3147 update wf_activities
3148 set end_date = sysdate
3149 where name = v_delete_list(v_ind)
3150 and item_type = 'AUDITMGR'
3151 and end_date is null;
3152
3153 --wf_purge.Items(
3154 -- itemtype => 'AUDITMGR',
3155 -- docommit => false);
3156 --wf_purge.Activities(
3157 -- itemtype => 'AUDITMGR');
3158
3159 v_child_name.delete;
3160 child_num := 0;
3161 --v_child_list.delete;
3162 v_delete_list.delete;
3163 v_delete_pid_list.delete;
3164 --v_amwp_name_pid.delete;
3165 v_amwp_name.trim;
3166 v_amwp_pid.trim;
3167
3168 -- nullify the standard variations that have been deleted
3169 update amw_process ap1
3170 set ap1.standard_variation =
3171 (select ap2.process_id from amw_process ap2 where ap2.process_id = ap1.standard_variation);
3172
3173 -- delete the corresponding variation exceptions created for them.
3174 open exceptions_to_be_del;
3175 loop
3176 fetch exceptions_to_be_del into l_ex_id;
3177 exit when exceptions_to_be_del%notfound;
3178 delete from amw_exceptions_b where exception_id = l_ex_id;
3179 delete from amw_exceptions_reasons where exception_id = l_ex_id;
3180 delete from amw_exceptions_tl where exception_id = l_ex_id;
3181 end loop;
3182 close exceptions_to_be_del;
3183
3184 -- delete those process rows from amw_process which have been deleted from wf
3185 delete from amw_process a where not exists
3186 (select name from wf_activities w where w.name = a.name and w.item_type = 'AUDITMGR' and w.end_date is null);
3187
3188 --populate_proc_flatlist;
3189 --reset_org_count;
3190 --reset_process_risk_ctrl_count;
3194
3191 -- reset_proc_org_risk_ctrl_count;
3192
3193 exception
3195 WHEN amw_deadlock_detected THEN
3196 ROLLBACK;
3197 x_return_status := FND_API.G_RET_STS_ERROR;
3198 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3199
3200 WHEN FND_API.G_EXC_ERROR THEN
3201 ROLLBACK;
3202 x_return_status := FND_API.G_RET_STS_ERROR;
3203 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3204
3205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3206 ROLLBACK;
3207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3208 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3209
3210
3211 -- kosriniv begin :- Handle amw_processing_exception
3212 WHEN amw_processing_exception THEN
3213 ROLLBACK;
3214 x_return_status := x_return_status1;
3215 x_msg_count := x_msg_count1;
3216 IF x_msg_count = 1 THEN
3217 x_msg_data := x_msg_data1;
3218 END IF;
3219 -- kosriniv end.
3220
3221 WHEN OTHERS THEN
3222 ROLLBACK;
3223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3224 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3225 THEN
3226 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
3227 END IF;
3228 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3229
3230 end adhoc_synch_hier_amw_proc;
3231
3232 ------------------------------------------------------------------------------------------------------------
3233 ------------------------------------------------------------------------------------------------------------
3234 -- DO NOT CALL THIS PROCEDURE FROM ANYWHERE IN AMW.D CODE. THIS IS BEING PROVIDED TO FIX BUG 4306756
3235 -- THIS IS CALLED ONLY FROM AMWDTFIX.SQL
3236
3237 procedure old_synch_hier_amw_process( x_return_status out nocopy varchar2,
3238 x_msg_count out nocopy number,
3239 x_msg_data out nocopy varchar2)
3240 is
3241
3242 L_API_NAME CONSTANT VARCHAR2(30) := 'old_synch_hier_amw_process';
3243 p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
3244
3245 type t_amwp_name is table of amw_process.name%type;
3246 v_amwp_name t_amwp_name;
3247 type t_amwp_pid is table of amw_process.process_id%type;
3248 v_amwp_pid t_amwp_pid;
3249
3250 type t_org is table of amw_process_organization.organization_id%type;
3251 v_org t_org;
3252
3253 type t_delete_list IS table of amw_process.name%type index by binary_integer;
3254 v_delete_list t_delete_list;
3255
3256 type t_delete_pid_list IS table of amw_process.process_id%type index by binary_integer;
3257 v_delete_pid_list t_delete_pid_list;
3258
3259 root_process_name amw_process.name%type;
3260 v_index NUMBER;
3261 v_index1 NUMBER;
3262 xst BOOLEAN;
3263 delete_row_cnt NUMBER;
3264 x_return_status1 varchar2(10);
3265 x_msg_count1 number;
3266 x_msg_data1 varchar2(4000);
3267
3268 cursor c_amwp_name is
3269 select name, process_id
3270 from amw_process;
3271
3272 cursor c_org (p_pid number) is
3273 select organization_id
3274 from amw_process_organization
3275 where process_id = p_pid;
3276
3277 cursor exceptions_to_be_del is
3278 select exception_id
3279 from amw_exceptions_b
3280 where object_type = 'PROCESS_VARIANT_ADD'
3281 and new_pk1 not in
3282 (select process_id from amw_process where standard_process_flag = 'Y');
3283
3284 l_ex_id number;
3285 hvalue number;
3286 v_inner_index number;
3287
3288 amw_processing_exception exception;
3289
3290
3291 begin
3292 x_return_status := FND_API.G_RET_STS_SUCCESS;
3293 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3294 FND_MSG_PUB.initialize;
3295 END IF;
3296 IF FND_GLOBAL.User_Id IS NULL THEN
3297 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
3298 RAISE FND_API.G_EXC_ERROR;
3299 END IF;
3300
3301 -- go through amw_process, see if the process does NOT exist as a child of ALL Process
3302 -- in wf, in that case, delete the row from amw_process. This is to take care of
3303 -- dangling rows in amw_process.
3304 open c_amwp_name;
3305 loop
3306 fetch c_amwp_name bulk collect into v_amwp_name, v_amwp_pid;
3307 exit when c_amwp_name%notfound;
3308 end loop;
3309 close c_amwp_name;
3310
3311 select name
3312 into root_process_name
3313 from amw_process
3314 where process_id = -1;
3315
3316 find_hierarchy_children(root_process_name);
3317
3318 delete_row_cnt := 0;
3319 v_index := v_amwp_name.first;
3320 while v_index <= v_amwp_name.last
3321 loop
3322 xst := false;
3323 v_inner_index := v_child_name.first;
3324 while v_inner_index <= v_child_name.last
3325 loop
3326 if v_amwp_name(v_index) = v_child_name(v_inner_index) then
3327 xst := true;
3328 exit;
3329 end if;
3330 v_inner_index := v_child_name.next(v_inner_index);
3331 end loop;
3332
3333 if ( (xst = false) AND (root_process_name <> v_amwp_name(v_index)) ) then
3334 delete_row_cnt := delete_row_cnt + 1;
3335 v_delete_list(delete_row_cnt) := v_amwp_name(v_index);
3336 end if;
3337 v_index := v_amwp_name.next(v_index);
3338 end loop;
3339
3340 v_index := v_delete_list.first;
3341 while v_index <= v_delete_list.last
3342 loop
3343 select process_id
3344 into v_delete_pid_list(v_index)
3345 from amw_process
3346 where item_type = 'AUDITMGR'
3347 and name = v_delete_list(v_index);
3348 v_index := v_delete_list.next(v_index);
3352 -- disassociate these processes from all the orgs they are associated with
3349 end loop;
3350
3351
3353 v_index := v_delete_pid_list.first;
3354 while v_index <= v_delete_pid_list.last
3355 loop
3356 open c_org( v_delete_pid_list(v_index) );
3357 loop
3358 fetch c_org bulk collect into v_org;
3359 exit when c_org%notfound;
3360 end loop;
3361 close c_org;
3362
3363 v_index1 := v_org.first;
3364 while v_index1 <= v_org.last
3365 loop
3366 disassociate_process_org(p_process_id => v_delete_pid_list(v_index),
3367 p_org_id => v_org(v_index1),
3368 x_return_status => x_return_status1,
3369 x_msg_count => x_msg_count1,
3370 x_msg_data => x_msg_data1);
3371
3372 -- kosriniv begin :- Check the return status, and if error then raise the amw_processing_exception to handle it.
3373 IF x_return_status1 <> FND_API.G_RET_STS_SUCCESS THEN
3374 RAISE amw_processing_exception;
3375 END IF;
3376 -- kosriniv end
3377 v_index1 := v_org.next(v_index1);
3378 end loop;
3379 v_index := v_delete_pid_list.next(v_index);
3380 end loop;
3381
3382
3383 FORALL v_ind IN 1..delete_row_cnt
3384 DELETE from amw_risk_associations
3385 where pk1 = v_delete_pid_list(v_ind)
3386 and object_type = 'PROCESS';
3387
3388 FORALL v_ind IN 1..delete_row_cnt
3389 DELETE from amw_objective_associations
3390 where pk1 = v_delete_pid_list(v_ind)
3391 and object_type = 'PROCESS';
3392
3393 -- delete from amw_process
3394 FORALL v_ind IN 1..delete_row_cnt
3395 DELETE from amw_process
3396 where name = v_delete_list(v_ind);
3397
3398 -- endate these processes in wf_activities and call wf purge api's to delete them
3399 FORALL v_ind IN 1..delete_row_cnt
3400 update wf_activities
3401 set end_date = sysdate
3402 where name = v_delete_list(v_ind)
3403 and item_type = 'AUDITMGR'
3404 and end_date is null;
3405
3406 wf_purge.Items(
3407 itemtype => 'AUDITMGR',
3408 docommit => false);
3409 wf_purge.Activities(
3410 itemtype => 'AUDITMGR');
3411
3412 v_child_name.delete;
3413 child_num := 0;
3414 --v_child_list.delete;
3415 v_delete_list.delete;
3416 v_delete_pid_list.delete;
3417 --v_amwp_name_pid.delete;
3418 v_amwp_name.trim;
3419 v_amwp_pid.trim;
3420
3421 -- nullify the standard variations that have been deleted
3422 update amw_process ap1
3423 set ap1.standard_variation =
3424 (select ap2.process_id from amw_process ap2 where ap2.process_id = ap1.standard_variation);
3425
3426 -- delete the corresponding variation exceptions created for them.
3427 open exceptions_to_be_del;
3428 loop
3429 fetch exceptions_to_be_del into l_ex_id;
3430 exit when exceptions_to_be_del%notfound;
3431 delete from amw_exceptions_b where exception_id = l_ex_id;
3432 delete from amw_exceptions_reasons where exception_id = l_ex_id;
3433 delete from amw_exceptions_tl where exception_id = l_ex_id;
3434 end loop;
3435 close exceptions_to_be_del;
3436
3437 -- delete those process rows from amw_process which have been deleted from wf
3438 delete from amw_process a where not exists
3439 (select name from wf_activities w where w.name = a.name and w.item_type = 'AUDITMGR' and w.end_date is null);
3440
3441 populate_proc_flatlist;
3442 reset_org_count;
3443 reset_process_risk_ctrl_count;
3444 -- reset_proc_org_risk_ctrl_count;
3445
3446 exception
3447
3448 WHEN amw_deadlock_detected THEN
3449 ROLLBACK;
3450 x_return_status := FND_API.G_RET_STS_ERROR;
3451 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3452
3453 WHEN FND_API.G_EXC_ERROR THEN
3454 ROLLBACK;
3455 x_return_status := FND_API.G_RET_STS_ERROR;
3456 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3457
3458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3459 ROLLBACK;
3460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3461 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3462
3463
3464 -- kosriniv begin :- Handle amw_processing_exception
3465 WHEN amw_processing_exception THEN
3466 ROLLBACK;
3467 x_return_status := x_return_status1;
3468 x_msg_count := x_msg_count1;
3469 IF x_msg_count = 1 THEN
3470 x_msg_data := x_msg_data1;
3471 END IF;
3472 -- kosriniv end.
3473
3474 WHEN OTHERS THEN
3475 ROLLBACK;
3476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3477 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3478 THEN
3479 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
3480 END IF;
3481 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3482
3483 end old_synch_hier_amw_process;
3484
3485
3486
3487 end AMW_WF_HIERARCHY_PKG;