[Home] [Help]
PACKAGE BODY: APPS.PER_QH_FIND_QUERY
Source
1 PACKAGE BODY per_qh_find_query as
2 /* $Header: peqhfndq.pkb 120.2.12020000.2 2012/12/06 12:25:36 ktithy ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) :=' per_qh_find_query.';
7 --
8 g_quote varchar2(1) := '''';
9
10 procedure findquery(resultset IN OUT NOCOPY findtab
11 ,p_effective_date date
12 ,business_group_id per_all_people_f.business_group_id%type
13 ,business_group_name per_business_groups.name%type default null
14 ,person_id per_all_people_f.person_id%type default null
15 ,person_type per_person_types.user_person_type%type default null
16 ,system_person_type per_person_types.system_person_type%type default null
17 ,person_type_id per_all_people_f.person_type_id%type default null
18 ,last_name per_all_people_f.last_name%type default null
19 ,start_date per_all_people_f.start_date%type default null
20 ,hire_date per_periods_of_service.date_start%type default null
21 ,applicant_number per_all_people_f.applicant_number%type default null
22 ,date_of_birth per_all_people_f.date_of_birth%type default null
23 ,email_address per_all_people_f.email_address%type default null
24 ,employee_number per_all_people_f.employee_number%type default null
25 --CWK
26 ,npw_number per_all_people_f.npw_number%type default null
27 ,project_title per_all_assignments_f.project_title%type default null
28 ,vendor_id per_all_assignments_f.vendor_id%type default null
29 ,vendor_name po_vendors.vendor_name%type default null
30 ,vendor_employee_number per_all_assignments_f.vendor_employee_number%type default null
31 ,vendor_assignment_number per_all_assignments_f.vendor_assignment_number%type default null
32 ,vendor_site_code po_vendor_sites_all.vendor_site_code%TYPE default null
33 ,vendor_site_id po_vendor_sites_all.vendor_site_id%TYPE default null
34 ,po_header_num po_headers_all.segment1%TYPE default null
35 ,po_header_id po_headers_all.po_header_id%TYPE default null
36 ,po_line_num po_lines_all.line_num%TYPE default null
37 ,po_line_id po_lines_all.po_line_id%TYPE default null
38 --
39 ,first_name per_all_people_f.first_name%type default null
40 ,full_name per_all_people_f.full_name%type default null
41 ,title per_all_people_f.title%type
42 ,middle_names per_all_people_f.middle_names%type
43 ,nationality_meaning hr_lookups.meaning%type default null
44 ,nationality per_all_people_f.nationality%type default null
45 ,national_identifier per_all_people_f.national_identifier%type default null
46 -- Bug 3037019
47 ,registered_disabled_flag hr_lookups.meaning%type default null
48 ,registered_disabled per_all_people_f.registered_disabled_flag%type default null
49 ,sex_meaning hr_lookups.meaning%type default null
50 ,sex per_all_people_f.sex%type default null
51 ,benefit_group ben_benfts_grp.name%type default null
52 ,benefit_group_id per_all_people_f.benefit_group_id%type default null
53 ,grade per_grades.name%type default null
54 ,grade_id per_all_assignments_f.grade_id%type default null
55 ,grade_ladder ben_pgm_f.name%type default null
56 ,grade_ladder_pgm_id per_all_assignments_f.grade_ladder_pgm_id%type default null
57 ,position hr_all_positions_f.name%type default null
58 ,position_id per_all_assignments_f.position_id%type default null
59 ,job per_jobs.name%type default null
60 ,job_id per_all_assignments_f.job_id%type default null
61 ,assignment_status_type per_assignment_status_types.user_status%type default null
62 ,assignment_status_type_id per_all_assignments_f.assignment_status_type_id%type default null
63 ,payroll pay_all_payrolls_f.payroll_name%type default null
64 ,payroll_id per_all_assignments_f.payroll_id%type default null
65 ,location hr_locations.location_code%type default null
66 ,location_id per_all_assignments_f.location_id%type default null
67 ,supervisor per_all_people_f.full_name%type default null
68 ,supervisor_id per_all_assignments_f.supervisor_id%type default null
69 ,supervisor_assignment_number per_assignments_v.supervisor_assignment_number%type default null
70 ,supervisor_assignment_id per_all_assignments_f.supervisor_assignment_id%type default null
71 ,recruitment_activity per_recruitment_activities.name%type default null
72 ,recruitment_activity_id per_all_assignments_f.recruitment_activity_id%type default null
73 ,organization hr_all_organization_units.name%type default null
74 ,organization_id per_all_assignments_f.organization_id%type default null
75 ,people_group pay_people_groups.group_name%type default null
76 ,people_group_id per_all_assignments_f.people_group_id%type default null
77 ,vacancy per_vacancies.name%type default null
78 ,vacancy_id per_all_assignments_f.vacancy_id%type default null
79 ,requisition per_requisitions.name%type default null
80 ,requisition_id per_requisitions.requisition_id%type default null
81 ,salary_basis per_pay_bases.name%type default null
82 ,pay_basis_id per_all_assignments_f.pay_basis_id%type default null
83 ,bargaining_unit_code_meaning hr_lookups.meaning%type default null
84 ,bargaining_unit_code per_all_assignments_f.bargaining_unit_code%type default null
85 ,employment_category_meaning hr_lookups.meaning%type default null
86 ,employment_category per_all_assignments_f.employment_category%type default null
87 -- BUG 3002915 starts here. modified type.
88 ,establishment hr_leg_establishments_v.name%type default null
89 ,establishment_id hr_leg_establishments_v.organization_id%type default null
90 -- BUG 3002915 ends here.
91 ,projected_hire_date per_applications.projected_hire_date%type default null
92 ,secure varchar2 default null
93 ,field1_name varchar2 default null
94 ,field1_condition_code varchar2 default null
95 ,field1_value varchar2 default null
96 ,field2_name varchar2 default null
97 ,field2_condition_code varchar2 default null
98 ,field2_value varchar2 default null
99 ,field3_name varchar2 default null
100 ,field3_condition_code varchar2 default null
101 ,field3_value varchar2 default null
102 ,field4_name varchar2 default null
103 ,field4_condition_code varchar2 default null
104 ,field4_value varchar2 default null
105 ,field5_name varchar2 default null
106 ,field5_condition_code varchar2 default null
107 ,field5_value varchar2 default null
108 ,p_fetch_details boolean default true
109 ,p_customized_restriction_id number default null
110 ,p_employees_allowed boolean default false
111 ,p_applicants_allowed boolean default false
112 ,p_cwk_allowed boolean default false
113 ,select_stmt out nocopy varchar2) is
114 type EmpCurTyp is ref cursor;
115 emp_cv EmpCurTyp;
116 --
117 num_row number:=0;
118 out_rec findrec;
119 --
120 l_person_id per_all_people_f.person_id%TYPE;
121 l_assignment_id per_all_assignments_f.assignment_id%type;
122 l_num_asgs NUMBER;
123
124 l_select_stmt_per VARCHAR2(20000);
125 l_select_stmt_per2 VARCHAR2(20000);
126 l_select_stmt_asg VARCHAR2(20000);
127 --
128 l_full_name per_all_people_f.full_name%type;
129 --
130 l_from_clause VARCHAR2(20000);
131 l_where_clause VARCHAR2(20000);
132 l_effective_date_clause VARCHAR2(20000);
133 l_proc varchar2(72):=g_package||'findquery';
134 l_effective_date varchar2(35);
135 l_asg boolean default FALSE;
136 l_other varchar2(30);
137 --
138 --Modified for PMxbg
139 cursor csr_bg_name
140 (p_business_group_id number) is
141 select x.name
142 from per_business_groups x
143 where x.business_group_id = p_business_group_id;
144 --
145 cursor csr_person_details
146 (p_person_id number
147 ,p_effective_date date) is
148
149 select *
150 from per_all_people_f
151 where person_id=p_person_id
152 and p_effective_date between effective_start_date and effective_end_date;
153 --
154 cursor csr_lang
155 (p_lang_code VARCHAR2) is
156 select description
157 from fnd_languages_vl
158 where language_code=p_lang_code;
159 --
160 cursor csr_benfts_grp
161 (p_benfts_grp_id NUMBER) is
162 select name
163 from ben_benfts_grp
164 where benfts_grp_id=p_benfts_grp_id;
165 --
166 per_rec per_all_people_f%rowtype;
167 cursor csr_assignment_details
168 (p_assignment_id number
169 ,p_effective_date date) is
170 select *
171 from per_all_assignments_f
172 where assignment_id=p_assignment_id
173 and p_effective_date between effective_start_date and effective_end_date;
174 --
175 cursor count_asgs(p_person_id number) is
176 select count(*)
177 from per_all_assignments_f per_asg
178 where per_asg.person_id=p_person_id
179 and p_effective_date between per_asg.effective_start_date and per_asg.effective_end_date
180 and per_asg.assignment_type <> 'B' ; -- Added for fix of #3286659
181 --
182 cursor csr_full_name
183 (p_person_id number
184 ,p_effective_date date) is
185
186 select full_name
187 from per_all_people_f
188 where person_id=p_person_id
189 and p_effective_date between effective_start_date and effective_end_date;
190 --
191 cursor csr_supervisor_assgt_number
192 (p_supervisor_assignment_id number
193 ,p_effective_date date) is
194
195 select assignment_number
196 from per_all_assignments_f
197 where assignment_id = p_supervisor_assignment_id
198 and p_effective_date between effective_start_date and effective_end_date;
199 --
200 cursor csr_pds(p_person_id number,p_effective_date date) is
201 select date_start
202 from per_periods_of_service
203 where person_id=p_person_id
204 and p_effective_date between date_start and nvl(final_process_date,p_effective_date);
205 --
206 cursor csr_app(p_person_id number,p_effective_date date) is
207 select projected_hire_date
208 from per_applications
209 where person_id=p_person_id
210 and p_effective_date between date_received and nvl(date_end,p_effective_date);
211 --
212 cursor csr_grade
213 (p_grade_id number) is
214 select name
215 from per_grades_vl
216 where grade_id=p_grade_id;
217 --
218 cursor csr_grade_ladder
219 (p_grade_ladder_pgm_id number
220 ,p_effective_date date) is
221 select name
222 from ben_pgm_f
223 where pgm_id = p_grade_ladder_pgm_id
224 and p_effective_date between effective_start_date and effective_end_date;
225
226 --
227 -- PMFLETCH - MLS: Always select eot record from tl table
228 --
229 cursor csr_position
230 (p_position_id number
231 ,p_effective_date date) is
232 select name
233 from hr_all_positions_f_tl
234 where position_id=p_position_id
235 and language = userenv('LANG');
236 --PMFLETCH - No effective date in tl table
237 --and p_effective_date between effective_start_date and effective_end_date;
238 --
239 cursor csr_job
240 (p_job_id number) is
241 select name
242 from per_jobs_vl
243 where job_id=p_job_id;
244 --
245 --Modified for PMxbg
246 cursor csr_asg_status
247 (p_assignment_status_type_id number) is
248 SELECT
249 nvl(atl.user_status ,stl.user_status),
250 nvl(a.per_system_status ,s.per_system_status)
251 FROM
252 per_ass_status_type_amends_tl atl,
253 per_ass_status_type_amends a,
254 per_assignment_status_types_tl stl,
255 per_assignment_status_types s
256 WHERE
257 s.assignment_status_type_id=p_assignment_status_type_id and
258 a.assignment_status_type_id (+)=s.assignment_status_type_id and
259 a.business_group_id (+) = s.business_group_id and
260 nvl(a.active_flag, s.active_flag)='Y' and
261 a.ass_status_type_amend_id=atl.ass_status_type_amend_id (+) and
262 decode(atl.language,null,'1',atl.language)=decode(atl.language,null,'1',userenv('LANG')) and
263 s.assignment_status_type_id=stl.assignment_status_type_id and
264 stl.language=userenv('LANG');
265 --
266 cursor csr_payroll
267 (p_payroll_id number
268 ,p_effective_date date) is
269 select payroll_name
270 from pay_all_payrolls_f
271 where payroll_id=p_payroll_id
272 and p_effective_date between effective_start_date and effective_end_date;
273 --
274 cursor csr_location
275 (p_location_id number) is
276 select location_code
277 from hr_locations
278 where location_id=p_location_id;
279 --
280 cursor csr_rec_activity
281 (p_recruitment_activity_id number) is
282 select name
283 from per_recruitment_activities
284 where recruitment_activity_id=p_recruitment_activity_id;
285 --
286 cursor csr_organization
287 (p_organization_id number) is
288 select name
289 from hr_organization_units
290 where organization_id=p_organization_id;
291 --
292 cursor csr_pgp_rec
293 (p_people_group_id number) is
294 select * from pay_people_groups
295 where people_group_id=p_people_group_id;
296 --
297 cursor csr_scl_rec
298 (p_soft_coding_keyflex_id number) is
299 select * from hr_soft_coding_keyflex
300 where soft_coding_keyflex_id=p_soft_coding_keyflex_id;
301 --
302 cursor csr_vacancy
303 (p_vacancy_id number) is
304 select vac.name
305 , rec.name
306 from per_vacancies vac
307 , per_requisitions rec
308 where vac.vacancy_id=p_vacancy_id
309 and vac.requisition_id=rec.requisition_id;
310 --
311 cursor csr_pay_basis
312 (p_pay_basis number) is
313 select ppb.name
314 , ppb.pay_basis
315 from per_pay_bases ppb
316 where ppb.pay_basis_id=p_pay_basis;
317 --
318 cursor csr_ceiling_step
319 (p_special_ceiling_step_id number
320 ,p_effective_date date) is
321 select psp.spinal_point spinal_point
322 , count(*) step
323 from per_spinal_points psp
324 , per_spinal_points psp2
325 , per_spinal_point_steps_f psps
326 , per_spinal_point_steps_f psps2
327 where psp.spinal_point_id=psps.spinal_point_id
328 and psps.grade_spine_id=psps2.grade_spine_id
329 and psp2.spinal_point_id=psps2.spinal_point_id
330 and psps.step_id=p_special_ceiling_step_id
331 and psp.sequence >=psp2.sequence
332 and p_effective_date between psps.effective_start_date
333 and psps.effective_end_date
334 and p_effective_date between psps2.effective_start_date
335 and psps2.effective_end_date
336 group by psp.spinal_point
337 , psps.step_id
338 , psps.sequence
339 , psps.effective_start_date
340 , psps.effective_end_date
341 order by 2;
342 --
343 cursor csr_reference
344 (p_contract_id number
345 ,p_effective_date date) is
346 select reference
347 from per_contracts_f
348 where contract_id=p_contract_id
349 and p_effective_date between effective_start_date and effective_end_date;
350 --
351 cursor csr_collective_agr
352 (p_collective_agreement_id number) is
353 select name
354 from per_collective_agreements
355 where collective_agreement_id=p_collective_agreement_id;
356 --
357 cursor csr_cagr_flex_num
358 (p_id_flex_num number) is
359 select id_flex_structure_name
360 from fnd_id_flex_structures_vl
361 where id_flex_code='CAGR'
362 and application_id=800
363 and id_flex_num=p_id_flex_num;
364 --
365 asg_rec per_all_assignments_f%rowtype;
366 pgp_rec pay_people_groups%rowtype;
367 scl_rec hr_soft_coding_keyflex%rowtype;
368 --
369 cursor csr_vendor(p_vendor_id in number) is
370 select vendor_name
371 from po_vendors pov
372 where pov.vendor_id = p_vendor_id;
373 --
374 cursor csr_vendor_site(p_vendor_site_id in number) is
375 select vendor_site_code
376 from po_vendor_sites
377 where vendor_site_id=p_vendor_site_id;
378 --
379 cursor csr_po_header(p_po_header_id in number) is
380 select segment1
381 from po_headers_all
382 where po_header_id = p_po_header_id;
383 --
384 cursor csr_po_line(p_po_line_id in number) is
385 select line_num
386 from po_lines_all
387 where po_line_id = p_po_line_id;
388 --Bug 4060365. Changed the size of the variable l_where from 400 to 2000
389 function build_varchar2_where
390 (p_parent_column varchar2
391 ,p_condition varchar2
392 ,p_value varchar2
393 ,p_child_table varchar2 default null
394 ,p_child_column varchar2 default null
395 ,p_child_meaning varchar2 default null
396 ,p_inner_where varchar2 default null)
397 return varchar2 is
398 l_where varchar2(2000);
399 begin
400 if p_child_table is null then
401 l_where:=p_parent_column;
402 if p_condition='IS' then
403 l_where:='upper('||l_where||')='''||upper(p_value)||'''';
404 elsif p_condition='IS_NOT' then
405 l_where:='upper('||l_where||')<>'''||upper(p_value)||'''';
406 elsif p_condition='CONTAINS' then
407 l_where:='upper('||l_where||') like(''%'||upper(p_value)||'%'')';
408 elsif p_condition='STARTS' then
409 l_where:='upper('||l_where||') like('''||upper(p_value)||'%'')';
410 elsif p_condition='ENDS' then
411 l_where:='upper('||l_where||') like(''%'||upper(p_value)||''')';
412 elsif p_condition='NOT_CONTAINS' then
413 l_where:='upper('||l_where||') not like(''%'||upper(p_value)||'%'')';
414 elsif p_condition='NULL' then
415 l_where:=l_where||' is null';
416 elsif p_condition='NOT_NULL' then
417 l_where:=l_where||' is not null';
418 end if;
419 else
420 l_where:=p_parent_column;
421 if p_condition in ('IS','CONTAINS','STARTS','ENDS','NULL','NOT_NULL') then
422 l_where:=l_where||' in ';
423 elsif p_condition in ('IS_NOT','NOT_CONTAINS') then
424 l_where:=l_where||' not in ';
425 end if;
426 l_where:=l_where||'(select '||p_child_column||' from '||
427 p_child_table||' where upper('||p_child_meaning||')';
428 if p_condition in ('IS','IS_NOT') then
429 l_where:=l_where||'='''||upper(p_value)||'''';
430 elsif p_condition in ('CONTAINS','STARTS','ENDS','NOT_CONTAINS') then
431 l_where:=l_where||' like (''';
432 end if;
433 if p_condition in ('CONTAINS','NOT_CONTAINS') then
434 l_where:=l_where||'%'||upper(p_value)||'%'')';
435 elsif p_condition='STARTS' then
436 l_where:=l_where||upper(p_value)||'%'')';
437 elsif p_condition='ENDS' then
438 l_where:=l_where||'%'||upper(p_value)||''')';
439 elsif p_condition='NULL' then
440 --Bug 4060365. Removed the extra paranthesis after is Null and is not Null
441 l_where:=l_where||' is null';
442 elsif p_condition='NOT_NULL' then
443 l_where:=l_where||' is not null';
444 --Bug 4060365. End of Fix
445 end if;
446 if p_inner_where is not null then
447 l_where:=l_where||p_inner_where;
448 end if;
449 if p_condition in ('IS_NOT','NOT_CONTAINS') then
450 l_where:='(('||l_where||') or ('||p_parent_column||' is null))';
451 end if;
452 l_where:=l_where||')';
453 end if;
454 l_where:=' and '||l_where;
455 return l_where;
456 end build_varchar2_where;
457 --
458 function build_number_where
459 (p_parent_column varchar2
460 ,p_condition varchar2
461 ,p_value varchar2)
462 return varchar2 is
463 l_where varchar2(200);
464 begin
465 l_where:=p_parent_column;
466 if p_condition='IS' then
467 l_where:=l_where||'='||p_value;
468 elsif p_condition='IS_NOT' then
469 l_where:=l_where||'<>'||p_value;
470 elsif p_condition='IS_LESS' then
471 l_where:=l_where||'<='||p_value;
472 elsif p_condition='IS_GREATER' then
473 l_where:=l_where||'>='||p_value;
474 elsif p_condition='NULL' then
475 l_where:=l_where||' is null';
476 elsif p_condition='NOT_NULL' then
477 l_where:=l_where||' is not null';
478 end if;
479 l_where:=' and '||l_where;
480 return l_where;
481 end build_number_where;
482 --
483 function build_date_where
484 (p_parent_column varchar2
485 ,p_condition varchar2
486 ,p_value varchar2
487 ,p_child_table varchar2 default null
488 ,p_child_column varchar2 default null
489 ,p_child_meaning varchar2 default null
490 ,p_inner_where varchar2 default null)
491 return varchar2 is
492 l_where varchar2(200);
493 l_value varchar2(2000) := 'fnd_date.canonical_to_date('''||p_value||''')';
494 begin
495 if p_child_table is null then
496 l_where:=p_parent_column;
497 if p_condition='IS' then
498 l_where:=l_where||'='||l_value;
499 elsif p_condition='IS_NOT' then
500 l_where:=l_where||'<>'||l_value;
501 elsif p_condition='IS_LESS' then
502 l_where:=l_where||'<='||l_value;
503 elsif p_condition='IS_GREATER' then
504 l_where:=l_where||'>='||l_value;
505 elsif p_condition='NULL' then
506 l_where:=l_where||' is null';
507 elsif p_condition='NOT_NULL' then
508 l_where:=l_where||' is not null';
509 end if;
510 else
511 l_where:=p_parent_column;
512 if p_condition IN ('IS','IS_LESS','IS_GREATER','NULL','NOT_NULL') then
513 l_where:=l_where||' in ';
514 elsif p_condition='IS_NOT' then
515 l_where:=l_where||' not in ';
516 end if;
517 l_where:=l_where||'(select '||p_child_column||' from '||
518 p_child_table||' where '||p_child_meaning;
519 if p_condition in ('IS','IS_NOT') then
520 l_where:=l_where||'='||l_value||')';
521 elsif p_condition='IS_LESS' then
522 l_where:=l_where||' <='||l_value||')';
523 elsif p_condition='IS_GREATER' then
524 l_where:=l_where||' >='||l_value||')';
525 elsif p_condition='NULL' then
526 l_where:=l_where||' is null)';
527 elsif p_condition='NOT_NULL' then
528 l_where:=l_where||' is not null)';
529 end if;
530 if p_inner_where is not null then
531 l_where:=l_where||p_inner_where;
532 end if;
533 if p_condition='IS_NOT' then
534 l_where:='(('||l_where||') or ('||p_parent_column||' is null))';
535 end if;
536 end if;
537 l_where:=' and '||l_where;
538 return l_where;
539 end build_date_where;
540 --
541 function build_grade_where
542 (p_condition varchar2
543 ,p_value varchar2)
544 return varchar2 is
545 l_where varchar2(200);
546 begin
547 if p_condition in ('IS','IS_NOT','CONTAINS','STARTS','ENDS','NOT_CONTAINS','NULL','NOT_NULL') then
548 l_where:=build_varchar2_where('per_asg.grade_id',p_condition,p_value
549 ,'per_grades_vl grd_a','grd_a.grade_id','grd_a.name');
550 elsif p_condition in ('IS_LESS','IS_GREATER') then
551 l_where:='and per_asg.grade_id in (select grd_a.grade_id from per_grades_vl grd_a where grd_a.sequence';
552 if p_condition='IS_LESS' then
553 l_where:=l_where||' <=';
554 else
555 l_where:=l_where||' >=';
556 end if;
557 l_where:=l_where||'(select grd_a2.sequence from per_grades_vl grd_a2 where grd_a2.name like ('''
558 ||p_value||''') ) )';
559 end if;
560 return l_where;
561 end build_grade_where;
562 --
563 --Bug 4060365. Changed the size of the variable l_a_where_clause from 2000 to 4000
564 function advanced_where(p_field_name varchar2
565 ,p_condition varchar2
566 ,p_value varchar2)
567 return varchar2 is
568 l_a_where_clause varchar2(4000);
569 l_parent_column varchar2(50);
570 l_child_table varchar2(50);
571 l_child_column varchar2(50);
572 l_child_meaning varchar2(50);
573 --
574 begin
575
576 if p_field_name='FULL_NAME' then
577 l_a_where_clause:=build_varchar2_where('per.full_name',p_condition,p_value);
578 elsif p_field_name='LAST_NAME' then
579 l_a_where_clause:=build_varchar2_where('per.last_name',p_condition,p_value);
580 elsif p_field_name='FIRST_NAME' then
581 l_a_where_clause:=build_varchar2_where('per.first_name',p_condition,p_value);
582 elsif p_field_name='NATIONAL_IDENTIFIER' then
583 l_a_where_clause:=build_varchar2_where('per.national_identifier',p_condition,p_value);
584 elsif p_field_name='APPLICANT_NUMBER' then
585 l_a_where_clause:=build_varchar2_where('per.applicant_number',p_condition,p_value);
586 elsif p_field_name='EMPLOYEE_NUMBER' then
587 l_a_where_clause:=build_varchar2_where('per.employee_number',p_condition,p_value);
588 elsif p_field_name='DATE_OF_BIRTH' then
589 l_a_where_clause:=build_date_where('per.date_of_birth',p_condition,p_value);
590 elsif p_field_name='NATIONALITY' then
591 l_a_where_clause:=build_varchar2_where('per.nationality',p_condition,p_value
592 ,'hr_lookups hlna_a','hlna_a.lookup_code','hlna_a.meaning',' and hlna_a.lookup_type=''NATIONALITY''');
593 elsif p_field_name='SEX' then
594 l_a_where_clause:=build_varchar2_where('per.sex',p_condition,p_value
595 ,'hr_lookups hlse_a','hlse_a.lookup_code','hlse_a.meaning',' and hlse_a.lookup_type=''SEX''');
596 elsif p_field_name='ESTABLISHMENT' then
597 -- bug 3002915 starts here.
598 -- commented existing one added new.
599 --
600 -- l_a_where_clause:=build_varchar2_where('per.person_id',p_condition,p_value
601 --,'per_establishment_attendances peat_a, per_establishments pest_a ','peat_a.person_id','pest_a.name'
602 --,'pest_a.establishment_id=peat_a.establishment_id');
603 --
604 l_a_where_clause:=build_varchar2_where('per_asg.establishment_id',p_condition,p_value,
605 'hr_leg_establishments_v hle','hle.organization_id','hle.name');
606 l_asg:=TRUE;
607 -- bug 3002915 ends here.
608 elsif p_field_name='USER_PERSON_TYPE' then
609 l_a_where_clause:=build_varchar2_where
610 ('per.person_id'
611 ,p_condition
612 ,p_value
613 ,'per_person_type_usages_f ptua, per_person_types_tl ppttla'
614 ,'ptua.person_id'
615 ,'ppttla.user_person_type'
616 ,' AND ptua.person_type_id = ppttla.person_type_id'
617 ||' AND ppttla.language=userenv(''LANG'')'
618 ||' AND ' ||l_effective_date_clause
619 || ' BETWEEN ptua.effective_start_date'
620 || ' AND ptua.effective_end_date'
621 );
622
623 -- Bug 3037019 Start Here
624 elsif p_field_name='REGISTERED_DISABLED' then
625 l_a_where_clause:=build_varchar2_where('per.registered_disabled_flag',p_condition,p_value
626 ,'hr_lookups hlrd_a','hlrd_a.lookup_code','hlrd_a.meaning',' and hlrd_a.lookup_type=''REGISTERED_DISABLED''');
627 -- Bug 3037019 End Here
628
629 elsif p_field_name='EMPLOYMENT_CATEGORY' then
630 l_a_where_clause:=build_varchar2_where('per_asg.employment_category',p_condition,p_value
631 ,'hr_lookups hlec_a','hlec_a.lookup_code','hlec_a.meaning',' and hlec_a.lookup_type=''EMP_CAT''');
632 l_asg:=TRUE;
633 elsif p_field_name='BENEFITS_GROUP' then
634 -- Bug 4060365 Changed the parameter from per.benefit_group to per.benefit_group_id and
635 -- ben_a.benefit_group_id to ben_a.benfts_grp_id
636 l_a_where_clause:=build_varchar2_where('per.benefit_group_id',p_condition,p_value
637 ,'ben_benfts_grp ben_a','ben_a.benfts_grp_id','ben_a.name');
638 elsif p_field_name='VACANCY' then
639 l_a_where_clause:=build_varchar2_where('per_asg.vacancy_id',p_condition,p_value
640 ,'per_all_vacancies vac_a','vac_a.vacancy_id','vac_a.name');
641 l_asg:=TRUE;
642 elsif p_field_name='REQUISITION' then
643 l_a_where_clause:=build_varchar2_where('per_asg.vacancy_id',p_condition,p_value
644 ,'per_all_vacancies vac_a2,per_requisitions rec_a','vac_a2.vacancy_id','rec_a.name'
645 ,' and vac_a2.requisition_id=rec_a.requisition_id');
646 l_asg:=TRUE;
647 elsif p_field_name='JOB' then
648 l_a_where_clause:=build_varchar2_where('per_asg.job_id',p_condition,p_value
649 ,'per_jobs_v job_a','job_a.job_id','job_a.name');
650 l_asg:=TRUE;
651 elsif p_field_name='POSITION' then
652 -- PMFLETCH - Now using VL translation table
653 l_a_where_clause:=build_varchar2_where('per_asg.position_id',p_condition,p_value
654 ,'hr_all_positions_f_vl pos_a','pos_a.position_id','pos_a.name'); -- Bug 3891920
655 l_asg:=TRUE;
656 elsif p_field_name='GRADE' then
657 l_a_where_clause:=build_grade_where(p_condition,p_value);
658 l_asg:=TRUE;
659 elsif p_field_name='GRADE_LADDER' then
660 l_a_where_clause:=build_varchar2_where('per_asg.grade_ladder_pgm_id',p_condition,p_value
661 ,'ben_pgm_f pgm','pgm.pgm_id','pgm.name');
662 l_asg:=TRUE;
663 elsif p_field_name='PEOPLE_GROUP' then
664 l_a_where_clause:=build_varchar2_where('per_asg.people_group_id',p_condition,p_value
665 ,'pay_people_groups pgp_a','pgp_a.people_group_id','pgp_a.group_name');
666 l_asg:=TRUE;
667 elsif p_field_name='SUPERVISOR' then
668 l_a_where_clause:=build_varchar2_where('per_asg.supervisor_id',p_condition,p_value
669 ,'per_all_people_f sup_a','sup_a.person_id','sup_a.full_name');
670 l_asg:=TRUE;
671 elsif p_field_name='SUPERVISOR_ASSIGNMENT_NUMBER' then
672 l_a_where_clause:=build_varchar2_where('per_asg.supervisor_assignment_id',p_condition,p_value
673 ,'per_all_assignments_f supan_a','supan_a.assignment_id','supan_a.assignment_number');
674 l_asg:=TRUE;
675 elsif p_field_name='ORGANIZATION' then
676 l_a_where_clause:=build_varchar2_where('per_asg.organization_id',p_condition,p_value
677 ,'hr_all_organization_units org_a','org_a.organization_id','org_a.name');
678 l_asg:=TRUE;
679 elsif p_field_name='LOCATION' then
680 l_a_where_clause:=build_varchar2_where('per_asg.location_id',p_condition,p_value
681 ,'hr_locations loc_a','loc_a.location_id','loc_a.location_code');
682 l_asg:=TRUE;
683 elsif p_field_name='PAYROLL' then
684 l_a_where_clause:=build_varchar2_where('per_asg.payroll_id',p_condition,p_value
685 ,'pay_all_payrolls pay_a','pay_a.payroll_id','pay_a.payroll_name');
686 --*** Changed pay_a.name to pay_a.payroll_name. Bug2830622
687 l_asg:=TRUE;
688 elsif p_field_name='PROJECTED_HIRE_DATE' then
689 l_a_where_clause:=build_date_where('per.person_id',p_condition,p_value
690 ,'per_applications pap_a ','pap_a.person_id','pap_a.projected_hire_date');
691 elsif p_field_name='HIRE_DATE' then
692 l_a_where_clause:=build_date_where('per.person_id',p_condition,p_value
693 ,'per_periods_of_service pds_a ','pds_a.person_id','pds_a.date_start');
694 elsif p_field_name='ASSIGNMENT_STATUS' then
695 -- Bug 4060365 Removed the first comma placed before table s_a in the 4th parameter
696 --Modified for PMxbg
697 l_a_where_clause:=build_varchar2_where('per_asg.assignment_status_type_id'
698 ,p_condition
699 ,p_value
700 ,'per_assignment_status_types s_a,per_assignment_status_types_tl stl_a,per_ass_status_type_amends a_a,per_ass_status_type_amends_tl atl_a'
701 ,'s_a.assignment_status_type_id'
702 ,'nvl(atl_a.user_status,stl_a.user_status)'
703 ,' and a_a.assignment_status_type_id (+)=s_a.assignment_status_type_id'
704 ||' and s_a.assignment_status_type_id=stl_a.assignment_status_type_id'
705 ||' and a_a.ass_status_type_amend_id=atl_a.ass_status_type_amend_id(+)'
706 ||' and a_a.business_group_id (+) = s_a.business_group_id'
707 ||' and nvl(a_a.active_flag, s_a.active_flag)=''Y''
708 and decode(atl_a.language,null,''1'',atl_a.language)=decode(atl_a.language,null,''1'',userenv(''LANG''))
709 and stl_a.language=userenv(''LANG'')');
710 l_asg:=TRUE;
711 elsif p_field_name='START_DATE' then
712 l_a_where_clause:=build_date_where('per.start_date',p_condition,p_value);
713 elsif p_field_name='BACKGROUND_DATE_CHECK' then
714 l_a_where_clause:=build_date_where('per.background_date_check',p_condition,p_value);
715 elsif p_field_name='DATE_EMPLOYEE_DATA_VERIFIED' then
716 l_a_where_clause:=build_date_where('per.date_employee_data_verified',p_condition,p_value);
717 elsif p_field_name='EMAIL_ADDRESS' then
718 l_a_where_clause:=build_varchar2_where('per.email_address',p_condition,p_value);
719 elsif p_field_name='FTE_CAPACITY' then
720 l_a_where_clause:=build_number_where('per.fte_capacity',p_condition,p_value);
721 elsif p_field_name='HOLD_APPLICANT_DATE_UNTIL' then
722 l_a_where_clause:=build_date_where('per.hold_applicant_date_until',p_condition,p_value);
723 elsif p_field_name='HONORS' then
724 l_a_where_clause:=build_varchar2_where('per.honors',p_condition,p_value);
725 elsif p_field_name='INTERNAL_LOCATION' then
726 l_a_where_clause:=build_varchar2_where('per.internal_location',p_condition,p_value);
727 elsif p_field_name='KNOWN_AS' then
728 l_a_where_clause:=build_varchar2_where('per.known_as',p_condition,p_value);
729 elsif p_field_name='LAST_MEDICAL_TEST_BY' then
730 l_a_where_clause:=build_varchar2_where('per.last_medical_test_by',p_condition,p_value);
731 elsif p_field_name='LAST_MEDICAL_TEST_DATE' then
732 l_a_where_clause:=build_date_where('per.last_medical_test_date',p_condition,p_value);
733 elsif p_field_name='MAILSTOP' then
734 l_a_where_clause:=build_varchar2_where('per.mailstop',p_condition,p_value);
735 elsif p_field_name='MIDDLE_NAMES' then
736 l_a_where_clause:=build_varchar2_where('per.middle_names',p_condition,p_value);
737 elsif p_field_name='OFFICE_NUMBER' then
738 l_a_where_clause:=build_varchar2_where('per.office_number',p_condition,p_value);
739 elsif p_field_name='PREVIOUS_LAST_NAME' then
740 l_a_where_clause:=build_varchar2_where('per.previous_last_name',p_condition,p_value);
741 elsif p_field_name='REHIRE_AUTHORIZOR' then
742 l_a_where_clause:=build_varchar2_where('per.rehire_authorizor',p_condition,p_value);
743 elsif p_field_name='REHIRE_REASON' then
744 l_a_where_clause:=build_varchar2_where('per.rehire_reason',p_condition,p_value);
745 elsif p_field_name='RESUME_LAST_UPDATED' then
746 l_a_where_clause:=build_date_where('per.resume_last_updated',p_condition,p_value);
747 elsif p_field_name='SUFFIX' then
748 l_a_where_clause:=build_varchar2_where('per.suffix',p_condition,p_value);
749 elsif p_field_name='PREFIX' then
750 -- Bug 4060365 Changed the parameter from per.prefix to per.pre_name_adjunct
751 l_a_where_clause:=build_varchar2_where('per.pre_name_adjunct',p_condition,p_value);
752 elsif p_field_name='SALARY_BASIS' then
753 l_a_where_clause:=build_varchar2_where('per_asg.pay_basis_id',p_condition,p_value
754 ,'per_pay_bases ppb_a','ppb_a.pay_basis_id','ppb_a.name');
755 l_asg:=TRUE;
756 elsif p_field_name='BARGAINING_UNIT' then
757 -- Bug 4060365 Changed the parameter from per.barganing_unit_code to per.bargaining_unit_code
758 l_a_where_clause:=build_varchar2_where('per_asg.bargaining_unit_code',p_condition,p_value
759 ,'hr_lookups hlbu_a','hlbu_a.lookup_code','hlbu_a.meaning',' and hlbu_a.lookup_type=''BARGAINING_UNIT_CODE''');
760 l_asg:=TRUE;
761 elsif p_field_name='COORD_BEN_MED_PLN_NO' then
762 l_a_where_clause:=build_varchar2_where('per.coord_ben_med_pln_no',p_condition,p_value);
763 elsif p_field_name='DPDNT_ADOPTION_DATE' then
764 l_a_where_clause:=build_date_where('per.dpdnt_adoption_date',p_condition,p_value);
765 elsif p_field_name='CHANGE_REASON' then
766 l_a_where_clause:=build_varchar2_where('per_asg.change_reason',p_condition,p_value
767 ,'hr_lookups hlcr_a','hlcr_a.lookup_code','hlcr_a.meaning',' and hlcr_a.lookup_type=''APL_ASSIGN_REASON''');
768 l_asg:=TRUE;
769 elsif p_field_name='DATE_PROBATION_END' then
770 l_a_where_clause:=build_date_where('per_asg.date_probation_end',p_condition,p_value);
771 l_asg:=TRUE;
772 elsif p_field_name='INTERNAL_ADDRESS_LINE' then
773 l_a_where_clause:=build_varchar2_where('per_asg.internal_address_line',p_condition,p_value);
774 l_asg:=TRUE;
775 elsif p_field_name='NORMAL_HOURS' then
776 l_a_where_clause:=build_number_where('per_asg.normal_hours',p_condition,p_value);
777 l_asg:=TRUE;
778 elsif p_field_name='TIME_NORMAL_FINISH' then
779 l_a_where_clause:=build_varchar2_where('per_asg.time_normal_finish',p_condition,p_value);
780 l_asg:=TRUE;
781 elsif p_field_name='TIME_NORMAL_START' then
782 l_a_where_clause:=build_varchar2_where('per_asg.time_normal_start',p_condition,p_value);
783 l_asg:=TRUE;
784 end if;
785 return l_a_where_clause;
786 end advanced_where;
787 begin
788 --
789 hr_utility.set_location('Entering: '||l_proc,10);
790 l_effective_date:='to_date('''||to_char(p_effective_date,'DDMMYYYY')||''',''DDMMYYYY'')';
791 --
792 --for bug 2632619 conditionally switch the from clause
793 if secure='Y' then
794 l_from_clause:=' from per_people_f per';
795 else
796 l_from_clause:=' from per_all_people_f per';
797 end if;
798
799 --Modified for PMxbg
800
801 l_where_clause:=' where '||l_effective_date||' between per.effective_start_date and per.effective_end_date';
802 --commented for PMP and modified upper line
803 --'and' ||'per.business_group_id+0=nvl('||business_group_id||',per.business_group_id)';
804 --
805 l_effective_date_clause :=
806 'to_date('''||to_char(p_effective_date,'DDMMYYYY')||''',''DDMMYYYY'')';
807 --Modified for PMxbg
808
809 --commmented for PMP
810 -- if business_group_name is not null then
811 l_from_clause:=l_from_clause||',per_business_groups b';
812 l_where_clause:=l_where_clause||' and per.business_group_id=b.business_group_id'
813 ||' and (upper(b.name) like ('''||upper(replace(business_group_name,g_quote,g_quote||g_quote))||''') or '''
814 ||(replace(business_group_name,g_quote,g_quote||g_quote))||''' is null)'; --fix for bug 8648029
815 --commmented for PMP
816 -- end if;
817 --
818 --Modified for PMxbg
819
820 if person_id is not null then
821 l_where_clause:=l_where_clause||' and per.person_id='||person_id;
822 end if;
823 --
824 if person_type_id is not null then
825 l_where_clause:=l_where_clause
826 ||' AND per.person_id IN'
827 || ' (SELECT ptu.person_id'
828 || ' FROM per_person_type_usages_f ptu'
829 || ' WHERE ptu.person_type_id = '||person_type_id
830 || ' AND ' ||l_effective_date_clause
831 || ' BETWEEN ptu.effective_start_date'
832 || ' AND ptu.effective_end_date)';
833 else
834 if person_type is not null then
835 /* l_where_clause:=l_where_clause
836 ||' AND per.person_id IN'
837 || ' (SELECT ptu.person_id'
838 || ' FROM per_person_type_usages_f ptu'
839 || ' ,per_person_types_tl ppttl'
840 || ' WHERE ptu.person_type_id = ppttl.person_type_id'
841 || ' AND UPPER(ppttl.user_person_type)'
842 || ' LIKE '''||upper(replace(person_type,g_quote,g_quote||g_quote))||''''
843 || ' AND ppttl.language = userenv(''LANG'')'
844 || ' AND '||l_effective_date_clause
845 || ' BETWEEN ptu.effective_start_date'
846 || ' AND ptu.effective_end_date)';*/
847 -- Fix For Bug # 14145402 --
848 l_where_clause:=l_where_clause
849 ||' AND EXISTS'
850 || ' (SELECT /*+ push_subq no_unnest */ 1'
851 || ' FROM per_person_type_usages_f ptu'
852 || ' ,per_person_types_tl ppttl'
853 || ' WHERE ptu.person_type_id = ppttl.person_type_id'
854 || ' AND ptu.person_id=per.person_id'
855 || ' AND UPPER(ppttl.user_person_type)'
856 || ' LIKE '''||upper(replace(person_type,g_quote,g_quote||g_quote))||''''
857 || ' AND ppttl.language = userenv(''LANG'')'
858 || ' AND '||l_effective_date_clause
859 || ' BETWEEN ptu.effective_start_date'
860 || ' AND ptu.effective_end_date)';
861
862
863 end if;
864 if system_person_type is not null then
865 l_where_clause:=l_where_clause
866 ||' AND per.person_id IN'
867 || ' (SELECT ptu.person_id'
868 || ' FROM per_person_type_usages_f ptu'
869 || ' ,per_person_types ppt'
870 || ' WHERE ptu.person_type_id = ppt.person_type_id'
871 || ' AND ppt.system_person_type ='''||system_person_type||''''
872 || ' AND '||l_effective_date_clause
873 || ' BETWEEN ptu.effective_start_date'
874 || ' AND ptu.effective_end_date)';
875 end if;
876 end if;
877 --
878 --Modified for PMxbg
879
880 if last_name is not null then
881 l_where_clause:=l_where_clause||' and upper(per.last_name) like ('''||upper(replace(last_name,g_quote,g_quote||g_quote))||''')';
882 end if;
883 --
884 if start_date is not null then
885 l_where_clause:=l_where_clause||' and per.start_date=to_date('''||to_char(start_date,'DD/MM/YYYY')
886 ||''',''DD/MM/YYYY'')';
887 end if;
888 --
889 if hire_date is not null then
890 l_from_clause:=l_from_clause||',per_periods_of_service pds';
891 l_where_clause:=l_where_clause||' and pds.person_id=per.person_id'
892 ||' and pds.date_start=to_date('''||to_char(hire_date,'DD/MM/YYYY')
893 ||''',''DD/MM/YYYY'')';
894 end if;
895 --
896 if applicant_number is not null then
897 l_where_clause:=l_where_clause||' and upper(per.applicant_number) like ('''||upper(replace(applicant_number,g_quote,g_quote||g_quote))||''')';
898 end if;
899 --
900 if date_of_birth is not null then
901 l_where_clause:=l_where_clause||' and per.date_of_birth=to_date('''||to_char(date_of_birth,'DD/MM/YYYY')
902 ||''',''DD/MM/YYYY'')';
903 end if;
904 --
905 if email_address is not null then
906 l_where_clause:=l_where_clause||' and upper(per.email_address) like ('''||upper(replace(email_address,g_quote,g_quote||g_quote))||''')';
907 end if;
908 --
909 if employee_number is not null then
910 l_where_clause:=l_where_clause||' and upper(per.employee_number) like ('''||upper(replace(employee_number,g_quote,g_quote||g_quote))||''')';
911 end if;
912 --
913 if npw_number is not null then
914 l_where_clause:=l_where_clause||' and upper(per.npw_number) like ('''||upper(replace(npw_number,g_quote,g_quote||g_quote))||''')';
915 end if;
916 --
917 if first_name is not null then
918 l_where_clause:=l_where_clause||' and upper(per.first_name) like ('''||upper(replace(first_name,g_quote,g_quote||g_quote))||''')';
919 end if;
920 --
921 if full_name is not null then
922 l_where_clause:=l_where_clause||' and upper(per.full_name) like ('''||upper(replace(full_name,g_quote,g_quote||g_quote))||''')';
923 end if;
924 --
925 if title is not null then
926 l_where_clause:=l_where_clause||' and per.title = '''||replace(title,g_quote,g_quote||g_quote)||'''';
927 end if;
928 --
929 if middle_names is not null then
930 l_where_clause:=l_where_clause||' and upper(per.middle_names) like ('''||upper(replace(middle_names,g_quote,g_quote||g_quote))||''')';
931 end if;
932 --
933 if nationality is not null then
934 l_where_clause:=l_where_clause||' and per.nationality='''||nationality||'''';
935 elsif nationality_meaning is not null then
936 l_from_clause:=l_from_clause||',hr_lookups hlnat';
937 l_where_clause:=l_where_clause||' and per.nationality=hlnat.lookup_code'
938 ||' and hlnat.lookup_type=''NATIONALITY'''
939 ||' and upper(hlnat.meaning) like ('''||upper(replace(nationality_meaning,g_quote,g_quote||g_quote))||''')';
940 end if;
941 --
942 if national_identifier is not null then
943 l_where_clause:=l_where_clause||' and upper(per.national_identifier) like ('''||upper(replace(national_identifier,g_quote,g_quote||g_quote))||''')';
944 end if;
945 --
946 if registered_disabled is not null then
947 l_where_clause:=l_where_clause||' and per.registered_disabled_flag='''||registered_disabled||'''';
948
949 -- Bug 3037019 Start Here
950 elsif registered_disabled_flag is not null then
951 l_from_clause:=l_from_clause||',hr_lookups hlnat';
952 l_where_clause:=l_where_clause||' and per.registered_disabled_flag=hlnat.lookup_code'
953 ||' and hlnat.lookup_type=''REGISTERED_DISABLED'''
954 ||' and upper(hlnat.meaning) like ('''||upper(replace(registered_disabled_flag,g_quote,g_quote||g_quote))||''')';
955 -- Bug 3037019 Ends Here
956
957 end if;
958 --
959 if sex is not null then
960 l_where_clause:=l_where_clause||' and per.sex='''||sex||'''';
961 elsif sex_meaning is not null then
962 l_from_clause:=l_from_clause||',hr_lookups hlsex';
963 l_where_clause:=l_where_clause||' and per.sex=hlsex.lookup_code'
964 ||' and hlsex.lookup_type=''SEX'''
965 ||' and upper(hlsex.meaning) like ('''||upper(replace(sex_meaning,g_quote,g_quote||g_quote))||''')';
966 end if;
967 --
968 if benefit_group_id is not null then
969 l_where_clause:=l_where_clause||' and per.benefit_group_id='||benefit_group_id;
970 elsif benefit_group is not null then
971 l_from_clause:=l_from_clause||',ben_benfts_grp per_ben';
972 l_where_clause:=l_where_clause||' and per.benefit_group_id=per_ben.benefit_group_id'
973 ||' and upper(per_ben.name) like ('''||upper(replace(benefit_group,g_quote,g_quote||g_quote))||''')';
974 end if;
975 --
976 --CWK
977 --
978 if project_title is not null then
979 l_where_clause:=l_where_clause||' and per_asg.project_title='''||replace(project_title,g_quote,g_quote||g_quote)||'''';
980 l_asg:=TRUE;
981 end if;
982 --
983 if vendor_employee_number is not null then
984 l_where_clause:=l_where_clause||' and per_asg.vendor_employee_number='''||replace(vendor_employee_number,g_quote,g_quote||g_quote)||'''';
985 l_asg:=TRUE;
986 end if;
987 --
988 if vendor_assignment_number is not null then
989 l_where_clause:=l_where_clause||' and per_asg.vendor_assignment_number='''||replace(vendor_assignment_number,g_quote,g_quote||g_quote)||'''';
990 l_asg:=TRUE;
991 end if;
992 --
993 if vendor_id is not null then
994 l_where_clause:=l_where_clause||' and per_asg.vendor_id='||vendor_id;
995 l_asg:=TRUE;
996 elsif vendor_name is not null then
997 l_from_clause:=l_from_clause||',po_vendors pov';
998 l_where_clause:=l_where_clause||' and per_asg.vendor_id=pov.vendor_id'
999 ||' and upper(pov.vendor_name) like ('''||upper(replace(vendor_name,g_quote,g_quote||g_quote))||''')';
1000 l_asg:=TRUE;
1001 end if;
1002 --
1003 if vendor_site_id is not null then
1004 l_where_clause:=l_where_clause||' and per_asg.vendor_site_id='||vendor_site_id;
1005 l_asg:=TRUE;
1006 elsif vendor_site_code is not null then
1007 l_from_clause:=l_from_clause||',po_vendor_sites povs';
1008 l_where_clause:=l_where_clause||' and per_asg.vendor_site_id=povs.vendor_site_id'
1009 ||' and upper(povs.vendor_site_code) like ('''||upper(replace(vendor_site_code,g_quote,g_quote||g_quote))||''')';
1010 l_asg:=TRUE;
1011 end if;
1012 --
1013 if po_header_id is not null then
1014 l_where_clause:=l_where_clause||' and per_asg.po_header_id='||po_header_id;
1015 l_asg:=TRUE;
1016 elsif po_header_num is not null then
1017 l_from_clause:=l_from_clause||',po_headers_all poh';
1018 l_where_clause:=l_where_clause||' and per_asg.po_header_id=poh.po_header_id'
1019 ||' and upper(poh.segment1) like ('''||upper(replace(po_header_num,g_quote,g_quote||g_quote))||''')';
1020 l_asg:=TRUE;
1021 end if;
1022 --
1023 if po_line_id is not null then
1024 l_where_clause:=l_where_clause||' and per_asg.po_line_id='||po_line_id;
1025 l_asg:=TRUE;
1026 elsif po_line_num is not null then
1027 l_from_clause:=l_from_clause||',po_lines_all pol';
1028 l_where_clause:=l_where_clause||' and per_asg.po_line_id=pol.po_line_id'
1029 ||' and upper(pol.line_num) like ('''||upper(replace(po_line_num,g_quote,g_quote||g_quote))||''')';
1030 l_asg:=TRUE;
1031 end if;
1032 --Modified for PMxbg
1033 if grade_id is not null then
1034 l_where_clause:=l_where_clause||' and per_asg.grade_id='||grade_id;
1035 l_asg:=TRUE;
1036 elsif grade is not null then
1037 l_from_clause:=l_from_clause||',per_grades_vl grd';
1038 l_where_clause:=l_where_clause||' and per_asg.grade_id=grd.grade_id'
1039 ||' and upper(grd.name) like ('''||upper(replace(grade,g_quote,g_quote||g_quote))||''')'
1040 ||' and grd.business_group_id=per.business_group_id';
1041 l_asg:=TRUE;
1042 end if;
1043 --Modified for PMxbg
1044
1045 if grade_ladder_pgm_id is not null then
1046 l_where_clause:=l_where_clause||' and per_asg.grade_ladder_pgm_id='||grade_ladder_pgm_id;
1047 l_asg:=TRUE;
1048 elsif grade_ladder is not null then
1049 l_from_clause:=l_from_clause||',ben_pgm_f pgm';
1050 l_where_clause:=l_where_clause||' and per_asg.grade_ladder_pgm_id=pgm.pgm_id'
1051 ||' and upper(pgm.name) like ('''||upper(replace(grade_ladder,g_quote,g_quote||g_quote))||''')'
1052 --Modified for PMxbg
1053 ||' and gpm.business_group_id+0=nvl('||business_group_id||',gpm.business_group_id)'
1054 ||' and '||l_effective_date||' between pgm.effective_start_date'
1055 ||' and pgm.effective_end_date';
1056 l_asg:=TRUE;
1057 end if;
1058 --Modified for PMxbg
1059 if position_id is not null then
1060 l_where_clause:=l_where_clause||' and per_asg.position_id='||position_id;
1061 l_asg:=TRUE;
1062 elsif position is not null then
1063 -- PMFLETCH - Now using VL translation table
1064 l_from_clause:=l_from_clause||',hr_all_positions_f_vl per_pos'; -- Bug 3891920
1065 l_where_clause:=l_where_clause||' and per_asg.position_id=per_pos.position_id'
1066 ||' and upper(per_pos.name) like ('''||upper(replace(position,g_quote,g_quote||g_quote))||''')'
1067 ||' and per_pos.business_group_id=per.business_group_id';
1068 l_asg:=TRUE;
1069 end if;
1070 --Modified for PMxbg
1071
1072 if job_id is not null then
1073 l_where_clause:=l_where_clause||' and per_asg.job_id='||job_id;
1074 l_asg:=TRUE;
1075 elsif job is not null then
1076 l_from_clause:=l_from_clause||',per_jobs_v job';
1077 l_where_clause:=l_where_clause||' and per_asg.job_id=job.job_id'
1078 ||' and upper(job.name) like ('''||upper(replace(job,g_quote,g_quote||g_quote))||''')'
1079 ||' and job.business_group_id = per.business_group_id';
1080 l_asg:=TRUE;
1081 end if;
1082 --Modified for PMxbg
1083
1084 if assignment_status_type_id is not null then
1085 l_where_clause:=l_where_clause||' and per_asg.assignment_status_type_id='||assignment_status_type_id;
1086 l_asg:=TRUE;
1087 elsif assignment_status_type is not null then
1088 l_from_clause:=l_from_clause||',per_assignment_status_types s,per_assignment_status_types_tl stl';
1089 l_from_clause:=l_from_clause||',per_ass_status_type_amends a,per_ass_status_type_amends_tl atl';
1090 l_where_clause:=l_where_clause||' and upper(nvl(atl.user_status,stl.user_status))'
1091 ||' like ('''||upper(replace(assignment_status_type,g_quote,g_quote||g_quote))||''')'
1092 ||' and s.assignment_status_type_id=per_asg.assignment_status_type_id'
1093 ||' and a.assignment_status_type_id (+)=s.assignment_status_type_id'
1094 ||' and s.assignment_status_type_id =stl.assignment_status_type_id'
1095 ||' and a.ass_status_type_amend_id=atl.ass_status_type_amend_id(+)'
1096 ||' and a.business_group_id (+)= s.business_group_id'
1097 ||' and nvl(a.active_flag, s.active_flag)=''Y'''
1098 ||' and decode(atl.language,null,''1'',atl.language)=decode(atl.language,null,''1'',userenv(''LANG''))'
1099 ||' and stl.language=userenv(''LANG'')';
1100 l_asg:=TRUE;
1101 end if;
1102 --Modified for PMxbg
1103
1104 if payroll_id is not null then
1105 l_where_clause:=l_where_clause||' and per_asg.payroll_id='||payroll_id;
1106 l_asg:=TRUE;
1107 elsif payroll is not null then
1108 l_from_clause:=l_from_clause||',pay_all_payrolls_f pay';
1109 l_where_clause:=l_where_clause||' and per_asg.payroll_id=pay.payroll_id'
1110 ||' and upper(pay.payroll_name) like ('''||upper(replace(payroll,g_quote,g_quote||g_quote))||''')'
1111 ||' and pay.business_group_id = per.business_group_id'
1112 ||' and '||l_effective_date||' between pay.effective_start_date'
1113 ||' and pay.effective_end_date';
1114 l_asg:=TRUE;
1115 end if;
1116 --
1117 if location_id is not null then
1118 l_where_clause:=l_where_clause||' and per_asg.location_id='||location_id;
1119 l_asg:=TRUE;
1120 elsif location is not null then
1121 l_from_clause:=l_from_clause||',hr_locations loc';
1122 l_where_clause:=l_where_clause||' and per_asg.location_id=loc.location_id'
1123 ||' and upper(loc.location_code) like ('''||upper(replace(location,g_quote,g_quote||g_quote))||''')';
1124 l_asg:=TRUE;
1125 end if;
1126 --Modified for PMxbg
1127 if supervisor_id is not null then
1128 l_where_clause:=l_where_clause||' and per_asg.supervisor_id='||supervisor_id;
1129 l_asg:=TRUE;
1130 elsif supervisor is not null then
1131 l_from_clause:=l_from_clause||',per_all_people_f sup';
1132 l_where_clause:=l_where_clause||' and per_asg.supervisor_id=sup.person_id'
1133 ||' and upper(sup.full_name) like ('''||upper(replace(supervisor,g_quote,g_quote||g_quote))||''')'
1134 ||' and '||l_effective_date||' between sup.effective_start_date and sup.effective_end_date'
1135 ||' and sup.business_group_id=per.business_group_id';
1136 l_asg:=TRUE;
1137 end if;
1138 --Modified for PMxbg
1139 if supervisor_assignment_id is not null then
1140 l_where_clause:=l_where_clause||' and per_asg.supervisor_assignment_id='||supervisor_assignment_id;
1141 l_asg:=TRUE;
1142 elsif supervisor_assignment_number is not null then
1143 l_from_clause:=l_from_clause||',per_all_assignments_f supan';
1144 l_where_clause:=l_where_clause||' and per_asg.supervisor_assignment_id=supan.assignment_id'
1145 ||' and upper(supan.assignment_number)
1146 like ('''||upper(replace(supervisor_assignment_number,g_quote,g_quote||g_quote))||''')'
1147 ||' and '||l_effective_date||' between supan.effective_start_date and supan.effective_end_date'
1148 ||' and supan.business_group_id = per.business_group_id';
1149 l_asg:=TRUE;
1150 end if;
1151 --
1152 if recruitment_activity_id is not null then
1153 l_where_clause:=l_where_clause||' and per_asg.recruitment_activity_id='||recruitment_activity_id;
1154 l_asg:=TRUE;
1155 elsif recruitment_activity is not null then
1156 l_from_clause:=l_from_clause||',per_recruitment_activities ract';
1157 l_where_clause:=l_where_clause||' and per_asg.recruitment_activity_id = ract.recruitment_activity_id'
1158 ||' and upper(ract.name) like ('''||upper(replace(recruitment_activity,g_quote,g_quote||g_quote))||''')';
1159 l_asg:=TRUE;
1160 end if;
1161 --Modified for PMxbg
1162
1163 if organization_id is not null then
1164 l_where_clause:=l_where_clause||' and per_asg.organization_id='||organization_id;
1165 l_asg:=TRUE;
1166 elsif organization is not null then
1167 l_from_clause:=l_from_clause||',hr_all_organization_units_tl houtl,hr_all_organization_units hou';
1168 l_where_clause:=l_where_clause||' and per_asg.organization_id=hou.organization_id'
1169 ||' and hou.organization_id=houtl.organization_id'
1170 ||' and upper(houtl.name) like ('''||upper(replace(organization,g_quote,g_quote||g_quote))||''')'
1171 ||' and hou.internal_external_flag=''INT'''
1172 ||' and houtl.language=userenv(''LANG'')'
1173 ||' and hou.business_group_id=per.business_group_id';
1174 l_asg:=TRUE;
1175 end if;
1176 --
1177 if people_group_id is not null then
1178 l_where_clause:=l_where_clause||' and per_asg.people_group_id='||people_group_id;
1179 l_asg:=TRUE;
1180 elsif people_group is not null then
1181 l_from_clause:=l_from_clause||',pay_people_groups ppg';
1182 l_where_clause:=l_where_clause||' and per_asg.people_group_id=ppg.people_group_id'
1183 ||' and upper(ppg.group_name) like ('''||upper(replace(people_group,g_quote,g_quote||g_quote))||''')';
1184 l_asg:=TRUE;
1185 end if;
1186 --Modified for PMxbg
1187 if vacancy_id is not null then
1188 l_where_clause:=l_where_clause||' and per_asg.vacancy_id='||vacancy_id;
1189 l_asg:=TRUE;
1190 elsif vacancy is not null then
1191 l_from_clause:=l_from_clause||',per_all_vacancies vac';
1192 l_where_clause:=l_where_clause||' and per_asg.vacancy_id=vac.vacancy_id'
1193 ||' and upper(vac.name) like ('''||upper(replace(vacancy,g_quote,g_quote||g_quote))||''')'
1194 ||' and vac.business_group_id = per.business_group_id';
1195 l_asg:=TRUE;
1196 end if;
1197 --Modified for PMxbg
1198 if requisition_id is not null then
1199 if vacancy_id is null then
1200 if vacancy is null then
1201 l_from_clause:=l_from_clause||',per_all_vacancies vac';
1202 l_where_clause:=l_where_clause||' and per_asg.vacancy_id=vac.vacancy_id'
1203 ||' and vac.business_group_id = per.business_group_id';
1204 end if;
1205 l_where_clause:=l_where_clause||' and vac.requisition_id='||requisition_id;
1206 else
1207 l_from_clause:=l_from_clause||',per_all_vacancies vac';
1208 l_where_clause:=l_where_clause||' and vac.requisition_id='||requisition_id
1209 ||' and vac.vacancy_id=per_asg.vacancy_id';
1210 end if;
1211 l_asg:=TRUE;
1212 elsif requisition is not null then
1213 if vacancy_id is null then
1214 if vacancy is null then
1215 l_from_clause:=l_from_clause||',per_all_vacancies vac';
1216 l_where_clause:=l_where_clause||' and per_asg.vacancy_id=vac.vacancy_id'
1217 ||' and vac.business_group_id = per.business_group_id';
1218 end if;
1219 l_from_clause:=l_from_clause||',per_requisitions rec';
1220 l_where_clause:=l_where_clause||' and vac.requisition_id=rec.requisition_id'
1221 ||' and upper(rec.name) like ('''||upper(replace(requisition,g_quote,g_quote||g_quote))||''')';
1222 else
1223 l_from_clause:=l_from_clause||',per_all_vacancies vac, per_requisitions rec';
1224 l_where_clause:=l_where_clause||' and vac.requisition_id,rec.requisition_id'
1225 ||' and vac.requisition_id=rec.requisition_id'
1226 ||' and vac.vacancy_id=per_asg.vacancy_id'
1227 ||' and upper(rec.name) like ('''||upper(replace(requisition,g_quote,g_quote||g_quote))||''')';
1228 end if;
1229 l_asg:=TRUE;
1230 end if;
1231 -- Modified for PMxbg
1232 if pay_basis_id is not null then
1233 l_where_clause:=l_where_clause||' and per_asg.pay_basis_id='||pay_basis_id;
1234 l_asg:=TRUE;
1235 elsif salary_basis is not null then
1236 l_from_clause:=l_from_clause||',per_pay_bases ppb';
1237 l_where_clause:=l_where_clause||' and per_asg.pay_basis_id=ppb.pay_basis_id'
1238 ||' and upper(ppb.name) like ('''||upper(replace(salary_basis,g_quote,g_quote||g_quote))||''')'
1239 ||' and ppb.business_group_id = per.business_group_id';
1240 l_asg:=TRUE;
1241 end if;
1242 --
1243 if bargaining_unit_code is not null then
1244 l_where_clause:=l_where_clause||' and per_asg.bargaining_unit_code='''||bargaining_unit_code||'''';
1245 l_asg:=TRUE;
1246 elsif bargaining_unit_code_meaning is not null then
1247 l_from_clause:=l_from_clause||',hr_lookups buc';
1248 l_where_clause:=l_where_clause||' and per_asg.bargaining_unit_code=buc.lookup_code'
1249 ||' and buc.lookup_type=''BARGAINING_UNIT_CODE'''
1250 ||' and upper(buc.meaning) like ('''||upper(replace(bargaining_unit_code_meaning,g_quote,g_quote||g_quote))||''')';
1251 l_asg:=TRUE;
1252 end if;
1253 --
1254 if employment_category is not null then
1255 l_where_clause:=l_where_clause||' and per_asg.employment_category='''||employment_category||'''';
1256 l_asg:=TRUE;
1257 elsif employment_category_meaning is not null then
1258 l_from_clause:=l_from_clause||',hr_lookups empc';
1259 l_where_clause:=l_where_clause||' and per_asg.employment_category=empc.lookup_code
1260 and empc.lookup_type=''EMP_CAT''
1261 and upper(empc.meaning) like ('''||upper(replace(employment_category_meaning,g_quote,g_quote||g_quote))||''')';
1262 l_asg:=TRUE;
1263 end if;
1264 --
1265 if establishment_id is not null then
1266 --bug 3002915 starts here.
1267 --added new code replacing old.
1268 --
1269 --l_where_clause:=l_where_clause||' and exists'
1270 --||' (select 1'
1271 --||' from per_establishment_attendances eta'
1272 --||' where eta.establishment_id='||establishment_id
1273 --||' and eta.person_id=per.person_id)';
1274 --
1275 l_where_clause:=l_where_clause||' and per_asg.establishment_id ='||establishment_id;
1276 l_asg:=TRUE;
1277 -- bug 3002915 ends here.
1278 elsif establishment is not null then
1279 l_where_clause:=l_where_clause||' and exists'
1280 ||' (select 1'
1281 ||' from hr_leg_establishments_v hle'
1282 ||' where hle.organization_id = per_asg.establishment_id and upper(hle.name) like('''||upper(establishment)||'''))';
1283
1284 l_asg:=TRUE;
1285 --bug 3002915.
1286 -- ||' from per_establishment_attendances eta'
1287 -- ||',per_establishments est'
1288 -- ||' where eta.establishment_id=est.establishment_id'
1289 -- ||' and eta.person_id=per.person_id'
1290 -- ||' and upper(est.name) like('''||upper(replace(establishment,g_quote,g_quote||g_quote))||'''))';
1291 end if;
1292 --
1293 if projected_hire_date is not null then
1294 l_from_clause:=l_from_clause||',per_applications appl';
1295 l_where_clause:=l_where_clause||' and appl.projected_hire_date=to_date('''||
1296 to_char(projected_hire_date,'DD/MM/YYYY')||''',''DD/MM/YYYY'')'
1297 ||' and per.person_id=appl.person_id';
1298 end if;
1299 --
1300 --removed for bug 2632619, replaced by conditional change of l_from_clause
1301 -- if secure='Y' then
1302 -- l_where_clause:=l_where_clause||' and (hr_security.view_all=''Y'''
1303 -- ||' or hr_security.show_record(''PER_ALL_PEOPLE_F'''
1304 -- ||',per.person_id,per.person_type_id,per.employee_number,per.applicant_number)=''TRUE'')';
1305 -- end if;
1306 --
1307 if field1_name is not null and field1_condition_code is not null then
1308 l_where_clause:=l_where_clause||advanced_where
1309 (p_field_name=> field1_name
1310 ,p_condition => field1_condition_code
1311 ,p_value => replace(field1_value,g_quote,g_quote||g_quote));
1312 end if;
1313 --
1314 if field2_name is not null and field2_condition_code is not null then
1315 l_where_clause:=l_where_clause||advanced_where
1316 (p_field_name=> field2_name
1317 ,p_condition => field2_condition_code
1318 ,p_value => replace(field2_value,g_quote,g_quote||g_quote));
1319 end if;
1320 --
1321 if field3_name is not null and field3_condition_code is not null then
1322 l_where_clause:=l_where_clause||advanced_where
1323 (p_field_name=> field3_name
1324 ,p_condition => field3_condition_code
1325 ,p_value => replace(field3_value,g_quote,g_quote||g_quote));
1326 end if;
1327 --
1328 if field4_name is not null and field4_condition_code is not null then
1329 l_where_clause:=l_where_clause||advanced_where
1330 (p_field_name=> field4_name
1331 ,p_condition => field4_condition_code
1332 ,p_value => replace(field4_value,g_quote,g_quote||g_quote));
1333 end if;
1334 --
1335 if field5_name is not null and field5_condition_code is not null then
1336 l_where_clause:=l_where_clause||advanced_where
1337 (p_field_name=> field5_name
1338 ,p_condition => field5_condition_code
1339 ,p_value => replace(field5_value,g_quote,g_quote||g_quote));
1340 end if;
1341 --
1342 if l_asg then
1343 --
1344 -- Bug 4282150
1345 -- Added if condition to secure on each individual assignment
1346 --checkpoint for PMP
1347
1348 if secure='Y' then
1349 l_from_clause:=l_from_clause||',per_assignments_f2 per_asg';
1350 else
1351 l_from_clause:=l_from_clause||',per_all_assignments_f per_asg';
1352 end if;
1353 --
1354 l_where_clause:=l_where_clause||' and per.person_id=per_asg.person_id and '
1355 ||l_effective_date||
1356 ' between per_asg.effective_start_date and per_asg.effective_end_date '||
1357 ' AND per_asg.assignment_type <> ''B'''; -- Bug 3816589
1358 end if;
1359
1360
1361
1362 l_select_stmt_per:=
1363 'select per.person_id,nvl(per.order_name,per.full_name),count(*) '||l_from_clause||l_where_clause;
1364 if p_customized_restriction_id is not null then
1365 l_select_stmt_per:=l_select_stmt_per
1366 ||' AND (( EXISTS'
1367 || ' (SELECT 1'
1368 || ' FROM pay_restriction_values prv'
1369 || ' WHERE prv.customized_restriction_id = '
1370 || p_customized_restriction_id
1371 || ' AND prv.value IN'
1372 || ' (SELECT ptu.person_type_id'
1373 || ' FROM per_person_type_usages ptu'
1374 || ' WHERE ptu.person_id = per.person_id'
1375 || ' AND '||l_effective_date_clause
1376 || ' BETWEEN ptu.effective_start_date'
1377 || ' AND ptu.effective_end_date)'
1378 || ' AND NVL(prv.include_exclude_flag, ''I'') = ''I'' '
1379 || ' AND prv.restriction_code = ''PERSON_TYPE'')'
1380 || ' OR NOT EXISTS'
1381 || ' (SELECT 1'
1382 || ' FROM pay_restriction_values prv'
1383 || ' WHERE prv.customized_restriction_id ='
1384 || p_customized_restriction_id
1385 || ' AND NVL(prv.include_exclude_flag, ''I'') = ''I'' '
1386 || ' AND prv.restriction_code = ''PERSON_TYPE'') )'
1387 ||' AND NOT EXISTS '
1388 || ' (SELECT 1'
1389 || ' FROM pay_restriction_values prv2'
1390 || ' WHERE prv2.customized_restriction_id = '
1391 || p_customized_restriction_id
1392 || ' AND prv2.value IN'
1393 || ' (SELECT ptu2.person_type_id'
1394 || ' FROM per_person_type_usages ptu2'
1395 || ' WHERE ptu2.person_id = per.person_id'
1396 || ' AND '||l_effective_date_clause
1397 || ' BETWEEN ptu2.effective_start_date'
1398 || ' AND ptu2.effective_end_date)'
1399 || ' AND NVL(prv2.include_exclude_flag, ''I'') = ''E'' '
1400 || ' AND prv2.restriction_code = ''PERSON_TYPE''))';
1401 -- Removed the additional brace for fix of #3430507
1402 end if;
1403
1404 -- if not p_employees_allowed and not p_applicants_allowed then
1405 -- l_select_stmt_per:=l_select_stmt_per||
1406 -- ' and per.current_emp_or_apl_flag is null';
1407 -- end if;
1408 -- if p_employees_allowed and not p_applicants_allowed then
1409 -- l_select_stmt_per:=l_select_stmt_per||
1410 -- ' and (per.current_employee_flag =''Y''
1411 -- or per.current_emp_or_apl_flag is null)';
1412 -- end if;
1413 -- if not p_employees_allowed and p_applicants_allowed then
1414 -- l_select_stmt_per:=l_select_stmt_per||
1415 -- ' and (per.current_applicant_flag =''Y''
1416 -- or per.current_emp_or_apl_flag is null)';
1417 -- end if;
1418 -- if p_cwk_allowed then
1419 -- l_select_stmt_per:=l_select_stmt_per||
1420 -- ' and (per.current_npw_flag = ''Y''
1421 -- or per.current_emp_or_apl_flag is null)';
1422 -- end if;
1423 --AH bug 2854634:following clause replaces previous. All templates can see people who are
1424 --not "current" anything, then add conditions to also bring in types allowed by template
1425 --
1426 l_select_stmt_per:=l_select_stmt_per||
1427 ' and ( (per.current_emp_or_apl_flag is null and per.current_npw_flag is null)';
1428 if p_employees_allowed then
1429 l_select_stmt_per:=l_select_stmt_per||
1430 ' OR per.current_employee_flag =''Y'' ';
1431 end if;
1432 if p_applicants_allowed then
1433 l_select_stmt_per:=l_select_stmt_per||
1434 ' OR per.current_applicant_flag =''Y'' ';
1435 end if;
1436 if p_cwk_allowed then
1437 l_select_stmt_per:=l_select_stmt_per||
1438 ' OR per.current_npw_flag = ''Y'' ';
1439 end if;
1440 l_select_stmt_per:=l_select_stmt_per||')';
1441 --
1442 --bug 2854634 end
1443
1444
1445
1446 l_select_stmt_per:=l_select_stmt_per||' group by per.person_id,nvl(per.order_name,per.full_name) order by nvl(per.order_name,per.full_name)';
1447 hr_utility.set_location('r'||l_select_stmt_per,2001);
1448
1449 select_stmt:=l_from_clause||l_where_clause;
1450 l_select_stmt_asg:='select per_asg.assignment_id '||l_from_clause;
1451 if not l_asg then
1452 l_select_stmt_asg:=l_select_stmt_asg||',per_all_assignments_f per_asg';
1453 end if;
1454 l_select_stmt_asg:=l_select_stmt_asg||l_where_clause;
1455 if not l_asg then
1456 l_select_stmt_asg:=l_select_stmt_asg||' and per.person_id=per_asg.person_id and '
1457 ||l_effective_date||
1458 ' between per_asg.effective_start_date and per_asg.effective_end_date ';
1459 end if;
1460 l_select_stmt_asg:=l_select_stmt_asg||'and per_asg.assignment_type <> ''B''';-- Added for fix of #3286659
1461 l_select_stmt_asg:=l_select_stmt_asg||' and per.person_id=:1';
1462 l_select_stmt_per2:=l_select_stmt_per;
1463
1464 hr_utility.set_location('r'||l_select_stmt_per,2000);
1465
1466 while length(l_select_stmt_per2)>0 loop
1467
1468 hr_utility.set_location(substr(l_select_stmt_per2,1,70),1);
1469 l_select_stmt_per2:=substr(l_select_stmt_per2,71);
1470 end loop;
1471 --
1472
1473 hr_utility.set_location('hidd'||l_select_stmt_per,1000);
1474
1475 open emp_cv for l_select_stmt_per;
1476 loop
1477 num_row:=num_row+1;
1478 fetch emp_cv into l_person_id,l_full_name,l_num_asgs;
1479 exit when emp_cv%notfound;
1480 --
1481 open csr_person_details(p_person_id => l_person_id
1482 ,p_effective_date=> p_effective_date);
1483 fetch csr_person_details into per_rec;
1484 if csr_person_details%notfound then
1485 close csr_person_details;
1486 fnd_message.set_name(800,'XXX');
1487 fnd_message.raise_error;
1488 else
1489 close csr_person_details;
1490 end if;
1491 --
1492 -- set the output fields
1493 --
1494 out_rec.person_id :=per_rec.person_id;
1495 out_rec.full_name :=per_rec.full_name;
1496
1497 if p_fetch_details then
1498 out_rec.per_effective_start_date :=per_rec.effective_start_date;
1499 out_rec.per_effective_end_date :=per_rec.effective_end_date;
1500 --
1501 out_rec.person_type :=hr_person_type_usage_info.get_user_person_type
1502 (p_effective_date => p_effective_date
1503 ,p_person_id => per_rec.person_id);
1504 --
1505 out_rec.last_name :=per_rec.last_name;
1506 out_rec.start_date :=per_rec.start_date;
1507 out_rec.applicant_number :=per_rec.applicant_number;
1508 out_rec.background_chk_stat_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.background_check_status);
1509 out_rec.background_date_check :=per_rec.background_date_check;
1510 out_rec.blood_type_meaning :=hr_reports.get_lookup_meaning('BLOOD_TYPE',per_rec.blood_type);
1511
1512 --Modified for PMxbg
1513 if per_rec.person_id is not null then
1514 open csr_bg_name(per_rec.business_group_id);
1515 fetch csr_bg_name into out_rec.business_group_name;
1516 close csr_bg_name;
1517 else
1518 out_rec.business_group_name:=null;
1519 end if;
1520 --
1521 if per_rec.correspondence_language is not null then
1522 open csr_lang(per_rec.correspondence_language);
1523 fetch csr_lang into out_rec.corr_lang_meaning;
1524 close csr_lang;
1525 else
1526 out_rec.corr_lang_meaning:=null;
1527 end if;
1528 --
1529 out_rec.date_employee_data_verified :=per_rec.date_employee_data_verified;
1530 out_rec.date_of_birth :=per_rec.date_of_birth;
1531 out_rec.email_address :=per_rec.email_address;
1532 out_rec.employee_number :=per_rec.employee_number;
1533 out_rec.expnse_chk_send_addr_meaning :=hr_reports.get_lookup_meaning('HOME_OFFICE',per_rec.expense_check_send_to_address);
1534 out_rec.npw_number :=per_rec.npw_number;
1535 out_rec.first_name :=per_rec.first_name;
1536 out_rec.per_fte_capacity :=per_rec.fte_capacity;
1537 out_rec.full_name :=per_rec.full_name;
1538 out_rec.hold_applicant_date_until :=per_rec.hold_applicant_date_until;
1539 out_rec.honors :=per_rec.honors;
1540 out_rec.internal_location :=per_rec.internal_location;
1541 out_rec.known_as :=per_rec.known_as;
1542 out_rec.last_medical_test_by :=per_rec.last_medical_test_by;
1543 out_rec.last_medical_test_date :=per_rec.last_medical_test_date;
1544 out_rec.mailstop :=per_rec.mailstop;
1545 out_rec.marital_status_meaning :=hr_reports.get_lookup_meaning('MAR_STATUS',per_rec.marital_status);
1546 out_rec.middle_names :=per_rec.middle_names;
1547 out_rec.nationality_meaning :=hr_reports.get_lookup_meaning('NATIONALITY',per_rec.nationality);
1548 out_rec.national_identifier :=per_rec.national_identifier;
1549 out_rec.office_number :=per_rec.office_number;
1550 out_rec.on_military_service_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.on_military_service);
1551 out_rec.pre_name_adjunct :=per_rec.pre_name_adjunct;
1552 out_rec.previous_last_name :=per_rec.previous_last_name;
1553 out_rec.rehire_recommendation :=per_rec.rehire_recommendation;
1554 out_rec.resume_exists_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.resume_exists);
1555 out_rec.resume_last_updated :=per_rec.resume_last_updated;
1556 -- Bug 3037019
1557 out_rec.registered_disabled_flag :=hr_reports.get_lookup_meaning('REGISTERED_DISABLED',per_rec.registered_disabled_flag);
1558 out_rec.secnd_passport_exsts_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.second_passport_exists);
1559 out_rec.sex_meaning :=hr_reports.get_lookup_meaning('SEX',per_rec.sex);
1560 out_rec.student_status_meaning :=hr_reports.get_lookup_meaning('STUDENT_STATUS',per_rec.student_status);
1561 out_rec.suffix :=per_rec.suffix;
1562 out_rec.title_meaning :=hr_reports.get_lookup_meaning('TITLE',per_rec.title);
1563 out_rec.work_schedule_meaning :=hr_reports.get_lookup_meaning('WORK_SCHEDULE',per_rec.work_schedule);
1564 out_rec.coord_ben_med_pln_no :=per_rec.coord_ben_med_pln_no;
1565 out_rec.cord_ben_no_cvg_flag_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.coord_ben_no_cvg_flag);
1566 out_rec.dpdnt_adoption_date :=per_rec.dpdnt_adoption_date;
1567 out_rec.dpdnt_vlntry_svc_flg_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.dpdnt_vlntry_svce_flag);
1568 out_rec.receipt_of_death_cert_date :=per_rec.receipt_of_death_cert_date;
1569 out_rec.uses_tobacco_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.uses_tobacco_flag);
1570 --
1571 if per_rec.benefit_group_id is not null then
1572 open csr_benfts_grp(per_rec.benefit_group_id);
1573 fetch csr_benfts_grp into out_rec.benefit_group;
1574 close csr_benfts_grp;
1575 else
1576 out_rec.benefit_group:=null;
1577 end if;
1578 --
1579 /* These fields are no longer used because they may change
1580 context on a row by row basis, making them meaningless in a table
1581
1582 out_rec.attribute_category :=per_rec.attribute_category;
1583 out_rec.attribute1 :=per_rec.attribute1;
1584 out_rec.attribute2 :=per_rec.attribute2;
1585 out_rec.attribute3 :=per_rec.attribute3;
1586 out_rec.attribute4 :=per_rec.attribute4;
1587 out_rec.attribute5 :=per_rec.attribute5;
1588 out_rec.attribute6 :=per_rec.attribute6;
1589 out_rec.attribute7 :=per_rec.attribute7;
1590 out_rec.attribute8 :=per_rec.attribute8;
1591 out_rec.attribute9 :=per_rec.attribute9;
1592 out_rec.attribute10 :=per_rec.attribute10;
1593 out_rec.attribute11 :=per_rec.attribute11;
1594 out_rec.attribute12 :=per_rec.attribute12;
1595 out_rec.attribute13 :=per_rec.attribute13;
1596 out_rec.attribute14 :=per_rec.attribute14;
1597 out_rec.attribute15 :=per_rec.attribute15;
1598 out_rec.attribute16 :=per_rec.attribute16;
1599 out_rec.attribute17 :=per_rec.attribute17;
1600 out_rec.attribute18 :=per_rec.attribute18;
1601 out_rec.attribute19 :=per_rec.attribute19;
1602 out_rec.attribute20 :=per_rec.attribute20;
1603 out_rec.attribute21 :=per_rec.attribute21;
1604 out_rec.attribute22 :=per_rec.attribute22;
1605 out_rec.attribute23 :=per_rec.attribute23;
1606 out_rec.attribute24 :=per_rec.attribute24;
1607 out_rec.attribute25 :=per_rec.attribute25;
1608 out_rec.attribute26 :=per_rec.attribute26;
1609 out_rec.attribute27 :=per_rec.attribute27;
1610 out_rec.attribute28 :=per_rec.attribute28;
1611 out_rec.attribute29 :=per_rec.attribute29;
1612 out_rec.attribute30 :=per_rec.attribute30;
1613 */
1614
1615 out_rec.per_information_category :=per_rec.per_information_category;
1616 out_rec.per_information1 :=per_rec.per_information1;
1617 out_rec.per_information2 :=per_rec.per_information2;
1618 out_rec.per_information3 :=per_rec.per_information3;
1619 out_rec.per_information4 :=per_rec.per_information4;
1620 out_rec.per_information5 :=per_rec.per_information5;
1621 out_rec.per_information6 :=per_rec.per_information6;
1622 out_rec.per_information7 :=per_rec.per_information7;
1623 out_rec.per_information8 :=per_rec.per_information8;
1624 out_rec.per_information9 :=per_rec.per_information9;
1625 out_rec.per_information10 :=per_rec.per_information10;
1626 out_rec.per_information11 :=per_rec.per_information11;
1627 out_rec.per_information12 :=per_rec.per_information12;
1628 out_rec.per_information13 :=per_rec.per_information13;
1629 out_rec.per_information14 :=per_rec.per_information14;
1630 out_rec.per_information15 :=per_rec.per_information15;
1631 out_rec.per_information16 :=per_rec.per_information16;
1632 out_rec.per_information17 :=per_rec.per_information17;
1633 out_rec.per_information18 :=per_rec.per_information18;
1634 out_rec.per_information19 :=per_rec.per_information19;
1635 out_rec.per_information20 :=per_rec.per_information20;
1636 out_rec.per_information21 :=per_rec.per_information21;
1637 out_rec.per_information22 :=per_rec.per_information22;
1638 out_rec.per_information23 :=per_rec.per_information23;
1639 out_rec.per_information24 :=per_rec.per_information24;
1640 out_rec.per_information25 :=per_rec.per_information25;
1641 out_rec.per_information26 :=per_rec.per_information26;
1642 out_rec.per_information27 :=per_rec.per_information27;
1643 out_rec.per_information28 :=per_rec.per_information28;
1644 out_rec.per_information29 :=per_rec.per_information29;
1645 out_rec.per_information30 :=per_rec.per_information30;
1646 out_rec.date_of_death :=per_rec.date_of_death;
1647 --
1648 if per_rec.current_employee_flag='Y' then
1649 open csr_pds(per_rec.person_id,per_rec.effective_start_date);
1650 fetch csr_pds into out_rec.hire_date;
1651 close csr_pds;
1652 else
1653 out_rec.hire_date:=null;
1654 end if;
1655 --
1656 if per_rec.current_applicant_flag='Y' then
1657 open csr_app(per_rec.person_id,per_rec.effective_start_date);
1658 fetch csr_app into out_rec.projected_hire_date;
1659 close csr_app;
1660 else
1661 out_rec.projected_hire_date:=null;
1662 end if;
1663 --
1664 if not l_asg then
1665 open count_asgs(per_rec.person_id);
1666 fetch count_asgs into l_num_asgs;
1667 close count_asgs;
1668 end if;
1669 --
1670 if(l_num_asgs=1) then
1671 execute immediate l_select_stmt_asg into l_assignment_id using per_rec.person_id;
1672 open csr_assignment_details(l_assignment_id,p_effective_date);
1673 fetch csr_assignment_details into asg_rec;
1674 close csr_assignment_details;
1675 --
1676 out_rec.assignment_id :=asg_rec.assignment_id;
1677 out_rec.asg_effective_start_date :=asg_rec.effective_start_date;
1678 out_rec.asg_effective_end_date :=asg_rec.effective_end_date;
1679 --
1680 if asg_rec.recruiter_id is not null then
1681 open csr_full_name(asg_rec.recruiter_id,p_effective_date);
1682 fetch csr_full_name into out_rec.recruiter;
1683 close csr_full_name;
1684 else
1685 out_rec.recruiter:=null;
1686 end if;
1687 --
1688 if asg_rec.grade_id is not null then
1689 open csr_grade(asg_rec.grade_id);
1690 fetch csr_grade into out_rec.grade;
1691 close csr_grade;
1692 else
1693 out_rec.grade:=null;
1694 end if;
1695 --
1696 if asg_rec.grade_ladder_pgm_id is not null then
1697 open csr_grade_ladder(asg_rec.grade_ladder_pgm_id,p_effective_date);
1698 fetch csr_grade_ladder into out_rec.grade_ladder;
1699 close csr_grade_ladder;
1700 else
1701 out_rec.grade_ladder:=null;
1702 end if;
1703 --
1704 if asg_rec.position_id is not null then
1705 open csr_position(asg_rec.position_id,p_effective_date);
1706 fetch csr_position into out_rec.position;
1707 close csr_position;
1708 else
1709 out_rec.position:=null;
1710 end if;
1711 --
1712 if asg_rec.job_id is not null then
1713 open csr_job(asg_rec.job_id);
1714 fetch csr_job into out_rec.job;
1715 close csr_job;
1716 else
1717 out_rec.job:=null;
1718 end if;
1719 --
1720 open csr_asg_status(asg_rec.assignment_status_type_id);
1721 fetch csr_asg_status into out_rec.assignment_status_type,out_rec.system_status;
1722 close csr_asg_status;
1723 --
1724 if asg_rec.payroll_id is not null then
1725 open csr_payroll(asg_rec.payroll_id,p_effective_date);
1726 fetch csr_payroll into out_rec.payroll;
1727 close csr_payroll;
1728 else
1729 out_rec.payroll:=null;
1730 end if;
1731 --
1732 if asg_rec.location_id is not null then
1733 open csr_location(asg_rec.location_id);
1734 fetch csr_location into out_rec.location;
1735 close csr_location;
1736 else
1737 out_rec.location:=null;
1738 end if;
1739 --
1740 if asg_rec.person_referred_by_id is not null then
1741 open csr_full_name(asg_rec.person_referred_by_id,p_effective_date);
1742 fetch csr_full_name into out_rec.person_referred_by;
1743 close csr_full_name;
1744 else
1745 out_rec.person_referred_by:=null;
1746 end if;
1747 --
1748 if asg_rec.supervisor_id is not null then
1749 open csr_full_name(asg_rec.supervisor_id,p_effective_date);
1750 fetch csr_full_name into out_rec.supervisor;
1751 close csr_full_name;
1752 else
1753 out_rec.supervisor:=null;
1754 end if;
1755 --
1756 if asg_rec.supervisor_assignment_id is not null then
1757
1758 hr_utility.set_location('super_assgt_id is not null',990);
1759 hr_utility.set_location('super assgt id is ' || to_char(asg_rec.supervisor_assignment_id),990);
1760 open csr_supervisor_assgt_number(asg_rec.supervisor_assignment_id,p_effective_date);
1761 fetch csr_supervisor_assgt_number into out_rec.supervisor_assignment_number;
1762 hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1763 close csr_supervisor_assgt_number;
1764 else
1765 out_rec.supervisor_assignment_number:=null;
1766 hr_utility.set_location('set super_assgt_id to null',992);
1767 end if;
1768 --
1769 if asg_rec.recruitment_activity_id is not null then
1770 open csr_rec_activity(asg_rec.recruitment_activity_id);
1771 fetch csr_rec_activity into out_rec.recruitment_activity;
1772 close csr_rec_activity;
1773 else
1774 out_rec.recruitment_activity:=null;
1775 end if;
1776 --
1777 if asg_rec.source_organization_id is not null then
1778 open csr_organization(asg_rec.source_organization_id);
1779 fetch csr_organization into out_rec.source_organization;
1780 close csr_organization;
1781 else
1782 out_rec.source_organization:=null;
1783 end if;
1784 --
1785 open csr_organization(asg_rec.organization_id);
1786 fetch csr_organization into out_rec.organization;
1787 close csr_organization;
1788 --
1789 if asg_rec.people_group_id is not null then
1790 open csr_pgp_rec(asg_rec.people_group_id);
1791 fetch csr_pgp_rec into pgp_rec;
1792 close csr_pgp_rec;
1793 --
1794 out_rec.pgp_segment1 :=pgp_rec.segment1;
1795 out_rec.pgp_segment2 :=pgp_rec.segment2;
1796 out_rec.pgp_segment3 :=pgp_rec.segment3;
1797 out_rec.pgp_segment4 :=pgp_rec.segment4;
1798 out_rec.pgp_segment5 :=pgp_rec.segment5;
1799 out_rec.pgp_segment6 :=pgp_rec.segment6;
1800 out_rec.pgp_segment7 :=pgp_rec.segment7;
1801 out_rec.pgp_segment8 :=pgp_rec.segment9;
1802 out_rec.pgp_segment9 :=pgp_rec.segment9;
1803 out_rec.pgp_segment10 :=pgp_rec.segment10;
1804 out_rec.pgp_segment11 :=pgp_rec.segment11;
1805 out_rec.pgp_segment12 :=pgp_rec.segment12;
1806 out_rec.pgp_segment13 :=pgp_rec.segment13;
1807 out_rec.pgp_segment14 :=pgp_rec.segment14;
1808 out_rec.pgp_segment15 :=pgp_rec.segment15;
1809 out_rec.pgp_segment16 :=pgp_rec.segment16;
1810 out_rec.pgp_segment17 :=pgp_rec.segment17;
1811 out_rec.pgp_segment18 :=pgp_rec.segment18;
1812 out_rec.pgp_segment19 :=pgp_rec.segment19;
1813 out_rec.pgp_segment20 :=pgp_rec.segment20;
1814 out_rec.pgp_segment21 :=pgp_rec.segment21;
1815 out_rec.pgp_segment22 :=pgp_rec.segment22;
1816 out_rec.pgp_segment23 :=pgp_rec.segment23;
1817 out_rec.pgp_segment24 :=pgp_rec.segment24;
1818 out_rec.pgp_segment25 :=pgp_rec.segment25;
1819 out_rec.pgp_segment26 :=pgp_rec.segment26;
1820 out_rec.pgp_segment27 :=pgp_rec.segment27;
1821 out_rec.pgp_segment28 :=pgp_rec.segment28;
1822 out_rec.pgp_segment29 :=pgp_rec.segment29;
1823 out_rec.pgp_segment30 :=pgp_rec.segment30;
1824 end if;
1825 --
1826 if asg_rec.soft_coding_keyflex_id is not null then
1827 open csr_scl_rec(asg_rec.soft_coding_keyflex_id);
1828 fetch csr_scl_rec into scl_rec;
1829 close csr_scl_rec;
1830
1831 out_rec.scl_segment1 :=scl_rec.segment1;
1832 out_rec.scl_segment2 :=scl_rec.segment2;
1833 out_rec.scl_segment3 :=scl_rec.segment3;
1834 out_rec.scl_segment4 :=scl_rec.segment4;
1835 out_rec.scl_segment5 :=scl_rec.segment5;
1836 out_rec.scl_segment6 :=scl_rec.segment6;
1837 out_rec.scl_segment7 :=scl_rec.segment7;
1838 out_rec.scl_segment8 :=scl_rec.segment8;
1839 out_rec.scl_segment9 :=scl_rec.segment9;
1840 out_rec.scl_segment10 :=scl_rec.segment10;
1841 out_rec.scl_segment11 :=scl_rec.segment11;
1842 out_rec.scl_segment12 :=scl_rec.segment12;
1843 out_rec.scl_segment13 :=scl_rec.segment13;
1844 out_rec.scl_segment14 :=scl_rec.segment14;
1845 out_rec.scl_segment15 :=scl_rec.segment15;
1846 out_rec.scl_segment16 :=scl_rec.segment16;
1847 out_rec.scl_segment17 :=scl_rec.segment17;
1848 out_rec.scl_segment18 :=scl_rec.segment18;
1849 out_rec.scl_segment19 :=scl_rec.segment19;
1850 out_rec.scl_segment20 :=scl_rec.segment20;
1851 out_rec.scl_segment21 :=scl_rec.segment21;
1852 out_rec.scl_segment22 :=scl_rec.segment22;
1853 out_rec.scl_segment23 :=scl_rec.segment23;
1854 out_rec.scl_segment24 :=scl_rec.segment24;
1855 out_rec.scl_segment25 :=scl_rec.segment25;
1856 out_rec.scl_segment26 :=scl_rec.segment26;
1857 out_rec.scl_segment27 :=scl_rec.segment27;
1858 out_rec.scl_segment28 :=scl_rec.segment28;
1859 out_rec.scl_segment29 :=scl_rec.segment29;
1860 out_rec.scl_segment30 :=scl_rec.segment30;
1861 end if;
1862 --
1863 if asg_rec.vacancy_id is not null then
1864 open csr_vacancy(asg_rec.vacancy_id);
1865 fetch csr_vacancy into out_rec.vacancy,out_rec.requisition;
1866 close csr_vacancy;
1867 else
1868 out_rec.vacancy:=null;
1869 out_rec.requisition:=null;
1870 end if;
1871 --
1872
1873 if asg_rec.pay_basis_id is not null then
1874 open csr_pay_basis(asg_rec.pay_basis_id);
1875 fetch csr_pay_basis into out_rec.salary_basis,out_rec.pay_basis;
1876 close csr_pay_basis;
1877 else
1878 out_rec.salary_basis:=null;
1879 out_rec.pay_basis:=null;
1880 end if;
1881 --
1882 out_rec.assignment_sequence :=asg_rec.assignment_sequence;
1883 out_rec.assignment_type :=asg_rec.assignment_type;
1884 out_rec.asg_primary_flag :=asg_rec.primary_flag;
1885 out_rec.assignment_number :=asg_rec.assignment_number;
1886 out_rec.date_probation_end :=asg_rec.date_probation_end;
1887 out_rec.default_code_comb_id :=asg_rec.default_code_comb_id;
1888 out_rec.employment_category_meaning :=hr_reports.get_lookup_meaning('EMP_CAT',asg_rec.employment_category);
1889 out_rec.frequency_meaning :=hr_reports.get_lookup_meaning('FREQUENCY',asg_rec.frequency);
1890 out_rec.normal_hours :=asg_rec.normal_hours;
1891 out_rec.probation_period :=asg_rec.probation_period;
1892 out_rec.probation_unit_meaning :=hr_reports.get_lookup_meaning('QUALIFYING_UNITS',asg_rec.probation_unit);
1893 out_rec.time_normal_finish :=asg_rec.time_normal_finish;
1894 out_rec.time_normal_start :=asg_rec.time_normal_start;
1895 --CWK
1896 out_rec.project_title :=asg_rec.project_title;
1897 out_rec.vendor_employee_number :=asg_rec.vendor_employee_number;
1898 out_rec.vendor_assignment_number :=asg_rec.vendor_assignment_number;
1899 --
1900 if asg_rec.vendor_id is not null then
1901 open csr_vendor(asg_rec.vendor_id);
1902 fetch csr_vendor into out_rec.vendor_name;
1903 close csr_vendor;
1904 else
1905 out_rec.vendor_name:=null;
1906 end if;
1907 --
1908 if asg_rec.vendor_site_id is not null then
1909 open csr_vendor_site(asg_rec.vendor_id);
1910 fetch csr_vendor_site into out_rec.vendor_site_code;
1911 close csr_vendor_site;
1912 else
1913 out_rec.vendor_site_code:=null;
1914 end if;
1915 --
1916 if asg_rec.po_header_id is not null then
1917 open csr_po_header(asg_rec.po_header_id);
1918 fetch csr_po_header into out_rec.po_header_num;
1919 close csr_po_header;
1920 else
1921 out_rec.po_header_num:=null;
1922 end if;
1923 --
1924 if asg_rec.po_line_id is not null then
1925 open csr_po_line(asg_rec.po_line_id);
1926 fetch csr_po_line into out_rec.po_line_num;
1927 close csr_po_line;
1928 else
1929 out_rec.po_line_num:=null;
1930 end if;
1931 --
1932 /* These fields are no longer used because they may change
1933 context on a row by row basis, making them meaningless in a table
1934
1935 out_rec.ass_attribute_category :=asg_rec.ass_attribute_category;
1936 out_rec.ass_attribute1 :=asg_rec.ass_attribute1;
1937 out_rec.ass_attribute2 :=asg_rec.ass_attribute2;
1938 out_rec.ass_attribute3 :=asg_rec.ass_attribute3;
1939 out_rec.ass_attribute4 :=asg_rec.ass_attribute4;
1940 out_rec.ass_attribute5 :=asg_rec.ass_attribute5;
1941 out_rec.ass_attribute6 :=asg_rec.ass_attribute6;
1942 out_rec.ass_attribute7 :=asg_rec.ass_attribute7;
1943 out_rec.ass_attribute8 :=asg_rec.ass_attribute8;
1944 out_rec.ass_attribute9 :=asg_rec.ass_attribute9;
1945 out_rec.ass_attribute10 :=asg_rec.ass_attribute10;
1946 out_rec.ass_attribute11 :=asg_rec.ass_attribute11;
1947 out_rec.ass_attribute12 :=asg_rec.ass_attribute12;
1948 out_rec.ass_attribute13 :=asg_rec.ass_attribute13;
1949 out_rec.ass_attribute14 :=asg_rec.ass_attribute14;
1950 out_rec.ass_attribute15 :=asg_rec.ass_attribute15;
1951 out_rec.ass_attribute16 :=asg_rec.ass_attribute16;
1952 out_rec.ass_attribute17 :=asg_rec.ass_attribute17;
1953 out_rec.ass_attribute18 :=asg_rec.ass_attribute18;
1954 out_rec.ass_attribute19 :=asg_rec.ass_attribute19;
1955 out_rec.ass_attribute20 :=asg_rec.ass_attribute20;
1956 out_rec.ass_attribute21 :=asg_rec.ass_attribute21;
1957 out_rec.ass_attribute22 :=asg_rec.ass_attribute22;
1958 out_rec.ass_attribute23 :=asg_rec.ass_attribute23;
1959 out_rec.ass_attribute24 :=asg_rec.ass_attribute24;
1960 out_rec.ass_attribute25 :=asg_rec.ass_attribute25;
1961 out_rec.ass_attribute26 :=asg_rec.ass_attribute26;
1962 out_rec.ass_attribute27 :=asg_rec.ass_attribute27;
1963 out_rec.ass_attribute28 :=asg_rec.ass_attribute28;
1964 out_rec.ass_attribute29 :=asg_rec.ass_attribute29;
1965 out_rec.ass_attribute30 :=asg_rec.ass_attribute30;
1966 */
1967
1968 out_rec.bargaining_unit_code_meaning :=hr_reports.get_lookup_meaning('BARGAINING_UNIT_CODE',asg_rec.bargaining_unit_code);
1969 out_rec.labour_union_member_flag :=asg_rec.labour_union_member_flag;
1970 out_rec.hourly_salaried_meaning :=hr_reports.get_lookup_meaning('HOURLY_SALARIED_CODE',asg_rec.hourly_salaried_code);
1971 --
1972 if asg_rec.special_ceiling_step_id is not null then
1973 open csr_ceiling_step(asg_rec.special_ceiling_step_id,p_effective_date);
1974 fetch csr_ceiling_step into out_rec.special_ceiling_point, out_rec.special_ceiling_step;
1975 close csr_ceiling_step;
1976 else
1977 out_rec.special_ceiling_point:=null;
1978 out_rec.special_ceiling_step:=null;
1979 end if;
1980 --
1981 out_rec.change_reason_meaning :=hr_reports.get_lookup_meaning('APL_ASSIGN_REASON',asg_rec.change_reason);
1982 out_rec.internal_address_line :=asg_rec.internal_address_line;
1983 out_rec.manager_flag :=asg_rec.manager_flag;
1984 out_rec.perf_review_period :=asg_rec.perf_review_period;
1985 out_rec.perf_rev_period_freq_meaning :=hr_reports.get_lookup_meaning('FREQUENCY',asg_rec.perf_review_period_frequency);
1986 out_rec.sal_review_period :=asg_rec.sal_review_period;
1987 out_rec.sal_rev_period_freq_meaning :=hr_reports.get_lookup_meaning('FREQUENCY',asg_rec.sal_review_period_frequency);
1988 out_rec.source_type_meaning :=hr_reports.get_lookup_meaning('REC_TYPE',asg_rec.source_type);
1989 --
1990 if asg_rec.contract_id is not null then
1991 open csr_reference(asg_rec.contract_id,p_effective_date);
1992 fetch csr_reference into out_rec.contract;
1993 close csr_reference;
1994 else
1995 out_rec.contract:=null;
1996 end if;
1997 --
1998 if asg_rec.collective_agreement_id is not null then
1999 open csr_collective_agr(asg_rec.collective_agreement_id);
2000 fetch csr_collective_agr into out_rec.collective_agreement;
2001 close csr_collective_agr;
2002 else
2003 out_rec.collective_agreement:=null;
2004 end if;
2005 --
2006 if asg_rec.cagr_id_flex_num is not null then
2007 open csr_cagr_flex_num(asg_rec.cagr_id_flex_num);
2008 fetch csr_cagr_flex_num into out_rec.cagr_id_flex_name;
2009 close csr_cagr_flex_num;
2010 else
2011 out_rec.cagr_id_flex_name:=null;
2012 end if;
2013 --
2014 if asg_rec.establishment_id is not null then
2015 open csr_organization(asg_rec.establishment_id);
2016 fetch csr_organization into out_rec.establishment;
2017 close csr_organization;
2018 else
2019 out_rec.establishment:=null;
2020 end if;
2021 else
2022 if l_num_asgs=0 then
2023 l_other:=null;
2024 else
2025 l_other:='****';
2026 end if;
2027 hr_utility.set_location(l_proc,100);
2028 out_rec.assignment_id :=null;
2029 out_rec.asg_effective_start_date :=null;
2030 out_rec.asg_effective_end_date :=null;
2031 out_rec.recruiter :=l_other;
2032 out_rec.grade :=l_other;
2033 out_rec.grade_ladder :=l_other;
2034 out_rec.position :=l_other;
2035 out_rec.job :=l_other;
2036 out_rec.assignment_status_type :=l_other;
2037 out_rec.system_status :=l_other;
2038 out_rec.payroll :=l_other;
2039 out_rec.location :=l_other;
2040 out_rec.person_referred_by :=l_other;
2041 out_rec.supervisor :=l_other;
2042 out_rec.supervisor_assignment_number :=l_other;
2043 out_rec.recruitment_activity :=l_other;
2044 out_rec.source_organization :=l_other;
2045 out_rec.organization :=l_other;
2046 out_rec.pgp_segment1 :=l_other;
2047 out_rec.pgp_segment1_v :=l_other;
2048 out_rec.pgp_segment1_m :=l_other;
2049 out_rec.pgp_segment2 :=l_other;
2050 out_rec.pgp_segment2_v :=l_other;
2051 out_rec.pgp_segment2_m :=l_other;
2052 out_rec.pgp_segment3 :=l_other;
2053 out_rec.pgp_segment3_v :=l_other;
2054 out_rec.pgp_segment3_m :=l_other;
2055 out_rec.pgp_segment4 :=l_other;
2056 out_rec.pgp_segment4_v :=l_other;
2057 out_rec.pgp_segment4_m :=l_other;
2058 out_rec.pgp_segment5 :=l_other;
2059 out_rec.pgp_segment5_v :=l_other;
2060 out_rec.pgp_segment5_m :=l_other;
2061 out_rec.pgp_segment6 :=l_other;
2062 out_rec.pgp_segment6_v :=l_other;
2063 out_rec.pgp_segment6_m :=l_other;
2064 out_rec.pgp_segment7 :=l_other;
2065 out_rec.pgp_segment7_v :=l_other;
2066 out_rec.pgp_segment7_m :=l_other;
2067 out_rec.pgp_segment8 :=l_other;
2068 out_rec.pgp_segment8_v :=l_other;
2069 out_rec.pgp_segment8_m :=l_other;
2070 out_rec.pgp_segment9 :=l_other;
2071 out_rec.pgp_segment9_v :=l_other;
2072 out_rec.pgp_segment9_m :=l_other;
2073 out_rec.pgp_segment10 :=l_other;
2074 out_rec.pgp_segment10_v :=l_other;
2075 out_rec.pgp_segment10_m :=l_other;
2076 out_rec.pgp_segment11 :=l_other;
2077 out_rec.pgp_segment11_v :=l_other;
2078 out_rec.pgp_segment11_m :=l_other;
2079 out_rec.pgp_segment12 :=l_other;
2080 out_rec.pgp_segment12_v :=l_other;
2081 out_rec.pgp_segment12_m :=l_other;
2082 out_rec.pgp_segment13 :=l_other;
2083 out_rec.pgp_segment13_v :=l_other;
2084 out_rec.pgp_segment13_m :=l_other;
2085 out_rec.pgp_segment14 :=l_other;
2086 out_rec.pgp_segment14_v :=l_other;
2087 out_rec.pgp_segment14_m :=l_other;
2088 out_rec.pgp_segment15 :=l_other;
2089 out_rec.pgp_segment15_v :=l_other;
2090 out_rec.pgp_segment15_m :=l_other;
2091 out_rec.pgp_segment16 :=l_other;
2092 out_rec.pgp_segment16_v :=l_other;
2093 out_rec.pgp_segment16_m :=l_other;
2094 out_rec.pgp_segment17 :=l_other;
2095 out_rec.pgp_segment17_v :=l_other;
2096 out_rec.pgp_segment17_m :=l_other;
2097 out_rec.pgp_segment18 :=l_other;
2098 out_rec.pgp_segment18_v :=l_other;
2099 out_rec.pgp_segment18_m :=l_other;
2100 out_rec.pgp_segment19 :=l_other;
2101 out_rec.pgp_segment19_v :=l_other;
2102 out_rec.pgp_segment19_m :=l_other;
2103 out_rec.pgp_segment20 :=l_other;
2104 out_rec.pgp_segment20_v :=l_other;
2105 out_rec.pgp_segment20_m :=l_other;
2106 out_rec.pgp_segment21 :=l_other;
2107 out_rec.pgp_segment21_v :=l_other;
2108 out_rec.pgp_segment21_m :=l_other;
2109 out_rec.pgp_segment22 :=l_other;
2110 out_rec.pgp_segment22_v :=l_other;
2111 out_rec.pgp_segment22_m :=l_other;
2112 out_rec.pgp_segment23 :=l_other;
2113 out_rec.pgp_segment23_v :=l_other;
2114 out_rec.pgp_segment23_m :=l_other;
2115 out_rec.pgp_segment24 :=l_other;
2116 out_rec.pgp_segment24_v :=l_other;
2117 out_rec.pgp_segment24_m :=l_other;
2118 out_rec.pgp_segment25 :=l_other;
2119 out_rec.pgp_segment25_v :=l_other;
2120 out_rec.pgp_segment25_m :=l_other;
2121 out_rec.pgp_segment26 :=l_other;
2122 out_rec.pgp_segment26_v :=l_other;
2123 out_rec.pgp_segment26_m :=l_other;
2124 out_rec.pgp_segment27 :=l_other;
2125 out_rec.pgp_segment27_v :=l_other;
2126 out_rec.pgp_segment27_m :=l_other;
2127 out_rec.pgp_segment28 :=l_other;
2128 out_rec.pgp_segment28_v :=l_other;
2129 out_rec.pgp_segment28_m :=l_other;
2130 out_rec.pgp_segment29 :=l_other;
2131 out_rec.pgp_segment29_v :=l_other;
2132 out_rec.pgp_segment29_m :=l_other;
2133 out_rec.pgp_segment30 :=l_other;
2134 out_rec.pgp_segment30_v :=l_other;
2135 out_rec.pgp_segment30_m :=l_other;
2136 hr_utility.set_location(l_proc,110);
2137 out_rec.people_group_id :=null;
2138 out_rec.scl_segment1 :=l_other;
2139 out_rec.scl_segment1_v :=l_other;
2140 out_rec.scl_segment1_m :=l_other;
2141 out_rec.scl_segment2 :=l_other;
2142 out_rec.scl_segment2_v :=l_other;
2143 out_rec.scl_segment2_m :=l_other;
2144 out_rec.scl_segment3 :=l_other;
2145 out_rec.scl_segment3_v :=l_other;
2146 out_rec.scl_segment3_m :=l_other;
2147 out_rec.scl_segment4 :=l_other;
2148 out_rec.scl_segment4_v :=l_other;
2149 out_rec.scl_segment4_m :=l_other;
2150 out_rec.scl_segment5 :=l_other;
2151 out_rec.scl_segment5_v :=l_other;
2152 out_rec.scl_segment5_m :=l_other;
2153 out_rec.scl_segment6 :=l_other;
2154 out_rec.scl_segment6_v :=l_other;
2155 out_rec.scl_segment6_m :=l_other;
2156 out_rec.scl_segment7 :=l_other;
2157 out_rec.scl_segment7_v :=l_other;
2158 out_rec.scl_segment7_m :=l_other;
2159 out_rec.scl_segment8 :=l_other;
2160 out_rec.scl_segment8_v :=l_other;
2161 out_rec.scl_segment8_m :=l_other;
2162 out_rec.scl_segment9 :=l_other;
2163 out_rec.scl_segment9_v :=l_other;
2164 out_rec.scl_segment9_m :=l_other;
2165 out_rec.scl_segment10 :=l_other;
2166 out_rec.scl_segment10_v :=l_other;
2167 out_rec.scl_segment10_m :=l_other;
2168 out_rec.scl_segment11 :=l_other;
2169 out_rec.scl_segment11_v :=l_other;
2170 out_rec.scl_segment11_m :=l_other;
2171 out_rec.scl_segment12 :=l_other;
2172 out_rec.scl_segment12_v :=l_other;
2173 out_rec.scl_segment12_m :=l_other;
2174 out_rec.scl_segment13 :=l_other;
2175 out_rec.scl_segment13_v :=l_other;
2176 out_rec.scl_segment13_m :=l_other;
2177 out_rec.scl_segment14 :=l_other;
2178 out_rec.scl_segment14_v :=l_other;
2179 out_rec.scl_segment14_m :=l_other;
2180 out_rec.scl_segment15 :=l_other;
2181 out_rec.scl_segment15_v :=l_other;
2182 out_rec.scl_segment15_m :=l_other;
2183 out_rec.scl_segment16 :=l_other;
2184 out_rec.scl_segment16_v :=l_other;
2185 out_rec.scl_segment16_m :=l_other;
2186 out_rec.scl_segment17 :=l_other;
2187 out_rec.scl_segment17_v :=l_other;
2188 out_rec.scl_segment17_m :=l_other;
2189 out_rec.scl_segment18 :=l_other;
2190 out_rec.scl_segment18_v :=l_other;
2191 out_rec.scl_segment18_m :=l_other;
2192 out_rec.scl_segment19 :=l_other;
2193 out_rec.scl_segment19_v :=l_other;
2194 out_rec.scl_segment19_m :=l_other;
2195 out_rec.scl_segment20 :=l_other;
2196 out_rec.scl_segment20_v :=l_other;
2197 out_rec.scl_segment20_m :=l_other;
2198 out_rec.scl_segment21 :=l_other;
2199 out_rec.scl_segment21_v :=l_other;
2200 out_rec.scl_segment21_m :=l_other;
2201 out_rec.scl_segment22 :=l_other;
2202 out_rec.scl_segment22_v :=l_other;
2203 out_rec.scl_segment22_m :=l_other;
2204 out_rec.scl_segment23 :=l_other;
2205 out_rec.scl_segment23_v :=l_other;
2206 out_rec.scl_segment23_m :=l_other;
2207 out_rec.scl_segment24 :=l_other;
2208 out_rec.scl_segment24_v :=l_other;
2209 out_rec.scl_segment24_m :=l_other;
2210 out_rec.scl_segment25 :=l_other;
2211 out_rec.scl_segment25_v :=l_other;
2212 out_rec.scl_segment25_m :=l_other;
2213 out_rec.scl_segment26 :=l_other;
2214 out_rec.scl_segment26_v :=l_other;
2215 out_rec.scl_segment26_m :=l_other;
2216 out_rec.scl_segment27 :=l_other;
2217 out_rec.scl_segment27_v :=l_other;
2218 out_rec.scl_segment27_m :=l_other;
2219 out_rec.scl_segment28 :=l_other;
2220 out_rec.scl_segment28_v :=l_other;
2221 out_rec.scl_segment28_m :=l_other;
2222 out_rec.scl_segment29 :=l_other;
2223 out_rec.scl_segment29_v :=l_other;
2224 out_rec.scl_segment29_m :=l_other;
2225 out_rec.scl_segment30 :=l_other;
2226 out_rec.scl_segment30_v :=l_other;
2227 out_rec.scl_segment30_m :=l_other;
2228 out_rec.soft_coding_keyflex_id :=null;
2229 hr_utility.set_location(l_proc,120);
2230 out_rec.vacancy :=l_other;
2231 out_rec.requisition :=l_other;
2232 out_rec.salary_basis :=l_other;
2233 out_rec.pay_basis :=l_other;
2234 out_rec.assignment_sequence :=null;
2235 out_rec.assignment_type :=null;
2236 out_rec.asg_primary_flag :=l_other;
2237 out_rec.assignment_number :=l_other;
2238 out_rec.date_probation_end :=null;
2239 out_rec.default_code_comb_id :=null;
2240 out_rec.employment_category_meaning :=l_other;
2241 out_rec.frequency_meaning :=l_other;
2242 out_rec.normal_hours :=null;
2243 out_rec.probation_period :=null;
2244 out_rec.probation_unit_meaning :=l_other;
2245 out_rec.time_normal_finish :=l_other;
2246 out_rec.time_normal_start :=l_other;
2247 --CWK
2248 out_rec.project_title :=l_other;
2249 out_rec.vendor_name :=l_other;
2250 out_rec.vendor_employee_number :=l_other;
2251 out_rec.vendor_assignment_number :=l_other;
2252 out_rec.vendor_site_code :=l_other;
2253 out_rec.po_header_num :=l_other;
2254 out_rec.po_line_num :=null;
2255 --
2256 /* These fields are no longer used because they may change
2257 context on a row by row basis, making them meaningless in a table
2258
2259 out_rec.ass_attribute_category :=l_other;
2260 out_rec.ass_attribute1 :=l_other;
2261 out_rec.ass_attribute1_v :=l_other;
2262 out_rec.ass_attribute1_m :=l_other;
2263 out_rec.ass_attribute2 :=l_other;
2264 out_rec.ass_attribute2_v :=l_other;
2265 out_rec.ass_attribute2_m :=l_other;
2266 out_rec.ass_attribute3 :=l_other;
2267 out_rec.ass_attribute3_v :=l_other;
2268 out_rec.ass_attribute3_m :=l_other;
2269 out_rec.ass_attribute4 :=l_other;
2270 out_rec.ass_attribute4_v :=l_other;
2271 out_rec.ass_attribute4_m :=l_other;
2272 out_rec.ass_attribute5 :=l_other;
2273 out_rec.ass_attribute5_v :=l_other;
2274 out_rec.ass_attribute5_m :=l_other;
2275 out_rec.ass_attribute6 :=l_other;
2276 out_rec.ass_attribute6_v :=l_other;
2277 out_rec.ass_attribute6_m :=l_other;
2278 out_rec.ass_attribute7 :=l_other;
2279 out_rec.ass_attribute7_v :=l_other;
2280 out_rec.ass_attribute7_m :=l_other;
2281 out_rec.ass_attribute8 :=l_other;
2282 out_rec.ass_attribute8_v :=l_other;
2283 out_rec.ass_attribute8_m :=l_other;
2284 out_rec.ass_attribute9 :=l_other;
2285 out_rec.ass_attribute9_v :=l_other;
2286 out_rec.ass_attribute9_m :=l_other;
2287 out_rec.ass_attribute10 :=l_other;
2288 out_rec.ass_attribute10_v :=l_other;
2289 out_rec.ass_attribute10_m :=l_other;
2290 out_rec.ass_attribute11 :=l_other;
2291 out_rec.ass_attribute11_v :=l_other;
2292 out_rec.ass_attribute11_m :=l_other;
2293 out_rec.ass_attribute12 :=l_other;
2294 out_rec.ass_attribute12_v :=l_other;
2295 out_rec.ass_attribute12_m :=l_other;
2296 out_rec.ass_attribute13 :=l_other;
2297 out_rec.ass_attribute13_v :=l_other;
2298 out_rec.ass_attribute13_m :=l_other;
2299 out_rec.ass_attribute14 :=l_other;
2300 out_rec.ass_attribute14_v :=l_other;
2301 out_rec.ass_attribute14_m :=l_other;
2302 out_rec.ass_attribute15 :=l_other;
2303 out_rec.ass_attribute15_v :=l_other;
2304 out_rec.ass_attribute15_m :=l_other;
2305 out_rec.ass_attribute16 :=l_other;
2306 out_rec.ass_attribute16_v :=l_other;
2307 out_rec.ass_attribute16_m :=l_other;
2308 out_rec.ass_attribute17 :=l_other;
2309 out_rec.ass_attribute17_v :=l_other;
2310 out_rec.ass_attribute17_m :=l_other;
2311 out_rec.ass_attribute18 :=l_other;
2312 out_rec.ass_attribute18_v :=l_other;
2313 out_rec.ass_attribute18_m :=l_other;
2314 out_rec.ass_attribute19 :=l_other;
2315 out_rec.ass_attribute19_v :=l_other;
2316 out_rec.ass_attribute19_m :=l_other;
2317 out_rec.ass_attribute20 :=l_other;
2318 out_rec.ass_attribute20_v :=l_other;
2319 out_rec.ass_attribute20_m :=l_other;
2320 out_rec.ass_attribute21 :=l_other;
2321 out_rec.ass_attribute21_v :=l_other;
2322 out_rec.ass_attribute21_m :=l_other;
2323 out_rec.ass_attribute22 :=l_other;
2324 out_rec.ass_attribute22_v :=l_other;
2325 out_rec.ass_attribute22_m :=l_other;
2326 out_rec.ass_attribute23 :=l_other;
2327 out_rec.ass_attribute23_v :=l_other;
2328 out_rec.ass_attribute23_m :=l_other;
2329 out_rec.ass_attribute24 :=l_other;
2330 out_rec.ass_attribute24_v :=l_other;
2331 out_rec.ass_attribute24_m :=l_other;
2332 out_rec.ass_attribute25 :=l_other;
2333 out_rec.ass_attribute25_v :=l_other;
2334 out_rec.ass_attribute25_m :=l_other;
2335 out_rec.ass_attribute26 :=l_other;
2336 out_rec.ass_attribute26_v :=l_other;
2337 out_rec.ass_attribute26_m :=l_other;
2338 out_rec.ass_attribute27 :=l_other;
2339 out_rec.ass_attribute27_v :=l_other;
2340 out_rec.ass_attribute27_m :=l_other;
2341 out_rec.ass_attribute28 :=l_other;
2342 out_rec.ass_attribute28_v :=l_other;
2343 out_rec.ass_attribute28_m :=l_other;
2344 out_rec.ass_attribute29 :=l_other;
2345 out_rec.ass_attribute29_v :=l_other;
2346 out_rec.ass_attribute29_m :=l_other;
2347 out_rec.ass_attribute30 :=l_other;
2348 out_rec.ass_attribute30_v :=l_other;
2349 out_rec.ass_attribute30_m :=l_other;
2350 */
2351 hr_utility.set_location(l_proc,130);
2352 out_rec.bargaining_unit_code_meaning :=l_other;
2353 out_rec.labour_union_member_flag :=l_other;
2354 out_rec.hourly_salaried_meaning :=l_other;
2355 out_rec.special_ceiling_step :=null;
2356 out_rec.special_ceiling_point :=l_other;
2357 out_rec.change_reason_meaning :=l_other;
2358 out_rec.internal_address_line :=l_other;
2359 out_rec.manager_flag :=l_other;
2360 out_rec.perf_review_period :=null;
2361 out_rec.perf_rev_period_freq_meaning :=l_other;
2362 out_rec.sal_review_period :=null;
2363 out_rec.sal_rev_period_freq_meaning :=l_other;
2364 out_rec.source_type_meaning :=l_other;
2365 out_rec.contract :=l_other;
2366 out_rec.collective_agreement :=l_other;
2367 out_rec.cagr_id_flex_name :=l_other;
2368 out_rec.cagr_grade :=null;
2369 out_rec.establishment :=l_other;
2370 hr_utility.set_location(l_proc,140);
2371 --
2372 end if;
2373 end if;
2374 resultset(num_row):=out_rec;
2375 end loop;
2376
2377 hr_utility.set_location('Leaving: '||l_proc||num_row,1000);
2378 close emp_cv;
2379 --
2380 end findquery;
2381 --
2382 --
2383 procedure insert_varchar2(p_query_id number
2384 ,p_field varchar2
2385 ,p_value varchar2
2386 ) is
2387 begin
2388 insert into per_query_criteria
2389 (query_id
2390 ,field
2391 ,field_type
2392 ,varchar2_value
2393 ,number_value
2394 ,date_value
2395 ,object_version_number)
2396 values
2397 (p_query_id
2398 ,p_field
2399 ,'V'
2400 ,replace(p_value,g_quote,g_quote||g_quote)
2401 ,null
2402 ,null
2403 ,1);
2404 end insert_varchar2;
2405 --
2406 procedure insert_number (p_query_id number
2407 ,p_field varchar2
2408 ,p_value number
2409 ) is
2410 begin
2411 insert into per_query_criteria
2412 (query_id
2413 ,field
2414 ,field_type
2415 ,varchar2_value
2416 ,number_value
2417 ,date_value
2418 ,object_version_number)
2419 values
2420 (p_query_id
2421 ,p_field
2422 ,'N'
2423 ,null
2424 ,p_value
2425 ,null
2426 ,1);
2427 end insert_number;
2428 --
2429 procedure insert_date (p_query_id number
2430 ,p_field varchar2
2431 ,p_value date
2432 ) is
2433 begin
2434 insert into per_query_criteria
2435 (query_id
2436 ,field
2437 ,field_type
2438 ,varchar2_value
2439 ,number_value
2440 ,date_value
2441 ,object_version_number)
2442 values
2443 (p_query_id
2444 ,p_field
2445 ,'D'
2446 ,null
2447 ,null
2448 ,p_value
2449 ,1);
2450 end insert_date;
2451 --
2452 procedure findsave(
2453 query_id in number
2454 ,business_group_id in per_all_people_f.business_group_id%type
2455 ,business_group_name in per_business_groups.name%type
2456 ,person_id in per_all_people_f.person_id%type default null
2457 ,person_type in per_person_types.user_person_type%type default null
2458 ,system_person_type in per_person_types.system_person_type%type default null
2459 ,person_type_id in per_all_people_f.person_type_id%type default null
2460 ,last_name in per_all_people_f.last_name%type default null
2461 ,start_date in per_all_people_f.start_date%type default null
2462 ,hire_date in per_periods_of_service.date_start%type default null
2463 ,applicant_number in per_all_people_f.applicant_number%type default null
2464 ,date_of_birth in per_all_people_f.date_of_birth%type default null
2465 ,email_address in per_all_people_f.email_address%type default null
2466 ,employee_number in per_all_people_f.employee_number%type default null
2467 --CWK
2468 ,npw_number in per_all_people_f.npw_number%type default null
2469 ,project_title in per_all_assignments_f.project_title%type default null
2470 ,vendor_id in per_all_assignments_f.vendor_id%type default null
2471 ,vendor_name in po_vendors.vendor_name%type default null
2472 ,vendor_employee_number in per_all_assignments_f.vendor_employee_number%type default null
2473 ,vendor_assignment_number in per_all_assignments_f.vendor_assignment_number%type default null
2474 ,vendor_site_code in po_vendor_sites_all.vendor_site_code%TYPE default null
2475 ,vendor_site_id in po_vendor_sites_all.vendor_site_id%TYPE default null
2476 ,po_header_num in po_headers_all.segment1%TYPE default null
2477 ,po_header_id in po_headers_all.po_header_id%TYPE default null
2478 ,po_line_num in po_lines_all.line_num%TYPE default null
2479 ,po_line_id in po_lines_all.po_line_id%TYPE default null
2480 --
2481 ,first_name in per_all_people_f.first_name%type default null
2482 ,full_name in per_all_people_f.full_name%type default null
2483 ,title in per_all_people_f.title%type
2484 ,middle_names in per_all_people_f.middle_names%type
2485 ,nationality_meaning in hr_lookups.meaning%type default null
2486 ,nationality in per_all_people_f.nationality%type default null
2487 ,national_identifier in per_all_people_f.national_identifier%type default null
2488 -- Bug 3037019 Start Here
2489 ,registered_disabled_flag in hr_lookups.meaning%type default null
2490 ,registered_disabled in per_all_people_f.registered_disabled_flag%type default null
2491 ,sex_meaning in hr_lookups.meaning%type default null
2492 ,sex in per_all_people_f.sex%type default null
2493 ,benefit_group in ben_benfts_grp.name%type default null
2494 ,benefit_group_id in per_all_people_f.benefit_group_id%type default null
2495 ,grade in per_grades.name%type default null
2496 ,grade_id in per_all_assignments_f.grade_id%type default null
2497 ,grade_ladder in ben_pgm_f.name%type default null
2498 ,grade_ladder_pgm_id in per_all_assignments_f.grade_ladder_pgm_id%type default null
2499 ,position in hr_all_positions_f.name%type default null
2500 ,position_id in per_all_assignments_f.position_id%type default null
2501 ,job in per_jobs.name%type default null
2502 ,job_id in per_all_assignments_f.job_id%type default null
2503 ,assignment_status_type in per_assignment_status_types.user_status%type default null
2504 ,assignment_status_type_id in per_all_assignments_f.assignment_status_type_id%type default null
2505 ,payroll in pay_all_payrolls_f.payroll_name%type default null
2506 ,payroll_id in per_all_assignments_f.payroll_id%type default null
2507 ,location in hr_locations.location_code%type default null
2508 ,location_id in per_all_assignments_f.location_id%type default null
2509 ,supervisor in per_all_people_f.full_name%type default null
2510 ,supervisor_id in per_all_assignments_f.supervisor_id%type default null
2511 ,supervisor_assignment_number in per_assignments_v.supervisor_assignment_number%type default null
2512 ,supervisor_assignment_id in per_all_assignments_f.supervisor_assignment_id%type default null
2513 ,recruitment_activity in per_recruitment_activities.name%type default null
2514 ,recruitment_activity_id in per_all_assignments_f.recruitment_activity_id%type default null
2515 ,organization in hr_all_organization_units.name%type default null
2516 ,organization_id in per_all_assignments_f.organization_id%type default null
2517 ,people_group in pay_people_groups.group_name%type default null
2518 ,people_group_id in per_all_assignments_f.people_group_id%type default null
2519 ,vacancy in per_vacancies.name%type default null
2520 ,vacancy_id in per_all_assignments_f.vacancy_id%type default null
2521 ,requisition in per_requisitions.name%type default null
2522 ,requisition_id in per_requisitions.requisition_id%type default null
2523 ,salary_basis in per_pay_bases.name%type default null
2524 ,pay_basis_id in per_all_assignments_f.pay_basis_id%type default null
2525 ,bargaining_unit_code_meaning in hr_lookups.meaning%type default null
2526 ,bargaining_unit_code in per_all_assignments_f.bargaining_unit_code%type default null
2527 ,employment_category_meaning in hr_lookups.meaning%type default null
2528 ,employment_category in per_all_assignments_f.employment_category%type default null
2529 --bug 3002915 starts here. modified the type.
2530 ,establishment in hr_leg_establishments_v.name%type default null
2531 ,establishment_id in hr_leg_establishments_v.organization_id%type default null
2532 --bug 3002915 ends here.
2533 ,projected_hire_date in per_applications.projected_hire_date%type default null
2534 ,secure in varchar2 default null
2535 ,field1_name in varchar2 default null
2536 ,field1_condition_code in varchar2 default null
2537 ,field1_value in varchar2 default null
2538 ,field2_name in varchar2 default null
2539 ,field2_condition_code in varchar2 default null
2540 ,field2_value in varchar2 default null
2541 ,field3_name in varchar2 default null
2542 ,field3_condition_code in varchar2 default null
2543 ,field3_value in varchar2 default null
2544 ,field4_name in varchar2 default null
2545 ,field4_condition_code in varchar2 default null
2546 ,field4_value in varchar2 default null
2547 ,field5_name in varchar2 default null
2548 ,field5_condition_code in varchar2 default null
2549 ,field5_value in varchar2 default null
2550 ) is
2551 pragma autonomous_transaction;
2552 --
2553 l_query_id number;
2554 l_proc varchar2(72):=g_package||'findsave';
2555 --
2556 --
2557 begin
2558 hr_utility.set_location('Entering '||l_proc,10);
2559 l_query_id:=query_id;
2560 --
2561 if business_group_id is not null then
2562 insert_number(l_query_id,'BUSINESS_GROUP_ID',business_group_id);
2563 end if;
2564 --Modified for PMxbg
2565 if business_group_name is not null then
2566 insert_varchar2(l_query_id,'BUSINESS_GROUP_NAME',business_group_name);
2567 end if;
2568 if person_id is not null then
2569 insert_number(l_query_id,'PERSON_ID',person_id);
2570 end if;
2571 if person_type is not null then
2572 insert_varchar2(l_query_id,'PERSON_TYPE',person_type);
2573 end if;
2574 if system_person_type is not null then
2575 insert_varchar2(l_query_id,'SYSTEM_PERSON_TYPE',system_person_type);
2576 end if;
2577 if person_type_id is not null then
2578 insert_number(l_query_id,'PERSON_TYPE_ID',person_type_id);
2579 end if;
2580 if last_name is not null then
2581 insert_varchar2(l_query_id,'LAST_NAME',last_name);
2582 end if;
2583 if start_date is not null then
2584 insert_date(l_query_id,'START_DATE',start_date);
2585 end if;
2586 if hire_date is not null then
2587 insert_date(l_query_id,'HIRE_DATE',hire_date);
2588 end if;
2589 if applicant_number is not null then
2590 insert_varchar2(l_query_id,'APPLICANT_NUMBER',applicant_number);
2591 end if;
2592 if date_of_birth is not null then
2593 insert_date(l_query_id,'DATE_OF_BIRTH',date_of_birth);
2594 end if;
2595 if email_address is not null then
2596 insert_varchar2(l_query_id,'EMAIL_ADDRESS',email_address);
2597 end if;
2598 if employee_number is not null then
2599 insert_varchar2(l_query_id,'EMPLOYEE_NUMBER',employee_number);
2600 end if;
2601 --CWK
2602 if npw_number is not null then
2603 insert_varchar2(l_query_id,'NPW_NUMBER',npw_number);
2604 end if;
2605 if project_title is not null then
2606 insert_varchar2(l_query_id,'PROJECT_TITLE',project_title);
2607 end if;
2608 if vendor_id is not null then
2609 insert_number(l_query_id,'VENDOR_ID',vendor_id);
2610 end if;
2611 if vendor_name is not null then
2612 insert_varchar2(l_query_id,'VENDOR_NAME',vendor_name);
2613 end if;
2614 if vendor_employee_number is not null then
2615 insert_varchar2(l_query_id,'VENDOR_EMPLOYEE_NUMBER',vendor_employee_number);
2616 end if;
2617 if vendor_assignment_number is not null then
2618 insert_varchar2(l_query_id,'VENDOR_ASSIGNMENT_NUMBER',vendor_assignment_number);
2619 end if;
2620 if vendor_site_id is not null then
2621 insert_number(l_query_id,'VENDOR_SITE_ID',vendor_site_id);
2622 end if;
2623 if vendor_site_code is not null then
2624 insert_varchar2(l_query_id,'VENDOR_SITE_CODE',vendor_site_code);
2625 end if;
2626 if po_header_id is not null then
2627 insert_number(l_query_id,'PO_HEADER_ID',po_header_id);
2628 end if;
2629 if po_header_num is not null then
2630 insert_varchar2(l_query_id,'PO_HEADER_NUM',po_header_num);
2631 end if;
2632 if po_line_id is not null then
2633 insert_number(l_query_id,'PO_LINE_ID',po_line_id);
2634 end if;
2635 if po_line_num is not null then
2636 insert_varchar2(l_query_id,'PO_LINE_NUM',po_line_num);
2637 end if;
2638 --
2639 if first_name is not null then
2640 insert_varchar2(l_query_id,'FIRST_NAME',first_name);
2641 end if;
2642 if full_name is not null then
2643 insert_varchar2(l_query_id,'FULL_NAME',full_name);
2644 end if;
2645 if title is not null then
2646 insert_varchar2(l_query_id,'TITLE',title);
2647 end if;
2648 if middle_names is not null then
2649 insert_varchar2(l_query_id,'MIDDLE_NAMES',middle_names);
2650 end if;
2651 if nationality_meaning is not null then
2652 insert_varchar2(l_query_id,'NATIONALITY_MEANING',nationality_meaning);
2653 end if;
2654 if nationality is not null then
2655 insert_varchar2(l_query_id,'NATIONALITY',nationality);
2656 end if;
2657 if national_identifier is not null then
2658 insert_varchar2(l_query_id,'NATIONAL_IDENTIFIER',national_identifier);
2659 end if;
2660 -- Bug 3037019 Start here
2661 if registered_disabled_flag is not null then
2662 insert_varchar2(l_query_id,'REGISTERED_DISABLED_FLAG',registered_disabled_flag);
2663 end if;
2664 -- Bug 3037019 End Here
2665 if registered_disabled is not null then
2666 insert_varchar2(l_query_id,'REGISTERED_DISABLED',registered_disabled);
2667 end if;
2668 if sex_meaning is not null then
2669 insert_varchar2(l_query_id,'SEX_MEANING',sex_meaning);
2670 end if;
2671 if sex is not null then
2672 insert_varchar2(l_query_id,'SEX',sex);
2673 end if;
2674 if benefit_group is not null then
2675 insert_varchar2(l_query_id,'BENEFIT_GROUP',benefit_group);
2676 end if;
2677 if benefit_group_id is not null then
2678 insert_number(l_query_id,'BENEFIT_GROUP_ID',benefit_group_id);
2679 end if;
2680 if grade is not null then
2681 insert_varchar2(l_query_id,'GRADE',grade);
2682 end if;
2683 if grade_id is not null then
2684 insert_number(l_query_id,'GRADE_ID',grade_id);
2685 end if;
2686 if grade_ladder is not null then
2687 insert_varchar2(l_query_id,'GRADE_LADDER',grade_ladder);
2688 end if;
2689 if grade_ladder_pgm_id is not null then
2690 insert_number(l_query_id,'GRADE_LADDER_PGM_ID',grade_ladder_pgm_id);
2691 end if;
2692 if position is not null then
2693 insert_varchar2(l_query_id,'POSITION',position);
2694 end if;
2695 if position_id is not null then
2696 insert_number(l_query_id,'POSITION_ID',position_id);
2697 end if;
2698 if job is not null then
2699 insert_varchar2(l_query_id,'JOB',job);
2700 end if;
2701 if job_id is not null then
2702 insert_number(l_query_id,'JOB_ID',job_id);
2703 end if;
2704 if assignment_status_type is not null then
2705 insert_varchar2(l_query_id,'ASSIGNMENT_STATUS_TYPE',assignment_status_type);
2706 end if;
2707 if assignment_status_type_id is not null then
2708 insert_number(l_query_id,'ASSIGNMENT_STATUS_TYPE_ID',assignment_status_type_id);
2709 end if;
2710 if payroll is not null then
2711 insert_varchar2(l_query_id,'PAYROLL',payroll);
2712 end if;
2713 if payroll_id is not null then
2714 insert_number(l_query_id,'PAYROLL_ID',payroll_id);
2715 end if;
2716 if location is not null then
2717 insert_varchar2(l_query_id,'LOCATION',location);
2718 end if;
2719 if location_id is not null then
2720 insert_number(l_query_id,'LOCATION_ID',location_id);
2721 end if;
2722 if supervisor is not null then
2723 insert_varchar2(l_query_id,'SUPERVISOR',supervisor);
2724 end if;
2725 if supervisor_id is not null then
2726 insert_number(l_query_id,'SUPERVISOR_ID',supervisor_id);
2727 end if;
2728 if supervisor_assignment_number is not null then
2729 insert_varchar2(l_query_id,'SUPERVISOR_ASSIGNMENT_NUMBER',supervisor_assignment_number);
2730 end if;
2731 if supervisor_assignment_id is not null then
2732 insert_number(l_query_id,'SUPERVISOR_ASSIGNMENT_ID',supervisor_assignment_id);
2733 end if;
2734 if recruitment_activity is not null then
2735 insert_varchar2(l_query_id,'RECRUITMENT_ACTIVITY',recruitment_activity);
2736 end if;
2737 if recruitment_activity_id is not null then
2738 insert_number(l_query_id,'RECRUITMENT_ACTIVITY_ID',recruitment_activity_id);
2739 end if;
2740 if organization is not null then
2741 insert_varchar2(l_query_id,'ORGANIZATION',organization);
2742 end if;
2743 if organization_id is not null then
2744 insert_number(l_query_id,'ORGANIZATION_ID',organization_id);
2745 end if;
2746 if people_group is not null then
2747 insert_varchar2(l_query_id,'PEOPLE_GROUP',people_group);
2748 end if;
2749 if people_group_id is not null then
2750 insert_number(l_query_id,'PEOPLE_GROUP_ID',people_group_id);
2751 end if;
2752 if vacancy is not null then
2753 insert_varchar2(l_query_id,'VACANCY',vacancy);
2754 end if;
2755 if vacancy_id is not null then
2756 insert_number(l_query_id,'VACANCY_ID',vacancy_id);
2757 end if;
2758 if requisition is not null then
2759 insert_varchar2(l_query_id,'REQUISITION',requisition);
2760 end if;
2761 if requisition_id is not null then
2762 insert_number(l_query_id,'REQUISITION_ID',requisition_id);
2763 end if;
2764 if salary_basis is not null then
2765 insert_varchar2(l_query_id,'SALARY_BASIS',salary_basis);
2766 end if;
2767 if pay_basis_id is not null then
2768 insert_number(l_query_id,'PAY_BASIS_ID',pay_basis_id);
2769 end if;
2770 if bargaining_unit_code_meaning is not null then
2771 insert_varchar2(l_query_id,'BARGAINING_UNIT_CODE_MEANING',bargaining_unit_code_meaning);
2772 end if;
2773 if bargaining_unit_code is not null then
2774 insert_varchar2(l_query_id,'BARGAINING_UNIT_CODE',bargaining_unit_code);
2775 end if;
2776 if employment_category_meaning is not null then
2777 insert_varchar2(l_query_id,'EMPLOYMENT_CATEGORY_MEANING',employment_category_meaning);
2778 end if;
2779 if employment_category is not null then
2780 insert_varchar2(l_query_id,'EMPLOYMENT_CATEGORY',employment_category);
2781 end if;
2782 if establishment is not null then
2783 insert_varchar2(l_query_id,'ESTABLISHMENT',establishment);
2784 end if;
2785 if establishment_id is not null then
2786 insert_number(l_query_id,'ESTABLISHMENT_ID',establishment_id);
2787 end if;
2788 if projected_hire_date is not null then
2789 insert_date(l_query_id,'PROJECTED_HIRE_DATE',projected_hire_date);
2790 end if;
2791 if secure is not null then
2792 insert_varchar2(l_query_id,'SECURE',secure);
2793 end if;
2794 if field1_name is not null then
2795 insert_varchar2(l_query_id,'FIELD1_NAME',field1_name);
2796 end if;
2797 if field1_condition_code is not null then
2798 insert_varchar2(l_query_id,'FIELD1_CONDITION_CODE',field1_condition_code);
2799 end if;
2800 if field1_value is not null then
2801 insert_varchar2(l_query_id,'FIELD1_VALUE',field1_value);
2802 end if;
2803 if field2_name is not null then
2804 insert_varchar2(l_query_id,'FIELD2_NAME',field2_name);
2805 end if;
2806 if field2_condition_code is not null then
2807 insert_varchar2(l_query_id,'FIELD2_CONDITION_CODE',field2_condition_code);
2808 end if;
2809 if field2_value is not null then
2810 insert_varchar2(l_query_id,'FIELD2_VALUE',field2_value);
2811 end if;
2812 if field3_name is not null then
2813 insert_varchar2(l_query_id,'FIELD3_NAME',field3_name);
2814 end if;
2815 if field3_condition_code is not null then
2816 insert_varchar2(l_query_id,'FIELD3_CONDITION_CODE',field3_condition_code);
2817 end if;
2818 if field3_value is not null then
2819 insert_varchar2(l_query_id,'FIELD3_VALUE',field3_value);
2820 end if;
2821 if field4_name is not null then
2822 insert_varchar2(l_query_id,'FIELD4_NAME',field4_name);
2823 end if;
2824 if field4_condition_code is not null then
2825 insert_varchar2(l_query_id,'FIELD4_CONDITION_CODE',field4_condition_code);
2826 end if;
2827 if field4_value is not null then
2828 insert_varchar2(l_query_id,'FIELD4_VALUE',field4_value);
2829 end if;
2830 if field5_name is not null then
2831 insert_varchar2(l_query_id,'FIELD5_NAME',field5_name);
2832 end if;
2833 if field5_condition_code is not null then
2834 insert_varchar2(l_query_id,'FIELD5_CONDITION_CODE',field5_condition_code);
2835 end if;
2836 if field5_value is not null then
2837 insert_varchar2(l_query_id,'FIELD5_VALUE',field5_value);
2838 end if;
2839 commit;
2840 hr_utility.set_location('Leaving '||l_proc,100);
2841 end findsave;
2842 --
2843 function get_varchar2(p_query_id number
2844 ,p_field varchar2)
2845 return varchar2 is
2846 l_value varchar2(240);
2847 --
2848 cursor get_varchar2 is
2849 select varchar2_value
2850 from per_query_criteria
2851 where query_id=p_query_id
2852 and field=p_field;
2853 begin
2854 open get_varchar2;
2855 fetch get_varchar2 into l_value;
2856 close get_varchar2;
2857 return l_value;
2858 end get_varchar2;
2859 --
2860 function get_number (p_query_id number
2861 ,p_field varchar2)
2862 return number is
2863 l_value number;
2864 --
2865 cursor get_number is
2866 select number_value
2867 from per_query_criteria
2868 where query_id=p_query_id
2869 and field=p_field;
2870 begin
2871 open get_number;
2872 fetch get_number into l_value;
2873 close get_number;
2874 return l_value;
2875 end get_number;
2876 --
2877 function get_date (p_query_id number
2878 ,p_field varchar2)
2879 return date is
2880 l_value date;
2881 --
2882 cursor get_date is
2883 select date_value
2884 from per_query_criteria
2885 where query_id=p_query_id
2886 and field=p_field;
2887 begin
2888 open get_date;
2889 fetch get_date into l_value;
2890 close get_date;
2891 return l_value;
2892 end get_date;
2893
2894 procedure findretrieve
2895 (p_query_id in number
2896 ,p_effective_date in date
2897 ,p_customized_restriction_id in number default null
2898 ,p_employees_allowed in boolean default false
2899 ,p_applicants_allowed in boolean default false
2900 ,p_cwk_allowed in boolean default false
2901 ,p_people_tab out nocopy findtab
2902 ) is
2903 --
2904 l_findtab findtab;
2905 i number;
2906 l_select_stmt_per VARCHAR2(20000);
2907 begin
2908 --
2909 findquery(resultset => l_findtab
2910 ,p_effective_date => p_effective_date
2911 ,business_group_id => get_number(p_query_id,'BUSINESS_GROUP_ID')
2912 ,business_group_name => get_varchar2(p_query_id,'BUSINESS_GROUP_NAME')
2913 ,person_id => get_number(p_query_id,'PERSON_ID')
2914 ,person_type => get_varchar2(p_query_id,'PERSON_TYPE')
2915 ,system_person_type => get_varchar2(p_query_id,'SYSTEM_PERSON_TYPE')
2916 ,person_type_id => get_number(p_query_id,'PERSON_TYPE_ID')
2917 ,last_name => get_varchar2(p_query_id,'LAST_NAME')
2918 ,start_date => get_date(p_query_id,'START_DATE')
2919 ,hire_date => get_date(p_query_id,'HIRE_DATE')
2920 ,applicant_number => get_varchar2(p_query_id,'APPLICANT_NUMBER')
2921 ,date_of_birth => get_date(p_query_id,'DATE_OF_BIRTH')
2922 ,email_address => get_varchar2(p_query_id,'EMAIL_ADDRESS')
2923 ,employee_number => get_varchar2(p_query_id,'EMPLOYEE_NUMBER')
2924 --CWK
2925 ,npw_number => get_varchar2(p_query_id,'')
2926 ,project_title => get_varchar2(p_query_id,'PROJECT_TITLE')
2927 ,vendor_id => get_number(p_query_id,'VENDOR_ID')
2928 ,vendor_name => get_varchar2(p_query_id,'VENDOR_NAME')
2929 ,vendor_employee_number => get_varchar2(p_query_id,'VENDOR_EMPLOYEE_NUMBER')
2930 ,vendor_assignment_number => get_varchar2(p_query_id,'VENDOR_ASSIGNMENT_NUMBER')
2931 ,vendor_site_id => get_number(p_query_id,'VENDOR_SITE_ID')
2932 ,vendor_site_code => get_varchar2(p_query_id,'VENDOR_SITE_CODE')
2933 ,po_header_id => get_number(p_query_id,'PO_HEADER_ID')
2934 ,po_header_num => get_varchar2(p_query_id,'PO_HEADER_NUM')
2935 ,po_line_id => get_number(p_query_id,'PO_LINE_ID')
2936 ,po_line_num => get_varchar2(p_query_id,'PO_LINE_NUM')
2937 --
2938 ,first_name => get_varchar2(p_query_id,'FIRST_NAME')
2939 ,full_name => get_varchar2(p_query_id,'FULL_NAME')
2940 ,title => get_varchar2(p_query_id,'TITLE')
2941 ,middle_names => get_varchar2(p_query_id,'MIDDLE_NAMES')
2942 ,nationality_meaning => get_varchar2(p_query_id,'NATIONALITY_MEANING')
2943 ,nationality => get_varchar2(p_query_id,'NATIONALITY')
2944 ,national_identifier => get_varchar2(p_query_id,'NATIONAL_IDENTIFIER')
2945 -- Bug 3037019
2946 ,registered_disabled_flag => get_varchar2(p_query_id,'REGISTERED_DISABLED_FLAG')
2947 ,registered_disabled => get_varchar2(p_query_id,'REGISTERED_DISABLED')
2948 ,sex_meaning => get_varchar2(p_query_id,'SEX_MEANING')
2949 ,sex => get_varchar2(p_query_id,'SEX')
2950 ,benefit_group => get_varchar2(p_query_id,'BENEFIT_GROUP')
2951 ,benefit_group_id => get_number(p_query_id,'BENEFIT_GROUP_ID')
2952 ,grade => get_varchar2(p_query_id,'GRADE')
2953 ,grade_id => get_number(p_query_id,'GRADE_ID')
2954 ,grade_ladder => get_varchar2(p_query_id,'GRADE_LADDER')
2955 ,grade_ladder_pgm_id => get_number(p_query_id,'GRADE_LADDER_PGM_ID')
2956 ,position => get_varchar2(p_query_id,'POSITION')
2957 ,position_id => get_number(p_query_id,'POSITION_ID')
2958 ,job => get_varchar2(p_query_id,'JOB')
2959 ,job_id => get_number(p_query_id,'JOB_ID')
2960 ,assignment_status_type => get_varchar2(p_query_id,'ASSIGNMENT_STATUS_TYPE')
2961 ,assignment_status_type_id => get_number(p_query_id,'ASSIGNMENT_STATUS_TYPE_ID')
2962 ,payroll => get_varchar2(p_query_id,'PAYROLL')
2963 ,payroll_id => get_number(p_query_id,'PAYROLL_ID')
2964 ,location => get_varchar2(p_query_id,'LOCATION')
2965 ,location_id => get_number(p_query_id,'LOCATION_ID')
2966 ,supervisor => get_varchar2(p_query_id,'SUPERVISOR')
2967 ,supervisor_id => get_number(p_query_id,'SUPERVISOR_ID')
2968 ,supervisor_assignment_number => get_varchar2(p_query_id,'SUPERVISOR_ASSIGNMENT_NUMBER')
2969 ,supervisor_assignment_id => get_number(p_query_id,'SUPERVISOR_ASSIGNMENT_ID')
2970 ,recruitment_activity => get_varchar2(p_query_id,'RECRUITMENT_ACTIVITY')
2971 ,recruitment_activity_id => get_number(p_query_id,'RECRUITMENT_ACTIVITY_ID')
2972 ,organization => get_varchar2(p_query_id,'ORGANIZATION')
2973 ,organization_id => get_number(p_query_id,'ORGANIZATION_ID')
2974 ,people_group => get_varchar2(p_query_id,'PEOPLE_GROUP')
2975 ,people_group_id => get_number(p_query_id,'PEOPLE_GROUP_ID')
2976 ,vacancy => get_varchar2(p_query_id,'VACANCY')
2977 ,vacancy_id => get_number(p_query_id,'VACANCY_ID')
2978 ,requisition => get_varchar2(p_query_id,'REQUISITION')
2979 ,requisition_id => get_number(p_query_id,'REQUISITION_ID')
2980 ,salary_basis => get_varchar2(p_query_id,'SALARY_BASIS')
2981 ,pay_basis_id => get_number(p_query_id,'PAY_BASIS_ID')
2982 ,bargaining_unit_code_meaning => get_varchar2(p_query_id,'BARGAINING_UNIT_CODE_MEANING')
2983 ,bargaining_unit_code => get_varchar2(p_query_id,'BARGAINING_UNIT_CODE')
2984 ,employment_category_meaning => get_varchar2(p_query_id,'EMPLOYMENT_CATEGORY_MEANING')
2985 ,employment_category => get_varchar2(p_query_id,'EMPLOYMENT_CATEGORY')
2986 ,establishment => get_varchar2(p_query_id,'ESTABLISHMENT')
2987 ,establishment_id => get_number(p_query_id,'ESTABLISHMENT_ID')
2988 ,projected_hire_date => get_date(p_query_id,'PROJECTED_HIRE_DATE')
2989 ,secure =>'Y'
2990 ,field1_name => get_varchar2(p_query_id,'FIELD1_NAME')
2991 ,field1_condition_code => get_varchar2(p_query_id,'FIELD1_CONDITION_CODE')
2992 ,field1_value => get_varchar2(p_query_id,'FIELD1_VALUE')
2993 ,field2_name => get_varchar2(p_query_id,'FIELD2_NAME')
2994 ,field2_condition_code => get_varchar2(p_query_id,'FIELD2_CONDITION_CODE')
2995 ,field2_value => get_varchar2(p_query_id,'FIELD2_VALUE')
2996 ,field3_name => get_varchar2(p_query_id,'FIELD3_NAME')
2997 ,field3_condition_code => get_varchar2(p_query_id,'FIELD3_CONDITION_CODE')
2998 ,field3_value => get_varchar2(p_query_id,'FIELD3_VALUE')
2999 ,field4_name => get_varchar2(p_query_id,'FIELD4_NAME')
3000 ,field4_condition_code => get_varchar2(p_query_id,'FIELD4_CONDITION_CODE')
3001 ,field4_value => get_varchar2(p_query_id,'FIELD4_VALUE')
3002 ,field5_name => get_varchar2(p_query_id,'FIELD5_NAME')
3003 ,field5_condition_code => get_varchar2(p_query_id,'FIELD5_CONDITION_CODE')
3004 ,field5_value => get_varchar2(p_query_id,'FIELD5_VALUE')
3005 ,p_customized_restriction_id => p_customized_restriction_id
3006 ,p_employees_allowed => p_employees_allowed
3007 ,p_applicants_allowed => p_applicants_allowed
3008 ,p_cwk_allowed => p_cwk_allowed
3009 ,p_fetch_details => FALSE
3010 ,select_stmt => l_select_stmt_per);
3011
3012 p_people_tab:=l_findtab;
3013
3014 end findretrieve;
3015
3016
3017 end per_qh_find_query;