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