[Home] [Help]
PACKAGE BODY: APPS.GHR_COMPLAINTS2_PKG
Source
1 PACKAGE BODY ghr_complaints2_pkg AS
2 /* $Header: ghrcomp2.pkb 115.9 2004/06/09 00:31:26 sumarimu noship $ */
3
4 -- Get Fullname and SSN
5 PROCEDURE get_fullname_ssn (p_complainant_person_id IN NUMBER
6 ,p_effective_date IN DATE
7 ,p_full_name IN OUT NOCOPY VARCHAR2
8 ,p_ssn IN OUT NOCOPY VARCHAR2) IS
9
10 l_full_name per_people_f.full_name%TYPE := NULL;
11 l_ssn per_people_f.national_identifier%TYPE := NULL;
12 l_record_found BOOLEAN := FALSE;
13
14 /*
15 CURSOR cur_cper IS
16 SELECT per.full_name
17 ,per.national_identifier
18 FROM per_people_v per
19 WHERE p_complainant_person_id = per.person_id
20 AND p_effective_date between per.effective_start_date and per.effective_end_date; */
21 -- Above commented by Sundar for performance changes
22 CURSOR cur_cper IS
23 SELECT per.full_name
24 ,per.national_identifier
25 FROM per_people_f per
26 WHERE per.person_id = p_complainant_person_id
27 AND p_effective_date between per.effective_start_date and per.effective_end_date;
28 -- NOCOPY variables
29 l_nc_full_name per_people_f.full_name%TYPE;
30 l_nc_ssn per_people_f.national_identifier%TYPE;
31
32 BEGIN
33 -- NOCOPY Changes
34 l_nc_full_name := p_full_name;
35 l_nc_ssn := p_ssn;
36
37 FOR cur_cper_rec IN cur_cper LOOP
38 IF not l_record_found THEN
39 l_full_name := cur_cper_rec.full_name;
40 l_ssn := cur_cper_rec.national_identifier;
41 l_record_found := TRUE;
42 ELSE
43 l_full_name := null;
44 l_ssn := null;
45 EXIT;
46 END IF;
47 END LOOP;
48
49 p_full_name := l_full_name;
50 p_ssn := l_ssn;
51 EXCEPTION
52 WHEN OTHERS THEN
53 p_full_name := l_nc_full_name;
54 p_ssn := l_nc_ssn;
55 END get_fullname_ssn;
56 --
57
58 -- Get Assignment IDs
59 PROCEDURE get_asg_ids (p_complainant_person_id IN NUMBER
60 ,p_effective_date IN DATE
61 ,p_asg_id IN OUT NOCOPY NUMBER
62 ,p_posn_id IN OUT NOCOPY NUMBER
63 ,p_grade_id IN OUT NOCOPY NUMBER
64 ,p_job_id IN OUT NOCOPY NUMBER)IS
65
66 l_asg_id per_assignments_f.assignment_id%TYPE := NULL;
67 l_posn_id per_assignments_f.position_id%TYPE := NULL;
68 l_grade_id per_assignments_f.grade_id%TYPE := NULL;
69 l_job_id per_assignments_f.job_id%TYPE := NULL;
70 l_record_found BOOLEAN := FALSE;
71
72 CURSOR cur_casg IS
73 SELECT pas.assignment_id, pas.position_id, pas.grade_id, pas.job_id
74 FROM per_assignments_f pas
75 WHERE p_complainant_person_id = pas.person_id
76 AND assignment_type <> 'B'
77 AND p_effective_date between pas.effective_start_date and pas.effective_end_date;
78
79 -- NOCOPY Variables
80 l_nc_asg_id NUMBER;
81 l_nc_posn_id NUMBER;
82 l_nc_grade_id NUMBER;
83 l_nc_job_id NUMBER;
84
85 BEGIN
86 -- NOCOPY Changes
87 l_nc_asg_id := p_asg_id;
88 l_nc_posn_id := p_posn_id;
89 l_nc_grade_id := p_grade_id;
90 l_nc_job_id := p_job_id;
91
92 FOR cur_casg_rec IN cur_casg LOOP
93 IF not l_record_found THEN
94 l_asg_id := cur_casg_rec.assignment_id;
95 l_posn_id := cur_casg_rec.position_id;
96 l_grade_id := cur_casg_rec.grade_id;
97 l_job_id := cur_casg_rec.job_id;
98 l_record_found := TRUE;
99 EXIT;
100 ELSE
101 l_asg_id := null;
102 l_posn_id := null;
103 l_grade_id := null;
104 l_job_id := null;
105 EXIT;
106 END IF;
107 END LOOP;
108
109 p_asg_id := l_asg_id;
110 p_posn_id := l_posn_id;
111 p_grade_id := l_grade_id;
112 p_job_id := l_job_id;
113 EXCEPTION
114 WHEN OTHERS THEN
115 p_asg_id := l_nc_asg_id;
116 p_posn_id := l_nc_posn_id;
117 p_grade_id := l_nc_grade_id;
118 p_job_id := l_nc_job_id;
119 END get_asg_ids;
120 --
121
122 -- Fetch People EI
123 PROCEDURE fetch_peopleei(
124 p_person_id IN NUMBER
125 ,p_information_type IN VARCHAR2
126 ,p_date_effective IN DATE
127 ,p_information1 IN OUT NOCOPY VARCHAR2
128 ,p_information2 IN OUT NOCOPY VARCHAR2
129 ,p_information3 IN OUT NOCOPY VARCHAR2
130 ,p_information4 IN OUT NOCOPY VARCHAR2
131 ,p_information5 IN OUT NOCOPY VARCHAR2
132 ,p_information6 IN OUT NOCOPY VARCHAR2
133 ,p_information7 IN OUT NOCOPY VARCHAR2
134 ,p_information8 IN OUT NOCOPY VARCHAR2
135 ,p_information9 IN OUT NOCOPY VARCHAR2
136 ,p_information10 IN OUT NOCOPY VARCHAR2
137 ,p_information11 IN OUT NOCOPY VARCHAR2
138 ,p_information12 IN OUT NOCOPY VARCHAR2
139 ,p_information13 IN OUT NOCOPY VARCHAR2
140 ,p_information14 IN OUT NOCOPY VARCHAR2
141 ,p_information15 IN OUT NOCOPY VARCHAR2
142 ,p_information16 IN OUT NOCOPY VARCHAR2
143 ,p_information17 IN OUT NOCOPY VARCHAR2
144 ,p_information18 IN OUT NOCOPY VARCHAR2
145 ,p_information19 IN OUT NOCOPY VARCHAR2
146 ,p_information20 IN OUT NOCOPY VARCHAR2
147 ,p_information21 IN OUT NOCOPY VARCHAR2
148 ,p_information22 IN OUT NOCOPY VARCHAR2
149 ,p_information23 IN OUT NOCOPY VARCHAR2
150 ,p_information24 IN OUT NOCOPY VARCHAR2
151 ,p_information25 IN OUT NOCOPY VARCHAR2
152 ,p_information26 IN OUT NOCOPY VARCHAR2
153 ,p_information27 IN OUT NOCOPY VARCHAR2
154 ,p_information28 IN OUT NOCOPY VARCHAR2
155 ,p_information29 IN OUT NOCOPY VARCHAR2
156 ,p_information30 IN OUT NOCOPY VARCHAR2) IS
157
158 l_per_ei_data per_people_extra_info%rowtype;
159 -- NOCOPY Variables
160 l_information1 per_people_extra_info.pei_information1%type:= p_information1 ;
161 l_information2 per_people_extra_info.pei_information2%type:= p_information2 ;
162 l_information3 per_people_extra_info.pei_information3%type:= p_information3 ;
163 l_information4 per_people_extra_info.pei_information4%type:= p_information4 ;
164 l_information5 per_people_extra_info.pei_information5%type:= p_information5 ;
165 l_information6 per_people_extra_info.pei_information6%type:= p_information6 ;
166 l_information7 per_people_extra_info.pei_information7%type:= p_information7 ;
167 l_information8 per_people_extra_info.pei_information8%type:= p_information8 ;
168 l_information9 per_people_extra_info.pei_information9%type:= p_information9 ;
169 l_information10 per_people_extra_info.pei_information10%type:= p_information10;
170 l_information11 per_people_extra_info.pei_information11%type:= p_information11;
171 l_information12 per_people_extra_info.pei_information12%type:= p_information12;
172 l_information13 per_people_extra_info.pei_information13%type:= p_information13;
173 l_information14 per_people_extra_info.pei_information14%type:= p_information14;
174 l_information15 per_people_extra_info.pei_information15%type:= p_information15;
175 l_information16 per_people_extra_info.pei_information16%type:= p_information16;
176 l_information17 per_people_extra_info.pei_information17%type:= p_information17;
177 l_information18 per_people_extra_info.pei_information18%type:= p_information18;
178 l_information19 per_people_extra_info.pei_information19%type:= p_information19;
179 l_information20 per_people_extra_info.pei_information20%type:= p_information20;
180 l_information21 per_people_extra_info.pei_information21%type:= p_information21;
181 l_information22 per_people_extra_info.pei_information22%type:= p_information22;
182 l_information23 per_people_extra_info.pei_information23%type:= p_information23;
183 l_information24 per_people_extra_info.pei_information24%type:= p_information24;
184 l_information25 per_people_extra_info.pei_information25%type:= p_information25;
185 l_information26 per_people_extra_info.pei_information26%type:= p_information26;
186 l_information27 per_people_extra_info.pei_information27%type:= p_information27;
187 l_information28 per_people_extra_info.pei_information28%type:= p_information28;
188 l_information29 per_people_extra_info.pei_information29%type:= p_information29;
189 l_information30 per_people_extra_info.pei_information30%type:= p_information30;
190
191 BEGIN
192 ghr_history_fetch.fetch_peopleei(
193 p_person_id => p_person_id
194 ,p_information_type => p_information_type
195 ,p_date_effective => p_date_effective
196 ,p_per_ei_data => l_per_ei_data);
197
198 p_information1 := l_per_ei_data.pei_information1;
199 p_information2 := l_per_ei_data.pei_information2;
200 p_information3 := l_per_ei_data.pei_information3;
201 p_information4 := l_per_ei_data.pei_information4;
202 p_information5 := l_per_ei_data.pei_information5;
203 p_information6 := l_per_ei_data.pei_information6;
204 p_information7 := l_per_ei_data.pei_information7;
205 p_information8 := l_per_ei_data.pei_information8;
206 p_information9 := l_per_ei_data.pei_information9;
207 p_information10 := l_per_ei_data.pei_information10;
208 p_information11 := l_per_ei_data.pei_information11;
209 p_information12 := l_per_ei_data.pei_information12;
210 p_information13 := l_per_ei_data.pei_information13;
211 p_information14 := l_per_ei_data.pei_information14;
212 p_information15 := l_per_ei_data.pei_information15;
213 p_information16 := l_per_ei_data.pei_information16;
214 p_information17 := l_per_ei_data.pei_information17;
215 p_information18 := l_per_ei_data.pei_information18;
216 p_information19 := l_per_ei_data.pei_information19;
217 p_information20 := l_per_ei_data.pei_information20;
218 p_information21 := l_per_ei_data.pei_information21;
219 p_information22 := l_per_ei_data.pei_information22;
220 p_information23 := l_per_ei_data.pei_information23;
221 p_information24 := l_per_ei_data.pei_information24;
222 p_information25 := l_per_ei_data.pei_information25;
223 p_information26 := l_per_ei_data.pei_information26;
224 p_information27 := l_per_ei_data.pei_information27;
225 p_information28 := l_per_ei_data.pei_information28;
226 p_information29 := l_per_ei_data.pei_information29;
227 p_information30 := l_per_ei_data.pei_information30;
228 EXCEPTION
229 WHEN OTHERS THEN
230 p_information1 := l_information1 ;
231 p_information2 := l_information2 ;
232 p_information3 := l_information3 ;
233 p_information4 := l_information4 ;
234 p_information5 := l_information5 ;
235 p_information6 := l_information6 ;
236 p_information7 := l_information7 ;
237 p_information8 := l_information8 ;
238 p_information9 := l_information9 ;
239 p_information10 := l_information10;
240 p_information11 := l_information11;
241 p_information12 := l_information12;
242 p_information13 := l_information13;
243 p_information14 := l_information14;
244 p_information15 := l_information15;
245 p_information16 := l_information16;
246 p_information17 := l_information17;
247 p_information18 := l_information18;
248 p_information19 := l_information19;
249 p_information20 := l_information20;
250 p_information21 := l_information21;
251 p_information22 := l_information22;
252 p_information23 := l_information23;
253 p_information24 := l_information24;
254 p_information25 := l_information25;
255 p_information26 := l_information26;
256 p_information27 := l_information27;
257 p_information28 := l_information28;
258 p_information29 := l_information29;
259 p_information30 := l_information30;
260 END fetch_peopleei;
261 --
262
263 -- Fetch Assignment EI
264 PROCEDURE fetch_asgei(
265 p_assignment_id IN NUMBER
266 ,p_information_type IN VARCHAR2
267 ,p_date_effective IN DATE
268 ,p_information1 IN OUT NOCOPY VARCHAR2
269 ,p_information2 IN OUT NOCOPY VARCHAR2
270 ,p_information3 IN OUT NOCOPY VARCHAR2
271 ,p_information4 IN OUT NOCOPY VARCHAR2
272 ,p_information5 IN OUT NOCOPY VARCHAR2
273 ,p_information6 IN OUT NOCOPY VARCHAR2
274 ,p_information7 IN OUT NOCOPY VARCHAR2
275 ,p_information8 IN OUT NOCOPY VARCHAR2
276 ,p_information9 IN OUT NOCOPY VARCHAR2
277 ,p_information10 IN OUT NOCOPY VARCHAR2
278 ,p_information11 IN OUT NOCOPY VARCHAR2
279 ,p_information12 IN OUT NOCOPY VARCHAR2
280 ,p_information13 IN OUT NOCOPY VARCHAR2
281 ,p_information14 IN OUT NOCOPY VARCHAR2
282 ,p_information15 IN OUT NOCOPY VARCHAR2
283 ,p_information16 IN OUT NOCOPY VARCHAR2
284 ,p_information17 IN OUT NOCOPY VARCHAR2
285 ,p_information18 IN OUT NOCOPY VARCHAR2
286 ,p_information19 IN OUT NOCOPY VARCHAR2
287 ,p_information20 IN OUT NOCOPY VARCHAR2
288 ,p_information21 IN OUT NOCOPY VARCHAR2
289 ,p_information22 IN OUT NOCOPY VARCHAR2
290 ,p_information23 IN OUT NOCOPY VARCHAR2
291 ,p_information24 IN OUT NOCOPY VARCHAR2
292 ,p_information25 IN OUT NOCOPY VARCHAR2
293 ,p_information26 IN OUT NOCOPY VARCHAR2
294 ,p_information27 IN OUT NOCOPY VARCHAR2
295 ,p_information28 IN OUT NOCOPY VARCHAR2
296 ,p_information29 IN OUT NOCOPY VARCHAR2
297 ,p_information30 IN OUT NOCOPY VARCHAR2) IS
298
299 l_asg_ei_data per_assignment_extra_info%rowtype;
300
301 -- NOCOPY Variables
302 l_information1 per_assignment_extra_info.aei_information1%type := p_information1 ;
303 l_information2 per_assignment_extra_info.aei_information2%type := p_information2 ;
304 l_information3 per_assignment_extra_info.aei_information3%type := p_information3 ;
305 l_information4 per_assignment_extra_info.aei_information4%type := p_information4 ;
306 l_information5 per_assignment_extra_info.aei_information5%type := p_information5 ;
307 l_information6 per_assignment_extra_info.aei_information6%type := p_information6 ;
308 l_information7 per_assignment_extra_info.aei_information7%type := p_information7 ;
309 l_information8 per_assignment_extra_info.aei_information8%type := p_information8 ;
310 l_information9 per_assignment_extra_info.aei_information9%type := p_information9 ;
311 l_information10 per_assignment_extra_info.aei_information10%type := p_information10 ;
312 l_information11 per_assignment_extra_info.aei_information11%type := p_information11 ;
313 l_information12 per_assignment_extra_info.aei_information12%type := p_information12 ;
314 l_information13 per_assignment_extra_info.aei_information13%type := p_information13 ;
315 l_information14 per_assignment_extra_info.aei_information14%type := p_information14 ;
316 l_information15 per_assignment_extra_info.aei_information15%type := p_information15 ;
317 l_information16 per_assignment_extra_info.aei_information16%type := p_information16 ;
318 l_information17 per_assignment_extra_info.aei_information17%type := p_information17 ;
319 l_information18 per_assignment_extra_info.aei_information18%type := p_information18 ;
320 l_information19 per_assignment_extra_info.aei_information19%type := p_information19 ;
321 l_information20 per_assignment_extra_info.aei_information20%type := p_information20 ;
322 l_information21 per_assignment_extra_info.aei_information21%type := p_information21 ;
323 l_information22 per_assignment_extra_info.aei_information22%type := p_information22 ;
324 l_information23 per_assignment_extra_info.aei_information23%type := p_information23 ;
325 l_information24 per_assignment_extra_info.aei_information24%type := p_information24 ;
326 l_information25 per_assignment_extra_info.aei_information25%type := p_information25 ;
327 l_information26 per_assignment_extra_info.aei_information26%type := p_information26 ;
328 l_information27 per_assignment_extra_info.aei_information27%type := p_information27 ;
329 l_information28 per_assignment_extra_info.aei_information28%type := p_information28 ;
330 l_information29 per_assignment_extra_info.aei_information29%type := p_information29 ;
334 ghr_history_fetch.fetch_asgei(
331 l_information30 per_assignment_extra_info.aei_information30%type := p_information30 ;
332
333 BEGIN
335 p_assignment_id => p_assignment_id
336 ,p_information_type => p_information_type
337 ,p_date_effective => p_date_effective
338 ,p_asg_ei_data => l_asg_ei_data);
339
340 p_information1 := l_asg_ei_data.aei_information1;
341 p_information2 := l_asg_ei_data.aei_information2;
342 p_information3 := l_asg_ei_data.aei_information3;
343 p_information4 := l_asg_ei_data.aei_information4;
344 p_information5 := l_asg_ei_data.aei_information5;
345 p_information6 := l_asg_ei_data.aei_information6;
346 p_information7 := l_asg_ei_data.aei_information7;
347 p_information8 := l_asg_ei_data.aei_information8;
348 p_information9 := l_asg_ei_data.aei_information9;
349 p_information10 := l_asg_ei_data.aei_information10;
350 p_information11 := l_asg_ei_data.aei_information11;
351 p_information12 := l_asg_ei_data.aei_information12;
352 p_information13 := l_asg_ei_data.aei_information13;
353 p_information14 := l_asg_ei_data.aei_information14;
354 p_information15 := l_asg_ei_data.aei_information15;
355 p_information16 := l_asg_ei_data.aei_information16;
356 p_information17 := l_asg_ei_data.aei_information17;
357 p_information18 := l_asg_ei_data.aei_information18;
358 p_information19 := l_asg_ei_data.aei_information19;
359 p_information20 := l_asg_ei_data.aei_information20;
360 p_information21 := l_asg_ei_data.aei_information21;
361 p_information22 := l_asg_ei_data.aei_information22;
362 p_information23 := l_asg_ei_data.aei_information23;
363 p_information24 := l_asg_ei_data.aei_information24;
364 p_information25 := l_asg_ei_data.aei_information25;
365 p_information26 := l_asg_ei_data.aei_information26;
366 p_information27 := l_asg_ei_data.aei_information27;
367 p_information28 := l_asg_ei_data.aei_information28;
368 p_information29 := l_asg_ei_data.aei_information29;
369 p_information30 := l_asg_ei_data.aei_information30;
370 EXCEPTION
371 WHEN OTHERS THEN
372 p_information1 := l_information1 ;
373 p_information2 := l_information2 ;
374 p_information3 := l_information3 ;
375 p_information4 := l_information4 ;
376 p_information5 := l_information5 ;
377 p_information6 := l_information6 ;
378 p_information7 := l_information7 ;
379 p_information8 := l_information8 ;
380 p_information9 := l_information9 ;
381 p_information10 := l_information10;
382 p_information11 := l_information11;
383 p_information12 := l_information12;
384 p_information13 := l_information13;
385 p_information14 := l_information14;
386 p_information15 := l_information15;
387 p_information16 := l_information16;
388 p_information17 := l_information17;
389 p_information18 := l_information18;
390 p_information19 := l_information19;
391 p_information20 := l_information20;
392 p_information21 := l_information21;
393 p_information22 := l_information22;
394 p_information23 := l_information23;
395 p_information24 := l_information24;
396 p_information25 := l_information25;
397 p_information26 := l_information26;
398 p_information27 := l_information27;
399 p_information28 := l_information28;
400 p_information29 := l_information29;
401 p_information30 := l_information30;
402 END fetch_asgei;
403 --
404
405 -- Fetch Position EI
406 PROCEDURE fetch_positionei(
407 p_position_id IN NUMBER
408 ,p_information_type IN VARCHAR2
409 ,p_date_effective IN DATE
410 ,p_information1 IN OUT NOCOPY VARCHAR2
411 ,p_information2 IN OUT NOCOPY VARCHAR2
412 ,p_information3 IN OUT NOCOPY VARCHAR2
413 ,p_information4 IN OUT NOCOPY VARCHAR2
414 ,p_information5 IN OUT NOCOPY VARCHAR2
415 ,p_information6 IN OUT NOCOPY VARCHAR2
416 ,p_information7 IN OUT NOCOPY VARCHAR2
417 ,p_information8 IN OUT NOCOPY VARCHAR2
418 ,p_information9 IN OUT NOCOPY VARCHAR2
419 ,p_information10 IN OUT NOCOPY VARCHAR2
420 ,p_information11 IN OUT NOCOPY VARCHAR2
421 ,p_information12 IN OUT NOCOPY VARCHAR2
422 ,p_information13 IN OUT NOCOPY VARCHAR2
423 ,p_information14 IN OUT NOCOPY VARCHAR2
424 ,p_information15 IN OUT NOCOPY VARCHAR2
425 ,p_information16 IN OUT NOCOPY VARCHAR2
426 ,p_information17 IN OUT NOCOPY VARCHAR2
427 ,p_information18 IN OUT NOCOPY VARCHAR2
428 ,p_information19 IN OUT NOCOPY VARCHAR2
429 ,p_information20 IN OUT NOCOPY VARCHAR2
430 ,p_information21 IN OUT NOCOPY VARCHAR2
431 ,p_information22 IN OUT NOCOPY VARCHAR2
432 ,p_information23 IN OUT NOCOPY VARCHAR2
433 ,p_information24 IN OUT NOCOPY VARCHAR2
434 ,p_information25 IN OUT NOCOPY VARCHAR2
435 ,p_information26 IN OUT NOCOPY VARCHAR2
436 ,p_information27 IN OUT NOCOPY VARCHAR2
437 ,p_information28 IN OUT NOCOPY VARCHAR2
438 ,p_information29 IN OUT NOCOPY VARCHAR2
439 ,p_information30 IN OUT NOCOPY VARCHAR2) IS
440
441 l_pos_ei_data per_position_extra_info%rowtype;
442 -- NOCOPY Changes
443 l_information1 per_position_extra_info.poei_information1%type := p_information1 ;
447 l_information5 per_position_extra_info.poei_information5%type := p_information5 ;
444 l_information2 per_position_extra_info.poei_information2%type := p_information2 ;
445 l_information3 per_position_extra_info.poei_information3%type := p_information3 ;
446 l_information4 per_position_extra_info.poei_information4%type := p_information4 ;
448 l_information6 per_position_extra_info.poei_information6%type := p_information6 ;
449 l_information7 per_position_extra_info.poei_information7%type := p_information7 ;
450 l_information8 per_position_extra_info.poei_information8%type := p_information8 ;
451 l_information9 per_position_extra_info.poei_information9%type := p_information9 ;
452 l_information10 per_position_extra_info.poei_information10%type := p_information10 ;
453 l_information11 per_position_extra_info.poei_information11%type := p_information11 ;
454 l_information12 per_position_extra_info.poei_information12%type := p_information12 ;
455 l_information13 per_position_extra_info.poei_information13%type := p_information13 ;
456 l_information14 per_position_extra_info.poei_information14%type := p_information14 ;
457 l_information15 per_position_extra_info.poei_information15%type := p_information15 ;
458 l_information16 per_position_extra_info.poei_information16%type := p_information16 ;
459 l_information17 per_position_extra_info.poei_information17%type := p_information17 ;
460 l_information18 per_position_extra_info.poei_information18%type := p_information18 ;
461 l_information19 per_position_extra_info.poei_information19%type := p_information19 ;
462 l_information20 per_position_extra_info.poei_information20%type := p_information20 ;
463 l_information21 per_position_extra_info.poei_information21%type := p_information21 ;
464 l_information22 per_position_extra_info.poei_information22%type := p_information22 ;
465 l_information23 per_position_extra_info.poei_information23%type := p_information23 ;
466 l_information24 per_position_extra_info.poei_information24%type := p_information24 ;
467 l_information25 per_position_extra_info.poei_information25%type := p_information25 ;
468 l_information26 per_position_extra_info.poei_information26%type := p_information26 ;
469 l_information27 per_position_extra_info.poei_information27%type := p_information27 ;
470 l_information28 per_position_extra_info.poei_information28%type := p_information28 ;
471 l_information29 per_position_extra_info.poei_information29%type := p_information29 ;
472 l_information30 per_position_extra_info.poei_information30%type := p_information30 ;
473
474 BEGIN
475 ghr_history_fetch.fetch_positionei(
476 p_position_id => p_position_id
477 ,p_information_type => p_information_type
478 ,p_date_effective => p_date_effective
479 ,p_pos_ei_data => l_pos_ei_data);
480
481 p_information1 := l_pos_ei_data.poei_information1;
482 p_information2 := l_pos_ei_data.poei_information2;
483 p_information3 := l_pos_ei_data.poei_information3;
484 p_information4 := l_pos_ei_data.poei_information4;
485 p_information5 := l_pos_ei_data.poei_information5;
486 p_information6 := l_pos_ei_data.poei_information6;
487 p_information7 := l_pos_ei_data.poei_information7;
488 p_information8 := l_pos_ei_data.poei_information8;
489 p_information9 := l_pos_ei_data.poei_information9;
490 p_information10 := l_pos_ei_data.poei_information10;
491 p_information11 := l_pos_ei_data.poei_information11;
495 p_information15 := l_pos_ei_data.poei_information15;
492 p_information12 := l_pos_ei_data.poei_information12;
493 p_information13 := l_pos_ei_data.poei_information13;
494 p_information14 := l_pos_ei_data.poei_information14;
496 p_information16 := l_pos_ei_data.poei_information16;
497 p_information17 := l_pos_ei_data.poei_information17;
498 p_information18 := l_pos_ei_data.poei_information18;
499 p_information19 := l_pos_ei_data.poei_information19;
500 p_information20 := l_pos_ei_data.poei_information20;
501 p_information21 := l_pos_ei_data.poei_information21;
502 p_information22 := l_pos_ei_data.poei_information22;
503 p_information23 := l_pos_ei_data.poei_information23;
504 p_information24 := l_pos_ei_data.poei_information24;
505 p_information25 := l_pos_ei_data.poei_information25;
506 p_information26 := l_pos_ei_data.poei_information26;
507 p_information27 := l_pos_ei_data.poei_information27;
508 p_information28 := l_pos_ei_data.poei_information28;
509 p_information29 := l_pos_ei_data.poei_information29;
510 p_information30 := l_pos_ei_data.poei_information30;
511
512 EXCEPTION
513 WHEN OTHERS THEN
514 p_information1 := l_information1 ;
515 p_information2 := l_information2 ;
516 p_information3 := l_information3 ;
517 p_information4 := l_information4 ;
518 p_information5 := l_information5 ;
519 p_information6 := l_information6 ;
520 p_information7 := l_information7 ;
521 p_information8 := l_information8 ;
522 p_information9 := l_information9 ;
523 p_information10 := l_information10;
524 p_information11 := l_information11;
525 p_information12 := l_information12;
526 p_information13 := l_information13;
527 p_information14 := l_information14;
528 p_information15 := l_information15;
529 p_information16 := l_information16;
530 p_information17 := l_information17;
531 p_information18 := l_information18;
532 p_information19 := l_information19;
533 p_information20 := l_information20;
534 p_information21 := l_information21;
535 p_information22 := l_information22;
536 p_information23 := l_information23;
537 p_information24 := l_information24;
538 p_information25 := l_information25;
539 p_information26 := l_information26;
540 p_information27 := l_information27;
541 p_information28 := l_information28;
542 p_information29 := l_information29;
543 p_information30 := l_information30;
544 END fetch_positionei;
545 --
546
547 -- Get POI Desc
548 FUNCTION get_poi_desc (p_personnel_office_id IN NUMBER)
549 RETURN VARCHAR2 IS
550 --
551 CURSOR cur_poi IS
552 SELECT poi.description
553 FROM ghr_pois poi
554 WHERE poi.personnel_office_id = p_personnel_office_id;
555
556 BEGIN
557 FOR cur_poi_rec IN cur_poi LOOP
558 RETURN(cur_poi_rec.description);
559 END LOOP;
560 --
561 RETURN(NULL);
562 --
563 END get_poi_desc;
564 --
565
566 -- Get Grade Details
567 PROCEDURE get_grade_details (p_grade_id IN NUMBER
568 ,p_grade_name IN OUT NOCOPY VARCHAR2
569 ,p_pay_plan IN OUT NOCOPY VARCHAR2
570 ,p_grade_or_level IN OUT NOCOPY VARCHAR2) IS
571 --
572 CURSOR cur_grd IS
573 SELECT gdf.segment1 pay_plan
574 ,gdf.segment2 grade_or_level
575 ,grd.name grade_name
576 FROM per_grade_definitions gdf
577 ,per_grades grd
578 WHERE grd.grade_id = p_grade_id
579 AND grd.grade_definition_id = gdf.grade_definition_id;
580 --
581 l_grade_name per_grades.name%type := p_grade_name;
582 l_pay_plan per_grade_definitions.segment1%type := p_pay_plan;
583 l_grade_or_level per_grade_definitions.segment2%type := p_grade_or_level;
584
585 BEGIN
586
587 FOR cur_grd_rec IN cur_grd LOOP
588 p_grade_name := cur_grd_rec.grade_name;
589 p_pay_plan := cur_grd_rec.pay_plan;
590 p_grade_or_level := cur_grd_rec.grade_or_level;
591 END LOOP;
592 EXCEPTION
593 WHEN OTHERS THEN
594 p_grade_name := l_grade_name;
595 p_pay_plan := l_pay_plan;
596 p_grade_or_level := l_grade_or_level;
597
598 END get_grade_details;
599 --
600
601 -- Get Consolidated Count
602 FUNCTION get_consolidated_count (p_consolidated_complaint_id IN NUMBER
603 ,p_complaint_id IN NUMBER)
604 RETURN NUMBER IS
605 --
606 CURSOR cur_cmp IS
607 SELECT count(*) rec_count
608 FROM ghr_complaints2 cmp
609 WHERE cmp.consolidated_complaint_id = p_consolidated_complaint_id
610 AND (cmp.complaint_id <> p_complaint_id OR p_complaint_id IS NULL);
611
612 BEGIN
616 --
613 FOR cur_cmp_rec IN cur_cmp LOOP
614 RETURN(cur_cmp_rec.rec_count);
615 END LOOP;
617 RETURN(NULL);
618 --
619 END get_consolidated_count;
620 --
621
622 -- Get Docket Number
623 FUNCTION get_docket_number (p_consolidated_complaint_id IN NUMBER)
624 RETURN VARCHAR2 IS
625 --
626 CURSOR cur_cmp IS
627 SELECT docket_number
628 FROM ghr_complaints2 cmp
629 WHERE cmp.consolidated_complaint_id = p_consolidated_complaint_id;
630
631 BEGIN
632 FOR cur_cmp_rec IN cur_cmp LOOP
633 RETURN(cur_cmp_rec.docket_number);
634 END LOOP;
635 --
636 RETURN(NULL);
637 --
638 END get_docket_number;
639 --
640
641 -- Mixed Complaint
642 FUNCTION mixed_complaint(p_complaint_id IN NUMBER)
643 RETURN BOOLEAN IS
644 --
645 CURSOR cur_clm IS
646 SELECT 1
647 FROM ghr_compl_claims clm
648 WHERE clm.complaint_id = p_complaint_id
649 AND clm.mixed_flag = 'Y';
650 --
651 BEGIN
652 FOR cur_clm_rec IN cur_clm LOOP
653 RETURN(TRUE);
654 END LOOP;
655 --
656 RETURN(FALSE);
657 --
658 END mixed_complaint;
659 --
660
661 -- Remand Complaint
662 FUNCTION remand_complaint(p_complaint_id IN NUMBER)
663 RETURN BOOLEAN IS
664 --
665 -- Must use only Max Date Appeal for code test
666 CURSOR cur_rmd IS
667 SELECT 1
668 FROM ghr_compl_appeals cap
669 WHERE cap.complaint_id = p_complaint_id
670 AND cap.appeal_date = (SELECT MAX(cmp.appeal_date)
671 FROM ghr_compl_appeals cmp
672 WHERE cmp.complaint_id = p_complaint_id)
673 AND cap.decision IN ('30', '40');
674 --
675 BEGIN
676 FOR cur_rmd_rec IN cur_rmd LOOP
677 RETURN(TRUE);
678 END LOOP;
679 --
680 RETURN(FALSE);
681 --
682 END remand_complaint;
683 --
684
685 -- Get Window Title
686 FUNCTION get_window_title(p_complaint_id IN NUMBER
687 ,p_effective_date IN DATE)
688 RETURN VARCHAR2 IS
689 --
690 l_full_name per_people_f.full_name%TYPE := NULL;
691 l_ssn per_people_f.national_identifier%TYPE := NULL;
692 l_complainant_person_id ghr_complaints2.complainant_person_id%TYPE := NULL;
693 l_docket_number ghr_complaints2.docket_number%TYPE := NULL;
694
695 CURSOR cur_cmp IS
696 SELECT complainant_person_id,docket_number
697 FROM ghr_complaints2 cmp
698 WHERE cmp.complaint_id = p_complaint_id;
699 --
700 BEGIN
701
702 FOR cur_cmp_rec IN cur_cmp LOOP
703 l_complainant_person_id := cur_cmp_rec.complainant_person_id;
704 l_docket_number := cur_cmp_rec.docket_number;
705 END LOOP;
706 --
707 ghr_complaints2_pkg.get_fullname_ssn(l_complainant_person_id
708 ,p_effective_date
709 ,l_full_name
710 ,l_ssn);
711
712 RETURN(l_docket_number||' '||l_full_name||' '||l_ssn);
713 --
714 END get_window_title;
715 --
716
717 -- Get Lookup Code
718 FUNCTION get_lookup_code(
719 p_application_id fnd_common_lookups.application_id%TYPE
720 ,p_lookup_type fnd_common_lookups.lookup_type%TYPE
721 ,p_meaning fnd_common_lookups.meaning%TYPE
722 )
723 RETURN VARCHAR2 IS
724 --
725 l_ret_val fnd_common_lookups.lookup_code%TYPE := NULL;
726
727 CURSOR cur_loc IS
728 SELECT loc.lookup_code
729 FROM fnd_common_lookups loc
730 WHERE loc.application_id = p_application_id
731 AND loc.lookup_type = p_lookup_type
732 AND loc.meaning = p_meaning;
733 --
734 BEGIN
735
736 FOR cur_loc_rec IN cur_loc LOOP
737 l_ret_val := cur_loc_rec.lookup_code;
738 END LOOP;
739 --
740 RETURN(l_ret_val);
741 --
742 END get_lookup_code;
743
744 -- Get Lookup Meaning
745 FUNCTION get_lookup_meaning(
746 p_application_id fnd_common_lookups.application_id%TYPE
747 ,p_lookup_type fnd_common_lookups.lookup_type%TYPE
748 ,p_lookup_code fnd_common_lookups.lookup_code%TYPE
749 )
750 RETURN VARCHAR2 IS
751 --
752 l_ret_val fnd_common_lookups.meaning%TYPE := NULL;
753
754 CURSOR cur_loc IS
755 SELECT loc.meaning
756 FROM fnd_common_lookups loc
757 WHERE loc.application_id = p_application_id
758 AND loc.lookup_type = p_lookup_type
759 AND loc.lookup_code = p_lookup_code;
760 --
761 BEGIN
762
763 FOR cur_loc_rec IN cur_loc LOOP
764 l_ret_val := cur_loc_rec.meaning;
765 END LOOP;
766 --
767 RETURN(l_ret_val);
768 --
769 END get_lookup_meaning;
770 --
771
772 -- Test Existence of Corrective Action Records.
773 FUNCTION ca_rec_exists(p_complaint_id IN NUMBER)
774 RETURN BOOLEAN IS
775 --
776 --
777 CURSOR cur_cdt IS
778 SELECT 1
779 FROM ghr_compl_ca_details cdt
780 WHERE cdt.compl_ca_header_id in (select compl_ca_header_id
781 from ghr_compl_ca_headers
782 where complaint_id = p_complaint_id);
783 --
784 BEGIN
785 FOR cur_cdt_rec IN cur_cdt LOOP
789 RETURN(FALSE);
786 RETURN(TRUE);
787 END LOOP;
788 --
790 --
791 END ca_rec_exists;
792
793
794 -- Test Existence of Claim for Letter Date.
795 FUNCTION ltr_claim_chk(p_complaint_id IN NUMBER)
796 RETURN BOOLEAN IS
797 --
798 --
799 CURSOR cur_clm IS
800 SELECT 1
801 FROM ghr_compl_claims clm, ghr_compl_bases cba
802 WHERE clm.complaint_id = p_complaint_id
803 AND clm.compl_claim_id = cba.compl_claim_id
804 AND clm.phase in ('20','30')
805 AND cba.basis is NOT NULL;
806 --
807 BEGIN
808 FOR cur_clm_rec IN cur_clm LOOP
809 RETURN(TRUE);
810 END LOOP;
811 --
812 RETURN(FALSE);
813 --
814 END ltr_claim_chk;
815
816 -- Claim Check for RR Ltr Received and Pre-Com Initiated.
817 FUNCTION claim_chk(p_complaint_id IN NUMBER)
818 RETURN BOOLEAN IS
819 --
820 --
821 CURSOR cur_clm2 IS
822 SELECT 1
823 FROM ghr_compl_claims clm, ghr_compl_bases cba
824 WHERE clm.complaint_id = p_complaint_id
825 AND clm.compl_claim_id = cba.compl_claim_id
826 AND clm.phase in ('10','30')
827 AND cba.basis is NOT NULL;
828 --
829 BEGIN
830 FOR cur_clm2_rec IN cur_clm2 LOOP
831 RETURN(TRUE);
832 END LOOP;
833 --
834 RETURN(FALSE);
835 --
836 END claim_chk;
837
838 -- Test if COMPLAINTS.PCOM_INIT is NOT NULL
839 FUNCTION pcom_init_chk(p_complaint_id IN NUMBER)
840 RETURN BOOLEAN IS
841 --
842 --
843 CURSOR cur_cmp IS
844 SELECT 1
845 FROM ghr_complaints2 cmp
846 WHERE cmp.complaint_id = p_complaint_id
847 AND cmp.pcom_init is NOT NULL;
848 --
849 BEGIN
850 FOR cur_cmp_rec IN cur_cmp LOOP
851 RETURN(TRUE);
852 END LOOP;
853 --
854 RETURN(FALSE);
855 --
856 END pcom_init_chk;
857
858 -- Test bases exist for a claim
859 FUNCTION basis_chk(p_compl_claim_id IN NUMBER)
860 RETURN BOOLEAN IS
861 --
862 --
863 CURSOR cur_cba IS
864 SELECT 1
865 FROM ghr_compl_bases cba
866 WHERE cba.compl_claim_id = p_compl_claim_id;
867 --
868 BEGIN
869 FOR cur_cba_rec IN cur_cba LOOP
870 RETURN(TRUE);
871 END LOOP;
872 --
873 RETURN(FALSE);
874 --
875 END basis_chk;
876
877 -- Return true if a claim exists for the complaint_id passed.
878 FUNCTION claim_exists(p_complaint_id IN NUMBER)
879 RETURN BOOLEAN IS
880 --
881 --
882 CURSOR cur_clm IS
883 SELECT 1
884 FROM ghr_compl_claims clm
885 WHERE clm.complaint_id = p_complaint_id;
886 --
887 BEGIN
888 FOR cur_clm_rec IN cur_clm LOOP
889 RETURN(TRUE);
890 END LOOP;
891 --
892 RETURN(FALSE);
893 --
894 END claim_exists;
895
896
897 END ghr_complaints2_pkg;