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