[Home] [Help]
PACKAGE BODY: APPS.OE_RESERVE_CONC
Source
1 PACKAGE BODY OE_RESERVE_CONC AS
2 /* $Header: OEXCRSVB.pls 120.17.12010000.3 2008/11/11 08:17:20 rmoharan ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_RESERVE_CONC';
7 G_SET_ID NUMBER;
8 G_PROGRAM_APPLICATION_ID NUMBER;
9 G_PROGRAM_ID NUMBER;
10 G_RESERVATION_MODE VARCHAR2(30);
11 G_TOTAL_CONSUMED NUMBER :=0;
12 G_CONSUMED_FOR_LOT NUMBER :=0;
13 G_TOTAL_CONSUMED2 NUMBER :=0; -- INVCONV
14 G_CONSUMED_FOR_LOT2 NUMBER :=0; -- INVCONV
15
16
17 Procedure Query_Qty_Tree(p_ship_from_org_id IN NUMBER,
18 p_subinventory_code IN VARCHAR2,
19 p_inventory_item_id IN NUMBER,
20 x_on_hand_qty OUT NOCOPY NUMBER,
21 x_avail_to_reserve OUT NOCOPY NUMBER,
22 x_on_hand_qty2 OUT NOCOPY NUMBER, -- INVCONV
23 x_avail_to_reserve2 OUT NOCOPY NUMBER -- INVCONV
24 );
25
26 /*------------------------------------------------------
27 Procedure Name : Reservable_Quantity
28 Description : This api will call get the primary UOM code of the item and
29 quantity available for reservation.
30 --------------------------------------------------------------------- */
31 PROCEDURE Reservable_Quantity( p_inventory_item_id IN NUMBER,
32 p_ship_from_org_id IN NUMBER,
33 p_subinventory IN VARCHAR2,
34 p_org_id IN NUMBER, -- 4759251
35 x_primary_uom OUT NOCOPY VARCHAR2,
36 x_available_qty OUT NOCOPY NUMBER,
37 x_available_qty2 OUT NOCOPY NUMBER -- INVCONV
38 )
39
40 IS
41
42 l_primary_uom VARCHAR2(3);
43 l_total_supply NUMBER :=0;
44 l_on_hand_qty NUMBER;
45 -- INVCONV
46 l_total_supply2 NUMBER :=0;
47 l_on_hand_qty2 NUMBER;
48
49 --
50 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
51 --
52 BEGIN
53 IF l_debug_level > 0 THEN
54 oe_debug_pub.add( ' ENTERING RESERVABLE_QUANTITY' , 1 ) ;
55 END IF;
56 -- Getting the Primary UOM of the item
57 BEGIN
58 SELECT primary_uom_code
59 INTO l_primary_uom
60 FROM mtl_system_items_b
61 WHERE inventory_item_id = p_inventory_item_id
62 AND organization_id = NVL(p_ship_from_org_id,
63 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID',p_org_id)); --4759251
64 EXCEPTION
65 WHEN OTHERS THEN
66 IF l_debug_level > 0 THEN
67 OE_DEBUG_PUB.Add('Error in selecting Primary UOM code',1);
68 END IF;
69 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
70 THEN
71 OE_MSG_PUB.Add_Exc_Msg
72 ( G_PKG_NAME, 'Reservable_Quantity');
73 END IF;
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75
76 END;
77 -- Getting reservable quantity
78 Query_Qty_Tree(p_ship_from_org_id => p_ship_from_org_id,
79 p_subinventory_code => p_subinventory,
80 p_inventory_item_id => p_inventory_item_id,
81 x_on_hand_qty => l_on_hand_qty,
82 x_avail_to_reserve => l_total_supply,
83 x_on_hand_qty2 => l_on_hand_qty2, -- INVCONV
84 x_avail_to_reserve2 => l_total_supply2 -- INVCONV
85 );
86
87 l_total_supply := l_total_supply - g_total_consumed;
88 x_primary_uom := l_primary_uom;
89 x_available_qty := l_total_supply;
90
91 -- INVCONV
92 l_total_supply2 := l_total_supply2 - g_total_consumed2;
93 x_available_qty2 := l_total_supply2;
94
95 IF l_debug_level > 0 THEN
96 oe_debug_pub.add( ' EXITING RESERVABLE_QUANTITY' , 1 ) ;
97 END IF;
98 EXCEPTION
99 WHEN OTHERS THEN
100
101 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
102 THEN
103 OE_MSG_PUB.Add_Exc_Msg
104 ( G_PKG_NAME,
105 'Reservable_Quantity');
106 END IF;
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108
109 END Reservable_Quantity;
110 /*------------------------------------------------------
111 Procedure Name : Commit_Reservation
112 Description : This api will call Do_Check_for_Commit api to check
113 reservation status before commitng the work and will
114 save errors if occured during reservation.
115 --------------------------------------------------------------------- */
116 PROCEDURE Commit_Reservation(p_request_id IN NUMBER,
117 x_return_status OUT NOCOPY VARCHAR2)
118 IS
119 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
120 l_msg_count NUMBER;
121 l_msg_data VARCHAR2(1000);
122 l_failed_rsv_temp_tbl INV_RESERVATION_GLOBAL.mtl_failed_rsv_tbl_type;
123 --
124 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
125 --
126
127 BEGIN
128 IF l_debug_level > 0 THEN
129 oe_debug_pub.add( ' BEFORE CALLING DO_CHECK_FOR_COMMIT' , 1 ) ;
130 END IF;
131 INV_RESERVATION_PVT.Do_Check_For_Commit
132 (p_api_version_number => 1.0
133 ,p_init_msg_lst => FND_API.G_FALSE
134 ,x_return_status => l_return_status
135 ,x_msg_count => l_msg_count
136 ,x_msg_data => l_msg_data
137 ,x_failed_rsv_temp_tbl => l_failed_rsv_temp_tbl);
138
139 IF l_debug_level > 0 THEN
140 oe_debug_pub.add( ' AFTER CALLING DO_CHECK_FOR_COMMIT:'||l_return_status , 1 ) ;
141 END IF;
142 -- Error Handling Start
143 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
144 IF l_debug_level > 0 THEN
145 oe_debug_pub.add( 'INSIDE UNEXPECTED ERROR' , 1 ) ;
146 END IF;
147 OE_MSG_PUB.Transfer_Msg_Stack;
148 l_msg_count := OE_MSG_PUB.COUNT_MSG;
149
150 FOR I IN 1..l_msg_count LOOP
151 l_msg_data := OE_MSG_PUB.Get(I,'F');
152
153 IF l_debug_level > 0 THEN
154 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
155 END IF;
156 END LOOP;
157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
158
159 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
160 IF l_debug_level > 0 THEN
161 oe_debug_pub.add( ' INSIDE EXPECTED ERROR' , 1 ) ;
162 END IF;
163 OE_MSG_PUB.Save_Messages(p_request_id => p_request_id);
164 RAISE FND_API.G_EXC_ERROR;
165 END IF;
166 x_return_status := l_return_status;
167 EXCEPTION
168 WHEN FND_API.G_EXC_ERROR THEN
169 IF l_debug_level > 0 THEN
170 OE_DEBUG_PUB.Add('In Expected Error...in Proc Commit_Reservation for rsv_tbl',1);
171 END IF;
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 IF l_debug_level > 0 THEN
175 OE_DEBUG_PUB.Add('In Unexpected Error...in Proc Commit_Reservation ',1 );
176 END IF;
177 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 WHEN OTHERS THEN
180 IF l_debug_level > 0 THEN
181 OE_DEBUG_PUB.Add('In others error...in Proc Commit_Reservation ',1);
182 END IF;
183 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
185 END Commit_Reservation;
186
187 /*------------------------------------------------------
188 Procedure Name : Validate_and_Reserve_for_Set
189 Description : This api will fetch corrected quantity of each line
190 and will call create_reservation with the
191 lines having corrected quantity > 0
192 --------------------------------------------------------------------- */
193 Procedure Validate_and_Reserve_for_Set
194 (p_x_rsv_tbl IN OUT NOCOPY OE_RESERVE_CONC.rsv_tbl_type,
195 p_reservation_set_id IN NUMBER,
196 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
197 IS
198
199 CURSOR rsv_set_line(p_line_id NUMBER) IS
200 SELECT inventory_item_id,
201 ordered_qty,
202 ordered_qty2, --INVCONV
203 ship_from_org_id,
204 subinventory,
205 corrected_qty,
206 corrected_qty2 -- INVCONV
207 FROM oe_rsv_set_details
208 WHERE reservation_set_id = p_reservation_set_id
209 AND line_id = p_line_id;
210
211 TYPE Rsv_set_rec_type IS RECORD
212 (inventory_item_id NUMBER
213 ,ordered_qty NUMBER
214 ,ordered_qty2 NUMBER -- INVCONV
215 ,ship_from_org_id NUMBER
216 ,subinventory VARCHAR2(10)
217 ,corrected_qty NUMBER
218 ,corrected_qty2 NUMBER -- INVCONV
219 );
220
221 l_rsv_set_rec Rsv_set_rec_type;
222 l_rsv_tbl OE_RESERVE_CONC.rsv_tbl_type;
223 l_return_status VARCHAR2(1);
224 l_line_reserve_qty NUMBER :=0;
225 l_line_reserve_qty2 NUMBER :=0; -- INVCONV
226 l_sales_order_id NUMBER;
227 l_count_header NUMBER :=0;
228 --
229 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
230 --
231 BEGIN
232 IF l_debug_level > 0 THEN
233 oe_debug_pub.add( 'ENTERING VALIDATE_AND_RESERVE_FOR_SET ' , 1 ) ;
234 END IF;
235
236 FOR I IN 1..p_x_rsv_tbl.COUNT
237 LOOP
238 l_count_header := l_count_header + 1;
239 IF l_count_header = 1 THEN
240 -- Set Message Context
241 OE_MSG_PUB.set_msg_context(
242 p_entity_code => 'LINE'
243 ,p_entity_id => p_x_rsv_tbl(I).line_id
244 ,p_header_id => p_x_rsv_tbl(I).header_id
245 ,p_line_id => p_x_rsv_tbl(I).line_id
246 ,p_order_source_id => p_x_rsv_tbl(I).order_source_id
247 ,p_orig_sys_document_ref => p_x_rsv_tbl(I).orig_sys_document_ref
248 ,p_orig_sys_document_line_ref => p_x_rsv_tbl(I).orig_sys_line_ref
249 ,p_orig_sys_shipment_ref => p_x_rsv_tbl(I).orig_sys_shipment_ref
250 ,p_change_sequence => p_x_rsv_tbl(I).change_sequence
251 ,p_source_document_type_id => p_x_rsv_tbl(I).source_document_type_id
252 ,p_source_document_id => p_x_rsv_tbl(I).source_document_id
253 ,p_source_document_line_id => p_x_rsv_tbl(I).source_document_line_id
254
255 );
256 ELSIF l_count_header > 1 THEN
257 -- Update Message Context
258 OE_MSG_PUB.update_msg_context(
259 p_entity_code => 'LINE'
260 ,p_entity_id => p_x_rsv_tbl(I).line_id
261 ,p_header_id => p_x_rsv_tbl(I).header_id
262 ,p_line_id => p_x_rsv_tbl(I).line_id
263 ,p_orig_sys_document_ref => p_x_rsv_tbl(I).orig_sys_document_ref
264 ,p_orig_sys_document_line_ref => p_x_rsv_tbl(I).orig_sys_line_ref
265 ,p_change_sequence => p_x_rsv_tbl(I).change_sequence
266 ,p_source_document_id => p_x_rsv_tbl(I).source_document_id
267 ,p_source_document_line_id => p_x_rsv_tbl(I).source_document_line_id
268 ,p_order_source_id => p_x_rsv_tbl(I).order_source_id
269 ,p_source_document_type_id => p_x_rsv_tbl(I).source_document_type_id
270 );
271 END IF;
272
273 -- Getting set record information
274 OPEN rsv_set_line(p_x_rsv_tbl(I).line_id);
275 FETCH rsv_set_line INTO l_rsv_set_rec;
276 CLOSE rsv_set_line;
277 -- comparing item information
278 IF NOT OE_GLOBALS.EQUAL(p_x_rsv_tbl(I).inventory_item_id, l_rsv_set_rec.inventory_item_id)
279 OR NOT OE_GLOBALS.EQUAL(p_x_rsv_tbl(I).ordered_qty, l_rsv_set_rec.ordered_qty)
280 OR NOT OE_GLOBALS.EQUAL(p_x_rsv_tbl(I).ship_from_org_id, l_rsv_set_rec.ship_from_org_id)
281 OR NOT OE_GLOBALS.EQUAL(p_x_rsv_tbl(I).subinventory, l_rsv_set_rec.subinventory)
282 OR NOT OE_GLOBALS.EQUAL(p_x_rsv_tbl(I).ordered_qty2, l_rsv_set_rec.ordered_qty2) -- INVCONV from code review by AK
283 OR nvl(p_x_rsv_tbl(I).shipped_quantity,0)> 0 THEN
284 -- Save error message
285 fnd_message.set_name('ONT', 'OE_SCH_ITEM_NOT_RESERVABLE');
286 OE_MSG_PUB.Add;
287 FND_FILE.Put_Line(FND_FILE.LOG,'Item can not reserved: Simulation information differ from line information');
288 ELSE
289 IF NVL(l_rsv_set_rec.corrected_qty,0) > 0 THEN
290
291 p_x_rsv_tbl(I).Derived_Reserved_Qty := l_rsv_set_rec.corrected_qty;
292 p_x_rsv_tbl(I).Derived_Reserved_Qty2 := l_rsv_set_rec.corrected_qty2; -- INVCONV
293 p_x_rsv_tbl(I).corrected_reserved_qty := l_rsv_set_rec.corrected_qty;
294 p_x_rsv_tbl(I).corrected_reserved_qty2 := l_rsv_set_rec.corrected_qty2; -- INVCONV
295 -- Getting reserved quantity if any
296 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(p_x_rsv_tbl(I).HEADER_ID);
297 l_line_reserve_qty := OE_LINE_UTIL.Get_Reserved_Quantity
298 (p_header_id => l_sales_order_id,
299 p_line_id => p_x_rsv_tbl(I).line_id,
300 p_org_id => p_x_rsv_tbl(I).ship_from_org_id);
301
302
303
304 IF (NVL(p_x_rsv_tbl(I).Derived_Reserved_Qty,0)+ NVL(l_line_reserve_qty,0))
305 <= p_x_rsv_tbl(I).ordered_qty
306 AND NVL(p_x_rsv_tbl(I).Derived_Reserved_Qty,0) > 0 THEN
307 -- Partial Reservation
308 --Reservation exists for the line. Set the flag
309 IF l_line_reserve_qty > 0 THEN
310 p_x_rsv_tbl(I).reservation_exists := 'Y';
311 END IF;
312 l_rsv_tbl(l_rsv_tbl.COUNT+1) := p_x_rsv_tbl(I);
313 ELSE
314 -- Save error message
315 fnd_message.set_name('ONT', 'OE_SCH_ITEM_NOT_RESERVABLE');
316 OE_MSG_PUB.Add;
317 FND_FILE.Put_Line(FND_FILE.LOG,'Item can not reserved: Simulation is greater than the remaining reservable quantity');
318 END IF;
319 END IF;
320 END IF;
321 END LOOP;
322 oe_debug_pub.add('Before calling Create Reservation: ' || l_rsv_tbl.count,1);
323 create_reservation(p_x_rsv_tbl => l_rsv_tbl
324 ,p_partial_reservation => FND_API.G_FALSE
325 ,x_return_status => l_return_status);
326 x_return_status := l_return_status;
327 IF l_debug_level > 0 THEN
328 oe_debug_pub.add( 'EXITING VALIDATE_AND_RESERVE_FOR_SET: '||l_return_status , 1 ) ;
329 END IF;
330 EXCEPTION
331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
332 IF l_debug_level > 0 THEN
333 OE_DEBUG_PUB.Add('In Unexpected Error...in Proc Validate_and_Reserve_for_Set ',1 );
334 END IF;
335 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
337 WHEN OTHERS THEN
338 IF l_debug_level > 0 THEN
339 OE_DEBUG_PUB.Add('In others error...in Proc Validate_and_Reserve_for_Set ',1);
340 END IF;
341 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 END Validate_and_Reserve_for_Set;
344
345 /*------------------------------------------------------
346 Procedure Name : Update_Reservation_Set
347 Description : To update the process_flag of the processed set
348 --------------------------------------------------------------------- */
349 Procedure Update_Reservation_Set
350 (p_reservation_set_id IN NUMBER,
351 x_return_status OUT NOCOPY VARCHAR2)
352 IS
353 l_request_id NUMBER;
354 --
355 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
356 --
357 BEGIN
358 IF l_debug_level > 0 THEN
359 oe_debug_pub.add( 'ENTERING UPDATE_RESERVATION_SET ' , 1 ) ;
360 END IF;
361
362 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
363 UPDATE oe_reservation_sets
364 SET process_flag = 'Y',
365 reservation_request_id = l_request_id,
366 program_update_date = sysdate,
367 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
368 last_updated_by = FND_GLOBAL.USER_ID,
369 last_update_date = sysdate
370 WHERE reservation_set_id = p_reservation_set_id;
371 IF (SQL%NOTFOUND) THEN
372 x_return_status := FND_API.G_RET_STS_ERROR;
373 ELSE
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375 END IF;
376 IF l_debug_level > 0 THEN
377 oe_debug_pub.add( 'EXITING UPDATE_RESERVATION_SET ' , 1 ) ;
378 END IF;
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 IF l_debug_level > 0 THEN
383 OE_DEBUG_PUB.Add('In others error...in Proc Update_Reservation_Set for rsv set tbl',1);
384 END IF;
385 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
387 END Update_Reservation_Set;
388
389
390 /*------------------------------------------------------
391 Procedure Name : Create_Reservation_Set
392 Description : Inserts simulated or reserved records
393 into oe_reservation_sets and oe_rsv_set_details table.
394 --------------------------------------------------------------------- */
395 Procedure Create_Reservation_Set(p_rsv_tbl IN OE_RESERVE_CONC.Rsv_Tbl_Type,
396 p_reserve_set_name IN VARCHAR2,
397 p_rsv_request_id IN NUMBER DEFAULT NULL,
398 p_simulation_request_id IN NUMBER DEFAULT NULL,
399 x_return_status OUT NOCOPY VARCHAR2)
400 IS
401 l_set_id NUMBER;
402 l_delete_set_id NUMBER;
403 l_process_flag VARCHAR2(1) :='N';
404 l_line_count NUMBER :=0;
405 --
406 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
407 --
408 BEGIN
409 IF l_debug_level > 0 THEN
410 oe_debug_pub.add( 'ENTERING CREATE_RESERVATION_SET ' , 1 ) ;
411 END IF;
412
413 IF g_set_id IS NULL THEN
414 g_program_application_id := Fnd_global.PROG_APPL_ID;
415 g_program_id := Fnd_Global.Conc_Program_Id;
416
417 -- Deleting records from table for the given set name.
418 BEGIN
419 SELECT reservation_set_id into l_set_id
420 FROM oe_reservation_sets
421 WHERE reservation_set_name = P_reserve_set_name;
422 g_set_id := l_set_id;
423 -- Deleting the existing records.
424 DELETE oe_rsv_set_details
425 WHERE reservation_set_id = l_set_id;
426
427 DELETE oe_reservation_sets
428 WHERE reservation_set_id = l_set_id;
429
430 EXCEPTION
431 WHEN OTHERS THEN
432 NULL;
433 END;
434 IF g_set_id IS NULL THEN
435 SELECT oe_reservation_sets_s.nextval INTO g_set_id FROM dual;
436 END IF;
437
438 IF p_rsv_request_id IS NOT NULL THEN
439 l_process_flag := 'Y';
440 END IF;
441 IF l_debug_level > 0 THEN
442 oe_debug_pub.add( 'Reservation_Set_Id: '||g_set_id , 1 ) ;
443 END IF;
444 -- Insert Header information
445 Insert Into oe_reservation_sets
446 (Reservation_Set_Id
447 ,Reservation_Set_Name
448 ,Reservation_request_id
449 ,process_Flag
450 ,Simulation_Request_id
451 ,Creation_date
452 ,created_by
453 ,program_update_date
454 ,program_application_id
455 ,program_id
456 ,last_update_login
457 ,last_updated_by
458 ,last_update_date)
459 Values
460 ( g_set_id
461 , P_reserve_set_name
462 , p_rsv_request_id
463 , l_process_flag
464 , p_simulation_request_id
465 , sysdate
466 , FND_GLOBAL.USER_ID
467 , sysdate
468 , g_program_application_id
469 , g_program_id
470 , FND_GLOBAL.CONC_LOGIN_ID
471 , FND_GLOBAL.USER_ID
472 , sysdate);
473
474 END IF;
475 -- Insert Detail Information
476 FOR I IN 1..p_rsv_tbl.COUNT
477 LOOP
478 l_line_count := l_line_count + 1;
479
480 INSERT INTO oe_rsv_set_details
481 (Reservation_set_id
482 ,Line_id
483 ,header_id
484 ,inventory_item_id
485 ,ordered_qty
486 ,ordered_qty2 -- INVCONV
487 ,ordered_qty_uom
488 ,ordered_qty_uom2 -- INVCONV
489 ,derived_qty
490 ,derived_qty2 -- INVCONV
491 ,derived_qty_uom
492 ,corrected_qty
493 ,corrected_qty2 -- INVCONV
494 ,ship_from_org_id
495 ,subinventory
496 ,creation_date
497 ,created_by
498 ,last_update_date
499 ,last_updated_by
500 ,last_update_login
501 ,program_application_id
502 ,program_id
503 ,program_update_date)
504 Values
505 (g_set_id
506 ,p_rsv_tbl(I).line_id
507 ,p_rsv_tbl(I).header_id
508 ,p_rsv_tbl(I).inventory_item_id
509 ,p_rsv_tbl(I).ordered_qty
510 ,p_rsv_tbl(I).ordered_qty2 -- INVCONV
511 ,p_rsv_tbl(I).ordered_qty_uom
512 ,p_rsv_tbl(I).ordered_qty_uom2 -- INVCONV
513 ,p_rsv_tbl(I).derived_reserved_qty
514 ,p_rsv_tbl(I).derived_reserved_qty2 -- INVCONV
515 ,p_rsv_tbl(I).reserved_qty_UOM
516 ,NVL(p_rsv_tbl(I).corrected_reserved_qty,p_rsv_tbl(I).derived_reserved_qty)
517 ,NVL(p_rsv_tbl(I).corrected_reserved_qty2,p_rsv_tbl(I).derived_reserved_qty2) -- INVCONV
518 ,p_rsv_tbl(I).ship_from_org_id
519 ,p_rsv_tbl(I).subinventory
520 ,sysdate
521 ,FND_GLOBAL.USER_ID
522 ,sysdate
523 ,FND_GLOBAL.USER_ID
524 ,FND_GLOBAL.CONC_LOGIN_ID
525 ,g_program_application_id
526 ,g_program_id
527 ,sysdate);
528 IF l_line_count = 500 THEN
529 COMMIT;
530 l_line_count := 0;
531 END IF;
532
533 END LOOP;
534
535 IF l_debug_level > 0 THEN
536 oe_debug_pub.add( 'EXITING CREATE_RESERVATION_SET ' , 1 ) ;
537 END IF;
538 x_return_status := FND_API.G_RET_STS_SUCCESS;
539 EXCEPTION
540 WHEN OTHERS THEN
541 IF l_debug_level > 0 THEN
542 OE_DEBUG_PUB.Add('In others error...in Proc Create_Reservation_Set ',1);
543 END IF;
544 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
546 END Create_Reservation_Set;
547
548 /*------------------------------------------------------
549 Procedure Name : Calculate_Partial_Quantity
550 Description : calculate quantity to be reserved based on reservation mode.
551 If mode is "Only unreserved lines" then derived quantity
552 will be the ordered quantity, otherwise it will be difference
553 of ordered quantity and quantity already reserved
554 --------------------------------------------------------------------- */
555 Procedure Calculate_Partial_Quantity
556 ( p_x_rsv_tbl IN OUT NOCOPY rsv_tbl_type,
557 p_reservation_mode IN VARCHAR2,
558 x_return_status OUT NOCOPY VARCHAR2)
559 IS
560
561 l_sales_order_id NUMBER;
562 l_return_status VARCHAR2(1);
563 l_reserved_quantity NUMBER :=0;
564 l_reserved_quantity2 NUMBER :=0; -- INVCONV
565 l_primary_uom VARCHAR2(3);
566 l_total_supply NUMBER:=0;
567 l_total_supply2 NUMBER:=0; -- INVCONV
568 --4695715
569 l_total_conv_supply NUMBER:=0;
570 l_total_conv_supply2 NUMBER:=0; -- INVCONV
571 l_temp NUMBER:=0;
572 --
573 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
574 --
575 BEGIN
576 IF l_debug_level > 0 THEN
577 oe_debug_pub.add( 'ENTERING CALCULATE_PARTIAL_QUANTITY ' , 1 ) ;
578 END IF;
579 Reservable_Quantity(p_inventory_item_id => p_x_rsv_tbl(1).inventory_item_id,
580 p_ship_from_org_id => p_x_rsv_tbl(1).ship_from_org_id,
581 p_subinventory => p_x_rsv_tbl(1).subinventory,
582 p_org_id => p_x_rsv_tbl(1).org_id, -- 4759251
583 x_primary_uom => l_primary_uom,
584 x_available_qty => l_total_supply,
585 x_available_qty2 => l_total_supply2 -- INVCONV
586 );
587
588
589 FOR I IN 1..p_x_rsv_tbl.COUNT
590 LOOP
591 -- 4695715 : Start
592 l_temp :=0;
593 IF NOT OE_GLOBALS.Equal(p_x_rsv_tbl(I).ordered_qty_UOM, l_primary_uom)
594 THEN
595 IF l_debug_level > 0 THEN
596 oe_debug_pub.add( 'Before UOM convertion :' || l_total_supply || '/' || l_primary_uom, 1 ) ;
597 END IF;
598 l_total_conv_supply :=
599 INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
600 precision => 5,
601 from_quantity => l_total_supply,
602 from_unit => l_primary_uom,
603 to_unit => p_x_rsv_tbl(I).ordered_qty_uom,
604 from_name => NULL,
605 to_name => NULL
606 );
607 l_total_conv_supply2 :=
608 INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
609 precision => 5,
610 from_quantity => l_total_supply2,
611 from_unit => l_primary_uom,
612 to_unit => p_x_rsv_tbl(I).ordered_qty_uom,
613 from_name => NULL,
614 to_name => NULL
615 );
616
617 --l_primary_uom := p_x_rsv_tbl(I).ordered_qty_uom;
618 IF l_debug_level > 0 THEN
619 oe_debug_pub.add( 'After UOM convertion :' || l_total_conv_supply || '/' || p_x_rsv_tbl(I).ordered_qty_uom, 1 ) ;
620 oe_debug_pub.add( 'Total Supply2 :' || l_total_conv_supply2 || '/' || p_x_rsv_tbl(I).ordered_qty_uom, 1 ) ;
621 END IF;
622 ELSE
623 l_total_conv_supply := l_total_supply;
624 l_total_conv_supply2 := l_total_supply2;
625 END IF;
626 -- 4695715 : End
627 IF p_reservation_mode = 'PARTIAL_ONLY_UNRESERVED' THEN
628 p_x_rsv_tbl(I).derived_reserved_qty :=
629 p_x_rsv_tbl(I).ordered_qty;
630 p_x_rsv_tbl(I).derived_reserved_qty2 := -- INVCONV
631 nvl(p_x_rsv_tbl(I).ordered_qty2,0);
632
633 IF l_total_conv_supply > 0
634 AND p_x_rsv_tbl(I).derived_reserved_qty > l_total_conv_supply THEN
635 p_x_rsv_tbl(I).derived_reserved_qty := l_total_conv_supply;
636 END IF;
637 IF l_total_conv_supply2 > 0
638 AND p_x_rsv_tbl(I).derived_reserved_qty2 > l_total_conv_supply2 THEN --INVCONV
639 p_x_rsv_tbl(I).derived_reserved_qty2 := l_total_conv_supply2;
640 END IF;
641
642 --l_total_supply := l_total_supply - p_x_rsv_tbl(I).derived_reserved_qty;
643 -- 4695715 : Start
644 IF OE_GLOBALS.Equal(l_primary_uom ,p_x_rsv_tbl(I).ordered_qty_uom) THEN
645 g_consumed_for_lot := g_consumed_for_lot + p_x_rsv_tbl(I).derived_reserved_qty;
646 l_total_supply := l_total_supply - p_x_rsv_tbl(I).derived_reserved_qty;
647 ELSE
648 l_temp := INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
649 precision => 5,
650 from_quantity => p_x_rsv_tbl(I).derived_reserved_qty,
651 from_unit => p_x_rsv_tbl(I).ordered_qty_uom,
652 to_unit => l_primary_uom,
653 from_name => NULL,
654 to_name => NULL
655 );
656 g_consumed_for_lot := g_consumed_for_lot + l_temp;
657 l_total_supply := l_total_supply - l_temp;
658 END IF;
659 -- 4695715 : End
660 p_x_rsv_tbl(I).reserved_qty_UOM :=
661 p_x_rsv_tbl(I).ordered_qty_UOM;
662 -- 4695715 : Start
663 --l_total_supply2 := l_total_supply2 - nvl(p_x_rsv_tbl(I).derived_reserved_qty2,0); -- INVCONV
664 l_temp :=0;
665 IF nvl(p_x_rsv_tbl(I).derived_reserved_qty2,0) > 0 THEN
666 IF OE_GLOBALS.Equal(l_primary_uom ,p_x_rsv_tbl(I).ordered_qty_uom) THEN
667 g_consumed_for_lot2 := g_consumed_for_lot2 + nvl(p_x_rsv_tbl(I).derived_reserved_qty2,0); -- INVCONV
668 l_total_supply2 := l_total_supply2 - nvl(p_x_rsv_tbl(I).derived_reserved_qty2,0); -- INVCONV
669 ELSE
670 l_temp := INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
671 precision => 5,
672 from_quantity => p_x_rsv_tbl(I).derived_reserved_qty2,
673 from_unit => p_x_rsv_tbl(I).ordered_qty_uom,
674 to_unit => l_primary_uom,
675 from_name => NULL,
676 to_name => NULL
677 );
678 g_consumed_for_lot2 := g_consumed_for_lot2 + l_temp;
679 l_total_supply2 := l_total_supply2 - l_temp;
680 END IF;
681
682 END IF;
683 -- 4695715 : End
684 ELSE
685 /* -- 6814153 : moved to procedure Reserve
686 l_reserved_quantity :=0;
687 l_reserved_quantity2 :=0; -- INVCONV
688 l_sales_order_id :=
689 Oe_Schedule_Util.Get_mtl_sales_order_id(p_x_rsv_tbl(I).HEADER_ID);
690 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
691
692 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
693 ,p_line_id => p_x_rsv_tbl(I).line_id
694 ,p_org_id => p_x_rsv_tbl(I).ship_from_org_id
695 ,x_reserved_quantity => l_reserved_quantity
696 ,x_reserved_quantity2 => l_reserved_quantity2
697 );
698
699
700 l_reserved_quantity := OE_LINE_UTIL.Get_Reserved_Quantity
701 (p_header_id => l_sales_order_id,
702 p_line_id => p_x_rsv_tbl(I).line_id,
703 p_org_id => p_x_rsv_tbl(I).ship_from_org_id);
704
705 l_reserved_quantity2 := OE_LINE_UTIL.Get_Reserved_Quantity2 -- INVCONV
706 (p_header_id => l_sales_order_id,
707 p_line_id => p_x_rsv_tbl(I).line_id,
708 p_org_id => p_x_rsv_tbl(I).ship_from_org_id);
709
710 -- Derive the quantity to be reserved
711 p_x_rsv_tbl(I).derived_reserved_qty
712 := p_x_rsv_tbl(I).ordered_qty - NVL(l_reserved_quantity,0);
713 p_x_rsv_tbl(I).derived_reserved_qty2 -- INVCONV
714 := p_x_rsv_tbl(I).ordered_qty2 - NVL(l_reserved_quantity2,0);
715 IF l_debug_level > 0 THEN
716 oe_debug_pub.add( 'Derived Reserved Qty: '||p_x_rsv_tbl(I).derived_reserved_qty , 1 ) ;
717 END IF;
718 -- Partial Reservation
719 -- Reservation exists for the line. Set the flag
720 IF l_reserved_quantity > 0 THEN
721 p_x_rsv_tbl(I).reservation_exists := 'Y';
722 END IF;
723 */
724 IF l_total_conv_supply > 0
725 AND p_x_rsv_tbl(I).derived_reserved_qty > l_total_conv_supply THEN
726 p_x_rsv_tbl(I).derived_reserved_qty := l_total_conv_supply;
727 ELSIF l_total_conv_supply <=0 THEN ---6814153
728 p_x_rsv_tbl(I).derived_reserved_qty := 0;
729 END IF;
730 IF l_total_conv_supply2 > 0
731 AND p_x_rsv_tbl(I).derived_reserved_qty2 > l_total_conv_supply2 THEN
732 p_x_rsv_tbl(I).derived_reserved_qty2 := l_total_conv_supply2; -- INVCONV
733 ELSIF l_total_conv_supply2 <=0 THEN ---6814153
734 p_x_rsv_tbl(I).derived_reserved_qty2 := 0;
735 END IF;
736
737 --4695715 : Start
738 l_temp :=0;
739 --l_total_supply := l_total_supply - p_x_rsv_tbl(I).derived_reserved_qty;
740 IF OE_GLOBALS.Equal(l_primary_uom ,p_x_rsv_tbl(I).ordered_qty_uom) THEN
741 g_consumed_for_lot := g_consumed_for_lot + p_x_rsv_tbl(I).derived_reserved_qty;
742 l_total_supply := l_total_supply - p_x_rsv_tbl(I).derived_reserved_qty;
743 ELSE
744 l_temp := INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
745 precision => 5,
746 from_quantity => p_x_rsv_tbl(I).derived_reserved_qty,
747 from_unit => p_x_rsv_tbl(I).ordered_qty_uom,
748 to_unit => l_primary_uom,
749 from_name => NULL,
750 to_name => NULL
751 );
752 g_consumed_for_lot := g_consumed_for_lot + l_temp;
753 l_total_supply := l_total_supply - l_temp;
754 END IF;
755 -- 4695715 : End
756 p_x_rsv_tbl(I).reserved_qty_UOM := p_x_rsv_tbl(I).ordered_qty_UOM;
757
758 -- 4695715 : Start
759 l_temp :=0;
760 --l_total_supply2 := l_total_supply2 - nvl(p_x_rsv_tbl(I).derived_reserved_qty2,0); -- INVCONV
761
762 IF nvl(p_x_rsv_tbl(I).derived_reserved_qty2,0) > 0 THEN
763 IF OE_GLOBALS.Equal(l_primary_uom ,p_x_rsv_tbl(I).ordered_qty_uom) THEN
764 g_consumed_for_lot2 := g_consumed_for_lot2 + nvl(p_x_rsv_tbl(I).derived_reserved_qty2,0); -- INVCONV
765 l_total_supply2 := l_total_supply2 - nvl(p_x_rsv_tbl(I).derived_reserved_qty2,0); -- INVCONV
766 ELSE
767 l_temp := INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
768 precision => 5,
769 from_quantity => p_x_rsv_tbl(I).derived_reserved_qty2,
770 from_unit => p_x_rsv_tbl(I).ordered_qty_uom,
771 to_unit => l_primary_uom,
772 from_name => NULL,
773 to_name => NULL
774 );
775 g_consumed_for_lot2 := g_consumed_for_lot2 + l_temp;
776 l_total_supply2 := l_total_supply2 - l_temp; -- INVCONV
777
778 END IF;
779 END IF;
780 --4695715 : End
781 END IF;
782 -- Keeping derived quantity
783 p_x_rsv_tbl(I).derived_reserved_qty_mir := p_x_rsv_tbl(I).derived_reserved_qty;
784 p_x_rsv_tbl(I).derived_reserved_qty2_mir := p_x_rsv_tbl(I).derived_reserved_qty2; -- INVCONV
785 --5041136
786 p_x_rsv_tbl(I).corrected_reserved_qty := p_x_rsv_tbl(I).derived_reserved_qty;
787 p_x_rsv_tbl(I).corrected_reserved_qty2 := p_x_rsv_tbl(I).derived_reserved_qty2;
788 END LOOP;
789 IF l_debug_level > 0 THEN
790 oe_debug_pub.add( 'EXITING CALCULATE_PARTIAL_QUANTITY ' , 1 ) ;
791 END IF;
792
793 EXCEPTION
794 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
795 IF l_debug_level > 0 THEN
796 OE_DEBUG_PUB.Add('In Unexpected Error...in Proc Calculate_Partial_Quantity ',1 );
797 END IF;
798 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800 WHEN OTHERS THEN
801 IF l_debug_level > 0 THEN
802 OE_DEBUG_PUB.Add('In others error...in Proc Calculate_Partial_Quantity ',1);
803 END IF;
804 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
806 END Calculate_Partial_Quantity;
807
808 /*-------------------------------------------------------------------
809 Procedure Name : Query_Qty_Tree
810 Description : Queries the On-Hand and Available to Reserve
811 quantites by calling INV's
812 inv_quantity_tree_pub.query_quantities.
813 The quantities are given at the highest level
814 (Item, Org, Subinventory combination).
815 --------------------------------------------------------------------- */
816 Procedure Query_Qty_Tree(p_ship_from_org_id IN NUMBER,
817 p_subinventory_code IN VARCHAR2,
818 p_inventory_item_id IN NUMBER,
819 x_on_hand_qty OUT NOCOPY NUMBER,
820 x_avail_to_reserve OUT NOCOPY NUMBER,
821 x_on_hand_qty2 OUT NOCOPY NUMBER, -- INVCONV
822 x_avail_to_reserve2 OUT NOCOPY NUMBER -- INVCONV
823 )
824
825 IS
826 --
827 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
828 --
829 l_return_status VARCHAR2(1);
830 l_msg_count NUMBER;
831 l_msg_data VARCHAR2(2000);
832 l_qoh NUMBER;
833 l_rqoh NUMBER;
834 l_msg_index NUMBER;
835 l_lot_control_flag BOOLEAN;
836 l_lot_control_code NUMBER;
837 l_qr NUMBER;
838 l_qs NUMBER;
839 l_att NUMBER;
840 l_atr NUMBER;
841
842
843 l_sqoh NUMBER; -- INVCONV
844 l_srqoh NUMBER; -- INVCONV
845 l_sqr NUMBER; -- INVCONV
846 l_sqs NUMBER; -- INVCONV
847 l_satt NUMBER; -- INVCONV
848 l_satr NUMBER; -- INVCONV
849
850 BEGIN
851
852 IF l_debug_level > 0 THEN
853 oe_debug_pub.add( 'ENTERING QUERY_QTY_TREE ' , 1 ) ;
854 END IF;
855
856 BEGIN
857
858 SELECT msi.lot_control_code
859 INTO l_lot_control_code
860 FROM mtl_system_items msi
861 WHERE msi.inventory_item_id = p_inventory_item_id
862 AND msi.organization_id = p_ship_from_org_id;
863
864 IF l_lot_control_code = 2 THEN
865 l_lot_control_flag := TRUE;
866 ELSE
867 l_lot_control_flag := FALSE;
868 END IF;
869
870 EXCEPTION
871 WHEN OTHERS THEN
872 l_lot_control_flag := FALSE;
873 END;
874
875 --inv_quantity_tree_pvt.clear_quantity_cache;
876 inv_quantity_tree_pvt.mark_all_for_refresh
877 ( p_api_version_number => 1.0
878 , p_init_msg_lst => FND_API.G_TRUE
879 , x_return_status => l_return_status
880 , x_msg_count => l_msg_count
881 , x_msg_data => l_msg_data
882 );
883
884 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
885 oe_msg_pub.transfer_msg_stack;
886 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
887 for I in 1..l_msg_count loop
888 l_msg_data := OE_MSG_PUB.Get(I,'F');
889 IF l_debug_level > 0 THEN
890 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
891 END IF;
892 end loop;
893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
894 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
895 oe_msg_pub.transfer_msg_stack;
896 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
897 for I in 1..l_msg_count loop
898 l_msg_data := OE_MSG_PUB.Get(I,'F');
899 IF l_debug_level > 0 THEN
900 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
901 END IF;
902 end loop;
903 RAISE FND_API.G_EXC_ERROR;
904 END IF;
905
906
907
908 inv_quantity_tree_pub.query_quantities
909 ( p_api_version_number => 1.0
910 , x_return_status => l_return_status
911 , x_msg_count => l_msg_count
912 , x_msg_data => l_msg_data
913 , p_organization_id => p_ship_from_org_id
914 , p_inventory_item_id => p_inventory_item_id
915 , p_tree_mode => 2
916 , p_is_revision_control => false
917 , p_is_lot_control => l_lot_control_flag
918 , p_lot_expiration_date => sysdate
919 , p_is_serial_control => false
920 , p_grade_code => NULL -- INVCONV NOT NEEDED NOW
921 , p_revision => null
922 , p_lot_number => null
923 , p_subinventory_code => p_subinventory_code
924 , p_locator_id => null
925 , x_qoh => l_qoh
926 , x_rqoh => l_rqoh
927 , x_qr => l_qr
928 , x_qs => l_qs
929 , x_att => l_att
930 , x_atr => l_atr
931 , x_sqoh => l_sqoh -- INVCONV
932 , x_srqoh => l_srqoh -- INVCONV
933 , x_sqr => l_sqr -- INVCONV
934 , x_sqs => l_sqs -- INVCONV
935 , x_satt => l_satt -- INVCONV
936 , x_satr => l_satr -- INVCONV
937 );
938
939 IF l_debug_level > 0 THEN
940 oe_debug_pub.add( 'AFTER CALLING QUERY_QUANTITIES' , 1 ) ;
941 oe_debug_pub.add( 'RR: L_QOH ' || L_QOH , 1 ) ;
942 oe_debug_pub.add( 'RR: L_QOH ' || L_ATR , 1 ) ;
943 END IF;
944 x_on_hand_qty := l_qoh;
945 x_avail_to_reserve := l_atr;
946 x_on_hand_qty2 := l_sqoh; -- INVCONV
947 x_avail_to_reserve2 := l_satr; -- INVCONV
948
949 FND_FILE.Put_Line(FND_FILE.LOG, 'Quantity on Hand = '||
950 x_on_hand_qty);
951 FND_FILE.Put_Line(FND_FILE.LOG, 'Quantity available to reserve = '||
952 x_avail_to_reserve);
953 IF l_debug_level > 0 THEN
954 oe_debug_pub.add( 'EXITING QUERY_QTY_TREE ' , 1 ) ;
955 END IF;
956
957 EXCEPTION
958
959 WHEN OTHERS THEN
960
961 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
962 THEN
963 OE_MSG_PUB.Add_Exc_Msg
964 ( G_PKG_NAME,
965 'Query_Qty_Tree');
966 END IF;
967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
968 END Query_Qty_Tree;
969 /*----------------------------------------------------------------
970 PROCEDURE : Calculate_Percentage
971 DESCRIPTION: This Procedure is to determine the percengae when program
972 is running in "Fair Share" mode.
973 ----------------------------------------------------------------*/
974 Procedure Calculate_Percentage
975 ( p_inventory_item_id IN NUMBER,
976 p_ship_from_org_id IN NUMBER,
977 p_subinventory IN VARCHAR2,
978 p_rsv_tbl IN OE_RESERVE_CONC.rsv_tbl_type,
979 x_percentage OUT NOCOPY /* file.sql.39 change */ NUMBER,
980 x_primary_uom OUT NOCOPY VARCHAR2
981 )
982 IS
983 l_total_demand NUMBER :=0;
984 l_total_supply NUMBER :=0;
985 l_primary_uom VARCHAR2(3);
986 l_converted_qty NUMBER :=0;
987 l_on_hand_qty NUMBER;
988
989 l_on_hand_qty2 NUMBER; -- INVCONV
990 l_avail_to_reserve2 NUMBER; -- INVCONV
991
992
993 --
994 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
995 --
996 BEGIN
997 IF l_debug_level > 0 THEN
998 OE_DEBUG_PUB.Add('Inside Calculate Percentage Procedure',1);
999 END IF;
1000 -- Getting the Primary UOM of the item
1001 BEGIN
1002 SELECT primary_uom_code
1003 INTO l_primary_uom
1004 FROM mtl_system_items_b
1005 WHERE inventory_item_id = p_inventory_item_id
1006 AND organization_id = NVL(p_ship_from_org_id,
1007 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID',p_rsv_tbl(1).org_id)); --4759251
1008 EXCEPTION
1009 WHEN OTHERS THEN
1010 IF l_debug_level > 0 THEN
1011 OE_DEBUG_PUB.Add('Error in selecting Primary UOM code',1);
1012 END IF;
1013 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1014 THEN
1015 OE_MSG_PUB.Add_Exc_Msg
1016 ( G_PKG_NAME,
1017 'Calculate_Percentage');
1018 END IF;
1019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1020
1021 END;
1022 -- Getting the ordered quantity
1023 IF l_debug_level > 0 THEN
1024 OE_DEBUG_PUB.Add('Calculate Percentage Table Count '||p_rsv_tbl.COUNT,1);
1025 END IF;
1026 FOR I IN 1..p_rsv_tbl.COUNT
1027 LOOP
1028
1029 IF OE_GLOBALS.Equal(p_rsv_tbl(I).ordered_qty_uom, l_primary_uom)
1030 THEN
1031 l_total_demand := l_total_demand + p_rsv_tbl(I).ordered_qty;
1032 ELSE
1033 l_converted_qty :=
1034 INV_CONVERT.INV_UM_CONVERT( item_id => p_inventory_item_id,
1035 precision => 5,
1036 from_quantity => p_rsv_tbl(I).ordered_qty,
1037 from_unit => p_rsv_tbl(I).ordered_qty_uom,
1038 to_unit => l_primary_uom,
1039 from_name => NULL,
1040 to_name => NULL
1041 );
1042 l_total_demand := l_total_demand + l_converted_qty;
1043
1044 END IF;
1045 END LOOP;
1046 IF l_debug_level > 0 THEN
1047 OE_DEBUG_PUB.Add('Calculate Percentage Total demand '||l_total_demand,1);
1048 END IF;
1049 -- Getting reservable quantity
1050 IF l_total_demand > 0
1051 THEN
1052 Query_Qty_Tree(p_ship_from_org_id => p_ship_from_org_id,
1053 p_subinventory_code => p_subinventory,
1054 p_inventory_item_id => p_inventory_item_id,
1055 x_on_hand_qty => l_on_hand_qty,
1056 x_avail_to_reserve => l_total_supply,
1057 x_on_hand_qty2 => l_on_hand_qty2, -- INVCONV
1058 x_avail_to_reserve2 => l_avail_to_reserve2 -- INVCONV
1059 );
1060
1061 END IF;
1062 l_total_supply := l_total_supply - g_total_consumed;
1063 IF l_debug_level > 0 THEN
1064 OE_DEBUG_PUB.Add('Calculate Percentage Total Supply '||l_total_Supply,1);
1065 END IF;
1066 -- Percent calculation
1067 IF (l_total_demand = 0
1068 OR l_total_supply = 0)
1069 THEN
1070 x_percentage := 0;
1071 ELSIF l_total_demand <= l_total_supply
1072 THEN
1073 x_percentage := 100;
1074 ELSE
1075 x_percentage := TRUNC((l_total_supply / l_total_demand) * 100, 5);
1076 END IF;
1077 -- 4695715
1078 x_primary_uom := l_primary_uom;
1079
1080 IF l_debug_level > 0 THEN
1081 OE_DEBUG_PUB.Add('Exiting Calculate Percentage '||x_percentage,1);
1082 END IF;
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085
1086 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1087 THEN
1088 OE_MSG_PUB.Add_Exc_Msg
1089 ( G_PKG_NAME,
1090 'Calculate_Percentage');
1091 END IF;
1092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1093
1094
1095 END calculate_Percentage;
1096
1097 /*----------------------------------------------------------------
1098 PROCEDURE : Derive_Reservation_qty
1099 DESCRIPTION: This Procedure is to derive reservation qty for each line
1100 based on the percentage passed or derived.
1101 ----------------------------------------------------------------*/
1102 Procedure Derive_Reservation_qty
1103 ( p_x_rsv_tbl IN OUT NOCOPY rsv_tbl_type,
1104 p_percentage IN NUMBER,
1105 p_reservation_mode IN VARCHAR2 DEFAULT NULL,
1106 p_primary_uom IN VARCHAR2 DEFAULT NULL
1107 )
1108 IS
1109 l_primary_uom VARCHAR2(3);
1110 l_total_supply NUMBER :=0;
1111 l_total_supply2 NUMBER :=0; -- INVCONV
1112
1113 l_validated_quantity NUMBER;
1114 l_primary_quantity NUMBER;
1115 l_qty_return_status VARCHAR2(1);
1116 -- 4695715
1117 l_total_conv_supply NUMBER :=0;
1118 l_total_conv_supply2 NUMBER :=0; -- INVCONV
1119 l_temp NUMBER :=0;
1120 --
1121 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1122 --
1123 BEGIN
1124 --4695715
1125 IF p_primary_uom IS NOT NULL THEN
1126 l_primary_uom := p_primary_uom;
1127 END IF;
1128 IF NVL(p_reservation_mode,'*') ='PERCENT' THEN
1129 Reservable_Quantity(p_inventory_item_id => p_x_rsv_tbl(1).inventory_item_id,
1130 p_ship_from_org_id => p_x_rsv_tbl(1).ship_from_org_id,
1131 p_subinventory => p_x_rsv_tbl(1).subinventory,
1132 p_org_id => p_x_rsv_tbl(1).org_id, --4759251
1133 x_primary_uom => l_primary_uom,
1134 x_available_qty => l_total_supply,
1135 x_available_qty2 => l_total_supply2 -- INVCONV
1136 );
1137 l_total_conv_supply := l_total_supply;
1138 l_total_conv_supply2 := l_total_supply2;
1139 END IF;
1140 FOR I in 1..p_x_rsv_tbl.COUNT
1141 LOOP
1142 -- 4695715 :Start
1143
1144 IF NOT OE_GLOBALS.Equal(p_x_rsv_tbl(I).ordered_qty_UOM, l_primary_uom)
1145 AND NVL(p_reservation_mode,'*') ='PERCENT'
1146 THEN
1147 IF l_debug_level > 0 THEN
1148 oe_debug_pub.add( 'Before UOM convertion :' || l_total_supply || '/' || l_primary_uom, 1 ) ;
1149 END IF;
1150 l_total_conv_supply :=
1151 INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
1152 precision => 5,
1153 from_quantity => l_total_supply,
1154 from_unit => l_primary_uom,
1155 to_unit => p_x_rsv_tbl(I).ordered_qty_uom,
1156 from_name => NULL,
1157 to_name => NULL
1158 );
1159 l_total_conv_supply2 :=
1160 INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
1161 precision => 5,
1162 from_quantity => l_total_supply2,
1163 from_unit => l_primary_uom,
1164 to_unit => p_x_rsv_tbl(I).ordered_qty_uom,
1165 from_name => NULL,
1166 to_name => NULL
1167 );
1168
1169 --l_primary_uom := p_x_rsv_tbl(I).ordered_qty_uom;
1170 IF l_debug_level > 0 THEN
1171 oe_debug_pub.add( 'After UOM convertion :' || l_total_conv_supply || '/' || p_x_rsv_tbl(I).ordered_qty_uom, 1 );
1172 oe_debug_pub.add( 'Total Supply2 :' || l_total_conv_supply2 || '/' || p_x_rsv_tbl(I).ordered_qty_uom, 1 );
1173 END IF;
1174 ELSIF NVL(p_reservation_mode,'*') ='PERCENT' THEN
1175 l_total_conv_supply := l_total_supply;
1176 l_total_conv_supply2 := l_total_supply2;
1177 END IF;
1178 -- 4695715 :End
1179 p_x_rsv_tbl(I).derived_reserved_qty :=
1180 TRUNC((p_x_rsv_tbl(I).ordered_qty * p_percentage) / 100, 5);
1181 p_x_rsv_tbl(I).derived_reserved_qty2 := -- INVCONV
1182 TRUNC((p_x_rsv_tbl(I).ordered_qty2 * p_percentage) / 100, 5); -- INVCONV
1183 IF l_debug_level > 0 THEN
1184 OE_DEBUG_PUB.Add('Before Calling validate_quantity ' ,1);
1185 END IF;
1186 inv_decimals_pub.validate_quantity(
1187 p_item_id => p_x_rsv_tbl(I).inventory_item_id,
1188 p_organization_id =>
1189 OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',p_x_rsv_tbl(I).org_id), -- 4759251
1190 p_input_quantity => p_x_rsv_tbl(I).derived_reserved_qty,
1191 p_uom_code => p_x_rsv_tbl(I).ordered_qty_uom,
1192 x_output_quantity => l_validated_quantity,
1193 x_primary_quantity => l_primary_quantity,
1194 x_return_status => l_qty_return_status);
1195 IF l_debug_level > 0 THEN
1196 OE_DEBUG_PUB.Add('After Calling validate_quantity: '||l_qty_return_status ,1);
1197 END IF;
1198 IF l_qty_return_status = 'W' OR l_qty_return_status = 'E' THEN
1199 IF l_debug_level > 0 THEN
1200 OE_DEBUG_PUB.Add('Validate_quantity returns Error/Warning: truncating the quantity');
1201 END IF;
1202 p_x_rsv_tbl(I).derived_reserved_qty := TRUNC(l_validated_quantity);
1203 END IF;
1204 IF l_debug_level > 0 THEN
1205 OE_DEBUG_PUB.Add('Validate_quantity: '||p_x_rsv_tbl(I).derived_reserved_qty ,1);
1206 OE_DEBUG_PUB.Add('Available quantity: '||l_total_conv_supply ,1);
1207 END IF;
1208 IF NVL(p_reservation_mode,'*') ='PERCENT' THEN
1209 IF l_total_conv_supply > 0
1210 AND p_x_rsv_tbl(I).derived_reserved_qty > l_total_conv_supply THEN
1211 p_x_rsv_tbl(I).derived_reserved_qty := l_total_conv_supply;
1212 ELSIF l_total_conv_supply = 0 THEN
1213 p_x_rsv_tbl(I).derived_reserved_qty := 0;
1214 END IF;
1215 --l_total_supply := l_total_supply - p_x_rsv_tbl(I).derived_reserved_qty;
1216 IF l_total_conv_supply2 > 0
1217 AND p_x_rsv_tbl(I).derived_reserved_qty2 > l_total_conv_supply2 THEN -- INVCONV from code review comments from AK
1218 p_x_rsv_tbl(I).derived_reserved_qty2 := l_total_conv_supply2;
1219 ELSIF l_total_conv_supply2 = 0 THEN
1220 p_x_rsv_tbl(I).derived_reserved_qty2 := 0;
1221 END IF;
1222 --l_total_supply2 := l_total_supply2 - p_x_rsv_tbl(I).derived_reserved_qty2; -- INVCONV from code review comments from AK
1223 END IF;
1224 -- Keeping copy of derived quantity
1225 p_x_rsv_tbl(I).derived_reserved_qty_mir := p_x_rsv_tbl(I).derived_reserved_qty;
1226 p_x_rsv_tbl(I).reserved_qty_UOM := p_x_rsv_tbl(I).ordered_qty_UOM;
1227 -- 4695715 :Start
1228 IF OE_GLOBALS.Equal(l_primary_uom, p_x_rsv_tbl(I).ordered_qty_uom) THEN
1229 g_consumed_for_lot := g_consumed_for_lot + p_x_rsv_tbl(I).derived_reserved_qty;
1230 IF NVL(p_reservation_mode,'*') ='PERCENT' THEN
1231 l_total_supply := l_total_supply - p_x_rsv_tbl(I).derived_reserved_qty;
1232
1233 END IF;
1234 ELSE
1235 l_temp:= INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
1236 precision => 5,
1237 from_quantity => p_x_rsv_tbl(I).derived_reserved_qty,
1238 from_unit => p_x_rsv_tbl(I).ordered_qty_uom,
1239 to_unit => l_primary_uom,
1240 from_name => NULL,
1241 to_name => NULL
1242 );
1243 g_consumed_for_lot := g_consumed_for_lot + l_temp;
1244 IF NVL(p_reservation_mode,'*') ='PERCENT' THEN
1245 l_total_supply := l_total_supply - l_temp;
1246 END IF;
1247 END IF;
1248 -- 4695715 : End
1249 -- INVCONV from code review comments from AK
1250 p_x_rsv_tbl(I).derived_reserved_qty2_mir := p_x_rsv_tbl(I).derived_reserved_qty2;
1251 --p_x_rsv_tbl(I).reserved_qty_UOM := p_x_rsv_tbl(I).ordered_qty_UOM;
1252 -- 4695715 : Start
1253 l_temp :=0;
1254 IF OE_GLOBALS.Equal(l_primary_uom ,p_x_rsv_tbl(I).ordered_qty_uom) THEN
1255 g_consumed_for_lot2 := g_consumed_for_lot2 + p_x_rsv_tbl(I).derived_reserved_qty2;
1256 IF NVL(p_reservation_mode,'*') ='PERCENT' THEN
1257 l_total_supply2 := l_total_supply2 - p_x_rsv_tbl(I).derived_reserved_qty2; -- INVCONV from code review comments from AK
1258 END IF;
1259 ELSE
1260 l_temp := INV_CONVERT.INV_UM_CONVERT( item_id => p_x_rsv_tbl(I).inventory_item_id,
1261 precision => 5,
1262 from_quantity => p_x_rsv_tbl(I).derived_reserved_qty2,
1263 from_unit => p_x_rsv_tbl(I).ordered_qty_uom,
1264 to_unit => l_primary_uom,
1265 from_name => NULL,
1266 to_name => NULL
1267 );
1268 g_consumed_for_lot2 := g_consumed_for_lot2 + l_temp;
1269 IF NVL(p_reservation_mode,'*') ='PERCENT' THEN
1270 l_total_supply2 := l_total_supply2 - l_temp;
1271 END IF;
1272 END IF;
1273 -- 4695715 : End
1274 --5041136
1275 p_x_rsv_tbl(I).corrected_reserved_qty := p_x_rsv_tbl(I).derived_reserved_qty;
1276 p_x_rsv_tbl(I).corrected_reserved_qty2 := p_x_rsv_tbl(I).derived_reserved_qty2;
1277 IF l_debug_level > 0 THEN
1278 OE_Debug_pub.Add('Derived reserved Qty '||p_x_rsv_tbl(I).derived_reserved_qty,1);
1279 OE_Debug_pub.Add('Consumed reserved Qty '||g_consumed_for_lot,1);
1280 OE_Debug_pub.Add('Derived reserved Qty2 '||p_x_rsv_tbl(I).derived_reserved_qty2,1);
1281 OE_Debug_pub.Add('Consumed reserved Qty2 '||g_consumed_for_lot2,1);
1282 END IF;
1283
1284 END LOOP;
1285 END Derive_Reservation_qty;
1286
1287
1288 /*----------------------------------------------------------------
1289 PROCEDURE : Create_Reservation
1290 DESCRIPTION: This Procedure send each line in rsv_tbl to the Inventory for
1291 Reservation
1292 -----------------------------------------------------------------*/
1293 Procedure Create_Reservation
1294 (p_x_rsv_tbl IN OUT NOCOPY OE_RESERVE_CONC.rsv_tbl_type,
1295 p_partial_reservation IN VARCHAR2 DEFAULT FND_API.G_TRUE,
1296 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1297 IS
1298 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1299 l_reservation_rec Inv_Reservation_Global.Mtl_Reservation_Rec_Type;
1300 l_msg_count NUMBER;
1301 l_dummy_sn Inv_Reservation_Global.Serial_Number_Tbl_Type;
1302 l_msg_data VARCHAR2(1000);
1303 l_buffer VARCHAR2(1000);
1304 l_quantity_reserved NUMBER;
1305 l_quantity2_reserved NUMBER; -- INVCONV
1306 l_rsv_id NUMBER;
1307 --Pack J
1308 l_count_header NUMBER:=0;
1309 l_request_id NUMBER;
1310 l_commit_count NUMBER :=0;
1311 l_partial_reservation_flag VARCHAR2(1);
1312 l_validated_quantity NUMBER;
1313 l_primary_quantity NUMBER;
1314 l_qty_return_status VARCHAR2(1);
1315 l_rsv_exists BOOLEAN;
1316 --
1317 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1318 --
1319
1320 BEGIN
1321 Oe_debug_pub.add ('inside Create reservation',1);
1322 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
1323 l_partial_reservation_flag := p_partial_reservation; -- Pack J
1324 FOR I IN 1..p_x_rsv_tbl.COUNT
1325 LOOP
1326 --5041136
1327 --IF p_x_rsv_tbl(I).derived_reserved_qty > 0 THEN
1328 IF p_x_rsv_tbl(I).corrected_reserved_qty > 0 THEN
1329 IF l_debug_level > 0 THEN
1330 OE_Debug_pub.Add('Creating reservation record',1);
1331 END IF;
1332 --Pack J
1333 l_count_header := l_count_header + 1;
1334 IF l_count_header = 1 THEN
1335 -- Set Message Context
1336 OE_MSG_PUB.set_msg_context(
1337 p_entity_code => 'LINE'
1338 ,p_entity_id => p_x_rsv_tbl(I).line_id
1339 ,p_header_id => p_x_rsv_tbl(I).header_id
1340 ,p_line_id => p_x_rsv_tbl(I).line_id
1341 ,p_order_source_id => p_x_rsv_tbl(I).order_source_id
1342 ,p_orig_sys_document_ref => p_x_rsv_tbl(I).orig_sys_document_ref
1343 ,p_orig_sys_document_line_ref => p_x_rsv_tbl(I).orig_sys_line_ref
1344 ,p_orig_sys_shipment_ref => p_x_rsv_tbl(I).orig_sys_shipment_ref
1345 ,p_change_sequence => p_x_rsv_tbl(I).change_sequence
1346 ,p_source_document_type_id => p_x_rsv_tbl(I).source_document_type_id
1347 ,p_source_document_id => p_x_rsv_tbl(I).source_document_id
1348 ,p_source_document_line_id => p_x_rsv_tbl(I).source_document_line_id
1349 );
1350 ELSIF l_count_header > 1 THEN
1351 -- Update Message Context
1352 OE_MSG_PUB.update_msg_context(
1353 p_entity_code => 'LINE'
1354 ,p_entity_id => p_x_rsv_tbl(I).line_id
1355 ,p_header_id => p_x_rsv_tbl(I).header_id
1356 ,p_line_id => p_x_rsv_tbl(I).line_id
1357 ,p_orig_sys_document_ref => p_x_rsv_tbl(I).orig_sys_document_ref
1358 ,p_orig_sys_document_line_ref => p_x_rsv_tbl(I).orig_sys_line_ref
1359 ,p_change_sequence => p_x_rsv_tbl(I).change_sequence
1360 ,p_source_document_id => p_x_rsv_tbl(I).source_document_id
1361 ,p_source_document_line_id => p_x_rsv_tbl(I).source_document_line_id
1362 ,p_order_source_id => p_x_rsv_tbl(I).order_source_id
1363 ,p_source_document_type_id => p_x_rsv_tbl(I).source_document_type_id
1364 );
1365 END IF;
1366
1367
1368 l_reservation_rec := null;
1369 l_reservation_rec.reservation_id := fnd_api.g_miss_num; -- cannot know
1370 l_reservation_rec.requirement_date := p_x_rsv_tbl(I).schedule_ship_date;
1371 l_reservation_rec.organization_id := p_x_rsv_tbl(I).ship_from_org_id;
1372 l_reservation_rec.inventory_item_id := p_x_rsv_tbl(I).inventory_item_id;
1373
1374 IF p_x_rsv_tbl(I).source_document_type_id = 10 THEN
1375 -- This is an internal order line. We need to give
1376 -- a different demand source type for these lines.
1377 l_reservation_rec.demand_source_type_id :=
1378 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INTERNAL_ORD; -- intenal order
1379 ELSE
1380 l_reservation_rec.demand_source_type_id :=
1381 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE; -- order entry
1382 END IF;
1383 l_reservation_rec.demand_source_header_id :=
1384 OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(p_x_rsv_tbl(I).header_id);
1385 l_reservation_rec.demand_source_line_id := p_x_rsv_tbl(I).line_id;
1386 l_reservation_rec.reservation_uom_code := p_x_rsv_tbl(I).ordered_qty_uom;
1387 --5041136
1388 --IF p_x_rsv_tbl(I).derived_reserved_qty IS NOT NULL THEN
1389 IF p_x_rsv_tbl(I).corrected_reserved_qty IS NOT NULL THEN
1390 --l_reservation_rec.reservation_quantity := p_x_rsv_tbl(I).derived_reserved_qty;
1391 l_reservation_rec.reservation_quantity := p_x_rsv_tbl(I).corrected_reserved_qty;
1392 ELSE
1393 l_reservation_rec.reservation_quantity := p_x_rsv_tbl(I).ordered_qty;
1394 END IF;
1395 --IF p_x_rsv_tbl(I).derived_reserved_qty2 IS NOT NULL THEN -- INVCONV
1396 IF p_x_rsv_tbl(I).corrected_reserved_qty2 IS NOT NULL THEN -- INVCONV
1397 --l_reservation_rec.secondary_reservation_quantity := p_x_rsv_tbl(I).derived_reserved_qty2;
1398 l_reservation_rec.secondary_reservation_quantity := p_x_rsv_tbl(I).corrected_reserved_qty2;
1399 ELSE
1400 l_reservation_rec.secondary_reservation_quantity := p_x_rsv_tbl(I).ordered_qty2;
1401 END IF;
1402
1403 IF l_reservation_rec.secondary_reservation_quantity = 0 -- INVCONV
1404 THEN
1405 l_reservation_rec.secondary_reservation_quantity := NULL;
1406 END IF;
1407
1408
1409 l_reservation_rec.supply_source_type_id :=
1410 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
1411 l_reservation_rec.subinventory_code := p_x_rsv_tbl(I).subinventory;
1412 -- check if derived qty has changed then validate the quantity
1413 IF NVL(l_reservation_rec.reservation_quantity,0)
1414 <> NVL(p_x_rsv_tbl(I).derived_reserved_qty_mir,0) THEN
1415
1416 IF l_debug_level > 0 THEN
1417 OE_DEBUG_PUB.Add('Before Calling inv_reservation_pub.validate_quantity ' ,1);
1418 END IF;
1419 inv_decimals_pub.validate_quantity(
1420 p_item_id => p_x_rsv_tbl(I).inventory_item_id,
1421 p_organization_id =>
1422 OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',p_x_rsv_tbl(I).org_id), --4759251
1423 p_input_quantity => l_reservation_rec.reservation_quantity,
1424 p_uom_code => l_reservation_rec.reservation_uom_code,
1425 x_output_quantity => l_validated_quantity,
1426 x_primary_quantity => l_primary_quantity,
1427 x_return_status => l_qty_return_status);
1428 IF l_debug_level > 0 THEN
1429 OE_DEBUG_PUB.Add('After Calling inv_reservation_pub.validate_quantity: '||l_qty_return_status ,1);
1430 END IF;
1431
1432 IF l_qty_return_status = 'W' OR l_qty_return_status = 'E' THEN
1433 IF l_debug_level > 0 THEN
1434 OE_DEBUG_PUB.Add('Validate_quantity returns Error/Warning: truncating the quantity');
1435 END IF;
1436 l_validated_quantity := TRUNC(l_validated_quantity);
1437 END IF;
1438 IF l_debug_level > 0 THEN
1439 OE_DEBUG_PUB.Add('Parimary_quantity: '||l_primary_quantity ,1);
1440 OE_DEBUG_PUB.Add('Validate_quantity: '||l_validated_quantity ,1);
1441 END IF;
1442 ELSE
1443 l_validated_quantity := l_reservation_rec.reservation_quantity;
1444 END IF;
1445
1446 --IF l_qty_return_status = FND_API.G_RET_STS_SUCCESS
1447 IF l_validated_quantity > 0 THEN
1448 l_reservation_rec.reservation_quantity := l_validated_quantity;
1449
1450 -- Partial reservation check
1451 IF NVL(p_x_rsv_tbl(I).reservation_exists,'N') = 'Y' THEN
1452 l_rsv_exists := TRUE;
1453 ELSE
1454 l_rsv_exists := FALSE;
1455 END IF;
1456
1457 -- Call INV with action = RESERVE
1458 IF l_debug_level > 0 THEN
1459 OE_DEBUG_PUB.Add('Calling inv_reservation_pub.create_reservation '
1460 ||l_reservation_rec.reservation_quantity,1);
1461 END IF;
1462 INV_RESERVATION_PUB.Create_Reservation
1463 ( p_api_version_number => 1.0
1464 , p_init_msg_lst => FND_API.G_TRUE
1465 , x_return_status => l_return_status
1466 , x_msg_count => l_msg_count
1467 , x_msg_data => l_msg_data
1468 , p_rsv_rec => l_reservation_rec
1469 , p_serial_number => l_dummy_sn
1470 , x_serial_number => l_dummy_sn
1471 , p_partial_reservation_flag => l_partial_reservation_flag --FND_API.G_TRUE
1472 , p_force_reservation_flag => FND_API.G_FALSE
1473 , p_validation_flag => FND_API.G_TRUE
1474 , x_quantity_reserved => l_quantity_reserved
1475 , x_secondary_quantity_reserved => l_quantity2_reserved -- INVCONV
1476 , x_reservation_id => l_rsv_id
1477 , p_partial_rsv_exists => l_rsv_exists
1478 );
1479 IF l_debug_level > 0 THEN
1480 OE_DEBUG_PUB.Add('1. After Calling Create Reservation' || l_return_status ||' '||l_quantity_reserved,1);
1481 END IF;
1482 -- Pack J
1483 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1484 l_commit_count := l_commit_count + 1;
1485
1486 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1487 IF l_debug_level > 0 THEN
1488 OE_DEBUG_PUB.Add('Raising Unexpected error',1);
1489 END IF;
1490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1491
1492 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1493 IF l_debug_level > 0 THEN
1494 OE_DEBUG_PUB.Add('Raising Expected error',1);
1495 END IF;
1496 IF l_msg_data is not null THEN
1497 fnd_message.set_encoded(l_msg_data);
1498 l_buffer := fnd_message.get;
1499 OE_MSG_PUB.Add_text(p_message_text => l_buffer);
1500 IF l_debug_level > 0 THEN
1501 OE_DEBUG_PUB.Add(l_msg_data,1);
1502 END IF;
1503 END IF;
1504 --RAISE FND_API.G_EXC_ERROR; -- Commented as we don't need to fail the program for excepted error.
1505
1506 END IF;
1507 END IF;
1508 -- Pack J
1509 IF l_commit_count = 500 THEN
1510
1511 IF l_debug_level > 0 THEN
1512 oe_debug_pub.add( ' INSIDE LOOP BEFORE CALLING THE COMMIT_RESERVATION' , 1 ) ;
1513 END IF;
1514 Commit_Reservation(p_request_id => l_request_id
1515 ,x_return_status => l_return_status);
1516 IF l_debug_level > 0 THEN
1517 oe_debug_pub.add( 'INSIDE LOOP AFTER CALLING THE COMMIT_RESERVATION : ' , 1 ) ;
1518 END IF;
1519 --Pack J
1520 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
1521
1522 COMMIT;
1523 l_commit_count := 0;
1524 END IF;
1525 END IF;
1526 END LOOP;
1527 IF l_debug_level > 0 THEN
1528 oe_debug_pub.add( ' BEFORE CALLING THE COMMIT_RESERVATION' , 1 ) ;
1529 END IF;
1530 Commit_Reservation(p_request_id => l_request_id
1531 ,x_return_status => l_return_status);
1532 IF l_debug_level > 0 THEN
1533 oe_debug_pub.add( ' AFTER CALLING THE COMMIT_RESERVATION ', 1 ) ;
1534 END IF;
1535 --Pack J
1536 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
1537
1538 COMMIT;
1539 -- For Expected errors we will consider status as success.
1540 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1541 l_return_status := FND_API.G_RET_STS_SUCCESS;
1542 END IF;
1543 x_return_status := l_return_status;
1544 IF l_debug_level > 0 THEN
1545 OE_DEBUG_PUB.Add('Exiting OE_RESERVE_CONC.Create_reservation for rsv_tbl',1);
1546 END IF;
1547
1548 EXCEPTION
1549 WHEN FND_API.G_EXC_ERROR THEN
1550 IF l_debug_level > 0 THEN
1551 OE_DEBUG_PUB.Add('In Expected Error...in Proc Create_Reservation for rsv_tbl',1);
1552 END IF;
1553 x_return_status := FND_API.G_RET_STS_ERROR;
1554
1555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1556 IF l_debug_level > 0 THEN
1557 OE_DEBUG_PUB.Add('In Unexpected Error...in Proc Create_Reservation for rsv tbl',1 );
1558 END IF;
1559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1560
1561 WHEN OTHERS THEN
1562 IF l_debug_level > 0 THEN
1563 OE_DEBUG_PUB.Add('In others error...in Proc Create_Reservation for rsv tbl',1);
1564 END IF;
1565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1566
1567 END create_reservation;
1568
1569 /*----------------------------------------------------------------
1570 PROCEDURE : Prepare_And_Reserve
1571 DESCRIPTION: This Procedure will call procedures calculate_percentage,
1572 Derive_reservation_qty and create_reservation based on
1573 reservation mode and reservation run type.
1574 -----------------------------------------------------------------*/
1575 Procedure Prepare_And_Reserve(p_rsv_tbl IN OUT NOCOPY /* file.sql.39 change */ OE_RESERVE_CONC.Rsv_Tbl_Type,
1576 p_percent IN NUMBER DEFAULT NULL,
1577 p_reservation_mode IN VARCHAR2,
1578 p_reserve_run_type IN VARCHAR2,
1579 p_reserve_set_name IN VARCHAR2 DEFAULT NULL)
1580 IS
1581 l_percent NUMBER;
1582 l_return_status VARCHAR2(1);
1583 l_request_id NUMBER;
1584 l_primary_uom VARCHAR2(3) := NULL; -- 4695715
1585 --
1586 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1587 --
1588
1589 BEGIN
1590 l_percent := p_percent;
1591 IF p_reservation_mode = 'FAIR' THEN
1592 calculate_percentage( p_inventory_item_id => p_rsv_tbl(1).inventory_item_id,
1593 p_ship_from_org_id => p_rsv_tbl(1).ship_from_org_id,
1594 p_subinventory => p_rsv_tbl(1).subinventory ,
1595 p_rsv_tbl => p_rsv_tbl ,
1596 x_percentage => l_percent ,
1597 x_primary_uom => l_primary_uom --4695715
1598 );
1599 END IF; -- 'FAIR'
1600 IF p_reservation_mode = 'FAIR'
1601 OR p_reservation_mode = 'PERCENT' THEN
1602 Derive_reservation_qty( p_x_rsv_tbl => p_rsv_tbl,
1603 p_percentage => l_percent,
1604 p_reservation_mode => p_reservation_mode,
1605 p_primary_uom => l_primary_uom --4695715
1606 );
1607 --END IF;
1608 ELSIF p_reservation_mode = 'PARTIAL_ONLY_UNRESERVED'
1609 OR p_reservation_mode = 'PARTIAL' THEN -- Pack J
1610 Calculate_Partial_Quantity(p_x_rsv_tbl => p_rsv_tbl,
1611 p_reservation_mode => p_reservation_mode,
1612 x_return_status => l_return_status);
1613 --5034236
1614 ELSE -- Mode is not supplied
1615 Derive_reservation_qty( p_x_rsv_tbl => p_rsv_tbl,
1616 p_percentage => 100,
1617 p_reservation_mode => 'PERCENT',
1618 p_primary_uom => l_primary_uom
1619 );
1620
1621 END IF;
1622
1623 IF l_debug_level > 0 THEN
1624 OE_DEBUG_PUB.Add('Going to call OE_RESERVE_CONC_HOOK.Qty_Per_Business_Rule ',1);
1625 END IF;
1626
1627 OE_RESERVE_CONC_HOOK.Qty_Per_Business_Rule(p_x_rsv_tbl => p_rsv_tbl);
1628
1629 IF l_debug_level > 0 THEN
1630 OE_DEBUG_PUB.Add('After calling OE_RESERVE_CONC_HOOK.Qty_Per_Business_Rule ',1);
1631 END IF;
1632
1633 IF p_reserve_run_type = 'RESERVE'
1634 OR p_reserve_run_type IS NULL THEN
1635
1636 --OE_RESERVE_CONC_HOOK.Qty_Per_Business_Rule(p_x_rsv_tbl => p_rsv_tbl);
1637
1638
1639 create_reservation(p_x_rsv_tbl => p_rsv_tbl
1640 ,x_return_status => l_return_status);
1641 IF l_return_status = FND_API.G_RET_STS_SUCCESS
1642 AND p_reserve_set_name IS NOT NULL THEN -- Pack J
1643 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
1644 Create_Reservation_Set(p_rsv_tbl => p_rsv_tbl,
1645 p_reserve_set_name => p_reserve_set_name,
1646 p_rsv_request_id => l_request_id,
1647 x_return_status => l_return_status);
1648
1649 END IF;
1650
1651 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1652 IF l_debug_level > 0 THEN
1653 OE_DEBUG_PUB.Add('Create Reservation returned with Expected error for rsv tbl ',1);
1654 END IF;
1655 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1656 IF l_debug_level > 0 THEN
1657 OE_DEBUG_PUB.Add('Create Reservation returned with Unexpected error for rsv tbl',1);
1658 END IF;
1659 END IF;
1660
1661 ELSIF p_reserve_run_type = 'SIMULATE TO EXTERNAL TABLE' THEN
1662 IF l_debug_level > 0 THEN
1663 OE_DEBUG_PUB.Add('Going to call OE_RESERVE_CONC_HOOK.Simulated_Results ',1);
1664 END IF;
1665
1666 OE_RESERVE_CONC_HOOK.Simulated_Results(p_x_rsv_tbl => p_rsv_tbl);
1667
1668 IF l_debug_level > 0 THEN
1669 OE_DEBUG_PUB.Add('After calling OE_RESERVE_CONC_HOOK.Simulated_Results ',1);
1670 END IF;
1671
1672 ELSIF p_reserve_run_type = 'SIMULATE' THEN -- Pack J
1673 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
1674 Create_Reservation_set(p_rsv_tbl => p_rsv_tbl,
1675 p_reserve_set_name=> p_reserve_set_name,
1676 p_simulation_request_id => l_request_id,
1677 x_return_status => l_return_status);
1678
1679 END IF;
1680 END Prepare_And_Reserve;
1681
1682 /*----------------------------------------------------------------
1683 PROCEDURE : Reserve_Eligible
1684 DESCRIPTION: This Procedure is to check if the Line that is being
1685 considered needs Reservation
1686 ----------------------------------------------------------------*/
1687 Procedure Reserve_Eligible
1688 ( p_line_rec IN OE_ORDER_PUB.line_rec_type,
1689 p_use_reservation_time_fence IN VARCHAR2,
1690 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1691 )
1692 IS
1693 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1694 l_result Varchar2(30);
1695 l_scheduling_level_code VARCHAR2(30) := NULL;
1696 l_out_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1697 l_type_code VARCHAR2(30);
1698 l_org_id NUMBER;
1699 l_time_fence BOOLEAN;
1700 l_msg_count NUMBER;
1701 l_msg_data VARCHAR2(1000);
1702 l_dummy VARCHAR2(100);
1703
1704 --
1705 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1706 --
1707
1708 BEGIN
1709 IF l_debug_level > 0 THEN
1710 OE_DEBUG_PUB.Add('Inside Reserve Eligible Procedure',1);
1711 END IF;
1712
1713 /* Check if line is open, if not open ignore the line */
1714 IF ( p_line_rec.open_flag = 'N' ) THEN
1715 IF l_debug_level > 0 THEN
1716 OE_DEBUG_PUB.Add('Line is closed, not eligible for reservation', 1);
1717 END IF;
1718 l_return_status := FND_API.G_RET_STS_ERROR;
1719
1720 /* Check if line is shipped, if shipped then ignore the line */
1721 ELSIF ( nvl(p_line_rec.shipped_quantity, -99) > 0 ) THEN
1722 IF l_debug_level > 0 THEN
1723 OE_DEBUG_PUB.Add('Line is shipped, not eligible for reservation', 1);
1724 END IF;
1725 l_return_status := FND_API.G_RET_STS_ERROR;
1726 --Added for bug 6873122
1727 ELSIF ( nvl(p_line_rec.fulfilled_quantity, -99) > 0 ) THEN
1728 IF l_debug_level > 0 THEN
1729 OE_DEBUG_PUB.Add('Line is Fulfilled, not eligible for reservation', 1);
1730 END IF;
1731 l_return_status := FND_API.G_RET_STS_ERROR;
1732 --Added for bug 6873122
1733 END IF;
1734
1735 IF l_return_status = FND_API.G_RET_STS_SUCCESS
1736 AND NVL(g_reservation_mode,'*') <> 'PARTIAL' THEN -- Pack J
1737 /* We need to check for Existing Reservations on the Line */
1738 BEGIN
1739 IF l_debug_level > 0 THEN
1740 OE_DEBUG_PUB.Add('Before checking Existing Reservations',1);
1741 END IF;
1742
1743 SELECT 'Reservation Exists'
1744 INTO l_dummy
1745 FROM MTL_RESERVATIONS
1746 WHERE DEMAND_SOURCE_LINE_ID = p_line_rec.line_id;
1747
1748 IF l_debug_level > 0 THEN
1749 OE_DEBUG_PUB.Add('Reservations exists on the line',3);
1750 END IF;
1751
1752 RAISE FND_API.G_EXC_ERROR;
1753 EXCEPTION
1754 WHEN FND_API.G_EXC_ERROR THEN
1755 IF l_debug_level > 0 THEN
1756 OE_DEBUG_PUB.Add('In Expected Error for Check Reservation',3);
1757 END IF;
1758 l_return_status := FND_API.G_RET_STS_ERROR;
1759
1760 WHEN NO_DATA_FOUND THEN
1761 NULL;
1762 WHEN TOO_MANY_ROWS THEN
1763 -- NULL;
1764 l_return_status := FND_API.G_RET_STS_ERROR; --2929716
1765 END;
1766 END IF;
1767 -- 3250889 Starts
1768 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1769 BEGIN
1770 IF l_debug_level > 0 THEN
1771 OE_DEBUG_PUB.Add('Before checking for Staged/Closed deliveries', 1);
1772 END IF;
1773
1774 SELECT 'Staging Exists'
1775 INTO l_dummy
1776 FROM WSH_DELIVERY_DETAILS
1777 WHERE SOURCE_LINE_ID = p_line_rec.line_id
1778 AND SOURCE_CODE = 'OE' -- Added for bug 3286756
1779 AND RELEASED_STATUS IN ('Y', 'C');
1780
1781 IF l_debug_level > 0 THEN
1782 OE_DEBUG_PUB.Add('Staged/Closed deliveries exist for the line', 3);
1783 END IF;
1784
1785 RAISE FND_API.G_EXC_ERROR;
1786
1787 EXCEPTION
1788 WHEN FND_API.G_EXC_ERROR THEN
1789 IF l_debug_level > 0 THEN
1790 OE_DEBUG_PUB.Add('In Expected Error for Checking Staged/Closed deliveries', 3);
1791 END IF;
1792 l_return_status := FND_API.G_RET_STS_ERROR;
1793 WHEN NO_DATA_FOUND THEN
1794 NULL;
1795 WHEN TOO_MANY_ROWS THEN
1796 l_return_status := FND_API.G_RET_STS_ERROR;
1797 END;
1798 END IF;
1799 -- 3250889 Ends
1800 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1801
1802 -- WE NEED TO CHECK FOR THE reservation_time_fence Value.
1803 -- If the Value of the parameter passed to the concurrent
1804 -- program is "NO' then we reserve the lines irrespective
1805 -- of the profile option: OM : Reservation_Time_fence.
1806 -- By default this parameter will have a value of YES.
1807
1808 IF (NVL(p_use_reservation_time_fence,'Y') = 'Y' or
1809 NVL(p_use_reservation_time_fence,'Yes') = 'Yes') THEN
1810 IF l_debug_level > 0 THEN
1811 OE_DEBUG_PUB.Add('Schedule Ship Date:'||
1812 p_line_rec.schedule_ship_date,3);
1813 END IF;
1814
1815 -- Scheduling restructure
1816 /* IF NVL(FND_PROFILE.VALUE('ONT_BRANCH_SCHEDULING'),'N') = 'N' THEN
1817 -- 4689197
1818 IF NOT OE_ORDER_SCH_UTIL.Within_Rsv_Time_Fence
1819 (p_line_rec.schedule_ship_date, p_line_rec.org_id) THEN
1820 IF l_debug_level > 0 THEN
1821 OE_DEBUG_PUB.Add('The Schedule Date for Line falls
1822 beyond reservation Time Fence',3);
1823 END IF;
1824 RAISE FND_API.G_EXC_ERROR ;
1825
1826 END IF;
1827 ELSE */
1828 -- 4689197
1829 IF NOT OE_SCHEDULE_UTIL.Within_Rsv_Time_Fence
1830 (p_line_rec.schedule_ship_date, p_line_rec.org_id) THEN
1831 IF l_debug_level > 0 THEN
1832 OE_DEBUG_PUB.Add('The Schedule Date for Line falls
1833 beyond reservation Time Fence',3);
1834 END IF;
1835 RAISE FND_API.G_EXC_ERROR ;
1836
1837 END IF;
1838 END IF;
1839 -- END IF;
1840
1841
1842 -- We need to check if the Line Type for the Line allows
1843 -- us to reserve the Line or not.
1844 IF l_debug_level > 0 THEN
1845 OE_DEBUG_PUB.Add('Checking Scheduling Level...',3);
1846 END IF;
1847 -- Scheduling restructure
1848 /* Bug: 4504362
1849 IF NVL(FND_PROFILE.VALUE('ONT_BRANCH_SCHEDULING'),'N') = 'N' THEN
1850 l_scheduling_level_code := OE_ORDER_SCH_UTIL.Get_Scheduling_Level
1851 (p_line_rec.header_id
1852 ,p_line_rec.line_type_id);
1853 ELSE
1854 */
1855 l_scheduling_level_code := OE_SCHEDULE_UTIL.Get_Scheduling_Level
1856 (p_line_rec.header_id
1857 ,p_line_rec.line_type_id);
1858 --END IF;
1859
1860 IF l_debug_level > 0 THEN
1861 OE_DEBUG_PUB.Add('l_scheduling_level_code:'||l_scheduling_level_code,1);
1862 END IF;
1863
1864 IF l_scheduling_level_code is not null AND
1865 (l_scheduling_level_code = SCH_LEVEL_ONE
1866 OR l_scheduling_level_code = SCH_LEVEL_TWO
1867 OR l_scheduling_level_code = SCH_LEVEL_FIVE)
1868 THEN
1869 IF p_line_rec.schedule_action_code = OESCH_ACT_RESERVE OR
1870 (p_line_rec.schedule_status_code is null AND
1871 (p_line_rec.schedule_ship_date is NOT NULL OR
1872 p_line_rec.schedule_arrival_date is NOT NULL))
1873 THEN
1874 IF l_debug_level > 0 THEN
1875 OE_DEBUG_PUB.Add('Order Type Does not Allow Scheduling',3);
1876 END IF;
1877 RAISE FND_API.G_EXC_ERROR;
1878 END IF;
1879 END IF;
1880
1881 END IF; -- Check for Reservation Exists Clause
1882 x_return_status := l_return_status;
1883
1884 IF l_debug_level > 0 THEN
1885 OE_DEBUG_PUB.Add('..Exiting OE_RESERVE_CONC.Need_Reservation' ||
1886 l_return_status ,1);
1887 END IF;
1888 EXCEPTION
1889 WHEN FND_API.G_EXC_ERROR THEN
1890 IF l_debug_level > 0 THEN
1891 OE_DEBUG_PUB.Add('In Expected Error...in Proc Reserve_Eligible',3);
1892 END IF;
1893 x_return_status := FND_API.G_RET_STS_ERROR;
1894
1895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1896 IF l_debug_level > 0 THEN
1897 OE_DEBUG_PUB.Add('In UnExpected Error...in Proc Reserve_Eligible',3);
1898 END IF;
1899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1900
1901 WHEN OTHERS THEN
1902
1903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1904
1905 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1906 THEN
1907 OE_MSG_PUB.Add_Exc_Msg
1908 ( G_PKG_NAME
1909 , 'Action_Reserve'
1910 );
1911 END IF;
1912
1913 End Reserve_Eligible;
1914
1915
1916 /*----------------------------------------------------------------
1917 PROCEDURE : Create_Reservation
1918 DESCRIPTION: This Procedure send the line to the Inventory for
1919 Reservation
1920 -----------------------------------------------------------------*/
1921 Procedure Create_Reservation
1922 (p_line_rec IN OE_ORDER_PUB.line_rec_type,
1923 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1924 IS
1925 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1926 l_reservation_rec Inv_Reservation_Global.Mtl_Reservation_Rec_Type;
1927 l_msg_count NUMBER;
1928 l_dummy_sn Inv_Reservation_Global.Serial_Number_Tbl_Type;
1929 l_msg_data VARCHAR2(1000);
1930 l_buffer VARCHAR2(1000);
1931 l_quantity_reserved NUMBER;
1932 l_quantity_to_reserve NUMBER;
1933 l_rsv_id NUMBER;
1934
1935 l_quantity2_reserved NUMBER; -- INVCONV
1936 l_quantity2_to_reserve NUMBER; -- INVCONV
1937
1938 --
1939 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1940 --
1941
1942
1943 BEGIN
1944 IF l_debug_level > 0 THEN
1945 OE_Debug_pub.Add('In the Procedure Create Reservation',1);
1946 OE_Debug_pub.Add('Before call of Load_INV_Request',1);
1947 END IF;
1948
1949
1950 IF p_line_rec.ordered_quantity2 = 0 -- INVCONV
1951 THEN
1952 l_quantity2_to_reserve := NULL;
1953 END IF;
1954
1955
1956 -- Added for Scheduling Restructring
1957 /* Bug: 4504362
1958 IF NVL(FND_PROFILE.VALUE('ONT_BRANCH_SCHEDULING'),'N') = 'N' THEN
1959 OE_ORDER_SCH_UTIL.Load_Inv_Request
1960 ( p_line_rec => p_line_rec
1961 , p_quantity_to_reserve => p_line_rec.ordered_quantity
1962 , p_quantity2_to_reserve => l_quantity2_to_reserve -- INVCONV
1963 , x_reservation_rec => l_reservation_rec);
1964 ELSE
1965 */
1966 OE_SCHEDULE_UTIL.Load_Inv_Request
1967 ( p_line_rec => p_line_rec
1968 , p_quantity_to_reserve => p_line_rec.ordered_quantity
1969 , p_quantity2_to_reserve => l_quantity2_to_reserve -- INVCONV
1970 , x_reservation_rec => l_reservation_rec);
1971 --END IF;
1972
1973
1974 -- Call INV with action = RESERVE
1975 IF l_debug_level > 0 THEN
1976 OE_DEBUG_PUB.Add('Before call of inv_reservation_pub.create_reservation',1);
1977 END IF;
1978
1979 INV_RESERVATION_PUB.Create_Reservation
1980 ( p_api_version_number => 1.0
1981 , p_init_msg_lst => FND_API.G_TRUE
1982 , x_return_status => l_return_status
1983 , x_msg_count => l_msg_count
1984 , x_msg_data => l_msg_data
1985 , p_rsv_rec => l_reservation_rec
1986 , p_serial_number => l_dummy_sn
1987 , x_serial_number => l_dummy_sn
1988 , p_partial_reservation_flag => FND_API.G_FALSE
1989 , p_force_reservation_flag => FND_API.G_FALSE
1990 , p_validation_flag => FND_API.G_TRUE
1991 , x_quantity_reserved => l_quantity_reserved
1992 , x_secondary_quantity_reserved => l_quantity2_reserved -- INVCONV
1993 , x_reservation_id => l_rsv_id
1994 );
1995 IF l_debug_level > 0 THEN
1996 OE_DEBUG_PUB.Add('1. After Calling Create Reservation' ||
1997 l_return_status,1);
1998 OE_DEBUG_PUB.Add(l_msg_data,1);
1999 END IF;
2000
2001 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2002 IF l_debug_level > 0 THEN
2003 OE_DEBUG_PUB.Add('Raising Unexpected error',1);
2004 END IF;
2005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2006
2007 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2008 IF l_debug_level > 0 THEN
2009 OE_DEBUG_PUB.Add('Raising Expected error',1);
2010 END IF;
2011 IF l_msg_data is not null THEN
2012 fnd_message.set_encoded(l_msg_data);
2013 l_buffer := fnd_message.get;
2014 OE_MSG_PUB.Add_text(p_message_text => l_buffer);
2015 IF l_debug_level > 0 THEN
2016 OE_DEBUG_PUB.Add(l_msg_data,1);
2017 END IF;
2018 END IF;
2019 RAISE FND_API.G_EXC_ERROR;
2020
2021 END IF;
2022 IF l_debug_level > 0 THEN
2023 OE_DEBUG_PUB.Add('..Exiting OE_RESERVE_CONC.Create_reservation' ||
2024 l_return_status ,1);
2025 END IF;
2026 x_return_status := FND_API.G_RET_STS_SUCCESS;
2027
2028 EXCEPTION
2029 WHEN FND_API.G_EXC_ERROR THEN
2030 IF l_debug_level > 0 THEN
2031 OE_DEBUG_PUB.Add('In Expected Error...in Proc Create_Reservation',1);
2032 END IF;
2033 x_return_status := FND_API.G_RET_STS_ERROR;
2034
2035 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2036 IF l_debug_level > 0 THEN
2037 OE_DEBUG_PUB.Add('In Unexpected Error...in Proc Create_Reservation');
2038 END IF;
2039 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2040
2041 WHEN OTHERS THEN
2042 IF l_debug_level > 0 THEN
2043 OE_DEBUG_PUB.Add('In others error...in Proc Create_Reservation');
2044 END IF;
2045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2046
2047 END;
2048
2049
2050 /*----------------------------------------------------------------
2051 PROCEDURE : Reserve
2052 DESCRIPTION: Reserve Scheduled Orders Concurrent Request
2053 -----------------------------------------------------------------*/
2054
2055 Procedure Reserve
2056 (ERRBUF OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2057 RETCODE OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2058 /* Moac */
2059 p_org_id IN NUMBER,
2060 p_use_reservation_time_fence IN CHAR,
2061 p_order_number_low IN NUMBER,
2062 p_order_number_high IN NUMBER,
2063 p_customer_id IN VARCHAR2,
2064 p_order_type IN VARCHAR2,
2065 p_line_type_id IN VARCHAR2,
2066 p_warehouse IN VARCHAR2,
2067 p_inventory_item_id IN VARCHAR2,
2068 p_request_date_low IN VARCHAR2,
2069 p_request_date_high IN VARCHAR2,
2070 p_schedule_ship_date_low IN VARCHAR2,
2071 p_schedule_ship_date_high IN VARCHAR2,
2072 p_schedule_arrival_date_low IN VARCHAR2,
2073 p_schedule_arrival_date_high IN VARCHAR2,
2074 p_ordered_date_low IN VARCHAR2,
2075 p_ordered_date_high IN VARCHAR2,
2076 p_demand_class_code IN VARCHAR2,
2077 p_planning_priority IN NUMBER,
2078 p_booked IN VARCHAR2 DEFAULT NULL,
2079 p_reservation_mode IN VARCHAR2 DEFAULT NULL,
2080 p_dummy1 IN VARCHAR2 DEFAULT NULL,
2081 p_dummy2 IN VARCHAR2 DEFAULT NULL,
2082 p_percent IN NUMBER DEFAULT NULL,
2083 p_shipment_priority IN VARCHAR2 DEFAULT NULL,
2084 p_reserve_run_type IN VARCHAR2 DEFAULT NULL,
2085 p_reserve_set_name IN VARCHAR2 DEFAULT NULL,
2086 p_override_set IN VARCHAR2 DEFAULT NULL,
2087 p_order_by IN VARCHAR2,
2088 p_selected_ids IN VARCHAR2 DEFAULT NULL,
2089 p_dummy3 IN VARCHAR2 DEFAULT NULL,
2090 p_partial_preference IN VARCHAR2 DEFAULT 'N'
2091 )IS
2092
2093 l_stmt VARCHAR2(4000) :=NULL;
2094 l_line_rec OE_ORDER_PUB.line_rec_type;
2095 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2096 l_request_date_low DATE;
2097 l_request_date_high DATE;
2098 l_schedule_ship_date_low DATE;
2099 l_schedule_ship_date_high DATE;
2100 l_schedule_arrival_date_low DATE;
2101 l_schedule_arrival_date_high DATE;
2102 l_ordered_date_low DATE;
2103 l_ordered_date_high DATE;
2104 l_line_id NUMBER;
2105 l_rsv_tbl Rsv_Tbl_Type;
2106 l_temp_rsv_tbl Rsv_Tbl_Type;
2107 l_temp_par_rsv_tbl Rsv_Tbl_Type;
2108 l_percent NUMBER;
2109 l_old_warehouse NUMBER;
2110 l_old_subinventory VARCHAR2(10);
2111 l_old_item_id NUMBER;
2112 l_index NUMBER :=0;
2113 l_cursor_id INTEGER;
2114 l_retval INTEGER;
2115 l_set_id NUMBER :=0;
2116 l_process_flag VARCHAR2(1);
2117 --3710133
2118 l_request_id NUMBER;
2119 l_msg_data VARCHAR2(2000);
2120 l_sales_order_id NUMBER;
2121 l_reserved_quantity NUMBER;
2122 l_reserved_quantity2 NUMBER;
2123
2124 CURSOR get_reservation_set IS
2125 SELECT reservation_set_id FROM oe_reservation_sets
2126 WHERE reservation_set_name = p_reserve_set_name;
2127
2128 CURSOR rsv_process_flag IS
2129 SELECT process_flag FROM oe_reservation_sets
2130 WHERE reservation_set_name = p_reserve_set_name;
2131 --
2132 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2133 --
2134 -- Moac
2135 l_single_org BOOLEAN := FALSE;
2136 l_old_org_id NUMBER := -99;
2137 l_org_id NUMBER;
2138 l_user_set_id NUMBER :=0;
2139 l_created_by NUMBER;
2140 BEGIN
2141 --Bug #4220950
2142 ERRBUF := 'Reserve Schedule Orders Request completed successfully';
2143 RETCODE := 0;
2144
2145 --3710133
2146 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
2147
2148 -- MOAC Start
2149 BEGIN
2150 IF p_reserve_set_name IS NOT NULL THEN
2151 SELECT created_by INTO l_created_by
2152 from OE_RESERVATION_SETS
2153 WHERE reservation_set_name = p_reserve_set_name;
2154 --AND CREATED_BY = FND_GLOBAL.USER_ID ;
2155 IF l_created_by <> FND_GLOBAL.USER_ID THEN
2156 Fnd_Message.set_name('ONT', 'ONT_RSV_SET_NOT_CREATED_BY_USR');
2157 Fnd_Message.Set_Token('SET_NAME', p_reserve_set_name );
2158 Oe_Msg_Pub.Add;
2159 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
2160 l_msg_data := Fnd_Message.get_string('ONT', 'ONT_RSV_SET_NOT_CREATED_BY_USR');
2161 FND_FILE.Put_Line(FND_FILE.LOG, l_msg_data);
2162 ERRBUF := l_msg_data;
2163 RETCODE := 2;
2164 IF l_debug_level > 0 THEN
2165 OE_DEBUG_PUB.Add('Error : Reservation set is not created by the current user',1);
2166 END IF;
2167 GOTO END_OF_PROCESS;
2168 END IF;
2169 /*
2170 IF l_user_set_id IS NULL THEN
2171 OE_DEBUG_PUB.Add('Error: user_set_id is null',1);
2172 END IF;
2173 */
2174 END IF;
2175 EXCEPTION
2176 WHEN NO_DATA_FOUND THEN
2177 NULL;
2178 /*
2179 Fnd_Message.set_name('ONT', 'ONT_RSV_SET_NOT_CREATED_BY_USR');
2180 Oe_Msg_Pub.Add;
2181 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
2182 l_msg_data := Fnd_Message.get_string('ONT', 'ONT_RSV_SET_NOT_CREATED_BY_USR');
2183 FND_FILE.Put_Line(FND_FILE.LOG, l_msg_data);
2184 ERRBUF := l_msg_data;
2185 RETCODE := 2;
2186 IF l_debug_level > 0 THEN
2187 OE_DEBUG_PUB.Add('Error : Reservation set is not created by the current user',1);
2188 END IF;
2189 GOTO END_OF_PROCESS;
2190 */
2191 WHEN OTHERS THEN
2192 NULL;
2193 END ;
2194 -- MOAC End
2195
2196 -- validating reservation mode
2197 /* Commented to allow 'FAIR' mode for multiple items
2198 IF p_reservation_mode = 'FAIR'
2199 AND p_inventory_item_id IS NULL
2200 THEN
2201 IF l_debug_level > 0 THEN
2202 OE_DEBUG_PUB.Add('Error : Item not supplied ',1);
2203 END IF;
2204 FND_FILE.Put_Line(FND_FILE.LOG, ' Concurrent request failed - item not supplied');
2205 ERRBUF := ' Concurrent request failed - item not supplied';
2206 RETCODE := 2;
2207 goto END_OF_PROCESS;
2208 */ -- Pack J
2209 IF p_reservation_mode = 'PERCENT'
2210 THEN
2211 --code change for bug 3738107
2212 IF p_percent IS NULL
2213 THEN
2214 Fnd_Message.set_name('ONT', 'ONT_RSV_PCT_NULL');
2215 Oe_Msg_Pub.Add;
2216 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
2217 l_msg_data := Fnd_Message.get_string('ONT', 'ONT_RSV_PCT_NULL');
2218 FND_FILE.Put_Line(FND_FILE.LOG, l_msg_data);
2219 ERRBUF := 'ONT_RSV_PCT_NULL';
2220 RETCODE := 2;
2221 IF l_debug_level > 0 THEN
2222 OE_DEBUG_PUB.Add('Error : Percentage is null ',1);
2223 END IF;
2224 goto END_OF_PROCESS;
2225 ELSIF p_percent > 100 OR p_percent < 1
2226 THEN
2227 Fnd_Message.set_name('ONT', 'ONT_RSV_PCT_INVALID');
2228 Oe_Msg_Pub.Add;
2229 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
2230 l_msg_data := Fnd_Message.get_string('ONT', 'ONT_RSV_PCT_INVALID');
2231 FND_FILE.Put_Line(FND_FILE.LOG, l_msg_data);
2232 ERRBUF := 'ONT_RSV_PCT_INVALID';
2233 RETCODE := 2;
2234 IF l_debug_level > 0 THEN
2235 OE_DEBUG_PUB.Add('Error : Percentage is lesser than 1 or greater than 100 ',1);
2236 END IF;
2237 goto END_OF_PROCESS;
2238 END IF;
2239 ELSIF (p_reserve_run_type = 'SIMULATE'
2240 OR p_reserve_run_type = 'CREATE_RESERVATION')
2241 AND p_reserve_set_name IS NULL
2242 THEN -- Pack J
2243 -- code change for bug 3738107
2244 Fnd_Message.set_name('ONT', 'ONT_RSV_SET_NOT_PROVIDED');
2245 Oe_Msg_Pub.Add;
2246 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
2247 l_msg_data := Fnd_Message.get_string('ONT', 'ONT_RSV_SET_NOT_PROVIDED');
2248 FND_FILE.Put_Line(FND_FILE.LOG, l_msg_data);
2249 ERRBUF := 'ONT_RSV_SET_NOT_PROVIDED';
2250 RETCODE := 2;
2251 IF l_debug_level > 0 THEN
2252 OE_DEBUG_PUB.Add('Error : Reservation Set Name is not provided ',1);
2253 END IF;
2254 goto END_OF_PROCESS;
2255 ELSIF p_reserve_set_name IS NOT NULL
2256 AND NVL(p_override_set,'N') = 'N'
2257 AND p_reserve_run_type <> 'CREATE_RESERVATION'
2258 THEN -- Pack J
2259 OPEN get_reservation_set;
2260 FETCH get_reservation_set INTO l_set_id;
2261 CLOSE get_reservation_set;
2262 IF l_set_id > 0 THEN
2263 -- code change for 3738107
2264 Fnd_Message.set_name('ONT', 'ONT_RSV_SET_EXISTS');
2265 Oe_Msg_Pub.Add;
2266 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
2267 l_msg_data := Fnd_Message.get_string('ONT', 'ONT_RSV_SET_EXISTS');
2268 FND_FILE.Put_Line(FND_FILE.LOG, l_msg_data);
2269 ERRBUF := 'ONT_RSV_SET_EXISTS';
2270 RETCODE := 2;
2271 IF l_debug_level > 0 THEN
2272 OE_DEBUG_PUB.Add('Error : Reservation Set Name exists ',1);
2273 END IF;
2274 goto END_OF_PROCESS;
2275 END IF;
2276 ELSIF p_reserve_set_name IS NOT NULL
2277 THEN -- Pack J --- p_reserve_run_type = 'CREATE_RESERVATION'
2278 OPEN rsv_process_flag;
2279 FETCH rsv_process_flag INTO l_process_flag;
2280 CLOSE rsv_process_flag;
2281 IF l_process_flag = 'Y' THEN
2282
2283 IF l_debug_level > 0 THEN
2284 OE_DEBUG_PUB.Add('Error : Reservation Set already processed ',1);
2285 END IF;
2286 FND_FILE.Put_Line(FND_FILE.LOG, ' Concurrent request failed - Reserevation Set already processed');
2287 ERRBUF := ' Concurrent request failed - Reserevation Set already processed';
2288 RETCODE := 2;
2289 goto END_OF_PROCESS;
2290 END IF;
2291 END IF;
2292 IF p_reservation_mode = 'FAIR'
2293 AND p_percent IS NOT NULL
2294 THEN
2295 IF l_debug_level > 0 THEN
2296 OE_DEBUG_PUB.Add('Warning : Percent is not valid for this reservation mode ',1);
2297 END IF;
2298 FND_FILE.Put_Line(FND_FILE.LOG, ' Percent is not valid for this reservation mode');
2299 ERRBUF := ' Percent is not valid for this reservation mode - hence not considered';
2300 RETCODE := 2;
2301 goto END_OF_PROCESS;
2302 END IF;
2303 G_RESERVATION_MODE := p_reservation_mode; -- Pack J
2304
2305 FND_FILE.Put_Line(FND_FILE.LOG, 'Parameters:');
2306 FND_FILE.Put_Line(FND_FILE.LOG, ' Use_reservation_time_fence = '||
2307 p_use_reservation_time_fence);
2308 FND_FILE.Put_Line(FND_FILE.LOG, ' order_number_low = '||
2309 p_order_number_low);
2310 FND_FILE.Put_Line(FND_FILE.LOG, ' order_number_high = '||
2311 p_order_number_high);
2312 FND_FILE.Put_Line(FND_FILE.LOG, ' Customer = '||
2313 p_customer_id);
2314 FND_FILE.Put_Line(FND_FILE.LOG, ' order_type = '||
2315 p_order_type);
2316 FND_FILE.Put_Line(FND_FILE.LOG, ' Warehouse = '||
2317 p_Warehouse);
2318 FND_FILE.Put_Line(FND_FILE.LOG, ' request_date_low = '||
2319 p_request_date_low);
2320 FND_FILE.Put_Line(FND_FILE.LOG, ' request_date_high = '||
2321 p_request_date_high);
2322 FND_FILE.Put_Line(FND_FILE.LOG, ' schedule_date_low = '||
2323 p_schedule_ship_date_low);
2324 FND_FILE.Put_Line(FND_FILE.LOG, ' schedule_date_high = '||
2325 p_schedule_ship_date_high);
2326 FND_FILE.Put_Line(FND_FILE.LOG, ' ordered_date_low = '||
2327 p_ordered_date_low);
2328 FND_FILE.Put_Line(FND_FILE.LOG, ' ordered_date_high = '||
2329 p_ordered_date_high);
2330 FND_FILE.Put_Line(FND_FILE.LOG, ' Demand Class = '||
2331 p_demand_class_code);
2332 FND_FILE.Put_Line(FND_FILE.LOG, ' item = '||
2333 p_inventory_item_id);
2334 FND_FILE.Put_Line(FND_FILE.LOG, ' Planning Priority = '||
2335 p_Planning_priority);
2336 FND_FILE.Put_Line(FND_FILE.LOG, ' Booked Flag = '||
2337 p_booked);
2338 FND_FILE.Put_Line(FND_FILE.LOG, ' Order By = '||
2339 p_order_by);
2340 FND_FILE.Put_Line(FND_FILE.LOG, ' Reservation Mode = '||
2341 p_reservation_mode);
2342 FND_FILE.Put_Line(FND_FILE.LOG, ' Percent = '||
2343 p_percent);
2344 FND_FILE.Put_Line(FND_FILE.LOG, ' Shipment Priority = '||
2345 p_shipment_priority);
2346 FND_FILE.Put_Line(FND_FILE.LOG, ' Reserve Run Type = '||
2347 p_reserve_run_type);
2348 FND_FILE.Put_Line(FND_FILE.LOG, ' Reserve Set Name = '||
2349 p_reserve_set_name);
2350
2351 IF l_debug_level > 0 THEN
2352 OE_DEBUG_PUB.Add('Inside the Reserve Order Concurrent Program',1);
2353 END IF;
2354
2355 SELECT FND_DATE.Canonical_To_Date(p_request_date_low),
2356 FND_DATE.Canonical_To_Date(p_request_date_high),
2357 FND_DATE.Canonical_To_Date(p_schedule_ship_date_low),
2358 FND_DATE.Canonical_To_Date(p_schedule_ship_date_high),
2359 FND_DATE.Canonical_To_Date(p_schedule_arrival_date_low),
2360 FND_DATE.Canonical_To_Date(p_schedule_arrival_date_high),
2361 FND_DATE.Canonical_To_Date(p_ordered_date_low),
2362 FND_DATE.Canonical_To_Date(p_ordered_date_high)
2363 INTO l_request_date_low,
2364 l_request_date_high,
2365 l_schedule_ship_date_low,
2366 l_schedule_ship_date_high,
2367 l_schedule_arrival_date_low,
2368 l_schedule_arrival_date_high,
2369 l_ordered_date_low,
2370 l_ordered_date_high
2371 FROM DUAL;
2372
2373 -- Moac Start
2374 IF MO_GLOBAL.get_access_mode = 'S' THEN
2375 l_single_org := TRUE;
2376 ELSIF p_org_id IS NOT NULL THEN
2377 l_single_org := TRUE;
2378 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
2379 END IF;
2380 -- Moac End
2381
2382 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
2383 IF p_reserve_run_type ='CREATE_RESERVATION' THEN --Pack J
2384 l_stmt := 'SELECT Line_id FROM OE_Reservation_Sets_V rset WHERE '||
2385 'rset.reservation_set_name=:reservation_set_name';
2386
2387 ELSE
2388 --Moac
2389 l_stmt := 'SELECT Line_id, l.org_id FROM OE_ORDER_LINES l, OE_ORDER_HEADERS_ALL h ,MTL_SYSTEM_ITEMS msi ';
2390 l_stmt := l_stmt|| ' WHERE NVL(h.cancelled_flag,'||'''N'''||') <> ' ||'''Y'''||
2391 ' AND h.header_id = l.header_id'||
2392 ' AND h.open_flag = '||'''Y'''||
2393 ' AND NVL(l.cancelled_flag,'||'''N'''||') <> '||'''Y'''||
2394 ' AND NVL(l.line_category_code,'||'''ORDER'''||') <> '||'''RETURN''';
2395 IF NVL(p_booked,'*') = 'Y' THEN
2396 l_stmt := l_stmt||' AND h.booked_flag = '||'''Y''';
2397 ELSIF NVL(p_booked,'*') = 'N' THEN
2398 l_stmt := l_stmt||' AND h.booked_flag = '||'''N''';
2399 END IF;
2400
2401 -- Moac Start
2402 IF p_org_id IS NOT NULL THEN
2403 l_stmt := l_stmt ||' AND l.org_id = :org_id'; -- p_org_id
2404 END IF;
2405 -- Moac End
2406
2407 IF p_order_number_low IS NOT NULL THEN
2408 l_stmt := l_stmt ||' AND h.order_number >=:order_number_low'; -- p_order_number_low
2409 END IF;
2410 IF p_order_number_high IS NOT NULL THEN
2411 l_stmt := l_stmt ||' AND h.order_number <=:order_number_high'; -- p_order_number_high
2412 END IF;
2413 IF p_customer_id IS NOT NULL THEN
2414 l_stmt := l_stmt ||' AND h.sold_to_org_id =:customer_id'; --p_customer_id
2415 END IF;
2416 IF p_order_type IS NOT NULL THEN
2417 l_stmt := l_stmt ||' AND h.order_type_id =:order_type'; --p_order_type
2418 END IF;
2419 IF l_ordered_date_low IS NOT NULL THEN
2420 l_stmt := l_stmt ||' AND h.ordered_date >=:ordered_date_low'; --l_ordered_date_low
2421 END IF;
2422 IF l_ordered_date_high IS NOT NULL THEN
2423 l_stmt := l_stmt ||' AND h.ordered_date <=:ordered_date_high'; --l_ordered_date_high;
2424 END IF;
2425 IF p_line_type_id IS NOT NULL THEN
2426 l_stmt := l_stmt ||' AND l.line_type_id =:line_type_id'; --p_line_type_id
2427 END IF;
2428 l_stmt := l_stmt ||' AND l.open_flag = '||'''Y''';
2429 IF p_warehouse IS NOT NULL THEN
2430 l_stmt := l_stmt ||' AND l.ship_from_org_id =:warehouse'; --p_warehouse
2431 END IF;
2432 IF l_request_date_low IS NOT NULL THEN
2433 l_stmt := l_stmt ||' AND l.request_date >=:request_date_low'; --l_request_date_low;
2434 END IF;
2435 IF l_request_date_high IS NOT NULL THEN
2436 l_stmt := l_stmt ||' AND l.request_date <=:request_date_high'; --l_request_date_high
2437 END IF;
2438 IF l_schedule_ship_date_low IS NOT NULL THEN
2439 l_stmt := l_stmt ||' AND l.schedule_ship_date >=:schedule_ship_date_low'; --l_schedule_ship_date_low
2440 END IF;
2441 IF l_schedule_ship_date_high IS NOT NULL THEN
2442 l_stmt := l_stmt ||' AND l.schedule_ship_date <=:schedule_ship_date_high'; --l_schedule_ship_date_high
2443 END IF;
2444 IF l_schedule_arrival_date_low IS NOT NULL THEN
2445 l_stmt := l_stmt ||' AND l.Schedule_Arrival_Date >=:schedule_arrival_date_low'; --l_schedule_arrival_date_low
2446 END IF;
2447 IF l_schedule_arrival_date_high IS NOT NULL THEN
2448 l_stmt := l_stmt ||' AND l.Schedule_Arrival_Date <=:schedule_arrival_date_high'; --l_schedule_arrival_date_high
2449 END IF;
2450 IF p_inventory_item_id IS NOT NULL THEN
2451 l_stmt := l_stmt ||' AND l.inventory_item_id =:inventory_item_id'; -- p_inventory_item_id
2452 END IF;
2453 IF p_demand_class_code IS NOT NULL THEN
2454 l_stmt := l_stmt ||' AND NVL(l.demand_class_code,'||'''-99'''||') =:demand_class_code'; --p_demand_class_code
2455 END IF;
2456 IF p_planning_priority IS NOT NULL THEN
2457 l_stmt := l_stmt ||' AND NVL(l.planning_priority,-99)=:planning_priority'; --p_planning_priority
2458 END IF;
2459 IF p_shipment_priority IS NOT NULL THEN
2460 l_stmt := l_stmt ||' AND NVL(l.shipment_priority_code, '||'''XX'''||')=:shipment_priority'; --p_shipment_priority
2461 END IF;
2462 IF p_selected_ids IS NOT NULL THEN --Pack J
2463 --l_stmt := l_stmt ||' AND l.line_id IN(:selected_ids)'; --p_selected_ids
2464 --R12.MOAC
2465 l_stmt := l_stmt ||' AND l.line_id IN(SELECT line_id FROM
2466 oe_rsv_set_details WHERE reservation_set_id=:set_id)';
2467 END IF;
2468 IF p_reservation_mode = 'PARTIAL_ONLY_UNRESERVED' THEN -- Pack J
2469 l_stmt := l_stmt ||' AND NOT Exists (SELECT 1 FROM mtl_reservations mrs '
2470 ||' WHERE l.line_id = mrs.demand_source_line_id)';
2471 ELSIF p_reservation_mode ='PARTIAL' THEN
2472 l_stmt := l_stmt ||' AND (NOT Exists (SELECT 1 FROM mtl_reservations mrs '
2473 ||' WHERE l.line_id = mrs.demand_source_line_id)'
2474 ||' OR l. ordered_quantity > (select sum(INV_CONVERT.INV_UM_CONVERT( '
2475 ||' l.inventory_item_id, 5, reservation_quantity, reservation_uom_code,'
2476 ||' l.order_quantity_uom, NULL, NULL)) from mtl_reservations '
2477 ||' where demand_source_line_id = l.line_id))';
2478 END IF;
2479
2480 l_stmt := l_stmt|| ' AND l.shipped_quantity IS NULL'||
2481 ' AND l.source_type_code = '||'''INTERNAL'''||
2482 ' AND l.schedule_ship_date IS NOT NULL'||
2483 ' AND NVL(l.shippable_flag,'||'''N'''||') = '||'''Y'''||
2484 ' AND l.ship_from_org_id = msi.organization_id'||
2485 ' AND l.inventory_item_id = msi.inventory_item_id'||
2486 ' AND msi.service_item_flag <> '||'''Y'''||
2487 ' AND msi.reservable_type <> 2';
2488 IF p_order_by IS NOT NULL THEN
2489 -- start for bug 3476226
2490 -- the following IF was added as ORDERED_DATE is not present im OE_ORDER_LINES l but in OE_ORDER_HEADERS h
2491 IF UPPER(p_order_by) = 'ORDERED_DATE' THEN
2492 l_stmt := l_stmt || ' ORDER BY l.inventory_item_id,l.ship_from_org_id,l.subinventory,h.ORDERED_DATE';
2493 ELSE
2494 l_stmt := l_stmt || ' ORDER BY l.inventory_item_id,l.ship_from_org_id,l.subinventory,l.'|| p_order_by;
2495 END IF;
2496 -- end for bug 3476226
2497 ELSE
2498 l_stmt := l_stmt || ' ORDER BY l.inventory_item_id,l.ship_from_org_id,l.subinventory';
2499 END IF;
2500 END IF;
2501 --OE_DEBUG_PUB.Add(substr(l_stmt,1,length(l_stmt)),1);
2502 DBMS_SQL.PARSE(l_cursor_id,l_stmt,DBMS_SQL.NATIVE);
2503
2504 -- Bind variables
2505 -- 4287740
2506 IF p_reserve_run_type <> 'CREATE_RESERVATION' OR p_reserve_run_type IS NULL THEN
2507
2508 -- Moac start
2509 IF p_org_id IS NOT NULL THEN
2510 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':org_id',p_org_id);
2511 END IF;
2512 -- Moac end
2513
2514 IF p_order_number_low IS NOT NULL THEN
2515 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_number_low',p_order_number_low);
2516 END IF;
2517 IF p_order_number_high IS NOT NULL THEN
2518 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_number_high',p_order_number_high);
2519 END IF;
2520 IF p_customer_id IS NOT NULL THEN
2521 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':customer_id',p_customer_id);
2522 END IF;
2523 IF p_order_type IS NOT NULL THEN
2524 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_type',p_order_type);
2525 END IF;
2526 IF l_ordered_date_low IS NOT NULL THEN
2527 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':ordered_date_low',l_ordered_date_low);
2528 END IF;
2529 IF l_ordered_date_high IS NOT NULL THEN
2530 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':ordered_date_high',l_ordered_date_high);
2531 END IF;
2532 IF p_line_type_id IS NOT NULL THEN
2533 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':line_type_id',p_line_type_id);
2534 END IF;
2535 IF p_warehouse IS NOT NULL THEN
2536 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':warehouse',p_warehouse);
2537 END IF;
2538 IF l_request_date_low IS NOT NULL THEN
2539 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':request_date_low',l_request_date_low);
2540 END IF;
2541 IF l_request_date_high IS NOT NULL THEN
2542 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':request_date_high',l_request_date_high);
2543 END IF;
2544 IF l_schedule_ship_date_low IS NOT NULL THEN
2545 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':schedule_ship_date_low',l_schedule_ship_date_low);
2546 END IF;
2547 IF l_schedule_ship_date_high IS NOT NULL THEN
2548 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':schedule_ship_date_high',l_schedule_ship_date_high);
2549 END IF;
2550 IF l_schedule_arrival_date_low IS NOT NULL THEN
2551 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':schedule_arrival_date_low',l_schedule_arrival_date_low);
2552 END IF;
2553 IF l_schedule_arrival_date_high IS NOT NULL THEN
2554 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':schedule_arrival_date_high',l_schedule_arrival_date_high);
2555 END IF;
2556 IF p_inventory_item_id IS NOT NULL THEN
2557 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':inventory_item_id',p_inventory_item_id);
2558 END IF;
2559 IF p_demand_class_code IS NOT NULL THEN
2560 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':demand_class_code',p_demand_class_code);
2561 END IF;
2562 IF p_planning_priority IS NOT NULL THEN
2563 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':planning_priority',p_planning_priority);
2564 END IF;
2565 IF p_shipment_priority IS NOT NULL THEN
2566 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':shipment_priority',p_shipment_priority);
2567 END IF;
2568 --R12.MOAC
2569 IF p_selected_ids IS NOT NULL THEN
2570 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':set_id',p_selected_ids);
2571 END IF;
2572 ELSIF p_reserve_run_type ='CREATE_RESERVATION'
2573 AND p_reserve_set_name IS NOT NULL THEN --Pack J
2574 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':reservation_set_name',p_reserve_set_name);
2575 END IF;
2576
2577 -- Define the output variables
2578 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_line_id);
2579
2580 -- OE_DEBUG_PUB.Add(length(l_stmt));
2581 IF l_debug_level > 0 THEN
2582 OE_DEBUG_PUB.Add(substr(l_stmt,1,length(l_stmt)),1);
2583 END IF;
2584 l_retval := DBMS_SQL.EXECUTE(l_cursor_id);
2585
2586 -- OPEN l_ref_cur_lines FOR l_stmt;
2587 LOOP
2588 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
2589 EXIT;
2590 END IF;
2591 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_line_id);
2592
2593 FND_FILE.Put_Line(FND_FILE.LOG, ' ');
2594 FND_FILE.Put_Line(FND_FILE.LOG, '***** Processing Line id '||
2595 l_Line_id||' *****');
2596 l_return_status := FND_API.G_RET_STS_SUCCESS;
2597
2598 OE_LINE_UTIL.Lock_Row
2599 (p_line_id => l_Line_id,
2600 p_x_line_rec => l_line_rec,
2601 x_return_status => l_return_status);
2602
2603 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2604 IF l_debug_level > 0 THEN
2605 OE_DEBUG_PUB.Add('Lock row returned with error',1);
2606 END IF;
2607 END IF;
2608
2609 -- Updating the value of the Schedule Action Code
2610 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2611
2612 l_line_rec.schedule_action_code := OESCH_ACT_RESERVE;
2613 IF l_debug_level > 0 THEN
2614 OE_DEBUG_PUB.Add('set mesg context line_id:'||l_line_rec.line_id);
2615 OE_DEBUG_PUB.Add('set mesg context header_id:'||l_line_rec.header_id);
2616 END IF;
2617
2618 OE_MSG_PUB.Set_Msg_Context(
2619 p_entity_code => 'LINE'
2620 ,p_entity_id => l_line_rec.line_id
2621 ,p_header_id => l_line_rec.header_id
2622 ,p_line_id => l_line_rec.line_id
2623 ,p_order_source_id => l_line_rec.order_source_id
2624 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
2625 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
2626 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
2627 ,p_change_sequence => l_line_rec.change_sequence
2628 ,p_source_document_type_id => l_line_rec.source_document_type_id
2629 ,p_source_document_id => l_line_rec.source_document_id
2630 ,p_source_document_line_id => l_line_rec.source_document_line_id
2631 );
2632 IF NVL(p_reserve_run_type,'RESERVE') <> 'CREATE_RESERVATION' THEN -- Pack J
2633 Reserve_Eligible(
2634 p_line_rec => l_line_rec
2635 ,p_use_reservation_time_fence => p_use_reservation_time_fence
2636 ,x_return_status => l_return_status
2637 );
2638
2639 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2640 IF l_debug_level > 0 THEN
2641 OE_DEBUG_PUB.Add('Require Reservation returned with error for
2642 Line id:'||l_line_rec.line_id,1);
2643 END IF;
2644
2645 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2646 IF l_debug_level > 0 THEN
2647 OE_DEBUG_PUB.Add('Require Reservation returned with error for
2648 Line id:'||l_line_rec.line_id,1);
2649 OE_DEBUG_PUB.Add(substr(sqlerrm, 1, 2000));
2650 END IF;
2651
2652 END IF;
2653 ELSE
2654 l_return_status := FND_API.G_RET_STS_SUCCESS;
2655 END IF;
2656
2657 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2658
2659 /* Here we will call the Procedure for Reservation */
2660
2661 IF p_reservation_mode IS NULL
2662 AND (p_reserve_run_type ='RESERVE'
2663 OR p_reserve_run_type IS NULL) THEN
2664 Create_Reservation(
2665 p_line_rec => l_line_rec
2666 ,x_return_status => l_return_status
2667 );
2668
2669 -- 4287740 Start
2670 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2671 IF l_debug_level > 0 THEN
2672 OE_DEBUG_PUB.Add('Create Reservation returned with
2673 Expected error for Line id:' ||l_line_rec.line_id,1);
2674 END IF;
2675 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2676 IF l_debug_level > 0 THEN
2677 OE_DEBUG_PUB.Add('Create Reservation returned with
2678 Unexpected error for Line id:'||l_line_rec.line_id,1);
2679 OE_DEBUG_PUB.Add(substr(sqlerrm, 1, 2000));
2680 END IF;
2681 ELSE
2682 COMMIT;
2683 END IF;
2684 -- 4287740 End
2685
2686 ELSE
2687 l_index := l_rsv_tbl.COUNT +1;
2688 l_rsv_tbl(l_index).line_id := l_line_rec.line_id;
2689 l_rsv_tbl(l_index).header_id := l_line_rec.header_id;
2690 l_rsv_tbl(l_index).inventory_item_id := l_line_rec.inventory_item_id;
2691 l_rsv_tbl(l_index).ordered_qty := l_line_rec.ordered_quantity;
2692 l_rsv_tbl(l_index).ordered_qty_UOM := l_line_rec.order_quantity_uom;
2693 l_rsv_tbl(l_index).ship_from_org_id := l_line_rec.ship_from_org_id;
2694 l_rsv_tbl(l_index).subinventory := l_line_rec.subinventory;
2695 l_rsv_tbl(l_index).schedule_ship_date := l_line_rec.schedule_ship_date;
2696 l_rsv_tbl(l_index).source_document_type_id
2697 := l_line_rec.source_document_type_id;
2698 -- Pack J
2699 l_rsv_tbl(l_index).order_source_id := l_line_rec.order_source_id;
2700 l_rsv_tbl(l_index).orig_sys_document_ref := l_line_rec.orig_sys_document_ref;
2701 l_rsv_tbl(l_index).orig_sys_line_ref := l_line_rec.orig_sys_line_ref;
2702 l_rsv_tbl(l_index).orig_sys_shipment_ref := l_line_rec.orig_sys_shipment_ref;
2703 l_rsv_tbl(l_index).change_sequence := l_line_rec.change_sequence;
2704 l_rsv_tbl(l_index).source_document_id := l_line_rec.source_document_id;
2705 l_rsv_tbl(l_index).source_document_line_id := l_line_rec.source_document_line_id;
2706 l_rsv_tbl(l_index).shipped_quantity := l_line_rec.shipped_quantity;
2707 l_rsv_tbl(l_index).shipped_quantity2 := l_line_rec.shipped_quantity2; -- INVCONV
2708 l_rsv_tbl(l_index).ordered_qty2 := l_line_rec.ordered_quantity2; -- INVCONV
2709 --4759251
2710 l_rsv_tbl(l_index).org_id := l_line_rec.org_id;
2711 END IF;
2712
2713 --4287740 Commented the below code as it has been moved up
2714 /*
2715 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2716 IF l_debug_level > 0 THEN
2717 OE_DEBUG_PUB.Add('Create Reservation returned with
2718 Expected error for Line id:' ||l_line_rec.line_id,1);
2719 END IF;
2720 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2721 IF l_debug_level > 0 THEN
2722 OE_DEBUG_PUB.Add('Create Reservation returned with
2723 Unexpected error for Line id:'||l_line_rec.line_id,1);
2724 OE_DEBUG_PUB.Add(substr(sqlerrm, 1, 2000));
2725 END IF;
2726 ELSE
2727 COMMIT;
2728 END IF;
2729 */
2730
2731 END IF; -- End of Create_Reservation
2732
2733 END IF; -- End of Lock Row
2734
2735 END LOOP; -- End of lines_cur
2736 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
2737
2738 --3710133
2739 --4287740 Added Not null check for reservation mode
2740 IF p_reservation_mode IS NOT NULL AND l_rsv_tbl.count = 0
2741 THEN
2742
2743 Fnd_Message.set_name('ONT', 'ONT_NO_LINES_RSV_ELIGIBLE');
2744 Oe_Msg_Pub.Add;
2745 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
2746 l_msg_data := Fnd_Message.get_string('ONT', 'ONT_NO_LINES_RSV_ELIGIBLE');
2747 FND_FILE.Put_Line(FND_FILE.LOG, l_msg_data);
2748 RETCODE := 1;
2749 IF l_debug_level > 0 THEN
2750 OE_DEBUG_PUB.Add('Warning : No lines were eligible for
2751 reservation.',1);
2752 END IF;
2753
2754 IF p_reserve_set_name IS NOT NULL
2755 THEN
2756
2757 Fnd_Message.set_name('ONT', 'ONT_RSV_SET_NOT_CREATED');
2758 Oe_Msg_Pub.Add;
2759 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
2760 l_msg_data := Fnd_Message.get_string('ONT', 'ONT_RSV_SET_NOT_CREATED');
2761 FND_FILE.Put_Line(FND_FILE.LOG, l_msg_data);
2762 RETCODE := 1;
2763 IF l_debug_level > 0 THEN
2764 OE_DEBUG_PUB.Add('Warning : The set is not created as no eligible
2765 lines were selected',1);
2766 END IF;
2767 END IF;
2768
2769 GOTO END_OF_PROCESS;
2770 END IF;
2771
2772
2773 IF p_reserve_run_type = 'CREATE_RESERVATION' THEN -- Pack J
2774 -- Get the set id
2775 OPEN get_reservation_set;
2776 FETCH get_reservation_set INTO l_set_id;
2777 CLOSE get_reservation_set;
2778 Validate_and_Reserve_for_Set
2779 (p_x_rsv_tbl => l_rsv_tbl
2780 ,p_reservation_set_id => l_set_id
2781 ,x_return_status => l_return_status);
2782 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2783 Update_Reservation_Set
2784 (p_reservation_set_id => l_set_id
2785 ,x_return_status => l_return_status);
2786 END IF;
2787 ELSE
2788 l_percent := p_percent;
2789 FOR I IN 1..l_rsv_tbl.COUNT
2790 LOOP
2791 IF l_old_item_id IS NULL THEN -- Pack J
2792 --IF l_old_warehouse IS NULL THEN
2793 l_old_item_id := l_rsv_tbl(I).inventory_item_id;
2794 l_old_warehouse := l_rsv_tbl(I).ship_from_org_id;
2795 l_old_subinventory := l_rsv_tbl(I).subinventory;
2796 l_old_org_id := l_rsv_tbl(I).org_id; -- 4759251
2797 END IF;
2798 IF OE_GLOBALS.Equal(l_old_item_id, l_rsv_tbl(I).inventory_item_id)
2799 AND OE_GLOBALS.Equal(l_old_warehouse, l_rsv_tbl(I).ship_from_org_id)
2800 AND OE_GLOBALS.Equal(l_old_subinventory, l_rsv_tbl(I).subinventory)
2801 AND OE_GLOBALS.Equal(l_old_org_id,l_rsv_tbl(I).org_id) THEN --4759251
2802 -- 6814153
2803 IF p_reservation_mode ='PARTIAL' THEN
2804 l_sales_order_id :=
2805 Oe_Schedule_Util.Get_mtl_sales_order_id(l_rsv_tbl(I).HEADER_ID);
2806 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
2807 ,p_line_id => l_rsv_tbl(I).line_id
2808 ,p_org_id => l_rsv_tbl(I).ship_from_org_id
2809 ,x_reserved_quantity => l_reserved_quantity
2810 ,x_reserved_quantity2 => l_reserved_quantity2);
2811
2812 -- Derive the quantity to be reserved
2813 l_rsv_tbl(I).derived_reserved_qty
2814 := l_rsv_tbl(I).ordered_qty - NVL(l_reserved_quantity,0);
2815 l_rsv_tbl(I).derived_reserved_qty2 -- INVCONV
2816 := l_rsv_tbl(I).ordered_qty2 - NVL(l_reserved_quantity2,0);
2817 -- Partial Reservation
2818 -- Reservation exists for the line. Set the flag
2819 IF l_reserved_quantity > 0 THEN
2820 l_rsv_tbl(I).reservation_exists := 'Y';
2821 END IF;
2822 END IF;
2823 IF NVL(l_rsv_tbl(I).reservation_exists,'N') = 'Y'
2824 AND p_partial_preference = 'Y' THEN
2825 l_temp_par_rsv_tbl(l_temp_par_rsv_tbl.COUNT + 1) := l_rsv_tbl(I);
2826 ELSE
2827 l_temp_rsv_tbl(l_temp_rsv_tbl.COUNT + 1) := l_rsv_tbl(I);
2828 END IF;
2829 ELSE
2830 IF l_temp_rsv_tbl.COUNT > 0 OR l_temp_par_rsv_tbl.COUNT > 0 THEN -- 6814153
2831 IF l_temp_rsv_tbl.COUNT > 0 THEN
2832 Prepare_And_Reserve(p_rsv_tbl => l_temp_rsv_tbl,
2833 p_percent => l_percent,
2834 p_reservation_mode => p_reservation_mode,
2835 p_reserve_run_type => p_reserve_run_type,
2836 p_reserve_set_name => p_reserve_set_name);
2837
2838 IF p_reserve_run_type ='SIMULATE'
2839 AND OE_GLOBALS.Equal(l_old_item_id, l_rsv_tbl(I).inventory_item_id)
2840 AND OE_GLOBALS.Equal(l_old_warehouse, l_rsv_tbl(I).ship_from_org_id)
2841 AND OE_GLOBALS.Equal(l_old_org_id,l_rsv_tbl(I).org_id) -- 4759251
2842 AND NOT OE_GLOBALS.Equal(l_old_subinventory, l_rsv_tbl(I).subinventory) THEN
2843 G_Total_Consumed := G_Total_Consumed + G_Consumed_for_Lot;
2844 G_Consumed_for_Lot := 0;
2845 G_Total_Consumed2 := G_Total_Consumed2 + G_Consumed_for_Lot2; -- INVCONV from code review by AK
2846 G_Consumed_for_Lot2 := 0;
2847
2848 ELSE
2849 G_Total_Consumed :=0;
2850 G_Consumed_for_Lot := 0;
2851 G_Total_Consumed2 :=0; -- INVCONV from code review by AK
2852 G_Consumed_for_Lot2 := 0;
2853 END IF;
2854 END IF;
2855 Prepare_And_Reserve(p_rsv_tbl => l_temp_rsv_tbl,
2856 p_percent => l_percent,
2857 p_reservation_mode => p_reservation_mode,
2858 p_reserve_run_type => p_reserve_run_type,
2859 p_reserve_set_name => p_reserve_set_name);
2860
2861 IF p_reserve_run_type ='SIMULATE'
2862 AND OE_GLOBALS.Equal(l_old_item_id, l_rsv_tbl(I).inventory_item_id)
2863 AND OE_GLOBALS.Equal(l_old_warehouse, l_rsv_tbl(I).ship_from_org_id)
2864 AND OE_GLOBALS.Equal(l_old_org_id,l_rsv_tbl(I).org_id) -- 4759251
2865 AND NOT OE_GLOBALS.Equal(l_old_subinventory, l_rsv_tbl(I).subinventory) THEN
2866 G_Total_Consumed := G_Total_Consumed + G_Consumed_for_Lot;
2867 G_Consumed_for_Lot := 0;
2868 G_Total_Consumed2 := G_Total_Consumed2 + G_Consumed_for_Lot2; -- INVCONV from code review by AK
2869 G_Consumed_for_Lot2 := 0;
2870
2871 ELSE
2872 G_Total_Consumed :=0;
2873 G_Consumed_for_Lot := 0;
2874 G_Total_Consumed2 :=0; -- INVCONV from code review by AK
2875 G_Consumed_for_Lot2 := 0;
2876 END IF;
2877 END IF;
2878 COMMIT;
2879
2880 l_temp_rsv_tbl.DELETE;
2881 l_old_item_id := l_rsv_tbl(I).inventory_item_id; --Pack J
2882 l_old_warehouse := l_rsv_tbl(I).ship_from_org_id;
2883 l_old_subinventory := l_rsv_tbl(I).subinventory;
2884 l_old_org_id := l_rsv_tbl(I).org_id;
2885 -- l_temp_rsv_tbl(1) := l_rsv_tbl(I);
2886 IF p_reservation_mode ='PARTIAL' THEN
2887 l_sales_order_id :=
2888 Oe_Schedule_Util.Get_mtl_sales_order_id(l_rsv_tbl(I).HEADER_ID);
2889 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
2890 ,p_line_id => l_rsv_tbl(I).line_id
2891 ,p_org_id => l_rsv_tbl(I).ship_from_org_id
2892 ,x_reserved_quantity => l_reserved_quantity
2893 ,x_reserved_quantity2 => l_reserved_quantity2);
2894
2895 -- Derive the quantity to be reserved
2896 l_rsv_tbl(I).derived_reserved_qty
2897 := l_rsv_tbl(I).ordered_qty - NVL(l_reserved_quantity,0);
2898 l_rsv_tbl(I).derived_reserved_qty2 -- INVCONV
2899 := l_rsv_tbl(I).ordered_qty2 - NVL(l_reserved_quantity2,0);
2900 -- Partial Reservation
2901 -- Reservation exists for the line. Set the flag
2902 IF l_reserved_quantity > 0 THEN
2903 l_rsv_tbl(I).reservation_exists := 'Y';
2904 END IF;
2905 END IF;
2906
2907 IF NVL(l_rsv_tbl(I).reservation_exists,'N') = 'Y'
2908 AND p_partial_preference = 'Y' THEN
2909 l_temp_par_rsv_tbl(1) := l_rsv_tbl(I);
2910 ELSE
2911 l_temp_rsv_tbl(1) := l_rsv_tbl(I);
2912 END IF;
2913 END IF;
2914
2915 END LOOP;
2916 IF l_temp_rsv_tbl.COUNT > 0 OR l_temp_par_rsv_tbl.COUNT > 0 THEN --- 6814153
2917 IF l_temp_par_rsv_tbl.COUNT > 0 THEN
2918 Prepare_And_Reserve(p_rsv_tbl => l_temp_par_rsv_tbl,
2919 p_percent => l_percent,
2920 p_reservation_mode => p_reservation_mode,
2921 p_reserve_run_type => p_reserve_run_type,
2922 p_reserve_set_name => p_reserve_set_name);
2923 IF p_reserve_run_type ='SIMULATE' THEN
2924 G_Total_Consumed := G_Total_Consumed + G_Consumed_for_Lot;
2925 G_Consumed_for_Lot := 0;
2926 G_Total_Consumed2 := G_Total_Consumed2 + G_Consumed_for_Lot2; -- INVCONV from code review by AK
2927 G_Consumed_for_Lot2 := 0;
2928 ELSE
2929 G_Total_Consumed :=0;
2930 G_Consumed_for_Lot := 0;
2931 G_Total_Consumed2 :=0;
2932 G_Consumed_for_Lot2 := 0;
2933 END IF;
2934 END IF;
2935 Prepare_And_Reserve(p_rsv_tbl => l_temp_rsv_tbl,
2936 p_percent => l_percent,
2937 p_reservation_mode => p_reservation_mode,
2938 p_reserve_run_type => p_reserve_run_type,
2939 p_reserve_set_name => p_reserve_set_name);
2940
2941
2942 END IF;
2943 END IF; -- Pack J
2944 --R12.MOAC
2945 IF p_selected_ids IS NOT NULL THEN
2946 DELETE FROM oe_rsv_set_details
2947 WHERE reservation_set_id = p_selected_ids;
2948 END IF;
2949
2950 COMMIT;
2951
2952
2953 <<END_OF_PROCESS>>
2954 IF l_debug_level > 0 THEN
2955 OE_DEBUG_PUB.Add('Exiting Reserve procedure ',1);
2956 END IF;
2957
2958
2959 EXCEPTION
2960 WHEN OTHERS THEN
2961 IF l_debug_level > 0 THEN
2962 OE_DEBUG_PUB.Add('Inside the When Others Execption',1);
2963 OE_DEBUG_PUB.Add(substr(sqlerrm, 1, 2000));
2964 END IF;
2965 END Reserve;
2966
2967
2968 END OE_RESERVE_CONC;