[Home] [Help]
5: g_package constant varchar2(150) := 'pqp_alien_expat_taxation_pkg';
6: g_bus_grp_id number(15);
7: --
8: -- The cursor below selects the process_event_id, object_version_number,
9: -- assignment_id, description from pay_process_events with status = 'NOT_READ'
10: --
11: cursor pay_process_events_ovn_cursor(p_person_id1 in number
12: ,p_change_type1 in varchar2
13: ,p_effective_date1 in date) is
7: --
8: -- The cursor below selects the process_event_id, object_version_number,
9: -- assignment_id, description from pay_process_events with status = 'NOT_READ'
10: --
11: cursor pay_process_events_ovn_cursor(p_person_id1 in number
12: ,p_change_type1 in varchar2
13: ,p_effective_date1 in date) is
14:
15: select ppe.process_event_id process_event_id
16: ,ppe.object_version_number object_version_number
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
119: from per_people_extra_info
120: where information_type = 'PER_US_ADDITIONAL_DETAILS'
121: and person_id = p_person_id );
122: --
123: -- The cursor below selects the object version number in pay_process_events table.
124: --
125: cursor c_ovn_ppe(p_process_event_id in number) is
126: select object_version_number
127: from pay_process_events
123: -- The cursor below selects the object version number in pay_process_events table.
124: --
125: cursor c_ovn_ppe(p_process_event_id in number) is
126: select object_version_number
127: from pay_process_events
128: where process_event_id = p_process_event_id;
129:
130: -- local Variable
131: l_batch_size number;
611:
612: -- ===========================================================================
613: -- Name : PQP_Process_Events_ErrorLog
614: -- Purpose : the following procedure is called from pqp_windstar_person_read.
615: -- This inserts a record in pay_process_events table
616: -- with DATA_VALIDATION_FAILED status. A record is created only
617: -- if a record for an assignment does not already exist
618: -- Arguments :
619: -- In
627: -- ===========================================================================
628: procedure PQP_Process_Events_ErrorLog
629: (p_assignment_id1 in per_assignments_f.assignment_id%type
630: ,p_effective_date1 in date
631: ,p_change_type1 in pay_process_events.change_type%type
632: ,p_status1 in pay_process_events.status%type
633: ,p_description1 in pay_process_events.description%type
634: ) is
635: --
628: procedure PQP_Process_Events_ErrorLog
629: (p_assignment_id1 in per_assignments_f.assignment_id%type
630: ,p_effective_date1 in date
631: ,p_change_type1 in pay_process_events.change_type%type
632: ,p_status1 in pay_process_events.status%type
633: ,p_description1 in pay_process_events.description%type
634: ) is
635: --
636: l_process_event_id pay_process_events.process_event_id%type;
629: (p_assignment_id1 in per_assignments_f.assignment_id%type
630: ,p_effective_date1 in date
631: ,p_change_type1 in pay_process_events.change_type%type
632: ,p_status1 in pay_process_events.status%type
633: ,p_description1 in pay_process_events.description%type
634: ) is
635: --
636: l_process_event_id pay_process_events.process_event_id%type;
637: l_object_version_number pay_process_events.object_version_number%type;
632: ,p_status1 in pay_process_events.status%type
633: ,p_description1 in pay_process_events.description%type
634: ) is
635: --
636: l_process_event_id pay_process_events.process_event_id%type;
637: l_object_version_number pay_process_events.object_version_number%type;
638: l_proc varchar2(72) := g_package||'PQP_Process_Events_ErrorLog';
639:
640: begin
633: ,p_description1 in pay_process_events.description%type
634: ) is
635: --
636: l_process_event_id pay_process_events.process_event_id%type;
637: l_object_version_number pay_process_events.object_version_number%type;
638: l_proc varchar2(72) := g_package||'PQP_Process_Events_ErrorLog';
639:
640: begin
641: --
638: l_proc varchar2(72) := g_package||'PQP_Process_Events_ErrorLog';
639:
640: begin
641: --
642: -- The procedure pqp_process_events_errorlog creates a record in pay_process_events
643: -- table, if a record for an assignment does not already exist
644: --
645: hr_utility.set_location('Entering:'||l_proc, 5);
646:
645: hr_utility.set_location('Entering:'||l_proc, 5);
646:
647: --
648: -- The following procedure pay_ppe_api.create_process_event creates a record
649: -- in the pay_process_events table
650: --
651: pay_ppe_api.create_process_event
652: (p_validate => false
653: ,p_assignment_id => p_assignment_id1
672:
673: -- ===========================================================================
674: -- Name : PQP_Process_Events_ErrorLog
675: --Purpose : the following procedure is called from pqp_windstar_person_read.
676: -- This updates a record in pay_process_events table
677: -- with DATA_VALIDATION_FAILED status.
678: --Arguments :
679: -- In
680: -- t_people_tab : PL/sql table contains the Personal details.
693: -- Out NoCopy : none
694: -- Notes : private
695: -- ===========================================================================
696: procedure pqp_process_events_errorlog
697: (p_process_event_id1 in pay_process_events.process_event_id%type
698: ,p_object_version_number1 in pay_process_events.object_version_number%type
699: ,p_status1 in pay_process_events.status%type
700: ,p_description1 in pay_process_events.description%type
701: ) is
694: -- Notes : private
695: -- ===========================================================================
696: procedure pqp_process_events_errorlog
697: (p_process_event_id1 in pay_process_events.process_event_id%type
698: ,p_object_version_number1 in pay_process_events.object_version_number%type
699: ,p_status1 in pay_process_events.status%type
700: ,p_description1 in pay_process_events.description%type
701: ) is
702: --
695: -- ===========================================================================
696: procedure pqp_process_events_errorlog
697: (p_process_event_id1 in pay_process_events.process_event_id%type
698: ,p_object_version_number1 in pay_process_events.object_version_number%type
699: ,p_status1 in pay_process_events.status%type
700: ,p_description1 in pay_process_events.description%type
701: ) is
702: --
703: -- the procedure pqp_process_events_errorlog updates a record in
696: procedure pqp_process_events_errorlog
697: (p_process_event_id1 in pay_process_events.process_event_id%type
698: ,p_object_version_number1 in pay_process_events.object_version_number%type
699: ,p_status1 in pay_process_events.status%type
700: ,p_description1 in pay_process_events.description%type
701: ) is
702: --
703: -- the procedure pqp_process_events_errorlog updates a record in
704: -- pay_process_events table. the following procedure
700: ,p_description1 in pay_process_events.description%type
701: ) is
702: --
703: -- the procedure pqp_process_events_errorlog updates a record in
704: -- pay_process_events table. the following procedure
705: -- pay_ppe_api.update_process_event updates a record
706: -- in the pay_process_events table
707: --
708:
702: --
703: -- the procedure pqp_process_events_errorlog updates a record in
704: -- pay_process_events table. the following procedure
705: -- pay_ppe_api.update_process_event updates a record
706: -- in the pay_process_events table
707: --
708:
709: l_object_version_number pay_process_events.object_version_number%type;
710: l_proc varchar2(72) := g_package||'PQP_Process_Events_ErrorLog';
705: -- pay_ppe_api.update_process_event updates a record
706: -- in the pay_process_events table
707: --
708:
709: l_object_version_number pay_process_events.object_version_number%type;
710: l_proc varchar2(72) := g_package||'PQP_Process_Events_ErrorLog';
711:
712: begin
713:
736:
737: end PQP_Process_Events_ErrorLog;
738:
739: -- ===========================================================================
740: -- Name : Insert_Pay_Process_Events
741: -- Purpose : The following procedure is called from pqp_windstar_person_read.
742: -- This inserts a record in pay_process_events table.
743: -- Arguments :
744: -- In
738:
739: -- ===========================================================================
740: -- Name : Insert_Pay_Process_Events
741: -- Purpose : The following procedure is called from pqp_windstar_person_read.
742: -- This inserts a record in pay_process_events table.
743: -- Arguments :
744: -- In
745: -- p_type 'ALL' or a valid SSN
746: -- p_effective_date Effective Date
749: --
750: -- Notes : Private
751: -- ===========================================================================
752:
753: procedure Insert_Pay_Process_Events
754: (p_type in varchar2
755: ,p_effective_date in date) is
756:
757: --
797: and c_end_date)
798: )
799: )
800: and not exists (select 1
801: from pay_process_events
802: where assignment_id = paf.assignment_id
803: and change_type = 'PQP_US_ALIEN_WINDSTAR'
804: and status in ('N', 'D')
805:
842: paf.assignment_id
843: and effective_start_date <=
844: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
845: and not exists (select 1
846: from pay_process_events
847: where assignment_id = paf.assignment_id
848: and status in ('N', 'D')
849: and change_type = 'PQP_US_ALIEN_WINDSTAR'
850: )
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
871:
872: where ppt.person_type_id = ppf.person_type_id
873: and ppt.business_group_id = ppf.business_group_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
919: where ppf.person_type_id = ppt.person_type_id
920: and ppt.system_person_type in ('EMP' , 'EX_EMP')
921: and ppf.effective_start_date <=
946: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
947: and paf.person_id = ppf.person_id
948: and paf.assignment_id = ppe.assignment_id
949: and exists (select 1
950: from pay_process_events
951: where assignment_id = paf.assignment_id
952: and status in ('D')
953: and change_type = 'PQP_US_ALIEN_WINDSTAR'
954: )
954: )
955: order by paf.assignment_id desc; */
956:
957: --
958: -- Cursor when national identifier is passed and no pay process events exists
959: --
960: cursor ssn_cursor_n (c_start_date in date
961: ,c_end_date in date
962: ,c_national_indentifier in varchar2
986: )
987: and paf.effective_end_date between ppf.effective_start_date
988: and ppf.effective_end_date
989: and not exists (select 1
990: from pay_process_events
991: where assignment_id = paf.assignment_id
992: and status in ('N', 'D')
993: and change_type = 'PQP_US_ALIEN_WINDSTAR'
994: )
1047: and ppf.person_id = ppei.person_id
1048: and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1049: and ppei.pei_information12 = 'WINDSTAR'
1050: and not exists (select 1
1051: from pay_process_events
1052: where assignment_id = paf.assignment_id
1053: and status in ('N', 'D')
1054: and change_type = 'PQP_US_ALIEN_WINDSTAR'
1055: )
1055: )
1056: order by paf.assignment_id ;
1057: */
1058: --
1059: -- Cursor when national identifier is passed and with pay process events
1060: --
1061: cursor ssn_cursor_d (c_start_date in date
1062: ,c_end_date in date
1063: ,c_national_indentifier in varchar2
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
1076: and ppf.person_type_id = ppt.person_type_id
1077: and ppf.business_group_id = ppt.business_group_id
1125: and TO_CHAR(p_effective_date, 'YYYY') <=
1126: TO_CHAR(NVL(fnd_date.canonical_to_date(pei_information13),
1127: TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
1128: ) ppei ,
1129: pay_process_events ppe
1130: where ppf.person_id = paf.person_id
1131: and ppf.person_type_id = ppt.person_type_id
1132: and ppf.national_identifier= p_type
1133: and ppt.system_person_type in ('EMP' , 'EX_EMP')
1156: and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
1157: and ppei.pei_information12 = 'WINDSTAR'
1158: and paf.assignment_id = ppe.assignment_id
1159: and exists (select 1
1160: from pay_process_events
1161: where assignment_id = paf.assignment_id
1162: and status in ('D')
1163: and change_type = 'PQP_US_ALIEN_WINDSTAR'
1164: )
1169: l_assignment_id number;
1170: l_start_date date;
1171: l_end_date date;
1172:
1173: l_proc constant varchar2(150) := g_package||'Insert_Pay_Process_Events';
1174:
1175: begin
1176: hr_utility.set_location('Entering:'||l_proc, 5);
1177: --
1307: 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1308: hr_utility.set_location('Leaving :'||l_proc, 160);
1309: hr_utility.raise_error;
1310:
1311: end insert_pay_process_events;
1312:
1313: /**************************************************************************
1314: name : address_select
1315: Purpose : the following procedure is called from pqp_windstar_person_read.
1567: -- procedure returns the person details in a PL/sql table t_people_tab.
1568: -- Arguments :
1569: -- IN
1570: -- p_selection_criterion : if the user wants to select all records,
1571: -- or the records in the PAY_PROCESS_EVENTS table,
1572: -- or a specifice national_identifier.
1573: -- p_source_type : if the req is from Windstar or some other sys.
1574: -- p_effective_date : Effective date.
1575: -- Out
1590: ,p_person_err_count out nocopy number
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'
1600: -- 2. pei_information12 is process_type. It means that the person is an alien
1601: -- and has to be processed by WINDSTAR
1602: --
1603: cursor pay_process_events_cursor
1604: (c_year_start_date in date
1605: ,c_year_end_date in date
1606: ,p_source_type in varchar2) is
1607: select distinct
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:
1625: where ppf.person_id = paf.person_id
1658: and ppf.effective_end_date)
1659: )
1660: order by ppf.person_id;
1661:
1662: /* cursor pay_process_events_cursor(p_effective_date in date ,
1663: p_source_type in varchar2) is
1664: select distinct
1665: ppf.last_name ,
1666: ppf.first_name ,
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
1681: and ppf.effective_start_date <= TO_DATE(('12/31/' ||
1932: l_telephone_number_1 hr_locations.telephone_number_1%type ;
1933: l_telephone_number_2 hr_locations.telephone_number_2%type ;
1934: l_telephone_number_3 hr_locations.telephone_number_3%type ;
1935: l_postal_code hr_locations.postal_code%type ;
1936: l_process_event_id pay_process_events.process_event_id%type ;
1937: l_object_version_number pay_process_events.object_version_number%type;
1938: l_out_mesg out_mesg_type ;
1939:
1940: l_work_home varchar2(15) ;
1933: l_telephone_number_2 hr_locations.telephone_number_2%type ;
1934: l_telephone_number_3 hr_locations.telephone_number_3%type ;
1935: l_postal_code hr_locations.postal_code%type ;
1936: l_process_event_id pay_process_events.process_event_id%type ;
1937: l_object_version_number pay_process_events.object_version_number%type;
1938: l_out_mesg out_mesg_type ;
1939:
1940: l_work_home varchar2(15) ;
1941: l_description varchar2(250) ;
1999: --
2000: if (p_source_type = 'PQP_US_ALIEN_WINDSTAR') then
2001: hr_utility.set_location(l_proc, 15);
2002:
2003: if (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2004: hr_utility.set_location(l_proc, 20);
2005: open pay_process_events_cursor
2006: (c_year_start_date => l_year_start_date
2007: ,c_year_end_date => l_year_end_date
2001: hr_utility.set_location(l_proc, 15);
2002:
2003: if (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2004: hr_utility.set_location(l_proc, 20);
2005: open pay_process_events_cursor
2006: (c_year_start_date => l_year_start_date
2007: ,c_year_end_date => l_year_end_date
2008: ,p_source_type => p_source_type);
2009:
2014: else
2015: hr_utility.set_location(l_proc, 30);
2016: --
2017: -- Else executes when p_selection_criterion is neither 'ALL' nor
2018: -- 'PAY_PROCESS_EVENTS'. Program flow assumes that the
2019: -- NOT NULL string present in the p_selection_criterion is a valid
2020: -- National Identifier (SSN Number). p_selection_criterion string is
2021: -- NOT NULL at this stage as NULL error is checked earlier.
2022: --
2035: if (p_selection_criterion = 'ALL') then
2036:
2037: hr_utility.set_location(l_proc, 40);
2038: --
2039: -- Insert_Pay_Process_Events procedure inserts into pay_process_events
2040: -- table. The records are inserted in this table for the
2041: -- reconciliation purpose.
2042: --
2043: Insert_Pay_Process_Events
2039: -- Insert_Pay_Process_Events procedure inserts into pay_process_events
2040: -- table. The records are inserted in this table for the
2041: -- reconciliation purpose.
2042: --
2043: Insert_Pay_Process_Events
2044: (p_type => 'ALL'
2045: ,p_effective_date => p_effective_date);
2046:
2047: loop
2232:
2233: hr_utility.set_location(l_proc, 110);
2234: --
2235: -- A warning message is appended to the description field of the
2236: -- pay_process_events table, if the Non US Country code is not a
2237: -- valid tax country code
2238: --
2239: l_country_validate_count := 0;
2240: l_warn_mesg := null;
2257: end if;
2258: hr_utility.set_location(l_proc, 120);
2259: --
2260: -- Delete the current row in the PL/SQL table. Update the status
2261: -- in the pay_process_events table to reflect the status as
2262: -- DATA_VALIDATION_FAILED. The row is deleted as we do not want
2263: -- to insert the row containing an error/validation failure
2264: -- in indv_rev1_temp table.
2265: --
2264: -- in indv_rev1_temp table.
2265: --
2266: l_process_event_id := null;
2267:
2268: open pay_process_events_ovn_cursor(l_person_id
2269: ,p_source_type
2270: ,p_effective_date);
2271: loop
2272: l_description := null;
2269: ,p_source_type
2270: ,p_effective_date);
2271: loop
2272: l_description := null;
2273: fetch pay_process_events_ovn_cursor
2274: into
2275: l_process_event_id ,
2276: l_object_version_number ,
2277: l_assignment_id ,
2277: l_assignment_id ,
2278: l_description ;
2279: hr_utility.set_location(l_proc, 130);
2280:
2281: exit when pay_process_events_ovn_cursor%notfound;
2282:
2283: if (l_out_mesg is null) then
2284: --
2285: -- l_out_mesg = NULL means that there was no failure. Increment
2303: else
2304: --
2305: -- Since l_out_mesg is NOT NULL, that means an error was
2306: -- detected. We therefore change the status of the
2307: -- pay_process_events table to 'D' meaning DATA_VALIDATION_FAILED
2308: --
2309: pqp_process_events_errorlog
2310: (p_process_event_id1 => l_process_event_id
2311: ,p_object_version_number1 => l_object_version_number
2317: hr_utility.set_location(l_proc, 150);
2318:
2319: end loop;
2320:
2321: close pay_process_events_ovn_cursor;
2322:
2323: if (l_out_mesg is null) then
2324: i := i + 1;
2325: hr_utility.set_location(l_proc, 160);
2352: if (t_people_tab.exists(i)) then
2353: t_people_tab.delete(i) ;
2354: l_person_err_count := l_person_err_count + 1;
2355: end if;
2356: if (pay_process_events_ovn_cursor%isopen = true) then
2357: close pay_process_events_ovn_cursor;
2358: end if;
2359:
2360: l_process_event_id := null;
2353: t_people_tab.delete(i) ;
2354: l_person_err_count := l_person_err_count + 1;
2355: end if;
2356: if (pay_process_events_ovn_cursor%isopen = true) then
2357: close pay_process_events_ovn_cursor;
2358: end if;
2359:
2360: l_process_event_id := null;
2361:
2358: end if;
2359:
2360: l_process_event_id := null;
2361:
2362: open pay_process_events_ovn_cursor(l_person_id
2363: ,p_source_type
2364: ,p_effective_date);
2365: loop
2366: fetch pay_process_events_ovn_cursor
2362: open pay_process_events_ovn_cursor(l_person_id
2363: ,p_source_type
2364: ,p_effective_date);
2365: loop
2366: fetch pay_process_events_ovn_cursor
2367: into l_process_event_id
2368: ,l_object_version_number
2369: ,l_assignment_id
2370: ,l_description;
2370: ,l_description;
2371:
2372: hr_utility.set_location(l_proc, 190);
2373:
2374: exit when pay_process_events_ovn_cursor%notfound;
2375:
2376: pqp_process_events_errorlog
2377: (p_process_event_id1 => l_process_event_id
2378: ,p_object_version_number1 => l_object_version_number
2381: ' ' ||sqlerrm, 1, 240)
2382: );
2383:
2384: end loop;
2385: close pay_process_events_ovn_cursor;
2386: --
2387: -- for wf notification consolidation
2388: --
2389: if l_process_event_id is not null then
2399: end loop;
2400:
2401: close per_people_f_cursor;
2402:
2403: elsif (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2404: loop
2405: begin
2406: l_last_name := null;
2407: l_first_name := null;
2425: l_telephone_number_3 := null;
2426: l_postal_code := null;
2427: l_out_mesg := null;
2428:
2429: fetch pay_process_events_cursor
2430: into l_last_name
2431: ,l_first_name
2432: ,l_middle_names
2433: ,l_national_identifier
2439: ,l_person_id;
2440:
2441: hr_utility.set_location(l_proc, 200);
2442:
2443: exit when pay_process_events_cursor%notfound;
2444:
2445: l_person_read_count := l_person_read_count + 1;
2446: l_work_home := 'HOME';
2447:
2563: end loop;
2564: end if;
2565: hr_utility.set_location(l_proc, 260);
2566: -- A warning message is appended to the description field of
2567: -- the pay_process_events table, if the Non US Country code
2568: -- is not a valid tax country code
2569:
2570: l_country_validate_count := 0;
2571: l_warn_mesg := null;
2586: end if;
2587: end if;
2588: hr_utility.set_location(l_proc, 270);
2589: -- Delete the current row in the PL/SQL table. Update the status in
2590: -- the pay_process_events table to reflect the status as DATA_VALIDATION_FAILED.
2591: -- The row is deleted as we do not want to insert the row containing
2592: -- an error/validation failure in indv_rev1_temp table.
2593:
2594: l_process_event_id := null;
2592: -- an error/validation failure in indv_rev1_temp table.
2593:
2594: l_process_event_id := null;
2595:
2596: open pay_process_events_ovn_cursor(l_person_id ,
2597: p_source_type ,
2598: p_effective_date);
2599: loop
2600:
2598: p_effective_date);
2599: loop
2600:
2601: l_description := null;
2602: fetch pay_process_events_ovn_cursor into
2603: l_process_event_id ,
2604: l_object_version_number ,
2605: l_assignment_id ,
2606: l_description ;
2605: l_assignment_id ,
2606: l_description ;
2607: hr_utility.set_location(l_proc, 280);
2608:
2609: exit when pay_process_events_ovn_cursor%notfound;
2610:
2611: if (l_out_mesg is null) then
2612: hr_utility.set_location(l_proc, 290);
2613: -- l_out_mesg = NULL means that there was no failure.
2630: else
2631: hr_utility.set_location(l_proc, 310);
2632: -- Since l_out_mesg is NOT NULL, that means an error was
2633: -- detected. We therefore change the status of the
2634: -- pay_process_events table to 'D' meaning DATA_VALIDATION_FAILED
2635: --
2636: pqp_process_events_errorlog
2637: (p_process_event_id1 => l_process_event_id
2638: ,p_object_version_number1 => l_object_version_number
2641: l_description, 1, 240)
2642: );
2643: end if;
2644: end loop;
2645: close pay_process_events_ovn_cursor;
2646: if (l_out_mesg is null) then
2647: i := i + 1;
2648: hr_utility.set_location(l_proc, 320);
2649: else
2673: end if;
2674: --
2675: l_out_mesg := SUBSTR('Oralce Error ' || TO_CHAR(sqlcode) ||
2676: sqlerrm, 1, 240);
2677: if (pay_process_events_ovn_cursor%isopen = true) then
2678: close pay_process_events_ovn_cursor;
2679: end if;
2680: l_process_event_id := null;
2681:
2674: --
2675: l_out_mesg := SUBSTR('Oralce Error ' || TO_CHAR(sqlcode) ||
2676: sqlerrm, 1, 240);
2677: if (pay_process_events_ovn_cursor%isopen = true) then
2678: close pay_process_events_ovn_cursor;
2679: end if;
2680: l_process_event_id := null;
2681:
2682: open pay_process_events_ovn_cursor(l_person_id ,
2678: close pay_process_events_ovn_cursor;
2679: end if;
2680: l_process_event_id := null;
2681:
2682: open pay_process_events_ovn_cursor(l_person_id ,
2683: p_source_type ,
2684: p_effective_date);
2685: loop
2686: fetch pay_process_events_ovn_cursor into
2682: open pay_process_events_ovn_cursor(l_person_id ,
2683: p_source_type ,
2684: p_effective_date);
2685: loop
2686: fetch pay_process_events_ovn_cursor into
2687: l_process_event_id ,
2688: l_object_version_number ,
2689: l_assignment_id ,
2690: l_description ;
2689: l_assignment_id ,
2690: l_description ;
2691: hr_utility.set_location(l_proc, 350);
2692:
2693: exit when pay_process_events_ovn_cursor%notfound;
2694: pqp_process_events_errorlog
2695: (
2696: p_process_event_id1 => l_process_event_id ,
2697: p_object_version_number1 => l_object_version_number ,
2698: p_status1 => 'D' ,
2699: p_description1 => l_out_mesg
2700: );
2701: end loop;
2702: close pay_process_events_ovn_cursor;
2703:
2704: if l_process_event_id is not null then
2705: l_err_count := l_err_count+1;
2706: t_error_tab(l_err_count).person_id := t_people_tab(i).person_id;
2712: end; -- exception
2713:
2714: end loop;
2715:
2716: close pay_process_events_cursor;
2717:
2718: else
2719: -- Executing the code for a particular National Identifier
2720: hr_utility.set_location(l_proc, 360);
2718: else
2719: -- Executing the code for a particular National Identifier
2720: hr_utility.set_location(l_proc, 360);
2721: /******
2722: insert_pay_process_events procedure inserts into pay_process_events
2723: table. the records are inserted in this table for the reconciliation purpose.
2724: *****/
2725: insert_pay_process_events(p_selection_criterion ,
2726: p_effective_date );
2721: /******
2722: insert_pay_process_events procedure inserts into pay_process_events
2723: table. the records are inserted in this table for the reconciliation purpose.
2724: *****/
2725: insert_pay_process_events(p_selection_criterion ,
2726: p_effective_date );
2727: loop
2728: begin
2729: l_last_name := null;
2882: end loop;
2883: end if;
2884: hr_utility.set_location(l_proc, 430);
2885: /*****
2886: A warning message is appended to the description field of the pay_process_events
2887: table, if the Non US Country code is not a valid tax country code
2888: *****/
2889: l_country_validate_count := 0;
2890: l_warn_mesg := null;
2905: hr_utility.set_location(l_proc, 440);
2906:
2907: /*****
2908: delete the current row in the PL/sql table. update the status in the
2909: pay_process_events table to reflect the status as DATA_VALIDATION_FAILED.
2910: the row is deleted as we do not want to insert the row containing an
2911: error/validation failure in indv_rev1_temp table.
2912: *****/
2913: l_process_event_id := null;
2911: error/validation failure in indv_rev1_temp table.
2912: *****/
2913: l_process_event_id := null;
2914:
2915: open pay_process_events_ovn_cursor(l_person_id ,
2916: p_source_type ,
2917: p_effective_date);
2918: loop
2919:
2917: p_effective_date);
2918: loop
2919:
2920: l_description := null;
2921: fetch pay_process_events_ovn_cursor into
2922: l_process_event_id ,
2923: l_object_version_number ,
2924: l_assignment_id ,
2925: l_description ;
2924: l_assignment_id ,
2925: l_description ;
2926: hr_utility.set_location(l_proc, 450);
2927:
2928: exit when pay_process_events_ovn_cursor%notfound;
2929:
2930: if (l_out_mesg is null) then
2931: hr_utility.set_location(l_proc, 460);
2932: /*****
2950: else
2951: hr_utility.set_location(l_proc, 480);
2952: /*****
2953: Since l_out_mesg is not null, that means an error was detected. We therefore
2954: change the status of the pay_process_events table to 'D' meaning
2955: DATA_VALIDATION_FAILED
2956: *****/
2957: pqp_process_events_errorlog
2958: (
2965: );
2966: end if;
2967: end loop;
2968:
2969: close pay_process_events_ovn_cursor;
2970: if (l_out_mesg is null) then
2971: i := i + 1;
2972: hr_utility.set_location(l_proc, 490);
2973: else
2995: l_person_err_count := l_person_err_count + 1;
2996: end if;
2997: l_out_mesg := SUBSTR('Oracle Error ' || TO_CHAR(sqlcode) ||
2998: sqlerrm, 1, 240);
2999: if (pay_process_events_ovn_cursor%isopen = true) then
3000: close pay_process_events_ovn_cursor;
3001: end if;
3002:
3003: l_process_event_id := null;
2996: end if;
2997: l_out_mesg := SUBSTR('Oracle Error ' || TO_CHAR(sqlcode) ||
2998: sqlerrm, 1, 240);
2999: if (pay_process_events_ovn_cursor%isopen = true) then
3000: close pay_process_events_ovn_cursor;
3001: end if;
3002:
3003: l_process_event_id := null;
3004:
3001: end if;
3002:
3003: l_process_event_id := null;
3004:
3005: open pay_process_events_ovn_cursor(l_person_id ,
3006: p_source_type ,
3007: p_effective_date);
3008: loop
3009: fetch pay_process_events_ovn_cursor into
3005: open pay_process_events_ovn_cursor(l_person_id ,
3006: p_source_type ,
3007: p_effective_date);
3008: loop
3009: fetch pay_process_events_ovn_cursor into
3010: l_process_event_id ,
3011: l_object_version_number ,
3012: l_assignment_id ,
3013: l_description ;
3012: l_assignment_id ,
3013: l_description ;
3014: hr_utility.set_location(l_proc, 520);
3015:
3016: exit when pay_process_events_ovn_cursor%notfound;
3017: pqp_process_events_errorlog
3018: (
3019: p_process_event_id1 => l_process_event_id ,
3020: p_object_version_number1 => l_object_version_number ,
3021: p_status1 => 'D' ,
3022: p_description1 => l_out_mesg
3023: );
3024: end loop;
3025: close pay_process_events_ovn_cursor;
3026: /* Added by tmehra for wf notification consolidation */
3027: if l_process_event_id is not null then
3028: l_err_count := l_err_count+1;
3029:
3723: hr_utility.set_location(l_proc, 190);
3724: else --ELSE4}{
3725: hr_utility.set_location(l_proc, 200);
3726: -- Delete the current row in the PL/sql table. update the
3727: -- status in the pay_process_events table to reflect the
3728: -- status as DATA_VALIDATION_FAILED
3729: --
3730: if (pay_process_events_ovn_cursor%isopen = true) then
3731: close pay_process_events_ovn_cursor;
3726: -- Delete the current row in the PL/sql table. update the
3727: -- status in the pay_process_events table to reflect the
3728: -- status as DATA_VALIDATION_FAILED
3729: --
3730: if (pay_process_events_ovn_cursor%isopen = true) then
3731: close pay_process_events_ovn_cursor;
3732: end if;
3733:
3734: l_process_event_id := null;
3727: -- status in the pay_process_events table to reflect the
3728: -- status as DATA_VALIDATION_FAILED
3729: --
3730: if (pay_process_events_ovn_cursor%isopen = true) then
3731: close pay_process_events_ovn_cursor;
3732: end if;
3733:
3734: l_process_event_id := null;
3735:
3732: end if;
3733:
3734: l_process_event_id := null;
3735:
3736: open pay_process_events_ovn_cursor(l_person_id ,
3737: p_source_type ,
3738: p_effective_date );
3739: loop
3740: fetch pay_process_events_ovn_cursor
3736: open pay_process_events_ovn_cursor(l_person_id ,
3737: p_source_type ,
3738: p_effective_date );
3739: loop
3740: fetch pay_process_events_ovn_cursor
3741: into l_process_event_id
3742: ,l_object_version_number
3743: ,l_assignment_id
3744: ,l_description;
3744: ,l_description;
3745:
3746: hr_utility.set_location(l_proc, 210);
3747:
3748: exit when pay_process_events_ovn_cursor%notfound;
3749:
3750: pqp_process_events_errorlog
3751: (
3752: p_process_event_id1 => l_process_event_id ,
3758: );
3759:
3760: end loop;
3761:
3762: if (pay_process_events_ovn_cursor%isopen = true) then
3763: close pay_process_events_ovn_cursor;
3764: end if;
3765:
3766: /* Added by tmehra for wf notification consolidation */
3759:
3760: end loop;
3761:
3762: if (pay_process_events_ovn_cursor%isopen = true) then
3763: close pay_process_events_ovn_cursor;
3764: end if;
3765:
3766: /* Added by tmehra for wf notification consolidation */
3767: if l_process_event_id is not null then
3783: close c_analyzed_data;
3784: exception
3785: when OTHERS then
3786: hr_utility.set_location(l_proc, 270);
3787: if (pay_process_events_ovn_cursor%isopen = true) then
3788: close pay_process_events_ovn_cursor;
3789: end if;
3790: if (c_analyzed_data%isopen = true) then
3791: close c_analyzed_data;
3784: exception
3785: when OTHERS then
3786: hr_utility.set_location(l_proc, 270);
3787: if (pay_process_events_ovn_cursor%isopen = true) then
3788: close pay_process_events_ovn_cursor;
3789: end if;
3790: if (c_analyzed_data%isopen = true) then
3791: close c_analyzed_data;
3792: end if;
3794: TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
3795:
3796: l_process_event_id := null;
3797:
3798: open pay_process_events_ovn_cursor(l_person_id ,
3799: p_source_type ,
3800: p_effective_date );
3801: loop --LOOP3{
3802: fetch pay_process_events_ovn_cursor into
3798: open pay_process_events_ovn_cursor(l_person_id ,
3799: p_source_type ,
3800: p_effective_date );
3801: loop --LOOP3{
3802: fetch pay_process_events_ovn_cursor into
3803: l_process_event_id ,
3804: l_object_version_number ,
3805: l_assignment_id ,
3806: l_description ;
3803: l_process_event_id ,
3804: l_object_version_number ,
3805: l_assignment_id ,
3806: l_description ;
3807: exit when pay_process_events_ovn_cursor%notfound;
3808: hr_utility.set_location(l_proc, 280);
3809:
3810: /* Update pay_process_events table with a status of 'D' */
3811:
3806: l_description ;
3807: exit when pay_process_events_ovn_cursor%notfound;
3808: hr_utility.set_location(l_proc, 280);
3809:
3810: /* Update pay_process_events table with a status of 'D' */
3811:
3812: pqp_process_events_errorlog
3813: (
3814: p_process_event_id1 =>l_process_event_id ,
3817: p_description1 => SUBSTR(l_out_mesg, 1, 240)
3818: );
3819:
3820: end loop; --ENDLOOP3}
3821: close pay_process_events_ovn_cursor;
3822: if (t_balance_tab.exists(j)) then
3823: t_balance_tab.delete(j) ;
3824: end if;
3825:
3854: if l_income_code_count = 0 then
3855:
3856: l_process_event_id := null;
3857:
3858: open pay_process_events_ovn_cursor(l_person_id ,
3859: p_source_type ,
3860: p_effective_date );
3861: loop
3862: fetch pay_process_events_ovn_cursor into
3858: open pay_process_events_ovn_cursor(l_person_id ,
3859: p_source_type ,
3860: p_effective_date );
3861: loop
3862: fetch pay_process_events_ovn_cursor into
3863: l_process_event_id ,
3864: l_object_version_number ,
3865: l_assignment_id ,
3866: l_description ;
3863: l_process_event_id ,
3864: l_object_version_number ,
3865: l_assignment_id ,
3866: l_description ;
3867: exit when pay_process_events_ovn_cursor%notfound;
3868: hr_utility.set_location(l_proc, 280);
3869:
3870: /* Update pay_process_events table with a status of 'D' */
3871:
3866: l_description ;
3867: exit when pay_process_events_ovn_cursor%notfound;
3868: hr_utility.set_location(l_proc, 280);
3869:
3870: /* Update pay_process_events table with a status of 'D' */
3871:
3872: pqp_process_events_errorlog
3873: (p_process_event_id1 =>l_process_event_id ,
3874: p_object_version_number1=>l_object_version_number,
3886: t_error_tab(l_err_count).process_event_id := l_process_event_id;
3887:
3888: end if;
3889: /* Changes for wf notification consolidation ends */
3890: close pay_process_events_ovn_cursor;
3891: l_out_mesg := null;
3892: t_people_tab(i).validation_flag := '0';
3893: t_people_tab(i).error_mesg :=
3894: SUBSTR('No Alien Income or Forecast found' ||
4170: j := j + 1;
4171: hr_utility.set_location(l_proc, 60);
4172: else --ELSE3}{
4173: /* Delete the current row in the PL/SQL table. Update the status in the
4174: pay_process_events table to reflect the status as DATA_VALIDATION_FAILED
4175: */
4176: hr_utility.set_location(l_proc, 70);
4177:
4178: l_process_event_id := null;
4176: hr_utility.set_location(l_proc, 70);
4177:
4178: l_process_event_id := null;
4179:
4180: open pay_process_events_ovn_cursor(l_person_id ,
4181: p_source_type ,
4182: p_effective_date );
4183: loop
4184: fetch pay_process_events_ovn_cursor into
4180: open pay_process_events_ovn_cursor(l_person_id ,
4181: p_source_type ,
4182: p_effective_date );
4183: loop
4184: fetch pay_process_events_ovn_cursor into
4185: l_process_event_id ,
4186: l_object_version_number ,
4187: l_assignment_id ,
4188: l_description ;
4186: l_object_version_number ,
4187: l_assignment_id ,
4188: l_description ;
4189: hr_utility.set_location(l_proc, 80);
4190: exit when pay_process_events_ovn_cursor%notfound;
4191: /* Update pay_process_events table */
4192: pqp_process_events_errorlog
4193: (
4194: p_process_event_id1 => l_process_event_id ,
4187: l_assignment_id ,
4188: l_description ;
4189: hr_utility.set_location(l_proc, 80);
4190: exit when pay_process_events_ovn_cursor%notfound;
4191: /* Update pay_process_events table */
4192: pqp_process_events_errorlog
4193: (
4194: p_process_event_id1 => l_process_event_id ,
4195: p_object_version_number1 => l_object_version_number ,
4198: );
4199: hr_utility.set_location(l_proc, 90);
4200: hr_utility.set_location(l_proc, 100);
4201: end loop;
4202: close pay_process_events_ovn_cursor;
4203: if (t_visa_tab.exists(j)) then
4204: t_visa_tab.delete(j) ;
4205: end if;
4206: /* Added by tmehra for wf notification consolidation */
4222: end if; --ENDIF}
4223: exception
4224: when OTHERS then
4225: hr_utility.set_location(l_proc, 110);
4226: if (pay_process_events_ovn_cursor%isopen = true) then
4227: close pay_process_events_ovn_cursor;
4228: end if;
4229: l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
4230:
4223: exception
4224: when OTHERS then
4225: hr_utility.set_location(l_proc, 110);
4226: if (pay_process_events_ovn_cursor%isopen = true) then
4227: close pay_process_events_ovn_cursor;
4228: end if;
4229: l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
4230:
4231: l_process_event_id := null;
4229: l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
4230:
4231: l_process_event_id := null;
4232:
4233: open pay_process_events_ovn_cursor(l_person_id ,
4234: p_source_type ,
4235: p_effective_date );
4236: loop
4237: fetch pay_process_events_ovn_cursor into
4233: open pay_process_events_ovn_cursor(l_person_id ,
4234: p_source_type ,
4235: p_effective_date );
4236: loop
4237: fetch pay_process_events_ovn_cursor into
4238: l_process_event_id ,
4239: l_object_version_number ,
4240: l_assignment_id ,
4241: l_description ;
4239: l_object_version_number ,
4240: l_assignment_id ,
4241: l_description ;
4242: hr_utility.set_location(l_proc, 120);
4243: exit when pay_process_events_ovn_cursor%notfound;
4244:
4245: /* Update pay_process_events table with a status of 'D' */
4246:
4247: pqp_process_events_errorlog
4241: l_description ;
4242: hr_utility.set_location(l_proc, 120);
4243: exit when pay_process_events_ovn_cursor%notfound;
4244:
4245: /* Update pay_process_events table with a status of 'D' */
4246:
4247: pqp_process_events_errorlog
4248: (
4249: p_process_event_id1 => l_process_event_id ,
4253: );
4254: hr_utility.set_location(l_proc, 130);
4255:
4256: end loop;
4257: close pay_process_events_ovn_cursor;
4258: if (t_visa_tab.exists(j)) then
4259: t_visa_tab.delete(j) ;
4260: end if;
4261: hr_utility.set_location(l_proc, 140);
4293: end if;
4294:
4295: l_process_event_id := null;
4296:
4297: open pay_process_events_ovn_cursor(l_person_id ,
4298: p_source_type ,
4299: p_effective_date );
4300: loop
4301: fetch pay_process_events_ovn_cursor into
4297: open pay_process_events_ovn_cursor(l_person_id ,
4298: p_source_type ,
4299: p_effective_date );
4300: loop
4301: fetch pay_process_events_ovn_cursor into
4302: l_process_event_id ,
4303: l_object_version_number ,
4304: l_assignment_id ,
4305: l_description ;
4302: l_process_event_id ,
4303: l_object_version_number ,
4304: l_assignment_id ,
4305: l_description ;
4306: exit when pay_process_events_ovn_cursor%notfound;
4307: hr_utility.set_location(l_proc, 280);
4308:
4309: /* Update pay_process_events table with a status of 'D' */
4310:
4305: l_description ;
4306: exit when pay_process_events_ovn_cursor%notfound;
4307: hr_utility.set_location(l_proc, 280);
4308:
4309: /* Update pay_process_events table with a status of 'D' */
4310:
4311: pqp_process_events_errorlog
4312: (p_process_event_id1 =>l_process_event_id ,
4313: p_object_version_number1=>l_object_version_number,
4316: );
4317:
4318: end loop;
4319:
4320: close pay_process_events_ovn_cursor;
4321:
4322: /* Added by tmehra for wf notification consolidation */
4323: if l_process_event_id is not null then
4324: l_err_count := l_err_count+1;
4347: l_visa_err_mesg;
4348:
4349: l_process_event_id := null;
4350:
4351: open pay_process_events_ovn_cursor(l_person_id ,
4352: p_source_type ,
4353: p_effective_date );
4354: loop
4355: fetch pay_process_events_ovn_cursor into
4351: open pay_process_events_ovn_cursor(l_person_id ,
4352: p_source_type ,
4353: p_effective_date );
4354: loop
4355: fetch pay_process_events_ovn_cursor into
4356: l_process_event_id ,
4357: l_object_version_number ,
4358: l_assignment_id ,
4359: l_description ;
4356: l_process_event_id ,
4357: l_object_version_number ,
4358: l_assignment_id ,
4359: l_description ;
4360: exit when pay_process_events_ovn_cursor%notfound;
4361: hr_utility.set_location(l_proc, 280);
4362:
4363: /* Update pay_process_events table with a status of 'D' */
4364:
4359: l_description ;
4360: exit when pay_process_events_ovn_cursor%notfound;
4361: hr_utility.set_location(l_proc, 280);
4362:
4363: /* Update pay_process_events table with a status of 'D' */
4364:
4365: pqp_process_events_errorlog
4366: (p_process_event_id1 =>l_process_event_id ,
4367: p_object_version_number1=>l_object_version_number,
4370: );
4371:
4372: end loop;
4373:
4374: close pay_process_events_ovn_cursor;
4375:
4376: /* Added by tmehra for wf notification consolidation */
4377: if l_process_event_id is not null then
4378: l_err_count := l_err_count+1;
4421: wrapper script. This procedure returns 3 tables.
4422: Arguments :
4423: in
4424: p_selection_criterion : if the user wants to select all records,
4425: or the records in the PAY_PROCESS_EVENTS table,
4426: or a specifice national_identifier.
4427: p_effective_date : Effective date.
4428: out
4429: p_batch_size : out NOCOPY number gives the batch size
4569: p_batch_size := null;
4570: end;
4571: end pqp_read_public;
4572: /********************************************************************
4573: name : update_pay_process_events
4574: Purpose : the following function is called from any wrapper script.
4575: This updates pay_process_events and changes the status.
4576: Arguments :
4577: in
4571: end pqp_read_public;
4572: /********************************************************************
4573: name : update_pay_process_events
4574: Purpose : the following function is called from any wrapper script.
4575: This updates pay_process_events and changes the status.
4576: Arguments :
4577: in
4578: p_person_id : Person Id
4579: p_effective_date : Effective date.
4584: out NOCOPY : none
4585: Notes : public
4586: exception HANDLING???
4587: *************************************************************************/
4588: procedure update_pay_process_events
4589: (
4590: p_person_id in number ,
4591: p_effective_date in date ,
4592: p_source_type in varchar2 ,
4597: l_process_event_id number ;
4598: l_object_version_number number ;
4599: l_assignment_id number ;
4600: l_description varchar2(250);
4601: l_proc varchar2(72) := g_package||'update_pay_process_events' ;
4602:
4603: begin
4604: hr_utility.set_location('Entering:'||l_proc, 5);
4605: if (pay_process_events_ovn_cursor%isopen = true) then
4601: l_proc varchar2(72) := g_package||'update_pay_process_events' ;
4602:
4603: begin
4604: hr_utility.set_location('Entering:'||l_proc, 5);
4605: if (pay_process_events_ovn_cursor%isopen = true) then
4606: close pay_process_events_ovn_cursor;
4607: end if;
4608: hr_utility.set_location(l_proc, 10);
4609: for ppeoc1 in pay_process_events_ovn_cursor(p_person_id ,
4602:
4603: begin
4604: hr_utility.set_location('Entering:'||l_proc, 5);
4605: if (pay_process_events_ovn_cursor%isopen = true) then
4606: close pay_process_events_ovn_cursor;
4607: end if;
4608: hr_utility.set_location(l_proc, 10);
4609: for ppeoc1 in pay_process_events_ovn_cursor(p_person_id ,
4610: p_source_type ,
4605: if (pay_process_events_ovn_cursor%isopen = true) then
4606: close pay_process_events_ovn_cursor;
4607: end if;
4608: hr_utility.set_location(l_proc, 10);
4609: for ppeoc1 in pay_process_events_ovn_cursor(p_person_id ,
4610: p_source_type ,
4611: p_effective_date)
4612: loop
4613: hr_utility.set_location(l_proc, 20);
4615: l_object_version_number := ppeoc1.object_version_number;
4616: l_assignment_id := ppeoc1.assignment_id ;
4617: l_description := ppeoc1.description ;
4618:
4619: /* Update pay_process_events table */
4620: pay_ppe_api.update_process_event
4621: ( p_validate => false ,
4622: p_status => p_status ,
4623: p_description =>
4627: );
4628: hr_utility.set_location(l_proc, 30);
4629: end loop;
4630: hr_utility.set_location('Leaving:'||l_proc, 50);
4631: end update_pay_process_events;
4632: /****************************************************************************
4633: name : pqp_windstar_reconcile
4634: Purpose : This procedure reconciles data in pay_process_events table.
4635: Arguments : none
4630: hr_utility.set_location('Leaving:'||l_proc, 50);
4631: end update_pay_process_events;
4632: /****************************************************************************
4633: name : pqp_windstar_reconcile
4634: Purpose : This procedure reconciles data in pay_process_events table.
4635: Arguments : none
4636: Notes : public
4637: ****************************************************************************/
4638: procedure pqp_windstar_reconcile(p_assignment_id in number ,
4639: p_effective_date in date ,
4640: p_source_type in varchar ,
4641: p_process_event_id out NOCOPY number ,
4642: p_object_version_number out NOCOPY number ) is
4643: cursor c_pay_process_events(p_assignment_id in number ,
4644: p_effective_date in date ,
4645: p_source_type in varchar2 ) is
4646: select process_event_id,
4647: object_version_number
4644: p_effective_date in date ,
4645: p_source_type in varchar2 ) is
4646: select process_event_id,
4647: object_version_number
4648: from pay_process_events
4649: where assignment_id = p_assignment_id
4650: and change_type = p_source_type
4651: and status in ('R', 'C')
4652: order by status asc;
4660: some rows with status = 'R' as well as 'C', then rows with the status = 'R'
4661: will be fetched in the end. We can this way return the process event Id
4662: with status 'R'. Otherwise we will return the process event id of a row with
4663: a status of 'C'.This cursor is to make sure that the assignment exists in
4664: pay_process_events table.
4665:
4666: Status in 'C' was added on Oct 13, 2000 after discussion with Subbu.
4667: This will ensure that reconciliation occurs properly.
4668:
4678: l_proc varchar2(72) := g_package || 'pqp_windstar_reconcile';
4679: begin
4680: hr_utility.set_location('Entering ' || l_proc, 10);
4681: l_process_event_id := null;
4682: for cppe in c_pay_process_events(p_assignment_id ,
4683: p_effective_date ,
4684: p_source_type )
4685: loop
4686: hr_utility.set_location(l_proc, 20);
4842: ,p_source_type in varchar) is
4843: select distinct
4844: paf.assignment_id
4845: from per_assignments_f paf,
4846: pay_process_events ppe
4847: where paf.person_id = p_person_id
4848: and paf.effective_start_date <=
4849: to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4850: and paf.effective_end_date >=
4861: and paf.assignment_id = ppe.assignment_id
4862: and ppe.status in ( 'R','C')
4863: and ppe.change_type = p_source_type;
4864:
4865: -- In the above sql statement (cursor c_person_assgn) the pay_process_events
4866: -- table is used due to the following reasons.
4867: --
4868: -- 1. It will select only those assignments that have a status of read. So if a new
4869: -- assignment is created for a person, and that assignment is with a status
5077: where information_type = 'PER_US_ADDITIONAL_DETAILS'
5078: and person_id = p_person_id;
5079:
5080: --
5081: -- The following cursor fetches the current pay_process_events records
5082: -- With status of 'N' or 'D'
5083: --
5084: cursor c_pay_process_events(p_assignment_id in number
5085: ,p_source_type in varchar2) is
5080: --
5081: -- The following cursor fetches the current pay_process_events records
5082: -- With status of 'N' or 'D'
5083: --
5084: cursor c_pay_process_events(p_assignment_id in number
5085: ,p_source_type in varchar2) is
5086: select process_event_id
5087: ,object_version_number
5088: from pay_process_events
5084: cursor c_pay_process_events(p_assignment_id in number
5085: ,p_source_type in varchar2) is
5086: select process_event_id
5087: ,object_version_number
5088: from pay_process_events
5089: where assignment_id = p_assignment_id
5090: and change_type = p_source_type
5091: and status in ('N', 'D');
5092:
5439: --
5440: -- The following cursor gives the assignment_id's of a person. All
5441: -- the assignments that are active in the year of the effective
5442: -- date(Tax year) are reported. But that assignment should be present
5443: -- in pay_process_events table with a status of 'R'.
5444: --
5445: l_reco_flag := false;
5446:
5447: if (c_person_assgn%isopen = true) then
5734: -- Following code sets the residency status codein the person extra
5735: -- info as per the analysis. Made changes to the following logic
5736: -- So that no new pay_process_event is logged with the status 'N'.
5737: -- The code after changing Residency status would go and change the
5738: -- status from Not-Read to Read in the pay_process_events However if
5739: -- there is an existing record with a status of 'N' or 'D' the status
5740: -- would not be changed.
5741:
5742: l_ppe_status_n_recs := false;
5740: -- would not be changed.
5741:
5742: l_ppe_status_n_recs := false;
5743:
5744: for c_rec in c_pay_process_events (l_cpa_assignment_id,
5745: 'PQP_US_ALIEN_WINDSTAR')
5746: loop
5747: l_ppe_status_n_recs := true;
5748: end loop;
5757: end loop;
5758:
5759: if l_ppe_status_n_recs = false then
5760:
5761: update pay_process_events
5762: set status = 'R',
5763: description = substr('Record Read | '|| description, 1, 240),
5764: object_version_number = object_version_number + 1
5765: where assignment_id = l_cpa_assignment_id
5768:
5769: end if;
5770: --
5771: -- l_process_event_id is not null if an assignment id exists in the
5772: -- pay_process_events table with change_type PQP_US_WINSTAR,
5773: -- staus = read or complete in the given year
5774: --
5775: l_process_event_id := null;
5776: l_process_ovn := null;
5793: l_reco_flag = false) then
5794:
5795: hr_utility.set_location(l_proc, 250);
5796: -- l_process_event_id will be null if an assignment id does
5797: -- not exist in the pay_process_events table for PQP_US_WINSTAR
5798: -- or such an assignment exists in the pay_process_events
5799: -- table, but the status of such record is not read
5800: --
5801: pqp_alien_trans_data_api.update_alien_trans_data
5794:
5795: hr_utility.set_location(l_proc, 250);
5796: -- l_process_event_id will be null if an assignment id does
5797: -- not exist in the pay_process_events table for PQP_US_WINSTAR
5798: -- or such an assignment exists in the pay_process_events
5799: -- table, but the status of such record is not read
5800: --
5801: pqp_alien_trans_data_api.update_alien_trans_data
5802: (p_validate => false
5847: -- stauts of only one record to 'C'. Hence updating the
5848: -- remaining records to a status of 'C'.
5849: --
5850: begin
5851: update pay_process_events ppe
5852:
5853: set ppe.status = 'C'
5854: ,ppe.description = 'Assignment has been Reconciled'
5855: ,ppe.object_version_number =
5956:
5957: else
5958: hr_utility.set_location(l_proc, 300);
5959: -- Means no assignment with read/complete status was present in
5960: -- pay_process_events table
5961: pqp_alien_trans_data_api.update_alien_trans_data
5962: (p_validate => false
5963: ,p_alien_transaction_id => l_alien_transaction_id
5964: ,p_object_version_number => l_transaction_ovn
6098: end pqp_batch_size;
6099:
6100: -- =============================================================================
6101: -- Name : ResetForReadAPI
6102: -- Purpose : This resets the status in pay_process_events table back to 'N'.
6103: -- Arguments :
6104: -- IN : p_process_event_id
6105: -- Notes : public
6106: -- =============================================================================
6138: end ResetForReadAPI;
6139:
6140: -- =============================================================================
6141: -- Name : AbortReadAPI
6142: -- Purpose : This resets the status in pay_process_events table to 'C'.
6143: -- Arguments :
6144: -- IN : p_process_event_id
6145: -- Notes : public
6146: -- =============================================================================