[Home] [Help]
9:
10: -- have to define these as global because of recursion.
11: -- make sure to free the memory after use
12:
13: type t_parent_name IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
14: v_parent_name t_parent_name;
15: type t_child_name IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
16: v_child_name t_child_name;
17: type t_transition_parent_list IS table of wf_activity_transitions.from_process_activity%type INDEX BY BINARY_INTEGER;
11: -- make sure to free the memory after use
12:
13: type t_parent_name IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
14: v_parent_name t_parent_name;
15: type t_child_name IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
16: v_child_name t_child_name;
17: type t_transition_parent_list IS table of wf_activity_transitions.from_process_activity%type INDEX BY BINARY_INTEGER;
18: v_transition_parent_list t_transition_parent_list;
19: type t_org_parent_id IS table of Amw_Process_Org_Relations.parent_process_id%type INDEX BY BINARY_INTEGER;
15: type t_child_name IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
16: v_child_name t_child_name;
17: type t_transition_parent_list IS table of wf_activity_transitions.from_process_activity%type INDEX BY BINARY_INTEGER;
18: v_transition_parent_list t_transition_parent_list;
19: type t_org_parent_id IS table of Amw_Process_Org_Relations.parent_process_id%type INDEX BY BINARY_INTEGER;
20: v_org_parent_id t_org_parent_id;
21: type t_org_child_id IS table of Amw_Process_Org_Relations.child_process_id%type INDEX BY BINARY_INTEGER;
22: v_org_child_id t_org_child_id;
23:
17: type t_transition_parent_list IS table of wf_activity_transitions.from_process_activity%type INDEX BY BINARY_INTEGER;
18: v_transition_parent_list t_transition_parent_list;
19: type t_org_parent_id IS table of Amw_Process_Org_Relations.parent_process_id%type INDEX BY BINARY_INTEGER;
20: v_org_parent_id t_org_parent_id;
21: type t_org_child_id IS table of Amw_Process_Org_Relations.child_process_id%type INDEX BY BINARY_INTEGER;
22: v_org_child_id t_org_child_id;
23:
24: type t_parent_id IS table of amw_process.process_id%type;
25: v_parent_id t_parent_id;
20: v_org_parent_id t_org_parent_id;
21: type t_org_child_id IS table of Amw_Process_Org_Relations.child_process_id%type INDEX BY BINARY_INTEGER;
22: v_org_child_id t_org_child_id;
23:
24: type t_parent_id IS table of amw_process.process_id%type;
25: v_parent_id t_parent_id;
26: type t_child_id IS table of amw_process.process_id%type;
27: v_child_id t_child_id;
28:
22: v_org_child_id t_org_child_id;
23:
24: type t_parent_id IS table of amw_process.process_id%type;
25: v_parent_id t_parent_id;
26: type t_child_id IS table of amw_process.process_id%type;
27: v_child_id t_child_id;
28:
29: oldCountProc number := 0;
30: oldCountProcDown number := 0;
44: ------------------------------------------------------------------------------------------------------------
45: ------------------------------------------------------------------------------------------------------------
46:
47:
48: procedure write_amw_process (
49: p_process_name IN VARCHAR2,
50: p_SIGNIFICANT_PROCESS_FLAG IN VARCHAR2,
51: p_STANDARD_PROCESS_FLAG IN VARCHAR2,
52: p_APPROVAL_STATUS IN VARCHAR2,
62: x_msg_count out nocopy number,
63: x_msg_data out nocopy varchar2
64: ) is
65:
66: L_API_NAME CONSTANT VARCHAR2(30) := 'write_amw_process';
67:
68: -- 8.1.7 compatibility issue
69: /*
70: type t_name IS table of amw_process.name%type INDEX BY varchar2(30);
66: L_API_NAME CONSTANT VARCHAR2(30) := 'write_amw_process';
67:
68: -- 8.1.7 compatibility issue
69: /*
70: type t_name IS table of amw_process.name%type INDEX BY varchar2(30);
71: v_name t_name;
72:
73: type t_amwp_name is table of amw_process.name%type;
74: v_amwp_name t_amwp_name;
69: /*
70: type t_name IS table of amw_process.name%type INDEX BY varchar2(30);
71: v_name t_name;
72:
73: type t_amwp_name is table of amw_process.name%type;
74: v_amwp_name t_amwp_name;
75: */
76: -- 8.1.7 compatibility issue
77: /*
74: v_amwp_name t_amwp_name;
75: */
76: -- 8.1.7 compatibility issue
77: /*
78: type t_final_list IS table of amw_process.name%type INDEX BY varchar2(30);
79: v_final_list t_final_list;
80: */
81: type t_forall_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
82: v_forall_list t_forall_list;
77: /*
78: type t_final_list IS table of amw_process.name%type INDEX BY varchar2(30);
79: v_final_list t_final_list;
80: */
81: type t_forall_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
82: v_forall_list t_forall_list;
83:
84: type t_forall_final_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
85: v_forall_final_list t_forall_final_list;
80: */
81: type t_forall_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
82: v_forall_list t_forall_list;
83:
84: type t_forall_final_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
85: v_forall_final_list t_forall_final_list;
86:
87: -- 8.1.7 compatibility issue
88: /*
87: -- 8.1.7 compatibility issue
88: /*
89: cursor c_amwp_name is
90: select name
91: from amw_process;
92: */
93:
94: hash_value NUMBER;
95: v_index NUMBER;
155: begin
156: xst := false;
157: select 1
158: into l_dummy
159: from amw_process
160: where name = v_child_name(i);
161: xst := true;
162: exception
163: when no_data_found then
185: begin
186: xst := false;
187: select 1
188: into l_dummy
189: from amw_process
190: where name = v_parent_name(j);
191: xst := true;
192: exception
193: when no_data_found then
245:
246:
247: --FORALL v_ind IN v_forall_list.FIRST..v_forall_list.LAST
248: FORALL v_ind IN 1..final_row_cnt
249: INSERT INTO amw_process( PROCESS_REV_ID,
250: PROCESS_ID,
251: SIGNIFICANT_PROCESS_FLAG,
252: STANDARD_PROCESS_FLAG,
253: APPROVAL_STATUS,
264: OBJECT_VERSION_NUMBER,
265: ITEM_TYPE,
266: NAME )
267: VALUES
268: (AMW_PROCESS_S.nextval,
269: AMW_PROCESS_S.nextval,
270: p_SIGNIFICANT_PROCESS_FLAG,
271: l_std_process_flag,
272: p_APPROVAL_STATUS,
265: ITEM_TYPE,
266: NAME )
267: VALUES
268: (AMW_PROCESS_S.nextval,
269: AMW_PROCESS_S.nextval,
270: p_SIGNIFICANT_PROCESS_FLAG,
271: l_std_process_flag,
272: p_APPROVAL_STATUS,
273: p_CERTIFICATION_STATUS,
291: child_num := 0;
292: --v_name.delete;
293: --v_amwp_name.trim;
294:
295: synch_hierarchy_amw_process( l_return_status, l_msg_count, l_msg_data);
296:
297: exception
298:
299: WHEN amw_deadlock_detected THEN
319: FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
320: END IF;
321: FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
322:
323: end write_amw_process;
324:
325:
326: ------------------------------------------------------------------------------------------------------------
327: ------------------------------------------------------------------------------------------------------------
373: ------------------------------------------------------------------------------------------------------------
374: ------------------------------------------------------------------------------------------------------------
375:
376:
377: procedure synch_hierarchy_amw_process( x_return_status out nocopy varchar2,
378: x_msg_count out nocopy number,
379: x_msg_data out nocopy varchar2)
380: is
381: begin
382: x_return_status := 'S';
383: x_msg_count := 0;
384: x_msg_data := null;
385: null;
386: end synch_hierarchy_amw_process;
387:
388:
389: ------------------------------------------------------------------------------------------------------------
390: ------------------------------------------------------------------------------------------------------------
390: ------------------------------------------------------------------------------------------------------------
391:
392:
393: /*
394: ** procedure synch_hierarchy_amw_process(errbuf OUT NOCOPY VARCHAR2,
395: ** retcode OUT NOCOPY NUMBER)
396: ** is
397: **
398: ** type t_amwp_name is table of amw_process.name%type;
394: ** procedure synch_hierarchy_amw_process(errbuf OUT NOCOPY VARCHAR2,
395: ** retcode OUT NOCOPY NUMBER)
396: ** is
397: **
398: ** type t_amwp_name is table of amw_process.name%type;
399: ** v_amwp_name t_amwp_name;
400: ** type t_delete_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
401: ** v_delete_list t_delete_list;
402: ** type t_child_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
396: ** is
397: **
398: ** type t_amwp_name is table of amw_process.name%type;
399: ** v_amwp_name t_amwp_name;
400: ** type t_delete_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
401: ** v_delete_list t_delete_list;
402: ** type t_child_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
403: ** v_child_list t_child_list;
404: **
398: ** type t_amwp_name is table of amw_process.name%type;
399: ** v_amwp_name t_amwp_name;
400: ** type t_delete_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
401: ** v_delete_list t_delete_list;
402: ** type t_child_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
403: ** v_child_list t_child_list;
404: **
405: ** root_process_name amw_process.name%type;
406: ** v_index NUMBER;
401: ** v_delete_list t_delete_list;
402: ** type t_child_list IS table of amw_process.name%type INDEX BY BINARY_INTEGER;
403: ** v_child_list t_child_list;
404: **
405: ** root_process_name amw_process.name%type;
406: ** v_index NUMBER;
407: ** xst BOOLEAN;
408: ** delete_row_cnt NUMBER;
409: ** conc_status BOOLEAN;
409: ** conc_status BOOLEAN;
410: **
411: ** cursor c_amwp_name is
412: ** select name
413: ** from amw_process;
414: **
415: ** begin
416: **
417: ** retcode := 0;
426: **
427: **
428: ** select name
429: ** into root_process_name
430: ** from amw_process
431: ** where process_id = -1;
432: **
433: ** find_hierarchy_children(root_process_name);
434: **
458: ** end loop;
459: **
460: **
461: ** FORALL v_ind IN 1..delete_row_cnt
462: ** DELETE from amw_process
463: ** where name = v_delete_list(v_ind);
464: **
465: **
466: ** v_child_name.delete;
477: ** errbuf := SUBSTR(SQLERRM, 1,240);
478: ** fnd_file.put_line(fnd_file.log,errbuf);
479: ** conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
480: **
481: ** end synch_hierarchy_amw_process;
482: */
483:
484: ------------------------------------------------------------------------------------------------------------
485: ------------------------------------------------------------------------------------------------------------
533: procedure find_org_hierarchy_parent(p_org_id in number, p_process_id in number)
534: is
535: cursor c10 (l_org_id number, l_process_id number) is
536: select PARENT_PROCESS_ID
537: from Amw_Process_Org_Relations
538: where CHILD_PROCESS_ID=l_process_id
539: and organization_id = l_org_id;
540:
541: c10_rec c10%rowtype;
558: procedure find_org_hierarchy_children(p_org_id in number, p_process_id in number)
559: is
560: cursor c11 (l_org_id number, l_process_id number) is
561: select CHILD_PROCESS_ID
562: from Amw_Process_Org_Relations
563: where PARENT_PROCESS_ID=l_process_id
564: and organization_id = l_org_id;
565:
566: c11_rec c11%rowtype;
583:
584: procedure reset_process_risk_ctrl_count is
585:
586: type rec_amw_counts IS record
587: (process_id amw_process.process_id%type,
588: no_assoc_risks amw_process.risk_count%type,
589: no_assoc_controls amw_process.control_count%type,
590: risk_count amw_process.risk_count%type,
591: control_count amw_process.control_count%type);
584: procedure reset_process_risk_ctrl_count is
585:
586: type rec_amw_counts IS record
587: (process_id amw_process.process_id%type,
588: no_assoc_risks amw_process.risk_count%type,
589: no_assoc_controls amw_process.control_count%type,
590: risk_count amw_process.risk_count%type,
591: control_count amw_process.control_count%type);
592:
585:
586: type rec_amw_counts IS record
587: (process_id amw_process.process_id%type,
588: no_assoc_risks amw_process.risk_count%type,
589: no_assoc_controls amw_process.control_count%type,
590: risk_count amw_process.risk_count%type,
591: control_count amw_process.control_count%type);
592:
593:
586: type rec_amw_counts IS record
587: (process_id amw_process.process_id%type,
588: no_assoc_risks amw_process.risk_count%type,
589: no_assoc_controls amw_process.control_count%type,
590: risk_count amw_process.risk_count%type,
591: control_count amw_process.control_count%type);
592:
593:
594: v_rec_amw_counts rec_amw_counts;
587: (process_id amw_process.process_id%type,
588: no_assoc_risks amw_process.risk_count%type,
589: no_assoc_controls amw_process.control_count%type,
590: risk_count amw_process.risk_count%type,
591: control_count amw_process.control_count%type);
592:
593:
594: v_rec_amw_counts rec_amw_counts;
595: type t_amw_counts IS table of rec_amw_counts index by binary_integer;
595: type t_amw_counts IS table of rec_amw_counts index by binary_integer;
596: v_amw_counts t_amw_counts;
597:
598: cursor c_populate_amw_counts is
599: select process_id, risk_count, control_count, 0, 0 from amw_process;
600:
601: v_index NUMBER;
602: l_risk_count number;
603: l_control_count number;
604: insert_row_cnt number;
605:
606: begin
607:
608: -- updating every process in amw_process with its risk/ctrl count
609:
610: update amw_process ap
611: set risk_count =
612: (select count(ara.risk_id)
606: begin
607:
608: -- updating every process in amw_process with its risk/ctrl count
609:
610: update amw_process ap
611: set risk_count =
612: (select count(ara.risk_id)
613: from amw_risk_associations ara
614: where pk1 = ap.process_id
636: loop
637:
638: select nvl(sum(risk_count), 0), nvl(sum(control_count), 0)
639: into l_risk_count, l_control_count
640: from amw_process amwp, Amw_Proc_Hierarchy_Denorm apdenorm
641: where apdenorm.process_id = v_index
642: and apdenorm.up_down_ind = 'D'
643: and amwp.process_id = apdenorm.parent_child_id;
644:
651:
652: v_index := v_amw_counts.first;
653: while v_index <= v_amw_counts.last
654: loop
655: update amw_process
656: set risk_count = v_amw_counts(v_index).risk_count,
657: control_count = v_amw_counts(v_index).control_count
658: where process_id = v_index;
659:
674:
675: procedure reset_proc_org_risk_ctrl_count( p_org_id IN NUMBER) is
676:
677: type rec_amw_porg_counts IS record
678: (process_id amw_process_organization.process_id%type,
679: no_assoc_risks amw_process_organization.risk_count%type,
680: no_assoc_controls amw_process_organization.control_count%type,
681: risk_count amw_process_organization.risk_count%type,
682: control_count amw_process_organization.control_count%type);
675: procedure reset_proc_org_risk_ctrl_count( p_org_id IN NUMBER) is
676:
677: type rec_amw_porg_counts IS record
678: (process_id amw_process_organization.process_id%type,
679: no_assoc_risks amw_process_organization.risk_count%type,
680: no_assoc_controls amw_process_organization.control_count%type,
681: risk_count amw_process_organization.risk_count%type,
682: control_count amw_process_organization.control_count%type);
683:
676:
677: type rec_amw_porg_counts IS record
678: (process_id amw_process_organization.process_id%type,
679: no_assoc_risks amw_process_organization.risk_count%type,
680: no_assoc_controls amw_process_organization.control_count%type,
681: risk_count amw_process_organization.risk_count%type,
682: control_count amw_process_organization.control_count%type);
683:
684:
677: type rec_amw_porg_counts IS record
678: (process_id amw_process_organization.process_id%type,
679: no_assoc_risks amw_process_organization.risk_count%type,
680: no_assoc_controls amw_process_organization.control_count%type,
681: risk_count amw_process_organization.risk_count%type,
682: control_count amw_process_organization.control_count%type);
683:
684:
685: v_rec_amw_porg_counts rec_amw_porg_counts;
678: (process_id amw_process_organization.process_id%type,
679: no_assoc_risks amw_process_organization.risk_count%type,
680: no_assoc_controls amw_process_organization.control_count%type,
681: risk_count amw_process_organization.risk_count%type,
682: control_count amw_process_organization.control_count%type);
683:
684:
685: v_rec_amw_porg_counts rec_amw_porg_counts;
686: type t_amw_porg_counts IS table of rec_amw_porg_counts index by binary_integer;
687: v_amw_porg_counts t_amw_porg_counts;
688:
689: cursor c_populate_amw_org_counts(l_org_id number) is
690: select process_id, risk_count, control_count, 0, 0
691: from amw_process_organization
692: where organization_id = l_org_id
693: and end_date is null;
694:
695: cursor c_org(p_org_id number) is
692: where organization_id = l_org_id
693: and end_date is null;
694:
695: cursor c_org(p_org_id number) is
696: select distinct organization_id from amw_process_organization where organization_id = nvl(p_org_id, organization_id) and end_date is null;
697:
698: l_org number;
699: v_index NUMBER;
700: l_risk_count number;
701: l_control_count number;
702: insert_row_cnt number;
703:
704: begin
705: update amw_process_organization apo
706: set risk_count =
707: (select count(ara.risk_id)
708: from amw_risk_associations ara
709: where pk1 = apo.process_organization_id
736: loop
737:
738: select nvl(sum(risk_count), 0), nvl(sum(control_count), 0)
739: into l_risk_count, l_control_count
740: from amw_process_organization amwp, Amw_Org_Hierarchy_Denorm aodenorm
741: where aodenorm.organization_id = l_org
742: and aodenorm.process_id = v_index
743: and aodenorm.up_down_ind = 'D'
744: and amwp.process_id = aodenorm.parent_child_id
755: v_index := v_amw_porg_counts.first;
756: while v_index <= v_amw_porg_counts.last
757: loop
758:
759: update amw_process_organization
760: set risk_count = v_amw_porg_counts(v_index).risk_count,
761: control_count = v_amw_porg_counts(v_index).control_count
762: where process_id = v_index
763: and organization_id = l_org
794:
795: procedure populate_flatlist(p_org_id in number) is
796:
797: cursor c11(l_org_id number) is
798: select process_id from amw_process_organization where organization_id = l_org_id and end_date is null;
799:
800: type t_apo_id is table of amw_process_organization.process_id%type;
801: v_apo_id t_apo_id;
802:
796:
797: cursor c11(l_org_id number) is
798: select process_id from amw_process_organization where organization_id = l_org_id and end_date is null;
799:
800: type t_apo_id is table of amw_process_organization.process_id%type;
801: v_apo_id t_apo_id;
802:
803: v_index number;
804: v_indexDown number;
937: ------------------------------------------------------------------------------------------------------------
938: ------------------------------------------------------------------------------------------------------------
939:
940:
941: -- get the process hierarchy IN AMW (i.e. the process should exist in amw_process) and
942: -- populate Amw_Process_Org_Relations
943: procedure create_org_relations( p_process_name in varchar2,
944: p_org_id in number,
945: x_return_status out nocopy varchar2,
938: ------------------------------------------------------------------------------------------------------------
939:
940:
941: -- get the process hierarchy IN AMW (i.e. the process should exist in amw_process) and
942: -- populate Amw_Process_Org_Relations
943: procedure create_org_relations( p_process_name in varchar2,
944: p_org_id in number,
945: x_return_status out nocopy varchar2,
946: x_msg_count out nocopy number,
947: x_msg_data out nocopy varchar2)
948: is
949: cursor c1 (l_name varchar2) is
950: select child_nondisp_name
951: from amw_process_hierarchy_v
952: where parent_nondisp_name=l_name;
953:
954: c1_rec c1%rowtype;
955: parent_id number;
961: -- much scope for performance improvement lies here. Too many sql's being fired.
962: for c1_rec in c1(p_process_name) loop
963: exit when c1%notfound;
964:
965: select process_id into parent_id from amw_process where name = p_process_name;
966: select process_id into child_id from amw_process where name = c1_rec.child_nondisp_name;
967:
968: select wpa.instance_id
969: into instance_id
962: for c1_rec in c1(p_process_name) loop
963: exit when c1%notfound;
964:
965: select process_id into parent_id from amw_process where name = p_process_name;
966: select process_id into child_id from amw_process where name = c1_rec.child_nondisp_name;
967:
968: select wpa.instance_id
969: into instance_id
970: from wf_process_activities wpa,
982: begin
983:
984: select 1
985: into l_dummy
986: from Amw_Process_Org_Relations
987: where ORGANIZATION_ID = p_org_id
988: and PARENT_PROCESS_ID = parent_id
989: and CHILD_PROCESS_ID = child_id;
990:
990:
991: exception
992: when no_data_found then
993:
994: insert into Amw_Process_Org_Relations
995: (ORGANIZATION_ID,
996: PARENT_PROCESS_ID,
997: CHILD_PROCESS_ID,
998: INSTANCE_ID,
1148: assoc_process_org_hier(p_process_id, p_org_id, -1, l_return_status, l_msg_count, l_msg_data);
1149:
1150: select 1
1151: into l_dummy
1152: from Amw_Process_Org_Relations
1153: where ORGANIZATION_ID = p_org_id
1154: and PARENT_PROCESS_ID = -1
1155: and CHILD_PROCESS_ID = p_process_id;
1156:
1157: exception
1158: when no_data_found then
1159:
1160:
1161: insert into Amw_Process_Org_Relations
1162: (ORGANIZATION_ID,
1163: PARENT_PROCESS_ID,
1164: CHILD_PROCESS_ID,
1165: INSTANCE_ID,
1185: end;
1186:
1187: select name
1188: into p_name
1189: from amw_process
1190: where process_id = p_process_id;
1191:
1192: create_org_relations(p_name, p_org_id, l_return_status, l_msg_count, l_msg_data);
1193:
1284: begin
1285:
1286: select 1
1287: into l_dummy
1288: from Amw_Process_Org_Relations
1289: where ORGANIZATION_ID = p_org_id
1290: and PARENT_PROCESS_ID = -1
1291: and CHILD_PROCESS_ID = p_process_id;
1292:
1293: exception
1294: when no_data_found then
1295:
1296:
1297: insert into Amw_Process_Org_Relations
1298: (ORGANIZATION_ID,
1299: PARENT_PROCESS_ID,
1300: CHILD_PROCESS_ID,
1301: INSTANCE_ID,
1321: end;
1322:
1323: select name
1324: into p_name
1325: from amw_process
1326: where process_id = p_process_id;
1327:
1328: create_org_relations(p_name, p_org_id, l_return_status, l_msg_count, l_msg_data);
1329:
1376: l_apo_type AMW_PROC_ORG_HIERARCHY_PVT.apo_type;
1377:
1378: cursor c1 (l_pid number, p_oid number) is
1379: select CHILD_PROCESS_ID
1380: from Amw_Process_Org_Relations
1381: where PARENT_PROCESS_ID = l_pid
1382: and organization_id = p_oid;
1383:
1384:
1382: and organization_id = p_oid;
1383:
1384:
1385: begin
1386: delete from Amw_Process_Org_Relations
1387: where ORGANIZATION_ID = p_org_id
1388: and PARENT_PROCESS_ID = p_parent_process_id
1389: and CHILD_PROCESS_ID = p_child_process_id;
1390:
1389: and CHILD_PROCESS_ID = p_child_process_id;
1390:
1391: select count(ORGANIZATION_ID)
1392: into l_exist
1393: from Amw_Process_Org_Relations
1394: where ORGANIZATION_ID = p_org_id
1395: and CHILD_PROCESS_ID = p_child_process_id;
1396:
1397: IF l_exist = 0 THEN
1437: ------------------------------------------------------------------------------------------------------------
1438: ------------------------------------------------------------------------------------------------------------
1439:
1440:
1441: -- get the process hierarchy IN Amw_Process_Org_Relations and fire atomic
1442: -- disassociate api for each.
1443: procedure disassociate_process_org(
1444: p_process_id in number,
1445: p_org_id in number,
1468: -- first disassociate the process itself, then loop through the org hierarchy
1469: l_apo_type.process_id := p_process_id;
1470: l_apo_type.organization_id := p_org_id;
1471:
1472: delete from Amw_Process_Org_Relations
1473: where ORGANIZATION_ID = p_org_id
1474: and CHILD_PROCESS_ID = p_process_id;
1475:
1476:
1486: x_msg_data => x_msg_data );
1487:
1488: disassoc_proc_org_hier(p_process_id =>p_process_id, p_org_id => p_org_id);
1489:
1490: delete from amw_process_organization
1491: where organization_id = p_org_id
1492: and end_date is not null;
1493:
1494: populate_flatlist(p_org_id);
1532: procedure disassoc_proc_org_hier(p_process_id in number, p_org_id in number) is
1533:
1534: cursor c1 (l_id number, l_oid number) is
1535: select CHILD_PROCESS_ID
1536: from Amw_Process_Org_Relations
1537: where PARENT_PROCESS_ID=l_id
1538: and organization_id = l_oid;
1539:
1540: c1_rec c1%rowtype;
1549: -- loop through the org hierarchy and disassociate the children
1550: for c1_rec in c1(p_process_id, p_org_id) loop
1551: exit when c1%notfound;
1552:
1553: delete from Amw_Process_Org_Relations
1554: where ORGANIZATION_ID = p_org_id
1555: and PARENT_PROCESS_ID = p_process_id
1556: and CHILD_PROCESS_ID = c1_rec.CHILD_PROCESS_ID;
1557:
1641: where party_id = p_process_owner_party_id;
1642:
1643: select watl.display_name
1644: into l_parent_disp_name
1645: from wf_activities_tl watl, wf_activities wa, amw_process ap
1646: where ap.process_id = p_parent_process_id
1647: and ap.name = wa.name
1648: and wa.item_type = 'AUDITMGR'
1649: and wa.end_date is null
1653: and watl.language = userenv('LANG');
1654:
1655: select watl.display_name
1656: into l_child_disp_name
1657: from wf_activities_tl watl, wf_activities wa, amw_process ap
1658: where ap.process_id = p_child_process_id
1659: and ap.name = wa.name
1660: and wa.item_type = 'AUDITMGR'
1661: and wa.end_date is null
1681: begin
1682:
1683: select 1
1684: into l_dummy
1685: from Amw_Process_Org_Relations
1686: where ORGANIZATION_ID = p_org_id
1687: and PARENT_PROCESS_ID = p_parent_process_id
1688: and CHILD_PROCESS_ID = p_child_process_id;
1689:
1689:
1690: exception
1691: when no_data_found then
1692:
1693: insert into Amw_Process_Org_Relations
1694: (ORGANIZATION_ID,
1695: PARENT_PROCESS_ID,
1696: CHILD_PROCESS_ID,
1697: INSTANCE_ID,
1717: end;
1718:
1719: select name
1720: into p_name
1721: from amw_process
1722: where process_id = p_child_process_id;
1723:
1724: create_org_relations(p_name, p_org_id, l_return_status, l_msg_count, l_msg_data);
1725:
1800:
1801: procedure reset_org_count is
1802: begin
1803:
1804: update amw_process amwp
1805: set amwp.org_count = (select count(process_organization_id)
1806: from amw_process_organization amwpo
1807: where amwpo.process_id = amwp.process_id
1808: and amwpo.end_date is null);
1802: begin
1803:
1804: update amw_process amwp
1805: set amwp.org_count = (select count(process_organization_id)
1806: from amw_process_organization amwpo
1807: where amwpo.process_id = amwp.process_id
1808: and amwpo.end_date is null);
1809: exception
1810: when deadlock_detected then
1817:
1818:
1819:
1820: /*==============================================================+
1821: | This procedure is a wrapper for synch_hierarchy_amw_process |
1822: | To use as a concurrent program. |
1823: +==============================================================*/
1824:
1825:
1822: | To use as a concurrent program. |
1823: +==============================================================*/
1824:
1825:
1826: PROCEDURE sync_hier_amw_process_wrap (
1827: errbuf out nocopy varchar2,
1828: retcode out nocopy varchar2 ) IS
1829:
1830: x_return_status varchar2(10);
1835: BEGIN
1836:
1837: retcode :=0;
1838: errbuf :='';
1839: synch_hierarchy_amw_process( x_return_status => x_return_status ,
1840: x_msg_count => x_msg_count ,
1841: x_msg_data => x_msg_data);
1842:
1843: FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status :' || x_return_status || ':'||x_msg_count||':'||x_msg_data);
1858: ROLLBACK;
1859: RETCODE :=2;
1860: errbuf := SUBSTR(SQLERRM,1,1000);
1861: conc_status :=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error: '|| SQLERRM);
1862: END sync_hier_amw_process_wrap;
1863:
1864:
1865:
1866: /*==============================================================+
2005: l_org_id number;
2006:
2007: cursor c_all_orgs(l_pid number) is
2008: select distinct organization_id
2009: from amw_process_organization
2010: where process_id = l_pid
2011: and end_date is null;
2012:
2013: cursor c_all_exorgs(l_pid number) is
2011: and end_date is null;
2012:
2013: cursor c_all_exorgs(l_pid number) is
2014: select distinct organization_id
2015: from amw_process_organization
2016: where process_id = l_pid
2017: and end_date is null
2018: and organization_id not in
2019: (select distinct old_pk1 from amw_exceptions_b where ((old_pk2 = l_pid and object_type in ('PROCESS','RISK','CONTROL'))or (object_type = 'PROCESS' and old_pk3 = l_pid)) and old_pk1 is not null)
2239: amw_exception exception;
2240:
2241: cursor c_parents(l_org number, l_proc number) is
2242: select parent_process_id
2243: from Amw_Process_Org_Relations
2244: where organization_id = p_org_id
2245: and child_process_id = p_process_id;
2246:
2247: type t_parent_pid is table of amw_process.process_id%type;
2243: from Amw_Process_Org_Relations
2244: where organization_id = p_org_id
2245: and child_process_id = p_process_id;
2246:
2247: type t_parent_pid is table of amw_process.process_id%type;
2248: v_parent_pid t_parent_pid;
2249:
2250: begin
2251:
2282: if l_return_status <> 'S' then
2283: raise amw_exception;
2284: end if;
2285:
2286: insert into Amw_Process_Org_Relations
2287: (ORGANIZATION_ID,
2288: PARENT_PROCESS_ID,
2289: CHILD_PROCESS_ID,
2290: INSTANCE_ID,
2313:
2314:
2315: select name
2316: into l_name
2317: from amw_process
2318: where process_id = p_process_id;
2319:
2320: create_org_relations(l_name, p_org_id, l_return_status, l_msg_count, l_msg_data);
2321:
2356: l_pname varchar2(30);
2357: l_dummy number;
2358: j number;
2359: begin
2360: select name into l_pname from amw_process where process_id = p_process_id;
2361: find_hierarchy_children(l_pname);
2362: j := oldCount1 + 1;
2363: FOR i IN j..v_child_name.count LOOP
2364: begin
2362: j := oldCount1 + 1;
2363: FOR i IN j..v_child_name.count LOOP
2364: begin
2365: select 1 into l_dummy
2366: from amw_process_organization
2367: where end_date is null
2368: and process_id = (select process_id from amw_process where name = v_child_name(i));
2369: oldCount1 := v_child_name.count;
2370: return 1;
2364: begin
2365: select 1 into l_dummy
2366: from amw_process_organization
2367: where end_date is null
2368: and process_id = (select process_id from amw_process where name = v_child_name(i));
2369: oldCount1 := v_child_name.count;
2370: return 1;
2371: exception
2372: when too_many_rows then
2394: begin
2395: x_return_status := FND_API.G_RET_STS_SUCCESS;
2396:
2397: select 1 into l_dummy
2398: from amw_process_organization
2399: where process_id = p_process_id
2400: and end_date is null;
2401:
2402: p_out := 1;
2575: end;
2576:
2577: begin
2578: select 1 into l_dummy
2579: from amw_process_organization
2580: where end_date is null
2581: and organization_id = p_org_id
2582: and process_owner_id = l_party_id;
2583: return 1;
2697: procedure find_amwp_hierarchy_children(p_process_id in number)
2698: is
2699: cursor c1 (l_id number) is
2700: select b2.process_id
2701: from amw_wf_hierarchy_v a, amw_process b1, amw_process b2
2702: where b1.name = a.parent_process_name
2703: and b1.item_type = a.parent_item_type
2704: and b2.name = a.child_process_name
2705: and b2.item_type = a.child_item_type
2724: procedure find_amwp_hierarchy_parent(p_process_id in number)
2725: is
2726: cursor c1 (l_id number) is
2727: select b1.process_id
2728: from amw_wf_hierarchy_v a, amw_process b1, amw_process b2
2729: where b1.name = a.parent_process_name
2730: and b1.item_type = a.parent_item_type
2731: and b2.name = a.child_process_name
2732: and b2.item_type = a.child_item_type
2749: -- abb changed
2750: procedure populate_proc_flatlist is
2751:
2752: cursor c12 is
2753: select process_id from amw_process;
2754:
2755: type t_ap_id is table of amw_process.process_id%type;
2756: v_ap_id t_ap_id;
2757:
2751:
2752: cursor c12 is
2753: select process_id from amw_process;
2754:
2755: type t_ap_id is table of amw_process.process_id%type;
2756: v_ap_id t_ap_id;
2757:
2758: v_index number;
2759: v_indexDown number;
2871: x_msg_count out nocopy number,
2872: x_msg_data out nocopy varchar2)
2873: is
2874:
2875: L_API_NAME CONSTANT VARCHAR2(30) := 'synch_hierarchy_amw_process';
2876: p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
2877:
2878: type t_amwp_name is table of amw_process.name%type;
2879: v_amwp_name t_amwp_name;
2874:
2875: L_API_NAME CONSTANT VARCHAR2(30) := 'synch_hierarchy_amw_process';
2876: p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
2877:
2878: type t_amwp_name is table of amw_process.name%type;
2879: v_amwp_name t_amwp_name;
2880: type t_amwp_pid is table of amw_process.process_id%type;
2881: v_amwp_pid t_amwp_pid;
2882:
2876: p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
2877:
2878: type t_amwp_name is table of amw_process.name%type;
2879: v_amwp_name t_amwp_name;
2880: type t_amwp_pid is table of amw_process.process_id%type;
2881: v_amwp_pid t_amwp_pid;
2882:
2883: type t_org is table of amw_process_organization.organization_id%type;
2884: v_org t_org;
2879: v_amwp_name t_amwp_name;
2880: type t_amwp_pid is table of amw_process.process_id%type;
2881: v_amwp_pid t_amwp_pid;
2882:
2883: type t_org is table of amw_process_organization.organization_id%type;
2884: v_org t_org;
2885:
2886: --type rec_amw IS record
2887: -- (name amw_process.name%type,
2883: type t_org is table of amw_process_organization.organization_id%type;
2884: v_org t_org;
2885:
2886: --type rec_amw IS record
2887: -- (name amw_process.name%type,
2888: -- process_id amw_process.process_id%type);
2889: --v_rec_amw rec_amw;
2890: --type t_amw IS table of rec_amw index by binary_integer;
2891: --v_amw t_amw;
2884: v_org t_org;
2885:
2886: --type rec_amw IS record
2887: -- (name amw_process.name%type,
2888: -- process_id amw_process.process_id%type);
2889: --v_rec_amw rec_amw;
2890: --type t_amw IS table of rec_amw index by binary_integer;
2891: --v_amw t_amw;
2892:
2891: --v_amw t_amw;
2892:
2893: -- 8.1.7 compatibility issue
2894: /*
2895: type t_amwp_name_pid is table of amw_process.process_id%type index by VARCHAR2(30);
2896: v_amwp_name_pid t_amwp_name_pid;
2897: */
2898: -- added by abedajna
2899: /*
2902: type t_wf_name_table IS table of t_wf_name index by binary_integer;
2903: v_wf_name_table t_wf_name_table;
2904: */
2905:
2906: type t_delete_list IS table of amw_process.name%type index by binary_integer;
2907: v_delete_list t_delete_list;
2908:
2909: type t_delete_pid_list IS table of amw_process.process_id%type index by binary_integer;
2910: v_delete_pid_list t_delete_pid_list;
2905:
2906: type t_delete_list IS table of amw_process.name%type index by binary_integer;
2907: v_delete_list t_delete_list;
2908:
2909: type t_delete_pid_list IS table of amw_process.process_id%type index by binary_integer;
2910: v_delete_pid_list t_delete_pid_list;
2911:
2912: -- 8.1.7 compatibility issue
2913: /*
2910: v_delete_pid_list t_delete_pid_list;
2911:
2912: -- 8.1.7 compatibility issue
2913: /*
2914: type t_child_list IS table of amw_process.name%type index by VARCHAR2(30);
2915: v_child_list t_child_list;
2916: */
2917:
2918: root_process_name amw_process.name%type;
2914: type t_child_list IS table of amw_process.name%type index by VARCHAR2(30);
2915: v_child_list t_child_list;
2916: */
2917:
2918: root_process_name amw_process.name%type;
2919: v_index NUMBER;
2920: v_index1 NUMBER;
2921: xst BOOLEAN;
2922: delete_row_cnt NUMBER;
2925: x_msg_data1 varchar2(4000);
2926:
2927: cursor c_amwp_name is
2928: select name, process_id
2929: from amw_process;
2930:
2931: cursor c_org (p_pid number) is
2932: select organization_id
2933: from amw_process_organization
2929: from amw_process;
2930:
2931: cursor c_org (p_pid number) is
2932: select organization_id
2933: from amw_process_organization
2934: where process_id = p_pid;
2935:
2936: cursor exceptions_to_be_del is
2937: select exception_id
2937: select exception_id
2938: from amw_exceptions_b
2939: where object_type = 'PROCESS_VARIANT_ADD'
2940: and new_pk1 not in
2941: (select process_id from amw_process where standard_process_flag = 'Y');
2942:
2943: l_ex_id number;
2944: hvalue number;
2945: v_inner_index number;
2943: l_ex_id number;
2944: hvalue number;
2945: v_inner_index number;
2946:
2947: amw_processing_exception exception;
2948:
2949:
2950: begin
2951: x_return_status := FND_API.G_RET_STS_SUCCESS;
2956: AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
2957: RAISE FND_API.G_EXC_ERROR;
2958: END IF;
2959:
2960: -- go through amw_process, see if the process does NOT exist as a child of ALL Process
2961: -- in wf, in that case, delete the row from amw_process. This is to take care of
2962: -- dangling rows in amw_process.
2963: open c_amwp_name;
2964: loop
2957: RAISE FND_API.G_EXC_ERROR;
2958: END IF;
2959:
2960: -- go through amw_process, see if the process does NOT exist as a child of ALL Process
2961: -- in wf, in that case, delete the row from amw_process. This is to take care of
2962: -- dangling rows in amw_process.
2963: open c_amwp_name;
2964: loop
2965: fetch c_amwp_name bulk collect into v_amwp_name, v_amwp_pid;
2958: END IF;
2959:
2960: -- go through amw_process, see if the process does NOT exist as a child of ALL Process
2961: -- in wf, in that case, delete the row from amw_process. This is to take care of
2962: -- dangling rows in amw_process.
2963: open c_amwp_name;
2964: loop
2965: fetch c_amwp_name bulk collect into v_amwp_name, v_amwp_pid;
2966: exit when c_amwp_name%notfound;
2979: */
2980:
2981: select name
2982: into root_process_name
2983: from amw_process
2984: where process_id = -1;
2985:
2986: find_hierarchy_children(root_process_name);
2987:
3065: loop
3066: -- opportunity for performance improvement exist
3067: select process_id
3068: into v_delete_pid_list(v_index)
3069: from amw_process
3070: where item_type = 'AUDITMGR'
3071: and name = v_delete_list(v_index);
3072: -- v_delete_pid_list(v_index) := v_amwp_name_pid( v_delete_list(v_index) );
3073: v_index := v_delete_list.next(v_index);
3115: x_return_status => x_return_status1,
3116: x_msg_count => x_msg_count1,
3117: x_msg_data => x_msg_data1);
3118:
3119: -- kosriniv begin :- Check the return status, and if error then raise the amw_processing_exception to handle it.
3120: IF x_return_status1 <> FND_API.G_RET_STS_SUCCESS THEN
3121: RAISE amw_processing_exception;
3122: END IF;
3123: -- kosriniv end
3117: x_msg_data => x_msg_data1);
3118:
3119: -- kosriniv begin :- Check the return status, and if error then raise the amw_processing_exception to handle it.
3120: IF x_return_status1 <> FND_API.G_RET_STS_SUCCESS THEN
3121: RAISE amw_processing_exception;
3122: END IF;
3123: -- kosriniv end
3124: v_index1 := v_org.next(v_index1);
3125: end loop;
3136: DELETE from amw_objective_associations
3137: where pk1 = v_delete_pid_list(v_ind)
3138: and object_type = 'PROCESS';
3139:
3140: -- delete from amw_process
3141: FORALL v_ind IN 1..delete_row_cnt
3142: DELETE from amw_process
3143: where name = v_delete_list(v_ind);
3144:
3138: and object_type = 'PROCESS';
3139:
3140: -- delete from amw_process
3141: FORALL v_ind IN 1..delete_row_cnt
3142: DELETE from amw_process
3143: where name = v_delete_list(v_ind);
3144:
3145: -- endate these processes in wf_activities and call wf purge api's to delete them
3146: FORALL v_ind IN 1..delete_row_cnt
3165: v_amwp_name.trim;
3166: v_amwp_pid.trim;
3167:
3168: -- nullify the standard variations that have been deleted
3169: update amw_process ap1
3170: set ap1.standard_variation =
3171: (select ap2.process_id from amw_process ap2 where ap2.process_id = ap1.standard_variation);
3172:
3173: -- delete the corresponding variation exceptions created for them.
3167:
3168: -- nullify the standard variations that have been deleted
3169: update amw_process ap1
3170: set ap1.standard_variation =
3171: (select ap2.process_id from amw_process ap2 where ap2.process_id = ap1.standard_variation);
3172:
3173: -- delete the corresponding variation exceptions created for them.
3174: open exceptions_to_be_del;
3175: loop
3180: delete from amw_exceptions_tl where exception_id = l_ex_id;
3181: end loop;
3182: close exceptions_to_be_del;
3183:
3184: -- delete those process rows from amw_process which have been deleted from wf
3185: delete from amw_process a where not exists
3186: (select name from wf_activities w where w.name = a.name and w.item_type = 'AUDITMGR' and w.end_date is null);
3187:
3188: --populate_proc_flatlist;
3181: end loop;
3182: close exceptions_to_be_del;
3183:
3184: -- delete those process rows from amw_process which have been deleted from wf
3185: delete from amw_process a where not exists
3186: (select name from wf_activities w where w.name = a.name and w.item_type = 'AUDITMGR' and w.end_date is null);
3187:
3188: --populate_proc_flatlist;
3189: --reset_org_count;
3207: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3208: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3209:
3210:
3211: -- kosriniv begin :- Handle amw_processing_exception
3212: WHEN amw_processing_exception THEN
3213: ROLLBACK;
3214: x_return_status := x_return_status1;
3215: x_msg_count := x_msg_count1;
3208: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3209:
3210:
3211: -- kosriniv begin :- Handle amw_processing_exception
3212: WHEN amw_processing_exception THEN
3213: ROLLBACK;
3214: x_return_status := x_return_status1;
3215: x_msg_count := x_msg_count1;
3216: IF x_msg_count = 1 THEN
3233: ------------------------------------------------------------------------------------------------------------
3234: -- DO NOT CALL THIS PROCEDURE FROM ANYWHERE IN AMW.D CODE. THIS IS BEING PROVIDED TO FIX BUG 4306756
3235: -- THIS IS CALLED ONLY FROM AMWDTFIX.SQL
3236:
3237: procedure old_synch_hier_amw_process( x_return_status out nocopy varchar2,
3238: x_msg_count out nocopy number,
3239: x_msg_data out nocopy varchar2)
3240: is
3241:
3238: x_msg_count out nocopy number,
3239: x_msg_data out nocopy varchar2)
3240: is
3241:
3242: L_API_NAME CONSTANT VARCHAR2(30) := 'old_synch_hier_amw_process';
3243: p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
3244:
3245: type t_amwp_name is table of amw_process.name%type;
3246: v_amwp_name t_amwp_name;
3241:
3242: L_API_NAME CONSTANT VARCHAR2(30) := 'old_synch_hier_amw_process';
3243: p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
3244:
3245: type t_amwp_name is table of amw_process.name%type;
3246: v_amwp_name t_amwp_name;
3247: type t_amwp_pid is table of amw_process.process_id%type;
3248: v_amwp_pid t_amwp_pid;
3249:
3243: p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
3244:
3245: type t_amwp_name is table of amw_process.name%type;
3246: v_amwp_name t_amwp_name;
3247: type t_amwp_pid is table of amw_process.process_id%type;
3248: v_amwp_pid t_amwp_pid;
3249:
3250: type t_org is table of amw_process_organization.organization_id%type;
3251: v_org t_org;
3246: v_amwp_name t_amwp_name;
3247: type t_amwp_pid is table of amw_process.process_id%type;
3248: v_amwp_pid t_amwp_pid;
3249:
3250: type t_org is table of amw_process_organization.organization_id%type;
3251: v_org t_org;
3252:
3253: type t_delete_list IS table of amw_process.name%type index by binary_integer;
3254: v_delete_list t_delete_list;
3249:
3250: type t_org is table of amw_process_organization.organization_id%type;
3251: v_org t_org;
3252:
3253: type t_delete_list IS table of amw_process.name%type index by binary_integer;
3254: v_delete_list t_delete_list;
3255:
3256: type t_delete_pid_list IS table of amw_process.process_id%type index by binary_integer;
3257: v_delete_pid_list t_delete_pid_list;
3252:
3253: type t_delete_list IS table of amw_process.name%type index by binary_integer;
3254: v_delete_list t_delete_list;
3255:
3256: type t_delete_pid_list IS table of amw_process.process_id%type index by binary_integer;
3257: v_delete_pid_list t_delete_pid_list;
3258:
3259: root_process_name amw_process.name%type;
3260: v_index NUMBER;
3255:
3256: type t_delete_pid_list IS table of amw_process.process_id%type index by binary_integer;
3257: v_delete_pid_list t_delete_pid_list;
3258:
3259: root_process_name amw_process.name%type;
3260: v_index NUMBER;
3261: v_index1 NUMBER;
3262: xst BOOLEAN;
3263: delete_row_cnt NUMBER;
3266: x_msg_data1 varchar2(4000);
3267:
3268: cursor c_amwp_name is
3269: select name, process_id
3270: from amw_process;
3271:
3272: cursor c_org (p_pid number) is
3273: select organization_id
3274: from amw_process_organization
3270: from amw_process;
3271:
3272: cursor c_org (p_pid number) is
3273: select organization_id
3274: from amw_process_organization
3275: where process_id = p_pid;
3276:
3277: cursor exceptions_to_be_del is
3278: select exception_id
3278: select exception_id
3279: from amw_exceptions_b
3280: where object_type = 'PROCESS_VARIANT_ADD'
3281: and new_pk1 not in
3282: (select process_id from amw_process where standard_process_flag = 'Y');
3283:
3284: l_ex_id number;
3285: hvalue number;
3286: v_inner_index number;
3284: l_ex_id number;
3285: hvalue number;
3286: v_inner_index number;
3287:
3288: amw_processing_exception exception;
3289:
3290:
3291: begin
3292: x_return_status := FND_API.G_RET_STS_SUCCESS;
3297: AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
3298: RAISE FND_API.G_EXC_ERROR;
3299: END IF;
3300:
3301: -- go through amw_process, see if the process does NOT exist as a child of ALL Process
3302: -- in wf, in that case, delete the row from amw_process. This is to take care of
3303: -- dangling rows in amw_process.
3304: open c_amwp_name;
3305: loop
3298: RAISE FND_API.G_EXC_ERROR;
3299: END IF;
3300:
3301: -- go through amw_process, see if the process does NOT exist as a child of ALL Process
3302: -- in wf, in that case, delete the row from amw_process. This is to take care of
3303: -- dangling rows in amw_process.
3304: open c_amwp_name;
3305: loop
3306: fetch c_amwp_name bulk collect into v_amwp_name, v_amwp_pid;
3299: END IF;
3300:
3301: -- go through amw_process, see if the process does NOT exist as a child of ALL Process
3302: -- in wf, in that case, delete the row from amw_process. This is to take care of
3303: -- dangling rows in amw_process.
3304: open c_amwp_name;
3305: loop
3306: fetch c_amwp_name bulk collect into v_amwp_name, v_amwp_pid;
3307: exit when c_amwp_name%notfound;
3309: close c_amwp_name;
3310:
3311: select name
3312: into root_process_name
3313: from amw_process
3314: where process_id = -1;
3315:
3316: find_hierarchy_children(root_process_name);
3317:
3341: while v_index <= v_delete_list.last
3342: loop
3343: select process_id
3344: into v_delete_pid_list(v_index)
3345: from amw_process
3346: where item_type = 'AUDITMGR'
3347: and name = v_delete_list(v_index);
3348: v_index := v_delete_list.next(v_index);
3349: end loop;
3368: x_return_status => x_return_status1,
3369: x_msg_count => x_msg_count1,
3370: x_msg_data => x_msg_data1);
3371:
3372: -- kosriniv begin :- Check the return status, and if error then raise the amw_processing_exception to handle it.
3373: IF x_return_status1 <> FND_API.G_RET_STS_SUCCESS THEN
3374: RAISE amw_processing_exception;
3375: END IF;
3376: -- kosriniv end
3370: x_msg_data => x_msg_data1);
3371:
3372: -- kosriniv begin :- Check the return status, and if error then raise the amw_processing_exception to handle it.
3373: IF x_return_status1 <> FND_API.G_RET_STS_SUCCESS THEN
3374: RAISE amw_processing_exception;
3375: END IF;
3376: -- kosriniv end
3377: v_index1 := v_org.next(v_index1);
3378: end loop;
3389: DELETE from amw_objective_associations
3390: where pk1 = v_delete_pid_list(v_ind)
3391: and object_type = 'PROCESS';
3392:
3393: -- delete from amw_process
3394: FORALL v_ind IN 1..delete_row_cnt
3395: DELETE from amw_process
3396: where name = v_delete_list(v_ind);
3397:
3391: and object_type = 'PROCESS';
3392:
3393: -- delete from amw_process
3394: FORALL v_ind IN 1..delete_row_cnt
3395: DELETE from amw_process
3396: where name = v_delete_list(v_ind);
3397:
3398: -- endate these processes in wf_activities and call wf purge api's to delete them
3399: FORALL v_ind IN 1..delete_row_cnt
3418: v_amwp_name.trim;
3419: v_amwp_pid.trim;
3420:
3421: -- nullify the standard variations that have been deleted
3422: update amw_process ap1
3423: set ap1.standard_variation =
3424: (select ap2.process_id from amw_process ap2 where ap2.process_id = ap1.standard_variation);
3425:
3426: -- delete the corresponding variation exceptions created for them.
3420:
3421: -- nullify the standard variations that have been deleted
3422: update amw_process ap1
3423: set ap1.standard_variation =
3424: (select ap2.process_id from amw_process ap2 where ap2.process_id = ap1.standard_variation);
3425:
3426: -- delete the corresponding variation exceptions created for them.
3427: open exceptions_to_be_del;
3428: loop
3433: delete from amw_exceptions_tl where exception_id = l_ex_id;
3434: end loop;
3435: close exceptions_to_be_del;
3436:
3437: -- delete those process rows from amw_process which have been deleted from wf
3438: delete from amw_process a where not exists
3439: (select name from wf_activities w where w.name = a.name and w.item_type = 'AUDITMGR' and w.end_date is null);
3440:
3441: populate_proc_flatlist;
3434: end loop;
3435: close exceptions_to_be_del;
3436:
3437: -- delete those process rows from amw_process which have been deleted from wf
3438: delete from amw_process a where not exists
3439: (select name from wf_activities w where w.name = a.name and w.item_type = 'AUDITMGR' and w.end_date is null);
3440:
3441: populate_proc_flatlist;
3442: reset_org_count;
3460: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3461: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3462:
3463:
3464: -- kosriniv begin :- Handle amw_processing_exception
3465: WHEN amw_processing_exception THEN
3466: ROLLBACK;
3467: x_return_status := x_return_status1;
3468: x_msg_count := x_msg_count1;
3461: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3462:
3463:
3464: -- kosriniv begin :- Handle amw_processing_exception
3465: WHEN amw_processing_exception THEN
3466: ROLLBACK;
3467: x_return_status := x_return_status1;
3468: x_msg_count := x_msg_count1;
3469: IF x_msg_count = 1 THEN
3479: FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
3480: END IF;
3481: FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
3482:
3483: end old_synch_hier_amw_process;
3484:
3485:
3486:
3487: end AMW_WF_HIERARCHY_PKG;