[Home] [Help]
PACKAGE BODY: APPS.GMDOPGAP_WF_PKG
Source
1 PACKAGE BODY GMDOPGAP_wf_pkg AS
2 /* $Header: GMDOPGAB.pls 120.1 2011/03/22 18:28:12 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,
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
11 )
12 IS
13 /* procedure to initialize and run Workflow */
14
15 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE := 'GMDOPGAP';
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
21 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
22 l_oprn_desc GMD_OPERATIONS.OPRN_DESC%TYPE;
23 l_oprn_class GMD_OPERATIONS.OPRN_CLASS%TYPE;
24 l_item_um GMD_OPERATIONS.PROCESS_QTY_UOM%TYPE;
25 l_effective_start_date GMD_OPERATIONS.EFFECTIVE_START_DATE%TYPE;
26 l_effective_end_date GMD_OPERATIONS.EFFECTIVE_END_DATE%TYPE;
27 /* Mercy Thomas Bug 3173515 End of the changes */
28
29 /* make sure that process runs with background engine
30 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
31 the value to use for this is -1 */
32
33 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
34 l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
35
36
37 l_WorkflowProcess VARCHAR2(30) := 'GMDOPGAP_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 => 'GMDOPGAP_OPERATION_ID',
50 avalue => p_operation_id);
51
52 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
53 aname => 'GMDOPGAP_START_STATUS',
54 avalue => p_start_status);
55
56 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
57 aname => 'GMDOPGAP_TARGET_STATUS',
58 avalue => p_target_status);
59
60 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
61 aname => 'GMDOPGAP_OPERATION_NO',
62 avalue => p_operation_no);
63
64 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
65 aname => 'GMDOPGAP_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 => 'GMDOPGAP_TIMEOUT',
72 avalue => l_wf_timeout);
73 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,itemkey => l_itemkey,
74 aname => 'GMDOPGAP_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 => 'GMDOPGAP_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 => 'GMDOPGAP_REQUSTER',
91 avalue => l_performer_name );
92
93 /* Mercy Thomas Bug 3173515 Added the following variables for the NPD workflow changes */
94 SELECT OPRN_DESC, OPRN_CLASS, PROCESS_QTY_UOM, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
95 INTO l_oprn_desc, l_oprn_class, l_item_um, l_effective_start_date, l_effective_end_date
96 FROM GMD_OPERATIONS
97 WHERE OPRN_ID = P_OPERATION_ID;
98
99 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
100 aname => 'GMDOPGAP_OPRN_DESC',
101 avalue => l_oprn_desc);
102
103 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
104 aname => 'GMDOPGAP_OPRN_CLASS',
105 avalue => l_oprn_class);
106
107 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
108 aname => 'GMDOPGAP_OPRN_UOM',
109 avalue => l_item_um);
110
111 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
112 aname => 'GMDOPGAP_EFF_START_DATE',
113 avalue => l_effective_start_date);
114
115 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
116 aname => 'GMDOPGAP_EFF_END_DATE',
117 avalue => l_effective_end_date);
118
119 /* Mercy Thomas Bug 3173515 End of the changes */
120
121
122 /* start the Workflow process */
123
124 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
125
126
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 WF_CORE.CONTEXT ('GMDOPGAP_wf_pkg','wf_init',l_itemtype,l_itemkey,'Initial' );
131 raise;
132
133 END wf_init;
134
135
136
137
138
139 /* ######################################################################## */
140
141 PROCEDURE is_approval_req
142 (p_itemtype IN VARCHAR2,
143 p_itemkey IN VARCHAR2,
144 p_actid IN NUMBER,
145 p_funcmode IN VARCHAR2,
146 p_resultout OUT NOCOPY VARCHAR2
147 )
148 IS
149 /* procedure to Check Approval is required or not if required find the approver and send the notification to
150 approver */
151
152 l_operation_id GMD_OPERATIONS_B.oprn_id%TYPE:=to_number(wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPGAP_OPERATION_ID'));
153 p_data_string VARCHAR2(2000);
154 p_wf_data_string VARCHAR2(2000);
155 p_lab_wf_item_type VARCHAR2(8) := 'GMDOPGAP'; -- Recipe Lab use Approval Workflow Inernal Name
156 P_lab_Process_name VARCHAR2(32) := 'GMDOPGAP_PROCESS'; -- Recipe Lab use Approval Workflow Process Inernal Name
157 P_lab_activity_name VARCHAR2(80) := 'GMDOPGAP_NOTI_REQUEST';
158 P_table_name VARCHAR2(32) := 'GMD_OPERATIONS_B'; -- Key Table
159 P_where_clause VARCHAR2(100):= ' GMD_OPERATIONS_B.OPRN_ID='||l_OPERATION_ID; -- Where clause to be appended
160 p_role GMA_ACTDATA_WF.ROLE%TYPE;
161 l_data_string VARCHAR2(2000);
162 l_delimiter VARCHAR2(15) := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
163 l_target_status GMD_STATUS_NEXT.TARGET_STATUS%TYPE;
164 BEGIN
165
166 IF (p_funcmode = 'RUN') THEN
167 gma_wfstd_p.WF_GET_CONTORL_PARAMS(P_LAB_WF_ITEM_TYPE,
168 P_LAB_PROCESS_NAME,
169 P_LAB_ACTIVITY_NAME,
170 P_TABLE_NAME,
171 P_WHERE_CLAUSE,
172 P_DATA_STRING,
173 p_wf_data_string);
174 IF gma_wfstd_p.check_activity_approval_req(p_lab_wf_item_type,
175 p_lab_process_name,
176 p_lab_activity_name,
177 p_data_string) = 'Y'
178 THEN
179 gma_wfstd_p.get_role (p_lab_wf_item_type,
180 p_lab_process_name,
181 p_lab_activity_name,
182 p_data_string,
183 P_role);
184 l_data_string := replace(p_wf_data_string,l_delimiter,wf_core.newline);
185 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
186 itemkey => p_itemkey,
187 aname => 'GMDOPGAP_ADDL_TEXT',
188 avalue => l_data_string);
189
190 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
191 itemkey => p_itemkey,
192 aname => 'GMDOPGAP_APPROVER',
193 avalue => p_role);
194
195 p_resultout:='COMPLETE:Y';
196 ELSE
197 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPGAP_TARGET_STATUS');
198 UPDATE GMD_OPERATIONS_B
199 SET OPERATION_STATUS = l_target_status
200 WHERE OPRN_ID = l_operation_id;
201 p_resultout:='COMPLETE:N';
202 END IF;
203 END IF;
204 EXCEPTION
205 WHEN OTHERS THEN
206 WF_CORE.CONTEXT ('GMDOPGAP_wf_pkg','is_approval_req',p_itemtype,p_itemkey,p_role);
207 raise;
208 END is_approval_req;
209
210 PROCEDURE REMINDAR_CHECK (
211 p_itemtype IN VARCHAR2,
212 p_itemkey IN VARCHAR2,
213 p_actid IN NUMBER,
214 p_funcmode IN VARCHAR2,
215 p_resultout OUT NOCOPY VARCHAR2) IS
216 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDOPGAP_MESG_CNT');
217 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPGAP_APPROVER');
218 BEGIN
219 IF (p_funcmode = 'TIMEOUT') THEN
220 l_mesg_cnt := l_mesg_cnt + 1;
221 IF l_mesg_cnt <= 4 THEN
222 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
223 aname => 'GMDOPGAP_MESG_CNT',
224 avalue => l_mesg_cnt);
225 ELSE
226 p_resultout := 'COMPLETE:DEFAULT';
227 END IF;
228 ELSIF (p_funcmode = 'RESPOND') THEN
229 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
230 itemkey => p_itemkey,
231 aname => 'GMDOPGAP_CURR_PERFORMER',
232 avalue => l_approver);
233 END IF;
234 END;
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,'GMDOPGAP_OPERATION_ID'));
244 BEGIN
245 IF (p_funcmode = 'RUN') THEN
246 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPGAP_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,
256 p_itemkey IN VARCHAR2,
257 p_actid IN NUMBER,
258 p_funcmode IN VARCHAR2,
259 p_resultout OUT NOCOPY VARCHAR2) IS
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,'GMDOPGAP_OPERATION_ID'));
264 BEGIN
265 IF (p_funcmode = 'RUN') THEN
266 l_start_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPGAP_START_STATUS');
267 l_target_status := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPGAP_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,'GMDOPGAP_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,'GMDOPGAP_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,'GMDOPGAP_REQUSTER');
303 BEGIN
304 IF (p_funcmode = 'RESPOND') THEN
305 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
306 itemkey => p_itemkey,
307 aname => 'GMDOPGAP_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,'GMDOPGAP_COMMENT');
320 l_mesg_comment VARCHAR2(4000):=wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPGAP_DISP_COMMENT');
321 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDOPGAP_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 => 'GMDOPGAP_DISP_COMMENT',
335 avalue => l_mesg_comment);
336 -- WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
337 -- itemkey => p_itemkey,
338 -- aname => 'GMDOPGAP_HTML_DISP_COMMENT',
339 -- avalue => l_html_mesg);
340 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
341 itemkey => p_itemkey,
342 aname => 'GMDOPGAP_COMMENT',
343 avalue => l_comment);
344 END IF;
345 END;
346
347
348 END GMDOPGAP_wf_pkg;