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