[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_IMPORT_RESERVE_PVT
Source
1 PACKAGE BODY OE_ORDER_IMPORT_RESERVE_PVT AS
2 /* $Header: OEXVIMRB.pls 120.3.12010000.2 2008/11/13 15:07:11 vmachett ship $ */
3
4 /*
5 ---------------------------------------------------------------
6 -- Start of Comments
7 -- API name OE_ORDER_IMPORT_RESERVE_PVT
8 -- Type Private
9 -- Purpose Inventory Reservation
10 -- Function
11 -- Pre-reqs
12 -- Parameters
13 -- Version Current version = 1.0
14 -- Initial version = 1.0
15 --
16 -- Notes:
17 --
18 -- End of Comments
19 ------------------------------------------------------------------
20 */
21
22 /* ------------------------------------------------------------------
23 Procedure: Reserve_Inventory
24 ------------------------------------------------------------------
25 */
26 G_ORDER_NUMBER NUMBER;
27 G_ORIG_SYS_DOCUMENT_REF VARCHAR2(50);
28 PROCEDURE Reserve_Inventory (
29 p_header_rec IN OE_Order_Pub.Header_Rec_Type
30 ,p_line_tbl IN OE_Order_Pub.Line_Tbl_Type
31 ,p_reservation_tbl IN OE_Order_Pub.Reservation_Tbl_Type
32 ,p_header_val_rec IN OE_Order_Pub.Header_Val_Rec_Type
33 ,p_line_val_tbl IN OE_Order_Pub.Line_Val_Tbl_Type
34 ,p_reservation_val_tbl IN OE_Order_Pub.Reservation_Val_Tbl_Type
35 ,p_return_status OUT NOCOPY VARCHAR2
36
37 ) IS
38 l_header_rec OE_Order_Pub.Header_Rec_Type;
39 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
40 l_reservation_tbl OE_Order_Pub.Reservation_Tbl_Type;
41 l_header_val_rec OE_Order_Pub.Header_Val_Rec_Type;
42 l_line_val_tbl OE_Order_Pub.Line_Val_Tbl_Type;
43 l_reservation_val_tbl OE_Order_Pub.Reservation_Val_Tbl_Type;
44
45 mtl_res_rec INV_RESERVATION_GLOBAL.mtl_reservation_rec_type;
46
47 l_mtl_sales_order_id NUMBER;
48 l_reservation_id NUMBER;
49 l_qty_already_rsv_loop NUMBER := 0;
50 l_qty_already_rsv_global NUMBER := 0;
51 l_qty_to_reserve NUMBER := 0;
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(2000);
54 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
55 l_reservable_type NUMBER;
56 -- INVCONV
57
58 l_qty2_already_rsv_loop NUMBER := 0;
59 l_qty2_already_rsv_global NUMBER := 0;
60 l_qty2_to_reserve NUMBER := 0;
61
62
63 --
64 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
65 --
66 BEGIN
67 l_header_rec := p_header_rec;
68 l_line_tbl := p_line_tbl;
69 l_reservation_tbl := p_reservation_tbl;
70 l_header_val_rec := p_header_val_rec;
71 l_line_val_tbl := p_line_val_tbl;
72 l_reservation_val_tbl := p_reservation_val_tbl;
73 G_ORDER_NUMBER := l_header_rec.order_number;
74 G_ORIG_SYS_DOCUMENT_REF := l_header_rec.orig_sys_document_ref;
75
76 p_return_status := l_return_status;
77
78 IF l_debug_level > 0 THEN
79 oe_debug_pub.add( 'BEFORE RESERVATION' ) ;
80 END IF;
81
82 FOR I in 1..l_reservation_tbl.count
83 LOOP
84 IF l_debug_level > 0 THEN
85 oe_debug_pub.add( 'HEADER_ID: '|| L_HEADER_REC.HEADER_ID ) ;
86 END IF;
87 IF l_debug_level > 0 THEN
88 oe_debug_pub.add( 'LINE_ID: '|| L_LINE_TBL ( L_RESERVATION_TBL ( I ) .LINE_INDEX ) .LINE_ID ) ;
89 END IF;
90 IF l_debug_level > 0 THEN
91 oe_debug_pub.add( 'ORG_ID: '|| L_LINE_TBL ( L_RESERVATION_TBL ( I ) .LINE_INDEX ) .ORG_ID ) ;
92 END IF;
93 IF l_debug_level > 0 THEN
94 oe_debug_pub.add( 'REQUEST_DATE: '|| L_LINE_TBL ( L_RESERVATION_TBL ( I ) .LINE_INDEX ) .REQUEST_DATE ) ;
95 END IF;
96 IF l_debug_level > 0 THEN
97 oe_debug_pub.add( 'ORD_QTY_UOM: '|| L_LINE_TBL ( L_RESERVATION_TBL ( I ) .LINE_INDEX ) .ORDER_QUANTITY_UOM ) ;
98 END IF;
99 IF l_debug_level > 0 THEN
100 oe_debug_pub.add( 'ORD_QTY: '|| L_LINE_TBL ( L_RESERVATION_TBL ( I ) .LINE_INDEX ) .ORDERED_QUANTITY ) ;
101 END IF;
102 -- Following code can be removed yet to confirm with old release
103 -- IF (nvl(l_reservation_tbl(I).revision, FND_API.G_MISS_CHAR)
104 -- <> FND_API.G_MISS_CHAR OR
105 -- nvl(l_reservation_tbl(I).lot_number_id, FND_API.G_MISS_NUM)
106 -- <> FND_API.G_MISS_NUM OR
107 -- nvl(l_reservation_val_tbl(I).lot_number, FND_API.G_MISS_CHAR)
108 -- <> FND_API.G_MISS_CHAR OR
109 -- nvl(l_reservation_tbl(I).subinventory_id, FND_API.G_MISS_NUM)
110 -- <> FND_API.G_MISS_NUM OR
111 -- nvl(l_reservation_val_tbl(I).subinventory_code, FND_API.G_MISS_CHAR)
112 -- <> FND_API.G_MISS_CHAR OR
113 -- nvl(l_reservation_tbl(I).locator_id, FND_API.G_MISS_NUM)
114 -- <> FND_API.G_MISS_NUM)
115 -- Upto Here
116 -- Changed following line AND to IF
117 -- AND (l_reservation_tbl(I).quantity > 0)
118 IF (l_reservation_tbl(I).quantity > 0)
119
120 AND nvl(l_header_rec.header_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
121 AND nvl(l_line_tbl(l_reservation_tbl(I).line_index).line_id,
122 FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
123 -- This condition is removed for the bug 1579224 --
124 -- Single Org installation ------------------------
125 -- AND nvl(l_line_tbl(l_reservation_tbl(I).line_index).org_id,
126 -- FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
127 -- This condition is changed for the bug 1817012 --
128 -- The check should be done with the schedule_ship_date
129 -- Also check for the Null condition during testing
130 -- AND nvl(l_line_tbl(l_reservation_tbl(I).line_index).request_date,
131 -- FND_API.G_MISS_DATE) <> FND_API.G_MISS_DATE
132 AND nvl(l_line_tbl(l_reservation_tbl(I).line_index).schedule_ship_date,
133 FND_API.G_MISS_DATE) <> FND_API.G_MISS_DATE
134 AND nvl(l_line_tbl(l_reservation_tbl(I).line_index).order_quantity_uom,
135 FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
136 THEN
137 BEGIN
138 IF l_debug_level > 0 THEN
139 oe_debug_pub.add( 'BEFORE SETTING RESERVATION PARAMETERS' ) ;
140 END IF;
141
142
143 IF l_debug_level > 0 THEN
144 oe_debug_pub.add( 'REVISION: ' || L_RESERVATION_TBL ( I ) .REVISION ) ;
145 END IF;
146 IF l_debug_level > 0 THEN
147 oe_debug_pub.add( 'LOT_NUMBER_ID: ' || L_RESERVATION_TBL ( I ) .LOT_NUMBER_ID ) ;
148 END IF;
149 IF l_debug_level > 0 THEN
150 oe_debug_pub.add( 'LOT_NUMBER: ' || L_RESERVATION_VAL_TBL ( I ) .LOT_NUMBER ) ;
151 END IF;
152 IF l_debug_level > 0 THEN
153 oe_debug_pub.add( 'SUBINVENTORY_ID: ' || L_RESERVATION_TBL ( I ) .SUBINVENTORY_ID ) ;
154 END IF;
155 IF l_debug_level > 0 THEN
156 oe_debug_pub.add( 'SUBINVENTORY_CODE: '|| L_RESERVATION_VAL_TBL ( I ) .SUBINVENTORY_CODE ) ;
157 END IF;
158 IF l_debug_level > 0 THEN
159 oe_debug_pub.add( 'LOCATOR_ID: ' || L_RESERVATION_TBL ( I ) .LOCATOR_ID ) ;
160 END IF;
161 IF l_debug_level > 0 THEN
162 oe_debug_pub.add( 'QUANTITY: ' || L_RESERVATION_TBL ( I ) .QUANTITY ) ;
163 END IF;
164
165
166 -- Get demand_source_header_id from mtl_sales_orders
167 --4504362
168 l_mtl_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(l_line_tbl(l_reservation_tbl(I).line_index).header_id);
169
170 mtl_res_rec.reservation_id := NULL;
171 -- This condition is changed for the bug 1817012 --
172 -- The check should be done with the schedule_ship_date
173 -- mtl_res_rec.requirement_date := l_line_tbl(l_reservation_tbl(I).line_index).request_date;
174 mtl_res_rec.requirement_date := l_line_tbl(l_reservation_tbl(I).line_index).schedule_ship_date;
175 mtl_res_rec.organization_id := l_line_tbl(l_reservation_tbl(I).line_index).ship_from_org_id;
176 mtl_res_rec.inventory_item_id := l_line_tbl(l_reservation_tbl(I).line_index).inventory_item_id;
177 -- aksingh this change made on 07/28/00 after 11i2
178 If l_line_tbl(l_reservation_tbl(I).line_index).order_source_id = 10 then
179 mtl_res_rec.demand_source_type_id :=
180 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INTERNAL_ORD; -- Internal Order
181 else
182 mtl_res_rec.demand_source_type_id :=
183 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE; -- Order Entry
184 end if;
185 mtl_res_rec.demand_source_name := NULL;
186 mtl_res_rec.demand_source_header_id := l_mtl_sales_order_id;
187 mtl_res_rec.demand_source_line_id := l_line_tbl(l_reservation_tbl(I).line_index).line_id;
188 -- bug# 1244758 mtl_res_rec.demand_source_delivery := l_line_tbl(l_reservation_tbl(I).line_index).line_id;
189 mtl_res_rec.demand_source_delivery := NULL;
190 mtl_res_rec.primary_uom_code := NULL;
191 mtl_res_rec.primary_uom_id := NULL;
192 mtl_res_rec.reservation_uom_code := l_line_tbl(l_reservation_tbl(I).line_index).order_quantity_uom;
193 mtl_res_rec.reservation_uom_id := NULL;
194 mtl_res_rec.reservation_quantity := l_reservation_tbl(I).quantity;
195 mtl_res_rec.primary_reservation_quantity := NULL;
196 mtl_res_rec.autodetail_group_id := NULL;
197 mtl_res_rec.external_source_code := NULL;
198 mtl_res_rec.external_source_line_id := NULL;
199 mtl_res_rec.supply_source_type_id :=
200 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
201 mtl_res_rec.supply_source_header_id := NULL;
202 mtl_res_rec.supply_source_line_id := NULL;
203 mtl_res_rec.supply_source_name := NULL;
204 mtl_res_rec.supply_source_line_detail := NULL;
205 mtl_res_rec.revision := l_reservation_tbl(I).revision;
206 mtl_res_rec.subinventory_code := l_reservation_val_tbl(I).subinventory_code;
207 mtl_res_rec.subinventory_id := l_reservation_tbl(I).subinventory_id;
208 mtl_res_rec.locator_id := l_reservation_tbl(I).locator_id;
209 mtl_res_rec.lot_number := l_reservation_val_tbl(I).lot_number;
210 mtl_res_rec.lot_number_id := l_reservation_tbl(I).lot_number_id;
211 mtl_res_rec.ship_ready_flag := 2;
212 mtl_res_rec.pick_slip_number := NULL;
213 mtl_res_rec.lpn_id := NULL;
214 mtl_res_rec.attribute_category := l_reservation_tbl(I).attribute_category;
215 mtl_res_rec.attribute1 := l_reservation_tbl(I).attribute1;
216 mtl_res_rec.attribute2 := l_reservation_tbl(I).attribute2;
217 mtl_res_rec.attribute3 := l_reservation_tbl(I).attribute3;
218 mtl_res_rec.attribute4 := l_reservation_tbl(I).attribute4;
219 mtl_res_rec.attribute5 := l_reservation_tbl(I).attribute5;
220 mtl_res_rec.attribute6 := l_reservation_tbl(I).attribute6;
221 mtl_res_rec.attribute7 := l_reservation_tbl(I).attribute7;
222 mtl_res_rec.attribute8 := l_reservation_tbl(I).attribute8;
223 mtl_res_rec.attribute9 := l_reservation_tbl(I).attribute9;
224 mtl_res_rec.attribute10 := l_reservation_tbl(I).attribute10;
225 mtl_res_rec.attribute11 := l_reservation_tbl(I).attribute11;
226 mtl_res_rec.attribute12 := l_reservation_tbl(I).attribute12;
227 mtl_res_rec.attribute13 := l_reservation_tbl(I).attribute13;
228 mtl_res_rec.attribute14 := l_reservation_tbl(I).attribute14;
229 mtl_res_rec.attribute15 := l_reservation_tbl(I).attribute15;
230
231 -- aksingh for the bug# 1537689 and bug# 1661359
232 IF l_debug_level > 0 THEN
233 oe_debug_pub.add( 'DEMAND_SOURCE_HEADER_ID: '|| TO_CHAR ( L_HEADER_REC.HEADER_ID ) ) ;
234 END IF;
235 IF l_debug_level > 0 THEN
236 oe_debug_pub.add( 'DEMAND_SOURCE_LINE_ID: ' || TO_CHAR ( L_LINE_TBL ( L_RESERVATION_TBL ( I ) .LINE_INDEX ) .LINE_ID ) ) ;
237 END IF;
238 IF l_debug_level > 0 THEN
239 oe_debug_pub.add( 'ORG_ID: ' || TO_CHAR ( L_LINE_TBL ( L_RESERVATION_TBL ( I ) .LINE_INDEX ) .ORG_ID ) ) ;
240 END IF;
241 l_qty_already_rsv_loop := 0;
242 l_qty_to_reserve := 0;
243 -- INVCONV
244 l_qty2_already_rsv_loop := 0;
245 l_qty2_to_reserve := 0;
246
247
248 -- bug1817012, ask if scheduling put the data on line record for reserved
249 -- quantity, then this call can be avoided.
250 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
251
252 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => OE_HEADER_UTIL.Get_Mtl_Sales_Order_Id
253 (p_header_id => l_header_rec.header_id)
254 ,p_line_id => l_line_tbl(l_reservation_tbl(I).line_index).line_id
255 ,p_org_id => l_line_tbl(l_reservation_tbl(I).line_index).org_id
256 ,x_reserved_quantity => l_qty_already_rsv_loop
257 ,x_reserved_quantity2 => l_qty2_already_rsv_loop
258 );
259
260 /*l_qty_already_rsv_loop := OE_LINE_UTIL.get_reserved_quantity
261 (p_header_id => OE_HEADER_UTIL.Get_Mtl_Sales_Order_Id
262 (p_header_id => l_header_rec.header_id),
263 p_line_id => l_line_tbl(l_reservation_tbl(I).line_index).line_id,
264 p_org_id => l_line_tbl(l_reservation_tbl(I).line_index).org_id); */
265
266 l_qty_already_rsv_loop := nvl(l_qty_already_rsv_loop, 0);
267
268 -- INVCONV
269 /*l_qty2_already_rsv_loop := OE_LINE_UTIL.get_reserved_quantity2
270 (p_header_id => OE_HEADER_UTIL.Get_Mtl_Sales_Order_Id
271 (p_header_id => l_header_rec.header_id),
272 p_line_id => l_line_tbl(l_reservation_tbl(I).line_index).line_id,
273 p_org_id => l_line_tbl(l_reservation_tbl(I).line_index).org_id); */
274
275
276 l_qty2_already_rsv_loop := nvl(l_qty2_already_rsv_loop, 0);
277
278 -- bug1817012, Check the l_qty_already_rsv_loop, At this stage for
279 -- reservation done during the scheduling, if it is reserved and there
280 -- are some additional information about the lot,subinv or something
281 -- we have to unreserve and re-reserve otherwise if no additional information
282 -- then we will leave the reservation as it is.
283 IF l_debug_level > 0 THEN
284 oe_debug_pub.add( 'L_QTY_ALREADY_RSV_LOOP: ' || L_QTY_ALREADY_RSV_LOOP ) ;
285 oe_debug_pub.add( 'L_QTY2_ALREADY_RSV_LOOP: ' || L_QTY2_ALREADY_RSV_LOOP ) ;
286 END IF;
287
288 --check
289
290 if(I>1) then
291
292 if (l_qty_already_rsv_loop >0 AND (l_reservation_tbl(I).line_index <> l_reservation_tbl(I-1).line_index) ) then
293 IF l_debug_level > 0 THEN
294 oe_debug_pub.add( 'I>2' ) ;
295 END IF;
296 IF l_debug_level > 0 THEN
297 oe_debug_pub.add( 'BEFORE CALLING UNRESERVE_LINE' ) ;
298 END IF;
299 IF l_debug_level > 0 THEN
300 oe_debug_pub.add( 'RESERVED_QUANTITY ' ||L_QTY_ALREADY_RSV_LOOP ) ;
301 oe_debug_pub.add( 'RESERVED_QUANTITY2 ' ||L_QTY2_ALREADY_RSV_LOOP ) ;
302 END IF;
303
304 IF l_qty2_already_rsv_loop = 0 -- INVCONV PAL
305 THEN
306 -- Currently setting the reserved2 quantity to null if it is zero.
307 l_qty2_already_rsv_loop := null;
308 END IF;
309
310
311 -- 4504362
312
313 IF l_debug_level > 0 THEN
314 oe_debug_pub.add( 'BEFORE CALLING OE_SCHEDULE_UTIL' ) ;
315 END IF;
316 --NULL; -- invconv inserted just for compile need to add qty2
317 OE_SCHEDULE_UTIL.Unreserve_Line( p_line_rec => l_line_tbl(l_reservation_tbl(I).line_index),
318 p_quantity_to_unreserve => l_qty_already_rsv_loop,
319 p_quantity2_to_unreserve => l_qty2_already_rsv_loop, -- INVCONV
320 x_return_status => l_return_status);
321
322
323 IF l_debug_level > 0 THEN
324 oe_debug_pub.add( 'AFTER CALLING UNRESERVE_LINE' ) ;
325 END IF;
326
327 end if;
328
329 else
330 if (l_qty_already_rsv_loop >0) then
331 IF l_debug_level > 0 THEN
332 oe_debug_pub.add( 'I=1' ) ;
333 END IF;
334 IF l_debug_level > 0 THEN
335 oe_debug_pub.add( 'BEFORE CALLING UNRESERVE_LINE' ) ;
336 END IF;
337 IF l_debug_level > 0 THEN
338 oe_debug_pub.add( 'RESERVED_QUANTITY ' ||L_QTY_ALREADY_RSV_LOOP ) ;
339 oe_debug_pub.add( 'RESERVED2_QUANTITY ' ||L_QTY2_ALREADY_RSV_LOOP ) ;
340 END IF;
341
342 --4504362
343
344 IF l_debug_level > 0 THEN
345 oe_debug_pub.add( 'BEFORE CALLING OE_SCHEDULE_UTIL' ) ;
346 END IF;
347 OE_SCHEDULE_UTIL.Unreserve_Line(p_line_rec =>l_line_tbl(l_reservation_tbl(I).line_index),
348 p_quantity_to_unreserve => l_qty_already_rsv_loop,
349 p_quantity2_to_unreserve =>l_qty2_already_rsv_loop, -- INVCONV
350 x_return_status => l_return_status);
351
352
353 IF l_debug_level > 0 THEN
354 oe_debug_pub.add( 'AFTER CALLING UNRESERVE_LINE' ) ;
355 END IF;
356
357 end if;
358
359 end if;
360
361 IF l_debug_level > 0 THEN
362 oe_debug_pub.add( 'AFTER CHECK' ) ;
363 END IF;
364
365 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
366
367 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => OE_HEADER_UTIL.Get_Mtl_Sales_Order_Id
368 (p_header_id => l_header_rec.header_id)
369 ,p_line_id => l_line_tbl(l_reservation_tbl(I).line_index).line_id
370 ,p_org_id => l_line_tbl(l_reservation_tbl(I).line_index).org_id
371 ,x_reserved_quantity => l_qty_already_rsv_loop
372 ,x_reserved_quantity2 => l_qty2_already_rsv_loop
373 );
374
375
376
377 /*l_qty_already_rsv_loop := OE_LINE_UTIL.get_reserved_quantity
378 (p_header_id => OE_HEADER_UTIL.Get_Mtl_Sales_Order_Id
379 (p_header_id => l_header_rec.header_id),
380 p_line_id => l_line_tbl(l_reservation_tbl(I).line_index).line_id,
381 p_org_id => l_line_tbl(l_reservation_tbl(I).line_index).org_id); */
382
383 IF l_debug_level > 0 THEN
384 oe_debug_pub.add( 'L_QTY_ALREADY_RSV_LOOP'||L_QTY_ALREADY_RSV_LOOP ) ;
385 END IF;
386 -- INVCONV
387 /*l_qty2_already_rsv_loop := OE_LINE_UTIL.get_reserved_quantity2
388 (p_header_id => OE_HEADER_UTIL.Get_Mtl_Sales_Order_Id
389 (p_header_id => l_header_rec.header_id),
390 p_line_id => l_line_tbl(l_reservation_tbl(I).line_index).line_id,
391 p_org_id => l_line_tbl(l_reservation_tbl(I).line_index).org_id); */
392
393 IF l_debug_level > 0 THEN
394 oe_debug_pub.add( 'L_QTY2_ALREADY_RSV_LOOP'||L_QTY2_ALREADY_RSV_LOOP ) ;
395 END IF;
396
397
398 l_qty_to_reserve :=
399 l_line_tbl(l_reservation_tbl(I).line_index).ordered_quantity -
400 l_qty_already_rsv_loop;
401
402 -- INVCONV
403 l_qty2_to_reserve :=
404 l_line_tbl(l_reservation_tbl(I).line_index).ordered_quantity2 -
405 NVL(l_qty2_already_rsv_loop,0);
406
407 IF l_debug_level > 0 THEN
408 oe_debug_pub.add( 'L_QTY_TO_RESERVE'||L_QTY_TO_RESERVE ) ;
409 oe_debug_pub.add( 'L_QTY2_TO_RESERVE'||L_QTY2_TO_RESERVE ) ;
410 END IF;
411
412
413 IF (l_qty_to_reserve-l_reservation_tbl(I).quantity < 0)
414 THEN
415
416 FND_MESSAGE.SET_NAME('ONT','OE_SCH_RES_MORE_ORD_QTY');
417 OE_MSG_PUB.Add;
418 --p_return_status := FND_API.G_RET_STS_ERROR;
419 --EXIT;
420 goto next_rec_rsrv;
421 END IF;
422
423 -- end aksingh for the bug# 1537689 and bug# 1661359
424
425
426
427 SELECT RESERVABLE_TYPE
428 INTO l_reservable_type
429 FROM MTL_SYSTEM_ITEMS
430 WHERE INVENTORY_ITEM_ID = l_line_tbl(l_reservation_tbl(I).line_index).inventory_item_id
431 AND ORGANIZATION_ID = l_line_tbl(l_reservation_tbl(I).line_index).ship_from_org_id;
432
433
434
435 IF l_reservation_tbl(I).operation in ('CREATE', 'INSERT') AND
436 l_qty_to_reserve-l_reservation_tbl(I).quantity >= 0
437 THEN
438
439 IF(nvl(l_line_tbl(l_reservation_tbl(I).line_index).shippable_flag, 'N') = 'Y' and l_reservable_type =1) THEN
440 IF l_debug_level > 0 THEN
441 oe_debug_pub.add( 'BEFORE CALLING CREATE_RESERVATION' ) ;
442 END IF;
443 Create_Reservation (
444 p_rsv => mtl_res_rec
445 ,p_rsv_id => l_reservation_id
446 ,p_msg_count => l_msg_count
447 ,p_msg_data => l_msg_data
448 ,p_return_status => l_return_status);
449 END IF;
450 END IF;
451
452 IF l_debug_level > 0 THEN
453 oe_debug_pub.add( 'AFTER CALLING CREATE_RESERVATION' ) ;
454 END IF;
455
456 IF p_return_status NOT IN (FND_API.G_RET_STS_ERROR)
457 AND l_return_status IN (FND_API.G_RET_STS_ERROR,
458 FND_API.G_RET_STS_UNEXP_ERROR)
459 THEN
460 p_return_status := l_return_status;
461 END IF;
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 IF l_debug_level > 0 THEN
466 oe_debug_pub.add( 'UNEXPECTED ERROR IN RESERVE_INVENTORY: '||SQLERRM ) ;
467 END IF;
468 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
469 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
470 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Reserve_Inventory');
471 END IF;
472
473 END;
474
475 END IF;
476
477 <<Next_Rec_Rsrv>>
478 Null;
479 END LOOP;
480
481 END Reserve_Inventory;
482
483
484 /* ------------------------------------------------------------------
485 Procedure: Create_Reservation
486 ------------------------------------------------------------------
487 */
488
489 PROCEDURE Create_Reservation (
490 p_rsv IN INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE
491 ,p_rsv_id OUT NOCOPY NUMBER
492
493 ,p_msg_count OUT NOCOPY NUMBER
494
495 ,p_msg_data OUT NOCOPY VARCHAR2
496
497 ,p_return_status OUT NOCOPY VARCHAR2
498
499 )
500 IS
501 l_rsv INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE :=p_rsv;
502 l_dummy_sn INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
503 l_rsv_id NUMBER;
504 l_rsv_qty NUMBER;
505 l_rsv_qty2 NUMBER; -- INVCONV
506 l_msg_index NUMBER;
507 l_msg_count NUMBER;
508 l_msg_data VARCHAR2(240);
509 l_return_status VARCHAR2(1);
510
511 --
512 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
513 --
514 BEGIN
515 --Start of Bug#7564285
516 l_rsv.secondary_reservation_quantity := NULL;
517 l_rsv.secondary_uom_code := NULL;
518 l_rsv.secondary_uom_id := NULL; --End of Bug#7564285
519
520 IF l_debug_level > 0 THEN
521 oe_debug_pub.add( 'BEFORE CALLING RESERVATIONS API' ) ;
522 END IF;
523
524 INV_RESERVATION_PUB.Create_Reservation (
525 p_api_version_number => 1.0
526 ,p_init_msg_lst => FND_API.G_TRUE
527 ,p_partial_reservation_flag => FND_API.G_TRUE
528 ,p_force_reservation_flag => FND_API.G_FALSE
529 ,p_validation_flag => FND_API.G_TRUE
530 ,p_rsv_rec => l_rsv
531 ,p_serial_number => l_dummy_sn
532 ,x_reservation_id => l_rsv_id
533 ,x_quantity_reserved => l_rsv_qty
534 ,x_secondary_quantity_reserved => l_rsv_qty2
535 ,x_serial_number => l_dummy_sn
536 ,x_msg_count => l_msg_count
537 ,x_msg_data => l_msg_data
538 ,x_return_status => l_return_status
539 );
540
541 IF l_return_status IN (FND_API.G_RET_STS_SUCCESS) THEN
542 IF l_debug_level > 0 THEN
543 oe_debug_pub.add( 'QUANTITY RESERVED: ' || TO_CHAR ( L_RSV_QTY ) ) ;
544 oe_debug_pub.add( 'QUANTITY2 RESERVED: ' || TO_CHAR ( L_RSV_QTY2 ) ) ;
545 END IF;
546 IF l_debug_level > 0 THEN
547 oe_debug_pub.add( 'RESERVATION ID: ' || TO_CHAR ( L_RSV_ID ) ) ;
548 END IF;
549 ELSE
550 IF l_debug_level > 0 THEN
551 oe_debug_pub.add( 'RESERVATION RETURN STATUS: '|| L_RETURN_STATUS || ' IF ERROR EVEN THEN THE IMPORT SHOULD NOT FAIL' ) ;
552 END IF;
553 fnd_file.put_line(FND_FILE.OUTPUT, 'Not able to reserve quantity for requisition number ' || G_ORIG_SYS_DOCUMENT_REF);
554 IF l_debug_level > 0 THEN
555 oe_debug_pub.add( 'MSG COUNT: '|| L_MSG_COUNT ) ;
556 END IF;
557
558 IF l_msg_count = 1 THEN
559 OE_Msg_Pub.Add_Text(p_message_text => l_msg_data);
560 IF l_debug_level > 0 THEN
561 oe_debug_pub.add( 'ERROR: '|| L_MSG_DATA ) ;
562 END IF;
563 ELSE
564 FOR i IN 1..l_msg_count
565 LOOP
566 FND_Msg_Pub.Get(
567 -- p_msg_index => FND_MSG_PUB.G_NEXT,
568 p_encoded => FND_API.G_TRUE,
569 p_data => l_msg_data,
570 p_msg_index_out => l_msg_index);
571
572 OE_Msg_Pub.Add_Text(p_message_text => l_msg_data);
573 IF l_debug_level > 0 THEN
574 oe_debug_pub.add( 'ERROR: '|| L_MSG_DATA ) ;
575 END IF;
576 END LOOP;
577 END IF;
578 END IF;
579
580 p_msg_count := l_msg_count;
581 p_msg_data := l_msg_data;
582 p_return_status := FND_API.G_RET_STS_SUCCESS;
583
584 EXCEPTION
585 WHEN OTHERS THEN
586 IF l_debug_level > 0 THEN
587 oe_debug_pub.add( 'UNEXPECTED ERROR IN CREATE_RESERVATION: '||SQLERRM ) ;
588 END IF;
589 IF l_debug_level > 0 THEN
590 oe_debug_pub.add( 'BUT THE SUCCESS WILL BE RETURNED' ) ;
591 END IF;
592 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
593 p_return_status := FND_API.G_RET_STS_SUCCESS;
594 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Create_Reservation');
595 END IF;
596
597 END Create_Reservation;
598
599
600
601
602
603
604
605
606
607 PROCEDURE Delete_Reservation (
608 p_rsv IN INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE
609 ,p_msg_count OUT NOCOPY NUMBER
610
611 ,p_msg_data OUT NOCOPY VARCHAR2
612
613 ,p_return_status OUT NOCOPY VARCHAR2
614
615 )
616 IS
617 l_rsv INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE :=p_rsv;
618 l_dummy_sn INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
619 l_msg_index NUMBER;
620 l_msg_count NUMBER;
621 l_msg_data VARCHAR2(240);
622 l_return_status VARCHAR2(1);
623 --
624 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
625 --
626 BEGIN
627
628 IF l_debug_level > 0 THEN
629 oe_debug_pub.add( 'BEFORE CALLING DELETE RESERVATIONS API' ) ;
630 END IF;
631
632 INV_RESERVATION_PUB.Delete_Reservation (
633 p_api_version_number => 1.0
634 ,p_init_msg_lst => FND_API.G_TRUE
635 ,p_rsv_rec => l_rsv
636 ,p_serial_number => l_dummy_sn
637 ,x_msg_count => l_msg_count
638 ,x_msg_data => l_msg_data
639 ,x_return_status => l_return_status
640 );
641
642 IF l_return_status IN (FND_API.G_RET_STS_SUCCESS) THEN
643 IF l_debug_level > 0 THEN
644 oe_debug_pub.add( 'RESERVATION DELETED' ) ;
645 END IF;
646 ELSE
647 IF l_debug_level > 0 THEN
648 oe_debug_pub.add( 'RESERVATION RETURN STATUS: '|| L_RETURN_STATUS || ' IF ERROR EVEN THEN THE IMPORT SHOULD NOT FAIL' ) ;
649 END IF;
650 fnd_file.put_line(FND_FILE.OUTPUT, 'Not able to cancel quantity for requisition number ' || G_ORIG_SYS_DOCUMENT_REF);
651 IF l_debug_level > 0 THEN
652 oe_debug_pub.add( 'MSG COUNT: '|| L_MSG_COUNT ) ;
653 END IF;
654
655 IF l_msg_count = 1 THEN
656 OE_Msg_Pub.Add_Text(p_message_text => l_msg_data);
657 IF l_debug_level > 0 THEN
658 oe_debug_pub.add( 'ERROR: '|| L_MSG_DATA ) ;
659 END IF;
660 ELSE
661 FOR i IN 1..l_msg_count
662 LOOP
663 FND_Msg_Pub.Get(
664 -- p_msg_index => FND_MSG_PUB.G_NEXT,
665 p_encoded => FND_API.G_TRUE,
666 p_data => l_msg_data,
667 p_msg_index_out => l_msg_index);
668
669 OE_Msg_Pub.Add_Text(p_message_text => l_msg_data);
670 IF l_debug_level > 0 THEN
671 oe_debug_pub.add( 'ERROR: '|| L_MSG_DATA ) ;
672 END IF;
673 END LOOP;
674 END IF;
675 END IF;
676
677 p_msg_count := l_msg_count;
678 p_msg_data := l_msg_data;
679 p_return_status := FND_API.G_RET_STS_SUCCESS;
680
681 EXCEPTION
682 WHEN OTHERS THEN
683 IF l_debug_level > 0 THEN
684 oe_debug_pub.add( 'UNEXPECTED ERROR IN DELETE_RESERVATION: '||SQLERRM ) ;
685 END IF;
686 IF l_debug_level > 0 THEN
687 oe_debug_pub.add( 'BUT THE SUCCESS WILL BE RETURNED' ) ;
688 END IF;
689 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
690 p_return_status := FND_API.G_RET_STS_SUCCESS;
691 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Delete_Reservation');
692 END IF;
693
694 END Delete_Reservation;
695
696 END OE_ORDER_IMPORT_RESERVE_PVT;