[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Function to display the details of the selected employee
********************************************************************/
FUNCTION formated_detail_string(
p_output_file_type in varchar2
,p_year varchar2
,p_gre_name varchar2
,p_pre_name varchar2
,p_employee_name varchar2
,p_employee_sin varchar2
,p_employee_number varchar2
,p_report_type varchar2
) RETURN varchar2
IS
lv_format1 varchar2(22000);
Procedure to display message if no employees are selected for
any of the four sections -
- Processed Assignments
- Eligible Assignments
- Not Eligible Assignments
********************************************************************/
PROCEDURE formated_zero_count(output_file_type varchar2,
p_flag varchar2)
IS
lvc_message1 varchar2(200);
select 1 from dual
where exists
(select 'X'
from hr_organization_information mag,
hr_organization_information gre,
hr_all_organization_units hou,
pay_payroll_actions ppa
where hou.business_group_id = cp_business_group_id
and hou.organization_id = gre.organization_id
and gre.org_information_context = 'Canada Employer Identification'
and gre.organization_id = cp_gre_id
and gre.org_information11 = get_parameter('TRANSMITTER_GRE',ppa.legislative_parameters)
and ppa.business_group_id+0 = cp_business_group_id
and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
and ppa.action_status = 'C'
and ppa.report_type = 'PYT4MAG'
and mag.org_information_context = 'Fed Magnetic Reporting'
and mag.organization_id = to_number(gre.org_information11) );
select 1 from dual
where exists
(select 'X'
from hr_organization_information mag,
hr_organization_information gre,
hr_all_organization_units hou,
pay_payroll_actions ppa
where hou.business_group_id = cp_business_group_id
and hou.organization_id = gre.organization_id
and gre.org_information_context = 'Canada Employer Identification'
and gre.organization_id = cp_gre_id
and gre.org_information11 = get_parameter('TRANSMITTER_GRE',ppa.legislative_parameters)
and ppa.business_group_id+0 = cp_business_group_id
and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
and ppa.action_status = 'C'
and ppa.report_type = 'MAG_T4A'
and mag.org_information_context = 'Fed Magnetic Reporting'
and mag.organization_id = to_number(gre.org_information11) );
select 1 from dual
where exists
(select 'X'
from hr_organization_information pre,
hr_all_organization_units hou,
pay_payroll_actions ppa
where hou.business_group_id = cp_business_group_id
and hou.organization_id = pre.organization_id
and pre.org_information4 = 'P01'
and pre.org_information_context = 'Prov Reporting Est'
and pre.organization_id = cp_pre_id
and decode(pre.org_information3, 'Y', to_char(pre.organization_id), pre.org_information20) =
get_parameter('TRANSMITTER_PRE',ppa.legislative_parameters)
and ppa.business_group_id+0 = cp_business_group_id
and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
and ppa.action_status = 'C'
and ppa.report_type = 'RL1_XML_MAG'); --Bug 7392645
select 1 from dual
where exists
(select 'X'
from hr_organization_information pre,
hr_all_organization_units hou,
pay_payroll_actions ppa
where hou.business_group_id = cp_business_group_id
and hou.organization_id = pre.organization_id
and pre.org_information4 = 'P02'
and pre.org_information_context = 'Prov Reporting Est'
and pre.organization_id = cp_pre_id
and decode(pre.org_information3, 'Y', to_char(pre.organization_id), pre.org_information20) =
get_parameter('TRANSMITTER_PRE',ppa.legislative_parameters)
and ppa.business_group_id+0 = cp_business_group_id
and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
and ppa.action_status = 'C'
and ppa.report_type = 'RL2_XML_MAG'); ----Bug 7392645
select name
from hr_all_organization_units_tl
where organization_id = cp_org_id
and language = userenv('LANG');
select person_id
from per_all_assignments_f
where assignment_id = cp_assign_id;
select full_name,national_identifier
from per_all_people_f
where person_id = cp_person_id;
select assignment_number
from per_all_assignments_f
where assignment_id = cp_assign_id;
l_assignment_inserted number :=0;
select distinct paf.person_id
from hr_assignment_set_amendments has,
per_all_assignments_f paf
where has.assignment_set_id = cp_assign_set_id
and has.include_or_exclude = 'I'
and paf.assignment_id = has.assignment_id
and paf.assignment_type = 'E'
and paf.business_group_id+ 0 = p_bus_grp;
select paa.assignment_action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_action_classifications pac
where paf.person_id = cp_person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = cp_gre_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id+0 = p_bus_grp
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
and ppa.effective_date +0 between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date +0 between cp_effective_date
and add_months(cp_effective_date, 12) - 1
and ((nvl(paa.run_type_id, ppa.run_type_id) is null
and paa.source_action_id is null)
or (nvl(paa.run_type_id, ppa.run_type_id) is not null
and paa.source_action_id is not null)
or (ppa.action_type = 'V'
and ppa.run_type_id is null
and paa.run_type_id is not null
and paa.source_action_id is null))
and not exists (select 1
from pay_payroll_actions ppa1,
pay_assignment_actions paa1
where ppa1.report_type = p_report_type
and ppa1.business_group_id+0 = p_bus_grp
and ppa1.effective_date = add_months(cp_effective_date, 12) - 1
and to_number(get_parameter('TRANSFER_GRE',
ppa1.legislative_parameters)) = cp_gre_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and paa1.serial_number = to_char(paf.person_id))
order by paa.action_sequence desc;
select payroll_action_id
from pay_payroll_actions
where action_type = 'X'
and action_status = 'C'
and report_type = p_report_type
and business_group_id+0 = p_bus_grp
and to_number(get_parameter('TRANSFER_GRE',legislative_parameters)) = cp_gre_id
and effective_date = add_months(cp_effective_date, 12) - 1;
if the assignment selected in the assignment set is secondary
Get the primary assignment for the given person_id */
cursor c_get_asg_id (cp_person_id number) is
select assignment_id
from per_all_assignments_f paf
where person_id = cp_person_id
and primary_flag = 'Y'
and assignment_type = 'E'
and paf.effective_start_date <= add_months(p_effective_date, 12) - 1
and paf.effective_end_date >= p_effective_date
order by assignment_id desc;
/* Get the latest assignment action of selected person */
open c_get_latest_asg(l_person_id,
p_gre_id,
p_effective_date);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
select distinct paf.person_id
from hr_assignment_set_amendments has,
per_all_assignments_f paf
where has.assignment_set_id = cp_assign_set_id
and has.include_or_exclude = 'I'
and paf.assignment_id = has.assignment_id
and paf.assignment_type = 'E'
and paf.business_group_id+0 = p_bus_grp;
select hoi.organization_id
from hr_organization_information hoi,
hr_all_organization_units hou
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = to_char(p_pre_id)
and hou.business_group_id = p_bus_grp
and hou.organization_id = hoi.organization_id;
select paa.assignment_action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f ppf,
pay_payroll_actions ppa,
pay_action_classifications pac
where ppf.person_id = cp_person_id
and paf.person_id = ppf.person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = cp_gre_id
and ppa.business_group_id+0 = p_bus_grp
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date between cp_effective_date
and add_months(cp_effective_date, 12) - 1
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
and not exists (select 1
from pay_payroll_actions ppa1,
pay_assignment_actions paa1
where ppa1.report_type = p_report_type
and ppa1.business_group_id+0 = p_bus_grp
and ppa1.effective_date = add_months(cp_effective_date, 12) - 1
and to_number(get_parameter('PRE_ORGANIZATION_ID',
ppa1.legislative_parameters)) = p_pre_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and paa1.serial_number = to_char(paf.person_id))
order by paa.action_sequence desc;
select payroll_action_id
from pay_payroll_actions
where action_type = 'X'
and action_status = 'C'
and report_type = p_report_type
and business_group_id+0 = p_bus_grp
and to_number(get_parameter('PRE_ORGANIZATION_ID',legislative_parameters)) = cp_pre_id
and effective_date = add_months(cp_effective_date, 12) - 1;
if the assignment selected in the assignment set is secondary
Get the primary assignment for the given person_id */
cursor c_get_asg_id (cp_person_id number) is
select assignment_id
from per_all_assignments_f paf
where person_id = cp_person_id
and primary_flag = 'Y'
and assignment_type = 'E'
and paf.effective_start_date <= add_months(p_effective_date, 12) - 1
and paf.effective_end_date >= p_effective_date
order by assignment_id desc;
/* Get the latest assignment action of selected person */
open c_get_latest_asg(l_person_id,
l_tax_unit_id,
p_effective_date);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
select distinct has.assignment_id
from hr_assignment_set_amendments has,
per_all_assignments_f paf
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and paf.business_group_id+0 = p_bus_grp
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and exists (select 1
from pay_payroll_actions ppa1,
pay_assignment_actions paa1
where ppa1.report_type = p_report_type
and ppa1.business_group_id+0 = p_bus_grp
and ppa1.effective_date = add_months(cp_effective_date, 12) - 1
and to_number(get_parameter('TRANSFER_GRE', ppa1.legislative_parameters))
= cp_gre_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and paa1.serial_number = to_char(paf.person_id));
select distinct has.assignment_id
from hr_assignment_set_amendments has,
per_all_assignments_f paf
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and paf.business_group_id+0 = p_bus_grp
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and exists (select 1
from pay_payroll_actions ppa1,
pay_assignment_actions paa1
where ppa1.report_type = p_report_type
and ppa1.business_group_id+0 = p_bus_grp
and ppa1.effective_date = add_months(cp_effective_date, 12) - 1
and to_number(get_parameter('PRE_ORGANIZATION_ID', ppa1.legislative_parameters))
= cp_pre_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and paa1.serial_number = to_char(paf.person_id));
select distinct has.assignment_id
from hr_assignment_set_amendments has,
per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and nvl(has.include_or_exclude,'I') = 'I'
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and paf.business_group_id+0 = p_bus_grp
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = cp_gre_id
and ppa.business_group_id+0 = p_bus_grp
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q','V','B','I')
and ppa.effective_date between cp_effective_date
and add_months(cp_effective_date, 12) - 1
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y';
select hoi.organization_id
from hr_organization_information hoi,
hr_all_organization_units hou
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = to_char(p_pre_id)
and hou.business_group_id = p_bus_grp
and hou.organization_id = hoi.organization_id;
l_assignment_inserted := l_assignment_inserted + 1;
select distinct has.assignment_id
from hr_assignment_set_amendments has,
per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and nvl(has.include_or_exclude,'I') = 'I'
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and paf.business_group_id+0 = p_bus_grp
and paa.assignment_id = paf.assignment_id
and ppa.business_group_id+0 = p_bus_grp
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q','V','B','I')
and ppa.effective_date between cp_effective_date
and add_months(cp_effective_date, 12) - 1
and paa.tax_unit_id = cp_gre_id
and paf.assignment_type = 'E'
and paf.primary_flag <> 'Y';
select distinct has.assignment_id
from hr_assignment_set_amendments has,
per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and nvl(has.include_or_exclude,'I') = 'I'
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and paf.business_group_id+0 = p_bus_grp
and paa.assignment_id = paf.assignment_id
and ppa.business_group_id+0 = p_bus_grp
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q','V','B','I')
and ppa.effective_date between cp_effective_date
and add_months(cp_effective_date, 12) - 1
and paf.assignment_type = 'E'
and paf.primary_flag <> 'Y'
and paa.tax_unit_id in (select hoi.organization_id
from hr_organization_information hoi,
hr_all_organization_units hou
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = to_char(cp_pre_id)
and hou.business_group_id = p_bus_grp
and hou.organization_id = hoi.organization_id);
select distinct has.assignment_id,
paa.tax_unit_id
from hr_assignment_set_amendments has,
per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and nvl(has.include_or_exclude,'I') = 'I'
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and paf.business_group_id+0 = p_bus_grp
and paf.assignment_type = 'E'
and paa.assignment_id = paf.assignment_id
and ppa.business_group_id+0 = p_bus_grp
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q','V','B','I')
and ppa.effective_date between cp_effective_date
and add_months(cp_effective_date, 12) - 1
and nvl(paa.tax_unit_id, cp_gre_id) <> cp_gre_id;
select distinct has.assignment_id,
hoi.org_information2
from hr_assignment_set_amendments has,
per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_organization_information hoi
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and nvl(has.include_or_exclude,'I') = 'I'
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and paf.business_group_id+0 = p_bus_grp
and paf.assignment_type = 'E'
and paa.assignment_id = paf.assignment_id
and ppa.business_group_id+0 = p_bus_grp
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q','V','B','I')
and ppa.effective_date between cp_effective_date
and add_months(cp_effective_date, 12) - 1
and paa.tax_unit_id = hoi.organization_id
and hoi.org_information_context = 'Canada Employer Identification'
and paa.tax_unit_id not in (select hoi1.organization_id
from hr_organization_information hoi1,
hr_all_organization_units hou1
where hoi1.org_information_context = 'Canada Employer Identification'
and hoi1.org_information2 = to_char(cp_pre_id)
and hou1.business_group_id = p_bus_grp
and hou1.organization_id = hoi1.organization_id);
select name
from hr_all_organization_units_tl
where organization_id = cp_org_id
and language = userenv('LANG');
if l_assignment_inserted = 0 then
hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 110);
l_assignment_inserted := 0;
if l_assignment_inserted = 0 then
formated_zero_count(p_output_file_type,'ELGBLE');
l_assignment_inserted := 0;
if l_assignment_inserted = 0 then
formated_zero_count(p_output_file_type,'NOTELGBLE');
update fnd_concurrent_requests
set output_file_type = 'HTML'
where request_id = FND_GLOBAL.CONC_REQUEST_ID ;