[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqlstr := 'select distinct person_id '||
'from per_people_f ppf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :pactid '||
'and ppa.business_group_id = ppf.business_group_id '||
'order by ppf.person_id';
SELECT
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
SELECT to_char(to_number(p_data), p_format)
INTO l_data FROM dual;
SELECT to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
INTO l_data FROM dual;
select
to_number(substr(pact.legislative_parameters,
instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
(instr(pact.legislative_parameters,'REP_GROUP=')
-
(instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
)) arch_payroll_action_id
from pay_payroll_actions pact
where pact.payroll_action_id = c_mag_payroll_actid;
select pai.action_information1,
pai.action_information2,
pai.action_information3,
pai.action_information4,
pai.action_information5,
pai.action_information6,
pai.action_information7,
pai.action_information8,
pai.action_information9
from pay_payroll_actions ppa,
pay_action_information pai
where ppa.payroll_action_id = c_arch_pay_action_id
and ppa.payroll_action_id = pai.action_context_id
and pai.action_information_category='GB_RTI_FPS_EXTRA_DET'
and pai.action_context_type='PA';
select
to_number(substr(pact.legislative_parameters,
instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
(instr(pact.legislative_parameters,'REP_GROUP=')
-
(instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
)) arch_payroll_action_id
from pay_payroll_actions pact
where pact.payroll_action_id = c_mag_payroll_actid;
select pai.action_information8 PAYROLL_PROD_VER,
pai.action_information4 EMPLOYER_NAME,
pai.action_information6 HMRC_OFFICE_NO,
pai.action_information7 EMPLOYER_PAYE_REF,
pai.action_information11 EMPLOYER_AO_REF,
pai.action_information12 SENDER_ID,
pai.action_information13 TEST_INDICATOR,
pai.action_information14 HEADER_VALUE,
pai.action_information15 TAX_YEAR
from pay_payroll_actions ppa,
pay_action_information pai
where ppa.payroll_action_id = c_arch_pay_action_id
and ppa.payroll_action_id = pai.action_context_id
and pai.action_information_category='RTI PAYROLL INFO'
and pai.action_context_type='PA';
select
to_number(substr(pact.legislative_parameters,
instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
(instr(pact.legislative_parameters,'REP_GROUP=')
-
(instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
)) arch_payroll_action_id
from pay_payroll_actions pact
where pact.payroll_action_id = c_mag_payroll_actid;
select paa_arch.assignment_action_id
from pay_assignment_actions paa_arch,
pay_assignment_actions paa_mag,
pay_action_information pai
where paa_mag.payroll_action_id = c_mag_payroll_actid
and paa_mag.assignment_action_id = c_mag_asg_action_id
and paa_arch.payroll_action_id = c_arch_pay_action_id
and paa_arch.assignment_id = paa_mag.assignment_id
and paa_arch.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'GB_RTI_FPS_ASG_DET1' -- to check whether that asg is archived or not
and pai.action_context_type = 'AAP';
select distinct paa_other.assignment_id,
paa_other.assignment_action_id
from pay_assignment_actions paa_current,
per_all_assignments_f paaf_current,
pay_assignment_actions paa_other,
per_all_assignments_f paaf_other
where paa_current.payroll_action_id = c_arch_pay_action_id
and paa_current.assignment_action_id = c_arch_asg_action_id
and paaf_current.assignment_id = paa_current.assignment_id
and paa_other.payroll_action_id = c_arch_pay_action_id
and paa_other.assignment_action_id <> c_arch_asg_action_id
and paaf_other.assignment_id = paa_other.assignment_id
and paaf_other.person_id = paaf_current.person_id
and paa_current.ACTION_STATUS = 'C'
and paa_other.ACTION_STATUS = 'C';
select pai_addr.action_information5,
pai_addr.action_information6,
pai_addr.action_information7,
pai_addr.action_information8,
pai_addr.action_information13,
pai_empl.action_information3,
pai_empl.action_information4,
pai_empl.action_information5,
pai_empl.action_information6,
pai_addr.action_information12,
pai_empl.action_information7,
pai_empl.action_information8,
pai_empl.action_information9,
pai_empl.action_information16,
pai_empl.action_information12,
pai_empl.action_information13,
pai_empl.action_information14,
pai_empl.action_information15
from pay_action_information pai_addr,
pay_action_information pai_empl
where pai_addr.action_context_id = c_arch_asg_action_id
and pai_addr.action_information_category = 'ADDRESS DETAILS'
and pai_addr.action_context_type = 'AAP'
and pai_empl.action_context_id = c_arch_asg_action_id
and pai_empl.action_information_category = 'GB RTI EMPLOYEE DETAILS'
and pai_empl.action_context_type = 'AAP';
select pai_asg.ACTION_INFORMATION1,
pai_asg.ACTION_INFORMATION2,
pai_asg.ACTION_INFORMATION3,
pai_asg.ACTION_INFORMATION4,
pai_asg.ACTION_INFORMATION5,
pai_asg.ACTION_INFORMATION6,
pai_asg.ACTION_INFORMATION7,
pai_asg.ACTION_INFORMATION8,
pai_asg.ACTION_INFORMATION9,
pai_asg.ACTION_INFORMATION10,
pai_asg.ACTION_INFORMATION12,
pai_asg.ACTION_INFORMATION13,
pai_asg.ACTION_INFORMATION14,
pai_asg.ACTION_INFORMATION15,
pai_asg.ACTION_INFORMATION16,
pai_asg.ACTION_INFORMATION17,
pai_asg.ACTION_INFORMATION18,
pai_asg.ACTION_INFORMATION19,
pai_asg.ACTION_INFORMATION20,
pai_asg.ACTION_INFORMATION21,
pai_asg.ACTION_INFORMATION22,
pai_asg.ACTION_INFORMATION23,
pai_asg.ACTION_INFORMATION24,
pai_asg.ACTION_INFORMATION25,
pai_asg.ACTION_INFORMATION26,
pai_asg.ACTION_INFORMATION28
from pay_action_information pai_asg
where pai_asg.action_context_id = c_arch_asg_action_id
and pai_asg.action_information_category = 'GB_RTI_FPS_ASG_DET1'
and pai_asg.action_context_type = 'AAP';
select pai_asg.ACTION_INFORMATION1,
pai_asg.ACTION_INFORMATION2,
pai_asg.ACTION_INFORMATION3,
pai_asg.ACTION_INFORMATION6,
pai_asg.ACTION_INFORMATION7,
pai_asg.ACTION_INFORMATION8,
pai_asg.ACTION_INFORMATION9,
pai_asg.ACTION_INFORMATION10,
pai_asg.ACTION_INFORMATION11,
pai_asg.ACTION_INFORMATION12,
pai_asg.ACTION_INFORMATION13,
pai_asg.ACTION_INFORMATION14,
pai_asg.ACTION_INFORMATION15,
pai_asg.ACTION_INFORMATION16,
pai_asg.ACTION_INFORMATION17,
pai_asg.ACTION_INFORMATION18,
pai_asg.ACTION_INFORMATION19,
pai_asg.ACTION_INFORMATION20,
pai_asg.ACTION_INFORMATION21,
pai_asg.ACTION_INFORMATION22,
pai_asg.ACTION_INFORMATION25,
pai_asg.ACTION_INFORMATION27
from pay_action_information pai_asg
where pai_asg.action_context_id = c_arch_asg_action_id
and pai_asg.action_information_category = 'GB_RTI_FPS_ASG_DET2'
and pai_asg.action_context_type = 'AAP';
select
ACTION_INFORMATION1,
ACTION_INFORMATION2,
ACTION_INFORMATION3,
ACTION_INFORMATION4,
ACTION_INFORMATION5,
ACTION_INFORMATION6,
ACTION_INFORMATION7,
ACTION_INFORMATION8,
ACTION_INFORMATION9,
ACTION_INFORMATION10
from pay_action_information where action_context_id = l_arch_asg_action_id
and ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_NI_DET'
and ACTION_CONTEXT_TYPE = 'AAP';
select
to_number(substr(pact.legislative_parameters,
instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
(instr(pact.legislative_parameters,'REP_GROUP=')
-
(instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
)) arch_payroll_action_id
from pay_payroll_actions pact
where pact.payroll_action_id=pactid;
select distinct paa.assignment_id
from pay_assignment_actions paa,
pay_action_information pai,
per_all_assignments_f paaf
where paa.payroll_action_id = c_arch_payroll_action_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
and pai.action_context_type = 'AAP'
and paaf.assignment_id = paa.assignment_id
and paaf.person_id between stperson and endperson;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;