[Home] [Help]
PACKAGE BODY: APPS.GMI_LOTUPDATE_PUB
Source
1 PACKAGE BODY GMI_LotUpdate_PUB AS
2 /* $Header: GMIPLALB.pls 120.0 2005/05/25 15:44:37 appldev noship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | GMIPLALB.pls |
10 | |
11 | TYPE |
12 | Public |
13 | |
14 | PACKAGE NAME |
15 | GMIPLALB |
16 | |
17 | DESCRIPTION |
18 | This package contains the public APIs for updating the descriptive |
19 | columns in lot master |
20 | |
21 | Contents |
22 | update_lot_dff |
23 | |
24 | HISTORY |
25 | Created - Jatinder Gogna - 2/5/04 |
26 | |
27 | |
28 +==========================================================================+
29 */
30
31 /* Global variables */
32 G_PKG_NAME CONSTANT VARCHAR2(30):='GMIVDX';
33
34 PROCEDURE update_lot_dff
35 ( p_api_version IN NUMBER
36 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
37 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
38 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
39 , x_return_status OUT NOCOPY VARCHAR2
40 , x_msg_count OUT NOCOPY NUMBER
41 , x_msg_data OUT NOCOPY VARCHAR2
42 , p_lot_rec IN ic_lots_mst%ROWTYPE
43 ) IS
44 l_api_name CONSTANT VARCHAR2(30) := 'update_lot_dff' ;
45 l_api_version CONSTANT NUMBER := 1.0 ;
46 l_count NUMBER;
47 BEGIN
48 IF FND_API.to_boolean(p_init_msg_list) THEN
49 FND_MSG_PUB.Initialize;
50 END IF;
51
52 -- Standard call to check for call compatibility.
53 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
54 p_api_version ,
55 l_api_name ,
56 G_PKG_NAME ) THEN
57 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
58 END IF;
59 x_return_status :=FND_API.G_RET_STS_SUCCESS;
60
61 /* Transactions are not allowed for the default lots */
62 IF (p_lot_rec.lot_id = 0) THEN
63 FND_MESSAGE.SET_NAME ( 'GMI', 'IC_DEFAULTLOTERR');
64 FND_MSG_PUB.Add;
65 RAISE FND_API.G_EXC_ERROR;
66 END IF;
67
68 /* Validate Item */
69 l_count := 0;
70 SELECT count(*)
71 INTO l_count
72 FROM ic_item_mst_b
73 WHERE item_id = p_lot_rec.item_id;
74 IF l_count = 0 THEN
75 FND_MESSAGE.SET_NAME ( 'GMI', 'IC_ITEMERR');
76 FND_MSG_PUB.Add;
77 RAISE FND_API.G_EXC_ERROR;
78 END IF;
79
80 UPDATE ic_lots_mst
81 SET
82 attribute1 = p_lot_rec.attribute1,
83 attribute2 = p_lot_rec.attribute2,
84 attribute3 = p_lot_rec.attribute3,
85 attribute4 = p_lot_rec.attribute4,
86 attribute5 = p_lot_rec.attribute5,
87 attribute6 = p_lot_rec.attribute6,
88 attribute7 = p_lot_rec.attribute7,
89 attribute8 = p_lot_rec.attribute8,
90 attribute9 = p_lot_rec.attribute9,
91 attribute10 = p_lot_rec.attribute10,
92 attribute11 = p_lot_rec.attribute11,
93 attribute12 = p_lot_rec.attribute12,
94 attribute13 = p_lot_rec.attribute13,
95 attribute14 = p_lot_rec.attribute14,
96 attribute15 = p_lot_rec.attribute15,
97 attribute16 = p_lot_rec.attribute16,
98 attribute17 = p_lot_rec.attribute17,
99 attribute18 = p_lot_rec.attribute18,
100 attribute19 = p_lot_rec.attribute19,
101 attribute20 = p_lot_rec.attribute20,
102 attribute22 = p_lot_rec.attribute22,
103 attribute21 = p_lot_rec.attribute21,
104 attribute23 = p_lot_rec.attribute23,
105 attribute24 = p_lot_rec.attribute24,
106 attribute25 = p_lot_rec.attribute25,
107 attribute26 = p_lot_rec.attribute26,
108 attribute27 = p_lot_rec.attribute27,
109 attribute28 = p_lot_rec.attribute28,
110 attribute29 = p_lot_rec.attribute29,
111 attribute30 = p_lot_rec.attribute30
112 WHERE
113 item_id = p_lot_rec.item_id and
114 lot_id = p_lot_rec.lot_id;
115 IF SQL%NOTFOUND THEN
116 FND_MESSAGE.SET_NAME ( 'GMI', 'IC_NO_REC_GIVEN_PARAM');
117 FND_MSG_PUB.Add;
118 RAISE FND_API.G_EXC_ERROR;
119 END IF;
120
121 IF FND_API.To_Boolean(p_commit) THEN
122 COMMIT WORK;
123 END IF;
124
125 FND_MSG_PUB.Count_AND_GET
126 (p_count => x_msg_count, p_data => x_msg_data);
127
128 EXCEPTION
129 WHEN FND_API.G_EXC_ERROR THEN
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 FND_MSG_PUB.Count_AND_GET
132 (p_count => x_msg_count, p_data => x_msg_data);
133
134 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136 FND_MSG_PUB.Count_AND_GET
137 (p_count => x_msg_count, p_data => x_msg_data);
138
139 WHEN OTHERS THEN
140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
141 FND_MSG_PUB.Count_AND_GET
142 (p_count => x_msg_count, p_data => x_msg_data);
143 END;
144
145
146 /*
147 +==========================================================================+
148 | PROCEDURE NAME |
149 | Update_Lot |
150 | |
151 | TYPE |
152 | Public |
153 | |
154 | DESCRIPTION |
155 | This is a public API for updating the descriptive columns as well |
156 | as expire date in lot master |
157 | |
158 | HISTORY |
159 | Created - Supriya Malluru - 21-Jan-2005 |
160 | |
161 +==========================================================================+
162 */
163 PROCEDURE update_lot
164 ( p_api_version IN NUMBER
165 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
166 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
167 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
168 , x_return_status OUT NOCOPY VARCHAR2
169 , x_msg_count OUT NOCOPY NUMBER
170 , x_msg_data OUT NOCOPY VARCHAR2
171 , p_lot_rec IN ic_lots_mst%ROWTYPE
172 ) IS
173 l_api_name CONSTANT VARCHAR2(30) := 'update_lot' ;
174 l_api_version CONSTANT NUMBER := 1.0 ;
175 l_count NUMBER;
176
177 CURSOR cur_old_lot IS SELECT *
178 FROM ic_lots_mst a
179 WHERE a.item_id = p_lot_rec.item_id
180 AND a.lot_id = p_lot_rec.lot_id;
181
182
183 l_old_rec cur_old_lot%ROWTYPE;
184
185 BEGIN
186 IF FND_API.to_boolean(p_init_msg_list) THEN
187 FND_MSG_PUB.Initialize;
188 END IF;
189
190 -- Standard call to check for call compatibility.
191 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
192 p_api_version ,
193 l_api_name ,
194 G_PKG_NAME ) THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END IF;
197 x_return_status :=FND_API.G_RET_STS_SUCCESS;
198
199 OPEN cur_old_lot;
200 FETCH cur_old_lot INTO l_old_rec;
201 CLOSE cur_old_lot;
202
203 /* Transactions are not allowed for the default lots */
204 IF (l_old_rec.lot_id = 0) THEN
205 FND_MESSAGE.SET_NAME ( 'GMI', 'IC_DEFAULTLOTERR');
206 FND_MSG_PUB.Add;
207 RAISE FND_API.G_EXC_ERROR;
208 END IF;
209
210 /* Validate Item */
211 l_count := 0;
212 SELECT count(*)
213 INTO l_count
214 FROM ic_item_mst_b
215 WHERE item_id = l_old_rec.item_id;
216 IF l_count = 0 THEN
217 FND_MESSAGE.SET_NAME ( 'GMI', 'IC_ITEMERR');
218 FND_MSG_PUB.Add;
219 RAISE FND_API.G_EXC_ERROR;
220 END IF;
221
222 /* Validate EXPIRE Date */
223 IF (NVL(p_lot_rec.expire_date, SYSDATE ) <> NVL(l_old_rec.expire_date, SYSDATE)) THEN
224 IF p_lot_rec.expire_date IS NOT NULL AND p_lot_rec.expire_date < NVL(p_lot_rec.lot_created, l_old_rec.lot_created) THEN
225 FND_MESSAGE.SET_NAME('GMI','IC_PAST_EXPIRE_DATE');
226 FND_MSG_PUB.Add;
227 RAISE FND_API.G_EXC_ERROR;
228 END IF;
229 END IF;
230
231 UPDATE ic_lots_mst
232 SET
233
234 expire_date = p_lot_rec.expire_date,
235 attribute1 = p_lot_rec.attribute1,
236 attribute2 = p_lot_rec.attribute2,
237 attribute3 = p_lot_rec.attribute3,
238 attribute4 = p_lot_rec.attribute4,
239 attribute5 = p_lot_rec.attribute5,
240 attribute6 = p_lot_rec.attribute6,
241 attribute7 = p_lot_rec.attribute7,
242 attribute8 = p_lot_rec.attribute8,
243 attribute9 = p_lot_rec.attribute9,
244 attribute10 = p_lot_rec.attribute10,
245 attribute11 = p_lot_rec.attribute11,
246 attribute12 = p_lot_rec.attribute12,
247 attribute13 = p_lot_rec.attribute13,
248 attribute14 = p_lot_rec.attribute14,
249 attribute15 = p_lot_rec.attribute15,
250 attribute16 = p_lot_rec.attribute16,
251 attribute17 = p_lot_rec.attribute17,
252 attribute18 = p_lot_rec.attribute18,
253 attribute19 = p_lot_rec.attribute19,
254 attribute20 = p_lot_rec.attribute20,
255 attribute22 = p_lot_rec.attribute22,
256 attribute21 = p_lot_rec.attribute21,
257 attribute23 = p_lot_rec.attribute23,
258 attribute24 = p_lot_rec.attribute24,
259 attribute25 = p_lot_rec.attribute25,
260 attribute26 = p_lot_rec.attribute26,
261 attribute27 = p_lot_rec.attribute27,
262 attribute28 = p_lot_rec.attribute28,
263 attribute29 = p_lot_rec.attribute29,
264 attribute30 = p_lot_rec.attribute30
265 WHERE
266 item_id = l_old_rec.item_id
267 AND lot_id = l_old_rec.lot_id;
268
269
270 IF SQL%NOTFOUND THEN
271 FND_MESSAGE.SET_NAME ( 'GMI', 'IC_NO_REC_GIVEN_PARAM');
272 FND_MSG_PUB.Add;
273 RAISE FND_API.G_EXC_ERROR;
274 END IF;
275
276 IF FND_API.To_Boolean(p_commit) THEN
277 COMMIT WORK;
278 END IF;
279
280 FND_MSG_PUB.Count_AND_GET
281 (p_count => x_msg_count, p_data => x_msg_data);
282
283 EXCEPTION
284 WHEN FND_API.G_EXC_ERROR THEN
285 x_return_status := FND_API.G_RET_STS_ERROR;
286 FND_MSG_PUB.Count_AND_GET
287 (p_count => x_msg_count, p_data => x_msg_data);
288
289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
291 FND_MSG_PUB.Count_AND_GET
292 (p_count => x_msg_count, p_data => x_msg_data);
293
294 WHEN OTHERS THEN
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 FND_MSG_PUB.Count_AND_GET
297 (p_count => x_msg_count, p_data => x_msg_data);
298 END;
299
300 END GMI_LotUpdate_PUB;