[Home] [Help]
311: cursor csr_ni is
312: select
313: hr_ni_chk_pkg.chk_nat_id_format(per.national_identifier, 'DDDDDD-DDDDDDD') NI
314: from per_people_f per,
315: per_assignments_f asg
316: where asg.assignment_id = p_assignment_id
317: and per.person_id = asg.person_id
318: and p_effective_date between per.effective_start_date and per.effective_end_date
319: and p_effective_date between asg.effective_start_date and asg.effective_end_date;
662: cursor csr_exception_flag is
663: select pei.pei_information1
664: from per_people_f pap
665: ,per_people_extra_info pei
666: ,per_assignments_f paa
667: where paa.assignment_id = p_assignment_id
668: and paa.business_group_id = p_business_group_id
669: and paa.person_id = pap.person_id
670: and pap.person_id = pei.person_id
957: select
958: nvl(cei.cei_information13, 'N')
959: from per_people_f per,
960: per_contact_relationships ctr,
961: per_assignments_f asg,
962: per_contact_extra_info_f cei
963: where asg.assignment_id = p_assignment_id
964: and p_date_earned
965: between asg.effective_start_date and asg.effective_end_date
1124: decode(ctr.cont_information2, 'Y', decode(addtl_child_flag(ctr.contact_type, per.national_identifier, ctr.cont_information4, ctr.cont_information11, ctr.cont_information15, p_date_earned),'Y','Y','N'), 'N') ADDTL_CHILD,
1125: decode(ctr.cont_information2, 'Y', single_parent_child_flag(ctr.cont_information11,ctr.contact_type,per.national_identifier, p_date_earned), 'N') SINGLE_PARENT_CHILD_FLAG
1126: from per_people_f per,
1127: per_contact_relationships ctr,
1128: per_assignments_f asg
1129: where asg.assignment_id = p_assignment_id
1130: and p_date_earned
1131: between asg.effective_start_date and asg.effective_end_date
1132: and ctr.person_id = asg.person_id
1162: child_flag('0','E',per.national_identifier, p_date_earned) CHILD_FLAG,
1163: nvl(pei.pei_information3,'N') FEMALE_EMP_DOC,
1164: super_aged_flag(per.national_identifier, p_date_earned) SUPER_AGED_FLAG -- Bug 4124430
1165: from per_people_f per,
1166: per_assignments_f asg,
1167: per_people_extra_info pei
1168: where asg.assignment_id = p_assignment_id
1169: and p_date_earned
1170: between asg.effective_start_date and asg.effective_end_date
1329: select pei_information8 loss_code
1330: ,pei_information9 loss_date
1331: from per_people_extra_info pei
1332: ,per_people_f pp
1333: ,per_assignments_f paa
1334: ,per_time_periods ptp
1335: where paa.assignment_id = p_assignment_id
1336: and paa.business_group_id = p_business_group_id
1337: and pp.person_id = paa.person_id
1348: is
1349: select pei_information1 ei_exception_code
1350: ,pei_information4 ei_exception_type
1351: from per_people_extra_info pei
1352: ,per_assignments_f paa
1353: ,per_time_periods ptp
1354: where paa.assignment_id = p_assignment_id
1355: and paa.business_group_id = p_business_group_id
1356: and pei.person_id = paa.person_id
1674: ---------------------------------------------------------------------------
1675:
1676: ---------------------------------------------------------------------------
1677: -- procedure get_double_exem_amt for Bug 6716401
1678: procedure get_double_exem_amt(p_assignment_id in per_assignments_f.assignment_id%type,
1679: p_effective_year in varchar2,
1680: p_double_exm_amt out nocopy number)
1681: is
1682:
1680: p_double_exm_amt out nocopy number)
1681: is
1682:
1683:
1684: cursor csr_get_ass_act_id(p_assignment_id per_assignments_f.assignment_id%type,
1685: p_effective_year varchar2)
1686: is
1687: SELECT paa.assignment_action_id ass_act_id
1688: from pay_payroll_actions ppa,
1695: and ppa.report_qualifier = 'KR'
1696: and ppa.action_type in ('B','X')
1697: order by paa.action_sequence desc ;
1698:
1699: l_assignment_action_id per_assignments_f.assignment_id%type;
1700:
1701: Begin
1702: p_double_exm_amt := 0;
1703: Open csr_get_ass_act_id(p_assignment_id,p_effective_year);