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