[Home] [Help]
PACKAGE BODY: APPS.MST_WORKFLOW_PKG
Source
1 PACKAGE BODY MST_WORKFLOW_PKG AS
2 /* $Header: MSTEXWFB.pls 115.5 2004/08/04 15:16:19 atsrivas noship $ */
3
4 type number_tab_type is table of number index by binary_integer;
5 type varchar2_tab_type is table of varchar2(500) index by binary_integer;
6
7 p_log_message number := 1;
8
9 procedure print_info(p_log_message in number, p_info_str in varchar2);
10
11
12 PROCEDURE launch_workflow (errbuf out nocopy varchar2
13 ,retcode out nocopy number
14 ,p_plan_id in number) IS
15
16 cursor plan_cur (l_plan_id in NUMBER)
17 is
18 select compile_designator
19 from mst_plans
20 where plan_id = l_plan_id;
21
22 l_compile_designator varchar2(100);
23 l_message_prefix varchar2(100);
24
25 cursor StartWFProcess_cur (l_plan_id in NUMBER)
26 is
27 select med.exception_detail_id
28 , med.exception_type
29 , 'EXCEPTION_PROCESS1'
30 , mst_wb_util.get_trip_tokenized_exception(med.plan_id, med.exception_detail_id, med.trip_id1, 1)
31 from mst_excep_preferences mep
32 , mst_exception_details med
33 where mep.exception_type = med.exception_type
34 and mep.user_id = -9999
35 and mep.work_flow_item_type = '1' -- 1 stands for status:enabled
36 and med.plan_id = l_plan_id;
37
38 l_excep_det_id number;
39 l_excep_type number;
40
41 l_excep_det_id_tab number_tab_type;
42 l_excep_type_tab number_tab_type;
43
44 l_workflow_process_tab varchar2_tab_type;
45 l_message_tab varchar2_tab_type;
46 BEGIN
47 -- delete previously generated workflow notifications
48 DeleteActivities(p_plan_id);
49
50 open plan_cur(p_plan_id);
51 fetch plan_cur into l_compile_designator;
52 close plan_cur;
53
54 fnd_message.set_name('MST','MST_STRING_PLAN');
55 --fnd_message.get(l_message_prefix);
56 l_message_prefix := fnd_message.get;
57
58 open StartWFProcess_cur(p_plan_id);
59 fetch StartWFProcess_cur bulk collect into l_excep_det_id_tab, l_excep_type_tab, l_workflow_process_tab, l_message_tab;
60 close StartWFProcess_cur;
61
62 if nvl(l_excep_type_tab.last,0) > 0 then
63 for i in 1..l_excep_type_tab.last loop
64
65 StartWFProcess('MSTEXPWF'
66 , to_char(p_plan_id) || '-' ||to_char(l_excep_det_id_tab(i))
67 , l_message_prefix||' '||l_compile_designator||': '||l_message_tab(i)
68 , l_workflow_process_tab(i));
69 end loop;
70 end if;
71 exception
72 when others then
73 errbuf := 'Error in MST_WORKFLOW_PKG.launch_workflow function SQL error: ' || sqlerrm;
74 -- dbms_output.put_line('inside Exception #'||sqlerrm);
75 print_info(p_log_message, 'inside Exception #'||sqlerrm);
76 retcode := 2;
77
78 END launch_workflow;
79
80 PROCEDURE StartWFProcess ( p_item_type in varchar2 default null
81 , p_item_key in varchar2
82 , p_message in varchar2
83 , p_workflow_process in varchar2) is
84 BEGIN
85
86 wf_engine.CreateProcess( itemtype => p_item_type
87 , itemkey => p_item_key
88 , process => p_workflow_process);
89
90 wf_engine.SetItemAttrText( itemtype => p_item_type
91 , itemkey => p_item_key
92 , aname => 'MST_MSG_BODY'
93 , avalue => p_Message);
94
95 wf_engine.SetItemAttrText( itemtype => p_item_type
96 , itemkey => p_item_key
97 , aname => 'MST_MSG_SUBJECT'
98 , avalue => p_Message);
99
100 wf_engine.StartProcess( itemtype => p_item_type
101 , itemkey => p_item_key);
102
103 END StartWFProcess;
104
105
106
107 PROCEDURE Select_Planner( itemtype in varchar2
108 , itemkey in varchar2
109 , actid in number
110 , funcmode in varchar2
111 , resultout out NOCOPY varchar2) is
112
113 l_msg varchar2(500);
114 l_name varchar2(500);
115
116 BEGIN
117
118 l_name := fnd_global.user_name;
119 --l_name := 'MFG';
120 --l_name := '[email protected]';
121
122 wf_engine.SetItemAttrText( itemtype => itemtype
123 , itemkey => itemkey
124 , aname => 'PLANNER'
125 , avalue => l_name);
126
127 l_msg := GetPlannerMsgName;
128
129 wf_engine.SetItemAttrText( itemtype => itemtype
130 , itemkey => itemkey
131 , aname => 'MST_MESSAGE'
132 , avalue => l_msg);
133
134 resultout := 'COMPLETE:FOUND';
135
136 END Select_Planner;
137
138 FUNCTION GetPlannerMsgName
139 RETURN varchar2 IS
140 BEGIN
141
142 return 'MST_MSG1';
143
144 END GetPlannerMsgName;
145
146 PROCEDURE DeleteActivities( arg_plan_id in number) IS
147
148 CURSOR Cur_Delete_Activities (l_item_type in varchar2, l_plan_char in varchar2)
149 IS
150 SELECT wi.item_key
151 FROM wf_items wi
152 WHERE wi.item_type = l_item_type
153 AND wi.item_key like l_plan_char;
154
155 TYPE varchar2_tab_type IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
156
157 l_item_key_tab varchar2_tab_type;
158 l_item_type varchar2(8);
159 l_plan_char varchar2(100);
160
161 BEGIN
162 l_item_type := 'MSTEXPWF';
163 l_plan_char := to_char(arg_plan_id) ||'-%';
164
165 OPEN Cur_Delete_Activities (l_item_type,l_plan_char);
166 FETCH Cur_Delete_Activities BULK COLLECT INTO l_item_key_tab;
167 CLOSE Cur_Delete_Activities;
168
169 IF NVL(l_item_key_tab.LAST,0) > 0 THEN
170 FORALL i IN l_item_key_tab.FIRST..l_item_key_tab.LAST
171 UPDATE wf_notifications wn
172 SET wn.end_date = sysdate
173 WHERE wn.group_id IN (SELECT wias.notification_id
174 FROM wf_item_activity_statuses wias
175 WHERE wias.item_type = l_item_type
176 AND wias.item_key = l_item_key_tab(i)
177 UNION ALL
178 SELECT wiah.notification_id
179 FROM wf_item_activity_statuses_h wiah
180 WHERE wiah.item_type = l_item_type
181 AND wiah.item_key = l_item_key_tab(i));
182
183 FORALL i IN l_item_key_tab.FIRST..l_item_key_tab.LAST
184 UPDATE wf_items wi
185 SET wi.end_date = sysdate
186 WHERE wi.item_type = l_item_type
187 AND wi.item_key = l_item_key_tab(i);
188
189 FORALL i IN l_item_key_tab.FIRST..l_item_key_tab.LAST
190 UPDATE wf_item_activity_statuses wias
191 SET wias.end_date = sysdate
192 WHERE wias.item_type = l_item_type
193 AND wias.item_key = l_item_key_tab(i);
194
195 FORALL i IN l_item_key_tab.FIRST..l_item_key_tab.LAST
196 UPDATE wf_item_activity_statuses_h wiah
197 SET wiah.end_date = sysdate
198 WHERE wiah.item_type = l_item_type
199 AND wiah.item_key = l_item_key_tab(i);
200
201 COMMIT;
202
203 FOR i IN l_item_key_tab.FIRST..l_item_key_tab.LAST LOOP
204 wf_purge.items(l_item_type,l_item_key_tab(i),sysdate); -- bug 3741028
205 END LOOP;
206
207 COMMIT;
208 END IF;
209
210 EXCEPTION
211 when others then
212 print_info(p_log_message, 'Error in delete activities:'|| to_char(sqlcode) || ':' || substr(sqlerrm,1,100));
213 ROLLBACK;
214 END DeleteActivities;
215
216 Procedure submit_workflow_request (p_request_id OUT NOCOPY NUMBER
217 ,p_plan_id IN NUMBER) IS
218 l_errbuf varchar2(1000);
219 l_retcode number;
220 begin
221 p_request_id := fnd_request.submit_request('MST', 'MSTEXPWF', null, null, false, p_plan_id);
222 if p_request_id = 0 then
223 l_errbuf := fnd_message.get;
224 else
225 commit;
226 end if;
227
228 Exception
229 when others then
230 -- dbms_output.put_line('Error in Submitting Request: '||substr(sqlerrm,1,100));
231 print_info(p_log_message, 'Error in Submitting Request: '||substr(sqlerrm,1,100));
232 return;
233 END submit_workflow_request;
234
235 procedure print_info(p_log_message in number, p_info_str in varchar2) is
236 begin
237 if p_log_message = 1 then
238 fnd_file.put_line(fnd_file.log, p_info_str);
239 --dbms_output.put_line(p_info_str);
240 --abc123pro(p_info_str);
241 end if;
242 end print_info;
243
244 END MST_WORKFLOW_PKG;