[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
distinct pap.person_id p_person_id,
paa.assignment_id,
pap.employee_number emp_no,
substr(trim(pap.last_name), 1,35) last_name,
substr(trim(pap.first_name), 1,least(decode(instr(trim(pap.first_name),' '),0,35,instr(trim(pap.first_name),' ')),35)) first_name,
substr(pap.national_identifier,1,9) national_identifier,
pap.effective_start_date effective_start_date
FROM pay_assignment_actions act,
per_all_assignments_f paa,
per_all_people_f pap
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and paa.person_id = pap.person_id
and pap.effective_start_date <= p_effective_date
and paa.effective_start_date <= p_effective_date
order by pap.effective_start_date desc;
PROCEDURE insert_archive_row(p_assactid IN NUMBER,
p_effective_date IN DATE,
p_tab_rec_data IN action_info_table) IS
l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
END insert_archive_row;
Purpose : This returns the select statement that is used to create the
range rows.
Arguments :
Notes : The range cursor determines which people should be processed.
The normal practice is to include everyone, and then limit
the list during the assignment action creation.
--------------------------------------------------------------------------*/
PROCEDURE range_cursor (pactid IN NUMBER,
sqlstr OUT NOCOPY VARCHAR2)
IS
cursor csr_parameter_info IS
SELECT report_type
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
sqlstr := 'select distinct person_id '||
'from per_all_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 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
-- to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_FROM=') + 13,10),'YYYY-MM-DD') payment_from_date,
-- to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_TO=') + 11,10),'YYYY-MM-DD') payment_to_date,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_FROM'),1,10)) payment_from_date,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_TO'),1,10)) payment_to_date,
business_group_id
from pay_payroll_actions pact
where payroll_action_id = pactid;
select distinct paaf.person_id,max(peef.assignment_id) assignment_id
from pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entry_values_f peev,
pay_element_entries_f peef,
per_all_assignments_f paaf,
hr_soft_coding_keyflex sc,
pay_all_payrolls_f ppf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pet.element_name in ('Court Order','Court Order NTPP')
AND pet.business_group_id IS NULL
AND pet.legislation_code = 'GB'
AND pet.element_type_id = piv.element_type_id
AND piv.business_group_id IS NULL
AND piv.legislation_code = 'GB'
AND piv.name = 'Type'
AND piv.input_value_id = peev.input_value_id
AND peev.screen_entry_value in ('DEO','DEO_PERCENT','CMS_DEO')
AND peev.element_entry_id = peef.element_entry_id
AND paaf.person_id between stperson and endperson
AND peef.assignment_id = paaf.assignment_id
AND ppf.soft_coding_keyflex_id = sc.soft_coding_keyflex_id
AND sc.segment1 = p_asg_tax_ref
AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
AND paaf.payroll_id = ppf.payroll_id
AND ppa.payroll_id = ppf.payroll_id
AND paaf.assignment_id = paa.assignment_id
AND paa.assignment_id = peef.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.business_group_id = p_business_group_id
AND paaf.business_group_id = ppa.business_group_id
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND paa.SOURCE_ACTION_ID is not null
AND ppa.action_type in ('Q','R')
AND ppa.EFFECTIVE_DATE between p_payment_from_date and p_payment_to_date
--AND ppa.EFFECTIVE_DATE between paaf.effective_start_date AND paaf.effective_end_date
AND ppa.EFFECTIVE_DATE between peef.effective_start_date AND peef.effective_end_date
AND ppa.EFFECTIVE_DATE between peev.effective_start_date AND peev.effective_end_date
AND ppa.EFFECTIVE_DATE between piv.effective_start_date AND piv.effective_end_date
AND ppa.EFFECTIVE_DATE between ppf.effective_start_date AND ppf.effective_end_date
AND paaf.effective_start_date =
(select max(paaf1.effective_start_date)
from per_all_assignments_f paaf1 where
paaf1.assignment_id = paaf.assignment_id
and paaf1.assignment_type = 'E'
and paaf1.effective_start_date <= ppa.EFFECTIVE_DATE)
group by paaf.person_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
-- Insert assignment into pay_assignment_actions
hr_nonrun_asact.insact
(
lockingactid,
asg_rec.assignment_id,
pactid,
chunk,
null
);
select substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'TAX_REF'),1,20) tax_ref,
to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
--to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_FROM=') + 13,10),'YYYY-MM-DD') payment_from_date,
--to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_TO=') + 11,10),'YYYY-MM-DD') payment_to_date,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_FROM'),1,10)) payment_from_date,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_TO'),1,10)) payment_to_date,
business_group_id
from pay_payroll_actions pact,
pay_assignment_actions paa
where paa.assignment_action_id = p_assactid
and paa.payroll_action_id = pact.payroll_action_id;
select paaf.person_id person_id,max(peef.assignment_id) assignment_id,max(peef.element_entry_id) element_entry_id,peev2.screen_entry_value employee_ref
from pay_element_types_f pet1,
pay_input_values_f piv1,
pay_input_values_f piv2,
pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_element_entries_f peef,
per_all_assignments_f paaf,
hr_soft_coding_keyflex scl,
pay_all_payrolls_f ppf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pet1.element_name in ('Court Order','Court Order NTPP')
AND pet1.business_group_id IS NULL
AND pet1.legislation_code = 'GB'
AND pet1.element_type_id = piv1.element_type_id
AND piv1.business_group_id IS NULL
AND piv1.legislation_code = 'GB'
AND piv1.name = 'Type'
AND piv1.input_value_id = peev1.input_value_id
AND peev1.screen_entry_value in ('DEO','DEO_PERCENT','CMS_DEO')
AND peev1.element_entry_id = peef.element_entry_id
AND paaf.person_id = p_person_id
AND peef.assignment_id = paaf.assignment_id
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
AND paaf.payroll_id = ppf.payroll_id
AND ppa.payroll_id = ppf.payroll_id
AND peev1.element_entry_id = peev2.element_entry_id
AND piv1.element_type_id = piv2.element_type_id
AND peev2.input_value_id = piv2.input_value_id
AND piv2.name in ('Reference')
AND piv2.business_group_id IS NULL
AND piv2.legislation_code = 'GB'
AND paaf.assignment_id = paa.assignment_id
AND paa.assignment_id = peef.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = ppf.payroll_id
AND ppa.business_group_id = p_business_group_id
AND paaf.business_group_id = ppa.business_group_id
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND paa.SOURCE_ACTION_ID is not null
AND ppa.action_type in ('Q','R')
AND ppa.EFFECTIVE_DATE between p_payment_from_date and p_payment_to_date
-- AND ppa.EFFECTIVE_DATE between paaf.effective_start_date AND paaf.effective_end_date
AND ppa.EFFECTIVE_DATE between peef.effective_start_date AND peef.effective_end_date
AND ppa.EFFECTIVE_DATE between peev1.effective_start_date AND peev1.effective_end_date
AND ppa.EFFECTIVE_DATE between peev2.effective_start_date AND peev2.effective_end_date
AND ppa.EFFECTIVE_DATE between piv1.effective_start_date AND piv1.effective_end_date
AND ppa.EFFECTIVE_DATE between piv2.effective_start_date AND piv2.effective_end_date
AND ppa.EFFECTIVE_DATE between ppf.effective_start_date AND ppf.effective_end_date
AND paaf.effective_start_date =
(select max(paaf1.effective_start_date)
from per_all_assignments_f paaf1 where
paaf1.assignment_id = paaf.assignment_id
and paaf1.assignment_type = 'E'
and paaf1.effective_start_date <= ppa.EFFECTIVE_DATE)
group by paaf.person_id,peev2.screen_entry_value;
select SCREEN_ENTRY_VALUE
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and INPUT_VALUE_ID
in (select INPUT_VALUE_ID from pay_input_values_f where name = 'DEO Underpayment Reason');
select paa.assignment_action_id asgactid,
peef.assignment_id,ppa.EFFECTIVE_DATE
from pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entry_values_f peev,
pay_element_entries_f peef,
per_all_assignments_f paaf,
hr_soft_coding_keyflex sc,
pay_all_payrolls_f ppf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pet.element_name in ('Court Order','Court Order NTPP')
AND pet.business_group_id IS NULL
AND pet.legislation_code = 'GB'
AND pet.element_type_id = piv.element_type_id
AND piv.business_group_id IS NULL
AND piv.legislation_code = 'GB'
AND piv.name = 'Reference'
AND piv.input_value_id = peev.input_value_id
AND nvl(peev.screen_entry_value, ' ') = nvl(p_employee_ref, ' ')
AND peev.element_entry_id = p_element_entry_id
AND peev.element_entry_id = peef.element_entry_id
AND peef.assignment_id = paaf.assignment_id
AND ppf.soft_coding_keyflex_id = sc.soft_coding_keyflex_id
AND sc.segment1 = p_asg_tax_ref
AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
AND paaf.payroll_id = ppf.payroll_id
AND ppa.payroll_id = ppf.payroll_id
AND paaf.assignment_id = p_assignment_id
AND paaf.assignment_id = paa.assignment_id
AND paa.assignment_id = peef.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.business_group_id = p_business_group_id
AND paaf.business_group_id = ppa.business_group_id
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND paa.SOURCE_ACTION_ID is not null
AND ppa.action_type in ('Q','R')
AND ppa.EFFECTIVE_DATE between p_payment_from_date and p_payment_to_date
--AND ppa.EFFECTIVE_DATE between paaf.effective_start_date AND paaf.effective_end_date
AND ppa.EFFECTIVE_DATE between peef.effective_start_date AND peef.effective_end_date
AND ppa.EFFECTIVE_DATE between peev.effective_start_date AND peev.effective_end_date
AND ppa.EFFECTIVE_DATE between piv.effective_start_date AND piv.effective_end_date
AND ppa.EFFECTIVE_DATE between ppf.effective_start_date AND ppf.effective_end_date
AND paaf.effective_start_date =
(select max(paaf1.effective_start_date)
from per_all_assignments_f paaf1 where
paaf1.assignment_id = paaf.assignment_id
and paaf1.assignment_type = 'E'
and paaf1.effective_start_date <= ppa.EFFECTIVE_DATE)
order by peef.assignment_id,paa.assignment_action_id;
SELECT defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension_name
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id;
insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
Select report_type
From pay_payroll_actions pact
Where pact.payroll_action_id = pactid;
select substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
--to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_FROM=') + 13,10),'YYYY-MM-DD') payment_from_date,
--to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_TO=') + 11,10),'YYYY-MM-DD') payment_to_date,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_FROM'),1,10)) payment_from_date,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_TO'),1,10)) payment_to_date,
business_group_id,
pact.effective_date,
pay_gb_eoy_archive.get_parameter(legislative_parameters,'REPORT_FORMAT') report_format,
fnd_number.number_to_canonical(pact.request_id) request_id,
nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
nvl(substr(hoi.org_information20,1,12),' ') employer_no
from pay_payroll_actions pact,
hr_organization_information hoi
where payroll_action_id = pactid
and pact.business_group_id = hoi.organization_id
and hoi.org_information_context = 'Tax Details References'
and nvl(hoi.org_information10,'UK') = 'UK'
and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
select ppf.payroll_name
from pay_payrolls_f ppf
where ppf.payroll_id = p_payroll_id
and ppf.business_group_id = p_business_group_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
select legislative_parameters para,
fnd_number.number_to_canonical(request_id) control_id,
report_type,
business_group_id,
pact.effective_date
from pay_payroll_actions pact
where payroll_action_id = pactid;
select
to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
-- to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_FROM=') + 13,10),'YYYY-MM-DD') payment_from_date,
-- to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_TO=') + 11,10),'YYYY-MM-DD') payment_to_date,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_FROM'),1,10)) payment_from_date,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_TO'),1,10)) payment_to_date
from pay_payroll_actions pact
where payroll_action_id = pactid;
select nvl(hoi.org_information11,' ') sender_id,
nvl(upper(hoi.org_information2),' ') hrmc_office,
nvl(upper(hoi.org_information4),' ') er_addr,
nvl(upper(hoi.org_information3),' ') er_name
from hr_organization_information hoi
where hoi.organization_id = p_bus_id
and hoi.org_information_context = 'Tax Details References'
and nvl(hoi.org_information10,'UK') = 'UK'
and upper(hoi.org_information1) = upper(p_tax_ref);
select ppf.payroll_name
from pay_payrolls_f ppf
where ppf.payroll_id = p_payroll_id
and ppf.business_group_id = p_business_group_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
select distinct paa.assignment_action_id asg_action_id, paa.assignment_id
, paaf.assignment_number
from pay_payroll_actions ppa,
pay_assignment_actions paa
, per_all_assignments_f paaf
where ppa.payroll_action_id = pactid -- pact_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paaf.assignment_id = paa.assignment_id
and paaf.effective_start_date =
(
SELECT MAX(paaf2.effective_start_date)
FROM per_all_assignments_f paaf2
WHERE paaf2.assignment_id = paaf.assignment_id
)
ORDER BY
paaf.assignment_number;
select pai_emp.action_information3 f_name,
pai_emp.action_information4 l_name,
nvl(pai_emp.action_information5,' ')ni_no,
nvl(pai_emp.action_information6,' ')emp_no
from pay_action_information pai_emp,
pay_assignment_actions paa
where paa.payroll_action_id = pactid and paa.ACTION_STATUS = 'C' and paa.assignment_id = c_assignment_id
and pai_emp.action_context_id = paa.assignment_action_id
and pai_emp.action_information_category = 'GB DEO EMPLOYEE DETAILS';
select count(distinct(paaf.person_id))
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paaf
where ppa.payroll_action_id = pactid -- pact_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paaf.assignment_id = paa.assignment_id;
delete from pay_action_information pai
where pai.action_context_id = pactid
and pai.action_context_type = 'PA'
and pai.action_information_category in ('GB DEO EMPLOYER DETAILS');