[Home] [Help]
PACKAGE BODY: APPS.OE_LINE_FULLFILL
Source
1 PACKAGE BODY OE_LINE_FULLFILL AS
2 /* $Header: OEXVFULB.pls 120.24.12020000.8 2013/04/09 08:00:43 spothula ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Line_Fullfill';
5 G_FULFILL_WITH_ACTIVITY CONSTANT VARCHAR2(30) := 'With Activity';
6 G_FULFILL_NO_ACTIVITY CONSTANT VARCHAR2(30) := 'No Activity';
7 G_BINARY_LIMIT CONSTANT NUMBER :=OE_GLOBALS.G_BINARY_LIMIT; --7827727
8 g_set_tbl processed_set;
9
10 -------------------------------------------------------------------
11 -- Added 09-DEC-2002
12 -- LOCAL PROCEDURE Update_Blanket_Qty
13 -- Updates fulfilled quantity on the blanket line as release
14 -- lines referencing this blanket line are fulfilled. For RMAs, it
15 -- updates returned quantity as return lines are received.
16 -------------------------------------------------------------------
17 -- 5126873 - Blankets not supported for fulfilled quantity2 as per initial INV design from PM so no need to calculate fulfilled quantity2
18 PROCEDURE Update_Blanket_Qty
19 (p_line_rec IN OE_Order_PUB.Line_Rec_Type
20 ,p_fulfilled_quantity IN NUMBER DEFAULT NULL
21 )
22 IS
23
24 l_fulfilled_quantity NUMBER := p_fulfilled_quantity;
25 l_blanket_uom VARCHAR2(30);
26 l_amount NUMBER := 0;
27 l_order_currency VARCHAR2(30);
28 l_blanket_currency VARCHAR2(30);
29 l_conversion_type VARCHAR2(30);
30
31 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
32
33 BEGIN
34
35 IF l_fulfilled_quantity IS NULL THEN
36
37 l_fulfilled_quantity := p_line_rec.fulfilled_quantity;
38
39 END IF;
40
41
42
43 if l_debug_level > 0 then
44 oe_debug_pub.add('blanket number : '||p_line_rec.blanket_number);
45 oe_debug_pub.add('blanket line number : '||p_line_rec.blanket_line_number);
46 oe_debug_pub.add('UOM : '||p_line_rec.order_quantity_uom);
47 oe_debug_pub.add('Item Type : '||p_line_rec.item_type_code);
48 oe_debug_pub.add('Line Category : '||p_line_rec.line_category_code);
49 end if;
50
51
52 -- 1. COMPUTE AMOUNT INTO Blanket Currency
53
54 OE_Order_Cache.Load_Order_Header(p_line_rec.header_id);
55 l_order_currency := OE_Order_Cache.g_header_rec.transactional_curr_code;
56
57 l_amount := nvl(l_fulfilled_quantity,0) *
58 nvl(p_line_rec.unit_selling_price,0);
59
60 SELECT transactional_curr_code, conversion_type_code
61 INTO l_blanket_currency, l_conversion_type
62 FROM OE_BLANKET_HEADERS
63 WHERE ORDER_NUMBER = p_line_rec.blanket_number
64 AND SALES_DOCUMENT_TYPE_CODE = 'B';
65
66 IF l_order_currency <> l_blanket_currency THEN
67 l_amount := OE_Blkt_Release_Util.Convert_Amount
68 (p_from_currency => l_order_currency
69 ,p_to_currency => l_blanket_currency
70 ,p_conversion_date => sysdate
71 ,p_conversion_type => l_conversion_type
72 ,p_amount => l_amount
73 );
74 END IF;
75
76 if l_debug_level > 0 then
77 oe_debug_pub.add('Order Currency : '||l_order_currency);
78 oe_debug_pub.add('Blanket Currency : '||l_blanket_currency);
79 oe_debug_pub.add('Fulfilled Amount : '||l_amount);
80 end if;
81
82
83 -- 2. CONVERT fulfilled quantity into blanket UOM
84
85 -- New Data Model Changes for Blanket Orders
86
87 SELECT L.Order_Quantity_UOM
88 INTO l_blanket_uom
89 FROM OE_BLANKET_LINES L,OE_BLANKET_LINES_EXT BL
90 WHERE BL.ORDER_NUMBER = p_line_rec.blanket_number
91 AND BL.LINE_NUMBER = p_line_rec.blanket_line_number
92 AND L.LINE_ID = BL.LINE_ID
93 AND L.SALES_DOCUMENT_TYPE_CODE = 'B';
94
95 -- Blanket uom is null, quantities are not recorded on blanket
96 IF l_blanket_uom IS NULL THEN
97
98 l_fulfilled_quantity := null;
99
100 -- Uoms are different, convert to blanket uom
101 ELSIF p_line_rec.order_quantity_uom <> l_blanket_uom THEN
102
103 l_fulfilled_quantity := OE_Order_Misc_Util.Convert_UOM
104 (p_item_id => p_line_rec.inventory_item_id
105 ,p_from_uom_code => p_line_rec.order_quantity_uom
106 ,p_to_uom_code => l_blanket_uom
107 ,p_from_qty => l_fulfilled_quantity
108 );
109
110 END IF;
111
112 if l_debug_level > 0 then
113 oe_debug_pub.add('blkt uom :'||l_blanket_uom);
114 oe_debug_pub.add('fulfilled qty :'||l_fulfilled_quantity);
115 end if;
116
117
118 -- 3. UPDATE quantity/amount on blanket line and header
119
120 IF p_line_rec.line_category_code = 'ORDER' THEN
121
122 UPDATE oe_blanket_lines l
123 SET l.lock_control = l.lock_control + 1
124 WHERE L.Line_Id IN (SELECT Line_Id FROM oe_blanket_lines_ext bl
125 WHERE bl.order_number = p_line_rec.blanket_number
126 AND bl.line_number = p_line_rec.blanket_line_number)
127 AND l.sales_document_type_code = 'B';
128
129 -- Bug 2734877
130 -- Update fulfilled qty only if uom is not null
131 -- If blanket uom is null, fulfilled qty should also be null
132 IF l_blanket_uom IS NULL THEN
133 UPDATE oe_blanket_lines_ext ble
134 SET ble.fulfilled_amount = nvl(ble.fulfilled_amount,0) +
135 l_amount
136 WHERE ble.order_number = p_line_rec.blanket_number
137 AND ble.line_number = p_line_rec.blanket_line_number;
138
139 ELSE
140 UPDATE oe_blanket_lines_ext ble
141 SET ble.fulfilled_quantity = nvl(ble.fulfilled_quantity,0) +
142 l_fulfilled_quantity
143 ,ble.fulfilled_amount = nvl(ble.fulfilled_amount,0) +
144 l_amount
145 WHERE ble.order_number = p_line_rec.blanket_number
146 AND ble.line_number = p_line_rec.blanket_line_number;
147 END IF;
148
149 UPDATE oe_blanket_headers_ext bhe
150 SET bhe.fulfilled_amount = nvl(bhe.fulfilled_amount,0) +
151 l_amount
152 WHERE bhe.order_number = p_line_rec.blanket_number;
153
154 UPDATE oe_blanket_headers bh
155 SET bh.lock_control = bh.lock_control + 1
156 WHERE bh.order_number = p_line_rec.blanket_number
157 AND bh.sales_document_type_code = 'B';
158
159 ELSIF p_line_rec.line_category_code = 'RETURN' THEN
160
161 -- Bug 2734877
162 -- Update returned qty only if uom is not null
163 -- If blanket uom is null, returned qty should also be null
164 IF l_blanket_uom IS NULL THEN
165 UPDATE oe_blanket_lines_ext ble
166 SET ble.returned_amount = nvl(ble.returned_amount,0) +
167 l_amount
168 WHERE ble.order_number = p_line_rec.blanket_number
169 AND ble.line_number = p_line_rec.blanket_line_number;
170
171 ELSE
172 UPDATE oe_blanket_lines_ext ble
173 SET ble.returned_quantity = nvl(ble.returned_quantity,0) +
174 l_fulfilled_quantity
175 ,ble.returned_amount = nvl(ble.returned_amount,0) +
176 l_amount
177 WHERE ble.order_number = p_line_rec.blanket_number
178 AND ble.line_number = p_line_rec.blanket_line_number;
179 END IF;
180
181 UPDATE oe_blanket_lines bl
182 SET bl.lock_control = bl.lock_control + 1
183 WHERE bl.line_Id IN (SELECT Line_Id FROM oe_blanket_lines_ext bl
184 WHERE bl.order_number = p_line_rec.blanket_number
185 AND bl.line_number = p_line_rec.blanket_line_number)
186 AND bl.sales_document_type_code = 'B';
187
188 UPDATE oe_blanket_headers_ext bhe
189 SET bhe.returned_amount = nvl(bhe.returned_amount,0) +
190 l_amount
191 WHERE bhe.order_number = p_line_rec.blanket_number;
192
193 UPDATE oe_blanket_headers bh
194 SET bh.lock_control = bh.lock_control + 1
195 WHERE bh.order_number = p_line_rec.blanket_number
196 AND bh.sales_document_type_code = 'B';
197
198 END IF;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 if l_debug_level > 0 then
203 oe_debug_pub.add('Update_Blanket_Qty: Others Error', 1);
204 oe_debug_pub.ADD('Error: '||substr(sqlerrm,1,200),1);
205 end if;
206 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
207 THEN
208 OE_MSG_PUB.Add_Exc_Msg
209 ( G_PKG_NAME
210 , 'Update_Blanket_Qty'
211 );
212 END IF;
213 G_DEBUG_MSG := G_DEBUG_MSG || 'E1,';
214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
215 END Update_Blanket_Qty;
216
217
218 /*
219 This function will check if a passed line is part of any fulfillment set.
220 And will return True/False accordingly.
221 */
222
223 FUNCTION Is_Part_Of_Fulfillment_Set
224 (
225 p_line_id IN NUMBER
226 ) return VARCHAR2
227 IS
228 l_set_id NUMBER;
229 --
230 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
231 --
232 BEGIN
233
234 IF l_debug_level > 0 THEN
235 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.IS_PART_OF_FULFILLMENT_SET '|| TO_CHAR ( P_LINE_ID ) , 5 ) ;
236 END IF;
237
238 Select set_id
239 Into l_set_id
240 From oe_line_sets
241 Where line_id = p_line_id;
242
243 IF l_debug_level > 0 THEN
244 oe_debug_pub.add( 'Yes, part of fulfillment set ' , 5 ) ;
245 END IF;
246
247 Return FND_API.G_TRUE;
248
249 EXCEPTION
250
251 WHEN NO_DATA_FOUND THEN
252 Return FND_API.G_FALSE;
253 WHEN TOO_MANY_ROWS THEN
254 Return FND_API.G_TRUE;
255 WHEN OTHERS THEN
256 Return FND_API.G_FALSE;
257
258 END Is_Part_Of_Fulfillment_Set;
259
260 PROCEDURE Get_service_lines
261 (
262 p_line_id IN NUMBER,
263 p_header_id IN NUMBER DEFAULT NULL, -- 1717444
264 x_return_status OUT NOCOPY VARCHAR2,
265
266 x_line_tbl OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
267
268 )
269 IS
270
271 l_header_id NUMBER; -- 1717444
272 l_service_index NUMBER := 0;
273 l_activity_status VARCHAR2(8);
274 l_activity_result VARCHAR2(30);
275 l_activity_id NUMBER;
276 l_item_key VARCHAR2(240);
277 l_fulfill_activity VARCHAR2(30) := 'FULFILL_LINE';
278 l_return_status VARCHAR2(1);
279 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
280
281 CURSOR C_Service_Lines IS
282 SELECT LINE_ID,
283 HEADER_ID,
284 ORDERED_QUANTITY,
285 SHIPPED_QUANTITY,
286 FULFILLED_FLAG,
287 BLANKET_NUMBER,
288 BLANKET_LINE_NUMBER,
289 ORDER_QUANTITY_UOM,
290 ITEM_TYPE_CODE,
291 LINE_CATEGORY_CODE,
292 UNIT_SELLING_PRICE,
293 INVENTORY_ITEM_ID,
294 ORDER_FIRMED_DATE,
295 ACTUAL_SHIPMENT_DATE
296 FROM OE_ORDER_LINES_ALL
297 WHERE SERVICE_REFERENCE_LINE_ID = p_line_id
298 AND SERVICE_REFERENCE_TYPE_CODE = 'ORDER' -- added in 115.46
299 AND HEADER_ID = l_header_id -- 1717444
300 AND TOP_MODEL_LINE_ID IS NULL; --3449588
301 BEGIN
302
303 IF l_debug_level > 0 THEN
304 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.GET_SERVICE_LINES '|| TO_CHAR ( P_LINE_ID ) , 1 ) ;
305 END IF;
306
307
308 IF p_header_id IS NULL OR -- This IF introduced for 1717444
309 p_header_id = FND_API.G_MISS_NUM THEN
310 SELECT header_id
311 INTO l_header_id
312 FROM oe_order_lines
313 WHERE line_id = p_line_id;
314 ELSE
315 l_header_id := p_header_id;
316 END IF;
317
318 FOR l_service_lines IN c_service_lines
319 LOOP
320
321 IF l_service_lines.fulfilled_flag = 'Y' THEN
322
323 IF l_debug_level > 0 THEN
324 oe_debug_pub.add( 'SERVICE LINE IS ALREADY FULFILLED ' , 3 ) ;
325 END IF;
326
327 ELSE
328
329 l_item_key := to_char(l_service_lines.line_id);
330
331 IF l_debug_level > 0 THEN
332 oe_debug_pub.add( 'CALLING GET ACTIVITY RESULT FOR ITEM : '||L_ITEM_KEY||'/'||L_FULFILL_ACTIVITY , 3 ) ;
333 END IF;
334
335 Get_Activity_Result
336 (
337 p_item_type => OE_GLOBALS.G_WFI_LIN
338 , p_item_key => l_item_key
339 , p_activity_name => l_fulfill_activity
340 , x_return_status => l_return_status
341 , x_activity_result => l_activity_result
342 , x_activity_status_code => l_activity_status
343 , x_activity_id => l_activity_id
344 );
345
346 IF l_debug_level > 0 THEN
347 oe_debug_pub.add( 'RETURN STATUS FROM GET ACTIVITY RESULT : '||L_RETURN_STATUS||'/'||L_ACTIVITY_STATUS , 3 ) ;
348 END IF;
349
350 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 ELSIF l_return_status = FND_API.G_RET_STS_ERROR OR
353 l_activity_status <> 'NOTIFIED' THEN
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( 'SERVICE LINE IS NOT AT FULFILL LINE : '||TO_CHAR ( L_SERVICE_LINES.LINE_ID ) , 3 ) ;
356 END IF;
357 GOTO END_SERVICE_LOOP;
358 END IF;
359
360 l_service_index := l_service_index + 1;
361 x_line_tbl(l_service_index).line_id := l_service_lines.line_id;
362 x_line_tbl(l_service_index).header_id := l_service_lines.header_id;
363 x_line_tbl(l_service_index).ordered_quantity := l_service_lines.ordered_quantity;
364 x_line_tbl(l_service_index).shipped_quantity := l_service_lines.shipped_quantity;
365 x_line_tbl(l_service_index).blanket_number := l_service_lines.blanket_number;
366 x_line_tbl(l_service_index).blanket_line_number := l_service_lines.blanket_line_number;
367 x_line_tbl(l_service_index).order_quantity_uom := l_service_lines.order_quantity_uom;
368 x_line_tbl(l_service_index).item_type_code := l_service_lines.item_type_code;
369 x_line_tbl(l_service_index).line_category_code := l_service_lines.line_category_code;
370 x_line_tbl(l_service_index).unit_selling_price := l_service_lines.unit_selling_price;
371 x_line_tbl(l_service_index).inventory_item_id := l_service_lines.inventory_item_id;
372 x_line_tbl(l_service_index).order_firmed_date := l_service_lines.order_firmed_date;
373 x_line_tbl(l_service_index).actual_shipment_date := l_service_lines.actual_shipment_date;
374
375 END IF;
376
377 << END_SERVICE_LOOP >>
378 NULL;
379
380 END LOOP;
381
382
383 IF l_service_index = 0 THEN
384 x_return_status := FND_API.G_RET_STS_ERROR;
385 ELSE
386 x_return_status := FND_API.G_RET_STS_SUCCESS;
387 END IF;
388
389 IF l_debug_level > 0 THEN
390 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.GET_SERVICE_LINES WITH STATUS: '|| X_RETURN_STATUS , 1 ) ;
391 END IF;
392
393 EXCEPTION
394
395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
397
398 IF l_debug_level > 0 THEN
399 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.GET_SERVICE_LINES WITH STATUS: '|| X_RETURN_STATUS , 1 ) ;
400 END IF;
401 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
402 THEN
403 OE_MSG_PUB.Add_Exc_Msg
404 ( G_PKG_NAME,
405 'Get_Service_Lines'
406 );
407 END IF;
408 G_DEBUG_MSG := G_DEBUG_MSG || '5,';
409
410 WHEN OTHERS THEN
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
412 IF l_debug_level > 0 THEN
413 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
414 END IF;
415 IF l_debug_level > 0 THEN
416 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.GET_SERVICE_LINES WITH STATUS: '|| X_RETURN_STATUS , 1 ) ;
417 END IF;
418
419 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
420 THEN
421 OE_MSG_PUB.Add_Exc_Msg
422 ( G_PKG_NAME,
423 'Get_Service_Lines'
424 );
425 END IF;
426 G_DEBUG_MSG := G_DEBUG_MSG || '6,';
427
428 END Get_service_lines;
429
430 PROCEDURE Fulfill_Service_Lines
431 (
432 p_line_id IN NUMBER,
433 p_header_id IN NUMBER DEFAULT NULL, -- 1717444
434 x_return_status OUT NOCOPY VARCHAR2
435
436 )
437
438 IS
439 l_fulfill_tbl OE_Order_Pub.Line_Tbl_Type;
440 l_fulfill_index NUMBER := 0 ;
441 l_service_tbl OE_Order_Pub.Line_Tbl_Type;
442 l_service_index NUMBER := 0 ;
443 l_line_id NUMBER;
444 l_return_status VARCHAR2(1);
445 l_parent_line_fulfilled_qty NUMBER := null; --5699215
446
447 --
448 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
449 --
450 BEGIN
451
452 IF l_debug_level > 0 THEN
453 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.FULFILL_SERVICE_LINES '|| TO_CHAR ( P_LINE_ID ) , 1 ) ;
454 END IF;
455
456 /* Get the service lines if there are any associated with the
457 line */
458
459
460 Get_Service_Lines
461 (
462 p_line_id => p_line_id,
463 p_header_id => p_header_id, -- 1717444
464 x_return_status => l_return_status,
465 x_line_tbl => l_service_tbl
466 );
467
468
469 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
471 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
472 GOTO END_SERVICE_LOOP;
473 END IF;
474
475 --5699215
476 SELECT fulfilled_quantity
477 INTO l_parent_line_fulfilled_qty
478 FROM oe_order_lines
479 WHERE line_id = p_line_id;
480 --5699215
481
482 /* Add service lines to l_fulfill_tbl for fulfillment */
483
484 FOR l_service_index IN 1 .. l_service_tbl.count
485 LOOP
486 l_fulfill_index := l_fulfill_index + 1;
487 l_fulfill_tbl(l_fulfill_index) := OE_Order_PUB.G_MISS_LINE_REC;
488 l_fulfill_tbl(l_fulfill_index).line_id := l_service_tbl(l_service_index).line_id;
489 l_fulfill_tbl(l_fulfill_index).fulfilled_flag := 'Y';
490 l_fulfill_tbl(l_fulfill_index).fulfillment_date := SYSDATE;
491 --5699215
492 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity := nvl(l_parent_line_fulfilled_qty,(Nvl(l_service_tbl(l_service_index).shipped_quantity,l_service_tbl(l_service_index).ordered_quantity)));
493 --nvl(l_service_tbl(l_service_index).shipped_quantity,l_service_tbl(l_service_index).ordered_quantity);
494 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity2 := nvl(l_service_tbl(l_service_index).shipped_quantity2,l_service_tbl(l_service_index).ordered_quantity2); --bug 5126873
495 IF l_fulfill_tbl(l_fulfill_index).fulfilled_quantity2 = 0 then -- bug 5126873
496 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity2 := NULL;
497 END IF;
498 l_fulfill_tbl(l_fulfill_index).blanket_number := l_service_tbl(l_service_index).blanket_number;
499 l_fulfill_tbl(l_fulfill_index).blanket_line_number := l_service_tbl(l_service_index).blanket_line_number;
500 l_fulfill_tbl(l_fulfill_index).order_quantity_uom := l_service_tbl(l_service_index).order_quantity_uom;
501 l_fulfill_tbl(l_fulfill_index).item_type_code := l_service_tbl(l_service_index).item_type_code;
502 l_fulfill_tbl(l_fulfill_index).line_category_code := l_service_tbl(l_service_index).line_category_code;
503 l_fulfill_tbl(l_fulfill_index).unit_selling_price := l_service_tbl(l_service_index).unit_selling_price;
504 l_fulfill_tbl(l_fulfill_index).header_id := l_service_tbl(l_service_index).header_id;
505 l_fulfill_tbl(l_fulfill_index).inventory_item_id := l_service_tbl(l_service_index).inventory_item_id;
506 l_fulfill_tbl(l_fulfill_index).order_firmed_date := l_service_tbl(l_service_index).order_firmed_date;
507 l_fulfill_tbl(l_fulfill_index).actual_shipment_date := l_service_tbl(l_service_index).actual_shipment_date;
508 l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
509 IF l_debug_level > 0 THEN
510 oe_debug_pub.add( 'FULFILL INDEX : '||TO_CHAR ( L_FULFILL_INDEX ) , 3 ) ;
511 oe_debug_pub.add( 'FULFILLED FLAG : '||L_FULFILL_TBL ( L_FULFILL_INDEX ) .FULFILLED_FLAG , 3 ) ;
512 oe_debug_pub.add( 'FULFILLED QUANTITY : '||TO_CHAR ( L_FULFILL_TBL ( L_FULFILL_INDEX ) .FULFILLED_QUANTITY ) , 3 ) ;
513 END IF;
514
515 END LOOP;
516
517 /* Update the fulfilled flag and quantity for the service lines */
518
519 IF l_fulfill_index <> 0 THEN
520
521 IF l_debug_level > 0 THEN
522 oe_debug_pub.add( '..516..CALLING FULFILL LINE TABLE : ' , 3 ) ;
523 END IF;
524
525 Fulfill_Line
526 (
527 p_line_tbl => l_fulfill_tbl,
528 p_mode => 'TABLE',
529 p_fulfillment_type => G_FULFILL_WITH_ACTIVITY,
530 x_return_status => l_return_status
531 );
532
533 IF l_debug_level > 0 THEN
534 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL LINE : '||L_RETURN_STATUS , 3 ) ;
535 END IF;
536
537 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
538 IF l_debug_level > 0 THEN
539 oe_debug_pub.add('raising unexpected error '||sqlerrm,1);
540 END IF;
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
543 IF l_debug_level > 0 THEN
544 oe_debug_pub.add('raising exc error '||sqlerrm,1);
545 END IF;
546 RAISE FND_API.G_EXC_ERROR;
547 END IF;
548 END IF;
549
550 /* Complete the work flow activity for the service lines */
551
552 FOR l_service_index IN 1 .. l_service_tbl.count
553 LOOP
554
555 l_line_id := l_service_tbl(l_service_index).line_id;
556 IF l_debug_level > 0 THEN
557 oe_debug_pub.add( 'CALLING FLOW STATUS API FOR LINE '||L_SERVICE_TBL ( L_SERVICE_INDEX ) .LINE_ID , 3 ) ;
558 END IF;
559
560 OE_Order_WF_Util.Update_Flow_Status_Code
561 (p_line_id => l_service_tbl(l_service_index).line_id,
562 p_flow_status_code => 'FULFILLED',
563 x_return_status => l_return_status
564 );
565
566 IF l_debug_level > 0 THEN
567 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
568 END IF;
569
570 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
571 IF l_debug_level > 0 THEN
572 oe_debug_pub.add('after update of flow status code - error '||sqlerrm,1);
573 END IF;
574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
575 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
576 IF l_debug_level > 0 THEN
577 oe_debug_pub.add('after update of flow status code - exc error '||sqlerrm,1);
578 END IF;
579 RAISE FND_API.G_EXC_ERROR;
580 END IF;
581 IF l_debug_level > 0 THEN
582 oe_debug_pub.add( 'CALLING WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( L_LINE_ID ) , 3 ) ;
583 END IF;
584 BEGIN
585 wf_engine.CompleteActivityInternalName('OEOL', to_char(l_line_id), 'FULFILL_LINE', '#NULL',TRUE ); --15870313
586 EXCEPTION
587 WHEN OTHERS THEN
588 oe_debug_pub.add('Workflow complete activity failed. Raising');
589 RAISE ;
590 END ;
591 IF l_debug_level > 0 THEN
592 oe_debug_pub.add( 'RETURNED FROM WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( L_LINE_ID ) , 3 ) ;
593 END IF;
594
595 END LOOP;
596
597
598 << END_SERVICE_LOOP >>
599
600 x_return_status := FND_API.G_RET_STS_SUCCESS;
601
602 IF l_debug_level > 0 THEN
603 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.FULFILL_SERVICE_LINES '|| X_RETURN_STATUS , 1 ) ;
604 END IF;
605
606 EXCEPTION
607
608 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
610
611 IF l_debug_level > 0 THEN
612 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.FULFILL_SERVICE_LINES WITH STATUS: '|| X_RETURN_STATUS , 1 ) ;
613 END IF;
614 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
615 THEN
616 OE_MSG_PUB.Add_Exc_Msg
617 ( G_PKG_NAME,
618 'Fulfill_Service_Lines'
619 );
620 END IF;
621 G_DEBUG_MSG := G_DEBUG_MSG || 'E4,';
622
623 WHEN OTHERS THEN
624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
625 IF l_debug_level > 0 THEN
626 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
627 END IF;
628 IF l_debug_level > 0 THEN
629 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.FULFILL_SERVICE_LINES WITH STATUS: '|| X_RETURN_STATUS , 1 ) ;
630 END IF;
631
632 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
633 THEN
634 OE_MSG_PUB.Add_Exc_Msg
635 ( G_PKG_NAME,
636 'Fulfill_Service_Lines'
637 );
638 END IF;
639 G_DEBUG_MSG := G_DEBUG_MSG || 'E5';
640
641 END Fulfill_Service_Lines;
642
643
644 PROCEDURE Update_Service_Dates /* 2048753 */
645 (
646 p_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
647 )
648 IS
649 l_return_status VARCHAR2(1);
650 l_line_rec OE_Order_Pub.Line_Rec_Type; -- added for 2897505
651
652 --
653 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
654 --
655 BEGIN
656
657 IF l_debug_level > 0 THEN
658 oe_debug_pub.add( 'ENTERING UPDATE_SERVICE_DATES: ' || TO_CHAR ( P_LINE_REC.LINE_ID ) || ' '|| TO_CHAR ( P_LINE_REC.SERVICE_START_DATE , 'YYYY/MM/DD' ) ||' , '|| TO_CHAR ( P_LINE_REC.SERVICE_END_DATE , 'YYYY/MM/DD' ) , 5 ) ;
659 END IF;
660
661
662 IF p_line_rec.service_start_date IS NULL OR
663 p_line_rec.service_start_date = FND_API.G_MISS_DATE
664 THEN
665 l_line_rec := p_line_rec; -- 2897505 start
666 l_line_rec.service_start_date := NULL;
667 l_line_rec.service_reference_type_code := 'GET_SVC_START';
668
669
670 OE_SERVICE_UTIL.Get_Service_Duration(
671 p_x_line_rec => l_line_rec,
672 x_return_status => l_return_status
673 );
674
675
676
677 p_line_rec.service_start_date := l_line_rec.service_start_date;
678
679 /* commented out for 4110237
680 IF p_line_rec.service_start_date IS NULL THEN
681 p_line_rec.service_start_date := p_line_rec.fulfillment_date;
682 END IF; -- 2897505 end
683 */
684
685 -- IF condition added for 4110237
686 IF p_line_rec.service_start_date IS NOT NULL THEN
687 p_line_rec.service_end_date := NULL;
688 OE_SERVICE_UTIL.Get_Service_Duration(
689 p_x_line_rec => p_line_rec,
690 x_return_status => l_return_status
691 );
692 END IF;
693
694 ELSIF p_line_rec.service_end_date IS NULL OR
695 p_line_rec.service_end_date = FND_API.G_MISS_DATE
696 THEN
697
698 OE_SERVICE_UTIL.Get_Service_Duration(
699 p_x_line_rec => p_line_rec,
700 x_return_status => l_return_status
701 );
702
703 END IF;
704
705 IF l_debug_level > 0 THEN
706 oe_debug_pub.add( 'EXITING UPDATE_SERVICE_DATES:' || TO_CHAR ( P_LINE_REC.SERVICE_START_DATE , 'YYYY/MM/DD' ) ||' , '|| TO_CHAR ( P_LINE_REC.SERVICE_END_DATE , 'YYYY/MM/DD' ) , 5 ) ;
707 END IF;
708
709 END Update_Service_Dates;
710
711 /*
712 This procedure is to update the fulfilled flag, fulfilled quantity and
713 fulfillment date for a line or a table of line records by calling
714 Process_Order API
715 */
716
717 PROCEDURE Fulfill_Line
718 (
719 p_line_rec IN OE_Order_Pub.Line_Rec_Type DEFAULT OE_Order_Pub.G_MISS_LINE_REC
720 ,p_line_tbl IN OE_Order_Pub.Line_Tbl_Type DEFAULT OE_Order_Pub.G_MISS_LINE_TBL
721 ,p_mode IN VARCHAR2
722 ,p_fulfillment_type IN VARCHAR2
723 ,p_fulfillment_activity IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
724 ,x_return_status OUT NOCOPY VARCHAR2
725 )
726 IS
727 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
728 l_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
729 l_aso_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
730 l_return_status VARCHAR2(1);
731 l_index NUMBER;
732 l_fulfilled_quantity NUMBER;
733 l_fulfilled_quantity2 NUMBER; -- 5126873
734 l_line_rec OE_Order_Pub.Line_Rec_Type; /* 2048753 */
735 l_notify_index NUMBER;
736 l_user NUMBER := NVL(OE_STANDARD_WF.G_USER_ID, FND_GLOBAL.USER_ID); --3169637
737 -- Changes for AFD
738 l_ordered_date date;
739 l_actual_fulfillment_date date;
740 l number;
741 -- end Changes for AFD
742
743 Cursor srv_lines is -- 2292133
744 select service_start_date,
745 service_end_date,
746 service_period,
747 service_duration,
748 service_coterminate_flag,
749 item_type_code -- this one added for 2417601
750 from oe_order_lines
751 where line_id = l_line_rec.line_id;
752 --
753 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
754 --
755 /* Start Improved OM,IB,OKS changes */
756 l_msg_data VARCHAR2(2000) := NULL;
757 l_validation_level NUMBER;
758 l_Service_Order_Lines OKS_OMINT_PUB.Service_Order_Lines_TblType;
759 l_order_number NUMBER;
760 /* end Improved OM,IB,OKS changes */
761 l_message_id NUMBER;
762 l_error_code NUMBER;
763 l_error_message VARCHAR2(4000);
764 l_accounting_rule_type VARCHAR2(60); -- webroot bug 6826344 added
765
766 CAN_NOT_LOCK_MODEL EXCEPTION;
767
768 l_recalc_First_periodamt char(1) := 'N'; --sol_ord_er #16014165
769 BEGIN
770
771 IF l_debug_level > 0 THEN -- debug level changed to 0.5 for bug 13435459
772 OE_DEBUG_PUB.add('Entering Fulfill_line() procedure in OEXVFULB at ',0.5);
773 oe_debug_pub.add('Order Line ID being fulfilled '||to_char(p_line_rec.line_id),1) ;
774 END IF;
775
776
777 l_line_rec.service_start_date := NULL; -- initialization for 2292133
778 l_line_rec.service_end_date := NULL;
779
780 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
781 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
782 END IF;
783
784 /* Commented for performance changes.
785 l_control_rec := OE_GLOBALS.G_MISS_CONTROL_REC;
786 l_control_rec.validate_entity := FALSE;
787 l_control_rec.check_security := FALSE;
788 */
789 IF p_mode = 'RECORD' THEN
790
791
792 -- CHANGES for AFD
793 select ordered_date into l_ordered_date
794 from oe_order_headers_all
795 where header_id = p_line_rec.header_id;
796
797 l_actual_fulfillment_date := nvl(p_line_rec.actual_shipment_date,nvl(p_line_rec.order_firmed_date,l_ordered_date));
798 -- end CHANGES for AFD
799
800
801 /* Commented for performance changes
802 l_update_line_tbl(1) := OE_Order_PUB.G_MISS_LINE_REC;
803 l_update_line_tbl(1).line_id := p_line_rec.line_id;
804 l_update_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
805 */
806 /* Start Audit Trail */
807 -- l_update_line_tbl(1).change_reason := 'SYSTEM';
808 -- l_update_line_tbl(1).change_comments := 'Process fulfillment set';
809 /* End Audit Trail */
810 /*
811 l_update_line_tbl(1).fulfilled_flag := 'Y';
812 l_update_line_tbl(1).fulfillment_date := SYSDATE;
813
814 */
815 IF p_fulfillment_type = G_FULFILL_WITH_ACTIVITY THEN
816
817 IF l_debug_level > 0 THEN
818 oe_debug_pub.add('Updating with shipped quantity',3) ;
819 END IF;
820 --l_update_line_tbl(1).fulfilled_quantity := p_line_rec.shipped_quantity;
821 l_fulfilled_quantity := p_line_rec.shipped_quantity;
822 l_fulfilled_quantity2 := p_line_rec.shipped_quantity2; -- 5126873
823 IF l_fulfilled_quantity2 = 0 then -- bug 5126873
824 l_fulfilled_quantity2 := NULL;
825 END IF;
826
827
828 ELSE
829 IF l_debug_level > 0 THEN
830 oe_debug_pub.add('Updating with ordered quantity',3) ;
831 END IF;
832 --l_update_line_tbl(1).fulfilled_quantity := p_line_rec.ordered_quantity;
833 l_fulfilled_quantity := p_line_rec.ordered_quantity;
834 l_fulfilled_quantity2 := p_line_rec.ordered_quantity2; -- 5126873
835 IF l_fulfilled_quantity2 = 0 then -- bug 5126873
836 l_fulfilled_quantity2 := NULL;
837 END IF;
838
839 END IF;
840 --webroot bug 6826344 start
841 IF p_line_rec.accounting_rule_id IS NOT NULL THEN
842
843 SELECT type INTO l_accounting_rule_type
844 FROM ra_rules
845 WHERE rule_id = p_line_rec.accounting_rule_id;
846
847 END IF;
848 --webroot bug 6826344 end
849
850 IF p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE /* 2048753 */
851 OR p_line_rec.subscription_enable_flag = 'Y' --sol_ord_er #16014165
852 OR (l_accounting_rule_type = 'PP_DR_PP' OR l_accounting_rule_type = 'PP_DR_ALL') THEN -- added for webroot bug 6826344
853 l_line_rec := p_line_rec;
854 l_line_rec.fulfillment_date := SYSDATE;
855 --sol_ord_er #16014165
856 l_recalc_First_periodamt := 'N';
857 If NVL(l_LINE_REC.service_bill_option_code,'FBOM') = 'FPOM'
858 AND (l_LINE_REC.SERVICE_START_DATE is NULL or l_LINE_REC.SERVICE_END_DATE is null) then
859 IF l_debug_level > 0 THEN
860 oe_debug_pub.add('OEXVFULB ..101..Bill Option= '||l_LINE_REC.service_bill_option_code,3) ;
861 oe_debug_pub.add('OEXVFULB ..101.1..StrtDate= '||l_LINE_REC.SERVICE_START_DATE
862 ||' ..endDate= '||l_LINE_REC.SERVICE_END_DATE,3) ;
863 END IF;
864 l_recalc_First_periodamt := 'Y';
865 end if;
866 --sol_ord_er #16014165 end
867
868 Update_Service_Dates(l_line_rec);
869
870 END IF;
871 IF l_debug_level > 0 THEN
872 oe_debug_pub.add('After calling update_service_lines() ',5) ;
873 END IF;
874 IF ( (OE_GLOBALS.G_ASO_INSTALLED = 'Y') OR
875 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y') ) THEN
876 IF l_debug_level > 0 THEN
877 oe_debug_pub.add('mode is record',3) ;
878 END IF;
879 OE_Line_Util.Lock_Rows
880 (p_line_id => p_line_rec.line_id
881 ,x_line_tbl => l_old_line_tbl
882 ,x_return_status => l_return_status
883 );
884 IF l_debug_level > 0 THEN
885 oe_debug_pub.add('Returned from lock row : '||l_return_status,3) ;
886 END IF;
887 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
888 IF l_debug_level > 0 THEN
889 oe_debug_pub.add('STS error while locking row '||sqlerrm,1);
890 END IF;
891 RAISE CAN_NOT_LOCK_MODEL;
892 --RAISE FND_API.G_EXC_ERROR;
893 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
894 IF l_debug_level > 0 THEN
895 oe_debug_pub.add('EXC error while locking row '||sqlerrm,1);
896 END IF;
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 END IF;
899
900
901 l_line_tbl := l_old_line_tbl;
902 l_aso_line_tbl := l_old_line_tbl;
903 l_line_tbl(1).fulfilled_flag := 'Y';
904 l_line_tbl(1).fulfilled_quantity := l_fulfilled_quantity;
905 l_line_tbl(1).fulfilled_quantity2 := l_fulfilled_quantity2; -- 5126873
906 IF l_line_tbl(1).fulfilled_quantity2 = 0 then -- bug 5126873
907 l_line_tbl(1).fulfilled_quantity2 := NULL;
908 END IF;
909
910
911 l_line_tbl(1).fulfillment_date := SYSDATE;
912 l_line_tbl(1).last_update_date := SYSDATE;
913 l_line_tbl(1).last_updated_by := l_user; -- 3169637
914 l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
915 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
916 /* next two lines: 2048753 */
917 l_line_tbl(1).service_start_date := l_line_rec.service_start_date;
918 l_line_tbl(1).service_end_date := l_line_rec.service_end_date;
919
920 -- changes for AFD
921 l_line_tbl(1).actual_fulfillment_date := l_actual_fulfillment_date;
922
923 -- added for notification framework
924 --check code release level first. Notification framework is at Pack H level
925 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
926 -- calling notification framework to get index position
927 IF l_debug_level > 0 THEN
928 oe_debug_pub.add('Line_id is:' || p_line_rec.line_id ) ;
929 END IF;
930 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
931 p_header_id=>p_line_rec.header_id,
932 p_line_rec =>l_line_tbl(1),
933 p_line_id => p_line_rec.line_id,
934 x_index => l_notify_index,
935 x_return_status => l_return_status);
936
937
938 IF l_debug_level > 0 THEN
939 oe_debug_pub.add('Update_global return status from oe_line_fullfill.fullfill_line is: '|| l_return_status ) ;
940 oe_debug_pub.add('Global picture index is: '||l_notify_index,1) ;
941 END IF;
942 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
944 END IF;
945 IF l_notify_index IS NOT NULL THEN
946 --update Global Picture directly
947 OE_ORDER_UTIL.g_old_line_tbl(l_notify_index):= l_old_line_tbl(1);
948 OE_ORDER_UTIL.g_line_tbl(l_notify_index):= OE_ORDER_UTIL.g_old_line_tbl(l_notify_index);
949
950 oe_debug_pub.add('line_rec line id is :'|| l_line_rec.line_id);
951 oe_debug_pub.add('line_rec header id is :'|| l_line_rec.header_id);
952
953 /* commented as unnecessary for 3803251
954 OE_ORDER_UTIL.g_line_tbl(l_notify_index).line_id := l_line_rec.line_id;
955 OE_ORDER_UTIL.g_line_tbl(l_notify_index).header_id := l_line_rec.header_id;
956 */
957 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfilled_flag := 'Y';
958 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfilled_quantity := l_fulfilled_quantity;
959 IF l_fulfilled_quantity2 <> 0 then
960 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfilled_quantity2 := l_fulfilled_quantity2; -- 5126873
961 ELSE
962 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfilled_quantity2 := NULL;
963 END IF;
964
965 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfillment_date := SYSDATE;
966 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date := SYSDATE;
967 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by := l_user; -- 3169637
968 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login :=FND_GLOBAL.LOGIN_ID;
969 OE_ORDER_UTIL.g_line_tbl(l_notify_index).service_start_date :=l_line_rec.service_start_date;
970 OE_ORDER_UTIL.g_line_tbl(l_notify_index).service_end_date := l_line_rec.service_end_date;
971 /* changes for AFD */
972 OE_ORDER_UTIL.g_line_tbl(l_notify_index).actual_fulfillment_date := l_actual_fulfillment_date;
973
974 IF l_debug_level > 0 THEN
975 oe_debug_pub.add('global line fulfilled_flag is: '||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).FULFILLED_FLAG,1) ;
976 END IF;
977 END IF;
978 END IF; /*code_release_level*/
979 -- notification framework end
980 END IF; /* aso installed*/
981
982
983 UPDATE OE_ORDER_LINES_ALL
984 SET FULFILLED_FLAG = 'Y',
985 FULFILLED_QUANTITY = l_fulfilled_quantity,
986 FULFILLED_QUANTITY2 = l_fulfilled_quantity2, -- 5126873
987 FULFILLMENT_DATE = SYSDATE,
988 LAST_UPDATE_DATE = SYSDATE,
989 LAST_UPDATED_BY = l_user, -- 3169637
990 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
991 /* next two lines: 2048753 */
992 SERVICE_START_DATE = l_line_rec.service_start_date,
993 SERVICE_END_DATE = l_line_rec.service_end_date,
994 ACTUAL_FULFILLMENT_DATE = l_actual_fulfillment_date,
995 LOCK_CONTROL = LOCK_CONTROL+1
996 WHERE LINE_ID = p_line_rec.line_id;
997 --- sol_ord_er #16014165
998 If l_recalc_First_periodamt = 'Y'
999 AND l_line_rec.SERVICE_START_DATE is not NULL and l_line_rec.SERVICE_END_DATE is not null
1000 THEN
1001 IF l_debug_level > 0 THEN
1002 oe_Debug_pub.add(' ..OEXVFULB 102.. Calculating first period amount fpr lineid= '
1003 ||P_LINE_REC.line_id);
1004 End If;
1005 oe_delayed_requests_util.First_period_bill_amt (
1006 p_header_id => '',
1007 p_line_id => P_LINE_REC.line_id,
1008 x_return_status => l_return_status);
1009 -- reset back to N
1010 l_recalc_First_periodamt := 'N';
1011 End If;
1012 -- sol_ord_er #16014165 end
1013
1014 IF p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE
1015 OR p_line_rec.subscription_enable_flag = 'Y' THEN -- sol_ord_er #16014165 (for Model)
1016 /* Start Improved OM,IB,OKS changes */
1017 select order_number
1018 into l_order_number
1019 from oe_order_headers_all
1020 where header_id = p_line_rec.header_id;
1021
1022 l_Service_Order_Lines(1).Order_Header_ID := p_line_rec.header_id;
1023 l_Service_Order_Lines(1).Order_Line_ID := p_line_rec.line_id;
1024 l_Service_Order_Lines(1).Order_Number := l_order_number;
1025 l_Service_Order_Lines(1).Ref_Order_Line_ID := p_line_rec.service_reference_line_id;
1026 IF l_debug_level > 0 THEN
1027 oe_debug_pub.add('Calling OKS_OMINT_PUB.Interface_Service_Order_Lines:p_line_rec.line_id:'
1028 || p_line_rec.line_id,3) ;
1029 END IF;
1030
1031 OKS_OMINT_PUB.Interface_Service_Order_Lines(
1032 p_Service_Order_Lines => l_Service_Order_Lines,
1033 x_Return_Status => l_return_status,
1034 x_Error_Message => l_msg_data);
1035
1036 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1037 oe_debug_pub.add('FND_API.G_RET_STS_ERROR after call to OKS_OMINT_PUB.Interface_Service');
1038 oe_debug_pub.add('l_msg_data' || l_msg_data);
1039 RAISE FND_API.G_EXC_ERROR;
1040 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1041 oe_debug_pub.add('FND_API.G_RET_STS_UNEXP_ERROR after call to OKS_OMINT_PUB.Interface_Service');
1042 oe_debug_pub.add('l_msg_data' || l_msg_data);
1043 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1044 END IF;
1045 /* End Improved OM,IB,OKS changes */
1046 END IF;
1047
1048 -- Added 09-Dec-2002
1049 -- BLANKETS: Update returned/fulfilled qty
1050 IF OE_CODE_CONTROL.Code_Release_Level >= '110509' THEN
1051 if l_debug_level > 0 then
1052 oe_debug_pub.add('OEXVFULB 1, blanket number :'||
1053 p_line_rec.blanket_number);
1054 end if;
1055 IF p_line_rec.blanket_number IS NOT NULL THEN
1056 Update_Blanket_Qty(p_line_rec,l_fulfilled_quantity);
1057 END IF;
1058 END IF;
1059
1060 oe_debug_pub.add(' FND_PROFILE.VALUE(ONT_AUTO_INTERFACE_LINES_TO_IB) = ' || FND_PROFILE.VALUE('ONT_AUTO_INTERFACE_LINES_TO_IB'), 5);
1061
1062 IF p_line_rec.item_type_code <> OE_GLOBALS.G_ITEM_SERVICE
1063 AND NVL(p_line_rec.subscription_enable_flag,'N') <> 'Y' -- sol_ord_er #16014165 (for Model)
1064 AND ( NVL(FND_PROFILE.VALUE('ONT_AUTO_INTERFACE_LINES_TO_IB'),'Y') = 'Y') -- bug 9245134
1065 THEN
1066 oe_debug_pub.add('Before Call to csi_ont_txn_pub.posttransaction',1);
1067 csi_ont_txn_pub.posttransaction
1068 (
1069 p_order_line_id => p_line_rec.line_id,
1070 x_return_status => l_return_status,
1071 x_message_id => l_message_id,
1072 x_error_code => l_error_code,
1073 x_error_message => l_error_message);
1074 oe_debug_pub.add('After Call to csi_ont_txn_pub.posttransaction',1);
1075
1076 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1077 IF l_debug_level > 0 THEN
1078 oe_debug_pub.add('raising unexpected error in csi_ont_txn_pub.posttransaction'||sqlerrm,1);
1079 END IF;
1080 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1081 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1082 IF l_debug_level > 0 THEN
1083 oe_debug_pub.add('raising exc error in csi_ont_txn_pub.posttransaction'||sqlerrm,1);
1084 END IF;
1085 RAISE FND_API.G_EXC_ERROR;
1086 END IF;
1087 END IF;
1088
1089 ELSE
1090 IF l_debug_level > 0 THEN
1091 oe_debug_pub.add('Number of lines to update: '||p_line_tbl.count,3) ;
1092 END IF;
1093
1094 IF p_line_tbl.count = 0 THEN -- this IF added for 2231594
1095 IF l_debug_level > 0 THEN
1096 oe_debug_pub.add('Exiting fulfill_line() - no lines to update',3) ;
1097 END IF;
1098 x_return_status := FND_API.G_RET_STS_SUCCESS;
1099 RETURN;
1100 END IF;
1101
1102 -- CHANGES for AFD
1103 l := p_line_tbl.FIRST;
1104
1105 select ordered_date into l_ordered_date
1106 from oe_order_headers_all
1107 where header_id = p_line_tbl(l).header_id;
1108 -- end CHANGES for AFD
1109
1110
1111
1112 FOR l_index IN p_line_tbl.FIRST .. p_line_tbl.LAST LOOP
1113
1114 -- Changes for AFD
1115 l_actual_fulfillment_date := nvl(p_line_tbl(l_index).actual_shipment_date,nvl(p_line_tbl(l_index).order_firmed_date,l_ordered_date));
1116 -- END CHANGES for AFD
1117
1118 IF ( (OE_GLOBALS.G_ASO_INSTALLED = 'Y') OR
1119 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y') )THEN
1120
1121 OE_Line_Util.Lock_Rows
1122 (p_line_id => p_line_tbl(l_index).line_id
1123 ,x_line_tbl => l_old_line_tbl
1124 ,x_return_status => l_return_status
1125 );
1126 IF l_debug_level > 0 THEN
1127 oe_debug_pub.add('Returned from lock row : '||l_return_status,3) ;
1128 END IF;
1129
1130 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1131 IF l_debug_level > 0 THEN
1132 oe_debug_pub.add('STS error while locking row '||sqlerrm,1);
1133 END IF;
1134 RAISE CAN_NOT_LOCK_MODEL;
1135 --RAISE FND_API.G_EXC_ERROR;
1136 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1137 IF l_debug_level > 0 THEN
1138 oe_debug_pub.add('EXC error while locking row '||sqlerrm,1);
1139 END IF;
1140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1141 END IF;
1142
1143 l_line_tbl(l_index) := l_old_line_tbl(1);
1144 l_aso_line_tbl(l_index) := l_old_line_tbl(1);
1145 l_line_tbl(l_index).fulfilled_flag := p_line_tbl(l_index).fulfilled_flag;
1146 l_line_tbl(l_index).fulfilled_quantity := p_line_tbl(l_index).fulfilled_quantity;
1147 l_line_tbl(l_index).fulfilled_quantity2 := p_line_tbl(l_index).fulfilled_quantity2; -- 5126873
1148 IF l_line_tbl(l_index).fulfilled_quantity2 = 0 then -- 5126873
1149 l_line_tbl(l_index).fulfilled_quantity2 := NULL;
1150 END IF;
1151 l_line_tbl(l_index).fulfillment_date := p_line_tbl(l_index).fulfillment_date;
1152 l_line_tbl(l_index).last_update_date := SYSDATE;
1153 l_line_tbl(l_index).last_updated_by := l_user; -- 3169637
1154 l_line_tbl(l_index).last_update_login := FND_GLOBAL.LOGIN_ID;
1155 l_line_tbl(l_index).lock_control := l_line_tbl(l_index).lock_control + 1;
1156
1157 -- Changes for AFD
1158 l_line_tbl(l_index).actual_fulfillment_date := l_actual_fulfillment_date;
1159 -- END CHANGES for AFD
1160
1161 IF l_line_tbl(l_index).item_type_code = OE_GLOBALS.G_ITEM_SERVICE /* 2048753 */
1162 OR l_line_tbl(l_index).subscription_enable_flag = 'Y' --sol_ord_er #16014165
1163 THEN
1164 l_line_rec := l_line_tbl(l_index);
1165 --sol_ord_er #16014165
1166 l_recalc_First_periodamt := 'N';
1167 If NVL(l_line_rec.service_bill_option_code,'FBOM') = 'FPOM'
1168 AND (l_line_rec.SERVICE_START_DATE is NULL or l_line_rec.SERVICE_END_DATE is null) then
1169 IF l_debug_level > 0 THEN
1170 oe_debug_pub.add('OEXVFULB ..103..Bill Option= '||l_LINE_REC.service_bill_option_code,3) ;
1171 oe_debug_pub.add('OEXVFULB ..103.1..StrtDate= '||l_LINE_REC.SERVICE_START_DATE
1172 ||' ..endDate= '||l_LINE_REC.SERVICE_END_DATE,3) ;
1173 END IF;
1174 l_recalc_First_periodamt := 'Y';
1175 end if;
1176 --sol_ord_er #16014165 end
1177 Update_Service_Dates(l_line_rec);
1178 l_line_tbl(l_index).service_start_date := l_line_rec.service_start_date;
1179 l_line_tbl(l_index).service_end_date := l_line_rec.service_end_date;
1180 IF l_debug_level > 0 THEN
1181 oe_debug_pub.add( 'AFTER CALLING UPDATE_SERVICE_DATES - ASO INSTALLED' , 5 ) ;
1182 END IF;
1183 END IF;
1184
1185 -- added for notification framework
1186 --check code release level first. Notification framework is at Pack H level
1187 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1188 -- calling notification framework to get index position
1189 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
1190 p_header_id=>l_line_tbl(l_index).header_id,
1191 p_line_rec=>l_line_tbl(l_index),
1192 p_line_id => p_line_tbl(l_index).line_id,
1193 x_index => l_notify_index,
1194 x_return_status => l_return_status);
1195 IF l_debug_level > 0 THEN
1196 oe_debug_pub.add('Update_global return status from fulfill_line with line table is: '||l_return_status);
1197 END IF;
1198 IF l_debug_level > 0 THEN
1199 oe_debug_pub.add( 'GLOBAL PICTURE INDEX IS: ' || L_NOTIFY_INDEX , 1 ) ;
1200 END IF;
1201 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1203 END IF;
1204 IF l_notify_index IS NOT NULL THEN
1205 --update Global Picture directly
1206 -- Fix for the bug2635911
1207 -- OE_ORDER_UTIL.g_old_line_tbl(l_notify_index) := l_old_line_tbl(l_index);
1208 OE_ORDER_UTIL.g_old_line_tbl(l_notify_index) := l_old_line_tbl(1);
1209 OE_ORDER_UTIL.g_line_tbl(l_notify_index) := OE_ORDER_UTIL.g_old_line_tbl(l_notify_index);
1210 OE_ORDER_UTIL.g_line_tbl(l_notify_index).line_id := p_line_tbl(l_index).line_id;
1211 OE_ORDER_UTIL.g_line_tbl(l_notify_index).header_id := p_line_tbl(l_index).header_id;
1212 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfilled_flag := p_line_tbl(l_index).fulfilled_flag;
1213 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfilled_quantity := p_line_tbl(l_index).fulfilled_quantity;
1214 IF p_line_tbl(l_index).fulfilled_quantity2 = 0 then -- 5126873
1215 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfilled_quantity2 := NULL;
1216 ELSE
1217 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfilled_quantity2 := p_line_tbl(l_index).fulfilled_quantity2; -- 5126873
1218 END IF;
1219 OE_ORDER_UTIL.g_line_tbl(l_notify_index).fulfillment_date := p_line_tbl(l_index).fulfillment_date;
1220 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date := SYSDATE;
1221 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by := l_user; -- 3169637
1222 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login :=FND_GLOBAL.LOGIN_ID;
1223 OE_ORDER_UTIL.g_line_tbl(l_notify_index).service_start_date :=l_line_rec.service_start_date;
1224 OE_ORDER_UTIL.g_line_tbl(l_notify_index).service_end_date := l_line_rec.service_end_date;
1225 -- Changes for AFD
1226 OE_ORDER_UTIL.g_line_tbl(l_notify_index).actual_fulfillment_date := l_actual_fulfillment_date;
1227 -- end Changes for AFD
1228 IF l_debug_level > 0 THEN
1229 oe_debug_pub.add('Line fulfilled_flag is: '||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).FULFILLED_FLAG,1);
1230 END IF;
1231 END IF;
1232 END IF; --code_release_level
1233 -- notification framework end
1234 ELSIF p_line_tbl(l_index).item_type_code = OE_GLOBALS.G_ITEM_SERVICE /* 2292133 */
1235 OR p_line_tbl(l_index).item_type_code IS NULL -- 2417601
1236 OR p_line_tbl(l_index).subscription_enable_flag = 'Y' --sol_ord_er #16014165
1237 OR p_line_tbl(l_index).item_type_code = FND_API.G_MISS_CHAR THEN -- 2417601
1238 l_line_rec := p_line_tbl(l_index);
1239 open srv_lines;
1240 fetch srv_lines into
1241 l_line_rec.service_start_date,
1242 l_line_rec.service_end_date,
1243 l_line_rec.service_period,
1244 l_line_rec.service_duration,
1245 l_line_rec.service_coterminate_flag,
1246 l_line_rec.item_type_code;
1247 close srv_lines;
1248 IF l_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE
1249 OR l_line_rec.subscription_enable_flag = 'Y' --sol_ord_er #16014165
1250 THEN -- 2417601
1251 Update_Service_Dates(l_line_rec);
1252 IF l_debug_level > 0 THEN
1253 oe_debug_pub.add('After calling update_service_dates - aso not installed',5) ;
1254 END IF;
1255 END IF;
1256 END IF; /* IF OE_GLOBALS.G_ASO_INSTALLED */
1257
1258 IF l_debug_level > 0 THEN
1259 oe_debug_pub.add('Updating line : '||p_line_tbl(l_index).line_id,3) ;
1260 END IF;
1261
1262 -- IF l_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE THEN --3471423
1263 IF p_line_tbl(l_index).item_type_code = OE_GLOBALS.G_ITEM_SERVICE --3471423
1264 OR p_line_tbl(l_index).subscription_enable_flag = 'Y' THEN -- sol_ord_er #16014165 (for Model)
1265 /* 2417601 uses a different update statements for service line */
1266
1267
1268 UPDATE OE_ORDER_LINES_ALL
1269 SET FULFILLED_FLAG = p_line_tbl(l_index).fulfilled_flag,
1270 FULFILLED_QUANTITY = p_line_tbl(l_index).fulfilled_quantity,
1271 FULFILLED_QUANTITY2 = p_line_tbl(l_index).fulfilled_quantity2, -- 5126873
1272 FULFILLMENT_DATE = p_line_tbl(l_index).fulfillment_date,
1273 LAST_UPDATE_DATE = SYSDATE,
1274 LAST_UPDATED_BY = l_user, -- 3169637
1275 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1276 SERVICE_START_DATE = l_line_rec.service_start_date,
1277 SERVICE_END_DATE = l_line_rec.service_end_date,
1278 ACTUAL_FULFILLMENT_DATE = l_actual_fulfillment_date,
1279 LOCK_CONTROL = LOCK_CONTROL+1
1280 WHERE LINE_ID = p_line_tbl(l_index).line_id;
1281 --- sol_ord_er #16014165
1282 If l_recalc_First_periodamt = 'Y'
1283 AND l_line_rec.SERVICE_START_DATE is not NULL and l_line_rec.SERVICE_END_DATE is not null
1284 THEN
1285 IF l_debug_level > 0 THEN
1286 oe_Debug_pub.add(' ..104.. Calculating first period amount from OEXVFULB ');
1287 End If;
1288 oe_delayed_requests_util.First_period_bill_amt (
1289 p_header_id => '',
1290 p_line_id => p_line_tbl(l_index).line_id,
1291 x_return_status => l_return_status);
1292 -- reset back to N
1293 l_recalc_First_periodamt := 'N';
1294 End If;
1295 -- sol_ord_er #16014165 end
1296
1297 /* Start Improved OM,IB,OKS changes */
1298 select order_number
1299 into l_order_number
1300 from oe_order_headers_all
1301 where header_id = p_line_tbl(l_index).header_id;
1302
1303 l_Service_Order_Lines(1).Order_Header_ID := p_line_tbl(l_index).header_id;
1304 l_Service_Order_Lines(1).Order_Line_ID := p_line_tbl(l_index).line_id;
1305 l_Service_Order_Lines(1).Order_Number := l_order_number;
1306 l_Service_Order_Lines(1).Ref_Order_Line_ID := p_line_tbl(l_index).service_reference_line_id;
1307 IF l_debug_level > 0 THEN
1308 oe_debug_pub.add('Calling OKS_OMINT_PUB.Interface_Service_Order_Lines:LineID:'
1309 || p_line_tbl(l_index).line_id,3) ;
1310 END IF;
1311
1312 OKS_OMINT_PUB.Interface_Service_Order_Lines(
1313 p_Service_Order_Lines => l_Service_Order_Lines,
1314 x_Return_Status => l_return_status,
1315 x_Error_Message => l_msg_data);
1316
1317 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1318 oe_debug_pub.add('FND_API.G_RET_STS_ERROR after call to OKS_OMINT_PUB.Interface_Service');
1319 oe_debug_pub.add('l_msg_data' || l_msg_data);
1320 RAISE FND_API.G_EXC_ERROR;
1321 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1322 oe_debug_pub.add('FND_API.G_RET_STS_UNEXP_ERROR after call to OKS_OMINT_PUB.Interface_Service');
1323 oe_debug_pub.add('l_msg_data' || l_msg_data);
1324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1325 END IF;
1326 /* End Improved OM,IB,OKS changes */
1327
1328
1329 ELSE -- 2417601
1330
1331 UPDATE OE_ORDER_LINES_ALL
1332 SET FULFILLED_FLAG = p_line_tbl(l_index).fulfilled_flag,
1333 FULFILLED_QUANTITY = p_line_tbl(l_index).fulfilled_quantity,
1334 FULFILLED_QUANTITY2 = p_line_tbl(l_index).fulfilled_quantity2, -- 5126873
1335 FULFILLMENT_DATE = p_line_tbl(l_index).fulfillment_date,
1336 LAST_UPDATE_DATE = SYSDATE,
1337 LAST_UPDATED_BY = l_user, -- 3169637
1338 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1339 ACTUAL_FULFILLMENT_DATE = l_actual_fulfillment_date,
1340 LOCK_CONTROL = LOCK_CONTROL+1
1341 WHERE LINE_ID = p_line_tbl(l_index).line_id;
1342
1343
1344 END IF; -- 2417601
1345
1346 -- Added 09-Dec-2002
1347 -- BLANKETS: Update returned/fulfilled qty
1348 IF OE_CODE_CONTROL.Code_Release_Level >= '110509' THEN
1349 -- Bug 3061559
1350 -- Use p_line_tbl, not l_line_tbl.
1351 if l_debug_level > 0 then
1352 oe_debug_pub.add('OEXVFULB 2, blanket number :'|| p_line_tbl(l_index).blanket_number);
1353 oe_debug_pub.add('Line ID : '|| p_line_tbl(l_index).line_id);
1354 oe_debug_pub.add('Fulfilled Qty : '|| p_line_tbl(l_index).fulfilled_quantity);
1355 oe_debug_pub.add('Fulfilled Qty2 : '|| p_line_tbl(l_index).fulfilled_quantity2); -- 5126873
1356 oe_debug_pub.add('Item Type : '|| p_line_tbl(l_index).item_type_code);
1357 end if;
1358 IF p_line_tbl(l_index).blanket_number IS NOT NULL THEN
1359 Update_Blanket_Qty(p_line_tbl(l_index)
1360 ,p_line_tbl(l_index).fulfilled_quantity);
1361 END IF;
1362 END IF;
1363
1364 oe_debug_pub.add(' FND_PROFILE.VALUE(ONT_AUTO_INTERFACE_LINES_TO_IB) =>> ' || FND_PROFILE.VALUE('ONT_AUTO_INTERFACE_LINES_TO_IB'), 5);
1365
1366 IF p_line_tbl(l_index).item_type_code <> OE_GLOBALS.G_ITEM_SERVICE
1367 AND NVL(p_line_tbl(l_index).subscription_enable_flag,'N') <> 'Y' -- sol_ord_er #16014165 (for Model)
1368 AND ( NVL(FND_PROFILE.VALUE('ONT_AUTO_INTERFACE_LINES_TO_IB'),'Y') = 'Y') -- bug 9245134
1369 THEN
1370 oe_debug_pub.add('Before Call to csi_ont_txn_pub.posttransaction',1);
1371 csi_ont_txn_pub.posttransaction(
1372 p_order_line_id => p_line_tbl(l_index).line_id,
1373 x_return_status => x_return_status,
1374 x_message_id => l_message_id,
1375 x_error_code => l_error_code,
1376 x_error_message => l_error_message);
1377 oe_debug_pub.add('After Call to csi_ont_txn_pub.posttransaction',1);
1378
1379 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1380 IF l_debug_level > 0 THEN
1381 oe_debug_pub.add('raising unexpected error in csi_ont_txn_pub.posttransaction '||sqlerrm,1);
1382 END IF;
1383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1384 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1385 IF l_debug_level > 0 THEN
1386 oe_debug_pub.add('raising exc error in csi_ont_txn_pub.posttransaction '||sqlerrm,1);
1387 END IF;
1388 RAISE FND_API.G_EXC_ERROR;
1389 END IF;
1390 END IF;
1391
1392 END LOOP;
1393
1394 END IF;
1395
1396 /* Commented for performance changes.
1397 oe_debug_pub.ADD('Calling OE_Order_PVT.Process_Order to update the fulfilled quantity and fulfilled flag',2);
1398
1399 OE_GLOBALS.G_RECURSION_MODE := 'Y';
1400 OE_Shipping_Integration_PVT.Call_Process_Order
1401 (
1402 p_line_tbl => l_update_line_tbl,
1403 p_control_rec => l_control_rec,
1404 x_return_status => l_return_status
1405 );
1406
1407 OE_GLOBALS.G_RECURSION_MODE := 'N';
1408
1409 oe_debug_pub.add('Return Status from Process Order : '||l_return_status,2);
1410
1411 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1413 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1414 RAISE FND_API.G_EXC_ERROR;
1415 END IF;
1416 */
1417 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL < '110508' THEN
1418 IF (OE_GLOBALS.G_ASO_INSTALLED = 'Y') THEN
1419
1420 -- #2769599
1421 IF (nvl(oe_order_cache.g_header_rec.header_id,-999)<>p_line_rec.header_id) THEN
1422 IF l_debug_level > 0 THEN
1423 OE_DEBUG_PUB.add('Loading header rec from cache : ',5);
1424 END IF;
1425 OE_Order_Cache.Load_Order_Header(p_line_rec.header_id);
1426 END IF;
1427 IF l_debug_level > 0 THEN
1428 OE_DEBUG_PUB.add('Cached header id: '||to_char(oe_order_cache.g_header_rec.header_id), 5);
1429 END IF;
1430 OE_Order_PVT.Process_Requests_And_Notify
1431 ( p_process_requests => FALSE
1432 , p_notify => TRUE
1433 , x_return_status => l_return_status
1434 , p_line_tbl => l_line_tbl
1435 , p_old_line_tbl => l_aso_line_tbl
1436 );
1437
1438
1439 IF l_debug_level > 0 THEN
1440 oe_debug_pub.add( 'RETURNED FROM PROCESS REQUEST AND NOTIFY : '||L_RETURN_STATUS , 3 ) ;
1441 END IF;
1442 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1443 RAISE FND_API.G_EXC_ERROR;
1444 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1446 END IF;
1447 END IF; /* ASO installed */
1448
1449
1450 ELSE
1451 /* Pack H or higher */
1452 IF ( (OE_GLOBALS.G_ASO_INSTALLED = 'Y') OR
1453 ( NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y') ) THEN
1454
1455
1456 OE_ORDER_PVT.Process_Requests_and_Notify
1457 (p_process_requests => FALSE
1458 ,p_notify => FALSE
1459 ,x_return_status => l_return_status
1460 ,p_line_tbl => l_line_tbl
1461 ,p_old_line_tbl => l_aso_line_tbl
1462 );
1463
1464 IF l_debug_level > 0 THEN
1465 oe_debug_pub.add('returned from process request and notify : '||l_return_status,3) ;
1466 END IF;
1467
1468 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1469 RAISE FND_API.G_EXC_ERROR;
1470 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1472 END IF;
1473
1474
1475 END IF; /* ASO and DBI check for Pack H and higher */
1476
1477 END IF; --code_release_level
1478 IF l_debug_level > 0 THEN -- debug level changed to 0.5 for bug 13435459
1479 oe_debug_pub.add('Exiting fulfill_line() procedure in OEXVFULB at ',0.5) ;
1480 END IF;
1481 x_return_status := FND_API.G_RET_STS_SUCCESS;
1482
1483 EXCEPTION
1484 WHEN CAN_NOT_LOCK_MODEL THEN
1485 IF l_debug_level > 0 THEN
1486 oe_debug_pub.add( 'OEXVFULB.pls:Fulfill_line API- MODEL LOCKING EXCEPTION' , 1 ) ;
1487 END IF;
1488 x_return_status := 'D';
1489
1490 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1491 IF l_debug_level > 0 THEN
1492 oe_debug_pub.add('Unexp Error'||sqlerrm,1);
1493 END IF;
1494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1495 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1496 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Fulfill_Line');
1497 END IF;
1498 G_DEBUG_MSG := G_DEBUG_MSG || 'E6,';
1499 WHEN FND_API.G_EXC_ERROR THEN
1500 IF l_debug_level > 0 THEN
1501 oe_debug_pub.add('EXC Error '||sqlerrm,1);
1502 END IF;
1503 x_return_status := FND_API.G_RET_STS_ERROR;
1504 WHEN OTHERS THEN
1505 IF l_debug_level > 0 THEN
1506 oe_debug_pub.add('Error '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1507 END IF;
1508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1509 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1510 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Fulfill_Line');
1511 END IF;
1512 G_DEBUG_MSG := G_DEBUG_MSG || 'E7,';
1513 END Fulfill_Line;
1514
1515 /*
1516 This procedure is to get the work flow activity attribute for a given
1517 item type, item key, activity id and attribute name using work flow
1518 engine API GetActivityAttrText.
1519 */
1520
1521 PROCEDURE Get_Activity_Attribute
1522 (
1523 p_item_type IN VARCHAR2
1524 , p_item_key IN VARCHAR2
1525 , p_activity_id IN VARCHAR2
1526 , p_fulfill_attr_name IN VARCHAR2
1527 , x_attribute_value OUT NOCOPY VARCHAR2
1528
1529 , x_return_status OUT NOCOPY VARCHAR2
1530
1531 )
1532 IS
1533 l_errname VARCHAR2(30);
1534 l_errmsg VARCHAR2(2000);
1535 l_errstack VARCHAR2(2000);
1536 --
1537 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1538 --
1539 BEGIN
1540 x_attribute_value := wf_engine.GetActivityAttrText(p_item_type,p_item_key,p_activity_id,p_fulfill_attr_name);
1541
1542 x_return_status := FND_API.G_RET_STS_SUCCESS;
1543
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546 IF l_debug_level > 0 THEN
1547 oe_debug_pub.add( 'WORK FLOW ERROR HAS OCCURED ' , 1 ) ;
1548 END IF;
1549 WF_CORE.Get_Error(l_errname, l_errmsg, l_errstack);
1550 IF l_errname = 'WFENG_ACTIVITY_ATTR' THEN
1551 IF l_debug_level > 0 THEN
1552 oe_debug_pub.add( 'ERROR MESSAGE '||L_ERRMSG , 1 ) ;
1553 END IF;
1554 x_attribute_value := 'NONE';
1555 x_return_status := FND_API.G_RET_STS_SUCCESS;
1556 ELSE
1557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1558 END IF;
1559
1560 END Get_Activity_Attribute;
1561
1562 /*
1563 This API is to get the Activity result code and activity status from work
1564 flow tables for given item type, item key and activity name
1565 */
1566
1567 PROCEDURE Get_Activity_Result
1568 (
1569 p_item_type IN VARCHAR2
1570 , p_item_key IN VARCHAR2
1571 , p_activity_name IN VARCHAR2
1572 , x_return_status OUT NOCOPY VARCHAR2
1573
1574 , x_activity_result OUT NOCOPY VARCHAR2
1575
1576 , x_activity_status_code OUT NOCOPY VARCHAR2
1577
1578 , x_activity_id OUT NOCOPY NUMBER
1579
1580 )
1581 IS
1582 l_upgraded_flag VARCHAR2(1);
1583 --
1584 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1585 --
1586 BEGIN
1587
1588 IF l_debug_level > 0 THEN
1589 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.GET_ACTIVITY_RESULT '|| P_ITEM_TYPE||'/'||P_ITEM_KEY||'/'||P_ACTIVITY_NAME , 1 ) ;
1590 END IF;
1591
1592 SELECT wias.ACTIVITY_STATUS, wias.ACTIVITY_RESULT_CODE, wias.PROCESS_ACTIVITY
1593 INTO x_activity_status_code, x_activity_result, x_activity_id
1594 FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
1595 WHERE wias.ITEM_KEY = p_item_key
1596 AND wias.ITEM_TYPE = p_item_type
1597 AND wpa.ACTIVITY_NAME = p_activity_name
1598 AND wias.PROCESS_ACTIVITY = wpa.INSTANCE_ID;
1599
1600 x_return_status := FND_API.G_RET_STS_SUCCESS;
1601
1602 IF l_debug_level > 0 THEN
1603 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.GET_ACTIVITY_RESULT '||X_ACTIVITY_RESULT||'/'||X_RETURN_STATUS , 1 ) ;
1604 END IF;
1605
1606 EXCEPTION
1607 WHEN NO_DATA_FOUND THEN
1608 --- commmented for 3589692
1609 /*
1610 -- Bug-2791964
1611 SELECT nvl(upgraded_flag,'N')
1612 INTO l_upgraded_flag
1613 FROM oe_order_lines
1614 WHERE line_id = to_number(p_item_key);
1615
1616 IF l_upgraded_flag = 'N' THEN
1617 x_return_status := FND_API.G_RET_STS_ERROR;
1618 ELSE
1619 x_return_status := FND_API.G_RET_STS_SUCCESS;
1620 END IF;
1621 */
1622 x_return_status := FND_API.G_RET_STS_ERROR;
1623 G_DEBUG_MSG := G_DEBUG_MSG || '34,';
1624 WHEN OTHERS THEN
1625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1626
1627 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1628 THEN
1629 OE_MSG_PUB.Add_Exc_Msg
1630 ( G_PKG_NAME,
1631 'Get_Activity_Result'
1632 );
1633 END IF;
1634 IF l_debug_level > 0 THEN
1635 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1636 END IF;
1637 G_DEBUG_MSG := G_DEBUG_MSG || '35,';
1638
1639 END Get_Activity_Result;
1640
1641 /*
1642 This procedure gets all the lines and sets for a given line_id from
1643 oe_sets and oe_line_sets tables
1644 */
1645
1646 PROCEDURE Get_Fulfillment_Set
1647 (
1648 p_line_id IN NUMBER
1649 , x_return_status OUT NOCOPY VARCHAR2
1650
1651 , x_set_tbl OUT NOCOPY Line_Set_Tbl_Type
1652
1653 )
1654 IS
1655
1656 CURSOR c_set IS
1657 SELECT OLS.LINE_ID, OLS.SET_ID
1658 FROM OE_LINE_SETS OLS, OE_SETS OST
1659 WHERE OLS.LINE_ID = p_line_id
1660 AND OLS.SET_ID = OST.SET_ID
1661 AND OST.SET_TYPE = 'FULFILLMENT_SET';
1662
1663 l_set_id NUMBER;
1664
1665 CURSOR c_line_set IS
1666 SELECT LINE_ID, SET_ID
1667 FROM OE_LINE_SETS
1668 WHERE SET_ID = l_set_id;
1669
1670 l_set_index NUMBER := 0;
1671 l_set_tbl Line_Set_Tbl_Type;
1672 l_temp_tbl Line_Set_Tbl_Type;
1673 l_return_status VARCHAR2(1);
1674 l_loop_index NUMBER := 0;
1675 l_temp_index NUMBER := 0;
1676
1677 l_line_set_exists VARCHAR2(1) := FND_API.G_FALSE;
1678 --Start 7827727
1679 l_line_id_mod NUMBER;
1680 l_set_id_mod NUMBER;
1681 --End 7827727
1682
1683 --
1684 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1685 --
1686 BEGIN
1687
1688 IF l_debug_level > 0 THEN
1689 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.GET_FULFILLMENT_SET '|| TO_CHAR ( P_LINE_ID ) , 1 ) ;
1690 END IF;
1691
1692
1693
1694 FOR l_set in c_set
1695 LOOP
1696
1697 l_set_id := l_set.set_id;
1698 IF l_debug_level > 0 THEN
1699 oe_debug_pub.add( 'SET ID : '||TO_CHAR ( L_SET_ID ) , 3 ) ;
1700 END IF;
1701
1702 l_set_id_mod := MOD(l_set_id,G_BINARY_LIMIT); --7827727
1703 IF g_set_tbl.EXISTS(l_set_id_mod) THEN --7827727
1704 -- IF g_set_tbl.EXISTS(l_set_id) THEN
1705 GOTO END_SET_LOOP;
1706 END IF;
1707 --g_set_tbl(l_set_id) := l_set_id;
1708 g_set_tbl(l_set_id_mod) := l_set_id; --7827727
1709 IF l_debug_level > 0 THEN
1710 oe_debug_pub.add( 'SET ID ADDED : '||TO_CHAR ( L_SET_ID ) , 3 ) ;
1711 END IF;
1712
1713 FOR l_line_set in c_line_set
1714 LOOP
1715 l_line_set_exists := FND_API.G_FALSE;
1716 FOR l_temp_index IN 1..l_temp_tbl.count
1717 LOOP
1718
1719 IF l_temp_tbl(l_temp_index).line_id = l_line_set.line_id AND
1720 l_temp_tbl(l_temp_index).set_id = l_set_id THEN
1721 IF l_debug_level > 0 THEN
1722 oe_debug_pub.add( 'LINE EXISTS FOR COMBINATION' , 3 ) ;
1723 END IF;
1724 l_line_set_exists := FND_API.G_TRUE;
1725 GOTO END_LINE_SET;
1726
1727 END IF;
1728
1729 << END_LINE_SET >>
1730 NULL;
1731 END LOOP;
1732
1733
1734 IF l_line_set_exists = FND_API.G_FALSE THEN
1735 l_temp_index := l_temp_tbl.count + 1;
1736 l_temp_tbl(l_temp_index).line_id := l_line_set.line_id;
1737 l_temp_tbl(l_temp_index).set_id := l_line_set.set_id;
1738 IF l_debug_level > 0 THEN
1739 oe_debug_pub.add('adding line to set table '||l_temp_tbl(l_temp_index).line_id||'/'||l_temp_tbl(l_temp_index).set_id,5);
1740 END IF;
1741 END IF;
1742
1743 IF l_line_set.line_id <> p_line_id THEN
1744
1745 Get_Fulfillment_Set
1746 (
1747 p_line_id => l_line_set.line_id
1748 , x_return_status => l_return_status
1749 , x_set_tbl => l_set_tbl
1750 );
1751
1752
1753 IF l_debug_level > 0 THEN
1754 oe_debug_pub.add( 'NUMBER OF ROWS RETURNED : '||L_SET_TBL.COUNT , 3 ) ;
1755 END IF;
1756
1757 IF l_set_tbl.COUNT > 0 THEN
1758 l_loop_index := 0;
1759 FOR l_loop_index IN l_set_tbl.FIRST .. l_set_tbl.LAST
1760 LOOP
1761
1762 l_line_set_exists := FND_API.G_FALSE;
1763 FOR l_temp_index IN 1..l_temp_tbl.count
1764 LOOP
1765
1766 IF l_temp_tbl(l_temp_index).line_id = l_set_tbl(l_loop_index).line_id AND
1767 l_temp_tbl(l_temp_index).set_id = l_set_tbl(l_loop_index).set_id THEN
1768 IF l_debug_level > 0 THEN
1769 oe_debug_pub.add( 'LINE EXISTS FOR COMBINATION' , 3 ) ;
1770 END IF;
1771 l_line_set_exists := FND_API.G_TRUE;
1772 GOTO END_LINE_SET1;
1773
1774 END IF;
1775
1776 << END_LINE_SET1 >>
1777 NULL;
1778 END LOOP;
1779
1780 IF l_line_set_exists = FND_API.G_FALSE THEN
1781 l_temp_index := l_temp_tbl.count + 1;
1782 l_temp_tbl(l_temp_index).line_id := l_set_tbl(l_loop_index).line_id;
1783 l_temp_tbl(l_temp_index).set_id := l_set_tbl(l_loop_index).set_id;
1784 IF l_debug_level > 0 THEN
1785 oe_debug_pub.add('line does not exist, adding now '||l_temp_tbl(l_temp_index).line_id||'/'||l_temp_tbl(l_temp_index).set_id,3);
1786 END IF;
1787 END IF;
1788 END LOOP;
1789 END IF;
1790 END IF;
1791 END LOOP;
1792
1793 << END_SET_LOOP >>
1794 NULL;
1795
1796 END LOOP;
1797
1798 l_set_index := l_temp_tbl.FIRST;
1799 l_loop_index := 0;
1800 IF l_debug_level>0 THEN
1801 oe_debug_pub.add('SET table picture',5);
1802 END IF;
1803
1804 WHILE l_set_index IS NOT NULL
1805 LOOP
1806
1807 l_loop_index := l_loop_index + 1;
1808 x_set_tbl(l_loop_index).line_id := l_temp_tbl(l_set_index).line_id;
1809 x_set_tbl(l_loop_index).set_id := l_temp_tbl(l_set_index).set_id;
1810 l_set_index := l_temp_tbl.NEXT(l_set_index);
1811 IF l_debug_level > 0 THEN
1812 oe_debug_pub.add( 'LINE/SET : '||TO_CHAR ( X_SET_TBL ( L_LOOP_INDEX ) .LINE_ID ) ||'/'||TO_CHAR ( X_SET_TBL ( L_LOOP_INDEX ) .SET_ID ) , 3 ) ;
1813 END IF;
1814
1815 END LOOP;
1816
1817 x_return_status := FND_API.G_RET_STS_SUCCESS;
1818 IF l_debug_level > 0 THEN
1819 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.GET_FULFILLMENT_SET ' , 3 ) ;
1820 END IF;
1821
1822 EXCEPTION
1823 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1824 IF l_debug_level > 0 THEN
1825 oe_debug_pub.add('Unexp Error '||sqlerrm,1);
1826 END IF;
1827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1828
1829 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1830 THEN
1831 OE_MSG_PUB.Add_Exc_Msg
1832 ( G_PKG_NAME,
1833 'Get_Fulfillment_Set'
1834 );
1835 END IF;
1836 G_DEBUG_MSG := G_DEBUG_MSG || 'E10,';
1837
1838 WHEN FND_API.G_EXC_ERROR THEN
1839 IF l_debug_level > 0 THEN
1840 oe_debug_pub.add('Exc Error '||sqlerrm,1);
1841 END IF;
1842 x_return_status := FND_API.G_RET_STS_ERROR;
1843 WHEN OTHERS THEN
1844 IF l_debug_level > 0 THEN
1845 oe_debug_pub.add('error '||SUBSTR(SQLERRM,1,2000),1);
1846 END IF;
1847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1848 IF l_debug_level > 0 THEN
1849 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1850 END IF;
1851
1852 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1853 THEN
1854 OE_MSG_PUB.Add_Exc_Msg
1855 ( G_PKG_NAME,
1856 'Get_Fulfillment_Set'
1857 );
1858 END IF;
1859 G_DEBUG_MSG := G_DEBUG_MSG || 'E11,';
1860
1861 END Get_Fulfillment_Set;
1862
1863 /*
1864 This procedure derives the fulfillment activity(method) for a given item
1865 key and activity id. The fulfillment activity is derived using activity
1866 attributes FULFILLMENT_ACTIVITY and COMPLETION_RESULT for item 'OEOL' and
1867 activity FULFILL_LINE. If no FULFILLMENT_ACTIVITY is defined fulfillment
1868 activity will be returned as 'NO_ACTIVITY'
1869 */
1870
1871 PROCEDURE Get_Fulfillment_Activity
1872 (
1873 p_item_key IN VARCHAR2
1874 , p_activity_id IN NUMBER
1875 , x_fulfillment_activity OUT NOCOPY VARCHAR2
1876
1877 , x_return_status OUT NOCOPY VARCHAR2
1878
1879 )
1880 IS
1881 l_fulfill_attr_name VARCHAR2(30) := 'FULFILLMENT_ACTIVITY';
1882 l_in_fulfill_attr_name VARCHAR2(30) := 'INBOUND_FULFILLMENT_ACTIVITY';
1883 l_fulfill_activity_name VARCHAR2(30);
1884 l_item_type VARCHAR2(8) := OE_GLOBALS.G_WFI_LIN;
1885 l_result_attr_name VARCHAR2(30) := 'COMPLETION_RESULT';
1886 l_in_result_attr_name VARCHAR2(30) := 'INBOUND_COMPLETION_RESULT';
1887 l_completion_result VARCHAR2(30);
1888 l_activity_result VARCHAR2(30) := 'NO_RESULT';
1889 l_activity_status VARCHAR2(8);
1890 l_activity_id NUMBER;
1891 l_return_status VARCHAR2(1);
1892 l_line_category_code VARCHAR2(30);
1893 --
1894 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1895 l_upgraded_flag VARCHAR2(1); -- bug 3589692
1896 BEGIN
1897
1898 IF l_debug_level > 0 THEN
1899 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.GET_FULFILLMENT_ACTIVITY '|| P_ITEM_KEY , 1 ) ;
1900 END IF;
1901
1902
1903 select line_category_code into l_line_category_code
1904 from oe_order_lines_all
1905 where line_id = to_number(p_item_key);
1906
1907 IF l_line_category_code = 'RETURN' THEN
1908 l_fulfill_attr_name := l_in_fulfill_attr_name;
1909 l_result_attr_name := l_in_result_attr_name;
1910 END IF;
1911
1912 IF l_debug_level > 0 THEN
1913 oe_debug_pub.add( 'CALLING GET ATTRIBUTE - ACTIVITY ' , 3 ) ;
1914 END IF;
1915 Get_Activity_Attribute
1916 (
1917 p_item_type => l_item_type,
1918 p_item_key => p_item_key,
1919 p_activity_id => p_activity_id,
1920 p_fulfill_attr_name => l_fulfill_attr_name,
1921 x_attribute_value => l_fulfill_activity_name,
1922 x_return_status => l_return_status
1923 );
1924
1925
1926 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1928 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1929 RAISE FND_API.G_EXC_ERROR;
1930 END IF;
1931
1932 IF l_fulfill_activity_name = 'NONE' OR
1933 l_fulfill_activity_name IS NULL THEN
1934 l_fulfill_activity_name := 'NO_ACTIVITY';
1935 END IF;
1936
1937 IF l_debug_level > 0 THEN
1938 oe_debug_pub.add( 'FULFILLMENT ACTIVITY NAME : '|| L_FULFILL_ACTIVITY_NAME , 3 ) ;
1939 END IF;
1940 IF l_fulfill_activity_name <> 'NO_ACTIVITY' THEN
1941
1942 IF l_debug_level > 0 THEN
1943 oe_debug_pub.add( 'CALLING GET ATTRIBUTE - RESULT ' , 3 ) ;
1944 END IF;
1945
1946
1947 Get_Activity_Attribute
1948 (
1949 p_item_type => l_item_type,
1950 p_item_key => p_item_key,
1951 p_activity_id => p_activity_id,
1952 p_fulfill_attr_name => l_result_attr_name,
1953 x_attribute_value => l_completion_result,
1954 x_return_status => l_return_status
1955 );
1956
1957 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1959 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1960 RAISE FND_API.G_EXC_ERROR;
1961 END IF;
1962
1963 IF l_completion_result = 'NONE'OR
1964 l_completion_result IS NULL THEN
1965 l_completion_result := 'NO_RESULT';
1966 ELSE
1967
1968
1969 IF l_debug_level > 0 THEN
1970 oe_debug_pub.add( 'REQUIRED COMPLETION RESULT : '|| L_COMPLETION_RESULT , 3 ) ;
1971 END IF;
1972
1973 IF l_debug_level > 0 THEN
1974 oe_debug_pub.add( 'CALLING GET_ACTIVITY_STATUS ' , 3 ) ;
1975 END IF;
1976
1977 Get_Activity_Result
1978 (
1979 p_item_type => l_item_type
1980 , p_item_key => p_item_key
1981 , p_activity_name => l_fulfill_activity_name
1982 , x_return_status => l_return_status
1983 , x_activity_result => l_activity_result
1984 , x_activity_status_code => l_activity_status
1985 , x_activity_id => l_activity_id
1986 );
1987
1988 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1990 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1991 SELECT nvl(upgraded_flag,'N') -- Bug 3589692
1992 INTO l_upgraded_flag
1993 FROM oe_order_lines
1994 WHERE line_id = to_number(p_item_key);
1995 IF l_upgraded_flag = 'N' THEN
1996 RAISE FND_API.G_EXC_ERROR;
1997 ELSE
1998 l_activity_status := OE_GLOBALS.G_WFR_COMPLETE;
1999 END IF; -- bug 3589692 ends
2000 END IF;
2001
2002 IF l_activity_status <> OE_GLOBALS.G_WFR_COMPLETE THEN
2003 RAISE FND_API.G_EXC_ERROR;
2004 END IF;
2005
2006 END IF;
2007
2008 IF l_completion_result <> 'NO_RESULT' THEN
2009 IF l_activity_result <> l_completion_result AND -- added for 1570684
2010 nvl(l_activity_result, 'NONE') <> 'OVER_SHIPPED' THEN
2011 l_fulfill_activity_name := 'NO_ACTIVITY';
2012 END IF;
2013 END IF;
2014
2015
2016 END IF;
2017
2018 x_fulfillment_activity := l_fulfill_activity_name;
2019 x_return_status := FND_API.G_RET_STS_SUCCESS;
2020
2021 EXCEPTION
2022 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2024
2025 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2026 THEN
2027 OE_MSG_PUB.Add_Exc_Msg
2028 ( G_PKG_NAME,
2029 'Get_Fulfillment_Activity'
2030 );
2031 END IF;
2032 G_DEBUG_MSG := G_DEBUG_MSG || 'E12,';
2033
2034 WHEN FND_API.G_EXC_ERROR THEN
2035 x_return_status := FND_API.G_RET_STS_ERROR;
2036 WHEN OTHERS THEN
2037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2038 IF l_debug_level > 0 THEN
2039 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
2040 END IF;
2041
2042 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2043 THEN
2044 OE_MSG_PUB.Add_Exc_Msg
2045 ( G_PKG_NAME,
2046 'Get_Fulfillment_Activity'
2047 );
2048 END IF;
2049 G_DEBUG_MSG := G_DEBUG_MSG || 'E13,';
2050
2051
2052 END Get_Fulfillment_Activity;
2053
2054 PROCEDURE Check_PTO_KIT_Fulfillment
2055 (
2056 p_top_model_line_id IN NUMBER,
2057 x_fulfill_status OUT NOCOPY VARCHAR2,
2058
2059 x_return_status OUT NOCOPY VARCHAR2
2060
2061 )
2062 IS
2063
2064 l_top_model_line_id NUMBER;
2065
2066 CURSOR C_lines IS
2067 SELECT LINE_ID,
2068 FULFILLED_FLAG
2069 FROM OE_ORDER_LINES
2070 WHERE TOP_MODEL_LINE_ID = l_top_model_line_id
2071 AND OPEN_FLAG = 'Y';
2072
2073 l_activity_status VARCHAR2(8);
2074 l_activity_result VARCHAR2(30);
2075 l_activity_id NUMBER;
2076 l_item_key VARCHAR2(240);
2077 l_fulfill_activity VARCHAR2(30) := 'FULFILL_LINE';
2078 l_return_status VARCHAR2(1);
2079 l_fulfill_status VARCHAR2(1) := FND_API.G_TRUE;
2080
2081 --
2082 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2083 --
2084 BEGIN
2085
2086 IF l_debug_level > 0 THEN
2087 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.CHECK_PTO_KIT_FULFILLMENT '|| TO_CHAR ( P_TOP_MODEL_LINE_ID ) , 1 ) ;
2088 END IF;
2089
2090
2091 l_top_model_line_id := p_top_model_line_id;
2092
2093 FOR l_top_lines IN c_lines
2094 LOOP
2095
2096 l_item_key := to_char(l_top_lines.line_id);
2097
2098 IF l_debug_level > 0 THEN
2099 oe_debug_pub.add( 'CALLING GET ACTIVITY RESULT FOR ITEM : '||L_ITEM_KEY||'/'||L_FULFILL_ACTIVITY , 3 ) ;
2100 END IF;
2101
2102 Get_Activity_Result
2103 (
2104 p_item_type => OE_GLOBALS.G_WFI_LIN
2105 , p_item_key => l_item_key
2106 , p_activity_name => l_fulfill_activity
2107 , x_return_status => l_return_status
2108 , x_activity_result => l_activity_result
2109 , x_activity_status_code => l_activity_status
2110 , x_activity_id => l_activity_id
2111 );
2112
2113 IF l_debug_level > 0 THEN
2114 oe_debug_pub.add( 'RETURN STATUS FROM GET ACTIVITY RESULT : '||L_RETURN_STATUS||'/'||L_ACTIVITY_STATUS , 3 ) ;
2115 END IF;
2116
2117 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2119 ELSIF l_return_status = FND_API.G_RET_STS_ERROR OR
2120 l_activity_status <> 'NOTIFIED' THEN
2121 l_fulfill_status := FND_API.G_FALSE;
2122 IF l_debug_level > 0 THEN
2123 oe_debug_pub.add( 'LINE IS NOT FULFILLED : '||TO_CHAR ( L_TOP_LINES.LINE_ID ) , 3 ) ;
2124 END IF;
2125 GOTO END_CHECK_LOOP;
2126 END IF;
2127
2128 END LOOP;
2129
2130
2131 << END_CHECK_LOOP >>
2132
2133 x_fulfill_status := l_fulfill_status;
2134 x_return_status := FND_API.G_RET_STS_SUCCESS;
2135
2136 IF l_debug_level > 0 THEN
2137 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.CHECK_PTO_KIT_FULFILLMENT '||L_FULFILL_STATUS||'/'||X_RETURN_STATUS , 1 ) ;
2138 END IF;
2139
2140 EXCEPTION
2141
2142 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2144
2145 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2146 THEN
2147 OE_MSG_PUB.Add_Exc_Msg
2148 ( G_PKG_NAME,
2149 'Check_PTO_KIT_Fulfillment'
2150 );
2151 END IF;
2152 G_DEBUG_MSG := G_DEBUG_MSG || 'E14-1,';
2153
2154 WHEN FND_API.G_EXC_ERROR THEN
2155 x_return_status := FND_API.G_RET_STS_ERROR;
2156 G_DEBUG_MSG := G_DEBUG_MSG || 'E14-2,';
2157 WHEN OTHERS THEN
2158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2159 IF l_debug_level > 0 THEN
2160 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
2161 END IF;
2162
2163 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2164 THEN
2165 OE_MSG_PUB.Add_Exc_Msg
2166 ( G_PKG_NAME,
2167 'Check_PTO_KIT_Fulfillment'
2168 );
2169 END IF;
2170 G_DEBUG_MSG := G_DEBUG_MSG || 'E14-3,';
2171
2172 END Check_PTO_KIT_Fulfillment;
2173
2174 PROCEDURE Fulfill_PTO_KIT
2175 (
2176 p_top_model_line_id IN NUMBER,
2177 x_return_status OUT NOCOPY VARCHAR2
2178 )
2179 IS
2180 l_top_model_line_id NUMBER;
2181 l_return_status VARCHAR2(1);
2182 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
2183 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
2184 l_index NUMBER;
2185 --Changes for AFD
2186 l_ordered_date DATE;
2187 --
2188 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2189 --
2190 -- Bug 14192627
2191 l_message_id NUMBER;
2192 l_error_code NUMBER;
2193 l_error_message VARCHAR2(4000);
2194 -- Bug 14192627
2195
2196 CAN_NOT_LOCK_MODEL EXCEPTION;
2197
2198 CURSOR C_fulfill_lines IS
2199 SELECT LINE_ID,
2200 HEADER_ID,
2201 FULFILLED_FLAG
2202 FROM OE_ORDER_LINES
2203 WHERE TOP_MODEL_LINE_ID = l_top_model_line_id
2204 AND OPEN_FLAG = 'Y';
2205 BEGIN
2206
2207 IF l_debug_level > 0 THEN
2208 oe_debug_pub.add('Entering oe_line_fullfill.fulfill_pto_kit() '||to_char(p_top_model_line_id),1) ;
2209 END IF;
2210
2211 l_top_model_line_id := p_top_model_line_id;
2212 FOR l_top_lines IN c_fulfill_lines LOOP
2213 IF nvl(l_top_lines.fulfilled_flag,'N') = 'N' THEN
2214 IF l_debug_level > 0 THEN
2215 oe_debug_pub.add('line is not fulfilled '||l_top_lines.line_id,3) ;
2216 END IF;
2217 OE_Line_Util.Lock_Rows
2218 (p_line_id => l_top_lines.line_id
2219 ,x_line_tbl => l_old_line_tbl
2220 ,x_return_status => l_return_status
2221 );
2222
2223 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2224 RAISE CAN_NOT_LOCK_MODEL;
2225 -- RAISE FND_API.G_EXC_ERROR;
2226 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2228 END IF;
2229
2230 l_line_tbl := l_old_line_tbl;
2231 l_line_tbl(1).fulfilled_flag := 'Y';
2232 l_line_tbl(1).fulfilled_quantity := nvl(l_line_tbl(1).shipped_quantity,l_line_tbl(1).ordered_quantity);
2233 l_line_tbl(1).fulfillment_date := SYSDATE;
2234
2235 -- CHANGES for AFD
2236 IF l_ordered_date is null THEN
2237 select ordered_date into l_ordered_date
2238 from oe_order_headers_all
2239 where header_id = l_line_tbl(1).header_id;
2240 END IF;
2241
2242 l_line_tbl(1).actual_fulfillment_date := nvl(l_line_tbl(1).actual_shipment_date,nvl(l_line_tbl(1).order_firmed_date,l_ordered_date));
2243 -- end CHANGES for AFD
2244
2245 update oe_order_lines
2246 set fulfilled_flag = l_line_tbl(1).fulfilled_flag,
2247 fulfilled_quantity = l_line_tbl(1).fulfilled_quantity,
2248 fulfillment_date = l_line_tbl(1).fulfillment_date,
2249 actual_fulfillment_date = l_line_tbl(1).actual_fulfillment_date,
2250 lock_control = lock_control+1
2251 where line_id = l_line_tbl(1).line_id;
2252
2253 -- Bug 14192627
2254 IF l_old_line_tbl(1).item_type_code <> OE_GLOBALS.G_ITEM_SERVICE
2255 AND NVL(l_old_line_tbl(1).subscription_enable_flag,'N') <> 'Y' -- sol_ord_er #16014165
2256 AND ( NVL(FND_PROFILE.VALUE('ONT_AUTO_INTERFACE_LINES_TO_IB'),'Y') = 'Y')
2257 THEN
2258 oe_debug_pub.add('Before Call to csi_ont_txn_pub.posttransaction',1);
2259 csi_ont_txn_pub.posttransaction
2260 (
2261 p_order_line_id => l_line_tbl(1).line_id,
2262 x_return_status => l_return_status,
2263 x_message_id => l_message_id,
2264 x_error_code => l_error_code,
2265 x_error_message => l_error_message);
2266 oe_debug_pub.add('After Call to csi_ont_txn_pub.posttransaction',1);
2267
2268 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2269 IF l_debug_level > 0 THEN
2270 oe_debug_pub.add('raising unexpected error in csi_ont_txn_pub.posttransaction'||sqlerrm,1);
2271 END IF;
2272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2273 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2274 IF l_debug_level > 0 THEN
2275 oe_debug_pub.add('raising exc error in csi_ont_txn_pub.posttransaction'||sqlerrm,1);
2276 END IF;
2277 RAISE FND_API.G_EXC_ERROR;
2278 END IF;
2279 END IF;
2280 -- Bug 14192627
2281
2282 -- added for notification framework
2283 -- check code release level first.
2284 -- Notification framework is at Pack H level
2285 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
2286 -- calling notification framework to get index position
2287 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
2288 p_header_id=>l_line_tbl(1).header_id,
2289 p_old_line_rec => l_old_line_tbl(1),
2290 p_line_rec =>l_line_tbl(1),
2291 p_line_id =>l_line_tbl(1).line_id ,
2292 x_index => l_index,
2293 x_return_status => l_return_status);
2294 IF l_debug_level > 0 THEN
2295 oe_debug_pub.add('update_global return status for line ID '||L_LINE_TBL(1).LINE_ID||'IS:'||l_return_status,1) ;
2296 oe_debug_pub.add('update_global index in fulfill_pto_kit for line_id '||l_line_tbl(1).LINE_ID ||' is: '||l_index,1);
2297 END IF;
2298 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2299 RAISE FND_API.G_EXC_ERROR;
2300 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2302 END IF;
2303 IF l_index IS NOT NULL THEN
2304 --update Global Picture directly
2305 OE_ORDER_UTIL.g_old_line_tbl(l_index) := l_old_line_tbl(1);
2306 OE_ORDER_UTIL.g_line_tbl(l_index) := OE_ORDER_UTIL.g_old_line_tbl(l_index);
2307 OE_ORDER_UTIL.g_line_tbl(l_index).header_id:= l_line_tbl(1).header_id;
2308 OE_ORDER_UTIL.g_line_tbl(l_index).line_id:= l_line_tbl(1).line_id;
2309 OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date:= l_line_tbl(1).last_update_date;
2310 OE_ORDER_UTIL.g_line_tbl(l_index).fulfilled_flag := l_line_tbl(1).fulfilled_flag;
2311 OE_ORDER_UTIL.g_line_tbl(l_index).fulfilled_quantity := l_line_tbl(1).fulfilled_quantity;
2312 OE_ORDER_UTIL.g_line_tbl(l_index).fulfillment_date := l_line_tbl(1).fulfillment_date;
2313 -- Changes for AFD
2314 OE_ORDER_UTIL.g_line_tbl(l_index).actual_fulfillment_date := l_line_tbl(1).actual_fulfillment_date;
2315 -- end Changes for AFD
2316 IF l_debug_level > 0 THEN
2317 oe_debug_pub.add('global line fulfilled_flag is: '||OE_ORDER_UTIL.G_LINE_TBL(l_index).fulfilled_flag,1) ;
2318 END IF;
2319 END IF;
2320
2321
2322
2323 /* Call Process Requests and Notify also since this does not call PO */
2324 /* p_notify will be seto False in this case as the Event Notification */
2325 /* framework does not look at this flag */
2326 OE_Order_PVT.Process_Requests_And_Notify
2327 (p_process_requests => FALSE
2328 ,p_notify => FALSE
2329 ,x_return_status => l_return_status
2330 ,p_line_tbl => l_line_tbl
2331 ,p_old_line_tbl => l_old_line_tbl
2332 );
2333 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2334 RAISE FND_API.G_EXC_ERROR;
2335 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2337 END IF;
2338
2339 ELSE /*pre-pack H*/
2340
2341 -- #2769599
2342 IF (nvl(oe_order_cache.g_header_rec.header_id,-999)<>l_top_lines.header_id) THEN
2343 IF l_debug_level > 0 THEN
2344 OE_DEBUG_PUB.add('Loading header rec from cache : ',5);
2345 END IF;
2346 OE_Order_Cache.Load_Order_Header(l_top_lines.header_id);
2347 END IF;
2348 IF l_debug_level > 0 THEN
2349 OE_DEBUG_PUB.add('Cached header id: '||to_char(oe_order_cache.g_header_rec.header_id), 5);
2350 END IF;
2351 OE_Order_PVT.Process_Requests_And_Notify
2352 ( p_process_requests => FALSE
2353 , p_notify => TRUE
2354 , x_return_status => l_return_status
2355 , p_line_tbl => l_line_tbl
2356 , p_old_line_tbl => l_old_line_tbl
2357 );
2358
2359
2360 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2361 RAISE FND_API.G_EXC_ERROR;
2362 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2364 END IF;
2365
2366 END IF; -- code_release_level
2367 END IF;
2368
2369 OE_Order_WF_Util.Update_Flow_Status_Code
2370 (p_line_id => l_top_lines.line_id,
2371 p_flow_status_code => 'FULFILLED',
2372 x_return_status => l_return_status
2373 );
2374
2375 IF l_debug_level > 0 THEN
2376 oe_debug_pub.add('Return status from flow status '||l_return_status,3) ;
2377 END IF;
2378 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2380 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2381 RAISE FND_API.G_EXC_ERROR;
2382 END IF;
2383 IF l_debug_level > 0 THEN
2384 oe_debug_pub.add('Fulfill associated service lines' , 3 ) ;
2385 END IF;
2386
2387 Fulfill_Service_Lines
2388 (
2389 p_line_id => l_top_lines.line_id,
2390 p_header_id => l_top_lines.header_id, -- 1717444
2391 x_return_status => l_return_status
2392 );
2393
2394 IF l_debug_level > 0 THEN
2395 oe_debug_pub.add('Return status from fulfill service lines '||l_return_status , 3 ) ;
2396 END IF;
2397
2398 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2400 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2401 RAISE FND_API.G_EXC_ERROR;
2402 END IF;
2403 IF l_debug_level > 0 THEN
2404 oe_debug_pub.add('Calling wf_engine.completeactivityinternalname '||to_char(l_top_lines.line_id),3) ;
2405 END IF;
2406 BEGIN
2407 wf_engine.CompleteActivityInternalName('OEOL', to_char(l_top_lines.line_id), 'FULFILL_LINE', '#NULL',TRUE ); --15870313
2408 EXCEPTION
2409 WHEN OTHERS THEN
2410 oe_debug_pub.add('Workflow complete activity failed. Raising');
2411 RAISE ;
2412 END ;
2413 IF l_debug_level > 0 THEN
2414 oe_debug_pub.add('returned from wf_engine.completeactivityinternalname '||to_char ( l_top_lines.line_id ) , 3 ) ;
2415 END IF;
2416
2417 END LOOP;
2418
2419 x_return_status := FND_API.G_RET_STS_SUCCESS;
2420
2421 IF l_debug_level > 0 THEN
2422 oe_debug_pub.add('Exiting oe_line_fullfill.fulfill_pto_kit '||x_return_status,3) ;
2423 END IF;
2424
2425 EXCEPTION
2426
2427 WHEN CAN_NOT_LOCK_MODEL THEN
2428 IF l_debug_level > 0 THEN
2429 oe_debug_pub.add( 'OEXVSHPB.pls:Fulfill_PTO_KIT-MODEL LOCKING EXCEPTION', 1 ) ;
2430 END IF;
2431 x_return_status := 'D' ;
2432
2433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2435 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2436 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Fulfill_PTO_KIT');
2437 END IF;
2438 G_DEBUG_MSG := G_DEBUG_MSG || 'E15,';
2439 WHEN FND_API.G_EXC_ERROR THEN
2440 x_return_status := FND_API.G_RET_STS_ERROR;
2441 G_DEBUG_MSG := G_DEBUG_MSG || 'E16,';
2442 WHEN OTHERS THEN
2443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2444 IF l_debug_level > 0 THEN
2445 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
2446 END IF;
2447 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2448 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Fulfill_PTO_KIT');
2449 END IF;
2450 G_DEBUG_MSG := G_DEBUG_MSG || 'E17,';
2451 END Fulfill_PTO_KIT;
2452
2453 /*
2454 This procedure is to do the processing of fulfillment for a line which is
2455 part of a PTO/KIT. This procedure checks if all the lines which have a
2456 fulfillment activity have been fulfilled or not. If all such lines are
2457 fulfilled then all the lines which don't have any fulfillment activity
2458 gets fulfilled, else the lines will wait at FULFILL_LINE workflow activity
2459 untill all the lines of MODEL are fulfilled. This procedure returns
2460 fulfillment status True if all the lines are fulfilled, else will return
2461 FALSE.
2462 */
2463 -- 5126873 - PTO KIT not supported for dual controlled items so no need to calculate fulfilled quantity2
2464 PROCEDURE Process_PTO_KIT
2465 (
2466 p_line_id IN NUMBER
2467 , p_top_model_line_id IN NUMBER
2468 , p_fulfillment_activity IN VARCHAR2
2469 , p_process_all IN VARCHAR2
2470 , p_part_of_fullfillment_set IN BOOLEAN := FALSE
2471 , x_return_status OUT NOCOPY VARCHAR2
2472
2473 , x_fulfillment_status OUT NOCOPY VARCHAr2
2474
2475 )
2476 IS
2477 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
2478 l_fulfill_tbl OE_Order_Pub.Line_Tbl_Type;
2479 l_service_tbl OE_Order_Pub.Line_Tbl_Type;
2480 l_service_index NUMBER;
2481 l_line_index NUMBER;
2482 l_fulfill_index NUMBER := 0 ;
2483 l_fulfilled_flag VARCHAR2(1) := FND_API.G_TRUE;
2484 l_return_status VARCHAR2(1);
2485 l_line_id NUMBER;
2486 l_activity_status VARCHAR2(8);
2487 l_activity_result VARCHAR2(30);
2488 l_activity_id NUMBER;
2489 l_item_key VARCHAR2(240);
2490 l_fulfill_activity VARCHAR2(30) := 'FULFILL_LINE';
2491 l_count NUMBER;
2492 l_item_type_code VARCHAR2(30);
2493 l_ato_line_id NUMBER;
2494 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2495 -- 4640517
2496 x_msg_count NUMBER;
2497 x_msg_data VARCHAR2(2000);
2498 l_hold_result_out VARCHAR2(30):= 'TRUE';
2499
2500 TYPE Fulfill_Service_Rec IS RECORD
2501 (
2502 line_id NUMBER := FND_API.G_MISS_NUM
2503 , header_id NUMBER := FND_API.G_MISS_NUM
2504 );
2505
2506 TYPE Fulfill_Service_Tbl IS TABLE OF Fulfill_Service_Rec
2507 INDEX BY BINARY_INTEGER;
2508
2509 l_fulfill_service_tbl Fulfill_Service_Tbl;
2510 l_fulfill_service_index NUMBER :=0;
2511
2512 CURSOR pto_kit_lines IS
2513 SELECT line_id, item_type_code, shippable_flag, fulfilled_flag
2514 FROM OE_ORDER_LINES
2515 WHERE top_model_line_id = p_top_model_line_id
2516 AND open_flag = 'Y';
2517 --
2518 BEGIN
2519
2520 IF l_debug_level > 0 THEN
2521 oe_debug_pub.add('enter Process_Pto_Kit() for line '||to_char(p_line_id),1);
2522 oe_debug_pub.add('fulfillment activity - '||p_fulfillment_activity,1);
2523 oe_debug_pub.add('process all - '||p_process_all,1);
2524 END IF;
2525
2526 IF p_line_id = p_top_model_line_id THEN
2527
2528 IF l_debug_level > 0 THEN
2529 oe_debug_pub.add('model line is at fulfillment, top model is '||p_top_model_line_id,1);
2530 END IF;
2531
2532 -- bug 3974488 changes starts
2533 IF l_debug_level > 0 THEN
2534 oe_debug_pub.add('Profile value OM: Allow Model Fulfillment = '||NVL(FND_PROFILE.VALUE('ONT_ALLOW_MODEL_FULFILL_WITHOUT_CONFIG'),'N'),1);
2535 END IF;
2536
2537 IF NVL(FND_PROFILE.VALUE('ONT_ALLOW_MODEL_FULFILL_WITHOUT_CONFIG'),'N') = 'N' THEN
2538
2539 SELECT count(*)
2540 INTO l_count
2541 FROM oe_order_lines
2542 WHERE top_model_line_id = p_line_id;
2543
2544
2545 IF l_count = 1 THEN
2546
2547 SELECT item_type_code,ato_line_id
2548 INTO l_item_type_code,l_ato_line_id
2549 FROM oe_order_lines
2550 WHERE line_id = p_line_id;
2551
2552 IF l_debug_level > 0 THEN
2553 oe_debug_pub.add('No child lines yet for the model' , 1 ) ;
2554 END IF;
2555
2556
2557 IF p_fulfillment_activity = 'NO_ACTIVITY' THEN
2558
2559 IF l_item_type_code = 'MODEL' THEN
2560
2561 IF l_debug_level > 0 THEN
2562 oe_debug_pub.add( 'CAN NOT FULFILL MODEL YET!!' , 1 ) ;
2563 END IF;
2564
2565 l_fulfilled_flag := FND_API.G_FALSE;
2566 GOTO END_PTO_KIT_LOOP;
2567
2568 END IF;
2569 END IF;
2570 END IF;
2571 END IF; -- Allow model filfillment profile
2572 END IF;
2573
2574
2575 /* Following loop is being changed to loop thru the cursor instead of
2576 the line table because of performance changes
2577 */
2578
2579
2580 FOR l_pto_kit_lines IN pto_kit_lines
2581 LOOP
2582
2583 IF l_debug_level > 0 THEN
2584 oe_debug_pub.add('line ID - '||to_char(l_pto_kit_lines.line_id),5);
2585 oe_debug_pub.add('item type/shippable - '||l_pto_kit_lines.item_type_code||'/'||l_pto_kit_lines.shippable_flag,5);
2586 END IF;
2587
2588
2589 IF l_pto_kit_lines.line_id <> p_line_id OR
2590 p_process_all = FND_API.G_TRUE THEN
2591 IF l_pto_kit_lines.fulfilled_flag = 'Y' THEN
2592 l_fulfilled_flag := FND_API.G_TRUE;
2593 IF l_debug_level > 0 THEN
2594 oe_debug_pub.add('line '||l_pto_kit_lines.line_id||' is already fulfilled ',5);
2595 END IF;
2596 ELSE
2597
2598 l_item_key := to_char(l_pto_kit_lines.line_id);
2599
2600 IF l_debug_level > 0 THEN
2601 oe_debug_pub.add('call Get_Activity_Result() for item - '||l_item_key||'/'||l_fulfill_activity,5);
2602 END IF;
2603
2604 Get_Activity_Result
2605 (
2606 p_item_type => OE_GLOBALS.G_WFI_LIN
2607 , p_item_key => l_item_key
2608 , p_activity_name => l_fulfill_activity
2609 , x_return_status => l_return_status
2610 , x_activity_result => l_activity_result
2611 , x_activity_status_code => l_activity_status
2612 , x_activity_id => l_activity_id
2613 );
2614
2615 IF l_debug_level > 0 THEN
2616 oe_debug_pub.add('return status - '||l_return_status||'/'||l_activity_status,5);
2617 END IF;
2618
2619 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2621 ELSIF l_return_status = FND_API.G_RET_STS_ERROR OR
2622 l_activity_status <> 'NOTIFIED' THEN
2623 l_fulfilled_flag := FND_API.G_FALSE;
2624 x_fulfillment_status := FND_API.G_FALSE;
2625 IF l_debug_level > 0 THEN
2626 oe_debug_pub.add('line '||l_pto_kit_lines.line_id||' is not fulfilled ',5);
2627 END IF;
2628 GOTO END_PTO_KIT_LOOP;
2629 END IF;
2630 -- 4640517, models with autoselected components are entered after the line is booked
2631 -- The model goes to Config Hold but the autoselected components progress to Fulfill/Closed
2632 -- Through this fix, if the model is at Config Hold, stop the components at Awaiting Fulfillment
2633
2634 IF p_top_model_line_id is not NULL then
2635
2636 SELECT item_type_code,ato_line_id
2637 INTO l_item_type_code,l_ato_line_id
2638 FROM oe_order_lines
2639 WHERE line_id = p_line_id;
2640
2641 IF l_Debug_level > 0 THEN
2642 oe_Debug_pub.add('item type '||l_item_type_code||','||l_ato_line_id||','||l_fulfilled_flag||','||p_line_id||','||p_top_model_line_id,1);
2643 END IF;
2644 IF l_pto_kit_lines.line_id = l_ato_line_id THEN
2645 IF l_debug_level > 0 THEN
2646 oe_debug_pub.add('Check if Model in configuration validation hold (hold ID 3) ' , 1 ) ;
2647 END IF;
2648
2649 OE_HOLDS_PUB.CHECK_HOLDS
2650 ( p_api_version => 1.0,
2651 p_line_id => p_line_id,
2652 p_hold_id => 3,
2653 x_result_out => l_hold_result_out,
2654 x_return_status => l_return_status,
2655 x_msg_count => x_msg_count,
2656 x_msg_data => x_msg_data);
2657
2658 oe_debug_pub.add('after model check '||l_hold_result_out,1);
2659
2660 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2661 IF l_hold_result_out = FND_API.G_TRUE THEN
2662 IF l_debug_level > 0 THEN
2663 oe_debug_pub.add('Model on Hold - CAN NOT FULFILL MODEL YET!!' , 1 ) ;
2664 END IF;
2665 l_fulfilled_flag := FND_API.G_FALSE;
2666 GOTO END_PTO_KIT_LOOP;
2667 END IF;
2668 END IF;
2669 END IF;
2670 END IF;
2671
2672 /* If the line is at fulfill line work flow activity and the
2673 fulfilled flag is not set it will be a line without fulfillment
2674 activity. So for the purpose of Configuration fulfillment it
2675 will can be considered as fulfilled */
2676
2677 l_fulfilled_flag := FND_API.G_TRUE;
2678 IF l_debug_level > 0 THEN
2679 oe_debug_pub.add( 'LINE IS FULFILLED : '||TO_CHAR ( L_PTO_KIT_LINES.LINE_ID ) , 3 ) ;
2680 END IF;
2681 END IF;
2682 END IF;
2683
2684 END LOOP;
2685 << END_PTO_KIT_LOOP >>
2686
2687 IF l_fulfilled_flag = FND_API.G_TRUE AND p_part_of_fullfillment_set THEN
2688
2689 x_fulfillment_status := l_fulfilled_flag;
2690 x_return_status := FND_API.G_RET_STS_SUCCESS;
2691 IF l_debug_level > 0 THEN
2692 oe_debug_pub.add('line is part of fulfillment set',1);
2693 END IF;
2694 RETURN;
2695 END IF;
2696
2697 IF l_fulfilled_flag = FND_API.G_TRUE THEN
2698
2699 -- Prepare the table to update the fulfilled quantity and fulfilled
2700 -- flag for derived lines.
2701
2702 IF l_debug_level > 0 THEN
2703 oe_debug_pub.add('now all the related lines are fulfilled',1);
2704 oe_debug_pub.add('call Query_Options() '||to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'),5);
2705 END IF;
2706
2707 OE_Config_Util.Query_Options(p_top_model_line_id => p_top_model_line_id,
2708 x_line_tbl => l_line_tbl);
2709 IF l_debug_level > 0 THEN
2710 oe_debug_pub.add('after Query_Options() '||to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'),5);
2711 END IF;
2712
2713
2714 FOR l_line_index IN 1 .. l_line_tbl.count
2715 LOOP
2716
2717 IF l_debug_level > 0 THEN
2718 oe_debug_pub.add('index/line_id/fulfilled :'||l_line_index||'/'||l_line_tbl(l_line_index).line_id||'/'||l_line_tbl(l_line_index).fulfilled_flag,5);
2719 END IF;
2720 IF (l_line_tbl(l_line_index).line_id <> p_line_id OR
2721 p_process_all = FND_API.G_TRUE) AND
2722 nvl(l_line_tbl(l_line_index).fulfilled_flag,'N') <> 'Y' THEN
2723
2724 l_fulfill_index := l_fulfill_index + 1;
2725 l_fulfill_tbl(l_fulfill_index) := OE_Order_PUB.G_MISS_LINE_REC;
2726 l_fulfill_tbl(l_fulfill_index).line_id := l_line_tbl(l_line_index).line_id;
2727 l_fulfill_tbl(l_fulfill_index).fulfilled_flag := 'Y';
2728 l_fulfill_tbl(l_fulfill_index).fulfillment_date := SYSDATE;
2729 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity := nvl(l_line_tbl(l_line_index).shipped_quantity,l_line_tbl(l_line_index).ordered_quantity);
2730 -- Bug 3061559
2731 -- Copy fields used in blankets processing
2732 l_fulfill_tbl(l_fulfill_index).blanket_number := l_line_tbl(l_line_index).blanket_number;
2733 l_fulfill_tbl(l_fulfill_index).blanket_line_number := l_line_tbl(l_line_index).blanket_line_number;
2734 l_fulfill_tbl(l_fulfill_index).order_quantity_uom := l_line_tbl(l_line_index).order_quantity_uom;
2735 l_fulfill_tbl(l_fulfill_index).item_type_code := l_line_tbl(l_line_index).item_type_code;
2736 l_fulfill_tbl(l_fulfill_index).line_category_code := l_line_tbl(l_line_index).line_category_code;
2737 l_fulfill_tbl(l_fulfill_index).unit_selling_price := l_line_tbl(l_line_index).unit_selling_price;
2738 l_fulfill_tbl(l_fulfill_index).header_id := l_line_tbl(l_line_index).header_id;
2739 l_fulfill_tbl(l_fulfill_index).inventory_item_id := l_line_tbl(l_line_index).inventory_item_id;
2740 -- End bug 3061559
2741 -- changes for AFD
2742 l_fulfill_tbl(l_fulfill_index).actual_shipment_date := l_line_tbl(l_line_index).actual_shipment_date;
2743 l_fulfill_tbl(l_fulfill_index).order_firmed_date := l_line_tbl(l_line_index).order_firmed_date;
2744 -- end of changes for AFD
2745 -- sol_ord_er #16014165
2746 If oe_line_util.Is_Subscription_Service_Line(l_fulfill_tbl(l_fulfill_index).line_id)
2747 THEN
2748 l_fulfill_tbl(l_fulfill_index).subscription_enable_flag := 'Y';
2749 End If;
2750 -- sol_ord_er #16014165 end
2751
2752 l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
2753 IF l_debug_level > 0 THEN
2754 oe_debug_pub.add('index - '||l_fulfill_index,5);
2755 oe_debug_pub.add('fulfilled flag - '||l_fulfill_tbl(l_fulfill_index).fulfilled_flag,5);
2756 oe_debug_pub.add('quantity - '||l_fulfill_tbl(l_fulfill_index).fulfilled_quantity,5);
2757 END IF;
2758 END IF;
2759
2760 /* Get the service lines if there are any associated with the
2761 lines of the Model */
2762
2763 Get_Service_Lines
2764 (
2765 p_line_id => l_line_tbl(l_line_index).line_id,
2766 p_header_id => l_line_tbl(l_line_index).header_id, -- 1717444
2767 x_return_status => l_return_status,
2768 x_line_tbl => l_service_tbl
2769 );
2770
2771 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2773 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2774 GOTO END_FULFILL_LOOP;
2775 END IF;
2776
2777 /* Add service lines to l_fulfill_tbl for fulfillment */
2778
2779 FOR l_service_index IN 1 .. l_service_tbl.count
2780 LOOP
2781 l_fulfill_index := l_fulfill_index + 1;
2782 l_fulfill_tbl(l_fulfill_index) := OE_Order_PUB.G_MISS_LINE_REC;
2783 l_fulfill_tbl(l_fulfill_index).line_id := l_service_tbl(l_service_index).line_id;
2784 l_fulfill_tbl(l_fulfill_index).fulfilled_flag := 'Y';
2785 l_fulfill_tbl(l_fulfill_index).fulfillment_date := SYSDATE;
2786 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity := nvl(l_service_tbl(l_service_index).shipped_quantity,l_service_tbl(l_service_index).ordered_quantity);
2787 -- Bug 3061559
2788 -- Copy fields used in blankets processing
2789 l_fulfill_tbl(l_fulfill_index).blanket_number := l_service_tbl(l_service_index).blanket_number;
2790 l_fulfill_tbl(l_fulfill_index).blanket_line_number := l_service_tbl(l_service_index).blanket_line_number;
2791 l_fulfill_tbl(l_fulfill_index).order_quantity_uom := l_service_tbl(l_service_index).order_quantity_uom;
2792 l_fulfill_tbl(l_fulfill_index).item_type_code := l_service_tbl(l_service_index).item_type_code;
2793 l_fulfill_tbl(l_fulfill_index).line_category_code := l_service_tbl(l_service_index).line_category_code;
2794 l_fulfill_tbl(l_fulfill_index).unit_selling_price := l_service_tbl(l_service_index).unit_selling_price;
2795 l_fulfill_tbl(l_fulfill_index).header_id := l_service_tbl(l_service_index).header_id;
2796 l_fulfill_tbl(l_fulfill_index).inventory_item_id := l_service_tbl(l_service_index).inventory_item_id;
2797 -- End bug 3061559
2798 -- changes for AFD
2799 l_fulfill_tbl(l_fulfill_index).actual_shipment_date := l_service_tbl(l_service_index).actual_shipment_date;
2800 l_fulfill_tbl(l_fulfill_index).order_firmed_date := l_service_tbl(l_service_index).order_firmed_date;
2801 -- end changes for AFD
2802 -- sol_ord_er #16014165
2803 If oe_line_util.Is_Subscription_Service_Line(l_fulfill_tbl(l_fulfill_index).line_id)
2804 THEN
2805 l_fulfill_tbl(l_fulfill_index).subscription_enable_flag := 'Y';
2806 End If;
2807 -- sol_ord_er #16014165 end
2808 l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
2809 IF l_debug_level > 0 THEN
2810 oe_debug_pub.add('index - '||l_fulfill_index,5);
2811 oe_debug_pub.add('fulfilled flag - '||l_fulfill_tbl(l_fulfill_index).fulfilled_flag,5);
2812 oe_debug_pub.add('fulfilled quantity - '||l_fulfill_tbl(l_fulfill_index).fulfilled_quantity,5);
2813 END IF;
2814
2815
2816 /* Add to the fulfill service table for completing
2817 the FULFILL_LINE workflow activity */
2818
2819 l_fulfill_service_index := l_fulfill_service_index + 1;
2820 l_fulfill_service_tbl(l_fulfill_service_index).line_id := l_service_tbl(l_service_index).line_id;
2821 l_fulfill_service_tbl(l_fulfill_service_index).header_id := l_service_tbl(l_service_index).header_id;
2822
2823 IF l_debug_level > 0 THEN
2824 oe_debug_pub.add('service fulfill index - '||l_fulfill_service_index,5);
2825 oe_debug_pub.add('line id - '||l_fulfill_service_tbl(l_fulfill_service_index).line_id,5);
2826 END IF;
2827
2828 END LOOP;
2829
2830
2831 << END_FULFILL_LOOP >>
2832 NULL;
2833
2834 END LOOP;
2835
2836 IF l_debug_level > 0 THEN
2837 oe_debug_pub.add('..501..call Fulfill_Line() with full picture ',5);
2838 END IF;
2839
2840 Fulfill_Line
2841 (
2842 p_line_tbl => l_fulfill_tbl,
2843 p_mode => 'TABLE',
2844 p_fulfillment_type => G_FULFILL_WITH_ACTIVITY,
2845 p_fulfillment_activity => p_fulfillment_activity,
2846 x_return_status => l_return_status
2847 );
2848
2849 IF l_debug_level > 0 THEN
2850 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL LINE : '||L_RETURN_STATUS , 3 ) ;
2851 END IF;
2852
2853 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2855 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2856 RAISE FND_API.G_EXC_ERROR;
2857 END IF;
2858
2859
2860 -- Complete the FULFILL_LINE activity for the fulfilled lines.
2861
2862 FOR l_line_index IN 1 .. l_line_tbl.count
2863 LOOP
2864 IF l_line_tbl(l_line_index).line_id <> p_line_id OR
2865 p_process_all = FND_API.G_TRUE THEN
2866 l_line_id := l_line_tbl(l_line_index).line_id;
2867
2868 IF l_debug_level > 0 THEN
2869 oe_debug_pub.add('update the flow status code on the lines ',5);
2870 END IF;
2871
2872 OE_Order_WF_Util.Update_Flow_Status_Code
2873 (p_line_id => l_line_tbl(l_line_index).line_id,
2874 p_flow_status_code => 'FULFILLED',
2875 x_return_status => l_return_status
2876 );
2877
2878 IF l_debug_level > 0 THEN
2879 oe_debug_pub.add('after update of flow status '||l_return_status,5);
2880 END IF;
2881
2882 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2884 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2885 RAISE FND_API.G_EXC_ERROR;
2886 END IF;
2887
2888 IF l_debug_level > 0 THEN
2889 oe_debug_pub.add('** complete the WF activity on this '||l_line_id,1);
2890 END IF;
2891 BEGIN
2892 wf_engine.CompleteActivityInternalName('OEOL', to_char(l_line_id), 'FULFILL_LINE', '#NULL',TRUE ); --15870313
2893 EXCEPTION
2894 WHEN OTHERS THEN
2895 oe_debug_pub.add('Workflow complete activity failed. Raising');
2896 RAISE ;
2897 END ;
2898 IF l_debug_level > 0 THEN
2899 oe_debug_pub.add('after wf complete ',5);
2900 END IF;
2901
2902 END IF;
2903 END LOOP;
2904
2905 IF l_debug_level > 0 THEN
2906 oe_debug_pub.add('*** process the service lines now ***',1);
2907 END IF;
2908
2909 IF l_fulfill_service_index <> 0 THEN
2910
2911
2912 FOR l_fulfill_service_index IN 1 .. l_fulfill_service_tbl.count
2913 LOOP
2914 l_line_id := l_fulfill_service_tbl(l_fulfill_service_index).line_id;
2915
2916 IF l_debug_level > 0 THEN
2917 oe_debug_pub.add('update flow status for - '||l_line_id,5);
2918 END IF;
2919
2920 OE_Order_WF_Util.Update_Flow_Status_Code
2921 (p_line_id => l_fulfill_service_tbl(l_fulfill_service_index).line_id,
2922 p_flow_status_code => 'FULFILLED',
2923 x_return_status => l_return_status
2924 );
2925
2926 IF l_debug_level > 0 THEN
2927 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
2928 END IF;
2929
2930 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2932 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2933 RAISE FND_API.G_EXC_ERROR;
2934 END IF;
2935
2936 IF l_debug_level > 0 THEN
2937 oe_debug_pub.add('call complete WF for this service line - '||l_line_id,5);
2938 END IF;
2939 BEGIN
2940 wf_engine.CompleteActivityInternalName('OEOL', to_char(l_line_id), 'FULFILL_LINE', '#NULL',TRUE ); --15870313
2941 EXCEPTION
2942 WHEN OTHERS THEN
2943 oe_debug_pub.add('Workflow complete activity failed. Raising');
2944 RAISE ;
2945 END ;
2946 IF l_debug_level > 0 THEN
2947 oe_debug_pub.add('after WF complete ',5);
2948 END IF;
2949
2950 END LOOP;
2951
2952 END IF;
2953
2954 END IF;
2955
2956 x_fulfillment_status := l_fulfilled_flag;
2957 x_return_status := FND_API.G_RET_STS_SUCCESS;
2958
2959 IF l_debug_level > 0 THEN
2960 oe_debug_pub.add('** complete OE_Line_Fullfill.Process_PTO_Kit() ',1);
2961 END IF;
2962
2963 EXCEPTION
2964 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2965 IF l_debug_level > 0 THEN
2966 oe_debug_pub.add('*** 1. Error - '||SUBSTR(SQLERRM,1,200),1);
2967 END IF;
2968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2969
2970 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2971 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Process_PTO_KIT');
2972 END IF;
2973
2974 WHEN FND_API.G_EXC_ERROR THEN
2975 IF l_debug_level > 0 THEN
2976 oe_debug_pub.add('*** 2. Error - '||SUBSTR(SQLERRM,1,200),1);
2977 END IF;
2978 x_return_status := FND_API.G_RET_STS_ERROR;
2979
2980 WHEN OTHERS THEN
2981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2982 IF l_debug_level > 0 THEN
2983 oe_debug_pub.add('*** 3. Error - '||SUBSTR(SQLERRM,1,200),1);
2984 END IF;
2985
2986 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2987 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Process_PTO_KIT');
2988 END IF;
2989
2990 END Process_PTO_KIT;
2991
2992 /*
2993 This procedure is for processing of fulfillment prcessing of a line which
2994 is part of fulfillment set(s). This procedure returns TRUE if all the
2995 lines of all the fulfillment set(s) to which this line is linked to are
2996 fulfilled, else will return FALSE. If a line's fulfilled flag is not set
2997 to 'Y' and the line is waiting at FULFILL_LINE work flow activity the line
2998 will be considered to be fulfilled if all the other lines of the
2999 fulfillment set are fulfilled.
3000 */
3001 PROCEDURE Process_Fulfillment_Set
3002 (
3003 p_line_id IN NUMBER
3004 , p_fulfillment_activity IN VARCHAR2
3005 , p_line_set_tbl IN Line_Set_Tbl_Type
3006 , x_return_status OUT NOCOPY VARCHAR2
3007
3008 , x_fulfillment_status OUT NOCOPY VARCHAR2
3009
3010 )
3011 IS
3012 l_line_set_index NUMBER := 0;
3013 l_prev_set_id NUMBER := 0;
3014 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
3015 l_fulfilled_flag VARCHAR2(1) := FND_API.G_TRUE;
3016 l_item_key VARCHAR2(240);
3017 l_fulfill_activity VARCHAR2(30) := 'FULFILL_LINE';
3018 l_activity_status VARCHAR2(8);
3019 l_activity_result VARCHAR2(30);
3020 l_activity_id NUMBER;
3021 l_return_status VARCHAR2(1);
3022 TYPE line_tbl IS TABLE OF NUMBER
3023 INDEX BY BINARY_INTEGER;
3024 l_line_tbl line_tbl;
3025 l_line_tbl_index NUMBER;
3026 TYPE set_tbl IS TABLE OF NUMBER
3027 INDEX BY BINARY_INTEGER;
3028 l_set_tbl set_tbl;
3029 l_set_tbl_index NUMBER := 0;
3030 l_fulfill_tbl OE_Order_Pub.Line_Tbl_Type;
3031 l_fulfill_index NUMBER := 0 ;
3032 l_process_tbl Line_Set_Tbl_Type;
3033 l_process_index NUMBER := 0;
3034 l_fulfillment_status VARCHAR2(1);
3035 l_process_all VARCHAR2(1) := FND_API.G_TRUE;
3036 l_top_model_line_id NUMBER;
3037 l_header_id NUMBER;
3038 l_service_tbl OE_Order_Pub.Line_Tbl_Type;
3039 l_service_index NUMBER;
3040 --Start 7827727
3041 l_line_id_mod NUMBER;
3042 l_set_id_mod NUMBER;
3043 --End 7827727
3044 -- Bug 3061559
3045 -- Select fields used in blankets processing
3046 CURSOR c_line(l_cursor_line_id IN NUMBER) IS
3047 SELECT HEADER_ID,
3048 BLANKET_NUMBER,
3049 BLANKET_LINE_NUMBER,
3050 ORDER_QUANTITY_UOM,
3051 ITEM_TYPE_CODE,
3052 LINE_CATEGORY_CODE,
3053 UNIT_SELLING_PRICE,
3054 INVENTORY_ITEM_ID,
3055 -- changes for AFD
3056 ACTUAL_SHIPMENT_DATE,
3057 ORDER_FIRMED_DATE
3058 FROM OE_ORDER_LINES_ALL
3059 WHERE LINE_ID = l_cursor_line_id;
3060
3061 --
3062 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3063 --
3064 BEGIN
3065 IF l_debug_level > 0 THEN
3066 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.PROCESS_FULFILLMENT_SET '|| TO_CHAR ( P_LINE_ID ) , 1 ) ;
3067 END IF;
3068
3069 /* Prepare a table of line and set id's to be processed. This new table
3070 will have indication whether it is a standard line or a configuration. */
3071
3072
3073 FOR l_line_set_index IN p_line_set_tbl.FIRST .. p_line_set_tbl.LAST
3074 LOOP
3075
3076 -- l_line_rec := OE_Line_Util.Query_Row(p_line_set_tbl(l_line_set_index).line_id);
3077 OE_Line_Util.Query_Row(p_line_id => p_line_set_tbl(l_line_set_index).line_id,
3078 x_line_rec => l_line_rec);
3079 l_set_id_mod :=
3080 MOD(p_line_set_tbl(l_line_set_index).set_id,G_BINARY_LIMIT);--7827727
3081
3082 --IF NOT l_set_tbl.EXISTS(p_line_set_tbl(l_line_set_index).set_id) THEN
3083 IF NOT l_set_tbl.EXISTS(l_set_id_mod) THEN --7827727
3084 IF l_debug_level > 0 THEN
3085 oe_debug_pub.add( 'SET ID ADDED : '||TO_CHAR ( P_LINE_SET_TBL ( L_LINE_SET_INDEX ) .SET_ID ) , 3 ) ;
3086 END IF;
3087 --l_set_tbl(p_line_set_tbl(l_line_set_index).set_id) := p_line_set_tbl(l_line_set_index).set_id;
3088 l_set_tbl(l_set_id_mod) := p_line_set_tbl(l_line_set_index).set_id; --7827727
3089
3090 END IF;
3091
3092 IF l_line_rec.top_model_line_id IS NOT NULL AND
3093 l_line_rec.top_model_line_id <> FND_API.G_MISS_NUM THEN
3094 IF l_debug_level > 0 THEN
3095 oe_debug_pub.add( 'LINE IS PART OF A CONFIGURATION : '||TO_CHAR ( L_LINE_REC.LINE_ID ) ||'/'||L_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
3096 END IF;
3097
3098 l_process_index := l_process_index + 1;
3099 l_process_tbl(l_process_index) := p_line_set_tbl(l_line_set_index);
3100 l_process_tbl(l_process_index).fulfilled_flag := l_line_rec.fulfilled_flag;
3101 l_process_tbl(l_process_index).ordered_quantity := l_line_rec.ordered_quantity;
3102
3103 IF l_line_rec.top_model_line_id = l_line_rec.line_id THEN
3104 l_process_tbl(l_process_index).type := 'C';
3105 IF l_debug_level > 0 THEN
3106 oe_debug_pub.add( 'LINE HAS BEEN ADDED AS CONFIGURATION LINE : '||TO_CHAR ( L_LINE_REC.LINE_ID ) ||'/'||L_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
3107 END IF;
3108 ELSE
3109 --bug12869907
3110 IF (NVL(L_LINE_REC.model_remnant_flag ,'N') = 'N' ) Then
3111 l_process_tbl(l_process_index).type := 'O';
3112 IF l_debug_level > 0 THEN
3113 oe_debug_pub.add( 'LINE HAS BEEN ADDED AS OPTION LINE : '||TO_CHAR ( L_LINE_REC.LINE_ID ) ||'/'||L_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
3114 END IF;
3115 ELSE
3116 l_process_tbl(l_process_index).type := 'R';
3117 IF l_debug_level > 0 THEN
3118 oe_debug_pub.add( 'LINE HAS BEEN ADDED AS REMNANT OPTION LINE : '||TO_CHAR ( L_LINE_REC.LINE_ID ) ||'/'||L_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
3119 END IF;
3120 END IF; --end bug12869907
3121
3122 END IF;
3123 ELSE
3124 l_process_index := l_process_index + 1;
3125 l_process_tbl(l_process_index) := p_line_set_tbl(l_line_set_index);
3126 l_process_tbl(l_process_index).type := 'S';
3127 l_process_tbl(l_process_index).fulfilled_flag := l_line_rec.fulfilled_flag;
3128 l_process_tbl(l_process_index).ordered_quantity := l_line_rec.ordered_quantity;
3129 IF l_debug_level > 0 THEN
3130 oe_debug_pub.add( 'LINE HAS BEEN ADDED AS STANDARD LINE : '||TO_CHAR ( L_LINE_REC.LINE_ID ) ||'/'||L_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
3131 END IF;
3132
3133 END IF;
3134 END LOOP;
3135
3136
3137
3138 FOR l_line_set_index IN l_process_tbl.FIRST .. l_process_tbl.LAST
3139 LOOP
3140 IF l_debug_level > 0 THEN
3141 oe_debug_pub.add( 'LINE ID/SET ID : '|| TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) ||'/'||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .SET_ID ) , 3 ) ;
3142 END IF;
3143
3144 IF l_debug_level > 0 THEN
3145 oe_debug_pub.add( 'LINE TYPE : '||L_PROCESS_TBL ( L_LINE_SET_INDEX ) .TYPE , 3 ) ;
3146 END IF;
3147 IF l_process_tbl(l_line_set_index).type = 'C' THEN
3148
3149 IF l_debug_level > 0 THEN
3150 oe_debug_pub.add( 'IT IS A CONFIGURATION TOP MODEL ' , 3 ) ;
3151 END IF;
3152
3153 -- l_line_rec := OE_Line_Util.Query_Row(p_line_id);
3154
3155 SELECT TOP_MODEL_LINE_ID
3156 INTO l_top_model_line_id
3157 FROM OE_ORDER_LINES
3158 WHERE LINE_ID = p_line_id;
3159
3160
3161 IF l_top_model_line_id = nvl(l_process_tbl(l_line_set_index).line_id,0) THEN
3162 IF l_debug_level > 0 THEN
3163 oe_debug_pub.add( 'DO NOT PROCESS ALL THE LINES ' , 3 ) ;
3164 END IF;
3165 l_process_all := FND_API.G_FALSE;
3166 ELSE
3167 IF l_debug_level > 0 THEN
3168 oe_debug_pub.add( 'PROCESS ALL THE LINES ' , 3 ) ;
3169 END IF;
3170 l_process_all := FND_API.G_TRUE;
3171 END IF;
3172
3173
3174 IF l_debug_level > 0 THEN
3175 oe_debug_pub.add( 'CALLING PROCESS PTO FULFILLMENT ' , 3 ) ;
3176 END IF;
3177 Process_PTO_KIT
3178 (
3179 p_line_id => p_line_id,
3180 p_top_model_line_id => l_process_tbl(l_line_set_index).line_id,
3181 p_fulfillment_activity => p_fulfillment_activity,
3182 p_process_all => l_process_all,
3183 p_part_of_fullfillment_set => TRUE,
3184 x_return_status => l_return_status,
3185 x_fulfillment_status => l_fulfillment_status
3186 );
3187
3188 IF l_debug_level > 0 THEN
3189 oe_debug_pub.add( 'RETURN STATUS/FULFILLMENT STATUS : '||L_RETURN_STATUS||'/'||L_FULFILLMENT_STATUS , 3 ) ;
3190 END IF;
3191
3192 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3194 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3195 RAISE FND_API.G_EXC_ERROR;
3196 END IF;
3197
3198
3199 IF l_fulfillment_status = FND_API.G_TRUE THEN
3200 l_fulfilled_flag := FND_API.G_TRUE;
3201 IF l_debug_level > 0 THEN
3202 oe_debug_pub.add( 'CONFIGURATION IS FULFILLED : '||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) , 3 ) ;
3203 END IF;
3204 ELSE
3205 l_fulfilled_flag := FND_API.G_FALSE;
3206 x_fulfillment_status := FND_API.G_FALSE;
3207 IF l_debug_level > 0 THEN
3208 oe_debug_pub.add( 'CONFIGURATION IS NOT FULFILLED : '||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) , 3 ) ;
3209 END IF;
3210 GOTO END_CHECK_FULFILLMENT;
3211 END IF;
3212 END IF;
3213
3214 IF p_line_id <> l_process_tbl(l_line_set_index).line_id AND
3215 -- l_process_tbl(l_line_set_index).type = 'S' THEN --commented for bug12869907
3216 l_process_tbl(l_line_set_index).type IN ('R', 'S') THEN
3217
3218 IF l_process_tbl(l_line_set_index).fulfilled_flag = 'Y' THEN
3219 l_fulfilled_flag := FND_API.G_TRUE;
3220 IF l_debug_level > 0 THEN
3221 oe_debug_pub.add( 'LINE IS FULFILLED : '||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) , 3 ) ;
3222 END IF;
3223 ELSE
3224
3225 l_item_key := to_char(l_process_tbl(l_line_set_index).line_id);
3226
3227 IF l_debug_level > 0 THEN
3228 oe_debug_pub.add( 'CALLING GET ACTIVITY RESULT FOR ITEM : '||L_ITEM_KEY||'/'||L_FULFILL_ACTIVITY , 3 ) ;
3229 END IF;
3230
3231 Get_Activity_Result
3232 (
3233 p_item_type => OE_GLOBALS.G_WFI_LIN
3234 , p_item_key => l_item_key
3235 , p_activity_name => l_fulfill_activity
3236 , x_return_status => l_return_status
3237 , x_activity_result => l_activity_result
3238 , x_activity_status_code => l_activity_status
3239 , x_activity_id => l_activity_id
3240 );
3241
3242 IF l_debug_level > 0 THEN
3243 oe_debug_pub.add( 'RETURN STATUS FROM GET ACTIVITY RESULT : '||L_RETURN_STATUS||L_ACTIVITY_STATUS , 3 ) ;
3244 END IF;
3245
3246 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3248 ELSIF l_return_status = FND_API.G_RET_STS_ERROR OR
3249 l_activity_status <> 'NOTIFIED' THEN
3250 l_fulfilled_flag := FND_API.G_FALSE;
3251 x_fulfillment_status := FND_API.G_FALSE;
3252 IF l_debug_level > 0 THEN
3253 oe_debug_pub.add( 'LINE IS NOT FULFILLED : '||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) , 3 ) ;
3254 END IF;
3255 GOTO END_CHECK_FULFILLMENT;
3256 END IF;
3257
3258 /* If the line is at fulfill line work flow activity and the
3259 fulfilled flag is not set it will be a line without fulfillment
3260 activity. So for the purpose of fulfillment set fulfillment it will
3261 can be considered as fulfilled */
3262
3263 l_fulfilled_flag := FND_API.G_TRUE;
3264
3265 END IF;
3266
3267 END IF;
3268
3269 END LOOP;
3270 << END_CHECK_FULFILLMENT >>
3271
3272
3273 /* If the fulfilled flag after the above loop is TRUE, complete the
3274 fulfill line work flow for all the lines except the current line */
3275
3276 IF l_fulfilled_flag = FND_API.G_TRUE THEN
3277
3278 /* Prepare a table of lines which have not been fulfilled to update
3279 the fulfillment related attributes and comlpete the fulfill line */
3280
3281 FOR l_line_set_index IN 1 .. l_process_tbl.count
3282 LOOP
3283 IF l_debug_level > 0 THEN
3284 oe_debug_pub.add( 'LINE ID/SET ID : '|| TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) ||'/'||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .SET_ID ) , 3 ) ;
3285 END IF;
3286
3287 /* Get the associated service lines */
3288
3289 --IF NOT l_line_tbl.EXISTS(l_process_tbl(l_line_set_index).line_id) THEN
3290 IF NOT
3291 l_line_tbl.EXISTS(MOD(l_process_tbl(l_line_set_index).line_id,G_BINARY_LIMIT))
3292 THEN --7827727
3293
3294 Get_Service_Lines
3295 (
3296 p_line_id => l_process_tbl(l_line_set_index).line_id,
3297 x_return_status => l_return_status,
3298 x_line_tbl => l_service_tbl
3299 );
3300
3301 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3303 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3304 GOTO END_SERVICE_LINE;
3305 END IF;
3306
3307 /* Add service lines to l_fulfill_tbl for fulfillment */
3308
3309 FOR l_service_index IN 1 .. l_service_tbl.count
3310 LOOP
3311 l_line_id_mod:=MOD(l_service_tbl(l_service_index).line_id,G_BINARY_LIMIT);--7827727
3312 --IF NOT l_line_tbl.EXISTS(l_service_tbl(l_service_index).line_id) THEN
3313 IF NOT l_line_tbl.EXISTS(l_line_id_mod) THEN --7827727
3314 l_fulfill_index := l_fulfill_index + 1;
3315 l_fulfill_tbl(l_fulfill_index) := OE_Order_PUB.G_MISS_LINE_REC;
3316 l_fulfill_tbl(l_fulfill_index).line_id := l_service_tbl(l_service_index).line_id;
3317 l_fulfill_tbl(l_fulfill_index).fulfilled_flag := 'Y';
3318 l_fulfill_tbl(l_fulfill_index).fulfillment_date := SYSDATE;
3319 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity := nvl(l_service_tbl(l_service_index).shipped_quantity,l_service_tbl(l_service_index).ordered_quantity);
3320 -- Bug 3061559
3321 -- Copy fields used in blankets processing
3322 l_fulfill_tbl(l_fulfill_index).blanket_number := l_service_tbl(l_service_index).blanket_number;
3323 l_fulfill_tbl(l_fulfill_index).blanket_line_number := l_service_tbl(l_service_index).blanket_line_number;
3324 l_fulfill_tbl(l_fulfill_index).order_quantity_uom := l_service_tbl(l_service_index).order_quantity_uom;
3325 l_fulfill_tbl(l_fulfill_index).item_type_code := l_service_tbl(l_service_index).item_type_code;
3326 l_fulfill_tbl(l_fulfill_index).line_category_code := l_service_tbl(l_service_index).line_category_code;
3327 l_fulfill_tbl(l_fulfill_index).unit_selling_price := l_service_tbl(l_service_index).unit_selling_price;
3328 l_fulfill_tbl(l_fulfill_index).header_id := l_service_tbl(l_service_index).header_id;
3329 l_fulfill_tbl(l_fulfill_index).inventory_item_id := l_service_tbl(l_service_index).inventory_item_id;
3330 -- End bug 3061559
3331 -- changes for AFD
3332 l_fulfill_tbl(l_fulfill_index).actual_shipment_date := l_service_tbl(l_service_index).actual_shipment_date;
3333 l_fulfill_tbl(l_fulfill_index).order_firmed_date := l_service_tbl(l_service_index).order_firmed_date;
3334 -- end changes for AFD
3335
3336 l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
3337 IF l_debug_level > 0 THEN
3338 oe_debug_pub.add( 'FULFILL INDEX : '||TO_CHAR ( L_FULFILL_INDEX ) , 3 ) ;
3339 oe_debug_pub.add( 'FULFILLED FLAG : '||L_FULFILL_TBL ( L_FULFILL_INDEX ) .FULFILLED_FLAG , 3 ) ;
3340 oe_debug_pub.add( 'FULFILLED QUANTITY : '||TO_CHAR ( L_FULFILL_TBL ( L_FULFILL_INDEX ) .FULFILLED_QUANTITY ) , 3 ) ;
3341 END IF;
3342
3343
3344 --l_line_tbl(l_service_tbl(l_service_index).line_id) := l_service_tbl(l_service_index).line_id;
3345 l_line_tbl(l_line_id_mod) :=l_service_tbl(l_service_index).line_id;--7827727
3346 IF l_debug_level > 0 THEN
3347 oe_debug_pub.add( 'SERVICE LINE IS ADDED TO TABLE '||TO_CHAR ( L_SERVICE_TBL ( L_SERVICE_INDEX ) .LINE_ID ) , 3 ) ;
3348 END IF;
3349
3350 END IF;
3351
3352 END LOOP;
3353
3354 END IF;
3355
3356 << END_SERVICE_LINE >>
3357
3358 IF l_debug_level > 0 THEN
3359 oe_debug_pub.add( 'LINE ID/SET ID AFTER SERVICE : '|| TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) ||'/'||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .SET_ID ) , 3 ) ;
3360 END IF;
3361 l_line_id_mod:=MOD(l_process_tbl(l_line_set_index).line_id,G_BINARY_LIMIT);--7827727
3362 IF p_line_id <> l_process_tbl(l_line_set_index).line_id AND
3363 NOT l_line_tbl.EXISTS(l_line_id_mod) THEN --7827727
3364 --NOT l_line_tbl.EXISTS(l_process_tbl(l_line_set_index).line_id) THEN
3365 --l_line_tbl(l_process_tbl(l_line_set_index).line_id) := l_process_tbl(l_line_set_index).line_id;
3366 l_line_tbl(l_line_id_mod) :=l_process_tbl(l_line_set_index).line_id; --7827727
3367 IF l_debug_level > 0 THEN
3368 oe_debug_pub.add( 'LINE IS ADDED TO TABLE '||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) , 3 ) ;
3369 END IF;
3370
3371 /* If the line is not fulfilled earlier add this to fulfill
3372 table for updating the fulfillment related attributes on the
3373 line */
3374
3375 IF nvl(l_process_tbl(l_line_set_index).fulfilled_flag,'N') <> 'Y' THEN
3376
3377 IF l_debug_level > 0 THEN
3378 oe_debug_pub.add( 'THE LINE IS NOT FULFILLED '||TO_CHAR ( L_PROCESS_TBL ( L_LINE_SET_INDEX ) .LINE_ID ) , 3 ) ;
3379 END IF;
3380
3381 l_fulfill_index := l_fulfill_index + 1;
3382 l_fulfill_tbl(l_fulfill_index):= OE_Order_PUB.G_MISS_LINE_REC;
3383 l_fulfill_tbl(l_fulfill_index).line_id := l_process_tbl(l_line_set_index).line_id;
3384 l_fulfill_tbl(l_fulfill_index).fulfilled_flag := 'Y';
3385 l_fulfill_tbl(l_fulfill_index).fulfillment_date := SYSDATE;
3386 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity := nvl(l_process_tbl(l_line_set_index).ordered_quantity,l_process_tbl(l_line_set_index).ordered_quantity);
3387 -- Bug 3061559
3388 -- Copy fields used in blankets processing
3389 OPEN c_line(l_fulfill_tbl(l_fulfill_index).line_id);
3390 FETCH c_line INTO
3391 l_fulfill_tbl(l_fulfill_index).header_id,
3392 l_fulfill_tbl(l_fulfill_index).blanket_number,
3393 l_fulfill_tbl(l_fulfill_index).blanket_line_number,
3394 l_fulfill_tbl(l_fulfill_index).order_quantity_uom,
3395 l_fulfill_tbl(l_fulfill_index).item_type_code,
3396 l_fulfill_tbl(l_fulfill_index).line_category_code,
3397 l_fulfill_tbl(l_fulfill_index).unit_selling_price,
3398 l_fulfill_tbl(l_fulfill_index).inventory_item_id,
3399 -- Changes for AFD
3400 l_fulfill_tbl(l_fulfill_index).actual_shipment_date,
3401 l_fulfill_tbl(l_fulfill_index).order_firmed_date;
3402 -- end Changes for AFD
3403
3404 CLOSE c_line;
3405 -- End bug 3061559
3406 l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
3407 IF l_debug_level > 0 THEN
3408 oe_debug_pub.add( 'FULFILL INDEX : '||TO_CHAR ( L_FULFILL_INDEX ) , 3 ) ;
3409 oe_debug_pub.add( 'FULFILLED FLAG : '||L_FULFILL_TBL ( L_FULFILL_INDEX ) .FULFILLED_FLAG , 3 ) ;
3410 oe_debug_pub.add( 'FULFILLED QUANTITY : '||TO_CHAR ( L_FULFILL_TBL ( L_FULFILL_INDEX ) .FULFILLED_QUANTITY ) , 3 ) ;
3411 END IF;
3412 ELSE
3413
3414 IF l_debug_level > 0 THEN
3415 oe_debug_pub.add( 'LINE IS FULFILLED ' , 3 ) ;
3416 END IF;
3417
3418 END IF;
3419 END IF;
3420 END LOOP;
3421
3422 IF l_fulfill_index <> 0 THEN
3423
3424 IF l_debug_level > 0 THEN
3425 oe_debug_pub.add( '..502..CALLING FULFILL LINE TABLE : ' , 3 ) ;
3426 END IF;
3427
3428
3429 Fulfill_Line
3430 (
3431 p_line_tbl => l_fulfill_tbl,
3432 p_mode => 'TABLE',
3433 p_fulfillment_type => G_FULFILL_WITH_ACTIVITY,
3434 p_fulfillment_activity => p_fulfillment_activity,
3435 x_return_status => l_return_status
3436 );
3437
3438 IF l_debug_level > 0 THEN
3439 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL LINE : '||L_RETURN_STATUS , 3 ) ;
3440 END IF;
3441
3442 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3444 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3445 RAISE FND_API.G_EXC_ERROR;
3446 END IF;
3447
3448 END IF;
3449
3450 l_line_tbl_index := l_line_tbl.FIRST;
3451
3452 WHILE l_line_tbl_index IS NOT NULL
3453 LOOP
3454 IF l_debug_level > 0 THEN
3455 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
3456 END IF;
3457
3458 -- l_line_rec := OE_Line_Util.Query_Row(l_line_tbl(l_line_tbl_index));
3459
3460 /*
3461 SELECT HEADER_ID
3462 INTO l_header_id
3463 FROM OE_ORDER_LINES
3464 WHERE LINE_ID = l_line_tbl(l_line_tbl_index);
3465 */
3466 OE_Order_WF_Util.Update_Flow_Status_Code
3467 (p_line_id => l_line_tbl(l_line_tbl_index),
3468 p_flow_status_code => 'FULFILLED',
3469 x_return_status => l_return_status
3470 );
3471
3472 IF l_debug_level > 0 THEN
3473 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
3474 END IF;
3475
3476 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3478 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3479 RAISE FND_API.G_EXC_ERROR;
3480 END IF;
3481
3482 IF l_debug_level > 0 THEN
3483 oe_debug_pub.add( 'CALLING WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( L_LINE_TBL ( L_LINE_TBL_INDEX ) ) , 3 ) ;
3484 END IF;
3485 BEGIN
3486 wf_engine.CompleteActivityInternalName('OEOL', to_char(l_line_tbl(l_line_tbl_index)), 'FULFILL_LINE', '#NULL',TRUE ); --15870313
3487 EXCEPTION
3488 WHEN OTHERS THEN
3489 oe_debug_pub.add('Workflow complete activity failed. Raising');
3490 RAISE ;
3491 END ;
3492 IF l_debug_level > 0 THEN
3493 oe_debug_pub.add( 'RETURNED FROM WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( L_LINE_TBL ( L_LINE_TBL_INDEX ) ) , 3 ) ;
3494 END IF;
3495
3496 l_line_tbl_index := l_line_tbl.NEXT(l_line_tbl_index);
3497 END LOOP;
3498
3499 -- Update the set status in oe_sets to closed.
3500 l_set_tbl_index := l_set_tbl.FIRST;
3501
3502 WHILE l_set_tbl_index IS NOT NULL
3503 LOOP
3504 -- 3772947
3505 UPDATE OE_SETS
3506 SET SET_STATUS = 'C',
3507 UPDATE_DATE = SYSDATE
3508 WHERE SET_ID = l_set_tbl(l_set_tbl_index);
3509
3510 IF l_debug_level > 0 THEN
3511 oe_debug_pub.add( 'SET IS CLOSED : '||TO_CHAR ( L_SET_TBL ( L_SET_TBL_INDEX ) ) , 3 ) ;
3512 END IF;
3513 l_set_tbl_index := l_set_tbl.NEXT(l_set_tbl_index);
3514
3515 OE_Set_util.g_set_rec.set_status := 'C'; -- 4080531
3516
3517 END LOOP;
3518
3519 END IF;
3520
3521 x_return_status := FND_API.G_RET_STS_SUCCESS;
3522 x_fulfillment_status := l_fulfilled_flag;
3523 IF l_debug_level > 0 THEN
3524 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.PROCESS_FULFILLMENT_SET '||X_RETURN_STATUS||'/'||X_FULFILLMENT_STATUS , 1 ) ;
3525 END IF;
3526
3527
3528 EXCEPTION
3529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3531
3532 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3533 THEN
3534 OE_MSG_PUB.Add_Exc_Msg
3535 ( G_PKG_NAME,
3536 'Process_Fulfillment_Set'
3537 );
3538 END IF;
3539 G_DEBUG_MSG := G_DEBUG_MSG || 'E20,';
3540
3541 WHEN FND_API.G_EXC_ERROR THEN
3542 x_return_status := FND_API.G_RET_STS_ERROR;
3543 G_DEBUG_MSG := G_DEBUG_MSG || 'E20-1,';
3544 WHEN OTHERS THEN
3545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3546 IF l_debug_level > 0 THEN
3547 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
3548 END IF;
3549
3550 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3551 THEN
3552 OE_MSG_PUB.Add_Exc_Msg
3553 ( G_PKG_NAME,
3554 'Process_Fulfillment_Set'
3555 );
3556 END IF;
3557 G_DEBUG_MSG := G_DEBUG_MSG || 'E21,';
3558
3559 END Process_Fulfillment_Set;
3560 /*
3561 This procedure is called when a line reaches FULFILL_LINE work flow
3562 activity. It gets the fulfillment activity for the line if the line
3563 has a fulfillment activity it updates the fulfilled quantity, flag and
3564 fulfillment date for the line. If the line is part of a MODEL the
3565 fulfillment processing for PTO/KIT will take place, if the line is part
3566 of fulfillment set(s) fulfillment processing for a fulfillment set will
3567 take place. If the line is part of a remnant MODEL the FULILL_LINE work
3568 flow activity will be completed for the line. If the line is not part of
3569 either MODEL or fulfillment set, the FULFILL_LINE work flow activity will
3570 be set to COMPLETE. If the line does not have a fulfillment activity and
3571 is not part of MODEL or fulfillment set, the fulfilled quantity will be
3572 updated from the ordered quantity of the line and FULFILL_LINE activity
3573 will be completed.
3574 */
3575
3576 PROCEDURE Process_Fulfillment
3577 (
3578 p_api_version_number IN NUMBER
3579 , p_line_id IN NUMBER
3580 , p_activity_id IN NUMBER
3581 , x_result_out OUT NOCOPY VARCHAR2
3582
3583 , x_return_status OUT NOCOPY VARCHAR2
3584
3585 , x_msg_count OUT NOCOPY VARCHAR2
3586
3587 , x_msg_data OUT NOCOPY VARCHAR2
3588
3589 )
3590 IS
3591
3592 l_line_rec OE_Order_Pub.Line_Rec_Type;
3593 l_return_status VARCHAR2(1);
3594 l_item_key VARCHAR2(240);
3595 l_fulfillment_activity VARCHAR2(30);
3596 l_fulfillment_type VARCHAR2(30);
3597 l_set_tbl Line_Set_Tbl_Type;
3598 l_fulfillment_status VARCHAR2(1);
3599 l_flow_status_code VARCHAR2(30);
3600 l_activity_status VARCHAR2(8);
3601 l_activity_result VARCHAR2(30);
3602 l_activity_id NUMBER;
3603 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
3604 l_line_index NUMBER;
3605 l_config_index NUMBER := 0;
3606 l_fulfill_tbl OE_Order_Pub.Line_Tbl_Type;
3607 l_fulfill_index NUMBER := 0 ;
3608 l_service_tbl OE_Order_Pub.Line_Tbl_Type;
3609 l_service_index NUMBER := 0 ;
3610 l_line_id NUMBER;
3611 l_fulfilled_flag VARCHAR2(1);
3612 l_top_model_line_id NUMBER;
3613 l_set_id NUMBER;
3614 l_header_id NUMBER;
3615 l_ref_header_id NUMBER; -- 1717444
3616 lock_set_id NUMBER;
3617 CAN_NOT_LOCK_MODEL EXCEPTION;
3618 l_dummy VARCHAR2(1); --10298607
3619 --
3620 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3621 --
3622 BEGIN
3623
3624 IF l_debug_level > 0 THEN
3625 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.PROCESS_FULFILLMENT '|| TO_CHAR ( P_LINE_ID ) , 1 ) ;
3626 END IF;
3627
3628
3629
3630 -- l_line_rec := OE_Line_Util.Query_Row(p_line_id);
3631 OE_Line_Util.Query_Row(p_line_id => p_line_id,
3632 x_line_rec => l_line_rec);
3633
3634 OE_MSG_PUB.set_msg_context(
3635 p_entity_code => 'LINE'
3636 ,p_entity_id => l_line_rec.line_id
3637 ,p_header_id => l_line_rec.header_id
3638 ,p_line_id => l_line_rec.line_id
3639 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
3640 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
3641 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
3642 ,p_change_sequence => l_line_rec.change_sequence
3643 ,p_source_document_id => l_line_rec.source_document_id
3644 ,p_source_document_line_id => l_line_rec.source_document_line_id
3645 ,p_order_source_id => l_line_rec.order_source_id
3646 ,p_source_document_type_id => l_line_rec.source_document_type_id);
3647
3648 x_result_out := 'COMPLETE:#NULL';
3649
3650 IF l_debug_level > 0 THEN
3651 oe_debug_pub.add('check if this line is part of fulfillment set ',5);
3652 END IF;
3653
3654 g_set_tbl.delete;
3655
3656
3657
3658 Get_Fulfillment_Set
3659 (
3660 p_line_id => l_line_rec.line_id
3661 , x_return_status => l_return_status
3662 , x_set_tbl => l_set_tbl
3663 );
3664
3665
3666 IF l_debug_level > 0 THEN
3667 oe_debug_pub.add( 'RETURN STATUS FROM GET FULFILLMENT SET : '||L_RETURN_STATUS||'/'||TO_CHAR ( L_SET_TBL.COUNT ) , 3 ) ;
3668 END IF;
3669
3670 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3671 IF l_debug_level > 0 THEN
3672 oe_debug_pub.add('unexp error while getting fulfillment set info '||sqlerrm,1);
3673 END IF;
3674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3675 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3676 IF l_debug_level > 0 THEN
3677 oe_debug_pub.add('exc error while getting fulfillment set info '||sqlerrm,1);
3678 END IF;
3679 RAISE FND_API.G_EXC_ERROR;
3680 END IF;
3681
3682 /* Lock the relevent lines */
3683
3684 BEGIN
3685 IF l_set_tbl.count > 0 THEN
3686 IF g_set_tbl.count > 1 THEN
3687
3688 IF l_debug_level > 0 THEN
3689 oe_debug_pub.add('LOCKING HEADER '||L_LINE_REC.HEADER_ID||'/'||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
3690 END IF;
3691
3692 SELECT HEADER_ID
3693 INTO l_header_id
3694 FROM OE_ORDER_HEADERS
3695 WHERE HEADER_ID = l_line_rec.header_id
3696 FOR UPDATE NOWAIT;
3697
3698 IF l_debug_level > 0 THEN
3699 oe_debug_pub.add('header locked successfully ',1);
3700 END IF;
3701
3702 ELSE
3703 --7827727
3704 --lock_set_id := g_set_tbl.FIRST;
3705 IF g_set_tbl.FIRST IS NULL THEN
3706 lock_set_id := NULL;
3707 ELSE
3708 lock_set_id := g_set_tbl(g_set_tbl.FIRST);
3709 END IF;
3710
3711 IF lock_set_id is NOT NULL THEN
3712 IF l_debug_level > 0 THEN
3713 oe_debug_pub.add( 'locking set with ID '||LOCK_SET_ID||' at '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
3714 END IF;
3715
3716 SELECT SET_ID
3717 INTO l_set_id
3718 FROM OE_SETS
3719 WHERE SET_ID = lock_set_id
3720 FOR UPDATE NOWAIT;
3721
3722 IF l_debug_level > 0 THEN
3723 oe_debug_pub.add( 'set locked successfully ',1);
3724 END IF;
3725 END IF;
3726 END IF;
3727 END IF;
3728 IF nvl(l_line_rec.top_model_line_id,0) <> 0 THEN --bug 4189737, the ELSIF was changed to a IF condition
3729 IF l_debug_level > 0 THEN
3730 oe_debug_pub.add('this line is part of configuration',5);
3731 oe_debug_pub.add('locking while configuration at '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'),5);
3732 END IF;
3733
3734 SELECT line_id, top_model_line_id
3735 INTO l_line_id, l_top_model_line_id
3736 FROM oe_order_lines
3737 WHERE line_id = l_line_rec.top_model_line_id
3738 FOR UPDATE NOWAIT;
3739
3740 IF l_debug_level > 0 THEN
3741 oe_debug_pub.add('configuration locked successfully',1);
3742 END IF;
3743 /* Fix for bug 2560644 */
3744
3745 ELSIF l_line_rec.item_type_code = 'SERVICE' AND
3746 l_line_rec.service_reference_line_id IS NOT NULL AND
3747 l_line_rec.service_reference_type_code = 'ORDER' THEN
3748 IF l_debug_level > 0 THEN
3749 oe_debug_pub.add('this is a service line now at fulfillment '|| L_LINE_REC.SERVICE_REFERENCE_TYPE_CODE,5);
3750 END IF;
3751
3752 /* First lock the product */
3753
3754 SELECT top_model_line_id
3755 INTO l_top_model_line_id
3756 FROM oe_order_lines
3757 WHERE line_id = l_line_rec.service_reference_line_id
3758 FOR UPDATE NOWAIT;
3759
3760 IF nvl(l_top_model_line_id,0) <> 0 THEN
3761 IF l_debug_level > 0 THEN
3762 oe_debug_pub.add('locking the parent model line of this service line '||L_TOP_MODEL_LINE_ID,1);
3763 END IF;
3764
3765 SELECT line_id
3766 INTO l_line_id
3767 FROM oe_order_lines
3768 WHERE line_id = l_top_model_line_id
3769 FOR UPDATE NOWAIT;
3770
3771 ELSE
3772 IF l_debug_level > 0 THEN
3773 oe_debug_pub.add('locking the parent reference line of this service line '||L_TOP_MODEL_LINE_ID,1);
3774 END IF;
3775 SELECT line_id
3776 INTO l_line_id
3777 FROM oe_order_lines
3778 WHERE line_id = l_line_rec.service_reference_line_id
3779 FOR UPDATE NOWAIT;
3780
3781 END IF;
3782 IF l_debug_level > 0 THEN
3783 oe_debug_pub.add('product lines locked successfully '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
3784 END IF;
3785
3786 SELECT line_id
3787 INTO l_line_id
3788 FROM oe_order_lines
3789 WHERE line_id = l_line_rec.line_id
3790 FOR UPDATE NOWAIT;
3791
3792 IF l_debug_level > 0 THEN
3793 oe_debug_pub.add('locked the service line '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
3794 END IF;
3795 /* fix for bug 2517485 */
3796 ELSE
3797 IF l_debug_level > 0 THEN
3798 oe_debug_pub.add('locking the current line '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
3799 END IF;
3800
3801 SELECT line_id
3802 INTO l_line_id
3803 FROM oe_order_lines
3804 WHERE line_id = l_line_rec.line_id
3805 FOR UPDATE NOWAIT;
3806
3807 IF l_debug_level > 0 THEN
3808 oe_debug_pub.add( 'LINE LOCKED '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
3809 END IF;
3810
3811 END IF;
3812
3813 EXCEPTION
3814 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
3815 -- some one else is currently working on the line
3816 IF l_debug_level > 0 THEN
3817 oe_debug_pub.add('unable to lock the lines or configuration',1);
3818 END IF;
3819 G_DEBUG_MSG := G_DEBUG_MSG || 'ELOCK1,';
3820 RAISE CAN_NOT_LOCK_MODEL;
3821 END;
3822
3823
3824 IF l_debug_level > 0 THEN
3825 oe_debug_pub.add( 'ACTIVTITY ID : '||TO_CHAR ( P_ACTIVITY_ID ) , 3 ) ;
3826 END IF;
3827 l_item_key := to_char(p_line_id);
3828
3829 IF l_debug_level > 0 THEN
3830 oe_debug_pub.add( 'CALLING GET FULFILLMENT ACTIVITY ' , 3 ) ;
3831 END IF;
3832
3833 Get_Fulfillment_Activity
3834 (
3835 p_item_key => l_item_key,
3836 p_activity_id => p_activity_id,
3837 x_fulfillment_activity => l_fulfillment_activity,
3838 x_return_status => l_return_status
3839 );
3840
3841
3842
3843 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3845 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3846 RAISE FND_API.G_EXC_ERROR;
3847 END IF;
3848
3849 IF l_debug_level > 0 THEN
3850 oe_debug_pub.add( 'FULFILLMENT ACTIVITY : '|| L_FULFILLMENT_ACTIVITY , 3 ) ;
3851 END IF;
3852
3853
3854
3855 IF l_fulfillment_activity <> 'NO_ACTIVITY' THEN
3856 l_fulfillment_type := G_FULFILL_WITH_ACTIVITY;
3857
3858 -- Fulfill Line.
3859
3860 IF l_debug_level > 0 THEN
3861 oe_debug_pub.add( '..503..CALLING FULFILL LINE WITH : '|| L_FULFILLMENT_TYPE||'/'||L_FULFILLMENT_ACTIVITY , 3 ) ;
3862 END IF;
3863
3864
3865 Fulfill_Line
3866 (
3867 p_line_rec => l_line_rec,
3868 p_mode => 'RECORD',
3869 p_fulfillment_type => l_fulfillment_type,
3870 p_fulfillment_activity => l_fulfillment_activity,
3871 x_return_status => l_return_status
3872 );
3873
3874
3875 IF l_debug_level > 0 THEN
3876 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL LINE : '||L_RETURN_STATUS , 3 ) ;
3877 END IF;
3878
3879 IF l_return_status = 'D' THEN
3880 RAISE CAN_NOT_LOCK_MODEL;
3881 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3883 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3884 RAISE FND_API.G_EXC_ERROR;
3885 END IF;
3886 ELSE
3887 l_fulfillment_type := G_FULFILL_NO_ACTIVITY;
3888 --l_fulfillment_activity := FND_API.G_MISS_CHAR;
3889 END IF;
3890
3891 IF l_set_tbl.count > 0 THEN
3892 -- Processing for Fulfillment set
3893 IF l_debug_level > 0 THEN
3894 oe_debug_pub.add( 'IT IS LINE PART OF A FULFILLMENT SET' , 3 ) ;
3895 END IF;
3896
3897
3898
3899 Process_Fulfillment_Set
3900 (
3901 p_line_id => l_line_rec.line_id,
3902 p_fulfillment_activity => l_fulfillment_activity,
3903 p_line_set_tbl => l_set_tbl,
3904 x_return_status => l_return_status,
3905 x_fulfillment_status => l_fulfillment_status
3906 );
3907
3908
3909 IF l_debug_level > 0 THEN
3910 oe_debug_pub.add( 'RETURN STATUS/FULFILLMENT STATUS : '||L_RETURN_STATUS||'/'||L_FULFILLMENT_STATUS , 3 ) ;
3911 END IF;
3912
3913 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3915 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3916 RAISE FND_API.G_EXC_ERROR;
3917 END IF;
3918
3919
3920
3921 IF l_fulfillment_status = FND_API.G_FALSE THEN
3922 IF l_debug_level > 0 THEN
3923 oe_debug_pub.add( 'ALL THE LINES OF THE FULFILLMENT SET HAS NOT BEEN FULFILLED' , 3 ) ;
3924 END IF;
3925 x_result_out := 'NOTIFIED:#NULL';
3926 ELSIF l_fulfillment_type = G_FULFILL_NO_ACTIVITY THEN
3927
3928 IF l_debug_level > 0 THEN
3929 oe_debug_pub.add( '..505.. Calling Fulfill_Line' , 3 ) ;
3930 END IF;
3931
3932 Fulfill_Line
3933 (
3934 p_line_rec => l_line_rec,
3935 p_mode => 'RECORD',
3936 p_fulfillment_type => l_fulfillment_type,
3937 p_fulfillment_activity => l_fulfillment_activity,
3938 x_return_status => l_return_status
3939 );
3940
3941 IF l_return_status = 'D' THEN
3942 RAISE CAN_NOT_LOCK_MODEL;
3943 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3945 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3946 RAISE FND_API.G_EXC_ERROR;
3947 END IF;
3948 END IF;
3949
3950
3951
3952 ELSIF l_line_rec.top_model_line_id IS NOT NULL AND
3953 l_line_rec.top_model_line_id <> FND_API.G_MISS_NUM THEN
3954
3955 -- Processing for PTO/ATO/KIT
3956 IF l_debug_level > 0 THEN
3957 oe_debug_pub.add( 'IT IS LINE PART OF A PTO' , 3 ) ;
3958 END IF;
3959
3960 -- Processing for Remnant Model
3961
3962
3963
3964 IF l_line_rec.model_remnant_flag = 'Y' THEN
3965
3966 IF l_debug_level > 0 THEN
3967 oe_debug_pub.add( 'LINE IS PART OF REMNANT MODEL '||TO_CHAR ( L_LINE_REC.LINE_ID ) , 3 ) ;
3968 END IF;
3969
3970
3971
3972 -- Bug-2376255
3973 SELECT fulfilled_flag
3974 INTO l_fulfilled_flag
3975 FROM oe_order_lines
3976 WHERE Line_id = l_line_rec.line_id;
3977
3978
3979
3980 IF nvl(l_fulfilled_flag,'N') = 'N'
3981 --bug 6394191 AND Condition Added
3982 AND nvl(l_line_rec.ato_line_id,0) = 0 THEN -- Bug-2376255
3983 IF l_debug_level > 0 THEN
3984 oe_debug_pub.add( '..506..CALLING FULFILL LINE WITH : '|| G_FULFILL_NO_ACTIVITY , 3 ) ;
3985 END IF;
3986
3987 Fulfill_Line
3988 (
3989 p_line_rec => l_line_rec,
3990 p_mode => 'RECORD',
3991 p_fulfillment_type => G_FULFILL_NO_ACTIVITY,
3992 p_fulfillment_activity => l_fulfillment_activity,
3993 x_return_status => l_return_status
3994 );
3995
3996 IF l_return_status = 'D' THEN
3997 RAISE CAN_NOT_LOCK_MODEL;
3998 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4000 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4001 RAISE FND_API.G_EXC_ERROR;
4002 END IF;
4003
4004 END IF; /* fulfilled _flag */
4005 --bug 6394191 ATO Remnant Model Processing Modified ****Start
4006 IF l_line_rec.ato_line_id IS NOT NULL AND l_line_rec.ato_line_id <> FND_API.G_MISS_NUM then --AND
4007 --(l_line_rec.item_type_code = 'CONFIG' OR (l_line_rec.item_type_code = 'CLASS' AND
4008 -- l_line_rec.ato_line_id = l_line_rec.line_id)) THEN
4009
4010 IF l_debug_level > 0 THEN
4011 oe_debug_pub.add('line is part of ATO sub configuration ',5);
4012 END IF;
4013
4014 IF l_line_rec.ato_line_id = l_line_rec.line_id THEN
4015 IF l_debug_level > 0 THEN
4016 oe_debug_pub.add('Model line of ATO sub configuration ',5);
4017 END IF;
4018 END IF;
4019
4020 IF l_line_rec.item_type_code = 'CONFIG' THEN
4021 IF l_debug_level > 0 THEN
4022 oe_debug_pub.add('config line of ATO sub configuration ',5);
4023 END IF;
4024 END IF;
4025
4026 OE_Config_Util.Query_ATO_Options( p_ato_line_id => l_line_rec.ato_line_id,
4027 x_line_tbl => l_line_tbl);
4028
4029 FOR l_line_index IN l_line_tbl.FIRST .. l_line_tbl.LAST LOOP
4030
4031
4032 IF l_line_tbl(l_line_index).line_id <> p_line_id THEN
4033 IF l_debug_level > 0 THEN
4034 oe_debug_pub.add('call Get_Activity_Result() - '||L_ITEM_KEY , 3 ) ;
4035 END IF;
4036 l_item_key := to_char(l_line_tbl(l_line_index).line_id);
4037
4038 Get_Activity_Result
4039 (
4040 p_item_type => OE_GLOBALS.G_WFI_LIN
4041 , p_item_key => l_item_key
4042 , p_activity_name => 'FULFILL_LINE'
4043 , x_return_status => l_return_status
4044 , x_activity_result => l_activity_result
4045 , x_activity_status_code => l_activity_status
4046 , x_activity_id => l_activity_id
4047 );
4048
4049 IF l_debug_level > 0 THEN
4050 oe_debug_pub.add('return status - '||L_RETURN_STATUS , 3 ) ;
4051 oe_debug_pub.add('activity status - '||l_activity_status , 3 ) ;
4052 END IF;
4053 /*Added for bug 8790623*/
4054 IF l_activity_status = 'DEFERRED' THEN
4055 wf_item_activity_status.create_status('OEOL',to_char(l_item_key),l_activity_id,'NOTIFIED',NULL,SYSDATE,null);
4056 l_activity_status:= NULL ;
4057 END IF ;
4058 /*Added for bug 8790623*/
4059
4060 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4062 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4063 IF l_debug_level > 0 THEN
4064 oe_debug_pub.add('The ATO line is not at fulfillment '||TO_CHAR ( L_LINE_REC.ATO_LINE_ID ) , 3 ) ;
4065 END IF;
4066 x_result_out := 'NOTIFIED:#NULL';
4067 GOTO END_ATO_REMNANT;
4068 END IF;
4069 END IF;
4070 END LOOP;
4071
4072 IF l_debug_level > 0 THEN
4073 oe_debug_pub.add('..507..Calling Fulfill_Line',5);
4074 END IF;
4075
4076 FOR l_line_index IN l_line_tbl.FIRST .. l_line_tbl.LAST LOOP
4077 Fulfill_Line
4078 ( p_line_rec => l_line_tbl(l_line_index),
4079 p_mode => 'RECORD',
4080 p_fulfillment_type => G_FULFILL_NO_ACTIVITY,
4081 p_fulfillment_activity => l_fulfillment_activity,
4082 x_return_status => l_return_status
4083 );
4084
4085 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4087 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4088 RAISE FND_API.G_EXC_ERROR;
4089 END IF;
4090
4091
4092 IF l_line_tbl(l_line_index).line_id <> p_line_id THEN
4093 IF l_debug_level > 0 THEN
4094 oe_debug_pub.add('update flow status code',5);
4095 END IF;
4096
4097 OE_Order_WF_Util.Update_Flow_Status_Code
4098 (p_line_id => l_line_tbl(l_line_index).line_id,
4099 p_flow_status_code => 'FULFILLED',
4100 x_return_status => l_return_status
4101 );
4102
4103 IF l_debug_level > 0 THEN
4104 oe_debug_pub.add('return status - '||L_RETURN_STATUS,5);
4105 END IF;
4106
4107 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4109 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4110 RAISE FND_API.G_EXC_ERROR;
4111 END IF;
4112
4113
4114 IF l_debug_level > 0 THEN
4115 oe_debug_pub.add('complete the WF activity for line - '||L_LINE_TBL(L_LINE_INDEX).LINE_ID,5) ;
4116 END IF;
4117 BEGIN
4118 wf_engine.CompleteActivityInternalName('OEOL', to_char(l_line_tbl(l_line_index).line_id), 'FULFILL_LINE', '#NULL',TRUE ); --15870313
4119 EXCEPTION
4120 WHEN OTHERS THEN
4121 oe_debug_pub.add('Workflow complete activity failed. Raising');
4122 RAISE ;
4123 END ;
4124 IF l_debug_level > 0 THEN
4125 oe_debug_pub.add('after wf complete ',5);
4126 END IF;
4127 END IF;
4128
4129 IF l_debug_level > 0 THEN
4130 oe_debug_pub.add('now fulfill the associated service lines of '||L_LINE_TBL(L_LINE_INDEX).LINE_ID,5);
4131 END IF;
4132
4133 Fulfill_Service_Lines
4134 (
4135 p_line_id => l_line_tbl(l_line_index).line_id,
4136 p_header_id => l_line_tbl(l_line_index).header_id, -- 1717444
4137 x_return_status => l_return_status
4138 );
4139
4140 IF l_debug_level > 0 THEN
4141 oe_debug_pub.add('return status - '||L_RETURN_STATUS , 3 ) ;
4142 END IF;
4143
4144 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4146 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4147 RAISE FND_API.G_EXC_ERROR;
4148 END IF;
4149 END LOOP;
4150 --bug6394191 Remnant ATO Model Processing Modified **End
4151
4152 --Old Processing Commented Below
4153 /* IF l_line_rec.ato_line_id IS NOT NULL AND
4154 l_line_rec.ato_line_id <> FND_API.G_MISS_NUM AND
4155 fix for bug 2206098
4156 (l_line_rec.item_type_code = 'CONFIG' OR
4157 (l_line_rec.item_type_code = 'CLASS' AND
4158 l_line_rec.ato_line_id = l_line_rec.line_id)) THEN
4159
4160
4161
4162 IF l_debug_level > 0 THEN
4163 oe_debug_pub.add( 'LINE IS PART OF ATO SUB CONFIG ' , 3 ) ;
4164 END IF;
4165
4166 IF l_line_rec.ato_line_id = l_line_rec.line_id THEN
4167
4168 IF l_debug_level > 0 THEN
4169 oe_debug_pub.add( 'MODEL LINE OF ATO SUB CONFIG ' , 3 ) ;
4170 END IF;
4171
4172 l_line_tbl := OE_Config_Util.Query_Options(l_line_rec.top_model_line_id);
4173 OE_Config_Util.Query_Options(p_top_model_line_id => l_line_rec.top_model_line_id,
4174 x_line_tbl => l_line_tbl);
4175 FOR l_line_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
4176 LOOP
4177 IF l_line_tbl(l_line_index).ato_line_id = l_line_rec.ato_line_id AND
4178 l_line_tbl(l_line_index).item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
4179 l_config_index := l_line_index;
4180 GOTO CONFIG_FOUND;
4181 END IF;
4182
4183 END LOOP;
4184 << CONFIG_FOUND >>
4185
4186 IF l_debug_level > 0 THEN
4187 oe_debug_pub.add( 'CONFIG LINE OF ATO SUB CONFIG '||L_LINE_TBL ( L_CONFIG_INDEX ) .LINE_ID , 3 ) ;
4188 END IF;
4189
4190
4191 l_item_key := to_char(l_line_tbl(l_config_index).line_id);
4192
4193 IF l_debug_level > 0 THEN
4194 oe_debug_pub.add( 'CALLING GET ACTIVITY RESULT FOR ITEM : '||L_ITEM_KEY , 3 ) ;
4195 END IF;
4196
4197 Get_Activity_Result
4198 (
4199 p_item_type => OE_GLOBALS.G_WFI_LIN
4200 , p_item_key => l_item_key
4201 , p_activity_name => 'FULFILL_LINE'
4202 , x_return_status => l_return_status
4203 , x_activity_result => l_activity_result
4204 , x_activity_status_code => l_activity_status
4205 , x_activity_id => l_activity_id
4206 );
4207
4208
4209 IF l_debug_level > 0 THEN
4210 oe_debug_pub.add( 'RETURN STATUS FROM GET ACTIVITY RESULT : '||L_RETURN_STATUS , 3 ) ;
4211 END IF;
4212
4213 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4215 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4216 IF l_debug_level > 0 THEN
4217 oe_debug_pub.add( 'CONFIG LINE IS NOT AT FULFILLMENT : '||TO_CHAR ( L_LINE_REC.ATO_LINE_ID ) , 3 ) ;
4218 END IF;
4219 x_result_out := 'NOTIFIED:#NULL';
4220 GOTO END_ATO_REMNANT;
4221 END IF;
4222
4223 /* bug 4460242 */
4224 -- Move update flow status code only when NOTIFIED status
4225 -- and before we do complete that activity
4226
4227
4228 /* Complete CONFIG if it is notified
4229 IF l_activity_status = 'NOTIFIED' THEN
4230 -- bug 4460242 start
4231 IF l_debug_level > 0 THEN
4232 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
4233 END IF;
4234
4235 OE_Order_WF_Util.Update_Flow_Status_Code
4236 (p_line_id => l_line_tbl(l_config_index).line_id,
4237 p_flow_status_code => 'FULFILLED',
4238 x_return_status => l_return_status
4239 );
4240
4241 IF l_debug_level > 0 THEN
4242 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
4243 END IF;
4244
4245 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4247 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4248 RAISE FND_API.G_EXC_ERROR;
4249 END IF;
4250 -- end of bug 4460242
4251
4252 IF l_debug_level > 0 THEN
4253 oe_debug_pub.add( 'CALLING WF_ENGINE.COMPLETEACTIVITYINTERNALNAME FOR CONFIG LINE OF ATO SUB CONFIG '|| TO_CHAR ( L_LINE_TBL ( L_CONFIG_INDEX ) .LINE_ID ) , 3 ) ;
4254 END IF;
4255
4256 wf_engine.CompleteActivityInternalName('OEOL', to_char(l_line_tbl(l_config_index).line_id), 'FULFILL_LINE', '#NULL');
4257 IF l_debug_level > 0 THEN
4258 oe_debug_pub.add( 'RETURNED FROM WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( L_LINE_TBL ( L_CONFIG_INDEX ) .LINE_ID ) , 3 ) ;
4259 END IF;
4260
4261 END IF;
4262
4263 IF l_debug_level > 0 THEN
4264 oe_debug_pub.add( 'FULFILL ASSOCIATED SERVICE LINES '||L_LINE_TBL ( L_CONFIG_INDEX ) .LINE_ID , 3 ) ;
4265 END IF;
4266
4267 Fulfill_Service_Lines
4268 (
4269 p_line_id => l_line_tbl(l_config_index).line_id,
4270 p_header_id => l_line_tbl(l_config_index).header_id, -- 1717444
4271 x_return_status => l_return_status
4272 );
4273
4274
4275 IF l_debug_level > 0 THEN
4276 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL SERVICE LINES API '||L_RETURN_STATUS , 3 ) ;
4277 END IF;
4278
4279 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4281 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4282 RAISE FND_API.G_EXC_ERROR;
4283 END IF;
4284
4285
4286 ELSE
4287 IF l_debug_level > 0 THEN
4288 oe_debug_pub.add( 'CONFIG LINE OF ATO SUB CONFIG '||L_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
4289 END IF;
4290
4291 l_item_key := to_char(l_line_rec.ato_line_id);
4292
4293 IF l_debug_level > 0 THEN
4294 oe_debug_pub.add( 'CALLING GET ACTIVITY RESULT FOR ITEM : '||L_ITEM_KEY , 3 ) ;
4295 END IF;
4296
4297 Get_Activity_Result
4298 (
4299 p_item_type => OE_GLOBALS.G_WFI_LIN
4300 , p_item_key => l_item_key
4301 , p_activity_name => 'FULFILL_LINE'
4302 , x_return_status => l_return_status
4303 , x_activity_result => l_activity_result
4304 , x_activity_status_code => l_activity_status
4305 , x_activity_id => l_activity_id
4306 );
4307
4308 IF l_debug_level > 0 THEN
4309 oe_debug_pub.add( 'RETURN STATUS FROM GET ACTIVITY RESULT : '||L_RETURN_STATUS||'/'||L_ACTIVITY_STATUS , 3 ) ;
4310 END IF;
4311
4312 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4314 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4315 IF l_debug_level > 0 THEN
4316 oe_debug_pub.add( 'MODEL LINE IS NOT AT FULFILLMENT : '||TO_CHAR ( L_LINE_REC.ATO_LINE_ID ) , 3 ) ;
4317 END IF;
4318 x_result_out := 'NOTIFIED:#NULL';
4319 GOTO END_ATO_REMNANT;
4320 END IF;
4321
4322 -- bug 4460242
4323 -- move update_flow_status inside IF NOTIFIED loop
4324
4325 /* Complete ATO MODEL if it is notified
4326 IF l_activity_status = 'NOTIFIED' THEN
4327 -- bug 4460242 start
4328 IF l_debug_level > 0 THEN
4329 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
4330 END IF;
4331
4332 OE_Order_WF_Util.Update_Flow_Status_Code
4333 (p_line_id => l_line_rec.ato_line_id,
4334 p_flow_status_code => 'FULFILLED',
4335 x_return_status => l_return_status
4336 );
4337
4338 IF l_debug_level > 0 THEN
4339 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
4340 END IF;
4341
4342 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4344 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4345 RAISE FND_API.G_EXC_ERROR;
4346 END IF;
4347 -- bug 4460242 end
4348
4349 IF l_debug_level > 0 THEN
4350 oe_debug_pub.add( 'CALLING WF_ENGINE.COMPLETEACTIVITYINTERNALNAME FOR MODEL LINE OF ATO SUB CONFIG '|| TO_CHAR ( L_LINE_REC.ATO_LINE_ID ) , 3 ) ;
4351 END IF;
4352
4353 wf_engine.CompleteActivityInternalName('OEOL', to_char(l_line_rec.ato_line_id), 'FULFILL_LINE', '#NULL');
4354 IF l_debug_level > 0 THEN
4355 oe_debug_pub.add( 'RETURNED FROM WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( L_LINE_REC.ATO_LINE_ID ) , 3 ) ;
4356 END IF;
4357
4358 END IF;
4359
4360 IF l_debug_level > 0 THEN
4361 oe_debug_pub.add( 'FULFILL ASSOCIATED SERVICE LINES '||L_LINE_REC.ATO_LINE_ID , 3 ) ;
4362 END IF;
4363
4364 Fulfill_Service_Lines
4365 (
4366 p_line_id => l_line_rec.ato_line_id,
4367 p_header_id => l_line_rec.header_id, -- 1717444
4368 x_return_status => l_return_status
4369 );
4370
4371
4372 IF l_debug_level > 0 THEN
4373 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL SERVICE LINES API '||L_RETURN_STATUS , 3 ) ;
4374 END IF;
4375
4376 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4378 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4379 RAISE FND_API.G_EXC_ERROR;
4380 END IF;
4381
4382 END IF;*/
4383
4384
4385 << END_ATO_REMNANT >>
4386 NULL;
4387 ELSE
4388
4389 IF l_debug_level > 0 THEN
4390 oe_debug_pub.add( 'FULFILL ASSOCIATED SERVICE LINES '||L_LINE_REC.LINE_ID , 3 ) ;
4391 END IF;
4392
4393 Fulfill_Service_Lines
4394 (
4395 p_line_id => l_line_rec.line_id,
4396 p_header_id => l_line_rec.header_id, -- 1717444
4397 x_return_status => l_return_status
4398 );
4399
4400
4401 IF l_debug_level > 0 THEN
4402 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL SERVICE LINES API '||L_RETURN_STATUS , 3 ) ;
4403 END IF;
4404
4405 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4407 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4408 RAISE FND_API.G_EXC_ERROR;
4409 END IF;
4410
4411 END IF;
4412
4413 ELSE
4414
4415 -- process PTO fulfillment activity.
4416 IF l_debug_level > 0 THEN
4417 oe_debug_pub.add( 'CALLING PROCESS PTO FULFILLMENT ' , 3 ) ;
4418 END IF;
4419 Process_PTO_KIT
4420 (
4421 p_line_id => l_line_rec.line_id,
4422 p_top_model_line_id => l_line_rec.top_model_line_id,
4423 p_fulfillment_activity => l_fulfillment_activity,
4424 p_process_all => FND_API.G_FALSE,
4425 x_return_status => l_return_status,
4426 x_fulfillment_status => l_fulfillment_status
4427 );
4428
4429
4430 IF l_debug_level > 0 THEN
4431 oe_debug_pub.add( 'RETURN STATUS/FULFILLMENT STATUS : '||L_RETURN_STATUS||'/'||L_FULFILLMENT_STATUS , 3 ) ;
4432 END IF;
4433
4434 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4436 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4437 RAISE FND_API.G_EXC_ERROR;
4438 END IF;
4439
4440
4441 IF l_fulfillment_status = FND_API.G_FALSE THEN
4442 IF l_debug_level > 0 THEN
4443 oe_debug_pub.add( 'ALL THE LINES OF THE FULFILLMENT SET HAS NOT BEEN FULFILLED' , 3 ) ;
4444 END IF;
4445 x_result_out := 'NOTIFIED:#NULL';
4446 ELSIF l_fulfillment_type = G_FULFILL_NO_ACTIVITY THEN
4447
4448 IF l_debug_level > 0 THEN
4449 oe_debug_pub.add( '..509..Calling Fulfill_Line' , 3 ) ;
4450 END IF;
4451
4452 Fulfill_Line
4453 (
4454 p_line_rec => l_line_rec,
4455 p_mode => 'RECORD',
4456 p_fulfillment_type => l_fulfillment_type,
4457 p_fulfillment_activity => l_fulfillment_activity,
4458 x_return_status => l_return_status
4459 );
4460
4461 IF l_return_status = 'D' THEN
4462 RAISE CAN_NOT_LOCK_MODEL;
4463 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4465 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4466 RAISE FND_API.G_EXC_ERROR;
4467 END IF;
4468 END IF;
4469 -- END IF;
4470
4471 END IF;
4472
4473
4474 ELSIF l_line_rec.item_type_code = 'SERVICE' AND
4475 l_line_rec.service_reference_line_id IS NOT NULL AND
4476 l_line_rec.service_reference_type_code = 'ORDER' THEN
4477
4478 IF l_debug_level > 0 THEN
4479 oe_debug_pub.add( 'SERVICE LINE AT FULFILLMENT : '||L_LINE_REC.SERVICE_REFERENCE_TYPE_CODE , 3 ) ;
4480 END IF;
4481
4482
4483 SELECT fulfilled_flag,
4484 header_id -- 1717444
4485 INTO l_fulfilled_flag,
4486 l_ref_header_id -- 1717444
4487 FROM oe_order_lines
4488 WHERE line_id = l_line_rec.service_reference_line_id;
4489
4490 IF l_debug_level > 0 THEN
4491 oe_debug_pub.add( 'PRODUCT LINE FULFILLED : '||L_FULFILLED_FLAG , 3 ) ;
4492 END IF;
4493
4494 IF l_fulfilled_flag = 'Y' OR -- OR condition added for 1717444
4495 l_ref_header_id <> l_line_rec.header_id THEN
4496
4497
4498 IF l_fulfillment_type = G_FULFILL_NO_ACTIVITY THEN
4499
4500
4501 IF l_debug_level > 0 THEN
4502 oe_debug_pub.add( '..510..CALLING FULFILL LINE WITH : '|| L_FULFILLMENT_TYPE||'/'||L_FULFILLMENT_ACTIVITY , 3 ) ;
4503 END IF;
4504
4505 Fulfill_Line
4506 (
4507 p_line_rec => l_line_rec,
4508 p_mode => 'RECORD',
4509 p_fulfillment_type => l_fulfillment_type,
4510 p_fulfillment_activity => l_fulfillment_activity,
4511 x_return_status => l_return_status
4512 );
4513
4514
4515 IF l_debug_level > 0 THEN
4516 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL LINE : '||L_RETURN_STATUS , 3 ) ;
4517 END IF;
4518
4519 IF l_return_status = 'D' THEN
4520 RAISE CAN_NOT_LOCK_MODEL;
4521 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4523 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4524 RAISE FND_API.G_EXC_ERROR;
4525 END IF;
4526
4527 END IF;
4528
4529 IF l_debug_level > 0 THEN
4530 oe_debug_pub.add( 'SERVICE LINE IS FULFILLED' , 3 ) ;
4531 END IF;
4532 ELSE
4533
4534
4535 IF l_debug_level > 0 THEN
4536 oe_debug_pub.add( 'SERVICE LINE AT FULFILLMENT' , 3 ) ;
4537 END IF;
4538 x_result_out := 'NOTIFIED:#NULL';
4539
4540 END IF;
4541
4542 ELSE
4543 IF l_debug_level > 0 THEN
4544 oe_debug_pub.add( 'FULFILL_LINE ACTIVITY IS COMPLETE '||L_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
4545 END IF;
4546
4547
4548 IF l_fulfillment_type = G_FULFILL_NO_ACTIVITY THEN
4549
4550 IF l_debug_level > 0 THEN
4551 oe_debug_pub.add( '..511..CALLING FULFILL LINE WITH : '|| L_FULFILLMENT_TYPE||'/'||L_FULFILLMENT_ACTIVITY , 3 ) ;
4552 END IF;
4553
4554 Fulfill_Line
4555 (
4556 p_line_rec => l_line_rec,
4557 p_mode => 'RECORD',
4558 p_fulfillment_type => l_fulfillment_type,
4559 p_fulfillment_activity => l_fulfillment_activity,
4560 x_return_status => l_return_status
4561 );
4562
4563
4564 IF l_debug_level > 0 THEN
4565 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL LINE : '||L_RETURN_STATUS , 3 ) ;
4566 END IF;
4567
4568 IF l_return_status = 'D' THEN
4569 RAISE CAN_NOT_LOCK_MODEL;
4570 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4572 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4573 RAISE FND_API.G_EXC_ERROR;
4574 END IF;
4575
4576 IF l_debug_level > 0 THEN
4577 oe_debug_pub.add( 'FULFILL ASSOCIATED SERVICE LINES '||L_LINE_REC.LINE_ID , 3 ) ;
4578 END IF;
4579
4580 Fulfill_Service_Lines
4581 (
4582 p_line_id => l_line_rec.line_id,
4583 p_header_id => l_line_rec.header_id, -- 1717444
4584 x_return_status => l_return_status
4585 );
4586
4587
4588 IF l_debug_level > 0 THEN
4589 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL SERVICE LINES API '||L_RETURN_STATUS , 3 ) ;
4590 END IF;
4591
4592 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4594 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4595 RAISE FND_API.G_EXC_ERROR;
4596 END IF;
4597
4598 ELSE
4599 IF l_debug_level > 0 THEN
4600 oe_debug_pub.add( 'FULFILL ASSOCIATED SERVICE LINES '||L_LINE_REC.LINE_ID , 3 ) ;
4601 END IF;
4602
4603
4604 Fulfill_Service_Lines
4605 (
4606 p_line_id => l_line_rec.line_id,
4607 p_header_id => l_line_rec.header_id, -- 1717444
4608 x_return_status => l_return_status
4609 );
4610
4611 IF l_debug_level > 0 THEN
4612 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL SERVICE LINES API '||L_RETURN_STATUS , 3 ) ;
4613 END IF;
4614
4615 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4617 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4618 RAISE FND_API.G_EXC_ERROR;
4619 END IF;
4620
4621
4622 END IF;
4623
4624 END IF;
4625
4626 IF l_debug_level > 0 THEN
4627 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.PROCESS_FULFILLMENT '||X_RESULT_OUT , 3 ) ;
4628 END IF;
4629
4630 IF x_result_out = 'NOTIFIED:#NULL' THEN
4631
4632 l_flow_status_code := 'AWAITING_FULFILLMENT';
4633 /* 10298607: Must handle locking exception because fulfill_line() is not
4634 called in this cas and the record is still unlocked */
4635 BEGIN
4636 SELECT '1'
4637 INTO l_dummy
4638 FROM oe_order_lines_all
4639 WHERE line_id = l_line_rec.line_id
4640 FOR update nowait;
4641 EXCEPTION
4642 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
4643 -- some one else is currently working on the line
4644 IF l_debug_level > 0 THEN
4645 oe_debug_pub.add('Unable to lock to update status to'||l_flow_status_code,3);
4646 END IF;
4647 RAISE CAN_NOT_LOCK_MODEL;
4648 END;
4649 IF l_debug_level > 0 THEN
4650 oe_debug_pub.add('Returned from lock row with success:'||l_return_status,3);
4651 END IF;
4652 ------- END OF 10298607 -----------------
4653 ELSE
4654 l_flow_status_code := 'FULFILLED';
4655 END IF;
4656
4657 IF l_debug_level > 0 THEN
4658 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
4659 END IF;
4660
4661
4662 OE_Order_WF_Util.Update_Flow_Status_Code
4663 (p_line_id => l_line_rec.line_id,
4664 p_flow_status_code => l_flow_status_code,
4665 x_return_status => l_return_status
4666 );
4667
4668 IF l_debug_level > 0 THEN
4669 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
4670 END IF;
4671
4672 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4674 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4675 RAISE FND_API.G_EXC_ERROR;
4676 END IF;
4677
4678 x_return_status := FND_API.G_RET_STS_SUCCESS;
4679
4680 IF l_debug_level > 0 THEN
4681 oe_debug_pub.add( 'EXITING FROM OE_LINE_FULFILL.PROCESS_FULFILLMENT : '||X_RETURN_STATUS , 1 ) ;
4682 END IF;
4683
4684 EXCEPTION
4685
4686 WHEN CAN_NOT_LOCK_MODEL THEN
4687 IF l_debug_level > 0 THEN
4688 oe_debug_pub.add('OEXVFULB.pls: Process_fulfillment- MODEL LOCKING EXCEPTION' , 1 ) ;
4689 END IF;
4690 x_return_status := 'DEFERRED';
4691 G_DEBUG_MSG := G_DEBUG_MSG || 'ELOCK2,';
4692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4693 IF l_debug_level > 0 THEN
4694 oe_debug_pub.add( 'PROCESS_FULFILLMENT : EXITING WITH UNEXPECTED ERROR'||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
4695 END IF;
4696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4697 G_DEBUG_MSG := G_DEBUG_MSG || 'E22-1,';
4698
4699 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4700 THEN
4701 OE_MSG_PUB.Add_Exc_Msg
4702 ( G_PKG_NAME,
4703 'Process_Fulfillment'
4704 );
4705 END IF;
4706 G_DEBUG_MSG := G_DEBUG_MSG || 'E22-2,';
4707
4708 WHEN FND_API.G_EXC_ERROR THEN
4709 x_return_status := FND_API.G_RET_STS_ERROR;
4710 WHEN OTHERS THEN
4711 IF l_debug_level > 0 THEN
4712 oe_debug_pub.add( 'PROCESS_FULFILLMENT : EXITING WITH OTHERS ERROR' , 1 ) ;
4713 END IF;
4714 IF l_debug_level > 0 THEN
4715 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
4716 END IF;
4717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4718
4719 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4720 THEN
4721 OE_MSG_PUB.Add_Exc_Msg
4722 ( G_PKG_NAME
4723 , 'Process_Fulfillment'
4724 );
4725 END IF;
4726 G_DEBUG_MSG := G_DEBUG_MSG || 'E23,';
4727
4728 -- Get message count and data
4729
4730 OE_MSG_PUB.Count_And_Get
4731 ( p_count => x_msg_count
4732 , p_data => x_msg_data
4733 );
4734
4735 END Process_Fulfillment;
4736
4737
4738 -- Bug2068310: Parameter p_fulfill_operation is added. Value passed will be
4739 -- 'N' in case of deletion and cancellation of a line, and will be 'Y' when
4740 -- removing the line from fulfillment set.
4741
4742 PROCEDURE Cancel_line
4743 (
4744 p_line_id IN NUMBER
4745 , x_return_status OUT NOCOPY VARCHAR2
4746
4747 , p_fulfill_operation IN VARCHAR2 DEFAULT 'N'
4748 , p_set_id IN NUMBER DEFAULT NULL -- 2525203
4749 )
4750 IS
4751 -- Added For bug#2965878 Begin
4752 j Integer;
4753 initial Integer;
4754 nextpos Integer;
4755 t_line_id NUMBER;
4756 set_flag VARCHAR2(1) := 'N';
4757 -- Added For bug#2965878 End
4758
4759 l_set_tbl Line_Set_Tbl_Type;
4760 l_set_index NUMBER := 0;
4761 l_activity_status VARCHAR2(8);
4762 l_activity_result VARCHAR2(30);
4763 l_activity_id NUMBER;
4764 l_return_status VARCHAR2(1);
4765 l_line_rec OE_Order_Pub.Line_Rec_Type;
4766 l_process_current_line BOOLEAN := TRUE;
4767 l_complete_fulfillment BOOLEAN := TRUE;
4768
4769 /* 2525203 */
4770 l_set_f BOOLEAN := TRUE; /* fulfill set p_set_id? */
4771 l_oth_f BOOLEAN := TRUE; /* fulfill all other sets except p_set_id? */
4772 l_shared_lines BOOLEAN := FALSE;
4773 l_set_id NUMBER;
4774 l_howmany INTEGER := 0;
4775 l_common_sets INTEGER := 0;
4776 TYPE line_tbl IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
4777 l_fulfilled_lines line_tbl;
4778 l_set_status VARCHAR2(1);
4779 /* end 2525203 */
4780 --Start Bug 7827727
4781 l_line_id_mod NUMBER;
4782 l_set_id_mod NUMBER;
4783 --end of Bug 7827727
4784
4785 TYPE set_tbl IS TABLE OF NUMBER
4786 INDEX BY BINARY_INTEGER;
4787 l_close_tbl set_tbl;
4788 l_close_tbl_index NUMBER := 0;
4789 l_header_id NUMBER := 0;
4790 l_top_model_line_id NUMBER :=0;
4791 l_model_remnant_flag VARCHAR2(1);
4792 l_store_top NUMBER :=0;
4793 l_store_rem VARCHAR2(1);
4794
4795 TYPE fulfill_set IS RECORD
4796 (
4797 line_id NUMBER := FND_API.G_MISS_NUM
4798 , top_model_line_id NUMBER := FND_API.G_MISS_NUM
4799 , set_id NUMBER := FND_API.G_MISS_NUM
4800 , model_remnant_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
4801 );
4802
4803 TYPE fulfill_set_tbl IS TABLE OF fulfill_set
4804 INDEX BY BINARY_INTEGER;
4805
4806 l_ful_set_tbl fulfill_set_tbl;
4807 l_ful_set_index NUMBER := 0;
4808
4809 l_fulfill_status VARCHAR2(1);
4810
4811 -- Added for Bug-2560644
4812 l_fulfilled_flag VARCHAR2(1);
4813 l_fulfillment_activity VARCHAR2(30);
4814 l_fulfillment_type VARCHAR2(30);
4815
4816 --
4817 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4818 --
4819 BEGIN
4820
4821 IF l_debug_level > 0 THEN
4822 oe_debug_pub.add( 'ENTERING OE_LINE_FULLFILL.CANCEL_LINE : '||TO_CHAR ( P_LINE_ID ) , 1 ) ;
4823 END IF;
4824
4825
4826
4827 -- Check if being called for child line
4828
4829 SELECT TOP_MODEL_LINE_ID,
4830 MODEL_REMNANT_FLAG
4831 INTO l_store_top,
4832 l_store_rem
4833 FROM OE_ORDER_LINES
4834 WHERE LINE_ID = p_line_id;
4835
4836
4837 IF l_store_top IS NOT NULL AND
4838 l_store_top <> p_line_id AND
4839 nvl(l_store_rem,'N') = 'N' THEN
4840
4841 IF l_debug_level > 0 THEN
4842 oe_debug_pub.add( 'CALLED FOR CHILD ' , 3 ) ;
4843 END IF;
4844 GOTO LINE_NOT_IN_SET;
4845
4846 END IF;
4847
4848 /* 2525203 start */
4849
4850
4851 IF p_set_id IS NOT NULL THEN
4852
4853 select count(*)
4854 into l_howmany
4855 from oe_line_sets l,
4856 oe_sets s
4857 where l.line_id = p_line_id
4858 and l.set_id = s.set_id
4859 and s.set_id <> p_set_id
4860 and s.set_type = 'FULFILLMENT_SET';
4861 IF l_howmany > 0 THEN
4862 l_set_id := p_set_id;
4863 ELSE
4864 l_set_id := NULL; -- p_line_id is in only one fulfillment set, p_set_id
4865 END IF;
4866
4867 ELSE
4868 l_set_id := NULL;
4869 END IF;
4870
4871 IF l_debug_level > 0 THEN
4872 oe_debug_pub.add( 'L_SET_ID :'||TO_CHAR ( L_SET_ID ) ||'.' , 5 ) ;
4873 END IF;
4874
4875 IF l_set_id IS NOT NULL THEN
4876
4877 /* check if there is a line other than p_line_id which is a member of both
4878 p_set_id and another fulfillment set */
4879
4880 select count(*) -- added for 2525203
4881 into l_howmany
4882 from oe_line_sets l1,
4883 oe_line_sets l2,
4884 oe_sets s1,
4885 oe_order_lines ol
4886 where l1.line_id <> p_line_id
4887 and l1.set_id = s1.set_id
4888 and s1.set_type = 'FULFILLMENT_SET'
4889 and s1.set_id <> l_set_id
4890 and l2.line_id = l1.line_id
4891 and l1.line_id = ol.line_id
4892 and (ol.top_model_line_id is null or
4893 ol.top_model_line_id = ol.line_id or
4894 nvl(ol.model_remnant_flag, 'N') = 'Y')
4895 and l2.set_id = l_set_id;
4896 IF l_howmany > 0 THEN
4897 l_shared_lines := TRUE;
4898 IF l_debug_level > 0 THEN
4899 oe_debug_pub.add( 'L_SHARED_LINES IS TRUE' , 5 ) ;
4900 END IF;
4901 END IF;
4902
4903
4904 /* check if there is another line which is with p_line_id in l_set_id and at least one other
4905 fulfillment set */
4906 select count(*)
4907 into l_common_sets
4908 from oe_line_sets l1,
4909 oe_line_sets l2,
4910 oe_line_sets l3,
4911 oe_sets s1,
4912 oe_order_lines ol
4913 where l1.line_id = p_line_id
4914 and l1.set_id = s1.set_id
4915 and s1.set_type = 'FULFILLMENT_SET'
4916 and s1.set_id <> l_set_id
4917 and l2.set_id = s1.set_id
4918 and l2.line_id = ol.line_id
4919 and (ol.top_model_line_id is null or
4920 ol.top_model_line_id = ol.line_id or
4921 nvl(ol.model_remnant_flag, 'N') = 'Y')
4922 and l2.line_id <> l1.line_id
4923 and l2.line_id = l3.line_id
4924 and l3.set_id = l_set_id;
4925
4926 END IF;
4927
4928 IF l_debug_level > 0 THEN
4929 oe_debug_pub.add( 'L_COMMON_SETS :'||TO_CHAR ( L_COMMON_SETS ) ||'.' , 5 ) ;
4930 END IF;
4931
4932 /* 2525203 end */
4933
4934 IF l_debug_level > 0 THEN
4935 oe_debug_pub.add( 'GET THE FULFILLMENT SET CALLING GET FULFILLMENT SET : ' , 3 ) ;
4936 END IF;
4937
4938 g_set_tbl.delete;
4939
4940 Get_Fulfillment_Set
4941 (
4942 p_line_id => p_line_id
4943 , x_return_status => l_return_status
4944 , x_set_tbl => l_set_tbl
4945 );
4946
4947 IF l_debug_level > 0 THEN
4948 oe_debug_pub.add( 'RETURN STATUS FROM GET FULFILLMENT SET : '||L_RETURN_STATUS||'/'||TO_CHAR ( L_SET_TBL.COUNT ) , 3 ) ;
4949 END IF;
4950
4951 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4953 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4954 RAISE FND_API.G_EXC_ERROR;
4955 END IF;
4956
4957 IF l_set_tbl.count > 0 THEN
4958 NULL;
4959 ELSE
4960 GOTO LINE_NOT_IN_SET;
4961 END IF;
4962
4963
4964
4965 --Prepare a table for MODEL/Standard to be processed.
4966
4967 FOR l_set_index IN l_set_tbl.FIRST .. l_set_tbl.LAST
4968 LOOP
4969
4970 IF l_debug_level > 0 THEN
4971 oe_debug_pub.add( 'LOOPING FOR LINE_ID :'||L_SET_TBL ( L_SET_INDEX ) .LINE_ID , 3 ) ;
4972 END IF;
4973
4974
4975 SELECT TOP_MODEL_LINE_ID,
4976 MODEL_REMNANT_FLAG
4977 INTO l_top_model_line_id,
4978 l_model_remnant_flag
4979 FROM OE_ORDER_LINES
4980 WHERE LINE_ID = l_set_tbl(l_set_index).line_id;
4981
4982 IF l_top_model_line_id IS NULL OR
4983 l_top_model_line_id = l_set_tbl(l_set_index).line_id OR
4984 nvl(l_model_remnant_flag,'N') = 'Y' THEN
4985 -- Added For bug#2965878 Begin
4986 /* Not needed with MOAC
4987 j := 1.0;
4988 initial := 1.0;
4989 nextpos := INSTR(OE_OE_FORM_CANCEL_LINE.g_record_ids,',',1,j) ;
4990 */
4991 set_flag := 'N';
4992
4993
4994 FOR i IN 1..OE_OE_FORM_CANCEL_LINE.g_num_of_records LOOP
4995 IF l_debug_level > 0 THEN
4996 OE_DEBUG_PUB.Add('Number Of records'||to_char(OE_OE_FORM_CANCEL_LINE.g_num_of_records),1);
4997 END IF;
4998 --MOAC
4999 --t_line_id := to_number(substr(OE_OE_FORM_CANCEL_LINE.g_record_ids,initial, nextpos-initial));
5000 t_line_id := OE_OE_FORM_CANCEL_LINE.g_record_ids(i).id1;
5001
5002 IF l_debug_level > 0 THEN
5003 OE_DEBUG_PUB.Add('Line Id: '||to_char(t_line_id),1);
5004 END IF;
5005 /* Not needed with MOAC
5006 initial := nextpos + 1.0;
5007 j := j + 1.0;
5008 nextpos := INSTR(OE_OE_FORM_CANCEL_LINE.g_record_ids,',',1,j) ;
5009 */
5010
5011 IF t_line_id = l_set_tbl(l_set_index).line_id THEN
5012 set_flag := 'Y';
5013 exit;
5014 END IF;
5015 END LOOP;
5016 IF set_flag = 'Y' THEN
5017 IF l_debug_level > 0 THEN
5018 oe_debug_pub.add( 'LINE IS GETTING CANCELLED,NOT ADDED TO TABLE : '||L_SET_TBL ( L_SET_INDEX ) .LINE_ID , 3 ) ;
5019 END IF;
5020 NULL;
5021 ELSE -- Added For bug#2965878 End
5022 IF l_debug_level > 0 THEN
5023 oe_debug_pub.add( 'ADD THE LINE TO TABLE/REM : '||L_SET_TBL ( L_SET_INDEX ) .LINE_ID||'/'||L_MODEL_REMNANT_FLAG , 3 ) ;
5024 END IF;
5025 l_ful_set_index := l_ful_set_index + 1;
5026 l_ful_set_tbl(l_ful_set_index).line_id := l_set_tbl(l_set_index).line_id;
5027 l_ful_set_tbl(l_ful_set_index).set_id := l_set_tbl(l_set_index).set_id;
5028 l_ful_set_tbl(l_ful_set_index).top_model_line_id := l_top_model_line_id;
5029 l_ful_set_tbl(l_ful_set_index).model_remnant_flag := l_model_remnant_flag;
5030 IF l_debug_level > 0 THEN
5031 oe_debug_pub.add( 'INDEX : '||L_FUL_SET_INDEX , 3 ) ;
5032 END IF;
5033 END IF;
5034 ELSE
5035 IF l_debug_level > 0 THEN
5036 oe_debug_pub.add( 'LINE NOT ADDED TO TABLE : '||L_SET_TBL ( L_SET_INDEX ) .LINE_ID , 3 ) ;
5037 END IF;
5038
5039 END IF;
5040
5041 END LOOP;
5042
5043 IF l_debug_level > 0 THEN
5044 oe_debug_pub.add( 'COUNT '||L_FUL_SET_TBL.COUNT , 3 ) ;
5045 END IF;
5046
5047 IF L_FUL_SET_TBL.COUNT >0 THEN -- Added For bug#2965878
5048 FOR l_ful_set_index IN l_ful_set_tbl.FIRST .. l_ful_set_tbl.LAST
5049 LOOP
5050
5051 IF l_debug_level > 0 THEN
5052 oe_debug_pub.add( 'PROCESSING LINE : '||TO_CHAR ( L_FUL_SET_TBL ( L_FUL_SET_INDEX ) .LINE_ID ) , 3 ) ;
5053 END IF;
5054
5055 -- Check for the passed line. If the line is at FULFILL_LINE work
5056 -- flow activity, then the fulfillment processing for this line
5057 -- should take place.
5058
5059
5060 l_set_id_mod:=MOD(l_ful_set_tbl(l_ful_set_index).set_id,G_BINARY_LIMIT);--7827727
5061 --IF NOT l_close_tbl.EXISTS(l_ful_set_tbl(l_ful_set_index).set_id) THEN
5062 IF NOT l_close_tbl.EXISTS(l_set_id_mod) THEN --7827727
5063
5064 IF l_debug_level > 0 THEN
5065 oe_debug_pub.add( 'SET ID ADDED : '||TO_CHAR ( L_FUL_SET_TBL ( L_FUL_SET_INDEX ) .SET_ID ) , 3 ) ;
5066 END IF;
5067 --l_close_tbl(l_ful_set_tbl(l_ful_set_index).set_id) := l_ful_set_tbl(l_ful_set_index).set_id;
5068 l_close_tbl(l_set_id_mod) :=l_ful_set_tbl(l_ful_set_index).set_id;--7827727
5069
5070 END IF;
5071
5072 IF l_ful_set_tbl(l_ful_set_index).top_model_line_id is NOT NULL AND
5073 nvl(l_ful_set_tbl(l_ful_set_index).model_remnant_flag,'N') = 'N' THEN
5074
5075 Check_PTO_KIT_Fulfillment
5076 (
5077 p_top_model_line_id => l_ful_set_tbl(l_ful_set_index).top_model_line_id,
5078 x_return_status => l_return_status,
5079 x_fulfill_status => l_fulfill_status
5080 );
5081
5082 IF l_debug_level > 0 THEN
5083 oe_debug_pub.add( 'RETURN FROM CHECK_PTO_KIT_FULFILLMENT '||L_RETURN_STATUS||'/'||L_FULFILL_STATUS , 3 ) ;
5084 END IF;
5085 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5087
5088 END IF;
5089
5090 IF l_fulfill_status = FND_API.G_FALSE THEN
5091 IF l_ful_set_tbl(l_ful_set_index).line_id = p_line_id THEN
5092 l_process_current_line := FALSE;
5093 IF l_debug_level > 0 THEN
5094 oe_debug_pub.add( '1- DO NOT PROCESS CURRENT LINE '||L_FUL_SET_TBL ( L_FUL_SET_INDEX ) .LINE_ID , 3 ) ;
5095 END IF;
5096
5097 IF l_set_id IS NOT NULL THEN -- 2525203
5098 l_complete_fulfillment := FALSE;
5099 l_oth_f := FALSE;
5100 IF l_common_sets > 0 THEN
5101 l_set_f := FALSE;
5102 END IF;
5103 END IF; -- 2525203 end
5104
5105 GOTO END_SET_LOOP;
5106 ELSE
5107 l_complete_fulfillment := FALSE;
5108
5109 IF l_ful_set_tbl(l_ful_set_index).set_id = l_set_id THEN -- 2525203
5110 l_set_f := FALSE;
5111 ELSIF l_set_id IS NOT NULL THEN
5112 l_oth_f := FALSE;
5113 END IF; -- 2525203 end
5114
5115 IF l_set_id IS NULL OR -- GOTO made conditional for 2525203
5116 (NOT l_set_f AND NOT l_oth_f) THEN
5117 GOTO END_CANCEL_PROCESS;
5118 END IF;
5119 END IF;
5120 END IF;
5121
5122 ELSE
5123
5124 Get_Activity_Result
5125 (
5126 p_item_type => OE_GLOBALS.G_WFI_LIN
5127 , p_item_key => to_char(l_ful_set_tbl(l_ful_set_index).line_id)
5128 , p_activity_name => 'FULFILL_LINE'
5129 , x_return_status => l_return_status
5130 , x_activity_result => l_activity_result
5131 , x_activity_status_code => l_activity_status
5132 , x_activity_id => l_activity_id
5133 );
5134
5135 IF l_debug_level > 0 THEN
5136 oe_debug_pub.add( 'RETURN STATUS FROM GET ACTIVITY RESULT : '||L_RETURN_STATUS , 3 ) ;
5137 END IF;
5138
5139 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5141 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR
5142 OR
5143 NVL(l_activity_status, 'COMPLETE') <> 'NOTIFIED' )
5144 THEN
5145 IF l_ful_set_tbl(l_ful_set_index).line_id = p_line_id THEN
5146 l_process_current_line := FALSE;
5147 IF l_debug_level > 0 THEN
5148 oe_debug_pub.add( '2- DO NOT PROCESS CURRENT LINE ' , 3 ) ;
5149 END IF;
5150
5151 IF l_set_id IS NOT NULL THEN -- 2525203
5152 l_complete_fulfillment := FALSE;
5153 l_oth_f := FALSE;
5154 IF l_common_sets > 0 THEN
5155 l_set_f := FALSE;
5156 END IF;
5157 END IF; -- 2525203 end
5158
5159 GOTO END_SET_LOOP;
5160 ELSE
5161 l_complete_fulfillment := FALSE;
5162
5163 IF l_ful_set_tbl(l_ful_set_index).set_id = l_set_id THEN -- 2525203
5164 l_set_f := FALSE;
5165 ELSIF l_set_id IS NOT NULL THEN
5166 l_oth_f := FALSE;
5167 END IF; -- 2525203 end
5168
5169 IF l_set_id IS NULL OR -- GOTO made conditional for 2525203
5170 (NOT l_set_f AND NOT l_oth_f) THEN
5171 GOTO END_CANCEL_PROCESS;
5172 END IF;
5173 END IF;
5174 END IF;
5175
5176 END IF;
5177
5178 << END_SET_LOOP >>
5179 NULL;
5180 /* Debugging statements added for 2525203 */
5181 IF l_debug_level > 0 THEN
5182 oe_debug_pub.add( 'FLAGS:' , 5 ) ;
5183 END IF;
5184 IF l_complete_fulfillment THEN
5185 IF l_debug_level > 0 THEN
5186 oe_debug_pub.add( 'L_COMPLETE_FULFILLMENT IS TRUE' , 5 ) ;
5187 END IF;
5188 ELSE
5189 IF l_debug_level > 0 THEN
5190 oe_debug_pub.add( 'L_COMPLETE_FULFILLMENT IS FALSE' , 5 ) ;
5191 END IF;
5192 END IF;
5193 IF l_set_f THEN
5194 IF l_debug_level > 0 THEN
5195 oe_debug_pub.add( 'L_SET_F IS TRUE' , 5 ) ;
5196 END IF;
5197 ELSE
5198 IF l_debug_level > 0 THEN
5199 oe_debug_pub.add( 'L_SET_F IS FALSE' , 5 ) ;
5200 END IF;
5201 END IF;
5202 IF l_oth_f THEN
5203 IF l_debug_level > 0 THEN
5204 oe_debug_pub.add( 'L_OTH_F IS TRUE' , 5 ) ;
5205 END IF;
5206 ELSE
5207 IF l_debug_level > 0 THEN
5208 oe_debug_pub.add( 'L_OTH_F IS FALSE' , 5 ) ;
5209 END IF;
5210 END IF;
5211 IF l_process_current_line THEN
5212 IF l_debug_level > 0 THEN
5213 oe_debug_pub.add( 'L_PROCESS_CURRENT_LINE IS TRUE' , 5 ) ;
5214 END IF;
5215 ELSE
5216 IF l_debug_level > 0 THEN
5217 oe_debug_pub.add( 'L_PROCESS_CURRENT_LINE IS FALSE' , 5 ) ;
5218 END IF;
5219 END IF;
5220 /* end 2525203 */
5221
5222 END LOOP;
5223 END IF; -- Count of l_ful_set_tbl is 0
5224
5225 << END_CANCEL_PROCESS >>
5226
5227 IF l_shared_lines AND -- added for 2525203
5228 (NOT l_set_f OR NOT l_oth_f) THEN
5229 l_set_f := FALSE;
5230 l_oth_f := FALSE;
5231 END IF;
5232
5233 IF l_complete_fulfillment OR -- OR added for 2525203
5234 (l_set_id IS NOT NULL AND (l_set_f OR l_oth_f)) THEN
5235
5236 IF l_debug_level > 0 THEN
5237 oe_debug_pub.add( 'FULFILL SOME LINES <> P_LINE_ID: ' , 3 ) ;
5238 END IF;
5239
5240 l_fulfilled_lines.DELETE; -- 2525203
5241 IF L_FUL_SET_TBL.COUNT >0 THEN -- Added For bug#2965878
5242 FOR l_ful_set_index IN l_ful_set_tbl.FIRST .. l_ful_set_tbl.LAST
5243 LOOP
5244 IF l_ful_set_tbl(l_ful_set_index).line_id <> p_line_id AND -- AND added for 2525203
5245 (l_complete_fulfillment OR
5246 l_set_f AND l_ful_set_tbl(l_ful_set_index).set_id = l_set_id OR
5247 l_oth_f AND l_ful_set_tbl(l_ful_set_index).set_id <> l_set_id) AND
5248 NOT l_fulfilled_lines.EXISTS(MOD(l_ful_set_tbl(l_ful_set_index).line_id,G_BINARY_LIMIT)) THEN --7827727
5249 --NOT l_fulfilled_lines.EXISTS(l_ful_set_tbl(l_ful_set_index).line_id) THEN
5250
5251 --l_fulfilled_lines(l_ful_set_tbl(l_ful_set_index).line_id) := 'Y'; -- 2525203
5252 l_fulfilled_lines(MOD(l_ful_set_tbl(l_ful_set_index).line_id,G_BINARY_LIMIT)) :='Y';--2525203 --7827727
5253
5254 IF l_ful_set_tbl(l_ful_set_index).line_id = l_ful_set_tbl(l_ful_set_index).top_model_line_id AND
5255 nvl(l_ful_set_tbl(l_ful_set_index).model_remnant_flag,'N') = 'N' THEN
5256
5257 IF l_debug_level > 0 THEN
5258 oe_debug_pub.add( 'CALLING FULFILL_PTO_KIT '||L_FUL_SET_TBL ( L_FUL_SET_INDEX ) .TOP_MODEL_LINE_ID , 3 ) ;
5259 END IF;
5260
5261 Fulfill_PTO_KIT
5262 (
5263 p_top_model_line_id => l_ful_set_tbl(l_ful_set_index).top_model_line_id,
5264 x_return_status => l_return_status
5265 );
5266
5267 IF l_debug_level > 0 THEN
5268 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL_PTO_KIT API '||L_RETURN_STATUS , 3 ) ;
5269 END IF;
5270
5271 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5273 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5274 RAISE FND_API.G_EXC_ERROR;
5275 END IF;
5276
5277 ELSE
5278 /* Fix for 2560644 */
5279
5280 SELECT fulfilled_flag
5281 INTO l_fulfilled_flag
5282 FROM OE_ORDER_LINES
5283 WHERE LINE_ID = l_ful_set_tbl(l_ful_set_index).line_id;
5284
5285 /* If the line is not fulfilled set the fulfillment related
5286 attributes for the line before it goes beyond FULFILL_LINE */
5287
5288 IF l_debug_level > 0 THEN
5289 oe_debug_pub.add( '..512..FULFILLED FLAG '||L_FULFILLED_FLAG , 5 ) ;
5290 END IF;
5291
5292 IF nvl(l_fulfilled_flag,'N') <> 'Y' THEN
5293
5294 l_fulfillment_type := G_FULFILL_NO_ACTIVITY;
5295 l_fulfillment_activity := 'NO_ACTIVITY';
5296
5297 OE_Line_Util.Query_Row(p_line_id => l_ful_set_tbl(l_ful_set_index).line_id,
5298 x_line_rec => l_line_rec);
5299 Fulfill_Line
5300 (
5301 p_line_rec => l_line_rec,
5302 p_mode => 'RECORD',
5303 p_fulfillment_type => l_fulfillment_type,
5304 p_fulfillment_activity => l_fulfillment_activity,
5305 x_return_status => l_return_status
5306 );
5307
5308 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5310 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5311 RAISE FND_API.G_EXC_ERROR;
5312 END IF;
5313
5314 --END IF; /* Commented for bug 2965878 */
5315
5316 --bug3292817: shippable line in a fulfillment set
5317 --must get closed
5318 END IF; /*bug3292817*/
5319
5320 --bug3292817 start
5321 l_activity_status := 'NOTIFIED';
5322 IF OE_OE_FORM_CANCEL_LINE.g_num_of_records > 1 THEN
5323 Get_Activity_Result
5324 ( p_item_type => OE_GLOBALS.G_WFI_LIN
5325 ,p_item_key =>
5326 to_char(l_ful_set_tbl(l_ful_set_index).line_id)
5327 ,p_activity_name => 'FULFILL_LINE'
5328 ,x_return_status => l_return_status
5329 ,x_activity_result => l_activity_result
5330 ,x_activity_status_code => l_activity_status
5331 ,x_activity_id => l_activity_id
5332 );
5333 END IF;
5334 --bug3292817 end
5335
5336 IF NVL(l_activity_status,'NOTIFIED') <> 'COMPLETE' THEN
5337 /* bug3292817 */
5338 IF l_debug_level > 0 THEN
5339 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
5340 END IF;
5341
5342 OE_Order_WF_Util.Update_Flow_Status_Code
5343 (p_line_id => l_ful_set_tbl(l_ful_set_index).line_Id,
5344 p_flow_status_code => 'FULFILLED',
5345 x_return_status => l_return_status
5346 );
5347
5348 IF l_debug_level > 0 THEN
5349 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
5350 END IF;
5351
5352 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5354 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5355 RAISE FND_API.G_EXC_ERROR;
5356 END IF;
5357
5358 IF l_debug_level > 0 THEN
5359 oe_debug_pub.add( 'FULFILL ASSOCIATED SERVICE LINES ' , 3 ) ;
5360 END IF;
5361
5362 Fulfill_Service_Lines
5363 (
5364 p_line_id => l_ful_set_tbl(l_ful_set_index).line_id,
5365 x_return_status => l_return_status
5366 );
5367
5368 IF l_debug_level > 0 THEN
5369 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL SERVICE LINES API '||L_RETURN_STATUS , 3 ) ;
5370 END IF;
5371
5372 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5374 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5375 RAISE FND_API.G_EXC_ERROR;
5376 END IF;
5377
5378 IF l_debug_level > 0 THEN
5379 oe_debug_pub.add( 'CALLING WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( L_FUL_SET_TBL ( L_FUL_SET_INDEX ) .LINE_ID ) , 3 ) ;
5380 END IF;
5381 BEGIN
5382 wf_engine.CompleteActivityInternalName('OEOL', to_char(l_ful_set_tbl(l_ful_set_index).line_id), 'FULFILL_LINE', '#NULL',TRUE ); --15870313
5383 EXCEPTION
5384 WHEN OTHERS THEN
5385 oe_debug_pub.add('Workflow complete activity failed. Raising');
5386 RAISE ;
5387 END ;
5388 IF l_debug_level > 0 THEN
5389 oe_debug_pub.add( 'RETURNED FROM WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( L_FUL_SET_TBL ( L_FUL_SET_INDEX ) .LINE_ID ) , 3 ) ;
5390 END IF;
5391
5392 END IF; --Line fulfilled
5393 END IF;
5394 END IF; /*Top Model */
5395 END LOOP;
5396
5397 END IF; --Count of l_ful_set_tbl is 0
5398 -- Update the set status in oe_sets to closed.
5399 l_close_tbl_index := l_close_tbl.FIRST;
5400
5401 WHILE l_close_tbl_index IS NOT NULL
5402 LOOP
5403 IF l_complete_fulfillment OR -- This IF added for 2525203
5404 l_set_f AND l_close_tbl(l_close_tbl_index) = l_set_id OR
5405 l_oth_f AND l_close_tbl(l_close_tbl_index) <> l_set_id THEN
5406 -- 3772947
5407 UPDATE OE_SETS
5408 SET SET_STATUS = 'C',
5409 UPDATE_DATE = SYSDATE
5410 WHERE SET_ID = l_close_tbl(l_close_tbl_index);
5411
5412 IF l_debug_level > 0 THEN
5413 oe_debug_pub.add( 'SET IS CLOSED : '||TO_CHAR ( L_CLOSE_TBL ( L_CLOSE_TBL_INDEX ) ) , 3 ) ;
5414 END IF;
5415 END IF; -- end 2525203
5416 l_close_tbl_index := l_close_tbl.NEXT(l_close_tbl_index);
5417
5418 OE_Set_util.g_set_rec.set_status := 'C'; -- 4080531
5419
5420 END LOOP;
5421
5422 END IF;
5423
5424 /* following IF for Bug2068310. */
5425 IF p_fulfill_operation = 'N' THEN
5426 IF l_debug_level > 0 THEN
5427 oe_debug_pub.add( 'CURRENT LINE IS GETTING DELETED OR CANCELLED , DONOT PROCESS ' , 3 ) ;
5428 END IF;
5429 l_process_current_line := FALSE;
5430 END IF;
5431
5432 IF l_process_current_line AND -- AND added for 2525203
5433 (l_set_id IS NULL OR l_oth_f) THEN
5434
5435 IF l_debug_level > 0 THEN
5436 oe_debug_pub.add( 'PROCESSING CURRENT LINE' , 3 ) ;
5437 END IF;
5438
5439 IF l_store_top IS NOT NULL AND
5440 l_store_top = p_line_id AND
5441 nvl(l_store_rem,'N') = 'N' THEN
5442
5443 IF l_debug_level > 0 THEN
5444 oe_debug_pub.add( 'CALLING CHECK_PTO_KIT_FULFILLMENT ' , 3 ) ;
5445 END IF;
5446
5447 Check_PTO_KIT_Fulfillment
5448 (
5449 p_top_model_line_id => p_line_id,
5450
5451 x_return_status => l_return_status,
5452 x_fulfill_status => l_fulfill_status
5453 );
5454
5455 IF l_debug_level > 0 THEN
5456 oe_debug_pub.add( 'RETURN FROM CHECK_PTO_KIT_FULFILLMENT '||L_RETURN_STATUS||'/'||L_FULFILL_STATUS , 3 ) ;
5457 END IF;
5458
5459 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5461
5462 END IF;
5463
5464 IF l_fulfill_status = FND_API.G_FALSE THEN
5465
5466 IF l_debug_level > 0 THEN
5467 oe_debug_pub.add( 'LINE CAN NOT BE FULFILLED ' , 3 ) ;
5468 END IF;
5469 GOTO LINE_NOT_IN_SET;
5470
5471 ELSE
5472
5473 IF l_debug_level > 0 THEN
5474 oe_debug_pub.add( 'CALLING FULFILL_PTO_KIT '||P_LINE_ID , 3 ) ;
5475 END IF;
5476
5477 Fulfill_PTO_KIT
5478 (
5479 p_top_model_line_id => p_line_id,
5480 x_return_status => l_return_status
5481 );
5482
5483 IF l_debug_level > 0 THEN
5484 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL_PTO_KIT API '||L_RETURN_STATUS , 3 ) ;
5485 END IF;
5486
5487 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5489 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5490 RAISE FND_API.G_EXC_ERROR;
5491 END IF;
5492
5493 END IF;
5494
5495 ELSE
5496
5497 Get_Activity_Result
5498 (
5499 p_item_type => OE_GLOBALS.G_WFI_LIN
5500 , p_item_key => to_char(p_line_id)
5501 , p_activity_name => 'FULFILL_LINE'
5502 , x_return_status => l_return_status
5503 , x_activity_result => l_activity_result
5504 , x_activity_status_code => l_activity_status
5505 , x_activity_id => l_activity_id
5506 );
5507
5508 IF l_debug_level > 0 THEN
5509 oe_debug_pub.add( 'RETURN STATUS FROM GET ACTIVITY RESULT : '||L_RETURN_STATUS , 3 ) ;
5510 END IF;
5511
5512 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5514 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5515 IF l_debug_level > 0 THEN
5516 oe_debug_pub.add( 'DO NOT PROCESS CURRENT LINE ' , 3 ) ;
5517 END IF;
5518 GOTO LINE_NOT_IN_SET;
5519 END IF;
5520
5521 /* Fix for 2560644 */
5522
5523 SELECT fulfilled_flag
5524 INTO l_fulfilled_flag
5525 FROM OE_ORDER_LINES
5526 WHERE LINE_ID = p_line_id;
5527
5528 /* If the line is not fulfilled set the fulfillment related
5529 attributes for the line before it goes beyond FULFILL_LINE */
5530
5531 IF l_debug_level > 0 THEN
5532 oe_debug_pub.add( '..515..FULFILLED FLAG '||L_FULFILLED_FLAG , 5 ) ;
5533 END IF;
5534 IF nvl(l_fulfilled_flag,'N') <> 'Y' THEN
5535
5536 l_fulfillment_type := G_FULFILL_NO_ACTIVITY;
5537 l_fulfillment_activity := 'NO_ACTIVITY';
5538
5539 OE_Line_Util.Query_Row(p_line_id => p_line_id,
5540 x_line_rec => l_line_rec);
5541
5542 Fulfill_Line
5543 (
5544 p_line_rec => l_line_rec,
5545 p_mode => 'RECORD',
5546 p_fulfillment_type => l_fulfillment_type,
5547 p_fulfillment_activity => l_fulfillment_activity,
5548 x_return_status => l_return_status
5549 );
5550
5551 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5553 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5554 RAISE FND_API.G_EXC_ERROR;
5555 END IF;
5556
5557 END IF;
5558
5559 IF l_debug_level > 0 THEN
5560 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
5561 END IF;
5562
5563 -- l_line_rec := OE_Line_Util.Query_Row(p_line_id);
5564 /*
5565 SELECT HEADER_ID
5566 INTO l_header_id
5567 FROM OE_ORDER_LINES
5568 WHERE LINE_ID = p_line_id;
5569 */
5570 OE_Order_WF_Util.Update_Flow_Status_Code
5571 (p_line_id => p_line_Id,
5572 p_flow_status_code => 'FULFILLED',
5573 x_return_status => l_return_status
5574 );
5575
5576
5577 IF l_debug_level > 0 THEN
5578 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
5579 END IF;
5580
5581 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5583 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5584 RAISE FND_API.G_EXC_ERROR;
5585 END IF;
5586
5587 IF l_debug_level > 0 THEN
5588 oe_debug_pub.add( 'FULFILL ASSOCIATED SERVICE LINES ' , 3 ) ;
5589 END IF;
5590
5591
5592 Fulfill_Service_Lines
5593 (
5594 p_line_id => p_line_id,
5595 x_return_status => l_return_status
5596 );
5597
5598 IF l_debug_level > 0 THEN
5599 oe_debug_pub.add( 'RETURN STATUS FROM FULFILL SERVICE LINES API '||L_RETURN_STATUS , 3 ) ;
5600 END IF;
5601
5602 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5604 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5605 RAISE FND_API.G_EXC_ERROR;
5606 END IF;
5607
5608 IF l_debug_level > 0 THEN
5609 oe_debug_pub.add( 'CALLING WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( P_LINE_ID ) , 3 ) ;
5610 END IF;
5611 BEGIN
5612 wf_engine.CompleteActivityInternalName('OEOL', to_char(p_line_id), 'FULFILL_LINE', '#NULL',TRUE ); --15870313
5613 EXCEPTION
5614 WHEN OTHERS THEN
5615 oe_debug_pub.add('Workflow complete activity failed. Raising');
5616 RAISE ;
5617 END ;
5618 IF l_debug_level > 0 THEN
5619 oe_debug_pub.add( 'RETURNED FROM WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( P_LINE_ID ) , 3 ) ;
5620 END IF;
5621
5622 END IF;
5623
5624 END IF;
5625
5626
5627 << LINE_NOT_IN_SET >>
5628
5629 x_return_status := FND_API.G_RET_STS_SUCCESS;
5630 IF l_debug_level > 0 THEN
5631 oe_debug_pub.add( 'EXITING OE_LINE_FULLFILL.CANCEL_LINE : '||X_RETURN_STATUS , 1 ) ;
5632 END IF;
5633
5634 EXCEPTION
5635 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5636 IF l_debug_level > 0 THEN
5637 oe_debug_pub.add( 'CANCEL_LINE : EXITING WITH UNEXPECTED ERROR'||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
5638 END IF;
5639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5640
5641 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5642 THEN
5643 OE_MSG_PUB.Add_Exc_Msg
5644 ( G_PKG_NAME,
5645 'Cancel_Line'
5646 );
5647 END IF;
5648 G_DEBUG_MSG := G_DEBUG_MSG || 'E24,';
5649
5650 WHEN FND_API.G_EXC_ERROR THEN
5651 x_return_status := FND_API.G_RET_STS_ERROR;
5652
5653 WHEN OTHERS THEN
5654 IF l_debug_level > 0 THEN
5655 oe_debug_pub.add( 'CANCEL_LINE : EXITING WITH OTHERS ERROR' , 1 ) ;
5656 END IF;
5657 IF l_debug_level > 0 THEN
5658 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
5659 END IF;
5660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5661
5662 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5663 THEN
5664 OE_MSG_PUB.Add_Exc_Msg
5665 ( G_PKG_NAME
5666 , 'Cancel_Line'
5667 );
5668 END IF;
5669 G_DEBUG_MSG := G_DEBUG_MSG || 'E25,';
5670
5671 END Cancel_Line;
5672
5673 END OE_LINE_FULLFILL;
5674