[Home] [Help]
PACKAGE BODY: APPS.GME_MOBILE_RSRV
Source
1 PACKAGE BODY GME_MOBILE_RSRV AS
2 /* $Header: GMEMORSB.pls 120.11.12000000.2 2007/02/06 13:45:40 svgonugu 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 cursors used by the mobile reservation transactions |
10 | |
11 +===========================================================================+
12 | HISTORY |
13 | |
14 | Date Who What |
15 | ==== === ==== |
16 | 26-Apr-05 Eddie Oumerretane First version |
17 | 23-Jun-06 Shrikant Nene Bug 5263908 |
18 | 23-Jun-06 Shrikant Nene Bug 5263908 |
19 | Changed Create_Reservation procedure to fetch material detail rec |
20 | before calling create_reservation API |
21 | |
22 +===========================================================================*/
23
24 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
25
26 /*+========================================================================+
27 | PROCEDURE NAME
28 | Fetch_Lot_Reservations
29 |
30 | USAGE
31 |
32 | ARGUMENTS
33 |
34 | RETURNS
35 |
36 | HISTORY
37 | Created 26-Apr-05 Eddie Oumerretane
38 |
39 +========================================================================+*/
40 PROCEDURE Fetch_Lot_Reservations(p_organization_id IN NUMBER,
41 p_item_id IN NUMBER,
42 p_lot_number IN VARCHAR2,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_error_msg OUT NOCOPY VARCHAR2,
45 x_rsrv_cursor OUT NOCOPY t_genref)
46 IS
47 l_date_format VARCHAR2(100);
48 BEGIN
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50 x_error_msg := ' ';
51
52 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
53
54 IF l_date_format IS NULL THEN
55 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
56 END IF;
57
58 OPEN x_rsrv_cursor FOR
59
60 SELECT
61 mr.RESERVATION_ID
62 ,TO_CHAR(mr.REQUIREMENT_DATE, l_date_format)
63 ,mr.PRIMARY_UOM_CODE
64 ,mr.RESERVATION_UOM_CODE
65 ,NVL(mr.RESERVATION_QUANTITY,0)
66 ,NVL(mr.PRIMARY_RESERVATION_QUANTITY,0)
67 ,mr.SUBINVENTORY_CODE
68 ,mr.SUBINVENTORY_ID
69 ,NVL(mr.LOCATOR_ID, -1)
70 ,mr.LOT_NUMBER
71 ,mr.LOT_NUMBER_ID
72 ,NVL(mr.DETAILED_QUANTITY,0)
73 ,NVL(mr.SECONDARY_DETAILED_QUANTITY,0)
74 ,NVL(mr.SECONDARY_RESERVATION_QUANTITY,0)
75 ,mr.SECONDARY_UOM_CODE
76 ,mr.inventory_item_id
77 ,mr.revision
78 FROM mtl_reservations mr
79 WHERE mr.organization_id = p_organization_id
80 AND mr.inventory_item_id = p_item_id
81 AND mr.lot_number = p_lot_number;
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 IF g_debug <= gme_debug.g_log_unexpected THEN
86 gme_debug.put_line('When others exception in Fetch Lot Reservation');
87 END IF;
88 fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','fetch_lot_reservations');
89 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
90 x_error_msg := fnd_message.get;
91
92 END Fetch_Lot_Reservations;
93
94 /*+========================================================================+
95 | PROCEDURE NAME
96 | Check_Rsrv_Exist
97 |
98 | USAGE
99 |
100 | ARGUMENTS
101 |
102 | RETURNS
103 |
104 | HISTORY
105 | Created 26-Apr-05 Eddie Oumerretane
106 |
107 +========================================================================+*/
108 PROCEDURE Check_Rsrv_Exist(p_organization_id IN NUMBER,
109 p_batch_id IN NUMBER,
110 p_material_detail_id IN NUMBER,
111 p_subinventory_code IN VARCHAR2,
112 p_locator_id IN NUMBER,
113 p_lot_number IN VARCHAR2,
114 p_exclude_res_id IN NUMBER,
115 x_return_status OUT NOCOPY VARCHAR2,
116 x_error_msg OUT NOCOPY VARCHAR2,
117 x_rsrv_cursor OUT NOCOPY t_genref)
118 IS
119 BEGIN
120 x_return_status := FND_API.G_RET_STS_SUCCESS;
121 x_error_msg := ' ';
122
123
124 OPEN x_rsrv_cursor FOR
125
126 SELECT
127 mr.RESERVATION_ID
128 FROM mtl_reservations mr,
129 wms_item_locations_kfv loc
130 WHERE mr.organization_id = p_organization_id
131 AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
132 AND mr.demand_source_header_id = p_batch_id
133 AND mr.demand_source_line_id = p_material_detail_id
134 AND NVL(mr.subinventory_code, '-1') = NVL(p_subinventory_code, '-1')
135 AND NVL(mr.locator_id, -1) = NVL(p_locator_id, -1)
136 AND mr.organization_id = loc.organization_id(+)
137 AND mr.subinventory_code = loc.subinventory_code(+)
138 AND mr.locator_id = loc.inventory_location_id(+)
139 AND NVL(mr.lot_number, '-1') = NVL(p_lot_number, '-1')
140 AND mr.reservation_id <> NVL(p_exclude_res_id, 0)
141 AND NOT EXISTS (SELECT 1
142 FROM mtl_material_transactions_temp
143 WHERE reservation_id = mr.reservation_id);
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 IF g_debug <= gme_debug.g_log_unexpected THEN
148 gme_debug.put_line('When others exception in Check Rsrv Exist');
149 END IF;
150 fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','Check_Rsrv_Exist');
151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
152 x_error_msg := fnd_message.get;
153
154 END Check_Rsrv_Exist;
155
156 /*+========================================================================+
157 | PROCEDURE NAME
158 | Get_Material_Reservations
159 |
160 | USAGE
161 |
162 | ARGUMENTS
163 |
164 | RETURNS
165 |
166 | HISTORY
167 | Created 26-Apr-05 Eddie Oumerretane
168 |
169 +========================================================================+*/
170 PROCEDURE Get_Material_Reservations(p_organization_id IN NUMBER,
171 p_batch_id IN NUMBER,
172 p_material_detail_id IN NUMBER,
173 p_subinventory_code IN VARCHAR2,
174 p_locator_id IN NUMBER,
175 p_lot_number IN VARCHAR2,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_error_msg OUT NOCOPY VARCHAR2,
178 x_rsrv_cursor OUT NOCOPY t_genref)
179 IS
180 l_date_format VARCHAR2(100);
181 BEGIN
182 x_return_status := FND_API.G_RET_STS_SUCCESS;
183 x_error_msg := ' ';
184
185 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
186
187 IF l_date_format IS NULL THEN
188 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
189 END IF;
190
191 OPEN x_rsrv_cursor FOR
192
193 SELECT
194 mr.RESERVATION_ID
195 ,TO_CHAR(mr.REQUIREMENT_DATE, l_date_format)
196 ,mr.PRIMARY_UOM_CODE
197 ,mr.RESERVATION_UOM_CODE
198 ,NVL(mr.RESERVATION_QUANTITY,0)
199 ,NVL(mr.PRIMARY_RESERVATION_QUANTITY,0)
200 ,mr.SUBINVENTORY_CODE
201 ,mr.SUBINVENTORY_ID
202 ,NVL(mr.LOCATOR_ID, -1)
203 ,mr.LOT_NUMBER
204 ,mr.LOT_NUMBER_ID
205 ,NVL(mr.DETAILED_QUANTITY,0)
206 ,NVL(mr.SECONDARY_DETAILED_QUANTITY,0)
207 ,NVL(mr.SECONDARY_RESERVATION_QUANTITY,0)
208 ,mr.SECONDARY_UOM_CODE
209 ,mr.inventory_item_id
210 ,loc.concatenated_segments
211 ,mr.revision
212 FROM mtl_reservations mr,
213 wms_item_locations_kfv loc
214 WHERE mr.organization_id = p_organization_id
215 AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
216 AND mr.demand_source_header_id = p_batch_id
217 AND mr.demand_source_line_id = p_material_detail_id
218 AND (p_subinventory_code IS NULL OR (mr.subinventory_code = p_subinventory_code))
219 AND (p_locator_id IS NULL OR (mr.locator_id = p_locator_id))
220 AND mr.organization_id = loc.organization_id(+)
221 AND mr.subinventory_code = loc.subinventory_code(+)
222 AND mr.locator_id = loc.inventory_location_id(+)
223 AND (p_lot_number IS NULL OR (mr.lot_number = p_lot_number))
224 AND NOT EXISTS (SELECT 1
225 FROM mtl_material_transactions_temp
226 WHERE reservation_id = mr.reservation_id)
227 ORDER BY mr.requirement_date;
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 IF g_debug <= gme_debug.g_log_unexpected THEN
232 gme_debug.put_line('When others exception in get Mtl Reservation');
233 END IF;
234 fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','get_material_reservations');
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 x_error_msg := fnd_message.get;
237
238 END get_material_reservations;
239
240
241
242 /* Bug#5663458
243 * Created the following procedure. This procedure is to get DLR of material line.
244 * Item revision is also considered to determine DLR
245 */
246 PROCEDURE Get_Material_Dtl_Reservations(p_organization_id IN NUMBER,
247 p_batch_id IN NUMBER,
248 p_material_detail_id IN NUMBER,
249 p_eff_loccontrol IN NUMBER,
250 p_lotcontrol IN NUMBER,
251 p_revcontrol IN NUMBER,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_error_msg OUT NOCOPY VARCHAR2,
254 x_rsrv_cursor OUT NOCOPY t_genref)
255 IS
256 l_date_format VARCHAR2(100);
257 BEGIN
258 x_return_status := FND_API.G_RET_STS_SUCCESS;
259 x_error_msg := ' ';
260
261 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
262
263 IF l_date_format IS NULL THEN
264 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
265 END IF;
266
267 OPEN x_rsrv_cursor FOR
268
269 SELECT
270 mr.RESERVATION_ID
271 ,TO_CHAR(mr.REQUIREMENT_DATE, l_date_format)
272 ,mr.PRIMARY_UOM_CODE
273 ,mr.RESERVATION_UOM_CODE
274 ,NVL(mr.RESERVATION_QUANTITY,0)
275 ,NVL(mr.PRIMARY_RESERVATION_QUANTITY,0)
276 ,mr.SUBINVENTORY_CODE
277 ,mr.SUBINVENTORY_ID
278 ,NVL(mr.LOCATOR_ID, -1)
279 ,mr.LOT_NUMBER
280 ,mr.LOT_NUMBER_ID
281 ,NVL(mr.DETAILED_QUANTITY,0)
282 ,NVL(mr.SECONDARY_DETAILED_QUANTITY,0)
283 ,NVL(mr.SECONDARY_RESERVATION_QUANTITY,0)
284 ,mr.SECONDARY_UOM_CODE
285 ,mr.inventory_item_id
286 ,loc.concatenated_segments
287 ,mr.revision
288 FROM mtl_reservations mr,
289 wms_item_locations_kfv loc
290 WHERE mr.organization_id = p_organization_id
291 AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
292 AND mr.demand_source_header_id = p_batch_id
293 AND mr.demand_source_line_id = p_material_detail_id
294 AND (mr.subinventory_code IS NOT NULL)
295 AND (p_revcontrol = 0 OR (p_revcontrol = 1 AND mr.revision IS NOT NULL))
296 AND (p_eff_loccontrol = 0 OR (p_eff_loccontrol = 1 AND mr.locator_id IS NOT NULL))
297 AND mr.organization_id = loc.organization_id(+)
298 AND mr.subinventory_code = loc.subinventory_code(+)
299 AND mr.locator_id = loc.inventory_location_id(+)
300 AND (p_lotcontrol = 0 OR (p_lotcontrol = 1 AND mr.lot_number IS NOT NULL))
301 AND NOT EXISTS (SELECT 1
302 FROM mtl_material_transactions_temp
303 WHERE reservation_id = mr.reservation_id)
304 ORDER BY mr.requirement_date;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 IF g_debug <= gme_debug.g_log_unexpected THEN
309 gme_debug.put_line('When others exception in get Mtl detail Reservation');
310 END IF;
311 fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','Get_Material_Detail_Reservations');
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 x_error_msg := fnd_message.get;
314
315 END Get_Material_Dtl_Reservations;
316
317
318 /*+========================================================================+
319 | PROCEDURE NAME
320 | Get_Available_Qties
321 |
322 | USAGE
323 |
324 | ARGUMENTS
325 |
326 | RETURNS
327 |
328 | HISTORY
329 | Created 26-Apr-05 Eddie Oumerretane
330 |
331 +========================================================================+*/
332 PROCEDURE Get_Available_Qties (p_organization_id IN NUMBER,
333 p_inventory_item_id IN NUMBER,
334 p_revision IN VARCHAR2,
335 p_subinventory_code IN VARCHAR2,
336 p_locator_id IN NUMBER,
337 p_lot_number IN VARCHAR2,
338 p_revision_control IN VARCHAR2,
339 p_lot_control IN VARCHAR2,
340 p_tree_mode IN VARCHAR2,
341 x_att_qty OUT NOCOPY NUMBER,
342 x_sec_att_qty OUT NOCOPY NUMBER,
343 x_atr_qty OUT NOCOPY NUMBER,
344 x_sec_atr_qty OUT NOCOPY NUMBER)
345 IS
346
347 l_is_revision_control BOOLEAN := FALSE;
348 l_is_lot_control BOOLEAN := FALSE;
349
350 l_return_status VARCHAR2(1);
351 l_msg_count NUMBER(10);
352 l_msg_data VARCHAR2(1000);
353 l_qoh NUMBER;
354 l_rqoh NUMBER;
355 l_qr NUMBER;
356 l_qs NUMBER;
357 l_att NUMBER;
358 l_atr NUMBER;
359 l_sqoh NUMBER;
360 l_srqoh NUMBER;
361 l_sqr NUMBER;
362 l_sqs NUMBER;
363 l_satt NUMBER;
364
365 l_locator_id number;
366 l_cost_group_id number;
367
368 BEGIN
369
370
371 -- Clearing the quantity cache
372 inv_quantity_tree_pub.clear_quantity_cache;
373
374
375 if upper(p_revision_control) = 'TRUE' then
376 l_is_revision_control := TRUE;
377 end if;
378 if upper(p_lot_control) = 'TRUE' then
379 l_is_lot_control := TRUE;
380 end if;
381
382 if p_locator_id <= 0 then
383 l_locator_id := null;
384 else
385 l_locator_id := p_locator_id;
386 end if;
387
388 Inv_Quantity_Tree_Pub.Query_Quantities (
389 p_api_version_number => 1.0,
390 p_init_msg_lst => fnd_api.g_false,
391 x_return_status => l_return_status,
392 x_msg_count => l_msg_count,
393 x_msg_data => l_msg_data,
394 p_organization_id => p_organization_id,
395 p_inventory_item_id => p_inventory_item_id,
396 p_tree_mode => p_tree_mode,
397 p_is_revision_control => l_is_revision_control,
398 p_is_lot_control => l_is_lot_control,
399 p_is_serial_control => FALSE,
400 p_grade_code => NULL,
401 p_revision => p_revision,
402 p_lot_number => p_lot_number,
403 p_subinventory_code => p_subinventory_code,
404 p_locator_id => l_locator_id,
405 p_cost_group_id => NULL,
406 x_qoh => l_qoh,
407 x_rqoh => l_rqoh,
408 x_qr => l_qr,
409 x_qs => l_qs,
410 x_att => x_att_qty,
411 x_atr => x_atr_qty,
412 x_sqoh => l_sqoh,
413 x_srqoh => l_srqoh,
414 x_sqr => l_sqr,
415 x_sqs => l_sqs,
416 x_satt => x_sec_att_qty,
417 x_satr => x_sec_atr_qty);
418
419
420 EXCEPTION
421 WHEN OTHERS THEN
422 IF g_debug <= gme_debug.g_log_unexpected THEN
423 gme_debug.put_line('When others exception in get available qties');
424 END IF;
425 fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','get_available_qties');
426
427 END Get_Available_Qties;
428
429 /*+========================================================================+
430 | PROCEDURE NAME
431 | Update_Qty_Tree_For_Rsrv
432 |
433 | USAGE
434 |
435 | ARGUMENTS
436 |
437 | RETURNS
438 |
439 | HISTORY
440 | Created 26-Apr-05 Eddie Oumerretane
441 |
442 +========================================================================+*/
443 PROCEDURE Update_Qty_Tree_For_Rsrv (p_organization_id IN NUMBER,
444 p_batch_id IN NUMBER,
445 p_material_detail_id IN NUMBER,
446 p_inventory_item_id IN NUMBER,
447 p_revision IN VARCHAR2,
448 p_subinventory_code IN VARCHAR2,
449 p_locator_id IN NUMBER,
450 p_lot_number IN VARCHAR2,
451 p_revision_control IN VARCHAR2,
452 p_lot_control IN VARCHAR2,
453 p_primary_qty IN NUMBER,
454 p_secondary_qty IN NUMBER,
455 x_tree_id OUT NOCOPY NUMBER,
456 x_atr OUT NOCOPY NUMBER,
457 x_satr OUT NOCOPY NUMBER,
458 x_return_status OUT NOCOPY VARCHAR2,
459 x_error_msg OUT NOCOPY VARCHAR2) IS
460
461 l_is_revision_control BOOLEAN := FALSE;
462 l_is_lot_control BOOLEAN := FALSE;
463
464 l_primary_qty NUMBER;
465 l_secondary_qty NUMBER;
466 l_msg_count NUMBER(10);
467 l_qoh NUMBER;
468 l_rqoh NUMBER;
469 l_qr NUMBER;
470 l_qs NUMBER;
471 l_att NUMBER;
472 l_sqoh NUMBER;
473 l_srqoh NUMBER;
474 l_sqr NUMBER;
475 l_sqs NUMBER;
476 l_satt NUMBER;
477 l_locator_id NUMBER;
478 CREATE_TREE_ERROR EXCEPTION;
479
480 BEGIN
481
482
483 IF (g_debug IS NOT NULL) THEN
484 gme_debug.log_initialize ('MobileCreQtyTree');
485 END IF;
486
487 x_return_status := FND_API.G_RET_STS_SUCCESS;
488 x_error_msg := ' ';
489
490 IF upper(p_revision_control) = 'TRUE' THEN
491 l_is_revision_control := TRUE;
492 END IF;
493
494 IF upper(p_lot_control) = 'TRUE' THEN
495 l_is_lot_control := TRUE;
496 END IF;
497
498 IF p_locator_id <= 0 THEN
499 l_locator_id := null;
500 ELSE
501 l_locator_id := p_locator_id;
502 END IF;
503
504 l_primary_qty := p_primary_qty * -1;
505 l_secondary_qty := p_secondary_qty * -1;
506
507 -- Clearing the quantity cache
508 INV_Quantity_Tree_Pub.Clear_Quantity_Cache;
509
510
511 INV_Quantity_Tree_Grp.Create_Tree
512 (
513 p_api_version_number => 1.0
514 , p_init_msg_lst => 'T'
515 , x_return_status => x_return_status
516 , x_msg_count => l_msg_count
517 , x_msg_data => x_error_msg
518 , p_organization_id => p_organization_id
519 , p_inventory_item_id => p_inventory_item_id
520 , p_tree_mode => 1
521 , p_is_revision_control => l_is_revision_control
522 , p_is_lot_control => l_is_lot_control
523 , p_is_serial_control => FALSE
524 , p_grade_code => NULL
525 , p_demand_source_type_id => gme_common_pvt.g_txn_source_type
526 , p_demand_source_header_id => p_batch_id
527 , p_demand_source_line_id => p_material_detail_id
528 , p_demand_source_name => NULL
529 , p_lot_expiration_date => SYSDATE
530 , x_tree_id => x_tree_id
531 );
532
533
534 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
535 RAISE CREATE_TREE_ERROR;
536 END IF;
537
538 IF (l_primary_qty <> 0) THEN
539
540 INV_Quantity_Tree_Grp.Update_Quantities(
541 p_api_version_number => 1.0,
542 p_init_msg_lst => 'T',
543 x_return_status => x_return_status,
544 x_msg_count => l_msg_count,
545 x_msg_data => x_error_msg,
546 p_tree_id => x_tree_id,
547 p_revision => p_revision,
548 p_lot_number => p_lot_number,
549 p_subinventory_code => p_subinventory_code,
550 p_locator_id => p_locator_id,
551 p_primary_quantity => l_primary_qty,
552 p_quantity_type => 3, --inv_quantity_tree_pvt.g_qr_other_demand
553 p_secondary_quantity => l_secondary_qty,
554 x_qoh => l_qoh,
555 x_rqoh => l_rqoh,
556 x_qr => l_qr,
557 x_qs => l_qs,
558 x_att => l_att,
559 x_atr => x_atr,
560 x_sqoh => l_sqoh,
561 x_srqoh => l_srqoh,
562 x_sqr => l_sqr,
563 x_sqs => l_sqs,
564 x_satt => l_satt,
565 x_satr => x_satr,
566 p_containerized => 0,
567 p_lpn_id => NULL);
568
569 ELSE
570
571 INV_Quantity_Tree_Grp.Query_Tree(
572 p_api_version_number => 1
573 , p_init_msg_lst => NULL
574 , x_return_status => x_return_status
575 , x_msg_count => l_msg_count
576 , x_msg_data => x_error_msg
577 , p_tree_id => x_tree_id
578 , p_revision => p_revision
579 , p_lot_number => p_lot_number
580 , p_subinventory_code => p_subinventory_code
581 , p_locator_id => p_locator_id
582 , p_lpn_id => NULL
583 , x_qoh => l_qoh
584 , x_rqoh => l_rqoh
585 , x_qr => l_qr
586 , x_qs => l_qs
587 , x_att => l_att
588 , x_atr => x_atr
589 , x_sqoh => l_sqoh
590 , x_srqoh => l_srqoh
591 , x_sqr => l_sqr
592 , x_sqs => l_sqs
593 , x_satt => l_satt
594 , x_satr => x_satr
595 );
596
597 END IF;
598
599 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
600 RAISE CREATE_TREE_ERROR;
601 END IF;
602
603 INV_Quantity_Tree_Pvt.Prepare_Reservation_Quantities(
604 x_return_status => x_return_status
605 , p_tree_id => x_tree_id);
606
607 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
608 fnd_message.set_name('INV', 'ERR_PREPARE_RSV_QTY');
609 x_error_msg := fnd_message.get;
610 END IF;
611
612
613 EXCEPTION
614 WHEN CREATE_TREE_ERROR THEN
615 IF g_debug <= gme_debug.g_log_unexpected THEN
616 gme_debug.put_line('Create Qty Tree exception in Update Qty Tree');
617 END IF;
618
619 WHEN OTHERS THEN
620 IF g_debug <= gme_debug.g_log_unexpected THEN
621 gme_debug.put_line('When others exception in Update Qty Tree');
622 END IF;
623 fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','Update_Qty_Tree_For_Rsrv');
624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
625 x_error_msg := fnd_message.get;
626
627 END Update_Qty_Tree_For_Rsrv;
628
629 /*+========================================================================+
630 | PROCEDURE NAME
631 | Fetch_Atr_Qty
632 |
633 | USAGE
634 |
635 | ARGUMENTS
636 |
637 | RETURNS
638 |
639 | HISTORY
640 | Created 26-Apr-05 Eddie Oumerretane
641 |
642 +========================================================================+*/
643 PROCEDURE Fetch_Atr_Qty (p_revision IN VARCHAR2,
644 p_subinventory_code IN VARCHAR2,
645 p_locator_id IN NUMBER,
646 p_lot_number IN VARCHAR2,
647 p_revision_control IN VARCHAR2,
648 p_lot_control IN VARCHAR2,
649 p_tree_id IN NUMBER,
650 x_atr OUT NOCOPY NUMBER,
651 x_satr OUT NOCOPY NUMBER,
652 x_return_status OUT NOCOPY VARCHAR2,
653 x_error_msg OUT NOCOPY VARCHAR2) IS
654
655 l_msg_count NUMBER(10);
656 l_qoh NUMBER;
657 l_rqoh NUMBER;
658 l_qr NUMBER;
659 l_qs NUMBER;
660 l_att NUMBER;
661 l_sqoh NUMBER;
662 l_srqoh NUMBER;
663 l_sqr NUMBER;
664 l_sqs NUMBER;
665 l_satt NUMBER;
666 QUERY_TREE_ERROR EXCEPTION;
667
668 BEGIN
669
670 IF (g_debug IS NOT NULL) THEN
671 gme_debug.log_initialize ('MobileFetchAtrQty');
672 END IF;
673
674 x_return_status := FND_API.G_RET_STS_SUCCESS;
675 x_error_msg := ' ';
676
677 INV_Quantity_Tree_Grp.Query_Tree(
678 p_api_version_number => 1
679 , p_init_msg_lst => NULL
680 , x_return_status => x_return_status
681 , x_msg_count => l_msg_count
682 , x_msg_data => x_error_msg
683 , p_tree_id => p_tree_id
684 , p_revision => p_revision
685 , p_lot_number => p_lot_number
686 , p_subinventory_code => p_subinventory_code
687 , p_locator_id => p_locator_id
688 , p_lpn_id => NULL
689 , x_qoh => l_qoh
690 , x_rqoh => l_rqoh
691 , x_qr => l_qr
692 , x_qs => l_qs
693 , x_att => l_att
694 , x_atr => x_atr
695 , x_sqoh => l_sqoh
696 , x_srqoh => l_srqoh
697 , x_sqr => l_sqr
698 , x_sqs => l_sqs
699 , x_satt => l_satt
700 , x_satr => x_satr
701 );
702
703 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
704 RAISE QUERY_TREE_ERROR;
705 END IF;
706
707 EXCEPTION
708 WHEN QUERY_TREE_ERROR THEN
709 IF g_debug <= gme_debug.g_log_unexpected THEN
710 gme_debug.put_line('Query Qty Tree exception in Fetch ATR Qty');
711 END IF;
712
713 WHEN OTHERS THEN
714 IF g_debug <= gme_debug.g_log_unexpected THEN
715 gme_debug.put_line('When others exception in Fetch_Atr_Qty');
716 END IF;
717 fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','fetch_Atr_Qty');
718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
719 x_error_msg := fnd_message.get;
720
721 END Fetch_Atr_Qty;
722
723 /*+========================================================================+
724 | PROCEDURE NAME
725 | Check_UoM_Conv_Deviation
726 |
727 | USAGE
728 |
729 | ARGUMENTS
730 |
731 | RETURNS
732 |
733 | HISTORY
734 | Created 26-Apr-05 Eddie Oumerretane
735 |
736 +========================================================================+*/
737 PROCEDURE Check_UoM_Conv_Deviation(
738 p_organization_id IN NUMBER
739 , p_inventory_item_id IN NUMBER
740 , p_lot_number IN VARCHAR2
741 , p_primary_quantity IN NUMBER
742 , p_primary_uom_code IN VARCHAR2
743 , p_secondary_quantity IN NUMBER
744 , p_secondary_uom_code IN VARCHAR2
745 , x_return_status OUT NOCOPY VARCHAR2
746 , x_error_msg OUT NOCOPY VARCHAR2)
747 IS
748
749 l_is_valid NUMBER(1);
750 l_msg_index_out NUMBER;
751
752 BEGIN
753
754 x_return_status := FND_API.G_RET_STS_SUCCESS;
755 x_error_msg := '';
756
757 -- Validate the quantities within deviation
758 l_is_valid := INV_CONVERT.within_deviation(
759 p_organization_id => p_organization_id
760 , p_inventory_item_id => p_inventory_item_id
761 , p_lot_number => p_lot_number
762 , p_precision => 5
763 , p_quantity => ABS(p_primary_quantity)
764 , p_uom_code1 => p_primary_uom_code
765 , p_quantity2 => ABS(p_secondary_quantity)
766 , p_uom_code2 => p_secondary_uom_code);
767 IF (l_is_valid = 0)
768 THEN
769
770 x_return_status := FND_API.G_RET_STS_ERROR;
771
772 FND_MSG_PUB.Get(
773 p_msg_index => 1,
774 p_data => x_error_msg,
775 p_encoded => FND_API.G_FALSE,
776 p_msg_index_out => l_msg_index_out);
777 END IF;
778
779 END Check_UoM_Conv_Deviation;
780
781 /*+========================================================================+
782 | PROCEDURE NAME
783 | Get_Stacked_Message
784 |
785 | USAGE
786 |
787 | ARGUMENTS
788 |
789 | RETURNS
790 |
791 | HISTORY
792 | Created 26-Apr-05 Eddie Oumerretane
793 |
794 +========================================================================+*/
795 PROCEDURE Get_Stacked_Messages(x_message OUT NOCOPY VARCHAR2)
796 IS
797 l_message VARCHAR2(2000);
798 l_msg_count NUMBER;
799 BEGIN
800
801 fnd_msg_pub.Count_And_Get
802 (p_encoded => FND_API.g_false,
803 p_count => l_msg_count,
804 p_data => l_message
805 );
806
807
808 fnd_msg_pub.delete_msg;
809
810 x_message := l_message;
811
812 EXCEPTION
813 WHEN OTHERS THEN
814 NULL;
815
816 END Get_Stacked_Messages;
817
818 /*+========================================================================+
819 | PROCEDURE NAME
820 | Create_Reservation
821 |
822 | USAGE
823 |
824 | ARGUMENTS
825 |
826 | RETURNS
827 |
828 | HISTORY
829 | Created 26-Apr-05 Eddie Oumerretane
830 | 23-Jun-06 Shrikant Nene Bug 5263908 |
831 |
832 +========================================================================+*/
833 PROCEDURE Create_Reservation(p_organization_id IN NUMBER,
834 p_batch_id IN NUMBER,
835 p_material_detail_id IN NUMBER,
836 p_item_id IN NUMBER,
837 p_revision IN VARCHAR2,
838 p_subinventory_code IN VARCHAR2,
839 p_locator_id IN NUMBER,
840 p_lot_number IN VARCHAR2,
841 p_reserved_qty IN NUMBER,
842 p_reserved_uom_code IN VARCHAR2,
843 p_sec_reserved_qty IN NUMBER,
844 p_sec_reserved_uom_code IN VARCHAR2,
845 p_requirement_date IN DATE,
846 x_return_status OUT NOCOPY VARCHAR2,
847 x_error_msg OUT NOCOPY VARCHAR2)
848 IS
849 l_matl_dtl_rec gme_material_details%ROWTYPE;
850 l_msg_count NUMBER;
851 BEGIN
852
853 x_return_status := FND_API.G_RET_STS_SUCCESS;
854 x_error_msg := ' ';
855
856 IF (g_debug IS NOT NULL) THEN
857 gme_debug.log_initialize ('MobileCreRsrv');
858 END IF;
859
860 l_matl_dtl_rec.material_detail_id := p_material_detail_id;
861 IF NOT gme_material_details_dbl.fetch_row (l_matl_dtl_rec
862 ,l_matl_dtl_rec) THEN
863 RAISE fnd_api.g_exc_error;
864 END IF;
865
866 l_matl_dtl_rec.material_requirement_date := p_requirement_date;
867 l_matl_dtl_rec.organization_id := p_organization_id;
868 l_matl_dtl_rec.inventory_item_id := p_item_id;
869 l_matl_dtl_rec.batch_id := p_batch_id;
870 l_matl_dtl_rec.revision := p_revision;
871
872 GME_RESERVATIONS_PVT.Create_Material_Reservation(
873 p_matl_dtl_rec => l_matl_dtl_rec,
874 p_resv_qty => p_reserved_qty,
875 p_sec_resv_qty => p_sec_reserved_qty,
876 p_resv_um => p_reserved_uom_code,
877 p_subinventory => p_subinventory_code,
878 p_locator_id => p_locator_id,
879 p_lot_number => p_lot_number,
880 x_return_status => x_return_status);
881
882 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
883 Get_Stacked_Messages(x_message => x_error_msg);
884 /* Bug 5438355: Added success message. */
885 ELSE
886 gme_common_pvt.log_message('GME_RESERVATION_CREATED');
887 gme_common_pvt.count_and_get (x_count => l_msg_count
888 ,p_encoded => fnd_api.g_false
889 ,x_data => x_error_msg);
890 COMMIT;
891 END IF;
892
893 EXCEPTION
894 WHEN FND_API.G_EXC_ERROR THEN
895 x_return_status := fnd_api.g_ret_sts_error;
896 gme_common_pvt.count_and_get (x_count => l_msg_count
897 ,p_encoded => fnd_api.g_false
898 ,x_data => x_error_msg);
899 WHEN OTHERS THEN
900 IF g_debug <= gme_debug.g_log_unexpected THEN
901 gme_debug.put_line('When others exception in Create Reservation');
902 END IF;
903 fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','create_reservation');
904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
905 x_error_msg := fnd_message.get;
906
907 END Create_Reservation;
908
909 /*+========================================================================+
910 | PROCEDURE NAME
911 | Update_Reservation
912 |
913 | USAGE
914 |
915 | ARGUMENTS
916 |
917 | RETURNS
918 |
919 | HISTORY
920 | Created 26-Apr-05 Eddie Oumerretane
921 |
922 +========================================================================+*/
923 PROCEDURE Update_Reservation(p_reservation_id IN NUMBER,
924 p_revision IN VARCHAR2,
925 p_subinventory_code IN VARCHAR2,
926 p_locator_id IN NUMBER,
927 p_lot_number IN VARCHAR2,
928 p_reserved_qty IN NUMBER,
929 p_reserved_uom_code IN VARCHAR2,
930 p_sec_reserved_qty IN NUMBER,
931 p_requirement_date IN DATE,
932 x_return_status OUT NOCOPY VARCHAR2,
933 x_error_msg OUT NOCOPY VARCHAR2)
934
935 IS
936 BEGIN
937
938 x_return_status := FND_API.G_RET_STS_SUCCESS;
939 x_error_msg := ' ';
940
941 IF (g_debug IS NOT NULL) THEN
942 gme_debug.log_initialize ('MobileUpdRsrv');
943 gme_debug.put_line('Reservation id = '||p_reservation_id);
944 gme_debug.put_line('Revision = '||p_revision);
945 gme_debug.put_line('Sub inventory = '||p_subinventory_code);
946 gme_debug.put_line('Locator Id = '||p_locator_id);
947 gme_debug.put_line('Lot = '||p_lot_number);
948 gme_debug.put_line('Reserved Qty = '||p_reserved_qty);
949 gme_debug.put_line('Sec Reserved Qty = '||p_sec_reserved_qty);
950 gme_debug.put_line('Reserved UOM = '||p_reserved_uom_code);
951 gme_debug.put_line('Date = '||p_requirement_date);
952 END IF;
953
954 -- Clearing the quantity cache
955 INV_Quantity_Tree_Pub.Clear_Quantity_Cache;
956
957 GME_RESERVATIONS_PVT.update_reservation(
958 p_reservation_id => p_reservation_id,
959 p_revision => p_revision,
960 p_subinventory => p_subinventory_code,
961 p_locator_id => p_locator_id,
962 p_lot_number => p_lot_number,
963 p_new_qty => p_reserved_qty,
964 p_new_sec_qty => p_sec_reserved_qty,
965 p_new_uom => p_reserved_uom_code,
966 p_new_date => p_requirement_date,
967 x_return_status => x_return_status);
968
969 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
970 Get_Stacked_Messages(x_message => x_error_msg);
971 /* Bug 5438355: Added success message. */
972 ELSE
973 gme_common_pvt.log_message('GME_RESERVATION_UPDATED');
974 Get_Stacked_Messages(x_message => x_error_msg);
975 COMMIT;
976 END IF;
977
978 EXCEPTION
979 WHEN OTHERS THEN
980 IF g_debug <= gme_debug.g_log_unexpected THEN
981 gme_debug.put_line('When others exception in Update Reservation');
982 END IF;
983 fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','update_reservation');
984 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
985 x_error_msg := fnd_message.get;
986
987 END Update_Reservation;
988
989
990 END GME_MOBILE_RSRV;