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