1 PACKAGE BODY AMW_RL_HIERARCHY_PKG AS
2 /*$Header: amwrlhrb.pls 120.6 2006/03/20 02:52:55 appldev noship $*/
3
4 function encode (n1 in number, n2 in number) return varchar2 is
5 begin
6 return to_char(n1) || ':' || to_char(n2);
7 end encode;
8
9 procedure init is
10 begin
11
12 x_index := 0;
13 x_process_tbl := process_tbl();
14 x_parent_child_tbl := parent_child_tbl();
15 x_up_down_ind_tbl := up_down_ind_tbl();
16
17 x_index_tbl.delete;
18 x_t1.delete;
19 x_t2.delete;
20
21 end init;
22
23
24 function is_ancestor_in_hierarchy(p_process1_id in number,
25 p_process2_id in number)
26 return boolean
27 is
28 cursor c_relation_exists (l_ancestor_id number,
29 l_descendant_id number) is
30 select 1 from amw_proc_hierarchy_denorm
31 where process_id = l_ancestor_id and
32 parent_child_id = l_descendant_id and
33 hierarchy_type = 'L' and
34 up_down_ind = 'D';
35 l_dummy number;
36 begin
37
38 open c_relation_exists(p_process1_id, p_process2_id);
39 fetch c_relation_exists into l_dummy;
40 if (c_relation_exists%found)
41 then
42 close c_relation_exists;
43 return true;
44 else
45 close c_relation_exists;
46 return false;
47 end if;
48
49
50 end is_ancestor_in_hierarchy;
51
52 procedure add_rows_to_denorm
53 (p_ancestor_id in number,
54 p_descendant_id in number)
55
56 is
57
58 begin
59 x_index := x_index+2;
60 x_process_tbl.extend(2);
61 x_parent_child_tbl.extend(2);
62 x_up_down_ind_tbl.extend(2);
63
64 --add the new relation
65 x_process_tbl(x_index-1) := p_ancestor_id;
66 x_parent_child_tbl(x_index-1) := p_descendant_id;
67 x_up_down_ind_tbl(x_index-1) := 'D';
68
69 --add the new relation
70 x_process_tbl(x_index) := p_descendant_id;
71 x_parent_child_tbl(x_index) := p_ancestor_id;
72 x_up_down_ind_tbl(x_index) := 'U';
73 x_index_tbl(encode(p_ancestor_id,p_descendant_id)) := 1;
74 end add_rows_to_denorm;
75
76
77 procedure update_denorm_add_child(p_parent_id number,
78 p_child_id number,
79 l_sysdate in Date default sysdate)
80
81 is
82
83 -- CURSOR TO SELECT ALL THE ANCESTORS OF THE GIVEN PROCESS.
84 cursor c_ancestors(l_process_id number) is
85 select parent_child_id
86 from amw_proc_hierarchy_denorm
87 where process_id = l_process_id and
88 up_down_ind = 'U';
89
90 type ancestor_tbl is table of c_ancestors%rowtype;
91
92
93 -- CURSOR TO SELECT ALL THE DESCENDANTS OF THE GIVEN PROCESS.
94 cursor c_descendants (l_process_id number) is
95 select parent_child_id
96 from amw_proc_hierarchy_denorm
97 where process_id = l_process_id and
98 up_down_ind = 'D';
99
100 type descendants_tbl is table of c_descendants%rowtype;
101 type relations_tbl is table of c_all_latest_relations%rowtype;
102
103
104 -- CURSOR TO FIND WHETHER THE A GIVEN RELATION EXISTS OR NOT..
105 cursor c_relation_exists (l_ancestor_id number,
106 l_descendant_id number) is
107 select 1 from amw_proc_hierarchy_denorm
108 where process_id = l_ancestor_id and
109 parent_child_id = l_descendant_id and
110 up_down_ind = 'D';
111
112 l_dummy number;
113 x_atbl ancestor_tbl;
114 x_dtbl descendants_tbl;
115 x_rtbl relations_tbl;
116 add_flag boolean;
117 add_flag1 boolean;
118 add_flag2 boolean;
119 add_flag3 boolean;
120
121
122
123 begin
124 x_index := 0;
125 x_process_tbl := process_tbl();
126 x_parent_child_tbl := parent_child_tbl();
127 x_up_down_ind_tbl := up_down_ind_tbl();
128 x_index_tbl.delete;
129
130
131 -- COLLECT ALL THE ANCESTORS OF THE PARENT INTO A TABLE..
132 open c_ancestors(p_parent_id);
133 fetch c_ancestors bulk collect into x_atbl;
134 close c_ancestors;
135
136 -- COLLECT ALL THE DESCENDENTS OF THE CHILD INTO A TABLE.
137 open c_descendants(p_child_id);
138 fetch c_descendants bulk collect into x_dtbl;
139 close c_descendants;
140
141
142 -- COLLECT ALL THE RELATIONS THAT EXISTS IN THE TABLE..
143 open c_all_latest_relations;
144 fetch c_all_latest_relations bulk collect into x_rtbl;
145 close c_all_latest_relations;
146
147
148 -- Store all the relations into an associative array..to transfer into the table later..
149
150 -- GET ALL THE RELATIONS THAT ALREADY IN THE HIERARCHY DENORM TABLE IN TO THE 'X_INDEX_TBL'.
151 --input all the relations into the associative array
152 if(x_rtbl.exists(1))
153 then
154 for ctr in x_rtbl.first .. x_rtbl.last loop
155 if(x_rtbl(ctr).up_down_ind = 'D') then
156 x_index_tbl(encode(x_rtbl(ctr).process_id,x_rtbl(ctr).parent_child_id)) := 1;
157 end if;
158 end loop;
159 end if;
160
161
162
163 -- CHECK IF THE PARENT-CHILD RELATION EXISTS ALREAY IN THE DENORM TABLE.
164 add_flag := true;
165 if(x_index_tbl.exists(encode(p_parent_id,p_child_id)))
166 then
167 add_flag := false;
168 end if;
169
170 -- IF NOT EXISTS ADD IT TO THE DENORM..(I.E. ADD IT TO INTURN ADD X_INDEX_TBL)
171 if (add_flag) then
172 add_rows_to_denorm(p_parent_id, p_child_id);
173
174 -- kosriniv YOU NEED TO DO IT FOR ALL THE ANCESTORS ALSO..
175 if(x_atbl.exists(1)) then
176 for ctr1 in x_atbl.first .. x_atbl.last loop
177 add_flag3 := true;
178 if(x_index_tbl.exists(encode(x_atbl(ctr1).parent_child_id,
179 p_child_id)))
180 then
181 add_flag3 := false;
182 end if;
183 if (add_flag3) then
184 add_rows_to_denorm(x_atbl(ctr1).parent_child_id,
185 p_child_id);
186 end if;
187 end loop;
188 -- ADDED ALL THE ANSCENDENT-DESCENDENTS LINKS..
189 end if;
190 -- IF ANY DESCENTS EXISTS FOR THE CHILD, YOU NEED TO ADD IT TO THEM TO THE HIERARHY DENORM ALSO..
191 if(x_dtbl.exists(1)) then
192 -- DO FOR ALL THE DESCNENDENTS..WITH THE PARENT
193 for ctr in x_dtbl.first .. x_dtbl.last loop
194 add_flag1 := true;
195 if(x_index_tbl.exists(encode(p_parent_id,x_dtbl(ctr).parent_child_id)))
196 then
197 add_flag1 := false;
198 end if;
199
200 if(add_flag1)
201 then
202 add_rows_to_denorm(p_parent_id,x_dtbl(ctr).parent_child_id);
203 -- IF YOU HAVE ANY ASSCENDENTS TO THE PARENT, YOU NEED TO ADD THE DESCENEDENTS TO THEM ALSO IN TO THE HIERARHCY DENORM...
204 if(x_atbl.exists(1)) then
205 for ctr1 in x_atbl.first .. x_atbl.last loop
206 add_flag2 := true;
207 if(x_index_tbl.exists(encode(x_atbl(ctr1).parent_child_id,
208 x_dtbl(ctr).parent_child_id)))
209 then
210 add_flag2 := false;
211 end if;
212 if (add_flag2) then
213 add_rows_to_denorm(x_atbl(ctr1).parent_child_id,
214 x_dtbl(ctr).parent_child_id);
215 end if;
216 end loop;
217 -- ADDED ALL THE ANSCENDENT-DESCENDENTS LINKS..
218 end if;
219 end if;
220 end loop;
221 end if;
222 end if;
223
224 --now insert all the rows found into the amw_proc_hierarchy_denorm table
225 if(x_process_tbl.exists(1))
226
227 then
228 forall i in x_process_tbl.first .. x_process_tbl.last
229 insert into amw_proc_hierarchy_denorm (PROCESS_ID,
230 PARENT_CHILD_ID,
231 UP_DOWN_IND,
232 LAST_UPDATE_DATE,
233 LAST_UPDATED_BY,
234 CREATION_DATE,
235 CREATED_BY,
236 LAST_UPDATE_LOGIN,
237 OBJECT_VERSION_NUMBER,
238 HIERARCHY_TYPE)
239 values
240 (x_process_tbl(i),
241 x_parent_child_tbl(i),
242 x_up_down_ind_tbl(i),
243 l_sysdate,
244 G_USER_ID,
245 l_sysdate,
246 G_USER_ID,
247 G_LOGIN_ID,
248 1,
249 'L');
250 end if;
251
252
253 exception
254 WHEN OTHERS THEN raise;
255
256
257 end update_denorm_add_child;
258
259
260
261
262
263
264 --assumes that x_index_tbl has been initialized before first call
265 procedure recursive_construct_denorm(p_process_id in number)
266
267 is
268
269 i pls_integer;
270
271
272 add_flag boolean;
273 add_flag1 boolean;
274
275 temp tn;
276 temp1 tn;
277
278 str varchar2(50);
279 begin
280
281 /* first get the children of p_process_id
282 * get it from the x_t1 table which stores these children
283 */
284
285 str := to_char(p_process_id);
286
287 if(x_t1.exists(str))
288 then
289 temp := x_t1(str);
290 else
291 temp := tn();
292 end if;
293
294 /* check to avoid numeric error */
295 if(temp.exists(1)) then
296
297 --for each child (of the process on which the procedure is called)
298 for i in temp.first .. temp.last loop
299
300 --first construct the denorm hierarchy for that child
301 recursive_construct_denorm( p_process_id => temp(i));
302
303
304 /* if the relationship between the process and the child does
305 * not already exist in the denorm table
306 * then add the two rows creating the relations
307 */
308 add_flag := true;
309
310 /* the x_index_tbl just check if we ever added an entry for
311 * <p_process_id>:<temp(i)>. If so it would never go process
312 * down further because all relations of process_id with the
313 * descendants of temp(i) would already have been added
314 */
315 if (x_index_tbl.exists(encode(p_process_id,temp(i))))
316 then
317 add_flag := false;
318 end if;
319
320
321
322 /* if the relation between p_process_id and temp(i)
323 * already existed in the relations table we dont need to even look into
324 * descendants of child_id .. else we look into them
325 * thus only if add_flag = true do any processing.
326 */
327 if (add_flag)
328 then
329 add_rows_to_denorm(p_process_id, temp(i));
330 --record this new descendant for p_process_id in the descendants array
331 x_t2(str).extend;
332 x_t2(str)(x_t2(str).last) := temp(i);
333
334 /* check if the child just processed has a single descendant
335 * if it doesnt have a single descendant then there is no need
336 * to process further in this recursive call
337 */
338 if(x_t2.exists(to_char(temp(i))))
339 then
340 --now temp1 points to the descendants of temp(i)
341 temp1 := x_t2(to_char(temp(i)));
342 else
343 temp1 := tn();
344 end if;
345
346
347
348
349 --check if necessary to look at descendants of child
350 if(temp1.exists(1))
351 then
352 --for each descendant
353 for ctr in temp1.first .. temp1.last loop
354 add_flag1 := true;
355
356 --is the p_process_id, temp1(ctr) relation already done?
357 if (x_index_tbl.exists(encode(p_process_id,temp1(ctr))))
358 then
359 add_flag1 := false;
360 end if;
361
362 --if fine to add the link between p_process_id and temp1(ctr)
363 if(add_flag1)
364
365 then
366 add_rows_to_denorm(p_process_id, temp1(ctr));
367
368 --record temp1(ctr) as a descendant of p_process_id
369 x_t2(str).extend;
370 x_t2(str)(x_t2(str).last) := temp1(ctr);
371 end if;
372 end loop;
373 end if;
374 end if;
375 end loop;
376 end if;
377
378
379
380 end recursive_construct_denorm;
381
382 /* this procedure assumes that the amw_approved_hierarchy
383 * table stores current data .. the entire denorm table is
384 * refreshed for the approved hierarchy
385 * for RL pass -1 as the p_org_id
386 */
387 procedure update_approved_denorm(p_org_id in number,
388 l_sysdate in Date default sysdate)
389 is
390
391 cursor c_all_approved_links_rl is
392 select parent_id, child_id
393 from amw_approved_hierarchies
394 where (organization_id is null or organization_id = -1)
395 and (end_date is null or end_date > l_sysdate);
396
397 cursor c_all_approved_links_org(l_org_id in number) is
398 select parent_id, child_id
399 from amw_approved_hierarchies
400 where (organization_id = l_org_id) and
401 (end_date is null or end_date > l_sysdate);
402
403 str varchar2(50);
404
405 begin
406 init;
407
408 if(p_org_id is null or p_org_id = -1)
409 then
410 open c_all_approved_links_rl;
411 fetch c_all_approved_links_rl bulk collect into p_links_tbl;
412 close c_all_approved_links_rl;
413 else
414 --ko removing the denorm usage..
415 return;
416 /*
417 open c_all_approved_links_org(p_org_id);
418 fetch c_all_approved_links_org bulk collect into p_links_tbl;
419 close c_all_approved_links_org;
420 */
421 end if;
422
423 -- Now if the p_links_tbl contains any data then
424 if p_links_tbl.exists(1) then
425 /* initialize the tables
426 * this is needed only for parents
427 * i.e only parents can have children and other descendants
428 */
429 for ctr in p_links_tbl.first .. p_links_tbl.last loop
430 str := to_char(p_links_tbl(ctr).parent_id);
431 x_t1(str) := tn();
432 x_t2(str) := tn();
433 end loop;
434
435 --put in all the links
436 for ctr in p_links_tbl.first .. p_links_tbl.last loop
437 str := to_char(p_links_tbl(ctr).parent_id);
438 x_t1(str).extend;
439 x_t1(str)(x_t1(str).last) := p_links_tbl(ctr).child_id;
440 end loop;
441
442
443 if(p_org_id is null or p_org_id = -1)
444 then
445 --make the pl/sql tables contain the up2date data
446 recursive_construct_denorm(p_process_id => -1);
447 --DML operations
448 delete from amw_proc_hierarchy_denorm where hierarchy_type='A';
449
450 else
451 recursive_construct_denorm(p_process_id => -2);
452
453 delete from amw_org_hierarchy_denorm
454 where organization_id = p_org_id and
455 hierarchy_type='A';
456 end if;
457
458
459 if(x_process_tbl.exists(1))
460
461 then
462 if(p_org_id is null or p_org_id = -1)
463
464 then
465 forall i in x_process_tbl.first .. x_process_tbl.last
466 insert into amw_proc_hierarchy_denorm (PROCESS_ID,
467 PARENT_CHILD_ID,
468 UP_DOWN_IND,
469 LAST_UPDATE_DATE,
470 LAST_UPDATED_BY,
471 CREATION_DATE,
472 CREATED_BY,
473 LAST_UPDATE_LOGIN,
474 OBJECT_VERSION_NUMBER,
475 HIERARCHY_TYPE)
476 values
477 (x_process_tbl(i),
478 x_parent_child_tbl(i),
479 x_up_down_ind_tbl(i),
480 l_sysdate,
481 G_USER_ID,
482 l_sysdate,
483 G_USER_ID,
484 G_LOGIN_ID,
485 1,
486 'A');
487 else --p_org_id is not null
488 forall i in x_process_tbl.first .. x_process_tbl.last
489 insert into amw_org_hierarchy_denorm (ORGANIZATION_ID,
490 PROCESS_ID,
491 PARENT_CHILD_ID,
492 UP_DOWN_IND,
493 LAST_UPDATE_DATE,
494 LAST_UPDATED_BY,
495 CREATION_DATE,
496 CREATED_BY,
497 LAST_UPDATE_LOGIN,
498 OBJECT_VERSION_NUMBER,
499 HIERARCHY_TYPE)
500 values
501 (p_org_id,
502 x_process_tbl(i),
503 x_parent_child_tbl(i),
504 x_up_down_ind_tbl(i),
505 l_sysdate,
506 G_USER_ID,
507 l_sysdate,
508 G_USER_ID,
509 G_LOGIN_ID,
510 1,
511 'A');
512 end if;
513 end if;
514 end if;
515 exception
516 WHEN OTHERS then
517 raise;
518
519
520 end update_approved_denorm;
521
522 procedure update_denorm(p_org_id in number,
523 l_sysdate in Date default sysdate)
524
525 is
526 --l_sysdate DATE := sysdate;
527 cursor c_top_level_rl is
528 (select distinct parent_id
529 from amw_latest_hierarchies
530 where (organization_id is null or organization_id = -1)
531 )
532 minus
533 (select distinct child_id parent_id
534 from amw_latest_hierarchies
535 where (organization_id is null or organization_id = -1)
536 );
537
538 cursor c_top_level_org(l_org_id in number) is
539 (select distinct parent_id
540 from amw_latest_hierarchies
541 where (organization_id = l_org_id)
542 )
543 minus
544 (select distinct child_id parent_id
545 from amw_latest_hierarchies
546 where (organization_id = l_org_id)
547 );
548
549
550 type ttbl is table of c_top_level_rl%rowtype;
551
552 p_tbl ttbl;
553 p_ltbl links_tbl;
554 l_batch_size pls_integer := 1000;
555 i pls_integer;
556 str varchar2(50);
557
558
559 begin
560
561
562
563
564 --initialize all global tables
565 init;
566
567 if(p_org_id is null or p_org_id = -1)
568 then
569 open c_top_level_rl;
570 fetch c_top_level_rl bulk collect into p_tbl;
571 close c_top_level_rl;
572 else
573 --ko removing the denorm usage..
574 return;
575 /*
576 open c_top_level_org(p_org_id);
577 fetch c_top_level_org bulk collect into p_tbl;
578 close c_top_level_org;
579 */
580 end if;
581
582
583 if(p_org_id is null or p_org_id = -1)
584 then
585 open c_all_latest_links_rl;
586 fetch c_all_latest_links_rl bulk collect into p_links_tbl;
587 close c_all_latest_links_rl;
588 else
589 --ko removing the denorm usage..
590 return;
591 /*
592 open c_all_latest_links_org(p_org_id);
593 fetch c_all_latest_links_org bulk collect into p_links_tbl;
594 close c_all_latest_links_org;
595 */
596 end if;
597
598
599
600 -- kosriniv check for p_links_tbl existance..
601 if p_links_tbl.exists(1) then
602
603
604 /* initialize the tables
605 * this is needed only for parents
606 * i.e only parents can have children and other descendants
607 */
608 for ctr in p_links_tbl.first .. p_links_tbl.last loop
609 str := to_char(p_links_tbl(ctr).parent_id);
610 x_t1(str) := tn();
611 x_t2(str) := tn();
612 end loop;
613
614 --put in all the links
615 for ctr in p_links_tbl.first .. p_links_tbl.last loop
616 str := to_char(p_links_tbl(ctr).parent_id);
617 x_t1(str).extend;
618 x_t1(str)(x_t1(str).last) := p_links_tbl(ctr).child_id;
619 end loop;
620
621 end if;
622 if(p_tbl.exists(1))
623
624 then
625
626 --call the updating procedure for each top level process
627 for i in p_tbl.first .. p_tbl.last loop
628 recursive_construct_denorm(p_process_id => p_tbl(i).parent_id);
629 end loop;
630 end if;
631
632
633 --delete all rows from the denorm table
634
635 if(p_org_id is null or p_org_id = -1)
636 then
637 delete from amw_proc_hierarchy_denorm where hierarchy_type='L';
638 else
639 delete from amw_org_hierarchy_denorm
640 where organization_id = p_org_id and
641 hierarchy_type='L';
642 end if;
643
644
645 if(x_process_tbl.exists(1))
646
647 then
648 if(p_org_id is null or p_org_id = -1)
649
650 then
651 forall i in x_process_tbl.first .. x_process_tbl.last
652 insert into amw_proc_hierarchy_denorm (PROCESS_ID,
653 PARENT_CHILD_ID,
654 UP_DOWN_IND,
655 LAST_UPDATE_DATE,
656 LAST_UPDATED_BY,
657 CREATION_DATE,
658 CREATED_BY,
659 LAST_UPDATE_LOGIN,
660 OBJECT_VERSION_NUMBER,
661 HIERARCHY_TYPE)
662 values
663 (x_process_tbl(i),
664 x_parent_child_tbl(i),
665 x_up_down_ind_tbl(i),
666 l_sysdate,
667 G_USER_ID,
668 l_sysdate,
669 G_USER_ID,
670 G_LOGIN_ID,
671 1,
672 'L');
673 else --p_org_id is not null
674 forall i in x_process_tbl.first .. x_process_tbl.last
675 insert into amw_org_hierarchy_denorm (ORGANIZATION_ID,
676 PROCESS_ID,
677 PARENT_CHILD_ID,
678 UP_DOWN_IND,
679 LAST_UPDATE_DATE,
680 LAST_UPDATED_BY,
681 CREATION_DATE,
682 CREATED_BY,
683 LAST_UPDATE_LOGIN,
684 OBJECT_VERSION_NUMBER,
685 HIERARCHY_TYPE)
686 values
687 (p_org_id,
688 x_process_tbl(i),
689 x_parent_child_tbl(i),
690 x_up_down_ind_tbl(i),
691 l_sysdate,
692 G_USER_ID,
693 l_sysdate,
694 G_USER_ID,
695 G_LOGIN_ID,
696 1,
697 'L');
698 end if;
699 end if;
700
701 exception
702 WHEN OTHERS then
703 raise;
704
705 end update_denorm;
706
707
708 procedure revise_process_if_necessary
709 (p_process_id in number,
710 l_sysdate in Date default sysdate)
711 is
712
713 l_process_id amw_process.process_id%type;
714 l_process_rev_id amw_process.process_rev_id%type;
715 l_item_type amw_process.item_type%type;
716 l_name amw_process.name%type;
717 l_process_code amw_process.process_code%type;
718 l_revision_number amw_process.revision_number%type;
719 l_approval_status amw_process.approval_status%type;
720 l_control_count amw_process.control_count%type;
721 l_risk_count amw_process.risk_count%type;
722 l_org_count amw_process.org_count%type;
723 l_significant_process_flag amw_process.significant_process_flag%type;
724 l_standard_process_flag amw_process.standard_process_flag%type;
725 l_certification_status amw_process.certification_status%type;
726 l_process_category amw_process.process_category%type;
727 l_process_owner_id amw_process.process_owner_id%type;
728 l_finance_owner_id amw_process.finance_owner_id%type;
729 l_application_owner_id amw_process.application_owner_id%type;
730 l_standard_variation amw_process.standard_variation%type;
731 l_deletion_date amw_process.deletion_date%type;
732 l_process_type amw_process.process_type%type;
733 l_control_activity_type amw_process.control_activity_type%type;
734 l_object_version_number amw_process.object_version_number%type;
735 l_attribute_category amw_process.attribute_category%type;
736 l_attribute1 amw_process.attribute1%type;
737 l_attribute2 amw_process.attribute2%type;
738 l_attribute3 amw_process.attribute3%type;
739 l_attribute4 amw_process.attribute4%type;
740 l_attribute5 amw_process.attribute5%type;
741 l_attribute6 amw_process.attribute6%type;
742 l_attribute7 amw_process.attribute7%type;
743 l_attribute8 amw_process.attribute8%type;
744 l_attribute9 amw_process.attribute9%type;
745 l_attribute10 amw_process.attribute10%type;
746 l_attribute11 amw_process.attribute11%type;
747 l_attribute12 amw_process.attribute12%type;
748 l_attribute13 amw_process.attribute13%type;
749 l_attribute14 amw_process.attribute14%type;
750 l_attribute15 amw_process.attribute15%type;
751
752 l_created_from amw_process.created_from%type;
753 l_program_update_date amw_process.program_update_date%type;
754 l_program_id amw_process.program_id%type;
755 l_program_application_id amw_process.program_application_id%type;
756 l_request_id amw_process.request_id%type;
757 l_risk_count_latest amw_process.risk_count_latest%type;
758 l_control_count_latest amw_process.control_count_latest%type;
759
760 l_new_process_rev_id number;
761
762 l_display_name amw_process_names_tl.display_name%type;
763 l_description amw_process_names_tl.description%type;
764 l_language amw_process_names_tl.language%type;
765 l_source_lang amw_process_names_tl.source_lang%type;
766
767 l_classification amw_process.classification%type;
768
769 -- l_sysdate DATE := sysdate;
770
771 l_dummy number;
772 begin
773 if(p_process_id <> -1)
774 then
775
776 select process_id, process_rev_id, item_type, name, process_code,
777 revision_number, approval_status, control_count,
778 risk_count, org_count, significant_process_flag,
779 standard_process_flag, certification_status,
780 process_category, process_owner_id, finance_owner_id,
781 application_owner_id, standard_variation,
782 object_version_number, deletion_date,
783 process_type, control_activity_type,
784 attribute_category, attribute1,
785 attribute2, attribute3, attribute4,
786 attribute5, attribute6, attribute7,
787 attribute8, attribute9, attribute10,
788 attribute11, attribute12, attribute13,
789 attribute14, attribute15, created_from,
790 program_id, program_application_id,
791 request_id, program_update_date, risk_count_latest,
792 control_count_latest, classification
793
794 into l_process_id, l_process_rev_id, l_item_type, l_name, l_process_code,
795 l_revision_number, l_approval_status, l_control_count,
796 l_risk_count, l_org_count, l_significant_process_flag,
797 l_standard_process_flag, l_certification_status,
798 l_process_category, l_process_owner_id, l_finance_owner_id,
799 l_application_owner_id, l_standard_variation,
800 l_object_version_number, l_deletion_date,
801 l_process_type, l_control_activity_type,
802 l_attribute_category, l_attribute1,
803 l_attribute2, l_attribute3, l_attribute4,
804 l_attribute5, l_attribute6, l_attribute7,
805 l_attribute8, l_attribute9, l_attribute10,
806 l_attribute11, l_attribute12, l_attribute13,
807 l_attribute14, l_attribute15, l_created_from,
808 l_program_id, l_program_application_id,
809 l_request_id, l_program_update_date,
810 l_risk_count_latest, l_control_count_latest,
811 l_classification
812
813
814 from amw_process
815
816 where process_id = p_process_id
817 and (end_date is null or end_date > l_sysdate);
818
819 /* any too_many_rows exception will propogate to caller */
820 if(sql%notfound)
821 then
822 raise FND_API.G_EXC_UNEXPECTED_ERROR;
823 end if;
824
825
826
827 if (l_approval_status='A')
828
829 --revise the parent process
830 then
831
832 insert into amw_process (PROCESS_ID,
833 ITEM_TYPE,
834 NAME,
835 PROCESS_CODE,
836 REVISION_NUMBER,
837 PROCESS_REV_ID,
838 APPROVAL_STATUS,
839 START_DATE,
840 CONTROL_COUNT,
841 RISK_COUNT,
842 ORG_COUNT,
843 SIGNIFICANT_PROCESS_FLAG,
844 STANDARD_PROCESS_FLAG,
845 CERTIFICATION_STATUS,
846 PROCESS_CATEGORY,
847 PROCESS_OWNER_ID,
848 FINANCE_OWNER_ID,
849 APPLICATION_OWNER_ID,
850 STANDARD_VARIATION,
851 LAST_UPDATE_DATE,
852 LAST_UPDATED_BY,
853 CREATION_DATE,
854 CREATED_BY,
855 LAST_UPDATE_LOGIN,
856 OBJECT_VERSION_NUMBER,
857 DELETION_DATE,
858 PROCESS_TYPE,
859 CONTROL_ACTIVITY_TYPE,
860 ATTRIBUTE_CATEGORY,
861 ATTRIBUTE1,
862 ATTRIBUTE2,
863 ATTRIBUTE3,
864 ATTRIBUTE4,
865 ATTRIBUTE5,
866 ATTRIBUTE6,
867 ATTRIBUTE7,
868 ATTRIBUTE8,
869 ATTRIBUTE9,
870 ATTRIBUTE10,
871 ATTRIBUTE11,
872 ATTRIBUTE12,
873 ATTRIBUTE13,
874 ATTRIBUTE14,
875 ATTRIBUTE15,
876 CREATED_FROM,
877 PROGRAM_ID,
878 PROGRAM_APPLICATION_ID,
879 REQUEST_ID,
880 PROGRAM_UPDATE_DATE,
881 RISK_COUNT_LATEST,
882 CONTROL_COUNT_LATEST,
883 CLASSIFICATION)
884
885 VALUES
886 (l_process_id,
887 l_item_type,
888 l_name,
889 l_process_code,
890 l_revision_number + 1,
891 AMW_PROCESS_S.nextval,
892 'D',
893 l_sysdate,
894 l_control_count,
895 l_risk_count,
896 l_org_count,
897 l_significant_process_flag,
898 l_standard_process_flag,
899 l_certification_status,
900 l_process_category,
901 l_process_owner_id,
902 l_finance_owner_id,
903 l_application_owner_id,
904 l_standard_variation,
905 l_sysdate,
906 G_USER_ID,
907 l_sysdate,
908 G_USER_ID,
909 G_LOGIN_ID,
910 1,
911 l_deletion_date,
912 l_process_type,
913 l_control_activity_type,
914 l_attribute_category,
915 l_attribute1,
916 l_attribute2,
917 l_attribute3,
918 l_attribute4,
919 l_attribute5,
920 l_attribute6,
921 l_attribute7,
922 l_attribute8,
923 l_attribute9,
924 l_attribute10,
925 l_attribute11,
926 l_attribute12,
927 l_attribute13,
928 l_attribute14,
929 l_attribute15,
930 l_created_from,
931 l_program_id,
932 l_program_application_id,
933 l_request_id,
934 l_program_update_date,
935 l_risk_count_latest,
936 l_control_count_latest,
937 l_classification)
938 RETURNING
939 PROCESS_REV_ID
940 INTO
941 l_new_process_rev_id;
942
943
944
945
946
947 update amw_process set
948 last_update_date = l_sysdate,
949 last_updated_by = G_USER_ID,
950 last_update_login = G_LOGIN_ID,
951 end_date = l_sysdate,
952 object_version_number = l_object_version_number + 1
953
954
955
956 where
957 process_id = p_process_id and
958 revision_number = l_revision_number and
959 object_version_number = l_object_version_number;
960
961
962 /* The only reason why the above insert could fail is:
963 * The object version number had already been incremented
964 * By some other process and thus the where clause failed to
965 * update any row
966 */
967 if(sql%notfound)
968 then
969 raise FND_API.G_EXC_ERROR;
970 end if;
971
972
973 --now update the translatable table
974 select display_name, description, language, source_lang
975
976 into l_display_name, l_description, l_language, l_source_lang
977
978 from amw_process_names_tl
979
980 where process_id = l_process_id and
981 revision_number = l_revision_number and
982 language = userenv('LANG');
983
984 /* too_many_rows will be propagated */
985 if(sql%notfound)
986 then
987 raise FND_API.G_EXC_UNEXPECTED_ERROR;
988 end if;
989
990
991
992 insert into amw_process_names_tl
993 (
994 process_id
995 ,revision_number
996 ,process_rev_id
997 ,display_name
998 ,description
999 ,language
1000 ,source_lang
1001 ,creation_date
1002 ,created_by
1003 ,last_update_date
1004 ,last_updated_by
1005 ,last_update_login
1006 ,object_version_number
1007 )
1008 select
1009 l_process_id
1010 ,l_revision_number+1
1011 ,l_new_process_rev_id
1012 ,l_display_name
1013 ,l_description
1014 ,L.LANGUAGE_CODE
1015 ,USERENV('LANG')
1016 ,l_sysdate
1017 ,g_user_id
1018 ,l_sysdate
1019 ,g_user_id
1020 ,g_login_id
1021 ,1
1022 from FND_LANGUAGES L
1023 where L.INSTALLED_FLAG in ('I', 'B');
1024
1025 --write code to copy attachments of the previous rev_id to the new one
1026 fnd_attached_documents2_pkg.copy_attachments(
1027 X_from_entity_name => 'AMW_PROCESS',
1028 X_from_pk1_value => l_process_rev_id,
1029 X_to_entity_name => 'AMW_PROCESS',
1030 X_to_pk1_value => l_new_process_rev_id,
1031 X_created_by => g_user_id,
1032 X_last_update_login => g_login_id,
1033 X_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
1034 X_request_id => FND_GLOBAL.conc_request_id);
1035
1036
1037
1038
1039
1040
1041
1042 end if;
1043 end if;
1044
1045
1046
1047
1048 end revise_process_if_necessary;
1049
1050 /* this procedure updates the instance_id in the
1051 * amw_approved_hierarchy table if necessary
1052 * it should be called after a link is inserted in the
1053 * latest hierarchy (or an instance_id is updated)
1054 * the reason is that: the child order numbers of any
1055 * link in the latest and approved hierarchies MUST MATCH
1056 */
1057 procedure update_appr_ch_ord_num_if_reqd
1058 (p_org_id in number,
1059 p_parent_id in number,
1060 p_child_id in number,
1061 p_instance_id in number)
1062
1063 is
1064
1065 l_dummy pls_integer;
1066
1067 begin
1068
1069 select 1 into l_dummy from amw_approved_hierarchies
1070 where parent_id = p_parent_id
1071 and child_id = p_child_id
1072 and end_date is null
1073 and ((-1 = p_org_id and (organization_id is null or organization_id = -1)) OR
1074 (p_org_id <> -1 and organization_id = p_org_id));
1075
1076 update amw_approved_hierarchies set
1077 last_update_date = sysdate,
1078 last_updated_by = G_USER_ID,
1079 last_update_login = G_LOGIN_ID,
1080 child_order_number = p_instance_id,
1081 object_version_number = object_version_number + 1
1082 where parent_id = p_parent_id
1083 and child_id = p_child_id
1084 and end_date is null
1085 and ((-1 = p_org_id and (organization_id is null or organization_id = -1)) OR
1086 (p_org_id <> -1 and organization_id = p_org_id));
1087
1088 exception
1089 when too_many_rows then
1090 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1091 when others then null;
1092
1093
1094 end update_appr_ch_ord_num_if_reqd;
1095
1096 function get_approval_status(p_process_id in number)
1097 return varchar2
1098 is
1099
1100 l_approval_status amw_process.approval_status%type;
1101 begin
1102 l_approval_status := 'D';
1103
1104 select approval_status into l_approval_status
1105 from amw_process where process_id = p_process_id and
1106 end_date is null;
1107
1108 return l_approval_status;
1109
1110
1111 exception
1112
1113 when others then
1114 return l_approval_status;
1115
1116 end get_approval_status;
1117
1118 --The parent process and the child process both exist as ICM processes
1119 procedure add_existing_process_as_child(
1120
1121 p_parent_process_id in number,
1122 p_child_process_id in number,
1123 l_sysdate in Date default sysdate,
1124 x_return_status out nocopy varchar2,
1125 x_msg_count out nocopy number,
1126 x_msg_data out nocopy varchar2)
1127
1128 is
1129
1130
1131 l_api_name constant varchar2(30) := 'add_existing_process_as_child';
1132
1133 p_init_msg_list varchar2(10) := FND_API.G_FALSE;
1134
1135 l_dummy pls_integer;
1136
1137 l_approval_status amw_process.approval_status%type;
1138
1139 l_child_order_number amw_latest_hierarchies.child_order_number%type;
1140
1141 CURSOR c1 is
1142 (select parent_process_id,
1143 child_process_id,
1144 child_order_number from AMW_LATEST_HIERARCHY_RL_V
1145 start with parent_process_id = -1 and
1146 parent_approval_status = 'A'
1147 connect by prior child_process_id = parent_process_id and
1148 child_approval_status = 'A' )
1149 MINUS
1150
1151 (select parent_process_id,
1152 child_process_id,
1153 child_order_number
1154 from AMW_CURR_APP_HIERARCHY_RL_V);
1155
1156
1157 begin
1158 x_return_status := FND_API.G_RET_STS_SUCCESS;
1159 if FND_API.to_Boolean(p_init_msg_list) then
1160 FND_MSG_PUB.initialize;
1161 end if;
1162
1163 if FND_GLOBAL.user_id is null then
1164 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1165 raise FND_API.G_EXC_ERROR;
1166 end if;
1167
1168 --check if parent_process_id is null
1169 if p_parent_process_id is null then
1170 raise FND_API.G_EXC_ERROR;
1171 end if;
1172
1173 --check if child_process_id is null
1174 if p_child_process_id is null then
1175 raise FND_API.G_EXC_ERROR;
1176 end if;
1177
1178
1179
1180
1181 --check if parent process is locked
1182 if (is_locked_process(p_parent_process_id))
1183 then
1184 raise amw_process_locked_exception;
1185 end if;
1186
1187
1188 /* check if the child is an ancestor of parent already
1189 * so that a circular hierarchy cannot be created
1190 */
1191 if(is_ancestor_in_hierarchy(p_child_process_id,p_parent_process_id))
1192 then
1193 raise amw_circularity_exception;
1194 end if;
1195
1196
1197 --we now check if this link is already existing in the latest hierarchy
1198
1199 open c_link_exist(p_parent_process_id,p_child_process_id);
1200 fetch c_link_exist into l_dummy;
1201 if(c_link_exist%found)
1202 then
1203 close c_link_exist;
1204 return;
1205 end if;
1206 close c_link_exist;
1207
1208 /* else, all is fine, we can proceed with revising the parent process
1209 * and creating the parent, child link in the latest hierarchy
1210 */
1211 revise_process_if_necessary(p_process_id => p_parent_process_id,
1212 l_sysdate => l_sysdate);
1213
1214
1215
1216
1217
1218 --update the latest hierarchy table
1219 insert into amw_latest_hierarchies(ORGANIZATION_ID,
1220 PARENT_ID,
1221 CHILD_ID,
1222 CHILD_ORDER_NUMBER,
1223 LAST_UPDATE_DATE,
1224 LAST_UPDATED_BY,
1225 LAST_UPDATE_LOGIN,
1226 CREATION_DATE,
1227 CREATED_BY,
1228 OBJECT_VERSION_NUMBER
1229 )
1230 VALUES (-1,
1231 p_parent_process_id,
1232 p_child_process_id,
1233 AMW_CHILD_ORDER_S.nextval,
1234 l_sysdate,
1235 g_user_id,
1236 g_login_id,
1237 l_sysdate,
1238 g_user_id,
1239 1)
1240 returning CHILD_ORDER_NUMBER
1241 into l_child_order_number;
1242
1243
1244 update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id, p_child_process_id,
1245 l_child_order_number);
1246
1247
1248 /* update the denorm table
1249 * can throw an amw_processing_exception, so it has been handled.
1250 */
1251 update_denorm_add_child(p_parent_id => p_parent_process_id,
1252 p_child_id => p_child_process_id,
1253 l_sysdate => l_sysdate);
1254
1255 /* if the process was approved to begin with
1256 * and its parent was -1
1257 * then the approved hierarchy needs to be modified
1258 */
1259 l_approval_status := get_approval_status(p_child_process_id);
1260
1261 if(l_approval_status = 'A' and p_parent_process_id = -1)
1262
1263 then
1264 for a_link in c1 loop
1265 insert into amw_approved_hierarchies
1266 (organization_id,
1267 parent_id,
1268 child_id,
1269 start_date,
1270 child_order_number,
1271 LAST_UPDATE_DATE,
1272 LAST_UPDATED_BY,
1273 LAST_UPDATE_LOGIN,
1274 CREATION_DATE,
1275 CREATED_BY,
1276 OBJECT_VERSION_NUMBER)
1277 values
1278 (-1,
1279 a_link.parent_process_id,
1280 a_link.child_process_id,
1281 l_sysdate,
1282 a_link.child_order_number,
1283 l_sysdate,
1284 g_user_id,
1285 g_login_id,
1286 l_sysdate,
1287 g_user_id,
1288 1);
1289 end loop;
1290
1291 /* now update the denorm table */
1292 update_approved_denorm(-1,l_sysdate);
1293
1294 end if;
1295
1296
1297
1298 --Call the APIs to adjust the risk and control counts
1299 update_latest_control_counts(p_parent_process_id);
1300 update_latest_risk_counts(p_parent_process_id);
1301
1302
1303
1304
1305
1306 exception
1307 when FND_API.G_EXC_ERROR then
1308 ROLLBACK;
1309 x_return_status := FND_API.G_RET_STS_ERROR;
1310 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1311 p_count => x_msg_count,
1312 p_data => x_msg_data);
1313
1314
1315 when FND_API.G_EXC_UNEXPECTED_ERROR then
1316 ROLLBACK;
1317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1318 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1319 p_count => x_msg_count,
1320 p_data => x_msg_data);
1321
1322 when amw_process_locked_exception then
1323 x_return_status := FND_API.G_RET_STS_ERROR;
1324 x_msg_count := x_msg_count + 1;
1325 fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
1326 x_msg_data := fnd_message.get;
1327 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1328 p_procedure_name => 'add_existing_process_as_child',
1329 p_error_text => x_msg_data);
1330 raise FND_API.G_EXC_ERROR;
1331
1332
1333
1334
1335
1336 when amw_circularity_exception then
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 fnd_message.set_name('AMW','AMW_CIRCULARITY_CREATION');
1339 x_msg_count := x_msg_count + 1;
1340 x_msg_data := fnd_message.get;
1341 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1342 p_procedure_name => 'add_existing_process_as_child',
1343 p_error_text => x_msg_count);
1344 raise FND_API.G_EXC_ERROR;
1345
1346
1347 when amw_processing_exception then
1348 ROLLBACK;
1349 x_return_status := FND_API.G_RET_STS_ERROR;
1350 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1351 p_count => x_msg_count,
1352 p_data => x_msg_data);
1353
1354
1355 when OTHERS then
1356 ROLLBACK;
1357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1358
1359 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1360 p_count => x_msg_count,
1361 p_data => x_msg_data);
1362
1363
1364
1365
1366
1367
1368
1369 end add_existing_process_as_child;
1370
1371
1372
1373
1374 procedure delete_child(
1375
1376 p_parent_process_id in number,
1377 p_child_process_id in number,
1378 l_sysdate in Date default sysdate,
1379 x_return_status out nocopy varchar2,
1380 x_msg_count out nocopy number,
1381 x_msg_data out nocopy varchar2)
1382
1383 is
1384
1385 l_api_name constant varchar2(30) := 'delete_child';
1386 p_init_msg_list varchar2(10) := FND_API.G_FALSE;
1387
1388 l_approval_status amw_process.approval_status%type;
1389
1390 l_dummy number;
1391
1392 CURSOR c3 is
1393 (select parent_process_id, child_process_id
1394 from AMW_CURR_APP_HIERARCHY_RL_V
1395 where parent_process_id is not null)
1396 MINUS
1397 (select parent_process_id, child_process_id
1398 from AMW_CURR_APP_HIERARCHY_RL_V
1399 start with parent_process_id = -1
1400 connect by prior child_process_id = parent_process_id);
1401 begin
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403 if FND_API.to_Boolean(p_init_msg_list) then
1404 FND_MSG_PUB.initialize;
1405 end if;
1406
1407 if FND_GLOBAL.user_id is null then
1408 raise FND_API.G_EXC_ERROR;
1409 end if;
1410
1411 --check if parent_process_id is null
1412 if p_parent_process_id is null then
1413 raise FND_API.G_EXC_ERROR;
1414 end if;
1415
1416
1417 --check if child_process_id is null
1418 if p_child_process_id is null then
1419 raise FND_API.G_EXC_ERROR;
1420 end if;
1421
1422 --check if parent process is locked
1423 if (is_locked_process(p_parent_process_id))
1424 then
1425 raise amw_process_locked_exception;
1426 end if;
1427
1428
1429
1430 /* check if the link exists: if it doesnt then this is an
1431 * unexpected error
1432 */
1433
1434 open c_link_exist(p_parent_process_id,p_child_process_id);
1435 fetch c_link_exist into l_dummy;
1436
1437 if(c_link_exist%notfound)
1438 then
1439 close c_link_exist;
1440 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1441 end if;
1442
1443 close c_link_exist;
1444
1445
1446 /* revise the parent */
1447 revise_process_if_necessary(p_process_id => p_parent_process_id,
1448 l_sysdate => l_sysdate);
1449
1450
1451
1452
1453
1454
1455 /* update the latest hierarchy table
1456 * first we update the denorm table
1457 */
1458 delete from amw_latest_hierarchies
1459
1460 where
1461 parent_id = p_parent_process_id and
1462 child_id = p_child_process_id and
1463 (organization_id is null or organization_id = -1);
1464
1465
1466 if (sql%notfound)
1467 then
1468 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1469 end if;
1470
1471
1472 update_denorm(p_org_id => -1,
1473 l_sysdate =>l_sysdate);
1474
1475
1476
1477 /* under some conditions the approved hierarchy
1478 * may need to be changed
1479 * the conditions is
1480 * The process is present under -1 in the approved hierarchy
1481 */
1482 begin
1483 select 1 into l_dummy from amw_approved_hierarchies
1484 where (organization_id is null or organization_id = -1)
1485 and parent_id = -1
1486 and child_id = p_child_process_id
1487 and end_date is null;
1488
1489 delete from amw_approved_hierarchies
1490 where parent_id = -1
1491 and child_id = p_child_process_id
1492 and (organization_id is null or organization_id = -1)
1493 and end_date is null;
1494
1495 /* run a cursor to remove defunct links */
1496 for defunct_link in c3 loop
1497 update amw_approved_hierarchies
1498 set end_date = l_sysdate,
1499 object_version_number = object_version_number + 1
1500 where (organization_id is null or organization_id = -1)
1501 and parent_id = defunct_link.parent_process_id
1502 and child_id = defunct_link.child_process_id
1503 and end_date is null;
1504 end loop;
1505
1506 /* finally update the denorm table */
1507 update_approved_denorm(-1,l_sysdate);
1508
1509
1510
1511 exception
1512 when no_data_found then null;
1513 when others then null;
1514
1515 end;
1516
1517 --Call the procedures to update the risk, control counts
1518 update_latest_control_counts(p_parent_process_id);
1519 update_latest_risk_counts(p_parent_process_id);
1520
1521
1522
1523
1524 exception
1525 when FND_API.G_EXC_ERROR then
1526 ROLLBACK;
1527 x_return_status := FND_API.G_RET_STS_ERROR;
1528 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1529 p_count => x_msg_count,
1530 p_data => x_msg_data);
1531
1532
1533 when FND_API.G_EXC_UNEXPECTED_ERROR then
1534 ROLLBACK;
1535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1536 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1537 p_count => x_msg_count,
1538 p_data => x_msg_data);
1539
1540 when amw_process_locked_exception then
1541 x_return_status := FND_API.G_RET_STS_ERROR;
1542 x_msg_count := x_msg_count + 1;
1543 fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
1544 x_msg_data := fnd_message.get;
1545 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1546 p_procedure_name => 'delete_child',
1547 p_error_text => x_msg_data);
1548 raise FND_API.G_EXC_ERROR;
1549
1550
1551 when amw_processing_exception then
1552 ROLLBACK;
1553 x_return_status := FND_API.G_RET_STS_ERROR;
1554 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1555 p_count => x_msg_count,
1556 p_data => x_msg_data);
1557
1558
1559 when OTHERS then
1560 ROLLBACK;
1561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1562 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1563 p_count => x_msg_count,
1564 p_data => x_msg_data);
1565
1566
1567
1568
1569
1570
1571
1572 end delete_child;
1573
1574
1575
1576
1577
1578
1579
1580 /* return TRUE is process is locked in RL: either because it is itself
1581 * submitted for approval or because some higher process is submitted
1582 * for approval and the approval profile option dictates locking of
1583 * all downward processes in the hierarchy.
1584 */
1585 function is_locked_process(p_process_id in number) return boolean is
1586
1587 cursor c_locks(l_process_id number) is
1588 select locked_process_id from amw_process_locks
1589 where (organization_id is null or organization_id=-1) and
1590 locked_process_id=l_process_id;
1591 l_dummy number;
1592 begin
1593 open c_locks(p_process_id);
1594 fetch c_locks into l_dummy;
1595 if(c_locks%notfound)
1596 then
1597 close c_locks;
1598 return false;
1599 end if;
1600
1601 close c_locks;
1602 return true;
1603
1604 end is_locked_process;
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622 /* this is called to get the process_id from the name and item
1623 * type. This function will RAISE AN EXCEPTION (not return null)
1624 * if it does not find such a process in amw_process table
1625 */
1626 function get_process_id_from_wf_params(p_name in varchar2,
1627 p_item_type in varchar2)
1628 return number is
1629
1630 l_process_id amw_process.process_id%type;
1631
1632 begin
1633
1634 select process_id into l_process_id from amw_process
1635 where name = p_name
1636 and item_type = p_item_type
1637 and end_date is null;
1638
1639
1640 return l_process_id;
1641 exception
1642 when others
1643 then raise FND_API.G_EXC_UNEXPECTED_ERROR;
1644
1645 end get_process_id_from_wf_params;
1646
1647 function does_wf_proc_exist_in_icm(p_name in varchar2,
1648 p_item_type in varchar2)
1649 return boolean is
1650
1651 l_dummy pls_integer;
1652
1653 begin
1654
1655 select 1 into l_dummy from amw_process
1656 where name = p_name
1657 and item_type = p_item_type
1658 and end_date is null;
1659
1660 return true;
1661
1662 exception
1663 when no_data_found then
1664 return false;
1665 when others then
1666 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1667
1668 end does_wf_proc_exist_in_icm;
1669
1670 function get_process_code return varchar2
1671
1672 is
1673
1674
1675 l_code varchar2(100);
1676 l_dummy pls_integer;
1677
1678 l_prefix amw_parameters.parameter_value%type;
1679
1680 begin
1681
1682
1683 l_prefix := AMW_UTILITY_PVT.get_parameter(p_org_id => -1,
1684 p_param_name => 'PROCESS_CODE_PREFIX');
1685
1686
1687 while (true) loop
1688 select l_prefix || to_char(AMW_PROCESS_CODE_S.nextval) into l_code from dual;
1689
1690 select 1 into l_dummy from amw_process
1691 where process_code = l_code
1692 and end_date is null;
1693
1694 end loop;
1695
1696 exception
1697 when no_data_found then
1698 return l_code;
1699 when others then
1700 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1701
1702
1703 end;
1704 /* create a new icm process and return the process_id */
1705 function create_new_icm_process(
1706 p_name in varchar2,
1707 p_item_type in varchar2,
1708 p_display_name in varchar2,
1709 p_description in varchar2)
1710
1711 return number
1712
1713 is
1714
1715 l_process_id amw_process.process_id%type;
1716 l_process_rev_id amw_process.process_rev_id%type;
1717 l_new_code amw_process.process_code%type;
1718 begin
1719 l_new_code := get_process_code;
1720
1721 insert into amw_process (PROCESS_ID,
1722 ITEM_TYPE,
1723 NAME,
1724 PROCESS_CODE,
1725 REVISION_NUMBER,
1726 PROCESS_REV_ID,
1727 APPROVAL_STATUS,
1728 START_DATE,
1729 CONTROL_COUNT,
1730 RISK_COUNT,
1731 ORG_COUNT,
1732 LAST_UPDATE_DATE,
1733 LAST_UPDATED_BY,
1734 CREATION_DATE,
1735 CREATED_BY,
1736 LAST_UPDATE_LOGIN,
1737 OBJECT_VERSION_NUMBER,
1738 PROCESS_TYPE,
1739 PROGRAM_ID,
1740 REQUEST_ID,
1741 PROGRAM_UPDATE_DATE,
1742 RISK_COUNT_LATEST,
1743 CONTROL_COUNT_LATEST,
1744 SIGNIFICANT_PROCESS_FLAG,
1745 STANDARD_PROCESS_FLAG,
1746 PROCESS_CATEGORY)
1747
1748 VALUES
1749 (AMW_PROCESS_S.nextval,
1750 p_item_type,
1751 p_name,
1752 l_new_code,
1753 1,
1754 AMW_PROCESS_S.nextval,
1755 'D',
1756 g_sysdate,
1757 0,
1758 0,
1759 0,
1760 g_sysdate,
1761 G_USER_ID,
1762 g_sysdate,
1763 G_USER_ID,
1764 G_LOGIN_ID,
1765 1,
1766 'P',
1767 FND_GLOBAL.CONC_PROGRAM_ID,
1768 FND_GLOBAL.CONC_REQUEST_ID,
1769 DECODE(FND_GLOBAL.CONC_PROGRAM_ID,null,null,g_sysdate),
1770 0,
1771 0,
1772 'Y',
1773 'Y',
1774 'R')
1775 RETURNING
1776 PROCESS_ID, PROCESS_REV_ID
1777 INTO
1778 l_process_id, l_process_rev_id;
1779
1780
1781 insert into amw_process_names_tl
1782 (
1783 process_id
1784 ,revision_number
1785 ,process_rev_id
1786 ,display_name
1787 ,description
1788 ,language
1789 ,source_lang
1790 ,creation_date
1791 ,created_by
1792 ,last_update_date
1793 ,last_updated_by
1794 ,last_update_login
1795 ,object_version_number
1796 )
1797 select
1798 l_process_id
1799 ,1
1800 ,l_process_rev_id
1801 ,p_display_name
1802 ,p_description
1803 ,L.LANGUAGE_CODE
1804 ,USERENV('LANG')
1805 ,g_sysdate
1806 ,g_user_id
1807 ,g_sysdate
1808 ,g_user_id
1809 ,g_login_id
1810 ,1
1811 from FND_LANGUAGES L
1812 where L.INSTALLED_FLAG in ('I', 'B');
1813
1814
1815 return l_process_id;
1816
1817 end create_new_icm_process;
1818
1819 function is_deleted_process(p_process_id in number)
1820 return boolean is
1821 l_dummy pls_integer;
1822 begin
1823 select 1 into l_dummy from amw_process
1824 where process_id = p_process_id
1825 and end_date is null
1826 and deletion_date is not null;
1827
1828 return true;
1829
1830 exception
1831 when no_data_found then
1832 return false;
1833 when others then
1834 raise;
1835
1836 end is_deleted_process;
1837
1838 /* @p_parent_process_id : the process_id of the parent process under which to add
1839 * @p_name : the name (from wf)
1840 * @p_item_type : the item_type(from wf)
1841 * @p_instance_id : the instance_id from WF
1842 * @p_overwrite_ex : 'Y' or 'N' telling whether or not to overwrite ICM defn.
1843 with WF definition
1844 */
1845
1846 procedure recursive_wf_import (p_parent_process_id in number,
1847 p_name in varchar2,
1848 p_item_type in varchar2,
1849 p_display_name in varchar2,
1850 p_description in varchar2,
1851 p_instance_id in number,
1852 p_overwrite_ex in varchar2)
1853
1854
1855 is
1856 cursor c_wf_children (l_name in varchar2, l_item_type in varchar2) is
1857 select activity_item_type, activity_name,
1858 activity_display_name, activity_description,
1859 instance_id
1860 from amw_wf_process_hierarchy_vl
1861 where process_name = l_name and
1862 process_item_type = l_item_type;
1863 type wf_children_tbl is table of c_wf_children%rowtype;
1864
1865 wfc_tbl wf_children_tbl;
1866
1867
1868 cursor c_wf_minus_icm(l_name in varchar2, l_item_type in varchar2) is
1869 (select activity_name, activity_item_type
1870 from amw_wf_process_hierarchy_vl
1871 where process_name = l_name
1872 and process_item_type = l_item_type
1873 )
1874 MINUS
1875 (select child_name activity_name, child_item_type activity_item_type
1876 from amw_latest_hierarchy_rl_v
1877 where parent_name = l_name
1878 and parent_item_type = l_item_type
1879 );
1880
1881 type wf_minus_icm_tbl is table of c_wf_minus_icm%rowtype;
1882 wf_diff_icm wf_minus_icm_tbl;
1883
1884 cursor c_icm_minus_wf(l_name in varchar2, l_item_type in varchar2) is
1885 (select child_name, child_item_type
1886 from amw_latest_hierarchy_rl_v
1887 where parent_name = l_name
1888 and parent_item_type = l_item_type
1889 )
1890 MINUS
1891 (select activity_name child_name, activity_item_type child_item_type
1892 from amw_wf_process_hierarchy_vl
1893 where process_name = l_name
1894 and process_item_type = l_item_type
1895 );
1896
1897 type icm_minus_wf_tbl is table of c_icm_minus_wf%rowtype;
1898 icm_diff_wf icm_minus_wf_tbl;
1899
1900
1901 CURSOR c1 is
1902 (select parent_process_id,
1903 child_process_id,
1904 child_order_number from AMW_LATEST_HIERARCHY_RL_V
1905 start with parent_process_id = -1 and
1906 parent_approval_status = 'A'
1907 connect by prior child_process_id = parent_process_id and
1908 child_approval_status = 'A' )
1909 MINUS
1910
1911 (select parent_process_id,
1912 child_process_id,
1913 child_order_number
1914 from AMW_CURR_APP_HIERARCHY_RL_V);
1915
1916
1917
1918
1919 indexing_str varchar2(40);
1920 -- whether am currently processing some recursive invocation on
1921 -- this name and item_type : then value is : 'VISITING'
1922 -- if for first time : value is 'ARRIVED'
1923 -- if already visited : value is 'VISITED'
1924
1925 visiting_status varchar2(8);
1926 l_process_id amw_process.process_id%type;
1927 l_dummy pls_integer;
1928 exist_in_icm boolean;
1929 cur_child_id amw_process.process_id%type;
1930 l_approval_status amw_process.approval_status%type;
1931 security_check varchar2(1);
1932
1933 begin
1934
1935 indexing_str := p_name || 'w' || p_item_type;
1936
1937 if(not visited_tbl.exists(indexing_str))
1938 then
1939 visiting_status := 'ARRIVED';
1940 elsif (visited_tbl(indexing_str) = 1)
1941 then
1942 visiting_status := 'VISITING';
1943 else
1944 visiting_status := 'VISITED';
1945 end if;
1946
1947
1948
1949 if(visiting_status = 'VISITING')
1950 then
1951 raise wf_cycle_present_exception;
1952 end if;
1953
1954 -- so visiting_status = 'ARRIVED' or 'VISITED'
1955 if(visiting_status = 'VISITED')
1956 then
1957 -- 1. Must add link in latest hierarchy unless present
1958 -- 2. Must add link in approved hierarchy if necessary
1959 -- 3. Must update instance_id of approved hierarchy if necessary
1960 -- 4. Must update the denorm table of approved hierarchy if a link was
1961 -- indeed added
1962
1963 -- FIRST: Get the process_id : it must be there since this node
1964 -- has already been visited.
1965 l_process_id := get_process_id_from_wf_params(p_name, p_item_type);
1966
1967 -- CHECK IF CIRCULARITY BEING CREATED ...
1968 -- Notice : circularity can be created only if
1969 -- this process and its parent were both in ICM before the
1970 -- recursive_wf_import began ... if circularity is through the
1971 -- wf hierarchy it will be detected as a cycle in wf ...
1972 -- hence the point is : we can rely on the non-updated
1973 -- denorm tables....
1974 if(is_ancestor_in_hierarchy(l_process_id, p_parent_process_id))
1975 then
1976 raise amw_circularity_exception;
1977 end if;
1978
1979
1980 -- If this link is not in latest hierachy we add it
1981 -- If it was present we do not do anything
1982 -- In particular we DO NOT UPDATE the child_order_number ..
1983 -- even though p_overwrite_ex may be 'Y'
1984 begin
1985 select 1 into l_dummy from amw_latest_hierarchies
1986 where parent_id = p_parent_process_id
1987 and child_id = l_process_id
1988 and (organization_id is null or organization_id = -1);
1989 exception
1990 when no_data_found
1991 then
1992 insert into amw_latest_hierarchies (ORGANIZATION_ID,
1993 PARENT_ID,
1994 CHILD_ID,
1995 CHILD_ORDER_NUMBER,
1996 LAST_UPDATE_DATE,
1997 LAST_UPDATED_BY,
1998 LAST_UPDATE_LOGIN,
1999 CREATION_DATE,
2000 CREATED_BY,
2001 OBJECT_VERSION_NUMBER
2002 )
2003 values (-1,
2004 p_parent_process_id,
2005 l_process_id,
2006 p_instance_id,
2007 g_sysdate,
2008 g_user_id,
2009 g_login_id,
2010 g_sysdate,
2011 g_user_id,
2012 1);
2013 update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id,
2014 l_process_id,
2015 p_instance_id);
2016
2017 when others
2018 then raise;
2019 end;
2020
2021 -- In this block we would have added a link to approved_hierarchy if necessary
2022 -- The only case when this can be necessary is the following
2023 -- 1. p_parent_process_id = -1 and
2024 -- 2. the process with l_process_id is approved ...
2025 -- 3. However this is not possible because : since this process has been
2026 -- completely visited the calling parent process cannot be -1
2027 -- -1 can call this recursive procedure on only 1 child ....
2028 -- thus if that child was already completed ... this procedure
2029 -- cannot be called with -1 as the parent_process_id .. hence we do
2030 -- not need to check for addition to the approved hierarchy
2031
2032 return;
2033
2034 end if;
2035
2036 /* so here assume : visiting_status = 'ARRIVED' */
2037 --first change the visiting status to reflect visiting
2038 visited_tbl(indexing_str) := 1;
2039
2040 --check if process exist in ICM
2041 exist_in_icm := does_wf_proc_exist_in_icm(p_name,p_item_type);
2042
2043
2044 if(exist_in_icm)
2045 then
2046 --just need to add the links in the latest hierarchy if so needed ...
2047 l_process_id := get_process_id_from_wf_params(p_name, p_item_type);
2048
2049 if(is_deleted_process(l_process_id))
2050 then
2051 raise amw_process_deleted_exception;
2052 end if;
2053
2054 if(is_ancestor_in_hierarchy(l_process_id, p_parent_process_id))
2055 then
2056 raise amw_circularity_exception;
2057 end if;
2058
2059 --security check : the user needs to have appropriate privilege on this
2060 --process to be able to add it under a process on which the user has the
2061 --privilege!
2062
2063 security_check := amw_security_utils_pvt.check_function(p_function => 'AMW_UPD_RL_PROC'
2064 ,p_object_name => 'AMW_PROCESS_APPR_ETTY'
2065 ,p_instance_pk1_value => to_char(l_process_id)
2066 ,p_instance_pk2_value => '*NULL*'
2067 ,p_instance_pk3_value => '*NULL*'
2068 ,p_instance_pk4_value => '*NULL*'
2069 ,p_instance_pk5_value => '*NULL*');
2070 --it can be 'T', 'F', 'U' (unsupported API version)
2071 if (security_check <> 'T') then
2072 raise amw_insfcnt_prvlg_exception;
2073 end if;
2074
2075
2076
2077
2078 begin
2079 select 1 into l_dummy from amw_latest_hierarchies
2080 where parent_id = p_parent_process_id
2081 and child_id = l_process_id
2082 and (organization_id is null or organization_id = -1);
2083 exception
2084 when no_data_found then
2085 insert into amw_latest_hierarchies (ORGANIZATION_ID,
2086 PARENT_ID,
2087 CHILD_ID,
2088 CHILD_ORDER_NUMBER,
2089 LAST_UPDATE_DATE,
2090 LAST_UPDATED_BY,
2091 LAST_UPDATE_LOGIN,
2092 CREATION_DATE,
2093 CREATED_BY,
2094 OBJECT_VERSION_NUMBER)
2095 values (-1,
2096 p_parent_process_id,
2097 l_process_id,
2098 p_instance_id,
2099 g_sysdate,
2100 g_user_id,
2101 g_login_id,
2102 g_sysdate,
2103 g_user_id,
2104 1);
2105 update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id,
2106 l_process_id,
2107 p_instance_id);
2108
2109 when others then
2110 raise;
2111 end;
2112
2113
2114
2115 if(p_overwrite_ex = 'N')
2116 then
2117 --dont have to follow the wf_hierarchy any further ...
2118
2119 --indicate that this node has been visited
2120 visited_tbl(indexing_str) := 2;
2121 return;
2122 else
2123
2124 --1. get process_id
2125 --2. Get icm_minus_wf and wf_minus_icm
2126 --3. if there is a difference ... then revise if necessary
2127 --4. update info abt links
2128 --5. for each check if appr_hierarchy needs to be updated
2129 --6. call recursive on each child
2130 --7. before returning indicate that you are done
2131
2132 open c_wf_minus_icm(p_name, p_item_type);
2133 open c_icm_minus_wf(p_name, p_item_type);
2134 fetch c_wf_minus_icm bulk collect into wf_diff_icm;
2135 fetch c_icm_minus_wf bulk collect into icm_diff_wf;
2136
2137
2138
2139 if(wf_diff_icm.exists(1) or icm_diff_wf.exists(1))
2140 then
2141 if(is_locked_process(l_process_id))
2142 then
2143 raise amw_process_locked_exception;
2144 end if;
2145 revise_process_if_necessary(p_process_id => l_process_id,
2146 l_sysdate => g_sysdate);
2147 end if;
2148
2149 --end_date all the links in icm_minus_wf
2150 if(icm_diff_wf.exists(1))
2151 then
2152 for ctr in icm_diff_wf.first .. icm_diff_wf.last loop
2153 cur_child_id := get_process_id_from_wf_params
2154 (icm_diff_wf(ctr).child_name,
2155 icm_diff_wf(ctr).child_item_type);
2156
2157 delete from amw_latest_hierarchies
2158 where (organization_id is null or organization_id = -1)
2159 and parent_id = l_process_id
2160 and child_id = cur_child_id;
2161 end loop;
2162 end if;
2163 close c_wf_minus_icm;
2164 close c_icm_minus_wf;
2165
2166 open c_wf_children(p_name, p_item_type);
2167 fetch c_wf_children bulk collect into wfc_tbl;
2168 if(wfc_tbl.exists(1))
2169 then
2170 for ctr in wfc_tbl.first .. wfc_tbl.last loop
2171 recursive_wf_import(p_parent_process_id => l_process_id,
2172 p_name => wfc_tbl(ctr).activity_name,
2173 p_item_type => wfc_tbl(ctr).activity_item_type,
2174 p_display_name => wfc_tbl(ctr).activity_display_name,
2175 p_description => wfc_tbl(ctr).activity_description,
2176 p_instance_id => wfc_tbl(ctr).instance_id,
2177 p_overwrite_ex => p_overwrite_ex);
2178 end loop;
2179 end if;
2180 close c_wf_children;
2181 end if;
2182 --there can be a case where the approved hierarchy needs to be changed
2183 --if the parent_process_id = -1 and the current procedure is approved ..
2184 --and will remain approved ...
2185 --however in this case this recursive invocation must be the first one itself
2186 --since parent_id = -1
2187
2188
2189 if(p_parent_process_id = -1)
2190 then
2191 select approval_status into l_approval_status
2192 from amw_process
2193 where process_id = l_process_id
2194 and end_date is null;
2195
2196 if(l_approval_status = 'A')
2197 then
2198 /* add link to approved hierarchy if not already there ... */
2199 for a_link in c1 loop
2200 insert into amw_approved_hierarchies
2201 (organization_id,
2202 parent_id,
2203 child_id,
2204 start_date,
2205 child_order_number,
2206 LAST_UPDATE_DATE,
2207 LAST_UPDATED_BY,
2208 LAST_UPDATE_LOGIN,
2209 CREATION_DATE,
2210 CREATED_BY,
2211 OBJECT_VERSION_NUMBER)
2212 values
2213 (-1,
2214 a_link.parent_process_id,
2215 a_link.child_process_id,
2216 g_sysdate,
2217 a_link.child_order_number,
2218 g_sysdate,
2219 g_user_id,
2220 g_login_id,
2221 g_sysdate,
2222 g_user_id,
2223 1);
2224 end loop;
2225
2226 /* now update the denorm table */
2227 update_approved_denorm(-1,g_sysdate);
2228 update_appr_control_counts;
2229 update_appr_risk_counts;
2230 end if;
2231
2232 end if;
2233
2234 visited_tbl(indexing_str) := 2;
2235 return;
2236
2237 end if;
2238 /* so process was not in ICM */
2239 --create a new process and add links ...
2240 --1. get the process_id of the newly created process
2241 --- and make the link from parent to child.
2242 --2. call recursively on each child
2243 --3. make links in latest hierarchy
2244 --4. before returning indicate that you are done ...
2245
2246 l_process_id := create_new_icm_process(p_name,p_item_type,p_display_name,
2247 p_description);
2248
2249
2250 insert into amw_latest_hierarchies (organization_id,
2251 parent_id,
2252 child_id,
2253 child_order_number,
2254 LAST_UPDATE_DATE,
2255 LAST_UPDATED_BY,
2256 LAST_UPDATE_LOGIN,
2257 CREATION_DATE,
2258 CREATED_BY,
2259 OBJECT_VERSION_NUMBER)
2260 values
2261 (-1,
2262 p_parent_process_id,
2263 l_process_id,
2264 p_instance_id,
2265 g_sysdate,
2266 g_user_id,
2267 g_login_id,
2268 g_sysdate,
2269 g_user_id,
2270 1);
2271
2272
2273
2274
2275
2276 /* loop and call recursively on each child in WF */
2277 open c_wf_children(p_name,p_item_type);
2278 fetch c_wf_children bulk collect into wfc_tbl;
2279
2280
2281
2282 if(wfc_tbl.exists(1))
2283 then
2284 for ctr in wfc_tbl.first .. wfc_tbl.last loop
2285 recursive_wf_import(p_parent_process_id => l_process_id,
2286 p_name => wfc_tbl(ctr).activity_name,
2287 p_item_type => wfc_tbl(ctr).activity_item_type,
2288 p_display_name => wfc_tbl(ctr).activity_display_name,
2289 p_description => wfc_tbl(ctr).activity_description,
2290 p_instance_id => wfc_tbl(ctr).instance_id,
2291 p_overwrite_ex => p_overwrite_ex);
2292 end loop;
2293 end if;
2294 close c_wf_children;
2295
2296 visited_tbl(indexing_str) := 2;
2297 return;
2298
2299 end recursive_wf_import;
2300
2301
2302 procedure import_wf_process(
2303 p_parent_process_id in number,
2304 p_comb_string in varchar2,
2305 p_overwrite_ex in varchar2,
2306 l_sysdate in Date default sysdate,
2307 p_update_denorm_count IN VARCHAR2 := 'Y',
2308 x_return_status out nocopy varchar2,
2309 x_msg_count out nocopy number,
2310 x_msg_data out nocopy varchar2)
2311 is
2312 iStart pls_integer := 1;
2313 iEnd pls_integer;
2314 cur_name wf_activities_vl.name%type;
2315 cur_item_type wf_activities_vl.item_type%type;
2316 cur_display_name wf_activities_vl.display_name%type;
2317 cur_description wf_activities_vl.description%type;
2318 cur_instance_id number;
2319
2320
2321 begin
2322 x_return_status := 'S';
2323 x_msg_count := 0;
2324 x_msg_data := '';
2325
2326 /* initializations for this procedure */
2327 g_sysdate := l_sysdate;
2328
2329 while (true) loop
2330 /* loop level initialization */
2331 visited_tbl.delete;
2332
2333 /* returns the position of first occurence of 'w' */
2334 iEnd := INSTR(p_comb_string, 'x', iStart);
2335 if(iEnd = 0)
2336 then
2337 exit;
2338 end if;
2339
2340 cur_name := substr(p_comb_string, iStart, iEnd-iStart);
2341 iStart := iEnd+1;
2342 iEnd := INSTR(p_comb_string, 'w', iStart);
2343 if(iEnd = 0)
2344 then
2345 iEnd := length(p_comb_string) + 1;
2346 end if;
2347 cur_item_type := substr(p_comb_string, iStart,iEnd-iStart);
2348 iStart := iEnd + 1;
2349
2350
2351 /* do your processing */
2352 select display_name, description, AMW_CHILD_ORDER_S.nextval
2353 into cur_display_name, cur_description, cur_instance_id
2354 from wf_activities_vl
2355 where name = cur_name
2356 and item_type = cur_item_type
2357 and end_date is null;
2358
2359
2360
2361
2362 recursive_wf_import(p_parent_process_id => p_parent_process_id,
2363 p_name => cur_name,
2364 p_item_type => cur_item_type,
2365 p_display_name => cur_display_name,
2366 p_description => cur_description,
2367 p_instance_id => cur_instance_id,
2368 p_overwrite_ex => p_overwrite_ex);
2369
2370
2371
2372
2373 end loop;
2374 IF p_update_denorm_count = 'Y' THEN
2375 /* update the denorm tables .. */
2376 update_denorm(p_org_id => -1,
2377 l_sysdate => g_sysdate);
2378
2379
2380 /* then update the risk_control_counts */
2381 update_all_latest_rc_counts(p_mode => 'RC');
2382 END IF;
2383
2384 exception
2385 when amw_process_locked_exception then
2386 ROLLBACK;
2387 x_return_status := FND_API.G_RET_STS_ERROR;
2388 x_msg_count := x_msg_count + 1;
2389 fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
2390 x_msg_data := fnd_message.get;
2391 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2392 p_procedure_name => 'import_wf_process',
2393 p_error_text => x_msg_data);
2394 raise FND_API.G_EXC_ERROR;
2395
2396 when amw_circularity_exception then
2397 ROLLBACK;
2398 x_return_status := FND_API.G_RET_STS_ERROR;
2399 x_msg_count := x_msg_count + 1;
2400 fnd_message.set_name('AMW','AMW_WF_CIRCULARITY_CREATION');
2401 x_msg_data := fnd_message.get;
2402 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2403 p_procedure_name => 'import_wf_process',
2404 p_error_text => x_msg_data);
2405 raise FND_API.G_EXC_ERROR;
2406
2407
2408 when amw_process_deleted_exception then
2409 ROLLBACK;
2410 x_return_status := FND_API.G_RET_STS_ERROR;
2411 x_msg_count := x_msg_count + 1;
2412 fnd_message.set_name('AMW','AMW_DELETED_PROC_MODIF');
2413 x_msg_data := fnd_message.get;
2414 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2415 p_procedure_name => 'import_wf_process',
2416 p_error_text => x_msg_data);
2417 raise FND_API.G_EXC_ERROR;
2418
2419 when amw_insfcnt_prvlg_exception then
2420 ROLLBACK;
2421 x_return_status := FND_API.G_RET_STS_ERROR;
2422 x_msg_count := x_msg_count + 1;
2423 fnd_message.set_name('AMW','AMW_INSFCNT_OPRN_PRVLG');
2424 x_msg_data := fnd_message.get;
2425 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2426 p_procedure_name => 'import_wf_process',
2427 p_error_text => x_msg_data);
2428 raise FND_API.G_EXC_ERROR;
2429
2430 when wf_cycle_present_exception then
2431 ROLLBACK;
2432 x_return_status := FND_API.G_RET_STS_ERROR;
2433 x_msg_count := x_msg_count + 1;
2434 fnd_message.set_name('AMW','AMW_CYCLE_IN_WF_HIERARCHY');
2435 x_msg_data := fnd_message.get;
2436 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2437 p_procedure_name => 'import_wf_process',
2438 p_error_text => x_msg_count);
2439 raise FND_API.G_EXC_ERROR;
2440
2441 when OTHERS then
2442 ROLLBACK;
2443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2444 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2445 p_count => x_msg_count,
2446 p_data => x_msg_data);
2447 end import_wf_process;
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465 --THIS IS REALLY A MIRROR IMAGE OF add_exisiting_process_as_child PROCEDURE
2466 --WITH SOME CHANGES ....
2467 --The parent process and the child process both exist as ICM processes
2468 procedure add_WEBADI_HIERARCHY_LINKS(
2469 p_child_order_number in number,
2470 p_parent_process_id in number,
2471 p_child_process_id in number,
2472 l_sysdate in Date default sysdate,
2473 x_return_status out nocopy varchar2,
2474 x_msg_count out nocopy number,
2475 x_msg_data out nocopy varchar2)
2476
2477 is
2478
2479 l_api_name constant varchar2(30) := 'add_WEBADI_HIERARCHY_LINKS';
2480 p_init_msg_list varchar2(10) := FND_API.G_FALSE;
2481 l_dummy pls_integer;
2482 L_CHILD_ORDER NUMBER;
2483 l_approval_status amw_process.approval_status%type;
2484 l_child_order_number amw_latest_hierarchies.child_order_number%type;
2485
2486 CURSOR c1 is
2487 (select parent_process_id,
2488 child_process_id,
2489 child_order_number from AMW_LATEST_HIERARCHY_RL_V
2490 start with parent_process_id = -1 and
2491 parent_approval_status = 'A'
2492 connect by prior child_process_id = parent_process_id and
2493 child_approval_status = 'A' )
2494 MINUS
2495
2496 (select parent_process_id,
2497 child_process_id,
2498 child_order_number
2499 from AMW_CURR_APP_HIERARCHY_RL_V);
2500
2501
2502 begin
2503 ---05.23.2005 npanandi: commenting below log message
2504 ---FND_FILE.PUT_LINE(FND_FILE.LOG,'add_WEBADI_HIERARCHY_LINKS START');
2505 x_return_status := FND_API.G_RET_STS_SUCCESS;
2506 if FND_API.to_Boolean(p_init_msg_list) then
2507 FND_MSG_PUB.initialize;
2508 end if;
2509
2510 if FND_GLOBAL.user_id is null then
2511 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
2512 raise FND_API.G_EXC_ERROR;
2513 end if;
2514
2515 --check if parent_process_id is null
2516 if p_parent_process_id is null then
2517 raise FND_API.G_EXC_ERROR;
2518 end if;
2519
2520 --check if child_process_id is null
2521 if p_child_process_id is null then
2522 raise FND_API.G_EXC_ERROR;
2523 end if;
2524
2525
2526
2527
2528 --check if parent process is locked
2529 if (is_locked_process(p_parent_process_id))
2530 then
2531 raise amw_process_locked_exception;
2532 end if;
2533
2534
2535 /* check if the child is an ancestor of parent already
2536 * so that a circular hierarchy cannot be created
2537 */
2538 if(is_ancestor_in_hierarchy(p_child_process_id,p_parent_process_id))
2539 then
2540 raise amw_circularity_exception;
2541 end if;
2542
2543
2544 --we now check if this link is already existing in the latest hierarchy
2545
2546 open c_link_exist(p_parent_process_id,p_child_process_id);
2547 fetch c_link_exist into l_dummy;
2548
2549
2550 if(c_link_exist%found)
2551 then
2552 close c_link_exist;
2553 return;
2554 end if;
2555 close c_link_exist;
2556
2557 /* else, all is fine, we can proceed with revising the parent process
2558 * and creating the parent, child link in the latest hierarchy
2559 */
2560 revise_process_if_necessary(p_process_id => p_parent_process_id,
2561 l_sysdate => l_sysdate);
2562
2563
2564
2565
2566
2567 --update the latest hierarchy table
2568 ---IF p_child_order_number IS -100, THIS MEANS THAT NO SEQ NUM WAS DEFINED BY USER
2569 --SO GENERATED VIA SEQUENCE
2570 IF(P_CHILD_ORDER_NUMBER = -100) THEN
2571 SELECT AMW_CHILD_ORDER_S.nextval INTO L_CHILD_ORDER FROM DUAL;
2572 ELSE
2573 L_CHILD_ORDER := P_CHILD_ORDER_NUMBER;
2574 END IF;
2575
2576 insert into amw_latest_hierarchies(ORGANIZATION_ID,
2577 PARENT_ID,
2578 CHILD_ID,
2579 CHILD_ORDER_NUMBER,
2580 LAST_UPDATE_DATE,
2581 LAST_UPDATED_BY,
2582 LAST_UPDATE_LOGIN,
2583 CREATION_DATE,
2584 CREATED_BY,
2585 OBJECT_VERSION_NUMBER)
2586 VALUES (-1,
2587 p_parent_process_id,
2588 p_child_process_id,
2589 L_CHILD_ORDER,
2590 l_sysdate,
2591 g_user_id,
2592 g_login_id,
2593 l_sysdate,
2594 g_user_id,
2595 1)
2596 returning CHILD_ORDER_NUMBER
2597 into l_child_order_number;
2598
2599
2600 update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id, p_child_process_id,
2601 l_child_order_number);
2602
2603
2604 /* update the denorm table
2605 * can throw an amw_processing_exception, so it has been handled.
2606 */
2607 ---COMMENTING THE BELOW ... DON'T NEED THIS FROM WEBADI
2608 /*
2609 update_denorm_add_child(p_parent_id => p_parent_process_id,
2610 p_child_id => p_child_process_id,
2611 l_sysdate => l_sysdate);
2612 */
2613
2614 /* if the process was approved to begin with
2615 * and its parent was -1
2616 * then the approved hierarchy needs to be modified
2617 */
2618 select approval_status into l_approval_status
2619 from amw_process where process_id = p_child_process_id and
2620 end_date is null;
2621
2622 if(l_approval_status = 'A' and p_parent_process_id = -1)
2623
2624 then
2625 for a_link in c1 loop
2626 insert into amw_approved_hierarchies
2627 (organization_id,
2628 parent_id,
2629 child_id,
2630 start_date,
2631 child_order_number,
2632 LAST_UPDATE_DATE,
2633 LAST_UPDATED_BY,
2634 LAST_UPDATE_LOGIN,
2635 CREATION_DATE,
2636 CREATED_BY,
2637 OBJECT_VERSION_NUMBER)
2638 values
2639 (-1,
2640 a_link.parent_process_id,
2641 a_link.child_process_id,
2642 l_sysdate,
2643 a_link.child_order_number,
2644 l_sysdate,
2645 g_user_id,
2646 g_login_id,
2647 l_sysdate,
2648 g_user_id,
2649 1);
2650 end loop;
2651
2652 /* now update the denorm table */
2653 update_approved_denorm(-1,l_sysdate);
2654
2655 end if;
2656
2657 ---05.23.2005 npanandi: commenting below log message
2658 ---FND_FILE.PUT_LINE(FND_FILE.LOG,'add_WEBADI_HIERARCHY_LINKS END');
2659
2660 --Call the APIs to adjust the risk and control counts
2661 --COMMENTING THE BELOW .... DON'T NEED THIS WHEN CALLING FROM WEBADI
2662 ---update_latest_control_counts(p_parent_process_id);
2663 ---update_latest_risk_counts(p_parent_process_id);
2664
2665 exception
2666 when FND_API.G_EXC_ERROR then
2667 ROLLBACK;
2668 x_return_status := FND_API.G_RET_STS_ERROR;
2669 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2670 p_count => x_msg_count,
2671 p_data => x_msg_data);
2672
2673
2674 when FND_API.G_EXC_UNEXPECTED_ERROR then
2675 ROLLBACK;
2676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2677 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2678 p_count => x_msg_count,
2679 p_data => x_msg_data);
2680
2681 when amw_process_locked_exception then
2682 x_return_status := FND_API.G_RET_STS_ERROR;
2683 x_msg_count := x_msg_count + 1;
2684 fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
2685 x_msg_data := fnd_message.get;
2686 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2687 p_procedure_name => 'add_WEBADI_HIERARCHY_LINKS',
2688 p_error_text => x_msg_data);
2689 raise FND_API.G_EXC_ERROR;
2690
2691
2692
2693
2694
2695 when amw_circularity_exception then
2696 x_return_status := FND_API.G_RET_STS_ERROR;
2697 x_msg_count := x_msg_count + 1;
2698 fnd_message.set_name('AMW','AMW_CIRCULARITY_CREATION');
2699 x_msg_data := fnd_message.get;
2700 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2701 p_procedure_name => 'add_WEBADI_HIERARCHY_LINKS',
2702 p_error_text => x_msg_data);
2703 raise FND_API.G_EXC_ERROR;
2704
2705
2706 when amw_processing_exception then
2707 ROLLBACK;
2708 x_return_status := FND_API.G_RET_STS_ERROR;
2709 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2710 p_count => x_msg_count,
2711 p_data => x_msg_data);
2712
2713
2714 when OTHERS then
2715 ROLLBACK;
2716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2717
2718 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2719 p_count => x_msg_count,
2720 p_data => x_msg_data);
2721
2722 end add_WEBADI_HIERARCHY_LINKS;
2723
2724
2725
2726 ----------------------------COUNT UPDATE API's---------------------------------------------
2727 /* update the approved risk counts for all processes above
2728 * p_process_id in RL Approved Hierarchy
2729 * Call this ONLY AFTER making the denorm tables reflect
2730 * the current denormed approved hierarchy
2731 * This can be called for post approval processing
2732 *** BE CAREFUL WITH THE USE OF THIS PROCEDURE ***
2733 * This process only affects procedures in the approved_hierarchy
2734 * The approved_hierarchy is always connected to the root
2735 * It is possible that a process was approved and yet not present
2736 * in the approved hierarchy. Thus after some process P was approved
2737 * and its child Q (approved earlier) was added to the approved
2738 * hierarchy WHEN P was approved: IF THIS PROCEDURE WAS CALLED ONLY FOR
2739 * P it will not affect Q and the final result would be INCORRECT.
2740 */
2741 procedure update_approved_risk_counts(p_process_id in number)
2742 is
2743
2744
2745 cursor c is
2746 (select process_id from amw_process where process_id in
2747 ((select parent_child_id
2748 from amw_proc_hierarchy_denorm
2749 where process_id = p_process_id
2750 and up_down_ind = 'U'
2751 and hierarchy_type = 'A'
2752 )
2753 union all
2754 (select p_process_id from dual)));
2755 type t_n is table of number;
2756 x t_n;
2757 begin
2758 open c;
2759 fetch c bulk collect into x;
2760 close c;
2761
2762 if x.exists(1) then
2763 forall ctr in x.first .. x.last
2764 update amw_process
2765 set risk_count = (select count(*) from (
2766 select distinct risk_id from amw_risk_associations
2767 where pk1 in ( ( select parent_child_id
2768 from amw_proc_hierarchy_denorm
2769 where process_id = x(ctr)
2770 and up_down_ind = 'D'
2771 and hierarchy_type = 'A') union all (select x(ctr) from dual) )
2772 and approval_date is not null
2773 and deletion_approval_date is null
2774 and object_type = 'PROCESS'
2775 ) ),
2776 object_version_number = object_version_number + 1,
2777 last_update_date = sysdate,
2778 last_updated_by = G_USER_ID,
2779 last_update_login = G_LOGIN_ID
2780 where process_id = x(ctr)
2781 and approval_date is not null
2782 and approval_end_date is null
2783 and process_id <> -1;
2784 end if;
2785
2786 exception
2787
2788 when others
2789 then raise FND_API.G_EXC_UNEXPECTED_ERROR;
2790
2791 end update_approved_risk_counts;
2792
2793
2794 /* update the approved control counts for all processes above
2795 * p_process_id in RL Approved Hierarchy
2796 * Call this ONLY AFTER making the denorm tables reflect
2797 * the current denormed approved hierarchy
2798 * This can be called for post approval processing
2799 *** BE CAREFUL WITH THE USE OF THIS PROCEDURE ***
2800 * This process only affects procedures in the approved_hierarchy
2801 * The approved_hierarchy is always connected to the root
2802 * It is possible that a process was approved and yet not present
2803 * in the approved hierarchy. Thus after some process P was approved
2804 * and its child Q (approved earlier) was added to the approved
2805 * hierarchy WHEN P was approved: IF THIS PROCEDURE WAS CALLED ONLY FOR
2806 * P it will not affect Q and the final result would be INCORRECT.
2807 */
2808 procedure update_approved_control_counts(p_process_id in number)
2809 is
2810 cursor c is
2811 (select process_id from amw_process where process_id in
2812 ((select parent_child_id
2813 from amw_proc_hierarchy_denorm
2814 where process_id = p_process_id
2815 and up_down_ind = 'U'
2816 and hierarchy_type = 'A'
2817 )
2818 union all
2819 (select p_process_id from dual)));
2820
2821 type t_n is table of number;
2822 x t_n;
2823 begin
2824 open c;
2825 fetch c bulk collect into x;
2826 close c;
2827
2828 if x.exists(1) then
2829 forall ctr in x.first .. x.last
2830 update amw_process
2831 set control_count = (select count(*) from (
2832 select distinct control_id from amw_control_associations
2833 where pk1 in ( ( select parent_child_id
2834 from amw_proc_hierarchy_denorm
2835 where process_id = x(ctr)
2836 and up_down_ind = 'D'
2837 and hierarchy_type = 'A') union all (select x(ctr) from dual) )
2838 and approval_date is not null
2839 and deletion_approval_date is null
2840 and object_type = 'RISK'
2841 ) ),
2842 object_version_number = object_version_number + 1,
2843 last_update_date = sysdate,
2844 last_updated_by = G_USER_ID,
2845 last_update_login = G_LOGIN_ID
2846 where process_id = x(ctr)
2847 and approval_date is not null
2848 and approval_end_date is null
2849 and process_id <> -1;
2850 end if;
2851
2852 exception
2853
2854 when others
2855 then raise FND_API.G_EXC_UNEXPECTED_ERROR;
2856
2857 end update_approved_control_counts;
2858
2859
2860
2861 /* updates risk counts for every process in
2862 * the approved hierarchy
2863 */
2864 procedure update_appr_risk_counts is
2865
2866 cursor c is
2867 (select process_id from amw_process where process_id in
2868 (select parent_child_id
2869 from amw_proc_hierarchy_denorm
2870 where process_id = -1
2871 and up_down_ind = 'D'
2872 and hierarchy_type = 'A'
2873 ));
2874 type t_n is table of number;
2875 x t_n;
2876 begin
2877 open c;
2878 fetch c bulk collect into x;
2879 close c;
2880
2881 if x.exists(1) then
2882 forall ctr in x.first .. x.last
2883 update amw_process
2884 set risk_count = (select count(*) from (
2885 select distinct risk_id from amw_risk_associations
2886 where pk1 in ( ( select parent_child_id
2887 from amw_proc_hierarchy_denorm
2888 where process_id = x(ctr)
2889 and up_down_ind = 'D'
2890 and hierarchy_type = 'A') union all (select x(ctr) from dual) )
2891 and approval_date is not null
2892 and deletion_approval_date is null
2893 and object_type = 'PROCESS'
2894 ) ),
2895 object_version_number = object_version_number + 1,
2896 last_update_date = sysdate,
2897 last_updated_by = G_USER_ID,
2898 last_update_login = G_LOGIN_ID
2899 where process_id = x(ctr)
2900 and approval_date is not null
2901 and approval_end_date is null
2902 and process_id <> -1;
2903 end if;
2904
2905 exception
2906
2907 when others
2908 then raise FND_API.G_EXC_UNEXPECTED_ERROR;
2909
2910 end update_appr_risk_counts;
2911
2912 procedure update_appr_control_counts is
2913 cursor c is
2914 (select process_id from amw_process where process_id in
2915 (select parent_child_id
2916 from amw_proc_hierarchy_denorm
2917 where process_id = -1
2918 and up_down_ind = 'D'
2919 and hierarchy_type = 'A'
2920 ));
2921 type t_n is table of number;
2922 x t_n;
2923
2924 begin
2925
2926 open c;
2927 fetch c bulk collect into x;
2928 close c;
2929
2930 if x.exists(1) then
2931 forall ctr in x.first .. x.last
2932 update amw_process
2933 set control_count = (select count(*) from (
2934 select distinct control_id from amw_control_associations
2935 where pk1 in ( (select parent_child_id
2936 from amw_proc_hierarchy_denorm
2937 where process_id = x(ctr)
2938 and up_down_ind = 'D'
2939 and hierarchy_type = 'A') union all (select x(ctr) from dual) )
2940 and approval_date is not null
2941 and deletion_approval_date is null
2942 and object_type = 'RISK'
2943 ) )
2944 ,object_version_number = object_version_number + 1,
2945 last_update_date = sysdate,
2946 last_updated_by = G_USER_ID,
2947 last_update_login = G_LOGIN_ID
2948 where process_id = x(ctr)
2949 and approval_date is not null
2950 and approval_end_date is null
2951 and process_id <> -1;
2952 end if;
2953
2954 exception
2955
2956 when others
2957 then raise FND_API.G_EXC_UNEXPECTED_ERROR;
2958
2959
2960 end update_appr_control_counts;
2961
2962
2963
2964
2965 /* update the latest control counts for all processes above
2966 * p_process_id in RL
2967 * Call this ONLY AFTER making the denorm tables reflect
2968 * the current denormed hierarchy
2969 */
2970 procedure update_latest_control_counts(p_process_id in number)
2971 is
2972
2973 cursor c is
2974 (select process_id from amw_process where process_id in
2975 (select parent_child_id
2976 from amw_proc_hierarchy_denorm
2977 where process_id = p_process_id
2978 and up_down_ind = 'U'
2979 and hierarchy_type = 'L'
2980 ) union all
2981 (select p_process_id from dual));
2982 type t_n is table of number;
2983 x t_n;
2984 begin
2985 open c;
2986 fetch c bulk collect into x;
2987 close c;
2988
2989 if x.exists(1) then
2990 forall ctr in x.first .. x.last
2991 update amw_process
2992 set control_count_latest = (select count(*) from (
2993 select distinct control_id from amw_control_associations
2994 where pk1 in ( ( select parent_child_id
2995 from amw_proc_hierarchy_denorm
2996 where process_id = x(ctr)
2997 and up_down_ind = 'D'
2998 and hierarchy_type = 'L') union all (select x(ctr) from dual) )
2999 and deletion_date is null
3000 and object_type = 'RISK'
3001 ) )
3002 --unsure whether FWK validation may throw error if ovn is incremented here
3003 --so am removing it here.
3004 --.object_version_number = object_version_number + 1,
3005 ,last_update_date = sysdate
3006 ,last_updated_by = G_USER_ID
3007 ,last_update_login = G_LOGIN_ID
3008 where process_id = x(ctr)
3009 and end_date is null
3010 and process_id <> -1;
3011 end if;
3012
3013
3014
3015 exception
3016
3017 when others
3018 then raise FND_API.G_EXC_UNEXPECTED_ERROR;
3019
3020 end update_latest_control_counts;
3021
3022
3023 procedure update_latest_risk_counts(p_process_id in number)
3024 is
3025 cursor c is
3026 (select process_id from amw_process where process_id in
3027 (select parent_child_id
3028 from amw_proc_hierarchy_denorm
3029 where process_id = p_process_id
3030 and up_down_ind = 'U'
3031 and hierarchy_type = 'L'
3032 ) union all
3033 (select p_process_id from dual));
3034 type t_n is table of number;
3035 x t_n;
3036
3037 begin
3038
3039 open c;
3040 fetch c bulk collect into x;
3041 close c;
3042
3043 if x.exists(1) then
3044 forall ctr in x.first .. x.last
3045 update amw_process
3046 set risk_count_latest = (select count(*) from (
3047 select distinct risk_id from amw_risk_associations
3048 where pk1 in ( (select parent_child_id
3049 from amw_proc_hierarchy_denorm
3050 where process_id = x(ctr)
3051 and up_down_ind = 'D'
3052 and hierarchy_type = 'L') union all (select x(ctr) from dual) )
3053 and deletion_date is null
3054 and object_type = 'PROCESS'
3055 ) )
3056 --,object_version_number = object_version_number + 1
3057 ,last_update_date = sysdate
3058 ,last_updated_by = G_USER_ID
3059 ,last_update_login = G_LOGIN_ID
3060 where process_id = x(ctr)
3061 and end_date is null
3062 and process_id <> -1;
3063 end if;
3064
3065 exception
3066
3067 when others
3068 then raise FND_API.G_EXC_UNEXPECTED_ERROR;
3069
3070 end update_latest_risk_counts;
3071
3072
3073 /* This is only being used by my Java API
3074 * assumes that a rollback on error will be done from the caller
3075 * please keep this in mind when using
3076 */
3077 procedure update_rc_latest_counts(p_process_id in number,
3078 x_return_status out nocopy varchar2,
3079 x_msg_count out nocopy number,
3080 x_msg_data out nocopy varchar2)
3081
3082 is
3083
3084
3085
3086
3087 l_api_name constant varchar2(30) := 'update_rc_latest_counts';
3088
3089 p_init_msg_list varchar2(10) := FND_API.G_FALSE;
3090
3091 begin
3092 x_return_status := FND_API.G_RET_STS_SUCCESS;
3093 if FND_API.to_Boolean(p_init_msg_list) then
3094 FND_MSG_PUB.initialize;
3095 end if;
3096
3097 if FND_GLOBAL.user_id is null then
3098 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
3099 raise FND_API.G_EXC_ERROR;
3100 end if;
3101
3102
3103 update_latest_risk_counts(p_process_id => p_process_id);
3104 update_latest_control_counts(p_process_id => p_process_id);
3105
3106 exception
3107 when others then
3108 x_return_status := FND_API.G_RET_STS_ERROR;
3109 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3110 p_count => x_msg_count,
3111 p_data => x_msg_data);
3112 end update_rc_latest_counts;
3113
3114
3115
3116 /* update the latest risk-control counts for ALL processes in RL */
3117 procedure update_all_latest_rc_counts(p_mode in varchar2)
3118
3119 is
3120 cursor c is
3121 (select process_id from amw_process where end_date is null and process_id <> -1);
3122 type t_n is table of number;
3123 x t_n;
3124
3125 begin
3126 open c;
3127 fetch c bulk collect into x;
3128 close c;
3129
3130 if x.exists(1) then
3131 if(p_mode = 'R')
3132 then
3133 forall ctr in x.first .. x.last
3134 update amw_process
3135 set risk_count_latest = (select count(*) from (
3136 select distinct risk_id
3137 from amw_risk_associations
3138 where pk1 in ((select parent_child_id
3139 from amw_proc_hierarchy_denorm
3140 where process_id = x(ctr)
3141 and up_down_ind = 'D'
3142 and hierarchy_type = 'L') union all (select x(ctr) from dual) )
3143 and deletion_date is null
3144 and object_type = 'PROCESS'
3145 )),
3146 object_version_number = object_version_number + 1
3147 ,last_update_date = sysdate
3148 ,last_updated_by = G_USER_ID
3149 ,last_update_login = G_LOGIN_ID
3150 where process_id = x(ctr)
3151 and end_date is null;
3152
3153 elsif (p_mode = 'C')
3154 then
3155 forall ctr in x.first .. x.last
3156 update amw_process
3157 set control_count_latest = (select count(*) from
3158
3159 (select distinct control_id from amw_control_associations
3160 where pk1 in ((select parent_child_id
3161 from amw_proc_hierarchy_denorm
3162 where process_id = x(ctr)
3163 and up_down_ind = 'D'
3164 and hierarchy_type = 'L') union all (select x(ctr) from dual) )
3165 and deletion_date is null
3166 and object_type = 'RISK'
3167 )),
3168 object_version_number = object_version_number + 1
3169 ,last_update_date = sysdate
3170 ,last_updated_by = G_USER_ID
3171 ,last_update_login = G_LOGIN_ID
3172 where process_id = x(ctr)
3173 and end_date is null;
3174 elsif (p_mode = 'RC')
3175 then
3176 forall ctr in x.first .. x.last
3177 update amw_process
3178 set risk_count_latest = (select count(*) from (
3179 select distinct risk_id
3180 from amw_risk_associations
3181 where pk1 in ((select parent_child_id
3182 from amw_proc_hierarchy_denorm
3183 where process_id = x(ctr)
3184 and up_down_ind = 'D'
3185 and hierarchy_type = 'L') union all (select x(ctr) from dual) )
3186 and deletion_date is null
3187 and object_type = 'PROCESS'
3188 )),
3189
3190 control_count_latest = (select count(*) from
3191
3192 (select distinct control_id from amw_control_associations
3193 where pk1 in ((select parent_child_id
3194 from amw_proc_hierarchy_denorm
3195 where process_id = x(ctr)
3196 and up_down_ind = 'D'
3197 and hierarchy_type = 'L') union all (select x(ctr) from dual) )
3198 and deletion_date is null
3199 and object_type = 'RISK')),
3200 object_version_number = object_version_number + 1
3201 ,last_update_date = sysdate
3202 ,last_updated_by = G_USER_ID
3203 ,last_update_login = G_LOGIN_ID
3204 where process_id = x(ctr)
3205 and end_date is null;
3206
3207
3208
3209
3210 end if;
3211 end if;
3212
3213
3214 exception
3215
3216 when others
3217 then raise FND_API.G_EXC_UNEXPECTED_ERROR;
3218
3219 end update_all_latest_rc_counts;
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231 /* Update the org_counts for all approved processes in RL
3232 * Amit, in my opinion we need something that does it per process
3233 * rather than for everything -- pls, give this a thought
3234 */
3235 procedure update_all_org_counts
3236
3237 is
3238 cursor c is (select process_id from amw_process where
3239 approval_date is not null
3240 and approval_end_date is null
3241 and process_id <> -1);
3242 type t_n is table of number;
3243 x t_n;
3244 begin
3245 open c;
3246 fetch c bulk collect into x;
3247 close c;
3248
3249 if x.exists(1) then
3250 forall ctr in x.first .. x.last
3251 update amw_process
3252 set org_count = (select count(*) from
3253 (select distinct organization_id
3254 from amw_process_organization
3255 where process_id = x(ctr)
3256 and end_date is null
3257 and (deletion_date is null or (deletion_date is not null and approval_date is null)))),
3258 object_version_number = object_version_number + 1
3259 ,last_update_date = sysdate
3260 ,last_updated_by = G_USER_ID
3261 ,last_update_login = G_LOGIN_ID
3262 where process_id = x(ctr)
3263 and approval_date is not null
3264 and approval_end_date is null;
3265 end if;
3266
3267 end update_all_org_counts;
3268
3269
3270 /* update the org count for p_process_id */
3271 procedure update_org_count(p_process_id in number)
3272
3273 is
3274
3275 begin
3276 update amw_process
3277 set org_count = (select count(*) from
3278 (select distinct organization_id
3279 from amw_process_organization
3280 where process_id = p_process_id
3281 and end_date is null
3282 and (deletion_date is null or (deletion_date is not null and approval_date is null)))),
3283 object_version_number = object_version_number + 1
3284 ,last_update_date = sysdate
3285 ,last_updated_by = G_USER_ID
3286 ,last_update_login = G_LOGIN_ID
3287 where approval_date is not null
3288 and approval_end_date is null
3289 and process_id <> -1 --retained for safety
3290 and process_id = p_process_id;
3291
3292
3293 exception
3294
3295
3296 when others
3297 then raise FND_API.G_EXC_UNEXPECTED_ERROR;
3298
3299 end update_org_count;
3300
3301 -------------------------------------------------------------------------------------------
3302
3303
3304 /* the following is needed for ProcessRevisionAMImpl.java
3305 */
3306
3307 procedure update_attachments(p_old_prev_id in varchar2,
3308 p_new_prev_id in varchar2,
3309 x_return_status out nocopy varchar2,
3310 x_msg_count out nocopy number,
3311 x_msg_data out nocopy varchar2)
3312
3313 is
3314 l_api_name constant varchar2(30) := 'update_attachments';
3315
3316 p_init_msg_list varchar2(10) := FND_API.G_FALSE;
3317
3318 begin
3319 x_return_status := FND_API.G_RET_STS_SUCCESS;
3320 if FND_API.to_Boolean(p_init_msg_list) then
3321 FND_MSG_PUB.initialize;
3322 end if;
3323
3324 if FND_GLOBAL.user_id is null then
3325 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
3326 raise FND_API.G_EXC_ERROR;
3327 end if;
3328
3329 --First remove all the old attachments
3330 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS',
3331 X_pk1_value => p_old_prev_id);
3332
3333 --copy over all attachments of the temporary rev id
3334 fnd_attached_documents2_pkg.copy_attachments(
3335 X_from_entity_name => 'AMW_PROCESS',
3336 X_from_pk1_value => p_new_prev_id,
3337 X_to_entity_name => 'AMW_PROCESS',
3338 X_to_pk1_value => p_old_prev_id,
3339 X_created_by => g_user_id,
3340 X_last_update_login => g_login_id,
3341 X_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
3342 X_request_id => FND_GLOBAL.conc_request_id);
3343
3344
3345 --remove all the attachments of the temporary id
3346
3347 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS',
3348 X_pk1_value => p_new_prev_id);
3349
3350
3351 exception
3352 when OTHERS then
3353
3354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3355
3356 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3357 p_count => x_msg_count,
3358 p_data => x_msg_data);
3359
3360 end update_attachments;
3361
3362 procedure create_new_process_as_child(
3363 p_parent_process_id in number,
3364 p_item_type in varchar2,
3365 p_display_name in varchar2,
3366 p_description in varchar2,
3367 p_control_type in varchar2,
3368 x_return_status out nocopy varchar2,
3369 x_msg_count out nocopy number,
3370 x_msg_data out nocopy varchar2) is
3371
3372 l_pid number;
3373 l_name number;
3374
3375 begin
3376
3377 select amw_process_name_s.nextval into l_name from dual;
3378
3379 l_pid := create_new_icm_process(
3380 p_name => to_char(l_name),
3381 p_item_type => p_item_type,
3382 p_display_name => p_display_name,
3383 p_description => p_description);
3384
3385 update amw_process
3386 set PROCESS_TYPE = decode(p_control_type, 'A', 'C', 'M', 'C', 'B', 'C', PROCESS_TYPE),
3387 CONTROL_ACTIVITY_TYPE = decode(p_control_type, '-1', CONTROL_ACTIVITY_TYPE, p_control_type)
3388 where process_id = l_pid
3389 and end_date is null;
3390
3391 add_existing_process_as_child(
3392 p_parent_process_id => p_parent_process_id,
3393 p_child_process_id => l_pid,
3394 x_return_status => x_return_status,
3395 x_msg_count => x_msg_count,
3396 x_msg_data => x_msg_data);
3397
3398 conv_tutor_grants(l_pid);
3399
3400 end create_new_process_as_child;
3401
3402
3403
3404 procedure conv_tutor_add_child(
3405 p_parent_process_id in number,
3406 p_display_name in varchar2,
3407 p_control_type in varchar2,
3408 x_return_status out nocopy varchar2,
3409 x_msg_count out nocopy number,
3410 x_msg_data out nocopy varchar2) is
3411
3412 l_pid number;
3413
3414 begin
3415
3416 select process_id
3417 into l_pid
3418 from AMW_LATEST_REVISIONS_V
3419 where display_name = p_display_name;
3420
3421 add_existing_process_as_child(
3422 p_parent_process_id => p_parent_process_id,
3423 p_child_process_id => l_pid,
3424 x_return_status => x_return_status,
3425 x_msg_count => x_msg_count,
3426 x_msg_data => x_msg_data);
3427
3428 conv_tutor_grants(l_pid);
3429
3430 exception
3431 when too_many_rows then
3432 fnd_message.set_name('AMW','AMW_COV_TUTOR_NONUNQ');
3433 fnd_message.set_token('AMW_COV_TUTOR_NONUNQ', p_display_name);
3434 x_msg_data := fnd_message.get;
3435 x_return_status := FND_API.G_RET_STS_ERROR;
3436
3437
3438 when no_data_found then
3439 create_new_process_as_child(
3440 p_parent_process_id => p_parent_process_id,
3441 p_item_type => 'AUDITMGR',
3442 p_display_name => p_display_name,
3443 p_description => p_display_name,
3444 p_control_type => p_control_type,
3445 x_return_status => x_return_status,
3446 x_msg_count => x_msg_count,
3447 x_msg_data => x_msg_data);
3448
3449 end conv_tutor_add_child;
3450
3451
3452 procedure conv_tutor_grants(l_process_id in number) is
3453
3454 l_return_status varchar2(10);
3455 l_msg_count number;
3456 l_msg_data varchar2(4000);
3457 l_party_id number;
3458
3459 begin
3460
3461 select person_party_id
3462 into l_party_id
3463 from fnd_user
3464 where user_id = G_USER_ID;
3465
3466 AMW_SECURITY_PUB.grant_role_guid
3467 (
3468 p_api_version => 1,
3469 p_role_name => 'AMW_RL_PROC_OWNER_ROLE',
3470 p_object_name => 'AMW_PROCESS_APPR_ETTY',
3471 p_instance_type => 'INSTANCE',
3472 p_instance_set_id => null,
3473 p_instance_pk1_value => l_process_id,
3474 p_instance_pk2_value => null,
3475 p_instance_pk3_value => null,
3476 p_instance_pk4_value => null,
3477 p_instance_pk5_value => null,
3478 p_party_id => l_party_id,
3479 p_start_date => g_sysdate,
3480 p_end_date => null,
3481 x_return_status => l_return_status,
3482 x_errorcode => l_msg_count,
3483 x_grant_guid => l_msg_data);
3484
3485 /* AMW_SECURITY_PUB.grant_role_guid
3486 (
3487 p_api_version => 1,
3488 p_role_name => 'AMW_RL_PROC_FINANCE_OWNER_ROLE',
3489 p_object_name => 'AMW_PROCESS_APPR_ETTY',
3490 p_instance_type => 'INSTANCE',
3491 p_instance_set_id => null,
3492 p_instance_pk1_value => l_process_id,
3493 p_instance_pk2_value => null,
3494 p_instance_pk3_value => null,
3495 p_instance_pk4_value => null,
3496 p_instance_pk5_value => null,
3497 p_party_id => l_party_id,
3498 p_start_date => g_sysdate,
3499 p_end_date => null,
3500 x_return_status => l_return_status,
3501 x_errorcode => l_msg_count,
3502 x_grant_guid => l_msg_data);
3503
3504 AMW_SECURITY_PUB.grant_role_guid
3505 (
3506 p_api_version => 1,
3507 p_role_name => 'AMW_RL_PROC_APPL_OWNER_ROLE',
3508 p_object_name => 'AMW_PROCESS_APPR_ETTY',
3509 p_instance_type => 'INSTANCE',
3510 p_instance_set_id => null,
3511 p_instance_pk1_value => l_process_id,
3512 p_instance_pk2_value => null,
3513 p_instance_pk3_value => null,
3514 p_instance_pk4_value => null,
3515 p_instance_pk5_value => null,
3516 p_party_id => l_party_id,
3517 p_start_date => g_sysdate,
3518 p_end_date => null,
3519 x_return_status => l_return_status,
3520 x_errorcode => l_msg_count,
3521 x_grant_guid => l_msg_data);
3522 */
3523
3524 end conv_tutor_grants;
3525
3526
3527
3528 procedure Check_Root_Access(p_predicate in varchar2,
3529 p_hasAccess out NOCOPY varchar2) is
3530
3531 l_hasaccess varchar2(1) := 'N';
3532 cursor_select INTEGER;
3533 cursor_execute INTEGER;
3534 query_to_exec VARCHAR2(32767);
3535
3536 begin
3537
3538 query_to_exec := 'select process_id from AMW_CURRENT_APPRVD_REV_V where process_id = -1 and '||p_predicate;
3539 cursor_select := DBMS_SQL.OPEN_CURSOR;
3540 DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
3541 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
3542 IF DBMS_SQL.FETCH_ROWS(cursor_select) > 0 THEN
3543 l_hasaccess := 'Y';
3544 ELSE
3545 l_hasaccess := 'N';
3546 END IF;
3547 DBMS_SQL.CLOSE_CURSOR(cursor_select);
3548
3549 p_hasAccess := l_hasAccess;
3550
3551 end Check_Root_Access;
3552
3553
3554
3555 PROCEDURE reset_count(
3556 errbuf out nocopy varchar2,
3557 retcode out nocopy varchar2
3558 ) IS
3559
3560 conc_status boolean;
3561
3562 BEGIN
3563 retcode :=0;
3564 errbuf :='';
3565
3566 --updates latest hier denorm
3567 amw_rl_hierarchy_pkg.update_denorm (-1, sysdate);
3568 --updates approved hier denorm
3569 amw_rl_hierarchy_pkg.update_approved_denorm (-1, sysdate);
3570
3571 update amw_process
3572 set risk_count = null,
3573 control_count = null,
3574 risk_count_latest = null,
3575 control_count_latest = null;
3576
3577 --updates latest risk/control counts
3578 amw_rl_hierarchy_pkg.update_all_latest_rc_counts('RC');
3579 --updates approved risk counts
3580 amw_rl_hierarchy_pkg.update_appr_risk_counts;
3581 --updates approved control counts
3582 amw_rl_hierarchy_pkg.update_appr_control_counts;
3583 -- update approved org counts
3584 amw_rl_hierarchy_pkg.update_all_org_counts;
3585
3586 commit;
3587
3588 EXCEPTION
3589
3590 WHEN OTHERS THEN
3591 rollback;
3592 retcode :=2;
3593 errbuf := SUBSTR(SQLERRM,1,1000);
3594 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error: '|| SQLERRM);
3595
3596 END reset_count;
3597
3598
3599 -- returns 1 if process is present in the latest hiearchy
3600 -- 0 otherwise
3601 function is_proc_in_ltst_hier(p_process_id in number) return number is
3602 ret_val number := 1;
3603 begin
3604 if p_process_id = -1 then
3605 return 1;
3606 else
3607 select 1 into ret_val
3608 from dual
3609 where (exists
3610 (select parent_id from amw_latest_hierarchies
3611 where parent_id = p_process_id
3612 and (organization_id = -1 or organization_id is null)))
3613 or (exists
3614 (select child_id from amw_latest_hierarchies
3615 where child_id = p_process_id
3616 and (organization_id = -1 or organization_id is null)));
3617
3618 return ret_val;
3619 end if;
3620 exception
3621 when no_data_found then
3622 ret_val := 0;
3623 return ret_val;
3624
3625 when too_many_rows then -- shouldn't happen... still
3626 ret_val := 1;
3627 return ret_val;
3628
3629 end is_proc_in_ltst_hier;
3630
3631 function areChildListSame(p_process_id in number) return varchar is
3632 retvalue varchar2(1);
3633 l_dummy number;
3634 begin
3635
3636 retvalue := 'N';
3637
3638 begin
3639 select child_id
3640 into l_dummy
3641 from amw_approved_hierarchies
3642 where parent_id = p_process_id
3643 and (organization_id is null or organization_id = -1)
3644 and (end_date is null or end_date > sysdate)
3645 and child_id not in
3646 (select child_id
3647 from amw_latest_hierarchies
3648 where parent_id = p_process_id
3649 and (organization_id is null or organization_id = -1));
3650 exception
3651 when no_data_found then
3652 begin
3653 select child_id
3654 into l_dummy
3655 from amw_latest_hierarchies
3656 where parent_id = p_process_id
3657 and (organization_id is null or organization_id = -1)
3658 and child_id not in
3659 (select child_id
3660 from amw_approved_hierarchies
3661 where parent_id = p_process_id
3662 and (organization_id is null or organization_id = -1)
3663 and (end_date is null or end_date > sysdate));
3664 exception
3665 when too_many_rows then
3666 return retvalue;
3667 when no_data_found then
3668 retvalue := 'Y';
3669 return retvalue;
3670 end;
3671 when too_many_rows then
3672 return retvalue;
3673 end;
3674 return retvalue;
3675 end;
3676
3677 function does_apprvd_ver_exst(p_process_id in number) return varchar is
3678 l_dummy number;
3679 begin
3680 select 1
3681 into l_dummy
3682 from amw_process
3683 where process_id = p_process_id
3684 and approval_status = 'A';
3685
3686 return 'Y';
3687
3688 exception
3689 when no_data_found then
3690 return 'N';
3691 when too_many_rows then
3692 return 'Y';
3693 end;
3694
3695 -- this api is to be called from java to figure out if the process
3696 -- is undoable or not. Based on this, the Undo buutton should
3697 -- be rendered
3698 procedure isProcessUndoAble ( p_process_id in number,
3699 ret_value out nocopy varchar2,
3700 x_return_status out nocopy varchar2,
3701 x_msg_count out nocopy number,
3702 x_msg_data out nocopy varchar2) is
3703
3704 l_api_name constant varchar2(30) := 'isProcessUndoAble';
3705 p_init_msg_list varchar2(10) := FND_API.G_FALSE;
3706 err_msg varchar2(4000);
3707 l_dummy number;
3708 appstatus varchar2(10);
3709
3710 begin
3711 x_return_status := FND_API.G_RET_STS_SUCCESS;
3712 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3713 FND_MSG_PUB.initialize;
3714 END IF;
3715 IF FND_GLOBAL.User_Id IS NULL THEN
3716 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
3717 RAISE FND_API.G_EXC_ERROR;
3718 END IF;
3719
3720 ret_value := 'N';
3721
3722 -- check if the process is draft
3723
3724 select approval_status into appstatus from amw_process
3725 where process_id = p_process_id and end_date is null;
3726
3727 if appstatus <> 'D' then
3728 return;
3729 end if;
3730
3731 -- check if the draft has been created due to addition/deletion of children
3732
3733 if areChildListSame(p_process_id) = 'Y' then
3734 ret_value := 'Y';
3735 return;
3736 else
3737 return;
3738 end if;
3739
3740 exception
3741 WHEN FND_API.G_EXC_ERROR THEN
3742 ROLLBACK;
3743 x_return_status := FND_API.G_RET_STS_ERROR;
3744 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3745 p_count => x_msg_count,
3746 p_data => x_msg_data);
3747
3748 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3749 ROLLBACK;
3750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3751 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3752 p_count => x_msg_count,
3753 p_data => x_msg_data);
3754
3755 WHEN OTHERS THEN
3756 ROLLBACK;
3757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3758 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3759 THEN
3760 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
3761 END IF;
3762 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
3763 p_count => x_msg_count,
3764 p_data => x_msg_data);
3765 end;
3766
3767 --========================================================================================================
3768 -- call this only after calling isProcessUndoAble.
3769 -- This api performs the delete (purging of draft row) action
3770 -- if conditions are satisfied
3771 procedure delete_draft (p_process_id in number,
3772 x_return_status out nocopy varchar2,
3773 x_msg_count out nocopy number,
3774 x_msg_data out nocopy varchar2) is
3775
3776 l_api_name constant varchar2(30) := 'delete_draft';
3777 p_init_msg_list varchar2(10) := FND_API.G_FALSE;
3778 err_msg varchar2(4000);
3779 appexst varchar2(1);
3780 l_risk_exists boolean :=false;
3781 l_control_exists boolean :=false;
3782 cursor parents(pid number) is
3783 select parent_id
3784 from amw_latest_hierarchies
3785 where child_id = pid
3786 and (organization_id is null or organization_id = -1);
3787 parent_rec parents%rowtype;
3788 l_flag varchar2(10);
3789 previd number;
3790 l_dummy number;
3791 ret_val varchar2(10);
3792
3793 begin
3794 x_return_status := FND_API.G_RET_STS_SUCCESS;
3795 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3796 FND_MSG_PUB.initialize;
3797 END IF;
3798 IF FND_GLOBAL.User_Id IS NULL THEN
3799 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
3800 RAISE FND_API.G_EXC_ERROR;
3801 END IF;
3802
3803
3804
3805 --ko we need this to be outside of IF block to delete the attachments.
3806 select process_rev_id
3807 into previd from amw_process
3808 where process_id = p_process_id
3809 and end_date is null;
3810
3811 appexst := does_apprvd_ver_exst(p_process_id);
3812 if appexst = 'Y' then
3813
3814 --ko moved this undoable check from outside of ifblock to inside..As a new draft can be deleted even if it is having children.
3815 -- do another check for undoablity
3816
3817 isProcessUndoAble ( p_process_id => p_process_id,
3818 ret_value => ret_val,
3819 x_return_status => x_return_status,
3820 x_msg_count => x_msg_count,
3821 x_msg_data => x_msg_data);
3822
3823 if ret_val <> 'Y' then
3824 fnd_message.set_name('AMW','AMW_CANT_UNDO_DRAFT');
3825 err_msg := fnd_message.get;
3826 fnd_msg_pub.add_exc_msg(p_pkg_name => 'amw_rl_hierarchy_pkg',
3827 p_procedure_name => 'delete_draft',
3828 p_error_text => err_msg);
3829 raise FND_API.G_EXC_ERROR;
3830 end if;
3831 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3832 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3833 end if;
3834
3835
3836 delete from amw_process
3837 where process_id = p_process_id
3838 and end_date is null;
3839
3840 delete from amw_process_names_tl
3841 where process_rev_id = previd;
3842
3843 update amw_process
3844 set end_date = null
3845 where process_id = p_process_id
3846 and approval_date is not null
3847 and approval_end_date is null;
3848
3849 else -- appexst = 'N'
3850
3851 select standard_process_flag, process_rev_id
3852 into l_flag, previd from amw_process
3853 where process_id = p_process_id;
3854
3855 if l_flag = 'Y' then
3856 begin
3857 select 1 into l_dummy from dual
3858 where exists
3859 (select 1 from amw_process
3860 where standard_variation = previd
3861 and end_date is null);
3862
3863 fnd_message.set_name('AMW','AMW_CANT_DEL_DRAFT_NS');
3864 err_msg := fnd_message.get;
3865 fnd_msg_pub.add_exc_msg(p_pkg_name => 'amw_rl_hierarchy_pkg',
3866 p_procedure_name => 'delete_draft',
3867 p_error_text => err_msg);
3868 raise FND_API.G_EXC_ERROR;
3869
3870 exception
3871 when no_data_found then
3872 null;
3873 end;
3874 end if;
3875
3876 for parent_rec in parents(p_process_id) loop
3877 exit when parents%notfound;
3878 revise_process_if_necessary(parent_rec.parent_id);
3879 delete from amw_latest_hierarchies
3880 where parent_id = parent_rec.parent_id
3881 and child_id = p_process_id
3882 and (organization_id is null or organization_id = -1);
3883 end loop;
3884
3885 --kosriniv need to de link the children this process has..
3886 delete from amw_latest_hierarchies
3887 where parent_id = p_process_id
3888 and (organization_id is null or organization_id = -1);
3889 delete from amw_process where process_id = p_process_id;
3890 delete from amw_process_names_tl where process_rev_id = previd;
3891
3892 end if;
3893
3894 -- perform other common delete operations
3895
3896 delete from amw_risk_associations
3897 where pk1 = p_process_id
3898 and approval_date is null
3899 and object_type = 'PROCESS';
3900 --ko we need to update the latest risk & controls counts..
3901 IF SQL%FOUND THEN
3902 l_risk_exists := TRUE;
3903 END IF;
3904
3905 update amw_risk_associations
3906 set deletion_date = null
3907 where pk1 = p_process_id
3908 and object_type = 'PROCESS'
3909 and deletion_date is not null
3910 and deletion_approval_date is null;
3911
3912 IF SQL%FOUND THEN
3913 l_risk_exists := TRUE;
3914 END IF;
3915
3916 delete from amw_control_associations
3917 where pk1 = p_process_id
3918 and approval_date is null
3919 and object_type = 'RISK';
3920
3921 IF SQL%FOUND THEN
3922 l_control_exists := TRUE;
3923 END IF;
3924
3925 update amw_control_associations
3926 set deletion_date = null
3927 where pk1 = p_process_id
3928 and object_type = 'RISK'
3929 and deletion_date is not null
3930 and deletion_approval_date is null;
3931
3932 IF SQL%FOUND THEN
3933 l_control_exists := TRUE;
3934 END IF;
3935
3936 delete from amw_acct_associations
3937 where pk1 = p_process_id
3938 and approval_date is null
3939 and object_type = 'PROCESS';
3940
3941 update amw_acct_associations
3942 set deletion_date = null
3943 where pk1 = p_process_id
3944 and object_type = 'PROCESS'
3945 and deletion_date is not null
3946 and deletion_approval_date is null;
3947
3948
3949 delete from amw_objective_associations
3950 where pk1 = p_process_id
3951 and approval_date is null
3952 and object_type in ('PROCESS', 'CONTROL');
3953
3954 update amw_objective_associations
3955 set deletion_date = null
3956 where pk1 = p_process_id
3957 and object_type in ('PROCESS', 'CONTROL')
3958 and deletion_date is not null
3959 and deletion_approval_date is null;
3960
3961
3962 delete from amw_significant_elements
3963 where pk1 = p_process_id
3964 and approval_date is null
3965 and object_type = 'PROCESS';
3966
3967 update amw_significant_elements
3968 set deletion_date = null
3969 where pk1 = p_process_id
3970 and object_type = 'PROCESS'
3971 and deletion_date is not null
3972 and deletion_approval_date is null;
3973
3974
3975 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS',
3976 X_pk1_value => previd);
3977
3978 -- cancel existing change requests
3979
3980 -- update latest hierarchy denorm
3981 amw_rl_hierarchy_pkg.update_denorm (-1, sysdate);
3982
3983 --ko update the risk control counts..
3984
3985 if appexst = 'Y' AND l_risk_exists then
3986
3987 -- Update the latest risk control counts..
3988 update_latest_risk_counts(p_process_id);
3989 end if;
3990
3991 if appexst = 'Y' AND l_control_exists then
3992
3993 update_latest_control_counts(p_process_id);
3994
3995 end if;
3996
3997 exception
3998 WHEN FND_API.G_EXC_ERROR THEN
3999 ROLLBACK;
4000 x_return_status := FND_API.G_RET_STS_ERROR;
4001 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4002 p_count => x_msg_count,
4003 p_data => x_msg_data);
4004
4005 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4006 ROLLBACK;
4007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4008 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4009 p_count => x_msg_count,
4010 p_data => x_msg_data);
4011
4012 WHEN OTHERS THEN
4013 ROLLBACK;
4014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4015 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4016 THEN
4017 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
4018 END IF;
4019 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
4020 p_count => x_msg_count,
4021 p_data => x_msg_data);
4022 end;
4023
4024
4025 --========================================================================================================
4026 -- call this in the create process from UI page..
4027 -- This api gives the user process owner grant..
4028 -- if conditions are satisfied
4029 procedure create_process_owner_grant (p_process_id in varchar2,
4030 x_return_status out nocopy varchar2,
4031 x_msg_count out nocopy number,
4032 x_msg_data out nocopy varchar2) is
4033
4034 l_api_name constant varchar2(30) := 'create_process_owner_grant';
4035 p_init_msg_list varchar2(10) := FND_API.G_FALSE;
4036 err_msg varchar2(4000);
4037 l_party_id number;
4038
4039 begin
4040
4041 --always initialize global variables in th api's used from SelfSerivice Fwk..
4042 G_USER_ID := FND_GLOBAL.USER_ID;
4043 G_LOGIN_ID := FND_GLOBAL.CONC_LOGIN_ID;
4044 x_return_status := FND_API.G_RET_STS_SUCCESS;
4045 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4046 FND_MSG_PUB.initialize;
4047 END IF;
4048 IF FND_GLOBAL.User_Id IS NULL THEN
4049 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
4050 RAISE FND_API.G_EXC_ERROR;
4051 END IF;
4052
4053 select person_party_id
4054 into l_party_id
4055 from fnd_user
4056 where user_id = G_USER_ID;
4057
4058 AMW_SECURITY_PUB.grant_role_guid
4059 (
4060 p_api_version => 1,
4061 p_role_name => 'AMW_RL_PROC_OWNER_ROLE',
4062 p_object_name => 'AMW_PROCESS_APPR_ETTY',
4063 p_instance_type => 'INSTANCE',
4064 p_instance_set_id => null,
4065 p_instance_pk1_value => p_process_id,
4066 p_instance_pk2_value => null,
4067 p_instance_pk3_value => null,
4068 p_instance_pk4_value => null,
4069 p_instance_pk5_value => null,
4070 p_party_id => l_party_id,
4071 p_start_date => g_sysdate,
4072 p_end_date => null,
4073 x_return_status =>x_return_status,
4074 x_errorcode => x_msg_count,
4075 x_grant_guid => x_msg_data);
4076
4077
4078
4079 exception
4080 WHEN FND_API.G_EXC_ERROR THEN
4081 ROLLBACK;
4082 x_return_status := FND_API.G_RET_STS_ERROR;
4083 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4084 p_count => x_msg_count,
4085 p_data => x_msg_data);
4086
4087 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4088 ROLLBACK;
4089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4090 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4091 p_count => x_msg_count,
4092 p_data => x_msg_data);
4093
4094 WHEN OTHERS THEN
4095 ROLLBACK;
4096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4097 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4098 THEN
4099 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
4100 END IF;
4101 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
4102 p_count => x_msg_count,
4103 p_data => x_msg_data);
4104 end create_process_owner_grant;
4105
4106 -- *******************************************************--
4107
4108 procedure delete_activities(p_parent_process_id in number,
4109 p_child_id_string in varchar2,
4110 x_return_status out nocopy varchar2,
4111 x_msg_count out nocopy number,
4112 x_msg_data out nocopy varchar2)
4113 is
4114 l_api_name constant varchar2(30) := 'delete_activities';
4115 p_init_msg_list varchar2(10) := FND_API.G_TRUE;
4116 str varchar2(4000);
4117 diff number;
4118 childstr varchar2(100);
4119 l_child_string varchar2(4000);
4120 l_child_id number;
4121
4122 begin
4123 x_return_status := FND_API.G_RET_STS_SUCCESS;
4124 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4125 FND_MSG_PUB.initialize;
4126 END IF;
4127 IF FND_GLOBAL.User_Id IS NULL THEN
4128 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
4129 RAISE FND_API.G_EXC_ERROR;
4130 END IF;
4131
4132 l_child_string := p_child_id_string;
4133 while LENGTH(l_child_string) <> 0 loop
4134 select LTRIM(l_child_string, '1234567890') into str from dual;
4135 diff := LENGTH(l_child_string) - LENGTH(str);
4136 if LENGTH(str) is null then
4137 diff := LENGTH(l_child_string);
4138 end if;
4139 select SUBSTR(l_child_string, 1, diff) into childstr from dual;
4140 l_child_id := to_number(childstr);
4141 delete from amw_latest_hierarchies where parent_id = p_parent_process_id
4142 and child_id = l_child_id and organization_id = -1;
4143 select LTRIM(str, 'x') into l_child_string from dual;
4144 end loop;
4145 exception
4146 WHEN FND_API.G_EXC_ERROR THEN
4147 ROLLBACK;
4148 x_return_status := FND_API.G_RET_STS_ERROR;
4149 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
4150
4151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4152 ROLLBACK;
4153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4154 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
4155
4156 WHEN OTHERS THEN
4157 ROLLBACK;
4158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4159 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4160 THEN
4161 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
4162 END IF;
4163 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
4164
4165 end delete_activities;
4166
4167
4168 --The parent process and the child process both exist as ICM processes
4169 procedure add_activities( p_parent_process_id in number,
4170 p_child_id_string in varchar2,
4171 p_sysdate in Date default sysdate,
4172 x_return_status out nocopy varchar2,
4173 x_msg_count out nocopy number,
4174 x_msg_data out nocopy varchar2)
4175 is
4176 l_api_name constant varchar2(30) := 'add_activities';
4177 l_dummy pls_integer;
4178 l_approval_status amw_process.approval_status%type;
4179 l_child_order_number amw_latest_hierarchies.child_order_number%type;
4180 p_init_msg_list varchar2(10) := FND_API.G_TRUE;
4181 str varchar2(4000);
4182 diff number;
4183 childstr varchar2(100);
4184 l_child_string varchar2(4000);
4185 l_child_id number;
4186
4187 begin
4188 x_return_status := FND_API.G_RET_STS_SUCCESS;
4189 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4190 FND_MSG_PUB.initialize;
4191 END IF;
4192 if FND_GLOBAL.user_id is null then
4193 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
4194 raise FND_API.G_EXC_ERROR;
4195 end if;
4196
4197 --check if parent_process_id is null
4198 if p_parent_process_id is null then
4199 raise FND_API.G_EXC_ERROR;
4200 end if;
4201
4202 --check if parent process is locked
4203 if (is_locked_process(p_parent_process_id))
4204 then
4205 raise amw_process_locked_exception;
4206 end if;
4207
4208 l_child_string := p_child_id_string;
4209 while LENGTH(l_child_string) <> 0 loop
4210 select LTRIM(l_child_string, '1234567890') into str from dual;
4211 diff := LENGTH(l_child_string) - LENGTH(str);
4212 if LENGTH(str) is null then
4213 diff := LENGTH(l_child_string);
4214 end if;
4215 select SUBSTR(l_child_string, 1, diff) into childstr from dual;
4216 l_child_id := to_number(childstr);
4217 /* check if the child is an ancestor of parent already
4218 * so that a circular hierarchy cannot be created
4219 */
4220 if(is_ancestor_in_hierarchy(l_child_id,p_parent_process_id))
4221 then
4222 raise amw_circularity_exception;
4223 end if;
4224
4225 --we now check if this link is already existing in the latest hierarchy
4226
4227 open c_link_exist(p_parent_process_id,l_child_id);
4228 fetch c_link_exist into l_dummy;
4229 if(c_link_exist%found)
4230 then
4231 close c_link_exist;
4232 return;
4233 end if;
4234 close c_link_exist;
4235
4236
4237 --update the latest hierarchy table
4238 insert into amw_latest_hierarchies(ORGANIZATION_ID,
4239 PARENT_ID,
4240 CHILD_ID,
4241 CHILD_ORDER_NUMBER,
4242 LAST_UPDATE_DATE,
4243 LAST_UPDATED_BY,
4244 LAST_UPDATE_LOGIN,
4245 CREATION_DATE,
4246 CREATED_BY,
4247 OBJECT_VERSION_NUMBER
4248 )
4249 VALUES (-1,
4250 p_parent_process_id,
4251 l_child_id,
4252 AMW_CHILD_ORDER_S.nextval,
4253 p_sysdate,
4254 g_user_id,
4255 g_login_id,
4256 p_sysdate,
4257 g_user_id,
4258 1)
4259 returning CHILD_ORDER_NUMBER
4260 into l_child_order_number;
4261
4262
4263 update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id, l_child_id,
4264 l_child_order_number);
4265
4266
4267 select LTRIM(str, 'x') into l_child_string from dual;
4268 end loop;
4269
4270 exception
4271 when FND_API.G_EXC_ERROR then
4272 ROLLBACK;
4273 x_return_status := FND_API.G_RET_STS_ERROR;
4274 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4275 p_count => x_msg_count,
4276 p_data => x_msg_data);
4277 when FND_API.G_EXC_UNEXPECTED_ERROR then
4278 ROLLBACK;
4279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4280 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4281 p_count => x_msg_count,
4282 p_data => x_msg_data);
4283 when amw_process_locked_exception then
4284 x_return_status := FND_API.G_RET_STS_ERROR;
4285 x_msg_count := x_msg_count + 1;
4286 fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
4287 x_msg_data := fnd_message.get;
4288 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4289 p_procedure_name => l_api_name,
4290 p_error_text => x_msg_data);
4291 raise FND_API.G_EXC_ERROR;
4292 when amw_circularity_exception then
4293 x_return_status := FND_API.G_RET_STS_ERROR;
4294 fnd_message.set_name('AMW','AMW_CIRCULARITY_CREATION');
4295 x_msg_count := x_msg_count + 1;
4296 x_msg_data := fnd_message.get;
4297 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4298 p_procedure_name => l_api_name,
4299 p_error_text => x_msg_count);
4300 raise FND_API.G_EXC_ERROR;
4301 when amw_processing_exception then
4302 ROLLBACK;
4303 x_return_status := FND_API.G_RET_STS_ERROR;
4304 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4305 p_count => x_msg_count,
4306 p_data => x_msg_data);
4307 when OTHERS then
4308 ROLLBACK;
4309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4310 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4311 p_count => x_msg_count,
4312 p_data => x_msg_data);
4313
4314 end add_activities;
4315
4316 procedure revise_process(p_process_id in number,
4317 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4318 x_return_status out nocopy varchar2,
4319 x_msg_count out nocopy number,
4320 x_msg_data out nocopy varchar2)
4321 is
4322 begin
4323 G_USER_ID := FND_GLOBAL.USER_ID;
4324 G_LOGIN_ID := FND_GLOBAL.CONC_LOGIN_ID;
4325 x_return_status := FND_API.G_RET_STS_SUCCESS;
4326 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4327 FND_MSG_PUB.initialize;
4328 END IF;
4329 IF FND_GLOBAL.User_Id IS NULL THEN
4330 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
4331 RAISE FND_API.G_EXC_ERROR;
4332 END IF;
4333
4334 revise_process_if_necessary(p_process_id => p_process_id);
4335
4336 exception
4337 when OTHERS then
4338 ROLLBACK;
4339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4340 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4341 p_count => x_msg_count,
4342 p_data => x_msg_data);
4343 end revise_process;
4344
4345 PROCEDURE update_latest_denorm_counts
4346 ( p_process_id IN NUMBER,
4347 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4348 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4349 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4350 x_return_status OUT NOCOPY VARCHAR2,
4351 x_msg_count OUT NOCOPY VARCHAR2,
4352 x_msg_data OUT NOCOPY VARCHAR2)
4353 IS
4354
4355 L_API_NAME CONSTANT VARCHAR2(30) := 'update_latest_denorm_counts';
4356
4357
4358 BEGIN
4359
4360 --always initialize global variables in th api's used from SelfSerivice Fwk..
4361 G_USER_ID := FND_GLOBAL.USER_ID;
4362 G_LOGIN_ID := FND_GLOBAL.CONC_LOGIN_ID;
4363 x_return_status := FND_API.G_RET_STS_SUCCESS;
4364 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4365 FND_MSG_PUB.initialize;
4366 END IF;
4367 IF FND_GLOBAL.User_Id IS NULL THEN
4368 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
4369 RAISE FND_API.G_EXC_ERROR;
4370 END IF;
4371
4372 -- update the latest denorm hierarchy..
4373 AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => -1);
4374 -- Update the Risk Counts..
4375 update_latest_risk_counts(p_process_id => p_process_id);
4376 -- Update the Control Counts..
4377 update_latest_control_counts( p_process_id => p_process_id);
4378
4379
4380 exception
4381 WHEN FND_API.G_EXC_ERROR THEN
4382 ROLLBACK;
4383 x_return_status := FND_API.G_RET_STS_ERROR;
4384 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
4385
4386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4387 ROLLBACK;
4388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4389 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
4390
4391 WHEN OTHERS THEN
4392 ROLLBACK;
4393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4394 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4395 THEN
4396 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
4397 END IF;
4398 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
4399 END update_latest_denorm_counts;
4400
4401 end AMW_RL_HIERARCHY_PKG;
[Home] [Help]