[Home] [Help]
PACKAGE BODY: APPS.CN_ATTAIN_TIER_PVT
Source
1 PACKAGE BODY CN_ATTAIN_TIER_PVT AS
2 /*$Header: cnvattrb.pls 115.4 2002/11/21 21:11:39 hlchen ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_ATTAIN_TIER_PVT';
5
6 -- Start of comments
7 -- API name : Create_Attain_Tier
8 -- Type : Private.
9 -- Function :
10 -- Pre-reqs : None.
11 -- Parameters :
12 -- IN : p_api_version IN NUMBER Required
13 -- p_init_msg_list IN VARCHAR2 Optional
14 -- Default = FND_API.G_FALSE
15 -- p_commit IN VARCHAR2 Optional
16 -- Default = FND_API.G_FALSE
17 -- p_validation_level IN NUMBER Optional
18 -- Default = FND_API.G_VALID_LEVEL_FULL
19 -- p_attain_tier IN attain_tier_rec_type
20 -- OUT : x_return_status OUT VARCHAR2(1)
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2(2000)
23 -- Version : Current version 1.0
24 -- Notes : Note text
25 --
26 -- End of comments
27 PROCEDURE Create_Attain_Tier
28 (p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
30 p_commit IN VARCHAR2 := FND_API.G_FALSE,
31 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
32 p_attain_tier IN attain_tier_rec_type,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2
36 ) IS
37
38 G_LAST_UPDATE_DATE DATE := Sysdate;
39 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
40 G_CREATION_DATE DATE := Sysdate;
41 G_CREATED_BY NUMBER := fnd_global.user_id;
42 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
43 G_ROWID VARCHAR2(30);
44
45 l_api_name CONSTANT VARCHAR2(30) := 'Create_Attain_Tier';
46 l_api_version CONSTANT NUMBER := 1.0;
47
48 l_attain_tier_id NUMBER;
49 l_current_seq NUMBER;
50 l_assign_seq NUMBER;
51 l_temp_count NUMBER;
52 BEGIN
53 -- Standard Start of API savepoint
54 SAVEPOINT Create_Attain_Tier;
55 -- Standard call to check for call compatibility.
56 IF NOT FND_API.Compatible_API_Call
57 (l_api_version ,
58 p_api_version ,
59 l_api_name ,
60 G_PKG_NAME )
61 THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64 -- Initialize message list if p_init_msg_list is set to TRUE.
65 IF FND_API.to_Boolean( p_init_msg_list ) THEN
66 FND_MSG_PUB.initialize;
67 END IF;
68 -- Initialize API return status to success
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70 -- API body
71
72 IF ( p_attain_tier.attain_schedule_id is NULL ) OR
73 ( p_attain_tier.percent is NULL )
74 THEN
75 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
76 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
77 FND_MESSAGE.SET_TOKEN('INPUT_NAME',
78 'Attain Schedule, Attain Tier or Percentage Amount');
79 FND_MSG_PUB.Add;
80 END IF;
81 RAISE FND_API.G_EXC_ERROR ;
82 END IF;
83
84 -- same role quota category is not allowed to be
85 -- assigned twice
86 SELECT count(1)
87 INTO l_temp_count
88 FROM cn_attain_tiers
89 WHERE attain_schedule_id = p_attain_tier.attain_schedule_id
90 AND percent = p_attain_tier.percent
91 ;
92
93 IF l_temp_count > 0 THEN
94 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
95 FND_MESSAGE.SET_NAME ('CN' , 'CN_ASSIGN_CANT_SAME');
96 FND_MSG_PUB.Add;
97 END IF;
98 RAISE FND_API.G_EXC_ERROR ;
99 END IF;
100
101 IF p_attain_tier.percent < 0 THEN
102 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
103 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_INPUT');
104 FND_MSG_PUB.Add;
105 END IF;
106 RAISE FND_API.G_EXC_ERROR ;
107 END IF;
108
109 select cn_role_quota_formulas_s.nextval
110 into l_attain_tier_id
111 from dual;
112
113 CN_ATTAIN_TIERS_PKG.INSERT_ROW
114 (
115 X_ROWID => G_ROWID,
116 X_ATTAIN_TIER_ID => l_attain_tier_id,
117 X_ATTAIN_SCHEDULE_ID => p_attain_tier.ATTAIN_SCHEDULE_ID,
118 X_PERCENT => p_attain_tier.PERCENT,
119 X_ATTRIBUTE_CATEGORY => p_attain_tier.ATTRIBUTE_CATEGORY,
120 X_ATTRIBUTE1 => p_attain_tier.ATTRIBUTE1,
121 X_ATTRIBUTE2 => p_attain_tier.ATTRIBUTE2,
122 X_ATTRIBUTE3 => p_attain_tier.ATTRIBUTE3,
123 X_ATTRIBUTE4 => p_attain_tier.ATTRIBUTE4,
124 X_ATTRIBUTE5 => p_attain_tier.ATTRIBUTE5,
125 X_ATTRIBUTE6 => p_attain_tier.ATTRIBUTE6,
126 X_ATTRIBUTE7 => p_attain_tier.ATTRIBUTE7,
127 X_ATTRIBUTE8 => p_attain_tier.ATTRIBUTE8,
128 X_ATTRIBUTE9 => p_attain_tier.ATTRIBUTE9,
129 X_ATTRIBUTE10 => p_attain_tier.ATTRIBUTE10,
130 X_ATTRIBUTE11 => p_attain_tier.ATTRIBUTE11,
131 X_ATTRIBUTE12 => p_attain_tier.ATTRIBUTE12,
132 X_ATTRIBUTE13 => p_attain_tier.ATTRIBUTE13,
133 X_ATTRIBUTE14 => p_attain_tier.ATTRIBUTE14,
134 X_ATTRIBUTE15 => p_attain_tier.ATTRIBUTE15,
135 X_OBJECT_VERSION_NUMBER => 1,
136 X_CREATION_DATE => G_CREATION_DATE,
137 X_CREATED_BY => G_CREATED_BY,
138 X_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE,
139 X_LAST_UPDATED_BY => G_LAST_UPDATED_BY,
140 X_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN
141 );
142
143 -- End of API body.
144 -- Standard check of p_commit.
145 IF FND_API.To_Boolean( p_commit ) THEN
146 COMMIT WORK;
147 END IF;
148 -- Standard call to get message count and if count is 1, get message info.
149 FND_MSG_PUB.Count_And_Get
150 (p_count => x_msg_count ,
151 p_data => x_msg_data ,
152 p_encoded => FND_API.G_FALSE );
153 EXCEPTION
154 WHEN FND_API.G_EXC_ERROR THEN
155 ROLLBACK TO Create_Attain_Tier;
156 x_return_status := FND_API.G_RET_STS_ERROR ;
157 FND_MSG_PUB.Count_And_Get
158 (p_count => x_msg_count ,
159 p_data => x_msg_data ,
160 p_encoded => FND_API.G_FALSE );
161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
162 ROLLBACK TO Create_Attain_Tier;
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
164 FND_MSG_PUB.Count_And_Get
165 (p_count => x_msg_count ,
166 p_data => x_msg_data ,
167 p_encoded => FND_API.G_FALSE );
168 WHEN OTHERS THEN
169 ROLLBACK TO Create_Attain_Tier;
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
171 IF FND_MSG_PUB.Check_Msg_Level
172 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
173 THEN
174 FND_MSG_PUB.Add_Exc_Msg
175 (G_PKG_NAME ,
176 l_api_name );
177 END IF;
178 FND_MSG_PUB.Count_And_Get
179 (p_count => x_msg_count ,
180 p_data => x_msg_data ,
181 p_encoded => FND_API.G_FALSE );
182 END Create_Attain_Tier;
183
184
185 -- Start of comments
186 -- API name : Update_Attain_Tier
187 -- Type : Private.
188 -- Function :
189 -- Pre-reqs : None.
190 -- Parameters :
191 -- IN : p_api_version IN NUMBER Required
192 -- p_init_msg_list IN VARCHAR2 Optional
193 -- Default = FND_API.G_FALSE
194 -- p_commit IN VARCHAR2 Optional
195 -- Default = FND_API.G_FALSE
196 -- p_validation_level IN NUMBER Optional
197 -- Default = FND_API.G_VALID_LEVEL_FULL
198 -- p_attain_tier IN attain_tier_rec_type
199 -- OUT : x_return_status OUT VARCHAR2(1)
200 -- x_msg_count OUT NUMBER
201 -- x_msg_data OUT VARCHAR2(2000)
202 -- Version : Current version 1.0
203 -- Notes : Note text
204 --
205 -- End of comments
206 PROCEDURE Update_Attain_Tier
207 (p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
209 p_commit IN VARCHAR2 := FND_API.G_FALSE,
210 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
211 p_attain_tier IN attain_tier_rec_type,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2
215 ) IS
216
217 G_LAST_UPDATE_DATE DATE := Sysdate;
218 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
219 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
220 G_ROWID VARCHAR2(30);
221
222 l_api_name CONSTANT VARCHAR2(30) := 'Update_Attain_Tier';
223 l_api_version CONSTANT NUMBER := 1.0;
224
225 l_temp_count NUMBER;
226
227 CURSOR l_old_attain_tier_cr IS
228 SELECT *
229 FROM cn_attain_tiers
230 WHERE attain_tier_id = p_attain_tier.attain_tier_id;
231
232 l_old_attain_tier l_old_attain_tier_cr%ROWTYPE;
233 BEGIN
234 -- Standard Start of API savepoint
235 SAVEPOINT Update_Attain_Tier;
236 -- Standard call to check for call compatibility.
237 IF NOT FND_API.Compatible_API_Call
238 (l_api_version ,
239 p_api_version ,
240 l_api_name ,
241 G_PKG_NAME )
242 THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245 -- Initialize message list if p_init_msg_list is set to TRUE.
246 IF FND_API.to_Boolean( p_init_msg_list ) THEN
247 FND_MSG_PUB.initialize;
248 END IF;
249 -- Initialize API return status to success
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251 -- API body
252
253 IF ( p_attain_tier.attain_tier_id is NULL ) OR
254 ( p_attain_tier.attain_schedule_id is NULL ) OR
255 ( p_attain_tier.percent is NULL )
256 THEN
257 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
258 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
259 FND_MESSAGE.SET_TOKEN('INPUT_NAME',
260 'Attain Schedule, Attain Tier or Percentage Amount');
261 FND_MSG_PUB.Add;
262 END IF;
263 RAISE FND_API.G_EXC_ERROR ;
264 END IF;
265
266 -- same role quota category is not allowed to be
267 -- assigned twice
268 SELECT count(1)
269 INTO l_temp_count
270 FROM cn_attain_tiers
271 WHERE attain_schedule_id = p_attain_tier.attain_schedule_id
272 AND percent = p_attain_tier.percent
273 AND attain_tier_id <> p_attain_tier.attain_tier_id;
274
275 IF l_temp_count > 0 THEN
276 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
277 FND_MESSAGE.SET_NAME ('CN' , 'CN_ASSIGN_CANT_SAME');
278 FND_MSG_PUB.Add;
279 END IF;
280 RAISE FND_API.G_EXC_ERROR ;
281 END IF;
282
283 IF p_attain_tier.percent < 0 THEN
284 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
285 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_INPUT');
286 FND_MSG_PUB.Add;
287 END IF;
288 RAISE FND_API.G_EXC_ERROR ;
289 END IF;
290
291 OPEN l_old_attain_tier_cr;
292 FETCH l_old_attain_tier_cr INTO l_old_attain_tier;
293 CLOSE l_old_attain_tier_cr;
294
295 -- check object version number
296 IF l_old_attain_tier.object_version_number <>
297 p_attain_tier.object_version_number THEN
298 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
299 fnd_msg_pub.add;
300 raise fnd_api.g_exc_error;
301 END IF;
302
303 CN_ATTAIN_TIERS_PKG.UPDATE_ROW
304 (
305 X_ATTAIN_TIER_ID => p_attain_tier.ATTAIN_TIER_ID,
306 X_ATTAIN_SCHEDULE_ID => p_attain_tier.ATTAIN_SCHEDULE_ID,
307 X_PERCENT => p_attain_tier.PERCENT,
308 X_ATTRIBUTE_CATEGORY => p_attain_tier.ATTRIBUTE_CATEGORY,
309 X_ATTRIBUTE1 => p_attain_tier.ATTRIBUTE1,
310 X_ATTRIBUTE2 => p_attain_tier.ATTRIBUTE2,
311 X_ATTRIBUTE3 => p_attain_tier.ATTRIBUTE3,
312 X_ATTRIBUTE4 => p_attain_tier.ATTRIBUTE4,
313 X_ATTRIBUTE5 => p_attain_tier.ATTRIBUTE5,
314 X_ATTRIBUTE6 => p_attain_tier.ATTRIBUTE6,
315 X_ATTRIBUTE7 => p_attain_tier.ATTRIBUTE7,
316 X_ATTRIBUTE8 => p_attain_tier.ATTRIBUTE8,
317 X_ATTRIBUTE9 => p_attain_tier.ATTRIBUTE9,
318 X_ATTRIBUTE10 => p_attain_tier.ATTRIBUTE10,
319 X_ATTRIBUTE11 => p_attain_tier.ATTRIBUTE11,
320 X_ATTRIBUTE12 => p_attain_tier.ATTRIBUTE12,
321 X_ATTRIBUTE13 => p_attain_tier.ATTRIBUTE13,
322 X_ATTRIBUTE14 => p_attain_tier.ATTRIBUTE14,
323 X_ATTRIBUTE15 => p_attain_tier.ATTRIBUTE15,
324 X_OBJECT_VERSION_NUMBER => p_attain_tier.OBJECT_VERSION_NUMBER+1,
325 X_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE,
326 X_LAST_UPDATED_BY => G_LAST_UPDATED_BY,
327 X_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN
328 );
329
330 -- End of API body.
331 -- Standard check of p_commit.
332 IF FND_API.To_Boolean( p_commit ) THEN
333 COMMIT WORK;
334 END IF;
335 -- Standard call to get message count and if count is 1, get message info.
336 FND_MSG_PUB.Count_And_Get
337 (p_count => x_msg_count ,
338 p_data => x_msg_data ,
339 p_encoded => FND_API.G_FALSE );
340 EXCEPTION
341 WHEN FND_API.G_EXC_ERROR THEN
342 ROLLBACK TO Update_Attain_Tier;
343 x_return_status := FND_API.G_RET_STS_ERROR ;
344 FND_MSG_PUB.Count_And_Get
345 (p_count => x_msg_count ,
346 p_data => x_msg_data ,
347 p_encoded => FND_API.G_FALSE );
348 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
349 ROLLBACK TO Update_Attain_Tier;
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
351 FND_MSG_PUB.Count_And_Get
352 (p_count => x_msg_count ,
353 p_data => x_msg_data ,
354 p_encoded => FND_API.G_FALSE );
355 WHEN OTHERS THEN
356 ROLLBACK TO Update_Attain_Tier;
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
358 IF FND_MSG_PUB.Check_Msg_Level
359 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
360 THEN
361 FND_MSG_PUB.Add_Exc_Msg
362 (G_PKG_NAME ,
363 l_api_name );
364 END IF;
365 FND_MSG_PUB.Count_And_Get
366 (p_count => x_msg_count ,
367 p_data => x_msg_data ,
368 p_encoded => FND_API.G_FALSE );
369 END Update_Attain_Tier;
370
371
372 -- Start of comments
373 -- API name : Delete_Attain_Tier
374 -- Type : Private.
375 -- Function :
376 -- Pre-reqs : None.
377 -- Parameters :
378 -- IN : p_api_version IN NUMBER Required
379 -- p_init_msg_list IN VARCHAR2 Optional
380 -- Default = FND_API.G_FALSE
381 -- p_commit IN VARCHAR2 Optional
382 -- Default = FND_API.G_FALSE
383 -- p_validation_level IN NUMBER Optional
384 -- Default = FND_API.G_VALID_LEVEL_FULL
385 -- p_attain_tier IN attain_tier_rec_type
386 -- OUT : x_return_status OUT VARCHAR2(1)
387 -- x_msg_count OUT NUMBER
388 -- x_msg_data OUT VARCHAR2(2000)
389 -- Version : Current version 1.0
390 -- Notes : Note text
391 --
392 -- End of comments
393 PROCEDURE Delete_Attain_Tier
394 (p_api_version IN NUMBER,
395 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
396 p_commit IN VARCHAR2 := FND_API.G_FALSE,
397 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
398 p_attain_tier IN attain_tier_rec_type,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_msg_count OUT NOCOPY NUMBER,
401 x_msg_data OUT NOCOPY VARCHAR2
402 ) IS
403 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attain_Tier';
404 l_api_version CONSTANT NUMBER := 1.0;
405 BEGIN
406 -- Standard Start of API savepoint
407 SAVEPOINT Delete_Attain_Tier;
408 -- Standard call to check for call compatibility.
409 IF NOT FND_API.Compatible_API_Call
410 (l_api_version ,
411 p_api_version ,
412 l_api_name ,
413 G_PKG_NAME )
414 THEN
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 END IF;
417 -- Initialize message list if p_init_msg_list is set to TRUE.
418 IF FND_API.to_Boolean( p_init_msg_list ) THEN
419 FND_MSG_PUB.initialize;
420 END IF;
421 -- Initialize API return status to success
422 x_return_status := FND_API.G_RET_STS_SUCCESS;
423 -- API body
424
425 CN_ATTAIN_TIERS_PKG.DELETE_ROW(
426 X_ATTAIN_TIER_ID => p_attain_tier.ATTAIN_TIER_ID);
427
428 -- End of API body.
429 -- Standard check of p_commit.
430 IF FND_API.To_Boolean( p_commit ) THEN
431 COMMIT WORK;
432 END IF;
433 -- Standard call to get message count and if count is 1, get message info.
434 FND_MSG_PUB.Count_And_Get
435 (p_count => x_msg_count ,
436 p_data => x_msg_data ,
437 p_encoded => FND_API.G_FALSE );
438 EXCEPTION
439 WHEN FND_API.G_EXC_ERROR THEN
440 ROLLBACK TO Delete_Attain_Tier;
441 x_return_status := FND_API.G_RET_STS_ERROR ;
442 FND_MSG_PUB.Count_And_Get
443 (p_count => x_msg_count ,
444 p_data => x_msg_data ,
445 p_encoded => FND_API.G_FALSE );
446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447 ROLLBACK TO Delete_Attain_Tier;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
449 FND_MSG_PUB.Count_And_Get
450 (p_count => x_msg_count ,
451 p_data => x_msg_data ,
452 p_encoded => FND_API.G_FALSE );
453 WHEN OTHERS THEN
454 ROLLBACK TO Delete_Attain_Tier;
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
456 IF FND_MSG_PUB.Check_Msg_Level
457 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
458 THEN
459 FND_MSG_PUB.Add_Exc_Msg
460 (G_PKG_NAME ,
461 l_api_name );
462 END IF;
463 FND_MSG_PUB.Count_And_Get
464 (p_count => x_msg_count ,
465 p_data => x_msg_data ,
466 p_encoded => FND_API.G_FALSE );
467 END Delete_Attain_Tier;
468
469
470
471 -- Start of comments
472 -- API name : Get_Attain_Tier
473 -- Type : Private.
474 -- Function :
475 -- Pre-reqs : None.
476 -- Parameters :
477 -- IN : p_api_version IN NUMBER Required
478 -- p_init_msg_list IN VARCHAR2 Optional
479 -- Default = FND_API.G_FALSE
480 -- p_commit IN VARCHAR2 Optional
481 -- Default = FND_API.G_FALSE
482 -- p_validation_level IN NUMBER Optional
483 -- Default = FND_API.G_VALID_LEVEL_FULL
484 -- p_attain_schedule_id IN NUMBER
485 -- OUT : x_return_status OUT VARCHAR2(1)
486 -- x_msg_count OUT NUMBER
487 -- x_msg_data OUT VARCHAR2(2000)
488 -- x_attain_tier OUT attain_tier_tbl_type
489 -- Version : Current version 1.0
490 -- Notes : Note text
491 --
492 -- End of comments
493 PROCEDURE Get_Attain_Tier
494 (p_api_version IN NUMBER,
495 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
496 p_commit IN VARCHAR2 := FND_API.G_FALSE,
497 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
498 p_attain_schedule_id IN NUMBER,
499 x_attain_tier OUT NOCOPY attain_tier_tbl_type,
500 x_return_status OUT NOCOPY VARCHAR2,
501 x_msg_count OUT NOCOPY NUMBER,
502 x_msg_data OUT NOCOPY VARCHAR2
503 ) IS
504 l_api_name CONSTANT VARCHAR2(30) := 'Get_Attain_Tier';
505 l_api_version CONSTANT NUMBER := 1.0;
506 l_counter NUMBER;
507
508 CURSOR l_attain_tier_cr IS
509 SELECT *
510 FROM cn_attain_tiers
511 WHERE attain_schedule_id = p_attain_schedule_id
512 ORDER BY percent
513 ;
514
515 l_attain_tier l_attain_tier_cr%ROWTYPE;
516
517 BEGIN
518 -- Standard Start of API savepoint
519 SAVEPOINT Get_Attain_Tier;
520 -- Standard call to check for call compatibility.
521 IF NOT FND_API.Compatible_API_Call
522 (l_api_version ,
523 p_api_version ,
524 l_api_name ,
525 G_PKG_NAME )
526 THEN
527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
528 END IF;
529 -- Initialize message list if p_init_msg_list is set to TRUE.
530 IF FND_API.to_Boolean( p_init_msg_list ) THEN
531 FND_MSG_PUB.initialize;
532 END IF;
533 -- Initialize API return status to success
534 x_return_status := FND_API.G_RET_STS_SUCCESS;
535 -- API body
536
537 l_counter := 1;
538
539 OPEN l_attain_tier_cr;
540 LOOP
541 FETCH l_attain_tier_cr INTO l_attain_tier;
542 EXIT WHEN l_attain_tier_cr%NOTFOUND ;
543
544 x_attain_tier(l_counter).attain_tier_id :=
545 l_attain_tier.attain_tier_id;
546 x_attain_tier(l_counter).percent := l_attain_tier.percent;
547 x_attain_tier(l_counter).object_version_number :=
548 l_attain_tier.object_version_number;
549
550 l_counter := l_counter +1;
551
552 END LOOP;
553
554 IF l_attain_tier_cr%ROWCOUNT = 0 THEN
555 x_attain_tier := G_MISS_ATTAIN_TIER_REC_TB ;
556 END IF;
557
558 CLOSE l_attain_tier_cr;
559
560 -- End of API body.
561 -- Standard check of p_commit.
562 IF FND_API.To_Boolean( p_commit ) THEN
563 COMMIT WORK;
564 END IF;
565 -- Standard call to get message count and if count is 1, get message info.
566 FND_MSG_PUB.Count_And_Get
567 (p_count => x_msg_count ,
568 p_data => x_msg_data ,
569 p_encoded => FND_API.G_FALSE );
570 EXCEPTION
571 WHEN FND_API.G_EXC_ERROR THEN
572 ROLLBACK TO Get_Attain_Tier;
573 x_return_status := FND_API.G_RET_STS_ERROR ;
574 FND_MSG_PUB.Count_And_Get
575 (p_count => x_msg_count ,
576 p_data => x_msg_data ,
577 p_encoded => FND_API.G_FALSE );
578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
579 ROLLBACK TO Get_Attain_Tier;
580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
581 FND_MSG_PUB.Count_And_Get
582 (p_count => x_msg_count ,
583 p_data => x_msg_data ,
584 p_encoded => FND_API.G_FALSE );
585 WHEN OTHERS THEN
586 ROLLBACK TO Get_Attain_Tier;
587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
588 IF FND_MSG_PUB.Check_Msg_Level
589 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
590 THEN
591 FND_MSG_PUB.Add_Exc_Msg
592 (G_PKG_NAME ,
593 l_api_name );
594 END IF;
595 FND_MSG_PUB.Count_And_Get
596 (p_count => x_msg_count ,
597 p_data => x_msg_data ,
598 p_encoded => FND_API.G_FALSE );
599 END Get_Attain_Tier;
600
601 END CN_ATTAIN_TIER_PVT;