1 PACKAGE BODY GML_ValidateDerive_GRP AS
2 /* $Header: GMLGVSQB.pls 120.0 2005/05/25 16:52:09 appldev noship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation
5 | Redwood Shores, CA, USA
6 | All rights reserved.
7 +==========================================================================+
8 | FILE NAME
9 | GMLGVSQB.pls
10 |
11 | PACKAGE NAME
12 | GML_ValidateDerive_GRP
13 | TYPE
14 | Group
15 |
16 | DESCRIPTION
17 | This package contains the group API for Change PO API and ROIO
18 | validations for
19 | Process Purchase Orders
20 |
21 | CONTENTS
22 | Secondary_Qty
23 |
24 | HISTORY
25 | Created - Preetam Bamb
26 +==========================================================================+
27 */
28
29 /* Global variables */
30 -- Read the profile option that enables/disables the debug log
31 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
32
33 --yannamal bug4189249 Feb 17 2005
34 --Getting the Debug Log Level to check the level before logging
35 g_fnd_debug_level NUMBER := NVL(FND_PROFILE.VALUE('AFLOG_LEVEL'),0);
36
37 G_PKG_NAME CONSTANT VARCHAR2(30) :='GML_ValidateDerive_GRP';
38 g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
39
40 /* +==========================================================================+
41 | PROCEDURE NAME
42 | Secondary_Qty
43 |
44 | TYPE
45 | Group
46 |
47 | USAGE
48 | Validates Secondary Quantity passed by the Change PO API or receiving
49 | transaction pre-processor from PO.If validation indicator is 'N' then
50 | directly convert the Secondary Qty and return. If validation indicator
51 | is 'Y' then other sources then check the deviation and return errors
52 | if secondary quantity passed is out of deviation else compute the
53 | Secondary Qty and return.
54 | In both the cases check if the Secondary Qty and Secondary Unit of
55 | Measure if not null then only do these validations and conversion
56 | RETURNS
57 | Via x_ OUT parameters
58 |
59 | HISTORY
60 | Created Preetam Bamb
61 +==========================================================================+ */
62 PROCEDURE Secondary_Qty
63 ( p_api_version IN NUMBER
64 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
65 , p_validate_ind IN VARCHAR2
66 , p_item_no IN VARCHAR2
67 , p_unit_of_measure IN VARCHAR2
68 , p_quantity IN NUMBER
69 , p_lot_id IN NUMBER DEFAULT 0
70 , p_secondary_unit_of_measure IN OUT NOCOPY VARCHAR2
71 , p_secondary_quantity IN OUT NOCOPY NUMBER
72 , x_return_status OUT NOCOPY VARCHAR2
73 , x_msg_count OUT NOCOPY NUMBER
74 , x_msg_data OUT NOCOPY VARCHAR2
75 )
76
77 IS
78
79 l_api_name CONSTANT VARCHAR2(30) := 'Secondary_Qty' ;
80 l_api_version CONSTANT NUMBER := 1.0 ;
81 l_progress VARCHAR2(3) := '000';
82
83 l_error_message VARCHAR2(2000);
84 l_opm_um_code VARCHAR2(25);
85 l_passed_opm_sec_um_code VARCHAR2(25);
86 l_opm_item_id NUMBER;
87 l_opm_dualum_ind NUMBER;
88 l_opm_secondary_um VARCHAR2(25);
89
90 v_ret_val NUMBER;
91
92 Cursor Cr_get_opm_attr IS
93 Select ilm.item_id,
94 ilm.dualum_ind,
95 ilm.item_um2
96 From ic_item_mst ilm
97 Where ilm.item_no = p_item_no;
98
99 BEGIN
100
101 --yannamal bug4189249 Feb 17 2005 Added check to debug level before logging
102 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
103 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name,
104 'Entering ' || l_api_name );
105 END IF;
106
107 IF FND_API.to_boolean(p_init_msg_list) THEN
108 FND_MSG_PUB.Initialize;
109 END IF;
110
111 -- Standard call to check for call compatibility.
112 IF NOT FND_API.Compatible_API_Call ( l_api_version,
113 p_api_version,
114 l_api_name ,
115 G_PKG_NAME
116 ) THEN
117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
118 END IF;
119
120 x_return_status :=FND_API.G_RET_STS_SUCCESS;
121
122 -- If validation indicator is 'N' that means its coming from Change PO API.
123 -- If the secondary attributes are NULL then return successful
124 IF (p_secondary_quantity IS NULL AND p_secondary_unit_of_measure IS NULL and p_validate_ind = 'N')
125 OR p_item_no IS NULL --OR p_unit_of_measure IS NULL OR p_quantity IS NULL
126 THEN
127 RETURN;
128 ELSE
129 l_progress := '001';
130 --Get opm attributes for the item.
131 Open Cr_get_opm_attr;
132 Fetch Cr_get_opm_attr Into l_opm_item_id, l_opm_dualum_ind, l_opm_secondary_um;
133 --do not need p_item_no IS NULL but does'nt matter to keep it.
134 IF (Cr_get_opm_attr%NOTFOUND) OR p_item_no IS NULL THEN
135 --item not an opm item do nothing just return
136 CLOSE Cr_get_opm_attr;
137 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');
138 FND_MESSAGE.SET_TOKEN('ITEM',p_item_no);
139 FND_MSG_PUB.ADD;
140 RAISE FND_API.G_EXC_ERROR;
141 END IF;
142 CLOSE Cr_get_opm_attr;
143 l_progress := '002';
144 --if item is not dualum control then return doing nothing.
145 IF l_opm_dualum_ind = 0 THEN
146 RETURN;
147 END IF;
148 l_progress := '003';
149 --Get opm uom code for the passed apps unit of measure.
150 IF p_unit_of_measure IS NOT NULL THEN
151 BEGIN
152 l_opm_um_code := po_gml_db_common.get_opm_uom_code(p_unit_of_measure);
153
154 EXCEPTION WHEN OTHERS THEN
155 --If unit of measure is not passed the error out.
156 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
157 FND_MESSAGE.SET_TOKEN('UOM','NULL');
158 FND_MESSAGE.SET_TOKEN('ITEM',p_item_no);
159 FND_MSG_PUB.ADD;
160 RAISE FND_API.G_EXC_ERROR;
161 END;
162 ELSE
163 --If unit of measure is not passed the error out.
164 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
165 FND_MESSAGE.SET_TOKEN('UOM','NULL');
166 FND_MESSAGE.SET_TOKEN('ITEM',p_item_no);
167 FND_MSG_PUB.ADD;
168 RAISE FND_API.G_EXC_ERROR;
169 END IF;
170 l_progress := '004';
171 --Validate secondary unit of measure passed in case validate ind is 'Y' (receiving preprocessor)
172 IF p_secondary_unit_of_measure is NOT NULL and p_validate_ind = 'Y' THEN
173 l_passed_opm_sec_um_code := po_gml_db_common.get_opm_uom_code(p_secondary_unit_of_measure);
174
175 -- bug# 3442888. If items secondary unit of measure is different than ROI secondary unit of
176 -- measure then error message is not put on the stack since fnd_msg_pub.add was missing.
177 -- adding fnd_msg_pub.add
178 IF l_opm_secondary_um <> l_passed_opm_sec_um_code THEN
179 --return error cause the secondary unit of measure does not match the items definition
180 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
181 FND_MESSAGE.SET_TOKEN('UOM',p_secondary_unit_of_measure);
182 FND_MESSAGE.SET_TOKEN('ITEM',p_item_no);
183 FND_MSG_PUB.ADD;
184 RAISE FND_API.G_EXC_ERROR;
185 END IF;
186 ELSIF p_secondary_unit_of_measure is NULL and p_validate_ind = 'Y' THEN
187 --in case validate ind is 'Y' then derive the secondary unit of measure.
188 --return the secondary unit of measure
189 P_secondary_unit_of_measure := po_gml_db_common.get_apps_uom_code(l_opm_secondary_um);
190 END IF;
191 l_progress := '005';
192 IF l_opm_um_code is NOT NULL and l_opm_secondary_um IS NOT NULL THEN
193 IF p_validate_ind = 'N' or p_secondary_quantity IS NULL THEN
194 gmicuom.icuomcv ( l_opm_item_id,
195 p_lot_id,
196 p_quantity,
197 l_opm_um_code,
198 l_opm_secondary_um,
199 p_secondary_quantity );
200
201 ELSE
202 IF l_opm_dualum_ind = 1 THEN
203 gmicuom.icuomcv ( l_opm_item_id,
204 p_lot_id,
205 p_quantity,
206 l_opm_um_code,
207 l_opm_secondary_um,
208 p_secondary_quantity );
209
210 ELSIF l_opm_dualum_ind in (2,3) and nvl(p_secondary_quantity,0) > 0 THEN
211 v_ret_val := gmicval.dev_validation ( l_opm_item_id,
212 p_lot_id,
213 p_quantity,
214 l_opm_um_code,
215 p_secondary_quantity,
216 l_opm_secondary_um,
217 0 );
218
219 IF v_ret_val in (-68) THEN
220 FND_MESSAGE.SET_NAME( 'GMI','IC_DEVIATION_HI_ERR');
221 FND_MSG_PUB.Add;
222 RAISE FND_API.G_EXC_ERROR;
223 ELSIF v_ret_val in (-69) THEN
224 FND_MESSAGE.SET_NAME( 'GMI','IC_DEVIATION_LOW_ERR');
225 FND_MSG_PUB.Add;
226 RAISE FND_API.G_EXC_ERROR;
227 ELSIF ( v_ret_val < 0 ) THEN
228 FND_MESSAGE.SET_NAME( 'GMI','CONV_ERROR');
229 FND_MSG_PUB.Add;
230 RAISE FND_API.G_EXC_ERROR;
231 END IF;
232 END IF; /*l_opm_dualum_ind = 1 */
233 END IF;/*p_validate_ind = 'N' or p_secondary_quantity IS NULL */
234 END IF; /*l_opm_um_code is NOT NULL and l_opm_secondary_um IS NOT NULL */
235 l_progress := '006';
236 END IF; /*(p_secondary_quantity IS NULL AND p_.....*/
237
238 FND_MSG_PUB.Count_AND_GET(p_count=>x_msg_count, p_data=>x_msg_data);
239
240 --yannamal bug4189249 Feb 17 2005 Added check to debug level before logging
241 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
242 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name,
243 'Exiting ' || l_api_name );
244 END IF;
245
246 EXCEPTION
247 WHEN FND_API.G_EXC_ERROR THEN
248 x_return_status := FND_API.G_RET_STS_ERROR;
249 FND_MSG_PUB.Count_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 FND_MSG_PUB.Count_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
253 WHEN OTHERS THEN
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 FND_MSG_PUB.Count_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
256 --yannamal bug4189249 Feb 17 2005 Added check to debug level before logging
257 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
258 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED,
259 g_module_prefix || l_api_name || '.' || l_progress, x_msg_data);
260 END IF;
261
262 END Secondary_Qty;
263
264 END GML_ValidateDerive_GRP;
[Home] [Help]