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