[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ppe.process_event_id process_event_id
,ppe.object_version_number object_version_number
,paf.assignment_id assignment_id
,ppe.description description
from pay_process_events ppe
,per_people_f ppf
,per_assignments_f paf
where ppf.person_id = p_person_id1
and ppf.person_id = paf.person_id
and ppe.assignment_id = paf.assignment_id
and ppe.change_type = p_change_type1
and ppf.effective_start_date <= to_date(('12/31/' ||
to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
and ppf.effective_end_date >= to_date(('01/01/' ||
to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
and ppf.effective_start_date =
(select max(effective_start_date)
from per_people_f
where person_id = ppf.person_id
and effective_start_date <=
to_date(('12/31/' ||TO_CHAR(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
)
and paf.effective_start_date <=
to_date(('12/31/' ||TO_CHAR(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
and paf.effective_end_date >=
to_date(('01/01/' || to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
and paf.effective_start_date =
(select max(effective_start_date)
from per_assignments_f
where assignment_id = paf.assignment_id
and effective_start_date <=
to_date(('12/31/' ||to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
)
and ppe.status = 'N';
select count(*)
from hr_lookups hrl
where hrl.lookup_type = 'PER_US_COUNTRY_CODE'
and hrl.enabled_flag = 'Y'
and nvl(start_date_active, p_effective_date) <= to_date(('12/31/' ||
to_char(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and nvl(end_date_active, p_effective_date) >= to_date(('01/01/' ||
to_char(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and upper(hrl.lookup_code) = upper(p_country_code)
order by hrl.lookup_code;
select pei_information5 purpose
,pei_information7 start_date
,pei_information8 end_date
,pei_information9 spouse_accompanied
,pei_information10 child_accompanied
from (select *
from per_people_extra_info
where information_type = 'PER_US_VISIT_HISTORY'
) ppei
where ppei.person_id = p_person_id
and ppei.information_type = 'PER_US_VISIT_HISTORY'
and (to_char(fnd_date.canonical_to_date(ppei.pei_information7),'YYYY')=
to_char(p_effective_date, 'YYYY')
or
to_char(fnd_date.canonical_to_date(ppei.pei_information8),'YYYY')=
to_char(p_effective_date, 'YYYY')
or
p_effective_date
between fnd_date.canonical_to_date(ppei.pei_information7)
and nvl(fnd_date.canonical_to_date(ppei.pei_information8),
to_date('12/31/4712','MM/DD/YYYY')
)
)
order by 4 asc;
select parameter_value
from pay_action_parameters
where parameter_name = 'PQP_US_WINDSTAR_READ_BATCH';
select pei_information5 residency_status
,pei_information7 resident_status_date
,pei_information12 process_type
,pei_information8 first_entry_date
,nvl(pei_information10, 0) dep_children_total
,nvl(pei_information11, 0) dep_children_in_cntry
,pei_information9 tax_res_country_code
from (select *
from per_people_extra_info
where information_type = 'PER_US_ADDITIONAL_DETAILS'
and person_id = p_person_id );
select object_version_number
from pay_process_events
where process_event_id = p_process_event_id;
select 'Y' from
dual where exists
(select ppa.date_earned
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_element_types_f pet
,pay_element_classifications pec
,per_assignments_f paf
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and ppa.action_status = 'C'
and paa.action_status = 'C'
and ppa.action_type in ('R','Q','I','B','V')
and paf.person_id = p_person_id
and prr.assignment_action_id = paa.assignment_action_id
and pet.element_type_id = prr.element_type_id
and prr.status = 'P'
and pet.classification_id = pec.classification_id
and pec.classification_name = 'Alien/Expat Earnings'
and pet.element_information1 = p_income_code
and paf.effective_start_date <= p_effective_date
and ppa.effective_date <= p_effective_date);
select pei_information7 amount
from (select *
from per_people_extra_info
where person_id = p_person_id
and information_type = 'PER_US_INCOME_FORECAST'
)
where pei_information5 = p_income_code
and pei_information8 = to_char(p_effective_date, 'YYYY');
select count(*)
from fnd_territories_vl
where territory_code = upper(p_country_code);
pay_ppe_api.update_process_event
(p_validate => false
,p_status => p_status1
,p_description => substr(p_description1, 1, 240)
,p_process_event_id => p_process_event_id1
,p_object_version_number => l_object_version_number
);
|| 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(Update). Error '
|| 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
procedure Insert_Pay_Process_Events
(p_type in varchar2
,p_effective_date in date) is
--
-- The following cursor gets executed when the p_type is ALL. It selects
-- all assignments that are active in the calendar year of the effective date.
--
cursor all_people_f_cursor_n (c_start_date in date
,c_end_date in date
,c_national_indentifier in varchar2
,c_effective_date in date) is
select paf.assignment_id
,paf.effective_start_date
from per_people_f ppf
,per_person_types ppt
,per_people_extra_info pei
,per_all_assignments_f paf
where ppf.person_type_id = ppt.person_type_id
and ppf.business_group_id = ppt.business_group_id
and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
--
and pei.person_id = ppf.person_id
and pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and pei.pei_information12 = 'WINDSTAR'
and to_char(c_effective_date, 'YYYY') <=
to_char(nvl(fnd_date.canonical_to_date(pei.pei_information13)
,to_date('31/12/4712','DD/MM/YYYY')
),'YYYY'
)
--
and paf.person_id = ppf.person_id
and paf.business_group_id = ppf.business_group_id
and paf.effective_end_date between ppf.effective_start_date
and ppf.effective_end_date
and ((c_end_date between paf.effective_start_date
and paf.effective_end_date
)
or
(paf.effective_end_date =
(select max(asx.effective_end_date)
from per_all_assignments_f asx
where asx.assignment_id = paf.assignment_id
and asx.effective_end_date between c_start_date
and c_end_date)
)
)
and not exists (select 1
from pay_process_events
where assignment_id = paf.assignment_id
and change_type = 'PQP_US_ALIEN_WINDSTAR'
and status in ('N', 'D')
)
order by paf.assignment_id desc;
select paf.assignment_id ,
paf.effective_start_date
from per_people_f ppf ,
per_person_types ppt ,
per_people_extra_info ppei,
per_assignments_f paf
where ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP' , 'EX_EMP')
and ppf.effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_end_date >=
TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_start_date = (select MAX(effective_start_date)
from per_people_f
where person_id =
ppf.person_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and ppf.person_id = ppei.person_id
and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and ppei.pei_information12 = 'WINDSTAR'
and TO_CHAR(p_effective_date, 'YYYY') <=
TO_CHAR(NVL(fnd_date.canonical_to_date(ppei.pei_information13),
TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
and paf.person_id = ppf.person_id
and paf.effective_start_date <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_end_date >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_start_date = (select MAX(effective_start_date)
from per_assignments_f
where assignment_id =
paf.assignment_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and not exists (select 1
from pay_process_events
where assignment_id = paf.assignment_id
and status in ('N', 'D')
and change_type = 'PQP_US_ALIEN_WINDSTAR'
)
order by paf.assignment_id desc ;
select paf.assignment_id
,paf.effective_start_date
,ppe.process_event_id
,ppe.object_version_number
from per_people_f ppf
,per_person_types ppt
,per_people_extra_info pei
,pay_process_events ppe
,per_all_assignments_f paf
where ppt.person_type_id = ppf.person_type_id
and ppt.business_group_id = ppf.business_group_id
and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
--
and ppe.assignment_id = paf.assignment_id
and ppe.change_type = 'PQP_US_ALIEN_WINDSTAR'
and ppe.status in ('D')
-- only if person EIT exists
and pei.person_id = ppf.person_id
and pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and pei.pei_information12 = 'WINDSTAR'
and to_char(c_effective_date, 'YYYY') <=
to_char(nvl(fnd_date.canonical_to_date(pei_information13)
,to_date('31/12/4712','DD/MM/YYYY')
),'YYYY'
)
--
and paf.person_id = ppf.person_id
and paf.business_group_id = ppf.business_group_id
and paf.effective_end_date between ppf.effective_start_date
and ppf.effective_end_date
and ((c_end_date between paf.effective_start_date
and paf.effective_end_date
)
or
(paf.effective_end_date =
(select max(asx.effective_end_date)
from per_all_assignments_f asx
where asx.assignment_id = paf.assignment_id
and asx.effective_end_date between c_start_date
and c_end_date)
)
)
--
order by paf.assignment_id desc;
select paf.assignment_id ,
paf.effective_start_date ,
ppe.process_event_id ,
ppe.object_version_number
from per_people_f ppf ,
per_person_types ppt ,
per_people_extra_info ppei,
pay_process_events ppe ,
per_assignments_f paf
where ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP' , 'EX_EMP')
and ppf.effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_end_date >=
TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_start_date = (select MAX(effective_start_date)
from per_people_f
where person_id =
ppf.person_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and ppf.person_id = ppei.person_id
and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and ppei.pei_information12 = 'WINDSTAR'
and TO_CHAR(p_effective_date, 'YYYY') <=
TO_CHAR(NVL(fnd_date.canonical_to_date(ppei.pei_information13),
TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
and paf.effective_start_date <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_end_date >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_start_date = (select MAX(effective_start_date)
from per_assignments_f
where assignment_id =
paf.assignment_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and paf.person_id = ppf.person_id
and paf.assignment_id = ppe.assignment_id
and exists (select 1
from pay_process_events
where assignment_id = paf.assignment_id
and status in ('D')
and change_type = 'PQP_US_ALIEN_WINDSTAR'
)
order by paf.assignment_id desc; */
select paf.assignment_id
,paf.effective_start_date
from per_all_assignments_f paf
,per_people_f ppf
,per_person_types ppt
where ppf.person_id = paf.person_id
and ppf.person_type_id = ppt.person_type_id
and ppf.national_identifier = c_national_indentifier
and ppt.system_person_type in ('EMP', 'EX_EMP')
and ((c_end_date between paf.effective_start_date
and paf.effective_end_date
)
or
(paf.effective_end_date =
(select max(asx.effective_end_date)
from per_all_assignments_f asx
where asx.assignment_id = paf.assignment_id
and asx.effective_end_date between c_start_date
and c_end_date)
)
)
and paf.effective_end_date between ppf.effective_start_date
and ppf.effective_end_date
and not exists (select 1
from pay_process_events
where assignment_id = paf.assignment_id
and status in ('N', 'D')
and change_type = 'PQP_US_ALIEN_WINDSTAR'
)
and exists
(select 1
from per_people_extra_info pei
where pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and pei.pei_information12 = 'WINDSTAR'
and pei.person_id = ppf.person_id
and to_char(c_effective_date, 'YYYY') <=
to_char(nvl(fnd_date.canonical_to_date(pei_information13)
,to_date('31/12/4712','DD/MM/YYYY')
),'YYYY'
)
)
order by paf.assignment_id;
select paf.assignment_id ,
paf.effective_start_date
from per_assignments_f paf ,
per_people_f ppf ,
per_person_types ppt ,
(select * from per_people_extra_info
where information_type = 'PER_US_ADDITIONAL_DETAILS'
and pei_information12 = 'WINDSTAR'
and TO_CHAR(p_effective_date, 'YYYY') <=
TO_CHAR(NVL(fnd_date.canonical_to_date(pei_information13),
TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
) ppei
where ppf.person_id = paf.person_id
and ppf.person_type_id = ppt.person_type_id
and ppf.national_identifier= p_type
and ppt.system_person_type in ('EMP' , 'EX_EMP')
and ppf.effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_end_date >=
TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_start_date = (select MAX(effective_start_date)
from per_people_f
where person_id =
ppf.person_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and paf.effective_start_date <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_end_date >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_start_date = (select MAX(effective_start_date)
from per_assignments_f
where assignment_id =
paf.assignment_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and ppf.person_type_id = ppt.person_type_id
and ppf.person_id = ppei.person_id
and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and ppei.pei_information12 = 'WINDSTAR'
and not exists (select 1
from pay_process_events
where assignment_id = paf.assignment_id
and status in ('N', 'D')
and change_type = 'PQP_US_ALIEN_WINDSTAR'
)
order by paf.assignment_id ;
select paf.assignment_id
,paf.effective_start_date
,ppe.process_event_id
,ppe.object_version_number
from per_all_assignments_f paf
,per_people_f ppf
,per_person_types ppt
,pay_process_events ppe
where ppf.person_id = paf.person_id
and ppf.person_type_id = ppt.person_type_id
and ppf.business_group_id = ppt.business_group_id
and ppf.national_identifier = c_national_indentifier
and ppt.system_person_type in ('EMP', 'EX_EMP')
and ((c_end_date between paf.effective_start_date
and paf.effective_end_date
)
or
(paf.effective_end_date =
(select max(asx.effective_end_date)
from per_all_assignments_f asx
where asx.assignment_id = paf.assignment_id
and asx.business_group_id = paf.business_group_id
and asx.person_id = paf.person_id
and asx.effective_end_date between c_start_date
and c_end_date)
)
)
and paf.effective_end_date between ppf.effective_start_date
and ppf.effective_end_date
and paf.business_group_id = ppf.business_group_id
and ppe.assignment_id = paf.assignment_id
and ppe.status in ('D')
and ppe.change_type = 'PQP_US_ALIEN_WINDSTAR'
and exists (select 1
from per_people_extra_info pei
where pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and pei.pei_information12 = 'WINDSTAR'
and pei.person_id = ppf.person_id
and to_char(c_effective_date, 'YYYY') <=
to_char(nvl(fnd_date.canonical_to_date(pei_information13)
,to_date('31/12/4712','DD/MM/YYYY')
),'YYYY'
)
)
order by paf.assignment_id;
select paf.assignment_id,
paf.effective_start_date,
ppe.process_event_id,
ppe.object_version_number
from per_assignments_f paf ,
per_people_f ppf ,
per_person_types ppt ,
(select * from per_people_extra_info
where information_type = 'PER_US_ADDITIONAL_DETAILS'
and pei_information12 = 'WINDSTAR'
and TO_CHAR(p_effective_date, 'YYYY') <=
TO_CHAR(NVL(fnd_date.canonical_to_date(pei_information13),
TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
) ppei ,
pay_process_events ppe
where ppf.person_id = paf.person_id
and ppf.person_type_id = ppt.person_type_id
and ppf.national_identifier= p_type
and ppt.system_person_type in ('EMP' , 'EX_EMP')
and ppf.effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_end_date >=
TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_start_date = (select MAX(effective_start_date)
from per_people_f
where person_id =
ppf.person_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and paf.effective_start_date <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_end_date >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_start_date = (select MAX(effective_start_date)
from per_assignments_f
where assignment_id =
paf.assignment_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and ppf.person_id = ppei.person_id
and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and ppei.pei_information12 = 'WINDSTAR'
and paf.assignment_id = ppe.assignment_id
and exists (select 1
from pay_process_events
where assignment_id = paf.assignment_id
and status in ('D')
and change_type = 'PQP_US_ALIEN_WINDSTAR'
)
order by paf.assignment_id ;
l_proc constant varchar2(150) := g_package||'Insert_Pay_Process_Events';
,p_description => '| Inserted thru PL/SQL Code |'
,p_process_event_id => l_process_event_id
,p_object_version_number => l_object_version_number
);
pay_ppe_api.update_process_event
(p_validate => false
,p_status => 'N'
,p_description => null
,p_process_event_id => apfc.process_event_id
,p_object_version_number => apfc.object_version_number
);
,p_description => '| Inserted thru PL/SQL Code |'
,p_process_event_id => l_process_event_id
,p_object_version_number => l_object_version_number
);
pay_ppe_api.update_process_event
(p_validate => false
,p_status => 'N'
,p_description => null
,p_process_event_id => c1.process_event_id
,p_object_version_number => c1.object_version_number
);
'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
end insert_pay_process_events;
name : address_select
Purpose : the following procedure is called from pqp_windstar_person_read.
This selects the address of an assignment.
Arguments :
in
p_per_assign_id : Person or Assignment Id.
Person Id if home address is
needed. Assignment Id if work
address is needed.
p_effective_date : Effective date
in/out
p_work_home : Flag to select Home or work
address. if it is HOME, then
home address is selected. if it
is work then work address is
selected.
out
p_county : County
p_state : State
p_city : City
p_address_line1 : Address Line 1
p_address_line2 : Address Line 2
p_address_line3 : Address Line 3
p_telephone_number_1: Tel Phone 1
p_telephone_number_2: Tel Phone 2
p_telephone_number_3: Tel Phone 3
p_postal_code : Postal Code
Notes : private
***************************************************************************/
procedure address_select(p_per_assign_id in number ,
p_effective_date in date ,
p_work_home in out NOCOPY varchar2 ,
p_county out NOCOPY varchar2 ,
p_state out NOCOPY varchar2 ,
p_city out NOCOPY varchar2 ,
p_address_line1 out NOCOPY varchar2 ,
p_address_line2 out NOCOPY varchar2 ,
p_address_line3 out NOCOPY varchar2 ,
p_telephone_number_1 out NOCOPY varchar2 ,
p_telephone_number_2 out NOCOPY varchar2 ,
p_telephone_number_3 out NOCOPY varchar2 ,
p_postal_code out NOCOPY varchar2 )
is --{
/*****
This procedure selects HOME/work the address of an assignment
*****/
/*****
the following cursor selects the details of the home address
08-JAN-04 Bug #3347853 Fix latest addrress is send now instead of
the address as of the interface date.
MAX(date_from) is now being equated instead of less then equal to.
*****/
cursor home_address_cur is
select NVL(addr.add_information19 , addr.region_1 ) county ,
NVL(addr.add_information17 , addr.region_2 ) state ,
NVL(addr.add_information18 , addr.town_or_city) city ,
NVL(addr.address_line1 , ' ' ) address_line1 ,
NVL(addr.address_line2 , ' ' ) address_line2 ,
NVL(addr.address_line3 , ' ' ) address_line3 ,
NVL(addr.telephone_number_1, ' ' ) telephone_number_1 ,
NVL(addr.telephone_number_2, ' ' ) telephone_number_2 ,
NVL(addr.telephone_number_3, ' ' ) telephone_number_3 ,
NVL(addr.postal_code , ' ' ) postal_code
from per_addresses addr
where addr.person_id = p_per_assign_id
and addr.primary_flag = 'Y'
and NVL(addr.address_type,' ') <> 'PHCA'
and addr.date_from = (select MAX(date_From)
from per_addresses
where person_id = p_per_assign_id
and primary_flag = 'Y'
and NVL(address_type,' ') <> 'PHCA');
the following cursor selects the details of the work address
*****/
cursor work_address_cur is
select NVL(hrlock.loc_information19 , hrlock.region_1) county ,
NVL(hrlock.loc_information17 , hrlock.region_2) state ,
NVL(hrlock.loc_information18 , hrlock.town_or_city) city ,
NVL(hrlock.address_line_1 , ' ' ) address_line_1 ,
NVL(hrlock.address_line_2 , ' ' ) address_line_2 ,
NVL(hrlock.address_line_3 , ' ' ) address_line_3 ,
NVL(hrlock.telephone_number_1 , ' ' ) telephone_number_1,
NVL(hrlock.telephone_number_2 , ' ' ) telephone_number_2,
NVL(hrlock.telephone_number_3 , ' ' ) telephone_number_3,
NVL(hrlock.postal_code , ' ' ) postal_code
from hr_locations hrlock,
hr_soft_coding_keyflex hrsckf,
per_all_assignments_f assign
where p_effective_date between assign.effective_start_date
and assign.effective_end_date
and assign.assignment_id = p_per_assign_id
and assign.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
and NVL(hrsckf.segment18,assign.location_id) = hrlock.location_id;
l_proc varchar2(72) := g_package||'address_select' ;
end address_select;
(p_selection_criterion in varchar2
,p_source_type in varchar2
,p_effective_date in date
,t_people_tab out nocopy t_people_tab_type
,t_error_tab in out nocopy t_error_tab_type
,p_person_read_count out nocopy number
,p_person_err_count out nocopy number
) is
--
-- The cursor selects all the assignment_id's from pay_process_events table
-- that have a status of NOT_READ and then joins it with the per_people_f,
-- and per_assignments_f table. This cursor can be coded without the parameter
-- p_source_type, since the only user will be Windstar. But just to make the
-- program flexible, p_source_type is used.
-- 1. A status of 'N' means 'NOT_READ'
-- 2. pei_information12 is process_type. It means that the person is an alien
-- and has to be processed by WINDSTAR
--
cursor pay_process_events_cursor
(c_year_start_date in date
,c_year_end_date in date
,p_source_type in varchar2) is
select distinct
ppf.last_name
,ppf.first_name
,ppf.middle_names
,ppf.national_identifier
,ppf.employee_number
,ppf.date_of_birth
,ppf.title
,ppf.suffix
,upper(ppf.marital_status)
,ppf.person_id
from per_all_assignments_f paf
,per_people_f ppf
,pay_process_events ppe
,per_person_types ppt
,per_people_extra_info pei
where ppf.person_id = paf.person_id
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
--
and ppe.change_type = p_source_type
and ppe.assignment_id = paf.assignment_id
and ppe.status = 'N'
-- Person extra Info
and ppf.person_id = pei.person_id
and pei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and pei.pei_information12 = 'WINDSTAR'
and to_char(c_year_end_date, 'YYYY') <=
to_char(nvl(fnd_date.canonical_to_date(pei.pei_information13),
c_year_end_date),'YYYY')
and ((c_year_end_date between paf.effective_start_date
and paf.effective_end_date
)
or
(paf.effective_end_date =
(select max(asx.effective_end_date)
from per_all_assignments_f asx
where asx.assignment_id = paf.assignment_id
and asx.business_group_id = paf.business_group_id
and asx.person_id = paf.person_id
and asx.effective_end_date between c_year_start_date
and c_year_end_date)
)
)
and ((c_year_end_date between ppf.effective_start_date
and ppf.effective_end_date
)
or
(paf.effective_end_date between ppf.effective_start_date
and ppf.effective_end_date)
)
order by ppf.person_id;
select distinct
ppf.last_name ,
ppf.first_name ,
ppf.middle_names ,
ppf.national_identifier ,
ppf.employee_number ,
ppf.date_of_birth ,
ppf.title ,
ppf.suffix ,
UPPER(ppf.marital_status),
ppf.person_id
from per_assignments_f paf ,
per_people_f ppf ,
pay_process_events ppe ,
per_person_types ppt ,
per_people_extra_info ppei
where ppf.person_id = paf.person_id
and ppf.effective_start_date <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_end_date >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_start_date = (select MAX(effective_start_date)
from per_people_f
where person_id = ppf.person_id
and effective_start_date <=
TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and ppei.pei_information12 = 'WINDSTAR'
and TO_CHAR(p_effective_date, 'YYYY') <=
TO_CHAR(NVL(fnd_date.canonical_to_date(ppei.pei_information13),
TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
and paf.effective_start_date <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_end_date >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_start_date = (select MAX(effective_start_date)
from per_assignments_f
where assignment_id =
paf.assignment_id
and effective_start_date <=
TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP' , 'EX_EMP')
and ppe.change_type = p_source_type
and ppe.assignment_id = paf.assignment_id
and ppe.status = 'N'
and ppf.person_id = ppei.person_id
order by ppf.person_id;
select ppf.last_name ,
ppf.first_name ,
ppf.middle_names ,
ppf.national_identifier ,
ppf.employee_number ,
ppf.date_of_birth ,
ppf.title ,
ppf.suffix ,
UPPER(ppf.marital_status) ,
ppf.person_id
from per_people_f ppf ,
per_person_types ppt ,
per_people_extra_info ppei
where ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
and ppf.effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_end_date >=
TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_start_date = (select MAX(effective_start_date)
from per_people_f
where person_id =
ppf.person_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and ppf.person_type_id = ppt.person_type_id
and ppf.person_id = ppei.person_id
and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and ppei.pei_information12 = 'WINDSTAR'
and TO_CHAR(p_effective_date, 'YYYY') <=
TO_CHAR(NVL(fnd_date.canonical_to_date(ppei.pei_information13),
TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
order by ppf.person_id ;
the cursor(written below) national_identifier_cursor selects the details of
a person with the passed national Identifier
*****/
cursor national_identifier_cursor(p_effective_date in date ,
p_national_identifier in varchar2) is
select ppf.last_name ,
ppf.first_name ,
ppf.middle_names ,
ppf.national_identifier ,
ppf.employee_number ,
ppf.date_of_birth ,
ppf.title ,
ppf.suffix ,
UPPER(ppf.marital_status) ,
ppf.person_id
from per_people_f ppf ,
per_person_types ppt ,
(select * from per_people_extra_info
where information_type = 'PER_US_ADDITIONAL_DETAILS'
and pei_information12 = 'WINDSTAR'
and TO_CHAR(p_effective_date, 'YYYY') <=
TO_CHAR(NVL(fnd_date.canonical_to_date(pei_information13),
TO_DATE('31/12/4712','DD/MM/YYYY')),'YYYY')
) ppei
where ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL')
and ppf.effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_end_date >=
TO_DATE(('01/01/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_start_date = (select MAX(effective_start_date)
from per_people_f
where person_id =
ppf.person_id
and effective_start_date <=
TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and ppf.national_identifier = p_national_identifier
and ppf.person_id = ppei.person_id
and ppei.information_type = 'PER_US_ADDITIONAL_DETAILS'
and ppei.pei_information12 = 'WINDSTAR'
order by ppf.person_id ;
person id . rownum is used as we are interested in selecting just a single
row.
****/
cursor c_person_passport_info(p_person_id in number ) is
select ppei.pei_information5 country ,
ppei.pei_information6 passport_number ,
ppei.pei_information7 issue_date ,
ppei.pei_information8 expiry_date
from (select *
from per_people_extra_info
where information_type = 'PER_US_PASSPORT_DETAILS'
and person_id = p_person_id) ppei
where rownum < 2;
select COUNT(*) count
from hr_lookups
where lookup_type = p_lookup_type
and enabled_flag = 'Y'
and NVL(end_date_active, p_effective_date) >= p_effective_date
and lookup_code = p_country_code;
the following cursor c_non_us_address_cur selects the Non US address for a
person_id
Added the code to fetch the complete non us address - tmehra 15-OCT-2001
Added region_2 --> non_us_region_postal_cd - 05-APR-2002
08-JAN-04 Bug #3347853 Fix - foreign Address was not being passed if the primary address
was updated and the update date was in the new year. A new clause to check for 'PHCA'
has been added to the subquery.
*****/
cursor c_non_us_address_cur(p_person_id in number ,
p_effective_date in date ) is
select NVL(addr.address_line1,' ') non_us_addr1,
NVL(addr.address_line2,' ') non_us_addr2,
NVL(addr.address_line3,' ') non_us_addr3,
NVL(addr.postal_code,' ' ) non_us_city_postal_cd,
NVL(addr.town_or_city,' ' ) non_us_city,
NVL(addr.region_1,' ' ) non_us_region,
NVL(addr.region_2,' ' ) non_us_region_postal_cd,
NVL(addr.country, ' ' ) non_us_cc
from per_addresses addr
where addr.person_id = p_person_id
and addr.address_type = 'PHCA'
and addr.date_from = (select MAX(date_from)
from per_addresses
where person_id = p_person_id
and address_type = 'PHCA'
)
and rownum < 2;
select NVL(addr.country, ' ') non_us_cc
from per_addresses addr
where addr.person_id = p_person_id
and addr.address_type = 'PHCA'
and NVL(addr.date_from, p_effective_date) <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and NVL(addr.date_to, p_effective_date) >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and NVL(addr.date_from, p_effective_date) = (select MAX(date_from)
from per_addresses
where person_id = p_person_id
and NVL(date_from, p_effective_date) <=
TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and rownum < 2;
select hrl.meaning
from hr_lookups hrl
where hrl.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
and hrl.enabled_flag = 'Y'
and NVL(start_date_active, p_effective_date) <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and NVL(end_date_active, p_effective_date) >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and UPPER(hrl.lookup_code) = UPPER(p_country_code)
and rownum < 2;
if (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
hr_utility.set_location(l_proc, 20);
elsif (p_selection_criterion = 'ALL' ) then
hr_utility.set_location(l_proc, 25);
p_selection_criterion );
if (p_selection_criterion = 'ALL') then
hr_utility.set_location(l_proc, 40);
Insert_Pay_Process_Events
(p_type => 'ALL'
,p_effective_date => p_effective_date);
Address_Select(l_person_id ,
p_effective_date ,
l_work_home ,
l_county ,
l_state ,
l_city ,
l_address_line1 ,
l_address_line2 ,
l_address_line3 ,
l_telephone_number_1 ,
l_telephone_number_2 ,
l_telephone_number_3 ,
l_postal_code);
t_people_tab.delete(i) ;
t_people_tab.delete(i) ;
elsif (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
loop
begin
l_last_name := null;
Address_Select(l_person_id ,
p_effective_date ,
l_work_home ,
l_county ,
l_state ,
l_city ,
l_address_line1 ,
l_address_line2 ,
l_address_line3 ,
l_telephone_number_1 ,
l_telephone_number_2 ,
l_telephone_number_3 ,
l_postal_code);
t_people_tab.delete(i) ;
t_people_tab.delete(i) ;
insert_pay_process_events procedure inserts into pay_process_events
table. the records are inserted in this table for the reconciliation purpose.
*****/
insert_pay_process_events(p_selection_criterion ,
p_effective_date );
address_select(l_person_id ,
p_effective_date ,
l_work_home ,
l_county ,
l_state ,
l_city ,
l_address_line1 ,
l_address_line2 ,
l_address_line3 ,
l_telephone_number_1 ,
l_telephone_number_2 ,
l_telephone_number_3 ,
l_postal_code );
delete the current row in the PL/sql table. update the status in the
pay_process_events table to reflect the status as DATA_VALIDATION_FAILED.
the row is deleted as we do not want to insert the row containing an
error/validation failure in indv_rev1_temp table.
*****/
l_process_event_id := null;
after a row in inserted in ten42s_state_temp table
*****/
if (l_warn_mesg is not null) then
hr_utility.set_location(l_proc, 470);
t_people_tab.delete(i) ;
t_people_tab.delete(i) ;
select income_code ,
exemption_code ,
withholding_rate ,
income_code_sub_type ,
constant_addl_tax
from pqp_analyzed_alien_data paadat ,
pqp_analyzed_alien_details paadet ,
per_people_f ppf ,
per_assignments_f paf
where paadat.analyzed_data_id = paadet.analyzed_data_id
and paadet.income_code = p_income_code
and ppf.person_id = paf.person_id
and ppf.person_id = p_person_id
and paadat.tax_year = p_tax_year
and paf.assignment_id = paadat.assignment_id
and rownum < 2;
select distinct
nvl(pet.element_information1, ' ') income_code
from per_all_assignments_f paf
,per_all_people_f ppf
,pay_element_entries_f pee
,pay_element_links_f pel
,pay_element_types_f pet
,pay_element_classifications pec
where paf.person_id = ppf.person_id
and ppf.person_id = p_person_id
and ((c_year_end between paf.effective_start_date
and paf.effective_end_date
)
or
(paf.effective_end_date =
(select max(asx.effective_end_date)
from per_all_assignments_f asx
where asx.assignment_id = paf.assignment_id
and asx.business_group_id = paf.business_group_id
and asx.person_id = paf.person_id
and asx.effective_end_date between c_year_start
and c_year_end)
)
)
and paf.effective_end_date between ppf.effective_start_date
and ppf.effective_end_date
and paf.assignment_id = pee.assignment_id
and pee.element_link_id = pel.element_link_id
and pel.element_type_id = pet.element_type_id
and pet.classification_id = pec.classification_id
and pec.classification_name = 'Alien/Expat Earnings'
and ((c_year_end between pee.effective_start_date
and pee.effective_end_date
)
or
(pee.effective_end_date =
(select max(pex.effective_end_date)
from pay_element_entries_f pex
where pex.assignment_id = paf.assignment_id
and pex.effective_end_date between c_year_start
and c_year_end)
)
)
and pee.effective_end_date between pel.effective_start_date
and pel.effective_end_date;
the following cursor selects all the active assignments for the person
in the calender year of the effective date
****/
cursor c_assignment_id(p_person_id in number ,
p_effective_date in date ) is
select person_id ,
assignment_id
from per_assignments_f paf
where paf.person_id = p_person_id
and paf.effective_start_date <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_end_date >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_start_date = (select MAX(effective_start_date)
from per_assignments_f
where assignment_id =
paf.assignment_id
and effective_start_date <=
TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
order by person_id ,
assignment_id;
the following cursor selects all the details about the payroll information
about the person.
*****/
cursor c_person_payroll_info(p_person_id in number,
p_income_code in varchar2,
p_year in varchar2) is
select pei_information5 income_code ,
pei_information6 prev_er_treaty_ben_amt ,
pei_information7 prev_er_treaty_ben_year
from (select *
from per_people_extra_info
where information_type = 'PER_US_PAYROLL_DETAILS'
and person_id = p_person_id )
where pei_information7 = p_year
and pei_information5 = p_income_code;
the following cursor selects the primary assignment Id for the person
in the calender year of the effective date. This cursor should always
return 0 or 1 row as rownum < 2 has been yse
*****/
cursor c_person_assignment(p_person_id in number) is
select distinct assignment_id
from per_assignments_f paf,
per_people_f ppf
where ppf.person_id = paf.person_id
and ppf.person_id = p_person_id
and ppf.effective_start_date <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_end_date >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and ppf.effective_start_date = (select MAX(effective_start_date)
from per_people_f
where person_id = ppf.person_id
and effective_start_date <=
TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and paf.effective_start_date <= TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_end_date >= TO_DATE(('01/01/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
and paf.effective_start_date = (select MAX(effective_start_date)
from per_assignments_f
where assignment_id =
paf.assignment_id
and effective_start_date <=
TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
and paf.primary_flag = 'Y'
and rownum < 2;
select nvl(hrlock.loc_information17
,hrlock.region_2) state
from hr_locations hrlock
,hr_soft_coding_keyflex hrsckf
,per_all_assignments_f paf
where paf.effective_start_date <=
to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
and paf.effective_end_date >=
to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
and paf.effective_start_date =
(select max(effective_start_date)
from per_assignments_f
where assignment_id = paf.assignment_id
and effective_start_date <=
to_date(('12/31/'||to_char(p_effective_date,'YYYY'))
,'MM/DD/YYYY')
)
and paf.assignment_id = p_assign_id
and paf.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
and nvl(hrsckf.segment18,paf.location_id) = hrlock.location_id
and rownum < 2;
select max(ppa.effective_date) date_paid ,
max(ppa.date_earned) date_earned
from pay_payroll_actions ppa
,pay_assignment_actions paa
,per_assignments_f paf
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and ppa.action_status = 'C'
and paa.action_status = 'C'
and ppa.action_type in ('R','Q','I','B','V')
and paf.person_id = p_person_id
and paf.effective_start_date <= p_effective_date
and ppa.effective_date <= p_effective_date;
select min(trunc((52/ number_per_fiscal_year) * 7)) days_in_cycle
from per_time_periods ptp
,per_assignments_f paf
,per_time_period_types ptt
where ptp.payroll_id = paf.payroll_id
and ptp.period_type = ptt.period_type
and paf.person_id = p_person_id
and paf.effective_start_date <= p_effective_date;
select pei_information5 income_code
from per_people_extra_info
where person_id = p_person_id
and information_type = 'PER_US_INCOME_FORECAST'
and pei_information8 = to_char(p_effective_date, 'YYYY');
select paf.effective_end_date
from per_people_f ppf
,per_person_types ppt
,per_assignments_f paf
where ppf.person_id = p_person_id
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type ='EX_EMP'
and paf.person_id = ppf.person_id ;
t_balance_tab.delete(j) ;
/* Update pay_process_events table with a status of 'D' */
pqp_process_events_errorlog
(
p_process_event_id1 =>l_process_event_id ,
p_object_version_number1=>l_object_version_number,
p_status1 => 'D' ,
p_description1 => SUBSTR(l_out_mesg, 1, 240)
);
t_balance_tab.delete(j) ;
/* Update pay_process_events table with a status of 'D' */
pqp_process_events_errorlog
(p_process_event_id1 =>l_process_event_id ,
p_object_version_number1=>l_object_version_number,
p_status1 => 'D' ,
p_description1 => 'No Alien Income or Forecast found'
);
the following cursor selects all the visa details of a person. We are sending
the status of the current visa record only to Windstar.
*****/
cursor c_person_visa_info(p_person_id in number,
p_visa_no in varchar2) is
select pei_information5 visa_type ,
SUBSTR(pei_information6, 1, 20) visa_number ,
fnd_date.canonical_to_date(pei_information7) visa_issue_date ,
fnd_date.canonical_to_date(pei_information8) visa_expiry_date ,
pei_information9 visa_category ,
pei_information10 current_status
from (select * from per_people_extra_info
where information_type = 'PER_US_VISA_DETAILS' )
where person_id = p_person_id
and information_type = 'PER_US_VISA_DETAILS'
and pei_information6 = NVL(p_visa_no, pei_information6)
order by 6 desc, -- So that Y comes first
3 asc,
4 asc;
select pei_information5 purpose ,
fnd_date.canonical_to_date(pei_information7) start_date ,
fnd_date.canonical_to_date(pei_information8) end_date ,
pei_information11 visa_number
from (select * from per_people_extra_info
where information_type = 'PER_US_VISIT_HISTORY'
and person_id = p_person_id )
order by 2 asc,
3 asc;
select count(*) ct
from
(select *
from per_people_extra_info
where information_type = 'PER_US_VISA_DETAILS') visa
where visa.person_id = p_person_id;
select visa.visa_number
from
(select *
from per_people_extra_info
where information_type = 'PER_US_ADDITIONAL_DETAILS'
and pei_information12 = 'WINDSTAR') pei,
(select person_id,
SUBSTR(pei_information6, 1, 20) visa_number
from per_people_extra_info
where information_type = 'PER_US_VISA_DETAILS') visa
where visa.person_id = pei.person_id
and pei.person_id = p_person_id
and not exists
(select 'X'
from per_people_extra_info
where information_type = 'PER_US_VISIT_HISTORY'
and person_id = visa.person_id
and SUBSTR(pei_information11, 1, 20) = visa.visa_number
);
/* Delete the current row in the PL/SQL table. Update the status in the
pay_process_events table to reflect the status as DATA_VALIDATION_FAILED
*/
hr_utility.set_location(l_proc, 70);
/* Update pay_process_events table */
pqp_process_events_errorlog
(
p_process_event_id1 => l_process_event_id ,
p_object_version_number1 => l_object_version_number ,
p_status1 => 'D' ,
p_description1 => SUBSTR(l_out_mesg, 1, 240)
);
t_visa_tab.delete(j) ;
/* Update pay_process_events table with a status of 'D' */
pqp_process_events_errorlog
(
p_process_event_id1 => l_process_event_id ,
p_object_version_number1 => l_object_version_number ,
p_status1 => 'D' ,
p_description1 => SUBSTR(l_out_mesg, 1, 240)
);
t_visa_tab.delete(j) ;
/* Update pay_process_events table with a status of 'D' */
pqp_process_events_errorlog
(p_process_event_id1 =>l_process_event_id ,
p_object_version_number1=>l_object_version_number,
p_status1 => 'D' ,
p_description1 => l_visa_err_mesg
);
/* Update pay_process_events table with a status of 'D' */
pqp_process_events_errorlog
(p_process_event_id1 =>l_process_event_id ,
p_object_version_number1=>l_object_version_number,
p_status1 => 'D' ,
p_description1 => SUBSTR(l_visa_err_mesg,1,240)
);
p_selection_criterion : if the user wants to select all records,
or the records in the PAY_PROCESS_EVENTS table,
or a specifice national_identifier.
p_effective_date : Effective date.
out
p_batch_size : out NOCOPY number gives the batch size
t_people_tab : PL/sql table contains personal_details
t_balance_tab : PL/sql table contains the balance details
p_visa_tab : PL/sql table contains the visa details
Notes : public
****************************************************************************/
procedure pqp_read_public
(
p_selection_criterion in varchar2 ,
p_effective_date in date ,
p_batch_size out NOCOPY number ,
t_people_tab out NOCOPY t_people_tab_type ,
t_balance_tab out NOCOPY t_balance_tab_type ,
t_visa_tab out NOCOPY t_visa_tab_type ,
p_person_read_count out NOCOPY number ,
p_person_err_count out NOCOPY number
)
is
/*****
This is the definition of the table of the t_error_rec_type record type
the record and the table definition is being added to consolidate
the wf (workflow) notification logic at one place.
Added by tmehra 20-Oct-2003.
*****/
l_count number := 0 ;
raise error message as Selection Criterion cannot be null
******/
if (p_selection_criterion is null) then
hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
pqp_windstar_person_read(p_selection_criterion=> p_selection_criterion ,
p_source_type =>'PQP_US_ALIEN_WINDSTAR',
p_effective_date => p_effective_date ,
t_people_tab => t_people_tab ,
t_error_tab => t_error_tab ,
p_person_read_count => l_person_read_count ,
p_person_err_count => l_person_err_count );
assignments selected in the first procedure*/
/*****
call pqp_windstar_visa_read procedure to read all the information about
the visa into PL/sql t_visa_tab table.
******/
pqp_windstar_visa_read(t_people_tab ,
t_error_tab ,
'PQP_US_ALIEN_WINDSTAR' ,
p_effective_date ,
t_visa_tab );
name : update_pay_process_events
Purpose : the following function is called from any wrapper script.
This updates pay_process_events and changes the status.
Arguments :
in
p_person_id : Person Id
p_effective_date : Effective date.
p_source_type : source of Request. Normally Windstar
p_status : the final status of record being updated. read,
DATE_VALIDATION_FAILED etc.
p_desc : Description to be appended
out NOCOPY : none
Notes : public
exception HANDLING???
*************************************************************************/
procedure update_pay_process_events
(
p_person_id in number ,
p_effective_date in date ,
p_source_type in varchar2 ,
p_status in varchar2 ,
p_desc in varchar2
)
is
l_process_event_id number ;
l_proc varchar2(72) := g_package||'update_pay_process_events' ;
/* Update pay_process_events table */
pay_ppe_api.update_process_event
( p_validate => false ,
p_status => p_status ,
p_description =>
SUBSTR('Record Read | '|| p_desc || l_description, 1, 240),
p_process_event_id => l_process_event_id ,
p_object_version_number => l_object_version_number
);
end update_pay_process_events;
select process_event_id,
object_version_number
from pay_process_events
where assignment_id = p_assignment_id
and change_type = p_source_type
and status in ('R', 'C')
order by status asc;
This cursor will select all the rows for an assignment with a status of read
or complete. order by asc has been used so that cursor selects all the rows
with status 'C' first, and then selects all the rows with status = 'R'. in
reconciliation, we will try to reconcile records with status with 'R' first,
and then records with status = 'C'. Therefore if pa_process_events table has
some rows with status = 'R' as well as 'C', then rows with the status = 'R'
will be fetched in the end. We can this way return the process event Id
with status 'R'. Otherwise we will return the process event id of a row with
a status of 'C'.This cursor is to make sure that the assignment exists in
pay_process_events table.
Status in 'C' was added on Oct 13, 2000 after discussion with Subbu.
This will ensure that reconciliation occurs properly.
--- Nocopy changes. Added the exception block and Nullified the
the process_event_id. Did not raise the exception since the
the null process_event_id is being handled in the calling
procedure and a proper notification is raised indicating
that the Assignment is not reconciled.
****/
l_process_event_id number;
,p_date_treaty_updated in date
,p_exempt_fica in number
,p_exempt_student_fica in number
,p_add_wh_for_nra_whennotreaty in number
,p_amount_of_addl_withholding in number
,p_personal_exemption in varchar2
,p_add_exemptions_allowed in number
,p_days_in_usa in number
,p_eligible_for_whallowance in number
,p_treatybenefits_allowed in number
,p_treatybenefit_startdate in date
,p_ra_effective_date in date
,p_state_code in varchar2
,p_state_honours_treaty in number
,p_ytd_payments in number
,p_ytd_w2payments in number
,p_ytd_withholding in number
,p_ytd_whallowance in number
,p_ytd_treaty_payments in number
,p_ytd_treaty_withheld_amts in number
,p_record_source in varchar2
,p_visa_type in varchar2
,p_jsub_type in varchar2
,p_primary_activity in varchar2
,p_nus_countrycode in varchar2
,p_citizenship in varchar2
,p_constant_additional_tax in number
,p_out_of_system_treaty in number
,p_amount_of_addl_wh_type in varchar2
,p_error_indicator in varchar2
,p_error_text in varchar2
,p_date_w4_signed in date
,p_date_8233_signed in date
,p_reconcile in boolean
,p_effective_date in date
,p_current_analysis in number
,p_forecast_income_code in varchar2
,p_error_message out nocopy varchar2
) is
t_balance_tab pqp_alien_expat_taxation_pkg.t_balance_tab_type;
select distinct
paf.assignment_id
from per_assignments_f paf,
pay_process_events ppe
where paf.person_id = p_person_id
and paf.effective_start_date <=
to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
and paf.effective_end_date >=
to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
and paf.effective_start_date =
(select max(effective_start_date)
from per_assignments_f
where assignment_id = paf.assignment_id
and effective_start_date <=
to_date(('12/31/'||to_char(p_effective_date, 'YYYY'))
,'MM/DD/YYYY'
)
)
and paf.assignment_id = ppe.assignment_id
and ppe.status in ( 'R','C')
and ppe.change_type = p_source_type;
select person_id
from per_all_people_f
where national_identifier = p_social_security_number
and rownum =1;
select analyzed_data_id
,object_version_number
from pqp_analyzed_alien_data
where assignment_id = p_assignment_id
and tax_year = p_tax_year;
select analyzed_data_details_id
,object_version_number
,retro_lose_ben_amt_flag
,retro_lose_ben_date_flag
from pqp_analyzed_alien_details
where analyzed_data_id = p_analyzed_data_id
and income_code = p_income_code;
select lookup_code,
meaning
from hr_lookups
where lookup_type = 'PQP_US_WIND_ORA_PERIODS'
and lookup_code = p_lookup_code ;
select object_version_number
from pqp_alien_transaction_data
where alien_transaction_id = p_alien_transaction_id;
select MAX(effective_end_date) effective_end_date
from per_people_f ppf
,per_person_types ppt
where ppf.person_id = p_person_id
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP', 'EX_EMP') -- RLN 7039307
and ppf.effective_start_date <=
to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
and ppf.effective_end_date >=
to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY');
select distinct
paf.assignment_id
from per_assignments_f paf
where paf.person_id = p_person_id
and paf.effective_start_date <=
to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
and paf.effective_end_date >=
to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
and paf.effective_start_date = (select max(effective_start_date)
from per_assignments_f
where assignment_id =
paf.assignment_id
and effective_start_date <=
TO_DATE(('12/31/' ||
TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'));
select patd.date_w4_signed
,patd.personal_exemption
,patd.addl_exemption_allowed
,patd.addl_withholding_amt
,patd.constant_addl_tax
,patd.current_residency_status
from pqp_alien_transaction_data patd
where person_id = c_person_id
and alien_transaction_id =
(select MAX(patd1.alien_transaction_id)
from PQP_ALIEN_TRANSACTION_DATA patd1
where patd.person_id=patd1.person_id
having tax_year =max(tax_year)
group by tax_year);
select pee.element_entry_id element_entry_id,
pet.element_name element_name,
pee.effective_start_date entry_start_date,
nvl(pet.element_information1, ' ') element_income_code
from per_all_assignments_f paf,
per_all_people_f ppf,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_element_classifications pec
where paf.person_id = ppf.person_id
and paf.business_group_id = ppf.business_group_id
and ppf.person_id = p_person_id
and pec.classification_name = 'Alien/Expat Earnings'
and pet.element_information1 = p_income_code
and paf.assignment_id = pee.assignment_id
and pee.element_link_id = pel.element_link_id
and pel.business_group_id = ppf.business_group_id
and pel.element_type_id = pet.element_type_id
and pet.classification_id = pec.classification_id
and p_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and p_effective_date between pee.effective_start_date
and pee.effective_end_date
and p_effective_date between pel.effective_start_date
and pel.effective_end_date
and p_effective_Date between pet.effective_start_date
and pet.effective_end_date;
select pee.element_entry_id element_entry_id,
pet.element_name element_name,
pee.effective_start_date entry_start_date,
NVL(pet.element_information1, ' ') element_income_code
from per_assignments_f paf,
per_people_f ppf,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_element_classifications pec
where paf.person_id = ppf.person_id
and ppf.person_id = p_person_id
and ppf.effective_start_date <= p_effective_date
and ppf.effective_end_date >= p_effective_date
and paf.effective_start_date <= p_effective_date
and paf.effective_end_date >= p_effective_date
and paf.assignment_id = pee.assignment_id
and pee.element_link_id = pel.element_link_id
and p_effective_date
between pee.effective_start_date
and pee.effective_end_date
and pel.element_type_id = pet.element_type_id
and p_effective_date
between pel.effective_start_date
and pel.effective_end_date
and pet.classification_id = pec.classification_id
and p_effective_Date
between pet.effective_start_date
and pet.effective_end_date
and pec.classification_name = 'Alien/Expat Earnings'
and pet.element_information1 = p_income_code;*/
select pei_information5 residency_status
,person_extra_info_id
from per_people_extra_info
where information_type = 'PER_US_ADDITIONAL_DETAILS'
and person_id = p_person_id;
select process_event_id
,object_version_number
from pay_process_events
where assignment_id = p_assignment_id
and change_type = p_source_type
and status in ('N', 'D');
l_logic_state := ' while validating data selected from payment_export: ';
l_logic_state := ' while inserting in PQP_ALIEN_TRANSACTION_DATA : ';
,p_treaty_info_update_date => p_date_treaty_updated
,p_nra_exempt_from_fica => l_nra_exempt_from_fica
,p_student_exempt_from_fica => l_student_exempt_from_fica
,p_addl_withholding_flag => l_addl_withholding_flag
,p_addl_withholding_amt => p_amount_of_addl_withholding
,p_addl_wthldng_amt_period_type => l_period_type
,p_personal_exemption => l_personal_exemption
,p_addl_exemption_allowed => p_add_exemptions_allowed
,p_number_of_days_in_usa => p_days_in_usa
,p_current_analysis => l_current_analysis
,p_wthldg_allow_eligible_flag => l_wthldg_allow_eligible_flag
,p_treaty_ben_allowed_flag => l_treaty_ben_allowed_flag
,p_treaty_benefits_start_date => p_treatybenefit_startdate
,p_ra_effective_date => p_ra_effective_date
,p_state_code => p_state_code
,p_state_honors_treaty_flag => l_state_honors_treaty_flag
,p_ytd_payments => p_ytd_payments
,p_ytd_w2_payments => p_ytd_w2payments
,p_ytd_w2_withholding => p_ytd_withholding
,p_ytd_withholding_allowance => p_ytd_whallowance
,p_ytd_treaty_payments => p_ytd_treaty_payments
,p_ytd_treaty_withheld_amt => p_ytd_treaty_withheld_amts
,p_record_source => p_record_source
,p_visa_type => p_visa_type
,p_j_sub_type => p_jsub_type
,p_primary_activity => p_primary_activity
,p_non_us_country_code => p_nus_countrycode
,p_citizenship_country_code => p_citizenship
,p_constant_addl_tax => p_constant_additional_tax
,p_date_8233_signed => l_date_8233_signed
,p_date_w4_signed => p_date_w4_signed
,p_error_indicator => null
,p_prev_er_treaty_benefit_amt => p_out_of_system_treaty
,p_error_text => l_error_message
,p_object_version_number => l_transaction_ovn
,p_person_id => l_person_id
,p_effective_date =>
TO_DATE('01/01' || TO_CHAR(p_taxyear), 'DD/MM/YYYY')
);
pay_us_web_w4.update_alien_tax_records
-- pay_us_otf_util_web.update_tax_records
(p_filing_status_code => '01'
,p_allowances => (nvl(p_add_exemptions_allowed, 0) +
nvl(p_personal_exemption,0))
,p_additional_amount => l_additional_amt
,p_exempt_status_code => 'N'
--,p_process => 'PAY_FED_W4_NOTIFICATION_PRC'
,p_process => 'PAY_OTF_NOTIFY_PRC'
,p_itemtype => 'HRSSA'
,p_person_id => l_person_id
,p_effective_date => p_date_w4_signed
,p_source_name => 'PQP_US_ALIEN_WINDSTAR'
);
l_logic_state := ' while inserting in PQP_ANALYZED_ALIEN_DATA:';
,p_treaty_info_update_date => p_date_treaty_updated
,p_number_of_days_in_usa => p_days_in_usa
,p_withldg_allow_eligible_flag => l_wthldg_allow_eligible_flag
,p_ra_effective_date => p_ra_effective_date
,p_record_source => p_record_source
,p_visa_type => p_visa_type
,p_j_sub_type => p_jsub_type
,p_primary_activity => p_primary_activity
,p_non_us_country_code => p_nus_countrycode
,p_citizenship_country_code => p_citizenship
,p_object_version_number => l_analyzed_data_ovn
,p_date_w4_signed => p_date_w4_signed
,p_date_8233_signed => l_date_8233_signed
,p_effective_date => to_date('01/01/' ||
p_taxyear, 'DD/MM/YYYY')
);
pqp_analyzed_alien_data_api.update_analyzed_alien_data
(p_validate => false
,p_analyzed_data_id => l_analyzed_data_id
,p_assignment_id => l_cpa_assignment_id
,p_data_source => 'PQP_US_ALIEN_WINDSTAR'
,p_tax_year => p_taxyear
,p_current_residency_status => p_residency_status
,p_nra_to_ra_date => p_date_becomes_ra
,p_target_departure_date => p_target_departure_date
,p_tax_residence_country_code => p_tax_residence_country_code
,p_treaty_info_update_date => p_date_treaty_updated
,p_number_of_days_in_usa => p_days_in_usa
,p_withldg_allow_eligible_flag => l_wthldg_allow_eligible_flag
,p_ra_effective_date => p_ra_effective_date
,p_record_source => p_record_source
,p_visa_type => p_visa_type
,p_j_sub_type => p_jsub_type
,p_primary_activity => p_primary_activity
,p_non_us_country_code => p_nus_countrycode
,p_citizenship_country_code => p_citizenship
,p_object_version_number => l_analyzed_data_ovn
,p_date_w4_signed => p_date_w4_signed
,p_date_8233_signed => l_date_8233_signed
,p_effective_date => to_date('01/01/' ||
p_taxyear, 'DD/MM/YYYY')
);
l_logic_state := ' while inserting in PQP_ANALYZED_ALIEN_DETAIL: ';
pqp_alien_trans_data_api.update_alien_trans_data
(p_validate => false
,p_alien_transaction_id => l_alien_transaction_id
,p_object_version_number => l_transaction_ovn
,p_error_indicator => 'WARNING : CHANGED INCOME CODE'
,p_error_text => 'Changed Income Code'
,p_effective_date => TO_DATE('01/01/' ||
p_taxyear, 'DD/MM/YYYY')
);
pqp_analyzed_alien_det_api.update_analyzed_alien_det
(p_validate => false ,
p_analyzed_data_details_id => l_analyzed_data_details_id ,
p_analyzed_data_id => l_analyzed_data_id ,
p_income_code => p_income_code|| p_scholarship_type ,
p_current_analysis => l_current_analysis , -- Oct02 changes
p_forecast_income_code => l_forecast_income_code ,
p_withholding_rate => l_withholding_rate ,
p_income_code_sub_type => p_scholarship_type ,
p_exemption_code => p_exemption_code ,
p_maximum_benefit_amount => l_maximum_benefit ,
p_retro_lose_ben_amt_flag => l_retro_lose_ben_amt_flag ,
p_date_benefit_ends => p_date_benefit_ends ,
p_retro_lose_ben_date_flag => l_retro_lose_ben_date_flag ,
p_nra_exempt_from_ss => l_nra_exempt_from_fica ,
p_nra_exempt_from_medicare => l_nra_exempt_from_fica ,
p_student_exempt_from_ss => l_student_exempt_from_fica ,
p_student_exempt_from_medi => l_student_exempt_from_fica ,
p_addl_withholding_flag => null ,
p_constant_addl_tax => p_constant_additional_tax ,
p_addl_withholding_amt => l_amount_of_addl_withholding ,
p_addl_wthldng_amt_period_type => null ,
p_personal_exemption => p_personal_exemption ,
p_addl_exemption_allowed => p_add_exemptions_allowed,
p_treaty_ben_allowed_flag => l_treaty_ben_allowed_flag ,
p_treaty_benefits_start_date => p_treatybenefit_startdate ,
p_object_version_number => l_analyzed_det_ovn ,
p_effective_date => TO_DATE('01/01/' || p_taxyear, 'DD/MM/YYYY')
);
pqp_alien_trans_data_api.update_alien_trans_data
(p_validate => false ,
p_alien_transaction_id => l_alien_transaction_id ,
p_object_version_number => l_transaction_ovn ,
p_error_indicator => 'WARNING : INVALID INCOME CODE',
p_error_text => 'Invalid Income Code',
p_effective_date => TO_DATE('01/01/' ||
p_taxyear, 'DD/MM/YYYY')
);
update per_people_extra_info
set pei_information5 = p_residency_status
where person_extra_info_id = c_rec.person_extra_info_id;
update pay_process_events
set status = 'R',
description = substr('Record Read | '|| description, 1, 240),
object_version_number = object_version_number + 1
where assignment_id = l_cpa_assignment_id
and status = 'N'
and change_type = 'PQP_US_ALIEN_WINDSTAR';
pqp_alien_trans_data_api.update_alien_trans_data
(p_validate => false
,p_alien_transaction_id => l_alien_transaction_id
,p_object_version_number => l_transaction_ovn
,p_error_indicator => 'ERROR : NOT_RECONCILED 1'
,p_error_text => l_error_message ||
'Assignment not Reconciled'
,p_effective_date => TO_DATE('01/01/' ||
p_taxyear, 'DD/MM/YYYY')
);
pay_ppe_api.update_process_event
(p_validate => false
,p_change_type => 'PQP_US_ALIEN_WINDSTAR'
,p_description => 'Assignment has been Reconciled'
,p_status => 'C'
,p_process_event_id => l_process_event_id
,p_object_version_number=> l_process_ovn
);
update pay_process_events ppe
set ppe.status = 'C'
,ppe.description = 'Assignment has been Reconciled'
,ppe.object_version_number =
ppe.object_version_number + 1
where ppe.assignment_id = l_pri_assgn
and change_type = 'PQP_US_ALIEN_WINDSTAR'
and ppe.status = 'R';
select retro_loss_notification_sent
into l_notification_sent
from pqp_analyzed_alien_details
where analyzed_data_details_id = l_analyzed_data_details_id
and analyzed_data_id = l_analyzed_data_id;
pqp_alien_trans_data_api.update_alien_trans_data
(p_validate => false
,p_alien_transaction_id => l_alien_transaction_id
,p_object_version_number => l_transaction_ovn
,p_error_indicator => 'WARNING : RETRO LOSS'
,p_error_text => NVL(l_retro_lose_ben_date_mesg
,l_retro_lose_ben_amt_mesg)
,p_effective_date => TO_DATE('01/01/' ||
p_taxyear, 'DD/MM/YYYY')
);
pqp_analyzed_alien_det_api.update_analyzed_alien_det
(p_validate => false
,p_analyzed_data_details_id => l_analyzed_data_details_id
,p_analyzed_data_id => l_analyzed_data_id
,p_effective_date => TO_DATE('01/01/' ||
p_taxyear, 'DD/MM/YYYY')
,p_retro_loss_notification_sent => 'Y'
,p_object_version_number => l_transaction_ovn
);
pqp_alien_trans_data_api.update_alien_trans_data
(p_validate => false
,p_alien_transaction_id => l_alien_transaction_id
,p_object_version_number => l_transaction_ovn
,p_error_indicator => 'ERROR : NOT_RECONCILED 2'
,p_error_text => l_error_message||'Assignment not Reconciled'
,p_effective_date => TO_DATE('01/01/'||p_taxyear, 'DD/MM/YYYY')
);
pqp_alien_trans_data_api.update_alien_trans_data
(p_validate => false
,p_alien_transaction_id => l_alien_transaction_id
,p_object_version_number => l_atd_ovn
,p_error_indicator => 'ERROR : ORACLE'
,p_error_text => l_error_message
,p_effective_date => to_date('01/01/' ||
p_taxyear, 'DD/MM/YYYY')
);
pay_ppe_api.update_process_event
(p_validate => false
,p_status => 'N'
,p_description => null
,p_process_event_id => p_process_event_id
,p_object_version_number => l_ovn
);
pay_ppe_api.update_process_event
(p_validate => false
,p_status => 'C'
,p_description => 'This record was forcibly ABORTED using workflow'
,p_process_event_id => p_process_event_id
,p_object_version_number => l_ovn
);