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