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