1 PACKAGE BODY GMD_COMMON_GRP AS
2 --$Header: GMDGCOMB.pls 120.2 2006/01/02 02:13:22 svankada noship $ */
3
7 FUNCTION set_debug_flag RETURN VARCHAR2;
4 -- Global variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_COMMON_GRP';
6
8 l_debug VARCHAR2(1) := set_debug_flag;
9
10 FUNCTION set_debug_flag RETURN VARCHAR2 IS
11 l_debug VARCHAR2(1):= 'N';
12 BEGIN
13 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
14 l_debug := 'Y';
15 END IF;
16 RETURN l_debug;
17 END set_debug_flag;
18
19 -- Start of comments
20 --+=======================================================================================================+
21 --| Copyright (c) 1998 Oracle Corporation
22 --| Redwood Shores, CA, USA
23 --| All rights reserved.
24 --+========================================================================================================+
25 --| File Name : GMDGCOMB.pls
26 --| Package Name : GMD_COMMON_GRP
27 --| Type : Group
28 --|
29 --| Notes
30 --| This package contains common group layer APIs for Quality
31 --|
32 --| HISTORY
33 --| S. Feinstein 05-Jan-2005 Created.
34 --|
35 --+========================================================================================================+
36
37 --+========================================================================================================+
38 --| API Name : item_is_locator_controlled
39 --| TYPE : Group
40 --|
41 --| NOTE: Values for locator control are:
42 --| =1 None: Inventory transactions within this organization do not require locator information.
43 --| =2 Prespecified only: Inventory transactions within this organization require a valid,
44 --| predefined locator for each item.
45 --| =3 Dynamic entry allowed: Inventory transactions within this organization require a locator
46 --| for each item. You can choose a valid, predefined locator, or define a locator dynamically
47 --| at the time of transaction.
48 --| =4 Determined at subinventory level: Inventory transactions use locator control information
49 --| that you define at the subinventory level.
50 --| =5 Determined at item level: Inventory transactions use locator control information that you
51 --| define at the item level.
52 --|
53 --| HISTORY
54 --| S. Feinstein 05-Jan-2005 Created.
55 --+===========================================================================================================+
56
57 PROCEDURE item_is_locator_controlled (
58 p_organization_id IN NUMBER
59 ,p_subinventory IN VARCHAR2
60 ,p_inventory_item_id IN NUMBER
61 ,x_locator_type OUT NOCOPY NUMBER
62 ,x_return_status OUT NOCOPY VARCHAR2) IS
63
64 CURSOR Cur_subinventory(subinventory varchar2 ) IS
65 SELECT Locator_type
66 FROM mtl_secondary_inventories
67 WHERE secondary_inventory_name = subinventory
68 AND organization_id = p_organization_id;
69
70 CURSOR Cur_item (item_id number ) IS
71 SELECT location_control_code
72 FROM mtl_system_items_b_kfv
73 WHERE organization_id = p_organization_id
74 AND inventory_item_id = item_id;
75
76 CURSOR Cur_organization IS
77 SELECT stock_locator_control_code
78 FROM mtl_parameters
79 WHERE organization_id = p_organization_id ;
80
81 subinventory_ctrl NUMBER;
82 organization_ctrl NUMBER;
83 item_ctrl NUMBER;
84
85
86 BEGIN
87 IF (l_debug = 'Y') THEN
88 gmd_debug.put_line('Entered Procedure ITEM_IS_LOCATOR_CONTROLLED');
89 gmd_debug.put_line('p_organization_id : ' || p_organization_id);
90 gmd_debug.put_line('p_subinventory : ' || p_subinventory);
91 gmd_debug.put_line('p_inventory_item_id : ' || p_inventory_item_id);
92 END IF;
93
94
95 IF p_subinventory IS NULL
96 OR p_organization_id IS NULL
97 /*OR p_inventory_item_id IS NULL --bug# 4916503 */
98 THEN
99 x_locator_type := 0;
100 x_return_status := FND_API.G_RET_STS_ERROR;
101 RAISE fnd_api.g_exc_unexpected_error;
102 END IF;
103
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105
106
107 OPEN cur_subinventory( p_subinventory );
108 FETCH cur_subinventory INTO subinventory_ctrl;
109 CLOSE cur_subinventory;
110
111 gmd_debug.put_line('subinventory ctrl = '||subinventory_ctrl);
112
113 OPEN cur_item(p_inventory_item_id );
114 FETCH cur_item INTO item_ctrl;
115 CLOSE cur_item;
116 gmd_debug.put_line('item ctrl = '||item_ctrl);
117
118 --OPEN cur_organization(p_organization_id);
119 OPEN cur_organization;
120 FETCH cur_organization INTO organization_ctrl;
121 CLOSE cur_organization;
122 gmd_debug.put_line('organization ctrl = '||organization_ctrl||' org id='||p_organization_id);
123
124 If organization_ctrl in (1,2,3) then
125 x_locator_type := organization_ctrl;
126 ELSIF organization_ctrl = 4
127 AND subinventory_ctrl in (1,2,3) then
128 x_locator_type := subinventory_ctrl;
129 ELSIF subinventory_ctrl = 5
130 AND item_ctrl in (1,2,3) then
131 x_locator_type := item_ctrl;
132 ELSE
133 x_locator_type := 0;
134 x_return_status := FND_API.G_RET_STS_ERROR;
135 END IF;
136 gmd_debug.put_line('x_locator_type ='||x_locator_type);
137
138 EXCEPTION
139 WHEN FND_API.G_EXC_ERROR THEN
140 x_return_status := FND_API.G_RET_STS_ERROR ;
141 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
143 WHEN OTHERS THEN
144 x_locator_type := 0;
145 fnd_msg_pub.add_exc_msg (g_pkg_name );
146 x_return_status := FND_API.g_ret_sts_unexp_error;
147
148 END item_is_locator_controlled;
149
150
151 --+========================================================================================================+
152 --| API Name : get_organization_type
153 --| TYPE : Group
154 --|
155 --| HISTORY
156 --| S. Feinstein 05-Jan-2005 Created.
157 --+===========================================================================================================+
158 PROCEDURE Get_organization_type ( p_organization_id IN Number
159 ,x_plant OUT NOCOPY NUMBER
160 ,x_lab OUT NOCOPY NUMBER
161 ,x_return_status OUT NOCOPY VARCHAR2) IS
162
163 CURSOR Cur_get_lab_plant_ind IS
164 SELECT plant_ind, lab_ind
165 FROM gmd_parameters_hdr
166 WHERE organization_id = P_organization_id;
167
168 --gmd_parameters GMD_PARAMETERS_DTL_PKG.parameter_rec_type;
169
170 BEGIN
171
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 OPEN Cur_get_lab_plant_ind;
175 FETCH Cur_get_lab_plant_ind INTO x_plant,
176 x_lab;
177 CLOSE Cur_get_lab_plant_ind;
178
179 EXCEPTION
180
181 WHEN OTHERS THEN
182 x_return_status := FND_API.G_RET_STS_ERROR;
183 FND_MSG_PUB.ADD_EXC_MSG (g_pkg_name );
184 END get_organization_type;
185
186
187 --+========================================================================================================+
188 --| API Name : get_lot_attributes
189 --| TYPE : Group
190 --|
191 --| HISTORY
192 --| S. Feinstein 01-Jun-2005 Created.
193 --+===========================================================================================================+
194 PROCEDURE Get_lot_attributes ( p_organization_id IN NUMBER
195 ,p_inventory_item_id IN NUMBER
196 ,p_lot_number IN VARCHAR2
197 ,p_parent_lot_number IN VARCHAR2
198 ,x_lot_status_code OUT NOCOPY VARCHAR2
199 ,x_grade_code OUT NOCOPY VARCHAR2
200 ,x_return_status OUT NOCOPY VARCHAR2) IS
201
202 CURSOR Cur_get_lot_attrib IS
203 SELECT status_id,
204 grade_code
205 FROM mtl_lot_numbers
206 WHERE inventory_item_id = p_inventory_item_id
207 AND organization_id = p_organization_id
208 AND ((p_lot_number IS NULL) OR (lot_number = p_lot_number))
209 AND ((parent_lot_number = p_parent_lot_number)
210 OR (parent_lot_number IS NULL)
211 OR (p_parent_lot_number IS NULL));
212
213 p_lot_status_id NUMBER;
214
215 BEGIN
216
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218
219 OPEN Cur_get_lot_attrib;
220 FETCH Cur_get_lot_attrib INTO p_lot_status_id,
221 x_grade_code;
222 CLOSE Cur_get_lot_attrib;
223
224 IF p_lot_status_id IS NOT NULL THEN
225 SELECT status_code into x_lot_status_code
226 FROM mtl_material_statuses
227 WHERE status_id = p_lot_status_id;
228 END IF;
229
230 IF (l_debug = 'Y') THEN
231 gmd_debug.put_line('Entered Procedure get_lot_attributes');
232 gmd_debug.put_line('p_organization_id : ' || p_organization_id);
233 gmd_debug.put_line('p_lot_number : ' || p_lot_number);
234 gmd_debug.put_line('p_inventory_item_id : ' || p_inventory_item_id);
235 gmd_debug.put_line('p_lot_status_id : ' || p_lot_status_id);
236 END IF;
237
238 EXCEPTION
239
240 WHEN OTHERS THEN
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 FND_MSG_PUB.ADD_EXC_MSG (g_pkg_name );
243 END get_lot_attributes;
244
245 END GMD_COMMON_GRP;
246
[Home] [Help]