[Home] [Help]
PACKAGE BODY: APPS.CSI_HZ_LOCATIONS_PVT
Source
1 PACKAGE BODY CSI_HZ_LOCATIONS_PVT AS
2 /* $Header: csivhzlb.pls 120.2 2005/12/09 16:32:36 rmamidip noship $ */
3 -- Start of Comments
4 -- Package name : CSI_HZ_LOCATIONS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 -- Default number of records fetch per call
11
12 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_hz_locations_pvt';
13 g_file_name CONSTANT VARCHAR2(12) := 'csivhzlb.pls';
14
15 --+=================================================================+
16 --| Create_location procedure written for calling it from CSI forms |
17 --| This procedure validates for unique clli_code and calls |
18 --| hz_location_v2pub.create_location |
19 --+=================================================================+
20
21 PROCEDURE create_location(
22 p_api_version IN NUMBER ,
23 p_commit IN VARCHAR2 := fnd_api.g_false,
24 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
25 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
26 p_country IN VARCHAR2,
27 p_address1 IN VARCHAR2,
28 p_address2 IN VARCHAR2,
29 p_address3 IN VARCHAR2,
30 p_address4 IN VARCHAR2,
31 p_city IN VARCHAR2,
32 p_postal_code IN VARCHAR2,
33 p_state IN VARCHAR2,
34 p_province IN VARCHAR2,
35 p_county IN VARCHAR2,
36 p_clli_code IN VARCHAR2,
37 p_description IN VARCHAR2,
38 p_last_update_date IN DATE ,
39 p_last_updated_by IN NUMBER ,
40 p_creation_date IN DATE ,
41 p_created_by IN NUMBER ,
42 p_created_by_module IN VARCHAR2,
43 x_location_id OUT NOCOPY NUMBER ,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER ,
46 x_msg_data OUT NOCOPY VARCHAR2
47 ) IS
48 l_api_name CONSTANT VARCHAR2(30) := 'create_location';
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_location_rec HZ_LOCATION_v2PUB.LOCATION_REC_TYPE;
51 l_location_id NUMBER;
52 l_debug_level NUMBER;
53 l_dummy VARCHAR2(1);
54
55 BEGIN
56 SAVEPOINT create_location_pvt;
57
58 -- standard call to check for call compatibility.
59 IF NOT fnd_api.compatible_api_call ( l_api_version,
60 p_api_version,
61 l_api_name,
62 g_pkg_name)
63 THEN
64 RAISE fnd_api.g_exc_unexpected_error;
65 END IF;
66
67
68 -- initialize message list if p_init_msg_list is set to true.
69 IF fnd_api.to_boolean( p_init_msg_list )
70 THEN
71 fnd_msg_pub.initialize;
72 END IF;
73
74 -- initialize api return status to success
75 x_return_status := fnd_api.g_ret_sts_success;
76
77 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
78 -- if debug_level = 1 then dump the procedure name
79 IF (l_debug_level > 0) THEN
80 csi_gen_utility_pvt.put_line( 'create_location');
81 END IF;
82
83 -- if the debug level = 2 then dump all the parameters values.
84 IF (l_debug_level > 1) THEN
85 csi_gen_utility_pvt.put_line(
86 p_api_version ||'-'||
87 p_commit ||'-'||
88 p_init_msg_list ||'-'||
89 p_validation_level ||'-'||
90 p_country ||'-'||
91 p_address1 ||'-'||
92 p_address2 ||'-'||
93 p_address3 ||'-'||
94 p_address4 ||'-'||
95 p_city ||'-'||
96 p_postal_code ||'-'||
97 p_state ||'-'||
98 p_province ||'-'||
99 p_county ||'-'||
100 p_clli_code ||'-'||
101 p_created_by_module);
102 END IF;
103
104 l_location_rec.country := p_country ;
105 l_location_rec.address1 := p_address1;
106 l_location_rec.address2 := p_address2;
107 l_location_rec.address3 := p_address3;
108 l_location_rec.address4 := p_address4;
109 l_location_rec.city := p_city;
110 l_location_rec.postal_code := p_postal_code;
111 l_location_rec.state := p_state;
112 l_location_rec.province := p_province;
113 l_location_rec.county := p_county;
114 l_location_rec.content_source_type := 'USER_ENTERED';
115 l_location_rec.clli_code := p_clli_code;
116 l_location_rec.description := p_description;
117 l_location_rec.created_by_module := p_created_by_module;
118
119 -- Now call the stored program
120
124 SELECT 'x'
121 IF l_location_rec.clli_code IS NOT NULL AND
122 l_location_rec.clli_code <> FND_API.G_MISS_CHAR THEN
123 BEGIN
125 INTO l_dummy
126 FROM hz_locations
127 WHERE clli_code = l_location_rec.clli_code;
128
129 fnd_message.set_name('CSI', 'CSI_DUPLICATE_CLLI_CODE');
130 fnd_message.set_token('PARAMETER',l_location_rec.clli_code);
131 fnd_msg_pub.add;
132 RAISE fnd_api.g_exc_error;
133 EXCEPTION
134 WHEN TOO_MANY_ROWS THEN
135 fnd_message.set_name('CSI', 'CSI_DUPLICATE_CLLI_CODE');
136 fnd_message.set_token('PARAMETER',l_location_rec.clli_code);
137 fnd_msg_pub.add;
138 RAISE fnd_api.g_exc_error;
139 WHEN NO_DATA_FOUND THEN
140 NULL;
141 END;
142 END IF;
143
144 hz_location_v2pub.create_location(p_init_msg_list => p_init_msg_list
145 ,p_location_rec => l_location_rec
146 ,x_location_id => x_location_id
147 ,x_return_status => x_return_status
148 ,x_msg_count => x_msg_count
149 ,x_msg_data => x_msg_data);
150
151 IF fnd_api.to_boolean( p_commit )
152 THEN
153 COMMIT WORK;
154 END IF;
155
156 -- standard call to get message count and if count is 1, get message info.
157 fnd_msg_pub.count_and_get
158 ( p_count => x_msg_count,
159 p_data => x_msg_data
160 );
161
162 EXCEPTION
163 WHEN fnd_api.g_exc_error THEN
164 ROLLBACK TO create_location_pvt;
165 x_return_status := fnd_api.g_ret_sts_error ;
166 fnd_msg_pub.count_and_get
167 (p_count => x_msg_count ,
168 p_data => x_msg_data
169 );
170 END create_location;
171
172
173 --+=================================================================+
174 --| Update_location procedure written for calling it from CSI forms |
175 --| This procedure validates for unique clli_code and calls |
176 --| hz_location_v2pub.update_location |
177 --+=================================================================+
178
179 PROCEDURE update_location(
180 p_api_version IN NUMBER,
181 p_commit IN VARCHAR2 := fnd_api.g_false,
182 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
183 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
184 p_location_id IN NUMBER,
185 p_country IN VARCHAR2,
186 p_address1 IN VARCHAR2,
187 p_address2 IN VARCHAR2,
188 p_address3 IN VARCHAR2,
189 p_address4 IN VARCHAR2,
190 p_city IN VARCHAR2,
191 p_postal_code IN VARCHAR2,
192 p_state IN VARCHAR2,
193 p_province IN VARCHAR2,
194 p_county IN VARCHAR2,
195 p_clli_code IN VARCHAR2,
196 p_description IN VARCHAR2,
197 p_last_update_date IN DATE ,
198 p_last_updated_by IN NUMBER ,
199 p_creation_date IN DATE ,
200 p_created_by IN NUMBER ,
201 p_created_by_module IN VARCHAR2,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER ,
204 x_msg_data OUT NOCOPY VARCHAR2
205 ) IS
206 l_api_name CONSTANT VARCHAR2(30) := 'update_location';
207 l_api_version CONSTANT NUMBER := 1.0;
208 l_location_rec HZ_LOCATION_v2PUB.LOCATION_REC_TYPE;
209 l_location_id NUMBER;
210 l_debug_level NUMBER;
211 l_object_version_number number;
212 l_dummy VARCHAR2(1);
213 BEGIN
214
215 SAVEPOINT update_location_pvt;
216
217 -- standard call TO check FOR call compatibility.
218 IF NOT fnd_api.compatible_api_call ( l_api_version,
219 p_api_version,
220 l_api_name,
221 g_pkg_name)
222 THEN
223 RAISE fnd_api.g_exc_unexpected_error;
224 END IF;
225
226
227 -- initialize message list IF p_init_msg_list IS set TO true.
228 IF fnd_api.to_boolean( p_init_msg_list )
229 THEN
230 fnd_msg_pub.initialize;
231 END IF;
232
233 -- initialize api return status to success
234 x_return_status := fnd_api.g_ret_sts_success;
235
236 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
237 -- if debug_level = 1 then dump the procedure name
238 IF (l_debug_level > 0) THEN
239 csi_gen_utility_pvt.put_line( 'update_location');
240 END IF;
241
242 -- IF the debug level = 2 THEN dump all the parameters values.
243 IF (l_debug_level > 1) THEN
244 csi_gen_utility_pvt.put_line(
245 p_api_version ||'-'||
246 p_commit ||'-'||
247 p_init_msg_list ||'-'||
248 p_validation_level ||'-'||
249 p_location_id ||'-'||
250 p_country ||'-'||
251 p_address1 ||'-'||
252 p_address2 ||'-'||
253 p_address3 ||'-'||
254 p_address4 ||'-'||
255 p_city ||'-'||
256 p_postal_code ||'-'||
257 p_state ||'-'||
258 p_province ||'-'||
259 p_county ||'-'||
260 p_clli_code ||'-'||
261 p_created_by_module);
262 END IF;
263
264 l_location_rec.location_id := p_location_id;
265 l_location_rec.country := p_country ;
266 l_location_rec.address1 := p_address1;
267 l_location_rec.address2 := p_address2;
268 l_location_rec.address3 := p_address3;
269 l_location_rec.address4 := p_address4;
270 l_location_rec.city := p_city;
271 l_location_rec.postal_code := p_postal_code;
272 l_location_rec.state := p_state;
273 l_location_rec.province := p_province;
274 l_location_rec.county := p_county;
275 l_location_rec.clli_code := p_clli_code;
276 l_location_rec.description := p_description;
277 l_location_rec.created_by_module := p_created_by_module;
278
279
280 -- Now call the stored program
281
282
283
284 IF p_location_id IS NULL OR
285 p_location_id = FND_API.G_MISS_NUM THEN
286 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
287 FND_MESSAGE.SET_TOKEN('COLUMN', 'location id');
288 FND_MSG_PUB.ADD;
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291
292 BEGIN
293 SELECT object_version_number
294 INTO l_object_version_number
295 FROM hz_locations
296 WHERE location_id = l_location_rec.location_id;
297 EXCEPTION
298 WHEN NO_DATA_FOUND THEN
299 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
300 FND_MESSAGE.SET_TOKEN('RECORD', 'location');
301 FND_MESSAGE.SET_TOKEN('VALUE', to_char(l_location_rec.location_id));
302 FND_MSG_PUB.ADD;
303 RAISE FND_API.G_EXC_ERROR;
304 END;
305
306 IF l_location_rec.clli_code IS NOT NULL AND
307 l_location_rec.clli_code <> FND_API.G_MISS_CHAR THEN
308 BEGIN
309 SELECT 'x'
310 INTO l_dummy
311 FROM hz_locations
312 WHERE clli_code = l_location_rec.clli_code
313 AND location_id <> l_location_rec.location_id;
314 fnd_message.set_name('CSI', 'CSI_DUPLICATE_CLLI_CODE');
315 fnd_message.set_token('PARAMETER',l_location_rec.clli_code);
316 fnd_msg_pub.add;
317 RAISE fnd_api.g_exc_error;
318 EXCEPTION
319 WHEN TOO_MANY_ROWS THEN
320 fnd_message.set_name('CSI', 'CSI_DUPLICATE_CLLI_CODE');
321 fnd_message.set_token('PARAMETER',l_location_rec.clli_code);
322 fnd_msg_pub.add;
323 RAISE fnd_api.g_exc_error;
324 WHEN NO_DATA_FOUND THEN
325 NULL;
326 END;
327 END IF;
328
329 -- Now call the stored program
330
331 hz_location_v2pub.update_location(p_init_msg_list => p_init_msg_list
332 ,p_location_rec => l_location_rec
333 ,p_object_version_number => l_object_version_number
334 ,x_return_status => x_return_status
335 ,x_msg_count => x_msg_count
336 ,x_msg_data => x_msg_data
337 );
338
339
340
341 IF fnd_api.to_boolean( p_commit )
342 THEN
343 COMMIT WORK;
344 END IF;
345
346 -- standard call to get message count and if count is 1, get message info.
347 fnd_msg_pub.count_and_get
348 ( p_count => x_msg_count,
349 p_data => x_msg_data
350 );
351
352 EXCEPTION
353 WHEN fnd_api.g_exc_error THEN
354 ROLLBACK TO update_location_pvt;
355 x_return_status := fnd_api.g_ret_sts_error ;
356 fnd_msg_pub.count_and_get
357 (p_count => x_msg_count ,
358 p_data => x_msg_data
359 );
360 END update_location;
361
362 --+=================================================================+
363 --| Lock_location procedure written for calling it from CSI forms |
364 --+=================================================================+
365
366 PROCEDURE Lock_location(
367 p_location_id NUMBER ,
368 p_last_update_date DATE ,
369 p_last_updated_by NUMBER ,
370 p_creation_date DATE ,
371 p_created_by NUMBER ,
372 p_country VARCHAR2,
373 p_address1 VARCHAR2,
374 p_address2 VARCHAR2,
375 p_address3 VARCHAR2,
376 p_address4 VARCHAR2,
377 p_city VARCHAR2,
378 p_postal_code VARCHAR2,
379 p_state VARCHAR2,
380 p_province VARCHAR2,
381 p_county VARCHAR2,
382 p_clli_code VARCHAR2,
383 p_description VARCHAR2,
384 p_created_by_module VARCHAR2
385 )
386 IS
387 CURSOR C IS
388 SELECT *
389 FROM hz_locations
390 WHERE location_id = p_location_id
391 FOR UPDATE of location_id NOWAIT;
392 Recinfo C%ROWTYPE;
393 BEGIN
394 OPEN C;
395 FETCH C INTO Recinfo;
396 If (C%NOTFOUND) then
397 CLOSE C;
398 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
399 APP_EXCEPTION.RAISE_EXCEPTION;
400 End If;
401 CLOSE C;
402
403 if (
404 ( ( recinfo.location_id = p_location_id)
405 OR ( ( recinfo.location_id IS NULL )
406 AND ( p_location_id IS NULL )))
407 AND ( ( recinfo.last_update_date = p_last_update_date)
408 OR ( ( recinfo.last_update_date IS NULL )
409 AND ( p_last_update_date IS NULL )))
410 AND ( ( recinfo.last_updated_by = p_last_updated_by)
411 OR ( ( recinfo.last_updated_by IS NULL )
412 AND ( p_last_updated_by IS NULL )))
413 AND ( ( recinfo.creation_date = p_creation_date)
414 OR ( ( recinfo.creation_date IS NULL )
415 AND ( p_creation_date IS NULL )))
416 AND ( ( recinfo.created_by = p_created_by)
417 OR ( ( recinfo.created_by IS NULL )
418 AND ( p_created_by IS NULL )))
419 AND ( ( recinfo.country = p_country)
420 OR ( ( recinfo.country IS NULL )
421 AND ( p_country IS NULL )))
422 AND ( ( recinfo.address1 = p_address1)
423 OR ( ( recinfo.address1 IS NULL )
424 AND ( p_address1 IS NULL )))
425 AND ( ( recinfo.address2 = p_address2)
426 OR ( ( recinfo.address2 IS NULL )
427 AND ( p_address2 IS NULL )))
428 AND ( ( recinfo.address3 = p_address3)
429 OR ( ( recinfo.address3 IS NULL )
430 AND ( p_address3 IS NULL )))
434 AND ( ( recinfo.city = p_city)
431 AND ( ( recinfo.address4 = p_address4)
432 OR ( ( recinfo.address4 IS NULL )
433 AND ( p_address4 IS NULL )))
435 OR ( ( recinfo.city IS NULL )
436 AND ( p_city IS NULL )))
437 AND ( ( recinfo.postal_code = p_postal_code)
438 OR ( ( recinfo.postal_code IS NULL )
439 AND ( p_postal_code IS NULL )))
440 AND ( ( recinfo.state = p_state)
441 OR ( ( recinfo.state IS NULL )
442 AND ( p_state IS NULL )))
443 AND ( ( recinfo.province = p_province)
444 OR ( ( recinfo.province IS NULL )
445 AND ( p_province IS NULL )))
446 AND ( ( recinfo.county = p_county)
447 OR ( ( recinfo.county IS NULL )
448 AND ( p_county IS NULL )))
449 AND ( ( recinfo.clli_code = p_clli_code)
450 OR ( ( recinfo.clli_code IS NULL )
451 AND ( p_clli_code IS NULL )))
452 AND ( ( recinfo.description = p_description)
453 OR ( ( recinfo.description IS NULL )
454 AND ( p_description IS NULL )))
455 AND ( ( recinfo.created_by_module = p_created_by_module)
456 OR ( ( recinfo.created_by_module IS NULL )
457 AND ( p_created_by_module IS NULL )))
458 ) THEN
459 RETURN;
460 ELSE
461 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
462 APP_EXCEPTION.RAISE_EXCEPTION;
463 END IF;
464 END Lock_location;
465
466 END CSI_HZ_LOCATIONS_PVT;