1 PACKAGE BODY pqh_tenure_detail AS
2 /* $Header: pqhusprn.pkb 120.2 2005/08/17 11:25:18 nsanghal noship $ */
3
4 PROCEDURE getPersonInfo (
5 p_person_id IN NUMBER,
6 p_employee_number OUT NOCOPY VARCHAR2,
7 p_full_name OUT NOCOPY VARCHAR2,
8 p_last_name OUT NOCOPY VARCHAR2,
9 p_title OUT NOCOPY VARCHAR2,
10 p_email_addr OUT NOCOPY VARCHAR2,
11 p_start_date OUT NOCOPY DATE ) IS
12
13 CURSOR emp_cur IS
14 SELECT employee_number, full_name, last_name, title, email_address, start_date
15 FROM per_all_people_f
16 WHERE SYSDATE BETWEEN effective_start_date and effective_end_date
17 AND person_id = p_person_id;
18
19 l_title VARCHAR2(30);
20 BEGIN
21 OPEN emp_cur;
22 FETCH emp_cur INTO p_employee_number,p_full_name, p_last_name, l_title, p_email_addr, p_start_date;
23 CLOSE emp_cur;
24 p_title := hr_general.decode_lookup(p_lookup_type => 'TITLE',p_lookup_code => l_title);
25 exception
26 when others then
27 p_employee_number := null;
28 p_full_name := null;
29 p_last_name := null;
30 p_title := null;
31 p_email_addr := null;
32 p_start_date := null;
33 END;
34
35 PROCEDURE getPersonTenure (
36 p_person_id IN NUMBER,
37 p_tenure_status OUT NOCOPY VARCHAR2,
38 p_date_determine OUT NOCOPY VARCHAR2,
39 p_adjust_date OUT NOCOPY VARCHAR2,
40 p_remain_years OUT NOCOPY VARCHAR2,
41 p_remain_months OUT NOCOPY VARCHAR2,
42 p_completed_years OUT NOCOPY VARCHAR2,
43 p_completed_months OUT NOCOPY VARCHAR2 ) IS
44
45 CURSOR emp_tenure_cur IS
46 SELECT pei_information1 tenure_status,
47 FND_DATE.canonical_to_date(PEI_INFORMATION2) date_determined,
48 FND_DATE.canonical_to_date(nvl(PEI_INFORMATION4,PEI_INFORMATION3)) adjusted_tenure_date
49 FROM per_people_extra_info
50 WHERE person_id = p_person_id
51 AND information_type = 'PQH_TENURE_STATUS' ;
52
53 /* Join with HR_LOOKUPS to get the Tenure Status Description. */
54 l_date_determine date;
55 l_adjust_date date;
56
57 BEGIN
58 OPEN emp_tenure_cur;
59 FETCH emp_tenure_cur INTO p_tenure_status, l_date_determine, l_adjust_date;
60 CLOSE emp_tenure_cur;
61
62 p_date_determine := fnd_date.date_to_displaydate(l_date_determine);
63 p_adjust_date := fnd_date.date_to_displaydate(l_adjust_date);
64
65 p_remain_years := TRUNC((l_adjust_date - trunc(SYSDATE) ) / 365 );
66 p_remain_months := ROUND( MOD ((l_adjust_date - trunc(SYSDATE)) , 365) / 30 );
67
68 p_completed_years := TRUNC((trunc(SYSDATE) - l_date_determine) / 365 );
69 p_completed_months := ROUND( MOD ((trunc(SYSDATE) - l_date_determine),365) / 30);
70
71 exception when others then
72 p_tenure_status := null;
73 p_date_determine := null;
74 p_adjust_date := null;
75 p_remain_years := null;
76 p_remain_months := null;
77 p_completed_years := null;
78 p_completed_months := null;
79 END;
80
81 FUNCTION getPersonAddress (
82 p_person_id IN NUMBER,
83 p_bgroup_id IN NUMBER ) RETURN VARCHAR2 IS
84
85 CURSOR emp_addr_cur IS
86 SELECT address_line1||DECODE(NVL(address_line2,'X'),'X',FND_GLOBAL.local_chr(10),', '||address_line2||
87 FND_GLOBAL.local_chr(10))|| DECODE(NVL(address_line3,'X'),'X','',address_line3||FND_GLOBAL.local_chr(10))||
88 town_or_city||', '||region_2||' '||postal_code
89 FROM per_addresses
90 WHERE SYSDATE BETWEEN date_from AND NVL(date_to,SYSDATE)
91 AND person_id = p_person_id
92 AND business_group_id = p_bgroup_id
93 AND primary_flag = 'Y';
94
95 l_address VARCHAR2(1000);
96 BEGIN
97 OPEN emp_addr_cur;
98 FETCH emp_addr_cur INTO l_address;
99 CLOSE emp_addr_cur;
100
101 RETURN l_address;
102 END;
103
104 FUNCTION getPersonSupervisor ( p_person_id IN NUMBER ) RETURN NUMBER IS
105
106 CURSOR emp_super_cur IS
107 SELECT supervisor_id
108 FROM per_all_assignments_f
109 WHERE person_id = p_person_id
110 AND primary_flag = 'Y'
111 AND SYSDATE BETWEEN effective_start_date and effective_end_date;
112
113 l_supervisor_id NUMBER;
114 BEGIN
115
116 OPEN emp_super_cur;
117 FETCH emp_super_cur INTO l_supervisor_id;
118 CLOSE emp_super_cur;
119 RETURN l_supervisor_id;
120 END;
121
122 FUNCTION getPersonRank (p_person_id IN NUMBER ) RETURN VARCHAR2 IS
123
124 CURSOR emp_rank_cur IS
125 SELECT hr_general.decode_lookup('PQH_ACADEMIC_RANK',pei_information1)
126 FROM per_people_extra_info
127 WHERE person_id = p_person_id
128 AND information_type = 'PQH_ACADEMIC_RANK' ;
129
130 l_academic_rank hr_lookups.meaning%type;
131
132 BEGIN
133 OPEN emp_rank_cur;
134 FETCH emp_rank_cur INTO l_academic_rank;
135 CLOSE emp_rank_cur;
136 RETURN l_academic_rank;
137 END;
138
139 FUNCTION getPersonJobPosition ( p_person_id IN NUMBER ) RETURN VARCHAR2 IS
140
141 CURSOR prim_asg IS
142 SELECT assignment_id,job_id,position_id
143 FROM per_all_assignments_f paf
144 WHERE paf.person_id = p_person_id
145 and sysdate between effective_start_date and effective_end_date
146 and primary_flag = 'Y'
147 and assignment_type = 'E';
148
149 l_assignment_id number;
150 l_job_id number;
151 l_position_id number;
152 l_job_name per_jobs.name%type;
153 l_position_name hr_all_positions_f.name%type;
154 BEGIN
155 -- fetch the primary assignment
156 OPEN prim_asg;
157 FETCH prim_asg INTO l_assignment_id,l_job_id,l_position_id;
158 CLOSE prim_asg;
159 if l_position_id is not null then
160 l_position_name := hr_general.decode_position_latest_name(l_position_id);
161 elsif l_job_id is not null then
162 l_job_name := hr_general.decode_job(l_job_id);
163 end if;
164 RETURN nvl(l_job_name,l_position_name);
165 END;
166
167 FUNCTION getManagerSequence RETURN NUMBER IS
168 BEGIN
169 managerSeq := managerSeq + 1;
170 RETURN (managerSeq);
171 END;
172
173 FUNCTION getSupStatusCount (
174 p_supervisor_id IN NUMBER,
175 p_tenure_status IN VARCHAR2,
176 p_effective_date IN DATE ) RETURN NUMBER IS
177
178 /* Headcount of Tenure status till now for a supervisor */
179 CURSOR prsn_status_cnt_cur IS
180 SELECT COUNT(paf.person_id)
181 FROM per_all_assignments_f paf,
182 per_people_extra_info ppe
183 WHERE paf.person_id = ppe.person_id
184 and primary_flag ='Y' -- primary assignments only
185 and assignment_type ='E' -- only employees
186 AND ppe.information_type = 'PQH_TENURE_STATUS'
187 AND ppe.pei_information1 = p_tenure_status
188 AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
189 AND FND_DATE.canonical_to_date(PEI_INFORMATION2) <= p_effective_date
190 AND paf.supervisor_id = p_supervisor_id ;
191
192 l_status_count NUMBER;
193
194 BEGIN
195 OPEN prsn_status_cnt_cur;
196 FETCH prsn_status_cnt_cur INTO l_status_count;
197 CLOSE prsn_status_cnt_cur;
198
199 RETURN l_status_count;
200 END;
201
202 FUNCTION getPeriodStatusCount (
203 p_supervisor_id IN NUMBER,
204 p_tenure_status IN VARCHAR2,
205 p_start_date IN DATE,
206 p_end_date IN DATE ) RETURN NUMBER IS
207
208 /* 4Faculty count of Tenure status till now for a supervisor */
209 CURSOR prsn_status_cnt_cur IS
210 SELECT count(person_id)
211 FROM per_all_assignments_f paf
212 WHERE assignment_type ='E'
213 and exists (
214 SELECT null
215 FROM per_people_extra_info ppe
216 WHERE ppe.information_type = 'PQH_TENURE_STATUS'
217 AND ppe.pei_information1 = p_tenure_status
218 AND paf.person_id = ppe.person_id
219 AND FND_DATE.canonical_to_date(PPE.PEI_INFORMATION2) BETWEEN p_start_date AND p_end_date)
220 and exists (
221 SELECT null
222 FROM per_people_extra_info eiar
223 WHERE eiar.information_type = 'PQH_ACADEMIC_RANK'
224 AND paf.person_id = eiar.person_id
225 AND SYSDATE BETWEEN FND_DATE.canonical_to_date(eiar.PEI_INFORMATION2) AND
226 NVL(FND_DATE.canonical_to_date(eiar.PEI_INFORMATION3),SYSDATE))
227 CONNECT BY PRIOR person_id = supervisor_id
228 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
229 AND primary_flag ='Y'
230 START WITH supervisor_id = p_supervisor_id
231 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
232 and primary_flag ='Y';
233
234 l_status_count NUMBER;
235
236 BEGIN
237 OPEN prsn_status_cnt_cur;
238 FETCH prsn_status_cnt_cur INTO l_status_count;
239 CLOSE prsn_status_cnt_cur;
240
241 RETURN l_status_count;
242 END;
243
244 FUNCTION getStatusCount (
245 p_supervisor_id IN NUMBER,
246 p_tenure_status IN VARCHAR2 ) RETURN NUMBER IS
247
248 /* Faculty count of Tenure status till now for a supervisor */
249 CURSOR prsn_status_cnt_cur IS
250 SELECT count(person_id)
251 FROM per_all_assignments_f paf
252 WHERE assignment_type ='E'
253 and exists (
254 SELECT null
255 FROM per_people_extra_info ppe
256 WHERE ppe.information_type = 'PQH_TENURE_STATUS'
257 AND ppe.pei_information1 = p_tenure_status
258 AND paf.person_id = ppe.person_id
259 AND fnd_date.canonical_to_date(ppe.pei_information2) <= SYSDATE)
260 CONNECT BY PRIOR person_id = supervisor_id
261 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
262 AND primary_flag ='Y'
263 START WITH supervisor_id = p_supervisor_id
264 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
265 and primary_flag ='Y';
266
267 l_status_count NUMBER;
268
269 BEGIN
270 OPEN prsn_status_cnt_cur;
271 FETCH prsn_status_cnt_cur INTO l_status_count;
272 CLOSE prsn_status_cnt_cur;
273
274 RETURN l_status_count;
275 END;
276
277 PROCEDURE getTenuredCount (
278 p_supervisor_id IN NUMBER,
279 p_top_level IN VARCHAR2, -- Top level info T-Top only, S-Supervisor only B-Both
280 p_start_academic_dt IN DATE, -- Academic Year start date
281 p_end_academic_dt IN DATE, -- Academic Year End date
282 p_total_cnt OUT NOCOPY NUMBER, -- Total count
283 p_tenured_cnt OUT NOCOPY NUMBER, -- Total Tenured
284 p_tenured_sup_cnt OUT NOCOPY NUMBER, -- Tenured tenured for supervisor
285 p_tt_cnt OUT NOCOPY NUMBER, -- Tenure-track
286 p_tt_sup_cnt OUT NOCOPY NUMBER, -- Tenure-track for supervisor
287 p_tt_final_yr_cnt OUT NOCOPY NUMBER, -- Tenure-track in Final Year
288 p_tt_final_yr_sup_cnt OUT NOCOPY NUMBER, -- Tenure-track for supervisor in Final Year
289 p_ten_cur_yr_cnt OUT NOCOPY NUMBER, -- Tenured for the acdemic year
290 p_ten_cur_yr_sup_cnt OUT NOCOPY NUMBER, -- Tenured for supervisor for acdemic year
291 p_te_cur_yr_cnt OUT NOCOPY NUMBER, -- Tenure-eligible duing the academic year
292 p_te_cur_yr_sup_cnt OUT NOCOPY NUMBER, -- Tenure-eligible for supervisor for academic year
293 p_td_cur_yr_cnt OUT NOCOPY NUMBER, -- Tenure-denied for academic year
294 p_td_cur_yr_sup_cnt OUT NOCOPY NUMBER)IS -- Tenure-denied for supervisor for academic year
295
296 l_tenure_status VARCHAR2(20);
297 l_effective_date DATE;
298
299 /* Total Faculty count irrespective of Tenure status */
300 CURSOR prsn_cnt_cur IS
301 SELECT COUNT(person_id)
302 FROM per_all_assignments_f
303 WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date
304 and primary_flag ='Y'
305 -- 2005/08/08: NS: Performance fix: fetch the count for the business group
306 and business_group_id = hr_general.get_business_group_id
307 and assignment_type ='E';
308
309 /* Faculty count of Tenured/Tenure-track status in final year of consideration*/
310 CURSOR prsn_tt_cnt_cur IS
311 SELECT COUNT(paf.person_id)
312 FROM per_all_assignments_f paf,
313 per_people_extra_info ppe
314 WHERE paf.person_id = ppe.person_id
315 AND ppe.information_type = 'PQH_TENURE_STATUS'
316 and primary_flag ='Y'
317 and assignment_type ='E'
318 AND ppe.pei_information1 = l_tenure_status
319 AND paf.effective_start_date <= SYSDATE
320 AND paf.effective_end_date >= SYSDATE
321 AND nvl(fnd_date.canonical_to_date(NVL(ppe.pei_information4,ppe.pei_information3)),sysdate)
322 BETWEEN p_start_academic_dt AND p_end_academic_dt;
323
324 /* Faculty count of Tenure Status for current Year */
325 CURSOR prsn_ed_cnt_cur IS
326 SELECT COUNT(paf.person_id)
327 FROM per_all_assignments_f paf,
328 per_people_extra_info ppe
329 WHERE paf.person_id = ppe.person_id
330 and primary_flag ='Y'
331 and assignment_type ='E'
332 AND ppe.information_type= 'PQH_TENURE_STATUS'
333 AND ppe.pei_information1= l_tenure_status
334 AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
335 AND fnd_date.canonical_to_date(ppe.pei_information2) BETWEEN
336 p_start_academic_dt AND p_end_academic_dt;
337
338 /* Faculty count of Tenure Status upto to start date */
339 CURSOR prsn_cnt_tillnow_cur IS
340 SELECT COUNT(paf.person_id)
341 FROM per_all_assignments_f paf,
342 per_people_extra_info ppe
343 WHERE paf.person_id = ppe.person_id
344 AND ppe.information_type = 'PQH_TENURE_STATUS'
345 and primary_flag ='Y'
346 and assignment_type ='E'
347 AND ppe.pei_information1= l_tenure_status
348 AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
349 AND fnd_date.canonical_to_date(ppe.pei_information2) <= l_effective_date;
350
351 /*Faculty count of Tenured/Tenure-track status for current/upcoming/previous years */
352 CURSOR prsn_tt_sup_cnt_cur IS
353 SELECT count(person_id)
354 FROM per_all_assignments_f paf
355 WHERE assignment_type ='E'
356 and exists (
357 SELECT null
358 FROM per_people_extra_info ppe
359 WHERE ppe.information_type = 'PQH_TENURE_STATUS'
360 AND ppe.pei_information1 = l_tenure_status
361 AND paf.person_id = ppe.person_id
362 AND nvl(fnd_date.canonical_to_date(
363 NVL(ppe.pei_information4,ppe.pei_information3)),SYSDATE)
364 BETWEEN p_start_academic_dt AND p_end_academic_dt)
365 CONNECT BY PRIOR person_id = supervisor_id
366 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
367 AND primary_flag ='Y'
368 START WITH supervisor_id = p_supervisor_id
369 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
370 and primary_flag ='Y';
371
372 /* Faculty count of Tenure eligible/denied status for current year */
373 CURSOR prsn_ed_sup_cnt_cur IS
374 SELECT count(person_id)
375 FROM per_all_assignments_f paf
376 WHERE assignment_type ='E'
377 and exists (
378 SELECT null
379 FROM per_people_extra_info ppe
380 WHERE ppe.information_type = 'PQH_TENURE_STATUS'
381 AND ppe.pei_information1 = l_tenure_status
382 AND paf.person_id = ppe.person_id
383 AND fnd_date.canonical_to_date(ppe.pei_information2)
384 BETWEEN p_start_academic_dt AND p_end_academic_dt)
385 CONNECT BY PRIOR person_id = supervisor_id
386 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
387 AND primary_flag ='Y'
388 START WITH supervisor_id = p_supervisor_id
389 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
390 and primary_flag ='Y';
391
392 /* Faculty count of Tenure status till now for a supervisor */
393 CURSOR prsn_sup_cnt_tillnow_cur IS
394 SELECT count(person_id)
395 FROM per_all_assignments_f paf
396 WHERE assignment_type ='E'
397 and exists (
398 SELECT null
399 FROM per_people_extra_info ppe
400 WHERE ppe.information_type = 'PQH_TENURE_STATUS'
401 AND ppe.pei_information1 = l_tenure_status
402 AND paf.person_id = ppe.person_id
403 AND fnd_date.canonical_to_date(ppe.pei_information2) <= l_effective_date)
404 CONNECT BY PRIOR person_id = supervisor_id
405 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
406 AND primary_flag ='Y'
407 START WITH supervisor_id = p_supervisor_id
408 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
409 and primary_flag ='Y';
410 BEGIN
411 IF p_top_level IN ('T','B') THEN
412 OPEN prsn_cnt_cur;
413 FETCH prsn_cnt_cur INTO p_total_cnt;
414 CLOSE prsn_cnt_cur;
415 END IF;
416
417 /* ******** TENURED FACULTY COUNT ******** -- */
418 l_tenure_status := '01';
419
420 /* Upto the starting of the academic Year */
421 IF p_top_level IN ('T','B') THEN
422 IF p_start_academic_dt IS NOT NULL THEN
423 l_effective_date := p_start_academic_dt;
424 ELSE
425 l_effective_date := SYSDATE;
426 END IF;
427
428 OPEN prsn_cnt_tillnow_cur;
429 FETCH prsn_cnt_tillnow_cur INTO p_tenured_cnt;
430 CLOSE prsn_cnt_tillnow_cur;
431 END IF;
432
433 /* For specific Supervisor */
434 IF p_top_level IN ('S','B') THEN
435 IF p_start_academic_dt IS NOT NULL THEN
436 l_effective_date := p_start_academic_dt;
437 ELSE
438 l_effective_date := SYSDATE;
439 END IF;
440
441 OPEN prsn_sup_cnt_tillnow_cur;
442 FETCH prsn_sup_cnt_tillnow_cur INTO p_tenured_sup_cnt;
443 CLOSE prsn_sup_cnt_tillnow_cur;
444 END IF;
445
446 /* During the academic year */
447 IF p_top_level IN ('T','B') THEN
448 OPEN prsn_ed_cnt_cur;
449 FETCH prsn_ed_cnt_cur INTO p_ten_cur_yr_cnt;
450 CLOSE prsn_ed_cnt_cur;
451 END IF;
452
453 /* During the academic year for specific supervisor */
454 IF p_top_level IN ('S','B') THEN
455 OPEN prsn_ed_sup_cnt_cur;
456 FETCH prsn_ed_sup_cnt_cur INTO p_ten_cur_yr_sup_cnt;
457 CLOSE prsn_ed_sup_cnt_cur;
458 END IF;
459
460 /* ******** TENURE TRACK FACULTY COUNT ******** -- */
461 l_tenure_status := '02';
462
463 /* Upto the starting of the academic Year */
464 IF p_top_level IN ('T','B') THEN
465 OPEN prsn_cnt_tillnow_cur;
466 FETCH prsn_cnt_tillnow_cur INTO p_tt_cnt;
467 CLOSE prsn_cnt_tillnow_cur;
468 END IF;
469
470 /* For specific Supervisor */
471 IF p_top_level IN ('S','B') THEN
472 OPEN prsn_sup_cnt_tillnow_cur;
473 FETCH prsn_sup_cnt_tillnow_cur INTO p_tt_sup_cnt;
474 CLOSE prsn_sup_cnt_tillnow_cur;
475 END IF;
476
477 /* Final year for tenure consideration */
478 IF p_top_level IN ('T','B') THEN
479 OPEN prsn_tt_cnt_cur;
480 FETCH prsn_tt_cnt_cur INTO p_tt_final_yr_cnt;
481 CLOSE prsn_tt_cnt_cur;
482 END IF;
483
484 /* Final year for tenure consideration for specific supervisor */
485 IF p_top_level IN ('S','B') THEN
486 OPEN prsn_tt_sup_cnt_cur;
487 FETCH prsn_tt_sup_cnt_cur INTO p_tt_final_yr_sup_cnt;
488 CLOSE prsn_tt_sup_cnt_cur;
489 END IF;
490
491 /* ******** TENURE ELIGIBLE FACULTY COUNT ******** -- */
492 l_tenure_status := '04';
493
494 /* Tenure-Eligible this academic year */
495 IF p_top_level IN ('T','B') THEN
496 OPEN prsn_ed_cnt_cur;
497 FETCH prsn_ed_cnt_cur INTO p_te_cur_yr_cnt;
498 CLOSE prsn_ed_cnt_cur;
499 END IF;
500
501 /* Tenure-eligible this academic year for specific supervisor */
502 IF p_top_level IN ('S','B') THEN
503 OPEN prsn_ed_sup_cnt_cur;
504 FETCH prsn_ed_sup_cnt_cur INTO p_te_cur_yr_sup_cnt;
505 CLOSE prsn_ed_sup_cnt_cur;
506 END IF;
507
508 /* ******** TENURE DENIED FACULTY COUNT ******** --*/
509 l_tenure_status := '05';
510
511 /* Tenure-denied this academic year*/
512 IF p_top_level IN ('T','B') THEN
513 OPEN prsn_ed_cnt_cur;
514 FETCH prsn_ed_cnt_cur INTO p_td_cur_yr_cnt;
515 CLOSE prsn_ed_cnt_cur;
516 END IF;
517
518 /* Tenure-denied this academic year for specific supervisor */
519 IF p_top_level IN ('S','B') THEN
520 OPEN prsn_ed_sup_cnt_cur;
521 FETCH prsn_ed_sup_cnt_cur INTO p_td_cur_yr_sup_cnt;
522 CLOSE prsn_ed_sup_cnt_cur;
523 END IF;
524 exception when others then
525 p_total_cnt := null;
526 p_tenured_cnt := null;
527 p_tenured_sup_cnt := null;
528 p_tt_cnt := null;
529 p_tt_sup_cnt := null;
530 p_tt_final_yr_cnt := null;
531 p_tt_final_yr_sup_cnt := null;
532 p_ten_cur_yr_cnt := null;
533 p_ten_cur_yr_sup_cnt := null;
534 p_te_cur_yr_cnt := null;
535 p_te_cur_yr_sup_cnt := null;
536 p_td_cur_yr_cnt := null;
537 p_td_cur_yr_sup_cnt := null;
538 END getTenuredCount;
539
540 PROCEDURE getReportBodyText (
541 p_report_id IN VARCHAR2,
542 p_body_regards OUT NOCOPY VARCHAR2,
543 p_body_text1 OUT NOCOPY VARCHAR2,
544 p_body_text2 OUT NOCOPY VARCHAR2,
545 p_body_text3 OUT NOCOPY VARCHAR2,
546 p_body_text4 OUT NOCOPY VARCHAR2,
547 p_body_text5 OUT NOCOPY VARCHAR2,
548 p_body_text6 OUT NOCOPY VARCHAR2,
549 p_body_text7 OUT NOCOPY VARCHAR2,
550 p_body_text8 OUT NOCOPY VARCHAR2 ) IS
551
552 BEGIN
553 p_body_regards := fnd_message.get_string('PQH','PQH_RGDS_TENURE_RPT_TXT');
554
555 IF p_report_id = 'NT' THEN -- Non-Tenure
556
557 p_body_text1 := fnd_message.get_string('PQH','PQH_NT_TENURE_RPT_TXT');
558
559 ELSIF p_report_id = 'TS' THEN -- Tenure Status
560 p_body_text1 := fnd_message.get_string('PQH','PQH_TS_TENURE_RPT_TXT');
561
562 ELSIF p_report_id = 'TT' THEN -- Tenure Track
563 p_body_text1 := fnd_message.get_string('PQH','PQH_TT_TENURE_RPT_TXT1');
564
565 p_body_text2 := fnd_message.get_string('PQH','PQH_TT_TENURE_RPT_TXT2');
566
567 p_body_text3 := fnd_message.get_string('PQH','PQH_TT_TENURE_RPT_TXT3');
568
569 p_body_text4 := fnd_message.get_string('PQH','PQH_TT_TENURE_RPT_TXT4');
570
571 ELSIF p_report_id = 'RW' THEN -- Review
572
573 p_body_text1 := fnd_message.get_string('PQH','PQH_RW_TENURE_RPT_TXT1');
574
575 p_body_text2 := fnd_message.get_string('PQH','PQH_RW_TENURE_RPT_TXT2');
576
577 ELSIF p_report_id = 'AS' THEN -- Annual Status
578
579 p_body_text1 := fnd_message.get_string('PQH','PQH_AS_TENURE_RPT_TXT1');
580
581 p_body_text2 := fnd_message.get_string('PQH','PQH_AS_TENURE_RPT_TXT2');
582
583 p_body_text3 := fnd_message.get_string('PQH','PQH_AS_TENURE_RPT_TXT3');
584
585 p_body_text4 := fnd_message.get_string('PQH','PQH_AS_TENURE_RPT_TXT4');
586
587 p_body_text5 := fnd_message.get_string('PQH','PQH_AS_TENURE_RPT_TXT5');
588
589 p_body_text6 := fnd_message.get_string('PQH','PQH_AS_TENURE_RPT_TXT6');
590
591 p_body_text7 := fnd_message.get_string('PQH','PQH_AS_TENURE_RPT_TXT7');
592
593 ELSIF p_report_id = 'CE' THEN
594
595 p_body_text1 := fnd_message.get_string('PQH','PQH_CE_TENURE_RPT_TXT');
596 END IF;
597
598 END;
599 END pqh_tenure_detail;
[Home] [Help]