[Home] [Help]
PACKAGE BODY: APPS.GMDRTLAP_WF_PKG
Source
1 PACKAGE BODY GMDRTLAP_wf_pkg AS
2 /* $Header: GMDRTLAB.pls 120.1 2011/03/22 18:33:10 rnalla ship $ */
3 PROCEDURE wf_init (
4 p_Routing_id IN gmd_routings_b.Routing_id%TYPE,
5 p_Routing_no IN gmd_routings_b.Routing_no%TYPE,
6 p_Routing_vers IN gmd_routings_b.Routing_vers%TYPE,
7 p_start_status IN gmd_routings_b.Routing_status%TYPE,
8 p_target_status IN gmd_routings_b.Routing_status%TYPE,
9 p_requester IN gmd_routings_b.LAST_UPDATED_BY%TYPE,
10 p_last_update_date IN gmd_routings_b.LAST_UPDATE_DATE%TYPE
11 )
12 IS
13 /* procedure to initialize and run Workflow */
14
15 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE := 'GMDRTLAP';
16 l_itemkey WF_ITEMS.ITEM_KEY%TYPE := to_char(p_Routing_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_routing_desc GMD_ROUTINGS.ROUTING_DESC%TYPE;
23 l_owner_id GMD_ROUTINGS.OWNER_ID%TYPE;
24 l_routing_class GMD_ROUTINGS.ROUTING_CLASS%TYPE;
25 l_routing_qty GMD_ROUTINGS.ROUTING_QTY%TYPE;
26 l_item_um GMD_ROUTINGS.ROUTING_UOM%TYPE;
27 l_effective_start_date GMD_ROUTINGS.EFFECTIVE_START_DATE%TYPE;
28 l_effective_end_date GMD_ROUTINGS.EFFECTIVE_END_DATE%TYPE;
29 /* Mercy Thomas Bug 3173515 End of the changes */
30
31 /* make sure that process runs with background engine
32 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
33 the value to use for this is -1 */
34
35 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
36 l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
37
38
39 l_WorkflowProcess VARCHAR2(30) := 'GMDRTLAP_PROCESS';
40 l_count NUMBER;
41 BEGIN
42
43 /* create the process */
44 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype, itemkey => l_itemkey, process => l_WorkflowProcess) ;
45
46 /* make sure that process runs with background engine */
47 WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
48
49 /* set the item attributes */
50 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
51 aname => 'GMDRTLAP_ROUTING_ID',
52 avalue => p_Routing_id);
53
54 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
55 aname => 'GMDRTLAP_START_STATUS',
56 avalue => p_start_status);
57
58 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
59 aname => 'GMDRTLAP_TARGET_STATUS',
60 avalue => p_target_status);
61
62 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
63 aname => 'GMDRTLAP_ROUTING_NO',
64 avalue => p_Routing_no);
65
66 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
67 aname => 'GMDRTLAP_ROUTING_VERS',
68 avalue => p_Routing_vers);
72 aname => 'GMDRTLAP_TIMEOUT',
69 l_wf_timeout := (l_wf_timeout * 24 * 60) / 4 ; -- Converting days into minutes
70
71 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
73 avalue => l_wf_timeout);
74 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
75 aname => 'GMDRTLAP_MESG_CNT',
76 avalue => 1);
77
78
79 l_runform := 'GMDROTED_F:ROUTING_ID='||to_char(p_Routing_id);
80
81 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
82 aname => 'GMDRTLAP_FORM',
83 avalue => l_runform);
84
85 -- get values to be stored into the workflow item
86 SELECT USER_NAME , DESCRIPTION
87 INTO l_performer_name ,l_performer_display_name
88 FROM FND_USER
89 WHERE USER_ID = p_Requester;
90
91 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
92 aname => 'GMDRTLAP_REQUSTER',
93 avalue => l_performer_name );
94
95 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
96 SELECT ROUTING_DESC, OWNER_ID, ROUTING_CLASS, ROUTING_QTY, ROUTING_UOM, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
97 INTO l_routing_desc, l_owner_id, l_routing_class, l_routing_qty, l_item_um, l_effective_start_date, l_effective_end_date
98 FROM GMD_ROUTINGS
99 WHERE ROUTING_ID = P_ROUTING_ID;
100
101 SELECT USER_NAME , DESCRIPTION
102 INTO l_performer_name ,l_performer_display_name
103 FROM FND_USER
104 WHERE USER_ID = l_owner_id;
105
106 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
107 aname => 'GMDRTLAP_OWNER_ID',
108 avalue => l_performer_name);
109
110 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
111 aname => 'GMDRTLAP_ROUTING_DESC',
112 avalue => l_routing_desc);
113
114 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
115 aname => 'GMDRTLAP_ROUTING_CLASS',
116 avalue => l_routing_class);
117
118 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
119 aname => 'GMDRTLAP_ROUTING_QTY',
120 avalue => l_routing_qty);
121
122 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
123 aname => 'GMDRTLAP_ROUTING_UOM',
124 avalue => l_item_um);
125
126 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
127 aname => 'GMDRTLAP_EFF_START_DATE',
128 avalue => l_effective_start_date);
129
130 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
131 aname => 'GMDRTLAP_EFF_END_DATE',
132 avalue => l_effective_end_date);
133
134 /* Mercy Thomas Bug 3173515 End of the changes */
135
136 /* start the Workflow process */
137
138 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
139
140
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 WF_CORE.CONTEXT ('GMDRTLAP_wf_pkg','wf_init',l_itemtype,l_itemkey,'Initial' );
145 raise;
146
147 END wf_init;
148
149
150
151
152
153 /* ######################################################################## */
154
155 PROCEDURE is_approval_req
156 (p_itemtype IN VARCHAR2,
157 p_itemkey IN VARCHAR2,
158 p_actid IN NUMBER,
159 p_funcmode IN VARCHAR2,
160 p_resultout OUT NOCOPY VARCHAR2
161 )
162 IS
163 /* procedure to Check Approval is required or not if required find the approver and send the notification to
164 approver */
165
166 l_Routing_id gmd_routings_b.Routing_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_ROUTING_ID'));
167 p_data_string VARCHAR2(2000);
168 p_wf_data_string VARCHAR2(2000);
169 p_lab_wf_item_type VARCHAR2(8) := 'GMDRTLAP'; -- Recipe Lab use Approval Workflow Inernal Name
170 P_lab_Process_name VARCHAR2(32) := 'GMDRTLAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
171 P_lab_activity_name VARCHAR2(80) := 'GMDRTLAP_NOTI_REQUEST';
172 P_table_name VARCHAR2(32) := 'GMD_ROUTINGS_B'; -- Key Table
173 P_where_clause VARCHAR2(100):= ' gmd_routings_b.ROUTING_ID='||l_ROUTING_ID; -- Where clause to be appended
174 p_role GMA_ACTDATA_WF.ROLE%TYPE;
175 l_data_string VARCHAR2(2000);
176 l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
177 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
178 BEGIN
179
180 IF (p_funcmode = 'RUN') THEN
181 gma_wfstd_p.WF_GET_CONTORL_PARAMS(P_LAB_WF_ITEM_TYPE,
182 P_LAB_PROCESS_NAME,
183 P_LAB_ACTIVITY_NAME,
184 P_TABLE_NAME,
185 P_WHERE_CLAUSE,
186 P_DATA_STRING,
187 p_wf_data_string);
188 IF gma_wfstd_p.check_activity_approval_req(p_lab_wf_item_type,
189 p_lab_process_name,
190 p_lab_activity_name,
191 p_data_string) = 'Y'
192 THEN
193 gma_wfstd_p.get_role (p_lab_wf_item_type,
197 P_role);
194 p_lab_process_name,
195 p_lab_activity_name,
196 p_data_string,
198 l_data_string := replace(p_wf_data_string,l_delimiter,wf_core.newline);
199 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
200 itemkey => p_itemkey,
201 aname => 'GMDRTLAP_ADDL_TEXT',
202 avalue => l_data_string);
203
204 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
205 itemkey => p_itemkey,
206 aname => 'GMDRTLAP_APPROVER',
207 avalue => p_role);
208
209 p_resultout:='COMPLETE:Y';
210 ELSE
211 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_TARGET_STATUS');
212 UPDATE GMD_ROUTINGS_B
213 SET ROUTING_STATUS = l_target_status
214 WHERE ROUTING_ID = l_Routing_id;
215 p_resultout:='COMPLETE:N';
216 END IF;
217 END IF;
218 EXCEPTION
219 WHEN OTHERS THEN
220 WF_CORE.CONTEXT ('GMDRTLAP_wf_pkg','is_approval_req',p_itemtype,p_itemkey,p_role);
221 raise;
222 END is_approval_req;
223
224
225 PROCEDURE REMINDAR_CHECK (
226 p_itemtype IN VARCHAR2,
227 p_itemkey IN VARCHAR2,
228 p_actid IN NUMBER,
229 p_funcmode IN VARCHAR2,
230 p_resultout OUT NOCOPY VARCHAR2) IS
231 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDRTLAP_MESG_CNT');
232 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_APPROVER');
233 BEGIN
234 IF (p_funcmode = 'TIMEOUT') THEN
235 l_mesg_cnt := l_mesg_cnt + 1;
236 IF l_mesg_cnt <= 4 THEN
237 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
238 aname => 'GMDRTLAP_MESG_CNT',
239 avalue => l_mesg_cnt);
240 ELSE
241 p_resultout := 'COMPLETE:DEFAULT';
242 END IF;
243 ELSIF (p_funcmode = 'RESPOND') THEN
244 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
245 itemkey => p_itemkey,
246 aname => 'GMDRTLAP_CURR_PERFORMER',
247 avalue => l_approver);
248 END IF;
249 END;
250
251
252 PROCEDURE REQ_APPROVED (
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_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
259 l_Routing_id gmd_routings_b.Routing_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_ROUTING_ID'));
260 BEGIN
261 IF (p_funcmode = 'RUN') THEN
262 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_TARGET_STATUS');
263 UPDATE GMD_ROUTINGS_B
264 SET ROUTING_STATUS = l_target_status
265 WHERE ROUTING_ID = l_Routing_id;
266 END IF;
267
268 END REQ_APPROVED;
269
270 PROCEDURE REQ_REJECTED (
271 p_itemtype IN VARCHAR2,
272 p_itemkey IN VARCHAR2,
273 p_actid IN NUMBER,
274 p_funcmode IN VARCHAR2,
275 p_resultout OUT NOCOPY VARCHAR2) IS
276 l_rework_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
277 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
278 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
279 l_Routing_id gmd_routings_b.Routing_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_ROUTING_ID'));
280 BEGIN
281 IF (p_funcmode = 'RUN') THEN
282 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_START_STATUS');
283 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_TARGET_STATUS');
284 SELECT rework_status into l_rework_status
285 FROM GMD_STATUS_NEXT
286 WHERE current_status = l_start_status
287 AND target_status = l_target_status
288 AND pending_status IS NOT NULL;
289 UPDATE GMD_ROUTINGS_B
290 SET ROUTING_STATUS = l_rework_status
291 WHERE ROUTING_ID = l_Routing_id;
292 END IF;
293 END REQ_REJECTED;
294
295 PROCEDURE NO_RESPONSE (
296 p_itemtype IN VARCHAR2,
297 p_itemkey IN VARCHAR2,
298 p_actid IN NUMBER,
299 p_funcmode IN VARCHAR2,
300 p_resultout OUT NOCOPY VARCHAR2) IS
301 l_Routing_id gmd_routings_b.Routing_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_ROUTING_ID'));
302 l_start_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
303 BEGIN
304 IF (p_funcmode = 'RUN') THEN
305 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_START_STATUS');
306 UPDATE GMD_ROUTINGS_B
307 SET ROUTING_STATUS = l_start_status
308 WHERE ROUTING_ID = l_Routing_id;
309 END IF;
310 END NO_RESPONSE;
311 PROCEDURE MOREINFO_RESPONSE (
312 p_itemtype IN VARCHAR2,
313 p_itemkey IN VARCHAR2,
314 p_actid IN NUMBER,
315 p_funcmode IN VARCHAR2,
316 p_resultout OUT NOCOPY VARCHAR2) IS
317 l_requester VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_REQUSTER');
318 BEGIN
319 IF (p_funcmode = 'RESPOND') THEN
320 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
324 END IF;
321 itemkey => p_itemkey,
322 aname => 'GMDRTLAP_CURR_PERFORMER',
323 avalue => l_requester);
325 END;
326
327 PROCEDURE APPEND_COMMENTS (
328 p_itemtype IN VARCHAR2,
329 p_itemkey IN VARCHAR2,
330 p_actid IN NUMBER,
331 p_funcmode IN VARCHAR2,
332 p_resultout OUT NOCOPY VARCHAR2) IS
333 l_html_mesg VARCHAR2(4000);
334 l_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_COMMENT');
335 l_mesg_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_DISP_COMMENT');
336 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDRTLAP_CURR_PERFORMER');
337 BEGIN
338 IF (p_funcmode = 'RUN' AND l_comment IS NOT NULL) THEN
339 BEGIN
340 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(DATEVAL => SYSDATE, CALENDAR_AWARE => 2)||
341 wf_core.newline||l_comment;
342 -- l_html_mesg := replace(l_mesg_comment,wf_core.newline,'<BR>'||wf_core.newline);
343 l_comment := null;
344 EXCEPTION WHEN OTHERS THEN
345 NULL;
346 END;
347 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
348 itemkey => p_itemkey,
349 aname => 'GMDRTLAP_DISP_COMMENT',
350 avalue => l_mesg_comment);
351 -- WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
352 -- itemkey => p_itemkey,
353 -- aname => 'GMDRTLAP_HTML_DISP_COMMENT',
354 -- avalue => l_html_mesg);
355 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
356 itemkey => p_itemkey,
357 aname => 'GMDRTLAP_COMMENT',
358 avalue => l_comment);
359 END IF;
360 END;
361
362 END GMDRTLAP_wf_pkg;