1 PACKAGE GME_MOBILE_RSRV AS
2 /* $Header: GMEMORSS.pls 120.2.12000000.2 2007/02/06 13:43:23 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 | |
18 +===========================================================================*/
19
20 TYPE t_genref IS REF CURSOR;
21
22 PROCEDURE Get_Material_Reservations(p_organization_id IN NUMBER,
23 p_batch_id IN NUMBER,
24 p_material_detail_id IN NUMBER,
25 p_subinventory_code IN VARCHAR2,
26 p_locator_id IN NUMBER,
27 p_lot_number IN VARCHAR2,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_error_msg OUT NOCOPY VARCHAR2,
30 x_rsrv_cursor OUT NOCOPY t_genref);
31
32 /* Bug#5663458
33 * Created the following procedure
34 */
35 PROCEDURE Get_Material_Dtl_Reservations(p_organization_id IN NUMBER,
36 p_batch_id IN NUMBER,
37 p_material_detail_id IN NUMBER,
38 p_eff_loccontrol IN NUMBER,
39 p_lotcontrol IN NUMBER,
40 p_revcontrol IN NUMBER,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_error_msg OUT NOCOPY VARCHAR2,
43 x_rsrv_cursor OUT NOCOPY t_genref);
44
45
46 PROCEDURE Check_Rsrv_Exist(p_organization_id IN NUMBER,
47 p_batch_id IN NUMBER,
48 p_material_detail_id IN NUMBER,
49 p_subinventory_code IN VARCHAR2,
50 p_locator_id IN NUMBER,
51 p_lot_number IN VARCHAR2,
52 p_exclude_res_id IN NUMBER,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_error_msg OUT NOCOPY VARCHAR2,
55 x_rsrv_cursor OUT NOCOPY t_genref);
56
57 PROCEDURE Get_Stacked_Messages(x_message OUT NOCOPY VARCHAR2);
58
59 PROCEDURE Create_Reservation(p_organization_id IN NUMBER,
60 p_batch_id IN NUMBER,
61 p_material_detail_id IN NUMBER,
62 p_item_id IN NUMBER,
63 p_revision IN VARCHAR2,
64 p_subinventory_code IN VARCHAR2,
65 p_locator_id IN NUMBER,
66 p_lot_number IN VARCHAR2,
67 p_reserved_qty IN NUMBER,
68 p_reserved_uom_code IN VARCHAR2,
69 p_sec_reserved_qty IN NUMBER,
70 p_sec_reserved_uom_code IN VARCHAR2,
71 p_requirement_date IN DATE,
72 x_return_status OUT NOCOPY VARCHAR2,
73 x_error_msg OUT NOCOPY VARCHAR2);
74
75 PROCEDURE Update_Reservation(p_reservation_id IN NUMBER,
76 p_revision IN VARCHAR2,
77 p_subinventory_code IN VARCHAR2,
78 p_locator_id IN NUMBER,
79 p_lot_number IN VARCHAR2,
80 p_reserved_qty IN NUMBER,
81 p_reserved_uom_code IN VARCHAR2,
82 p_sec_reserved_qty IN NUMBER,
83 p_requirement_date IN DATE,
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_error_msg OUT NOCOPY VARCHAR2);
86
87
88
89 PROCEDURE Get_Available_Qties (p_organization_id IN NUMBER,
90 p_inventory_item_id IN NUMBER,
91 p_revision IN VARCHAR2,
92 p_subinventory_code IN VARCHAR2,
93 p_locator_id IN NUMBER,
94 p_lot_number IN VARCHAR2,
95 p_revision_control IN VARCHAR2,
96 p_lot_control IN VARCHAR2,
97 p_tree_mode IN VARCHAR2,
98 x_att_qty OUT NOCOPY NUMBER,
99 x_sec_att_qty OUT NOCOPY NUMBER,
100 x_atr_qty OUT NOCOPY NUMBER,
101 x_sec_atr_qty OUT NOCOPY NUMBER);
102
103 PROCEDURE Check_UoM_Conv_Deviation(
104 p_organization_id IN NUMBER
105 , p_inventory_item_id IN NUMBER
106 , p_lot_number IN VARCHAR2
107 , p_primary_quantity IN NUMBER
108 , p_primary_uom_code IN VARCHAR2
109 , p_secondary_quantity IN NUMBER
110 , p_secondary_uom_code IN VARCHAR2
111 , x_return_status OUT NOCOPY VARCHAR2
112 , x_error_msg OUT NOCOPY VARCHAR2);
113
114 PROCEDURE Update_Qty_Tree_For_Rsrv (p_organization_id IN NUMBER,
115 p_batch_id IN NUMBER,
116 p_material_detail_id IN NUMBER,
117 p_inventory_item_id IN NUMBER,
118 p_revision IN VARCHAR2,
119 p_subinventory_code IN VARCHAR2,
120 p_locator_id IN NUMBER,
121 p_lot_number IN VARCHAR2,
122 p_revision_control IN VARCHAR2,
123 p_lot_control IN VARCHAR2,
124 p_primary_qty IN NUMBER,
125 p_secondary_qty IN NUMBER,
126 x_tree_id OUT NOCOPY NUMBER,
127 x_atr OUT NOCOPY NUMBER,
128 x_satr OUT NOCOPY NUMBER,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_error_msg OUT NOCOPY VARCHAR2);
131
132 PROCEDURE Fetch_Atr_Qty (p_revision IN VARCHAR2,
133 p_subinventory_code IN VARCHAR2,
134 p_locator_id IN NUMBER,
135 p_lot_number IN VARCHAR2,
136 p_revision_control IN VARCHAR2,
137 p_lot_control IN VARCHAR2,
138 p_tree_id IN NUMBER,
139 x_atr OUT NOCOPY NUMBER,
140 x_satr OUT NOCOPY NUMBER,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_error_msg OUT NOCOPY VARCHAR2);
143
144 PROCEDURE Fetch_Lot_Reservations(p_organization_id IN NUMBER,
145 p_item_id IN NUMBER,
146 p_lot_number IN VARCHAR2,
147 x_return_status OUT NOCOPY VARCHAR2,
148 x_error_msg OUT NOCOPY VARCHAR2,
149 x_rsrv_cursor OUT NOCOPY t_genref);
150
151
152 END GME_MOBILE_RSRV;