[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sql := 'select distinct person_id ' ||
'from per_people_f ppf, ' ||
'pay_payroll_actions ppa ' ||
'where ppa.payroll_action_id = :payroll_action_id ' ||
'and ppa.business_group_id = ppf.business_group_id ' ||
'order by ppf.person_id';
select nvl(to_date(to_char(to_date(pay_core_utils.get_parameter('EFFECTIVE_DATE', legislative_parameters),
'YYYY/MM/DD'),'DD-MM-YYYY'),'DD-MM-YYYY'),
to_date('31-12-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY')),
pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters),
to_date('01-01-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
to_date('31-12-'|| pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters),
pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters)
from pay_payroll_actions
where payroll_action_id =c_payroll_Action_id;
select pay_assignment_actions_s.nextval
from dual;
select distinct a.assignment_id
from per_assignments_f a,
pay_payroll_actions pa
where pa.payroll_action_id = c_payroll_action_id
and a.person_id between c_start_person_id and c_end_person_id
and a.business_group_id = pa.business_group_id
and ( a.effective_start_date <= v_basis_end and a.effective_end_date>= v_basis_start)
----------------------------------------------------------------------
-- Do not select the person if they have had an IR21 Form produced
----------------------------------------------------------------------
and not exists
( select null
from per_people_extra_info pei
where pei.person_id = a.person_id
and pei.pei_information1 is not null
and pei.information_type = 'HR_IR21_PROCESSING_DATES_SG'
)
----------------------------------------------------------------------
-- Do not select the person if they have had any Magtape File produced for the same
-- Business Group, Legal Entity and Basis Year
-- If they want to re-archive a person, they must ROLLBACK the magtape first
----------------------------------------------------------------------
and not exists
( select null
from per_assignments_f paf,
pay_assignment_actions mcl,
pay_payroll_actions mpl
where paf.assignment_id = a.assignment_id
and paf.assignment_id = mcl.assignment_id
and mpl.payroll_action_id = mcl.payroll_action_id
and mpl.business_group_id = pa.business_group_id
and mpl.effective_date between v_basis_start and v_basis_end
and pay_core_utils.get_parameter('LEGAL_ENTITY_ID',mpl.legislative_parameters) = v_legal_entity_id
and mpl.report_type in ( 'SG_A8B','SG_IR8A','SG_IR8S','SG_A8A' )
and mpl.action_type = 'X'
and mcl.action_status = 'C'
group by paf.assignment_id
)
--------------------------------------------------------------------------------------------------------------------
-- Select an assignment if any payroll runs exist for assignment in processing
-- year and legal entity.
--------------------------------------------------------------------------------------------------------------------
and exists
( select null
from per_assignments_f paf,
pay_assignment_actions pac,
pay_payroll_actions ppa
where paf.assignment_id = a.assignment_id
and paf.assignment_id = pac.assignment_id
and pac.tax_unit_id = v_legal_entity_id
and ppa.payroll_action_id = pac.payroll_action_id
and ppa.action_type in ('R','B','I','Q','V')
and pac.action_status = 'C'
and ppa.business_group_id = v_business_group_id
and ppa.effective_date between v_basis_start and v_basis_end
group by paf.assignment_id
);
select nvl(pap.national_identifier,per_information12),
fnd_date.date_to_canonical(pap.start_date), /* Bug# 3910804 */
paa.person_id,
pac.assignment_id
from pay_assignment_actions pac,
per_assignments_f paa,
per_people_f pap
where pac.assignment_action_id = c_assignment_action_id
and paa.assignment_id = pac.assignment_id
and paa.person_id = pap.person_id;
select payroll_action_id
into v_setup_action
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id ;
hr_utility.trace(l_package_name||'Archive_Code - Before pai insert');
insert into pay_action_information
( action_information_id,
action_context_id,
action_context_type,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id )
values
( pay_action_information_s.nextval,
v_setup_action,
'AAP',
'SG_IRAS_SETUP',
v_national_identifier,
v_person_id,
v_start_date,
v_assignment_id );
hr_utility.trace(l_package_name||'Archive_Code - After pai insert');
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters),
pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters),
pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),
pay_core_utils.get_parameter('MAGNETIC_FILE_NAME',legislative_parameters),
pay_core_utils.get_parameter('ACTION_PARAMETER_GROUP',legislative_parameters)
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;