[Home] [Help]
PACKAGE BODY: APPS.PER_QH_MAINTAIN_QUERY
Source
1 PACKAGE BODY per_qh_maintain_query as
2 /* $Header: peqhmntq.pkb 120.3.12000000.2 2007/02/23 06:55:02 ktithy noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' per_qh_maintain_query.';
7 --
8 procedure onerow
9 (out_rec IN OUT NOCOPY mainrec
10 ,p_person_id IN number
11 ,p_assignment_id IN number default null
12 ,p_effective_date IN date
13 ,p_template_name IN varchar2
14 ,p_block_name IN varchar2
15 ,p_legislation_code IN varchar2
16 ) is
17 --
18 --Added for PMxbg
19 cursor csr_bg_name
20 (p_person_id number,
21 p_effective_date date) is
22 select x.name
23 from per_business_groups x , per_all_people_f papf
24 where x.business_group_id = papf.business_group_id
25 and papf.person_id=p_person_id;
26 --
27 --Added for PMxbg
28 cursor csr_get_leg
29 (p_business_group_id number) is
30 select x.legislation_code
31 from per_business_groups x
32 where x.business_group_id=p_business_group_id;
33 --
34 cursor csr_template(p_legislation_code varchar2) is
35 select
36 hft.form_template_id
37 from hr_form_templates_b hft
38 , fnd_form ff
39 where
40 ff.form_name='PERWSQHM'
41 and ff.application_id=800
42 and ff.form_id=hft.form_id
43 and hft.template_name=p_template_name
44 and (hft.legislation_code = p_legislation_code
45 or hft.legislation_code is null);
46 --
47 l_form_template_id hr_form_templates_b.form_template_id%type;
48 --
49 l_default_value VARCHAR2(2000);
50 --
51 CURSOR csr_template_items
52 (p_form_template_id IN NUMBER
53 ,p_full_item_name IN VARCHAR2
54 )
55 IS
56 SELECT tim.template_item_id
57 ,itptl.default_value
58 FROM hr_template_items_b tim
59 , hr_form_items_b fim
60 , hr_item_properties_b itpb
61 , hr_item_properties_tl itptl
62 WHERE fim.full_item_name = p_full_item_name
63 AND tim.form_item_id = fim.form_item_id
64 AND tim.form_template_id = p_form_template_id
65 AND itpb.template_item_id (+) = tim.template_item_id
66 AND itptl.item_property_id (+) = itpb.item_property_id
67 AND itptl.language (+) = USERENV('LANG');
68
69 l_template_item csr_template_items%ROWTYPE;
70 --
71 CURSOR csr_template_item_contexts
72 (p_template_item_id IN NUMBER
73 ,p_emp_apl_flag IN VARCHAR2
74 )
75 IS
76 SELECT itptl.default_value
77 FROM hr_item_contexts icx
78 ,hr_item_properties_b itpb
79 ,hr_item_properties_tl itptl
80 ,hr_template_item_contexts_b tic
81 WHERE icx.segment1 = p_emp_apl_flag
82 AND icx.item_context_id = tic.item_context_id
83 AND tic.context_type = 'QH_PER_TYPE'
84 AND tic.template_item_id = p_template_item_id
85 AND itpb.template_item_context_id (+) = tic.template_item_context_id
86 AND itptl.item_property_id (+) = itpb.item_property_id
87 AND itptl.language (+) = USERENV('LANG');
88
89 l_template_item_context csr_template_item_contexts%ROWTYPE;
90 --
91 l_template_item_found BOOLEAN;
92
93 --
94 cursor csr_person_details
95 (p_person_id number
96 ,p_effective_date date) is
97
98 select *
99 from per_all_people_f
100 where person_id=p_person_id
101 and p_effective_date between effective_start_date and effective_end_date;
102 --
103 cursor csr_person_type
104 (p_person_type_id number) is
105 SELECT
106 ppttl.user_person_type,
107 ppt.system_person_type
108 FROM
109 per_person_types_tl ppttl,
110 per_person_types ppt
111 WHERE
112 ppt.person_type_id=p_person_type_id
113 and ppt.active_flag = 'Y'
114 and ppttl.person_type_id = ppt.person_type_id
115 and ppttl.language = userenv('LANG');
116 --
117 cursor csr_lang
118 (p_lang_code VARCHAR2) is
119 select description
120 from fnd_languages_vl
121 where language_code=p_lang_code;
122 --
123 cursor csr_benfts_grp
124 (p_benfts_grp_id NUMBER) is
125 select name
126 from ben_benfts_grp
127 where benfts_grp_id=p_benfts_grp_id;
128 --
129 cursor leg_lookup(p_type varchar2) is
130 select rule_type
131 from pay_legislative_field_info plfi
132 where plfi.field_name = p_type
133 and plfi.legislation_code = p_legislation_code;
134 --
135 l_rule_type pay_legislative_field_info.rule_type%type;
136 --
137 cursor csr_application
138 (p_person_id number
139 ,p_effective_date date) is
140 select * from per_applications
141 where person_id=p_person_id
142 and p_effective_date between date_received and nvl(date_end,p_effective_date);
143 --
144 -- Bug 3540524 Starts here
145 -- Description : Modified the query of the cursor csr_period_of_svc.
146 /*cursor csr_period_of_svc
147 (p_person_id number
148 ,p_effective_date date) is
149 select * from per_periods_of_service
150 where person_id=p_person_id
151 and p_effective_date between date_start and nvl(final_process_date,p_effective_date);
152 */
153 --
154 cursor csr_period_of_svc
155 (p_person_id number
156 ,p_effective_date date) is
157 select * from per_periods_of_service
158 where person_id=p_person_id
159 and date_start <= p_effective_date
160 order by date_start desc;
161 --
162 -- Bug 3540524 Ends here
163 --
164 --
165 -- Bug 3983662 Starts here
166 --
167 cursor csr_period_of_placement
168 (p_person_id number
169 ,p_effective_date date) is
170 select * from per_periods_of_placement
171 where person_id=p_person_id
172 and date_start <= p_effective_date
173 order by date_start desc;
174 /*and p_effective_date between date_start and nvl(actual_termination_date,p_effective_date);*/
175
176 -- Bug 3983662 Ends here
177 --
178 cursor csr_addresses
179 (p_person_id number
180 ,p_effective_date date) is
181 select *
182 from per_addresses
183 where person_id=p_person_id
184 and primary_flag='Y'
185 and p_effective_date between date_from and nvl(date_to,p_effective_date);
186 --
187 cursor csr_phone
188 (p_person_id number
189 ,p_type VARCHAR2
190 ,p_effective_date date) is
191 select 0,phone_id,date_from,date_to,phone_number,object_version_number
192 from per_phones
193 where parent_id=p_person_id
194 and parent_table='PER_ALL_PEOPLE_F'
195 and phone_type=p_type
196 and p_effective_date between date_from and nvl(date_to,p_effective_date)
197 UNION
198 select months_between(p_effective_date,date_to),phone_id,date_from,date_to,phone_number,object_version_number
199 from per_phones
200 where parent_id=p_person_id
201 and parent_table='PER_ALL_PEOPLE_F'
202 and phone_type=p_type
203 and p_effective_date>date_to
204 UNION
205 select months_between(date_from,p_effective_date),phone_id,date_from,date_to,phone_number,object_version_number
206 from per_phones
207 where parent_id=p_person_id
208 and parent_table='PER_ALL_PEOPLE_F'
209 and phone_type=p_type
210 and p_effective_date<date_from
211 order by 1;
212 --
213 type phn_typ is record (dummy number
214 ,phone_id per_phones.phone_id%type
215 ,date_from per_phones.date_from%type
216 ,date_to per_phones.date_to%type
217 ,phone_number per_phones.phone_number%type
218 ,object_version_number per_phones.object_version_number%type);
219 phn_rec phn_typ;
220 --
221 cursor csr_assignment_details
222 (p_assignment_id number
223 ,p_effective_date date) is
224 select *
225 from per_all_assignments_f
226 where assignment_id=p_assignment_id
227 and p_effective_date between effective_start_date and effective_end_date;
228 --
229 cursor csr_asg_status
230 (p_assignment_status_type_id number
231 ,p_business_group_id number) is
232 SELECT
233 nvl(atl.user_status ,stl.user_status),
234 nvl(a.per_system_status ,s.per_system_status)
235 FROM
236 per_ass_status_type_amends_tl atl,
237 per_ass_status_type_amends a,
238 per_assignment_status_types_tl stl,
239 per_assignment_status_types s
240 WHERE
241 s.assignment_status_type_id= p_assignment_status_type_id and
242 a.assignment_status_type_id (+) = s.assignment_status_type_id and
243 a.business_group_id (+) +0= p_business_group_id and
244 nvl(a.active_flag, s.active_flag) = 'Y' and
245 a.ass_status_type_amend_id = atl.ass_status_type_amend_id (+) and
246 decode(atl.language,null,'1',atl.language) = decode(atl.language,null,'1',userenv('LANG')) and
247 s.assignment_status_type_id = stl.assignment_status_type_id and
248 stl.language = userenv('LANG');
249 --
250 cursor csr_rec_activity
251 (p_recruitment_activity_id number) is
252 select name
253 from per_recruitment_activities
254 where recruitment_activity_id=p_recruitment_activity_id;
255 --
256 cursor csr_pgp_rec
257 (p_people_group_id number) is
258 select * from pay_people_groups
259 where people_group_id=p_people_group_id;
260 --
261 cursor csr_scl_rec
262 (p_soft_coding_keyflex_id number) is
263 select * from hr_soft_coding_keyflex
264 where soft_coding_keyflex_id=p_soft_coding_keyflex_id;
265 --
266 cursor csr_vacancy
267 (p_vacancy_id number) is
268 select vac.name
269 , rec.name
270 from per_requisitions rec
271 , per_vacancies vac
272 where vacancy_id=p_vacancy_id
273 and vac.requisition_id=rec.requisition_id;
274 --
275 cursor csr_ceiling_step
276 (p_special_ceiling_step_id number
277 ,p_effective_date date) is
278 select psp.spinal_point spinal_point
279 , count(*) step
280 from per_spinal_points psp
281 , per_spinal_points psp2
282 , per_spinal_point_steps_f psps
283 , per_spinal_point_steps_f psps2
284 where psp.spinal_point_id = psps.spinal_point_id
285 and psps.grade_spine_id = psps2.grade_spine_id
286 and psp2.spinal_point_id = psps2.spinal_point_id
287 and psps.step_id=p_special_ceiling_step_id
288 and psp.sequence >= psp2.sequence
289 and p_effective_date between psps.effective_start_date
290 and psps.effective_end_date
291 and p_effective_date between psps2.effective_start_date
292 and psps2.effective_end_date
293 group by psp.spinal_point
294 , psps.step_id
295 , psps.sequence
296 , psps.effective_start_date
297 , psps.effective_end_date
298 order by 2;
299 --
300 cursor csr_reference
301 (p_contract_id number
302 ,p_effective_date date) is
303 select reference
304 from per_contracts_f
305 where contract_id=p_contract_id
306 and p_effective_date between effective_start_date and effective_end_date;
307 --
308 cursor csr_collective_agr
309 (p_collective_agreement_id number) is
310 select name
311 from per_collective_agreements
312 where collective_agreement_id=p_collective_agreement_id;
313 --
314 cursor csr_cagr_flex_num
315 (p_id_flex_num number) is
316 select id_flex_structure_name
317 from fnd_id_flex_structures_vl
318 where id_flex_code= 'CAGR'
319 and application_id = 800
320 and id_flex_num=p_id_flex_num;
321 --
322 cursor csr_address_style
323 (p_style VARCHAR2) is
324 select descriptive_flex_context_name
325 from fnd_descr_flex_contexts_vl
326 where descriptive_flexfield_name='Address Structure'
327 and application_id=800
328 and descriptive_flex_context_code=p_style;
329 --
330 cursor csr_pay_proposal
331 (p_assignment_id number
332 ,p_effective_date date) is
333 select *
334 from per_pay_proposals p1
335 where p1.assignment_id=p_assignment_id
336 and p1.change_date=
337 (select max(p2.change_date)
338 from per_pay_proposals p2
339 where p2.assignment_id=p_assignment_id
340 and change_date<=p_effective_date);
341 --
342 cursor csr_deployment
343 (p_person_id number) is
344 select *
345 from per_deployment_factors
346 where person_id=p_person_id;
347 --
348 cursor reverse_lookup(p_meaning varchar2
349 ,p_lookup_type varchar2) is
350 select lookup_code
351 from hr_lookups
352 where lookup_type=p_lookup_type
353 and meaning=p_meaning
354 and enabled_flag='Y'
355 and p_effective_date between
356 nvl(start_date_active,p_effective_date) and nvl(end_date_active,p_effective_date);
357 --
358 cursor csr_checklist
359 (p_person_id number
360 ,p_item_code varchar2) is
361 select *
362 from per_checklist_items
363 where person_id=p_person_id
364 and item_code=p_item_code;
365 --
366 cursor csr_country
367 (p_territory_code varchar2) is
368 select territory_short_name
369 from fnd_territories_vl
370 where territory_code=p_territory_code;
371 --
372 cursor salary_basis(p_effective_date date
373 ,p_pay_basis_id number) is
374 SELECT ppb.name
375 , ppb.pay_basis
376 ,PET.INPUT_CURRENCY_CODE
377 , PIV.UOM
378 FROM PAY_ELEMENT_TYPES_F PET
379 , PAY_INPUT_VALUES_F PIV
380 , PER_PAY_BASES PPB
381 WHERE PPB.PAY_BASIS_ID=p_pay_basis_id
382 AND PPB.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
383 AND p_effective_date BETWEEN
384 PIV.EFFECTIVE_START_DATE AND
385 PIV.EFFECTIVE_END_DATE
386 AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
387 AND p_effective_date BETWEEN
388 PET.EFFECTIVE_START_DATE AND
389 PET.EFFECTIVE_END_DATE;
390 --
391 cursor csr_vendor(p_vendor_id in number) is
392 select vendor_name
393 from po_vendors pov
394 where pov.vendor_id = p_vendor_id;
395 --
396 cursor csr_vendor_site(p_vendor_site_id in number) is
397 select vendor_site_code
398 from po_vendor_sites_all
399 where vendor_site_id=p_vendor_site_id;
400 --
401 cursor csr_po_header(p_po_header_id in number) is
402 select segment1
403 from po_headers_all
404 where po_header_id = p_po_header_id;
405 --
406 cursor csr_po_line(p_po_line_id in number) is
407 select line_num
408 from po_lines_all
409 where po_line_id = p_po_line_id;
410 --
411 cursor csr_sob(p_set_of_books_id number) is
412 select sob.name, sob.chart_of_accounts_id
413 from gl_sets_of_books sob
414 where sob.set_of_books_id = p_set_of_books_id;
415 --
416 cursor csr_asg_rates(p_assignment_id number
417 ,p_effective_date date) is
418 select gr.grade_rule_id
419 ,pr.name rate_name
420 ,gr.rate_id
421 ,SUBSTR(hr_general.decode_lookup('RATE_BASIS',pr.rate_basis),1,80) rate_basis
422 ,fnd_asr.meaning asg_rate_type_name
423 ,gr.currency_code
424 ,f.name rate_currency
425 ,gr.value
426 ,gr.effective_start_date
427 ,gr.effective_end_date
428 ,gr.object_version_number
429 from pay_grade_rules_f gr
430 ,pay_rates pr
431 ,fnd_currencies_vl f
432 ,fnd_lookups fnd_asr
433 where gr.rate_type = 'A'
434 and gr.grade_or_spinal_point_id = p_assignment_id
435 and p_effective_date between gr.effective_start_date and gr.effective_end_date
436 and gr.rate_id = pr.rate_id
437 and gr.currency_code = f.currency_code
438 and fnd_asr.lookup_code(+)=pr.asg_rate_type
439 and fnd_asr.lookup_type(+)='PRICE DIFFERENTIALS';
440 --
441 l_rate_rec csr_asg_rates%rowtype;
442 --
443 l_uom pay_input_values_f.uom%type;
444 --
445 per_rec per_all_people_f%rowtype;
446 app_rec per_applications%rowtype;
447 pds_rec per_periods_of_service%rowtype;
448 pdp_rec per_periods_of_placement%rowtype;
449 addr_rec per_addresses%rowtype;
450 asg_rec per_all_assignments_f%rowtype;
451 pgp_rec pay_people_groups%rowtype;
452 scl_rec hr_soft_coding_keyflex%rowtype;
453 pyp_rec per_pay_proposals%rowtype;
454 dpf_rec per_deployment_factors%rowtype;
455 chk_rec per_checklist_items%rowtype;
456 --
457 l_end_of_time date;
458 l_legislation_code varchar2(150);
459 l_proc varchar2(72) := g_package||'onerow';
460 --
461 begin
462 hr_utility.set_location('Entering:'|| l_proc, 10);
463 --
464 l_end_of_time:=hr_api.g_eot;
465 --
466 -- get the person record
467 --
468 open csr_person_details(p_person_id => p_person_id
469 ,p_effective_date => p_effective_date);
470 fetch csr_person_details into per_rec;
471 if csr_person_details%notfound then
472 close csr_person_details;
473 out_rec.person_update_allowed:='FALSE';
474 out_rec.asg_update_allowed:='FALSE';
475 out_rec.tax_update_allowed:='FALSE';
476 else
477 close csr_person_details;
478 --
479 hr_utility.set_location(l_proc, 20);
480 --
481 -- set the output fields
482 --
483 out_rec.person_id :=per_rec.person_id;
484 out_rec.business_group_id :=per_rec.business_group_id;
485 out_rec.per_effective_start_date :=per_rec.effective_start_date;
486 out_rec.per_effective_end_date :=per_rec.effective_end_date;
487 --
488 if per_rec.effective_end_date=l_end_of_time then
489 out_rec.person_update_allowed:='TRUE';
490 else
491 out_rec.person_update_allowed:='FALSE';
492 end if;
493 --
494 --Added for PMxbg
495 open csr_get_leg(per_rec.business_group_id);
496 fetch csr_get_leg into l_legislation_code;
497 close csr_get_leg;
498 --
499 out_rec.legislation :=l_legislation_code;
500 --
501 hr_utility.set_location(l_proc, 30);
502 --
503 open csr_person_type(per_rec.person_type_id);
504 fetch csr_person_type into out_rec.person_type,out_rec.system_person_type;
505 close csr_person_type;
506 --
507 hr_utility.set_location(l_proc, 40);
508 --
509 out_rec.person_type_id :=per_rec.person_type_id;
510 out_rec.last_name :=per_rec.last_name;
511 out_rec.start_date :=per_rec.start_date;
512 out_rec.applicant_number :=per_rec.applicant_number;
513 out_rec.background_check_status :=per_rec.background_check_status;
514 out_rec.background_date_check :=per_rec.background_date_check;
515 out_rec.blood_type_meaning :=hr_reports.get_lookup_meaning('BLOOD_TYPE',per_rec.blood_type);
516 out_rec.blood_type :=per_rec.blood_type;
517 --
518 hr_utility.set_location(l_proc, 50);
519 --
520 if per_rec.person_id is not null then
521 open csr_bg_name(per_rec.person_id,p_effective_date);
522 fetch csr_bg_name into out_rec.business_group_name;
523 close csr_bg_name;
524 else
525 out_rec.business_group_name:=null;
526 end if;
527 --
528
529
530 if per_rec.correspondence_language is not null then
531 open csr_lang(per_rec.correspondence_language);
532 fetch csr_lang into out_rec.corr_lang_meaning;
533 close csr_lang;
534 end if;
535 --
536 hr_utility.set_location(l_proc, 60);
537 --
538 out_rec.correspondence_language :=per_rec.correspondence_language;
539 out_rec.current_applicant_flag :=per_rec.current_applicant_flag;
540 out_rec.current_emp_or_apl_flag :=per_rec.current_emp_or_apl_flag;
541 out_rec.current_employee_flag :=per_rec.current_employee_flag;
542 --CWK
543 out_rec.current_npw_flag :=per_rec.current_npw_flag;
544 out_rec.npw_number :=per_rec.npw_number;
545 out_rec.date_employee_data_verified :=per_rec.date_employee_data_verified;
546 out_rec.date_of_birth :=per_rec.date_of_birth;
547 out_rec.age:=floor(months_between(p_effective_date,per_rec.date_of_birth)/12);
548 out_rec.email_address :=per_rec.email_address;
549 out_rec.employee_number :=per_rec.employee_number;
550 out_rec.expnse_chk_send_addr_meaning :=hr_reports.get_lookup_meaning('HOME_OFFICE',per_rec.expense_check_send_to_address);
551 out_rec.expnse_check_send_to_address :=per_rec.expense_check_send_to_address;
552 out_rec.first_name :=per_rec.first_name;
553 out_rec.per_fte_capacity :=per_rec.fte_capacity;
554 --
555 hr_utility.set_location(l_proc, 70);
556 --
557 out_rec.full_name :=per_rec.full_name;
558 out_rec.hold_applicant_date_until :=per_rec.hold_applicant_date_until;
559 out_rec.honors :=per_rec.honors;
560 out_rec.internal_location :=per_rec.internal_location;
561 out_rec.known_as :=per_rec.known_as;
562 out_rec.last_medical_test_by :=per_rec.last_medical_test_by;
563 out_rec.last_medical_test_date :=per_rec.last_medical_test_date;
564 out_rec.mailstop :=per_rec.mailstop;
565 out_rec.marital_status_meaning :=hr_reports.get_lookup_meaning('MAR_STATUS',per_rec.marital_status);
566 out_rec.marital_status :=per_rec.marital_status;
567 out_rec.middle_names :=per_rec.middle_names;
568 out_rec.nationality_meaning :=hr_reports.get_lookup_meaning('NATIONALITY',per_rec.nationality);
569 out_rec.nationality :=per_rec.nationality;
570 out_rec.national_identifier :=per_rec.national_identifier;
571 out_rec.office_number :=per_rec.office_number;
572 out_rec.on_military_service :=per_rec.on_military_service;
573 --
574 hr_utility.set_location(l_proc, 80);
575 --
576 out_rec.pre_name_adjunct :=per_rec.pre_name_adjunct;
577 out_rec.previous_last_name :=per_rec.previous_last_name;
578 out_rec.rehire_recommendation :=per_rec.rehire_recommendation;
579 out_rec.rehire_reason :=per_rec.rehire_reason;
580 out_rec.resume_exists :=per_rec.resume_exists;
581 out_rec.resume_last_updated :=per_rec.resume_last_updated;
582 -- Bug 3037019
583 out_rec.registered_disabled_flag :=hr_reports.get_lookup_meaning('REGISTERED_DISABLED',per_rec.registered_disabled_flag);
584 out_rec.registered_disabled :=per_rec.registered_disabled_flag;
585 out_rec.second_passport_exists :=per_rec.second_passport_exists;
586 out_rec.sex_meaning :=hr_reports.get_lookup_meaning('SEX',per_rec.sex);
587 out_rec.sex :=per_rec.sex;
588 --
589 hr_utility.set_location(l_proc, 90);
590 --
591 out_rec.student_status_meaning :=hr_reports.get_lookup_meaning('STUDENT_STATUS',per_rec.student_status);
592 out_rec.student_status :=per_rec.student_status;
593 out_rec.suffix :=per_rec.suffix;
594 out_rec.title_meaning :=hr_reports.get_lookup_meaning('TITLE',per_rec.title);
595 out_rec.title :=per_rec.title;
596 out_rec.work_schedule_meaning :=hr_reports.get_lookup_meaning('WORK_SCHEDULE',per_rec.work_schedule);
597 out_rec.work_schedule :=per_rec.work_schedule;
598 out_rec.coord_ben_med_pln_no :=per_rec.coord_ben_med_pln_no;
599 out_rec.coord_ben_no_cvg_flag :=per_rec.coord_ben_no_cvg_flag;
600 out_rec.dpdnt_adoption_date :=per_rec.dpdnt_adoption_date;
601 out_rec.dpdnt_vlntry_svce_flag :=per_rec.dpdnt_vlntry_svce_flag;
602 out_rec.receipt_of_death_cert_date :=per_rec.receipt_of_death_cert_date;
603 out_rec.uses_tobacco_flag :=per_rec.uses_tobacco_flag;
604 --
605 hr_utility.set_location(l_proc, 100);
606 --
607 if per_rec.benefit_group_id is not null then
608 open csr_benfts_grp(per_rec.benefit_group_id);
609 fetch csr_benfts_grp into out_rec.benefit_group;
610 close csr_benfts_grp;
611 end if;
612 --
613 hr_utility.set_location(l_proc, 110);
614 --
615 out_rec.benefit_group_id :=per_rec.benefit_group_id;
616 out_rec.attribute_category :=per_rec.attribute_category;
617 out_rec.attribute1 :=per_rec.attribute1;
618 out_rec.attribute2 :=per_rec.attribute2;
619 out_rec.attribute3 :=per_rec.attribute3;
620 out_rec.attribute4 :=per_rec.attribute4;
621 out_rec.attribute5 :=per_rec.attribute5;
622 out_rec.attribute6 :=per_rec.attribute6;
623 out_rec.attribute7 :=per_rec.attribute7;
624 out_rec.attribute8 :=per_rec.attribute8;
625 out_rec.attribute9 :=per_rec.attribute9;
626 out_rec.attribute10 :=per_rec.attribute10;
627 out_rec.attribute11 :=per_rec.attribute11;
628 out_rec.attribute12 :=per_rec.attribute12;
629 out_rec.attribute13 :=per_rec.attribute13;
630 out_rec.attribute14 :=per_rec.attribute14;
631 out_rec.attribute15 :=per_rec.attribute15;
632 out_rec.attribute16 :=per_rec.attribute16;
633 out_rec.attribute17 :=per_rec.attribute17;
634 out_rec.attribute18 :=per_rec.attribute18;
635 out_rec.attribute19 :=per_rec.attribute19;
636 out_rec.attribute20 :=per_rec.attribute20;
637 out_rec.attribute21 :=per_rec.attribute21;
638 out_rec.attribute22 :=per_rec.attribute22;
639 out_rec.attribute23 :=per_rec.attribute23;
640 out_rec.attribute24 :=per_rec.attribute24;
641 out_rec.attribute25 :=per_rec.attribute25;
642 out_rec.attribute26 :=per_rec.attribute26;
643 out_rec.attribute27 :=per_rec.attribute27;
644 out_rec.attribute28 :=per_rec.attribute28;
645 out_rec.attribute29 :=per_rec.attribute29;
646 out_rec.attribute30 :=per_rec.attribute30;
647 out_rec.per_information_category :=per_rec.per_information_category;
648 out_rec.per_information1 :=per_rec.per_information1;
649 out_rec.per_information2 :=per_rec.per_information2;
650 out_rec.per_information3 :=per_rec.per_information3;
651 out_rec.per_information4 :=per_rec.per_information4;
652 out_rec.per_information5 :=per_rec.per_information5;
653 out_rec.per_information6 :=per_rec.per_information6;
654 out_rec.per_information7 :=per_rec.per_information7;
655 out_rec.per_information8 :=per_rec.per_information8;
656 out_rec.per_information9 :=per_rec.per_information9;
657 out_rec.per_information10 :=per_rec.per_information10;
658 out_rec.per_information11 :=per_rec.per_information11;
659 out_rec.per_information12 :=per_rec.per_information12;
660 out_rec.per_information13 :=per_rec.per_information13;
661 out_rec.per_information14 :=per_rec.per_information14;
662 out_rec.per_information15 :=per_rec.per_information15;
663 out_rec.per_information16 :=per_rec.per_information16;
664 out_rec.per_information17 :=per_rec.per_information17;
665 out_rec.per_information18 :=per_rec.per_information18;
666 out_rec.per_information19 :=per_rec.per_information19;
667 out_rec.per_information20 :=per_rec.per_information20;
668 out_rec.per_information21 :=per_rec.per_information21;
669 out_rec.per_information22 :=per_rec.per_information22;
670 out_rec.per_information23 :=per_rec.per_information23;
671 out_rec.per_information24 :=per_rec.per_information24;
672 out_rec.per_information25 :=per_rec.per_information25;
673 out_rec.per_information26 :=per_rec.per_information26;
674 out_rec.per_information27 :=per_rec.per_information27;
675 out_rec.per_information28 :=per_rec.per_information28;
676 out_rec.per_information29 :=per_rec.per_information29;
677 out_rec.per_information30 :=per_rec.per_information30;
678 out_rec.date_of_death :=per_rec.date_of_death;
679 out_rec.original_date_of_hire :=per_rec.original_date_of_hire;
680 --
681 out_rec.town_of_birth :=per_rec.town_of_birth;
682 open leg_lookup('TOWN_OF_BIRTH');
683 fetch leg_lookup into l_rule_type;
684 if leg_lookup%notfound then
685 close leg_lookup;
686 out_rec.town_of_birth_meaning:=per_rec.town_of_birth;
687 else
688 close leg_lookup;
689 out_rec.town_of_birth_meaning:=
690 hr_reports.get_lookup_meaning(l_rule_type,per_rec.town_of_birth);
691 end if;
692 --
693 out_rec.region_of_birth :=per_rec.region_of_birth;
694 open leg_lookup('REGION_OF_BIRTH');
695 fetch leg_lookup into l_rule_type;
696 if leg_lookup%notfound then
697 close leg_lookup;
698 out_rec.region_of_birth_meaning:=per_rec.region_of_birth;
699 else
700 close leg_lookup;
701 out_rec.region_of_birth_meaning:=
702 hr_reports.get_lookup_meaning(l_rule_type,per_rec.region_of_birth);
703 end if;
704 --
705 out_rec.country_of_birth :=per_rec.country_of_birth;
706 --
707 if per_rec.country_of_birth is not null then
708 open csr_country(per_rec.country_of_birth);
709 fetch csr_country into out_rec.country_of_birth_meaning;
710 close csr_country;
711 end if;
712 --
713 out_rec.per_object_version_number :=per_rec.object_version_number;
714 --
715 hr_utility.set_location(l_proc, 120);
716 --
717 if per_rec.current_applicant_flag='Y' then
718 --
719 hr_utility.set_location(l_proc, 130);
720 --
721 open csr_application(p_person_id,p_effective_date);
722 fetch csr_application into app_rec;
723 close csr_application;
724 --
725 hr_utility.set_location(l_proc, 140);
726 --
727 out_rec.application_id :=app_rec.application_id;
728 out_rec.projected_hire_date :=app_rec.projected_hire_date;
729 out_rec.appl_attribute_category :=app_rec.appl_attribute_category;
730 out_rec.appl_attribute1 :=app_rec.appl_attribute1;
731 out_rec.appl_attribute2 :=app_rec.appl_attribute2;
732 out_rec.appl_attribute3 :=app_rec.appl_attribute3;
733 out_rec.appl_attribute4 :=app_rec.appl_attribute4;
734 out_rec.appl_attribute5 :=app_rec.appl_attribute5;
735 out_rec.appl_attribute6 :=app_rec.appl_attribute6;
736 out_rec.appl_attribute7 :=app_rec.appl_attribute7;
737 out_rec.appl_attribute8 :=app_rec.appl_attribute8;
738 out_rec.appl_attribute9 :=app_rec.appl_attribute9;
739 out_rec.appl_attribute10 :=app_rec.appl_attribute10;
740 out_rec.appl_attribute11 :=app_rec.appl_attribute11;
741 out_rec.appl_attribute12 :=app_rec.appl_attribute12;
742 out_rec.appl_attribute13 :=app_rec.appl_attribute13;
743 out_rec.appl_attribute14 :=app_rec.appl_attribute14;
744 out_rec.appl_attribute15 :=app_rec.appl_attribute15;
745 out_rec.appl_attribute16 :=app_rec.appl_attribute16;
746 out_rec.appl_attribute17 :=app_rec.appl_attribute17;
747 out_rec.appl_attribute18 :=app_rec.appl_attribute18;
748 out_rec.appl_attribute19 :=app_rec.appl_attribute19;
749 out_rec.appl_attribute20 :=app_rec.appl_attribute20;
750 out_rec.current_employer :=app_rec.current_employer;
751 out_rec.successful_flag :=app_rec.successful_flag;
752 out_rec.termination_reason :=app_rec.termination_reason;
753 out_rec.termination_reason_meaning :=hr_reports.get_lookup_meaning('TERM_APL_REASON',app_rec.termination_reason);
754 out_rec.app_date_received :=app_rec.date_received;
755 out_rec.app_date_end :=app_rec.date_end;
756 out_rec.app_object_version_number :=app_rec.object_version_number;
757 end if;
758 --
759 hr_utility.set_location(l_proc, 150);
760 --
761 -- Bug 3540524 Starts Here
762 -- Description : Bring the data back to form even if the person is not an employee.
763 -- Here this restriction is blocking the Adjusted service date to propagate
764 -- to the form, if the person is not an Emplyee.Made necessary code changes
765 -- in the form to prevent an update of the Adjusted Service date, if the person
766 -- is not an employee.
767 -- if per_rec.current_employee_flag='Y' then
768 --
769 hr_utility.set_location(l_proc, 160);
770 --
771 open csr_period_of_svc(p_person_id,p_effective_date);
772 fetch csr_period_of_svc into pds_rec;
773 close csr_period_of_svc;
774 out_rec.period_of_service_id:=pds_rec.period_of_service_id;
775 out_rec.adjusted_svc_date:=pds_rec.adjusted_svc_date;
776 out_rec.employment_end_date:=pds_rec.final_process_date;
777 out_rec.actual_termination_date:=pds_rec.actual_termination_date;
778 out_rec.hire_date:=pds_rec.date_start;
779 out_rec.pds_object_version_number :=pds_rec.object_version_number;
780 -- end if; -- Bug 3540524
781 --
782 -- Bug 3540524 Ends Here
783 --
784 --CWK added
785 -- if per_rec.current_npw_flag='Y' then
786 --
787 -- The above checks were commented as part of 3540524 fix
788 -- This is not correct as this would not allow EX-CWK workers
789 -- to populate the employment_end_date.
790 --New check to verify the system_person_type is required as part of 5507008
791
792 if out_rec.SYSTEM_PERSON_TYPE in ('OTHER','APL') then
793 --
794 hr_utility.set_location(l_proc, 165);
795 --
796 open csr_period_of_placement(p_person_id,p_effective_date);
797 fetch csr_period_of_placement into pdp_rec;
798 close csr_period_of_placement;
799 out_rec.period_of_placement_date_start:=pdp_rec.date_start;
800 out_rec.pdp_object_version_number := pdp_rec.object_version_number;
801 --this will populate the employment_end_date
802 out_rec.employment_end_date:=pdp_rec.final_process_date;
803
804 end if;
805 -- end of fix 5507008
806 hr_utility.set_location(l_proc, 170);
807 --
808 hr_utility.set_location(l_proc, 180);
809 --
810 open csr_addresses(p_person_id,p_effective_date);
811 fetch csr_addresses into addr_rec;
812 if csr_addresses%found then
813 --
814 hr_utility.set_location(l_proc, 190);
815 --
816 close csr_addresses;
817 out_rec.address_id :=addr_rec.address_id;
818 out_rec.adr_date_from :=addr_rec.date_from;
819 out_rec.addr_primary_flag :=addr_rec.primary_flag;
820 --
821 open csr_address_style(addr_rec.style);
822 fetch csr_address_style into out_rec.style_meaning;
823 close csr_address_style;
824 --
825 hr_utility.set_location(l_proc, 200);
826 --
827 out_rec.style :=addr_rec.style;
828 out_rec.address_line1 :=addr_rec.address_line1;
829 out_rec.address_line2 :=addr_rec.address_line2;
830 out_rec.address_line3 :=addr_rec.address_line3;
831 out_rec.address_type_meaning :=hr_reports.get_lookup_meaning('ADDRESS_TYPE',addr_rec.address_type);
832 out_rec.address_type :=addr_rec.address_type;
833 out_rec.country :=addr_rec.country;
834 out_rec.adr_date_to :=addr_rec.date_to;
835 out_rec.postal_code :=addr_rec.postal_code;
836 out_rec.region_1 :=addr_rec.region_1;
837 out_rec.region_2 :=addr_rec.region_2;
838 out_rec.region_3 :=addr_rec.region_3;
839 out_rec.town_or_city :=addr_rec.town_or_city;
840 out_rec.telephone_number_1 :=addr_rec.telephone_number_1;
841 out_rec.telephone_number_2 :=addr_rec.telephone_number_2;
842 out_rec.telephone_number_3 :=addr_rec.telephone_number_3;
843 out_rec.add_information13 :=addr_rec.add_information13;
844 out_rec.add_information14 :=addr_rec.add_information14;
845 out_rec.add_information15 :=addr_rec.add_information15;
846 out_rec.add_information16 :=addr_rec.add_information16;
847 out_rec.add_information17 :=addr_rec.add_information17;
848 out_rec.add_information18 :=addr_rec.add_information18;
849 out_rec.add_information19 :=addr_rec.add_information19;
850 out_rec.add_information20 :=addr_rec.add_information20;
851 out_rec.addr_attribute_category :=addr_rec.addr_attribute_category;
852 out_rec.addr_attribute1 :=addr_rec.addr_attribute1;
853 out_rec.addr_attribute2 :=addr_rec.addr_attribute2;
854 out_rec.addr_attribute3 :=addr_rec.addr_attribute3;
855 out_rec.addr_attribute4 :=addr_rec.addr_attribute4;
856 out_rec.addr_attribute5 :=addr_rec.addr_attribute5;
857 out_rec.addr_attribute6 :=addr_rec.addr_attribute6;
858 out_rec.addr_attribute7 :=addr_rec.addr_attribute7;
859 out_rec.addr_attribute8 :=addr_rec.addr_attribute8;
860 out_rec.addr_attribute9 :=addr_rec.addr_attribute9;
861 out_rec.addr_attribute10 :=addr_rec.addr_attribute10;
862 out_rec.addr_attribute11 :=addr_rec.addr_attribute11;
863 out_rec.addr_attribute12 :=addr_rec.addr_attribute12;
864 out_rec.addr_attribute13 :=addr_rec.addr_attribute13;
865 out_rec.addr_attribute14 :=addr_rec.addr_attribute14;
866 out_rec.addr_attribute15 :=addr_rec.addr_attribute15;
867 out_rec.addr_attribute16 :=addr_rec.addr_attribute16;
868 out_rec.addr_attribute17 :=addr_rec.addr_attribute17;
869 out_rec.addr_attribute18 :=addr_rec.addr_attribute18;
870 out_rec.addr_attribute19 :=addr_rec.addr_attribute19;
871 out_rec.addr_attribute20 :=addr_rec.addr_attribute20;
872 out_rec.addr_object_version_number :=addr_rec.object_version_number;
873 --
874 hr_utility.set_location(l_proc, 240);
875 else
876 close csr_addresses;
877 end if;
878 --
879 hr_utility.set_location(l_proc, 250);
880 --
881 open csr_phone(p_person_id,'H1',p_effective_date);
882 fetch csr_phone into phn_rec;
883 if csr_phone%found then
884 --
885 hr_utility.set_location(l_proc, 260);
886 --
887 close csr_phone;
888 out_rec.phn_h_phone_id :=phn_rec.phone_id;
889 out_rec.phn_h_date_from :=phn_rec.date_from;
890 out_rec.phn_h_date_to :=phn_rec.date_to;
891 out_rec.phn_h_phone_number :=phn_rec.phone_number;
892 out_rec.phn_h_object_version_number :=phn_rec.object_version_number;
893 else
894 close csr_phone;
895 end if;
896 --
897 hr_utility.set_location(l_proc, 270);
898 --
899 open csr_phone(p_person_id,'W1',p_effective_date);
900 fetch csr_phone into phn_rec;
901 if csr_phone%found then
902 --
903 hr_utility.set_location(l_proc, 280);
904 --
905 close csr_phone;
906 out_rec.phn_w_phone_id :=phn_rec.phone_id;
907 out_rec.phn_w_date_from :=phn_rec.date_from;
908 out_rec.phn_w_date_to :=phn_rec.date_to;
909 out_rec.phn_w_phone_number :=phn_rec.phone_number;
910 out_rec.phn_w_object_version_number :=phn_rec.object_version_number;
911 else
912 close csr_phone;
913 end if;
914 --
915 hr_utility.set_location(l_proc, 290);
916 --
917 open csr_phone(p_person_id,'M',p_effective_date);
918 fetch csr_phone into phn_rec;
919 if csr_phone%found then
920 --
921 hr_utility.set_location(l_proc, 300);
922 --
923 close csr_phone;
924 out_rec.phn_m_phone_id :=phn_rec.phone_id;
925 out_rec.phn_m_date_from :=phn_rec.date_from;
926 out_rec.phn_m_date_to :=phn_rec.date_to;
927 out_rec.phn_m_phone_number :=phn_rec.phone_number;
928 out_rec.phn_m_object_version_number :=phn_rec.object_version_number;
929 else
930 close csr_phone;
931 end if;
932 --
933 hr_utility.set_location(l_proc, 310);
934 --
935 open csr_phone(p_person_id,'HF',p_effective_date);
936 fetch csr_phone into phn_rec;
937 if csr_phone%found then
938 --
939 hr_utility.set_location(l_proc, 320);
940 --
941 close csr_phone;
942 out_rec.phn_hf_phone_id :=phn_rec.phone_id;
943 out_rec.phn_hf_date_from :=phn_rec.date_from;
944 out_rec.phn_hf_date_to :=phn_rec.date_to;
945 out_rec.phn_hf_phone_number :=phn_rec.phone_number;
946 out_rec.phn_hf_object_version_number :=phn_rec.object_version_number;
947 else
948 close csr_phone;
949 end if;
950 --
951 hr_utility.set_location(l_proc, 330);
952 --
953 open csr_phone(p_person_id,'WF',p_effective_date);
954 fetch csr_phone into phn_rec;
955 if csr_phone%found then
956 --
957 hr_utility.set_location(l_proc, 340);
958 --
959 close csr_phone;
960 out_rec.phn_wf_phone_id :=phn_rec.phone_id;
961 out_rec.phn_wf_date_from :=phn_rec.date_from;
962 out_rec.phn_wf_date_to :=phn_rec.date_to;
963 out_rec.phn_wf_phone_number :=phn_rec.phone_number;
964 out_rec.phn_wf_object_version_number :=phn_rec.object_version_number;
965 else
966 close csr_phone;
967 end if;
968 --
969 hr_utility.set_location(l_proc, 350);
970 --
971 open csr_deployment(p_person_id);
972 fetch csr_deployment into dpf_rec;
973 if csr_deployment%found then
974 --
975 hr_utility.set_location(l_proc, 360);
976 --
977 out_rec.deployment_factor_id :=dpf_rec.deployment_factor_id;
978 out_rec.work_any_country :=dpf_rec.work_any_country;
979 out_rec.work_any_location :=dpf_rec.work_any_location;
980 out_rec.relocate_domestically :=dpf_rec.relocate_domestically;
981 out_rec.relocate_internationally :=dpf_rec.relocate_internationally;
982 out_rec.travel_required :=dpf_rec.travel_required;
983 --
984 hr_utility.set_location(l_proc, 370);
985 --
986 if dpf_rec.country1 is not null then
987 --
988 hr_utility.set_location(l_proc, 380);
989 --
990 open csr_country(dpf_rec.country1);
991 fetch csr_country into out_rec.country1_meaning;
992 close csr_country;
993 end if;
994 out_rec.country1 :=dpf_rec.country1;
995 --
996 if dpf_rec.country2 is not null then
997 --
998 hr_utility.set_location(l_proc, 390);
999 --
1000 open csr_country(dpf_rec.country2);
1001 fetch csr_country into out_rec.country2_meaning;
1002 close csr_country;
1003 end if;
1004 out_rec.country2 :=dpf_rec.country2;
1005 --
1006 if dpf_rec.country3 is not null then
1007 --
1008 hr_utility.set_location(l_proc, 400);
1009 --
1010 open csr_country(dpf_rec.country3);
1011 fetch csr_country into out_rec.country3_meaning;
1012 close csr_country;
1013 end if;
1014 out_rec.country3 :=dpf_rec.country3;
1015 --
1016 hr_utility.set_location(l_proc, 410);
1017 --
1018 out_rec.dpf_work_duration_meaning :=hr_reports.get_lookup_meaning('PER_TIME_SCALES',dpf_rec.work_duration);
1019 out_rec.dpf_work_duration :=dpf_rec.work_duration;
1020 out_rec.dpf_work_schedule_meaning :=hr_reports.get_lookup_meaning('PER_WORK_SCHEDULE',dpf_rec.work_schedule);
1021 out_rec.dpf_work_schedule :=dpf_rec.work_schedule;
1022 out_rec.dpf_work_hours_meaning :=hr_reports.get_lookup_meaning('PER_WORK_HOURS',dpf_rec.work_hours);
1023 out_rec.dpf_work_hours :=dpf_rec.work_hours;
1024 out_rec.dpf_fte_capacity_meaning :=hr_reports.get_lookup_meaning('PER_FTE_CAPACITY',dpf_rec.fte_capacity);
1025 out_rec.dpf_fte_capacity :=dpf_rec.fte_capacity;
1026 out_rec.visit_internationally :=dpf_rec.visit_internationally;
1027 out_rec.only_current_location :=dpf_rec.only_current_location;
1028 --
1029 hr_utility.set_location(l_proc, 420);
1030 --
1031 if dpf_rec.no_country1 is not null then
1032 --
1033 hr_utility.set_location(l_proc, 430);
1034 --
1035 open csr_country(dpf_rec.no_country1);
1036 fetch csr_country into out_rec.no_country1_meaning;
1037 close csr_country;
1038 end if;
1039 out_rec.no_country1 :=dpf_rec.no_country1;
1040 --
1041 if dpf_rec.no_country2 is not null then
1042 --
1043 hr_utility.set_location(l_proc, 440);
1044 --
1045 open csr_country(dpf_rec.no_country2);
1046 fetch csr_country into out_rec.no_country2_meaning;
1047 close csr_country;
1048 end if;
1049 out_rec.no_country2 :=dpf_rec.no_country2;
1050 --
1051 if dpf_rec.no_country3 is not null then
1052 --
1053 hr_utility.set_location(l_proc, 450);
1054 --
1055 open csr_country(dpf_rec.no_country3);
1056 fetch csr_country into out_rec.no_country3_meaning;
1057 close csr_country;
1058 end if;
1059 out_rec.no_country3 :=dpf_rec.no_country3;
1060 --
1061 --
1062 hr_utility.set_location(l_proc, 460);
1063 --
1064 out_rec.earliest_available_date :=dpf_rec.earliest_available_date;
1065 out_rec.available_for_transfer :=dpf_rec.available_for_transfer;
1066 out_rec.relocation_pref_meaning :=hr_reports.get_lookup_meaning('PER_RELOCATION_PREFERENCES',dpf_rec.relocation_preference);
1067 out_rec.relocation_preference :=dpf_rec.relocation_preference;
1068 out_rec.dpf_object_version_number :=dpf_rec.object_version_number;
1069 out_rec.dpf_attribute_category :=dpf_rec.attribute_category;
1070 out_rec.dpf_attribute1 :=dpf_rec.attribute1;
1071 out_rec.dpf_attribute2 :=dpf_rec.attribute2;
1072 out_rec.dpf_attribute3 :=dpf_rec.attribute3;
1073 out_rec.dpf_attribute4 :=dpf_rec.attribute4;
1074 out_rec.dpf_attribute5 :=dpf_rec.attribute5;
1075 out_rec.dpf_attribute6 :=dpf_rec.attribute6;
1076 out_rec.dpf_attribute7 :=dpf_rec.attribute7;
1077 out_rec.dpf_attribute8 :=dpf_rec.attribute8;
1078 out_rec.dpf_attribute9 :=dpf_rec.attribute9;
1079 out_rec.dpf_attribute10 :=dpf_rec.attribute10;
1080 out_rec.dpf_attribute11 :=dpf_rec.attribute11;
1081 out_rec.dpf_attribute12 :=dpf_rec.attribute12;
1082 out_rec.dpf_attribute13 :=dpf_rec.attribute13;
1083 out_rec.dpf_attribute14 :=dpf_rec.attribute14;
1084 out_rec.dpf_attribute15 :=dpf_rec.attribute15;
1085 out_rec.dpf_attribute16 :=dpf_rec.attribute16;
1086 out_rec.dpf_attribute17 :=dpf_rec.attribute17;
1087 out_rec.dpf_attribute18 :=dpf_rec.attribute18;
1088 out_rec.dpf_attribute19 :=dpf_rec.attribute19;
1089 out_rec.dpf_attribute20 :=dpf_rec.attribute20;
1090 --
1091 hr_utility.set_location(l_proc, 470);
1092 --
1093 else
1094 close csr_deployment;
1095 end if;
1096 --
1097 open csr_template(per_per_bus.return_legislation_code(p_person_id));
1098 fetch csr_template into l_form_template_id;
1099 close csr_template;
1100 --
1101 hr_utility.set_location(l_proc, 475);
1102 --
1103 open csr_template_items
1104 (p_form_template_id => l_form_template_id
1105 ,p_full_item_name => p_block_name||'.CHK1_ITEM_CODE_MEANING'
1106 );
1107 fetch csr_template_items into l_template_item;
1108 --
1109 IF (csr_template_items%found) THEN
1110 hr_utility.set_location(l_proc, 480);
1111 close csr_template_items;
1112 open csr_template_item_contexts
1113 (p_template_item_id => l_template_item.template_item_id
1114 ,p_emp_apl_flag => out_rec.person_type
1115 );
1116 fetch csr_template_item_contexts INTO l_template_item_context;
1117 IF (csr_template_item_contexts%FOUND)
1118 THEN
1119 l_default_value := l_template_item_context.default_value;
1120 ELSE
1121 l_default_value := l_template_item.default_value;
1122 END IF;
1123 CLOSE csr_template_item_contexts;
1124 --
1125 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1126 fetch reverse_lookup into chk_rec.item_code;
1127 if reverse_lookup%found then
1128 hr_utility.set_location(l_proc, 485);
1129 close reverse_lookup;
1130 out_rec.chk1_item_code_meaning:=l_default_value;
1131 out_rec.chk1_item_code :=chk_rec.item_code;
1132 --
1133 open csr_checklist(p_person_id, chk_rec.item_code);
1134 fetch csr_checklist into chk_rec;
1135 if csr_checklist%found then
1136 --
1137 hr_utility.set_location(l_proc, 490);
1138 --
1139 close csr_checklist;
1140 out_rec.chk1_checklist_item_id :=chk_rec.checklist_item_id;
1141 out_rec.chk1_date_due :=chk_rec.date_due;
1142 out_rec.chk1_date_done :=chk_rec.date_done;
1143 out_rec.chk1_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1144 out_rec.chk1_status :=chk_rec.status;
1145 out_rec.chk1_notes :=chk_rec.notes;
1146 out_rec.chk1_object_version_number :=chk_rec.object_version_number;
1147 --
1148 hr_utility.set_location(l_proc,495);
1149 --
1150 else
1151 close csr_checklist;
1152 end if;
1153 else
1154 hr_utility.set_location(l_proc,500);
1155 close reverse_lookup;
1156 out_rec.chk1_item_code_meaning:=null;
1157 out_rec.chk1_item_code :=null;
1158 end if;
1159
1160 else
1161 close csr_template_items;
1162 END IF;
1163 --
1164 hr_utility.set_location(l_proc, 505);
1165 open csr_template_items
1166 (p_form_template_id => l_form_template_id
1167 ,p_full_item_name => p_block_name||'.CHK2_ITEM_CODE_MEANING'
1168 );
1169 fetch csr_template_items into l_template_item;
1170 --
1171 IF (csr_template_items%found) THEN
1172 hr_utility.set_location(l_proc, 510);
1173 close csr_template_items;
1174 open csr_template_item_contexts
1175 (p_template_item_id => l_template_item.template_item_id
1176 ,p_emp_apl_flag => out_rec.person_type
1177 );
1178 fetch csr_template_item_contexts INTO l_template_item_context;
1179 IF (csr_template_item_contexts%FOUND)
1180 THEN
1181 l_default_value := l_template_item_context.default_value;
1182 ELSE
1183 l_default_value := l_template_item.default_value;
1184 END IF;
1185 CLOSE csr_template_item_contexts;
1186 --
1187 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1188 fetch reverse_lookup into chk_rec.item_code;
1189 if reverse_lookup%found then
1190 hr_utility.set_location(l_proc, 515);
1191 close reverse_lookup;
1192 out_rec.chk2_item_code_meaning:=l_default_value;
1193 out_rec.chk2_item_code :=chk_rec.item_code;
1194 --
1195 open csr_checklist(p_person_id, chk_rec.item_code);
1196 fetch csr_checklist into chk_rec;
1197 if csr_checklist%found then
1198 --
1199 hr_utility.set_location(l_proc, 520);
1200 --
1201 close csr_checklist;
1202 out_rec.chk2_checklist_item_id :=chk_rec.checklist_item_id;
1203 out_rec.chk2_date_due :=chk_rec.date_due;
1204 out_rec.chk2_date_done :=chk_rec.date_done;
1205 out_rec.chk2_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1206 out_rec.chk2_status :=chk_rec.status;
1207 out_rec.chk2_notes :=chk_rec.notes;
1208 out_rec.chk2_object_version_number :=chk_rec.object_version_number;
1209 --
1210 hr_utility.set_location(l_proc,525);
1211 --
1212 else
1213 close csr_checklist;
1214 end if;
1215 else
1216 hr_utility.set_location(l_proc,530);
1217 close reverse_lookup;
1218 out_rec.chk2_item_code_meaning:=null;
1219 out_rec.chk2_item_code :=null;
1220 end if;
1221
1222 else
1223 close csr_template_items;
1224 END IF;
1225 --
1226 hr_utility.set_location(l_proc,535);
1227 --
1228 open csr_template_items
1229 (p_form_template_id => l_form_template_id
1230 ,p_full_item_name => p_block_name||'.CHK3_ITEM_CODE_MEANING'
1231 );
1232 fetch csr_template_items into l_template_item;
1233 --
1234 IF (csr_template_items%found) THEN
1235 hr_utility.set_location(l_proc, 540);
1236 close csr_template_items;
1237 open csr_template_item_contexts
1238 (p_template_item_id => l_template_item.template_item_id
1239 ,p_emp_apl_flag => out_rec.person_type
1240 );
1241 fetch csr_template_item_contexts INTO l_template_item_context;
1242 IF (csr_template_item_contexts%FOUND)
1243 THEN
1244 l_default_value := l_template_item_context.default_value;
1245 ELSE
1246 l_default_value := l_template_item.default_value;
1247 END IF;
1248 CLOSE csr_template_item_contexts;
1249 --
1250 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1251 fetch reverse_lookup into chk_rec.item_code;
1252 if reverse_lookup%found then
1253 hr_utility.set_location(l_proc, 545);
1254 close reverse_lookup;
1255 out_rec.chk3_item_code_meaning:=l_default_value;
1256 out_rec.chk3_item_code :=chk_rec.item_code;
1257 --
1258 open csr_checklist(p_person_id, chk_rec.item_code);
1259 fetch csr_checklist into chk_rec;
1260 if csr_checklist%found then
1261 --
1262 hr_utility.set_location(l_proc, 550);
1263 --
1264 close csr_checklist;
1265 out_rec.chk3_checklist_item_id :=chk_rec.checklist_item_id;
1266 out_rec.chk3_date_due :=chk_rec.date_due;
1267 out_rec.chk3_date_done :=chk_rec.date_done;
1268 out_rec.chk3_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1269 out_rec.chk3_status :=chk_rec.status;
1270 out_rec.chk3_notes :=chk_rec.notes;
1271 out_rec.chk3_object_version_number :=chk_rec.object_version_number;
1272 --
1273 hr_utility.set_location(l_proc,555);
1274 --
1275 else
1276 close csr_checklist;
1277 end if;
1278 else
1279 hr_utility.set_location(l_proc,560);
1280 close reverse_lookup;
1281 out_rec.chk3_item_code_meaning:=null;
1282 out_rec.chk3_item_code :=null;
1283 end if;
1284
1285 else
1286 close csr_template_items;
1287 END IF;
1288 --
1289 hr_utility.set_location(l_proc,565);
1290 --
1291 open csr_template_items
1292 (p_form_template_id => l_form_template_id
1293 ,p_full_item_name => p_block_name||'.CHK4_ITEM_CODE_MEANING'
1294 );
1295 fetch csr_template_items into l_template_item;
1296 --
1297 IF (csr_template_items%found) THEN
1298 hr_utility.set_location(l_proc, 570);
1299 close csr_template_items;
1300 open csr_template_item_contexts
1301 (p_template_item_id => l_template_item.template_item_id
1302 ,p_emp_apl_flag => out_rec.person_type
1303 );
1304 fetch csr_template_item_contexts INTO l_template_item_context;
1305 IF (csr_template_item_contexts%FOUND)
1306 THEN
1307 l_default_value := l_template_item_context.default_value;
1308 ELSE
1309 l_default_value := l_template_item.default_value;
1310 END IF;
1311 CLOSE csr_template_item_contexts;
1312 --
1313 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1314 fetch reverse_lookup into chk_rec.item_code;
1315 if reverse_lookup%found then
1316 hr_utility.set_location(l_proc, 575);
1317 close reverse_lookup;
1318 out_rec.chk4_item_code_meaning:=l_default_value;
1319 out_rec.chk4_item_code :=chk_rec.item_code;
1320 --
1321 open csr_checklist(p_person_id, chk_rec.item_code);
1322 fetch csr_checklist into chk_rec;
1323 if csr_checklist%found then
1324 --
1325 hr_utility.set_location(l_proc, 580);
1326 --
1327 close csr_checklist;
1328 out_rec.chk4_checklist_item_id :=chk_rec.checklist_item_id;
1329 out_rec.chk4_date_due :=chk_rec.date_due;
1330 out_rec.chk4_date_done :=chk_rec.date_done;
1331 out_rec.chk4_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1332 out_rec.chk4_status :=chk_rec.status;
1333 out_rec.chk4_notes :=chk_rec.notes;
1334 out_rec.chk4_object_version_number :=chk_rec.object_version_number;
1335 --
1336 hr_utility.set_location(l_proc,585);
1337 --
1338 else
1339 close csr_checklist;
1340 end if;
1341 else
1342 hr_utility.set_location(l_proc,590);
1343 close reverse_lookup;
1344 out_rec.chk4_item_code_meaning:=null;
1345 out_rec.chk4_item_code :=null;
1346 end if;
1347
1348 else
1349 close csr_template_items;
1350 END IF;
1351 --
1352 hr_utility.set_location(l_proc,595);
1353 --
1354 open csr_template_items
1355 (p_form_template_id => l_form_template_id
1356 ,p_full_item_name => p_block_name||'.CHK5_ITEM_CODE_MEANING'
1357 );
1358 fetch csr_template_items into l_template_item;
1359 --
1360 IF (csr_template_items%found) THEN
1361 hr_utility.set_location(l_proc, 600);
1362 close csr_template_items;
1363 open csr_template_item_contexts
1364 (p_template_item_id => l_template_item.template_item_id
1365 ,p_emp_apl_flag => out_rec.person_type
1366 );
1367 fetch csr_template_item_contexts INTO l_template_item_context;
1368 IF (csr_template_item_contexts%FOUND)
1369 THEN
1370 l_default_value := l_template_item_context.default_value;
1371 ELSE
1372 l_default_value := l_template_item.default_value;
1373 END IF;
1374 CLOSE csr_template_item_contexts;
1375 --
1376 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1377 fetch reverse_lookup into chk_rec.item_code;
1378 if reverse_lookup%found then
1379 hr_utility.set_location(l_proc, 605);
1380 close reverse_lookup;
1381 out_rec.chk5_item_code_meaning:=l_default_value;
1382 out_rec.chk5_item_code :=chk_rec.item_code;
1383 --
1384 open csr_checklist(p_person_id, chk_rec.item_code);
1385 fetch csr_checklist into chk_rec;
1386 if csr_checklist%found then
1387 --
1388 hr_utility.set_location(l_proc, 610);
1389 --
1390 close csr_checklist;
1391 out_rec.chk5_checklist_item_id :=chk_rec.checklist_item_id;
1392 out_rec.chk5_date_due :=chk_rec.date_due;
1393 out_rec.chk5_date_done :=chk_rec.date_done;
1394 out_rec.chk5_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1395 out_rec.chk5_status :=chk_rec.status;
1396 out_rec.chk5_notes :=chk_rec.notes;
1397 out_rec.chk5_object_version_number :=chk_rec.object_version_number;
1398 --
1399 hr_utility.set_location(l_proc,615);
1400 --
1401 else
1402 close csr_checklist;
1403 end if;
1404 else
1405 hr_utility.set_location(l_proc,620);
1406 close reverse_lookup;
1407 out_rec.chk5_item_code_meaning:=null;
1408 out_rec.chk5_item_code :=null;
1409 end if;
1410
1411 else
1412 close csr_template_items;
1413 END IF;
1414 --
1415 hr_utility.set_location(l_proc,625);
1416 --
1417 open csr_template_items
1418 (p_form_template_id => l_form_template_id
1419 ,p_full_item_name => p_block_name||'.CHK6_ITEM_CODE_MEANING'
1420 );
1421 fetch csr_template_items into l_template_item;
1422 --
1423 IF (csr_template_items%found) THEN
1424 hr_utility.set_location(l_proc, 630);
1425 close csr_template_items;
1426 open csr_template_item_contexts
1427 (p_template_item_id => l_template_item.template_item_id
1428 ,p_emp_apl_flag => out_rec.person_type
1429 );
1430 fetch csr_template_item_contexts INTO l_template_item_context;
1431 IF (csr_template_item_contexts%FOUND)
1432 THEN
1433 l_default_value := l_template_item_context.default_value;
1434 ELSE
1435 l_default_value := l_template_item.default_value;
1436 END IF;
1437 CLOSE csr_template_item_contexts;
1438 --
1439 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1440 fetch reverse_lookup into chk_rec.item_code;
1441 if reverse_lookup%found then
1442 hr_utility.set_location(l_proc, 635);
1443 close reverse_lookup;
1444 out_rec.chk6_item_code_meaning:=l_default_value;
1445 out_rec.chk6_item_code :=chk_rec.item_code;
1446 --
1447 open csr_checklist(p_person_id, chk_rec.item_code);
1448 fetch csr_checklist into chk_rec;
1449 if csr_checklist%found then
1450 --
1451 hr_utility.set_location(l_proc, 640);
1452 --
1453 close csr_checklist;
1454 out_rec.chk6_checklist_item_id :=chk_rec.checklist_item_id;
1455 out_rec.chk6_date_due :=chk_rec.date_due;
1456 out_rec.chk6_date_done :=chk_rec.date_done;
1457 out_rec.chk6_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1458 out_rec.chk6_status :=chk_rec.status;
1459 out_rec.chk6_notes :=chk_rec.notes;
1460 out_rec.chk6_object_version_number :=chk_rec.object_version_number;
1461 --
1462 hr_utility.set_location(l_proc,645);
1463 --
1464 else
1465 close csr_checklist;
1466 end if;
1467 else
1468 hr_utility.set_location(l_proc,650);
1469 close reverse_lookup;
1470 out_rec.chk6_item_code_meaning:=null;
1471 out_rec.chk6_item_code :=null;
1472 end if;
1473
1474 else
1475 close csr_template_items;
1476 END IF;
1477 --
1478 hr_utility.set_location(l_proc,655);
1479 --
1480 open csr_template_items
1481 (p_form_template_id => l_form_template_id
1482 ,p_full_item_name => p_block_name||'.CHK7_ITEM_CODE_MEANING'
1483 );
1484 fetch csr_template_items into l_template_item;
1485 --
1486 IF (csr_template_items%found) THEN
1487 hr_utility.set_location(l_proc, 660);
1488 close csr_template_items;
1489 open csr_template_item_contexts
1490 (p_template_item_id => l_template_item.template_item_id
1491 ,p_emp_apl_flag => out_rec.person_type
1492 );
1493 fetch csr_template_item_contexts INTO l_template_item_context;
1494 IF (csr_template_item_contexts%FOUND)
1495 THEN
1496 l_default_value := l_template_item_context.default_value;
1497 ELSE
1498 l_default_value := l_template_item.default_value;
1499 END IF;
1500 CLOSE csr_template_item_contexts;
1501 --
1502 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1503 fetch reverse_lookup into chk_rec.item_code;
1504 if reverse_lookup%found then
1505 hr_utility.set_location(l_proc, 665);
1506 close reverse_lookup;
1507 out_rec.chk7_item_code_meaning:=l_default_value;
1508 out_rec.chk7_item_code :=chk_rec.item_code;
1509 --
1510 open csr_checklist(p_person_id, chk_rec.item_code);
1511 fetch csr_checklist into chk_rec;
1512 if csr_checklist%found then
1513 --
1514 hr_utility.set_location(l_proc, 670);
1515 --
1516 close csr_checklist;
1517 out_rec.chk7_checklist_item_id :=chk_rec.checklist_item_id;
1518 out_rec.chk7_date_due :=chk_rec.date_due;
1519 out_rec.chk7_date_done :=chk_rec.date_done;
1520 out_rec.chk7_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1521 out_rec.chk7_status :=chk_rec.status;
1522 out_rec.chk7_notes :=chk_rec.notes;
1523 out_rec.chk7_object_version_number :=chk_rec.object_version_number;
1524 --
1525 hr_utility.set_location(l_proc,675);
1526 --
1527 else
1528 close csr_checklist;
1529 end if;
1530 else
1531 hr_utility.set_location(l_proc,680);
1532 close reverse_lookup;
1533 out_rec.chk7_item_code_meaning:=null;
1534 out_rec.chk7_item_code :=null;
1535 end if;
1536
1537 else
1538 close csr_template_items;
1539 END IF;
1540 --
1541 hr_utility.set_location(l_proc,685);
1542 --
1543 open csr_template_items
1544 (p_form_template_id => l_form_template_id
1545 ,p_full_item_name => p_block_name||'.CHK8_ITEM_CODE_MEANING'
1546 );
1547 fetch csr_template_items into l_template_item;
1548 --
1549 IF (csr_template_items%found) THEN
1550 hr_utility.set_location(l_proc, 690);
1551 close csr_template_items;
1552 open csr_template_item_contexts
1553 (p_template_item_id => l_template_item.template_item_id
1554 ,p_emp_apl_flag => out_rec.person_type
1555 );
1556 fetch csr_template_item_contexts INTO l_template_item_context;
1557 IF (csr_template_item_contexts%FOUND)
1558 THEN
1559 l_default_value := l_template_item_context.default_value;
1560 ELSE
1561 l_default_value := l_template_item.default_value;
1562 END IF;
1563 CLOSE csr_template_item_contexts;
1564 --
1565 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1566 fetch reverse_lookup into chk_rec.item_code;
1567 if reverse_lookup%found then
1568 hr_utility.set_location(l_proc, 695);
1569 close reverse_lookup;
1570 out_rec.chk8_item_code_meaning:=l_default_value;
1571 out_rec.chk8_item_code :=chk_rec.item_code;
1572 --
1573 open csr_checklist(p_person_id, chk_rec.item_code);
1574 fetch csr_checklist into chk_rec;
1575 if csr_checklist%found then
1576 --
1577 hr_utility.set_location(l_proc, 700);
1578 --
1579 close csr_checklist;
1580 out_rec.chk8_checklist_item_id :=chk_rec.checklist_item_id;
1581 out_rec.chk8_date_due :=chk_rec.date_due;
1582 out_rec.chk8_date_done :=chk_rec.date_done;
1583 out_rec.chk8_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1584 out_rec.chk8_status :=chk_rec.status;
1585 out_rec.chk8_notes :=chk_rec.notes;
1586 out_rec.chk8_object_version_number :=chk_rec.object_version_number;
1587 --
1588 hr_utility.set_location(l_proc,705);
1589 --
1590 else
1591 close csr_checklist;
1592 end if;
1593 else
1594 hr_utility.set_location(l_proc,710);
1595 close reverse_lookup;
1596 out_rec.chk8_item_code_meaning:=null;
1597 out_rec.chk8_item_code :=null;
1598 end if;
1599
1600 else
1601 close csr_template_items;
1602 END IF;
1603 --
1604 hr_utility.set_location(l_proc,715);
1605 --
1606 open csr_template_items
1607 (p_form_template_id => l_form_template_id
1608 ,p_full_item_name => p_block_name||'.CHK9_ITEM_CODE_MEANING'
1609 );
1610 fetch csr_template_items into l_template_item;
1611 --
1612 IF (csr_template_items%found) THEN
1613 hr_utility.set_location(l_proc, 720);
1614 close csr_template_items;
1615 open csr_template_item_contexts
1616 (p_template_item_id => l_template_item.template_item_id
1617 ,p_emp_apl_flag => out_rec.person_type
1618 );
1619 fetch csr_template_item_contexts INTO l_template_item_context;
1620 IF (csr_template_item_contexts%FOUND)
1621 THEN
1622 l_default_value := l_template_item_context.default_value;
1623 ELSE
1624 l_default_value := l_template_item.default_value;
1625 END IF;
1626 CLOSE csr_template_item_contexts;
1627 --
1628 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1629 fetch reverse_lookup into chk_rec.item_code;
1630 if reverse_lookup%found then
1631 hr_utility.set_location(l_proc, 725);
1632 close reverse_lookup;
1633 out_rec.chk9_item_code_meaning:=l_default_value;
1634 out_rec.chk9_item_code :=chk_rec.item_code;
1635 --
1636 open csr_checklist(p_person_id, chk_rec.item_code);
1637 fetch csr_checklist into chk_rec;
1638 if csr_checklist%found then
1639 --
1640 hr_utility.set_location(l_proc, 730);
1641 --
1642 close csr_checklist;
1643 out_rec.chk9_checklist_item_id :=chk_rec.checklist_item_id;
1644 out_rec.chk9_date_due :=chk_rec.date_due;
1645 out_rec.chk9_date_done :=chk_rec.date_done;
1646 out_rec.chk9_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1647 out_rec.chk9_status :=chk_rec.status;
1648 out_rec.chk9_notes :=chk_rec.notes;
1649 out_rec.chk9_object_version_number :=chk_rec.object_version_number;
1650 --
1651 hr_utility.set_location(l_proc,735);
1652 --
1653 else
1654 close csr_checklist;
1655 end if;
1656 else
1657 hr_utility.set_location(l_proc,740);
1658 close reverse_lookup;
1659 out_rec.chk9_item_code_meaning:=null;
1660 out_rec.chk9_item_code :=null;
1661 end if;
1662
1663 else
1664 close csr_template_items;
1665 END IF;
1666 --
1667 hr_utility.set_location(l_proc,745);
1668 --
1669 open csr_template_items
1670 (p_form_template_id => l_form_template_id
1671 ,p_full_item_name => p_block_name||'.CHK10_ITEM_CODE_MEANING'
1672 );
1673 fetch csr_template_items into l_template_item;
1674 --
1675 IF (csr_template_items%found) THEN
1676 hr_utility.set_location(l_proc, 750);
1677 close csr_template_items;
1678 open csr_template_item_contexts
1679 (p_template_item_id => l_template_item.template_item_id
1680 ,p_emp_apl_flag => out_rec.person_type
1681 );
1682 fetch csr_template_item_contexts INTO l_template_item_context;
1683 IF (csr_template_item_contexts%FOUND)
1684 THEN
1685 l_default_value := l_template_item_context.default_value;
1686 ELSE
1687 l_default_value := l_template_item.default_value;
1688 END IF;
1689 CLOSE csr_template_item_contexts;
1690 --
1691 open reverse_lookup(l_default_value,'CHECKLIST_ITEM');
1692 fetch reverse_lookup into chk_rec.item_code;
1693 if reverse_lookup%found then
1694 hr_utility.set_location(l_proc, 755);
1695 close reverse_lookup;
1696 out_rec.chk10_item_code_meaning:=l_default_value;
1697 out_rec.chk10_item_code :=chk_rec.item_code;
1698 --
1699 open csr_checklist(p_person_id, chk_rec.item_code);
1700 fetch csr_checklist into chk_rec;
1701 if csr_checklist%found then
1702 --
1703 hr_utility.set_location(l_proc, 760);
1704 --
1705 close csr_checklist;
1706 out_rec.chk10_checklist_item_id :=chk_rec.checklist_item_id;
1707 out_rec.chk10_date_due :=chk_rec.date_due;
1708 out_rec.chk10_date_done :=chk_rec.date_done;
1709 out_rec.chk10_status_meaning :=hr_reports.get_lookup_meaning('CHECKLIST_STATUS',chk_rec.status);
1710 out_rec.chk10_status :=chk_rec.status;
1711 out_rec.chk10_notes :=chk_rec.notes;
1712 out_rec.chk10_object_version_number :=chk_rec.object_version_number;
1713 --
1714 hr_utility.set_location(l_proc,765);
1715 --
1716 else
1717 close csr_checklist;
1718 end if;
1719 else
1720 hr_utility.set_location(l_proc,770);
1721 close reverse_lookup;
1722 out_rec.chk10_item_code_meaning:=null;
1723 out_rec.chk10_item_code :=null;
1724 end if;
1725
1726 else
1727 close csr_template_items;
1728 END IF;
1729 --
1730 hr_utility.set_location(l_proc,771);
1731 hr_utility.trace('p_assignment_id : ' || p_assignment_id);
1732 hr_utility.trace('p_effective_date : ' || p_effective_date);
1733 --
1734 if p_assignment_id is not null then
1735 --
1736 hr_utility.set_location(l_proc,772);
1737 --
1738 open csr_assignment_details(p_assignment_id,p_effective_date);
1739 fetch csr_assignment_details into asg_rec;
1740 if csr_assignment_details%notfound then
1741 close csr_assignment_details;
1742 out_rec.assignment_id:=-1;
1743 --no assignment found mean person is OTHER or EX-something
1744 --flag up that the fields should be disabled, fixes bug 3929761
1745 out_rec.asg_update_allowed:='FALSE';
1746 hr_utility.set_location(l_proc,773);
1747 else
1748 --
1749 hr_utility.set_location(l_proc,774);
1750 --
1751 out_rec.assignment_id :=asg_rec.assignment_id;
1752 out_rec.asg_effective_start_date :=asg_rec.effective_start_date;
1753 out_rec.asg_effective_end_date :=asg_rec.effective_end_date;
1754 --
1755 if asg_rec.effective_end_date=l_end_of_time then
1756 out_rec.asg_update_allowed:='TRUE';
1757 else
1758 out_rec.asg_update_allowed:='FALSE';
1759 end if;
1760 --
1761 hr_utility.set_location(l_proc,775);
1762 --
1763 if asg_rec.recruiter_id is not null then
1764 out_rec.recruiter
1765 :=per_qh_populate.get_full_name(asg_rec.recruiter_id,p_effective_date);
1766 end if;
1767 out_rec.recruiter_id :=asg_rec.recruiter_id;
1768 --
1769 hr_utility.set_location(l_proc,776);
1770 --
1771 if asg_rec.grade_id is not null then
1772 out_rec.grade:=per_qh_populate.get_grade(asg_rec.grade_id);
1773 end if;
1774 out_rec.grade_id :=asg_rec.grade_id;
1775 --
1776 hr_utility.set_location(l_proc,777);
1777 --
1778 if asg_rec.grade_ladder_pgm_id is not null then
1779 out_rec.grade_ladder
1780 := per_qh_populate.get_grade_ladder(asg_rec.grade_ladder_pgm_id
1781 ,p_effective_date);
1782 end if;
1783 out_rec.grade_ladder_pgm_id := asg_rec.grade_ladder_pgm_id;
1784 --
1785 hr_utility.set_location(l_proc,778);
1786 --
1787 if asg_rec.position_id is not null then
1788 out_rec.position
1789 :=per_qh_populate.get_position(asg_rec.position_id,p_effective_date);
1790 end if;
1791 out_rec.position_id :=asg_rec.position_id;
1792 --
1793 hr_utility.set_location(l_proc,779);
1794 --
1795 if asg_rec.job_id is not null then
1796 out_rec.job:=per_qh_populate.get_job(asg_rec.job_id);
1797 end if;
1798 out_rec.job_id :=asg_rec.job_id;
1799 --
1800 hr_utility.set_location(l_proc,780);
1801 --
1802 open csr_asg_status(asg_rec.assignment_status_type_id,asg_rec.business_group_id);
1803 fetch csr_asg_status into out_rec.assignment_status_type,out_rec.system_status;
1804 close csr_asg_status;
1805 out_rec.assignment_status_type_id :=asg_rec.assignment_status_type_id;
1806 --
1807 hr_utility.set_location(l_proc,782);
1808 --
1809 if asg_rec.payroll_id is not null then
1810 out_rec.payroll:=
1811 per_qh_populate.get_payroll(asg_rec.payroll_id,p_effective_date);
1812 end if;
1813 out_rec.payroll_id :=asg_rec.payroll_id;
1814 --
1815 hr_utility.set_location(l_proc,785);
1816 --
1817 if asg_rec.location_id is not null then
1818 out_rec.location:=per_qh_populate.get_location(asg_rec.location_id);
1819 out_rec.location_address:=hr_general.hr_lookup_locations(asg_rec.location_id);
1820 end if;
1821 out_rec.location_id :=asg_rec.location_id;
1822 --
1823 hr_utility.set_location(l_proc,790);
1824 --
1825 if asg_rec.person_referred_by_id is not null then
1826 out_rec.person_referred_by:=
1827 per_qh_populate.get_full_name(asg_rec.person_referred_by_id,p_effective_date);
1828 end if;
1829 out_rec.person_referred_by_id :=asg_rec.person_referred_by_id;
1830 --
1831 hr_utility.set_location(l_proc,800);
1832 --
1833 if asg_rec.supervisor_id is not null then
1834 out_rec.supervisor:=
1835 per_qh_populate.get_full_name(asg_rec.supervisor_id,p_effective_date);
1836 end if;
1837 out_rec.supervisor_id :=asg_rec.supervisor_id;
1838 --
1839 hr_utility.set_location(l_proc,805);
1840 --
1841 if asg_rec.supervisor_assignment_id is not null then
1842 out_rec.supervisor_assignment_number :=
1843 per_qh_populate.get_supervisor_assgn_number(
1844 asg_rec.supervisor_assignment_id,asg_rec.business_group_id);
1845 end if;
1846 out_rec.supervisor_assignment_id := asg_rec.supervisor_assignment_id;
1847 --
1848 hr_utility.set_location(l_proc,810);
1849 --
1850 if asg_rec.recruitment_activity_id is not null then
1851 open csr_rec_activity(asg_rec.recruitment_activity_id);
1852 fetch csr_rec_activity into out_rec.recruitment_activity;
1853 close csr_rec_activity;
1854 end if;
1855 out_rec.recruitment_activity_id :=asg_rec.recruitment_activity_id;
1856 --
1857 hr_utility.set_location(l_proc,820);
1858 --
1859 if asg_rec.source_organization_id is not null then
1860 out_rec.source_organization
1861 :=per_qh_populate.get_organization(asg_rec.source_organization_id);
1862 end if;
1863 out_rec.source_organization_id :=asg_rec.source_organization_id;
1864 --
1865 hr_utility.set_location(l_proc,830);
1866 --
1867 out_rec.organization
1868 :=per_qh_populate.get_organization(asg_rec.organization_id);
1869 out_rec.organization_id :=asg_rec.organization_id;
1870 --
1871 hr_utility.set_location(l_proc,840);
1872 --
1873 if asg_rec.people_group_id is not null then
1874 open csr_pgp_rec(asg_rec.people_group_id);
1875 fetch csr_pgp_rec into pgp_rec;
1876 close csr_pgp_rec;
1877 --
1878 hr_utility.set_location(l_proc,850);
1879 --
1880 out_rec.pgp_segment1 :=pgp_rec.segment1;
1881 out_rec.pgp_segment2 :=pgp_rec.segment2;
1882 out_rec.pgp_segment3 :=pgp_rec.segment3;
1883 out_rec.pgp_segment4 :=pgp_rec.segment4;
1884 out_rec.pgp_segment5 :=pgp_rec.segment5;
1885 out_rec.pgp_segment6 :=pgp_rec.segment6;
1886 out_rec.pgp_segment7 :=pgp_rec.segment7;
1887 out_rec.pgp_segment8 :=pgp_rec.segment8;
1888 out_rec.pgp_segment9 :=pgp_rec.segment9;
1889 out_rec.pgp_segment10 :=pgp_rec.segment10;
1890 out_rec.pgp_segment11 :=pgp_rec.segment11;
1891 out_rec.pgp_segment12 :=pgp_rec.segment12;
1892 out_rec.pgp_segment13 :=pgp_rec.segment13;
1893 out_rec.pgp_segment14 :=pgp_rec.segment14;
1894 out_rec.pgp_segment15 :=pgp_rec.segment15;
1895 out_rec.pgp_segment16 :=pgp_rec.segment16;
1896 out_rec.pgp_segment17 :=pgp_rec.segment17;
1897 out_rec.pgp_segment18 :=pgp_rec.segment18;
1898 out_rec.pgp_segment19 :=pgp_rec.segment19;
1899 out_rec.pgp_segment20 :=pgp_rec.segment20;
1900 out_rec.pgp_segment21 :=pgp_rec.segment21;
1901 out_rec.pgp_segment22 :=pgp_rec.segment22;
1902 out_rec.pgp_segment23 :=pgp_rec.segment23;
1903 out_rec.pgp_segment24 :=pgp_rec.segment24;
1904 out_rec.pgp_segment25 :=pgp_rec.segment25;
1905 out_rec.pgp_segment26 :=pgp_rec.segment26;
1906 out_rec.pgp_segment27 :=pgp_rec.segment27;
1907 out_rec.pgp_segment28 :=pgp_rec.segment28;
1908 out_rec.pgp_segment29 :=pgp_rec.segment29;
1909 out_rec.pgp_segment30 :=pgp_rec.segment30;
1910 end if;
1911 out_rec.people_group_id :=asg_rec.people_group_id;
1912 --
1913 hr_utility.set_location(l_proc,860);
1914 --
1915 if asg_rec.soft_coding_keyflex_id is not null then
1916 open csr_scl_rec(asg_rec.soft_coding_keyflex_id);
1917 fetch csr_scl_rec into scl_rec;
1918 close csr_scl_rec;
1919 --
1920 hr_utility.set_location(l_proc,870);
1921 --
1922 out_rec.scl_segment1 :=scl_rec.segment1;
1923 out_rec.scl_segment2 :=scl_rec.segment2;
1924 out_rec.scl_segment3 :=scl_rec.segment3;
1925 out_rec.scl_segment4 :=scl_rec.segment4;
1926 out_rec.scl_segment5 :=scl_rec.segment5;
1927 out_rec.scl_segment6 :=scl_rec.segment6;
1928 out_rec.scl_segment7 :=scl_rec.segment7;
1929 out_rec.scl_segment8 :=scl_rec.segment8;
1930 out_rec.scl_segment9 :=scl_rec.segment9;
1931 out_rec.scl_segment10 :=scl_rec.segment10;
1932 out_rec.scl_segment11 :=scl_rec.segment11;
1933 out_rec.scl_segment12 :=scl_rec.segment12;
1934 out_rec.scl_segment13 :=scl_rec.segment13;
1935 out_rec.scl_segment14 :=scl_rec.segment14;
1936 out_rec.scl_segment15 :=scl_rec.segment15;
1937 out_rec.scl_segment16 :=scl_rec.segment16;
1938 out_rec.scl_segment17 :=scl_rec.segment17;
1939 out_rec.scl_segment18 :=scl_rec.segment18;
1940 out_rec.scl_segment19 :=scl_rec.segment19;
1941 out_rec.scl_segment20 :=scl_rec.segment20;
1942 out_rec.scl_segment21 :=scl_rec.segment21;
1943 out_rec.scl_segment22 :=scl_rec.segment22;
1944 out_rec.scl_segment23 :=scl_rec.segment23;
1945 out_rec.scl_segment24 :=scl_rec.segment24;
1946 out_rec.scl_segment25 :=scl_rec.segment25;
1947 out_rec.scl_segment26 :=scl_rec.segment26;
1948 out_rec.scl_segment27 :=scl_rec.segment27;
1949 out_rec.scl_segment28 :=scl_rec.segment28;
1950 out_rec.scl_segment29 :=scl_rec.segment29;
1951 out_rec.scl_segment30 :=scl_rec.segment30;
1952 end if;
1953 out_rec.soft_coding_keyflex_id :=asg_rec.soft_coding_keyflex_id;
1954 --
1955 hr_utility.set_location(l_proc,880);
1956 --
1957 if asg_rec.vacancy_id is not null then
1958 open csr_vacancy(asg_rec.vacancy_id);
1959 fetch csr_vacancy into out_rec.vacancy,out_rec.requisition;
1960 close csr_vacancy;
1961 end if;
1962 out_rec.vacancy_id :=asg_rec.vacancy_id;
1963 --
1964 hr_utility.set_location(l_proc,890);
1965 --
1966 if asg_rec.pay_basis_id is not null then
1967 open salary_basis(p_effective_date,asg_rec.pay_basis_id);
1968 fetch salary_basis into
1969 out_rec.salary_basis
1970 ,out_rec.pay_basis
1971 ,out_rec.currency_code
1972 ,l_uom;
1973 close salary_basis;
1974 if l_uom='M' then
1975 per_pay_proposals_populate.get_currency_format(out_rec.currency_code,out_rec.salary_format);
1976 else
1977 per_pay_proposals_populate.get_number_format(out_rec.salary_format);
1978 end if;
1979 end if;
1980 out_rec.pay_basis_id :=asg_rec.pay_basis_id;
1981 out_rec.pay_basis_meaning :=hr_reports.get_lookup_meaning('PAY_BASIS',out_rec.pay_basis);
1982 --
1983 hr_utility.set_location(l_proc,900);
1984 --
1985 out_rec.assignment_sequence :=asg_rec.assignment_sequence;
1986 out_rec.assignment_type :=asg_rec.assignment_type;
1987 out_rec.asg_primary_flag :=asg_rec.primary_flag;
1988 out_rec.assignment_number :=asg_rec.assignment_number;
1989 out_rec.date_probation_end :=asg_rec.date_probation_end;
1990 out_rec.default_code_comb_id :=asg_rec.default_code_comb_id;
1991 --
1992 if asg_rec.assignment_type = 'E' then
1993 out_rec.employment_category_meaning
1994 :=hr_reports.get_lookup_meaning('EMP_CAT',asg_rec.employment_category);
1995 elsif asg_rec.assignment_type = 'C' then
1996 out_rec.employment_category_meaning
1997 := hr_reports.get_lookup_meaning('CWK_ASG_CATEGORY',asg_rec.employment_category);
1998 end if;
1999 --
2000 out_rec.employment_category :=asg_rec.employment_category;
2001 out_rec.employee_category_meaning :=hr_reports.get_lookup_meaning('EMPLOYEE_CATG',asg_rec.employee_category);
2002 out_rec.employee_category :=asg_rec.employee_category;
2003 out_rec.frequency_meaning :=hr_reports.get_lookup_meaning('FREQUENCY',asg_rec.frequency);
2004 out_rec.frequency :=asg_rec.frequency;
2005 out_rec.normal_hours :=asg_rec.normal_hours;
2006 out_rec.probation_period :=asg_rec.probation_period;
2007 out_rec.probation_unit_meaning :=hr_reports.get_lookup_meaning('QUALIFYING_UNITS',asg_rec.probation_unit);
2008 out_rec.probation_unit :=asg_rec.probation_unit;
2009 out_rec.notice_period :=asg_rec.notice_period;
2010 out_rec.notice_unit_meaning :=hr_reports.get_lookup_meaning('QUALIFYING_UNITS',asg_rec.notice_period_uom);
2011 out_rec.notice_unit :=asg_rec.notice_period_uom;
2012 out_rec.set_of_books_id :=asg_rec.set_of_books_id;
2013 if asg_rec.set_of_books_id is not null then
2014 open csr_sob(asg_rec.set_of_books_id);
2015 fetch csr_sob into out_rec.set_of_books_name, out_rec.gl_keyflex_struct;
2016 close csr_sob;
2017 end if;
2018 out_rec.billing_title :=asg_rec.title;
2019 out_rec.time_normal_finish :=asg_rec.time_normal_finish;
2020 out_rec.time_normal_start :=asg_rec.time_normal_start;
2021 --
2022 hr_utility.set_location(l_proc,910);
2023 --
2024 --CWK asg fields
2025 out_rec.projected_assignment_end :=asg_rec.projected_assignment_end;
2026 out_rec.vendor_employee_number :=asg_rec.vendor_employee_number;
2027 out_rec.vendor_assignment_number :=asg_rec.vendor_assignment_number;
2028 if asg_rec.vendor_id is not null then
2029 open csr_vendor(asg_rec.vendor_id);
2030 fetch csr_vendor into out_rec.vendor_name;
2031 close csr_vendor;
2032 end if;
2033 out_rec.vendor_id :=asg_rec.vendor_id;
2034 out_rec.project_title :=asg_rec.project_title;
2035 --
2036 if asg_rec.vendor_site_id is not null then
2037 open csr_vendor_site(asg_rec.vendor_site_id);
2038 fetch csr_vendor_site into out_rec.vendor_site_code;
2039 close csr_vendor_site;
2040 end if;
2041 out_rec.vendor_site_id :=asg_rec.vendor_site_id;
2042 --
2043 if asg_rec.po_header_id is not null then
2044 open csr_po_header(asg_rec.po_header_id);
2045 fetch csr_po_header into out_rec.po_header_num;
2046 close csr_po_header;
2047 end if;
2048 out_rec.po_header_id :=asg_rec.po_header_id;
2049 --
2050 if asg_rec.po_line_id is not null then
2051 open csr_po_line(asg_rec.po_line_id);
2052 fetch csr_po_line into out_rec.po_line_num;
2053 close csr_po_line;
2054 end if;
2055 out_rec.po_line_id :=asg_rec.po_line_id;
2056 --
2057 hr_utility.set_location(l_proc,915);
2058 --CWK asg rates
2059 if nvl(fnd_profile.value('PO_SERVICES_ENABLED'),'N') = 'Y' then
2060 fnd_message.set_name('PER','HR_PO_RATE_MESG');
2061 out_rec.grade_rule_id := l_rate_rec.grade_rule_id;
2062 out_rec.rate_id := null;
2063 out_rec.rate_name := null;
2064 out_rec.rate_basis := substrb(fnd_message.get,1,30);
2065 out_rec.asg_rate_type_name := null;
2066 out_rec.rate_currency := null;
2067 out_rec.rate_currency_code := null;
2068 out_rec.rate_value := null;
2069 out_rec.rate_effective_start_date := null;
2070 out_rec.rate_effective_end_date := null;
2071 out_rec.rate_object_version_number := null;
2072 else --try to find the HR assignment rates
2073 open csr_asg_rates(p_assignment_id, p_effective_date);
2074 fetch csr_asg_rates into l_rate_rec;
2075 if csr_asg_rates%notfound then
2076 hr_utility.set_location(l_proc,916);
2077 out_rec.grade_rule_id := null;
2078 out_rec.rate_id := null;
2079 out_rec.rate_name := null;
2080 out_rec.rate_basis := null;
2081 out_rec.asg_rate_type_name := null;
2082 out_rec.rate_currency := null;
2083 out_rec.rate_currency_code := null;
2084 out_rec.rate_value := null;
2085 out_rec.rate_effective_start_date := null;
2086 out_rec.rate_effective_end_date := null;
2087 out_rec.rate_object_version_number := null;
2088 elsif csr_asg_rates%found then
2089 hr_utility.set_location(l_proc,917);
2090 out_rec.grade_rule_id := l_rate_rec.grade_rule_id;
2091 out_rec.rate_id := l_rate_rec.rate_id;
2092 out_rec.rate_name := l_rate_rec.rate_name;
2093 out_rec.rate_basis := l_rate_rec.rate_basis;
2094 out_rec.asg_rate_type_name := l_rate_rec.asg_rate_type_name;
2095 out_rec.rate_currency := l_rate_rec.rate_currency;
2096 out_rec.rate_currency_code := l_rate_rec.currency_code;
2097 out_rec.rate_value := l_rate_rec.value;
2098 out_rec.rate_effective_start_date := l_rate_rec.effective_start_date;
2099 out_rec.rate_effective_end_date := l_rate_rec.effective_end_date;
2100 out_rec.rate_object_version_number := l_rate_rec.object_version_number;
2101 fetch csr_asg_rates into l_rate_rec; --try a second fetch
2102 if csr_asg_rates%notfound then
2103 null;
2104 elsif csr_asg_rates%found then
2105 --this means multiple rates exits. Further update processing will rely on setting
2106 --grade_rule_id but nothing else
2107 hr_utility.set_location(l_proc,918);
2108 fnd_message.set_name('PER','HR_MULTI_RATE_MESG');
2109 out_rec.grade_rule_id := l_rate_rec.grade_rule_id;
2110 out_rec.rate_id := null;
2111 out_rec.rate_name := null;
2112 out_rec.rate_basis := substrb(fnd_message.get,1,30);
2113 out_rec.asg_rate_type_name := null;
2114 out_rec.rate_currency := null;
2115 out_rec.rate_currency_code := null;
2116 out_rec.rate_value := null;
2117 out_rec.rate_effective_start_date := null;
2118 out_rec.rate_effective_end_date := null;
2119 out_rec.rate_object_version_number := null;
2120 end if;
2121 end if;
2122 close csr_asg_rates;
2123 end if; --end check for PO_SERVICES_ENABLED
2124 --
2125 hr_utility.set_location(l_proc,919);
2126 --
2127 out_rec.ass_attribute_category :=asg_rec.ass_attribute_category;
2128 out_rec.ass_attribute1 :=asg_rec.ass_attribute1;
2129 out_rec.ass_attribute2 :=asg_rec.ass_attribute2;
2130 out_rec.ass_attribute3 :=asg_rec.ass_attribute3;
2131 out_rec.ass_attribute4 :=asg_rec.ass_attribute4;
2132 out_rec.ass_attribute5 :=asg_rec.ass_attribute5;
2133 out_rec.ass_attribute6 :=asg_rec.ass_attribute6;
2134 out_rec.ass_attribute7 :=asg_rec.ass_attribute7;
2135 out_rec.ass_attribute8 :=asg_rec.ass_attribute8;
2136 out_rec.ass_attribute9 :=asg_rec.ass_attribute9;
2137 out_rec.ass_attribute10 :=asg_rec.ass_attribute10;
2138 out_rec.ass_attribute11 :=asg_rec.ass_attribute11;
2139 out_rec.ass_attribute12 :=asg_rec.ass_attribute12;
2140 out_rec.ass_attribute13 :=asg_rec.ass_attribute13;
2141 out_rec.ass_attribute14 :=asg_rec.ass_attribute14;
2142 out_rec.ass_attribute15 :=asg_rec.ass_attribute15;
2143 out_rec.ass_attribute16 :=asg_rec.ass_attribute16;
2144 out_rec.ass_attribute17 :=asg_rec.ass_attribute17;
2145 out_rec.ass_attribute18 :=asg_rec.ass_attribute18;
2146 out_rec.ass_attribute19 :=asg_rec.ass_attribute19;
2147 out_rec.ass_attribute20 :=asg_rec.ass_attribute20;
2148 out_rec.ass_attribute21 :=asg_rec.ass_attribute21;
2149 out_rec.ass_attribute22 :=asg_rec.ass_attribute22;
2150 out_rec.ass_attribute23 :=asg_rec.ass_attribute23;
2151 out_rec.ass_attribute24 :=asg_rec.ass_attribute24;
2152 out_rec.ass_attribute25 :=asg_rec.ass_attribute25;
2153 out_rec.ass_attribute26 :=asg_rec.ass_attribute26;
2154 out_rec.ass_attribute27 :=asg_rec.ass_attribute27;
2155 out_rec.ass_attribute28 :=asg_rec.ass_attribute28;
2156 out_rec.ass_attribute29 :=asg_rec.ass_attribute29;
2157 out_rec.ass_attribute30 :=asg_rec.ass_attribute30;
2158 out_rec.asg_object_version_number :=asg_rec.object_version_number;
2159 out_rec.bargaining_unit_code_meaning :=hr_reports.get_lookup_meaning('BARGAINING_UNIT_CODE',asg_rec.bargaining_unit_code);
2160 out_rec.bargaining_unit_code :=asg_rec.bargaining_unit_code;
2161 out_rec.labour_union_member_flag :=asg_rec.labour_union_member_flag;
2162 out_rec.hourly_salaried_meaning :=hr_reports.get_lookup_meaning('HOURLY_SALARIED_CODE',asg_rec.hourly_salaried_code);
2163 out_rec.hourly_salaried_code :=asg_rec.hourly_salaried_code;
2164 --
2165 hr_utility.set_location(l_proc,920);
2166 --
2167 if asg_rec.special_ceiling_step_id is not null then
2168 open csr_ceiling_step(asg_rec.special_ceiling_step_id,p_effective_date);
2169 fetch csr_ceiling_step into out_rec.special_ceiling_point, out_rec.special_ceiling_step;
2170 close csr_ceiling_step;
2171 end if;
2172 out_rec.special_ceiling_step_id :=asg_rec.special_ceiling_step_id;
2173 --
2174 hr_utility.set_location(l_proc,930);
2175 --
2176 -- Bug# 4153433 Start Here
2177 --
2178 if asg_rec.assignment_type = 'E' then
2179 out_rec.change_reason_meaning :=hr_reports.get_lookup_meaning('EMP_ASSIGN_REASON',asg_rec.change_reason);
2180 elsif asg_rec.assignment_type = 'A' then
2181 out_rec.change_reason_meaning :=hr_reports.get_lookup_meaning('APL_ASSIGN_REASON',asg_rec.change_reason);
2182 elsif asg_rec.assignment_type = 'C' then
2183 out_rec.change_reason_meaning :=hr_reports.get_lookup_meaning('CWK_ASSIGN_REASON',asg_rec.change_reason);
2184 end if;
2185 --
2186 --Bug# 4153433 End here
2187 --
2188 out_rec.change_reason :=asg_rec.change_reason;
2189 out_rec.internal_address_line :=asg_rec.internal_address_line;
2190 out_rec.manager_flag :=asg_rec.manager_flag;
2191 out_rec.perf_review_period :=asg_rec.perf_review_period;
2192 out_rec.perf_rev_period_freq_meaning :=hr_reports.get_lookup_meaning('FREQUENCY',asg_rec.perf_review_period_frequency);
2193 out_rec.perf_review_period_frequency :=asg_rec.perf_review_period_frequency;
2194 out_rec.sal_review_period :=asg_rec.sal_review_period;
2195 out_rec.sal_rev_period_freq_meaning :=hr_reports.get_lookup_meaning('FREQUENCY',asg_rec.sal_review_period_frequency);
2196 out_rec.sal_review_period_frequency :=asg_rec.sal_review_period_frequency;
2197 out_rec.source_type_meaning :=hr_reports.get_lookup_meaning('REC_TYPE',asg_rec.source_type);
2198 out_rec.source_type :=asg_rec.source_type;
2199 --
2200 hr_utility.set_location(l_proc,940);
2201 --
2202 if asg_rec.contract_id is not null then
2203 open csr_reference(asg_rec.contract_id,p_effective_date);
2204 fetch csr_reference into out_rec.contract;
2205 close csr_reference;
2206 end if;
2207 out_rec.contract_id :=asg_rec.contract_id;
2208 --
2209 hr_utility.set_location(l_proc,950);
2210 --
2211 if asg_rec.collective_agreement_id is not null then
2212 open csr_collective_agr(asg_rec.collective_agreement_id);
2213 fetch csr_collective_agr into out_rec.collective_agreement;
2214 close csr_collective_agr;
2215 end if;
2216 out_rec.collective_agreement_id :=asg_rec.collective_agreement_id;
2217 --
2218 hr_utility.set_location(l_proc,960);
2219 --
2220 if asg_rec.cagr_id_flex_num is not null then
2221 open csr_cagr_flex_num(asg_rec.cagr_id_flex_num);
2222 fetch csr_cagr_flex_num into out_rec.cagr_id_flex_name;
2223 close csr_cagr_flex_num;
2224 end if;
2225 out_rec.cagr_id_flex_num :=asg_rec.cagr_id_flex_num;
2226 --
2227 out_rec.cagr_grade_def_id :=asg_rec.cagr_grade_def_id;
2228 --
2229 hr_utility.set_location(l_proc,970);
2230 --
2231 if asg_rec.establishment_id is not null then
2232 out_rec.establishment
2233 :=per_qh_populate.get_organization(asg_rec.establishment_id);
2234 end if;
2235 out_rec.establishment_id :=asg_rec.establishment_id;
2236 --
2237 hr_utility.set_location(l_proc,980);
2238 --
2239 --Bug 3063591 Start Here
2240 out_rec.work_at_home :=asg_rec.work_at_home;
2241 --Bug 3063591 End Here
2242 --
2243 hr_utility.set_location(l_proc,985);
2244 --
2245 open csr_pay_proposal(p_assignment_id,p_effective_date);
2246 fetch csr_pay_proposal into pyp_rec;
2247 if csr_pay_proposal%found then
2248 --
2249 hr_utility.set_location(l_proc,990);
2250 --
2251 close csr_pay_proposal;
2252 out_rec.pay_proposal_id :=pyp_rec.pay_proposal_id;
2253 out_rec.change_date :=pyp_rec.change_date;
2254 out_rec.proposed_salary_n :=pyp_rec.proposed_salary_n;
2255 out_rec.proposal_reason :=pyp_rec.proposal_reason;
2256 out_rec.proposal_reason_meaning :=
2257 hr_reports.get_lookup_meaning('PROPOSAL_REASON',pyp_rec.proposal_reason);
2258 out_rec.pyp_attribute_category :=pyp_rec.attribute_category;
2259 out_rec.pyp_attribute1 :=pyp_rec.attribute1;
2260 out_rec.pyp_attribute2 :=pyp_rec.attribute2;
2261 out_rec.pyp_attribute3 :=pyp_rec.attribute3;
2262 out_rec.pyp_attribute4 :=pyp_rec.attribute4;
2263 out_rec.pyp_attribute5 :=pyp_rec.attribute5;
2264 out_rec.pyp_attribute6 :=pyp_rec.attribute6;
2265 out_rec.pyp_attribute7 :=pyp_rec.attribute7;
2266 out_rec.pyp_attribute8 :=pyp_rec.attribute8;
2267 out_rec.pyp_attribute9 :=pyp_rec.attribute9;
2268 out_rec.pyp_attribute10 :=pyp_rec.attribute10;
2269 out_rec.pyp_attribute11 :=pyp_rec.attribute11;
2270 out_rec.pyp_attribute12 :=pyp_rec.attribute12;
2271 out_rec.pyp_attribute13 :=pyp_rec.attribute13;
2272 out_rec.pyp_attribute14 :=pyp_rec.attribute14;
2273 out_rec.pyp_attribute15 :=pyp_rec.attribute15;
2274 out_rec.pyp_attribute16 :=pyp_rec.attribute16;
2275 out_rec.pyp_attribute17 :=pyp_rec.attribute17;
2276 out_rec.pyp_attribute18 :=pyp_rec.attribute18;
2277 out_rec.pyp_attribute19 :=pyp_rec.attribute19;
2278 out_rec.pyp_attribute20 :=pyp_rec.attribute20;
2279 out_rec.pyp_object_version_number :=pyp_rec.object_version_number;
2280 out_rec.multiple_components :=pyp_rec.multiple_components;
2281 out_rec.approved :=pyp_rec.approved;
2282 else
2283 close csr_pay_proposal;
2284 end if;
2285 end if; -- assignment found
2286 end if; -- assignment_id is not null
2287 --
2288 out_rec.person_type :=hr_person_type_usage_info.get_user_person_type
2289 (p_effective_date => p_effective_date
2290 ,p_person_id => p_person_id
2291 );
2292 --
2293 per_qh_tax_query.tax_query
2294 (tax_effective_start_date => out_rec.tax_effective_start_date
2295 ,tax_effective_end_date => out_rec.tax_effective_end_date
2296 ,tax_field1 => out_rec.tax_field1
2297 ,tax_field2 => out_rec.tax_field2
2298 ,tax_field3 => out_rec.tax_field3
2299 ,tax_field4 => out_rec.tax_field4
2300 ,tax_field5 => out_rec.tax_field5
2301 ,tax_field6 => out_rec.tax_field6
2302 ,tax_field7 => out_rec.tax_field7
2303 ,tax_field8 => out_rec.tax_field8
2304 ,tax_field9 => out_rec.tax_field9
2305 ,tax_field10 => out_rec.tax_field10
2306 ,tax_field11 => out_rec.tax_field11
2307 ,tax_field12 => out_rec.tax_field12
2308 ,tax_field13 => out_rec.tax_field13
2309 ,tax_field14 => out_rec.tax_field14
2310 ,tax_field15 => out_rec.tax_field15
2311 ,tax_field16 => out_rec.tax_field16
2312 ,tax_field17 => out_rec.tax_field17
2313 ,tax_field18 => out_rec.tax_field18
2314 ,tax_field19 => out_rec.tax_field19
2315 ,tax_field20 => out_rec.tax_field20
2316 ,tax_field21 => out_rec.tax_field21
2317 ,tax_field22 => out_rec.tax_field22
2318 ,tax_field23 => out_rec.tax_field23
2319 ,tax_field24 => out_rec.tax_field24
2320 ,tax_field25 => out_rec.tax_field25
2321 ,tax_field26 => out_rec.tax_field26
2322 ,tax_field27 => out_rec.tax_field27
2323 ,tax_field28 => out_rec.tax_field28
2324 ,tax_field29 => out_rec.tax_field29
2325 ,tax_field30 => out_rec.tax_field30
2326 ,tax_field31 => out_rec.tax_field31
2327 ,tax_field32 => out_rec.tax_field32
2328 ,tax_field33 => out_rec.tax_field33
2329 ,tax_field34 => out_rec.tax_field34
2330 ,tax_field35 => out_rec.tax_field35
2331 ,tax_field36 => out_rec.tax_field36
2332 ,tax_field37 => out_rec.tax_field37
2333 ,tax_field38 => out_rec.tax_field38
2334 ,tax_field39 => out_rec.tax_field39
2335 ,tax_field40 => out_rec.tax_field40
2336 ,tax_field41 => out_rec.tax_field41
2337 ,tax_field42 => out_rec.tax_field42
2338 ,tax_field43 => out_rec.tax_field43
2339 ,tax_field44 => out_rec.tax_field44
2340 ,tax_field45 => out_rec.tax_field45
2341 ,tax_field46 => out_rec.tax_field46
2342 ,tax_field47 => out_rec.tax_field47
2343 ,tax_field48 => out_rec.tax_field48
2344 ,tax_field49 => out_rec.tax_field49
2345 ,tax_field50 => out_rec.tax_field50
2346 ,tax_field51 => out_rec.tax_field51
2347 ,tax_field52 => out_rec.tax_field52
2348 ,tax_field53 => out_rec.tax_field53
2349 ,tax_field54 => out_rec.tax_field54
2350 ,tax_field55 => out_rec.tax_field55
2351 ,tax_field56 => out_rec.tax_field56
2352 ,tax_field57 => out_rec.tax_field57
2353 ,tax_field58 => out_rec.tax_field58
2354 ,tax_field59 => out_rec.tax_field59
2355 ,tax_field60 => out_rec.tax_field60
2356 ,tax_field61 => out_rec.tax_field61
2357 ,tax_field62 => out_rec.tax_field62
2358 ,tax_field63 => out_rec.tax_field63
2359 ,tax_field64 => out_rec.tax_field64
2360 ,tax_field65 => out_rec.tax_field65
2361 ,tax_field66 => out_rec.tax_field66
2362 ,tax_field67 => out_rec.tax_field67
2363 ,tax_field68 => out_rec.tax_field68
2364 ,tax_field69 => out_rec.tax_field69
2365 ,tax_field70 => out_rec.tax_field70
2366 ,tax_field71 => out_rec.tax_field71
2367 ,tax_field72 => out_rec.tax_field72
2368 ,tax_field73 => out_rec.tax_field73
2369 ,tax_field74 => out_rec.tax_field74
2370 ,tax_field75 => out_rec.tax_field75
2371 ,tax_field76 => out_rec.tax_field76
2372 ,tax_field77 => out_rec.tax_field77
2373 ,tax_field78 => out_rec.tax_field78
2374 ,tax_field79 => out_rec.tax_field79
2375 ,tax_field80 => out_rec.tax_field80
2376 ,tax_field81 => out_rec.tax_field81
2377 ,tax_field82 => out_rec.tax_field82
2378 ,tax_field83 => out_rec.tax_field83
2379 ,tax_field84 => out_rec.tax_field84
2380 ,tax_field85 => out_rec.tax_field85
2381 ,tax_field86 => out_rec.tax_field86
2382 ,tax_field87 => out_rec.tax_field87
2383 ,tax_field88 => out_rec.tax_field88
2384 ,tax_field89 => out_rec.tax_field89
2385 ,tax_field90 => out_rec.tax_field90
2386 ,tax_field91 => out_rec.tax_field91
2387 ,tax_field92 => out_rec.tax_field92
2388 ,tax_field93 => out_rec.tax_field93
2389 ,tax_field94 => out_rec.tax_field94
2390 ,tax_field95 => out_rec.tax_field95
2391 ,tax_field96 => out_rec.tax_field96
2392 ,tax_field97 => out_rec.tax_field97
2393 ,tax_field98 => out_rec.tax_field98
2394 ,tax_field99 => out_rec.tax_field99
2395 ,tax_field100 => out_rec.tax_field100
2396 ,tax_field101 => out_rec.tax_field101
2397 ,tax_field102 => out_rec.tax_field102
2398 ,tax_field103 => out_rec.tax_field103
2399 ,tax_field104 => out_rec.tax_field104
2400 ,tax_field105 => out_rec.tax_field105
2401 ,tax_field106 => out_rec.tax_field106
2402 ,tax_field107 => out_rec.tax_field107
2403 ,tax_field108 => out_rec.tax_field108
2404 ,tax_field109 => out_rec.tax_field109
2405 ,tax_field110 => out_rec.tax_field110
2406 ,tax_field111 => out_rec.tax_field111
2407 ,tax_field112 => out_rec.tax_field112
2408 ,tax_field113 => out_rec.tax_field113
2409 ,tax_field114 => out_rec.tax_field114
2410 ,tax_field115 => out_rec.tax_field115
2411 ,tax_field116 => out_rec.tax_field116
2412 ,tax_field117 => out_rec.tax_field117
2413 ,tax_field118 => out_rec.tax_field118
2414 ,tax_field119 => out_rec.tax_field119
2415 ,tax_field120 => out_rec.tax_field120
2416 ,tax_field121 => out_rec.tax_field121
2417 ,tax_field122 => out_rec.tax_field122
2418 ,tax_field123 => out_rec.tax_field123
2419 ,tax_field124 => out_rec.tax_field124
2420 ,tax_field125 => out_rec.tax_field125
2421 ,tax_field126 => out_rec.tax_field126
2422 ,tax_field127 => out_rec.tax_field127
2423 ,tax_field128 => out_rec.tax_field128
2424 ,tax_field129 => out_rec.tax_field129
2425 ,tax_field130 => out_rec.tax_field130
2426 ,tax_field131 => out_rec.tax_field131
2427 ,tax_field132 => out_rec.tax_field132
2428 ,tax_field133 => out_rec.tax_field133
2429 ,tax_field134 => out_rec.tax_field134
2430 ,tax_field135 => out_rec.tax_field135
2431 ,tax_field136 => out_rec.tax_field136
2432 ,tax_field137 => out_rec.tax_field137
2433 ,tax_field138 => out_rec.tax_field138
2434 ,tax_field139 => out_rec.tax_field139
2435 ,tax_field140 => out_rec.tax_field140
2436 ,tax_field141 => out_rec.tax_field141
2437 ,tax_field142 => out_rec.tax_field142
2438 ,tax_field143 => out_rec.tax_field143
2439 ,tax_field144 => out_rec.tax_field144
2440 ,tax_field145 => out_rec.tax_field145
2441 ,tax_field146 => out_rec.tax_field146
2442 ,tax_field147 => out_rec.tax_field147
2443 ,tax_field148 => out_rec.tax_field148
2444 ,tax_field149 => out_rec.tax_field149
2445 ,tax_field150 => out_rec.tax_field150
2446 ,tax_update_allowed => out_rec.tax_update_allowed
2447 ,p_person_id => p_person_id
2448 ,p_assignment_id => p_assignment_id
2449 ,p_legislation_code => l_legislation_code
2450 ,p_effective_date => p_effective_date
2451 );
2452 --
2453 end if;
2454 hr_utility.set_location('Leaving: '||l_proc,1000);
2455 --
2456
2457 end onerow;
2458 --
2459 procedure mntquery
2460 (resultset IN OUT NOCOPY maintab
2461 ,p_person_id IN number
2462 ,p_assignment_id IN number default null
2463 ,p_effective_date IN date
2464 ,p_template_name IN varchar2
2465 ,p_block_name IN varchar2
2466 ,p_legislation_code IN varchar2
2467 ) is
2468 l_out_rec mainrec;
2469 begin
2470 onerow(l_out_rec
2471 ,p_person_id
2472 ,p_assignment_id
2473 ,p_effective_date
2474 ,p_template_name
2475 ,p_block_name
2476 ,p_legislation_code
2477 );
2478 resultset(1):=l_out_rec;
2479 end mntquery;
2480 --
2481 end per_qh_maintain_query;