[Home] [Help]
PACKAGE BODY: APPS.CN_PLAN_TEXTS_PVT
Source
1 PACKAGE BODY CN_PLAN_TEXTS_PVT AS
2 /* $Header: cnvsptb.pls 115.16 2002/11/21 21:18:53 hlchen ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_PLAN_TEXTS_PVT';
5
6
7
8
9 CURSOR validate_role_state (c_role_id IN NUMBER) IS
10 SELECT 1
11 FROM cn_srp_role_dtls_v s, cn_role_quota_cates r
12 WHERE s.status not in ('PENDING','ACCEPTED')
13 AND s.role_id = r.role_id
14 AND r.role_model_id is NULL
15 AND s.role_model_id is NULL
16 AND s.role_id = c_role_id;
17
18 PROCEDURE validate_role(c_role_id IN NUMBER) IS
19 l_dummy NUMBER ;
20 BEGIN
21
22 OPEN validate_role_state(c_role_id) ;
23 FETCH validate_role_state INTO l_dummy;
24 IF (validate_role_state%found) THEN
25 CLOSE validate_role_state;
26 fnd_message.set_name('CN', 'CN_ROLE_DETAIL_ASGNED');
27 fnd_msg_pub.ADD;
28 RAISE fnd_api.g_exc_error;
29 END IF;
30 CLOSE validate_role_state;
31
32 END ;
33
34 -- Start of comments
35 -- API name : Create_Plan_Text
36 -- Type : Private.
37 -- Function :
38 -- Pre-reqs : None.
39 -- Parameters :
40 -- IN : p_api_version IN NUMBER Required
41 -- p_init_msg_list IN VARCHAR2 Optional
42 -- Default = FND_API.G_FALSE
43 -- p_commit IN VARCHAR2 Optional
44 -- Default = FND_API.G_FALSE
45 -- p_validation_level IN NUMBER Optional
46 -- Default = FND_API.G_VALID_LEVEL_FULL
47 -- p_plan_text IN plan_text_rec_type
48 -- OUT : x_return_status OUT VARCHAR2(1)
49 -- x_msg_count OUT NUMBER
50 -- x_msg_data OUT VARCHAR2(2000)
51 -- Version : Current version 1.0
52 -- Notes : Note text
53 --
54 -- End of comments
55 PROCEDURE Create_Plan_Text (
56 p_api_version IN NUMBER,
57 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
58 p_commit IN VARCHAR2 := FND_API.G_FALSE,
59 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
60 p_plan_text IN plan_text_rec_type,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2
64 ) IS
65
66 G_LAST_UPDATE_DATE DATE := Sysdate;
67 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
68 G_CREATION_DATE DATE := Sysdate;
69 G_CREATED_BY NUMBER := fnd_global.user_id;
70 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
71
72 l_api_name CONSTANT VARCHAR2(30) := 'Create_Plan_Text';
73 l_api_version CONSTANT NUMBER := 1.0;
74
75 l_plan_text_id NUMBER;
76 l_temp_count NUMBER;
77
78 BEGIN
79 -- Standard Start of API savepoint
80 SAVEPOINT Create_Plan_Text;
81 -- Standard call to check for call compatibility.
82 IF NOT FND_API.Compatible_API_Call
83 (l_api_version ,
84 p_api_version ,
85 l_api_name ,
86 G_PKG_NAME )
87 THEN
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90 -- Initialize message list if p_init_msg_list is set to TRUE.
91 IF FND_API.to_Boolean( p_init_msg_list ) THEN
92 FND_MSG_PUB.initialize;
93 END IF;
94 -- Initialize API return status to success
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96 -- API body
97
98 IF ( p_plan_text.role_id is NULL ) OR
99 ( p_plan_text.text_type is NULL )
100 THEN
101 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
102 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
103 FND_MESSAGE.SET_TOKEN('INPUT_NAME', 'Role or Text Type');
104 FND_MSG_PUB.Add;
105 END IF;
106 RAISE FND_API.G_EXC_ERROR ;
107 END IF;
108
109 -- CHECK THE ROLE SETUP
110
111
112 validate_role( p_plan_text.role_id ) ;
113
114
115 -- same plan text is not allowed to be
116 -- assigned twice
117 SELECT count(1)
118 INTO l_temp_count
119 FROM cn_plan_texts
120 WHERE role_id = p_plan_text.role_id
121 AND nvl(role_model_id, -1) = nvl(p_plan_text.role_model_id, -1)
122 AND text_type = p_plan_text.text_type
123 AND ( quota_category_id is NULL OR
124 quota_category_id = nvl(p_plan_text.quota_category_id,
125 quota_category_id)
126 )
127 AND ( sequence_id is NULL OR
128 sequence_id = nvl(p_plan_text.sequence_id, sequence_id)
129 );
130
131 IF l_temp_count > 0 THEN
132 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
133 FND_MESSAGE.SET_NAME ('CN' , 'CN_ASSIGN_CANT_SAME');
134 FND_MSG_PUB.Add;
135 END IF;
136 RAISE FND_API.G_EXC_ERROR ;
137 END IF;
138
139 SELECT cn_plan_texts_s.NEXTVAL INTO l_plan_text_id FROM DUAL;
140
141 CN_PLAN_TEXTS_PKG.Insert_Row
142 (
143 P_PLAN_TEXT_ID => l_plan_text_id,
144 P_ROLE_ID => p_plan_text.role_id,
145 P_SEQUENCE_ID => p_plan_text.sequence_id,
146 P_QUOTA_CATEGORY_ID => p_plan_text.quota_category_id,
147 P_TEXT_TYPE => p_plan_text.text_type,
148 P_TEXT => p_plan_text.text,
149 P_TEXT2 => p_plan_text.text2,
150 P_OBJECT_VERSION_NUMBER => 1,
151 P_ROLE_MODEL_ID => p_plan_text.role_model_id,
152 P_CREATION_DATE => G_CREATION_DATE,
153 P_CREATED_BY => G_CREATED_BY,
154 P_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE,
155 P_LAST_UPDATED_BY => G_LAST_UPDATED_BY,
156 P_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN
157 );
158
159 -- End of API body.
160 -- Standard check of p_commit.
161 IF FND_API.To_Boolean( p_commit ) THEN
162 COMMIT WORK;
163 END IF;
164 -- Standard call to get message count and if count is 1, get message info.
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 EXCEPTION
170 WHEN FND_API.G_EXC_ERROR THEN
171 ROLLBACK TO Create_Plan_Text;
172 x_return_status := FND_API.G_RET_STS_ERROR ;
173 FND_MSG_PUB.Count_And_Get
174 (p_count => x_msg_count ,
175 p_data => x_msg_data ,
176 p_encoded => FND_API.G_FALSE );
177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
178 ROLLBACK TO Create_Plan_Text;
179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
180 FND_MSG_PUB.Count_And_Get
181 (p_count => x_msg_count ,
182 p_data => x_msg_data ,
183 p_encoded => FND_API.G_FALSE );
184 WHEN OTHERS THEN
185 ROLLBACK TO Create_Plan_Text;
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
187 IF FND_MSG_PUB.Check_Msg_Level
188 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
189 THEN
190 FND_MSG_PUB.Add_Exc_Msg
191 (G_PKG_NAME ,
192 l_api_name );
193 END IF;
194 FND_MSG_PUB.Count_And_Get
195 (p_count => x_msg_count ,
196 p_data => x_msg_data ,
197 p_encoded => FND_API.G_FALSE );
198 END Create_Plan_Text;
199
200
201
202 -- Start of comments
203 -- API name : Update_Plan_Text
204 -- Type : Private.
205 -- Function :
206 -- Pre-reqs : None.
207 -- Parameters :
208 -- IN : p_api_version IN NUMBER Required
209 -- p_init_msg_list IN VARCHAR2 Optional
210 -- Default = FND_API.G_FALSE
211 -- p_commit IN VARCHAR2 Optional
212 -- Default = FND_API.G_FALSE
213 -- p_validation_level IN NUMBER Optional
214 -- Default = FND_API.G_VALID_LEVEL_FULL
215 -- p_plan_text IN plan_text_rec_type
216 -- OUT : x_return_status OUT VARCHAR2(1)
217 -- x_msg_count OUT NUMBER
218 -- x_msg_data OUT VARCHAR2(2000)
219 -- Version : Current version 1.0
220 -- Notes : Note text
221 --
222 -- End of comments
223 PROCEDURE Update_Plan_Text (
224 p_api_version IN NUMBER,
225 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
226 p_commit IN VARCHAR2 := FND_API.G_FALSE,
227 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
228 p_plan_text IN plan_text_rec_type,
229 x_return_status OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_msg_data OUT NOCOPY VARCHAR2
232 ) IS
233
234 G_LAST_UPDATE_DATE DATE := Sysdate;
235 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
236 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
237
238 l_api_name CONSTANT VARCHAR2(30) := 'Update_Plan_Text';
239 l_api_version CONSTANT NUMBER := 1.0;
240
241 CURSOR l_cr (P_PLAN_TEXT_ID NUMBER) IS
242 SELECT
243 object_version_number,
244 attribute_category,
245 attribute1,
246 attribute2,
247 attribute3,
248 attribute4,
249 attribute5,
250 attribute6,
251 attribute7,
252 attribute8,
253 attribute9,
254 attribute10,
255 attribute11,
256 attribute12,
257 attribute13,
258 attribute14,
259 attribute15
260 FROM cn_plan_texts
261 WHERE plan_text_id = P_PLAN_TEXT_ID;
262
263 l_plan_text l_cr%ROWTYPE;
264 l_temp_count NUMBER;
265 l_dummy NUMBER ;
266
267
268 BEGIN
269 -- Standard Start of API savepoint
270 SAVEPOINT Update_Plan_Text;
271 -- Standard call to check for call compatibility.
272 IF NOT FND_API.Compatible_API_Call
273 (l_api_version ,
274 p_api_version ,
275 l_api_name ,
276 G_PKG_NAME )
277 THEN
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 END IF;
280 -- Initialize message list if p_init_msg_list is set to TRUE.
281 IF FND_API.to_Boolean( p_init_msg_list ) THEN
282 FND_MSG_PUB.initialize;
283 END IF;
284 -- Initialize API return status to success
285 x_return_status := FND_API.G_RET_STS_SUCCESS;
286 -- API body
287
288 IF ( p_plan_text.role_id is NULL ) OR
289 ( p_plan_text.text_type is NULL )
290 THEN
291 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
292 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
293 FND_MESSAGE.SET_TOKEN('INPUT_NAME', 'Role or Text Type');
294 FND_MSG_PUB.Add;
295 END IF;
296 RAISE FND_API.G_EXC_ERROR ;
297 END IF;
298
299 /*
300 OPEN validate_role_state(p_plan_text.role_id) ;
301 FETCH validate_role_state INTO l_dummy;
302 IF (validate_role_state%found) THEN
303 CLOSE validate_role_state;
304 fnd_message.set_name('CN', 'CN_ROLE_DETAIL_ASGNED');
305 fnd_msg_pub.ADD;
306 RAISE fnd_api.g_exc_error;
307 END IF;
308 CLOSE validate_role_state;
309 */
310
311 validate_role (p_plan_text.role_id ) ;
312
313 -- same plan text is not allowed to be
314 -- assigned twice
315 SELECT count(1)
316 INTO l_temp_count
317 FROM cn_plan_texts
318 WHERE role_id = p_plan_text.role_id
319 AND nvl(role_model_id, -1) = nvl(p_plan_text.role_model_id, -1)
320 AND text_type = p_plan_text.text_type
321 AND ( quota_category_id is NULL OR
322 quota_category_id = nvl(p_plan_text.quota_category_id,
323 quota_category_id)
324 )
325 AND ( sequence_id is NULL OR
326 sequence_id = nvl(p_plan_text.sequence_id, sequence_id)
327 )
328 AND plan_text_id <> p_plan_text.plan_text_id
329 ;
330
331 IF l_temp_count > 0 THEN
332 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
333 FND_MESSAGE.SET_NAME ('CN' , 'CN_ASSIGN_CANT_SAME');
334 FND_MSG_PUB.Add;
335 END IF;
336 RAISE FND_API.G_EXC_ERROR ;
337 END IF;
338
339 OPEN l_cr(p_plan_text.plan_text_id);
340 FETCH l_cr into l_plan_text;
341 CLOSE l_cr;
342
343 -- check object version number
344 IF l_plan_text.object_version_number <>
345 p_plan_text.object_version_number THEN
346 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
347 fnd_msg_pub.add;
348 raise fnd_api.g_exc_error;
349 END IF;
350
351 CN_PLAN_TEXTS_PKG.Update_Row
352 (
353 P_PLAN_TEXT_ID => p_plan_text.plan_text_id,
354 P_ROLE_ID => p_plan_text.role_id,
355 P_SEQUENCE_ID => p_plan_text.sequence_id,
356 P_QUOTA_CATEGORY_ID => p_plan_text.quota_category_id,
357 P_TEXT_TYPE => p_plan_text.text_type,
358 P_TEXT => p_plan_text.text,
359 P_TEXT2 => p_plan_text.text2,
360 P_OBJECT_VERSION_NUMBER => p_plan_text.object_version_number + 1,
361 P_ROLE_MODEL_ID => p_plan_text.role_model_id,
362 P_ATTRIBUTE_CATEGORY => l_plan_text.attribute_category,
363 P_ATTRIBUTE1 => l_plan_text.attribute1,
364 P_ATTRIBUTE2 => l_plan_text.attribute2,
365 P_ATTRIBUTE3 => l_plan_text.attribute3,
366 P_ATTRIBUTE4 => l_plan_text.attribute4,
367 P_ATTRIBUTE5 => l_plan_text.attribute5,
368 P_ATTRIBUTE6 => l_plan_text.attribute6,
369 P_ATTRIBUTE7 => l_plan_text.attribute7,
370 P_ATTRIBUTE8 => l_plan_text.attribute8,
371 P_ATTRIBUTE9 => l_plan_text.attribute9,
372 P_ATTRIBUTE10 => l_plan_text.attribute10,
373 P_ATTRIBUTE11 => l_plan_text.attribute11,
374 P_ATTRIBUTE12 => l_plan_text.attribute12,
375 P_ATTRIBUTE13 => l_plan_text.attribute13,
376 P_ATTRIBUTE14 => l_plan_text.attribute14,
377 P_ATTRIBUTE15 => l_plan_text.attribute15,
378 P_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE,
379 P_LAST_UPDATED_BY => G_LAST_UPDATED_BY,
380 P_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN
381 );
382
383 -- End of API body.
384 -- Standard check of p_commit.
385 IF FND_API.To_Boolean( p_commit ) THEN
386 COMMIT WORK;
387 END IF;
388 -- Standard call to get message count and if count is 1, get message info.
389 FND_MSG_PUB.Count_And_Get
390 (p_count => x_msg_count ,
391 p_data => x_msg_data ,
392 p_encoded => FND_API.G_FALSE );
393 EXCEPTION
394 WHEN FND_API.G_EXC_ERROR THEN
395 ROLLBACK TO Update_Plan_Text;
396 x_return_status := FND_API.G_RET_STS_ERROR ;
397 FND_MSG_PUB.Count_And_Get
398 (p_count => x_msg_count ,
399 p_data => x_msg_data ,
400 p_encoded => FND_API.G_FALSE );
401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
402 ROLLBACK TO Update_Plan_Text;
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
404 FND_MSG_PUB.Count_And_Get
405 (p_count => x_msg_count ,
406 p_data => x_msg_data ,
407 p_encoded => FND_API.G_FALSE );
408 WHEN OTHERS THEN
409 ROLLBACK TO Update_Plan_Text;
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
411 IF FND_MSG_PUB.Check_Msg_Level
412 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
413 THEN
414 FND_MSG_PUB.Add_Exc_Msg
415 (G_PKG_NAME ,
416 l_api_name );
417 END IF;
418 FND_MSG_PUB.Count_And_Get
419 (p_count => x_msg_count ,
420 p_data => x_msg_data ,
421 p_encoded => FND_API.G_FALSE );
422 END Update_Plan_Text;
423
424
425
426
427 -- Start of comments
428 -- API name : Delete_Plan_Text
429 -- Type : Private.
430 -- Function :
431 -- Pre-reqs : None.
432 -- Parameters :
433 -- IN : p_api_version IN NUMBER Required
434 -- p_init_msg_list IN VARCHAR2 Optional
435 -- Default = FND_API.G_FALSE
436 -- p_commit IN VARCHAR2 Optional
437 -- Default = FND_API.G_FALSE
438 -- p_validation_level IN NUMBER Optional
439 -- Default = FND_API.G_VALID_LEVEL_FULL
440 -- p_plan_text IN plan_text_rec_type
441 -- OUT : x_return_status OUT VARCHAR2(1)
442 -- x_msg_count OUT NUMBER
443 -- x_msg_data OUT VARCHAR2(2000)
444 -- Version : Current version 1.0
445 -- Notes : Note text
446 --
447 -- End of comments
448 PROCEDURE Delete_Plan_Text (
449 p_api_version IN NUMBER,
450 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
451 p_commit IN VARCHAR2 := FND_API.G_FALSE,
452 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
453 p_plan_text IN plan_text_rec_type,
454 x_return_status OUT NOCOPY VARCHAR2,
455 x_msg_count OUT NOCOPY NUMBER,
456 x_msg_data OUT NOCOPY VARCHAR2
457 ) IS
458
459 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Plan_Text';
460 l_api_version CONSTANT NUMBER := 1.0;
461
462 BEGIN
463 -- Standard Start of API savepoint
464 SAVEPOINT Delete_Plan_Text;
465 -- Standard call to check for call compatibility.
466 IF NOT FND_API.Compatible_API_Call
467 (l_api_version ,
468 p_api_version ,
469 l_api_name ,
470 G_PKG_NAME )
471 THEN
472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
473 END IF;
474 -- Initialize message list if p_init_msg_list is set to TRUE.
475 IF FND_API.to_Boolean( p_init_msg_list ) THEN
476 FND_MSG_PUB.initialize;
477 END IF;
478 -- Initialize API return status to success
479 x_return_status := FND_API.G_RET_STS_SUCCESS;
480 -- API body
481
482 CN_PLAN_TEXTS_PKG.Delete_Row(p_plan_text.plan_text_id);
483
484 -- End of API body.
485 -- Standard check of p_commit.
486 IF FND_API.To_Boolean( p_commit ) THEN
487 COMMIT WORK;
488 END IF;
489 -- Standard call to get message count and if count is 1, get message info.
490 FND_MSG_PUB.Count_And_Get
491 (p_count => x_msg_count ,
492 p_data => x_msg_data ,
493 p_encoded => FND_API.G_FALSE );
494 EXCEPTION
495 WHEN FND_API.G_EXC_ERROR THEN
496 ROLLBACK TO Delete_Plan_Text;
497 x_return_status := FND_API.G_RET_STS_ERROR ;
498 FND_MSG_PUB.Count_And_Get
499 (p_count => x_msg_count ,
500 p_data => x_msg_data ,
501 p_encoded => FND_API.G_FALSE );
502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
503 ROLLBACK TO Delete_Plan_Text;
504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
505 FND_MSG_PUB.Count_And_Get
506 (p_count => x_msg_count ,
507 p_data => x_msg_data ,
508 p_encoded => FND_API.G_FALSE );
509 WHEN OTHERS THEN
510 ROLLBACK TO Delete_Plan_Text;
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
512 IF FND_MSG_PUB.Check_Msg_Level
513 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
514 THEN
515 FND_MSG_PUB.Add_Exc_Msg
516 (G_PKG_NAME ,
517 l_api_name );
518 END IF;
519 FND_MSG_PUB.Count_And_Get
520 (p_count => x_msg_count ,
521 p_data => x_msg_data ,
522 p_encoded => FND_API.G_FALSE );
523 END Delete_Plan_Text;
524
525
526
527 -- Start of comments
528 -- API name : Get_Plan_Texts
529 -- Type : Private.
530 -- Function :
531 -- Pre-reqs : None.
532 -- Parameters :
533 -- IN : p_api_version IN NUMBER Required
534 -- p_init_msg_list IN VARCHAR2 Optional
535 -- Default = FND_API.G_FALSE
536 -- p_commit IN VARCHAR2 Optional
537 -- Default = FND_API.G_FALSE
538 -- p_validation_level IN NUMBER Optional
539 -- Default = FND_API.G_VALID_LEVEL_FULL
540 -- p_role_id IN NUMBER
541 -- p_role_model_id IN NUMBER
542 -- OUT : x_return_status OUT VARCHAR2(1)
543 -- x_msg_count OUT NUMBER
544 -- x_msg_data OUT VARCHAR2(2000)
545 -- x_plan_texts OUT plan_text_tbl_type
546 -- x_updatable OUT VARCHAR2(1)
547 -- Version : Current version 1.0
548 -- Notes : Note text
549 --
550 -- End of comments
551 PROCEDURE Get_Plan_Texts (
552 p_api_version IN NUMBER,
553 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
554 p_commit IN VARCHAR2 := FND_API.G_FALSE,
555 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
556 p_role_id IN NUMBER,
557 p_role_model_id IN NUMBER,
558 x_plan_texts OUT NOCOPY plan_text_tbl_type,
559 x_updatable OUT NOCOPY VARCHAR2,
560 x_return_status OUT NOCOPY VARCHAR2,
561 x_msg_count OUT NOCOPY NUMBER,
562 x_msg_data OUT NOCOPY VARCHAR2
563 ) IS
564
565 l_api_name CONSTANT VARCHAR2(30) := 'Get_Plan_Texts';
566 l_api_version CONSTANT NUMBER := 1.0;
567
568 l_ctr NUMBER;
569
570 CURSOR l_spt_cr (P_ROLE_ID NUMBER) IS
571 SELECT srp_id
572 FROM cn_srp_role_dtls_v
573 WHERE role_id = P_ROLE_ID
574 AND role_model_id is NULL
575 AND (status <> 'PENDING' or non_std_flag = 'Y' );
576
577 CURSOR l_pts_cr (C_ROLE_ID IN NUMBER,
578 C_ROLE_MODEL_ID IN NUMBER) IS
579 SELECT
580 plan_text_id,
581 role_id,
582 role_model_id,
583 sequence_id,
584 quota_category_id,
585 text_type,
586 text,
587 text2,
588 object_version_number
589 FROM cn_plan_texts
590 WHERE role_id = c_role_id
591 AND nvl(role_model_id, -1) = nvl(c_role_model_id, -1)
592 ORDER BY sequence_id;
593
594 l_plan_text l_pts_cr%ROWTYPE;
595
596 l_temp_con_title cn_sf_repositories.CONTRACT_TITLE%TYPE := NULL;
597 l_temp_term_con cn_sf_repositories.TERMS_AND_CONDITIONS%TYPE := NULL;
598 l_temp_club cn_sf_repositories.CLUB_QUAL_TEXT%TYPE := NULL;
599 l_temp_app_name cn_sf_repositories.APPROVER_NAME%TYPE := NULL;
600 l_temp_app_title cn_sf_repositories.APPROVER_TITLE%TYPE := NULL;
601 l_temp_app_org cn_sf_repositories.APPROVER_ORG_NAME%TYPE := NULL;
602
603 BEGIN
604 -- Standard Start of API savepoint
605 SAVEPOINT Get_Plan_Texts;
606 -- Standard call to check for call compatibility.
607 IF NOT FND_API.Compatible_API_Call
608 (l_api_version ,
609 p_api_version ,
610 l_api_name ,
611 G_PKG_NAME )
612 THEN
613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
614 END IF;
615 -- Initialize message list if p_init_msg_list is set to TRUE.
616 IF FND_API.to_Boolean( p_init_msg_list ) THEN
617 FND_MSG_PUB.initialize;
618 END IF;
619 -- Initialize API return status to success
620 x_return_status := FND_API.G_RET_STS_SUCCESS;
621 -- API body
622
623 BEGIN
624 SELECT contract_title, terms_and_conditions,
625 club_qual_text, approver_name,
626 approver_title, approver_org_name
627 INTO l_temp_con_title, l_temp_term_con,
628 l_temp_club, l_temp_app_name,
629 l_temp_app_title, l_temp_app_org
630 FROM cn_sf_repositories;
631 EXCEPTION
632 WHEN No_Data_Found THEN
633 null;
634 END;
635
636 l_ctr := 1;
637
638 OPEN l_pts_cr(p_role_id, p_role_model_id);
639 LOOP
640 FETCH l_pts_cr INTO l_plan_text;
641 EXIT WHEN l_pts_cr%NOTFOUND ;
642
643 x_plan_texts(l_ctr).plan_text_id := l_plan_text.plan_text_id;
644 x_plan_texts(l_ctr).role_id := l_plan_text.role_id;
645 x_plan_texts(l_ctr).role_model_id := l_plan_text.role_model_id;
646 x_plan_texts(l_ctr).sequence_id := l_plan_text.sequence_id;
647 x_plan_texts(l_ctr).quota_category_id := l_plan_text.quota_category_id;
648 x_plan_texts(l_ctr).text_type := l_plan_text.text_type;
649 x_plan_texts(l_ctr).text := l_plan_text.text;
650 x_plan_texts(l_ctr).text2 := l_plan_text.text2;
651 x_plan_texts(l_ctr).object_version_number
652 := l_plan_text.object_version_number;
653
654 l_ctr := l_ctr + 1;
655 END LOOP;
656
657 IF l_pts_cr%ROWCOUNT = 0 THEN
658 x_plan_texts := G_MISS_PLAN_TEXT_TBL;
659 END IF;
660
661 CLOSE l_pts_cr;
662
663 IF l_ctr = 1 THEN
664 x_plan_texts(1).role_id := p_role_id;
665 x_plan_texts(1).role_model_id := p_role_model_id;
666 x_plan_texts(1).text_type := 'PLAN_TITLE_TEXT';
667 x_plan_texts(1).text := l_temp_con_title;
668
669 x_plan_texts(2).role_id := p_role_id;
670 x_plan_texts(2).role_model_id := p_role_model_id;
671 x_plan_texts(2).text_type := 'PLAN_TC_TEXT';
672 x_plan_texts(2).text := l_temp_term_con;
673
674 x_plan_texts(3).role_id := p_role_id;
675 x_plan_texts(3).role_model_id := p_role_model_id;
676 x_plan_texts(3).text_type := 'PLAN_CLUB_TEXT';
677 x_plan_texts(3).text := l_temp_club;
678
679 x_plan_texts(4).role_id := p_role_id;
680 x_plan_texts(4).role_model_id := p_role_model_id;
681 x_plan_texts(4).text_type := 'PLAN_APPR_NAME';
682 x_plan_texts(4).text := l_temp_app_name;
683
684 x_plan_texts(5).role_id := p_role_id;
685 x_plan_texts(5).role_model_id := p_role_model_id;
686 x_plan_texts(5).text_type := 'PLAN_APPR_TITLE';
687 x_plan_texts(5).text := l_temp_app_title;
688
689 x_plan_texts(6).role_id := p_role_id;
690 x_plan_texts(6).role_model_id := p_role_model_id;
691 x_plan_texts(6).text_type := 'PLAN_APPR_ORG_NAME';
692 x_plan_texts(6).text := l_temp_app_org;
693 END IF;
694
695 -- check updateable or not
696 OPEN l_spt_cr(p_role_id);
697
698 FETCH l_spt_cr INTO l_ctr;
699 IF l_spt_cr%NOTFOUND THEN
700 x_updatable := 'T';
701 ELSE
702 x_updatable := 'F';
703 END IF;
704
705 CLOSE l_spt_cr;
706
707 -- End of API body.
708 -- Standard check of p_commit.
709 IF FND_API.To_Boolean( p_commit ) THEN
710 COMMIT WORK;
711 END IF;
712 -- Standard call to get message count and if count is 1, get message info.
713 FND_MSG_PUB.Count_And_Get
714 (p_count => x_msg_count ,
715 p_data => x_msg_data ,
716 p_encoded => FND_API.G_FALSE );
717 EXCEPTION
718 WHEN FND_API.G_EXC_ERROR THEN
719 ROLLBACK TO Get_Plan_Texts;
720 x_return_status := FND_API.G_RET_STS_ERROR ;
721 FND_MSG_PUB.Count_And_Get
722 (p_count => x_msg_count ,
723 p_data => x_msg_data ,
724 p_encoded => FND_API.G_FALSE );
725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
726 ROLLBACK TO Get_Plan_Texts;
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
728 FND_MSG_PUB.Count_And_Get
729 (p_count => x_msg_count ,
730 p_data => x_msg_data ,
731 p_encoded => FND_API.G_FALSE );
732 WHEN OTHERS THEN
733 ROLLBACK TO Get_Plan_Texts;
734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
735 IF FND_MSG_PUB.Check_Msg_Level
736 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
737 THEN
738 FND_MSG_PUB.Add_Exc_Msg
739 (G_PKG_NAME ,
740 l_api_name );
741 END IF;
742 FND_MSG_PUB.Count_And_Get
743 (p_count => x_msg_count ,
744 p_data => x_msg_data ,
745 p_encoded => FND_API.G_FALSE );
746 END Get_Plan_Texts;
747
748
749 -- Start of comments
750 -- API name : Get_Fixed_Quota_Cates
751 -- Type : Private.
752 -- Function :
753 -- Pre-reqs : None.
754 -- Parameters :
755 -- IN : p_api_version IN NUMBER Required
756 -- p_init_msg_list IN VARCHAR2 Optional
757 -- Default = FND_API.G_FALSE
758 -- p_commit IN VARCHAR2 Optional
759 -- Default = FND_API.G_FALSE
760 -- p_validation_level IN NUMBER Optional
761 -- Default = FND_API.G_VALID_LEVEL_FULL
762 -- p_role_id IN NUMBER
763 -- p_role_model_id IN NUMBER
764 -- OUT : x_return_status OUT VARCHAR2(1)
765 -- x_msg_count OUT NUMBER
766 -- x_msg_data OUT VARCHAR2(2000)
767 -- x_quota_cates OUT quota_cate_tbl_type
768 -- Version : Current version 1.0
769 -- Notes : Note text
770 --
771 -- End of comments
772 PROCEDURE Get_Fixed_Quota_Cates (
773 p_api_version IN NUMBER,
774 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
775 p_commit IN VARCHAR2 := FND_API.G_FALSE,
776 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
777 p_role_id IN NUMBER,
778 p_role_model_id IN NUMBER,
779 x_quota_cates OUT NOCOPY quota_cate_tbl_type,
780 x_return_status OUT NOCOPY VARCHAR2,
781 x_msg_count OUT NOCOPY NUMBER,
782 x_msg_data OUT NOCOPY VARCHAR2
783 ) IS
784
785 l_api_name CONSTANT VARCHAR2(30) := 'Get_Fixed_Quota_Cates';
786 l_api_version CONSTANT NUMBER := 1.0;
787
788 l_ctr NUMBER;
789
790 CURSOR l_qcs_cr (C_ROLE_ID IN NUMBER,
791 C_ROLE_MODEL_ID IN NUMBER) IS
792 SELECT qc.quota_category_id quota_cate_id,
793 qc.name quota_name
794 FROM cn_quota_categories qc,
795 cn_role_quota_cates pqc
796 WHERE pqc.role_id = c_role_id
797 AND qc.quota_category_id = pqc.quota_category_id
798 AND qc.type = 'FIXED'
799 AND nvl(pqc.role_model_id, -1) = nvl(c_role_model_id, -1)
800 ORDER BY quota_cate_id;
801
802 l_quota_cate l_qcs_cr%ROWTYPE;
803
804 BEGIN
805 -- Standard Start of API savepoint
806 SAVEPOINT Get_Fixed_Quota_Cates;
807 -- Standard call to check for call compatibility.
808 IF NOT FND_API.Compatible_API_Call
809 (l_api_version ,
810 p_api_version ,
811 l_api_name ,
812 G_PKG_NAME )
813 THEN
814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
815 END IF;
816 -- Initialize message list if p_init_msg_list is set to TRUE.
817 IF FND_API.to_Boolean( p_init_msg_list ) THEN
818 FND_MSG_PUB.initialize;
819 END IF;
820 -- Initialize API return status to success
821 x_return_status := FND_API.G_RET_STS_SUCCESS;
822 -- API body
823
824 l_ctr := 1;
825
826 OPEN l_qcs_cr(p_role_id, p_role_model_id);
827 LOOP
828 FETCH l_qcs_cr INTO l_quota_cate;
829 EXIT WHEN l_qcs_cr%NOTFOUND ;
830
831 x_quota_cates(l_ctr).quota_cate_id := l_quota_cate.quota_cate_id;
832 x_quota_cates(l_ctr).quota_name := l_quota_cate.quota_name;
833
834 l_ctr := l_ctr + 1;
835 END LOOP;
836
837 IF l_qcs_cr%ROWCOUNT = 0 THEN
838 x_quota_cates := G_MISS_QUOTA_CATE_TBL;
839 END IF;
840
841 CLOSE l_qcs_cr;
842
843 -- End of API body.
844 -- Standard check of p_commit.
845 IF FND_API.To_Boolean( p_commit ) THEN
846 COMMIT WORK;
847 END IF;
848 -- Standard call to get message count and if count is 1, get message info.
849 FND_MSG_PUB.Count_And_Get
850 (p_count => x_msg_count ,
851 p_data => x_msg_data ,
852 p_encoded => FND_API.G_FALSE );
853 EXCEPTION
854 WHEN FND_API.G_EXC_ERROR THEN
855 ROLLBACK TO Get_Fixed_Quota_Cates;
856 x_return_status := FND_API.G_RET_STS_ERROR ;
857 FND_MSG_PUB.Count_And_Get
858 (p_count => x_msg_count ,
859 p_data => x_msg_data ,
860 p_encoded => FND_API.G_FALSE );
861 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
862 ROLLBACK TO Get_Fixed_Quota_Cates;
863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
864 FND_MSG_PUB.Count_And_Get
865 (p_count => x_msg_count ,
866 p_data => x_msg_data ,
867 p_encoded => FND_API.G_FALSE );
868 WHEN OTHERS THEN
869 ROLLBACK TO Get_Fixed_Quota_Cates;
870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
871 IF FND_MSG_PUB.Check_Msg_Level
872 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
873 THEN
874 FND_MSG_PUB.Add_Exc_Msg
875 (G_PKG_NAME ,
876 l_api_name );
877 END IF;
878 FND_MSG_PUB.Count_And_Get
879 (p_count => x_msg_count ,
880 p_data => x_msg_data ,
881 p_encoded => FND_API.G_FALSE );
882 END Get_Fixed_Quota_Cates;
883
884 -- Start of comments
885 -- API name : Get_Var_Quota_Cates
886 -- Type : Private.
887 -- Function :
888 -- Pre-reqs : None.
889 -- Parameters :
890 -- IN : p_api_version IN NUMBER Required
891 -- p_init_msg_list IN VARCHAR2 Optional
892 -- Default = FND_API.G_FALSE
893 -- p_commit IN VARCHAR2 Optional
894 -- Default = FND_API.G_FALSE
895 -- p_validation_level IN NUMBER Optional
896 -- Default = FND_API.G_VALID_LEVEL_FULL
897 -- p_role_id IN NUMBER
898 -- p_role_model_id IN NUMBER
899 -- OUT : x_return_status OUT VARCHAR2(1)
900 -- x_msg_count OUT NUMBER
901 -- x_msg_data OUT VARCHAR2(2000)
902 -- x_quota_cates OUT quota_cate_tbl_type
903 -- Version : Current version 1.0
904 -- Notes : Note text
905 --
906 -- End of comments
907 PROCEDURE Get_Var_Quota_Cates (
908 p_api_version IN NUMBER,
909 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
910 p_commit IN VARCHAR2 := FND_API.G_FALSE,
911 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
912 p_role_id IN NUMBER,
913 p_role_model_id IN NUMBER,
914 x_quota_cates OUT NOCOPY quota_cate_tbl_type,
915 x_return_status OUT NOCOPY VARCHAR2,
916 x_msg_count OUT NOCOPY NUMBER,
917 x_msg_data OUT NOCOPY VARCHAR2
918 ) IS
919
920 l_api_name CONSTANT VARCHAR2(30) := 'Get_Var_Quota_Cates';
921 l_api_version CONSTANT NUMBER := 1.0;
922
923 l_ctr NUMBER;
924
925 CURSOR l_qcs_cr(c_role_id IN NUMBER,
926 c_role_model_id IN NUMBER) IS
927 SELECT qc.quota_category_id quota_cate_id,
928 qc.name quota_name
929 FROM cn_quota_categories qc,
930 cn_role_quota_cates pqc
931 WHERE pqc.role_id = c_role_id
932 and (NOT pqc.rate_schedule_id IS NULL)
933 and qc.quota_category_id = pqc.quota_category_id
934 and qc.type = 'VAR_QUOTA'
935 and nvl(pqc.role_model_id, -1) = nvl(c_role_model_id, -1)
936 ORDER BY quota_cate_id;
937
938 l_quota_cate l_qcs_cr%ROWTYPE;
939
940 BEGIN
941 -- Standard Start of API savepoint
942 SAVEPOINT Get_Var_Quota_Cates;
943 -- Standard call to check for call compatibility.
944 IF NOT FND_API.Compatible_API_Call
945 (l_api_version ,
946 p_api_version ,
947 l_api_name ,
948 G_PKG_NAME )
949 THEN
950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
951 END IF;
952 -- Initialize message list if p_init_msg_list is set to TRUE.
953 IF FND_API.to_Boolean( p_init_msg_list ) THEN
954 FND_MSG_PUB.initialize;
955 END IF;
956 -- Initialize API return status to success
957 x_return_status := FND_API.G_RET_STS_SUCCESS;
958 -- API body
959
960 l_ctr := 1;
961
962 OPEN l_qcs_cr(p_role_id, p_role_model_id);
963 LOOP
964 FETCH l_qcs_cr INTO l_quota_cate;
965 EXIT WHEN l_qcs_cr%NOTFOUND ;
966
967 x_quota_cates(l_ctr).quota_cate_id := l_quota_cate.quota_cate_id;
968
969 IF l_quota_cate.quota_cate_id = -1000 THEN
970 SELECT meaning
971 INTO x_quota_cates(l_ctr).quota_name
972 FROM cn_lookups
973 WHERE lookup_type = 'QUOTA_CATEGORY'
974 AND lookup_code = 'TOTAL_QUOTA';
975 ELSE
976 x_quota_cates(l_ctr).quota_name := l_quota_cate.quota_name;
977 END IF;
978
979 l_ctr := l_ctr + 1;
980 END LOOP;
981
982 IF l_qcs_cr%ROWCOUNT = 0 THEN
983 x_quota_cates := G_MISS_QUOTA_CATE_TBL;
984 END IF;
985
986 CLOSE l_qcs_cr;
987
988 -- End of API body.
989 -- Standard check of p_commit.
990 IF FND_API.To_Boolean( p_commit ) THEN
991 COMMIT WORK;
992 END IF;
993 -- Standard call to get message count and if count is 1, get message info.
994 FND_MSG_PUB.Count_And_Get
995 (p_count => x_msg_count ,
996 p_data => x_msg_data ,
997 p_encoded => FND_API.G_FALSE );
998 EXCEPTION
999 WHEN FND_API.G_EXC_ERROR THEN
1000 ROLLBACK TO Get_Var_Quota_Cates;
1001 x_return_status := FND_API.G_RET_STS_ERROR ;
1002 FND_MSG_PUB.Count_And_Get
1003 (p_count => x_msg_count ,
1004 p_data => x_msg_data ,
1005 p_encoded => FND_API.G_FALSE );
1006 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1007 ROLLBACK TO Get_Var_Quota_Cates;
1008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1009 FND_MSG_PUB.Count_And_Get
1010 (p_count => x_msg_count ,
1011 p_data => x_msg_data ,
1012 p_encoded => FND_API.G_FALSE );
1013 WHEN OTHERS THEN
1014 ROLLBACK TO Get_Var_Quota_Cates;
1015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1016 IF FND_MSG_PUB.Check_Msg_Level
1017 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1018 THEN
1019 FND_MSG_PUB.Add_Exc_Msg
1020 (G_PKG_NAME ,
1021 l_api_name );
1022 END IF;
1023 FND_MSG_PUB.Count_And_Get
1024 (p_count => x_msg_count ,
1025 p_data => x_msg_data ,
1026 p_encoded => FND_API.G_FALSE );
1027 END Get_Var_Quota_Cates;
1028
1029
1030 -- Start of comments
1031 -- API name : Get_Quota_Cates
1032 -- Type : Private.
1033 -- Function :
1034 -- Pre-reqs : None.
1035 -- Parameters :
1036 -- IN : p_api_version IN NUMBER Required
1037 -- p_init_msg_list IN VARCHAR2 Optional
1038 -- Default = FND_API.G_FALSE
1039 -- p_commit IN VARCHAR2 Optional
1040 -- Default = FND_API.G_FALSE
1041 -- p_validation_level IN NUMBER Optional
1042 -- Default = FND_API.G_VALID_LEVEL_FULL
1043 -- p_role_id IN NUMBER
1044 -- p_role_model_id IN NUMBER
1045 -- OUT : x_return_status OUT VARCHAR2(1)
1046 -- x_msg_count OUT NUMBER
1047 -- x_msg_data OUT VARCHAR2(2000)
1048 -- x_quota_cates OUT quota_cate_tbl_type
1049 -- Version : Current version 1.0
1050 -- Notes : Note text
1051 --
1052 -- End of comments
1053 PROCEDURE Get_Quota_Cates (
1054 p_api_version IN NUMBER,
1055 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1056 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1057 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1058 p_role_id IN NUMBER,
1059 p_role_model_id IN NUMBER,
1060 p_quota_cate_type IN VARCHAR2,
1061 x_quota_cates OUT NOCOPY quota_cate_tbl_type,
1062 x_return_status OUT NOCOPY VARCHAR2,
1063 x_msg_count OUT NOCOPY NUMBER,
1064 x_msg_data OUT NOCOPY VARCHAR2
1065 ) IS
1066
1067 l_api_name CONSTANT VARCHAR2(30) := 'Get_Quota_Cates';
1068 l_api_version CONSTANT NUMBER := 1.0;
1069
1070 l_ctr NUMBER;
1071
1072 CURSOR l_qcs_cr(c_role_id IN NUMBER,
1073 c_role_model_id IN NUMBER,
1074 c_quota_cate_type IN VARCHAR) IS
1075 SELECT qc.quota_category_id quota_cate_id,
1076 qc.name quota_name
1077 FROM cn_quota_categories qc,
1078 cn_role_quota_cates pqc
1079 WHERE pqc.role_id = c_role_id
1080 and (NOT pqc.rate_schedule_id IS NULL)
1081 and qc.quota_category_id = pqc.quota_category_id
1082 and qc.type = c_quota_cate_type
1083 and nvl(pqc.role_model_id, -1) = nvl(c_role_model_id, -1)
1084 ORDER BY quota_cate_id;
1085
1086 l_quota_cate l_qcs_cr%ROWTYPE;
1087 l_quota_type VARCHAR2(2000) ;
1088
1089 BEGIN
1090 -- Standard Start of API savepoint
1091 SAVEPOINT Get_Quota_Cates;
1092 -- Standard call to check for call compatibility.
1093 IF NOT FND_API.Compatible_API_Call
1094 (l_api_version ,
1095 p_api_version ,
1096 l_api_name ,
1097 G_PKG_NAME )
1098 THEN
1099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100 END IF;
1101 -- Initialize message list if p_init_msg_list is set to TRUE.
1102 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1103 FND_MSG_PUB.initialize;
1104 END IF;
1105 -- Initialize API return status to success
1106 x_return_status := FND_API.G_RET_STS_SUCCESS;
1107 -- API body
1108
1109 l_ctr := 1;
1110
1111 l_quota_type := p_quota_cate_type ;
1112
1113 OPEN l_qcs_cr(p_role_id, p_role_model_id,l_quota_type);
1114 LOOP
1115 FETCH l_qcs_cr INTO l_quota_cate;
1116 EXIT WHEN l_qcs_cr%NOTFOUND ;
1117
1118 x_quota_cates(l_ctr).quota_cate_id := l_quota_cate.quota_cate_id;
1119
1120 IF l_quota_cate.quota_cate_id = -1000 THEN
1121 SELECT meaning
1122 INTO x_quota_cates(l_ctr).quota_name
1123 FROM cn_lookups
1124 WHERE lookup_type = 'QUOTA_CATEGORY'
1125 AND lookup_code = 'TOTAL_QUOTA';
1126 ELSE
1127 x_quota_cates(l_ctr).quota_name := l_quota_cate.quota_name;
1128 END IF;
1129
1130 l_ctr := l_ctr + 1;
1131 END LOOP;
1132
1133 IF l_qcs_cr%ROWCOUNT = 0 THEN
1134 x_quota_cates := G_MISS_QUOTA_CATE_TBL;
1135 END IF;
1136
1137 CLOSE l_qcs_cr;
1138
1139 -- End of API body.
1140 -- Standard check of p_commit.
1141 IF FND_API.To_Boolean( p_commit ) THEN
1142 COMMIT WORK;
1143 END IF;
1144 -- Standard call to get message count and if count is 1, get message info.
1145 FND_MSG_PUB.Count_And_Get
1146 (p_count => x_msg_count ,
1147 p_data => x_msg_data ,
1148 p_encoded => FND_API.G_FALSE );
1149 EXCEPTION
1150 WHEN FND_API.G_EXC_ERROR THEN
1151 ROLLBACK TO Get_Quota_Cates;
1152 x_return_status := FND_API.G_RET_STS_ERROR ;
1153 FND_MSG_PUB.Count_And_Get
1154 (p_count => x_msg_count ,
1155 p_data => x_msg_data ,
1156 p_encoded => FND_API.G_FALSE );
1157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1158 ROLLBACK TO Get_Quota_Cates;
1159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1160 FND_MSG_PUB.Count_And_Get
1161 (p_count => x_msg_count ,
1162 p_data => x_msg_data ,
1163 p_encoded => FND_API.G_FALSE );
1164 WHEN OTHERS THEN
1165 ROLLBACK TO Get_Quota_Cates;
1166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1167 IF FND_MSG_PUB.Check_Msg_Level
1168 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1169 THEN
1170 FND_MSG_PUB.Add_Exc_Msg
1171 (G_PKG_NAME ,
1172 l_api_name );
1173 END IF;
1174 FND_MSG_PUB.Count_And_Get
1175 (p_count => x_msg_count ,
1176 p_data => x_msg_data ,
1177 p_encoded => FND_API.G_FALSE );
1178 END Get_Quota_Cates;
1179
1180
1181 -- Start of comments
1182 -- API name : Get_Role_Name
1183 -- Type : Private.
1184 -- Function :
1185 -- Pre-reqs : None.
1186 -- Parameters :
1187 -- IN : p_api_version IN NUMBER Required
1188 -- p_init_msg_list IN VARCHAR2 Optional
1189 -- Default = FND_API.G_FALSE
1190 -- p_commit IN VARCHAR2 Optional
1191 -- Default = FND_API.G_FALSE
1192 -- p_validation_level IN NUMBER Optional
1193 -- Default = FND_API.G_VALID_LEVEL_FULL
1194 -- p_role_id IN NUMBER
1195 -- p_role_model_id IN NUMBER
1196 -- OUT : x_return_status OUT VARCHAR2(1)
1197 -- x_msg_count OUT NUMBER
1198 -- x_msg_data OUT VARCHAR2(2000)
1199 -- x_role_name OUT VARCHAR2(80)
1200 -- Version : Current version 1.0
1201 -- Notes : Note text
1202 --
1203 -- End of comments
1204 PROCEDURE Get_Role_Name (
1205 p_api_version IN NUMBER,
1206 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1207 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1208 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1209 p_role_id IN NUMBER,
1210 p_role_model_id IN NUMBER,
1211 x_role_name OUT NOCOPY VARCHAR2,
1212 x_return_status OUT NOCOPY VARCHAR2,
1213 x_msg_count OUT NOCOPY NUMBER,
1214 x_msg_data OUT NOCOPY VARCHAR2
1215 ) IS
1216
1217 l_api_name CONSTANT VARCHAR2(30) := 'Get_Role_Name';
1218 l_api_version CONSTANT NUMBER := 1.0;
1219
1220 BEGIN
1221 -- Standard Start of API savepoint
1222 SAVEPOINT Get_Role_Name;
1223 -- Standard call to check for call compatibility.
1224 IF NOT FND_API.Compatible_API_Call
1225 (l_api_version ,
1226 p_api_version ,
1227 l_api_name ,
1228 G_PKG_NAME )
1229 THEN
1230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1231 END IF;
1232 -- Initialize message list if p_init_msg_list is set to TRUE.
1233 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1234 FND_MSG_PUB.initialize;
1235 END IF;
1236 -- Initialize API return status to success
1237 x_return_status := FND_API.G_RET_STS_SUCCESS;
1238 -- API body
1239
1240 IF p_role_model_id is NULL THEN
1241 SELECT name INTO x_role_name
1242 FROM cn_role_details_v
1243 WHERE role_id = P_ROLE_ID;
1244 ELSE
1245 SELECT name INTO x_role_name
1246 FROM cn_role_models
1247 WHERE role_model_id = P_ROLE_MODEL_ID;
1248 END IF;
1249
1250 -- End of API body.
1251 -- Standard check of p_commit.
1252 IF FND_API.To_Boolean( p_commit ) THEN
1253 COMMIT WORK;
1254 END IF;
1255 -- Standard call to get message count and if count is 1, get message info.
1256 FND_MSG_PUB.Count_And_Get
1257 (p_count => x_msg_count ,
1258 p_data => x_msg_data ,
1259 p_encoded => FND_API.G_FALSE );
1260 EXCEPTION
1261 WHEN FND_API.G_EXC_ERROR THEN
1262 ROLLBACK TO Get_Role_Name;
1263 x_return_status := FND_API.G_RET_STS_ERROR ;
1264 FND_MSG_PUB.Count_And_Get
1265 (p_count => x_msg_count ,
1266 p_data => x_msg_data ,
1267 p_encoded => FND_API.G_FALSE );
1268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1269 ROLLBACK TO Get_Role_Name;
1270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1271 FND_MSG_PUB.Count_And_Get
1272 (p_count => x_msg_count ,
1273 p_data => x_msg_data ,
1274 p_encoded => FND_API.G_FALSE );
1275 WHEN OTHERS THEN
1276 ROLLBACK TO Get_Role_Name;
1277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1278 IF FND_MSG_PUB.Check_Msg_Level
1279 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1280 THEN
1281 FND_MSG_PUB.Add_Exc_Msg
1282 (G_PKG_NAME ,
1283 l_api_name );
1284 END IF;
1285 FND_MSG_PUB.Count_And_Get
1286 (p_count => x_msg_count ,
1287 p_data => x_msg_data ,
1288 p_encoded => FND_API.G_FALSE );
1289 END Get_Role_Name;
1290
1291
1292
1293 FUNCTION Get_Text (
1294 p_role_id IN NUMBER,
1295 p_text_type IN VARCHAR2,
1296 p_quota_category_id IN NUMBER := NULL,
1297 p_role_model_id IN NUMBER := NULL)
1298 RETURN VARCHAR2
1299 IS
1300
1301 l_ret_val VARCHAR2(4000) := NULL ;
1302 l_ret_text2 VARCHAR2(4000) := NULL ;
1303 l_loop_count NUMBER;
1304
1305 CURSOR qc_rate_text_cur(
1306 i_role_id IN NUMBER,
1307 i_quota_category_id IN NUMBER,
1308 i_text_type IN VARCHAR2)
1309 IS
1310 SELECT text,
1311 NVL(text2, ' ') text2
1312 FROM cn_plan_texts
1313 WHERE role_id = i_role_id
1314 AND NVL(role_model_id, 0) = NVL(p_role_model_id, 0)
1315 AND quota_category_id = i_quota_category_id
1316 AND text_type = i_text_type
1317 ;
1318
1319 CURSOR plan_level_text_cur (i_role_id IN NUMBER, i_text_type IN VARCHAR2)
1320 IS
1321 SELECT NVL(text, ' ') text,
1322 NVL(text2, ' ') text2
1323 FROM cn_plan_texts
1324 WHERE role_id = i_role_id
1325 AND NVL(role_model_id, 0) = NVL(p_role_model_id, 0)
1326 AND text_type = i_text_type
1327 ORDER BY sequence_id
1328 ;
1329
1330 BEGIN
1331
1332 IF p_text_type = 'QC_QUOTA_DISP_NAME'
1333 OR p_text_type = 'QC_ATT_TBL_DISP_INFO'
1334 OR p_text_type = 'QC_RT_TIER_DISP_NAME'
1335 THEN
1336 l_loop_count := 0;
1337 FOR qc_rate_text_rec IN qc_rate_text_cur(p_role_id, p_quota_category_id, p_text_type)
1338 LOOP
1339 l_ret_val := qc_rate_text_rec.text;
1340 l_ret_text2 := qc_rate_text_rec.text2;
1341 l_loop_count := l_loop_count + 1;
1342 END LOOP;
1343 END IF;
1344
1345 IF p_text_type = 'PLAN_CLUB_TEXT'
1346 OR p_text_type = 'PLAN_NON_QUOTA_TEXT'
1347 OR p_text_type = 'PLAN_QUOTA_DISPLAY_TEXT'
1348 OR p_text_type = 'PLAN_APPR_NAME'
1349 OR p_text_type = 'PLAN_APPR_TITLE'
1350 OR p_text_type = 'PLAN_APPR_ORG_NAME'
1351 OR p_text_type = 'PLAN_TC_TEXT'
1352 OR p_text_type = 'PLAN_DISP_TOT_FLAG'
1353 OR p_text_type = 'PLAN_DISP_PCT_TGT_FLAG'
1354 or p_text_type = 'PLAN_TITLE_TEXT'
1355 THEN
1356 l_loop_count := 0;
1357 FOR plan_level_text_rec IN plan_level_text_cur(p_role_id, p_text_type)
1358 LOOP
1359 l_ret_val := plan_level_text_rec.text;
1360 l_ret_text2 := plan_level_text_rec.text2;
1361 l_loop_count := l_loop_count + 1;
1362 END LOOP;
1363 END IF;
1364 RETURN l_ret_val ;
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 RETURN NULL;
1368 END Get_Text;
1369
1370
1371 END CN_PLAN_TEXTS_PVT;