[Home] [Help]
PACKAGE BODY: APPS.GMIVLDX
Source
1 PACKAGE BODY GMIVLDX AS
2 /* $Header: GMIVLDXB.pls 120.0 2005/05/26 00:12:32 appldev noship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | GMIVLDXB.pls |
10 | |
11 | TYPE |
12 | Private |
13 | |
14 | PACKAGE NAME |
15 | GMIVLDX |
16 | |
17 | DESCRIPTION |
18 | This package contains the private APIs for |
19 | creating lots in OPM for Process / Discrete Transfer |
20 | |
21 | CONTENTS |
22 | create_lot_in_opm |
23 | verify_lot_uniqueness_in_odm |
24 | |
25 | HISTORY |
26 | Created - Jalaj Srivastava |
27 | |
28 | |
29 +==========================================================================+
30 */
31
32 PROCEDURE log_msg(p_msg_text IN VARCHAR2);
33
34 /* Global variables */
35 G_PKG_NAME CONSTANT VARCHAR2(30):='GMIVLDX';
36 G_tmp BOOLEAN := FND_MSG_PUB.Check_Msg_Level(0) ; -- temp call to initialize the
37 -- msg level threshhold gobal
38 -- variable.
39 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
40 -- to decide to log a debug msg.
41
42 /* +==========================================================================+
43 | PROCEDURE NAME |
44 | create_lot_in_opm |
45 | |
46 | TYPE |
47 | Private |
48 | |
49 | USAGE |
50 | |
51 | It will create lots in OPM if the lot does not exist. |
52 | |
53 | |
54 | RETURNS |
55 | Via x_ OUT parameters |
56 | |
57 | HISTORY |
58 | Created Jalaj Srivastava |
59 | |
60 +==========================================================================+ */
61
62 PROCEDURE create_lot_in_opm
63 ( p_api_version IN NUMBER
64 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
65 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
66 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
67 , x_return_status OUT NOCOPY VARCHAR2
68 , x_msg_count OUT NOCOPY NUMBER
69 , x_msg_data OUT NOCOPY VARCHAR2
70 , p_hdr_rec IN GMIVDX.hdr_type
71 , p_line_rec IN GMIVDX.line_type
72 , p_lot_rec IN GMIVDX.lot_type
73 , x_ic_lots_mst_row OUT NOCOPY ic_lots_mst%ROWTYPE
74 ) IS
75
76 l_api_name CONSTANT VARCHAR2(30) := 'create_lot_in_opm' ;
77 l_api_version CONSTANT NUMBER := 1.0 ;
78 l_lot_rec GMIGAPI.lot_rec_typ;
79 l_ic_lots_cpg_row ic_lots_cpg%ROWTYPE;
80
81 BEGIN
82
83 IF FND_API.to_boolean(p_init_msg_list) THEN
84 FND_MSG_PUB.Initialize;
85 END IF;
86
87 SAVEPOINT create_lot_in_opm;
88
89 -- Standard call to check for call compatibility.
90 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
91 p_api_version ,
92 l_api_name ,
93 G_PKG_NAME ) THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END IF;
96
97 x_return_status :=FND_API.G_RET_STS_SUCCESS;
98
99 l_lot_rec.item_no := p_line_rec.opm_item_no;
100 l_lot_rec.lot_no := p_lot_rec.opm_lot_no;
101 l_lot_rec.sublot_no := p_lot_rec.opm_sublot_no;
102 l_lot_rec.lot_desc := NULL;
103 l_lot_rec.qc_grade := p_lot_rec.opm_grade;
104 l_lot_rec.lot_created := p_hdr_rec.trans_date;
105 l_lot_rec.expire_date := p_lot_rec.opm_lot_expiration_date;
106 l_lot_rec.origination_type := 1;
107 l_lot_rec.vendor_lot_no := NULL;
108 l_lot_rec.shipvendor_no := NULL;
109 l_lot_rec.user_name := FND_GLOBAL.USER_NAME;
110
111 GMIPAPI.Create_Lot
112 ( p_api_version => 3.0
113 , p_init_msg_list => FND_API.G_FALSE
114 , p_commit => FND_API.G_FALSE
115 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
116 , x_return_status => x_return_status
117 , x_msg_count => x_msg_count
118 , x_msg_data => x_msg_data
119 , p_lot_rec => l_lot_rec
120 , x_ic_lots_mst_row => x_ic_lots_mst_row
121 , x_ic_lots_cpg_row => l_ic_lots_cpg_row
122 );
123
124 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
125 log_msg('After the call to GMIPAPI.Create_Lot. return status is '||x_return_status);
126 END IF;
127
128 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
129 RAISE FND_API.G_EXC_ERROR;
130 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
132 END IF;
133
134 FND_MSG_PUB.Count_AND_GET
135 (p_count => x_msg_count, p_data => x_msg_data);
136
137 EXCEPTION
138
139 WHEN FND_API.G_EXC_ERROR THEN
140 ROLLBACK to create_lot_in_opm;
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 FND_MSG_PUB.Count_AND_GET
143 (p_count => x_msg_count, p_data => x_msg_data);
144
145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
146 ROLLBACK to create_lot_in_opm;
147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
148 FND_MSG_PUB.Count_AND_GET
149 (p_count => x_msg_count, p_data => x_msg_data);
150
151 WHEN OTHERS THEN
152 ROLLBACK to create_lot_in_opm;
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 FND_MSG_PUB.Count_AND_GET
155 (p_count => x_msg_count, p_data => x_msg_data);
156
157
158 END create_lot_in_opm;
159
160
161 /* +==========================================================================+
162 | PROCEDURE NAME |
163 | verify_lot_uniqueness_in_odm |
164 | |
165 | TYPE |
166 | Private |
167 | |
168 | USAGE |
169 | In discrete parameter lot_number_uniqueness could be set at org level |
170 | to have distinct lot numbers across items in the org. |
171 | This procedure will validate whether the lot number is unique. |
172 | |
173 | |
174 | RETURNS |
175 | Via x_ OUT parameters |
176 | |
177 | HISTORY |
178 | Created Jalaj Srivastava |
179 | |
180 +==========================================================================+ */
181
182 PROCEDURE verify_lot_uniqueness_in_odm
183 ( p_api_version IN NUMBER
184 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
185 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
186 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
187 , x_return_status OUT NOCOPY VARCHAR2
188 , x_msg_count OUT NOCOPY NUMBER
189 , x_msg_data OUT NOCOPY VARCHAR2
190 , p_odm_item_id IN gmi_discrete_transfer_lines.odm_item_id%TYPE
191 , p_odm_lot_number IN gmi_discrete_transfer_lines.odm_lot_number%TYPE
192 ) IS
193
194 l_api_name CONSTANT VARCHAR2(30) := 'verify_lot_uniqueness_in_odm' ;
195 l_api_version CONSTANT NUMBER := 1.0 ;
196 l_count pls_integer;
197
198 BEGIN
199
200 IF FND_API.to_boolean(p_init_msg_list) THEN
201 FND_MSG_PUB.Initialize;
202 END IF;
203
204 -- Standard call to check for call compatibility.
205 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
206 p_api_version ,
207 l_api_name ,
208 G_PKG_NAME ) THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211
212 x_return_status :=FND_API.G_RET_STS_SUCCESS;
213
214 SELECT COUNT(1)
215 INTO l_count
216 FROM mtl_transaction_lot_numbers
217 WHERE inventory_item_id <> p_odm_item_id
218 AND lot_number = p_odm_lot_number
219 AND ROWNUM = 1;
220
221 IF (l_count > 0) THEN
222 FND_MESSAGE.SET_NAME('INV', 'INV_LOT_NUMBER_EXISTS');
223 FND_MSG_PUB.Add;
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226
227 SELECT COUNT(1)
228 INTO l_count
229 FROM mtl_transaction_lots_temp lot, mtl_material_transactions_temp mtl
230 WHERE mtl.inventory_item_id <> p_odm_item_id
231 AND lot.lot_number = p_odm_lot_number
232 AND mtl.transaction_temp_id = lot.transaction_temp_id
233 AND ROWNUM = 1;
234
235 IF (l_count > 0) THEN
236 FND_MESSAGE.SET_NAME('INV', 'INV_LOT_NUMBER_EXISTS');
237 FND_MSG_PUB.Add;
238 RAISE FND_API.G_EXC_ERROR;
239 END IF;
240
241 SELECT COUNT(1)
242 INTO l_count
243 FROM mtl_material_transactions_temp mtl
244 WHERE mtl.inventory_item_id <> p_odm_item_id
245 AND mtl.lot_number = p_odm_lot_number
246 AND ROWNUM = 1;
247
248 IF (l_count > 0) THEN
249 FND_MESSAGE.SET_NAME('INV', 'INV_LOT_NUMBER_EXISTS');
250 FND_MSG_PUB.Add;
251 RAISE FND_API.G_EXC_ERROR;
252 END IF;
253
254 FND_MSG_PUB.Count_AND_GET
255 (p_count => x_msg_count, p_data => x_msg_data);
256
257 EXCEPTION
258
259 WHEN FND_API.G_EXC_ERROR THEN
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 FND_MSG_PUB.Count_AND_GET
262 (p_count => x_msg_count, p_data => x_msg_data);
263
264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 FND_MSG_PUB.Count_AND_GET
267 (p_count => x_msg_count, p_data => x_msg_data);
268
269 WHEN OTHERS THEN
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 FND_MSG_PUB.Count_AND_GET
272 (p_count => x_msg_count, p_data => x_msg_data);
273
274
275 END verify_lot_uniqueness_in_odm;
276
277 PROCEDURE log_msg(p_msg_text IN VARCHAR2) IS
278 BEGIN
279
280 FND_MESSAGE.SET_NAME('GMI','GMI_DEBUG_API');
281 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
282 FND_MSG_PUB.Add;
283
284 END log_msg ;
285
286 END GMIVLDX;