[Home] [Help]
PACKAGE BODY: APPS.GMI_RESERVATION_UTIL
Source
1 PACKAGE BODY GMI_Reservation_Util AS
2 /* $Header: GMIURSVB.pls 120.0.12020000.2 2012/10/12 15:06:45 adeshmuk ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIURSVB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private utilities relating to OPM |
13 | reservation. |
14 | |
15 | - Check_Missing |
16 | - Validation_for_Query |
17 | - Validation_Before_Allocate |
18 | - Get_Default_Lot |
19 | - Create_Default_Lot |
20 | - Get_Allocation |
21 | - Get_OPMUOM_from_AppsUOM |
22 | - Get_AppsUOM_from_OPMUOM |
23 | - Get_Org_from_SO_Line |
24 | - Get_OPM_item_from_Apps |
25 | - Reallocate |
26 | - Transfer_Msg_Stack - Removed and put into OE_MSG_PUB |
27 | - Get_DefaultLot_from_ItemCtl |
28 | - PrintLn |
29 | - Validation_ictran_pnd |
30 | - Set_Pick_Lots |
31 | - Create_Empty_Default_Lot |
32 | - Default_Lot_Exist |
33 | |
34 | HISTORY |
35 | 07-MAR-2000 odaboval Created |
36 | 27-Apr-2000 mpetrosi Removed Transfer_Msg_Stack to OE_MSG_PUB |
37 | 15-Nov-2000 odaboval B1471071 Changes the setting of the trans_date. |
38 | 16-Nov-2000 odaboval B1390816 Added a check on the Horizon period. |
39 | 12-Apr-2001 KYH B1731567 Correct 2 co_codes sharing 1 variable |
40 | 03-JUL-2001 HVERDDIN Added Old Cursors For UOM routines, this is |
41 | For Customers Not On OPM mini-pack E. Uncomment |
42 | these and Comment Out Original Search For MINI-E |
43 | 20/08/2001 FABDI B2023369 Move Order Locking issue |
44 | 20-Jun-2002 pupakare B2418860 Commented out the code to validate the |
45 | demand source header id in Check_Missing proc |
46 | 09/16/02 HAW BUG#:2536589 New procedures: update_opm_trxns, |
47 | and find_lot_id. |
48 | These procedures are called from OM file |
49 | OEXVIIFB.pls in procedure Inventory_Interface |
50 | Thess procedures will be called if user uses |
51 | the Bill To functionality from Order Pad |
52 | 9/29/02 NC Added p_commit parameter for Set_pick_lots inorder|
53 | to support the Public API allocate_opm_orders. |
54 | added IF condition to commit only if this commit |
55 | flag is set. |
56 | Oct, 2002 HW Added new procedures to support WSH.I - |
57 | Harmonization project. |
58 | Two new procedures:Validate_lot_number and |
59 | line_allocated |
60 | |
61 | Nov, 2002 HW BUG#:2654963 Added p_delivery_detail_id to proc |
62 | line_allocated |
63 | Nov, 2002 HW BUG#:2654963 Added p_delivery_detail_id to proc. |
64 | line_allocated. |
65 | |
66 | Nov, 2002 HW bug#2677054 - WSH.I project |
67 | Feb, 2003 PK Bug#2749329 - Commented call to Lock_inventory in |
68 | set_pick_lots. |
69 | Apr, 2004 Vipul BUG#3503593 - Added code in Procedure |
70 | create_transaction_for_rcv to convert the qty into|
71 | item's uom. |
72 | Aug 2004 Plowe BUG#3770264 - Added code in Procedures |
73 | create_transaction_for_rcv, set_pick_lots and |
74 | create_dflt_lot_from_scratch to |
75 | retrieve correct lang |
76 | retrieval of mtl_sales_orders |
77 | Oct 2012 Bug 14722708 - Commenting out this procedure as GMI module |
78 | is obsolete in R12. This procedure should not be used for |
79 | debugging R12 code. Instead please use appropriate product |
80 | (INV/OM/WSH) specific debugging mechanism. |
81 +=========================================================================+
82 API Name : GMI_Reservation_Util
83 Type : Private Package Body
84 Function : This package contains Private Utilities procedures used to
85 OPM reservation process.
86 -
87 Pre-reqs : N/A
88 Parameters: Per function
89
90 Current Vers : 1.0
91 */
92
93
94 /* Global variables */
95 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_Reservation_UTIL';
96
97 /* B1731568 - There is a need to distinguish between the co_code
98 associated with the OPM cust_no and that associated with
99 inventory transactions (orgn_code owning the warehouse).
100 This global var is to be used when writing inv transactions
101 to ic_tran_pnd/ic_tran_cmp */
102
103 g_co_code IC_TRAN_PND.CO_CODE%TYPE;
104
105 PROCEDURE Check_Missing
106 ( p_event IN VARCHAR2
107 , p_rec_to_check IN INV_Reservation_Global.mtl_reservation_rec_type
108 , x_return_status OUT NOCOPY VARCHAR2
109 , x_msg_count OUT NOCOPY NUMBER
110 , x_msg_data OUT NOCOPY VARCHAR2
111 ) IS
112
113 l_api_name CONSTANT VARCHAR2 (30) := 'Check_Missing';
114
115 BEGIN
116 /* =======================================================================
117 Init variables
118 ======================================================================= */
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120
121 IF (p_event = 'QUERY') THEN
122 /* =======================================================================
123 At Query(Pub) we just need to check :
124 organization_id
125 demand_source_header_id
126 demand_source_line_id
127 ======================================================================= */
128 IF ( p_rec_to_check.organization_id IS NULL
129 OR p_rec_to_check.organization_id = 0
130 OR p_rec_to_check.organization_id = FND_API.G_MISS_NUM )
131 THEN
132 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(q): Error, organization_id missing ');
133 FND_MESSAGE.Set_Name('GMI','MISSING');
134 FND_MESSAGE.Set_Token('MISSING', 'Organization_id');
135 FND_MSG_PUB.Add;
136 raise FND_API.G_EXC_ERROR;
137 END IF;
138
139 /* Begin - Bug 2418860 */
140 /*
141 IF ( p_rec_to_check.demand_source_header_id IS NULL
142 OR p_rec_to_check.demand_source_header_id = 0
143 OR p_rec_to_check.demand_source_header_id = FND_API.G_MISS_NUM )
144 THEN
145 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(q): Error, demand_source_header_id missing ');
146 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
147 FND_MESSAGE.Set_Token('MISSING', 'demand_source_header_id');
148 FND_MSG_PUB.Add;
149 raise FND_API.G_EXC_ERROR;
150 END IF;
151 */
152 /* End - Bug 2418860 */
153
154 IF ( p_rec_to_check.demand_source_line_id IS NULL
155 OR p_rec_to_check.demand_source_line_id = 0
156 OR p_rec_to_check.demand_source_line_id = FND_API.G_MISS_NUM )
157 THEN
158 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(q): Error, demand_source_line_id missing ');
159 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
160 FND_MESSAGE.Set_Token('MISSING', 'demand_source_line_id');
161 FND_MSG_PUB.Add;
162 raise FND_API.G_EXC_ERROR;
163 END IF;
164 ELSIF p_event = 'CREATE' THEN
165 /* =======================================================================
166 At Create(Pub) we just need to check :
167 organization_id
168 demand_source_header_id
169 demand_source_line_id
170 demand_source_type_id
171 inventory_item_id
172 reservation_uom_code
173 reservation_quantity
174 requirement_date
175
176 ======================================================================= */
177
178 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(c): Check organization_id ');
179 IF ( p_rec_to_check.organization_id IS NULL
180 OR p_rec_to_check.organization_id = 0
181 OR p_rec_to_check.organization_id = FND_API.G_MISS_NUM )
182 THEN
183 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
184 FND_MESSAGE.Set_Token('MISSING', 'Organization_id');
185 FND_MSG_PUB.Add;
186 raise FND_API.G_EXC_ERROR;
187 END IF;
188
189 /* Begin - Bug 2418860 */
190 /*
191 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(c): Check demand_source_header_id ');
192 IF ( p_rec_to_check.demand_source_header_id IS NULL
193 OR p_rec_to_check.demand_source_header_id = 0
194 OR p_rec_to_check.demand_source_header_id = FND_API.G_MISS_NUM )
195 THEN
196 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
197 FND_MESSAGE.Set_Token('MISSING', 'demand_source_header_id');
198 FND_MSG_PUB.Add;
199 raise FND_API.G_EXC_ERROR;
200 END IF;
201 */
202 /* End - Bug 2418860 */
203
204 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(c): Check demand_source_line_id ');
205 IF ( p_rec_to_check.demand_source_line_id IS NULL
206 OR p_rec_to_check.demand_source_line_id = 0
207 OR p_rec_to_check.demand_source_line_id = FND_API.G_MISS_NUM )
208 THEN
209 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
210 FND_MESSAGE.Set_Token('MISSING', 'demand_source_line_id');
211 FND_MSG_PUB.Add;
212 raise FND_API.G_EXC_ERROR;
213 END IF;
214
215 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(c): Check demand_source_type_id ');
216 IF ( p_rec_to_check.demand_source_type_id IS NULL
217 OR p_rec_to_check.demand_source_type_id = 0
218 OR p_rec_to_check.demand_source_type_id = FND_API.G_MISS_NUM )
219 THEN
220 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
221 FND_MESSAGE.Set_Token('MISSING', 'demand_source_type_id');
222 FND_MSG_PUB.Add;
223 raise FND_API.G_EXC_ERROR;
224 END IF;
225
226 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(c): Check inventory_item_id ');
227 IF ( p_rec_to_check.inventory_item_id IS NULL
228 OR p_rec_to_check.inventory_item_id = 0
229 OR p_rec_to_check.inventory_item_id = FND_API.G_MISS_NUM )
230 THEN
231 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
232 FND_MESSAGE.Set_Token('MISSING', 'inventory_item_id');
233 FND_MSG_PUB.Add;
234 raise FND_API.G_EXC_ERROR;
235 END IF;
236
237 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(c): Check reservation_uom_code ');
238 IF ( p_rec_to_check.reservation_uom_code IS NULL
239 OR p_rec_to_check.reservation_uom_code = FND_API.G_MISS_CHAR )
240 THEN
241 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
242 FND_MESSAGE.Set_Token('MISSING', 'reservation_uom_code');
243 FND_MSG_PUB.Add;
244 raise FND_API.G_EXC_ERROR;
245 END IF;
246
247 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(c): Check reservation_quantity ');
248 IF ( p_rec_to_check.reservation_quantity IS NULL
249 OR p_rec_to_check.reservation_quantity = 0
250 OR p_rec_to_check.reservation_quantity = FND_API.G_MISS_NUM )
251 THEN
252 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
253 FND_MESSAGE.Set_Token('MISSING', 'reservation_quantity');
254 FND_MSG_PUB.Add;
255 raise FND_API.G_EXC_ERROR;
256 END IF;
257
258 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Check_Mising(c): Check requirement_date ');
259 IF ( p_rec_to_check.requirement_date IS NULL
260 OR p_rec_to_check.requirement_date = FND_API.G_MISS_DATE )
261 THEN
262 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
263 FND_MESSAGE.Set_Token('MISSING', 'requirement_date');
264 FND_MSG_PUB.Add;
265 raise FND_API.G_EXC_ERROR;
266 END IF;
267 END IF;
268
269 GMI_reservation_Util.PrintLn('(opm_dbg) end of Util.Check_Mising(q): No Error ');
270 EXCEPTION
271 WHEN FND_API.G_EXC_ERROR THEN
272 x_return_status := FND_API.G_RET_STS_ERROR;
273
274 /* Get message count and data */
275 FND_MSG_PUB.count_and_get
276 ( p_count => x_msg_count
277 , p_data => x_msg_data
278 );
279
280 GMI_reservation_Util.PrintLn('(opm_dbg) end of Util.Check_Mising(q): EXP Error count='||x_msg_count);
281
282 WHEN OTHERS THEN
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284
285 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
286 , l_api_name
287 );
288
289 /* Get message count and data */
290 FND_MSG_Pub.Count_and_Get
291 ( p_count => x_msg_count
292 , p_data => x_msg_data
293 );
294
295 GMI_reservation_Util.PrintLn('(opm_dbg) end of Util.Check_Mising(q): UNEXP Error count='||x_msg_count);
296
297 END Check_Missing;
298
299 PROCEDURE Validation_for_Query
300 ( p_query_input IN inv_reservation_global.mtl_reservation_rec_type
301 , x_opm_um OUT NOCOPY VARCHAR2
302 , x_apps_um OUT NOCOPY VARCHAR2
303 , x_ic_item_mst_rec OUT NOCOPY GMI_Reservation_Util.ic_item_mst_rec
304 , x_return_status OUT NOCOPY VARCHAR2
305 , x_msg_count OUT NOCOPY NUMBER
306 , x_msg_data OUT NOCOPY VARCHAR2
307 , x_error_code OUT NOCOPY NUMBER /* Added parameter, Bug 2168710 */
308 ) IS
309
310 l_api_name CONSTANT VARCHAR2 (30) := 'Validation_for_Query';
311
312
313 /* ====== variables ========================================================================== */
314 l_inventory_item_id NUMBER;
315 l_order_quantity_uom VARCHAR2(3);
316
317 /* ====== cursors ============================================================================ */
318 CURSOR c_sales_order (om_line_id IN NUMBER) IS
319 SELECT inventory_item_id,
320 order_quantity_uom
321 FROM oe_order_lines_all
322 WHERE line_id = om_line_id;
323
324
325 BEGIN
326 /* ======================================================================= */
327 /* Init variables */
328 /* ======================================================================= */
329 x_return_status := FND_API.G_RET_STS_SUCCESS;
330 x_error_code := 0; /* Bug2168710 */
331
332 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Entering Validation_For_Query. line_id='
333 ||p_query_input.demand_source_line_id);
334 /* ============================================================================================= */
335 /* Get sales order lines details */
336 /* ============================================================================================= */
337 OPEN c_sales_order(p_query_input.demand_source_line_id);
338 FETCH c_sales_order
339 INTO l_inventory_item_id,
340 l_order_quantity_uom;
341
342 IF c_sales_order%NOTFOUND THEN
343 /* ================================================================ */
344 /* Don't raise any error here, because no reservation has been created for that line (anyway) */
345 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: om_line_id=NOTFOUND='
346 ||p_query_input.demand_source_line_id||'.');
347 /* ================================================================*/
348 /* FND_MESSAGE.Set_Name('GMI','SO_Not_Found'); */
349 /* FND_MESSAGE.Set_Token('LINE_ID', p_query_input.demand_source_line_id); */
350 /* FND_MSG_PUB.Add; */
351 /* RAISE FND_API.G_EXC_ERROR; */
352 ELSE
353 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: inv_item_id='||l_inventory_item_id||'.');
354
355 /* ============================================================================================= */
356 /* Get Item details */
357 /* ============================================================================================= */
358 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Entering Validation_For_Query. item_id='
359 ||l_inventory_item_id);
360 Get_OPM_item_from_Apps(
361 p_organization_id => p_query_input.organization_id
362 , p_inventory_item_id => l_inventory_item_id
363 , x_ic_item_mst_rec => x_ic_item_mst_rec
364 , x_return_status => x_return_status
365 , x_msg_count => x_msg_count
366 , x_msg_data => x_msg_data);
367
368 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
369 FND_MESSAGE.Set_Name('GMI','GMI_OPM_ITEM');
370 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_query_input.organization_id);
371 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', l_inventory_item_id);
372 FND_MSG_PUB.Add;
373 CLOSE c_sales_order; -- Bug 3598280
374 raise FND_API.G_EXC_ERROR;
375 END IF;
376
377
378
379 /* ============================================================================================= */
380 /* Get UOM details */
381 /* ============================================================================================= */
382 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Entering Validation_For_Query.');
383 x_apps_um := l_order_quantity_uom;
384 Get_OPMUOM_from_AppsUOM(
385 p_Apps_UOM => l_order_quantity_uom
386 , x_OPM_UOM => x_opm_um
387 , x_return_status => x_return_status
388 , x_msg_count => x_msg_count
389 , x_msg_data => x_msg_data);
390
391
392 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
393 THEN
394 FND_MESSAGE.Set_Name('GMI','GMI_OPM_UOM_NOT_FOUND');
395 FND_MESSAGE.Set_Token('APPS_UOM_CODE', l_order_quantity_uom);
396 FND_MSG_PUB.Add;
397 CLOSE c_sales_order; -- Bug 3598280
398 RAISE FND_API.G_EXC_ERROR;
399 ELSE
400 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: uom1='||x_opm_um||'.');
401 END IF;
402
403 END IF;
404 CLOSE c_sales_order;
405
406 EXCEPTION
407 WHEN FND_API.G_EXC_ERROR THEN
408 /* Begin - Bug 2168710 */
409 x_return_status := FND_API.G_RET_STS_ERROR;
410 x_error_code := SQLCODE;
411 /* End - Bug 2168710 */
412
413 /* Get message count and data */
414 FND_MSG_Pub.count_and_get
415 ( p_count => x_msg_count
416 , p_data => x_msg_data
417 );
418
419 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Exp_Error ');
420
421 WHEN OTHERS THEN
422 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Error in Select='||SQLCODE||'.');
423 x_return_status := SQLCODE;
424
425 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
426 , l_api_name
427 );
428
429 /* Get message count and data */
430 FND_MSG_Pub.count_and_get
431 ( p_count => x_msg_count
432 , p_data => x_msg_data
433 );
434
435 END Validation_for_Query;
436
437
438 PROCEDURE Get_Default_Lot
439 ( x_ic_tran_pnd_index OUT NOCOPY BINARY_INTEGER
440 , x_return_status OUT NOCOPY VARCHAR2
441 , x_msg_count OUT NOCOPY NUMBER
442 , x_msg_data OUT NOCOPY VARCHAR2
443 ) IS
444
445 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Default_Lot';
446
447 i BINARY_INTEGER;
448
449 BEGIN
450 /* ======================================================================= */
451 /* Init variables */
452 /* ======================================================================= */
453 x_return_status := FND_API.G_RET_STS_SUCCESS;
454 x_ic_tran_pnd_index := 0;
455
456 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Get_Default_Lot. default lot='
457 ||GMI_Reservation_Util.G_DEFAULT_LOCT||', ic_tran.COUNT='
458 ||GMI_Reservation_Util.ic_tran_rec_tbl.COUNT);
459
460 IF GMI_Reservation_Util.ic_tran_rec_tbl.COUNT > 0
461 THEN
462 i := GMI_Reservation_Util.ic_tran_rec_tbl.COUNT;
463 WHILE i >= 1
464 LOOP
465 IF (ic_tran_rec_tbl(i).location = GMI_Reservation_Util.G_DEFAULT_LOCT
466 AND ic_tran_rec_tbl(i).lot_id = 0 )
467 THEN
468 x_ic_tran_pnd_index := i;
469 /* Exit at the next loop. */
470 i := 0;
471 /* EXIT; */
472 ELSE
473 i := i - 1;
474 END IF;
475 END LOOP;
476 END IF;
477
478 IF x_ic_tran_pnd_index = 0
479 THEN
480 /* the default lot doesn't exist */
481 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Get_default_lot_qty. no default lot.');
482 ELSE
483 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Get_default_lot_qty. default lot exists.
484 default_lot_index='||x_ic_tran_pnd_index);
485
486 END IF;
487 GMI_reservation_Util.PrintLn('(opm_dbg) end of Util Get_default_lot_qty NO Error.');
488
489 EXCEPTION
490 WHEN FND_API.G_EXC_ERROR THEN
491 x_return_status := FND_API.G_RET_STS_ERROR;
492
493 /* Get message count and data */
494 FND_MSG_PUB.count_and_get
495 ( p_count => x_msg_count
496 , p_data => x_msg_data
497 );
498
499 WHEN OTHERS THEN
500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
501
502 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
503 , l_api_name
504 );
505
506 /* Get message count and data */
507 FND_MSG_PUB.count_and_get
508 ( p_count => x_msg_count
509 , p_data => x_msg_data
510 );
511
512 END Get_Default_Lot;
513
514 /* ======================================================================= */
515 /* In this procedure, only when create the default lot, uses the Item UOM. */
516 /* So convert the quantities into Item UOMs */
517 /* ======================================================================= */
518 PROCEDURE Create_Default_Lot
519 ( p_allocation_rec IN GMI_AUTO_ALLOCATE_PUB.gmi_allocation_rec
520 , p_ic_item_mst_rec IN GMI_Reservation_Util.ic_item_mst_rec
521 , p_orgn_code IN VARCHAR2
522 , p_trans_id IN NUMBER DEFAULT NULL
523 , x_return_status OUT NOCOPY VARCHAR2
524 , x_msg_count OUT NOCOPY NUMBER
525 , x_msg_data OUT NOCOPY VARCHAR2
526 ) IS
527
528 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Default_Lot';
529
530 l_ictran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
531 l_tran_row IC_TRAN_PND%ROWTYPE;
532 l_tmp_qty NUMBER(19,9);
533
534 ll_trans_id NUMBER;
535
536 -- BEGIN - Bug 3216096
537 Cursor get_line_info (l_line_id IN number) IS
538 Select SCHEDULE_SHIP_DATE
539 From oe_order_lines_all
540 Where line_id = l_line_id;
541
542 l_schedule_ship_date DATE;
543 -- END - Bug 3216096
544
545 BEGIN
546 /* ======================================================================= */
547 /* Init variables */
548 /* ======================================================================= */
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Create_default_lot.');
552 /* Fill the ic_tran_pnd record type, and then insert into ic_tran_pnd */
553 l_ictran_rec.item_id := p_ic_item_mst_rec.item_id;
554 l_ictran_rec.line_id := p_allocation_rec.line_id;
555 l_ictran_rec.co_code := g_co_code; -- B1731567
556 l_ictran_rec.orgn_code := p_orgn_code;
557 l_ictran_rec.whse_code := p_allocation_rec.whse_code;
558 l_ictran_rec.lot_id := 0; /* the default lot */
559 l_ictran_rec.location := GMI_Reservation_Util.G_DEFAULT_LOCT;
560 l_ictran_rec.doc_id := p_allocation_rec.doc_id;
561 l_ictran_rec.doc_type := 'OMSO';
562 l_ictran_rec.doc_line := p_allocation_rec.doc_line;
563 l_ictran_rec.line_type := 0;
564 l_ictran_rec.reason_code := NULL;
565 l_ictran_rec.trans_date := p_allocation_rec.trans_date;
566 l_ictran_rec.qc_grade := p_allocation_rec.prefqc_grade;
567 l_ictran_rec.user_id := p_allocation_rec.user_id;
568 l_ictran_rec.staged_ind := 0;
569 l_ictran_rec.event_id := 0;
570
571 /* ============================================================================== */
572 /* Convert order quantity into item's uom quantity */
573 /* If order UM differs from inventory UM, conversion is required.*/
574 /* The allocations are recorded as transactions written in the inventory UM */
575 /* ============================================================================== */
576 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Create_default_lot. before call GMICUOM order_um1='
577 ||p_allocation_rec.order_um1||', item_um='||p_ic_item_mst_rec.item_um||'.' );
578 IF (p_allocation_rec.order_um1 <> p_ic_item_mst_rec.item_um)
579 THEN
580 GMICUOM.icuomcv(pitem_id => p_ic_item_mst_rec.item_id,
581 plot_id => 0,
582 pcur_qty => p_allocation_rec.order_qty1,
583 pcur_uom => p_allocation_rec.order_um1,
584 pnew_uom => p_ic_item_mst_rec.item_um,
585 onew_qty => l_tmp_qty);
586
587
588 /* Invert the quantity for ic_tran_pnd */
589 l_ictran_rec.trans_qty := l_tmp_qty * (-1);
590
591 ELSE
592 l_ictran_rec.trans_qty := p_allocation_rec.order_qty1 * (-1);
593 END IF;
594
595 l_ictran_rec.trans_um := p_ic_item_mst_rec.item_um;
596 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Create_default_lot. trans_um='
597 ||p_ic_item_mst_rec.item_um||', trans_qty='||l_ictran_rec.trans_qty||'.' );
598
599 /* Note that the UOM2 are already in the Item UOM2. No need to convert. */
600 l_ictran_rec.trans_qty2 := p_allocation_rec.order_qty2 * (-1);
601 l_ictran_rec.trans_um2 := p_allocation_rec.order_um2;
602
603 /* odab remove on 30-Aug-2000
604 IF ( p_ic_item_mst_rec.dualum_ind > 0 )
605 THEN
606 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Create_default_lot. Need to populate qty2/um2.');
607 l_ictran_rec.trans_um2 := p_ic_item_mst_rec.item_um2;
608
609 GMICUOM.icuomcv(pitem_id => p_ic_item_mst_rec.item_id,
610 plot_id => 0,
611 pcur_qty => l_ictran_rec.trans_qty,
612 pcur_uom => l_ictran_rec.trans_um,
613 pnew_uom => l_ictran_rec.trans_um2,
614 onew_qty => l_ictran_rec.trans_qty2);
615
616 ELSE
617 l_ictran_rec.trans_qty2 := NULL;
618 l_ictran_rec.trans_um2 := NULL;
619 END IF;
620 odab */
621
622 /* odab : I need to more investigate this pb. */
623 /* why trans_qty2 >0 ! it should alway be <0 ! */
624 IF (nvl(l_ictran_rec.trans_qty2,0) > 0)
625 THEN
626 l_ictran_rec.trans_qty2 := l_ictran_rec.trans_qty2 * (-1);
627 END IF;
628
629 IF (p_trans_id is NULL)
630 THEN
631 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Create_default_lot.
632 before call create_pending_transaction qty1='||l_ictran_rec.trans_qty||' '
633 ||l_ictran_rec.trans_um||', qty2='||l_ictran_rec.trans_qty2||' '||l_ictran_rec.trans_um2);
634
635 GMI_Reservation_Util.Default_Lot_Exist
636 ( p_line_id => l_ictran_rec.line_id
637 , p_item_id => l_ictran_rec.item_id
638 , x_trans_id => ll_trans_id
639 , x_return_status => x_return_status
640 , x_msg_count => x_msg_count
641 , x_msg_data => x_msg_data);
642
643 IF (ll_trans_id is NULL)
644 THEN
645
646 -- BEGIN - Bug 3216096
647 -- Get the scheduled to ship date from the line
648 Open get_line_info (l_ictran_rec.line_id);
649 Fetch get_line_info into l_schedule_ship_date;
650 Close get_line_info;
651 l_ictran_rec.trans_date := l_schedule_ship_date;
652 -- END - Bug 3216096
653
654 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
655 ( p_api_version => 1.0
656 , p_init_msg_list => FND_API.G_FALSE
657 , p_commit => FND_API.G_FALSE
658 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
659 , p_tran_rec => l_ictran_rec
660 , x_tran_row => l_tran_row
661 , x_return_status => x_return_status
662 , x_msg_count => x_msg_count
663 , x_msg_data => x_msg_data
664 );
665
666 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
667 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by Create_Pending_Transaction,
668 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
669 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
670 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
671 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
672 FND_MESSAGE.Set_Token('WHERE','Create_Default_Lot');
673 FND_MSG_PUB.Add;
674 raise FND_API.G_EXC_ERROR;
675 END IF;
676 ELSE
677 GMI_reservation_Util.PrintLn('(opm_dbg) Dont create the default lot again (Shouldnt be here) - 1 ! ');
678 END IF;
679 ELSE
680 l_ictran_rec.trans_id := p_trans_id;
681
682 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Create_default_lot.
683 before call update_pending_transaction qty1='||l_ictran_rec.trans_qty||' '
684 ||l_ictran_rec.trans_um||', qty2='||l_ictran_rec.trans_qty2||' '
685 ||l_ictran_rec.trans_um2||', trans_id='||l_ictran_rec.trans_id);
686
687 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
688 ( p_api_version => 1.0
689 , p_init_msg_list => FND_API.G_FALSE
690 , p_commit => FND_API.G_FALSE
691 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
692 , p_tran_rec => l_ictran_rec
693 , x_tran_row => l_tran_row
694 , x_return_status => x_return_status
695 , x_msg_count => x_msg_count
696 , x_msg_data => x_msg_data);
697
698 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
699 THEN
700 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Default_Lot:
701 Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.' );
702 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
703 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
704 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
705 FND_MESSAGE.Set_Token('WHERE', 'Create_Default_Lot');
706 FND_MSG_PUB.Add;
707 RAISE FND_API.G_EXC_ERROR;
708 END IF;
709
710 END IF;
711
712 GMI_reservation_Util.PrintLn('(opm_dbg) end of Util Create_default_lot x_return_status='
713 || x_return_status||', x_msg_count='|| x_msg_count||'.');
714 EXCEPTION
715 WHEN FND_API.G_EXC_ERROR THEN
716 x_return_status := FND_API.G_RET_STS_ERROR;
717
718 /* Get message count and data */
719 FND_MSG_PUB.count_and_get
720 ( p_count => x_msg_count
721 , p_data => x_msg_data
722 );
723
724 WHEN OTHERS THEN
725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726
727 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
728 , l_api_name
729 );
730
731 /* Get message count and data */
732 FND_MSG_PUB.count_and_get
733 ( p_count => x_msg_count
734 , p_data => x_msg_data
735 );
736
737 END Create_Default_Lot;
738
739 PROCEDURE Validation_Before_Allocate
740 ( p_mtl_rsv_rec IN INV_Reservation_Global.mtl_reservation_rec_type
741 , x_allocation_rec OUT NOCOPY GMI_Auto_Allocate_PUB.gmi_allocation_rec
742 , x_ic_item_mst_rec OUT NOCOPY GMI_Reservation_Util.ic_item_mst_rec
743 , x_orgn_code OUT NOCOPY VARCHAR2
744 , x_return_status OUT NOCOPY VARCHAR2
745 , x_msg_count OUT NOCOPY NUMBER
746 , x_msg_data OUT NOCOPY VARCHAR2
747 ) IS
748
749 l_api_name CONSTANT VARCHAR2 (30) := 'Validation_before_Allocate';
750 l_tmp_qty NUMBER(19,9);
751
752 /* ==== Cursors ============================================================== */
753 /* removed from this cursor : */
754 /* oe_order_header_all oeh, */
755 /* AND oeh.header_id = oel.header_id */
756
757 --B1731567 - Retrieve co_code associated with cust_no to give the customer key
758 --============================================================================
759 /* bug 2245351, to carry the code forward without forking, we will eliminate the use
760 of op_cust_mst
761 the co_code should be same as g_co_code */
762 CURSOR c_customer_and_so_info (oe_line_id IN NUMBER) IS
763 SELECT oel.sold_to_org_id
764 , oel.ship_to_org_id
765 , oel.line_number + (oel.shipment_number / 10)
766 , oel.org_id
767 FROM oe_order_lines_all oel
768 WHERE oel.line_id = oe_line_id;
769
770 CURSOR c_user IS
771 SELECT user_id,
772 user_name
773 FROM fnd_user
774 WHERE user_id = FND_GLOBAL.USER_ID;
775
776 BEGIN
777 /* ======================================================================= */
778 /* Init variables */
779 /* ======================================================================= */
780 x_return_status := FND_API.G_RET_STS_SUCCESS;
781
782
783 GMI_reservation_Util.PrintLn('(opm_dbg) Entering Util validation_before_allocate:');
784
785 /* ============================================================================================= */
786 /* Initialize the allocation record type */
787 /* Note that the Qty are not converted (only the Apps/OPM UOM) */
788 /* ============================================================================================= */
789 x_allocation_rec.doc_id := p_mtl_rsv_rec.demand_source_header_id;
790 x_allocation_rec.line_id := p_mtl_rsv_rec.demand_source_line_id;
791 x_allocation_rec.trans_date := p_mtl_rsv_rec.requirement_date;
792 x_allocation_rec.prefqc_grade := p_mtl_rsv_rec.attribute1;
793 x_allocation_rec.order_qty1 := p_mtl_rsv_rec.reservation_quantity;
794 IF (p_mtl_rsv_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
795 x_allocation_rec.line_detail_id := null;
796 ELSE
797 x_allocation_rec.line_detail_id := to_number(p_mtl_rsv_rec.attribute4);
798 END IF;
799
800 /* qty2 is set after test on value of dualum_ind */
801 /* x_allocation_rec.order_qty2 := p_mtl_rsv_rec.attribute2; */
802 x_allocation_rec.user_id := FND_GLOBAL.user_id;
803
804
805 /* ============================================================================================= */
806 /* Check Source Type */
807 /* ============================================================================================= */
808 /* IF (p_mtl_rsv_rec.demand_source_name <> 'OMSO') THEN */
809 /* raise_application_error(-20001,'source type='||p_mtl_rsv_rec.demand_source_name||', not OMSO');*/
810 /* END IF; */
811
812 /* ============================================================================================= */
813 /* Get whse, and organization code from Process. */
814 /* ============================================================================================= */
815 INV_GMI_RSV_Branch.Get_Process_Org(
816 p_organization_id => p_mtl_rsv_rec.organization_id,
817 x_opm_whse_code => x_allocation_rec.whse_code,
818 x_opm_co_code => g_co_code, -- B1731567
819 x_opm_orgn_code => x_orgn_code,
820 x_return_status => x_return_status );
821
822 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
823 THEN
824 GMI_reservation_Util.PrintLn('(opm_dbg) in end of GMI_Reservation_Util.Validation_Before_Allocate
825 ERROR:Returned by Get_Process_Org.');
826 FND_MESSAGE.Set_Name('GMI','GMI_GET_PROCESS_ORG');
827 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_mtl_rsv_rec.organization_id);
828 FND_MSG_PUB.Add;
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831
832 x_allocation_rec.co_code := g_co_code;
833
834 /* ============================================================================================= */
835 /* Get Item details */
836 /* ============================================================================================= */
837 Get_OPM_item_from_Apps(
838 p_organization_id => p_mtl_rsv_rec.organization_id
839 , p_inventory_item_id => p_mtl_rsv_rec.inventory_item_id
840 , x_ic_item_mst_rec => x_ic_item_mst_rec
841 , x_return_status => x_return_status
842 , x_msg_count => x_msg_count
843 , x_msg_data => x_msg_data);
844
845
846 GMI_reservation_Util.PrintLn('(opm_dbg) in Util v: item_no='||x_ic_item_mst_rec.item_no);
847
848 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
849 THEN
850 GMI_reservation_Util.PrintLn('(opm_dbg) in end of GMI_Reservation_Util.Validation_Before_Allocate
851 ERROR:Returned by Get_OPM_item_from_Apps.');
852 FND_MESSAGE.Set_Name('GMI','GMI_OPM_ITEM');
853 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_mtl_rsv_rec.organization_id);
854 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_mtl_rsv_rec.inventory_item_id);
855 FND_MSG_PUB.Add;
856 RAISE FND_API.G_EXC_ERROR;
857 ELSE
858 x_allocation_rec.item_no := x_ic_item_mst_rec.item_no;
859 END IF;
860
861
862
863 /*=========================================================================================== */
864 /* Get Customer details */
865 /* ============================================================================================= */
866 /*
867 IN A NEXT VERSION : CUST_ID is going to be returned
868 */
869 /* B1731567 - Retrieve co_code which is part of the customer key
870 ============================================================*/
871 OPEN c_customer_and_so_info(x_allocation_rec.line_id);
872 FETCH c_customer_and_so_info
873 INTO x_allocation_rec.of_cust_id,
874 x_allocation_rec.ship_to_org_id,
875 x_allocation_rec.doc_line,
876 x_allocation_rec.org_id;
877
878 IF (c_customer_and_so_info%NOTFOUND) THEN
879 CLOSE c_customer_and_so_info;
880 GMI_reservation_Util.PrintLn('(opm_dbg) in Util v: cust_no=NOTFOUND');
881 FND_MESSAGE.Set_Name('GMI','GMI_CUST_INFO');
882 FND_MESSAGE.Set_Token('SO_LINE_ID', x_allocation_rec.line_id);
883 FND_MSG_PUB.Add;
884 RAISE FND_API.G_EXC_ERROR;
885 ELSE
886 GMI_reservation_Util.PrintLn('(opm_dbg) in Util v: cust_no='||x_allocation_rec.cust_no||',
887 doc_line='||x_allocation_rec.doc_line);
888 END IF;
889 CLOSE c_customer_and_so_info;
890
891
892 /* ============================================================================================= */
893 /* Get UOM details */
894 /* Allocated UOM1 = The primary UOM of the item */
895 /* if order_um1 <> primary UOM */
896 /* then */
897 /* convert the reserved qty into primary UOM of the item */
898 /* */
899 /* Allocated UOM2 = the dual UOM of the item */
900 /* ============================================================================================= */
901 Get_OPMUOM_from_AppsUOM(
902 p_Apps_UOM => p_mtl_rsv_rec.reservation_uom_code
903 , x_OPM_UOM => x_allocation_rec.order_um1
904 , x_return_status => x_return_status
905 , x_msg_count => x_msg_count
906 , x_msg_data => x_msg_data);
907
908
909 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
910 THEN
911 FND_MESSAGE.Set_Name('GMI','GMI_OPM_UOM_NOT_FOUND');
912 FND_MESSAGE.Set_Token('APPS_UOM_CODE', p_mtl_rsv_rec.reservation_uom_code);
913 FND_MSG_PUB.Add;
914 RAISE FND_API.G_EXC_ERROR;
915 ELSE
916 GMI_reservation_Util.PrintLn('(opm_dbg) in Util v: uom1='||x_allocation_rec.order_um1||'.');
917 END IF;
918
919 IF (x_ic_item_mst_rec.dualum_ind > 0) THEN
920 /* No need to convert Apps/OPM UOM, because in the Sales Order line, UOM2 is always the Item UOM2. */
921 x_allocation_rec.order_qty2 := p_mtl_rsv_rec.attribute2;
922 x_allocation_rec.order_um2 := x_ic_item_mst_rec.item_um2;
923
924 ELSE
925 x_allocation_rec.order_qty2 := NULL;
926 x_allocation_rec.order_um2 := NULL;
927 END IF;
928
929 /* ============================================================================================= */
930 /* Convert Reservation quantity into the Item UOM : */
931 /* Only if allocation_class is MANUAL allocation (for the default lot) */
932 /* This means that the Order_Qties/UOM are passed to the Allocation Engine. */
933 /* ============================================================================================= */
934 /* odab Added this on 1-Sept-2000, because it is not passed by the SO. */
935 IF (x_allocation_rec.order_um2 is null
936 and x_ic_item_mst_rec.item_um2 is not null)
937 THEN
938
939 IF (x_allocation_rec.order_um1 <> x_ic_item_mst_rec.item_um
940 AND (x_ic_item_mst_rec.alloc_class = ' ' OR x_ic_item_mst_rec.alloc_class IS NULL))
941 THEN
942 GMICUOM.icuomcv(pitem_id => x_ic_item_mst_rec.item_id,
943 plot_id => 0,
944 pcur_qty => p_mtl_rsv_rec.reservation_quantity,
945 pcur_uom => x_allocation_rec.order_um1,
946 pnew_uom => x_ic_item_mst_rec.item_um,
947 onew_qty => l_tmp_qty);
948
949 x_allocation_rec.order_qty1 := l_tmp_qty;
950 x_allocation_rec.order_um1 := x_ic_item_mst_rec.item_um;
951
952 ELSE
953 x_allocation_rec.order_qty1 := p_mtl_rsv_rec.reservation_quantity;
954 END IF;
955
956 /* Calculation of Qty2 from Qty1 */
957 IF x_ic_item_mst_rec.dualum_ind > 0
958 THEN
959 x_allocation_rec.order_um2 := x_ic_item_mst_rec.item_um2;
960
961 GMICUOM.icuomcv(pitem_id => x_ic_item_mst_rec.item_id,
962 plot_id => 0,
963 pcur_qty => x_allocation_rec.order_qty1,
964 pcur_uom => x_allocation_rec.order_um1,
965 pnew_uom => x_allocation_rec.order_um2,
966 onew_qty => l_tmp_qty);
967
968 x_allocation_rec.order_qty2 := l_tmp_qty;
969
970
971 ELSE
972 x_allocation_rec.order_qty2 := NULL;
973 x_allocation_rec.order_um2 := NULL;
974 END IF;
975 END IF;
976
977 /* ============================================================================================= */
978 /* Get User details not needed */
979 /* ============================================================================================= */
980
981 GMI_reservation_Util.PrintLn('(opm_dbg) Exiting Util validation_before_allocate:');
982
983
984 EXCEPTION
985 WHEN FND_API.G_EXC_ERROR THEN
986 GMI_Reservation_Util.PrintLn('Exiting Util validation_before_allocate: Error');
987 x_return_status := FND_API.G_RET_STS_ERROR;
988
989 /* Get message count and data */
990 FND_MSG_PUB.count_and_get
991 ( p_count => x_msg_count
992 , p_data => x_msg_data
993 );
994
995 WHEN OTHERS THEN
996 GMI_Reservation_Util.PrintLn('Exiting Util validation_before_allocate: ErrorOther');
997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
998
999 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1000 , l_api_name
1001 );
1002
1003 /* Get message count and data */
1004 FND_MSG_PUB.count_and_get
1005 ( p_count => x_msg_count
1006 , p_data => x_msg_data
1007 );
1008
1009
1010 END Validation_Before_Allocate;
1011
1012 PROCEDURE Get_Allocation
1013 ( p_trans_id IN NUMBER
1014 , x_ic_tran_pnd_index OUT NOCOPY BINARY_INTEGER
1015 , x_return_status OUT NOCOPY VARCHAR2
1016 , x_msg_count OUT NOCOPY NUMBER
1017 , x_msg_data OUT NOCOPY VARCHAR2
1018 ) IS
1019
1020 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Allocation';
1021
1022 i BINARY_INTEGER;
1023
1024 BEGIN
1025 /* ======================================================================= */
1026 /* Init variables */
1027 /* ======================================================================= */
1028 x_return_status := FND_API.G_RET_STS_SUCCESS;
1029 x_ic_tran_pnd_index := 0;
1030
1031 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Get_Allocation.');
1032 IF GMI_Reservation_Util.ic_tran_rec_tbl.COUNT > 0
1033 THEN
1034 i := 1;
1035 WHILE i <= GMI_Reservation_Util.ic_tran_rec_tbl.COUNT
1036 LOOP
1037 IF (ic_tran_rec_tbl(i).trans_id = p_trans_id )
1038 THEN
1039 x_ic_tran_pnd_index := i;
1040 /* Exit at the next loop. */
1041 i := GMI_Reservation_Util.ic_tran_rec_tbl.COUNT +1;
1042 /* EXIT; */
1043 ELSE
1044 i := i + 1;
1045 END IF;
1046 END LOOP;
1047 END IF;
1048
1049 IF x_ic_tran_pnd_index = 0
1050 THEN
1051 /* the default lot doesn't exist */
1052 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Get_Allocation. no transaction='||p_trans_id);
1053 ELSE
1054 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Get_Allocation. Allocation exists.');
1055
1056 END IF;
1057 GMI_reservation_Util.PrintLn('(opm_dbg) end of Util Get_Allocation.');
1058
1059 EXCEPTION
1060 WHEN FND_API.G_EXC_ERROR THEN
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062
1063 /* Get message count and data */
1064 FND_MSG_PUB.count_and_get
1065 ( p_count => x_msg_count
1066 , p_data => x_msg_data
1067 );
1068
1069 WHEN OTHERS THEN
1070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071
1072 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1073 , l_api_name
1074 );
1075
1076 /* Get message count and data */
1077 FND_MSG_PUB.count_and_get
1078 ( p_count => x_msg_count
1079 , p_data => x_msg_data
1080 );
1081
1082
1083 END Get_Allocation;
1084
1085 PROCEDURE Get_OPMUOM_from_AppsUOM(
1086 p_Apps_UOM IN VARCHAR2
1087 , x_OPM_UOM OUT NOCOPY VARCHAR2
1088 , x_return_status OUT NOCOPY VARCHAR2
1089 , x_msg_count OUT NOCOPY NUMBER
1090 , x_msg_data OUT NOCOPY VARCHAR2
1091 ) IS
1092
1093 l_api_name CONSTANT VARCHAR2 (30) := 'Get_OPMUOM_from_AppsUOM';
1094
1095 /* For OPM MiniPack E customers */
1096 CURSOR c_uom (discrete_uom IN VARCHAR) IS
1097 SELECT sy.um_code
1098 FROM mtl_units_of_measure mtl,
1099 sy_uoms_mst sy
1100 WHERE sy.unit_of_measure = mtl.unit_of_measure
1101 AND mtl.uom_code = discrete_uom;
1102
1103 /*
1104 Only UNCOMMENT for NON - MINI-E customers
1105 CURSOR c_uom (discrete_uom IN VARCHAR) IS
1106 SELECT sy.um_code
1107 FROM mtl_units_of_measure mtl,
1108 sy_uoms_mst sy
1109 WHERE sy.um_code = mtl.unit_of_measure
1110 AND mtl.uom_code = discrete_uom;
1111 */
1112
1113 BEGIN
1114 /* ======================================================================= */
1115 /* Init variables */
1116 /* ======================================================================= */
1117 x_return_status := FND_API.G_RET_STS_SUCCESS;
1118
1119
1120 OPEN c_uom(p_Apps_UOM);
1121 FETCH c_uom
1122 INTO x_OPM_UOM;
1123
1124 IF c_uom%NOTFOUND THEN
1125 FND_MESSAGE.Set_Name('GMI','GMI_OPM_UOM_NOT_FOUND');
1126 FND_MESSAGE.Set_Token('APPS_UOM_CODE', p_Apps_UOM);
1127 FND_MSG_PUB.Add;
1128 CLOSE c_uom; -- Bug 3598280
1129 RAISE FND_API.G_EXC_ERROR;
1130 END IF;
1131 CLOSE c_uom;
1132
1133
1134 EXCEPTION
1135 WHEN FND_API.G_EXC_ERROR THEN
1136 x_return_status := FND_API.G_RET_STS_ERROR;
1137
1138 /* Get message count and data */
1139 FND_MSG_PUB.count_and_get
1140 ( p_count => x_msg_count
1141 , p_data => x_msg_data
1142 );
1143
1144 WHEN OTHERS THEN
1145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1146
1147 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1148 , l_api_name
1149 );
1150
1151 /* Get message count and data */
1152 FND_MSG_PUB.count_and_get
1153 ( p_count => x_msg_count
1154 , p_data => x_msg_data
1155 );
1156
1157
1158 END Get_OPMUOM_from_AppsUOM;
1159
1160 PROCEDURE Get_AppsUOM_from_OPMUOM(
1161 p_OPM_UOM IN VARCHAR2
1162 , x_Apps_UOM OUT NOCOPY VARCHAR2
1163 , x_return_status OUT NOCOPY VARCHAR2
1164 , x_msg_count OUT NOCOPY NUMBER
1165 , x_msg_data OUT NOCOPY VARCHAR2
1166 ) IS
1167
1168 l_api_name CONSTANT VARCHAR2 (30) := 'Get_AppsUOM_from_OPMUOM';
1169
1170
1171 /* For OPM MiniPack E customers */
1172 CURSOR c_uom (process_uom IN VARCHAR) IS
1173 SELECT mtl.uom_code
1174 FROM mtl_units_of_measure mtl,
1175 sy_uoms_mst sy
1176 WHERE sy.unit_of_measure = mtl.unit_of_measure
1177 AND sy.um_code = process_uom;
1178
1179 /*
1180 Only UNCOMMENT for NON - MINI-E customers
1181 CURSOR c_uom (process_uom IN VARCHAR) IS
1182 SELECT mtl.uom_code
1183 FROM mtl_units_of_measure mtl,
1184 sy_uoms_mst sy
1185 WHERE sy.um_code = mtl.unit_of_measure
1186 AND sy.um_code = process_uom;
1187 */
1188
1189
1190 BEGIN
1191 /* ======================================================================= */
1192 /* Init variables */
1193 /* ======================================================================= */
1194 x_return_status := FND_API.G_RET_STS_SUCCESS;
1195
1196
1197 OPEN c_uom(p_OPM_UOM);
1198 FETCH c_uom
1199 INTO x_Apps_UOM;
1200
1201 IF c_uom%NOTFOUND THEN
1202 FND_MESSAGE.Set_Name('GMI','GMI_APPS_UOM_NOT_FOUND');
1203 FND_MESSAGE.Set_Token('OPM_UOM_CODE', p_OPM_UOM);
1204 FND_MSG_PUB.Add;
1205 CLOSE c_uom; -- Bug 3598280
1206 RAISE FND_API.G_EXC_ERROR;
1207 END IF;
1208 CLOSE c_uom;
1209
1210
1211 EXCEPTION
1212 WHEN FND_API.G_EXC_ERROR THEN
1213 x_return_status := FND_API.G_RET_STS_ERROR;
1214
1215 /* Get message count and data */
1216 FND_MSG_PUB.count_and_get
1217 ( p_count => x_msg_count
1218 , p_data => x_msg_data
1219 );
1220
1221 WHEN OTHERS THEN
1222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1223
1224 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1225 , l_api_name
1226 );
1227
1228 /* Get message count and data */
1229 FND_MSG_PUB.count_and_get
1230 ( p_count => x_msg_count
1231 , p_data => x_msg_data
1232 );
1233
1234 END Get_AppsUOM_from_OPMUOM;
1235
1236 PROCEDURE Get_Org_from_SO_Line
1237 ( p_oe_line_id IN NUMBER
1238 , x_organization_id OUT NOCOPY NUMBER
1239 , x_return_status OUT NOCOPY VARCHAR2
1240 , x_msg_count OUT NOCOPY NUMBER
1241 , x_msg_data OUT NOCOPY VARCHAR2
1242 ) IS
1243
1244 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Org_from_SO_Line';
1245
1246 CURSOR c_org (oe_line_id IN NUMBER) IS
1247 SELECT ship_from_org_id
1248 FROM oe_order_lines_all
1249 WHERE line_id = oe_line_id;
1250
1251 BEGIN
1252 /* ======================================================================= */
1253 /* Init variables */
1254 /* ======================================================================= */
1255 x_return_status := FND_API.G_RET_STS_SUCCESS;
1256
1257 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Get_org line_id='||p_oe_line_id);
1258
1259 OPEN c_org(p_oe_line_id);
1260 FETCH c_org
1261 INTO x_organization_id;
1262
1263 IF c_org%NOTFOUND THEN
1264 FND_MESSAGE.Set_Name('GMI','GMI_ORG_NOT_FOUND_IN_OE');
1265 FND_MESSAGE.Set_Token('SO_LINE_ID', p_oe_line_id);
1266 FND_MSG_PUB.Add;
1267 CLOSE c_org; -- Bug 3598280
1268 RAISE FND_API.G_EXC_ERROR;
1269 END IF;
1270 CLOSE c_org;
1271
1272 GMI_reservation_Util.PrintLn('(opm_dbg) in Util Get_org org_id='||x_organization_id);
1273
1274 EXCEPTION
1275 WHEN FND_API.G_EXC_ERROR THEN
1276 x_return_status := FND_API.G_RET_STS_ERROR;
1277
1278 /* Get message count and data */
1279 FND_MSG_PUB.count_and_get
1280 ( p_count => x_msg_count
1281 , p_data => x_msg_data
1282 );
1283
1284 WHEN OTHERS THEN
1285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1286
1287 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1288 , l_api_name
1289 );
1290
1291 /* Get message count and data */
1292 FND_MSG_PUB.count_and_get
1293 ( p_count => x_msg_count
1294 , p_data => x_msg_data
1295 );
1296
1297 END Get_Org_from_SO_Line;
1298
1299
1300 PROCEDURE Get_OPM_item_from_Apps
1301 ( p_organization_id IN NUMBER
1302 , p_inventory_item_id IN NUMBER
1303 , x_ic_item_mst_rec OUT NOCOPY GMI_Reservation_Util.ic_item_mst_rec
1304 , x_return_status OUT NOCOPY VARCHAR2
1305 , x_msg_count OUT NOCOPY NUMBER
1306 , x_msg_data OUT NOCOPY VARCHAR2
1307 ) IS
1308
1309 l_api_name CONSTANT VARCHAR2 (30) := 'Get_OPM_item_from_Apps';
1310
1311 CURSOR c_item ( discrete_org_id IN NUMBER
1312 , discrete_item_id IN NUMBER) IS
1313 SELECT item_id
1314 , discrete_item_id
1315 , item_no
1316 , whse_item_id
1317 , item_um
1318 , item_um2
1319 , dualum_ind
1320 , alloc_class
1321 , noninv_ind
1322 , deviation_lo
1323 , deviation_hi
1324 , grade_ctl
1325 , inactive_ind
1326 , lot_ctl
1327 , lot_indivisible
1328 , loct_ctl
1329 FROM ic_item_mst
1330 WHERE delete_mark = 0
1331 AND item_no in (SELECT segment1
1332 FROM mtl_system_items
1333 WHERE organization_id = discrete_org_id
1334 AND inventory_item_id = discrete_item_id);
1335
1336 BEGIN
1337 /* ======================================================================= */
1338 /* Init variables */
1339 /* ======================================================================= */
1340 x_return_status := FND_API.G_RET_STS_SUCCESS;
1341
1342 OPEN c_item( p_organization_id
1343 , p_inventory_item_id);
1344 FETCH c_item
1345 INTO x_ic_item_mst_rec;
1346
1347 IF c_item%NOTFOUND THEN
1348 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: item_no=NOTFOUND inv_item_id='
1349 ||p_inventory_item_id||', org_id='||p_organization_id);
1350 FND_MESSAGE.Set_Name('GMI','GMI_OPM_ITEM');
1351 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
1352 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
1353 FND_MSG_PUB.Add;
1354 CLOSE c_item; -- Bug 3598280
1355 RAISE FND_API.G_EXC_ERROR;
1356 ELSE
1357 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: item_no='||x_ic_item_mst_rec.item_no||'.');
1358 END IF;
1359 CLOSE c_item;
1360
1361
1362 EXCEPTION
1363 WHEN FND_API.G_EXC_ERROR THEN
1364 x_return_status := FND_API.G_RET_STS_ERROR;
1365
1366 /* Get message count and data */
1367 FND_MSG_PUB.count_and_get
1368 ( p_count => x_msg_count
1369 , p_data => x_msg_data
1370 );
1371
1372 WHEN OTHERS THEN
1373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1374
1375 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1376 , l_api_name
1377 );
1378
1379 /* Get message count and data */
1380 FND_MSG_PUB.count_and_get
1381 ( p_count => x_msg_count
1382 , p_data => x_msg_data
1383 );
1384
1385 END Get_OPM_item_from_Apps;
1386
1387 PROCEDURE Reallocate
1388 ( p_query_input IN inv_reservation_global.mtl_reservation_rec_type
1389 , x_allocated_trans OUT NOCOPY NUMBER
1390 , x_allocated_qty OUT NOCOPY NUMBER
1391 , x_return_status OUT NOCOPY VARCHAR2
1392 , x_msg_count OUT NOCOPY NUMBER
1393 , x_msg_data OUT NOCOPY VARCHAR2
1394 ) IS
1395
1396 l_api_name CONSTANT VARCHAR2 (30) := 'Reallocate';
1397
1398 l_serial_number inv_reservation_global.serial_number_tbl_type;
1399 l_partial_reservation_flag VARCHAR2(2) := FND_API.G_FALSE;
1400 l_quantity_reserved NUMBER(19,9);
1401 l_reservation_id NUMBER;
1402 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
1403 l_mtl_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
1404 l_reservation_count NUMBER;
1405 l_error_code NUMBER;
1406
1407 l_default_lot_index BINARY_INTEGER;
1408 l_default_lot_qty NUMBER(19,9);
1409 l_default_lot_um VARCHAR2(3);
1410 l_primary_reservation_qty NUMBER(19,9);
1411 l_secondary_reservation_qty NUMBER(19,9);
1412 l_allocated_transactions NUMBER;
1413 l_delta_Not_Alloc_qty NUMBER(19,9);
1414 l_delta_Not_Alloc_qty2 NUMBER(19,9);
1415 l_default_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
1416
1417 l_qc_grade VARCHAR2(10);
1418 l_trans_qty2 NUMBER(19,9);
1419 l_trans_um2 VARCHAR2(4);
1420
1421 -- BUG 3538734
1422 l_organization_id NUMBER;
1423 l_inventory_item_id NUMBER;
1424 l_ctl_ind VARCHAR2(1);
1425
1426 /* ====== cursors ============================================================================*/
1427 CURSOR c_sales_order_line (om_line_id IN NUMBER) IS
1428 SELECT preferred_grade,
1429 ordered_quantity2 * (-1),
1430 ordered_quantity_uom2,
1431 ship_from_org_id, -- BUG 3538734
1432 inventory_item_id
1433 FROM oe_order_lines_all
1434 WHERE line_id = om_line_id;
1435
1436
1437 BEGIN
1438
1439 GMI_Reservation_Util.PrintLn('Entering GMI_Reservation_Util.Reallocate');
1440 GMI_Reservation_Util.PrintLn('(opm_dbg) attribute1='||p_query_input.attribute1);
1441 GMI_Reservation_Util.PrintLn('(opm_dbg) attribute2='||p_query_input.attribute2);
1442 GMI_Reservation_Util.PrintLn('(opm_dbg) attribute3='||p_query_input.attribute3);
1443 GMI_Reservation_Util.PrintLn('(opm_dbg) attribute4='||p_query_input.attribute4);
1444 GMI_Reservation_Util.PrintLn('(opm_dbg) reservation_qty= '||p_query_input.reservation_quantity);
1445
1446 /* ======================================================================= */
1447 /* Init variables */
1448 /* ======================================================================= */
1449 x_return_status := FND_API.G_RET_STS_SUCCESS;
1450
1451 SAVEPOINT Reallocate_Transactions;
1452
1453
1454 /* ========================================================== */
1455 /* Get Process features from the sales order. */
1456 /* ========================================================== */
1457 OPEN c_sales_order_line(p_query_input.demand_source_line_id);
1458 FETCH c_sales_order_line
1459 INTO l_qc_grade
1460 , l_trans_qty2
1461 , l_trans_um2
1462 , l_organization_id -- BUG 3538734
1463 , l_inventory_item_id;
1464
1465 IF (c_sales_order_line%NOTFOUND)
1466 THEN
1467 GMI_Reservation_Util.printLn('Query SO_line failed with so_line_id='||p_query_input.demand_source_line_id);
1468
1469 FND_MESSAGE.Set_Name('GMI','GMI_QRY_SO_FAILED');
1470 FND_MESSAGE.Set_Token('SO_LINE_ID', p_query_input.demand_source_line_id);
1471 FND_MESSAGE.Set_Token('WHERE', l_api_name);
1472 FND_MSG_PUB.Add;
1473 CLOSE c_sales_order_line; -- Bug 3598280
1474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1475 END IF;
1476 CLOSE c_sales_order_line;
1477
1478
1479 IF (p_query_input.reservation_quantity > 0)
1480 THEN
1481 /* should use query input quanitity*/
1482 /* ============================================================================================= */
1483 /* Prepare the mtl_reservation_rec in order to call Create_Reservation :*/
1484 /* ============================================================================================= */
1485 l_mtl_reservation_rec.reservation_id := NULL; /* cannot know */
1486 l_mtl_reservation_rec.requirement_date := p_query_input.requirement_date;
1487 l_mtl_reservation_rec.organization_id := p_query_input.organization_id;
1488 l_mtl_reservation_rec.inventory_item_id := p_query_input.inventory_item_id;
1489 l_mtl_reservation_rec.demand_source_type_id := p_query_input.demand_source_type_id;
1490 l_mtl_reservation_rec.demand_source_name := NULL;
1491 l_mtl_reservation_rec.demand_source_header_id := p_query_input.demand_source_header_id;
1492 l_mtl_reservation_rec.demand_source_line_id := p_query_input.demand_source_line_id;
1493 l_mtl_reservation_rec.demand_source_delivery := NULL;
1494 l_mtl_reservation_rec.primary_uom_code := p_query_input.reservation_uom_code;
1495 l_mtl_reservation_rec.primary_uom_id := NULL;
1496 l_mtl_reservation_rec.reservation_uom_code := p_query_input.reservation_uom_code;
1497 l_mtl_reservation_rec.reservation_uom_id := NULL;
1498 l_mtl_reservation_rec.reservation_quantity := p_query_input.reservation_quantity;
1499 l_mtl_reservation_rec.primary_reservation_quantity := p_query_input.reservation_quantity;
1500 l_mtl_reservation_rec.autodetail_group_id := NULL;
1501 l_mtl_reservation_rec.external_source_code := NULL;
1502 l_mtl_reservation_rec.external_source_line_id := NULL;
1503 l_mtl_reservation_rec.supply_source_header_id := NULL;
1504 l_mtl_reservation_rec.supply_source_line_id := NULL;
1505 l_mtl_reservation_rec.supply_source_name := NULL;
1506 l_mtl_reservation_rec.supply_source_line_detail := NULL;
1507 l_mtl_reservation_rec.revision := NULL;
1508 l_mtl_reservation_rec.subinventory_code := NULL;
1509 l_mtl_reservation_rec.subinventory_id := NULL;
1510 l_mtl_reservation_rec.locator_id := NULL;
1511 l_mtl_reservation_rec.lot_number := NULL;
1512 l_mtl_reservation_rec.lot_number_id := NULL;
1513 l_mtl_reservation_rec.pick_slip_number := NULL;
1514 l_mtl_reservation_rec.lpn_id := NULL;
1515 l_mtl_reservation_rec.attribute_category := NULL;
1516 l_mtl_reservation_rec.attribute1 := l_qc_grade;
1517 l_mtl_reservation_rec.attribute2 := p_query_input.attribute2;
1518 l_mtl_reservation_rec.attribute3 := l_trans_um2;
1519 /* attribute4 is used for line_detail_id*/
1520 l_mtl_reservation_rec.attribute4 := p_query_input.attribute4;
1521 l_mtl_reservation_rec.attribute5 := NULL;
1522 l_mtl_reservation_rec.attribute6 := NULL;
1523 l_mtl_reservation_rec.attribute7 := NULL;
1524 l_mtl_reservation_rec.attribute8 := NULL;
1525 l_mtl_reservation_rec.attribute9 := NULL;
1526 l_mtl_reservation_rec.attribute10 := NULL;
1527 l_mtl_reservation_rec.attribute11 := NULL;
1528 l_mtl_reservation_rec.attribute12 := NULL;
1529 l_mtl_reservation_rec.attribute13 := NULL;
1530 l_mtl_reservation_rec.attribute14 := NULL;
1531 l_mtl_reservation_rec.attribute15 := NULL;
1532 l_mtl_reservation_rec.ship_ready_flag := 2;
1533 l_mtl_reservation_rec.detailed_quantity := 0;
1534
1535
1536 GMI_Reservation_Util.printLn(' (opm_dbg) just before calling Create_Reservation for qty='||
1537 l_delta_Not_Alloc_qty||', shed_ship_date='||l_mtl_reservation_rec.requirement_date);
1538 GMI_Reservation_Util.printLn(' (opm_dbg) more info: qty='||l_mtl_reservation_rec.reservation_quantity||',
1539 um='||l_mtl_reservation_rec.reservation_uom_code||',
1540 qty2='||l_mtl_reservation_rec.attribute2||', um2='||l_mtl_reservation_rec.attribute3);
1541 GMI_Reservation_PUB.Create_Reservation
1542 ( p_api_version_number => 1.0
1543 , p_init_msg_lst => FND_API.G_FALSE
1544 , x_return_status => x_return_status
1545 , x_msg_count => x_msg_count
1546 , x_msg_data => x_msg_data
1547 , p_rsv_rec => l_mtl_reservation_rec
1548 , p_serial_number => l_serial_number
1549 , x_serial_number => l_serial_number
1550 , p_partial_reservation_flag => l_partial_reservation_flag
1551 , p_force_reservation_flag => FND_API.G_TRUE
1552 , x_quantity_reserved => l_quantity_reserved
1553 , x_reservation_id => l_reservation_id
1554 );
1555 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1556 THEN
1557 GMI_reservation_Util.PrintLn('(opm_dbg) before Transfer_Msg_Stack');
1558 RAISE FND_API.G_EXC_ERROR;
1559 END IF;
1560 /* At this stage, the Reallocation is done. */
1561 ELSE
1562 GMI_Reservation_Util.printLn(' (opm_dbg) Nothing to Auto-Detail.');
1563 END IF; /* End of l_delta_Not_Alloc_qty >0 */
1564
1565 GMI_RESERVATION_PUB.query_reservation
1566 ( p_api_version_number => 1.0
1567 , p_query_input => p_query_input
1568 , x_mtl_reservation_tbl => l_mtl_reservation_tbl
1569 , x_mtl_reservation_tbl_count => l_reservation_count
1570 , x_error_code => l_error_code
1571 , x_return_status => x_return_status
1572 , x_msg_count => x_msg_count
1573 , x_msg_data => x_msg_data
1574 );
1575
1576 /* Return an error if the query reservations call failed */
1577 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
1578 THEN
1579 GMI_Reservation_Util.printLn('return error from query reservation');
1580 FND_MESSAGE.Set_Name('GMI','GMI_QRY_RSV_FAILED');
1581 FND_MESSAGE.Set_Token('SO_LINE_ID', p_query_input.demand_source_line_id);
1582 FND_MESSAGE.Set_Token('WHERE', l_api_name);
1583 FND_MSG_PUB.Add;
1584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1585 END IF;
1586
1587 GMI_Reservation_Util.printLn('After Query_Res, count='||l_reservation_count);
1588 IF (l_reservation_count <= 0)
1589 THEN
1590 IF (l_reservation_count < 0)
1591 THEN
1592 GMI_Reservation_Util.printLn('Invalid value for reservation_count='||l_reservation_count);
1593 FND_MESSAGE.Set_Name('INV','INV_INVALID_COUNT');
1594 FND_MSG_PUB.Add;
1595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1596 END IF;
1597
1598 GMI_Reservation_Util.printLn('As nothing reserved, need to build the default_lot rec_type,
1599 by querying the sales order, then populate the mtl rec_type');
1600
1601 l_default_lot_index := 1;
1602
1603 GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index).qc_grade := l_qc_grade;
1604 GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index).trans_qty2:= l_trans_qty2;
1605 GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index).trans_um2 := l_trans_um2;
1606 GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index).line_id := p_query_input.demand_source_line_id;
1607
1608 l_delta_Not_Alloc_qty := p_query_input.reservation_quantity;
1609 l_delta_Not_Alloc_qty2 := p_query_input.attribute2;
1610 l_default_lot_um := p_query_input.reservation_uom_code;
1611
1612 GMI_Reservation_Util.PrintLn(' (opm_dbg) Nothing reserved');
1613
1614 ELSE
1615 GMI_reservation_Util.PrintLn('(opm_dbg) in Reallocate, qty='
1616 ||GMI_Reservation_Util.ic_tran_rec_tbl(1).trans_qty||',
1617 qty2='||GMI_Reservation_Util.ic_tran_rec_tbl(1).trans_qty2);
1618
1619 /* Get the Default Lot, and default lot quantity */
1620 /* Retrieve the default lot in the transaction (being aware of the item controls) */
1621 GMI_Reservation_Util.Get_Default_Lot(
1622 x_ic_tran_pnd_index => l_default_lot_index
1623 , x_return_status => x_return_status
1624 , x_msg_count => x_msg_count
1625 , x_msg_data => x_msg_data);
1626 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1627 THEN
1628 GMI_reservation_Util.PrintLn('(opm_dbg) in Reallocate, ERROR:Returned by Get_Default_Lot.');
1629 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1630 FND_MESSAGE.Set_Token('BY_PROC','GMI_Reservation_Util.Get_Default_Lot');
1631 FND_MESSAGE.Set_Token('WHERE','Reallocate');
1632 FND_MSG_PUB.Add;
1633 RAISE FND_API.G_EXC_ERROR;
1634 END IF;
1635
1636 GMI_reservation_Util.PrintLn('(opm_dbg) in Reallocate, qty='
1637 ||GMI_Reservation_Util.ic_tran_rec_tbl(1).trans_qty||',
1638 qty2='||GMI_Reservation_Util.ic_tran_rec_tbl(1).trans_qty2);
1639 END IF;
1640
1641 l_default_tran_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index);
1642
1643 balance_default_lot
1644 ( p_ic_default_rec => l_default_tran_rec
1645 , p_opm_item_id => l_default_tran_rec.item_id
1646 , x_return_status => x_return_status
1647 , x_msg_count => x_msg_count
1648 , x_msg_data => x_msg_data
1649 );
1650
1651 -- BEGIN - BUG 3538734
1652 check_lot_loct_ctl
1653 ( p_inventory_item_id => l_inventory_item_id
1654 ,p_mtl_organization_id => l_organization_id
1655 ,x_ctl_ind => l_ctl_ind
1656 ) ;
1657
1658 IF (l_ctl_ind = 'Y') THEN --- either lot or location control exists
1659 select NVL(SUM(ABS(TRANS_QTY)),0)
1660 into x_allocated_qty
1661 from ic_tran_pnd
1662 where line_id = p_query_input.demand_source_line_id
1663 and trans_id <> l_default_tran_rec.trans_id
1664 and (lot_id <> 0 or location <> gmi_reservation_util.g_default_loct)
1665 and doc_type='OMSO'
1666 and delete_mark =0
1667 and completed_ind=0;
1668 ELSE --- no control
1669 select NVL(SUM(ABS(TRANS_QTY)),0)
1670 into x_allocated_qty
1671 from ic_tran_pnd
1672 where line_id = p_query_input.demand_source_line_id
1673 and lot_id = 0
1674 and location = gmi_reservation_util.g_default_loct
1675 and doc_type='OMSO'
1676 and delete_mark =0
1677 and completed_ind=0;
1678 END IF;
1679 -- END - BUG 3538734
1680
1681 /* HW 2296620 This is wrong !!!!!
1682 x_allocated_qty := l_quantity_reserved; */
1683
1684 Select count(*)
1685 INTO x_allocated_trans
1686 From ic_tran_pnd
1687 Where line_id = p_query_input.demand_source_line_id
1688 And line_detail_id = p_query_input.attribute4
1689 And delete_mark=0;
1690
1691 GMI_Reservation_Util.printLn('End of GMI_Reservation.Reallocate. No Error, Allocated_qty='||x_allocated_qty);
1692 GMI_Reservation_Util.printLn('End of GMI_Reservation.Reallocate. No Error, Allocated_transactions='||x_allocated_trans);
1693
1694 EXCEPTION
1695 WHEN FND_API.G_EXC_ERROR THEN
1696 ROLLBACK TO SAVEPOINT Reallocate_Transactions;
1697 GMI_Reservation_Util.printLn('End of GMI_Reservation.Reallocate. Error');
1698 x_return_status := FND_API.G_RET_STS_ERROR;
1699
1700 /* Get message count and data */
1701 FND_MSG_PUB.count_and_get
1702 ( p_count => x_msg_count
1703 , p_data => x_msg_data
1704 );
1705
1706 WHEN OTHERS THEN
1707 ROLLBACK TO SAVEPOINT Reallocate_Transactions;
1708 GMI_Reservation_Util.printLn('End of GMI_Reservation.Reallocate. ErrorOthers');
1709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1710
1711 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1712 , l_api_name
1713 );
1714
1715 /* Get message count and data */
1716 FND_MSG_PUB.count_and_get
1717 ( p_count => x_msg_count
1718 , p_data => x_msg_data
1719 );
1720
1721
1722 END Reallocate;
1723
1724
1725
1726
1727 PROCEDURE Get_DefaultLot_from_ItemCtl
1728 ( p_organization_id IN NUMBER
1729 , p_inventory_item_id IN NUMBER
1730 , x_default_lot_index OUT NOCOPY NUMBER
1731 , x_return_status OUT NOCOPY VARCHAR2
1732 , x_msg_count OUT NOCOPY NUMBER
1733 , x_msg_data OUT NOCOPY VARCHAR2
1734 ) IS
1735
1736 l_api_name CONSTANT VARCHAR2 (30) := 'Get_DefaultLot_from_ItemCtl';
1737
1738 l_ic_item_mst_rec GMI_Reservation_Util.ic_item_mst_rec;
1739
1740 -- added by fabdi 10/01/2001
1741 -- fix for bug # 1574957
1742 l_whse_ctl number;
1743
1744 Cursor get_whse_ctl (org_id IN NUMBER)
1745 IS
1746 select loct_ctl
1747 from ic_whse_mst
1748 where mtl_organization_id = org_id;
1749
1750 -- end fabdi
1751
1752 BEGIN
1753
1754 /* ======================================================================= */
1755 /* Init variables */
1756 /* ======================================================================= */
1757 x_return_status := FND_API.G_RET_STS_SUCCESS;
1758
1759 /* Get the Default Lot, and default lot quantity */
1760 /* Retrieve the default lot in the transaction */
1761 GMI_Reservation_Util.Get_Default_Lot(
1762 x_ic_tran_pnd_index => x_default_lot_index
1763 , x_return_status => x_return_status
1764 , x_msg_count => x_msg_count
1765 , x_msg_data => x_msg_data);
1766
1767 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1768 THEN
1769 GMI_reservation_Util.PrintLn('(opm_dbg) in Get_DefaultLot_from_ItemCtl, ERROR:Returned by Get_Default_Lot.');
1770 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1771 FND_MESSAGE.Set_Token('BY_PROC','GMI_Reservation_Util.Get_Default_Lot');
1772 FND_MESSAGE.Set_Token('WHERE','Get_DefaultLot_from_ItemCtl');
1773 FND_MSG_PUB.Add;
1774 RAISE FND_API.G_EXC_ERROR;
1775 END IF;
1776
1777 /* ============================================================================================= */
1778 /* Get Item details */
1779 /* ============================================================================================= */
1780 Get_OPM_item_from_Apps(
1781 p_organization_id => p_organization_id
1782 , p_inventory_item_id => p_inventory_item_id
1783 , x_ic_item_mst_rec => l_ic_item_mst_rec
1784 , x_return_status => x_return_status
1785 , x_msg_count => x_msg_count
1786 , x_msg_data => x_msg_data);
1787
1788
1789 GMI_Reservation_Util.printLn('After Get_OPM_Item : item_no='||l_ic_item_mst_rec.item_no);
1790
1791 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1792 THEN
1793 GMI_Reservation_Util.printLn('(opm_dbg) in end of Get_DefaultLot_from_ItemCtl ERROR:Returned by Get_OPM_item_from_Apps.');
1794 FND_MESSAGE.Set_Name('GMI','GMI_OPM_ITEM');
1795 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
1796 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
1797 FND_MSG_PUB.Add;
1798 RAISE FND_API.G_EXC_ERROR;
1799 END IF;
1800
1801
1802 /* ============================================================================================= */
1803 /* If the Item is NOT lot_ctl and location_ctl, then the default lot qty has to be taken */
1804 /* ===> the allocated qty includes the default lot qty. */
1805 /* so I change the value of x_default_lot_index to the opposite ! */
1806 /* in order to correctly use the following loop. */
1807 /*============================================================================================ */
1808
1809
1810 -- added by fabdi 10/01/2001
1811 -- fix for bug # 1574957
1812 OPEN get_whse_ctl(p_organization_id);
1813 FETCH get_whse_ctl INTO l_whse_ctl;
1814 -- end fabdi
1815
1816 GMI_Reservation_Util.printLn('(opm_dbg) l_whse_ctl='||l_whse_ctl);
1817 /* the correct condition should be lot_ctl and( loct_ctl or whse loct_ctl)*/
1818 IF (l_ic_item_mst_rec.lot_ctl = 0) AND (l_ic_item_mst_rec.loct_ctl = 0 OR l_whse_ctl = 0)
1819 THEN
1820 x_default_lot_index := (-1) * x_default_lot_index;
1821 END IF;
1822
1823 CLOSE get_whse_ctl;
1824
1825 GMI_Reservation_Util.printLn('(opm_dbg) x_default_lot_index='||x_default_lot_index);
1826
1827 EXCEPTION
1828 WHEN FND_API.G_EXC_ERROR THEN
1829 GMI_Reservation_Util.printLn('End of Get_DefaultLot_from_ItemCtl. Error');
1830 x_return_status := FND_API.G_RET_STS_ERROR;
1831
1832 /* Get message count and data */
1833 FND_MSG_PUB.count_and_get
1834 ( p_count => x_msg_count
1835 , p_data => x_msg_data
1836 );
1837
1838 WHEN OTHERS THEN
1839 GMI_Reservation_Util.printLn('End of Get_DefaultLot_from_ItemCtl. ErrorOthers');
1840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1841
1842 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1843 , l_api_name
1844 );
1845
1846 /* Get message count and data */
1847 FND_MSG_PUB.count_and_get
1848 ( p_count => x_msg_count
1849 , p_data => x_msg_data
1850 );
1851
1852 END Get_DefaultLot_from_ItemCtl;
1853
1854
1855
1856
1857 PROCEDURE PrintLn
1858 ( p_msg IN VARCHAR2
1859 , p_file_name IN VARCHAR2 DEFAULT '0'
1860 ) IS
1861
1862 CURSOR get_log_file_location IS
1863 SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value)
1864 FROM v$parameter
1865 WHERE name = 'utl_file_dir';
1866
1867 l_api_name CONSTANT VARCHAR2 (30) := 'PrintLn';
1868 l_location VARCHAR2(255);
1869 l_log UTL_FILE.file_type;
1870 l_debug_level VARCHAR2(240) := fnd_profile.value ('ONT_DEBUG_LEVEL');
1871 l_time VARCHAR2(10);
1872 l_file_name VARCHAR2(80);
1873
1874 BEGIN
1875
1876 -- Bug 14722708 - Commenting out this procedure as GMI is obsolete in R12.
1877 -- This procedure should not be used for debugging R12 code. Instead please
1878 -- use appropriate product (INV/OM/WSH) specific debugging mechanism.
1879
1880 null;
1881
1882
1883 -- WSH_Util_Core.PrintLn(p_msg);
1884 -- oe_debug_pub.add(p_msg, 1);
1885
1886 /* Hardcode the log file name */
1887 /*IF (p_file_name = '0') THEN
1888 l_file_name := 'OPMLOG';
1889 ELSE l_file_name := p_file_name;
1890 END IF;*/
1891 /* always write to OPMLOG */
1892 /* l_file_name := 'OPMLOG';
1893
1894 l_debug_level := NVL(TO_NUMBER(l_debug_level),0);
1895
1896 IF (l_debug_level = 5)
1897 THEN
1898 l_file_name := l_file_name||FND_GLOBAL.user_id;
1899
1900 OPEN get_log_file_location;
1901 FETCH get_log_file_location into l_location;
1902 CLOSE get_log_file_location;
1903
1904 l_log := UTL_FILE.fopen(l_location, l_file_name, 'a');
1905 IF UTL_FILE.IS_OPEN(l_log) THEN
1906 UTL_FILE.put_line(l_log, p_msg);
1907 UTL_FILE.fflush(l_log);
1908 UTL_FILE.fclose(l_log);
1909 END IF;
1910
1911 IF (p_file_name <> '0') THEN
1912 l_file_name := p_file_name||FND_GLOBAL.user_id;
1913
1914 OPEN get_log_file_location;
1915 FETCH get_log_file_location into l_location;
1916 CLOSE get_log_file_location;
1917
1918 l_log := UTL_FILE.fopen(l_location, l_file_name, 'a');
1919 IF UTL_FILE.IS_OPEN(l_log) THEN
1920 UTL_FILE.put_line(l_log, p_msg);
1921 UTL_FILE.fflush(l_log);
1922 UTL_FILE.fclose(l_log);
1923 END IF;
1924 END IF;
1925
1926 END IF;
1927
1928
1929 EXCEPTION
1930
1931 WHEN OTHERS THEN
1932 NULL;
1933 */
1934
1935 END PrintLn;
1936
1937 PROCEDURE Validation_ictran_rec
1938 ( p_ic_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
1939 , x_ic_tran_rec OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
1940 , x_return_status OUT NOCOPY VARCHAR2
1941 , x_msg_count OUT NOCOPY NUMBER
1942 , x_msg_data OUT NOCOPY VARCHAR2
1943 ) Is
1944
1945 l_api_name CONSTANT VARCHAR2 (30) := 'Validation_ictran_rec';
1946
1947 CURSOR Get_Orgn_Co_from_Whse (whse IN VARCHAR2)
1948 IS
1949 SELECT sy.co_code,
1950 sy.orgn_code
1951 FROM sy_orgn_mst sy,
1952 ic_whse_mst wh
1953 WHERE sy.orgn_code = wh.orgn_code
1954 AND wh.whse_code = whse;
1955
1956 BEGIN
1957
1958 /* ======================================================================= */
1959 /* Init variables */
1960 /* ======================================================================= */
1961 x_return_status := FND_API.G_RET_STS_SUCCESS;
1962
1963 x_ic_tran_rec := p_ic_tran_rec;
1964
1965 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_id='||p_ic_tran_rec.trans_id, 'pick_lots.log');
1966 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: line_id='||p_ic_tran_rec.line_id, 'pick_lots.log');
1967 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: item_id='||p_ic_tran_rec.item_id, 'pick_lots.log');
1968 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: co_code='||p_ic_tran_rec.co_code, 'pick_lots.log');
1969 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: orgn_code='||p_ic_tran_rec.orgn_code, 'pick_lots.log');
1970 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: whse_code='||p_ic_tran_rec.whse_code, 'pick_lots.log');
1971 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: lot_id='||p_ic_tran_rec.lot_id, 'pick_lots.log');
1972 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: location='||p_ic_tran_rec.location, 'pick_lots.log');
1973 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: doc_id='||p_ic_tran_rec.doc_id, 'pick_lots.log');
1974 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: doc_type='||p_ic_tran_rec.doc_type, 'pick_lots.log');
1975 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: doc_line='||p_ic_tran_rec.doc_line, 'pick_lots.log');
1976 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: line_type='||p_ic_tran_rec.line_type, 'pick_lots.log');
1977 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: reason_code='||p_ic_tran_rec.reason_code, 'pick_lots.log');
1978 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_date='||p_ic_tran_rec.trans_date, 'pick_lots.log');
1979 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_qty='||p_ic_tran_rec.trans_qty, 'pick_lots.log');
1980 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_qty2='||p_ic_tran_rec.trans_qty2, 'pick_lots.log');
1981 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: qc_grade='||p_ic_tran_rec.qc_grade, 'pick_lots.log');
1982 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: lot_no='||p_ic_tran_rec.lot_no, 'pick_lots.log');
1983 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: sublot_no='||p_ic_tran_rec.sublot_no, 'pick_lots.log');
1984 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: lot_status='||p_ic_tran_rec.lot_status, 'pick_lots.log');
1985 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_um='||p_ic_tran_rec.trans_um, 'pick_lots.log');
1986 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_um2='||p_ic_tran_rec.trans_um2, 'pick_lots.log');
1987 /* GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: staged_ind='||p_ic_tran_rec.staged_ind, 'pick_lots.log'); */
1988 /* GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: text_code='||p_ic_tran_rec.text_code, 'pick_lots.log'); */
1989
1990 IF p_ic_tran_rec.text_code = 0 THEN
1991 x_ic_tran_rec.text_code := NULL;
1992 END IF;
1993 x_ic_tran_rec.staged_ind := 0;
1994
1995 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check trans_id ', 'pick_lots.log');
1996 IF ( p_ic_tran_rec.trans_id = 0
1997 OR p_ic_tran_rec.trans_id IS NULL
1998 OR p_ic_tran_rec.trans_id = FND_API.G_MISS_NUM)
1999 THEN
2000 x_ic_tran_rec.trans_id := 0;
2001 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Set trans_id=0 ', 'pick_lots.log');
2002 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2003 FND_MESSAGE.Set_Token('MISSING', 'trans_id');
2004 FND_MSG_PUB.Add;
2005 /* raise FND_API.G_EXC_ERROR; */
2006 END IF;
2007
2008 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check item_id ', 'pick_lots.log');
2009 IF ( p_ic_tran_rec.item_id = 0
2010 OR p_ic_tran_rec.item_id IS NULL
2011 OR p_ic_tran_rec.item_id = FND_API.G_MISS_NUM)
2012 THEN
2013 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2014 FND_MESSAGE.Set_Token('MISSING', 'item_id');
2015 FND_MSG_PUB.Add;
2016 /* raise FND_API.G_EXC_ERROR; */
2017 END IF;
2018
2019 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check line_id ', 'pick_lots.log');
2020 IF ( p_ic_tran_rec.line_id = 0
2021 OR p_ic_tran_rec.line_id IS NULL
2022 OR p_ic_tran_rec.line_id = FND_API.G_MISS_NUM)
2023 THEN
2024 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2025 FND_MESSAGE.Set_Token('MISSING', 'line_id');
2026 FND_MSG_PUB.Add;
2027 /* raise FND_API.G_EXC_ERROR; */
2028 END IF;
2029
2030 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check whse_code ', 'pick_lots.log');
2031 IF ( p_ic_tran_rec.whse_code = ''
2032 OR p_ic_tran_rec.whse_code IS NULL
2033 OR p_ic_tran_rec.whse_code = FND_API.G_MISS_CHAR)
2034 THEN
2035 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2036 FND_MESSAGE.Set_Token('MISSING', 'whse_code');
2037 FND_MSG_PUB.Add;
2038 raise FND_API.G_EXC_ERROR;
2039 END IF;
2040
2041 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check co_code/orgn_code ', 'pick_lots.log');
2042
2043 IF ( p_ic_tran_rec.co_code = ''
2044 OR p_ic_tran_rec.co_code IS NULL
2045 OR p_ic_tran_rec.co_code = FND_API.G_MISS_CHAR)
2046 OR ( p_ic_tran_rec.orgn_code = ''
2047 OR p_ic_tran_rec.orgn_code IS NULL
2048 OR p_ic_tran_rec.orgn_code = FND_API.G_MISS_CHAR)
2049 THEN
2050 OPEN Get_Orgn_Co_from_Whse(p_ic_tran_rec.whse_code);
2051 FETCH Get_Orgn_Co_from_Whse
2052 INTO x_ic_tran_rec.co_code,
2053 x_ic_tran_rec.orgn_code;
2054
2055 IF (Get_Orgn_Co_from_Whse%NOTFOUND)
2056 THEN
2057 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2058 FND_MESSAGE.Set_Token('MISSING', 'NOTFOUND co_code/orgn_code');
2059 FND_MSG_PUB.Add;
2060 CLOSE Get_Orgn_Co_from_Whse;
2061 raise FND_API.G_EXC_ERROR;
2062 END IF;
2063 CLOSE Get_Orgn_Co_from_Whse;
2064
2065 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2066 FND_MESSAGE.Set_Token('MISSING', 'co_code/orgn_code');
2067 FND_MSG_PUB.Add;
2068 /* raise FND_API.G_EXC_ERROR; */
2069 END IF;
2070
2071
2072 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check lot_id ', 'pick_lots.log');
2073 IF ( p_ic_tran_rec.lot_id = 0
2074 OR p_ic_tran_rec.lot_id IS NULL
2075 OR p_ic_tran_rec.lot_id = FND_API.G_MISS_NUM)
2076 THEN
2077 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2078 FND_MESSAGE.Set_Token('MISSING', 'lot_id');
2079 FND_MSG_PUB.Add;
2080 /* raise FND_API.G_EXC_ERROR; */
2081 END IF;
2082
2083 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check location ', 'pick_lots.log');
2084 IF ( p_ic_tran_rec.location = ''
2085 OR p_ic_tran_rec.location IS NULL
2086 OR p_ic_tran_rec.location = FND_API.G_MISS_CHAR)
2087 THEN
2088 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2089 FND_MESSAGE.Set_Token('MISSING', 'location');
2090 FND_MSG_PUB.Add;
2091 /* raise FND_API.G_EXC_ERROR; */
2092 END IF;
2093
2094 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check doc_id ', 'pick_lots.log');
2095 IF ( p_ic_tran_rec.doc_id = 0
2096 OR p_ic_tran_rec.doc_id IS NULL
2097 OR p_ic_tran_rec.doc_id = FND_API.G_MISS_NUM)
2098 THEN
2099 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2100 FND_MESSAGE.Set_Token('MISSING', 'doc_id');
2101 FND_MSG_PUB.Add;
2102 /* raise FND_API.G_EXC_ERROR; */
2103 END IF;
2104
2105 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check doc_type ', 'pick_lots.log');
2106 IF ( p_ic_tran_rec.doc_type = ''
2107 OR p_ic_tran_rec.doc_type IS NULL
2108 OR p_ic_tran_rec.doc_type = FND_API.G_MISS_CHAR)
2109 THEN
2110 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2111 FND_MESSAGE.Set_Token('MISSING', 'doc_type');
2112 FND_MSG_PUB.Add;
2113 /* raise FND_API.G_EXC_ERROR; */
2114 END IF;
2115
2116 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check doc_line ', 'pick_lots.log');
2117 IF ( p_ic_tran_rec.doc_line = 0
2118 OR p_ic_tran_rec.doc_line IS NULL
2119 OR p_ic_tran_rec.doc_line = FND_API.G_MISS_NUM)
2120 THEN
2121 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2122 FND_MESSAGE.Set_Token('MISSING', 'doc_line');
2123 FND_MSG_PUB.Add;
2124 /* raise FND_API.G_EXC_ERROR; */
2125 END IF;
2126
2127 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check line_type ', 'pick_lots.log');
2128 IF ( p_ic_tran_rec.line_type = 0
2129 OR p_ic_tran_rec.line_type IS NULL
2130 OR p_ic_tran_rec.line_type = FND_API.G_MISS_NUM)
2131 THEN
2132 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2133 FND_MESSAGE.Set_Token('MISSING', 'line_type');
2134 FND_MSG_PUB.Add;
2135 /* raise FND_API.G_EXC_ERROR; */
2136 END IF;
2137
2138 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check trans_qty ', 'pick_lots.log');
2139 IF ( p_ic_tran_rec.trans_qty = 0
2140 OR p_ic_tran_rec.trans_qty IS NULL
2141 OR p_ic_tran_rec.trans_qty = FND_API.G_MISS_NUM)
2142 THEN
2143 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2144 FND_MESSAGE.Set_Token('MISSING', 'trans_qty');
2145 FND_MSG_PUB.Add;
2146 /* raise FND_API.G_EXC_ERROR; */
2147 END IF;
2148
2149 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check trans_qty2 ', 'pick_lots.log');
2150 IF ( p_ic_tran_rec.trans_qty2 = 0
2151 OR p_ic_tran_rec.trans_qty2 IS NULL
2152 OR p_ic_tran_rec.trans_qty2 = FND_API.G_MISS_NUM)
2153 THEN
2154 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2155 FND_MESSAGE.Set_Token('MISSING', 'trans_qty2');
2156 FND_MSG_PUB.Add;
2157 /* raise FND_API.G_EXC_ERROR; */
2158 END IF;
2159
2160 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check trans_um ', 'pick_lots.log');
2161 IF ( p_ic_tran_rec.trans_um = ''
2162 OR p_ic_tran_rec.trans_um IS NULL
2163 OR p_ic_tran_rec.trans_um = FND_API.G_MISS_CHAR)
2164 THEN
2165 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2166 FND_MESSAGE.Set_Token('MISSING', 'trans_um');
2167 FND_MSG_PUB.Add;
2168 /* raise FND_API.G_EXC_ERROR; */
2169 END IF;
2170
2171 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check trans_um2 ', 'pick_lots.log');
2172 IF ( p_ic_tran_rec.trans_um2 = ''
2173 OR p_ic_tran_rec.trans_um2 IS NULL
2174 OR p_ic_tran_rec.trans_um2 = FND_API.G_MISS_CHAR)
2175 THEN
2176 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2177 FND_MESSAGE.Set_Token('MISSING', 'trans_um2');
2178 FND_MSG_PUB.Add;
2179 /* raise FND_API.G_EXC_ERROR; */
2180 END IF;
2181
2182 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: Check trans_date ', 'pick_lots.log');
2183 IF ( p_ic_tran_rec.trans_date IS NULL
2184 OR p_ic_tran_rec.trans_date = FND_API.G_MISS_DATE)
2185 THEN
2186 /* x_ic_tran_rec.trans_date := sysdate; */
2187 FND_MESSAGE.Set_Name('GMI','GMI_MISSING');
2188 FND_MESSAGE.Set_Token('MISSING', 'trans_date');
2189 FND_MSG_PUB.Add;
2190 /* raise FND_API.G_EXC_ERROR; */
2191 END IF;
2192
2193
2194
2195 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_id='||x_ic_tran_rec.trans_id, 'pick_lots.log');
2196 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: line_id='||x_ic_tran_rec.line_id, 'pick_lots.log');
2197 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: item_id='||x_ic_tran_rec.item_id, 'pick_lots.log');
2198 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: co_code='||x_ic_tran_rec.co_code, 'pick_lots.log');
2199 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: orgn_code='||x_ic_tran_rec.orgn_code, 'pick_lots.log');
2200 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: whse_code='||x_ic_tran_rec.whse_code, 'pick_lots.log');
2201 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: lot_id='||x_ic_tran_rec.lot_id, 'pick_lots.log');
2202 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: location='||x_ic_tran_rec.location, 'pick_lots.log');
2203 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: doc_id='||x_ic_tran_rec.doc_id, 'pick_lots.log');
2204 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: doc_type='||x_ic_tran_rec.doc_type, 'pick_lots.log');
2205 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: doc_line='||x_ic_tran_rec.doc_line, 'pick_lots.log');
2206 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: line_type='||x_ic_tran_rec.line_type, 'pick_lots.log');
2207 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: reason_code='||x_ic_tran_rec.reason_code, 'pick_lots.log');
2208 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_date='||x_ic_tran_rec.trans_date, 'pick_lots.log');
2209 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_qty='||x_ic_tran_rec.trans_qty, 'pick_lots.log');
2210 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_qty2='||x_ic_tran_rec.trans_qty2, 'pick_lots.log');
2211 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: qc_grade='||x_ic_tran_rec.qc_grade, 'pick_lots.log');
2212 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: lot_no='||x_ic_tran_rec.lot_no, 'pick_lots.log');
2213 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: sublot_no='||x_ic_tran_rec.sublot_no, 'pick_lots.log');
2214 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: lot_status='||x_ic_tran_rec.lot_status, 'pick_lots.log');
2215 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_um='||x_ic_tran_rec.trans_um, 'pick_lots.log');
2216 GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: trans_um2='||x_ic_tran_rec.trans_um2, 'pick_lots.log');
2217 /* GMI_reservation_Util.PrintLn('(opm_dbg) in Util.Validation_ictran_rec: staged_ind='||x_ic_tran_rec.staged_ind, 'pick_lots.log'); */
2218
2219 EXCEPTION
2220 WHEN FND_API.G_EXC_ERROR THEN
2221 GMI_Reservation_Util.printLn('End of Validation_ic_tran_rec. Error', 'pick_lots.log');
2222 x_return_status := FND_API.G_RET_STS_ERROR;
2223
2224 /* Get message count and data */
2225 FND_MSG_PUB.count_and_get
2226 ( p_count => x_msg_count
2227 , p_data => x_msg_data
2228 );
2229
2230 WHEN OTHERS THEN
2231 GMI_Reservation_Util.printLn('End of Validation_ic_tran_rec. ErrorOthers', 'pick_lots.log');
2232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2233
2234 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
2235 , l_api_name
2236 );
2237
2238 /* Get message count and data */
2239 FND_MSG_PUB.count_and_get
2240 ( p_count => x_msg_count
2241 , p_data => x_msg_data
2242 );
2243
2244 END Validation_ictran_rec;
2245
2246
2247 /* ======================================================================== */
2248 /* Notes : */
2249 /* The passed qties are positive */
2250 /* if trans_id > 0, then : */
2251 /* If new_qty = 0, then delete reservation */
2252 /* If new_qty > 0, then update transaction */
2253 /* if trans_id = 0, then : */
2254 /* Create a new transaction. */
2255 /* */
2256 /* line_id is mandatory in any case. */
2257 /* when trans_id=0, then whse_code is mandatory. */
2258 /* */
2259 /* co_code and orgn_code are null when they are passed by ACCEPT(Pick_Lot) */
2260 /* doc_line and doc_id are null when they are passed by ACCEPT(Pick_Lot) */
2261 /* */
2262 /* Note that each UOM will be in passed in p_ic_tran_rec as AppsUOM (3char). */
2263 /* Need to be converted back to OPMUOM. */
2264 /* */
2265 /* Note2 : If default lot, then the lot_Status has to be null. */
2266 /* */
2267 /* The item_id is the OPM one. */
2268 /* ======================================================================== */
2269 PROCEDURE Set_Pick_Lots
2270 ( p_ic_tran_rec IN OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
2271 , p_mo_line_id IN NUMBER
2272 , p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE
2273 , x_return_status OUT NOCOPY VARCHAR2
2274 , x_msg_count OUT NOCOPY NUMBER
2275 , x_msg_data OUT NOCOPY VARCHAR2
2276 ) IS
2277
2278
2279 l_api_name CONSTANT VARCHAR2(30) := 'Set_Pick_Lots';
2280 l_api_version CONSTANT VARCHAR2(10) := '1.0';
2281
2282 l_whse_code VARCHAR2(4);
2283 l_commit VARCHAR2(5) := FND_API.G_FALSE;
2284 l_validation_level VARCHAR2(4) := FND_API.G_VALID_LEVEL_FULL;
2285 l_validation_flag VARCHAR2(10) := FND_API.G_TRUE;
2286 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
2287 l_serial_number inv_reservation_global.serial_number_tbl_type;
2288 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
2289 l_mtl_reservation_tbl_count NUMBER;
2290 l_error_code NUMBER;
2291 l_default_lot_index NUMBER;
2292 l_original_tran_index NUMBER;
2293 l_default_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
2294 l_original_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
2295 l_ic_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
2296 ll_ic_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
2297 l_temp_tran_row ic_tran_pnd%ROWTYPE;
2298
2299 l_delta_qty1 NUMBER(19,9);
2300 l_delta_qty2 NUMBER(19,9);
2301 -- HW nocopy
2302 l_uom1 VARCHAR2(4);
2303 l_uom2 VARCHAR2(4);
2304 /* For creating the default_lot */
2305 l_organization_id NUMBER;
2306 l_site_use_id NUMBER;
2307 l_demand_source_header_id NUMBER;
2308 l_demand_source_line_id NUMBER;
2309 l_trans_date DATE;
2310 l_doc_line NUMBER;
2311 l_need_update_default_lot BOOLEAN;
2312
2313 /* For Change lot in an existing allocated transaction : */
2314 l_change_lot BOOLEAN;
2315 l_deleted_qty1 NUMBER(19,9);
2316 l_deleted_qty2 NUMBER(19,9);
2317
2318 /* For the Allocation rules. */
2319 l_op_alot_prm_rec op_alot_prm%ROWTYPE;
2320 l_inventory_item_id NUMBER;
2321 l_ic_item_mst_rec GMI_Reservation_Util.ic_item_mst_rec;
2322 l_cust_no op_cust_mst.cust_no%TYPE;
2323 l_co_code op_cust_mst.co_code%TYPE; --B1731567 co_code of cust
2324
2325 /* added by fabdi 20/08/2001 Bug 2023369 */
2326 locked_by_other_user EXCEPTION;
2327 PRAGMA EXCEPTION_INIT (locked_by_other_user,-54);
2328 l_ic_txn_request_lines ic_txn_request_lines%ROWTYPE;
2329 l_NEW_ALLOCATED_QTY NUMBER;
2330 l_NEW_ALLOCATED_QTY2 NUMBER;
2331 l_whse_ctl NUMBER;
2332 l_lock_status BOOLEAN;
2333
2334 Cursor get_whse_ctl (org_id IN NUMBER)
2335 IS
2336 select loct_ctl
2337 from ic_whse_mst
2338 where mtl_organization_id = org_id;
2339
2340 /* end fabdi */
2341
2342 /* ===CURSOR=============================================================== */
2343 /* B1731567 - Retrieve co_code relating to cust_no
2344 ==============================================*/
2345 CURSOR c_sales_order_info(so_line_id NUMBER) IS
2346 SELECT sol.ship_from_org_id
2347 , mtl.sales_order_id
2348 , sol.line_id
2349 , sol.schedule_ship_date
2350 , sol.line_number + (sol.shipment_number / 10)
2351 , sol.inventory_item_id
2352 , sol.ship_to_org_id
2353 FROM oe_order_lines_all sol
2354 , oe_order_headers_all soh
2355 , oe_transaction_types_tl tt
2356 , mtl_sales_orders mtl
2357 WHERE mtl.segment1 = to_char(soh.order_number)
2358 AND mtl.segment2 = tt.name
2359 AND mtl.segment3 = fnd_profile.value('ONT_SOURCE_CODE')
2360 -- AND tt.language = userenv('LANG') -- OPM bug 3770264
2361 AND tt.language = (select language_code -- OPM bug 3770264
2362 from fnd_languages
2363 where installed_flag = 'B')
2364 AND tt.transaction_type_id = soh.order_type_id
2365 AND soh.header_id = sol.header_id
2366 AND sol.line_id = so_line_id ;
2367
2368 Cursor get_cust_no IS
2369 Select opc.cust_no
2370 From op_cust_mst opc
2371 , sy_orgn_mst som
2372 , ic_whse_mst whse
2373 Where whse.mtl_organization_id = l_organization_id
2374 and whse.orgn_code = som.orgn_code
2375 and som.co_code =opc.co_code
2376 and opc.of_ship_to_site_use_id(+) = l_site_use_id ;
2377
2378 l_pick_slip_number NUMBER;
2379
2380 BEGIN
2381
2382 /* ======================================================================= */
2383 /* Init variables */
2384 /* ======================================================================= */
2385 x_return_status := FND_API.G_RET_STS_SUCCESS;
2386 SAVEPOINT Accept_Pick_Lots;
2387
2388 l_need_update_default_lot := TRUE;
2389 l_change_lot := FALSE;
2390
2391 ll_ic_tran_rec := p_ic_tran_rec;
2392
2393 GMI_reservation_Util.PrintLn('Entering Set_Pick_Lots, trans_date='||to_char(p_ic_tran_rec.trans_date, 'DD/MM/YYYY HH24:MI:SS'));
2394 GMI_reservation_Util.PrintLn(' reason_code='||p_ic_tran_rec.reason_code);
2395
2396 /* do this first to resolve a performance issue 1846396*/
2397 /* do nothing if no real allocations */
2398 IF (nvl(ll_ic_tran_rec.trans_id,0) = 0 AND nvl(ll_ic_tran_rec.trans_qty,0) = 0) THEN
2399 RETURN;
2400 END IF;
2401
2402 IF (NVL(ll_ic_tran_rec.line_id, 0) <= 0)
2403 THEN
2404 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Error Because trans_line_id (='||ll_ic_tran_rec.line_id||') is not >0');
2405 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2406 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Validation_ictran_rec');
2407 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2408 FND_MSG_PUB.ADD;
2409 RAISE FND_API.G_EXC_ERROR;
2410 ELSE
2411 /* ================================================================ */
2412 /* Init variables before calling QueryReservation: */
2413 /* ================================================================ */
2414 OPEN c_sales_order_info(ll_ic_tran_rec.line_id);
2415 /* B1731567 - retrieve co_code relating to cust_no */
2416 FETCH c_sales_order_info
2417 INTO l_organization_id
2418 , l_demand_source_header_id
2419 , l_demand_source_line_id
2420 , l_trans_date
2421 , l_doc_line
2422 , l_inventory_item_id
2423 , l_site_use_id ;
2424
2425 IF (c_sales_order_info%NOTFOUND)
2426 THEN
2427 CLOSE c_sales_order_info;
2428 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots : sales_order(line_id='||ll_ic_tran_rec.line_id||')=NOTFOUND', 'pick_lots.log');
2429 FND_MESSAGE.Set_Name('GMI','GMI_QRY_SO_FAILED');
2430 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2431 FND_MESSAGE.Set_Token('SO_LINE_ID', ll_ic_tran_rec.line_id);
2432 FND_MSG_PUB.Add;
2433 RAISE FND_API.G_EXC_ERROR;
2434 ELSE
2435 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots FOUND: line_id='||ll_ic_tran_rec.line_id||'.', 'pick_lots.log');
2436 END IF;
2437 CLOSE c_sales_order_info;
2438
2439 ll_ic_tran_rec.doc_id := l_demand_source_header_id;
2440
2441 /* The date is passed by the ICPCKLOT.fmb, so I don't overwrite it. */
2442 /* B1471071, odaboval 15-Nov-2000 : Set the transaction date to the schedule ship date when it is null. */
2443 IF (ll_ic_tran_rec.trans_date IS NULL)
2444 THEN
2445 ll_ic_tran_rec.trans_date := l_trans_date;
2446 END IF;
2447 ll_ic_tran_rec.doc_line := l_doc_line;
2448
2449 /* ============================================================================================= */
2450 /* Get whse, and organization code from Process. */
2451 /* ============================================================================================= */
2452 INV_GMI_RSV_Branch.Get_Process_Org(
2453 p_organization_id => l_organization_id,
2454 x_opm_whse_code => l_whse_code,
2455 x_opm_co_code => ll_ic_tran_rec.co_code,
2456 x_opm_orgn_code => ll_ic_tran_rec.orgn_code,
2457 x_return_status => x_return_status );
2458
2459 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
2460 OR (l_whse_code <> ll_ic_tran_rec.whse_code)
2461 THEN
2462 GMI_reservation_Util.PrintLn('(opm_dbg) in end of GMI_Reservation_Util.Set_Pick_Lots ERROR:Returned by Get_Process_Org.');
2463 FND_MESSAGE.Set_Name('GMI','GMI_GET_PROCESS_ORG');
2464 FND_MESSAGE.Set_Token('ORGANIZATION_ID', l_organization_id);
2465 FND_MSG_PUB.Add;
2466 RAISE FND_API.G_EXC_ERROR;
2467 END IF;
2468
2469 l_co_code := ll_ic_tran_rec.co_code;
2470 /* ============================================================================================= */
2471 /* Get Item details*/
2472 /* =============================================================================================*/
2473 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Entering Validation_For_Query. item_id='||l_inventory_item_id);
2474 Get_OPM_item_from_Apps(
2475 p_organization_id => l_organization_id
2476 , p_inventory_item_id => l_inventory_item_id
2477 , x_ic_item_mst_rec => l_ic_item_mst_rec
2478 , x_return_status => x_return_status
2479 , x_msg_count => x_msg_count
2480 , x_msg_data => x_msg_data);
2481
2482 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2483 FND_MESSAGE.Set_Name('GMI','GMI_OPM_ITEM');
2484 FND_MESSAGE.Set_Token('ORGANIZATION_ID', l_organization_id);
2485 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', l_inventory_item_id);
2486 FND_MSG_PUB.Add;
2487 raise FND_API.G_EXC_ERROR;
2488 END IF;
2489
2490 /* B1390816, odaboval 16-Nov-2000 : Added a check on the Horizon period. */
2491 /* ======================================================================= */
2492 /* Check : Allocation Rules Validity */
2493 /* ======================================================================= */
2494
2495 OPEN get_cust_no;
2496 FETCH get_cust_no INTO l_cust_no;
2497 /* B1731567 - retrieve co_code relating to cust_no */
2498 IF get_cust_no%NOTFOUND THEN
2499 GMI_reservation_Util.PrintLn('cust_no not found whse co_code/site_use_id ');
2500 CLOSE get_cust_no;
2501 ELSE
2502 CLOSE get_cust_no; -- Bug 3598280
2503 GMI_ALLOCATION_RULES_PVT.GET_ALLOCATION_PARMS
2504 ( p_co_code => l_co_code, --B1731567
2505 p_cust_no => l_cust_no,
2506 p_alloc_class => l_ic_item_mst_rec.alloc_class,
2507 x_op_alot_prm => l_op_alot_prm_rec,
2508 x_return_status => x_return_status,
2509 x_msg_count => x_msg_count,
2510 x_msg_data => x_msg_data
2511 );
2512
2513 /* if no allocation rules found, then Raise Exception and return */
2514 /* ------------------------------------------------------------------------ */
2515 IF ((l_op_alot_prm_rec.alloc_class = ' ') OR
2516 (l_op_alot_prm_rec.delete_mark = 1))
2517 THEN
2518 GMI_Reservation_Util.PrintLn('(opm_dbg) allocation - Error missing allocation parms',1);
2519 FND_MESSAGE.SET_NAME('GML','GML_NO_ALLOCATION_PARMS');
2520 FND_MESSAGE.SET_TOKEN('ALLOC_CLASS', l_ic_item_mst_rec.alloc_class);
2521 FND_MSG_PUB.Add;
2522 RAISE FND_API.G_EXC_ERROR;
2523 END IF;
2524
2525 /* It is not possible to allocate (create/update) if the period between */
2526 /* trans_date and sysdate is greater than the Horizon period. */
2527 /* --------------------------------------------------------------------- */
2528 IF (l_op_alot_prm_rec.alloc_horizon > 0) AND
2529 (p_ic_tran_rec.trans_date > (SYSDATE + l_op_alot_prm_rec.alloc_horizon))
2530 THEN
2531 GMI_Reservation_Util.PrintLn('(opm_dbg) allocation horizon is out - using '|| l_op_alot_prm_rec.alloc_horizon||' days.');
2532 GMI_Reservation_Util.PrintLn('(opm_dbg) do allocate from sysdate > ' || (p_ic_tran_rec.trans_date + l_op_alot_prm_rec.alloc_horizon));
2533 GMI_Reservation_Util.PrintLn('(opm_dbg) or choose trans_date < '|| (SYSDATE + l_op_alot_prm_rec.alloc_horizon));
2534 FND_MESSAGE.SET_NAME('GML','SO_E_ALLOC_HORIZON_ERR');
2535 FND_MSG_PUB.Add;
2536 RAISE FND_API.G_EXC_ERROR;
2537 END IF;
2538
2539 /* ==================================================================== */
2540 /* End of check : trans_date validity */
2541 /* ===================================================================== */
2542 END IF;
2543
2544 END IF;
2545
2546 /* ============================================================================================== */
2547 /* Convert the Apps UOM into OPM UOM.(because ICPCKLOT.fmb always gives a rec_type with Apps UOM (char3)) */
2548 /* ============================================================================================== */
2549
2550 IF (ll_ic_tran_rec.trans_um is not NULL)
2551 THEN
2552 -- HW nocopy replaced ll_ic_tran_rec.trans_um with l_uom1
2553 Get_OPMUOM_from_AppsUOM(
2554 p_Apps_UOM => ll_ic_tran_rec.trans_um
2555 , x_OPM_UOM => l_uom1
2556 , x_return_status => x_return_status
2557 , x_msg_count => x_msg_count
2558 , x_msg_data => x_msg_data);
2559
2560 ll_ic_tran_rec.trans_um :=l_uom1;
2561 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: trans_um='||ll_ic_tran_rec.trans_um||'.', 'pick_lots.log');
2562 END IF;
2563
2564 IF (ll_ic_tran_rec.trans_um2 is not NULL)
2565 THEN
2566 Get_OPMUOM_from_AppsUOM(
2567 p_Apps_UOM => ll_ic_tran_rec.trans_um2
2568 , x_OPM_UOM => l_uom2
2569 , x_return_status => x_return_status
2570 , x_msg_count => x_msg_count
2571 , x_msg_data => x_msg_data);
2572 ll_ic_tran_rec.trans_um2:=l_uom2;
2573 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: trans_um2='||ll_ic_tran_rec.trans_um2||'.', 'pick_lots.log');
2574 END IF;
2575
2576
2577 /* ============================================================================================== */
2578 /* So I need to get the Reservations, and find out where is the default lot. */
2579 /* ============================================================================================== */
2580 IF (ll_ic_tran_rec.line_id > 0)
2581 THEN
2582 l_rsv_rec.organization_id := l_organization_id;
2583 l_rsv_rec.demand_source_header_id := l_demand_source_header_id;
2584 l_rsv_rec.demand_source_line_id := l_demand_source_line_id;
2585
2586 l_validation_flag := FND_API.G_FALSE;
2587
2588 GMI_reservation_pub.Query_reservation
2589 ( p_api_version_number => 1.0
2590 , p_init_msg_lst => FND_API.G_FALSE
2591 , x_return_status => x_return_status
2592 , x_msg_count => x_msg_count
2593 , x_msg_data => x_msg_data
2594 , p_validation_flag => l_validation_flag
2595 , p_query_input => l_rsv_rec
2596 , p_cancel_order_mode => INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_YES
2597 , x_mtl_reservation_tbl => l_mtl_reservation_tbl
2598 , x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count
2599 , x_error_code => l_error_code
2600 , p_lock_records => FND_API.G_FALSE
2601 , p_sort_by_req_date => INV_RESERVATION_GLOBAL.G_QUERY_NO_SORT
2602 );
2603
2604 /* ======================================================================================= */
2605 /* There may not be any rows, and it should be possible ! (if not auto-allocate before) */
2606 /* Case where Nothing is Reserved. */
2607 /* ======================================================================================= */
2608 IF (GMI_Reservation_Util.ic_tran_rec_tbl.COUNT = 0)
2609 THEN
2610 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: No Error but No Rows Found in reservation', 'pick_lots.log');
2611 /* Create the default lot */
2612 GMI_Reservation_Util.Create_Empty_Default_Lot
2613 ( p_ic_tran_rec => ll_ic_tran_rec
2614 , p_organization_id => l_organization_id
2615 , x_default_lot_index => l_default_lot_index
2616 , x_return_status => x_return_status
2617 , x_msg_count => x_msg_count
2618 , x_msg_data => x_msg_data);
2619
2620 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2621 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Error Returned by Create_Empty_Default_Lot(1).', 'pick_lots.log');
2622 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2623 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Create_Empty_Default_Lot');
2624 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2625 FND_MSG_PUB.ADD;
2626 RAISE FND_API.G_EXC_ERROR;
2627 END IF;
2628 l_need_update_default_lot := FALSE;
2629
2630 /* ======================================================================================= */
2631 /* At this stage, the Query_Reservation has been called by reate_Empty_Default_Lot */
2632 /* to update the memory table GMI_Reservation_Util.ic_tran_rec_tbl. */
2633 /* ======================================================================================= */
2634
2635 END IF;
2636
2637 /* There may have been a problem getting the rows */
2638 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
2639 THEN
2640 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Error Returned by Query_Reservation.', 'pick_lots.log');
2641 FND_MESSAGE.SET_NAME('GMI','GMI_QRY_RSV_FAILED');
2642 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2643 FND_MESSAGE.Set_Token('SO_LINE_ID', l_rsv_rec.demand_source_line_id);
2644 FND_MSG_PUB.ADD;
2645 RAISE FND_API.G_EXC_ERROR;
2646 END IF;
2647
2648 /* At this point the table should have rows */
2649 IF (GMI_Reservation_Util.ic_tran_rec_tbl.COUNT > 0)
2650 THEN
2651 /* Retrieve the default lot transaction we'll need it later */
2652 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: we have rows now calling Get_Default_Lot.', 'pick_lots.log');
2653 GMI_Reservation_Util.Get_Default_Lot(
2654 x_ic_tran_pnd_index => l_default_lot_index
2655 , x_return_status => x_return_status
2656 , x_msg_count => x_msg_count
2657 , x_msg_data => x_msg_data);
2658
2659 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2660 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Error Returned by Get_Default_Lot.', 'pick_lots.log');
2661 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2662 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_Default_Lot');
2663 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2664 FND_MSG_PUB.ADD;
2665 RAISE FND_API.G_EXC_ERROR;
2666 END IF;
2667
2668 /* ============================================================================================= */
2669 /* Lock rows in ic_loct_inv. */
2670 /* ============================================================================================= */
2671 -- Bug 2749329 Do not lock. Lock_inventory call and return handling is commented.
2672 /*
2673 GMI_Locks.Lock_Inventory(
2674 i_item_id => ll_ic_tran_rec.item_id
2675 , i_whse_code => ll_ic_tran_rec.whse_code
2676 , o_lock_status => l_lock_status
2677 );
2678
2679 IF (l_lock_status = FALSE) THEN
2680 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots ERROR:Returned by Lock_Inventory.');
2681 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
2682 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Locks.Lock_Inventory');
2683 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2684 FND_MSG_PUB.Add;
2685 RAISE FND_API.G_EXC_ERROR;
2686 END IF;
2687 */
2688 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: default_lot_index='||l_default_lot_index, 'pick_lots.log');
2689 /* ======================================================================================= */
2690 /* Case where a reservation has been called but the default lot is not created. */
2691 /* Need to create a default lot : */
2692 /* ======================================================================================= */
2693 IF (l_default_lot_index=0)
2694 THEN
2695 GMI_Reservation_Util.Create_Empty_Default_Lot
2696 ( p_ic_tran_rec => ll_ic_tran_rec
2697 , p_organization_id => l_organization_id
2698 , x_default_lot_index => l_default_lot_index
2699 , x_return_status => x_return_status
2700 , x_msg_count => x_msg_count
2701 , x_msg_data => x_msg_data);
2702
2703 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2704 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Error Returned by Create_Empty_Default_Lot(2).', 'pick_lots.log');
2705 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2706 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Create_Empty_Default_Lot');
2707 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2708 FND_MSG_PUB.ADD;
2709 RAISE FND_API.G_EXC_ERROR;
2710 END IF;
2711 l_need_update_default_lot := FALSE;
2712
2713 /* ======================================================================================= */
2714 /* At this stage, the Query_Reservation has been called by reate_Empty_Default_Lot */
2715 /* to update the memory table GMI_Reservation_Util.ic_tran_rec_tbl. */
2716 /* ======================================================================================= */
2717
2718 END IF;
2719 /* Populate local default row to hold values for comparision */
2720 l_default_tran_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index);
2721
2722 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots u:: saved default transaction to local rec.', 'pick_lots.log');
2723
2724 /* ============================================================================================== */
2725 /* Populate the rec_types (with the original values) and make the choices. */
2726 /* ============================================================================================== */
2727 IF (ll_ic_tran_rec.trans_id >0)
2728 THEN
2729 /* ============================================================================================== */
2730 /* At this stage, it is either a delete (allocated transaction) or an update (default lot transaction) : */
2731 /* Make the choice here : */
2732 /* ============================================================================================== */
2733 /* --------------------------------------------------------------------------------------- */
2734 /* Populate local original rec to hold values for comparision */
2735 /* if this is not the default rec copy the original rec to l_original_tran_rec */
2736 /* else this is the default rec copy the default rec to l_original_tran_rec */
2737 /* --------------------------------------------------------------------------------------- */
2738 GMI_reservation_Util.PrintLn('opm_dbg) in Set_Pick_Lots: l_default_tran_rec.trans_id is ' || l_default_tran_rec.trans_id, 'pick_lots.log');
2739 GMI_reservation_Util.PrintLn('opm_dbg) in Set_Pick_Lots: p_original_rsv_rec.reservation_id is ' || ll_ic_tran_rec.trans_id, 'pick_lots.log');
2740 IF (l_default_tran_rec.trans_id <> ll_ic_tran_rec.trans_id)
2741 THEN
2742 /* This is NOT the default lot. */
2743 GMI_Reservation_Util.Get_Allocation(
2744 p_trans_id => ll_ic_tran_rec.trans_id
2745 ,x_ic_tran_pnd_index => l_original_tran_index
2746 ,x_return_status => x_return_status
2747 ,x_msg_count => x_msg_count
2748 ,x_msg_data => x_msg_data);
2749
2750 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2751 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Error Returned by Get_Allocation.', 'pick_lots.log');
2752 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2753 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_Allocation');
2754 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2755 FND_MSG_PUB.ADD;
2756 RAISE FND_API.G_EXC_ERROR;
2757 END IF;
2758
2759 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Not updating the default, save orig trans to local rec.', 'pick_lots.log');
2760 l_original_tran_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_original_tran_index);
2761 ELSE
2762 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Updating the default, save default trans to local rec.', 'pick_lots.log');
2763 l_original_tran_rec := l_default_tran_rec;
2764 END IF;
2765 ELSE
2766 /* ==============================================================================================*/
2767 /* trans_id = 0, So create transaction. */
2768 /* ============================================================================================== */
2769 l_ic_tran_rec := ll_ic_tran_rec;
2770
2771 GMI_Reservation_Util.Validation_ictran_rec
2772 (p_ic_tran_rec => ll_ic_tran_rec
2773 ,x_ic_tran_rec => l_ic_tran_rec
2774 ,x_return_status => x_return_status
2775 ,x_msg_count => x_msg_count
2776 ,x_msg_data => x_msg_data);
2777
2778 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2779 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Error Returned by Validation_ictran_rec.', 'pick_lots.log');
2780 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2781 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Validation_ictran_rec');
2782 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2783 FND_MSG_PUB.ADD;
2784 RAISE FND_API.G_EXC_ERROR;
2785 END IF;
2786
2787 l_ic_tran_rec.doc_id := l_demand_source_header_id;
2788 l_ic_tran_rec.line_id := l_demand_source_line_id;
2789
2790 ll_ic_tran_rec := l_ic_tran_rec;
2791 END IF;
2792 ELSE
2793 /* ============================================================================================== */
2794 /* Nothing reserved. */
2795 /* This case may cause a pb, if there is an allocated transaction manually created without default lot created. */
2796 /* I may need to call Create_reservation in this case !. */
2797 /* Or create the default lot. */
2798 /* Shouldn't go here !!! */
2799 /* ============================================================================================== */
2800 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Nothing Reserved (PROBLEM?).', 'pick_lots.log');
2801 l_default_lot_index := 0;
2802 END IF;
2803 END IF;
2804
2805 /* ==============================================================================================*/
2806 /* Process the Transaction : */
2807 /* Update Transaction (either update qty - if no Change lot, or Delete/Create - if Change lot) */
2808 /* Delete Transaction */
2809 /* Create a new transaction */
2810 /* ============================================================================================== */
2811 GMI_reservation_Util.PrintLn('(opm_dbg)in Set_Pick_Lots: What to do : trans_id='||ll_ic_tran_rec.trans_id||', trans_qty='||ll_ic_tran_rec.trans_qty||', trans_date='||ll_ic_tran_rec.trans_date, 'pick_lots.log');
2812 IF (ll_ic_tran_rec.trans_id > 0 AND ll_ic_tran_rec.trans_qty <>0)
2813 THEN
2814 IF (l_original_tran_rec.lot_id = p_ic_tran_rec.lot_id)
2815 AND (l_original_tran_rec.location = p_ic_tran_rec.location)
2816 THEN
2817 /* ======================================================= */
2818 /* Calculate the delta: */
2819 /* Note that delta should be <0. */
2820 /* If it is >0, We won't have to update the default lot. NO*/
2821 /* If it is >0, update the default qty = 0 NO*/
2822 /* ======================================================= */
2823 l_delta_qty1 := l_original_tran_rec.trans_qty + ll_ic_tran_rec.trans_qty;
2824 l_delta_qty2 := l_original_tran_rec.trans_qty2 + ll_ic_tran_rec.trans_qty2;
2825
2826 /* ======================================================= */
2827 /* Beginning of the process */
2828 /* Note that in ll_ic_tran_rec, qties are >0 */
2829 /* ======================================================= */
2830 l_original_tran_rec.trans_qty := (-1) * ll_ic_tran_rec.trans_qty;
2831 l_original_tran_rec.trans_qty2 := (-1) * ll_ic_tran_rec.trans_qty2;
2832 l_original_tran_rec.trans_date := ll_ic_tran_rec.trans_date;
2833 l_original_tran_rec.reason_code := ll_ic_tran_rec.reason_code;
2834 GMI_reservation_Util.PrintLn('(opm_dbg)in Set_Pick_Lots: Update the transaction qty to:' || l_original_tran_rec.trans_qty , 'pick_lots.log');
2835 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
2836 p_api_version => 1.0
2837 ,p_init_msg_list => FND_API.G_FALSE
2838 ,p_commit => l_commit
2839 ,p_validation_level => l_validation_level
2840 ,p_tran_rec => l_original_tran_rec
2841 ,x_tran_row => l_temp_tran_row
2842 ,x_return_status => x_return_status
2843 ,x_msg_count => x_msg_count
2844 ,x_msg_data => x_msg_data);
2845
2846 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
2847 THEN
2848 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.', 'pick_lots.log');
2849 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
2850 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2851 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
2852 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2853 FND_MSG_PUB.Add;
2854 RAISE FND_API.G_EXC_ERROR;
2855 END IF;
2856 ELSE
2857 /* Need to delete the Original transaction, and then create a new one. */
2858 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Change of Lot...', 'pick_lots.log');
2859 l_change_lot := TRUE;
2860 END IF;
2861 END IF;
2862
2863 /* ========================================================================= */
2864 /* trans_id >0, and Qty = 0, then delete the transaction */
2865 /* ========================================================================= */
2866 IF (ll_ic_tran_rec.trans_id > 0 AND ll_ic_tran_rec.trans_qty = 0)
2867 OR (l_change_lot = TRUE)
2868 THEN
2869 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, Before calling Delete_Reservation', 'pick_lots.log');
2870
2871 /* ========================================================================= */
2872 /* Init variables (l_rsv_rec has already been setup)*/
2873 /* ========================================================================= */
2874 l_rsv_rec.reservation_id := ll_ic_tran_rec.trans_id;
2875
2876 GMI_Reservation_PVT.Delete_Reservation(
2877 x_return_status => x_return_status
2878 , x_msg_count => x_msg_count
2879 , x_msg_data => x_msg_data
2880 , p_validation_flag => l_validation_flag
2881 , p_rsv_rec => l_rsv_rec
2882 , p_serial_number => l_serial_number
2883 );
2884
2885 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
2886 THEN
2887 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, Error returned by Delete_Reservation, Error='||x_return_status, 'pick_lots.log');
2888 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2889 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_PVT.Delete_Reservation');
2890 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2891 FND_MSG_PUB.Add;
2892 RAISE FND_API.G_EXC_ERROR;
2893 END IF;
2894
2895 END IF;
2896
2897 /* ========================================================================= */
2898 /* trans_id =0, and Qty > 0, then create a new OMSO transaction */
2899 /* ========================================================================= */
2900 IF (ll_ic_tran_rec.trans_id = 0 AND ll_ic_tran_rec.trans_qty <> 0)
2901 OR (l_change_lot = TRUE)
2902 THEN
2903 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, Before creating new transaction', 'pick_lots.log');
2904 IF (l_change_lot = FALSE)
2905 THEN
2906 l_original_tran_rec := l_ic_tran_rec;
2907 ELSE
2908 l_original_tran_rec := ll_ic_tran_rec;
2909 l_original_tran_rec.trans_id := null;
2910 END IF;
2911
2912 /* ======================================================= */
2913 /* Beginning of the process */
2914 /* By security, the lot_status is set to null. (04-Sept-2000) */
2915 /* ======================================================= */
2916 l_original_tran_rec.doc_type := 'OMSO';
2917 /* hverddin Commented out lot_staus defaulting
2918 l_original_tran_rec.lot_status := null;
2919 For Bug 1790512 18-MAY-01
2920 */
2921 l_original_tran_rec.event_id := null;
2922 l_original_tran_rec.text_code := null;
2923 l_original_tran_rec.trans_qty := (-1) * ll_ic_tran_rec.trans_qty;
2924 l_original_tran_rec.trans_um := ll_ic_tran_rec.trans_um;
2925 l_original_tran_rec.trans_qty2 := (-1) * ll_ic_tran_rec.trans_qty2;
2926 l_original_tran_rec.trans_um2 := ll_ic_tran_rec.trans_um2;
2927
2928 gmi_reservation_util.println('Value of l_original_tran_rec.trans_qty is '||l_original_tran_rec.trans_qty);
2929 gmi_reservation_util.println('Value of l_original_tran_rec.trans_qty2 is '||l_original_tran_rec.trans_qty2);
2930 gmi_reservation_util.println('Value of l_original_tran_rec.trans_um is '|| l_original_tran_rec.trans_um);
2931 gmi_reservation_util.println('Value of l_original_tran_rec.trans_um2 is '|| l_original_tran_rec.trans_um2);
2932 GMI_reservation_Util.PrintLn('(opm_dbg)in Set_Pick_Lots: Create the new transaction :', 'pick_lots.log');
2933 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION(
2934 p_api_version => 1.0
2935 ,p_init_msg_list => FND_API.G_FALSE
2936 ,p_commit => l_commit
2937 ,p_validation_level => l_validation_level
2938 ,p_tran_rec => l_original_tran_rec
2939 ,x_tran_row => l_temp_tran_row
2940 ,x_return_status => x_return_status
2941 ,x_msg_count => x_msg_count
2942 ,x_msg_data => x_msg_data);
2943
2944 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
2945 THEN
2946 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_lots: Error returned by GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION.', 'pick_lots.log');
2947 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
2948 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2949 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
2950 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2951 FND_MSG_PUB.Add;
2952 RAISE FND_API.G_EXC_ERROR;
2953 END IF;
2954
2955 /* Begin enhancement 1928979 - lakshmi swamy */
2956
2957 IF (p_mo_line_id is NOT NULL) THEN
2958 GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip ( l_organization_id , p_mo_line_id,
2959 x_return_status, x_msg_count, x_msg_data, l_pick_slip_number);
2960
2961 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
2962 THEN
2963 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_lots: Error returned by GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip.', 'pick_lots.log');
2964 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2965 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip');
2966 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2967 FND_MSG_PUB.Add;
2968 RAISE FND_API.G_EXC_ERROR;
2969 END IF;
2970
2971 UPDATE ic_tran_pnd
2972 SET pick_slip_number = l_pick_slip_number
2973 WHERE trans_id = l_temp_tran_row.trans_id;
2974 END IF;
2975 /* End enhancement 1928979 - lakshmi swamy */
2976
2977 p_ic_tran_rec.trans_id := l_temp_tran_row.trans_id;
2978
2979 END IF;
2980 /* balancing default lot no matter what action has been taken in allocations */
2981
2982 l_default_tran_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_default_lot_index);
2983 balance_default_lot
2984 ( p_ic_default_rec => l_default_tran_rec
2985 , p_opm_item_id => l_default_tran_rec.item_id
2986 , x_return_status => x_return_status
2987 , x_msg_count => x_msg_count
2988 , x_msg_data => x_msg_data
2989 );
2990
2991 /* added by fabdi 20/08/2001 Bug 2023369 */
2992 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, p_ic_tran_rec.line_id= '||p_ic_tran_rec.line_id, 'pick_lots.log');
2993
2994 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, p_mo_line_id = '||p_mo_line_id, 'pick_lots.log');
2995 if p_mo_line_id IS NOT NULL
2996 THEN
2997 select *
2998 into l_ic_txn_request_lines
2999 from ic_txn_request_lines
3000 where LINE_ID = p_mo_line_id
3001 for update OF quantity_detailed, secondary_quantity_detailed NOWAIT;
3002
3003 OPEN get_whse_ctl(l_organization_id);
3004 FETCH get_whse_ctl INTO l_whse_ctl;
3005 CLOSE get_whse_ctl;
3006
3007 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, l_ic_item_mst_rec.lot_ctl= '||l_ic_item_mst_rec.lot_ctl, 'pick_lots.log');
3008 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, l_organization_id= '||l_organization_id, 'pick_lots.log');
3009
3010 IF l_ic_item_mst_rec.lot_ctl <> 0
3011 THEN
3012 SELECT SUM(ABS(TRANS_QTY)), SUM(ABS(TRANS_QTY2))
3013 INTO l_NEW_ALLOCATED_QTY, l_NEW_ALLOCATED_QTY2
3014 from ic_tran_pnd
3015 where line_id = p_ic_tran_rec.line_id
3016 and staged_ind = 0
3017 and completed_ind = 0
3018 and delete_mark = 0
3019 and lot_id <> 0
3020 and doc_type = 'OMSO'
3021 and line_detail_id in
3022 (Select delivery_detail_id
3023 From wsh_delivery_details
3024 Where move_order_line_id = p_mo_line_id);
3025 ELSIF ((l_ic_item_mst_rec.loct_ctl * l_whse_ctl) <> 0 )
3026 THEN
3027 SELECT SUM(ABS(TRANS_QTY)), SUM(ABS(TRANS_QTY2))
3028 INTO l_NEW_ALLOCATED_QTY, l_NEW_ALLOCATED_QTY2
3029 from ic_tran_pnd
3030 where line_id = p_ic_tran_rec.line_id
3031 and staged_ind = 0
3032 and completed_ind = 0
3033 and delete_mark = 0
3034 and location <> GMI_Reservation_Util.G_DEFAULT_LOCT
3035 and doc_type = 'OMSO'
3036 and line_detail_id in
3037 (Select delivery_detail_id
3038 From wsh_delivery_details
3039 Where move_order_line_id = p_mo_line_id);
3040 ELSE
3041 SELECT SUM(ABS(TRANS_QTY)), SUM(ABS(TRANS_QTY2))
3042 INTO l_NEW_ALLOCATED_QTY, l_NEW_ALLOCATED_QTY2
3043 from ic_tran_pnd
3044 where line_id = p_ic_tran_rec.line_id
3045 and staged_ind = 0
3046 and completed_ind = 0
3047 and delete_mark = 0
3048 and doc_type = 'OMSO';
3049 END IF;
3050
3051 IF SQL%FOUND THEN
3052 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, l_NEW_ALLOCATED_QTY= '||l_NEW_ALLOCATED_QTY, 'pick_lots.log');
3053 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, l_NEW_ALLOCATED_QTY2= '||l_NEW_ALLOCATED_QTY2, 'pick_lots.log');
3054
3055 update ic_txn_request_lines
3056 set quantity_detailed = nvl(quantity_delivered,0) + NVL(l_NEW_ALLOCATED_QTY,0)
3057 , secondary_quantity_detailed = nvl(secondary_quantity_delivered,0) + NVL(l_NEW_ALLOCATED_QTY2,0)
3058 where line_id = p_mo_line_id;
3059 ELSE
3060 GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, Transaction Not Found for= '||p_ic_tran_rec.line_id, 'pick_lots.log');
3061 END IF;
3062 END IF;
3063
3064 /* end fabdi */
3065
3066 /* Oct-2000 : odaboval added the commit in order to solve the problem of switching between ICPCKLOT.fmb's tabs. */
3067 /* Only commit if called via Apps. (not SQLPLUS) */
3068
3069 /* NC - 11/13/01 changed IF(FND_GLOBAL.user_id > 0) to IF(FND_GLOBAL.user_id >= 0).
3070 User_id 0 is a valid one( sysadmin). Bug#2108143 .This issue caused problems at numerous customers'.*/
3071 IF (FND_GLOBAL.user_id >= 0)
3072 THEN
3073 IF ( p_commit = FND_API.G_TRUE)
3074 THEN
3075 GMI_Reservation_Util.printLn('End of Set_Pick_Lots. No Error (COMMIT)', 'pick_lots.log');
3076 COMMIT;
3077 ELSE
3078 GMI_Reservation_Util.printLn('End of Set_Pick_Lots. No Error but Not Commiting as the commit_flag is not set',
3079 'pick_lots.log');
3080 END IF;
3081 ELSE
3082 GMI_Reservation_Util.printLn('End of Set_Pick_Lots. Error (NO COMMIT)','pick_lots.log');
3083 END IF;
3084
3085 GMI_Reservation_Util.PrintLn('(opm_dbg) END of Set_Pick_Lots', 'pick_lots.log');
3086
3087 EXCEPTION
3088 WHEN FND_API.G_EXC_ERROR THEN
3089 ROLLBACK TO SAVEPOINT Accept_Pick_Lots;
3090
3091 GMI_Reservation_Util.printLn('End of Set_Pick_Lots. Error (Rollback)');
3092 FND_MESSAGE.Set_Name('GMI','UNEXPECTED_ERROR');
3093 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
3094 FND_MESSAGE.Set_Token('WHAT', 'ExpectedError');
3095 FND_MSG_PUB.Add;
3096 x_return_status := FND_API.G_RET_STS_ERROR;
3097
3098 /* Get message count and data */
3099 FND_MSG_PUB.count_and_get
3100 ( p_count => x_msg_count
3101 , p_data => x_msg_data
3102 );
3103
3104 WHEN locked_by_other_user
3105 THEN
3106 ROLLBACK TO SAVEPOINT Accept_Pick_Lots;
3107 GMI_Reservation_Util.printLn('End of Set_Pick_Lots. Error (Rollback) - lock error in Move Order Line', 'pick_lots.log');
3108 FND_MESSAGE.Set_Name('GMI','UNEXPECTED_ERROR');
3109 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
3110 FND_MESSAGE.Set_Token('WHAT', 'LockError');
3111 FND_MSG_PUB.Add;
3112 x_return_status := FND_API.G_RET_STS_ERROR;
3113
3114 WHEN OTHERS THEN
3115 ROLLBACK TO SAVEPOINT Accept_Pick_Lots;
3116
3117 GMI_Reservation_Util.printLn('End of Set_Pick_Lots. ErrorOthers (Rollback)');
3118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3119
3120 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
3121 , l_api_name
3122 );
3123
3124 /* Get message count and data */
3125 FND_MSG_PUB.count_and_get
3126 ( p_count => x_msg_count
3127 , p_data => x_msg_data
3128 );
3129
3130
3131 END Set_Pick_Lots;
3132
3133 PROCEDURE balance_default_lot
3134 ( p_ic_default_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
3135 , p_opm_item_id IN NUMBER
3136 , x_return_status OUT NOCOPY VARCHAR2
3137 , x_msg_count OUT NOCOPY NUMBER
3138 , x_msg_data OUT NOCOPY VARCHAR2
3139 ) IS
3140 l_line_rec OE_ORDER_PUB.line_rec_type;
3141 l_requested_qty NUMBER;
3142 l_requested_qty2 NUMBER;
3143 l_order_uom VARCHAR2(5);
3144 l_whse_code VARCHAR2(5);
3145 l_trans_qty NUMBER;
3146 l_trans_qty2 NUMBER;
3147 l_commit VARCHAR2(5) := FND_API.G_FALSE;
3148 l_validation_level VARCHAR2(4) := FND_API.G_VALID_LEVEL_FULL;
3149 l_ic_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
3150 l_temp_tran_row ic_tran_pnd%ROWTYPE;
3151 l_organization_id NUMBER;
3152 l_inventory_item_id NUMBER;
3153 l_ctl_ind VARCHAR2(1) ;
3154 l_opm_item_id NUMBER;
3155 l_trans_um VARCHAR2(5);
3156 l_trans_um2 VARCHAR2(5);
3157 l_orgn_code VARCHAR2(6);
3158
3159 Cursor get_trans_qty IS
3160 Select nvl(sum(trans_qty),0), nvl(sum(trans_qty2),0)
3161 From ic_tran_pnd
3162 Where line_id = l_line_rec.line_id
3163 And doc_type = 'OMSO'
3164 And item_id = p_opm_item_id
3165 And delete_mark = 0
3166 And (lot_id <> 0
3167 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
3168
3169 /* need to consider the non ctl items where after partial ship or stage,
3170 the default is split only after ship confirm
3171 a new oe line may or may not be created depends on the situation*/
3172 /* for a default lot, staged=1 means it is shipconfirmed
3173 completed=1 means it has been interfaced */
3174 Cursor get_trans_qty_non_ctl IS
3175 Select nvl(sum(trans_qty),0), nvl(sum(trans_qty2),0)
3176 From ic_tran_pnd
3177 Where line_id = l_line_rec.line_id
3178 And doc_type = 'OMSO'
3179 And item_id = p_opm_item_id
3180 And (staged_ind = 1 or completed_ind = 1)
3181 And delete_mark = 0
3182 And (lot_id = 0 AND location = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
3183
3184 -- Bug 3829535 added NVL
3185 Cursor get_line_rec IS
3186 Select order_quantity_uom
3187 ,NVL(ordered_quantity,0)
3188 ,NVL(ordered_quantity2,0)
3189 ,ship_from_org_id
3190 ,inventory_item_id
3191 From oe_order_lines_all
3192 Where line_id = l_line_rec.line_id;
3193
3194 Cursor get_whse_code IS
3195 Select whse_code
3196 , orgn_code
3197 From ic_whse_mst
3198 Where mtl_organization_id = l_organization_id;
3199
3200 Cursor get_opm_item_id (p_org_id in NUMBER
3201 , p_inv_item_id in NUMBER)
3202 IS
3203 Select ic.item_id
3204 , ic.item_um
3205 , ic.item_um2
3206 From ic_item_mst ic
3207 , mtl_system_items mtl
3208 Where mtl.organization_id = p_org_id
3209 and mtl.inventory_item_id = p_inv_Item_id
3210 and mtl.segment1 = ic.item_no;
3211 BEGIN
3212 l_ic_tran_rec := p_ic_default_rec;
3213 /* query the line information */
3214 /*
3215 OE_Line_Util.Query_Row
3216 (
3217 p_line_id => p_ic_default_rec.line_id,
3218 x_line_rec => l_line_rec
3219 );
3220 Query row was causing a problem at a customer's site. replacing it
3221 with the follwoing cursor. - NC 10/26/01 */
3222
3223 l_line_rec.line_id := p_ic_default_rec.line_id;
3224 GMI_reservation_Util.PrintLn('(opm_dbg)Balancing the default lot for order line_id: '||l_line_rec.line_id);
3225
3226 Open get_line_rec;
3227 Fetch get_line_rec
3228 INTO l_line_rec.order_quantity_uom
3229 , l_line_rec.ordered_quantity
3230 , l_line_rec.ordered_quantity2
3231 , l_organization_id
3232 , l_inventory_item_id
3233 ;
3234 Close get_line_rec;
3235
3236 /* get the order uom for OPM */
3237 Get_OPMUOM_from_AppsUOM
3238 (
3239 p_Apps_UOM => l_line_rec.order_quantity_uom
3240 , x_OPM_UOM => l_order_uom
3241 , x_return_status => x_return_status
3242 , x_msg_count => x_msg_count
3243 , x_msg_data => x_msg_data
3244 );
3245 GMI_reservation_Util.PrintLn('(opm_dbg)in balancing the default lot, l_order_uom: '||l_order_uom);
3246 /* with the available of changing item on the order pad, this item_id in the old trans
3247 * may have been changed, need to fetch the new one
3248 * bug 3018143
3249 */
3250 /* convert the ordered qty to requested qty in item_uom */
3251 Open get_opm_item_id(l_organization_id, l_inventory_item_id);
3252 Fetch get_opm_item_id
3253 Into l_opm_item_id
3254 , l_trans_um
3255 , l_trans_um2;
3256 Close get_opm_item_id;
3257
3258 -- Bug 3829535 added IF condition
3259 IF (l_order_uom <> l_trans_um) THEN
3260 GMI_reservation_Util.PrintLn('(opm_dbg)converting order qty: '||l_line_rec.ordered_quantity||' to item uom '||l_trans_um);
3261 GMICUOM.icuomcv
3262 (
3263 pitem_id => l_opm_item_id
3264 , plot_id => 0
3265 , pcur_qty => l_line_rec.ordered_quantity
3266 , pcur_uom => l_order_uom
3267 , pnew_uom => l_trans_um
3268 , onew_qty => l_requested_qty
3269 );
3270 ELSE
3271 l_requested_qty := l_line_rec.ordered_quantity;
3272 END IF;
3273 l_requested_qty2 := l_line_rec.ordered_quantity2;
3274 /* get the total supply qtys */
3275 l_trans_qty := 0;
3276 l_trans_qty2 := 0;
3277 /* see if it is a ctl item */
3278 GMI_RESERVATION_UTIL.check_lot_loct_ctl
3279 ( p_inventory_item_id => l_inventory_item_id
3280 ,p_mtl_organization_id => l_organization_id
3281 ,x_ctl_ind => l_ctl_ind
3282 ) ;
3283 IF l_ctl_ind = 'Y' THEN
3284 Open get_trans_qty;
3285 Fetch get_trans_qty INTO l_trans_qty, l_trans_qty2;
3286 Close get_trans_qty;
3287 ELSE -- no ctl
3288 Open get_trans_qty_non_ctl;
3289 Fetch get_trans_qty_non_ctl INTO l_trans_qty, l_trans_qty2;
3290 Close get_trans_qty_non_ctl;
3291 END IF;
3292
3293 l_trans_qty := ABS(l_trans_qty);
3294 l_trans_qty2 := ABS(l_trans_qty2);
3295 /* demand qtys should be requested - trans */
3296 l_ic_tran_rec.trans_qty := -1 * (l_requested_qty - l_trans_qty);
3297 l_ic_tran_rec.trans_qty2 := -1 * (l_requested_qty2 - l_trans_qty2);
3298 /* if trans_qty is 0 or neg, both would be 0*/
3299 IF l_ic_tran_rec.trans_qty >= 0 THEN
3300 l_ic_tran_rec.trans_qty := 0;
3301 l_ic_tran_rec.trans_qty2 := 0;
3302 END IF;
3303 /* if trans_qty2 is 0 or neg, both would be 0*/
3304 IF l_ic_tran_rec.trans_qty2 >= 0 THEN
3305 l_ic_tran_rec.trans_qty := 0;
3306 l_ic_tran_rec.trans_qty2 := 0;
3307 END IF;
3308
3309 GMI_reservation_Util.PrintLn('(opm_dbg)in balancing the default lot, qty1: '||l_ic_tran_rec.trans_qty);
3310 GMI_reservation_Util.PrintLn('(opm_dbg)in balancing the default lot, qty2: '||l_ic_tran_rec.trans_qty2);
3311
3312 /* check the whse, if it is changed, simply delete the trans and create a new default */
3313 Open get_whse_code;
3314 Fetch get_whse_code
3315 Into l_whse_code
3316 , l_orgn_code;
3317 Close get_whse_code;
3318
3319 IF (l_whse_code <> l_ic_tran_rec.whse_code OR l_opm_item_id <> p_opm_item_id) THEN
3320 GMI_reservation_Util.PrintLn('(opm_dbg)in balancing the default lot, whse or item change ');
3321 GMI_reservation_Util.PrintLn(' old whse '||l_whse_code);
3322 GMI_reservation_Util.PrintLn(' new whse '||l_ic_tran_rec.whse_code);
3323 GMI_reservation_Util.PrintLn(' old item_id '||l_opm_item_id);
3324 GMI_reservation_Util.PrintLn(' new item_id '||l_ic_tran_rec.item_id);
3325 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION(
3326 p_api_version => 1.0
3327 ,p_init_msg_list => fnd_api.g_false
3328 ,p_commit => l_commit
3329 ,p_validation_level => l_validation_level
3330 ,p_tran_rec => l_ic_tran_rec
3331 ,x_tran_row => l_temp_tran_row
3332 ,x_return_status => x_return_status
3333 ,x_msg_count => x_msg_count
3334 ,x_msg_data => x_msg_data);
3335 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d After DELETE_PENDING_TRANSACTION.');
3336 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
3337 THEN
3338 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Delete_Transaction().');
3339 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
3340 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
3341 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION');
3342 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
3343 FND_MSG_PUB.Add;
3344 RAISE FND_API.G_EXC_ERROR;
3345 END IF;
3346
3347 l_ic_tran_rec.trans_id := null;
3348 l_ic_tran_rec.whse_code := l_whse_code;
3349 l_ic_tran_rec.orgn_code := l_orgn_code;
3350 l_ic_tran_rec.item_id := l_opm_item_id;
3351 l_ic_tran_rec.trans_um := l_trans_um;
3352 l_ic_tran_rec.trans_um2 := l_trans_um2;
3353
3354 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION(
3355 p_api_version => 1.0
3356 ,p_init_msg_list => FND_API.G_FALSE
3357 ,p_commit => l_commit
3358 ,p_validation_level => l_validation_level
3359 ,p_tran_rec => l_ic_tran_rec
3360 ,x_tran_row => l_temp_tran_row
3361 ,x_return_status => x_return_status
3362 ,x_msg_count => x_msg_count
3363 ,x_msg_data => x_msg_data);
3364
3365 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
3366 THEN
3367 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot (Create DefaultLot): Error returned by GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION.', 'pick_lots.log');
3368 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
3369 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
3370 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
3371 FND_MESSAGE.Set_Token('WHERE', 'Create_Empty_Default_Lot');
3372 FND_MSG_PUB.Add;
3373 RAISE FND_API.G_EXC_ERROR;
3374 END IF;
3375 /* cancel all the reservations for GME */
3376 GMI_reservation_Util.PrintLn('(opm_dbg)in balancing the default lot, cancel reservation for line_id '||l_ic_tran_rec.line_id);
3377 GML_BATCH_OM_RES_PVT.cancel_res_for_so_line
3378 (
3379 P_so_line_id => l_ic_tran_rec.line_id
3380 , X_return_status => x_return_status
3381 , X_msg_cont => x_msg_count
3382 , X_msg_data => x_msg_data
3383 ) ;
3384
3385 ELSE
3386 GMI_reservation_Util.PrintLn('(opm_dbg)in balancing the default lot, update pending trans');
3387 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
3388 (
3389 p_api_version => 1.0
3390 ,p_init_msg_list => FND_API.G_FALSE
3391 ,p_commit => l_commit
3392 ,p_validation_level => l_validation_level
3393 ,p_tran_rec => l_ic_tran_rec
3394 ,x_tran_row => l_temp_tran_row
3395 ,x_return_status => x_return_status
3396 ,x_msg_count => x_msg_count
3397 ,x_msg_data => x_msg_data
3398 );
3399 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3400 GMI_reservation_Util.PrintLn('(opm_dbg)in balancing the default lot: ERROR');
3401 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
3402 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
3403 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
3404 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
3405 FND_MSG_PUB.Add;
3406 RAISE FND_API.G_EXC_ERROR;
3407 END IF;
3408 END IF;
3409 /* if the trans_qty is 0, remove all the outstand GME reservations as well */
3410 /* cancel all the reservations for GME */
3411 /*IF l_ic_tran_rec.trans_qty = 0 THEN
3412 GMI_reservation_Util.PrintLn('(opm_dbg)in balancing the default lot, cancel res');
3413 GML_BATCH_OM_RES_PVT.cancel_res_for_so_line
3414 (
3415 P_so_line_id => l_ic_tran_rec.line_id
3416 , X_return_status => x_return_status
3417 , X_msg_cont => x_msg_count
3418 , X_msg_data => x_msg_data
3419 ) ;
3420 END IF;
3421 */
3422 EXCEPTION
3423 WHEN FND_API.G_EXC_ERROR THEN
3424 /* Get message count and data*/
3425 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
3426 WHEN OTHERS THEN
3427 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
3428
3429 END;
3430
3431 PROCEDURE Create_Empty_Default_Lot
3432 ( p_ic_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
3433 , p_organization_id IN NUMBER
3434 , x_default_lot_index OUT NOCOPY BINARY_INTEGER
3435 , x_return_status OUT NOCOPY VARCHAR2
3436 , x_msg_count OUT NOCOPY NUMBER
3437 , x_msg_data OUT NOCOPY VARCHAR2
3438 ) IS
3439
3440 l_api_name CONSTANT VARCHAR2(30) := 'Create_Empty_Default_Lot';
3441 l_api_version CONSTANT VARCHAR2(10) := '1.0';
3442
3443 l_lock_status BOOLEAN;
3444 l_validation_flag VARCHAR2(10) := FND_API.G_TRUE;
3445 l_commit VARCHAR2(5) := FND_API.G_FALSE;
3446 l_validation_level VARCHAR2(4) := FND_API.G_VALID_LEVEL_FULL;
3447
3448 l_temp_tran_row ic_tran_pnd%ROWTYPE;
3449 l_original_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
3450 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
3451 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
3452 l_mtl_reservation_tbl_count NUMBER;
3453 l_error_code NUMBER;
3454
3455 ll_trans_id NUMBER;
3456
3457 BEGIN
3458 /* ======================================================================= */
3459 /* Init variables */
3460 /* ======================================================================= */
3461 x_return_status := FND_API.G_RET_STS_SUCCESS;
3462
3463
3464 SAVEPOINT Empty_Default_lot;
3465
3466 /* ============================================================================================= */
3467 /* Create the default Lot. */
3468 /* ============================================================================================= */
3469 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. Need to create default lot');
3470
3471 /* ============================================================================================= */
3472 /* Lock rows in ic_loct_inv. */
3473 /* ============================================================================================= */
3474 GMI_Locks.Lock_Inventory(
3475 i_item_id => p_ic_tran_rec.item_id
3476 , i_whse_code => p_ic_tran_rec.whse_code
3477 , o_lock_status => l_lock_status
3478 );
3479
3480 IF (l_lock_status = FALSE) THEN
3481 GMI_reservation_Util.PrintLn('(opm_dbg) in end of Create_Empty_Default_Lot c ERROR:Returned by Lock_Inventory.');
3482 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
3483 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Locks.Lock_Inventory');
3484 FND_MESSAGE.Set_Token('WHERE', 'Create_Empty_Default_Lot');
3485 FND_MSG_PUB.Add;
3486 RAISE FND_API.G_EXC_ERROR;
3487 END IF;
3488
3489 /* ============================================================================================= */
3490 /* No default lot exist AND MANUAL Allocation. Then create the default lot */
3491 /* ============================================================================================= */
3492 /* Fill the ic_tran_pnd record type, and then insert into ic_tran_pnd */
3493 l_original_tran_rec.item_id := p_ic_tran_rec.item_id;
3494 l_original_tran_rec.line_id := p_ic_tran_rec.line_id;
3495 l_original_tran_rec.co_code := p_ic_tran_rec.co_code;
3496 l_original_tran_rec.orgn_code := p_ic_tran_rec.orgn_code;
3497 l_original_tran_rec.whse_code := p_ic_tran_rec.whse_code;
3498 l_original_tran_rec.lot_id := 0; /* the default lot */
3499 l_original_tran_rec.lot_no := 'NONE'; /* the default lot */
3500 l_original_tran_rec.location := GMI_Reservation_Util.G_DEFAULT_LOCT;
3501 l_original_tran_rec.doc_id := p_ic_tran_rec.doc_id;
3502 l_original_tran_rec.doc_type := 'OMSO';
3503 l_original_tran_rec.doc_line := p_ic_tran_rec.doc_line;
3504 l_original_tran_rec.line_type := 0;
3505 l_original_tran_rec.trans_qty := 0;
3506 l_original_tran_rec.trans_um := p_ic_tran_rec.trans_um;
3507 l_original_tran_rec.trans_qty2 := 0;
3508 l_original_tran_rec.trans_um2 := p_ic_tran_rec.trans_um2;
3509 l_original_tran_rec.reason_code := NULL;
3510 l_original_tran_rec.trans_date := p_ic_tran_rec.trans_date;
3511 l_original_tran_rec.qc_grade := p_ic_tran_rec.qc_grade;
3512 l_original_tran_rec.user_id := FND_GLOBAL.user_id;
3513 l_original_tran_rec.staged_ind := 0;
3514 l_original_tran_rec.event_id := 0;
3515 l_original_tran_rec.text_code := NULL;
3516
3517 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. item_id='||l_original_tran_rec.item_id);
3518 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. line_id='||l_original_tran_rec.line_id);
3519 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. co_code='||l_original_tran_rec.co_code);
3520 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. orgn_code='||l_original_tran_rec.orgn_code);
3521 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. whse_code='||l_original_tran_rec.whse_code);
3522 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. location='||l_original_tran_rec.location);
3523 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. doc_id='||l_original_tran_rec.doc_id);
3524 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. doc_line='||l_original_tran_rec.doc_line);
3525 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. trans_um='||l_original_tran_rec.trans_um);
3526 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. trans_um2='||l_original_tran_rec.trans_um2);
3527 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. trans_date='||l_original_tran_rec.trans_date);
3528 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. qc_grade='||l_original_tran_rec.qc_grade);
3529 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot. user_id='||l_original_tran_rec.user_id);
3530
3531 /* =================================================== */
3532 /* Check that the default lot doesnt exist. */
3533 /* =================================================== */
3534 GMI_Reservation_Util.Default_Lot_Exist
3535 ( p_line_id => l_original_tran_rec.line_id
3536 , p_item_id => l_original_tran_rec.item_id
3537 , x_trans_id => ll_trans_id
3538 , x_return_status => x_return_status
3539 , x_msg_count => x_msg_count
3540 , x_msg_data => x_msg_data);
3541
3542 IF (ll_trans_id is NULL)
3543 THEN
3544 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION(
3545 p_api_version => 1.0
3546 ,p_init_msg_list => FND_API.G_FALSE
3547 ,p_commit => l_commit
3548 ,p_validation_level => l_validation_level
3549 ,p_tran_rec => l_original_tran_rec
3550 ,x_tran_row => l_temp_tran_row
3551 ,x_return_status => x_return_status
3552 ,x_msg_count => x_msg_count
3553 ,x_msg_data => x_msg_data);
3554
3555 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
3556 THEN
3557 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot (Create DefaultLot): Error returned by GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION.', 'pick_lots.log');
3558 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
3559 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
3560 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
3561 FND_MESSAGE.Set_Token('WHERE', 'Create_Empty_Default_Lot');
3562 FND_MSG_PUB.Add;
3563 RAISE FND_API.G_EXC_ERROR;
3564 END IF;
3565 ELSE
3566 GMI_reservation_Util.PrintLn('(opm_dbg) Dont create the default lot again (Shouldnt be here) - 2 ! ');
3567 END IF;
3568
3569 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot (Create DefaultLot): trans_id='||l_temp_tran_row.trans_id, 'pick_lots.log');
3570
3571 /* ==================================================================================== */
3572 /* Need to update the memory table of Reservation. */
3573 /* ==================================================================================== */
3574 l_rsv_rec.organization_id := p_organization_id;
3575 l_rsv_rec.demand_source_header_id := p_ic_tran_rec.doc_id;
3576 l_rsv_rec.demand_source_line_id := p_ic_tran_rec.line_id;
3577
3578 l_validation_flag := FND_API.G_FALSE;
3579
3580 GMI_reservation_pub.Query_reservation
3581 ( p_api_version_number => 1.0
3582 , p_init_msg_lst => FND_API.G_FALSE
3583 , x_return_status => x_return_status
3584 , x_msg_count => x_msg_count
3585 , x_msg_data => x_msg_data
3586 , p_validation_flag => l_validation_flag
3587 , p_query_input => l_rsv_rec
3588 , p_cancel_order_mode => INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_YES
3589 , x_mtl_reservation_tbl => l_mtl_reservation_tbl
3590 , x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count
3591 , x_error_code => l_error_code
3592 , p_lock_records => FND_API.G_FALSE
3593 , p_sort_by_req_date => INV_RESERVATION_GLOBAL.G_QUERY_NO_SORT
3594 );
3595
3596 /* There may not be any rows, and it should be possible ! (if not auto-allocate before) */
3597 IF (GMI_Reservation_Util.ic_tran_rec_tbl.COUNT = 0)
3598 THEN
3599 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot: No Error but No Rows Found in reservation (ERROR)', 'pick_lots.log');
3600 FND_MESSAGE.SET_NAME('GMI','GMI_QRY_RSV_FAILED');
3601 FND_MESSAGE.Set_Token('WHERE', 'Create_Empty_Default_Lot');
3602 FND_MESSAGE.Set_Token('SO_LINE_ID', l_rsv_rec.demand_source_line_id);
3603 FND_MSG_PUB.ADD;
3604 RAISE FND_API.G_EXC_ERROR;
3605 END IF;
3606
3607 /* There may have been a problem getting the rows */
3608 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
3609 THEN
3610 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot: Error Returned by Query_Reservation(2).', 'pick_lots.log');
3611 FND_MESSAGE.SET_NAME('GMI','GMI_QRY_RSV_FAILED');
3612 FND_MESSAGE.Set_Token('WHERE', 'Create_Empty_Default_Lot');
3613 FND_MESSAGE.Set_Token('SO_LINE_ID', l_rsv_rec.demand_source_line_id);
3614 FND_MSG_PUB.ADD;
3615 RAISE FND_API.G_EXC_ERROR;
3616 END IF;
3617
3618 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot: we have rows now calling Get_Default_Lot.', 'pick_lots.log');
3619 GMI_Reservation_Util.Get_Default_Lot(
3620 x_ic_tran_pnd_index => x_default_lot_index
3621 , x_return_status => x_return_status
3622 , x_msg_count => x_msg_count
3623 , x_msg_data => x_msg_data);
3624
3625 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3626 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot: Error Returned by Get_Default_Lot.', 'pick_lots.log');
3627 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
3628 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_Default_Lot');
3629 FND_MESSAGE.Set_Token('WHERE', 'Create_Empty_Default_Lot');
3630 FND_MSG_PUB.ADD;
3631 RAISE FND_API.G_EXC_ERROR;
3632 END IF;
3633
3634 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot: default_lot_index='||x_default_lot_index, 'pick_lots.log');
3635 IF (x_default_lot_index=0)
3636 THEN
3637 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot: Still no default lot = ERROR.', 'pick_lots.log');
3638 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
3639 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_Util.Get_Default_Lot');
3640 FND_MESSAGE.Set_Token('WHERE', 'Create_Empty_Default_Lot');
3641 FND_MSG_PUB.ADD;
3642 RAISE FND_API.G_EXC_ERROR;
3643 END IF;
3644
3645 EXCEPTION
3646 WHEN FND_API.G_EXC_ERROR THEN
3647 ROLLBACK TO SAVEPOINT Empty_Default_lot;
3648
3649 GMI_Reservation_Util.printLn('End of Create_Empty_Default_Lot. Error');
3650 FND_MESSAGE.Set_Name('GMI','UNEXPECTED_ERROR');
3651 FND_MESSAGE.Set_Token('WHERE', 'Create_Empty_Default_Lot');
3652 FND_MESSAGE.Set_Token('WHAT', 'ExpectedError');
3653 FND_MSG_PUB.Add;
3654 x_return_status := FND_API.G_RET_STS_ERROR;
3655
3656 /* Get message count and data */
3657 FND_MSG_PUB.count_and_get
3658 ( p_count => x_msg_count
3659 , p_data => x_msg_data
3660 );
3661
3662 WHEN OTHERS THEN
3663 ROLLBACK TO SAVEPOINT Empty_Default_lot;
3664
3665 GMI_Reservation_Util.printLn('End of Create_Empty_Default_Lot. ErrorOthers');
3666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3667
3668 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
3669 , l_api_name
3670 );
3671
3672 /* Get message count and data */
3673 FND_MSG_PUB.count_and_get
3674 ( p_count => x_msg_count
3675 , p_data => x_msg_data
3676 );
3677
3678
3679 END Create_Empty_Default_Lot;
3680
3681 PROCEDURE Default_Lot_Exist
3682 ( p_line_id IN NUMBER
3683 , p_item_id IN NUMBER
3684 , x_trans_id OUT NOCOPY NUMBER
3685 , x_return_status OUT NOCOPY VARCHAR2
3686 , x_msg_count OUT NOCOPY NUMBER
3687 , x_msg_data OUT NOCOPY VARCHAR2
3688 ) IS
3689
3690 l_api_name CONSTANT VARCHAR2(30) := 'Default_Lot_Exist';
3691 l_api_version CONSTANT VARCHAR2(10) := '1.0';
3692
3693 /* Cursor for Checking the Default Lot existence : */
3694 CURSOR c_default_exist(l_line_id IN NUMBER,
3695 l_item_id IN NUMBER) IS
3696 SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */trans_id
3697 FROM ic_tran_pnd
3698 WHERE lot_id = 0
3699 AND delete_mark = 0
3700 AND doc_type = 'OMSO'
3701 AND item_id = l_item_id
3702 AND line_id = l_line_id;
3703
3704 BEGIN
3705 /* ======================================================================= */
3706 /* Init variables */
3707 /* ======================================================================= */
3708 x_return_status := FND_API.G_RET_STS_SUCCESS;
3709
3710 OPEN c_default_exist( p_line_id, p_item_id);
3711 FETCH c_default_exist
3712 INTO x_trans_id;
3713
3714 IF (c_default_exist%NOTFOUND)
3715 THEN
3716 GMI_Reservation_Util.PrintLn('(opm_dbg) end of Default_Lot_Exist : No default lot, Ok.');
3717 ELSE
3718 GMI_Reservation_Util.PrintLn('(opm_dbg) end of Default_Lot_Exist : One default lot already, no need to create another one.');
3719 END IF;
3720 CLOSE c_default_exist;
3721
3722
3723 EXCEPTION
3724 WHEN FND_API.G_EXC_ERROR THEN
3725 GMI_Reservation_Util.printLn('End of Default_Lot_Exist. Error');
3726 FND_MESSAGE.Set_Name('GMI','UNEXPECTED_ERROR');
3727 FND_MESSAGE.Set_Token('WHERE', 'Default_Lot_Exist');
3728 FND_MESSAGE.Set_Token('WHAT', 'ExpectedError');
3729 FND_MSG_PUB.Add;
3730 x_return_status := FND_API.G_RET_STS_ERROR;
3731
3732 /* Get message count and data */
3733 FND_MSG_PUB.count_and_get
3734 ( p_count => x_msg_count
3735 , p_data => x_msg_data
3736 );
3737
3738 WHEN OTHERS THEN
3739 GMI_Reservation_Util.printLn('End of Default_Lot_Exist. ErrorOthers');
3740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3741
3742 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
3743 , l_api_name
3744 );
3745
3746 /* Get message count and data */
3747 FND_MSG_PUB.count_and_get
3748 ( p_count => x_msg_count
3749 , p_data => x_msg_data
3750 );
3751
3752
3753 END Default_Lot_Exist;
3754
3755 Procedure create_dflt_lot_from_scratch
3756 ( p_whse_code IN VARCHAR2
3757 , p_line_id IN NUMBER
3758 , p_item_id IN NUMBER
3759 , p_qty1 IN NUMBER
3760 , p_qty2 IN NUMBER
3761 , x_return_status OUT NOCOPY VARCHAR2
3762 , x_msg_count OUT NOCOPY NUMBER
3763 , x_msg_data OUT NOCOPY VARCHAR2
3764 ) IS
3765 l_ictran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
3766 l_tran_row IC_TRAN_PND%ROWTYPE;
3767 l_uom1 Varchar2(5);
3768 l_uom2 Varchar2(5);
3769 l_orgn_code Varchar2(10);
3770 l_co_code Varchar2(10);
3771 l_whse_code Varchar2(10);
3772 l_doc_id NUMBER;
3773
3774 Cursor get_orgn_code IS
3775 Select w.orgn_code, co.co_code
3776 From ic_whse_mst w, sy_orgn_mst co
3777 Where w.whse_code = p_whse_code
3778 AND w.orgn_code = co.orgn_code;
3779 Cursor get_doc_id IS
3780 Select mtl.sales_order_id
3781 From mtl_sales_orders mtl,
3782 oe_order_lines_all sol,
3783 oe_order_headers_all soh,
3784 oe_transaction_types_tl tt
3785 Where sol.line_id = p_line_id
3786 AND mtl.segment1 = to_char(soh.order_number)
3787 AND mtl.segment2 = tt.name
3788 -- AND tt.language = userenv('LANG') -- OPM bug 3770264
3789 AND tt.language = (select language_code -- OPM bug 3770264
3790 from fnd_languages
3791 where installed_flag = 'B')
3792 AND mtl.segment3 = fnd_profile.value('ONT_SOURCE_CODE')
3793 AND tt.transaction_type_id = soh.order_type_id
3794 AND sol.header_id = soh.header_id;
3795 Cursor get_uom IS
3796 Select item_um, item_um2
3797 From ic_item_mst
3798 Where item_id = p_item_id;
3799
3800 -- BEGIN - Bug 3216096.
3801 -- Bug 3558787 get line Number as well
3802 Cursor get_line_info (l_line_id IN number) IS
3803 Select SCHEDULE_SHIP_DATE, line_number+(shipment_number / 10)
3804 From oe_order_lines_all
3805 Where line_id = l_line_id;
3806
3807 l_schedule_ship_date DATE;
3808 l_line_number NUMBER;
3809 -- END - Bug 3216096
3810
3811 Begin
3812
3813 Open get_uom;
3814 Fetch get_uom Into l_uom1,l_uom2;
3815 Close get_uom;
3816 Open get_doc_id;
3817 Fetch get_doc_id Into l_doc_id;
3818 Close get_doc_id;
3819 Open get_orgn_code;
3820 Fetch get_orgn_code Into l_orgn_code, l_co_code;
3821 Close get_orgn_code;
3822
3823 -- BEGIN - Bug 3216096 Bug 3558787 added l_line_number
3824 -- Get the scheduled to ship date from the line
3825 Open get_line_info (p_line_id);
3826 Fetch get_line_info into l_schedule_ship_date, l_line_number;
3827 Close get_line_info;
3828 -- END - Bug 3216096
3829
3830 GMI_reservation_Util.PrintLn('(opm_dbg) Create_default_lot from scratch','opm.log');
3831 l_ictran_rec.trans_id := null;
3832 l_ictran_rec.lot_id := 0;
3833 l_ictran_rec.location := GMI_Reservation_Util.G_DEFAULT_LOCT;
3834 l_ictran_rec.trans_qty := -1 * p_qty1;
3835 l_ictran_rec.trans_qty2 := -1 * p_qty2;
3836 l_ictran_rec.qc_grade := null;
3837 l_ictran_rec.lot_status := null;
3838 l_ictran_rec.trans_date := l_schedule_ship_date; -- Bug 3216096
3839 l_ictran_rec.item_id := p_ITEM_ID;
3840 l_ictran_rec.line_id := p_LINE_ID;
3841 l_ictran_rec.co_code := l_co_code;
3842 l_ictran_rec.orgn_code := l_orgn_code;
3843 l_ictran_rec.whse_code := p_whse_code;
3844 l_ictran_rec.doc_id := l_doc_id;
3845 l_ictran_rec.doc_type := 'OMSO';
3846 l_ictran_rec.doc_line := l_line_number; -- Bug 3558787
3847 l_ictran_rec.line_type := 0; -- Check this value.
3848 l_ictran_rec.reason_code := NULL;
3849 l_ictran_rec.trans_stat := NULL;
3850 l_ictran_rec.trans_um := l_uom1;
3851 l_ictran_rec.trans_um2 := l_uom2;
3852 l_ictran_rec.staged_ind := 0;
3853 l_ictran_rec.event_id := 0;
3854 l_ictran_rec.text_code := NULL;
3855 l_ictran_rec.user_id := NULL;
3856 l_ictran_rec.create_lot_index := NULL;
3857
3858 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
3859 ( p_api_version => 1.0
3860 , p_init_msg_list => FND_API.G_FALSE
3861 , p_commit => FND_API.G_FALSE
3862 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3863 , p_tran_rec => l_ictran_rec
3864 , x_tran_row => l_tran_row
3865 , x_return_status => x_return_status
3866 , x_msg_count => x_msg_count
3867 , x_msg_data => x_msg_data
3868 );
3869
3870 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3871 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by Create_Pending_Transaction,
3872 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
3873 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
3874 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
3875 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
3876 FND_MESSAGE.Set_Token('WHERE','Create_Default_Lot');
3877 FND_MSG_PUB.Add;
3878 raise FND_API.G_EXC_ERROR;
3879 END IF;
3880 END create_dflt_lot_from_scratch;
3881
3882 Procedure create_transaction_for_rcv
3883 ( p_whse_code IN VARCHAR2
3884 , p_transaction_id IN NUMBER
3885 , p_line_id IN NUMBER
3886 , p_item_id IN NUMBER
3887 , p_lot_id IN NUMBER
3888 , p_location IN VARCHAR2
3889 , p_qty1 IN NUMBER
3890 , p_qty2 IN NUMBER
3891 , x_return_status OUT NOCOPY VARCHAR2
3892 , x_msg_count OUT NOCOPY NUMBER
3893 , x_msg_data OUT NOCOPY VARCHAR2
3894 ) IS
3895
3896 l_ictran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
3897 l_tran_row IC_TRAN_PND%ROWTYPE;
3898 l_uom1 Varchar2(5);
3899 l_uom2 Varchar2(5);
3900 l_orgn_code Varchar2(10);
3901 l_co_code Varchar2(10);
3902 l_whse_code Varchar2(10);
3903 l_doc_id NUMBER;
3904 l_locator_id NUMBER;
3905 l_item_loct_ctl NUMBER;
3906 l_whse_loct_ctl NUMBER;
3907 l_inv_item_id NUMBER;
3908 l_cnt NUMBER;
3909 l_msg_data VARCHAR2(250);
3910 l_rcv_trans_type Varchar2(25);
3911 l_rcv_qty NUMBER;
3912
3913 Cursor get_orgn_code IS
3914 Select w.orgn_code
3915 , co.co_code
3916 , w.loct_ctl
3917 From ic_whse_mst w, sy_orgn_mst co
3918 Where w.whse_code = p_whse_code
3919 AND w.orgn_code = co.orgn_code;
3920 Cursor get_doc_id IS
3921 Select mtl.sales_order_id
3922 , sol.inventory_item_id
3923 From mtl_sales_orders mtl,
3924 oe_order_lines_all sol,
3925 oe_order_headers_all soh,
3926 oe_transaction_types_tl tt
3927 Where sol.line_id = p_line_id
3928 AND mtl.segment1 = to_char(soh.order_number)
3929 AND mtl.segment2 = tt.name
3930 AND mtl.segment3 = fnd_profile.value('ONT_SOURCE_CODE')
3931 --AND tt.language = userenv('LANG') -- OPM bug 3770264
3932 AND tt.language = (select language_code -- OPM bug 3770264
3933 from fnd_languages
3934 where installed_flag = 'B')
3935 AND tt.transaction_type_id = soh.order_type_id
3936 AND sol.header_id = soh.header_id;
3937 Cursor get_uom IS
3938 Select item_um
3939 , item_um2
3940 , loct_ctl
3941 From ic_item_mst
3942 Where item_id = p_item_id;
3943
3944 Cursor get_rcv_trans(p_transaction_id IN NUMBER) IS
3945 Select transaction_date
3946 , locator_id
3947 , transaction_type
3948 , quantity
3949 , uom_code --BUG#3503593
3950 From rcv_transactions
3951 Where transaction_id = p_transaction_id;
3952 --BEGIN BUG#3503593
3953 l_uom_code varchar2(4);
3954 l_uom varchar2(25);
3955 l_qty NUMBER;
3956 --END BUG#3503593
3957
3958 Cursor get_grade IS
3959 Select qc_grade
3960 From ic_lots_mst
3961 Where lot_id = p_lot_id;
3962
3963 Cursor get_location(p_locator_id IN NUMBER) IS
3964 Select location
3965 From ic_loct_mst
3966 Where inventory_location_id = p_locator_id;
3967
3968 Cursor get_mtl_location (p_locator_id IN NUMBER
3969 , p_inventory_item_id IN NUMBER) IS
3970 Select segment1
3971 From mtl_item_locations
3972 Where inventory_location_id = p_locator_id;
3973
3974 Cursor get_lot_status (p_lot_id IN NUMBER
3975 , p_whse_code IN VARCHAR2
3976 , p_location IN VARCHAR2
3977 , p_item_id IN NUMBER)
3978 IS
3979 Select lot_status
3980 From ic_loct_inv
3981 Where item_id = p_item_id
3982 and lot_id = p_lot_id
3983 and location = p_location
3984 and whse_code = p_whse_code;
3985
3986 -- BEGIN Bug 3558787 get line Number
3987 Cursor get_line_info (l_line_id IN number) IS
3988 Select SCHEDULE_SHIP_DATE, line_number+(shipment_number / 10)
3989 From oe_order_lines_all
3990 Where line_id = l_line_id;
3991
3992 l_schedule_ship_date DATE;
3993 l_line_number NUMBER;
3994 -- END Bug 3558787
3995
3996 Begin
3997
3998 x_return_status := FND_API.G_RET_STS_SUCCESS;
3999
4000 GMI_reservation_Util.PrintLn('(opm_dbg) entering the trans for rcv ');
4001 Open get_uom;
4002 Fetch get_uom
4003 Into l_uom1
4004 ,l_uom2
4005 ,l_item_loct_ctl
4006 ;
4007 Close get_uom;
4008 Open get_doc_id;
4009 Fetch get_doc_id
4010 Into l_doc_id
4011 , l_inv_item_id
4012 ;
4013 Close get_doc_id;
4014 Open get_orgn_code;
4015 Fetch get_orgn_code
4016 Into l_orgn_code
4017 , l_co_code
4018 , l_whse_loct_ctl
4019 ;
4020 Close get_orgn_code;
4021
4022 -- BEGIN Bug 3558787 get line Number
4023 Open get_line_info (p_line_id);
4024 Fetch get_line_info into l_schedule_ship_date, l_line_number;
4025 Close get_line_info;
4026 -- END Bug 3558787
4027
4028
4029 GMI_reservation_Util.PrintLn('(opm_dbg) Create_lot from scratch');
4030 l_ictran_rec.location := NVL(p_location,GMI_Reservation_Util.G_DEFAULT_LOCT);
4031 l_ictran_rec.trans_id := null;
4032 l_ictran_rec.trans_qty := -1 * p_qty1;
4033 l_ictran_rec.trans_qty2 := -1 * p_qty2;
4034 l_ictran_rec.qc_grade := null;
4035 l_ictran_rec.lot_status := null;
4036 l_ictran_rec.trans_date := null;
4037 l_ictran_rec.trans_date := NULL;
4038 l_ictran_rec.item_id := p_ITEM_ID;
4039 l_ictran_rec.line_id := p_LINE_ID;
4040 l_ictran_rec.co_code := l_co_code;
4041 l_ictran_rec.orgn_code := l_orgn_code;
4042 l_ictran_rec.whse_code := p_whse_code;
4043 l_ictran_rec.doc_id := l_doc_id;
4044 l_ictran_rec.doc_type := 'OMSO';
4045 l_ictran_rec.doc_line := l_line_number; --Bug 3558787
4046 l_ictran_rec.line_type := 2; -- Bug 3850980 2 is Drop Shipments.
4047 l_ictran_rec.reason_code := NULL;
4048 l_ictran_rec.trans_stat := NULL;
4049 l_ictran_rec.trans_um := l_uom1;
4050 l_ictran_rec.trans_um2 := l_uom2;
4051 l_ictran_rec.staged_ind := 1;
4052 l_ictran_rec.event_id := 0;
4053 l_ictran_rec.non_inv := 0;
4054 l_ictran_rec.text_code := NULL;
4055 l_ictran_rec.user_id := NULL;
4056 l_ictran_rec.create_lot_index := NULL;
4057 IF p_lot_id <> 0 THEN
4058 l_ictran_rec.lot_id := p_lot_id;
4059 Open get_grade;
4060 Fetch get_grade Into l_ictran_rec.qc_grade;
4061 Close get_grade;
4062 GMI_RESERVATION_UTIL.println('qc_grade '||l_ictran_rec.qc_grade);
4063 ELSE
4064 l_ictran_rec.lot_id := 0;
4065 END IF;
4066
4067 l_ictran_rec.trans_id := l_tran_row.trans_id;
4068 GMI_RESERVATION_UTIL.println('trans_id '||l_ictran_rec.trans_id);
4069 Open get_rcv_trans (p_transaction_id);
4070 Fetch get_rcv_trans
4071 Into l_ictran_rec.trans_date
4072 ,l_locator_id
4073 ,l_rcv_trans_type
4074 ,l_rcv_qty
4075 ,l_uom_code --BUG#3503593
4076 ;
4077 Close get_rcv_trans;
4078
4079 --BEGIN BUG#3503593
4080 GMI_RESERVATION_UTIL.Get_OPMUOM_from_AppsUOM(
4081 p_Apps_UOM => l_uom_code
4082 , x_OPM_UOM => l_uom
4083 , x_return_status => x_return_status
4084 , x_msg_count => x_msg_count
4085 , x_msg_data => x_msg_data);
4086
4087 IF l_ictran_rec.trans_um <> l_uom THEN
4088 GMICUOM.icuomcv(pitem_id => l_ictran_rec.item_id ,
4089 plot_id => l_ictran_rec.lot_id,
4090 pcur_qty => p_qty1,
4091 pcur_uom => l_uom,
4092 pnew_uom => l_ictran_rec.trans_um,
4093 onew_qty => l_qty);
4094 l_ictran_rec.trans_qty := -1 * l_qty;
4095 END IF;
4096 --END BUG#3503593
4097
4098 IF ( l_rcv_trans_type = 'RETURN TO RECEIVING' or l_rcv_trans_type = 'RETURN TO VENDOR') THEN
4099 GMI_RESERVATION_UTIL.println('rcv type is return '||l_rcv_trans_type);
4100 GMI_RESERVATION_UTIL.println('rcv type is return , no transactions are created');
4101 RETURN;
4102 END IF;
4103 GMI_RESERVATION_UTIL.println('trans_date '||l_ictran_rec.trans_date);
4104 IF nvl(l_locator_id, 0) <> 0 THEN
4105 IF l_whse_loct_ctl * l_item_loct_ctl = 1 THEN
4106 Open get_location (l_locator_id);
4107 Fetch get_location Into l_ictran_rec.location;
4108 Close get_location;
4109 ELSE
4110 Open get_mtl_location (l_locator_id, l_inv_item_id);
4111 Fetch get_mtl_location Into l_ictran_rec.location;
4112 Close get_mtl_location;
4113 END IF;
4114 END IF;
4115 --BUG#3503593 Changed CORREET to CORRECT.
4116 IF ( l_rcv_trans_type = 'CORRECT') THEN
4117 GMI_RESERVATION_UTIL.println('rcv type is correct '||l_rcv_trans_type);
4118 IF( l_rcv_qty < 0 ) THEN
4119 --BEGIN BUG#3503593
4120 IF l_ictran_rec.trans_um <> l_uom THEN
4121 l_ictran_rec.trans_qty := l_qty;
4122 ELSE
4123 l_ictran_rec.trans_qty := p_qty1;
4124 END IF;
4125 --END BUG#3503593
4126 l_ictran_rec.trans_qty2 := p_qty2;
4127 END IF;
4128 END IF;
4129
4130 GMI_RESERVATION_UTIL.println('location '||l_ictran_rec.location);
4131 GMI_RESERVATION_UTIL.println('lot_id '||l_ictran_rec.lot_id);
4132 GMI_RESERVATION_UTIL.println('whse_code '||l_ictran_rec.whse_code);
4133 Open get_lot_status(l_ictran_rec.lot_id
4134 , l_ictran_rec.whse_code
4135 , l_ictran_rec.location
4136 , l_ictran_rec.item_id);
4137 Fetch get_lot_status INTO l_ictran_rec.lot_status;
4138 IF get_lot_status%NOTFOUND THEN
4139 l_ictran_rec.lot_status := null;
4140 END IF;
4141 Close get_lot_status;
4142 GMI_RESERVATION_UTIL.println('lot_status '||l_ictran_rec.lot_status);
4143
4144 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
4145 ( p_api_version => 1.0
4146 , p_init_msg_list => FND_API.G_FALSE
4147 , p_commit => FND_API.G_FALSE
4148 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
4149 , p_tran_rec => l_ictran_rec
4150 , x_tran_row => l_tran_row
4151 , x_return_status => x_return_status
4152 , x_msg_count => x_msg_count
4153 , x_msg_data => x_msg_data
4154 );
4155
4156 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4157 GMI_reservation_Util.PrintLn('(opm_dbg) Error in creating transaction for receiving');
4158 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by Create_Pending_Transaction,
4159 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
4160 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
4161 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
4162 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
4163 FND_MESSAGE.Set_Token('WHERE','create_transaction_for_rcv');
4164 FND_MSG_PUB.Add;
4165 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4166 END IF;
4167
4168 l_ictran_rec.trans_id := l_tran_row.trans_id;
4169 GMI_RESERVATION_UTIL.println('trans_id '||l_ictran_rec.trans_id);
4170
4171 GMI_RESERVATION_UTIL.println('after create pending transaction ');
4172 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
4173 (
4174 p_api_version => 1
4175 ,p_init_msg_list => FND_API.G_FALSE
4176 ,p_commit => FND_API.G_FALSE
4177 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
4178 ,p_tran_rec => l_ictran_rec
4179 ,x_tran_row => l_tran_row
4180 ,x_return_status => x_return_status
4181 ,x_msg_count => x_msg_count
4182 ,x_msg_data => x_msg_data
4183 );
4184
4185 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4186 THEN
4187
4188 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by UPDATE_PENDING_TO_COMPLETED,
4189 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
4190 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
4191 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
4192 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED');
4193 FND_MESSAGE.Set_Token('WHERE','create_transaction_for_rcv');
4194 FND_MSG_PUB.Add;
4195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4196 END IF;
4197
4198 EXCEPTION
4199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4200 x_return_status := FND_API.G_RET_STS_ERROR;
4201
4202 /* Get message count and data */
4203 FND_MSG_PUB.count_and_get
4204 ( p_encoded=> FND_API.G_FALSE
4205 , p_count => x_msg_count
4206 , p_data => x_msg_data
4207 );
4208 for l_cnt in 1..x_msg_count loop
4209 l_msg_data := FND_MSG_PUB.GET(l_cnt,'F');
4210 GMI_reservation_Util.PrintLn('for_rcv '||l_msg_data);
4211 end loop;
4212 GMI_reservation_Util.PrintLn('for rcv:EXP Error count='||x_msg_count);
4213
4214 WHEN OTHERS THEN
4215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4216
4217 FND_MSG_PUB.Add_Exc_Msg ( 'RESERVATION_UTIL'
4218 , 'create_trans_for_rcv'
4219 );
4220
4221 /* Get message count and data */
4222 FND_MSG_Pub.Count_and_Get
4223 ( p_count => x_msg_count
4224 , p_data => x_msg_data
4225 );
4226
4227 for l_cnt in 1..x_msg_count loop
4228 l_msg_data := FND_MSG_PUB.GET(l_cnt,'F');
4229 GMI_reservation_Util.PrintLn('for_rcv '||l_msg_data);
4230 end loop;
4231 GMI_reservation_Util.PrintLn('for rcv:EXP Error count='||x_msg_count);
4232
4233 END create_transaction_for_rcv;
4234
4235 Procedure get_OPM_account ( v_dest_org_id IN NUMBER,
4236 v_apps_item_id IN NUMBER,
4237 v_vendor_site_id IN number,
4238 x_cc_id OUT NOCOPY NUMBER,
4239 x_ac_id OUT NOCOPY NUMBER)
4240 IS
4241 l_subinv_type varchar2(25);
4242 l_dest_sub_inv VARCHAR2(25);
4243 l_inv_item_type VARCHAR2(25);
4244 l_asset_subinv NUMBER;
4245 l_account number := NULL;
4246 l_accrual_account number := NULL;
4247 l_vendor_site_id number;
4248 l_asset_item_flag VARCHAR2(1);
4249 l_status varchar2(1);
4250
4251 CURSOR get_asset_flag IS
4252 Select inventory_asset_flag
4253 From mtl_system_items
4254 Where organization_id = v_dest_org_id
4255 and inventory_item_id = v_apps_item_id;
4256
4257 Cursor get_subinv IS
4258 Select whse_code
4259 From ic_whse_mst
4260 Where mtl_organization_id = v_dest_org_id;
4261
4262 CURSOR Get_asset_sub IS
4263 Select asset_inventory
4264 From mtl_secondary_inventories
4265 Where organization_id = v_dest_org_id
4266 and secondary_inventory_name = l_dest_sub_inv ;
4267 BEGIN
4268 GMI_reservation_Util.PrintLn('(opm_dbg) dest_org_id '||v_dest_org_id);
4269 OPEN get_asset_flag;
4270 FETCH get_asset_flag into l_asset_item_flag ;
4271 CLOSE get_asset_flag;
4272 if l_asset_item_flag = 'Y' then
4273 l_inv_item_type := 'ASSET';
4274 else
4275 l_inv_item_type := 'EXPENSE';
4276 end if;
4277
4278 l_dest_sub_inv := null;
4279 Open get_subinv;
4280 Fetch get_subinv Into l_dest_sub_inv;
4281 Close get_subinv;
4282
4283 GMI_reservation_Util.PrintLn('(opm_dbg) inventory_item_id '|| v_apps_item_id);
4284 GMI_reservation_Util.PrintLn('(opm_dbg) inv_item_type '|| l_inv_item_type);
4285 GMI_reservation_Util.PrintLn('(opm_dbg) ventor_site_id '|| v_vendor_site_id);
4286 GMI_reservation_Util.PrintLn('(opm_dbg) dest_sub_inv '|| l_dest_sub_inv);
4287 if l_inv_item_type = 'ASSET' then
4288 if l_dest_sub_inv is not null then
4289 OPEN get_asset_sub;
4290 FETCH get_asset_sub into l_asset_subinv ;
4291 CLOSE get_asset_sub;
4292
4293 if (l_asset_subinv = 1) then
4294 l_subinv_type := 'ASSET' ;
4295 elsif (l_asset_subinv = 2) then
4296 l_subinv_type := 'EXPENSE';
4297 else
4298 l_subinv_type := '';
4299 end if;
4300 else /* Dest. sub inv is null */
4301 l_subinv_type := '';
4302 end if;
4303 else /* l_inv_item_type = 'EXPENSE' */
4304 l_subinv_type := '' ;
4305 end if;
4306
4307 GMI_reservation_Util.PrintLn('(opm_dbg) subinv_type '|| l_subinv_type);
4308 GMI_reservation_Util.PrintLn('(opm_dbg) dest_org_id '|| v_dest_org_id);
4309
4310 if l_inv_item_type = 'ASSET' then
4311 GML_ACCT_GENERATE.generate_opm_acct
4312 ('INVENTORY' ,
4313 l_inv_item_type,
4314 l_subinv_type,
4315 v_dest_org_id,
4316 v_apps_item_id,
4317 v_vendor_site_id,
4318 l_account
4319 );
4320 elsif l_inv_item_type = 'EXPENSE' then
4321 GML_ACCT_GENERATE.generate_opm_acct
4322 ('EXPENSE' ,
4323 '',
4324 '',
4325 v_dest_org_id,
4326 v_apps_item_id,
4327 v_vendor_site_id,
4328 l_account);
4329
4330 end if;
4331 GML_ACCT_GENERATE.generate_opm_acct
4332 ('ACCRUAL' ,
4333 '',
4334 '',
4335 v_dest_org_id,
4336 v_apps_item_id,
4337 v_vendor_site_id,
4338 l_accrual_account);
4339
4340 x_cc_id := l_account;
4341 x_ac_id := l_accrual_account;
4342
4343 END Get_OPM_account;
4344
4345 /* this procedure is created for when booking a so line, a new delviery would be created,
4346 if user has already allocated inv in order pad, here we would update this trans with
4347 the new delivery_detail_id */
4348 Procedure check_OPM_trans_for_so_line
4349 ( p_so_line_id IN NUMBER,
4350 p_new_delivery_detail_id IN NUMBER,
4351 x_return_status OUT NOCOPY VARCHAR2)
4352 IS
4353 l_count number := 0;
4354
4355 BEGIN
4356 x_return_status := FND_API.G_RET_STS_SUCCESS;
4357
4358 Select count(*)
4359 INTO l_count
4360 From ic_tran_pnd
4361 Where line_id = p_so_line_id
4362 and doc_type='OMSO'
4363 and delete_mark=0
4364 and completed_ind=0
4365 and staged_ind=0
4366 and line_detail_id is null
4367 and (lot_id<>0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
4368
4369 --B2523798 Add check for location not equal to default location to
4370 --accomodate for location only items.
4371 IF l_count <> 0 THEN
4372 Update ic_tran_pnd
4373 Set line_detail_id = p_new_delivery_detail_id
4374 Where line_id = p_so_line_id
4375 and doc_type='OMSO'
4376 and delete_mark=0
4377 and completed_ind=0
4378 and staged_ind=0
4379 and line_detail_id is null
4380 and (lot_id<>0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
4381 END IF;
4382 /* call update reservations for gme */
4383 GML_BATCH_OM_RES_PVT.check_gmeres_for_so_line
4384 ( p_so_line_id => p_so_line_id
4385 , p_delivery_detail_id => p_new_delivery_detail_id
4386 , x_return_status => x_return_status
4387 ) ;
4388 END check_OPM_trans_for_so_line;
4389
4390 FUNCTION Get_Opm_converted_qty
4391 (
4392 p_apps_item_id IN NUMBER,
4393 p_organization_id IN NUMBER,
4394 p_apps_from_uom IN VARCHAR2,
4395 p_apps_to_uom IN VARCHAR2,
4396 p_original_qty IN NUMBER,
4397 p_lot_id IN NUMBER DEFAULT 0
4398 ) RETURN NUMBER IS
4399
4400 l_inventory_item_id NUMBER;
4401 l_ic_item_mst_rec GMI_RESERVATION_UTIL.ic_item_mst_rec;
4402 l_return_status VARCHAR2(30);
4403 l_msg_count NUMBER;
4404 l_msg_data VARCHAR2(5);
4405 l_opm_from_uom VARCHAR2(5);
4406 l_opm_to_uom VARCHAR2(5);
4407 l_converted_qty NUMBER;
4408 l_epsilon NUMBER;
4409 n NUMBER;
4410
4411 -- BEGIN Bug 3776538
4412 l_ALLOW_OPM_TRUNCATE_TXN VARCHAR2(4);
4413 l_TRUNCATE_TO_LENGTH CONSTANT INTEGER := 5;
4414 -- END Bug 3776538
4415
4416 BEGIN
4417 l_inventory_item_id := p_apps_item_id;
4418
4419 GMI_reservation_Util.PrintLn('in GMI: Get_OPM_converted_qty ');
4420 GMI_reservation_Util.PrintLn('from uom (APPS) '||p_apps_from_uom);
4421 GMI_reservation_Util.PrintLn('to uom (APPS) '||p_apps_to_uom);
4422
4423 GMI_RESERVATION_UTIL.Get_OPM_Item_From_Apps(
4424 p_organization_id => p_organization_id,
4425 p_inventory_item_id => l_inventory_item_id,
4426 x_ic_item_mst_rec => l_ic_item_mst_rec,
4427 x_return_status => l_return_status,
4428 x_msg_count => l_msg_count,
4429 x_msg_data => l_msg_data);
4430
4431 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4432 RAISE FND_API.G_EXC_ERROR;
4433 END IF;
4434
4435 GMI_RESERVATION_UTIL.Get_OPMUOM_from_AppsUOM(
4436 p_apps_uom =>p_apps_from_uom,
4437 x_opm_uom =>l_opm_from_uom,
4438 x_return_status =>l_return_status,
4439 x_msg_count =>l_msg_count,
4440 x_msg_data =>l_msg_data);
4441 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4442 FND_MESSAGE.Set_Name('GMI','GMI_OPM_UOM_NOT_FOUND');
4443 FND_MESSAGE.Set_Token('APPS_UOM_CODE', p_apps_from_uom);
4444 FND_MSG_PUB.Add;
4445 RAISE FND_API.G_EXC_ERROR;
4446 END IF;
4447 GMI_reservation_Util.PrintLn('from uom (OPM) '||l_opm_from_uom);
4448 GMI_RESERVATION_UTIL.Get_OPMUOM_from_AppsUOM(
4449 p_apps_uom =>p_apps_to_uom,
4450 x_opm_uom =>l_opm_to_uom,
4451 x_return_status =>l_return_status,
4452 x_msg_count =>l_msg_count,
4453 x_msg_data =>l_msg_data);
4454 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4455 FND_MESSAGE.Set_Name('GMI','GMI_OPM_UOM_NOT_FOUND');
4456 FND_MESSAGE.Set_Token('APPS_UOM_CODE', p_apps_to_uom);
4457 FND_MSG_PUB.Add;
4458 RAISE FND_API.G_EXC_ERROR;
4459 END IF;
4460 GMI_reservation_Util.PrintLn('to uom (OPM) '||l_opm_to_uom);
4461
4462 GMICUOM.icuomcv(
4463 pitem_id =>l_ic_item_mst_rec.item_id,
4464 plot_id =>p_lot_id,
4465 pcur_qty =>p_original_qty,
4466 pcur_uom =>l_opm_from_uom,
4467 pnew_uom =>l_opm_to_uom,
4468 onew_qty =>l_converted_qty);
4469 IF l_converted_qty < 0 THEN
4470 GMI_reservation_Util.PrintLn('conversion error code'|| l_converted_qty);
4471 FND_MESSAGE.Set_Name('GMI','GMICUOM.icuomcv');
4472 FND_MESSAGE.Set_Token('CONVERSION_ERROR', p_apps_to_uom);
4473 FND_MSG_PUB.Add;
4474 RAISE FND_API.G_EXC_ERROR;
4475 END IF;
4476
4477 --
4478 -- BUG 3581429 Added the following anonymous block
4479 --
4480 BEGIN
4481 l_epsilon := to_number(NVL(FND_PROFILE.VALUE('IC$EPSILON'),0)) ;
4482 n := (-1) * round(log(10,l_epsilon));
4483 EXCEPTION
4484 WHEN OTHERS THEN
4485 n := 9;
4486 END;
4487
4488 GMI_reservation_Util.PrintLn('converted_qty '|| l_converted_qty);
4489 --
4490 -- Bug 3776538 - See if the converted qty should be trucated rather than rounded!
4491 --
4492 l_ALLOW_OPM_TRUNCATE_TXN := nvl(fnd_profile.value ('ALLOW_OPM_TRUNCATE_TXN'),'N');
4493 GMI_Reservation_Util.PrintLn('Profile: ALLOW_OPM_TRUNCATE_TXN '||l_ALLOW_OPM_TRUNCATE_TXN);
4494 IF (l_ALLOW_OPM_TRUNCATE_TXN = 'Y') THEN
4495 l_converted_qty:=trunc(l_converted_qty, l_TRUNCATE_TO_LENGTH);
4496 GMI_reservation_Util.PrintLn('converted_qty after truncating '|| l_converted_qty);
4497 ELSE
4498 l_converted_qty:=round(l_converted_qty, n);
4499 GMI_reservation_Util.PrintLn('converted_qty after rounding '|| l_converted_qty);
4500 END IF;
4501 -- End 3776538
4502
4503
4504 return l_converted_qty;
4505
4506 END Get_Opm_converted_qty;
4507
4508 Procedure query_staged_flag
4509 ( x_return_status OUT NOCOPY VARCHAR2,
4510 x_msg_count OUT NOCOPY NUMBER,
4511 x_msg_data OUT NOCOPY VARCHAR2,
4512 x_staged_flag OUT NOCOPY VARCHAR2,
4513 p_reservation_id IN NUMBER) IS
4514 l_staged_flag NUMBER;
4515 BEGIN
4516 x_return_status := FND_API.G_RET_STS_SUCCESS;
4517
4518 GMI_reservation_Util.PrintLn('(opm_dbg) trans_id '|| p_reservation_id);
4519 Select staged_ind
4520 Into l_staged_flag
4521 From ic_tran_pnd
4522 Where trans_id = p_reservation_id;
4523
4524 IF l_staged_flag = 1 THEN
4525 x_staged_flag := 'Y';
4526 ELSE
4527 x_staged_flag := 'N';
4528 END IF;
4529 END query_staged_flag;
4530
4531 Procedure find_default_lot
4532 ( x_return_status OUT NOCOPY VARCHAR2,
4533 x_msg_count OUT NOCOPY NUMBER,
4534 x_msg_data OUT NOCOPY VARCHAR2,
4535 x_reservation_id OUT NOCOPY VARCHAR2,
4536 p_line_id IN NUMBER) IS
4537
4538 CURSOR find_trans IS
4539 Select trans_id
4540 From ic_tran_pnd
4541 Where line_id = p_line_id
4542 And doc_type = 'OMSO'
4543 And delete_mark = 0
4544 And completed_ind = 0
4545 And staged_ind = 0
4546 And (lot_id = 0
4547 AND location = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
4548 BEGIN
4549 x_return_status := FND_API.G_RET_STS_SUCCESS;
4550 GMI_reservation_Util.PrintLn('(opm_dbg) in find_default_lot defaultloc '
4551 || GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
4552 GMI_reservation_Util.PrintLn('(opm_dbg) in find_default_lot line_id '|| p_line_id);
4553 Open find_trans;
4554 Fetch find_trans Into x_reservation_id;
4555 IF find_trans%NOTFOUND THEN
4556 x_reservation_id := 0;
4557 END IF;
4558 Close find_trans;
4559
4560 GMI_reservation_Util.PrintLn('(opm_dbg) find_default_lot trans_id '|| x_reservation_id);
4561 END find_default_lot;
4562
4563 procedure check_lot_loct_ctl (
4564 p_inventory_item_id IN NUMBER
4565 ,p_mtl_organization_id IN NUMBER
4566 ,x_ctl_ind OUT NOCOPY VARCHAR2)
4567 IS
4568 l_item_id ic_tran_pnd.item_id%TYPE;
4569 l_lot_ctl NUMBER;
4570 l_loct_ctl NUMBER;
4571 l_whse_ctl NUMBER;
4572 l_inventory_item_id NUMBER;
4573 l_organization_id NUMBER;
4574 l_whse_code VARCHAR2(5);
4575
4576 Cursor Get_item_info IS
4577 SELECT iim.item_id, iim.lot_ctl, iim.loct_ctl
4578 FROM ic_item_mst iim,
4579 mtl_system_items msi
4580 WHERE msi.inventory_item_id = p_inventory_item_id
4581 AND msi.organization_id = p_mtl_organization_id
4582 AND msi.segment1 = iim.item_no;
4583
4584
4585 BEGIN
4586 /* get lot_ctl and loct_ctl */
4587 GMI_reservation_Util.PrintLn('check_lot_loct_ctl for item_id '|| p_inventory_item_id
4588 ||' for org '||p_mtl_organization_id);
4589 Open get_item_info;
4590 Fetch get_item_info
4591 Into l_item_id
4592 , l_lot_ctl
4593 , l_loct_ctl
4594 ;
4595 Close get_item_info;
4596
4597 /* get whse loct_ctl */
4598 Select loct_ctl
4599 Into l_whse_ctl
4600 From ic_whse_mst
4601 Where mtl_organization_id = p_mtl_organization_id;
4602
4603 IF l_lot_ctl = 0 AND (l_loct_ctl * l_whse_ctl) = 0 THEN
4604 x_ctl_ind := 'N';
4605 ElSE
4606 x_ctl_ind := 'Y';
4607 END IF;
4608 GMI_reservation_Util.PrintLn('check_lot_loct_ctl returning '|| x_ctl_ind);
4609 End check_lot_loct_ctl;
4610
4611 /* this procedure is called by OM for the order lines which are not interfaced with shipping
4612 -- not yet booked :-...
4613 */
4614 PROCEDURE split_trans_from_om
4615 ( p_old_source_line_id IN NUMBER,
4616 p_new_source_line_id IN NUMBER,
4617 p_qty_to_split IN NUMBER, -- remaining qty to the old line_id
4618 p_qty2_to_split IN NUMBER, -- remaining qty2 to the old line_id
4619 x_return_status OUT NOCOPY VARCHAR2,
4620 x_msg_count OUT NOCOPY NUMBER,
4621 x_msg_data OUT NOCOPY VARCHAR2)
4622 IS
4623 l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
4624 l_new_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
4625 l_old_transaction_row ic_tran_pnd%ROWTYPE ;
4626 l_new_transaction_row ic_tran_pnd%ROWTYPE;
4627 l_trans_id ic_tran_pnd.trans_id%TYPE;
4628 l_new_trans_id ic_tran_pnd.trans_id%TYPE;
4629 l_item_id ic_tran_pnd.item_id%TYPE;
4630 l_location ic_tran_pnd.location%TYPE;
4631 l_lot_id ic_tran_pnd.lot_id%TYPE;
4632 l_line_detail_id wsh_delivery_details.delivery_detail_id%TYPE;
4633 l_new_delivery_detail_id NUMBER;
4634 l_source_line_id NUMBER;
4635 l_fulfilled_qty NUMBER;
4636 l_qty_to_fulfil NUMBER;
4637 l_qty2_to_fulfil NUMBER;
4638 l_orig_qty NUMBER;
4639 l_orig_qty2 NUMBER;
4640 l_lot_ctl NUMBER;
4641 l_loct_ctl NUMBER;
4642 l_whse_ctl NUMBER;
4643 l_doc_id NUMBER;
4644 l_inventory_item_id NUMBER;
4645 l_organization_id NUMBER;
4646 l_whse_code VARCHAR2(5);
4647 l_released_status VARCHAR2(5);
4648
4649 cursor c_reservations IS
4650 SELECT trans_id, doc_id
4651 FROM ic_tran_pnd
4652 WHERE line_id = p_old_source_line_id
4653 AND delete_mark = 0
4654 AND doc_type = 'OMSO'
4655 AND trans_qty <> 0
4656 And (lot_id <> 0 -- only real trans
4657 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
4658 ORDER BY trans_qty desc; /* the smaller qty is at the top, keep in mind it is neg */
4659 /* or should consider the alloc rules */
4660 cursor c_reserved_qty IS
4661 SELECT abs(sum(trans_qty)),abs(sum(trans_qty2))
4662 FROM ic_tran_pnd
4663 WHERE line_id = p_old_source_line_id
4664 AND delete_mark = 0
4665 AND completed_ind = 0
4666 AND staged_ind = 0
4667 AND doc_type = 'OMSO'
4668 AND trans_qty <> 0;
4669
4670 Cursor Get_item_info IS
4671 Select ic.item_id
4672 , ic.lot_ctl
4673 , ic.loct_ctl
4674 From ic_item_mst ic
4675 , mtl_system_items mtl
4676 Where ic.item_no = mtl.segment1
4677 and mtl.inventory_item_id = l_inventory_item_id
4678 and mtl.organization_id = l_organization_id;
4679
4680 /* Begin bug 2871929 */
4681 Cursor c_order_line_info IS
4682 SELECT inventory_item_id, Ship_from_org_id
4683 FROM oe_order_lines_all
4684 WHERE line_id = p_old_source_line_id;
4685 /* End bug 2871929 */
4686
4687 BEGIN
4688 GMI_RESERVATION_UTIL.Println('in split_trans_from OM where order has not been booked');
4689 GMI_RESERVATION_UTIL.Println(' p_old_source_line_id '||p_old_source_line_id);
4690 GMI_RESERVATION_UTIL.Println(' p_new_source_line_id '||p_new_source_line_id);
4691 l_fulfilled_qty := 0;
4692
4693 /* Begin bug 2871929 */
4694 /* Variables l_item_id, l_organization_id used in subsequent
4695 cursors were NOT INITIALIAZED at all */
4696
4697 Open c_order_line_info;
4698 Fetch c_order_line_info INTO l_item_id,l_organization_id;
4699 Close c_order_line_info;
4700 /* End bug 2871929 */
4701
4702 GMI_RESERVATION_UTIL.Println('l_organization_id '||l_organization_id);
4703
4704
4705 /* get lot_ctl and loct_ctl */
4706 Open get_item_info;
4707 Fetch get_item_info
4708 Into l_item_id
4709 , l_lot_ctl
4710 , l_loct_ctl
4711 ;
4712 Close get_item_info;
4713
4714 Open c_reserved_qty;
4715 Fetch c_reserved_qty
4716 Into l_orig_qty
4717 , l_orig_qty2
4718 ;
4719 Close c_reserved_qty;
4720 /* get whse loct_ctl */
4721 Select loct_ctl
4722 Into l_whse_ctl
4723 From ic_whse_mst
4724 Where mtl_organization_id = l_organization_id;
4725
4726 l_qty_to_fulfil := p_qty_to_split;
4727 l_qty2_to_fulfil := p_qty2_to_split;
4728
4729 GMI_RESERVATION_UTIL.Println('in split_trans, qty to split'||p_qty_to_split);
4730 GMI_RESERVATION_UTIL.Println('in split_trans, qty2 to split'||p_qty2_to_split);
4731 oe_debug_pub.add('Going to find default lot in split_reservation',2);
4732 GMI_RESERVATION_UTIL.find_default_lot
4733 ( x_return_status => x_return_status,
4734 x_msg_count => x_msg_count,
4735 x_msg_data => x_msg_data,
4736 x_reservation_id => l_trans_id,
4737 p_line_id => p_old_source_line_id
4738 );
4739 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4740 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
4741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4742 END IF;
4743 IF l_trans_id > 0 THEN
4744 oe_debug_pub.add('Going to find default lot in for new line ',2);
4745 GMI_RESERVATION_UTIL.find_default_lot
4746 ( x_return_status => x_return_status,
4747 x_msg_count => x_msg_count,
4748 x_msg_data => x_msg_data,
4749 x_reservation_id => l_new_trans_id,
4750 p_line_id => p_new_source_line_id
4751 );
4752 IF nvl(l_new_trans_id, 0) = 0 THEN -- B2985470 changed <> to =
4753 /* if not exist, create a default trans for the new line_id */
4754 /* this would be just a place holder where trans_qty would be 0 */
4755 /* trans qty would be udpated when balance default lot is called */
4756 l_old_transaction_rec.trans_id := l_trans_id;
4757 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
4758 (l_old_transaction_rec, l_old_transaction_rec )
4759 THEN
4760 l_new_transaction_rec := l_old_transaction_rec;
4761 l_new_transaction_rec.trans_id := NULL;
4762 l_new_transaction_rec.trans_qty := 0;
4763 l_new_transaction_rec.trans_qty2 := 0;
4764 l_new_transaction_rec.line_id := p_new_source_line_id;
4765
4766 GMI_RESERVATION_UTIL.PrintLn('creating the default trans for the new line');
4767 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
4768 ( p_api_version => 1
4769 , p_init_msg_list => FND_API.G_FALSE
4770 , p_commit => FND_API.G_FALSE
4771 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
4772 , p_tran_rec => l_new_transaction_rec
4773 , x_tran_row => l_new_transaction_row
4774 , x_return_status => x_return_status
4775 , x_msg_count => x_msg_count
4776 , x_msg_data => x_msg_data
4777 );
4778
4779 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4780 THEN
4781 GMI_reservation_Util.PrintLn('(opm_dbg) Error in creating default transaction ');
4782 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by Create_Pending_Transaction,
4783 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
4784 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
4785 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
4786 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
4787 FND_MESSAGE.Set_Token('WHERE','split_trans_from_om');
4788 FND_MSG_PUB.Add;
4789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4790 END IF;
4791 END IF;
4792 END IF;
4793 END IF;
4794
4795 OPEN c_reservations;
4796 LOOP
4797 FETCH c_reservations INTO l_trans_id, l_doc_id;
4798 EXIT WHEN c_reservations%NOTFOUND;
4799
4800 l_old_transaction_rec.trans_id := l_trans_id;
4801
4802 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
4803 (l_old_transaction_rec, l_old_transaction_rec )
4804 THEN
4805 GMI_RESERVATION_UTIL.Println('got trans for trans_id '||l_trans_id);
4806 GMI_RESERVATION_UTIL.Println('l_qty_to_fulfil '||l_qty_to_fulfil);
4807 GMI_RESERVATION_UTIL.Println('l_qty2_to_fulfil '||l_qty2_to_fulfil);
4808 IF abs(l_old_transaction_rec.trans_qty) <= l_qty_to_fulfil THEN
4809 /* do nothing for the tran */
4810 GMI_RESERVATION_UTIL.Println('in split_trans, keep trans the same for trans_id '||l_trans_id);
4811 GMI_RESERVATION_UTIL.Println('in split_trans, trans_qty '||l_old_transaction_rec.trans_qty);
4812 l_qty_to_fulfil := l_qty_to_fulfil - abs(l_old_transaction_rec.trans_qty);
4813 l_qty2_to_fulfil := l_qty2_to_fulfil - abs(l_old_transaction_rec.trans_qty2);
4814 ELSIF abs(l_old_transaction_rec.trans_qty) > l_qty_to_fulfil
4815 AND l_qty_to_fulfil > 0 THEN
4816
4817 update ic_tran_pnd
4818 set trans_qty = -1 * l_qty_to_fulfil
4819 , trans_qty2 = -1 * l_qty2_to_fulfil
4820 Where trans_id = l_trans_id;
4821
4822 /* create a new trans for the new wdd, and new line_id if applicable */
4823 l_new_transaction_rec := l_old_transaction_rec;
4824 l_new_transaction_rec.trans_id := NULL;
4825 l_new_transaction_rec.trans_qty := -1 * (abs(l_new_transaction_rec.trans_qty)
4826 - l_qty_to_fulfil);
4827 l_new_transaction_rec.trans_qty2 := -1 * (abs(l_new_transaction_rec.trans_qty2)
4828 - l_qty2_to_fulfil);
4829 l_new_transaction_rec.line_id := p_new_source_line_id;
4830
4831 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
4832 ( p_api_version => 1
4833 , p_init_msg_list => FND_API.G_FALSE
4834 , p_commit => FND_API.G_FALSE
4835 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
4836 , p_tran_rec => l_new_transaction_rec
4837 , x_tran_row => l_new_transaction_row
4838 , x_return_status => x_return_status
4839 , x_msg_count => x_msg_count
4840 , x_msg_data => x_msg_data
4841 );
4842
4843 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4844 THEN
4845 GMI_reservation_Util.PrintLn('(opm_dbg) Error in creating transaction ');
4846 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by Create_Pending_Transaction,
4847 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
4848 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
4849 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
4850 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
4851 FND_MESSAGE.Set_Token('WHERE','split_trans_from_om');
4852 FND_MSG_PUB.Add;
4853
4854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4855 END IF;
4856 /* no more fulfilment */
4857 l_qty_to_fulfil := 0;
4858 l_qty2_to_fulfil := 0;
4859 ELSIF l_qty_to_fulfil <= 0 THEN
4860 /* do nothing */
4861 null;
4862 END IF;
4863 END IF;
4864 END LOOP;
4865 CLOSE c_reservations;
4866 /* need to balance default lot for both new sol and old sol */
4867 GMI_RESERVATION_UTIL.find_default_lot
4868 ( x_return_status => x_return_status,
4869 x_msg_count => x_msg_count,
4870 x_msg_data => x_msg_data,
4871 x_reservation_id => l_trans_id,
4872 p_line_id => p_old_source_line_id
4873 );
4874 IF l_trans_id > 0 THEN -- if it does not exist, don't bother
4875 l_old_transaction_rec.trans_id := l_trans_id;
4876
4877 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
4878 (l_old_transaction_rec, l_old_transaction_rec )
4879 THEN
4880 GMI_RESERVATION_UTIL.PrintLn('balancing default lot for old source line_id '|| p_old_source_line_id);
4881 GMI_RESERVATION_UTIL.balance_default_lot
4882 ( p_ic_default_rec => l_old_transaction_rec
4883 , p_opm_item_id => l_old_transaction_rec.item_id
4884 , x_return_status => x_return_status
4885 , x_msg_count => x_msg_count
4886 , x_msg_data => x_msg_data
4887 );
4888 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4889 THEN
4890 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
4891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4892 END IF;
4893 END IF;
4894 END IF;
4895 GMI_RESERVATION_UTIL.find_default_lot
4896 ( x_return_status => x_return_status,
4897 x_msg_count => x_msg_count,
4898 x_msg_data => x_msg_data,
4899 x_reservation_id => l_trans_id,
4900 p_line_id => p_new_source_line_id
4901 );
4902 IF l_trans_id > 0 AND p_new_source_line_id <> p_old_source_line_id
4903 THEN -- if it does not exist, don't bother
4904 l_old_transaction_rec.trans_id := l_trans_id;
4905
4906 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
4907 (l_old_transaction_rec, l_old_transaction_rec )
4908 THEN
4909 GMI_RESERVATION_UTIL.PrintLn('balancing default lot for new source line_id '|| p_new_source_line_id);
4910 GMI_RESERVATION_UTIL.balance_default_lot
4911 ( p_ic_default_rec => l_old_transaction_rec
4912 , p_opm_item_id => l_old_transaction_rec.item_id
4913 , x_return_status => x_return_status
4914 , x_msg_count => x_msg_count
4915 , x_msg_data => x_msg_data
4916 );
4917 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4918 THEN
4919 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
4920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4921 END IF;
4922 END IF;
4923 END IF;
4924 /* split the reservations if necessary */
4925 GML_BATCH_OM_RES_PVT.split_reservations_from_om
4926 ( p_old_source_line_id => p_old_source_line_id
4927 ,p_new_source_line_id => p_new_source_line_id
4928 ,p_qty_to_split => p_qty_to_split
4929 ,p_qty2_to_split => p_qty2_to_split
4930 ,p_orig_qty => l_orig_qty
4931 ,p_orig_qty2 => l_orig_qty
4932 ,x_return_status => x_return_status
4933 ,x_msg_count => x_msg_count
4934 ,x_msg_data => x_msg_data
4935 );
4936
4937 GMI_RESERVATION_UTIL.PrintLn('Exit split_trans_from_OM');
4938 END split_trans_from_om;
4939
4940 -- HW OPM BUG#:2536589 New procedure
4941 PROCEDURE update_opm_trxns(
4942 p_trans_id IN NUMBER,
4943 p_inventory_item_id IN NUMBER,
4944 p_organization_id IN NUMBER,
4945 x_return_status OUT NOCOPY VARCHAR2,
4946 x_msg_count OUT NOCOPY NUMBER,
4947 x_msg_data OUT NOCOPY VARCHAR2)
4948
4949 IS
4950
4951 l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
4952 l_old_transaction_row ic_tran_pnd%ROWTYPE;
4953
4954 -- HW cursor for cntl items
4955 CURSOR get_opm_txn_cntl (p_trans_id NUMBER) IS
4956 SELECT IC.trans_id
4957
4958 FROM IC_TRAN_PND IC
4959 WHERE IC.trans_id = p_trans_id
4960 AND IC.DOC_TYPE='OMSO'
4961 AND IC.DELETE_MARK =0
4962 AND IC.COMPLETED_IND =0
4963 AND IC.STAGED_IND = 0
4964 AND ( IC.LOT_ID <> 0 OR
4965 IC.LOCATION <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
4966
4967 -- Cursor for non-ctl items
4968 CURSOR get_opm_txn_non_cntl (p_trans_id NUMBER) IS
4969 SELECT IC.trans_id
4970
4971 FROM IC_TRAN_PND IC
4972 WHERE IC.trans_id = p_trans_id
4973 AND IC.DOC_TYPE='OMSO'
4974 AND IC.DELETE_MARK =0
4975 AND IC.COMPLETED_IND =0
4976 AND IC.STAGED_IND = 0
4977 AND ( IC.LOT_ID = 0 OR
4978 IC.LOCATION = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
4979
4980
4981
4982 l_ctl_ind VARCHAR2(1);
4983 BEGIN
4984
4985 x_return_status := FND_API.G_RET_STS_SUCCESS;
4986
4987
4988 /* see if item is a ctl item */
4989 GMI_RESERVATION_UTIL.check_lot_loct_ctl
4990 ( p_inventory_item_id => p_inventory_item_id
4991 ,p_mtl_organization_id => p_organization_id
4992 ,x_ctl_ind => l_ctl_ind
4993 );
4994
4995
4996 IF l_ctl_ind = 'Y' THEN -- control/location items
4997 gmi_reservation_util.println('Going to open cursor for lot/location items');
4998 OPEN get_opm_txn_cntl(p_trans_id);
4999 FETCH get_opm_txn_cntl into l_old_transaction_rec.trans_id;
5000 IF get_opm_txn_cntl%NOTFOUND THEN
5001 CLOSE get_opm_txn_cntl;
5002 gmi_reservation_util.println('Failed to fetch trans_id in update_opm_trxns for lot/location item= '||p_trans_id);
5003 x_return_status := FND_API.G_RET_STS_ERROR;
5004 RETURN;
5005 END IF;
5006 CLOSE get_opm_txn_cntl;
5007
5008 ELSE -- non ctl
5009 gmi_reservation_util.println('Going to open cursor for non-lot/non location');
5010 OPEN get_opm_txn_non_cntl(p_trans_id);
5011 FETCH get_opm_txn_non_cntl into l_old_transaction_rec.trans_id;
5012 IF get_opm_txn_non_cntl%NOTFOUND THEN
5013 CLOSE get_opm_txn_non_cntl;
5014 gmi_reservation_util.println('Failed to fetch trans_id in update_opm_trxns for non-lot/non location= '||p_trans_id);
5015 x_return_status := FND_API.G_RET_STS_ERROR;
5016 RETURN;
5017 END IF;
5018 CLOSE get_opm_txn_non_cntl;
5019 END IF;
5020
5021 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
5022 (l_old_transaction_rec, l_old_transaction_rec ) THEN
5023
5024 -- Update staged_ind
5025 l_old_transaction_rec.staged_ind :=1;
5026 -- Make the Bill-To transactions unique so subledger can identify them since
5027 -- there are no records in wsh_delivery_details
5028 l_old_transaction_rec.line_detail_id := -999;
5029 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
5030 (
5031 p_api_version => 1
5032 ,p_init_msg_list => FND_API.G_FALSE
5033 ,p_commit => FND_API.G_FALSE
5034 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
5035 ,p_tran_rec => l_old_transaction_rec
5036 ,x_tran_row => l_old_transaction_row
5037 ,x_return_status => x_return_status
5038 ,x_msg_count => x_msg_count
5039 ,x_msg_data => x_msg_data
5040 );
5041
5042 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5043 GMI_reservation_Util.PrintLn('(opm_dbg) Error in updating transaction ');
5044 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by UPDATE_PENDING_TO_COMPLETED,
5045 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
5046 GMI_reservation_Util.PrintLn('Error Message '|| x_msg_data);
5047 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
5048 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED');
5049 FND_MESSAGE.Set_Token('WHERE','update_opm_trxns');
5050 FND_MSG_PUB.Add;
5051 x_return_status := FND_API.G_RET_STS_ERROR;
5052 RETURN;
5053 END IF; -- of update
5054
5055 ELSE -- of fetching trxn
5056 gmi_reservation_util.println('Failed to fetch opm trxn in update_opm_trxns for trans_id:'||p_trans_id);
5057 x_return_status := FND_API.G_RET_STS_ERROR;
5058 RETURN;
5059 END IF; -- of fetching OPM trx
5060
5061 gmi_reservation_util.println('Done upating ic_tran_pnd in update_opm_trxns.');
5062
5063 END update_opm_trxns;
5064
5065 -- HW OPM BUG#:2536589 New procedure
5066 PROCEDURE find_lot_id (
5067 p_trans_id IN NUMBER,
5068 x_return_status OUT NOCOPY VARCHAR2,
5069 x_msg_count OUT NOCOPY NUMBER,
5070 x_msg_data OUT NOCOPY VARCHAR2)
5071
5072 IS
5073
5074 -- Cursor to fetch all lots for items that are lot control
5075 CURSOR lot_info IS
5076 SELECT IC.LOT_ID
5077 FROM IC_TRAN_PND IC
5078 WHERE IC.TRANS_ID = P_TRANS_ID
5079 AND IC.DOC_TYPE='OMSO'
5080 AND IC.DELETE_MARK =0
5081 AND IC.COMPLETED_IND =0
5082 AND IC.STAGED_IND = 0
5083 AND IC.LOT_ID <> 0;
5084
5085 x_lot_id NUMBER;
5086
5087 BEGIN
5088
5089 x_return_status := FND_API.G_RET_STS_SUCCESS;
5090 gmi_reservation_util.println('In GMI_reservation_util.find_lot_id');
5091 OPEN lot_info ;
5092 FETCH lot_info into x_lot_id ;
5093 IF lot_info%NOTFOUND THEN
5094 CLOSE lot_info ;
5095 gmi_reservation_util.println('Failed to fetch lot_id in in find_lot_id for trans _id= '||p_trans_id);
5096 x_return_status := FND_API.G_RET_STS_ERROR;
5097 RETURN;
5098 END IF;
5099
5100 CLOSE lot_info;
5101
5102 gmi_reservation_util.println('Done fetching lot_id in find_lot_id');
5103
5104 END find_lot_id ;
5105
5106
5107 -- HW OPM -added for Harmonization project for WSH.I
5108 PROCEDURE validate_lot_number (
5109 p_inventory_item_id IN NUMBER,
5110 p_organization_id IN NUMBER,
5111 p_lot_number IN VARCHAR2,
5112 x_return_status OUT NOCOPY VARCHAR2) IS
5113
5114
5115 l_return_status VARCHAR2(1);
5116 l_okay BOOLEAN;
5117 l_ic_item_mst_rec GMI_RESERVATION_UTIL.ic_item_mst_rec;
5118 l_msg_data VARCHAR2(2000);
5119 l_msg_count NUMBER;
5120 opm_item_id NUMBER;
5121 l_lot_number VARCHAR2(32); -- Bug 3598280 - Made the variable varchar2(32) from varchar2(4)
5122 x_msg_count NUMBER;
5123 x_msg_data VARCHAR2(1000);
5124 l_api_name CONSTANT VARCHAR2 (30) := 'validate_lot_number';
5125
5126 CURSOR get_lot_no (opm_item_id IN NUMBER) IS
5127 SELECT lot_no
5128 FROM IC_LOTS_MST
5129 WHERE item_id = opm_item_id
5130 AND lot_no = p_lot_number ;
5131
5132 BEGIN
5133
5134 -- Get OPM item information
5135 x_return_status := FND_API.G_RET_STS_SUCCESS;
5136 GMI_RESERVATION_UTIL.Get_OPM_Item_From_Apps(
5137 p_organization_id =>p_organization_id,
5138 p_inventory_item_id => p_inventory_item_id,
5139 x_ic_item_mst_rec => l_ic_item_mst_rec,
5140 x_return_status => l_return_status,
5141 x_msg_count => l_msg_count,
5142 x_msg_data => l_msg_data);
5143
5144 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5145 raise FND_API.G_EXC_ERROR;
5146 RETURN;
5147 END IF;
5148
5149 opm_item_id := l_ic_item_mst_rec.item_id ;
5150
5151 OPEN get_lot_no(opm_item_id);
5152 FETCH get_lot_no into l_lot_number;
5153 IF get_lot_no%NOTFOUND THEN
5154 CLOSE get_lot_no ;
5155 gmi_reservation_util.println('Failed to fetch lot_no in validate_lot_no');
5156 x_return_status := FND_API.G_RET_STS_ERROR;
5157 RETURN;
5158 END IF;
5159
5160 CLOSE get_lot_no;
5161
5162 x_return_status := FND_API.G_RET_STS_SUCCESS;
5163
5164
5165 EXCEPTION
5166 WHEN FND_API.G_EXC_ERROR THEN
5167
5168 x_return_status := FND_API.G_RET_STS_ERROR;
5169
5170 FND_MSG_Pub.count_and_get
5171 ( p_count => x_msg_count
5172 , p_data => x_msg_data
5173 );
5174
5175 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Exp_Error ');
5176
5177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5179 FND_MSG_PUB.Count_And_Get
5180 (
5181 p_count => x_msg_count,
5182 p_data => x_msg_data,
5183 p_encoded => FND_API.G_FALSE
5184 );
5185
5186 WHEN OTHERS THEN
5187 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Error in Select='||SQLCODE||'.');
5188 x_return_status := SQLCODE;
5189
5190 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
5191 , l_api_name
5192 );
5193
5194 /* Get message count and data */
5195 FND_MSG_Pub.count_and_get
5196 ( p_count => x_msg_count
5197 , p_data => x_msg_data
5198 );
5199
5200 END validate_lot_number;
5201
5202 -- HW OPM -added for Harmonization project for WSH.I
5203 -- This procedure will be called from the shipping transaction form
5204 -- from the action procedure when user requests a split.
5205 -- HW BUG#:2654963 Added p_delivery_detail_id to proc.
5206 PROCEDURE line_allocated (
5207 p_inventory_item_id IN NUMBER,
5208 p_organization_id IN NUMBER,
5209 p_line_id IN NUMBER,
5210 p_delivery_detail_id IN NUMBER DEFAULT NULL,
5211 check_status OUT NOCOPY NUMBER,
5212 x_return_status OUT NOCOPY VARCHAR2)
5213
5214 IS
5215
5216 -- HW cursor for ctl_items
5217 CURSOR get_opm_txn_cntl IS
5218 SELECT COUNT(1)
5219
5220 FROM IC_TRAN_PND IC
5221 WHERE IC.LINE_ID = p_line_id
5222 AND IC.line_detail_id=p_delivery_detail_id
5223 AND IC.DOC_TYPE='OMSO'
5224 AND IC.DELETE_MARK =0
5225 AND IC.COMPLETED_IND =0
5226 AND ( IC.LOT_ID <> 0 OR
5227 IC.LOCATION <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
5228
5229 -- Cursor for non-ctl items
5230 CURSOR get_opm_txn_non_cntl IS
5231 SELECT COUNT(1)
5232
5233 FROM IC_TRAN_PND IC
5234 WHERE IC.trans_id = p_line_id
5235 AND IC.line_detail_id=p_delivery_detail_id
5236 AND IC.DOC_TYPE='OMSO'
5237 AND IC.DELETE_MARK =0
5238 AND IC.COMPLETED_IND =0
5239 AND ( IC.LOT_ID = 0 OR
5240 IC.LOCATION = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
5241
5242
5243 l_ctl_ind VARCHAR2(1);
5244
5245 l_lot_id NUMBER;
5246 l_whse_code VARCHAR2(5);
5247 l_location VARCHAR(20);
5248 l_count NUMBER;
5249
5250 x_msg_count NUMBER;
5251 x_msg_data VARCHAR2(1000);
5252 l_api_name CONSTANT VARCHAR2 (30) := 'line_allocated';
5253
5254 BEGIN
5255
5256 x_return_status := FND_API.G_RET_STS_SUCCESS;
5257
5258
5259 /* see if item is a ctl item */
5260 GMI_RESERVATION_UTIL.check_lot_loct_ctl
5261 ( p_inventory_item_id => p_inventory_item_id
5262 ,p_mtl_organization_id => p_organization_id
5263 ,x_ctl_ind => l_ctl_ind
5264 );
5265
5266
5267 IF l_ctl_ind = 'Y' THEN -- control/location items
5268 gmi_reservation_util.println('Going to open cursor for lot/location items');
5269 OPEN get_opm_txn_cntl;
5270 FETCH get_opm_txn_cntl into l_count;
5271 IF ( get_opm_txn_cntl%NOTFOUND OR l_count = 0 ) THEN
5272 check_status := 0; -- No allocation
5273 ELSE
5274 check_status :=1; -- Line is allocated
5275 END IF;
5276
5277 CLOSE get_opm_txn_cntl;
5278
5279 ELSE -- non ctl
5280 gmi_reservation_util.println('Going to open cursor for non-lot/non location');
5281 OPEN get_opm_txn_non_cntl;
5282 FETCH get_opm_txn_non_cntl into l_count;
5283 IF ( get_opm_txn_non_cntl%NOTFOUND OR l_count = 0 ) THEN
5284 check_status := 0; -- No allocation
5285 ELSE
5286 check_status :=1; -- Line is allocated
5287 END IF;
5288
5289 CLOSE get_opm_txn_non_cntl;
5290
5291 END IF;
5292
5293
5294 EXCEPTION
5295 WHEN FND_API.G_EXC_ERROR THEN
5296
5297 x_return_status := FND_API.G_RET_STS_ERROR;
5298
5299 FND_MSG_Pub.count_and_get
5300 ( p_count => x_msg_count
5301 , p_data => x_msg_data
5302 );
5303
5304 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Exp_Error ');
5305
5306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5308 FND_MSG_PUB.Count_And_Get
5309 (
5310 p_count => x_msg_count,
5311 p_data => x_msg_data,
5312 p_encoded => FND_API.G_FALSE
5313 );
5314
5315 WHEN OTHERS THEN
5316 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Error in Select='||SQLCODE||'.');
5317 x_return_status := SQLCODE;
5318
5319 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
5320 , l_api_name
5321 );
5322
5323 /* Get message count and data */
5324 FND_MSG_Pub.count_and_get
5325 ( p_count => x_msg_count
5326 , p_data => x_msg_data
5327 );
5328
5329 END line_allocated;
5330
5331
5332 -- HW Added for bug#2677054 - WSH.I project
5333 -- This rotine will be called from WSH Group API
5334 -- WSH_DELIVERY_DETAILS_GRP.Delivery_Detail_Action
5335 -- to check if line is allocated to lot_indivisble or not
5336
5337
5338 PROCEDURE is_line_allocated (
5339 p_inventory_item_id IN NUMBER,
5340 p_organization_id IN NUMBER,
5341 p_delivery_detail_id IN NUMBER DEFAULT NULL,
5342 check_status OUT NOCOPY NUMBER,
5343 x_return_status OUT NOCOPY VARCHAR2)
5344 IS
5345
5346
5347 -- HW cursor for ctl_items
5348 CURSOR get_opm_txn_cntl IS
5349 SELECT COUNT(1)
5350
5351 FROM IC_TRAN_PND IC
5352 WHERE IC.line_detail_id=p_delivery_detail_id
5353 AND IC.DOC_TYPE='OMSO'
5354 AND IC.DELETE_MARK =0
5355 AND IC.COMPLETED_IND =0
5356 AND ( IC.LOT_ID <> 0 OR
5357 IC.LOCATION <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
5358
5359 -- Cursor for non-ctl items
5360 CURSOR get_opm_txn_non_cntl IS
5361 SELECT COUNT(1)
5362
5363 FROM IC_TRAN_PND IC
5364 WHERE IC.line_detail_id=p_delivery_detail_id
5365 AND IC.DOC_TYPE='OMSO'
5366 AND IC.DELETE_MARK =0
5367 AND IC.COMPLETED_IND =0
5368 AND ( IC.LOT_ID = 0 OR
5369 IC.LOCATION = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
5370
5371
5372 l_ctl_ind VARCHAR2(1);
5373
5374 l_lot_id NUMBER;
5375 l_whse_code VARCHAR2(5);
5376 l_location VARCHAR(20);
5377 l_count NUMBER;
5378 l_ic_item_mst_rec GMI_Reservation_Util.ic_item_mst_rec;
5379 x_msg_count NUMBER;
5380 x_msg_data VARCHAR2(1000);
5381 l_api_name CONSTANT VARCHAR2 (30) := 'is_line_allocated';
5382 return_status VARCHAR2(10);
5383
5384 BEGIN
5385
5386 x_return_status := FND_API.G_RET_STS_SUCCESS;
5387
5388 gmi_reservation_util.println('In procedure is_line_allocated');
5389
5390 -- Get item info
5391
5392 Get_OPM_item_from_Apps(
5393 p_organization_id => p_organization_id
5394 , p_inventory_item_id => p_inventory_item_id
5395 , x_ic_item_mst_rec => l_ic_item_mst_rec
5396 , x_return_status => return_status
5397 , x_msg_count => x_msg_count
5398 , x_msg_data => x_msg_data);
5399
5400 IF (return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5401 FND_MESSAGE.Set_Name('GMI','GMI_OPM_ITEM');
5402 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
5403 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
5404 FND_MSG_PUB.Add;
5405 raise FND_API.G_EXC_ERROR;
5406 END IF;
5407
5408 IF ( l_ic_item_mst_rec.lot_indivisible = 1 ) THEN
5409 /* see if item is a ctl item */
5410 GMI_RESERVATION_UTIL.check_lot_loct_ctl
5411 ( p_inventory_item_id => p_inventory_item_id
5412 ,p_mtl_organization_id => p_organization_id
5413 ,x_ctl_ind => l_ctl_ind
5414 );
5415
5416
5417 IF l_ctl_ind = 'Y' THEN -- control/location items
5418 gmi_reservation_util.println('Going to open cursor for lot/location items');
5419 OPEN get_opm_txn_cntl;
5420 FETCH get_opm_txn_cntl into l_count;
5421 IF ( get_opm_txn_cntl%NOTFOUND OR l_count = 0 ) THEN
5422 check_status := 0; -- No allocation
5423 ELSE
5424 check_status :=1; -- Line is allocated
5425 END IF;
5426
5427 CLOSE get_opm_txn_cntl;
5428
5429 ELSE -- non ctl
5430 gmi_reservation_util.println('Going to open cursor for non-lot/non location');
5431 OPEN get_opm_txn_non_cntl;
5432 FETCH get_opm_txn_non_cntl into l_count;
5433 IF ( get_opm_txn_non_cntl%NOTFOUND OR l_count = 0 ) THEN
5434 check_status := 0; -- No allocation
5435 ELSE
5436 check_status :=1; -- Line is allocated
5437 END IF;
5438
5439 CLOSE get_opm_txn_non_cntl;
5440
5441 END IF; -- of ctl_item
5442
5443 ELSE
5444 gmi_reservation_util.println('Not lot indivisible');
5445 check_status :=0;
5446 END IF; -- of lot_ind
5447
5448
5449 EXCEPTION
5450 WHEN FND_API.G_EXC_ERROR THEN
5451
5452 x_return_status := FND_API.G_RET_STS_ERROR;
5453
5454 FND_MSG_Pub.count_and_get
5455 ( p_count => x_msg_count
5456 , p_data => x_msg_data
5457 );
5458
5459 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Exp_Error ');
5460
5461 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5463 FND_MSG_PUB.Count_And_Get
5464 (
5465 p_count => x_msg_count,
5466 p_data => x_msg_data,
5467 p_encoded => FND_API.G_FALSE
5468 );
5469
5470 WHEN OTHERS THEN
5471 GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Error in Select='||SQLCODE||'.');
5472 x_return_status := SQLCODE;
5473
5474 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
5475 , l_api_name
5476 );
5477
5478 /* Get message count and data */
5479 FND_MSG_Pub.count_and_get
5480 ( p_count => x_msg_count
5481 , p_data => x_msg_data
5482 );
5483
5484
5485 END is_line_allocated;
5486
5487 -- PK Added for bug#3055126 - WSH.J
5488 -- This rotine will be called from WSH Group API
5489 -- WSH_DELIVERY_DETAILS_GRP.Create_Update_Delivery_Detail
5490 -- If this group layer API is called from public API
5491 -- WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes
5492 -- Actual call is from WSHDDGPB.pls PROCEDURE Validate_Delivery_Detail
5493 -- This procedure checks if quantity1 and quantity2 ( Shipped or Cycle Count0
5494 -- are within tolerance and can raise these exceptions.
5495 -- 1) Required quantity not populated
5496 -- 2) Deviation_hi
5497 -- 3) Deviation_lo
5498
5499 PROCEDURE validate_opm_quantities(
5500 p_inventory_item_id IN NUMBER,
5501 p_organization_id IN NUMBER,
5502 p_quantity IN OUT NOCOPY NUMBER,
5503 p_quantity2 IN OUT NOCOPY NUMBER,
5504 p_lot_number IN VARCHAR2,
5505 p_sublot_number IN VARCHAR2,
5506 x_check_status OUT NOCOPY NUMBER,
5507 x_return_status OUT NOCOPY VARCHAR2)
5508 IS
5509
5510 l_ic_item_mst_rec GMI_Reservation_Util.ic_item_mst_rec;
5511 x_msg_count NUMBER;
5512 x_msg_data VARCHAR2(1000);
5513 l_api_name CONSTANT VARCHAR2 (30) := 'validate_opm_quantities';
5514 return_status VARCHAR2(10);
5515 l_lot_id NUMBER;
5516 l_return NUMBER;
5517
5518 CURSOR cur_lot_id_with_sublot IS
5519 SELECT ic.lot_id
5520 FROM ic_lots_mst ic
5521 WHERE ic.item_id = l_ic_item_mst_rec.item_id
5522 AND ic.lot_no = p_lot_number
5523 AND ic.sublot_no = p_sublot_number;
5524
5525 CURSOR cur_lot_id_with_lot IS
5526 SELECT ic.lot_id
5527 FROM ic_lots_mst ic
5528 WHERE ic.item_id = l_ic_item_mst_rec.item_id
5529 AND ic.lot_no = p_lot_number
5530 AND ic.sublot_no IS NULL;
5531
5532 BEGIN
5533
5534 -- Get OPM Item
5535 -- Check OPM Item controls.
5536 -- See if required qty1 and qty2 are populated
5537 -- (Add later Default if not populated).
5538 -- If item is dual control and both fields are not populated then raise exception
5539 -- If both are populated then get lot_id from lot_no and sublot_no. If lot not found use lot_id = 0
5540 -- Call dev_validation API. If valid return success if not return exception.
5541 -- x_check_status 1- Success 2- Required field not populated 3- IC_DEVIATION_HI_ERR 4- IC_DEVIATION_LO_ERR
5542 --
5543 x_return_status := FND_API.G_RET_STS_SUCCESS;
5544 x_check_status := 1;
5545 gmi_reservation_util.println('Calling Get_OPM_item_from_Apps from validate_opm_quantities');
5546 Get_OPM_item_from_Apps(
5547 p_organization_id => p_organization_id
5548 , p_inventory_item_id => p_inventory_item_id
5549 , x_ic_item_mst_rec => l_ic_item_mst_rec
5550 , x_return_status => return_status
5551 , x_msg_count => x_msg_count
5552 , x_msg_data => x_msg_data);
5553 gmi_reservation_util.println('BaCK FROM Get_OPM_item_from_Apps IN validate_opm_quantities');
5554
5555 IF (return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5556 FND_MESSAGE.Set_Name('GMI','GMI_OPM_ITEM');
5557 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
5558 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
5559 FND_MSG_PUB.Add;
5560 x_check_status := 0;
5561 raise FND_API.G_EXC_ERROR;
5562 END IF;
5563
5564 IF ( l_ic_item_mst_rec.dualum_ind > 0 ) THEN
5565 IF (nvl(p_quantity,fnd_api.g_miss_num) = fnd_api.g_miss_num ) THEN
5566 gmi_reservation_util.println('Item Dual Control. Field not populated Qty1= '||p_quantity);
5567 x_check_status := 2;
5568 x_return_status := FND_API.G_RET_STS_ERROR;
5569 RETURN;
5570 ELSIF ( nvl(p_quantity2,fnd_api.g_miss_num) = fnd_api.g_miss_num) THEN
5571 gmi_reservation_util.println('Item Dual Control. Field not populated Qty2= '||p_quantity2);
5572 x_check_status := 5;
5573 x_return_status := FND_API.G_RET_STS_ERROR;
5574 RETURN;
5575 END IF;
5576 ELSE -- Item is Not dual control No need to check
5577 IF (nvl(p_quantity,fnd_api.g_miss_num) = fnd_api.g_miss_num) THEN
5578 x_check_status := 2;
5579 x_return_status := FND_API.G_RET_STS_ERROR;
5580 RETURN;
5581 ELSE
5582 GMI_reservation_Util.PrintLn('(opm_dbg) in Util validate_opm_quantities: Item Not dualUM. No need to check ');
5583 x_check_status := 1;
5584 RETURN;
5585 END IF;
5586 END IF;
5587 -- Get lot_id.
5588 GMI_reservation_Util.PrintLn('(opm_dbg) in Util validate_opm_quantities: Lot '|| p_lot_number || ' Sublot '|| p_sublot_number);
5589 IF ( l_ic_item_mst_rec.lot_ctl = 0 ) THEN
5590 GMI_reservation_Util.PrintLn('(opm_dbg) in Util validate_opm_quantities: Item Not Lot Control.');
5591 l_lot_id := 0;
5592 ELSIF (p_lot_number IS NOT NULL AND p_sublot_number IS NOT NULL) THEN
5593 GMI_reservation_Util.PrintLn('(opm_dbg) in Util validate_opm_quantities: Sublot Not Null.');
5594 OPEN cur_lot_id_with_sublot;
5595 FETCH cur_lot_id_with_sublot INTO l_lot_id;
5596 IF (cur_lot_id_with_sublot%NOTFOUND) THEN
5597 l_lot_id := 0;
5598 END IF;
5599 CLOSE cur_lot_id_with_sublot;
5600 ELSIF (p_lot_number IS NOT NULL AND p_sublot_number IS NULL) THEN
5601 GMI_reservation_Util.PrintLn('(opm_dbg) in Util validate_opm_quantities: Sublot Is Null.');
5602 OPEN cur_lot_id_with_lot;
5603 FETCH cur_lot_id_with_lot INTO l_lot_id;
5604 IF (cur_lot_id_with_lot%NOTFOUND) THEN
5605 l_lot_id := 0;
5606 END IF;
5607 CLOSE cur_lot_id_with_sublot;
5608 ELSE
5609 l_lot_id := 0;
5610 END IF;
5611 GMI_reservation_Util.PrintLn('(opm_dbg) in Util validate_opm_quantities: lot_id '||l_lot_id);
5612 -- We have item_id, lot_id, qty1 and qty2 Now call deviation check
5613 l_return := GMICVAL.dev_validation(l_ic_item_mst_rec.item_id
5614 ,l_lot_id
5615 ,p_quantity
5616 ,l_ic_item_mst_rec.item_um
5617 ,p_quantity2
5618 ,l_ic_item_mst_rec.item_um2
5619 ,0);
5620
5621 IF(l_return = -68) THEN
5622 -- 'IC_DEVIATION_HI_ERR'
5623 x_check_status := 3;
5624 x_return_status := FND_API.G_RET_STS_ERROR;
5625 ELSIF (l_return = -69) THEN
5626 -- 'IC_DEVIATION_LO_ERR'
5627 x_check_status := 4;
5628 x_return_status := FND_API.G_RET_STS_ERROR;
5629 ELSE
5630 x_check_status := 1;
5631 END IF;
5632
5633
5634 EXCEPTION
5635 WHEN FND_API.G_EXC_ERROR THEN
5636
5637 x_return_status := FND_API.G_RET_STS_ERROR;
5638
5639 FND_MSG_Pub.count_and_get
5640 ( p_count => x_msg_count
5641 , p_data => x_msg_data
5642 );
5643
5644 GMI_reservation_Util.PrintLn('(opm_dbg) in Util validate_opm_quantities: Exp_Error ');
5645
5646 WHEN OTHERS THEN
5647 GMI_reservation_Util.PrintLn('(opm_dbg) in Util validate_opm_quantities: Error in Select='||SQLCODE||'.');
5648 x_return_status := SQLCODE;
5649
5650 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
5651 , l_api_name
5652 );
5653
5654 /* Get message count and data */
5655 FND_MSG_Pub.count_and_get
5656 ( p_count => x_msg_count
5657 , p_data => x_msg_data
5658 );
5659
5660 END validate_opm_quantities;
5661
5662 END GMI_Reservation_Util;