[Home] [Help]
PACKAGE BODY: APPS.OE_SCHEDULE_GRP
Source
1 PACKAGE BODY OE_SCHEDULE_GRP AS
2 /* $Header: OEXGSCHB.pls 120.14.12020000.2 2013/03/13 23:45:49 gabhatia ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_SCHEDULE_GRP';
5 G_SCH_TBL sch_tbl_type;
6 G_LINE_TBL OE_Order_PUB.Line_Tbl_Type;
7 G_OLD_LINE_TBL OE_Order_PUB.Line_Tbl_Type;
8 G_BINARY_LIMIT CONSTANT NUMBER := OE_GLOBALS.G_BINARY_LIMIT; -- 9187335
9
10 /** Fwd declaration ********/
11 PROCEDURE Process_order(x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2);
12 PROCEDURE Validate_sch_data(x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2);
13 PROCEDURE Validate_Lines(x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2);
14 PROCEDURE Validate_set(p_ship_set_id IN NUMBER DEFAULT NULL,
15 p_arrival_set_id IN NUMBER DEFAULT NULL,
16 p_top_model_line_id IN NUMBER DEFAULT NULL);
17
18 FUNCTION Find_line(p_line_id IN NUMBER)
19 RETURN BOOLEAN;
20 FUNCTION Find_index(p_line_id IN NUMBER)
21 RETURN NUMBER;
22 Procedure Update_reservation(p_index IN NUMBER,
23 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
24 Procedure Update_Scheduling_Results
25 (p_x_sch_tbl IN OUT NOCOPY sch_tbl_type,
26 p_request_id IN Number,
27 x_return_status OUT NOCOPY Varchar2)
28 IS
29 l_old_org_id Number := -99;
30 l_old_header_id Number;
31 l_count Number;
32 l_return_status Varchar2(1);
33 l_msg_count NUMBER;
34 l_msg_data VARCHAR2(2000);
35 l_orig_user_id NUMBER;
36 l_orig_resp_id NUMBER;
37 l_orig_resp_appl_id NUMBER;
38 J NUMBER;
39 --9187335 Start
40 TYPE set_line_rec_type IS RECORD
41 (line_id NUMBER
42 ,set_id NUMBER);
43
44 TYPE OE_set_line_tbl_type IS TABLE OF
45 set_line_rec_type INDEX by BINARY_INTEGER;
46 l_set_line_tbl OE_set_line_tbl_type;
47
48 TYPE set_rec_type IS RECORD
49 (set_id NUMBER);
50
51 TYPE OE_sets_tbl_type IS TABLE OF
52 set_rec_type INDEX by BINARY_INTEGER;
53 l_sets_tbl OE_sets_tbl_type;
54
55 l_ship_set_id NUMBER;
56 l_arrival_set_id NUMBER;
57 l_non_plan_line_rec sch_rec_type ;
58 l_add_count NUMBER :=0; --1; --12639544
59
60 CURSOR ship_set(p_ship_set number) IS
61 SELECT ol.line_id,
62 ol.inventory_item_id,
63 ol.ordered_quantity
64 FROM oe_order_lines_all ol
65 WHERE ship_set_id=p_ship_set;
66
67
68 CURSOR arrival_set(p_arrival_set number) IS
69 SELECT ol.line_id ,
70 ol.inventory_item_id,
71 ol.ordered_quantity
72 FROM oe_order_lines_all ol
73 WHERE arrival_set_id=p_arrival_set;
74 --9187335 End
75 BEGIN
76
77 oe_debug_pub.add('Entering oe_schedule_grp.update_scheduling_results' || p_x_sch_tbl.count ,1);
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79
80 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL > '110509' THEN
81 l_count := p_x_sch_tbl.count;
82 --9187335 Start
83 -- Populate l_set_line_tbl
84 FOR I IN 1..p_x_sch_tbl.count LOOP
85 l_set_line_tbl(MOD(p_x_sch_tbl(I).line_id,G_BINARY_LIMIT)).line_id := p_x_sch_tbl(I).line_id;
86 END LOOP;
87 --9187335 End
88 FOR I IN 1..p_x_sch_tbl.count LOOP
89
90 IF l_old_org_id <> p_x_sch_tbl(I).org_id THEN
91 l_old_org_id := p_x_sch_tbl(I).org_id;
92
93 oe_debug_pub.add('Set the Org ',1);
94 -- dbms_application_info.set_client_info(p_x_sch_tbl(I).org_id);
95
96 OE_Order_Context_Grp.Set_Created_By_Context
97 (p_header_id => p_x_sch_tbl(I).header_id
98 ,p_line_id => p_x_sch_tbl(I).line_id
99 ,x_orig_user_id => l_orig_user_id
100 ,x_orig_resp_id => l_orig_resp_id
101 ,x_orig_resp_appl_id => l_orig_resp_appl_id
102 ,x_return_status => l_return_status
103 ,x_msg_count => l_msg_count
104 ,x_msg_data => l_msg_data);
105
106 END IF;
107 --9187335 Start
108 --g_sch_tbl(I) := p_x_sch_tbl(I);
109 --12639544
110 --l_add_count:=g_sch_tbl.count+1;
111 l_add_count:= l_add_count +1;
112 g_sch_tbl(l_add_count) :=p_x_sch_tbl(I);
113 --g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_SUCCESS;
114 g_sch_tbl(l_add_count).x_return_status := FND_API.G_RET_STS_SUCCESS;
115
116 BEGIN
117 SELECT ship_set_id, arrival_set_id
118 INTO l_ship_set_id, l_arrival_set_id
119 FROM oe_order_lines_all
120 WHERE line_id=p_x_sch_tbl(I).line_id;
121
122 oe_debug_pub.add('Ship Set '||l_ship_set_id||' Arrival Set '||l_arrival_set_id,1);
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 null;
126 oe_debug_pub.add('NO_DATA_FOUND',1);
127 WHEN OTHERS THEN
128 null;
129 oe_debug_pub.add('Error'||sqlerrm,1);
130 END ;
131
132 IF l_ship_set_id IS NOT NULL THEN
133 -- Add the set to the table
134 IF l_sets_tbl.EXISTS (MOD(l_ship_set_id,G_BINARY_LIMIT)) THEN
135 NULL;
136 ELSE
137 l_sets_tbl(MOD(l_ship_set_id,G_BINARY_LIMIT)).set_id := l_ship_set_id;
138 FOR k IN ship_set(l_ship_set_id) LOOP
139 IF l_set_line_tbl.EXISTS (MOD(k.line_id,G_BINARY_LIMIT)) THEN
140 null;
141 oe_debug_pub.ADD('Line is already present in the table');
142 ELSE
143 l_non_plan_line_rec:=p_x_sch_tbl(I);
144 l_non_plan_line_rec.line_id:=k.line_id;
145 l_non_plan_line_rec.inventory_item_id:=k.inventory_item_id;
146 l_non_plan_line_rec.Orig_Inventory_item_id:=k.inventory_item_id;
147 l_non_plan_line_rec.Orig_ordered_quantity:=k.ordered_quantity;
148 l_non_plan_line_rec.x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 IF g_sch_tbl.count>0 THEN
151 l_add_count:=g_sch_tbl.count+1;
152 END IF ;
153 oe_debug_pub.ADD('adding the line:'||l_non_plan_line_rec.line_id||' count '||l_add_count);
154 g_sch_tbl(l_add_count):=l_non_plan_line_rec;
155 l_set_line_tbl(MOD(l_non_plan_line_rec.line_id,G_BINARY_LIMIT)).line_id := l_non_plan_line_rec.line_id;
156 END IF ; --line is not in the table passed by planning
157 END LOOP ;
158 END IF;
159 ELSIF l_arrival_set_id IS NOT NULL THEN
160 IF l_sets_tbl.EXISTS (MOD(l_arrival_set_id,G_BINARY_LIMIT)) THEN
161 NULL;
162 ELSE
163 l_sets_tbl(MOD(l_arrival_set_id,G_BINARY_LIMIT)).set_id := l_arrival_set_id;
164 FOR k IN arrival_set(l_arrival_set_id) LOOP
165 IF l_set_line_tbl.EXISTS (MOD(k.line_id,G_BINARY_LIMIT)) THEN
166 null;
167 oe_debug_pub.ADD('Line is already present in the table');
168 ELSE
169 l_non_plan_line_rec:=p_x_sch_tbl(I);
170 l_non_plan_line_rec.line_id:=k.line_id;
171 l_non_plan_line_rec.inventory_item_id:=k.inventory_item_id;
172 l_non_plan_line_rec.Orig_Inventory_item_id:=k.inventory_item_id;
173 l_non_plan_line_rec.Orig_ordered_quantity:=k.ordered_quantity;
174 l_non_plan_line_rec.x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 IF g_sch_tbl.count>0 THEN
177 l_add_count:=g_sch_tbl.count+1;
178 END IF ;
179 oe_debug_pub.ADD('adding the line:'||l_non_plan_line_rec.line_id);
180 g_sch_tbl(l_add_count):=l_non_plan_line_rec;
181 l_set_line_tbl(MOD(l_non_plan_line_rec.line_id,G_BINARY_LIMIT)).line_id := l_non_plan_line_rec.line_id;
182 END IF ; --line is not in the table passed by planning
183 END LOOP ;
184 END IF;
185 END IF ;
186 --9187335 End
187
188 IF I = l_count
189 OR p_x_sch_tbl(I).header_id <> p_x_sch_tbl(I + 1).header_id THEN
190
191 SAVEPOINT Group_Schedule;
192 oe_debug_pub.ADD('Line to be processed: '||l_add_count||' - '||g_sch_tbl(l_add_count).line_id);
193 Process_order(x_return_status => l_return_status);
194 oe_debug_pub.ADD('After Process Order :'|| l_return_status);
195 /*
196 -- Handling error as per bug 7679398
197 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
200 RAISE FND_API.G_EXC_ERROR;
201 END IF;
202 */
203 -- Commented the above for bug 7679398/7675256, and added the SAVEPOINT -
204 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
205 ROLLBACK TO Group_Schedule;
206 END IF;
207
208
209 J := g_sch_tbl.FIRST;
210 WHILE J IS NOT NULL
211 LOOP
212
213 oe_debug_pub.add('J : ' || J);
214 oe_debug_pub.add('J Return Status :' || g_sch_tbl(J).x_return_status,1);
215 --12639544
216 oe_debug_pub.add('Count for p_x_sch_tbl :' || p_x_sch_tbl.count,1);
217 oe_debug_pub.add('Count for g_sch_tbl :' || g_sch_tbl.count);
218 FOR I in 1..p_x_sch_tbl.LAST LOOP
219 IF p_x_sch_tbl(I).line_id = g_sch_tbl(J).line_id THEN
220 p_x_sch_tbl(I).x_return_status
221 := g_sch_tbl(J).x_return_status;
222 IF g_sch_tbl(J).x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
223
224 oe_debug_pub.add('Setting the status to W to indicate partial processing',1);
225 x_return_status := 'W';
226
227 END IF;
228
229 p_x_sch_tbl(I).x_override_atp_date_code :=
230 g_sch_tbl(J).x_override_atp_date_code;
231 oe_debug_pub.add('J+ Return Status :' || p_x_sch_tbl(I).x_return_status,1);
232 oe_debug_pub.add('J+ line_id :' || p_x_sch_tbl(I).line_id,1);
233 EXIT;
234 END IF;
235 END LOOP;
236 J := g_sch_tbl.next(J);
237 END LOOP;
238
239 g_sch_tbl.delete;
240
241 END IF;
242
243 END LOOP;
244
245 END IF; -- Code release.
246
247 oe_msg_pub.save_messages(p_request_id => p_request_id);
248
249 oe_debug_pub.add('Exiting oe_schedule_grp.update_scheduling_results' ||
250 x_return_status,1);
251
252 EXCEPTION
253 WHEN FND_API.G_EXC_ERROR THEN
254 x_return_status := FND_API.G_RET_STS_ERROR;
255
256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258
259 WHEN OTHERS THEN
260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
261
262 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
263 THEN
264 OE_MSG_PUB.Add_Exc_Msg
265 ( G_PKG_NAME
266 , 'update_scheduling_results'
267 );
268 END IF;
269 END Update_Scheduling_Results;
270
271 PROCEDURE Process_order(x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2)
272 IS
273 l_line_rec OE_Order_PUB.Line_Rec_type;
274 l_local_line_tbl OE_ORDER_PUB.line_tbl_type;
275 l_control_rec OE_GLOBALS.control_rec_type;
276 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
277 l_count Number;
278 l_ship_from_is_changed BOOLEAN := FALSE;
279 l_ship_method_is_changed BOOLEAN := FALSE;
280
281 -- The l_ord_qty_is_changed is added for IR ISO CMS project.
282 -- Refer bug #7576948
283 l_ord_qty_is_changed BOOLEAN := FALSE;
284
285 l_item_substituted BOOLEAN := FALSE; -- Added or ER 6110708
286 l_shippable_flag VARCHAR2(1); -- Added for ER 6110708
287 l_opm_enabled BOOLEAN; -- Added for ER 6110708
288 l_sales_order_id Number;
289 I Number;
290 l_index Number;
291 l_line_tbl OE_Order_Pub.Line_Tbl_Type; --12639544
292 l_old_line_tbl OE_Order_Pub.Line_Tbl_Type; --12639544
293 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
294 BEGIN
295
296 oe_debug_pub.add('Entering oe_schedule_grp.Process_order',1);
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 -- This variable is to track that the Item is being Substituted by Planning Loop Back and not being changed manully by user.
300 -- Initializing to 'N' for current set of Lines. Will set it to Y if any item substitutions are happening.
301 OE_SCHEDULE_UTIL.OESCH_ITEM_IS_SUBSTITUTED := 'N'; -- Added for ER 6110708
302
303 -- Bug #7667702: Tracking bug for IR ISO CMS Project
304 -- Since, in this code, caller can only be Planning (Planning Workbench
305 -- or DRP), setting the global with default value FALSE
306 OE_Schedule_GRP.G_ISO_Planning_Update := FALSE;
307
308
309 Validate_sch_data(x_return_status => l_return_status);
310 l_count := 1;
311
312 I := g_sch_tbl.FIRST;
313 WHILE I IS NOT NULL
314 LOOP
315 -- FOR I IN 1..g_sch_tbl.count LOOP
316
317 IF g_sch_tbl(I).x_return_status = FND_API.G_RET_STS_SUCCESS THEN
318
319 -- g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_SUCCESS;
320
321 BEGIN
322 OE_Line_Util.Query_Row( p_line_id => g_sch_tbl(I).line_id
323 ,x_line_rec => l_line_rec);
324
325 EXCEPTION
326 WHEN NO_DATA_FOUND THEN
327
328 OE_MSG_PUB.set_msg_context
329 ( p_entity_code => 'LINE'
330 ,p_entity_id => g_sch_tbl(I).line_id
331 ,p_header_id => g_sch_tbl(I).header_id
332 ,p_line_id => g_sch_tbl(I).line_id);
333
334 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_LOOP_LINE');
335 FND_MESSAGE.SET_TOKEN('LINE_ID',g_sch_tbl(I).line_id);
336 OE_MSG_PUB.ADD;
337 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
338 goto end_main_loop;
339 END;
340 -- Special Validation is needed before taking the data
341
342 OE_MSG_PUB.set_msg_context
343 ( p_entity_code => 'LINE'
344 ,p_entity_id => l_line_rec.line_id
345 ,p_header_id => l_line_rec.header_id
346 ,p_line_id => l_line_rec.line_id
347 ,p_orig_sys_document_ref =>
348 l_line_rec.orig_sys_document_ref
349 ,p_orig_sys_document_line_ref =>
350 l_line_rec.orig_sys_line_ref
351 ,p_orig_sys_shipment_ref =>
352 l_line_rec.orig_sys_shipment_ref
353 ,p_change_sequence => l_line_rec.change_sequence
354 ,p_source_document_id =>
355 l_line_rec.source_document_id
356 ,p_source_document_line_id =>
357 l_line_rec.source_document_line_id
358 ,p_order_source_id =>
359 l_line_rec.order_source_id
360 ,p_source_document_type_id =>
361 l_line_rec.source_document_type_id);
362
363
364 IF g_sch_tbl(I).Orig_Schedule_Ship_Date IS NOT NULL
365 AND trunc(l_line_rec.Schedule_Ship_Date) <>
366 trunc(g_sch_tbl(I).Orig_Schedule_Ship_Date) THEN
367
368 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ATTRIBUTE');
369 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
370 OE_Order_Util.Get_Attribute_Name('Schedule_Ship_Date'));
371 OE_MSG_PUB.ADD;
372 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
373
374 END IF;
375
376 IF g_sch_tbl(I).Orig_Schedule_arrival_date IS NOT NULL
377 AND trunc(l_line_rec.Schedule_Arrival_date) <>
378 trunc(g_sch_tbl(I).Orig_Schedule_arrival_date)
379 THEN
380
381 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ATTRIBUTE');
382 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
383 OE_Order_Util.Get_Attribute_Name('Schedule_Arrival_date'));
384 OE_MSG_PUB.ADD;
385 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
386
387 END IF;
388
389 IF g_sch_tbl(I).Orig_ship_from_org_id IS NOT NULL
390 AND l_line_rec.ship_from_org_id <>
391 g_sch_tbl(I).Orig_ship_from_org_id
392 THEN
393
394 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ATTRIBUTE');
395 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
396 OE_Order_Util.Get_Attribute_Name('ship_from_org_id'));
397 OE_MSG_PUB.ADD;
398 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
399
400 END IF;
401
402 IF g_sch_tbl(I).Orig_Shipping_Method_Code IS NOT NULL
403 AND l_line_rec.Shipping_Method_Code <>
404 g_sch_tbl(I).Orig_Shipping_Method_Code
405 THEN
406
407 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ATTRIBUTE');
408 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
409 OE_Order_Util.Get_Attribute_Name('Shipping_Method_Code'));
410 OE_MSG_PUB.ADD;
411 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
412
413 END IF;
414
415 IF g_sch_tbl(I).Orig_ordered_quantity IS NOT NULL
416 AND l_line_rec.ordered_quantity <>
417 g_sch_tbl(I).Orig_ordered_quantity
418 THEN
419
420 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ATTRIBUTE');
421 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
422 OE_Order_Util.Get_Attribute_Name('ordered_quantity'));
423 OE_MSG_PUB.ADD;
424 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
425
426 END IF;
427
428 IF g_sch_tbl(I).Orig_Earliest_Ship_date IS NOT NULL
429 AND trunc(Nvl(l_line_rec.Earliest_Ship_date,
430 g_sch_tbl(I).Orig_Earliest_Ship_date)) <>
431 trunc(g_sch_tbl(I).Orig_Earliest_Ship_date)
432
433 THEN
434
435 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ATTRIBUTE');
436 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
437 OE_Order_Util.Get_Attribute_Name('Earliest_Ship_date'));
438 OE_MSG_PUB.ADD;
439 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
440
441 END IF;
442
443 IF l_line_rec.ato_line_id is not null
444 AND l_line_rec.ato_line_id <> l_line_rec.line_id
445 AND NOT l_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
446
447 oe_debug_pub.add('E4',2);
448 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
449 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_LOOP_NOT_SUP');
450 OE_MSG_PUB.ADD;
451 END IF;
452
453 /* Added for ER 6110708 to support Item Substitutions from Planning Loop Back */
454 IF g_sch_tbl(I).inventory_item_id IS NOT NULL
455 AND g_sch_tbl(I).inventory_item_id <> g_sch_tbl(I).orig_inventory_item_id THEN
456
457 oe_debug_pub.add('Doing validations for Item Substitutions for Line Id : ' || g_sch_tbl(I).line_id, 5);
458 oe_debug_pub.add(' Original Item on Line : ' || g_sch_tbl(I).orig_inventory_item_id, 5);
459 oe_debug_pub.add(' Substitute Item : ' || g_sch_tbl(I).inventory_item_id, 5);
460 oe_debug_pub.add(' Current Item on Line : ' || l_line_rec.inventory_item_id, 5);
461
462 -- If Item on the Sales Order Line has changed after running the plan and
463 -- before Planning releases the recommendations, then we should not accept the Item
464 -- Substitution. This means that planning has recommended item substitution based on old data.
465 IF l_line_rec.inventory_item_id <> g_sch_tbl(I).orig_inventory_item_id THEN
466 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ATTRIBUTE');
467 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
468 OE_Order_Util.Get_Attribute_Name('inventory_item_id'));
469 OE_MSG_PUB.ADD;
470 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
471 END IF;
472
473 l_opm_enabled := INV_GMI_RSV_BRANCH.Process_Branch(p_organization_id => l_line_rec.ship_from_org_id);
474
475 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(l_line_rec.HEADER_ID);
476 l_line_rec.reserved_quantity := OE_LINE_UTIL.Get_Reserved_Quantity
477 ( p_header_id => l_sales_order_id,
478 p_line_id => l_line_rec.line_id,
479 p_org_id => l_line_rec.ship_from_org_id
480 );
481 --bug 16434168/16480604 Issue 1
482
483 l_line_rec.reserved_quantity2 := OE_LINE_UTIL.Get_Reserved_Quantity2
484 ( p_header_id => l_sales_order_id,
485 p_line_id => l_line_rec.line_id,
486 p_org_id => l_line_rec.ship_from_org_id
487 );
488
489 --end bug 16434168/16480604
490
491 -- Do not allow Item Substitutions in below cases.
492 oe_debug_pub.add(' Item Type : ' || l_line_rec.item_type_code, 5);
493 oe_debug_pub.add(' Line Set Id : ' || l_line_rec.line_set_id, 5);
494 oe_debug_pub.add(' Split From Line Id : ' || l_line_rec.split_from_line_id, 5);
495 oe_debug_pub.add(' Source Document Type Id : ' || nvl(l_line_rec.source_document_type_id, -99), 5);
496 oe_debug_pub.add(' Source Type : ' || l_line_rec.source_type_code, 5);
497 oe_debug_pub.add(' Booked Flag : ' || nvl(l_line_rec.booked_flag, 'N'), 5);
498 IF l_opm_enabled THEN
499 oe_debug_pub.add(' OPM Enabled Org : Yes', 5);
500 ELSE
501 oe_debug_pub.add(' OPM Enabled Org : No', 5);
502 END IF;
503 oe_debug_pub.add(' Reserved Qty : ' || nvl(l_line_rec.reserved_quantity, 0), 5);
504
505 IF ( l_line_rec.item_type_code <> OE_GLOBALS.G_ITEM_STANDARD OR -- Not a Standard Item
506 l_line_rec.line_set_id IS NOT NULL OR -- Line is Split
507 l_line_rec.split_from_line_id IS NOT NULL OR -- Split Line
508 nvl(l_line_rec.source_document_type_id, -99) = 10 OR -- Internal Sales Order Line
509 l_line_rec.source_type_code = 'EXTERNAL' OR -- Externally Sourced Line
510 ( nvl(l_line_rec.booked_flag, 'N') = 'Y' and INV_GMI_RSV_BRANCH.Process_Branch(p_organization_id => l_line_rec.ship_from_org_id) ) OR
511 -- Booked Line and OPM Item
512 ( nvl(l_line_rec.booked_flag, 'N') = 'Y' and nvl(l_line_rec.reserved_quantity, 0) <> 0 ) -- Booked Line with Reservations
513 )
514 THEN
515 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
516 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_LOOP_NOT_SUP');
517 OE_MSG_PUB.ADD;
518 END IF;
519
520 -- Do not allow substitution of Shippable Item with non-Shippable and vise versa once the Line is Booked.
521 IF nvl(l_line_rec.booked_flag, 'N') = 'Y' THEN
522 BEGIN
523 SELECT shippable_item_flag
524 INTO l_shippable_flag
525 FROM MTL_SYSTEM_ITEMS
526 WHERE INVENTORY_ITEM_ID = g_sch_tbl(I).inventory_item_id
527 AND ORGANIZATION_ID = nvl(g_sch_tbl(I).ship_from_org_id, l_line_rec.ship_from_org_id);
528
529 IF l_shippable_flag <> l_line_rec.shippable_flag THEN
530 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
531 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_LOOP_SHP_NONSHP');
532 OE_MSG_PUB.ADD;
533 END IF;
534 END;
535 END IF;
536
537 oe_debug_pub.add('Finished with validations for Item Substitutions', 5);
538
539 END IF;
540 /* End of ER 6110708 */
541
542 -- Special Validation.
543
544 oe_debug_pub.add('After Special Validation ',2);
545 IF g_sch_tbl(I).x_return_status = FND_API.G_RET_STS_SUCCESS THEN
546
547 IF nvl(l_line_rec.override_atp_date_code,'N') = 'Y' THEN
548 g_sch_tbl(I).x_override_atp_date_code := 'Y';
549 END IF;
550
551 IF (l_line_rec.ato_line_id = l_line_rec.line_id
552 AND l_line_rec.item_type_code IN (OE_GLOBALS.G_ITEM_MODEL,
553 OE_GLOBALS.G_ITEM_CLASS))
554 OR l_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG
555
556 THEN
557 oe_debug_pub.add('ATO Model',2);
558
559 OE_Config_Util.Query_ATO_Options
560 (p_ato_line_id => l_line_rec.ato_line_id,
561 x_line_tbl => l_local_line_tbl);
562
563 FOR J IN 1..l_local_line_tbl.COUNT LOOP
564
565 IF l_local_line_tbl(J).shippable_flag = 'Y' THEN
566
567 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
568 (l_local_line_tbl(J).HEADER_ID);
569 l_local_line_tbl(J).reserved_quantity :=
570 OE_LINE_UTIL.Get_Reserved_Quantity
571 (p_header_id => l_sales_order_id,
572 p_line_id => l_local_line_tbl(J).line_id,
573 p_org_id => l_local_line_tbl(J).ship_from_org_id);
574 --bug 16434168/16480604 Issue 1
575 l_local_line_tbl(J).reserved_quantity2 :=
576 OE_LINE_UTIL.Get_Reserved_Quantity2
577 (p_header_id => l_sales_order_id,
578 p_line_id => l_local_line_tbl(J).line_id,
579 p_org_id => l_local_line_tbl(J).ship_from_org_id);
580
581 --end bug 16434168/16480604
582
583 END IF;
584
585 g_old_line_tbl(l_count) := l_local_line_tbl(J);
586
587 IF g_sch_tbl(I).schedule_ship_date is NOT NULL THEN
588 /*
589 IF trunc(g_sch_tbl(I).schedule_ship_date) < trunc(l_local_line_tbl(J).request_date) THEN
590
591 oe_debug_pub.add('Schedule Ship Date connot be less than request_date');
592 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
593 l_local_line_tbl(J).operation := OE_GLOBALS.G_OPR_NONE;
594 l_local_line_tbl(J).return_status := FND_API.G_RET_STS_ERROR;
595 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_INVALID_DATE');
596 OE_MSG_PUB.ADD;
597 goto end_loop;
598 END IF;
599 */
600 l_local_line_tbl(J).schedule_ship_date := g_sch_tbl(I).schedule_ship_date;
601
602 END IF;
603
604 IF g_sch_tbl(I).schedule_arrival_date IS NOT NULL THEN
605
606 /* IF trunc(g_sch_tbl(I).schedule_arrival_date) < trunc(l_local_line_tbl(J).request_date) THEN
607
608 oe_debug_pub.add('Schedule Ship Date connot be less than request_date');
609 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
610 l_local_line_tbl(J).operation := OE_GLOBALS.G_OPR_NONE;
611 l_local_line_tbl(J).return_status := FND_API.G_RET_STS_ERROR;
612 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_INVALID_DATE');
613 oe_msg_pub.add;
614 goto end_loop;
615 END IF;
616 */
617 l_local_line_tbl(J).schedule_arrival_date := g_sch_tbl(I).schedule_arrival_date;
618 END IF;
619
620 IF g_sch_tbl(I).ship_from_org_id is not null
621 AND g_sch_tbl(I).ship_from_org_id <> l_local_line_tbl(J).ship_from_org_id THEN
622 l_ship_from_is_changed := TRUE;
623 l_local_line_tbl(J).ship_from_org_id := g_sch_tbl(I).ship_from_org_id;
624 END IF;
625
626 IF g_sch_tbl(I).Delivery_lead_time is not null THEN
627 l_local_line_tbl(J).Delivery_lead_time := g_sch_tbl(I).Delivery_lead_time ;
628 END IF;
629
630 IF g_sch_tbl(I).Shipping_Method_Code is not null
631 AND nvl(l_local_line_tbl(J).Shipping_Method_Code,'-X') <> g_sch_tbl(I).Shipping_Method_Code THEN
632 l_ship_method_is_changed := TRUE;
633 l_local_line_tbl(J).Shipping_Method_Code := g_sch_tbl(I).Shipping_Method_Code;
634 END IF;
635 oe_debug_pub.add('Local shipping Method ' || l_local_line_tbl(J).Shipping_Method_Code,2);
636
637 IF nvl(g_sch_tbl(I).Firm_Demand_Flag,'N') = 'Y'
638 AND nvl(l_local_line_tbl(J).Firm_demand_Flag,'N') = 'N' THEN
639 l_local_line_tbl(J).Firm_Demand_Flag := 'Y';
640 END IF;
641
642 IF g_sch_tbl(I).Earliest_ship_date is not null THEN
643 l_local_line_tbl(J).Earliest_ship_date := g_sch_tbl(I).Earliest_ship_date;
644 END IF;
645
646 -- The Ordered Quantity check is added for IR ISO CMS project.
647 -- Refer bug #7576948
648 IF g_sch_tbl(I).Ordered_Quantity is not null
649 AND g_sch_tbl(I).Ordered_Quantity <> nvl(l_local_line_tbl(J).Ordered_Quantity,0) THEN
650 l_ord_qty_is_changed := TRUE;
651 l_local_line_tbl(J).Ordered_Quantity := g_sch_tbl(I).Ordered_Quantity;
652 END IF;
653
654 oe_debug_pub.add('Before assigning' || l_local_line_tbl(J).line_id,2);
655 g_line_tbl(l_count) := l_local_line_tbl(J);
656 g_line_tbl(l_count).operation := OE_GLOBALS.G_OPR_UPDATE;
657 g_line_tbl(l_count).return_status := Null;
658 l_count := l_count + 1;
659 <<end_loop>>
660 g_sch_tbl(I).x_line_number :=
661 OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(g_sch_tbl(I).line_id);
662 END LOOP;
663
664 ELSE
665
666 IF nvl(l_line_rec.shippable_flag,'N') = 'Y' THEN
667
668 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
669 (l_line_rec.HEADER_ID);
670 l_line_rec.reserved_quantity :=
671 OE_LINE_UTIL.Get_Reserved_Quantity
672 (p_header_id => l_sales_order_id,
673 p_line_id => l_line_rec.line_id,
674 p_org_id => l_line_rec.ship_from_org_id);
675 --bug 16434168/16480604 Issue 1
676 l_line_rec.reserved_quantity2 :=
677 OE_LINE_UTIL.Get_Reserved_Quantity2
678 (p_header_id => l_sales_order_id,
679 p_line_id => l_line_rec.line_id,
680 p_org_id => l_line_rec.ship_from_org_id);
681
682 --end bug 16434168/16480604
683
684 END IF;
685
686 g_old_line_tbl(l_count) := l_line_rec;
687
688 /* Added for ER 6110708 */
689 IF g_sch_tbl(I).inventory_item_id IS NOT NULL
690 AND g_sch_tbl(I).inventory_item_id <> g_sch_tbl(I).orig_inventory_item_id THEN
691
692 -- When doing item substitutions, store the original item details.
693 IF l_line_rec.Original_Inventory_Item_Id is null THEN
694 l_line_rec.Original_Inventory_Item_Id := l_line_rec.Inventory_Item_id;
695 l_line_rec.Original_item_identifier_Type := l_line_rec.item_identifier_type;
696 l_line_rec.Original_ordered_item_id := l_line_rec.ordered_item_id;
697 l_line_rec.Original_ordered_item := l_line_rec.ordered_item;
698 END IF;
699
700 l_line_rec.inventory_item_id := g_sch_tbl(I).inventory_item_id;
701 l_line_rec.item_identifier_type := 'INT';
702 l_item_substituted := TRUE;
703
704 -- This variable is to track that the Item is being Substituted by Planning Loop Back and not being changed manully by user.
705 OE_SCHEDULE_UTIL.OESCH_ITEM_IS_SUBSTITUTED := 'Y';
706
707 END IF;
708 /* End of ER 6110708 */
709
710 IF g_sch_tbl(I).schedule_ship_date is NOT NULL THEN
711 /* IF trunc(g_sch_tbl(I).schedule_ship_date) < trunc(l_line_rec.request_date) THEN
712
713 oe_debug_pub.add('Schedule Ship Date connot be less than request_date');
714 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
715 l_line_rec.operation := OE_GLOBALS.G_OPR_NONE;
716 l_line_rec.return_status := FND_API.G_RET_STS_ERROR;
717 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_INVALID_DATE');
718 OE_MSG_PUB.ADD;
719 goto end_loop1;
720 END IF;
721 */ l_line_rec.schedule_ship_date := g_sch_tbl(I).schedule_ship_date;
722 END IF;
723 IF g_sch_tbl(I).schedule_arrival_date is NOT NULL THEN
724 /* IF trunc(g_sch_tbl(I).schedule_arrival_date) < trunc(l_line_rec.request_date) THEN
725
726 oe_debug_pub.add('Schedule Ship Date connot be less than request_date');
727 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
728 l_line_rec.operation := OE_GLOBALS.G_OPR_NONE;
729 l_line_rec.return_status := FND_API.G_RET_STS_ERROR;
730 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_INVALID_DATE');
731 oe_msg_pub.add;
732 goto end_loop1;
733 END IF;
734 */ l_line_rec.schedule_arrival_date := g_sch_tbl(I).schedule_arrival_date;
735 END IF;
736
737 IF g_sch_tbl(I).ship_from_org_id is not null
738 AND g_sch_tbl(I).ship_from_org_id <> l_line_rec.ship_from_org_id THEN
739 l_ship_from_is_changed := TRUE;
740 l_line_rec.ship_from_org_id := g_sch_tbl(I).ship_from_org_id;
741 END IF;
742
743 IF g_sch_tbl(I).Delivery_lead_time is not null THEN
744 l_line_rec.Delivery_lead_time := g_sch_tbl(I).Delivery_lead_time;
745 END IF;
746
747 IF g_sch_tbl(I).Shipping_Method_Code is not null
748 AND g_sch_tbl(I).Shipping_Method_Code <> nvl(l_line_rec.Shipping_Method_Code,'-X') THEN
749 l_ship_method_is_changed := TRUE;
750 l_line_rec.Shipping_Method_Code := g_sch_tbl(I).Shipping_Method_Code;
751 END IF;
752 oe_debug_pub.add('Shipping Method on line rec ' || l_line_rec.Shipping_Method_Code,2);
753
754 IF nvl(g_sch_tbl(I).Firm_Demand_Flag,'N') = 'Y'
755 AND nvl(l_line_rec.Firm_demand_Flag,'N') = 'N' THEN
756 l_line_rec.Firm_Demand_Flag := 'Y';
757 END IF;
758
759 IF g_sch_tbl(I).Earliest_ship_date is not null THEN
760 l_line_rec.Earliest_ship_date := g_sch_tbl(I).Earliest_ship_date;
761 END IF;
762
763 -- The Ordered Quantity check is added for IR ISO CMS project.
764 -- Refer bug #7576948
765 IF g_sch_tbl(I).Ordered_Quantity is not null
766 AND g_sch_tbl(I).Ordered_Quantity <> nvl(l_line_rec.Ordered_Quantity,0) THEN
767 l_ord_qty_is_changed := TRUE;
768 l_line_rec.Ordered_Quantity := g_sch_tbl(I).Ordered_Quantity;
769 END IF;
770
771 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
772 g_line_tbl(l_count) := l_line_rec;
773 g_line_tbl(l_count).return_status := Null;
774 l_count := l_count + 1;
775 <<end_loop1>>
776 g_sch_tbl(I).x_line_number :=
777 OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(g_sch_tbl(I).line_id);
778
779 END IF;
780 END IF; -- Second Return
781 END IF; -- return status
782
783 <<end_main_loop>>
784 I := g_sch_tbl.NEXT(I);
785 END LOOP;
786
787 -- assign the error status to g_sch_tbl
788 Validate_lines(x_return_status => l_return_status);
789
790
791 FOR J IN 1..g_line_tbl.count LOOP
792
793 oe_debug_pub.add('Operation ' || g_line_tbl(J).operation ,2);
794
795 IF g_line_tbl(J).return_status <> FND_API.G_RET_STS_SUCCESS THEN
796
797 oe_debug_pub.add('None ' || g_line_tbl(J).return_status,2);
798 g_line_tbl(J).operation := OE_GLOBALS.G_OPR_NONE;
799
800 ELSE
801
802
803 IF g_line_tbl(J).schedule_status_code is not null THEN
804
805 OE_SCHEDULE_UTIL.Promise_Date_for_Sch_Action
806 ( p_x_line_rec => g_line_tbl(J)
807 ,p_sch_action => OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE
808 ,p_header_id => g_line_tbl(J).header_id);
809
810
811 ELSE
812
813 g_line_tbl(J).schedule_status_code := OE_SCHEDULE_UTIL.OESCH_STATUS_SCHEDULED;
814 g_line_tbl(J).visible_demand_flag := 'Y';
815
816 OE_SCHEDULE_UTIL.Promise_Date_for_Sch_Action
817 ( p_x_line_rec => g_line_tbl(J)
818 ,p_sch_action => OE_SCHEDULE_UTIL.OESCH_ACT_SCHEDULE
819 ,p_header_id => g_line_tbl(J).header_id);
820
821 -- Firm Demand Flag.
822 IF nvl(g_line_tbl(J).firm_demand_flag,'N') = 'N'
823 AND Oe_Sys_Parameters.Value('FIRM_DEMAND_EVENTS') = 'SCHEDULE' THEN
824 g_line_tbl(J).firm_demand_flag := 'Y';
825
826 END IF;
827
828 END IF;
829
830 -- 4558027
831 /* Start Audit Trail */
832 g_line_tbl(J).change_reason := 'SYSTEM';
833 g_line_tbl(J).change_comments := 'ASCP UPDATE';
834 /* End Audit Trail */
835
836 END IF;
837
838 IF l_debug_level > 0 THEN
839 oe_debug_pub.add('Operation1 ' || g_line_tbl(J).operation ,2);
840 oe_debug_pub.add('Line_id ' || g_line_tbl(J).line_id,2);
841 oe_debug_pub.add('Schedule_ship_date ' || g_line_tbl(J).Schedule_ship_date,2);
842 oe_debug_pub.add('Schedule_arrival_date ' || g_line_tbl(J).Schedule_arrival_date,2);
843 oe_debug_pub.add('Schedule_status_code ' || g_line_tbl(J).Schedule_status_code,2);
844 oe_debug_pub.add('Ship_from_org ' || g_line_tbl(J).Ship_from_org_id,2);
845 Oe_debug_pub.add('Shipping Method ' || g_line_tbl(J).shipping_method_code,2);
846 oe_debug_pub.add('Ordered Quantity ' || g_line_tbl(J).Ordered_Quantity,2);
847 END IF;
848
849 END LOOP;
850
851 -- Bug #7576948: FOr IR ISO CMS Project
852 --
853 -- Since, in this code, caller can only be Planning (Planning Workbench
854 -- or DRP), setting the global to TRUE, which can be read while processing
855 -- Scheduling for an internal sales order line with quantity as 0, i.e.
856 -- line is cancelled by Planner/DRP user
857 -- This global will even be used to supress the notification to be send
858 -- to Purchasing user for teh corresponding internal requisition, when the
859 -- changes are from Planning user
860 --
861 OE_Schedule_GRP.G_ISO_Planning_Update := TRUE;
862 IF l_debug_level > 0 THEN
863 oe_debug_pub.add(' Setting global OE_Schedule_GRP.G_ISO_Planning_Update to TRUE',5);
864 END IF;
865 --
866
867 IF l_ship_from_is_changed
868 OR l_ship_method_is_changed
869 OR l_item_substituted -- Added for ER 6110708, Process Order should be called in case of Item Substitutions also.
870 OR l_ord_qty_is_changed -- Added as part of IR ISO CMS Project. Refer bug #7576948
871 THEN
872
873 l_control_rec.controlled_operation := TRUE;
874 l_control_rec.change_attributes := TRUE;
875
876 l_control_rec.clear_dependents := TRUE;
877 l_control_rec.default_attributes := TRUE;
878 l_control_rec.check_security := TRUE; -- For ER 6110708 made it to TRUE, previously it was FALSE;
879
880 l_control_rec.write_to_DB := TRUE;
881 l_control_rec.validate_entity := TRUE;
882 l_control_rec.process := FALSE;
883
884 -- Instruct API to retain its caches
885
886 l_control_rec.clear_api_cache := FALSE;
887 l_control_rec.clear_api_requests := FALSE;
888
889 FOR J IN 1..g_line_tbl.count LOOP
890
891 oe_debug_pub.add('OP ' || g_line_tbl(J).operation ,2);
892 oe_debug_pub.add('OP ' || g_line_tbl(J).line_id ,2);
893 oe_debug_pub.add('Ol OP ' || g_old_line_tbl(J).operation ,2);
894 oe_debug_pub.add('Ol OP ' || g_old_line_tbl(J).line_id ,2);
895
896 END LOOP;
897
898 OE_Schedule_Util.Call_Process_Order
899 ( p_x_old_line_tbl => g_old_line_tbl
900 ,p_x_line_tbl => g_line_tbl
901 ,p_control_rec => l_control_rec
902 ,p_caller => OE_SCHEDULE_UTIL.SCH_EXTERNAL
903 ,x_return_status => x_return_status);
904 /* Commented for 4606248
905 OE_Order_PVT.Process_Requests_And_Notify
906 ( p_process_requests => TRUE
907 ,p_notify => TRUE
908 ,p_line_tbl => g_line_tbl
909 ,p_old_line_tbl => g_old_line_tbl
910 ,x_return_status => x_return_status);
911 */
912
913 ELSE
914 -- 12639544
915 -- Send one line at a time as its not returning process status for each line.
916 FOR J IN 1..g_line_tbl.count LOOP
917 BEGIN
918 l_line_tbl(1):= g_line_tbl(J);
919 l_old_line_tbl(1) := g_old_line_tbl(J);
920
921 OE_Config_Schedule_Pvt.Save_Sch_Attributes
922 ( p_x_line_tbl => l_line_tbl --g_line_tbl
923 ,p_old_line_tbl => l_old_line_tbl --g_old_line_tbl
924 ,p_sch_action => OE_SCHEDULE_UTIL.OESCH_ACT_SCHEDULE
925 ,p_caller => OE_SCHEDULE_UTIL.SCH_EXTERNAL
926 ,x_return_status => x_return_status);
927
928 EXCEPTION
929 WHEN OTHERS THEN
930 x_return_status := FND_API.G_RET_STS_ERROR;
931 END;
932 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
933 oe_debug_pub.add('OEXGSCHB : Setting the x_return_status for failed lines', 5); -- Added for ER 6110708
934 oe_debug_pub.add('OEXGSCHB : Failed Line Id : ' || g_line_tbl(J).line_id, 5); -- Added for ER 6110708
935 g_sch_tbl(J).x_return_status := x_return_status;
936 END IF;
937 END LOOP;
938 END IF;
939 -- 4606248
940 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
941
942 OE_Order_PVT.Process_Requests_And_Notify
943 ( p_process_requests => TRUE
944 ,p_notify => TRUE
945 ,p_line_tbl => g_line_tbl
946 ,p_old_line_tbl => g_old_line_tbl
947 ,x_return_status => x_return_status);
948
949 -- Resetting the variable after the Process Order API Call.
950 OE_SCHEDULE_UTIL.OESCH_ITEM_IS_SUBSTITUTED := 'N'; -- Added for ER 6110708
951
952 -- ELSE -- Updated for bug 7679398/7675256
953 END IF;
954 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
955
956
957
958 FOR I IN 1..g_line_tbl.COUNT LOOP
959
960 l_index := find_index(g_line_tbl(I).line_id);
961 g_sch_tbl(l_index).x_return_status := x_return_status;
962 END LOOP;
963
964 oe_debug_pub.add('Before clearing all requests',2);
965
966 oe_delayed_requests_pvt.Clear_Request(x_return_status=> l_return_status);
967 OE_ORDER_UTIL.Clear_Global_Picture(l_return_status);
968 IF OE_Config_Pvt.OE_MODIFY_INC_ITEMS_TBL.COUNT > 0 THEN
969 OE_Config_Pvt.OE_MODIFY_INC_ITEMS_TBL.DELETE;
970 END IF;
971
972
973 END IF;
974
975 -- Bug #7576948: For IR ISO CMS Project
976 -- Ressting the global to FALSE
977 --
978 OE_Schedule_GRP.G_ISO_Planning_Update := FALSE;
979 IF l_debug_level > 0 THEN
980 oe_debug_pub.add(' Setting global OE_Schedule_GRP.G_ISO_Planning_Update to FALSE',5);
981 END IF;
982 --
983
984
985 FOR I IN 1..g_line_tbl.COUNT LOOP
986
987 IF ( g_line_tbl(I).reserved_quantity > 0
988 OR g_line_tbl(I).inventory_item_id <> g_old_line_tbl(I).inventory_item_id -- Added for ER 6110708
989 )
990 AND (g_line_tbl(I).return_status is null OR
991 g_line_tbl(I).return_status = FND_API.G_RET_STS_SUCCESS) THEN
992
993 Update_reservation(p_index => I,
994 x_return_status => l_return_status);
995
996 END IF;
997 END LOOP;
998 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1000 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1001 RAISE FND_API.G_EXC_ERROR;
1002 END IF;
1003
1004
1005 g_line_tbl.delete;
1006 g_old_line_tbl.delete;
1007
1008 oe_debug_pub.add('Exiting oe_schedule_grp.Process_order '||x_return_status,1);
1009 EXCEPTION
1010 WHEN FND_API.G_EXC_ERROR THEN
1011
1012 OE_Schedule_GRP.G_ISO_Planning_Update := FALSE;
1013 -- Added for IR ISO Tracking bug 7667702
1014
1015 x_return_status := FND_API.G_RET_STS_ERROR;
1016 g_line_tbl.delete;
1017 g_old_line_tbl.delete;
1018
1019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1020
1021 OE_Schedule_GRP.G_ISO_Planning_Update := FALSE;
1022 -- Added for IR ISO Tracking bug 7667702
1023
1024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1025 g_line_tbl.delete;
1026 g_old_line_tbl.delete;
1027
1028 WHEN OTHERS THEN
1029
1030 OE_Schedule_GRP.G_ISO_Planning_Update := FALSE;
1031 -- Added for IR ISO Tracking bug 7667702
1032
1033 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1034 g_line_tbl.delete;
1035 g_old_line_tbl.delete;
1036
1037 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1038 THEN
1039 OE_MSG_PUB.Add_Exc_Msg
1040 ( G_PKG_NAME
1041 , 'process_order'
1042 );
1043 END IF;
1044 END process_order;
1045
1046 PROCEDURE Validate_sch_data(x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2)
1047 IS
1048 I NUMBER;
1049 BEGIN
1050
1051 I := g_sch_tbl.FIRST;
1052 WHILE I IS NOT NULL
1053 LOOP
1054 -- FOR I IN 1..g_sch_tbl.count LOOP
1055
1056 IF g_sch_tbl(I).schedule_ship_date is null
1057 OR g_sch_tbl(I).schedule_arrival_date is NULL
1058 THEN
1059
1060 OE_MSG_PUB.set_msg_context
1061 ( p_entity_code => 'LINE'
1062 ,p_entity_id => g_sch_tbl(I).line_id
1063 ,p_header_id => g_sch_tbl(I).header_id
1064 ,p_line_id => g_sch_tbl(I).line_id);
1065
1066 oe_debug_pub.add('ONT_SCH_LOOP_DATE_NULL',1);
1067 g_sch_tbl(I).x_return_status := FND_API.G_RET_STS_ERROR;
1068 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_LOOP_DATE_NULL');
1069 OE_MSG_PUB.ADD;
1070
1071 END IF;
1072
1073 I := g_sch_tbl.NEXT(I);
1074 END LOOP;
1075 EXCEPTION
1076 WHEN FND_API.G_EXC_ERROR THEN
1077 x_return_status := FND_API.G_RET_STS_ERROR;
1078
1079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1081
1082 WHEN OTHERS THEN
1083 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1084
1085 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1086 THEN
1087 OE_MSG_PUB.Add_Exc_Msg
1088 ( G_PKG_NAME
1089 , 'Validate_sch_data'
1090 );
1091 END IF;
1092 END Validate_sch_data;
1093
1094 PROCEDURE Validate_Lines(x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2)
1095 IS
1096 l_scheduling_level_code VARCHAR2(30);
1097 l_out_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1098 l_msg_count NUMBER;
1099 l_msg_data VARCHAR2(2000);
1100 l_result Varchar2(30);
1101 l_index NUMBER;
1102
1103 BEGIN
1104
1105 oe_debug_pub.add('Entering Validate_Lines',1);
1106
1107 FOR I IN 1..G_LINE_TBL.COUNT LOOP
1108
1109 oe_debug_pub.add('Validate_Lines' || g_line_tbl(I).line_id,1);
1110
1111 x_return_status := FND_API.G_RET_STS_SUCCESS;
1112 OE_MSG_PUB.set_msg_context
1113 ( p_entity_code => 'LINE'
1114 ,p_entity_id => g_line_tbl(I).line_id
1115 ,p_header_id => g_line_tbl(I).header_id
1116 ,p_line_id => g_line_tbl(I).line_id
1117 ,p_orig_sys_document_ref =>
1118 g_line_tbl(I).orig_sys_document_ref
1119 ,p_orig_sys_document_line_ref =>
1120 g_line_tbl(I).orig_sys_line_ref
1121 ,p_orig_sys_shipment_ref =>
1122 g_line_tbl(I).orig_sys_shipment_ref
1123 ,p_change_sequence => g_line_tbl(I).change_sequence
1124 ,p_source_document_id =>
1125 g_line_tbl(I).source_document_id
1126 ,p_source_document_line_id =>
1127 g_line_tbl(I).source_document_line_id
1128 ,p_order_source_id =>
1129 g_line_tbl(I).order_source_id
1130 ,p_source_document_type_id =>
1131 g_line_tbl(I).source_document_type_id);
1132
1133 /*
1134 IF g_line_tbl(I).schedule_status_code is null THEN
1135
1136 oe_debug_pub.add('E1',2);
1137 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1138 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_LOOP_CANT_UPDATE');
1139 OE_MSG_PUB.ADD;
1140 x_return_status := FND_API.G_RET_STS_ERROR;
1141 END IF;
1142 */
1143 IF g_line_tbl(I).cancelled_flag = 'Y' THEN
1144
1145 oe_debug_pub.add('E1-1',2);
1146 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1147 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_LINE_FULLY_CANCELLED');
1148 OE_MSG_PUB.ADD;
1149 x_return_status := FND_API.G_RET_STS_ERROR;
1150 END IF;
1151
1152 IF g_line_tbl(I).shipped_quantity is not null THEN
1153
1154 oe_debug_pub.add('E1-2',2);
1155 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1156 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_LINE_SHIPPED');
1157 OE_MSG_PUB.ADD;
1158 x_return_status := FND_API.G_RET_STS_ERROR;
1159 END IF;
1160
1161 oe_debug_pub.add('Firm_demand_flag :' || g_line_tbl(I).firm_demand_flag,1);
1162
1163 -- Commenting this code as ASCP is doing this already.
1164 /* IF NVL(g_old_line_tbl(I).firm_demand_flag,'N') = 'Y'
1165 AND not oe_globals.equal(g_line_tbl(I).ship_from_org_id,
1166 g_old_line_tbl(I).ship_from_org_id) THEN
1167
1168 oe_debug_pub.add('E2',2);
1169 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1170 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_LOOP_FRMD');
1171 OE_MSG_PUB.ADD;
1172 x_return_status := FND_API.G_RET_STS_ERROR;
1173 END IF; */
1174
1175 IF g_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_SERVICE THEN
1176
1177 oe_debug_pub.add('E3',2);
1178 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1179 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_LOOP_SRV');
1180 OE_MSG_PUB.ADD;
1181 x_return_status := FND_API.G_RET_STS_ERROR;
1182 END IF;
1183
1184 IF g_line_tbl(I).ato_line_id IS NOT NULL THEN
1185 /* IF NOT (g_line_tbl(I).ato_line_id = g_line_tbl(I).line_id OR
1186 g_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_CONFIG) THEN
1187
1188 oe_debug_pub.add('E4',2);
1189 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1190 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_LOOP_NOT_SUP');
1191 OE_MSG_PUB.ADD;
1192 x_return_status := FND_API.G_RET_STS_ERROR;
1193 END IF;
1194 */
1195 IF nvl(g_line_tbl(I).model_remnant_flag,'N') = 'Y' THEN
1196 oe_debug_pub.add('E5',2);
1197 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1198 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_LOOP_NOT_SUP');
1199 OE_MSG_PUB.ADD;
1200 x_return_status := FND_API.G_RET_STS_ERROR;
1201
1202 END IF;
1203 END IF;
1204
1205 IF g_line_tbl(I).line_category_code = 'RETURN' THEN
1206
1207 oe_debug_pub.add('E6',2);
1208 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1209 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_LOOP_RET');
1210 OE_MSG_PUB.ADD;
1211 x_return_status := FND_API.G_RET_STS_ERROR;
1212 END IF;
1213
1214 IF OE_OTA_UTIL.Is_OTA_Line(g_line_tbl(I).order_quantity_uom) THEN
1215
1216 oe_debug_pub.add('E7',2);
1217 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1218 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_LOOP_NOT_SUP');
1219 OE_MSG_PUB.ADD;
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221 END IF;
1222
1223 IF g_line_tbl(I).source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL THEN
1224
1225 oe_debug_pub.add('E8',2);
1226 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1227 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_LOOP_EXT');
1228 OE_MSG_PUB.ADD;
1229 x_return_status := FND_API.G_RET_STS_ERROR;
1230 END IF;
1231
1232 l_scheduling_level_code := OE_SCHEDULE_UTIL.Get_Scheduling_Level
1233 (g_line_tbl(I).header_id,
1234 g_line_tbl(I).line_type_id);
1235
1236 IF l_scheduling_level_code = OE_SCHEDULE_UTIL.SCH_LEVEL_FOUR OR
1237 l_scheduling_level_code = OE_SCHEDULE_UTIL.SCH_LEVEL_FIVE OR
1238 l_scheduling_level_code = OE_SCHEDULE_UTIL.SCH_LEVEL_ONE THEN
1239
1240 oe_debug_pub.add('E9',2);
1241 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1242 FND_MESSAGE.SET_NAME('ONT', 'ONT_SCH_LOOP_LVL');
1243 OE_MSG_PUB.ADD;
1244 x_return_status := FND_API.G_RET_STS_ERROR;
1245
1246
1247 END IF;
1248
1249 IF g_line_tbl(I).arrival_set_id is not null OR
1250 g_line_tbl(I).ship_set_id is not null OR
1251 g_line_tbl(I).ship_model_complete_flag = 'Y' THEN
1252
1253
1254 -- Check all the lines in the set are passed or not.
1255
1256 Validate_set(p_ship_set_id => g_line_tbl(I).ship_set_id,
1257 p_arrival_set_id => g_line_tbl(I).arrival_set_id,
1258 p_top_model_line_id => g_line_tbl(I).top_model_line_id);
1259
1260
1261 END IF;
1262 /*
1263 IF NVL(g_line_tbl(I).re_source_flag,'Y') = 'N' AND
1264 g_line_tbl(I).ship_from_org_id <>
1265 NVL(g_sch_tbl(I).ship_from_org_id,g_line_tbl(I).ship_from_org_id)
1266 THEN
1267 oe_debug_pub.add('E10',2);
1268 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1269 FND_MESSAGE.SET_NAMe('ONT', 'OE_SCH_LOOP_WSH_UPD');
1270 OE_MSG_PUB.ADD;
1271 x_return_status := FND_API.G_RET_STS_ERROR;
1272
1273 END IF;
1274 */
1275 IF Oe_Sys_Parameters.Value('ONT_SCHEDULE_LINE_ON_HOLD') = 'N' THEN
1276
1277 OE_Holds_PUB.Check_Holds
1278 ( p_api_version => 1.0
1279 , p_init_msg_list => FND_API.G_FALSE
1280 , p_commit => FND_API.G_FALSE
1281 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1282 , x_return_status => l_out_return_status
1283 , x_msg_count => l_msg_count
1284 , x_msg_data => l_msg_data
1285 , p_line_id => g_line_tbl(I).line_id
1286 , p_hold_id => NULL
1287 , p_entity_code => NULL
1288 , p_entity_id => NULL
1289 , x_result_out => l_result
1290 );
1291
1292 IF (l_out_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1293 IF l_out_return_status = FND_API.G_RET_STS_ERROR THEN
1294 RAISE FND_API.G_EXC_ERROR;
1295 ELSE
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297 END IF;
1298 END IF;
1299
1300 IF (l_result = FND_API.G_TRUE) THEN
1301 oe_debug_pub.add('E11',2);
1302 g_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1303 FND_MESSAGE.SET_NAME('ONT','OE_SCH_LINE_ON_HOLD');
1304 OE_MSG_PUB.Add;
1305 x_return_status := FND_API.G_RET_STS_ERROR;
1306 END IF;
1307
1308
1309 END IF;
1310
1311 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1312
1313 l_index := find_index(g_line_tbl(I).line_id);
1314 g_sch_tbl(l_index).x_return_status := x_return_status;
1315 END IF;
1316 END LOOP;
1317
1318 oe_debug_pub.add('Existing Validate_Lines',1);
1319
1320 EXCEPTION
1321 WHEN FND_API.G_EXC_ERROR THEN
1322 x_return_status := FND_API.G_RET_STS_ERROR;
1323
1324 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1326
1327 WHEN OTHERS THEN
1328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1329
1330 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1331 THEN
1332 OE_MSG_PUB.Add_Exc_Msg
1333 ( G_PKG_NAME
1334 , 'Validate_Lines'
1335 );
1336 END IF;
1337 END Validate_Lines;
1338
1339 PROCEDURE Validate_set(p_ship_set_id IN NUMBER DEFAULT NULL,
1340 p_arrival_set_id IN NUMBER DEFAULT NULL,
1341 p_top_model_line_id IN NUMBER DEFAULT NULL)
1342 IS
1343
1344 Cursor line_ship_set IS
1345 Select line_id,ato_line_id,item_type_code
1346 From oe_order_lines_all
1347 Where ship_set_id = p_ship_set_id;
1348
1349 Cursor line_arrival_set IS
1350 Select line_id,ato_line_id,item_type_code
1351 From oe_order_lines_all
1352 Where arrival_set_id = p_arrival_set_id;
1353
1354 Cursor line_smc IS
1355 Select line_id,ato_line_id,item_type_code
1356 From oe_order_lines_all
1357 Where top_model_line_id = p_top_model_line_id;
1358
1359 l_result Boolean := TRUE;
1360 l_index Number;
1361
1362 BEGIN
1363
1364 Oe_debug_pub.add('Entering Procedure Validate_set'|| p_ship_set_id,1);
1365 IF p_Ship_set_id is not null THEN
1366
1367 FOR C1 IN line_ship_set LOOP
1368 IF NOT Find_line(C1.line_id) THEN
1369
1370 l_result := FALSE;
1371 EXIT;
1372
1373 END IF;
1374
1375
1376 END LOOP;
1377
1378 ELSIF p_arrival_set_id is not null THEN
1379
1380 FOR C1 IN line_arrival_set LOOP
1381 IF NOT Find_line(C1.line_id) THEN
1382
1383 l_result := FALSE;
1384 EXIT;
1385
1386 END IF;
1387
1388
1389 END LOOP;
1390
1391 ELSE
1392
1393 FOR C1 IN line_smc LOOP
1394
1395 IF NOT Find_line(C1.line_id) THEN
1396
1397 l_result := FALSE;
1398 EXIT;
1399
1400 END IF;
1401
1402
1403 END LOOP;
1404 END IF;
1405
1406 IF NOT l_result THEN
1407
1408 oe_debug_pub.add('E10',2);
1409 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_LOOP_NOT_SUP');
1410 OE_MSG_PUB.ADD;
1411 IF p_ship_set_id IS NOT NULL THEN
1412
1413 FOR J IN 1..g_line_tbl.count LOOP
1414
1415 IF g_line_tbl(J).ship_set_id = p_ship_set_id THEN
1416
1417 g_line_tbl(J).operation := OE_GLOBALS.G_OPR_NONE;
1418 g_line_tbl(J).return_status := FND_API.G_RET_STS_ERROR;
1419
1420 l_index := find_index(g_line_tbl(J).line_id);
1421 g_sch_tbl(l_index).x_return_status := FND_API.G_RET_STS_ERROR;
1422 END IF;
1423
1424 END LOOP;
1425
1426 ELSIF p_arrival_set_id IS NOT NULL THEN
1427
1428 FOR J IN 1..g_line_tbl.count LOOP
1429
1430 IF g_line_tbl(J).arrival_set_id = p_arrival_set_id THEN
1431
1432 g_line_tbl(J).operation := OE_GLOBALS.G_OPR_NONE;
1433 g_line_tbl(J).return_status := FND_API.G_RET_STS_ERROR;
1434 l_index := find_index(g_line_tbl(J).line_id);
1435 g_sch_tbl(l_index).x_return_status := FND_API.G_RET_STS_ERROR;
1436 END IF;
1437
1438 END LOOP;
1439
1440 ELSIF p_top_model_line_id IS NOT NULL THEN
1441
1442 FOR J IN 1..g_line_tbl.count LOOP
1443
1444 IF g_line_tbl(J).top_model_line_id = p_top_model_line_id THEN
1445
1446 g_line_tbl(J).operation := OE_GLOBALS.G_OPR_NONE;
1447 l_index := find_index(g_line_tbl(J).line_id);
1448 g_sch_tbl(l_index).x_return_status := FND_API.G_RET_STS_ERROR;
1449
1450 END IF;
1451
1452 END LOOP;
1453
1454 END IF;
1455
1456 END IF;
1457 EXCEPTION
1458
1459 WHEN OTHERS THEN
1460
1461 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1462 THEN
1463 OE_MSG_PUB.Add_Exc_Msg
1464 ( G_PKG_NAME
1465 , 'Validate_set'
1466 );
1467 END IF;
1468 END Validate_set;
1469
1470 FUNCTION Find_line(p_line_id IN NUMBER)
1471 Return BOOLEAN
1472 IS
1473 --
1474 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1475 --
1476 BEGIN
1477
1478 IF l_debug_level > 0 THEN
1479 oe_debug_pub.add( 'ENTERING FIND_LINE: ' || P_LINE_ID , 1 ) ;
1480 END IF;
1481
1482 FOR J IN 1..g_line_tbl.count LOOP
1483
1484 IF p_line_id = g_line_tbl(J).line_id THEN
1485
1486 IF l_debug_level > 0 THEN
1487 oe_debug_pub.add( ' LINE EXISTS IN THE TABLE' , 1 ) ;
1488 END IF;
1489 RETURN TRUE;
1490 END IF;
1491 END LOOP;
1492
1493
1494
1495 RETURN FALSE;
1496 EXCEPTION
1497 WHEN FND_API.G_EXC_ERROR THEN
1498 RETURN FALSE;
1499
1500 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1501 RETURN FALSE;
1502
1503 WHEN OTHERS THEN
1504
1505 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1506 THEN
1507 OE_MSG_PUB.Add_Exc_Msg
1508 ( G_PKG_NAME
1509 , 'Find_line'
1510 );
1511 END IF;
1512 RETURN FALSE;
1513 END Find_line;
1514
1515
1516 FUNCTION Find_index(p_line_id IN NUMBER)
1517 Return NUMBER
1518 IS
1519 --
1520 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1521 J NUMBER;
1522 --
1523 BEGIN
1524
1525 IF l_debug_level > 0 THEN
1526 oe_debug_pub.add( 'ENTERING FIND_Index: ' || P_LINE_ID , 1 ) ;
1527 END IF;
1528
1529 J := g_sch_tbl.FIRST;
1530 WHILE J IS NOT NULL
1531 LOOP
1532
1533 IF p_line_id = g_sch_tbl(J).line_id THEN
1534
1535 IF l_debug_level > 0 THEN
1536 oe_debug_pub.add( ' J : ' || J, 1 ) ;
1537 END IF;
1538 RETURN J;
1539 END IF;
1540 J := g_sch_tbl.NEXT(J);
1541 END LOOP;
1542
1543
1544
1545 RETURN null;
1546 EXCEPTION
1547 WHEN FND_API.G_EXC_ERROR THEN
1548 RETURN Null;
1549
1550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1551 RETURN Null;
1552
1553 WHEN OTHERS THEN
1554
1555 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1556 THEN
1557 OE_MSG_PUB.Add_Exc_Msg
1558 ( G_PKG_NAME
1559 , 'Find_index'
1560 );
1561 END IF;
1562 RETURN Null;
1563 END Find_index;
1564 Procedure Update_reservation(p_index IN NUMBER,
1565 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1566 IS
1567 BEGIN
1568
1569 IF (NOT OE_GLOBALS.Equal(g_line_tbl(p_index).ship_from_org_id,
1570 g_old_line_tbl(p_index).ship_from_org_id))
1571 OR ( NOT OE_GLOBALS.Equal(g_line_tbl(p_index).inventory_item_id, g_old_line_tbl(p_index).inventory_item_id)
1572 AND nvl(g_line_tbl(p_index).booked_flag, 'N') = 'N'
1573 ) -- OR clause added for ER 6110708
1574 THEN
1575
1576 OE_SCHEDULE_UTIL.Unreserve_Line
1577 (p_line_rec => g_old_line_tbl(p_index),
1578 p_quantity_to_unreserve => g_old_line_tbl(p_index).reserved_quantity,
1579 p_quantity2_to_unreserve => g_old_line_tbl(p_index).reserved_quantity2,-- --bug 16434168/16480604
1580 x_return_status => x_return_status);
1581
1582 OE_SCHEDULE_UTIL.Reserve_Line
1583 (p_line_rec => g_line_tbl(p_index)
1584 ,p_quantity_to_reserve => g_line_tbl(p_index).reserved_quantity
1585 ,x_return_Status => x_return_status);
1586
1587 ELSIF NOT OE_GLOBALS.Equal(g_line_tbl(p_index).schedule_ship_date,
1588 g_old_line_tbl(p_index).schedule_ship_date)
1589 THEN
1590
1591 OE_CONFIG_SCHEDULE_PVT.Update_Reservation
1592 ( p_line_rec => g_line_tbl(p_index)
1593 ,p_old_line_rec => g_old_line_tbl(p_index)
1594 ,x_return_status => x_return_status);
1595
1596 END IF;
1597 EXCEPTION
1598 WHEN FND_API.G_EXC_ERROR THEN
1599 x_return_status := FND_API.G_RET_STS_ERROR;
1600
1601 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1603
1604 WHEN OTHERS THEN
1605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1606
1607 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1608 THEN
1609 OE_MSG_PUB.Add_Exc_Msg
1610 ( G_PKG_NAME
1611 , 'Validate_set'
1612 );
1613 END IF;
1614 END Update_Reservation;
1615
1616 END OE_SCHEDULE_GRP;