1 PACKAGE BODY ZPB_WFMNT AS
2 /* $Header: zpbwkfmnt.plb 120.0.12010.2 2006/08/03 18:48:48 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZPB_WFMNT';
5
6 -- Wrapper to call DeleteWorkflow and clean zpb_excp* tables for a BAID
7 Procedure PurgeWF_BusinessArea (p_business_area_id in number)
8 is
9
10 l_thisInst number;
11 l_thisACID number;
12 retcode varchar2(2);
13 errbuf varchar2(100);
14
15 CURSOR c_instances is
16 select instance_ac_id
17 from zpb_analysis_cycle_instances
18 where analysis_cycle_id = l_thisACID;
19
20 v_instance c_instances%ROWTYPE;
21
22 CURSOR c_acids is
23 select ANALYSIS_CYCLE_ID
24 from ZPB_ANALYSIS_CYCLES
25 where BUSINESS_AREA_ID = p_business_area_id;
26
27 v_acid c_acids%ROWTYPE;
28
29
30 BEGIN
31
32 -- for each ACID within each BA abort and delete the EPBCYCLE and ZPBSCHED workflows
33 for v_acid in c_acids loop
34
35 l_thisACID := v_acid.ANALYSIS_CYCLE_ID;
36 ZPB_WFMNT.purge_Workflows (errbuf, retcode, l_thisACID, 'A');
37
38 -- Delete task rows from zpb_excp_results, zpb_exp_explanations by instance
39 for v_instance in c_instances loop
40 l_thisInst := v_instance.instance_ac_id;
41 delete from zpb_excp_results re
42 where re.task_id in (select pd.task_id from zpb_process_details_v pd
43 where analysis_cycle_id = l_thisInst);
44 delete from zpb_excp_explanations ex
45 where ex.task_id in (select pd.task_id from zpb_process_details_v pd
46 where analysis_cycle_id = l_thisInst);
47 end loop;
48
49 end loop;
50
51
52
53 -- The default date setting for exec wf_purge.adhocdirectory is sysdate.
54 -- This will purge out any ad hoc roles or users zpb generated based on the expiration_date
55 -- whcih were set by wf_directory.CreateAdHocRole. This is a standard WF API.
56 wf_purge.adhocdirectory;
57
58
59 return;
60
61 exception
62
63 when others then
64 raise;
65 -- RAISE_APPLICATION_ERROR(-20100, 'Error in ZPB_WF.CallDelWF');
66 end PurgeWF_BusinessArea;
67
68
69 procedure purge_Workflows (errbuf out nocopy varchar2,
70 retcode out nocopy varchar2,
71 p_inACID in Number,
72 ACIDType in varchar2)
73 IS
74 AttrName varchar2(30);
75 CurrStatus varchar2(20);
76 result varchar2(100);
77
78 CURSOR c_ItemKeys is
79 select item_type, item_key
80 from WF_ITEM_ATTRIBUTE_VALUES
81 where (item_type = 'ZPBSCHED' OR item_type = 'EPBCYCLE')
82 and name = AttrName
83 and number_value = p_inACID;
84
85 v_ItemKey c_ItemKeys%ROWTYPE;
86
87
88 CURSOR c_dc_objects is
89 select w.item_key
90 from ZPB_DC_OBJECTS d,
91 WF_ITEM_ATTRIBUTE_VALUES w
92 where analysis_cycle_id = p_inACID
93 and (w.item_type = 'EPBDC')
94 and w.name = 'DC_OBJECT_ID'
95 and w.number_value = d.object_id;
96
97 v_dc_object c_dc_objects%ROWTYPE;
98
99
100 BEGIN
101
102 retcode := '0';
103
104 if ACIDType = 'I' then
105 AttrName := 'INSTANCEID';
106 else
107 AttrName := 'ACID';
108 end if;
109
110 for v_ItemKey in c_ItemKeys loop
111 wf_engine.ItemStatus(v_ItemKey.item_type, v_ItemKey.item_key, currStatus, result);
112
113 if UPPER(RTRIM(currStatus)) = 'COMPLETE' then
114 WF_PURGE.Total(v_ItemKey.item_Type, v_ItemKey.item_key);
115 else
116 WF_ENGINE.AbortProcess(v_ItemKey.item_Type, v_ItemKey.item_key);
117 WF_PURGE.Total(v_ItemKey.item_Type, v_ItemKey.item_key);
118 end if;
119
120 end loop;
121
122 -- To PURGE out the EPBDC objects too!
123 for v_dc_object in c_dc_objects loop
124
125 wf_engine.ItemStatus('EPBDC', v_dc_object.item_key, currStatus, result);
126
127 if UPPER(RTRIM(currStatus)) = 'COMPLETE' then
128 WF_PURGE.Total('EPBDC', v_dc_object.item_key);
129 else
130 WF_ENGINE.AbortProcess('EPBDC', v_dc_object.item_key);
131 WF_PURGE.Total('EPBDC', v_dc_object.item_key);
132 end if;
133
134 end loop;
135
136
137 return;
138
139 exception
140
141 when NO_DATA_FOUND then
142 retcode :='0';
143
144 when others then
145 retcode :='2';
146 errbuf:=substr(sqlerrm, 1, 255);
147
148 end purge_Workflows;
149
150
151
152 end ZPB_WFMNT;
153
[Home] [Help]