[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
808:
809: /* CURSOR all_people_f_cursor_n IS
810: select paf.assignment_id ,
811: paf.effective_start_date
812: from per_people_f ppf ,
813: per_person_types ppt ,
814: per_people_extra_info ppei,
815: per_assignments_f paf
816: where ppf.person_type_id = ppt.person_type_id
819: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
820: and ppf.effective_end_date >=
821: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
822: and ppf.effective_start_date = (select MAX(effective_start_date)
823: from per_people_f
824: where person_id =
825: ppf.person_id
826: and effective_start_date <=
827: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
862: ,paf.effective_start_date
863: ,ppe.process_event_id
864: ,ppe.object_version_number
865:
866: from per_people_f ppf
867: ,per_person_types ppt
868: ,per_people_extra_info pei
869: ,pay_process_events ppe
870: ,per_all_assignments_f paf
910: select paf.assignment_id ,
911: paf.effective_start_date ,
912: ppe.process_event_id ,
913: ppe.object_version_number
914: from per_people_f ppf ,
915: per_person_types ppt ,
916: per_people_extra_info ppei,
917: pay_process_events ppe ,
918: per_assignments_f paf
922: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
923: and ppf.effective_end_date >=
924: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
925: and ppf.effective_start_date = (select MAX(effective_start_date)
926: from per_people_f
927: where person_id =
928: ppf.person_id
929: and effective_start_date <=
930: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
964: select paf.assignment_id
965: ,paf.effective_start_date
966:
967: from per_all_assignments_f paf
968: ,per_people_f ppf
969: ,per_person_types ppt
970:
971: where ppf.person_id = paf.person_id
972: and ppf.person_type_id = ppt.person_type_id
1009: /* CURSOR ssn_cursor_n IS
1010: select paf.assignment_id ,
1011: paf.effective_start_date
1012: from per_assignments_f paf ,
1013: per_people_f ppf ,
1014: per_person_types ppt ,
1015: (select * from per_people_extra_info
1016: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1017: and pei_information12 = 'WINDSTAR'
1027: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1028: and ppf.effective_end_date >=
1029: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1030: and ppf.effective_start_date = (select MAX(effective_start_date)
1031: from per_people_f
1032: where person_id =
1033: ppf.person_id
1034: and effective_start_date <=
1035: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1067: ,ppe.process_event_id
1068: ,ppe.object_version_number
1069:
1070: from per_all_assignments_f paf
1071: ,per_people_f ppf
1072: ,per_person_types ppt
1073: ,pay_process_events ppe
1074:
1075: where ppf.person_id = paf.person_id
1116: ppe.process_event_id,
1117: ppe.object_version_number
1118:
1119: from per_assignments_f paf ,
1120: per_people_f ppf ,
1121: per_person_types ppt ,
1122: (select * from per_people_extra_info
1123: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1124: and pei_information12 = 'WINDSTAR'
1135: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1136: and ppf.effective_end_date >=
1137: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1138: and ppf.effective_start_date = (select MAX(effective_start_date)
1139: from per_people_f
1140: where person_id =
1141: ppf.person_id
1142: and effective_start_date <=
1143: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1591: ) is
1592:
1593: --
1594: -- The cursor selects all the assignment_id's from pay_process_events table
1595: -- that have a status of NOT_READ and then joins it with the per_people_f,
1596: -- and per_assignments_f table. This cursor can be coded without the parameter
1597: -- p_source_type, since the only user will be Windstar. But just to make the
1598: -- program flexible, p_source_type is used.
1599: -- 1. A status of 'N' means 'NOT_READ'
1616: ,upper(ppf.marital_status)
1617: ,ppf.person_id
1618:
1619: from per_all_assignments_f paf
1620: ,per_people_f ppf
1621: ,pay_process_events ppe
1622: ,per_person_types ppt
1623: ,per_people_extra_info pei
1624:
1672: ppf.suffix ,
1673: UPPER(ppf.marital_status),
1674: ppf.person_id
1675: from per_assignments_f paf ,
1676: per_people_f ppf ,
1677: pay_process_events ppe ,
1678: per_person_types ppt ,
1679: per_people_extra_info ppei
1680: where ppf.person_id = paf.person_id
1682: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1683: and ppf.effective_end_date >= TO_DATE(('01/01/' ||
1684: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1685: and ppf.effective_start_date = (select MAX(effective_start_date)
1686: from per_people_f
1687: where person_id = ppf.person_id
1688: and effective_start_date <=
1689: TO_DATE(('12/31/' ||
1690: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1714: order by ppf.person_id;
1715: */
1716:
1717: --
1718: -- The cursor(written below) per_people_f_cursor selects the details of all the
1719: -- persons that are to be processed by Windstar. Basically pei_information12
1720: -- = 'WINDSTAR' tells that the particular person will be processed by Windstar
1721: --
1722:
1719: -- persons that are to be processed by Windstar. Basically pei_information12
1720: -- = 'WINDSTAR' tells that the particular person will be processed by Windstar
1721: --
1722:
1723: cursor per_people_f_cursor(p_effective_date in date) is
1724: select ppf.last_name ,
1725: ppf.first_name ,
1726: ppf.middle_names ,
1727: ppf.national_identifier ,
1730: ppf.title ,
1731: ppf.suffix ,
1732: UPPER(ppf.marital_status) ,
1733: ppf.person_id
1734: from per_people_f ppf ,
1735: per_person_types ppt ,
1736: per_people_extra_info ppei
1737: where ppf.person_type_id = ppt.person_type_id
1738: and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
1740: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1741: and ppf.effective_end_date >=
1742: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1743: and ppf.effective_start_date = (select MAX(effective_start_date)
1744: from per_people_f
1745: where person_id =
1746: ppf.person_id
1747: and effective_start_date <=
1748: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1771: ppf.title ,
1772: ppf.suffix ,
1773: UPPER(ppf.marital_status) ,
1774: ppf.person_id
1775: from per_people_f ppf ,
1776: per_person_types ppt ,
1777: (select * from per_people_extra_info
1778: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1779: and pei_information12 = 'WINDSTAR'
1787: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1788: and ppf.effective_end_date >=
1789: TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1790: and ppf.effective_start_date = (select MAX(effective_start_date)
1791: from per_people_f
1792: where person_id =
1793: ppf.person_id
1794: and effective_start_date <=
1795: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1911: and UPPER(hrl.lookup_code) = UPPER(p_country_code)
1912: and rownum < 2;
1913:
1914: t_people_rec t_people_rec_type ;
1915: l_last_name per_people_f.last_name%type ;
1916: l_first_name per_people_f.first_name%type ;
1917: l_middle_names per_people_f.middle_names%type ;
1918: l_national_identifier per_people_f.national_identifier%type ;
1919: l_employee_number per_people_f.employee_number%type ;
1912: and rownum < 2;
1913:
1914: t_people_rec t_people_rec_type ;
1915: l_last_name per_people_f.last_name%type ;
1916: l_first_name per_people_f.first_name%type ;
1917: l_middle_names per_people_f.middle_names%type ;
1918: l_national_identifier per_people_f.national_identifier%type ;
1919: l_employee_number per_people_f.employee_number%type ;
1920: l_date_of_birth per_people_f.date_of_birth%type ;
1913:
1914: t_people_rec t_people_rec_type ;
1915: l_last_name per_people_f.last_name%type ;
1916: l_first_name per_people_f.first_name%type ;
1917: l_middle_names per_people_f.middle_names%type ;
1918: l_national_identifier per_people_f.national_identifier%type ;
1919: l_employee_number per_people_f.employee_number%type ;
1920: l_date_of_birth per_people_f.date_of_birth%type ;
1921: l_title per_people_f.title%type ;
1914: t_people_rec t_people_rec_type ;
1915: l_last_name per_people_f.last_name%type ;
1916: l_first_name per_people_f.first_name%type ;
1917: l_middle_names per_people_f.middle_names%type ;
1918: l_national_identifier per_people_f.national_identifier%type ;
1919: l_employee_number per_people_f.employee_number%type ;
1920: l_date_of_birth per_people_f.date_of_birth%type ;
1921: l_title per_people_f.title%type ;
1922: l_suffix per_people_f.suffix%type ;
1915: l_last_name per_people_f.last_name%type ;
1916: l_first_name per_people_f.first_name%type ;
1917: l_middle_names per_people_f.middle_names%type ;
1918: l_national_identifier per_people_f.national_identifier%type ;
1919: l_employee_number per_people_f.employee_number%type ;
1920: l_date_of_birth per_people_f.date_of_birth%type ;
1921: l_title per_people_f.title%type ;
1922: l_suffix per_people_f.suffix%type ;
1923: l_person_id per_people_f.person_id%type ;
1916: l_first_name per_people_f.first_name%type ;
1917: l_middle_names per_people_f.middle_names%type ;
1918: l_national_identifier per_people_f.national_identifier%type ;
1919: l_employee_number per_people_f.employee_number%type ;
1920: l_date_of_birth per_people_f.date_of_birth%type ;
1921: l_title per_people_f.title%type ;
1922: l_suffix per_people_f.suffix%type ;
1923: l_person_id per_people_f.person_id%type ;
1924: l_marital_status per_people_f.marital_status%type ;
1917: l_middle_names per_people_f.middle_names%type ;
1918: l_national_identifier per_people_f.national_identifier%type ;
1919: l_employee_number per_people_f.employee_number%type ;
1920: l_date_of_birth per_people_f.date_of_birth%type ;
1921: l_title per_people_f.title%type ;
1922: l_suffix per_people_f.suffix%type ;
1923: l_person_id per_people_f.person_id%type ;
1924: l_marital_status per_people_f.marital_status%type ;
1925: l_assignment_id per_assignments_f.assignment_id%type ;
1918: l_national_identifier per_people_f.national_identifier%type ;
1919: l_employee_number per_people_f.employee_number%type ;
1920: l_date_of_birth per_people_f.date_of_birth%type ;
1921: l_title per_people_f.title%type ;
1922: l_suffix per_people_f.suffix%type ;
1923: l_person_id per_people_f.person_id%type ;
1924: l_marital_status per_people_f.marital_status%type ;
1925: l_assignment_id per_assignments_f.assignment_id%type ;
1926: l_county hr_locations.loc_information19%type ;
1919: l_employee_number per_people_f.employee_number%type ;
1920: l_date_of_birth per_people_f.date_of_birth%type ;
1921: l_title per_people_f.title%type ;
1922: l_suffix per_people_f.suffix%type ;
1923: l_person_id per_people_f.person_id%type ;
1924: l_marital_status per_people_f.marital_status%type ;
1925: l_assignment_id per_assignments_f.assignment_id%type ;
1926: l_county hr_locations.loc_information19%type ;
1927: l_state hr_locations.loc_information18%type ;
1920: l_date_of_birth per_people_f.date_of_birth%type ;
1921: l_title per_people_f.title%type ;
1922: l_suffix per_people_f.suffix%type ;
1923: l_person_id per_people_f.person_id%type ;
1924: l_marital_status per_people_f.marital_status%type ;
1925: l_assignment_id per_assignments_f.assignment_id%type ;
1926: l_county hr_locations.loc_information19%type ;
1927: l_state hr_locations.loc_information18%type ;
1928: l_city hr_locations.loc_information17%type ;
2008: ,p_source_type => p_source_type);
2009:
2010: elsif (p_selection_criterion = 'ALL' ) then
2011: hr_utility.set_location(l_proc, 25);
2012: open per_people_f_cursor(p_effective_date);
2013:
2014: else
2015: hr_utility.set_location(l_proc, 30);
2016: --
2068: l_telephone_number_3 := null;
2069: l_postal_code := null;
2070: l_out_mesg := null;
2071:
2072: fetch per_people_f_cursor into
2073: l_last_name
2074: ,l_first_name
2075: ,l_middle_names
2076: ,l_national_identifier
2082: ,l_person_id;
2083:
2084: hr_utility.set_location(l_proc, 50);
2085:
2086: exit when per_people_f_cursor%notfound;
2087:
2088: l_person_read_count := l_person_read_count + 1;
2089:
2090: l_work_home := 'HOME';
2397: l_warn_mesg := null;
2398: end;
2399: end loop;
2400:
2401: close per_people_f_cursor;
2402:
2403: elsif (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2404: loop
2405: begin
3164: income_code_sub_type ,
3165: constant_addl_tax
3166: from pqp_analyzed_alien_data paadat ,
3167: pqp_analyzed_alien_details paadet ,
3168: per_people_f ppf ,
3169: per_assignments_f paf
3170: where paadat.analyzed_data_id = paadet.analyzed_data_id
3171: and paadet.income_code = p_income_code
3172: and ppf.person_id = paf.person_id
3277: *****/
3278: cursor c_person_assignment(p_person_id in number) is
3279: select distinct assignment_id
3280: from per_assignments_f paf,
3281: per_people_f ppf
3282: where ppf.person_id = paf.person_id
3283: and ppf.person_id = p_person_id
3284: and ppf.effective_start_date <= TO_DATE(('12/31/' ||
3285: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3285: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3286: and ppf.effective_end_date >= TO_DATE(('01/01/' ||
3287: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3288: and ppf.effective_start_date = (select MAX(effective_start_date)
3289: from per_people_f
3290: where person_id = ppf.person_id
3291: and effective_start_date <=
3292: TO_DATE(('12/31/' ||
3293: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
3384: -- of a terminated employee.
3385: --
3386: cursor c_terminated_employee_asg(p_person_id in number) is
3387: select paf.effective_end_date
3388: from per_people_f ppf
3389: ,per_person_types ppt
3390: ,per_assignments_f paf
3391: where ppf.person_id = p_person_id
3392: and ppf.person_type_id = ppt.person_type_id
4935: --
4936: cursor c_person(p_person_id in number
4937: ,p_effective_date in date ) is
4938: select MAX(effective_end_date) effective_end_date
4939: from per_people_f ppf
4940: ,per_person_types ppt
4941: where ppf.person_id = p_person_id
4942: and ppf.person_type_id = ppt.person_type_id
4943: and ppt.system_person_type in ('EMP', 'EX_EMP') -- RLN 7039307
5038: pet.element_name element_name,
5039: pee.effective_start_date entry_start_date,
5040: NVL(pet.element_information1, ' ') element_income_code
5041: from per_assignments_f paf,
5042: per_people_f ppf,
5043: pay_element_entries_f pee,
5044: pay_element_links_f pel,
5045: pay_element_types_f pet,
5046: pay_element_classifications pec