[Home] [Help]
PACKAGE BODY: APPS.GMI_RESERVATION_PVT
Source
1 PACKAGE BODY GMI_Reservation_PVT AS
2 /* $Header: GMIVRSVB.pls 115.72 2004/06/07 17:10:35 pkanetka ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIVRSVB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private procedures relating to OPM |
13 | reservation. |
14 | |
15 | - Query_Reservation |
16 | - Create_Reservation |
17 | - Update_Reservation |
18 | - Delete_Reservation |
19 | - Transfer_Reservation |
20 | - Check_Shipping_Details |
21 | - Calculate_Prior_Reservations
22 | |
23 | HISTORY |
24 | 21-feb-2000 odaboval Created |
25 | 7-Nov-2000 odaboval, B1479751 : Added the test and a message. |
26 | 28-Nov-2000 odaboval, B1504749 : in Query_reservation, swapped columns |
27 | staged_ind with event_id. |
28 | 24-AUG-2001 NC Added line_detail_id in the SELECT in Query_Reservation |
29 | Bug#1675561 |
30 | 03-OCT-2001 odaboval, local fix for bug 2025611 |
31 | added procedure Check_Shipping_Details |
32 | 24-JAN-2002 plowe -- added rounding fix for query_reservation so that |
33 | reserved quantity rounding for recurring decimals|
34 | uses GMI: EPSILON for decimal precision rounding |
35 | in case where primary item UOM is different to |
36 | ordered quantity UOM. |
37 | 13-JAN-2003 NC - Added procedure Calculate_prior_reservations. |
38 | for prior reservations project. Bug#2670928 |
39 | 23-MAR-2004 P.Raghu Bug#3411704 |
40 | Modified procedure Update_Reservation such that |
41 | reserved quantity is calculated if it is equal to |
42 | FND_API.G_MISS_NUM. |
43 +========================================================================+
44 API Name : GMI_Reservation_PVT
45 Type : Private - Package Body
46 Function : This package contains Private procedures used to
47 OPM reservation process.
48 -
49 Pre-reqs : N/A
50 Parameters: Per function
51
52 Current Vers : 1.0
53 */
54
55
56 /* Global variables */
57 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_Reservation_PVT';
58
59 -- HW BUG#:1941429 OPM cross_docking. Record table to hold information
60
61 TYPE demRecTyp_opm IS RECORD (
62 whse_code VARCHAR2(4),
63 item_id NUMBER,
64 qty_available NUMBER,
65 qty2_available NUMBER,
66 qty_committed NUMBER,
67 qty2_committed NUMBER
68 );
69
70 TYPE demRecTabTyp_opm IS TABLE OF demRecTyp_opm INDEX BY BINARY_INTEGER;
71 g_demand_table demRecTabTyp_opm;
72
73 -- PK Bug#3297382 New PL/SQL Table defined to hold shipset information
74
75 TYPE shipset_rectyp_opm IS RECORD (
76 shipset_id NUMBER,
77 order_id NUMBER,
78 shipset_valid VARCHAR2(1),
79 shipset_reserved VARCHAR2(1)
80 );
81
82 TYPE shipset_tabtyp_opm IS TABLE OF shipset_rectyp_opm INDEX BY BINARY_INTEGER;
83 g_shipset_table shipset_tabtyp_opm;
84
85 /* Api start of comments
86 +==========================================================================+
87 | PROCEDURE NAME |
88 | Query_Reservation |
89 | |
90 | TYPE |
91 | Global |
92 | |
93 | USAGE |
94 | Query reservations included in table IC_TRAN_PND. |
95 | If found, fetch data into a table of rec_type. |
96 | |
97 | DESCRIPTION |
98 | Query reservations included in table IC_TRAN_PND. |
99 | If found, fetch data into a table of rec_type. |
100 | |
101 | PARAMETERS |
102 | x_return_status OUT VARCHAR2 - Return Status |
103 | x_msg_count OUT NUMBER - |
104 | x_msg_data OUT VARCHAR2 - |
105 | p_validation_flag IN VARCHAR2 - |
106 | p_query_input IN rec_type - |
107 | p_lock_records IN VARCHAR2 - |
108 | x_mtl_reservation_tbl OUT rec_type - |
109 | x_mtl_reservation_tbl_count OUT NUMBER - |
110 | x_error_code OUT NUMBER - |
111 | |
112 | RETURNS |
113 | None |
114 | |
115 | HISTORY |
116 | 21-FEB-2000 odaboval Created |
117 | 24-JAN-2002 plowe added rounding fix for query_reservation so that |
118 | reserved quantity rounding for recurring decimals |
119 | uses GMI: EPSILON for decimal precision rounding |
120 | in case where primary item UOM is different to |
121 | ordered quantity UOM. |
122 | |
123 +==========================================================================+
124 Api end of comments
125 */
126 PROCEDURE Query_Reservation
127 (
128 x_return_status OUT NOCOPY VARCHAR2
129 , x_msg_count OUT NOCOPY NUMBER
130 , x_msg_data OUT NOCOPY VARCHAR2
131 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
132 , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
133 , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
134 , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
135 , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
136 , x_error_code OUT NOCOPY NUMBER
137 ) IS
138
139
140 /* ============================================================================= */
141 /* Variables */
142 l_api_name CONSTANT VARCHAR2 (30) := 'Query_Reservation';
143
144 l_ic_tran_rec_tbl_empty GMI_Reservation_Util.l_ic_tran_rec_tbl;
145 rec_index BINARY_INTEGER :=1;
146 i BINARY_INTEGER :=1;
147
148 l_quantity_to_convert NUMBER;
149 l_converted_quantity NUMBER;
150 l_OPM_order_um VARCHAR2(4);
151 l_Apps_order_um VARCHAR2(3);
152 l_Apps_primary_um VARCHAR2(3);
153 l_ic_item_mst_rec GMI_Reservation_Util.ic_item_mst_rec;
154
155 -- OPM 2115306
156 l_epsilon NUMBER;
157 n NUMBER;
158 -- OPM 2115306 end
159
160
161 TYPE ref_cursor_type IS REF CURSOR;
162 c_Get_Reservation ref_cursor_type;
163
164
165 BEGIN
166
167 GMI_reservation_Util.PrintLn('(opm_dbg) entering proc GMI_Reservation_PVT.query_reservation (PVT q)');
168 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : reservation_id='||p_query_input.reservation_id||'.');
169 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : organization_id='||p_query_input.organization_id||'.');
170 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : demand_source_type_id='||p_query_input.demand_source_type_id||'.');
171 /* GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : demand_source_name='||p_query_input.demand_source_name||'.'); */
172 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : demand_source_header_id='||p_query_input.demand_source_header_id||'.');
173 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : demand_source_line_id='||p_query_input.demand_source_line_id||'.');
174 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : inventory_item_id='||p_query_input.inventory_item_id||'.');
175 /* GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : primary_uom_code='||p_query_input.primary_uom_code||'.'); */
176 /* GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : reservation_uom_code='||p_query_input.reservation_uom_code||'.'); */
177 /* GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q : reservation_quantity='||p_query_input.reservation_quantity||'.'); */
178
179 /* Initialize API return status to success */
180 x_return_status := FND_API.G_RET_STS_SUCCESS;
181
182 /* ============================================================================================= */
183 /* Reinit the transaction cache. */
184 /* ============================================================================================= */
185 GMI_Reservation_Util.ic_tran_rec_tbl := l_ic_tran_rec_tbl_empty;
186 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q begin COUNT reservation='||GMI_Reservation_Util.ic_tran_rec_tbl.COUNT||'.');
187
188 /* ============================================================================================= */
189 /* Choose which cursor from trans_id or line_id */
190 /* Line_id is the most common use of the API. */
191 /* ============================================================================================= */
192 /* With line_id, the cursor returns all rows attached to the sales order line. */
193 /* (the default Lot, and the allocated lots) */
194 /* ============================================================================================= */
195 /* With trans_id, the cursor returns only one row. */
196 /* */
197 /* ============================================================================================= */
198 /* 28-Nov-2000, B1504749, odaboval : swapped columns staged_ind with event_id */
199 OPEN c_Get_Reservation FOR
200 SELECT trans_id
201 , item_id
202 , line_id
203 , co_code
204 , orgn_code
205 , whse_code
206 , lot_id
207 , location
208 , doc_id
209 , doc_type
210 , doc_line
211 , line_type
212 , reason_code
213 , trans_date
214 , trans_qty
215 , trans_qty2
216 , qc_grade
217 , NULL /* lot no */
218 , NULL /* sublot no */
219 , lot_status
220 , trans_stat
221 , trans_um
222 , trans_um2
223 , staged_ind
224 , event_id
225 , text_code
226 , NULL /* user id */
227 , NULL /* create_lot_index */
228 , NULL /* non_inv field */
229 , line_detail_id
230 FROM ic_tran_pnd
231 WHERE doc_type ='OMSO'
232 AND delete_mark = 0
233 AND completed_ind = 0
234 AND line_id = p_query_input.demand_source_line_id
235 ORDER BY lot_id DESC;
236
237 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q before loop ROWCOUNT='||c_Get_Reservation%ROWCOUNT);
238 /* ============================================================================================= */
239 /* Retrieve the reservation */
240 /* ============================================================================================= */
241 rec_index := 1;
242 LOOP
243 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q in loop='||rec_index);
244 FETCH c_Get_Reservation
245 INTO GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_id
246 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).item_id
247 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).line_id
248 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).co_code
249 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).orgn_code
250 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).whse_code
251 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).lot_id
252 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).location
253 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).doc_id
254 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).doc_type
255 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).doc_line
256 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).line_type
257 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).reason_code
258 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_date
259 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_qty
260 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_qty2
261 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).qc_grade
262 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).lot_no
263 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).sublot_no
264 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).lot_status
265 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_stat
266 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_um
267 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_um2
268 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).staged_ind
269 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).event_id
270 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).text_code
271 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).user_id
272 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).create_lot_index
273 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).non_inv
274 , GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).line_detail_id
275 ;
276 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q before loop ROWCOUNT='||c_Get_Reservation%ROWCOUNT);
277 EXIT WHEN c_Get_Reservation%NOTFOUND;
278
279 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q just after fetch, trans_id='||GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_id);
280 GMI_reservation_Util.PrintLn('qty1='||GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_qty);
281 GMI_reservation_Util.PrintLn('qty2='||GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_qty2);
282
283 l_quantity_to_convert := GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_qty;
284 l_converted_quantity := 0;
285 /* always Get the Apps Primary UM for the Item : */
286 /* only need to do this once */
287 IF rec_index = 1 THEN
288 GMI_Reservation_Util.Get_AppsUOM_from_OPMUOM(
289 p_OPM_UOM => GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_um
290 , x_Apps_UOM => l_Apps_primary_um
291 , x_return_status => x_return_status
292 , x_msg_count => x_msg_count
293 , x_msg_data => x_msg_data);
294
295 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
296 THEN
297 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
298 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_AppsUOM_from_OPMUOM');
299 FND_MESSAGE.Set_Token('WHERE', 'Query_Reservation');
300 FND_MSG_PUB.Add;
301 raise FND_API.G_EXC_ERROR;
302 END IF;
303 /* ============================================================================================= */
304 /* Validation for the query (private level) */
305 /* ============================================================================================= */
306 GMI_Reservation_Util.Validation_for_Query
307 ( p_query_input => p_query_input
308 , x_opm_um => l_OPM_order_um
309 , x_apps_um => l_Apps_order_um
310 , x_ic_item_mst_rec => l_ic_item_mst_rec
311 , x_return_status => x_return_status
312 , x_msg_count => x_msg_count
313 , x_msg_data => x_msg_data
314 , x_error_code => x_error_code); /* Bug 2168710 - Added parameter */
315
316 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
317 THEN
318 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
319 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Validation_for_Query');
320 FND_MESSAGE.Set_Token('WHERE', 'Query_Reservation');
321 /* x_error_code := x_return_status ; */ /* Bug 2168710 */
322 x_return_status := FND_API.G_RET_STS_ERROR;
323 FND_MSG_PUB.Add;
324 raise FND_API.G_EXC_ERROR;
325 END IF;
326 END IF;
327
328 /* ============================================================================================= */
329 /* Convert reserved quantity (in ic_tran_pnd, so OPM) into the reservation_UOM of the SO. */
330 /* =============================================================================================*/
331 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q OPM_order_um='||l_OPM_order_um||', item_um='||l_ic_item_mst_rec.item_um||'.');
332 IF (l_OPM_order_um <> l_ic_item_mst_rec.item_um)
333 THEN
334 GMICUOM.icuomcv(pitem_id => l_ic_item_mst_rec.item_id
335 , plot_id => 0
336 , pcur_qty => l_quantity_to_convert
337 , pcur_uom => l_ic_item_mst_rec.item_um
338 , pnew_uom => l_OPM_order_um
339 , onew_qty => l_converted_quantity);
340
341 --
342 -- BUG 3581429 Added the following anonymous block
343 --
344 BEGIN
345 l_epsilon := to_number(NVL(FND_PROFILE.VALUE('IC$EPSILON'),0)) ;
346 n := (-1) * round(log(10,l_epsilon));
347 EXCEPTION
348 WHEN OTHERS THEN
349 n := 9;
350 END;
351
352 l_converted_quantity := round(l_converted_quantity, n); -- OPM 2115306
353 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q converted qty AFTER rounding = '|| l_converted_quantity);
354 ELSE
355 l_converted_quantity := l_quantity_to_convert;
356 END IF;
357
358
359 /* ============================================================================================= */
360 /* Populate the mtl_reservation rec type */
361 /* and the ic_tran_rec table */
362 /* ============================================================================================= */
363 GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).user_id := FND_GLOBAL.USER_ID;
364 GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).non_inv := l_ic_item_mst_rec.noninv_ind;
365
366 x_mtl_reservation_tbl(rec_index).reservation_id := GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_id;
367 x_mtl_reservation_tbl(rec_index).organization_id := p_query_input.organization_id;
368 x_mtl_reservation_tbl(rec_index).inventory_item_id := l_ic_item_mst_rec.inventory_item_id;
369 x_mtl_reservation_tbl(rec_index).demand_source_header_id := GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).doc_id;
370 x_mtl_reservation_tbl(rec_index).demand_source_line_id := GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).line_id;
371 /* hwahdani 1388867 */
372 x_mtl_reservation_tbl(rec_index).demand_source_type_id := INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE;
373 x_mtl_reservation_tbl(rec_index).requirement_date := GMI_RESERVATION_UTIL.ic_tran_rec_tbl(rec_index).trans_date;
374 /* end of 1388867 */
375
376 /* Quantities in mtl_reservation_tbl are >0, those in ic_tran_rec_tbl are <=0. */
377 x_mtl_reservation_tbl(rec_index).primary_reservation_quantity := (-1) * GMI_Reservation_Util.ic_tran_rec_tbl(rec_index).trans_qty;
378 x_mtl_reservation_tbl(rec_index).primary_uom_code := l_Apps_primary_um;
379 x_mtl_reservation_tbl(rec_index).reservation_quantity := (-1) * l_converted_quantity;
380 x_mtl_reservation_tbl(rec_index).reservation_uom_code := l_Apps_order_um;
381 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q reservation_quantity='||x_mtl_reservation_tbl(rec_index).reservation_quantity||'.');
382
383 x_mtl_reservation_tbl(rec_index).attribute1 := GMI_RESERVATION_UTIL.ic_tran_rec_tbl(rec_index).qc_grade ;
384 x_mtl_reservation_tbl(rec_index).attribute2 := (-1) * GMI_RESERVATION_UTIL.ic_tran_rec_tbl(rec_index).trans_qty2 ;
385 x_mtl_reservation_tbl(rec_index).attribute3 := GMI_RESERVATION_UTIL.ic_tran_rec_tbl(rec_index).trans_um2 ;
386 x_mtl_reservation_tbl(rec_index).attribute4 := GMI_RESERVATION_UTIL.ic_tran_rec_tbl(rec_index).line_detail_id ;
387 x_mtl_reservation_tbl(rec_index).detailed_quantity := 0;
388 rec_index := rec_index + 1;
389 END LOOP;
390 CLOSE c_Get_Reservation;
391
392 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT q COUNT reservation='||GMI_Reservation_Util.ic_tran_rec_tbl.COUNT||'.');
393 x_mtl_reservation_tbl_count := GMI_Reservation_Util.ic_tran_rec_tbl.COUNT;
394
395
396
397 EXCEPTION
398 WHEN FND_API.G_EXC_ERROR THEN
399 x_return_status := FND_API.G_RET_STS_ERROR;
400
401 /* Get message count and data */
402 FND_MSG_PUB.count_and_get
403 ( p_count => x_msg_count
404 , p_data => x_msg_data
405 );
406
407 WHEN OTHERS THEN
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409 IF ( SQLCODE <> 0
410 AND SQLCODE <> 100)
411 THEN
412 x_error_code := SQLCODE;
413 GMI_reservation_Util.PrintLn('(opm_dbg) in GMI_Reservation_PVT.Query_reservation SQLCODE:error='||SQLCODE||'.');
414 FND_MESSAGE.Set_Name('GMI','GMI_SQL_ERROR');
415 FND_MESSAGE.Set_Token('WHERE', 'Query_Reservation');
416 FND_MESSAGE.Set_Token('SQL_CODE', SQLCODE);
417 FND_MESSAGE.Set_Token('SQL_ERRM', SQLERRM);
418 FND_MSG_PUB.Add;
419 raise FND_API.G_EXC_ERROR;
420 END IF;
421
422 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
423 , l_api_name
424 );
425
426 /* Get message count and data */
427 FND_MSG_PUB.count_and_get
428 ( p_count => x_msg_count
429 , p_data => x_msg_data
430 );
431
432
433 END Query_Reservation;
434
435 /* Api start of comments */
436 /* +==========================================================================+
437 | PROCEDURE NAME |
438 | Create_Reservation |
439 | |
440 | TYPE |
441 | Global |
442 | |
443 | USAGE |
444 | Create reservation by calling OPM_Allocation manager. |
445 | |
446 | DESCRIPTION |
447 | Create reservation by calling OPM_Allocation manager. |
448 | |
449 | PARAMETERS |
450 | x_return_status OUT VARCHAR2 - Return Status |
451 | x_msg_count OUT NUMBER - |
452 | x_msg_data OUT VARCHAR2 - |
453 | p_validation_flag IN VARCHAR2 - |
454 | p_rsv_rec IN rec_type - |
455 | p_serial_number IN rec_type - |
456 | x_serial_number OUT rec_type - |
457 | x_quantity_reserved OUT rec_type - |
458 | x_reservation_id OUT NUMBER - |
459 | |
460 | RETURNS |
461 | None |
462 | |
463 | HISTORY |
464 | 21-FEB-2000 odaboval Created |
465 | |
466 +==========================================================================+
467 Api end of comments
468 */
469 PROCEDURE Create_Reservation
470 (
471 x_return_status OUT NOCOPY VARCHAR2
472 , x_msg_count OUT NOCOPY NUMBER
473 , x_msg_data OUT NOCOPY VARCHAR2
474 , p_validation_flag IN VARCHAR2 DEFAULT FND_API.G_TRUE
475 , p_rsv_rec IN INV_reservation_global.mtl_reservation_rec_type
476 , p_serial_number IN INV_reservation_global.serial_number_tbl_type
477 , x_serial_number OUT NOCOPY INV_reservation_global.serial_number_tbl_type
478 , p_partial_reservation_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE
479 , p_force_reservation_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE
480 , x_quantity_reserved OUT NOCOPY NUMBER
481 , x_reservation_id OUT NOCOPY NUMBER
482 ) IS
483
484 /* ==== Variables ============================================================== */
485 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Reservation';
486 l_default_lot_index BINARY_INTEGER;
487 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
488 l_mtl_rsv_tbl_count NUMBER;
489 x_error_code NUMBER;
490 l_lock_status BOOLEAN;
491 l_allocation_rec GMI_AUTO_ALLOCATE_PUB.gmi_allocation_rec;
492 l_ic_item_mst_rec GMI_Reservation_Util.ic_item_mst_rec;
493 l_cust_no op_cust_mst.cust_no%TYPE;
494 l_orgn_code sy_orgn_mst.orgn_code%TYPE;
495 l_trans_id NUMBER;
496 X_ALLOCATED_QTY1 NUMBER;
497 X_ALLOCATED_QTY2 NUMBER;
498 l_default_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
499 i BINARY_INTEGER :=1;
500 -- added by fabdi 10/01/2001
501 -- fix for bug # 1574957
502 l_whse_ctl number;
503 --B1766055 - Retrieve whse loct_ctl data using primary key
504 --========================================================
505 Cursor get_whse_ctl IS
506 select loct_ctl
507 from ic_whse_mst
508 where whse_code = l_allocation_rec.whse_code;
509 -- end fabdi
510 CURSOR check_detailed_allocations IS
511 SELECT SUM(ABS(trans_qty))
512 FROM ic_tran_pnd
513 WHERE line_id = p_rsv_rec.demand_source_line_id
514 AND doc_type = 'OMSO'
515 AND staged_ind = 0
516 AND completed_ind = 0
517 AND lot_id <> 0
518 AND delete_mark = 0;
519 BEGIN
520 GMI_reservation_Util.PrintLn('(opm_dbg) Entering proc GMI_Reservation_PVT.Create_reservation ');
521 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : user_id='||FND_GLOBAL.USER_ID||'.');
522 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : reservation_id='||p_rsv_rec.reservation_id||'.');
523 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : organization_id='||p_rsv_rec.organization_id||'.');
524 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : inventory_item_id='||p_rsv_rec.inventory_item_id||'.');
525 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : requirement_date='||p_rsv_rec.requirement_date||'.');
526 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : demand_source_type_id='||p_rsv_rec.demand_source_type_id||'.');
527 /* GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : demand_source_name='||p_rsv_rec.demand_source_name||'.'); */
528 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : demand_source_header_id='||p_rsv_rec.demand_source_header_id||'.');
529 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : demand_source_line_id='||p_rsv_rec.demand_source_line_id||'.');
530 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : primary_uom_id='||p_rsv_rec.primary_uom_id||'.');
531 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : primary_uom_code='||p_rsv_rec.primary_uom_code||'.');
532 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : reservation_uom_code='||p_rsv_rec.reservation_uom_code||'.');
533 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : reservation_quantity='||p_rsv_rec.reservation_quantity||'.');
534 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : prim_reservation_quantity='||p_rsv_rec.primary_reservation_quantity||'.');
535 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : detailed_quantity='||p_rsv_rec.detailed_quantity||'.');
536 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : external_source_code='||p_rsv_rec.external_source_code||'.');
537 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : subinventory_code='||p_rsv_rec.subinventory_code||'.');
538 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : subinventory_id='||p_rsv_rec.subinventory_id||'.');
539 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : attribute1='||p_rsv_rec.attribute1||'.');
540 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : attribute2='||p_rsv_rec.attribute2||'.');
541 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : attribute3='||p_rsv_rec.attribute3||'.');
542
543 /* ============================================================================================= */
544 /* Initialize API return status to success */
545 /* =============================================================================================*/
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547
548 /* if detailed allocation exists, no need to go through the process
549 because auto alloation engin does not take detailed lines for requests
550 */
551 /* not to do this any more bug 1830327 */
552 /*Open check_detailed_allocations;
553 Fetch check_detailed_allocations into x_allocated_qty1;
554 Close check_detailed_allocations;
555 IF nvl(x_allocated_qty1,0) <> 0 THEN
556 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : details exits, no auto allocations ');
557 ELSE*/
558 /* =============================================================================================*/
559 /* If allocations exist then the reservation_quantity == 0, and I don't create anything */
560 /* Need to check that assumption! */
561 /* ============================================================================================= */
562 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : Begin of treatment');
563
564 IF (p_rsv_rec.reservation_quantity = 0)
565 THEN
566 GMI_reservation_Util.PrintLn('(opm_dbg) in end of PVT c ERROR:Nothing to reserve.');
567 FND_MESSAGE.Set_Name('GMI','GMI_NOTHING_TO_RESERVE');
568 FND_MSG_PUB.Add;
569 RAISE FND_API.G_EXC_ERROR;
570 END IF;
571
572 /* =============================================================================================*/
573 /* Following a pb in August2000, as the query_reservation seems to not be called systematically,*/
574 /* I need to call Query_Reservation by myself.*/
575 /* ============================================================================================= */
576 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c: GMI_Reservation_Util.ic_tran_rec_tbl.COUNT = 0, calling Query_Reservation.');
577 GMI_reservation_pub.Query_Reservation
578 ( p_api_version_number => 1.0
579 , p_init_msg_lst => FND_API.G_FALSE
580 , x_return_status => x_return_status
581 , x_msg_count => x_msg_count
582 , x_msg_data => x_msg_data
583 , p_validation_flag => p_validation_flag
584 , p_query_input => p_rsv_rec
585 , p_cancel_order_mode => INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_YES
586 , x_mtl_reservation_tbl => l_mtl_reservation_tbl
587 , x_mtl_reservation_tbl_count => l_mtl_rsv_tbl_count
588 , x_error_code => x_error_code
589 , p_lock_records => FND_API.G_FALSE
590 , p_sort_by_req_date => inv_reservation_global.g_query_no_sort
591 );
592
593 /* There may have been a problem getting the rows */
594 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
595 THEN
596 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c: Error Returned by Query_Reservation.');
597 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
598 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_reservation_pub.Query_Reservation');
599 FND_MESSAGE.Set_Token('WHERE', 'Create_Reservation');
600 FND_MSG_PUB.ADD;
601 RAISE FND_API.G_EXC_ERROR;
602 END IF;
603
604 /* At this point the table ic_tran_rec_tbl should have rows*/
605
606 /* =============================================================================================*/
607 /* Validation then fill the l_allocation_rec in.*/
608 /* =============================================================================================*/
609 GMI_Reservation_Util.Validation_before_Allocate(
610 p_mtl_rsv_rec => p_rsv_rec
611 , x_allocation_rec => l_allocation_rec
612 , x_ic_item_mst_rec => l_ic_item_mst_rec
613 , x_orgn_code => l_orgn_code
614 , x_return_status => x_return_status
615 , x_msg_count => x_msg_count
616 , x_msg_data => x_msg_data);
617
618 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
619 THEN
620 GMI_reservation_Util.PrintLn('(opm_dbg) in end of PVT c ERROR:Returned by Validation_Before_Allocate.');
621 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
622 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Validation_before_Allocate');
623 FND_MESSAGE.Set_Token('WHERE', 'Create_Reservation');
624 FND_MSG_PUB.Add;
625 RAISE FND_API.G_EXC_ERROR;
626 END IF;
627
628
629
630 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : default_loct='||GMI_Reservation_Util.G_DEFAULT_LOCT||'.');
631 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : default_lot_index='||l_default_lot_index||'.');
632 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : alloc_class='||l_ic_item_mst_rec.alloc_class||'.');
633
634 /* =============================================================================================*/
635 /* No allocation exist and rules are AUTO_ALLOC. Then call OPM_auto_allocation and the transaction engine.*/
636 /* =============================================================================================*/
637 /* Allocation rec type :*/
638 /* l_allocation_rec.doc_id := demand_source_header_id done*/
639 /* l_allocation_rec.line_id := demand_source_line_id done*/
640 /* l_allocation_rec.item_no := (c_item) done*/
641 /* l_allocation_rec.whse_code := INV_GMI_RSV_Branch.Get_Process_Org done*/
642 /* l_allocation_rec.co_code := INV_GMI_RSV_Branch.Get_Process_Org done*/
643 /* l_allocation_rec.cust_no := (c_customer)*/
644 /* l_allocation_rec.prefqc_grade := attribute1 done*/
645 /* l_allocation_rec.order_qty1 := reservation_quantity done*/
646 /* l_allocation_rec.order_qty2 := attribute2 done */
647 /* l_allocation_rec.order_um1 := reservation_uom_code (c_uom) done*/
648 /* l_allocation_rec.order_um2 := attribute3 (c_uom) done*/
649 /* l_allocation_rec.trans_date := requirement_date done*/
650 /* l_allocation_rec.user_id := FND_GLOBAL.USER_ID (c_user) done*/
651 /* l_allocation_rec.user_name := FND_GLOBAL.USER_ID (c_user) done*/
652 /* =============================================================================================*/
653 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.doc_id='||l_allocation_rec.doc_id||'.');
654 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.line_id='||l_allocation_rec.line_id||'.');
655 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.item_no='||l_allocation_rec.item_no||'.');
656 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.whse_code='||l_allocation_rec.whse_code||'.');
657 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.co_code='||l_allocation_rec.co_code||'.');
658 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.cust_no='||l_allocation_rec.cust_no||'.');
659 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.grade='||l_allocation_rec.prefqc_grade||'.');
660 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.order_qty1='||l_allocation_rec.order_qty1||'.');
661 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.order_qty2='||l_allocation_rec.order_qty2||'.');
662 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.order_um1='||l_allocation_rec.order_um1||'.');
663 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.order_um2='||l_allocation_rec.order_um2||'.');
664 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.trans_date='||l_allocation_rec.trans_date||'.');
665 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.user_id='||l_allocation_rec.user_id||'.');
666 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.user_name='||l_allocation_rec.user_name||'.');
667 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c Calls Auto Allocation Engine');
668
669 /* =============================================================================================*/
670 /* Check the existence of the default lot for the transaction/item*/
671 /* =============================================================================================*/
672 /* Test the default_Loct constant.*/
673 IF (GMI_Reservation_Util.G_DEFAULT_LOCT IS NULL)
674 THEN
675 GMI_reservation_Util.PrintLn('(opm_dbg) in end of PVT c ERROR:Cannot get default lot.');
676 FND_MESSAGE.Set_Name('GMI','SY_API_UNABLE_TO_GET_CONSTANT');
677 FND_MESSAGE.Set_Token('CONSTANT_NAME','IC$DEFAULT_LOCT');
678 FND_MSG_PUB.Add;
679 RAISE FND_API.G_EXC_ERROR;
680 END IF;
681
682
683 /* Retrieve the default lot in the transaction*/
684 GMI_Reservation_Util.Get_Default_Lot(
685 x_ic_tran_pnd_index => l_default_lot_index
686 , x_return_status => x_return_status
687 , x_msg_count => x_msg_count
688 , x_msg_data => x_msg_data);
689
690 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
691 THEN
692 GMI_reservation_Util.PrintLn('(opm_dbg) in end of PVT c ERROR:Returned by Get_Default_Lot.');
693 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
694 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_Default_Lot');
695 FND_MESSAGE.Set_Token('WHERE', 'Create_Reservation');
696 FND_MSG_PUB.Add;
697 RAISE FND_API.G_EXC_ERROR;
698 END IF;
699
700 /* =============================================================================================*/
701 /* Lock rows in ic_loct_inv.*/
702 /* =============================================================================================*/
703 /* Bug 2521215 Do not Lock Inventory when Allocating
704 GMI_Locks.Lock_Inventory(
705 i_item_id => l_ic_item_mst_rec.item_id
706 , i_whse_code => l_allocation_rec.whse_code
707 , o_lock_status => l_lock_status
708 );
709
710 IF (l_lock_status = FALSE) THEN
711 GMI_reservation_Util.PrintLn('(opm_dbg) in end of PVT c ERROR:Returned by Lock_Inventory.');
712 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
713 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Locks.Lock_Inventory');
714 FND_MESSAGE.Set_Token('WHERE', 'Create_Reservation');
715 FND_MSG_PUB.Add;
716 GMI_reservation_Util.PrintLn('return 1 in lock inventory');
717 RETURN;
718 -- RAISE FND_API.G_EXC_ERROR; Bug2516545
719 END IF;
720
721 End Bug 2521215 */
722
723 IF (l_default_lot_index = 0) THEN
724 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : Going to create the Default Lot');
725 /* =============================================================================================*/
726 /* No default lot exist AND MANUAL Allocation. Then create the default lot*/
727 /* =============================================================================================*/
728 l_trans_id := NULL;
729 /* bug 1687531, moved here from out side of if */
730 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c before Create_Default_Lot orgn_code='||l_orgn_code||', trans_id(if not null then UPDATE default_lot)='||l_trans_id);
731 GMI_Reservation_Util.Create_Default_Lot(
732 p_allocation_rec => l_allocation_rec
733 , p_ic_item_mst_rec => l_ic_item_mst_rec
734 , p_orgn_code => l_orgn_code
735 , p_trans_id => l_trans_id
736 , x_return_status => x_return_status
737 , x_msg_count => x_msg_count
738 , x_msg_data => x_msg_data);
739
740 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
741 THEN
742 GMI_reservation_Util.PrintLn('(opm_dbg) in end of PVT c ERROR:Returned by Create_Default_Lot.');
743 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
744 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Create_Default_Lot');
745 FND_MESSAGE.Set_Token('WHERE', 'Create_Reservation');
746 FND_MSG_PUB.Add;
747 RAISE FND_API.G_EXC_ERROR;
748 END IF;
749
750 ELSE
751 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c DefaultLot already exist NO Error (Going to update the default lot transaction).');
752 l_default_tran_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index);
753 /* bug 2240221*/
754 IF p_rsv_rec.requirement_date <> FND_API.G_MISS_DATE THEN
755 l_default_tran_rec.trans_date := p_rsv_rec.requirement_date ;
756 END IF;
757
758
759 GMI_reservation_Util.PrintLn('(opm_dbg) p_rsv_rec.inventory_item_id='||p_rsv_rec.inventory_item_id);
760 GMI_reservation_Util.PrintLn('(opm_dbg) opm_item_id='||l_default_tran_rec.item_id);
761 GMI_reservation_Util.PrintLn('(opm_dbg) l_ic_item_mst_rec.item_id='||l_ic_item_mst_rec.item_id);
762
763 /* Start bug 2711467 */
764 IF (l_default_tran_rec.item_id <> l_ic_item_mst_rec.item_id) THEN
765 l_default_tran_rec.item_id := l_ic_item_mst_rec.item_id;
766 l_default_tran_rec.trans_um := l_ic_item_mst_rec.item_um;
767 l_default_tran_rec.trans_um2 := l_ic_item_mst_rec.item_um2;
768 END IF;
769
770 /* End bug 2711467*/
771
772 GMI_RESERVATION_UTIL.balance_default_lot
773 ( p_ic_default_rec => l_default_tran_rec
774 , p_opm_item_id => l_default_tran_rec.item_id
775 , x_return_status => x_return_status
776 , x_msg_count => x_msg_count
777 , x_msg_data => x_msg_data
778 );
779 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
780 THEN
781 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Update_Transaction() updating the default record.');
782 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
783 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
784 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
785 FND_MSG_PUB.Add;
786 RAISE FND_API.G_EXC_ERROR;
787 END IF;
788 /* =============================================================================================*/
789 /* Set the trans_id of the default transaction (passed as a parameter)*/
790 /* =============================================================================================*/
791 l_trans_id := GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index).trans_id;
792 END IF;
793
794
795 /* =============================================================================================*/
796 /* Call the Allocation engine if :*/
797 /* - Allocation class is defined*/
798 /* - item is lot control or location control*/
799 /* =============================================================================================*/
800 -- B1766055 - Ensure l_whse_ctl is populated with loct_ctl setting
801 OPEN get_whse_ctl;
802 FETCH get_whse_ctl INTO l_whse_ctl;
803 CLOSE get_whse_ctl;
804 -- B1766055 END
805 GMI_reservation_Util.PrintLn('OPM Whse LOCATION CTL is : ' || l_whse_ctl);
806 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : alloc_class='||l_ic_item_mst_rec.alloc_class||', lot_ctl='||l_ic_item_mst_rec.lot_ctl||', loct_ctl='||l_ic_item_mst_rec.loct_ctl);
807 -- added by fabdi 10/01/2001
808 -- fix for bug # 1574957
809 IF (l_ic_item_mst_rec.lot_ctl > 0) OR
810 (l_ic_item_mst_rec.loct_ctl > 0 AND l_whse_ctl > 0)
811 -- end fabdi
812 THEN
813 IF ( (l_ic_item_mst_rec.alloc_class <> FND_API.G_MISS_CHAR
814 AND l_ic_item_mst_rec.alloc_class IS NOT NULL )
815 AND p_force_reservation_flag = FND_API.G_TRUE)
816 THEN
817 /* 7-Nov-2000 odaboval : Bug 1479751 : Added the test and a message.*/
818 /* comment this out, no need after bug 2245351*/
819 /*IF (l_allocation_rec.cust_no = ' ' OR l_allocation_rec.cust_no IS NULL)
820 THEN
821 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : Customer is not synchronized. SO_line_id='||l_allocation_rec.line_id);
822 FND_MESSAGE.Set_Name('GML','GML_CUST_NOT_OPM_SYNCHRONIZED');
823 FND_MESSAGE.Set_Token('SO_LINE_ID', l_allocation_rec.line_id);
824 FND_MSG_PUB.Add;
825 RAISE FND_API.G_EXC_ERROR;
826 END IF;*/
827 /* End of Bug 1479751*/
828
829 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : Lock Rows then Call allocation engine');
830 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.grade='||l_allocation_rec.prefqc_grade||'.');
831 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.order_qty1='||l_allocation_rec.order_qty1||'.');
832 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.order_qty2='||l_allocation_rec.order_qty2||'.');
833 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.order_um1='||l_allocation_rec.order_um1||'.');
834 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c alloc_rec.order_um2='||l_allocation_rec.order_um2||'.');
835 /* =============================================================================================*/
836 /* Lock rows in ic_loct_inv.*/
837 /* =============================================================================================*/
838 /* Bug 2521215 Do not Lock Inventory when Allocating
839 GMI_Locks.Lock_Inventory(
840 i_item_id => l_ic_item_mst_rec.item_id
841 , i_whse_code => l_allocation_rec.whse_code
842 , o_lock_status => l_lock_status
843 );
844
845 IF (l_lock_status = FALSE) THEN
846 GMI_reservation_Util.PrintLn('(opm_dbg) in end of PVT c ERROR:Returned by Lock_Inventory.');
847 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
848 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Locks.Lock_Inventory');
849 FND_MESSAGE.Set_Token('WHERE', 'Create_Reservation');
850 FND_MSG_PUB.Add;
851 -- RAISE FND_API.G_EXC_ERROR; Bug2516545
852 GMI_reservation_Util.PrintLn('return 2 in lock inventory');
853 RETURN;
854 END IF;
855 End Bug 2521215 */
856
857 GMI_Auto_Allocate_PUB.Allocate_Inventory(
858 P_API_VERSION => 1.0
859 , P_INIT_MSG_LIST => FND_API.G_FALSE
860 , P_COMMIT => FND_API.G_FALSE
861 , P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
862 , P_ALLOCATION_REC => l_allocation_rec
863 , X_RESERVATION_ID => X_RESERVATION_ID
864 , X_ALLOCATED_QTY1 => X_ALLOCATED_QTY1
865 , X_ALLOCATED_QTY2 => X_ALLOCATED_QTY2
866 , X_RETURN_STATUS => X_RETURN_STATUS
867 , X_MSG_COUNT => X_MSG_COUNT
868 , X_MSG_DATA => X_MSG_DATA
869 );
870
871 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
872 GMI_reservation_Util.PrintLn('(opm_dbg) in end of PVT c ERROR:Returned by Allocate_Inventory.');
873 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
874 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Auto_Allocate_PUB.Allocate_Inventory');
875 FND_MESSAGE.Set_Token('WHERE', 'Create_Reservation');
876 FND_MSG_PUB.Add;
877 RAISE FND_API.G_EXC_ERROR;
878 END IF;
879 ELSE
880 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : Manual Allocation or HighLevelReservation: Only Default Lot.');
881 END IF;
882 ELSE
883 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c : Item Not Lot or Location controlled : Only Default Lot.');
884 END IF;
885 --END IF;
886
887 /* =============================================================================================*/
888 /* Returned values*/
889 /* =============================================================================================*/
890 x_quantity_reserved := p_rsv_rec.reservation_quantity;
891 x_reservation_id := p_rsv_rec.demand_source_line_id;
892
893 GMI_reservation_Util.PrintLn('(opm_dbg) in end of PVT c No Error, quantity_reserved='||x_quantity_reserved||'.');
894
895 EXCEPTION
896
897 /* =============================================================================================*/
898 /* Error*/
899 /* =============================================================================================*/
900 WHEN FND_API.G_EXC_ERROR THEN
901 GMI_Reservation_Util.PrintLn('in end of PVT c ERROR.');
902 x_return_status := FND_API.G_RET_STS_ERROR;
903
904 /* Get message count and data*/
905 FND_MSG_PUB.count_and_get
906 ( p_count => x_msg_count
907 , p_data => x_msg_data
908 );
909
910 WHEN OTHERS THEN
911 GMI_Reservation_Util.PrintLn('in end of PVT c ERROR:Other.');
912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913
914 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
915 , l_api_name
916 );
917
918 /* Get message count and data*/
919 FND_MSG_PUB.count_and_get
920 ( p_count => x_msg_count
921 , p_data => x_msg_data
922 );
923
924
925 END Create_Reservation;
926
927
928
929 /* Api start of comments*/
930 /* +==========================================================================+
931 | PROCEDURE NAME |
932 | Update_Reservation |
933 | |
934 | TYPE |
935 | Global |
936 | |
937 | USAGE |
938 | Update reservation by calling OPM_Allocation manager. |
939 | |
940 | DESCRIPTION |
941 | Update reservation by calling OPM_Allocation manager. |
942 | |
943 | PARAMETERS |
944 | p_init_msg_lst IN VARCHAR2 - Msg init |
945 | x_return_status OUT VARCHAR2 - Return Status |
946 | x_msg_count OUT NUMBER - |
947 | x_msg_data OUT VARCHAR2 - |
948 | p_validation_flag IN VARCHAR2 - |
949 | p_original_rsv_rec IN rec_type - |
950 | p_to_rsv_rec IN rec_type - |
951 | p_serial_number IN rec_type - |
952 | x_serial_number OUT rec_type - |
953 | |
954 | RETURNS |
955 | None |
956 | |
957 | HISTORY |
958 | 21-FEB-2000 odaboval Created |
959 | 23-MAR-2004 P.Raghu Bug#3411704 |
960 | Reserved quantity is calculated correctly if it|
961 | is equal to FND_API.G_MISS_NUM. |
962 | |
963 +==========================================================================+
964 Api end of comments
965 */
966 PROCEDURE Update_Reservation
967 (
968 x_return_status OUT NOCOPY VARCHAR2
969 , x_msg_count OUT NOCOPY NUMBER
970 , x_msg_data OUT NOCOPY VARCHAR2
971 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
972 , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
973 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
974 , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
975 , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
976 ) IS
977
978 l_commit VARCHAR2(5) := fnd_api.g_false;
979 l_validation_level VARCHAR2(4) := fnd_api.g_valid_level_full;
980 l_api_name CONSTANT VARCHAR2(30) := 'Update_Reservation';
981 l_api_version CONSTANT VARCHAR2(10) := '1.0';
982
983 l_temp_tran_row IC_TRAN_PND%ROWTYPE;
984 l_ic_tran_row IC_TRAN_PND%ROWTYPE;
985
986 l_default_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
987 l_original_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
988 l_default_lot_index BINARY_INTEGER;
989 l_original_tran_index BINARY_INTEGER;
990
991 l_to_rsv_qty NUMBER;
992 l_original_rsv_qty NUMBER;
993 l_delta_rsv_qty NUMBER;
994 l_delta_tran_qty NUMBER;
995
996 l_to_rsv_um VARCHAR2(3);
997 l_opm_uom VARCHAR2(4);
998 l_orig_conv_to_new_rsv_qty NUMBER;
999 l_new_rsv_to_item_um_qty NUMBER;
1000 l_new_rsv_to_item_um_qty2 NUMBER;
1001 l_default_lot_quantity NUMBER;
1002 l_default_loct VARCHAR2(4) := fnd_profile.value('IC$DEFAULT_LOCT');
1003 l_old_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
1004 l_old_mtl_rsv_tbl_count NUMBER;
1005 x_error_code NUMBER;
1006
1007
1008 BEGIN
1009 GMI_reservation_Util.PrintLn('(opm_dbg) Entering PVT u.');
1010 x_return_status := FND_API.G_RET_STS_SUCCESS;
1011
1012 /* GMI_Reservation_Util.Validation_before_Update(
1013 p_mtl_rsv_rec => p_to_rsv_rec
1014 ,x_ic_tran_rec => l_ic_tran_rec_out
1015 ,x_orgn_code => x_orgn_code
1016 ,x_return_status => x_return_status
1017 ,x_msg_count => x_msg_count
1018 ,x_msg_data => x_msg_data);
1019 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1020 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u : Error Returned by Validation_before_Update');
1021 FND_MESSAGE.SET_NAME('GMI','ERROR_IN_VALIDATION_BEFORE_UPDATE');
1022 FND_MSG_PUB.ADD;
1023 RAISE FND_API.G_EXC_ERROR;
1024 END IF;
1025 */
1026 GMI_reservation_Util.PrintLn('ATTRIBUTE2 => ' || p_to_rsv_rec.attribute2);
1027 GMI_reservation_Util.PrintLn('ATTRIBUTE3 => ' || p_to_rsv_rec.attribute3);
1028
1029 /* The query_reservation may not have been called prior to getting here so call it now */
1030 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Reinit GMI_Reservation_Util.ic_tran_rec_tbl, calling Query_Reservation.');
1031 GMI_reservation_pub.Query_Reservation
1032 ( p_api_version_number => 1.0
1033 ,p_init_msg_lst => fnd_api.g_false
1034 ,x_return_status => x_return_status
1035 ,x_msg_count => x_msg_count
1036 ,x_msg_data => x_msg_data
1037 ,p_validation_flag => p_validation_flag
1038 ,p_query_input => p_original_rsv_rec
1039 ,p_cancel_order_mode => INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_YES
1040 ,x_mtl_reservation_tbl => l_old_mtl_reservation_tbl
1041 ,x_mtl_reservation_tbl_count => l_old_mtl_rsv_tbl_count
1042 ,x_error_code => x_error_code
1043 ,p_lock_records => fnd_api.g_false
1044 ,p_sort_by_req_date => inv_reservation_global.g_query_no_sort
1045 );
1046
1047 /* There may not be any rows*/
1048 IF (GMI_Reservation_Util.ic_tran_rec_tbl.COUNT = 0) THEN
1049 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error No Rows Found in mtl_reservation');
1050 FND_MESSAGE.SET_NAME('GMI','GMI_QTY_RSV_NOT_FOUND');
1051 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1052 FND_MESSAGE.Set_Token('SO_LINE_ID', p_original_rsv_rec.demand_source_line_id);
1053 FND_MSG_PUB.ADD;
1054 RAISE FND_API.G_EXC_ERROR;
1055 END IF;
1056
1057 /* There may have been a problem getting the rows*/
1058 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1059 THEN
1060 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error Returned by Query_Reservation.');
1061 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1062 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_reservation_pub.Query_Reservation');
1063 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1064 FND_MSG_PUB.ADD;
1065 RAISE FND_API.G_EXC_ERROR;
1066 END IF;
1067
1068 /* At this point the table should have rows*/
1069
1070 /* Retrieve the default lot transaction we'll need it later */
1071 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u:: we have rows now calling Get_Default_Lot.');
1072 GMI_Reservation_Util.Get_Default_Lot(
1073 x_ic_tran_pnd_index => l_default_lot_index
1074 , x_return_status => x_return_status
1075 , x_msg_count => x_msg_count
1076 , x_msg_data => x_msg_data);
1077
1078 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1079 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error Returned by Get_Default_Lot.');
1080 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1081 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_Default_Lot');
1082 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1083 FND_MSG_PUB.ADD;
1084 RAISE FND_API.G_EXC_ERROR;
1085 END IF;
1086
1087 /* Populate local default row to hold values for comparision*/
1088 l_default_tran_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index);
1089
1090 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u:: saved default transaction to local rec.');
1091
1092 /* ---------------------------------------------------------------------------------------*/
1093 /* Populate local original rec to hold values for comparision*/
1094 /* if this is not the default rec copy the original rec to l_original_tran_rec*/
1095 /* else this is the default rec copy the default rec to l_original_tran_rec*/
1096 /* ---------------------------------------------------------------------------------------*/
1097 GMI_reservation_Util.PrintLn('opm_dbg) in PVT u: l_default_tran_rec.trans_id is ' || l_default_tran_rec.trans_id);
1098 GMI_reservation_Util.PrintLn('opm_dbg) in PVT u: p_original_rsv_rec.reservation_id is ' || p_original_rsv_rec.reservation_id);
1099 IF (l_default_tran_rec.trans_id <> p_original_rsv_rec.reservation_id)
1100 THEN
1101 GMI_Reservation_Util.Get_Allocation(
1102 p_trans_id => p_original_rsv_rec.reservation_id
1103 ,x_ic_tran_pnd_index => l_original_tran_index
1104 ,x_return_status => x_return_status
1105 ,x_msg_count => x_msg_count
1106 ,x_msg_data => x_msg_data);
1107
1108 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1109 THEN
1110 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error Returned by Get_Allocation.');
1111 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1112 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_Allocation');
1113 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1114 FND_MSG_PUB.ADD;
1115 RAISE FND_API.G_EXC_ERROR;
1116 END IF;
1117
1118 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Not updating the default, save orig trans to local rec.');
1119 l_original_tran_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_original_tran_index);
1120 ELSE
1121 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Updating the default, save default trans to local rec.');
1122 l_original_tran_rec := l_default_tran_rec;
1123 END IF;
1124
1125 /* -----------------------------------------------------------------------------------*/
1126 /* Convert the new rsv qty to the opm item uom*/
1127 /* This way it doesn't matter what the new rsv uom is */
1128 /* -----------------------------------------------------------------------------------*/
1129 /* map to rsv um to opm um*/
1130 IF p_to_rsv_rec.primary_reservation_quantity = FND_API.G_MISS_NUM
1131 or nvl(p_to_rsv_rec.primary_reservation_quantity,0) = 0 THEN
1132 l_to_rsv_qty := p_to_rsv_rec.reservation_quantity;
1133 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: primary_res_qty is null and rsv qty is'||l_to_rsv_qty);
1134 l_to_rsv_um := p_to_rsv_rec.reservation_uom_code;
1135 --Begin Bug#3411704
1136 IF l_to_rsv_qty = FND_API.G_MISS_NUM THEN
1137 l_to_rsv_qty := p_original_rsv_rec.primary_reservation_quantity;
1138 END IF;
1139 --End Bug#3411704
1140 -- IF l_to_rsv_um is NULL THEN
1141 /* Bug 2882209*/
1142 -- PK Bug 3606481. l_to_rsv_um should not be compared to FND_API.G_MISS_NUM. Removed part of OR clause.
1143 IF (l_to_rsv_um is NULL OR l_to_rsv_um = FND_API.G_MISS_CHAR) THEN
1144 l_to_rsv_um := p_original_rsv_rec.reservation_uom_code;
1145 END IF;
1146 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: rsv uom is'||l_to_rsv_um);
1147 GMI_Reservation_Util.Get_OPMUOM_from_AppsUOM(p_apps_uom => l_to_rsv_um
1148 ,x_opm_uom => l_opm_uom
1149 ,x_return_status => x_return_status
1150 ,x_msg_count => x_msg_count
1151 ,x_msg_data => x_msg_data);
1152
1153 ELSE
1154 l_to_rsv_qty := p_to_rsv_rec.primary_reservation_quantity;
1155 l_opm_uom := l_original_tran_rec.trans_um;
1156 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: primary_res_qty is not null '||l_to_rsv_qty);
1157 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: uom '||l_opm_uom);
1158 END IF;
1159 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1160 THEN
1161 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error Returned by Get_OPMUOMfromAppsUOM ');
1162 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1163 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_OPMUOM_from_AppsUOM');
1164 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1165 FND_MSG_PUB.add;
1166 RAISE FND_API.G_EXC_ERROR;
1167 END IF;
1168
1169 /* convert the new rsv qty to the item um qty*/
1170 IF (l_original_tran_rec.trans_um <> l_opm_uom)
1171 THEN
1172 GMICUOM.icuomcv(pitem_id => l_original_tran_rec.item_id
1173 ,plot_id => l_original_tran_rec.lot_id
1174 ,pcur_qty => l_to_rsv_qty
1175 ,pcur_uom => l_opm_uom
1176 ,pnew_uom => l_original_tran_rec.trans_um
1177 ,onew_qty => l_new_rsv_to_item_um_qty);
1178 ELSE
1179 l_new_rsv_to_item_um_qty := l_to_rsv_qty;
1180 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: new qty'||l_new_rsv_to_item_um_qty);
1181 END IF;
1182
1183 /* Okay Lets Check For Secondary Quantities. We should Store*/
1184 /* The Secondary Qty in p_to_rsv_rec. Attribute2 the UOM is*/
1185 /* Always the same as the transaction in IC_TRAN_PND*/
1186 /* Therefore there will be no conversions so Store the Value.*/
1187
1188 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: attribute2'||p_to_rsv_rec.attribute2);
1189 IF p_to_rsv_rec.attribute2 = FND_API.G_MISS_CHAR or p_to_rsv_rec.attribute2 is null
1190 THEN
1191 l_new_rsv_to_item_um_qty2 := NULL;
1192 ELSE
1193 l_new_rsv_to_item_um_qty2 := to_number(p_to_rsv_rec.attribute2);
1194 END IF;
1195 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: qty2'||l_new_rsv_to_item_um_qty2);
1196
1197 /* Compare reservation qtys if new qty is greater than old qty then add difference to the default*/
1198 /* l_orig_conv_to_new_rsv_qty is the same whether the uom changed or not, it is the new reservation quantity*/
1199 /* next we need to convert it to the ic_tran_pnd uom which is the item primary uom*/
1200 /* ---------------------------------------------------------------------------------*/
1201 /* If the new qty is greater than the old qty add the change to the default row qty*/
1202 /* ---------------------------------------------------------------------------------*/
1203 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: l_new_rsv_to_item_um_qty is ' || l_new_rsv_to_item_um_qty);
1204 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: l_original_tran_rec.trans_qty is ' || l_original_tran_rec.trans_qty);
1205 /* ======================================================= */
1206 /* if the trans_id is the default_lot's trans_id*/
1207 /* then*/
1208 /* Update the default_lot with Delta*/
1209 /* else*/
1210 /* if NewQty > OldQty*/
1211 /* then*/
1212 /* Update the default_lot with Delta*/
1213 /* else*/
1214 /* Delete the Allocated lot*/
1215 /* and */
1216 /* Update the default_lot with Delta*/
1217 /* endif*/
1218 /* endif*/
1219 /* ======================================================= */
1220 /* Beginning of the process*/
1221 /* =======================================================*/
1222 /* bug 2240221*/
1223 IF p_to_rsv_rec.requirement_date <> FND_API.G_MISS_DATE THEN
1224 l_default_tran_rec.trans_date := p_to_rsv_rec.requirement_date;
1225 END IF;
1226
1227 IF (l_default_tran_rec.trans_id = p_original_rsv_rec.reservation_id)
1228 THEN
1229
1230 l_default_tran_rec.trans_qty := -1 * ABS(l_new_rsv_to_item_um_qty);
1231 l_default_tran_rec.trans_qty2 := -1 * ABS(l_new_rsv_to_item_um_qty2);
1232 /* l_default_tran_rec.non_inv := 0;*/
1233 GMI_reservation_Util.PrintLn('(opm_dbg)in PVT u: Update PRIM default Lot to:' || l_default_tran_rec.trans_qty );
1234 GMI_reservation_Util.PrintLn('(opm_dbg)in PVT u: Update SECO default Lot to:' || l_default_tran_rec.trans_qty2 );
1235 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
1236 p_api_version => 1.0
1237 ,p_init_msg_list => fnd_api.g_false
1238 ,p_commit => l_commit
1239 ,p_validation_level => l_validation_level
1240 ,p_tran_rec => l_default_tran_rec
1241 ,x_tran_row => l_temp_tran_row
1242 ,x_return_status => x_return_status
1243 ,x_msg_count => x_msg_count
1244 ,x_msg_data => x_msg_data);
1245
1246 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1247 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.');
1248 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1249 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
1250 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1251 FND_MSG_PUB.Add;
1252 RAISE FND_API.G_EXC_ERROR;
1253 END IF;
1254
1255 ELSE
1256 /* the p_to_rsv_rec is not the default lot ...*/
1257
1258 /* If the new qty is less than the old qty, check the default row*/
1259 IF (l_new_rsv_to_item_um_qty >= ABS(l_original_tran_rec.trans_qty)) THEN
1260 /* Here we have to update the default_lot, only.*/
1261
1262 l_default_tran_rec.trans_qty := -1 * (ABS(l_default_tran_rec.trans_qty) + ABS(l_new_rsv_to_item_um_qty));
1263
1264 l_default_tran_rec.trans_qty2 := -1 * (ABS(l_default_tran_rec.trans_qty2) + ABS(l_new_rsv_to_item_um_qty2));
1265
1266 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: New qty is More than old ');
1267 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Update default row trans_qty to '|| l_default_tran_rec.trans_qty);
1268 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
1269 p_api_version => 1.0
1270 ,p_init_msg_list => fnd_api.g_false
1271 ,p_commit => l_commit
1272 ,p_validation_level => l_validation_level
1273 ,p_tran_rec => l_default_tran_rec
1274 ,x_tran_row => l_temp_tran_row
1275 ,x_return_status => x_return_status
1276 ,x_msg_count => x_msg_count
1277 ,x_msg_data => x_msg_data);
1278
1279 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1280 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.');
1281 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1282 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
1283 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1284 FND_MSG_PUB.Add;
1285 RAISE FND_API.G_EXC_ERROR;
1286 END IF;
1287 ELSE
1288 /* --------------------------------------------------------------------*/
1289 /* Otherwise call opm delete reservation to remove old allocation*/
1290 /* -------------------------------------------------------------------- */
1291 GMI_reservation_Util.PrintLn('(opm_dbg) in UpdateReserv: The new rsv qty is smaller than the default qty.' );
1292 GMI_reservation_Util.PrintLn('(opm_dbg) in UpdateReserv: We must delete the old reservation.' );
1293
1294 /* Find the matching ic_tran_rec_tbl record for the rsv_rec passed in*/
1295 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: deleting allocation.');
1296 /* This command will disappear when Query is changed to set it correctly*/
1297 /* l_original_tran_rec.non_inv := 0;*/
1298 /* Delete the record since it is not the default record*/
1299 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Deleting transaction record res_id='||p_original_rsv_rec.reservation_id);
1300 GMI_reservation_Util.PrintLn('(opm_dbg) trans_id='|| l_original_tran_rec.trans_id );
1301
1302 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION(
1303 p_api_version => 1.0
1304 ,p_init_msg_list => fnd_api.g_false
1305 ,p_commit => l_commit
1306 ,p_validation_level => l_validation_level
1307 ,p_tran_rec => l_original_tran_rec
1308 ,x_tran_row => l_temp_tran_row
1309 ,x_return_status => x_return_status
1310 ,x_msg_count => x_msg_count
1311 ,x_msg_data => x_msg_data);
1312
1313 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1314 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: ERROR Returned by Delete_Transaction().');
1315 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1316 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION');
1317 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1318 FND_MSG_PUB.Add;
1319 RAISE FND_API.G_EXC_ERROR;
1320 END IF;
1321
1322 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: After DELETE_PENDING_TRANSACTION.');
1323
1324 /* Transfer the deleted qties to the default_lot + new requested Values.*/
1325 l_default_tran_rec.trans_qty := -1 * (ABS(l_default_tran_rec.trans_qty) + ABS(l_new_rsv_to_item_um_qty));
1326
1327 l_default_tran_rec.trans_qty2:= -1 * (ABS(l_default_tran_rec.trans_qty2) + ABS(l_new_rsv_to_item_um_qty2));
1328
1329
1330 /* Using the modified copy update the default record by calling the transaction engine*/
1331 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
1332 p_api_version => 1.0
1333 ,p_init_msg_list => fnd_api.g_false
1334 ,p_commit => l_commit
1335 ,p_validation_level => l_validation_level
1336 ,p_tran_rec => l_default_tran_rec
1337 ,x_tran_row => l_temp_tran_row
1338 ,x_return_status => x_return_status
1339 ,x_msg_count => x_msg_count
1340 ,x_msg_data => x_msg_data);
1341
1342 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1343 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: ERROR Returned by Update_Pending_Transaction updating the default record.');
1344 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1345 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
1346 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1347 FND_MSG_PUB.Add;
1348 RAISE FND_API.G_EXC_ERROR;
1349 END IF;
1350 END IF;
1351 END IF;
1352
1353 /* -------------------------------------*/
1354 /* Re Query before leaving*/
1355 /* -------------------------------------*/
1356 GMI_reservation_Util.PrintLn('(opm_dbg) in before leaving PVT u: calling Query_Reservation.');
1357 GMI_reservation_pub.Query_Reservation
1358 ( p_api_version_number => 1.0
1359 ,p_init_msg_lst => fnd_api.g_false
1360 ,x_return_status => x_return_status
1361 ,x_msg_count => x_msg_count
1362 ,x_msg_data => x_msg_data
1363 ,p_validation_flag => p_validation_flag
1364 --,p_query_input => p_to_rsv_rec
1365 ,p_query_input => p_original_rsv_rec
1366 ,p_cancel_order_mode => INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_YES
1367 ,x_mtl_reservation_tbl => l_old_mtl_reservation_tbl
1368 ,x_mtl_reservation_tbl_count => l_old_mtl_rsv_tbl_count
1369 ,x_error_code => x_error_code
1370 ,p_lock_records => fnd_api.g_false
1371 ,p_sort_by_req_date => inv_reservation_global.g_query_no_sort
1372 );
1373
1374 /* There may not be any rows*/
1375 IF (GMI_Reservation_Util.ic_tran_rec_tbl.COUNT = 0)
1376 THEN
1377 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Called Query_Reservation Received NoError No Rows Found in mtl_reservation');
1378 /* FND_MESSAGE.Set_Name('GMI','GMI_QRY_RSV_NOT_FOUND');*/
1379 /* FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');*/
1380 /* FND_MESSAGE.Set_Token('SO_LINE_ID', p_to_rsv_rec.demand_source_line_id);*/
1381 /* FND_MSG_PUB.ADD;*/
1382 /* RAISE FND_API.G_EXC_ERROR;*/
1383 END IF;
1384
1385 /* There may have been a problem getting the rows*/
1386 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1387 THEN
1388 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error Returned by Query_Reservation.');
1389 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1390 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Query_Reservation');
1391 FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
1392 FND_MSG_PUB.ADD;
1393 RAISE FND_API.G_EXC_ERROR;
1394 END IF;
1395
1396
1397 GMI_reservation_Util.PrintLn('(opm_dbg) leaving PVT u NO Error');
1398
1399 EXCEPTION
1400 WHEN FND_API.G_EXC_ERROR THEN
1401 x_return_status := FND_API.G_RET_STS_ERROR;
1402
1403 /* Get message count and data*/
1404 FND_MSG_PUB.count_and_get
1405 ( p_count => x_msg_count
1406 , p_data => x_msg_data
1407 );
1408
1409 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u EXCEPTION: Expected');
1410
1411 WHEN OTHERS THEN
1412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1413
1414 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1415 , l_api_name
1416 );
1417
1418 /* Get message count and data*/
1419 FND_MSG_PUB.count_and_get
1420 ( p_count => x_msg_count
1421 , p_data => x_msg_data
1422 );
1423
1424 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u EXCEPTION: Others');
1425
1426 END Update_Reservation;
1427
1428
1429
1430 /* Api start of comments
1431 +==========================================================================+
1432 | PROCEDURE NAME |
1433 | Delete_Reservation |
1434 | |
1435 | TYPE |
1436 | Global |
1437 | |
1438 | USAGE |
1439 | Delete reservation by calling OPM_Allocation manager. |
1440 | |
1441 | DESCRIPTION |
1442 | Delete reservation by calling OPM_Allocation manager. |
1443 | |
1444 | PARAMETERS |
1445 | x_return_status OUT VARCHAR2 - Return Status |
1446 | x_msg_count OUT NUMBER - |
1447 | x_msg_data OUT VARCHAR2 - |
1448 | p_validation_flag IN VARCHAR2 - |
1449 | p_rsv_rec IN rec_type - |
1450 | p_serial_number IN rec_type - |
1451 | |
1452 | RETURNS |
1453 | None |
1454 | |
1455 | HISTORY |
1456 | 21-FEB-2000 odaboval Created |
1457 | |
1458 +==========================================================================+
1459 Api end of comments
1460 */
1461 PROCEDURE Delete_Reservation
1462 (
1463 x_return_status OUT NOCOPY VARCHAR2
1464 , x_msg_count OUT NOCOPY NUMBER
1465 , x_msg_data OUT NOCOPY VARCHAR2
1466 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
1467 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
1468 , p_serial_number IN inv_reservation_global.serial_number_tbl_type
1469 ) IS
1470
1471 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Reservation';
1472
1473 l_commit VARCHAR2(5) := fnd_api.g_false;
1474 l_validation_level VARCHAR2(4) := fnd_api.g_valid_level_full;
1475 l_default_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
1476 l_tran_to_delete_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
1477 l_temp_tran_row IC_TRAN_PND%ROWTYPE;
1478 l_default_lot_index BINARY_INTEGER;
1479 l_allocated_lot_index BINARY_INTEGER;
1480 l_default_lot_quantity1 NUMBER;
1481 l_default_lot_quantity2 NUMBER;
1482 l_new_default_lot_quantity1 NUMBER;
1483 l_new_default_lot_quantity2 NUMBER;
1484 x_error_code NUMBER;
1485 x_mtl_reservation_tbl_count NUMBER;
1486 x_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
1487
1488 -- Begin 3248046 (lswamy)
1489 Cursor get_line_rec(l_line_id IN NUMBER)IS
1490 Select ship_from_org_id
1491 From oe_order_lines_all
1492 Where line_id = l_line_id;
1493
1494 Cursor get_whse_code(l_organization_id IN NUMBER) IS
1495 Select whse_code
1496 From ic_whse_mst
1497 Where mtl_organization_id = l_organization_id;
1498
1499 l_organization_id NUMBER;
1500 l_whse_code VARCHAR2(5);
1501 -- End Bug3248046
1502
1503 BEGIN
1504 GMI_reservation_Util.PrintLn('(opm_dbg) in proc OPM_Reservation_PVT.OPM_Delete_reservation ');
1505 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: reservation_id='||p_rsv_rec.reservation_id||'.');
1506 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: organization_id='||p_rsv_rec.organization_id||'.');
1507 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: inventory_item_id='||p_rsv_rec.inventory_item_id||'.');
1508 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: demand_source_type_id='||p_rsv_rec.demand_source_type_id||'.');
1509 /* GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: demand_source_name='||p_rsv_rec.demand_source_name||'.'); */
1510 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: demand_source_header_id='||p_rsv_rec.demand_source_header_id||'.');
1511 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: demand_source_line_id='||p_rsv_rec.demand_source_line_id||'.');
1512 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: primary_uom_code='||p_rsv_rec.primary_uom_code||'.');
1513
1514 /* Initialize API return status to success*/
1515 x_return_status := FND_API.G_RET_STS_SUCCESS;
1516
1517 /* Always re-Query before doing anything. */
1518 GMI_reservation_pub.Query_reservation
1519 ( p_api_version_number => 1.0
1520 , p_init_msg_lst => fnd_api.g_false
1521 , x_return_status => x_return_status
1522 , x_msg_count => x_msg_count
1523 , x_msg_data => x_msg_data
1524 , p_validation_flag => p_validation_flag
1525 , p_query_input => p_rsv_rec
1526 , p_cancel_order_mode => INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_YES
1527 , x_mtl_reservation_tbl => x_mtl_reservation_tbl
1528 , x_mtl_reservation_tbl_count => x_mtl_reservation_tbl_count
1529 , x_error_code => x_error_code
1530 , p_lock_records => fnd_api.g_false
1531 , p_sort_by_req_date => inv_reservation_global.g_query_no_sort
1532 );
1533
1534 /* If we were able to find records then*/
1535 IF (GMI_Reservation_Util.ic_tran_rec_tbl.COUNT <= 0)
1536 THEN
1537 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: No records found to delete.');
1538 FND_MESSAGE.Set_name('GMI','GMI_ERROR');
1539 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Query_Reservation');
1540 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
1541 FND_MSG_PUB.Add;
1542 RAISE FND_API.G_EXC_ERROR;
1543 END IF;
1544
1545 GMI_Reservation_Util.Get_Default_Lot(
1546 x_ic_tran_pnd_index => l_default_lot_index
1547 , x_return_status => x_return_status
1548 , x_msg_count => x_msg_count
1549 , x_msg_data => x_msg_data);
1550
1551 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1552 THEN
1553 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: No records found to delete.');
1554 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1555 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_Default_Lot');
1556 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
1557 FND_MSG_PUB.Add;
1558 RAISE FND_API.G_EXC_ERROR;
1559 END IF;
1560
1561 /* Find the matching ic_tran_rec_tbl record for the rsv_rec passed in*/
1562 IF (p_rsv_rec.reservation_id <> GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index).trans_id)
1563 THEN
1564 /* If the record is not the default record then just delete the record*/
1565
1566 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: deleting allocation.');
1567
1568 /* Get the Allocation*/
1569 GMI_Reservation_Util.Get_Allocation(
1570 p_trans_id => p_rsv_rec.reservation_id
1571 , x_ic_tran_pnd_index => l_allocated_lot_index
1572 , x_return_status => x_return_status
1573 , x_msg_count => x_msg_count
1574 , x_msg_data => x_msg_data);
1575
1576 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1577 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Get_Allocation().');
1578 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1579 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_Allocation');
1580 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
1581 FND_MSG_PUB.Add;
1582 RAISE FND_API.G_EXC_ERROR;
1583 END IF;
1584
1585 /* added the following condition for OM changes when org id is changed
1586 the array does not contain newly created default lot*/
1587 IF l_allocated_lot_index <> 0 THEN
1588 /* Save a copy of the record to be deleted*/
1589 l_tran_to_delete_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_allocated_lot_index);
1590
1591 /* This command will desappear whem Query is going to set it correctly*/
1592 /* l_tran_to_delete_rec.non_inv := 0;*/
1593 /* Delete the record since it is not the default record*/
1594 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: Deleting transaction record res_id='||p_rsv_rec.reservation_id||', trans_id='||GMI_Reservation_Util.ic_tran_rec_tbl(l_allocated_lot_index).trans_id );
1595
1596 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION(
1597 p_api_version => 1.0
1598 ,p_init_msg_list => fnd_api.g_false
1599 ,p_commit => l_commit
1600 ,p_validation_level => l_validation_level
1601 ,p_tran_rec => l_tran_to_delete_rec
1602 ,x_tran_row => l_temp_tran_row
1603 ,x_return_status => x_return_status
1604 ,x_msg_count => x_msg_count
1605 ,x_msg_data => x_msg_data);
1606
1607 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d After DELETE_PENDING_TRANSACTION.');
1608
1609 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1610 THEN
1611 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Delete_Transaction().');
1612 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1613 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION');
1614 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
1615 FND_MSG_PUB.Add;
1616 RAISE FND_API.G_EXC_ERROR;
1617 END IF;
1618
1619 END IF;
1620 l_default_tran_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index);
1621 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: Qties to update: qty=='||l_default_tran_rec.trans_qty||', qty2='||l_default_tran_rec.trans_qty2);
1622
1623 GMI_RESERVATION_UTIL.balance_default_lot
1624 ( p_ic_default_rec => l_default_tran_rec
1625 , p_opm_item_id => l_default_tran_rec.item_id
1626 , x_return_status => x_return_status
1627 , x_msg_count => x_msg_count
1628 , x_msg_data => x_msg_data
1629 );
1630 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1631 THEN
1632 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Update_Transaction() updating the default record.');
1633 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1634 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
1635 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
1636 FND_MSG_PUB.Add;
1637 RAISE FND_API.G_EXC_ERROR;
1638 END IF;
1639 ELSE
1640
1641 -- Begin 3248046
1642 -- Bug3035697 ( as part of this bug, we eliminated update pending transaction
1643 -- and called balance_default_lot instead)
1644 -- We now conditionally call balance_default_lot
1645
1646 GMI_reservation_Util.PrintLn('(opm_dbg) ELSE PORTION - Handling default transaction');
1647 l_default_tran_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index);
1648
1649 OPEN get_line_rec(l_default_tran_rec.line_id);
1650 FETCH get_line_rec INTO l_organization_id;
1651 CLOSE get_line_rec;
1652
1653 OPEN get_whse_code(l_organization_id);
1654 FETCH get_whse_code INTO l_whse_code;
1655 CLOSE get_whse_code;
1656
1657 IF (l_whse_code <> l_default_tran_rec.whse_code) THEN
1658 GMI_reservation_Util.PrintLn('Calling Balancing when there is whse change');
1659 GMI_RESERVATION_UTIL.balance_default_lot
1660 ( p_ic_default_rec => l_default_tran_rec
1661 , p_opm_item_id => l_default_tran_rec.item_id
1662 , x_return_status => x_return_status
1663 , x_msg_count => x_msg_count
1664 , x_msg_data => x_msg_data
1665 );
1666
1667 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1668 THEN
1669 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Balancing the default record.');
1670 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1671 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_RESERVATION_UTIL.balance_default_lot');
1672 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
1673 FND_MSG_PUB.Add;
1674 RAISE FND_API.G_EXC_ERROR;
1675 END IF;
1676
1677 ELSE
1678 /* If the record is the default record then don't delete it just set the quantity to zero*/
1679 l_default_tran_rec.trans_qty := 0 ;
1680 l_default_tran_rec.trans_qty2 := 0 ;
1681 GMI_reservation_Util.PrintLn('updating to zero for the default transction ');
1682 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
1683 p_api_version => 1.0
1684 ,p_init_msg_list => fnd_api.g_false
1685 ,p_commit => l_commit
1686 ,p_validation_level => l_validation_level
1687 ,p_tran_rec => l_default_tran_rec
1688 ,x_tran_row => l_temp_tran_row
1689 ,x_return_status => x_return_status
1690 ,x_msg_count => x_msg_count
1691 ,x_msg_data => x_msg_data);
1692 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1693 THEN
1694 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Update_Transaction() updating the default record.');
1695 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1696 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
1697 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
1698 FND_MSG_PUB.Add;
1699 RAISE FND_API.G_EXC_ERROR;
1700 END IF;
1701 END IF;
1702 -- End Bug3248046
1703 /* cancel all the reservations for GME */
1704 GMI_reservation_Util.PrintLn('(opm_dbg)in balancing the default lot, cancel res');
1705 GML_BATCH_OM_RES_PVT.cancel_res_for_so_line
1706 (
1707 P_so_line_id => l_default_tran_rec.line_id
1708 , X_return_status => x_return_status
1709 , X_msg_cont => x_msg_count
1710 , X_msg_data => x_msg_data
1711 ) ;
1712
1713 END IF;
1714
1715 GMI_reservation_Util.PrintLn('(opm_dbg) leaving PVT d NO Error');
1716
1717 /* When there is an exception*/
1718 EXCEPTION
1719 WHEN FND_API.G_EXC_ERROR THEN
1720 x_return_status := FND_API.G_RET_STS_ERROR;
1721
1722 /* Get message count and data*/
1723 FND_MSG_PUB.count_and_get
1724 ( p_count => x_msg_count
1725 , p_data => x_msg_data
1726 );
1727 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d EXCEPTION: Expected');
1728
1729 WHEN OTHERS THEN
1730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1731
1732 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1733 , l_api_name
1734 );
1735
1736 /* Get message count and data*/
1737 FND_MSG_PUB.count_and_get
1738 ( p_count => x_msg_count
1739 , p_data => x_msg_data
1740 );
1741
1742 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d EXCEPTION: Others');
1743
1744
1745 END Delete_Reservation;
1746
1747 /* Api start of comments
1748 +==========================================================================+
1749 | PROCEDURE NAME |
1750 | Transfer_Reservation |
1751 | |
1752 | TYPE |
1753 | Global |
1754 | |
1755 | USAGE |
1756 | Transfer reservation - Not Used, just a message |
1757 | |
1758 | DESCRIPTION |
1759 | Transfer reservation - Not Used, just a message |
1760 | |
1761 | PARAMETERS |
1762 | p_init_msg_lst IN VARCHAR2 - Msg init |
1763 | x_return_status OUT VARCHAR2 - Return Status |
1764 | x_msg_count OUT NUMBER - |
1765 | x_msg_data OUT VARCHAR2 - |
1766 | p_is_transfer_supply IN VARCHAR2 - |
1767 | p_original_rsv_rec IN rec_type - |
1768 | p_to_rsv_rec IN rec_type - |
1769 | p_original_serial_number IN rec_type - |
1770 | p_to_serial_number IN rec_type - |
1771 | p_validation_flag IN VARCHAR2 - |
1772 | x_to_reservation_id OUT NUMBER - |
1773 | |
1774 | RETURNS |
1775 | None |
1776 | |
1777 | HISTORY |
1778 | 21-FEB-2000 odaboval Created |
1779 | |
1780 +==========================================================================+
1781 Api end of comments
1782 */
1783 PROCEDURE Transfer_Reservation
1784 (
1785 p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1786 , x_return_status OUT NOCOPY VARCHAR2
1787 , x_msg_count OUT NOCOPY NUMBER
1788 , x_msg_data OUT NOCOPY VARCHAR2
1789 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
1790 , p_is_transfer_supply IN VARCHAR2 DEFAULT fnd_api.g_true
1791 , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
1792 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
1793 , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
1794 , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
1795 , x_to_reservation_id OUT NOCOPY NUMBER
1796 ) IS
1797
1798 l_api_name CONSTANT VARCHAR2 (30) := 'Transfer_Reservation';
1799
1800 BEGIN
1801
1802 FND_MESSAGE.SET_NAME('GMI','GMI_RSV_UNAVAILABLE');
1803 OE_MSG_PUB.Add;
1804 RAISE FND_API.G_EXC_ERROR;
1805
1806 EXCEPTION
1807 WHEN fnd_api.g_exc_error THEN
1808 x_return_status := fnd_api.g_ret_sts_error;
1809
1810 /* Get message count and data*/
1811 FND_MSG_PUB.count_and_get
1812 ( p_count => x_msg_count
1813 , p_data => x_msg_data
1814 );
1815
1816
1817 END Transfer_Reservation;
1818
1819 /* Bug 3297382 Forward Declaration of Calc_Reservation_For_shipset */
1820
1821 PROCEDURE Calc_Reservation_For_shipset(
1822 p_shipset_id IN NUMBER,
1823 p_organization_id IN NUMBER,
1824 p_item_id IN NUMBER,
1825 p_source_header_id IN NUMBER,
1826 p_whse_code IN VARCHAR2,
1827 p_whse_loct_ctl IN NUMBER,
1828 p_chk_inv IN VARCHAR2,
1829 p_requested_quantity IN NUMBER,
1830 p_requested_quantity2 IN NUMBER DEFAULT NULL,
1831 x_shipset_reserved OUT NOCOPY VARCHAR2);
1832
1833
1834 /* Api start of comments
1835 +==========================================================================+
1836 | PROCEDURE NAME |
1837 | calculate_reservation |
1838 | |
1839 | TYPE |
1840 | Global |
1841 | |
1842 | USAGE |
1843 | This procedure calculates qty used for a specific item |
1844 | |
1845 | |
1846 | PARAMETERS |
1847 | p_organization_id IN NUMBER |
1848 | p_item_id IN NUMBER |
1849 | p_demand_source_line_id IN NUMBER |
1850 | p_requested_quantity IN NUMBER |
1851 | p_requested_quantity2 IN NUMBER DEFAULT NULL |
1852 | x_result_qty1 OUT NUMBER |
1853 | x_result_qty2 OUT NUMBER |
1854 | |
1855 | |
1856 | RETURNS |
1857 | None |
1858 | |
1859 | HISTORY |
1860 | September, 2001 Hasan Wahdani |
1861 | |
1862 +==========================================================================+
1863 Api end of comments
1864 */
1865
1866 -- HW BUG#:1941429 added a new procedure to calculate reservation and remaining qty
1867 -- for cross_docking
1868 PROCEDURE Calculate_Reservation(
1869 p_organization_id IN NUMBER,
1870 p_item_id IN NUMBER,
1871 p_demand_source_line_id IN NUMBER,
1872 p_delivery_detail_id IN NUMBER,
1873 p_requested_quantity IN NUMBER,
1874 p_requested_quantity2 IN NUMBER DEFAULT NULL,
1875 x_result_qty1 OUT NOCOPY NUMBER,
1876 x_result_qty2 OUT NOCOPY NUMBER
1877 )IS
1878
1879 l_demand_exists BOOLEAN;
1880 l_count NUMBER := 0;
1881 l_reservation_quantity NUMBER := 0;
1882 l_reservation_quantity2 NUMBER := 0;
1883 l_onhand_qty NUMBER := 0;
1884 l_onhand_qty2 NUMBER := 0;
1885 l_committedsales_qty NUMBER := 0;
1886 l_committedsales_qty2 NUMBER := 0;
1887 l_trans_qty NUMBER := 0;
1888 l_trans_qty2 NUMBER := 0;
1889 l_used_reserved_quantity NUMBER := 0;
1890 l_used_reserved_quantity2 NUMBER := 0;
1891 l_index NUMBER := 0;
1892 l_whse_code VARCHAR2(5);
1893 l_is_grade_ctl VARCHAR2(2);
1894 l_chk_inv VARCHAR2(5);
1895 x_return_status VARCHAR2(20);
1896 l_status VARCHAR2(4);
1897 l_qty_reserved NUMBER;
1898 l_qty2_reserved NUMBER;
1899 l_qty_reserved_default NUMBER;
1900 l_qty2_reserved_default NUMBER;
1901 l_qty_reserved_real NUMBER;
1902 l_qty2_reserved_real NUMBER;
1903 l_qty_remaining NUMBER;
1904 l_qty2_remaining NUMBER;
1905 /* bug 2499153 */
1906 l_item_loct_ctl NUMBER;
1907 l_whse_loct_ctl NUMBER;
1908 l_lot_ctl NUMBER;
1909 l_noninv_ind NUMBER;
1910 l_inventory_item_id NUMBER;
1911 l_is_noctl BOOLEAN := FALSE;
1912 l_def_trans_qty NUMBER := 0;
1913 l_def_trans_qty2 NUMBER := 0;
1914 l_default_loct VARCHAR2(4) := fnd_profile.value('IC$DEFAULT_LOCT');
1915 /* Bug 3297382 shipset declarations */
1916 l_shipset_id NUMBER;
1917 l_source_header_id NUMBER;
1918 l_shipset_reserved VARCHAR2(1);
1919 l_enforce_shipset VARCHAR2(1);
1920 --Bug 3551144
1921 l_high_lev_res_qty NUMBER := 0;
1922 l_high_lev_res_qty2 NUMBER := 0;
1923 l_real_high_lev_res_qty NUMBER := 0;
1924 l_real_high_lev_res_qty2 NUMBER := 0;
1925 l_net_high_lev_res_qty NUMBER := 0;
1926 l_net_high_lev_res_qty2 NUMBER := 0;
1927
1928 -- Get qty on hand
1929 CURSOR qty_on_hand(l_whse_code VARCHAR2,p_item_id NUMBER) IS
1930 SELECT SUM(nvl(s.onhand_order_qty,0)),
1931 SUM(nvl(s.onhand_order_qty2,0)),
1932 SUM(nvl(s.committedsales_qty,0)),
1933 SUM(nvl(s.committedsales_qty2,0))
1934 FROM ic_summ_inv s
1935 WHERE s.item_id = p_item_id
1936 AND s.whse_code = l_whse_code;
1937
1938 -- Get whse information
1939 CURSOR get_whse_code (l_organization_id NUMBER ) IS
1940 SELECT whse_code ,loct_ctl
1941 FROM IC_WHSE_MST
1942 WHERE mtl_organization_id = l_organization_id ;
1943
1944 -- Get item ctl
1945 CURSOR get_item_ctl IS
1946 SELECT loct_ctl,lot_ctl,noninv_ind
1947 FROM ic_item_mst
1948 WHERE item_id=p_item_id;
1949
1950 -- Get the inventory_item_id for the org
1951 Cursor get_inventory_item_id IS
1952 Select inventory_item_id
1953 From mtl_system_items_b mtl
1954 , ic_item_mst ic
1955 Where ic.item_id = p_item_id
1956 and mtl.organization_id = p_organization_id
1957 and ic.item_no = mtl.segment1;
1958
1959 -- Get the allocated qty, this part is not deducted from onhand yet
1960 CURSOR reserved_quantity_real(p_item_id NUMBER
1961 ,l_whse_code VARCHAR2) is
1962 SELECT ABS(SUM(nvl(trans_qty,0))),
1963 ABS(SUM(nvl(trans_qty2,0)))
1964 FROM ic_tran_pnd
1965 WHERE item_id = p_item_id
1966 AND whse_code = l_whse_code
1967 AND (lot_id <> 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
1968 AND completed_ind = 0
1969 AND delete_mark = 0
1970 AND trans_qty < 0; -- pending incoming is sorta available, but not real yet
1971 -- Only at the time this qty is received, it becomes truely available
1972
1973 CURSOR reserved_quantity_nonctl( p_organization_id NUMBER
1974 ,p_inventory_item_id NUMBER ) is
1975 Select sum(nvl(requested_quantity,0)), sum(nvl(requested_quantity2,0))
1976 From wsh_delivery_details
1977 Where organization_id = p_organization_id
1978 and inventory_item_id = p_inventory_item_id
1979 and released_status in ('S', 'Y');
1980
1981 -- Get the allocated qty for this delivery detail line
1982 CURSOR Get_trans_for_del IS
1983 SELECT ABS(SUM(nvl(trans_qty,0))),
1984 ABS(SUM(nvl(trans_qty2,0)))
1985 FROM ic_tran_pnd
1986 WHERE line_id = p_demand_source_line_id
1987 AND line_detail_id = p_delivery_detail_id
1988 AND doc_type='OMSO'
1989 AND completed_ind = 0
1990 AND delete_mark = 0;
1991
1992 /* Bug 3297382 shipset cursor declarations */
1993 CURSOR get_shipset_id IS
1994 SELECT nvl(ship_set_id, 0), source_header_id
1995 FROM wsh_delivery_details
1996 WHERE delivery_detail_id = p_delivery_detail_id;
1997
1998 CURSOR Shipping_parameters( v_org_id IN NUMBER) IS
1999 SELECT NVL(ENFORCE_SHIP_SET_AND_SMC,'N')
2000 FROM WSH_SHIPPING_PARAMETERS
2001 WHERE ORGANIZATION_ID = v_org_id;
2002
2003 --Bug 3551144
2004 -- Get high level reserved qty. That is sum of requested qtys for all delivery detail lines which are
2005 -- relesed to warehouse for a given warehouse and item combination.
2006 CURSOR high_level_reserved_qty(p_organization_id NUMBER, p_inventory_item_id NUMBER) is
2007 SELECT NVL(sum(nvl(requested_quantity,0)),0), NVL(sum(nvl(requested_quantity2,0)),0)
2008 FROM wsh_delivery_details
2009 WHERE organization_id = p_organization_id
2010 AND inventory_item_id = p_inventory_item_id
2011 AND source_code = 'OE'
2012 AND released_status = 'S';
2013
2014 --Bug 3551144
2015 --Get sum of allocated qty against high level reserved qty
2016 CURSOR high_level_res_qty_real(p_item_id NUMBER,l_whse_code VARCHAR2) is
2017 SELECT NVL(ABS(SUM(nvl(trans_qty,0))),0), NVL(ABS(SUM(nvl(trans_qty2,0))),0)
2018 FROM ic_tran_pnd itp
2019 WHERE item_id = p_item_id
2020 AND whse_code = l_whse_code
2021 AND (lot_id <> 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
2022 AND completed_ind = 0
2023 AND delete_mark = 0
2024 AND trans_qty < 0
2025 AND EXISTS(SELECT 1
2026 FROM wsh_delivery_details
2027 WHERE delivery_detail_id = itp.line_detail_id
2028 AND source_code = 'OE'
2029 AND released_status = 'S');
2030
2031 BEGIN
2032
2033 gmi_reservation_util.println('value of is p_organization_id'|| p_organization_id);
2034 gmi_reservation_util.println('value of p_demand_source_line_id is ' ||p_demand_source_line_id);
2035 gmi_reservation_util.println('Value of item_id is '||p_item_id);
2036 gmi_reservation_util.println('value of p_requested_quantity is '||p_requested_quantity);
2037 gmi_reservation_util.println('value of p_requested_quantity2 is '||p_requested_quantity2);
2038
2039
2040 -- Get whse code
2041 OPEN get_whse_code (p_organization_id);
2042 FETCH get_whse_code INTO l_whse_code,l_whse_loct_ctl;
2043 IF ( get_whse_code%NOTFOUND ) THEN
2044 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2045 GMI_RESERVATION_UTIL.PRINTLN('Error retrieving whse code');
2046 RAISE NO_DATA_FOUND;
2047 CLOSE get_whse_code;
2048 RETURN;
2049 END IF;
2050
2051 CLOSE get_whse_code;
2052
2053 gmi_reservation_util.PRINTLN('Value of Whse Code is '||l_whse_code);
2054 -- Get value of profile
2055 -- l_is_grade_ctl := FND_PROFILE.VALUE('IC$AVAILABLE_BY_GRADE');
2056
2057
2058 -- Bug 2499153. Get item ctl.
2059 OPEN get_item_ctl;
2060 FETCH get_item_ctl INTO l_item_loct_ctl, l_lot_ctl, l_noninv_ind;
2061 CLOSE get_item_ctl;
2062
2063 IF (l_lot_ctl = 0 AND (l_item_loct_ctl * l_whse_loct_ctl)= 0) THEN
2064 l_is_noctl := TRUE;
2065 GMI_Reservation_Util.PrintLn('Working with no control item');
2066 END IF;
2067
2068 -- End Bug 2499153
2069
2070 Open get_inventory_item_id;
2071 Fetch get_inventory_item_id into l_inventory_item_id;
2072 Close get_inventory_item_id;
2073
2074 l_chk_inv := FND_PROFILE.VALUE('GML_CHK_INV_PICK_REL');
2075
2076
2077
2078 gmi_reservation_util.println('check profile GML_CHK_INV_PICK_REL is '||l_chk_inv);
2079 IF (l_chk_inv = 'N' OR l_noninv_ind = 1 ) THEN
2080 x_result_qty1 := p_requested_quantity;
2081 x_result_qty2 := p_requested_quantity2;
2082 RETURN;
2083 END IF;
2084
2085 -- Bug 3297382 If the line belongs to a shipset call new procedure Calculate_reservation_for_shipset
2086
2087 OPEN get_shipset_id;
2088 FETCH get_shipset_id INTO l_shipset_id, l_source_header_id;
2089 CLOSE get_shipset_id;
2090
2091 IF (l_shipset_id <> 0) THEN
2092
2093 gmi_reservation_util.println('Line has Shipset '||l_shipset_id);
2094
2095 -- Now check if shipping Parameter for the warehouse have Enforce shipset flag set.
2096 OPEN Shipping_parameters(p_organization_id);
2097 FETCH Shipping_parameters INTO l_enforce_shipset;
2098 CLOSE Shipping_parameters;
2099
2100 gmi_reservation_util.println('Enforce shipset in Shipping parameters for the warehouse is set as '||l_enforce_shipset);
2101
2102 IF ( l_enforce_shipset = 'Y') THEN
2103
2104 gmi_reservation_util.println('Line Has Shipset and shipset is enforced for the warehouse');
2105 gmi_reservation_util.println('Calling Calc_Reservation_For_shipset');
2106
2107 Calc_Reservation_For_shipset(
2108 p_shipset_id => l_shipset_id
2109 ,p_organization_id => p_organization_id
2110 ,p_item_id => p_item_id
2111 ,p_source_header_id => l_source_header_id
2112 ,p_whse_code => l_whse_code
2113 ,p_whse_loct_ctl => l_whse_loct_ctl
2114 ,p_chk_inv => l_chk_inv
2115 ,p_requested_quantity => p_requested_quantity
2116 ,p_requested_quantity2 => p_requested_quantity2
2117 ,x_shipset_reserved => l_shipset_reserved
2118 );
2119
2120 IF (l_shipset_reserved = 'Y') THEN
2121 x_result_qty1 := p_requested_quantity;
2122 x_result_qty2 := p_requested_quantity2;
2123 RETURN;
2124 ELSE
2125 x_result_qty1 := 0;
2126 x_result_qty2 := 0;
2127 RETURN;
2128 END IF;
2129 END IF; -- ( l_enforce_shipset = 'Y')
2130 END IF; -- (l_shipset_id <> 0)
2131
2132 -- End Bug 3297382 Shipset enhancement
2133
2134 l_demand_exists := FALSE;
2135 FOR i in 1..g_demand_table.COUNT LOOP
2136 IF (g_demand_table(i).item_id= p_item_id AND
2137 g_demand_table(i).whse_code = l_whse_code) THEN
2138 l_index := i;
2139 l_demand_exists := TRUE;
2140 gmi_reservation_util.println('Value of item_id found in loop is '||g_demand_table(i).item_id);
2141 gmi_reservation_util.println('In opm calculate_reservation Found the Reservation Details');
2142 gmi_reservation_util.println('value of g_demand_table(l_count).qty_available found is '||g_demand_table(i).qty_available);
2143 gmi_reservation_util.println('value of g_demand_table(l_count).qty_committed found is '||g_demand_table(i).qty_committed);
2144 gmi_reservation_util.println('value of g_demand_table(l_count).qty2_available found is '||g_demand_table(i).qty2_available);
2145 gmi_reservation_util.println('value of g_demand_table(l_count).qty2_committed found is '||g_demand_table(i).qty2_committed);
2146 EXIT;
2147 END IF;
2148 END LOOP;
2149
2150 IF (not l_demand_exists) THEN
2151 gmi_reservation_util.Println('Fetching Reservation Details');
2152
2153 IF (l_is_noctl = TRUE) THEN
2154 gmi_reservation_util.Println('inventory_item_id is '||l_inventory_item_id);
2155 OPEN reserved_quantity_nonctl(p_organization_id,l_inventory_item_id);
2156 FETCH reserved_quantity_nonctl INTO l_qty_reserved_real,l_qty2_reserved_real;
2157 CLOSE reserved_quantity_nonctl;
2158 ELSE
2159 OPEN reserved_quantity_real(p_item_id,l_whse_code);
2160 FETCH reserved_quantity_real into l_qty_reserved_real,l_qty2_reserved_real;
2161 CLOSE reserved_quantity_real;
2162 END IF;
2163 l_qty_reserved_real := nvl(l_qty_reserved_real, 0);
2164 l_qty2_reserved_real := nvl(l_qty2_reserved_real, 0);
2165
2166 gmi_reservation_util.println('value of l_qty_reserved_real '|| l_qty_reserved_real);
2167 gmi_reservation_util.println('value of l_qty2_reserved_real '|| l_qty2_reserved_real);
2168
2169 -- Let's increment the counter
2170
2171 l_count := g_demand_table.COUNT + 1;
2172
2173 g_demand_table(l_count).item_id := p_item_id;
2174 g_demand_table(l_count).whse_code := l_whse_code;
2175
2176 --Bug 3551144 added OR condition
2177 IF (l_chk_inv = 'Y' OR l_chk_inv = 'S') THEN
2178 OPEN qty_on_hand(l_whse_code,p_item_id);
2179 FETCH qty_on_hand INTO
2180 g_demand_table(l_count).qty_available,
2181 g_demand_table(l_count).qty2_available,
2182 g_demand_table(l_count).qty_committed,
2183 g_demand_table(l_count).qty2_committed ;
2184 CLOSE qty_on_hand;
2185 END IF;
2186
2187 --begin bug 3551144
2188 IF (l_chk_inv = 'S') THEN
2189 -- In case of l_is_noctl = TRUE high_level_reserved_qty is considered as part of l_qty_reserved_real
2190 IF (l_is_noctl = FALSE) THEN
2191 OPEN high_level_reserved_qty(p_organization_id,l_inventory_item_id);
2192 FETCH high_level_reserved_qty INTO l_high_lev_res_qty, l_high_lev_res_qty2;
2193 CLOSE high_level_reserved_qty;
2194 gmi_reservation_util.println('value of l_high_lev_res_qty '||l_high_lev_res_qty);
2195 gmi_reservation_util.println('value of l_high_lev_res_qty2 '||l_high_lev_res_qty2);
2196 OPEN high_level_res_qty_real(p_item_id,l_whse_code);
2197 FETCH high_level_res_qty_real into l_real_high_lev_res_qty,l_real_high_lev_res_qty2;
2198 CLOSE high_level_res_qty_real;
2199 gmi_reservation_util.println('value of l_real_high_lev_res_qty '||l_real_high_lev_res_qty);
2200 gmi_reservation_util.println('value of l_real_high_lev_res_qty2 '||l_real_high_lev_res_qty2);
2201 l_net_high_lev_res_qty := l_high_lev_res_qty - l_real_high_lev_res_qty;
2202 l_net_high_lev_res_qty2 := l_high_lev_res_qty2 - l_real_high_lev_res_qty2;
2203 IF l_net_high_lev_res_qty < 0 THEN -- This could happen in case of over allocation.
2204 l_net_high_lev_res_qty := 0;
2205 l_net_high_lev_res_qty2 := 0;
2206 END IF;
2207 gmi_reservation_util.println('value of l_net_high_lev_res_qty '||l_net_high_lev_res_qty);
2208 gmi_reservation_util.println('value of l_net_high_lev_res_qty2 '||l_net_high_lev_res_qty2);
2209 g_demand_table(l_count).qty_available := g_demand_table(l_count).qty_available - l_net_high_lev_res_qty;
2210 g_demand_table(l_count).qty2_available := g_demand_table(l_count).qty2_available - l_net_high_lev_res_qty2;
2211 END IF;
2212 END IF;
2213 --end bug 3551144
2214
2215 g_demand_table(l_count).qty_available := nvl(g_demand_table(l_count).qty_available ,0);
2216 g_demand_table(l_count).qty2_available := nvl(g_demand_table(l_count).qty2_available,0);
2217 g_demand_table(l_count).qty_committed := nvl(g_demand_table(l_count).qty_committed, 0);
2218 g_demand_table(l_count).qty2_committed := nvl(g_demand_table(l_count).qty2_committed,0);
2219 gmi_reservation_util.println('value of g_demand_table(l_count).qty_available '||g_demand_table(l_count).qty_available);
2220 gmi_reservation_util.println('value of g_demand_table(l_count).qty2_available '||g_demand_table(l_count).qty2_available);
2221 gmi_reservation_util.println('value of g_demand_table(l_count).qty_committed '||g_demand_table(l_count).qty_committed);
2222 gmi_reservation_util.println('value of g_demand_table(l_count).qty2_committed '||g_demand_table(l_count).qty2_committed);
2223
2224 l_index := l_count;
2225
2226 --Bug 3551144 added OR condition
2227 IF (l_chk_inv = 'Y' OR l_chk_inv = 'S') THEN
2228 g_demand_table(l_index).qty_available := g_demand_table(l_index).qty_available - l_qty_reserved_real;
2229 g_demand_table(l_index).qty2_available := g_demand_table(l_index).qty2_available - l_qty2_reserved_real;
2230 END IF;
2231
2232 IF g_demand_table(l_index).qty_available < 0 THEN
2233 g_demand_table(l_index).qty_available := 0;
2234 g_demand_table(l_index).qty2_available := 0;
2235 END IF;
2236 IF g_demand_table(l_index).qty2_available < 0 THEN
2237 g_demand_table(l_index).qty2_available := 0;
2238 END IF;
2239 END IF; -- of not l_demand_exists
2240
2241 -- Let's get qty reserved from ic_tran_pnd
2242 gmi_reservation_util.println('value of p_demand_source_line_id before calling res_qty is '||p_demand_source_line_id);
2243 gmi_reservation_util.println('Value of p_item_id before calling res_q is '||p_item_id);
2244 gmi_reservation_util.println('Value of l_whse_code before calling res_q is '||l_whse_code);
2245
2246 IF (l_is_noctl = TRUE) THEN
2247 l_qty_reserved := 0;
2248 l_qty2_reserved := 0;
2249 ELSE
2250 OPEN get_trans_for_del;
2251 FETCH get_trans_for_del into l_qty_reserved,l_qty2_reserved;
2252 CLOSE get_trans_for_del;
2253 END IF;
2254 l_qty_reserved := nvl(l_qty_reserved, 0);
2255 l_qty2_reserved := nvl(l_qty2_reserved, 0);
2256
2257 gmi_reservation_util.println('Value of l_qty_reserved is '||l_qty_reserved);
2258 gmi_reservation_util.println('Value of l_qty2_reserved is '||l_qty2_reserved);
2259 gmi_reservation_util.println('value of l_count before checking is '||l_count);
2260 gmi_reservation_util.println('Value of l_index before checking is '||l_index);
2261
2262 l_qty_remaining := p_requested_quantity - l_qty_reserved;
2263 l_qty2_remaining := p_requested_quantity2 - l_qty2_reserved;
2264
2265 gmi_reservation_util.println('Value of remainig requested qty is '||l_qty_remaining);
2266 gmi_reservation_util.println('Value of remainig requested qty2 is '||l_qty2_remaining);
2267 gmi_reservation_util.println('Value of g_demand_table(l_index).qty_available '||g_demand_table(l_index).qty_available);
2268
2269 IF l_qty_remaining <= 0 THEN
2270 x_result_qty1 := p_requested_quantity;
2271 x_result_qty2 := p_requested_quantity2;
2272 ELSE -- remaining qty is > 0
2273 IF ( g_demand_table(l_index).qty_available - l_qty_remaining >= 0 ) THEN
2274 gmi_reservation_util.println('RELEASE TO WHSE');
2275 gmi_reservation_util.println('Returning Qty reserved from calculate_reservation');
2276 x_result_qty1 := p_requested_quantity;
2277 x_result_qty2 := p_requested_quantity2;
2278 g_demand_table(l_index).qty_available := g_demand_table(l_index).qty_available - p_requested_quantity ;
2279 g_demand_table(l_index).qty2_available := nvl(g_demand_table(l_index).qty2_available - p_requested_quantity2,0) ;
2280 gmi_reservation_util.println('x_result_qty1 '||x_result_qty1);
2281 gmi_reservation_util.println('x_result_qty2 '||x_result_qty2);
2282
2283 ELSIF ( g_demand_table(l_index).qty_available - l_qty_remaining < 0
2284 AND g_demand_table(l_index).qty_available > 0 ) THEN -- SPLIT
2285 gmi_reservation_util.println('SPLIT');
2286 gmi_reservation_util.println('Returning Qty available from caclulate_reservation');
2287 x_result_qty1 := g_demand_table(l_index).qty_available + l_qty_reserved;
2288 x_result_qty2 := g_demand_table(l_index).qty2_available + l_qty2_reserved;
2289 IF x_result_qty2 < 0 THEN
2290 x_result_qty2 := 0;
2291 END IF;
2292 g_demand_table(l_index).qty_available := g_demand_table(l_index).qty_available
2293 - x_result_qty1 ;
2294 g_demand_table(l_index).qty2_available := nvl(g_demand_table(l_index).qty2_available
2295 - x_result_qty2,0) ;
2296 gmi_reservation_util.println('x_result_qty1 '||x_result_qty1);
2297 gmi_reservation_util.println('x_result_qty2 '||x_result_qty2);
2298
2299 ELSIF ( g_demand_table(l_index).qty_available <= 0 )THEN -- Backorder line
2300 /*gmi_reservation_util.println('BACKORDER');
2301 gmi_reservation_util.println('Returning 0 from calculate_reservation');*/
2302 x_result_qty1 := 0;
2303 x_result_qty2 := 0;
2304 --/* bug 2585286, if the availability at high level has already been driven to sub-zero
2305 -- * need to check the real allocations for the del because user still can allocate a perticular
2306 -- * lot-location in order pad, this piece should be allowed to move to whse -- */
2307 IF nvl(l_qty_reserved,0) <> 0 THEN
2308 x_result_qty1 := l_qty_reserved;
2309 x_result_qty2 := l_qty2_reserved;
2310 END IF;
2311 g_demand_table(l_index).qty_available := g_demand_table(l_index).qty_available
2312 - l_qty_reserved ;
2313 g_demand_table(l_index).qty2_available := nvl(g_demand_table(l_index).qty2_available
2314 - l_qty2_reserved,0) ;
2315 IF x_result_qty1 <> 0 THEN
2316 gmi_reservation_util.println('RELEASE qty1 '|| x_result_qty1 || ' TO WHSE because detail reservatin exists');
2317 gmi_reservation_util.println('x_result_qty1 '||x_result_qty1);
2318 gmi_reservation_util.println('x_result_qty2 '||x_result_qty2);
2319 ELSE
2320 gmi_reservation_util.println('BACKORDER');
2321 gmi_reservation_util.println('Returning 0 from calculate_reservation');
2322 gmi_reservation_util.println('x_result_qty1 '||x_result_qty1);
2323 gmi_reservation_util.println('x_result_qty2 '||x_result_qty2);
2324 END IF;
2325 END IF;
2326 END IF;
2327
2328 return;
2329
2330 EXCEPTION
2331
2332 WHEN fnd_api.g_exc_error THEN
2333 x_return_status := fnd_api.g_ret_sts_error;
2334
2335 WHEN NO_DATA_FOUND THEN
2336 GMI_RESERVATION_UTIL.PRINTLN('No Data found raised error in GMI_Reservation_PVT.calculate_reservation');
2337
2338 WHEN OTHERS THEN
2339 x_return_status := FND_API.G_RET_STS_ERROR;
2340 GMI_RESERVATION_UTIL.PRINTLN('RAISE WHEN OTHERS');
2341
2342
2343 END Calculate_Reservation;
2344
2345 /* Api start of comments
2346 +==========================================================================+
2347 | PROCEDURE NAME |
2348 | Check_Shipping_Details |
2349 | |
2350 | TYPE |
2351 | Private |
2352 | |
2353 | DESCRIPTION |
2354 | Check the released_status of the shipping details in order to |
2355 | raise a message if there is a released_status = Y (staged) |
2356 | or C (shipped) |
2357 | |
2358 | PARAMETERS |
2359 | p_rsv_rec IN rec_type - |
2360 | x_return_status OUT VARCHAR2 - Return Status |
2361 | x_msg_count OUT NUMBER - |
2362 | x_msg_data OUT VARCHAR2 - |
2363 | |
2364 | RETURNS |
2365 | None |
2366 | |
2367 | HISTORY |
2368 | 02-OCT-2001 odaboval Created, bug 2025611 |
2369 | |
2370 +==========================================================================+
2371 Api end of comments
2372 */
2373 PROCEDURE Check_Shipping_Details
2374 ( p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
2375 , x_return_status OUT NOCOPY VARCHAR2
2376 , x_msg_count OUT NOCOPY NUMBER
2377 , x_msg_data OUT NOCOPY VARCHAR2
2378 ) IS
2379
2380 l_api_name CONSTANT VARCHAR2 (30) := 'Check_Shipping_Details';
2381 l_released_status VARCHAR2(2);
2382
2383
2384 -- odaboval, Oct-2001, standalone fix for Tropicana.
2385 CURSOR c_get_wsh_released_status( l_so_line_id IN NUMBER) IS
2386 SELECT released_status
2387 FROM wsh_delivery_details
2388 WHERE released_status IN ('Y', 'C')
2389 AND source_line_id = l_so_line_id;
2390
2391 BEGIN
2392
2393 /* Initialize API return status to success */
2394 x_return_status := FND_API.G_RET_STS_SUCCESS;
2395
2396 OPEN c_get_wsh_released_status(p_rsv_rec.demand_source_line_id);
2397 FETCH c_get_wsh_released_status
2398 INTO l_released_status;
2399
2400 IF (c_get_wsh_released_status%NOTFOUND)
2401 THEN
2402 -- There is no problem, the user can delete_reservation.
2403 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT Check_Shipping_Details, Unreserve is allowed. ');
2404 ELSE
2405 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT Check_Shipping_Details, Unreserve is forbidden. ');
2406 x_return_status := FND_API.G_RET_STS_ERROR;
2407 END IF;
2408
2409 CLOSE c_get_wsh_released_status;
2410
2411 EXCEPTION
2412 WHEN FND_API.G_EXC_ERROR THEN
2413 x_return_status := FND_API.G_RET_STS_ERROR;
2414
2415 IF c_get_wsh_released_status%ISOPEN THEN
2416 CLOSE c_get_wsh_released_status;
2417 END IF;
2418
2419 /* Get message count and data*/
2420 FND_MSG_PUB.count_and_get
2421 ( p_count => x_msg_count
2422 , p_data => x_msg_data
2423 );
2424
2425 WHEN OTHERS THEN
2426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2427 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT Check_Shipping_Details EXCEPTION: Others, SqlCode='||SQLCODE);
2428
2429 IF c_get_wsh_released_status%ISOPEN THEN
2430 CLOSE c_get_wsh_released_status;
2431 END IF;
2432
2433 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
2434 , l_api_name
2435 );
2436
2437 /* Get message count and data*/
2438 FND_MSG_PUB.count_and_get
2439 ( p_count => x_msg_count
2440 , p_data => x_msg_data
2441 );
2442
2443
2444
2445 END Check_Shipping_Details;
2446
2447 PROCEDURE query_qty_for_ATP(
2448 p_organization_id IN NUMBER,
2449 p_item_id IN NUMBER,
2450 p_demand_source_line_id IN NUMBER,
2451 x_onhand_qty1 OUT NOCOPY NUMBER,
2452 x_onhand_qty2 OUT NOCOPY NUMBER,
2453 x_avail_qty1 OUT NOCOPY NUMBER,
2454 x_avail_qty2 OUT NOCOPY NUMBER
2455 )IS
2456
2457 l_onhand_qty1 NUMBER := 0;
2458 l_onhand_qty2 NUMBER := 0;
2459 l_committedsales_qty1 NUMBER := 0;
2460 l_committedsales_qty2 NUMBER := 0;
2461 l_trans_qty NUMBER := 0;
2462 l_trans_qty2 NUMBER := 0;
2463 l_whse_code VARCHAR2(5);
2464 l_grade VARCHAR2(6);
2465 l_qty_reserved NUMBER := 0;
2466 l_qty2_reserved NUMBER := 0;
2467 l_qty_reserved_default NUMBER := 0;
2468 l_qty2_reserved_default NUMBER := 0;
2469 l_qty_reserved_real NUMBER := 0;
2470 l_qty2_reserved_real NUMBER := 0;
2471 l_qty_available NUMBER := 0;
2472 l_qty2_available NUMBER := 0;
2473 l_grade_ctl NUMBER := 0;
2474
2475 -- Get qty on hand
2476 CURSOR qty_on_hand(l_whse_code VARCHAR2,p_item_id NUMBER) IS
2477 SELECT SUM(nvl(s.onhand_order_qty,0)),
2478 SUM(nvl(s.onhand_order_qty2,0)),
2479 SUM(nvl(s.committedsales_qty,0)),
2480 SUM(nvl(s.committedsales_qty2,0))
2481 FROM ic_summ_inv s
2482 WHERE s.item_id = p_item_id
2483 AND s.whse_code = l_whse_code;
2484
2485 -- Get whse information
2486 CURSOR get_whse_code (l_organization_id NUMBER ) IS
2487 SELECT whse_code
2488 FROM IC_WHSE_MST
2489 WHERE mtl_organization_id = l_organization_id ;
2490
2491 -- get grade_ctl
2492 Cursor get_grade_ctl IS
2493 Select grade_ctl
2494 from ic_item_mst
2495 where item_id=p_item_id;
2496
2497 -- Get qty reserved for this order line for a grade
2498 CURSOR reserved_quantity_for_grd(l_whse_code VARCHAR2,
2499 p_item_id NUMBER,
2500 l_qc_grade VARCHAR2) is
2501 SELECT SUM(nvl(trans_qty,0)),
2502 SUM(nvl(trans_qty2,0))
2503 FROM ic_tran_pnd
2504 WHERE item_id = p_item_id
2505 -- AND line_id = p_demand_source_line_id
2506 AND whse_code = l_whse_code
2507 AND completed_ind = 0
2508 AND delete_mark = 0
2509 -- AND doc_type='OMSO'
2510 AND qc_grade = l_qc_grade
2511 AND line_type = decode(doc_type,'PROD',-1,line_type); --Bug3163165
2512
2513 CURSOR reserved_quantity_for_atp(l_whse_code VARCHAR2,
2514 p_item_id NUMBER) is
2515 SELECT SUM(nvl(trans_qty,0)),
2516 SUM(nvl(trans_qty2,0))
2517 FROM ic_tran_pnd
2518 WHERE item_id = p_item_id
2519 -- AND line_id = p_demand_source_line_id
2520 AND whse_code = l_whse_code
2521 AND completed_ind = 0
2522 AND delete_mark = 0
2523 AND line_type = decode(doc_type,'PROD',-1,line_type); --Bug3163165
2524
2525 -- Get qty on hand for a grade
2526 CURSOR qty_on_hand_grade(l_whse_code VARCHAR2,
2527 p_item_id NUMBER,
2528 l_qc_grade VARCHAR2) IS
2529 SELECT SUM(nvl(s.onhand_order_qty,0)),
2530 SUM(nvl(s.onhand_order_qty2,0)),
2531 SUM(nvl(s.committedsales_qty,0)),
2532 SUM(nvl(s.committedsales_qty2,0))
2533 FROM ic_summ_inv s
2534 WHERE s.item_id = p_item_id
2535 AND s.whse_code = l_whse_code
2536 AND s.qc_grade = l_qc_grade
2537 ;
2538
2539 -- Get the committed sales for this order line
2540 CURSOR Get_trans_for_null_del IS -- this would include the default lot
2541 SELECT SUM(ABS(nvl(trans_qty,0))),
2542 SUM(ABS(nvl(trans_qty2,0)))
2543 FROM ic_tran_pnd
2544 WHERE line_id = p_demand_source_line_id
2545 AND doc_type='OMSO'
2546 AND completed_ind = 0
2547 AND delete_mark = 0;
2548
2549 Cursor c_get_grade (p_line_id number) IS
2550 SELECT preferred_grade
2551 FROM oe_order_lines_all
2552 WHERE line_id = p_line_id;
2553
2554 BEGIN
2555
2556 gmi_reservation_util.println('value of is p_organization_id'|| p_organization_id);
2557 gmi_reservation_util.println('value of p_demand_source_line_id is ' ||p_demand_source_line_id);
2558 gmi_reservation_util.println('Value of item_id is '||p_item_id);
2559
2560 -- Get whse code
2561 OPEN get_whse_code (p_organization_id);
2562 FETCH get_whse_code INTO l_whse_code;
2563 IF ( get_whse_code%NOTFOUND ) THEN
2564 GMI_RESERVATION_UTIL.PRINTLN('Error retrieving whse code');
2565 RAISE NO_DATA_FOUND;
2566 CLOSE get_whse_code;
2567 RETURN;
2568 END IF;
2569
2570 CLOSE get_whse_code;
2571
2572 gmi_reservation_util.PRINTLN('Value of Whse Code is '||l_whse_code);
2573 Open get_grade_ctl;
2574 Fetch get_grade_ctl Into l_grade_ctl;
2575 Close get_grade_ctl;
2576 gmi_reservation_util.Println('For ATP window');
2577 OPEN c_get_grade(p_demand_source_line_id);
2578 FETCH c_get_grade into l_grade;
2579 CLOSE c_get_grade;
2580
2581 IF (l_grade_ctl > 0 and l_grade is not null) THEN
2582 gmi_reservation_util.Println('For grade ');
2583 OPEN reserved_quantity_for_grd(l_whse_code,p_item_id,l_grade);
2584 FETCH reserved_quantity_for_grd into l_qty_reserved_real,l_qty2_reserved_real;
2585 CLOSE reserved_quantity_for_grd;
2586 ELSE -- not grade ctl
2587 gmi_reservation_util.Println('For NON grade ');
2588 OPEN reserved_quantity_for_atp(l_whse_code,p_item_id);
2589 FETCH reserved_quantity_for_atp into l_qty_reserved_real,l_qty2_reserved_real;
2590 CLOSE reserved_quantity_for_atp;
2591 END IF;
2592 l_qty_reserved_real := nvl(l_qty_reserved_real, 0);
2593 l_qty2_reserved_real := nvl(l_qty2_reserved_real, 0);
2594 gmi_reservation_util.Println('total reserved in ic_tran_pnd qty '|| l_qty_reserved_real);
2595 gmi_reservation_util.Println('total reserved in ic_tran_pnd qty2 '|| l_qty2_reserved_real);
2596
2597 IF (l_grade_ctl > 0 and l_grade is not null) THEN
2598 --- from ATP window
2599 OPEN qty_on_hand_grade(l_whse_code,p_item_id,l_grade);
2600 FETCH qty_on_hand_grade INTO
2601 l_onhand_qty1,
2602 l_onhand_qty2,
2603 l_committedsales_qty1,
2604 l_committedsales_qty2;
2605 CLOSE qty_on_hand_grade;
2606 ELSE
2607 OPEN qty_on_hand(l_whse_code,p_item_id);
2608 FETCH qty_on_hand INTO
2609 l_onhand_qty1,
2610 l_onhand_qty2,
2611 l_committedsales_qty1,
2612 l_committedsales_qty2;
2613 CLOSE qty_on_hand;
2614 END IF;
2615
2616 l_onhand_qty1:= nvl(l_onhand_qty1,0);
2617 l_onhand_qty2:= nvl(l_onhand_qty2,0);
2618 l_committedsales_qty1:= nvl(l_committedsales_qty1, 0);
2619 l_committedsales_qty2:= nvl(l_committedsales_qty2,0);
2620 gmi_reservation_util.println('value of l_onhand_qty1'||l_onhand_qty1);
2621 gmi_reservation_util.println('value of l_onhand_qty2'||l_onhand_qty2);
2622 gmi_reservation_util.println('value of l_committedsales_qty1'||l_committedsales_qty1);
2623 gmi_reservation_util.println('value of l_committedsales_qty2'||l_committedsales_qty2);
2624
2625 -- Begin Bug 2801666 - Pushkar Upakare
2626 l_qty_available := l_onhand_qty1 + l_qty_reserved_real;
2627 l_qty2_available := l_onhand_qty2 + l_qty2_reserved_real;
2628 -- End Bug 2801666
2629
2630 -- Let's get qty reserved from ic_tran_pnd
2631
2632 OPEN get_trans_for_null_del;
2633 FETCH get_trans_for_null_del into l_qty_reserved,l_qty2_reserved;
2634 CLOSE get_trans_for_null_del;
2635
2636 l_qty_reserved := nvl(l_qty_reserved, 0);
2637 l_qty2_reserved := nvl(l_qty2_reserved, 0);
2638
2639 gmi_reservation_util.println('value of l_qty_reserved for this line is '||l_qty_reserved);
2640 gmi_reservation_util.println('value of l_qty2_reserved for this line is '||l_qty2_reserved);
2641
2642 x_onhand_qty1 := l_onhand_qty1 ;
2643 x_onhand_qty2 := l_onhand_qty2 ;
2644 x_avail_qty1 := l_qty_available + l_qty_reserved;
2645 x_avail_qty2 := l_qty2_available+ l_qty2_reserved ;
2646
2647 gmi_reservation_util.println('value of x_onhand_qty1 is '||x_onhand_qty1);
2648 gmi_reservation_util.println('value of x_avail_qty1 is '||x_avail_qty1);
2649
2650 return;
2651 END query_qty_for_ATP;
2652
2653 /* Api start of comments
2654 +==========================================================================+
2655 | PROCEDURE NAME |
2656 | calculate_prior_reservations |
2657 | |
2658 | TYPE |
2659 | Global |
2660 | |
2661 | USAGE |
2662 | This procedure calculates reservationsqty for a particular |
2663 | sales order/delivery detail line. |
2664 | |
2665 | |
2666 | PARAMETERS |
2667 | p_organization_id IN NUMBER |
2668 | p_item_id IN NUMBER |
2669 | p_demand_source_line_id IN NUMBER |
2670 | p_requested_quantity IN NUMBER |
2671 | p_requested_quantity2 IN NUMBER |
2672 | x_result_qty1 OUT NUMBER |
2673 | x_result_qty2 OUT NUMBER |
2674 | x_return_status OUT NOCOPY VARCHAR2 |
2675 | x_msg_count OUT NOCOPY NUMBER |
2676 | x_msg_data OUT NOCOPY VARCHAR2 |
2677 | |
2678 | |
2679 | RETURNS |
2680 | None |
2681 | |
2682 | HISTORY |
2683 | 1/13/03 NC Added to support Prior Reservations while pick-- |
2684 | Releasing. Bug#2670928 |
2685 +==========================================================================+
2686 Api end of comments
2687 */
2688
2689 PROCEDURE Calculate_Prior_Reservations(
2690 p_organization_id IN NUMBER
2691 ,p_item_id IN NUMBER
2692 ,p_demand_source_line_id IN NUMBER
2693 ,p_delivery_detail_id IN NUMBER
2694 ,p_requested_quantity IN NUMBER
2695 ,p_requested_quantity2 IN NUMBER
2696 ,x_result_qty1 OUT NOCOPY NUMBER
2697 ,x_result_qty2 OUT NOCOPY NUMBER
2698 ,x_return_status OUT NOCOPY VARCHAR2
2699 ,x_msg_count OUT NOCOPY NUMBER
2700 ,x_msg_data OUT NOCOPY VARCHAR2) IS
2701
2702 -- Standard Constants.
2703
2704 l_api_name CONSTANT VARCHAR2(30):= 'Calculate_Prior_Reservations';
2705
2706 -- Local Variables
2707
2708 l_qty_reserved NUMBER;
2709 l_qty2_reserved NUMBER;
2710 l_qty_remaining NUMBER;
2711 l_qty2_remaining NUMBER;
2712 l_item_loct_ctl NUMBER;
2713 l_whse_loct_ctl NUMBER;
2714 l_whse_code VARCHAR2(5);
2715 l_lot_ctl NUMBER;
2716 l_noninv_ind NUMBER;
2717 l_inventory_item_id NUMBER;
2718 l_is_noctl BOOLEAN := FALSE;
2719
2720 -- Cursor to Get the allocated qty for this deilery
2721 CURSOR Get_trans_for_del IS
2722 SELECT ABS(SUM(nvl(trans_qty,0))),
2723 ABS(SUM(nvl(trans_qty2,0)))
2724 FROM ic_tran_pnd
2725 WHERE line_id = p_demand_source_line_id
2726 AND line_detail_id = p_delivery_detail_id
2727 AND doc_type='OMSO'
2728 AND completed_ind = 0
2729 AND delete_mark = 0;
2730
2731 -- Get whse information
2732 CURSOR get_whse_code (l_organization_id NUMBER ) IS
2733 SELECT whse_code ,loct_ctl
2734 FROM ic_whse_mst
2735 WHERE mtl_organization_id = l_organization_id ;
2736
2737 -- Get item ctl
2738 CURSOR get_item_ctl IS
2739 SELECT loct_ctl,lot_ctl,noninv_ind
2740 FROM ic_item_mst
2741 WHERE item_id=p_item_id;
2742
2743 BEGIN
2744
2745 GMI_RESERVATION_UTIL.PrintLn('In Procedure Calulate_Prior_Reservations');
2746
2747 /* Initialize return status to success */
2748 x_return_status := FND_API.G_RET_STS_SUCCESS;
2749
2750 OPEN get_item_ctl;
2751 FETCH get_item_ctl INTO l_item_loct_ctl, l_lot_ctl, l_noninv_ind;
2752 IF(get_item_ctl%NOTFOUND)
2753 THEN
2754 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2755 CLOSE get_item_ctl;
2756 GMI_RESERVATION_UTIL.PrintLn('Error retrieving item details');
2757 FND_MESSAGE.Set_Name('GMI','GMI_API_ITEM_NOT_FOUND');
2758 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
2759 FND_MSG_PUB.Add;
2760 RAISE FND_API.G_EXC_ERROR;
2761 ELSE
2762 CLOSE get_item_ctl;
2763 END IF;
2764
2765 -- Get whse code
2766 OPEN get_whse_code (p_organization_id);
2767 FETCH get_whse_code INTO l_whse_code,l_whse_loct_ctl;
2768 IF ( get_whse_code%NOTFOUND ) THEN
2769 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2770 CLOSE get_whse_code;
2771 GMI_RESERVATION_UTIL.PrintLn('Error retrieving whse code');
2772 FND_MESSAGE.SET_NAME('GMI','GMI_API_WHSE_NOT_FOUND');
2773 FND_MESSAGE.SET_TOKEN('ORG', p_organization_id);
2774 FND_MESSAGE.SET_TOKEN('LINE_ID', p_demand_source_line_id);
2775 FND_MSG_PUB.Add;
2776 RAISE FND_API.G_EXC_ERROR;
2777 ELSE
2778 CLOSE get_whse_code;
2779 END IF;
2780
2781 IF (l_lot_ctl = 0 AND (l_item_loct_ctl * l_whse_loct_ctl)= 0)
2782 THEN
2783 l_is_noctl := TRUE;
2784 GMI_RESERVATION_UTIL.PrintLn('Working with no control item');
2785 ELSE
2786 l_is_noctl := FALSE;
2787 END IF;
2788
2789 IF ( l_noninv_ind = 1 or l_is_noctl = TRUE )
2790 THEN
2791 x_result_qty1 := p_requested_quantity;
2792 x_result_qty2 := p_requested_quantity2;
2793 RETURN;
2794 END IF;
2795
2796 /*IF (l_is_noctl = TRUE)
2797 THEN
2798 l_qty_reserved := 0;
2799 l_qty2_reserved := 0;
2800 ELSE*/
2801 IF (l_is_noctl <> TRUE) THEN
2802 OPEN get_trans_for_del;
2803 FETCH get_trans_for_del INTO l_qty_reserved,l_qty2_reserved;
2804 IF (get_trans_for_del%NOTFOUND ) THEN
2805 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2806 CLOSE get_trans_for_del;
2807 GMI_RESERVATION_UTIL.PrintLn('Error retrieving Reserved qunatity');
2808 RAISE FND_API.G_EXC_ERROR;
2809 ELSE
2810 CLOSE get_trans_for_del;
2811 END IF;
2812 END IF;
2813
2814 l_qty_reserved := nvl(l_qty_reserved, 0);
2815 l_qty2_reserved := nvl(l_qty2_reserved, 0);
2816
2817 GMI_RESERVATION_UTIL.PrintLn('Value of l_qty_reserved is '||l_qty_reserved);
2818 GMI_RESERVATION_UTIL.PrintLn('Value of l_qty2_reserved is '||l_qty2_reserved);
2819
2820 l_qty_remaining := p_requested_quantity - l_qty_reserved;
2821 l_qty2_remaining := p_requested_quantity2 - l_qty2_reserved;
2822
2823 GMI_RESERVATION_UTIL.PrintLn('Value of remainig requested qty is '||l_qty_remaining);
2824 GMI_RESERVATION_UTIL.PrintLn('Value of remainig requested qty2 is '||l_qty2_remaining);
2825
2826 IF l_qty_remaining <= 0 THEN
2827 x_result_qty1 := p_requested_quantity;
2828 x_result_qty2 := p_requested_quantity2;
2829 ELSE -- remaining qty is > 0
2830 x_result_qty1 := l_qty_reserved;
2831 x_result_qty2 := l_qty2_reserved;
2832 END IF;
2833
2834 x_return_status := FND_API.G_RET_STS_SUCCESS;
2835
2836 GMI_RESERVATION_UTIL.PrintLn('Returning from Procedure Calulate_Prior_Reservations with Success');
2837
2838 EXCEPTION
2839 WHEN FND_API.G_EXC_ERROR THEN
2840 x_return_status := FND_API.G_RET_STS_ERROR;
2841
2842 GMI_RESERVATION_UTIL.PrintLn('Exeption GMI_Reservation_PVT.calculate_prior_reservations');
2843 FND_MSG_PUB.Add_Exc_Msg (
2844 G_PKG_NAME
2845 , l_api_name);
2846
2847 /* Get message count and data */
2848 FND_MSG_PUB.count_and_get (
2849 p_count => x_msg_count
2850 , p_data => x_msg_data);
2851
2852 WHEN NO_DATA_FOUND THEN
2853 x_return_status := FND_API.G_RET_STS_ERROR;
2854 GMI_RESERVATION_UTIL.PrintLn('No Data found Exception GMI_Reservation_PVT.calculate_prior_reservations');
2855
2856 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
2857 , l_api_name);
2858
2859 /* Get message count and data */
2860 FND_MSG_PUB.count_and_get
2861 ( p_count => x_msg_count
2862 , p_data => x_msg_data);
2863
2864 WHEN OTHERS THEN
2865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2866
2867 GMI_RESERVATION_UTIL.PrintLn('OTHERS Exception GMI_Reservation_PVT.calculate_prior_reservations');
2868
2869 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
2870 , l_api_name);
2871
2872 /* Get message count and data */
2873 FND_MSG_PUB.count_and_get (
2874 p_count => x_msg_count
2875 , p_data => x_msg_data);
2876
2877 END calculate_prior_reservations;
2878
2879 /* Api start of comments
2880 +==========================================================================+
2881 | PROCEDURE NAME |
2882 | Calc_Reservation_For_shipset |
2883 | |
2884 | TYPE |
2885 | Local |
2886 | |
2887 | USAGE |
2888 | This procedure calculates reservationsqty for a particular |
2889 | sales order/delivery detail line. |
2890 | |
2891 | |
2892 | PARAMETERS |
2893 | p_shipset_id IN NUMBER, |
2894 | p_organization_id IN NUMBER, |
2895 | p_item_id IN NUMBER, opm item_id |
2896 | p_source_header_id IN NUMBER, |
2897 | p_whse_code IN VARCHAR2, |
2898 | p_whse_loct_ctl IN NUMBER, |
2899 | p_chk_inv IN VARCHAR2, |
2900 | p_requested_quantity IN NUMBER, |
2901 | p_requested_quantity2 IN NUMBER DEFAULT NULL, |
2902 | x_shipset_reserved OUT NOCOPY VARCHAR2 |
2903 | |
2904 | |
2905 | RETURNS |
2906 | x_shipset_reserved |
2907 | 'Y' if Shipset is reserved. 'N' if shipset is not reserved |
2908 | |
2909 | |
2910 | HISTORY |
2911 | 03/16/2004 PK Added code to support shipset during pickrelease |
2912 | Enhancement Bug #3297382 |
2913 +==========================================================================+
2914 Api end of comments
2915 */
2916
2917 PROCEDURE Calc_Reservation_For_shipset(
2918 p_shipset_id IN NUMBER,
2919 p_organization_id IN NUMBER,
2920 p_item_id IN NUMBER,
2921 p_source_header_id IN NUMBER,
2922 p_whse_code IN VARCHAR2,
2923 p_whse_loct_ctl IN NUMBER,
2924 p_chk_inv IN VARCHAR2,
2925 p_requested_quantity IN NUMBER,
2926 p_requested_quantity2 IN NUMBER DEFAULT NULL,
2927 x_shipset_reserved OUT NOCOPY VARCHAR2)IS
2928
2929 -- Declaration section
2930
2931 l_shipset_found NUMBER := 0;
2932 l_opm_itm_id NUMBER;
2933 l_apps_itm_id NUMBER;
2934 l_item_loct_ctl NUMBER;
2935 l_lot_ctl NUMBER;
2936 l_noninv_ind NUMBER;
2937 l_demand_exists BOOLEAN;
2938 l_is_noctl BOOLEAN := FALSE;
2939 l_qty_reserved_real NUMBER;
2940 l_qty2_reserved_real NUMBER;
2941 l_count NUMBER := 0;
2942 l_index NUMBER := 0;
2943 l_source_line_id NUMBER;
2944 l_def_trans_qty NUMBER := 0;
2945 l_def_trans_qty2 NUMBER := 0;
2946 l_shipset_qty_avl NUMBER := 1;
2947 l_default_loct VARCHAR2(4) := fnd_profile.value('IC$DEFAULT_LOCT');
2948 --Bug 3551144
2949 l_high_lev_res_qty NUMBER := 0;
2950 l_high_lev_res_qty2 NUMBER := 0;
2951 l_real_high_lev_res_qty NUMBER := 0;
2952 l_real_high_lev_res_qty2 NUMBER := 0;
2953 l_net_high_lev_res_qty NUMBER := 0;
2954 l_net_high_lev_res_qty2 NUMBER := 0;
2955
2956 -- Cursor Declarations
2957
2958 CURSOR shipset_item IS
2959 Select delivery_detail_id, inventory_item_id, organization_id, source_line_id, requested_quantity, requested_quantity2
2960 FROM wsh_delivery_details
2961 WHERE source_header_id = p_source_header_id
2962 AND ship_set_id = p_shipset_id
2963 AND source_code = 'OE'
2964 AND released_status <> 'D';
2965
2966 itm_rec shipset_item%ROWTYPE;
2967
2968 CURSOR shipset_item_group IS
2969 Select inventory_item_id, Sum(requested_quantity) total_requested
2970 FROM wsh_delivery_details
2971 WHERE source_header_id = p_source_header_id
2972 AND ship_set_id = p_shipset_id
2973 AND source_code = 'OE'
2974 AND released_status <> 'D'
2975 Group by inventory_item_id;
2976
2977 itm_group_rec shipset_item_group%ROWTYPE;
2978
2979 CURSOR opm_itm(l_apps_itm_id NUMBER) IS
2980 SELECT item_id
2981 FROM ic_item_mst
2982 WHERE delete_mark = 0
2983 AND item_no in (SELECT segment1
2984 FROM mtl_system_items
2985 WHERE organization_id = p_organization_id
2986 AND inventory_item_id = l_apps_itm_id);
2987
2988 CURSOR get_item_ctl(l_opm_itm_id NUMBER) IS
2989 SELECT loct_ctl,lot_ctl,noninv_ind
2990 FROM ic_item_mst
2991 WHERE item_id=l_opm_itm_id;
2992
2993 CURSOR reserved_quantity_nonctl(l_apps_itm_id NUMBER ) is
2994 Select sum(nvl(requested_quantity,0)), sum(nvl(requested_quantity2,0))
2995 From wsh_delivery_details
2996 Where organization_id = p_organization_id
2997 and inventory_item_id = l_apps_itm_id
2998 and released_status in ('S', 'Y');
2999
3000
3001 CURSOR reserved_quantity_real(l_opm_itm_id NUMBER
3002 ,l_whse_code VARCHAR2 ) is
3003 SELECT ABS(SUM(nvl(trans_qty,0))),
3004 ABS(SUM(nvl(trans_qty2,0)))
3005 FROM ic_tran_pnd
3006 WHERE item_id = l_opm_itm_id
3007 AND whse_code = l_whse_code
3008 AND (lot_id <> 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
3009 AND completed_ind = 0
3010 AND delete_mark = 0
3011 AND trans_qty < 0;
3012
3013 CURSOR qty_on_hand(l_whse_code VARCHAR2,l_opm_itm_id NUMBER) IS
3014 SELECT SUM(nvl(s.onhand_order_qty,0)),
3015 SUM(nvl(s.onhand_order_qty2,0)),
3016 SUM(nvl(s.committedsales_qty,0)),
3017 SUM(nvl(s.committedsales_qty2,0))
3018 FROM ic_summ_inv s
3019 WHERE s.item_id = l_opm_itm_id
3020 AND s.whse_code = l_whse_code;
3021
3022 --Bug 3551144
3023 -- Get high level reserved qty. That is sum of requested qtys for all delivery detail lines which are
3024 -- relesed to warehouse for a given warehouse and item combination.
3025 CURSOR high_level_reserved_qty(p_organization_id NUMBER, p_inventory_item_id NUMBER) is
3026 SELECT NVL(sum(nvl(requested_quantity,0)),0), NVL(sum(nvl(requested_quantity2,0)),0)
3027 FROM wsh_delivery_details
3028 WHERE organization_id = p_organization_id
3029 AND inventory_item_id = p_inventory_item_id
3030 AND source_code = 'OE'
3031 AND released_status = 'S';
3032
3033 --Bug 3551144
3034 --Get sum of allocated qty against high level reserved qty
3035 CURSOR high_level_res_qty_real(p_item_id NUMBER,l_whse_code VARCHAR2) is
3036 SELECT NVL(ABS(SUM(nvl(trans_qty,0))),0), NVL(ABS(SUM(nvl(trans_qty2,0))),0)
3037 FROM ic_tran_pnd itp
3038 WHERE item_id = p_item_id
3039 AND whse_code = l_whse_code
3040 AND (lot_id <> 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
3041 AND completed_ind = 0
3042 AND delete_mark = 0
3043 AND trans_qty < 0
3044 AND EXISTS(SELECT 1
3045 FROM wsh_delivery_details
3046 WHERE delivery_detail_id = itp.line_detail_id
3047 AND source_code = 'OE'
3048 AND released_status = 'S');
3049
3050 BEGIN
3051
3052 GMI_Reservation_Util.PrintLn('IN Calc_Reservation_For_shipset id ' || p_shipset_id );
3053 -- check if record exists and shipset is already valid.
3054 -- If valid return requested quantity as quantity reserved.
3055 FOR i in 1..g_shipset_table.COUNT LOOP
3056 IF (g_shipset_table(i).shipset_id = p_shipset_id) THEN
3057 l_shipset_found := 1;
3058 IF (g_shipset_table(i).shipset_reserved = 'Y' AND g_shipset_table(i).shipset_valid = 'Y') THEN
3059 GMI_Reservation_Util.PrintLn('Shipset Found in global table - RESERVED - Exiting from calc_reservation_for_shipset');
3060 x_shipset_reserved := 'Y';
3061 RETURN;
3062 ELSIF (g_shipset_table(i).shipset_reserved = 'N' OR g_shipset_table(i).shipset_valid = 'N') THEN
3063 GMI_Reservation_Util.PrintLn('Shipset Found in global table - NOT RESERVED - Exiting from calc_reservation_for_shipset');
3064 x_shipset_reserved := 'N';
3065 RETURN;
3066 END IF;
3067 END IF;
3068 END LOOP;
3069
3070 GMI_Reservation_Util.PrintLn('Shipset NOT FOUND in global table ');
3071
3072
3073 -- If l_shipset_found is still zero need to load shipset in demand table.
3074 IF (l_shipset_found = 0) THEN
3075 -- Building demand table for shipset.
3076 FOR itm_rec IN shipset_item LOOP
3077 l_apps_itm_id := itm_rec.inventory_item_id;
3078 OPEN opm_itm(l_apps_itm_id);
3079 FETCH opm_itm INTO l_opm_itm_id;
3080 CLOSE opm_itm;
3081
3082 OPEN get_item_ctl(l_opm_itm_id);
3083 FETCH get_item_ctl INTO l_item_loct_ctl, l_lot_ctl, l_noninv_ind;
3084 CLOSE get_item_ctl;
3085
3086 IF (l_lot_ctl = 0 AND (l_item_loct_ctl * p_whse_loct_ctl)= 0) THEN
3087 l_is_noctl := TRUE;
3088 GMI_Reservation_Util.PrintLn('Working with no control item in shipset');
3089 END IF;
3090
3091 -- Now copy code below to build Demand table
3092 l_demand_exists := FALSE;
3093 FOR i in 1..g_demand_table.COUNT LOOP
3094 l_index := i;
3095 IF (g_demand_table(i).item_id= l_opm_itm_id AND
3096 g_demand_table(i).whse_code = p_whse_code) THEN
3097 l_demand_exists := TRUE;
3098 gmi_reservation_util.println('Building demand table for shipset if not already done');
3099 gmi_reservation_util.println('Value of item_id found in loop is '||g_demand_table(i).item_id);
3100 gmi_reservation_util.println('In opm calc_reservation_for_shipset Found the Reservation Details');
3101 gmi_reservation_util.println('value of g_demand_table(i).qty_available found is '||g_demand_table(i).qty_available);
3102 gmi_reservation_util.println('value of g_demand_table(i).qty_committed found is '||g_demand_table(i).qty_committed);
3103 gmi_reservation_util.println('value of g_demand_table(i).qty2_available found is '||g_demand_table(i).qty2_available);
3104 gmi_reservation_util.println('value of g_demand_table(i).qty2_committed found is '||g_demand_table(i).qty2_committed);
3105 EXIT;
3106 END IF;
3107 END LOOP;
3108
3109 IF (not l_demand_exists) THEN
3110 gmi_reservation_util.Println('Building demand table For Shipset item id ' || l_opm_itm_id );
3111
3112 IF (l_is_noctl = TRUE) THEN
3113 gmi_reservation_util.println('Item warehouse combination is No Control');
3114 gmi_reservation_util.Println('inventory_item_id is '||l_apps_itm_id);
3115 OPEN reserved_quantity_nonctl(l_apps_itm_id);
3116 FETCH reserved_quantity_nonctl INTO l_qty_reserved_real,l_qty2_reserved_real;
3117 CLOSE reserved_quantity_nonctl;
3118 ELSE
3119 OPEN reserved_quantity_real(l_opm_itm_id, p_whse_code);
3120 FETCH reserved_quantity_real into l_qty_reserved_real,l_qty2_reserved_real;
3121 CLOSE reserved_quantity_real;
3122 END IF;
3123 l_qty_reserved_real := nvl(l_qty_reserved_real, 0);
3124 l_qty2_reserved_real := nvl(l_qty2_reserved_real, 0);
3125
3126 gmi_reservation_util.println('value of l_qty_reserved_real '|| l_qty_reserved_real);
3127 gmi_reservation_util.println('value of l_qty2_reserved_real '|| l_qty2_reserved_real);
3128
3129 -- Let's increment the counter
3130
3131 l_count := g_demand_table.COUNT + 1;
3132
3133 g_demand_table(l_count).item_id := l_opm_itm_id;
3134 g_demand_table(l_count).whse_code := p_whse_code;
3135
3136 --Bug 3551144 added OR condition
3137 IF (p_chk_inv = 'Y' OR p_chk_inv = 'S') THEN
3138 OPEN qty_on_hand(p_whse_code,l_opm_itm_id);
3139 FETCH qty_on_hand INTO
3140 g_demand_table(l_count).qty_available,
3141 g_demand_table(l_count).qty2_available,
3142 g_demand_table(l_count).qty_committed,
3143 g_demand_table(l_count).qty2_committed ;
3144 CLOSE qty_on_hand;
3145 END IF;
3146
3147 --begin bug 3551144
3148 IF (p_chk_inv = 'S') THEN
3149 -- In case of l_is_noctl = TRUE high_level_reserved_qty is considered as part of l_qty_reserved_real
3150 IF (l_is_noctl = FALSE) THEN
3151 OPEN high_level_reserved_qty(p_organization_id,l_apps_itm_id);
3152 FETCH high_level_reserved_qty INTO l_high_lev_res_qty, l_high_lev_res_qty2;
3153 CLOSE high_level_reserved_qty;
3154 gmi_reservation_util.println('value of l_high_lev_res_qty '||l_high_lev_res_qty);
3155 gmi_reservation_util.println('value of l_high_lev_res_qty2 '||l_high_lev_res_qty2);
3156 OPEN high_level_res_qty_real(p_item_id,p_whse_code);
3157 FETCH high_level_res_qty_real into l_real_high_lev_res_qty,l_real_high_lev_res_qty2;
3158 CLOSE high_level_res_qty_real;
3159 gmi_reservation_util.println('value of l_real_high_lev_res_qty '||l_real_high_lev_res_qty);
3160 gmi_reservation_util.println('value of l_real_high_lev_res_qty2 '||l_real_high_lev_res_qty2);
3161 l_net_high_lev_res_qty := l_high_lev_res_qty - l_real_high_lev_res_qty;
3162 l_net_high_lev_res_qty2 := l_high_lev_res_qty2 - l_real_high_lev_res_qty2;
3163 IF l_net_high_lev_res_qty < 0 THEN -- This could happen in case of over allocation.
3164 l_net_high_lev_res_qty := 0;
3165 l_net_high_lev_res_qty2 := 0;
3166 END IF;
3167 gmi_reservation_util.println('value of l_net_high_lev_res_qty '||l_net_high_lev_res_qty);
3168 gmi_reservation_util.println('value of l_net_high_lev_res_qty2 '||l_net_high_lev_res_qty2);
3169 g_demand_table(l_count).qty_available := g_demand_table(l_count).qty_available - l_net_high_lev_res_qty;
3170 g_demand_table(l_count).qty2_available := g_demand_table(l_count).qty2_available - l_net_high_lev_res_qty2;
3171 END IF;
3172 END IF;
3173 --end bug 3551144
3174
3175 l_index := l_count;
3176
3177 --Bug 3551144 added OR condition
3178 IF (p_chk_inv = 'Y' OR p_chk_inv = 'S') THEN
3179 g_demand_table(l_index).qty_available := g_demand_table(l_index).qty_available - l_qty_reserved_real;
3180 g_demand_table(l_index).qty2_available := g_demand_table(l_index).qty2_available - l_qty2_reserved_real;
3181 END IF;
3182
3183 IF g_demand_table(l_index).qty_available < 0 THEN
3184 g_demand_table(l_index).qty_available := 0;
3185 g_demand_table(l_index).qty2_available := 0;
3186 END IF;
3187 IF g_demand_table(l_index).qty2_available < 0 THEN
3188 g_demand_table(l_index).qty2_available := 0;
3189 END IF;
3190
3191 gmi_reservation_util.Println('Demand table Built For Shipset item id ' || l_opm_itm_id );
3192 gmi_reservation_util.println('value of g_demand_table(l_index).qty_available '||g_demand_table(l_index).qty_available);
3193 gmi_reservation_util.println('value of g_demand_table(l_index).qty2_available '||g_demand_table(l_index).qty2_available);
3194 gmi_reservation_util.println('value of g_demand_table(l_index).qty_committed '||g_demand_table(l_index).qty_committed);
3195 gmi_reservation_util.println('value of g_demand_table(l_index).qty2_committed '||g_demand_table(l_index).qty2_committed);
3196
3197
3198 END IF; -- of not l_demand_exists
3199 -- End copy of code below.
3200 END LOOP;
3201
3202 gmi_reservation_util.println('Global Demand table is now available for all items in Shipset id '||p_shipset_id);
3203
3204 -- Now done building demand table. We should loop again to see if demand can be fulfilled.
3205 -- Validation loop should use group by item since same item/warehouse can exist in shipset (Generally not but it can)
3206 -- You have itm_rec for requested_qty and l_opm_itm_id. Find the record in demand table and check if .
3207 -- Demand can be fulfilled. If it can not be fulfilled then set l_shipset_qty_avl to zero and exit.
3208 l_shipset_qty_avl := 1;
3209 FOR itm_group_rec IN shipset_item_group LOOP
3210 l_apps_itm_id := itm_group_rec.inventory_item_id;
3211 OPEN opm_itm(l_apps_itm_id);
3212 FETCH opm_itm INTO l_opm_itm_id;
3213 CLOSE opm_itm;
3214 gmi_reservation_util.println('Checking availability in global demand table for Item id '||l_opm_itm_id);
3215 -- Find the rec in demand table
3216 FOR i in 1..g_demand_table.COUNT LOOP
3217 IF (g_demand_table(i).item_id= l_opm_itm_id AND
3218 g_demand_table(i).whse_code = p_whse_code) THEN
3219 IF (g_demand_table(i).qty_available < itm_group_rec.total_requested) THEN
3220 gmi_reservation_util.println('Demand can NOT be fulfilled for Item id '||l_opm_itm_id);
3221 l_shipset_qty_avl := 0;
3222 EXIT;
3223 ELSE
3224 gmi_reservation_util.println('Demand can be FULFILLED for Item id '||l_opm_itm_id);
3225 END IF;
3226 END IF;
3227 END LOOP;
3228 IF (l_shipset_qty_avl = 0) THEN
3229 gmi_reservation_util.println('Demand check FAILED for Shipset id '||p_shipset_id);
3230 EXIT;
3231 END IF;
3232 END LOOP;
3233 -- if l_shipset_qty_avl is still 1 means shipset availability check is successful. Then go and also book the demand
3234 -- for all the lines ( another loop) and enter Fulfilled record in PL/SQL table. Then return original requested quantity
3235 -- p_requested_quantity + (2) as reserved quantities and return.
3236 -- If it is zero then enter Failed record in PL/SQL table and return with Zero quantity reserved.
3237 IF (l_shipset_qty_avl = 0) THEN
3238 l_count := g_shipset_table.COUNT + 1;
3239 g_shipset_table(l_count).shipset_id := p_shipset_id;
3240 g_shipset_table(l_count).order_id := p_source_header_id;
3241 g_shipset_table(l_count).shipset_valid := 'N';
3242 g_shipset_table(l_count).shipset_reserved := 'N';
3243 x_shipset_reserved := 'N';
3244 gmi_reservation_util.println('Exiting calc_reservation_for_shipset check FAILED for Shipset id '||p_shipset_id);
3245 RETURN;
3246 ELSIF (l_shipset_qty_avl = 1) THEN
3247 l_count := g_shipset_table.COUNT + 1;
3248 g_shipset_table(l_count).shipset_id := p_shipset_id;
3249 g_shipset_table(l_count).order_id := p_source_header_id;
3250 g_shipset_table(l_count).shipset_valid := 'Y';
3251 g_shipset_table(l_count).shipset_reserved := 'N';
3252 gmi_reservation_util.println('Record added to global table check SUCCESSFUL for Shipset id '||p_shipset_id);
3253 END IF;
3254 -- Now book the demand for shipset
3255 gmi_reservation_util.println('Now Booking demand for Shipset id '||p_shipset_id);
3256 FOR itm_rec IN shipset_item LOOP
3257 l_apps_itm_id := itm_rec.inventory_item_id;
3258 OPEN opm_itm(l_apps_itm_id);
3259 FETCH opm_itm INTO l_opm_itm_id;
3260 CLOSE opm_itm;
3261 FOR i in 1..g_demand_table.COUNT LOOP
3262 IF (g_demand_table(i).item_id= l_opm_itm_id AND
3263 g_demand_table(i).whse_code = p_whse_code) THEN
3264 gmi_reservation_util.println('Booking demand for Item id '||l_opm_itm_id);
3265 g_demand_table(i).qty_available := g_demand_table(i).qty_available - itm_rec.requested_quantity;
3266 g_demand_table(i).qty2_available := g_demand_table(i).qty2_available - nvl(itm_rec.requested_quantity2, 0);
3267 gmi_reservation_util.Println('Demand table Updated For Shipset item id ' || l_opm_itm_id );
3268 gmi_reservation_util.println('value of g_demand_table(i).qty_available '||g_demand_table(i).qty_available);
3269 gmi_reservation_util.println('value of g_demand_table(i).qty2_available '||g_demand_table(i).qty2_available);
3270
3271 EXIT;
3272 END IF;
3273 END LOOP;
3274 END LOOP;
3275 -- Now Update g_shipset_table to mark Shipset as reserved.
3276 g_shipset_table(l_count).shipset_reserved := 'Y';
3277 x_shipset_reserved := 'Y';
3278 gmi_reservation_util.println('EXITING Record Updated- RESERVED Shipset id '||p_shipset_id);
3279 RETURN;
3280
3281
3282 END IF; -- (l_shipset_found = 0)
3283
3284
3285 END Calc_Reservation_For_shipset;
3286
3287
3288 END GMI_Reservation_PVT;