[Home] [Help]
PACKAGE BODY: APPS.OE_SHIPPING_WF
Source
1 PACKAGE BODY OE_Shipping_WF as
2 /* $Header: OEXWSHPB.pls 120.4 2008/01/11 11:55:20 prpathak ship $ */
3
4
5 PROCEDURE Inc_Items_Freeze_Required(
6 itemtype in varchar2,
7 itemkey in varchar2,
8 actid in number,
9 funcmode in varchar2,
10 resultout in out nocopy varchar2 /* file.sql.39 change */
11 )
12 IS
13 l_item_type VARCHAR2(80);
14 l_ato_line_id NUMBER;
15 l_explosion_date DATE;
16 --
17 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
18 --
19
20 BEGIN
21 IF itemtype = OE_GLOBALS.G_WFI_LIN THEN
22
23 IF l_debug_level > 0 THEN
24 OE_DEBUG_PUB.Add('G_FREEZE_II:'||G_FREEZE_II);
25 END IF;
26
27 IF G_FREEZE_II = 'PICK RELEASE' THEN
28
29 SELECT item_type_code,ato_line_id,explosion_date
30 INTO l_item_type,l_ato_line_id,l_explosion_date
31 FROM oe_order_lines_all
32 WHERE line_id = to_number(itemkey);
33
34 IF l_debug_level > 0 THEN
35 OE_DEBUG_PUB.Add('Item Type:'||l_item_type);
36 OE_DEBUG_PUB.Add('Exp Date:'||l_explosion_date);
37 END IF;
38
39 IF l_item_type in ('MODEL','CLASS','KIT') AND
40 l_ato_line_id is NULL AND
41 l_explosion_date is NULL THEN
42
43 resultout := 'COMPLETE:Y';
44 IF l_debug_level > 0 THEN
45 OE_DEBUG_PUB.Add('Result set to YES!!');
46 END IF;
47 ELSE
48 resultout := 'COMPLETE:N';
49 IF l_debug_level > 0 THEN
50 OE_DEBUG_PUB.Add('Result set to No!!');
51 END IF;
52 END IF;
53
54 ELSE
55 resultout := 'COMPLETE:N';
56 END IF;
57
58 ELSE
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 -- item type is not a line
61 END IF;
62
63 EXCEPTION
64 WHEN OTHERS THEN
65 -- The line below records this function call in the error system
66 -- in the case of an exception.
67 WF_CORE.Context('OE_WF_SHIPPING', 'Inc_items_freeze_required',
68 itemtype, itemkey, to_char(actid), funcmode);
69 -- start data fix project
70 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
71 p_itemtype => itemtype,
72 p_itemkey => itemkey);
73 OE_STANDARD_WF.Save_Messages;
74 OE_STANDARD_WF.Clear_Msg_Context;
75 -- end data fix project
76 RAISE;
77
78 End Inc_items_Freeze_Required;
79
80
81 PROCEDURE Start_Shipping(
82 itemtype in varchar2,
83 itemkey in varchar2,
84 actid in number,
85 funcmode in varchar2,
86 resultout in out nocopy varchar2 /* file.sql.39 change */
87 )
88 IS
89 l_line_id NUMBER;
90 --bug 2979522
91 l_header_id NUMBER;
92 l_user_id NUMBER;
93 l_resp_id NUMBER;
94 l_resp_appl_id NUMBER;
95 l_resp_name VARCHAR2(100);
96 l_appl_name VARCHAR2(240);
97 l_descriptor VARCHAR2(1000);
98 l_doc_type VARCHAR2(100);
99 l_nid NUMBER;
100 l_from_role VARCHAR2(320);
101 l_validate_user NUMBER;
102 role_name VARCHAR2(320);
103 l_eid NUMBER;
104
105 l_top_model_line_id NUMBER;
106 l_return_status VARCHAR2(30);
107 l_result_out VARCHAR2(240);
108 l_msg_count NUMBER;
109 l_msg_data VARCHAR2(2000);
110 --
111 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
112 --
113 l_orig_sys_document_ref VARCHAR2(50);
114 l_orig_sys_line_ref VARCHAR2(50);
115 l_orig_sys_shipment_ref VARCHAR2(50);
116 l_change_sequence VARCHAR2(50);
117 l_source_document_type_id NUMBER;
118 l_source_document_id NUMBER;
119 l_source_document_line_id NUMBER;
120 l_order_source_id NUMBER;
121 --variable added for bug 3814076
122 l_itemkey NUMBER ;
123 --variable added for bug#6029753
124 l_if VARCHAR2(1);
125 l_oe_shipped_quantity NUMBER;
126 BEGIN
127
128 --
129 -- RUN mode - normal process execution
130 --
131 IF l_debug_level > 0 THEN
132 oe_debug_pub.add( 'ENTERING OE_SHIPPING_WF.START_SHIPPING '||ITEMTYPE||'/'||ITEMKEY , 1 ) ;
133 END IF;
134 oe_msg_pub.set_process_activity(actid);
135 if (funcmode = 'RUN') then
136
137
138 -- CODE CHANGES FOR BUG#6029753 STARTS HERE
139 BEGIN
140
141 SELECT nvl(wdd.oe_interfaced_flag, 'N')
142 INTO l_if
143 FROM wsh_delivery_details wdd
144 WHERE wdd.source_line_id = to_number(itemkey)
145 AND wdd.source_code = 'OE'
146 AND released_status = 'C' --Added for bug#6727843
147 GROUP BY oe_interfaced_flag;
148
149 IF l_if = 'N' then
150 resultout := 'NOTIFIED';
151 RETURN;
152 ELSIF l_if = 'Y' THEN
153 BEGIN
154 SELECT NVL(shipped_quantity,0)
155 INTO l_oe_shipped_quantity
156 FROM oe_order_lines
157 WHERE line_id= to_number(itemkey);
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 IF l_debug_level > 0 THEN
161 oe_debug_pub.add( 'ERROR: INVALID CONTEXT ATTRIBUTES' , 5 ) ;
162 END IF;
163 resultout := 'NOTIFIED';
164 RETURN;
165 END;
166
167 IF l_oe_shipped_quantity > 0 THEN
168 resultout := 'COMPLETE:SHIP_CONFIRM';
169 RETURN;
170 END IF;
171 END IF;
172
173 EXCEPTION
174 WHEN TOO_MANY_ROWS THEN
175 resultout := 'NOTIFIED';
176 RETURN;
177 WHEN OTHERS THEN
178 NULL;
179 END;
180 -- CODE CHANGES FOR BUG#6029753 ENDS HERE
181
182 -- If it is BULK Mode then no need to query these values from Database
183
184 IF OE_BULK_WF_UTIL.G_LINE_INDEX IS NOT NULL THEN
185 IF l_debug_level > 0 THEN
186 oe_debug_pub.add('SHP BULK MODE' , 5 ) ;
187 END IF;
188 -- bug 4070931 starts
189 IF OE_SHIPPING_INTEGRATION_PVT.G_BULK_WSH_INTERFACE_CALLED = TRUE THEN
190 app_exception.raise_exception;
191 END IF; -- bug 4070931 ends
192
193 l_line_id := OE_BULK_ORDER_PVT.G_LINE_REC.line_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
194 l_top_model_line_id := OE_BULK_ORDER_PVT.G_LINE_REC.top_model_line_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
195
196 l_header_id := OE_BULK_ORDER_PVT.G_LINE_REC.header_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
197
198 l_order_source_id := OE_BULK_ORDER_PVT.G_LINE_REC.order_source_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
199
200 l_orig_sys_document_ref := OE_BULK_ORDER_PVT.G_LINE_REC.orig_sys_document_ref(OE_BULK_WF_UTIL.G_LINE_INDEX);
201
202 l_orig_sys_line_ref := OE_BULK_ORDER_PVT.G_LINE_REC.orig_sys_line_ref(OE_BULK_WF_UTIL.G_LINE_INDEX);
203
204 l_orig_sys_shipment_ref := OE_BULK_ORDER_PVT.G_LINE_REC.orig_sys_shipment_ref(OE_BULK_WF_UTIL.G_LINE_INDEX);
205
206 l_change_sequence := OE_BULK_ORDER_PVT.G_LINE_REC.change_sequence(OE_BULK_WF_UTIL.G_LINE_INDEX);
207
208 /* Commenting for Bug 3319095
209 l_source_document_type_id := OE_BULK_ORDER_PVT.G_LINE_REC.source_document_type_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
210
211 l_source_document_id := OE_BULK_ORDER_PVT.G_LINE_REC.source_document_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
212
213 l_source_document_line_id := OE_BULK_ORDER_PVT.G_LINE_REC.source_document_line_id(OE_BULK_WF_UTIL.G_LINE_INDEX);
214 */
215
216 ELSE
217 -- # 2416391, locking issue
218 SELECT line_id, top_model_line_id
219 , header_id
220 , order_source_id
221 , orig_sys_document_ref
222 , orig_sys_line_ref
223 , orig_sys_shipment_ref
224 , change_sequence
225 , source_document_type_id
226 , source_document_id
227 , source_document_line_id
228 INTO l_line_id, l_top_model_line_id
229 , l_header_id
230 , l_order_source_id
231 , l_orig_sys_document_ref
232 , l_orig_sys_line_ref
233 , l_orig_sys_shipment_ref
234 , l_change_sequence
235 , l_source_document_type_id
236 , l_source_document_id
237 , l_source_document_line_id
238 FROM oe_order_lines
239 WHERE line_id = to_number(itemkey);
240 END IF; -- bulk mode or not
241
242 OE_MSG_PUB.set_msg_context(
243 p_entity_code => 'LINE'
244 ,p_entity_id => l_line_id
245 ,p_header_id => l_header_id
246 ,p_line_id => l_line_id
247 ,p_order_source_id => l_order_source_id
248 ,p_orig_sys_document_ref => l_orig_sys_document_ref
249 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
250 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
251 ,p_change_sequence => l_change_sequence
252 ,p_source_document_type_id => l_source_document_type_id
253 ,p_source_document_id => l_source_document_id
254 ,p_source_document_line_id => l_source_document_line_id
255 );
256
257 OE_SHIPPING_INTEGRATION_PVT.G_DEBUG_MSG := NULL;
258 OE_SHIPPING_INTEGRATION_PVT.G_DEBUG_CALL := 1;
259
260 IF l_debug_level > 0 THEN
261 oe_debug_pub.add( 'TOP MODEL LINE ID : '||L_TOP_MODEL_LINE_ID , 3 ) ;
262 END IF;
263
264 IF OE_BULK_WF_UTIL.G_LINE_INDEX IS NULL THEN
265
266 IF nvl(l_top_model_line_id,0) <> 0 THEN
267
268 IF l_debug_level > 0 THEN
269 oe_debug_pub.add( 'BEFORE LOCKING THE TOP LINE '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
270 END IF;
271 SELECT line_id, top_model_line_id
272 INTO l_line_id, l_top_model_line_id
273 FROM oe_order_lines_all
274 WHERE line_id = l_top_model_line_id
275 FOR UPDATE NOWAIT;
276 IF l_debug_level > 0 THEN
277 oe_debug_pub.add( 'TOP LINE LOCKED AT '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
278 END IF;
279
280 ELSE
281 --bug 3814076
282 l_itemkey := to_number(itemkey);
283 SELECT line_id
284 INTO l_line_id
285 FROM oe_order_lines_all
286 WHERE line_id = l_itemkey
287 FOR UPDATE NOWAIT;
288
289 END IF;
290 END IF; -- no bulk mode
291
292 l_line_id := to_number(itemkey);
293
294 IF l_debug_level > 0 THEN
295 oe_debug_pub.add( 'CALLING OE_SHIPPING_INTEGRATION_PVT.PROCESS_SHIPPING_ACTIVITY '||TO_CHAR ( L_LINE_ID ) , 2 ) ;
296 END IF;
297 OE_Shipping_Integration_PVT.Process_Shipping_Activity
298 ( p_api_version_number => 1.0
299 , p_line_id => l_line_id
300 , x_result_out => l_result_out
301 , x_return_status => l_return_status
302 , x_msg_count => l_msg_count
303 , x_msg_data => l_msg_data
304 );
305 OE_SHIPPING_INTEGRATION_PVT.G_DEBUG_CALL := 0;
306
307 IF l_debug_level > 0 THEN
308 oe_debug_pub.add( 'RETURNED FROM OE_SHIPPING_INTEGRATION_PVT.PROCESS_SHIPPING_ACTIVITY '||L_RETURN_STATUS , 2 ) ;
309 END IF;
310
311
312 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
313 app_exception.raise_exception;
314 END IF;
315
316 resultout := l_result_out;
317 OE_STANDARD_WF.Clear_Msg_Context;
318 return;
319
320 end if; -- End for 'RUN' mode
321
322 --
323 -- CANCEL mode - activity 'compensation'
324 --
325 -- This is an event point is called with the effect of the activity must
326 -- be undone, for example when a process is reset to an earlier point
327 -- due to a loop back.
328 --
329 if (funcmode = 'CANCEL') then
330
331 -- your cancel code goes here
332 null;
333
334 -- no result needed
335 resultout := 'COMPLETE';
336 return;
337 end if;
338
339 /* bug 2979522 */
340 if (funcmode = 'SKIP') then
341 BEGIN
342 -- WF require a COMPLETE result out
343 IF G_DEV_SKIP = 'Y' THEN
344 resultout := 'COMPLETE';
345 ELSE
346
347 insert into ont_wf_skip_log(creation_date, line_id, activity_id, user_id, responsibility_id, application_id) values (sysdate, to_number(itemkey), actid, FND_GLOBAL.user_id, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);
348
349 resultout := 'COMPLETE';
350 select header_id
351 into l_header_id
352 from oe_order_lines_all
353 where line_id = to_number(itemkey);
354
355 oe_msg_pub.initialize;
356 oe_msg_pub.set_process_activity(actid);
357 oe_msg_pub.set_msg_context(p_header_id=>l_header_id, p_line_id=>to_number(itemkey));
358 fnd_message.set_name('ONT', 'ONT_WF_NO_SKIP_SHIP');
359 oe_msg_pub.add;
360 OE_STANDARD_WF.save_messages;
361 --log a OM message in our message stack
362
363 l_user_id := FND_GLOBAL.USER_ID;
364 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
365 l_resp_id := FND_GLOBAL.RESP_ID;
366
367
368 select employee_id
369 into l_eid
370 from fnd_user
371 where user_id =l_user_id;
372
373 IF l_eid is null THEN
374 select name
375 into role_name
376 from wf_roles
377 where orig_system='FND_USR'
378 and orig_system_id = l_user_id;
379 ELSE
380 select name
381 into role_name
382 from wf_roles
383 where orig_system='PER'
384 and orig_system_id = l_eid;
385 END IF;
386
387
388 select RESPONSIBILITY_NAME
389 into l_resp_name
390 from FND_RESPONSIBILITY_VL
391 where RESPONSIBILITY_ID=l_resp_id
392 and APPLICATION_ID=l_resp_appl_id;
393
394 select APPLICATION_NAME
395 into l_appl_name
396 from fnd_application_vl
397 where APPLICATION_ID = l_resp_appl_id;
398
399 l_from_role := 'SYSADMIN';
400
401 BEGIN
402 select 1
403 into l_validate_user
404 from wf_roles
405 where name = l_from_role;
406
407 EXCEPTION
408 WHEN OTHERS THEN
409 l_from_role := null; -- do not set FROM_ROLE then
410 END;
411
412 -- notification to the skipper
413 wf_engine.SetItemAttrText(itemtype, itemkey, 'NOTIFICATION_FROM_ROLE', l_from_role);
414
415 l_nid := WF_NOTIFICATION.Send(role_name, 'OEOL', 'WORKFLOW_SKIPPED_MSG');
416
417 OE_ORDER_WF_UTIL.Set_Line_Descriptor(l_nid, null, l_descriptor, l_doc_type);
418 WF_NOTIFICATION.SetAttrText(l_nid, 'LINE_DESCRIPTOR', l_descriptor);
419
420
421 -- l_from_role = SYSADMIN
422 -- notification to SYSADMIN
423 l_nid := WF_NOTIFICATION.Send(l_from_role, 'OEOL', 'WORKFLOW_SKIPPED_ADMIN_MSG');
424
425 OE_ORDER_WF_UTIL.Set_Line_Descriptor(l_nid, null, l_descriptor, l_doc_type);
426
427 WF_NOTIFICATION.SetAttrText(l_nid, 'USER_NAME', role_name);
428 WF_NOTIFICATION.SetAttrText(l_nid, 'RESP_NAME', l_resp_name);
429 WF_NOTIFICATION.SetAttrText(l_nid, 'APPL_NAME', l_appl_name);
430 WF_NOTIFICATION.SetAttrText(l_nid, 'LINE_DESCRIPTOR', l_descriptor);
431 END IF; --G_DEV_SKIP == 'N'
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 null;
436 END;
437
438 end if;
439
440
441
442 --
443 -- Other execution modes may be created in the future. Your
444 -- activity will indicate that it does not implement a mode
445 -- by returning null
446 --
447 -- resultout := '';
448 -- return;
449
450 exception
451 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
452 -- some one else is currently working on the line
453 IF l_debug_level > 0 THEN
454 oe_debug_pub.add('OEXWSHPB.pls: unable to lock the lines',1);
455 END IF;
456 resultout := 'DEFERRED';
457
458 when others then
459 -- The line below records this function call in the error system
460 -- in the case of an exception.
461 wf_core.context('OE_Shipping_WF', 'Shipping_Activity',
462 itemtype, itemkey, to_char(actid), funcmode,
463 OE_SHIPPING_INTEGRATION_PVT.G_DEBUG_MSG);
464 OE_SHIPPING_INTEGRATION_PVT.G_DEBUG_CALL := 0;
465 -- start data fix project
466 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
467 p_itemtype => itemtype,
468 p_itemkey => itemkey);
469 -- end data fix project
470 OE_STANDARD_WF.Save_Messages;
471 OE_STANDARD_WF.Clear_Msg_Context;
472 raise;
473 END START_SHIPPING;
474
475 END OE_Shipping_WF;