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