[Home] [Help]
PACKAGE BODY: APPS.PER_FR_WORK_CERT
Source
1 PACKAGE BODY per_fr_work_cert AS
2 /* $Header: pefrwkct.pkb 120.0 2005/05/31 09:08:26 appldev noship $ */
3 --
4 -- Cursor To Fetch Employee Job Details
5 --
6 CURSOR c_get_jobs(p_person_id NUMBER
7 ,p_period_of_service_id NUMBER) IS
8 --
9 SELECT job.job_id,
10 job.job_definition_id,
11 to_char(asg.effective_start_date, 'DD-MON-YYYY'),
12 to_char(asg.effective_end_date, 'DD-MON-YYYY')
13 FROM per_jobs job,
14 per_all_assignments_f asg
15 WHERE asg.person_id = p_person_id
16 AND asg.job_id = job.job_id
17 AND asg.period_of_service_id = p_period_of_service_id
18 AND asg.primary_flag = 'Y'
19 ORDER BY asg.effective_start_date;
20 --
21 -- Function To Return Employees Job Details
22 --
23 FUNCTION get_job_details(p_person_id NUMBER
24 ,p_period_of_service_id NUMBER) RETURN VARCHAR2 IS
25 --
26 l_job_name1 per_jobs_tl.name%TYPE;
27 l_job_name2 per_jobs_tl.name%TYPE;
28 l_job_name3 per_jobs_tl.name%TYPE;
29 l_job_name4 per_jobs_tl.name%TYPE;
30 l_job_name5 per_jobs_tl.name%TYPE;
31 --
32 l_job_name6 per_jobs_tl.name%TYPE;
33 l_job_name7 per_jobs_tl.name%TYPE;
34 l_job_name8 per_jobs_tl.name%TYPE;
35 l_job_name9 per_jobs_tl.name%TYPE;
36 l_job_name10 per_jobs_tl.name%TYPE;
37 --
38 l_job_start_date1 VARCHAR2(11);
39 l_job_start_date2 VARCHAR2(11);
40 l_job_start_date3 VARCHAR2(11);
41 l_job_start_date4 VARCHAR2(11);
42 l_job_start_date5 VARCHAR2(11);
43 --
44 l_job_start_date6 VARCHAR2(11);
45 l_job_start_date7 VARCHAR2(11);
46 l_job_start_date8 VARCHAR2(11);
47 l_job_start_date9 VARCHAR2(11);
48 l_job_start_date10 VARCHAR2(11);
49 --
50 l_job_end_date1 VARCHAR2(11);
51 l_job_end_date2 VARCHAR2(11);
52 l_job_end_date3 VARCHAR2(11);
53 l_job_end_date4 VARCHAR2(11);
54 l_job_end_date5 VARCHAR2(11);
55 --
56 l_job_end_date6 VARCHAR2(11);
57 l_job_end_date7 VARCHAR2(11);
58 l_job_end_date8 VARCHAR2(11);
59 l_job_end_date9 VARCHAR2(11);
60 l_job_end_date10 VARCHAR2(11);
61 -- 3815632
62 l_job_id1 per_jobs.job_id%TYPE;
63 l_job_id2 per_jobs.job_id%TYPE;
64 l_job_id3 per_jobs.job_id%TYPE;
65 l_job_id4 per_jobs.job_id%TYPE;
66 l_job_id5 per_jobs.job_id%TYPE;
67 --
68 l_job_id6 per_jobs.job_id%TYPE;
69 l_job_id7 per_jobs.job_id%TYPE;
70 l_job_id8 per_jobs.job_id%TYPE;
71 l_job_id9 per_jobs.job_id%TYPE;
72 l_job_id10 per_jobs.job_id%TYPE;
73 --
74 l_job_definition_id1 per_jobs.job_definition_id%TYPE;
75 l_job_definition_id2 per_jobs.job_definition_id%TYPE;
76 l_job_definition_id3 per_jobs.job_definition_id%TYPE;
77 l_job_definition_id4 per_jobs.job_definition_id%TYPE;
78 l_job_definition_id5 per_jobs.job_definition_id%TYPE;
79 --
80 l_job_definition_id6 per_jobs.job_definition_id%TYPE;
81 l_job_definition_id7 per_jobs.job_definition_id%TYPE;
82 l_job_definition_id8 per_jobs.job_definition_id%TYPE;
83 l_job_definition_id9 per_jobs.job_definition_id%TYPE;
84 l_job_definition_id10 per_jobs.job_definition_id%TYPE;
85 -- 3815632
86
87 l_concat_job_details VARCHAR2(3000);
88 l_prev_job_end_date DATE;
89 --
90 BEGIN
91 --
92 -- Find all Employee Job Information
93 --
94 OPEN c_get_jobs(p_person_id
95 ,p_period_of_service_id);
96 --
97 -- Job 1 Details
98 --
99 FETCH c_get_jobs into l_job_id1, l_job_definition_id1, l_job_start_date1, l_job_end_date1;
100 if l_job_definition_id1 is not null then
101 l_job_name1 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id1,
102 p_job_definition_id => l_job_definition_id1,
103 p_report_name => 'WORK_CERTIFICATE');
104 else
105 l_job_name1 := null;
106 end if;
107
108 --
109 -- Job 2 Details
110 --
111 FETCH c_get_jobs into l_job_id2, l_job_definition_id2, l_job_start_date2, l_job_end_date2;
112 if l_job_definition_id2 is not null then
113 l_job_name2 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id2,
114 p_job_definition_id => l_job_definition_id2,
115 p_report_name => 'WORK_CERTIFICATE');
116 else
117 l_job_name2 := null;
118 end if;
119 --
120 -- If the job names are equal, a change in the assignment has occurred but
121 -- not a change of job. Therefore enter loop to find the next job. If
122 -- the job names do not equal, job2 is found and bypass loop.
123 --
124 IF l_job_name2 = l_job_name1 THEN
125 LOOP
126 EXIT when l_job_name2 <> l_job_name1 or c_get_jobs%NOTFOUND;
127 l_prev_job_end_date := l_job_end_date2;
128 FETCH c_get_jobs into l_job_id2, l_job_definition_id2, l_job_start_date2, l_job_end_date2;
129 if l_job_definition_id2 is not null then
130 l_job_name2 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id2,
131 p_job_definition_id => l_job_definition_id2,
132 p_report_name => 'WORK_CERTIFICATE');
133 else
134 l_job_name2 := null;
135 end if;
136 IF c_get_jobs%NOTFOUND THEN
137 l_job_end_date1 := l_job_end_date2;
138 l_job_name2 := null;
139 l_job_start_date2 := null;
140 l_job_end_date2 := null;
141 ELSE
142 l_job_end_date1 := l_prev_job_end_date;
143 END IF;
144 END LOOP;
145 END IF;
146 --
147 -- Job 3 Details
148 --
149 FETCH c_get_jobs into l_job_id3, l_job_definition_id3, l_job_start_date3, l_job_end_date3;
150 if l_job_definition_id3 is not null then
151 l_job_name3 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id3,
152 p_job_definition_id => l_job_definition_id3,
153 p_report_name => 'WORK_CERTIFICATE');
154 else
155 l_job_name3 := null;
156 end if;
157 --
158 -- If the job names are equal, a change in the assignment has occurred but
159 -- not a change of job. Therefore enter loop to find the next job. If
160 -- the job names do not equal, job3 is found and bypass loop.
161 --
162 IF l_job_name3 = l_job_name2 THEN
163 LOOP
164 EXIT when l_job_name3 <> l_job_name2 or c_get_jobs%NOTFOUND;
165 l_prev_job_end_date := l_job_end_date3;
166 FETCH c_get_jobs into l_job_id3, l_job_definition_id3, l_job_start_date3, l_job_end_date3;
167 if l_job_definition_id3 is not null then
168 l_job_name3 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id3,
169 p_job_definition_id => l_job_definition_id3,
170 p_report_name => 'WORK_CERTIFICATE');
171 else
172 l_job_name3 := null;
173 end if;
174 IF c_get_jobs%NOTFOUND THEN
175 l_job_end_date2 := l_job_end_date3;
176 l_job_name3 := null;
177 l_job_start_date3 := null;
178 l_job_end_date3 := null;
179 ELSE
180 l_job_end_date2 := l_prev_job_end_date;
181 END IF;
182 END Loop;
183 END IF;
184 --
185 -- Job 4 Details
186 --
187 FETCH c_get_jobs into l_job_id4, l_job_definition_id4, l_job_start_date4, l_job_end_date4;
188 if l_job_definition_id4 is not null then
189 l_job_name4 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id4,
190 p_job_definition_id => l_job_definition_id4,
191 p_report_name => 'WORK_CERTIFICATE');
192 else
193 l_job_name4 := null;
194 end if;
195 --
196 -- If the job names are equal, a change in the assignment has occurred but
197 -- not a change of job. Therefore enter loop to find the next job. If
198 -- the job names do not equal, job4 is found and bypass loop.
199 --
200 IF l_job_name4 = l_job_name3 THEN
201 LOOP
202 EXIT when l_job_name4 <> l_job_name3 or c_get_jobs%NOTFOUND;
203 l_prev_job_end_date := l_job_end_date4;
204 FETCH c_get_jobs into l_job_id4, l_job_definition_id4, l_job_start_date4, l_job_end_date4;
205 if l_job_definition_id4 is not null then
206 l_job_name4 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id4,
207 p_job_definition_id => l_job_definition_id4,
208 p_report_name => 'WORK_CERTIFICATE');
209 else
210 l_job_name4 := null;
211 end if;
212 IF c_get_jobs%NOTFOUND THEN
213 l_job_end_date3 := l_job_end_date4;
214 l_job_name4 := null;
215 l_job_start_date4 := null;
216 l_job_end_date4 := null;
217 ELSE
218 l_job_end_date3 := l_prev_job_end_date;
219 END IF;
220 END LOOP;
221 END IF;
222 --
223 -- Job 5 Details
224 --
225 FETCH c_get_jobs into l_job_id5, l_job_definition_id5, l_job_start_date5, l_job_end_date5;
226 if l_job_definition_id5 is not null then
227 l_job_name5 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id5,
228 p_job_definition_id => l_job_definition_id5,
229 p_report_name => 'WORK_CERTIFICATE');
230 else
231 l_job_name5 := null;
232 end if;
233 --
234 -- If the job names are equal, a change in the assignment has occurred but
235 -- not a change of job. Therefore enter loop to find the next job. If
236 -- the job names do not equal, job5 is found and bypass loop.
237 --
238 IF l_job_name5 = l_job_name4 THEN
239 LOOP
240 EXIT when l_job_name5 <> l_job_name4 or c_get_jobs%NOTFOUND;
241 l_prev_job_end_date := l_job_end_date5;
242 FETCH c_get_jobs into l_job_id5, l_job_definition_id5, l_job_start_date5, l_job_end_date5;
243 if l_job_definition_id5 is not null then
244 l_job_name5 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id5,
245 p_job_definition_id => l_job_definition_id5,
246 p_report_name => 'WORK_CERTIFICATE');
247 else
248 l_job_name5 := null;
249 end if;
250 IF c_get_jobs%NOTFOUND THEN
251 l_job_end_date4 := l_job_end_date5;
252 l_job_name5 := null;
253 l_job_start_date5 := null;
254 l_job_end_date5 := null;
255 ELSE
256 l_job_end_date4 := l_prev_job_end_date;
257 END IF;
258 END LOOP;
259 END IF;
260 --
261 --
262 -- Job 6 Details
263 --
264 FETCH c_get_jobs into l_job_id6, l_job_definition_id6, l_job_start_date6, l_job_end_date6;
265 if l_job_definition_id6 is not null then
266 l_job_name6 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id6,
267 p_job_definition_id => l_job_definition_id6,
268 p_report_name => 'WORK_CERTIFICATE');
269 else
270 l_job_name6 := null;
271 end if;
272 --
273 -- If the job names are equal, a change in the assignment has occurred but
274 -- not a change of job. Therefore enter loop to find the next job. If
275 -- the job names do not equal, job6 is found and bypass loop.
276 --
277 IF l_job_name6 = l_job_name5 THEN
278 LOOP
279 EXIT when l_job_name6 <> l_job_name5 or c_get_jobs%NOTFOUND;
280 l_prev_job_end_date := l_job_end_date6;
281 FETCH c_get_jobs into l_job_id6, l_job_definition_id6, l_job_start_date6, l_job_end_date6;
282 if l_job_definition_id6 is not null then
283 l_job_name6 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id6,
284 p_job_definition_id => l_job_definition_id6,
285 p_report_name => 'WORK_CERTIFICATE');
286 else
287 l_job_name6 := null;
288 end if;
289 IF c_get_jobs%NOTFOUND THEN
290 l_job_end_date5 := l_job_end_date6;
291 l_job_name6 := null;
292 l_job_start_date6 := null;
293 l_job_end_date6 := null;
294 ELSE
295 l_job_end_date5 := l_prev_job_end_date;
296 END IF;
297 END LOOP;
298 END IF;
299 --
300 --
301 --
302 -- Job 7 Details
303 --
304 FETCH c_get_jobs into l_job_id7, l_job_definition_id7, l_job_start_date7, l_job_end_date7;
305 if l_job_definition_id7 is not null then
306 l_job_name7 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id7,
307 p_job_definition_id => l_job_definition_id7,
308 p_report_name => 'WORK_CERTIFICATE');
309 else
310 l_job_name7 := null;
311 end if;
312 --
313 -- If the job names are equal, a change in the assignment has occurred but
317 IF l_job_name7 = l_job_name6 THEN
314 -- not a change of job. Therefore enter loop to find the next job. If
315 -- the job names do not equal, job7 is found and bypass loop.
316 --
318 LOOP
319 EXIT when l_job_name7 <> l_job_name6 or c_get_jobs%NOTFOUND;
320 l_prev_job_end_date := l_job_end_date7;
321 FETCH c_get_jobs into l_job_id7, l_job_definition_id7, l_job_start_date7, l_job_end_date7;
322 if l_job_definition_id7 is not null then
323 l_job_name7 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id7,
324 p_job_definition_id => l_job_definition_id7,
325 p_report_name => 'WORK_CERTIFICATE');
326 else
327 l_job_name7 := null;
328 end if;
329 IF c_get_jobs%NOTFOUND THEN
330 l_job_end_date6 := l_job_end_date7;
331 l_job_name7 := null;
332 l_job_start_date7 := null;
333 l_job_end_date7 := null;
334 ELSE
335 l_job_end_date6 := l_prev_job_end_date;
336 END IF;
337 END LOOP;
338 END IF;
339 --
340 --
341 --
342 --
343 -- Job 8 Details
344 --
345 FETCH c_get_jobs into l_job_id8, l_job_definition_id8, l_job_start_date8, l_job_end_date8;
346 if l_job_definition_id8 is not null then
347 l_job_name8 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id8,
348 p_job_definition_id => l_job_definition_id8,
349 p_report_name => 'WORK_CERTIFICATE');
350 else
351 l_job_name8 := null;
352 end if;
353 --
354 -- If the job names are equal, a change in the assignment has occurred but
355 -- not a change of job. Therefore enter loop to find the next job. If
356 -- the job names do not equal, job8 is found and bypass loop.
357 --
358 IF l_job_name8 = l_job_name7 THEN
359 LOOP
360 EXIT when l_job_name8 <> l_job_name7 or c_get_jobs%NOTFOUND;
361 l_prev_job_end_date := l_job_end_date8;
362 FETCH c_get_jobs into l_job_id8, l_job_definition_id8, l_job_start_date8, l_job_end_date8;
363 if l_job_definition_id8 is not null then
364 l_job_name8 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id8,
365 p_job_definition_id => l_job_definition_id8,
366 p_report_name => 'WORK_CERTIFICATE');
367 else
368 l_job_name8 := null;
369 end if;
370 IF c_get_jobs%NOTFOUND THEN
371 l_job_end_date7 := l_job_end_date8;
372 l_job_name8 := null;
373 l_job_start_date8 := null;
374 l_job_end_date8 := null;
375 ELSE
376 l_job_end_date7 := l_prev_job_end_date;
377 END IF;
378 END LOOP;
379 END IF;
380 --
381 --
382 --
383 --
384 --
385 -- Job 9 Details
386 --
387 FETCH c_get_jobs into l_job_id9, l_job_definition_id9, l_job_start_date9, l_job_end_date9;
388 if l_job_definition_id9 is not null then
389 l_job_name9 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id9,
390 p_job_definition_id => l_job_definition_id9,
391 p_report_name => 'WORK_CERTIFICATE');
392 else
393 l_job_name9 := null;
394 end if;
395 --
396 -- If the job names are equal, a change in the assignment has occurred but
397 -- not a change of job. Therefore enter loop to find the next job. If
398 -- the job names do not equal, job9 is found and bypass loop.
399 --
400 IF l_job_name9 = l_job_name8 THEN
401 LOOP
402 EXIT when l_job_name9 <> l_job_name8 or c_get_jobs%NOTFOUND;
403 l_prev_job_end_date := l_job_end_date9;
404 FETCH c_get_jobs into l_job_id9, l_job_definition_id9, l_job_start_date9, l_job_end_date9;
405 if l_job_definition_id9 is not null then
406 l_job_name9 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id9,
407 p_job_definition_id => l_job_definition_id9,
408 p_report_name => 'WORK_CERTIFICATE');
409 else
410 l_job_name9 := null;
411 end if;
412 IF c_get_jobs%NOTFOUND THEN
413 l_job_end_date8 := l_job_end_date9;
414 l_job_name9 := null;
415 l_job_start_date9 := null;
416 l_job_end_date9 := null;
417 ELSE
418 l_job_end_date8 := l_prev_job_end_date;
419 END IF;
420 END LOOP;
421 END IF;
422 --
423 --
424 --
425 --
426 --
427 --
428 -- Job 10 Details
429 --
430 FETCH c_get_jobs into l_job_id10, l_job_definition_id10, l_job_start_date10, l_job_end_date10;
431 if l_job_definition_id10 is not null then
432 l_job_name10 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id10,
433 p_job_definition_id => l_job_definition_id10,
434 p_report_name => 'WORK_CERTIFICATE');
435 else
436 l_job_name10 := null;
437 end if;
438 --
439 -- If the job names are equal, a change in the assignment has occurred but
440 -- not a change of job. Therefore enter loop to find the next job. If
441 -- the job names do not equal, job10 is found and bypass loop.
442 --
443 IF l_job_name10 = l_job_name9 THEN
444 LOOP
445 EXIT when l_job_name10 <> l_job_name9 or c_get_jobs%NOTFOUND;
446 l_prev_job_end_date := l_job_end_date10;
447 FETCH c_get_jobs into l_job_id10, l_job_definition_id10, l_job_start_date10, l_job_end_date10;
448 if l_job_definition_id10 is not null then
449 l_job_name10 := per_fr_report_utilities.get_job_names(p_job_id => l_job_id10,
450 p_job_definition_id => l_job_definition_id10,
451 p_report_name => 'WORK_CERTIFICATE');
452 else
453 l_job_name10 := null;
454 end if;
455 IF c_get_jobs%NOTFOUND THEN
459 l_job_end_date10 := null;
456 l_job_end_date9 := l_job_end_date10;
457 l_job_name10 := null;
458 l_job_start_date10 := null;
460 ELSE
461 l_job_end_date9 := l_prev_job_end_date;
462 END IF;
463 END LOOP;
464 END IF;
465 --
466 CLOSE c_get_jobs;
467 --
468 -- Concatenate The Job Details
469 --
470 l_concat_job_details := 'JN1,' || l_job_name1 || ',JSD1,' || l_job_start_date1 || ',JED1,'
471 || l_job_end_date1 || ',JN2,' || l_job_name2 || ',JSD2,' || l_job_start_date2
472 || ',JED2,' || l_job_end_date2 || ',JN3,' || l_job_name3 || ',JSD3,'
473 || l_job_start_date3 || ',JED3,' || l_job_end_date3 || ',JN4,' || l_job_name4
474 || ',JSD4,' || l_job_start_date4 || ',JED4,' || l_job_end_date4 || ',JN5,'
475 || l_job_name5 || ',JSD5,' || l_job_start_date5 || ',JED5,' || l_job_end_date5
476 || ',JN6,' || l_job_name6 || ',JSD6,' || l_job_start_date6 || ',JED6,'
477 || l_job_end_date6 || ',JN7,' || l_job_name7 || ',JSD7,' || l_job_start_date7
478 || ',JED7,' || l_job_end_date7 || ',JN8,' || l_job_name8 || ',JSD8,'
479 || l_job_start_date8 || ',JED8,' || l_job_end_date8 || ',JN9,' || l_job_name9
480 || ',JSD9,' || l_job_start_date9 || ',JED9,' || l_job_end_date9 || ',JN10,'
481 || l_job_name10 || ',JSD10,' || l_job_start_date10 || ',JED10,' || l_job_end_date10
482 || ',END';
483 --
484 RETURN l_concat_job_details;
485 --
486 END get_job_details;
487
488 END per_fr_work_cert;