[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_CATEGORIES_PUB
Source
1 PACKAGE BODY cn_quota_categories_pub AS
2 -- $Header: cnpqcatb.pls 115.10 2003/01/25 00:09:12 fmburu ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_QUOTA_CATEGORIES_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpqcatb.pls';
6
7 PROCEDURE Create_Quota_Category(
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10 p_commit IN VARCHAR2 := FND_API.G_FALSE,
11 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
12 p_rec IN quota_category_rec_type,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2,
16 x_quota_category_id OUT NOCOPY NUMBER) IS
17
18 l_api_name CONSTANT VARCHAR2(30) := 'Create_Quota_Category';
19 l_api_version CONSTANT NUMBER := 1.0;
20 l_newrec CN_QUOTA_CATEGORIES_PKG.quota_categories_rec_type;
21 l_count NUMBER;
22
23 G_LAST_UPDATE_DATE DATE := sysdate;
24 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
25 G_CREATION_DATE DATE := sysdate;
26 G_CREATED_BY NUMBER := fnd_global.user_id;
27 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
28 BEGIN
29 -- Standard Start of API savepoint
30 SAVEPOINT Create_Quota_Category;
31 -- Standard call to check for call compatibility.
32 IF NOT FND_API.Compatible_API_Call
33 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
34 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
35 END IF;
36 -- Initialize message list if p_init_msg_list is set to TRUE.
37 IF FND_API.to_Boolean( p_init_msg_list ) THEN
38 FND_MSG_PUB.initialize;
39 END IF;
40 -- Initialize API return status to success
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42
43 l_newrec.name := p_rec.name;
44 l_newrec.description := p_rec.description;
45 l_newrec.type := p_rec.type;
46 l_newrec.compute_flag := p_rec.compute_flag;
47 l_newrec.interval_type_id := p_rec.interval_type_id;
48 l_newrec.quota_unit_code := p_rec.quota_unit_code;
49
50 -- make sure that same name is not existing.
51 SELECT count(quota_category_id)
52 INTO l_count
53 FROM cn_quota_categories
54 WHERE UPPER(name) = UPPER(l_newrec.name);
55 IF l_count > 0 THEN
56 FND_MESSAGE.SET_NAME ('CN','CN_QUOTA_CATEGORY_DUP');
57 FND_MSG_PUB.Add;
58 RAISE FND_API.G_EXC_ERROR;
59 END IF;
60
61 cn_quota_categories_pkg.insert_row(l_newrec);
62 -- Standard check of p_commit.
63 IF FND_API.To_Boolean( p_commit ) THEN
64 COMMIT WORK;
65 END IF;
66 FND_MSG_PUB.Count_And_Get(
67 p_count => x_msg_count,
68 p_data => x_msg_data,
69 p_encoded => FND_API.G_FALSE);
70 EXCEPTION
71 WHEN FND_API.G_EXC_ERROR THEN
72 ROLLBACK TO Create_Quota_Category;
73 x_return_status := FND_API.G_RET_STS_ERROR;
74 FND_MSG_PUB.Count_And_Get(
75 p_count => x_msg_count,
76 p_data => x_msg_data,
77 p_encoded => FND_API.G_FALSE);
78 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
79 ROLLBACK TO Create_Quota_Category;
80 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
81 FND_MSG_PUB.Count_And_Get(
82 p_count => x_msg_count,
83 p_data => x_msg_data,
84 p_encoded => FND_API.G_FALSE);
85 WHEN OTHERS THEN
86 ROLLBACK TO Create_Quota_Category;
87 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
88 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
89 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
90 END IF;
91 FND_MSG_PUB.Count_And_Get(
92 p_count => x_msg_count,
93 p_data => x_msg_data,
94 p_encoded => FND_API.G_FALSE);
95 END Create_Quota_Category;
96 --
97
98 PROCEDURE Update_Quota_Category(
99 p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
101 p_commit IN VARCHAR2 := FND_API.G_FALSE,
102 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
103 p_rec IN quota_category_rec_type,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_msg_count OUT NOCOPY NUMBER,
106 x_msg_data OUT NOCOPY VARCHAR2) IS
107
108 l_api_name CONSTANT VARCHAR2(30) := 'Update_Quota_Category';
109 l_api_version CONSTANT NUMBER := 1.0;
110 l_newrec CN_QUOTA_CATEGORIES_PKG.quota_categories_rec_type;
111 l_count NUMBER;
112 l_old_name cn_quota_categories.name%type ;
113
114 G_LAST_UPDATE_DATE DATE := sysdate;
115 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
116 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
117
118 BEGIN
119 -- Standard Start of API savepoint
120 SAVEPOINT Update_Quota_Category;
121
122 -- Standard call to check for call compatibility.
123 IF NOT FND_API.Compatible_API_Call
124 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END IF;
127
128 -- Initialize message list if p_init_msg_list is set to TRUE.
129 IF FND_API.to_Boolean( p_init_msg_list ) THEN
130 FND_MSG_PUB.initialize;
131 END IF;
132
133 -- Initialize API return status to success
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135
136 -- make sure record to be updated exists.
137 SELECT count(quota_category_id)
138 INTO l_count
139 FROM cn_quota_categories
140 WHERE quota_category_id = p_rec.quota_category_id;
141 IF (l_count = 0) THEN
142 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_UPDATE_REC');
143 FND_MSG_PUB.ADD;
144 RAISE FND_API.G_EXC_ERROR;
145 END IF;
146
147 -- check whether this quota_category_id is already assigned
148 -- cn_role_quota_cates table
149 SELECT count(quota_category_id)
150 INTO l_count
151 FROM cn_role_quota_cates
152 WHERE quota_category_id = p_rec.quota_category_id;
153
154 IF (l_count > 0) THEN
155 SELECT NAME
156 INTO l_old_name
157 FROM cn_quota_categories
158 WHERE quota_category_id = p_rec.quota_category_id;
159
160 FND_MESSAGE.SET_NAME('CN', 'CN_RECORD_EXISTS_ERR');
161 FND_MESSAGE.SET_TOKEN('QUOTA_CAT_NAME', l_old_name);
162 FND_MSG_PUB.ADD;
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165
166 l_newrec.quota_category_id := p_rec.quota_category_id;
167 l_newrec.name := p_rec.name;
168 l_newrec.description := p_rec.description;
169 l_newrec.type := p_rec.type;
170 l_newrec.compute_flag := p_rec.compute_flag;
171 l_newrec.interval_type_id := p_rec.interval_type_id;
172 l_newrec.quota_unit_code := p_rec.quota_unit_code;
173 l_newrec.object_version_number := p_rec.object_version_number;
174
175 -- make sure the object version number hasn't changed in the meantime
176 cn_quota_categories_pkg.lock_update_row(l_newrec);
177
178 -- Standard check of p_commit.
179 IF FND_API.To_Boolean( p_commit ) THEN
180 COMMIT WORK;
181 END IF;
182
183 FND_MSG_PUB.Count_And_Get(
184 p_count => x_msg_count,
185 p_data => x_msg_data,
186 p_encoded => FND_API.G_FALSE);
187 EXCEPTION
188 WHEN FND_API.G_EXC_ERROR THEN
189 ROLLBACK TO Update_Quota_Category;
190 x_return_status := FND_API.G_RET_STS_ERROR;
191 FND_MSG_PUB.Count_And_Get(
192 p_count => x_msg_count,
193 p_data => x_msg_data,
194 p_encoded => FND_API.G_FALSE);
195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
196 ROLLBACK TO Update_Quota_Category;
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 FND_MSG_PUB.Count_And_Get(
199 p_count => x_msg_count,
200 p_data => x_msg_data,
201 p_encoded => FND_API.G_FALSE);
202 WHEN OTHERS THEN
203 ROLLBACK TO Update_Quota_Category;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
206 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
207 END IF;
208 FND_MSG_PUB.Count_And_Get(
209 p_count => x_msg_count,
210 p_data => x_msg_data,
211 p_encoded => FND_API.G_FALSE);
212 END Update_Quota_Category;
213
214 PROCEDURE Delete_Quota_Category(
215 p_api_version IN NUMBER,
216 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
217 p_commit IN VARCHAR2 := FND_API.G_FALSE,
218 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
219 p_quota_category_id IN NUMBER,
220 p_object_version_number IN NUMBER,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2) IS
224
225 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Quota_Category';
226 l_api_version CONSTANT NUMBER := 1.0;
227 l_count NUMBER;
228 qc_name CN_QUOTA_CATEGORIES.NAME%TYPE ;
229 BEGIN
230
231 -- Standard Start of API savepoint
232 SAVEPOINT Delete_Quota_Category;
233 -- Standard call to check for call compatibility.
234 IF NOT FND_API.Compatible_API_Call
235 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
237 END IF;
238
239 -- Initialize message list if p_init_msg_list is set to TRUE.
240 IF FND_API.to_Boolean( p_init_msg_list ) THEN
241 FND_MSG_PUB.initialize;
242 END IF;
243
244 -- Initialize API return status to success
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246 -- make sure record to be deleted exists.
247 SELECT count(quota_category_id)
248 INTO l_count
249 FROM cn_quota_categories
250 WHERE quota_category_id = p_quota_category_id;
251 IF (l_count = 0) THEN
252 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_DELETE_REC');
253 FND_MSG_PUB.ADD;
254 RAISE FND_API.G_EXC_ERROR;
255 END IF;
256 -- check whether this quota_category_id is already assigned
257 -- cn_role_quota_cates table
258 SELECT count(quota_category_id)
259 INTO l_count
260 FROM cn_role_quota_cates
261 WHERE quota_category_id = p_quota_category_id;
262
263 IF (l_count > 0) THEN
264 SELECT NAME
265 INTO qc_name
266 FROM CN_QUOTA_CATEGORIES
267 WHERE quota_category_id = p_quota_category_id;
268
269 FND_MESSAGE.SET_NAME('CN', 'CN_RECORD_EXISTS_ERR');
270 FND_MESSAGE.SET_TOKEN('QUOTA_CAT_NAME', qc_name);
271 FND_MSG_PUB.ADD;
272 RAISE FND_API.G_EXC_ERROR ;
273 END IF;
274
275 -- make sure the object version number hasn't changed in the meantime
276 cn_quota_categories_pkg.delete_row(p_quota_category_id);
277
278 -- Standard check of p_commit.
279 IF FND_API.To_Boolean( p_commit ) THEN
280 COMMIT WORK;
281 END IF;
282
283 FND_MSG_PUB.Count_And_Get(
284 p_count => x_msg_count,
285 p_data => x_msg_data,
286 p_encoded => FND_API.G_FALSE);
287 EXCEPTION
288 WHEN FND_API.G_EXC_ERROR THEN
289 ROLLBACK TO Delete_Quota_Category;
290 x_return_status := FND_API.G_RET_STS_ERROR;
291 FND_MSG_PUB.Count_And_Get(
292 p_count => x_msg_count,
293 p_data => x_msg_data,
294 p_encoded => FND_API.G_FALSE);
295 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
296 ROLLBACK TO Delete_Quota_Category;
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 FND_MSG_PUB.Count_And_Get(
299 p_count => x_msg_count,
300 p_data => x_msg_data,
301 p_encoded => FND_API.G_FALSE);
302 WHEN OTHERS THEN
303 ROLLBACK TO Delete_Quota_Category;
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
306 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
307 END IF;
308 FND_MSG_PUB.Count_And_Get(
309 p_count => x_msg_count,
310 p_data => x_msg_data,
311 p_encoded => FND_API.G_FALSE);
312 END Delete_Quota_Category;
313
314 PROCEDURE get_quota_category_details
315 ( p_api_version IN NUMBER,
316 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
317 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
318
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2,
322
323 p_start_record IN NUMBER,
324 p_increment_count IN NUMBER,
325
326 p_search_name IN VARCHAR2,
327 p_search_type IN VARCHAR2,
328 p_search_unit IN VARCHAR2,
329
330 x_quota_categories_tbl OUT NOCOPY quota_categories_tbl_type,
331
332 x_total_records OUT NOCOPY NUMBER
333 ) IS
334
335 l_api_name CONSTANT VARCHAR2(30) := 'get_quota_category_details';
336 l_api_version CONSTANT NUMBER := 1.0;
337
338 l_counter NUMBER;
339 l_quota_description VARCHAR2(30);
340
341 CURSOR c_quota_categories IS
342 SELECT cn_cat.quota_CATEGORY_ID,
343 cn_cat.name ,
344 cn_cat.description ,
345 cn_cat.type ,
346 Nvl(cn_cat.compute_flag,'N') compute_flag,
347 cn_cat.object_version_number,
348 cn_cat.interval_type_id,
349 cn_cat.quota_unit_code
350 FROM cn_quota_categories cn_cat
351 WHERE upper(name) like upper(p_search_name)
352 AND type = p_search_type
353 AND quota_unit_code = p_search_unit
354 ORDER BY name;
355
356 BEGIN
357 --
358 -- Standard call to check for call compatibility.
359 --
360 IF NOT FND_API.compatible_api_call
361 ( l_api_version ,p_api_version ,l_api_name,G_PKG_NAME )
362 THEN
363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
364 END IF;
365
366 --
367 -- Initialize message list if p_init_msg_list is set to TRUE.
368 --
369 IF FND_API.to_Boolean( p_init_msg_list ) THEN
370 FND_MSG_PUB.initialize;
371 END IF;
372
373 --
374 -- Initialize API return status to success
375 --
376 x_return_status := FND_API.G_RET_STS_SUCCESS;
377
378 --
379 -- API body
380 --
381 l_counter := 0;
382 x_total_records := 0;
383
384 FOR quota_categories_rec IN c_quota_categories LOOP
385 x_total_records := x_total_records + 1;
386 IF l_counter + 1 BETWEEN p_start_record
387 AND (p_start_record + Nvl(p_increment_count,
388 9999999999999999999999) - 1) THEN
389 x_quota_categories_tbl(l_counter).quota_category_id :=
390 quota_categories_rec.quota_category_id;
391 x_quota_categories_tbl(l_counter).name :=
392 quota_categories_rec.name;
393 x_quota_categories_tbl(l_counter).description :=
394 quota_categories_rec.description;
395 x_quota_categories_tbl(l_counter).type :=
396 quota_categories_rec.type;
397 x_quota_categories_tbl(l_counter).interval_type_id :=
398 quota_categories_rec.interval_type_id;
399 x_quota_categories_tbl(l_counter).quota_unit_code :=
400 quota_categories_rec.quota_unit_code;
401 x_quota_categories_tbl(l_counter).compute_flag :=
402 quota_categories_rec.compute_flag;
403 x_quota_categories_tbl(l_counter).object_version_number :=
404 quota_categories_rec.object_version_number;
405
406 IF quota_categories_rec.quota_category_id = -1000 THEN
407 x_quota_categories_tbl(l_counter).name :=
408 cn_api.get_lkup_meaning
409 (p_lkup_code => quota_categories_rec.name,
410 p_lkup_type => 'QUOTA_CATEGORY');
411 END IF;
412
413 IF quota_categories_rec.TYPE IS NOT NULL THEN
414 x_quota_categories_tbl(l_counter).type_meaning :=
415 cn_api.get_lkup_meaning
416 (p_lkup_code => quota_categories_rec.TYPE,
417 p_lkup_type => 'QUOTA_CATEGORY');
418 END IF;
419
420 SELECT meaning INTO x_quota_categories_tbl(l_counter).computed
421 FROM fnd_lookups
422 WHERE lookup_code = quota_categories_rec.compute_flag
423 AND lookup_type = 'YES_NO';
424
425 END IF;
426 l_counter := l_counter + 1;
427 END LOOP;
428
429 EXCEPTION
430 WHEN FND_API.G_EXC_ERROR THEN
431 x_return_status := FND_API.G_RET_STS_ERROR ;
432 FND_MSG_PUB.Count_And_Get
433 (
434 p_count => x_msg_count ,
435 p_data => x_msg_data ,
436 p_encoded => FND_API.G_FALSE
437 );
438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
440 FND_MSG_PUB.Count_And_Get
441 (
442 p_count => x_msg_count ,
443 p_data => x_msg_data ,
444 p_encoded => FND_API.G_FALSE
445 );
446 WHEN OTHERS THEN
447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
448 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
449 THEN
450 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
451 END IF;
452 FND_MSG_PUB.Count_And_Get
453 (
454 p_count => x_msg_count ,
455 p_data => x_msg_data ,
456 p_encoded => FND_API.G_FALSE
457 );
458 END;
459
460 END cn_quota_categories_pub;
461