1 PACKAGE BODY cn_job_title_pvt AS
2 --$Header: cnvjobb.pls 115.14.115100.3 2004/05/11 01:32:51 sbadami noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CN_JOB_TITLE_PVT';
5
6 PROCEDURE valid_no_srp(p_job_role_id IN cn_job_roles.job_role_id%type) IS
7 l_count NUMBER;
8 BEGIN
9 -- make sure the job title and role involved in this record is
10 -- not being used by any salesrep.
11 BEGIN
12 SELECT 1
13 INTO l_count
14 FROM cn_job_roles jr
15 WHERE jr.job_role_id = p_job_role_id
16 AND EXISTS (
17 SELECT 1
18 FROM cn_srp_role_dtls_v sr
19 WHERE sr.job_title_id = jr.job_title_id
20 AND jr.role_id = sr.role_id );
21
22 FND_MESSAGE.SET_NAME('CN', 'CN_JOB_ROLE_USED');
23 FND_MSG_PUB.ADD;
24 RAISE FND_API.G_EXC_ERROR;
25 EXCEPTION
26 WHEN NO_DATA_FOUND THEN
27 NULL ;
28 END ;
29
30 END valid_no_srp;
31
32
33
34 PROCEDURE Verify_Date(p_rec IN job_role_rec_type)
35 IS
36 l_count NUMBER;
37 l_min_start_date DATE ;
38 l_max_end_date DATE ;
39 L_NULL_END_DATE DATE := to_date('31-12-9999','DD-MM-YYYY'); --to_date(to_char(TO_DATE('1','j'), 'DD-MM-RRRR'), 'DD-MM-RRRR') ;
40
41 BEGIN
42 -- get the min start_date and the max end_date
43 -- assigned to an srp for this job_role assignment
44 SELECT COUNT(sj.srp_id), min(sj.start_date),
45 MAX(sj.end_date)
46 into l_count,l_min_start_date , l_max_end_date
47 from cn_job_roles jr,
48 cn_srp_job_titles sj,
49 cn_srp_roles sr
50 where jr.job_role_id = p_rec.job_role_id
51 and jr.job_title_id = sj.job_title_id
52 and jr.role_id = sr.role_id
53 and sj.srp_id = sr.salesrep_id
54 ;
55
56 IF L_COUNT > 0 THEN
57 IF l_min_start_date < p_rec.start_date THEN
58 FND_MESSAGE.SET_NAME('CN', 'CN_JOB_ROLE_USED');
59 FND_MSG_PUB.ADD;
60 RAISE FND_API.G_EXC_ERROR;
61 END IF ;
62
63 IF l_max_end_date IS NOT NULL THEN
64 IF NVL(p_rec.end_date, L_NULL_END_DATE) < l_max_end_date
65 THEN
66 FND_MESSAGE.SET_NAME('CN', 'CN_JOB_ROLE_USED');
67 FND_MSG_PUB.ADD;
68 RAISE FND_API.G_EXC_ERROR;
69 END IF ;
70 END IF ;
71 END IF;
72 END Verify_Date;
73
74
75 -- Start of comments
76 -- API name : Create_Job_Role - Private.
77 -- Pre-reqs : None.
78 -- IN : standard params
79 -- p_rec of table rec type
80 -- OUT : standard params
81 -- x_job_role_id
82 -- Version : 1.0
83 --
84 -- End of comments
85
86 PROCEDURE Create_Job_Role
87 (p_api_version IN NUMBER,
88 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
89 p_commit IN VARCHAR2 := FND_API.G_FALSE,
90 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
91 p_rec IN job_role_rec_type,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_msg_count OUT NOCOPY NUMBER,
94 x_msg_data OUT NOCOPY VARCHAR2,
95 x_job_role_id OUT NOCOPY cn_job_roles.job_role_id%type) IS
96
97 l_api_name CONSTANT VARCHAR2(30) := 'Create_Job_Role';
98 l_api_version CONSTANT NUMBER := 1.0;
99 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
100
101 l_newrec job_role_rec_type := p_rec;
102 l_count NUMBER;
103 BEGIN
104 -- Standard Start of API savepoint
105 SAVEPOINT Create_Job_Role;
106
107 -- Standard call to check for call compatibility.
108 IF NOT FND_API.Compatible_API_Call
109 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 END IF;
112
113 -- Initialize message list if p_init_msg_list is set to TRUE.
114 IF FND_API.to_Boolean( p_init_msg_list ) THEN
115 FND_MSG_PUB.initialize;
116 END IF;
117
118 -- Initialize API return status to success
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120
121 -- make sure job_title_id and role_id are valid
122 -- if default, make sure it doesn't overlap with another default
123
124 -- checks validity of job_title_id, role_id, date range
125 if l_newrec.end_date is not null and
126 l_newrec.end_date < l_newrec.start_date then
127 FND_MESSAGE.SET_NAME('CN', 'ALL_INVALID_PERIOD_RANGE');
128 FND_MSG_PUB.ADD;
129 RAISE FND_API.G_EXC_ERROR;
130 end if;
131
132
133 --
134 select count(job_role_id) into l_count
135 from cn_job_roles
136 where job_title_id = l_newrec.job_title_id
137 and default_flag = 'Y'
138 and ((start_date <= l_newrec.start_date and
139 nvl(end_date, l_null_date) >=
140 l_newrec.start_date ) OR
141 (start_date >= l_newrec.start_date and
142 start_date <= nvl(l_newrec.end_date, l_null_date)));
143
144 if l_count > 0 and l_newrec.default_flag = 'Y' then
145 FND_MESSAGE.SET_NAME('CN', 'CN_JOB_ROLE_OVERLAP');
146 FND_MSG_PUB.ADD;
147 RAISE FND_API.G_EXC_ERROR;
148 end if;
149
150 select count(job_role_id) into l_count
151 from cn_job_roles
152 where job_title_id = l_newrec.job_title_id
153 and role_id = l_newrec.role_id
154 and ((start_date <= l_newrec.start_date and
155 nvl(end_date, l_null_date) >=
156 l_newrec.start_date ) OR
157 (start_date >= l_newrec.start_date and
158 start_date <= nvl(l_newrec.end_date, l_null_date)));
159
160 if l_count > 0 then
161 FND_MESSAGE.SET_NAME('CN', 'CN_JOB_ROLE_OVERLAP');
162 FND_MSG_PUB.ADD;
163 RAISE FND_API.G_EXC_ERROR;
164 end if;
165
166 select count(1) into l_count
167 from cn_job_roles
168 where role_id = l_newrec.role_id
169 and job_title_id = l_newrec.job_title_id
170 and trunc(start_date) = trunc(l_newrec.start_date)
171 ;
172
173 if l_count > 0 then
174 FND_MESSAGE.SET_NAME('CN', 'CN_JOB_ROLE_OVERLAP');
175 FND_MSG_PUB.ADD;
176 RAISE FND_API.G_EXC_ERROR;
177 end if;
178
179 cn_job_title_pkg.insert_row(l_newrec);
180
181 -- Standard check of p_commit.
182 IF FND_API.To_Boolean( p_commit ) THEN
183 COMMIT WORK;
184 END IF;
185
186 FND_MSG_PUB.Count_And_Get
187 (p_count => x_msg_count,
188 p_data => x_msg_data,
189 p_encoded => FND_API.G_FALSE);
190
191 EXCEPTION
192 WHEN FND_API.G_EXC_ERROR THEN
193 ROLLBACK TO Create_Job_Role;
194 x_return_status := FND_API.G_RET_STS_ERROR;
195 FND_MSG_PUB.Count_And_Get
196 (p_count => x_msg_count,
197 p_data => x_msg_data,
198 p_encoded => FND_API.G_FALSE);
199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
200 ROLLBACK TO Create_Job_Role;
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 FND_MSG_PUB.Count_And_Get
203 (p_count => x_msg_count,
204 p_data => x_msg_data,
205 p_encoded => FND_API.G_FALSE);
206 WHEN OTHERS THEN
207 ROLLBACK TO Create_Job_Role;
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
209 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
210 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
211 END IF;
212 FND_MSG_PUB.Count_And_Get
213 (p_count => x_msg_count,
214 p_data => x_msg_data,
215 p_encoded => FND_API.G_FALSE);
216 END Create_Job_Role;
217
218 -- Start of comments
219 -- API name : Update_Job_Role - Private.
220 -- Pre-reqs : None.
221 -- IN : standard params
222 -- p_rec of table rec type
223 -- OUT : standard params
224 -- Version : Current version 1.0
225 --
226 -- End of comments
227
228 PROCEDURE Update_Job_Role
229 (p_api_version IN NUMBER, -- required
230 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
231 p_commit IN VARCHAR2 := FND_API.G_FALSE,
232 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
233 p_rec IN job_role_rec_type,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2) IS
237
238 l_api_name CONSTANT VARCHAR2(30) := 'Update_Job_Role';
239 l_api_version CONSTANT NUMBER := 1.0;
240 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
241
242 l_newrec job_role_rec_type := p_rec;
243 l_count NUMBER;
244 l_old_start DATE;
245 l_old_end DATE;
246 BEGIN
247 -- Standard Start of API savepoint
248 SAVEPOINT Update_Job_Role;
249
250 -- Standard call to check for call compatibility.
251 IF NOT FND_API.Compatible_API_Call
252 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 IF FND_API.to_Boolean( p_init_msg_list ) THEN
258 FND_MSG_PUB.initialize;
259 END IF;
260
261 -- Initialize API return status to success
262 x_return_status := FND_API.G_RET_STS_SUCCESS;
263
264
265 -- checks validity of start_date
266 if l_newrec.start_date is null then
267 FND_MESSAGE.SET_NAME('PA', 'PA_START_DATE_INVALID');
268 FND_MSG_PUB.ADD;
269 RAISE FND_API.G_EXC_ERROR;
270 end if;
271
272
273 -- make sure job_title_id and role_id are valid
274 -- if default, make sure it doesn't overlap with another default
275
276 -- make sure record to be updated exists.
277 select count(*) into l_count
278 from cn_job_roles
279 where job_role_id = l_newrec.job_role_id;
280
281 if l_count = 0 then
282 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_UPDATE_REC');
283 FND_MSG_PUB.ADD;
284 RAISE FND_API.G_EXC_ERROR;
285 end if;
286
287 -- checks validity of date range
288 if l_newrec.end_date is not null and
289 l_newrec.end_date < l_newrec.start_date then
290 FND_MESSAGE.SET_NAME('CN', 'ALL_INVALID_PERIOD_RANGE');
291 FND_MSG_PUB.ADD;
292 RAISE FND_API.G_EXC_ERROR;
293 end if;
294
295 -- make sure it is not being used by any salesrep if we are narrowing
296 -- the assignment interval
297 select start_date, end_date
298 into l_old_start, l_old_end
299 from cn_job_roles
300 where job_role_id = l_newrec.job_role_id;
301
302 if l_old_start < l_newrec.start_date OR
303 nvl(l_old_end, to_date('12-31-9999','MM-DD-YYYY')) >
304 nvl(l_newrec.end_date, to_date('12-31-9999','MM-DD-YYYY')) then
305 verify_date(l_newrec);
306 end if;
307
308 select count(job_role_id) into l_count
309 from cn_job_roles
310 where job_title_id = l_newrec.job_title_id
311 and job_role_id <> l_newrec.job_role_id
312 and default_flag = 'Y'
313 and ((start_date <= l_newrec.start_date and
314 nvl(end_date, l_null_date) >= l_newrec.start_date) OR
315 (start_date >= l_newrec.start_date and
316 start_date <= nvl(l_newrec.end_date, l_null_date)));
317 if l_count > 0 and l_newrec.default_flag = 'Y' then
318 FND_MESSAGE.SET_NAME('CN', 'CN_JOB_ROLE_OVERLAP');
319 FND_MSG_PUB.ADD;
320 RAISE FND_API.G_EXC_ERROR;
321 end if;
322
323 select count(job_role_id) into l_count
324 from cn_job_roles
325 where job_title_id = l_newrec.job_title_id
326 and job_role_id <> l_newrec.job_role_id
327 and role_id = l_newrec.role_id
328 and ((start_date <= l_newrec.start_date and
329 nvl(end_date, l_null_date) >=
330 l_newrec.start_date ) OR
331 (start_date >= l_newrec.start_date and
332 start_date <= nvl(l_newrec.end_date, l_null_date)));
333
334 if l_count > 0 then
335 FND_MESSAGE.SET_NAME('CN', 'CN_JOB_ROLE_OVERLAP');
336 FND_MSG_PUB.ADD;
337 RAISE FND_API.G_EXC_ERROR;
338 end if;
339
340 -- make sure the object version number hasn't changed in the meantime
341 cn_job_title_pkg.lock_row(l_newrec.job_role_id,
342 l_newrec.object_version_number);
343
344 cn_job_title_pkg.update_row(l_newrec);
345
346 -- Standard check of p_commit.
347 IF FND_API.To_Boolean( p_commit ) THEN
348 COMMIT WORK;
349 END IF;
350
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 EXCEPTION
356 WHEN FND_API.G_EXC_ERROR THEN
357 ROLLBACK TO Update_Job_Role;
358 x_return_status := FND_API.G_RET_STS_ERROR;
359 FND_MSG_PUB.Count_And_Get
360 (p_count => x_msg_count,
361 p_data => x_msg_data,
362 p_encoded => FND_API.G_FALSE);
363 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
364 ROLLBACK TO Update_Job_Role;
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366 FND_MSG_PUB.Count_And_Get
367 (p_count => x_msg_count,
368 p_data => x_msg_data,
369 p_encoded => FND_API.G_FALSE);
370 WHEN OTHERS THEN
371 ROLLBACK TO Update_Job_Role;
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
373 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
374 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
375 END IF;
376 FND_MSG_PUB.Count_And_Get
377 (p_count => x_msg_count,
378 p_data => x_msg_data,
379 p_encoded => FND_API.G_FALSE);
380 END Update_Job_Role;
381
382 -- Start of comments
383 -- API name : Delete_Job_Role - Private.
384 -- Pre-reqs : None.
385 -- IN : standard params
386 -- p_job_role_id
387 -- OUT : standard params
388 -- Version : Current version 1.0
389 --
390 -- End of comments
391
392 PROCEDURE Delete_Job_Role
393 (p_api_version IN NUMBER,
394 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
395 p_commit IN VARCHAR2 := FND_API.G_FALSE,
396 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
397 p_job_role_id IN cn_job_roles.job_role_id%type,
398 p_object_version_number IN cn_job_roles.object_version_number%type,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_msg_count OUT NOCOPY NUMBER,
401 x_msg_data OUT NOCOPY VARCHAR2) IS
402
403 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Job_Role';
404 l_api_version CONSTANT NUMBER := 1.0;
405 l_count NUMBER;
406
407 BEGIN
408 -- Standard Start of API savepoint
409 SAVEPOINT Delete_Job_Role;
410
411 -- Standard call to check for call compatibility.
412 IF NOT FND_API.Compatible_API_Call
413 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415 END IF;
416
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
422 -- Initialize API return status to success
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424
425 -- make sure record to be deleted exists.
426 select count(*) into l_count
427 from cn_job_roles
428 where job_role_id = p_job_role_id;
429
430 if l_count = 0 then
431 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_DEL_REC');
432 FND_MSG_PUB.ADD;
433 RAISE FND_API.G_EXC_ERROR;
434 end if;
435
436 -- make sure it is not being used by any salesrep
437 valid_no_srp(p_job_role_id);
438
439 cn_job_title_pkg.delete_row(p_job_role_id);
440
441 -- Standard check of p_commit.
442 IF FND_API.To_Boolean( p_commit ) THEN
443 COMMIT WORK;
444 END IF;
445
446 FND_MSG_PUB.Count_And_Get
447 (p_count => x_msg_count,
448 p_data => x_msg_data,
449 p_encoded => FND_API.G_FALSE);
450 EXCEPTION
451 WHEN FND_API.G_EXC_ERROR THEN
452 ROLLBACK TO Delete_Job_Role;
453 x_return_status := FND_API.G_RET_STS_ERROR;
454 FND_MSG_PUB.Count_And_Get
455 (p_count => x_msg_count,
456 p_data => x_msg_data,
457 p_encoded => FND_API.G_FALSE);
458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
459 ROLLBACK TO Delete_Job_Role;
460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
461 FND_MSG_PUB.Count_And_Get
462 (p_count => x_msg_count,
463 p_data => x_msg_data,
464 p_encoded => FND_API.G_FALSE);
465 WHEN OTHERS THEN
466 ROLLBACK TO Delete_Job_Role;
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
468 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
469 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
470 END IF;
471 FND_MSG_PUB.Count_And_Get
472 (p_count => x_msg_count,
473 p_data => x_msg_data,
474 p_encoded => FND_API.G_FALSE);
475 END Delete_Job_Role;
476
477 -- Start of comments
478 -- API name : Get_Job_Details - Private
479 -- Pre-reqs : None.
480 -- IN : p_job_title_id
481 -- OUT : x_result_tbl
482 -- Version : Current version 1.0
483 --
484 -- End of comments
485
486 PROCEDURE Get_Job_Details
487 (p_job_title_id IN cn_job_titles.job_title_id%type,
488 x_result_tbl OUT NOCOPY job_role_tbl_type) IS
489
490 l_count NUMBER := 1;
491
492 cursor get_data is
493 select job_role_id, job_title_id, role_id,
494 start_date, end_date, nvl(default_flag, 'N'),
495 attribute_category,
496 attribute1, attribute2, attribute3, attribute4, attribute5,
497 attribute6, attribute7, attribute8, attribute9, attribute10,
498 attribute11, attribute12, attribute13, attribute14, attribute15,
499 object_version_number
500 from cn_job_roles
501 where job_title_id = p_job_title_id
502 order by start_date;
503
504 BEGIN
505 open get_data;
506 loop
507 fetch get_data into x_result_tbl(l_count);
508 exit when get_data%notfound;
509 l_count := l_count + 1;
510 end loop;
511 close get_data;
512 END Get_Job_Details;
513
514 -- Start of comments
515 -- API name : Get_Job_Titles - Private
516 -- Pre-reqs : None.
517 -- IN : range params, search string for name
518 -- OUT : x_result_tbl
519 -- Version : Current version 1.0
520 --
521 -- End of comments
522
523 PROCEDURE Get_Job_Titles
524 (p_range_low IN NUMBER,
525 p_range_high IN NUMBER,
526 p_search_name IN VARCHAR2 := '%',
527 p_search_code IN VARCHAR2 := '%',
528 x_total_rows OUT NOCOPY NUMBER,
529 x_result_tbl OUT NOCOPY job_title_tbl_type) IS
530
531 rec job_title_rec_type;
532 l_index NUMBER := 0;
533 l_name cn_job_titles.name%type;
534 l_code cn_job_titles.job_code%type;
535
536 cursor get_data is
537 select job_title_id, name, job_code,
538 null role_id, null role_name
539 from cn_job_titles
540 where upper(name) like l_name
541 and upper(job_code) like l_code
542 order by name;
543
544 BEGIN
545 x_total_rows := 0;
546
547 l_name := upper(p_search_name) || '%';
548 l_code := upper(p_search_code) || '%';
549
550 open get_data;
551 loop
552 fetch get_data into rec;
553 exit when get_data%notfound;
554
555 x_total_rows := x_total_rows + 1;
556 if (x_total_rows between p_range_low and p_range_high) OR
557 (p_range_high = -1) then
558 l_index := l_index + 1;
559 x_result_tbl(l_index) := rec;
560 end if;
561 end loop;
562 close get_data;
563 END Get_Job_Titles;
564
565 -- Start of comments
566 -- API name : Get_All_Job_Titles - Private
567 -- Pre-reqs : None.
568 -- IN : (none)
569 -- OUT : x_result_tbl
570 -- Version : Current version 1.0
571 --
572 -- End of comments
573
574 PROCEDURE Get_All_Job_Titles
575 (x_result_tbl OUT NOCOPY job_title_tbl_type) IS
576 l_total_rows NUMBER;
577 BEGIN
578 Get_Job_Titles
579 (p_range_low => -1,
580 p_range_high => -1,
581 p_search_name => '%',
582 p_search_code => '%',
583 x_total_rows => l_total_rows,
584 x_result_tbl => x_result_tbl);
585 END Get_All_Job_Titles;
586
587 -- Start of comments
588 -- API name : Get_Job_Roles - Private
589 -- Pre-reqs : None.
590 -- IN : (none)
591 -- OUT : x_result_tbl
592 -- Version : Current version 1.0
593 --
594 -- End of comments
595
596 PROCEDURE Get_Job_Roles
597 (x_result_tbl OUT NOCOPY job_title_tbl_type) IS
598
599 l_count NUMBER := 1;
600
601 cursor get_data is
602 select distinct * from
603 (select jr.job_title_id,
604 j.name job_name,
605 j.job_code,
606 jr.role_id,
607 r.role_name
608 from cn_job_roles jr,
609 cn_job_titles j,
610 jtf_rs_roles_vl r
611 where jr.job_title_id = j.job_title_id
612 and r.role_type_code= 'SALES_COMP'
613 and (r.member_flag = 'Y' OR r.manager_flag = 'Y') -- added check
614 and jr.role_id = r.role_id
615 order by jr.job_title_id, jr.default_flag DESC);
616
617 BEGIN
618 open get_data;
619 loop
620 fetch get_data into x_result_tbl(l_count);
621 exit when get_data%notfound;
622 l_count := l_count + 1;
623 end loop;
624 close get_data;
625 END Get_Job_Roles;
626
627 END CN_JOB_TITLE_PVT;