[Home] [Help]
PACKAGE BODY: APPS.PER_QH_FIND_QUERY
Source
1 PACKAGE BODY per_qh_find_query as
2 /* $Header: peqhfndq.pkb 120.1 2005/12/29 05:36:38 rvarshne noship $ */
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 ||business_group_name||''' is null)';
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 end if;
848 if system_person_type is not null then
849 l_where_clause:=l_where_clause
850 ||' AND per.person_id IN'
851 || ' (SELECT ptu.person_id'
852 || ' FROM per_person_type_usages_f ptu'
853 || ' ,per_person_types ppt'
854 || ' WHERE ptu.person_type_id = ppt.person_type_id'
855 || ' AND ppt.system_person_type ='''||system_person_type||''''
856 || ' AND '||l_effective_date_clause
857 || ' BETWEEN ptu.effective_start_date'
858 || ' AND ptu.effective_end_date)';
859 end if;
860 end if;
861 --
862 --Modified for PMxbg
863
864 if last_name is not null then
865 l_where_clause:=l_where_clause||' and upper(per.last_name) like ('''||upper(replace(last_name,g_quote,g_quote||g_quote))||''')';
866 end if;
867 --
868 if start_date is not null then
869 l_where_clause:=l_where_clause||' and per.start_date=to_date('''||to_char(start_date,'DD/MM/YYYY')
870 ||''',''DD/MM/YYYY'')';
871 end if;
872 --
873 if hire_date is not null then
874 l_from_clause:=l_from_clause||',per_periods_of_service pds';
875 l_where_clause:=l_where_clause||' and pds.person_id=per.person_id'
876 ||' and pds.date_start=to_date('''||to_char(hire_date,'DD/MM/YYYY')
877 ||''',''DD/MM/YYYY'')';
878 end if;
879 --
880 if applicant_number is not null then
881 l_where_clause:=l_where_clause||' and upper(per.applicant_number) like ('''||upper(replace(applicant_number,g_quote,g_quote||g_quote))||''')';
882 end if;
883 --
884 if date_of_birth is not null then
885 l_where_clause:=l_where_clause||' and per.date_of_birth=to_date('''||to_char(date_of_birth,'DD/MM/YYYY')
886 ||''',''DD/MM/YYYY'')';
887 end if;
888 --
889 if email_address is not null then
890 l_where_clause:=l_where_clause||' and upper(per.email_address) like ('''||upper(replace(email_address,g_quote,g_quote||g_quote))||''')';
891 end if;
892 --
893 if employee_number is not null then
894 l_where_clause:=l_where_clause||' and upper(per.employee_number) like ('''||upper(replace(employee_number,g_quote,g_quote||g_quote))||''')';
895 end if;
896 --
897 if npw_number is not null then
898 l_where_clause:=l_where_clause||' and upper(per.npw_number) like ('''||upper(replace(npw_number,g_quote,g_quote||g_quote))||''')';
899 end if;
900 --
901 if first_name is not null then
902 l_where_clause:=l_where_clause||' and upper(per.first_name) like ('''||upper(replace(first_name,g_quote,g_quote||g_quote))||''')';
903 end if;
904 --
905 if full_name is not null then
906 l_where_clause:=l_where_clause||' and upper(per.full_name) like ('''||upper(replace(full_name,g_quote,g_quote||g_quote))||''')';
907 end if;
908 --
909 if title is not null then
910 l_where_clause:=l_where_clause||' and per.title = '''||replace(title,g_quote,g_quote||g_quote)||'''';
911 end if;
912 --
913 if middle_names is not null then
914 l_where_clause:=l_where_clause||' and upper(per.middle_names) like ('''||upper(replace(middle_names,g_quote,g_quote||g_quote))||''')';
915 end if;
916 --
917 if nationality is not null then
918 l_where_clause:=l_where_clause||' and per.nationality='''||nationality||'''';
919 elsif nationality_meaning is not null then
920 l_from_clause:=l_from_clause||',hr_lookups hlnat';
921 l_where_clause:=l_where_clause||' and per.nationality=hlnat.lookup_code'
922 ||' and hlnat.lookup_type=''NATIONALITY'''
923 ||' and upper(hlnat.meaning) like ('''||upper(replace(nationality_meaning,g_quote,g_quote||g_quote))||''')';
924 end if;
925 --
926 if national_identifier is not null then
927 l_where_clause:=l_where_clause||' and upper(per.national_identifier) like ('''||upper(replace(national_identifier,g_quote,g_quote||g_quote))||''')';
928 end if;
929 --
930 if registered_disabled is not null then
931 l_where_clause:=l_where_clause||' and per.registered_disabled_flag='''||registered_disabled||'''';
932
933 -- Bug 3037019 Start Here
934 elsif registered_disabled_flag is not null then
935 l_from_clause:=l_from_clause||',hr_lookups hlnat';
936 l_where_clause:=l_where_clause||' and per.registered_disabled_flag=hlnat.lookup_code'
937 ||' and hlnat.lookup_type=''REGISTERED_DISABLED'''
938 ||' and upper(hlnat.meaning) like ('''||upper(replace(registered_disabled_flag,g_quote,g_quote||g_quote))||''')';
939 -- Bug 3037019 Ends Here
940
941 end if;
942 --
943 if sex is not null then
944 l_where_clause:=l_where_clause||' and per.sex='''||sex||'''';
945 elsif sex_meaning is not null then
946 l_from_clause:=l_from_clause||',hr_lookups hlsex';
947 l_where_clause:=l_where_clause||' and per.sex=hlsex.lookup_code'
948 ||' and hlsex.lookup_type=''SEX'''
949 ||' and upper(hlsex.meaning) like ('''||upper(replace(sex_meaning,g_quote,g_quote||g_quote))||''')';
950 end if;
951 --
952 if benefit_group_id is not null then
953 l_where_clause:=l_where_clause||' and per.benefit_group_id='||benefit_group_id;
954 elsif benefit_group is not null then
955 l_from_clause:=l_from_clause||',ben_benfts_grp per_ben';
956 l_where_clause:=l_where_clause||' and per.benefit_group_id=per_ben.benefit_group_id'
957 ||' and upper(per_ben.name) like ('''||upper(replace(benefit_group,g_quote,g_quote||g_quote))||''')';
958 end if;
959 --
960 --CWK
961 --
962 if project_title is not null then
963 l_where_clause:=l_where_clause||' and per_asg.project_title='''||replace(project_title,g_quote,g_quote||g_quote)||'''';
964 l_asg:=TRUE;
965 end if;
966 --
967 if vendor_employee_number is not null then
968 l_where_clause:=l_where_clause||' and per_asg.vendor_employee_number='''||replace(vendor_employee_number,g_quote,g_quote||g_quote)||'''';
969 l_asg:=TRUE;
970 end if;
971 --
972 if vendor_assignment_number is not null then
973 l_where_clause:=l_where_clause||' and per_asg.vendor_assignment_number='''||replace(vendor_assignment_number,g_quote,g_quote||g_quote)||'''';
974 l_asg:=TRUE;
975 end if;
976 --
977 if vendor_id is not null then
978 l_where_clause:=l_where_clause||' and per_asg.vendor_id='||vendor_id;
979 l_asg:=TRUE;
980 elsif vendor_name is not null then
981 l_from_clause:=l_from_clause||',po_vendors pov';
982 l_where_clause:=l_where_clause||' and per_asg.vendor_id=pov.vendor_id'
983 ||' and upper(pov.vendor_name) like ('''||upper(replace(vendor_name,g_quote,g_quote||g_quote))||''')';
984 l_asg:=TRUE;
985 end if;
986 --
987 if vendor_site_id is not null then
988 l_where_clause:=l_where_clause||' and per_asg.vendor_site_id='||vendor_site_id;
989 l_asg:=TRUE;
990 elsif vendor_site_code is not null then
991 l_from_clause:=l_from_clause||',po_vendor_sites povs';
992 l_where_clause:=l_where_clause||' and per_asg.vendor_site_id=povs.vendor_site_id'
993 ||' and upper(povs.vendor_site_code) like ('''||upper(replace(vendor_site_code,g_quote,g_quote||g_quote))||''')';
994 l_asg:=TRUE;
995 end if;
996 --
997 if po_header_id is not null then
998 l_where_clause:=l_where_clause||' and per_asg.po_header_id='||po_header_id;
999 l_asg:=TRUE;
1000 elsif po_header_num is not null then
1001 l_from_clause:=l_from_clause||',po_headers_all poh';
1002 l_where_clause:=l_where_clause||' and per_asg.po_header_id=poh.po_header_id'
1003 ||' and upper(poh.segment1) like ('''||upper(replace(po_header_num,g_quote,g_quote||g_quote))||''')';
1004 l_asg:=TRUE;
1005 end if;
1006 --
1007 if po_line_id is not null then
1008 l_where_clause:=l_where_clause||' and per_asg.po_line_id='||po_line_id;
1009 l_asg:=TRUE;
1010 elsif po_line_num is not null then
1011 l_from_clause:=l_from_clause||',po_lines_all pol';
1012 l_where_clause:=l_where_clause||' and per_asg.po_line_id=pol.po_line_id'
1013 ||' and upper(pol.line_num) like ('''||upper(replace(po_line_num,g_quote,g_quote||g_quote))||''')';
1014 l_asg:=TRUE;
1015 end if;
1016 --Modified for PMxbg
1017 if grade_id is not null then
1018 l_where_clause:=l_where_clause||' and per_asg.grade_id='||grade_id;
1019 l_asg:=TRUE;
1020 elsif grade is not null then
1021 l_from_clause:=l_from_clause||',per_grades_vl grd';
1022 l_where_clause:=l_where_clause||' and per_asg.grade_id=grd.grade_id'
1023 ||' and upper(grd.name) like ('''||upper(replace(grade,g_quote,g_quote||g_quote))||''')'
1024 ||' and grd.business_group_id=per.business_group_id';
1025 l_asg:=TRUE;
1026 end if;
1027 --Modified for PMxbg
1028
1029 if grade_ladder_pgm_id is not null then
1030 l_where_clause:=l_where_clause||' and per_asg.grade_ladder_pgm_id='||grade_ladder_pgm_id;
1031 l_asg:=TRUE;
1032 elsif grade_ladder is not null then
1033 l_from_clause:=l_from_clause||',ben_pgm_f pgm';
1034 l_where_clause:=l_where_clause||' and per_asg.grade_ladder_pgm_id=pgm.pgm_id'
1035 ||' and upper(pgm.name) like ('''||upper(replace(grade_ladder,g_quote,g_quote||g_quote))||''')'
1036 --Modified for PMxbg
1037 ||' and gpm.business_group_id+0=nvl('||business_group_id||',gpm.business_group_id)'
1038 ||' and '||l_effective_date||' between pgm.effective_start_date'
1039 ||' and pgm.effective_end_date';
1040 l_asg:=TRUE;
1041 end if;
1042 --Modified for PMxbg
1043 if position_id is not null then
1044 l_where_clause:=l_where_clause||' and per_asg.position_id='||position_id;
1045 l_asg:=TRUE;
1046 elsif position is not null then
1047 -- PMFLETCH - Now using VL translation table
1048 l_from_clause:=l_from_clause||',hr_all_positions_f_vl per_pos'; -- Bug 3891920
1049 l_where_clause:=l_where_clause||' and per_asg.position_id=per_pos.position_id'
1050 ||' and upper(per_pos.name) like ('''||upper(replace(position,g_quote,g_quote||g_quote))||''')'
1051 ||' and per_pos.business_group_id=per.business_group_id';
1052 l_asg:=TRUE;
1053 end if;
1054 --Modified for PMxbg
1055
1056 if job_id is not null then
1057 l_where_clause:=l_where_clause||' and per_asg.job_id='||job_id;
1058 l_asg:=TRUE;
1059 elsif job is not null then
1060 l_from_clause:=l_from_clause||',per_jobs_v job';
1061 l_where_clause:=l_where_clause||' and per_asg.job_id=job.job_id'
1062 ||' and upper(job.name) like ('''||upper(replace(job,g_quote,g_quote||g_quote))||''')'
1063 ||' and job.business_group_id = per.business_group_id';
1064 l_asg:=TRUE;
1065 end if;
1066 --Modified for PMxbg
1067
1068 if assignment_status_type_id is not null then
1069 l_where_clause:=l_where_clause||' and per_asg.assignment_status_type_id='||assignment_status_type_id;
1070 l_asg:=TRUE;
1071 elsif assignment_status_type is not null then
1072 l_from_clause:=l_from_clause||',per_assignment_status_types s,per_assignment_status_types_tl stl';
1073 l_from_clause:=l_from_clause||',per_ass_status_type_amends a,per_ass_status_type_amends_tl atl';
1074 l_where_clause:=l_where_clause||' and upper(nvl(atl.user_status,stl.user_status))'
1075 ||' like ('''||upper(replace(assignment_status_type,g_quote,g_quote||g_quote))||''')'
1076 ||' and s.assignment_status_type_id=per_asg.assignment_status_type_id'
1077 ||' and a.assignment_status_type_id (+)=s.assignment_status_type_id'
1078 ||' and s.assignment_status_type_id =stl.assignment_status_type_id'
1079 ||' and a.ass_status_type_amend_id=atl.ass_status_type_amend_id(+)'
1080 ||' and a.business_group_id (+)= s.business_group_id'
1081 ||' and nvl(a.active_flag, s.active_flag)=''Y'''
1082 ||' and decode(atl.language,null,''1'',atl.language)=decode(atl.language,null,''1'',userenv(''LANG''))'
1083 ||' and stl.language=userenv(''LANG'')';
1084 l_asg:=TRUE;
1085 end if;
1086 --Modified for PMxbg
1087
1088 if payroll_id is not null then
1089 l_where_clause:=l_where_clause||' and per_asg.payroll_id='||payroll_id;
1090 l_asg:=TRUE;
1091 elsif payroll is not null then
1092 l_from_clause:=l_from_clause||',pay_all_payrolls_f pay';
1093 l_where_clause:=l_where_clause||' and per_asg.payroll_id=pay.payroll_id'
1094 ||' and upper(pay.payroll_name) like ('''||upper(replace(payroll,g_quote,g_quote||g_quote))||''')'
1095 ||' and pay.business_group_id = per.business_group_id'
1096 ||' and '||l_effective_date||' between pay.effective_start_date'
1097 ||' and pay.effective_end_date';
1098 l_asg:=TRUE;
1099 end if;
1100 --
1101 if location_id is not null then
1102 l_where_clause:=l_where_clause||' and per_asg.location_id='||location_id;
1103 l_asg:=TRUE;
1104 elsif location is not null then
1105 l_from_clause:=l_from_clause||',hr_locations loc';
1106 l_where_clause:=l_where_clause||' and per_asg.location_id=loc.location_id'
1107 ||' and upper(loc.location_code) like ('''||upper(replace(location,g_quote,g_quote||g_quote))||''')';
1108 l_asg:=TRUE;
1109 end if;
1110 --Modified for PMxbg
1111 if supervisor_id is not null then
1112 l_where_clause:=l_where_clause||' and per_asg.supervisor_id='||supervisor_id;
1113 l_asg:=TRUE;
1114 elsif supervisor is not null then
1115 l_from_clause:=l_from_clause||',per_all_people_f sup';
1116 l_where_clause:=l_where_clause||' and per_asg.supervisor_id=sup.person_id'
1117 ||' and upper(sup.full_name) like ('''||upper(replace(supervisor,g_quote,g_quote||g_quote))||''')'
1118 ||' and '||l_effective_date||' between sup.effective_start_date and sup.effective_end_date'
1119 ||' and sup.business_group_id=per.business_group_id';
1120 l_asg:=TRUE;
1121 end if;
1122 --Modified for PMxbg
1123 if supervisor_assignment_id is not null then
1124 l_where_clause:=l_where_clause||' and per_asg.supervisor_assignment_id='||supervisor_assignment_id;
1125 l_asg:=TRUE;
1126 elsif supervisor_assignment_number is not null then
1127 l_from_clause:=l_from_clause||',per_all_assignments_f supan';
1128 l_where_clause:=l_where_clause||' and per_asg.supervisor_assignment_id=supan.assignment_id'
1129 ||' and upper(supan.assignment_number)
1130 like ('''||upper(replace(supervisor_assignment_number,g_quote,g_quote||g_quote))||''')'
1131 ||' and '||l_effective_date||' between supan.effective_start_date and supan.effective_end_date'
1132 ||' and supan.business_group_id = per.business_group_id';
1133 l_asg:=TRUE;
1134 end if;
1135 --
1136 if recruitment_activity_id is not null then
1137 l_where_clause:=l_where_clause||' and per_asg.recruitment_activity_id='||recruitment_activity_id;
1138 l_asg:=TRUE;
1139 elsif recruitment_activity is not null then
1140 l_from_clause:=l_from_clause||',per_recruitment_activities ract';
1141 l_where_clause:=l_where_clause||' and per_asg.recruitment_activity_id = ract.recruitment_activity_id'
1142 ||' and upper(ract.name) like ('''||upper(replace(recruitment_activity,g_quote,g_quote||g_quote))||''')';
1143 l_asg:=TRUE;
1144 end if;
1145 --Modified for PMxbg
1146
1147 if organization_id is not null then
1148 l_where_clause:=l_where_clause||' and per_asg.organization_id='||organization_id;
1149 l_asg:=TRUE;
1150 elsif organization is not null then
1151 l_from_clause:=l_from_clause||',hr_all_organization_units_tl houtl,hr_all_organization_units hou';
1152 l_where_clause:=l_where_clause||' and per_asg.organization_id=hou.organization_id'
1153 ||' and hou.organization_id=houtl.organization_id'
1154 ||' and upper(houtl.name) like ('''||upper(replace(organization,g_quote,g_quote||g_quote))||''')'
1155 ||' and hou.internal_external_flag=''INT'''
1156 ||' and houtl.language=userenv(''LANG'')'
1157 ||' and hou.business_group_id=per.business_group_id';
1158 l_asg:=TRUE;
1159 end if;
1160 --
1161 if people_group_id is not null then
1162 l_where_clause:=l_where_clause||' and per_asg.people_group_id='||people_group_id;
1163 l_asg:=TRUE;
1164 elsif people_group is not null then
1165 l_from_clause:=l_from_clause||',pay_people_groups ppg';
1166 l_where_clause:=l_where_clause||' and per_asg.people_group_id=ppg.people_group_id'
1167 ||' and upper(ppg.group_name) like ('''||upper(replace(people_group,g_quote,g_quote||g_quote))||''')';
1168 l_asg:=TRUE;
1169 end if;
1170 --Modified for PMxbg
1171 if vacancy_id is not null then
1172 l_where_clause:=l_where_clause||' and per_asg.vacancy_id='||vacancy_id;
1173 l_asg:=TRUE;
1174 elsif vacancy is not null then
1175 l_from_clause:=l_from_clause||',per_all_vacancies vac';
1176 l_where_clause:=l_where_clause||' and per_asg.vacancy_id=vac.vacancy_id'
1177 ||' and upper(vac.name) like ('''||upper(replace(vacancy,g_quote,g_quote||g_quote))||''')'
1178 ||' and vac.business_group_id = per.business_group_id';
1179 l_asg:=TRUE;
1180 end if;
1181 --Modified for PMxbg
1182 if requisition_id is not null then
1183 if vacancy_id is null then
1184 if vacancy is null then
1185 l_from_clause:=l_from_clause||',per_all_vacancies vac';
1186 l_where_clause:=l_where_clause||' and per_asg.vacancy_id=vac.vacancy_id'
1187 ||' and vac.business_group_id = per.business_group_id';
1188 end if;
1189 l_where_clause:=l_where_clause||' and vac.requisition_id='||requisition_id;
1190 else
1191 l_from_clause:=l_from_clause||',per_all_vacancies vac';
1192 l_where_clause:=l_where_clause||' and vac.requisition_id='||requisition_id
1193 ||' and vac.vacancy_id=per_asg.vacancy_id';
1194 end if;
1195 l_asg:=TRUE;
1196 elsif requisition is not null then
1197 if vacancy_id is null then
1198 if vacancy is null then
1199 l_from_clause:=l_from_clause||',per_all_vacancies vac';
1200 l_where_clause:=l_where_clause||' and per_asg.vacancy_id=vac.vacancy_id'
1201 ||' and vac.business_group_id = per.business_group_id';
1202 end if;
1203 l_from_clause:=l_from_clause||',per_requisitions rec';
1204 l_where_clause:=l_where_clause||' and vac.requisition_id=rec.requisition_id'
1205 ||' and upper(rec.name) like ('''||upper(replace(requisition,g_quote,g_quote||g_quote))||''')';
1206 else
1207 l_from_clause:=l_from_clause||',per_all_vacancies vac, per_requisitions rec';
1208 l_where_clause:=l_where_clause||' and vac.requisition_id,rec.requisition_id'
1209 ||' and vac.requisition_id=rec.requisition_id'
1210 ||' and vac.vacancy_id=per_asg.vacancy_id'
1211 ||' and upper(rec.name) like ('''||upper(replace(requisition,g_quote,g_quote||g_quote))||''')';
1212 end if;
1213 l_asg:=TRUE;
1214 end if;
1215 -- Modified for PMxbg
1216 if pay_basis_id is not null then
1217 l_where_clause:=l_where_clause||' and per_asg.pay_basis_id='||pay_basis_id;
1218 l_asg:=TRUE;
1219 elsif salary_basis is not null then
1220 l_from_clause:=l_from_clause||',per_pay_bases ppb';
1221 l_where_clause:=l_where_clause||' and per_asg.pay_basis_id=ppb.pay_basis_id'
1222 ||' and upper(ppb.name) like ('''||upper(replace(salary_basis,g_quote,g_quote||g_quote))||''')'
1223 ||' and ppb.business_group_id = per.business_group_id';
1224 l_asg:=TRUE;
1225 end if;
1226 --
1227 if bargaining_unit_code is not null then
1228 l_where_clause:=l_where_clause||' and per_asg.bargaining_unit_code='''||bargaining_unit_code||'''';
1229 l_asg:=TRUE;
1230 elsif bargaining_unit_code_meaning is not null then
1231 l_from_clause:=l_from_clause||',hr_lookups buc';
1232 l_where_clause:=l_where_clause||' and per_asg.bargaining_unit_code=buc.lookup_code'
1233 ||' and buc.lookup_type=''BARGAINING_UNIT_CODE'''
1234 ||' and upper(buc.meaning) like ('''||upper(replace(bargaining_unit_code_meaning,g_quote,g_quote||g_quote))||''')';
1235 l_asg:=TRUE;
1236 end if;
1237 --
1238 if employment_category is not null then
1239 l_where_clause:=l_where_clause||' and per_asg.employment_category='''||employment_category||'''';
1240 l_asg:=TRUE;
1241 elsif employment_category_meaning is not null then
1242 l_from_clause:=l_from_clause||',hr_lookups empc';
1243 l_where_clause:=l_where_clause||' and per_asg.employment_category=empc.lookup_code
1244 and empc.lookup_type=''EMP_CAT''
1245 and upper(empc.meaning) like ('''||upper(replace(employment_category_meaning,g_quote,g_quote||g_quote))||''')';
1246 l_asg:=TRUE;
1247 end if;
1248 --
1249 if establishment_id is not null then
1250 --bug 3002915 starts here.
1251 --added new code replacing old.
1252 --
1253 --l_where_clause:=l_where_clause||' and exists'
1254 --||' (select 1'
1255 --||' from per_establishment_attendances eta'
1256 --||' where eta.establishment_id='||establishment_id
1257 --||' and eta.person_id=per.person_id)';
1258 --
1259 l_where_clause:=l_where_clause||' and per_asg.establishment_id ='||establishment_id;
1260 l_asg:=TRUE;
1261 -- bug 3002915 ends here.
1262 elsif establishment is not null then
1263 l_where_clause:=l_where_clause||' and exists'
1264 ||' (select 1'
1265 ||' from hr_leg_establishments_v hle'
1266 ||' where hle.organization_id = per_asg.establishment_id and upper(hle.name) like('''||upper(establishment)||'''))';
1267
1268 l_asg:=TRUE;
1269 --bug 3002915.
1270 -- ||' from per_establishment_attendances eta'
1271 -- ||',per_establishments est'
1272 -- ||' where eta.establishment_id=est.establishment_id'
1273 -- ||' and eta.person_id=per.person_id'
1274 -- ||' and upper(est.name) like('''||upper(replace(establishment,g_quote,g_quote||g_quote))||'''))';
1275 end if;
1276 --
1277 if projected_hire_date is not null then
1278 l_from_clause:=l_from_clause||',per_applications appl';
1279 l_where_clause:=l_where_clause||' and appl.projected_hire_date=to_date('''||
1280 to_char(projected_hire_date,'DD/MM/YYYY')||''',''DD/MM/YYYY'')'
1281 ||' and per.person_id=appl.person_id';
1282 end if;
1283 --
1284 --removed for bug 2632619, replaced by conditional change of l_from_clause
1285 -- if secure='Y' then
1286 -- l_where_clause:=l_where_clause||' and (hr_security.view_all=''Y'''
1287 -- ||' or hr_security.show_record(''PER_ALL_PEOPLE_F'''
1288 -- ||',per.person_id,per.person_type_id,per.employee_number,per.applicant_number)=''TRUE'')';
1289 -- end if;
1290 --
1291 if field1_name is not null and field1_condition_code is not null then
1292 l_where_clause:=l_where_clause||advanced_where
1293 (p_field_name=> field1_name
1294 ,p_condition => field1_condition_code
1295 ,p_value => replace(field1_value,g_quote,g_quote||g_quote));
1296 end if;
1297 --
1298 if field2_name is not null and field2_condition_code is not null then
1299 l_where_clause:=l_where_clause||advanced_where
1300 (p_field_name=> field2_name
1301 ,p_condition => field2_condition_code
1302 ,p_value => replace(field2_value,g_quote,g_quote||g_quote));
1303 end if;
1304 --
1305 if field3_name is not null and field3_condition_code is not null then
1306 l_where_clause:=l_where_clause||advanced_where
1307 (p_field_name=> field3_name
1308 ,p_condition => field3_condition_code
1309 ,p_value => replace(field3_value,g_quote,g_quote||g_quote));
1310 end if;
1311 --
1312 if field4_name is not null and field4_condition_code is not null then
1313 l_where_clause:=l_where_clause||advanced_where
1314 (p_field_name=> field4_name
1315 ,p_condition => field4_condition_code
1316 ,p_value => replace(field4_value,g_quote,g_quote||g_quote));
1317 end if;
1318 --
1319 if field5_name is not null and field5_condition_code is not null then
1320 l_where_clause:=l_where_clause||advanced_where
1321 (p_field_name=> field5_name
1322 ,p_condition => field5_condition_code
1323 ,p_value => replace(field5_value,g_quote,g_quote||g_quote));
1324 end if;
1325 --
1326 if l_asg then
1327 --
1328 -- Bug 4282150
1329 -- Added if condition to secure on each individual assignment
1330 --checkpoint for PMP
1331
1332 if secure='Y' then
1333 l_from_clause:=l_from_clause||',per_assignments_f2 per_asg';
1334 else
1335 l_from_clause:=l_from_clause||',per_all_assignments_f per_asg';
1336 end if;
1337 --
1338 l_where_clause:=l_where_clause||' and per.person_id=per_asg.person_id and '
1339 ||l_effective_date||
1340 ' between per_asg.effective_start_date and per_asg.effective_end_date '||
1341 ' AND per_asg.assignment_type <> ''B'''; -- Bug 3816589
1342 end if;
1343
1344
1345
1346 l_select_stmt_per:=
1347 'select per.person_id,nvl(per.order_name,per.full_name),count(*) '||l_from_clause||l_where_clause;
1348 if p_customized_restriction_id is not null then
1349 l_select_stmt_per:=l_select_stmt_per
1350 ||' AND (( EXISTS'
1351 || ' (SELECT 1'
1352 || ' FROM pay_restriction_values prv'
1353 || ' WHERE prv.customized_restriction_id = '
1354 || p_customized_restriction_id
1355 || ' AND prv.value IN'
1356 || ' (SELECT ptu.person_type_id'
1357 || ' FROM per_person_type_usages ptu'
1358 || ' WHERE ptu.person_id = per.person_id'
1359 || ' AND '||l_effective_date_clause
1360 || ' BETWEEN ptu.effective_start_date'
1361 || ' AND ptu.effective_end_date)'
1362 || ' AND NVL(prv.include_exclude_flag, ''I'') = ''I'' '
1363 || ' AND prv.restriction_code = ''PERSON_TYPE'')'
1364 || ' OR NOT EXISTS'
1365 || ' (SELECT 1'
1366 || ' FROM pay_restriction_values prv'
1367 || ' WHERE prv.customized_restriction_id ='
1368 || p_customized_restriction_id
1369 || ' AND NVL(prv.include_exclude_flag, ''I'') = ''I'' '
1370 || ' AND prv.restriction_code = ''PERSON_TYPE'') )'
1371 ||' AND NOT EXISTS '
1372 || ' (SELECT 1'
1373 || ' FROM pay_restriction_values prv2'
1374 || ' WHERE prv2.customized_restriction_id = '
1375 || p_customized_restriction_id
1376 || ' AND prv2.value IN'
1377 || ' (SELECT ptu2.person_type_id'
1378 || ' FROM per_person_type_usages ptu2'
1379 || ' WHERE ptu2.person_id = per.person_id'
1380 || ' AND '||l_effective_date_clause
1381 || ' BETWEEN ptu2.effective_start_date'
1382 || ' AND ptu2.effective_end_date)'
1383 || ' AND NVL(prv2.include_exclude_flag, ''I'') = ''E'' '
1384 || ' AND prv2.restriction_code = ''PERSON_TYPE''))';
1385 -- Removed the additional brace for fix of #3430507
1386 end if;
1387
1388 -- if not p_employees_allowed and not p_applicants_allowed then
1389 -- l_select_stmt_per:=l_select_stmt_per||
1390 -- ' and per.current_emp_or_apl_flag is null';
1391 -- end if;
1392 -- if p_employees_allowed and not p_applicants_allowed then
1393 -- l_select_stmt_per:=l_select_stmt_per||
1394 -- ' and (per.current_employee_flag =''Y''
1395 -- or per.current_emp_or_apl_flag is null)';
1396 -- end if;
1397 -- if not p_employees_allowed and p_applicants_allowed then
1398 -- l_select_stmt_per:=l_select_stmt_per||
1399 -- ' and (per.current_applicant_flag =''Y''
1400 -- or per.current_emp_or_apl_flag is null)';
1401 -- end if;
1402 -- if p_cwk_allowed then
1403 -- l_select_stmt_per:=l_select_stmt_per||
1404 -- ' and (per.current_npw_flag = ''Y''
1405 -- or per.current_emp_or_apl_flag is null)';
1406 -- end if;
1407 --AH bug 2854634:following clause replaces previous. All templates can see people who are
1408 --not "current" anything, then add conditions to also bring in types allowed by template
1409 --
1410 l_select_stmt_per:=l_select_stmt_per||
1411 ' and ( (per.current_emp_or_apl_flag is null and per.current_npw_flag is null)';
1412 if p_employees_allowed then
1413 l_select_stmt_per:=l_select_stmt_per||
1414 ' OR per.current_employee_flag =''Y'' ';
1415 end if;
1416 if p_applicants_allowed then
1417 l_select_stmt_per:=l_select_stmt_per||
1418 ' OR per.current_applicant_flag =''Y'' ';
1419 end if;
1420 if p_cwk_allowed then
1421 l_select_stmt_per:=l_select_stmt_per||
1422 ' OR per.current_npw_flag = ''Y'' ';
1423 end if;
1424 l_select_stmt_per:=l_select_stmt_per||')';
1425 --
1426 --bug 2854634 end
1427
1428
1429
1430 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)';
1431 hr_utility.set_location('r'||l_select_stmt_per,2001);
1432
1433 select_stmt:=l_from_clause||l_where_clause;
1434 l_select_stmt_asg:='select per_asg.assignment_id '||l_from_clause;
1435 if not l_asg then
1436 l_select_stmt_asg:=l_select_stmt_asg||',per_all_assignments_f per_asg';
1437 end if;
1438 l_select_stmt_asg:=l_select_stmt_asg||l_where_clause;
1439 if not l_asg then
1440 l_select_stmt_asg:=l_select_stmt_asg||' and per.person_id=per_asg.person_id and '
1441 ||l_effective_date||
1442 ' between per_asg.effective_start_date and per_asg.effective_end_date ';
1443 end if;
1444 l_select_stmt_asg:=l_select_stmt_asg||'and per_asg.assignment_type <> ''B''';-- Added for fix of #3286659
1445 l_select_stmt_asg:=l_select_stmt_asg||' and per.person_id=:1';
1446 l_select_stmt_per2:=l_select_stmt_per;
1447
1448 hr_utility.set_location('r'||l_select_stmt_per,2000);
1449
1450 while length(l_select_stmt_per2)>0 loop
1451
1452 hr_utility.set_location(substr(l_select_stmt_per2,1,70),1);
1453 l_select_stmt_per2:=substr(l_select_stmt_per2,71);
1454 end loop;
1455 --
1456
1457 hr_utility.set_location('hidd'||l_select_stmt_per,1000);
1458
1459 open emp_cv for l_select_stmt_per;
1460 loop
1461 num_row:=num_row+1;
1462 fetch emp_cv into l_person_id,l_full_name,l_num_asgs;
1463 exit when emp_cv%notfound;
1464 --
1465 open csr_person_details(p_person_id => l_person_id
1466 ,p_effective_date=> p_effective_date);
1467 fetch csr_person_details into per_rec;
1468 if csr_person_details%notfound then
1469 close csr_person_details;
1470 fnd_message.set_name(800,'XXX');
1471 fnd_message.raise_error;
1472 else
1473 close csr_person_details;
1474 end if;
1475 --
1476 -- set the output fields
1477 --
1478 out_rec.person_id :=per_rec.person_id;
1479 out_rec.full_name :=per_rec.full_name;
1480
1481 if p_fetch_details then
1482 out_rec.per_effective_start_date :=per_rec.effective_start_date;
1483 out_rec.per_effective_end_date :=per_rec.effective_end_date;
1484 --
1485 out_rec.person_type :=hr_person_type_usage_info.get_user_person_type
1486 (p_effective_date => p_effective_date
1487 ,p_person_id => per_rec.person_id);
1488 --
1489 out_rec.last_name :=per_rec.last_name;
1490 out_rec.start_date :=per_rec.start_date;
1491 out_rec.applicant_number :=per_rec.applicant_number;
1492 out_rec.background_chk_stat_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.background_check_status);
1493 out_rec.background_date_check :=per_rec.background_date_check;
1494 out_rec.blood_type_meaning :=hr_reports.get_lookup_meaning('BLOOD_TYPE',per_rec.blood_type);
1495
1496 --Modified for PMxbg
1497 if per_rec.person_id is not null then
1498 open csr_bg_name(per_rec.business_group_id);
1499 fetch csr_bg_name into out_rec.business_group_name;
1500 close csr_bg_name;
1501 else
1502 out_rec.business_group_name:=null;
1503 end if;
1504 --
1505 if per_rec.correspondence_language is not null then
1506 open csr_lang(per_rec.correspondence_language);
1507 fetch csr_lang into out_rec.corr_lang_meaning;
1508 close csr_lang;
1509 else
1510 out_rec.corr_lang_meaning:=null;
1511 end if;
1512 --
1513 out_rec.date_employee_data_verified :=per_rec.date_employee_data_verified;
1514 out_rec.date_of_birth :=per_rec.date_of_birth;
1515 out_rec.email_address :=per_rec.email_address;
1516 out_rec.employee_number :=per_rec.employee_number;
1517 out_rec.expnse_chk_send_addr_meaning :=hr_reports.get_lookup_meaning('HOME_OFFICE',per_rec.expense_check_send_to_address);
1518 out_rec.npw_number :=per_rec.npw_number;
1519 out_rec.first_name :=per_rec.first_name;
1520 out_rec.per_fte_capacity :=per_rec.fte_capacity;
1521 out_rec.full_name :=per_rec.full_name;
1522 out_rec.hold_applicant_date_until :=per_rec.hold_applicant_date_until;
1523 out_rec.honors :=per_rec.honors;
1524 out_rec.internal_location :=per_rec.internal_location;
1525 out_rec.known_as :=per_rec.known_as;
1526 out_rec.last_medical_test_by :=per_rec.last_medical_test_by;
1527 out_rec.last_medical_test_date :=per_rec.last_medical_test_date;
1528 out_rec.mailstop :=per_rec.mailstop;
1529 out_rec.marital_status_meaning :=hr_reports.get_lookup_meaning('MAR_STATUS',per_rec.marital_status);
1530 out_rec.middle_names :=per_rec.middle_names;
1531 out_rec.nationality_meaning :=hr_reports.get_lookup_meaning('NATIONALITY',per_rec.nationality);
1532 out_rec.national_identifier :=per_rec.national_identifier;
1533 out_rec.office_number :=per_rec.office_number;
1534 out_rec.on_military_service_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.on_military_service);
1535 out_rec.pre_name_adjunct :=per_rec.pre_name_adjunct;
1536 out_rec.previous_last_name :=per_rec.previous_last_name;
1537 out_rec.rehire_recommendation :=per_rec.rehire_recommendation;
1538 out_rec.resume_exists_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.resume_exists);
1539 out_rec.resume_last_updated :=per_rec.resume_last_updated;
1540 -- Bug 3037019
1541 out_rec.registered_disabled_flag :=hr_reports.get_lookup_meaning('REGISTERED_DISABLED',per_rec.registered_disabled_flag);
1542 out_rec.secnd_passport_exsts_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.second_passport_exists);
1543 out_rec.sex_meaning :=hr_reports.get_lookup_meaning('SEX',per_rec.sex);
1544 out_rec.student_status_meaning :=hr_reports.get_lookup_meaning('STUDENT_STATUS',per_rec.student_status);
1545 out_rec.suffix :=per_rec.suffix;
1546 out_rec.title_meaning :=hr_reports.get_lookup_meaning('TITLE',per_rec.title);
1547 out_rec.work_schedule_meaning :=hr_reports.get_lookup_meaning('WORK_SCHEDULE',per_rec.work_schedule);
1548 out_rec.coord_ben_med_pln_no :=per_rec.coord_ben_med_pln_no;
1549 out_rec.cord_ben_no_cvg_flag_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.coord_ben_no_cvg_flag);
1550 out_rec.dpdnt_adoption_date :=per_rec.dpdnt_adoption_date;
1551 out_rec.dpdnt_vlntry_svc_flg_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.dpdnt_vlntry_svce_flag);
1552 out_rec.receipt_of_death_cert_date :=per_rec.receipt_of_death_cert_date;
1553 out_rec.uses_tobacco_meaning :=hr_reports.get_lookup_meaning('YES_NO',per_rec.uses_tobacco_flag);
1554 --
1555 if per_rec.benefit_group_id is not null then
1556 open csr_benfts_grp(per_rec.benefit_group_id);
1557 fetch csr_benfts_grp into out_rec.benefit_group;
1558 close csr_benfts_grp;
1559 else
1560 out_rec.benefit_group:=null;
1561 end if;
1562 --
1563 /* These fields are no longer used because they may change
1564 context on a row by row basis, making them meaningless in a table
1565
1566 out_rec.attribute_category :=per_rec.attribute_category;
1567 out_rec.attribute1 :=per_rec.attribute1;
1568 out_rec.attribute2 :=per_rec.attribute2;
1569 out_rec.attribute3 :=per_rec.attribute3;
1570 out_rec.attribute4 :=per_rec.attribute4;
1571 out_rec.attribute5 :=per_rec.attribute5;
1572 out_rec.attribute6 :=per_rec.attribute6;
1573 out_rec.attribute7 :=per_rec.attribute7;
1574 out_rec.attribute8 :=per_rec.attribute8;
1575 out_rec.attribute9 :=per_rec.attribute9;
1576 out_rec.attribute10 :=per_rec.attribute10;
1577 out_rec.attribute11 :=per_rec.attribute11;
1578 out_rec.attribute12 :=per_rec.attribute12;
1579 out_rec.attribute13 :=per_rec.attribute13;
1580 out_rec.attribute14 :=per_rec.attribute14;
1581 out_rec.attribute15 :=per_rec.attribute15;
1582 out_rec.attribute16 :=per_rec.attribute16;
1583 out_rec.attribute17 :=per_rec.attribute17;
1584 out_rec.attribute18 :=per_rec.attribute18;
1585 out_rec.attribute19 :=per_rec.attribute19;
1586 out_rec.attribute20 :=per_rec.attribute20;
1587 out_rec.attribute21 :=per_rec.attribute21;
1588 out_rec.attribute22 :=per_rec.attribute22;
1589 out_rec.attribute23 :=per_rec.attribute23;
1590 out_rec.attribute24 :=per_rec.attribute24;
1591 out_rec.attribute25 :=per_rec.attribute25;
1592 out_rec.attribute26 :=per_rec.attribute26;
1593 out_rec.attribute27 :=per_rec.attribute27;
1594 out_rec.attribute28 :=per_rec.attribute28;
1595 out_rec.attribute29 :=per_rec.attribute29;
1596 out_rec.attribute30 :=per_rec.attribute30;
1597 */
1598
1599 out_rec.per_information_category :=per_rec.per_information_category;
1600 out_rec.per_information1 :=per_rec.per_information1;
1601 out_rec.per_information2 :=per_rec.per_information2;
1602 out_rec.per_information3 :=per_rec.per_information3;
1603 out_rec.per_information4 :=per_rec.per_information4;
1604 out_rec.per_information5 :=per_rec.per_information5;
1605 out_rec.per_information6 :=per_rec.per_information6;
1606 out_rec.per_information7 :=per_rec.per_information7;
1607 out_rec.per_information8 :=per_rec.per_information8;
1608 out_rec.per_information9 :=per_rec.per_information9;
1609 out_rec.per_information10 :=per_rec.per_information10;
1610 out_rec.per_information11 :=per_rec.per_information11;
1611 out_rec.per_information12 :=per_rec.per_information12;
1612 out_rec.per_information13 :=per_rec.per_information13;
1613 out_rec.per_information14 :=per_rec.per_information14;
1614 out_rec.per_information15 :=per_rec.per_information15;
1615 out_rec.per_information16 :=per_rec.per_information16;
1616 out_rec.per_information17 :=per_rec.per_information17;
1617 out_rec.per_information18 :=per_rec.per_information18;
1618 out_rec.per_information19 :=per_rec.per_information19;
1619 out_rec.per_information20 :=per_rec.per_information20;
1620 out_rec.per_information21 :=per_rec.per_information21;
1621 out_rec.per_information22 :=per_rec.per_information22;
1622 out_rec.per_information23 :=per_rec.per_information23;
1623 out_rec.per_information24 :=per_rec.per_information24;
1624 out_rec.per_information25 :=per_rec.per_information25;
1625 out_rec.per_information26 :=per_rec.per_information26;
1626 out_rec.per_information27 :=per_rec.per_information27;
1627 out_rec.per_information28 :=per_rec.per_information28;
1628 out_rec.per_information29 :=per_rec.per_information29;
1629 out_rec.per_information30 :=per_rec.per_information30;
1630 out_rec.date_of_death :=per_rec.date_of_death;
1631 --
1632 if per_rec.current_employee_flag='Y' then
1633 open csr_pds(per_rec.person_id,per_rec.effective_start_date);
1634 fetch csr_pds into out_rec.hire_date;
1635 close csr_pds;
1636 else
1637 out_rec.hire_date:=null;
1638 end if;
1639 --
1640 if per_rec.current_applicant_flag='Y' then
1641 open csr_app(per_rec.person_id,per_rec.effective_start_date);
1642 fetch csr_app into out_rec.projected_hire_date;
1643 close csr_app;
1644 else
1645 out_rec.projected_hire_date:=null;
1646 end if;
1647 --
1648 if not l_asg then
1649 open count_asgs(per_rec.person_id);
1650 fetch count_asgs into l_num_asgs;
1651 close count_asgs;
1652 end if;
1653 --
1654 if(l_num_asgs=1) then
1655 execute immediate l_select_stmt_asg into l_assignment_id using per_rec.person_id;
1656 open csr_assignment_details(l_assignment_id,p_effective_date);
1657 fetch csr_assignment_details into asg_rec;
1658 close csr_assignment_details;
1659 --
1660 out_rec.assignment_id :=asg_rec.assignment_id;
1661 out_rec.asg_effective_start_date :=asg_rec.effective_start_date;
1662 out_rec.asg_effective_end_date :=asg_rec.effective_end_date;
1663 --
1664 if asg_rec.recruiter_id is not null then
1665 open csr_full_name(asg_rec.recruiter_id,p_effective_date);
1666 fetch csr_full_name into out_rec.recruiter;
1667 close csr_full_name;
1668 else
1669 out_rec.recruiter:=null;
1670 end if;
1671 --
1672 if asg_rec.grade_id is not null then
1673 open csr_grade(asg_rec.grade_id);
1674 fetch csr_grade into out_rec.grade;
1675 close csr_grade;
1676 else
1677 out_rec.grade:=null;
1678 end if;
1679 --
1680 if asg_rec.grade_ladder_pgm_id is not null then
1681 open csr_grade_ladder(asg_rec.grade_ladder_pgm_id,p_effective_date);
1682 fetch csr_grade_ladder into out_rec.grade_ladder;
1683 close csr_grade_ladder;
1684 else
1685 out_rec.grade_ladder:=null;
1686 end if;
1687 --
1688 if asg_rec.position_id is not null then
1689 open csr_position(asg_rec.position_id,p_effective_date);
1690 fetch csr_position into out_rec.position;
1691 close csr_position;
1692 else
1693 out_rec.position:=null;
1694 end if;
1695 --
1696 if asg_rec.job_id is not null then
1697 open csr_job(asg_rec.job_id);
1698 fetch csr_job into out_rec.job;
1699 close csr_job;
1700 else
1701 out_rec.job:=null;
1702 end if;
1703 --
1704 open csr_asg_status(asg_rec.assignment_status_type_id);
1705 fetch csr_asg_status into out_rec.assignment_status_type,out_rec.system_status;
1706 close csr_asg_status;
1707 --
1708 if asg_rec.payroll_id is not null then
1709 open csr_payroll(asg_rec.payroll_id,p_effective_date);
1710 fetch csr_payroll into out_rec.payroll;
1711 close csr_payroll;
1712 else
1713 out_rec.payroll:=null;
1714 end if;
1715 --
1716 if asg_rec.location_id is not null then
1717 open csr_location(asg_rec.location_id);
1718 fetch csr_location into out_rec.location;
1719 close csr_location;
1720 else
1721 out_rec.location:=null;
1722 end if;
1723 --
1724 if asg_rec.person_referred_by_id is not null then
1725 open csr_full_name(asg_rec.person_referred_by_id,p_effective_date);
1726 fetch csr_full_name into out_rec.person_referred_by;
1727 close csr_full_name;
1728 else
1729 out_rec.person_referred_by:=null;
1730 end if;
1731 --
1732 if asg_rec.supervisor_id is not null then
1733 open csr_full_name(asg_rec.supervisor_id,p_effective_date);
1734 fetch csr_full_name into out_rec.supervisor;
1735 close csr_full_name;
1736 else
1737 out_rec.supervisor:=null;
1738 end if;
1739 --
1740 if asg_rec.supervisor_assignment_id is not null then
1741
1742 hr_utility.set_location('super_assgt_id is not null',990);
1743 hr_utility.set_location('super assgt id is ' || to_char(asg_rec.supervisor_assignment_id),990);
1744 open csr_supervisor_assgt_number(asg_rec.supervisor_assignment_id,p_effective_date);
1745 fetch csr_supervisor_assgt_number into out_rec.supervisor_assignment_number;
1746 hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1747 close csr_supervisor_assgt_number;
1748 else
1749 out_rec.supervisor_assignment_number:=null;
1750 hr_utility.set_location('set super_assgt_id to null',992);
1751 end if;
1752 --
1753 if asg_rec.recruitment_activity_id is not null then
1754 open csr_rec_activity(asg_rec.recruitment_activity_id);
1755 fetch csr_rec_activity into out_rec.recruitment_activity;
1756 close csr_rec_activity;
1757 else
1758 out_rec.recruitment_activity:=null;
1759 end if;
1760 --
1761 if asg_rec.source_organization_id is not null then
1762 open csr_organization(asg_rec.source_organization_id);
1763 fetch csr_organization into out_rec.source_organization;
1764 close csr_organization;
1765 else
1766 out_rec.source_organization:=null;
1767 end if;
1768 --
1769 open csr_organization(asg_rec.organization_id);
1770 fetch csr_organization into out_rec.organization;
1771 close csr_organization;
1772 --
1773 if asg_rec.people_group_id is not null then
1774 open csr_pgp_rec(asg_rec.people_group_id);
1775 fetch csr_pgp_rec into pgp_rec;
1776 close csr_pgp_rec;
1777 --
1778 out_rec.pgp_segment1 :=pgp_rec.segment1;
1779 out_rec.pgp_segment2 :=pgp_rec.segment2;
1780 out_rec.pgp_segment3 :=pgp_rec.segment3;
1781 out_rec.pgp_segment4 :=pgp_rec.segment4;
1782 out_rec.pgp_segment5 :=pgp_rec.segment5;
1783 out_rec.pgp_segment6 :=pgp_rec.segment6;
1784 out_rec.pgp_segment7 :=pgp_rec.segment7;
1785 out_rec.pgp_segment8 :=pgp_rec.segment9;
1786 out_rec.pgp_segment9 :=pgp_rec.segment9;
1787 out_rec.pgp_segment10 :=pgp_rec.segment10;
1788 out_rec.pgp_segment11 :=pgp_rec.segment11;
1789 out_rec.pgp_segment12 :=pgp_rec.segment12;
1790 out_rec.pgp_segment13 :=pgp_rec.segment13;
1791 out_rec.pgp_segment14 :=pgp_rec.segment14;
1792 out_rec.pgp_segment15 :=pgp_rec.segment15;
1793 out_rec.pgp_segment16 :=pgp_rec.segment16;
1794 out_rec.pgp_segment17 :=pgp_rec.segment17;
1795 out_rec.pgp_segment18 :=pgp_rec.segment18;
1796 out_rec.pgp_segment19 :=pgp_rec.segment19;
1797 out_rec.pgp_segment20 :=pgp_rec.segment20;
1798 out_rec.pgp_segment21 :=pgp_rec.segment21;
1799 out_rec.pgp_segment22 :=pgp_rec.segment22;
1800 out_rec.pgp_segment23 :=pgp_rec.segment23;
1801 out_rec.pgp_segment24 :=pgp_rec.segment24;
1802 out_rec.pgp_segment25 :=pgp_rec.segment25;
1803 out_rec.pgp_segment26 :=pgp_rec.segment26;
1804 out_rec.pgp_segment27 :=pgp_rec.segment27;
1805 out_rec.pgp_segment28 :=pgp_rec.segment28;
1806 out_rec.pgp_segment29 :=pgp_rec.segment29;
1807 out_rec.pgp_segment30 :=pgp_rec.segment30;
1808 end if;
1809 --
1810 if asg_rec.soft_coding_keyflex_id is not null then
1811 open csr_scl_rec(asg_rec.soft_coding_keyflex_id);
1812 fetch csr_scl_rec into scl_rec;
1813 close csr_scl_rec;
1814
1815 out_rec.scl_segment1 :=scl_rec.segment1;
1816 out_rec.scl_segment2 :=scl_rec.segment2;
1817 out_rec.scl_segment3 :=scl_rec.segment3;
1818 out_rec.scl_segment4 :=scl_rec.segment4;
1819 out_rec.scl_segment5 :=scl_rec.segment5;
1820 out_rec.scl_segment6 :=scl_rec.segment6;
1821 out_rec.scl_segment7 :=scl_rec.segment7;
1822 out_rec.scl_segment8 :=scl_rec.segment8;
1823 out_rec.scl_segment9 :=scl_rec.segment9;
1824 out_rec.scl_segment10 :=scl_rec.segment10;
1825 out_rec.scl_segment11 :=scl_rec.segment11;
1826 out_rec.scl_segment12 :=scl_rec.segment12;
1827 out_rec.scl_segment13 :=scl_rec.segment13;
1828 out_rec.scl_segment14 :=scl_rec.segment14;
1829 out_rec.scl_segment15 :=scl_rec.segment15;
1830 out_rec.scl_segment16 :=scl_rec.segment16;
1831 out_rec.scl_segment17 :=scl_rec.segment17;
1832 out_rec.scl_segment18 :=scl_rec.segment18;
1833 out_rec.scl_segment19 :=scl_rec.segment19;
1834 out_rec.scl_segment20 :=scl_rec.segment20;
1835 out_rec.scl_segment21 :=scl_rec.segment21;
1836 out_rec.scl_segment22 :=scl_rec.segment22;
1837 out_rec.scl_segment23 :=scl_rec.segment23;
1838 out_rec.scl_segment24 :=scl_rec.segment24;
1839 out_rec.scl_segment25 :=scl_rec.segment25;
1840 out_rec.scl_segment26 :=scl_rec.segment26;
1841 out_rec.scl_segment27 :=scl_rec.segment27;
1842 out_rec.scl_segment28 :=scl_rec.segment28;
1843 out_rec.scl_segment29 :=scl_rec.segment29;
1844 out_rec.scl_segment30 :=scl_rec.segment30;
1845 end if;
1846 --
1847 if asg_rec.vacancy_id is not null then
1848 open csr_vacancy(asg_rec.vacancy_id);
1849 fetch csr_vacancy into out_rec.vacancy,out_rec.requisition;
1850 close csr_vacancy;
1851 else
1852 out_rec.vacancy:=null;
1853 out_rec.requisition:=null;
1854 end if;
1855 --
1856
1857 if asg_rec.pay_basis_id is not null then
1858 open csr_pay_basis(asg_rec.pay_basis_id);
1859 fetch csr_pay_basis into out_rec.salary_basis,out_rec.pay_basis;
1860 close csr_pay_basis;
1861 else
1862 out_rec.salary_basis:=null;
1863 out_rec.pay_basis:=null;
1864 end if;
1865 --
1866 out_rec.assignment_sequence :=asg_rec.assignment_sequence;
1867 out_rec.assignment_type :=asg_rec.assignment_type;
1868 out_rec.asg_primary_flag :=asg_rec.primary_flag;
1869 out_rec.assignment_number :=asg_rec.assignment_number;
1870 out_rec.date_probation_end :=asg_rec.date_probation_end;
1871 out_rec.default_code_comb_id :=asg_rec.default_code_comb_id;
1872 out_rec.employment_category_meaning :=hr_reports.get_lookup_meaning('EMP_CAT',asg_rec.employment_category);
1873 out_rec.frequency_meaning :=hr_reports.get_lookup_meaning('FREQUENCY',asg_rec.frequency);
1874 out_rec.normal_hours :=asg_rec.normal_hours;
1875 out_rec.probation_period :=asg_rec.probation_period;
1876 out_rec.probation_unit_meaning :=hr_reports.get_lookup_meaning('QUALIFYING_UNITS',asg_rec.probation_unit);
1877 out_rec.time_normal_finish :=asg_rec.time_normal_finish;
1878 out_rec.time_normal_start :=asg_rec.time_normal_start;
1879 --CWK
1880 out_rec.project_title :=asg_rec.project_title;
1881 out_rec.vendor_employee_number :=asg_rec.vendor_employee_number;
1882 out_rec.vendor_assignment_number :=asg_rec.vendor_assignment_number;
1883 --
1884 if asg_rec.vendor_id is not null then
1885 open csr_vendor(asg_rec.vendor_id);
1886 fetch csr_vendor into out_rec.vendor_name;
1887 close csr_vendor;
1888 else
1889 out_rec.vendor_name:=null;
1890 end if;
1891 --
1892 if asg_rec.vendor_site_id is not null then
1893 open csr_vendor_site(asg_rec.vendor_id);
1894 fetch csr_vendor_site into out_rec.vendor_site_code;
1895 close csr_vendor_site;
1896 else
1897 out_rec.vendor_site_code:=null;
1898 end if;
1899 --
1900 if asg_rec.po_header_id is not null then
1901 open csr_po_header(asg_rec.po_header_id);
1902 fetch csr_po_header into out_rec.po_header_num;
1903 close csr_po_header;
1904 else
1905 out_rec.po_header_num:=null;
1906 end if;
1907 --
1908 if asg_rec.po_line_id is not null then
1909 open csr_po_line(asg_rec.po_line_id);
1910 fetch csr_po_line into out_rec.po_line_num;
1911 close csr_po_line;
1912 else
1913 out_rec.po_line_num:=null;
1914 end if;
1915 --
1916 /* These fields are no longer used because they may change
1917 context on a row by row basis, making them meaningless in a table
1918
1919 out_rec.ass_attribute_category :=asg_rec.ass_attribute_category;
1920 out_rec.ass_attribute1 :=asg_rec.ass_attribute1;
1921 out_rec.ass_attribute2 :=asg_rec.ass_attribute2;
1922 out_rec.ass_attribute3 :=asg_rec.ass_attribute3;
1923 out_rec.ass_attribute4 :=asg_rec.ass_attribute4;
1924 out_rec.ass_attribute5 :=asg_rec.ass_attribute5;
1925 out_rec.ass_attribute6 :=asg_rec.ass_attribute6;
1926 out_rec.ass_attribute7 :=asg_rec.ass_attribute7;
1927 out_rec.ass_attribute8 :=asg_rec.ass_attribute8;
1928 out_rec.ass_attribute9 :=asg_rec.ass_attribute9;
1929 out_rec.ass_attribute10 :=asg_rec.ass_attribute10;
1930 out_rec.ass_attribute11 :=asg_rec.ass_attribute11;
1931 out_rec.ass_attribute12 :=asg_rec.ass_attribute12;
1932 out_rec.ass_attribute13 :=asg_rec.ass_attribute13;
1933 out_rec.ass_attribute14 :=asg_rec.ass_attribute14;
1934 out_rec.ass_attribute15 :=asg_rec.ass_attribute15;
1935 out_rec.ass_attribute16 :=asg_rec.ass_attribute16;
1936 out_rec.ass_attribute17 :=asg_rec.ass_attribute17;
1937 out_rec.ass_attribute18 :=asg_rec.ass_attribute18;
1938 out_rec.ass_attribute19 :=asg_rec.ass_attribute19;
1939 out_rec.ass_attribute20 :=asg_rec.ass_attribute20;
1940 out_rec.ass_attribute21 :=asg_rec.ass_attribute21;
1941 out_rec.ass_attribute22 :=asg_rec.ass_attribute22;
1942 out_rec.ass_attribute23 :=asg_rec.ass_attribute23;
1943 out_rec.ass_attribute24 :=asg_rec.ass_attribute24;
1944 out_rec.ass_attribute25 :=asg_rec.ass_attribute25;
1945 out_rec.ass_attribute26 :=asg_rec.ass_attribute26;
1946 out_rec.ass_attribute27 :=asg_rec.ass_attribute27;
1947 out_rec.ass_attribute28 :=asg_rec.ass_attribute28;
1948 out_rec.ass_attribute29 :=asg_rec.ass_attribute29;
1949 out_rec.ass_attribute30 :=asg_rec.ass_attribute30;
1950 */
1951
1952 out_rec.bargaining_unit_code_meaning :=hr_reports.get_lookup_meaning('BARGAINING_UNIT_CODE',asg_rec.bargaining_unit_code);
1953 out_rec.labour_union_member_flag :=asg_rec.labour_union_member_flag;
1954 out_rec.hourly_salaried_meaning :=hr_reports.get_lookup_meaning('HOURLY_SALARIED_CODE',asg_rec.hourly_salaried_code);
1955 --
1956 if asg_rec.special_ceiling_step_id is not null then
1957 open csr_ceiling_step(asg_rec.special_ceiling_step_id,p_effective_date);
1958 fetch csr_ceiling_step into out_rec.special_ceiling_point, out_rec.special_ceiling_step;
1959 close csr_ceiling_step;
1960 else
1961 out_rec.special_ceiling_point:=null;
1962 out_rec.special_ceiling_step:=null;
1963 end if;
1964 --
1965 out_rec.change_reason_meaning :=hr_reports.get_lookup_meaning('APL_ASSIGN_REASON',asg_rec.change_reason);
1966 out_rec.internal_address_line :=asg_rec.internal_address_line;
1967 out_rec.manager_flag :=asg_rec.manager_flag;
1968 out_rec.perf_review_period :=asg_rec.perf_review_period;
1969 out_rec.perf_rev_period_freq_meaning :=hr_reports.get_lookup_meaning('FREQUENCY',asg_rec.perf_review_period_frequency);
1970 out_rec.sal_review_period :=asg_rec.sal_review_period;
1971 out_rec.sal_rev_period_freq_meaning :=hr_reports.get_lookup_meaning('FREQUENCY',asg_rec.sal_review_period_frequency);
1972 out_rec.source_type_meaning :=hr_reports.get_lookup_meaning('REC_TYPE',asg_rec.source_type);
1973 --
1974 if asg_rec.contract_id is not null then
1975 open csr_reference(asg_rec.contract_id,p_effective_date);
1976 fetch csr_reference into out_rec.contract;
1977 close csr_reference;
1978 else
1979 out_rec.contract:=null;
1980 end if;
1981 --
1982 if asg_rec.collective_agreement_id is not null then
1983 open csr_collective_agr(asg_rec.collective_agreement_id);
1984 fetch csr_collective_agr into out_rec.collective_agreement;
1985 close csr_collective_agr;
1986 else
1987 out_rec.collective_agreement:=null;
1988 end if;
1989 --
1990 if asg_rec.cagr_id_flex_num is not null then
1991 open csr_cagr_flex_num(asg_rec.cagr_id_flex_num);
1992 fetch csr_cagr_flex_num into out_rec.cagr_id_flex_name;
1993 close csr_cagr_flex_num;
1994 else
1995 out_rec.cagr_id_flex_name:=null;
1996 end if;
1997 --
1998 if asg_rec.establishment_id is not null then
1999 open csr_organization(asg_rec.establishment_id);
2000 fetch csr_organization into out_rec.establishment;
2001 close csr_organization;
2002 else
2003 out_rec.establishment:=null;
2004 end if;
2005 else
2006 if l_num_asgs=0 then
2007 l_other:=null;
2008 else
2009 l_other:='****';
2010 end if;
2011 hr_utility.set_location(l_proc,100);
2012 out_rec.assignment_id :=null;
2013 out_rec.asg_effective_start_date :=null;
2014 out_rec.asg_effective_end_date :=null;
2015 out_rec.recruiter :=l_other;
2016 out_rec.grade :=l_other;
2017 out_rec.grade_ladder :=l_other;
2018 out_rec.position :=l_other;
2019 out_rec.job :=l_other;
2020 out_rec.assignment_status_type :=l_other;
2021 out_rec.system_status :=l_other;
2022 out_rec.payroll :=l_other;
2023 out_rec.location :=l_other;
2024 out_rec.person_referred_by :=l_other;
2025 out_rec.supervisor :=l_other;
2026 out_rec.supervisor_assignment_number :=l_other;
2027 out_rec.recruitment_activity :=l_other;
2028 out_rec.source_organization :=l_other;
2029 out_rec.organization :=l_other;
2030 out_rec.pgp_segment1 :=l_other;
2031 out_rec.pgp_segment1_v :=l_other;
2032 out_rec.pgp_segment1_m :=l_other;
2033 out_rec.pgp_segment2 :=l_other;
2034 out_rec.pgp_segment2_v :=l_other;
2035 out_rec.pgp_segment2_m :=l_other;
2036 out_rec.pgp_segment3 :=l_other;
2037 out_rec.pgp_segment3_v :=l_other;
2038 out_rec.pgp_segment3_m :=l_other;
2039 out_rec.pgp_segment4 :=l_other;
2040 out_rec.pgp_segment4_v :=l_other;
2041 out_rec.pgp_segment4_m :=l_other;
2042 out_rec.pgp_segment5 :=l_other;
2043 out_rec.pgp_segment5_v :=l_other;
2044 out_rec.pgp_segment5_m :=l_other;
2045 out_rec.pgp_segment6 :=l_other;
2046 out_rec.pgp_segment6_v :=l_other;
2047 out_rec.pgp_segment6_m :=l_other;
2048 out_rec.pgp_segment7 :=l_other;
2049 out_rec.pgp_segment7_v :=l_other;
2050 out_rec.pgp_segment7_m :=l_other;
2051 out_rec.pgp_segment8 :=l_other;
2052 out_rec.pgp_segment8_v :=l_other;
2053 out_rec.pgp_segment8_m :=l_other;
2054 out_rec.pgp_segment9 :=l_other;
2055 out_rec.pgp_segment9_v :=l_other;
2056 out_rec.pgp_segment9_m :=l_other;
2057 out_rec.pgp_segment10 :=l_other;
2058 out_rec.pgp_segment10_v :=l_other;
2059 out_rec.pgp_segment10_m :=l_other;
2060 out_rec.pgp_segment11 :=l_other;
2061 out_rec.pgp_segment11_v :=l_other;
2062 out_rec.pgp_segment11_m :=l_other;
2063 out_rec.pgp_segment12 :=l_other;
2064 out_rec.pgp_segment12_v :=l_other;
2065 out_rec.pgp_segment12_m :=l_other;
2066 out_rec.pgp_segment13 :=l_other;
2067 out_rec.pgp_segment13_v :=l_other;
2068 out_rec.pgp_segment13_m :=l_other;
2069 out_rec.pgp_segment14 :=l_other;
2070 out_rec.pgp_segment14_v :=l_other;
2071 out_rec.pgp_segment14_m :=l_other;
2072 out_rec.pgp_segment15 :=l_other;
2073 out_rec.pgp_segment15_v :=l_other;
2074 out_rec.pgp_segment15_m :=l_other;
2075 out_rec.pgp_segment16 :=l_other;
2076 out_rec.pgp_segment16_v :=l_other;
2077 out_rec.pgp_segment16_m :=l_other;
2078 out_rec.pgp_segment17 :=l_other;
2079 out_rec.pgp_segment17_v :=l_other;
2080 out_rec.pgp_segment17_m :=l_other;
2081 out_rec.pgp_segment18 :=l_other;
2082 out_rec.pgp_segment18_v :=l_other;
2083 out_rec.pgp_segment18_m :=l_other;
2084 out_rec.pgp_segment19 :=l_other;
2085 out_rec.pgp_segment19_v :=l_other;
2086 out_rec.pgp_segment19_m :=l_other;
2087 out_rec.pgp_segment20 :=l_other;
2088 out_rec.pgp_segment20_v :=l_other;
2089 out_rec.pgp_segment20_m :=l_other;
2090 out_rec.pgp_segment21 :=l_other;
2091 out_rec.pgp_segment21_v :=l_other;
2092 out_rec.pgp_segment21_m :=l_other;
2093 out_rec.pgp_segment22 :=l_other;
2094 out_rec.pgp_segment22_v :=l_other;
2095 out_rec.pgp_segment22_m :=l_other;
2096 out_rec.pgp_segment23 :=l_other;
2097 out_rec.pgp_segment23_v :=l_other;
2098 out_rec.pgp_segment23_m :=l_other;
2099 out_rec.pgp_segment24 :=l_other;
2100 out_rec.pgp_segment24_v :=l_other;
2101 out_rec.pgp_segment24_m :=l_other;
2102 out_rec.pgp_segment25 :=l_other;
2103 out_rec.pgp_segment25_v :=l_other;
2104 out_rec.pgp_segment25_m :=l_other;
2105 out_rec.pgp_segment26 :=l_other;
2106 out_rec.pgp_segment26_v :=l_other;
2107 out_rec.pgp_segment26_m :=l_other;
2108 out_rec.pgp_segment27 :=l_other;
2109 out_rec.pgp_segment27_v :=l_other;
2110 out_rec.pgp_segment27_m :=l_other;
2111 out_rec.pgp_segment28 :=l_other;
2112 out_rec.pgp_segment28_v :=l_other;
2113 out_rec.pgp_segment28_m :=l_other;
2114 out_rec.pgp_segment29 :=l_other;
2115 out_rec.pgp_segment29_v :=l_other;
2116 out_rec.pgp_segment29_m :=l_other;
2117 out_rec.pgp_segment30 :=l_other;
2118 out_rec.pgp_segment30_v :=l_other;
2119 out_rec.pgp_segment30_m :=l_other;
2120 hr_utility.set_location(l_proc,110);
2121 out_rec.people_group_id :=null;
2122 out_rec.scl_segment1 :=l_other;
2123 out_rec.scl_segment1_v :=l_other;
2124 out_rec.scl_segment1_m :=l_other;
2125 out_rec.scl_segment2 :=l_other;
2126 out_rec.scl_segment2_v :=l_other;
2127 out_rec.scl_segment2_m :=l_other;
2128 out_rec.scl_segment3 :=l_other;
2129 out_rec.scl_segment3_v :=l_other;
2130 out_rec.scl_segment3_m :=l_other;
2131 out_rec.scl_segment4 :=l_other;
2132 out_rec.scl_segment4_v :=l_other;
2133 out_rec.scl_segment4_m :=l_other;
2134 out_rec.scl_segment5 :=l_other;
2135 out_rec.scl_segment5_v :=l_other;
2136 out_rec.scl_segment5_m :=l_other;
2137 out_rec.scl_segment6 :=l_other;
2138 out_rec.scl_segment6_v :=l_other;
2139 out_rec.scl_segment6_m :=l_other;
2140 out_rec.scl_segment7 :=l_other;
2141 out_rec.scl_segment7_v :=l_other;
2142 out_rec.scl_segment7_m :=l_other;
2143 out_rec.scl_segment8 :=l_other;
2144 out_rec.scl_segment8_v :=l_other;
2145 out_rec.scl_segment8_m :=l_other;
2146 out_rec.scl_segment9 :=l_other;
2147 out_rec.scl_segment9_v :=l_other;
2148 out_rec.scl_segment9_m :=l_other;
2149 out_rec.scl_segment10 :=l_other;
2150 out_rec.scl_segment10_v :=l_other;
2151 out_rec.scl_segment10_m :=l_other;
2152 out_rec.scl_segment11 :=l_other;
2153 out_rec.scl_segment11_v :=l_other;
2154 out_rec.scl_segment11_m :=l_other;
2155 out_rec.scl_segment12 :=l_other;
2156 out_rec.scl_segment12_v :=l_other;
2157 out_rec.scl_segment12_m :=l_other;
2158 out_rec.scl_segment13 :=l_other;
2159 out_rec.scl_segment13_v :=l_other;
2160 out_rec.scl_segment13_m :=l_other;
2161 out_rec.scl_segment14 :=l_other;
2162 out_rec.scl_segment14_v :=l_other;
2163 out_rec.scl_segment14_m :=l_other;
2164 out_rec.scl_segment15 :=l_other;
2165 out_rec.scl_segment15_v :=l_other;
2166 out_rec.scl_segment15_m :=l_other;
2167 out_rec.scl_segment16 :=l_other;
2168 out_rec.scl_segment16_v :=l_other;
2169 out_rec.scl_segment16_m :=l_other;
2170 out_rec.scl_segment17 :=l_other;
2171 out_rec.scl_segment17_v :=l_other;
2172 out_rec.scl_segment17_m :=l_other;
2173 out_rec.scl_segment18 :=l_other;
2174 out_rec.scl_segment18_v :=l_other;
2175 out_rec.scl_segment18_m :=l_other;
2176 out_rec.scl_segment19 :=l_other;
2177 out_rec.scl_segment19_v :=l_other;
2178 out_rec.scl_segment19_m :=l_other;
2179 out_rec.scl_segment20 :=l_other;
2180 out_rec.scl_segment20_v :=l_other;
2181 out_rec.scl_segment20_m :=l_other;
2182 out_rec.scl_segment21 :=l_other;
2183 out_rec.scl_segment21_v :=l_other;
2184 out_rec.scl_segment21_m :=l_other;
2185 out_rec.scl_segment22 :=l_other;
2186 out_rec.scl_segment22_v :=l_other;
2187 out_rec.scl_segment22_m :=l_other;
2188 out_rec.scl_segment23 :=l_other;
2189 out_rec.scl_segment23_v :=l_other;
2190 out_rec.scl_segment23_m :=l_other;
2191 out_rec.scl_segment24 :=l_other;
2192 out_rec.scl_segment24_v :=l_other;
2193 out_rec.scl_segment24_m :=l_other;
2194 out_rec.scl_segment25 :=l_other;
2195 out_rec.scl_segment25_v :=l_other;
2196 out_rec.scl_segment25_m :=l_other;
2197 out_rec.scl_segment26 :=l_other;
2198 out_rec.scl_segment26_v :=l_other;
2199 out_rec.scl_segment26_m :=l_other;
2200 out_rec.scl_segment27 :=l_other;
2201 out_rec.scl_segment27_v :=l_other;
2202 out_rec.scl_segment27_m :=l_other;
2203 out_rec.scl_segment28 :=l_other;
2204 out_rec.scl_segment28_v :=l_other;
2205 out_rec.scl_segment28_m :=l_other;
2206 out_rec.scl_segment29 :=l_other;
2207 out_rec.scl_segment29_v :=l_other;
2208 out_rec.scl_segment29_m :=l_other;
2209 out_rec.scl_segment30 :=l_other;
2210 out_rec.scl_segment30_v :=l_other;
2211 out_rec.scl_segment30_m :=l_other;
2212 out_rec.soft_coding_keyflex_id :=null;
2213 hr_utility.set_location(l_proc,120);
2214 out_rec.vacancy :=l_other;
2215 out_rec.requisition :=l_other;
2216 out_rec.salary_basis :=l_other;
2217 out_rec.pay_basis :=l_other;
2218 out_rec.assignment_sequence :=null;
2219 out_rec.assignment_type :=null;
2220 out_rec.asg_primary_flag :=l_other;
2221 out_rec.assignment_number :=l_other;
2222 out_rec.date_probation_end :=null;
2223 out_rec.default_code_comb_id :=null;
2224 out_rec.employment_category_meaning :=l_other;
2225 out_rec.frequency_meaning :=l_other;
2226 out_rec.normal_hours :=null;
2227 out_rec.probation_period :=null;
2228 out_rec.probation_unit_meaning :=l_other;
2229 out_rec.time_normal_finish :=l_other;
2230 out_rec.time_normal_start :=l_other;
2231 --CWK
2232 out_rec.project_title :=l_other;
2233 out_rec.vendor_name :=l_other;
2234 out_rec.vendor_employee_number :=l_other;
2235 out_rec.vendor_assignment_number :=l_other;
2236 out_rec.vendor_site_code :=l_other;
2237 out_rec.po_header_num :=l_other;
2238 out_rec.po_line_num :=null;
2239 --
2240 /* These fields are no longer used because they may change
2241 context on a row by row basis, making them meaningless in a table
2242
2243 out_rec.ass_attribute_category :=l_other;
2244 out_rec.ass_attribute1 :=l_other;
2245 out_rec.ass_attribute1_v :=l_other;
2246 out_rec.ass_attribute1_m :=l_other;
2247 out_rec.ass_attribute2 :=l_other;
2248 out_rec.ass_attribute2_v :=l_other;
2249 out_rec.ass_attribute2_m :=l_other;
2250 out_rec.ass_attribute3 :=l_other;
2251 out_rec.ass_attribute3_v :=l_other;
2252 out_rec.ass_attribute3_m :=l_other;
2253 out_rec.ass_attribute4 :=l_other;
2254 out_rec.ass_attribute4_v :=l_other;
2255 out_rec.ass_attribute4_m :=l_other;
2256 out_rec.ass_attribute5 :=l_other;
2257 out_rec.ass_attribute5_v :=l_other;
2258 out_rec.ass_attribute5_m :=l_other;
2259 out_rec.ass_attribute6 :=l_other;
2260 out_rec.ass_attribute6_v :=l_other;
2261 out_rec.ass_attribute6_m :=l_other;
2262 out_rec.ass_attribute7 :=l_other;
2263 out_rec.ass_attribute7_v :=l_other;
2264 out_rec.ass_attribute7_m :=l_other;
2265 out_rec.ass_attribute8 :=l_other;
2266 out_rec.ass_attribute8_v :=l_other;
2267 out_rec.ass_attribute8_m :=l_other;
2268 out_rec.ass_attribute9 :=l_other;
2269 out_rec.ass_attribute9_v :=l_other;
2270 out_rec.ass_attribute9_m :=l_other;
2271 out_rec.ass_attribute10 :=l_other;
2272 out_rec.ass_attribute10_v :=l_other;
2273 out_rec.ass_attribute10_m :=l_other;
2274 out_rec.ass_attribute11 :=l_other;
2275 out_rec.ass_attribute11_v :=l_other;
2276 out_rec.ass_attribute11_m :=l_other;
2277 out_rec.ass_attribute12 :=l_other;
2278 out_rec.ass_attribute12_v :=l_other;
2279 out_rec.ass_attribute12_m :=l_other;
2280 out_rec.ass_attribute13 :=l_other;
2281 out_rec.ass_attribute13_v :=l_other;
2282 out_rec.ass_attribute13_m :=l_other;
2283 out_rec.ass_attribute14 :=l_other;
2284 out_rec.ass_attribute14_v :=l_other;
2285 out_rec.ass_attribute14_m :=l_other;
2286 out_rec.ass_attribute15 :=l_other;
2287 out_rec.ass_attribute15_v :=l_other;
2288 out_rec.ass_attribute15_m :=l_other;
2289 out_rec.ass_attribute16 :=l_other;
2290 out_rec.ass_attribute16_v :=l_other;
2291 out_rec.ass_attribute16_m :=l_other;
2292 out_rec.ass_attribute17 :=l_other;
2293 out_rec.ass_attribute17_v :=l_other;
2294 out_rec.ass_attribute17_m :=l_other;
2295 out_rec.ass_attribute18 :=l_other;
2296 out_rec.ass_attribute18_v :=l_other;
2297 out_rec.ass_attribute18_m :=l_other;
2298 out_rec.ass_attribute19 :=l_other;
2299 out_rec.ass_attribute19_v :=l_other;
2300 out_rec.ass_attribute19_m :=l_other;
2301 out_rec.ass_attribute20 :=l_other;
2302 out_rec.ass_attribute20_v :=l_other;
2303 out_rec.ass_attribute20_m :=l_other;
2304 out_rec.ass_attribute21 :=l_other;
2305 out_rec.ass_attribute21_v :=l_other;
2306 out_rec.ass_attribute21_m :=l_other;
2307 out_rec.ass_attribute22 :=l_other;
2308 out_rec.ass_attribute22_v :=l_other;
2309 out_rec.ass_attribute22_m :=l_other;
2310 out_rec.ass_attribute23 :=l_other;
2311 out_rec.ass_attribute23_v :=l_other;
2312 out_rec.ass_attribute23_m :=l_other;
2313 out_rec.ass_attribute24 :=l_other;
2314 out_rec.ass_attribute24_v :=l_other;
2315 out_rec.ass_attribute24_m :=l_other;
2316 out_rec.ass_attribute25 :=l_other;
2317 out_rec.ass_attribute25_v :=l_other;
2318 out_rec.ass_attribute25_m :=l_other;
2319 out_rec.ass_attribute26 :=l_other;
2320 out_rec.ass_attribute26_v :=l_other;
2321 out_rec.ass_attribute26_m :=l_other;
2322 out_rec.ass_attribute27 :=l_other;
2323 out_rec.ass_attribute27_v :=l_other;
2324 out_rec.ass_attribute27_m :=l_other;
2325 out_rec.ass_attribute28 :=l_other;
2326 out_rec.ass_attribute28_v :=l_other;
2327 out_rec.ass_attribute28_m :=l_other;
2328 out_rec.ass_attribute29 :=l_other;
2329 out_rec.ass_attribute29_v :=l_other;
2330 out_rec.ass_attribute29_m :=l_other;
2331 out_rec.ass_attribute30 :=l_other;
2332 out_rec.ass_attribute30_v :=l_other;
2333 out_rec.ass_attribute30_m :=l_other;
2334 */
2335 hr_utility.set_location(l_proc,130);
2336 out_rec.bargaining_unit_code_meaning :=l_other;
2337 out_rec.labour_union_member_flag :=l_other;
2338 out_rec.hourly_salaried_meaning :=l_other;
2339 out_rec.special_ceiling_step :=null;
2340 out_rec.special_ceiling_point :=l_other;
2341 out_rec.change_reason_meaning :=l_other;
2342 out_rec.internal_address_line :=l_other;
2343 out_rec.manager_flag :=l_other;
2344 out_rec.perf_review_period :=null;
2345 out_rec.perf_rev_period_freq_meaning :=l_other;
2346 out_rec.sal_review_period :=null;
2347 out_rec.sal_rev_period_freq_meaning :=l_other;
2348 out_rec.source_type_meaning :=l_other;
2349 out_rec.contract :=l_other;
2350 out_rec.collective_agreement :=l_other;
2351 out_rec.cagr_id_flex_name :=l_other;
2352 out_rec.cagr_grade :=null;
2353 out_rec.establishment :=l_other;
2354 hr_utility.set_location(l_proc,140);
2355 --
2356 end if;
2357 end if;
2358 resultset(num_row):=out_rec;
2359 end loop;
2360
2361 hr_utility.set_location('Leaving: '||l_proc||num_row,1000);
2362 close emp_cv;
2363 --
2364 end findquery;
2365 --
2366 --
2367 procedure insert_varchar2(p_query_id number
2368 ,p_field varchar2
2369 ,p_value varchar2
2370 ) is
2371 begin
2372 insert into per_query_criteria
2373 (query_id
2374 ,field
2375 ,field_type
2376 ,varchar2_value
2377 ,number_value
2378 ,date_value
2379 ,object_version_number)
2380 values
2381 (p_query_id
2382 ,p_field
2383 ,'V'
2384 ,replace(p_value,g_quote,g_quote||g_quote)
2385 ,null
2386 ,null
2387 ,1);
2388 end insert_varchar2;
2389 --
2390 procedure insert_number (p_query_id number
2391 ,p_field varchar2
2392 ,p_value number
2393 ) is
2394 begin
2395 insert into per_query_criteria
2396 (query_id
2397 ,field
2398 ,field_type
2399 ,varchar2_value
2400 ,number_value
2401 ,date_value
2402 ,object_version_number)
2403 values
2404 (p_query_id
2405 ,p_field
2406 ,'N'
2407 ,null
2408 ,p_value
2409 ,null
2410 ,1);
2411 end insert_number;
2412 --
2413 procedure insert_date (p_query_id number
2414 ,p_field varchar2
2415 ,p_value date
2416 ) is
2417 begin
2418 insert into per_query_criteria
2419 (query_id
2420 ,field
2421 ,field_type
2422 ,varchar2_value
2423 ,number_value
2424 ,date_value
2425 ,object_version_number)
2426 values
2427 (p_query_id
2428 ,p_field
2429 ,'D'
2430 ,null
2431 ,null
2432 ,p_value
2433 ,1);
2434 end insert_date;
2435 --
2436 procedure findsave(
2437 query_id in number
2438 ,business_group_id in per_all_people_f.business_group_id%type
2439 ,business_group_name in per_business_groups.name%type
2440 ,person_id in per_all_people_f.person_id%type default null
2441 ,person_type in per_person_types.user_person_type%type default null
2442 ,system_person_type in per_person_types.system_person_type%type default null
2443 ,person_type_id in per_all_people_f.person_type_id%type default null
2444 ,last_name in per_all_people_f.last_name%type default null
2445 ,start_date in per_all_people_f.start_date%type default null
2446 ,hire_date in per_periods_of_service.date_start%type default null
2447 ,applicant_number in per_all_people_f.applicant_number%type default null
2448 ,date_of_birth in per_all_people_f.date_of_birth%type default null
2449 ,email_address in per_all_people_f.email_address%type default null
2450 ,employee_number in per_all_people_f.employee_number%type default null
2451 --CWK
2452 ,npw_number in per_all_people_f.npw_number%type default null
2453 ,project_title in per_all_assignments_f.project_title%type default null
2454 ,vendor_id in per_all_assignments_f.vendor_id%type default null
2455 ,vendor_name in po_vendors.vendor_name%type default null
2456 ,vendor_employee_number in per_all_assignments_f.vendor_employee_number%type default null
2457 ,vendor_assignment_number in per_all_assignments_f.vendor_assignment_number%type default null
2458 ,vendor_site_code in po_vendor_sites_all.vendor_site_code%TYPE default null
2459 ,vendor_site_id in po_vendor_sites_all.vendor_site_id%TYPE default null
2460 ,po_header_num in po_headers_all.segment1%TYPE default null
2461 ,po_header_id in po_headers_all.po_header_id%TYPE default null
2462 ,po_line_num in po_lines_all.line_num%TYPE default null
2463 ,po_line_id in po_lines_all.po_line_id%TYPE default null
2464 --
2465 ,first_name in per_all_people_f.first_name%type default null
2466 ,full_name in per_all_people_f.full_name%type default null
2467 ,title in per_all_people_f.title%type
2468 ,middle_names in per_all_people_f.middle_names%type
2469 ,nationality_meaning in hr_lookups.meaning%type default null
2470 ,nationality in per_all_people_f.nationality%type default null
2471 ,national_identifier in per_all_people_f.national_identifier%type default null
2472 -- Bug 3037019 Start Here
2473 ,registered_disabled_flag in hr_lookups.meaning%type default null
2474 ,registered_disabled in per_all_people_f.registered_disabled_flag%type default null
2475 ,sex_meaning in hr_lookups.meaning%type default null
2476 ,sex in per_all_people_f.sex%type default null
2477 ,benefit_group in ben_benfts_grp.name%type default null
2478 ,benefit_group_id in per_all_people_f.benefit_group_id%type default null
2479 ,grade in per_grades.name%type default null
2480 ,grade_id in per_all_assignments_f.grade_id%type default null
2481 ,grade_ladder in ben_pgm_f.name%type default null
2482 ,grade_ladder_pgm_id in per_all_assignments_f.grade_ladder_pgm_id%type default null
2483 ,position in hr_all_positions_f.name%type default null
2484 ,position_id in per_all_assignments_f.position_id%type default null
2485 ,job in per_jobs.name%type default null
2486 ,job_id in per_all_assignments_f.job_id%type default null
2487 ,assignment_status_type in per_assignment_status_types.user_status%type default null
2488 ,assignment_status_type_id in per_all_assignments_f.assignment_status_type_id%type default null
2489 ,payroll in pay_all_payrolls_f.payroll_name%type default null
2490 ,payroll_id in per_all_assignments_f.payroll_id%type default null
2491 ,location in hr_locations.location_code%type default null
2492 ,location_id in per_all_assignments_f.location_id%type default null
2493 ,supervisor in per_all_people_f.full_name%type default null
2494 ,supervisor_id in per_all_assignments_f.supervisor_id%type default null
2495 ,supervisor_assignment_number in per_assignments_v.supervisor_assignment_number%type default null
2496 ,supervisor_assignment_id in per_all_assignments_f.supervisor_assignment_id%type default null
2497 ,recruitment_activity in per_recruitment_activities.name%type default null
2498 ,recruitment_activity_id in per_all_assignments_f.recruitment_activity_id%type default null
2499 ,organization in hr_all_organization_units.name%type default null
2500 ,organization_id in per_all_assignments_f.organization_id%type default null
2501 ,people_group in pay_people_groups.group_name%type default null
2502 ,people_group_id in per_all_assignments_f.people_group_id%type default null
2503 ,vacancy in per_vacancies.name%type default null
2504 ,vacancy_id in per_all_assignments_f.vacancy_id%type default null
2505 ,requisition in per_requisitions.name%type default null
2506 ,requisition_id in per_requisitions.requisition_id%type default null
2507 ,salary_basis in per_pay_bases.name%type default null
2508 ,pay_basis_id in per_all_assignments_f.pay_basis_id%type default null
2509 ,bargaining_unit_code_meaning in hr_lookups.meaning%type default null
2510 ,bargaining_unit_code in per_all_assignments_f.bargaining_unit_code%type default null
2511 ,employment_category_meaning in hr_lookups.meaning%type default null
2512 ,employment_category in per_all_assignments_f.employment_category%type default null
2513 --bug 3002915 starts here. modified the type.
2514 ,establishment in hr_leg_establishments_v.name%type default null
2515 ,establishment_id in hr_leg_establishments_v.organization_id%type default null
2516 --bug 3002915 ends here.
2517 ,projected_hire_date in per_applications.projected_hire_date%type default null
2518 ,secure in varchar2 default null
2519 ,field1_name in varchar2 default null
2520 ,field1_condition_code in varchar2 default null
2521 ,field1_value in varchar2 default null
2522 ,field2_name in varchar2 default null
2523 ,field2_condition_code in varchar2 default null
2524 ,field2_value in varchar2 default null
2525 ,field3_name in varchar2 default null
2526 ,field3_condition_code in varchar2 default null
2527 ,field3_value in varchar2 default null
2528 ,field4_name in varchar2 default null
2529 ,field4_condition_code in varchar2 default null
2530 ,field4_value in varchar2 default null
2531 ,field5_name in varchar2 default null
2532 ,field5_condition_code in varchar2 default null
2533 ,field5_value in varchar2 default null
2534 ) is
2535 pragma autonomous_transaction;
2536 --
2537 l_query_id number;
2538 l_proc varchar2(72):=g_package||'findsave';
2539 --
2540 --
2541 begin
2542 hr_utility.set_location('Entering '||l_proc,10);
2543 l_query_id:=query_id;
2544 --
2545 if business_group_id is not null then
2546 insert_number(l_query_id,'BUSINESS_GROUP_ID',business_group_id);
2547 end if;
2548 --Modified for PMxbg
2549 if business_group_name is not null then
2550 insert_varchar2(l_query_id,'BUSINESS_GROUP_NAME',business_group_name);
2551 end if;
2552 if person_id is not null then
2553 insert_number(l_query_id,'PERSON_ID',person_id);
2554 end if;
2555 if person_type is not null then
2556 insert_varchar2(l_query_id,'PERSON_TYPE',person_type);
2557 end if;
2558 if system_person_type is not null then
2559 insert_varchar2(l_query_id,'SYSTEM_PERSON_TYPE',system_person_type);
2560 end if;
2561 if person_type_id is not null then
2562 insert_number(l_query_id,'PERSON_TYPE_ID',person_type_id);
2563 end if;
2564 if last_name is not null then
2565 insert_varchar2(l_query_id,'LAST_NAME',last_name);
2566 end if;
2567 if start_date is not null then
2568 insert_date(l_query_id,'START_DATE',start_date);
2569 end if;
2570 if hire_date is not null then
2571 insert_date(l_query_id,'HIRE_DATE',hire_date);
2572 end if;
2573 if applicant_number is not null then
2574 insert_varchar2(l_query_id,'APPLICANT_NUMBER',applicant_number);
2575 end if;
2576 if date_of_birth is not null then
2577 insert_date(l_query_id,'DATE_OF_BIRTH',date_of_birth);
2578 end if;
2579 if email_address is not null then
2580 insert_varchar2(l_query_id,'EMAIL_ADDRESS',email_address);
2581 end if;
2582 if employee_number is not null then
2583 insert_varchar2(l_query_id,'EMPLOYEE_NUMBER',employee_number);
2584 end if;
2585 --CWK
2586 if npw_number is not null then
2587 insert_varchar2(l_query_id,'NPW_NUMBER',npw_number);
2588 end if;
2589 if project_title is not null then
2590 insert_varchar2(l_query_id,'PROJECT_TITLE',project_title);
2591 end if;
2592 if vendor_id is not null then
2593 insert_number(l_query_id,'VENDOR_ID',vendor_id);
2594 end if;
2595 if vendor_name is not null then
2596 insert_varchar2(l_query_id,'VENDOR_NAME',vendor_name);
2597 end if;
2598 if vendor_employee_number is not null then
2599 insert_varchar2(l_query_id,'VENDOR_EMPLOYEE_NUMBER',vendor_employee_number);
2600 end if;
2601 if vendor_assignment_number is not null then
2602 insert_varchar2(l_query_id,'VENDOR_ASSIGNMENT_NUMBER',vendor_assignment_number);
2603 end if;
2604 if vendor_site_id is not null then
2605 insert_number(l_query_id,'VENDOR_SITE_ID',vendor_site_id);
2606 end if;
2607 if vendor_site_code is not null then
2608 insert_varchar2(l_query_id,'VENDOR_SITE_CODE',vendor_site_code);
2609 end if;
2610 if po_header_id is not null then
2611 insert_number(l_query_id,'PO_HEADER_ID',po_header_id);
2612 end if;
2613 if po_header_num is not null then
2614 insert_varchar2(l_query_id,'PO_HEADER_NUM',po_header_num);
2615 end if;
2616 if po_line_id is not null then
2617 insert_number(l_query_id,'PO_LINE_ID',po_line_id);
2618 end if;
2619 if po_line_num is not null then
2620 insert_varchar2(l_query_id,'PO_LINE_NUM',po_line_num);
2621 end if;
2622 --
2623 if first_name is not null then
2624 insert_varchar2(l_query_id,'FIRST_NAME',first_name);
2625 end if;
2626 if full_name is not null then
2627 insert_varchar2(l_query_id,'FULL_NAME',full_name);
2628 end if;
2629 if title is not null then
2630 insert_varchar2(l_query_id,'TITLE',title);
2631 end if;
2632 if middle_names is not null then
2633 insert_varchar2(l_query_id,'MIDDLE_NAMES',middle_names);
2634 end if;
2635 if nationality_meaning is not null then
2636 insert_varchar2(l_query_id,'NATIONALITY_MEANING',nationality_meaning);
2637 end if;
2638 if nationality is not null then
2639 insert_varchar2(l_query_id,'NATIONALITY',nationality);
2640 end if;
2641 if national_identifier is not null then
2642 insert_varchar2(l_query_id,'NATIONAL_IDENTIFIER',national_identifier);
2643 end if;
2644 -- Bug 3037019 Start here
2645 if registered_disabled_flag is not null then
2646 insert_varchar2(l_query_id,'REGISTERED_DISABLED_FLAG',registered_disabled_flag);
2647 end if;
2648 -- Bug 3037019 End Here
2649 if registered_disabled is not null then
2650 insert_varchar2(l_query_id,'REGISTERED_DISABLED',registered_disabled);
2651 end if;
2652 if sex_meaning is not null then
2653 insert_varchar2(l_query_id,'SEX_MEANING',sex_meaning);
2654 end if;
2655 if sex is not null then
2656 insert_varchar2(l_query_id,'SEX',sex);
2657 end if;
2658 if benefit_group is not null then
2659 insert_varchar2(l_query_id,'BENEFIT_GROUP',benefit_group);
2660 end if;
2661 if benefit_group_id is not null then
2662 insert_number(l_query_id,'BENEFIT_GROUP_ID',benefit_group_id);
2663 end if;
2664 if grade is not null then
2665 insert_varchar2(l_query_id,'GRADE',grade);
2666 end if;
2667 if grade_id is not null then
2668 insert_number(l_query_id,'GRADE_ID',grade_id);
2669 end if;
2670 if grade_ladder is not null then
2671 insert_varchar2(l_query_id,'GRADE_LADDER',grade_ladder);
2672 end if;
2673 if grade_ladder_pgm_id is not null then
2674 insert_number(l_query_id,'GRADE_LADDER_PGM_ID',grade_ladder_pgm_id);
2675 end if;
2676 if position is not null then
2677 insert_varchar2(l_query_id,'POSITION',position);
2678 end if;
2679 if position_id is not null then
2680 insert_number(l_query_id,'POSITION_ID',position_id);
2681 end if;
2682 if job is not null then
2683 insert_varchar2(l_query_id,'JOB',job);
2684 end if;
2685 if job_id is not null then
2686 insert_number(l_query_id,'JOB_ID',job_id);
2687 end if;
2688 if assignment_status_type is not null then
2689 insert_varchar2(l_query_id,'ASSIGNMENT_STATUS_TYPE',assignment_status_type);
2690 end if;
2691 if assignment_status_type_id is not null then
2692 insert_number(l_query_id,'ASSIGNMENT_STATUS_TYPE_ID',assignment_status_type_id);
2693 end if;
2694 if payroll is not null then
2695 insert_varchar2(l_query_id,'PAYROLL',payroll);
2696 end if;
2697 if payroll_id is not null then
2698 insert_number(l_query_id,'PAYROLL_ID',payroll_id);
2699 end if;
2700 if location is not null then
2701 insert_varchar2(l_query_id,'LOCATION',location);
2702 end if;
2703 if location_id is not null then
2704 insert_number(l_query_id,'LOCATION_ID',location_id);
2705 end if;
2706 if supervisor is not null then
2707 insert_varchar2(l_query_id,'SUPERVISOR',supervisor);
2708 end if;
2709 if supervisor_id is not null then
2710 insert_number(l_query_id,'SUPERVISOR_ID',supervisor_id);
2711 end if;
2712 if supervisor_assignment_number is not null then
2713 insert_varchar2(l_query_id,'SUPERVISOR_ASSIGNMENT_NUMBER',supervisor_assignment_number);
2714 end if;
2715 if supervisor_assignment_id is not null then
2716 insert_number(l_query_id,'SUPERVISOR_ASSIGNMENT_ID',supervisor_assignment_id);
2717 end if;
2718 if recruitment_activity is not null then
2719 insert_varchar2(l_query_id,'RECRUITMENT_ACTIVITY',recruitment_activity);
2720 end if;
2721 if recruitment_activity_id is not null then
2722 insert_number(l_query_id,'RECRUITMENT_ACTIVITY_ID',recruitment_activity_id);
2723 end if;
2724 if organization is not null then
2725 insert_varchar2(l_query_id,'ORGANIZATION',organization);
2726 end if;
2727 if organization_id is not null then
2728 insert_number(l_query_id,'ORGANIZATION_ID',organization_id);
2729 end if;
2730 if people_group is not null then
2731 insert_varchar2(l_query_id,'PEOPLE_GROUP',people_group);
2732 end if;
2733 if people_group_id is not null then
2734 insert_number(l_query_id,'PEOPLE_GROUP_ID',people_group_id);
2735 end if;
2736 if vacancy is not null then
2737 insert_varchar2(l_query_id,'VACANCY',vacancy);
2738 end if;
2739 if vacancy_id is not null then
2740 insert_number(l_query_id,'VACANCY_ID',vacancy_id);
2741 end if;
2742 if requisition is not null then
2743 insert_varchar2(l_query_id,'REQUISITION',requisition);
2744 end if;
2745 if requisition_id is not null then
2746 insert_number(l_query_id,'REQUISITION_ID',requisition_id);
2747 end if;
2748 if salary_basis is not null then
2749 insert_varchar2(l_query_id,'SALARY_BASIS',salary_basis);
2750 end if;
2751 if pay_basis_id is not null then
2752 insert_number(l_query_id,'PAY_BASIS_ID',pay_basis_id);
2753 end if;
2754 if bargaining_unit_code_meaning is not null then
2755 insert_varchar2(l_query_id,'BARGAINING_UNIT_CODE_MEANING',bargaining_unit_code_meaning);
2756 end if;
2757 if bargaining_unit_code is not null then
2758 insert_varchar2(l_query_id,'BARGAINING_UNIT_CODE',bargaining_unit_code);
2759 end if;
2760 if employment_category_meaning is not null then
2761 insert_varchar2(l_query_id,'EMPLOYMENT_CATEGORY_MEANING',employment_category_meaning);
2762 end if;
2763 if employment_category is not null then
2764 insert_varchar2(l_query_id,'EMPLOYMENT_CATEGORY',employment_category);
2765 end if;
2766 if establishment is not null then
2767 insert_varchar2(l_query_id,'ESTABLISHMENT',establishment);
2768 end if;
2769 if establishment_id is not null then
2770 insert_number(l_query_id,'ESTABLISHMENT_ID',establishment_id);
2771 end if;
2772 if projected_hire_date is not null then
2773 insert_date(l_query_id,'PROJECTED_HIRE_DATE',projected_hire_date);
2774 end if;
2775 if secure is not null then
2776 insert_varchar2(l_query_id,'SECURE',secure);
2777 end if;
2778 if field1_name is not null then
2779 insert_varchar2(l_query_id,'FIELD1_NAME',field1_name);
2780 end if;
2781 if field1_condition_code is not null then
2782 insert_varchar2(l_query_id,'FIELD1_CONDITION_CODE',field1_condition_code);
2783 end if;
2784 if field1_value is not null then
2785 insert_varchar2(l_query_id,'FIELD1_VALUE',field1_value);
2786 end if;
2787 if field2_name is not null then
2788 insert_varchar2(l_query_id,'FIELD2_NAME',field2_name);
2789 end if;
2790 if field2_condition_code is not null then
2791 insert_varchar2(l_query_id,'FIELD2_CONDITION_CODE',field2_condition_code);
2792 end if;
2793 if field2_value is not null then
2794 insert_varchar2(l_query_id,'FIELD2_VALUE',field2_value);
2795 end if;
2796 if field3_name is not null then
2797 insert_varchar2(l_query_id,'FIELD3_NAME',field3_name);
2798 end if;
2799 if field3_condition_code is not null then
2800 insert_varchar2(l_query_id,'FIELD3_CONDITION_CODE',field3_condition_code);
2801 end if;
2802 if field3_value is not null then
2803 insert_varchar2(l_query_id,'FIELD3_VALUE',field3_value);
2804 end if;
2805 if field4_name is not null then
2806 insert_varchar2(l_query_id,'FIELD4_NAME',field4_name);
2807 end if;
2808 if field4_condition_code is not null then
2809 insert_varchar2(l_query_id,'FIELD4_CONDITION_CODE',field4_condition_code);
2810 end if;
2811 if field4_value is not null then
2812 insert_varchar2(l_query_id,'FIELD4_VALUE',field4_value);
2813 end if;
2814 if field5_name is not null then
2815 insert_varchar2(l_query_id,'FIELD5_NAME',field5_name);
2816 end if;
2817 if field5_condition_code is not null then
2818 insert_varchar2(l_query_id,'FIELD5_CONDITION_CODE',field5_condition_code);
2819 end if;
2820 if field5_value is not null then
2821 insert_varchar2(l_query_id,'FIELD5_VALUE',field5_value);
2822 end if;
2823 commit;
2824 hr_utility.set_location('Leaving '||l_proc,100);
2825 end findsave;
2826 --
2827 function get_varchar2(p_query_id number
2828 ,p_field varchar2)
2829 return varchar2 is
2830 l_value varchar2(240);
2831 --
2832 cursor get_varchar2 is
2833 select varchar2_value
2834 from per_query_criteria
2835 where query_id=p_query_id
2836 and field=p_field;
2837 begin
2838 open get_varchar2;
2839 fetch get_varchar2 into l_value;
2840 close get_varchar2;
2841 return l_value;
2842 end get_varchar2;
2843 --
2844 function get_number (p_query_id number
2845 ,p_field varchar2)
2846 return number is
2847 l_value number;
2848 --
2849 cursor get_number is
2850 select number_value
2851 from per_query_criteria
2852 where query_id=p_query_id
2853 and field=p_field;
2854 begin
2855 open get_number;
2856 fetch get_number into l_value;
2857 close get_number;
2858 return l_value;
2859 end get_number;
2860 --
2861 function get_date (p_query_id number
2862 ,p_field varchar2)
2863 return date is
2864 l_value date;
2865 --
2866 cursor get_date is
2867 select date_value
2868 from per_query_criteria
2869 where query_id=p_query_id
2870 and field=p_field;
2871 begin
2872 open get_date;
2873 fetch get_date into l_value;
2874 close get_date;
2875 return l_value;
2876 end get_date;
2877
2878 procedure findretrieve
2879 (p_query_id in number
2880 ,p_effective_date in date
2881 ,p_customized_restriction_id in number default null
2882 ,p_employees_allowed in boolean default false
2883 ,p_applicants_allowed in boolean default false
2884 ,p_cwk_allowed in boolean default false
2885 ,p_people_tab out nocopy findtab
2886 ) is
2887 --
2888 l_findtab findtab;
2889 i number;
2890 l_select_stmt_per VARCHAR2(20000);
2891 begin
2892 --
2893 findquery(resultset => l_findtab
2894 ,p_effective_date => p_effective_date
2895 ,business_group_id => get_number(p_query_id,'BUSINESS_GROUP_ID')
2896 ,business_group_name => get_varchar2(p_query_id,'BUSINESS_GROUP_NAME')
2897 ,person_id => get_number(p_query_id,'PERSON_ID')
2898 ,person_type => get_varchar2(p_query_id,'PERSON_TYPE')
2899 ,system_person_type => get_varchar2(p_query_id,'SYSTEM_PERSON_TYPE')
2900 ,person_type_id => get_number(p_query_id,'PERSON_TYPE_ID')
2901 ,last_name => get_varchar2(p_query_id,'LAST_NAME')
2902 ,start_date => get_date(p_query_id,'START_DATE')
2903 ,hire_date => get_date(p_query_id,'HIRE_DATE')
2904 ,applicant_number => get_varchar2(p_query_id,'APPLICANT_NUMBER')
2905 ,date_of_birth => get_date(p_query_id,'DATE_OF_BIRTH')
2906 ,email_address => get_varchar2(p_query_id,'EMAIL_ADDRESS')
2907 ,employee_number => get_varchar2(p_query_id,'EMPLOYEE_NUMBER')
2908 --CWK
2909 ,npw_number => get_varchar2(p_query_id,'')
2910 ,project_title => get_varchar2(p_query_id,'PROJECT_TITLE')
2911 ,vendor_id => get_number(p_query_id,'VENDOR_ID')
2912 ,vendor_name => get_varchar2(p_query_id,'VENDOR_NAME')
2913 ,vendor_employee_number => get_varchar2(p_query_id,'VENDOR_EMPLOYEE_NUMBER')
2914 ,vendor_assignment_number => get_varchar2(p_query_id,'VENDOR_ASSIGNMENT_NUMBER')
2915 ,vendor_site_id => get_number(p_query_id,'VENDOR_SITE_ID')
2916 ,vendor_site_code => get_varchar2(p_query_id,'VENDOR_SITE_CODE')
2917 ,po_header_id => get_number(p_query_id,'PO_HEADER_ID')
2918 ,po_header_num => get_varchar2(p_query_id,'PO_HEADER_NUM')
2919 ,po_line_id => get_number(p_query_id,'PO_LINE_ID')
2920 ,po_line_num => get_varchar2(p_query_id,'PO_LINE_NUM')
2921 --
2922 ,first_name => get_varchar2(p_query_id,'FIRST_NAME')
2923 ,full_name => get_varchar2(p_query_id,'FULL_NAME')
2924 ,title => get_varchar2(p_query_id,'TITLE')
2925 ,middle_names => get_varchar2(p_query_id,'MIDDLE_NAMES')
2926 ,nationality_meaning => get_varchar2(p_query_id,'NATIONALITY_MEANING')
2927 ,nationality => get_varchar2(p_query_id,'NATIONALITY')
2928 ,national_identifier => get_varchar2(p_query_id,'NATIONAL_IDENTIFIER')
2929 -- Bug 3037019
2930 ,registered_disabled_flag => get_varchar2(p_query_id,'REGISTERED_DISABLED_FLAG')
2931 ,registered_disabled => get_varchar2(p_query_id,'REGISTERED_DISABLED')
2932 ,sex_meaning => get_varchar2(p_query_id,'SEX_MEANING')
2933 ,sex => get_varchar2(p_query_id,'SEX')
2934 ,benefit_group => get_varchar2(p_query_id,'BENEFIT_GROUP')
2935 ,benefit_group_id => get_number(p_query_id,'BENEFIT_GROUP_ID')
2936 ,grade => get_varchar2(p_query_id,'GRADE')
2937 ,grade_id => get_number(p_query_id,'GRADE_ID')
2938 ,grade_ladder => get_varchar2(p_query_id,'GRADE_LADDER')
2939 ,grade_ladder_pgm_id => get_number(p_query_id,'GRADE_LADDER_PGM_ID')
2940 ,position => get_varchar2(p_query_id,'POSITION')
2941 ,position_id => get_number(p_query_id,'POSITION_ID')
2942 ,job => get_varchar2(p_query_id,'JOB')
2943 ,job_id => get_number(p_query_id,'JOB_ID')
2944 ,assignment_status_type => get_varchar2(p_query_id,'ASSIGNMENT_STATUS_TYPE')
2945 ,assignment_status_type_id => get_number(p_query_id,'ASSIGNMENT_STATUS_TYPE_ID')
2946 ,payroll => get_varchar2(p_query_id,'PAYROLL')
2947 ,payroll_id => get_number(p_query_id,'PAYROLL_ID')
2948 ,location => get_varchar2(p_query_id,'LOCATION')
2949 ,location_id => get_number(p_query_id,'LOCATION_ID')
2950 ,supervisor => get_varchar2(p_query_id,'SUPERVISOR')
2951 ,supervisor_id => get_number(p_query_id,'SUPERVISOR_ID')
2952 ,supervisor_assignment_number => get_varchar2(p_query_id,'SUPERVISOR_ASSIGNMENT_NUMBER')
2953 ,supervisor_assignment_id => get_number(p_query_id,'SUPERVISOR_ASSIGNMENT_ID')
2954 ,recruitment_activity => get_varchar2(p_query_id,'RECRUITMENT_ACTIVITY')
2955 ,recruitment_activity_id => get_number(p_query_id,'RECRUITMENT_ACTIVITY_ID')
2956 ,organization => get_varchar2(p_query_id,'ORGANIZATION')
2957 ,organization_id => get_number(p_query_id,'ORGANIZATION_ID')
2958 ,people_group => get_varchar2(p_query_id,'PEOPLE_GROUP')
2959 ,people_group_id => get_number(p_query_id,'PEOPLE_GROUP_ID')
2960 ,vacancy => get_varchar2(p_query_id,'VACANCY')
2961 ,vacancy_id => get_number(p_query_id,'VACANCY_ID')
2962 ,requisition => get_varchar2(p_query_id,'REQUISITION')
2963 ,requisition_id => get_number(p_query_id,'REQUISITION_ID')
2964 ,salary_basis => get_varchar2(p_query_id,'SALARY_BASIS')
2965 ,pay_basis_id => get_number(p_query_id,'PAY_BASIS_ID')
2966 ,bargaining_unit_code_meaning => get_varchar2(p_query_id,'BARGAINING_UNIT_CODE_MEANING')
2967 ,bargaining_unit_code => get_varchar2(p_query_id,'BARGAINING_UNIT_CODE')
2968 ,employment_category_meaning => get_varchar2(p_query_id,'EMPLOYMENT_CATEGORY_MEANING')
2969 ,employment_category => get_varchar2(p_query_id,'EMPLOYMENT_CATEGORY')
2970 ,establishment => get_varchar2(p_query_id,'ESTABLISHMENT')
2971 ,establishment_id => get_number(p_query_id,'ESTABLISHMENT_ID')
2972 ,projected_hire_date => get_date(p_query_id,'PROJECTED_HIRE_DATE')
2973 ,secure =>'Y'
2974 ,field1_name => get_varchar2(p_query_id,'FIELD1_NAME')
2975 ,field1_condition_code => get_varchar2(p_query_id,'FIELD1_CONDITION_CODE')
2976 ,field1_value => get_varchar2(p_query_id,'FIELD1_VALUE')
2977 ,field2_name => get_varchar2(p_query_id,'FIELD2_NAME')
2978 ,field2_condition_code => get_varchar2(p_query_id,'FIELD2_CONDITION_CODE')
2979 ,field2_value => get_varchar2(p_query_id,'FIELD2_VALUE')
2980 ,field3_name => get_varchar2(p_query_id,'FIELD3_NAME')
2981 ,field3_condition_code => get_varchar2(p_query_id,'FIELD3_CONDITION_CODE')
2982 ,field3_value => get_varchar2(p_query_id,'FIELD3_VALUE')
2983 ,field4_name => get_varchar2(p_query_id,'FIELD4_NAME')
2984 ,field4_condition_code => get_varchar2(p_query_id,'FIELD4_CONDITION_CODE')
2985 ,field4_value => get_varchar2(p_query_id,'FIELD4_VALUE')
2986 ,field5_name => get_varchar2(p_query_id,'FIELD5_NAME')
2987 ,field5_condition_code => get_varchar2(p_query_id,'FIELD5_CONDITION_CODE')
2988 ,field5_value => get_varchar2(p_query_id,'FIELD5_VALUE')
2989 ,p_customized_restriction_id => p_customized_restriction_id
2990 ,p_employees_allowed => p_employees_allowed
2991 ,p_applicants_allowed => p_applicants_allowed
2992 ,p_cwk_allowed => p_cwk_allowed
2993 ,p_fetch_details => FALSE
2994 ,select_stmt => l_select_stmt_per);
2995
2996 p_people_tab:=l_findtab;
2997
2998 end findretrieve;
2999
3000
3001 end per_qh_find_query;