[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from pay_payroll_actions pay,
pay_assignment_actions paa
where (pay.report_type like l_mode1
or
pay.report_type like l_mode2) -- Bug 6770200.
and pay.action_status ='C'
and pay.report_qualifier = 'GB'
and pay.report_category = 'EDI'
and pay.payroll_action_id = paa.payroll_action_id
and paa.action_status = 'C'
and paa.assignment_id = p_assignment_id;
select aei.assignment_extra_info_id,
aei.object_version_number,
aei.aei_information1
from pay_assignment_actions paa,
per_assignment_extra_info aei
where paa.assignment_action_id = p_assact
and aei.assignment_id = paa.assignment_id
and aei.information_type = p_type;
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_aei_rec.assignment_extra_info_id,
p_aei_information_category => p_type,
p_aei_information1 => 'Y');
select to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
effective_date,
business_group_id
from pay_payroll_actions
where payroll_action_id = pactid;
select asg.assignment_id
from per_all_people_f pap,
per_assignments_f asg,
per_periods_of_service serv,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where pap.person_id between stperson and endperson
and pap.current_employee_flag = 'Y'
and pap.person_id = asg.person_id
and asg.business_group_id = l_business_group_id
and asg.payroll_id = pay.payroll_id
and asg.period_of_service_id = serv.period_of_service_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and (l_payroll_id IS NULL
or
l_payroll_id = pay.payroll_id)
and serv.date_start <= l_effective_date
and l_effective_date between asg.effective_start_date and asg.effective_end_date
and l_effective_date between pap.effective_start_date and pap.effective_end_date
and l_effective_date between pay.effective_start_date and pay.effective_end_date;
select aei_information1,
decode(p_info_type,'GB_P45_3', aei_information8
,'GB_P46PENNOT', aei_information4
,'GB_P46', aei_information3)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_info_type;
select aei_information5,
aei_information6
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_info_type;
select 1
from pay_action_information pa
,pay_payroll_actions ppa
,pay_assignment_actions paa
where pa.action_information_category in 'GB P46_5 EDI'
and pa.action_context_type = 'AAP'
and pa.action_information4 = default_archive
and pa.assignment_id = p_assignment_id
and paa.assignment_action_id = pa.action_context_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_status = 'C';
select act.assignment_action_id
from pay_payroll_actions pact,
pay_assignment_actions act
where pact.report_type = 'P46_5'
and pact.action_status ='C'
and pact.report_qualifier = 'GB'
and pact.report_category = 'EDI'
and pact.payroll_action_id = act.payroll_action_id
and act.action_status = 'C'
and act.assignment_id = p_assignment_id;
select pay_assignment_actions_s.nextval
into l_ass_act_id
from dual;
delete pay_action_interlocks where locked_action_id = l_locked_action_id;
select aei.assignment_extra_info_id,
aei.aei_information1 send_edi,
aei.aei_information2 annual_pension,
aei.aei_information3 date_pension_start,
aei.aei_information4 static_flag,
aei.aei_information5 prev_emp_paye_ref,
aei.aei_information6 date_left_prev_emp,
aei.aei_information7 prev_tax_code,
aei.aei_information8 prev_tax_basis,
aei.aei_information9 prev_last_pay_period_type,
aei.aei_information10 prev_last_pay_period,
aei.aei_information11 recently_bereaved,
aei.object_version_number
from per_assignment_extra_info aei
where aei.assignment_id = p_person_rec.assignment_id
and aei.information_type = p_info_type;
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_45_46_pennot_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_P46PENNOT',
p_aei_information1 => 'N');
select element_type_id
from pay_element_types_f
where element_name = p_name
and legislation_code = 'GB';
select /*+ ORDERED */
asg.assignment_id,
asg.effective_start_date,
asg.effective_end_date
from pay_assignment_actions paa,
per_assignments_f asg
where paa.assignment_action_id = p_assactid
and paa.assignment_id = asg.assignment_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date;
select /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
pact PAY_PAYROLL_ACTIONS_PK,
r2 PAY_RUN_RESULTS_N50)
USE_NL(assact2, pact, r2) */
/* to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type|| r2.run_result_id),17))
from pay_assignment_actions assact2,
pay_payroll_actions pact,
pay_run_results r2
where assact2.assignment_id = l_assignment_id
and r2.element_type_id+0 = p_element_id
and r2.assignment_action_id = assact2.assignment_action_id
and r2.status IN ('P', 'PA')
and pact.payroll_action_id = assact2.payroll_action_id
and pact.action_type IN ( 'Q','R','B','I')
and assact2.action_status = 'C'
and pact.effective_date between l_asg_start and l_asg_end
and not exists(
select '1'
from pay_action_interlocks pai,
pay_assignment_actions assact3,
pay_payroll_actions pact3
where pai.locked_action_id = assact2.assignment_action_id
and pai.locking_action_id = assact3.assignment_action_id
and pact3.payroll_action_id = assact3.payroll_action_id
and pact3.action_type = 'V'
and assact3.action_status = 'C');
select max(decode(name,'Tax Code',result_value,NULL)) tax_code,
max(decode(name,'Tax Basis',result_value,NULL)) tax_basis,
to_number(max(decode(name,'Pay Previous',
fnd_number.canonical_to_number(result_value),NULL))) pay_previous,
to_number(max(decode(name,'Tax Previous',
fnd_number.canonical_to_number(result_value),NULL))) tax_previous
from pay_input_values_f v,
pay_run_result_values rrv
where rrv.run_result_id = l_run_result_id
and v.input_value_id = rrv.input_value_id
and v.element_type_id = l_element_type_id;
select max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
max(decode(iv.name,'Pay Previous',screen_entry_value)) pay_previous,
max(decode(iv.name,'Tax Previous',screen_entry_value)) tax_previous
from pay_element_entries_f e,
pay_element_entry_values_f v,
pay_input_values_f iv,
pay_element_links_f link
where e.assignment_id = l_assignment_id
and link.element_type_id = l_paye_details_id
and e.element_link_id = link.element_link_id
and e.element_entry_id = v.element_entry_id
and iv.input_value_id = v.input_value_id
and p_effective_date between e.effective_start_date and e.effective_end_date -- 5660011
and p_effective_date between v.effective_start_date and v.effective_end_date
and p_effective_date between link.effective_start_date and link.effective_end_date
and e.effective_end_date between link.effective_start_date and link.effective_end_date
and e.effective_end_date between iv.effective_start_date and iv.effective_end_date
and e.effective_end_date between v.effective_start_date and v.effective_end_date ;
/*and e.effective_end_date = (select max(e1.effective_end_date)
from pay_element_entries_f e1,
pay_element_links_f link1
where link1.element_type_id = l_paye_details_id
and e1.assignment_id = l_assignment_id
and e1.element_link_id = link1.element_link_id);
select upper(substr(addr.address_line1,1,35)) addr1,
upper(substr(addr.address_line2,1,35)) addr2,
upper(substr(addr.address_line3,1,35)) addr3,
upper(hr_general.decode_lookup('GB_COUNTY', substr(addr.region_1,1,35))) county,
addr.postal_code post_code,
upper(addr.town_or_city) town_or_city,
upper(addr.country) country
from per_addresses addr
where addr.person_id(+) = p_person_id
and ( addr.primary_flag = 'Y'
or addr.primary_flag is null)
and p_effective_date between nvl(addr.date_from,fnd_date.canonical_to_date('0001/01/01 00:00:00'))
and nvl(addr.date_to, fnd_date.canonical_to_date('4712/12/31 00:00:00'));
select territory_short_name
from fnd_territories_vl
where territory_code = p_code;
select /*+ ORDERED */
pap.person_id,
paa.assignment_id,
pap.title,
pap.first_name,
pap.last_name,
pap.middle_names,
paa.ASSIGNMENT_NUMBER,
pap.national_identifier,
pap.sex,
fnd_date.date_to_canonical(pap.date_of_birth) date_of_birth,
fnd_date.date_to_canonical(decode(pap.current_employee_flag, 'Y', serv.date_start, null)) hire_date
from pay_assignment_actions act,
per_assignments_f paa,
per_people_f pap,
per_periods_of_service serv
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and paa.person_id = pap.person_id
and paa.period_of_service_id = serv.period_of_service_id
and serv.date_start <= p_effective_date
and p_effective_date between paa.effective_start_date and paa.effective_end_date
and p_effective_date between pap.effective_start_date and pap.effective_end_date;
select pay_get_job_segment_pkg.get_job_segment(paa.business_group_id,job.job_definition_id,act.payroll_action_id) job
from pay_assignment_actions act,
per_assignments_f paa,
per_jobs job
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and paa.job_id = job.job_id(+)
and p_effective_date between paa.effective_start_date and paa.effective_end_date;
select aei.assignment_extra_info_id,
aei.aei_information1 send_edi,
aei.aei_information2 prev_tax_district,
aei.aei_information3 date_left,
aei.aei_information4 prev_tax_code,
aei.aei_information5 prev_tax_basis,
aei.aei_information6 prev_period_type,
aei.aei_information7 prev_period,
aei.aei_information8 static_flag,
/*changes for P45PT_3 start*/
aei.aei_information9 prev_tax_paid_notified,
aei.aei_information10 not_paid_between_start_and5apr,
aei.aei_information11 continue_sl_deductions,
/*changes for P45PT_3 start*/
--Bug 6994632 fetching Prev Tax Pay Notified value
aei.aei_information12 prev_tax_pay_notified,
aei.object_version_number
from per_assignment_extra_info aei
where aei.assignment_id = p_person_rec.assignment_id
and aei.information_type = 'GB_P45_3';
select nvl(min(decode(inv.name, 'Start Date', eev.screen_entry_value, 'X')),'X') s_date,
nvl(min(decode(inv.name, 'End Date', eev.screen_entry_value, null)),'4712/12/31 00:00:00') e_date,
fnd_date.date_to_canonical(min(decode(inv.name, 'End Date', eev.effective_end_date, fnd_date.canonical_to_date('4712/12/31 00:00:00')))) eff_date
from pay_element_types_f elt,
pay_element_entries_f ele,
pay_input_values_f inv,
pay_element_entry_values_f eev
where elt.element_name = 'Student Loan'
and ele.element_type_id = elt.element_type_id
and ele.assignment_id = p_person_rec.assignment_id
and inv.element_type_id = elt.element_type_id
and eev.input_value_id + 0 = inv.input_value_id
and eev.element_entry_id = ele.element_entry_id -- Bug 5469122
and p_effective_date between elt.effective_start_date and elt.effective_end_date
and p_effective_date between ele.effective_start_date and ele.effective_end_date
and p_effective_date between inv.effective_start_date and inv.effective_end_date
and p_effective_date between eev.effective_start_date and eev.effective_end_date;
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_p45_3_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_P45_3',
p_aei_information1 => 'N');
select aei.assignment_extra_info_id,
aei.aei_information1 send_edi,
aei.aei_information2 p46_statement,
aei.aei_information3 static_flag,
aei.aei_information4 student_loan,
aei.object_version_number
from per_assignment_extra_info aei
where aei.assignment_id = p_person_rec.assignment_id
and aei.information_type = 'GB_P46';
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_P46',
p_aei_information1 => 'N');
select aei.assignment_extra_info_id,
aei.aei_information1 send_edi,
aei.aei_information2 p46_statement,
aei.aei_information3 static_flag,
aei.aei_information4 student_loan,
aei.aei_information5 default_send_edi,
aei.aei_information6 default_static_edi,
aei.object_version_number
from per_assignment_extra_info aei
where aei.assignment_id = p_person_rec.assignment_id
and aei.information_type = 'GB_P46';
select 1
from pay_action_information pa
,pay_payroll_actions ppa
,pay_assignment_actions paa
where pa.action_information_category = 'GB P46_5 EDI'
and pa.action_context_type = 'AAP'
and pa.action_information4 = 'Y'
and pa.assignment_id = p_person_rec.assignment_id
and paa.assignment_action_id = pa.action_context_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_status = 'C';
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_P46',
p_aei_information1 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_P46',
p_aei_information5 => 'N');
select aei.assignment_extra_info_id,
aei.aei_information1 send_edi,
aei.aei_information2 annual_pension,
aei.aei_information3 date_pension_start,
aei.aei_information4 static_flag,
aei.object_version_number
from per_assignment_extra_info aei
where aei.assignment_id = p_person_rec.assignment_id
and aei.information_type = 'GB_P46PENNOT';
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_p46p_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_P46PENNOT',
p_aei_information1 => 'N');
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;
select upper(hoi.org_information11),
upper(hoi.org_information1),
upper(hoi.org_information2),
upper(hoi.org_information3),
upper(hoi.org_information4)
from pay_payroll_actions pact,
hr_organization_information hoi
where pact.payroll_action_id = p_payroll_action_id
and pact.business_group_id = hoi.organization_id
and hoi.org_information_context = 'Tax Details References'
and (hoi.org_information10 is null
OR
hoi.org_information10 = 'UK')
and upper(hoi.org_information1) =
upper(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));
SELECT
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TEST'),1,1) test_indicator,
trim(substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TEST_ID'),1,8)) test_id,
report_type
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
sqlstr := '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 report_type
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_assactid
and paa.payroll_action_id = ppa.payroll_action_id;
insert_archive_row(p_assactid, p_effective_date, l_archive_tab);
select legislative_parameters para,
fnd_number.number_to_canonical(request_id) control_id,
report_type,
business_group_id
from pay_payroll_actions
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 /*+ ORDERED */
peo.first_name f_name ,
peo.middle_names m_name,
peo.last_name l_name,
peo.title title,
paf.assignment_number emp_no,
peo.national_identifier ni_no
from pay_payroll_actions pay,
pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f peo
where pay.payroll_action_id = pactid
and paa.payroll_action_id = pay.payroll_action_id
and paa.action_status = p_type
and paf.assignment_id = paa.assignment_id
and peo.person_id = paf.person_id
and pay.effective_date between paf.effective_start_date and paf.effective_end_date
and pay.effective_date between peo.effective_start_date and peo.effective_end_date;
select pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST_ID'),
pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST'),
/*ppa.effective_date*/
sysdate
from pay_payroll_actions ppa
,pay_assignment_actions paa
where paa.assignment_action_id = c_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;