[Home] [Help]
17: ,paf.assignment_id assignment_id
18: ,ppe.description description
19:
20: from pay_process_events ppe
21: ,per_people_f ppf
22: ,per_assignments_f paf
23:
24: where ppf.person_id = p_person_id1
25: and ppf.person_id = paf.person_id
30: and ppf.effective_end_date >= to_date(('01/01/' ||
31: to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
32: and ppf.effective_start_date =
33: (select max(effective_start_date)
34: from per_people_f
35: where person_id = ppf.person_id
36: and effective_start_date <=
37: to_date(('12/31/' ||TO_CHAR(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
38: )
763: ,c_national_indentifier in varchar2
764: ,c_effective_date in date) is
765: select paf.assignment_id
766: ,paf.effective_start_date
767: from per_people_f ppf
768: ,per_person_types ppt
769: ,per_people_extra_info pei
770: ,per_all_assignments_f paf
771: where ppf.person_type_id = ppt.person_type_id
809:
810: /* CURSOR all_people_f_cursor_n IS
811: select paf.assignment_id ,
812: paf.effective_start_date
813: from per_people_f ppf ,
814: per_person_types ppt ,
815: per_people_extra_info ppei,
816: per_assignments_f paf
817: where ppf.person_type_id = ppt.person_type_id
820: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
821: and ppf.effective_end_date >=
822: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
823: and ppf.effective_start_date = (select MAX(effective_start_date)
824: from per_people_f
825: where person_id =
826: ppf.person_id
827: and effective_start_date <=
828: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
863: ,paf.effective_start_date
864: ,ppe.process_event_id
865: ,ppe.object_version_number
866:
867: from per_people_f ppf
868: ,per_person_types ppt
869: ,per_people_extra_info pei
870: ,pay_process_events ppe
871: ,per_all_assignments_f paf
912: select paf.assignment_id ,
913: paf.effective_start_date ,
914: ppe.process_event_id ,
915: ppe.object_version_number
916: from per_people_f ppf ,
917: per_person_types ppt ,
918: per_people_extra_info ppei,
919: pay_process_events ppe ,
920: per_assignments_f paf
924: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
925: and ppf.effective_end_date >=
926: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
927: and ppf.effective_start_date = (select MAX(effective_start_date)
928: from per_people_f
929: where person_id =
930: ppf.person_id
931: and effective_start_date <=
932: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
966: select paf.assignment_id
967: ,paf.effective_start_date
968:
969: from per_all_assignments_f paf
970: ,per_people_f ppf
971: ,per_person_types ppt
972:
973: where ppf.person_id = paf.person_id
974: and paf.assignment_type <> 'B' --RLN
1012: /* CURSOR ssn_cursor_n IS
1013: select paf.assignment_id ,
1014: paf.effective_start_date
1015: from per_assignments_f paf ,
1016: per_people_f ppf ,
1017: per_person_types ppt ,
1018: (select * from per_people_extra_info
1019: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1020: and pei_information12 = 'WINDSTAR'
1030: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1031: and ppf.effective_end_date >=
1032: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1033: and ppf.effective_start_date = (select MAX(effective_start_date)
1034: from per_people_f
1035: where person_id =
1036: ppf.person_id
1037: and effective_start_date <=
1038: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1070: ,ppe.process_event_id
1071: ,ppe.object_version_number
1072:
1073: from per_all_assignments_f paf
1074: ,per_people_f ppf
1075: ,per_person_types ppt
1076: ,pay_process_events ppe
1077:
1078: where ppf.person_id = paf.person_id
1120: ppe.process_event_id,
1121: ppe.object_version_number
1122:
1123: from per_assignments_f paf ,
1124: per_people_f ppf ,
1125: per_person_types ppt ,
1126: (select * from per_people_extra_info
1127: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1128: and pei_information12 = 'WINDSTAR'
1139: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1140: and ppf.effective_end_date >=
1141: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1142: and ppf.effective_start_date = (select MAX(effective_start_date)
1143: from per_people_f
1144: where person_id =
1145: ppf.person_id
1146: and effective_start_date <=
1147: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1595: ) is
1596:
1597: --
1598: -- The cursor selects all the assignment_id's from pay_process_events table
1599: -- that have a status of NOT_READ and then joins it with the per_people_f,
1600: -- and per_assignments_f table. This cursor can be coded without the parameter
1601: -- p_source_type, since the only user will be Windstar. But just to make the
1602: -- program flexible, p_source_type is used.
1603: -- 1. A status of 'N' means 'NOT_READ'
1620: ,upper(ppf.marital_status)
1621: ,ppf.person_id
1622:
1623: from per_all_assignments_f paf
1624: ,per_people_f ppf
1625: ,pay_process_events ppe
1626: ,per_person_types ppt
1627: ,per_people_extra_info pei
1628:
1676: ppf.suffix ,
1677: UPPER(ppf.marital_status),
1678: ppf.person_id
1679: from per_assignments_f paf ,
1680: per_people_f ppf ,
1681: pay_process_events ppe ,
1682: per_person_types ppt ,
1683: per_people_extra_info ppei
1684: where ppf.person_id = paf.person_id
1686: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1687: and ppf.effective_end_date >= TO_DATE(('01/01/' ||
1688: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1689: and ppf.effective_start_date = (select MAX(effective_start_date)
1690: from per_people_f
1691: where person_id = ppf.person_id
1692: and effective_start_date <=
1693: TO_DATE(('12/31/' ||
1694: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1718: order by ppf.person_id;
1719: */
1720:
1721: --
1722: -- The cursor(written below) per_people_f_cursor selects the details of all the
1723: -- persons that are to be processed by Windstar. Basically pei_information12
1724: -- = 'WINDSTAR' tells that the particular person will be processed by Windstar
1725: --
1726:
1723: -- persons that are to be processed by Windstar. Basically pei_information12
1724: -- = 'WINDSTAR' tells that the particular person will be processed by Windstar
1725: --
1726:
1727: cursor per_people_f_cursor(p_effective_date in date) is
1728: select ppf.last_name ,
1729: ppf.first_name ,
1730: ppf.middle_names ,
1731: ppf.national_identifier ,
1734: ppf.title ,
1735: ppf.suffix ,
1736: UPPER(ppf.marital_status) ,
1737: ppf.person_id
1738: from per_people_f ppf ,
1739: per_person_types ppt ,
1740: per_people_extra_info ppei
1741: where ppf.person_type_id = ppt.person_type_id
1742: and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
1744: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1745: and ppf.effective_end_date >=
1746: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1747: and ppf.effective_start_date = (select MAX(effective_start_date)
1748: from per_people_f
1749: where person_id =
1750: ppf.person_id
1751: and effective_start_date <=
1752: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1775: ppf.title ,
1776: ppf.suffix ,
1777: UPPER(ppf.marital_status) ,
1778: ppf.person_id
1779: from per_people_f ppf ,
1780: per_person_types ppt ,
1781: (select * from per_people_extra_info
1782: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1783: and pei_information12 = 'WINDSTAR'
1791: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1792: and ppf.effective_end_date >=
1793: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1794: and ppf.effective_start_date = (select MAX(effective_start_date)
1795: from per_people_f
1796: where person_id =
1797: ppf.person_id
1798: and effective_start_date <=
1799: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1915: and UPPER(hrl.lookup_code) = UPPER(p_country_code)
1916: and rownum < 2;
1917:
1918: t_people_rec t_people_rec_type ;
1919: l_last_name per_people_f.last_name%type ;
1920: l_first_name per_people_f.first_name%type ;
1921: l_middle_names per_people_f.middle_names%type ;
1922: l_national_identifier per_people_f.national_identifier%type ;
1923: l_employee_number per_people_f.employee_number%type ;
1916: and rownum < 2;
1917:
1918: t_people_rec t_people_rec_type ;
1919: l_last_name per_people_f.last_name%type ;
1920: l_first_name per_people_f.first_name%type ;
1921: l_middle_names per_people_f.middle_names%type ;
1922: l_national_identifier per_people_f.national_identifier%type ;
1923: l_employee_number per_people_f.employee_number%type ;
1924: l_date_of_birth per_people_f.date_of_birth%type ;
1917:
1918: t_people_rec t_people_rec_type ;
1919: l_last_name per_people_f.last_name%type ;
1920: l_first_name per_people_f.first_name%type ;
1921: l_middle_names per_people_f.middle_names%type ;
1922: l_national_identifier per_people_f.national_identifier%type ;
1923: l_employee_number per_people_f.employee_number%type ;
1924: l_date_of_birth per_people_f.date_of_birth%type ;
1925: l_title per_people_f.title%type ;
1918: t_people_rec t_people_rec_type ;
1919: l_last_name per_people_f.last_name%type ;
1920: l_first_name per_people_f.first_name%type ;
1921: l_middle_names per_people_f.middle_names%type ;
1922: l_national_identifier per_people_f.national_identifier%type ;
1923: l_employee_number per_people_f.employee_number%type ;
1924: l_date_of_birth per_people_f.date_of_birth%type ;
1925: l_title per_people_f.title%type ;
1926: l_suffix per_people_f.suffix%type ;
1919: l_last_name per_people_f.last_name%type ;
1920: l_first_name per_people_f.first_name%type ;
1921: l_middle_names per_people_f.middle_names%type ;
1922: l_national_identifier per_people_f.national_identifier%type ;
1923: l_employee_number per_people_f.employee_number%type ;
1924: l_date_of_birth per_people_f.date_of_birth%type ;
1925: l_title per_people_f.title%type ;
1926: l_suffix per_people_f.suffix%type ;
1927: l_person_id per_people_f.person_id%type ;
1920: l_first_name per_people_f.first_name%type ;
1921: l_middle_names per_people_f.middle_names%type ;
1922: l_national_identifier per_people_f.national_identifier%type ;
1923: l_employee_number per_people_f.employee_number%type ;
1924: l_date_of_birth per_people_f.date_of_birth%type ;
1925: l_title per_people_f.title%type ;
1926: l_suffix per_people_f.suffix%type ;
1927: l_person_id per_people_f.person_id%type ;
1928: l_marital_status per_people_f.marital_status%type ;
1921: l_middle_names per_people_f.middle_names%type ;
1922: l_national_identifier per_people_f.national_identifier%type ;
1923: l_employee_number per_people_f.employee_number%type ;
1924: l_date_of_birth per_people_f.date_of_birth%type ;
1925: l_title per_people_f.title%type ;
1926: l_suffix per_people_f.suffix%type ;
1927: l_person_id per_people_f.person_id%type ;
1928: l_marital_status per_people_f.marital_status%type ;
1929: l_assignment_id per_assignments_f.assignment_id%type ;
1922: l_national_identifier per_people_f.national_identifier%type ;
1923: l_employee_number per_people_f.employee_number%type ;
1924: l_date_of_birth per_people_f.date_of_birth%type ;
1925: l_title per_people_f.title%type ;
1926: l_suffix per_people_f.suffix%type ;
1927: l_person_id per_people_f.person_id%type ;
1928: l_marital_status per_people_f.marital_status%type ;
1929: l_assignment_id per_assignments_f.assignment_id%type ;
1930: l_county hr_locations.loc_information19%type ;
1923: l_employee_number per_people_f.employee_number%type ;
1924: l_date_of_birth per_people_f.date_of_birth%type ;
1925: l_title per_people_f.title%type ;
1926: l_suffix per_people_f.suffix%type ;
1927: l_person_id per_people_f.person_id%type ;
1928: l_marital_status per_people_f.marital_status%type ;
1929: l_assignment_id per_assignments_f.assignment_id%type ;
1930: l_county hr_locations.loc_information19%type ;
1931: l_state hr_locations.loc_information18%type ;
1924: l_date_of_birth per_people_f.date_of_birth%type ;
1925: l_title per_people_f.title%type ;
1926: l_suffix per_people_f.suffix%type ;
1927: l_person_id per_people_f.person_id%type ;
1928: l_marital_status per_people_f.marital_status%type ;
1929: l_assignment_id per_assignments_f.assignment_id%type ;
1930: l_county hr_locations.loc_information19%type ;
1931: l_state hr_locations.loc_information18%type ;
1932: l_city hr_locations.loc_information17%type ;
2012: ,p_source_type => p_source_type);
2013:
2014: elsif (p_selection_criterion = 'ALL' ) then
2015: hr_utility.set_location(l_proc, 25);
2016: open per_people_f_cursor(p_effective_date);
2017:
2018: else
2019: hr_utility.set_location(l_proc, 30);
2020: --
2072: l_telephone_number_3 := null;
2073: l_postal_code := null;
2074: l_out_mesg := null;
2075:
2076: fetch per_people_f_cursor into
2077: l_last_name
2078: ,l_first_name
2079: ,l_middle_names
2080: ,l_national_identifier
2086: ,l_person_id;
2087:
2088: hr_utility.set_location(l_proc, 50);
2089:
2090: exit when per_people_f_cursor%notfound;
2091:
2092: l_person_read_count := l_person_read_count + 1;
2093:
2094: l_work_home := 'HOME';
2401: l_warn_mesg := null;
2402: end;
2403: end loop;
2404:
2405: close per_people_f_cursor;
2406:
2407: elsif (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2408: loop
2409: begin
3168: income_code_sub_type ,
3169: constant_addl_tax
3170: from pqp_analyzed_alien_data paadat ,
3171: pqp_analyzed_alien_details paadet ,
3172: per_people_f ppf ,
3173: per_assignments_f paf
3174: where paadat.analyzed_data_id = paadet.analyzed_data_id
3175: and paadet.income_code = p_income_code
3176: and ppf.person_id = paf.person_id
3282: *****/
3283: cursor c_person_assignment(p_person_id in number) is
3284: select distinct assignment_id
3285: from per_assignments_f paf,
3286: per_people_f ppf
3287: where ppf.person_id = paf.person_id
3288: and ppf.person_id = p_person_id
3289: and paf.assignment_type <> 'B' --RLN
3290: and ppf.effective_start_date <= TO_DATE(('12/31/' ||
3291: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3292: and ppf.effective_end_date >= TO_DATE(('01/01/' ||
3293: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3294: and ppf.effective_start_date = (select MAX(effective_start_date)
3295: from per_people_f
3296: where person_id = ppf.person_id
3297: and effective_start_date <=
3298: TO_DATE(('12/31/' ||
3299: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
3390: -- of a terminated employee.
3391: --
3392: cursor c_terminated_employee_asg(p_person_id in number) is
3393: SELECT paf.effective_end_date
3394: FROM per_people_f ppf,
3395: per_person_types ppt,
3396: per_person_type_usages_f pptu,
3397: per_assignments_f paf
3398: WHERE ppf.person_id = 57504
4950: --
4951: cursor c_person(p_person_id in number
4952: ,p_effective_date in date ) is
4953: select MAX(effective_end_date) effective_end_date
4954: from per_people_f ppf
4955: ,per_person_types ppt
4956: where ppf.person_id = p_person_id
4957: and ppf.person_type_id = ppt.person_type_id
4958: and ppt.system_person_type in ('EMP', 'EX_EMP') -- RLN 7039307
5053: pet.element_name element_name,
5054: pee.effective_start_date entry_start_date,
5055: NVL(pet.element_information1, ' ') element_income_code
5056: from per_assignments_f paf,
5057: per_people_f ppf,
5058: pay_element_entries_f pee,
5059: pay_element_links_f pel,
5060: pay_element_types_f pet,
5061: pay_element_classifications pec