1 PACKAGE BODY OE_GRP_SCH_UTIL AS
2 /* $Header: OEXVGRPB.pls 120.3 2005/08/05 07:54:27 kmuruges noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_GRP_SCH_UTIL';
5
6 PROCEDURE Query_Lines
7 ( p_header_id IN NUMBER,
8 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type);
9
10 Procedure Validate_Group_Request
11 (p_group_req_rec IN OE_GRP_SCH_UTIL.Sch_Group_Rec_Type
12 ,x_return_status OUT NOCOPY VARCHAR2);
13
14
15 Procedure Validate_Warehouse
16 (p_line_tbl IN OE_ORDER_PUB.line_tbl_type
17 ,p_ship_from_org_id IN NUMBER
18 ,x_return_status OUT NOCOPY VARCHAR2);
19
20
21 /*--------------------------------------------------------------------
22 Procedure Name : Delink_Required
23 Description : ** Currently not used **
24 ---------------------------------------------------------------------*/
25 FUNCTION Delink_Required(p_line_rec IN OE_ORDER_PUB.line_rec_type)
26 RETURN BOOLEAN
27 IS
28 --
29 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
30 --
31 BEGIN
32 RETURN TRUE;
33 END Delink_Required;
34 /*--------------------------------------------------------------------
35 Procedure Name : Validate_Line
36 ---------------------------------------------------------------------*/
37 Procedure Validate_Line(p_line_rec IN OE_ORDER_PUB.Line_Rec_Type,
38 p_old_line_rec IN OE_ORDER_PUB.Line_Rec_Type,
39 x_return_status OUT NOCOPY VARCHAR2)
40
41 IS
42 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
43 l_msg_count NUMBER;
44 l_msg_data VARCHAR2(2000);
45 l_result Varchar2(30);
46 l_scheduling_level_code VARCHAR2(30) := NULL;
47 l_out_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
48 l_type_code VARCHAR2(30);
49 l_org_id NUMBER;
50 l_bill_seq_id NUMBER;
51 l_make_buy NUMBER;
52 --
53 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
54 --
55 BEGIN
56
57 IF l_debug_level > 0 THEN
58 oe_debug_pub.add( '..ENTERING OE_GRP_SCH_UTIL.VALIDATE_LINE' , 1 ) ;
59 END IF;
60
61 -- If the quantity on the line is missing or null and if
62 -- the user is trying to performing scheduling, it is an error
63
64 IF ((p_old_line_rec.ordered_quantity is null OR
65 p_old_line_rec.ordered_quantity = FND_API.G_MISS_NUM) AND
66 (p_line_rec.ordered_quantity is null OR
67 p_line_rec.ordered_quantity = FND_API.G_MISS_NUM)) THEN
68
69 FND_MESSAGE.SET_NAME('ONT','OE_SCH_MISSING_QUANTITY');
70 OE_MSG_PUB.Add;
71 l_return_status := FND_API.G_RET_STS_ERROR;
72 END IF;
73
74 -- If the quantity on the line is zero(which is different from
75 -- missing) and if the user is trying to performing scheduling,
76 -- it is an error
77
78 IF (((p_old_line_rec.ordered_quantity is null OR
79 p_old_line_rec.ordered_quantity = FND_API.G_MISS_NUM OR
80 p_old_line_rec.ordered_quantity = 0) AND
81 p_line_rec.ordered_quantity = 0) AND
82 (nvl(p_line_rec.cancelled_flag,'N') = 'N')) THEN
83
84 IF p_line_rec.schedule_action_code is not null THEN
85
86 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ZERO_QTY');
87 OE_MSG_PUB.Add;
88
89 IF l_debug_level > 0 THEN
90 oe_debug_pub.add( 'E2' , 1 ) ;
91 END IF;
92 l_return_status := FND_API.G_RET_STS_ERROR;
93 END IF;
94
95 END IF;
96
97 -- If the line is shipped, scheduling is not allowed.
98
99 IF (p_line_rec.shipped_quantity is not null) AND
100 (p_line_rec.shipped_quantity <> FND_API.G_MISS_NUM) THEN
101
102 IF p_line_rec.schedule_action_code is not null THEN
103
104 -- The line is cancelled. Cannot perform scheduling
105 -- on it.
106
107 FND_MESSAGE.SET_NAME('ONT','OE_SCH_LINE_SHIPPED');
108 OE_MSG_PUB.Add;
109
110 IF l_debug_level > 0 THEN
111 oe_debug_pub.add( 'OE_SCH_LINE_SHIPPED' , 1 ) ;
112 END IF;
113 l_return_status := FND_API.G_RET_STS_ERROR;
114
115 END IF;
116 END IF;
117
118 -- Check to see if the reserved quantity is changed and is more
119 -- than the ordered quantity. This should not be allowed.
120
121 IF NOT OE_GLOBALS.Equal(p_old_line_rec.reserved_quantity,
122 p_line_rec.reserved_quantity)
123 THEN
124 -- Reserved Quantity has changed
125 IF (p_line_rec.ordered_quantity < p_line_rec.reserved_quantity)
126 THEN
127
128 FND_MESSAGE.SET_NAME('ONT','OE_SCH_RES_MORE_ORD_QTY');
129 OE_MSG_PUB.Add;
130
131 IF l_debug_level > 0 THEN
132 oe_debug_pub.add( 'E4' , 1 ) ;
133 END IF;
134 l_return_status := FND_API.G_RET_STS_ERROR;
135 END IF;
136
137 -- after changing reserved qty, trying to unschedule or unreserve
138 -- dose not make sense.
139 IF (p_line_rec.schedule_action_code =
140 OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE OR
141 p_line_rec.schedule_action_code =
142 OE_ORDER_SCH_UTIL.OESCH_ACT_UNRESERVE) AND
143 (p_line_rec.reserved_quantity is not null) THEN
144
145 FND_MESSAGE.SET_NAME('ONT','OE_SCH_RES_QTY_CHG_NOT_ALLOWED');
146 OE_MSG_PUB.Add;
147 l_return_status := FND_API.G_RET_STS_ERROR;
148 IF l_debug_level > 0 THEN
149 oe_debug_pub.add( 'E5' , 1 ) ;
150 END IF;
151 END IF;
152 END IF;
153
154 -- Check to see if the ordered quantity and reserved quantity
155 -- both have changed and if the ordered quantity is less than
156 -- the reserved quantity. This should not be allowed.
157
158 IF NOT OE_GLOBALS.Equal(p_old_line_rec.ordered_quantity,
159 p_line_rec.ordered_quantity)
160 THEN
161 -- Ordered Quantity has changed
162 IF NOT OE_GLOBALS.Equal(p_old_line_rec.reserved_quantity,
163 p_line_rec.reserved_quantity)
164 THEN
165 IF (p_line_rec.ordered_quantity < p_line_rec.reserved_quantity)
166 THEN
167
168 FND_MESSAGE.SET_NAME('ONT','OE_SCH_RES_MORE_ORD_QTY');
169 OE_MSG_PUB.Add;
170
171 l_return_status := FND_API.G_RET_STS_ERROR;
172 IF l_debug_level > 0 THEN
173 oe_debug_pub.add( 'E6' , 1 ) ;
174 END IF;
175 END IF;
176 END IF;
177 END IF;
178
179 -- If the order quantity uom on the line is missing or null
180 -- and if the user is trying to performing scheduling,
181 -- it is an error
182
183 IF (p_line_rec.order_quantity_uom is null OR
184 p_line_rec.order_quantity_uom = FND_API.G_MISS_CHAR) THEN
185
186 FND_MESSAGE.SET_NAME('ONT','OE_SCH_MISSING_UOM');
187 OE_MSG_PUB.Add;
188
189 l_return_status := FND_API.G_RET_STS_ERROR;
190 IF l_debug_level > 0 THEN
191 oe_debug_pub.add( 'E7' , 1 ) ;
192 END IF;
193 END IF;
194
195 -- If the item on the line is missing or null and if the user
196 -- is trying to performing scheduling, it is an error
197
198 IF l_debug_level > 0 THEN
199 oe_debug_pub.add( 'CHECKING THE ITEM....' , 1 ) ;
200 END IF;
201
202 IF (p_line_rec.inventory_item_id is null OR
203 p_line_rec.inventory_item_id = FND_API.G_MISS_NUM) THEN
204
205 FND_MESSAGE.SET_NAME('ONT','OE_SCH_MISSING_ITEM');
206 OE_MSG_PUB.Add;
207
208 l_return_status := FND_API.G_RET_STS_ERROR;
209 END IF;
210
211 -- If the request_date on the line is missing or null and
212 -- if the user is trying to performing scheduling,
213 -- it is an error
214
215 IF l_debug_level > 0 THEN
216 oe_debug_pub.add( 'CHECKING THE REQUEST DATE....' , 1 ) ;
217 END IF;
218 IF (p_line_rec.request_date is null OR
219 p_line_rec.request_date = FND_API.G_MISS_DATE) THEN
220
221 FND_MESSAGE.SET_NAME('ONT','OE_SCH_MISSING_REQUEST_DATE');
222 OE_MSG_PUB.Add;
223 l_return_status := FND_API.G_RET_STS_ERROR;
224 END IF;
225
226 -- If the line belongs to a set, you cannot unschedule the line
227 IF l_debug_level > 0 THEN
228 oe_debug_pub.add( 'CHECKING FOR SET VALIDATIONS....' , 1 ) ;
229 END IF;
230 IF ((p_line_rec.ship_set_id is not null AND
231 p_line_rec.ship_set_id <> FND_API.G_MISS_NUM) AND
232 (p_line_rec.schedule_action_code =
233 OE_ORDER_SCH_UTIL.OESCH_ACT_UNDEMAND OR
234 p_line_rec.schedule_action_code =
235 OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE))
236 THEN
237
238 FND_MESSAGE.SET_NAME('ONT','OE_SCH_OE_ORDER_FAILED');
239 OE_MSG_PUB.Add;
240
241 l_return_status := FND_API.G_RET_STS_ERROR;
242 END IF;
243
244 IF l_debug_level > 0 THEN
245 oe_debug_pub.add( 'CHECKING FOR HOLDS....' , 1 ) ;
246 END IF;
247 --IF FND_PROFILE.VALUE('ONT_SCHEDULE_LINE_ON_HOLD') = 'N' AND
248 IF oe_sys_parameters.value ('ONT_SCHEDULE_LINE_ON_HOLD') = 'N' AND --moac
249 (p_line_rec.schedule_action_code =
250 OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE OR
251 p_line_rec.schedule_action_code =
252 OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE OR
253 (p_line_rec.schedule_status_code is not null AND
254 OE_ORDER_SCH_UTIL.Schedule_Attribute_Changed
255 (p_line_rec => p_line_rec,
256 p_old_line_rec => p_old_line_rec)) OR
257 (p_line_rec.schedule_status_code is not null AND
258 p_line_rec.ordered_quantity > p_old_line_rec.ordered_quantity))
259
260 THEN
261 -- Since the profile is set to NO, we should not schedule
262 -- the line if the line is on hold.
263
264 IF l_debug_level > 0 THEN
265 oe_debug_pub.add( 'CALLING CHECK HOLDS' , 1 ) ;
266 END IF;
267
268 OE_Holds_PUB.Check_Holds
269 ( p_api_version => 1.0
270 , p_init_msg_list => FND_API.G_FALSE
271 , p_commit => FND_API.G_FALSE
272 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
273 , x_return_status => l_out_return_status
274 , x_msg_count => l_msg_count
275 , x_msg_data => l_msg_data
276 , p_line_id => p_line_rec.line_id
277 , p_hold_id => NULL
278 , p_entity_code => NULL
279 , p_entity_id => NULL
280 , x_result_out => l_result
281 );
282
283 IF l_debug_level > 0 THEN
284 oe_debug_pub.add( 'AFTER CALLING CHECK HOLDS: ' || L_OUT_RETURN_STATUS , 1 ) ;
285 END IF;
286
287 IF (l_out_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
288 IF l_out_return_status = FND_API.G_RET_STS_ERROR THEN
289 RAISE FND_API.G_EXC_ERROR;
290 ELSE
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 END IF;
293 END IF;
294
295 IF (l_result = FND_API.G_TRUE) THEN
296 FND_MESSAGE.SET_NAME('ONT','OE_SCH_LINE_ON_HOLD');
297 OE_MSG_PUB.Add;
298 l_return_status := FND_API.G_RET_STS_ERROR;
299 END IF;
300
301 END IF;
302
303 -- Check to see what scheduling level is allowed to be performed
304 -- on this line. If the action requested is not allowed for the
305 -- scheduling action, error out.
306
307 IF l_debug_level > 0 THEN
308 oe_debug_pub.add( 'CHECKING SCHEDULING LEVEL...' , 1 ) ;
309 END IF;
310 l_scheduling_level_code := OE_ORDER_SCH_UTIL.Get_Scheduling_Level
311 (p_line_rec.header_id,
312 p_line_rec.line_type_id);
313
314 IF l_debug_level > 0 THEN
315 oe_debug_pub.add( 'L_SCHEDULING_LEVEL_CODE : ' || L_SCHEDULING_LEVEL_CODE , 1 ) ;
316 END IF;
317
318 IF l_scheduling_level_code is not null THEN
319 IF l_scheduling_level_code = OE_ORDER_SCH_UTIL.SCH_LEVEL_ONE THEN
320 IF p_line_rec.schedule_action_code =
321 OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE OR
322 p_line_rec.schedule_action_code =
323 OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE OR
324 (p_line_rec.schedule_status_code is null AND
325 (p_line_rec.schedule_ship_date is NOT NULL OR
326 p_line_rec.schedule_arrival_date is NOT NULL))
327 THEN
328
329 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ACTION_NOT_ALLOWED');
330 FND_MESSAGE.SET_TOKEN('ACTION',
331 nvl(p_line_rec.schedule_action_code,
332 OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE));
333 FND_MESSAGE.SET_TOKEN('ORDER_TYPE',
334 nvl(oe_order_sch_util.sch_cached_line_type,
335 oe_order_sch_util.sch_cached_order_type));
336 OE_MSG_PUB.Add;
337 l_return_status := FND_API.G_RET_STS_ERROR;
338 END IF;
339 ELSIF l_scheduling_level_code = OE_ORDER_SCH_UTIL.SCH_LEVEL_TWO THEN
340 -- Changes for Bug-2497354
341 -- Changed the IF condition for bug 2681047
342 IF p_line_rec.reserved_quantity > 0 AND
343 p_line_rec.reserved_quantity <> FND_API.G_MISS_NUM AND
344 p_line_rec.reserved_quantity IS NOT NULL THEN
345
346 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ACTION_NOT_ALLOWED');
347 FND_MESSAGE.SET_TOKEN('ACTION',
348 nvl(p_line_rec.schedule_action_code,
349 OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE));
350 IF l_debug_level > 0 THEN
351 oe_debug_pub.add( 'RESERVED QUANTITY IS GREATER THAN ZERO' ) ;
352 END IF;
353 OE_MSG_PUB.Add;
354 l_return_status := FND_API.G_RET_STS_ERROR;
355 END IF;
356
357 IF p_line_rec.schedule_action_code =
358 OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE THEN
359 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ACTION_NOT_ALLOWED');
360 FND_MESSAGE.SET_TOKEN('ACTION',
361 nvl(p_line_rec.schedule_action_code,
362 OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE));
363 FND_MESSAGE.SET_TOKEN('ORDER_TYPE',
364 nvl(oe_order_sch_util.sch_cached_line_type,
365 oe_order_sch_util.sch_cached_order_type));
366 OE_MSG_PUB.Add;
367 l_return_status := FND_API.G_RET_STS_ERROR;
368 END IF;
369 END IF;
370 END IF;
371
372 -- Added this part of validation to fix bug 2051855
373 IF p_line_rec.ato_line_id = p_line_rec.line_id AND
374 p_line_rec.item_type_code in ('STANDARD','OPTION') THEN
375
376 l_org_id := OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
377
378 -- Added code to fix bug 2156268
379 BEGIN
380
381 SELECT planning_make_buy_code
382 INTO l_make_buy
383 FROM mtl_system_items
384 WHERE inventory_item_id = p_line_rec.inventory_item_id
385 AND ORGANIZATION_ID = nvl(p_line_rec.ship_from_org_id,
386 l_org_id);
387
388 EXCEPTION
389 WHEN NO_DATA_FOUND THEN
390 l_make_buy := 1;
391 END;
392
393 IF l_debug_level > 0 THEN
394 oe_debug_pub.add( 'L_MAKE_BUY' || L_MAKE_BUY , 2 ) ;
395 END IF;
396
397 IF nvl(l_make_buy,1) <> 2 THEN
398 BEGIN
399
400 /* added the condition to check for the warehouse entered in the lines block */
401 /* This is done to fix the bug 2692235 */
402
403 SELECT BILL_SEQUENCE_ID
404 INTO l_bill_seq_id
405 FROM BOM_BILL_OF_MATERIALS
406 WHERE ORGANIZATION_ID = nvl(p_line_rec.ship_from_org_id,l_org_id)
407 AND ASSEMBLY_ITEM_ID = p_line_rec.inventory_item_id
408 AND ALTERNATE_BOM_DESIGNATOR IS NULL;
409
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 IF l_debug_level > 0 THEN
413 oe_debug_pub.add( 'NO BILL IS DEFINED' , 2 ) ;
414 END IF;
415 FND_MESSAGE.SET_NAME('ONT','OE_BOM_NO_BILL_IN_VAL_ORG');
416 FND_MESSAGE.SET_TOKEN('ITEM',nvl(p_line_rec.ordered_item,p_line_rec.inventory_item_id));
417 FND_MESSAGE.SET_TOKEN('ORG',l_org_id);
418 OE_MSG_PUB.Add;
419 l_return_status := FND_API.G_RET_STS_ERROR;
420
421 WHEN OTHERS THEN
422 Null;
423 END;
424 END IF;
425 END IF;
426
427 x_return_status := l_return_status;
428
429 IF l_debug_level > 0 THEN
430 oe_debug_pub.add( '..EXITING OE_GRP_SCH_UTIL.VALIDATE_LINE WITH ' || L_RETURN_STATUS , 1 ) ;
431 END IF;
432 END Validate_Line;
433
434 /*-------------------------------------------------------------------
435 Procedure Name : Group_Schedule
436 Description : This procedure will take in a request for a group and
437 will query up all the records for the group and perform
438 the group request on the records.
439 The groups could be:
440 Order
441 Configuration
442 ATO
443 Ship Set
444 Arrival Set
445 The action to be performed could be
446 Demand
447 Reserve
448 ATP_Check
449 Cancel
450 UnDemand
451 UnReserve
452 UnSchedule
453
454 --------------------------------------------------------------------- */
455
456 Procedure Group_Schedule(p_group_req_rec IN OE_GRP_SCH_UTIL.Sch_Group_Rec_Type
457 ,x_atp_tbl OUT NOCOPY OE_ATP.Atp_Tbl_Type
458
459 ,x_return_status OUT NOCOPY Varchar2)
460
461 IS
462 l_return_status VARCHAR2(1);
463
464 l_atp_tbl OE_ATP.Atp_Tbl_Type;
465 l_msg_count NUMBER;
466 l_msg_data VARCHAR2(2000);
467
468 --
469 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
470 --
471 BEGIN
472
473 IF l_debug_level > 0 THEN
474 oe_debug_pub.add( 'ENTERING GROUP SCHEDULE' , 1 ) ;
475 END IF;
476 IF l_debug_level > 0 THEN
477 oe_debug_pub.add( 'ENTITY :' || P_GROUP_REQ_REC.ENTITY_TYPE , 1 ) ;
478 END IF;
479 SAVEPOINT group_schedule;
480
481 Validate_Group_Request(p_group_req_rec => p_group_req_rec,
482 x_return_status => l_return_status);
483
484 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
486 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
487 RAISE FND_API.G_EXC_ERROR;
488 END IF;
489
490 IF (p_group_req_rec.entity_type =
491 OE_ORDER_SCH_UTIL.OESCH_ENTITY_ATO_CONFIG)
492 THEN
493 Schedule_ATO(p_group_req_rec => p_group_req_rec,
494 x_atp_tbl => l_atp_tbl,
495 x_return_status => l_return_status);
496
497 x_atp_tbl := l_atp_tbl;
498 ELSIF (p_group_req_rec.entity_type =
499 OE_ORDER_SCH_UTIL.OESCH_ENTITY_ORDER) OR
500 (p_group_req_rec.entity_type =
501 OE_ORDER_SCH_UTIL.OESCH_ENTITY_LINE)
502 THEN
503 Schedule_Order(p_header_id => p_group_req_rec.header_id,
504 p_sch_action => p_group_req_rec.action,
505 p_entity_type => p_group_req_rec.entity_type,
506 p_line_id => p_group_req_rec.line_id,
507 x_atp_tbl => l_atp_tbl,
508 x_return_status => l_return_status,
509 x_msg_count => l_msg_count,
510 x_msg_data => l_msg_data);
511
512 x_atp_tbl := l_atp_tbl;
513
514 ELSIF (p_group_req_rec.entity_type =
515 OE_ORDER_SCH_UTIL.OESCH_ENTITY_SMC)
516 THEN
517 Schedule_Set(p_group_req_rec => p_group_req_rec,
518 x_atp_tbl => l_atp_tbl,
519 x_return_status => l_return_status);
520
521 x_atp_tbl := l_atp_tbl;
522 ELSIF (p_group_req_rec.entity_type =
523 OE_ORDER_SCH_UTIL.OESCH_ENTITY_SHIP_SET) OR
524 (p_group_req_rec.entity_type =
525 OE_ORDER_SCH_UTIL.OESCH_ENTITY_ARRIVAL_SET)
526 THEN
527 Schedule_Set(p_group_req_rec => p_group_req_rec,
528 x_atp_tbl => l_atp_tbl,
529 x_return_status => l_return_status);
530
531 x_atp_tbl := l_atp_tbl;
532 END IF;
533
534 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
537 RAISE FND_API.G_EXC_ERROR;
538 END IF;
539
540 x_return_status := FND_API.G_RET_STS_SUCCESS;
541
542 IF l_debug_level > 0 THEN
543 oe_debug_pub.add( 'EXITING GROUP SCHEDULE' , 1 ) ;
544 END IF;
545
546 EXCEPTION
547
548 WHEN FND_API.G_EXC_ERROR THEN
549
550 x_return_status := FND_API.G_RET_STS_ERROR;
551
552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
553
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555
556 WHEN OTHERS THEN
557
558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
559
560 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
561 THEN
562 OE_MSG_PUB.Add_Exc_Msg
563 ( G_PKG_NAME
564 , 'Group_Schedule'
565 );
566 END IF;
567 END Group_Schedule;
568
569 /* ---------------------------------------------------------------
570 Procedure Schedule_Order
571 This procedure is responsible for scheduling the whole order.
572 An order can consists of many lines and each line can be within a group.
573 We plan to schedule in the following way:
574 1. Schedule standard lines and non smc PTO options
575 (not in any set) independently.
576 2. Schedule ATO (not in any other set) Options together.
577 3. Schedule PTO-SMC (not in any other set) Options together.
578 4. Schedule PTO-SMC (not in any other set) Options together.
579 4. Schedule Ship set (not in any arrival set) together.
580 4. Schedule Arrival set together.
581 ---------------------------------------------------------------*/
582 Procedure Schedule_Order(p_header_id IN NUMBER,
583 p_sch_action IN VARCHAR2,
584 p_entity_type IN VARCHAR2,
585 p_line_id IN NUMBER,
586 x_atp_tbl OUT NOCOPY OE_ATP.Atp_Tbl_Type,
587
588 x_return_status OUT NOCOPY VARCHAR2,
589
590 x_msg_count OUT NOCOPY NUMBER,
591
592 x_msg_data OUT NOCOPY VARCHAR2)
593
594 IS
595 l_old_line_rec OE_ORDER_PUB.line_rec_type;
596 --l_out_line_rec OE_ORDER_PUB.line_rec_type;
597 l_out_atp_rec OE_ATP.atp_rec_type;
598 l_out_atp_tbl OE_ATP.atp_tbl_type;
599 l_line_rec OE_ORDER_PUB.line_rec_type;
600 l_return_status VARCHAR2(1);
601 l_msg_count NUMBER;
602 l_msg_data VARCHAR2(2000);
603
604 l_control_rec OE_GLOBALS.control_rec_type;
605 l_line_tbl OE_ORDER_PUB.line_tbl_type;
606 l_header_out_rec OE_Order_PUB.Header_Rec_Type;
607 l_header_rec OE_Order_PUB.Header_Rec_Type;
608 l_line_out_tbl OE_Order_PUB.Line_Tbl_Type;
609 l_header_adj_out_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
610 l_header_scredit_out_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
611 l_line_adj_out_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
612 l_line_scredit_out_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
613 l_lot_serial_out_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
614 l_action_request_out_tbl OE_Order_PUB.Request_Tbl_Type;
615 l_new_line_tbl OE_ORDER_PUB.line_tbl_type;
616 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
617 l_out_line_tbl OE_ORDER_PUB.line_tbl_type;
618 l_group_req_rec OE_GRP_SCH_UTIL.Sch_Group_Rec_Type;
619
620 l_arrival_set_id NUMBER;
621 l_ato_line_id NUMBER;
622 l_demand_class_code VARCHAR2(30);
623 l_delivery_lead_time NUMBER;
624 l_freight_carrier_code VARCHAR2(30);
625 l_header_id NUMBER;
626 l_inventory_item_id NUMBER;
627 l_invoice_to_org_id NUMBER;
628 l_item_type_code VARCHAR2(30);
629 l_ordered_item VARCHAR2(2000);
630 l_line_id NUMBER;
631 l_ordered_quantity NUMBER;
632 l_order_quantity_uom VARCHAR2(3);
633 l_request_date DATE;
634 l_schedule_ship_date DATE;
635 l_schedule_arrival_date DATE;
636 l_ship_from_org_id NUMBER;
637 l_ship_model_complete_flag VARCHAR2(1);
638 l_ship_set_id NUMBER;
639 l_ship_to_org_id NUMBER;
640 l_schedule_status_code VARCHAR2(30);
641 l_shipping_method_code VARCHAR2(30);
642 l_sold_to_org_id NUMBER;
643 l_top_model_line_id NUMBER;
644
645 atp_count NUMBER;
646 I NUMBER;
647
648 processed_ship_set number_arr;
649 processed_arrival_set number_arr;
650 processed_pto_smc number_arr;
651 processed_ato number_arr;
652
653 ship_set_count NUMBER := 0;
654 arrival_set_count NUMBER := 0;
655 pto_smc_count NUMBER := 0;
656 ato_count NUMBER := 0;
657
658
659 CURSOR standard_lines IS
660 SELECT arrival_set_id,
661 ato_line_id,
662 demand_class_code,
663 delivery_lead_time,
664 freight_carrier_code,
665 header_id,
666 inventory_item_id,
667 invoice_to_org_id,
668 item_type_code,
669 ordered_item,
670 line_id,
671 ordered_quantity,
672 order_quantity_uom,
673 request_date,
674 schedule_ship_date,
675 schedule_arrival_date,
676 ship_from_org_id,
677 ship_model_complete_flag,
678 ship_set_id,
679 ship_to_org_id,
680 schedule_status_code,
681 shipping_method_code,
682 sold_to_org_id,
683 top_model_line_id
684 FROM oe_order_lines
685 WHERE header_id = p_header_id
686 AND item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED;
687
688 --
689 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
690 --
691 BEGIN
692
693 IF l_debug_level > 0 THEN
694 oe_debug_pub.add( 'ENTERING OE_GRP_SCH_UTIL.SCHEDULE_ORDER' , 1 ) ;
695 END IF;
696
697 /* Bug :2222360 */
698 IF p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
699 IF l_debug_level > 0 THEN
700 oe_debug_pub.add( 'INSIDE ATP CHECK SAVEPOINT' ) ;
701 END IF;
702 SAVEPOINT ATP_CHECK;
703 END IF;
704
705 IF l_debug_level > 0 THEN
706 oe_debug_pub.add( 'P_HEADER_ID : ' || P_HEADER_ID , 1 ) ;
707 END IF;
708 IF l_debug_level > 0 THEN
709 oe_debug_pub.add( 'P_SCH_ACTION: ' || P_SCH_ACTION , 1 ) ;
710 END IF;
711
712 -- l_line_tbl := Query_Lines(p_header_id => p_header_id);
713
714 Query_Lines(p_header_id => p_header_id,
715 x_line_tbl => l_line_tbl);
716
717 atp_count := 1;
718 I := 1;
719
720 IF l_debug_level > 0 THEN
721 oe_debug_pub.add( 'NO. OF LINES TO SCHEDULE: ' || L_LINE_TBL.COUNT , 1 ) ;
722 END IF;
723
724 l_arrival_set_id := null;
725 l_ship_set_id := null;
726 ship_set_count := 0;
727 arrival_set_count := 0;
728
729 FOR line_index IN 1..l_line_tbl.count
730 LOOP
731 BEGIN
732
733 SAVEPOINT SCHEDULE_ORDER;
734 OE_MSG_PUB.set_msg_context
735 ( p_entity_code => 'LINE'
736 ,p_entity_id => l_line_tbl(line_index).line_id
737 ,p_header_id => l_line_tbl(line_index).header_id
738 ,p_line_id => l_line_tbl(line_index).line_id
739 ,p_orig_sys_document_ref =>
740 l_line_tbl(line_index).orig_sys_document_ref
741 ,p_orig_sys_document_line_ref =>
742 l_line_tbl(line_index).orig_sys_line_ref
743 ,p_orig_sys_shipment_ref => l_line_tbl(line_index).orig_sys_shipment_ref
744 ,p_change_sequence => l_line_tbl(line_index).change_sequence
745 ,p_source_document_id =>
746 l_line_tbl(line_index).source_document_id
747 ,p_source_document_line_id =>
748 l_line_tbl(line_index).source_document_line_id
749 ,p_order_source_id =>
750 l_line_tbl(line_index).order_source_id
751 ,p_source_document_type_id =>
752 l_line_tbl(line_index).source_document_type_id);
753
754 -- Added code to fix bug 1778701.
755 IF l_line_tbl(line_index).line_category_code = 'RETURN' THEN
756
757
758 --Ingnore return lines to schedule.
759
760 goto end_loop;
761
762 END IF;
763
764 IF (l_line_tbl(line_index).item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
765 l_line_tbl(line_index).item_type_code = OE_GLOBALS.G_ITEM_OPTION) AND
766 l_line_tbl(line_index).ato_line_id <>
767 l_line_tbl(line_index).line_id AND
768 (l_line_tbl(line_index).ship_model_complete_flag = 'Y' OR
769 l_line_tbl(line_index).ato_line_id is not null) AND
770 (p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE OR
771 p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE OR
772 p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK) THEN
773
774 -- Skipping this option line since it will be picked up when the
775 -- model is scheduled.
776 goto end_loop;
777
778 END IF;
779
780 l_line_rec := l_line_tbl(line_index);
781 l_old_line_rec := l_line_rec;
782
783 -- Code has been modified to fix bug 1873099.
784 -- IF p_sch_action <> OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE THEN
785 IF p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE OR
786 p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE OR
787 p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK OR
788 (p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE AND
789 l_line_rec.schedule_status_code is null) THEN
790
791 -- We want to skip a line belonging to a set only if the action
792 -- is SCHEDULE,ATP or UNSCHEDULE. We do not want to skip if the
793 -- action is RESERVE or UNRESERVE, since then, we will treat the
794 -- line independently.
795
796 IF l_line_rec.arrival_set_id is not null THEN
797 -- Check to see if this set is already processed. If yes, then
798 -- go to the end.
799 FOR c IN 1..processed_arrival_set.count LOOP
800 IF l_line_rec.arrival_set_id = processed_arrival_set(c)
801 THEN
802 -- This set has been processed.
803 goto end_loop;
804 END IF;
805 END LOOP;
806
807 -- If the line could not find it's arrival set id in the
808 -- processed_arrival_set, it has not been processed. Let's add the
809 -- set_id to the table and process the set.
810
811 arrival_set_count := arrival_set_count + 1;
812 processed_arrival_set(arrival_set_count) :=
813 l_line_rec.arrival_set_id;
814
815 ELSIF l_line_rec.ship_set_id is not null THEN
816 -- Check to see if this set is already processed. If yes, then
817 -- go to the end.
818 FOR c IN 1..processed_ship_set.count LOOP
819 IF l_line_rec.ship_set_id = processed_ship_set(c)
820 THEN
821 -- This set has been processed.
822 goto end_loop;
823 END IF;
824 END LOOP;
825
826 -- If the line could not find it's ship set id in the
827 -- processed_ship_set, it has not been processed. Let's add the
828 -- set_id to the table and process the set.
829
830 ship_set_count := ship_set_count + 1;
831 processed_ship_set(ship_set_count) := l_line_rec.ship_set_id;
832 ELSIF nvl(l_line_rec.ship_model_complete_flag,'N') = 'Y' THEN
833 -- Check to see if this set is already processed. If yes, then
834 -- go to the end.
835 pto_smc_count := processed_pto_smc.count;
836 IF pto_smc_count > 0 THEN
837 FOR c IN 1..processed_pto_smc.count LOOP
838 IF l_line_rec.top_model_line_id = processed_pto_smc(c)
839 THEN
840 -- This set has been processed.
841 goto end_loop;
842 END IF;
843 END LOOP;
844 END IF;
845
846 -- If the line could not find it's top model line id in the
847 -- processed_pto_smc, it has not been processed. Let's add the
848 -- top_model_line_id to the table and process the set.
849
850 pto_smc_count := pto_smc_count + 1;
851 processed_pto_smc(pto_smc_count) := l_line_rec.top_model_line_id;
852 ELSIF l_line_rec.ato_line_id is not null THEN
853 -- Check to see if this set is already processed. If yes, then
854 -- go to the end.
855 ato_count := processed_ato.count;
856 IF ato_count > 0 THEN
857 FOR c IN 1..processed_ato.count LOOP
858 IF l_line_rec.ato_line_id = processed_ato(c)
859 THEN
860 -- This set has been processed.
861 goto end_loop;
862 END IF;
863 END LOOP;
864 END IF;
865
866 -- If the line could not find it's ATO Line id in the
867 -- processed_ato set, it has not been processed. Let's add the
868 -- set_id to the table and process the set.
869
870 ato_count := ato_count + 1;
871 processed_ato(ato_count) := l_line_rec.ato_line_id;
872
873 END IF;
874 END IF; /* Action is not Reserve */
875
876 IF (l_line_rec.ship_set_id is not null OR
877 l_line_rec.arrival_set_id is not null OR
878 l_line_rec.ship_model_complete_flag = 'Y' OR
879 (l_line_rec.ato_line_id = l_line_rec.line_id AND
880 l_line_rec.item_type_code <> OE_GLOBALS.G_ITEM_STANDARD AND
881 l_line_rec.item_type_code <> OE_GLOBALS.G_ITEM_OPTION)) AND
882 (p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE OR
883 p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE OR
884 p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK OR
885 (p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE AND
886 l_line_rec.schedule_status_code is null ) )
887 THEN
888
889 IF l_debug_level > 0 THEN
890 oe_debug_pub.add( 'SCHEDULING A GROUP ' , 1 ) ;
891 END IF;
892 l_line_rec.schedule_action_code := p_sch_action;
893
894 IF l_debug_level > 0 THEN
895 oe_debug_pub.add( 'SO: CREATING GROUP_REQUEST' , 1 ) ;
896 END IF;
897
898 OE_ORDER_SCH_UTIL.Create_Group_Request
899 ( p_line_rec => l_line_rec
900 , p_old_line_rec => l_line_rec
901 , x_group_req_rec => l_group_req_rec
902 , x_return_status => l_return_status
903 );
904
905 -- Line belongs to a group. Needs to be scheduled in a group.
906
907 IF l_debug_level > 0 THEN
908 oe_debug_pub.add( 'SO: CALLING GROUP_SCHEDULE: ' || L_RETURN_STATUS , 1 ) ;
909 END IF;
910
911 Group_Schedule
912 ( p_group_req_rec => l_group_req_rec
913 ,x_atp_tbl => l_out_atp_tbl
914 ,x_return_status => l_return_status);
915
916 IF l_debug_level > 0 THEN
917 oe_debug_pub.add( 'SO: AFTER CALLING GROUP_SCHEDULE' || L_RETURN_STATUS , 1 ) ;
918 END IF;
919
920 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
922 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
923 RAISE FND_API.G_EXC_ERROR;
924 END IF;
925
926 FOR J IN 1..l_out_atp_tbl.count
927 LOOP
928 x_atp_tbl(atp_count) := l_out_atp_tbl(J);
929 atp_count := atp_count + 1;
930 END LOOP;
931
932 ELSE
933
934 l_line_rec.schedule_action_code := p_sch_action;
935 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
936
937 IF l_debug_level > 0 THEN
938 oe_debug_pub.add( 'SCHEDULING LINE: ' || L_LINE_REC.LINE_ID , 1 ) ;
939 END IF;
940 IF l_debug_level > 0 THEN
941 oe_debug_pub.add( 'ITEM TYPE IS : ' || L_LINE_REC.ITEM_TYPE_CODE , 1 ) ;
942 END IF;
943 IF l_debug_level > 0 THEN
944 oe_debug_pub.add( ' ' , 1 ) ;
945 END IF;
946
947 IF l_debug_level > 0 THEN
948 oe_debug_pub.add( 'GRP: CALLING SCHEDULE LINE' , 1 ) ;
949 END IF;
950
951 OE_ORDER_SCH_UTIL.Schedule_line
952 ( p_old_line_rec => l_old_line_rec
953 ,p_write_to_db => FND_API.G_TRUE
954 ,p_x_line_rec => l_line_rec
955 ,x_atp_tbl => l_out_atp_tbl
956 ,x_return_status => l_return_status);
957
958 IF l_debug_level > 0 THEN
959 oe_debug_pub.add( 'GRP:AFTER CALLING SCHEDULE LINE: ' || L_RETURN_STATUS , 1 ) ;
960 END IF;
961
962 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
964 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
965 RAISE FND_API.G_EXC_ERROR;
966 END IF;
967
968 -- Load the ATP table which could have more records than 1 since
969 -- included items got scheduled.
970
971 FOR J IN 1..l_out_atp_tbl.count
972 LOOP
973 x_atp_tbl(atp_count) := l_out_atp_tbl(J);
974 atp_count := atp_count + 1;
975 END LOOP;
976
977 I := I + 1;
978
979 END IF;
980
981 <<end_loop>>
982 null;
983
984 EXCEPTION
985
986 WHEN FND_API.G_EXC_ERROR THEN
987
988 -- We do not want to error our the whole order if a line
989 -- did not schedule.
990
991 ROLLBACK TO SCHEDULE_ORDER;
992 null;
993
994 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
995
996 ROLLBACK TO SCHEDULE_ORDER;
997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998
999 WHEN OTHERS THEN
1000
1001 ROLLBACK TO SCHEDULE_ORDER;
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003
1004 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1005 THEN
1006 OE_MSG_PUB.Add_Exc_Msg
1007 ( G_PKG_NAME
1008 , 'Schedule_Order'
1009 );
1010 END IF;
1011 END;
1012 END LOOP;
1013
1014 oe_msg_pub.count_and_get
1015 ( p_count => x_msg_count
1016 ,p_data => x_msg_data
1017 );
1018
1019 IF l_debug_level > 0 THEN
1020 oe_debug_pub.add( 'COUNT IS ' || X_MSG_COUNT , 1 ) ;
1021 END IF;
1022 IF l_debug_level > 0 THEN
1023 oe_debug_pub.add( 'EXITING OE_GRP_SCH_UTIL.SCHEDULE_ORDER' , 1 ) ;
1024 END IF;
1025 x_return_status := FND_API.G_RET_STS_SUCCESS;
1026
1027 /* Bug :2222360 */
1028 IF p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
1029 IF l_debug_level > 0 THEN
1030 oe_debug_pub.add( 'INSIDE ATP CHECK ROLLBACK' ) ;
1031 END IF;
1032 ROLLBACK TO ATP_CHECK;
1033 OE_Delayed_Requests_Pvt.Clear_Request
1034 (x_return_status => l_return_status);
1035
1036 END IF;
1037
1038 EXCEPTION
1039
1040 WHEN FND_API.G_EXC_ERROR THEN
1041
1042 x_return_status := FND_API.G_RET_STS_ERROR;
1043
1044 oe_msg_pub.count_and_get
1045 ( p_count => x_msg_count
1046 ,p_data => x_msg_data
1047 );
1048 /* Bug :2222360 */
1049 IF p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
1050 IF l_debug_level > 0 THEN
1051 oe_debug_pub.add( 'INSIDE ATP CHECK ROLLBACK' ) ;
1052 END IF;
1053 ROLLBACK TO ATP_CHECK;
1054 OE_Delayed_Requests_Pvt.Clear_Request
1055 (x_return_status => l_return_status);
1056 END IF;
1057
1058
1059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1060
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1062
1063 oe_msg_pub.count_and_get
1064 ( p_count => x_msg_count
1065 ,p_data => x_msg_data
1066 );
1067
1068 /* Bug :2222360 */
1069 IF p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
1070 IF l_debug_level > 0 THEN
1071 oe_debug_pub.add( 'INSIDE ATP CHECK ROLLBACK' ) ;
1072 END IF;
1073 ROLLBACK TO ATP_CHECK;
1074 OE_Delayed_Requests_Pvt.Clear_Request
1075 (x_return_status => l_return_status);
1076 END IF;
1077
1078 WHEN OTHERS THEN
1079
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1081
1082 /* Bug :2222360 */
1083 IF p_sch_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
1084 IF l_debug_level > 0 THEN
1085 oe_debug_pub.add( 'INSIDE ATP CHECK ROLLBACK' ) ;
1086 END IF;
1087 ROLLBACK TO ATP_CHECK;
1088 OE_Delayed_Requests_Pvt.Clear_Request
1089 (x_return_status => l_return_status);
1090 END IF;
1091
1092 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1093 THEN
1094 OE_MSG_PUB.Add_Exc_Msg
1095 ( G_PKG_NAME
1096 , 'Schedule_Order'
1097 );
1098 END IF;
1099
1100 oe_msg_pub.count_and_get
1101 ( p_count => x_msg_count
1102 ,p_data => x_msg_data
1103 );
1104
1105 END Schedule_Order;
1106 /* ---------------------------------------------------------------
1107 Procedure Schedule_ATO
1108 ---------------------------------------------------------------*/
1109
1110 Procedure Schedule_ATO(p_group_req_rec IN OE_GRP_SCH_UTIL.Sch_Group_Rec_Type,
1111 x_atp_tbl OUT NOCOPY OE_ATP.Atp_Tbl_Type,
1112
1113 x_return_status OUT NOCOPY VARCHAR2)
1114
1115 IS
1116 l_line_id NUMBER;
1117 l_atp_tbl OE_ATP.atp_tbl_type;
1118 l_line_rec OE_ORDER_PUB.line_rec_type;
1119 l_config_line_rec OE_ORDER_PUB.line_rec_type;
1120 l_option_rec OE_ORDER_PUB.line_rec_type;
1121 l_option_tbl OE_ORDER_PUB.line_tbl_type;
1122 l_old_option_tbl OE_ORDER_PUB.line_tbl_type;
1123 l_old_line_rec OE_ORDER_PUB.line_rec_type;
1124 l_out_line_rec OE_ORDER_PUB.line_rec_type;
1125 l_model_line_rec OE_ORDER_PUB.line_rec_type;
1126 l_out_atp_rec OE_ATP.atp_rec_type;
1127 l_return_status VARCHAR2(1);
1128 l_msg_count NUMBER;
1129 l_msg_data VARCHAR2(2000);
1130 l_action VARCHAR2(30) := NULL;
1131 -- For calling process order.
1132 l_control_rec OE_GLOBALS.control_rec_type;
1133 l_line_tbl OE_ORDER_PUB.line_tbl_type;
1134 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
1135 l_header_out_rec OE_Order_PUB.Header_Rec_Type;
1136 l_header_rec OE_Order_PUB.Header_Rec_Type;
1137 l_line_out_tbl OE_Order_PUB.Line_Tbl_Type;
1138 l_header_adj_out_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
1139 l_header_scredit_out_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
1140 l_line_adj_out_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
1141 l_line_scredit_out_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
1142 l_lot_serial_out_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
1143 l_action_request_out_tbl OE_Order_PUB.Request_Tbl_Type;
1144 l_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
1145 l_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
1146 l_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
1147 l_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
1148 l_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
1149 l_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
1150
1151 l_component_ratio NUMBER;
1152 l_model_quantity_chg NUMBER;
1153 l_ato_model_quantity NUMBER;
1154 l_config_id NUMBER;
1155 l_ato_line_id NUMBER;
1156 l_ship_set_id NUMBER := null;
1157 l_arrival_set_id NUMBER := null;
1158 l_cancelled_flag VARCHAR2(1) := 'N';
1159
1160 --
1161 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1162 --
1163 BEGIN
1164
1165 IF l_debug_level > 0 THEN
1166 oe_debug_pub.add( 'ENTERING SCHEDULE_ATO' , 1 ) ;
1167 END IF;
1168
1169 l_line_id := p_group_req_rec.line_id;
1170
1171 -- Query the ATO line Id,Ship Set Id and Arrival Set Id from the line.
1172
1173 SELECT ato_line_id , ship_set_id, arrival_set_id
1174 INTO l_ato_line_id,l_ship_set_id,l_arrival_set_id
1175 FROM oe_order_lines_all
1176 WHERE line_id = l_line_id;
1177
1178 IF l_debug_level > 0 THEN
1179 oe_debug_pub.add( 'WAREHOUSE IS ' || P_GROUP_REQ_REC.SHIP_FROM_ORG_ID , 1 ) ;
1180 END IF;
1181 IF l_debug_level > 0 THEN
1182 oe_debug_pub.add( 'LINE ID IS : ' || L_LINE_ID , 1 ) ;
1183 END IF;
1184
1185 --l_model_line_rec := Oe_line_Util.Query_Row(p_line_id => l_line_id);
1186
1187 OE_Line_Util.Query_Row(p_line_id => l_ato_line_id,
1188 x_line_rec => l_model_line_rec);
1189
1190 IF l_debug_level > 0 THEN
1191 oe_debug_pub.add( 'CHECKING WAREHOUSE' , 1 ) ;
1192 END IF;
1193
1194 -- Check to see if a warehouse is specified on the line. You need a
1195 -- warehouse to schedule an ATO
1196
1197 IF l_model_line_rec.ship_from_org_id is NULL AND
1198 (p_group_req_rec.action = OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE OR
1199 p_group_req_rec.action = OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE) THEN
1200
1201 -- You cannot schedule a ATO model without a warehouse. So flag
1202 -- an error.
1203
1204 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ATO_WHSE_REQD');
1205 OE_MSG_PUB.Add;
1206 RAISE FND_API.G_EXC_ERROR;
1207
1208 END IF;
1209
1210 IF (p_group_req_rec.action = OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE) OR
1211 (p_group_req_rec.action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNRESERVE)
1212 THEN
1213 -- This action is not allowed on an ATO configuration.
1214 FND_MESSAGE.SET_NAME('ONT','OE_SCH_RES_NO_CONFIG');
1215 OE_MSG_PUB.Add;
1216 RAISE FND_API.G_EXC_ERROR;
1217
1218 END IF; /* If action was reserve or unreserve */
1219
1220 IF (p_group_req_rec.action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE)
1221 THEN
1222 -- This action is not allowed on an ATO configuration if the config
1223 -- item is created.
1224 BEGIN
1225 SELECT line_Id
1226 INTO l_config_id
1227 FROM OE_ORDER_LINES_ALL
1228 WHERE ato_line_id=l_model_line_rec.line_id
1229 AND top_model_line_id =l_model_line_rec.top_model_line_id
1230 AND item_type_code = 'CONFIG';
1231
1232 FND_MESSAGE.SET_NAME('ONT','OE_SCH_UNSCH_CONFIG_EXISTS');
1233 OE_MSG_PUB.Add;
1234 RAISE FND_API.G_EXC_ERROR;
1235
1236 EXCEPTION
1237 WHEN NO_DATA_FOUND THEN
1238 null;
1239 END;
1240
1241 END IF; /* If action was unschedule */
1242
1243 -- Query All the lines that belong to this ATO Group
1244 -- l_line_tbl := OE_CONFIG_UTIL.Query_ATO_Options
1245 -- (l_model_line_rec.ato_line_id);
1246 /*
1247 IF l_model_line_rec.ordered_quantity = 0 THEN
1248 l_cancelled_flag := 'Y';
1249 oe_debug_pub.add('SCH: Setting cancelled flag to Y',1);
1250 ELSE
1251 l_cancelled_flag := 'N';
1252 END IF;
1253 */
1254 l_cancelled_flag := 'Y';
1255
1256 OE_Config_Util.Query_ATO_Options
1257 (p_ato_line_id => l_model_line_rec.ato_line_id,
1258 p_send_cancel_lines => l_cancelled_flag,
1259 x_line_tbl => l_line_tbl);
1260
1261 -- If the group request has passed a new warehouse, validate the group
1262 -- with the new warehouse.
1263
1264 IF (p_group_req_rec.ship_from_org_id is not null) THEN
1265
1266 IF l_debug_level > 0 THEN
1267 oe_debug_pub.add( 'CALLING VALIDATE_WAREHOUSE' , 1 ) ;
1268 END IF;
1269
1270 Validate_Warehouse
1271 (p_line_tbl => l_line_tbl
1272 ,p_ship_from_org_id => p_group_req_rec.ship_from_org_id
1273 ,x_return_status => l_return_status);
1274
1275 IF l_debug_level > 0 THEN
1276 oe_debug_pub.add( 'AFTER CALLING VALIDATE_WAREHOUSE: ' || L_RETURN_STATUS , 1 ) ;
1277 END IF;
1278
1279 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1281 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1282 RAISE FND_API.G_EXC_ERROR;
1283 END IF;
1284 END IF;
1285
1286 IF l_debug_level > 0 THEN
1287 oe_debug_pub.add( 'NO. OF LINES: ' || L_LINE_TBL.COUNT , 1 ) ;
1288 END IF;
1289 l_old_line_tbl := l_line_tbl;
1290
1291 FOR I IN 1..l_old_line_tbl.count LOOP
1292
1293 IF l_old_line_tbl(1).schedule_status_code is null THEN
1294
1295 l_old_line_tbl(I).schedule_ship_date := null;
1296 l_old_line_tbl(I).schedule_arrival_date := null;
1297 l_old_line_tbl(I).arrival_set_id := null;
1298 l_old_line_tbl(I).ship_set_id := null;
1299 ELSE
1300
1301 IF l_debug_level > 0 THEN
1302 oe_debug_pub.add( 'SETTING OLD TO OLD DATES ' , 1 ) ;
1303 END IF;
1304 IF p_group_req_rec.old_schedule_ship_date is not null THEN
1305 l_old_line_tbl(I).schedule_ship_date :=
1306 p_group_req_rec.old_schedule_ship_date;
1307 END IF;
1308 IF p_group_req_rec.old_schedule_arrival_date is not null THEN
1309 l_old_line_tbl(I).schedule_arrival_date :=
1310 p_group_req_rec.old_schedule_arrival_date;
1311 END IF;
1312 IF p_group_req_rec.old_ship_from_org_id is not null THEN
1313
1314 l_old_line_tbl(I).ship_from_org_id :=
1315 p_group_req_rec.old_ship_from_org_id;
1316 END IF;
1317 -- Populating old dates to fix bug 2194475.
1318 IF p_group_req_rec.old_request_date is not null THEN
1319
1320 l_old_line_tbl(I).request_date :=
1321 p_group_req_rec.old_request_date;
1322 END IF;
1323 l_old_line_tbl(I).ship_set_id := p_group_req_rec.old_ship_set_number;
1324 l_old_line_tbl(I).arrival_set_id := p_group_req_rec.old_arrival_set_number;
1325 END IF;
1326
1327 END LOOP;
1328
1329 /* IF p_group_req_rec.old_schedule_ship_date is not null OR
1330 p_group_req_rec.old_schedule_arrival_date is not null THEN
1331
1332 oe_debug_pub.add('Schedule date has changed',1);
1333 oe_debug_pub.add('Setting Old to old dates ',1);
1334 FOR I IN 1..l_old_line_tbl.count LOOP
1335 IF p_group_req_rec.old_schedule_ship_date is not null THEN
1336 l_old_line_tbl(I).schedule_ship_date :=
1337 p_group_req_rec.old_schedule_ship_date;
1338 END IF;
1339 IF p_group_req_rec.old_schedule_arrival_date is not null THEN
1340 l_old_line_tbl(I).schedule_arrival_date :=
1341 p_group_req_rec.old_schedule_arrival_date;
1342 END IF;
1343 END LOOP;
1344 END IF;
1345
1346 IF l_old_line_tbl.count >= 1 AND
1347 l_old_line_tbl(1).schedule_status_code is null THEN
1348
1349 FOR I IN 1..l_old_line_tbl.count LOOP
1350 l_old_line_tbl(I).schedule_ship_date := null;
1351 l_old_line_tbl(I).schedule_arrival_date := null;
1352 l_old_line_tbl(I).arrival_set_id := null;
1353 l_old_line_tbl(I).ship_set_id := null;
1354 END LOOP;
1355
1356 END IF;
1357 */
1358
1359 -- Set the request on the line the lines of the ATO Group
1360
1361 IF p_group_req_rec.old_quantity <> p_group_req_rec.quantity
1362 THEN
1363 IF l_debug_level > 0 THEN
1364 oe_debug_pub.add( 'MODEL QUANTITY HAS CHANGED' , 1 ) ;
1365 END IF;
1366 IF l_debug_level > 0 THEN
1367 oe_debug_pub.add( 'MODEL OLD QUANTITY ' || P_GROUP_REQ_REC.OLD_QUANTITY , 1 ) ;
1368 END IF;
1369 IF l_debug_level > 0 THEN
1370 oe_debug_pub.add( 'MODEL QUANTITY ' || P_GROUP_REQ_REC.QUANTITY , 1 ) ;
1371 END IF;
1372 l_ato_model_quantity := p_group_req_rec.old_quantity;
1373 l_model_quantity_chg := p_group_req_rec.quantity -
1374 p_group_req_rec.old_quantity;
1375 END IF;
1376
1377 -- If the group action is schedule on a scheduled group, changing the same
1378 -- to reschedule, otherwise respect group action value.
1379
1380 IF l_model_line_rec.schedule_status_code is not null
1381 AND p_group_req_rec.action = OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE
1382 THEN
1383 l_action := OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE;
1384 ELSE
1385
1386 IF p_group_req_rec.action is not null THEN
1387 l_action := p_group_req_rec.action;
1388 ELSE
1389 l_action := OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE;
1390 END IF;
1391
1392 END IF;
1393
1394 FOR I IN 1..l_line_tbl.count LOOP
1395 l_line_rec := l_line_tbl(I);
1396
1397 /* IF p_group_req_rec.action is not null THEN
1398 l_line_rec.schedule_action_code := p_group_req_rec.action;
1399 ELSE
1400 l_line_rec.schedule_action_code :=
1401 OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE;
1402 END IF;
1403 */
1404 l_line_rec.schedule_action_code := l_action;
1405 l_line_rec.ship_set := l_line_rec.ato_line_id;
1406 l_line_rec.ship_set_id := l_ship_set_id;
1407 l_line_rec.arrival_set_id := l_arrival_set_id;
1408
1409 -- If there is a change to the model quantity,
1410 -- cascade the Quantity Change to all the Options
1411
1412 IF p_group_req_rec.old_quantity <> p_group_req_rec.quantity THEN
1413 l_component_ratio := l_line_rec.ordered_quantity/l_ato_model_quantity;
1414 l_line_rec.ordered_quantity := l_component_ratio *
1415 p_group_req_rec.quantity;
1416 IF l_debug_level > 0 THEN
1417 oe_debug_pub.add( 'NEW QTY: ' || L_LINE_REC.ORDERED_QUANTITY , 1 ) ;
1418 END IF;
1419 END IF;
1420
1421 IF (p_group_req_rec.ship_from_org_id is not null) THEN
1422 l_line_rec.ship_from_org_id := p_group_req_rec.ship_from_org_id;
1423 END IF;
1424 IF (p_group_req_rec.ship_to_org_id is not null) THEN
1425 l_line_rec.ship_to_org_id := p_group_req_rec.ship_to_org_id;
1426 END IF;
1427 IF (p_group_req_rec.request_date is not null) THEN
1428 l_line_rec.schedule_ship_date := p_group_req_rec.request_date;
1429 l_line_rec.request_date := p_group_req_rec.request_date;
1430 END IF;
1431 IF (p_group_req_rec.schedule_ship_date is not null) THEN
1432 l_line_rec.schedule_ship_date := p_group_req_rec.schedule_ship_date;
1433 END IF;
1434 IF (p_group_req_rec.schedule_arrival_date is not null) THEN
1435 l_line_rec.schedule_arrival_date := p_group_req_rec.schedule_arrival_date;
1436 END IF;
1437
1438 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1439 l_line_tbl(I) := l_line_rec;
1440 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
1441
1442 END LOOP;
1443
1444 -- Added a code to fix bug 2275374.
1445 FOR I IN 1..l_line_tbl.count LOOP
1446
1447 Validate_line(p_line_rec => l_line_tbl(I),
1448 p_old_line_rec => l_old_line_tbl(I),
1449 x_return_status => l_return_status);
1450
1451 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1452 IF l_debug_level > 0 THEN
1453 oe_debug_pub.add( 'ATO: AFTER VALIDATE LINE UN EXP ERROR' || L_LINE_TBL ( I ) .LINE_ID , 1 ) ;
1454 END IF;
1455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1456 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1457 RAISE FND_API.G_EXC_ERROR;
1458 IF l_debug_level > 0 THEN
1459 oe_debug_pub.add( 'ATO: AFTER VALIDATE LINE EXP ERROR' || L_LINE_TBL ( I ) .LINE_ID , 1 ) ;
1460 END IF;
1461 END IF;
1462
1463 END LOOP;
1464 IF l_debug_level > 0 THEN
1465 oe_debug_pub.add( 'ATO: CALLING PROCESS_SET_OF_LINES' , 1 ) ;
1466 END IF;
1467
1468 Process_set_of_lines( p_old_line_tbl => l_old_line_tbl,
1469 x_atp_tbl => l_atp_tbl,
1470 p_x_line_tbl => l_line_tbl,
1471 x_return_status => l_return_status);
1472
1473 IF l_debug_level > 0 THEN
1474 oe_debug_pub.add( 'ATO: AFTER CALLING PROCESS_SET_OF_LINES' , 1 ) ;
1475 END IF;
1476
1477 x_atp_tbl := l_atp_tbl;
1478
1479 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1481 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1482 RAISE FND_API.G_EXC_ERROR;
1483 END IF;
1484
1485 IF l_debug_level > 0 THEN
1486 oe_debug_pub.add( 'EXITING SCHEDULE_ATO' , 1 ) ;
1487 END IF;
1488
1489 EXCEPTION
1490
1491 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1492
1493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1494
1495 WHEN FND_API.G_EXC_ERROR THEN
1496
1497 x_return_status := FND_API.G_RET_STS_ERROR;
1498
1499 WHEN OTHERS THEN
1500
1501 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1502 THEN
1503 OE_MSG_PUB.Add_Exc_Msg
1504 ( G_PKG_NAME
1505 , 'Schedule_ATO'
1506 );
1507 END IF;
1508
1509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1510
1511 END Schedule_ATO;
1512
1513 /* ---------------------------------------------------------------
1514 Procedure : Schedule_SMC
1515 Description: ** Currently Not Used **
1516 ---------------------------------------------------------------*/
1517 Procedure Schedule_SMC(p_group_req_rec IN OE_GRP_SCH_UTIL.Sch_Group_Rec_Type,
1518 x_return_status OUT NOCOPY VARCHAR2)
1519
1520 IS
1521 --
1522 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1523 --
1524 BEGIN
1525 null;
1526
1527 EXCEPTION
1528
1529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1530
1531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1532
1533 WHEN OTHERS THEN
1534
1535 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1536 THEN
1537 OE_MSG_PUB.Add_Exc_Msg
1538 ( G_PKG_NAME
1539 , 'Schedule_SMC'
1540 );
1541 END IF;
1542
1543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1544
1545 END Schedule_SMC;
1546
1547 /* ---------------------------------------------------------------
1548 Procedure : Query_Set_Lines
1549 Description:
1550 ---------------------------------------------------------------*/
1551 Procedure Query_Set_Lines
1552 ( p_header_id IN NUMBER,
1553 p_entity_type VARCHAR2,
1554 p_ship_set_id NUMBER,
1555 p_arrival_set_id NUMBER,
1556 p_line_id NUMBER,
1557 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type)
1558 IS
1559 l_line_rec OE_Order_PUB.Line_Rec_Type;
1560 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1561 l_set_line_tbl OE_Order_PUB.Line_Tbl_Type;
1562 l_ii_line_tbl OE_Order_PUB.Line_Tbl_Type;
1563 K NUMBER := 0;
1564 J NUMBER := 0;
1565 orig_count NUMBER;
1566 l_sales_order_id NUMBER;
1567 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1568 l_old_recursion_mode VARCHAR2(1);
1569 --
1570 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1571 --
1572 BEGIN
1573
1574 IF l_debug_level > 0 THEN
1575 oe_debug_pub.add( 'ENTERING QUERY_SET_LINES' , 1 ) ;
1576 END IF;
1577 IF l_debug_level > 0 THEN
1578 oe_debug_pub.add( 'ENTITY IS ' || P_ENTITY_TYPE , 1 ) ;
1579 END IF;
1580
1581 IF p_entity_type = OE_ORDER_SCH_UTIL.OESCH_ENTITY_SHIP_SET THEN
1582 IF l_debug_level > 0 THEN
1583 oe_debug_pub.add( 'CALLING QUERY_SET_ROWS FOR: ' || P_SHIP_SET_ID , 1 ) ;
1584 END IF;
1585
1586 OE_Set_Util.Query_Set_Rows(p_set_id => p_ship_set_id,
1587 x_line_tbl => l_set_line_tbl);
1588
1589 ELSIF p_entity_type = OE_ORDER_SCH_UTIL.OESCH_ENTITY_ARRIVAL_SET THEN
1590 IF l_debug_level > 0 THEN
1591 oe_debug_pub.add( 'CALLING QUERY_SET_ROWS FOR: ' || P_ARRIVAL_SET_ID , 1 ) ;
1592 END IF;
1593
1594 OE_Set_Util.Query_Set_Rows(p_set_id => p_arrival_set_id,
1595 x_line_tbl => l_set_line_tbl);
1596
1597 END IF;
1598
1599 IF p_entity_type = OE_ORDER_SCH_UTIL.OESCH_ENTITY_SMC
1600 THEN
1601 OE_Config_Util.Query_Options(p_top_model_line_id => p_ship_set_id,
1602 p_send_cancel_lines => 'Y',
1603 x_line_tbl => l_set_line_tbl);
1604 END IF;
1605
1606
1607 -- Loop through the queried records, assign to out table. And also explode the
1608 -- included items for model,class and kit.
1609
1610 IF l_debug_level > 0 THEN
1611 oe_debug_pub.add( 'ORIGINAL COUNT ' || L_SET_LINE_TBL.COUNT , 1 ) ;
1612 END IF;
1613
1614 -- removed extra looping to explode included items and populate reservation
1615 -- quantity, that way we can improve performance of this procedure and also
1616 -- we can fix bug 1868706.
1617
1618 J := 0;
1619 FOR I IN 1..l_set_line_tbl.count LOOP
1620
1621 IF I = 1 THEN
1622 l_sales_order_id := OE_ORDER_SCH_UTIL.Get_mtl_sales_order_id
1623 (l_set_line_tbl(1).HEADER_ID);
1624 END IF;
1625
1626 IF l_set_line_tbl(I).item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED THEN
1627 J := J + 1;
1628 x_line_tbl(J) := l_set_line_tbl(I);
1629
1630 IF x_line_tbl(J).schedule_status_code is not null THEN
1631 x_line_tbl(J).reserved_quantity :=
1632 OE_LINE_UTIL.Get_Reserved_Quantity
1633 (p_header_id => l_sales_order_id,
1634 p_line_id => x_line_tbl(J).line_id,
1635 p_org_id => x_line_tbl(J).ship_from_org_id);
1636 END IF;
1637 IF x_line_tbl(J).reserved_quantity = FND_API.G_MISS_NUM
1638 OR x_line_tbl(J).reserved_quantity IS NULL THEN
1639 x_line_tbl(J).reserved_quantity := 0;
1640 END IF;
1641
1642 -- Get the included items for every line which is a model, class, kit
1643
1644 IF (x_line_tbl(J).ato_line_id is null) AND
1645 (x_line_tbl(J).item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
1646 x_line_tbl(J).item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
1647 x_line_tbl(J).item_type_code = OE_GLOBALS.G_ITEM_KIT) THEN
1648
1649 -- Calling Process_Included_Items. This procedure
1650 -- will take care of exploding and updating the picture
1651 -- of included_items in the oe_order_lines table.
1652
1653 l_old_recursion_mode := OE_GLOBALS.G_RECURSION_MODE;
1654 -- OE_GLOBALS.G_RECURSION_MODE := 'Y';
1655
1656 IF l_debug_level > 0 THEN
1657 oe_debug_pub.add( 'CALLING PROCESS_INCLUDED_ITEMS FOR ITEM: ' || X_LINE_TBL ( J ) .INVENTORY_ITEM_ID ) ;
1658 END IF;
1659
1660 l_return_status := OE_CONFIG_UTIL.Process_Included_Items
1661 (p_line_rec => x_line_tbl(J),
1662 p_freeze => FALSE);
1663
1664 -- OE_GLOBALS.G_RECURSION_MODE := l_old_recursion_mode;
1665
1666 IF l_debug_level > 0 THEN
1667 oe_debug_pub.add( 'AFTER CALLING PROCESS_INCLUDED_ITEMS ' , 1 ) ;
1668 END IF;
1669
1670 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1672 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1673 RAISE FND_API.G_EXC_ERROR;
1674 END IF;
1675
1676 OE_Config_Util.query_included_items(
1677 p_line_id => x_line_tbl(J).line_id,
1678 p_send_cancel_lines => 'Y',
1679 x_line_tbl => l_ii_line_tbl);
1680
1681 IF l_debug_level > 0 THEN
1682 oe_debug_pub.add( 'MERGING INCLUDED ITEM TABLE WITH LINE TABLE' , 1 ) ;
1683 END IF;
1684
1685 -- Merge the Included Item table to the line table
1686 FOR K IN 1..l_ii_line_tbl.count LOOP
1687 J := J+1;
1688 x_line_tbl(J) := l_ii_line_tbl(K);
1689 IF x_line_tbl(J).schedule_status_code is not null THEN
1690 x_line_tbl(J).reserved_quantity :=
1691 OE_LINE_UTIL.Get_Reserved_Quantity
1692 (p_header_id => l_sales_order_id,
1693 p_line_id => x_line_tbl(J).line_id,
1694 p_org_id => x_line_tbl(J).ship_from_org_id);
1695 END IF;
1696 IF x_line_tbl(J).reserved_quantity = FND_API.G_MISS_NUM
1697 OR x_line_tbl(J).reserved_quantity IS NULL THEN
1698 x_line_tbl(J).reserved_quantity := 0;
1699 END IF;
1700 END LOOP; -- Merge loop
1701
1702 END IF; -- Explode included items.
1703
1704
1705
1706 END IF; -- Included Items.
1707 END LOOP;
1708
1709 IF l_debug_level > 0 THEN
1710 oe_debug_pub.add( 'SIZE OF NEW TABLE IS: ' || X_LINE_TBL.COUNT , 1 ) ;
1711 END IF;
1712
1713 IF l_debug_level > 0 THEN
1714 oe_debug_pub.add( 'EXITING QUERY_SET_LINES' , 1 ) ;
1715 END IF;
1716
1717
1718 EXCEPTION
1719
1720 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1721
1722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1723
1724 WHEN OTHERS THEN
1725
1726 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1727 THEN
1728 OE_MSG_PUB.Add_Exc_Msg
1729 ( G_PKG_NAME
1730 , 'Query_Set_Lines'
1731 );
1732 END IF;
1733
1734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1735
1736 END Query_Set_Lines;
1737
1738 /* ---------------------------------------------------------------
1739 Procedure : Validate_Set_Attributes
1740 Description: ** Currently not used **
1741 ---------------------------------------------------------------*/
1742 Procedure Validate_Set_Attributes
1743 (p_entity_type IN VARCHAR2,
1744 p_line_tbl IN OE_ORDER_PUB.line_tbl_type,
1745 x_return_status OUT NOCOPY VARCHAR2) IS
1746
1747 --
1748 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1749 --
1750 BEGIN
1751 x_return_status := FND_API.G_RET_STS_SUCCESS;
1752 END Validate_Set_Attributes;
1753
1754 /* ---------------------------------------------------------------
1755 Procedure : Schedule_Set
1756 Description:
1757 ---------------------------------------------------------------*/
1758 Procedure Schedule_Set(p_group_req_rec IN OE_GRP_SCH_UTIL.Sch_Group_Rec_Type,
1759 x_atp_tbl OUT NOCOPY OE_ATP.Atp_Tbl_Type,
1760
1761 x_return_status OUT NOCOPY VARCHAR2)
1762
1763 IS
1764 l_line_tbl OE_ORDER_PUB.line_tbl_type;
1765 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
1766 l_new_line_tbl OE_ORDER_PUB.line_tbl_type;
1767 l_included_items_tbl OE_ORDER_PUB.line_tbl_type;
1768 l_line_rec OE_ORDER_PUB.line_rec_type;
1769 l_old_line_rec OE_ORDER_PUB.line_rec_type;
1770 l_new_line_rec OE_ORDER_PUB.line_rec_type;
1771 l_atp_tbl OE_ATP.atp_tbl_type;
1772 l_Ship_From_Org_Id NUMBER := null;
1773 l_Ship_To_Org_Id NUMBER := null;
1774 l_Schedule_Ship_Date DATE := null;
1775 l_Schedule_Arrival_Date DATE := null;
1776 l_Freight_Carrier_Code VARCHAR2(30) := null;
1777 l_Shipping_Method_Code VARCHAR2(30) := null;
1778 l_shipment_priority_code VARCHAR2(30) := null;
1779 l_return_status VARCHAR2(1);
1780 l_msg_count NUMBER;
1781 l_msg_data VARCHAR2(2000);
1782 l_new_quantity NUMBER;
1783 l_old_quantity NUMBER;
1784 l_quantity NUMBER;
1785 J NUMBER;
1786 l_config_id NUMBER;
1787 l_set_rec OE_ORDER_CACHE.set_rec_type;
1788 l_set_name VARCHAR2(30);
1789 l_set_id NUMBER := null;
1790 l_action VARCHAR2(30) := Null;
1791 --
1792 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1793 --
1794 BEGIN
1795 IF l_debug_level > 0 THEN
1796 oe_debug_pub.add( 'ENTERING SCHEDULE_SET' , 1 ) ;
1797 END IF;
1798
1799 IF l_debug_level > 0 THEN
1800 oe_debug_pub.add( 'CALLING QUERY_SET_LINES' , 1 ) ;
1801 END IF;
1802
1803 Query_Set_Lines
1804 (p_header_id => p_group_req_rec.header_id,
1805 p_entity_type => p_group_req_rec.entity_type,
1806 p_ship_set_id => p_group_req_rec.ship_set_number,
1807 p_arrival_set_id => p_group_req_rec.arrival_set_number,
1808 p_line_id => p_group_req_rec.line_id,
1809 x_line_tbl => l_line_tbl);
1810
1811 IF l_debug_level > 0 THEN
1812 oe_debug_pub.add( 'AFTER CALLING QUERY_SET_LINES' , 1 ) ;
1813 END IF;
1814 IF l_debug_level > 0 THEN
1815 oe_debug_pub.add( 'COUNT IS ' || L_LINE_TBL.COUNT , 1 ) ;
1816 END IF;
1817
1818 -- Added this part of validation to fix bug 2411889.
1819
1820 IF p_group_req_rec.action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE
1821 AND p_group_req_rec.entity_type = OE_ORDER_SCH_UTIL.OESCH_ENTITY_SMC
1822 AND l_line_tbl(1).schedule_status_code is not null
1823 THEN
1824 -- This action is not allowed on an ATO configuration if the config
1825 -- item is created.
1826 BEGIN
1827 SELECT line_Id
1828 INTO l_config_id
1829 FROM OE_ORDER_LINES_ALL
1830 WHERE top_model_line_id = l_line_tbl(1).top_model_line_id
1831 AND item_type_code = 'CONFIG';
1832
1833 FND_MESSAGE.SET_NAME('ONT','OE_SCH_UNSCH_CONFIG_EXISTS');
1834 OE_MSG_PUB.Add;
1835 RAISE FND_API.G_EXC_ERROR;
1836
1837 EXCEPTION
1838 WHEN NO_DATA_FOUND THEN
1839 null;
1840 WHEN TOO_MANY_ROWS THEN
1841 FND_MESSAGE.SET_NAME('ONT','OE_SCH_UNSCH_CONFIG_EXISTS');
1842 OE_MSG_PUB.Add;
1843 RAISE FND_API.G_EXC_ERROR;
1844 END;
1845
1846 END IF; /* If action was unschedule */
1847
1848 IF l_debug_level > 0 THEN
1849 oe_debug_pub.add( 'AFTER THE VALIDATION' , 1 ) ;
1850 END IF;
1851
1852 l_old_line_tbl := l_line_tbl;
1853
1854 -- If any of the lines is a model or class, get it's included items.
1855
1856 IF l_debug_level > 0 THEN
1857 oe_debug_pub.add( 'COUNT IS ' || L_LINE_TBL.COUNT , 1 ) ;
1858 END IF;
1859
1860 FOR I IN 1..l_old_line_tbl.count LOOP
1861
1862 IF l_old_line_tbl(1).schedule_status_code is null THEN
1863
1864 l_old_line_tbl(I).schedule_ship_date := null;
1865 l_old_line_tbl(I).schedule_arrival_date := null;
1866 l_old_line_tbl(I).ship_set_id := null;
1867 l_old_line_tbl(I).arrival_set_id := null;
1868
1869 ELSE
1870 l_old_line_tbl(I).ship_set_id :=
1871 p_group_req_rec.old_ship_set_number;
1872 l_old_line_tbl(I).arrival_set_id :=
1873 p_group_req_rec.old_arrival_set_number;
1874
1875 /* commented the following lines to fix the bug 2605588
1876 IF p_group_req_rec.old_schedule_ship_date is not null THEN
1877 l_old_line_tbl(I).schedule_ship_date :=
1878 p_group_req_rec.old_schedule_ship_date;
1879 END IF;
1880 IF p_group_req_rec.old_schedule_arrival_date is not null THEN
1881 l_old_line_tbl(I).schedule_arrival_date :=
1882 p_group_req_rec.old_schedule_arrival_date;
1883 END IF;
1884 */
1885 IF p_group_req_rec.old_ship_from_org_id is not null THEN
1886
1887 l_old_line_tbl(I).ship_from_org_id :=
1888 p_group_req_rec.old_ship_from_org_id;
1889 END IF;
1890 -- Populating old dates to fix bug 2194237.
1891 /* commented the following lines to fix the bug 2605588
1892 IF p_group_req_rec.old_request_date is not null THEN
1893
1894 l_old_line_tbl(I).request_date :=
1895 p_group_req_rec.old_request_date;
1896 END IF;
1897 */
1898
1899
1900 END IF;
1901
1902 END LOOP;
1903 -- If the group action is schedule on a scheduled group, changing the same
1904 -- to reschedule, otherwise respect group action value.
1905 IF l_line_tbl.count > 0 THEN
1906
1907 IF l_line_tbl(1).schedule_status_code is not null
1908 AND p_group_req_rec.action = OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE
1909 THEN
1910 l_action := OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE;
1911 ELSE
1912
1913 IF p_group_req_rec.action is not null THEN
1914 l_action := p_group_req_rec.action;
1915 ELSE
1916 l_action := OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE;
1917 END IF;
1918
1919 END IF;
1920
1921 END IF;
1922
1923 IF p_group_req_rec.entity_type = OE_ORDER_SCH_UTIL.OESCH_ENTITY_SMC OR
1924 p_group_req_rec.entity_type = OE_ORDER_SCH_UTIL.OESCH_ENTITY_SHIP_SET THEN
1925
1926 IF p_group_req_rec.entity_type =
1927 OE_ORDER_SCH_UTIL.OESCH_ENTITY_SHIP_SET
1928 THEN
1929 l_set_rec := OE_ORDER_CACHE.Load_Set
1930 (p_group_req_rec.ship_set_number);
1931 l_set_name := l_set_rec.set_name;
1932 l_set_id := l_set_rec.set_id;
1933 ELSE
1934 l_set_name := p_group_req_rec.ship_set_number;
1935 END IF;
1936
1937 FOR I IN 1..l_line_tbl.count LOOP
1938
1939 l_line_rec := l_line_tbl(I);
1940
1941 l_line_rec.ship_set := l_set_name;
1942
1943 /* IF p_group_req_rec.action is not null THEN
1944 l_line_rec.schedule_action_code := p_group_req_rec.action;
1945 ELSE
1946 l_line_rec.schedule_action_code :=
1947 OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE;
1948 END IF;*/
1949
1950 l_line_rec.schedule_action_code := l_action;
1951
1952 IF (p_group_req_rec.ship_to_org_id is not null) THEN
1953 l_line_rec.ship_to_org_id := p_group_req_rec.ship_to_org_id;
1954 END IF;
1955
1956 IF (p_group_req_rec.ship_from_org_id is not null) THEN
1957 l_line_rec.ship_from_org_id :=
1958 p_group_req_rec.ship_from_org_id;
1959 END IF;
1960
1961 IF (p_group_req_rec.request_date is not null) THEN
1962 l_line_rec.schedule_ship_date :=
1963 p_group_req_rec.request_date;
1964 l_line_rec.request_date :=
1965 p_group_req_rec.request_date;
1966 END IF;
1967
1968 IF (p_group_req_rec.schedule_ship_date is not null) THEN
1969 l_line_rec.schedule_ship_date :=
1970 p_group_req_rec.schedule_ship_date;
1971 END IF;
1972
1973 IF (p_group_req_rec.schedule_arrival_date is not null) THEN
1974 l_line_rec.schedule_arrival_date :=
1975 p_group_req_rec.schedule_arrival_date;
1976 END IF;
1977
1978 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1979 l_line_tbl(I) := l_line_rec;
1980
1981 END LOOP;
1982 END IF; /* Ship Set */
1983
1984 IF p_group_req_rec.entity_type =
1985 OE_ORDER_SCH_UTIL.OESCH_ENTITY_ARRIVAL_SET THEN
1986
1987 l_set_rec := OE_ORDER_CACHE.Load_Set(p_group_req_rec.arrival_set_number);
1988 l_set_name := l_set_rec.set_name;
1989 l_set_id := l_set_rec.set_id;
1990
1991 FOR I IN 1..l_line_tbl.count LOOP
1992 l_line_rec := l_line_tbl(I);
1993 l_line_rec.arrival_set := l_set_name;
1994 -- l_line_rec.schedule_action_code := p_group_req_rec.action;
1995 l_line_rec.schedule_action_code := l_action;
1996
1997 IF (p_group_req_rec.ship_to_org_id is not null) THEN
1998 l_line_rec.ship_to_org_id :=
1999 p_group_req_rec.ship_to_org_id;
2000 END IF;
2001
2002 IF (p_group_req_rec.request_date is not null) THEN
2003 l_line_rec.schedule_arrival_date :=
2004 p_group_req_rec.request_date;
2005 l_line_rec.request_date :=
2006 p_group_req_rec.request_date;
2007 END IF;
2008
2009 IF (p_group_req_rec.schedule_arrival_date is not null) THEN
2010 l_line_rec.schedule_arrival_date :=
2011 p_group_req_rec.schedule_arrival_date;
2012 END IF;
2013
2014 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
2015 l_line_tbl(I) := l_line_rec;
2016
2017 END LOOP;
2018 END IF;
2019
2020
2021 -- Added a code to fix bug 2275374.
2022 FOR I IN 1..l_line_tbl.count LOOP
2023
2024 Validate_line(p_line_rec => l_line_tbl(I),
2025 p_old_line_rec => l_old_line_tbl(I),
2026 x_return_status => l_return_status);
2027
2028 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2029 IF l_debug_level > 0 THEN
2030 oe_debug_pub.add( 'ATO: AFTER VALIDATE LINE UN EXP ERROR' || L_LINE_TBL ( I ) .LINE_ID , 1 ) ;
2031 END IF;
2032 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2033 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2034 RAISE FND_API.G_EXC_ERROR;
2035 IF l_debug_level > 0 THEN
2036 oe_debug_pub.add( 'ATO: AFTER VALIDATE LINE EXP ERROR' || L_LINE_TBL ( I ) .LINE_ID , 1 ) ;
2037 END IF;
2038 END IF;
2039
2040 END LOOP;
2041 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2043 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2044 RAISE FND_API.G_EXC_ERROR;
2045 END IF;
2046
2047 IF l_debug_level > 0 THEN
2048 oe_debug_pub.add( 'CALLING PROCESS_SET_OF_LINES' , 1 ) ;
2049 END IF;
2050
2051 Process_set_of_lines( p_old_line_tbl => l_old_line_tbl,
2052 x_atp_tbl => l_atp_tbl,
2053 p_x_line_tbl => l_line_tbl,
2054 x_return_status => l_return_status);
2055
2056 IF l_debug_level > 0 THEN
2057 oe_debug_pub.add( 'AFTER CALLING PROCESS_SET_OF_LINES' , 1 ) ;
2058 END IF;
2059
2060 x_atp_tbl := l_atp_tbl;
2061 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2063 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2064 RAISE FND_API.G_EXC_ERROR;
2065 END IF;
2066
2067 IF l_set_id is not null THEN
2068 -- If scheduling set suceeded, then the result of scheduling
2069 -- have been updated to the database. Will query one of the lines
2070 -- of the set to see the change is set attributes so that we can
2071 -- update the set itself.
2072
2073 l_old_line_rec := l_old_line_tbl(1);
2074 -- l_new_line_rec := OE_Line_Util.Query_Row
2075 -- (p_line_id => l_old_line_rec.line_id);
2076
2077 OE_Line_Util.Query_Row( p_line_id => l_old_line_rec.line_id,
2078 x_line_rec => l_new_line_rec);
2079
2080 -- Update the set attributes.
2081
2082 l_ship_from_org_id := l_new_line_rec.ship_from_org_id;
2083 l_ship_to_org_id := l_new_line_rec.ship_to_org_id;
2084 l_schedule_ship_date := l_new_line_rec.schedule_ship_date;
2085 l_schedule_arrival_date := l_new_line_rec.schedule_arrival_date;
2086 l_shipping_method_code := l_new_line_rec.shipping_method_code;
2087
2088 IF l_debug_level > 0 THEN
2089 oe_debug_pub.add( 'CALLING UPDATE SET' ) ;
2090 END IF;
2091
2092 OE_Set_Util.Update_Set
2093 (p_Set_Id => l_set_id,
2094 p_Ship_From_Org_Id => l_Ship_From_Org_Id,
2095 p_Ship_To_Org_Id => l_Ship_To_Org_Id,
2096 p_Schedule_Ship_Date => l_Schedule_Ship_Date,
2097 p_Schedule_Arrival_Date => l_Schedule_Arrival_Date,
2098 p_Freight_Carrier_Code => l_Freight_Carrier_Code,
2099 p_Shipping_Method_Code => l_Shipping_Method_Code,
2100 p_shipment_priority_code => l_shipment_priority_code,
2101 X_Return_Status => l_return_status,
2102 x_msg_count => l_msg_count,
2103 x_msg_data => l_msg_data
2104 );
2105
2106 IF l_debug_level > 0 THEN
2107 oe_debug_pub.add( 'AFTER CALLING UPDATE SET' ) ;
2108 END IF;
2109
2110 END IF;
2111
2112 IF l_debug_level > 0 THEN
2113 oe_debug_pub.add( 'EXITING SCHEDULE_SET' , 1 ) ;
2114 END IF;
2115
2116 EXCEPTION
2117 WHEN FND_API.G_EXC_ERROR THEN
2118
2119 x_return_status := FND_API.G_RET_STS_ERROR;
2120
2121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2122
2123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2124
2125 WHEN OTHERS THEN
2126
2127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2128
2129 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2130 THEN
2131 OE_MSG_PUB.Add_Exc_Msg
2132 ( G_PKG_NAME
2133 , 'Schedule_Set'
2134 );
2135 END IF;
2136 END Schedule_Set;
2137
2138
2139 /* ---------------------------------------------------------------
2140 FUNCTION : Compare_set_attr
2141 Description: This function is called to compare set and line record
2142 for set attributes. This will help in avoiding additional call
2143 to MRP if the line is scheduled for a same set attributes.
2144 ---------------------------------------------------------------*/
2145 FUNCTION Compare_Set_Attr(p_set_ship_from_org_id IN NUMBER ,
2146 p_line_ship_from_org_id IN NUMBER,
2147 p_set_ship_to_org_id IN NUMBER,
2148 p_line_ship_to_org_id IN NUMBER,
2149 p_set_schedule_ship_date IN DATE,
2150 p_line_schedule_ship_date IN DATE,
2151 p_set_arrival_date IN DATE,
2152 p_line_arrival_date IN DATE,
2153 p_set_type IN VARCHAR2)
2154 RETURN BOOLEAN
2155 IS
2156
2157 --
2158 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2159 --
2160 BEGIN
2161
2162 IF l_debug_level > 0 THEN
2163 oe_debug_pub.add( 'P_SET_TYPE :' || P_SET_TYPE , 2 ) ;
2164 END IF;
2165 IF l_debug_level > 0 THEN
2166 oe_debug_pub.add( 'P_LINE_SHIP_FROM_ORG_ID :' || P_LINE_SHIP_FROM_ORG_ID , 2 ) ;
2167 END IF;
2168 IF l_debug_level > 0 THEN
2169 oe_debug_pub.add( 'P_SET_SHIP_FROM_ORG_ID :' || P_SET_SHIP_FROM_ORG_ID , 2 ) ;
2170 END IF;
2171 IF l_debug_level > 0 THEN
2172 oe_debug_pub.add( 'P_LINE_SHIP_TO_ORG_ID :' || P_LINE_SHIP_TO_ORG_ID , 2 ) ;
2173 END IF;
2174 IF l_debug_level > 0 THEN
2175 oe_debug_pub.add( 'P_SET_SHIP_TO_ORG_ID :' || P_SET_SHIP_TO_ORG_ID , 2 ) ;
2176 END IF;
2177 IF l_debug_level > 0 THEN
2178 oe_debug_pub.add( 'P_LINE_SCHEDULE_SHIP_DATE :' || P_LINE_SCHEDULE_SHIP_DATE , 2 ) ;
2179 END IF;
2180 IF l_debug_level > 0 THEN
2181 oe_debug_pub.add( 'P_SET_SCHEDULE_SHIP_DATE :' || P_SET_SCHEDULE_SHIP_DATE , 2 ) ;
2182 END IF;
2183 IF l_debug_level > 0 THEN
2184 oe_debug_pub.add( 'P_LINE_ARRIVAL_DATE :' || P_LINE_ARRIVAL_DATE , 2 ) ;
2185 END IF;
2186 IF l_debug_level > 0 THEN
2187 oe_debug_pub.add( 'P_SET_ARRIVAL_DATE :' || P_SET_ARRIVAL_DATE , 2 ) ;
2188 END IF;
2189 IF (p_set_type = 'SHIP_SET' AND
2190 p_line_ship_from_org_id = p_set_ship_from_org_id AND
2191 p_line_ship_to_org_id = p_set_Ship_To_Org_Id AND
2192 p_line_schedule_ship_date = p_set_schedule_ship_date)
2193 OR (p_set_type = 'ARRIVAL_SET' AND
2194 p_line_ship_to_org_id = p_set_ship_to_org_id AND
2195 p_line_arrival_date = p_set_arrival_date) THEN
2196
2197 RETURN TRUE;
2198
2199 END IF;
2200
2201 IF l_debug_level > 0 THEN
2202 oe_debug_pub.add( 'EXITING CAMPARE ATTR' , 3 ) ;
2203 END IF;
2204 RETURN FALSE;
2205
2206 EXCEPTION
2207
2208 WHEN OTHERS THEN
2209
2210 oe_msg_pub.add('return false from debug');
2211 RETURN FALSE;
2212
2213 END Compare_Set_Attr;
2214
2215
2216 /* ---------------------------------------------------------------
2217 Procedure : Schedule_set_of_lines
2218 Description: This procedure is exlusivley called from the Sets APIs
2219 when there is a request to schedule lines which are a
2220 part of a set.
2221 ---------------------------------------------------------------*/
2222
2223 Procedure Schedule_set_of_lines
2224 (p_old_line_tbl IN OE_ORDER_PUB.line_tbl_type,
2225 p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type,
2226 x_return_status OUT NOCOPY VARCHAR2)
2227
2228 IS
2229 l_atp_tbl OE_ATP.ATP_Tbl_Type;
2230 l_line_rec OE_ORDER_PUB.line_rec_type;
2231 l_line_tbl OE_ORDER_PUB.line_tbl_type;
2232 l_grp_line_tbl OE_ORDER_PUB.line_tbl_type;
2233 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
2234 l_ii_line_tbl OE_ORDER_PUB.line_tbl_type;
2235 l_return_status VARCHAR2(1);
2236 K NUMBER := 0;
2237 J NUMBER := 0;
2238 l_old_recursion_mode VARCHAR2(1);
2239 l_old_perform VARCHAR2(1);
2240 l_sales_order_id NUMBER;
2241 l_need_reschedule VARCHAR2(1);
2242 l_entity_type VARCHAR2(30);
2243 l_log_msg VARCHAR2(1) := 'Y';
2244 l_msg_count NUMBER;
2245 l_msg_data VARCHAR2(2000);
2246 l_option_exists NUMBER; -- Bug - 2287767
2247 l_option_search NUMBER; -- Bug - 2287767
2248 l_set_rec OE_ORDER_CACHE.set_rec_type;
2249 l_can_bypass BOOLEAN := TRUE;
2250 --
2251 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2252 --
2253 BEGIN
2254
2255 IF l_debug_level > 0 THEN
2256 oe_debug_pub.add( 'ENTERING SCHEDULE_SET_OF_LINES' , 1 ) ;
2257 END IF;
2258
2259 -- This procedure is called from the SETS api. The sets API has taken
2260 -- care of of validation that needed to be done for the lines
2261 -- to be scheduled together. i.e It has made sure that all the scheduling
2262 -- attributes are sames across the line. We will just pass the request to
2263 -- Process_Set_of_lines for scheduling. I am introducing this procedure
2264 -- in between sets and Process_set_of_lines, just for the sake that if
2265 -- we need to add some SET Api specific logic, then we can add that here.
2266
2267 -- Let's first validate the lines passed to us. We will validate
2268 -- the attributes that we need for scheduling.
2269
2270 -- To fix the bug 2431390, adding this logic to see if the line attributes
2271 -- matches with set attributes. If they matches then we can bypass scheduling
2272
2273 x_return_status := FND_API.G_RET_STS_SUCCESS;
2274
2275 IF p_x_line_tbl(1).arrival_set_id is not null OR
2276 p_x_line_tbl(1).ship_set_id IS NOT NULL THEN
2277 l_set_rec := OE_ORDER_CACHE.Load_Set
2278 (nvl(p_x_line_tbl(1).arrival_set_id,p_x_line_tbl(1).ship_set_id));
2279 ELSE
2280 l_set_rec := Null;
2281 IF p_x_line_tbl(1).arrival_set IS not null THEN
2282 l_set_rec.set_type := 'SHIP_SET';
2283 ELSIF p_x_line_tbl(1).ship_set IS NOT NULL THEN
2284 l_set_rec.set_type := 'ARRIVAL_SET';
2285 END IF;
2286 END IF;
2287
2288
2289 IF p_x_line_tbl.count = 1 THEN
2290
2291 IF p_x_line_tbl(1).schedule_status_code IS NOT NULL AND
2292 NOT OE_ORDER_SCH_UTIL.Schedule_Attribute_Changed
2293 (p_line_rec => p_x_line_tbl(1),
2294 p_old_line_rec => p_old_line_tbl(1)) AND
2295 (p_x_line_tbl(1).item_type_code = OE_GLOBALS.G_ITEM_STANDARD OR
2296 nvl(p_x_line_tbl(1).model_remnant_flag,'N') = 'Y') THEN
2297
2298 IF l_debug_level > 0 THEN
2299 oe_debug_pub.add( 'ARRIVAL_SET_ID : ' || P_X_LINE_TBL ( 1 ) .ARRIVAL_SET_ID || ':' || P_X_LINE_TBL ( 1 ) .SHIP_SET_ID , 2 ) ;
2300 END IF;
2301
2302 IF l_debug_level > 0 THEN
2303 oe_debug_pub.add( 'OLD SHIP DATE ' || P_OLD_LINE_TBL ( 1 ) .SCHEDULE_SHIP_DATE , 2 ) ;
2304 END IF;
2305 IF l_set_rec.ship_from_org_id is null
2306 OR l_set_rec.ship_from_org_id = FND_API.G_MISS_NUM THEN
2307
2308 IF l_debug_level > 0 THEN
2309 oe_debug_pub.add( 'ONLY SCHEDULED LINE IS GETTING INTO NEW SET' , 2 ) ;
2310 END IF;
2311 GOTO END_PROCESS;
2312
2313 ELSE
2314
2315 IF Compare_Set_Attr
2316 (p_set_ship_from_org_id => l_set_rec.ship_from_org_id ,
2317 p_line_ship_from_org_id => p_x_line_tbl(1).ship_from_org_id,
2318 p_set_ship_to_org_id => l_set_rec.ship_to_org_id ,
2319 p_line_ship_to_org_id => p_x_line_tbl(1).ship_to_org_id ,
2320 p_set_schedule_ship_date => l_set_rec.schedule_ship_date ,
2321 p_line_schedule_ship_date => p_x_line_tbl(1).schedule_ship_date,
2322 p_set_arrival_date => l_set_rec.schedule_arrival_date,
2323 p_line_arrival_date => p_x_line_tbl(1).schedule_arrival_date,
2324 p_set_type => l_set_rec.set_type) THEN
2325
2326 IF l_debug_level > 0 THEN
2327 oe_debug_pub.add( 'ONLY SCHEDULED LINE IS GETTING INTO OLD SET' , 2 ) ;
2328 END IF;
2329 GOTO END_PROCESS;
2330
2331 END IF; -- compare.
2332
2333 END IF; -- set date is null/not null.
2334
2335
2336 END IF; -- not null
2337
2338 ELSE
2339
2340 FOR I IN 1..p_x_line_tbl.count LOOP
2341
2342 IF p_x_line_tbl(I).schedule_status_code IS NULL OR
2343 OE_ORDER_SCH_UTIL.Schedule_Attribute_Changed
2344 (p_line_rec => p_x_line_tbl(I),
2345 p_old_line_rec => p_old_line_tbl(I)) OR
2346 (p_x_line_tbl(I).item_type_code <> OE_GLOBALS.G_ITEM_STANDARD AND
2347 nvl(p_x_line_tbl(I).model_remnant_flag,'N') = 'N') THEN
2348
2349 IF l_debug_level > 0 THEN
2350 oe_debug_pub.add( 'UNABLE TO BYPASS' , 2 ) ;
2351 END IF;
2352 l_can_bypass := FALSE;
2353 EXIT;
2354
2355 END IF;
2356
2357 IF ((l_set_rec.ship_from_org_id is not null AND
2358 l_set_rec.ship_from_org_id <> FND_API.G_MISS_NUM) AND
2359 Compare_Set_Attr
2360 (p_set_ship_from_org_id => l_set_rec.ship_from_org_id ,
2361 p_line_ship_from_org_id => p_x_line_tbl(I).ship_from_org_id,
2362 p_set_ship_to_org_id => l_set_rec.ship_to_org_id ,
2363 p_line_ship_to_org_id => p_x_line_tbl(I).ship_to_org_id ,
2364 p_set_schedule_ship_date => l_set_rec.schedule_ship_date ,
2365 p_line_schedule_ship_date => p_x_line_tbl(I).schedule_ship_date,
2366 p_set_arrival_date => l_set_rec.schedule_arrival_date,
2367 p_line_arrival_date => p_x_line_tbl(I).schedule_arrival_date,
2368 p_set_type => l_set_rec.set_type)) OR
2369 ((l_set_rec.ship_from_org_id is null OR
2370 l_set_rec.ship_from_org_id = FND_API.G_MISS_NUM) AND
2371 Compare_Set_Attr
2372 (p_set_ship_from_org_id => p_x_line_tbl(1).ship_from_org_id ,
2373 p_line_ship_from_org_id => p_x_line_tbl(I).ship_from_org_id,
2374 p_set_ship_to_org_id => p_x_line_tbl(1).ship_to_org_id ,
2375 p_line_ship_to_org_id => p_x_line_tbl(I).ship_to_org_id ,
2376 p_set_schedule_ship_date => p_x_line_tbl(1).schedule_ship_date ,
2377 p_line_schedule_ship_date => p_x_line_tbl(I).schedule_ship_date,
2378 p_set_arrival_date => p_x_line_tbl(1).schedule_arrival_date,
2379 p_line_arrival_date => p_x_line_tbl(I).schedule_arrival_date,
2380 p_set_type => l_set_rec.set_type)) THEN
2381
2382 l_can_bypass := TRUE;
2383
2384 END IF;
2385
2386
2387
2388 END LOOP;
2389
2390 IF l_can_bypass THEN
2391
2392 IF l_debug_level > 0 THEN
2393 oe_debug_pub.add( 'ALL LINES MATCH WITH SET DATES , BYPASS MRP CALL' , 2 ) ;
2394 END IF;
2395 GOTO END_PROCESS;
2396
2397 END IF;
2398 END IF; -- count
2399
2400 -- Added arrival set to if stmt to fix bug 2527834.
2401 l_need_reschedule := 'N';
2402 FOR I IN 1..p_x_line_tbl.count LOOP
2403 BEGIN
2404 IF I =1 AND
2405 (p_x_line_tbl(I).ship_set_id is not null OR
2406 p_x_line_tbl(I).arrival_set_id is not null) THEN
2407 OE_ORDER_SCH_UTIL.OESCH_PERFORM_GRP_SCHEDULING := 'N';
2408 l_log_msg := 'N';
2409 END IF;
2410 IF ((p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
2411 nvl(p_x_line_tbl(I).model_remnant_flag,'N') = 'N') OR
2412 p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_SERVICE) THEN
2413
2414 -- Service items cannot be scheduled, so we will skip them.
2415 -- Included items will be picked up by their parent, so we will
2416 -- skip them.
2417 IF l_debug_level > 0 THEN
2418 oe_debug_pub.add( 'LINE IS A SERVICE OR INCLUDED ITEM' , 1 ) ;
2419 END IF;
2420
2421 ELSIF (nvl(p_x_line_tbl(I).source_type_code,'INTERNAL') = 'INTERNAL')
2422 THEN
2423 OE_ORDER_SCH_UTIL.Validate_Line
2424 (p_line_rec => p_x_line_tbl(I),
2425 p_old_line_rec => p_old_line_tbl(I),
2426 x_return_status => l_return_status);
2427
2428 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2430 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2431 RAISE FND_API.G_EXC_ERROR;
2432 END IF;
2433
2434 IF p_x_line_tbl(I).schedule_status_code is not null THEN
2435 l_need_reschedule := 'Y';
2436 END IF;
2437
2438 K := K + 1;
2439 l_line_tbl(K) := p_x_line_tbl(I);
2440 l_old_line_tbl(K) := p_old_line_tbl(I);
2441
2442 IF (p_x_line_tbl(I).ato_line_id is null) AND
2443 nvl(p_x_line_tbl(I).model_remnant_flag,'N') <> 'Y' AND
2444 (p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
2445 p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
2446 p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_KIT) THEN
2447
2448 -- Calling Process_Included_Items. This procedure
2449 -- will take care of exploding and updating the picture
2450 -- of included_items in the oe_order_lines table.
2451
2452 IF l_debug_level > 0 THEN
2453 oe_debug_pub.add( 'CALLING PROCESS_INCLUDED_ITEMS ' , 1 ) ;
2454 END IF;
2455
2456 l_old_recursion_mode := OE_GLOBALS.G_RECURSION_MODE;
2457 -- OE_GLOBALS.G_RECURSION_MODE := 'Y';
2458
2459 -- Bug 2304287
2460 l_old_perform := OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING;
2461 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
2462
2463 l_return_status := OE_CONFIG_UTIL.Process_Included_Items
2464 (p_line_rec => p_x_line_tbl(I),
2465 p_freeze => FALSE);
2466
2467 -- OE_GLOBALS.G_RECURSION_MODE := l_old_recursion_mode;
2468 IF l_debug_level > 0 THEN
2469 oe_debug_pub.add( 'AFTER CALLING PROCESS_INCLUDED_ITEMS ' , 1 ) ;
2470 END IF;
2471
2472 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2474 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2475 RAISE FND_API.G_EXC_ERROR;
2476 END IF;
2477
2478 OE_Config_Util.Query_Included_Items
2479 (p_line_id => p_x_line_tbl(I).line_id,
2480 x_line_tbl => l_ii_line_tbl);
2481
2482 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := l_old_perform;
2483
2484 IF l_debug_level > 0 THEN
2485 oe_debug_pub.add( 'MERGING INCLUDED ITEM TABLE WITH LINE TABLE' , 1 ) ;
2486 END IF;
2487
2488 -- Merge the Included Item table to the line table
2489 FOR J IN 1..l_ii_line_tbl.count LOOP
2490 K := K+1;
2491 l_line_tbl(k) := l_ii_line_tbl(J);
2492 l_old_line_tbl(K) := l_ii_line_tbl(J);
2493 l_line_tbl(k).operation := OE_GLOBALS.G_OPR_UPDATE;
2494 l_line_tbl(k).schedule_action_code :=
2495 OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE;
2496 l_line_tbl(k).ship_set :=
2497 p_x_line_tbl(I).ship_set;
2498 l_line_tbl(k).ship_set_id :=
2499 p_x_line_tbl(I).ship_set_id;
2500 -- bug fix for 2344800.
2501 l_line_tbl(k).arrival_set :=
2502 p_x_line_tbl(I).arrival_set;
2503 l_line_tbl(k).arrival_set_id :=
2504 p_x_line_tbl(I).arrival_set_id;
2505 l_line_tbl(k).schedule_ship_date :=
2506 p_x_line_tbl(I).schedule_ship_date;
2507 l_line_tbl(k).schedule_arrival_date :=
2508 p_x_line_tbl(I).schedule_arrival_date;
2509 END LOOP;
2510 END IF;
2511 ELSE
2512 /* Line is a Externally sourced line. We will not included
2513 it in the set */
2514
2515 FND_MESSAGE.SET_NAME('ONT','OE_DS_SET_INS_FAILED');
2516 FND_MESSAGE.SET_TOKEN('LINE',p_x_line_tbl(I).line_number);
2517 OE_MSG_PUB.Add;
2518
2519 END IF;
2520
2521 EXCEPTION
2522
2523 WHEN FND_API.G_EXC_ERROR THEN
2524
2525 -- We do not want to error our the all lines due to an error
2526 -- in one of the lines. We will just not included the line
2527 -- in the set.
2528
2529 null;
2530
2531 WHEN OTHERS THEN
2532
2533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2534 END;
2535 END LOOP;
2536
2537 IF l_debug_level > 0 THEN
2538 oe_debug_pub.add( 'CALLING PROCESS_SET_OF_LINES WITH : ' || L_LINE_TBL.COUNT , 1 ) ;
2539 END IF;
2540
2541 -- Added this part of code to populate reserved qty to
2542 -- fix bug 1874169.
2543
2544 FOR I IN 1..l_line_tbl.count LOOP
2545
2546 IF I = 1 THEN
2547
2548 l_sales_order_id := OE_ORDER_SCH_UTIL.Get_mtl_sales_order_id
2549 (l_line_tbl(1).HEADER_ID);
2550 END IF;
2551 -- If any of the lines are previously scheduled, then pass
2552 -- action as reschedule to MRP.
2553
2554 IF l_need_reschedule = 'Y' THEN
2555 l_line_tbl(I).schedule_action_code :=
2556 OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE;
2557 END IF;
2558
2559 IF l_line_tbl(I).schedule_status_code is not null THEN
2560 l_line_tbl(I).reserved_quantity :=
2561 OE_LINE_UTIL.Get_Reserved_Quantity
2562 (p_header_id => l_sales_order_id,
2563 p_line_id => l_line_tbl(I).line_id,
2564 p_org_id => l_line_tbl(I).ship_from_org_id);
2565 l_old_line_tbl(I).reserved_quantity := l_line_tbl(I).reserved_quantity;
2566 END IF;
2567 IF l_line_tbl(I).reserved_quantity = FND_API.G_MISS_NUM
2568 OR l_line_tbl(I).reserved_quantity IS NULL THEN
2569 l_liNe_tbl(I).reserved_quantity := 0;
2570 END IF;
2571 IF l_old_line_tbl(I).reserved_quantity = FND_API.G_MISS_NUM
2572 OR l_old_line_tbl(I).reserved_quantity IS NULL THEN
2573 l_old_line_tbl(I).reserved_quantity := 0;
2574 END IF;
2575 END LOOP;
2576
2577
2578 IF l_line_tbl.count > 0 THEN
2579 Process_set_of_lines(p_old_line_tbl => l_old_line_tbl,
2580 p_write_to_db => FND_API.G_FALSE,
2581 x_atp_tbl => l_atp_tbl,
2582 p_x_line_tbl => l_line_tbl,
2583 p_log_msg => l_log_msg,
2584 x_return_status => x_return_status);
2585
2586 END IF;
2587
2588 -- Added code to fix bug 1899651.
2589 IF l_debug_level > 0 THEN
2590 oe_debug_pub.add( 'AFTER PROCESS SET_OF_LINES ' || X_RETURN_STATUS , 1 ) ;
2591 END IF;
2592 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2593
2594 IF l_debug_level > 0 THEN
2595 oe_debug_pub.add( 'WELCOME TO AUTO PUSH GROUP ' || L_LINE_TBL.COUNT , 1 ) ;
2596 END IF;
2597 IF fnd_profile.value('ONT_AUTO_PUSH_GRP_DATE') = 'Y'
2598 AND (l_line_tbl(1).arrival_set_id is not null OR
2599 l_line_tbl(1).ship_set_id is not null) THEN
2600
2601 OE_ORDER_SCH_UTIL.OESCH_PERFORM_GRP_SCHEDULING := 'Y';
2602
2603 -- Derive the entity id.
2604 IF l_line_tbl(1).arrival_set_id is not null THEN
2605
2606 l_entity_type := OE_ORDER_SCH_UTIL.OESCH_ENTITY_ARRIVAL_SET;
2607
2608 ELSIF l_line_tbl(1).ship_set_id is not null THEN
2609
2610 l_entity_type := OE_ORDER_SCH_UTIL.OESCH_ENTITY_SHIP_SET;
2611
2612 END IF;
2613
2614 -- Call query set lines to query old lines from db.
2615
2616 Query_Set_Lines
2617 (p_header_id => Null,
2618 p_entity_type => l_entity_type,
2619 p_ship_set_id => l_line_tbl(1).ship_set_id,
2620 p_arrival_set_id => l_line_tbl(1).arrival_set_id,
2621 p_line_id => Null,
2622 x_line_tbl => l_grp_line_tbl);
2623
2624 IF l_debug_level > 0 THEN
2625 oe_debug_pub.add( 'AFTER QUERY SETS :' || L_GRP_LINE_TBL.COUNT , 1 ) ;
2626 END IF;
2627 IF l_grp_line_tbl.count > 0 THEN
2628 IF l_need_reschedule = 'N' THEN
2629
2630 -- These line are getting scheduled first time.
2631 -- Since we are passing lines which are scheduled earlier
2632 -- change schedule action code.
2633
2634 FOR I in 1..l_line_tbl.count LOOP
2635
2636 l_line_tbl(I).schedule_action_code :=
2637 OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE;
2638
2639 END LOOP;
2640
2641 END IF;
2642
2643 J := l_line_tbl.count;
2644
2645 FOR I in 1..l_grp_line_tbl.count LOOP
2646
2647 -- J := J + 1;
2648
2649 -- Populate correct schedule action code
2650 -- Populate old and new line table.
2651 -- Search for Option in the Line table ,if it does not exists
2652 -- then add it. Bug - 2287767
2653 l_option_exists := 0;
2654 FOR l_option_search in 1..J LOOP
2655 IF l_grp_line_tbl(I).line_id =
2656 l_line_tbl(l_option_search).line_id THEN
2657 l_option_exists := 1;
2658 IF l_debug_level > 0 THEN
2659 oe_debug_pub.add( 'OPTION ALREADY EXISTS IN THE LINE TABLE' ) ;
2660 END IF;
2661 EXIT;
2662 END IF;
2663 END LOOP;
2664
2665 IF l_option_exists = 0 THEN -- Bug - 2287767
2666 --2319050.
2667 J := J + 1;
2668 l_line_tbl(J) := l_grp_line_tbl(I);
2669 l_line_tbl(J).schedule_action_code :=
2670 OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE;
2671
2672 l_line_tbl(J).operation := OE_GLOBALS.G_OPR_UPDATE;
2673
2674 l_old_line_tbl(J) := l_grp_line_tbl(I);
2675 END IF;
2676 END LOOP;
2677
2678 IF l_debug_level > 0 THEN
2679 oe_debug_pub.add( 'BEFORE CALLING PROCESS SET ' || L_LINE_TBL.COUNT , 1 ) ;
2680 END IF;
2681 Process_set_of_lines(p_old_line_tbl => l_old_line_tbl,
2682 p_write_to_db => FND_API.G_FALSE,
2683 x_atp_tbl => l_atp_tbl,
2684 p_x_line_tbl => l_line_tbl,
2685 x_return_status => x_return_status);
2686
2687 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2688
2689
2690 OE_Set_Util.Update_Set
2691 (p_Set_Id => nvl(l_line_tbl(1).arrival_set_id,
2692 l_line_tbl(1).ship_set_id),
2693 p_Ship_From_Org_Id => l_line_tbl(1).Ship_From_Org_Id,
2694 p_Ship_To_Org_Id => l_line_tbl(1).Ship_To_Org_Id,
2695 p_Schedule_Ship_Date => l_line_tbl(1).Schedule_Ship_Date,
2696 p_Schedule_Arrival_Date => l_line_tbl(1).Schedule_Arrival_Date,
2697 p_Freight_Carrier_Code => l_line_tbl(1).Freight_Carrier_Code,
2698 p_Shipping_Method_Code => l_line_tbl(1).Shipping_Method_Code,
2699 p_shipment_priority_code => l_line_tbl(1).shipment_priority_code,
2700 X_Return_Status => x_return_status,
2701 x_msg_count => l_msg_count,
2702 x_msg_data => l_msg_data
2703 );
2704
2705 IF l_debug_level > 0 THEN
2706 oe_debug_pub.add( 'AFTER CALLING UPDATE SET' ) ;
2707 END IF;
2708
2709 END IF;
2710 END IF; -- l_grp_count.
2711 ELSE
2712 IF l_log_msg = 'N' THEN
2713 FND_MESSAGE.SET_NAME('ONT','OE_SCH_GROUP_MEMBER_FAILED');
2714 OE_MSG_PUB.Add;
2715 END IF;
2716 END IF; -- Push group
2717 END IF; -- Return Status is error.
2718
2719 OE_ORDER_SCH_UTIL.OESCH_PERFORM_GRP_SCHEDULING := 'Y';
2720 p_x_line_tbl := l_line_tbl;
2721 <<END_PROCESS>>
2722 null;
2723 IF l_debug_level > 0 THEN
2724 oe_debug_pub.add( 'EXITING SCHEDULE_SET_OF_LINES' , 1 ) ;
2725 END IF;
2726
2727 EXCEPTION
2728 WHEN FND_API.G_EXC_ERROR THEN
2729
2730 OE_ORDER_SCH_UTIL.OESCH_PERFORM_GRP_SCHEDULING := 'Y';
2731 x_return_status := FND_API.G_RET_STS_ERROR;
2732
2733 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2734
2735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2736 OE_ORDER_SCH_UTIL.OESCH_PERFORM_GRP_SCHEDULING := 'Y';
2737
2738 WHEN OTHERS THEN
2739
2740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2741 OE_ORDER_SCH_UTIL.OESCH_PERFORM_GRP_SCHEDULING := 'Y';
2742
2743 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2744 THEN
2745 OE_MSG_PUB.Add_Exc_Msg
2746 ( G_PKG_NAME
2747 , 'Schedule_set_of_lines'
2748 );
2749 END IF;
2750
2751 END Schedule_set_of_lines;
2752
2753 /* ---------------------------------------------------------------
2754 Procedure: Process_set_of_lines
2755 Description:
2756 ---------------------------------------------------------------*/
2757
2758 Procedure Process_set_of_lines
2759 ( p_old_line_tbl IN OE_ORDER_PUB.line_tbl_type
2760 := OE_ORDER_PUB.G_MISS_LINE_TBL,
2761 p_write_to_db IN VARCHAR2 := FND_API.G_TRUE,
2762 x_atp_tbl OUT NOCOPY OE_ATP.Atp_Tbl_Type,
2763
2764 p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type,
2765 p_log_msg IN VARCHAR2 := 'Y',
2766 x_return_status OUT NOCOPY VARCHAR2)
2767
2768 IS
2769 l_line_tbl OE_ORDER_PUB.line_tbl_type;
2770 l_x_line_tbl OE_ORDER_PUB.line_tbl_type;
2771 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
2772 l_out_line_tbl OE_ORDER_PUB.line_tbl_type;
2773 l_mrp_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
2774 l_out_mtp_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
2775 l_out_atp_table OE_ATP.ATP_Tbl_Type;
2776 l_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
2777 l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
2778 l_atp_details MRP_ATP_PUB.ATP_Details_Typ;
2779 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2780 l_msg_data VARCHAR2(2000);
2781 l_msg_count NUMBER;
2782 mrp_msg_data VARCHAR2(200);
2783 l_session_id NUMBER := 0;
2784 l_schedule_action_code VARCHAR2(30);
2785 l_avail_to_reserve NUMBER;
2786 l_on_hand_qty NUMBER;
2787 l_msg_index NUMBER;
2788 l_reset_action VARCHAR2(1) := 'N';
2789 l_schedule_level VARCHAR2(30) := null;
2790
2791 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
2792 l_query_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
2793 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
2794 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
2795 l_quantity_reserved NUMBER;
2796 l_qty_to_reserve NUMBER;
2797 l_rsv_id NUMBER;
2798 l_buffer VARCHAR2(2000);
2799
2800 l_old_line_tbl1 OE_ORDER_PUB.line_tbl_type;
2801 l_out_line_tbl1 OE_ORDER_PUB.line_tbl_type;
2802 K NUMBER := 0;
2803 M NUMBER := 0;
2804 N NUMBER := 0;
2805 l_process_requests BOOLEAN;
2806 TYPE char1 IS TABLE OF VARCHAR2(1) index by Binary_integer;
2807 l_re_reserve_flag char1;
2808 l_reservable_type NUMBER;
2809 -- added by fabdi 03/May/2001
2810 l_process_flag VARCHAR2(1) := FND_API.G_FALSE;
2811 -- end fabdi
2812 l_sales_order_id NUMBER;
2813 l_x_error_code NUMBER;
2814 l_lock_records VARCHAR2(1);
2815 l_sort_by_req_date NUMBER;
2816 l_count NUMBER;
2817
2818 -- subinventory
2819 l_revision_code NUMBER;
2820 l_lot_code NUMBER;
2821 l_serial_code NUMBER;
2822
2823
2824 --
2825 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2826 --
2827 BEGIN
2828
2829 IF l_debug_level > 0 THEN
2830 oe_debug_pub.add( 'ENTERING PROCESS_SET_OF_LINES' , 1 ) ;
2831 END IF;
2832
2833 l_line_tbl := p_x_line_tbl;
2834 l_old_line_tbl := p_old_line_tbl;
2835
2836 -- If the action is unreserve, we should unreserve what is reserved
2837
2838 IF l_line_tbl(1).schedule_action_code =
2839 OE_ORDER_SCH_UTIL.OESCH_ACT_UNRESERVE OR
2840 l_line_tbl(1).schedule_action_code =
2841 OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE THEN
2842
2843 FOR J IN 1..l_line_tbl.count LOOP
2844 IF l_old_line_tbl(J).reserved_quantity > 0 AND
2845 nvl(l_line_tbl(J).shipping_interfaced_flag, 'N') = 'N' THEN
2846
2847 /*OE_ORDER_SCH_UTIL.Unreserve_Line
2848 ( p_line_rec => l_old_line_tbl(J)
2849 , p_quantity_to_unreserve => l_old_line_tbl(J).reserved_quantity
2850 , x_return_status => l_return_status); */ -- INVCONV TO COPMPILE
2851
2852 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2854 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2855 RAISE FND_API.G_EXC_ERROR;
2856 END IF;
2857 ELSE
2858 -- Added for Bug-2319081
2859 IF l_line_tbl(J).schedule_action_code =
2860 OE_ORDER_SCH_UTIL.OESCH_ACT_UNRESERVE
2861 AND nvl(l_line_tbl(J).shipping_interfaced_flag, 'N') = 'Y' THEN
2862
2863 FND_MESSAGE.SET_NAME('ONT','OE_SCH_UNRSV_NOT_ALLOWED');
2864 OE_MSG_PUB.Add;
2865 END IF;
2866 END IF;
2867 END LOOP;
2868 l_out_line_tbl := l_line_tbl;
2869 IF l_line_tbl(1).schedule_action_code =
2870 OE_ORDER_SCH_UTIL.OESCH_ACT_UNRESERVE
2871 THEN
2872 -- To fix bug 1895086.
2873 l_schedule_action_code := l_line_tbl(1).schedule_action_code;
2874 goto end_processing;
2875 END IF;
2876 END IF;
2877
2878 -- If the action is reschedule, we should unreserve what is reserved
2879 -- for the line before redemanding.
2880
2881 IF l_line_tbl(1).schedule_action_code =
2882 OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE THEN
2883 FOR J IN 1..l_line_tbl.count LOOP
2884
2885 l_re_reserve_flag(j) := 'N';
2886 IF l_line_tbl(J).item_type_code <> OE_GLOBALS.G_ITEM_CONFIG AND
2887 nvl(l_line_tbl(J).shipping_interfaced_flag, 'N') = 'N' THEN
2888 -- We do not want to unreserve config item while rescheduling.
2889 -- Unreserve only if there is any change in the warehouse.
2890 -- Inventory changes are not allowed for model/option/classes.
2891 -- For subinventory changes system will not log group request
2892 -- Modified code to fix bug 1894284.
2893 IF NOT OE_GLOBALS.Equal(l_line_tbl(j).ship_from_org_id,
2894 l_old_line_tbl(j).ship_from_org_id) THEN
2895 IF l_old_line_tbl(J).reserved_quantity > 0 THEN
2896 /*OE_ORDER_SCH_UTIL.Unreserve_Line
2897 ( p_line_rec => l_old_line_tbl(J)
2898 , p_quantity_to_unreserve => l_old_line_tbl(J).reserved_quantity
2899 , x_return_status => l_return_status); */ -- INVCONV
2900
2901 l_line_tbl(j).reserved_quantity := 0;
2902 l_re_reserve_flag(j) := 'Y';
2903 IF l_debug_level > 0 THEN
2904 oe_debug_pub.add( 'L_RE_RESERVE_FLAG :' || J || L_RE_RESERVE_FLAG ( J ) , 1 ) ;
2905 END IF;
2906
2907 END IF; -- Reserved qty.
2908 END IF; -- ship from change.
2909
2910 -- If group request is logged due to change in the ordered qty.
2911 -- Will compare the reserved qty and ordered qty and if the reserved
2912 -- qty is higher than ordered qty, we will unreserve the difference.
2913 -- If the ordered qty is increased, we will not increase the
2914 -- reservation.
2915
2916 IF l_line_tbl(j).ordered_quantity <
2917 nvl(l_line_tbl(j).reserved_quantity,0) THEN
2918
2919 IF l_debug_level > 0 THEN
2920 oe_debug_pub.add( 'CALLING UNRESERVE FOR DIFFERENCE' , 1 ) ;
2921 END IF;
2922 /*OE_ORDER_SCH_UTIL.Unreserve_Line
2923 ( p_line_rec => l_line_tbl(J)
2924 , p_quantity_to_unreserve => l_line_tbl(J).reserved_quantity -
2925 l_line_tbl(j).ordered_quantity
2926 , x_return_status => l_return_status); */ -- INVCONV
2927
2928 l_line_tbl(j).reserved_quantity := l_line_tbl(j).ordered_quantity;
2929 END IF;
2930 END IF;
2931
2932
2933 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2935 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2936 RAISE FND_API.G_EXC_ERROR;
2937 END IF;
2938
2939 END LOOP;
2940 l_out_line_tbl := l_line_tbl;
2941 END IF;
2942
2943 l_schedule_action_code := l_line_tbl(1).schedule_action_code;
2944 IF l_debug_level > 0 THEN
2945 oe_debug_pub.add( 'SCHEDULE ACTION IS : ' || L_SCHEDULE_ACTION_CODE , 1 ) ;
2946 END IF;
2947
2948
2949 -- When User reserves the group lines without scheduling, changes the status to
2950 -- Schedule and call MRP and change the status back to reserve and call inv.
2951
2952 IF l_line_tbl(1).schedule_action_code =
2953 OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE
2954 AND l_line_tbl(1).schedule_status_code is null THEN
2955
2956 IF l_debug_level > 0 THEN
2957 oe_debug_pub.add( ' GRP SCHEDULE ACTION IS : ' || L_SCHEDULE_ACTION_CODE , 1 ) ;
2958 END IF;
2959 l_reset_action := 'Y';
2960
2961 FOR J IN 1..l_line_tbl.count LOOP
2962
2963 l_line_tbl(j).schedule_action_code := OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE;
2964
2965 END LOOP;
2966 END IF; -- Reserve.
2967
2968 -- Do not call scheduling is action is reserve and line already scheduled.
2969
2970 IF l_schedule_action_code = OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE
2971 AND l_line_tbl(1).schedule_status_code is NOT NULL
2972 THEN
2973
2974 Null;
2975
2976 ELSE
2977
2978 OE_ORDER_SCH_UTIL.Load_MRP_Request
2979 ( p_line_tbl => l_line_tbl
2980 , p_old_line_tbl => l_old_line_tbl
2981 , x_atp_table => l_mrp_atp_rec);
2982
2983 IF l_debug_level > 0 THEN
2984 oe_debug_pub.add( 'AFTER CALLING LOAD_MRP_REQUEST' , 1 ) ;
2985 END IF;
2986
2987 -- Added if stmt to fix bug 2162690.
2988 IF l_mrp_atp_rec.error_code.count > 0 THEN
2989 l_session_id := OE_ORDER_SCH_UTIL.Get_Session_Id;
2990
2991 IF l_debug_level > 0 THEN
2992 oe_debug_pub.add( 'CALLING MRPS ATP API ' || L_SESSION_ID , 1 ) ;
2993 END IF;
2994
2995 MRP_ATP_PUB.Call_ATP
2996 ( p_session_id => l_session_id
2997 , p_atp_rec => l_mrp_atp_rec
2998 , x_atp_rec => l_out_mtp_atp_rec
2999 , x_atp_supply_demand => l_atp_supply_demand
3000 , x_atp_period => l_atp_period
3001 , x_atp_details => l_atp_details
3002 , x_return_status => l_return_status
3003 , x_msg_data => mrp_msg_data
3004 , x_msg_count => l_msg_count);
3005
3006 IF l_debug_level > 0 THEN
3007 oe_debug_pub.add( 'AFTER CALLING MRPS ATP API: ' || L_RETURN_STATUS , 1 ) ;
3008 END IF;
3009
3010 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3012 END IF;
3013
3014 IF l_debug_level > 0 THEN
3015 oe_debug_pub.add( 'GRP1: CALLING LOAD_RESULTS' , 1 ) ;
3016 END IF;
3017
3018 OE_ORDER_SCH_UTIL.Load_Results
3019 ( p_atp_table => l_out_mtp_atp_rec
3020 , p_x_line_tbl => l_line_tbl
3021 , x_atp_tbl => l_out_atp_table
3022 , x_return_status => l_return_status);
3023
3024 IF l_debug_level > 0 THEN
3025 oe_debug_pub.add( 'GRP1: AFTER CALLING LOAD_RESULTS: ' || L_RETURN_STATUS , 1 ) ;
3026 END IF;
3027
3028 x_atp_tbl := l_out_atp_table;
3029 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3031 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3032 RAISE FND_API.G_EXC_ERROR;
3033 END IF;
3034 END IF; -- MRP count.
3035
3036 -- Code has been moved up to fix bug 2408551.
3037
3038 IF l_debug_level > 0 THEN
3039 oe_debug_pub.add( '----- AFTER PRINTING OUT NOCOPY TABLE -----' , 1 ) ;
3040
3041 END IF;
3042 IF l_debug_level > 0 THEN
3043 oe_debug_pub.add( ' ' , 1 ) ;
3044 END IF;
3045
3046
3047 IF NOT OE_GLOBALS.Equal(l_schedule_action_code,
3048 OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK) AND
3049 NOT OE_GLOBALS.Equal(l_schedule_action_code,
3050 OE_ORDER_SCH_UTIL.OESCH_ACT_UNRESERVE) AND
3051 p_write_to_db = FND_API.G_TRUE THEN
3052
3053 -- Turning off Perform Scheduling Flag Before calling
3054 -- this procedure since this procedure is calling Process Order
3055 -- which in turn will call scheduling if this flag is not turned off.
3056
3057 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
3058
3059 IF l_debug_level > 0 THEN
3060 oe_debug_pub.add( 'L_LINE_TBL.SHIP_FROM_ORG_ID' || L_LINE_TBL ( 1 ) .SHIP_FROM_ORG_ID ) ;
3061 END IF;
3062 IF l_debug_level > 0 THEN
3063 oe_debug_pub.add( 'L_LINE_TBL.SCHEDULE_SHIP_DATE' || L_LINE_TBL ( 1 ) .SCHEDULE_SHIP_DATE ) ;
3064 END IF;
3065 IF l_debug_level > 0 THEN
3066 oe_debug_pub.add( 'L_LINE_TBL.SCHEDULE_STATUS_CODE' || L_LINE_TBL ( 1 ) .SCHEDULE_STATUS_CODE ) ;
3067 END IF;
3068 IF l_debug_level > 0 THEN
3069 oe_debug_pub.add( 'L_LINE_TBL.INVENTORY_ITEM_ID' || L_LINE_TBL ( 1 ) .INVENTORY_ITEM_ID ) ;
3070 END IF;
3071
3072 -- Set the status of the lines to update
3073
3074 K := 1;
3075 FOR I IN 1..l_line_tbl.count LOOP
3076
3077 IF nvl(l_line_tbl(I).open_flag,'Y') = 'Y' THEN
3078
3079 l_out_line_tbl1(K) := l_line_tbl(I);
3080 l_out_line_tbl1(K).operation := OE_GLOBALS.G_OPR_UPDATE;
3081 l_old_line_tbl1(K) := p_old_line_tbl(I);
3082
3083 K := K + 1;
3084
3085 END IF;
3086
3087 END LOOP;
3088
3089
3090 -- Setting g_set_recursive_flag related flag to TRUE, since
3091 -- we do not want any set related changes to take place in this
3092 -- call to process order.
3093
3094 OE_SET_UTIL.g_set_recursive_flag := TRUE;
3095 IF l_out_line_tbl1.count >= 1 THEN
3096
3097 IF l_debug_level > 0 THEN
3098 oe_debug_pub.add( 'NOW CALLING OE_ORDER_SCH_UTIL.UPDATE_LINE_RECORD' , 1 ) ;
3099 END IF;
3100
3101 OE_ORDER_SCH_UTIL.Update_line_record
3102 ( p_line_tbl => l_old_line_tbl1
3103 , p_x_new_line_tbl => l_out_line_tbl1
3104 , p_write_to_db => p_write_to_db
3105 , p_recursive_call => FND_API.G_FALSE
3106 , x_return_status => l_return_status);
3107
3108 IF l_debug_level > 0 THEN
3109 oe_debug_pub.add( 'AFTER CALLING UPDATE_LINE_RECORD: ' || L_RETURN_STATUS , 1 ) ;
3110 END IF;
3111
3112 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3114 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3115 RAISE FND_API.G_EXC_ERROR;
3116 END IF;
3117
3118 -- Do not process delayed requests if this was a recursive
3119 -- call (e.g. from oe_line_util.pre_write_process)
3120 l_process_requests := TRUE;
3121
3122 OE_Order_PVT.Process_Requests_And_Notify
3123 ( p_process_requests => l_process_requests
3124 , p_notify => TRUE
3125 , p_line_tbl => l_out_line_tbl1
3126 , p_old_line_tbl => l_old_line_tbl1
3127 , x_return_status => l_return_status
3128 );
3129
3130 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3132 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3133 RAISE FND_API.G_EXC_ERROR;
3134 END IF;
3135
3136
3137 END IF;
3138
3139
3140 -- Resetting g_set_recursive_flag related flag to FALSE
3141
3142 OE_SET_UTIL.g_set_recursive_flag := FALSE;
3143
3144 -- Fix for bug 2898623
3145 M := l_out_line_tbl1.FIRST;
3146 N := l_line_tbl.FIRST;
3147
3148 WHILE M IS NOT NULL LOOP
3149 BEGIN
3150 WHILE N IS NOT NULL LOOP
3151 BEGIN
3152 IF l_line_tbl(N).line_id = l_out_line_tbl1(M).line_id THEN
3153 l_line_tbl(N) := l_out_line_tbl1(M);
3154 ELSE
3155 EXIT;
3156 END IF;
3157 END;
3158 N := l_line_tbl.NEXT(N);
3159 END LOOP;
3160 END;
3161 M := l_out_line_tbl1.NEXT(M);
3162 END LOOP;
3163 -- Fix for bug 2898623 ends
3164
3165 -- Resetting the Flag Back
3166
3167 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
3168
3169 END IF; -- Call po
3170
3171 -- No reservation is required.
3172 -- Modified if stmt and added atp_check to fix bug 1936990.
3173 IF l_line_tbl(1).schedule_action_code =
3174 OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE OR
3175 l_line_tbl(1).schedule_action_code =
3176 OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK
3177
3178 THEN
3179 goto end_processing;
3180 END IF;
3181
3182 END IF; -- Reserve MRP.
3183
3184 -- If user action is reserve then reset the action code in line level.
3185 -- If the user action is Reserve, then call reservation for reserving the line.
3186
3187
3188 IF l_debug_level > 0 THEN
3189 oe_debug_pub.add( ' GRP RES CALL INV ' , 1 ) ;
3190 END IF;
3191
3192 FOR J IN 1..l_line_tbl.count LOOP
3193
3194 IF l_debug_level > 0 THEN
3195 oe_debug_pub.add( 'LINE TO RESERVE ' || L_LINE_TBL ( J ) .LINE_ID , 1 ) ;
3196 END IF;
3197
3198 IF l_reset_action = 'Y' THEN
3199
3200 l_line_tbl(j).schedule_action_code := l_schedule_action_code;
3201
3202 END IF;
3203
3204 IF l_line_tbl(j).shippable_flag = 'Y'
3205 AND l_line_tbl(j).ordered_quantity > 0
3206 AND l_line_tbl(j).Item_type_code <> OE_GLOBALS.G_ITEM_CONFIG THEN
3207
3208 SELECT RESERVABLE_TYPE
3209 INTO l_reservable_type
3210 FROM MTL_SYSTEM_ITEMS
3211 WHERE INVENTORY_ITEM_ID = l_line_tbl(j).inventory_item_id
3212 AND ORGANIZATION_ID = l_line_tbl(j).ship_from_org_id;
3213
3214 IF l_reservable_type = 1 THEN
3215
3216 l_schedule_level :=
3217 OE_ORDER_SCH_UTIL.Get_Scheduling_Level(l_line_tbl(j).header_id,
3218 l_line_tbl(j).line_type_id);
3219
3220
3221 IF l_debug_level > 0 THEN
3222 oe_debug_pub.add( 'ACTION :' || L_LINE_TBL ( J ) .SCHEDULE_ACTION_CODE , 1 ) ;
3223 END IF;
3224 IF l_debug_level > 0 THEN
3225 oe_debug_pub.add( 'QTY :' || L_LINE_TBL ( J ) .RESERVED_QUANTITY , 1 ) ;
3226 END IF;
3227
3228 IF ((l_line_tbl(j).schedule_action_code =
3229 OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE
3230 OR ((l_schedule_level = OE_ORDER_SCH_UTIL.SCH_LEVEL_THREE OR
3231 l_schedule_level is NULL) AND
3232 OE_ORDER_SCH_UTIL.Within_Rsv_Time_Fence
3233 (l_line_tbl(j).schedule_ship_date)))
3234 AND nvl(l_line_tbl(j).reserved_quantity,0) = 0)
3235
3236 OR (l_line_tbl(j).schedule_action_code =
3237 OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE AND
3238 Nvl(l_re_reserve_flag(j),'N') = 'Y')
3239 THEN
3240 --newsub check if item is under lot/revision/serial control
3241 IF l_line_tbl(j).subinventory is not null
3242 AND l_line_tbl(j).subinventory <> FND_API.G_MISS_CHAR THEN
3243 BEGIN
3244 SELECT revision_qty_control_code, lot_control_code,
3245 serial_number_control_code
3246 INTO l_revision_code, l_lot_code, l_serial_code
3247 FROM mtl_system_items
3248 WHERE inventory_item_id = l_line_tbl(j).inventory_item_id
3249 AND organization_id = l_line_tbl(j).ship_from_org_id;
3250
3251 EXCEPTION
3252 WHEN OTHERS THEN
3253 l_return_status := FND_API.G_RET_STS_ERROR;
3254 fnd_message.set_name('ONT', 'OE_INVALID_ITEM_WHSE');
3255 OE_MSG_PUB.Add;
3256 END;
3257
3258
3259 IF l_revision_code = 2 OR l_lot_code = 2 THEN
3260 -- 2 == YES
3261 fnd_message.set_name('ONT', 'OE_SUBINV_NOT_ALLOWED');
3262 OE_MSG_PUB.Add;
3263 IF l_line_tbl(j).schedule_action_code =
3264 OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE THEN
3265 l_return_status := FND_API.G_RET_STS_ERROR;
3266 RAISE FND_API.G_EXC_ERROR;
3267 ELSE
3268 -- We should not fail the transaction, if we are
3269 -- not able to reserve the line.
3270 l_line_tbl(j).reserved_quantity := null;
3271 l_return_status := FND_API.G_RET_STS_SUCCESS;
3272 GOTO NO_RESERVATION;
3273 END IF;
3274 END IF;
3275 END IF;
3276 --end newsub
3277
3278
3279 IF l_debug_level > 0 THEN
3280 oe_debug_pub.add( 'GRP RES: RESERVED_QUANTITY ' || L_LINE_TBL ( J ) .ORDERED_QUANTITY , 1 ) ;
3281 END IF;
3282
3283 l_line_tbl(j).reserved_quantity := l_line_tbl(j).ordered_quantity;
3284
3285 OE_ORDER_SCH_UTIL.Load_INV_Request
3286 ( p_line_rec => l_line_tbl(j)
3287 , p_quantity_to_reserve => l_line_tbl(j).ordered_quantity
3288 , x_reservation_rec => l_reservation_rec);
3289
3290 IF l_debug_level > 0 THEN
3291 oe_debug_pub.add( 'GRP RES AFTER CALLING LOAD INV' , 1 ) ;
3292 END IF;
3293
3294 -- Call INV with action = RESERVE
3295
3296 inv_reservation_pub.create_reservation
3297 ( p_api_version_number => 1.0
3298 , p_init_msg_lst => FND_API.G_TRUE
3299 , x_return_status => l_return_status
3300 , x_msg_count => l_msg_count
3301 , x_msg_data => l_msg_data
3302 , p_rsv_rec => l_reservation_rec
3303 , p_serial_number => l_dummy_sn
3304 , x_serial_number => l_dummy_sn
3305 , p_partial_reservation_flag => FND_API.G_FALSE
3306 , p_force_reservation_flag => FND_API.G_FALSE
3307 , p_validation_flag => FND_API.G_TRUE
3308 , x_quantity_reserved => l_quantity_reserved
3309 , x_reservation_id => l_rsv_id
3310 );
3311
3312 IF l_debug_level > 0 THEN
3313 oe_debug_pub.add( 'GRP RES AFTER CALLING CREATE RESERVATION' || L_RETURN_STATUS , 1 ) ;
3314 END IF;
3315
3316 -- Bug No:2097933
3317 -- If the Reservation was succesfull we set
3318 -- the package variable to "Y".
3319 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3320 OE_ORDER_SCH_UTIL.OESCH_PERFORMED_RESERVATION := 'Y';
3321 END IF;
3322
3323 IF l_debug_level > 0 THEN
3324 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
3325 END IF;
3326
3327 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3328 l_line_tbl(j).reserved_quantity := null;
3329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3330 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3331 IF l_msg_data is not null THEN
3332 fnd_message.set_encoded(l_msg_data);
3333 l_buffer := fnd_message.get;
3334 oe_msg_pub.add_text(p_message_text => l_buffer);
3335 END IF;
3336
3337 l_line_tbl(j).reserved_quantity := null;
3338
3339 IF l_line_tbl(j).schedule_action_code =
3340 OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE
3341 THEN
3342 RAISE FND_API.G_EXC_ERROR;
3343
3344 ELSE
3345
3346
3347 -- We should not fail the transaction, if we are
3348 -- not able to reserve the line.
3349 l_return_status := FND_API.G_RET_STS_SUCCESS;
3350
3351 END IF;
3352
3353 END IF; -- return status
3354 IF l_debug_level > 0 THEN
3355 oe_debug_pub.add( 'GRP RES AFTER CALLING INVS CREATE_RESERVATION' , 1 ) ;
3356 END IF;
3357
3358
3359 -- l_line_tbl(j).reserved_quantity := l_quantity_reserved;
3360 END IF; -- Reservable condition.
3361 END IF; -- l_reservable_type
3362 END IF; -- Check for shippable flag.
3363
3364
3365 -- Adding code to fix bug 2126165.
3366
3367 IF l_line_tbl(j).schedule_action_code =
3368 OE_ORDER_SCH_UTIL.OESCH_ACT_RESCHEDULE
3369 AND NOT OE_GLOBALS.Equal(l_line_tbl(j).schedule_ship_date,
3370 l_old_line_tbl(j).schedule_ship_date)
3371 AND l_old_line_tbl(j).reserved_quantity > 0
3372 AND l_old_line_tbl(j).reserved_quantity <> FND_API.G_MISS_NUM
3373 AND Nvl(l_re_reserve_flag(j),'N') = 'N'
3374 THEN
3375
3376
3377 l_query_rsv_rec.reservation_id := fnd_api.g_miss_num;
3378
3379 l_sales_order_id
3380 := OE_ORDER_SCH_UTIL.Get_mtl_sales_order_id(l_old_line_tbl(j).header_id);
3381 l_query_rsv_rec.demand_source_header_id := l_sales_order_id;
3382 l_query_rsv_rec.demand_source_line_id := l_old_line_tbl(j).line_id;
3383
3384 -- 02-jun-2000 mpetrosi added org_id to query_reservation start
3385 l_query_rsv_rec.organization_id := l_old_line_tbl(j).ship_from_org_id;
3386 -- 02-jun-2000 mpetrosi added org_id to query_reservation end
3387
3388
3389 IF l_debug_level > 0 THEN
3390 oe_debug_pub.add( 'RSCH: CALLING INVS QUERY_RESERVATION ' , 1 ) ;
3391 END IF;
3392
3393 inv_reservation_pub.query_reservation
3394 ( p_api_version_number => 1.0
3395 , p_init_msg_lst => fnd_api.g_true
3396 , x_return_status => l_return_status
3397 , x_msg_count => l_msg_count
3398 , x_msg_data => l_msg_data
3399 , p_query_input => l_query_rsv_rec
3400 , x_mtl_reservation_tbl => l_rsv_tbl
3401 , x_mtl_reservation_tbl_count => l_count
3402 , x_error_code => l_x_error_code
3403 , p_lock_records => l_lock_records
3404 , p_sort_by_req_date => l_sort_by_req_date
3405 );
3406
3407 IF l_debug_level > 0 THEN
3408 oe_debug_pub.add( 'AFTER CALLING INVS QUERY_RESERVATION: ' || L_RETURN_STATUS , 1 ) ;
3409 END IF;
3410
3411 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3413 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3414 RAISE FND_API.G_EXC_ERROR;
3415 END IF;
3416
3417 IF l_debug_level > 0 THEN
3418 oe_debug_pub.add( 'RESERVATION RECORD COUNT IS: ' || L_RSV_TBL.COUNT , 1 ) ;
3419 END IF;
3420
3421 -- Let's get the total reserved_quantity
3422 FOR M IN 1..l_rsv_tbl.count LOOP
3423
3424 l_reservation_rec := l_rsv_tbl(M);
3425 l_reservation_rec.requirement_date := l_line_tbl(j).schedule_ship_date;
3426
3427 IF l_debug_level > 0 THEN
3428 oe_debug_pub.add( 'RSCH: CALLING INVS UPDATE RESERVATION ' , 1 ) ;
3429 END IF;
3430 inv_reservation_pub.update_reservation
3431 ( p_api_version_number => 1.0
3432 , p_init_msg_lst => fnd_api.g_true
3433 , x_return_status => l_return_status
3434 , x_msg_count => l_msg_count
3435 , x_msg_data => l_msg_data
3436 , p_original_rsv_rec => l_rsv_tbl(M)
3437 , p_to_rsv_rec => l_reservation_rec
3438 , p_original_serial_number => l_dummy_sn -- no serial contorl
3439 , p_to_serial_number => l_dummy_sn -- no serial control
3440 , p_validation_flag => fnd_api.g_true
3441 );
3442
3443 IF l_debug_level > 0 THEN
3444 oe_debug_pub.add( 'AFTER CALLING INVS UPDATE_RESERVATION: ' || L_RETURN_STATUS , 1 ) ;
3445 END IF;
3446
3447 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3449 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3450 IF l_msg_data is not null THEN
3451 fnd_message.set_encoded(l_msg_data);
3452 l_buffer := fnd_message.get;
3453 oe_msg_pub.add_text(p_message_text => l_buffer);
3454 IF l_debug_level > 0 THEN
3455 oe_debug_pub.add( 'ERROR : '|| L_BUFFER , 1 ) ;
3456 END IF;
3457 END IF;
3458 RAISE FND_API.G_EXC_ERROR;
3459 END IF;
3460 END LOOP;
3461
3462 END IF;
3463
3464 -- End code for bug 2126165.
3465 <<NO_RESERVATION>>
3466 null;
3467 END LOOP;
3468
3469 l_reset_action := 'N';
3470
3471 -- Moved up to fix bug 1936990.
3472 <<end_processing>>
3473 -- Get the on-hand and available_to_reserve quantities if you are
3474 -- performing ATP.
3475
3476 IF l_line_tbl(1).schedule_action_code =
3477 OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
3478
3479 FOR K IN 1..l_out_atp_table.count LOOP
3480
3481 IF l_debug_level > 0 THEN
3482 oe_debug_pub.add( 'CALLING QUERY_QTY_TREE' , 1 ) ;
3483 END IF;
3484
3485 -- added fabdi 03/May/2001
3486 -- added p_line_id - AS DEFAULT for process atp to work
3487
3488 -- passing additional parameter p_sch_date to fix bug 2111470.
3489 /*OE_ORDER_SCH_UTIL.Query_Qty_Tree -- INVCONV - COMMENTED OUT FOR COMPILES
3490
3491 (p_org_id => l_out_atp_table(K).ship_from_org_id,
3492 p_item_id => l_out_atp_table(K).inventory_item_id,
3493 p_line_id => l_out_atp_table(K).line_id,
3494 p_sch_date =>
3495 nvl(l_out_atp_table(K).group_available_date,
3496 l_out_atp_table(K).ordered_qty_Available_Date),
3497 x_on_hand_qty => l_on_hand_qty,
3498 x_avail_to_reserve => l_avail_to_reserve); */
3499 -- added fabdi 03/May/2001
3500 IF NOT INV_GMI_RSV_BRANCH.Process_Branch(p_organization_id => l_out_atp_table(K).ship_from_org_id)
3501 THEN
3502 l_process_flag := FND_API.G_FALSE;
3503 ELSE
3504 l_process_flag := FND_API.G_TRUE;
3505 END IF;
3506 IF l_process_flag = FND_API.G_TRUE
3507 THEN
3508
3509 l_out_atp_table(K).on_hand_qty := l_on_hand_qty;
3510 l_out_atp_table(K).available_to_reserve := l_avail_to_reserve;
3511 l_out_atp_table(K).QTY_ON_REQUEST_DATE := l_avail_to_reserve; -- This is Available field in ATP
3512
3513 IF l_debug_level > 0 THEN
3514 oe_debug_pub.add( 'L_ON_HAND_QTY ' || L_ON_HAND_QTY ) ;
3515 END IF;
3516 IF l_debug_level > 0 THEN
3517 oe_debug_pub.add( 'L_AVAIL_TO_RESERVE ' || L_AVAIL_TO_RESERVE ) ;
3518 END IF;
3519 IF l_debug_level > 0 THEN
3520 oe_debug_pub.add( 'ORDERED_QUANTITY ' || L_AVAIL_TO_RESERVE ) ;
3521 END IF;
3522 else
3523 l_out_atp_table(K).on_hand_qty := l_on_hand_qty;
3524 l_out_atp_table(K).available_to_reserve := l_avail_to_reserve;
3525 IF l_debug_level > 0 THEN
3526 oe_debug_pub.add( 'L_ON_HAND_QTY' || L_ON_HAND_QTY ) ;
3527 END IF;
3528 IF l_debug_level > 0 THEN
3529 oe_debug_pub.add( 'L_AVAIL_TO_RESERVE' || L_AVAIL_TO_RESERVE ) ;
3530 END IF;
3531 end if;
3532 -- end fabdi
3533
3534 END LOOP;
3535
3536 END IF;
3537
3538 FOR I IN 1..l_line_tbl.count LOOP
3539
3540
3541 IF l_debug_level > 0 THEN
3542 oe_debug_pub.add( 'LINE_ID :' || L_LINE_TBL ( I ) .LINE_ID , 20 ) ;
3543 END IF;
3544 IF l_debug_level > 0 THEN
3545 oe_debug_pub.add( 'ORDER_QUANTITY :' || L_LINE_TBL ( I ) .ORDERED_QUANTITY , 20 ) ;
3546 END IF;
3547 IF l_debug_level > 0 THEN
3548 oe_debug_pub.add( 'SCHEDULE_SHIP_DATE :' || L_LINE_TBL ( I ) .SCHEDULE_SHIP_DATE , 20 ) ;
3549 END IF;
3550 IF l_debug_level > 0 THEN
3551 oe_debug_pub.add( 'SCHEDULE_ARRIVAL_DATE :' || L_LINE_TBL ( I ) .SCHEDULE_ARRIVAL_DATE , 20 ) ;
3552 END IF;
3553 IF l_debug_level > 0 THEN
3554 oe_debug_pub.add( 'SHIP_FROM_ORG_ID :' || L_LINE_TBL ( I ) .SHIP_FROM_ORG_ID , 20 ) ;
3555 END IF;
3556 IF l_debug_level > 0 THEN
3557 oe_debug_pub.add( 'SCHEDULE_STATUS_CODE :' || L_LINE_TBL ( I ) .SCHEDULE_STATUS_CODE , 20 ) ;
3558 END IF;
3559 IF l_debug_level > 0 THEN
3560 oe_debug_pub.add( 'RESERVED_QUANTITY :' || L_LINE_TBL ( I ) .RESERVED_QUANTITY , 20 ) ;
3561 END IF;
3562 IF l_debug_level > 0 THEN
3563 oe_debug_pub.add( ' ' , 20 ) ;
3564 END IF;
3565
3566 END LOOP;
3567 /*
3568 oe_debug_pub.add('----- After Printing OUT Table -----',1);
3569 oe_debug_pub.add(' ',1);
3570
3571 IF NOT OE_GLOBALS.Equal(l_schedule_action_code,
3572 OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK) AND
3573 NOT OE_GLOBALS.Equal(l_schedule_action_code,
3574 OE_ORDER_SCH_UTIL.OESCH_ACT_UNRESERVE) AND
3575 p_write_to_db = FND_API.G_TRUE THEN
3576
3577 -- Turning off Perform Scheduling Flag Before calling
3578 -- this procedure since this procedure is calling Process Order
3579 -- which in turn will call scheduling if this flag is not turned off.
3580
3581 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
3582
3583 oe_debug_pub.add('l_line_tbl.ship_from_org_id' ||
3584 l_line_tbl(1).ship_from_org_id);
3585 oe_debug_pub.add('l_line_tbl.schedule_ship_date' ||
3586 l_line_tbl(1).schedule_ship_date);
3587 oe_debug_pub.add('l_line_tbl.schedule_status_code' ||
3588 l_line_tbl(1).schedule_status_code);
3589 oe_debug_pub.add('l_line_tbl.inventory_item_id' ||
3590 l_line_tbl(1).inventory_item_id);
3591
3592 -- Set the status of the lines to update
3593
3594 K := 1;
3595 FOR I IN 1..l_line_tbl.count LOOP
3596
3597 IF nvl(l_line_tbl(I).open_flag,'Y') = 'Y' THEN
3598
3599 l_out_line_tbl1(K) := l_line_tbl(I);
3600 l_out_line_tbl1(K).operation := OE_GLOBALS.G_OPR_UPDATE;
3601 l_old_line_tbl1(K) := p_old_line_tbl(I);
3602
3603 K := K + 1;
3604
3605 END IF;
3606
3607 END LOOP;
3608
3609
3610 -- Setting g_set_recursive_flag related flag to TRUE, since
3611 -- we do not want any set related changes to take place in this
3612 -- call to process order.
3613
3614 OE_SET_UTIL.g_set_recursive_flag := TRUE;
3615 IF l_out_line_tbl1.count >= 1 THEN
3616
3617 oe_debug_pub.add('Now Calling OE_ORDER_SCH_UTIL.Update_line_record',1);
3618
3619 OE_ORDER_SCH_UTIL.Update_line_record
3620 ( p_line_tbl => l_old_line_tbl1
3621 , p_x_new_line_tbl => l_out_line_tbl1
3622 , p_write_to_db => p_write_to_db
3623 , p_recursive_call => FND_API.G_FALSE
3624 , x_return_status => l_return_status);
3625
3626 oe_debug_pub.add('After Calling Update_line_record: ' ||
3627 l_return_status,1);
3628
3629 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3631 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3632 RAISE FND_API.G_EXC_ERROR;
3633 END IF;
3634
3635 -- Do not process delayed requests if this was a recursive
3636 -- call (e.g. from oe_line_util.pre_write_process)
3637 l_process_requests := TRUE;
3638
3639 OE_Order_PVT.Process_Requests_And_Notify
3640 ( p_process_requests => l_process_requests
3641 , p_notify => TRUE
3642 , p_line_tbl => l_out_line_tbl1
3643 , p_old_line_tbl => l_old_line_tbl1
3644 , x_return_status => l_return_status
3645 );
3646
3647 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3649 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3650 RAISE FND_API.G_EXC_ERROR;
3651 END IF;
3652
3653
3654 END IF;
3655
3656
3657 -- Resetting g_set_recursive_flag related flag to FALSE
3658
3659 OE_SET_UTIL.g_set_recursive_flag := FALSE;
3660
3661 p_x_line_tbl := l_out_line_tbl1;
3662 -- Resetting the Flag Back
3663
3664 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
3665
3666
3667 ELSE
3668 oe_debug_pub.add('Assigning l_out_table to x_line_tbl',1);
3669 p_x_line_tbl := l_line_tbl;
3670 END IF;
3671 */
3672 p_x_line_tbl := l_line_tbl;
3673 x_atp_tbl := l_out_atp_table;
3674 x_return_status := l_return_status;
3675
3676 IF l_debug_level > 0 THEN
3677 oe_debug_pub.add( 'PASSING BACK ' || L_OUT_ATP_TABLE.COUNT || ' LINES' || P_X_LINE_TBL.COUNT , 1 ) ;
3678 END IF;
3679 IF l_debug_level > 0 THEN
3680 oe_debug_pub.add( 'EXITING PROCESS_SET_OF_LINES WITH: ' || L_RETURN_STATUS , 1 ) ;
3681 END IF;
3682
3683 EXCEPTION
3684 WHEN FND_API.G_EXC_ERROR THEN
3685
3686 IF p_log_msg = 'Y' THEN
3687 FND_MESSAGE.SET_NAME('ONT','OE_SCH_GROUP_MEMBER_FAILED');
3688 OE_MSG_PUB.Add;
3689 END IF;
3690
3691 x_return_status := FND_API.G_RET_STS_ERROR;
3692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3693
3694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3695
3696 WHEN OTHERS THEN
3697
3698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3699
3700 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3701 THEN
3702 OE_MSG_PUB.Add_Exc_Msg
3703 ( G_PKG_NAME
3704 , 'Process_set_of_lines'
3705 );
3706 END IF;
3707
3708 END Process_set_of_lines;
3709
3710 /* ---------------------------------------------------------------
3711 Procedure : Line_In_Sch_Group
3712 Description: This procedure is written for the validation
3713 template Schedule Group. It will return 1 if the
3714 line is line has belongs to any of the scheduling groups.
3715
3716 ---------------------------------------------------------------*/
3717
3718 Procedure Line_In_Sch_Group
3719 (p_application_id in number,
3720 p_entity_short_name in varchar2,
3721 p_validation_entity_short_name in varchar2,
3722 p_validation_tmplt_short_name in varchar2,
3723 p_record_set_short_name in varchar2,
3724 p_scope in varchar2,
3725 x_result out nocopy number)
3726
3727 IS
3728 --
3729 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3730 --
3731 BEGIN
3732 x_result := 0;
3733 END;
3734
3735 /* -------------------------------------------------------------------
3736 FUNCTION: Query Lines is used to get all the lines we need
3737 to scheduled an order. We are not using the line utility
3738 because we want to sort the table in a particular format.
3739 ------------------------------------------------------------------- */
3740 PROCEDURE Query_Lines
3741 ( p_header_id IN NUMBER,
3742 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
3743 )
3744 IS
3745 l_line_rec OE_Order_PUB.Line_Rec_Type;
3746 --l_line_tbl OE_Order_PUB.Line_Tbl_Type;
3747 l_org_id NUMBER;
3748
3749 CURSOR l_line_csr IS
3750 SELECT LINE_ID
3751 FROM OE_ORDER_LINES_ALL
3752 WHERE
3753 HEADER_ID = p_header_id
3754 AND item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED
3755 ORDER BY arrival_set_id,ship_set_id,line_number,shipment_number,nvl(option_number,-1);
3756
3757 -- Added nvl stmt to fix bug 1937881.
3758 --
3759 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3760 --
3761 BEGIN
3762
3763 IF l_debug_level > 0 THEN
3764 oe_debug_pub.add( 'ENTERING QUERY LINES' , 1 ) ;
3765 END IF;
3766
3767 -- Loop over fetched records
3768
3769 FOR c2 IN l_line_csr LOOP
3770
3771 OE_Line_Util.Query_Row( p_line_id => c2.line_id
3772 ,x_line_rec => l_line_rec );
3773
3774 x_line_tbl(x_line_tbl.COUNT + 1) := l_line_rec;
3775
3776 --Removed extra assignmnet to fix bug 1612399.
3777
3778 END LOOP;
3779
3780
3781 -- Return fetched table
3782 IF l_debug_level > 0 THEN
3783 oe_debug_pub.add( 'EXITING OE_GRP_SCH_UTIL.QUERY_LINES' , 1 ) ;
3784 END IF;
3785
3786 -- RETURN l_line_tbl;
3787
3788 EXCEPTION
3789
3790 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3791
3792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3793
3794 WHEN OTHERS THEN
3795
3796 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3797 THEN
3798 OE_MSG_PUB.Add_Exc_Msg
3799 ( G_PKG_NAME
3800 , 'Query_Rows'
3801 );
3802 END IF;
3803
3804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3805
3806 END Query_Lines;
3807
3808 /* ---------------------------------------------------------------
3809 Procedure :Validate_Group_Request
3810 This procedure is written for the validation of group request
3811 that is passed in.
3812
3813 ---------------------------------------------------------------*/
3814 Procedure Validate_Group_Request
3815 (p_group_req_rec IN OE_GRP_SCH_UTIL.Sch_Group_Rec_Type
3816 ,x_return_status OUT NOCOPY VARCHAR2)
3817
3818 IS
3819 l_return_status VARCHAR2(1);
3820 --
3821 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3822 --
3823 BEGIN
3824
3825 IF l_debug_level > 0 THEN
3826 oe_debug_pub.add( 'ENTERING VALIDATE_GROUP_REQUEST' , 1 ) ;
3827 END IF;
3828
3829 -- Currently, we do not allow unscheduling a line, if it belongs
3830 -- to a user defined set (ship or arrival set).
3831
3832 IF (p_group_req_rec.entity_type =
3833 OE_ORDER_SCH_UTIL.OESCH_ENTITY_SHIP_SET) OR
3834 (p_group_req_rec.entity_type =
3835 OE_ORDER_SCH_UTIL.OESCH_ENTITY_ARRIVAL_SET) THEN
3836
3837 IF (p_group_req_rec.action =
3838 OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE ) OR
3839 (p_group_req_rec.action =
3840 OE_ORDER_SCH_UTIL.OESCH_ACT_UNDEMAND ) THEN
3841
3842 -- You cannot unschedule a set. This is an invalid action on
3843 -- the group.
3844
3845 FND_MESSAGE.SET_NAME('ONT','OE_SCH_CANNOT_UNSCH_SET');
3846 OE_MSG_PUB.Add;
3847 l_return_status := FND_API.G_RET_STS_ERROR;
3848
3849 END IF;
3850 END IF;
3851
3852 x_return_status := l_return_status;
3853 IF l_debug_level > 0 THEN
3854 oe_debug_pub.add( 'EXITING VALIDATE_GROUP_REQUEST WITH: ' || L_RETURN_STATUS , 1 ) ;
3855 END IF;
3856 IF l_debug_level > 0 THEN
3857 oe_debug_pub.add( ' ' , 1 ) ;
3858 END IF;
3859
3860 END Validate_Group_Request;
3861
3862 /* ---------------------------------------------------------------
3863 Procedure :Validate_Warehouse
3864 This procedure is written for the validation of group request
3865 that is passed in.
3866
3867 ---------------------------------------------------------------*/
3868 Procedure Validate_Warehouse
3869 (p_line_tbl IN OE_ORDER_PUB.line_tbl_type
3870 ,p_ship_from_org_id IN NUMBER
3871 ,x_return_status OUT NOCOPY VARCHAR2)
3872
3873 IS
3874 l_line_number NUMBER;
3875 l_dummy VARCHAR2(10);
3876 --
3877 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3878 --
3879 BEGIN
3880 FOR I in 1..p_line_tbl.count LOOP
3881
3882 l_line_number := p_line_tbl(I).line_number;
3883
3884 SELECT 'VALID'
3885 INTO l_dummy
3886 FROM MTL_SYSTEM_ITEMS
3887 WHERE INVENTORY_ITEM_ID = p_line_tbl(I).inventory_item_id
3888 AND ORGANIZATION_ID = p_ship_from_org_id;
3889
3890 END LOOP;
3891
3892 x_return_status := FND_API.G_RET_STS_SUCCESS;
3893
3894 EXCEPTION
3895 WHEN NO_DATA_FOUND THEN
3896 x_return_status := FND_API.G_RET_STS_ERROR;
3897
3898 FND_MESSAGE.SET_NAME('ONT','OE_SCH_GRP_WHSE_INVALID');
3899 OE_MSG_PUB.Add;
3900
3901 WHEN OTHERS THEN
3902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3903
3904 FND_MESSAGE.SET_NAME('ONT','OE_SCH_GRP_WHSE_INVALID');
3905 FND_MESSAGE.SET_TOKEN('LINE',l_line_number);
3906 OE_MSG_PUB.Add;
3907
3908 END Validate_Warehouse;
3909
3910 /* ---------------------------------------------------------------
3911 Procedure :Sch_Multi_selected_lines
3912 This procedure is called when lines are multi-selected and
3913 scheduling action is performed.
3914
3915 ---------------------------------------------------------------*/
3916 Procedure Sch_Multi_selected_lines
3917 (p_line_list IN VARCHAR2,
3918 p_line_count IN NUMBER,
3919 p_action IN VARCHAR2,
3920 x_atp_tbl OUT NOCOPY OE_ATP.Atp_Tbl_Type,
3921
3922 x_return_status OUT NOCOPY VARCHAR2,
3923
3924 x_msg_count OUT NOCOPY NUMBER,
3925
3926 x_msg_data OUT NOCOPY VARCHAR2)
3927
3928 IS
3929 j Integer;
3930 initial Integer;
3931 nextpos Integer;
3932 l_record_ids VARCHAR2(2000) := p_line_list || ',';
3933 l_line_id NUMBER;
3934 l_line_tbl OE_ORDER_PUB.line_tbl_type;
3935 l_option_tbl OE_ORDER_PUB.line_tbl_type;
3936 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
3937 l_new_line_tbl OE_ORDER_PUB.line_tbl_type;
3938 l_line_rec OE_ORDER_PUB.line_rec_type;
3939 l_group_req_rec OE_GRP_SCH_UTIL.Sch_Group_Rec_Type;
3940 l_out_atp_tbl OE_ATP.atp_tbl_type;
3941 l_return_status VARCHAR2(1);
3942 --l_out_line_rec OE_ORDER_PUB.line_rec_type;
3943 l_old_line_rec OE_ORDER_PUB.line_rec_type;
3944 atp_count NUMBER;
3945 line_count NUMBER := 0;
3946 option_count NUMBER;
3947 l_option_exists NUMBER; -- Bug-2287767
3948 l_option_search NUMBER; -- Bug-2287767
3949 I NUMBER;
3950 l_out_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3951
3952 processed_arrival_set number_arr;
3953 processed_ship_set number_arr;
3954 processed_pto_model number_arr;
3955 processed_pto_smc number_arr;
3956 processed_ato number_arr;
3957
3958 arrival_set_count NUMBER := 0;
3959 ship_set_count NUMBER := 0;
3960 pto_smc_count NUMBER := 0;
3961 ato_count NUMBER := 0;
3962
3963 l_top_model_line_id NUMBER;
3964 pto_model_count NUMBER := 0;
3965
3966 --
3967 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3968 --
3969 BEGIN
3970 IF l_debug_level > 0 THEN
3971 oe_debug_pub.add( 'GG: ENTERING SCH_MULTI_SELECTED_LINES' , 1 ) ;
3972 END IF;
3973
3974 IF l_debug_level > 0 THEN
3975 oe_debug_pub.add( 'LINE COUNT IS: ' || P_LINE_COUNT , 1 ) ;
3976 END IF;
3977 IF l_debug_level > 0 THEN
3978 oe_debug_pub.add( 'ACTION IS: ' || P_ACTION , 1 ) ;
3979 END IF;
3980
3981 /* Bug :2222360 */
3982 IF p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
3983 IF l_debug_level > 0 THEN
3984 oe_debug_pub.add( 'INSIDE ATP CHECK SAVEPOINT' ) ;
3985 END IF;
3986 SAVEPOINT ATP_CHECK;
3987 ELSE
3988 SAVEPOINT SCH_ACTION;
3989 END IF;
3990
3991 j := 1;
3992 initial := 1;
3993 nextpos := INSTR(l_record_ids,',',1,j) ;
3994
3995 FOR I IN 1..p_line_count LOOP
3996
3997
3998 l_line_id := to_number(substr(l_record_ids,initial, nextpos-initial));
3999 IF l_debug_level > 0 THEN
4000 oe_debug_pub.add( 'LINE_ID : ' || L_LINE_ID , 1 ) ;
4001 END IF;
4002
4003 initial := nextpos + 1.0;
4004 j := j + 1.0;
4005 nextpos := INSTR(l_record_ids,',',1,j) ;
4006
4007 -- l_line_rec := OE_LINE_UTIL.Query_Row
4008 -- ( p_line_id => l_line_id
4009 -- );
4010
4011 OE_Line_Util.Query_Row
4012 ( p_line_id => l_line_id,
4013 x_line_rec => l_line_rec);
4014
4015
4016 IF l_line_rec.ato_line_id = l_line_rec.line_id AND
4017 l_line_rec.top_model_line_id <> l_line_rec.line_id THEN
4018
4019 -- We want to skip the options under a Model, if the model
4020 -- has been selected for scheduling
4021
4022 IF pto_model_count > 0 THEN
4023 FOR P in 1..pto_model_count LOOP
4024 IF l_line_rec.top_model_line_id =
4025 processed_pto_model(pto_model_count)
4026 THEN
4027 goto end_loop_1;
4028 END IF;
4029 END LOOP;
4030 END IF;
4031
4032 -- Could not find the parent line processed.
4033 line_count := line_count + 1;
4034 l_line_tbl(line_count) := l_line_rec;
4035 ELSE
4036 line_count := line_count + 1;
4037 l_line_tbl(line_count) := l_line_rec;
4038 END IF;
4039
4040 IF l_line_tbl(line_count).item_type_code = OE_GLOBALS.G_ITEM_MODEL AND
4041 l_line_tbl(line_count).ato_line_id is null AND
4042 nvl(l_line_tbl(line_count).ship_model_complete_flag,'N') = 'N' THEN
4043
4044 l_top_model_line_id := l_line_tbl(line_count).top_model_line_id;
4045
4046 pto_model_count := pto_model_count + 1;
4047 processed_pto_model(pto_model_count) :=
4048 l_line_rec.top_model_line_id;
4049
4050 -- When a model is selected for scheduling, all the options
4051 -- under it should also get scheduled. Query all the options
4052 -- needed for the scheduling.
4053
4054 -- l_option_tbl := OE_CONFIG_UTIL.Query_Options
4055 -- (l_top_model_line_id);
4056
4057 OE_Config_Util.Query_Options
4058 (p_top_model_line_id => l_top_model_line_id,
4059 x_line_tbl => l_option_tbl);
4060
4061 FOR option_count in 1..l_option_tbl.count LOOP
4062 IF l_option_tbl(option_count).item_type_code <>
4063 OE_GLOBALS.G_ITEM_INCLUDED THEN
4064 IF l_option_tbl(option_count).line_id <>
4065 l_top_model_line_id THEN
4066 -- Search for Option in the Line table, if the option does
4067 -- not Exist in the table then add it to the table.
4068 -- Bug - 2287767.
4069 l_option_exists := 0;
4070 FOR l_option_search in 1..line_count LOOP
4071 IF l_option_tbl(option_count).line_id =
4072 l_line_tbl(l_option_search).line_id THEN
4073 l_option_exists := 1;
4074 IF l_debug_level > 0 THEN
4075 oe_debug_pub.add( 'OPTION ALREADY EXISTS IN LINE TABLE' ) ;
4076 END IF;
4077 EXIT;
4078 END IF;
4079 END LOOP;
4080 IF l_option_exists = 0 THEN -- Bug - 2287767
4081 line_count := line_count + 1;
4082 l_line_tbl(line_count) := l_option_tbl(option_count);
4083 END IF;
4084 END IF;
4085 END IF;
4086 END LOOP;
4087
4088 END IF;
4089 <<end_loop_1>>
4090 null;
4091 END LOOP;
4092
4093 -- l_line_tbl consists the lines we need to schedule.
4094
4095 atp_count := 1;
4096
4097 IF l_debug_level > 0 THEN
4098 oe_debug_pub.add( 'LINES TO PROCESS : ' || L_LINE_TBL.COUNT , 1 ) ;
4099 END IF;
4100
4101 FOR line_index IN 1..l_line_tbl.count
4102 LOOP
4103 BEGIN
4104
4105 SAVEPOINT SCH_LINE;
4106 -- Added code to fix bug 1778701.
4107 IF l_line_tbl(line_index).line_category_code = 'RETURN' THEN
4108
4109 -- Ignore return lines.
4110 goto end_loop;
4111
4112 END IF;
4113
4114
4115 l_line_rec := l_line_tbl(line_index);
4116 l_old_line_rec := l_line_rec;
4117
4118 IF p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE OR
4119 p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE OR
4120 p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK OR
4121 (p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE AND
4122 l_line_rec.schedule_status_code is null) THEN
4123
4124 -- We want to skip a line belonging to a set only if the action
4125 -- is SCHEDULE,ATP or UNSCHEDULE. We do not want to skip if the
4126 -- action is RESERVE or UNRESERVE, since then, we will treat the
4127 -- line independently.
4128
4129 IF l_line_rec.arrival_set_id is not null THEN
4130 -- Check to see if this set is already processed. If yes, then
4131 -- go to the end.
4132 arrival_set_count := processed_arrival_set.count;
4133 IF arrival_set_count > 0 THEN
4134 FOR c IN 1..processed_arrival_set.count LOOP
4135 IF l_line_rec.arrival_set_id = processed_arrival_set(c)
4136 THEN
4137 -- This set has been processed.
4138 goto end_loop;
4139 END IF;
4140 END LOOP;
4141 END IF;
4142
4143 -- If the line could not find it's arrival set id in the
4144 -- processed_arrival_set, it has not been processed. Let's add the
4145 -- set_id to the table and process the set.
4146
4147 arrival_set_count := Arrival_set_count + 1;
4148 processed_arrival_set(arrival_set_count) :=
4149 l_line_rec.arrival_set_id;
4150 ELSIF l_line_rec.ship_set_id is not null THEN
4151 -- Check to see if this set is already processed. If yes, then
4152 -- go to the end.
4153 ship_set_count := processed_ship_set.count;
4154 IF ship_set_count > 0 THEN
4155 FOR c IN 1..processed_ship_set.count LOOP
4156 IF l_line_rec.ship_set_id = processed_ship_set(c)
4157 THEN
4158 -- This set has been processed.
4159 goto end_loop;
4160 END IF;
4161 END LOOP;
4162 END IF;
4163
4164 -- If the line could not find it's ship set id in the
4165 -- processed_ship_set, it has not been processed. Let's add the
4166 -- set_id to the table and process the set.
4167
4168 ship_set_count := ship_set_count + 1;
4169 processed_ship_set(ship_set_count) := l_line_rec.ship_set_id;
4170 ELSIF nvl(l_line_rec.ship_model_complete_flag,'N') = 'Y' THEN
4171 -- Check to see if this set is already processed. If yes, then
4172 -- go to the end.
4173 pto_smc_count := processed_pto_smc.count;
4174 IF pto_smc_count > 0 THEN
4175 FOR c IN 1..processed_pto_smc.count LOOP
4176 IF l_line_rec.top_model_line_id = processed_pto_smc(c)
4177 THEN
4178 -- This set has been processed.
4179 goto end_loop;
4180 END IF;
4181 END LOOP;
4182 END IF;
4183
4184 -- If the line could not find it's top model line id in the
4185 -- processed_pto_smc, it has not been processed. Let's add the
4186 -- top_model_line_id to the table and process the set.
4187
4188 pto_smc_count := pto_smc_count + 1;
4189 processed_pto_smc(pto_smc_count) := l_line_rec.top_model_line_id;
4190 ELSIF l_line_rec.ato_line_id is not null THEN
4191 -- Check to see if this set is already processed. If yes, then
4192 -- go to the end.
4193 ato_count := processed_ato.count;
4194 IF ato_count > 0 THEN
4195 FOR c IN 1..processed_ato.count LOOP
4196 IF l_line_rec.ato_line_id = processed_ato(c)
4197 THEN
4198 -- This set has been processed.
4199 goto end_loop;
4200 END IF;
4201 END LOOP;
4202 END IF;
4203
4204 -- If the line could not find it's ATO Line id in the
4205 -- processed_ato set, it has not been processed. Let's add the
4206 -- set_id to the table and process the set.
4207
4208 ato_count := ato_count + 1;
4209 processed_ato(ato_count) := l_line_rec.ato_line_id;
4210 END IF;
4211 END IF; /* Action is not reserve */
4212
4213 IF (l_line_rec.ship_set_id is not null OR
4214 l_line_rec.arrival_set_id is not null OR
4215 l_line_rec.ship_model_complete_flag = 'Y' OR
4216 (l_line_rec.ato_line_id is not null AND
4217 NOT (l_line_rec.line_id = l_line_rec.ato_line_id AND
4218 l_line_rec.item_type_code IN( OE_GLOBALS.G_ITEM_STANDARD,
4219 OE_GLOBALS.G_ITEM_OPTION)))) AND
4220 (p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_SCHEDULE OR
4221 p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNSCHEDULE OR
4222 p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK OR
4223 ((p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNRESERVE OR
4224 p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE ) AND
4225 l_line_rec.ship_model_complete_flag = 'Y'))
4226 THEN
4227
4228 IF l_debug_level > 0 THEN
4229 oe_debug_pub.add( 'SCHEDULING A GROUP ' , 1 ) ;
4230 END IF;
4231 l_line_rec.schedule_action_code := p_action;
4232
4233 IF l_debug_level > 0 THEN
4234 oe_debug_pub.add( 'SM: CREATING GROUP_REQUEST' , 1 ) ;
4235 END IF;
4236
4237 OE_ORDER_SCH_UTIL.Create_Group_Request
4238 ( p_line_rec => l_line_rec
4239 , p_old_line_rec => l_line_rec
4240 , x_group_req_rec => l_group_req_rec
4241 , x_return_status => l_return_status
4242 );
4243
4244 /* Bug 2270426 */
4245 IF (p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE OR
4246 p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_UNRESERVE)
4247 AND l_line_rec.ship_model_complete_flag = 'Y'
4248 AND (l_line_rec.arrival_set_id is not null OR
4249 l_line_rec.ship_set_id is not null) THEN
4250
4251 IF l_debug_level > 0 THEN
4252 oe_debug_pub.add( 'INSIDE RESERVE , SMC AND SET' ) ;
4253 END IF;
4254 l_group_req_rec.entity_type := OE_ORDER_SCH_UTIL.OESCH_ENTITY_SMC;
4255 l_group_req_rec.ship_set_number := l_line_rec.top_model_line_id;
4256
4257 END IF;
4258
4259 -- Line belongs to a group. Needs to be scheduled in a group.
4260
4261
4262 IF l_debug_level > 0 THEN
4263 oe_debug_pub.add( 'SM: CALLING GRP_SCHEDULE: ' || L_RETURN_STATUS , 1 ) ;
4264 END IF;
4265
4266 Group_Schedule
4267 ( p_group_req_rec => l_group_req_rec
4268 ,x_atp_tbl => l_out_atp_tbl
4269 ,x_return_status => l_return_status);
4270
4271 IF l_debug_level > 0 THEN
4272 oe_debug_pub.add( 'SM: AFTER CALLING GROUP_SCHEDULE: ' || L_RETURN_STATUS , 1 ) ;
4273 END IF;
4274
4275 FOR J IN 1..l_out_atp_tbl.count
4276 LOOP
4277 x_atp_tbl(atp_count) := l_out_atp_tbl(J);
4278 atp_count := atp_count + 1;
4279 END LOOP;
4280
4281 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4283 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4284 l_out_return_status := FND_API.G_RET_STS_ERROR;
4285 ROLLBACK TO SCH_LINE;
4286 END IF;
4287
4288
4289 ELSE
4290
4291 -- The line will come here, if it is a standard line not
4292 -- belong to any group, or if the action on it is RESERVE
4293 -- or UNRESERVE (and it does belong to a group).
4294
4295 l_line_rec.schedule_action_code := p_action;
4296 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
4297
4298 IF l_debug_level > 0 THEN
4299 oe_debug_pub.add( 'SCHEDULING LINE: ' || L_LINE_REC.LINE_ID , 1 ) ;
4300 END IF;
4301 IF l_debug_level > 0 THEN
4302 oe_debug_pub.add( 'ITEM TYPE IS : ' || L_LINE_REC.ITEM_TYPE_CODE , 1 ) ;
4303 END IF;
4304 IF l_debug_level > 0 THEN
4305 oe_debug_pub.add( ' ' , 1 ) ;
4306 END IF;
4307
4308
4309 IF l_debug_level > 0 THEN
4310 oe_debug_pub.add( 'GRP2: CALLING SCHEDULE LINE' , 1 ) ;
4311 END IF;
4312
4313 OE_ORDER_SCH_UTIL.Schedule_line
4314 ( p_old_line_rec => l_old_line_rec
4315 ,p_write_to_db => FND_API.G_TRUE
4316 ,p_x_line_rec => l_line_rec
4317 ,x_atp_tbl => l_out_atp_tbl
4318 ,x_return_status => l_return_status);
4319
4320 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4322 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4323 l_out_return_status := FND_API.G_RET_STS_ERROR;
4324 ROLLBACK TO SCH_LINE;
4325 END IF;
4326
4327 IF l_debug_level > 0 THEN
4328 oe_debug_pub.add( 'GRP2:AFTER CALLING SCHEDULE LINE' || L_RETURN_STATUS , 1 ) ;
4329 END IF;
4330
4331 IF l_debug_level > 0 THEN
4332 oe_debug_pub.add( 'COUNT OF ATP IS : ' || L_OUT_ATP_TBL.COUNT , 1 ) ;
4333 END IF;
4334 -- Load the ATP table which could have more records than 1 since
4335 -- included items got scheduled.
4336
4337
4338 FOR J IN 1..l_out_atp_tbl.count
4339 LOOP
4340 x_atp_tbl(atp_count) := l_out_atp_tbl(J);
4341 atp_count := atp_count + 1;
4342 END LOOP;
4343
4344 END IF;
4345
4346 <<end_loop>>
4347 null;
4348
4349 EXCEPTION
4350
4351 WHEN FND_API.G_EXC_ERROR THEN
4352
4353 -- The line did not get scheduled, which is ok, since an error
4354 -- message would have captured the error.
4355 ROLLBACK TO SCH_LINE;
4356 null;
4357 END;
4358
4359 END LOOP;
4360
4361 -- Set return status.
4362
4363 /*
4364 IF l_out_return_status = FND_API.G_RET_STS_ERROR THEN
4365 RAISE FND_API.G_EXC_ERROR;
4366 END IF;
4367 */
4368
4369 -- x_return_status := l_out_return_status;
4370 -- Returning success, even if there were errors (unexpected errors will
4371 -- be raised and taken care of). This is because we do not want to rollback
4372 -- since the successful lines should get committed.
4373
4374 x_return_status := FND_API.G_RET_STS_SUCCESS;
4375
4376 -- Get message count and data
4377
4378 oe_msg_pub.count_and_get
4379 ( p_count => x_msg_count
4380 , p_data => x_msg_data
4381 );
4382
4383 /* Bug :2222360 */
4384 IF p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
4385 IF l_debug_level > 0 THEN
4386 oe_debug_pub.add( 'INSIDE ATP CHECK ROLLBACK' ) ;
4387 END IF;
4388 ROLLBACK TO ATP_CHECK;
4389 OE_Delayed_Requests_Pvt.Clear_Request
4390 (x_return_status => l_return_status);
4391 END IF;
4392
4393 IF l_debug_level > 0 THEN
4394 oe_debug_pub.add( 'EXITING SCH_MULTI_SELECTED_LINES WITH: ' || L_OUT_RETURN_STATUS , 1 ) ;
4395 END IF;
4396
4397 EXCEPTION
4398
4399 WHEN FND_API.G_EXC_ERROR THEN
4400
4401 x_return_status := FND_API.G_RET_STS_ERROR;
4402
4403 /* Bug :2222360 */
4404 IF p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
4405 IF l_debug_level > 0 THEN
4406 oe_debug_pub.add( 'INSIDE ATP CHECK ROLLBACK' ) ;
4407 END IF;
4408 ROLLBACK TO ATP_CHECK;
4409 OE_Delayed_Requests_Pvt.Clear_Request
4410 (x_return_status => l_return_status);
4411 ELSE
4412 ROLLBACK TO SCH_ACTION;
4413 END IF;
4414
4415
4416 -- Get message count and data
4417
4418 oe_msg_pub.count_and_get
4419 ( p_count => x_msg_count
4420 , p_data => x_msg_data
4421 );
4422
4423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4424
4425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4426
4427 /* Bug :2222360 */
4428 IF p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
4429 IF l_debug_level > 0 THEN
4430 oe_debug_pub.add( 'INSIDE ATP CHECK ROLLBACK' ) ;
4431 END IF;
4432 ROLLBACK TO ATP_CHECK;
4433 OE_Delayed_Requests_Pvt.Clear_Request
4434 (x_return_status => l_return_status);
4435 ELSE
4436 ROLLBACK TO SCH_ACTION;
4437 END IF;
4438
4439 -- Get message count and data
4440
4441 oe_msg_pub.count_and_get
4442 ( p_count => x_msg_count
4443 , p_data => x_msg_data
4444 );
4445
4446 WHEN OTHERS THEN
4447
4448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4449
4450 /* Bug :2222360 */
4451 IF p_action = OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK THEN
4452 IF l_debug_level > 0 THEN
4453 oe_debug_pub.add( 'INSIDE ATP CHECK ROLLBACK' ) ;
4454 END IF;
4455 ROLLBACK TO ATP_CHECK;
4456 OE_Delayed_Requests_Pvt.Clear_Request
4457 (x_return_status => l_return_status);
4458 ELSE
4459 ROLLBACK TO SCH_ACTION;
4460 END IF;
4461
4462 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
4463 THEN
4464 oe_msg_pub.Add_Exc_Msg
4465 ( G_PKG_NAME
4466 , 'Sch_Multi_selected_lines'
4467 );
4468 END IF;
4469
4470 -- Get message count and data
4471
4472 oe_msg_pub.count_and_get
4473 ( p_count => x_msg_count
4474 , p_data => x_msg_data
4475 );
4476
4477 END Sch_Multi_selected_lines;
4478
4479
4480 END OE_GRP_SCH_UTIL;
4481
[Home] [Help]