[Home] [Help]
PACKAGE BODY: APPS.GME_MOBILE_TXN
Source
1 PACKAGE BODY GME_MOBILE_TXN AS
2 /* $Header: GMEMOTXB.pls 120.36.12020000.3 2012/08/08 12:17:14 apmishra ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2005 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 |===========================================================================|
7 | |
8 | PL/SQL Package to support the (Java) GME Mobile Application. |
9 | Contains PL/SQL procedures used by mobile to transact material. |
10 | |
11 +===========================================================================+
12 | HISTORY |
13 | |
14 | Date Who What |
15 | ==== === ==== |
16 | 13-May-05 Eddie Oumerretane First version |
17 | 09-Jun-06 Namit S. Bug#5236906. Query parent lot too in |
18 | Fetch_Product_Pending_Lots. Modify |
19 | Update_Product_Pending_Lot |
20 | 21-Jun-06 Shrikant Nene Bug#5263908. Added revision in the proc |
21 | Populate_Dispensing_Table. |
22 | 11-Jul-06 Shrikant Nene Bug#5331639. Changed procedure |
23 | Validate_Item_For_IB |
24 | 26-Jan-07 Archana Mundhe Bug 4774944. Modified release_step and |
25 | complete_step procedure. Added call to |
26 | validate step for release and complete. |
27 | 28-Feb-07 Archana Mundhe Bug 4774944. REWORK Modified release_step |
28 | and complete_step procedure. Added code to|
29 | check for parameter step controls batch |
30 | status. |
31 | |
32 23-Feb-09 G. Muratore Bug 8234700. pass in false to process transactions
33 and commit after. This more closely mimics how save is done on the form and
34 commits all work together. GMF layers are not lost anymore.
35 | 09-jun-2009 Srinivasulu Puri backport of Bug 6925025
36 | Added parameters subinventory_code and locator_id.
37 | Pass these parameters to build_txn_inter_lot call.
38 |
39 | 30-Mar-10 APMISHRA Bug 9367054. Added a new procedure |
40 | print_label to print labels for product |
41 | transactions |
42 | 15-Apr-10 APMISHRA Bug 9483781. Modified the procedure |
43 | Create_material_txn to process the LPN |
44 | details existing for a reservation |
45 | 16-may-12 APMISHRA Bug 13986776 Added Code to call save batch |
46 | |
47 | 08-Aug-12 APMISHRA Bug 14376915 Modified |
48 | Validate_Step_Completion_Date to compare the input dates |
49 | after converting them to the correct format |
50 +===========================================================================*/
51
52 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
53 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_MOBILE_TXN';
54
55 /*+========================================================================+
56 | PROCEDURE NAME
57 | Get_Txn_Type
58 |
59 | USAGE
60 |
61 | ARGUMENTS
62 | p_transaction_type
63 |
64 | RETURNS
65 | transaction_type_id
66 |
67 | HISTORY
68 | Created 26-Apr-05 Eddie Oumerretane
69 |
70 +========================================================================+*/
71 FUNCTION Get_Txn_Type(p_transaction_type_id NUMBER) RETURN NUMBER IS
72 l_transaction_type_id NUMBER;
73 BEGIN
74
75 IF p_transaction_type_id = G_ING_ISSUE THEN
76 l_transaction_type_id := GME_COMMON_PVT.g_ing_issue;
77 ELSIF p_transaction_type_id = G_ING_RETURN THEN
78 l_transaction_type_id := GME_COMMON_PVT.g_ing_return;
79 ELSIF p_transaction_type_id = G_PROD_COMPLETION THEN
80 l_transaction_type_id := GME_COMMON_PVT.g_prod_completion;
81 ELSIF p_transaction_type_id = G_PROD_RETURN THEN
82 l_transaction_type_id := GME_COMMON_PVT.g_prod_return;
83 ELSIF p_transaction_type_id = G_BYPROD_COMPLETION THEN
84 l_transaction_type_id := GME_COMMON_PVT.g_byprod_completion;
85 ELSIF p_transaction_type_id = G_BYPROD_RETURN THEN
86 l_transaction_type_id := GME_COMMON_PVT.g_byprod_return;
87 END IF;
88
89 RETURN l_transaction_type_id;
90
91 END Get_Txn_Type;
92
93 /* Bug#5663458
94 * Created the following procedure. This procedure is used to relieve the either
95 * reservations or pending lots depends on the line type that we pass
96 */
97 PROCEDURE relieve_resvns_pend_lots(p_rsrv_pndlot_id IN NUMBER,
98 p_relieve_qty IN NUMBER,
99 p_sec_qty IN NUMBER,
100 p_line_type IN NUMBER,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_error_msg OUT NOCOPY VARCHAR2)
103 IS
104 l_count NUMBER;
105 no_rsrv_pndlot_id EXCEPTION;
106 BEGIN
107 IF (g_debug IS NOT NULL) THEN
108 gme_debug.log_initialize ('RelieveRsrvPndLots');
109 END IF;
110
111 x_return_status := FND_API.G_RET_STS_SUCCESS;
112 x_error_msg := ' ';
113
114 IF p_rsrv_pndlot_id IS NULL THEN
115 RAISE no_rsrv_pndlot_id;
116 END IF;
117
118 IF p_line_type = gme_common_pvt.g_line_type_ing THEN
119 --calling gme_reservations_pvt API to relieve reservations
120 gme_reservations_pvt.relieve_reservation(p_reservation_id => p_rsrv_pndlot_id,
121 p_prim_relieve_qty => p_relieve_qty,
122 x_return_status => x_return_status);
123
124 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
125 gme_common_pvt.count_and_get (x_count => l_count
126 ,p_encoded => fnd_api.g_false
127 ,x_data => x_error_msg);
128
129 END IF;
130 ELSE
131 gme_pending_product_lots_pvt.relieve_pending_lot(p_pending_lot_id => p_rsrv_pndlot_id
132 ,p_quantity => p_relieve_qty
133 ,p_secondary_quantity=> p_sec_qty
134 ,x_return_status => x_return_status);
135
136 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
137 gme_common_pvt.count_and_get (x_count => l_count
138 ,p_encoded => fnd_api.g_false
139 ,x_data => x_error_msg);
140
141 END IF;
142 END IF;
143 EXCEPTION
144 WHEN NO_RSRV_PNDLOT_ID THEN
145 x_return_status := fnd_api.g_ret_sts_error;
146 fnd_message.set_name('GME','INVALID_VALUE');
147 x_error_msg := fnd_message.get;
148 WHEN OTHERS THEN
149 IF g_debug <= gme_debug.g_log_unexpected THEN
150 gme_debug.put_line('When others exception in relieve_resvns_pend_lots');
151 END IF;
152 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','relieve_resvns_pend_lots');
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 x_error_msg := fnd_message.get;
155
156 END relieve_resvns_pend_lots;
157
158 /*
159 * Bug 13334961
160 * New procedure to relieve the reservations for dispense Items
161 *
162 */
163 PROCEDURE relieve_resvns_disp_items(p_rsrv_pndlot_id IN NUMBER,
164 p_relieve_qty IN NUMBER,
165 p_sec_qty IN NUMBER,
166 p_line_type IN NUMBER,
167 x_return_status OUT NOCOPY VARCHAR2,
168 x_error_msg OUT NOCOPY VARCHAR2) IS
169 CURSOR get_reservation_id IS
170 SELECT reservation_id from gmo_material_dispenses
171 WHERE dispense_id = p_rsrv_pndlot_id;
172
173 l_reservation_id NUMBER;
174 l_count NUMBER;
175 BEGIN
176
177 OPEN get_reservation_id;
178 FETCH get_reservation_id INTO l_reservation_id;
179 CLOSE get_reservation_id;
180
181 relieve_resvns_pend_lots(p_rsrv_pndlot_id => l_reservation_id,
182 p_relieve_qty => p_relieve_qty,
183 p_sec_qty => p_sec_qty,
184 p_line_type => p_line_type,
185 x_return_status => x_return_status,
186 x_error_msg => x_error_msg);
187
188 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
189 gme_common_pvt.count_and_get (x_count => l_count
190 ,p_encoded => fnd_api.g_false
191 ,x_data => x_error_msg);
192
193 END IF;
194 EXCEPTION
195 WHEN OTHERS THEN
196 IF g_debug <= gme_debug.g_log_unexpected THEN
197 gme_debug.put_line('When others exception in relieve_resvns_disp_items');
198 END IF;
199 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','relieve_resvns_disp_items');
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201 x_error_msg := fnd_message.get;
202 END relieve_resvns_disp_items;
203
204
205 /*+========================================================================+
206 | PROCEDURE NAME
207 | Get_Stacked_Messages
208 |
209 | USAGE
210 |
211 | ARGUMENTS
212 |
213 | RETURNS
214 | x_message
215 |
216 | HISTORY
217 | Created 26-Apr-05 Eddie Oumerretane
218 |
219 +========================================================================+*/
220 PROCEDURE Get_Stacked_Messages(x_message OUT NOCOPY VARCHAR2)
221 IS
222 l_message VARCHAR2(2000);
223 l_msg_count NUMBER;
224 BEGIN
225
226 fnd_msg_pub.Count_And_Get
227 (p_encoded => FND_API.g_false,
228 p_count => l_msg_count,
229 p_data => l_message
230 );
231
232
233 fnd_msg_pub.delete_msg;
234
235 x_message := l_message;
236
237 EXCEPTION
238 WHEN OTHERS THEN
239 NULL;
240
241 END Get_Stacked_Messages;
242
243 /*+========================================================================+
244 | PROCEDURE NAME
245 | Create_Material_Txns
246 |
247 | USAGE
248 |
249 | ARGUMENTS
250 | p_organization_id
251 | p_batch_id
252 | p_material_detail_id
253 | p_item_id
254 | p_revision
255 | p_subinventory_code
256 | p_locator_id
257 | p_txn_qty
258 | p_txn_uom_code
259 | p_sec_txn_qty
260 | p_sec_uom_code
261 | p_primary_uom_code
262 | p_txn_primary_qty
263 | p_reason_id
264 | p_txn_date
265 | p_txn_type_id
266 | p_phantom_type
267 | p_user_id
268 | p_login_id
269 | p_dispense_id
270 | p_reservation_id
271 |
272 | RETURNS
273 | x_message
274 |
275 | HISTORY
276 | Created 26-Apr-05 Eddie Oumerretane
277 | Modified 15-Apr-10 APMISHRA bug#9483781. Added a new parameter
278 | p_reservation_id to fetch and process the
279 | LPN details against it.
280 +========================================================================+*/
281 PROCEDURE Create_Material_Txn(p_organization_id IN NUMBER,
282 p_batch_id IN NUMBER,
283 p_material_detail_id IN NUMBER,
284 p_item_id IN NUMBER,
285 p_revision IN VARCHAR2,
286 p_subinventory_code IN VARCHAR2,
287 p_locator_id IN NUMBER,
288 p_txn_qty IN NUMBER,
289 p_txn_uom_code IN VARCHAR2,
290 p_sec_txn_qty IN NUMBER,
291 p_sec_uom_code IN VARCHAR2,
292 p_primary_uom_code IN VARCHAR2,
293 p_txn_primary_qty IN NUMBER,
294 p_reason_id IN NUMBER,
295 p_txn_date IN DATE,
296 p_txn_type_id IN NUMBER,
297 p_phantom_type IN NUMBER,
298 p_user_id IN NUMBER,
299 p_login_id IN NUMBER,
300 p_dispense_id IN NUMBER,
301 p_phantom_line_id IN NUMBER,
302 x_txn_id OUT NOCOPY NUMBER,
303 x_txn_type_id OUT NOCOPY NUMBER,
304 x_txn_header_id OUT NOCOPY NUMBER,
305 x_return_status OUT NOCOPY VARCHAR2,
306 x_error_msg OUT NOCOPY VARCHAR2,
307 p_reservation_id IN NUMBER DEFAULT NULL)
308 IS
309 l_count NUMBER;
310 l_assign_phantom NUMBER;
311 l_mmti_rec_in mtl_transactions_interface%ROWTYPE;
312 l_mmti_rec_out mtl_transactions_interface%ROWTYPE;
313
314 --
315 -- bug 9483781
316 -- Cursor to fetch the LPN_ID against a reservation
317 --
318 Cursor get_lpn_for_rsrv IS
319 Select lpn_id from mtl_reservations
320 where reservation_id = p_reservation_id;
321
322 l_lpn_id NUMBER;
323 BEGIN
324
325 -- Clearing the quantity cache
326 inv_quantity_tree_pub.clear_quantity_cache;
327
328 IF (g_debug IS NOT NULL) THEN
329 gme_debug.log_initialize ('MobileCreTxn');
330 END IF;
331
332 gme_common_pvt.g_user_ident := p_user_id;
333 gme_common_pvt.g_login_id := p_login_id;
334 gme_common_pvt.set_timestamp;
335
336 x_return_status := FND_API.G_RET_STS_SUCCESS;
337 x_error_msg := ' ';
338
339 l_mmti_rec_in.transaction_type_id := Get_Txn_Type(p_txn_type_id);
340
341 l_mmti_rec_in.transaction_source_id := p_batch_id;
342 l_mmti_rec_in.trx_source_line_id := p_material_detail_id;
343 l_mmti_rec_in.inventory_item_id := p_item_id;
344 l_mmti_rec_in.revision := p_revision;
345 l_mmti_rec_in.organization_id := p_organization_id;
346 l_mmti_rec_in.transaction_date := p_txn_date;
347 l_mmti_rec_in.transaction_quantity := p_txn_qty;
348 l_mmti_rec_in.primary_quantity := p_txn_primary_qty;
349 l_mmti_rec_in.reason_id := p_reason_id;
350 l_mmti_rec_in.secondary_transaction_quantity := p_sec_txn_qty;
351 l_mmti_rec_in.secondary_uom_code := p_sec_uom_code;
352 l_mmti_rec_in.transaction_uom := p_txn_uom_code;
353 l_mmti_rec_in.subinventory_code := p_subinventory_code;
354 l_mmti_rec_in.locator_id := p_locator_id;
355 l_mmti_rec_in.transaction_source_name := NULL;
356 l_mmti_rec_in.transaction_reference := p_dispense_id;
357 l_mmti_rec_in.transaction_action_id := NULL;
358
359 --
360 -- bug 9483781
361 -- If the reservation exists then fetch the LPN_ID
362 -- against it and pass it to the record structure.
363 --
364 IF (p_reservation_id IS NOT NULL AND
365 p_reservation_id > 0 ) THEN
366 OPEN get_lpn_for_rsrv;
367 FETCH get_lpn_for_rsrv INTO l_lpn_id;
368 CLOSE get_lpn_for_rsrv;
369
370 IF l_lpn_id IS NOT NULL THEN
371 l_mmti_rec_in.lpn_id := l_lpn_id;
372 END IF;
373 END IF;
374
375 l_assign_phantom := 0;
376
377 IF p_phantom_line_id IS NOT NULL THEN
378 -- This is a product of a phantom batch or a phantom ingredient
379 l_assign_phantom := 1;
380 END IF;
381
382 GME_TRANSACTIONS_PVT.Build_Txn_Inter_Hdr(
383 p_mmti_rec => l_mmti_rec_in,
384 p_assign_phantom => l_assign_phantom,
385 x_mmti_rec => l_mmti_rec_out,
386 x_return_status => x_return_status);
387
388
389 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
390 -- Bug 5255933 Use GME_COMMON_PVT to get error message
391 --x_error_msg := fnd_message.get;
392 GME_COMMON_PVT.count_and_get (x_count => l_count
393 ,p_encoded => fnd_api.g_false
394 ,x_data => x_error_msg);
395
396 x_txn_id := -1;
397 x_txn_type_id := -1;
398 x_txn_header_id := -1;
399 ELSE
400 x_txn_id := l_mmti_rec_out.transaction_interface_id;
401 x_txn_type_id := l_mmti_rec_in.transaction_type_id;
402 x_txn_header_id := l_mmti_rec_out.transaction_header_id;
403 END IF;
404
405 EXCEPTION
406 WHEN OTHERS THEN
407 IF g_debug <= gme_debug.g_log_unexpected THEN
408 gme_debug.put_line('When others exception in Create MAterial Txn');
409 END IF;
410 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','create_material_txn');
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412 x_error_msg := fnd_message.get;
413
414 END Create_Material_Txn;
415
416 /*+========================================================================+
417 | PROCEDURE NAME
418 | Create_Lot_Txn
419 |
420 | USAGE
421 |
422 | ARGUMENTS
423 |
424 | RETURNS
425 |
426 | HISTORY
427 | Created 26-Apr-05 Eddie Oumerretane
428 |
429 +========================================================================+*/
430 PROCEDURE Create_Lot_Txn(p_txn_id IN NUMBER,
431 p_txn_type_id IN NUMBER,
432 p_item_id IN NUMBER,
433 p_lot_number IN VARCHAR2,
434 p_txn_qty IN NUMBER,
435 p_txn_prim_qty IN NUMBER,
436 p_sec_txn_qty IN NUMBER,
437 p_user_id IN NUMBER,
438 p_login_id IN NUMBER,
439 p_subinventory_code IN VARCHAR2,
440 p_locator_id IN NUMBER,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_error_msg OUT NOCOPY VARCHAR2)
443 IS
444 l_mmli_rec_in mtl_transaction_lots_interface%ROWTYPE;
445 l_mmli_rec_out mtl_transaction_lots_interface%ROWTYPE;
446 BEGIN
447
448 IF (g_debug IS NOT NULL) THEN
449 gme_debug.log_initialize ('MobileCreLotTxn');
450 END IF;
451
452 x_return_status := FND_API.G_RET_STS_SUCCESS;
453 x_error_msg := ' ';
454
455 gme_common_pvt.g_user_ident := p_user_id;
456 gme_common_pvt.g_login_id := p_login_id;
457 gme_common_pvt.set_timestamp;
458
459 l_mmli_rec_in.lot_number := p_lot_number;
460 l_mmli_rec_in.transaction_quantity := p_txn_qty;
461 l_mmli_rec_in.primary_quantity := p_txn_prim_qty;
462 l_mmli_rec_in.secondary_transaction_quantity := p_sec_txn_qty;
463 -- Bug 6925025
464 -- Added parameters subinventory_code and locator_id
465
466 GME_TRANSACTIONS_PVT.build_txn_inter_lot(
467 p_trans_inter_id => p_txn_id,
468 p_transaction_type_id => p_txn_type_id,
469 p_inventory_item_id => p_item_id,
470 p_subinventory_code => p_subinventory_code,
471 p_locator_id => p_locator_id,
472 p_mmli_rec => l_mmli_rec_in,
473 x_mmli_rec => l_mmli_rec_out,
474 x_return_status => x_return_status
475 );
476
477 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
478 x_error_msg := fnd_message.get;
479 END IF;
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 IF g_debug <= gme_debug.g_log_unexpected THEN
484 gme_debug.put_line('When others exception in Create Lot Txn');
485 END IF;
486 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','create_lot_txn');
487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
488 x_error_msg := fnd_message.get;
489
490 END Create_Lot_Txn;
491
492 /*+========================================================================+
493 | PROCEDURE NAME
494 | Validate_Indiv_Lot_Txn
495 |
496 | USAGE
497 |
498 | ARGUMENTS
499 |
500 | RETURNS
501 |
502 | HISTORY
503 | Created 26-Apr-05 Eddie Oumerretane
504 |
505 +========================================================================+*/
506 PROCEDURE Validate_Indiv_Lot_Txn(
507 p_organization_id IN NUMBER,
508 p_item_id IN NUMBER,
509 p_revision IN VARCHAR2,
510 p_subinventory_code IN VARCHAR2,
511 p_locator_id IN NUMBER,
512 p_lot_number IN VARCHAR2,
513 p_primary_lot_qty IN NUMBER,
514 p_transaction_type_id IN NUMBER,
515 x_return_status OUT NOCOPY VARCHAR2,
516 x_error_msg OUT NOCOPY VARCHAR2)
517 IS
518
519 l_return_status VARCHAR2(1);
520 l_msg_data VARCHAR2(3000);
521 l_msg_count NUMBER;
522 l_transaction_type_id NUMBER;
523
524 BEGIN
525
526 IF (g_debug IS NOT NULL) THEN
527 gme_debug.log_initialize ('MobileValLotTxn');
528 END IF;
529 x_return_status := FND_API.G_RET_STS_SUCCESS;
530 x_error_msg := ' ';
531
532 l_transaction_type_id := Get_Txn_Type(p_transaction_type_id);
533
534 IF NOT (INV_LOT_API_PUB.validate_lot_indivisible(
535 p_api_version =>1.0
536 ,p_init_msg_list =>FND_API.G_FALSE
537 ,p_commit =>FND_API.G_FALSE
538 ,p_validation_level =>FND_API.G_VALID_LEVEL_FULL
539 ,p_transaction_type_id =>l_transaction_type_id
540 ,p_organization_id =>p_organization_id
541 ,p_inventory_item_id =>p_item_id
542 ,p_revision =>p_revision
543 ,p_subinventory_code =>p_subinventory_code
544 ,p_locator_id =>p_locator_id
545 ,p_lot_number =>p_lot_number
546 ,p_primary_quantity =>p_primary_lot_qty
547 ,p_qoh =>NULL
548 ,p_atr =>NULL
549 ,x_return_status =>l_return_status
550 ,x_msg_count =>l_msg_count
551 ,x_msg_data =>l_msg_data))
552
553 THEN
554 GME_COMMON_PVT.Count_And_Get (x_count => l_msg_count
555 ,p_encoded => fnd_api.g_false
556 ,x_data => x_error_msg);
557
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 END IF;
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 IF g_debug <= gme_debug.g_log_unexpected THEN
564 gme_debug.put_line('When others exception in Validate Indiv Lot Txn');
565 END IF;
566 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Validate_Indiv_Lot_Txn');
567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
568 x_error_msg := fnd_message.get;
569
570 END Validate_Indiv_Lot_Txn;
571
572 /*+========================================================================+
573 | PROCEDURE NAME
574 | Is_Material_Status_Applicable
575 |
576 | USAGE
577 |
578 | ARGUMENTS
579 |
580 | RETURNS
581 |
582 | HISTORY
583 | Created 26-Apr-05 Eddie Oumerretane
584 |
585 +========================================================================+*/
586 PROCEDURE Is_Material_Status_Applicable(
587 p_transaction_type_id IN NUMBER,
588 p_lot_status_enabled IN VARCHAR2,
589 p_organization_id IN NUMBER,
590 p_inventory_item_id IN NUMBER,
591 p_sub_code IN VARCHAR2,
592 p_locator_id IN NUMBER,
593 p_lot_number IN VARCHAR2,
594 p_object_type IN VARCHAR2,
595 x_return_status OUT NOCOPY VARCHAR2,
596 x_error_msg OUT NOCOPY VARCHAR2)
597 IS
598
599 l_is_mtl_status_applicable VARCHAR2(1);
600 l_transaction_type_id NUMBER;
601 l_object_type VARCHAR2(1);
602 l_type VARCHAR2(100);
603 l_item VARCHAR2(100);
604 l_locator VARCHAR2(100);
605
606 BEGIN
607
608 IF (g_debug IS NOT NULL) THEN
609 gme_debug.log_initialize ('MobileValMtlSts');
610 END IF;
611
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613 x_error_msg := ' ';
614
615 l_transaction_type_id := Get_Txn_Type(p_transaction_type_id);
616
617
618 --- Bug 5255933 use object type 'A'to check material status of all entities
619 --- i.e sub, locator, lot, serial
620 /*
621 IF p_sub_code IS NOT NULL THEN
622 l_object_type := 'Z';
623 END IF;
624
625 IF p_locator_id IS NOT NULL THEN
626 l_object_type := 'L';
627 END IF;
628
629 IF p_lot_number IS NOT NULL THEN
630 l_object_type := 'O';
631 END IF;
632 */
633
634 ---l_object_type := 'A';
635
636 l_is_mtl_status_applicable :=
637 INV_MATERIAL_STATUS_GRP.is_status_applicable(p_wms_installed => NULL,
638 p_trx_status_enabled => NULL,
639 p_trx_type_id => l_transaction_type_id,
640 p_lot_status_enabled =>p_lot_status_enabled,
641 p_serial_status_enabled => 'N',
642 p_organization_id => p_organization_id,
643 p_inventory_item_id => p_inventory_item_id,
644 p_sub_code => p_sub_code,
645 p_locator_id => p_locator_id,
646 p_lot_number => p_lot_number,
647 p_serial_number => NULL,
648 p_object_type => p_object_type);
649
650
651 IF l_is_mtl_status_applicable <> 'Y' THEN
652
653 SELECT transaction_type_name
654 INTO l_type
655 FROM mtl_transaction_types
656 WHERE transaction_type_id = p_transaction_type_id;
657
658 SELECT substr(concatenated_segments,1,100)
659 INTO l_item
660 FROM mtl_system_items_kfv
661 WHERE organization_id = p_organization_id
662 AND inventory_item_id = p_inventory_item_id;
663
664 IF p_object_type = 'O' THEN --- Lot
665 FND_MESSAGE.SET_NAME('GME','GME_MATERIAL_STS_INV_LOT');
666 FND_MESSAGE.SET_TOKEN('TRANSTYPE', l_type);
667 FND_MESSAGE.SET_TOKEN('ITEM', l_item);
668 FND_MESSAGE.SET_TOKEN('LOT', p_lot_number);
669 ELSIF p_object_type = 'Z' THEN --- Subinventory
670 FND_MESSAGE.SET_NAME('GME','GME_MATERIAL_STS_INV_SUB');
671 FND_MESSAGE.SET_TOKEN('TRANSTYPE', l_type);
672 FND_MESSAGE.SET_TOKEN('ITEM', l_item);
673 FND_MESSAGE.SET_TOKEN('SUBINV', p_sub_code);
674 ELSIF p_object_type = 'L' THEN --- Locator
675 SELECT substr(concatenated_segments,1,100)
676 INTO l_locator
677 FROM wms_item_locations_kfv
678 WHERE organization_id = p_organization_id
679 AND subinventory_code = p_sub_code
680 AND inventory_location_id = p_locator_id;
681
682 FND_MESSAGE.SET_NAME('GME','GME_MATERIAL_STS_INV_SUB_LOC');
683 FND_MESSAGE.SET_TOKEN('TRANSTYPE', l_type);
684 FND_MESSAGE.SET_TOKEN('SUBINV', p_sub_code);
685 FND_MESSAGE.SET_TOKEN('ITEM', l_item); --Bug#5658050
686 FND_MESSAGE.SET_TOKEN('LOCN',l_locator);
687 END IF;
688
689 x_return_status := FND_API.G_RET_STS_ERROR;
690 x_error_msg := fnd_message.get;
691
692 END IF;
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 IF g_debug <= gme_debug.g_log_unexpected THEN
697 gme_debug.put_line('When others exception in Is_Material_Status_Applicable');
698 END IF;
699 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Is_Material_Status_Applicable');
700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
701 x_error_msg := fnd_message.get;
702
703 END Is_Material_Status_Applicable;
704
705 /*+========================================================================+
706 | PROCEDURE NAME
707 | Fetch_Aggregated_Txns
708 |
709 | USAGE
710 |
711 | ARGUMENTS
712 |
713 | RETURNS
714 |
715 | HISTORY
716 | Created 26-Apr-05 Eddie Oumerretane
717 |
718 +========================================================================+*/
719 PROCEDURE Fetch_Aggregated_Txns(p_org_id IN NUMBER,
720 p_batch_id IN NUMBER,
721 p_material_detail_id IN NUMBER,
722 x_aggr_txn_qty OUT NOCOPY NUMBER,
723 x_aggr_txn_sec_qty OUT NOCOPY NUMBER,
724 x_return_status OUT NOCOPY VARCHAR2,
725 x_error_msg OUT NOCOPY VARCHAR2)
726 IS
727
728 CURSOR Get_Agg_Txn IS
729 SELECT ABS(SUM (NVL(primary_quantity,0))),
730 ABS(SUM (NVL(secondary_transaction_quantity,0)))
731 FROM mtl_material_transactions
732 WHERE organization_id = p_org_id
733 AND transaction_source_id = p_batch_id
734 AND trx_source_line_id = p_material_detail_id
735 AND transaction_source_type_id = gme_common_pvt.g_txn_source_type;
736 BEGIN
737
738 IF (g_debug IS NOT NULL) THEN
739 gme_debug.log_initialize ('MobileFetchAgrTxns');
740 END IF;
741
742
743 x_return_status := FND_API.G_RET_STS_SUCCESS;
744 x_error_msg := ' ';
745
746 OPEN Get_Agg_Txn;
747 FETCH Get_Agg_Txn INTO x_aggr_txn_qty, x_aggr_txn_sec_qty;
748 CLOSE Get_Agg_Txn;
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 IF g_debug <= gme_debug.g_log_unexpected THEN
753 gme_debug.put_line('When others exception in fetch aggrageted txns');
754 END IF;
755 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_aggregated_txns');
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757 x_error_msg := fnd_message.get;
758
759 END Fetch_Aggregated_Txns;
760
761 /*+========================================================================+
762 | PROCEDURE NAME
763 | Fetch_Aggregated_Lot_Txns
764 |
765 | USAGE
766 |
767 | ARGUMENTS
768 |
769 | RETURNS
770 |
771 | HISTORY
772 | Created 26-Apr-05 Eddie Oumerretane
773 |
774 +========================================================================+*/
775 PROCEDURE Fetch_Aggregated_Lot_Txns(p_org_id IN NUMBER,
776 p_batch_id IN NUMBER,
777 p_material_detail_id IN NUMBER,
778 p_lot_number IN VARCHAR2,
779 x_return_status OUT NOCOPY VARCHAR2,
780 x_error_msg OUT NOCOPY VARCHAR2,
781 x_txn_cursor OUT NOCOPY t_genref)
782 IS
783 BEGIN
784
785 IF (g_debug IS NOT NULL) THEN
786 gme_debug.log_initialize ('MobileFetchAgrLotTxns');
787 END IF;
788
789
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791 x_error_msg := ' ';
792
793 OPEN x_txn_cursor FOR
794 SELECT m.revision,lot_number,
795 ABS(SUM (NVL(l.primary_quantity,0))),
796 ABS(SUM (NVL(l.secondary_transaction_quantity,0)))
797 FROM mtl_material_transactions m,
798 mtl_transaction_lot_numbers l
799 WHERE l.transaction_id = m.transaction_id
800 AND lot_number = NVL(p_lot_number, l.lot_number)
801 AND m.organization_id = p_org_id
802 AND m.transaction_source_id = p_batch_id
803 AND m.trx_source_line_id = p_material_detail_id
804 AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
805 GROUP BY m.revision,l.lot_number;
806
807 EXCEPTION
808 WHEN OTHERS THEN
809 IF g_debug <= gme_debug.g_log_unexpected THEN
810 gme_debug.put_line('When others exception in fetch aggrageted lot txns');
811 END IF;
812 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_aggregated_lot_txns');
813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
814 x_error_msg := fnd_message.get;
815
816 END Fetch_Aggregated_Lot_Txns;
817
818 /* Bug 5451006: Added to used in return of the revision controlled plain items */
819 /*+========================================================================+
820 | PROCEDURE NAME
821 | Fetch_Aggregated_Rev_Txns
822 |
823 | USAGE
824 |
825 | ARGUMENTS
826 |
827 | RETURNS
828 |
829 | HISTORY
830 | Created 01-Sep-06 Shrikant Nene
831 |
832 +========================================================================+*/
833 PROCEDURE Fetch_Aggregated_Rev_Txns(p_org_id IN NUMBER,
834 p_batch_id IN NUMBER,
835 p_material_detail_id IN NUMBER,
836 p_revision IN VARCHAR2,
837 x_aggr_txn_qty OUT NOCOPY NUMBER,
838 x_aggr_txn_sec_qty OUT NOCOPY NUMBER,
839 x_return_status OUT NOCOPY VARCHAR2,
840 x_error_msg OUT NOCOPY VARCHAR2)
841 IS
842 CURSOR Get_Agg_Txn IS
843 SELECT ABS(SUM (NVL(primary_quantity,0))),
844 ABS(SUM (NVL(secondary_transaction_quantity,0)))
845 FROM mtl_material_transactions
846 WHERE organization_id = p_org_id
847 AND transaction_source_id = p_batch_id
848 AND trx_source_line_id = p_material_detail_id
849 AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
850 AND revision = p_revision
851 GROUP BY revision;
852 BEGIN
853
854 IF (g_debug IS NOT NULL) THEN
855 gme_debug.log_initialize ('MobileFetchAgrRevTxns');
856 END IF;
857
858
859 x_return_status := FND_API.G_RET_STS_SUCCESS;
860 x_error_msg := ' ';
861
862 OPEN Get_Agg_Txn;
863 FETCH Get_Agg_Txn INTO x_aggr_txn_qty, x_aggr_txn_sec_qty;
864 CLOSE Get_Agg_Txn;
865
866
867 EXCEPTION
868 WHEN OTHERS THEN
869 IF g_debug <= gme_debug.g_log_unexpected THEN
870 gme_debug.put_line('When others exception in fetch aggrageted lot txns');
871 END IF;
872 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_aggregated_rev_txns');
873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
874 x_error_msg := fnd_message.get;
875
876 END Fetch_Aggregated_Rev_Txns;
877
878 /*+========================================================================+
879 | PROCEDURE NAME
880 | Fetch_Material_Transactions
881 |
882 | USAGE
883 |
884 | ARGUMENTS
885 |
886 | RETURNS
887 |
888 | HISTORY
889 | Created 26-Apr-05 Eddie Oumerretane
890 |
891 +========================================================================+*/
892 PROCEDURE Fetch_Material_Transactions(p_organization_id IN NUMBER,
893 p_batch_id IN NUMBER,
894 p_material_detail_id IN NUMBER,
895 p_txn_type_id IN NUMBER,
896 x_return_status OUT NOCOPY VARCHAR2,
897 x_error_msg OUT NOCOPY VARCHAR2,
898 x_txn_cursor OUT NOCOPY t_genref)
899 IS
900 l_date_format VARCHAR2(100);
901 l_txn_type_id NUMBER;
902 BEGIN
903
904 IF (g_debug IS NOT NULL) THEN
905 gme_debug.log_initialize ('MobileFetchMtl');
906 END IF;
907
908 l_txn_type_id := Get_Txn_Type(p_txn_type_id);
909
910 x_return_status := FND_API.G_RET_STS_SUCCESS;
911 x_error_msg := ' ';
912
913 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
914
915 IF l_date_format IS NULL THEN
916 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
917 END IF;
918
919 OPEN x_txn_cursor FOR
920 SELECT
921 m.transaction_id,
922 m.transaction_quantity*(-1),
923 m.primary_quantity *(-1),
924 m.secondary_transaction_quantity *(-1),
925 m.transaction_uom,
926 m.subinventory_code,
927 m.locator_id,
928 m.reason_id,
929 l.concatenated_segments,
930 TO_CHAR(transaction_date, l_date_format),
931 revision
932 FROM mtl_material_transactions m,
933 wms_item_locations_kfv l
934 WHERE trx_source_line_id = NVL(p_material_detail_id, trx_source_line_id)
935 AND transaction_source_id = p_batch_id
936 AND transaction_type_id = l_txn_type_id
937 AND l.inventory_location_id = m.locator_id(+);
938
939 EXCEPTION
940 WHEN OTHERS THEN
941 IF g_debug <= gme_debug.g_log_unexpected THEN
942 gme_debug.put_line('When others exception in fetch Mtl Transactions');
943 END IF;
944 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_material_transactions');
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
946 x_error_msg := fnd_message.get;
947
948 END Fetch_Material_Transactions;
949
950 /*+========================================================================+
951 | PROCEDURE NAME
952 | Fetch_Lot_Transactions
953 |
954 | USAGE
955 |
956 | ARGUMENTS
957 |
958 | RETURNS
959 |
960 | HISTORY
961 | Created 26-Apr-05 Eddie Oumerretane
962 |
963 +========================================================================+*/
964 PROCEDURE Fetch_Lot_Transactions(
965 p_transaction_id IN NUMBER,
966 p_lot_number IN VARCHAR2,
967 x_return_status OUT NOCOPY VARCHAR2,
968 x_error_msg OUT NOCOPY VARCHAR2,
969 x_txn_cursor OUT NOCOPY t_genref)
970 IS
971 BEGIN
972
973 IF (g_debug IS NOT NULL) THEN
974 gme_debug.log_initialize ('MobileFetchLotTxn');
975 END IF;
976
977 x_return_status := FND_API.G_RET_STS_SUCCESS;
978 x_error_msg := ' ';
979
980 OPEN x_txn_cursor FOR
981 SELECT
982 lot_number,
983 transaction_quantity *(-1),
984 primary_quantity *(-1),
985 secondary_transaction_quantity *(-1)
986 FROM mtl_transaction_lot_numbers
987 WHERE transaction_id = p_transaction_id AND
988 lot_number = NVL(p_lot_number, lot_number);
989
990 EXCEPTION
991 WHEN OTHERS THEN
992 IF g_debug <= gme_debug.g_log_unexpected THEN
993 gme_debug.put_line('When others exception in fetch Mtl Lot Transactions');
994 END IF;
995 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_material_lot_transactions');
996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
997 x_error_msg := fnd_message.get;
998
999 END Fetch_Lot_Transactions;
1000
1001 /*+========================================================================+
1002 | PROCEDURE NAME
1003 | Process_Interface_Txn
1004 |
1005 | USAGE
1006 |
1007 | ARGUMENTS
1008 |
1009 | RETURNS
1010 |
1011 | HISTORY
1012 | Created 26-Apr-05 Eddie Oumerretane
1013 |
1014 +========================================================================+*/
1015 PROCEDURE Process_Interface_Txn( p_txn_header_id IN NUMBER,
1016 p_user_id IN NUMBER,
1017 p_login_id IN NUMBER,
1018 x_return_status OUT NOCOPY VARCHAR2,
1019 x_error_msg OUT NOCOPY VARCHAR2)
1020 IS
1021 l_msg_count NUMBER;
1022 l_msg_data VARCHAR2 (2000);
1023 l_trans_count NUMBER;
1024
1025 BEGIN
1026
1027 IF (g_debug IS NOT NULL) THEN
1028 gme_debug.log_initialize ('MobileProcessTxn');
1029 END IF;
1030
1031 gme_common_pvt.g_user_ident := p_user_id;
1032 gme_common_pvt.g_login_id := p_login_id;
1033 gme_common_pvt.set_timestamp;
1034
1035 -- Bug 8234700 - Let's pass in false to process transactions and commit after
1036 -- This more closely mimics how save is done on the form and commits all work together.
1037 GME_TRANSACTIONS_PVT.Process_Transactions
1038 (p_api_version => 2.0,
1039 p_init_msg_list => fnd_api.g_false,
1040 -- p_commit => fnd_api.g_true,
1041 p_commit => fnd_api.g_false,
1042 p_validation_level => fnd_api.g_valid_level_full,
1043 p_table => 1, -- Source table is Interface
1044 p_header_id => p_txn_header_id,
1045 x_return_status => x_return_status,
1046 x_msg_count => l_msg_count,
1047 x_msg_data => l_msg_data,
1048 x_trans_count => l_trans_count);
1049
1050 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1051 --x_error_msg := fnd_message.get;
1052 x_error_msg := l_msg_data;
1053 /* Bug 5438355: Added success message. */
1054 ELSE
1055 gme_common_pvt.log_message('PM_SAVED_CHANGES');
1056 gme_common_pvt.count_and_get(p_encoded => FND_API.G_FALSE,
1057 x_count => l_msg_count,
1058 x_data => x_error_msg);
1059 -- Bug 8234700 - Let's commit the work done by process transactions.
1060 COMMIT;
1061 END IF;
1062
1063 --- Reseting this global variable. I guess this should be done in
1064 --- GME_TRANSACTIONS_PVT.Process_Transactions
1065 GME_COMMON_PVT.g_transaction_header_id := NULL;
1066
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 IF g_debug <= gme_debug.g_log_unexpected THEN
1070 gme_debug.put_line('When others exception in Process_Transactions');
1071 END IF;
1072 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','process_transactions');
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074 x_error_msg := fnd_message.get;
1075
1076 END Process_Interface_Txn;
1077
1078 /*+========================================================================+
1079 | PROCEDURE NAME
1080 | VAlidate_Child_Lot
1081 |
1082 | USAGE
1083 |
1084 | ARGUMENTS
1085 |
1086 | RETURNS
1087 |
1088 | HISTORY
1089 | Created 26-Apr-05 Eddie Oumerretane
1090 |
1091 +========================================================================+*/
1092 PROCEDURE Validate_Child_Lot (
1093 p_org_id IN NUMBER
1094 , p_inventory_item_id IN NUMBER
1095 , p_parent_lot_number IN VARCHAR2
1096 , p_lot_number IN VARCHAR2
1097 , x_return_status OUT NOCOPY VARCHAR2
1098 , x_error_msg OUT NOCOPY VARCHAR2)
1099
1100 IS
1101 l_api_version NUMBER ;
1102 l_init_msg_list VARCHAR2(50) ;
1103 l_commit VARCHAR2 (50) ;
1104 l_return_status VARCHAR2 (50) ;
1105 l_msg_count NUMBER ;
1106 l_msg_data VARCHAR2(3000) ;
1107 BEGIN
1108
1109 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1110 l_api_version := 1.0;
1111 l_init_msg_list := fnd_api.g_false;
1112 l_commit := fnd_api.g_false;
1113
1114 INV_LOT_API_PUB.validate_child_lot (
1115 x_return_status => l_return_status
1116 , x_msg_count => l_msg_count
1117 , x_msg_data => l_msg_data
1118 , p_api_version => l_api_version
1119 , p_init_msg_list => l_init_msg_list
1120 , p_commit => l_commit
1121 , p_organization_id => p_org_id
1122 , p_inventory_item_id => p_inventory_item_id
1123 , p_parent_lot_number => p_parent_lot_number
1124 , p_child_lot_number => p_lot_number
1125 ) ;
1126
1127 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1128 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1129 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_LOT_API_PUB.VALIDATE_CHILD_LOT');
1130 fnd_msg_pub.ADD;
1131 RAISE fnd_api.g_exc_unexpected_error;
1132 END IF;
1133
1134 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1135 RAISE fnd_api.g_exc_error;
1136 END IF;
1137
1138
1139
1140 EXCEPTION
1141 WHEN NO_DATA_FOUND THEN
1142 x_return_status := FND_API.G_RET_STS_ERROR;
1143
1144 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1145 p_count => l_msg_count,
1146 p_data => x_error_msg);
1147 if( l_msg_count > 1 ) then
1148 x_error_msg:= fnd_msg_pub.get(l_msg_count, FND_API.G_FALSE);
1149 end if;
1150
1151 WHEN FND_API.G_EXC_ERROR THEN
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153
1154 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1155 p_count => l_msg_count,
1156 p_data => l_msg_data);
1157 if( l_msg_count > 1 ) then
1158 x_error_msg := fnd_msg_pub.get(l_msg_count, FND_API.G_FALSE);
1159 end if;
1160
1161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1163
1164 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1165 p_count => l_msg_count,
1166 p_data => l_msg_data);
1167 if( l_msg_count > 1 ) then
1168 x_error_msg := fnd_msg_pub.get(l_msg_count, FND_API.G_FALSE);
1169 end if;
1170
1171
1172 WHEN OTHERS THEN
1173 x_return_status := fnd_api.g_ret_sts_error;
1174
1175 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1176 p_count => l_msg_count,
1177 p_data => l_msg_data);
1178 if( l_msg_count > 1 ) then
1179 x_error_msg := fnd_msg_pub.get(l_msg_count, FND_API.G_FALSE);
1180 end if;
1181
1182 END Validate_Child_Lot;
1183
1184 /*+========================================================================+
1185 | PROCEDURE NAME
1186 | Generate_Lot_Number
1187 |
1188 | USAGE
1189 |
1190 | ARGUMENTS
1191 |
1192 | RETURNS
1193 |
1194 | HISTORY
1195 | Created 26-Apr-05 Eddie Oumerretane
1196 |
1197 +========================================================================+*/
1198 PROCEDURE Generate_Lot_Number(
1199 p_org_id IN NUMBER
1200 , p_inventory_item_id IN NUMBER
1201 , p_parent_lot_number IN VARCHAR2
1202 , x_lot_number OUT NOCOPY VARCHAR2
1203 , x_return_status OUT NOCOPY VARCHAR2
1204 , x_error_msg OUT NOCOPY VARCHAR2)
1205 IS
1206
1207 l_msg_count NUMBER;
1208
1209 BEGIN
1210
1211 IF (g_debug IS NOT NULL) THEN
1212 gme_debug.log_initialize ('MobileGenLotNumber');
1213 END IF;
1214
1215 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1216 x_error_msg := '';
1217
1218 x_lot_number := INV_LOT_API_PUB.auto_gen_lot
1219 (p_org_id => p_org_id,
1220 p_inventory_item_id => p_inventory_item_id,
1221 p_lot_generation => NULL,
1222 p_lot_uniqueness => NULL,
1223 p_lot_prefix => NULL,
1224 p_zero_pad => NULL,
1225 p_lot_length => NULL,
1226 p_transaction_date => NULL,
1227 p_revision => NULL,
1228 p_subinventory_code => NULL,
1229 p_locator_id => NULL,
1230 p_transaction_type_id => NULL,
1231 p_transaction_action_id => NULL,
1232 p_transaction_source_type_id => NULL,
1233 p_lot_number => NULL,
1234 p_api_version => 1.0,
1235 p_init_msg_list => FND_API.G_FALSE,
1236 p_commit => FND_API.G_FALSE,
1237 p_validation_level => NULL,
1238 p_parent_lot_number => p_parent_lot_number,
1239 x_return_status => x_return_status,
1240 x_msg_count => l_msg_count,
1241 x_msg_data => x_error_msg);
1242
1243 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1244 RAISE fnd_api.g_exc_error;
1245 END IF;
1246
1247 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1248 gme_debug.put_line ( 'lot_gen'
1249 || ':'
1250 || 'x_lot_number'
1251 || x_return_status);
1252 END IF;
1253
1254 EXCEPTION
1255 WHEN fnd_api.g_exc_error THEN
1256 IF g_debug <= gme_debug.g_log_unexpected THEN
1257 gme_debug.put_line ( 'GME_MOBILE_TXNS'
1258 || '.'
1259 || 'Generate_Lot_Number'
1260 || ':'
1261 || 'When others exception:'
1262 || SQLERRM);
1263 END IF;
1264
1265 x_return_status := fnd_api.g_ret_sts_error;
1266
1267 WHEN OTHERS THEN
1268 IF g_debug <= gme_debug.g_log_unexpected THEN
1269 gme_debug.put_line ( 'GME_MOBILE_TXNS'
1270 || '.'
1271 || 'Generate_Lot_Number'
1272 || ':'
1273 || 'When others exception:'
1274 || SQLERRM);
1275 END IF;
1276
1277 gme_common_pvt.count_and_get (x_count => l_msg_count,
1278 p_encoded => fnd_api.g_false,
1279 x_data => x_error_msg);
1280 x_return_status := fnd_api.g_ret_sts_unexp_error;
1281
1282 END Generate_Lot_Number;
1283
1284 /*+========================================================================+
1285 | PROCEDURE NAME
1286 | Create_Lot
1287 |
1288 | USAGE
1289 |
1290 | ARGUMENTS
1291 |
1292 | RETURNS
1293 |
1294 | HISTORY
1295 | Created 26-Apr-05 Eddie Oumerretane
1296 |
1297 +========================================================================+*/
1298 PROCEDURE Create_Lot(
1299 p_org_id NUMBER,
1300 p_inventory_item_id NUMBER,
1301 p_lot_number IN VARCHAR2,
1302 p_expiration_date IN DATE,
1303 p_supplier_lot_number IN VARCHAR2,
1304 p_grade_code IN VARCHAR2,
1305 p_ORIGINATION_DATE IN DATE,
1306 P_STATUS_ID IN NUMBER,
1307 p_RETEST_DATE IN DATE,
1308 P_MATURITY_DATE IN DATE,
1309 P_LOT_ATTRIBUTE_CATEGORY IN VARCHAR2,
1310 p_c_attribute1 IN VARCHAR2,
1311 p_c_attribute2 IN VARCHAR2,
1312 p_c_attribute3 IN VARCHAR2,
1313 p_c_attribute4 IN VARCHAR2,
1314 p_c_attribute5 IN VARCHAR2,
1315 p_c_attribute6 IN VARCHAR2,
1316 p_c_attribute7 IN VARCHAR2,
1317 p_c_attribute8 IN VARCHAR2,
1318 p_c_attribute9 IN VARCHAR2,
1319 p_c_attribute10 IN VARCHAR2,
1320 p_c_attribute11 IN VARCHAR2,
1321 p_c_attribute12 IN VARCHAR2,
1322 p_c_attribute13 IN VARCHAR2,
1323 p_c_attribute14 IN VARCHAR2,
1324 p_c_attribute15 IN VARCHAR2,
1325 p_c_attribute16 IN VARCHAR2,
1326 p_c_attribute17 IN VARCHAR2,
1327 p_c_attribute18 IN VARCHAR2,
1328 p_c_attribute19 IN VARCHAR2,
1329 p_c_attribute20 IN VARCHAR2,
1330 p_d_attribute1 IN DATE,
1331 p_d_attribute2 IN DATE,
1332 p_d_attribute3 IN DATE,
1333 p_d_attribute4 IN DATE,
1334 p_d_attribute5 IN DATE,
1335 p_d_attribute6 IN DATE,
1336 p_d_attribute7 IN DATE,
1337 p_d_attribute8 IN DATE,
1338 p_d_attribute9 IN DATE,
1339 p_d_attribute10 IN DATE,
1340 p_n_attribute1 IN NUMBER,
1341 p_n_attribute2 IN NUMBER,
1342 p_n_attribute3 IN NUMBER,
1343 p_n_attribute4 IN NUMBER,
1344 p_n_attribute5 IN NUMBER,
1345 p_n_attribute6 IN NUMBER,
1346 p_n_attribute7 IN NUMBER,
1347 p_n_attribute8 IN NUMBER,
1348 p_n_attribute9 IN NUMBER,
1349 p_n_attribute10 IN NUMBER,
1350 p_parent_lot_number IN VARCHAR2,
1351 P_ORIGINATION_TYPE IN NUMBER,
1352 P_EXPIRATION_ACTION_DATE IN DATE,
1353 P_EXPIRATION_ACTION_CODE IN VARCHAR2,
1354 P_HOLD_DATE IN DATE,
1355 P_REASON_ID IN NUMBER,
1356 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
1357 P_ATTRIBUTE1 IN VARCHAR2,
1358 P_ATTRIBUTE2 IN VARCHAR2,
1359 P_ATTRIBUTE3 IN VARCHAR2,
1360 P_ATTRIBUTE4 IN VARCHAR2,
1361 P_ATTRIBUTE5 IN VARCHAR2,
1362 P_ATTRIBUTE6 IN VARCHAR2,
1363 P_ATTRIBUTE7 IN VARCHAR2,
1364 P_ATTRIBUTE8 IN VARCHAR2,
1365 P_ATTRIBUTE9 IN VARCHAR2,
1366 P_ATTRIBUTE10 IN VARCHAR2,
1367 P_ATTRIBUTE11 IN VARCHAR2,
1368 P_ATTRIBUTE12 IN VARCHAR2,
1369 P_ATTRIBUTE13 IN VARCHAR2,
1370 P_ATTRIBUTE14 IN VARCHAR2,
1371 P_ATTRIBUTE15 IN VARCHAR2,
1372 p_disable_flag IN NUMBER,
1373 p_COLOR IN VARCHAR2,
1374 p_VOLUME IN NUMBER,
1375 p_VOLUME_UOM IN VARCHAR2,
1376 p_PLACE_OF_ORIGIN IN VARCHAR2,
1377 p_BEST_BY_DATE IN DATE,
1378 p_LENGTH IN NUMBER,
1379 p_LENGTH_UOM IN VARCHAR2,
1380 p_RECYCLED_CONTENT IN NUMBER,
1381 p_THICKNESS IN NUMBER,
1382 p_THICKNESS_UOM IN VARCHAR2,
1383 p_WIDTH IN NUMBER,
1384 p_WIDTH_UOM IN VARCHAR2,
1385 p_CURL_WRINKLE_FOLD IN VARCHAR2,
1386 p_territory_code IN VARCHAR2,
1387 p_date_code IN VARCHAR2,
1388 p_change_date IN DATE,
1389 p_age IN NUMBER,
1390 p_item_size IN NUMBER,
1391 p_vendor_name IN VARCHAR2,
1392 x_return_status OUT NOCOPY VARCHAR2,
1393 x_error_msg OUT NOCOPY VARCHAR2)
1394
1395 IS
1396 l_return_status VARCHAR2(1) ;
1397 l_msg_data VARCHAR2(3000) ;
1398 l_msg_count NUMBER ;
1399 x_lot_rec MTL_LOT_NUMBERS%ROWTYPE; -- for lot api
1400 l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE; -- for lot api
1401 l_api_version NUMBER;
1402 l_init_msg_list VARCHAR2(100);
1403 l_commit VARCHAR2(100);
1404 l_validation_level NUMBER;
1405 l_source NUMBER;
1406 l_row_id ROWID;
1407
1408 /* Cursor to check if a lot already exists*/
1409 CURSOR c_lot_exists IS
1410 SELECT 1
1411 FROM mtl_lot_numbers
1412 WHERE organization_id = p_org_id
1413 AND inventory_item_id = p_inventory_item_id
1414 AND lot_number = p_lot_number ;
1415
1416 l_lot_count NUMBER := 0;
1417
1418 BEGIN
1419
1420 OPEN c_lot_exists;
1421 FETCH c_lot_exists INTO l_lot_count;
1422 CLOSE c_lot_exists;
1423
1424
1425 l_return_status := NULL;
1426 l_msg_data := NULL;
1427 l_msg_count := NULL;
1428 l_source := NULL ;
1429 l_api_version := 1.0;
1430 l_init_msg_list := 'T';
1431 l_commit := 'F';
1432 l_validation_level := 100;
1433 l_in_lot_rec.organization_id := p_org_id ;
1434 l_in_lot_rec.inventory_item_id := p_inventory_item_id ;
1435 l_in_lot_rec.expiration_date := p_expiration_date;
1436 l_in_lot_rec.grade_code := p_grade_code ;
1437 l_in_lot_rec.lot_number := p_lot_number ;
1438 l_in_lot_rec.parent_lot_number := p_parent_lot_number;
1439 l_in_lot_rec.origination_date := p_ORIGINATION_DATE;
1440 l_in_lot_rec.retest_date := p_RETEST_DATE ;
1441 l_in_lot_rec.maturity_date := P_MATURITY_DATE;
1442 l_in_lot_rec.attribute_category := P_ATTRIBUTE_CATEGORY;
1443 l_in_lot_rec.origination_type := P_ORIGINATION_TYPE;
1444 l_in_lot_rec.hold_date := P_HOLD_DATE;
1445 l_in_lot_rec.expiration_action_code := P_EXPIRATION_ACTION_CODE;
1446 l_in_lot_rec.expiration_action_date := P_EXPIRATION_ACTION_DATE;
1447 l_in_lot_rec.status_id := P_STATUS_ID;
1448 l_in_lot_rec.supplier_lot_number := P_SUPPLIER_LOT_NUMBER;
1449 l_in_lot_rec.LOT_ATTRIBUTE_CATEGORY := P_LOT_ATTRIBUTE_CATEGORY;
1450 l_in_lot_rec.ATTRIBUTE1:= P_ATTRIBUTE1;
1451 l_in_lot_rec.ATTRIBUTE2:= P_ATTRIBUTE2;
1452 l_in_lot_rec.ATTRIBUTE3:= P_ATTRIBUTE3;
1453 l_in_lot_rec.ATTRIBUTE4:= P_ATTRIBUTE4;
1454 l_in_lot_rec.ATTRIBUTE5:= P_ATTRIBUTE5;
1455 l_in_lot_rec.ATTRIBUTE6:= P_ATTRIBUTE6;
1456 l_in_lot_rec.ATTRIBUTE7:= P_ATTRIBUTE7;
1457 l_in_lot_rec.ATTRIBUTE8:= P_ATTRIBUTE8;
1458 l_in_lot_rec.ATTRIBUTE9:= P_ATTRIBUTE9;
1459 l_in_lot_rec.ATTRIBUTE10:= P_ATTRIBUTE10;
1460 l_in_lot_rec.ATTRIBUTE11:= P_ATTRIBUTE11;
1461 l_in_lot_rec.ATTRIBUTE12:= P_ATTRIBUTE12;
1462 l_in_lot_rec.ATTRIBUTE13:= P_ATTRIBUTE13;
1463 l_in_lot_rec.ATTRIBUTE14:= P_ATTRIBUTE14;
1464 l_in_lot_rec.ATTRIBUTE15:= P_ATTRIBUTE15;
1465 l_in_lot_rec.C_ATTRIBUTE1:= P_C_ATTRIBUTE1;
1466 l_in_lot_rec.C_ATTRIBUTE2:= P_C_ATTRIBUTE2;
1467 l_in_lot_rec.C_ATTRIBUTE3:= P_C_ATTRIBUTE3;
1468 l_in_lot_rec.C_ATTRIBUTE4:= P_C_ATTRIBUTE4;
1469 l_in_lot_rec.C_ATTRIBUTE5:= P_C_ATTRIBUTE5;
1470 l_in_lot_rec.C_ATTRIBUTE6:= P_C_ATTRIBUTE6;
1471 l_in_lot_rec.C_ATTRIBUTE7:= P_C_ATTRIBUTE7;
1472 l_in_lot_rec.C_ATTRIBUTE8:= P_C_ATTRIBUTE8;
1473 l_in_lot_rec.C_ATTRIBUTE9:= P_C_ATTRIBUTE9;
1474 l_in_lot_rec.C_ATTRIBUTE10:= P_C_ATTRIBUTE10;
1475 l_in_lot_rec.C_ATTRIBUTE11:= P_C_ATTRIBUTE11;
1476 l_in_lot_rec.C_ATTRIBUTE12:= P_C_ATTRIBUTE12;
1477 l_in_lot_rec.C_ATTRIBUTE13:= P_C_ATTRIBUTE13;
1478 l_in_lot_rec.C_ATTRIBUTE14:= P_C_ATTRIBUTE14;
1479 l_in_lot_rec.C_ATTRIBUTE15:= P_C_ATTRIBUTE15;
1480 l_in_lot_rec.C_ATTRIBUTE16:= P_C_ATTRIBUTE16;
1481 l_in_lot_rec.C_ATTRIBUTE17:= P_C_ATTRIBUTE17;
1482 l_in_lot_rec.C_ATTRIBUTE18:= P_C_ATTRIBUTE18;
1483 l_in_lot_rec.C_ATTRIBUTE19:= P_C_ATTRIBUTE19;
1484 l_in_lot_rec.C_ATTRIBUTE20:= P_C_ATTRIBUTE20;
1485 l_in_lot_rec.D_ATTRIBUTE1:= P_D_ATTRIBUTE1;
1486 l_in_lot_rec.D_ATTRIBUTE2:= P_D_ATTRIBUTE2;
1487 l_in_lot_rec.D_ATTRIBUTE3:= P_D_ATTRIBUTE3;
1488 l_in_lot_rec.D_ATTRIBUTE4:= P_D_ATTRIBUTE4;
1489 l_in_lot_rec.D_ATTRIBUTE5:= P_D_ATTRIBUTE5;
1490 l_in_lot_rec.D_ATTRIBUTE6:= P_D_ATTRIBUTE6;
1491 l_in_lot_rec.D_ATTRIBUTE7:= P_D_ATTRIBUTE7;
1492 l_in_lot_rec.D_ATTRIBUTE8:= P_D_ATTRIBUTE8;
1493 l_in_lot_rec.D_ATTRIBUTE9:= P_D_ATTRIBUTE9;
1494 l_in_lot_rec.D_ATTRIBUTE10:= P_D_ATTRIBUTE10;
1495 l_in_lot_rec.N_ATTRIBUTE1:= P_N_ATTRIBUTE1;
1496 l_in_lot_rec.N_ATTRIBUTE2:= P_N_ATTRIBUTE2;
1497 l_in_lot_rec.N_ATTRIBUTE3:= P_N_ATTRIBUTE3;
1498 l_in_lot_rec.N_ATTRIBUTE4:= P_N_ATTRIBUTE4;
1499 l_in_lot_rec.N_ATTRIBUTE5:= P_N_ATTRIBUTE5;
1500 l_in_lot_rec.N_ATTRIBUTE6:= P_N_ATTRIBUTE6;
1501 l_in_lot_rec.N_ATTRIBUTE7:= P_N_ATTRIBUTE7;
1502 l_in_lot_rec.N_ATTRIBUTE8:= P_N_ATTRIBUTE8;
1503 l_in_lot_rec.N_ATTRIBUTE9:= P_N_ATTRIBUTE9;
1504 l_in_lot_rec.N_ATTRIBUTE10:= P_N_ATTRIBUTE10;
1505 l_in_lot_rec.disable_flag := p_disable_flag ;
1506 l_in_lot_rec.date_code := p_date_code;
1507 l_in_lot_rec.change_date := p_change_date ;
1508 l_in_lot_rec.age := p_age ;
1509 l_in_lot_rec.item_size := p_item_size ;
1510 l_in_lot_rec.color := p_color ;
1511 l_in_lot_rec.volume := p_volume ;
1512 l_in_lot_rec.volume_uom := p_volume_uom ;
1513 l_in_lot_rec.place_of_origin := p_place_of_origin ;
1514 l_in_lot_rec.best_by_date := p_best_by_date ;
1515 l_in_lot_rec.length := p_length ;
1516 l_in_lot_rec.length_uom := p_length_uom ;
1517 l_in_lot_rec.recycled_content := p_recycled_content ;
1518 l_in_lot_rec.thickness := p_thickness ;
1519 l_in_lot_rec.thickness_uom := p_thickness_uom ;
1520 l_in_lot_rec.width := p_width ;
1521 l_in_lot_rec.width_uom := p_width_uom ;
1522 l_in_lot_rec.territory_code := p_territory_code ;
1523 l_in_lot_rec.vendor_name := p_vendor_name ;
1524
1525 l_row_id := NULL;
1526
1527 IF l_lot_count = 0 THEN
1528
1529 INV_LOT_API_PUB.Create_Inv_lot(
1530 x_return_status => l_return_status
1531 , x_msg_count => l_msg_count
1532 , x_msg_data => x_error_msg
1533 , x_lot_rec => x_lot_rec
1534 , p_lot_rec => l_in_lot_rec
1535 , p_source => l_source
1536 , p_api_version => l_api_version
1537 , p_init_msg_list => l_init_msg_list
1538 , p_commit => l_commit
1539 , p_validation_level => l_validation_level
1540 , p_origin_txn_id => NULL
1541 , x_row_id => l_row_id
1542 );
1543
1544 IF l_return_status <> 'S' THEN
1545 FND_MSG_PUB.count_and_get
1546 ( p_count => l_msg_count
1547 , p_data => x_error_msg
1548 );
1549 END IF;
1550
1551 END IF;
1552
1553 x_return_status := l_return_status ;
1554
1555 END Create_Lot;
1556
1557 /*+========================================================================+
1558 | PROCEDURE NAME
1559 | Validate_Txn_date
1560 |
1561 | USAGE
1562 |
1563 | ARGUMENTS
1564 |
1565 | RETURNS
1566 |
1567 | HISTORY
1568 | Created 26-Apr-05 Eddie Oumerretane
1569 |
1570 +========================================================================+*/
1571 PROCEDURE Validate_Txn_Date(p_org_id IN NUMBER,
1572 p_txn_date IN DATE,
1573 x_period_id OUT NOCOPY NUMBER,
1574 x_return_status OUT NOCOPY VARCHAR2,
1575 x_error_msg OUT NOCOPY VARCHAR2) IS
1576 l_open_past_period BOOLEAN;
1577 DATE_ERROR EXCEPTION;
1578 BEGIN
1579
1580 IF (g_debug IS NOT NULL) THEN
1581 gme_debug.log_initialize ('MobileValTxnDate');
1582 END IF;
1583
1584 x_return_status := FND_API.G_RET_STS_SUCCESS;
1585 x_error_msg := ' ';
1586
1587 --- Check that transaction date is not in the future
1588 IF p_txn_date > SYSDATE THEN
1589 fnd_message.set_name('INV', 'INV_INT_TDATEEX');
1590 RAISE DATE_ERROR;
1591 END IF;
1592
1593 --- Check that transaction date falls within an open period
1594 INVTTMTX.tdatechk(org_id => p_org_id
1595 , transaction_date => p_txn_date
1596 , period_id => x_period_id
1597 , open_past_period => l_open_past_period);
1598
1599 IF x_period_id <= 0 THEN
1600 fnd_message.set_name('INV', 'INV_NO_OPEN_PERIOD');
1601 RAISE DATE_ERROR;
1602 END IF;
1603
1604
1605 EXCEPTION
1606 WHEN DATE_ERROR THEN
1607 x_period_id := -1;
1608 x_error_msg := fnd_message.get;
1609 x_return_status := FND_API.G_RET_STS_ERROR;
1610
1611 WHEN OTHERS THEN
1612 IF g_debug <= gme_debug.g_log_unexpected THEN
1613 gme_debug.put_line('When others exception in Validate Txn Date');
1614 END IF;
1615 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Validate_Txn_Date');
1616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1617 x_error_msg := fnd_message.get;
1618
1619 END Validate_Txn_Date;
1620
1621 /*+========================================================================+
1622 | PROCEDURE NAME
1623 | Validate_Batch_For_IB
1624 |
1625 | USAGE
1626 |
1627 | ARGUMENTS
1628 |
1629 | RETURNS
1630 |
1631 | HISTORY
1632 | Created 26-Apr-05 Eddie Oumerretane
1633 |
1634 +========================================================================+*/
1635 PROCEDURE Validate_Batch_For_IB (p_organization_id IN NUMBER,
1636 p_batch_id IN NUMBER,
1637 x_return_status OUT NOCOPY VARCHAR2,
1638 x_error_msg OUT NOCOPY VARCHAR2) IS
1639
1640 CURSOR Cur_batch_no IS
1641 SELECT b.parentline_id
1642 FROM gme_batch_header_vw b
1643 WHERE batch_type = 0
1644 AND organization_id = p_organization_id
1645 AND batch_id = p_batch_id;
1646
1647 CURSOR cur_get_step_status IS
1648 SELECT s.batchstep_no
1649 FROM gme_batch_steps s,
1650 gme_batch_step_items i,
1651 gme_material_details m
1652 WHERE m.batch_id = p_batch_id
1653 AND s.batch_id = p_batch_id
1654 AND i.batch_id = p_batch_id
1655 AND m.release_type = 2
1656 AND s.batchstep_id = i.batchstep_id
1657 AND i.material_detail_id = m.material_detail_id
1658 AND s.step_status = 4;
1659
1660 l_batchstep_no NUMBER ;
1661 l_parent_line_id NUMBER;
1662 BATCH_NOT_VALID EXCEPTION;
1663
1664 BEGIN
1665
1666 IF (g_debug IS NOT NULL) THEN
1667 gme_debug.log_initialize ('MobileValBatchForIB');
1668 END IF;
1669
1670 x_return_status := FND_API.G_RET_STS_SUCCESS;
1671 x_error_msg := ' ';
1672
1673 OPEN Cur_batch_no;
1674 FETCH Cur_batch_no INTO l_parent_line_id;
1675 IF Cur_batch_no%NOTFOUND THEN
1676 FND_MESSAGE.SET_NAME('GME', 'PM_INVBATCHNO');
1677 FND_MESSAGE.SET_TOKEN('STEP_NO', l_batchstep_no);
1678 CLOSE Cur_batch_no;
1679 RAISE BATCH_NOT_VALID;
1680 END IF;
1681
1682 CLOSE Cur_batch_no;
1683
1684 -- 2402919 Dont allow IB on batch that has any incrmental rel
1685 -- item associated to a closed step.
1686 OPEN cur_get_step_status;
1687 FETCH cur_get_step_status INTO l_batchstep_no;
1688 IF cur_get_step_status%FOUND THEN
1689 FND_MESSAGE.SET_NAME('GME','GME_STEP_CLOSED_ERR');
1690 FND_MESSAGE.SET_TOKEN('STEP_NO', l_batchstep_no);
1691 CLOSE cur_get_step_status;
1692 RAISE BATCH_NOT_VALID;
1693 END IF;
1694
1695 CLOSE cur_get_step_status;
1696
1697 IF l_parent_line_id > 0 THEN
1698 FND_MESSAGE.SET_NAME('GME', 'PM_INVALID_PHANTOM_ACTION');
1699 RAISE BATCH_NOT_VALID;
1700 END IF;
1701
1702
1703 EXCEPTION
1704 WHEN BATCH_NOT_VALID THEN
1705 x_return_status := FND_API.G_RET_STS_ERROR;
1706 x_error_msg := fnd_message.get;
1707
1708 WHEN OTHERS THEN
1709 IF g_debug <= gme_debug.g_log_unexpected THEN
1710 gme_debug.put_line('When others exception in Validate Batch For IB');
1711 END IF;
1712 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Validate_Batch_For_IB');
1713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1714 x_error_msg := fnd_message.get;
1715
1716 END Validate_Batch_For_IB;
1717
1718 /*+========================================================================+
1719 | PROCEDURE NAME
1720 | Validate_Item_For_IB
1721 |
1722 | USAGE
1723 |
1724 | ARGUMENTS
1725 |
1726 | RETURNS
1727 |
1728 | HISTORY
1729 | Created 26-Apr-05 Eddie Oumerretane
1730 | 11-Jul-06 Shrikant Nene Bug#5331639. Compare wip_plan_qty instead
1731 | of plan_qty
1732 |
1733 +========================================================================+*/
1734 PROCEDURE Validate_Item_For_IB (p_organization_id IN NUMBER,
1735 p_batch_id IN NUMBER,
1736 p_material_detail_id IN NUMBER,
1737 x_return_status OUT NOCOPY VARCHAR2,
1738 x_error_msg OUT NOCOPY VARCHAR2) IS
1739
1740 CURSOR Cur_batch_det IS
1741 SELECT release_type,
1742 -- Bug#5331639. Compare wip_plan_qty instead of plan_qty
1743 NVL(wip_plan_qty, 0) wip_planned_qty,
1744 NVL(phantom_type, 0) phantom_ind,
1745 phantom_line_id
1746 FROM gme_material_details
1747 WHERE material_detail_id = p_material_detail_id
1748 AND organization_id = p_organization_id
1749 AND batch_id = p_batch_id;
1750
1751 l_mat_rec Cur_batch_det%ROWTYPE;
1752 ITEM_NOT_VALID EXCEPTION;
1753
1754 BEGIN
1755
1756 IF (g_debug IS NOT NULL) THEN
1757 gme_debug.log_initialize ('MobileValItemForIB');
1758 END IF;
1759
1760 x_return_status := FND_API.G_RET_STS_SUCCESS;
1761 x_error_msg := ' ';
1762
1763 OPEN Cur_batch_det;
1764 FETCH Cur_batch_det INTO l_mat_rec;
1765 CLOSE Cur_batch_det;
1766
1767 -- If the ingredient is a phantom and it has not been exploded then we
1768 -- cannot consume it.
1769
1770 IF l_mat_rec.phantom_ind <> 0 THEN
1771 IF l_mat_rec.phantom_line_id IS NULL THEN
1772 FND_MESSAGE.SET_NAME('GME', 'GME_ISSUE_PHANTOM_NOT_EXPLOD');
1773 RAISE ITEM_NOT_VALID;
1774 END IF;
1775 END IF;
1776
1777 IF l_mat_rec.release_type IN (0,3) THEN
1778 FND_MESSAGE.SET_NAME('GME', 'GME_API_INV_RELEASE_TYPE');
1779 RAISE ITEM_NOT_VALID;
1780 END IF;
1781
1782 -- Bug#5331639. Compare wip_plan_qty instead of plan_qty
1783 IF l_mat_rec.wip_planned_qty = 0 THEN
1784 FND_MESSAGE.SET_NAME('GME', 'GME_API_INV_PLAN_QTY_PC');
1785 RAISE ITEM_NOT_VALID;
1786 END IF;
1787
1788
1789 EXCEPTION
1790 WHEN ITEM_NOT_VALID THEN
1791 x_return_status := FND_API.G_RET_STS_ERROR;
1792 x_error_msg := fnd_message.get;
1793
1794 WHEN OTHERS THEN
1795 IF g_debug <= gme_debug.g_log_unexpected THEN
1796 gme_debug.put_line('When others exception in Validate Item For IB');
1797 END IF;
1798 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Validate_Item_For_IB');
1799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1800 x_error_msg := fnd_message.get;
1801
1802 END Validate_Item_For_IB;
1803
1804
1805 /*+========================================================================+
1806 | PROCEDURE NAME
1807 | Backflush_Material
1808 |
1809 | USAGE
1810 |
1811 | ARGUMENTS
1812 |
1813 | RETURNS
1814 |
1815 | HISTORY
1816 | Created 26-Apr-05 Eddie Oumerretane
1817 |
1818 +========================================================================+*/
1819 PROCEDURE Backflush_Material (p_batch_id IN NUMBER,
1820 p_material_detail_id IN NUMBER,
1821 p_allow_neg_inv IN NUMBER,
1822 p_qty IN NUMBER,
1823 p_qty_type IN NUMBER,
1824 p_trans_date IN DATE,
1825 p_adjust_cmplt IN VARCHAR2,
1826 p_login_id IN NUMBER,
1827 p_user_id IN NUMBER,
1828 p_org_code IN VARCHAR2,
1829 x_return_status OUT NOCOPY VARCHAR2,
1830 x_error_msg OUT NOCOPY VARCHAR2) IS
1831
1832 l_msg_count NUMBER;
1833 l_msg_list VARCHAR2(2000);
1834 l_msg_data VARCHAR2(2000);
1835 l_msg_index NUMBER;
1836 l_batch_header GME_BATCH_HEADER%ROWTYPE;
1837 l_material_details GME_MATERIAL_DETAILS%ROWTYPE;
1838 l_exception_material_tab GME_COMMON_PVT.exceptions_tab;
1839
1840 BACKFLUSH_ERROR EXCEPTION;
1841
1842 BEGIN
1843
1844 IF (g_debug IS NOT NULL) THEN
1845 gme_debug.log_initialize ('MobileBackflushMtl');
1846 gme_debug.put_line('BatchId id = '||p_batch_id);
1847 gme_debug.put_line('Mtl DetailId = '||p_material_detail_id);
1848 gme_debug.put_line('Backflush Qty = '||p_qty);
1849 gme_debug.put_line('Qty Type = '||p_qty_type);
1850 gme_debug.put_line('Org Code = '||p_org_code);
1851 END IF;
1852
1853 fnd_profile.put('USER_ID',to_char(p_user_id));
1854 fnd_profile.put('LOGIN_ID',to_char(p_login_id));
1855
1856 x_return_status := FND_API.G_RET_STS_SUCCESS;
1857 x_error_msg := ' ';
1858
1859 l_material_details.material_detail_id := p_material_detail_id;
1860 l_batch_header.batch_id := p_batch_id;
1861
1862 GME_API_PUB.Incremental_Backflush
1863 (p_api_version => 2.0
1864 ,p_validation_level => gme_common_pvt.g_max_errors
1865 ,p_init_msg_list => fnd_api.g_false
1866 ,p_commit => fnd_api.g_true
1867 ,x_message_count => l_msg_count
1868 ,x_message_list => l_msg_list
1869 ,x_return_status => x_return_status
1870 ,p_batch_header_rec => l_batch_header
1871 ,p_org_code => p_org_code
1872 ,p_material_detail_rec => l_material_details
1873 ,p_qty => p_qty
1874 ,p_qty_type => p_qty_type
1875 ,p_trans_date => p_trans_date
1876 ,p_ignore_exception => fnd_api.g_false --- Do not proceed if exceptios
1877 ,p_adjust_cmplt => fnd_api.g_false --- Completed batches not supported
1878 ,x_exception_material_tbl => l_exception_material_tab);
1879
1880 IF (g_debug IS NOT NULL) THEN
1881 gme_debug.put_line('Return Status = '||x_return_status);
1882 gme_debug.put_line('Error Msg = '||l_msg_list);
1883 END IF;
1884
1885 IF x_return_status = 'X' THEN
1886 FND_MESSAGE.SET_NAME('GME','GME_INCR_BACKFLUSH_EXCEPTIONS');
1887 x_error_msg := FND_MESSAGE.GET;
1888 RAISE BACKFLUSH_ERROR;
1889 ELSIF x_return_status <> 'S' THEN
1890 FND_MSG_PUB.GET(p_msg_index => 1,
1891 p_data => x_error_msg,
1892 p_encoded => 'F',
1893 p_msg_index_out => l_msg_index);
1894 RAISE BACKFLUSH_ERROR;
1895 /* Bug 5438355: Added success message */
1896 ELSE
1897 x_error_msg := l_msg_list;
1898 END IF;
1899
1900 EXCEPTION
1901 WHEN BACKFLUSH_ERROR THEN
1902 x_return_status := FND_API.G_RET_STS_ERROR;
1903 ROLLBACK;
1904
1905 WHEN OTHERS THEN
1906 IF g_debug <= gme_debug.g_log_unexpected THEN
1907 gme_debug.put_line('When others exception in Backflush Material');
1908 END IF;
1909 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Backflush_Material');
1910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1911 x_error_msg := fnd_message.get;
1912 ROLLBACK;
1913
1914 END Backflush_Material;
1915
1916 /*+========================================================================+
1917 | PROCEDURE NAME
1918 | Save_Resource_Usage
1919 |
1920 | USAGE
1921 |
1922 | ARGUMENTS
1923 |
1924 | RETURNS
1925 |
1926 | HISTORY
1927 | Created 26-Apr-05 Eddie Oumerretane
1928 |
1929 +========================================================================+*/
1930 PROCEDURE Save_Resource_Usage( p_resource_id IN NUMBER
1931 , p_usage IN VARCHAR2
1932 , p_count IN VARCHAR2
1933 , p_qty IN VARCHAR2
1934 , p_uname IN VARCHAR2
1935 , p_uid IN NUMBER
1936 , x_result OUT NOCOPY NUMBER
1937 , x_error_msg OUT NOCOPY VARCHAR2
1938 )
1939 IS
1940 l_msg_count NUMBER;
1941 l_msg_list VARCHAR2(2000);
1942 l_return_status VARCHAR2(1);
1943
1944 i NUMBER;
1945 message VARCHAR2(2000);
1946 dummy NUMBER;
1947 l_input_rec GME_BATCH_STEP_RESOURCES%ROWTYPE;
1948 l_output_rec GME_BATCH_STEP_RESOURCES%ROWTYPE;
1949 BEGIN
1950
1951 IF (g_debug IS NOT NULL) THEN
1952 gme_debug.log_initialize ('MobileSaveRsrcUsage');
1953 END IF;
1954
1955 fnd_profile.put('USER_ID',to_char(p_uid));
1956 gme_common_pvt.g_user_ident := p_uid;
1957
1958 l_input_rec.batchstep_resource_id := p_resource_id;
1959 l_input_rec.actual_rsrc_count := to_number(p_count);
1960 l_input_rec.actual_rsrc_usage := to_number(p_usage);
1961 l_input_rec.actual_rsrc_qty := to_number(p_qty);
1962 l_input_rec.last_update_date := SYSDATE;
1963 l_input_rec.last_updated_by := p_uid;
1964
1965
1966 GME_API_PUB.Update_Batchstep_Resource
1967 ( p_api_version => 2.0
1968 , p_init_msg_list => FND_API.G_TRUE
1969 , p_commit => FND_API.G_TRUE
1970 , p_batchstep_resource_rec => l_input_rec
1971 , x_batchstep_resource_rec => l_output_rec
1972 , x_message_count => l_msg_count
1973 , x_message_list => l_msg_list
1974 , x_return_status => l_return_status
1975 );
1976
1977 IF l_msg_count > 0
1978 THEN
1979 FOR i IN 1..l_msg_count
1980 LOOP
1981 FND_MSG_PUB.get
1982 ( p_msg_index => i
1983 , p_data => message
1984 , p_encoded => fnd_api.g_false
1985 , p_msg_index_out => dummy
1986 );
1987
1988 IF i = 1
1989 THEN
1990 x_error_msg := message;
1991 END IF;
1992
1993 END LOOP;
1994 END IF;
1995
1996 IF l_return_status = 'S' THEN
1997 x_result := 0;
1998 ELSIF l_return_status = 'E' THEN
1999 x_result := -1;
2000 ELSIF l_return_status = 'U' THEN
2001 x_result := -2;
2002 ELSE
2003 x_result := -3;
2004 END IF;
2005
2006 EXCEPTION
2007 WHEN OTHERS THEN
2008 IF g_debug <= gme_debug.g_log_unexpected THEN
2009 gme_debug.put_line('When others exception in Save Resource Usage');
2010 END IF;
2011 x_result := -4;
2012 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Save_Resource_Usage');
2013 x_error_msg := fnd_message.get;
2014 ROLLBACK;
2015
2016 END Save_Resource_Usage;
2017
2018 /*+========================================================================+
2019 | PROCEDURE NAME
2020 | Fetch_Product_Pending_Lots
2021 |
2022 | USAGE
2023 |
2024 | ARGUMENTS
2025 |
2026 | RETURNS
2027 |
2028 | HISTORY
2029 | Created 26-Apr-05 Eddie Oumerretane
2030 | Bug#5236906. 09-Jun-06 Namit Singhi. Modified query to give Parent Lot too.
2031 |
2032 +========================================================================+*/
2033 PROCEDURE Fetch_Product_Pending_Lots(p_batch_id IN NUMBER,
2034 p_material_detail_id IN NUMBER,
2035 p_lot_number IN VARCHAR2,
2036 x_return_status OUT NOCOPY VARCHAR2,
2037 x_error_msg OUT NOCOPY VARCHAR2,
2038 x_lot_cursor OUT NOCOPY t_genref)
2039 IS
2040 l_date_format VARCHAR2(100);
2041 BEGIN
2042
2043 IF (g_debug IS NOT NULL) THEN
2044 gme_debug.log_initialize ('MobileFetchPendingLot');
2045 END IF;
2046
2047 x_return_status := FND_API.G_RET_STS_SUCCESS;
2048 x_error_msg := ' ';
2049
2050 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
2051
2052 IF l_date_format IS NULL THEN
2053 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
2054 END IF;
2055
2056 OPEN x_lot_cursor FOR
2057 SELECT
2058 l.pending_product_lot_id,
2059 l.lot_number,
2060 n.parent_lot_number, --nsinghi bug#5236906. Add this column
2061 l.revision,
2062 l.sequence,
2063 l.quantity,
2064 NVL(l.secondary_quantity, 0),
2065 NVL (l.reason_id, -1),
2066 reason_name,
2067 TO_CHAR(l.last_update_date, 'MM/DD/YYYY HH24:MI:SS')
2068 FROM
2069 gme_pending_product_lots l,
2070 mtl_transaction_reasons r,
2071 gme_material_details m, -- nsinghi bug#5236906. Add join to gme_material_details and MLN
2072 mtl_lot_numbers n
2073 WHERE
2074 l.batch_id = p_batch_id AND
2075 l.material_detail_id = p_material_detail_id AND
2076 l.lot_number = NVL(p_lot_number, l.lot_number) AND
2077 m.material_detail_id = l.material_detail_id AND -- nsinghi bug#5236906. Added where conditions
2078 m.inventory_item_id = n.inventory_item_id AND
2079 m.organization_id = n.organization_id AND
2080 l.lot_number = n.lot_number AND
2081 l.reason_id = r.reason_id(+);
2082
2083 EXCEPTION
2084 WHEN OTHERS THEN
2085 IF g_debug <= gme_debug.g_log_unexpected THEN
2086 gme_debug.put_line('When others exception in fetch Pending Lots');
2087 END IF;
2088 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_pending_product_lots');
2089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2090 x_error_msg := fnd_message.get;
2091
2092 END Fetch_Product_Pending_Lots;
2093
2094
2095 PROCEDURE Fetch_Rev_Product_Pending_Lots(p_batch_id IN NUMBER,
2096 p_material_detail_id IN NUMBER,
2097 p_lot_number IN VARCHAR2,
2098 p_rev_control IN NUMBER,
2099 x_return_status OUT NOCOPY VARCHAR2,
2100 x_error_msg OUT NOCOPY VARCHAR2,
2101 x_lot_cursor OUT NOCOPY t_genref)
2102 IS
2103 l_date_format VARCHAR2(100);
2104 BEGIN
2105
2106 IF (g_debug IS NOT NULL) THEN
2107 gme_debug.log_initialize ('MobileFetchRevPendingLot');
2108 END IF;
2109
2110 x_return_status := FND_API.G_RET_STS_SUCCESS;
2111 x_error_msg := ' ';
2112
2113 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
2114
2115 IF l_date_format IS NULL THEN
2116 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
2117 END IF;
2118
2119 OPEN x_lot_cursor FOR
2120 SELECT
2121 l.pending_product_lot_id,
2122 l.lot_number,
2123 n.parent_lot_number,
2124 l.revision,
2125 l.sequence,
2126 l.quantity,
2127 NVL(l.secondary_quantity, 0),
2128 NVL (l.reason_id, -1),
2129 reason_name,
2130 TO_CHAR(l.last_update_date, 'MM/DD/YYYY HH24:MI:SS')
2131 FROM
2132 gme_pending_product_lots l,
2133 mtl_transaction_reasons r,
2134 gme_material_details m,
2135 mtl_lot_numbers n
2136 WHERE
2137 l.batch_id = p_batch_id AND
2138 l.material_detail_id = p_material_detail_id AND
2139 l.lot_number = NVL(p_lot_number, l.lot_number) AND
2140 (p_rev_control = 0 OR (p_rev_control = 1 AND l.revision IS NOT NULL)) AND
2141 m.material_detail_id = l.material_detail_id AND
2142 m.inventory_item_id = n.inventory_item_id AND
2143 m.organization_id = n.organization_id AND
2144 l.lot_number = n.lot_number AND
2145 l.reason_id = r.reason_id(+);
2146
2147 EXCEPTION
2148 WHEN OTHERS THEN
2149 IF g_debug <= gme_debug.g_log_unexpected THEN
2150 gme_debug.put_line('When others exception in fetch Rev Pending Lots');
2151 END IF;
2152 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_rev_pending_product_lots');
2153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2154 x_error_msg := fnd_message.get;
2155
2156 END Fetch_Rev_Product_Pending_Lots;
2157
2158 /*+========================================================================+
2159 | PROCEDURE NAME
2160 | Create_Product_Pending_Lots
2161 |
2162 | USAGE
2163 |
2164 | ARGUMENTS
2165 |
2166 | RETURNS
2167 |
2168 | HISTORY
2169 | Created 26-Apr-05 Eddie Oumerretane
2170 |
2171 +========================================================================+*/
2172 PROCEDURE Create_Product_Pending_Lot(p_batch_id IN NUMBER,
2173 p_material_detail_id IN NUMBER,
2174 p_lot_number IN VARCHAR2,
2175 p_revision IN VARCHAR2,
2176 p_sequence IN NUMBER,
2177 p_qty IN NUMBER,
2178 p_sec_qty IN NUMBER,
2179 p_reason_id IN NUMBER,
2180 p_user_id IN NUMBER,
2181 p_login_id IN NUMBER,
2182 p_org_code IN VARCHAR2,
2183 p_create_lot IN VARCHAR2,
2184 x_return_status OUT NOCOPY VARCHAR2,
2185 x_error_msg OUT NOCOPY VARCHAR2)
2186 IS
2187 l_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
2188 l_out_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
2189 l_material_detail_rec gme_material_details%ROWTYPE;
2190 l_in_material_detail_rec gme_material_details%ROWTYPE;
2191 l_batch_header_rec gme_batch_header%ROWTYPE;
2192 l_message_count NUMBER;
2193 l_message_list VARCHAR2(2000);
2194 l_create_lot VARCHAR2(6);
2195 l_duom_ctl VARCHAR2(30);
2196
2197 -- nsinghi bug#5236702. Set secondary qty only if item is DUOM controlled.
2198 CURSOR get_item_duom_ctl IS
2199 SELECT NVL(tracking_quantity_ind, 'P')
2200 FROM mtl_system_items_b msi, gme_material_details gmd
2201 WHERE gmd.inventory_item_id = msi.inventory_item_id AND
2202 gmd.organization_id = msi.organization_id AND
2203 gmd.material_detail_id = p_material_detail_id;
2204
2205 BEGIN
2206
2207 IF p_create_lot = 'TRUE' THEN
2208 l_create_lot := fnd_api.g_true;
2209 ELSE
2210 l_create_lot := fnd_api.g_false;
2211 END IF;
2212
2213 IF (g_debug IS NOT NULL) THEN
2214 gme_debug.log_initialize ('MobileCrePendingLot');
2215 gme_debug.put_line('New lot = ' || p_create_lot);
2216 gme_debug.put_line('Material Detail Id = ' || p_material_detail_id);
2217 gme_debug.put_line('Sequence = ' || p_sequence);
2218 gme_debug.put_line('Revision = ' || p_revision);
2219 gme_debug.put_line('Lot number = ' || p_lot_number);
2220 gme_debug.put_line('Quantity = ' || p_qty);
2221 gme_debug.put_line('Sec quantity = ' || p_sec_qty);
2222 gme_debug.put_line('Reason_id = ' || p_reason_id);
2223 gme_debug.put_line('User Id = ' || p_user_id);
2224 gme_debug.put_line('Login Id = ' || p_login_id);
2225 END IF;
2226
2227 x_return_status := FND_API.G_RET_STS_SUCCESS;
2228 x_error_msg := ' ';
2229
2230 fnd_profile.put('USER_ID',to_char(p_user_id));
2231 fnd_profile.put('LOGIN_ID',to_char(p_login_id));
2232
2233 -- nsinghi bug#5236702
2234 OPEN get_item_duom_ctl;
2235 FETCH get_item_duom_ctl INTO l_duom_ctl;
2236 CLOSE get_item_duom_ctl;
2237
2238 ---l_pending_product_lots_rec.batch_id := p_batch_id;
2239 l_pending_product_lots_rec.material_detail_id := p_material_detail_id;
2240 l_pending_product_lots_rec.sequence := p_sequence;
2241 l_pending_product_lots_rec.revision := p_revision;
2242 l_pending_product_lots_rec.lot_number := p_lot_number;
2243 l_pending_product_lots_rec.quantity := p_qty;
2244 -- nsinghi bug#5236702 For non-duom item, value for p_sec_qty from mobile layer is 0.0
2245 -- sec_qty field is defined as double in mobile layer and initialized to 0.0 as double cannot take null.
2246 IF l_duom_ctl = 'PS' THEN
2247 l_pending_product_lots_rec.secondary_quantity := p_sec_qty;
2248 ELSE
2249 l_pending_product_lots_rec.secondary_quantity := NULL;
2250 END IF;
2251
2252 l_pending_product_lots_rec.reason_id := p_reason_id;
2253
2254 GME_API_PUB.create_pending_product_lot
2255 (p_api_version => 2.0
2256 ,p_validation_level => gme_common_pvt.g_max_errors
2257 ,p_init_msg_list => fnd_api.g_true
2258 ,p_commit => fnd_api.g_true
2259 ,x_message_count => l_message_count
2260 ,x_message_list => x_error_msg
2261 ,x_return_status => x_return_status
2262 ,p_batch_header_rec => l_batch_header_rec
2263 ,p_org_code => p_org_code
2264 ,p_create_lot => l_create_lot
2265 ,p_material_detail_rec => l_material_detail_rec
2266 ,p_pending_product_lots_rec => l_pending_product_lots_rec
2267 ,x_pending_product_lots_rec => l_out_pending_product_lots_rec);
2268
2269
2270 EXCEPTION
2271 WHEN OTHERS THEN
2272 FND_MSG_PUB.count_and_get
2273 ( p_count => l_message_count
2274 , p_data => x_error_msg
2275 );
2276 IF g_debug <= gme_debug.g_log_unexpected THEN
2277 gme_debug.put_line('When others exception in Create Pending Lots');
2278 END IF;
2279 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','create_pending_product_lots');
2280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2281 x_error_msg := fnd_message.get;
2282
2283 END Create_Product_Pending_Lot;
2284
2285 /*+========================================================================+
2286 | PROCEDURE NAME
2287 | Update_Product_Pending_Lot
2288 |
2289 | USAGE
2290 |
2291 | ARGUMENTS
2292 |
2293 | RETURNS
2294 |
2295 | HISTORY
2296 | Created 26-Apr-05 Eddie Oumerretane
2297 | Bug#5236906. 09-Jun-06 Namit S. Send Lot Number too when updating Pending Lot.
2298 |
2299 +========================================================================+*/
2300 PROCEDURE Update_Product_Pending_Lot(p_batch_id IN NUMBER,
2301 p_material_detail_id IN NUMBER,
2302 p_lot_id IN NUMBER,
2303 p_lot_number IN VARCHAR2,
2304 p_sequence IN NUMBER,
2305 p_qty IN NUMBER,
2306 p_sec_qty IN NUMBER,
2307 p_reason_id IN NUMBER,
2308 p_user_id IN NUMBER,
2309 p_login_id IN NUMBER,
2310 p_org_code IN VARCHAR2,
2311 p_last_update_date IN VARCHAR2,
2312 p_is_seq_changed IN VARCHAR2,
2313 x_return_status OUT NOCOPY VARCHAR2,
2314 x_error_msg OUT NOCOPY VARCHAR2)
2315 IS
2316 l_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
2317 l_out_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
2318 l_material_detail_rec gme_material_details%ROWTYPE;
2319 l_in_material_detail_rec gme_material_details%ROWTYPE;
2320 l_batch_header_rec gme_batch_header%ROWTYPE;
2321 l_message_count NUMBER;
2322 l_message_list VARCHAR2(2000);
2323 l_duom_ctl VARCHAR2(30);
2324
2325 -- nsinghi bug#5236702. Set secondary qty only if item is DUOM controlled.
2326 CURSOR get_item_duom_ctl IS
2327 SELECT NVL(tracking_quantity_ind, 'P')
2328 FROM mtl_system_items_b msi, gme_material_details gmd
2329 WHERE gmd.inventory_item_id = msi.inventory_item_id AND
2330 gmd.organization_id = msi.organization_id AND
2331 gmd.material_detail_id = p_material_detail_id;
2332
2333 BEGIN
2334
2335 IF (g_debug IS NOT NULL) THEN
2336 gme_debug.log_initialize ('MobileUpdatePendingLot');
2337 END IF;
2338
2339 x_return_status := FND_API.G_RET_STS_SUCCESS;
2340 x_error_msg := ' ';
2341
2342 fnd_profile.put('USER_ID',to_char(p_user_id));
2343 fnd_profile.put('LOGIN_ID',to_char(p_login_id));
2344
2345 IF p_sequence IS NULL THEN
2346 --- Set sequence to the last value
2347 l_pending_product_lots_rec.sequence := FND_API.G_MISS_NUM;
2348 ELSE
2349 IF p_is_seq_changed = 'Y' THEN
2350 l_pending_product_lots_rec.sequence := p_sequence;
2351 ELSE
2352 l_pending_product_lots_rec.sequence := NULL;
2353 END IF;
2354 END IF;
2355
2356 -- nsinghi bug#5236702
2357 OPEN get_item_duom_ctl;
2358 FETCH get_item_duom_ctl INTO l_duom_ctl;
2359 CLOSE get_item_duom_ctl;
2360
2361 ---l_pending_product_lots_rec.batch_id := p_batch_id;
2362 l_pending_product_lots_rec.lot_number := p_lot_number; -- nsinghi bug#5236906. Uncommented this line.
2363 l_pending_product_lots_rec.pending_product_lot_id := p_lot_id;
2364 l_pending_product_lots_rec.material_detail_id := p_material_detail_id;
2365 l_pending_product_lots_rec.quantity := p_qty;
2366 -- sec_qty field is defined as double in mobile layer and initialized to 0.0 as double cannot take null.
2367 IF l_duom_ctl = 'PS' THEN
2368 l_pending_product_lots_rec.secondary_quantity := p_sec_qty;
2369 ELSE
2370 l_pending_product_lots_rec.secondary_quantity := NULL;
2371 END IF;
2372
2373 IF p_reason_id IS NULL THEN
2374 l_pending_product_lots_rec.reason_id := FND_API.G_MISS_NUM;
2375 ELSE
2376 l_pending_product_lots_rec.reason_id := p_reason_id;
2377 END IF;
2378
2379 l_pending_product_lots_rec.last_update_date := to_date(p_last_update_date, 'MM/DD/YYYY HH24:MI:SS');
2380
2381 gme_debug.put_line('Update Pending Lot: ');
2382 gme_debug.put_line('Last Upd Date = '|| to_char(l_pending_product_lots_rec.last_update_date, 'MM/DD/YYYY HH24:MI:SS'));
2383 gme_debug.put_line('Sequence Chg = '|| p_is_seq_changed);
2384 gme_debug.put_line('Sequence = '|| l_pending_product_lots_rec.sequence);
2385 gme_debug.put_line('Lot id = '|| l_pending_product_lots_rec.pending_product_lot_id);
2386 gme_debug.put_line('Line Id = '|| l_pending_product_lots_rec.material_detail_id);
2387 gme_debug.put_line('Qty = '|| l_pending_product_lots_rec.quantity);
2388 gme_debug.put_line('Sec Qty = '|| l_pending_product_lots_rec.secondary_quantity);
2389 gme_debug.put_line('Reason id = '|| l_pending_product_lots_rec.reason_id);
2390
2391 GME_API_PUB.update_pending_product_lot
2392 (p_api_version => 2.0
2393 ,p_validation_level => gme_common_pvt.g_max_errors
2394 ,p_init_msg_list => fnd_api.g_true
2395 ,p_commit => fnd_api.g_true
2396 ,x_message_count => l_message_count
2397 ,x_message_list => l_message_list
2398 ,x_return_status => x_return_status
2399 ,p_batch_header_rec => l_batch_header_rec
2400 ,p_org_code => p_org_code
2401 ,p_material_detail_rec => l_material_detail_rec
2402 ,p_pending_product_lots_rec => l_pending_product_lots_rec
2403 ,x_pending_product_lots_rec => l_out_pending_product_lots_rec);
2404
2405 IF x_return_status <> 'S' THEN
2406 FND_MSG_PUB.count_and_get
2407 ( p_count => l_message_count
2408 , p_data => x_error_msg
2409 );
2410 /* Bug 5438355: Added success message */
2411 ELSE
2412 x_error_msg := l_message_list;
2413 END IF;
2414
2415 EXCEPTION
2416 WHEN OTHERS THEN
2417 IF g_debug <= gme_debug.g_log_unexpected THEN
2418 gme_debug.put_line('When others exception in Update Pending Lots');
2419 FND_MSG_PUB.count_and_get
2420 ( p_count => l_message_count
2421 , p_data => x_error_msg
2422 );
2423 gme_debug.put_line('Error = '||x_error_msg);
2424 END IF;
2425 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','update_pending_product_lots');
2426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2427
2428 END Update_Product_Pending_Lot;
2429
2430 /*+========================================================================+
2431 | PROCEDURE NAME
2432 | Populate_Dispensing_Table
2433 |
2434 | USAGE
2435 |
2436 | ARGUMENTS
2437 |
2438 | RETURNS
2439 |
2440 | HISTORY
2441 | Created 26-Apr-05 Eddie Oumerretane
2442 | 22-Aug-06 Shrikant Nene Bug 5456068
2443 | Added parameter x_dispensed_count
2444 |
2445 +========================================================================+*/
2446 PROCEDURE Populate_Dispensing_Table(
2447 p_material_detail_id IN NUMBER,
2448 x_dispensed_count OUT NOCOPY NUMBER,
2449 x_return_status OUT NOCOPY VARCHAR2,
2450 x_error_msg OUT NOCOPY VARCHAR2)
2451 IS
2452
2453 l_return_status VARCHAR2(2);
2454 l_msg_count NUMBER;
2455 l_msg_data VARCHAR2(2000);
2456 l_index INTEGER;
2457 l_nb_rec NUMBER;
2458 l_dispensed NUMBER := 0;
2459 l_rsrv_tab GME_COMMON_PVT.reservations_tab;
2460
2461 BEGIN
2462
2463 IF (g_debug IS NOT NULL) THEN
2464 gme_debug.log_initialize ('MobilePopulateDispTbl');
2465 END IF;
2466
2467 x_return_status := FND_API.G_RET_STS_SUCCESS;
2468 x_error_msg := ' ';
2469
2470 -- clear out temp table
2471 DELETE FROM GME_MATERIAL_DISPENSING_GTMP;
2472
2473 -- Fetch dispensing data for the given material line
2474
2475 GMO_DISPENSE_GRP.GET_MATERIAL_DISPENSE_DATA (
2476 p_api_version => 1.0,
2477 p_init_msg_list => FND_API.G_FALSE,
2478 x_return_status => l_return_status,
2479 x_msg_count => l_msg_count,
2480 x_msg_data => l_msg_data,
2481 p_material_detail_id => p_material_detail_id,
2482 x_dispense_data => l_rsrv_tab);
2483
2484 l_nb_rec := l_rsrv_tab.COUNT;
2485
2486 --insert into table
2487 FOR l_index in 1..l_nb_rec
2488 LOOP
2489
2490 IF l_rsrv_tab(l_index).external_source_line_id IS NOT NULL THEN
2491
2492 gme_debug.put_line('Inserting Dispensed record ');
2493 gme_debug.put_line('ID = ' ||
2494 l_rsrv_tab(l_index).external_source_line_id);
2495 gme_debug.put_line('Sub = ' ||
2496 l_rsrv_tab(l_index).subinventory_code);
2497 gme_debug.put_line('Locator Id = ' ||
2498 l_rsrv_tab(l_index).locator_id);
2499 gme_debug.put_line('UOM = ' ||
2500 l_rsrv_tab(l_index).reservation_uom_code);
2501 gme_debug.put_line('QTY = ' ||
2502 l_rsrv_tab(l_index).reservation_quantity);
2503 gme_debug.put_line('Sec QTY = ' ||
2504 l_rsrv_tab(l_index).secondary_reservation_quantity);
2505 gme_debug.put_line('Lot = ' ||
2506 l_rsrv_tab(l_index).lot_number);
2507 gme_debug.put_line('Revision = ' ||
2508 l_rsrv_tab(l_index).revision);
2509
2510 INSERT INTO GME_MATERIAL_DISPENSING_GTMP
2511 (
2512 DISPENSE_ID
2513 ,SUBINVENTORY_CODE
2514 ,LOCATOR_ID
2515 ,DISPENSE_UOM
2516 ,DISPENSED_QTY
2517 ,SECONDARY_DISPENSED_QTY
2518 ,LOT_NUMBER
2519 ,REVISION
2520 )
2521 VALUES
2522 (
2523 l_rsrv_tab(l_index).external_source_line_id
2524 ,l_rsrv_tab(l_index).subinventory_code
2525 ,l_rsrv_tab(l_index).locator_id
2526 ,l_rsrv_tab(l_index).reservation_uom_code
2527 ,l_rsrv_tab(l_index).reservation_quantity
2528 ,l_rsrv_tab(l_index).secondary_reservation_quantity
2529 ,l_rsrv_tab(l_index).lot_number
2530 ,l_rsrv_tab(l_index).revision
2531 );
2532
2533 l_dispensed := l_dispensed + 1;
2534 END IF;
2535
2536 END LOOP;
2537 x_dispensed_count := l_dispensed;
2538
2539 EXCEPTION
2540 WHEN OTHERS THEN
2541 IF g_debug <= gme_debug.g_log_unexpected THEN
2542 gme_debug.put_line('When others exception in Populate_Dispensing_Table');
2543 gme_debug.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,
2544 255));
2545
2546 END IF;
2547 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Populate_Dispensing_Table');
2548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2549 x_error_msg := fnd_message.get;
2550
2551 END Populate_Dispensing_Table;
2552
2553 /*+========================================================================+
2554 | PROCEDURE NAME
2555 | Delete_Dispensing_Record
2556 |
2557 | USAGE
2558 |
2559 | ARGUMENTS
2560 |
2561 | RETURNS
2562 |
2563 | HISTORY
2564 | Created 26-Apr-05 Eddie Oumerretane
2565 |
2566 +========================================================================+*/
2567 PROCEDURE Delete_Dispensing_Record(
2568 p_dispense_id IN NUMBER,
2569 x_return_status OUT NOCOPY VARCHAR2,
2570 x_error_msg OUT NOCOPY VARCHAR2)
2571 IS
2572
2573 BEGIN
2574
2575 IF (g_debug IS NOT NULL) THEN
2576 gme_debug.log_initialize ('MobileDelDispRec');
2577 END IF;
2578
2579 x_return_status := FND_API.G_RET_STS_SUCCESS;
2580 x_error_msg := ' ';
2581
2582 DELETE FROM GME_MATERIAL_DISPENSING_GTMP
2583 WHERE dispense_id = p_dispense_id;
2584
2585 EXCEPTION
2586 WHEN OTHERS THEN
2587 IF g_debug <= gme_debug.g_log_unexpected THEN
2588 gme_debug.put_line('When others exception in Delete_Dispensing_Record');
2589 END IF;
2590 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Delete_Dispensing_Record');
2591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2592 x_error_msg := fnd_message.get;
2593
2594 END Delete_Dispensing_Record;
2595
2596 /*+========================================================================+
2597 | PROCEDURE NAME
2598 | Get_Dispensed_Lot_Count
2599 |
2600 | USAGE
2601 |
2602 | ARGUMENTS
2603 |
2604 | RETURNS
2605 |
2606 | HISTORY
2607 | Created 26-Apr-05 Eddie Oumerretane
2608 |
2609 +========================================================================+*/
2610 PROCEDURE Get_Dispensed_Lot_Count(
2611 p_subinv_code IN VARCHAR2,
2612 p_locator_id IN NUMBER,
2613 x_lot_count OUT NOCOPY NUMBER,
2614 x_return_status OUT NOCOPY VARCHAR2,
2615 x_error_msg OUT NOCOPY VARCHAR2)
2616 IS
2617
2618 CURSOR lot_count IS
2619 SELECT count(*)
2620 FROM GME_MATERIAL_DISPENSING_GTMP
2621 WHERE subinventory_code = p_subinv_code AND
2622 NVL(locator_id, -1) = NVL(p_locator_id, -1);
2623 BEGIN
2624
2625 IF (g_debug IS NOT NULL) THEN
2626 gme_debug.log_initialize ('MobileCountDispLot');
2627 END IF;
2628
2629 x_return_status := FND_API.G_RET_STS_SUCCESS;
2630 x_error_msg := ' ';
2631
2632 OPEN lot_count;
2633 FETCH lot_count INTO x_lot_count;
2634 CLOSE lot_count;
2635
2636 EXCEPTION
2637 WHEN OTHERS THEN
2638 IF g_debug <= gme_debug.g_log_unexpected THEN
2639 gme_debug.put_line('When others exception in Get_Dispensed_Lot_Count');
2640 END IF;
2641 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Get_Dispensed_Lot_Count');
2642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2643 x_error_msg := fnd_message.get;
2644
2645 END Get_Dispensed_Lot_Count;
2646
2647 /*+========================================================================+
2648 | PROCEDURE NAME
2649 | Query_Qty_Tree
2650 |
2651 | USAGE
2652 |
2653 | ARGUMENTS
2654 |
2655 | RETURNS
2656 |
2657 | HISTORY
2658 | Created 26-Apr-05 Eddie Oumerretane
2659 |
2660 +========================================================================+*/
2661 PROCEDURE Query_Qty_Tree (p_organization_id IN NUMBER,
2662 p_inventory_item_id IN NUMBER,
2663 p_revision IN VARCHAR2,
2664 p_subinventory_code IN VARCHAR2,
2665 p_locator_id IN NUMBER,
2666 p_lot_number IN VARCHAR2,
2667 p_revision_control IN VARCHAR2,
2668 p_lot_control IN VARCHAR2,
2669 p_tree_mode IN VARCHAR2,
2670 x_qoh OUT NOCOPY NUMBER,
2671 x_sqoh OUT NOCOPY NUMBER,
2672 x_rqoh OUT NOCOPY NUMBER,
2673 x_srqoh OUT NOCOPY NUMBER,
2674 x_qr OUT NOCOPY NUMBER,
2675 x_sqr OUT NOCOPY NUMBER,
2676 x_qs OUT NOCOPY NUMBER,
2677 x_sqs OUT NOCOPY NUMBER,
2678 x_att OUT NOCOPY NUMBER,
2679 x_satt OUT NOCOPY NUMBER,
2680 x_atr OUT NOCOPY NUMBER,
2681 x_satr OUT NOCOPY NUMBER,
2682 x_return_status OUT NOCOPY VARCHAR2,
2683 x_error_msg OUT NOCOPY VARCHAR2)
2684 IS
2685
2686 l_is_revision_control BOOLEAN := FALSE;
2687 l_is_lot_control BOOLEAN := FALSE;
2688
2689 l_msg_count NUMBER(10);
2690 l_msg_data VARCHAR2(1000);
2691
2692 l_locator_id number;
2693 l_cost_group_id number;
2694
2695 BEGIN
2696
2697 x_return_status := FND_API.G_RET_STS_SUCCESS;
2698 x_error_msg := ' ';
2699
2700 IF (g_debug IS NOT NULL) THEN
2701 gme_debug.log_initialize ('MobileQueryQtyTree');
2702 END IF;
2703
2704 -- Clearing the quantity cache
2705 inv_quantity_tree_pub.clear_quantity_cache;
2706
2707 if upper(p_revision_control) = 'TRUE' then
2708 l_is_revision_control := TRUE;
2709 end if;
2710 if upper(p_lot_control) = 'TRUE' then
2711 l_is_lot_control := TRUE;
2712 end if;
2713
2714 if p_locator_id <= 0 then
2715 l_locator_id := null;
2716 else
2717 l_locator_id := p_locator_id;
2718 end if;
2719
2720 Inv_Quantity_Tree_Pub.Query_Quantities (
2721 p_api_version_number => 1.0,
2722 p_init_msg_lst => fnd_api.g_false,
2723 x_return_status => x_return_status,
2724 x_msg_count => l_msg_count,
2725 x_msg_data => x_error_msg,
2726 p_organization_id => p_organization_id,
2727 p_inventory_item_id => p_inventory_item_id,
2728 p_tree_mode => p_tree_mode,
2729 p_is_revision_control => l_is_revision_control,
2730 p_is_lot_control => l_is_lot_control,
2731 p_is_serial_control => FALSE,
2732 p_grade_code => NULL,
2733 p_revision => p_revision,
2734 p_lot_number => p_lot_number,
2735 p_subinventory_code => p_subinventory_code,
2736 p_locator_id => l_locator_id,
2737 p_cost_group_id => NULL,
2738 x_qoh => x_qoh,
2739 x_rqoh => x_rqoh,
2740 x_qr => x_qr,
2741 x_qs => x_qs,
2742 x_att => x_att,
2743 x_atr => x_atr,
2744 x_sqoh => x_sqoh,
2745 x_srqoh => x_srqoh,
2746 x_sqr => x_sqr,
2747 x_sqs => x_sqs,
2748 x_satt => x_satt,
2749 x_satr => x_satr);
2750
2751
2752 EXCEPTION
2753 WHEN OTHERS THEN
2754 IF g_debug <= gme_debug.g_log_unexpected THEN
2755 gme_debug.put_line('When others exception in Query_Qty_Tree');
2756 END IF;
2757 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Query_Qty_Tree');
2758
2759 END Query_Qty_Tree;
2760
2761 /*+========================================================================+
2762 | PROCEDURE NAME
2763 | Create_Qty_Tree
2764 |
2765 | USAGE
2766 |
2767 | ARGUMENTS
2768 |
2769 | RETURNS
2770 |
2771 | HISTORY
2772 | Created 26-Apr-05 Eddie Oumerretane
2773 |
2774 +========================================================================+*/
2775 PROCEDURE Create_Qty_Tree (p_tree_mode IN NUMBER,
2776 p_organization_id IN NUMBER,
2777 p_inventory_item_id IN NUMBER,
2778 p_revision IN VARCHAR2,
2779 p_subinventory_code IN VARCHAR2,
2780 p_locator_id IN NUMBER,
2781 p_revision_control IN VARCHAR2,
2782 p_lot_control IN VARCHAR2,
2783 x_tree_id OUT NOCOPY NUMBER,
2784 x_return_status OUT NOCOPY VARCHAR2,
2785 x_error_msg OUT NOCOPY VARCHAR2) IS
2786
2787 l_is_revision_control BOOLEAN := FALSE;
2788 l_is_lot_control BOOLEAN := FALSE;
2789
2790 l_msg_count NUMBER(10);
2791 l_locator_id NUMBER;
2792 CREATE_TREE_ERROR EXCEPTION;
2793
2794 BEGIN
2795
2796
2797 IF (g_debug IS NOT NULL) THEN
2798 gme_debug.log_initialize ('MobileCreQtyTree');
2799 END IF;
2800
2801 x_return_status := FND_API.G_RET_STS_SUCCESS;
2802 x_error_msg := ' ';
2803
2804 IF upper(p_revision_control) = 'TRUE' THEN
2805 l_is_revision_control := TRUE;
2806 END IF;
2807
2808 IF upper(p_lot_control) = 'TRUE' THEN
2809 l_is_lot_control := TRUE;
2810 END IF;
2811
2812 IF p_locator_id <= 0 THEN
2813 l_locator_id := null;
2814 ELSE
2815 l_locator_id := p_locator_id;
2816 END IF;
2817
2818 -- Clearing the quantity cache
2819 INV_Quantity_Tree_Pub.Clear_Quantity_Cache;
2820
2821 INV_Quantity_Tree_Grp.Create_Tree
2822 (
2823 p_api_version_number => 1.0
2824 , p_init_msg_lst => 'T'
2825 , x_return_status => x_return_status
2826 , x_msg_count => l_msg_count
2827 , x_msg_data => x_error_msg
2828 , p_organization_id => p_organization_id
2829 , p_inventory_item_id => p_inventory_item_id
2830 , p_tree_mode => p_tree_mode
2831 , p_is_revision_control => l_is_revision_control
2832 , p_is_lot_control => l_is_lot_control
2833 , p_is_serial_control => FALSE
2834 , p_grade_code => NULL
2835 , p_demand_source_type_id => gme_common_pvt.g_txn_source_type
2836 , p_demand_source_name => NULL
2837 , p_lot_expiration_date => SYSDATE
2838 , p_onhand_source => 3
2839 , x_tree_id => x_tree_id
2840 );
2841
2842 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2843 RAISE CREATE_TREE_ERROR;
2844 END IF;
2845
2846 EXCEPTION
2847 WHEN CREATE_TREE_ERROR THEN
2848 IF g_debug <= gme_debug.g_log_unexpected THEN
2849 gme_debug.put_line('Create Qty Tree exception');
2850 END IF;
2851
2852 WHEN OTHERS THEN
2853 IF g_debug <= gme_debug.g_log_unexpected THEN
2854 gme_debug.put_line('When others exception in Create Qty Tree');
2855 END IF;
2856 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Create_Qty_Tree');
2857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2858 x_error_msg := fnd_message.get;
2859
2860 END Create_Qty_Tree;
2861
2862 /*+========================================================================+
2863 | PROCEDURE NAME
2864 | Query_Qty_Tree
2865 |
2866 | USAGE
2867 |
2868 | ARGUMENTS
2869 |
2870 | RETURNS
2871 |
2872 | HISTORY
2873 | Created 26-Apr-05 Eddie Oumerretane
2874 |
2875 +========================================================================+*/
2876 PROCEDURE Query_Qty_Tree (p_revision IN VARCHAR2,
2877 p_subinventory_code IN VARCHAR2,
2878 p_locator_id IN NUMBER,
2879 p_lot_number IN VARCHAR2,
2880 p_tree_id IN NUMBER,
2881 x_qoh OUT NOCOPY NUMBER,
2882 x_sqoh OUT NOCOPY NUMBER,
2883 x_rqoh OUT NOCOPY NUMBER,
2884 x_srqoh OUT NOCOPY NUMBER,
2885 x_qr OUT NOCOPY NUMBER,
2886 x_sqr OUT NOCOPY NUMBER,
2887 x_qs OUT NOCOPY NUMBER,
2888 x_sqs OUT NOCOPY NUMBER,
2889 x_att OUT NOCOPY NUMBER,
2890 x_satt OUT NOCOPY NUMBER,
2891 x_atr OUT NOCOPY NUMBER,
2892 x_satr OUT NOCOPY NUMBER,
2893 x_return_status OUT NOCOPY VARCHAR2,
2894 x_error_msg OUT NOCOPY VARCHAR2) IS
2895
2896 l_msg_count NUMBER(10);
2897 QUERY_TREE_ERROR EXCEPTION;
2898
2899 BEGIN
2900
2901 IF (g_debug IS NOT NULL) THEN
2902 gme_debug.log_initialize ('MobileQtyTree');
2903 END IF;
2904
2905 x_return_status := FND_API.G_RET_STS_SUCCESS;
2906 x_error_msg := ' ';
2907
2908 INV_Quantity_Tree_Grp.Query_Tree(
2909 p_api_version_number => 1
2910 , p_init_msg_lst => NULL
2911 , x_return_status => x_return_status
2912 , x_msg_count => l_msg_count
2913 , x_msg_data => x_error_msg
2914 , p_tree_id => p_tree_id
2915 , p_revision => p_revision
2916 , p_lot_number => p_lot_number
2917 , p_subinventory_code => p_subinventory_code
2918 , p_locator_id => p_locator_id
2919 , p_lpn_id => NULL
2920 , x_qoh => x_qoh
2921 , x_rqoh => x_rqoh
2922 , x_qr => x_qr
2923 , x_qs => x_qs
2924 , x_att => x_att
2925 , x_atr => x_atr
2926 , x_sqoh => x_sqoh
2927 , x_srqoh => x_srqoh
2928 , x_sqr => x_sqr
2929 , x_sqs => x_sqs
2930 , x_satt => x_satt
2931 , x_satr => x_satr
2932 );
2933
2934 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2935 RAISE QUERY_TREE_ERROR;
2936 END IF;
2937
2938 EXCEPTION
2939 WHEN QUERY_TREE_ERROR THEN
2940 IF g_debug <= gme_debug.g_log_unexpected THEN
2941 gme_debug.put_line('Query Qty Tree exception');
2942 END IF;
2943
2944 WHEN OTHERS THEN
2945 IF g_debug <= gme_debug.g_log_unexpected THEN
2946 gme_debug.put_line('When others exception in Query_Tree');
2947 END IF;
2948 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Query_Tree');
2949 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2950 x_error_msg := fnd_message.get;
2951
2952 END Query_Qty_Tree;
2953
2954 /*+========================================================================+
2955 | PROCEDURE NAME
2956 | Update_Qty_Tree
2957 |
2958 | USAGE
2959 |
2960 | ARGUMENTS
2961 |
2962 | RETURNS
2963 |
2964 | HISTORY
2965 | Created 26-Apr-05 Eddie Oumerretane
2966 |
2967 +========================================================================+*/
2968 PROCEDURE Update_Qty_Tree ( p_tree_id IN NUMBER,
2969 p_revision IN VARCHAR2,
2970 p_subinventory_code IN VARCHAR2,
2971 p_locator_id IN NUMBER,
2972 p_lot_number IN VARCHAR2,
2973 p_primary_qty IN NUMBER,
2974 p_secondary_qty IN NUMBER,
2975 p_quantity_type IN NUMBER,
2976 x_return_status OUT NOCOPY VARCHAR2,
2977 x_error_msg OUT NOCOPY VARCHAR2) IS
2978
2979 l_msg_count NUMBER(10);
2980 l_qoh NUMBER;
2981 l_satr NUMBER;
2982 l_atr NUMBER;
2983 l_rqoh NUMBER;
2984 l_qr NUMBER;
2985 l_qs NUMBER;
2986 l_att NUMBER;
2987 l_sqoh NUMBER;
2988 l_srqoh NUMBER;
2989 l_sqr NUMBER;
2990 l_sqs NUMBER;
2991 l_satt NUMBER;
2992 l_locator_id NUMBER;
2993 UPDATE_TREE_ERROR EXCEPTION;
2994
2995 BEGIN
2996
2997
2998 IF (g_debug IS NOT NULL) THEN
2999 gme_debug.log_initialize ('MobileUpdateQtyTree');
3000 END IF;
3001
3002 x_return_status := FND_API.G_RET_STS_SUCCESS;
3003 x_error_msg := ' ';
3004
3005 IF p_locator_id <= 0 THEN
3006 l_locator_id := null;
3007 ELSE
3008 l_locator_id := p_locator_id;
3009 END IF;
3010
3011 IF (p_primary_qty <> 0) THEN
3012
3013 gme_debug.put_line('Tree id = '||p_tree_id);
3014 gme_debug.put_line('Qty = '||p_primary_qty);
3015 gme_debug.put_line('Sec Qty = '||p_secondary_qty);
3016 gme_debug.put_line('lot = '||p_lot_number);
3017 gme_debug.put_line('Sub = '||p_subinventory_code);
3018 gme_debug.put_line('Locator id = '||p_locator_id);
3019 gme_debug.put_line('revision = '||p_revision);
3020
3021 INV_Quantity_Tree_Grp.Update_Quantities(
3022 p_api_version_number => 1.0,
3023 p_init_msg_lst => 'T',
3024 x_return_status => x_return_status,
3025 x_msg_count => l_msg_count,
3026 x_msg_data => x_error_msg,
3027 p_tree_id => p_tree_id,
3028 p_revision => p_revision,
3029 p_lot_number => p_lot_number,
3030 p_subinventory_code => p_subinventory_code,
3031 p_locator_id => p_locator_id,
3032 p_primary_quantity => p_primary_qty,
3033 p_quantity_type => p_quantity_type,
3034 p_secondary_quantity => p_secondary_qty,
3035 x_qoh => l_qoh,
3036 x_rqoh => l_rqoh,
3037 x_qr => l_qr,
3038 x_qs => l_qs,
3039 x_att => l_att,
3040 x_atr => l_atr,
3041 x_sqoh => l_sqoh,
3042 x_srqoh => l_srqoh,
3043 x_sqr => l_sqr,
3044 x_sqs => l_sqs,
3045 x_satt => l_satt,
3046 x_satr => l_satr,
3047 p_containerized => 0,
3048 p_lpn_id => NULL);
3049
3050 gme_debug.put_line('New ATT = '||l_att);
3051 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3052 RAISE UPDATE_TREE_ERROR;
3053 END IF;
3054
3055 END IF;
3056
3057
3058 EXCEPTION
3059 WHEN UPDATE_TREE_ERROR THEN
3060 IF g_debug <= gme_debug.g_log_unexpected THEN
3061 gme_debug.put_line('Update Qty Tree exception');
3062 END IF;
3063
3064 WHEN OTHERS THEN
3065 IF g_debug <= gme_debug.g_log_unexpected THEN
3066 gme_debug.put_line('When others exception in Update Qty Tree');
3067 END IF;
3068 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Update_Qty_Tree');
3069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3070 x_error_msg := fnd_message.get;
3071
3072 END Update_Qty_Tree;
3073
3074 /*+========================================================================+
3075 | PROCEDURE NAME
3076 | Is_Lot_Indivisible
3077 |
3078 | USAGE
3079 |
3080 | ARGUMENTS
3081 |
3082 | RETURNS
3083 |
3084 | HISTORY
3085 | Created 21-Sep-05 Eddie Oumerretane
3086 |
3087 +========================================================================+*/
3088 FUNCTION Is_Lot_Indivisible_item (p_organization_id IN NUMBER,
3089 p_item_id IN NUMBER) RETURN BOOLEAN
3090 IS
3091 CURSOR Get_div_flag IS
3092 SELECT
3093 NVL(lot_divisible_flag, 'N'),
3094 NVL(lot_control_code, 1)
3095 FROM mtl_system_items_kfv
3096 WHERE inventory_item_id = p_item_id
3097 AND organization_id = p_organization_id;
3098
3099 l_lot_div_flag VARCHAR2(1);
3100 l_lot_control_code NUMBER;
3101
3102 BEGIN
3103
3104 OPEN Get_div_flag;
3105 FETCH Get_div_flag INTO l_lot_div_flag, l_lot_control_code;
3106 CLOSE Get_div_flag;
3107
3108 IF l_lot_control_code <> 1 THEN
3109 IF l_lot_div_flag = 'N' THEN
3110 RETURN TRUE;
3111 ELSE
3112 RETURN FALSE;
3113 END IF;
3114 ELSE
3115 RETURN FALSE;
3116 END IF;
3117
3118 END Is_Lot_Indivisible_Item;
3119
3120 /*+========================================================================+
3121 | PROCEDURE NAME
3122 | Validate_Item_To_Issue
3123 |
3124 | USAGE
3125 |
3126 | ARGUMENTS
3127 |
3128 | RETURNS
3129 |
3130 | HISTORY
3131 | Created 19-Sep-05 Eddie Oumerretane
3132 |
3133 +========================================================================+*/
3134 PROCEDURE Validate_Item_To_Issue(p_organization_id IN NUMBER,
3135 p_batch_id IN NUMBER,
3136 p_material_detail_id IN NUMBER,
3137 p_item_id IN NUMBER,
3138 x_return_status OUT NOCOPY VARCHAR2,
3139 x_error_msg OUT NOCOPY VARCHAR2) IS
3140 CURSOR Get_Mtl_Dtl IS
3141 SELECT release_type,
3142 NVL(phantom_type,0),
3143 phantom_line_id
3144 FROM gme_material_details
3145 WHERE material_detail_id = p_material_detail_id;
3146
3147 CURSOR Get_pplot_count IS
3148 SELECT count(*)
3149 FROM gme_pending_product_lots
3150 WHERE batch_id = p_batch_id;
3151
3152 ITEM_NOT_VALID EXCEPTION;
3153 l_release_type NUMBER;
3154 l_phantom_type NUMBER;
3155 l_phantom_line_id NUMBER;
3156 l_batchstep_id NUMBER;
3157 l_batchstep_status NUMBER;
3158 l_pplot_count NUMBER;
3159 l_is_item_associated_to_step BOOLEAN;
3160
3161 BEGIN
3162
3163 IF (g_debug IS NOT NULL) THEN
3164 gme_debug.log_initialize ('MobileValItemForIssue');
3165 gme_debug.put_line('Org Id = ' || p_organization_id);
3166 gme_debug.put_line('Batch Id = ' || p_batch_id);
3167 gme_debug.put_line('Line Id = ' || p_material_detail_id);
3168 gme_debug.put_line('Item Id = ' || p_item_id);
3169 END IF;
3170
3171 x_return_status := FND_API.G_RET_STS_SUCCESS;
3172 x_error_msg := ' ';
3173
3174 OPEN Get_Mtl_dtl;
3175 FETCH Get_Mtl_Dtl INTO l_release_type, l_phantom_type, l_phantom_line_id;
3176 CLOSE Get_Mtl_Dtl;
3177
3178 -- If the ingredient is a phantom and it has not been exploded then we
3179 -- cannot consume it.
3180
3181 IF l_phantom_type <> 0 THEN
3182 IF l_phantom_line_id IS NULL THEN
3183 FND_MESSAGE.SET_NAME('GME', 'GME_ISSUE_PHANTOM_NOT_EXPLOD');
3184 RAISE ITEM_NOT_VALID;
3185 END IF;
3186 END IF;
3187
3188 -- If the ingredient line has a release type of auto by step then the
3189 -- associated step must be in WIP or Completed
3190 IF l_release_type = GME_COMMON_PVT.g_mtl_autobystep_release THEN
3191 l_is_item_associated_to_step := GME_COMMON_PVT.Get_Assoc_Step(
3192 p_material_detail_id => p_material_detail_id
3193 ,x_batchstep_id => l_batchstep_id
3194 ,x_batchstep_status => l_batchstep_status);
3195 IF l_is_item_associated_to_step AND
3196 l_batchstep_status <> 2 AND
3197 l_batchstep_status <> 3 THEN
3198 FND_MESSAGE.SET_NAME('GME', 'GME_ISSUE_STEP_WIP_COMPLETE');
3199 RAISE ITEM_NOT_VALID;
3200 END IF;
3201 END IF;
3202
3203 -- If item is lot indivisible then you cannot consume it if there is a
3204 -- pending product lot for the batch
3205 /*IF Is_Lot_Indivisible_item(p_organization_id, p_item_id) THEN
3206 OPEN Get_pplot_count;
3207 FETCH Get_pplot_count INTO l_pplot_count;
3208 CLOSE Get_pplot_count;
3209
3210 IF l_pplot_count > 0 THEN
3211 FND_MESSAGE.SET_NAME('GME', 'GME_ISSUE_INDIV_PPLOT_EXIST');
3212 RAISE ITEM_NOT_VALID;
3213 END IF;
3214 END IF; */
3215
3216 EXCEPTION
3217 WHEN ITEM_NOT_VALID THEN
3218 x_return_status := FND_API.G_RET_STS_ERROR;
3219 x_error_msg := fnd_message.get;
3220
3221 WHEN OTHERS THEN
3222 IF g_debug <= gme_debug.g_log_unexpected THEN
3223 gme_debug.put_line('When others exception in Validate Item To Issue');
3224 END IF;
3225 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Validate_Item_To_Issue');
3226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3227 x_error_msg := fnd_message.get;
3228
3229 END Validate_Item_To_Issue;
3230
3231 /*+========================================================================+
3232 | PROCEDURE NAME
3233 | Validate_Item_To_Return
3234 |
3235 | USAGE
3236 |
3237 | ARGUMENTS
3238 |
3239 | RETURNS
3240 |
3241 | HISTORY
3242 | Created 04-Oct-05 Eddie Oumerretane
3243 |
3244 +========================================================================+*/
3245 PROCEDURE Validate_Item_To_Return(p_organization_id IN NUMBER,
3246 p_batch_id IN NUMBER,
3247 p_material_detail_id IN NUMBER,
3248 p_item_id IN NUMBER,
3249 x_return_status OUT NOCOPY VARCHAR2,
3250 x_error_msg OUT NOCOPY VARCHAR2) IS
3251 CURSOR Get_Mtl_Dtl IS
3252 SELECT NVL(dispense_ind,'N')
3253 FROM gme_material_details
3254 WHERE material_detail_id = p_material_detail_id;
3255
3256 ITEM_NOT_VALID EXCEPTION;
3257 l_dispense_ind VARCHAR2(2);
3258
3259 BEGIN
3260
3261 IF (g_debug IS NOT NULL) THEN
3262 gme_debug.log_initialize ('MobileValItemToReturn');
3263 gme_debug.put_line('Org Id = ' || p_organization_id);
3264 gme_debug.put_line('Batch Id = ' || p_batch_id);
3265 gme_debug.put_line('Line Id = ' || p_material_detail_id);
3266 gme_debug.put_line('Item Id = ' || p_item_id);
3267 END IF;
3268
3269 x_return_status := FND_API.G_RET_STS_SUCCESS;
3270 x_error_msg := ' ';
3271
3272 OPEN Get_Mtl_dtl;
3273 FETCH Get_Mtl_Dtl INTO l_dispense_ind;
3274 CLOSE Get_Mtl_Dtl;
3275
3276 -- Cannot return an ingredient that was dispensed
3277
3278 IF l_dispense_ind = 'Y' THEN
3279 FND_MESSAGE.SET_NAME('GME', 'GME_RETURN_DISPENSE_ING_ERROR');
3280 RAISE ITEM_NOT_VALID;
3281 END IF;
3282
3283 EXCEPTION
3284 WHEN ITEM_NOT_VALID THEN
3285 x_return_status := FND_API.G_RET_STS_ERROR;
3286 x_error_msg := fnd_message.get;
3287
3288 WHEN OTHERS THEN
3289 IF g_debug <= gme_debug.g_log_unexpected THEN
3290 gme_debug.put_line('When others exception in Validate Item To Return');
3291 END IF;
3292 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Validate_Item_To_Return');
3293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3294 x_error_msg := fnd_message.get;
3295
3296 END Validate_Item_To_Return;
3297
3298 /*+========================================================================+
3299 | PROCEDURE NAME
3300 | Validate_Prod_To_Yield
3301 |
3302 | USAGE
3303 |
3304 | ARGUMENTS
3305 |
3306 | RETURNS
3307 |
3308 | HISTORY
3309 | Created 19-Sep-05 Eddie Oumerretane
3310 |
3311 +========================================================================+*/
3312 PROCEDURE Validate_Prod_To_Yield (p_organization_id IN NUMBER,
3313 p_batch_id IN NUMBER,
3314 p_material_detail_id IN NUMBER,
3315 p_item_id IN NUMBER,
3316 x_return_status OUT NOCOPY VARCHAR2,
3317 x_error_msg OUT NOCOPY VARCHAR2) IS
3318
3319 CURSOR Get_Release_Type IS
3320 SELECT release_type
3321 FROM gme_material_details
3322 WHERE material_detail_id = p_material_detail_id;
3323
3324 CURSOR get_Batch_Status IS
3325 SELECT batch_status
3326 FROM gme_batch_header
3327 WHERE batch_id = p_batch_id;
3328
3329 l_release_type NUMBER;
3330 l_batch_status NUMBER;
3331 l_batchstep_id NUMBER;
3332 l_batchstep_status NUMBER;
3333 l_is_item_associated_to_step BOOLEAN;
3334 ITEM_NOT_VALID EXCEPTION;
3335
3336 BEGIN
3337
3338 IF (g_debug IS NOT NULL) THEN
3339 gme_debug.log_initialize ('MobileValProdToYield');
3340 gme_debug.put_line('Org Id = ' || p_organization_id);
3341 gme_debug.put_line('Batch Id = ' || p_batch_id);
3342 gme_debug.put_line('Line Id = ' || p_material_detail_id);
3343 gme_debug.put_line('Item Id = ' || p_item_id);
3344 END IF;
3345
3346 x_return_status := FND_API.G_RET_STS_SUCCESS;
3347 x_error_msg := ' ';
3348
3349 OPEN Get_Batch_Status;
3350 FETCH Get_Batch_Status INTO l_batch_status;
3351 CLOSE Get_Batch_Status;
3352
3353 OPEN Get_Release_Type;
3354 FETCH Get_Release_Type INTO l_release_type;
3355 CLOSE Get_Release_Type;
3356
3357 -- If product release type is Automatic, then batch status must be
3358 -- Completed
3359 -- If product release type is Automatic By Step, then:
3360 -- a) if associated to a step, step status must be Completed
3361 -- b) if not associated to a step, batch status must be Completed
3362
3363 IF l_release_type = GME_COMMON_PVT.g_mtl_auto_release THEN
3364 IF l_batch_status <> 3 THEN
3365 FND_MESSAGE.SET_NAME('GME', 'GME_YIELD_BATCH_COMPLETE');
3366 RAISE ITEM_NOT_VALID;
3367 END IF;
3368 ELSIF l_release_type = GME_COMMON_PVT.g_mtl_autobystep_release THEN
3369 l_is_item_associated_to_step := GME_COMMON_PVT.Get_Assoc_Step(
3370 p_material_detail_id => p_material_detail_id
3371 ,x_batchstep_id => l_batchstep_id
3372 ,x_batchstep_status => l_batchstep_status);
3373 IF l_is_item_associated_to_step THEN
3374 IF l_batchstep_status <> 3 THEN
3375 FND_MESSAGE.SET_NAME('GME', 'GME_YIELD_STEP_COMPLETE');
3376 RAISE ITEM_NOT_VALID;
3377 END IF;
3378 ELSIF l_batch_status <> 3 THEN
3379 FND_MESSAGE.SET_NAME('GME', 'GME_YIELD_BATCH_COMPLETE');
3380 RAISE ITEM_NOT_VALID;
3381 END IF;
3382 END IF;
3383
3384
3385
3386 EXCEPTION
3387 WHEN ITEM_NOT_VALID THEN
3388 x_return_status := FND_API.G_RET_STS_ERROR;
3389 x_error_msg := fnd_message.get;
3390
3391 WHEN OTHERS THEN
3392 IF g_debug <= gme_debug.g_log_unexpected THEN
3393 gme_debug.put_line('When others exception in Validate Prod To Yield');
3394 END IF;
3395 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Validate_Prod_To_Yield');
3396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3397 x_error_msg := fnd_message.get;
3398
3399 END Validate_Prod_To_Yield;
3400
3401 /*+========================================================================+
3402 | PROCEDURE NAME
3403 | Fetch_Indiv_Lot_Txn
3404 |
3405 | USAGE
3406 |
3407 | ARGUMENTS
3408 |
3409 | RETURNS
3410 |
3411 | HISTORY
3412 | Created 20-Sep-05 Eddie Oumerretane
3413 |
3414 +========================================================================+*/
3415 PROCEDURE Fetch_Issue_Transactions(
3416 p_organization_id IN NUMBER,
3417 p_batch_id IN NUMBER,
3418 p_material_detail_id IN NUMBER,
3419 p_lot_number IN VARCHAR2,
3420 x_return_status OUT NOCOPY VARCHAR2,
3421 x_error_msg OUT NOCOPY VARCHAR2,
3422 x_txn_cursor OUT NOCOPY t_genref)
3423 IS
3424 l_date_format VARCHAR2(100);
3425 BEGIN
3426
3427 IF (g_debug IS NOT NULL) THEN
3428 gme_debug.log_initialize ('MobileFetchIssueTxn');
3429 END IF;
3430
3431 x_return_status := FND_API.G_RET_STS_SUCCESS;
3432 x_error_msg := ' ';
3433
3434 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
3435
3436 IF l_date_format IS NULL THEN
3437 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
3438 END IF;
3439
3440 OPEN x_txn_cursor FOR
3441 SELECT DISTINCT
3442 m.transaction_id,
3443 m.transaction_quantity*(-1),
3444 m.primary_quantity *(-1),
3445 m.secondary_transaction_quantity *(-1),
3446 m.transaction_uom,
3447 m.subinventory_code,
3448 m.locator_id,
3449 m.reason_id,
3450 lc.concatenated_segments,
3451 TO_CHAR(m.transaction_date, l_date_format),
3452 revision
3453 FROM mtl_material_transactions m,
3454 mtl_transaction_lot_numbers l,
3455 wms_item_locations_kfv lc
3456 WHERE l.transaction_id = m.transaction_id
3457 AND l.lot_number = NVL(p_lot_number, l.lot_number)
3458 AND m.organization_id = p_organization_id
3459 AND m.transaction_source_id = p_batch_id
3460 AND m.trx_source_line_id = p_material_detail_id
3461 AND m.transaction_type_id = GME_COMMON_PVT.g_ing_issue
3462 AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
3463 AND lc.inventory_location_id(+) = m.locator_id;
3464
3465 EXCEPTION
3466 WHEN OTHERS THEN
3467 IF g_debug <= gme_debug.g_log_unexpected THEN
3468 gme_debug.put_line('When others exception in fetch Issue Transactions');
3469 END IF;
3470 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_issue_transactions');
3471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3472 x_error_msg := fnd_message.get;
3473
3474 END Fetch_Issue_Transactions;
3475
3476 /*+========================================================================+
3477 | PROCEDURE NAME
3478 | Fetch_Yield_Transactions
3479 |
3480 | USAGE
3481 |
3482 | ARGUMENTS
3483 |
3484 | RETURNS
3485 |
3486 | HISTORY
3487 | Created 20-Sep-05 Eddie Oumerretane
3488 |
3489 +========================================================================+*/
3490 PROCEDURE Fetch_Yield_Transactions(
3491 p_organization_id IN NUMBER,
3492 p_batch_id IN NUMBER,
3493 p_material_detail_id IN NUMBER,
3494 p_lot_number IN VARCHAR2,
3495 p_txn_type_id IN NUMBER,
3496 x_return_status OUT NOCOPY VARCHAR2,
3497 x_error_msg OUT NOCOPY VARCHAR2,
3498 x_txn_cursor OUT NOCOPY t_genref)
3499 IS
3500 l_date_format VARCHAR2(100);
3501 l_txn_type_id NUMBER;
3502 BEGIN
3503
3504 IF (g_debug IS NOT NULL) THEN
3505 gme_debug.log_initialize ('MobileFetchYieldTxn');
3506 END IF;
3507
3508 x_return_status := FND_API.G_RET_STS_SUCCESS;
3509 x_error_msg := ' ';
3510
3511 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
3512
3513 IF l_date_format IS NULL THEN
3514 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
3515 END IF;
3516
3517 l_txn_type_id := Get_Txn_Type(p_txn_type_id);
3518
3519 OPEN x_txn_cursor FOR
3520 SELECT DISTINCT
3521 m.transaction_id,
3522 m.transaction_quantity*(-1),
3523 m.primary_quantity *(-1),
3524 m.secondary_transaction_quantity *(-1),
3525 m.transaction_uom,
3526 m.subinventory_code,
3527 m.locator_id,
3528 m.reason_id,
3529 lc.concatenated_segments,
3530 TO_CHAR(m.transaction_date, l_date_format),
3531 revision
3532 FROM mtl_material_transactions m,
3533 mtl_transaction_lot_numbers l,
3534 wms_item_locations_kfv lc
3535 WHERE l.transaction_id = m.transaction_id
3536 AND l.lot_number = NVL(p_lot_number, l.lot_number)
3537 AND m.organization_id = p_organization_id
3538 AND m.transaction_source_id = p_batch_id
3539 AND m.trx_source_line_id = p_material_detail_id
3540 AND m.transaction_type_id = l_txn_type_id
3541 AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
3542 AND lc.inventory_location_id(+) = m.locator_id;
3543
3544 EXCEPTION
3545 WHEN OTHERS THEN
3546 IF g_debug <= gme_debug.g_log_unexpected THEN
3547 gme_debug.put_line('When others exception in fetch yield Transactions');
3548 END IF;
3549 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_yield_transactions');
3550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3551 x_error_msg := fnd_message.get;
3552
3553 END Fetch_Yield_Transactions;
3554
3555
3556
3557 /*+==========================================================================+
3558 | PROCEDURE NAME
3559 | CREATE_PHANTOM_TXN
3560 |
3561 | USAGE
3562 | create phantom transaction for the transaction passed.
3563 |
3564 | ARGUMENTS
3565 | p_mmti_trans_id - transaction_id from mmti
3566 |
3567 | RETURNS
3568 | returns via x_status OUT parameters
3569 |
3570 | HISTORY
3571 | Created 20-Sep-05
3572 |
3573 +==========================================================================+ */
3574 PROCEDURE Create_Phantom_Txn (
3575 p_mmti_trans_id IN NUMBER
3576 ,x_return_status OUT NOCOPY VARCHAR2
3577 ,x_error_msg OUT NOCOPY VARCHAR2) IS
3578
3579 CURSOR cur_get_transaction (v_transaction_id NUMBER)
3580 IS
3581 SELECT *
3582 FROM mtl_transactions_interface mmti
3583 WHERE transaction_interface_id = v_transaction_id;
3584
3585 CURSOR cur_get_lot_transaction (v_transaction_id NUMBER)
3586 IS
3587 SELECT *
3588 FROM mtl_transaction_lots_interface
3589 WHERE transaction_interface_id = v_transaction_id;
3590
3591 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_PHANTOM_TXN';
3592 l_mmti_rec mtl_transactions_interface%ROWTYPE;
3593 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
3594 BEGIN
3595
3596 IF (g_debug IS NOT NULL) THEN
3597 gme_debug.log_initialize ('MobileCreatePhantomTxn');
3598 END IF;
3599
3600 --Initially let us assign the return status to success
3601 x_return_status := fnd_api.g_ret_sts_success;
3602
3603 IF (g_debug <= gme_debug.g_log_statement) THEN
3604 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3605 || 'Entering');
3606 END IF;
3607
3608 OPEN cur_get_transaction(p_mmti_trans_id);
3609 FETCH cur_get_transaction INTO l_mmti_rec;
3610 IF cur_get_transaction%NOTFOUND THEN
3611 CLOSE cur_get_transaction;
3612 gme_common_pvt.log_message('GME_NO_TRANS_FOUND');
3613 RAISE fnd_api.g_exc_error;
3614 END IF;
3615 CLOSE cur_get_transaction;
3616
3617 OPEN cur_get_lot_transaction(p_mmti_trans_id);
3618 FETCH cur_get_lot_transaction
3619 BULK COLLECT INTO l_mmli_tbl;
3620 CLOSE cur_get_lot_transaction;
3621
3622 IF l_mmti_rec.transaction_type_id NOT IN
3623 (gme_common_pvt.g_ing_return
3624 ,gme_common_pvt.g_prod_completion
3625 ,gme_common_pvt.g_byprod_completion) THEN
3626
3627
3628 FOR i IN 1..l_mmli_tbl.COUNT
3629 LOOP
3630 l_mmli_tbl(i).transaction_quantity :=
3631 (-1) * l_mmli_tbl(i).transaction_quantity;
3632 l_mmli_tbl(i).secondary_transaction_quantity :=
3633 (-1) * l_mmli_tbl(i).secondary_transaction_quantity;
3634 END LOOP;
3635
3636 l_mmti_rec.transaction_quantity :=
3637 (-1) * l_mmti_rec.transaction_quantity;
3638 l_mmti_rec.secondary_transaction_quantity :=
3639 (-1) * l_mmti_rec.secondary_transaction_quantity;
3640
3641 END IF;
3642
3643 GME_COMMON_PVT.G_MOVE_TO_TEMP := FND_API.G_FALSE;
3644
3645 GME_TRANSACTIONS_PVT.Create_Material_Txn
3646
3647 (p_mmti_rec => l_mmti_rec
3648 ,p_mmli_tbl => l_mmli_tbl
3649 ,p_phantom_trans => 2
3650 ,x_return_status => x_return_status);
3651
3652 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3653 RAISE fnd_api.g_exc_error;
3654 END IF;
3655
3656 IF (g_debug <= gme_debug.g_log_statement) THEN
3657 gme_debug.put_line ( g_pkg_name
3658 || '.'
3659 || l_api_name
3660 || ':'
3661 || 'Exiting with '
3662 || x_return_status);
3663 END IF;
3664
3665 EXCEPTION
3666 WHEN fnd_api.g_exc_error THEN
3667 x_return_status := fnd_api.g_ret_sts_error;
3668 x_error_msg := fnd_message.get;
3669
3670 WHEN fnd_api.g_exc_unexpected_error THEN
3671 x_return_status := fnd_api.g_ret_sts_unexp_error;
3672
3673 WHEN OTHERS THEN
3674 x_return_status := fnd_api.g_ret_sts_unexp_error;
3675 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3676 x_error_msg := fnd_message.get;
3677 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3678 gme_debug.put_line ( g_pkg_name
3679 || '.'
3680 || l_api_name
3681 || ':'
3682 || 'WHEN OTHERS:'
3683 || SQLERRM);
3684 END IF;
3685
3686 END Create_Phantom_Txn;
3687
3688 /*###############################################################
3689 # DESCRIPTION
3690 # This procedure calls the GME complete_step api.
3691 ###############################################################*/
3692
3693 PROCEDURE complete_step (p_batch_id IN NUMBER,
3694 p_step_id IN NUMBER,
3695 p_act_step_qty IN NUMBER,
3696 p_act_strt_dt IN VARCHAR2,
3697 p_act_complt_dt IN VARCHAR2,
3698 p_date_format IN VARCHAR2,
3699 p_uname IN VARCHAR2,
3700 p_uid IN NUMBER,
3701 x_return_status OUT NOCOPY VARCHAR2,
3702 x_message_count OUT NOCOPY NUMBER,
3703 x_message_list OUT NOCOPY VARCHAR2) IS
3704 l_step_qty NUMBER;
3705 l_act_strt_dt DATE;
3706 l_act_complt_dt DATE;
3707 message VARCHAR2(2000);
3708 l_batch_step gme_batch_steps%ROWTYPE;
3709 l_batch_step_out gme_batch_steps%ROWTYPE;
3710 x_batch_step gme_batch_steps%ROWTYPE;
3711 l_batch_header gme_batch_header%ROWTYPE;
3712 x_exception_material gme_common_pvt.exceptions_tab;
3713 step_alloc_error EXCEPTION;
3714 expected_error EXCEPTION;
3715 validate_step_error EXCEPTION;
3716 validate_step_status_error EXCEPTION;
3717 BEGIN
3718 SAVEPOINT complete_step_mobile ;
3719
3720 x_return_status := FND_API.G_RET_STS_SUCCESS;
3721
3722 l_act_strt_dt := TO_DATE(p_act_strt_dt, p_date_format||HOUR_MIN_SEC_FORMAT_STRING);
3723 l_act_complt_dt := TO_DATE(p_act_complt_dt, p_date_format||HOUR_MIN_SEC_FORMAT_STRING);
3724
3725 fnd_profile.put('USER_ID',to_char(p_uid));
3726 gme_common_pvt.g_user_name := p_uname;
3727 gme_common_pvt.g_user_ident := p_uid;
3728 gme_common_pvt.g_login_id := TO_NUMBER (fnd_profile.VALUE ('LOGIN_ID'));
3729 gme_common_pvt.g_user_ident := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
3730
3731 l_batch_header.batch_id := p_batch_id;
3732 IF NOT (gme_batch_header_dbl.fetch_row(l_batch_header, l_batch_header)) THEN
3733 RAISE expected_error;
3734 END IF;
3735 l_batch_step.batch_id := p_batch_id;
3736 l_batch_step.batchstep_id := p_step_id;
3737 IF NOT (gme_batch_steps_dbl.fetch_row(l_batch_step, l_batch_step)) THEN
3738 RAISE expected_error;
3739 END IF;
3740
3741 l_batch_step.actual_step_qty := p_act_step_qty;
3742 l_batch_step.actual_start_date := l_act_strt_dt;
3743 l_batch_step.actual_cmplt_date := l_act_complt_dt;
3744
3745 -- Bug 4774944 Rework.
3746 -- Check for 'Step controls batch status' parameter.
3747 IF l_batch_header.batch_status = 1 AND
3748 (gme_common_pvt.g_step_controls_batch_sts_ind <> 1 OR
3749 l_batch_header.parentline_id IS NOT NULL ) THEN
3750 RAISE validate_step_status_error;
3751 END IF;
3752
3753 -- Bug 4774944
3754 -- Added call to validate step for complete.
3755 gme_complete_batch_step_pvt.validate_step_for_complete
3756 (p_batch_header_rec => l_batch_header
3757 ,p_batch_step_rec => l_batch_step
3758 ,p_override_quality => FND_API.G_FALSE
3759 ,x_batch_step_rec => l_batch_step_out
3760 ,x_return_status => x_return_status);
3761
3762 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3763 RAISE validate_step_error;
3764 END IF;
3765
3766 gme_api_main.complete_step (
3767 p_validation_level => gme_common_pvt.g_max_errors,
3768 p_init_msg_list => FND_API.G_TRUE,
3769 x_message_count => x_message_count,
3770 x_message_list => x_message_list,
3771 x_return_status => x_return_status,
3772 p_batch_step_rec => l_batch_step,
3773 p_batch_header_rec => l_batch_header,
3774 x_batch_step_rec => x_batch_step,
3775 x_exception_material_tbl => x_exception_material,
3776 p_ignore_exception => FND_API.G_FALSE);
3777 IF x_return_status = 'X' THEN
3778 RAISE step_alloc_error;
3779 END IF;
3780 IF x_return_status = 'S' THEN
3781 IF p_act_step_qty IS NOT NULL THEN
3782 SELECT actual_step_qty INTO l_step_qty
3783 FROM gme_batch_steps
3784 WHERE batch_id = p_batch_id
3785 AND batchstep_id = p_step_id;
3786 IF l_step_qty <> p_act_step_qty THEN /* Update Act Step Qty */
3787 UPDATE gme_batch_steps
3788 SET actual_step_qty = p_act_step_qty
3789 WHERE batch_id = p_batch_id
3790 AND batchstep_id = p_step_id;
3791 END IF;
3792 END IF;
3793
3794 -- Bug 13986776 - Added Code to call save batch.
3795 gme_api_pub.save_batch(x_return_status => x_return_status,
3796 p_header_id => gme_common_pvt.get_txn_header_id,
3797 p_table => 2,
3798 p_commit => 'T',
3799 p_clear_qty_cache => 'T');
3800
3801 END IF;
3802 EXCEPTION
3803 WHEN expected_error THEN
3804 x_return_status := FND_API.G_RET_STS_ERROR;
3805 WHEN step_alloc_error THEN
3806 ROLLBACK TO SAVEPOINT complete_step_mobile ;
3807 FND_MESSAGE.SET_NAME('GME', 'GME_API_UNALLOC_MATERIALS');
3808 x_message_list := FND_MESSAGE.GET;
3809 WHEN validate_step_error THEN
3810 gme_common_pvt.count_and_get (x_count => x_message_count
3811 ,p_encoded => fnd_api.g_false
3812 ,x_data => x_message_list);
3813 WHEN validate_step_status_error THEN
3814 FND_MESSAGE.SET_NAME('GME','GME_API_INV_BATCH_CMPL_STEP');
3815 x_message_list := FND_MESSAGE.GET;
3816 WHEN OTHERS THEN
3817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3818 END complete_step;
3819
3820 /*###############################################################
3821 # DESCRIPTION
3822 # Bug 4962372 Invoke the End resource transaction API
3823 ###############################################################*/
3824 PROCEDURE End_Cmplt_Actual_Rsrc_Txn(
3825 p_trans_id IN NUMBER
3826 ,p_organization_id IN NUMBER
3827 ,p_end_date IN DATE
3828 ,p_reason_id IN NUMBER
3829 ,p_instance_id IN NUMBER
3830 ,p_trans_date IN DATE
3831 ,p_uname IN VARCHAR2
3832 ,p_uid IN NUMBER
3833 ,x_trans_id OUT NOCOPY NUMBER
3834 ,x_return_status OUT NOCOPY VARCHAR2
3835 ,x_error_msg OUT NOCOPY VARCHAR2) IS
3836
3837 l_message_count NUMBER;
3838 l_in_rsrc_txn_rec gme_resource_txns%ROWTYPE;
3839 l_rsrc_txn_rec gme_resource_txns%ROWTYPE;
3840 l_api_name VARCHAR2(30) := 'End_Cmplt_Actual_Rsrc_Txn';
3841 BEGIN
3842 x_return_status := FND_API.G_RET_STS_SUCCESS;
3843 x_error_msg := '';
3844 FND_PROFILE.put('USER_ID',to_char(p_uid));
3845 gme_common_pvt.g_user_name := p_uname;
3846 gme_common_pvt.g_user_ident := p_uid;
3847 gme_common_pvt.g_login_id := TO_NUMBER (fnd_profile.VALUE ('LOGIN_ID'));
3848 gme_common_pvt.g_user_ident := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
3849
3850 l_in_rsrc_txn_rec.poc_trans_id := p_trans_id;
3851 l_in_rsrc_txn_rec.organization_id := p_organization_id;
3852 l_in_rsrc_txn_rec.trans_date := p_trans_date;
3853 l_in_rsrc_txn_rec.instance_id := p_instance_id;
3854 l_in_rsrc_txn_rec.end_date := p_end_date;
3855 l_in_rsrc_txn_rec.reason_id := p_reason_id;
3856
3857 GME_API_PUB.end_cmplt_actual_rsrc_txn (
3858 p_api_version => 2.0
3859 ,p_init_msg_list => FND_API.G_TRUE
3860 ,p_commit => FND_API.G_TRUE
3861 ,p_instance_no => NULL
3862 ,p_reason_name => NULL
3863 ,p_rsrc_txn_rec => l_in_rsrc_txn_rec
3864 ,x_rsrc_txn_rec => l_rsrc_txn_rec
3865 ,x_message_count => l_message_count
3866 ,x_message_list => x_error_msg
3867 ,x_return_status => x_return_status);
3868 x_trans_id := l_rsrc_txn_rec.poc_trans_id;
3869 EXCEPTION
3870 WHEN OTHERS THEN
3871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3872 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3873 END End_Cmplt_Actual_Rsrc_Txn;
3874
3875 /*###############################################################
3876 # DESCRIPTION
3877 # This procedure determines whether batch is ASQC or not.
3878 ###############################################################*/
3879 PROCEDURE get_ASQC ( p_batch_id IN NUMBER,
3880 x_ASQC_status OUT NOCOPY NUMBER) IS
3881 BEGIN
3882 SELECT NVL(automatic_step_calculation,0) INTO x_ASQC_status
3883 FROM gme_batch_header
3884 WHERE batch_id = p_batch_id;
3885 EXCEPTION
3886 WHEN OTHERS THEN NULL;
3887 END get_ASQC;
3888
3889 /*###############################################################
3890 # DESCRIPTION
3891 # This procedure returns the system date and time.
3892 ###############################################################*/
3893 PROCEDURE get_system_date ( p_date_format IN VARCHAR2,
3894 x_sys_date OUT NOCOPY VARCHAR2) IS
3895 BEGIN
3896 SELECT TO_CHAR(sysdate, p_date_format||HOUR_MIN_SEC_FORMAT_STRING)
3897 INTO x_sys_date
3898 FROM sys.DUAL;
3899 END get_system_date;
3900
3901 /*###############################################################
3902 # DESCRIPTION
3903 # This procedure calls the GME release_step api.
3904 ###############################################################*/
3905
3906 PROCEDURE release_step ( p_batch_id IN NUMBER,
3907 p_step_id IN NUMBER,
3908 p_act_strt_dt IN VARCHAR2,
3909 p_date_format IN VARCHAR2,
3910 p_uname IN VARCHAR2,
3911 p_uid IN NUMBER,
3912 x_return_status OUT NOCOPY VARCHAR2,
3913 x_message_count OUT NOCOPY NUMBER,
3914 x_message_list OUT NOCOPY VARCHAR2) IS
3915 l_step_qty NUMBER;
3916 l_count NUMBER;
3917 l_act_strt_dt DATE;
3918 message VARCHAR2(2000);
3919 l_api_name VARCHAR2(30) := 'release_step';
3920 l_batch_step gme_batch_steps%ROWTYPE;
3921 l_batch_step_out gme_batch_steps%ROWTYPE;
3922 x_batch_step gme_batch_steps%ROWTYPE;
3923 l_batch_hdr_rec gme_batch_header%ROWTYPE;
3924 x_exception_material gme_common_pvt.exceptions_tab;
3925 step_alloc_error EXCEPTION;
3926 expected_error EXCEPTION;
3927 validate_step_error EXCEPTION;
3928 validate_step_status_error EXCEPTION;
3929 BEGIN
3930 SAVEPOINT release_step_mobile;
3931 x_return_status := FND_API.G_RET_STS_SUCCESS;
3932
3933 l_act_strt_dt := TO_DATE(p_act_strt_dt, p_date_format||HOUR_MIN_SEC_FORMAT_STRING);
3934
3935 fnd_profile.put('USER_ID',to_char(p_uid));
3936 gme_common_pvt.g_user_name := p_uname;
3937 gme_common_pvt.g_user_ident := p_uid;
3938 gme_common_pvt.g_login_id := TO_NUMBER (fnd_profile.VALUE ('LOGIN_ID'));
3939 gme_common_pvt.g_user_ident := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
3940
3941 l_batch_hdr_rec.batch_id := p_batch_id;
3942 IF NOT (gme_batch_header_dbl.fetch_row(l_batch_hdr_rec, l_batch_hdr_rec)) THEN
3943 RAISE expected_error;
3944 END IF;
3945
3946 l_batch_step.batch_id := p_batch_id;
3947 l_batch_step.batchstep_id := p_step_id;
3948 IF NOT (gme_batch_steps_dbl.fetch_row(l_batch_step, l_batch_step)) THEN
3949 RAISE expected_error;
3950 END IF;
3951 l_batch_step.actual_start_date := l_act_strt_dt;
3952
3953 -- Bug 4774944 Rework.
3954 -- Check for 'Step controls batch status' parameter.
3955 IF l_batch_hdr_rec.batch_status = 1 AND
3956 (gme_common_pvt.g_step_controls_batch_sts_ind <> 1 OR
3957 l_batch_hdr_rec.parentline_id IS NOT NULL ) THEN
3958 RAISE validate_step_status_error;
3959 END IF;
3960
3961 -- Bug 4774944
3962 -- Added call to validate step for release.
3963
3964 gme_release_batch_step_pvt.validate_step_for_release
3965 (p_batch_header_rec => l_batch_hdr_rec
3966 ,p_batch_step_rec => l_batch_step
3967 ,x_batch_step_rec => l_batch_step_out
3968 ,x_return_status => x_return_status);
3969
3970 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3971 RAISE validate_step_error;
3972 END IF;
3973
3974 gme_api_main.release_step (
3975 p_validation_level => gme_common_pvt.g_max_errors,
3976 p_init_msg_list => FND_API.G_TRUE,
3977 x_message_count => x_message_count,
3978 x_message_list => x_message_list,
3979 x_return_status => x_return_status,
3980 p_batch_step_rec => l_batch_step,
3981 p_batch_header_rec => l_batch_hdr_rec,
3982 x_batch_step_rec => x_batch_step,
3983 x_exception_material_tbl => x_exception_material,
3984 p_ignore_exception => FND_API.G_FALSE);
3985 IF x_return_status = 'X' THEN
3986 RAISE step_alloc_error;
3987 --
3988 -- bug 10389977
3989 -- Added the code to perform the transactions on releasing the step
3990 --
3991 ELSE
3992 gme_api_pub.save_batch(x_return_status => x_return_status,
3993 p_header_id => gme_common_pvt.get_txn_header_id,
3994 p_table => 2,
3995 p_commit => 'T',
3996 p_clear_qty_cache => 'T');
3997 END IF;
3998 EXCEPTION
3999 WHEN expected_error THEN
4000 x_return_status := FND_API.G_RET_STS_ERROR;
4001 WHEN step_alloc_error THEN
4002 ROLLBACK TO SAVEPOINT release_step_mobile ;
4003 FND_MESSAGE.SET_NAME('GME', 'GME_API_UNALLOC_MATERIALS');
4004 x_message_list := FND_MESSAGE.GET;
4005 WHEN validate_step_error THEN
4006 GME_COMMON_PVT.count_and_get (x_count => l_count
4007 ,p_encoded => fnd_api.g_false
4008 ,x_data => x_message_list);
4009
4010 WHEN validate_step_status_error THEN
4011 FND_MESSAGE.SET_NAME('GME','GME_API_INV_BATCH_REL_STEP');
4012 x_message_list := FND_MESSAGE.GET;
4013 WHEN OTHERS THEN
4014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4015 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4016 END release_step;
4017
4018 /*###############################################################
4019 # DESCRIPTION
4020 # Bug 4962372 Invoke the Start resource transaction API
4021 ###############################################################*/
4022 PROCEDURE Start_Cmplt_Actual_Rsrc_Txn(
4023 p_resource_id IN NUMBER
4024 ,p_organization_id IN NUMBER
4025 ,p_start_date IN DATE
4026 ,p_instance_id IN NUMBER
4027 ,p_trans_date IN DATE
4028 ,p_reason_id IN NUMBER
4029 ,p_uname IN VARCHAR2
4030 ,p_uid IN NUMBER
4031 ,x_trans_id OUT NOCOPY NUMBER
4032 ,x_return_status OUT NOCOPY VARCHAR2
4033 ,x_error_msg OUT NOCOPY VARCHAR2) IS
4034 l_message_count NUMBER;
4035 l_rsrc_txn_rec gme_resource_txns%ROWTYPE;
4036 l_in_rsrc_txn_rec gme_resource_txns%ROWTYPE;
4037 l_api_name VARCHAR2(30) := 'Start_Cmplt_Actual_Rsrc_Txn';
4038 BEGIN
4039 x_return_status := FND_API.G_RET_STS_SUCCESS;
4040 x_error_msg := '';
4041 FND_PROFILE.put('USER_ID',to_char(p_uid));
4042 gme_common_pvt.g_user_name := p_uname;
4043 gme_common_pvt.g_user_ident := p_uid;
4044 gme_common_pvt.g_login_id := TO_NUMBER (fnd_profile.VALUE ('LOGIN_ID'));
4045 gme_common_pvt.g_user_ident := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
4046 l_in_rsrc_txn_rec.line_id := p_resource_id;
4047 l_in_rsrc_txn_rec.organization_id := p_organization_id;
4048 l_in_rsrc_txn_rec.start_date := p_start_date;
4049 l_in_rsrc_txn_rec.end_date := p_start_date;
4050 l_in_rsrc_txn_rec.instance_id := p_instance_id;
4051 l_in_rsrc_txn_rec.trans_date := p_trans_date;
4052 l_in_rsrc_txn_rec.reason_id := p_reason_id;
4053
4054 GME_API_PUB.start_cmplt_actual_rsrc_txn (
4055 p_api_version => 2.0
4056 ,p_init_msg_list => FND_API.G_TRUE
4057 ,p_commit => FND_API.G_TRUE
4058 ,p_org_code => NULL
4059 ,p_instance_no => NULL
4060 ,p_rsrc_txn_rec => l_in_rsrc_txn_rec
4061 ,x_rsrc_txn_rec => l_rsrc_txn_rec
4062 ,x_message_count => l_message_count
4063 ,x_message_list => x_error_msg
4064 ,x_return_status => x_return_status);
4065 x_trans_id := l_rsrc_txn_rec.poc_trans_id;
4066 EXCEPTION
4067 WHEN OTHERS THEN
4068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4069 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4070 END Start_Cmplt_Actual_Rsrc_Txn;
4071
4072 /*###############################################################
4073 # DESCRIPTION
4074 # This procedure Validates that a date has been entered if
4075 # not it will be initialized. Following scenarios are checked
4076 # Step Actual Completin Date >= Batch Step Actual Start Date
4077 # Actual Completion Date <= Sysdate
4078 ###############################################################*/
4079 PROCEDURE Validate_Step_Completion_Date (p_start_date IN VARCHAR2,
4080 p_complt_date IN VARCHAR2,
4081 p_date_format IN VARCHAR2,
4082 p_batch_id IN NUMBER,
4083 x_return_status OUT NOCOPY VARCHAR2,
4084 x_error_msg OUT NOCOPY VARCHAR2) IS
4085 l_sysdate DATE;
4086 l_status NUMBER(5);
4087 l_batch_strt_dt DATE;
4088 l_complt_date DATE;
4089 FUTURE_DATE_EX EXCEPTION;
4090 INVALID_STEP_COMPLT_DATE EXCEPTION;
4091 l_api_name VARCHAR2(30) := 'Validate_Step_Completion_Date';
4092
4093 --
4094 -- bug 14376915
4095 --
4096 l_start_date DATE;
4097 BEGIN
4098 x_return_status := FND_API.G_RET_STS_SUCCESS;
4099 l_sysdate := SYSDATE;
4100 l_complt_date := TO_DATE(p_complt_date, p_date_format||HOUR_MIN_SEC_FORMAT_STRING);
4101
4102 --
4103 -- bug 14376915
4104 --
4105 l_start_date := TO_DATE(p_start_date, p_date_format||HOUR_MIN_SEC_FORMAT_STRING);
4106
4107 IF l_complt_date > SYSDATE THEN
4108 RAISE FUTURE_DATE_EX;
4109 END IF;
4110
4111 --
4112 -- bug 14376915
4113 --
4114 -- IF p_complt_date < p_start_date THEN
4115 IF l_complt_date < l_start_date THEN
4116 RAISE INVALID_STEP_COMPLT_DATE;
4117 END IF;
4118 EXCEPTION
4119 WHEN FUTURE_DATE_EX THEN
4120 x_return_status := FND_API.G_RET_STS_ERROR;
4121 FND_MESSAGE.SET_NAME('GMA', 'SY_NOFUTUREDATE');
4122 x_error_msg := FND_MESSAGE.GET;
4123 WHEN INVALID_STEP_COMPLT_DATE THEN
4124 x_return_status := FND_API.G_RET_STS_ERROR;
4125 FND_MESSAGE.SET_NAME('GME', 'GME_CMPLT_DATE_OUTSIDE');
4126 x_error_msg := FND_MESSAGE.GET;
4127 WHEN OTHERS THEN
4128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4129 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4130 x_error_msg := FND_MESSAGE.GET;
4131 END Validate_Step_Completion_Date;
4132
4133 /*###############################################################
4134 # DESCRIPTION
4135 # This procedure Validates the step qty entered. It ensures that
4136 # the data entered is a number.
4137 ###############################################################*/
4138 PROCEDURE Validate_Step_Qty ( p_step_qty IN VARCHAR2,
4139 x_return_status OUT NOCOPY VARCHAR2,
4140 x_error_msg OUT NOCOPY VARCHAR2) IS
4141 l_step_qty NUMBER;
4142 l_api_name VARCHAR2(30) := 'Validate_Step_Qty';
4143 BEGIN
4144 x_return_status := FND_API.G_RET_STS_SUCCESS;
4145 l_step_qty := TO_NUMBER(p_step_qty);
4146 EXCEPTION
4147 WHEN VALUE_ERROR THEN
4148 x_return_status := FND_API.G_RET_STS_ERROR;
4149 FND_MESSAGE.SET_NAME('GME', 'GME_VALUE_ERROR');
4150 x_error_msg := FND_MESSAGE.GET;
4151 WHEN OTHERS THEN
4152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4153 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4154 x_error_msg := FND_MESSAGE.GET;
4155 END Validate_Step_Qty;
4156
4157 /*###############################################################
4158 # DESCRIPTION
4159 # This procedure Validates that a date has been entered if
4160 # not it will be initialized. Following scenarios are checked
4161 # Step Rel Actual Start Date >= Batch Actual Start Date
4162 # Actual Start Date <= Sysdate
4163 ###############################################################*/
4164 PROCEDURE Validate_Step_Start_Date (p_start_date IN VARCHAR2,
4165 p_date_format IN VARCHAR2,
4166 p_batch_id IN NUMBER,
4167 x_return_status OUT NOCOPY VARCHAR2,
4168 x_error_msg OUT NOCOPY VARCHAR2) IS
4169 l_sysdate DATE;
4170 l_status NUMBER(5);
4171 l_batch_strt_dt DATE;
4172 l_start_date DATE;
4173 l_api_name VARCHAR2(30) := 'Validate_Step_Start_Date';
4174 CURSOR Cur_get_stat IS
4175 SELECT batch_status
4176 FROM gme_batch_header
4177 WHERE batch_id = p_batch_id;
4178 CURSOR Cur_get_batch_start_dt IS
4179 SELECT actual_start_date
4180 FROM gme_batch_header
4181 WHERE batch_id = p_batch_id;
4182 FUTURE_DATE_EX EXCEPTION;
4183 INVALID_STEP_START_DATE EXCEPTION;
4184 BEGIN
4185 x_return_status := FND_API.G_RET_STS_SUCCESS;
4186 l_sysdate := SYSDATE;
4187 l_start_date := TO_DATE(p_start_date, p_date_format||HOUR_MIN_SEC_FORMAT_STRING);
4188 IF l_start_date > SYSDATE THEN
4189 RAISE FUTURE_DATE_EX;
4190 END IF;
4191 OPEN Cur_get_stat;
4192 FETCH Cur_get_stat INTO l_status;
4193 CLOSE Cur_get_stat;
4194 IF l_status = 2 THEN
4195 OPEN Cur_get_batch_start_dt;
4196 FETCH Cur_get_batch_start_dt INTO l_batch_strt_dt;
4197 CLOSE Cur_get_batch_start_dt;
4198 IF l_batch_strt_dt > l_start_date THEN
4199 RAISE INVALID_STEP_START_DATE;
4200 END IF;
4201 END IF;
4202 EXCEPTION
4203 WHEN FUTURE_DATE_EX THEN
4204 x_return_status := FND_API.G_RET_STS_ERROR;
4205 FND_MESSAGE.SET_NAME('GMA', 'SY_NOFUTUREDATE');
4206 x_error_msg := FND_MESSAGE.GET;
4207 WHEN INVALID_STEP_START_DATE THEN
4208 x_return_status := FND_API.G_RET_STS_ERROR;
4209 FND_MESSAGE.SET_NAME('GME', 'GME_STEP_START_BATCH_START_ERR');
4210 x_error_msg := FND_MESSAGE.GET;
4211 WHEN OTHERS THEN
4212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4213 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4214 x_error_msg := FND_MESSAGE.GET;
4215 END Validate_Step_Start_Date;
4216
4217 PROCEDURE check_close_period(p_org_id IN NUMBER,
4218 p_trans_date IN DATE,
4219 x_return_status OUT NOCOPY VARCHAR2,
4220 x_message OUT NOCOPY VARCHAR2) IS
4221 l_count NUMBER;
4222 BEGIN
4223 x_return_status := FND_API.G_RET_STS_SUCCESS;
4224 IF NOT (gme_common_pvt.check_close_period(p_org_id, p_trans_date)) THEN
4225 gme_common_pvt.count_and_get(p_encoded => FND_API.G_FALSE,
4226 x_count => l_count,
4227 x_data => x_message);
4228 END IF;
4229 END check_close_period;
4230 /*###############################################################
4231 # DESCRIPTION
4232 # This procedure Loads Resource Transactions
4233 ###############################################################*/
4234 PROCEDURE Load_resource_Txns (p_batch_id IN NUMBER,
4235 x_row_count OUT NOCOPY NUMBER,
4236 x_return_status OUT NOCOPY VARCHAR2) IS
4237
4238 l_batch_rec gme_batch_header%ROWTYPE;
4239 expected_error EXCEPTION;
4240 BEGIN
4241 l_batch_rec.batch_id := p_batch_id;
4242 IF NOT (gme_batch_header_dbl.fetch_row(l_batch_rec, l_batch_rec)) THEN
4243 RAISE expected_error;
4244 END IF;
4245
4246 gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_rec
4247 ,x_rsc_row_count => x_row_count
4248 ,x_return_status => x_return_status);
4249 EXCEPTION
4250 WHEN expected_error THEN
4251 x_return_status := FND_API.G_RET_STS_ERROR;
4252 WHEN OTHERS THEN
4253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4254 END Load_resource_Txns;
4255
4256 /* Bug#5663458 Begin
4257 * Created the following procedure. This procedure is to get transaction date of
4258 * material line depends on all release type and step associations
4259 */
4260 PROCEDURE fetch_txn_date(p_material_detail_id IN NUMBER,
4261 x_trans_date OUT NOCOPY VARCHAR2,
4262 x_return_status OUT NOCOPY VARCHAR2,
4263 x_error_msg OUT NOCOPY VARCHAR2)
4264 IS
4265 l_trans_date DATE;
4266 l_count NUMBER;
4267 l_date_format VARCHAR2(100);
4268 BEGIN
4269 IF (g_debug IS NOT NULL) THEN
4270 gme_debug.log_initialize ('RelieveRsrvPndLots');
4271 END IF;
4272
4273 x_return_status := fnd_api.g_ret_sts_success;
4274 x_error_msg := ' ';
4275
4276 --calling gme_common_pvt routine
4277 gme_common_pvt.fetch_trans_date(p_material_detail_id => p_material_detail_id,
4278 p_invoke_mode => 'T',
4279 x_trans_date => l_trans_date,
4280 x_return_status => x_return_status);
4281
4282 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4283 gme_common_pvt.count_and_get (x_count => l_count
4284 ,p_encoded => fnd_api.g_false
4285 ,x_data => x_error_msg);
4286
4287 END IF;
4288
4289 --getting the date into either mwa date format mask or ICX date format mask
4290 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
4291
4292 IF l_date_format IS NULL THEN
4293 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
4294 END IF;
4295
4296 x_trans_date := TO_CHAR(l_trans_date,l_date_format||HOUR_MIN_SEC_FORMAT_STRING);
4297
4298 EXCEPTION
4299 WHEN OTHERS THEN
4300 IF g_debug <= gme_debug.g_log_unexpected THEN
4301 gme_debug.put_line('When others exception in fetch_txn_date');
4302 END IF;
4303 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','fetch_txn_date');
4304 x_return_status := fnd_api.g_ret_sts_unexp_error;
4305 x_error_msg := fnd_message.get;
4306 END;
4307
4308 --Bug#5867209 added restricted subinv code
4309 PROCEDURE Validate_Subinv_Master(p_organization_id IN NUMBER,
4310 p_subinventory_code IN VARCHAR2,
4311 p_inventory_item_id IN NUMBER,
4312 p_restrict_code IN NUMBER,
4313 x_locator_type OUT NOCOPY VARCHAR2,
4314 x_return_status OUT NOCOPY VARCHAR2,
4315 x_error_message OUT NOCOPY VARCHAR2)
4316 IS
4317 CURSOR x_sub_lov IS
4318 SELECT NVL(locator_type, 1)
4319 FROM mtl_secondary_inventories
4320 WHERE organization_id = p_organization_id
4321 AND NVL(disable_date, SYSDATE + 1) > SYSDATE
4322 AND secondary_inventory_name = p_subinventory_code
4323 AND quantity_tracked = 1;
4324
4325 CURSOR x_sub_lov1 IS
4326 SELECT NVL(s.locator_type, 1)
4327 FROM mtl_secondary_inventories s,
4328 mtl_item_sub_inventories i
4329 WHERE s.secondary_inventory_name = i.secondary_inventory
4330 AND s.organization_id = i.organization_id
4331 AND s.organization_id = p_organization_id
4332 AND i.inventory_item_id = p_inventory_item_id
4333 AND NVL(s.disable_date, SYSDATE + 1) > SYSDATE
4334 AND secondary_inventory_name = p_subinventory_code
4335 AND s.quantity_tracked = 1;
4336
4337 l_exists NUMBER;
4338 ERROR_INV_SUBINV EXCEPTION;
4339 BEGIN
4340 x_return_status := fnd_api.g_ret_sts_success;
4341 x_error_message := ' ';
4342
4343 IF p_restrict_code = 1 THEN
4344 OPEN x_sub_lov1;
4345 FETCH x_sub_lov1 INTO x_locator_type;
4346 IF x_sub_lov1%NOTFOUND THEN
4347 RAISE ERROR_INV_SUBINV;
4348 END IF;
4349 CLOSE x_sub_lov1 ;
4350 ELSE
4351 OPEN x_sub_lov;
4352 FETCH x_sub_lov INTO x_locator_type;
4353 IF x_sub_lov%NOTFOUND THEN
4354 RAISE ERROR_INV_SUBINV;
4355 END IF;
4356 CLOSE x_sub_lov ;
4357 END IF;
4358
4359 EXCEPTION
4360 WHEN ERROR_INV_SUBINV THEN
4361 x_return_status := fnd_api.g_ret_sts_error;
4362 FND_MESSAGE.SET_NAME('GME','GME_NOT_VALID_SUBINV');
4363 x_error_message := FND_MESSAGE.GET;
4364 END Validate_Subinv_Master;
4365
4366 --Bug#5867209 added restricted locator code
4367 PROCEDURE Validate_Locator_Master(p_organization_id IN NUMBER,
4368 p_subinventory_code IN VARCHAR2,
4369 p_locator_code IN VARCHAR2,
4370 p_inventory_item_id IN NUMBER,
4371 p_restrict_code IN NUMBER,
4372 x_locator_id OUT NOCOPY VARCHAR2,
4373 x_return_status OUT NOCOPY VARCHAR2,
4374 x_error_message OUT NOCOPY VARCHAR2)
4375 IS
4376 CURSOR x_loc_lov IS
4377 SELECT inventory_location_id
4378 FROM wms_item_locations_kfv
4379 WHERE organization_id = p_organization_id
4380 AND NVL(disable_date, SYSDATE + 1) > SYSDATE
4381 AND subinventory_code = p_subinventory_code
4382 AND concatenated_segments = p_locator_code;
4383
4384 CURSOR x_loc_lov1 IS
4385 SELECT a.inventory_location_id
4386 FROM wms_item_locations_kfv a,
4387 mtl_secondary_locators b
4388 WHERE b.organization_id = p_organization_Id
4389 AND b.inventory_item_id = p_Inventory_Item_Id
4390 AND b.subinventory_code = p_Subinventory_Code
4391 AND a.inventory_location_id = b.secondary_locator
4392 AND NVL(a.disable_date, SYSDATE+1) > SYSDATE
4393 AND a.concatenated_segments = p_locator_code;
4394
4395 l_exists NUMBER;
4396 ERROR_INVALID_LOCATOR EXCEPTION;
4397 BEGIN
4398 x_return_status := fnd_api.g_ret_sts_success;
4399 x_error_message := ' ';
4400
4401 IF p_restrict_code = 1 THEN
4402 OPEN x_loc_lov1;
4403 FETCH x_loc_lov1 INTO x_locator_id;
4404 IF x_loc_lov1%NOTFOUND THEN
4405 RAISE ERROR_INVALID_LOCATOR;
4406 END IF;
4407 CLOSE x_loc_lov1 ;
4408 ELSE
4409 OPEN x_loc_lov;
4410 FETCH x_loc_lov INTO x_locator_id;
4411 IF x_loc_lov%NOTFOUND THEN
4412 RAISE ERROR_INVALID_LOCATOR;
4413 END IF;
4414 CLOSE x_loc_lov ;
4415 END IF;
4416 EXCEPTION
4417 WHEN ERROR_INVALID_LOCATOR THEN
4418 x_return_status := fnd_api.g_ret_sts_error;
4419 FND_MESSAGE.SET_NAME('GME','GME_NOT_VALID_LOC');
4420 x_error_message := FND_MESSAGE.GET;
4421 END Validate_Locator_Master;
4422 /* Bug#5663458 End*/
4423
4424 --Bug#5867209 added the procedure
4425 PROCEDURE Fetch_subinv_locator(p_batch_id IN NUMBER,
4426 p_material_detail_id IN NUMBER,
4427 x_subinventory_code OUT NOCOPY VARCHAR2,
4428 x_locator OUT NOCOPY VARCHAR2,
4429 x_locator_id OUT NOCOPY VARCHAR2,
4430 x_return_status OUT NOCOPY VARCHAR2,
4431 x_error_msg OUT NOCOPY VARCHAR2)
4432 IS
4433 CURSOR c_subinv_loc IS
4434 SELECT subinventory, loc.concatenated_segments,
4435 m.locator_id
4436 FROM gme_material_details m, wms_item_locations_kfv loc
4437 WHERE m.subinventory = loc.subinventory_code (+)
4438 AND m.locator_id = loc.inventory_location_id (+)
4439 AND m.batch_id = p_batch_id
4440 AND m.material_Detail_id = p_material_detail_id;
4441 BEGIN
4442 IF (g_debug IS NOT NULL) THEN
4443 gme_debug.log_initialize ('MobileFetchSubinvLoc');
4444 END IF;
4445
4446
4447 x_return_status := FND_API.G_RET_STS_SUCCESS;
4448 x_error_msg := ' ';
4449
4450 OPEN c_subinv_loc;
4451 FETCH c_subinv_loc INTO x_subinventory_code,x_locator,x_locator_id ;
4452 CLOSE c_subinv_loc;
4453
4454 EXCEPTION
4455 WHEN OTHERS THEN
4456 IF g_debug <= gme_debug.g_log_unexpected THEN
4457 gme_debug.put_line('When others exception in Fetch_subinv_locator');
4458 END IF;
4459 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Fetch_subinv_locator');
4460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4461 x_error_msg := fnd_message.get;
4462 END Fetch_subinv_locator;
4463
4464 -- nsinghi bug#5209065 START. Added following Procs.
4465 PROCEDURE get_expiration_date (
4466 x_expiration_date OUT NOCOPY DATE
4467 , x_return_status OUT NOCOPY VARCHAR2
4468 ) IS
4469
4470 l_mti_txn_tbl INV_CALCULATE_EXP_DATE.MTI_TAB;
4471 l_mti_txn_rec MTL_TRANSACTIONS_INTERFACE%ROWTYPE;
4472 l_mtli_txn_rec MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE;
4473 l_mmtt_txn_rec MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE;
4474 l_mtlt_txn_rec MTL_TRANSACTION_LOTS_TEMP%ROWTYPE;
4475 l_lot_expiration_date DATE;
4476
4477 BEGIN
4478 x_return_status := FND_API.G_RET_STS_SUCCESS;
4479 IF (g_debug IS NOT NULL) THEN
4480 gme_debug.log_initialize ('MobileGetExpDate');
4481 END IF;
4482
4483 l_mti_txn_tbl := inv_calculate_exp_date.get_mti_tbl;
4484 IF l_mti_txn_tbl.COUNT > 0 THEN
4485 l_mti_txn_rec := l_mti_txn_tbl(0);
4486 inv_calculate_exp_date.get_lot_expiration_date(
4487 p_mtli_lot_rec => l_mtli_txn_rec
4488 ,p_mti_trx_rec => l_mti_txn_rec
4489 ,p_mtlt_lot_rec => l_mtlt_txn_rec
4490 ,p_mmtt_trx_rec => l_mmtt_txn_rec
4491 ,p_table => 1
4492 ,x_lot_expiration_date => l_lot_expiration_date
4493 ,x_return_status => x_return_status);
4494
4495 inv_calculate_exp_date.purge_mti_tab;
4496 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4497 IF g_debug <= gme_debug.g_log_statement THEN
4498 gme_debug.put_line('Program inv_calculate_exp_date.get_lot_expiration_date has failed with a Unexpected exception');
4499 END IF;
4500 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
4501 FND_MESSAGE.SET_TOKEN('PROG_NAME','inv_calculate_exp_date.get_lot_expiration_date');
4502 fnd_msg_pub.ADD;
4503 RAISE fnd_api.g_exc_unexpected_error;
4504 END IF;
4505 IF g_debug = 1 THEN
4506 gme_debug.put_line('l_lot_expiration_date '||l_lot_expiration_date);
4507 END IF;
4508 x_expiration_date := l_lot_expiration_date;
4509 -- ELSE
4510 -- x_expiration_date := x_origination_date + l_get_dft_attr_rec.shelf_life_days;
4511 END IF;
4512 EXCEPTION
4513 WHEN OTHERS THEN
4514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4515 IF g_debug <= gme_debug.g_log_unexpected THEN
4516 gme_debug.put_line('WHEN OTHERS exception : '||SQLERRM);
4517 END IF;
4518 END get_expiration_date;
4519
4520 PROCEDURE get_exp_action_date (
4521 p_expiration_date IN DATE
4522 , p_exp_act_interval IN NUMBER
4523 , x_exp_act_date OUT NOCOPY DATE
4524 , x_return_status OUT NOCOPY VARCHAR2
4525 ) IS
4526 BEGIN
4527 x_return_status := FND_API.G_RET_STS_SUCCESS;
4528 x_exp_act_date := p_expiration_date;
4529
4530 IF p_expiration_date IS NOT NULL AND p_exp_act_interval IS NOT NULL THEN
4531 x_exp_act_date := p_expiration_date + p_exp_act_interval;
4532 END IF;
4533
4534 EXCEPTION
4535 WHEN OTHERS THEN
4536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4537 IF g_debug <= gme_debug.g_log_unexpected THEN
4538 gme_debug.put_line('WHEN OTHERS exception : '||SQLERRM);
4539 END IF;
4540 END get_exp_action_date;
4541 -- nsinghi bug#5209065 END.
4542
4543 --
4544 -- Bug 9367054
4545 -- New procedure to print the labels for
4546 -- product transactions
4547 --
4548 PROCEDURE print_label(p_txn_header_id IN NUMBER,
4549 x_return_status OUT NOCOPY VARCHAR2,
4550 x_error_msg OUT NOCOPY VARCHAR2) as
4551
4552 --
4553 -- bug 12382498
4554 -- Querying the transaction_type_id as well
4555 --
4556 CURSOR get_details IS
4557 SELECT
4558 msi.concatenated_segments item,
4559 bh.batch_no batch_no,
4560 mmt.transaction_id,
4561 mmt.created_by,
4562 mmt.transaction_type_id
4563 FROM gme_batch_header_vw bh,
4564 mtl_parameters mp,
4565 gme_material_details bl,
4566 mtl_system_items_vl msi,
4567 mtl_material_transactions mmt
4568 where mmt.transaction_set_id = p_txn_header_id AND
4569 bh.batch_id = mmt.TRANSACTION_SOURCE_ID AND
4570 bl.material_detail_id = mmt.TRX_SOURCE_LINE_ID AND
4571 mmt.organization_id = mp.organization_id AND
4572 bh.batch_id = bl.batch_id AND
4573 bl.organization_id = msi.organization_id AND
4574 bl.inventory_item_id = msi.inventory_item_id;
4575
4576 l_context gmo_label_mgmt_grp.context_table;
4577 l_item_no varchar2(2000);
4578 l_batch_no varchar2(2000);
4579
4580 l_label_id number;
4581
4582 l_ret_status varchar2(2000);
4583 l_msg_count number;
4584 l_msg_data varchar2(2000);
4585
4586 l_print_status varchar2(2000);
4587 l_txn_id Number;
4588 l_txn_type_id NUMBER;
4589 l_user_id number;
4590 BEGIN
4591 x_return_status := FND_API.G_RET_STS_SUCCESS;
4592 x_error_msg := ' ';
4593
4594 OPEN get_details;
4595 FETCH get_details INTO l_item_no, l_batch_no, l_txn_id, l_user_id, l_txn_type_id;
4596 CLOSE get_details;
4597
4598 --
4599 -- bug 12382498
4600 -- If the transaction is not a prod/byprod completion
4601 -- or if the Label Auto printing is switched off, then the
4602 -- labels are not to be printed
4603 --
4604 IF (l_txn_type_id NOT IN (GME_COMMON_PVT.g_prod_completion, GME_COMMON_PVT.g_byprod_completion) OR
4605 GMO_LABEL_MGMT_GRP.AUTO_PRINT_ENABLED () = FALSE) THEN
4606 x_return_status := 'S';
4607 return;
4608 END IF;
4609
4610 --
4611 -- Build the context object
4612 --
4613 L_CONTEXT(1).NAME :='GME_BATCH_NO';
4614 L_CONTEXT(1).VALUE:= l_batch_no;
4615 L_CONTEXT(1).DISPLAY_SEQUENCE:=1;
4616
4617 L_CONTEXT(2).NAME :='GME_ITEM_NO';
4618 L_CONTEXT(2).VALUE:= l_item_no;
4619 L_CONTEXT(2).DISPLAY_SEQUENCE:=2;
4620
4621 --
4622 -- Add the label details to the GMO tables
4623 --
4624 GMO_LABEL_MGMT_GRP.PRINT_LABEL
4625 (P_API_VERSION => 1,
4626 P_INIT_MSG_LIST =>'T',
4627 X_RETURN_STATUS => l_ret_status,
4628 X_MSG_COUNT => l_msg_count,
4629 X_MSG_DATA => l_msg_data,
4630 P_ENTITY_NAME => 'GME_PRODUCT',
4631 P_ENTITY_KEY => l_txn_id,
4632 P_WMS_BUSINESS_FLOW_CODE => 39,
4633 P_LABEL_TYPE => 14,
4634 P_TRANSACTION_ID => l_txn_id,
4635 P_TRANSACTION_TYPE => 1,
4636 P_APPLICATION_SHORT_NAME => 'GME',
4637 P_REQUESTER => l_user_id,
4638 P_CONTEXT => l_context,
4639 X_LABEL_ID => l_label_id);
4640
4641 --
4642 -- Post the label to Inventory
4643 --
4644 GMO_LABEL_MGMT_GRP.COMPLETE_LABEL_PRINT(
4645 p_api_version => 1,
4646 p_init_msg_list => 'T',
4647 x_return_status => l_ret_status,
4648 x_msg_count => l_msg_count,
4649 x_msg_data => l_msg_data,
4650 P_LABEL_ID => l_label_id,
4651 P_ERECORD_ID => NULL,
4652 P_ERECORD_STATUS => NULL,
4653 x_print_status => l_print_status);
4654
4655 x_return_status := l_ret_status;
4656 EXCEPTION
4657 WHEN OTHERS THEN
4658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4659 END print_label;
4660
4661 END GME_MOBILE_TXN;