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