[Home] [Help]
477: -- per_assignments_f asg,
478: per_all_assignments_f asg,
479: per_pay_bases ppb,
480: pay_all_payrolls_f paf,
481: per_all_people_f per
482: where per.person_id = p_person_id
483:
484: /* Bug:3265142 Start: Fetching salary from per_pay_proposals for assignment_type = 'E'
485: and per_assignment_extra_info.aei_information6 assignment_type = 'B' (Refer UNIONed-query).
528: per_all_assignments_f asg,
529: per_assignment_extra_info aei,
530: per_pay_bases ppb,
531: pay_all_payrolls_f paf,
532: per_all_people_f per
533: where per.person_id = p_person_id
534: and asg.assignment_type = 'B'
535: and asg.person_id = per.person_id
536: -- 4455689
547: and l_date between per.effective_start_date and per.effective_end_date
548: and asg.assignment_id = aei.assignment_id
549: and not exists (select 1
550: from per_all_assignments_f asg,
551: per_all_people_f per
552: where per.person_id = p_person_id
553: and asg.assignment_type = 'E'
554: and asg.person_id = per.person_id
555: and l_date between asg.effective_start_date and asg.effective_end_date
1670: -- FONM
1671: --
1672: cursor c_per is
1673: select per.date_of_birth
1674: from per_all_people_f per
1675: where per.person_id = p_person_id
1676: and per.business_group_id = p_business_group_id
1677: and l_effective_date
1678: between per.effective_start_date
1681:
1682: cursor c_per_spouse(l_person_id number) is
1683: select per.date_of_birth
1684: from per_contact_relationships ctr,
1685: per_all_people_f per
1686: where ctr.person_id = l_person_id
1687: and per.person_id = ctr.contact_person_id
1688: and per.business_group_id = p_business_group_id
1689: and ctr.personal_flag = 'Y'
1698: --
1699: cursor c_per_depen_first(l_person_id number) is
1700: select per.date_of_birth
1701: from per_contact_relationships ctr,
1702: per_all_people_f per
1703: where ctr.person_id = l_person_id
1704: and per.person_id = ctr.contact_person_id
1705: and per.business_group_id = p_business_group_id
1706: and ctr.personal_flag = 'Y'
1716: --
1717: cursor c_per_child_first(l_person_id number) is
1718: select per.date_of_birth
1719: from per_contact_relationships ctr,
1720: per_all_people_f per
1721: where ctr.person_id = l_person_id
1722: and per.person_id = ctr.contact_person_id
1723: and per.business_group_id = p_business_group_id
1724: and ctr.personal_flag = 'Y'
1734: --
1735: cursor c_per_depen_oldest(l_person_id number) is
1736: select min(per.date_of_birth)
1737: from per_contact_relationships ctr,
1738: per_all_people_f per
1739: where ctr.person_id = l_person_id
1740: and per.person_id = ctr.contact_person_id
1741: and per.business_group_id = p_business_group_id
1742: and ctr.personal_flag = 'Y'
1752: --
1753: cursor c_per_child_oldest(l_person_id number) is
1754: select min(per.date_of_birth)
1755: from per_contact_relationships ctr,
1756: per_all_people_f per
1757: where ctr.person_id = l_person_id
1758: and per.person_id = ctr.contact_person_id
1759: and per.business_group_id = p_business_group_id
1760: and ctr.personal_flag = 'Y'
1770: --
1771: cursor c_per_depen_young(l_person_id number) is
1772: select max(per.date_of_birth)
1773: from per_contact_relationships ctr,
1774: per_all_people_f per
1775: where ctr.person_id = l_person_id
1776: and per.person_id = ctr.contact_person_id
1777: and per.business_group_id = p_business_group_id
1778: and ctr.personal_flag = 'Y'
1788: --
1789: cursor c_per_child_young(l_person_id number) is
1790: select max(per.date_of_birth)
1791: from per_contact_relationships ctr,
1792: per_all_people_f per
1793: where ctr.person_id = l_person_id
1794: and per.business_group_id = p_business_group_id
1795: and per.person_id = ctr.contact_person_id
1796: and ctr.personal_flag = 'Y'
2263: cursor c_person is
2264: select pps.date_start,
2265: pps.adjusted_svc_date,
2266: ppf.original_date_of_hire
2267: from per_all_people_f ppf,
2268: per_periods_of_service pps
2269: where pps.person_id(+) = ppf.person_id
2270: and ppf.person_id = p_person_id
2271: and ppf.business_group_id = p_business_group_id