[Home] [Help]
PACKAGE BODY: APPS.GMDRPLAP_WF_PKG
Source
1 PACKAGE BODY GMDRPLAP_wf_pkg AS
2 /* $Header: GMDRPLAB.pls 120.1 2011/03/22 18:31:24 rnalla ship $ */
3 PROCEDURE wf_init (
4 p_recipe_id IN GMD_RECIPES_B.recipe_id%TYPE,
5 p_recipe_no IN GMD_RECIPES_B.recipe_no%TYPE,
6 p_recipe_vers IN GMD_RECIPES_B.recipe_version%TYPE,
7 p_start_status IN GMD_RECIPES_B.recipe_status%TYPE,
8 p_target_status IN GMD_RECIPES_B.recipe_status%TYPE,
9 p_requester IN GMD_RECIPES_B.LAST_UPDATED_BY%TYPE,
10 p_last_update_date IN GMD_RECIPES_B.LAST_UPDATE_DATE%TYPE
11 )
12 IS
13 /* procedure to initialize and run Workflow */
14
15 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE := 'GMDRPLAP';
16 l_itemkey WF_ITEMS.ITEM_KEY%TYPE := to_char(p_recipe_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
21 l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
22
23 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
24 l_recipe_description GMD_RECIPES.RECIPE_DESCRIPTION%TYPE;
25 l_owner_id GMD_RECIPES.OWNER_ID%TYPE;
26 l_creation_orgn_code ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_CODE%TYPE;
27 l_creation_orgn_id GMD_RECIPES.creation_organization_id%TYPE;
28 l_formula_no FM_FORM_MST.FORMULA_NO%TYPE;
29 l_formula_vers FM_FORM_MST.FORMULA_VERS%TYPE;
30 l_routing_no GMD_ROUTINGS.ROUTING_NO%TYPE;
31 l_routing_vers GMD_ROUTINGS.ROUTING_VERS%TYPE;
32 /* Mercy Thomas Bug 3173515 End of the changes */
33
34 /* make sure that process runs with background engine
35 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
36 the value to use for this is -1 */
37
38 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := 1000;
39
40
41 l_WorkflowProcess VARCHAR2(30) := 'GMDRPLAP_PROCESS';
42 l_count NUMBER;
43 BEGIN
44
45 /* create the process */
46 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype, itemkey => l_itemkey, process => l_WorkflowProcess) ;
47
48 /* make sure that process runs with background engine */
49 WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
50
51 /* set the item attributes */
52 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
53 aname => 'GMDRPLAP_RECIPE_ID',
54 avalue => p_recipe_id);
55
56 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
57 aname => 'GMDRPLAP_START_STATUS',
58 avalue => p_start_status);
59 l_wf_timeout := (l_wf_timeout * 24 * 60) / 4 ; -- Converting days into minutes
60
61 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
62 aname => 'GMDRPLAP_TIMEOUT',
63 avalue => l_wf_timeout);
64 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
65 aname => 'GMDRPLAP_MESG_CNT',
66 avalue => 1);
67
68
69 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
70 aname => 'GMDRPLAP_TARGET_STATUS',
71 avalue => p_target_status);
72
73 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
74 aname => 'GMDRPLAP_RECIPE_NO',
75 avalue => p_recipe_no);
76
77 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
78 aname => 'GMDRPLAP_RECIPE_VERS',
79 avalue => p_recipe_vers);
80
81 l_runform := 'GMDRCPED_F:RECIPE_ID='||to_char(p_recipe_id);
82
83 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
84 aname => 'GMDRPLAP_FORM',
85 avalue => l_runform);
86
87 -- get values to be stored into the workflow item
88 SELECT USER_NAME , DESCRIPTION
89 INTO l_performer_name ,l_performer_display_name
90 FROM FND_USER
91 WHERE USER_ID = p_Requester;
92
93 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
94 aname => 'GMDRPLAP_REQUSTER',
95 avalue => l_performer_name );
96
97 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
98 SELECT A.RECIPE_DESCRIPTION, A.OWNER_ID, A.CREATION_ORGANIZATION_ID, C.FORMULA_NO, C.FORMULA_VERS, B.ROUTING_NO, B.ROUTING_VERS
99 INTO l_recipe_description, l_owner_id, l_creation_orgn_id, l_formula_no, l_formula_vers, l_routing_no, l_routing_vers
100 FROM GMD_RECIPES A, GMD_ROUTINGS B, FM_FORM_MST C
101 WHERE A.RECIPE_ID = P_RECIPE_ID
102 AND A.ROUTING_ID = B.ROUTING_ID (+)
103 AND A.FORMULA_ID = C.FORMULA_ID;
104
105 /* Krishna, fetch Organization Code */
106 IF l_creation_orgn_id is NOT NULL then
107 GMD_ERES_UTILS.GET_ORGANIZATION_CODE(l_creation_orgn_id, l_creation_orgn_code);
108 END IF;
109
110 SELECT USER_NAME , DESCRIPTION
111 INTO l_performer_name ,l_performer_display_name
112 FROM FND_USER
113 WHERE USER_ID = l_owner_id;
114
115 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
116 aname => 'GMDRPLAP_OWNER_ID',
117 avalue => l_performer_name);
118
119 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
120 aname => 'GMDRPLAP_RECIPE_DESC',
121 avalue => l_recipe_description);
122
123 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
124 aname => 'GMDRPLAP_CREATION_ORGN_CODE',
125 avalue => l_creation_orgn_code);
126
127 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
128 aname => 'GMDRPLAP_ROUTING_NO',
129 avalue => l_routing_no);
130
131 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
132 aname => 'GMDRPLAP_ROUTING_VERS',
133 avalue => l_routing_vers);
134
135 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
136 aname => 'GMDRPLAP_FORMULA_NO',
137 avalue => l_formula_no);
138
139 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
140 aname => 'GMDRPLAP_FORMULA_VERS',
141 avalue => l_formula_vers);
142
143 /* Mercy Thomas Bug 3173515 End of the changes */
144
145
146 /* start the Workflow process */
147
148 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
149
150
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 WF_CORE.CONTEXT ('GMDRPLAP_wf_pkg','wf_init',l_itemtype,l_itemkey,'Initial' );
155 raise;
156
157 END wf_init;
158
159
160
161
162
163 /* ######################################################################## */
164
165 PROCEDURE is_approval_req
166 (p_itemtype IN VARCHAR2,
167 p_itemkey IN VARCHAR2,
168 p_actid IN NUMBER,
169 p_funcmode IN VARCHAR2,
170 p_resultout OUT NOCOPY VARCHAR2
171 )
172 IS
173 /* procedure to Check Approval is required or not if required find the approver and send the notification to
174 approver */
175
176 l_recipe_id GMD_RECIPES_B.recipe_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_RECIPE_ID'));
177 p_data_string VARCHAR2(2000);
178 p_wf_data_string VARCHAR2(2000);
179 p_lab_wf_item_type VARCHAR2(8) := 'GMDRPLAP'; -- Recipe Lab use Approval Workflow Inernal Name
180 P_lab_Process_name VARCHAR2(32) := 'GMDRPLAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
181 P_lab_activity_name VARCHAR2(80) := 'GMDRPLAP_NOTI_REQUEST';
182 P_table_name VARCHAR2(32) := 'GMD_RECIPES_B'; -- Key Table
183 P_where_clause VARCHAR2(100):= ' GMD_RECIPES_B.RECIPE_ID='||l_RECIPE_ID; -- Where clause to be appended
184 p_role GMA_ACTDATA_WF.ROLE%TYPE;
185 l_data_string VARCHAR2(2000);
186 l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
187 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
188 BEGIN
189
190 IF (p_funcmode = 'RUN') THEN
191
192 gma_wfstd_p.WF_GET_CONTORL_PARAMS(P_LAB_WF_ITEM_TYPE,
193 P_LAB_PROCESS_NAME,
194 P_LAB_ACTIVITY_NAME,
195 P_TABLE_NAME,
196 P_WHERE_CLAUSE,
197 P_DATA_STRING,
198 p_wf_data_string);
199 IF gma_wfstd_p.check_activity_approval_req(p_lab_wf_item_type,
200 p_lab_process_name,
201 p_lab_activity_name,
202 p_data_string) = 'Y'
203 THEN
204 gma_wfstd_p.get_role (p_lab_wf_item_type,
205 p_lab_process_name,
206 p_lab_activity_name,
207 p_data_string,
208 P_role);
209 l_data_string := replace(p_wf_data_string,l_delimiter,wf_core.newline);
210 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
211 itemkey => p_itemkey,
212 aname => 'GMDRPLAP_ADDL_TEXT',
213 avalue => l_data_string);
214
215 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
216 itemkey => p_itemkey,
217 aname => 'GMDRPLAP_APPROVER',
218 avalue => p_role);
219
220 p_resultout:='COMPLETE:Y';
221 ELSE
222 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_TARGET_STATUS');
223 UPDATE GMD_RECIPES_B
224 SET RECIPE_STATUS = l_target_status
225 WHERE RECIPE_ID = l_recipe_id;
226 p_resultout:='COMPLETE:N';
227 END IF;
228 END IF;
229 EXCEPTION
230 WHEN OTHERS THEN
231 WF_CORE.CONTEXT ('GMDRPLAP_wf_pkg','is_approval_req',p_itemtype,p_itemkey,p_role);
232 raise;
233 END is_approval_req;
234
235
236 PROCEDURE REMINDAR_CHECK (
237 p_itemtype IN VARCHAR2,
238 p_itemkey IN VARCHAR2,
239 p_actid IN NUMBER,
240 p_funcmode IN VARCHAR2,
241 p_resultout OUT NOCOPY VARCHAR2) IS
242 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRPLAP_MESG_CNT');
243 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_APPROVER');
244 BEGIN
245 IF (p_funcmode = 'TIMEOUT') THEN
246 l_mesg_cnt := l_mesg_cnt + 1;
247 IF l_mesg_cnt <= 4 THEN
248 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
249 aname => 'GMDRPLAP_MESG_CNT',
250 avalue => l_mesg_cnt);
251 ELSE
252 p_resultout := 'COMPLETE:DEFAULT';
253 END IF;
254 ELSIF (p_funcmode = 'RESPOND') THEN
255 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
256 itemkey => p_itemkey,
257 aname => 'GMDRPLAP_CURR_PERFORMER',
258 avalue => l_approver);
259 END IF;
260 END;
261
262
263 /* ########################################################################
264 ** Name : REQ_APPROVED
265 **
266 ** HISTORY
267 ** kkillams 01-dec-2004 orgn_code is replaced with organization_id/owner_organization_id w.r.t. 4004501
268 **+========================================================================+*/
269 PROCEDURE REQ_APPROVED (
270 p_itemtype IN VARCHAR2,
271 p_itemkey IN VARCHAR2,
272 p_actid IN NUMBER,
273 p_funcmode IN VARCHAR2,
274 p_resultout OUT NOCOPY VARCHAR2) IS
275 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
276 l_recipe_id GMD_RECIPES_B.recipe_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_RECIPE_ID'));
277 /* added variables for recipe generation for B3604554 */
278 l_return_status VARCHAR2(1);
279 l_formula_id NUMBER(10);
280 x_end_status VARCHAR2(32);
281 l_orgn_id NUMBER;
282 l_recipe_use VARCHAR2(1);
283
284 CURSOR c_get_details IS
285 SELECT r.owner_organization_id, r.formula_id, r.recipe_no, r.recipe_version, f.formula_status
286 FROM gmd_recipes_b r, fm_form_mst_b f
287 WHERE r.recipe_id = l_recipe_id
288 AND r.formula_id = f.formula_id;
289 LocalDetailsRecord c_get_details%ROWTYPE;
290
291 CURSOR cur_recipe_enable IS
292 SELECT recipe_use_prod, recipe_use_plan, recipe_use_cost, recipe_use_reg, recipe_use_tech, managing_validity_rules
293 FROM gmd_recipe_generation
294 WHERE (organization_id = l_orgn_id OR
295 organization_id IS NULL)
296 ORDER BY orgn_code;
297 LocalEnableRecord cur_recipe_enable%ROWTYPE;
298 BEGIN
299
300 IF (p_funcmode = 'RUN') THEN
301 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_TARGET_STATUS');
302 UPDATE GMD_RECIPES_B
303 SET RECIPE_STATUS = l_target_status
304 WHERE RECIPE_ID = l_recipe_id;
305
306 OPEN c_get_details;
307 FETCH c_get_details INTO LocalDetailsRecord;
308 l_orgn_id := LocalDetailsRecord.owner_organization_id;
309 l_formula_id := LocalDetailsRecord.formula_id;
310 CLOSE c_get_details;
311
312 OPEN cur_recipe_enable;
313 FETCH cur_recipe_enable INTO LocalEnableRecord;
314 IF cur_recipe_enable%FOUND THEN
315 GMD_RECIPE_GENERATE.create_validity_rule_set(p_recipe_id => l_recipe_id,
316 p_recipe_no => LocalDetailsRecord.recipe_no,
317 p_recipe_version => LocalDetailsRecord.recipe_version,
318 p_formula_id => l_formula_id,
319 p_orgn_id => l_orgn_id,
320 p_recipe_use_prod => LocalEnableRecord.recipe_use_prod,
321 p_recipe_use_plan => LocalEnableRecord.recipe_use_plan,
322 p_recipe_use_cost => LocalEnableRecord.recipe_use_cost,
323 p_recipe_use_reg => LocalEnableRecord.recipe_use_reg,
324 p_recipe_use_tech => LocalEnableRecord.recipe_use_tech,
325 p_manage_validity_rules => LocalEnableRecord.managing_validity_rules,
326 p_event_signed => FALSE,
327 x_return_status => l_return_status);
328 END IF;
329 CLOSE cur_recipe_enable;
330 END IF;
331
332 END REQ_APPROVED;
333
334 PROCEDURE REQ_REJECTED (
335 p_itemtype IN VARCHAR2,
336 p_itemkey IN VARCHAR2,
337 p_actid IN NUMBER,
338 p_funcmode IN VARCHAR2,
339 p_resultout OUT NOCOPY VARCHAR2) IS
340 l_rework_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
341 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
342 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
343 l_recipe_id GMD_RECIPES_B.recipe_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_RECIPE_ID'));
344 BEGIN
345 IF (p_funcmode = 'RUN') THEN
346 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_START_STATUS');
347 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_TARGET_STATUS');
348 SELECT rework_status into l_rework_status
349 FROM GMD_STATUS_NEXT
350 WHERE current_status = l_start_status
351 AND target_status = l_target_status
352 AND pending_status IS NOT NULL;
353 UPDATE GMD_RECIPES_B
354 SET RECIPE_STATUS = l_rework_status
355 WHERE RECIPE_ID = l_recipe_id;
356 END IF;
357 END REQ_REJECTED;
358
359 PROCEDURE NO_RESPONSE (
360 p_itemtype IN VARCHAR2,
361 p_itemkey IN VARCHAR2,
362 p_actid IN NUMBER,
363 p_funcmode IN VARCHAR2,
364 p_resultout OUT NOCOPY VARCHAR2) IS
365 l_recipe_id GMD_RECIPES_B.recipe_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_RECIPE_ID'));
366 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
367 BEGIN
368 IF (p_funcmode = 'RUN') THEN
369 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_START_STATUS');
370 UPDATE GMD_RECIPES_B
371 SET RECIPE_STATUS = l_start_status
372 WHERE RECIPE_ID = l_recipe_id;
373 END IF;
374 END NO_RESPONSE;
375
376 PROCEDURE MOREINFO_RESPONSE (
377 p_itemtype IN VARCHAR2,
378 p_itemkey IN VARCHAR2,
379 p_actid IN NUMBER,
380 p_funcmode IN VARCHAR2,
381 p_resultout OUT NOCOPY VARCHAR2) IS
382 l_requester VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_REQUSTER');
383 BEGIN
384 IF (p_funcmode = 'RESPOND') THEN
385 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
386 itemkey => p_itemkey,
387 aname => 'GMDRPLAP_CURR_PERFORMER',
388 avalue => l_requester);
389 END IF;
390 END;
391
392 PROCEDURE APPEND_COMMENTS (
393 p_itemtype IN VARCHAR2,
394 p_itemkey IN VARCHAR2,
395 p_actid IN NUMBER,
396 p_funcmode IN VARCHAR2,
397 p_resultout OUT NOCOPY VARCHAR2) IS
398 l_html_mesg VARCHAR2(4000);
399 l_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_COMMENT');
400 l_mesg_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_DISP_COMMENT');
401 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRPLAP_CURR_PERFORMER');
402 BEGIN
403 IF (p_funcmode = 'RUN' AND l_comment IS NOT NULL) THEN
404 BEGIN
405 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(DATEVAL => SYSDATE, CALENDAR_AWARE => 2)||
406 wf_core.newline||l_comment;
407 -- l_html_mesg := replace(l_mesg_comment,wf_core.newline,'<BR>'||wf_core.newline);
408 l_comment := null;
409 EXCEPTION WHEN OTHERS THEN
410 NULL;
411 END;
412 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
413 itemkey => p_itemkey,
414 aname => 'GMDRPLAP_DISP_COMMENT',
415 avalue => l_mesg_comment);
416 -- WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
417 -- itemkey => p_itemkey,
418 -- aname => 'GMDRPLAP_HTML_DISP_COMMENT',
419 -- avalue => l_html_mesg);
420 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
421 itemkey => p_itemkey,
422 aname => 'GMDRPLAP_COMMENT',
423 avalue => l_comment);
424 END IF;
425 END;
426
427 END GMDRPLAP_wf_pkg;