[Home] [Help]
PACKAGE BODY: APPS.PER_QH_TIMELINE
Source
1 PACKAGE BODY per_qh_timeline as
2 /* $Header: peqhtmln.pkb 120.1.12010000.2 2008/08/06 09:30:57 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) :=' per_qh_timeline.';
7 --
8 procedure get_dates
9 (p_field IN VARCHAR2
10 ,p_security_mode IN VARCHAR2
11 ,p_effective_date IN DATE
12 ,p_datetrack_date IN DATE
13 ,p_person_id IN NUMBER
14 ,p_assignment_id IN NUMBER
15 ,p_business_group_id IN NUMBER
16 ,records OUT NOCOPY datetab) is
17 --
18 val_old VARCHAR2(240);
19 val_new VARCHAR2(240);
20 date_start VARCHAR2(240);
21 date_end VARCHAR2(15);
22 --
23 l_column varchar2(240);
24 l_table varchar2(240);
25 l_type varchar2(2);
26 l_primary_key_name varchar2(240);
27 l_primary_key number;
28 --
29 l_select_stmt varchar2(20000);
30 TYPE DateCurTyp is REF CURSOR;
31 date_cv DateCurTyp;
32 l_date_rec daterec;
33 i number;
34 l_sub_query varchar2(20000);
35 l_date_string varchar2(30);
36 l_proc varchar2(72):=g_package||'get_dates';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering: '||l_proc,10);
41 --
42 l_date_string:='to_date('''
43 ||to_char(p_effective_date,'DDMMYYYY')||
44 ''',''DDMMYYYY'')';
45 --
46 hr_utility.set_location(l_proc,20);
47 --
48 -- look for the column name.
49 --
50 if P_FIELD='MAINTAIN.PERSON_TYPE' then
51 l_column:='PERSON_TYPE_ID';
52 l_table:='PER_PERSON_TYPE_USAGES_F';
53 l_type:='N';
54 --bug no 5169311 starts here
55 -- l_sub_query:= NULL;
56 l_sub_query:='select USER_PERSON_TYPE from per_person_types_tl where
57 language=userenv(''LANG'') and PERSON_TYPE_ID=:1';
58 --bug no 5169311 ends here
59
60 ELSIF P_FIELD='MAINTAIN.BACKGROUND_CHK_STAT_MEANING' then
61 l_column:='BACKGROUND_CHK_STATATUS';
62 l_table:='PER_ALL_PEOPLE_F';
63 l_type:='V';
64 l_sub_query:=
65 'select meaning from hr_lookups
66 where lookup_type=''YES_NO''
67 and enabled_flag=''Y''
68 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
69 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
70 ELSIF P_FIELD='MAINTAIN.BLOOD_TYPE_MEANING' then
71 l_column:='BLOOD_TYPE';
72 l_table:='PER_ALL_PEOPLE_F';
73 l_type:='V';
74 l_sub_query:=
75 'select meaning from hr_lookups
76 where lookup_type=''BLOOD_TYPE''
77 and enabled_flag=''Y''
78 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
79 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
80 ELSIF P_FIELD='MAINTAIN.CORR_LANG_MEANING' then
81 l_column:='CORRESPONDENCE_LANGUAGE';
82 l_table:='PER_ALL_PEOPLE_F';
83 l_type:='V';
84 l_sub_query:=
85 'select description
86 from fnd_languages_vl
87 where language_code=:1';
88 ELSIF P_FIELD='MAINTAIN.EXPNSE_CHK_SEND_ADDR_MEANING' then
89 l_column:='EXPNSE_CHECK_SEND_TO_ADDRESS';
90 l_table:='PER_ALL_PEOPLE_F';
91 l_type:='V';
92 l_sub_query:=
93 'select meaning from hr_lookups
94 where lookup_type=''HOME_OFFICE''
95 and enabled_flag=''Y''
96 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
97 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
98 ELSIF P_FIELD='MAINTAIN.MARITAL_STATUS_MEANING' then
99 l_column:='MARITAL_STATUS';
100 l_table:='PER_ALL_PEOPLE_F';
101 l_type:='V';
102 l_sub_query:=
103 'select meaning from hr_lookups
104 where lookup_type=''MAR_STATUS''
105 and enabled_flag=''Y''
106 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
107 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
108 ELSIF P_FIELD='MAINTAIN.NATIONALITY_MEANING' then
109 l_column:='NATIONALITY';
110 l_table:='PER_ALL_PEOPLE_F';
111 l_type:='V';
112 l_sub_query:=
113 'select meaning from hr_lookups
114 where lookup_type=''NATIONALITY''
115 and enabled_flag=''Y''
116 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
117 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
118 ELSIF P_FIELD='MAINTAIN.ON_MILITARY_SERVICE_MEANING' then
119 l_column:='ON_MILITARY_SERVICE';
120 l_table:='PER_ALL_PEOPLE_F';
121 l_type:='V';
122 l_sub_query:=
123 'select meaning from hr_lookups
124 where lookup_type=''YES_NO''
125 and enabled_flag=''Y''
126 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
127 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
128 ELSIF P_FIELD='MAINTAIN.RESUME_EXISTS_MEANING' then
129 l_column:='RESUME_EXISTS';
130 l_table:='PER_ALL_PEOPLE_F';
131 l_type:='V';
132 l_sub_query:=
133 'select meaning from hr_lookups
134 where lookup_type=''YES_NO''
135 and enabled_flag=''Y''
136 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
137 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
138 ELSIF P_FIELD='MAINTAIN.REGISTERED_DISABLED_FLAG' then
139 l_column:='REGISTERED_DISABLED_FLAG';
140 l_table:='PER_ALL_PEOPLE_F';
141 l_type:='V';
142
143 -- Bug 3037019 Start here
144
145 l_sub_query:=
146 'select meaning from hr_lookups
147 where lookup_type=''REGISTERED_DISABLED''
148 and enabled_flag=''Y''
149 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
150 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
151
152 -- Bug 3037019 End here
153
154 ELSIF P_FIELD='MAINTAIN.SECND_PASSPORT_EXSTS_MEANING' then
155 l_column:='SECOND_PASSPORT_EXISTS';
156 l_table:='PER_ALL_PEOPLE_F';
157 l_type:='V';
158 l_sub_query:=
159 'select meaning from hr_lookups
160 where lookup_type=''YES_NO''
161 and enabled_flag=''Y''
162 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
163 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
164 ELSIF P_FIELD='MAINTAIN.SEX_MEANING' then
165 l_column:='SEX';
166 l_table:='PER_ALL_PEOPLE_F';
167 l_type:='V';
168 l_sub_query:=
169 'select meaning from hr_lookups
170 where lookup_type=''SEX''
171 and enabled_flag=''Y''
172 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
173 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
174 ELSIF P_FIELD='MAINTAIN.STUDENT_STATUS_MEANING' then
175 l_column:='STUDENT_STATUS';
176 l_table:='PER_ALL_PEOPLE_F';
177 l_type:='V';
178 l_sub_query:=
179 'select meaning from hr_lookups
180 where lookup_type=''STUDENT_STATUS''
181 and enabled_flag=''Y''
182 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
183 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
184 ELSIF P_FIELD='MAINTAIN.TITLE_MEANING' then
185 l_column:='TITLE';
186 l_table:='PER_ALL_PEOPLE_F';
187 l_type:='V';
188 l_sub_query:=
189 'select meaning from hr_lookups
190 where lookup_type=''TITLE''
191 and enabled_flag=''Y''
192 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
193 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
194 ELSIF P_FIELD='MAINTAIN.WORK_SCHEDULE_MEANING' then
195 l_column:='WORK_SCHEDULE';
196 l_table:='PER_ALL_PEOPLE_F';
197 l_type:='V';
198 l_sub_query:=
199 'select meaning from hr_lookups
200 where lookup_type=''WORK_SCHEDULE''
201 and enabled_flag=''Y''
202 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
203 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
204 ELSIF P_FIELD='MAINTAIN.CORD_BEN_NO_CVG_FLAG_MEANING' then
205 l_column:='COORD_BEN_NO_CVG_FLAG';
206 l_table:='PER_ALL_PEOPLE_F';
207 l_type:='V';
208 l_sub_query:=
209 'select meaning from hr_lookups
210 where lookup_type=''YES_NO''
211 and enabled_flag=''Y''
212 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
213 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
214 ELSIF P_FIELD='MAINTAIN.DPDNT_VLNTRY_SVC_FLG_MEANING' then
215 l_column:='DPDNT_VLNTRY_SVCE_FLAG';
216 l_table:='PER_ALL_PEOPLE_F';
217 l_type:='V';
218 l_sub_query:=
219 'select meaning from hr_lookups
220 where lookup_type=''YES_NO''
221 and enabled_flag=''Y''
222 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
223 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
224 ELSIF P_FIELD='MAINTAIN.USES_TOBACCO_MEANING' then
225 l_column:='USES_TOBACCO';
226 l_table:='PER_ALL_PEOPLE_F';
227 l_type:='V';
228 l_sub_query:=
229 'select meaning from hr_lookups
230 where lookup_type=''YES_NO''
231 and enabled_flag=''Y''
232 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
233 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
234 ELSIF P_FIELD='MAINTAIN.BENEFIT_GROUP' then
235 l_column:='BENEFIT_GROUP_ID';
236 l_table:='PER_ALL_PEOPLE_F';
237 l_type:='N';
238 l_sub_query:=
239 'select name
240 from ben_benfts_grp
241 where benfts_grp_id=:1';
242 ELSIF P_FIELD in ('MAINTAIN.LAST_NAME'
243 ,'MAINTAIN.APPLICANT_NUMBER'
244 ,'MAINTAIN.BACKGROUND_DATE_CHECK'
245 ,'MAINTAIN.EMAIL_ADDRESS'
246 ,'MAINTAIN.EMPLOYEE_NUMBER'
247 ,'MAINTAIN.NPW_NUMBER'
248 ,'MAINTAIN.FIRST_NAME'
249 ,'MAINTAIN.PER_FTE_CAPACITY'
250 ,'MAINTAIN.FULL_NAME'
251 ,'MAINTAIN.HOLD_APPLICANT_DATE_UNTIL'
252 ,'MAINTAIN.HONORS'
253 ,'MAINTAIN.INTERNAL_LOCATION'
254 ,'MAINTAIN.KNOWN_AS'
255 ,'MAINTAIN.LAST_MEDICAL_TEST_BY'
256 ,'MAINTAIN.MAILSTOP'
257 ,'MAINTAIN.MIDDLE_NAMES'
258 ,'MAINTAIN.NATIONAL_IDENTIFIER'
259 ,'MAINTAIN.OFFICE_NUMBER'
260 ,'MAINTAIN.PRE_NAME_ADJUNCT'
261 ,'MAINTAIN.PREVIOUS_LAST_NAME'
262 ,'MAINTAIN.REHIRE_RECOMMENDATION'
263 ,'MAINTAIN.RESUME_LAST_UPDATED'
264 ,'MAINTAIN.SUFFIX'
265 ,'MAINTAIN.COORD_BEN_MED_PLN_NO'
266 ,'MAINTAIN.PER_ATTRIBUTE_CATEGORY'
267 /* ,'MAINTAIN.PER_ATTRIBUTE1_V' -- Commented for fix of #3211345
268 ,'MAINTAIN.PER_ATTRIBUTE2_V'
269 ,'MAINTAIN.PER_ATTRIBUTE3_V'
270 ,'MAINTAIN.PER_ATTRIBUTE4_V'
271 ,'MAINTAIN.PER_ATTRIBUTE5_V'
272 ,'MAINTAIN.PER_ATTRIBUTE6_V'
273 ,'MAINTAIN.PER_ATTRIBUTE7_V'
274 ,'MAINTAIN.PER_ATTRIBUTE8_V'
275 ,'MAINTAIN.PER_ATTRIBUTE9_V'
276 ,'MAINTAIN.PER_ATTRIBUTE10_V'
277 ,'MAINTAIN.PER_ATTRIBUTE11_V'
278 ,'MAINTAIN.PER_ATTRIBUTE12_V'
279 ,'MAINTAIN.PER_ATTRIBUTE13_V'
280 ,'MAINTAIN.PER_ATTRIBUTE14_V'
281 ,'MAINTAIN.PER_ATTRIBUTE15_V'
282 ,'MAINTAIN.PER_ATTRIBUTE16_V'
283 ,'MAINTAIN.PER_ATTRIBUTE17_V'
284 ,'MAINTAIN.PER_ATTRIBUTE18_V'
285 ,'MAINTAIN.PER_ATTRIBUTE19_V'
286 ,'MAINTAIN.PER_ATTRIBUTE20_V'
287 ,'MAINTAIN.PER_ATTRIBUTE21_V'
288 ,'MAINTAIN.PER_ATTRIBUTE22_V'
289 ,'MAINTAIN.PER_ATTRIBUTE23_V'
290 ,'MAINTAIN.PER_ATTRIBUTE24_V'
291 ,'MAINTAIN.PER_ATTRIBUTE25_V'
292 ,'MAINTAIN.PER_ATTRIBUTE26_V'
293 ,'MAINTAIN.PER_ATTRIBUTE27_V'
294 ,'MAINTAIN.PER_ATTRIBUTE28_V'
295 ,'MAINTAIN.PER_ATTRIBUTE29_V'
296 ,'MAINTAIN.PER_ATTRIBUTE30_V'*/
297 ,'MAINTAIN.PER_INFORMATION_CATEGORY'
298 /* ,'MAINTAIN.PER_INFORMATION1_V' -- Commented for fix of #3211345
299 ,'MAINTAIN.PER_INFORMATION2_V'
300 ,'MAINTAIN.PER_INFORMATION3_V'
301 ,'MAINTAIN.PER_INFORMATION4_V'
302 ,'MAINTAIN.PER_INFORMATION5_V'
303 ,'MAINTAIN.PER_INFORMATION6_V'
304 ,'MAINTAIN.PER_INFORMATION7_V'
305 ,'MAINTAIN.PER_INFORMATION8_V'
306 ,'MAINTAIN.PER_INFORMATION9_V'
307 ,'MAINTAIN.PER_INFORMATION10_V'
308 ,'MAINTAIN.PER_INFORMATION11_V'
309 ,'MAINTAIN.PER_INFORMATION12_V'
310 ,'MAINTAIN.PER_INFORMATION13_V'
311 ,'MAINTAIN.PER_INFORMATION14_V'
312 ,'MAINTAIN.PER_INFORMATION15_V'
313 ,'MAINTAIN.PER_INFORMATION16_V'
314 ,'MAINTAIN.PER_INFORMATION17_V'
315 ,'MAINTAIN.PER_INFORMATION18_V'
316 ,'MAINTAIN.PER_INFORMATION19_V'
317 ,'MAINTAIN.PER_INFORMATION20_V'
318 ,'MAINTAIN.PER_INFORMATION21_V'
319 ,'MAINTAIN.PER_INFORMATION22_V'
320 ,'MAINTAIN.PER_INFORMATION23_V'
321 ,'MAINTAIN.PER_INFORMATION24_V'
322 ,'MAINTAIN.PER_INFORMATION25_V'
323 ,'MAINTAIN.PER_INFORMATION26_V'
324 ,'MAINTAIN.PER_INFORMATION27_V'
325 ,'MAINTAIN.PER_INFORMATION28_V'
326 ,'MAINTAIN.PER_INFORMATION29_V'
327 ,'MAINTAIN.PER_INFORMATION30_V'
328 ,'MAINTAIN.PER_INFORMATION30_M'*/
329 ) then
330 l_column:=substrb(p_field,10);
331 l_table:='PER_ALL_PEOPLE_F';
332 l_type:='V';
333 l_sub_query:=null;
334 -- ADDED FOR FIX OF #3211345 START
335 elsif p_field in ('MAINTAIN.PER_ATTRIBUTE1_V'
336 ,'MAINTAIN.PER_ATTRIBUTE2_V'
337 ,'MAINTAIN.PER_ATTRIBUTE3_V'
338 ,'MAINTAIN.PER_ATTRIBUTE4_V'
339 ,'MAINTAIN.PER_ATTRIBUTE5_V'
340 ,'MAINTAIN.PER_ATTRIBUTE6_V'
341 ,'MAINTAIN.PER_ATTRIBUTE7_V'
342 ,'MAINTAIN.PER_ATTRIBUTE8_V'
343 ,'MAINTAIN.PER_ATTRIBUTE9_V'
344 ,'MAINTAIN.PER_ATTRIBUTE10_V'
345 ,'MAINTAIN.PER_ATTRIBUTE11_V'
346 ,'MAINTAIN.PER_ATTRIBUTE12_V'
347 ,'MAINTAIN.PER_ATTRIBUTE13_V'
348 ,'MAINTAIN.PER_ATTRIBUTE14_V'
349 ,'MAINTAIN.PER_ATTRIBUTE15_V'
350 ,'MAINTAIN.PER_ATTRIBUTE16_V'
351 ,'MAINTAIN.PER_ATTRIBUTE17_V'
352 ,'MAINTAIN.PER_ATTRIBUTE18_V'
353 ,'MAINTAIN.PER_ATTRIBUTE19_V'
357 ,'MAINTAIN.PER_ATTRIBUTE23_V'
354 ,'MAINTAIN.PER_ATTRIBUTE20_V'
355 ,'MAINTAIN.PER_ATTRIBUTE21_V'
356 ,'MAINTAIN.PER_ATTRIBUTE22_V'
358 ,'MAINTAIN.PER_ATTRIBUTE24_V'
359 ,'MAINTAIN.PER_ATTRIBUTE25_V'
360 ,'MAINTAIN.PER_ATTRIBUTE26_V'
361 ,'MAINTAIN.PER_ATTRIBUTE27_V'
362 ,'MAINTAIN.PER_ATTRIBUTE28_V'
363 ,'MAINTAIN.PER_ATTRIBUTE29_V'
364 ,'MAINTAIN.PER_ATTRIBUTE30_V') then
365 l_column:=substrb(p_field,14,LENGTH(p_field)-15);
366 l_table:='PER_ALL_PEOPLE_F';
367 l_type:='V';
368 l_sub_query:=null;
369 elsif p_field in ('MAINTAIN.PER_INFORMATION1_V'
370 ,'MAINTAIN.PER_INFORMATION2_V'
371 ,'MAINTAIN.PER_INFORMATION3_V'
372 ,'MAINTAIN.PER_INFORMATION4_V'
373 ,'MAINTAIN.PER_INFORMATION5_V'
374 ,'MAINTAIN.PER_INFORMATION6_V'
375 ,'MAINTAIN.PER_INFORMATION7_V'
376 ,'MAINTAIN.PER_INFORMATION8_V'
377 ,'MAINTAIN.PER_INFORMATION9_V'
378 ,'MAINTAIN.PER_INFORMATION10_V'
379 ,'MAINTAIN.PER_INFORMATION11_V'
380 ,'MAINTAIN.PER_INFORMATION12_V'
381 ,'MAINTAIN.PER_INFORMATION13_V'
382 ,'MAINTAIN.PER_INFORMATION14_V'
383 ,'MAINTAIN.PER_INFORMATION15_V'
384 ,'MAINTAIN.PER_INFORMATION16_V'
385 ,'MAINTAIN.PER_INFORMATION17_V'
386 ,'MAINTAIN.PER_INFORMATION18_V'
387 ,'MAINTAIN.PER_INFORMATION19_V'
388 ,'MAINTAIN.PER_INFORMATION20_V'
389 ,'MAINTAIN.PER_INFORMATION21_V'
390 ,'MAINTAIN.PER_INFORMATION22_V'
391 ,'MAINTAIN.PER_INFORMATION23_V'
392 ,'MAINTAIN.PER_INFORMATION24_V'
393 ,'MAINTAIN.PER_INFORMATION25_V'
394 ,'MAINTAIN.PER_INFORMATION26_V'
395 ,'MAINTAIN.PER_INFORMATION27_V'
396 ,'MAINTAIN.PER_INFORMATION28_V'
397 ,'MAINTAIN.PER_INFORMATION29_V'
398 ,'MAINTAIN.PER_INFORMATION30_V'
399 ,'MAINTAIN.PER_INFORMATION30_M') then
400 l_column:=substrb(p_field,10,LENGTH(p_field)-11);
401 l_table:='PER_ALL_PEOPLE_F';
402 l_type:='V';
403 l_sub_query:=null;
404 -- ADDED FOR FIX OF #3211345 END
405 elsif p_field in ('MAINTAIN.START_DATE'
406 ,'MAINTAIN.DATE_EMPLOYEE_DATA_VERIFIED'
407 ,'MAINTAIN.DATE_OF_BIRTH'
408 ,'MAINTAIN.LAST_MEDICAL_TEST_DATE'
409 ,'MAINTAIN.DPDNT_ADOPTION_DATE'
410 ,'MAINTAIN.RECEIPT_OF_DEATH_CERT_DATE'
411 ,'MAINTAIN.DATE_OF_DEATH'
412 ,'MAINTAIN.ORIGINAL_DATE_OF_HIRE') then
413 l_column:=substrb(p_field,10);
414 l_table:='PER_ALL_PEOPLE_F';
415 l_type:='D';
416 l_sub_query:=null;
417 ELSIF P_FIELD='MAINTAIN.RECRUITER' then
418 l_column:='RECRUITER_ID';
419 l_table:='PER_ALL_ASSIGNMENTS_F';
420 l_type:='N';
421 l_sub_query:=
422 'select full_name
423 from per_all_people_f
424 where person_id=:1
425 and to_date('''
426 ||to_char(p_effective_date,'DDMMYYYY')||
427 ''',''DDMMYYYY'') between effective_start_date and effective_end_date';
428 ELSIF P_FIELD='MAINTAIN.GRADE' then
429 l_column:='GRADE_ID';
430 l_table:='PER_ALL_ASSIGNMENTS_F';
431 l_type:='N';
432 l_sub_query:=
433 'select name
434 from per_grades_vl
435 where grade_id=:1';
436 ELSIF P_FIELD='MAINTAIN.POSITION' then
437 l_column:='POSITION_ID';
438 l_table:='PER_ALL_ASSIGNMENTS_F';
439 l_type:='N';
440 l_sub_query:=
441 'select name
442 from hr_all_positions_f_vl
443 where position_id=:1
444 and '||l_date_string||' between effective_start_date and effective_end_date';
445 ELSIF P_FIELD='MAINTAIN.JOB' then
446 l_column:='JOB_ID';
447 l_table:='PER_ALL_ASSIGNMENTS_F';
448 l_type:='N';
449 l_sub_query:=
450 'select name
451 from per_jobs_vl
452 where job_id=:1';
453 ELSIF P_FIELD='MAINTAIN.ASSIGNMENT_STATUS_TYPE' then
454 l_column:='ASSIGNMENT_STATUS_TYPE_ID';
455 l_table:='PER_ALL_ASSIGNMENTS_F';
456 l_type:='N';
457 l_sub_query:=
458 'SELECT nvl(atl.user_status,stl.user_status)
459 FROM
460 per_ass_status_type_amends_tl atl,
461 per_ass_status_type_amends a,
462 per_assignment_status_types_tl stl,
463 per_assignment_status_types s
464 WHERE
465 s.assignment_status_type_id=:1 and
466 a.assignment_status_type_id (+)=s.assignment_status_type_id and
467 a.business_group_id (+) +0='||p_business_group_id||' and
468 nvl(a.active_flag, s.active_flag)=''Y'' and
469 a.ass_status_type_amend_id=atl.ass_status_type_amend_id (+) and
470 decode(atl.language,null,''1'',atl.language)
471 =decode(atl.language,null,''1'',userenv(''LANG'')) and
472 s.assignment_status_type_id=stl.assignment_status_type_id and
473 stl.language=userenv(''LANG'')';
474 ELSIF P_FIELD='MAINTAIN.PAYROLL' then
475 l_column:='PAYROLL_ID';
479 'select payroll_name
476 l_table:='PER_ALL_ASSIGNMENTS_F';
477 l_type:='N';
478 l_sub_query:=
480 from pay_all_payrolls_f
481 where payroll_id=:1
482 and '||l_date_string||' between effective_start_date and effective_end_date';
483 ELSIF P_FIELD='MAINTAIN.LOCATION' then
484 l_column:='LOCATION_ID';
485 l_table:='PER_ALL_ASSIGNMENTS_F';
486 l_type:='N';
487 l_type:='N';
488 l_sub_query:=
489 'select location_code
490 from hr_locations
491 where location_id=:1';
492 ELSIF P_FIELD='MAINTAIN.PERSON_REFERRED_BY' then
493 l_column:='PERSON_REFERRED_BY_ID';
494 l_table:='PER_ALL_ASSIGNMENTS_F';
495 l_type:='N';
496 l_sub_query:=
497 'select full_name
498 from per_all_people_f
499 where person_id=:1
500 and '||l_date_string||' between effective_start_date and effective_end_date';
501 ELSIF P_FIELD='MAINTAIN.SUPERVISOR' then
502 l_column:='SUPERVISOR_ID';
503 l_table:='PER_ALL_ASSIGNMENTS_F';
504 l_type:='N';
505 l_sub_query:=
506 'select full_name
507 from per_all_people_f
508 where person_id=:1
509 and '||l_date_string||' between effective_start_date and effective_end_date';
510 ELSIF P_FIELD='MAINTAIN.SUPERVISOR_ASSIGNMENT_NUMBER' then
511 l_column:='SUPERVISOR_ASSIGNMENT_ID';
512 l_table:='PER_ALL_ASSIGNMENTS_F';
513 l_type:='N';
514 l_sub_query:=
515 'select assignment_number
516 from per_all_assignments_f
517 where person_id=:1
518 and '||l_date_string||' between effective_start_date and effective_end_date';
519 ELSIF P_FIELD='MAINTAIN.RECRUITMENT_ACTIVITY' then
520 l_column:='RECRUITMENT_ACTIVITY_ID';
521 l_table:='PER_ALL_ASSIGNMENTS_F';
522 l_type:='N';
523 l_sub_query:=
524 'select name
525 from per_recruitment_activities
526 where recruitment_activity_id=:1';
527 ELSIF P_FIELD='MAINTAIN.SOURCE_ORGANIZATION' then
528 l_column:='SOURCE_ORGANIZATION_ID';
529 l_table:='PER_ALL_ASSIGNMENTS_F';
530 l_type:='N';
531 l_sub_query:=
532 'select name
533 from hr_organization_units
534 where organization_id=:1';
535 ELSIF P_FIELD='MAINTAIN.ORGANIZATION' then
536 l_column:='ORGANIZATION_ID';
537 l_table:='PER_ALL_ASSIGNMENTS_F';
538 l_type:='N';
539 l_sub_query:=
540 'select name
541 from hr_organization_units
542 where organization_id=:1';
543 ELSIF P_FIELD='MAINTAIN.VACANCY' then
544 l_column:='VACANCY_ID';
545 l_table:='PER_ALL_ASSIGNMENTS_F';
546 l_type:='N';
547 l_sub_query:=
548 'select name
549 from per_vacancies
550 where vacancy_id=:1';
551 ELSIF P_FIELD='MAINTAIN.SALARY_BASIS' then
552 l_column:='PAY_BASIS_ID';
553 l_table:='PER_ALL_ASSIGNMENTS_F';
554 l_type:='N';
555 l_sub_query:=
556 'select name
557 from per_pay_bases
558 where pay_basis_id=:1';
559 ELSIF P_FIELD='MAINTAIN.ASG_PRIMARY_FLAG' then
560 l_column:='PRIMARY_FLAG';
561 l_table:='PER_ALL_ASSIGNMENTS_F';
562 l_type:='V';
563 l_sub_query:=
564 'select meaning from hr_lookups
565 where lookup_type=''YES_NO''
566 and enabled_flag=''Y''
567 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
568 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
569 --Bug 3063591 Start Here
570 ELSIF P_FIELD='MAINTAIN.WORK_AT_HOME' then
571 l_column:='WORK_AT_HOME';
572 l_table:='PER_ALL_ASSIGNMENTS_F';
573 l_type:='V';
574 l_sub_query:=
575 'select meaning from hr_lookups
576 where lookup_type=''YES_NO''
577 and enabled_flag=''Y''
578 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
579 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
580 --Bug 3063591 End Here
581 ELSIF P_FIELD='MAINTAIN.EMPLOYMENT_CATEGORY_MEANING' then
582 l_column:='EMPLOYMENT_CATEGORY';
583 l_table:='PER_ALL_ASSIGNMENTS_F';
584 l_type:='V';
585 l_sub_query:=
586 'select meaning from hr_lookups
587 where lookup_type=''EMP_CAT''
588 and enabled_flag=''Y''
589 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
590 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
591 ELSIF P_FIELD='MAINTAIN.EMPLOYEE_CATEGORY_MEANING' then
592 l_column:='EMPLOYEE_CATEGORY';
593 l_table:='PER_ALL_ASSIGNMENTS_F';
594 l_type:='V';
595 l_sub_query:=
596 'select meaning from hr_lookups
597 where lookup_type=''EMPLOYEE_CATG''
598 and enabled_flag=''Y''
599 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
600 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
601 ELSIF P_FIELD='MAINTAIN.FREQUENCY_MEANING' then
602 l_column:='FREQUENCY';
603 l_table:='PER_ALL_ASSIGNMENTS_F';
604 l_type:='V';
605 l_sub_query:=
606 'select meaning from hr_lookups
607 where lookup_type=''FREQUENCY''
608 and enabled_flag=''Y''
609 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
610 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
611 ELSIF P_FIELD='MAINTAIN.PROBATION_UNIT_MEANING' then
612 l_column:='PROBATION_UNIT';
616 'select meaning from hr_lookups
613 l_table:='PER_ALL_ASSIGNMENTS_F';
614 l_type:='V';
615 l_sub_query:=
617 where lookup_type=''QUALIFYING_UNITS''
618 and enabled_flag=''Y''
619 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
620 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
621 ELSIF P_FIELD='MAINTAIN.BARGAINING_UNIT_CODE_MEANING' then
622 l_column:='BARGAINING_UNIT_CODE';
623 l_table:='PER_ALL_ASSIGNMENTS_F';
624 l_type:='V';
625 l_sub_query:=
626 'select meaning from hr_lookups
627 where lookup_type=''BARGAINING_UNIT_CODE''
628 and enabled_flag=''Y''
629 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
630 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
631 ELSIF P_FIELD='MAINTAIN.HOURLY_SALARIED_MEANING' then
632 l_column:='HOURLY_SALARIED_CODE';
633 l_table:='PER_ALL_ASSIGNMENTS_F';
634 l_type:='V';
635 l_sub_query:=
636 'select meaning from hr_lookups
637 where lookup_type=''HOURLY_SALARIED_CODE''
638 and enabled_flag=''Y''
639 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
640 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
641 ELSIF P_FIELD='MAINTAIN.SPECIAL_CEILING_STEP' then
642 l_column:='SPECIAL_CEILING_STEP_ID';
643 l_table:='PER_ALL_ASSIGNMENTS_F';
644 l_type:='N';
645 l_sub_query:=
646 'select psp.spinal_point
647 from per_spinal_points psp
648 , per_spinal_point_steps_f psps
649 where psp.spinal_point_id=psps.spinal_point_id
650 and psps.step_id=:1
651 and '||l_date_string||' between psps.effective_start_date
652 and psps.effective_end_date';
653 ELSIF P_FIELD='MAINTAIN.CHANGE_REASON_MEANING' then
654 l_column:='CHANGE_REASON';
655 l_table:='PER_ALL_ASSIGNMENTS_F';
656 l_type:='V';
657 l_sub_query:=
658 'select meaning from hr_lookups
659 where lookup_type=''APL_ASSIGN_REASON''
660 and enabled_flag=''Y''
661 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
662 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
663 ELSIF P_FIELD='MAINTAIN.PERF_REV_PERIOD_FREQ_MEANING' then
664 l_column:='PERF_REV_PERIOD_FREQUENCY';
665 l_table:='PER_ALL_ASSIGNMENTS_F';
666 l_type:='V';
667 l_sub_query:=
668 'select meaning from hr_lookups
669 where lookup_type=''FREQUENCY''
670 and enabled_flag=''Y''
671 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
672 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
673 ELSIF P_FIELD='MAINTAIN.SAL_REV_PERIOD_FREQ_MEANING' then
674 l_column:='SAL_REV_PERIOD_FREQUENCY';
675 l_table:='PER_ALL_ASSIGNMENTS_F';
676 l_type:='V';
677 l_sub_query:=
678 'select meaning from hr_lookups
679 where lookup_type=''FREQUENCY''
680 and enabled_flag=''Y''
681 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
682 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
683 ELSIF P_FIELD='MAINTAIN.SOURCE_TYPE_MEANING' then
684 l_column:='SOURCE_TYPE';
685 l_table:='PER_ALL_ASSIGNMENTS_F';
686 l_type:='V';
687 l_sub_query:=
688 'select meaning from hr_lookups
689 where lookup_type=''REC_TYPE''
690 and enabled_flag=''Y''
691 and '||l_date_string||' between nvl(start_date_active,'||l_date_string||')
692 and nvl(end_date_active,'||l_date_string||') and lookup_code=:1';
693 ELSIF P_FIELD='MAINTAIN.CONTRACT' then
694 l_column:='CONTRACT_ID';
695 l_table:='PER_ALL_ASSIGNMENTS_F';
696 l_type:='N';
697 l_sub_query:=
698 'select reference
699 from per_contracts_f
700 where contract_id=:1
701 and '||l_date_string||' between effective_start_date
702 and effective_end_date';
703 ELSIF P_FIELD='MAINTAIN.COLLECTIVE_AGREEMENT' then
704 l_column:='COLLECTIVE_AGREEMENT_ID';
705 l_table:='PER_ALL_ASSIGNMENTS_F';
706 l_type:='N';
707 l_sub_query:=
708 'select name
709 from per_collective_agreements
710 where collective_agreement_id=:1';
711 ELSIF P_FIELD='MAINTAIN.CAGR_ID_FLEX_NAME' then
712 l_column:='CAGR_ID_FLEX_NUM';
713 l_table:='PER_ALL_ASSIGNMENTS_F';
714 l_type:='N';
715 l_sub_query:=
716 'select id_flex_structure_name
717 from fnd_id_flex_structures_vl
718 where id_flex_code=''CAGR''
719 and application_id=800
720 and id_flex_num=:1';
721 ELSIF P_FIELD='MAINTAIN.CAGR_GRADE' then
722 l_column:='CAGR_GRADE_DEF_ID';
723 l_table:='PER_ALL_ASSIGNMENTS_F';
724 l_type:='N';
725 ELSIF P_FIELD='MAINTAIN.ESTABLISHMENT' then
726 l_column:='ESTABLISHMENT_ID';
727 l_table:='PER_ALL_ASSIGNMENTS_F';
728 l_type:='N';
729 l_sub_query:=
730 'select name
731 from hr_organization_units
732 where organization_id=:1';
733 ELSIF P_FIELD='MAINTAIN.VENDOR_NAME' then
734 l_column:='VENDOR_ID';
735 l_table:='PER_ALL_ASSIGNMENTS_F';
736 l_type:='N';
737 l_sub_query:=
738 'select vendor_name
739 from po_vendors
740 where vendor_id=:1';
741 ELSIF P_FIELD='MAINTAIN.VENDOR_SITE_CODE' then
742 l_column:='VENDOR_SITE_ID';
743 l_table:='PER_ALL_ASSIGNMENTS_F';
747 from po_vendor_sites
744 l_type:='N';
745 l_sub_query:=
746 'select vendor_site_code
748 where vendor_site_id=:1';
749 ELSIF P_FIELD='MAINTAIN.PO_HEADER_NUM' then
750 l_column:='PO_HEADER_ID';
751 l_table:='PER_ALL_ASSIGNMENTS_F';
752 l_type:='N';
753 l_sub_query:=
754 'select segment1
755 from po_headers_all
756 where po_header_id=:1';
757 ELSIF P_FIELD='MAINTAIN.PO_LINE_NUM' then
758 l_column:='PO_LINE_ID';
759 l_table:='PER_ALL_ASSIGNMENTS_F';
760 l_type:='N';
761 l_sub_query:=
762 'select line_num
763 from po_lines_all
764 where po_line_id=:1';
765 ELSIF P_FIELD in('MAINTAIN.NORMAL_HOURS'
766 ,'MAINTAIN.PROBATION_PERIOD'
767 ,'MAINTAIN.TIME_NORMAL_FINISH'
768 ,'MAINTAIN.TIME_NORMAL_START'
769 ,'MAINTAIN.PERF_REVIEW_PERIOD'
770 ,'MAINTAIN.SAL_REVIEW_PERIOD'
771 ,'MAINTAIN.NOTICE_PERIOD') then
772 l_column:=substrb(p_field,10);
773 l_table:='PER_ALL_ASSIGNMENTS_F';
774 l_type:='N';
775 l_sub_query:=null;
776
777 ELSIF P_FIELD in('MAINTAIN.PROJECTED_ASSIGNMENT_END') then
778 l_column:=substrb(p_field,10);
779 l_table:='PER_ALL_ASSIGNMENTS_F';
780 l_type:='D';
781 l_sub_query:=null;
782
783 ELSIF P_FIELD in('MAINTAIN.PGP_SEGMENT1_V'
784 ,'MAINTAIN.PGP_SEGMENT2_V'
785 ,'MAINTAIN.PGP_SEGMENT3_V'
786 ,'MAINTAIN.PGP_SEGMENT4_V'
787 ,'MAINTAIN.PGP_SEGMENT5_V'
788 ,'MAINTAIN.PGP_SEGMENT6_V'
789 ,'MAINTAIN.PGP_SEGMENT7_V'
790 ,'MAINTAIN.PGP_SEGMENT8_V'
791 ,'MAINTAIN.PGP_SEGMENT9_V'
792 ,'MAINTAIN.PGP_SEGMENT10_V'
793 ,'MAINTAIN.PGP_SEGMENT11_V'
794 ,'MAINTAIN.PGP_SEGMENT12_V'
795 ,'MAINTAIN.PGP_SEGMENT13_V'
796 ,'MAINTAIN.PGP_SEGMENT14_V'
797 ,'MAINTAIN.PGP_SEGMENT15_V'
798 ,'MAINTAIN.PGP_SEGMENT16_V'
799 ,'MAINTAIN.PGP_SEGMENT17_V'
800 ,'MAINTAIN.PGP_SEGMENT18_V'
801 ,'MAINTAIN.PGP_SEGMENT19_V'
802 ,'MAINTAIN.PGP_SEGMENT20_V'
803 ,'MAINTAIN.PGP_SEGMENT21_V'
804 ,'MAINTAIN.PGP_SEGMENT22_V'
805 ,'MAINTAIN.PGP_SEGMENT23_V'
806 ,'MAINTAIN.PGP_SEGMENT24_V'
807 ,'MAINTAIN.PGP_SEGMENT25_V'
808 ,'MAINTAIN.PGP_SEGMENT26_V'
809 ,'MAINTAIN.PGP_SEGMENT27_V'
810 ,'MAINTAIN.PGP_SEGMENT28_V'
811 ,'MAINTAIN.PGP_SEGMENT29_V'
812 ,'MAINTAIN.PGP_SEGMENT30_V') then
813 l_column:='PEOPLE_GROUP_ID';
814 l_table:='PER_ALL_ASSIGNMENTS_F';
815 l_type:='N';
816 l_sub_query:=
817 'select group_name
818 from pay_people_groups
819 where people_group_id=:1';
820
821 ELSIF P_FIELD in('MAINTAIN.SCL_SEGMENT1_V'
822 ,'MAINTAIN.SCL_SEGMENT2_V'
823 ,'MAINTAIN.SCL_SEGMENT3_V'
824 ,'MAINTAIN.SCL_SEGMENT4_V'
825 ,'MAINTAIN.SCL_SEGMENT5_V'
826 ,'MAINTAIN.SCL_SEGMENT6_V'
827 ,'MAINTAIN.SCL_SEGMENT7_V'
828 ,'MAINTAIN.SCL_SEGMENT8_V'
829 ,'MAINTAIN.SCL_SEGMENT9_V'
830 ,'MAINTAIN.SCL_SEGMENT10_V'
831 ,'MAINTAIN.SCL_SEGMENT11_V'
832 ,'MAINTAIN.SCL_SEGMENT12_V'
833 ,'MAINTAIN.SCL_SEGMENT13_V'
834 ,'MAINTAIN.SCL_SEGMENT14_V'
835 ,'MAINTAIN.SCL_SEGMENT15_V'
836 ,'MAINTAIN.SCL_SEGMENT16_V'
837 ,'MAINTAIN.SCL_SEGMENT17_V'
838 ,'MAINTAIN.SCL_SEGMENT18_V'
839 ,'MAINTAIN.SCL_SEGMENT19_V'
840 ,'MAINTAIN.SCL_SEGMENT20_V'
841 ,'MAINTAIN.SCL_SEGMENT21_V'
842 ,'MAINTAIN.SCL_SEGMENT22_V'
843 ,'MAINTAIN.SCL_SEGMENT23_V'
844 ,'MAINTAIN.SCL_SEGMENT24_V'
845 ,'MAINTAIN.SCL_SEGMENT25_V'
846 ,'MAINTAIN.SCL_SEGMENT26_V'
847 ,'MAINTAIN.SCL_SEGMENT27_V'
848 ,'MAINTAIN.SCL_SEGMENT28_V'
849 ,'MAINTAIN.SCL_SEGMENT29_V'
850 ,'MAINTAIN.SCL_SEGMENT30_V') then
851 l_column:='SOFT_CODING_KEYFLEX_ID';
852 l_table:='PER_ALL_ASSIGNMENTS_F';
853 l_type:='N';
854 l_sub_query:=
855 'select concatenated_segments
856 from hr_soft_coding_keyflex
857 where soft_coding_keyflex_id=:1';
858
859 ELSIF P_FIELD in ('MAINTAIN.LABOUR_UNION_MEMBER_FLAG'
860 ,'MAINTAIN.INTERNAL_ADDRESS_LINE'
861 ,'MAINTAIN.MANAGER_FLAG'
862 ,'MAINTAIN.BILLING_TITLE'
863 ,'MAINTAIN.PROJECT_TITLE'
864 ,'MAINTAIN.VENDOR_EMPLOYEE_NUMBER'
865 ,'MAINTAIN.VENDOR_ASSIGNMENT_NUMBER'
866 ,'MAINTAIN.ASS_ATTRIBUTE_CATEGORY'
867 ,'MAINTAIN.ASS_ATTRIBUTE1_V'
868 ,'MAINTAIN.ASS_ATTRIBUTE2_V'
869 ,'MAINTAIN.ASS_ATTRIBUTE3_V'
870 ,'MAINTAIN.ASS_ATTRIBUTE4_V'
874 ,'MAINTAIN.ASS_ATTRIBUTE8_V'
871 ,'MAINTAIN.ASS_ATTRIBUTE5_V'
872 ,'MAINTAIN.ASS_ATTRIBUTE6_V'
873 ,'MAINTAIN.ASS_ATTRIBUTE7_V'
875 ,'MAINTAIN.ASS_ATTRIBUTE9_V'
876 ,'MAINTAIN.ASS_ATTRIBUTE10_V'
877 ,'MAINTAIN.ASS_ATTRIBUTE11_V'
878 ,'MAINTAIN.ASS_ATTRIBUTE12_V'
879 ,'MAINTAIN.ASS_ATTRIBUTE13_V'
880 ,'MAINTAIN.ASS_ATTRIBUTE14_V'
881 ,'MAINTAIN.ASS_ATTRIBUTE15_V'
882 ,'MAINTAIN.ASS_ATTRIBUTE16_V'
883 ,'MAINTAIN.ASS_ATTRIBUTE17_V'
884 ,'MAINTAIN.ASS_ATTRIBUTE18_V'
885 ,'MAINTAIN.ASS_ATTRIBUTE19_V'
886 ,'MAINTAIN.ASS_ATTRIBUTE20_V'
887 ,'MAINTAIN.ASS_ATTRIBUTE21_V'
888 ,'MAINTAIN.ASS_ATTRIBUTE22_V'
889 ,'MAINTAIN.ASS_ATTRIBUTE23_V'
890 ,'MAINTAIN.ASS_ATTRIBUTE24_V'
891 ,'MAINTAIN.ASS_ATTRIBUTE25_V'
892 ,'MAINTAIN.ASS_ATTRIBUTE26_V'
893 ,'MAINTAIN.ASS_ATTRIBUTE27_V'
894 ,'MAINTAIN.ASS_ATTRIBUTE28_V'
895 ,'MAINTAIN.ASS_ATTRIBUTE29_V'
896 ,'MAINTAIN.ASS_ATTRIBUTE30_V') then
897 -- l_column:=substrb(p_field,10);-- fix for bug6846610
898 l_column:=substrb(p_field,10,LENGTH(p_field)-11); -- fix for bug6846610
899 l_table:='PER_ALL_ASSIGNMENTS_F';
900 l_type:='V';
901 l_sub_query:=null;
902
903 --Bug 3063591 Start Here
904 ELSIF P_FIELD='MAINTAIN.WORK_AT_HOME' then
905 l_column:='WORK_AT_HOME';
906 l_table:='PER_ALL_ASSIGNMENTS_F';
907 l_type:='V';
908 l_sub_query:=null;
909 --Bug 3063591 End Here
910
911 ELSIF P_FIELD='MAINTAIN.DATE_PROBATION_END' then
912 l_column:=substrb(p_field,10);
913 l_table:='PER_ALL_ASSIGNMENTS_F';
914 l_type:='D';
915 l_sub_query:=null;
916
917 ELSIF P_FIELD='PALL' then
918 l_column:='ROWID';
919 l_table:='PER_ALL_PEOPLE_F';
920 l_type:='V';
921 l_sub_query:=null;
922 ELSIF P_FIELD='AALL' then
923 l_column:='ROWID';
924 l_table:='PER_ALL_ASSIGNMENTS_F';
925 l_type:='V';
926 l_sub_query:=null;
927 END IF;
928 --
929 hr_utility.set_location(l_proc,100);
930 --
931 if(l_type='V') then
932 l_select_stmt:='select '||l_column;
933 elsif(l_type='N') then
934 l_select_stmt:='select to_char('||l_column||')';
935 elsif(l_type='D') then
936 l_select_stmt:='select fnd_date.date_to_display_date('||l_column||')';
937 end if;
938 --
939 hr_utility.set_location(l_proc,110);
940 --
941 if ((l_table='PER_ALL_PEOPLE_F') or (l_table='PER_PERSON_TYPE_USAGES_F')) then
942 l_primary_key_name:='PERSON_ID';
943 l_primary_key:=NVL(p_person_id,hr_api.g_number);
944 else
945 l_primary_key_name:='ASSIGNMENT_ID';
946 l_primary_key:=NVL(p_assignment_id,hr_api.g_number);
947 end if;
948
949 l_select_stmt:=l_select_stmt||'
950 ,to_char(effective_start_date,''J'')
951 ,to_char(effective_end_date,''J'')
952 from '||l_table||'
953 where '||l_primary_key_name||' = '||l_primary_key;
954 --
955 -- get the dates to look at
956 if( p_security_mode='FUTURE') then
957 l_select_stmt:=l_select_stmt||'
958 and effective_end_date>=
959 to_date('''||to_char(p_datetrack_date,'YYYY/MM/DD')||''',''YYYY/MM/DD'')';
960 elsif(p_security_mode='PAST') then
961 l_select_stmt:=l_select_stmt||'
962 and effective_start_date<=
963 to_date('''||to_char(p_datetrack_date,'YYYY/MM/DD')||''',''YYYY/MM/DD'')';
964 elsif(p_security_mode='PRESENT') then
965 l_select_stmt:=l_select_stmt||'
966 and to_date('''||to_char(p_datetrack_date,'YYYY/MM/DD')||''',''YYYY/MM/DD'')
967 between effective_start_date and effective_end_date';
968 end if;
969 --
970 l_select_stmt:=l_select_stmt||' order by effective_start_date';
971 --
972 hr_utility.set_location(l_proc,120);
973 --
974 i:=1;
975 OPEN date_cv FOR l_select_stmt;
976 FETCH date_cv into l_date_rec;
977 if date_cv%FOUND then
978 --
979 hr_utility.set_location(l_proc,130);
980 --
981 date_start:=l_date_rec.start_date;
982 date_end:=l_date_rec.end_date;
983 val_old:=l_date_rec.value;
984 LOOP
985 FETCH date_cv into l_date_rec;
986 EXIT when date_cv%NOTFOUND;
987 val_new:=l_date_rec.value;
988 --
989 hr_utility.set_location(l_proc,140);
990 --
991 if( nvl(val_new,hr_api.g_varchar2)<>nvl(val_old,hr_api.g_varchar2) ) then
992 --
993 hr_utility.set_location(l_proc,150);
994 --
995 records(i).start_date:=date_start;
996 records(i).end_date:=date_end;
997 if l_sub_query is not null and val_old is not null then
998 EXECUTE IMMEDIATE l_sub_query
999 into records(i).value
1000 using val_old;
1001 else
1002 records(i).value:=val_old;
1003 end if;
1004 i:=i+1;
1005 date_start:=l_date_rec.start_date;
1006 end if;
1007 date_end:=l_date_rec.end_date;
1008 val_old:=val_new;
1009 END LOOP;
1013 records(i).start_date:=date_start;
1010 --
1011 hr_utility.set_location(l_proc,160);
1012 --
1014 records(i).end_date:=date_end;
1015 if l_sub_query is not null and val_old is not null then
1016 EXECUTE IMMEDIATE l_sub_query
1017 into records(i).value
1018 using val_old;
1019 else
1020 records(i).value:=val_old;
1021 end if;
1022 end if;
1023 CLOSE date_cv;
1024 --
1025 hr_utility.set_location('Leaving '||l_proc,200);
1026 --
1027 end get_dates;
1028
1029 procedure get_first_date
1030 (p_field IN VARCHAR2
1031 ,p_security_mode IN VARCHAR2
1032 ,p_effective_date IN DATE
1033 ,p_datetrack_date IN DATE
1034 ,p_person_id IN NUMBER
1035 ,p_assignment_id IN NUMBER
1036 ,p_business_group_id IN NUMBER
1037 ,p_new_date OUT NOCOPY DATE) is
1038
1039 l_records datetab;
1040 l_new_date date;
1041 i number;
1042 --
1043 l_proc varchar2(72):=g_package||'get_first_date';
1044 --
1045 begin
1046 --
1047 hr_utility.set_location('Entering: '||l_proc,10);
1048 --
1049 get_dates(p_field => p_field
1050 ,p_security_mode => ''
1051 ,p_effective_date => p_effective_date
1052 ,p_datetrack_date => p_datetrack_date
1053 ,p_person_id => p_person_id
1054 ,p_assignment_id => p_assignment_id
1055 ,p_business_group_id => p_business_group_id
1056 ,records => l_records);
1057 --
1058 hr_utility.set_location(l_proc,20);
1059 --
1060 if p_security_mode='FUTURE' then
1061 if to_date(l_records(1).start_date,'J')>p_datetrack_date then
1062 l_new_date:=to_date(l_records(1).start_date,'J');
1063 else
1064 l_new_date:=p_datetrack_date;
1065 end if;
1066 elsif p_security_mode='PRESENT' then
1067 l_new_date:=p_effective_date;
1068 else
1069 l_new_date:=to_date(l_records(1).start_date,'J');
1070 end if;
1071 --
1072 hr_utility.set_location(l_proc,30);
1073 --
1074 p_new_date:=l_new_date;
1075 --
1076 hr_utility.set_location('Leaving '||l_proc,40);
1077 --
1078 end get_first_date;
1079
1080 procedure get_previous_date
1081 (p_field IN VARCHAR2
1082 ,p_security_mode IN VARCHAR2
1083 ,p_effective_date IN DATE
1084 ,p_datetrack_date IN DATE
1085 ,p_person_id IN NUMBER
1086 ,p_assignment_id IN NUMBER
1087 ,p_business_group_id IN NUMBER
1088 ,p_new_date OUT NOCOPY DATE) is
1089
1090 l_records datetab;
1091 l_new_date date;
1092 i number;
1093 --
1094 l_proc varchar2(72):=g_package||'get_previous_date';
1095 --
1096 begin
1097 --
1098 hr_utility.set_location('Entering: '||l_proc,10);
1099 --
1100 get_dates(p_field => p_field
1101 ,p_security_mode => ''
1102 ,p_effective_date => p_effective_date
1103 ,p_datetrack_date => p_datetrack_date
1104 ,p_person_id => p_person_id
1105 ,p_assignment_id => p_assignment_id
1106 ,p_business_group_id => p_business_group_id
1107 ,records => l_records);
1108 --
1109 hr_utility.set_location(l_proc,20);
1110 --
1111 if p_security_mode='PRESENT' then
1112 l_new_date:=p_effective_date;
1113 else
1114 i:=l_records.COUNT;
1115 loop
1116 l_new_date:= to_date(l_records(i).start_date,'J');
1117 exit when l_new_date<p_effective_date
1118 or i=1;
1119 i:=i-1;
1120 end loop;
1121 if p_security_mode='FUTURE' and l_new_date<p_datetrack_date then
1122 l_new_date:=p_datetrack_date;
1123 end if;
1124 end if;
1125 --
1126 hr_utility.set_location(l_proc,30);
1127 --
1128 p_new_date:=l_new_date;
1129 --
1130 hr_utility.set_location('Leaving '||l_proc,40);
1131 --
1132 end get_previous_date;
1133
1134 procedure get_next_date
1135 (p_field IN VARCHAR2
1136 ,p_security_mode IN VARCHAR2
1137 ,p_effective_date IN DATE
1138 ,p_datetrack_date IN DATE
1139 ,p_person_id IN NUMBER
1140 ,p_assignment_id IN NUMBER
1141 ,p_business_group_id IN NUMBER
1142 ,p_new_date OUT NOCOPY DATE) is
1143
1144 l_records datetab;
1145 l_new_date date;
1146 i number;
1147 --
1148 l_proc varchar2(72):=g_package||'get_next_date';
1149 --
1150 begin
1151 --
1152 hr_utility.set_location('Entering: '||l_proc,10);
1153 --
1154 get_dates(p_field => p_field
1155 ,p_security_mode => ''
1156 ,p_effective_date => p_effective_date
1157 ,p_datetrack_date => p_datetrack_date
1158 ,p_person_id => p_person_id
1159 ,p_assignment_id => p_assignment_id
1160 ,p_business_group_id => p_business_group_id
1161 ,records => l_records);
1162 --
1163 hr_utility.set_location(l_proc,20);
1164 --
1165 if p_security_mode='PRESENT' then
1166 l_new_date:=p_effective_date;
1167 else
1168 i:=1;
1169 loop
1170 l_new_date:= to_date(l_records(i).start_date,'J');
1171 exit when l_new_date>p_effective_date
1172 or i=l_records.COUNT;
1173 i:=i+1;
1174 end loop;
1175 if p_security_mode='PAST' and l_new_date>p_datetrack_date then
1176 l_new_date:=p_datetrack_date;
1177 end if;
1178 end if;
1179 --
1180 hr_utility.set_location(l_proc,30);
1181 --
1182 if l_new_date>p_effective_date then
1183 p_new_date:=l_new_date;
1184 else
1185 p_new_date:=p_effective_date;
1186 end if;
1187 --
1188 hr_utility.set_location('Leaving '||l_proc,40);
1189 --
1190 end get_next_date;
1191
1192 procedure get_last_date
1193 (p_field IN VARCHAR2
1194 ,p_security_mode IN VARCHAR2
1195 ,p_effective_date IN DATE
1196 ,p_datetrack_date IN DATE
1197 ,p_person_id IN NUMBER
1198 ,p_assignment_id IN NUMBER
1199 ,p_business_group_id IN NUMBER
1200 ,p_new_date OUT NOCOPY DATE) is
1201
1202 l_records datetab;
1203 l_new_date date;
1204 i number;
1205 --
1206 l_proc varchar2(72):=g_package||'get_last_date';
1207 --
1208 begin
1209 --
1210 hr_utility.set_location('Entering: '||l_proc,10);
1211 --
1212 get_dates(p_field => p_field
1213 ,p_security_mode => ''
1214 ,p_effective_date => p_effective_date
1215 ,p_datetrack_date => p_datetrack_date
1216 ,p_person_id => p_person_id
1217 ,p_assignment_id => p_assignment_id
1218 ,p_business_group_id => p_business_group_id
1219 ,records => l_records);
1220 --
1221 hr_utility.set_location(l_proc,20);
1222 --
1223 if p_security_mode='PRESENT' then
1224 l_new_date:=p_effective_date;
1225 else
1226 l_new_date:=to_date(l_records(l_records.COUNT).start_date,'J');
1227 if p_security_mode='PAST' and l_new_date>p_datetrack_date then
1228 l_new_date:=p_datetrack_date;
1229 end if;
1230 end if;
1231 --
1232 hr_utility.set_location(l_proc,30);
1233 --
1234 if l_new_date>p_effective_date then
1235 p_new_date:=l_new_date;
1236 else
1237 p_new_date:=p_effective_date;
1238 end if;
1239 --
1240 hr_utility.set_location('Leaving '||l_proc,40);
1241 --
1242 end get_last_date;
1243
1244
1245 end per_qh_timeline;