[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_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
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 substr(to_char(sysdate, 'YYYYMMDD'),1,8),substr(to_char(sysdate, 'HH24MISS'),1,6) into l_system_date,l_system_time 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_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
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 ASG DETAILS'
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
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_INFORMATION3,
pai_asg.ACTION_INFORMATION4,
pai_asg.ACTION_INFORMATION5,
pai_asg.ACTION_INFORMATION6,
pai_asg.ACTION_INFORMATION7,
pai_asg.ACTION_INFORMATION8
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 ASG DETAILS'
and pai_asg.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 = 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 EMPLOYEE DETAILS'
and pai.action_context_type = 'AAP';
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
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
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_ASG_DETAILS'
and pai.action_information_category = 'GB RTI EMPLOYEE DETAILS'
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;