[Home] [Help]
PACKAGE BODY: APPS.GMDRVLAP_WF_PKG
Source
1 PACKAGE BODY GMDRVLAP_wf_pkg AS
2 /* $Header: GMDRVLAB.pls 120.1 2006/06/06 06:24:21 kmotupal noship $ */
3 PROCEDURE wf_init (
4 p_recipe_validity_rule_id IN GMD_RECIPE_VALIDITY_RULES.recipe_validity_rule_id%TYPE,
5 p_recipe_id IN GMD_RECIPE_VALIDITY_RULES.recipe_id%TYPE,
6 p_start_status IN GMD_RECIPE_VALIDITY_RULES.validity_rule_status%TYPE,
7 p_target_status IN GMD_RECIPE_VALIDITY_RULES.validity_rule_status%TYPE,
8 p_requester IN GMD_RECIPE_VALIDITY_RULES.LAST_UPDATED_BY%TYPE,
9 p_last_update_date IN GMD_RECIPE_VALIDITY_RULES.LAST_UPDATE_DATE%TYPE
10 )
11 IS
12 /* procedure to initialize and run Workflow */
13
14 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE := 'GMDRVLAP';
15 l_itemkey WF_ITEMS.ITEM_KEY%TYPE := to_char(p_recipe_validity_rule_id)||'-'||
16 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 l_recipe_no GMD_RECIPES.RECIPE_NO%TYPE;
21 l_recipe_vers GMD_RECIPES.RECIPE_VERSION%TYPE;
22 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
23 l_recipe_use VARCHAR2(80);
24 --Krishna NPD-Conv, Created l_orgn_id, l_item_id. Modified l_orgn_code, l_item_no.
25 l_orgn_id GMD_RECIPE_VALIDITY_RULES.organization_id%TYPE;
26 l_orgn_code ORG_ORGANIZATION_DEFINITIONS.organization_code%TYPE;
27 l_item_id GMD_RECIPE_VALIDITY_RULES.INVENTORY_ITEM_ID%TYPE;
28 l_item_no MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
29 l_preference GMD_RECIPE_VALIDITY_RULES.PREFERENCE%TYPE;
30 l_std_qty VARCHAR2(80);
31 l_min_qty VARCHAR2(80);
32 l_max_qty VARCHAR2(80);
33 l_effective_start_date GMD_RECIPE_VALIDITY_RULES.START_DATE%TYPE;
34 l_effective_end_date GMD_RECIPE_VALIDITY_RULES.END_DATE%TYPE;
35 /* Mercy Thomas Bug 3173515 End of the changes */
36
37 /* make sure that process runs with background engine
38 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
39 the value to use for this is -1 */
40
41 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
42 l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
43
44 l_WorkflowProcess VARCHAR2(30) := 'GMDRVLAP_PROCESS';
45 l_count NUMBER;
46 BEGIN
47
48 /* create the process */
49 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype, itemkey => l_itemkey, process => l_WorkflowProcess) ;
50
51 /* make sure that process runs with background engine */
52 WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
53
54 /* set the item attributes */
55 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
56 aname => 'GMDRVLAP_RECIPE_ID',
57 avalue => p_recipe_id);
58 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
59 aname => 'GMDRVLAP_RECIPE_VALIDITYRULEID',
60 avalue => p_recipe_validity_rule_id);
61
62 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
63 aname => 'GMDRVLAP_START_STATUS',
64 avalue => p_start_status);
65
66 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
67 aname => 'GMDRVLAP_TARGET_STATUS',
68 avalue => p_target_status);
69
70 SELECT RECIPE_NO,RECIPE_VERSION INTO l_recipe_no,l_recipe_vers
71 FROM GMD_RECIPES_B
72 WHERE RECIPE_ID = P_RECIPE_ID;
73 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
74 aname => 'GMDRVLAP_RECIPE_NO',
75 avalue => l_recipe_no);
76
77 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
78 aname => 'GMDRVLAP_RECIPE_VERS',
79 avalue => l_recipe_vers);
80
81
82 l_wf_timeout := (l_wf_timeout * 24 * 60) / 4 ; -- Converting days into minutes
83
84 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
85 aname => 'GMDRVLAP_TIMEOUT',
86 avalue => l_wf_timeout);
87 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
88 aname => 'GMDRVLAP_MESG_CNT',
89 avalue => 1);
90
91 l_runform := 'GMDRVRED_F:RECIPE_VALIDITY_RULE_ID='||to_char(p_recipe_validity_rule_id);
92
93 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
94 aname => 'GMDRVLAP_FORM',
95 avalue => l_runform);
96
97 -- get values to be stored into the workflow item
98 SELECT USER_NAME , DESCRIPTION
99 INTO l_performer_name ,l_performer_display_name
100 FROM FND_USER
101 WHERE USER_ID = p_Requester;
102
103 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
104 aname => 'GMDRVLAP_REQUSTER',
105 avalue => l_performer_name );
106
107 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
108 /* Krishna, Modified Query as per NPD-Convergence plan */
109 SELECT A.ORGANIZATION_ID,
110 DECODE(A.RECIPE_USE, 0, 'Production', 1, 'Planning', 2, 'Costing', 3, 'Regulatory', 4, 'Technical'),
111 B.CONCATENATED_SEGMENTS, A.PREFERENCE, A.STD_QTY || ' ' || A.DETAIL_UOM, A.MIN_QTY || ' ' || A.DETAIL_UOM, A.MAX_QTY || ' ' || A.DETAIL_UOM, A.START_DATE, A.END_DATE
112 INTO l_orgn_id, l_recipe_use, l_item_no, l_preference, l_std_qty, l_min_qty, l_max_qty, l_effective_start_date, l_effective_end_date
113 FROM GMD_RECIPE_VALIDITY_RULES A, MTL_SYSTEM_ITEMS_KFV B
114 WHERE A.RECIPE_VALIDITY_RULE_ID = P_RECIPE_VALIDITY_RULE_ID
115 AND A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
116 AND A.organization_id = B.organization_id;
117
118 /* fetch Organization Code */
119 IF l_orgn_id is NOT NULL then
120 GMD_ERES_UTILS.GET_ORGANIZATION_CODE(l_orgn_id, l_orgn_code);
121 END IF;
122
123 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
124 aname => 'GMDRVLAP_ORGN_CODE',
125 avalue => l_orgn_code);
126
127 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
128 aname => 'GMDRVLAP_RECIPE_USE',
129 avalue => l_recipe_use);
130
131 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
132 aname => 'GMDRVLAP_ITEM_NO',
133 avalue => l_item_no);
134
135 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
136 aname => 'GMDRVLAP_PREFERENCE',
137 avalue => l_preference);
138
139 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
140 aname => 'GMDRVLAP_STD_QTY',
141 avalue => l_std_qty);
142
143 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
144 aname => 'GMDRVLAP_MIN_QTY',
145 avalue => l_min_qty);
146
147 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
148 aname => 'GMDRVLAP_MAX_QTY',
149 avalue => l_max_qty);
150
151 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
152 aname => 'GMDRVLAP_EFF_START_DATE',
153 avalue => l_effective_start_date);
154
155 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
156 aname => 'GMDRVLAP_EFF_END_DATE',
157 avalue => l_effective_end_date);
158
159 /* Mercy Thomas Bug 3173515 End of the changes */
160
161 /* start the Workflow process */
162
163 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
164
165
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 WF_CORE.CONTEXT ('GMDRVLAP_wf_pkg','wf_init',l_itemtype,l_itemkey,'Initial' );
170 raise;
171
172 END wf_init;
173
174
175
176
177
178 /* ######################################################################## */
179
180 PROCEDURE is_approval_req
181 (p_itemtype IN VARCHAR2,
182 p_itemkey IN VARCHAR2,
183 p_actid IN NUMBER,
184 p_funcmode IN VARCHAR2,
185 p_resultout OUT NOCOPY VARCHAR2
186 )
187 IS
188 /* procedure to Check Approval is required or not if required find the approver and send the notification to
189 approver */
190
191 l_recipe_validity_rule_id GMD_RECIPE_VALIDITY_RULES.recipe_validity_rule_id%TYPE;
192 p_data_string VARCHAR2(2000);
193 p_wf_data_string VARCHAR2(2000);
194 p_lab_wf_item_type VARCHAR2(8) := 'GMDRVLAP'; -- Recipe Lab use Approval Workflow Inernal Name
195 P_lab_Process_name VARCHAR2(32) := 'GMDRVLAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
196 P_lab_activity_name VARCHAR2(80) := 'GMDRVLAP_NOTI_REQUEST';
197 P_table_name VARCHAR2(32) := 'GMD_RECIPE_VALIDITY_RULES'; -- Key Table
198 P_where_clause VARCHAR2(100); -- Where clause to be appended
199 p_role GMA_ACTDATA_WF.ROLE%TYPE;
200 l_data_string VARCHAR2(2000);
201 l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
202 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
203 BEGIN
204 l_recipe_validity_rule_id:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVLAP_RECIPE_VALIDITYRULEID');
205 -- Bug# 5030408 Kapil M : Removed the table name
206 P_where_clause := ' RECIPE_VALIDITY_RULE_ID='||l_recipe_validity_rule_id;
207 IF (p_funcmode = 'RUN') THEN
208 gma_wfstd_p.WF_GET_CONTORL_PARAMS(P_LAB_WF_ITEM_TYPE,
209 P_LAB_PROCESS_NAME,
210 P_LAB_ACTIVITY_NAME,
211 P_TABLE_NAME,
212 P_WHERE_CLAUSE,
213 P_DATA_STRING,
214 p_wf_data_string);
215 IF gma_wfstd_p.check_activity_approval_req(p_lab_wf_item_type,
216 p_lab_process_name,
217 p_lab_activity_name,
218 p_data_string) = 'Y'
219 THEN
220 gma_wfstd_p.get_role (p_lab_wf_item_type,
221 p_lab_process_name,
222 p_lab_activity_name,
223 p_data_string,
224 P_role);
225 l_data_string := replace(p_wf_data_string,l_delimiter,wf_core.newline);
226 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
227 itemkey => p_itemkey,
228 aname => 'GMDRVLAP_ADDL_TEXT',
229 avalue => l_data_string);
230
231 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
232 itemkey => p_itemkey,
233 aname => 'GMDRVLAP_APPROVER',
234 avalue => p_role);
235
236 p_resultout:='COMPLETE:Y';
237 ELSE
238 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_TARGET_STATUS');
239 UPDATE GMD_RECIPE_VALIDITY_RULES
240 SET VALIDITY_RULE_STATUS = l_target_status
241 WHERE RECIPE_VALIDITY_RULE_ID = l_recipe_validity_rule_id;
242 p_resultout:='COMPLETE:N';
243 END IF;
244 END IF;
245 EXCEPTION
246 WHEN OTHERS THEN
247 WF_CORE.CONTEXT ('GMDRVLAP_wf_pkg','is_approval_req',p_itemtype,p_itemkey,p_role);
248 raise;
249 END is_approval_req;
250
251
252 PROCEDURE REMINDAR_CHECK (
253 p_itemtype IN VARCHAR2,
254 p_itemkey IN VARCHAR2,
255 p_actid IN NUMBER,
256 p_funcmode IN VARCHAR2,
257 p_resultout OUT NOCOPY VARCHAR2) IS
258 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVLAP_MESG_CNT');
259 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_APPROVER');
260 BEGIN
261 IF (p_funcmode = 'TIMEOUT') THEN
262 l_mesg_cnt := l_mesg_cnt + 1;
263 IF l_mesg_cnt <= 4 THEN
264 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
265 aname => 'GMDRVLAP_MESG_CNT',
266 avalue => l_mesg_cnt);
267 ELSE
268 p_resultout := 'COMPLETE:DEFAULT';
269 END IF;
270 ELSIF (p_funcmode = 'RESPOND') THEN
271 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
272 itemkey => p_itemkey,
273 aname => 'GMDRVLAP_CURR_PERFORMER',
274 avalue => l_approver);
275 END IF;
276 END;
277
278
279 PROCEDURE REQ_APPROVED (
280 p_itemtype IN VARCHAR2,
281 p_itemkey IN VARCHAR2,
282 p_actid IN NUMBER,
283 p_funcmode IN VARCHAR2,
284 p_resultout OUT NOCOPY VARCHAR2) IS
285 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
286 l_recipe_validity_rule_id GMD_RECIPE_VALIDITY_RULES.recipe_validity_rule_id%TYPE;
287 BEGIN
288 l_recipe_validity_rule_id:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVLAP_RECIPE_VALIDITYRULEID');
289 IF (p_funcmode = 'RUN') THEN
290 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_TARGET_STATUS');
291 UPDATE GMD_RECIPE_VALIDITY_RULES
292 SET VALIDITY_RULE_STATUS = l_target_status
293 WHERE RECIPE_VALIDITY_RULE_ID = l_recipe_validity_rule_id;
294 END IF;
295 END REQ_APPROVED;
296
297 PROCEDURE REQ_REJECTED (
298 p_itemtype IN VARCHAR2,
299 p_itemkey IN VARCHAR2,
300 p_actid IN NUMBER,
301 p_funcmode IN VARCHAR2,
302 p_resultout OUT NOCOPY VARCHAR2) IS
303 l_rework_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
304 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
305 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
306 l_recipe_validity_rule_id GMD_RECIPE_VALIDITY_RULES.recipe_validity_rule_id%TYPE;
307 BEGIN
308 l_recipe_validity_rule_id:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVLAP_RECIPE_VALIDITYRULEID');
309 IF (p_funcmode = 'RUN') THEN
310 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_START_STATUS');
311 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_TARGET_STATUS');
312 SELECT rework_status into l_rework_status
313 FROM GMD_STATUS_NEXT
314 WHERE current_status = l_start_status
315 AND target_status = l_target_status
316 AND pending_status IS NOT NULL;
317 UPDATE GMD_RECIPE_VALIDITY_RULES
318 SET VALIDITY_RULE_STATUS = l_rework_status
319 WHERE RECIPE_VALIDITY_RULE_ID = l_recipe_validity_rule_id;
320 END IF;
321 END REQ_REJECTED;
322
323 PROCEDURE NO_RESPONSE (
324 p_itemtype IN VARCHAR2,
325 p_itemkey IN VARCHAR2,
329 l_recipe_validity_rule_id GMD_RECIPE_VALIDITY_RULES.recipe_validity_rule_id%TYPE;
326 p_actid IN NUMBER,
327 p_funcmode IN VARCHAR2,
328 p_resultout OUT NOCOPY VARCHAR2) IS
330 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
331 BEGIN
332 l_recipe_validity_rule_id:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVLAP_RECIPE_VALIDITYRULEID');
333 IF (p_funcmode = 'RUN') THEN
334 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_START_STATUS');
335 UPDATE GMD_RECIPE_VALIDITY_RULES
336 SET VALIDITY_RULE_STATUS = l_start_status
337 WHERE RECIPE_VALIDITY_RULE_ID = l_recipe_validity_rule_id;
338 END IF;
339 END NO_RESPONSE;
340
341 PROCEDURE MOREINFO_RESPONSE (
342 p_itemtype IN VARCHAR2,
343 p_itemkey IN VARCHAR2,
344 p_actid IN NUMBER,
345 p_funcmode IN VARCHAR2,
346 p_resultout OUT NOCOPY VARCHAR2) IS
347 l_requester VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_REQUSTER');
348 BEGIN
349 IF (p_funcmode = 'RESPOND') THEN
350 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
351 itemkey => p_itemkey,
352 aname => 'GMDRVLAP_CURR_PERFORMER',
353 avalue => l_requester);
354 END IF;
355 END;
356
357 PROCEDURE APPEND_COMMENTS (
358 p_itemtype IN VARCHAR2,
359 p_itemkey IN VARCHAR2,
360 p_actid IN NUMBER,
361 p_funcmode IN VARCHAR2,
362 p_resultout OUT NOCOPY VARCHAR2) IS
363 l_html_mesg VARCHAR2(4000);
364 l_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_COMMENT');
365 l_mesg_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_DISP_COMMENT');
366 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVLAP_CURR_PERFORMER');
367 BEGIN
368 IF (p_funcmode = 'RUN' AND l_comment IS NOT NULL) THEN
369 BEGIN
370 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(SYSDATE)||
371 wf_core.newline||l_comment;
372 -- l_html_mesg := replace(l_mesg_comment,wf_core.newline,'<BR>'||wf_core.newline);
373 l_comment := null;
374 EXCEPTION WHEN OTHERS THEN
375 NULL;
376 END;
377 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
378 itemkey => p_itemkey,
379 aname => 'GMDRVLAP_DISP_COMMENT',
380 avalue => l_mesg_comment);
381 -- WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
382 -- itemkey => p_itemkey,
383 -- aname => 'GMDRVLAP_HTML_DISP_COMMENT',
384 -- avalue => l_html_mesg);
385 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
386 itemkey => p_itemkey,
387 aname => 'GMDRVLAP_COMMENT',
388 avalue => l_comment);
389 END IF;
390 END;
391
392 END GMDRVLAP_wf_pkg;