1 PACKAGE BODY OE_BULK_SCHEDULE_UTIL AS
2 /* $Header: OEBUSCHB.pls 120.5.12020000.2 2012/09/20 05:14:48 rahujain ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_BULK_SCHEDULE_UTIL';
6
7
8 G_INSERT_FLAG NUMBER;
9 G_HEADER_ID NUMBER := null;
10 G_DATE_TYPE VARCHAR2(30) := null;
11
12 TYPE Schedule_Error_Rec_Type IS RECORD
13 (line_index NUMBER
14 ,error_code NUMBER
15 );
16
17 TYPE Schedule_Error_Tbl_Type IS TABLE OF Schedule_Error_Rec_Type
18 INDEX BY BINARY_INTEGER;
19
20 G_SCH_ERROR_TBL Schedule_Error_Tbl_Type;
21
22 -- BYPASS ATP
23 PROCEDURE Inactive_Demand_Scheduling
24 ( p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
25 , x_return_status OUT NOCOPY VARCHAR2
26 );
27
28 PROCEDURE Insert_Error_Messages
29 (p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
30 )
31 IS
32 l_no_source_msg VARCHAR2(2000);
33 l_sch_error_msg VARCHAR2(2000);
34 l_index NUMBER;
35 l_error_code NUMBER;
36 --
37 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
38 --
39 BEGIN
40
41 ---------------------------------------------------------------------
42 -- For order lines that failed scheduling:
43 -- Insert Error Messages
44 ---------------------------------------------------------------------
45 IF l_debug_level > 0 THEN
46 oe_debug_pub.add( 'ERROR COUNT :'||G_SCH_ERROR_TBL.COUNT ) ;
47 END IF;
48
49 l_no_source_msg := FND_MESSAGE.GET_STRING('ONT','OE_SCH_NO_SOURCE');
50 l_sch_error_msg := FND_MESSAGE.GET_STRING('ONT','OE_BULK_SCH_FAILED');
51
52 -- Error codes of 0,-99,150 should be ignored
53 -- Error code of 19 indicates that this is a line in an SMC set where
54 -- one of the other lines in the set could not be scheduled.
55 -- Need not insert a message as message will be populated for
56 -- the lines that failed.
57
58 FOR I IN 1..G_SCH_ERROR_TBL.COUNT LOOP
59 l_index := G_SCH_ERROR_TBL(I).line_index;
60 l_error_code := G_SCH_ERROR_TBL(I).error_code;
61 INSERT INTO OE_PROCESSING_MSGS
62 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
63 ,order_source_id ,original_sys_document_ref
64 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
65 ,attribute_code ,creation_date ,created_by ,last_update_date
66 ,last_updated_by ,last_update_login
67 ,program_application_id ,program_id,program_update_date
68 ,process_activity ,notification_flag ,type
69 ,message_source_code ,language
70 ,message_text
71 ,transaction_id
72 )
73 SELECT
74 OE_Bulk_Order_PVT.G_REQUEST_ID,'LINE' ,NULL
75 ,p_line_rec.line_id(l_index)
76 ,p_line_rec.header_id(l_index)
77 ,p_line_rec.line_id(l_index)
78 ,p_line_rec.order_source_id(l_index)
79 ,p_line_rec.orig_sys_document_ref(l_index)
80 ,p_line_rec.orig_sys_line_ref(l_index)
81 ,p_line_rec.orig_sys_shipment_ref(l_index)
82 ,p_line_rec.change_sequence(l_index)
83 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
84 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
85 ,660 ,NULL ,NULL
86 ,NULL ,NULL ,NULL
87 ,'C' ,USERENV('LANG')
88 ,decode(l_error_code,80,l_no_source_msg
89 , l_sch_error_msg||m.meaning)
90 ,OE_MSG_ID_S.NEXTVAL
91 FROM MFG_LOOKUPS m
92 WHERE l_error_code NOT IN (0,-99,150,19)
93 AND m.lookup_code(+) = l_error_code
94 AND m.lookup_type(+) = 'MTL_DEMAND_INTERFACE_ERRORS';
95 END LOOP;
96
97 EXCEPTION
98 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
99 IF l_debug_level > 0 THEN
100 oe_debug_pub.add( 'UNEXP ERROR , INSERT_ERROR_MESSAGE' ) ;
101 END IF;
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 WHEN OTHERS THEN
104 IF l_debug_level > 0 THEN
105 oe_debug_pub.add( 'OTHERS ERROR , INSERT_ERROR_MESSAGES' ) ;
106 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
107 END IF;
108 OE_BULK_MSG_PUB.Add_Exc_Msg
109 ( G_PKG_NAME
110 , 'Insert_Error_Messages'
111 );
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 END Insert_Error_Messages;
114
115 --BYPASS ATP
116 PROCEDURE Inactive_Demand_Scheduling
117 ( p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
118 , x_return_status OUT NOCOPY VARCHAR2
119 )
120 IS
121 l_order_date_type_code VARCHAR2(30);
122 l_promise_date_flag VARCHAR2(2);
123 I NUMBER;
124 --
125 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
126 --
127 BEGIN
128 IF l_debug_level > 0 THEN
129 oe_debug_pub.add( 'ENTERING INACTIVE_DEMAND_SCHEDULING' , 1 ) ;
130 END IF;
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132 FOR I IN 1..p_line_rec.line_id.count
133 LOOP
134 oe_bulk_msg_pub.set_msg_context
135 ( p_entity_code => 'LINE'
136 ,p_entity_id => p_line_rec.line_id(I)
137 ,p_header_id => p_line_rec.header_id(I)
138 ,p_line_id => p_line_rec.line_id(I)
139 ,p_orig_sys_document_ref => p_line_rec.orig_sys_document_ref(I)
140 ,p_orig_sys_document_line_ref => p_line_rec.orig_sys_line_ref(I)
141 ,p_source_document_id => NULL
142 ,p_source_document_line_id => NULL
143 ,p_order_source_id => p_line_rec.order_source_id(I)
144 ,p_source_document_type_id => NULL );
145
146 l_order_date_type_code :=
147 NVL(Get_Date_Type(p_line_rec.header_id(I)), 'SHIP');
148 IF l_order_date_type_code = 'SHIP' THEN
149 IF p_line_rec.schedule_ship_date(I) IS NOT NULL AND
150 p_line_rec.schedule_ship_date(I) <> FND_API.G_MISS_DATE THEN
151 -- If the user provides a ship_date, or changes the existing, use it
152 p_line_rec.schedule_arrival_date(I) :=
153 p_line_rec.schedule_ship_date(I);
154
155 ELSE
156 -- if the user changed request date, use it
157 p_line_rec.schedule_ship_date(I) := p_line_rec.request_date(I);
158 p_line_rec.schedule_arrival_date(I) := p_line_rec.request_date(I);
159 END IF;
160 ELSE -- Arrival
161 IF p_line_rec.schedule_arrival_date(I) IS NOT NULL AND
162 p_line_rec.schedule_arrival_date(I) <> FND_API.G_MISS_DATE THEN
163 -- If the user provides a arrival_date, or changes the existing, use it
164 p_line_rec.schedule_ship_date(I) :=
165 p_line_rec.schedule_arrival_date(I);
166 ELSE
167 -- if the user changed request date, use it
168 p_line_rec.schedule_ship_date(I) := p_line_rec.request_date(I);
169 p_line_rec.schedule_arrival_date(I) := p_line_rec.request_date(I);
170 END IF;
171 END IF;
172 -- we want this line scheduled, but not visible for demand
173 p_line_rec.visible_demand_flag(I) := 'N';
174 p_line_rec.schedule_status_code(I) := 'SCHEDULED';
175 /*
176 -- Latest Acceptable date violation check (Set to Ignore)
177 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
178 IF OE_SYS_PARAMETERS.value('LATEST_ACCEPTABLE_DATE_FLAG')='I' THEN
179 IF ((l_order_date_type_code = 'SHIP'
180 AND p_line_rec.schedule_ship_date(I)
181 > p_line_rec.latest_acceptable_date(I))
182 OR (l_order_date_type_code = 'ARRIVAL'
183 AND p_line_rec.schedule_arrival_date(I)
184 > p_line_rec.latest_acceptable_date(I))) THEN
185 FND_MESSAGE.SET_NAME('ONT','ONT_SCH_LAD_VIOLATE');
186 OE_BULK_MSG_PUB.Add;
187 END IF;
188 END IF;
189 -- Get the Promise date flag
190 l_promise_date_flag := Oe_sys_Parameters.Value('PROMISE_DATE_FLAG');
191 -- Set the Promise date with schedule ship date
192 IF l_promise_date_flag IN('FS','S') THEN
193 IF l_order_date_type_code = 'SHIP'
194 AND p_line_rec.schedule_ship_date(I) IS NOT NULL THEN
195 p_line_rec.promise_date(I) := p_line_rec.schedule_ship_date(I);
196 ELSIF l_order_date_type_code = 'ARRIVAL'
197 AND p_line_rec.schedule_arrival_date(I) IS NOT NULL THEN
198 p_line_rec.promise_date(I) := p_line_rec.schedule_arrival_date(I);
199 END IF;
200 ELSIF l_promise_date_flag IN('FR','R') THEN -- Set the Promise date with Request Date
201 p_line_rec.promise_date(I) := p_line_rec.request_date(I);
202 END IF;
203 IF Oe_Sys_Parameters.Value('FIRM_DEMAND_EVENTS') = 'SCHEDULE' THEN
204 p_line_rec.firm_demand_flag(I) := 'Y';
205 END IF;
206 END IF;
207 */
208 END LOOP;
209
210 END Inactive_Demand_Scheduling;
211
212 PROCEDURE Extend_MRP_Rec
213 (p_count IN NUMBER
214 ,p_x_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ
215 )
216 IS
217 --
218 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
219 --
220 BEGIN
221
222 p_x_atp_rec.Inventory_Item_Id.extend(p_count);
223 p_x_atp_rec.Source_Organization_Id.extend(p_count);
224 p_x_atp_rec.Identifier.extend(p_count);
225 p_x_atp_rec.Order_Number.extend(p_count);
226 p_x_atp_rec.Calling_Module.extend(p_count);
227 p_x_atp_rec.Customer_Id.extend(p_count);
228 p_x_atp_rec.Customer_Site_Id.extend(p_count);
229 p_x_atp_rec.Destination_Time_Zone.extend(p_count);
230 p_x_atp_rec.Quantity_Ordered.extend(p_count);
231 p_x_atp_rec.Quantity_UOM.extend(p_count);
232 p_x_atp_rec.Requested_Ship_Date.extend(p_count);
233 p_x_atp_rec.Requested_Arrival_Date.extend(p_count);
234 p_x_atp_rec.Earliest_Acceptable_Date.extend(p_count);
235 p_x_atp_rec.Latest_Acceptable_Date.extend(p_count);
236 p_x_atp_rec.Delivery_Lead_Time.extend(p_count);
237 p_x_atp_rec.Atp_Lead_Time.extend(p_count);
238 p_x_atp_rec.Freight_Carrier.extend(p_count);
239 p_x_atp_rec.Ship_Method.extend(p_count);
240 p_x_atp_rec.Demand_Class.extend(p_count);
241 p_x_atp_rec.Ship_Set_Name.extend(p_count);
242 p_x_atp_rec.Arrival_Set_Name.extend(p_count);
243 p_x_atp_rec.Override_Flag.extend(p_count);
244 p_x_atp_rec.Action.extend(p_count);
245 p_x_atp_rec.ship_date.extend(p_count);
246 p_x_atp_rec.Available_Quantity.extend(p_count);
247 p_x_atp_rec.Requested_Date_Quantity.extend(p_count);
248 p_x_atp_rec.Group_Ship_Date.extend(p_count);
249 p_x_atp_rec.Group_Arrival_Date.extend(p_count);
250 p_x_atp_rec.Vendor_Id.extend(p_count);
251 p_x_atp_rec.Vendor_Site_Id.extend(p_count);
252 p_x_atp_rec.Insert_Flag.extend(p_count);
253 p_x_atp_rec.Error_Code.extend(p_count);
254 p_x_atp_rec.Message.extend(p_count);
255 p_x_atp_rec.Old_Source_Organization_Id.extend(p_count);
256 p_x_atp_rec.Old_Demand_Class.extend(p_count);
257 p_x_atp_rec.oe_flag.extend(p_count);
258 p_x_atp_rec.ato_delete_flag.extend(p_count);
259 p_x_atp_rec.attribute_01.extend(p_count);
260 p_x_atp_rec.attribute_05.extend(p_count);
261 p_x_atp_rec.substitution_typ_code.extend(p_count); --BUG 4494602
262 p_x_atp_rec.req_item_detail_flag.extend(p_count); --BUG 4494602
263 EXCEPTION
264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
265 IF l_debug_level > 0 THEN
266 oe_debug_pub.add( 'UNEXP ERROR , EXTEND_MRP_REC' ) ;
267 END IF;
268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
269 WHEN OTHERS THEN
270 IF l_debug_level > 0 THEN
271 oe_debug_pub.add( 'OTHERS ERROR , EXTEND_MRP_REC' ) ;
272 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
273 END IF;
274 OE_BULK_MSG_PUB.Add_Exc_Msg
275 ( G_PKG_NAME
276 , 'Extend_MRP_Rec'
277 );
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 END Extend_MRP_Rec;
280
281 FUNCTION Get_Lead_Time
282 (
283 p_ato_index IN NUMBER
284 ,p_line_rec IN OE_WSH_BULK_GRP.LINE_REC_TYPE
285
286 )
287 RETURN NUMBER
288 IS
289 l_model_ordered_quantity NUMBER := 0;
290 l_model_order_qty_uom NUMBER := 0;
291 primary_model_qty NUMBER := 0;
292 st_lead_time NUMBER := 0;
293 db_full_lead_time NUMBER := 0;
294 db_fixed_lead_time NUMBER := 0;
295 db_variable_lead_time NUMBER := 0;
296 --db_primary_uom_code VARCHAR2(3);
297 --db_model_item_id NUMBER := 0;
298 --db_line_unit_code VARCHAR2(3);
299 --
300 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
301 --
302 l_org_id NUMBER;
303 l_c_index NUMBER;
304
305 BEGIN
306
307 IF l_debug_level > 0 THEN
308 oe_debug_pub.add('Entering OE_BULK_SCHEDULE_UTIL.Get_Lead_Time');
309
310 oe_debug_pub.add( 'ATO LINE IS ' ||
311 p_line_rec.line_id(p_ato_index) , 1 ) ;
312
313 oe_debug_pub.add( 'SHIP FROM IS ' ||
314 p_line_rec.ship_from_org_id(p_ato_index), 1 ) ;
315 END IF;
316
317 l_org_id := nvl(p_line_rec.ship_from_org_id(p_ato_index),
318 OE_BULK_ORDER_PVT.G_ITEM_ORG);
319
320 l_c_index := OE_BULK_CACHE.Load_Item
321 (p_key1 => p_line_rec.inventory_item_id(p_ato_index)
322 ,p_key2 => l_org_id
323 ,p_default_attributes => 'Y');
324
325
326 db_full_lead_time := OE_BULK_CACHE.G_ITEM_TBL(l_c_index).full_lead_time;
327 db_fixed_lead_time := OE_BULK_CACHE.G_ITEM_TBL(l_c_index).fixed_lead_time;
328 db_variable_lead_time :=
329 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).variable_lead_time;
330 primary_model_qty := p_line_rec.ordered_quantity(p_ato_index);
331
332 st_lead_time := ceil( nvl(db_fixed_lead_time,0) +
333 nvl(db_variable_lead_time,0)
334 * nvl(primary_model_qty,0));
335 IF nvl(db_full_lead_time,0) > nvl(st_lead_time,0) THEN
336 st_lead_time := ceil(db_full_lead_time);
337 END IF;
338
339 IF l_debug_level > 0 THEN
340 oe_debug_pub.add('Exiting OE_BULK_SCHEDULE_UTIL.Get_Lead_Time');
341 oe_debug_pub.add('Return st_lead_time :'|| st_lead_time);
342 END IF;
343
344 RETURN st_lead_time;
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 IF l_debug_level > 0 THEN
349 oe_debug_pub.add('Exiting OE_BULK_SCHEDULE_UTIL.Get_Lead_Time');
350 oe_debug_pub.add('No Data Found, Return value = 0 ');
351 END IF;
352
353 RETURN 0;
354 WHEN OTHERS THEN
355 IF l_debug_level > 0 THEN
356 oe_debug_pub.add('Exiting OE_BULK_SCHEDULE_UTIL.Get_Lead_Time');
357 oe_debug_pub.add('OTHERS, Return value = 0 ');
358 END IF;
359
360 RETURN 0;
361 END Get_Lead_Time;
362
363
364 PROCEDURE Add_MRP_Rec
365 (p_line_index IN NUMBER
366 ,p_curr_ato_index IN NUMBER
367 ,p_header_index IN NUMBER
368 ,p_atp_index IN NUMBER
369 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
370 ,p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
371 ,p_x_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ
372 )
373 IS
374 --
375 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
376 --
377 BEGIN
378
379 IF l_debug_level > 0 THEN
380 oe_debug_pub.add('Entering OE_BULK_SCHEDULE_UTIL.Add_MRP_Rec');
381 END IF;
382
383 p_x_atp_rec.substitution_typ_code(p_atp_index) := 4; --BUG 4494602
384 p_x_atp_rec.req_item_detail_flag(p_atp_index) := 2; --BUG 4494602
385
386 p_x_atp_rec.Inventory_Item_Id(p_atp_index) :=
387 p_line_rec.inventory_item_id(p_line_index);
388 p_x_atp_rec.Source_Organization_Id(p_atp_index) :=
389 p_line_rec.ship_from_org_id(p_line_index);
390 p_x_atp_rec.Identifier(p_atp_index) :=
391 p_line_rec.line_id(p_line_index);
392 -- p_x_atp_rec.Order_Number(p_atp_index) :=
393 -- p_header_rec.order_number(p_header_index);
394 p_x_atp_rec.Calling_Module(p_atp_index) := 660;
395 p_x_atp_rec.Customer_Id(p_atp_index) :=
396 p_line_rec.sold_to_org_id(p_line_index);
397 p_x_atp_rec.Customer_Site_Id(p_atp_index) :=
398 p_line_rec.ship_to_org_id(p_line_index);
399 p_x_atp_rec.Destination_Time_Zone(p_atp_index) :=
400 p_line_rec.item_type_code(p_line_index); -- Destination_Time_Zone
401 p_x_atp_rec.Quantity_Ordered(p_atp_index) :=
402 p_line_rec.ordered_quantity(p_line_index);
403 p_x_atp_rec.Quantity_UOM(p_atp_index) :=
404 p_line_rec.order_quantity_uom(p_line_index);
405
406 if p_header_rec.order_date_type_code(p_header_index) = 'ARRIVAL' then
407 p_x_atp_rec.Requested_Arrival_Date(p_atp_index) :=
408 nvl(p_line_rec.schedule_arrival_date(p_line_index),p_line_rec.request_date(p_line_index));
409 if p_line_rec.ship_model_complete_flag(p_line_index) = 'Y' then
410 p_x_atp_rec.Arrival_Set_Name(p_atp_index) :=
411 p_line_rec.top_model_line_id(p_line_index);
412 end if;
413 else
414 p_x_atp_rec.Requested_Ship_Date(p_atp_index) :=
415 nvl(p_line_rec.schedule_ship_date(p_line_index),p_line_rec.request_date(p_line_index));
416 if p_line_rec.ship_model_complete_flag(p_line_index) = 'Y' then
417 p_x_atp_rec.Ship_Set_Name(p_atp_index) :=
418 p_line_rec.top_model_line_id(p_line_index);
419 end if;
420 end if;
421
422 p_x_atp_rec.Latest_Acceptable_Date(p_atp_index) := p_line_rec.latest_acceptable_date(p_line_index);
423 p_x_atp_rec.Atp_Lead_Time(p_atp_index) := 0; -- ATP_Lead_Time;
424 p_x_atp_rec.Ship_Method(p_atp_index) := p_line_rec.shipping_method_code(p_line_index);
425 p_x_atp_rec.Demand_Class(p_atp_index) := p_line_rec.demand_class_code(p_line_index);
426 p_x_atp_rec.Action(p_atp_index) := 110; -- Action (OESCH_ACT_SCHEDULE)
427 p_x_atp_rec.Insert_Flag(p_atp_index) := G_INSERT_FLAG; -- Insert_Flag
428 p_x_atp_rec.oe_flag(p_atp_index) :='N';
429
430
431 IF p_line_rec.ato_line_id(p_line_index) IS NOT NULL AND
432 p_line_rec.ato_line_id(p_line_index) <> p_line_rec.line_id(p_line_index)
433 THEN
434
435 p_x_atp_rec.atp_lead_time(p_atp_index) :=
436 Get_Lead_Time(
437 p_ato_index => p_curr_ato_index,
438 p_line_rec => p_line_rec
439 );
440 END IF;
441
442 IF p_line_rec.top_model_line_id(p_line_index) IS NOT NULL THEN
443
444 p_x_atp_rec.Included_item_flag(p_atp_index) := 1;
445 p_x_atp_rec.top_model_line_id(p_atp_index) :=
446 p_line_rec.top_model_line_id(p_line_index);
447 p_x_atp_rec.ato_model_line_id(p_atp_index) :=
448 p_line_rec.ato_line_id(p_line_index);
449 p_x_atp_rec.parent_line_id(p_atp_index) :=
450 p_line_rec.link_to_line_id(p_line_index);
451 p_x_atp_rec.validation_org(p_atp_index) :=
452 OE_BULK_ORDER_PVT.G_ITEM_ORG;
453 p_x_atp_rec.component_code(p_atp_index) :=
454 p_line_rec.component_code(p_line_index);
455 p_x_atp_rec.component_sequence_id(p_atp_index) :=
456 p_line_rec.component_sequence_id(p_line_index);
457 p_x_atp_rec.line_number(p_atp_index) :=
458 p_line_rec.line_number(p_line_index)||'.'||
459 p_line_rec.shipment_number(p_line_index)||'.'||
460 p_line_rec.option_number(p_line_index)||'.'||
461 p_line_rec.component_number(p_line_index);
462
463 END IF;
464 IF l_debug_level > 0 THEN
465 oe_debug_pub.add('Exiting OE_BULK_SCHEDULE_UTIL.Add_MRP_Rec');
466 END IF;
467
468 EXCEPTION
469 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
470 IF l_debug_level > 0 THEN
471 oe_debug_pub.add( 'UNEXP ERROR , ADD_MRP_REC' ) ;
472 END IF;
473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
474 WHEN OTHERS THEN
475 IF l_debug_level > 0 THEN
476 oe_debug_pub.add( 'OTHERS ERROR , ADD_MRP_REC' ) ;
477 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
478 END IF;
479 OE_BULK_MSG_PUB.Add_Exc_Msg
480 ( G_PKG_NAME
481 , 'Add_MRP_Rec'
482 );
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 END Add_MRP_Rec;
485
486
487 PROCEDURE Load_MRP_Request
488 (p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
489 ,p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
490 ,p_x_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ
491 ,p_x_atp_line_map_rec IN OUT NOCOPY OE_WSH_BULK_GRP.T_NUM
492 )
493 IS
494 I NUMBER := 1;
495 J NUMBER := 1;
496 K NUMBER := 1;
497 l_line_count NUMBER;
498 l_header_count NUMBER;
499 l_ii_index NUMBER;
500 l_ii_last_index NUMBER;
501 l_curr_ato_index NUMBER; --
502 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
503 --
504 l_return_status VARCHAR2(10);
505 l_result BOOLEAN;
506 BEGIN
507 IF l_debug_level > 0 THEN
508 oe_debug_pub.add('Entering OE_BULK_SCHEDULE_UTIL.Load_MRP_Request');
509 END IF;
510
511 -- p_x_atp_line_map_rec stores the mapping of index positions
512 -- of order line in the MRP rec.
513 -- For e.g. p_x_atp_line_map_rec(2) = 5 indicates that
514 -- order line at index position 5 in p_line_rec is stored
515 -- at index position 2 in p_x_atp_rec
516
517 p_x_atp_line_map_rec.DELETE;
518 --commented out for bug3675870
519 /* Extend_MRP_Rec(p_count => OE_BULK_ORDER_PVT.G_SCH_COUNT
520 ,p_x_atp_rec => p_x_atp_rec
521 );*/
522
523 --for bug 3675870
524 MSC_SATP_FUNC.Extend_Atp(p_atp_tab=>p_x_atp_rec,
525 x_return_status => l_return_status,
526 p_index => OE_BULK_ORDER_PVT.G_SCH_COUNT);
527 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
528 oe_debug_pub.add('Error while extending ATP record');
529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
530 end if ;
531 p_x_atp_line_map_rec.extend(OE_BULK_ORDER_PVT.G_SCH_COUNT);
532
533 IF nvl(FND_PROFILE.VALUE('MRP_ATP_CALC_SD'),'N') = 'Y' THEN
534 G_INSERT_FLAG := 1;
535 ELSE
536 G_INSERT_FLAG := 0;
537 END IF;
538
539 l_line_count := p_line_rec.line_id.count;
540 l_header_count := p_line_rec.header_id.count;
541
542 IF l_debug_level > 0 THEN
543 oe_debug_pub.add( 'LINE COUNT :'||L_LINE_COUNT ) ;
544 END IF;
545 <<START_OF_LOOP>>
546 WHILE I <= l_line_count
547 AND p_line_rec.item_type_code(I) <> 'INCLUDED'
548 LOOP
549
550 IF l_debug_level > 0 THEN
551 oe_debug_pub.add( 'I :'||I ) ;
552 oe_debug_pub.add( 'J :'||J ) ;
553 END IF;
554
555 -- Line is NOT eligible for scheduling
556 IF p_line_rec.schedule_status_code(I) IS NULL
557 OR p_line_rec.schedule_status_code(I) = 'SCHEDULED' THEN --Bug 14180333
558 I := I+1;
559 GOTO START_OF_LOOP;
560 END IF;
561
562 -- Find index position of this line's header in p_header_rec
563 /* No longer needed as the header_index is populated on p_line_rec
564 WHILE K <= l_header_count LOOP
565 IF p_header_rec.header_id(K) = p_line_rec.header_id(I) THEN
566 EXIT;
567 END IF;
568 K := K+1;
569 END LOOP;
570 */
571 k := p_line_rec.header_index(I);
572
573 -- Add check here for HOLDS if the profile option schedule lines on hold is
574 -- False.
575
576 IF OE_BULK_ORDER_PVT.G_SCHEDULE_LINE_ON_HOLD = 'N' AND
577 p_line_rec.item_type_code(I) <> 'STANDARD'
578 THEN
579 IF l_debug_level > 0 THEN
580 oe_debug_pub.add( 'Calling check for holds :'||J ) ;
581 END IF;
582 l_result := OE_Bulk_Holds_PVT.Check_For_Holds(
583 p_header_id => p_line_rec.header_id(I),
584 p_line_id => p_line_rec.line_id(I),
585 p_line_index => I,
586 p_header_index => K,
587 p_top_model_line_index => p_line_rec.top_model_line_index(I),
588 p_ship_model_complete_flag => p_line_rec.ship_model_complete_flag(I),
589 p_ato_line_index => p_line_rec.ato_line_index(I),
590 p_ii_parent_line_index => NULL
591 );
592 IF l_debug_level > 0 THEN
593 oe_debug_pub.add('After Calling check for holds ');
594 END IF;
595
596 -- If found on hold then do not schedule the line.
597 IF l_result THEN
598
599 -- Check if the current line has included items and mark them to be
600 -- not scheduled.
601
602 IF p_line_rec.item_type_code(I) In ('MODEL', 'CLASS') AND
603 p_line_rec.ii_start_index(I) IS NOT NULL THEN
604
605 l_ii_last_index := p_line_rec.ii_start_index(I)
606 + p_line_rec.ii_count(I) - 1;
607 l_ii_index := p_line_rec.ii_start_index(I);
608
609 -- loop over included items for this line
610 WHILE l_ii_index <= l_ii_last_index LOOP
611
612 IF p_line_rec.schedule_status_code(l_ii_index) IS NOT NULL
613 THEN
614 p_line_rec.schedule_status_code(l_ii_index) := NULL;
615 END IF;
616 l_ii_index := l_ii_index + 1;
617 END LOOP;
618 END IF;
619 p_line_rec.schedule_status_code(I) := NULL;
620 I := I+1;
621 GOTO START_OF_LOOP;
622 END IF;
623
624 END IF;
625
626 l_curr_ato_index := p_line_rec.ato_line_index(I);
627
628 IF l_debug_level > 0 THEN
629 oe_debug_pub.add( 'The current ATO index is:'||l_curr_ato_index ) ;
630 END IF;
631
632 -- line should not be scheduled if
633 -- any included item is on hold
634 IF p_line_rec.item_type_code(I) IN ( 'KIT', 'CLASS', 'MODEL')
635 AND p_line_rec.schedule_status_code(I) = 'II_ON_HOLD'
636 THEN
637 -- Clear out the schedule status on kit line but
638 -- go on to add mrp records for included items that
639 -- are not on hold in this kit
640 p_line_rec.schedule_status_code(I) := NULL;
641 ELSE
642 IF l_debug_level > 0 THEN
643 oe_debug_pub.add('Calling add_mrp_rec ') ;
644 END IF;
645 Add_MRP_Rec(p_line_index => I
646 ,p_curr_ato_index => l_curr_ato_index
647 ,p_header_index => K
648 ,p_atp_index => J
649 ,p_line_rec => p_line_rec
650 ,p_header_rec => p_header_rec
651 ,p_x_atp_Rec => p_x_atp_rec
652 );
653 IF l_debug_level > 0 THEN
654 oe_debug_pub.add('After add_mrp_rec ') ;
655 END IF;
656 p_x_atp_line_map_rec(J) := I;
657 J := J+1;
658 END IF;
659
660 IF p_line_rec.item_type_code(I) In ('KIT', 'MODEL', 'CLASS') AND
661 p_line_rec.ii_start_index(I) IS NOT NULL THEN
662
663 l_ii_last_index := p_line_rec.ii_start_index(I)
664 + p_line_rec.ii_count(I) - 1;
665 l_ii_index := p_line_rec.ii_start_index(I);
666 -- loop over included items for this kit
667 WHILE l_ii_index <= l_ii_last_index LOOP
668 -- only add included items that need to be scheduled.
669 -- For non-SMCs, schedule status for iis on hold will be null
670 -- , all other lines should be scheduled
671
672 IF p_line_rec.schedule_status_code(l_ii_index) IS NOT NULL THEN
673
674 l_result := FALSE;
675
676 -- If Schedule Lines on holds is False then
677 IF OE_BULK_ORDER_PVT.G_SCHEDULE_LINE_ON_HOLD = 'N' THEN
678
679 -- Call check for holds
680 l_result := OE_Bulk_Holds_PVT.Check_For_Holds(
681 p_header_id => p_line_rec.header_id(l_ii_index),
682 p_line_id => p_line_rec.line_id(l_ii_index),
683 p_line_index => l_ii_index,
684 p_header_index => K,
685 p_top_model_line_index =>
686 p_line_rec.top_model_line_index(l_ii_index),
687 p_ship_model_complete_flag =>
688 p_line_rec.ship_model_complete_flag(l_ii_index),
689 p_ato_line_index => NULL,
690 p_ii_parent_line_index => I
691 );
692 END IF;
693
694 -- If found on hold then do not schedule the line.
695 IF l_result THEN
696 p_line_rec.schedule_status_code(I) := NULL;
697 ELSE
698 IF l_debug_level > 0 THEN
699 oe_debug_pub.add('calling add_mrp_rec for II ') ;
700 END IF;
701
702 Add_MRP_Rec(
703 p_line_index => l_ii_index
704 ,p_curr_ato_index => l_curr_ato_index
705 ,p_header_index => K
706 ,p_atp_index => J
707 ,p_line_rec => p_line_rec
708 ,p_header_rec => p_header_rec
709 ,p_x_atp_Rec => p_x_atp_rec
710 );
711 IF l_debug_level > 0 THEN
712 oe_debug_pub.add('After calling add_mrp_rec for II ') ;
713 END IF;
714 p_x_atp_line_map_rec(J) := l_ii_index;
715 J := J+1;
716 END IF;
717 END IF; -- IF p_line_rec.schedule_status_code(
718 l_ii_index := l_ii_index + 1;
719 END LOOP;
720
721 END IF; -- End IF for KITS
722
723 I := I+1;
724
725 END LOOP; -- End of loop over line record
726
727 If l_debug_level > 0 THEN
728 oe_debug_pub.add('Exiting OE_BULK_SCHEDULE_UTIL.Load_MRP_Request');
729 END IF;
730
731
732 EXCEPTION
733 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
734 oe_debug_pub.add('Unexp Error, Load_MRP_Request');
735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
736 WHEN OTHERS THEN
737 oe_debug_pub.add('Others Error, Load_MRP_Request');
738 oe_debug_pub.add(substr(sqlerrm,1,240));
739 OE_BULK_MSG_PUB.Add_Exc_Msg
740 ( G_PKG_NAME
741 , 'Load_MRP_Request'
742 );
743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
744 END Load_MRP_Request;
745
746 PROCEDURE Update_Line_Rec
747 (p_line_index IN NUMBER
748 ,p_atp_index IN NUMBER
749 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
750 ,p_x_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ
751 )
752 IS
753 l_time_to_ship NUMBER;
754 l_c_index NUMBER;
755 l_on_generic_hold BOOLEAN := FALSE;
756 l_on_booking_hold BOOLEAN := FALSE;
757 l_on_scheduling_hold BOOLEAN := FALSE;
758 l_error_index NUMBER;
759 l_promise_date_flag VARCHAR2(2);
760 l_order_date_type_code VARCHAR2(30);
761 l_hold_ii_flag VARCHAR2(1);
762 --
763 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
764 --
765 l_line_rec_for_hold OE_Order_PUB.Line_Rec_Type; --ER#7479609
766 l_header_rec_for_hold OE_Order_PUB.Header_Rec_Type; --ER#7479609
767 BEGIN
768
769 if l_debug_level > 0 then
770
771 oe_debug_pub.add('Line Index :'||p_line_index);
772 oe_debug_pub.add('ATP Index :'||p_atp_index);
773 oe_debug_pub.add('Ship Set :'||p_x_atp_rec.ship_set_name(p_atp_index));
774 oe_debug_pub.add('Error Code :'||p_x_atp_rec.error_code(p_atp_index));
775
776 end if;
777
778 --bug5880565
779 oe_bulk_msg_pub.set_msg_context
780 ( p_entity_code => 'LINE'
781 ,p_entity_id => p_line_rec.line_id(p_line_index)
782 ,p_header_id => p_line_rec.header_id(p_line_index)
783 ,p_line_id => p_line_rec.line_id(p_line_index)
784 ,p_orig_sys_document_ref => p_line_rec.orig_sys_document_ref(p_line_index)
785 ,p_orig_sys_document_line_ref => p_line_rec.orig_sys_line_ref(p_line_index)
786 ,p_source_document_id => NULL
787 ,p_source_document_line_id => NULL
788 ,p_order_source_id => p_line_rec.order_source_id(p_line_index)
789 ,p_source_document_type_id => NULL );
790
791
792
793 -- NOTE: Changes to OPM fields due to Ship From update
794 -- or Changes to Tax Date due to Schedule Ship Date update
795 -- are not done here as BULK does not support OPM or Tax Calculation
796
797 IF p_x_atp_rec.error_code(p_atp_index) IN (0,-99,150) THEN
798
799 -- Update warehouse if ATP check returns a different warehouse
800 IF nvl(p_line_rec.ship_from_org_id(p_line_index),-1)
801 <> nvl(p_x_atp_rec.source_organization_id(p_atp_index),-1)
802 THEN
803 p_line_rec.ship_from_org_id(p_line_index) := p_x_atp_rec.source_organization_id(p_atp_index);
804 /*ER#7479609 start
805 OE_Bulk_Holds_PVT.Evaluate_Holds(
806 p_header_id => p_line_rec.header_id(p_line_index),
807 p_line_id => p_line_rec.line_id(p_line_index),
808 p_line_number => p_line_rec.line_number(p_line_index),
809 p_sold_to_org_id => p_line_rec.sold_to_org_id(p_line_index),
810 p_inventory_item_id => p_line_rec.inventory_item_id(p_line_index),
811 p_ship_from_org_id => p_line_rec.ship_from_org_id(p_line_index),
812 p_invoice_to_org_id => p_line_rec.invoice_to_org_id(p_line_index),
813 p_ship_to_org_id => p_line_rec.ship_to_org_id(p_line_index),
814 p_top_model_line_id => p_line_rec.top_model_line_id(p_line_index),
815 p_ship_set_name => NULL,
816 p_arrival_set_name => NULL,
817 p_check_only_warehouse_holds => TRUE,
818 p_on_generic_hold => l_on_generic_hold,
819 p_on_booking_hold => l_on_booking_hold,
820 p_on_scheduling_hold => l_on_scheduling_hold
821 );
822 ER#7479609 end*/
823
824 --ER#7479609 start
825 BEGIN
826 SELECT order_type_id
827 INTO l_header_rec_for_hold.order_type_id
828 FROM OE_ORDER_HEADERS_ALL
829 WHERE header_id=p_line_rec.header_id(p_line_index);
830 EXCEPTION
831 WHEN OTHERS THEN
832 l_header_rec_for_hold.order_type_id := NULL;
833 END;
834
835 l_line_rec_for_hold.header_id := p_line_rec.header_id(p_line_index);
836 l_line_rec_for_hold.line_id := p_line_rec.line_id(p_line_index);
837 l_line_rec_for_hold.line_number := p_line_rec.line_number(p_line_index);
838 l_line_rec_for_hold.sold_to_org_id := p_line_rec.sold_to_org_id(p_line_index);
839 l_line_rec_for_hold.inventory_item_id := p_line_rec.inventory_item_id(p_line_index);
840 l_line_rec_for_hold.ship_from_org_id := p_line_rec.ship_from_org_id(p_line_index);
841 l_line_rec_for_hold.invoice_to_org_id := p_line_rec.invoice_to_org_id(p_line_index);
842 l_line_rec_for_hold.ship_to_org_id := p_line_rec.ship_to_org_id(p_line_index);
843 l_line_rec_for_hold.top_model_line_id := p_line_rec.top_model_line_id(p_line_index);
844 l_line_rec_for_hold.price_list_id := p_line_rec.price_list_id(p_line_index);
845 l_line_rec_for_hold.creation_date := to_char(sysdate,'DD-MON-RRRR');
846 l_line_rec_for_hold.shipping_method_code := p_line_rec.shipping_method_code(p_line_index);
847 l_line_rec_for_hold.deliver_to_org_id := p_line_rec.deliver_to_org_id(p_line_index);
848 l_line_rec_for_hold.source_type_code := p_line_rec.source_type_code(p_line_index);
849 l_line_rec_for_hold.line_type_id := p_line_rec.line_type_id(p_line_index);
850 l_line_rec_for_hold.payment_term_id := p_line_rec.payment_term_id(p_line_index);
851 l_line_rec_for_hold.created_by := NVL(FND_GLOBAL.USER_ID, -1);
852
853
854 OE_Bulk_Holds_PVT.Evaluate_Holds(
855 p_header_rec => l_header_rec_for_hold,
856 p_line_rec => l_line_rec_for_hold,
857 p_on_generic_hold => l_on_generic_hold,
858 p_on_booking_hold => l_on_booking_hold,
859 p_on_scheduling_hold => l_on_scheduling_hold
860 );
861 --ER#7479609 end
862 -- Also cache EDI attributes for the new ship from
863 l_c_index := OE_Bulk_Cache.Load_Ship_From
864 (p_key => p_line_rec.ship_from_org_id(p_line_index)
865 );
866 END IF;
867
868 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
869 IF p_x_atp_rec.group_ship_date(p_atp_index) IS NOT NULL
870 THEN
871 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.group_ship_date(p_atp_index);
872 ELSE
873 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.ship_date(p_atp_index);
874 END IF;
875
876 IF p_x_atp_rec.group_arrival_date(p_atp_index) IS NOT NULL THEN
877 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.group_arrival_date(p_atp_index);
878 ELSE
879 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.arrival_date(p_atp_index);
880 END IF;
881 ELSE
882 -- Group_ship_date/group_arrival_date - use these also!
883 if p_x_atp_rec.ship_set_name(p_atp_index) is not null then
884 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.group_ship_date(p_atp_index);
885 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.group_ship_date(p_atp_index)
886 + nvl(p_x_atp_rec.delivery_lead_time(p_atp_index),0);
887 elsif p_x_atp_rec.arrival_set_name(p_atp_index) is not null then
888 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.group_arrival_date(p_atp_index)
889 - nvl(p_x_atp_rec.delivery_lead_time(p_atp_index),0);
890 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.group_arrival_date(p_atp_index);
891 else
892 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.ship_date(p_atp_index);
893 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.ship_date(p_atp_index)
894 + nvl(p_x_atp_rec.delivery_lead_time(p_atp_index),0);
895 end if;
896 END IF;
897 -- Pack J
898 -- Latest Acceptable date violation check (Set to Ignore)
899 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
900 IF OE_SYS_PARAMETERS.value('LATEST_ACCEPTABLE_DATE_FLAG')='I' THEN
901 l_order_date_type_code := NVL(Get_Date_Type(p_line_rec.header_id(p_line_index)),'SHIP');
902 IF ((l_order_date_type_code = 'SHIP'
903 AND p_line_rec.schedule_ship_date(p_line_index)
904 > p_line_rec.latest_acceptable_date(p_line_index))
905 OR (l_order_date_type_code = 'ARRIVAL'
906 AND p_line_rec.schedule_arrival_date(p_line_index)
907 > p_line_rec.latest_acceptable_date(p_line_index))) THEN
908 FND_MESSAGE.SET_NAME('ONT','ONT_SCH_LAD_VIOLATE');
909 OE_BULK_MSG_PUB.Add;
910 END IF;
911 END IF;
912
913 -- Get the Promise date flag
914 l_promise_date_flag := Oe_sys_Parameters.Value('PROMISE_DATE_FLAG');
915 -- Set the Promise date with schedule ship date
916 IF l_promise_date_flag IN('FS','S') THEN
917 IF l_order_date_type_code = 'SHIP'
918 AND p_line_rec.schedule_ship_date(p_line_index) IS NOT NULL THEN
919 p_line_rec.promise_date(p_line_index) := p_line_rec.schedule_ship_date(p_line_index);
920 ELSIF l_order_date_type_code = 'ARRIVAL'
921 AND p_line_rec.schedule_arrival_date(p_line_index) IS NOT NULL THEN
922 p_line_rec.promise_date(p_line_index) := p_line_rec.schedule_arrival_date(p_line_index);
923 END IF;
924 ELSIF l_promise_date_flag IN('FR','R') THEN -- Set the Promise date with Request Date
925 p_line_rec.promise_date(p_line_index) := p_line_rec.request_date(p_line_index);
926 END IF;
927 END IF;
928
929 p_line_rec.delivery_lead_time(p_line_index) := p_x_atp_rec.delivery_lead_time(p_atp_index);
930 p_line_rec.mfg_lead_time(p_line_index) := p_x_atp_rec.atp_lead_time(p_atp_index);
931 p_line_rec.shipping_method_code(p_line_index) := nvl(p_x_atp_rec.ship_method(p_atp_index),
932 p_line_rec.shipping_method_code(p_line_index));
933 p_line_rec.schedule_status_code(p_line_index) := 'SCHEDULED';
934
935 IF Oe_Sys_Parameters.Value('FIRM_DEMAND_EVENTS') = 'SCHEDULE'
936 AND OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
937 p_line_rec.firm_demand_flag(p_line_index) := 'Y';
938 END IF;
939
940 -- Bug 2737274
941 -- Check if attribute_05 exists as the attribute may not be available
942 -- on out ATP rec from the MRP call.
943 -- Without the exists check, error 'subscript beyond count' can result
944 IF p_x_atp_rec.attribute_05.EXISTS(p_atp_index) THEN
945 p_line_rec.visible_demand_flag(p_line_index) := nvl(p_x_atp_rec.attribute_05(p_atp_index),'Y');
946 ELSE
947 p_line_rec.visible_demand_flag(p_line_index) := 'Y';
948 END IF;
949
950 --p_line_rec.last_update_date(p_line_index) := sysdate;
951 --p_line_rec.last_updated_by(p_line_index) := FND_GLOBAL.USER_ID;
952 --p_line_rec.last_update_login(p_line_index) := FND_GLOBAL.LOGIN_ID;
953 p_line_rec.lock_control(p_line_index) := (p_line_rec.lock_control(p_line_index) + 1);
954
955 IF OE_BULK_ORDER_PVT.G_RESERVATION_TIME_FENCE IS NOT NULL THEN
956 -- If ship date is within reservation time fence, populate a message
957 l_time_to_ship := to_number(trunc(p_line_rec.schedule_ship_date(p_line_index)) - trunc(SYSDATE));
958
959 BEGIN
960 IF l_time_to_ship < 0 THEN
961 NULL;
962 ELSIF l_time_to_ship <= to_number(OE_BULK_ORDER_PVT.G_RESERVATION_TIME_FENCE) THEN
963 FND_MESSAGE.SET_NAME('ONT','OE_BULK_NOT_SUPP_RSV');
964 OE_BULK_MSG_PUB.Add;
965 END IF;
966 EXCEPTION
967 WHEN OTHERS THEN
968 NULL;
969 END;
970 END IF; --reservation time fence
971 ELSE
972
973 -- Extend_Sch_Error_Rec
974 l_error_index := G_SCH_ERROR_TBL.COUNT + 1;
975 G_SCH_ERROR_TBL(l_error_index).line_index := p_line_index;
976 G_SCH_ERROR_TBL(l_error_index).error_code
977 := p_x_atp_rec.error_code(p_atp_index);
978 p_line_rec.schedule_status_code(p_line_index) := null;
979 p_line_rec.schedule_arrival_date(p_line_index) := null;
980 p_line_rec.schedule_ship_date(p_line_index) := null;
981
982 IF p_line_rec.item_type_code(p_line_index) = 'INCLUDED' THEN
983 p_line_rec.schedule_status_code
984 (p_line_rec.parent_line_index(p_line_index)) := null;
985 p_line_rec.schedule_arrival_date
986 (p_line_rec.parent_line_index(p_line_index)) := null;
987 p_line_rec.schedule_ship_date
988 (p_line_rec.parent_line_index(p_line_index)) := null;
989 END IF;
990
991 END IF;
992
993 EXCEPTION
994 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
995 IF l_debug_level > 0 THEN
996 oe_debug_pub.add( 'UNEXP ERROR , Update_Line_Rec' ) ;
997 END IF;
998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
999 WHEN OTHERS THEN
1000 IF l_debug_level > 0 THEN
1001 oe_debug_pub.add( 'OTHERS ERROR , Update_Line_Rec' ) ;
1002 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1003 END IF;
1004 OE_BULK_MSG_PUB.Add_Exc_Msg
1005 ( G_PKG_NAME
1006 , 'Update_Line_Rec'
1007 );
1008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1009 END Update_Line_Rec;
1010
1011 PROCEDURE Update_MRP_Results
1012 (p_x_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ
1013 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
1014 ,p_x_atp_line_map_rec IN OUT NOCOPY OE_WSH_BULK_GRP.T_NUM
1015 )
1016 IS
1017 J NUMBER;
1018 --
1019 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1020 --
1021 BEGIN
1022
1023 G_SCH_ERROR_TBL.DELETE;
1024
1025 --------------------------------------------------------------
1026 -- UPDATE lines global for successfully scheduled lines
1027 --------------------------------------------------------------
1028 FOR J IN p_x_atp_rec.Identifier.FIRST..p_x_atp_rec.Identifier.LAST
1029 LOOP
1030
1031 Update_Line_Rec(p_line_index => p_x_atp_line_map_rec(J)
1032 ,p_atp_index => J
1033 ,p_line_rec => p_line_rec
1034 ,p_x_atp_Rec => p_x_atp_rec
1035 );
1036
1037 END LOOP;
1038
1039 -- Return if all lines were successfully scheduled
1040
1041 IF (G_SCH_ERROR_TBL.COUNT = 0) THEN
1042 IF l_debug_level > 0 THEN
1043 oe_debug_pub.add( 'RETURNING AS ALL LINES WERE SUCCESSFUL' ) ;
1044 END IF;
1045 RETURN;
1046 END IF;
1047
1048
1049 ---------------------------------------------------------------------
1050 -- For order lines that failed scheduling:
1051 -- Insert Error Messages
1052 ---------------------------------------------------------------------
1053 Insert_Error_Messages(p_line_rec);
1054
1055 EXCEPTION
1056 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1057 IF l_debug_level > 0 THEN
1058 oe_debug_pub.add( 'UNEXP ERROR , UPDATE_MRP_RESULTS' ) ;
1059 END IF;
1060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1061 WHEN OTHERS THEN
1062 IF l_debug_level > 0 THEN
1063 oe_debug_pub.add( 'OTHERS ERROR , UPDATE_MRP_RESULTS' ) ;
1064 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1065 END IF;
1066 OE_BULK_MSG_PUB.Add_Exc_Msg
1067 ( G_PKG_NAME
1068 , 'Update_MRP_Results'
1069 );
1070 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1071 END Update_MRP_Results;
1072
1073
1074 ---------------------------------------------------------------------
1075 -- PROCEDURE Schedule_Orders
1076 --
1077 -- This procedure schedules all lines eligible for auto-scheduling
1078 -- in this order import batch.
1079 -- Scheduling updates are done directly on the line record.
1080 ---------------------------------------------------------------------
1081
1082 PROCEDURE Schedule_Orders
1083 (p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
1084 ,p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
1085 ,x_return_status OUT NOCOPY VARCHAR2)
1086 IS
1087 l_msg_text VARCHAR2(2000);
1088 l_msg_count NUMBER;
1089 l_session_id NUMBER := 0;
1090 l_mrp_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
1091 l_x_mrp_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
1092 l_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
1093 l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
1094 l_atp_details MRP_ATP_PUB.ATP_Details_Typ;
1095 l_mrp_msg_data VARCHAR2(200);
1096 l_start_time NUMBER;
1097 l_end_time NUMBER;
1098 l_atp_line_map_rec OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
1099 --
1100 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1101 --
1102 BEGIN
1103
1104 ------------------------------------------------------------------
1105 -- Before calling this procedure, it is assumed that all the lines
1106 -- in the batch went through entity validation. During entity
1107 -- validation, lines that were eligible for scheduling AND passed
1108 -- scheduling validations would have been marked with
1109 -- schedule_status_code of 'TO_BE_SCHEDULED'.
1110 -- Even holds evaluation is done at the time of entity validation
1111 -- (OEBLLINB.pls) and if line is eligible for a generic hold and
1112 -- profile OM:Schedule Lines on Hold is set to 'No', then the
1113 -- above schedule status would not be set.
1114 -------------------------------------------------------------------
1115 --- BYPASS ATP call
1116 IF NVL(fnd_profile.value('ONT_BYPASS_ATP'),'N') = 'Y' THEN
1117 -- this is an inactive demand line.
1118
1119 IF l_debug_level > 0 THEN
1120 oe_debug_pub.add( 'THIS IS INACTIVE DEMAND LINE' , 1 ) ;
1121 END IF;
1122 Inactive_Demand_Scheduling(
1123 p_line_rec => p_line_rec
1124 ,x_return_status => x_return_status);
1125
1126 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1128 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1129 RAISE FND_API.G_EXC_ERROR;
1130 END IF;
1131
1132 ELSE -- Existing code
1133 -------------------------------------------------------------------
1134 -- (1) Load Request Rec to call the MRP API
1135 -------------------------------------------------------------------
1136 Load_MRP_Request(p_line_rec
1137 ,p_header_rec
1138 ,l_mrp_atp_rec
1139 ,l_atp_line_map_rec
1140 );
1141
1142 IF l_mrp_atp_rec.identifier.count = 0
1143 OR l_mrp_atp_rec.inventory_item_id(1) IS NULL THEN
1144 IF l_debug_level > 0 THEN
1145 oe_debug_pub.add('No lines to schedule');
1146 END IF;
1147 RETURN;
1148 END IF;
1149
1150
1151 -------------------------------------------------------------------
1152 -- (2) Call the MRP API
1153 -------------------------------------------------------------------
1154
1155 SELECT mrp_atp_schedule_temp_s.nextval
1156 INTO l_session_id
1157 FROM dual;
1158
1159 -- Bug 2721165 =>
1160 -- x_atp_rec in call_atp spec was changed to an out nocopy parameter
1161 -- hence same local variable cannot be assigned to both p_atp_rec
1162 -- and x_atp_rec. Created new variable l_x_mrp_atp_rec to be used
1163 -- for x_atp_rec.
1164
1165 -- Bug 5640601 =>
1166 -- Selecting hsecs from v$times is changed to execute only when debug
1167 -- is enabled, as hsec is used for logging only when debug is enabled.
1168 IF l_debug_level > 0 THEN
1169 SELECT hsecs INTO l_start_time from v$timer;
1170 END IF;
1171 MRP_ATP_PUB.Call_ATP
1172 ( p_session_id => l_session_id
1173 , p_atp_rec => l_mrp_atp_rec
1174 , x_atp_rec => l_x_mrp_atp_rec
1175 , x_atp_supply_demand => l_atp_supply_demand
1176 , x_atp_period => l_atp_period
1177 , x_atp_details => l_atp_details
1178 , x_return_status => x_return_status
1179 , x_msg_data => l_mrp_msg_data
1180 , x_msg_count => l_msg_count);
1181
1182 IF l_debug_level > 0 THEN
1183 oe_debug_pub.add( '3. AFTER CALL_ATP , STATUS:' ||X_RETURN_STATUS , 1 ) ;
1184 -- Bug 5640601 =>
1185 -- Selecting hsecs from v$times is changed to execute only when debug
1186 -- is enabled, as hsec is used for logging only when debug is enabled.
1187 SELECT hsecs INTO l_end_time from v$timer;
1188 END IF;
1189
1190 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in MRP Call is (sec) '||((l_end_time-l_start_time)/100));
1191
1192 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1194 END IF;
1195
1196
1197 -------------------------------------------------------------------
1198 -- (3) Update Order Lines with Results from the MRP call
1199 -------------------------------------------------------------------
1200 Update_MRP_Results(l_x_mrp_atp_rec
1201 ,p_line_rec
1202 ,l_atp_line_map_rec
1203 );
1204
1205 END IF;
1206 IF l_debug_level > 0 THEN
1207 oe_debug_pub.add( 'Exiting SCHEDULE_ORDERS' ) ;
1208 END IF;
1209
1210 EXCEPTION
1211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1212 IF l_debug_level > 0 THEN
1213 oe_debug_pub.add( 'UNEXP ERROR , SCHEDULE_ORDERS' ) ;
1214 END IF;
1215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1216 WHEN OTHERS THEN
1217 IF l_debug_level > 0 THEN
1218 oe_debug_pub.add( 'OTHERS ERROR , SCHEDULE_ORDERS' ) ;
1219 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1220 END IF;
1221 OE_BULK_MSG_PUB.Add_Exc_Msg
1222 ( G_PKG_NAME
1223 , 'Schedule_Orders'
1224 );
1225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1226 END Schedule_Orders;
1227 -- Pack J
1228 /*-----------------------------------------------------------------------------
1229 Function Name : Get_Date_Type
1230 Description : This function returns the date type of the order.
1231 The date type could be SHIP or ARRIVAl or null. Null
1232 value will be treated as SHIP in the scheduling code.
1233 -----------------------------------------------------------------------------*/
1234
1235 FUNCTION Get_Date_Type
1236 ( p_header_id IN NUMBER)
1237 RETURN VARCHAR2
1238 IS
1239 l_order_date_type_code VARCHAR2(30) := null;
1240 --
1241 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1242 --
1243 BEGIN
1244 IF p_header_id <> nvl(G_HEADER_ID,0) THEN
1245 BEGIN
1246 SELECT order_date_type_code
1247 INTO l_order_date_type_code
1248 FROM oe_order_headers
1249 WHERE header_id = p_header_id;
1250
1251 G_HEADER_ID := p_header_id;
1252 G_DATE_TYPE := l_order_date_type_code;
1253 EXCEPTION
1254 WHEN OTHERS THEN
1255 RETURN null;
1256 END;
1257 ELSE
1258 l_order_date_type_code := G_DATE_TYPE;
1259 END IF;
1260 RETURN l_order_date_type_code;
1261 EXCEPTION
1262 WHEN NO_DATA_FOUND THEN
1263 RETURN NULL;
1264 END Get_Date_Type;
1265
1266
1267 END OE_BULK_SCHEDULE_UTIL;
[Home] [Help]