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