[Home] [Help]
PACKAGE BODY: APPS.GMDFMGAP_WF_PKG
Source
1 PACKAGE BODY GMDFMGAP_wf_pkg AS
2 /* $Header: GMDFMGAB.pls 120.4 2011/03/22 18:25:56 rnalla ship $ */
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 /* Bug No.8355449 - Added the below line to enable m_fs_context */
173 gmd_p_fs_context.set_additional_attr;
174
175 IF (p_funcmode = 'RUN') THEN
176 gma_wfstd_p.WF_GET_CONTORL_PARAMS(P_LAB_WF_ITEM_TYPE,
177 P_LAB_PROCESS_NAME,
178 P_LAB_ACTIVITY_NAME,
179 P_TABLE_NAME,
180 P_WHERE_CLAUSE,
181 P_DATA_STRING,
182 p_wf_data_string);
183 IF gma_wfstd_p.check_activity_approval_req(p_lab_wf_item_type,
184 p_lab_process_name,
185 p_lab_activity_name,
186 p_data_string) = 'Y'
187 THEN
188 gma_wfstd_p.get_role (p_lab_wf_item_type,
189 p_lab_process_name,
190 p_lab_activity_name,
191 p_data_string,
192 P_role);
193 l_data_string := replace(p_wf_data_string,l_delimiter,wf_core.newline);
194 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
195 itemkey => p_itemkey,
196 aname => 'GMDFMGAP_ADDL_TEXT',
197 avalue => l_data_string);
198
199 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
200 itemkey => p_itemkey,
201 aname => 'GMDFMGAP_APPROVER',
202 avalue => p_role);
203
204 p_resultout:='COMPLETE:Y';
205 ELSE
206 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_TARGET_STATUS');
207 UPDATE FM_FORM_MST_B
208 SET FORMULA_STATUS = l_target_status
209 WHERE FORMULA_ID = l_formula_id;
210 p_resultout:='COMPLETE:N';
211 END IF;
212 END IF;
213 EXCEPTION
214 WHEN OTHERS THEN
215 WF_CORE.CONTEXT ('GMDFMGAP_wf_pkg','is_approval_req',p_itemtype,p_itemkey,p_role);
216 raise;
217 END is_approval_req;
218
219
220
221 PROCEDURE REMINDAR_CHECK (
222 p_itemtype IN VARCHAR2,
223 p_itemkey IN VARCHAR2,
224 p_actid IN NUMBER,
225 p_funcmode IN VARCHAR2,
226 p_resultout OUT NOCOPY VARCHAR2) IS
227 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDFMGAP_MESG_CNT');
228 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_APPROVER');
229 BEGIN
230 IF (p_funcmode = 'TIMEOUT') THEN
231 l_mesg_cnt := l_mesg_cnt + 1;
232 IF l_mesg_cnt <= 4 THEN
233 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
234 aname => 'GMDFMGAP_MESG_CNT',
235 avalue => l_mesg_cnt);
236 ELSE
237 p_resultout := 'COMPLETE:DEFAULT';
238 END IF;
239 ELSIF (p_funcmode = 'RESPOND') THEN
240 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
241 itemkey => p_itemkey,
242 aname => 'GMDFMGAP_CURR_PERFORMER',
243 avalue => l_approver);
244 END IF;
245 END;
246
247
248 /*+========================================================================+
249 ** Name : req_approved
250 **
251 ** HISTORY
252 ** Ger Kelly 10 May B3604554 - added functionality for recipe generation.
253 ** G.Kelly 25-May-2004 B3648200 Modified the call to GMD_RECIPE_GENERATE as this was changed.
254 ** kkillams 01-dec-2004 orgn_code is replaced with organization_id/owner_organization_id w.r.t. 4004501
255 **+========================================================================+*/
256 PROCEDURE REQ_APPROVED (
257 p_itemtype IN VARCHAR2,
258 p_itemkey IN VARCHAR2,
259 p_actid IN NUMBER,
260 p_funcmode IN VARCHAR2,
261 p_resultout OUT NOCOPY VARCHAR2) IS
262
263 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
264 l_formula_id FM_FORM_MST_B.formula_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_FORMULA_ID'));
265
266 /* added variables for recipe generation */
267 l_return_status VARCHAR2(1);
268 x_recipe_no VARCHAR2(32);
269 x_recipe_version NUMBER;
270 l_orgn_id NUMBER;
271
272 CURSOR Cur_check_recipe (V_formula_id NUMBER) IS
273 SELECT 1
274 FROM sys.dual
275 WHERE EXISTS (SELECT 1
276 FROM gmd_recipes_b
277 WHERE formula_id = V_formula_id);
278
279 CURSOR c_get_orgn (V_formula_id NUMBER) IS
280 SELECT owner_organization_id
281 FROM fm_form_mst_b
282 WHERE formula_id = V_formula_id;
283
284 /* Bug 3748697 - Recipe should only be created for automatic */
285 /* or optional setup */
286 CURSOR cur_recipe_enable (V_orgn_id NUMBER) IS
287 SELECT creation_type
288 FROM gmd_recipe_generation
289 WHERE organization_id = V_orgn_id
290 AND creation_type IN (1,2)
291 UNION
292 SELECT creation_type
293 FROM gmd_recipe_generation
294 WHERE organization_id IS NULL
295 AND creation_type IN (1,2)
296 AND NOT EXISTS (SELECT 1
297 FROM gmd_recipe_generation
298 WHERE organization_id = V_orgn_id);
299 l_creation_type NUMBER(5);
300 BEGIN
301
302 /* Bug No.8355449 - Added the below line to enable m_fs_context */
303 gmd_p_fs_context.set_additional_attr;
304
305 IF (p_funcmode = 'RUN') THEN
306 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_TARGET_STATUS');
307 UPDATE FM_FORM_MST_B
308 SET FORMULA_STATUS = l_target_status
309 WHERE FORMULA_ID = l_formula_id;
310
311 /* Bug 3748697 - Thomas Daniel */
312 /* Added the following check to ensure that recipe */
313 /* is not created when the user is changing the status of the formula */
314 /*First lets check if a recipe exists for this formula */
315 OPEN Cur_check_recipe (l_formula_id);
316 FETCH Cur_check_recipe INTO l_creation_type;
317 IF Cur_check_recipe%NOTFOUND THEN
318 CLOSE Cur_check_recipe;
319 /* It implies that there is no recipe, so lets check if the rules have been set to create */
320 /* one automatically */
321 OPEN c_get_orgn (l_formula_id);
322 FETCH c_get_orgn INTO l_orgn_id;
323 CLOSE c_get_orgn;
324
325 OPEN cur_recipe_enable (l_orgn_id);
326 FETCH cur_recipe_enable INTO l_creation_type;
327 IF cur_recipe_enable%FOUND THEN
328 GMD_RECIPE_GENERATE.recipe_generate(l_orgn_id, l_formula_id, l_return_status, x_recipe_no, x_recipe_version, FALSE);
329 END IF;
330 CLOSE cur_recipe_enable;
331 ELSE
332 CLOSE Cur_check_recipe;
333 END IF; /* IF Cur_check_recipe%FOUND */
334 END IF;
335 END REQ_APPROVED;
336
337 PROCEDURE REQ_REJECTED (
341 p_funcmode IN VARCHAR2,
338 p_itemtype IN VARCHAR2,
339 p_itemkey IN VARCHAR2,
340 p_actid IN NUMBER,
342 p_resultout OUT NOCOPY VARCHAR2) IS
343 l_rework_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
344 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
345 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
346 l_formula_id FM_FORM_MST_B.formula_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_FORMULA_ID'));
347 BEGIN
348 IF (p_funcmode = 'RUN') THEN
349 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_START_STATUS');
350 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_TARGET_STATUS');
351 SELECT rework_status into l_rework_status
352 FROM GMD_STATUS_NEXT
353 WHERE current_status = l_start_status
354 AND target_status = l_target_status
355 AND pending_status IS NOT NULL;
356 UPDATE FM_FORM_MST_B
357 SET FORMULA_STATUS = l_rework_status
358 WHERE FORMULA_ID = l_formula_id;
359 END IF;
360 END REQ_REJECTED;
361
362 PROCEDURE NO_RESPONSE (
363 p_itemtype IN VARCHAR2,
364 p_itemkey IN VARCHAR2,
365 p_actid IN NUMBER,
366 p_funcmode IN VARCHAR2,
367 p_resultout OUT NOCOPY VARCHAR2) IS
368 l_formula_id FM_FORM_MST_B.formula_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_FORMULA_ID'));
369 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
370 BEGIN
371 IF (p_funcmode = 'RUN') THEN
372 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_START_STATUS');
373 UPDATE FM_FORM_MST_B
374 SET FORMULA_STATUS = l_start_status
375 WHERE FORMULA_ID = l_formula_id;
376 END IF;
377 END NO_RESPONSE;
378
379 PROCEDURE MOREINFO_RESPONSE (
380 p_itemtype IN VARCHAR2,
381 p_itemkey IN VARCHAR2,
382 p_actid IN NUMBER,
383 p_funcmode IN VARCHAR2,
384 p_resultout OUT NOCOPY VARCHAR2) IS
385 l_requester VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_REQUSTER');
386 BEGIN
387 IF (p_funcmode = 'RESPOND') THEN
388 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
389 itemkey => p_itemkey,
390 aname => 'GMDFMGAP_CURR_PERFORMER',
391 avalue => l_requester);
392 END IF;
393 END;
394
395 PROCEDURE APPEND_COMMENTS (
396 p_itemtype IN VARCHAR2,
397 p_itemkey IN VARCHAR2,
398 p_actid IN NUMBER,
399 p_funcmode IN VARCHAR2,
400 p_resultout OUT NOCOPY VARCHAR2) IS
401 l_html_mesg VARCHAR2(4000);
402 l_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_COMMENT');
403 l_mesg_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_DISP_COMMENT');
404 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDFMGAP_CURR_PERFORMER');
405 BEGIN
406 IF (p_funcmode = 'RUN' AND l_comment IS NOT NULL) THEN
407 BEGIN
408 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(DATEVAL => SYSDATE, CALENDAR_AWARE => 2)||
409 wf_core.newline||l_comment;
410 -- l_html_mesg := replace(l_mesg_comment,wf_core.newline,'<BR>'||wf_core.newline);
411 l_comment := null;
412 EXCEPTION WHEN OTHERS THEN
413 NULL;
414 END;
415 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
416 itemkey => p_itemkey,
417 aname => 'GMDFMGAP_DISP_COMMENT',
418 avalue => l_mesg_comment);
419 -- WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
420 -- itemkey => p_itemkey,
421 -- aname => 'GMDFMGAP_HTML_DISP_COMMENT',
422 -- avalue => l_html_mesg);
423 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
424 itemkey => p_itemkey,
425 aname => 'GMDFMGAP_COMMENT',
426 avalue => l_comment);
427 END IF;
428 END;
429
430 END GMDFMGAP_wf_pkg;