1 PACKAGE BODY OE_GROUP_SCH_UTIL AS
2 /* $Header: OEXUGRPB.pls 120.21.12000000.6 2007/08/31 07:17:03 sgoli ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_GROUP_SCH_UTIL';
5 G_TOP_MODEL_LINE_ID NUMBER;
6 G_PART_OF_SET BOOLEAN;
7
8 /*---------------------------------------------------------------------
9 Procedure Name : Validate_Item_Warehouse
10 Description : This API will be called to check valid Item and warehouse
11 combinition
12 --------------------------------------------------------------------- */
13 Procedure Validate_Item_Warehouse
14 (p_inventory_item_id IN NUMBER,
15 p_ship_from_org_id IN NUMBER,
16 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
17 IS
18 l_validate_combinition NUMBER;
19 --
20 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
21 --
22 BEGIN
23 IF l_debug_level > 0 THEN
24 oe_debug_pub.add( 'ENTERING OE_GROUP_SCH_UTIL.VALIDATE_ITEM_WAREHOUSE' , 2 ) ;
25 END IF;
26 -- bug 3594016
27 IF (p_inventory_item_id is NOT NULL
28 AND p_ship_from_org_id is NOT NULL)
29 THEN
30 SELECT 1
31 INTO l_validate_combinition
32 FROM mtl_system_items_b msi,
33 org_organization_definitions org
34 WHERE msi.inventory_item_id= p_inventory_item_id
35 AND org.organization_id=msi.organization_id
36 AND sysdate<=nvl(org.disable_date,sysdate)
37 AND org.organization_id=p_ship_from_org_id
38 AND rownum=1 ;
39 END IF;
40
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42 EXCEPTION
43 WHEN NO_DATA_FOUND THEN
44 IF l_debug_level > 0 THEN
45 oe_debug_pub.add('INVALID ITEM WAREHOUSE COMBINATION',3);
46 END IF;
47 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ITEM_WHSE');
48 OE_MSG_PUB.Add;
49 x_return_status := FND_API.G_RET_STS_ERROR;
50 END Validate_Item_Warehouse;
51
52 /*---------------------------------------------------------------------
53 Procedure Name : Validate_group_Line
54 Description : Validates a line before scheduling.
55 This API will be called only when line is getting
56 scheduled or reserved.
57 IF the profile OM:Schedule Line on Hold is set to 'Y'
58 we will perform scheduling on lines on hold. If it is
59 set to 'N', we will not perform scheduling.
60 --------------------------------------------------------------------- */
61 Procedure Validate_Group_Line
62 (p_line_rec IN OE_ORDER_PUB.Line_Rec_Type,
63 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
64 IS
65 l_msg_count NUMBER;
66 l_msg_data VARCHAR2(2000);
67 l_result VARCHAR2(30);
68 l_scheduling_level_code VARCHAR2(30) := NULL;
69 --
70 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
71 --
72 BEGIN
73
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75 IF l_debug_level > 0 THEN
76 oe_debug_pub.add( '..ENTERING OE_SCHEDULE_UTIL.VALIDATE_GROUP_LINE' , 6 ) ;
77 END IF;
78
79 -- If the line is shipped, scheduling is not allowed.
80
81 IF p_line_rec.shipped_quantity is not null AND
82 p_line_rec.shipped_quantity <> FND_API.G_MISS_NUM THEN
83
84 -- The line is cancelled. Cannot perform scheduling
85 -- on it.
86
87 FND_MESSAGE.SET_NAME('ONT','OE_SCH_LINE_SHIPPED');
88 OE_MSG_PUB.Add;
89
90 IF l_debug_level > 0 THEN
91 oe_debug_pub.add( ' SHIPPED LINE.' , 4 ) ;
92 END IF;
93 x_return_status := FND_API.G_RET_STS_ERROR;
94 RETURN;
95
96 END IF;
97
98 IF p_line_rec.schedule_status_code is NULL THEN
99
100 -- If the request_date on the line is missing or null and
101 -- if the user is trying to performing scheduling,
102 -- it is an error
103
104 IF l_debug_level > 0 THEN
105 oe_debug_pub.add( 'CHECKING THE REQUEST DATE....' , 1 ) ;
106 END IF;
107 IF (p_line_rec.request_date is null OR
108 p_line_rec.request_date = FND_API.G_MISS_DATE) THEN
109
110 FND_MESSAGE.SET_NAME('ONT','OE_SCH_MISSING_REQUEST_DATE');
111 OE_MSG_PUB.Add;
112 IF l_debug_level > 0 THEN
113 oe_debug_pub.add( 'REQUEST DATE IS MISSING' , 4 ) ;
114 END IF;
115 x_return_status := FND_API.G_RET_STS_ERROR;
116 RETURN;
117 END IF;
118
119 IF l_debug_level > 0 THEN
120 oe_debug_pub.add( 'CHECKING FOR HOLDS....' , 1 ) ;
121 END IF;
122 -- Call will be made to validate_group_line only when
123 -- line is getting scheduled due to action OESCH_ACT_SCHEDULE
124 -- or OESCH_ACT_RESERVE.
125
126 -- IF FND_PROFILE.VALUE('ONT_SCHEDULE_LINE_ON_HOLD') = 'N'
127 IF oe_sys_parameters.value ('ONT_SCHEDULE_LINE_ON_HOLD') = 'N' --moac
128 THEN
129 -- Since the profile is set to NO, we should not schedule
130 -- the line if the line is on hold.
131
132 IF l_debug_level > 0 THEN
133 oe_debug_pub.add( 'CALLING CHECK HOLDS' , 1 ) ;
134 END IF;
135
136 OE_Holds_PUB.Check_Holds
137 ( p_api_version => 1.0
138 , p_init_msg_list => FND_API.G_FALSE
139 , p_commit => FND_API.G_FALSE
140 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
141 , x_return_status => x_return_status
142 , x_msg_count => l_msg_count
143 , x_msg_data => l_msg_data
144 , p_line_id => p_line_rec.line_id
145 , p_hold_id => NULL
146 , p_entity_code => NULL
147 , p_entity_id => NULL
148 , x_result_out => l_result);
149
150 IF l_debug_level > 0 THEN
151 oe_debug_pub.add( 'AFTER CALLING CHECK HOLDS: ' || X_RETURN_STATUS , 1 ) ;
152 END IF;
153
154
155 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
156 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
157 RAISE FND_API.G_EXC_ERROR;
158 ELSE
159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
160 END IF;
161 END IF;
162
163 IF (l_result = FND_API.G_TRUE) THEN
164 FND_MESSAGE.SET_NAME('ONT','OE_SCH_LINE_ON_HOLD');
165 OE_MSG_PUB.Add;
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 IF l_debug_level > 0 THEN
168 oe_debug_pub.add( 'LINE IS ON HOLD' , 4 ) ;
169 END IF;
170 RETURN;
171 END IF;
172
173 END IF;
174 END IF;
175 -- Check to see what scheduling level is allowed to be performed
176 -- on this line. If the action requested is not allowed for the
177 -- scheduling action, error out.
178
179 IF l_debug_level > 0 THEN
180 oe_debug_pub.add( 'CHECKING SCHEDULING LEVEL...' , 1 ) ;
181 END IF;
182 l_scheduling_level_code :=
183 OE_SCHEDULE_UTIL.Get_Scheduling_Level(p_line_rec.header_id,
184 p_line_rec.line_type_id);
185
186 IF l_debug_level > 0 THEN
187 oe_debug_pub.add( 'L_SCHEDULING_LEVEL_CODE : ' || L_SCHEDULING_LEVEL_CODE , 1 ) ;
188 END IF;
189
190 IF l_scheduling_level_code is not null THEN
191 IF l_scheduling_level_code = OE_SCHEDULE_UTIL.SCH_LEVEL_ONE THEN
192
193 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ACTION_NOT_ALLOWED');
194 FND_MESSAGE.SET_TOKEN('ACTION', p_line_rec.schedule_action_code);
195 FND_MESSAGE.SET_TOKEN('ORDER_TYPE',
196 nvl(OE_SCHEDULE_UTIL.sch_cached_line_type,
197 OE_SCHEDULE_UTIL.sch_cached_order_type));
198 OE_MSG_PUB.Add;
199 x_return_status := FND_API.G_RET_STS_ERROR;
200 IF l_debug_level > 0 THEN
201 oe_debug_pub.add( 'LINE TYPE IS ATP ONLY' , 4 ) ;
202 END IF;
203
204 ELSIF l_scheduling_level_code = OE_SCHEDULE_UTIL.SCH_LEVEL_TWO
205 AND p_line_rec.schedule_action_code =
206 OE_SCHEDULE_UTIL.OESCH_ACT_RESERVE
207 THEN
208
209 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ACTION_NOT_ALLOWED');
210 FND_MESSAGE.SET_TOKEN('ACTION',
211 nvl(p_line_rec.schedule_action_code,
212 OE_SCHEDULE_UTIL.OESCH_ACT_RESERVE));
213 FND_MESSAGE.SET_TOKEN('ORDER_TYPE',
214 nvl(OE_SCHEDULE_UTIL.sch_cached_line_type,
215 OE_SCHEDULE_UTIL.sch_cached_order_type));
216 OE_MSG_PUB.Add;
217 x_return_status := FND_API.G_RET_STS_ERROR;
218 IF l_debug_level > 0 THEN
219 oe_debug_pub.add( 'LINE TYPE IS NOT ELIGIBLE FOR RESERVATION ' , 4 ) ;
220 END IF;
221
222 END IF;
223 END IF;
224
225 IF l_debug_level > 0 THEN
226 oe_debug_pub.add( '..EXITING OE_SCHEDULE_UTIL.VALIDATE_GROUP_LINE WITH ' || X_RETURN_STATUS , 1 ) ;
227 END IF;
228
229
230 EXCEPTION
231
232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
233
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235
236 WHEN OTHERS THEN
237
238 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
239 THEN
240 OE_MSG_PUB.Add_Exc_Msg
241 ( G_PKG_NAME
242 , 'Validate_Group_Line'
243 );
244 END IF;
245
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 END Validate_Group_Line;
248
249 /*---------------------------------------------------------------------
250 Procedure Name : Validate_Group
251 Description : Validate Group calls Validate Line and if the call is success
252 then and pass the record back to the caller. If it is a expected error, then ignore the record and if it is a unexpected error raise the error and stop the process.
253 --------------------------------------------------------------------- */
254 Procedure Validate_Group
255 (p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
256 p_sch_action IN VARCHAR2,
257 p_validate_action IN VARCHAR2 DEFAULT 'PARTIAL', --for bug 3590437
258 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
259 IS
260 l_return_status VARCHAR2(30);
261 J NUMBER := 0;
262 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
263 --
264 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
265 --
266 BEGIN
267 -- 3870895 : Revorted back the code modified for bug 3349770
268 IF l_debug_level > 0 THEN
269 oe_debug_pub.add( 'ENTERING VALIDATE GROUP' || P_X_LINE_TBL.COUNT , 1 ) ;
270 END IF;
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272 FOR I IN 1..p_x_line_tbl.count LOOP
273
274 OE_MSG_PUB.set_msg_context
275 ( p_entity_code => 'LINE'
276 ,p_entity_id => p_x_line_tbl(I).line_id
277 ,p_header_id => p_x_line_tbl(I).header_id
278 ,p_line_id => p_x_line_tbl(I).line_id
279 ,p_orig_sys_document_ref =>
280 p_x_line_tbl(I).orig_sys_document_ref
281 ,p_orig_sys_document_line_ref =>
282 p_x_line_tbl(I).orig_sys_line_ref
283 ,p_orig_sys_shipment_ref =>
284 p_x_line_tbl(I).orig_sys_shipment_ref
285 ,p_change_sequence => p_x_line_tbl(I).change_sequence
286 ,p_source_document_id =>
287 p_x_line_tbl(I).source_document_id
288 ,p_source_document_line_id =>
289 p_x_line_tbl(I).source_document_line_id
290 ,p_order_source_id =>
291 p_x_line_tbl(I).order_source_id
292 ,p_source_document_type_id =>
293 p_x_line_tbl(I).source_document_type_id);
294 --3564310
295 -- Checking for Warehouse Validation
296 Validate_Item_Warehouse
297 (p_inventory_item_id => p_x_line_tbl(I).inventory_item_id,
298 p_ship_from_org_id => p_x_line_tbl(I).ship_from_org_id,
299 x_return_status => l_return_status);
300 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
301 OE_SCHEDULE_UTIL.Validate_Line(p_line_rec => p_x_line_tbl(I),
302 p_old_line_rec => p_x_line_tbl(I),
303 p_sch_action => p_sch_action ,
304 x_return_status => l_return_status);
305
306 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
307
308 J := J + 1;
309 l_line_tbl(J) := p_x_line_tbl(I);
310
311 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
312
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN -- 3870895
316
317 x_return_status := FND_API.G_RET_STS_ERROR;
318 --5166476
319 IF OE_SCH_CONC_REQUESTS.g_conc_program = 'Y' THEN
320 IF p_x_line_tbl(I).ship_model_complete_flag = 'N' AND
321 p_x_line_tbl(I).top_model_line_id IS NOT NULL AND
322 (p_x_line_tbl(I).ato_line_id IS NULL OR
323 p_x_line_tbl(I).ato_line_id <> p_x_line_tbl(I).top_model_line_id) AND
324 p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
325 IF NOT OE_SCH_CONC_REQUESTS.included_processed(p_x_line_tbl(I).line_id) THEN
326 IF l_debug_level > 0 THEN
327 oe_debug_pub.add( 'INCLUDED PROCESSED : ' || p_x_line_tbl(I).line_id, 3 ) ;
328 END IF;
329 -- 5166476
330 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(p_x_line_tbl(I).line_id) := 'N';
331 END IF;
332 ELSE
333 --5166476
334 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(p_x_line_tbl(I).line_id) := 'N';
335 END IF;
336 END IF;
337 END IF;
338 ELSE -- 3564310
339 IF OE_SCH_CONC_REQUESTS.g_conc_program = 'Y' THEN
340 --5166476
341 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(p_x_line_tbl(I).line_id) := 'N';
342 END IF;
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 END IF;
345
346 END LOOP;
347 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN -- 3870895
348 p_x_line_tbl := l_line_tbl;
349 --5166476
350 ELSIF x_return_status = FND_API.G_RET_STS_ERROR
351 AND l_line_tbl.count > 0 THEN
352 IF OE_SCH_CONC_REQUESTS.g_conc_program = 'Y' THEN
353 FOR I IN 1..l_line_tbl.count LOOP
354 OE_SCH_CONC_REQUESTS.OE_line_status_Tbl(l_line_tbl(I).line_id) := 'N';
355 END LOOP;
356 END IF;
357
358 END IF;
359 IF l_debug_level > 0 THEN
360 oe_debug_pub.add( 'EXITING VALIDATE GROUP' || P_X_LINE_TBL.COUNT , 1 ) ;
361 END IF;
362
363 EXCEPTION
364
365 WHEN OTHERS THEN
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
368 THEN
369 OE_MSG_PUB.Add_Exc_Msg
370 ( G_PKG_NAME
371 , 'Validate_Group');
372 END IF;
373
374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
375
376 END Validate_Group;
377
378 Function Not_Part_of_set(p_top_model_line_id IN NUMBER)
379 RETURN BOOLEAN
380 IS
381 l_set_id NUMBER := Null;
382 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
383 BEGIN
384
385 IF l_debug_level > 0 THEN
386 oe_debug_pub.add( 'Entering Not_Part_of_set ' || p_top_model_line_id , 1 ) ;
387 oe_debug_pub.add( 'Value of the cached model ' || g_top_model_line_id,2);
388 END IF;
389 IF p_top_model_line_id = g_top_model_line_id
390 THEN
391 RETURN g_part_of_set;
392 Else
393
394 BEGIN
395
396 Select nvl(arrival_set_id,ship_set_id)
397 Into l_set_id
398 From oe_order_lines_all
399 Where line_id = p_top_model_line_id;
400
401 IF l_set_id is null THEN
402 g_part_of_set := TRUE;
403 ELSE
404 g_part_of_set := FALSE;
405 END IF;
406 g_top_model_line_id := p_top_model_line_id;
407 Return g_part_of_set;
408 EXCEPTION
409
410 WHEN OTHERS THEN
411 g_part_of_set := TRUE;
412 Return g_part_of_set;
413 END;
414
415 END IF;
416 END Not_Part_of_set;
417 /* ----------------------------------------------------------------
418 Procedure Check_Merge_Line:
419 This procedure checks if the Line Id that is going to be Merged
420 into the l_line_tbl is already existing in that plsql table.
421 If the Line_Id is not existing in the table it merges the Line_id
422 with the existing l_line_tbl plsql Table
423 Bug Number:2319608
424
425 Procedure Check_Merge_Line(
426 p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
427 p_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type
428 := OE_Order_PUB.G_MISS_LINE_TBL,
429 p_line_rec IN OE_ORDER_PUB.Line_rec_Type
430 := OE_Order_PUB.G_MISS_LINE_REC)
431 IS
432 J NUMBER;
433 l_option_exists VARCHAR2(1) := 'N';
434
435 BEGIN
436 IF l_debug_level > 0 THEN
437 oe_debug_pub.add('Entering Check_Merge_Line: ' || p_x_line_tbl.count,1);
438 oe_debug_pub.add('Size of the in table : ' || p_line_tbl.count,1);
439 END IF;
440 J := p_x_line_tbl.count;
441 IF p_line_rec.line_id is NOT NULL
442 AND p_line_rec.line_id <> FND_API.G_MISS_NUM THEN -- Main IF Start
443
444 FOR l_option_search in 1..J LOOP
445 IF p_line_rec.line_id = p_x_line_tbl(l_option_search).line_id THEN
446 IF l_debug_level > 0 THEN
447 OE_DEBUG_PUB.Add('Option already exists in the line table',1);
448 END IF;
449 l_option_exists := 'Y';
450 EXIT; -- Exit the Loop if a match is found
451 END IF;
452 END LOOP;
453
454 --If no matches are found then Merge the record.
455 IF l_option_exists = 'N' THEN
456 p_x_line_tbl(J+1) := p_line_rec;
457 END IF;
458
459 ELSE
460
461 FOR I in 1..p_line_tbl.count LOOP
462 l_option_exists := 'N';
463 FOR l_option_search in 1..p_x_line_tbl.count LOOP
464 IF p_line_tbl(I).line_id = p_x_line_tbl(l_option_search).line_id THEN
465 IF l_debug_level > 0 THEN
466 OE_DEBUG_PUB.Add('Option already exists in the line table',1);
467 END IF;
468 l_option_exists := 'Y';
469 EXIT; -- Exit the Inner Loop if a match is found
470 END IF;
471 END LOOP;
472
473 --If no matches are found then Merge the record.
474 IF l_option_exists = 'N' THEN
475 J := J + 1;
476 p_x_line_tbl(J) := p_line_tbl(I);
477 END IF;
478 END LOOP;
479
480 END IF; -- Main IF End
481 IF l_debug_level > 0 THEN
482 oe_debug_pub.add('Exiting Check_Merge_Line: ' || p_x_line_tbl.count,1);
483 END IF;
484
485 End Check_Merge_Line;
486 */
487
488 /*----------------------------------------------------------------
489 FUNCTION : Find_Line
490 Description : This Function returns TRUE if the line already
491 exist in the line table else FALSE
492 Added for Bug-2454163
493 ------------------------------------------------------------------*/
494
495 FUNCTION Find_line( p_x_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type,
496 p_line_id IN NUMBER)
497 Return BOOLEAN
498 IS
499 --
500 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
501 --
502 BEGIN
503
504 IF l_debug_level > 0 THEN
505 oe_debug_pub.add( 'ENTERING FIND_LINE: ' || P_LINE_ID , 1 ) ;
506 END IF;
507
508 FOR J IN 1..p_x_line_tbl.count LOOP
509
510 IF p_line_id = p_x_line_tbl(J).line_id THEN
511
512 IF l_debug_level > 0 THEN
513 oe_debug_pub.add( ' LINE EXISTS IN THE TABLE' , 1 ) ;
514 END IF;
515
516 RETURN TRUE;
517 END IF;
518 END LOOP;
519
520 RETURN FALSE;
521
522 END Find_line;
523
524
525 /*----------------------------------------------------------------
526 PROCEDURE Query_Lines
527 Description:
528
529 This is a internal procedure. This procedure will be called when user
530 performs any scheduling action from header lever. Schedule Order API will
531 will call this procedure to query all valid lines for requested scheduling
532 operation.
533 This procedure will return all valid records to caller when action is
534 schedule/unschedule/atp check/unreserve. If the action is reserve and line is
535 scheduled, the line will not be passed to caller and it performs the reservati on action.
536
537 ---------------------------------------------------------------------*/
538 PROCEDURE Query_Lines
539 ( p_header_id IN NUMBER,
540 p_sch_action IN VARCHAR2,
541 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
542 )
543 IS
544 I NUMBER;
545 l_inc_tbl OE_Order_PUB.Line_Tbl_Type;
546 l_sales_order_id NUMBER := Null;
547 l_line_rec OE_Order_PUB.Line_Rec_type;
548 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
549 l_set_rec OE_ORDER_CACHE.set_rec_type;
550 l_quantity_to_reserve NUMBER;
551 l_quantity2_to_reserve NUMBER; -- INVCONV
552 l_rsv_update BOOLEAN := FALSE;
553
554 CURSOR Process_inc is
555 SELECT line_id
556 FROM oe_order_lines_all
557 WHERE header_id = p_header_id
558 AND open_flag = 'Y'
559 AND ato_line_id is NULL
560 AND item_type_code IN ('MODEL', 'CLASS', 'KIT')
561 AND schedule_status_code is NULL;
562
563
564 CURSOR l_line_csr IS
565 SELECT line_id,schedule_status_code,
566 shippable_flag
567 FROM oe_order_lines_all
568 WHERE header_id = p_header_id
569 AND open_flag = 'Y'
570 AND line_category_code <> 'RETURN'
571 AND item_type_code <> 'SERVICE'
572 AND source_type_code <> OE_GLOBALS.G_SOURCE_EXTERNAL
573 ORDER BY arrival_set_id,ship_set_id,line_number,shipment_number,nvl(option_number,-1);
574
575 CURSOR l_line_inc_csr IS
576 SELECT line_id,ship_set_id,arrival_set_id, shippable_flag,
577 shipping_interfaced_flag,orig_sys_document_ref, orig_sys_line_ref,
578 orig_sys_shipment_ref, change_sequence, source_document_type_id,
579 source_document_id, source_document_line_id, order_source_id
580 FROM oe_order_lines_all
581 WHERE header_id = p_header_id
582 AND open_flag = 'Y'
583 AND line_category_code <> 'RETURN'
584 AND schedule_status_code IS NOT NULL
585 ORDER BY arrival_set_id,ship_set_id,line_number,shipment_number,nvl(option_number,-1);
586
587 --
588 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
589 --
590 BEGIN
591
592 IF l_debug_level > 0 THEN
593 oe_debug_pub.add( 'ENTERING QUERY LINES' , 1 ) ;
594 END IF;
595
596 -- Process Included Items if the action is schedule, atp_check
597 -- and reserve.
598
599 IF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_SCHEDULE OR
600 p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_RESERVE OR
601 p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_ATP_CHECK THEN
602
603 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
604 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
605
606 FOR I IN process_inc LOOP
607
608 IF l_debug_level > 0 THEN
609 oe_debug_pub.add( 'BEFORE CALLING INC'|| I.LINE_ID , 3 ) ;
610 END IF;
611 l_return_status := OE_CONFIG_UTIL.Process_Included_Items
612 (p_line_id => I.line_id,
613 p_freeze => FALSE);
614
615 IF l_debug_level > 0 THEN
616 oe_debug_pub.add( 'AFTER CALLING INC'|| L_RETURN_STATUS , 3 ) ;
617 END IF;
618
619 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
622 RAISE FND_API.G_EXC_ERROR;
623 END IF;
624
625 END LOOP;
626
627 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
628 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
629
630 END IF;
631
632
633
634 IF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_SCHEDULE
635 THEN
636
637 -- If the action is schedule,
638 -- then only query the lines that are not scheduled.
639 -- Re-scheduling should be allowed on the scheduled lines.
640 -- Prepare out table with the lines which are not scheduled.
641
642 I := 0;
643 FOR c1 IN l_line_csr LOOP
644
645
646 IF l_debug_level > 0 THEN
647 oe_debug_pub.add( 'PROCESSING LINE ' || C1.LINE_ID , 1 ) ;
648 END IF;
649
650 IF c1.schedule_status_code is null THEN
651
652 --4382036
653 /* OE_Line_Util.Query_Row( p_line_id => c1.line_id
654 ,x_line_rec => l_line_rec);
655 */
656
657 OE_Line_Util.Lock_Row(p_line_id => c1.line_id,
658 p_x_line_rec => l_line_rec,
659 x_return_status => l_return_status);
660
661
662 OE_MSG_PUB.set_msg_context
663 ( p_entity_code => 'LINE'
664 ,p_entity_id => l_line_rec.line_id
665 ,p_header_id => l_line_rec.header_id
666 ,p_line_id => l_line_rec.line_id
667 ,p_orig_sys_document_ref =>
668 l_line_rec.orig_sys_document_ref
669 ,p_orig_sys_document_line_ref =>
670 l_line_rec.orig_sys_line_ref
671 ,p_orig_sys_shipment_ref =>
672 l_line_rec.orig_sys_shipment_ref
673 ,p_change_sequence => l_line_rec.change_sequence
674 ,p_source_document_id =>
675 l_line_rec.source_document_id
676 ,p_source_document_line_id =>
677 l_line_rec.source_document_line_id
678 ,p_order_source_id =>
679 l_line_rec.order_source_id
680 ,p_source_document_type_id =>
681 l_line_rec.source_document_type_id);
682
683 l_line_rec.schedule_action_code := p_sch_action;
684 -- 2766876
685 l_line_rec.reserved_quantity := 0;
686
687 l_return_status := FND_API.G_RET_STS_SUCCESS;
688 OE_SCHEDULE_UTIL.Validate_Line(p_line_rec => l_line_rec,
689 p_old_line_rec => l_line_rec,
690 p_sch_action => p_sch_action,
691 x_return_status => l_return_status);
692
693
694 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
695
696 I := I + 1;
697 IF l_debug_level > 0 THEN
698 oe_debug_pub.add( 'LINE SELECTED FOR SCHEDULING **** : ' || L_LINE_REC.LINE_ID , 1 ) ;
699 END IF;
700 x_line_tbl(I) := l_line_rec;
701 x_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
702
703 IF (x_line_tbl(I).arrival_set_id is not null) THEN
704
705 l_set_rec := OE_ORDER_CACHE.Load_Set
706 ( x_line_tbl(I).arrival_set_id);
707 x_line_tbl(I).arrival_set := l_set_rec.set_name;
708
709 ELSIF (x_line_tbl(I).ship_set_id is not null) THEN
710
711 l_set_rec := OE_ORDER_CACHE.Load_Set
712 ( x_line_tbl(I).Ship_set_id);
713 x_line_tbl(I).ship_set := l_set_rec.set_name;
714
715 ELSIF (x_line_tbl(I).ship_model_complete_flag ='Y') THEN
716 x_line_tbl(I).ship_set := x_line_tbl(I).top_model_line_id;
717 ELSIF (x_line_tbl(I).ato_line_id is not null)
718 AND NOT(OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
719 AND MSC_ATP_GLOBAL.GET_APS_VERSION = 10) THEN
720 x_line_tbl(I).ship_set := x_line_tbl(I).ato_line_id;
721 END IF;
722
723
724 END IF;
725 END IF; -- schedule status code
726
727 END LOOP;
728
729 ELSIF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_RESERVE
730 THEN
731
732
733 --If the line is not scheduled,system should schedule and reserve the line.
734
735 I := 0;
736 FOR c1 IN l_line_csr LOOP
737
738 IF l_debug_level > 0 THEN
739 oe_debug_pub.add( 'LINE_ID :' || C1.LINE_ID , 1 ) ;
740 END IF;
741
742 -- 4382036
743 /* OE_Line_Util.Query_Row( p_line_id => c1.line_id
744 ,x_line_rec => l_line_rec);
745 */
746
747 OE_Line_Util.Lock_Row(p_line_id => c1.line_id,
748 p_x_line_rec => l_line_rec,
749 x_return_status => l_return_status);
750
751
752 OE_MSG_PUB.set_msg_context
753 ( p_entity_code => 'LINE'
754 ,p_entity_id => l_line_rec.line_id
755 ,p_header_id => l_line_rec.header_id
756 ,p_line_id => l_line_rec.line_id
757 ,p_orig_sys_document_ref =>
758 l_line_rec.orig_sys_document_ref
759 ,p_orig_sys_document_line_ref =>
760 l_line_rec.orig_sys_line_ref
761 ,p_orig_sys_shipment_ref =>
762 l_line_rec.orig_sys_shipment_ref
763 ,p_change_sequence => l_line_rec.change_sequence
764 ,p_source_document_id =>
765 l_line_rec.source_document_id
766 ,p_source_document_line_id =>
767 l_line_rec.source_document_line_id
768 ,p_order_source_id =>
769 l_line_rec.order_source_id
770 ,p_source_document_type_id =>
771 l_line_rec.source_document_type_id);
772
773 l_line_rec.schedule_action_code := p_sch_action;
774
775 IF l_line_rec.schedule_status_code is not null THEN
776
777 IF nvl(l_line_rec.shippable_flag,'N') = 'Y' THEN
778
779 IF l_sales_order_id is null THEN
780 l_sales_order_id :=
781 OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(p_header_id);
782 END IF;
783
784 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
785
786 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
787 ,p_line_id => l_line_rec.line_id
788 ,p_org_id => l_line_rec.ship_from_org_id
789 ,x_reserved_quantity => l_line_rec.reserved_quantity
790 ,x_reserved_quantity2 => l_line_rec.reserved_quantity2
791 );
792
793
794 /*l_line_rec.reserved_quantity :=
795 OE_LINE_UTIL.Get_Reserved_Quantity
796 (p_header_id => l_sales_order_id,
797 p_line_id => l_line_rec.line_id,
798 p_org_id => l_line_rec.ship_from_org_id); */
799
800 IF l_line_rec.reserved_quantity IS NULL
801 OR l_line_rec.reserved_quantity = FND_API.G_MISS_NUM THEN
802 l_line_rec.reserved_quantity := 0;
803 END IF;
804
805 /*l_line_rec.reserved_quantity2 := -- INVCONV
806 OE_LINE_UTIL.Get_Reserved_Quantity2
807 (p_header_id => l_sales_order_id,
808 p_line_id => l_line_rec.line_id,
809 p_org_id => l_line_rec.ship_from_org_id);*/
810
811 /*IF l_line_rec.reserved_quantity2 IS NULL
812 OR l_line_rec.reserved_quantity2 = FND_API.G_MISS_NUM THEN
813 l_line_rec.reserved_quantity2 := 0;
814 END IF; */ -- INVCONV PAL
815
816
817 -- Pack J
818 IF l_line_rec.reserved_quantity = 0
819 OR ( OE_CODE_CONTROL.Get_Code_Release_Level >= '110510'
820 --AND OE_SYS_PARAMETERS.value('PARTIAL_RESERVATION_FLAG')= 'Y'
821 AND l_line_rec.ordered_quantity >
822 l_line_rec.reserved_quantity) THEN
823
824
825 IF l_debug_level > 0 THEN
826 oe_debug_pub.add( 'NEED TO RESERVE THE LINE' , 2 ) ;
827 END IF;
828
829 -- Check if the line is eligible for reservation.
830 l_return_status := FND_API.G_RET_STS_SUCCESS;
831 OE_SCHEDULE_UTIL.Validate_Line(p_line_rec => l_line_rec,
832 p_old_line_rec => l_line_rec,
833 p_sch_action => p_sch_action,
834 x_return_status => l_return_status);
835
836 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
837 --Pack J
838 -- To calculate the remaining quantity to be reserved
839 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
840 --AND OE_SYS_PARAMETERS.value('PARTIAL_RESERVATION_FLAG')= 'Y'
841 AND l_line_rec.ordered_quantity >
842 l_line_rec.reserved_quantity THEN
843 l_quantity_to_reserve := l_line_rec.ordered_quantity - l_line_rec.reserved_quantity;
844 ELSE
845 l_quantity_to_reserve := l_line_rec.ordered_quantity;
846 END IF;
847
848 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' -- INVCONV
849 AND l_line_rec.ordered_quantity2 >
850 l_line_rec.reserved_quantity2 THEN
851 l_quantity2_to_reserve := nvl(l_line_rec.ordered_quantity2, 0) - nvl(l_line_rec.reserved_quantity2,0);
852 ELSE
853 l_quantity2_to_reserve := l_line_rec.ordered_quantity2;
854 END IF;
855
856 IF l_quantity2_to_reserve = 0 -- INVCONV
857 THEN
858 l_quantity2_to_reserve := NULL;
859 END IF;
860
861 IF l_debug_level > 0 THEN
862 oe_debug_pub.add( 'QUANTITY TO RESERVE '||l_quantity_to_reserve , 2 ) ;
863 oe_debug_pub.add( 'QUANTITY2 TO RESERVE '||l_quantity2_to_reserve , 2 ) ;
864 oe_debug_pub.add( 'LINE SELECTED FOR RESERVE **** : '|| L_LINE_REC.LINE_ID , 1 ) ;
865 END IF;
866 --partial reservation exists
867 IF nvl(l_line_rec.reserved_quantity,0) > 0 THEN
868 l_rsv_update := TRUE;
869 END IF;
870 OE_SCHEDULE_UTIL.Reserve_Line
871 (p_line_rec => l_line_rec
872 ,p_quantity_to_reserve => l_quantity_to_reserve --l_line_rec.ordered_quantity
873 ,p_quantity2_to_reserve => l_quantity2_to_reserve --l_line_rec.ordered_quantity2
874 ,p_rsv_update => l_rsv_update
875 ,x_return_status => l_return_status);
876
877 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
878 IF l_debug_level > 0 THEN
879 oe_debug_pub.add( 'RAISING UNEXPECTED ERROR' , 1 ) ;
880 END IF;
881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
882 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
883 IF l_debug_level > 0 THEN
884 oe_debug_pub.add( 'WILL IGNORE THE LINE AND PROCEED' , 1 ) ;
885 END IF;
886 l_return_status := FND_API.G_RET_STS_SUCCESS;
887 END IF;
888
889 END IF;
890 ELSE -- reservation exists
891
892 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
893 OE_MSG_PUB.Add;
894 IF l_debug_level > 0 THEN
895 oe_debug_pub.add( 'LINE IS ALREADY RESERVED , DO NOT NEED RESERVATION :' || L_LINE_REC.RESERVED_QUANTITY ) ;
896 END IF;
897
898 END IF; -- Reserve.
899 ELSE
900
901 l_line_rec.reserved_quantity := 0;
902 l_line_rec.reserved_quantity2 := 0; -- INVCONV
903 IF l_debug_level > 0 THEN
904 oe_debug_pub.add( 'LINE IS NON SHIPPABLE ' , 2 ) ;
905 END IF;
906 -- code fix for 3300528
907 --FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
908 IF l_line_rec.ato_line_id IS NOT NULL AND
909 NOT ( l_line_rec.ato_line_id = l_line_rec.line_id AND
910 l_line_rec.item_type_code IN ( OE_GLOBALS.G_ITEM_OPTION,
911 OE_GLOBALS.G_ITEM_STANDARD))
912 THEN
913 FND_MESSAGE.SET_NAME('ONT','OE_SCH_RES_NO_CONFIG');
914 ELSE
915 FND_MESSAGE.SET_NAME('ONT','ONT_SCH_NOT_RESERVABLE');
916 END IF;
917 -- code fix for 3300528
918 OE_MSG_PUB.Add;
919
920 END IF;
921 ELSE -- Line is not scheduled yet.
922
923 l_line_rec.reserved_quantity := 0;
924 l_line_rec.reserved_quantity2 := 0; -- INCONV
925 l_return_status := FND_API.G_RET_STS_SUCCESS;
926 OE_SCHEDULE_UTIL.Validate_Line(p_line_rec => l_line_rec,
927 p_old_line_rec => l_line_rec,
928 p_sch_action => p_sch_action,
929 x_return_status => l_return_status);
930
931 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
932 IF l_debug_level > 0 THEN
933 oe_debug_pub.add( 'LINE SELECTED FOR RESERVE **** : '|| L_LINE_REC.LINE_ID , 1 ) ;
934 END IF;
935 I := I + 1;
936 x_line_tbl(I) := l_line_rec;
937 x_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
938
939 IF (x_line_tbl(I).arrival_set_id is not null) THEN
940 l_set_rec := OE_ORDER_CACHE.Load_Set
941 ( x_line_tbl(I).arrival_set_id);
942 x_line_tbl(I).arrival_set := l_set_rec.set_name;
943 ELSIF (x_line_tbl(I).ship_set_id is not null) THEN
944 l_set_rec := OE_ORDER_CACHE.Load_Set
945 ( x_line_tbl(I).ship_set_id);
946 x_line_tbl(I).ship_set := l_set_rec.set_name;
947 ELSIF (x_line_tbl(I).ship_model_complete_flag ='Y') THEN
948 x_line_tbl(I).ship_set := x_line_tbl(I).top_model_line_id;
949 ELSIF (x_line_tbl(I).ato_line_id is not null)
950 AND NOT(OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
951 AND MSC_ATP_GLOBAL.GET_APS_VERSION = 10) THEN
952 x_line_tbl(I).ship_set := x_line_tbl(I).ato_line_id;
953 END IF;
954
955 x_line_tbl(I).reserved_quantity := 0;
956 x_line_tbl(I).reserved_quantity2 := 0; -- INVCONV
957
958 END IF; -- return status.
959 END IF; -- scheduled line.
960 END LOOP;
961
962 ELSIF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_ATP_CHECK
963 THEN
964
965 -- Load line table with scheduled and unscheduled lines.
966
967 I := 0;
968 FOR c1 IN l_line_csr LOOP
969 /*
970 I := I + 1;
971
972 IF I = 1 THEN */ -- 2327783
973 IF l_sales_order_id IS NULL THEN
974 l_sales_order_id :=OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(p_header_id);
975 END IF;
976
977 IF l_debug_level > 0 THEN
978 oe_debug_pub.add( 'LINE SELECTED FOR ATP **** : ' || C1.LINE_ID , 1 ) ;
979 END IF;
980 OE_Line_Util.Query_Row( p_line_id => c1.line_id
981 ,x_line_rec => l_line_rec);
982
983
984 OE_MSG_PUB.set_msg_context
985 ( p_entity_code => 'LINE'
986 ,p_entity_id => l_line_rec.line_id
987 ,p_header_id => l_line_rec.header_id
988 ,p_line_id => l_line_rec.line_id
989 ,p_orig_sys_document_ref =>
990 l_line_rec.orig_sys_document_ref
991 ,p_orig_sys_document_line_ref =>
992 l_line_rec.orig_sys_line_ref
993 ,p_orig_sys_shipment_ref =>
994 l_line_rec.orig_sys_shipment_ref
995 ,p_change_sequence => l_line_rec.change_sequence
996 ,p_source_document_id =>
997 l_line_rec.source_document_id
998 ,p_source_document_line_id =>
999 l_line_rec.source_document_line_id
1000 ,p_order_source_id =>
1001 l_line_rec.order_source_id
1002 ,p_source_document_type_id =>
1003 l_line_rec.source_document_type_id);
1004 l_line_rec.schedule_action_code := p_sch_action;
1005
1006
1007 l_return_status := FND_API.G_RET_STS_SUCCESS;
1008 OE_SCHEDULE_UTIL.Validate_Line(p_line_rec => l_line_rec,
1009 p_old_line_rec => l_line_rec,
1010 p_sch_action => p_sch_action,
1011 x_return_status => l_return_status);
1012
1013
1014 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1015 I := I +1; -- 2327783
1016 x_line_tbl(I) := l_line_rec;
1017 -- x_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
1018
1019 IF (x_line_tbl(I).arrival_set_id is not null) THEN
1020 l_set_rec := OE_ORDER_CACHE.Load_Set
1021 ( x_line_tbl(I).arrival_set_id);
1022 x_line_tbl(I).arrival_set := l_set_rec.set_name;
1023 ELSIF (x_line_tbl(I).ship_set_id is not null) THEN
1024 l_set_rec := OE_ORDER_CACHE.Load_Set
1025 ( x_line_tbl(I).ship_set_id);
1026 x_line_tbl(I).ship_set := l_set_rec.set_name;
1027 ELSIF (x_line_tbl(I).ship_model_complete_flag ='Y') THEN
1028 x_line_tbl(I).ship_set := x_line_tbl(I).top_model_line_id;
1029 ELSIF (x_line_tbl(I).ato_line_id is not null)
1030 AND NOT(OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
1031 AND MSC_ATP_GLOBAL.GET_APS_VERSION = 10) THEN
1032 x_line_tbl(I).ship_set := x_line_tbl(I).ato_line_id;
1033 END IF;
1034
1035 IF x_line_tbl(I).schedule_status_code is not null
1036 AND nvl(x_line_tbl(I).shippable_flag,'N') = 'Y' THEN
1037
1038 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
1039
1040 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
1041 ,p_line_id => x_line_tbl(I).line_id
1042 ,p_org_id => x_line_tbl(I).ship_from_org_id
1043 ,x_reserved_quantity => x_line_tbl(I).reserved_quantity
1044 ,x_reserved_quantity2 => x_line_tbl(I).reserved_quantity2
1045 );
1046
1047
1048 /*x_line_tbl(I).reserved_quantity :=
1049 OE_LINE_UTIL.Get_Reserved_Quantity
1050 (p_header_id => l_sales_order_id,
1051 p_line_id => x_line_tbl(I).line_id,
1052 p_org_id => x_line_tbl(I).ship_from_org_id);
1053 x_line_tbl(I).reserved_quantity2 := -- INVCONV
1054 OE_LINE_UTIL.Get_Reserved_Quantity2
1055 (p_header_id => l_sales_order_id,
1056 p_line_id => x_line_tbl(I).line_id,
1057 p_org_id => x_line_tbl(I).ship_from_org_id); */
1058
1059 END IF;
1060
1061 IF x_line_tbl(I).reserved_quantity IS NULL
1062 OR x_line_tbl(I).reserved_quantity = FND_API.G_MISS_NUM THEN
1063 x_line_tbl(I).reserved_quantity := 0;
1064 END IF;
1065
1066 /* IF x_line_tbl(I).reserved_quantity2 IS NULL -- INVCONV
1067 OR x_line_tbl(I).reserved_quantity2 = FND_API.G_MISS_NUM THEN
1068 x_line_tbl(I).reserved_quantity2 := 0;
1069 END IF; */
1070
1071 END IF; -- Validation success
1072 END LOOP;
1073
1074
1075 ELSIF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_UNRESERVE THEN
1076
1077 -- Lines are already scheduled. We no need to explode the included items.
1078
1079 I := 0;
1080
1081 FOR c2 IN l_line_inc_csr LOOP
1082
1083
1084 OE_MSG_PUB.set_msg_context
1085 ( p_entity_code => 'LINE'
1086 ,p_entity_id => C2.line_id
1087 ,p_header_id => p_header_id
1088 ,p_line_id => C2.line_id
1089 ,p_orig_sys_document_ref => C2.orig_sys_document_ref
1090 ,p_orig_sys_document_line_ref => C2.orig_sys_line_ref
1091 ,p_orig_sys_shipment_ref => C2.orig_sys_shipment_ref
1092 ,p_change_sequence => C2.change_sequence
1093 ,p_source_document_id => C2.source_document_id
1094 ,p_source_document_line_id => C2.source_document_line_id
1095 ,p_order_source_id => C2.order_source_id
1096 ,p_source_document_type_id => C2.source_document_type_id);
1097
1098 -- If the line belong to set, and action is unschedule,
1099 -- ignore the line.
1100 IF nvl(c2.shippable_flag,'N') = 'Y' THEN
1101
1102 IF (nvl(c2.shipping_interfaced_flag,'N') = 'Y'
1103 AND oe_schedule_util.Get_Pick_Status(c2.line_id)) THEN -- 2595661
1104
1105
1106 FND_MESSAGE.SET_NAME('ONT','OE_SCH_UNRSV_NOT_ALLOWED');
1107 OE_MSG_PUB.Add;
1108 IF l_debug_level > 0 THEN
1109 oe_debug_pub.add( 'LINE IS SHIPPING INTERFACED ' || C2.LINE_ID , 1 ) ;
1110 END IF;
1111
1112 ELSE
1113
1114 -- 4382036
1115 /* OE_Line_Util.Query_Row( p_line_id => c2.line_id
1116 ,x_line_rec => l_line_rec);
1117 */
1118
1119 OE_Line_Util.Lock_Row(p_line_id => c2.line_id,
1120 p_x_line_rec => l_line_rec,
1121 x_return_status => l_return_status);
1122
1123 OE_MSG_PUB.set_msg_context
1124 ( p_entity_code => 'LINE'
1125 ,p_entity_id => l_line_rec.line_id
1126 ,p_header_id => l_line_rec.header_id
1127 ,p_line_id => l_line_rec.line_id
1128 ,p_orig_sys_document_ref =>
1129 l_line_rec.orig_sys_document_ref
1130 ,p_orig_sys_document_line_ref =>
1131 l_line_rec.orig_sys_line_ref
1132 ,p_orig_sys_shipment_ref =>
1133 l_line_rec.orig_sys_shipment_ref
1134 ,p_change_sequence => l_line_rec.change_sequence
1135 ,p_source_document_id =>
1136 l_line_rec.source_document_id
1137 ,p_source_document_line_id =>
1138 l_line_rec.source_document_line_id
1139 ,p_order_source_id =>
1140 l_line_rec.order_source_id
1141 ,p_source_document_type_id =>
1142 l_line_rec.source_document_type_id);
1143
1144 IF l_sales_order_id is null THEN
1145 l_sales_order_id :=
1146 OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(p_header_id);
1147 END IF;
1148
1149
1150 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
1151
1152 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
1153 ,p_line_id => l_line_rec.line_id
1154 ,p_org_id => l_line_rec.ship_from_org_id
1155 ,x_reserved_quantity => l_line_rec.reserved_quantity
1156 ,x_reserved_quantity2 => l_line_rec.reserved_quantity2
1157 );
1158
1159
1160 /*l_line_rec.reserved_quantity :=
1161 OE_LINE_UTIL.Get_Reserved_Quantity
1162 (p_header_id => l_sales_order_id,
1163 p_line_id => l_line_rec.line_id,
1164 p_org_id => l_line_rec.ship_from_org_id); */
1165
1166 IF l_line_rec.reserved_quantity IS NULL
1167 OR l_line_rec.reserved_quantity = FND_API.G_MISS_NUM THEN
1168 l_line_rec.reserved_quantity := 0;
1169 END IF;
1170
1171
1172 /*l_line_rec.reserved_quantity2 := -- INVCONV
1173 OE_LINE_UTIL.Get_Reserved_Quantity2
1174 (p_header_id => l_sales_order_id,
1175 p_line_id => l_line_rec.line_id,
1176 p_org_id => l_line_rec.ship_from_org_id); */
1177
1178 /*IF l_line_rec.reserved_quantity2 IS NULL -- INVCONV - why was this commented out
1179 OR l_line_rec.reserved_quantity2 = FND_API.G_MISS_NUM THEN
1180 l_line_rec.reserved_quantity2 := 0;
1181 END IF; */
1182
1183
1184
1185 IF l_line_rec.reserved_quantity > 0 THEN
1186
1187 -- I := I + 1; 2327783
1188 IF l_sales_order_id is null THEN
1189
1190 l_sales_order_id :=OE_SCHEDULE_UTIL.
1191 Get_mtl_sales_order_id(p_header_id);
1192
1193 END IF;
1194
1195 IF l_debug_level > 0 THEN
1196 oe_debug_pub.add( 'LINE SELECTED FOR UNRES **** : ' || L_LINE_REC.LINE_ID , 1 ) ;
1197 END IF;
1198
1199
1200 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1201 l_line_rec.schedule_action_code := p_sch_action;
1202 l_return_status := FND_API.G_RET_STS_SUCCESS;
1203
1204 OE_SCHEDULE_UTIL.Validate_Line(p_line_rec => l_line_rec,
1205 p_old_line_rec => l_line_rec,
1206 p_sch_action => p_sch_action,
1207 x_return_status => l_return_status);
1208
1209
1210 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1211 I := I + 1; --2327783
1212 x_line_tbl(I) := l_line_rec;
1213 END IF;
1214 END IF; -- reservation qty.
1215 END IF; -- Shipping interfaced flag.
1216
1217 ELSE -- not shippable
1218 l_line_rec.reserved_quantity := 0;
1219 END IF; -- Line is not shippable line.
1220
1221 END LOOP;
1222 ELSIF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_UNSCHEDULE
1223 THEN
1224
1225 -- Lines are already scheduled. We no need to explode the included items.
1226
1227 I := 0;
1228
1229 FOR c2 IN l_line_inc_csr LOOP
1230 -- If the line belong to set, and action is unschedule,
1231 -- ignore the line.
1232
1233 OE_MSG_PUB.set_msg_context
1234 ( p_entity_code => 'LINE'
1235 ,p_entity_id => C2.line_id
1236 ,p_header_id => p_header_id
1237 ,p_line_id => C2.line_id
1238 ,p_orig_sys_document_ref => C2.orig_sys_document_ref
1239 ,p_orig_sys_document_line_ref => C2.orig_sys_line_ref
1240 ,p_orig_sys_shipment_ref => C2.orig_sys_shipment_ref
1241 ,p_change_sequence => C2.change_sequence
1242 ,p_source_document_id => C2.source_document_id
1243 ,p_source_document_line_id => C2.source_document_line_id
1244 ,p_order_source_id => C2.order_source_id
1245 ,p_source_document_type_id => C2.source_document_type_id);
1246
1247 IF c2.ship_set_id is null
1248 AND c2.arrival_set_id is null
1249 THEN
1250 /*
1251 I := I + 1;
1252 IF I = 1 THEN */ -- 2327783
1253
1254 IF l_sales_order_id IS NULL THEN
1255 l_sales_order_id :=OE_SCHEDULE_UTIL.
1256 Get_mtl_sales_order_id(p_header_id);
1257 END IF;
1258
1259
1260 IF l_debug_level > 0 THEN
1261 oe_debug_pub.add( 'LINE SELECTED FOR UNSCH **** : ' || C2.LINE_ID , 1 ) ;
1262 END IF;
1263
1264 -- 4382036
1265 /* OE_Line_Util.Query_Row( p_line_id => c2.line_id
1266 ,x_line_rec => l_line_rec);
1267 */
1268
1269 OE_Line_Util.Lock_Row(p_line_id => c2.line_id,
1270 p_x_line_rec => l_line_rec,
1271 x_return_status => l_return_status);
1272
1273 OE_MSG_PUB.set_msg_context
1274 ( p_entity_code => 'LINE'
1275 ,p_entity_id => l_line_rec.line_id
1276 ,p_header_id => l_line_rec.header_id
1277 ,p_line_id => l_line_rec.line_id
1278 ,p_orig_sys_document_ref =>
1279 l_line_rec.orig_sys_document_ref
1280 ,p_orig_sys_document_line_ref =>
1281 l_line_rec.orig_sys_line_ref
1282 ,p_orig_sys_shipment_ref =>
1283 l_line_rec.orig_sys_shipment_ref
1284 ,p_change_sequence => l_line_rec.change_sequence
1285 ,p_source_document_id =>
1286 l_line_rec.source_document_id
1287 ,p_source_document_line_id =>
1288 l_line_rec.source_document_line_id
1289 ,p_order_source_id =>
1290 l_line_rec.order_source_id
1291 ,p_source_document_type_id =>
1292 l_line_rec.source_document_type_id);
1293
1294
1295 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1296 l_line_rec.schedule_action_code := p_sch_action;
1297
1298 l_return_status := FND_API.G_RET_STS_SUCCESS;
1299 OE_SCHEDULE_UTIL.Validate_Line(p_line_rec => l_line_rec,
1300 p_old_line_rec => l_line_rec,
1301 p_sch_action => p_sch_action,
1302 x_return_status => l_return_status);
1303
1304 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1305 I := I + 1; --2327783
1306 x_line_tbl(I) := l_line_rec;
1307
1308 IF (x_line_tbl(I).ship_model_complete_flag ='Y') THEN
1309 x_line_tbl(I).ship_set := x_line_tbl(I).top_model_line_id;
1310 ELSIF (x_line_tbl(I).ato_line_id is not null)
1311 AND NOT(OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
1312 AND MSC_ATP_GLOBAL.GET_APS_VERSION = 10) THEN
1313 x_line_tbl(I).ship_set := x_line_tbl(I).ato_line_id;
1314 END IF;
1315
1316 IF nvl(x_line_tbl(I).shippable_flag,'N') = 'Y' THEN
1317 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
1318
1319 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
1320 ,p_line_id => x_line_tbl(I).line_id
1321 ,p_org_id => x_line_tbl(I).ship_from_org_id
1322 ,x_reserved_quantity => x_line_tbl(I).reserved_quantity
1323 ,x_reserved_quantity2 => x_line_tbl(I).reserved_quantity2
1324 );
1325
1326 /*x_line_tbl(I).reserved_quantity :=
1327 OE_LINE_UTIL.Get_Reserved_Quantity
1328 (p_header_id => l_sales_order_id,
1329 p_line_id => x_line_tbl(I).line_id,
1330 p_org_id => x_line_tbl(I).ship_from_org_id);
1331 x_line_tbl(I).reserved_quantity2 := -- INVCONV
1332 OE_LINE_UTIL.Get_Reserved_Quantity2
1333 (p_header_id => l_sales_order_id,
1334 p_line_id => x_line_tbl(I).line_id,
1335 p_org_id => x_line_tbl(I).ship_from_org_id);*/
1336
1337 END IF;
1338
1339 IF x_line_tbl(I).reserved_quantity IS NULL
1340 OR x_line_tbl(I).reserved_quantity = FND_API.G_MISS_NUM THEN
1341 x_line_tbl(I).reserved_quantity := 0;
1342 END IF;
1343 IF x_line_tbl(I).reserved_quantity2 IS NULL -- INVCONV
1344 OR x_line_tbl(I).reserved_quantity2 = FND_API.G_MISS_NUM THEN
1345 x_line_tbl(I).reserved_quantity2 := 0;
1346 END IF;
1347
1348 END IF; -- Success.
1349 ELSE -- part of set
1350 FND_MESSAGE.SET_NAME('ONT','OE_SCH_CANNOT_UNSCH_SET');
1351 OE_MSG_PUB.Add;
1352 IF l_debug_level > 0 THEN
1353 oe_debug_pub.add( 'LINE IS PART OF SET ' , 1 ) ;
1354 END IF;
1355
1356 END IF; -- do not unschedule if line is part of set.
1357
1358 END LOOP;
1359
1360 END IF; -- Main.
1361
1362
1363 IF l_debug_level > 0 THEN
1364 oe_debug_pub.add( 'EXITING OE_GROUP_SCH_UTIL.QUERY_LINES ' || X_LINE_TBL.COUNT , 1 ) ;
1365 END IF;
1366
1367
1368 EXCEPTION
1369
1370 WHEN FND_API.G_EXC_ERROR THEN
1371 IF l_debug_level > 0 THEN
1372 oe_debug_pub.add( 'EXPECTED ERROR IN QUERY LINES ' , 1 ) ;
1373 END IF;
1374 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
1375 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
1376 RAISE FND_API.G_EXC_ERROR;
1377
1378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1379
1380 IF l_debug_level > 0 THEN
1381 oe_debug_pub.add( 'UNEXPECTED ERROR IN QUERY LINES ' , 1 ) ;
1382 END IF;
1383 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
1384 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
1385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1386
1387 WHEN OTHERS THEN
1388
1389 IF l_debug_level > 0 THEN
1390 oe_debug_pub.add( 'WHEN OTHERS ERROR IN QUERY LINES ' , 1 ) ;
1391 END IF;
1392 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
1393 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
1394 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1395 THEN
1396 OE_MSG_PUB.Add_Exc_Msg
1397 ( G_PKG_NAME
1398 , 'Query_lines'
1399 );
1400 END IF;
1401
1402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1403
1404 END Query_Lines;
1405 /*----------------------------------------------------------------
1406 PROCEDURE Query_Schedule_Lines
1407 Description : This api will be called from schedule_multi_line API when
1408 action requested by user is schedule.
1409 System has to perform little validations before passing record back to the user.
1410 Initially system selects few scheduling related attributes to see whether the
1411 line has to be selected for scheduling or not. If it has to be selected then
1412 checks if it is part of non smc and model is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
1413 processing. If the line selected is Included item and No SMC, make sure not only
1414 it's modle is selected, also check for its immediate parent. If its immediate
1415 parent is selected then ignore the included item, since included item will be
1416 selected by it's parent.
1417
1418 If a line is part of SMC or ATO or if it is a TOP MODEL, selected whole model for processing. If it is a non smc class or kit, select its included items if any.
1419 ----------------------------------------------------------------- */
1420 PROCEDURE Query_Schedule_Lines
1421 ( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
1422 p_sch_action IN VARCHAR2,
1423 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
1424 )
1425 IS
1426 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1427 l_line_rec OE_Order_PUB.Line_rec_Type;
1428 l_line_id NUMBER;
1429 l_arrival_set_id NUMBER;
1430 l_ship_set_id NUMBER;
1431 l_top_model_line_id NUMBER;
1432 l_ato_line_id NUMBER;
1433 l_smc_flag VARCHAR2(1);
1434 l_item_type_code VARCHAR2(30);
1435 l_schedule_status_code VARCHAR2(30);
1436 l_line_category_code VARCHAR2(30);
1437 l_source_type_code VARCHAR2(30);
1438 l_link_to_line_id NUMBER;
1439 l_query VARCHAR2(1);
1440 l_found VARCHAR2(1);
1441 J NUMBER;
1442 l_header_id NUMBER;
1443 l_open_flag VARCHAR2(1);
1444
1445 --
1446 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1447 --
1448 l_orig_sys_document_ref VARCHAR2(50);
1449 l_orig_sys_line_ref VARCHAR2(50);
1450 l_orig_sys_shipment_ref VARCHAR2(50);
1451 l_source_document_type_id NUMBER;
1452 l_change_sequence VARCHAR2(50);
1453 l_source_document_id NUMBER;
1454 l_source_document_line_id NUMBER;
1455 l_order_source_id NUMBER;
1456 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1457 BEGIN
1458
1459 IF l_debug_level > 0 THEN
1460 oe_debug_pub.add( 'ENTERING INTO QUERY SCHEDULE LINES ' , 1 ) ;
1461 END IF;
1462 FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
1463
1464 IF l_debug_level > 0 THEN
1465 oe_debug_pub.add( 'PROCESSING LINE_ID' || P_SELECTED_TBL(L).ID1 , 2 ) ;
1466 END IF;
1467
1468 l_line_id := p_selected_tbl(L).id1;
1469 -- Query required attributes and check does any action is required
1470 -- on the line.
1471 BEGIN
1472
1473 Select Arrival_set_id, Ship_set_id,top_model_line_id,
1474 Ship_model_complete_flag, ato_line_id,item_type_code,
1475 Schedule_status_code,line_category_code,header_id,open_flag,
1476 source_type_code,link_to_line_id,
1477 orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
1478 source_document_type_id, change_sequence, source_document_id,
1479 source_document_line_id, order_source_id
1480 Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
1481 l_smc_flag, l_ato_line_id, l_item_type_code,
1482 l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
1483 l_source_type_code,l_link_to_line_id,
1484 l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
1485 l_source_document_type_id, l_change_sequence, l_source_document_id,
1486 l_source_document_line_id, l_order_source_id
1487 From oe_order_lines_all
1488 Where line_id = l_line_id;
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 Null;
1493 END;
1494
1495 OE_MSG_PUB.set_msg_context
1496 ( p_entity_code => 'LINE'
1497 ,p_entity_id => l_line_id
1498 ,p_header_id => l_header_id
1499 ,p_line_id => l_line_id
1500 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1501 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
1502 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
1503 ,p_change_sequence => l_change_sequence
1504 ,p_source_document_id => l_source_document_id
1505 ,p_source_document_line_id => l_source_document_line_id
1506 ,p_order_source_id => l_order_source_id
1507 ,p_source_document_type_id => l_source_document_type_id);
1508
1509 -- If the line is part of set then avoid selecting them ,
1510 -- since scheduling is prerequisite for sets.
1511 -- Also avoid selecting any line if they are part of ato model
1512 -- or smc and they are scheduled.
1513
1514 IF ((l_arrival_set_id is not null
1515 OR l_ship_set_id is not null
1516 OR l_ato_line_id is not null
1517 OR l_smc_flag = 'Y') AND
1518 l_schedule_status_code is NOT NULL)
1519 OR l_line_category_code = 'RETURN'
1520 OR l_item_type_code = 'SERVICE'
1521 OR l_source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL
1522 OR nvl(l_open_flag,'Y') = 'N'
1523 THEN
1524 -- Schedule action is not required for the line.
1525 -- Populate the message on the stack.
1526
1527 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
1528 OE_MSG_PUB.Add;
1529 IF l_debug_level > 0 THEN
1530 oe_debug_pub.add( 'LINE IS A SCHEDULED LINE' || L_LINE_ID , 2 ) ;
1531 END IF;
1532
1533 ELSE -- line not scheduled, please select the line to schedule.
1534
1535 l_query := 'Y';
1536
1537 -- If user slectes the non smc model and option,
1538 -- then avoid selecting the
1539 -- option, since model will select the option.
1540
1541 IF l_top_model_line_id IS NOT NULL
1542 AND l_smc_flag = 'N'
1543 AND l_top_model_line_id <> l_line_id
1544 THEN
1545
1546 -- Check if model is selected by user.
1547 -- If model is selected then ignore the line for query, since
1548 -- model will query the line.
1549
1550 FOR K IN 1..p_selected_tbl.count LOOP -- R12.MOAC
1551 IF l_top_model_line_id = p_selected_tbl(K).id1
1552 OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
1553 l_link_to_line_id = p_selected_tbl(K).id1) THEN
1554 IF l_debug_level > 0 THEN
1555 oe_debug_pub.add( 'PARENT LINE IS PART OF TABLE' || P_SELECTED_TBL(K).ID1 , 2 ) ;
1556 END IF;
1557 l_query := 'N';
1558 exit;
1559 END IF;
1560 END LOOP;
1561
1562
1563
1564 ELSIF x_line_tbl.count > 0 THEN
1565 -- If the line is not a non smc option or class, then
1566 -- check if the line is present in the table.
1567 -- Check if the line is already present. If line exists then
1568 -- ignore the line.
1569
1570 FOR I IN 1..x_line_tbl.count LOOP
1571 IF l_line_id = x_line_tbl(I).line_id
1572 THEN
1573 l_query := 'N';
1574 IF l_debug_level > 0 THEN
1575 oe_debug_pub.add( '2 LINE IS PART OF TABLE' , 2 ) ;
1576 END IF;
1577 Exit;
1578 END IF;
1579 END LOOP;
1580
1581 END IF;
1582
1583 IF l_query = 'Y' THEN
1584
1585 -- Decided to query the line. See what to pass to query sets.
1586 IF l_smc_flag = 'Y'
1587 THEN
1588
1589 Oe_Config_Schedule_Pvt.Query_Set_Lines
1590 (p_header_id => l_header_id,
1591 p_model_line_id => l_top_model_line_id,
1592 p_sch_action => p_sch_action,
1593 x_line_tbl => l_line_tbl,
1594 x_return_status => l_return_status);
1595
1596 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1598 END IF;
1599
1600 IF x_line_tbl.count = 0 THEN
1601
1602 x_line_tbl := l_line_tbl;
1603
1604 ELSE
1605 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
1606 ,p_line_tbl => l_line_tbl); */ -- Bug-2454163
1607
1608 J := x_line_tbl.count;
1609 FOR I IN 1..l_line_tbl.count LOOP
1610 IF NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
1611 x_line_tbl(J + I) := l_line_tbl(I);
1612 END IF;
1613 END LOOP;
1614 END IF;
1615
1616 ELSIF (l_ato_line_id is not null AND
1617 NOT (l_line_id = l_ato_line_id AND
1618 l_item_type_code IN(OE_GLOBALS.G_ITEM_STANDARD,
1619 OE_GLOBALS.G_ITEM_OPTION)))
1620 THEN
1621
1622
1623 -- Query ato model.
1624 OE_Config_Util.Query_ATO_Options
1625 (p_ato_line_id => l_ato_line_id,
1626 x_line_tbl => l_line_tbl);
1627
1628
1629 IF x_line_tbl.count = 0 THEN
1630
1631 x_line_tbl := l_line_tbl;
1632
1633 ELSE
1634 /*Check_Merge_Line(p_x_line_tbl => x_line_tbl
1635 ,p_line_tbl => l_line_tbl); */ -- Bug-2454163
1636
1637 J := x_line_tbl.count;
1638 FOR I IN 1..l_line_tbl.count LOOP
1639 IF NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
1640 x_line_tbl(J + I) := l_line_tbl(I);
1641 END IF;
1642 END LOOP;
1643 END IF;
1644
1645 ELSIF l_line_id = l_top_model_line_id THEN
1646
1647 -- This is a non smc top model. User might have selected model
1648 -- and its options If the model is selected query whole
1649 -- model and try to place them in the
1650 -- out table if they are not present already.
1651
1652 Oe_Config_Schedule_Pvt.Query_Set_Lines
1653 (p_header_id => l_header_id,
1654 p_model_line_id => l_top_model_line_id,
1655 p_sch_action => p_sch_action,
1656 x_line_tbl => l_line_tbl,
1657 x_return_status => l_return_status);
1658
1659 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1661 END IF;
1662
1663 IF x_line_tbl.count = 0 THEN
1664 -- Added for Bug 2454163
1665 J := x_line_tbl.count;
1666 FOR I IN 1..l_line_tbl.count LOOP
1667 IF l_line_tbl(I).schedule_status_code is NULL THEN
1668 J := J + 1;
1669 x_line_tbl(J) := l_line_tbl(I);
1670 END IF;
1671 END LOOP;
1672
1673 ELSE
1674 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
1675 ,p_line_tbl => l_line_tbl); */ --Bug-2454163
1676 J := x_line_tbl.count;
1677 FOR I IN 1..l_line_tbl.count LOOP
1678 IF l_line_tbl(I).schedule_status_code is NULL AND
1679 NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
1680 J := J + 1;
1681 x_line_tbl(J) := l_line_tbl(I);
1682 END IF;
1683 END LOOP;
1684 END IF;
1685 ELSE
1686
1687 IF l_schedule_status_code IS NULL THEN
1688 /*
1689 OE_Line_Util.Query_Row
1690 (p_line_id => l_line_id,
1691 x_line_rec => l_line_rec);
1692 */
1693 --4382036
1694 OE_LINE_UTIL.Lock_Row(p_line_id => l_line_id,
1695 p_x_line_rec => l_line_rec,
1696 x_return_status => l_return_status);
1697
1698 l_line_rec.reserved_quantity := 0;
1699 /*Check_Merge_Line(p_line_rec => l_line_rec
1700 ,p_x_line_tbl => x_line_tbl); */ --Bug-2454163
1701
1702 IF NOT Find_Line(x_line_tbl,l_line_rec.line_id) THEN
1703 x_line_tbl(x_line_tbl.count + 1) := l_line_rec;
1704 END IF;
1705
1706 IF l_item_type_code = 'CLASS'
1707 OR l_item_type_code = 'KIT'
1708 THEN
1709
1710 Oe_Config_Schedule_Pvt.Query_Set_Lines
1711 (p_header_id => l_header_id,
1712 p_link_to_line_id => l_line_id,
1713 p_sch_action => p_sch_action,
1714 x_line_tbl => l_line_tbl,
1715 x_return_status => l_return_status);
1716
1717 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1719 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1720 x_line_tbl.delete(x_line_tbl.count);
1721 END IF;
1722
1723 J := x_line_tbl.count;
1724 FOR I IN 1..l_line_tbl.count LOOP
1725 IF l_line_tbl(I).schedule_status_code is NULL
1726 AND NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
1727 J := J + 1;
1728 x_line_tbl(J) := l_line_tbl(I);
1729 END IF;
1730 END LOOP;
1731 END IF; -- Class or Kit.
1732 END IF; -- Schedule status code.
1733
1734 END IF; -- group query or single query.
1735
1736 END IF; -- l_query.
1737
1738 END IF; -- Ignore and do not ignore.
1739
1740 END LOOP;
1741
1742 IF l_debug_level > 0 THEN
1743 oe_debug_pub.add( 'EXITING QUERY_SCHEDULE_LINES' || X_LINE_TBL.COUNT , 1 ) ;
1744 END IF;
1745
1746
1747 EXCEPTION
1748
1749 WHEN FND_API.G_EXC_ERROR THEN
1750 IF l_debug_level > 0 THEN
1751 oe_debug_pub.add( 'EXPECTED ERROR IN QUERY_SCHEDULE_LINES ' , 1 ) ;
1752 END IF;
1753 RAISE FND_API.G_EXC_ERROR;
1754
1755 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1756
1757 IF l_debug_level > 0 THEN
1758 oe_debug_pub.add( 'UNEXPECTED ERROR IN QUERY_SCHEDULE_LINES ' , 1 ) ;
1759 END IF;
1760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1761 -- Start 2742982 --
1762 WHEN NO_DATA_FOUND THEN
1763 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
1764 OE_MSG_PUB.Add;
1765 RAISE FND_API.G_EXC_ERROR;
1766 -- End 2742982 --
1767
1768 WHEN OTHERS THEN
1769
1770 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1771 THEN
1772 OE_MSG_PUB.Add_Exc_Msg
1773 ( G_PKG_NAME
1774 , 'Query_Schedule_Lines'
1775 );
1776 END IF;
1777
1778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1779 END Query_Schedule_Lines;
1780
1781 /*----------------------------------------------------------------
1782 PROCEDURE Query_Unschedule_Lines
1783 Description : This api will be called from schedule_multi_line API when
1784 action requested by user is Unschedule.
1785 System has to perform little validations before passing record back to the user.
1786 Initially system selects few scheduling related attributes to see whether the
1787 line has to be selected for scheduling or not. If it has to be selected then
1788 check if it is part of non smc and model is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
1789 processing. If the line selected is Included item and No SMC, make sure not only
1790 it's modle is selected, also check for its immediate parent. If its immediate
1791 parent is selected then ignore the included item, since included item will be
1792 selected by it's parent.
1793
1794 If a line is part of SMC or ATO or if it is a TOP MODEL, selected whole model
1795 for processing. If it is a non smc class or kit, select its included items if any.
1796 ----------------------------------------------------------------- */
1797 PROCEDURE Query_Unschedule_Lines
1798 ( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
1799 p_sch_action IN VARCHAR2,
1800 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
1801 )
1802 IS
1803 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1804 l_line_rec OE_Order_PUB.Line_rec_Type;
1805 l_line_id NUMBER;
1806 l_arrival_set_id NUMBER;
1807 l_ship_set_id NUMBER;
1808 l_top_model_line_id NUMBER;
1809 l_ato_line_id NUMBER;
1810 l_smc_flag VARCHAR2(1);
1811 l_item_type_code VARCHAR2(30);
1812 l_schedule_status_code VARCHAR2(30);
1813 l_line_category_code VARCHAR2(30);
1814 l_source_type_code VARCHAR2(30);
1815 l_link_to_line_id NUMBER;
1816 l_query VARCHAR2(1);
1817 l_found VARCHAR2(1);
1818 J NUMBER;
1819 l_header_id NUMBER;
1820 l_sales_order_id NUMBER;
1821 l_open_flag VARCHAR2(1);
1822
1823 --
1824 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1825 --
1826 l_orig_sys_document_ref VARCHAR2(50);
1827 l_orig_sys_line_ref VARCHAR2(50);
1828 l_orig_sys_shipment_ref VARCHAR2(50);
1829 l_source_document_type_id NUMBER;
1830 l_change_sequence VARCHAR2(50);
1831 l_source_document_id NUMBER;
1832 l_source_document_line_id NUMBER;
1833 l_order_source_id NUMBER;
1834 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1835 BEGIN
1836
1837 IF l_debug_level > 0 THEN
1838 oe_debug_pub.add( 'ENTERING INTO QUERY UNSCHEDULE LINES ' , 1 ) ;
1839 END IF;
1840 FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
1841
1842 IF l_debug_level > 0 THEN
1843 oe_debug_pub.add( 'PROCESSING LINE_ID' || p_selected_tbl(L).id1 , 2 ) ;
1844 END IF;
1845
1846 --l_line_id := p_line_id_tbl(L);
1847 l_line_id := p_selected_tbl(L).id1;
1848 -- Query required attributes and check does any action is required
1849 -- on the line.
1850 Select Arrival_set_id, Ship_set_id,top_model_line_id,
1851 Ship_model_complete_flag, ato_line_id,item_type_code,
1852 Schedule_status_code,line_category_code,header_id,open_flag,
1853 source_type_code,link_to_line_id,
1854 orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
1855 source_document_type_id, change_sequence, source_document_id,
1856 source_document_line_id, order_source_id
1857 Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
1858 l_smc_flag, l_ato_line_id, l_item_type_code,
1859 l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
1860 l_source_type_code,l_link_to_line_id,
1861 l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
1862 l_source_document_type_id, l_change_sequence, l_source_document_id,
1863 l_source_document_line_id, l_order_source_id
1864 From oe_order_lines_all
1865 Where line_id = l_line_id;
1866
1867 OE_MSG_PUB.set_msg_context
1868 ( p_entity_code => 'LINE'
1869 ,p_entity_id => l_line_id
1870 ,p_header_id => l_header_id
1871 ,p_line_id => l_line_id
1872 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1873 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
1874 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
1875 ,p_change_sequence => l_change_sequence
1876 ,p_source_document_id => l_source_document_id
1877 ,p_source_document_line_id => l_source_document_line_id
1878 ,p_order_source_id => l_order_source_id
1879 ,p_source_document_type_id => l_source_document_type_id);
1880
1881
1882 -- If the line selected is not scheduled. Ignore the line, since
1883 -- no action is required here.
1884
1885 IF l_arrival_set_id is not null
1886 OR l_ship_set_id is not null THEN
1887
1888 IF l_debug_level > 0 THEN
1889 oe_debug_pub.add( 'LINE CANNOT BE UNSCHEDULED IF IT IS PART OF SET' , 1 ) ;
1890 END IF;
1891 FND_MESSAGE.SET_NAME('ONT','OE_SCH_CANNOT_UNSCH_SET');
1892 OE_MSG_PUB.Add;
1893 ELSIF (l_schedule_status_code IS NULL AND
1894 (l_smc_flag = 'Y' OR
1895 l_ato_line_id is not null OR
1896 l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED OR
1897 l_item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
1898 l_item_type_code = OE_GLOBALS.G_ITEM_STANDARD))
1899 OR l_line_category_code = 'RETURN'
1900 OR l_item_type_code = OE_GLOBALS.G_ITEM_SERVICE
1901 OR nvl(l_open_flag,'Y') = 'N'
1902 THEN
1903 -- line is part of ATO/SMC Model or an ato item which is
1904 -- not scheduled. Bipass these lines.
1905 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
1906 OE_MSG_PUB.Add;
1907 IF l_debug_level > 0 THEN
1908 oe_debug_pub.add( 'NOT A VALID LINE' || L_LINE_ID , 2 ) ;
1909 END IF;
1910 ELSE -- line not scheduled, please select the line to schedule.
1911
1912 l_query := 'Y';
1913
1914 -- If user slectes the non smc model and option, then avoid
1915 -- selecting the option, since model will select the option.
1916
1917 IF l_top_model_line_id IS NOT NULL
1918 AND l_smc_flag = 'N'
1919 AND l_top_model_line_id <> l_line_id THEN
1920
1921 -- Check if model is selected by user.
1922 -- If model is selected then ignore the line for query, since
1923 -- model will query the line.
1924
1925 --R12.MOAC
1926 FOR K IN 1..p_selected_tbl.count LOOP
1927 IF l_top_model_line_id =p_selected_tbl(K).id1
1928 OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
1929 l_link_to_line_id = p_selected_tbl(K).id1) THEN
1930 l_query := 'N';
1931 exit;
1932 END IF;
1933 END LOOP;
1934
1935 ELSIF x_line_tbl.count > 0 THEN
1936 -- if the line is not a non smc option or class, then check if the
1937 -- line is present in the table.
1938 -- Check if the line is already present. If line exists then
1939 -- ignore the line.
1940
1941 FOR I IN 1..x_line_tbl.count LOOP
1942 IF l_line_id = x_line_tbl(I).line_id
1943 THEN
1944 l_query := 'N';
1945 Exit;
1946 END IF;
1947 END LOOP;
1948 END IF;
1949
1950 IF l_query = 'Y' THEN
1951
1952 -- Decided to query the line. See what to pass to query sets.
1953 IF l_smc_flag = 'Y'
1954 OR (l_ato_line_id is not null AND
1955 NOT (l_line_id = l_ato_line_id AND
1956 l_item_type_code IN(OE_GLOBALS.G_ITEM_STANDARD,
1957 OE_GLOBALS.G_ITEM_OPTION)))
1958 THEN
1959
1960 -- query using top_model_line_id / ato line id
1961
1962
1963 IF l_smc_flag = 'Y' THEN
1964 Oe_Config_Schedule_Pvt.Query_Set_Lines
1965 (p_header_id => l_header_id,
1966 p_model_line_id => l_top_model_line_id,
1967 p_sch_action => p_sch_action,
1968 x_line_tbl => l_line_tbl,
1969 x_return_status => l_return_status);
1970
1971 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1973 END IF;
1974 ELSE
1975
1976 -- Query ato model.
1977 OE_Config_Util.Query_ATO_Options
1978 ( p_ato_line_id => l_ato_line_id,
1979 x_line_tbl => l_line_tbl);
1980
1981 END IF;
1982
1983 IF x_line_tbl.count = 0 THEN
1984
1985 x_line_tbl := l_line_tbl;
1986
1987 ELSE
1988 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
1989 ,p_line_tbl => l_line_tbl); */ --Bug-2454163
1990
1991 J := x_line_tbl.count;
1992 FOR I IN 1..l_line_tbl.count LOOP
1993 IF NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
1994 J := J +1;
1995 x_line_tbl(J) := l_line_tbl(I);
1996 END IF;
1997 END LOOP;
1998 END IF;
1999
2000 ELSIF l_line_id = l_top_model_line_id THEN
2001
2002 -- This is a non smc top model. User might have selected model
2003 -- and its options If the model is selected query
2004 -- whole model and try to place them in the
2005 -- out table if they are not present already.
2006
2007 Oe_Config_Schedule_Pvt.Query_Set_Lines
2008 (p_header_id => l_header_id,
2009 p_model_line_id => l_top_model_line_id,
2010 p_sch_action => p_sch_action,
2011 x_line_tbl => l_line_tbl,
2012 x_return_status => l_return_status);
2013
2014 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2016 END IF;
2017
2018 IF x_line_tbl.count = 0 THEN
2019 J := x_line_tbl.count;
2020 FOR I IN 1..l_line_tbl.count LOOP
2021 IF l_line_tbl(I).schedule_status_code is NOT NULL THEN
2022 J := J + 1;
2023 x_line_tbl(J) := l_line_tbl(I);
2024 END IF;
2025 END LOOP;
2026
2027 ELSE
2028
2029 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2030 ,p_line_tbl => l_line_tbl); */
2031
2032
2033 J := x_line_tbl.count;
2034 FOR I IN 1..l_line_tbl.count LOOP
2035 IF l_line_tbl(I).schedule_status_code is NOT NULL
2036 AND NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
2037 J := J + 1;
2038 x_line_tbl(J) := l_line_tbl(I);
2039 END IF;
2040 END LOOP;
2041
2042 END IF;
2043 ELSE
2044 -- 4382036
2045 OE_Line_Util.Lock_Row(p_line_id => l_line_id,
2046 p_x_line_rec => l_line_rec,
2047 x_return_status => l_return_status);
2048
2049 /* OE_Line_Util.Query_Row
2050 (p_line_id => l_line_id,
2051 x_line_rec => l_line_rec);
2052 */
2053
2054 IF nvl(l_line_rec.shippable_flag,'N') = 'Y' THEN
2055
2056 IF l_sales_order_id is null THEN
2057 l_sales_order_id :=
2058 OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
2059 (l_line_rec.header_id);
2060 END IF;
2061
2062
2063 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
2064
2065 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
2066 ,p_line_id => l_line_rec.line_id
2067 ,p_org_id => l_line_rec.ship_from_org_id
2068 ,x_reserved_quantity => l_line_rec.reserved_quantity
2069 ,x_reserved_quantity2 => l_line_rec.reserved_quantity2
2070 );
2071
2072
2073 /*l_line_rec.reserved_quantity :=
2074 OE_LINE_UTIL.Get_Reserved_Quantity
2075 (p_header_id => l_sales_order_id,
2076 p_line_id => l_line_rec.line_id,
2077 p_org_id => l_line_rec.ship_from_org_id);
2078 l_line_rec.reserved_quantity2 := -- INVCONV
2079 OE_LINE_UTIL.Get_Reserved_Quantity2
2080 (p_header_id => l_sales_order_id,
2081 p_line_id => l_line_rec.line_id,
2082 p_org_id => l_line_rec.ship_from_org_id); */
2083
2084
2085 END IF;
2086 IF l_line_rec.reserved_quantity IS NULL
2087 OR l_line_rec.reserved_quantity = FND_API.G_MISS_NUM
2088 THEN
2089 l_line_rec.reserved_quantity := 0;
2090 END IF;
2091 IF l_line_rec.reserved_quantity2 IS NULL -- INVCONV
2092 OR l_line_rec.reserved_quantity2 = FND_API.G_MISS_NUM
2093 THEN
2094 l_line_rec.reserved_quantity2 := 0;
2095 END IF;
2096
2097
2098
2099 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2100 ,p_line_rec => l_line_rec); */ -- Bug-2454163
2101
2102 IF NOT Find_Line(x_line_tbl,l_line_rec.line_id) THEN
2103 x_line_tbl(x_line_tbl.count + 1) := l_line_rec;
2104 END IF;
2105
2106 IF l_item_type_code = 'CLASS'
2107 OR l_item_type_code = 'KIT'
2108 THEN
2109
2110 Oe_Config_Schedule_Pvt.Query_Set_Lines
2111 (p_header_id => l_header_id,
2112 p_link_to_line_id => l_line_id,
2113 p_sch_action => p_sch_action,
2114 x_line_tbl => l_line_tbl,
2115 x_return_status => l_return_status);
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 THEN
2120 x_line_tbl.delete(x_line_tbl.count);
2121 END IF;
2122
2123
2124 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2125 ,p_line_tbl => l_line_tbl); */ --Bug-2454163
2126
2127 J := x_line_tbl.count;
2128 FOR I IN 1..l_line_tbl.count LOOP
2129 IF l_line_tbl(I).schedule_status_code is NOT NULL
2130 AND NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
2131 J := J + 1;
2132 x_line_tbl(J) := l_line_tbl(I);
2133 END IF;
2134 END LOOP;
2135
2136 END IF; -- Class or Kit.
2137 END IF; -- group query or single query.
2138
2139 END IF; -- l_query.
2140
2141 END IF; -- Ignore and do not ignore.
2142
2143
2144 END LOOP;
2145
2146
2147 EXCEPTION
2148
2149 WHEN FND_API.G_EXC_ERROR THEN
2150 IF l_debug_level > 0 THEN
2151 oe_debug_pub.add( 'EXPECTED ERROR IN QUERY_UNSCHEDULE_LINES ' , 1 ) ;
2152 END IF;
2153 RAISE FND_API.G_EXC_ERROR;
2154
2155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2156
2157 IF l_debug_level > 0 THEN
2158 oe_debug_pub.add( 'UNEXPECTED ERROR IN QUERY_UNSCHEDULE_LINES ' , 1 ) ;
2159 END IF;
2160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2161
2162 -- Start 2742982 --
2163 WHEN NO_DATA_FOUND THEN
2164 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
2165 OE_MSG_PUB.Add;
2166 RAISE FND_API.G_EXC_ERROR;
2167 -- End 2742982 --
2168
2169 WHEN OTHERS THEN
2170
2171 IF l_debug_level > 0 THEN
2172 oe_debug_pub.add( ' WHEN OTHERS ERROR IN QUERY_UNSCHEDULE_LINES ' , 1 ) ;
2173 END IF;
2174 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2175 THEN
2176 OE_MSG_PUB.Add_Exc_Msg
2177 ( G_PKG_NAME
2178 , 'Query_Unschedule_Lines'
2179 );
2180 END IF;
2181
2182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2183
2184 END Query_Unschedule_Lines;
2185
2186 /*----------------------------------------------------------------
2187 PROCEDURE Query_Unreserve_Lines
2188 Description : This api will be called from schedule_multi_line API when
2189 action requested by user is Unreserve.
2190 System has to perform little validations before passing record back to the user.
2191 Initially system selects few scheduling related attributes to see whether the
2192 line has to be selected for Unreserve or not. If it has to be selected then
2193 check if it is part of model and parent is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
2194 processing. If the line selected is Included item, make sure not only it's modle is selected, also check for its immediate parent. If its immediate parent is selected then ignore the included item, since included item will be selected by it's parent.
2195
2196 If line is a top model selected all its children that are scheduled and it is a class or kit select its included items as well or else select by itself.
2197
2198 ----------------------------------------------------------------- */
2199 PROCEDURE Query_Unreserve_Lines
2200 ( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
2201 p_sch_action IN VARCHAR2,
2202 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
2203 )
2204 IS
2205 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
2206 l_line_rec OE_Order_PUB.Line_rec_Type;
2207 l_line_id NUMBER;
2208 l_arrival_set_id NUMBER;
2209 l_ship_set_id NUMBER;
2210 l_top_model_line_id NUMBER;
2211 l_ato_line_id NUMBER;
2212 l_smc_flag VARCHAR2(1);
2213 l_shipping_interfaced_flag VARCHAR2(1);
2214 l_item_type_code VARCHAR2(30);
2215 l_schedule_status_code VARCHAR2(30);
2216 l_line_category_code VARCHAR2(30);
2217 l_source_type_code VARCHAR2(30);
2218 l_link_to_line_id NUMBER;
2219 l_query VARCHAR2(1);
2220 l_found VARCHAR2(1);
2221 J NUMBER;
2222 l_header_id NUMBER;
2223 l_sales_order_id NUMBER;
2224 l_open_flag VARCHAR2(1);
2225
2226 --
2227 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2228 --
2229 l_orig_sys_document_ref VARCHAR2(50);
2230 l_orig_sys_line_ref VARCHAR2(50);
2231 l_orig_sys_shipment_ref VARCHAR2(50);
2232 l_source_document_type_id NUMBER;
2233 l_change_sequence VARCHAR2(50);
2234 l_source_document_id NUMBER;
2235 l_source_document_line_id NUMBER;
2236 l_order_source_id NUMBER;
2237 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2238 BEGIN
2239
2240 FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
2241
2242
2243 IF l_debug_level > 0 THEN
2244 oe_debug_pub.add( 'PROCESSING LINE_ID' || P_SELECTED_TBL(L).ID1 , 2 ) ;
2245 END IF;
2246
2247 l_line_id := p_selected_tbl(L).id1;
2248 -- Query required attributes and check does any action is required
2249 -- on the line.
2250 Select Arrival_set_id, Ship_set_id,top_model_line_id,
2251 Ship_model_complete_flag, ato_line_id,item_type_code,
2252 Schedule_status_code,line_category_code,header_id,open_flag,
2253 nvl(shipping_interfaced_flag,'N'),source_type_code,link_to_line_id,
2254 orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
2255 source_document_type_id, change_sequence, source_document_id,
2256 source_document_line_id, order_source_id
2257 Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
2258 l_smc_flag, l_ato_line_id, l_item_type_code,
2259 l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
2260 l_shipping_interfaced_flag,l_source_type_code,l_link_to_line_id,
2261 l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
2262 l_source_document_type_id, l_change_sequence, l_source_document_id,
2263 l_source_document_line_id, l_order_source_id
2264 From oe_order_lines_all
2265 Where line_id = l_line_id;
2266
2267 OE_MSG_PUB.set_msg_context
2268 ( p_entity_code => 'LINE'
2269 ,p_entity_id => l_line_id
2270 ,p_header_id => l_header_id
2271 ,p_line_id => l_line_id
2272 ,p_orig_sys_document_ref => l_orig_sys_document_ref
2273 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
2274 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
2275 ,p_change_sequence => l_change_sequence
2276 ,p_source_document_id => l_source_document_id
2277 ,p_source_document_line_id => l_source_document_line_id
2278 ,p_order_source_id => l_order_source_id
2279 ,p_source_document_type_id => l_source_document_type_id);
2280
2281 -- If the line selected is not scheduled. Ignore the line, since
2282 -- no action is required here.
2283
2284 IF l_schedule_status_code IS NULL
2285 OR nvl(l_open_flag,'Y') = 'N'
2286 OR (l_shipping_interfaced_flag = 'Y'
2287 AND oe_schedule_util.Get_Pick_Status(l_line_id))
2288 THEN -- 2595661
2289 -- Go inside only if they are scheduled.
2290 -- and not part of set.
2291 IF l_shipping_interfaced_flag = 'Y' THEN
2292
2293 FND_MESSAGE.SET_NAME('ONT','OE_SCH_UNRSV_NOT_ALLOWED');
2294 OE_MSG_PUB.Add;
2295 IF l_debug_level > 0 THEN
2296 oe_debug_pub.add( 'LINE IS SHIPPING INTERFACED' || L_LINE_ID , 1 ) ;
2297 END IF;
2298
2299 END IF;
2300 ELSE -- line not scheduled, please select the line to schedule.
2301
2302 l_query := 'Y';
2303
2304 -- If user slectes the model and option, then avoid selecting the
2305 -- option, since model will select the option.
2306
2307 IF l_top_model_line_id IS NOT NULL
2308 AND l_top_model_line_id <> l_line_id THEN
2309
2310 -- Check if model is selected by user.
2311 -- If model is selected then ignore the line for query, since
2312 -- model will query the line.
2313
2314 --R12.MOAC
2315 FOR K IN 1..p_selected_tbl.count LOOP
2316 IF l_top_model_line_id = p_selected_tbl(K).id1
2317 OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
2318 l_link_to_line_id = p_selected_tbl(K).id1) THEN
2319 l_query := 'N';
2320 exit;
2321 END IF;
2322 END LOOP;
2323 END IF;
2324
2325 IF l_query = 'Y' THEN
2326
2327 -- Decided to query the line. See what to pass to query sets.
2328 IF l_line_id = l_top_model_line_id THEN
2329
2330 -- This is a non smc top model. User might have selected model
2331 -- and its options. If the model is selected query
2332 -- whole model and try to place them in the
2333 -- out table if they are not present already.
2334
2335 Oe_Config_Schedule_Pvt.Query_Set_Lines
2336 (p_header_id => l_header_id,
2337 p_model_line_id => l_top_model_line_id,
2338 p_sch_action => p_sch_action,
2339 x_line_tbl => l_line_tbl,
2340 x_return_status => l_return_status);
2341
2342 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2344 END IF;
2345
2346
2347 IF x_line_tbl.count = 0 THEN
2348 J := x_line_tbl.count;
2349 FOR I IN 1..l_line_tbl.count LOOP
2350 IF l_line_tbl(I).schedule_status_code is NOT NULL THEN
2351 J := J + 1;
2352 x_line_tbl(J) := l_line_tbl(I);
2353 END IF;
2354 END LOOP;
2355 ELSE
2356 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2357 ,p_line_tbl => l_line_tbl); */ --Bug-2454163
2358
2359 J := x_line_tbl.count;
2360 FOR I IN 1..l_line_tbl.count LOOP
2361 IF l_line_tbl(I).schedule_status_code is NOT NULL
2362 AND NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
2363 J := J + 1;
2364 x_line_tbl(J) := l_line_tbl(I);
2365 END IF;
2366 END LOOP;
2367
2368 END IF;
2369 ELSE
2370
2371 -- 4382036
2372 OE_Line_Util.Lock_Row(p_line_id => l_line_id,
2373 p_x_line_rec => l_line_rec,
2374 x_return_status => l_return_status);
2375
2376 /* OE_Line_Util.Query_Row
2377 (p_line_id => l_line_id,
2378 x_line_rec => l_line_rec);
2379 */
2380
2381 IF nvl(l_line_rec.shippable_flag,'N') = 'Y' THEN
2382
2383 IF l_sales_order_id is null THEN
2384 l_sales_order_id :=
2385 OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
2386 (l_line_rec.header_id);
2387 END IF;
2388
2389 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
2390
2391 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
2392 ,p_line_id => l_line_rec.line_id
2393 ,p_org_id => l_line_rec.ship_from_org_id
2394 ,x_reserved_quantity => l_line_rec.reserved_quantity
2395 ,x_reserved_quantity2 => l_line_rec.reserved_quantity2
2396 );
2397
2398 /*l_line_rec.reserved_quantity :=
2399 OE_LINE_UTIL.Get_Reserved_Quantity
2400 (p_header_id => l_sales_order_id,
2401 p_line_id => l_line_rec.line_id,
2402 p_org_id => l_line_rec.ship_from_org_id);
2403 l_line_rec.reserved_quantity2 := -- INVCONV
2404 OE_LINE_UTIL.Get_Reserved_Quantity2
2405 (p_header_id => l_sales_order_id,
2406 p_line_id => l_line_rec.line_id,
2407 p_org_id => l_line_rec.ship_from_org_id); */
2408 END IF;
2409
2410 IF l_line_rec.reserved_quantity IS NULL
2411 OR l_line_rec.reserved_quantity = FND_API.G_MISS_NUM
2412 THEN
2413 l_line_rec.reserved_quantity := 0;
2414 END IF;
2415 /*IF l_line_rec.reserved_quantity2 IS NULL -- INVCONV
2416 OR l_line_rec.reserved_quantity2 = FND_API.G_MISS_NUM
2417 THEN
2418 l_line_rec.reserved_quantity2 := 0;
2419 END IF; */
2420
2421
2422 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2423 ,p_line_rec => l_line_rec); */ --Bug-2454163
2424
2425 IF NOT Find_Line(x_line_tbl,l_line_rec.line_id) THEN
2426 x_line_tbl(x_line_tbl.count + 1) := l_line_rec;
2427 END IF;
2428
2429 IF l_item_type_code = 'CLASS'
2430 OR l_item_type_code = 'KIT'
2431 THEN
2432
2433 Oe_Config_Schedule_Pvt.Query_Set_Lines
2434 (p_header_id => l_header_id,
2435 p_link_to_line_id => l_line_id,
2436 p_sch_action => p_sch_action,
2437 x_line_tbl => l_line_tbl,
2438 x_return_status => l_return_status);
2439
2440 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2442 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2443 x_line_tbl.delete(x_line_tbl.count);
2444 END IF;
2445
2446 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2447 ,p_line_tbl => l_line_tbl); */ --Bug-2454163
2448
2449
2450 J := x_line_tbl.count;
2451 FOR I IN 1..l_line_tbl.count LOOP
2452 IF NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id)
2453 AND l_line_tbl(I).schedule_status_code IS NOT NULL THEN
2454 J := J + 1;
2455 x_line_tbl(J) := l_line_tbl(I);
2456 END IF;
2457 END LOOP;
2458 END IF; -- Class or Kit.
2459 END IF; -- group query or single query.
2460
2461 END IF; -- l_query.
2462
2463 END IF; -- Ignore and do not ignore.
2464
2465
2466 END LOOP;
2467
2468
2469
2470 EXCEPTION
2471
2472
2473 WHEN FND_API.G_EXC_ERROR THEN
2474 IF l_debug_level > 0 THEN
2475 oe_debug_pub.add( 'EXPECTED ERROR IN QUERY_UNRESERVE_LINES ' , 1 ) ;
2476 END IF;
2477 RAISE FND_API.G_EXC_ERROR;
2478
2479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2480
2481 IF l_debug_level > 0 THEN
2482 oe_debug_pub.add( 'EXPECTED ERROR IN QUERY_UNRESERVE_LINES ' , 1 ) ;
2483 END IF;
2484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2485
2486 -- Start 2742982 --
2487 WHEN NO_DATA_FOUND THEN
2488 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
2489 OE_MSG_PUB.Add;
2490 RAISE FND_API.G_EXC_ERROR;
2491 -- End 2742982 --
2492
2493 WHEN OTHERS THEN
2494
2495 IF l_debug_level > 0 THEN
2496 oe_debug_pub.add( ' WHEN OTHERS ERROR IN QUERY_UNRESERVE_LINES ' , 1 ) ;
2497 END IF;
2498 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2499 THEN
2500 OE_MSG_PUB.Add_Exc_Msg
2501 ( G_PKG_NAME
2502 , 'Query_Unreserve_Lines'
2503 );
2504 END IF;
2505
2506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2507 END Query_Unreserve_Lines;
2508
2509 /*----------------------------------------------------------------
2510 PROCEDURE Query_Reserve_Lines
2511 Description : This api will be called from schedule_multi_line API when
2512 action requested by user is reserve.
2513 System has to perform little validations before passing record back to the user.
2514 Initially system selects few scheduling related attributes to see whether the
2515 line has to be selected for reserve or not. If it has to be selected then
2516 check if it is part of model and parent is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
2517 processing. If the line selected is Included item, make sure not only it's
2518 modle is selected, also check for its immediate parent. If its immediate parent
2519 is selected then ignore the included item, since included item will be selected
2520 by it's parent.
2521
2522 If top model is selected for processing then query whole model. If a line
2523 selected is part of smc or ATO model and if it is not scheduled, then select
2524 whole model or else select only the line for reservation. If class or kit is
2525 selected then select its included items as well.
2526
2527 ------------------------------------------------------------------*/
2528 PROCEDURE Query_Reserve_Lines
2529 ( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
2530 p_sch_action IN VARCHAR2,
2531 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
2532 )
2533 IS
2534 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
2535 l_line_rec OE_Order_PUB.Line_rec_Type;
2536 l_line_id NUMBER;
2537 l_arrival_set_id NUMBER;
2538 l_ship_set_id NUMBER;
2539 l_top_model_line_id NUMBER;
2540 l_ato_line_id NUMBER;
2541 l_smc_flag VARCHAR2(1);
2542 l_item_type_code VARCHAR2(30);
2543 l_schedule_status_code VARCHAR2(30);
2544 l_line_category_code VARCHAR2(30);
2545 l_link_to_line_id NUMBER;
2546 l_source_type_code VARCHAR2(30);
2547 l_query VARCHAR2(1);
2548 l_found VARCHAR2(1);
2549 J NUMBER;
2550 l_header_id NUMBER;
2551 l_config_id NUMBER;
2552 l_sales_order_id NUMBER := Null;
2553 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
2554 l_open_flag VARCHAR2(1);
2555 --
2556 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2557 --
2558 l_orig_sys_document_ref VARCHAR2(50);
2559 l_orig_sys_line_ref VARCHAR2(50);
2560 l_orig_sys_shipment_ref VARCHAR2(50);
2561 l_source_document_type_id NUMBER;
2562 l_change_sequence VARCHAR2(50);
2563 l_source_document_id NUMBER;
2564 l_source_document_line_id NUMBER;
2565 l_order_source_id NUMBER;
2566 l_quantity_to_reserve NUMBER;
2567 l_quantity2_to_reserve NUMBER; -- INVCONV
2568 l_rsv_update BOOLEAN :=FALSE;
2569 BEGIN
2570
2571 IF l_debug_level > 0 THEN
2572 oe_debug_pub.add( 'ENTERING QUERY RESERVE LINES' , 1 ) ;
2573 END IF;
2574 FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
2575
2576 IF l_debug_level > 0 THEN
2577 oe_debug_pub.add( 'PROCESSING LINE_ID' || P_SELECTED_TBL(L).ID1 , 2 ) ;
2578 END IF;
2579
2580 l_line_id := p_selected_tbl(L).id1;
2581 -- Query required attributes and check does any action is required
2582 -- on the line.
2583 Select Arrival_set_id, Ship_set_id,top_model_line_id,
2584 Ship_model_complete_flag, ato_line_id,item_type_code,
2585 Schedule_status_code,line_category_code,header_id,open_flag,
2586 source_type_code,link_to_line_id,
2587 orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
2588 source_document_type_id, change_sequence, source_document_id,
2589 source_document_line_id, order_source_id
2590 Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
2591 l_smc_flag, l_ato_line_id, l_item_type_code,
2592 l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
2593 l_source_type_code,l_link_to_line_id,
2594 l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
2595 l_source_document_type_id, l_change_sequence, l_source_document_id,
2596 l_source_document_line_id, l_order_source_id
2597 From oe_order_lines_all
2598 Where line_id = l_line_id;
2599
2600 OE_MSG_PUB.set_msg_context
2601 ( p_entity_code => 'LINE'
2602 ,p_entity_id => l_line_id
2603 ,p_header_id => l_header_id
2604 ,p_line_id => l_line_id
2605 ,p_orig_sys_document_ref => l_orig_sys_document_ref
2606 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
2607 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
2608 ,p_change_sequence => l_change_sequence
2609 ,p_source_document_id => l_source_document_id
2610 ,p_source_document_line_id => l_source_document_line_id
2611 ,p_order_source_id => l_order_source_id
2612 ,p_source_document_type_id => l_source_document_type_id);
2613
2614
2615 IF l_line_category_code = 'RETURN'
2616 OR l_item_type_code = 'SERVICE'
2617 OR l_source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL
2618 OR nvl(l_open_flag,'Y') = 'N' THEN
2619
2620 -- populate the error message and skip the line.
2621 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
2622 OE_MSG_PUB.Add;
2623 IF l_debug_level > 0 THEN
2624 oe_debug_pub.add( 'NOT A VALIDE LINE' || L_LINE_ID , 2 ) ;
2625 END IF;
2626
2627 ELSE
2628 l_query := 'Y';
2629
2630 --If user slectes the non smc model and option, then avoid selecting the
2631 --option, since model will select the option.
2632
2633 IF l_top_model_line_id IS NOT NULL
2634 AND l_smc_flag = 'N'
2635 AND l_top_model_line_id <> l_line_id THEN
2636
2637 -- Check if model is selected by user.
2638 -- If model is selected then ignore the line for query, since
2639 -- model will query the line.
2640 --R12.MOAC
2641 FOR K IN 1..p_selected_tbl.count LOOP
2642 IF l_top_model_line_id = p_selected_tbl(K).id1
2643 OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
2644 l_link_to_line_id = p_selected_tbl(K).id1) THEN
2645 l_query := 'N';
2646 exit;
2647 END IF;
2648 END LOOP;
2649
2650 ELSIF l_line_tbl.count > 0 THEN
2651 -- if the line is not a non smc option or class, then check if the
2652 -- line is present in the table.
2653 -- Check if the line is already present. If line exists then
2654 -- ignore the line.
2655
2656 FOR I IN 1..l_line_tbl.count LOOP
2657 IF l_line_id = l_line_tbl(I).line_id
2658 THEN
2659 l_query := 'N';
2660 Exit;
2661 END IF;
2662 END LOOP;
2663 END IF;
2664
2665 IF l_query = 'Y' THEN
2666
2667 -- Decided to query the line. See what to pass to query sets.
2668 -- If line is part of smc or ATO if it is not scheduled , then select
2669 -- whole model or else select only that line to reserve.
2670
2671 IF (l_smc_flag = 'Y'
2672 AND l_schedule_status_code is null)
2673 OR (l_line_id = l_top_model_line_id
2674 AND l_ato_line_id is null) THEN
2675
2676 -- This is a non smc top model. User might have selected model
2677 -- and its options.If the model is selected query whole
2678 -- model and try to place them in the
2679 -- out table if they are not present already.
2680
2681 Oe_Config_Schedule_Pvt.Query_Set_Lines
2682 (p_header_id => l_header_id,
2683 p_model_line_id => l_top_model_line_id,
2684 p_sch_action => p_sch_action,
2685 x_line_tbl => x_line_tbl,
2686 x_return_status => l_return_status);
2687
2688 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2690 END IF;
2691
2692 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2693 ,p_line_tbl => l_line_tbl); */ -- Bug-2454163
2694
2695 IF l_line_tbl.count = 0 THEN
2696 l_line_tbl := x_line_tbl;
2697 ELSE
2698
2699 J := l_line_tbl.count;
2700 FOR I IN 1..x_line_tbl.count LOOP
2701 IF NOT Find_Line(l_line_tbl,x_line_tbl(I).line_id) THEN
2702 J := J + 1;
2703 l_line_tbl(J) := x_line_tbl(I);
2704 END IF;
2705 END LOOP;
2706
2707 END IF;
2708
2709
2710 ELSIF (l_ato_line_id is not null AND
2711 NOT (l_line_id = l_ato_line_id AND
2712 l_item_type_code IN(OE_GLOBALS.G_ITEM_STANDARD,
2713 OE_GLOBALS.G_ITEM_OPTION)))
2714 AND l_schedule_status_code is null
2715 THEN
2716
2717 -- Query ato model.
2718 OE_Config_Util.Query_ATO_Options
2719 (p_ato_line_id => l_ato_line_id,
2720 x_line_tbl => x_line_tbl);
2721
2722 IF l_line_tbl.count = 0 THEN
2723 l_line_tbl := x_line_tbl;
2724 ELSE
2725
2726 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2727 ,p_line_tbl => l_line_tbl); */
2728
2729
2730 J := l_line_tbl.count;
2731 FOR I IN 1..x_line_tbl.count LOOP
2732 IF NOT Find_Line(l_line_tbl,x_line_tbl(I).line_id) THEN
2733 J := J + 1;
2734 l_line_tbl(J) := x_line_tbl(I);
2735 END IF;
2736 END LOOP;
2737
2738
2739 END IF;
2740
2741 -- 2746802
2742 ELSIF l_ato_line_id = l_line_id
2743 AND l_item_type_code = OE_GLOBALS.G_ITEM_MODEL
2744 AND l_schedule_status_code is not null THEN
2745
2746 l_config_id := Null;
2747
2748 BEGIN
2749
2750 SELECT line_id
2751 INTO l_config_id
2752 FROM oe_order_lines_all
2753 WHERE header_id = l_header_id
2754 AND ato_line_id = l_ato_line_id
2755 AND item_type_code = 'CONFIG';
2756
2757 EXCEPTION
2758 When Others Then
2759
2760 l_config_id := Null;
2761 END;
2762
2763 IF l_config_id IS NOT NULL THEN
2764
2765 IF l_debug_level > 0 THEN
2766 oe_debug_pub.add( 'NEED TO RESERVE THE LINE' , 2 ) ;
2767 END IF;
2768
2769 OE_Line_Util.Query_Row
2770 (p_line_id => l_config_id,
2771 x_line_rec => l_line_rec);
2772
2773 IF nvl(l_line_rec.shippable_flag,'N') = 'Y' THEN
2774
2775 IF l_sales_order_id is null THEN
2776 l_sales_order_id :=
2777 OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
2778 (l_line_rec.header_id);
2779 END IF;
2780
2781 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
2782
2783 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
2784 ,p_line_id => l_line_rec.line_id
2785 ,p_org_id => l_line_rec.ship_from_org_id
2786 ,x_reserved_quantity => l_line_rec.reserved_quantity
2787 ,x_reserved_quantity2 => l_line_rec.reserved_quantity2
2788 );
2789
2790 /*l_line_rec.reserved_quantity :=
2791 OE_LINE_UTIL.Get_Reserved_Quantity
2792 (p_header_id => l_sales_order_id,
2793 p_line_id => l_line_rec.line_id,
2794 p_org_id => l_line_rec.ship_from_org_id);
2795 l_line_rec.reserved_quantity2 := -- INVCONV
2796 OE_LINE_UTIL.Get_Reserved_Quantity2
2797 (p_header_id => l_sales_order_id,
2798 p_line_id => l_line_rec.line_id,
2799 p_org_id => l_line_rec.ship_from_org_id); */
2800
2801
2802 END IF;
2803 IF l_line_rec.reserved_quantity IS NULL
2804 OR l_line_rec.reserved_quantity = FND_API.G_MISS_NUM
2805 THEN
2806 l_line_rec.reserved_quantity := 0;
2807 END IF;
2808 /*IF l_line_rec.reserved_quantity2 IS NULL -- INVCONV
2809 OR l_line_rec.reserved_quantity2 = FND_API.G_MISS_NUM
2810 THEN
2811 l_line_rec.reserved_quantity2 := 0;
2812 END IF; */
2813
2814
2815
2816 IF NOT Find_Line(l_line_tbl,l_line_rec.line_id) THEN
2817 l_line_tbl(l_line_tbl.count + 1) := l_line_rec;
2818 END IF;
2819
2820 END IF;
2821 ELSE
2822
2823 -- 4382036
2824 OE_Line_Util.Lock_Row(p_line_id => l_line_id,
2825 p_x_line_rec => l_line_rec,
2826 x_return_status => l_return_status);
2827
2828 /* OE_Line_Util.Query_Row
2829 (p_line_id => l_line_id,
2830 x_line_rec => l_line_rec);
2831 */
2832
2833 IF nvl(l_line_rec.shippable_flag,'N') = 'Y' THEN
2834
2835 IF l_sales_order_id is null THEN
2836 l_sales_order_id :=
2837 OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
2838 (l_line_rec.header_id);
2839 END IF;
2840
2841 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
2842
2843 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
2844 ,p_line_id => l_line_rec.line_id
2845 ,p_org_id => l_line_rec.ship_from_org_id
2846 ,x_reserved_quantity => l_line_rec.reserved_quantity
2847 ,x_reserved_quantity2 => l_line_rec.reserved_quantity2
2848 );
2849
2850 /* l_line_rec.reserved_quantity :=
2851 OE_LINE_UTIL.Get_Reserved_Quantity
2852 (p_header_id => l_sales_order_id,
2853 p_line_id => l_line_rec.line_id,
2854 p_org_id => l_line_rec.ship_from_org_id);
2855 l_line_rec.reserved_quantity2 :=
2856 OE_LINE_UTIL.Get_Reserved_Quantity2
2857 (p_header_id => l_sales_order_id,
2858 p_line_id => l_line_rec.line_id,
2859 p_org_id => l_line_rec.ship_from_org_id); */
2860
2861 END IF;
2862 IF l_line_rec.reserved_quantity IS NULL
2863 OR l_line_rec.reserved_quantity = FND_API.G_MISS_NUM
2864 THEN
2865 l_line_rec.reserved_quantity := 0;
2866 END IF;
2867 /* IF l_line_rec.reserved_quantity2 IS NULL -- INVCONV
2868 OR l_line_rec.reserved_quantity2 = FND_API.G_MISS_NUM
2869 THEN
2870 l_line_rec.reserved_quantity2 := 0;
2871 END IF; */
2872
2873
2874 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2875 , p_line_rec => l_line_rec ); */ -- Bug-2454163
2876
2877 IF NOT Find_Line(l_line_tbl,l_line_rec.line_id) THEN
2878 l_line_tbl(l_line_tbl.count + 1) := l_line_rec;
2879 END IF;
2880
2881 IF l_item_type_code = 'CLASS'
2882 OR l_item_type_code = 'KIT'
2883 THEN
2884
2885 Oe_Config_Schedule_Pvt.Query_Set_Lines
2886 (p_header_id => l_header_id,
2887 p_link_to_line_id => l_line_id,
2888 p_sch_action => p_sch_action,
2889 x_line_tbl => x_line_tbl,
2890 x_return_status => l_return_status);
2891
2892 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2894 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2895 l_line_tbl.delete(l_line_tbl.count);
2896 END IF;
2897
2898
2899 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
2900 ,p_line_tbl => l_line_tbl); */ --Bug-2454163
2901
2902 J := l_line_tbl.count;
2903 FOR I IN 1..x_line_tbl.count LOOP
2904 IF NOT Find_Line(l_line_tbl,x_line_tbl(I).line_id) THEN
2905 J := J + 1;
2906 l_line_tbl(J) := x_line_tbl(I);
2907 END IF;
2908 END LOOP;
2909
2910
2911 END IF; -- Class or Kit.
2912
2913 END IF; -- group query or single query.
2914
2915 END IF; -- l_query.
2916
2917
2918 END IF; -- do or do not.
2919
2920 END LOOP;
2921
2922 /* Bug 2319608
2923 FOR k IN 1..x_line_tbl.COUNT LOOP
2924 l_line_tbl(k) := x_line_tbl(k);
2925 END LOOP;
2926 */
2927 x_line_tbl.delete;
2928 J := 0;
2929 FOR I IN 1..l_line_tbl.count LOOP
2930
2931 OE_MSG_PUB.set_msg_context
2932 ( p_entity_code => 'LINE'
2933 ,p_entity_id => l_line_tbl(I).line_id
2934 ,p_header_id => l_line_tbl(I).header_id
2935 ,p_line_id => l_line_tbl(I).line_id
2936 ,p_orig_sys_document_ref => l_line_tbl(I).orig_sys_document_ref
2937 ,p_orig_sys_document_line_ref => l_line_tbl(I).orig_sys_line_ref
2938 ,p_orig_sys_shipment_ref => l_line_tbl(I).orig_sys_shipment_ref
2939 ,p_change_sequence => l_line_tbl(I).change_sequence
2940 ,p_source_document_id => l_line_tbl(I).source_document_id
2941 ,p_source_document_line_id => l_line_tbl(I).source_document_line_id
2942 ,p_order_source_id => l_line_tbl(I).order_source_id
2943 ,p_source_document_type_id => l_line_tbl(I).source_document_type_id);
2944
2945
2946 IF l_line_tbl(I).schedule_status_code is not null THEN
2947
2948 -- Pack J
2949 -- Check for Partial reservation flag when the line is partially reserved
2950 IF nvl(l_line_tbl(I).shippable_flag,'N') = 'Y'
2951 AND ((l_line_tbl(I).reserved_quantity = 0)
2952 OR ( OE_CODE_CONTROL.Get_Code_Release_Level >= '110510'
2953 --AND OE_SYS_PARAMETERS.value('PARTIAL_RESERVATION_FLAG')= 'Y'
2954 AND l_line_tbl(I).ordered_quantity >
2955 l_line_tbl(I).reserved_quantity)) THEN
2956
2957 IF l_debug_level > 0 THEN
2958 oe_debug_pub.add( 'NEED TO RESERVE THE LINE' , 2 ) ;
2959 END IF;
2960
2961 -- Check if the line is eligible for reservation.
2962 l_return_status := FND_API.G_RET_STS_SUCCESS;
2963 OE_SCHEDULE_UTIL.Validate_Line(p_line_rec => l_line_tbl(I),
2964 p_old_line_rec => l_line_tbl(I),
2965 p_sch_action => p_sch_action,
2966 x_return_status => l_return_status);
2967
2968 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2969 --Pack J
2970 -- To calculate the remaining quantity to be reserved
2971 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
2972 --AND OE_SYS_PARAMETERS.value('PARTIAL_RESERVATION_FLAG')= 'Y'
2973 AND l_line_tbl(I).ordered_quantity >
2974 l_line_tbl(I).reserved_quantity THEN
2975 l_quantity_to_reserve := l_line_tbl(I).ordered_quantity - l_line_tbl(I).reserved_quantity;
2976 ELSE
2977 l_quantity_to_reserve := l_line_tbl(I).ordered_quantity;
2978 END IF;
2979
2980 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' -- INVCONV
2981 --AND OE_SYS_PARAMETERS.value('PARTIAL_RESERVATION_FLAG')= 'Y'
2982 AND l_line_tbl(I).ordered_quantity2 >
2983 l_line_tbl(I).reserved_quantity2 THEN
2984 l_quantity2_to_reserve := l_line_tbl(I).ordered_quantity2 - l_line_tbl(I).reserved_quantity2;
2985 ELSE
2986 l_quantity2_to_reserve := l_line_tbl(I).ordered_quantity2;
2987 END IF;
2988
2989 IF l_quantity2_to_reserve = 0 -- INVCONV
2990 THEN
2991 l_quantity2_to_reserve := NULL;
2992 END IF;
2993
2994 IF l_debug_level > 0 THEN
2995 oe_debug_pub.add( 'QUANTITY TO RESERVE '||l_quantity_to_reserve , 2 ) ;
2996 oe_debug_pub.add( 'QUANTITY2 TO RESERVE '||l_quantity2_to_reserve , 2 ) ;
2997 END IF;
2998 IF nvl(l_line_tbl(I).reserved_quantity,0) > 0 THEN
2999 l_rsv_update := TRUE;
3000 END IF;
3001
3002 OE_SCHEDULE_UTIL.Reserve_Line
3003 (p_line_rec => l_line_tbl(I)
3004 ,p_quantity_to_reserve => l_quantity_to_reserve --l_line_tbl(I).ordered_quantity
3005 ,p_quantity2_to_reserve => l_quantity2_to_reserve --l_line_tbl(I).ordered_quantity2 -- INVCONV
3006 ,p_rsv_update => l_rsv_update
3007 ,x_return_status => l_return_status);
3008
3009 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3010 IF l_debug_level > 0 THEN
3011 oe_debug_pub.add( 'RAISING UNEXPECTED ERROR' , 1 ) ;
3012 END IF;
3013 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3014 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3015 IF l_debug_level > 0 THEN
3016 oe_debug_pub.add( 'WILL IGNORE THE LINE AND PROCEED' , 1 ) ;
3017 END IF;
3018 l_return_status := FND_API.G_RET_STS_SUCCESS;
3019 END IF;
3020
3021 END IF; -- Return status
3022 ELSE
3023 -- code fix for 3300528
3024 IF nvl(l_line_tbl(I).shippable_flag,'N') = 'N'
3025 THEN
3026 IF l_line_tbl(I).ato_line_id IS NOT NULL AND
3027 NOT ( l_line_tbl(I).ato_line_id = l_line_tbl(I).line_id AND
3028 l_line_tbl(I).item_type_code IN ( OE_GLOBALS.G_ITEM_OPTION,
3029 OE_GLOBALS.G_ITEM_STANDARD))
3030 THEN -- check for ato
3031 FND_MESSAGE.SET_NAME('ONT','OE_SCH_RES_NO_CONFIG');
3032 IF l_debug_level > 0 THEN
3033 oe_debug_pub.add( 'NON SHIPPABLE ATO LINE' , 2 ) ;
3034 END IF;
3035 ELSE
3036 FND_MESSAGE.SET_NAME('ONT','ONT_SCH_NOT_RESERVABLE');
3037 END IF;-- end check for ato
3038 ELSE
3039 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
3040 IF l_debug_level > 0 THEN
3041 oe_debug_pub.add( 'ALREADY RESERVED' , 2 ) ;
3042 END IF;
3043 END IF; -- shippable flag
3044 OE_MSG_PUB.Add;
3045 -- code fix for 3300528
3046 END IF; -- shippable flag and reserved quantity
3047 ELSE
3048
3049 J := J +1;
3050 x_line_tbl(J) := l_line_tbl(I);
3051
3052 END IF;
3053 END LOOP;
3054
3055 IF l_debug_level > 0 THEN
3056 oe_debug_pub.add( 'EXITING QUERY RESERVE LINES' , 1 ) ;
3057 END IF;
3058
3059
3060 EXCEPTION
3061
3062
3063 WHEN FND_API.G_EXC_ERROR THEN
3064 IF l_debug_level > 0 THEN
3065 oe_debug_pub.add( 'EXPECTED ERROR IN QUERY_RESERVE_LINES ' , 1 ) ;
3066 END IF;
3067 RAISE FND_API.G_EXC_ERROR;
3068
3069 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3070
3071 IF l_debug_level > 0 THEN
3072 oe_debug_pub.add( 'UNEXPECTED ERROR IN QUERY_RESERVE_LINES ' , 1 ) ;
3073 END IF;
3074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3075
3076 -- Start 2742982 --
3077 WHEN NO_DATA_FOUND THEN
3078 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
3079 OE_MSG_PUB.Add;
3080 RAISE FND_API.G_EXC_ERROR;
3081 -- End 2742982 --
3082
3083 WHEN OTHERS THEN
3084
3085 IF l_debug_level > 0 THEN
3086 oe_debug_pub.add( 'WHEN OTHERS ERROR IN QUERY_RESERVE_LINES ' , 1 ) ;
3087 END IF;
3088 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3089 THEN
3090 OE_MSG_PUB.Add_Exc_Msg
3091 ( G_PKG_NAME
3092 , 'Validate_Group_Line'
3093 );
3094 END IF;
3095
3096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3097 END Query_Reserve_Lines;
3098
3099 /*----------------------------------------------------------------
3100 PROCEDURE Query_ATP_CHECK_Lines
3101 Description : This api will be called from schedule_multi_line API when
3102 action requested by user is reserve.
3103 System has to perform little validations before passing record back to the user.
3104 Initially system selects few scheduling related attributes to see whether the
3105 line has to be selected for atp_check or not. If it has to be selected then
3106 check if it is part of model and parent is also selected by user. If Model is selected by user we will ignore the line or else we will select the line for
3107 processing. If the line selected is Included item, make sure not only it's modle is selected, also check for its immediate parent. If its immediate parent is selected then ignore the included item, since included item will be selected by it's parent.
3108
3109 If top model is selected for processing or line part of smc or ato then query whole model. If class or kit is selected then select its included items as well.If line is selected which is part of set then select whole set for processing
3110
3111 ------------------------------------------------------------------*/
3112 PROCEDURE Query_ATP_CHECK_Lines
3113 ( p_selected_tbl IN OE_GLOBALS.Selected_record_Tbl, --R12.MOAC
3114 p_sch_action IN VARCHAR2,
3115 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
3116 )
3117 IS
3118 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
3119 l_line_rec OE_Order_PUB.Line_rec_Type;
3120 l_line_id NUMBER;
3121 l_arrival_set_id NUMBER;
3122 l_ship_set_id NUMBER;
3123 l_top_model_line_id NUMBER;
3124 l_ato_line_id NUMBER;
3125 l_smc_flag VARCHAR2(1);
3126 l_item_type_code VARCHAR2(30);
3127 l_schedule_status_code VARCHAR2(30);
3128 l_line_category_code VARCHAR2(30);
3129 l_source_type_code VARCHAR2(30);
3130 l_link_to_line_id NUMBER;
3131 l_query VARCHAR2(1);
3132 l_found VARCHAR2(1);
3133 J NUMBER;
3134 l_header_id NUMBER;
3135 l_sales_order_id NUMBER := Null;
3136 l_open_flag VARCHAR2(1);
3137
3138 --
3139 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3140 --
3141 l_orig_sys_document_ref VARCHAR2(50);
3142 l_orig_sys_line_ref VARCHAR2(50);
3143 l_orig_sys_shipment_ref VARCHAR2(50);
3144 l_source_document_type_id NUMBER;
3145 l_change_sequence VARCHAR2(50);
3146 l_source_document_id NUMBER;
3147 l_source_document_line_id NUMBER;
3148 l_order_source_id NUMBER;
3149 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3150 BEGIN
3151
3152
3153 FOR L IN 1..p_selected_tbl.count LOOP --R12.MOAC
3154 IF l_debug_level > 0 THEN
3155 oe_debug_pub.add( 'PROCESSING LINE_ID ' || P_SELECTED_TBL(L).ID1 , 2 ) ;
3156 END IF;
3157
3158 l_line_id := p_selected_tbl(L).id1;
3159 -- Query required attributes and check does any action is required
3160 -- on the line.
3161 Select Arrival_set_id, Ship_set_id,top_model_line_id,
3162 Ship_model_complete_flag, ato_line_id,item_type_code,
3163 Schedule_status_code,line_category_code,header_id,open_flag,
3164 source_type_code,link_to_line_id,
3165 orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref,
3166 source_document_type_id, change_sequence, source_document_id,
3167 source_document_line_id, order_source_id
3168 Into l_arrival_set_id, l_ship_set_id, l_top_model_line_id,
3169 l_smc_flag, l_ato_line_id, l_item_type_code,
3170 l_schedule_status_code,l_line_category_code,l_header_id,l_open_flag,
3171 l_source_type_code,l_link_to_line_id,
3172 l_orig_sys_document_ref, l_orig_sys_line_ref, l_orig_sys_shipment_ref,
3173 l_source_document_type_id, l_change_sequence, l_source_document_id,
3174 l_source_document_line_id, l_order_source_id
3175 From oe_order_lines_all
3176 Where line_id = l_line_id;
3177
3178 OE_MSG_PUB.set_msg_context
3179 ( p_entity_code => 'LINE'
3180 ,p_entity_id => l_line_id
3181 ,p_header_id => l_header_id
3182 ,p_line_id => l_line_id
3183 ,p_orig_sys_document_ref => l_orig_sys_document_ref
3184 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
3185 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
3186 ,p_change_sequence => l_change_sequence
3187 ,p_source_document_id => l_source_document_id
3188 ,p_source_document_line_id => l_source_document_line_id
3189 ,p_order_source_id => l_order_source_id
3190 ,p_source_document_type_id => l_source_document_type_id);
3191
3192 IF l_line_category_code = 'RETURN'
3193 OR l_item_type_code = 'SERVICE'
3194 OR l_source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL
3195 OR nvl(l_open_flag,'Y') = 'N' THEN
3196
3197 -- populate the error message and skip the line.
3198 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
3199 OE_MSG_PUB.Add;
3200 ELSE
3201
3202
3203 -- If the line is part of set then avoid selecting them ,
3204 -- since scheduling is prerequisite for sets.
3205 -- Also avoid selecting any line if they are part of ato model or
3206 -- smc and they are scheduled.
3207
3208
3209 l_query := 'Y';
3210
3211 -- If user slectes the non smc model and option,
3212 -- then avoid selecting the
3213 -- option, since model will select the option.
3214
3215 IF l_top_model_line_id IS NOT NULL
3216 AND l_smc_flag = 'N'
3217 AND l_top_model_line_id <> l_line_id THEN
3218
3219 -- Check if model is selected by user.
3220 -- If model is selected then ignore the line for query, since
3221 -- model will query the line.
3222 --R12.MOAC
3223 FOR K IN 1..p_selected_tbl.count LOOP
3224 IF l_top_model_line_id = p_selected_tbl(K).id1
3225 OR (l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
3226 l_link_to_line_id = p_selected_tbl(K).id1) THEN
3227 l_query := 'N';
3228 exit;
3229 END IF;
3230 END LOOP;
3231
3232 ELSIF x_line_tbl.count > 0 THEN
3233 -- If the line is not a non smc option or class, then check if the
3234 -- line is present in the table.
3235 -- Check if the line is already present. If line exists then
3236 -- ignore the line.
3237
3238 FOR I IN 1..x_line_tbl.count LOOP
3239 IF l_line_id = x_line_tbl(I).line_id
3240 THEN
3241 l_query := 'N';
3242 Exit;
3243 END IF;
3244 END LOOP;
3245 END IF;
3246
3247 IF l_query = 'Y' THEN
3248
3249 -- Decided to query the line. See what to pass to query sets.
3250 IF l_arrival_set_id is not null
3251 OR l_ship_set_id is not null
3252 OR l_smc_flag = 'Y'
3253 OR (l_ato_line_id is not null AND
3254 NOT (l_line_id = l_ato_line_id AND
3255 l_item_type_code IN(OE_GLOBALS.G_ITEM_STANDARD,
3256 OE_GLOBALS.G_ITEM_OPTION)))
3257 THEN
3258
3259 IF l_arrival_set_id is not null
3260 OR l_ship_set_id is not null
3261 OR l_smc_flag = 'Y'
3262 THEN
3263
3264 Oe_Config_Schedule_Pvt.Query_Set_Lines
3265 (p_header_id => l_header_id,
3266 p_arrival_set_id => l_arrival_set_id,
3267 p_ship_set_id => l_ship_set_id,
3268 p_model_line_id => l_top_model_line_id,
3269 p_sch_action => p_sch_action,
3270 x_line_tbl => l_line_tbl,
3271 x_return_status => l_return_status);
3272
3273 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3275 END IF;
3276
3277 ELSE
3278
3279 -- Query ato model.
3280 OE_Config_Util.Query_ATO_Options
3281 ( p_ato_line_id => l_ato_line_id,
3282 x_line_tbl => l_line_tbl);
3283
3284 END IF;
3285
3286 -- query using top_model_line_id / ato line id
3287
3288
3289 IF x_line_tbl.count = 0 THEN
3290
3291 x_line_tbl := l_line_tbl;
3292
3293 ELSE
3294 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
3295 ,p_line_tbl => l_line_tbl); */ --Bug-2454163
3296
3297 J := x_line_tbl.count;
3298 FOR I IN 1..l_line_tbl.count LOOP
3299 IF NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
3300 x_line_tbl(J + I) := l_line_tbl(I);
3301 END IF;
3302 END LOOP;
3303
3304 END IF;
3305
3306 ELSIF l_line_id = l_top_model_line_id THEN
3307
3308 -- This is a non smc top model. User might have selected model
3309 -- and its options. If the model is selected query whole model
3310 -- and try to place them in the
3311 -- out table if they are not present already.
3312
3313 Oe_Config_Schedule_Pvt.Query_Set_Lines
3314 (p_header_id => l_header_id,
3315 p_model_line_id => l_top_model_line_id,
3316 p_sch_action => p_sch_action,
3317 x_line_tbl => l_line_tbl,
3318 x_return_status => l_return_status);
3319
3320 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3322 END IF;
3323
3324 IF x_line_tbl.count = 0 THEN
3325 x_line_tbl := l_line_tbl;
3326 ELSE
3327
3328 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
3329 ,p_line_tbl => l_line_tbl); */
3330
3331 J := x_line_tbl.count;
3332 FOR I IN 1..l_line_tbl.count LOOP
3333
3334 IF NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
3335 J := J + 1;
3336 x_line_tbl(J) := l_line_tbl(I);
3337 END IF;
3338 END LOOP;
3339
3340 END IF;
3341 ELSE
3342
3343
3344 OE_Line_Util.Query_Row
3345 (p_line_id => l_line_id,
3346 x_line_rec => l_line_rec);
3347
3348 IF nvl(l_line_rec.shippable_flag,'N') = 'Y' THEN
3349
3350 IF l_sales_order_id is null THEN
3351 l_sales_order_id :=
3352 OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
3353 (l_line_rec.header_id);
3354 END IF;
3355
3356 l_line_rec.reserved_quantity :=
3357 OE_LINE_UTIL.Get_Reserved_Quantity
3358 (p_header_id => l_sales_order_id,
3359 p_line_id => l_line_rec.line_id,
3360 p_org_id => l_line_rec.ship_from_org_id);
3361 l_line_rec.reserved_quantity2 := -- INVCONV
3362 OE_LINE_UTIL.Get_Reserved_Quantity
3363 (p_header_id => l_sales_order_id,
3364 p_line_id => l_line_rec.line_id,
3365 p_org_id => l_line_rec.ship_from_org_id);
3366 END IF;
3367 IF l_line_rec.reserved_quantity IS NULL
3368 OR l_line_rec.reserved_quantity = FND_API.G_MISS_NUM
3369 THEN
3370 l_line_rec.reserved_quantity := 0;
3371 END IF;
3372
3373 /*IF l_line_rec.reserved_quantity2 IS NULL -- INVCONV
3374 OR l_line_rec.reserved_quantity2 = FND_API.G_MISS_NUM
3375 THEN
3376 l_line_rec.reserved_quantity2 := 0;
3377 END IF; */
3378 /* Check_Merge_Line(p_line_rec => l_line_rec
3379 ,p_x_line_tbl => x_line_tbl); */ --Bug-2454163
3380
3381 IF NOT Find_Line(x_line_tbl,l_line_rec.line_id) THEN
3382 x_line_tbl(x_line_tbl.count + 1) := l_line_rec;
3383 END IF;
3384
3385 IF l_item_type_code = 'CLASS'
3386 OR l_item_type_code = 'KIT'
3387 THEN
3388
3389 Oe_Config_Schedule_Pvt.Query_Set_Lines
3390 (p_header_id => l_header_id,
3391 p_link_to_line_id => l_line_id,
3392 p_sch_action => p_sch_action,
3393 x_line_tbl => l_line_tbl,
3394 x_return_status => l_return_status);
3395
3396 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3398 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3399 x_line_tbl.delete(x_line_tbl.count);
3400
3401 END IF;
3402
3403 /* Check_Merge_Line(p_x_line_tbl => x_line_tbl
3404 ,p_line_tbl => l_line_tbl); */ -- Bug-2454163
3405
3406 J := x_line_tbl.count;
3407 FOR I IN 1..l_line_tbl.count LOOP
3408 IF NOT Find_Line(x_line_tbl,l_line_tbl(I).line_id) THEN
3409 J := J + 1;
3410 x_line_tbl(J) := l_line_tbl(I);
3411 END IF;
3412 END LOOP;
3413
3414 END IF; -- Class or Kit.
3415 END IF; -- group query or single query.
3416
3417 END IF; -- l_query.
3418
3419 END IF; -- do or do not.
3420 END LOOP;
3421
3422
3423 EXCEPTION
3424
3425
3426 WHEN FND_API.G_EXC_ERROR THEN
3427 IF l_debug_level > 0 THEN
3428 oe_debug_pub.add( 'EXPECTED ERROR IN QUERY_ATP_CHECK_LINES ' , 1 ) ;
3429 END IF;
3430 RAISE FND_API.G_EXC_ERROR;
3431
3432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3433
3434 IF l_debug_level > 0 THEN
3435 oe_debug_pub.add( 'UNEXPECTED ERROR IN QUERY_ATP_CHECK_LINES ' , 1 ) ;
3436 END IF;
3437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3438
3439 -- Start 2742982 --
3440 WHEN NO_DATA_FOUND THEN
3441 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
3442 OE_MSG_PUB.Add;
3443 -- End 2742982 --
3444
3445 WHEN OTHERS THEN
3446
3447 IF l_debug_level > 0 THEN
3448 oe_debug_pub.add( 'WHEN OTHERS ERROR IN QUERY_ATP_CHECK_LINES ' , 1 ) ;
3449 END IF;
3450 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3451 THEN
3452 OE_MSG_PUB.Add_Exc_Msg
3453 ( G_PKG_NAME
3454 , 'Query_ATP_CHECK_Lines'
3455 );
3456 END IF;
3457
3458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3459 END Query_ATP_CHECK_Lines;
3460
3461
3462 /* ---------------------------------------------------------------
3463 Procedure Schedule_Order
3464 This procedure will be called from pld when user performs any scheduling
3465 activity from header level.
3466 An order can consists of many lines and each line can be within a group.
3467 Call MRP for all lines at once. After calling the MRP for scheduling action
3468 loop through the table and ignore all lines that failed the scheduling and
3469 call process order for the lines that successfuly passed scheduling.
3470 ---------------------------------------------------------------*/
3471 Procedure Schedule_Order(p_header_id IN NUMBER,
3472 p_sch_action IN VARCHAR2,
3473 x_atp_tbl OUT NOCOPY /* file.sql.39 change */ OE_ATP.Atp_Tbl_Type,
3474 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3475 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3476 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3477 IS
3478
3479 l_line_tbl OE_ORDER_PUB.Line_tbl_type;
3480 l_old_line_tbl OE_ORDER_PUB.Line_tbl_type;
3481 l_return_status VARCHAR2(1);
3482 l_msg_count NUMBER;
3483 l_msg_data VARCHAR2(2000);
3484
3485 -- Need to remove this.
3486 l_atp_tbl OE_ATP.atp_tbl_type;
3487 --
3488 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3489 --
3490 BEGIN
3491
3492 x_return_status := FND_API.G_RET_STS_SUCCESS;
3493 IF l_debug_level > 0 THEN
3494 oe_debug_pub.add( 'ENTERING OE_GROUP_SCH_UTIL.SCHEDULE_ORDER' , 1 ) ;
3495 END IF;
3496
3497 IF l_debug_level > 0 THEN
3498 oe_debug_pub.add( 'P_HEADER_ID : ' || P_HEADER_ID , 1 ) ;
3499 END IF;
3500 IF l_debug_level > 0 THEN
3501 oe_debug_pub.add( 'P_SCH_ACTION: ' || P_SCH_ACTION , 1 ) ;
3502 END IF;
3503
3504 -- Start 2434807 -
3505 -- Added to clear existing records from g_atp_tbl
3506 -- before starting scheduling event
3507 IF p_sch_action <> OE_SCHEDULE_UTIL.OESCH_ACT_ATP_CHECK THEN
3508 Oe_Schedule_Util.g_atp_tbl.delete;
3509 END IF;
3510 -- End 2434807 -
3511
3512
3513 Query_Lines(p_header_id => p_header_id,
3514 p_sch_action => p_sch_action,
3515 x_line_tbl => l_line_tbl);
3516
3517 l_old_line_tbl := l_line_tbl;
3518
3519 IF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_ATP_CHECK THEN
3520 IF l_debug_level > 0 THEN
3521 oe_debug_pub.add( 'BEFORE CALLING MULTI_ATP_CHECK ' ) ;
3522 END IF;
3523 IF l_debug_level > 0 THEN
3524 oe_debug_pub.add( 'COUNT ' || L_LINE_TBL.COUNT ) ;
3525 END IF;
3526
3527 IF l_line_tbl.count > 0 THEN
3528
3529 OE_SCHEDULE_UTIL.Multi_ATP_CHECK
3530 (p_old_line_tbl => l_old_line_tbl,
3531 p_x_line_tbl => l_line_tbl,
3532 x_atp_tbl => x_atp_tbl,
3533 x_return_status => x_return_status);
3534
3535 ELSE
3536
3537 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
3538 OE_MSG_PUB.Add;
3539 END IF;
3540
3541 ELSE
3542
3543 IF l_debug_level > 0 THEN
3544 oe_debug_pub.add( 'BEFORE CALLING PROCESS SCHEDULING GROUP ' ) ;
3545 END IF;
3546 IF l_debug_level > 0 THEN
3547 oe_debug_pub.add( 'COUNT ' || L_LINE_TBL.COUNT ) ;
3548 END IF;
3549 IF l_debug_level > 0 THEN
3550 oe_debug_pub.add( 'FIRST ' || L_LINE_TBL.FIRST ) ;
3551 END IF;
3552 IF l_debug_level > 0 THEN
3553 oe_debug_pub.add( 'LAST ' || L_LINE_TBL.LAST ) ;
3554 END IF;
3555
3556 IF l_line_tbl.count > 0 THEN
3557
3558 Oe_Config_Schedule_Pvt.Process_Group
3559 (p_x_line_tbl => l_line_tbl
3560 ,p_old_line_tbl => l_old_line_tbl
3561 ,p_caller => 'UI_ACTION'
3562 ,p_sch_action => p_sch_action
3563 ,p_partial => TRUE
3564 ,x_return_status => x_return_status);
3565 ELSE
3566
3567 IF p_sch_action <> OE_SCHEDULE_UTIL.OESCH_ACT_RESERVE THEN
3568 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
3569 OE_MSG_PUB.Add;
3570 END IF;
3571 END IF;
3572
3573 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3575 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3576 RAISE FND_API.G_EXC_ERROR;
3577 END IF;
3578
3579 END IF;
3580
3581 oe_msg_pub.count_and_get
3582 (p_count => x_msg_count
3583 ,p_data => x_msg_data);
3584
3585 IF l_debug_level > 0 THEN
3586 oe_debug_pub.add( 'COUNT IS ' || X_MSG_COUNT , 1 ) ;
3587 END IF;
3588 IF l_debug_level > 0 THEN
3589 oe_debug_pub.add( 'EXITING OE_GROUP_SCH_UTIL.SCHEDULE_ORDER' , 1 ) ;
3590 END IF;
3591 x_return_status := FND_API.G_RET_STS_SUCCESS;
3592
3593 EXCEPTION
3594
3595 WHEN FND_API.G_EXC_ERROR THEN
3596
3597 x_return_status := FND_API.G_RET_STS_ERROR;
3598
3599 oe_msg_pub.count_and_get
3600 ( p_count => x_msg_count
3601 ,p_data => x_msg_data
3602 );
3603
3604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3605
3606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3607
3608 oe_msg_pub.count_and_get
3609 ( p_count => x_msg_count
3610 ,p_data => x_msg_data
3611 );
3612
3613 WHEN OTHERS THEN
3614
3615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3616
3617 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3618 THEN
3619 OE_MSG_PUB.Add_Exc_Msg
3620 ( G_PKG_NAME
3621 , 'Schedule_Order'
3622 );
3623 END IF;
3624
3625 oe_msg_pub.count_and_get
3626 ( p_count => x_msg_count
3627 ,p_data => x_msg_data
3628 );
3629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3630 END Schedule_Order;
3631 /*---------------------------------------------------------------
3632 Procedure :Schedule_Multi_lines
3633 This procedure is called when lines are multi-selected and
3634 scheduling action is performed.
3635 Based on the requested action this API will call appropriate
3636 query api's which will get all approprite a lines. Based on the
3637 action call process group or multi atp check.
3638 ---------------------------------------------------------------*/
3639 Procedure Schedule_Multi_lines
3640 (p_selected_line_tbl IN OE_GLOBALS.Selected_Record_Tbl, --R12.MOAC
3641 p_line_count IN NUMBER,
3642 p_sch_action IN VARCHAR2,
3643 x_atp_tbl OUT NOCOPY /* file.sql.39 change */ OE_ATP.Atp_Tbl_Type,
3644 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3645 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3646 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3647 IS
3648 j Integer;
3649 initial Integer;
3650 nextpos Integer;
3651 --3751812
3652 --l_record_ids VARCHAR2(2000) := p_line_list || ',';
3653 --l_record_ids VARCHAR2(32000) := p_line_list || ',';
3654
3655 l_line_id NUMBER;
3656
3657 l_line_tbl OE_ORDER_PUB.line_tbl_type;
3658 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
3659 l_line_id_tbl number_arr;
3660 l_set_rec OE_ORDER_CACHE.set_rec_type;
3661
3662 --
3663 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3664 --
3665 BEGIN
3666 IF l_debug_level > 0 THEN
3667 oe_debug_pub.add( 'ENTERING SCHEDULE_MULTI_LINES' , 1 ) ;
3668 oe_debug_pub.add( 'LINE COUNT IS: ' || P_LINE_COUNT , 1 ) ;
3669 oe_debug_pub.add( 'ACTION IS: ' || P_SCH_ACTION , 1 ) ;
3670 END IF;
3671
3672 -- Start 2434807 -
3673 -- Added to clear existing records from g_atp_tbl
3674 -- before starting scheduling event
3675 IF p_sch_action <> OE_SCHEDULE_UTIL.OESCH_ACT_ATP_CHECK THEN
3676 Oe_Schedule_Util.g_atp_tbl.delete;
3677 END IF;
3678 -- End 2434807 -
3679
3680 x_return_status := FND_API.G_RET_STS_SUCCESS;
3681 --R12.MOAC
3682 /*
3683 j := 1;
3684 initial := 1;
3685 nextpos := INSTR(l_record_ids,',',1,j) ;
3686
3687 FOR I IN 1..p_line_count LOOP
3688
3689 l_line_id := to_number(substr(l_record_ids,initial, nextpos-initial));
3690 l_line_id_tbl(j) := l_line_id;
3691
3692 IF l_debug_level > 0 THEN
3693 oe_debug_pub.add( 'LINE_ID : ' || L_LINE_ID , 1 ) ;
3694 END IF;
3695
3696 initial := nextpos + 1.0;
3697 j := j + 1.0;
3698 nextpos := INSTR(l_record_ids,',',1,j) ;
3699
3700 END LOOP;
3701 */
3702 IF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_SCHEDULE THEN
3703
3704 Query_Schedule_lines(p_sch_action => p_sch_action,
3705 p_selected_tbl => p_selected_line_tbl, --R12.MOAC
3706 x_line_tbl => l_line_tbl);
3707
3708 ELSIF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_UNSCHEDULE THEN
3709
3710 Query_Unschedule_lines(p_sch_action => p_sch_action,
3711 p_selected_tbl => p_selected_line_tbl, --R12.MOAC
3712 x_line_tbl => l_line_tbl);
3713
3714 ELSIF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_UNRESERVE THEN
3715
3716 Query_Unreserve_lines(p_sch_action => p_sch_action,
3717 p_selected_tbl => p_selected_line_tbl, --R12.MOAC
3718 x_line_tbl => l_line_tbl);
3719
3720 ELSIF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_RESERVE THEN
3721
3722 Query_Reserve_lines(p_sch_action => p_sch_action,
3723 p_selected_tbl => p_selected_line_tbl, --R12.MOAC
3724 x_line_tbl => l_line_tbl);
3725
3726 ELSIF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_ATP_CHECK THEN
3727 --Bug 5881611
3728 --Creating a savepoint, so that it can be be rolled for ATP Checks.
3729 --This is to rollback updates to database made by Availability window, which
3730 --calls process_included_items to explode included items and oe_order_pvt.lines
3731 --which updates the database tables.
3732 oe_debug_pub.add('Creating a savepoint for ATP Check activity.');
3733 SAVEPOINT SP_ONLY_ATP_CHECK;
3734 Query_ATP_CHECK_lines(p_sch_action => p_sch_action,
3735 p_selected_tbl => p_selected_line_tbl, --R12.MOAC
3736 x_line_tbl => l_line_tbl);
3737 END IF;
3738
3739 IF l_line_tbl.count > 0 THEN
3740
3741 IF l_debug_level > 0 THEN
3742 oe_debug_pub.add('No lines to process : '||l_line_tbl.count,1);
3743 END IF;
3744
3745 Validate_Group
3746 (p_x_line_tbl => l_line_tbl,
3747 p_sch_action => p_sch_action,
3748 p_validate_action => 'COMPLETE', --added for bug 3590437
3749 x_return_status => x_return_status);
3750
3751 --3990887
3752 --END IF;
3753
3754 IF l_debug_level > 0 THEN
3755 oe_debug_pub.add( ' AFTER VALIDATE_GROUP : '||x_return_status,1);
3756 END IF;
3757
3758 -- 3990887
3759 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3760
3761 ----------- set up the tables -------------------------
3762 l_old_line_tbl := l_line_tbl;
3763
3764 FOR I IN 1..l_line_tbl.count LOOP
3765
3766 IF l_debug_level > 0 THEN
3767 oe_debug_pub.add( ' LINE ID : ' || L_LINE_TBL ( I ) .LINE_ID , 4 ) ;
3768 END IF;
3769 l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
3770 l_line_tbl(I).schedule_action_code := p_sch_action;
3771
3772 IF (l_line_tbl(I).arrival_set_id is not null) THEN
3773 l_set_rec := OE_ORDER_CACHE.Load_Set
3774 (l_line_tbl(I).arrival_set_id);
3775 l_line_tbl(I).arrival_set := l_set_rec.set_name;
3776 ELSIF (l_line_tbl(I).ship_set_id is not null) THEN
3777 l_set_rec := OE_ORDER_CACHE.Load_Set
3778 (l_line_tbl(I).ship_set_id);
3779 l_line_tbl(I).ship_set := l_set_rec.set_name;
3780 ELSIF (l_line_tbl(I).ship_model_complete_flag ='Y') THEN
3781 l_line_tbl(I).ship_set := l_line_tbl(I).top_model_line_id;
3782 ELSIF (l_line_tbl(I).ato_line_id is not null)
3783 AND NOT(OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
3784 AND MSC_ATP_GLOBAL.GET_APS_VERSION = 10)
3785 THEN
3786 l_line_tbl(I).ship_set := l_line_tbl(I).ato_line_id;
3787 END IF;
3788
3789 END LOOP;
3790
3791 ------- Action Specific processing -----------------------
3792 -- 3990887
3793 IF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_ATP_CHECK
3794 THEN
3795 --Bug 5881611
3796 --Rolling back
3797 oe_debug_pub.add('Rollback for only atp check');
3798 oe_delayed_requests_pvt.Clear_Request(
3799 x_return_status => x_return_status);
3800 ROLLBACK TO SAVEPOINT SP_ONLY_ATP_CHECK;
3801
3802 IF l_debug_level > 0 THEN
3803 oe_debug_pub.add( 'BEFORE CALLING MULTI_ATP_CHECK ' ) ;
3804 END IF;
3805 IF l_debug_level > 0 THEN
3806 oe_debug_pub.add( 'LINE COUNT ' || L_LINE_TBL.COUNT , 1 ) ;
3807 END IF;
3808
3809 OE_SCHEDULE_UTIL.Multi_ATP_CHECK
3810 (p_old_line_tbl => l_old_line_tbl,
3811 p_x_line_tbl => l_line_tbl,
3812 x_atp_tbl => x_atp_tbl,
3813 x_return_status => x_return_status);
3814
3815 ELSE
3816
3817 IF l_debug_level > 0 THEN
3818 oe_debug_pub.add( 'BEFORE CALLING PROCESS SCHEDULING GROUP ' ) ;
3819 oe_debug_pub.add( 'LINE COUNT ' || L_LINE_TBL.COUNT , 1 ) ;
3820 END IF;
3821
3822 -- 3870895 : call process_group only when coun > 0 and return status is success
3823 IF l_line_tbl.count > 0
3824 AND x_return_status = FND_API.G_RET_STS_SUCCESS
3825 THEN
3826
3827 Oe_Config_Schedule_Pvt.Process_Group
3828 (p_x_line_tbl => l_line_tbl
3829 ,p_old_line_tbl => l_old_line_tbl
3830 ,p_caller => 'UI_ACTION'
3831 ,p_sch_action => p_sch_action
3832 ,p_partial => TRUE
3833 ,x_return_status => x_return_status);
3834
3835 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3837 -- ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3838 -- RAISE FND_API.G_EXC_ERROR;
3839 END IF;
3840
3841 ELSE
3842 IF p_sch_action <> OE_SCHEDULE_UTIL.OESCH_ACT_RESERVE
3843 AND p_sch_action <> OE_SCHEDULE_UTIL.OESCH_ACT_ATP_CHECK
3844 THEN
3845 FND_MESSAGE.SET_NAME('ONT','OE_SCH_NO_ACTION_DONE_NO_EXP');
3846 OE_MSG_PUB.Add;
3847 END IF;
3848 END IF;
3849
3850 IF l_debug_level > 0 THEN
3851 oe_debug_pub.add( 'AFTER PROCESS GROUP' || X_RETURN_STATUS , 1 ) ;
3852 END IF;
3853 END IF;
3854 -- Set return status.
3855
3856 END IF;
3857 END IF; -- 3990887
3858
3859 oe_msg_pub.count_and_get
3860 ( p_count => x_msg_count
3861 , p_data => x_msg_data);
3862
3863 IF l_debug_level > 0 THEN
3864 oe_debug_pub.add( ' MESSAGE COUNT: ' || X_MSG_COUNT , 1 ) ;
3865 END IF;
3866 -- Set return status.
3867
3868 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3869 RAISE FND_API.G_EXC_ERROR;
3870
3871 END IF;
3872
3873
3874 -- Returning success, even if there were errors (unexpected errors will
3875 -- be raised and taken care of). This is because we do not want to rollback
3876 -- since the successful lines should get committed.
3877
3878 x_return_status := FND_API.G_RET_STS_SUCCESS;
3879
3880 /* -- Get message count and data
3881
3882 oe_msg_pub.count_and_get
3883 ( p_count => x_msg_count
3884 , p_data => x_msg_data
3885 );
3886
3887 */
3888 IF l_debug_level > 0 THEN
3889 oe_debug_pub.add( 'EXITING SCHEDULE_MULTI_LINES WITH: ' || X_RETURN_STATUS , 1 ) ;
3890 END IF;
3891
3892 EXCEPTION
3893
3894 WHEN FND_API.G_EXC_ERROR THEN
3895
3896 x_return_status := FND_API.G_RET_STS_ERROR;
3897
3898 -- Get message count and data
3899
3900 oe_msg_pub.count_and_get
3901 ( p_count => x_msg_count
3902 , p_data => x_msg_data
3903 );
3904
3905 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3906
3907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3908
3909 -- Get message count and data
3910
3911 oe_msg_pub.count_and_get
3912 ( p_count => x_msg_count
3913 , p_data => x_msg_data
3914 );
3915
3916 WHEN OTHERS THEN
3917
3918 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3919
3920 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
3921 THEN
3922 oe_msg_pub.Add_Exc_Msg
3923 ( G_PKG_NAME,
3924 'Schedule_Multi_lines'
3925 );
3926 END IF;
3927
3928 -- Get message count and data
3929
3930 oe_msg_pub.count_and_get
3931 ( p_count => x_msg_count
3932 , p_data => x_msg_data
3933 );
3934
3935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3936 END Schedule_Multi_lines;
3937 /* ------------------------------------------------------------
3938 Procedure schedule_set_lines
3939 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
3940 p_entity_id => p_line_rec.line_id,
3941 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
3942 p_requesting_entity_id => p_line_rec.line_id,
3943 p_request_type => OE_GLOBALS.G_SCHEDULE_LINE,
3944 p_param1 => l_param1,
3945 p_param2 => p_line_rec.header_id,
3946 p_param3 => l_action,
3947 p_param4 => p_old_line_rec.ship_from_org_id,
3948 p_param5 => p_old_line_rec.ship_to_org_id,
3949 p_param6 => p_old_line_rec.ship_set_id,
3950 p_param7 => p_old_line_rec.arrival_set_id,
3951 p_param8 => l_entity_type,
3952 p_param9 => l_ship_to_org_id -- ship to from sets table.
3953 p_param10 => l_ship_to_org_id -- ship to from sets table.
3954 p_param11 => l_shipping_method_code
3955 p_date_param1 => p_old_line_rec.schedule_ship_date,
3956 p_date_param2 => p_old_line_rec.schedule_arrival_date,
3957 p_date_param3 => p_old_line_rec.request_date,
3958 p_date_param4 => l_schedule_ship_date,
3959 p_date_param5 => l_schedule_arrival_date,
3960 x_return_status => x_return_status);
3961
3962 Description: This procedure will be called when the delayed request
3963 SCHEDULE_LINE is logged. This delayed request is logged
3964 when new lines are inserted to a SCHEDULE SET. A set being
3965 a user defined ship or arrival set.When multiple lines are
3966 iserted to the same set, this procedure is called once for
3967 all the lines of the set.
3968 If the included item is part of request table, check for the
3969 parent. If parent exists in the table, assume that parent will
3970 fetch the included items and remove included items from the list.
3971
3972 Changes have been made to copy override_atp flag from model/class/
3973 kit to it's included items when the flag is set.
3974 -------------------------------------------------------------------*/
3975 Procedure Schedule_set_lines
3976 ( p_sch_set_tbl IN OE_ORDER_PUB.request_tbl_type
3977 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3978 IS
3979 l_line_rec OE_ORDER_PUB.line_rec_type;
3980 l_line_tbl OE_ORDER_PUB.line_Tbl_type;
3981 l_old_line_tbl OE_ORDER_PUB.line_Tbl_type;
3982 l_request_rec OE_ORDER_PUB.request_rec_type;
3983 l_old_set_tbl set_tbl_type;
3984 l_sales_order_id NUMBER;
3985 l_line_exists VARCHAR2(1) := 'N';
3986 l_iline_tbl OE_ORDER_PUB.line_Tbl_type;
3987 l_item_type_code VARCHAR2(30);
3988 l_link_to_line_id NUMBER;
3989 l_top_model_line_id NUMBER;
3990 l_ato_line_id NUMBER;
3991 l_header_id NUMBER;
3992 K NUMBER;
3993 l_param12 VARCHAR2(1) := 'N'; --3384975
3994 l_type_code VARCHAR2(30);-- 3564302(issue#1)
3995 --3564310
3996 l_ship_set_id NUMBER;
3997 l_arrival_set_id NUMBER;
3998 l_set_rec OE_ORDER_CACHE.set_rec_type;
3999 l_log_error BOOLEAN := FALSE;
4000 l_part_of_set VARCHAR2(1) :='Y'; --4405004
4001
4002 -- 3870895
4003 CURSOR C5 IS
4004 Select line_id, shipping_interfaced_flag
4005 from oe_order_lines_all
4006 where top_model_line_id = l_top_model_line_id
4007 and open_flag = 'Y'
4008 and shipping_interfaced_flag = 'Y';
4009 --
4010 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4011 --
4012 BEGIN
4013
4014 x_return_status := FND_API.G_RET_STS_SUCCESS;
4015 IF l_debug_level > 0 THEN
4016 oe_debug_pub.add( 'ENTERING OE_GROUP_SCH_UTIL.SCHEDULE_SET_LINES' , 1 ) ;
4017 END IF;
4018
4019 K := 0;
4020 FOR I in 1..p_sch_set_tbl.count LOOP
4021
4022 IF l_debug_level > 0 THEN
4023 oe_debug_pub.add( 'PROCESSING LINE' || P_SCH_SET_TBL ( I ) .ENTITY_ID , 1 ) ;
4024 END IF;
4025
4026 -- For the included item, check if the parent is present the in table.
4027 -- That means parent will explode the line or else add the line to
4028 -- line tbl.
4029
4030 -- 3384975
4031 IF p_sch_set_tbl(I).param12 = 'Y' THEN
4032 l_param12 := p_sch_set_tbl(I).param12;
4033 END IF;
4034
4035 l_line_exists := 'N';
4036 -- 3564310 set ids selected
4037 BEGIN
4038 Select item_type_code,
4039 link_to_line_id,
4040 top_model_line_id,
4041 ato_line_id,
4042 header_id,
4043 ship_set_id,
4044 arrival_set_id
4045 Into l_item_type_code,
4046 l_link_to_line_id,
4047 l_top_model_line_id,
4048 l_ato_line_id,
4049 l_header_id,
4050 l_ship_set_id,
4051 l_arrival_set_id
4052 From oe_order_lines_all
4053 Where line_id = p_sch_set_tbl(I).entity_id;
4054 EXCEPTION
4055 WHEN OTHERS THEN
4056 IF l_debug_level > 0 THEN
4057 oe_debug_pub.add( 'ERROR WHILE SELECTING DATA' , 5 ) ;
4058 END IF;
4059 l_line_exists := 'Y';
4060 END;
4061
4062 --{bug3885953: If the ship/arrival set has changed, do not use old delayed
4063 --request, but just go to the end of processing
4064 IF (p_sch_set_tbl(I).param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET AND
4065 l_ship_set_id <> p_sch_set_tbl(I).param1) OR
4066 (p_sch_set_tbl(I).param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET AND
4067 l_arrival_set_id <> p_sch_set_tbl(I).param1) THEN
4068 IF l_debug_level > 0 THEN
4069 OE_DEBUG_PUB.Add('Sets differs, goto END OF PROCESS',2);
4070 END IF;
4071 GOTO END_OF_PROCESS;
4072 END IF;
4073 -- end of bug3885953 }
4074
4075
4076
4077 IF l_ship_set_id is null
4078 AND l_arrival_set_id is null THEN
4079 -- 3870895
4080 IF l_top_model_line_id is not null
4081 AND l_top_model_line_id = p_sch_set_tbl(I).entity_id THEN
4082
4083 Update oe_order_lines_all l
4084 Set arrival_set_id = Null,
4085 ship_set_id = Null
4086 Where top_model_line_id = l_top_model_line_id
4087 And open_flag = 'Y';
4088
4089 END IF;
4090
4091 FOR optionrec in C5
4092 LOOP
4093
4094 IF l_debug_level > 0 THEN
4095 oe_debug_pub.add( 'UPDATE SHIPPING : CHILDREN OF MODEL ' || TO_CHAR ( OPTIONREC.LINE_ID ) , 1 ) ;
4096 END IF;
4097
4098 OE_Delayed_Requests_Pvt.Log_Request(
4099 p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
4100 p_entity_id => optionrec.line_id,
4101 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
4102 p_requesting_entity_id => optionrec.line_id,
4103 p_request_type => OE_GLOBALS.G_UPDATE_SHIPPING,
4104 p_request_unique_key1 => OE_GLOBALS.G_OPR_UPDATE,
4105 p_param1 => FND_API.G_TRUE,
4106 x_return_status => x_return_status);
4107
4108 End loop;
4109
4110 oe_debug_pub.add('Set does not exist on line ' || p_sch_set_tbl(I).entity_id,2);
4111
4112 goto END_OF_PROCESS;
4113
4114 END IF;
4115 IF (OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
4116 AND MSC_ATP_GLOBAL.GET_APS_VERSION = 10) THEN
4117
4118 IF l_top_model_line_id <> p_sch_set_tbl(I).entity_id
4119 AND l_top_model_line_id is not null
4120 THEN
4121 FOR J IN 1..p_sch_set_tbl.count LOOP
4122 IF (l_link_to_line_id = p_sch_set_tbl(J).entity_id
4123 AND l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED)
4124 OR l_top_model_line_id = p_sch_set_tbl(J).entity_id
4125 THEN
4126 --Parent exists.
4127 IF l_debug_level > 0 THEN
4128 oe_debug_pub.add( 'PARENT EXISTS: ' || P_SCH_SET_TBL ( J).ENTITY_ID , 1 ) ;
4129 END IF;
4130 l_line_exists := 'Y';
4131 EXIT;
4132 END IF;
4133 END LOOP;
4134 END IF; -- Not top
4135
4136 ELSE -- GOP Code
4137 -- If the line is present in the table ignore the line.
4138 IF l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
4139 THEN
4140 FOR J IN 1..p_sch_set_tbl.count LOOP
4141 IF l_link_to_line_id = p_sch_set_tbl(J).entity_id THEN
4142 --Parent exists.
4143 IF l_debug_level > 0 THEN
4144 oe_debug_pub.add( 'PARENT EXISTS: ' || P_SCH_SET_TBL ( J ) .ENTITY_ID , 1 ) ;
4145 END IF;
4146 l_line_exists := 'Y';
4147 EXIT;
4148 END IF;
4149 END LOOP;
4150 END IF; -- Included
4151
4152 END IF; -- GOP code
4153
4154 -- Do not go in to this code, if the line is a top model or
4155 -- an ato model
4156
4157 oe_debug_pub.add(' l_line_exists :' || l_line_exists,1);
4158 oe_debug_pub.add(' l_ato_line_id :' || l_ato_line_id,1);
4159 oe_debug_pub.add(' l_item_type_code :'|| l_item_type_code,1);
4160 IF l_line_exists = 'N'
4161 AND nvl(l_top_model_line_id,0) <> p_sch_set_tbl(I).entity_id
4162 AND NOT (l_item_type_code = OE_GLOBALS.G_ITEM_CLASS
4163 AND nvl(l_ato_line_id,-99) = p_sch_set_tbl(I).entity_id) THEN
4164
4165
4166 IF l_debug_level > 0 THEN
4167 oe_debug_pub.add( 'LINE IS SELECTED FOR PROCESSING ' || P_SCH_SET_TBL ( I ) .ENTITY_ID , 1 ) ;
4168 END IF;
4169 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
4170 OE_LINE_UTIL.Lock_Row(p_line_id => p_sch_set_tbl(I).entity_id,
4171 p_x_line_rec => l_line_rec,
4172 x_return_status => x_return_status);
4173
4174 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4176 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
4177 RAISE FND_API.G_EXC_ERROR;
4178 END IF;
4179
4180
4181 IF nvl(l_line_rec.shippable_flag,'N') = 'Y'
4182 AND l_line_rec.schedule_status_code is not null THEN
4183
4184 IF l_sales_order_id is null THEN
4185 l_sales_order_id :=
4186 OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(l_line_rec.header_id);
4187 END IF;
4188
4189 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
4190
4191 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
4192 ,p_line_id => l_line_rec.line_id
4193 ,p_org_id => l_line_rec.ship_from_org_id
4194 ,x_reserved_quantity => l_line_rec.reserved_quantity
4195 ,x_reserved_quantity2 => l_line_rec.reserved_quantity2
4196 );
4197
4198 /*l_line_rec.reserved_quantity :=
4199 OE_LINE_UTIL.Get_Reserved_Quantity
4200 (p_header_id => l_sales_order_id,
4201 p_line_id => l_line_rec.line_id,
4202 p_org_id => l_line_rec.ship_from_org_id);
4203
4204 l_line_rec.reserved_quantity2 := -- INVCONV
4205 OE_LINE_UTIL.Get_Reserved_Quantity2
4206 (p_header_id => l_sales_order_id,
4207 p_line_id => l_line_rec.line_id,
4208 p_org_id => l_line_rec.ship_from_org_id); */
4209 END IF;
4210
4211 IF l_line_rec.reserved_quantity IS NULL
4212 OR l_line_rec.reserved_quantity = FND_API.G_MISS_NUM THEN
4213 l_line_rec.reserved_quantity := 0;
4214 END IF;
4215 /* IF l_line_rec.reserved_quantity2 IS NULL -- INVCONV -- why was this commented out
4216 OR l_line_rec.reserved_quantity2 = FND_API.G_MISS_NUM THEN
4217 l_line_rec.reserved_quantity2 := 0;
4218 END IF; */
4219
4220
4221 -- Check if the line is part of Model and it is already scheduled.
4222 -- by its parent ??????????????????????????/
4223
4224 K := K + 1;
4225
4226 l_old_line_tbl(K) := l_line_rec;
4227 l_line_tbl(K) := l_line_rec;
4228
4229 l_old_line_tbl(K).schedule_ship_date := null;
4230 l_old_line_tbl(K).schedule_arrival_date:= null;
4231 l_old_line_tbl(K).schedule_ship_date := p_sch_set_tbl(I).date_param1;
4232 l_old_line_tbl(K).schedule_arrival_date:= p_sch_set_tbl(I).date_param2;
4233 l_old_line_tbl(K).request_date := p_sch_set_tbl(I).date_param3;
4234 -- bug 3850293; FP - added the IF condition
4235 IF p_sch_set_tbl(I).param4 IS NOT NULL THEN
4236 l_old_line_tbl(K).ship_from_org_id := p_sch_set_tbl(I).param4;
4237 END IF;
4238 l_old_line_tbl(K).ship_to_org_id := p_sch_set_tbl(I).param5;
4239
4240 -- l_old_line_tbl(K).ship_set_id := p_sch_set_tbl(I).param6;
4241 -- l_old_line_tbl(K).arrival_set_id := p_sch_set_tbl(I).param7;
4242
4243 l_old_set_tbl(k).ship_set_id := p_sch_set_tbl(I).param6;
4244 l_old_set_tbl(k).arrival_set_id := p_sch_set_tbl(I).param7;
4245
4246 /* Start Audit Trail */
4247 l_line_tbl(K).change_reason := 'SYSTEM';
4248 l_line_tbl(K).change_comments := 'Delayed Request , Scheduling';
4249 /* End Audit Trail */
4250
4251 l_line_tbl(K).operation := OE_GLOBALS.G_OPR_UPDATE;
4252
4253 /* Commented the above line to fix the bug 2916814 */
4254 --3564302 (#1)
4255 -- Get the order date type code
4256 l_type_code := oe_schedule_util.Get_Date_Type(l_line_tbl(K).header_id);
4257
4258 IF p_sch_set_tbl(I).param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET
4259 THEN
4260 -- Ship set date.
4261 l_line_tbl(K).Schedule_ship_date := p_sch_set_tbl(I).date_param4;
4262 -- 3564302
4263 IF l_type_code = 'ARRIVAL' THEN
4264 l_line_tbl(K).Schedule_arrival_date := p_sch_set_tbl(I).date_param5;
4265 END IF;
4266 IF p_sch_set_tbl(I).param10 is not null THEN
4267 l_line_tbl(K).ship_from_org_id := p_sch_set_tbl(I).param10;
4268 l_line_tbl(k).re_source_flag := 'N';
4269 END IF;
4270 IF fnd_profile.value('ONT_SHIP_METHOD_FOR_SHIP_SET') = 'Y' THEN
4271 oe_debug_pub.add('ONT_SHIP_METHOD_FOR_SHIP_SET' || p_sch_set_tbl(I).param11,2);
4272 l_line_tbl(K).shipping_method_code := p_sch_set_tbl(I).param11;
4273 END IF;
4274 END IF;
4275
4276 IF p_sch_set_tbl(I).param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET
4277 THEN
4278 l_line_tbl(K).Schedule_arrival_date := p_sch_set_tbl(I).date_param5;
4279 -- 3564302(issue 1)
4280 IF l_type_code = 'SHIP' THEN
4281 l_line_tbl(K).Schedule_ship_date := p_sch_set_tbl(I).date_param4;
4282 END IF;
4283 END IF;
4284
4285 -- Assign the value from the sets table. That way we are
4286 -- enforcing all the lines to have a same ship to.
4287
4288 IF p_sch_set_tbl(I).param9 is not null THEN
4289 l_line_tbl(K).ship_to_org_id := p_sch_set_tbl(I).param9;
4290 END IF;
4291
4292 l_line_tbl(K).schedule_action_code :=
4293 OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE;
4294
4295
4296 IF l_line_tbl(K).arrival_set_id is not null THEN
4297 l_line_tbl(K).arrival_set := l_line_tbl(K).arrival_set_id;
4298 ELSE
4299 l_line_tbl(K).ship_set := l_line_tbl(K).ship_set_id;
4300 END IF;
4301 END IF; -- Line Exists.
4302
4303
4304 IF l_item_type_code = 'CLASS'
4305 OR l_item_type_code = 'KIT'
4306 OR l_item_type_code = 'MODEL'
4307 THEN
4308
4309 oe_debug_pub.add('line_id ' || p_sch_set_tbl(I).entity_id,3);
4310 oe_debug_pub.add(' top line ' || l_top_model_line_id,3);
4311 oe_debug_pub.add(' ato line ' || l_ato_line_id,3);
4312 oe_debug_pub.add(' item type ' || l_item_type_code,3);
4313 oe_debug_pub.add(' header_id ' || l_header_id,3);
4314
4315 IF p_sch_set_tbl(I).entity_id = nvl(l_top_model_line_id,0) THEN
4316 Oe_Config_Schedule_Pvt.Query_Set_Lines
4317 (p_header_id => l_header_id,
4318 p_model_line_id => l_top_model_line_id,
4319 p_sch_action => 'SCHEDULE',
4320 x_line_tbl => l_iline_tbl,
4321 x_return_status => x_return_status);
4322
4323 ELSIF (l_ato_line_id is not null AND
4324 NOT (l_ato_line_id = p_sch_set_tbl(I).entity_id AND
4325 l_item_type_code IN (OE_GLOBALS.G_ITEM_STANDARD,
4326 OE_GLOBALS.G_ITEM_OPTION)))
4327 THEN
4328
4329 OE_Config_Util.Query_ATO_Options
4330 ( p_ato_line_id => l_ato_line_id
4331 ,p_send_cancel_lines => 'Y'
4332 ,p_source_type => OE_Globals.G_SOURCE_INTERNAL
4333 ,x_line_tbl => l_iline_tbl);
4334
4335
4336 ELSE
4337
4338 Oe_Config_Schedule_Pvt.Query_Set_Lines
4339 (p_header_id => l_header_id,
4340 p_link_to_line_id => p_sch_set_tbl(I).entity_id,
4341 p_sch_action => 'SCHEDULE',
4342 x_line_tbl => l_iline_tbl,
4343 x_return_status => x_return_status);
4344
4345 END IF;
4346
4347 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4349 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
4350 RAISE FND_API.G_EXC_ERROR;
4351 END IF;
4352 -- 3564310
4353 -- Line is no longer with the set
4354 -- rollback the changes.
4355 IF l_ship_set_id is null
4356 AND l_arrival_set_id IS null THEN
4357 FOR I IN 1..l_iline_tbl.count LOOP
4358 IF l_iline_tbl(I).schedule_status_code IS NULL THEN
4359
4360 UPDATE OE_ORDER_LINES_ALL
4361 SET SCHEDULE_SHIP_DATE = Null,
4362 SCHEDULE_ARRIVAL_DATE = Null,
4363 SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
4364 SHIP_SET_ID = Null,
4365 ARRIVAL_SET_ID = Null,
4366 override_atp_date_code = Null
4367 WHERE line_id = l_iline_tbl(I).line_id;
4368
4369 ELSE
4370 UPDATE OE_ORDER_LINES_ALL
4371 SET
4372 SCHEDULE_SHIP_DATE = p_sch_set_tbl(I).date_param1,
4373 SCHEDULE_ARRIVAL_DATE = p_sch_set_tbl(I).date_param2,
4374 SHIP_FROM_ORG_ID = p_sch_set_tbl(I).param4,
4375 SHIP_SET_ID = p_sch_set_tbl(I).param6,
4376 ARRIVAL_SET_ID = p_sch_set_tbl(I).param7
4377 WHERE line_id = l_iline_tbl(I).line_id;
4378 END IF;
4379 -- 4026758
4380 IF l_iline_tbl(I).ship_set_id IS NOT NULL
4381 OR l_iline_tbl(I).arrival_set_id IS NOT NULL THEN
4382 oe_schedule_util.Log_Delete_Set_Request
4383 (p_header_id => l_iline_tbl(I).header_id,
4384 p_line_id => l_iline_tbl(I).line_id,
4385 p_set_id => nvl(l_iline_tbl(I).ship_set_id,
4386 l_iline_tbl(I).arrival_set_id),
4387 x_return_status => x_return_status);
4388 IF l_debug_level > 0 THEN
4389 oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST '
4390 || X_RETURN_STATUS , 1 ) ;
4391 END IF;
4392 END IF;
4393
4394 IF l_iline_tbl(I).shipping_interfaced_flag = 'Y' THEN
4395
4396 oe_debug_pub.add('Line is interfaced ',2);
4397
4398 OE_Delayed_Requests_Pvt.Log_Request(
4399 p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
4400 p_entity_id => l_iline_tbl(I).line_id,
4401 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
4402 p_requesting_entity_id => l_iline_tbl(I).line_id,
4403 p_request_type => OE_GLOBALS.G_UPDATE_SHIPPING,
4404 p_request_unique_key1 => OE_GLOBALS.G_OPR_UPDATE,
4405 p_param1 => FND_API.G_TRUE,
4406 x_return_status => x_return_status);
4407
4408 END IF;
4409 END LOOP;
4410 EXIT;
4411 END IF;
4412
4413 IF l_debug_level > 0 THEN
4414 oe_debug_pub.add( 'INCLUDED ITEM COUNT ' || L_ILINE_TBL.COUNT,2);
4415 END IF;
4416
4417 -- Start 2716220 --
4418 -- To check schedule dates with override flag
4419
4420 l_type_code := oe_schedule_util.Get_Date_Type(l_header_id);
4421
4422 IF l_arrival_set_id is not null THEN
4423
4424 l_set_rec := OE_ORDER_CACHE.Load_Set
4425 (l_arrival_set_id);
4426
4427 ELSIF l_ship_set_id is not null THEN
4428
4429 l_set_rec := OE_ORDER_CACHE.Load_Set
4430 (l_Ship_set_id);
4431 END IF;
4432
4433 l_log_error := FALSE;
4434
4435 FOR O IN 1..l_iline_tbl.count LOOP
4436 IF NVL(l_iline_tbl(O).override_atp_date_code,'N') = 'Y'
4437 THEN
4438
4439 IF l_type_code = 'ARRIVAL' THEN
4440 IF l_iline_tbl(O).schedule_arrival_date IS NOT NULL
4441 AND l_set_rec.schedule_arrival_date IS NOT NULL
4442 AND l_iline_tbl(O).schedule_arrival_date <> l_set_rec.schedule_arrival_date
4443 THEN
4444 oe_debug_pub.add('Arr date does not match with set date' || l_iline_tbl(O).line_id,2);
4445 l_log_error := TRUE;
4446 END IF;
4447
4448 ELSE
4449
4450 IF l_iline_tbl(O).schedule_ship_date IS NOT NULL
4451 AND l_set_rec.schedule_ship_date IS NOT NULL
4452 AND l_iline_tbl(O).schedule_ship_date <> l_set_rec.schedule_ship_date
4453 THEN
4454 oe_debug_pub.add('Sch date does not match with set date' || l_iline_tbl(O).line_id,2);
4455 l_log_error := TRUE;
4456 END IF;
4457 END IF;
4458 END IF;
4459 END LOOP;
4460
4461 IF l_log_error THEN
4462 FND_MESSAGE.SET_NAME('ONT','OE_SCH_OVER_ATP_SET_NO_MATCH');
4463 OE_MSG_PUB.Add;
4464 x_return_status := FND_API.G_RET_STS_ERROR;
4465 RAISE FND_API.G_EXC_ERROR;
4466 END IF;
4467
4468 FOR M IN 1..l_iline_tbl.count LOOP
4469 IF l_debug_level > 0 THEN
4470 oe_debug_pub.add('LINE_ID: ' || l_iline_tbl(M).LINE_ID, 1);
4471 END IF;
4472
4473 K := K + 1;
4474
4475 -- BUG 1282873
4476 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
4477 IF l_line_rec.override_atp_date_code = 'Y' THEN
4478 l_iline_tbl(M).override_atp_date_code := l_line_rec.override_atp_date_code;
4479 IF l_debug_level > 0 THEN
4480 oe_debug_pub.add( 'OVERRIDE_ATP :' || L_ILINE_TBL(M).OVERRIDE_ATP_DATE_CODE , 3 ) ;
4481 END IF;
4482 END IF;
4483 END IF;
4484 -- END 1282873
4485
4486 l_line_tbl(K) := l_iline_tbl(M);
4487 l_old_line_tbl(K) := l_iline_tbl(M);
4488 l_old_line_tbl(K).schedule_ship_date := null;
4489 l_old_line_tbl(K).schedule_arrival_date := null;
4490 l_old_line_tbl(K).schedule_ship_date := p_sch_set_tbl(I).date_param1;
4491 l_old_line_tbl(K).schedule_arrival_date:= p_sch_set_tbl(I).date_param2;
4492 l_old_line_tbl(K).request_date := p_sch_set_tbl(I).date_param3;
4493 --bug 3850293; FP - added IF condition
4494 IF p_sch_set_tbl(I).param4 IS NOT NULL THEN
4495 l_old_line_tbl(K).ship_from_org_id := p_sch_set_tbl(I).param4;
4496 END IF;
4497 l_old_line_tbl(K).ship_to_org_id := p_sch_set_tbl(I).param5;
4498
4499 -- l_old_line_tbl(K).ship_set_id := p_sch_set_tbl(I).param6;
4500 -- l_old_line_tbl(K).arrival_set_id := p_sch_set_tbl(I).param7;
4501
4502
4503 l_old_set_tbl(k).ship_set_id := p_sch_set_tbl(I).param6;
4504 l_old_set_tbl(k).arrival_set_id := p_sch_set_tbl(I).param7;
4505
4506 /* Start Audit Trail */
4507 l_line_tbl(K).change_reason := 'SYSTEM';
4508 l_line_tbl(K).change_comments := 'Delayed Request , Scheduling';
4509 /* End Audit Trail */
4510
4511 l_line_tbl(K).operation := OE_GLOBALS.G_OPR_UPDATE;
4512
4513 IF p_sch_set_tbl(I).param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET
4514 THEN
4515 -- Ship set date.
4516 l_line_tbl(K).Schedule_ship_date := p_sch_set_tbl(I).date_param4;
4517 -- 3564302
4518 IF l_type_code = 'ARRIVAL' THEN
4519 l_line_tbl(K).Schedule_arrival_date := p_sch_set_tbl(I).date_param5;
4520 END IF;
4521 IF p_sch_set_tbl(I).param10 is not null THEN
4522 l_line_tbl(K).ship_from_org_id := p_sch_set_tbl(I).param10;
4523 l_line_tbl(k).re_source_flag := 'N';
4524 END IF;
4525 IF fnd_profile.value('ONT_SHIP_METHOD_FOR_SHIP_SET') = 'Y' THEN
4526 oe_debug_pub.add('ONT_SHIP_METHOD_FOR_SHIP_SET Model:' || p_sch_set_tbl(I).param11,1);
4527 l_line_tbl(K).shipping_method_code := p_sch_set_tbl(I).param11;
4528 END IF;
4529 END IF;
4530
4531 IF p_sch_set_tbl(I).param8
4532 = OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET
4533 THEN
4534 l_line_tbl(K).Schedule_arrival_date
4535 := p_sch_set_tbl(I).date_param5;
4536 -- 3564302
4537 IF l_type_code = 'SHIP' THEN
4538 l_line_tbl(K).Schedule_ship_date := p_sch_set_tbl(I).date_param4;
4539 END IF;
4540 END IF;
4541
4542 -- Assign the value from the sets table. That way we are
4543 -- enforcing all the lines to have a same ship to.
4544
4545 IF p_sch_set_tbl(I).param9 is not null THEN
4546 l_line_tbl(K).ship_to_org_id := p_sch_set_tbl(I).param9;
4547 END IF;
4548
4549 l_line_tbl(K).schedule_action_code :=
4550 OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE;
4551
4552 IF l_line_tbl(K).arrival_set_id is not null THEN
4553 l_line_tbl(K).arrival_set := l_line_tbl(K).arrival_set_id;
4554 ELSE
4555 l_line_tbl(K).ship_set := l_line_tbl(K).ship_set_id;
4556 END IF;
4557 END LOOP; -- End of included item
4558 END IF; -- Class or KIT
4559
4560 <<END_OF_PROCESS>>
4561 Null;
4562 END LOOP; -- Main loop.
4563
4564 IF l_debug_level > 0 THEN
4565 oe_debug_pub.add('Line Count :' || l_line_tbl.count,1);
4566 END IF;
4567 --3564310
4568 -- Validate the lines befor calling process_group
4569 IF l_line_tbl.count > 0 THEN
4570
4571
4572 Validate_Group
4573 (p_x_line_tbl => l_line_tbl,
4574 p_sch_action => 'SCHEDULE',
4575 x_return_status => x_return_status);
4576
4577 END IF;
4578
4579 IF l_debug_level > 0 THEN
4580 oe_debug_pub.add( 'AFTER CALLING Validate Group ' || X_RETURN_STATUS
4581 || l_line_tbl.count , 1 ) ;
4582 END IF;
4583 -- 3564310
4584 IF l_line_tbl.count > 0
4585 AND x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4586 Oe_Config_Schedule_Pvt.Process_Group
4587 (p_x_line_tbl => l_line_tbl
4588 ,p_old_line_tbl => l_old_line_tbl
4589 ,p_caller => 'SET'
4590 ,p_sch_action => OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE
4591 ,p_partial_set => TRUE
4592 ,p_part_of_set => l_part_of_set --4405004
4593 ,x_return_status => x_return_status);
4594
4595 IF l_debug_level > 0 THEN
4596 oe_debug_pub.add('After call to Process Group :' || x_return_status ,1);
4597 END IF;
4598 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4600 END IF;
4601 END IF;
4602
4603 -- Additional process we are doing to fix bug 2232950.
4604 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4605
4606 IF l_debug_level > 0 THEN
4607 oe_debug_pub.add('Compare output with requested values',1);
4608 END IF;
4609
4610 FOR I IN 1..l_line_tbl.count LOOP
4611
4612 IF p_sch_set_tbl(1).param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET
4613 AND trunc(l_line_tbl(I).Schedule_ship_date) <> trunc(p_sch_set_tbl(1).date_param4)
4614 THEN
4615 IF l_debug_level > 0 THEN
4616 oe_debug_pub.add('Not received correct values for ship set',1);
4617 END IF;
4618 x_return_status := FND_API.G_RET_STS_ERROR;
4619 EXIT;
4620 ELSIF p_sch_set_tbl(1).param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET
4621 AND trunc(l_line_tbl(I).Schedule_arrival_date) <> trunc(p_sch_set_tbl(1).date_param5)
4622 THEN
4623 IF l_debug_level > 0 THEN
4624 oe_debug_pub.add('Not received correct values for arrival set',1);
4625 END IF;
4626 x_return_status := FND_API.G_RET_STS_ERROR;
4627 EXIT;
4628 END IF;
4629
4630 END LOOP;
4631 END IF; -- Success.
4632
4633 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4634
4635 IF l_debug_level > 0 THEN
4636 oe_debug_pub.add('Line Id error out : ' || l_line_rec.line_id,2);
4637 END IF;
4638
4639 -- Could not schedule the line on the set date. Let's schedule
4640 -- the whole set to see if we get another date got the whole
4641 -- set.
4642
4643 IF fnd_profile.value('ONT_AUTO_PUSH_GRP_DATE') = 'Y' THEN
4644
4645 IF l_debug_level > 0 THEN
4646 oe_debug_pub.add('Auto Push Group Date is Yes',2);
4647 END IF;
4648
4649 -- Added this stmt to fix big 1899651.
4650 l_request_rec := p_sch_set_tbl(1);
4651 l_request_rec.param3 := OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE;
4652 -- 3384975
4653 l_request_rec.param12 := l_param12;
4654
4655 IF l_debug_level > 0 THEN
4656 oe_debug_pub.add('Stng G_CASCADING_REQUEST_LOGGED to TRUE',2);
4657 END IF;
4658 OE_GLOBALS.G_CASCADING_REQUEST_LOGGED := TRUE;
4659
4660 Schedule_Set(p_request_rec => l_request_rec,
4661 x_return_status => x_return_status);
4662
4663 ELSE
4664
4665 IF l_debug_level > 0 THEN
4666 oe_debug_pub.add('Before setting message for group failure',2);
4667 END IF;
4668
4669 FND_MESSAGE.SET_NAME('ONT','OE_SCH_GROUP_MEMBER_FAILED');
4670 OE_MSG_PUB.Add;
4671
4672 END IF; -- If Auto Push Group Date is Yes
4673
4674 -- Scheduling Failed. If the line belongs to a Ship Set or Arrival
4675 -- Set, then just clear out the scheduling attributes and return a
4676 -- message that the line schedule failed. We will return a success
4677 -- since we do not want to fail the line insert due to this.
4678
4679 IF l_debug_level > 0 THEN
4680 oe_debug_pub.add('Calling Update Set');
4681 END IF;
4682
4683 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4684
4685 IF l_debug_level > 0 THEN
4686 oe_debug_pub.add('Unable to schedule the line for set date');
4687 END IF;
4688
4689 FOR I IN 1..l_line_tbl.count LOOP
4690 IF l_line_tbl(I).top_model_line_id IS NOT NULL
4691 AND l_line_tbl(I).top_model_line_id <> l_line_tbl(I).line_id THEN
4692
4693 -- Scheduling Failed. If the line belongs to a ATO Model or SMC
4694 -- PTO, then return an error, since the option cannot be inserted
4695 -- to a scheduled ATO or SMC PTO if it cannot be scheduled on
4696 -- the same date as that of the model.
4697
4698 IF l_debug_level > 0 THEN
4699 oe_debug_pub.add('Line belong to model');
4700 END IF;
4701 fnd_message.set_name('ONT','OE_SCH_SET_INS_FAILED');
4702 OE_MSG_PUB.Add;
4703 RAISE FND_API.G_EXC_ERROR;
4704
4705 ELSE
4706 -- If the line is being added to set is a standard line
4707 -- save the line without scheduling attributes.
4708 IF l_debug_level > 0 THEN
4709 oe_debug_pub.add('Standard line is failed');
4710 END IF;
4711 fnd_message.set_name('ONT','OE_SCH_SET_INS_FAILED');
4712 OE_MSG_PUB.Add;
4713
4714 IF l_line_tbl(I).schedule_status_code IS NULL
4715 THEN
4716
4717 UPDATE OE_ORDER_LINES_ALL
4718 SET SCHEDULE_SHIP_DATE = Null,
4719 SCHEDULE_ARRIVAL_DATE = Null,
4720 SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
4721 SHIP_SET_ID = Null,
4722 ARRIVAL_SET_ID = Null,
4723 override_atp_date_code = Null
4724 WHERE line_id = l_line_tbl(I).line_id;
4725
4726 ELSE
4727 -- fix for 3557779
4728 -- update schedule_status_code and visible_demand_flag fields in
4729 -- addition to previously updated fields
4730 UPDATE OE_ORDER_LINES_ALL
4731 SET
4732 SCHEDULE_STATUS_CODE = l_old_line_tbl(I).schedule_status_code,
4733 VISIBLE_DEMAND_FLAG = l_old_line_tbl(I).visible_demand_flag,
4734 SCHEDULE_SHIP_DATE = l_old_line_tbl(I).schedule_ship_date,
4735 SCHEDULE_ARRIVAL_DATE = l_old_line_tbl(I).schedule_arrival_date,
4736 SHIP_FROM_ORG_ID = l_old_line_tbl(I).ship_from_org_id,
4737 SHIP_SET_ID = l_old_set_tbl(I).ship_set_id,
4738 ARRIVAL_SET_ID = l_old_set_tbl(I).arrival_set_id
4739 WHERE line_id = l_line_tbl(I).line_id;
4740 END IF;
4741 -- 4026758
4742 IF l_line_tbl(I).ship_set_id IS NOT NULL
4743 OR l_line_tbl(I).arrival_set_id IS NOT NULL THEN
4744 oe_schedule_util.Log_Delete_Set_Request
4745 (p_header_id => l_line_tbl(I).header_id,
4746 p_line_id => l_line_tbl(I).line_id,
4747 p_set_id => nvl(l_line_tbl(I).ship_set_id,l_line_tbl(I).arrival_set_id),
4748 x_return_status => x_return_status);
4749 IF l_debug_level > 0 THEN
4750 oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST '
4751 || X_RETURN_STATUS , 1 ) ;
4752 END IF;
4753 END IF;
4754
4755 IF l_line_tbl(I).shipping_interfaced_flag = 'Y'
4756 AND l_line_tbl(I).Ordered_quantity > 0 THEN
4757 OE_Delayed_Requests_Pvt.Log_Request(
4758 p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
4759 p_entity_id => l_line_tbl(I).line_id,
4760 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
4761 p_requesting_entity_id => l_line_tbl(I).line_id,
4762 p_request_type => OE_GLOBALS.G_UPDATE_SHIPPING,
4763 p_request_unique_key1 => OE_GLOBALS.G_OPR_UPDATE,
4764 p_param1 => FND_API.G_TRUE,
4765 x_return_status => x_return_status);
4766 END IF;
4767 END IF; -- Part of Model.
4768 END LOOP;
4769 -- 3384975
4770 IF l_param12 = 'N' THEN
4771 x_return_status := FND_API.G_RET_STS_SUCCESS;
4772 END IF;
4773
4774 END IF; -- x_return_status = succ/error
4775
4776 END IF; -- If g_ret_status is error
4777
4778 l_old_set_tbl.delete;
4779 IF l_debug_level > 0 THEN
4780 oe_debug_pub.add('Exiting OE_Delayed_Requests_UTIL.Schedule_set_lines');
4781 END IF;
4782 EXCEPTION
4783
4784 WHEN FND_API.G_EXC_ERROR THEN
4785 x_return_status := FND_API.G_RET_STS_ERROR;
4786 l_old_set_tbl.delete;
4787 IF l_debug_level > 0 THEN
4788 oe_debug_pub.add('No data from expected error',1);
4789 END IF;
4790 RAISE FND_API.G_EXC_ERROR;
4791
4792 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4793
4794 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4795 THEN
4796 OE_MSG_PUB.Add_Exc_Msg
4797 ( G_PKG_NAME
4798 , 'Schedule_set_lines'
4799 );
4800 END IF;
4801 IF l_debug_level > 0 THEN
4802 oe_debug_pub.add('No data from unexpected error',1);
4803 END IF;
4804
4805 l_old_set_tbl.delete;
4806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4807
4808 WHEN OTHERS THEN
4809
4810
4811 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4812 THEN
4813 OE_MSG_PUB.Add_Exc_Msg
4814 ( G_PKG_NAME
4815 , 'Schedule_set_lines'
4816 );
4817 END IF;
4818 IF l_debug_level > 0 THEN
4819 oe_debug_pub.add('No data from unexpected error',1);
4820 END IF;
4821 l_old_set_tbl.delete;
4822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4823
4824 END Schedule_set_lines;
4825
4826 /* ---------------------------------------------------------------
4827 Procedure : Schedule_Set
4828 Description:
4829 p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
4830 p_entity_id => p_line_rec.line_id,
4831 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
4832 p_requesting_entity_id => p_line_rec.line_id,
4833 p_request_type => OE_GLOBALS.G_GROUP_SCHEDULE,
4834 p_param1 => l_param1,ship_set_id/arrival_set_id
4835 p_param2 => p_line_rec.header_id,
4836 p_param3 => l_action,
4837 p_param4 => p_old_line_rec.ship_from_org_id,
4838 p_param5 => p_old_line_rec.ship_to_org_id,
4839 p_date_param1 => p_old_line_rec.schedule_ship_date,
4840 p_date_param2 => p_old_line_rec.schedule_arrival_date,
4841 p_date_param3 => p_old_line_rec.request_date,
4842 p_param6 => p_old_line_rec.ship_set_id,
4843 p_param7 => p_old_line_rec.arrival_set_id,
4844 p_param8 => l_entity_type,
4845 p_param11 => l_shipping_method_code
4846
4847 Changes have been made to copy override_atp flag from model/class/
4848 kit to it's included items when the flag is set. Code has been added to
4849 cascade the override_atp flag to all the line in an ato model.
4850 ---------------------------------------------------------------*/
4851 Procedure Schedule_Set(p_request_rec IN OE_ORDER_PUB.request_rec_type,
4852 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
4853 IS
4854 l_line_tbl OE_ORDER_PUB.line_tbl_type;
4855 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
4856 l_line_rec OE_ORDER_PUB.line_rec_type;
4857 l_old_line_rec OE_ORDER_PUB.line_rec_type;
4858 l_msg_count NUMBER;
4859 l_msg_data VARCHAR2(2000);
4860 l_set_rec OE_ORDER_CACHE.set_rec_type;
4861 l_set_name VARCHAR2(30);
4862 l_set_id NUMBER := null;
4863 l_ship_set_id NUMBER := null;
4864 l_arrival_set_id NUMBER := null;
4865
4866 l_entity_type VARCHAR2(30);
4867 l_action VARCHAR2(30);
4868 l_line_id NUMBER;
4869 l_header_id NUMBER;
4870 l_old_schedule_ship_date DATE := Null;
4871 l_old_schedule_arrival_date DATE := Null;
4872 l_old_request_date DATE := Null;
4873 l_old_ship_from_org_id NUMBER := Null;
4874 l_old_ship_set_id NUMBER := Null;
4875 l_old_arrival_set_id NUMBER := Null;
4876
4877 l_set_Schedule_ship_date DATE := Null;
4878 l_set_Schedule_arrival_date DATE := Null;
4879 l_set_ship_to_org_id NUMBER := Null;
4880 l_set_ship_from_org_id NUMBER := Null;
4881
4882 l_ship_to_org_id NUMBER := NULL;
4883 l_schedule_ship_date DATE := NULL;
4884 l_schedule_arrival_date DATE := NULL;
4885 l_ship_from_org_id NUMBER := NULL;
4886 l_request_date DATE := NULL;
4887 l_shipping_method_code VARCHAR2(30);
4888 l_Freight_Carrier_Code VARCHAR2(30);
4889 l_shipment_priority_code VARCHAR2(30);
4890 l_can_bypass BOOLEAN := FALSE;
4891 l_set_overridden BOOLEAN := FALSE;
4892 -- Start 2716220 --
4893 l_override_ship_date DATE := NULL;
4894 l_override_arrival_date DATE := NULL;
4895 l_log_error BOOLEAN := FALSE;
4896 l_operation VARCHAR2(30) := 'UPDATE';
4897 --
4898 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4899 --
4900 /* Added the following 2 line to fix the bug 2740480 */
4901 l_push_logic VARCHAR2(1) := 'N';
4902 l_date_changed VARCHAR2(1) := 'N';
4903
4904 l_index NUMBER;
4905 l_count NUMBER := 0; -- 5043206
4906
4907 BEGIN
4908 IF l_debug_level > 0 THEN
4909 oe_debug_pub.add( 'ENTERING SCHEDULE_SET' , 1 ) ;
4910 END IF;
4911
4912 l_entity_type := p_request_rec.param8;
4913 l_action := p_request_rec.param3;
4914 l_line_id := p_request_rec.entity_id;
4915 l_header_id := p_request_rec.param2;
4916 l_old_schedule_ship_date := p_request_rec.date_param1;
4917 l_old_schedule_arrival_date := p_request_rec.date_param2;
4918 l_old_request_date := p_request_rec.date_param3;
4919 l_old_ship_from_org_id := p_request_rec.param4;
4920 l_old_ship_set_id := p_request_rec.param6;
4921 l_old_arrival_set_id := p_request_rec.param7;
4922 l_set_ship_to_org_id := p_request_rec.param9;
4923 l_set_ship_from_org_id := p_request_rec.param10;
4924 /* Added the following 1 line to fix the bug 2740480 */
4925 l_push_logic := nvl(p_request_rec.param13,'N');
4926 l_operation := p_request_rec.param14;
4927 l_set_schedule_ship_date := p_request_rec.date_param4;
4928 l_set_schedule_arrival_date := p_request_rec.date_param5;
4929
4930 IF l_debug_level > 0 THEN
4931 oe_debug_pub.add( 'L_OLD_SCHEDULE_SHIP_DATE :'
4932 || L_OLD_SCHEDULE_SHIP_DATE , 1 ) ;
4933 oe_debug_pub.add( 'L_OLD_REQUEST_DATE :' || L_OLD_REQUEST_DATE , 1 ) ;
4934 oe_debug_pub.add( 'L_LINE_ID :' || L_LINE_ID , 1 ) ;
4935 oe_debug_pub.add( 'L_OLD_SHIP_SET_ID :' || L_OLD_SHIP_SET_ID , 1 ) ;
4936 END IF;
4937
4938 Select Schedule_ship_date, Schedule_arrival_date,
4939 ship_to_org_id, ship_from_org_id, request_date,
4940 Ship_set_id,arrival_Set_id
4941 Into l_Schedule_ship_date, l_Schedule_arrival_date,
4942 l_ship_to_org_id, l_ship_from_org_id, l_request_date,
4943 l_ship_set_id,l_arrival_set_id
4944 From OE_ORDER_LINES_ALL
4945 Where line_id = l_line_id;
4946
4947 -- Schedule set lines is called it will have param4 as
4948 -- set date, use the same to schedule the line.
4949
4950 IF l_ship_set_id is null
4951 AND l_arrival_set_id is null
4952 THEN
4953
4954 -- 5043206
4955 BEGIN
4956
4957 IF p_request_rec.param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET THEN
4958
4959 Select 1 into l_count from OE_ORDER_LINES_ALL
4960 WHERE header_id = l_header_id and arrival_Set_id = p_request_rec.param1
4961 AND rownum = 1;
4962
4963 ELSIF p_request_rec.param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET THEN
4964
4965 Select 1 into l_count from OE_ORDER_LINES_ALL
4966 WHERE header_id = l_header_id and ship_set_id = p_request_rec.param1
4967 AND rownum = 1;
4968
4969 END IF;
4970
4971 EXCEPTION
4972 WHEN OTHERS THEN
4973
4974 IF l_debug_level > 0 THEN
4975 oe_debug_pub.add( 'No Records found after checking For Lines in set' , 1 ) ;
4976 END IF;
4977
4978 END ;
4979
4980 IF l_count = 0 THEN
4981
4982 -- Delalyed request is logged for set id and the same
4983 -- is not available any more in the db. Do not process
4984 -- the request.
4985 goto END_OF_PROCESS;
4986
4987 END IF ;
4988
4989 END IF;
4990 /*
4991 IF p_request_rec.date_param4 IS NOT NULL
4992 AND p_request_rec.date_param4 <> FND_API.G_MISS_DATE THEN
4993 l_Schedule_ship_date := p_request_rec.date_param4;
4994 END IF;
4995 */
4996 IF l_debug_level > 0 THEN
4997 oe_debug_pub.add( 'L_SCHEDULE_SHIP_DATE :' || L_SCHEDULE_SHIP_DATE , 1 ) ;
4998 oe_debug_pub.add( 'L_REQUEST_DATE :' || L_REQUEST_DATE , 1 ) ;
4999 oe_debug_pub.add( 'L_LINE_ID :' || L_LINE_ID , 1 ) ;
5000 oe_debug_pub.add( 'L_SET_ID :' || L_SET_ID , 1 ) ;
5001 END IF;
5002
5003 l_set_id := p_request_rec.param1;
5004 IF l_debug_level > 0 THEN
5005 oe_debug_pub.add( 'CALLING QUERY_SET_LINES' , 1 ) ;
5006 END IF;
5007 BEGIN
5008
5009 IF p_request_rec.param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET THEN
5010
5011 Oe_Config_Schedule_Pvt.Query_Set_Lines
5012 (p_header_id => l_header_id,
5013 p_arrival_set_id => l_set_id,
5014 p_sch_action => l_action,
5015 x_line_tbl => l_line_tbl,
5016 x_return_status => x_return_status);
5017
5018 ELSIF p_request_rec.param8 = OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET THEN
5019
5020 Oe_Config_Schedule_Pvt.Query_Set_Lines
5021 (p_header_id => l_header_id,
5022 p_ship_set_id => l_set_id,
5023 p_sch_action => l_action,
5024 x_line_tbl => l_line_tbl,
5025 x_return_status => x_return_status);
5026
5027 END IF;
5028
5029 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5031 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5032 RAISE FND_API.G_EXC_ERROR;
5033 END IF;
5034
5035 EXCEPTION
5036 WHEN OTHERS THEN
5037 IF l_debug_level > 0 THEN
5038 oe_debug_pub.add( 'ERROR AFTER CALLING QUERY_SET_LINES' , 1 ) ;
5039 END IF;
5040 goto END_OF_PROCESS;
5041 END;
5042
5043 IF l_debug_level > 0 THEN
5044 oe_debug_pub.add( 'AFTER CALLING QUERY_SET_LINES' , 1 ) ;
5045 oe_debug_pub.add( 'COUNT IS ' || L_LINE_TBL.COUNT , 1 ) ;
5046 END IF;
5047
5048 -- Start 2716220 --
5049 -- To check schedule dates with override flag
5050 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
5051 FOR I IN 1..l_line_tbl.count LOOP
5052 IF NVL(l_line_tbl(I).override_atp_date_code,'N') = 'Y'
5053 AND l_line_tbl(I).schedule_status_code is NULL
5054 THEN
5055 IF l_override_ship_date IS NULL THEN
5056 l_override_ship_date := l_line_tbl(I).schedule_ship_date;
5057 ELSIF l_line_tbl(I).schedule_ship_date <> l_override_ship_date THEN
5058 -- Dates are different,log the error
5059 l_log_error := TRUE;
5060 EXIT;
5061 END IF;
5062 IF l_override_arrival_date IS NULL THEN
5063 l_override_arrival_date := l_line_tbl(I).schedule_arrival_date;
5064 ELSIF l_line_tbl(I).schedule_arrival_date <> l_override_arrival_date THEN
5065 -- log the error
5066 l_log_error := TRUE;
5067 EXIT;
5068 END IF;
5069 END IF;
5070 IF NVL(l_line_tbl(I).override_atp_date_code,'N') = 'Y'
5071 AND NOT l_set_overridden THEN
5072 l_set_overridden := TRUE;
5073 END IF;
5074 END LOOP;
5075
5076 IF l_log_error THEN
5077 FND_MESSAGE.SET_NAME('ONT','OE_SCH_OVER_ATP_SET_NO_MATCH');
5078 OE_MSG_PUB.Add;
5079 x_return_status := FND_API.G_RET_STS_ERROR;
5080 goto END_OF_PROCESS;
5081 END IF;
5082 END IF; -- code level.
5083
5084 -- End 2716220 -
5085
5086 -- Bypass if required.
5087
5088 IF nvl(p_request_rec.param12,'N') = 'N' THEN
5089 l_set_rec := OE_ORDER_CACHE.Load_Set(l_set_id); --3878494
5090
5091 FOR I IN 1..l_line_tbl.count LOOP
5092
5093 IF l_line_tbl(I).schedule_status_code IS NULL OR
5094 (l_line_tbl(I).item_type_code <> OE_GLOBALS.G_ITEM_STANDARD AND
5095 nvl(l_line_tbl(I).model_remnant_flag,'N') = 'N') THEN
5096
5097 IF l_debug_level > 0 THEN
5098 oe_debug_pub.add( 'UNABLE TO BYPASS' , 2 ) ;
5099 END IF;
5100 l_can_bypass := FALSE;
5101 EXIT;
5102
5103 END IF;
5104 -- following code commented for bug 3878494
5105 /*
5106 IF OE_SCHEDULE_UTIL.Set_Attr_Matched
5107 (p_set_ship_from_org_id => l_line_tbl(1).ship_from_org_id ,
5108 p_line_ship_from_org_id => l_line_tbl(I).ship_from_org_id,
5109 p_set_ship_to_org_id => l_line_tbl(1).ship_to_org_id ,
5110 p_line_ship_to_org_id => l_line_tbl(I).ship_to_org_id ,
5111 p_set_schedule_ship_date => l_line_tbl(1).schedule_ship_date ,
5112 p_line_schedule_ship_date => l_line_tbl(I).schedule_ship_date,
5113 p_set_arrival_date => l_line_tbl(1).schedule_arrival_date,
5114 p_line_arrival_date => l_line_tbl(I).schedule_arrival_date,
5115 p_set_shipping_method_code => l_line_tbl(1).shipping_method_code,
5116 p_line_shipping_method_code => l_line_tbl(I).shipping_method_code,
5117 p_set_type => p_request_rec.param8) THEN
5118 */
5119
5120 -- Bug 6363297 starts
5121 -- Modified below logic for
5122 -- 1) If l_set_rec is blank it means all the lines are being added to a new Set.
5123 -- If all the Lines are already Scheduled and have the same attributes then no need to call MRP again.
5124 -- We can directly put the lines into the same Set.
5125 -- 2) If l_set_rec has values, it means lines are being added to an existing set or scheduling attributes
5126 -- of the lines that are part of the set are being changed.
5127 -- In that case compare the line attributes with that of Set attributes. If any change is found call MRP.
5128 IF l_set_rec.schedule_ship_date IS NULL THEN
5129 IF l_debug_level > 0 THEN
5130 oe_debug_pub.add('New Set is being created, comparing scheduling attributes with first line', 5);
5131 END IF;
5132
5133 IF OE_SCHEDULE_UTIL.Set_Attr_Matched
5134 (p_set_ship_from_org_id => l_line_tbl(1).ship_from_org_id ,
5135 p_line_ship_from_org_id => l_line_tbl(I).ship_from_org_id,
5136 p_set_ship_to_org_id => l_line_tbl(1).ship_to_org_id ,
5137 p_line_ship_to_org_id => l_line_tbl(I).ship_to_org_id ,
5138 p_set_schedule_ship_date => l_line_tbl(1).schedule_ship_date ,
5139 p_line_schedule_ship_date => l_line_tbl(I).schedule_ship_date,
5140 p_set_arrival_date => l_line_tbl(1).schedule_arrival_date,
5141 p_line_arrival_date => l_line_tbl(I).schedule_arrival_date,
5142 p_set_shipping_method_code => l_line_tbl(1).shipping_method_code,
5143 p_line_shipping_method_code => l_line_tbl(I).shipping_method_code,
5144 p_set_type => p_request_rec.param8)
5145 THEN
5146 l_can_bypass := TRUE;
5147 ELSE
5148 l_can_bypass := FALSE;
5149 EXIT;
5150 END IF;
5151 ELSE
5152 IF l_debug_level > 0 THEN
5153 oe_debug_pub.add('Set already exists, comparing scheduling attributes with set', 5);
5154 END IF;
5155
5156 -- following code added for bug 3878494
5157 IF OE_SCHEDULE_UTIL.Set_Attr_Matched
5158 (p_set_ship_from_org_id => l_set_rec.ship_from_org_id ,
5159 p_line_ship_from_org_id => l_line_tbl(I).ship_from_org_id,
5160 p_set_ship_to_org_id => l_set_rec.ship_to_org_id ,
5161 p_line_ship_to_org_id => l_line_tbl(I).ship_to_org_id ,
5162 p_set_schedule_ship_date => l_set_rec.schedule_ship_date ,
5163 p_line_schedule_ship_date => l_line_tbl(I).schedule_ship_date,
5164 p_set_arrival_date => l_set_rec.schedule_arrival_date,
5165 p_line_arrival_date => l_line_tbl(I).schedule_arrival_date,
5166 p_set_shipping_method_code => l_set_rec.shipping_method_code,
5167 p_line_shipping_method_code => l_line_tbl(I).shipping_method_code,
5168 p_set_type => p_request_rec.param8) THEN
5169 l_can_bypass := TRUE;
5170
5171 ELSE
5172 IF l_debug_level > 0 THEN
5173 oe_debug_pub.add( 'LINES DID NOT MATCH' , 2 ) ;
5174 END IF;
5175 l_can_bypass := FALSE;
5176 EXIT;
5177 END IF;
5178 END IF;
5179 --Bug 6363297 ends
5180 END LOOP;
5181
5182
5183 IF l_can_bypass THEN
5184
5185 IF l_debug_level > 0 THEN
5186 oe_debug_pub.add( 'ALL LINES MATCH WITH SET DATES , BYPASS MRP CALL' , 2 ) ;
5187 END IF;
5188 GOTO BYPASS_PROCESS;
5189
5190 END IF;
5191
5192 END IF; -- param12.
5193
5194 -- End of bypass
5195
5196 l_old_line_tbl := l_line_tbl;
5197
5198 FOR I IN 1..l_old_line_tbl.count LOOP
5199
5200 IF l_old_line_tbl(1).schedule_status_code is null THEN
5201
5202 l_old_line_tbl(I).schedule_ship_date := null;
5203 l_old_line_tbl(I).schedule_arrival_date := null;
5204 -- l_old_line_tbl(I).ship_set_id := null;
5205 -- l_old_line_tbl(I).arrival_set_id := null;
5206
5207 ELSE
5208 IF l_old_line_tbl(I).line_id = l_line_id THEN
5209 /* l_old_line_tbl(I).ship_set_id :=
5210 l_old_ship_set_id;
5211 l_old_line_tbl(I).arrival_set_id :=
5212 l_old_arrival_set_id;
5213 */
5214 IF l_old_schedule_ship_date is not null THEN
5215 l_old_line_tbl(I).schedule_ship_date :=
5216 l_old_schedule_ship_date;
5217 END IF;
5218 IF l_old_schedule_arrival_date is not null THEN
5219 l_old_line_tbl(I).schedule_arrival_date :=
5220 l_old_schedule_arrival_date;
5221 END IF;
5222
5223 IF l_old_ship_from_org_id is not null THEN
5224
5225 l_old_line_tbl(I).ship_from_org_id :=
5226 l_old_ship_from_org_id;
5227 END IF;
5228 END IF;
5229 END IF;
5230
5231
5232 -- BUG 1282873 (Override Atp)
5233 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509'
5234 AND nvl(l_line_tbl(I).override_atp_date_code,'N') = 'N' THEN
5235
5236 IF (l_line_tbl(I).ato_line_id is not null AND
5237 NOT (l_line_tbl(I).ato_line_id = l_line_tbl(I).line_id AND
5238 l_line_tbl(I).item_type_code IN (OE_GLOBALS.G_ITEM_STANDARD,
5239 OE_GLOBALS.G_ITEM_OPTION)))
5240 THEN
5241
5242 BEGIN
5243
5244 Select override_atp_date_code
5245 Into l_line_tbl(I).override_atp_date_code
5246 From oe_order_lines_all
5247 Where header_id = l_line_tbl(I).header_id
5248 And ato_line_id = l_line_tbl(I).ato_line_id
5249 And override_atp_date_code = 'Y'
5250 And rownum < 2;
5251
5252 IF l_debug_level > 0 THEN
5253 oe_debug_pub.add( 'ato override flag :' || l_line_tbl ( I ) .override_atp_date_code , 3 ) ;
5254 END IF;
5255 EXCEPTION
5256 WHEN OTHERS THEN
5257 Null;
5258
5259 END;
5260 IF l_debug_level > 0 THEN
5261 oe_debug_pub.add( 'override_atp for ato :' || L_LINE_TBL ( I ) .OVERRIDE_ATP_DATE_CODE || L_LINE_TBL ( I ) .LINE_ID , 3 ) ;
5262 END IF;
5263 ELSIF l_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
5264
5265
5266 BEGIN
5267
5268
5269 Select override_atp_date_code
5270 Into l_line_tbl(I).override_atp_date_code
5271 From oe_order_lines_all
5272 Where header_id = l_line_tbl(I).header_id
5273 And line_id = l_line_tbl(I).link_to_line_id;
5274
5275 IF l_debug_level > 0 THEN
5276 oe_debug_pub.add( 'INC OVERRIDE FLAG :' || L_LINE_TBL ( I ) .OVERRIDE_ATP_DATE_CODE , 3 ) ;
5277 END IF;
5278 EXCEPTION
5279 WHEN OTHERS THEN
5280 Null;
5281
5282 END;
5283 IF l_debug_level > 0 THEN
5284 oe_debug_pub.add( 'override_atp for inc :' || L_LINE_TBL ( I ) .OVERRIDE_ATP_DATE_CODE || L_LINE_TBL ( I ) .LINE_ID , 3 ) ;
5285 END IF;
5286 END IF; -- Ato/included.
5287 END IF; -- Check for pack I
5288 -- END 1282873
5289 END LOOP;
5290 IF l_entity_type = OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET THEN
5291
5292 l_set_rec := OE_ORDER_CACHE.Load_Set(l_set_id);
5293 l_set_name := l_set_rec.set_name;
5294
5295 FOR I IN 1..l_line_tbl.count LOOP
5296
5297 l_line_tbl(I).ship_set := l_set_name;
5298 l_line_tbl(I).schedule_action_code := l_action;
5299
5300 /* Added for Bug 6250075 */
5301 l_line_tbl(I).change_reason := 'SYSTEM';
5302 l_line_tbl(I).change_comments := 'Delayed Request , Scheduling';
5303 /* End of Bug 6250075 */
5304
5305 IF fnd_profile.value('ONT_SHIP_METHOD_FOR_SHIP_SET') = 'Y' THEN
5306 oe_debug_pub.add('ONT_SHIP_METHOD_FOR_SHIP_SET: ' || p_request_rec.param11,2);
5307 l_line_tbl(I).shipping_method_code := p_request_rec.param11;
5308 END IF;
5309 -- l_line_tbl(I).shipping_method_code := p_request_rec.param11;
5310 /* Commented the above line to fix the bug 2916814 */
5311 /* -- 2716220
5312 IF l_line_tbl(I).override_atp_date_code = 'Y' AND
5313 l_line_tbl(I).schedule_status_code is NULL THEN
5314 -- Donot cascade the scheduling attributes since it is overridden line .
5315 null;
5316 ELSE
5317 */
5318 IF l_set_ship_to_org_id is not null THEN
5319 l_line_tbl(I).ship_to_org_id := l_set_ship_to_org_id;
5320 ELSIF (l_ship_to_org_id is not null) THEN
5321 l_line_tbl(I).ship_to_org_id := l_ship_to_org_id;
5322 END IF;
5323
5324 IF l_set_ship_from_org_id is not null THEN
5325 l_line_tbl(I).ship_from_org_id :=
5326 l_set_ship_from_org_id;
5327 ELSIF (l_ship_from_org_id is not null) THEN
5328 l_line_tbl(I).ship_from_org_id :=
5329 l_ship_from_org_id;
5330 END IF;
5331
5332 IF l_line_tbl(I).ship_from_org_id IS NOT NULL THEN
5333
5334 l_line_tbl(I).re_source_flag := 'N';
5335 END IF;
5336
5337 IF l_debug_level > 0 THEN
5338 oe_debug_pub.add( 'REQUEST DATE :' || L_REQUEST_DATE , 1 ) ;
5339 END IF;
5340
5341 -- Start 2787962
5342 IF l_line_tbl(I).top_model_line_id is not null
5343 AND oe_schedule_util.OE_sch_Attrb_Tbl.EXISTS
5344 (l_line_tbl(I).top_model_line_id )
5345 AND oe_schedule_util.OE_sch_Attrb_Tbl
5346 (l_line_tbl(I).top_model_line_id).date_attribute1 is not null
5347 THEN
5348 l_line_tbl(I).request_date :=
5349 oe_schedule_util.OE_sch_Attrb_Tbl
5350 (l_line_tbl(I).top_model_line_id).date_attribute1;
5351 ELSIF l_line_tbl(I).ato_line_id is not null
5352 AND NOT(l_line_tbl(I).ato_line_id = l_line_tbl(I).line_id
5353 AND l_line_tbl(I).item_type_code IN (OE_GLOBALS.G_ITEM_STANDARD,
5354 OE_GLOBALS.G_ITEM_OPTION))
5355 AND oe_schedule_util.OE_sch_Attrb_Tbl.EXISTS
5356 (l_line_tbl(I).ato_line_id )
5357 AND oe_schedule_util.OE_sch_Attrb_Tbl (l_line_tbl(I).ato_line_id)
5358 .date_attribute1 is not null
5359 THEN
5360 l_line_tbl(I).request_date :=
5361 oe_schedule_util.OE_sch_Attrb_Tbl
5362 (l_line_tbl(I).ato_line_id).date_attribute1;
5363 ELSIF l_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
5364 AND oe_schedule_util.OE_sch_Attrb_Tbl.EXISTS
5365 (l_line_tbl(I).link_to_line_id )
5366 AND oe_schedule_util.OE_sch_Attrb_Tbl
5367 (l_line_tbl(I).link_to_line_id).date_attribute1 is not null
5368 THEN
5369 l_line_tbl(I).request_date :=
5370 oe_schedule_util.OE_sch_Attrb_Tbl
5371 (l_line_tbl(I).link_to_line_id).date_attribute1;
5372
5373 END IF;
5374 -- End 2787962
5375
5376 IF (l_request_date is not null
5377 AND NOT OE_GLOBALS.Equal( l_request_date,
5378 l_old_request_date)) THEN
5379
5380 IF NOT l_set_overridden THEN
5381 --4483035
5382 --l_line_tbl(I).schedule_ship_date := l_request_date;
5383 --4929511
5384 IF l_line_tbl(I).schedule_status_code is NULL
5385 AND l_line_tbl(I).schedule_ship_date IS NULL THEN
5386 l_line_tbl(I).schedule_ship_date := l_line_tbl(I).request_date;
5387 END IF;
5388 END IF;
5389
5390 -- l_line_tbl(I).request_date := l_request_date; 2787962
5391
5392 END IF;
5393
5394 IF l_set_schedule_ship_date is not null THEN
5395 l_line_tbl(I).schedule_ship_date :=
5396 l_set_schedule_ship_date;
5397 -- 4929511 :Commented
5398 /*
5399 ELSIF (l_schedule_ship_date is not null
5400 AND NOT OE_GLOBALS.Equal( l_schedule_ship_date,
5401 l_old_schedule_ship_date)) THEN
5402 l_line_tbl(I).schedule_ship_date :=
5403 l_schedule_ship_date;
5404 */
5405 END IF;
5406
5407 IF l_line_tbl(I).schedule_ship_date is null THEN
5408 l_line_tbl(I).schedule_ship_date := l_line_tbl(I).request_date;
5409 END IF;
5410
5411 IF l_debug_level > 0 THEN
5412 oe_debug_pub.add( 'request ship date :' || L_LINE_TBL ( I ) .SCHEDULE_SHIP_DATE , 1 ) ;
5413 END IF;
5414
5415
5416 IF l_set_schedule_arrival_date is not null THEN
5417 l_line_tbl(I).schedule_arrival_date :=
5418 l_set_schedule_arrival_date;
5419 -- 4929511 :Commented
5420 /*
5421 ELSIF (l_schedule_arrival_date is not null
5422 AND NOT OE_GLOBALS.Equal( l_schedule_arrival_date,
5423 l_old_schedule_arrival_date)) THEN
5424 l_line_tbl(I).schedule_arrival_date :=
5425 l_schedule_arrival_date;
5426 */
5427 END IF;
5428 -- END IF; -- 2716220
5429
5430 l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
5431 IF l_debug_level > 0 THEN
5432 oe_debug_pub.add('request date :' || l_line_tbl(i).request_date , 1 ) ;
5433 oe_debug_pub.add('schedule_ship_date :' || l_line_tbl(I).schedule_ship_date , 1 ) ;
5434 oe_debug_pub.add('schedule_arrival_date :' || l_line_tbl(I).schedule_arrival_date , 1 ) ;
5435 oe_debug_pub.add('ship_from_org_id :' || l_line_tbl(I).ship_from_org_id , 1 ) ;
5436 END IF;
5437 END LOOP;
5438 END IF; -- Ship Set
5439
5440 IF l_entity_type =
5441 OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET THEN
5442
5443 l_set_rec := OE_ORDER_CACHE.Load_Set(l_set_id);
5444 l_set_name := l_set_rec.set_name;
5445
5446 /* Added for Bug 6250075 */
5447 /*
5448 Bug 6309823
5449 Moved this piece of code added through bug 6250075 into the below FOR loop
5450 l_line_tbl(I).change_reason := 'SYSTEM';
5451 l_line_tbl(I).change_comments := 'Delayed Request , Scheduling';
5452 */
5453 /* End of Bug 6250075 */
5454
5455 FOR I IN 1..l_line_tbl.count LOOP
5456
5457 l_line_tbl(I).arrival_set := l_set_name;
5458 l_line_tbl(I).schedule_action_code := l_action;
5459
5460 --Bug 6309823
5461 l_line_tbl(I).change_reason := 'SYSTEM';
5462 l_line_tbl(I).change_comments := 'Delayed Request , Scheduling';
5463
5464 -- 2716220
5465 /* IF l_line_tbl(I).override_atp_date_code = 'Y' AND
5466 l_line_tbl(I).schedule_status_code is NULL THEN
5467 -- Donot cascade the scheduling attributes since it is overridden line .
5468 null;
5469 ELSE
5470 */
5471
5472 IF l_set_ship_to_org_id is not null THEN
5473 l_line_tbl(I).ship_to_org_id := l_set_ship_to_org_id;
5474 ELSIF l_ship_to_org_id is not null THEN
5475 l_line_tbl(I).ship_to_org_id := l_ship_to_org_id;
5476 END IF;
5477
5478 -- Start 2787962
5479 IF l_line_tbl(I).top_model_line_id is not null
5480 AND oe_schedule_util.OE_sch_Attrb_Tbl.EXISTS
5481 (l_line_tbl(I).top_model_line_id )
5482 AND oe_schedule_util.OE_sch_Attrb_Tbl
5483 (l_line_tbl(I).top_model_line_id).date_attribute1 is not null
5484 THEN
5485 l_line_tbl(I).request_date :=
5486 oe_schedule_util.OE_sch_Attrb_Tbl
5487 (l_line_tbl(I).top_model_line_id).date_attribute1;
5488 ELSIF l_line_tbl(I).ato_line_id is not null
5489 AND NOT(l_line_tbl(I).ato_line_id = l_line_tbl(I).line_id
5490 AND l_line_tbl(I).item_type_code IN (OE_GLOBALS.G_ITEM_STANDARD,
5491 OE_GLOBALS.G_ITEM_OPTION))
5492 AND oe_schedule_util.OE_sch_Attrb_Tbl.EXISTS
5493 (l_line_tbl(I).ato_line_id )
5494 AND oe_schedule_util.OE_sch_Attrb_Tbl (l_line_tbl(I).ato_line_id)
5495 .date_attribute1 is not null
5496 THEN
5497 l_line_tbl(I).request_date :=
5498 oe_schedule_util.OE_sch_Attrb_Tbl
5499 (l_line_tbl(I).ato_line_id).date_attribute1;
5500 ELSIF l_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
5501 AND oe_schedule_util.OE_sch_Attrb_Tbl.EXISTS
5502 (l_line_tbl(I).link_to_line_id )
5503 AND oe_schedule_util.OE_sch_Attrb_Tbl
5504 (l_line_tbl(I).link_to_line_id).date_attribute1 is not null
5505 THEN
5506 l_line_tbl(I).request_date :=
5507 oe_schedule_util.OE_sch_Attrb_Tbl
5508 (l_line_tbl(I).link_to_line_id).date_attribute1;
5509
5510 END IF;
5511 -- End 2787962
5512
5513 -- Start 2391781
5514 IF NVL(l_line_tbl(I).Ship_model_complete_flag,'N') = 'Y'
5515 AND oe_schedule_util.OE_sch_Attrb_Tbl.EXISTS
5516 (l_line_tbl(I).top_model_line_id )
5517 AND oe_schedule_util.OE_sch_Attrb_Tbl
5518 (l_line_tbl(I).top_model_line_id).attribute1 is not null
5519 THEN
5520 l_line_tbl(I).Ship_from_org_id :=
5521 oe_schedule_util.OE_sch_Attrb_Tbl
5522 (l_line_tbl(I).top_model_line_id).attribute1;
5523 ELSIF l_line_tbl(I).ato_line_id is not null
5524 AND NOT(l_line_tbl(I).ato_line_id = l_line_tbl(I).line_id
5525 AND l_line_tbl(I).item_type_code IN (OE_GLOBALS.G_ITEM_STANDARD,
5526 OE_GLOBALS.G_ITEM_OPTION))
5527 AND oe_schedule_util.OE_sch_Attrb_Tbl.EXISTS
5528 (l_line_tbl(I).ato_line_id )
5529 AND oe_schedule_util.OE_sch_Attrb_Tbl (l_line_tbl(I).ato_line_id)
5530 .attribute1 is not null
5531 THEN
5532 l_line_tbl(I).Ship_from_org_id :=
5533 oe_schedule_util.OE_sch_Attrb_Tbl
5534 (l_line_tbl(I).ato_line_id).attribute1;
5535 END IF;
5536
5537 -- 2391781
5538
5539 IF (l_request_date is not null
5540 AND NOT OE_GLOBALS.Equal( l_request_date,
5541 l_old_request_date)) THEN
5542 IF NOT l_set_overridden THEN
5543 --4483035
5544 --l_line_tbl(I).schedule_arrival_date := l_request_date;
5545 --4929511
5546 IF l_line_tbl(I).schedule_status_code is NULL
5547 AND l_line_tbl(I).schedule_arrival_date IS NULL THEN
5548 l_line_tbl(I).schedule_arrival_date := l_line_tbl(I).request_date;
5549 END IF;
5550 END IF;
5551 -- l_line_tbl(I).request_date := l_request_date; 2787962
5552 END IF;
5553
5554 IF l_set_schedule_arrival_date is not null THEN
5555 l_line_tbl(I).schedule_arrival_date :=
5556 l_set_schedule_arrival_date;
5557 --4929511 : Commented
5558 /*
5559 ELSIF (l_schedule_arrival_date is not null
5560 AND NOT OE_GLOBALS.Equal( l_schedule_arrival_date,
5561 l_old_schedule_arrival_date)) THEN
5562 l_line_tbl(I).schedule_arrival_date := l_schedule_arrival_date;
5563 */
5564 END IF;
5565
5566 IF l_line_tbl(I).schedule_arrival_date is null THEN
5567 l_line_tbl(I).schedule_arrival_date :=
5568 l_line_tbl(I).request_date;
5569 END IF;
5570 IF l_set_schedule_ship_date is not null THEN -- 3281742
5571 l_line_tbl(I).schedule_ship_date :=
5572 l_set_schedule_ship_date;
5573 --4929511 : Commented
5574 /*
5575 ELSIF (l_schedule_ship_date is not null
5576 AND NOT OE_GLOBALS.Equal( l_schedule_ship_date,
5577 l_old_schedule_ship_date)) THEN
5578 l_line_tbl(I).schedule_ship_date :=
5579 l_schedule_ship_date;
5580 */
5581 END IF;
5582 -- END IF; -- 2716220
5583
5584 IF l_debug_level > 0 THEN
5585 oe_debug_pub.add( 'REQUEST ARRIVAL DATE :' || L_LINE_TBL ( I ) .SCHEDULE_ARRIVAL_DATE , 1 ) ;
5586 END IF;
5587
5588
5589 l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
5590
5591 END LOOP;
5592 END IF;
5593
5594 -- Start 2391781
5595 --- Deleteing delayed request of type cascade_warehous
5596 l_index := oe_schedule_util.OE_sch_Attrb_Tbl.FIRST;
5597 WHILE l_index is not null
5598 LOOP
5599 IF oe_schedule_util.OE_sch_Attrb_Tbl(l_index).set_id =l_set_id
5600 THEN
5601 oe_schedule_util.OE_sch_Attrb_Tbl.delete(l_index);
5602 END IF;
5603 l_index := oe_schedule_util.OE_sch_Attrb_Tbl.NEXT(l_index);
5604 END LOOP;
5605
5606 -- End 2391781
5607
5608 IF OE_SCHEDULE_UTIL.OE_Override_Tbl.count > 0 THEN
5609 FOR I IN 1..l_line_tbl.count LOOP
5610
5611 IF OE_SCHEDULE_UTIL.OE_Override_Tbl.EXISTS
5612 (l_line_tbl(I).line_id) THEN
5613
5614 OE_SCHEDULE_UTIL.OE_Override_Tbl.delete
5615 (l_line_tbl(I).line_id);
5616
5617 END IF;
5618
5619 END LOOP;
5620 END IF;
5621 IF l_debug_level > 0 THEN
5622 oe_debug_pub.add( 'CALLING PROCESS_GROUP' , 1 ) ;
5623 END IF;
5624
5625 IF l_line_tbl.count > 0 THEN
5626
5627
5628 Validate_Group
5629 (p_x_line_tbl => l_line_tbl,
5630 p_sch_action => l_action,
5631 x_return_status => x_return_status);
5632
5633 END IF;
5634
5635 IF l_debug_level > 0 THEN
5636 oe_debug_pub.add( 'AFTER CALLING Validate Group ' || X_RETURN_STATUS
5637 || l_line_tbl.count || ':' || l_old_line_tbl.count , 1 ) ;
5638 END IF;
5639
5640 IF l_line_tbl.count <> l_old_line_tbl.count THEN
5641 x_return_status := FND_API.G_RET_STS_ERROR;
5642 RAISE FND_API.G_EXC_ERROR;
5643 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5645 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5646 RAISE FND_API.G_EXC_ERROR;
5647 END IF;
5648
5649 /*
5650 Oe_Config_Schedule_Pvt.Process_Group
5651 (p_x_line_tbl => l_line_tbl
5652 ,p_old_line_tbl => l_old_line_tbl
5653 ,p_caller => 'SET'
5654 ,p_sch_action => l_action
5655 ,x_return_status => x_return_status);
5656 */
5657 /* Commented the above code and added the following code to fix the bug 2740480 */
5658 IF l_push_logic = 'Y' THEN
5659
5660 oe_debug_pub.add('2740480: Push logic is set to Y ',2);
5661 Oe_Config_Schedule_Pvt.Process_Group
5662 (p_x_line_tbl => l_line_tbl
5663 ,p_old_line_tbl => l_old_line_tbl
5664 ,p_caller => 'SET'
5665 ,p_sch_action => l_action
5666 ,p_partial_set => TRUE
5667 ,x_return_status => x_return_status);
5668
5669 IF l_debug_level > 0 THEN
5670 oe_debug_pub.add('2740480: After call to Process Group :' || x_return_status ,1);
5671 END IF;
5672 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5674 END IF;
5675 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
5676
5677 IF l_debug_level > 0 THEN
5678 oe_debug_pub.add('2740480: Compare output with requested values',1);
5679 END IF;
5680
5681 FOR I IN 1..l_line_tbl.count LOOP
5682 oe_debug_pub.add('2740480: Entity type : '|| l_entity_type);
5683 oe_debug_pub.add('2740480: line schedule ship date : '|| l_line_tbl(I).Schedule_ship_date);
5684 oe_debug_pub.add('2740480: set schedule ship date : '|| l_set_rec.Schedule_ship_date );
5685 IF l_entity_type = OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET
5686 AND trunc(l_line_tbl(I).Schedule_ship_date) <> trunc(l_set_rec.Schedule_ship_date)
5687 THEN
5688 IF l_debug_level > 0 THEN
5689 oe_debug_pub.add('2740480: Not received correct values for ship set',1);
5690 END IF;
5691 l_date_changed := 'Y' ;
5692 EXIT;
5693 ELSIF l_entity_type = OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET
5694 AND trunc(l_line_tbl(I).Schedule_arrival_date) <> trunc(l_set_rec.Schedule_arrival_date)
5695 THEN
5696 IF l_debug_level > 0 THEN
5697 oe_debug_pub.add('2740480: Not received correct values for arrival set',1);
5698 END IF;
5699 l_date_changed := 'Y' ;
5700 EXIT;
5701 END IF;
5702
5703 END LOOP;
5704 IF l_date_changed = 'N' THEN
5705 GOTO BYPASS_PROCESS;
5706 END IF;
5707
5708 END IF; -- Success.
5709
5710 IF fnd_profile.value('ONT_AUTO_PUSH_GRP_DATE') = 'Y' THEN
5711
5712 IF l_debug_level > 0 THEN
5713 oe_debug_pub.add('2740480: Auto Push Group Date is Yes',2);
5714 END IF;
5715 goto Push;
5716 ELSE
5717
5718 IF l_debug_level > 0 THEN
5719 oe_debug_pub.add('Before setting message for group failure',2);
5720 END IF;
5721
5722 x_return_status := FND_API.G_RET_STS_ERROR;
5723 FND_MESSAGE.SET_NAME('ONT','OE_SCH_GROUP_MEMBER_FAILED');
5724 OE_MSG_PUB.Add;
5725 RAISE FND_API.G_EXC_ERROR;
5726
5727 END IF;
5728
5729 END IF; -- Push logic ends here.
5730
5731 <<PUSH>>
5732
5733 oe_debug_pub.add('2740480: Push logic is set to N ',2);
5734 Oe_Config_Schedule_Pvt.Process_Group
5735 (p_x_line_tbl => l_line_tbl
5736 ,p_old_line_tbl => l_old_line_tbl
5737 ,p_caller => 'SET'
5738 ,p_sch_action => l_action
5739 ,x_return_status => x_return_status);
5740
5741 /* End of code for the bug fix 2740480 */
5742
5743
5744 IF l_debug_level > 0 THEN
5745 oe_debug_pub.add( 'AFTER CALLING PROCESS_GROUP' || X_RETURN_STATUS , 1 ) ;
5746 END IF;
5747
5748 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5750 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5751 -- RAISE FND_API.G_EXC_ERROR;
5752
5753 -- Code added for bug 2812346
5754
5755 IF l_debug_level > 0 THEN
5756 oe_debug_pub.add('l_operation :' || l_operation,2);
5757 oe_debug_pub.add('l_set_rec.set_status :' || l_set_rec.set_status,2);
5758 END IF;
5759 IF l_operation = OE_GLOBALS.G_OPR_CREATE
5760 AND l_set_rec.set_status = 'T' THEN
5761 -- Could not schedule the line on the set date. Let's schedule
5762 -- the whole set to see if we get another date got the whole
5763 -- set.
5764
5765
5766 FOR I IN 1..l_line_tbl.count LOOP
5767
5768 -- IF l_line_tbl(I).schedule_status_code IS NULL --commented for bug3986288
5769 -- THEN
5770 -- uncommented for bug 4188166
5771 IF l_line_tbl(I).schedule_status_code IS NULL OR
5772 l_old_line_tbl(I).schedule_status_code IS NULL
5773 THEN
5774
5775 IF l_line_tbl(I).top_model_line_id is null
5776 OR l_line_tbl(I).top_model_line_id =
5777 l_line_tbl(I).line_id
5778 OR (l_line_tbl(I).top_model_line_id is not null
5779 AND l_line_tbl(I).top_model_line_id <> l_line_tbl(I).line_id
5780 AND Not_part_of_set(l_line_tbl(I).top_model_line_id))
5781 THEN
5782
5783 UPDATE OE_ORDER_LINES_ALL
5784 SET SCHEDULE_SHIP_DATE = Null,
5785 SCHEDULE_ARRIVAL_DATE = Null,
5786 SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
5787 SHIP_SET_ID = Null,
5788 ARRIVAL_SET_ID = Null,
5789 override_atp_date_code = Null
5790 WHERE line_id = l_line_tbl(I).line_id;
5791
5792 -- 4026758
5793 IF l_line_tbl(I).ship_set_id IS NOT NULL
5794 OR l_line_tbl(I).arrival_set_id IS NOT NULL THEN
5795 oe_schedule_util.Log_Delete_Set_Request
5796 (p_header_id => l_line_tbl(I).header_id,
5797 p_line_id => l_line_tbl(I).line_id,
5798 p_set_id => nvl(l_line_tbl(I).ship_set_id,l_line_tbl(I).arrival_set_id),
5799 x_return_status => x_return_status);
5800 IF l_debug_level > 0 THEN
5801 oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST ' || X_RETURN_STATUS , 1 ) ;
5802 END IF;
5803 END IF;
5804
5805 ELSE
5806
5807 IF l_debug_level > 0 THEN
5808 oe_debug_pub.add('Model is part of a set cannot save ' ,2);
5809 END IF;
5810
5811 RAISE FND_API.G_EXC_ERROR;
5812 END IF;
5813 END IF; -- bug 4188166
5814 -- END IF; --commented for bug3986288
5815
5816 END LOOP;
5817 IF l_debug_level > 0 THEN
5818 oe_debug_pub.add('It is a create operation ' ,2);
5819 END IF;
5820 fnd_message.set_name('ONT','OE_SCH_SET_INS_FAILED');
5821 OE_MSG_PUB.Add;
5822 x_return_status := FND_API.G_RET_STS_SUCCESS;
5823 IF l_debug_level > 0 THEN
5824 oe_debug_pub.add('x_return_status ' || x_return_status,2);
5825 END IF;
5826 GOTO END_OF_PROCESS;
5827 ELSE -- Create
5828 IF l_debug_level > 0 THEN
5829 oe_debug_pub.add('It is not a create operation ' ,2);
5830 END IF;
5831
5832 RAISE FND_API.G_EXC_ERROR;
5833 END IF; -- Create.
5834 END IF;
5835
5836 -- If scheduling set suceeded, then the result of scheduling
5837 -- have been updated to the database. Will query one of the lines
5838 -- of the set to see the change is set attributes so that we can
5839 -- update the set itself.
5840 /*
5841 l_old_line_rec := l_old_line_tbl(1);
5842 OE_Line_Util.Query_Row( p_line_id => l_old_line_rec.line_id,
5843 x_line_rec => l_line_rec);
5844
5845 -- Update the set attributes.
5846
5847 l_ship_from_org_id := l_line_rec.ship_from_org_id;
5848 l_ship_to_org_id := l_line_rec.ship_to_org_id;
5849 l_schedule_ship_date := l_line_rec.schedule_ship_date;
5850 l_schedule_arrival_date := l_line_rec.schedule_arrival_date;
5851 l_shipping_method_code := l_line_rec.shipping_method_code;
5852 */
5853
5854 <<BYPASS_PROCESS>>
5855 BEGIN
5856 /* Removed the shipping_method_code from the following select to fix the bug 2916814 */
5857 Select ship_from_org_id, ship_to_org_id, schedule_ship_date,
5858 schedule_arrival_date,Shipping_Method_Code,
5859 Freight_Carrier_Code,shipment_priority_code
5860 INTO l_ship_from_org_id,l_ship_to_org_id,l_schedule_ship_date,
5861 l_schedule_arrival_date,l_Shipping_Method_Code,
5862 l_Freight_Carrier_Code,l_shipment_priority_code
5863 From oe_order_lines_all
5864 Where line_id = l_line_tbl(1).line_id;
5865
5866 IF l_debug_level > 0 THEN
5867 oe_debug_pub.add( 'CALLING UPDATE SET' ) ;
5868 END IF;
5869
5870 OE_Set_Util.Update_Set
5871 (p_Set_Id => l_set_id,
5872 p_Ship_From_Org_Id => l_Ship_From_Org_Id,
5873 p_Ship_To_Org_Id => l_Ship_To_Org_Id,
5874 p_Schedule_Ship_Date => l_Schedule_Ship_Date,
5875 p_Schedule_Arrival_Date => l_Schedule_Arrival_Date,
5876 p_Freight_Carrier_Code => l_Freight_Carrier_Code,
5877 p_Shipping_Method_Code => l_Shipping_Method_Code,
5878 p_shipment_priority_code => l_shipment_priority_code,
5879 X_Return_Status => x_return_status,
5880 x_msg_count => l_msg_count,
5881 x_msg_data => l_msg_data
5882 );
5883
5884 IF l_debug_level > 0 THEN
5885 oe_debug_pub.add( 'AFTER CALLING UPDATE SET' ) ;
5886 END IF;
5887
5888 EXCEPTION
5889 WHEN OTHERS THEN
5890 Null;
5891 END;
5892 <<END_OF_PROCESS>>
5893 G_TOP_MODEL_LINE_ID := Null;
5894 G_PART_OF_SET := Null;
5895
5896 IF l_debug_level > 0 THEN
5897 oe_debug_pub.add('Exiting Schedule_Set',1);
5898 END IF;
5899 EXCEPTION
5900 WHEN FND_API.G_EXC_ERROR THEN
5901 --3543774 If new set show the error message
5902 IF p_request_rec.request_type = OE_GLOBALS.G_GROUP_SCHEDULE
5903 AND ((l_entity_type =
5904 OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET
5905 AND NOT OE_GLOBALS.equal(l_ship_set_id,l_old_ship_set_id))
5906 OR (l_entity_type =
5907 OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET
5908 AND NOT OE_GLOBALS.equal(l_arrival_set_id,l_old_arrival_set_id)))
5909 THEN
5910 fnd_message.set_name('ONT','OE_SCH_SET_INS_FAILED');
5911 OE_MSG_PUB.Add;
5912 END IF;
5913 G_TOP_MODEL_LINE_ID := Null;
5914 G_PART_OF_SET := Null;
5915 x_return_status := FND_API.G_RET_STS_ERROR;
5916
5917 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5918
5919 G_TOP_MODEL_LINE_ID := Null;
5920 G_PART_OF_SET := Null;
5921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5922
5923 WHEN OTHERS THEN
5924
5925 G_TOP_MODEL_LINE_ID := Null;
5926 G_PART_OF_SET := Null;
5927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5928
5929 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5930 THEN
5931 OE_MSG_PUB.Add_Exc_Msg
5932 ( G_PKG_NAME
5933 , 'Schedule_Set'
5934 );
5935 END IF;
5936 END Schedule_Set;
5937 /***************************************************
5938 Procedure Group_Schedule_sets has been written to take care
5939 of set_for_each_line project.
5940
5941 p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
5942 p_entity_id => nvl(p_line_rec.ship_set_id,p_line_rec.arrival_set_
5943 id),
5944 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
5945 p_requesting_entity_id => p_line_rec.line_id,
5946 p_request_type => OE_GLOBALS.G_GROUP_SET,
5947 p_param1 => l_set_type,
5948 p_param2 => p_line_rec.header_id,
5949 p_param3 => p_line_rec.line_id,
5950 p_param4 => p_line_rec.top_model_line_id,
5951 p_param5 => p_line_rec.ship_to_org_id, -- added for bug 4188166
5952 p_param6 => p_line_rec.ship_from_org_id, -- added for bug 4188166
5953 x_return_status => x_return_status);
5954
5955
5956 ****************************************************/
5957
5958 Procedure Group_Schedule_sets
5959 ( p_sch_set_tbl IN OE_ORDER_PUB.request_tbl_type
5960 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
5961 IS
5962 l_line_tbl OE_ORDER_PUB.line_Tbl_type;
5963 l_old_line_tbl OE_ORDER_PUB.line_Tbl_type;
5964 l_sch_line_tbl OE_ORDER_PUB.line_Tbl_type;
5965 l_count NUMBER := 0;
5966 l_set_rec OE_ORDER_CACHE.set_rec_type;
5967 l_msg_count NUMBER;
5968 l_msg_data VARCHAR2(2000);
5969 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5970 l_set_exists VARCHAR2(1);
5971
5972 --
5973 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5974 --
5975 BEGIN
5976
5977 x_return_status := FND_API.G_RET_STS_SUCCESS;
5978
5979 IF l_debug_level > 0 THEN
5980 oe_debug_pub.add( 'ENTERING Group_Schedule_sets' , 1 ) ;
5981 END IF;
5982
5983 FOR I in 1..p_sch_set_tbl.count LOOP
5984 l_set_exists := 'Y';
5985
5986 IF l_debug_level > 0 THEN
5987 oe_debug_pub.add( 'Processing Line' || P_SCH_SET_TBL(I).ENTITY_ID, 1);
5988 END IF;
5989
5990 l_return_status := FND_API.G_RET_STS_SUCCESS;
5991
5992 BEGIN
5993
5994 Select 'Y'
5995 Into l_set_exists
5996 From oe_order_lines_all
5997 Where header_id = p_sch_set_tbl(I).param2
5998 And (ship_set_id = p_sch_set_tbl(I).entity_id
5999 Or arrival_set_id = p_sch_set_tbl(I).entity_id)
6000 And open_flag = 'Y'
6001 And rownum = 1;
6002
6003 IF l_debug_level > 0 THEN
6004 oe_debug_pub.add( 'Lines exists in the set', 2);
6005 END IF;
6006
6007 EXCEPTION
6008 WHEN NO_DATA_FOUND THEN
6009
6010 l_set_exists := 'N';
6011 WHEN OTHERS THEN
6012 l_set_exists := 'Y';
6013 END;
6014
6015 IF l_debug_level > 0 THEN
6016 oe_debug_pub.add( 'Before quering the date ' || l_set_exists, 2);
6017 END IF;
6018 IF l_set_exists = 'N' THEN
6019 GOTO END_PROCESS;
6020 END IF;
6021 IF p_sch_set_tbl(I).param1 = OE_SCHEDULE_UTIL.OESCH_ENTITY_ARRIVAL_SET
6022 THEN
6023 Oe_Config_Schedule_Pvt.Query_Set_Lines
6024 (p_header_id => p_sch_set_tbl(I).param2,
6025 p_arrival_set_id => p_sch_set_tbl(I).entity_id,
6026 p_sch_action => 'SCHEDULE',
6027 x_line_tbl => l_line_tbl,
6028 x_return_status => l_return_status);
6029
6030
6031 ELSIF p_sch_set_tbl(I).param1 = OE_SCHEDULE_UTIL.OESCH_ENTITY_SHIP_SET
6032 THEN
6033
6034 Oe_Config_Schedule_Pvt.Query_Set_Lines
6035 (p_header_id => p_sch_set_tbl(I).param2,
6036 p_ship_set_id => p_sch_set_tbl(I).entity_id,
6037 p_sch_action => 'SCHEDULE',
6038 x_line_tbl => l_line_tbl,
6039 x_return_status => l_return_status);
6040
6041 END IF;
6042
6043 IF l_debug_level > 0 THEN
6044 oe_debug_pub.add(' L_return_status :' || l_return_status,1);
6045 END IF;
6046
6047 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6049 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
6050 IF l_debug_level > 0 THEN
6051 oe_debug_pub.add(' Goto End due to error',1);
6052 END IF;
6053 GOTO END_PROCESS;
6054 END IF;
6055
6056 FOR J IN 1..l_line_tbl.count LOOP
6057
6058 OE_SCHEDULE_UTIL.Validate_Line(p_line_rec => l_line_tbl(J),
6059 p_old_line_rec => l_line_tbl(J),
6060 p_sch_action => 'SCHEDULE' ,
6061 x_return_status => l_return_status);
6062
6063 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6064
6065 IF l_debug_level > 0 THEN
6066 oe_debug_pub.add(' Error in validation ',1);
6067 END IF;
6068 GOTO END_PROCESS;
6069 END IF;
6070
6071 END LOOP;
6072
6073 l_count := l_sch_line_tbl.count;
6074 FOR J IN 1..l_line_tbl.count LOOP
6075
6076 l_count := l_count + 1;
6077
6078 l_sch_line_tbl(l_count) := l_line_tbl(J);
6079 l_old_line_tbl(l_count) := l_line_tbl(J);
6080
6081 l_sch_line_tbl(l_count).schedule_action_code := 'SCHEDULE';
6082 l_sch_line_tbl(l_count).operation := 'UPDATE';
6083
6084 /* Added for Bug 6250075 */
6085 l_sch_line_tbl(l_count).change_reason := 'SYSTEM';
6086 l_sch_line_tbl(l_count).change_comments := 'Delayed Request , Scheduling';
6087 /* End of Bug 6250075 */
6088
6089 IF nvl(l_line_tbl(J).override_atp_date_code,'N') = 'N'
6090 AND (l_line_tbl(J).ato_line_id is not null AND
6091 NOT (l_line_tbl(J).ato_line_id = l_line_tbl(J).line_id AND
6092 l_line_tbl(J).item_type_code IN (OE_GLOBALS.G_ITEM_STANDARD,
6093 OE_GLOBALS.G_ITEM_OPTION)))
6094 THEN
6095
6096 BEGIN
6097
6098 Select override_atp_date_code,
6099 schedule_ship_date,
6100 schedule_arrival_date
6101 Into l_sch_line_tbl(l_count).override_atp_date_code,
6102 l_sch_line_tbl(l_count).schedule_ship_date,
6103 l_sch_line_tbl(l_count).schedule_arrival_date
6104 From oe_order_lines_all
6105 Where header_id = l_line_tbl(J).header_id
6106 And ato_line_id = l_line_tbl(J).ato_line_id
6107 And override_atp_date_code = 'Y'
6108 And rownum < 2;
6109
6110 EXCEPTION
6111 WHEN OTHERS THEN
6112 Null;
6113
6114 END;
6115 END IF;
6116
6117
6118 IF (l_line_tbl(J).arrival_set_id is not null) THEN
6119
6120 l_set_rec := OE_ORDER_CACHE.Load_Set
6121 ( l_line_tbl(J).arrival_set_id);
6122 l_sch_line_tbl(l_count).arrival_set := l_set_rec.set_name;
6123 -- 4188166
6124 IF p_sch_set_tbl(I).param5 IS NOT NULL THEN --5151954
6125 l_sch_line_tbl(l_count).ship_to_org_id := p_sch_set_tbl(I).param5;
6126 END IF;
6127
6128 ELSIF (l_line_tbl(J).ship_set_id is not null) THEN
6129
6130 l_set_rec := OE_ORDER_CACHE.Load_Set
6131 ( l_line_tbl(J).Ship_set_id);
6132 l_sch_line_tbl(l_count).ship_set := l_set_rec.set_name;
6133 -- 4188166
6134 IF p_sch_set_tbl(I).param5 IS NOT NULL THEN --5151954
6135 l_sch_line_tbl(l_count).ship_to_org_id := p_sch_set_tbl(I).param5;
6136 END IF;
6137
6138 IF p_sch_set_tbl(I).param6 IS NOT NULL THEN --5151954
6139 l_sch_line_tbl(l_count).ship_from_org_id := p_sch_set_tbl(I).param6;
6140 END IF;
6141 END IF;
6142
6143
6144 END LOOP; -- lsch_tbl
6145
6146 <<END_PROCESS>>
6147
6148 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6149
6150 IF l_debug_level > 0 THEN
6151 oe_debug_pub.add(' Error in ' || p_sch_set_tbl(I).param1,2);
6152 END IF;
6153
6154 IF p_sch_set_tbl(I).param4 is null
6155 THEN
6156 UPDATE OE_ORDER_LINES_ALL
6157 SET SCHEDULE_SHIP_DATE = Null,
6158 SCHEDULE_ARRIVAL_DATE = Null,
6159 SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
6160 SHIP_SET_ID = Null,
6161 ARRIVAL_SET_ID = Null,
6162 OVERRIDE_ATP_DATE_CODE = Null
6163 WHERE line_id = p_sch_set_tbl(I).param3;
6164 ELSE
6165
6166 UPDATE OE_ORDER_LINES_ALL
6167 SET SCHEDULE_SHIP_DATE = Null,
6168 SCHEDULE_ARRIVAL_DATE = Null,
6169 SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
6170 SHIP_SET_ID = Null,
6171 ARRIVAL_SET_ID = Null,
6172 OVERRIDE_ATP_DATE_CODE = Null
6173 WHERE top_model_line_id = p_sch_set_tbl(I).param4;
6174 END IF;
6175 -- 4026758
6176 IF l_sch_line_tbl(I).ship_set_id IS NOT NULL
6177 OR l_sch_line_tbl(I).arrival_set_id IS NOT NULL THEN
6178 oe_schedule_util.Log_Delete_Set_Request
6179 (p_header_id => l_sch_line_tbl(I).header_id,
6180 p_line_id => l_sch_line_tbl(I).line_id,
6181 p_set_id => nvl(l_sch_line_tbl(I).ship_set_id,l_sch_line_tbl(I).arrival_set_id),
6182 x_return_status => x_return_status);
6183 IF l_debug_level > 0 THEN
6184 oe_debug_pub.add( 'AFTER LOGGING DELETE SETS DELAYED REQUEST '
6185 || X_RETURN_STATUS , 1 ) ;
6186 END IF;
6187 END IF;
6188 END IF;
6189 IF l_debug_level > 0 THEN
6190 oe_debug_pub.add(' Process next set :' || l_sch_line_tbl.count,1);
6191 END IF;
6192
6193 END LOOP; -- Main loop.
6194
6195 IF l_debug_level > 0 THEN
6196 oe_debug_pub.add('Line Count :' || l_sch_line_tbl.count,1);
6197 END IF;
6198
6199 IF l_sch_line_tbl.count > 0 THEN
6200
6201 Oe_Config_Schedule_Pvt.Process_Group
6202 (p_x_line_tbl => l_sch_line_tbl
6203 ,p_old_line_tbl => l_old_line_tbl
6204 ,p_caller => 'SET'
6205 ,p_sch_action => OE_SCHEDULE_UTIL.OESCH_ACT_SCHEDULE
6206 ,p_partial => TRUE
6207 ,x_return_status => x_return_status);
6208
6209 IF l_debug_level > 0 THEN
6210 oe_debug_pub.add('After call to Process Group :' || x_return_status ,1);
6211 END IF;
6212 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6214 END IF;
6215 END IF;
6216
6217
6218 FOR I IN 1..l_sch_line_tbl.count LOOP
6219
6220 oe_debug_pub.add('line id :' || l_sch_line_tbl(I).line_id ,1);
6221 oe_debug_pub.add('Schedule status code :' || l_sch_line_tbl(I).schedule_status_code ,1);
6222
6223 -- 4188166
6224 IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
6225 l_sch_line_tbl(I).schedule_status_code IS NOT NULL
6226 THEN
6227
6228 /*
6229 IF l_sch_line_tbl(I).schedule_status_code IS NULL
6230 THEN
6231
6232 IF l_sch_line_tbl(I).top_model_line_id is NULL THEN
6233 UPDATE OE_ORDER_LINES_ALL
6234 SET SCHEDULE_SHIP_DATE = Null,
6235 SCHEDULE_ARRIVAL_DATE = Null,
6236 SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
6237 SHIP_SET_ID = Null,
6238 ARRIVAL_SET_ID = Null,
6239 OVERRIDE_ATP_DATE_CODE = Null
6240 WHERE line_id = l_sch_line_tbl(I).line_id;
6241 ELSE
6242 UPDATE OE_ORDER_LINES_ALL
6243 SET SCHEDULE_SHIP_DATE = Null,
6244 SCHEDULE_ARRIVAL_DATE = Null,
6245 SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
6246 SHIP_SET_ID = Null,
6247 ARRIVAL_SET_ID = Null,
6248 OVERRIDE_ATP_DATE_CODE = Null
6249 WHERE top_model_line_id = l_sch_line_tbl(I).top_model_line_id;
6250 END IF;
6251
6252 ELSIF l_sch_line_tbl(I).line_id = l_sch_line_tbl(I).top_model_line_id
6253 OR l_sch_line_tbl(I).top_model_line_id is NULL THEN
6254 */
6255
6256 IF l_sch_line_tbl(I).line_id = l_sch_line_tbl(I).top_model_line_id
6257 OR l_sch_line_tbl(I).top_model_line_id is NULL THEN
6258
6259 OE_Set_Util.Update_Set
6260 (p_Set_Id => Nvl(l_sch_line_tbl(I).arrival_Set_id,
6261 l_sch_line_tbl(I).ship_Set_id),
6262 p_Ship_From_Org_Id => l_sch_line_tbl(I).Ship_From_Org_Id,
6263 p_Ship_To_Org_Id => l_sch_line_tbl(I).Ship_To_Org_Id,
6264 p_Schedule_Ship_Date => l_sch_line_tbl(I).Schedule_Ship_Date,
6265 p_Schedule_Arrival_Date => l_sch_line_tbl(I).Schedule_Arrival_Date,
6266 p_Freight_Carrier_Code => l_sch_line_tbl(I).Freight_Carrier_Code,
6267 p_Shipping_Method_Code => l_sch_line_tbl(I).Shipping_Method_Code,
6268 p_shipment_priority_code => l_sch_line_tbl(I).shipment_priority_code,
6269 X_Return_Status => x_return_status,
6270 x_msg_count => l_msg_count,
6271 x_msg_data => l_msg_data
6272 );
6273
6274 END IF;
6275 ELSE -- Return status has error (4188166)
6276
6277 IF l_sch_line_tbl(I).top_model_line_id is null
6278 OR l_sch_line_tbl(I).top_model_line_id = l_sch_line_tbl(I).line_id
6279 OR (l_sch_line_tbl(I).top_model_line_id is not null
6280 AND l_sch_line_tbl(I).top_model_line_id <> l_sch_line_tbl(I).line_id
6281 AND Not_part_of_set(l_sch_line_tbl(I).top_model_line_id))
6282 THEN
6283
6284 UPDATE OE_ORDER_LINES_ALL
6285 SET SCHEDULE_SHIP_DATE = Null,
6286 SCHEDULE_ARRIVAL_DATE = Null,
6287 SHIP_FROM_ORG_ID = decode(re_source_flag,'N',ship_from_org_id,null),
6288 SHIP_SET_ID = Null,
6289 ARRIVAL_SET_ID = Null,
6290 override_atp_date_code = Null
6291 --Bug 5654321
6292 --Modified the record from l_line_tbl to l_sch_line_tbl
6293 --WHERE line_id = l_line_tbl(I).line_id;
6294 WHERE line_id = l_sch_line_tbl(I).line_id;
6295 END IF;
6296
6297 -- Begin 4026758
6298 IF l_sch_line_tbl(I).ship_set_id IS NOT NULL
6299 OR l_sch_line_tbl(I).arrival_set_id IS NOT NULL THEN
6300
6301 oe_schedule_util.Log_Delete_Set_Request
6302 (p_header_id => l_sch_line_tbl(I).header_id,
6303 p_line_id => l_sch_line_tbl(I).line_id,
6304 p_set_id => nvl(l_sch_line_tbl(I).ship_set_id,l_sch_line_tbl(I).arrival_set_id),
6305 x_return_status => l_return_status);
6306
6307 IF l_debug_level > 0 THEN
6308 oe_debug_pub.add('AFTER LOGGING DELETE SETS DELAYED REQUEST ' || L_RETURN_STATUS,1) ;
6309 END IF;
6310
6311 END IF;
6312 -- End 4026758
6313
6314 END IF;
6315 END LOOP;
6316
6317 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
6318 fnd_message.set_name('ONT','OE_SCH_SET_INS_FAILED');
6319 OE_MSG_PUB.Add;
6320 x_return_status := FND_API.G_RET_STS_SUCCESS;
6321 IF l_debug_level > 0 THEN
6322 oe_debug_pub.add('x_return_status ' || x_return_status,2);
6323 END IF;
6324 END IF;
6325 -- End 4188166
6326
6327 IF l_debug_level > 0 THEN
6328 oe_debug_pub.add('Exiting Group_Schedule_sets' || x_return_status,1);
6329 END IF;
6330
6331 EXCEPTION
6332
6333 WHEN FND_API.G_EXC_ERROR THEN
6334 x_return_status := FND_API.G_RET_STS_ERROR;
6335 IF l_debug_level > 0 THEN
6336 oe_debug_pub.add('No data from expected error',1);
6337 END IF;
6338 RAISE FND_API.G_EXC_ERROR;
6339
6340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6341
6342 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6343 THEN
6344 OE_MSG_PUB.Add_Exc_Msg
6345 ( G_PKG_NAME
6346 , 'Group_Schedule_sets'
6347 );
6348 END IF;
6349 IF l_debug_level > 0 THEN
6350 oe_debug_pub.add('No data from unexpected error',1);
6351 END IF;
6352
6353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6354
6355 WHEN OTHERS THEN
6356
6357
6358 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6359 THEN
6360 OE_MSG_PUB.Add_Exc_Msg
6361 ( G_PKG_NAME
6362 , 'Group_Schedule_sets'
6363 );
6364 END IF;
6365 IF l_debug_level > 0 THEN
6366 oe_debug_pub.add('No data from unexpected error',1);
6367 END IF;
6368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6369 END Group_Schedule_sets;
6370 END OE_GROUP_SCH_UTIL;
6371
[Home] [Help]