1 PACKAGE BODY OE_Fulfill_WF as
2 /* $Header: OEXWFULB.pls 120.4.12020000.2 2012/11/14 05:18:31 spothula ship $ */
3
4 /*-------------------------------------------------------------
5 PROCEDURE Check_Wait_To_Fulfill_Line
6 --------------------------------------------------------------*/
7
8 PROCEDURE Check_Wait_To_Fulfill_Line
9 (itemtype IN VARCHAR2
10 ,itemkey IN VARCHAR2
11 ,actid IN NUMBER
12 ,funcmode IN VARCHAR2
13 ,resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
14 )
15 IS
16
17 l_item_type VARCHAR2(80);
18 l_shippable_flag VARCHAR2(1);
19 l_service_ship_flag VARCHAR2(1);
20 l_header_id NUMBER;
21 l_service_ref_line_id NUMBER;
22 l_service_header_id NUMBER;
23 l_top_model_line_id NUMBER;
24 -- Bug 4875015
25 l_service_reference_type_code VARCHAR2(30);
26
27 --
28 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
29 --
30
31 BEGIN
32
33 --
34 -- RUN mode - normal process execution
35 --
36
37 IF l_debug_level > 0 THEN
38 oe_debug_pub.add('Entering Check Wait to Fulfill Line'||itemkey,1 ) ;
39 END IF;
40
41 IF (funcmode = 'RUN') THEN
42
43 OE_STANDARD_WF.Set_Msg_Context(actid);
44
45 IF itemtype = OE_GLOBALS.G_WFI_LIN THEN
46 -- Bug 4875015
47 SELECT item_type_code,shippable_flag,top_model_line_id, service_reference_type_code
48 INTO l_item_type,l_shippable_flag,l_top_model_line_id, l_service_reference_type_code
49 FROM oe_order_lines_all
50 WHERE line_id = to_number(itemkey);
51
52 IF l_top_model_line_id IS NOT NULL AND
53 nvl(l_shippable_flag,'N') = 'N' THEN
54
55 -- Do not Hold Service and Non Shippable lines
56 -- part of configurations.
57
58 resultout := 'COMPLETE:N';
59
60 IF l_debug_level > 0 THEN
61 OE_DEBUG_PUB.Add('Result set to No, Line part of configuration!!');
62 END IF;
63
64 ELSIF l_item_type = 'SERVICE' AND
65 nvl(l_shippable_flag,'N') = 'N' THEN
66 -- Bug 4875015
67 IF l_service_reference_type_code = 'ORDER' THEN
68
69 SELECT header_id,service_reference_line_id
70 INTO l_header_id,l_service_ref_line_id
71 FROM oe_order_lines_all
72 WHERE line_id = to_number(itemkey)
73 AND service_reference_type_code = 'ORDER';
74
75
76 SELECT header_id,shippable_flag
77 INTO l_service_header_id,l_service_ship_flag
78 FROM oe_order_lines_all
79 WHERE line_id = l_service_ref_line_id;
80
81
82 IF l_header_id = l_service_header_id AND
83 nvl(l_service_ship_flag,'N') = 'Y' THEN
84
85 IF l_debug_level > 0 THEN
86 OE_DEBUG_PUB.Add('Result set to No!! Service in same order');
87 END IF;
88
89 -- Do not hold services lines attached
90 -- to lines with in the same order.
91
92 resultout := 'COMPLETE:N';
93 ELSE
94
95 IF l_debug_level > 0 THEN
96 OE_DEBUG_PUB.Add('Result set to Yes!! Service not in same order');
97 OE_DEBUG_PUB.Add('or Line is Service attached to Non Shippable Line');
98 END IF;
99
100 -- Hold services lines attached
101 -- to lines not in the same order.
102
103 resultout := 'COMPLETE:Y';
104 END IF;
105
106 ELSE -- Bug 4875015
107 -- resultout := 'COMPLETE:N'; --commneted for bug12958565
108 resultout := 'COMPLETE:Y'; --added for bug12958565
109 IF l_debug_level > 0 THEN
110 -- OE_DEBUG_PUB.Add('Result set to N. service reference type is not ORDER', 1); --commneted for bug12958565
111 OE_DEBUG_PUB.Add('Result set to Y. service reference type is Customer Product', 1); --added for bug12958565
112 END IF;
113 END IF; -- Bug 4875015 End
114
115 ELSIF nvl(l_shippable_flag,'N') = 'N' THEN
116
117 -- Hold Non Shippable Lines.
118
119 resultout := 'COMPLETE:Y';
120
121 IF l_debug_level > 0 THEN
122 OE_DEBUG_PUB.Add('Result set to YES!! Non Shippable Line');
123 END IF;
124
125 ELSE
126
127 resultout := 'COMPLETE:N';
128
129 IF l_debug_level > 0 THEN
130 OE_DEBUG_PUB.Add('Result set to No!!Shippable Line');
131 END IF;
132 END IF;
133
134 ELSE
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 -- item type is not a line
137 END IF;
138
139 END IF; -- End for 'RUN' mode
140
141 --
142 -- CANCEL mode - activity 'compensation'
143 --
144 -- This is an event point is called with the effect of the activity must
145 -- be undone, for example when a process is reset to an earlier point
146 -- due to a loop back.
147 --
148 IF (funcmode = 'CANCEL') THEN
149 -- your cancel code goes here
150 null;
151
152 -- no result needed
153 resultout := 'COMPLETE';
154 return;
155 END IF;
156
157
158 --
159 -- Other execution modes may be created in the future. Your
160 -- activity will indicate that it does not implement a mode
161 -- by returning null
162 --
163 -- resultout := '';
164 -- return;
165
166 EXCEPTION
167 WHEN OTHERS THEN
168 -- The line below records this function call in the error system
169 -- in the case of an exception.
170 wf_core.context('OE_FULFILL_WF', 'Check_Wait_To_Fulfill_Line',
171 itemtype, itemkey, to_char(actid), funcmode);
172 -- start data fix project
173 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
174 p_itemtype => itemtype,
175 p_itemkey => itemkey);
176 -- end data fix project
177 OE_STANDARD_WF.Save_Messages;
178 OE_STANDARD_WF.Clear_Msg_Context;
179 IF l_debug_level > 0 THEN
180 OE_DEBUG_PUB.Add('When Other in Check Wait Fulfill'||sqlerrm);
181 END IF;
182 raise;
183 END Check_Wait_To_Fulfill_Line;
184
185 /*-------------------------------------------------------------
186 PROCEDURE Complete_Fulfill_Eligible_Line
187 Description: This Procedure will complete the Fulfill Line
188 Eligible Activity for a line. This Procedure first
189 Checks whether the line is Notified for the activity
190 If Yes complete the activity else return.
191 Lock the Row before you complete the activity.
192 --------------------------------------------------------------*/
193
194 PROCEDURE Complete_Fulfill_Eligible_Line
195 (p_line_id IN NUMBER
196 ,x_return_status OUT NOCOPY VARCHAR2)
197 IS
198
199 l_activity_status VARCHAR2(30);
200 l_line_rec OE_Order_PUB.Line_Rec_Type;
201 l_return_status VARCHAR2(1);
202
203 --
204 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
205 --
206
207 BEGIN
208
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210
211 -- Check whether the line is eligible to Fulfill
212
213 IF l_debug_level > 0 THEN
214 OE_DEBUG_PUB.Add('Entering Complete Fulfill Eligible Line ');
215 END IF;
216
217 BEGIN
218 SELECT WIAS.Activity_Status
219 INTO l_activity_status
220 FROM wf_item_activity_statuses WIAS,
221 wf_process_activities WPA
222 WHERE WIAS.Process_Activity = WPA.instance_id
223 AND WPA.activity_name = 'FULFILL_LINE_ELIGIBLE'
224 AND WIAS.item_type = 'OEOL'
225 AND WIAS.item_key = to_char(p_line_id)
226 AND WIAS.activity_status = 'NOTIFIED' ;
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 x_return_status := FND_API.G_RET_STS_ERROR;
230
231 IF l_debug_level > 0 THEN
232 OE_DEBUG_PUB.Add('Line Not Eligible for Fulfillment');
233 END IF;
234 RETURN;
235 END;
236
237 -- Lock the Row
238
239 IF l_debug_level > 0 THEN
240 oe_debug_pub.add('Before Calling Lock Row'||p_line_id);
241 END IF;
242
243 OE_Line_Util.lock_Row
244 ( p_line_id => p_line_id
245 ,p_x_line_rec => l_line_rec
246 ,x_return_status => l_return_status
247 );
248
249 IF l_debug_level > 0 THEN
250 oe_debug_pub.add('After Calling Lock Row'||l_return_status);
251 END IF;
252
253 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
256 RAISE FND_API.G_EXC_ERROR;
257 END IF;
258
259 -- Complete the work flow Fulfill Line Eligible Activity
260 BEGIN --14599978
261 WF_ENGINE.CompleteActivityInternalName (
262 Itemtype => 'OEOL',
263 Itemkey => to_char (p_line_id),
264 Activity => 'FULFILL_LINE_ELIGIBLE',
265 Result => OE_GLOBALS.G_WFR_COMPLETE,
266 raise_engine_exception => TRUE );
267 exception
268 WHEN OTHERS THEN
269 oe_debug_pub.add('Workflow complete activity failed. Raising');
270 RAISE ;
271 END ;
272
273 IF l_debug_level > 0 THEN
274 OE_DEBUG_PUB.Add('Exiting Complete Fulfill Eligible Line ');
275 END IF;
276
277 EXCEPTION
278 WHEN FND_API.G_EXC_ERROR THEN
279 x_return_status := FND_API.G_RET_STS_ERROR;
280
281 IF l_debug_level > 0 THEN
282 OE_DEBUG_PUB.Add('Expected Error in Fulfill Eligible'||sqlerrm);
283 END IF;
284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286
287 IF l_debug_level > 0 THEN
288 OE_DEBUG_PUB.Add('Un Expected Error in Fulfill Eligible');
289 END IF;
290 WHEN OTHERS THEN
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
292
293 IF l_debug_level > 0 THEN
294 OE_DEBUG_PUB.Add('Un Expected Error in Fulfill Eligible'||sqlerrm);
295 END IF;
296
297 END Complete_Fulfill_Eligible_Line;
298
299
300
301 /*-------------------------------------------------------------
302 PROCEDURE Start_Fulfillment
303 --------------------------------------------------------------*/
304
305 PROCEDURE Start_Fulfillment(
306 itemtype in varchar2,
307 itemkey in varchar2,
308 actid in number,
309 funcmode in varchar2,
310 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
311 IS
312 l_line_id NUMBER;
313 l_return_status VARCHAR2(30);
314 l_result_out VARCHAR2(240);
315 l_msg_count NUMBER;
316 l_msg_data VARCHAR2(2000);
317 --
318 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
319 --
320
321 l_header_id NUMBER;
322 l_order_source_id NUMBER;
323 l_orig_sys_document_ref VARCHAR2(50);
324 l_orig_sys_line_ref VARCHAR2(50);
325 l_orig_sys_shipment_ref VARCHAR2(50);
326 l_change_sequence VARCHAR2(50);
327 l_source_document_type_id NUMBER;
328 l_source_document_id NUMBER;
329 l_source_document_line_id NUMBER;
330
331 BEGIN
332
333 --
334 -- RUN mode - normal process execution
335 --
336 IF l_debug_level > 0 THEN -- debug level changed to 0.5 for bug 13435459
337 oe_debug_pub.add( 'ENTERING OE_FULFILL_WF.START_FULFILLMENT '||ITEMTYPE||'/'||ITEMKEY , 0.5 ) ;
338 END IF;
339 OE_STANDARD_WF.Set_Msg_Context(actid);
340
341 if (funcmode = 'RUN') then
342
343
344 l_line_id := to_number(itemkey);
345
346 SELECT header_id
347 ,order_source_id
348 ,orig_sys_document_ref
349 ,orig_sys_line_ref
350 ,orig_sys_shipment_ref
351 ,change_sequence
352 ,source_document_type_id
353 ,source_document_id
354 ,source_document_line_id
355 INTO l_header_id
356 ,l_order_source_id
357 ,l_orig_sys_document_ref
358 ,l_orig_sys_line_ref
359 ,l_orig_sys_shipment_ref
360 ,l_change_sequence
361 ,l_source_document_type_id
362 ,l_source_document_id
363 ,l_source_document_line_id
364 FROM oe_order_lines_all
365 WHERE line_id = l_line_id;
366
367 OE_MSG_PUB.set_msg_context(
368 p_entity_code => 'LINE'
369 ,p_entity_id => l_line_id
370 ,p_header_id => l_header_id
371 ,p_line_id => l_line_id
372 ,p_order_source_id => l_order_source_id
373 ,p_orig_sys_document_ref => l_orig_sys_document_ref
374 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
375 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
376 ,p_change_sequence => l_change_sequence
377 ,p_source_document_type_id => l_source_document_type_id
378 ,p_source_document_id => l_source_document_id
379 ,p_source_document_line_id => l_source_document_line_id
380 );
381
382 OE_Line_Fullfill.G_DEBUG_MSG := NULL;
383
384 IF l_debug_level > 0 THEN
385 oe_debug_pub.add( 'call OE_LINE_FULLFILL.Process_Fulfillment() for line'||TO_CHAR ( L_LINE_ID ) , 2 ) ;
386 END IF;
387 OE_Line_Fullfill.Process_Fulfillment
388 ( p_api_version_number => 1.0
389 , p_line_id => l_line_id
390 , p_activity_id => actid
391 , x_result_out => l_result_out
392 , x_return_status => l_return_status
393 , x_msg_count => l_msg_count
394 , x_msg_data => l_msg_data
395 );
396
397 IF l_debug_level > 0 THEN
398 -- debug level changed to 0.5 for bug 13435459
399 oe_debug_pub.add( 'RETURNED FROM OE_LINE_FULLFILL.PROCESS_FULFILLMENT '||L_RETURN_STATUS , 0.5 );
400 END IF;
401
402 IF l_return_status = 'DEFERRED' THEN
403 IF l_debug_level > 0 THEN
404 oe_debug_pub.add( 'WF activity deferred') ;
405 END IF;
406 --resultout := 'DEFERRED:'||to_char(sysdate+0.02, wf_engine.date_format); --bug 4189737
407 resultout := 'DEFERRED';
408 OE_STANDARD_WF.Clear_Msg_Context;
409 return;
410 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
411 app_exception.raise_exception;
412 END IF;
413 IF l_debug_level > 0 THEN
414 oe_debug_pub.add( '*** completed fulfillment *** for line '||l_line_id,1);
415 END IF;
416 resultout := l_result_out;
417 OE_STANDARD_WF.Clear_Msg_Context;
418 return;
419
420 end if; -- End for 'RUN' mode
421
422 --
423 -- CANCEL mode - activity 'compensation'
424 --
425 -- This is an event point is called with the effect of the activity must
426 -- be undone, for example when a process is reset to an earlier point
427 -- due to a loop back.
428 --
429 if (funcmode = 'CANCEL') then
430
431 -- your cancel code goes here
432 null;
433
434 -- no result needed
435 resultout := 'COMPLETE';
436 return;
437 end if;
438
439
440 --
441 -- Other execution modes may be created in the future. Your
442 -- activity will indicate that it does not implement a mode
443 -- by returning null
444 --
445 -- resultout := '';
446 -- return;
447
448 exception
449
450 when others then
451 IF l_debug_level > 0 THEN
452 oe_debug_pub.add( 'Others exception in OEXWFULB.pls' ) ; -- bug 4189737
453 oe_debug_pub.add('Exception is '||sqlerrm,0.5); -- bug 4189737
454 END IF;
455 -- The line below records this function call in the error system
456 -- in the case of an exception.
457 wf_core.context('OE_Fulfill_WF', 'Fulfillment',
458 itemtype, itemkey, to_char(actid), funcmode,
459 OE_Line_Fullfill.G_DEBUG_MSG);
460 -- start data fix project
461 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
462 p_itemtype => itemtype,
463 p_itemkey => itemkey);
464 -- end data fix project
465 OE_STANDARD_WF.Save_Messages;
466 OE_STANDARD_WF.Clear_Msg_Context;
467 raise;
468 END Start_Fulfillment;
469
470 END OE_Fulfill_WF;
[Home] [Help]