[Home] [Help]
PACKAGE BODY: APPS.GMDFMGAP_WF_PKG
Source
1 PACKAGE BODY GMDFMGAP_wf_pkg AS
2 /* $Header: GMDFMGAB.pls 120.1 2006/08/08 11:25:08 kmotupal noship $ */
3 PROCEDURE wf_init (
4 p_formula_id IN FM_FORM_MST_B.formula_id%TYPE,
5 p_formula_no IN FM_FORM_MST_B.formula_no%TYPE,
6 p_formula_vers IN FM_FORM_MST_B.formula_vers%TYPE,
7 p_start_status IN FM_FORM_MST_B.formula_status%TYPE,
8 p_target_status IN FM_FORM_MST_B.formula_status%TYPE,
9 p_requester IN FM_FORM_MST_B.LAST_UPDATED_BY%TYPE,
10 p_last_update_date IN FM_FORM_MST_B.LAST_UPDATE_DATE%TYPE
11 )
12 IS
13 /* procedure to initialize and run Workflow */
14
15 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE := 'GMDFMGAP';
16 l_itemkey WF_ITEMS.ITEM_KEY%TYPE := to_char(p_formula_id)||'-'||to_char(p_last_update_date,'dd-MON-yyyy HH24:mi:ss');
17 l_runform VARCHAR2(100);
18 l_performer_name FND_USER.USER_NAME%TYPE ;
19 l_performer_display_name FND_USER.DESCRIPTION%TYPE ;
20 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
21 l_formula_desc FM_FORM_MST.FORMULA_DESC1%TYPE;
22 l_owner_id FM_FORM_MST.OWNER_ID%TYPE;
23 l_formula_class FM_FORM_MST.FORMULA_CLASS%TYPE;
24 l_scale_type VARCHAR2(10);
25 /* Mercy Thomas Bug 3173515 End of the changes */
26
27 /* make sure that process runs with background engine
28 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
29 the value to use for this is -1 */
30
31 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
32 l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
33
34
35 l_WorkflowProcess VARCHAR2(30) := 'GMDFMGAP_PROCESS';
36 l_count NUMBER;
37 BEGIN
38
39 /* create the process */
40 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype, itemkey => l_itemkey, process => l_WorkflowProcess) ;
41
42 /* make sure that process runs with background engine */
43 WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
44
45 /* set the item attributes */
46 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
47 aname => 'GMDFMGAP_FORMULA_ID',
48 avalue => p_formula_id);
49
50 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
51 aname => 'GMDFMGAP_START_STATUS',
52 avalue => p_start_status);
53
54 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
55 aname => 'GMDFMGAP_TARGET_STATUS',
56 avalue => p_target_status);
57
58 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
59 aname => 'GMDFMGAP_FORMULA_NO',
60 avalue => p_formula_no);
61
62 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
63 aname => 'GMDFMGAP_FORMULA_VERS',
64 avalue => p_formula_vers);
65
66 l_wf_timeout := (l_wf_timeout * 24 * 60) / 4 ; -- Converting days into minutes
67
68 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
69 aname => 'GMDFMGAP_TIMEOUT',
70 avalue => l_wf_timeout);
71 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
72 aname => 'GMDFMGAP_MESG_CNT',
73 avalue => 1);
74
75 l_runform := 'GMDFRMED_F:FORMULA_ID='||to_char(p_formula_id);
76
77 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
78 aname => 'GMDFMGAP_FORM',
79 avalue => l_runform);
80
81 -- get values to be stored into the workflow item
82 SELECT USER_NAME , DESCRIPTION
83 INTO l_performer_name ,l_performer_display_name
84 FROM FND_USER
85 WHERE USER_ID = p_Requester;
86
87 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
88 aname => 'GMDFMGAP_REQUSTER',
89 avalue => l_performer_name );
90
91 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
92 SELECT FORMULA_DESC1, OWNER_ID, FORMULA_CLASS, DECODE(SCALE_TYPE, 1, 'Yes', 0, 'No')
93 INTO l_formula_desc, l_owner_id, l_formula_class, l_scale_type
94 FROM FM_FORM_MST
95 WHERE FORMULA_ID = P_FORMULA_ID;
96
97 SELECT USER_NAME , DESCRIPTION
98 INTO l_performer_name ,l_performer_display_name
99 FROM FND_USER
100 WHERE USER_ID = l_owner_id;
101
102 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
103 aname => 'GMDFMGAP_OWNER_ID',
104 avalue => l_performer_name);
105
106 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
107 aname => 'GMDFMGAP_FORMULA_DESC',
108 avalue => l_formula_desc);
109
110 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
111 aname => 'GMDFMGAP_FORMULA_CLASS',
112 avalue => l_formula_class);
113
114 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
115 aname => 'GMDFMGAP_SCALE_TYPE',
116 avalue => l_scale_type);
117
118
119 /* Mercy Thomas Bug 3173515 End of the changes */
120
121
122 /* start the Workflow process */
123
124 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
125
126
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
131 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', sqlerrm);
132 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMDFMGAP_WF_PKG');
133 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', 'WF_INIT');
134 APP_EXCEPTION.RAISE_EXCEPTION;
135 WF_CORE.CONTEXT ('GMDFMGAP_wf_pkg','wf_init',l_itemtype,l_itemkey,'Initial' );
136 raise;
137
138 END wf_init;
139
140
141
142
143
144 /* ######################################################################## */
145
146 PROCEDURE is_approval_req
147 (p_itemtype IN VARCHAR2,
148 p_itemkey IN VARCHAR2,
149 p_actid IN NUMBER,
150 p_funcmode IN VARCHAR2,
151 p_resultout OUT NOCOPY VARCHAR2
152 )
153 IS
154 /* procedure to Check Approval is required or not if required find the approver and send the notification to
155 approver */
156
157 l_formula_id FM_FORM_MST_B.formula_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_FORMULA_ID'));
158 p_data_string VARCHAR2(2000);
159 p_lab_wf_item_type VARCHAR2(8) := 'GMDFMGAP'; -- Recipe Lab use Approval Workflow Inernal Name
160 P_lab_Process_name VARCHAR2(32) := 'GMDFMGAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
161 P_lab_activity_name VARCHAR2(80) := 'GMDFMGAP_NOTI_REQUEST';
162 P_table_name VARCHAR2(32) := 'FM_FORM_MST_B'; -- Key Table
163 P_where_clause VARCHAR2(100):= ' FM_FORM_MST_B.FORMULA_ID='||l_FORMULA_ID; -- Where clause to be appended
164 p_role GMA_ACTDATA_WF.ROLE%TYPE;
165 l_data_string VARCHAR2(2000);
166 p_wf_data_string VARCHAR2(2000);
167 l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
168 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
169
170 BEGIN
171
172 IF (p_funcmode = 'RUN') THEN
173 gma_wfstd_p.WF_GET_CONTORL_PARAMS(P_LAB_WF_ITEM_TYPE,
174 P_LAB_PROCESS_NAME,
175 P_LAB_ACTIVITY_NAME,
176 P_TABLE_NAME,
177 P_WHERE_CLAUSE,
178 P_DATA_STRING,
179 p_wf_data_string);
180 IF gma_wfstd_p.check_activity_approval_req(p_lab_wf_item_type,
181 p_lab_process_name,
182 p_lab_activity_name,
183 p_data_string) = 'Y'
184 THEN
185 gma_wfstd_p.get_role (p_lab_wf_item_type,
186 p_lab_process_name,
187 p_lab_activity_name,
188 p_data_string,
189 P_role);
190 l_data_string := replace(p_wf_data_string,l_delimiter,wf_core.newline);
191 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
192 itemkey => p_itemkey,
193 aname => 'GMDFMGAP_ADDL_TEXT',
194 avalue => l_data_string);
195
196 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
197 itemkey => p_itemkey,
198 aname => 'GMDFMGAP_APPROVER',
199 avalue => p_role);
200
201 p_resultout:='COMPLETE:Y';
202 ELSE
203 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_TARGET_STATUS');
204 UPDATE FM_FORM_MST_B
205 SET FORMULA_STATUS = l_target_status
206 WHERE FORMULA_ID = l_formula_id;
207 p_resultout:='COMPLETE:N';
208 END IF;
209 END IF;
210 EXCEPTION
211 WHEN OTHERS THEN
212 WF_CORE.CONTEXT ('GMDFMGAP_wf_pkg','is_approval_req',p_itemtype,p_itemkey,p_role);
213 raise;
214 END is_approval_req;
215
216
217
218 PROCEDURE REMINDAR_CHECK (
219 p_itemtype IN VARCHAR2,
220 p_itemkey IN VARCHAR2,
221 p_actid IN NUMBER,
222 p_funcmode IN VARCHAR2,
223 p_resultout OUT NOCOPY VARCHAR2) IS
224 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDFMGAP_MESG_CNT');
225 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_APPROVER');
226 BEGIN
227 IF (p_funcmode = 'TIMEOUT') THEN
228 l_mesg_cnt := l_mesg_cnt + 1;
229 IF l_mesg_cnt <= 4 THEN
230 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
231 aname => 'GMDFMGAP_MESG_CNT',
232 avalue => l_mesg_cnt);
233 ELSE
234 p_resultout := 'COMPLETE:DEFAULT';
235 END IF;
236 ELSIF (p_funcmode = 'RESPOND') THEN
237 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
238 itemkey => p_itemkey,
239 aname => 'GMDFMGAP_CURR_PERFORMER',
240 avalue => l_approver);
241 END IF;
242 END;
243
244
245 /*+========================================================================+
246 ** Name : req_approved
247 **
248 ** HISTORY
249 ** Ger Kelly 10 May B3604554 - added functionality for recipe generation.
250 ** G.Kelly 25-May-2004 B3648200 Modified the call to GMD_RECIPE_GENERATE as this was changed.
251 ** kkillams 01-dec-2004 orgn_code is replaced with organization_id/owner_organization_id w.r.t. 4004501
252 **+========================================================================+*/
253 PROCEDURE REQ_APPROVED (
254 p_itemtype IN VARCHAR2,
255 p_itemkey IN VARCHAR2,
256 p_actid IN NUMBER,
257 p_funcmode IN VARCHAR2,
258 p_resultout OUT NOCOPY VARCHAR2) IS
259
260 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
261 l_formula_id FM_FORM_MST_B.formula_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_FORMULA_ID'));
262
263 /* added variables for recipe generation */
264 l_return_status VARCHAR2(1);
265 x_recipe_no VARCHAR2(32);
266 x_recipe_version NUMBER;
267 l_orgn_id NUMBER;
268
269 CURSOR Cur_check_recipe (V_formula_id NUMBER) IS
270 SELECT 1
271 FROM sys.dual
272 WHERE EXISTS (SELECT 1
273 FROM gmd_recipes_b
274 WHERE formula_id = V_formula_id);
275
276 CURSOR c_get_orgn (V_formula_id NUMBER) IS
277 SELECT owner_organization_id
278 FROM fm_form_mst_b
279 WHERE formula_id = V_formula_id;
280
281 /* Bug 3748697 - Recipe should only be created for automatic */
282 /* or optional setup */
283 CURSOR cur_recipe_enable (V_orgn_id NUMBER) IS
284 SELECT creation_type
285 FROM gmd_recipe_generation
286 WHERE organization_id = V_orgn_id
287 AND creation_type IN (1,2)
288 UNION
289 SELECT creation_type
290 FROM gmd_recipe_generation
291 WHERE organization_id IS NULL
292 AND creation_type IN (1,2)
293 AND NOT EXISTS (SELECT 1
294 FROM gmd_recipe_generation
295 WHERE organization_id = V_orgn_id);
296 l_creation_type NUMBER(5);
297 BEGIN
298 IF (p_funcmode = 'RUN') THEN
299 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_TARGET_STATUS');
300 UPDATE FM_FORM_MST_B
301 SET FORMULA_STATUS = l_target_status
302 WHERE FORMULA_ID = l_formula_id;
303
304 /* Bug 3748697 - Thomas Daniel */
305 /* Added the following check to ensure that recipe */
306 /* is not created when the user is changing the status of the formula */
307 /*First lets check if a recipe exists for this formula */
308 OPEN Cur_check_recipe (l_formula_id);
309 FETCH Cur_check_recipe INTO l_creation_type;
310 IF Cur_check_recipe%NOTFOUND THEN
311 CLOSE Cur_check_recipe;
312 /* It implies that there is no recipe, so lets check if the rules have been set to create */
313 /* one automatically */
314 OPEN c_get_orgn (l_formula_id);
315 FETCH c_get_orgn INTO l_orgn_id;
316 CLOSE c_get_orgn;
317
318 OPEN cur_recipe_enable (l_orgn_id);
319 FETCH cur_recipe_enable INTO l_creation_type;
320 IF cur_recipe_enable%FOUND THEN
321 GMD_RECIPE_GENERATE.recipe_generate(l_orgn_id, l_formula_id, l_return_status, x_recipe_no, x_recipe_version, FALSE);
322 END IF;
323 CLOSE cur_recipe_enable;
324 ELSE
325 CLOSE Cur_check_recipe;
326 END IF; /* IF Cur_check_recipe%FOUND */
327 END IF;
328 END REQ_APPROVED;
329
330 PROCEDURE REQ_REJECTED (
331 p_itemtype IN VARCHAR2,
332 p_itemkey IN VARCHAR2,
333 p_actid IN NUMBER,
334 p_funcmode IN VARCHAR2,
335 p_resultout OUT NOCOPY VARCHAR2) IS
336 l_rework_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
337 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
338 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
339 l_formula_id FM_FORM_MST_B.formula_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_FORMULA_ID'));
340 BEGIN
341 IF (p_funcmode = 'RUN') THEN
342 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_START_STATUS');
343 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_TARGET_STATUS');
344 SELECT rework_status into l_rework_status
345 FROM GMD_STATUS_NEXT
346 WHERE current_status = l_start_status
347 AND target_status = l_target_status
348 AND pending_status IS NOT NULL;
349 UPDATE FM_FORM_MST_B
350 SET FORMULA_STATUS = l_rework_status
351 WHERE FORMULA_ID = l_formula_id;
352 END IF;
353 END REQ_REJECTED;
354
355 PROCEDURE NO_RESPONSE (
356 p_itemtype IN VARCHAR2,
357 p_itemkey IN VARCHAR2,
358 p_actid IN NUMBER,
359 p_funcmode IN VARCHAR2,
360 p_resultout OUT NOCOPY VARCHAR2) IS
361 l_formula_id FM_FORM_MST_B.formula_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_FORMULA_ID'));
362 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
363 BEGIN
364 IF (p_funcmode = 'RUN') THEN
365 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_START_STATUS');
366 UPDATE FM_FORM_MST_B
367 SET FORMULA_STATUS = l_start_status
368 WHERE FORMULA_ID = l_formula_id;
369 END IF;
370 END NO_RESPONSE;
371
372 PROCEDURE MOREINFO_RESPONSE (
373 p_itemtype IN VARCHAR2,
374 p_itemkey IN VARCHAR2,
375 p_actid IN NUMBER,
376 p_funcmode IN VARCHAR2,
377 p_resultout OUT NOCOPY VARCHAR2) IS
378 l_requester VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_REQUSTER');
379 BEGIN
380 IF (p_funcmode = 'RESPOND') THEN
381 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
382 itemkey => p_itemkey,
383 aname => 'GMDFMGAP_CURR_PERFORMER',
384 avalue => l_requester);
385 END IF;
386 END;
387
388 PROCEDURE APPEND_COMMENTS (
389 p_itemtype IN VARCHAR2,
390 p_itemkey IN VARCHAR2,
391 p_actid IN NUMBER,
392 p_funcmode IN VARCHAR2,
393 p_resultout OUT NOCOPY VARCHAR2) IS
394 l_html_mesg VARCHAR2(4000);
395 l_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_COMMENT');
396 l_mesg_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_DISP_COMMENT');
397 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_CURR_PERFORMER');
398 BEGIN
399 IF (p_funcmode = 'RUN' AND l_comment IS NOT NULL) THEN
400 BEGIN
401 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(SYSDATE)||
402 wf_core.newline||l_comment;
403 -- l_html_mesg := replace(l_mesg_comment,wf_core.newline,'<BR>'||wf_core.newline);
404 l_comment := null;
405 EXCEPTION WHEN OTHERS THEN
406 NULL;
407 END;
408 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
409 itemkey => p_itemkey,
410 aname => 'GMDFMGAP_DISP_COMMENT',
411 avalue => l_mesg_comment);
412 -- WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
413 -- itemkey => p_itemkey,
414 -- aname => 'GMDFMGAP_HTML_DISP_COMMENT',
415 -- avalue => l_html_mesg);
416 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
417 itemkey => p_itemkey,
418 aname => 'GMDFMGAP_COMMENT',
419 avalue => l_comment);
420 END IF;
421 END;
422
423 END GMDFMGAP_wf_pkg;