1 PACKAGE BODY OE_CONFIG_SCHEDULE_PVT AS
2 /* $Header: OEXVCSCB.pls 120.21.12020000.4 2013/05/09 18:35:10 gabhatia ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30):='Oe_Config_Schedule_Pvt';
6 G_BINARY_LIMIT CONSTANT NUMBER := OE_GLOBALS.G_BINARY_LIMIT; --7827737;
7
8 /*--------------------------------------------------------------
9 Forward Declarations
10 ---------------------------------------------------------------*/
11 PROCEDURE Print_Time(p_msg IN VARCHAR2);
12
13 PROCEDURE Call_Mrp
14 ( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
15 ,p_old_line_tbl IN OE_Order_Pub.Line_Tbl_Type
16 ,p_sch_action IN VARCHAR2
17 ,p_partial IN BOOLEAN := FALSE
18 ,p_partial_set IN BOOLEAN := FALSE
19 ,p_part_of_set IN VARCHAR2 DEFAULT 'N' -- 4405004
20 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
21
22 PROCEDURE Handle_Unreserve
23 ( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
24 ,p_old_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
25 ,p_post_process IN VARCHAR2 DEFAULT FND_API.G_FALSE
26 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
27
28 PROCEDURE Reserve_Group
29 ( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
30 ,p_sch_action IN VARCHAR2);
31
32 PROCEDURE Unreserve_group
33 ( p_line_tbl IN OE_Order_Pub.Line_Tbl_Type
34 ,p_sch_action IN VARCHAR2 := 'X');
35
36 Procedure Validate_Group_Request
37 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
38 ,p_request_type IN VARCHAR2
39 ,p_sch_action IN VARCHAR2
40 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
41
42 PROCEDURE Is_Group_Scheduled
43 ( p_line_tbl IN OE_Order_Pub.Line_Tbl_Type
44 ,p_caller IN VARCHAR2 := 'X'
45 ,x_result OUT NOCOPY /* file.sql.39 change */ NUMBER);
46
47 PROCEDURE Get_Reservations
48 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type
49 ,x_reservation_qty OUT NOCOPY /* file.sql.39 change */ NUMBER
50 ,x_reservation_qty2 OUT NOCOPY /* file.sql.39 change */ NUMBER -- INCONV
51 );
52
53 PROCEDURE Log_Attribute_Changes
54 (p_line_rec IN OE_Order_PUB.Line_Rec_Type
55 ,p_old_line_rec IN OE_Order_PUB.Line_Rec_Type
56 ,x_unreserve_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
57
58 --temp, just for testing
59 PROCEDURE Call_ATP
60 ( p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ
61 ,x_atp_rec OUT NOCOPY /* file.sql.39 change */ MRP_ATP_PUB.ATP_Rec_Typ);
62
63
64 PROCEDURE Validate_And_Assign_Sch_Params
65 ( p_request_rec IN OE_Order_Pub.Request_Rec_Type
66 ,p_x_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
67 ,p_x_old_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type);
68
69 PROCEDURE Get_Included_Items_To_Sch
70 ( p_x_line_tbl IN OUT NOCOPY OE_Order_PUB.line_tbl_type
71 ,p_x_old_line_tbl IN OUT NOCOPY OE_Order_PUB.line_tbl_type
72 ,p_request_tbl IN OUT NOCOPY OE_Order_PUB.request_tbl_type
73 ,p_in_index IN OUT NOCOPY /* file.sql.39 change */ NUMBER
74 ,p_req_index IN NUMBER);
75
76 -- this is temp, to be moved to oexuschb.pls
77 PROCEDURE Call_Security_And_Defaulting
78 ( p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
79 ,p_old_line_rec IN OE_ORDER_PUB.line_rec_type
80 ,x_direct_update OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
81
82 PROCEDURE Handle_Direct_Update
83 ( p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
84 ,p_old_line_rec IN OE_ORDER_PUB.line_rec_type
85 ,p_caller IN VARCHAR2);
86
87
88 FUNCTION Check_for_Request( p_set_id IN NUMBER,
89 p_ato_line_id IN NUMBER)
90 RETURN BOOLEAN
91 IS
92 K NUMBER;
93 BEGIN
94
95 oe_debug_pub.add('Entering Procedure Check_for_Request in Package OE_Delayed_
96 Requests_Pvt');
97
98
99 K := OE_Delayed_Requests_PVT.G_Delayed_Requests.FIRST;
100
101 WHILE K is not null
102 LOOP
103
104 IF (OE_Delayed_Requests_PVT.G_Delayed_Requests(K).request_type =
105 OE_GLOBALS.G_GROUP_SCHEDULE
106 OR (OE_Delayed_Requests_PVT.G_Delayed_Requests(K).request_type =
107 OE_GLOBALS.G_SCHEDULE_LINE
108 AND OE_Delayed_Requests_PVT.G_Delayed_Requests(K).entity_id = p_ato_line_id))
109 AND OE_Delayed_Requests_PVT.G_Delayed_Requests(K).param1 = p_set_id
110 THEN
111 RETURN TRUE;
112
113 END IF;
114
115 K := OE_Delayed_Requests_PVT.G_Delayed_Requests.NEXT(K);
116
117 END LOOP;
118
119 RETURN FALSE;
120 EXCEPTION
121 WHEN OTHERS THEN
122 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
123 THEN
124 OE_MSG_PUB.Add_Exc_Msg
125 (G_PKG_NAME
126 ,'CheckForRequest');
127 END IF;
128 RETURN FALSE;
129
130 End Check_For_Request;
131 /*---------------------------------------------------------
132 PROCEDURE Log_Config_Sch_Request
133
134 a) ATO model/options
135 SMC can have ATO : in this case, the req will be for SMC
136 NONSMC can have ATO : in this case, the req will be for ATO
137 top level ATO : in this case, the req will be for ATO
138
139 b) Scheduling parameters=>
140 1. request_date : reschedule
141 2. schedule_ship_date : reschedule
142 3. schedule_arrival_date: reschedule
143 4. ordered_quantity : unreserve of decrease, reschedule
144 5. order_quantity_uom : if std, unreserve, reschedule, reserve*
145 6. reserved_quantity : unreserve, reserve
146 7. inventory_item_id : unreserve, undemand, demand new, reserve*
147 8. ship_from_org_id : unreserve, reschedule, reserve
148 9. ship_to_org_id : reschedule
149 10. shipping_method_code: reschedule
150 11. demand_class_code : reschedule
151 12. planning_priority : reschedule*
152 13. delivery_lead_time : reschedule*
153
154 * means can not be changed for a configuration,
155 not to worry for now.
156
157 so ordered_quantity, reserved_quantity and ship_from change
158 results in more than scheduling/rescheduling.
159
160 c) We do not expect any exception raised in this API,
161 only unexpected errors can come.
162
163 d) The p_sch_action is obtained from need_scheduling,
164 it can have,
165
166 SCHEDULE
167 UNSCHEDULE
168 RESCHEDULE : any of the sch attibs changed. does not tell
169 how many, which
170 RESERVE : only indicates that there is a change
171 in reserved qty, does not tell increase
172 or decrease.
173
174 apart from the p_sch_action, we use the log_attrib_changes
175 procedure to get any scheduling co action.
176
177 e) In case of SMCPTO models,
178 1) The p_sch_action of RESERVE on a pre scheduled line means
179 that none of the other sch attribs changed. If any of the
180 other attrib changes ex: ordered_quantity along with
181 reserved quantity, the p_sch_action will be RESCHEDULE.
182 Thus, on a pre scheduled line whe action RESERVE comes,
183 we just process the change and not log a request in
184 oe_schedule_util.process_request itself.
185 any other attrib change is sending all the lines to mrp.
186
187 2) unschedule, unreserve can not come at the same time
188 of re/schedule. Only reschedule and schedule can come
189 together, due to options window/configurator changes.
190
191 f)FOR ATO, since we are overwriting the requests:
192 Spoke with Navneet about Schedule and Reschedule
193 coming together in case of ATO. He suggest that
194 if a group is scheduled, always send RESCHEDULE,
195 even if the line is getting added is not yet
196 scheduled. **ato_line_id may not be correct on class lines.
197
198 old values needed for rescheduling:
199 1.ship_from_org_id
200 2.demand_class_code
201 3.request_date,
202 4.schedule_ship_date,
203 5.schedule_arrival_date
204 6.ato_delete_flag : in case of deletes of ato options.
205
206 g)
207 Make sure that for smc, the l_res_change_flag is not
208 overwritten by some other rescheduling change.
209
210 pto_ato_nonui can not get overwritten by a value other
211 that Y if once set to Y becasue all records will
212 satisfy the condition whcih sets it.
213
214 e) if the caller is EXTERNAL i.e wf, the action
215 can be only SCHEDULE.
216
217 Changes have been made to pass override flag to schedule_nonsmc and
218 schedule_ato delayed request
219 ----------------------------------------------------------*/
220
221 PROCEDURE Log_Config_Sch_Request
222 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
223 ,p_old_line_rec IN OE_Order_PUB.Line_Rec_Type
224 ,p_sch_action IN VARCHAR2
225 ,p_caller IN VARCHAR2 := OE_SCHEDULE_UTIL.SCH_INTERNAL
226 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
227 IS
228 l_sch_action VARCHAR2(30);
229 l_unreserve_flag VARCHAR2(1);
230 l_pto_ato_nonui VARCHAR2(1) := 'N';
231 l_model_sch_status_code VARCHAR2(30);
232 l_model_ship_from NUMBER;
233 l_model_demand_class VARCHAR2(30);
234 l_model_ship_date DATE;
235 l_model_arrival_date DATE;
236 l_model_request_date DATE;
237 --
238 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
239 --
240 BEGIN
241
242 IF l_debug_level > 0 THEN
243 oe_debug_pub.add( 'ENTERING LOG_CONFIG_SCH_REQUEST' , 3 ) ;
244 END IF;
245
246 x_return_status := FND_API.G_RET_STS_SUCCESS;
247
248 -- the validate config flag is set to N in all 3 procedures
249 -- 1. Cascade_Changes
250 -- 2. Validate_Configuration
251 -- 3. Modify_Included_Items.
252
253 IF OE_Config_Pvt.OECFG_VALIDATE_CONFIG = 'N' AND
254 OE_CONFIG_UTIL.G_CONFIG_UI_USED = 'N'
255 THEN
256 IF p_line_rec.item_type_code = 'INCLUDED' THEN
257 IF l_debug_level > 0 THEN
258 oe_debug_pub.add( 'INC ITEM: CASCADE/MODIFY_INC_ITEMS CALL ' , 3 ) ;
259 END IF;
260 RETURN;
261 END IF;
262
263 IF p_line_rec.ship_model_complete_flag = 'Y' THEN
264 IF l_debug_level > 0 THEN
265 oe_debug_pub.add( 'SMC VALIDATE CFG CHANGES' , 3 ) ;
266 END IF;
267 RETURN;
268 END IF;
269
270 IF p_line_rec.ato_line_id is not NULL AND
271 p_line_rec.ato_line_id = p_line_rec.top_model_line_id THEN
272 IF l_debug_level > 0 THEN
273 oe_debug_pub.add( 'PURE ATO VALIDATE CFG CHANGES' , 3 ) ;
274 END IF;
275 RETURN;
276 END IF;
277 END IF;
278
279
280 l_sch_action := p_sch_action;
281
282 IF p_sch_action = OE_Schedule_Util.OESCH_ACT_SCHEDULE AND
283 (p_line_rec.ato_line_id is not null OR
284 p_line_rec.ship_model_complete_flag = 'Y')
285 THEN
286 -- we do not want to query for nonsmc
287
288 SELECT schedule_status_code, ship_from_org_id,
289 demand_class_code, schedule_ship_date,
290 schedule_arrival_date, request_date
291 INTO l_model_sch_status_code, l_model_ship_from,
292 l_model_demand_class, l_model_ship_date,
293 l_model_arrival_date, l_model_request_date
294 FROM oe_order_lines
295 WHERE line_id = p_line_rec.top_model_line_id;
296
297 IF l_model_sch_status_code is NOT NULL THEN
298 l_sch_action := OE_Schedule_Util.OESCH_ACT_RESCHEDULE;
299 END IF;
300
301 END IF;
302 --------- done l_sch_action and return part -----------------
303
304
305 -- log Reservation / ord qty changes
306 IF (p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE OR
307 NOT OE_GLOBALS.EQUAL(p_line_rec.reserved_quantity,
308 p_old_line_rec.reserved_quantity)) AND
309 p_line_rec.top_model_line_id <> nvl(p_line_rec.ato_line_id, -1)
310
311 THEN
312 Log_Attribute_Changes
313 (p_line_rec => p_line_rec
314 ,p_old_line_rec => p_old_line_rec
315 ,x_unreserve_flag => l_unreserve_flag);
316 END IF;
317
318
319 -- ato_line_id can be incorrect rarely
320
321 IF OE_Config_Util.G_Config_UI_Used = 'N' AND
322 p_line_rec.operation = OE_GLOBALS.G_OPR_CREATE AND
323 p_line_rec.ship_model_complete_flag = 'N' AND
324 p_line_rec.ato_line_id is not NULL
325 THEN
326 IF p_line_rec.item_type_code = 'CLASS' OR
327 p_line_rec.top_model_line_id <> p_line_rec.ato_line_id THEN
328 IF l_debug_level > 0 THEN
329 oe_debug_pub.add( 'SETTING PTO_ATO_NONUI TO Y' , 3 ) ;
330 END IF;
331 l_pto_ato_nonui := 'Y';
332 END IF;
333 END IF;
334
335
336 -------------------- SMC PTO log req -----------------------
337
338 IF p_line_rec.ship_model_complete_flag = 'Y' THEN
339
340 IF l_debug_level > 0 THEN
341 oe_debug_pub.add( 'LOGGING REQ TO SCHEDULE_SMC' , 3 ) ;
342 END IF;
343
344 -- 4052648 : Parameters p_param14 - 17 are added
345 OE_Delayed_Requests_Pvt.Log_Request
346 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
347 p_entity_id => p_line_rec.top_model_line_id,
348 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
349 p_requesting_entity_id => p_line_rec.line_id,
350 p_request_type => OE_GLOBALS.G_SCHEDULE_SMC,
351 p_param1 => l_sch_action,
352 p_param2 => p_line_rec.top_model_line_id,
353 p_param3 => p_line_rec.ship_from_org_id,
354 p_param4 => p_line_rec.ship_to_org_id,
355 p_param5 => p_line_rec.shipping_method_code,
356 p_param6 => p_line_rec.demand_class_code,
357 p_param7 => nvl(p_old_line_rec.ship_from_org_id,
358 l_model_ship_from),
359 p_param8 => nvl(p_old_line_rec.demand_class_code,
360 l_model_demand_class),
361 p_param14 => p_old_line_rec.ship_to_org_id,
362 p_param15 => p_old_line_rec.shipping_method_code,
363 p_param16 => p_old_line_rec.planning_priority,
364 p_param17 => p_old_line_rec.delivery_lead_time,
365 p_param24 => l_unreserve_flag, -- res changes**
366 p_param25 => p_line_rec.header_id,
367 p_date_param1 => p_line_rec.request_date,
368 p_date_param2 => p_line_rec.schedule_ship_date,
369 p_date_param3 => p_line_rec.schedule_arrival_date,
370 p_date_param4 => nvl(p_old_line_rec.request_date,
371 l_model_request_date),
372 p_date_param5 => nvl(p_old_line_rec.schedule_ship_date,
373 l_model_ship_date),
374 p_date_param6 => nvl(p_old_line_rec.schedule_arrival_date,
375 l_model_arrival_date),
376 x_return_status => x_return_status);
377
378
379 -------------------- ATO log req -----------------------
380
381 ELSIF p_line_rec.ato_line_id IS NOT NULL AND
382 NOT (p_line_rec.ato_line_id = p_line_rec.line_id AND
383 p_line_rec.item_type_code = 'OPTION')AND
384 NOT (p_line_rec.ato_line_id = p_line_rec.line_id AND
385 p_line_rec.item_type_code = 'INCLUDED') THEN --9775352
386
387 IF l_debug_level > 0 THEN
388 oe_debug_pub.add( 'LOGGING REQ TO SCHEDULE_ATO' , 3 ) ;
389 END IF;
390
391 -- 4052648 : Parameters p_param14 - 17 are added
392 OE_Delayed_Requests_Pvt.Log_Request
393 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
394 p_entity_id => p_line_rec.ato_line_id,
395 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
396 p_requesting_entity_id => p_line_rec.line_id,
397 p_request_type => OE_GLOBALS.G_SCHEDULE_ATO,
398 p_param1 => l_sch_action,
399 p_param2 => p_line_rec.top_model_line_id,
400 p_param3 => p_line_rec.ship_from_org_id,
401 p_param4 => p_line_rec.ship_to_org_id,
402 p_param5 => p_line_rec.shipping_method_code, -- not req.
403 p_param6 => p_line_rec.demand_class_code, -- not req.
404 p_param7 => nvl(p_old_line_rec.ship_from_org_id,
405 l_model_ship_from),
406 p_param8 => nvl(p_old_line_rec.demand_class_code,
407 l_model_demand_class),
408 p_param9 => l_pto_ato_nonui,
409 p_param11 => p_line_rec.override_atp_date_code,
410 p_param14 => p_old_line_rec.ship_to_org_id,
411 p_param15 => p_old_line_rec.shipping_method_code,
412 p_param16 => p_old_line_rec.planning_priority,
413 p_param17 => p_old_line_rec.delivery_lead_time,
414 p_date_param1 => p_line_rec.request_date,
415 p_date_param2 => p_line_rec.schedule_ship_date,
416 p_date_param3 => p_line_rec.schedule_arrival_date,
417 p_date_param4 => nvl(p_old_line_rec.request_date,
418 l_model_request_date),
419 p_date_param5 => nvl(p_old_line_rec.schedule_ship_date,
420 l_model_ship_date),
421 p_date_param6 => nvl(p_old_line_rec.schedule_arrival_date,
422 l_model_arrival_date),
423 x_return_status => x_return_status);
424
425
426
427 -------------------- NON SMC log req -----------------------
428
429 ELSE
430 IF l_debug_level > 0 THEN
431 oe_debug_pub.add( 'LOGGING REQ TO SCHEDULE_NONSMC' , 3 ) ;
432 END IF;
433
434 -- 4052648 : Parameters p_param14 - 17 are added
435 OE_Delayed_Requests_Pvt.Log_Request
436 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
437 p_entity_id => p_line_rec.line_id,
438 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
439 p_requesting_entity_id => p_line_rec.line_id,
440 p_request_type => OE_GLOBALS.G_SCHEDULE_NONSMC,
441 p_param1 => l_sch_action,
442 p_param2 => p_line_rec.top_model_line_id,
443 p_param3 => p_line_rec.ship_from_org_id,
444 p_param4 => p_line_rec.ship_to_org_id,
445 p_param5 => p_line_rec.shipping_method_code,
446 p_param6 => p_line_rec.demand_class_code,
447 p_param7 => p_old_line_rec.ship_from_org_id,
448 p_param8 => p_old_line_rec.demand_class_code,
449 p_param9 => l_pto_ato_nonui,
450 p_param10 => 'Y',
451 p_param11 => p_line_rec.override_atp_date_code,
452 p_param14 => p_old_line_rec.ship_to_org_id,
453 p_param15 => p_old_line_rec.shipping_method_code,
454 p_param16 => p_old_line_rec.planning_priority,
455 p_param17 => p_old_line_rec.delivery_lead_time,
456 p_param24 => l_unreserve_flag, -- res changes**
457 p_param25 => p_line_rec.header_id,
458 p_date_param1 => p_line_rec.request_date,
459 p_date_param2 => p_line_rec.schedule_ship_date,
460 p_date_param3 => p_line_rec.schedule_arrival_date,
461 p_date_param4 => p_old_line_rec.request_date,
462 p_date_param5 => p_old_line_rec.schedule_ship_date,
463 p_date_param6 => p_old_line_rec.schedule_arrival_date,
464 x_return_status => x_return_status);
465
466 END IF;
467
468
469 IF p_caller = OE_SCHEDULE_UTIL.SCH_EXTERNAL THEN
470
471 IF l_debug_level > 0 THEN
472 oe_debug_pub.add( 'CALLER IS EXTERNAL' , 1 ) ;
473 END IF;
474
475 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
476 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE
477 ,p_delete => FND_API.G_TRUE
478 ,x_return_status => x_return_status );
479
480
481 IF l_debug_level > 0 THEN
482 oe_debug_pub.add( 'After calling request for entity ' || X_RETURN_STATUS , 1 ) ;
483 END IF;
484
485 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
487 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
488 RAISE FND_API.G_EXC_ERROR;
489 END IF;
490
491 OE_Order_PVT.Process_Requests_And_Notify
492 ( x_return_status => x_return_status);
493
494 IF l_debug_level > 0 THEN
495 oe_debug_pub.add( '1 RETURN_STATUS IS ' || X_RETURN_STATUS , 1 ) ;
496 END IF;
497
498 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
500 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503
504 END IF;
505
506 IF l_debug_level > 0 THEN
507 oe_debug_pub.add( 'LEAVING LOG_CONFIG_SCH_REQUEST '|| X_RETURN_STATUS , 3 ) ;
508 END IF;
509
510 EXCEPTION
511 WHEN OTHERS THEN
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
513 IF l_debug_level > 0 THEN
514 oe_debug_pub.add( 'LOG_CONFIG_SCH_REQUEST ERROR '|| SQLERRM , 1 ) ;
515 END IF;
516
517 Delete_Attribute_Changes
518 (p_entity_id => p_line_rec.top_model_line_id);
519
520 RAISE;
521 END Log_Config_Sch_Request;
522
523
524
525 /*-----------------------------------------------------------
526 PROCEDURE Log_Attribute_Changes
527
528 This procedure can be used to save what key sch. params are
529 changed so that the sch action in not simply
530 SCHEDULE/RESCHEDULE, but needs some addtional work like
531 RESERVE/UNRESERVE.
532
533 Please note that the order of attribute change check is
534 very important because we are overwriting
535 x_qty_to_reserve and x_qty_to_unreserve.
536 ------------------------------------------------------------*/
537 PROCEDURE Log_Attribute_Changes
538 (p_line_rec IN OE_Order_PUB.Line_Rec_Type
539 ,p_old_line_rec IN OE_Order_PUB.Line_Rec_Type
540 ,x_unreserve_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
541 IS
542 l_diff_res_qty NUMBER;
543 l_qty_to_reserve NUMBER;
544 l_qty_to_unreserve NUMBER;
545 l_index NUMBER;
546 I NUMBER;
547 --
548 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
549 --
550 BEGIN
551 IF l_debug_level > 0 THEN
552 oe_debug_pub.add( 'ENTERING LOG_ATTRIBUTE_CHANGES' , 3 ) ;
553 END IF;
554
555 x_unreserve_flag := 'N';
556 --l_index := p_line_rec.line_id;
557 l_index := MOD(p_line_rec.line_id,G_BINARY_LIMIT); --7827737
558 -- 1. ordered qty
559 IF NOT OE_GLOBALS.Equal(p_line_rec.ordered_quantity,
560 p_old_line_rec.ordered_quantity)
561 THEN
562 IF l_debug_level > 0 THEN
563 oe_debug_pub.add( 'NEW ORD QTY '|| P_LINE_REC.ORDERED_QUANTITY , 4 ) ;
564 END IF;
565
566 IF nvl(p_old_line_rec.reserved_quantity, 0) >
567 p_line_rec.ordered_quantity
568 THEN
569 IF l_debug_level > 0 THEN
570 oe_debug_pub.add( 'MAY NEED TO UNRESERVE' , 4 ) ;
571 END IF;
572 l_qty_to_unreserve
573 := p_old_line_rec.reserved_quantity - p_line_rec.ordered_quantity;
574 END IF;
575
576 x_unreserve_flag := 'Y';
577
578 END IF;
579
580
581 -- 2. reserved qty
582 l_diff_res_qty := nvl(p_line_rec.reserved_quantity, 0) -
583 nvl(p_old_line_rec.reserved_quantity, 0);
584
585 IF l_debug_level > 0 THEN
586 oe_debug_pub.add( 'RES QTY DIFF '|| L_DIFF_RES_QTY , 1 ) ;
587 END IF;
588
589 IF l_diff_res_qty > 0 THEN
590 l_qty_to_reserve := l_diff_res_qty;
591 l_qty_to_unreserve := null;
592 ELSIF l_diff_res_qty < 0 THEN
593 l_qty_to_unreserve := 0 - l_diff_res_qty;
594 END IF;
595
596
597 -- 3. ship from org
598 IF NOT OE_GLOBALS.Equal(p_line_rec.ship_from_org_id,
599 p_old_line_rec.ship_from_org_id)
600 THEN
601
602 IF p_old_line_rec.ship_from_org_id is NOT NULL THEN
603 x_unreserve_flag := 'Y';
604 IF l_debug_level > 0 THEN
605 oe_debug_pub.add( 'OLD SFROM '||P_OLD_LINE_REC.SHIP_FROM_ORG_ID , 4 ) ;
606 END IF;
607 END IF;
608
609 l_qty_to_reserve
610 := p_old_line_rec.reserved_quantity + l_diff_res_qty;
611 l_qty_to_unreserve
612 := p_old_line_rec.reserved_quantity;
613 END IF;
614
615
616 IF l_debug_level > 0 THEN
617 oe_debug_pub.add( P_LINE_REC.LINE_ID || '.'|| L_QTY_TO_RESERVE , 3 ) ;
618 END IF;
619
620 IF l_qty_to_reserve is not NULL OR
621 l_qty_to_unreserve is not NULL THEN
622
623 OE_Reservations_Tbl(l_index).entity_id
624 := p_line_rec.top_model_line_id;
625 OE_Reservations_Tbl(l_index).line_id
626 := p_line_rec.line_id;
627 OE_Reservations_Tbl(l_index).qty_to_reserve
628 := l_qty_to_reserve;
629 OE_Reservations_Tbl(l_index).qty_to_unreserve
630 := l_qty_to_unreserve;
631
632 IF l_debug_level > 0 THEN
633 oe_debug_pub.add( 'RES ' || OE_RESERVATIONS_TBL ( L_INDEX ) .QTY_TO_RESERVE , 3 ) ;
634 END IF;
635 IF l_debug_level > 0 THEN
636 oe_debug_pub.add( 'ENTITY_ID '|| OE_RESERVATIONS_TBL ( L_INDEX ) .ENTITY_ID , 3 ) ;
637 END IF;
638 IF l_debug_level > 0 THEN
639 oe_debug_pub.add( 'LINE_ID ' || OE_RESERVATIONS_TBL ( L_INDEX ) .LINE_ID , 3 ) ;
640 END IF;
641
642 IF l_qty_to_unreserve is NOT NULL THEN
643 IF l_debug_level > 0 THEN
644 oe_debug_pub.add( 'UNRES '||OE_RESERVATIONS_TBL ( L_INDEX ) .QTY_TO_UNRESERVE , 3 ) ;
645 END IF;
646 x_unreserve_flag := 'Y';
647 END IF;
648
649 END IF;
650
651 IF l_debug_level > 0 THEN
652 oe_debug_pub.add( 'LEAVING LOG_ATTRIBUTE_CHANGES '||X_UNRESERVE_FLAG , 3 ) ;
653 END IF;
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 IF l_debug_level > 0 THEN
658 oe_debug_pub.add( 'LOG_ATTRIBUTE_CHANGES ERROR '|| SQLERRM , 1 ) ;
659 END IF;
660 RAISE;
661 END Log_Attribute_Changes;
662
663
664 /*------------------------------------------------------------
665 PROCEDURE Delete_Attribute_Changes
666
667 This procedure can be used to clean up the global table
668 where we store the reservations related informtion.
669
670 Use in the exceptions handlers of log_config_sch_requests
671 and schedule_smc, schedule_nonsmc.
672 ------------------------------------------------------------*/
673 PROCEDURE Delete_Attribute_Changes
674 (p_entity_id NUMBER := -1)
675 IS
676 I NUMBER;
677 --
678 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
679 --
680 BEGIN
681
682 IF l_debug_level > 0 THEN
683 oe_debug_pub.add( 'ENTERING DELETE_ATTRIBUTE_CHANGES '|| P_ENTITY_ID , 3 ) ;
684 END IF;
685
686 IF p_entity_id = -1 THEN
687 OE_Reservations_Tbl.DELETE;
688 RETURN;
689 END IF;
690
691 I := OE_Reservations_Tbl.FIRST;
692
693 WHILE I is not NULL
694 LOOP
695 IF l_debug_level > 0 THEN
696 oe_debug_pub.add( '---- LINE '|| OE_RESERVATIONS_TBL ( I ) .LINE_ID ) ;
697 END IF;
698 IF l_debug_level > 0 THEN
699 oe_debug_pub.add( 'QTY_TO_RES '|| OE_RESERVATIONS_TBL ( I ) .QTY_TO_RESERVE ) ;
700 END IF;
701
702 IF OE_Reservations_Tbl(I).entity_id = p_entity_id THEN
703 IF l_debug_level > 0 THEN
704 oe_debug_pub.add( 'DELETING '|| I , 3 ) ;
705 END IF;
706 OE_Reservations_Tbl.DELETE(I);
707 END IF;
708
709 I := OE_Reservations_Tbl.NEXT(I);
710 END LOOP;
711
712 IF l_debug_level > 0 THEN
713 oe_debug_pub.add( 'LEAVING LOG_ATTRIBUTE_CHANGES' , 3 ) ;
714 END IF;
715
716 EXCEPTION
717 WHEN OTHERS THEN
718 IF l_debug_level > 0 THEN
719 oe_debug_pub.add( 'DELETE_ATTRIBUTE_CHANGES ERROR '|| SQLERRM , 1 ) ;
720 END IF;
721 RAISE;
722 END Delete_Attribute_Changes;
723
724
725 /*------------------------------------------------------------
726 PROCEDURE Schedule_SMC
727
728 Dexcription:
729
730 Since this is a PTO model, we need to take care of included
731 items.
732
733 The possible actions to come here are,
734 SCHEDULE/RESERVE : query all options and bunch up all the lines
735 pass to mrp.
736 Note: action of RESERVE comes only if the reserved quantity
737 is changed on an unscheduled line or a scheduled line.
738 Note that for RESERVE to come, none of the other
739 sch. attributes are changed otherwise the action becomes
740 SCHEDULE or RESCHEDULE respectively. We will not even
741 log a delayed request for the scheduled line if only
742 reserved quantity changes, control will not come here.
743
744 UNSCHEDULE: query and work on all options
745 RESCHEDULE: query and work on all options.
746
747 If action is RESCHEDULE, we may need to do
748 unreserve/reserve depending on the change in the sch. attribs.
749
750 We do not need undemand/redemand because, change of
751 inventory_item is not allowed on configurations.
752
753 *may not always need to reschedule all lines.
754 *reserved quantity can be changed and diff on all lines.
755
756 Since RESCHEDULE with ship_from/reservation changed is a
757 special case and required some complicated logic,
758 it is handled in a seperate procedure.
759
760 if lines are added to a scheduled smc, we will have to pass all
761 lines in the smc: we have to honour the latest_accetable_date
762 i.e. if we pass only the new lines iwth clearinf lates-acc_date,
763 the insert itself can fail if sch fails. If larest_acc
764 is present, all lines will get pushed. Let us not
765 complicate the code for not so common case.
766 the action itself will be made RESCHEDULE in above case.
767
768 latest_acceptable_date if null, mrp can not give nay dates
769 other than passed.
770 -------------------------------------------------------------*/
771 PROCEDURE Schedule_SMC
772 ( p_request_rec IN OE_Order_Pub.Request_Rec_Type
773 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
774 IS
775 l_count NUMBER := 0;
776 l_msg_count NUMBER;
777 l_msg_data VARCHAR2(2000);
778 l_line_tbl OE_ORDER_PUB.line_tbl_type;
779 l_old_line_tbl OE_Order_PUB.line_tbl_type;
780 l_control_rec OE_GLOBALS.control_rec_type;
781 I NUMBER;
782 l_send_cancel_lines VARCHAR2(1); -- 2882255
783 --
784 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
785 --
786 BEGIN
787 Print_Time('entering Schedule_SMC ' || p_request_rec.entity_id);
788
789 x_return_status := FND_API.G_RET_STS_SUCCESS;
790 -- 2882255
791 IF p_request_rec.param1 = Oe_Schedule_Util.OESCH_ACT_SCHEDULE
792 OR p_request_rec.param1 = Oe_Schedule_Util.OESCH_ACT_DEMAND THEN
793 l_send_cancel_lines := 'N';
794 ELSE
795 l_send_cancel_lines := 'Y';
796 END IF;
797 --
798 BEGIN
799
800 Query_Set_Lines
801 ( p_header_id => p_request_rec.param25
802 ,p_model_line_id => p_request_rec.entity_id
803 ,p_sch_action => p_request_rec.param1
804 ,p_send_cancel_lines => l_send_cancel_lines --'Y' 2885522
805 ,x_line_tbl => l_line_tbl
806 ,x_return_status => x_return_status);
807
808
809 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
810 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
811 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
812 RAISE FND_API.G_EXC_ERROR;
813 END IF;
814
815 l_old_line_tbl := l_line_tbl;
816
817 I := l_line_tbl.FIRST;
818 WHILE I is NOT NULL
819 LOOP
820 Validate_And_Assign_Sch_Params
821 ( p_request_rec => p_request_rec
822 ,p_x_line_rec => l_line_tbl(I)
823 ,p_x_old_line_rec => l_old_line_tbl(I));
824 I := l_line_tbl.NEXT(I);
825 END LOOP;
826
827 ------------- setting attributes done -------------------
828
829 IF l_debug_level > 0 THEN
830 oe_debug_pub.add( 'CALLING PROCESS_GROUP IN SMC' , 3 ) ;
831 END IF;
832
833 Process_Group
834 (p_x_line_tbl => l_line_tbl
835 ,p_old_line_tbl => l_old_line_tbl
836 ,p_sch_action => p_request_rec.param1
837 ,p_handle_unreserve => p_request_rec.param24
838 ,x_return_status => x_return_status);
839
840 EXCEPTION
841 WHEN OTHERS THEN
842 Delete_Attribute_Changes
843 (p_entity_id => p_request_rec.entity_id);
844
845 RAISE;
846 END;
847
848 Print_Time('leaving Schedule_SMC ' || p_request_rec.entity_id);
849 EXCEPTION
850 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
851 IF l_debug_level > 0 THEN
852 oe_debug_pub.add( 'UNEXP ERROR IN SCHEDULE_SMC' , 1 ) ;
853 END IF;
854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
856
857 WHEN FND_API.G_EXC_ERROR THEN
858 IF l_debug_level > 0 THEN
859 oe_debug_pub.add( 'EXC ERROR IN SCHEDULE_SMC' , 1 ) ;
860 END IF;
861 x_return_status := FND_API.G_RET_STS_ERROR;
862 RAISE FND_API.G_EXC_ERROR;
863
864 WHEN OTHERS THEN
865 IF l_debug_level > 0 THEN
866 oe_debug_pub.add( 'OTHERS ERROR IN SCHEDULE_SMC '|| SQLERRM , 1 ) ;
867 END IF;
868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
870 THEN
871 OE_MSG_PUB.Add_Exc_Msg
872 ( G_PKG_NAME
873 ,'Schedule_SMC'
874 );
875 END IF;
876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
877 END Schedule_SMC;
878
879
880 /*------------------------------------------------------------
881 PROCEDURE Schedule_ATO
882 Description:
883
884 In case of ATO we always need to call MRP with the
885 entire group. This is a CTO's requirement.
886
887 Action RESERVE and UNRESERVE is not allowed on ATO's.
888 Action UNSCHEDULE is not allowed on ATO's if config
889 item is created.
890
891 getting mandatory components is done in load_mrp.
892
893 we are explicitly overwrite some values to all the
894 lines in the queried line_tbl, because scheduling
895 will make sure that these values are same on all
896 the lines of an ato configuration.
897 these columns are:
898 1) ship_from_org_id
899 2) ship_to_org_id
900 3) request_date
901 4) schedule_ship_date
902 5) schedule_arrival_date
903
904 NOTE:
905 The l_old_line_tbl is not doing any role in this proceudre.
906 It is passed only so that we pass it on the oe_order_pvt.lines
907 and avoid lines procedure querying all the same lines again.
908 This is same with Schedule_SMC and Schedule_NONSMC also.
909 Also we do not do any setting on the l_control_rec.
910
911 The old values of ship_from and demand_class saved in the
912 delayed_request are going to be the same on all the ato
913 options hance not passed on the old_line_tbl to
914 re/call_mrp. Thus saving looping through the
915 table.
916
917 Since you can not reserve/unreserve ato, all the actions
918 'SCHEDULE''UNSCHEDULE''RESCHEDULE', will be processed
919 by calling call_mrp only.
920 -------------------------------------------------------------*/
921 PROCEDURE Schedule_ATO
922 ( p_request_rec IN OE_Order_Pub.Request_Rec_Type
923 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
924 IS
925 l_line_tbl OE_ORDER_PUB.line_tbl_type;
926 l_old_line_tbl OE_Order_PUB.line_tbl_type;
927 I NUMBER;
928 l_send_cancel_lines VARCHAR2(1); -- 2882255
929 l_request VARCHAR2(8) := 'ATO';
930 l_request_search_rslt BOOLEAN;
931 --
932 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
933 --
934 BEGIN
935
936 Print_Time('Entering Schedule_ATO ' || p_request_rec.entity_id);
937 x_return_status := FND_API.G_RET_STS_SUCCESS;
938 -- 2882255
939 IF p_request_rec.param1 = Oe_Schedule_Util.OESCH_ACT_SCHEDULE
940 OR p_request_rec.param1 = Oe_Schedule_Util.OESCH_ACT_DEMAND THEN
941 l_send_cancel_lines := 'N';
942 ELSE
943 l_send_cancel_lines := 'Y';
944 END IF;
945 --
946
947 IF p_request_rec.param12 = 'DELETE' THEN
948
949 IF p_request_rec.param13 is not null
950 OR p_request_rec.param14 is not null THEN
951
952 IF Check_For_Request
953 (p_set_id => nvl(p_request_rec.param13,p_request_rec.param14),
954 p_ato_line_id => p_request_rec.entity_id)
955 THEN
956
957 l_request := 'NONE';
958 RETURN;
959
960 ELSE -- check
961
962
963 l_request := 'SET';
964
965 IF p_request_rec.param13 is not null THEN
966
967 Oe_Config_Schedule_Pvt.Query_Set_Lines
968 (p_header_id => p_request_rec.param25,
969 p_arrival_set_id => p_request_rec.param13,
970 p_sch_action => p_request_rec.param1,
971 x_line_tbl => l_line_tbl,
972 x_return_status => x_return_status);
973
974 ELSIF p_request_rec.param14 is not null THEN
975
976 Oe_Config_Schedule_Pvt.Query_Set_Lines
977 (p_header_id => p_request_rec.param25,
978 p_ship_set_id => p_request_rec.param14,
979 p_sch_action => p_request_rec.param1,
980 x_line_tbl => l_line_tbl,
981 x_return_status => x_return_status);
982
983 END IF; -- 13,14
984
985 END IF; -- Check
986
987 ELSIF p_request_rec.param15 = 'Y' THEN
988
989 l_request_search_rslt :=
990 OE_Delayed_Requests_PVT.Check_For_Request
991 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
992 p_entity_id => p_request_rec.param2,
993 p_request_type => OE_GLOBALS.G_SCHEDULE_SMC);
994
995 IF l_request_search_rslt THEN
996 l_request := 'NONE';
997 Return;
998 END IF;
999
1000 l_request := 'SMC';
1001 Query_Set_Lines
1002 ( p_header_id => p_request_rec.param25
1003 ,p_model_line_id => p_request_rec.param2
1004 ,p_sch_action => p_request_rec.param1
1005 ,p_send_cancel_lines => l_send_cancel_lines --'Y' 2885522
1006 ,x_line_tbl => l_line_tbl
1007 ,x_return_status => x_return_status);
1008
1009
1010
1011 END IF; -- Param 13,14,15
1012
1013 END IF; -- Delete
1014
1015 IF l_request = 'ATO' THEN
1016 OE_Config_Util.Query_ATO_Options
1017 ( p_ato_line_id => p_request_rec.entity_id
1018 ,p_send_cancel_lines => l_send_cancel_lines --'Y' 2882255
1019 --,p_source_type => OE_Globals.G_SOURCE_INTERNAL --3998413
1020 ,x_line_tbl => l_line_tbl);
1021 END IF;
1022
1023 l_old_line_tbl := l_line_tbl;
1024
1025 I := l_line_tbl.FIRST;
1026 WHILE I is NOT NULL
1027 LOOP
1028
1029 Validate_And_Assign_Sch_Params
1030 ( p_request_rec => p_request_rec
1031 ,p_x_line_rec => l_line_tbl(I)
1032 ,p_x_old_line_rec => l_old_line_tbl(I));
1033
1034 I := l_line_tbl.NEXT(I);
1035 END LOOP;
1036
1037
1038 Process_Group
1039 (p_x_line_tbl => l_line_tbl
1040 ,p_old_line_tbl => l_old_line_tbl
1041 ,p_sch_action => p_request_rec.param1
1042 ,p_caller => 'SCHEDULE_ATO'
1043 ,x_return_status => x_return_status);
1044
1045 Print_Time('Exiting Schedule_ATO');
1046
1047 EXCEPTION
1048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1049 IF l_debug_level > 0 THEN
1050 oe_debug_pub.add( 'UNEXP ERROR IN SCHEDULE_ATO' , 1 ) ;
1051 END IF;
1052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054
1055 WHEN FND_API.G_EXC_ERROR THEN
1056 IF l_debug_level > 0 THEN
1057 oe_debug_pub.add( 'EXC ERROR IN SCHEDULE_ATO' , 1 ) ;
1058 END IF;
1059 x_return_status := FND_API.G_RET_STS_ERROR;
1060 RAISE FND_API.G_EXC_ERROR;
1061
1062 WHEN OTHERS THEN
1063 IF l_debug_level > 0 THEN
1064 oe_debug_pub.add( 'OTHERS ERROR IN SCHEDULE_ATO '|| SQLERRM , 1 ) ;
1065 END IF;
1066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1067 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1068 THEN
1069 OE_MSG_PUB.Add_Exc_Msg
1070 ( G_PKG_NAME
1071 ,'Schedule_ATO'
1072 );
1073 END IF;
1074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1075 END Schedule_ATO;
1076
1077
1078 /*------------------------------------------------------------
1079 PROCEDURE Schedule_NONSMC
1080
1081 We will consolidate the Schedule_NONSMC requests in such a
1082 way that the p_request_tbl will have all request_recs with
1083 same scheduling action. Thus, within 1 nonsmc model if
1084 action of reschedule comes on couple of lines and action
1085 unschedule comes on few lines, there will be 2 groups
1086 and this procedure will get executed twice.
1087
1088 The assumption here is, there is a good chance that we get
1089 only 1 action type in 1 process_order call on the nonsmc
1090 lines. In a rare case if more thatn 1 action comes together,
1091 we handle them seperately.
1092
1093 Note: ATO witing nonSMC will be handled by the Schedule_ATO
1094 delayed request and should never come here.
1095 -------------------------------------------------------------*/
1096 PROCEDURE Schedule_NONSMC
1097 ( p_request_tbl IN OUT NOCOPY OE_Order_PUB.request_tbl_type
1098 ,p_res_changes IN VARCHAR2 := 'N'
1099 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1100 IS
1101 l_line_tbl OE_ORDER_PUB.line_tbl_type;
1102 l_old_line_tbl OE_Order_PUB.line_tbl_type;
1103 l_inc_items_tbl OE_Order_PUB.line_tbl_type;
1104 J NUMBER := 0;
1105 L NUMBER := 0;
1106 I NUMBER;
1107 l_sales_order_id NUMBER;
1108 --
1109 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1110 --
1111 BEGIN
1112 Print_Time('entering schedule_nonsmc ' || p_request_tbl.COUNT);
1113
1114 BEGIN
1115
1116 I := p_request_tbl.FIRST;
1117 WHILE I is not NULL
1118 LOOP
1119 IF p_request_tbl(I).param10 = 'Y' THEN
1120
1121 J := J + 1;
1122 IF l_debug_level > 0 THEN
1123 oe_debug_pub.add( 'LINE ' || P_REQUEST_TBL ( I ) .ENTITY_ID , 3 ) ;
1124 END IF;
1125
1126 l_line_tbl(J).line_id := 0;
1127
1128 OE_Line_Util.Query_Row
1129 ( p_line_id => p_request_tbl(I).entity_id
1130 ,x_line_rec => l_line_tbl(J));
1131
1132 IF l_line_tbl(J).schedule_status_code is not null AND
1133 l_line_tbl(J).shippable_flag = 'Y' THEN
1134
1135 IF l_sales_order_id is NULL THEN
1136 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
1137 (l_line_tbl(J).header_id);
1138 END IF;
1139
1140 l_line_tbl(J).reserved_quantity :=
1141 OE_LINE_UTIL.Get_Reserved_Quantity
1142 (p_header_id => l_sales_order_id,
1143 p_line_id => l_line_tbl(J).line_id,
1144 p_org_id => l_line_tbl(J).ship_from_org_id);
1145 END IF;
1146
1147 IF l_line_tbl(J).reserved_quantity = FND_API.G_MISS_NUM THEN
1148 l_line_tbl(J).reserved_quantity := 0;
1149 END IF;
1150
1151 l_old_line_tbl(J) := l_line_tbl(J);
1152
1153 IF l_debug_level > 0 THEN
1154 oe_debug_pub.add( 'CALLING VALIDATE_AND_ASSIGN_SCH_PARAMS' , 3 ) ;
1155 END IF;
1156
1157 Validate_And_Assign_Sch_Params
1158 ( p_request_rec => p_request_tbl(I)
1159 ,p_x_line_rec => l_line_tbl(J)
1160 ,p_x_old_line_rec => l_old_line_tbl(J));
1161
1162
1163 IF l_line_tbl(J).item_type_code in ('MODEL', 'CLASS', 'KIT')
1164 THEN
1165 IF l_debug_level > 0 THEN
1166 oe_debug_pub.add( 'CALLING GET_INCLUDED_ITEMS_TO_SCH' , 3 ) ;
1167 END IF;
1168
1169 Get_Included_Items_To_Sch
1170 ( p_x_line_tbl => l_line_tbl
1171 ,p_x_old_line_tbl => l_old_line_tbl
1172 ,p_request_tbl => p_request_tbl
1173 ,p_in_index => J
1174 ,p_req_index => I);
1175
1176 END IF;
1177
1178 ELSE
1179 IF l_debug_level > 0 THEN
1180 oe_debug_pub.add( 'ALREADY PROCESSED INC ITEM' , 3 ) ;
1181 END IF;
1182 END IF;
1183
1184 I := p_request_tbl.NEXT(I);
1185
1186 END LOOP;
1187
1188 IF l_debug_level > 0 THEN
1189 oe_debug_pub.add( P_REQUEST_TBL ( 1 ) .PARAM1||' '||P_REQUEST_TBL ( 1 ) .PARAM24 , 3 ) ;
1190 END IF;
1191
1192 Process_Group
1193 (p_x_line_tbl => l_line_tbl
1194 ,p_old_line_tbl => l_old_line_tbl
1195 ,p_sch_action => p_request_tbl(1).param1
1196 ,p_handle_unreserve => p_res_changes
1197 ,x_return_status => x_return_status);
1198
1199 EXCEPTION
1200 WHEN OTHERS THEN
1201 Delete_Attribute_Changes
1202 (p_entity_id => p_request_tbl(1).entity_id);
1203
1204 RAISE;
1205 END;
1206
1207 Print_Time('leaving schedule_nonsmc');
1208 EXCEPTION
1209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1210 IF l_debug_level > 0 THEN
1211 oe_debug_pub.add( 'UNEXP ERROR IN SCHEDULE_NONSMC' , 1 ) ;
1212 END IF;
1213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1215
1216 WHEN FND_API.G_EXC_ERROR THEN
1217 IF l_debug_level > 0 THEN
1218 oe_debug_pub.add( 'EXC ERROR IN SCHEDULE_NONSMC' , 1 ) ;
1219 END IF;
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221 RAISE FND_API.G_EXC_ERROR;
1222
1223 WHEN OTHERS THEN
1224 IF l_debug_level > 0 THEN
1225 oe_debug_pub.add( 'OTHERS IN SCHEDULE_NONSMC '|| SQLERRM , 1 ) ;
1226 END IF;
1227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1228 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1229 THEN
1230 OE_MSG_PUB.Add_Exc_Msg
1231 ( G_PKG_NAME
1232 ,'Schedule_NONSMC'
1233 );
1234 END IF;
1235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1236 END Schedule_NONSMC;
1237
1238
1239 /*----------------------------------------------------------
1240 Procedure Get_Included_Items_To_Sch
1241 This procedure is used to get the included items
1242 in case of scheduling a non SMC model.
1243
1244 Bug 2463742: call to log_attribute_changes for INCLUDED item
1245 if it is rescheduled because of change on the parent.
1246 -----------------------------------------------------------*/
1247 PROCEDURE Get_Included_Items_To_Sch
1248 ( p_x_line_tbl IN OUT NOCOPY OE_Order_PUB.line_tbl_type
1249 ,p_x_old_line_tbl IN OUT NOCOPY OE_Order_PUB.line_tbl_type
1250 ,p_request_tbl IN OUT NOCOPY OE_Order_PUB.request_tbl_type
1251 ,p_in_index IN OUT NOCOPY /* file.sql.39 change */ NUMBER
1252 ,p_req_index IN NUMBER)
1253 IS
1254 l_inc_items_tbl OE_Order_PUB.line_tbl_type;
1255 L NUMBER := 0;
1256 l_done BOOLEAN;
1257 l_unreserve_flag VARCHAR2(1);
1258 l_send_cancel_lines VARCHAR2(1); -- 2882255
1259 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1260 --
1261 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1262 --
1263 BEGIN
1264 IF l_debug_level > 0 THEN
1265 oe_debug_pub.add( 'ENTERING GET_INCLUDED_ITEMS_TO_SCH' , 3 ) ;
1266 END IF;
1267
1268 -- 2882255
1269 IF p_request_tbl(p_req_index).param1 = Oe_Schedule_Util.OESCH_ACT_SCHEDULE
1270 OR p_request_tbl(p_req_index).param1 = Oe_Schedule_Util.OESCH_ACT_DEMAND THEN
1271 l_send_cancel_lines := 'N';
1272 ELSE
1273 l_send_cancel_lines := 'Y';
1274 END IF;
1275 --
1276
1277 Query_Set_Lines
1278 ( p_header_id => p_x_line_tbl(p_in_index).header_id
1279 ,p_link_to_line_id => p_x_line_tbl(p_in_index).line_id
1280 ,p_sch_action => p_request_tbl(p_req_index).param1
1281 ,p_send_cancel_lines => l_send_cancel_lines -- 'Y' 2882255
1282 ,x_line_tbl => l_inc_items_tbl
1283 ,x_return_status => l_return_status);
1284
1285 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1287 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1288 RAISE FND_API.G_EXC_ERROR;
1289 END IF;
1290
1291 IF l_inc_items_tbl.COUNT > 0 THEN
1292
1293 FOR K in l_inc_items_tbl.FIRST..l_inc_items_tbl.LAST
1294 LOOP
1295
1296 p_in_index := p_in_index + 1;
1297 p_x_line_tbl(p_in_index) := l_inc_items_tbl(K);
1298 p_x_old_line_tbl(p_in_index) := l_inc_items_tbl(K);
1299 IF l_debug_level > 0 THEN
1300 oe_debug_pub.add( K||'INC ITEM '||P_X_LINE_TBL ( P_IN_INDEX ) .LINE_ID , 4 ) ;
1301 END IF;
1302
1303 l_done := FALSE;
1304
1305 L := p_request_tbl.FIRST;
1306 WHILE L is not NULL AND NOT l_done
1307 LOOP
1308 IF p_request_tbl(L).entity_id = l_inc_items_tbl(K).line_id
1309 THEN
1310 IF l_debug_level > 0 THEN
1311 oe_debug_pub.add( L || ' USER CHANGED INC ITEM '|| P_IN_INDEX , 5 ) ;
1312 END IF;
1313
1314 Validate_And_Assign_Sch_Params
1315 ( p_request_rec => p_request_tbl(L) -- imp use L
1316 ,p_x_line_rec => p_x_line_tbl(p_in_index)
1317 ,p_x_old_line_rec => p_x_old_line_tbl(p_in_index));
1318
1319 p_request_tbl(L).param10 := 'N';
1320 l_done := TRUE;
1321 END IF;
1322
1323 L := p_request_tbl.NEXT(L);
1324 END LOOP;
1325
1326 IF l_debug_level > 0 THEN
1327 oe_debug_pub.add( 'LOOPED THRU REQ TBL '|| L , 3 ) ;
1328 END IF;
1329
1330 IF NOT l_done THEN
1331 IF l_debug_level > 0 THEN
1332 oe_debug_pub.add( P_REQ_INDEX||' USER CHANGED PARENT '||P_IN_INDEX , 5 ) ;
1333 END IF;
1334
1335 Validate_And_Assign_Sch_Params
1336 ( p_request_rec => p_request_tbl(p_req_index) -- imp req_index
1337 ,p_x_line_rec => p_x_line_tbl(p_in_index)
1338 ,p_x_old_line_rec => p_x_old_line_tbl(p_in_index));
1339
1340 IF p_request_tbl(p_req_index).param1 =
1341 OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE THEN
1342 Log_Attribute_Changes
1343 (p_line_rec => p_x_line_tbl(p_in_index)
1344 ,p_old_line_rec => p_x_old_line_tbl(p_in_index)
1345 ,x_unreserve_flag => l_unreserve_flag);
1346 END IF;
1347
1348 END IF;
1349
1350 END LOOP; -- over the inc_tbl
1351
1352 END IF; -- if inc tbl count > 0
1353
1354 IF l_debug_level > 0 THEN
1355 oe_debug_pub.add( 'LEAVING GET_INCLUDED_ITEMS_TO_SCH' , 3 ) ;
1356 END IF;
1357
1358 EXCEPTION
1359 WHEN OTHERS THEN
1360 IF l_debug_level > 0 THEN
1361 oe_debug_pub.add( 'GET_INCLUDED_ITEMS_TO_SCH '|| SQLERRM , 1 ) ;
1362 END IF;
1363 RAISE;
1364 END Get_Included_Items_To_Sch;
1365
1366 /*----------------------------------------------------------
1367 PROCEDURE Process_Group
1368
1369 a) This procedure will be used when caller sets all the
1370 sch. attribs on all the lines of a group and now
1371 wants to perform any of the following actions on
1372 all the lines:
1373
1374 p_sch_action will be,
1375 1) SCHEDULE : also need to check the reservation time fence
1376 2) UNSCHEDULE : also need to unreserve if reqd.
1377 3) RESERVE : need to schedule if lines are not prescheduled.
1378 4) UNRESERVE : only unreserve(what about time fence)
1379
1380 b) Current major callers are schedule_multi_selected_lines
1381 and schedule_order APIs and schedule_smc, schedule_ato,
1382 schedule_nonsmc APIs, schedule_sets.
1383
1384 c) The reserved_quantity column should be populated
1385 on the line records by the caller.
1386 reserve_group API is smart enough to look at the sch level,
1387 time fence, shippable_flag, user enterd values etc.
1388
1389 d) The call to save_sch_attribs will try to save lines call
1390 in some cases,
1391 ex: ordered_qty changes and sch. success
1392 new lines addtion to a scheduled grp.
1393
1394 e) the p_partial and p_partial_set parameters are used for
1395 ui and sets caller.
1396
1397 f) the call to reserve_group is moved after the
1398 process_order call, because of the inv change,
1399 they need correct warehouse on the line.
1400 ---------------------------------------------------------*/
1401 PROCEDURE Process_Group
1402 ( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
1403 ,p_old_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
1404 ,p_sch_action IN VARCHAR2
1405 ,p_caller IN VARCHAR2 := 'X'
1406 ,p_handle_unreserve IN VARCHAR2 := 'N'
1407 ,p_partial IN BOOLEAN := FALSE
1408 ,p_partial_set IN BOOLEAN := FALSE
1409 ,p_part_of_set IN VARCHAR2 DEFAULT 'N' -- 4405004
1410 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1411 IS
1412 l_msg_count NUMBER;
1413 l_msg_data VARCHAR2(2000);
1414 l_control_rec OE_GLOBALS.control_rec_type;
1415 l_sch_action VARCHAR2(30);
1416 --
1417 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1418 --
1419 BEGIN
1420 Print_Time('entering Process_Group');
1421
1422 x_return_status := FND_API.G_RET_STS_SUCCESS;
1423
1424 IF p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_UNSCHEDULE OR
1425 p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_UNRESERVE
1426 THEN
1427 IF l_debug_level > 0 THEN
1428 oe_debug_pub.add( 'UNRESERVING...' , 3 ) ;
1429 END IF;
1430
1431 Unreserve_group(p_line_tbl => p_x_line_tbl
1432 ,p_sch_action => p_sch_action); -- 2595661
1433
1434 IF p_sch_action = OE_Schedule_Util.OESCH_ACT_UNSCHEDULE
1435 THEN
1436 IF l_debug_level > 0 THEN
1437 oe_debug_pub.add( 'NOW UNSCHEDULING...' , 3 ) ;
1438 END IF;
1439
1440 Call_Mrp
1441 ( p_x_line_tbl => p_x_line_tbl
1442 ,p_old_line_tbl => p_old_line_tbl
1443 ,p_sch_action => p_sch_action
1444 ,x_return_status => x_return_status);
1445 END IF;
1446
1447 ELSE ---------- action is sch or resch or reserve.-----
1448
1449 IF l_debug_level > 0 THEN
1450 oe_debug_pub.add( 'SCH ACTION IS '|| P_SCH_ACTION , 3 ) ;
1451 END IF;
1452
1453 IF p_sch_action = OE_Schedule_Util.OESCH_ACT_RESERVE THEN
1454 l_sch_action := OE_Schedule_Util.OESCH_ACT_SCHEDULE;
1455 ELSE
1456 l_sch_action := p_sch_action;
1457 END IF;
1458
1459 IF p_sch_action = OE_Schedule_Util.OESCH_ACT_RESCHEDULE AND
1460 (p_handle_unreserve = 'Y' OR
1461 p_caller = 'SET')
1462 THEN
1463 IF l_debug_level > 0 THEN
1464 oe_debug_pub.add( '1 CALLING HANDLE_UNRESERVE' , 4 ) ;
1465 END IF;
1466
1467 Handle_Unreserve
1468 (p_x_line_tbl => p_x_line_tbl
1469 ,p_old_line_tbl => p_old_line_tbl
1470 ,x_return_status => x_return_status);
1471
1472 END IF;
1473
1474 Call_Mrp
1475 ( p_x_line_tbl => p_x_line_tbl
1476 ,p_old_line_tbl => p_old_line_tbl
1477 ,p_sch_action => l_sch_action
1478 ,p_partial => p_partial
1479 ,p_partial_set => p_partial_set
1480 ,p_part_of_set => p_part_of_set --4405004
1481 ,x_return_status => x_return_status);
1482
1483
1484 -- Additing additional Unreserve call as part of item substitution
1485 -- Project. If the item has been replaced by MRP during re-scheduling
1486 -- process then unreserve the reservation on old item.
1487
1488 IF p_sch_action = OE_Schedule_Util.OESCH_ACT_RESCHEDULE AND
1489 p_caller = 'SET'
1490 THEN
1491 IF l_debug_level > 0 THEN
1492 oe_debug_pub.add( '2 CALLING HANDLE_UNRESERVE' , 4 ) ;
1493 END IF;
1494
1495 Handle_Unreserve
1496 (p_x_line_tbl => p_x_line_tbl
1497 ,p_old_line_tbl => p_old_line_tbl
1498 ,p_post_process => FND_API.G_TRUE
1499 ,x_return_status => x_return_status);
1500
1501 END IF;
1502
1503 END IF; ------- end if action = unsch or unres -----
1504
1505
1506 IF p_sch_action <> OE_SCHEDULE_UTIL.OESCH_ACT_UNRESERVE THEN
1507
1508 Save_Sch_Attributes
1509 ( p_x_line_tbl => p_x_line_tbl
1510 ,p_old_line_tbl => p_old_line_tbl
1511 ,p_sch_action => p_sch_action
1512 ,p_caller => OE_SCHEDULE_UTIL.SCH_EXTERNAL
1513 ,x_return_status => x_return_status);
1514
1515 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1517 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1518 RAISE FND_API.G_EXC_ERROR;
1519 END IF;
1520 ELSE
1521 IF l_debug_level > 0 THEN
1522 oe_debug_pub.add( 'NO NEED TO CALL LINES' , 3 ) ;
1523 END IF;
1524 END IF;
1525
1526
1527 IF p_caller <> 'SCHEDULE_ATO' AND
1528 p_sch_action <> OE_Schedule_Util.OESCH_ACT_UNSCHEDULE AND
1529 p_sch_action <> OE_Schedule_Util.OESCH_ACT_UNRESERVE
1530 THEN
1531 IF l_debug_level > 0 THEN
1532 oe_debug_pub.add( 'CALLING RESERVE_GROUP NOW ' , 3 ) ;
1533 END IF;
1534
1535 Reserve_Group
1536 ( p_x_line_tbl => p_x_line_tbl
1537 ,p_sch_action => p_sch_action);
1538 END IF;
1539
1540 Print_Time('leaving Process_Group');
1541
1542 EXCEPTION
1543 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1546
1547 WHEN FND_API.G_EXC_ERROR THEN
1548 x_return_status := FND_API.G_RET_STS_ERROR;
1549
1550 IF p_caller <> 'UI_ACTION' AND
1551 p_caller <> 'SCHEDULE_ATO'
1552 THEN
1553 Delete_Attribute_Changes
1554 (p_entity_id => nvl(nvl(p_x_line_tbl(1).arrival_set_id,
1555 p_x_line_tbl(1).ship_set_id),
1556 p_x_line_tbl(1).top_model_line_id));
1557
1558 IF l_debug_level > 0 THEN
1559 oe_debug_pub.add( 'EXC ERROR IN PROCESS_GROUP' , 1 ) ;
1560 END IF;
1561 END IF;
1562
1563 -- do not raise, caller wants to handle: sets
1564 --RAISE FND_API.G_EXC_ERROR;
1565
1566 WHEN OTHERS THEN
1567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1568 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1569 THEN
1570 OE_MSG_PUB.Add_Exc_Msg
1571 ( G_PKG_NAME
1572 ,'Process_Group'
1573 );
1574 END IF;
1575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1576
1577 END Process_Group;
1578
1579
1580 /*----------------------------------------------------------------
1581 PROCEDURE Call_Mrp
1582
1583 The code is same irrespective of action SCHEDULE or UNSCHEDULE
1584 or RESCHEDULE.
1585
1586 Aswin confirmed that once I pass p_sch_action to load_mrp
1587 and load_results, I do not have to pass schedule_action_code
1588 on individual lines. This is so that I can avoid looping
1589 through the line_tbl just to set the schedule_action_code.
1590
1591 Procedure has been modified to fix the bipass calling MRP code
1592 for inactive demand project.1955004
1593 -----------------------------------------------------------------*/
1594 PROCEDURE Call_Mrp
1595 ( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
1596 ,p_old_line_tbl IN OE_Order_Pub.Line_Tbl_Type
1597 ,p_sch_action IN VARCHAR2
1598 ,p_partial IN BOOLEAN := FALSE
1599 ,p_partial_set IN BOOLEAN := FALSE
1600 ,p_part_of_set IN VARCHAR2 DEFAULT 'N' --4405004
1601 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1602 IS
1603 l_session_id NUMBER := 0;
1604 l_mrp_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
1605 l_out_mtp_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
1606 l_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
1607 l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
1608 l_atp_details MRP_ATP_PUB.ATP_Details_Typ;
1609 l_msg_data VARCHAR2(200);
1610 l_msg_count NUMBER;
1611 l_mrp_called BOOLEAN := FALSE;
1612 l_line_id_mod NUMBER ; --7827737
1613 --
1614 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1615 --
1616 BEGIN
1617
1618 Print_Time('entering Call_Mrp');
1619
1620 x_return_status := FND_API.G_RET_STS_SUCCESS;
1621
1622 OE_Schedule_Util.Load_MRP_request_from_tbl
1623 ( p_line_tbl => p_x_line_tbl
1624 ,p_old_line_tbl => p_old_line_tbl
1625 ,p_sch_action => p_sch_action
1626 ,p_partial_set => p_partial_set
1627 ,p_part_of_set => p_part_of_set --4405004
1628 ,x_mrp_atp_rec => l_mrp_atp_rec);
1629
1630
1631 -- 1955004 modified statement below from = to >
1632 -- removed return and end if to encompass code to add an else clause
1633 IF l_mrp_atp_rec.error_code.count > 0 THEN
1634 IF l_debug_level > 0 THEN
1635 oe_debug_pub.add( 'NEED TO CALL MRP , > 0 COUNT' , 1 ) ;
1636 END IF;
1637
1638
1639 l_session_id := OE_SCHEDULE_UTIL.Get_Session_Id;
1640
1641 IF l_debug_level > 0 THEN
1642 oe_debug_pub.add( 'SESSION ID IN CALL_MRP ' || L_SESSION_ID , 2 ) ;
1643 END IF;
1644
1645 Print_Time('calling mrps atp api');
1646
1647 MRP_ATP_PUB.Call_ATP
1648 ( p_session_id => l_session_id
1649 ,p_atp_rec => l_mrp_atp_rec
1650 ,x_atp_rec => l_out_mtp_atp_rec
1651 ,x_atp_supply_demand => l_atp_supply_demand
1652 ,x_atp_period => l_atp_period
1653 ,x_atp_details => l_atp_details
1654 ,x_return_status => x_return_status
1655 ,x_msg_data => l_msg_data
1656 ,x_msg_count => l_msg_count);
1657
1658 l_mrp_called := TRUE;
1659
1660 Print_Time('After MRPs ATP API: ' || x_return_status);
1661
1662 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1663
1664 OE_SCHEDULE_UTIL.Display_sch_errors
1665 (p_atp_rec => l_out_mtp_atp_rec,
1666 p_line_tbl => p_x_line_tbl);
1667
1668 END IF;
1669
1670 END IF; -- Moved for 1955004
1671
1672 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1673 -- the call to MRP was successful, so load the results
1674
1675 IF l_debug_level > 0 THEN
1676 oe_debug_pub.add( 'CALLING LOAD_RESULTS' , 1 ) ;
1677 END IF;
1678
1679 OE_Schedule_Util.Load_Results_from_tbl
1680 ( p_atp_rec => l_out_mtp_atp_rec
1681 , p_old_line_tbl => p_old_line_tbl -- 1955004
1682 , p_x_line_tbl => p_x_line_tbl
1683 , p_sch_action => p_sch_action
1684 , p_partial => p_partial
1685 , p_partial_set => p_partial_set
1686 , x_return_status => x_return_status);
1687
1688 IF l_debug_level > 0 THEN
1689 oe_debug_pub.add( 'AFTER LOAD_RESULTS: ' || X_RETURN_STATUS , 1 ) ;
1690 END IF;
1691
1692 ELSE
1693 IF l_out_mtp_atp_rec.error_code.count = 0 AND
1694 l_mrp_called THEN
1695 -- we were expecting some date from MRP, but did not get any
1696
1697 IF l_debug_level > 0 THEN
1698 oe_debug_pub.add( 'MRP HAS NOT RETURNED ANY DATA' , 1 ) ;
1699 END IF;
1700
1701 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ATP_ERROR');
1702 OE_MSG_PUB.Add;
1703
1704 END IF;
1705
1706 -- New Code for 1955004
1707 IF OE_SCHEDULE_UTIL.OE_inactive_demand_tbl.count > 0 THEN
1708 -- even though MRP did not have anything, we have some
1709 -- Inactive Demand Rows to process
1710
1711 FOR I in 1.. p_x_line_tbl.count LOOP
1712
1713 l_line_id_mod := MOD(p_x_line_tbl(I).line_id,G_BINARY_LIMIT); --7827737
1714
1715 IF OE_SCHEDULE_UTIL.OE_inactive_demand_tbl.EXISTS
1716 (l_line_id_mod) THEN --7827737
1717 --(p_x_line_tbl(I).line_id) THEN
1718 -- we know this line has an inactive demand scheduling level
1719
1720 IF l_debug_level > 0 THEN
1721 oe_debug_pub.add( 'CALLING INACTIVE_DEMAND_SCHEDULING FROM ELSE IN CALL_MRP' , 1 ) ;
1722 END IF;
1723
1724 OE_SCHEDULE_UTIL.Inactive_demand_scheduling
1725 (p_x_old_line_rec => p_old_line_tbl(I)
1726 ,p_x_line_rec => p_x_line_tbl(I)
1727 ,x_return_status => x_return_status);
1728
1729 OE_SCHEDULE_UTIL.OE_inactive_demand_tbl.DELETE
1730 (l_line_id_mod) ; --7827737
1731 --(p_x_line_tbl(I).line_id);
1732
1733 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1735 END IF;
1736
1737 ELSE
1738 -- this row must have been a part of the Call_ATP call that
1739 -- failed, so set the operator flag.
1740
1741 p_x_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1742
1743 END IF;
1744
1745 END LOOP;
1746
1747 END IF;
1748
1749 END IF;
1750
1751 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1753 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1754 RAISE FND_API.G_EXC_ERROR;
1755 END IF;
1756
1757 Print_Time('leaving Call_Mrp');
1758
1759 EXCEPTION
1760 WHEN OTHERS THEN
1761 IF l_debug_level > 0 THEN
1762 oe_debug_pub.add( 'CALL_MRP ERROR' || SQLERRM , 1 ) ;
1763 END IF;
1764 RAISE;
1765 END Call_Mrp;
1766
1767
1768 /*----------------------------------------------------------------
1769 PROCEDURE Unreserve_group
1770 Note: I am not querying the reserved_qty, it should be set on
1771 the lines records.
1772
1773 This procedure is called from 2 places,
1774 1) process_group, if unschedule or unreserve action.
1775 2) handle_res_changes: called for ship_from change,
1776
1777 we should log the qty to reserve in the reservation_tbl,
1778 does not afftect 1st caller and is used later in
1779 reserve_group.
1780 -----------------------------------------------------------------*/
1781 PROCEDURE Unreserve_group
1782 ( p_line_tbl IN OE_Order_Pub.Line_Tbl_Type
1783 ,p_sch_action IN VARCHAR2 := 'X')
1784 IS
1785 l_return_status VARCHAR2(1);
1786 I NUMBER;
1787 l_line_id_mod NUMBER ; --7827737
1788 --
1789 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1790 --
1791 BEGIN
1792
1793 Print_Time('entering Unreserve_group');
1794
1795 I := p_line_tbl.FIRST;
1796 WHILE I is not NULL
1797 LOOP
1798 l_line_id_mod := MOD(p_line_tbl(I).line_id,G_BINARY_LIMIT); --7827737
1799 IF p_line_tbl(I).reserved_quantity > 0 AND
1800 p_line_tbl(I).reserved_quantity is NOT NULL THEN
1801
1802 IF l_debug_level > 0 THEN
1803 oe_debug_pub.add( P_LINE_TBL ( I ) .LINE_ID||'UNRESERVE ' ||P_LINE_TBL ( I ) .RESERVED_QUANTITY , 3 ) ;
1804 END IF;
1805 -- Start 2595661
1806 IF nvl(p_line_tbl(I).shipping_interfaced_flag,'N') = 'Y' THEN
1807 IF ( (p_sch_action = OE_Schedule_Util.OESCH_ACT_UNRESERVE
1808 OR p_sch_action = OE_Schedule_Util.OESCH_ACT_RESCHEDULE) -- 6628134
1809 AND NOT oe_schedule_util.Get_Pick_Status(p_line_tbl(I).line_id) ) THEN
1810
1811 OE_SCHEDULE_UTIL.Do_Unreserve
1812 ( p_line_rec => p_line_tbl(I)
1813 , p_quantity_to_unreserve => p_line_tbl(I).reserved_quantity
1814 , p_quantity2_to_unreserve => NVL(p_line_tbl(I).reserved_quantity2, 0) -- INVCONV
1815 , x_return_status => l_return_status);
1816 END IF;
1817 ELSE
1818 -- End 2595661
1819
1820 OE_SCHEDULE_UTIL.Unreserve_Line
1821 ( p_line_rec => p_line_tbl(I)
1822 ,p_quantity_to_unreserve => p_line_tbl(I).reserved_quantity
1823 ,p_quantity2_to_unreserve => nvl(p_line_tbl(I).reserved_quantity2, 0) -- INVCONV
1824 ,x_return_status => l_return_status);
1825 END IF; -- 2595661
1826
1827 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1829 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1830 RAISE FND_API.G_EXC_ERROR;
1831 END IF;
1832
1833 IF p_sch_action = OE_Schedule_Util.OESCH_ACT_RESCHEDULE THEN
1834
1835 -- IF NOT OE_Reservations_Tbl.EXISTS(p_line_tbl(I).line_id) OR
1836 -- OE_Reservations_Tbl(p_line_tbl(I).line_id).qty_to_reserve
1837 -- is NULL
1838 IF NOT OE_Reservations_Tbl.EXISTS(l_line_id_mod) OR --7827737
1839 OE_Reservations_Tbl(l_line_id_mod).qty_to_reserve
1840 is NULL
1841 THEN
1842 IF l_debug_level > 0 THEN
1843 oe_debug_pub.add( 'ADDING REC TO RES TBL' , 2 ) ;
1844 END IF;
1845
1846 IF p_line_tbl(I).arrival_set_id is NOT NULL THEN
1847 --OE_Reservations_Tbl(p_line_tbl(I).line_id).entity_id
1848 OE_Reservations_Tbl(l_line_id_mod).entity_id --7827737
1849 := p_line_tbl(I).arrival_set_id;
1850
1851 ELSIF p_line_tbl(I).ship_set_id is NOT NULL THEN
1852 --OE_Reservations_Tbl(p_line_tbl(I).line_id).entity_id
1853 OE_Reservations_Tbl(l_line_id_mod).entity_id --7827737
1854 := p_line_tbl(I).ship_set_id;
1855
1856 ELSIF p_line_tbl(I).top_model_line_id is NOT NULL THEN
1857 --OE_Reservations_Tbl(p_line_tbl(I).line_id).entity_id
1858 OE_Reservations_Tbl(l_line_id_mod).entity_id --7827737
1859 := p_line_tbl(I).top_model_line_id;
1860
1861 END IF;
1862
1863 --OE_Reservations_Tbl(p_line_tbl(I).line_id).line_id
1864 OE_Reservations_Tbl(l_line_id_mod).line_id --7827737
1865 := p_line_tbl(I).line_id;
1866
1867 -- this is only so that we can avoid reservation for CMS.
1868 -- note the change in get_reservation api.
1869 --OE_Reservations_Tbl(p_line_tbl(I).line_id).qty_to_unreserve
1870 OE_Reservations_Tbl(l_line_id_mod).qty_to_unreserve --7827737
1871 := p_line_tbl(I).reserved_quantity;
1872
1873 IF p_line_tbl(I).ordered_quantity > p_line_tbl(I).reserved_quantity
1874 THEN
1875 --OE_Reservations_Tbl(p_line_tbl(I).line_id).qty_to_reserve
1876 OE_Reservations_Tbl(l_line_id_mod).qty_to_reserve --7827737
1877 := p_line_tbl(I).reserved_quantity;
1878 ELSE
1879 --OE_Reservations_Tbl(p_line_tbl(I).line_id).qty_to_reserve
1880 OE_Reservations_Tbl(l_line_id_mod).qty_to_reserve --7827737
1881 := p_line_tbl(I).ordered_quantity;
1882 END IF;
1883
1884 IF l_debug_level > 0 THEN
1885 oe_debug_pub.add( 'RES ' || OE_RESERVATIONS_TBL ( P_LINE_TBL ( I ) .LINE_ID ) .QTY_TO_RESERVE , 3 ) ;
1886 END IF;
1887 END IF;
1888
1889 END IF; -- action = reschedule
1890
1891 END IF; -- res qty > 0
1892
1893 I := p_line_tbl.NEXT(I);
1894
1895 END LOOP;
1896
1897 Print_Time('leaving Unreserve_group');
1898
1899 EXCEPTION
1900 WHEN OTHERS THEN
1901 IF l_debug_level > 0 THEN
1902 oe_debug_pub.add( 'UNRESERVE_GROUP ERROR' || SQLERRM , 1 ) ;
1903 END IF;
1904 RAISE;
1905 END Unreserve_group;
1906
1907
1908 /*----------------------------------------------------------------
1909 PROCEDURE Reserve_Group
1910
1911 This procedure is mainly used when action is plain
1912 SCHEDULE or RESERVE. If the action is RESCHEDULE,
1913 we may follow different route if unreserve, ship_from etc.
1914 comes into picture, for normal reschedule, we still check
1915 the reservation time fence etc.
1916
1917 The order is:
1918 check the scheduling level
1919 if level ok,
1920 check the oe_reservation_tbl,
1921 if record exist, get value.
1922 check the the time fence, if within, reserve.
1923 end if;
1924
1925 Note: Do not call this procedure if the p_sch_action
1926 is UNSCHEDULE or UNRESERVE
1927
1928 **You may want to make the p_line_tbl IN and copy it
1929 to local variable since we are assigning some values to
1930 reserved_quantity whcih may or may not get reserved.
1931 or use l_old_reserved_quantity logic.
1932
1933 p_sch_action is just a hint.
1934 -----------------------------------------------------------------*/
1935 PROCEDURE Reserve_Group
1936 ( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
1937 ,p_sch_action IN VARCHAR2)
1938 IS
1939 l_return_status VARCHAR2(1);
1940 l_do_reserve BOOLEAN;
1941 l_sch_level VARCHAR2(30);
1942 l_scheduled_grp NUMBER;
1943 l_reservation_qty NUMBER;
1944 l_reservation_qty2 NUMBER; -- INVCONV
1945 I NUMBER;
1946 l_rsv_update BOOLEAN := FALSE;
1947 --
1948 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1949 --
1950 BEGIN
1951
1952 Print_Time('entering Reserve_Group ' || p_sch_action);
1953
1954 I := p_x_line_tbl.FIRST;
1955 WHILE I is not NULL
1956 LOOP
1957
1958 OE_MSG_PUB.set_msg_context
1959 ( p_entity_code => 'LINE'
1960 ,p_entity_id => p_x_line_tbl(I).line_id
1961 ,p_header_id => p_x_line_tbl(I).header_id
1962 ,p_line_id => p_x_line_tbl(I).line_id
1963 ,p_orig_sys_document_ref => p_x_line_tbl(I).orig_sys_document_ref
1964 ,p_orig_sys_document_line_ref => p_x_line_tbl(I).orig_sys_line_ref
1965 ,p_orig_sys_shipment_ref => p_x_line_tbl(I).orig_sys_shipment_ref
1966 ,p_change_sequence => p_x_line_tbl(I).change_sequence
1967 ,p_source_document_id => p_x_line_tbl(I).source_document_id
1968 ,p_source_document_line_id => p_x_line_tbl(I).source_document_line_id
1969 ,p_order_source_id => p_x_line_tbl(I).order_source_id
1970 ,p_source_document_type_id => p_x_line_tbl(I).source_document_type_id);
1971
1972 l_reservation_qty := 0;
1973 l_reservation_qty2 := 0; -- INVCONV
1974
1975 IF nvl(p_x_line_tbl(I).shippable_flag, 'N') = 'Y' AND
1976 p_x_line_tbl(I).ordered_quantity > 0 AND
1977 p_x_line_tbl(I).Item_type_code <> 'CONFIG' AND
1978 nvl(p_x_line_tbl(I).schedule_status_code, 'X') =
1979 OE_SCHEDULE_UTIL.OESCH_STATUS_SCHEDULED
1980 THEN
1981 IF l_debug_level > 0 THEN
1982 oe_debug_pub.add( 'WORKING ON LINE '|| P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
1983 END IF;
1984
1985 l_sch_level := OE_SCHEDULE_UTIL.Get_Scheduling_Level
1986 (p_x_line_tbl(I).header_id,
1987 p_x_line_tbl(I).line_type_id);
1988
1989
1990 IF nvl(l_sch_level, OE_Schedule_Util.SCH_LEVEL_THREE) =
1991 OE_Schedule_Util.SCH_LEVEL_THREE
1992 OR l_sch_level = OE_Schedule_Util.SCH_LEVEL_FOUR
1993 THEN
1994 IF l_debug_level > 0 THEN
1995 oe_debug_pub.add( 'SCH LEVEL ALLOWS FOR RESERVATION' , 3 ) ;
1996 END IF;
1997
1998 Get_Reservations( p_line_rec => p_x_line_tbl(I)
1999 ,x_reservation_qty => l_reservation_qty
2000 ,x_reservation_qty2 => l_reservation_qty2 -- INVCONV
2001 );
2002
2003 IF l_reservation_qty = 0 THEN
2004 -- no user change
2005 --10207418 : Set reserved_quantity to zero if its don't have any value.
2006 IF p_x_line_tbl(I).reserved_quantity is NULL OR
2007 p_x_line_tbl(I).reserved_quantity = FND_API.G_MISS_NUM
2008 THEN
2009 p_x_line_tbl(I).reserved_quantity := 0;
2010 END IF;
2011
2012 IF p_x_line_tbl(I).reserved_quantity = 0 THEN
2013
2014 IF p_sch_action = OE_Schedule_Util.OESCH_ACT_RESERVE OR
2015 OE_SCHEDULE_UTIL.Within_Rsv_Time_Fence
2016 (p_x_line_tbl(I).schedule_ship_date, p_x_line_tbl(I).org_id) THEN --4689197
2017
2018 IF l_debug_level > 0 THEN
2019 oe_debug_pub.add( 'RESERVE '||P_X_LINE_TBL ( I ) .ORDERED_QUANTITY , 3 ) ;
2020 oe_debug_pub.add( 'RESERVE 2 '||P_X_LINE_TBL ( I ) .ORDERED_QUANTITY2 , 3 ) ; -- INVCONV
2021 END IF;
2022 l_reservation_qty := p_x_line_tbl(I).ordered_quantity;
2023 l_reservation_qty2 := p_x_line_tbl(I).ordered_quantity2; -- INVCONV
2024
2025 END IF;
2026
2027 --Post Pack J
2028 -- If partial reservation falg is Yes and line have some reservation
2029 -- Reserve the remaining quantity.
2030 --ELSIF Oe_Sys_Parameters.Value('PARTIAL_RESERVATION_FLAG') = 'Y'
2031 ELSIF oe_schedule_util.Within_Rsv_Time_Fence(p_x_line_tbl(I).schedule_ship_date, p_x_line_tbl(I).org_id) THEN --4689197
2032 IF nvl(p_x_line_tbl(I).ordered_quantity,0) > nvl(p_x_line_tbl(I).reserved_quantity,0) THEN -- INVCONV
2033 l_reservation_qty := nvl(p_x_line_tbl(I).ordered_quantity,0) - nvl(p_x_line_tbl(I).reserved_quantity,0);
2034 END IF;
2035 IF nvl(p_x_line_tbl(I).ordered_quantity2,0) > nvl(p_x_line_tbl(I).reserved_quantity2,0) THEN -- INVCONV
2036 l_reservation_qty2 := nvl(p_x_line_tbl(I).ordered_quantity2,0) - nvl(p_x_line_tbl(I).reserved_quantity2,0);
2037 END IF;
2038
2039
2040 l_rsv_update := TRUE;
2041 IF l_debug_level > 0 THEN
2042 oe_debug_pub.add( 'RESERVE QUANTITY'||P_X_LINE_TBL(I).RESERVED_QUANTITY , 3 ) ;
2043 oe_debug_pub.add('Qunatity to reserve :'||l_reservation_qty,3);
2044 oe_debug_pub.add( 'RESERVE QUANTITY2'||P_X_LINE_TBL(I).RESERVED_QUANTITY2 , 3 ) ; -- INVCONV
2045 oe_debug_pub.add('Quantity to reserve :'||l_reservation_qty2,3);
2046
2047 END IF;
2048
2049
2050
2051 ELSE
2052 -- you do not want to overwrite previously supplied value,
2053 IF l_debug_level > 0 THEN
2054 oe_debug_pub.add( 'ALREADY RESERVED '||P_X_LINE_TBL ( I ) .RESERVED_QUANTITY , 3 ) ;
2055 END IF;
2056 END IF;
2057
2058 -- Post Pack J
2059 -- Send the full order quantity for reservation if partial reservation flag is 'Yes'.
2060 -- ELSIF l_reservation_qty > 0 commented for bug 16782621
2061 --AND Oe_Sys_Parameters.Value('PARTIAL_RESERVATION_FLAG') = 'Y'
2062 ELSIF ( ( l_reservation_qty > 0 AND Nvl(P_X_LINE_TBL ( I ).fulfillment_base ,'P') ='P')
2063 OR (l_reservation_qty2 > 0 AND Nvl(P_X_LINE_TBL ( I ).fulfillment_base ,'P') ='S')
2064 )--bug 16782621
2065 AND oe_schedule_util.Within_Rsv_Time_Fence(p_x_line_tbl(I).schedule_ship_date, p_x_line_tbl(I).org_id) THEN --4689197
2066 l_reservation_qty := p_x_line_tbl(I).ordered_quantity;
2067 l_reservation_qty2 := p_x_line_tbl(I).ordered_quantity2; -- INVCONV
2068 END IF; -- reservation_aty = 0
2069
2070 ELSE
2071 IF l_debug_level > 0 THEN
2072 oe_debug_pub.add( 'SCH LEVEL DOES NOT ALLOW RESERVATION' , 3 ) ;
2073 END IF;
2074 END IF; -- level check
2075
2076 -- IF l_reservation_qty > 0 THEN --bug 16782621
2077 IF ( ( l_reservation_qty > 0 AND Nvl(P_X_LINE_TBL ( I ).fulfillment_base ,'P') ='P')
2078 OR (l_reservation_qty2 > 0 AND Nvl(P_X_LINE_TBL ( I ).fulfillment_base ,'P') ='S')
2079 )--bug 16782621
2080 THEN
2081 p_x_line_tbl(I).reserved_quantity := l_reservation_qty;
2082 p_x_line_tbl(I).reserved_quantity2 := l_reservation_qty2; -- INVCONV
2083
2084 OE_SCHEDULE_UTIL.Reserve_Line
2085 ( p_line_rec => p_x_line_tbl(I)
2086 ,p_quantity_to_Reserve => p_x_line_tbl(I).reserved_quantity
2087 ,p_quantity2_to_Reserve => p_x_line_tbl(I).reserved_quantity2 -- INVCONV
2088 ,p_rsv_update => l_rsv_update
2089 ,x_return_status => l_return_status);
2090
2091 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2093 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2094 IF l_debug_level > 0 THEN
2095 oe_debug_pub.add( 'RESERVE LINE EXPECTED ERROR , IGNORE' , 1 ) ;
2096 END IF;
2097 END IF;
2098
2099 END IF; -- do reserve
2100
2101 END IF; -- if shippable
2102 I := p_x_line_tbl.NEXT(I);
2103 END LOOP;
2104
2105 Print_Time('leaving Reserve_Group ' ||l_return_status);
2106 EXCEPTION
2107 WHEN OTHERS THEN
2108 IF l_debug_level > 0 THEN
2109 oe_debug_pub.add( 'RESERVE_GROUP ERROR ' || SQLERRM , 1 ) ;
2110 END IF;
2111 RAISE;
2112 END Reserve_Group;
2113
2114
2115 /*---------------------------------------------------------------
2116 PROCEDURE Get_Reservations
2117 to get the reserved quantity, if manually entered on any lines.
2118
2119 we will come here, only if the action is not schedule.
2120 Thus, if user has explicitly give a reserved_quantity,
2121 we will have an entry in the OE_Reservations_Tbl.
2122 If an entry does not exist, the old reserved quantity in
2123 the p_x_line_rec is nulled OUT since we do not want to reserve
2124 a quantity whcih is already reserved.
2125
2126 Note that setting the x_reservation_qty to -1 is done so
2127 that we do not perform any reservations on this line.
2128 This will happen if the line is interfaced to shipping
2129 and
2130 1) ship_from_org is changed on the line
2131 2) manually reduced the reserved qty.
2132 ---------------------------------------------------------------*/
2133 PROCEDURE Get_Reservations
2134 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type
2135 ,x_reservation_qty OUT NOCOPY /* file.sql.39 change */ NUMBER
2136 ,x_reservation_qty2 OUT NOCOPY /* file.sql.39 change */ NUMBER -- INVCONV
2137 )
2138 IS
2139 l_line_id_mod NUMBER; --7827737
2140 --
2141 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2142 --
2143 BEGIN
2144 IF l_debug_level > 0 THEN
2145 oe_debug_pub.add( 'ENTERING GET_RESERVATIONS ' , 3 ) ;
2146 END IF;
2147 l_line_id_mod :=MOD(p_line_rec.line_id,G_BINARY_LIMIT); --7827737
2148
2149 --IF OE_Reservations_Tbl.EXISTS(p_line_rec.line_id)
2150 IF OE_Reservations_Tbl.EXISTS(l_line_id_mod) --7827737
2151 THEN
2152 --x_reservation_qty := OE_Reservations_Tbl(p_line_rec.line_id).qty_to_reserve;
2153 --x_reservation_qty2 := OE_Reservations_Tbl(p_line_rec.line_id).qty2_to_reserve;
2154 x_reservation_qty := OE_Reservations_Tbl(l_line_id_mod).qty_to_reserve;--7827737
2155 x_reservation_qty2 := OE_Reservations_Tbl(l_line_id_mod).qty2_to_reserve;--7827737
2156
2157 -- Added below IF for bug 6335352
2158 IF p_line_rec.ordered_quantity < x_reservation_qty THEN
2159 x_reservation_qty := p_line_rec.ordered_quantity;
2160 END IF;
2161
2162 IF l_debug_level > 0 THEN
2163 oe_debug_pub.add( 'RES VALUE '|| X_RESERVATION_QTY , 3 ) ;
2164 END IF;
2165
2166 /* Commented the below for bug 6335352
2167 IF p_line_rec.shipping_interfaced_flag = 'Y' AND
2168 OE_Reservations_Tbl(p_line_rec.line_id).qty_to_unreserve
2169 is NOT NULL
2170 THEN
2171 x_reservation_qty := -1;
2172 x_reservation_qty2 := -1; -- INVCONV
2173 END IF;
2174 */
2175
2176 --OE_Reservations_Tbl.DELETE(p_line_rec.line_id);
2177 OE_Reservations_Tbl.DELETE(l_line_id_mod); --7827737
2178 ELSE
2179 x_reservation_qty := 0;
2180 END IF; -- if entry exists in res tbl
2181
2182 IF l_debug_level > 0 THEN
2183 oe_debug_pub.add( P_LINE_REC.LINE_ID || ' LEAVING..'|| X_RESERVATION_QTY , 3 ) ;
2184 END IF;
2185
2186 EXCEPTION
2187 WHEN OTHERS THEN
2188 IF l_debug_level > 0 THEN
2189 oe_debug_pub.add( 'ERROR IN GET_RESERVATIONS '|| SQLERRM , 1 ) ;
2190 END IF;
2191 RAISE;
2192 END Get_Reservations;
2193
2194
2195 /*----------------------------------------------------------------
2196 PROCEDURE Handle_Unreserve
2197 Description:
2198
2199 since the query_set_lines give current reserved_qty,
2200 we only need to give the old_ship_from in case
2201 of ship_from_change.
2202
2203 Logic:
2204
2205 if ship_from_changed,
2206 set old ordered_quantity
2207 old_ship_from is already set.
2208 call unscheule_group.
2209
2210 if only res_qty / unreserved qty changed,
2211 set qty_to_res and qty_to_unres and calll res_grp/unres-grp.
2212 then call normal call_mrp.
2213
2214 should the log_attribute_changes be modified to work on
2215 the entire group, no cascade changes is modified.
2216
2217 Post Process parameter has been introduced to take care of
2218 deleting the reservation on old item if item has been changed
2219 on a line as part of item substitution.
2220
2221
2222 Unreserve_Group does not care about the ordered_quantity.
2223 -----------------------------------------------------------------*/
2224 PROCEDURE Handle_Unreserve
2225 ( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
2226 ,p_old_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
2227 ,p_post_process IN VARCHAR2 DEFAULT FND_API.G_FALSE
2228 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
2229 IS
2230 l_qty NUMBER;
2231 l_qty2 NUMBER; -- INVCONV
2232 I NUMBER;
2233 l_line_id_mod NUMBER; --7827737
2234 --
2235 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2236 --
2237 BEGIN
2238
2239 Print_Time('entering Handle_Unreserve');
2240
2241 IF p_post_process = FND_API.G_TRUE THEN
2242
2243 IF l_debug_level > 0 THEN
2244 oe_debug_pub.add( 'ENTERING POST PROCESS' , 1 ) ;
2245 END IF;
2246 I := p_x_line_tbl.FIRST;
2247 WHILE I is NOT NULL
2248 LOOP
2249
2250 IF nvl(p_x_line_tbl(I).shipping_interfaced_flag,'N') = 'N'
2251 AND NOT OE_GLOBALS.Equal(p_x_line_tbl(I).inventory_item_id,
2252 p_old_line_tbl(I).inventory_item_id)
2253 AND p_old_line_tbl(I).reserved_quantity > 0 THEN
2254
2255 OE_SCHEDULE_UTIL.Unreserve_Line
2256 (p_line_rec => p_old_line_tbl(I),
2257 p_quantity_to_unreserve => p_old_line_tbl(I).reserved_quantity,
2258 p_quantity2_to_unreserve => p_old_line_tbl(I).reserved_quantity2, -- INVCONV
2259 x_return_status => x_return_status);
2260
2261 END IF;
2262
2263 I := p_x_line_tbl.NEXT(I);
2264 END LOOP;
2265
2266 RETURN;
2267 END IF; -- Post Process.
2268
2269 IF (p_x_line_tbl(1).ship_model_complete_flag = 'Y' OR
2270 p_x_line_tbl(1).ship_set_id is not NULL OR
2271 p_x_line_tbl(1).arrival_set_id is not NULL) AND
2272 p_x_line_tbl(1).ship_from_org_id <> p_old_line_tbl(1).ship_from_org_id
2273 THEN
2274 IF l_debug_level > 0 THEN
2275 oe_debug_pub.add( 'UNRESERVING...' , 3 ) ;
2276 END IF;
2277 Unreserve_group
2278 ( p_line_tbl => p_old_line_tbl
2279 ,p_sch_action => OE_Schedule_Util.OESCH_ACT_RESCHEDULE);
2280
2281 RETURN;
2282 END IF;
2283
2284
2285 --------- handle the ordered qty etc. change -----------------
2286 IF l_debug_level > 0 THEN
2287 oe_debug_pub.add( 'ORDERED QTY ETC. CHANGES ' , 1 ) ;
2288 END IF;
2289
2290 I := p_x_line_tbl.FIRST;
2291 WHILE I is NOT NULL
2292 LOOP
2293
2294 l_qty := 0;
2295 l_line_id_mod := MOD(p_x_line_tbl(I).line_id,G_BINARY_LIMIT); --7827737
2296
2297 --IF OE_Reservations_Tbl.EXISTS(p_x_line_tbl(I).line_id) AND
2298 -- OE_Reservations_Tbl(p_x_line_tbl(I).line_id).qty_to_unreserve
2299 -- is not NULL THEN
2300 IF OE_Reservations_Tbl.EXISTS(l_line_id_mod) AND --7827737
2301 OE_Reservations_Tbl(l_line_id_mod).qty_to_unreserve
2302 is not NULL THEN
2303
2304 l_qty := OE_Reservations_Tbl
2305 (l_line_id_mod).qty_to_unreserve; --7827737
2306 --(p_x_line_tbl(I).line_id).qty_to_unreserve;
2307 l_qty2 := OE_Reservations_Tbl -- INVCONV
2308 (l_line_id_mod).qty2_to_unreserve; --7827737
2309 --(p_x_line_tbl(I).line_id).qty2_to_unreserve;
2310
2311
2312 --OE_Reservations_Tbl.DELETE(p_x_line_tbl(I).line_id);
2313 -- ELSIF p_x_line_tbl(I).ordered_quantity<p_x_line_tbl(I).reserved_quantity -- bug 16782621
2314 ELSIF ( ( p_x_line_tbl(I).ordered_quantity < p_x_line_tbl(I).reserved_quantity
2315 AND Nvl(P_X_LINE_TBL ( I ).fulfillment_base ,'P') ='P')
2316 OR( p_x_line_tbl(I).ordered_quantity2 < p_x_line_tbl(I).reserved_quantity2
2317 AND Nvl(P_X_LINE_TBL ( I ).fulfillment_base ,'P') ='S')
2318 )-- bug 16782621
2319 THEN
2320 IF l_debug_level > 0 THEN
2321 oe_debug_pub.add( 'ELSIF CHANGE'|| P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
2322 END IF;
2323
2324 l_qty := p_x_line_tbl(I).reserved_quantity -
2325 p_x_line_tbl(I).ordered_quantity;
2326 l_qty2 := p_x_line_tbl(I).reserved_quantity2 - -- INVCONV
2327 p_x_line_tbl(I).ordered_quantity2;
2328
2329 END IF;
2330
2331 IF l_debug_level > 0 THEN
2332 oe_debug_pub.add( L_QTY||' UNRESERVE '||P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
2333 oe_debug_pub.add( L_QTY2||' UNRESERVE 2'||P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
2334 END IF;
2335
2336 -- IF l_qty > 0 THEN -- bug 16782621
2337 IF ( l_qty > 0 AND Nvl(P_X_LINE_TBL ( I ).fulfillment_base ,'P') ='P')
2338 OR( l_qty2 >0 AND Nvl(P_X_LINE_TBL ( I ).fulfillment_base ,'P') ='S')
2339 THEN -- bug 16782621
2340 OE_SCHEDULE_UTIL.Unreserve_Line
2341 ( p_line_rec => p_x_line_tbl(I)
2342 ,p_old_ship_from_org_id => p_old_line_tbl(I).ship_from_org_id --6628134
2343 ,p_quantity_to_unreserve => l_qty
2344 ,p_quantity2_to_unreserve => l_qty2 -- INVCONV
2345 ,x_return_status => x_return_status);
2346 END IF;
2347
2348 I := p_x_line_tbl.NEXT(I);
2349 END LOOP;
2350
2351 Print_Time('leaving Handle_Unreserve');
2352 EXCEPTION
2353 WHEN OTHERS THEN
2354 IF l_debug_level > 0 THEN
2355 oe_debug_pub.add( 'HANDLE_UNRESERVE ERROR' || SQLERRM , 1 ) ;
2356 END IF;
2357 RAISE;
2358
2359 END Handle_Unreserve;
2360
2361
2362 /*-----------------------------------------------------------
2363 PROCEDURE Validate_And_Assign_Sch_Params
2364
2365 Do I need to check caller to not do the ship_set assignment
2366 for nonsmc? what if I assing line's own line_id.
2367 Changes have been made as part of override atp project to
2368 cascade override flag from model/class and kits to its included
2369 items. Changes are also made to cascade override flag on ato model.
2370 ------------------------------------------------------------*/
2371 PROCEDURE Validate_And_Assign_Sch_Params
2372 ( p_request_rec IN OE_Order_Pub.Request_Rec_Type
2373 ,p_x_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
2374 ,p_x_old_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type)
2375 IS
2376 l_return_status VARCHAR2(1);
2377 --
2378 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2379 --
2380 BEGIN
2381 IF l_debug_level > 0 THEN
2382 oe_debug_pub.add( '--------ENTERING VALIDATE_AND_ASSIGN' , 1 ) ;
2383 END IF;
2384
2385 /* 3533565 Validation will be done after cascading
2386 OE_Schedule_Util.Validate_Line
2387 ( p_line_rec => p_x_line_rec
2388 ,p_old_line_rec => p_x_old_line_rec
2389 ,p_sch_action => p_request_rec.param1
2390 ,x_return_status => l_return_status);
2391
2392
2393 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2395 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2396 RAISE FND_API.G_EXC_ERROR;
2397 END IF;
2398 */
2399 IF l_debug_level > 0 THEN
2400 oe_debug_pub.add( '-------ENTITY ' || P_REQUEST_REC.ENTITY_ID , 4 ) ;
2401 END IF;
2402 IF l_debug_level > 0 THEN
2403 oe_debug_pub.add( '-------LINE ' || P_X_LINE_REC.LINE_ID , 4 ) ;
2404 END IF;
2405 IF l_debug_level > 0 THEN
2406 oe_debug_pub.add( '-------ACTION ' || P_REQUEST_REC.PARAM1 , 4 ) ;
2407 END IF;
2408
2409 p_x_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
2410 p_x_line_rec.change_reason := 'SYSTEM';
2411 p_x_line_rec.change_comments := 'Scheduling Action';
2412 p_x_line_rec.schedule_action_code := p_request_rec.param1;
2413 -- 13250883 : Cascade warehouse only if the model is of type SMC
2414 -- 13969025 : Cascade of warehous will be allowed only if the call is from
2415 -- Sch. Order Concurrent program.
2416 IF p_x_line_rec.ship_model_complete_flag = 'Y' OR
2417 (p_x_line_rec.ship_model_complete_flag = 'N' AND
2418 OE_SCH_CONC_REQUESTS.g_conc_program = 'Y') THEN
2419 p_x_line_rec.ship_from_org_id := p_request_rec.param3;
2420 END IF;
2421 p_x_line_rec.ship_to_org_id := p_request_rec.param4;
2422
2423 --IF condition added for Bug 3524944
2424 IF p_x_line_rec.open_flag = 'Y' THEN
2425 p_x_line_rec.shipping_method_code := p_request_rec.param5;
2426 END IF;
2427
2428 p_x_line_rec.demand_class_code := p_request_rec.param6;
2429 p_x_line_rec.request_date := p_request_rec.date_param1;
2430 p_x_line_rec.schedule_ship_date := p_request_rec.date_param2;
2431 p_x_line_rec.schedule_arrival_date := p_request_rec.date_param3;
2432
2433 -- 3533565
2434 OE_Schedule_Util.Validate_Line
2435 ( p_line_rec => p_x_line_rec
2436 ,p_old_line_rec => p_x_old_line_rec
2437 ,p_sch_action => p_request_rec.param1
2438 ,x_return_status => l_return_status);
2439
2440
2441 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2443 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2444 RAISE FND_API.G_EXC_ERROR;
2445 END IF;
2446
2447 IF p_request_rec.request_type <> OE_GLOBALS.G_SCHEDULE_NONSMC
2448 AND NOT (p_request_rec.request_type = OE_GLOBALS.G_SCHEDULE_ATO
2449 AND OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
2450 AND MSC_ATP_GLOBAL.GET_APS_VERSION = 10 )
2451
2452 THEN
2453 p_x_line_rec.ship_set := p_request_rec.entity_id;
2454 END IF;
2455
2456 -- 4052648: Assign old values for ato model for unschedule action
2457 IF p_request_rec.param1 = OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE
2458 OR (p_request_rec.param1 = OE_SCHEDULE_UTIL.OESCH_ACT_UNSCHEDULE
2459 AND p_x_line_rec.ato_line_id IS NOT NULL)
2460 -- bug 2427280 AND p_x_old_line_rec.schedule_status_code is NOT NULL
2461 THEN
2462 IF l_debug_level > 0 THEN
2463 oe_debug_pub.add( 'ASSIGN OLD VALUES '|| P_REQUEST_REC.PARAM7 , 3 ) ;
2464 END IF;
2465 p_x_old_line_rec.ship_from_org_id := p_request_rec.param7;
2466 p_x_old_line_rec.demand_class_code := p_request_rec.param8;
2467 p_x_old_line_rec.request_date := p_request_rec.date_param4;
2468 p_x_old_line_rec.schedule_ship_date := p_request_rec.date_param5;
2469 p_x_old_line_rec.schedule_arrival_date := p_request_rec.date_param6;
2470 --4052648
2471 p_x_old_line_rec.ship_to_org_id := p_request_rec.param14;
2472 p_x_old_line_rec.shipping_method_code := p_request_rec.param15;
2473 p_x_old_line_rec.planning_priority := p_request_rec.param16;
2474 p_x_old_line_rec.delivery_lead_time := p_request_rec.param17;
2475 END IF;
2476
2477 -- BUG 1282873
2478 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
2479
2480 IF l_debug_level > 0 THEN
2481 oe_debug_pub.add( 'ASSIGN OVERRIDE FLAG' , 3 ) ;
2482 END IF;
2483 IF (p_x_line_rec.ato_line_id is not null AND
2484 NOT (p_x_line_rec.ato_line_id = p_x_line_rec.line_id AND
2485 p_x_line_rec.item_type_code
2486 IN (OE_GLOBALS.G_ITEM_OPTION,OE_GLOBALS.G_ITEM_INCLUDED)))
2487 THEN --9775352
2488
2489 IF p_x_line_rec.ship_model_complete_flag = 'Y'
2490 AND nvl(p_x_line_rec.override_atp_date_code,'N') = 'N' THEN
2491
2492 BEGIN
2493
2494
2495 Select override_atp_date_code
2496 Into p_x_line_rec.override_atp_date_code
2497 From oe_order_lines_all
2498 Where header_id = p_x_line_rec.header_id
2499 And ato_line_id = p_x_line_rec.ato_line_id
2500 And override_atp_date_code = 'Y'
2501 AND rownum < 2;
2502
2503 EXCEPTION
2504 WHEN OTHERS THEN
2505 Null;
2506
2507 END;
2508 IF l_debug_level > 0 THEN
2509 oe_debug_pub.add( 'OVERRIDE_ATP_DATE_CODE :' || P_X_LINE_REC.OVERRIDE_ATP_DATE_CODE || P_X_LINE_REC.LINE_ID , 3 ) ;
2510 END IF;
2511
2512 ELSIF NVL(p_request_rec.param11,'N') = 'Y' THEN
2513
2514 p_x_line_rec.override_atp_date_code := p_request_rec.param11;
2515
2516 END IF;
2517
2518 ELSIF p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
2519
2520
2521 -- 2722692
2522 -- IF p_x_line_rec.ship_model_complete_flag = 'Y'
2523 IF nvl(p_x_line_rec.override_atp_date_code,'N') = 'N' THEN
2524
2525 BEGIN
2526
2527
2528 Select override_atp_date_code
2529 Into p_x_line_rec.override_atp_date_code
2530 From oe_order_lines_all
2531 Where header_id = p_x_line_rec.header_id
2532 And line_id = p_x_line_rec.link_to_line_id;
2533
2534 EXCEPTION
2535 WHEN OTHERS THEN
2536 Null;
2537
2538 END;
2539
2540 IF l_debug_level > 0 THEN
2541 oe_debug_pub.add( '2 OVERRIDE_ATP_DATE_CODE :' || P_X_LINE_REC.OVERRIDE_ATP_DATE_CODE , 3 ) ;
2542 END IF;
2543
2544 ELSIF p_x_line_rec.link_to_line_id = p_request_rec.entity_id
2545 AND nvl(p_request_rec.param11,'N') = 'Y' THEN
2546
2547 p_x_line_rec.override_atp_date_code := p_request_rec.param11;
2548
2549 END IF;
2550 END IF;
2551
2552 END IF; -- Check for Pack I
2553 -- END 1282873
2554
2555 IF l_debug_level > 0 THEN
2556 oe_debug_pub.add( 'LEAVING VALIDATE_AND_ASSIGN_SCH_PARAMS' , 3 ) ;
2557 END IF;
2558
2559 EXCEPTION
2560 WHEN OTHERS THEN
2561 IF l_debug_level > 0 THEN
2562 oe_debug_pub.add( 'ERROR VALIDATE_AND_ASSIGN_SCH_PARAMS '|| SQLERRM , 1 ) ;
2563 END IF;
2564 RAISE;
2565 END Validate_And_Assign_Sch_Params;
2566
2567
2568 /*----------------------------------------------------------
2569 PROCEDURE Save_Sch_Attributes
2570 Call lines only for those lines where there is
2571 a change.
2572
2573 many of the lines could be sent to mrp because they are part of
2574 a group, but not all of them will have a change in sch attribs.
2575 ex: line added to a scheduled smc, only that line should
2576 be passed to process_order if all other lines have same
2577 dates and sch attribs after call to mrp.
2578
2579 call security if the line has change.
2580 call defaulting to see if we need to call process_order.
2581 if we do not need, set the operation to NONE and do a direct
2582 update.
2583 -----------------------------------------------------------*/
2584 PROCEDURE Save_Sch_Attributes
2585 ( p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
2586 ,p_old_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
2587 ,p_sch_action IN VARCHAR2
2588 ,p_caller IN VARCHAR2
2589 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
2590 IS
2591 I NUMBER;
2592 l_line_id VARCHAR2(1);
2593 l_call_po VARCHAR2(1) := 'N';
2594 l_direct_update VARCHAR2(1) := 'N';
2595 l_process_requests BOOLEAN;
2596 l_redo_security_check VARCHAR2(1) := 'N';
2597 l_control_rec OE_GLOBALS.control_rec_type;
2598 l_orig_old_line_rec OE_Order_PUB.Line_Rec_Type; --3144917
2599
2600 l_po_NeedByDate_Update VARCHAR2(10); -- Added for IR ISO CMS project
2601 l_return_status VARCHAR2(1); -- Added for IR ISO CMS project
2602
2603 --
2604 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2605 --
2606 BEGIN
2607 IF l_debug_level > 0 THEN
2608 oe_debug_pub.add( 'ENTERING SAVE_SCH '|| P_X_LINE_TBL.COUNT , 1 ) ;
2609 END IF;
2610
2611 x_return_status := FND_API.G_RET_STS_SUCCESS;
2612
2613 /*Initiliazed the x_return_status for bug 7555835. Since x_return_status is not initialized
2614 in the begining of the API,In certain flows, like when only schedule ship date is changed,
2615 it is not getting initialized at all. Due to this, this API is returning return status as null.
2616 So the caller is erroring out, as ther processing is checked based on the return status. */
2617
2618 I := p_x_line_tbl.FIRST;
2619 WHILE I is NOT NULL
2620 LOOP
2621
2622 IF p_x_line_tbl(I).operation = OE_GLOBALS.G_OPR_NONE THEN
2623 IF l_debug_level > 0 THEN
2624 oe_debug_pub.add( 'OPERATION IS NONE '|| P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
2625 END IF;
2626 goto end_of_loop;
2627 END IF;
2628
2629 p_x_line_tbl(I).ship_set := null;
2630 p_old_line_tbl(I).ship_set := null; -- 3878491
2631
2632 --Added the below IF condition for bug 4587506
2633 --Added IS_MASS_CHANGE condition for bug 4911340
2634 IF NOT OE_GLOBALS.G_UI_FLAG AND OE_MASS_CHANGE_PVT.IS_MASS_CHANGE = 'F' THEN
2635 p_x_line_tbl(I).change_reason := 'SYSTEM';
2636 END IF;
2637
2638
2639 IF l_debug_level > 0 THEN
2640 oe_debug_pub.add( 'GET THE OLD DATA' , 3 ) ;
2641 END IF;
2642
2643 l_orig_old_line_rec := p_old_line_tbl(I) ; --3144917
2644
2645 /* Commented the following sql as in case of group
2646 scheduling dates would have already been updated on line
2647 but reservations need to be called. Bug 2801597 */
2648
2649 /*Uncommented the sql for Bug 3144917 */
2650
2651 -- 4052648 : Do not query for action - UNSCHEDULE
2652 IF p_sch_action <> OE_SCHEDULE_UTIL.OESCH_ACT_UNSCHEDULE THEN
2653
2654 SELECT request_date, schedule_ship_date,
2655 schedule_arrival_date,
2656 ship_from_org_id, ship_to_org_id,
2657 shipping_method_code, demand_class_code,
2658 planning_priority, delivery_lead_time
2659 INTO p_old_line_tbl(I).request_date,
2660 p_old_line_tbl(I).schedule_ship_date,
2661 p_old_line_tbl(I).schedule_arrival_date,
2662 p_old_line_tbl(I).ship_from_org_id,
2663 p_old_line_tbl(I).ship_to_org_id,
2664 p_old_line_tbl(I).shipping_method_code,
2665 p_old_line_tbl(I).demand_class_code,
2666 p_old_line_tbl(I).planning_priority,
2667 p_old_line_tbl(I).delivery_lead_time
2668 FROM oe_order_lines
2669 WHERE line_id = p_x_line_tbl(I).line_id;
2670
2671 END IF;-- 4052648
2672
2673 IF p_x_line_tbl(I).open_flag = 'N' THEN
2674
2675 IF l_debug_level > 0 THEN
2676 oe_debug_pub.add( 'LINE IS CLOSED '||P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
2677 END IF;
2678 p_x_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
2679 IF l_debug_level > 0 THEN
2680 oe_debug_pub.add( P_X_LINE_TBL ( I ) .SHIPPING_METHOD_CODE , 3 ) ;
2681 END IF;
2682 IF l_debug_level > 0 THEN
2683 oe_debug_pub.add( P_OLD_LINE_TBL ( I ) .SHIPPING_METHOD_CODE , 3 ) ;
2684 END IF;
2685 goto direct_update;
2686 END IF;
2687
2688
2689 IF l_debug_level > 0 THEN
2690 oe_debug_pub.add( P_X_LINE_TBL ( I ) .REQUEST_DATE , 3 ) ;
2691 END IF;
2692 IF l_debug_level > 0 THEN
2693 oe_debug_pub.add( P_OLD_LINE_TBL ( I ) .REQUEST_DATE , 3 ) ;
2694 END IF;
2695 IF l_debug_level > 0 THEN
2696 oe_debug_pub.add( P_X_LINE_TBL ( I ) .SCHEDULE_SHIP_DATE , 3 ) ;
2697 END IF;
2698 IF l_debug_level > 0 THEN
2699 oe_debug_pub.add( P_OLD_LINE_TBL ( I ) .SCHEDULE_SHIP_DATE , 3 ) ;
2700 END IF;
2701 IF l_debug_level > 0 THEN
2702 oe_debug_pub.add( P_X_LINE_TBL ( I ) .SCHEDULE_ARRIVAL_DATE , 3 ) ;
2703 END IF;
2704 IF l_debug_level > 0 THEN
2705 oe_debug_pub.add( P_OLD_LINE_TBL ( I ) .SCHEDULE_ARRIVAL_DATE , 3 ) ;
2706 END IF;
2707 IF l_debug_level > 0 THEN
2708 oe_debug_pub.add( P_X_LINE_TBL ( I ) .SHIP_FROM_ORG_ID , 3 ) ;
2709 END IF;
2710 IF l_debug_level > 0 THEN
2711 oe_debug_pub.add( P_OLD_LINE_TBL ( I ) .SHIP_FROM_ORG_ID , 3 ) ;
2712 END IF;
2713 IF l_debug_level > 0 THEN
2714 oe_debug_pub.add( P_X_LINE_TBL ( I ) .SHIP_TO_ORG_ID , 3 ) ;
2715 END IF;
2716 IF l_debug_level > 0 THEN
2717 oe_debug_pub.add( P_OLD_LINE_TBL ( I ) .SHIP_TO_ORG_ID , 3 ) ;
2718 END IF;
2719 IF l_debug_level > 0 THEN
2720 oe_debug_pub.add( P_X_LINE_TBL ( I ) .SHIPPING_METHOD_CODE , 3 ) ;
2721 END IF;
2722 IF l_debug_level > 0 THEN
2723 oe_debug_pub.add( P_OLD_LINE_TBL ( I ) .SHIPPING_METHOD_CODE , 3 ) ;
2724 END IF;
2725 IF l_debug_level > 0 THEN
2726 oe_debug_pub.add( P_X_LINE_TBL ( I ) .DEMAND_CLASS_CODE , 3 ) ;
2727 END IF;
2728 IF l_debug_level > 0 THEN
2729 oe_debug_pub.add( P_OLD_LINE_TBL ( I ) .DEMAND_CLASS_CODE , 3 ) ;
2730 END IF;
2731 IF l_debug_level > 0 THEN
2732 oe_debug_pub.add(P_X_LINE_TBL( I ).Earliest_Ship_Date , 3 ) ;
2733 END IF;
2734 IF l_debug_level > 0 THEN
2735 oe_debug_pub.add(P_X_LINE_TBL( I ).Firm_Demand_Flag , 3 ) ;
2736 END IF;
2737
2738
2739 IF p_sch_action = OE_Schedule_Util.OESCH_ACT_RESCHEDULE THEN
2740 IF NOT OE_GLOBALS.Equal(p_x_line_tbl(I).request_date,
2741 p_old_line_tbl(I).request_date) OR
2742
2743 NOT OE_GLOBALS.Equal(p_x_line_tbl(I).schedule_ship_date,
2744 p_old_line_tbl(I).schedule_ship_date) OR
2745
2746 NOT OE_GLOBALS.Equal(p_x_line_tbl(I).schedule_arrival_date,
2747 p_old_line_tbl(I).schedule_arrival_date) OR
2748
2749 NOT OE_GLOBALS.Equal(p_x_line_tbl(I).ship_from_org_id,
2750 p_old_line_tbl(I).ship_from_org_id) OR
2751
2752 NOT OE_GLOBALS.Equal(p_x_line_tbl(I).ship_to_org_id,
2753 p_old_line_tbl(I).ship_to_org_id) OR
2754
2755 NOT OE_GLOBALS.Equal(p_x_line_tbl(I).shipping_method_code,
2756 p_old_line_tbl(I).shipping_method_code) OR
2757
2758 NOT OE_GLOBALS.Equal(p_x_line_tbl(I).demand_class_code,
2759 p_old_line_tbl(I).demand_class_code) OR
2760
2761 NOT OE_GLOBALS.Equal(p_x_line_tbl(I).planning_priority,
2762 p_old_line_tbl(I).planning_priority) OR
2763
2764 NOT OE_GLOBALS.Equal(p_x_line_tbl(I).delivery_lead_time,
2765 p_old_line_tbl(I).delivery_lead_time) OR
2766
2767 NOT OE_GLOBALS.Equal(p_x_line_tbl(I).inventory_item_id,
2768 p_old_line_tbl(I).inventory_item_id)
2769 THEN
2770 IF l_debug_level > 0 THEN
2771 oe_debug_pub.add( 'THERE IS A CHANGE '|| P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
2772 END IF;
2773 ELSE
2774 IF l_debug_level > 0 THEN
2775 oe_debug_pub.add( 'NO CHANGE '|| P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
2776 END IF;
2777 p_x_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
2778 goto direct_update;
2779 END IF;
2780 END IF; -- end if reschedule
2781
2782
2783 ------------- do security and defaulting -------------
2784
2785 Call_Security_And_Defaulting
2786 (p_x_line_rec => p_x_line_tbl(I)
2787 ,p_old_line_rec => p_old_line_tbl(I)
2788 ,x_direct_update => l_direct_update);
2789
2790 IF l_debug_level > 0 THEN
2791 oe_debug_pub.add( 'operation action ' || oe_line_security.g_operation_action , 1 ) ;
2792 END IF;
2793
2794 IF oe_line_security.g_operation_action is not null THEN
2795 l_call_po := 'Y';
2796 l_direct_update := 'N';
2797 l_redo_security_check := 'Y';
2798 END IF;
2799
2800 IF l_direct_update = 'Y' THEN
2801 p_x_line_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
2802 END IF;
2803
2804 <<direct_update>>
2805 IF p_x_line_tbl(I).operation = OE_GLOBALS.G_OPR_NONE THEN
2806 Handle_Direct_Update
2807 ( p_x_line_rec => p_x_line_tbl(I)
2808 ,p_old_line_rec => p_old_line_tbl(I)
2809 ,p_caller => p_caller);
2810
2811 /* Start - Bug 12613382 */
2812 -- Call PRN explicitly to refresh global tables with correct picture
2813 OE_Order_PVT.Process_Requests_And_Notify
2814 ( p_process_requests => FALSE
2815 , p_notify => FALSE
2816 , p_line_tbl => p_x_line_tbl
2817 , p_old_line_tbl => p_old_line_tbl
2818 , x_return_status => x_return_status);
2819
2820 IF l_debug_level > 0 THEN
2821 oe_debug_pub.add( 'SCH DU : AFTER CALLING PROCESS REQUEST AND NOTIFY', 1 ) ;
2822 oe_debug_pub.add( 'DU X_RETURN_STATUS IS ' || X_RETURN_STATUS , 1 ) ;
2823 END IF;
2824
2825 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2826 IF l_debug_level > 0 THEN
2827 oe_debug_pub.add( 'RR DU: UNEXP ERRORED OUT' , 1 ) ;
2828 END IF;
2829 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2830 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2831 IF l_debug_level > 0 THEN
2832 oe_debug_pub.add( 'RR DU: ERRORED OUT' , 1 ) ;
2833 END IF;
2834 RAISE FND_API.G_EXC_ERROR;
2835 END IF;
2836 /* End - Bug 12613382 */
2837
2838 IF p_x_line_tbl(I).top_model_line_id = p_x_line_tbl(I).line_id
2839 THEN
2840 IF l_debug_level > 0 THEN
2841 oe_debug_pub.add( 'CLEAR MODEL CACHE: '||P_X_LINE_TBL ( I ) .LINE_ID , 1 ) ;
2842 END IF;
2843 OE_Order_Cache.Clear_Top_Model_Line
2844 (p_key => p_x_line_tbl(I).line_id);
2845 END IF;
2846 ELSE
2847 l_call_po := 'Y';
2848 END IF;
2849
2850
2851 IF p_sch_action = OE_Schedule_Util.OESCH_ACT_RESCHEDULE THEN
2852
2853 IF l_debug_level > 0 THEN
2854 oe_debug_pub.add( 'MAY UPDATE RESERN '|| P_X_LINE_TBL ( I ) .LINE_ID , 3 ) ;
2855 END IF;
2856 Update_Reservation
2857 ( p_line_rec => p_x_line_tbl(I)
2858 ,p_old_line_rec => l_orig_old_line_rec --3144917
2859 ,x_return_status => x_return_status);
2860
2861 END IF;
2862
2863 <<end_of_loop>>
2864
2865 /* Loop Back*/
2866 IF nvl(p_x_line_tbl(I).open_flag,'Y') = 'Y' AND
2867 p_x_line_tbl(I).source_document_type_id = 10 AND
2868 NOT OE_GLOBALS.EQUAL(p_x_line_tbl(I).schedule_arrival_date,
2869 p_old_line_tbl(I).schedule_arrival_date)
2870
2871 THEN
2872 IF l_debug_level > 0 THEN
2873 oe_debug_pub.add( 'PASSING SCHEDULE_ARRIVAL_DATE TO PO ' , 3 ) ;
2874 END IF;
2875
2876 -- Bug ##7576948: For IR ISO CMS Project
2877 -- The below code is commented for IR ISO project because we no more
2878 -- required to call PO_Supply.PO_Req_Supply API to just update the
2879 -- MTL_Supply record for the corresponding internal requisition line
2880 -- We now be calling PO_RCO_Validation_GRP.Update_ReqChange_from_SO
2881 -- Purchasing product new API to update both the internal requisition
2882 -- line and the MTL_Supply. This will be achieved via the logging of
2883 -- a new delayed request of type OE_Globals.G_UPDATE_REQUISITION, which
2884 -- is added as part of this project
2885 --
2886 /*
2887 OE_SCHEDULE_UTIL.Update_PO(p_x_line_tbl(I).schedule_arrival_date,
2888 p_x_line_tbl(I).source_document_id,
2889 p_x_line_tbl(I).source_document_line_id);
2890 */
2891 --
2892 -- This delayed request will be logged only if global OE_Internal_Requisi
2893 -- tion_Pvt.G_Update_ISO_From_Req set to FALSE. If this global is TRUE
2894 -- then it means, the change requests for quantity/date or cancellation
2895 -- request is initiated by internal requisition user, in which case, it is
2896 -- not required to log the delayed request for updating the change to the
2897 -- requesting organization. System will also check that global OE_SALES_CAN
2898 -- _UTIL.G_IR_ISO_HDR_CANCEL, and will log a delayed request only if it is
2899 -- FALSE. If this global is TRUE then signifies that it is a case of full
2900 -- internal sales order header cancellation. Thus, in the event of full
2901 -- order cancellation, we only need to inform Purchasing about the
2902 -- cancellation. There is no need to provide specific line level information.
2903 -- Additionally, while logging a delayed request specific to Schedule Arrival
2904 -- Date change, system will ensure that it should be allowed via Purchasing
2905 -- profile 'POR: Sync Up Need By date on IR with OM'.
2906 --
2907 -- Moreover, it should be noted that if the current procedure is triggered
2908 -- for the changes initiated from Planning Workbench or DRP in ASCP, then
2909 -- the two globals G_Update_ISO_From_Req and G_IR_ISO_HDR_CANCEL should be
2910 -- FALSE
2911 --
2912 -- While logging the delayed request, we will log it for Order Header or
2913 -- Order Line entity, while Entity id will be the Header_id or Line_id
2914 -- respectively. In addition to this, we will even pass Unique_Params value
2915 -- to make this request very specific to Requisition Header or Requisition
2916 -- Line.
2917 --
2918 -- Please refer to following delayed request params with their meaning
2919 -- useful while logging the delayed request -
2920 --
2921 -- P_entity_code Entity for which delayed request has to be logged.
2922 -- In this project it can be OE_Globals.G_Entity_Line
2923 -- or OE_Globals.G_Entity_Header
2924 -- P_entity_id Primary key of the entity record. In this project,
2925 -- it can be Order Line_id or Header_id
2926 -- P_requesting_entity_code Which entity has requested this delayed request to
2927 -- be logged! In this project it will be OE_Globals.
2928 -- G_Entity_Line or OE_Globals.G_Entity_Header
2929 -- P_requesting_entity_id Primary key of the requesting entity. In this
2930 -- project, it is Line_id or Header_id
2931 -- P_request_type Indicates which business logic (or which procedure)
2932 -- should be executed. In this project, it is OE_Global
2933 -- s.G_UPDATE_REQUISITION
2934 -- P_request_unique_key1 Additional argument in form of parameters.
2935 -- In this project, it will denote the Sales Order
2936 -- Header id
2937 -- P_request_unique_key2 Additional argument in form of parameters.
2938 -- In this project, it will denote the Requisition
2939 -- Header id
2940 -- P_request_unique_key3 Additional argument in form of parameters. In
2941 -- this project, it will denote the Requistion Line
2942 -- id
2943 -- P_param1 Additional argument in form of parameters. In this project, it
2944 -- will denote net change in order quantity with respective single
2945 -- requisition line. If it is greater than 0 then it is an increment
2946 -- in the quantity, while if it is less than 0 then it is a decrement
2947 -- in the ordered quantity. If it is 0 then it indicates there is no
2948 -- change in ordered quantity value
2949 -- P_param2 Additional argument in form of parameters. In this project, it
2950 -- will denote whether internal sales order is cancelled or not. If
2951 -- it is cancelled then respective Purchasing api will be called to
2952 -- trigger the requisition header cancellation. It accepts a value of
2953 -- Y indicating requisition header has to be cancelled.
2954 -- P_param3 Additional argument in form of parameters. In this project, it
2955 -- will denote the number of sales order lines cancelled while order
2956 -- header is (Full/Partial) cancelled.
2957 -- p_date_param1 Additional date argument in form of parameters. In this
2958 -- project, it will denote the change in Schedule Ship/Arrival Date
2959 -- with to respect to single requisition line.
2960 -- P_Long_param1 Additional argument in form of parameters. In this project,
2961 -- it will store all the sales order line_ids, which are getting
2962 -- cancelled while order header gets cancelled (Full/Partial).
2963 -- These Line_ids will be separated by a delimiter comma ','
2964 --
2965 -- For details on IR ISO CMS project, please refer to FOL >
2966 -- OM Development > OM GM > 12.1.1 > TDD > IR_ISO_CMS_TDD.doc
2967 --
2968
2969 l_po_NeedByDate_Update := NVL(FND_PROFILE.VALUE('POR_SYNC_NEEDBYDATE_OM'),'NO');
2970
2971 IF l_debug_level > 0 THEN
2972 oe_debug_pub.add(' Need By Date update is allowed ? '||l_po_NeedByDate_Update);
2973 END IF;
2974
2975 IF NOT OE_Internal_Requisition_Pvt.G_Update_ISO_From_Req
2976 AND NOT OE_SALES_CAN_UTIL.G_IR_ISO_HDR_CANCEL THEN -- AND
2977 -- l_po_NeedByDate_Update = 'YES' THEN
2978 IF l_po_NeedByDate_Update = 'YES' THEN -- IR ISO Tracking bug 7667702
2979
2980 -- Log a delayed request to update the change in Schedule Arrival Date to
2981 -- Requisition Line. This request will be logged only if the change is
2982 -- not initiated from Requesting Organization, and it is not a case of
2983 -- Internal Sales Order Full Cancellation. It will even not be logged
2984 -- Purchasing profile option does not allow update of Need By Date when
2985 -- Schedule Ship Date changes on internal sales order line
2986
2987 OE_delayed_requests_Pvt.log_request
2988 ( p_entity_code => OE_GLOBALS.G_ENTITY_LINE
2989 , p_entity_id => p_x_line_tbl(I).line_id
2990 , p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE
2991 , p_requesting_entity_id => p_x_line_tbl(I).line_id
2992 , p_request_unique_key1 => p_x_line_tbl(I).header_id -- Order Hdr_id
2993 , p_request_unique_key2 => p_x_line_tbl(I).source_document_id -- Req Hdr_id
2994 , p_request_unique_key3 => p_x_line_tbl(I).source_document_line_id -- Req Line_id
2995 , p_date_param1 => p_x_line_tbl(I).schedule_arrival_date
2996 , p_request_type => OE_GLOBALS.G_UPDATE_REQUISITION
2997 , x_return_status => l_return_status
2998 );
2999
3000 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3002 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3003 RAISE FND_API.G_EXC_ERROR;
3004 END IF;
3005
3006
3007 ELSE -- Added for IR ISO Tracking bug 7667702
3008 IF l_debug_level > 0 THEN
3009 oe_debug_pub.add(' Need By Date is not allowed to update. Updating MTL_Supply only',5);
3010 END IF;
3011
3012 OE_SCHEDULE_UTIL.Update_PO(p_x_line_tbl(I).schedule_arrival_date,
3013 p_x_line_tbl(I).source_document_id,
3014 p_x_line_tbl(I).source_document_line_id);
3015 END IF;
3016
3017 END IF;
3018
3019 /* ============================= */
3020 /* IR ISO Change Management Ends */
3021
3022 IF l_debug_level > 0 THEN
3023 oe_debug_pub.add( 'AFTER PO CALL BACK' , 3 ) ;
3024 END IF;
3025 END IF;
3026
3027 I := p_x_line_tbl.NEXT(I);
3028
3029 END LOOP;
3030
3031 IF l_debug_level > 0 THEN
3032 oe_debug_pub.add( '------- DONE WITH DIRECT UPDATES IF ANY' , 1 ) ;
3033 END IF;
3034
3035 ------------------- call process order -------------------
3036
3037 IF l_call_po = 'Y' THEN
3038
3039 l_control_rec.controlled_operation := TRUE;
3040 l_control_rec.change_attributes := TRUE;
3041
3042 l_control_rec.clear_dependents := TRUE;
3043 l_control_rec.default_attributes := TRUE;
3044 IF l_redo_security_check = 'N' THEN
3045 l_control_rec.check_security := FALSE;
3046 ELSE
3047 l_control_rec.check_security := TRUE;
3048 END IF;
3049
3050 l_control_rec.write_to_DB := TRUE;
3051 l_control_rec.validate_entity := TRUE;
3052 l_control_rec.process := FALSE;
3053
3054 -- Instruct API to retain its caches
3055
3056 l_control_rec.clear_api_cache := FALSE;
3057 l_control_rec.clear_api_requests := FALSE;
3058
3059 OE_Schedule_Util.Call_Process_Order
3060 ( p_x_old_line_tbl => p_old_line_tbl
3061 ,p_x_line_tbl => p_x_line_tbl
3062 ,p_control_rec => l_control_rec
3063 ,p_caller => p_caller
3064 ,x_return_status => x_return_status);
3065
3066 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3068 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3069 RAISE FND_API.G_EXC_ERROR;
3070 END IF;
3071
3072 IF p_caller = OE_Schedule_Util.SCH_INTERNAL THEN
3073 l_process_requests := FALSE;
3074 ELSE
3075 l_process_requests := TRUE;
3076 END IF;
3077
3078 OE_Order_PVT.Process_Requests_And_Notify
3079 ( p_process_requests => l_process_requests
3080 ,p_notify => TRUE
3081 ,p_line_tbl => p_x_line_tbl
3082 ,p_old_line_tbl => p_old_line_tbl
3083 ,x_return_status => x_return_status);
3084
3085 IF l_debug_level > 0 THEN
3086 oe_debug_pub.add( 'PRN RETURN_STATUS ' || X_RETURN_STATUS , 1 ) ;
3087 END IF;
3088
3089 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3090 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3091 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3092 RAISE FND_API.G_EXC_ERROR;
3093 END IF;
3094
3095 END IF;
3096
3097 IF l_debug_level > 0 THEN
3098 oe_debug_pub.add( 'LEAVING SAVE_SCH_ATTRIBURES' , 1 ) ;
3099 END IF;
3100 EXCEPTION
3101 WHEN OTHERS THEN
3102 IF l_debug_level > 0 THEN
3103 oe_debug_pub.add( 'ERROR SAVE_SCH_ATTRIBUTES '|| SQLERRM , 1 ) ;
3104 END IF;
3105 RAISE;
3106 END Save_Sch_Attributes;
3107
3108
3109 /*-----------------------------------------------------------
3110 PROCEDURE Call_Security_And_Defaulting
3111
3112 This procedure is used to call security and defaulting
3113 and to decide if we need to make a process_order call
3114 for the scheduling attributes or not.
3115 ------------------------------------------------------------*/
3116 PROCEDURE Call_Security_And_Defaulting
3117 ( p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
3118 ,p_old_line_rec IN OE_ORDER_PUB.line_rec_type
3119 ,x_direct_update OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3120 IS
3121 l_sec_result NUMBER;
3122 l_src_attr_tbl OE_GLOBALS.NUMBER_Tbl_Type;
3123 l_return_status VARCHAR2(1);
3124 --
3125 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3126 --
3127 BEGIN
3128
3129 IF l_debug_level > 0 THEN
3130 oe_debug_pub.add( 'ENTERING SECURITY_AND_DEFAULTING ' , 2 ) ;
3131 END IF;
3132
3133 x_direct_update := 'N';
3134
3135 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
3136 OE_Line_Security.Attributes
3137 ( p_line_rec => p_x_line_rec
3138 ,p_old_line_rec => p_old_line_rec
3139 ,x_result => l_sec_result
3140 ,x_return_status => l_return_status );
3141
3142 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3144 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3145 RAISE FND_API.G_EXC_ERROR;
3146 END IF;
3147
3148 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
3149 IF l_debug_level > 0 THEN
3150 oe_debug_pub.add( 'AFTER SECURITY CALL' || L_RETURN_STATUS , 1 ) ;
3151 END IF;
3152
3153 -- if operation on any attribute is constrained
3154 IF l_sec_result = OE_PC_GLOBALS.YES THEN
3155 IF l_debug_level > 0 THEN
3156 oe_debug_pub.add( 'CONSTRAINT FOUND' , 4 ) ;
3157 END IF;
3158 RAISE FND_API.G_EXC_ERROR;
3159 END IF;
3160
3161
3162 IF NOT OE_GLOBALS.Equal(p_x_line_rec.ship_from_org_id,
3163 p_old_line_rec.ship_from_org_id) OR
3164 NOT OE_GLOBALS.Equal(p_x_line_rec.ship_to_org_id,
3165 p_old_line_rec.ship_to_org_id) OR
3166 NOT OE_GLOBALS.Equal(p_x_line_rec.inventory_item_id,
3167 p_old_line_rec.inventory_item_id)
3168
3169 THEN
3170 IF l_debug_level > 0 THEN
3171 oe_debug_pub.add( 'SHIP FROM/TO/ITEM CHANGED ON LINE CALL PO' , 1 ) ;
3172 END IF;
3173 ELSE
3174 OE_GLOBALS.G_ATTR_UPDATED_BY_DEF := 'N';
3175
3176 IF l_debug_level > 0 THEN
3177 oe_debug_pub.add( 'OLD SHIP :' || P_OLD_LINE_REC.SCHEDULE_SHIP_DATE , 1 ) ;
3178 END IF;
3179 IF l_debug_level > 0 THEN
3180 oe_debug_pub.add( 'NEW SHIP :' || P_X_LINE_REC.SCHEDULE_SHIP_DATE , 1 ) ;
3181 END IF;
3182 --8706868
3183 IF NOT OE_GLOBALS.Equal(p_x_line_rec.schedule_ship_date,
3184 p_old_line_rec.schedule_ship_date)
3185 OR NOT OE_GLOBALS.Equal(p_x_line_rec.schedule_arrival_date,
3186 p_old_line_rec.schedule_arrival_date)
3187
3188 THEN
3189
3190 IF l_debug_level > 0 THEN
3191 oe_debug_pub.add( 'SHIP_DATE HAS CHANGED ON THE LINE' , 1 ) ;
3192 END IF;
3193
3194 l_src_attr_tbl(1) := OE_LINE_UTIL.G_SCHEDULE_SHIP_DATE;
3195
3196 OE_Line_Util_Ext.Clear_Dep_And_Default
3197 ( p_src_attr_tbl => l_src_attr_tbl,
3198 p_x_line_rec => p_x_line_rec,
3199 p_old_line_rec => p_old_line_rec);
3200
3201 IF l_debug_level > 0 THEN
3202 oe_debug_pub.add( 'DEFAULT ' || OE_GLOBALS.G_ATTR_UPDATED_BY_DEF , 1 ) ;
3203 END IF;
3204
3205 IF OE_GLOBALS.G_ATTR_UPDATED_BY_DEF = 'N' THEN
3206 x_direct_update := 'Y';
3207 END IF;
3208
3209 END IF;
3210 END IF; -- ship from/ship to change
3211
3212 IF l_debug_level > 0 THEN
3213 oe_debug_pub.add( '------LEAVING SECURITY/DEF '|| X_DIRECT_UPDATE , 1 ) ;
3214 END IF;
3215 EXCEPTION
3216 WHEN OTHERS THEN
3217 IF l_debug_level > 0 THEN
3218 oe_debug_pub.add( 'CALL_SECURITY_AND_DEFAULTING '|| SQLERRM , 1 ) ;
3219 END IF;
3220 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
3221 RAISE;
3222 END Call_Security_And_Defaulting;
3223
3224
3225 /*-------------------------------------------------------------------
3226 PROCEDURE Handle_Direct_Update
3227 --------------------------------------------------------------------*/
3228 PROCEDURE Handle_Direct_Update
3229 ( p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type
3230 ,p_old_line_rec IN OE_ORDER_PUB.line_rec_type
3231 ,p_caller IN VARCHAR2)
3232 IS
3233 l_order_type_id NUMBER := OE_Order_Cache.g_header_rec.order_type_id;
3234 l_return_status VARCHAR2(1);
3235 l_index NUMBER;
3236 --
3237 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3238 --
3239 BEGIN
3240
3241 IF l_debug_level > 0 THEN
3242 oe_debug_pub.add( 'ENTERING HANDLE_DIRECT_UPDATE' , 1 ) ;
3243 END IF;
3244
3245 OE_MSG_PUB.set_msg_context
3246 ( p_entity_code => 'LINE'
3247 ,p_entity_id => p_x_line_rec.line_id
3248 ,p_header_id => p_x_line_rec.header_id
3249 ,p_line_id => p_x_line_rec.line_id
3250 ,p_orig_sys_document_ref => p_x_line_rec.orig_sys_document_ref
3251 ,p_orig_sys_document_line_ref => p_x_line_rec.orig_sys_line_ref
3252 ,p_orig_sys_shipment_ref => p_x_line_rec.orig_sys_shipment_ref
3253 ,p_change_sequence => p_x_line_rec.change_sequence
3254 ,p_source_document_id => p_x_line_rec.source_document_id
3255 ,p_source_document_line_id => p_x_line_rec.source_document_line_id
3256 ,p_order_source_id => p_x_line_rec.order_source_id
3257 ,p_source_document_type_id => p_x_line_rec.source_document_type_id);
3258
3259 OE_LINE_UTIL.Log_Scheduling_Requests
3260 ( p_x_line_rec => p_x_line_rec
3261 ,p_old_line_rec => p_old_line_rec
3262 ,p_caller => p_caller
3263 ,p_order_type_id => l_order_type_id
3264 ,x_return_status => l_return_status);
3265
3266 IF l_debug_level > 0 THEN
3267 oe_debug_pub.add( 'AFTER LOG_SCHEDULING REQS' || L_RETURN_STATUS , 1 ) ;
3268 END IF;
3269
3270 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3272 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3273 RAISE FND_API.G_EXC_ERROR;
3274 END IF;
3275
3276 IF l_debug_level > 0 THEN
3277 oe_debug_pub.add( 'BEFORE DOING DIRECT UPDATE' , 1 ) ;
3278 END IF;
3279
3280 IF NOT OE_SCHEDULE_UTIL.validate_ship_method
3281 (p_x_line_rec.shipping_method_code,
3282 p_old_line_rec.shipping_method_code,
3283 p_x_line_rec.ship_from_org_id) THEN
3284
3285 p_x_line_rec.shipping_method_code := Null;
3286 p_x_line_rec.freight_carrier_code := Null;
3287 END IF;
3288
3289 -- Start 2806483
3290 IF (p_x_line_rec.shipping_method_code IS NOT NULL
3291 AND p_x_line_rec.shipping_method_code <> FND_API.G_MISS_CHAR)
3292 AND NOT OE_GLOBALS.EQUAL(p_x_line_rec.shipping_method_code
3293 ,p_old_line_rec.shipping_method_code)
3294 THEN
3295
3296 p_x_line_rec.freight_carrier_code :=
3297 OE_Default_Line.Get_Freight_Carrier(p_line_rec => p_x_line_rec,
3298 p_old_line_rec => p_old_line_rec);
3299 END IF;
3300 -- End 2806483
3301
3302 -- Pack J -Promise Date added to reflect changes
3303 UPDATE OE_ORDER_LINES
3304 SET ship_from_org_id = p_x_line_rec.ship_from_org_id
3305 ,schedule_ship_date = p_x_line_rec.schedule_ship_date
3306 ,schedule_arrival_date = p_x_line_rec.schedule_arrival_date
3307 ,delivery_lead_time = p_x_line_rec.delivery_lead_time
3308 ,mfg_lead_time = p_x_line_rec.mfg_lead_time
3309 ,shipping_method_code = p_x_line_rec.shipping_method_code
3310 ,schedule_status_code = p_x_line_rec.schedule_status_code
3311 ,visible_demand_flag = p_x_line_rec.visible_demand_flag
3312 ,Original_Inventory_Item_Id = p_x_line_rec.Original_Inventory_Item_Id
3313 ,Original_item_identifier_Type
3314 = p_x_line_rec.Original_item_identifier_Type
3315 ,Original_ordered_item_id = p_x_line_rec.Original_ordered_item_id
3316 ,Original_ordered_item = p_x_line_rec.Original_ordered_item
3317 ,latest_acceptable_date = p_x_line_rec.latest_acceptable_date
3318 ,override_atp_date_code = p_x_line_rec.override_atp_date_code
3319 ,freight_carrier_code = p_x_line_rec.freight_carrier_code
3320 ,Firm_Demand_Flag = p_x_line_rec.Firm_Demand_Flag
3321 ,earliest_ship_date = p_x_line_rec.Earliest_ship_date
3322 ,promise_date = p_x_line_rec.promise_date
3323 ,last_update_date = SYSDATE
3324 ,last_updated_by = FND_GLOBAL.USER_ID
3325 ,last_update_login = FND_GLOBAL.LOGIN_ID
3326 ,lock_control = p_x_line_rec.lock_control + 1
3327 WHERE LINE_ID = p_x_line_rec.line_id; -- 2806483 Added fright_carrier_code
3328 --2792869
3329 -- added for notification framework
3330 --check code release level first. Notification framework is at Pack H level
3331
3332 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
3333
3334 -- calling notification framework to get index position
3335 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>True,-- bug 12613382
3336 p_old_line_rec => p_old_line_rec,
3337 p_line_rec =>p_x_line_rec,
3338 p_line_id => p_x_line_rec.line_id,
3339 x_index => l_index,
3340 x_return_status => l_return_status);
3341 IF l_debug_level > 0 THEN
3342 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_SCHEDULE_UTIL.PROCESS_LINE IS: ' || L_RETURN_STATUS ) ;
3343 oe_debug_pub.add( 'GLOBAL PICTURE INDEX IS: ' || L_INDEX , 1 ) ;
3344 END IF;
3345
3346 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3347 IF l_debug_level > 0 THEN
3348 oe_debug_pub.add( 'RR: UNEXP ERRORED OUT' , 1 ) ;
3349 END IF;
3350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3351 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3352 IF l_debug_level > 0 THEN
3353 oe_debug_pub.add( 'RR: ERRORED OUT' , 1 ) ;
3354 END IF;
3355 RAISE FND_API.G_EXC_ERROR;
3356 END IF;
3357 IF l_index is not NULL THEN
3358 --update Global Picture directly
3359 OE_ORDER_UTIL.g_line_tbl(l_index).ship_from_org_id := p_x_line_rec.ship_from_org_id;
3360 OE_ORDER_UTIL.g_line_tbl(l_index).schedule_ship_date := p_x_line_rec.schedule_ship_date;
3361 OE_ORDER_UTIL.g_line_tbl(l_index).schedule_arrival_date := p_x_line_rec.schedule_arrival_date;
3362 OE_ORDER_UTIL.g_line_tbl(l_index).delivery_lead_time := p_x_line_rec.delivery_lead_time;
3363 OE_ORDER_UTIL.g_line_tbl(l_index).mfg_lead_time := p_x_line_rec.mfg_lead_time;
3364 OE_ORDER_UTIL.g_line_tbl(l_index).shipping_method_code := p_x_line_rec.shipping_method_code;
3365 OE_ORDER_UTIL.g_line_tbl(l_index).schedule_status_code := p_x_line_rec.schedule_status_code;
3366 OE_ORDER_UTIL.g_line_tbl(l_index).visible_demand_flag := p_x_line_rec.visible_demand_flag;
3367 OE_ORDER_UTIL.g_line_tbl(l_index).latest_acceptable_date := p_x_line_rec.latest_acceptable_date;
3368 OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date := p_x_line_rec.last_update_date;
3369 OE_ORDER_UTIL.g_line_tbl(l_index).last_updated_by := p_x_line_rec.last_updated_by;
3370 OE_ORDER_UTIL.g_line_tbl(l_index).last_update_login := p_x_line_rec.last_update_login;
3371 OE_ORDER_UTIL.g_line_tbl(l_index).lock_control := p_x_line_rec.lock_control;
3372 IF l_debug_level > 0 THEN
3373 oe_debug_pub.add( 'GLOBAL SHIP_FROM_ORG_ID IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_INDEX ) .SHIP_FROM_ORG_ID , 1 ) ;
3374 END IF;
3375 END IF; /*l_index is not null check*/
3376 END IF; /*code_release_level*/
3377
3378 IF l_debug_level > 0 THEN
3379 oe_debug_pub.add( 'LEAVING HANDLE DIRECT UPDATE' , 1 ) ;
3380 END IF;
3381
3382 EXCEPTION
3383 WHEN OTHERS THEN
3384 IF l_debug_level > 0 THEN
3385 oe_debug_pub.add( 'HANDLE_DIRECT_UPDATE ERROR '|| SQLERRM , 1 ) ;
3386 END IF;
3387 RAISE;
3388 END Handle_Direct_Update;
3389
3390
3391 /*---------------------------------------------------------------
3392 PROCEDURE Update_Reservation
3393 This procedure should be used to update the reservation record
3394 with new schedule ship date, after a call to RESCHEDULE.
3395 ---------------------------------------------------------------*/
3396
3397 PROCEDURE Update_Reservation
3398 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type
3399 ,p_old_line_rec IN OE_Order_Pub.Line_Rec_Type
3400 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3401 IS
3402 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
3403 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
3404 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
3405 l_sales_order_id NUMBER;
3406 l_lock_records VARCHAR2(1);
3407 l_sort_by_req_date NUMBER;
3408 l_buffer VARCHAR2(2000);
3409 l_msg_data VARCHAR2(2000);
3410 l_msg_count NUMBER;
3411 l_error_code NUMBER;
3412 l_count NUMBER;
3413 I NUMBER;
3414 --
3415 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3416 --
3417 BEGIN
3418 Print_Time('entering Update_Reservation');
3419
3420 x_return_status := FND_API.G_RET_STS_SUCCESS;
3421
3422 IF OE_GLOBALS.Equal(p_line_rec.schedule_ship_date,
3423 p_old_line_rec.schedule_ship_date) OR
3424 p_line_rec.schedule_ship_date IS NULL OR -- bug 3542464
3425 p_line_rec.reserved_quantity <= 0 OR
3426 p_line_rec.ordered_quantity = 0
3427 THEN
3428 IF l_debug_level > 0 THEN
3429 oe_debug_pub.add( 'NO NEED TO UPDATE OR NO RESERVN' , 3 ) ;
3430 END IF;
3431 RETURN;
3432 END IF;
3433
3434 l_rsv_rec.reservation_id := fnd_api.g_miss_num;
3435
3436 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
3437 (p_line_rec.header_id);
3438
3439 l_rsv_rec.demand_source_header_id := l_sales_order_id;
3440 l_rsv_rec.demand_source_line_id := p_line_rec.line_id;
3441 l_rsv_rec.organization_id := p_line_rec.ship_from_org_id;
3442
3443 IF l_debug_level > 0 THEN
3444 oe_debug_pub.add( 'RSCH: CALLING INVS QUERY_RESERVATION ' , 1 ) ;
3445 END IF;
3446
3447 Inv_Reservation_Pub.Query_Reservation
3448 ( p_api_version_number => 1.0
3449 , p_init_msg_lst => fnd_api.g_true
3450 , x_return_status => x_return_status
3451 , x_msg_count => l_msg_count
3452 , x_msg_data => l_msg_data
3453 , p_query_input => l_rsv_rec
3454 , x_mtl_reservation_tbl => l_rsv_tbl
3455 , x_mtl_reservation_tbl_count => l_count
3456 , x_error_code => l_error_code
3457 , p_lock_records => l_lock_records
3458 , p_sort_by_req_date => l_sort_by_req_date );
3459
3460 IF l_debug_level > 0 THEN
3461 oe_debug_pub.add( 'AFTER INVS QUERY_RESERVATION: ' || X_RETURN_STATUS , 1 ) ;
3462 END IF;
3463
3464 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3466 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3467 RAISE FND_API.G_EXC_ERROR;
3468 END IF;
3469
3470 IF l_debug_level > 0 THEN
3471 oe_debug_pub.add( 'RESERVATION REC COUNT: ' || L_RSV_TBL.COUNT , 1 ) ;
3472 END IF;
3473
3474 I := l_rsv_tbl.FIRST;
3475 WHILE I is not NULL
3476 LOOP
3477
3478 l_rsv_rec := l_rsv_tbl(I);
3479 l_rsv_rec.requirement_date := p_line_rec.schedule_ship_date;
3480
3481 IF l_debug_level > 0 THEN
3482 oe_debug_pub.add( 'RSCH: CALLING UPDATE RESERVATION '|| I , 1 ) ;
3483 END IF;
3484
3485 Inv_Reservation_Pub.Update_Reservation
3486 ( p_api_version_number => 1.0
3487 , p_init_msg_lst => fnd_api.g_true
3488 , x_return_status => x_return_status
3489 , x_msg_count => l_msg_count
3490 , x_msg_data => l_msg_data
3491 , p_original_rsv_rec => l_rsv_tbl(I)
3492 , p_to_rsv_rec => l_rsv_rec
3493 , p_original_serial_number => l_dummy_sn -- no serial contorl
3494 , p_to_serial_number => l_dummy_sn -- no serial control
3495 , p_validation_flag => fnd_api.g_true
3496 , p_over_reservation_flag => 2 ); -- Added this for 4715544
3497
3498 IF l_debug_level > 0 THEN
3499 oe_debug_pub.add( 'AFTER UPDATE_RESERVATION: '||X_RETURN_STATUS , 1 ) ;
3500 END IF;
3501
3502 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3504
3505 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3506 IF l_msg_data is not null THEN
3507
3508 OE_MSG_PUB.set_msg_context
3509 ( p_entity_code => 'LINE'
3510 ,p_entity_id => p_line_rec.line_id
3511 ,p_header_id => p_line_rec.header_id
3512 ,p_line_id => p_line_rec.line_id
3513 ,p_orig_sys_document_ref => p_line_rec.orig_sys_document_ref
3514 ,p_orig_sys_document_line_ref => p_line_rec.orig_sys_line_ref
3515 ,p_orig_sys_shipment_ref => p_line_rec.orig_sys_shipment_ref
3516 ,p_change_sequence => p_line_rec.change_sequence
3517 ,p_source_document_id => p_line_rec.source_document_id
3518 ,p_source_document_line_id => p_line_rec.source_document_line_id
3519 ,p_order_source_id => p_line_rec.order_source_id
3520 ,p_source_document_type_id => p_line_rec.source_document_type_id);
3521
3522 fnd_message.set_encoded(l_msg_data);
3523 l_buffer := fnd_message.get;
3524 oe_msg_pub.add_text(p_message_text => l_buffer);
3525 IF l_debug_level > 0 THEN
3526 oe_debug_pub.add( 'ERROR : '|| L_BUFFER , 1 ) ;
3527 END IF;
3528 END IF;
3529 RAISE FND_API.G_EXC_ERROR;
3530 END IF;
3531
3532 I := l_rsv_tbl.NEXT(I);
3533 END LOOP;
3534
3535 EXCEPTION
3536 WHEN OTHERS THEN
3537 IF l_debug_level > 0 THEN
3538 oe_debug_pub.add( 'UPDATE_RESERVATION ERROR '|| SQLERRM , 1 ) ;
3539 END IF;
3540 RAISE;
3541 END Update_Reservation;
3542
3543
3544 /*---------------------------------------------------------------
3545 Procedure : Query_Set_Lines
3546 Description:
3547 This procedure will query the lines belonging to the group
3548 identified by the i/p **_id parameter.
3549 It will create the included items, if they are not already
3550 exploded. will obtain and set the reserved quantity of the
3551 included items.
3552
3553 Note: The order of if, elsif is importatnt, we want to give
3554 priority of the query as follows:
3555 1.Arrival_Set
3556 2.Ship_Set
3557 3.Model
3558
3559 Right now, I am not making the cursor parameterized, because
3560 even if all 3 parameters are passed, the cursor is going to
3561 result in correct selection, ex: if model_line_id and ship_set_id
3562 both are set, all lines selected by 1st part are subset of lines
3563 selected by 2nd part.
3564 ---------------------------------------------------------------*/
3565 Procedure Query_Set_Lines
3566 (p_header_id IN NUMBER,
3567 p_ship_set_id IN NUMBER := FND_API.G_MISS_NUM,
3568 p_arrival_set_id IN NUMBER := FND_API.G_MISS_NUM,
3569 p_model_line_id IN NUMBER := FND_API.G_MISS_NUM,
3570 p_link_to_line_id IN NUMBER := FND_API.G_MISS_NUM,
3571 p_sch_action IN VARCHAR2,
3572 p_send_cancel_lines IN VARCHAR2 := 'N',
3573 x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type,
3574 x_return_status OUT NOCOPY VARCHAR2)
3575 IS
3576 l_sales_order_id NUMBER;
3577 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3578 l_sch_gloabl_value VARCHAR2(1);
3579 I NUMBER;
3580
3581 CURSOR inc_parents is
3582 SELECT line_id
3583 FROM oe_order_lines
3584 WHERE item_type_code IN ('MODEL', 'CLASS', 'KIT')
3585 AND ato_line_id is NULL
3586 AND explosion_date is NULL
3587 AND header_id = p_header_id
3588 AND ((top_model_line_id = p_model_line_id)
3589 OR (ship_set_id = p_ship_set_id)
3590 OR (arrival_set_id = p_arrival_set_id)
3591 OR (line_id = p_link_to_line_id));
3592
3593 --
3594 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3595 --
3596 --sol_ord_er #16014165
3597 l_linetmp_tbl OE_Order_PUB.Line_Tbl_Type;
3598 l_new_ctr NUMBER := 0;
3599 --sol_ord_er #16014165 end
3600 BEGIN
3601 Print_Time('Entering Query_Set_Lines '|| p_sch_action);
3602
3603 x_return_status := FND_API.G_RET_STS_SUCCESS;
3604
3605 IF p_link_to_line_id <> FND_API.G_MISS_NUM AND
3606 (p_ship_set_id <> FND_API.G_MISS_NUM OR
3607 p_arrival_set_id <> FND_API.G_MISS_NUM OR
3608 p_model_line_id <> FND_API.G_MISS_NUM )
3609 THEN
3610 IF l_debug_level > 0 THEN
3611 oe_debug_pub.add( 'INCORRECT IN PARAMETERS' , 1 ) ;
3612 END IF;
3613 RAISE FND_API.G_EXC_ERROR;
3614 END IF;
3615
3616
3617 IF nvl(p_sch_action, 'A' ) <> OE_Schedule_Util.OESCH_ACT_UNSCHEDULE
3618 AND
3619 nvl(p_sch_action, 'A' ) <> OE_Schedule_Util.OESCH_ACT_UNRESERVE
3620 THEN
3621
3622 l_sch_gloabl_value := OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING;
3623 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
3624 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
3625
3626 IF l_debug_level > 0 THEN
3627 oe_debug_pub.add( 'CHECK IF II ITEMS REQ.' , 3 ) ;
3628 END IF;
3629
3630 FOR parentrec in inc_parents
3631 LOOP
3632 IF l_debug_level > 0 THEN
3633 oe_debug_pub.add( 'CALL II_ITEMS'|| PARENTREC.LINE_ID , 3 ) ;
3634 END IF;
3635 l_return_status := OE_CONFIG_UTIL.Process_Included_Items
3636 (p_line_id => parentrec.line_id,
3637 p_freeze => FALSE);
3638
3639
3640 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3642 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3643 RAISE FND_API.G_EXC_ERROR;
3644 END IF;
3645 END LOOP;
3646
3647 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := l_sch_gloabl_value;
3648 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
3649 END IF;
3650
3651
3652 IF p_arrival_set_id <> FND_API.G_MISS_NUM THEN
3653 IF l_debug_level > 0 THEN
3654 oe_debug_pub.add( 'ARRIVAL SET ID '|| P_ARRIVAL_SET_ID , 3 ) ;
3655 END IF;
3656
3657 OE_Set_Util.Query_Set_Rows
3658 (p_set_id => p_arrival_set_id,
3659 x_line_tbl => x_line_tbl);
3660
3661 ELSIF p_ship_set_id <> FND_API.G_MISS_NUM THEN
3662 IF l_debug_level > 0 THEN
3663 oe_debug_pub.add( 'SHIP SET ID '|| P_SHIP_SET_ID , 3 ) ;
3664 END IF;
3665
3666 OE_Set_Util.Query_Set_Rows
3667 (p_set_id => p_ship_set_id,
3668 x_line_tbl => x_line_tbl);
3669
3670 ELSIF p_model_line_id <> FND_API.G_MISS_NUM THEN
3671 IF l_debug_level > 0 THEN
3672 oe_debug_pub.add( 'P_TOP_MODEL_LINE_ID '|| P_MODEL_LINE_ID , 3 ) ;
3673 END IF;
3674
3675 OE_Config_Util.Query_Options
3676 (p_top_model_line_id => p_model_line_id,
3677 p_send_cancel_lines => p_send_cancel_lines,
3678 p_source_type => OE_Globals.G_SOURCE_INTERNAL,
3679 x_line_tbl => x_line_tbl);
3680
3681 ELSIF p_link_to_line_id <> FND_API.G_MISS_NUM THEN
3682 IF l_debug_level > 0 THEN
3683 oe_debug_pub.add( 'P_LINK_TO_LINE_ID '|| P_LINK_TO_LINE_ID , 3 ) ;
3684 END IF;
3685
3686 OE_Config_Util.Query_Included_Items
3687 (p_line_id => p_link_to_line_id,
3688 p_send_cancel_lines => p_send_cancel_lines,
3689 p_source_type => OE_Globals.G_SOURCE_INTERNAL,
3690 x_line_tbl => x_line_tbl);
3691
3692 END IF;
3693
3694 IF x_line_tbl.count > 0 THEN
3695 IF l_debug_level > 0 THEN
3696 oe_debug_pub.add( 'NO. OF LINES '|| X_LINE_TBL.COUNT , 3 ) ;
3697 END IF;
3698 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
3699 (p_header_id);
3700 END IF;
3701
3702 I := x_line_tbl.FIRST;
3703 WHILE I is not NULL
3704 LOOP
3705 IF x_line_tbl(I).schedule_status_code is not null THEN
3706
3707 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
3708
3709 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
3710 ,p_line_id => x_line_tbl(I).line_id
3711 ,p_org_id => x_line_tbl(I).ship_from_org_id
3712 ,x_reserved_quantity => x_line_tbl(I).reserved_quantity
3713 ,x_reserved_quantity2 => x_line_tbl(I).reserved_quantity2
3714 );
3715
3716 /*x_line_tbl(I).reserved_quantity :=
3717 OE_LINE_UTIL.Get_Reserved_Quantity
3718 (p_header_id => l_sales_order_id,
3719 p_line_id => x_line_tbl(I).line_id,
3720 p_org_id => x_line_tbl(I).ship_from_org_id);
3721 x_line_tbl(I).reserved_quantity2 := -- INVCONV
3722 OE_LINE_UTIL.Get_Reserved_Quantity2
3723 (p_header_id => l_sales_order_id,
3724 p_line_id => x_line_tbl(I).line_id,
3725 p_org_id => x_line_tbl(I).ship_from_org_id); */
3726
3727 END IF;
3728
3729
3730 IF x_line_tbl(I).reserved_quantity is NULL OR
3731 x_line_tbl(I).reserved_quantity = FND_API.G_MISS_NUM
3732 THEN
3733 x_line_tbl(I).reserved_quantity := 0;
3734 ELSE
3735 IF l_debug_level > 0 THEN
3736 oe_debug_pub.add( 'LINE '|| X_LINE_TBL ( I ) .LINE_ID , 4 ) ;
3737 END IF;
3738 IF l_debug_level > 0 THEN
3739 oe_debug_pub.add( 'RES QTY '|| X_LINE_TBL ( I ) .RESERVED_QUANTITY , 4 ) ;
3740 END IF;
3741 END IF;
3742
3743 IF x_line_tbl(I).reserved_quantity2 is NULL OR -- INVCONV
3744 x_line_tbl(I).reserved_quantity2 = FND_API.G_MISS_NUM
3745 THEN
3746 x_line_tbl(I).reserved_quantity2 := 0;
3747 ELSE
3748 IF l_debug_level > 0 THEN
3749 oe_debug_pub.add( 'LINE '|| X_LINE_TBL ( I ) .LINE_ID , 4 ) ;
3750 END IF;
3751 IF l_debug_level > 0 THEN
3752 oe_debug_pub.add( 'RES QTY2 '|| X_LINE_TBL ( I ) .RESERVED_QUANTITY2 , 4 ) ;
3753 END IF;
3754 END IF;
3755
3756 I := x_line_tbl.NEXT(I);
3757 END LOOP;
3758 -- sol_ord_er #16014165 start
3759 /* The Query out should not return Model components which are Subscription Lines */
3760 /* Remove the Subscription Line entries, Store the remaining entries into new temp table */
3761 /* Ensure index is not missed out and send the new table type as OUT */
3762 l_new_ctr := 1;
3763 If(x_line_tbl .count >0 ) THEN
3764 For l_ctr in x_line_tbl.FIRST .. x_line_tbl.LAST
3765 LOOP
3766 If NVL(x_line_tbl(l_ctr).subscription_enable_flag,'N') = 'Y' THEN
3767 If l_debug_level > 0 THEN
3768 oe_debug_pub.add( 'Line ID-- '||x_line_tbl(l_ctr).line_id||'..index '||l_ctr
3769 ||' is Subscription Line, removing from Query Set');
3770 End If;
3771 x_line_tbl.DELETE(l_ctr);
3772 Else
3773 l_linetmp_tbl(l_new_ctr) := x_line_tbl(l_ctr);
3774 If l_debug_level > 0 THEN
3775 oe_debug_pub.add( 'Line ID-- '||x_line_tbl(l_ctr).line_id||'..index '||l_ctr
3776 ||' is retained in Query Set; new index..'||l_new_ctr);
3777 End If;
3778 l_new_ctr := l_new_ctr+1;
3779 End If;
3780 End Loop;
3781 x_line_tbl.delete;
3782 x_line_tbl := l_linetmp_tbl;
3783 End IF;
3784 -- sol_ord_er #16014165 end
3785
3786 Print_Time('Exiting Query_Set_Lines');
3787
3788 EXCEPTION
3789
3790 WHEN FND_API.G_EXC_ERROR THEN
3791 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
3792 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
3793 IF l_debug_level > 0 THEN
3794 oe_debug_pub.add( 'EXC ERROR IN Query_Set_Lines' , 1 ) ;
3795 END IF;
3796 x_return_status := FND_API.G_RET_STS_ERROR;
3797
3798 WHEN OTHERS THEN
3799
3800 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
3801 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
3802
3803 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3804 THEN
3805 OE_MSG_PUB.Add_Exc_Msg
3806 ( G_PKG_NAME
3807 ,'Query_Set_Lines'
3808 );
3809 END IF;
3810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3812
3813 END Query_Set_Lines;
3814
3815
3816 /*-----------------------------------------------------------
3817 may be I will replace my individual queries by this api
3818 in future.
3819 ------------------------------------------------------------*/
3820 PROCEDURE Query_Config_Attributes
3821 ( p_line_id IN NUMBER
3822 ,p_sch_attibs IN VARCHAR2 := 'N'
3823 ,x_line_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_Pub.Line_Rec_Type)
3824 IS
3825 --
3826 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3827 --
3828 BEGIN
3829 IF l_debug_level > 0 THEN
3830 oe_debug_pub.add( 'QUERY CA LINE_ID '|| P_LINE_ID , 1 ) ;
3831 END IF;
3832 SELECT ATO_LINE_ID
3833 ,BOOKED_FLAG
3834 ,COMPONENT_CODE
3835 ,COMPONENT_NUMBER
3836 ,COMPONENT_SEQUENCE_ID
3837 ,CONFIG_HEADER_ID
3838 ,CONFIG_REV_NBR
3839 ,CONFIGURATION_ID
3840 ,CREATION_DATE
3841 ,EXPLOSION_DATE
3842 ,HEADER_ID
3843 ,ITEM_TYPE_CODE
3844 ,LINE_ID
3845 ,LINE_NUMBER
3846 ,LINK_TO_LINE_ID
3847 ,OPEN_FLAG
3848 ,OPTION_NUMBER
3849 ,ORDERED_QUANTITY
3850 ,ORDER_QUANTITY_UOM
3851 ,ORDERED_ITEM
3852 ,SHIPPABLE_FLAG
3853 ,SHIP_MODEL_COMPLETE_FLAG
3854 ,SPLIT_FROM_LINE_ID
3855 ,MODEL_REMNANT_FLAG
3856 ,TOP_MODEL_LINE_ID
3857 ,UPGRADED_FLAG
3858 ,LOCK_CONTROL
3859 INTO
3860 x_line_rec.ATO_LINE_ID
3861 ,x_line_rec.BOOKED_FLAG
3862 ,x_line_rec.COMPONENT_CODE
3863 ,x_line_rec.COMPONENT_NUMBER
3864 ,x_line_rec.COMPONENT_SEQUENCE_ID
3865 ,x_line_rec.CONFIG_HEADER_ID
3866 ,x_line_rec.CONFIG_REV_NBR
3867 ,x_line_rec.CONFIGURATION_ID
3868 ,x_line_rec.CREATION_DATE
3869 ,x_line_rec.EXPLOSION_DATE
3870 ,x_line_rec.HEADER_ID
3871 ,x_line_rec.ITEM_TYPE_CODE
3872 ,x_line_rec.LINE_ID
3873 ,x_line_rec.LINE_NUMBER
3874 ,x_line_rec.LINK_TO_LINE_ID
3875 ,x_line_rec.OPEN_FLAG
3876 ,x_line_rec.OPTION_NUMBER
3877 ,x_line_rec.ORDERED_QUANTITY
3878 ,x_line_rec.ORDER_QUANTITY_UOM
3879 ,x_line_rec.ORDERED_ITEM
3880 ,x_line_rec.SHIPPABLE_FLAG
3881 ,x_line_rec.SHIP_MODEL_COMPLETE_FLAG
3882 ,x_line_rec.SPLIT_FROM_LINE_ID
3883 ,x_line_rec.MODEL_REMNANT_FLAG
3884 ,x_line_rec.TOP_MODEL_LINE_ID
3885 ,x_line_rec.UPGRADED_FLAG
3886 ,x_line_rec.LOCK_CONTROL
3887 FROM OE_ORDER_LINES
3888 WHERE LINE_ID = P_LINE_ID;
3889 EXCEPTION
3890 WHEN OTHERS THEN
3891 IF l_debug_level > 0 THEN
3892 oe_debug_pub.add( 'ERROR IN QUERY_CONFIG_ATTRIBUTES '|| SQLERRM , 2 ) ;
3893 END IF;
3894 RAISE;
3895 END Query_Config_Attributes;
3896
3897
3898 /*---------------------------------------------------------------
3899 Procedure Validate_Group_Request
3900
3901 This procedure is written for the validation of group request
3902 that is passed in.
3903
3904 SCHEDULE_ATO:
3905 You cannot schedule a ATO model without a warehouse.
3906 reserve/unreserve and unscheudle whe config item present
3907 is not allowed on ATO's.
3908
3909 SCHEDULE SMC:
3910 SCHEDULE NONSMC:
3911
3912
3913 With cto change order project, we no longer need to check that
3914 if CONFIG item is created and not allow UNSCHEDULE.
3915
3916 Also the validate item warehouse in OEXLLINB.pls will take
3917 care of checking the warehouse on the ATO options, so we
3918 do not need that check.
3919
3920 ****Not used anymore.
3921 ---------------------------------------------------------------*/
3922 Procedure Validate_Group_Request
3923 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
3924 ,p_request_type IN VARCHAR2
3925 ,p_sch_action IN VARCHAR2
3926 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3927 IS
3928 l_return_status VARCHAR2(1);
3929 l_num_id NUMBER;
3930 --
3931 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3932 --
3933 BEGIN
3934 IF l_debug_level > 0 THEN
3935 oe_debug_pub.add( 'ENTERING VALIDATE_GROUP_REQUEST' , 1 ) ;
3936 END IF;
3937
3938 OE_MSG_PUB.set_msg_context
3939 ( p_entity_code => 'LINE'
3940 ,p_entity_id => p_line_rec.line_id
3941 ,p_header_id => p_line_rec.header_id
3942 ,p_line_id => p_line_rec.line_id
3943 ,p_orig_sys_document_ref => p_line_rec.orig_sys_document_ref
3944 ,p_orig_sys_document_line_ref => p_line_rec.orig_sys_line_ref
3945 ,p_orig_sys_shipment_ref => p_line_rec.orig_sys_shipment_ref
3946 ,p_change_sequence => p_line_rec.change_sequence
3947 ,p_source_document_id => p_line_rec.source_document_id
3948 ,p_source_document_line_id => p_line_rec.source_document_line_id
3949 ,p_order_source_id => p_line_rec.order_source_id
3950 ,p_source_document_type_id => p_line_rec.source_document_type_id);
3951
3952 IF p_request_type = 'SCHEDULE_ATO' THEN
3953
3954 SELECT ship_from_org_id
3955 INTO l_num_id
3956 FROM oe_order_lines
3957 WHERE line_id = p_line_rec.ato_line_id;
3958
3959 IF l_num_id is NULL AND
3960 p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_SCHEDULE
3961 THEN
3962
3963 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ATO_WHSE_REQD');
3964 OE_MSG_PUB.Add;
3965 x_return_status := FND_API.G_RET_STS_ERROR;
3966
3967 RETURN;
3968
3969 END IF;
3970
3971
3972 IF (p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_RESERVE) OR
3973 (p_sch_action = OE_SCHEDULE_UTIL.OESCH_ACT_UNRESERVE)
3974 THEN
3975
3976 FND_MESSAGE.SET_NAME('ONT','OE_SCH_RES_NO_CONFIG');
3977 OE_MSG_PUB.Add;
3978 x_return_status := FND_API.G_RET_STS_ERROR;
3979
3980 RETURN;
3981
3982 END IF;
3983
3984 END IF;
3985
3986
3987 IF l_debug_level > 0 THEN
3988 oe_debug_pub.add( 'LEAVING VALIDATE_GROUP_REQUEST' , 1 ) ;
3989 END IF;
3990 END Validate_Group_Request;
3991
3992
3993 /*-------------------------------------------------------------
3994 Call_ATP
3995 Temporary call.
3996 --------------------------------------------------------------*/
3997 PROCEDURE Call_ATP
3998 ( p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ
3999 ,x_atp_rec OUT NOCOPY /* file.sql.39 change */ MRP_ATP_PUB.ATP_Rec_Typ)
4000 IS
4001 --
4002 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4003 --
4004 BEGIN
4005 IF l_debug_level > 0 THEN
4006 oe_debug_pub.add( 'ENTERING CALL_ATP' , 1 ) ;
4007 END IF;
4008
4009 x_atp_rec := p_atp_rec;
4010
4011 FOR I IN 1..x_atp_rec.Action.LAST
4012 LOOP
4013 x_atp_rec.Ship_Date(I) := x_atp_rec.Requested_Ship_Date(I);
4014 x_atp_rec.Group_Ship_Date(I) := x_atp_rec.Requested_Ship_Date(I);
4015 x_atp_rec.Group_arrival_date(I) := x_atp_rec.Requested_Ship_Date(I);
4016 x_atp_rec.Source_Organization_ID(I) := 204;
4017 END LOOP;
4018
4019 IF l_debug_level > 0 THEN
4020 oe_debug_pub.add( 'LEAVING CALL_ATP' , 1 ) ;
4021 END IF;
4022 EXCEPTION
4023 WHEN OTHERS THEN
4024 IF l_debug_level > 0 THEN
4025 oe_debug_pub.add( 'ERROR IN CALL ATP '|| SQLERRM , 1 ) ;
4026 END IF;
4027 RAISE;
4028 END;
4029
4030
4031 /*----------------------------------------------------------
4032 PROCEDURE Is_Group_Scheduled
4033
4034 values of x_result,
4035 0 not scheduled
4036 1 some lines scheduled
4037 2 all lines scheduled
4038
4039 ****not used
4040 -----------------------------------------------------------*/
4041 PROCEDURE Is_Group_Scheduled
4042 ( p_line_tbl IN OE_Order_Pub.Line_Tbl_Type
4043 ,p_caller IN VARCHAR2 := 'X'
4044 ,x_result OUT NOCOPY /* file.sql.39 change */ NUMBER)
4045 IS
4046 l_unscheduled_line VARCHAR2(1) := 'N';
4047 --
4048 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4049 --
4050 BEGIN
4051
4052 IF p_caller = 'UI_ACTION' THEN
4053
4054 x_result := 0;
4055
4056 FOR I in p_line_tbl.FIRST..p_line_tbl.LAST
4057 LOOP
4058 IF p_line_tbl(I).schedule_status_code is NOT NULL THEN
4059 x_result := 2;
4060 ELSE
4061 l_unscheduled_line := 'Y';
4062 END IF;
4063 END LOOP;
4064
4065 IF l_unscheduled_line = 'Y' AND x_result = 2 THEN
4066 x_result := 1;
4067 END IF;
4068
4069 IF l_debug_level > 0 THEN
4070 oe_debug_pub.add( 'MULTI GRP '|| X_RESULT , 3 ) ;
4071 END IF;
4072
4073 ELSE
4074 IF p_line_tbl(p_line_tbl.FIRST).schedule_status_code is NULL
4075 THEN
4076 IF l_debug_level > 0 THEN
4077 oe_debug_pub.add( 'NOT SCHEDULED SINGLE GRP' , 3 ) ;
4078 END IF;
4079 x_result := 0;
4080 ELSE
4081 IF l_debug_level > 0 THEN
4082 oe_debug_pub.add( 'SCHEDULED SINGLE GRP' , 3 ) ;
4083 END IF;
4084 x_result := 2;
4085 END IF;
4086 END IF;
4087
4088 END Is_Group_Scheduled;
4089
4090
4091 /*--------------------------------------------------------
4092 PROCEDURE Print_Time
4093
4094 --------------------------------------------------------*/
4095
4096 PROCEDURE Print_Time(p_msg IN VARCHAR2)
4097 IS
4098 l_time VARCHAR2(100);
4099 --
4100 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4101 --
4102 BEGIN
4103 l_time := to_char (new_time (sysdate, 'PST', 'EST'),
4104 'DD-MON-YY HH24:MI:SS');
4105 IF l_debug_level > 0 THEN
4106 oe_debug_pub.add( P_MSG || ': '|| L_TIME , 1 ) ;
4107 END IF;
4108 END Print_Time;
4109
4110
4111 END OE_CONFIG_SCHEDULE_PVT;
[Home] [Help]