[Home] [Help]
309: cursor csr_ni is
310: select
311: hr_ni_chk_pkg.chk_nat_id_format(per.national_identifier, 'DDDDDD-DDDDDDD') NI
312: from per_people_f per,
313: per_assignments_f asg
314: where asg.assignment_id = p_assignment_id
315: and per.person_id = asg.person_id
316: and p_effective_date between per.effective_start_date and per.effective_end_date
317: and p_effective_date between asg.effective_start_date and asg.effective_end_date;
560: cursor csr_exception_flag is
561: select pei.pei_information1
562: from per_people_f pap
563: ,per_people_extra_info pei
564: ,per_assignments_f paa
565: where paa.assignment_id = p_assignment_id
566: and paa.business_group_id = p_business_group_id
567: and paa.person_id = pap.person_id
568: and pap.person_id = pei.person_id
724: select
725: nvl(cei.cei_information13, 'N')
726: from per_people_f per,
727: per_contact_relationships ctr,
728: per_assignments_f asg,
729: per_contact_extra_info_f cei
730: where asg.assignment_id = p_assignment_id
731: and p_date_earned
732: between asg.effective_start_date and asg.effective_end_date
836: -- Bug 6784288 Bug 6825145
837: decode(ctr.cont_information2, 'Y', decode(ctr.cont_information11,'4',underaged_dpnt_flag(ctr.contact_type, per.national_identifier, p_date_earned),addtl_child_flag(ctr.contact_type, per.national_identifier, p_date_earned)), 'N') ADDTL_CHILD
838: from per_people_f per,
839: per_contact_relationships ctr,
840: per_assignments_f asg
841: where asg.assignment_id = p_assignment_id
842: and p_date_earned
843: between asg.effective_start_date and asg.effective_end_date
844: and ctr.person_id = asg.person_id
874: child_flag(per.national_identifier, p_date_earned) CHILD_FLAG,
875: nvl(pei.pei_information3,'N') FEMALE_EMP_DOC,
876: super_aged_flag(per.national_identifier, p_date_earned) SUPER_AGED_FLAG -- Bug 4124430
877: from per_people_f per,
878: per_assignments_f asg,
879: per_people_extra_info pei
880: where asg.assignment_id = p_assignment_id
881: and p_date_earned
882: between asg.effective_start_date and asg.effective_end_date
1018: select pei_information8 loss_code
1019: ,pei_information9 loss_date
1020: from per_people_extra_info pei
1021: ,per_people_f pp
1022: ,per_assignments_f paa
1023: ,per_time_periods ptp
1024: where paa.assignment_id = p_assignment_id
1025: and paa.business_group_id = p_business_group_id
1026: and pp.person_id = paa.person_id
1037: is
1038: select pei_information1 ei_exception_code
1039: ,pei_information4 ei_exception_type
1040: from per_people_extra_info pei
1041: ,per_assignments_f paa
1042: ,per_time_periods ptp
1043: where paa.assignment_id = p_assignment_id
1044: and paa.business_group_id = p_business_group_id
1045: and pei.person_id = paa.person_id
1333: ---------------------------------------------------------------------------
1334:
1335: ---------------------------------------------------------------------------
1336: -- procedure get_double_exem_amt for Bug 6716401
1337: procedure get_double_exem_amt(p_assignment_id in per_assignments_f.assignment_id%type,
1338: p_effective_year in varchar2,
1339: p_double_exm_amt out nocopy number)
1340: is
1341:
1339: p_double_exm_amt out nocopy number)
1340: is
1341:
1342:
1343: cursor csr_get_ass_act_id(p_assignment_id per_assignments_f.assignment_id%type,
1344: p_effective_year varchar2)
1345: is
1346: SELECT paa.assignment_action_id ass_act_id
1347: from pay_payroll_actions ppa,
1354: and ppa.report_qualifier = 'KR'
1355: and ppa.action_type in ('B','X')
1356: order by paa.action_sequence desc ;
1357:
1358: l_assignment_action_id per_assignments_f.assignment_id%type;
1359:
1360: Begin
1361: p_double_exm_amt := 0;
1362: Open csr_get_ass_act_id(p_assignment_id,p_effective_year);