[Home] [Help]
PACKAGE BODY: APPS.GMDRVGAP_WF_PKG
Source
1 PACKAGE BODY GMDRVGAP_wf_pkg AS
2 /* $Header: GMDRVGAB.pls 120.2 2011/03/22 18:34:10 rnalla ship $ */
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 := 'GMDRVGAP';
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) := 'GMDRVGAP_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 => 'GMDRVGAP_RECIPE_ID',
57 avalue => p_recipe_id);
58 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
59 aname => 'GMDRVGAP_RECIPE_VALIDITYRULEID',
60 avalue => p_recipe_validity_rule_id);
61
62 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
63 aname => 'GMDRVGAP_START_STATUS',
64 avalue => p_start_status);
65
66 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
67 aname => 'GMDRVGAP_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 => 'GMDRVGAP_RECIPE_NO',
75 avalue => l_recipe_no);
76
77 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
78 aname => 'GMDRVGAP_RECIPE_VERS',
79 avalue => l_recipe_vers);
80 l_wf_timeout := (l_wf_timeout * 24 * 60) / 4 ; -- Converting days into minutes
81
82 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
83 aname => 'GMDRVGAP_TIMEOUT',
84 avalue => l_wf_timeout);
85 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
86 aname => 'GMDRVGAP_MESG_CNT',
87 avalue => 1);
88
89 l_runform := 'GMDRVRED_F:RECIPE_VALIDITY_RULE_ID='||to_char(p_recipe_validity_rule_id);
90
91 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
92 aname => 'GMDRVGAP_FORM',
93 avalue => l_runform);
94
95 -- get values to be stored into the workflow item
96 SELECT USER_NAME , DESCRIPTION
97 INTO l_performer_name ,l_performer_display_name
98 FROM FND_USER
99 WHERE USER_ID = p_Requester;
100
101 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
102 aname => 'GMDRVGAP_REQUSTER',
103 avalue => l_performer_name );
104
105 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
106 /* Krishna, Modified Query as per NPD-Convergence plan */
107 SELECT A.ORGANIZATION_ID,
108 DECODE(A.RECIPE_USE, 0, 'Production', 1, 'Planning', 2, 'Costing', 3, 'Regulatory', 4, 'Technical'),
109 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
110 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
111 FROM GMD_RECIPE_VALIDITY_RULES A, MTL_SYSTEM_ITEMS_KFV B
112 WHERE A.RECIPE_VALIDITY_RULE_ID = P_RECIPE_VALIDITY_RULE_ID
113 AND A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
114 AND A.organization_id = B.organization_id;
115
116 /* fetch Organization Code */
117 IF l_orgn_id is NOT NULL then
118 GMD_ERES_UTILS.GET_ORGANIZATION_CODE(l_orgn_id, l_orgn_code);
119 END IF;
120
121 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
122 aname => 'GMDRVGAP_ORGN_CODE',
123 avalue => l_orgn_code);
124
125 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
126 aname => 'GMDRVGAP_RECIPE_USE',
127 avalue => l_recipe_use);
128
129 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
130 aname => 'GMDRVGAP_ITEM_NO',
131 avalue => l_item_no);
132
133 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
134 aname => 'GMDRVGAP_PREFERENCE',
135 avalue => l_preference);
136
137 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
138 aname => 'GMDRVGAP_STD_QTY',
139 avalue => l_std_qty);
140
141 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
142 aname => 'GMDRVGAP_MIN_QTY',
143 avalue => l_min_qty);
144
145 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
146 aname => 'GMDRVGAP_MAX_QTY',
147 avalue => l_max_qty);
148
149 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
150 aname => 'GMDRVGAP_EFF_START_DATE',
151 avalue => l_effective_start_date);
152
153 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
154 aname => 'GMDRVGAP_EFF_END_DATE',
155 avalue => l_effective_end_date);
156
157 /* Mercy Thomas Bug 3173515 End of the changes */
158
159 /* start the Workflow process */
160
161 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
162
163
164
165 EXCEPTION
166 WHEN OTHERS THEN
167 WF_CORE.CONTEXT ('GMDRVGAP_wf_pkg','wf_init',l_itemtype,l_itemkey,'Initial' );
168 raise;
169
170 END wf_init;
171
172
173
174
175
176 /* ######################################################################## */
177
178 PROCEDURE is_approval_req
179 (p_itemtype IN VARCHAR2,
180 p_itemkey IN VARCHAR2,
181 p_actid IN NUMBER,
182 p_funcmode IN VARCHAR2,
183 p_resultout OUT NOCOPY VARCHAR2
184 )
185 IS
186 /* procedure to Check Approval is required or not if required find the approver and send the notification to
187 approver */
188
189 l_recipe_validity_rule_id GMD_RECIPE_VALIDITY_RULES.recipe_validity_rule_id%TYPE;
190 p_data_string VARCHAR2(2000);
191 p_wf_data_string VARCHAR2(2000);
192 p_lab_wf_item_type VARCHAR2(8) := 'GMDRVGAP'; -- Recipe Lab use Approval Workflow Inernal Name
193 P_lab_Process_name VARCHAR2(32) := 'GMDRVGAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
194 P_lab_activity_name VARCHAR2(80) := 'GMDRVGAP_NOTI_REQUEST';
195 P_table_name VARCHAR2(32) := 'GMD_RECIPE_VALIDITY_RULES'; -- Key Table
196 P_where_clause VARCHAR2(100) ;
197 p_role GMA_ACTDATA_WF.ROLE%TYPE;
198 l_data_string VARCHAR2(2000);
199 l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
200 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
201 BEGIN
202 l_recipe_validity_rule_id:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVGAP_RECIPE_VALIDITYRULEID');
203 -- Bug# 5030408 Kapil M : Removed the table name
204 P_where_clause := ' RECIPE_VALIDITY_RULE_ID=' ||l_recipe_validity_rule_id; -- Where clause to be appended
205 IF (p_funcmode = 'RUN') THEN
206 gma_wfstd_p.WF_GET_CONTORL_PARAMS(P_LAB_WF_ITEM_TYPE,
207 P_LAB_PROCESS_NAME,
208 P_LAB_ACTIVITY_NAME,
209 P_TABLE_NAME,
210 P_WHERE_CLAUSE,
211 P_DATA_STRING,
212 p_wf_data_string);
213 IF gma_wfstd_p.check_activity_approval_req(p_lab_wf_item_type,
214 p_lab_process_name,
215 p_lab_activity_name,
216 p_data_string) = 'Y'
217 THEN
218 gma_wfstd_p.get_role (p_lab_wf_item_type,
219 p_lab_process_name,
220 p_lab_activity_name,
221 p_data_string,
222 P_role);
223 l_data_string := replace(p_wf_data_string,l_delimiter,wf_core.newline);
224 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
225 itemkey => p_itemkey,
226 aname => 'GMDRVGAP_ADDL_TEXT',
227 avalue => l_data_string);
228
229 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
230 itemkey => p_itemkey,
231 aname => 'GMDRVGAP_APPROVER',
232 avalue => p_role);
233
234 p_resultout:='COMPLETE:Y';
235 ELSE
236 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_TARGET_STATUS');
237 UPDATE GMD_RECIPE_VALIDITY_RULES
238 SET VALIDITY_RULE_STATUS = l_target_status
239 WHERE RECIPE_VALIDITY_RULE_ID = l_recipe_validity_rule_id;
240 p_resultout:='COMPLETE:N';
241 END IF;
242 END IF;
243 EXCEPTION
244 WHEN OTHERS THEN
245 WF_CORE.CONTEXT ('GMDRVGAP_wf_pkg','is_approval_req',p_itemtype,p_itemkey,p_role);
246 raise;
247 END is_approval_req;
248
249 PROCEDURE REMINDAR_CHECK (
250 p_itemtype IN VARCHAR2,
251 p_itemkey IN VARCHAR2,
252 p_actid IN NUMBER,
253 p_funcmode IN VARCHAR2,
254 p_resultout OUT NOCOPY VARCHAR2) IS
255 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVGAP_MESG_CNT');
256 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_APPROVER');
257 BEGIN
258 IF (p_funcmode = 'TIMEOUT') THEN
259 l_mesg_cnt := l_mesg_cnt + 1;
260 IF l_mesg_cnt <= 4 THEN
261 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
262 aname => 'GMDRVGAP_MESG_CNT',
263 avalue => l_mesg_cnt);
264 ELSE
265 p_resultout := 'COMPLETE:DEFAULT';
266 END IF;
267 ELSIF (p_funcmode = 'RESPOND') THEN
268 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
269 itemkey => p_itemkey,
270 aname => 'GMDRVGAP_CURR_PERFORMER',
271 avalue => l_approver);
272 END IF;
273 END;
274
275 PROCEDURE REQ_APPROVED (
276 p_itemtype IN VARCHAR2,
277 p_itemkey IN VARCHAR2,
278 p_actid IN NUMBER,
279 p_funcmode IN VARCHAR2,
280 p_resultout OUT NOCOPY VARCHAR2) IS
281 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
282 l_recipe_validity_rule_id GMD_RECIPE_VALIDITY_RULES.recipe_validity_rule_id%TYPE;
283 BEGIN
284 l_recipe_validity_rule_id:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVGAP_RECIPE_VALIDITYRULEID');
285 IF (p_funcmode = 'RUN') THEN
286 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_TARGET_STATUS');
287 UPDATE GMD_RECIPE_VALIDITY_RULES
288 SET VALIDITY_RULE_STATUS = l_target_status
289 WHERE RECIPE_VALIDITY_RULE_ID = l_recipe_validity_rule_id;
290 END IF;
291
292 END REQ_APPROVED;
293
294 PROCEDURE REQ_REJECTED (
295 p_itemtype IN VARCHAR2,
296 p_itemkey IN VARCHAR2,
297 p_actid IN NUMBER,
298 p_funcmode IN VARCHAR2,
299 p_resultout OUT NOCOPY VARCHAR2) IS
300 l_rework_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
301 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
302 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
303 l_recipe_validity_rule_id GMD_RECIPE_VALIDITY_RULES.recipe_validity_rule_id%TYPE;
304 BEGIN
305 l_recipe_validity_rule_id:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVGAP_RECIPE_VALIDITYRULEID');
306 IF (p_funcmode = 'RUN') THEN
307 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_START_STATUS');
308 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_TARGET_STATUS');
309 SELECT rework_status into l_rework_status
310 FROM GMD_STATUS_NEXT
311 WHERE current_status = l_start_status
312 AND target_status = l_target_status
313 AND pending_status IS NOT NULL;
314 UPDATE GMD_RECIPE_VALIDITY_RULES
315 SET VALIDITY_RULE_STATUS = l_rework_status
316 WHERE RECIPE_VALIDITY_RULE_ID = l_recipe_validity_rule_id;
317 END IF;
318 END REQ_REJECTED;
319
320 PROCEDURE NO_RESPONSE (
321 p_itemtype IN VARCHAR2,
322 p_itemkey IN VARCHAR2,
323 p_actid IN NUMBER,
324 p_funcmode IN VARCHAR2,
325 p_resultout OUT NOCOPY VARCHAR2) IS
326 l_recipe_validity_rule_id GMD_RECIPE_VALIDITY_RULES.recipe_validity_rule_id%TYPE;
327 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
328 BEGIN
329 l_recipe_validity_rule_id:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRVGAP_RECIPE_VALIDITYRULEID');
330 IF (p_funcmode = 'RUN') THEN
331 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_START_STATUS');
332 UPDATE GMD_RECIPE_VALIDITY_RULES
333 SET VALIDITY_RULE_STATUS = l_start_status
334 WHERE RECIPE_VALIDITY_RULE_ID = l_recipe_validity_rule_id;
335 END IF;
336 END NO_RESPONSE;
337 PROCEDURE MOREINFO_RESPONSE (
338 p_itemtype IN VARCHAR2,
339 p_itemkey IN VARCHAR2,
340 p_actid IN NUMBER,
341 p_funcmode IN VARCHAR2,
342 p_resultout OUT NOCOPY VARCHAR2) IS
343 l_requester VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_REQUSTER');
344 BEGIN
345 IF (p_funcmode = 'RESPOND') THEN
346 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
347 itemkey => p_itemkey,
348 aname => 'GMDRVGAP_CURR_PERFORMER',
349 avalue => l_requester);
350 END IF;
351 END;
352
353 PROCEDURE APPEND_COMMENTS (
354 p_itemtype IN VARCHAR2,
355 p_itemkey IN VARCHAR2,
356 p_actid IN NUMBER,
357 p_funcmode IN VARCHAR2,
358 p_resultout OUT NOCOPY VARCHAR2) IS
359 l_html_mesg VARCHAR2(4000);
360 l_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_COMMENT');
361 l_mesg_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_DISP_COMMENT');
362 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRVGAP_CURR_PERFORMER');
363 BEGIN
364 IF (p_funcmode = 'RUN' AND l_comment IS NOT NULL) THEN
365 BEGIN
366 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(DATEVAL => SYSDATE, CALENDAR_AWARE => 2)||
367 wf_core.newline||l_comment;
368 -- l_html_mesg := replace(l_mesg_comment,wf_core.newline,'<BR>'||wf_core.newline);
369 l_comment := null;
370 EXCEPTION WHEN OTHERS THEN
371 NULL;
372 END;
373 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
374 itemkey => p_itemkey,
375 aname => 'GMDRVGAP_DISP_COMMENT',
376 avalue => l_mesg_comment);
377 -- WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
378 -- itemkey => p_itemkey,
379 -- aname => 'GMDRVGAP_HTML_DISP_COMMENT',
380 -- avalue => l_html_mesg);
381 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
382 itemkey => p_itemkey,
383 aname => 'GMDRVGAP_COMMENT',
384 avalue => l_comment);
385 END IF;
386 END;
387
388 END GMDRVGAP_wf_pkg;