1 PACKAGE BODY OE_BULK_SCHEDULE_UTIL AS
2 /* $Header: OEBUSCHB.pls 120.2.12010000.4 2008/11/18 13:37:58 smusanna 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 THEN
557 I := I+1;
558 GOTO START_OF_LOOP;
559 END IF;
560
561 -- Find index position of this line's header in p_header_rec
562 /* No longer needed as the header_index is populated on p_line_rec
563 WHILE K <= l_header_count LOOP
564 IF p_header_rec.header_id(K) = p_line_rec.header_id(I) THEN
565 EXIT;
566 END IF;
567 K := K+1;
568 END LOOP;
569 */
570 k := p_line_rec.header_index(I);
571
572 -- Add check here for HOLDS if the profile option schedule lines on hold is
573 -- False.
574
575 IF OE_BULK_ORDER_PVT.G_SCHEDULE_LINE_ON_HOLD = 'N' AND
576 p_line_rec.item_type_code(I) <> 'STANDARD'
577 THEN
578 IF l_debug_level > 0 THEN
579 oe_debug_pub.add( 'Calling check for holds :'||J ) ;
580 END IF;
581 l_result := OE_Bulk_Holds_PVT.Check_For_Holds(
582 p_header_id => p_line_rec.header_id(I),
583 p_line_id => p_line_rec.line_id(I),
584 p_line_index => I,
585 p_header_index => K,
586 p_top_model_line_index => p_line_rec.top_model_line_index(I),
587 p_ship_model_complete_flag => p_line_rec.ship_model_complete_flag(I),
588 p_ato_line_index => p_line_rec.ato_line_index(I),
589 p_ii_parent_line_index => NULL
590 );
591 IF l_debug_level > 0 THEN
592 oe_debug_pub.add('After Calling check for holds ');
593 END IF;
594
595 -- If found on hold then do not schedule the line.
596 IF l_result THEN
597
598 -- Check if the current line has included items and mark them to be
599 -- not scheduled.
600
601 IF p_line_rec.item_type_code(I) In ('MODEL', 'CLASS') AND
602 p_line_rec.ii_start_index(I) IS NOT NULL THEN
603
604 l_ii_last_index := p_line_rec.ii_start_index(I)
605 + p_line_rec.ii_count(I) - 1;
606 l_ii_index := p_line_rec.ii_start_index(I);
607
608 -- loop over included items for this line
609 WHILE l_ii_index <= l_ii_last_index LOOP
610
611 IF p_line_rec.schedule_status_code(l_ii_index) IS NOT NULL
612 THEN
613 p_line_rec.schedule_status_code(l_ii_index) := NULL;
614 END IF;
615 l_ii_index := l_ii_index + 1;
616 END LOOP;
617 END IF;
618 p_line_rec.schedule_status_code(I) := NULL;
619 I := I+1;
620 GOTO START_OF_LOOP;
621 END IF;
622
623 END IF;
624
625 l_curr_ato_index := p_line_rec.ato_line_index(I);
626
627 IF l_debug_level > 0 THEN
628 oe_debug_pub.add( 'The current ATO index is:'||l_curr_ato_index ) ;
629 END IF;
630
631 -- line should not be scheduled if
632 -- any included item is on hold
633 IF p_line_rec.item_type_code(I) IN ( 'KIT', 'CLASS', 'MODEL')
634 AND p_line_rec.schedule_status_code(I) = 'II_ON_HOLD'
635 THEN
636 -- Clear out the schedule status on kit line but
637 -- go on to add mrp records for included items that
638 -- are not on hold in this kit
639 p_line_rec.schedule_status_code(I) := NULL;
640 ELSE
641 IF l_debug_level > 0 THEN
642 oe_debug_pub.add('Calling add_mrp_rec ') ;
643 END IF;
644 Add_MRP_Rec(p_line_index => I
645 ,p_curr_ato_index => l_curr_ato_index
646 ,p_header_index => K
647 ,p_atp_index => J
648 ,p_line_rec => p_line_rec
649 ,p_header_rec => p_header_rec
650 ,p_x_atp_Rec => p_x_atp_rec
651 );
652 IF l_debug_level > 0 THEN
653 oe_debug_pub.add('After add_mrp_rec ') ;
654 END IF;
655 p_x_atp_line_map_rec(J) := I;
656 J := J+1;
657 END IF;
658
659 IF p_line_rec.item_type_code(I) In ('KIT', 'MODEL', 'CLASS') AND
660 p_line_rec.ii_start_index(I) IS NOT NULL THEN
661
662 l_ii_last_index := p_line_rec.ii_start_index(I)
663 + p_line_rec.ii_count(I) - 1;
664 l_ii_index := p_line_rec.ii_start_index(I);
665 -- loop over included items for this kit
666 WHILE l_ii_index <= l_ii_last_index LOOP
667 -- only add included items that need to be scheduled.
668 -- For non-SMCs, schedule status for iis on hold will be null
669 -- , all other lines should be scheduled
670
671 IF p_line_rec.schedule_status_code(l_ii_index) IS NOT NULL THEN
672
673 l_result := FALSE;
674
675 -- If Schedule Lines on holds is False then
676 IF OE_BULK_ORDER_PVT.G_SCHEDULE_LINE_ON_HOLD = 'N' THEN
677
678 -- Call check for holds
679 l_result := OE_Bulk_Holds_PVT.Check_For_Holds(
680 p_header_id => p_line_rec.header_id(l_ii_index),
681 p_line_id => p_line_rec.line_id(l_ii_index),
682 p_line_index => l_ii_index,
683 p_header_index => K,
684 p_top_model_line_index =>
685 p_line_rec.top_model_line_index(l_ii_index),
686 p_ship_model_complete_flag =>
687 p_line_rec.ship_model_complete_flag(l_ii_index),
688 p_ato_line_index => NULL,
689 p_ii_parent_line_index => I
690 );
691 END IF;
692
693 -- If found on hold then do not schedule the line.
694 IF l_result THEN
695 p_line_rec.schedule_status_code(I) := NULL;
696 ELSE
697 IF l_debug_level > 0 THEN
698 oe_debug_pub.add('calling add_mrp_rec for II ') ;
699 END IF;
700
701 Add_MRP_Rec(
702 p_line_index => l_ii_index
703 ,p_curr_ato_index => l_curr_ato_index
704 ,p_header_index => K
705 ,p_atp_index => J
706 ,p_line_rec => p_line_rec
707 ,p_header_rec => p_header_rec
708 ,p_x_atp_Rec => p_x_atp_rec
709 );
710 IF l_debug_level > 0 THEN
711 oe_debug_pub.add('After calling add_mrp_rec for II ') ;
712 END IF;
713 p_x_atp_line_map_rec(J) := l_ii_index;
714 J := J+1;
715 END IF;
716 END IF; -- IF p_line_rec.schedule_status_code(
717 l_ii_index := l_ii_index + 1;
718 END LOOP;
719
720 END IF; -- End IF for KITS
721
722 I := I+1;
723
724 END LOOP; -- End of loop over line record
725
726 If l_debug_level > 0 THEN
727 oe_debug_pub.add('Exiting OE_BULK_SCHEDULE_UTIL.Load_MRP_Request');
728 END IF;
729
730
731 EXCEPTION
732 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
733 oe_debug_pub.add('Unexp Error, Load_MRP_Request');
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735 WHEN OTHERS THEN
736 oe_debug_pub.add('Others Error, Load_MRP_Request');
737 oe_debug_pub.add(substr(sqlerrm,1,240));
738 OE_BULK_MSG_PUB.Add_Exc_Msg
739 ( G_PKG_NAME
740 , 'Load_MRP_Request'
741 );
742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
743 END Load_MRP_Request;
744
745 PROCEDURE Update_Line_Rec
746 (p_line_index IN NUMBER
747 ,p_atp_index IN NUMBER
748 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
749 ,p_x_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ
750 )
751 IS
752 l_time_to_ship NUMBER;
753 l_c_index NUMBER;
754 l_on_generic_hold BOOLEAN := FALSE;
755 l_on_booking_hold BOOLEAN := FALSE;
756 l_on_scheduling_hold BOOLEAN := FALSE;
757 l_error_index NUMBER;
758 l_promise_date_flag VARCHAR2(2);
759 l_order_date_type_code VARCHAR2(30);
760 l_hold_ii_flag VARCHAR2(1);
761 --
762 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
763 --
764 l_line_rec_for_hold OE_Order_PUB.Line_Rec_Type; --ER#7479609
765 l_header_rec_for_hold OE_Order_PUB.Header_Rec_Type; --ER#7479609
766 BEGIN
767
768 if l_debug_level > 0 then
769
770 oe_debug_pub.add('Line Index :'||p_line_index);
771 oe_debug_pub.add('ATP Index :'||p_atp_index);
772 oe_debug_pub.add('Ship Set :'||p_x_atp_rec.ship_set_name(p_atp_index));
773 oe_debug_pub.add('Error Code :'||p_x_atp_rec.error_code(p_atp_index));
774
775 end if;
776
777 --bug5880565
778 oe_bulk_msg_pub.set_msg_context
779 ( p_entity_code => 'LINE'
780 ,p_entity_id => p_line_rec.line_id(p_line_index)
781 ,p_header_id => p_line_rec.header_id(p_line_index)
782 ,p_line_id => p_line_rec.line_id(p_line_index)
783 ,p_orig_sys_document_ref => p_line_rec.orig_sys_document_ref(p_line_index)
784 ,p_orig_sys_document_line_ref => p_line_rec.orig_sys_line_ref(p_line_index)
785 ,p_source_document_id => NULL
786 ,p_source_document_line_id => NULL
787 ,p_order_source_id => p_line_rec.order_source_id(p_line_index)
788 ,p_source_document_type_id => NULL );
789
790
791
792 -- NOTE: Changes to OPM fields due to Ship From update
793 -- or Changes to Tax Date due to Schedule Ship Date update
794 -- are not done here as BULK does not support OPM or Tax Calculation
795
796 IF p_x_atp_rec.error_code(p_atp_index) IN (0,-99,150) THEN
797
798 -- Update warehouse if ATP check returns a different warehouse
799 IF nvl(p_line_rec.ship_from_org_id(p_line_index),-1)
800 <> nvl(p_x_atp_rec.source_organization_id(p_atp_index),-1)
801 THEN
802 p_line_rec.ship_from_org_id(p_line_index) := p_x_atp_rec.source_organization_id(p_atp_index);
803 /*ER#7479609 start
804 OE_Bulk_Holds_PVT.Evaluate_Holds(
805 p_header_id => p_line_rec.header_id(p_line_index),
806 p_line_id => p_line_rec.line_id(p_line_index),
807 p_line_number => p_line_rec.line_number(p_line_index),
808 p_sold_to_org_id => p_line_rec.sold_to_org_id(p_line_index),
809 p_inventory_item_id => p_line_rec.inventory_item_id(p_line_index),
810 p_ship_from_org_id => p_line_rec.ship_from_org_id(p_line_index),
811 p_invoice_to_org_id => p_line_rec.invoice_to_org_id(p_line_index),
812 p_ship_to_org_id => p_line_rec.ship_to_org_id(p_line_index),
813 p_top_model_line_id => p_line_rec.top_model_line_id(p_line_index),
814 p_ship_set_name => NULL,
815 p_arrival_set_name => NULL,
816 p_check_only_warehouse_holds => TRUE,
817 p_on_generic_hold => l_on_generic_hold,
818 p_on_booking_hold => l_on_booking_hold,
819 p_on_scheduling_hold => l_on_scheduling_hold
820 );
821 ER#7479609 end*/
822
823 --ER#7479609 start
824 BEGIN
825 SELECT order_type_id
826 INTO l_header_rec_for_hold.order_type_id
827 FROM OE_ORDER_HEADERS_ALL
828 WHERE header_id=p_line_rec.header_id(p_line_index);
829 EXCEPTION
830 WHEN OTHERS THEN
831 l_header_rec_for_hold.order_type_id := NULL;
832 END;
833
834 l_line_rec_for_hold.header_id := p_line_rec.header_id(p_line_index);
835 l_line_rec_for_hold.line_id := p_line_rec.line_id(p_line_index);
836 l_line_rec_for_hold.line_number := p_line_rec.line_number(p_line_index);
837 l_line_rec_for_hold.sold_to_org_id := p_line_rec.sold_to_org_id(p_line_index);
838 l_line_rec_for_hold.inventory_item_id := p_line_rec.inventory_item_id(p_line_index);
839 l_line_rec_for_hold.ship_from_org_id := p_line_rec.ship_from_org_id(p_line_index);
840 l_line_rec_for_hold.invoice_to_org_id := p_line_rec.invoice_to_org_id(p_line_index);
841 l_line_rec_for_hold.ship_to_org_id := p_line_rec.ship_to_org_id(p_line_index);
842 l_line_rec_for_hold.top_model_line_id := p_line_rec.top_model_line_id(p_line_index);
843 l_line_rec_for_hold.price_list_id := p_line_rec.price_list_id(p_line_index);
844 l_line_rec_for_hold.creation_date := to_char(sysdate,'DD-MON-RRRR');
845 l_line_rec_for_hold.shipping_method_code := p_line_rec.shipping_method_code(p_line_index);
846 l_line_rec_for_hold.deliver_to_org_id := p_line_rec.deliver_to_org_id(p_line_index);
847 l_line_rec_for_hold.source_type_code := p_line_rec.source_type_code(p_line_index);
848 l_line_rec_for_hold.line_type_id := p_line_rec.line_type_id(p_line_index);
849 l_line_rec_for_hold.payment_term_id := p_line_rec.payment_term_id(p_line_index);
850 l_line_rec_for_hold.created_by := NVL(FND_GLOBAL.USER_ID, -1);
851
852
853 OE_Bulk_Holds_PVT.Evaluate_Holds(
854 p_header_rec => l_header_rec_for_hold,
855 p_line_rec => l_line_rec_for_hold,
856 p_on_generic_hold => l_on_generic_hold,
857 p_on_booking_hold => l_on_booking_hold,
858 p_on_scheduling_hold => l_on_scheduling_hold
859 );
860 --ER#7479609 end
861 -- Also cache EDI attributes for the new ship from
862 l_c_index := OE_Bulk_Cache.Load_Ship_From
863 (p_key => p_line_rec.ship_from_org_id(p_line_index)
864 );
865 END IF;
866
867 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
868 IF p_x_atp_rec.group_ship_date(p_atp_index) IS NOT NULL
869 THEN
870 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.group_ship_date(p_atp_index);
871 ELSE
872 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.ship_date(p_atp_index);
873 END IF;
874
875 IF p_x_atp_rec.group_arrival_date(p_atp_index) IS NOT NULL THEN
876 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.group_arrival_date(p_atp_index);
877 ELSE
878 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.arrival_date(p_atp_index);
879 END IF;
880 ELSE
881 -- Group_ship_date/group_arrival_date - use these also!
882 if p_x_atp_rec.ship_set_name(p_atp_index) is not null then
883 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.group_ship_date(p_atp_index);
884 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.group_ship_date(p_atp_index)
885 + nvl(p_x_atp_rec.delivery_lead_time(p_atp_index),0);
886 elsif p_x_atp_rec.arrival_set_name(p_atp_index) is not null then
887 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.group_arrival_date(p_atp_index)
888 - nvl(p_x_atp_rec.delivery_lead_time(p_atp_index),0);
889 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.group_arrival_date(p_atp_index);
890 else
891 p_line_rec.schedule_ship_date(p_line_index) := p_x_atp_rec.ship_date(p_atp_index);
892 p_line_rec.schedule_arrival_date(p_line_index) := p_x_atp_rec.ship_date(p_atp_index)
893 + nvl(p_x_atp_rec.delivery_lead_time(p_atp_index),0);
894 end if;
895 END IF;
896 -- Pack J
897 -- Latest Acceptable date violation check (Set to Ignore)
898 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
899 IF OE_SYS_PARAMETERS.value('LATEST_ACCEPTABLE_DATE_FLAG')='I' THEN
900 l_order_date_type_code := NVL(Get_Date_Type(p_line_rec.header_id(p_line_index)),'SHIP');
901 IF ((l_order_date_type_code = 'SHIP'
902 AND p_line_rec.schedule_ship_date(p_line_index)
903 > p_line_rec.latest_acceptable_date(p_line_index))
904 OR (l_order_date_type_code = 'ARRIVAL'
905 AND p_line_rec.schedule_arrival_date(p_line_index)
906 > p_line_rec.latest_acceptable_date(p_line_index))) THEN
907 FND_MESSAGE.SET_NAME('ONT','ONT_SCH_LAD_VIOLATE');
908 OE_BULK_MSG_PUB.Add;
909 END IF;
910 END IF;
911
912 -- Get the Promise date flag
913 l_promise_date_flag := Oe_sys_Parameters.Value('PROMISE_DATE_FLAG');
914 -- Set the Promise date with schedule ship date
915 IF l_promise_date_flag IN('FS','S') THEN
916 IF l_order_date_type_code = 'SHIP'
917 AND p_line_rec.schedule_ship_date(p_line_index) IS NOT NULL THEN
918 p_line_rec.promise_date(p_line_index) := p_line_rec.schedule_ship_date(p_line_index);
919 ELSIF l_order_date_type_code = 'ARRIVAL'
920 AND p_line_rec.schedule_arrival_date(p_line_index) IS NOT NULL THEN
921 p_line_rec.promise_date(p_line_index) := p_line_rec.schedule_arrival_date(p_line_index);
922 END IF;
923 ELSIF l_promise_date_flag IN('FR','R') THEN -- Set the Promise date with Request Date
924 p_line_rec.promise_date(p_line_index) := p_line_rec.request_date(p_line_index);
925 END IF;
926 END IF;
927
928 p_line_rec.delivery_lead_time(p_line_index) := p_x_atp_rec.delivery_lead_time(p_atp_index);
929 p_line_rec.mfg_lead_time(p_line_index) := p_x_atp_rec.atp_lead_time(p_atp_index);
930 p_line_rec.shipping_method_code(p_line_index) := nvl(p_x_atp_rec.ship_method(p_atp_index),
931 p_line_rec.shipping_method_code(p_line_index));
932 p_line_rec.schedule_status_code(p_line_index) := 'SCHEDULED';
933
934 IF Oe_Sys_Parameters.Value('FIRM_DEMAND_EVENTS') = 'SCHEDULE'
935 AND OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
936 p_line_rec.firm_demand_flag(p_line_index) := 'Y';
937 END IF;
938
939 -- Bug 2737274
940 -- Check if attribute_05 exists as the attribute may not be available
941 -- on out ATP rec from the MRP call.
942 -- Without the exists check, error 'subscript beyond count' can result
943 IF p_x_atp_rec.attribute_05.EXISTS(p_atp_index) THEN
944 p_line_rec.visible_demand_flag(p_line_index) := nvl(p_x_atp_rec.attribute_05(p_atp_index),'Y');
945 ELSE
946 p_line_rec.visible_demand_flag(p_line_index) := 'Y';
947 END IF;
948
949 --p_line_rec.last_update_date(p_line_index) := sysdate;
950 --p_line_rec.last_updated_by(p_line_index) := FND_GLOBAL.USER_ID;
951 --p_line_rec.last_update_login(p_line_index) := FND_GLOBAL.LOGIN_ID;
952 p_line_rec.lock_control(p_line_index) := (p_line_rec.lock_control(p_line_index) + 1);
953
954 IF OE_BULK_ORDER_PVT.G_RESERVATION_TIME_FENCE IS NOT NULL THEN
955 -- If ship date is within reservation time fence, populate a message
956 l_time_to_ship := to_number(trunc(p_line_rec.schedule_ship_date(p_line_index)) - trunc(SYSDATE));
957
958 BEGIN
959 IF l_time_to_ship < 0 THEN
960 NULL;
961 ELSIF l_time_to_ship <= to_number(OE_BULK_ORDER_PVT.G_RESERVATION_TIME_FENCE) THEN
962 FND_MESSAGE.SET_NAME('ONT','OE_BULK_NOT_SUPP_RSV');
963 OE_BULK_MSG_PUB.Add;
964 END IF;
965 EXCEPTION
966 WHEN OTHERS THEN
967 NULL;
968 END;
969 END IF; --reservation time fence
970 ELSE
971
972 -- Extend_Sch_Error_Rec
973 l_error_index := G_SCH_ERROR_TBL.COUNT + 1;
974 G_SCH_ERROR_TBL(l_error_index).line_index := p_line_index;
975 G_SCH_ERROR_TBL(l_error_index).error_code
976 := p_x_atp_rec.error_code(p_atp_index);
977 p_line_rec.schedule_status_code(p_line_index) := null;
978 p_line_rec.schedule_arrival_date(p_line_index) := null;
979 p_line_rec.schedule_ship_date(p_line_index) := null;
980
981 IF p_line_rec.item_type_code(p_line_index) = 'INCLUDED' THEN
982 p_line_rec.schedule_status_code
983 (p_line_rec.parent_line_index(p_line_index)) := null;
984 p_line_rec.schedule_arrival_date
985 (p_line_rec.parent_line_index(p_line_index)) := null;
986 p_line_rec.schedule_ship_date
987 (p_line_rec.parent_line_index(p_line_index)) := null;
988 END IF;
989
990 END IF;
991
992 EXCEPTION
993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
994 IF l_debug_level > 0 THEN
995 oe_debug_pub.add( 'UNEXP ERROR , Update_Line_Rec' ) ;
996 END IF;
997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998 WHEN OTHERS THEN
999 IF l_debug_level > 0 THEN
1000 oe_debug_pub.add( 'OTHERS ERROR , Update_Line_Rec' ) ;
1001 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1002 END IF;
1003 OE_BULK_MSG_PUB.Add_Exc_Msg
1004 ( G_PKG_NAME
1005 , 'Update_Line_Rec'
1006 );
1007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1008 END Update_Line_Rec;
1009
1010 PROCEDURE Update_MRP_Results
1011 (p_x_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ
1012 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
1013 ,p_x_atp_line_map_rec IN OUT NOCOPY OE_WSH_BULK_GRP.T_NUM
1014 )
1015 IS
1016 J NUMBER;
1017 --
1018 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1019 --
1020 BEGIN
1021
1022 G_SCH_ERROR_TBL.DELETE;
1023
1024 --------------------------------------------------------------
1025 -- UPDATE lines global for successfully scheduled lines
1026 --------------------------------------------------------------
1027 FOR J IN p_x_atp_rec.Identifier.FIRST..p_x_atp_rec.Identifier.LAST
1028 LOOP
1029
1030 Update_Line_Rec(p_line_index => p_x_atp_line_map_rec(J)
1031 ,p_atp_index => J
1032 ,p_line_rec => p_line_rec
1033 ,p_x_atp_Rec => p_x_atp_rec
1034 );
1035
1036 END LOOP;
1037
1038 -- Return if all lines were successfully scheduled
1039
1040 IF (G_SCH_ERROR_TBL.COUNT = 0) THEN
1041 IF l_debug_level > 0 THEN
1042 oe_debug_pub.add( 'RETURNING AS ALL LINES WERE SUCCESSFUL' ) ;
1043 END IF;
1044 RETURN;
1045 END IF;
1046
1047
1048 ---------------------------------------------------------------------
1049 -- For order lines that failed scheduling:
1050 -- Insert Error Messages
1051 ---------------------------------------------------------------------
1052 Insert_Error_Messages(p_line_rec);
1053
1054 EXCEPTION
1055 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1056 IF l_debug_level > 0 THEN
1057 oe_debug_pub.add( 'UNEXP ERROR , UPDATE_MRP_RESULTS' ) ;
1058 END IF;
1059 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1060 WHEN OTHERS THEN
1061 IF l_debug_level > 0 THEN
1062 oe_debug_pub.add( 'OTHERS ERROR , UPDATE_MRP_RESULTS' ) ;
1063 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1064 END IF;
1065 OE_BULK_MSG_PUB.Add_Exc_Msg
1066 ( G_PKG_NAME
1067 , 'Update_MRP_Results'
1068 );
1069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1070 END Update_MRP_Results;
1071
1072
1073 ---------------------------------------------------------------------
1074 -- PROCEDURE Schedule_Orders
1075 --
1076 -- This procedure schedules all lines eligible for auto-scheduling
1077 -- in this order import batch.
1078 -- Scheduling updates are done directly on the line record.
1079 ---------------------------------------------------------------------
1080
1081 PROCEDURE Schedule_Orders
1082 (p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
1083 ,p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
1084 ,x_return_status OUT NOCOPY VARCHAR2)
1085 IS
1086 l_msg_text VARCHAR2(2000);
1087 l_msg_count NUMBER;
1088 l_session_id NUMBER := 0;
1089 l_mrp_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
1090 l_x_mrp_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
1091 l_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
1092 l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
1093 l_atp_details MRP_ATP_PUB.ATP_Details_Typ;
1094 l_mrp_msg_data VARCHAR2(200);
1095 l_start_time NUMBER;
1096 l_end_time NUMBER;
1097 l_atp_line_map_rec OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
1098 --
1099 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1100 --
1101 BEGIN
1102
1103 ------------------------------------------------------------------
1104 -- Before calling this procedure, it is assumed that all the lines
1105 -- in the batch went through entity validation. During entity
1106 -- validation, lines that were eligible for scheduling AND passed
1107 -- scheduling validations would have been marked with
1108 -- schedule_status_code of 'TO_BE_SCHEDULED'.
1109 -- Even holds evaluation is done at the time of entity validation
1110 -- (OEBLLINB.pls) and if line is eligible for a generic hold and
1111 -- profile OM:Schedule Lines on Hold is set to 'No', then the
1112 -- above schedule status would not be set.
1113 -------------------------------------------------------------------
1114 --- BYPASS ATP call
1115 IF NVL(fnd_profile.value('ONT_BYPASS_ATP'),'N') = 'Y' THEN
1116 -- this is an inactive demand line.
1117
1118 IF l_debug_level > 0 THEN
1119 oe_debug_pub.add( 'THIS IS INACTIVE DEMAND LINE' , 1 ) ;
1120 END IF;
1121 Inactive_Demand_Scheduling(
1122 p_line_rec => p_line_rec
1123 ,x_return_status => x_return_status);
1124
1125 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1127 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1128 RAISE FND_API.G_EXC_ERROR;
1129 END IF;
1130
1131 ELSE -- Existing code
1132 -------------------------------------------------------------------
1133 -- (1) Load Request Rec to call the MRP API
1134 -------------------------------------------------------------------
1135 Load_MRP_Request(p_line_rec
1136 ,p_header_rec
1137 ,l_mrp_atp_rec
1138 ,l_atp_line_map_rec
1139 );
1140
1141 IF l_mrp_atp_rec.identifier.count = 0
1142 OR l_mrp_atp_rec.inventory_item_id(1) IS NULL THEN
1143 IF l_debug_level > 0 THEN
1144 oe_debug_pub.add('No lines to schedule');
1145 END IF;
1146 RETURN;
1147 END IF;
1148
1149
1150 -------------------------------------------------------------------
1151 -- (2) Call the MRP API
1152 -------------------------------------------------------------------
1153
1154 SELECT mrp_atp_schedule_temp_s.nextval
1155 INTO l_session_id
1156 FROM dual;
1157
1158 -- Bug 2721165 =>
1159 -- x_atp_rec in call_atp spec was changed to an out nocopy parameter
1160 -- hence same local variable cannot be assigned to both p_atp_rec
1161 -- and x_atp_rec. Created new variable l_x_mrp_atp_rec to be used
1162 -- for x_atp_rec.
1163
1164 -- Bug 5640601 =>
1165 -- Selecting hsecs from v$times is changed to execute only when debug
1166 -- is enabled, as hsec is used for logging only when debug is enabled.
1167 IF l_debug_level > 0 THEN
1168 SELECT hsecs INTO l_start_time from v$timer;
1169 END IF;
1170 MRP_ATP_PUB.Call_ATP
1171 ( p_session_id => l_session_id
1172 , p_atp_rec => l_mrp_atp_rec
1173 , x_atp_rec => l_x_mrp_atp_rec
1174 , x_atp_supply_demand => l_atp_supply_demand
1175 , x_atp_period => l_atp_period
1176 , x_atp_details => l_atp_details
1177 , x_return_status => x_return_status
1178 , x_msg_data => l_mrp_msg_data
1179 , x_msg_count => l_msg_count);
1180
1181 IF l_debug_level > 0 THEN
1182 oe_debug_pub.add( '3. AFTER CALL_ATP , STATUS:' ||X_RETURN_STATUS , 1 ) ;
1183 -- Bug 5640601 =>
1184 -- Selecting hsecs from v$times is changed to execute only when debug
1185 -- is enabled, as hsec is used for logging only when debug is enabled.
1186 SELECT hsecs INTO l_end_time from v$timer;
1187 END IF;
1188
1189 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in MRP Call is (sec) '||((l_end_time-l_start_time)/100));
1190
1191 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1193 END IF;
1194
1195
1196 -------------------------------------------------------------------
1197 -- (3) Update Order Lines with Results from the MRP call
1198 -------------------------------------------------------------------
1199 Update_MRP_Results(l_x_mrp_atp_rec
1200 ,p_line_rec
1201 ,l_atp_line_map_rec
1202 );
1203
1204 END IF;
1205 IF l_debug_level > 0 THEN
1206 oe_debug_pub.add( 'Exiting SCHEDULE_ORDERS' ) ;
1207 END IF;
1208
1209 EXCEPTION
1210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1211 IF l_debug_level > 0 THEN
1212 oe_debug_pub.add( 'UNEXP ERROR , SCHEDULE_ORDERS' ) ;
1213 END IF;
1214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1215 WHEN OTHERS THEN
1216 IF l_debug_level > 0 THEN
1217 oe_debug_pub.add( 'OTHERS ERROR , SCHEDULE_ORDERS' ) ;
1218 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1219 END IF;
1220 OE_BULK_MSG_PUB.Add_Exc_Msg
1221 ( G_PKG_NAME
1222 , 'Schedule_Orders'
1223 );
1224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1225 END Schedule_Orders;
1226 -- Pack J
1227 /*-----------------------------------------------------------------------------
1228 Function Name : Get_Date_Type
1229 Description : This function returns the date type of the order.
1230 The date type could be SHIP or ARRIVAl or null. Null
1231 value will be treated as SHIP in the scheduling code.
1232 -----------------------------------------------------------------------------*/
1233
1234 FUNCTION Get_Date_Type
1235 ( p_header_id IN NUMBER)
1236 RETURN VARCHAR2
1237 IS
1238 l_order_date_type_code VARCHAR2(30) := null;
1239 --
1240 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1241 --
1242 BEGIN
1243 IF p_header_id <> nvl(G_HEADER_ID,0) THEN
1244 BEGIN
1245 SELECT order_date_type_code
1246 INTO l_order_date_type_code
1247 FROM oe_order_headers
1248 WHERE header_id = p_header_id;
1249
1250 G_HEADER_ID := p_header_id;
1251 G_DATE_TYPE := l_order_date_type_code;
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 RETURN null;
1255 END;
1256 ELSE
1257 l_order_date_type_code := G_DATE_TYPE;
1258 END IF;
1259 RETURN l_order_date_type_code;
1260 EXCEPTION
1261 WHEN NO_DATA_FOUND THEN
1262 RETURN NULL;
1263 END Get_Date_Type;
1264
1265
1266 END OE_BULK_SCHEDULE_UTIL;
[Home] [Help]