[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select upper(substr(trim(addr.address_line1),1,35)) addr1,
upper(substr(trim(addr.address_line2),1,35)) addr2,
upper(substr(trim(addr.address_line3),1,35)) addr3,
substr(addr.postal_code,1,10) post_code,
upper(substr(trim(addr.town_or_city),1,35)) addr4,
upper(substr(trim(addr.country),1,35)) 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 distinct pap.effective_start_date , pap.effective_end_date , pap.current_employee_flag current_employee_flag
from per_all_assignments_f paa,
per_all_people_f pap,
per_periods_of_service serv
where paa.assignment_id = asg_id
and pap.person_id = paa.person_id
and paa.period_of_service_id = serv.period_of_service_id and
(serv.actual_termination_date is not null and serv.actual_termination_date+1 between pap.effective_start_date and pap.effective_end_date);
SELECT
pap.person_id p_person_id,
paa.assignment_id,
paa.assignment_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(trim(pap.middle_names), 1,least(decode(instr(trim(pap.middle_names),' '),0,35,instr(trim(pap.middle_names),' ')),35)) middle_name,
pap.title title,
substr(pap.national_identifier,1,9) national_identifier,
pap.date_of_birth date_of_birth,
substr(pap.sex,1,1) sex ,
decode(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
decode(pap.per_information9,'Y','Y',NULL) multiple_asg_flag,
current_employee_flag,
pap.effective_start_date effective_start_date,
serv.actual_termination_date actual_termination_date
FROM pay_assignment_actions act,
per_all_assignments_f paa,
per_all_people_f pap,
per_periods_of_service serv,
per_assignment_status_types past
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and paa.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paa.person_id = pap.person_id
and paa.period_of_service_id = serv.period_of_service_id
and p_effective_date between pap.effective_start_date and pap.effective_end_date
and p_effective_date between paa.effective_start_date and paa.effective_end_date
order by pap.effective_start_date desc;
SELECT
pap.person_id p_person_id,
paa.assignment_id,
paa.assignment_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(trim(pap.middle_names), 1,least(decode(instr(trim(pap.middle_names),' '),0,35,instr(trim(pap.middle_names),' ')),35)) middle_name,
pap.title title,
substr(pap.national_identifier,1,9) national_identifier,
pap.date_of_birth date_of_birth,
substr(pap.sex,1,1) sex ,
decode(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
decode(pap.per_information9,'Y','Y',NULL) multiple_asg_flag,
current_employee_flag,
pap.effective_start_date effective_start_date,
serv.actual_termination_date actual_termination_date
FROM pay_assignment_actions act,
per_all_assignments_f paa,
per_all_people_f pap,
per_periods_of_service serv,
per_assignment_status_types past
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and paa.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paa.person_id = pap.person_id
and paa.period_of_service_id = serv.period_of_service_id
order by pap.effective_start_date desc;
SELECT AEI_INFORMATION5
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
AND assignment_id = p_assignment_id;
l_sqlstr := 'select ' || l_column_name ||
' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
l_sqlstr := 'select ' || l_column_name ||
' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
' and '||p_effective_start_date||' between effective_start_date and effective_end_date' ;
SELECT definition
INTO l_package
FROM ff_functions
WHERE name = l_function_name
and business_group_id = p_business_group_id;
'SELECT '
|| l_package
|| ' (:p_assignment_id,:p_eff_date) '
|| 'FROM DUAL';
SELECT AEI_INFORMATION6
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
AND assignment_id = p_assignment_id;
l_sqlstr := 'select ' || l_column_name ||
' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
l_sqlstr := 'select ' || l_column_name ||
' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
' and '||p_effective_start_date||' between effective_start_date and effective_end_date' ;
SELECT definition
INTO l_package
FROM ff_functions
WHERE name = l_function_name
and business_group_id = p_business_group_id;
'SELECT '
|| l_package
|| ' (:p_assignment_id,:p_eff_date) '
|| 'FROM DUAL';
SELECT AEI_INFORMATION7
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
AND assignment_id = p_assignment_id;
l_sqlstr := 'select ' || l_column_name ||
' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
l_sqlstr := 'select ' || l_column_name ||
' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
' and '||p_effective_start_date||' between effective_start_date and effective_end_date' ;
SELECT definition
INTO l_package
FROM ff_functions
WHERE name = l_function_name
and business_group_id = p_business_group_id;
'SELECT '
|| l_package
|| ' (:p_assignment_id,:p_eff_date) '
|| 'FROM DUAL';
select assignment_number,
paaf.effective_start_date
from per_all_assignments_f paaf
where assignment_id = p_person_rec.assignment_id
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 <= l_eff_date
);
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis
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 = p_person_rec.assignment_id
AND link.element_type_id = g_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 iv.legislation_code = 'GB'
AND l_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
AND l_effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date
AND l_effective_date BETWEEN v.effective_start_date AND v.effective_end_date
AND l_effective_date between e.effective_start_date and e.effective_end_date;
select
pay_gb_eoy_archive.get_agg_active_start(p_person_rec.assignment_id,l_tax_ref,l_effective_date) start_date,
pay_gb_eoy_archive.get_agg_active_end(p_person_rec.assignment_id,l_tax_ref,l_effective_date) end_date
from dual;
SELECT AEI_INFORMATION3
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
AND assignment_id = p_person_rec.assignment_id;
select substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date,
business_group_id
from pay_payroll_actions pact,
pay_assignment_actions paa
where paa.assignment_action_id = p_assactid
and pact.payroll_action_id = paa.payroll_action_id;
select min(paaf.EFFECTIVE_START_DATE) , max(paaf1.EFFECTIVE_END_DATE) , max(paaf.assignment_id) l_prev_asg_id
from
per_all_people_f peo,
per_all_people_f peo1,
per_all_assignments_f paaf,
per_all_assignments_f paaf1,
per_assignment_status_types past,
per_assignment_status_types past1,
pay_all_payrolls_f papf ,
pay_all_payrolls_f papf1,
hr_soft_coding_keyflex flex
where
peo.person_id = p_person_id
and paaf.person_id = peo.person_id
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and papf.payroll_id = paaf.payroll_id
and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
and upper(p_tax_ref) = upper(flex.segment1)
and peo1.person_id = p_person_id
and paaf1.person_id = peo1.person_id
and paaf1.assignment_id = p_person_rec.assignment_id
and paaf1.assignment_status_type_id = past1.assignment_status_type_id
and past1.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paaf.assignment_type = 'E'
and paaf1.assignment_type = 'E'
and papf1.payroll_id = paaf1.payroll_id
and flex.SOFT_CODING_KEYFLEX_ID = papf1.SOFT_CODING_KEYFLEX_ID
and upper(p_tax_ref) = upper(flex.segment1)
and paaf.EFFECTIVE_END_DATE = paaf1.EFFECTIVE_START_DATE - 1
and paaf.assignment_id <> paaf1.assignment_id
and exists
(select '1' from per_all_assignments_f paafs,
per_assignment_status_types pasts
where paafs.assignment_id = paaf.assignment_id
and paafs.effective_start_date = paaf1.effective_start_date
and paafs.assignment_status_type_id = pasts.assignment_status_type_id
and pasts.per_system_status in ('TERM_ASSIGN')
) ;
select paaf.effective_start_date,paaf.effective_end_date
from
per_all_assignments_f paaf,
pay_all_payrolls_f papf ,
hr_soft_coding_keyflex flex,
per_assignment_status_types past
where
paaf.assignment_id = p_person_rec.assignment_id
and papf.payroll_id = paaf.payroll_id
and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
and upper(p_tax_ref) = upper(flex.segment1)
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paaf.effective_start_date =
(select max(paaf1.effective_start_date) from per_all_assignments_f paaf1,per_assignment_status_types past1
where paaf1.assignment_id = paaf.assignment_id
and paaf1.assignment_type = 'E'
and paaf1.assignment_status_type_id = past1.assignment_status_type_id
and past1.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paaf1.effective_start_date <= p_eff_date
)
and p_eff_date between papf.effective_start_date and papf.effective_end_date;
SELECT distinct element_type_id
into g_paye_details_id
FROM pay_element_types_f
WHERE element_name = 'PAYE Details';
INSERT INTO pay_message_lines(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
VALUES(
pay_message_lines_s.nextval
,100
,'F'
,p_assignment_action_id
,'A'
,substr(p_message_text,1,240)
);
INSERT INTO pay_message_lines(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
VALUES(
pay_message_lines_s.nextval
,200
,'W'
,p_assignment_action_id
,'A'
,substr(p_message_text,1,240)
);
SELECT ppa.effective_date
FROM pay_payroll_actions ppa, pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assactid;
SELECT DISTINCT employee_number into l_employee_number from per_all_people_f where EMPLOYEE_NUMBER IS NOT NULL AND person_id=p_tab_rec_data(0).person_id;
select ASSIGNMENT_EXTRA_INFO_ID, AEI_INFORMATION8,object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
hr_assignment_extra_info_api.update_assignment_extra_info
(p_validate => false,
p_object_version_number => l_object_version_number,
p_assignment_extra_info_id => l_asg_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information8 => 'Y'
);
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 'Y' from dual where exists(select *
from pay_payroll_actions prev
where prev.payroll_action_id <> pactid
and prev.business_group_id =bg_id
and prev.ACTION_STATUS='C'
and prev.REPORT_TYPE='RTI_EAS_REP_13'
and substr(pay_gb_eoy_archive.get_parameter(prev.legislative_parameters,'TAX_REF'),1,20) = substr(tax_ref,1,20)
and substr(pay_gb_eoy_archive.get_parameter(prev.legislative_parameters,'PERMIT'),1,12)=substr(permit,1,12)
and substr(pay_gb_eoy_archive.get_parameter(prev.legislative_parameters,'NO_OF_PARTS'),1,2)=
nvl('',substr(pay_gb_eoy_archive.get_parameter(prev.legislative_parameters,'NO_OF_PARTS'),1,2))
);
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
l_report_type varchar2(15);
select
fnd_number.number_to_canonical(pact.request_id) request_id,
to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date,
substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'NO_OF_PARTS'),1,2) no_of_parts,
substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'PERMIT'),1,12) unique_part_id,
report_type,
business_group_id
from pay_payroll_actions pact,
hr_organization_information hoi
where pact.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 pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF')
from pay_payroll_actions pact
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';
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';
sqlstr := 'select distinct PERSON_ID '||
'from per_all_assignments_f paaf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :payroll_action_id '||
'and ppa.business_group_id = paaf.business_group_id '||
' and paaf.PAYROLL_ID = ' ||''''||l_payroll_id||''''||
' order by person_id';
sqlstr := 'select distinct ppf.person_id '||
'from per_all_people_f ppf, '||
'per_all_assignments_f paaf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :payroll_action_id '||
' and paaf.person_id=ppf.person_id '||
'and ppa.business_group_id = ppf.business_group_id '||
' and ppf.EMPLOYEE_NUMBER = ' ||''''||l_employee_number||''''||
' and paaf.PAYROLL_ID = ' ||''''||l_payroll_id||''''||
'order by ppf.person_id';
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_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'NO_OF_PARTS'),1,2) no_of_parts,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PERMIT'),1,12) unique_part_id,
business_group_id
from pay_payroll_actions pact
where payroll_action_id = pactid;
select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,trim(pap.per_information10) per_agg_flag,
pap.person_id person_id,past.per_system_status status
from per_all_people_f pap,
per_all_assignments_f asg,
per_assignment_status_types past,
per_periods_of_service serv,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck,
hr_organization_information org
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.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN','TERM_ASSIGN')
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(nvl(l_tax_ref, sck.segment1)) =
upper(sck.segment1)
and org.organization_id = l_business_group_id
and org.org_information_context =
'Tax Details References'||decode(sck.segment1,'','','')
and org.org_information1 = sck.segment1
and nvl(org.org_information10,'UK') = 'UK'
and nvl(l_unique_part_id,substr(sck.segment10,1,12)) = substr(sck.segment10,1,12)
--and l_effective_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = asg.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= l_effective_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
order by person_id,assignment_id;
select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,
pap.person_id person_id, serv.actual_termination_date actual_termination_date
from per_all_people_f pap,
per_all_assignments_f asg,
per_periods_of_service serv,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck,
hr_organization_information org
where pap.person_id between stperson and endperson
and serv.actual_termination_date is not null
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 asg.assignment_type = 'E'
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(nvl(l_tax_ref, sck.segment1)) =
upper(sck.segment1)
and org.organization_id = l_business_group_id
and org.org_information_context =
'Tax Details References'||decode(sck.segment1,'','','')
and org.org_information1 = sck.segment1
and nvl(org.org_information10,'UK') = 'UK'
and nvl(l_unique_part_id,substr(sck.segment10,1,12)) = substr(sck.segment10,1,12)
and pap.effective_start_date =
( select max(pap2.effective_start_date) from
per_all_people_f pap2
where pap2.person_id = pap.person_id
and pap2.effective_start_date <= l_effective_date
)
and asg.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = asg.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= l_effective_date
)
and asg.effective_end_date >= l_tax_year_start_date
and l_effective_date between pay.effective_start_date and pay.effective_end_date
and serv.actual_termination_date >= l_tax_year_start_date
and serv.actual_termination_date < l_effective_date
order by person_id,assignment_id;
select per_information10 per_agg_flag from per_all_people_f
where PERSON_ID = p_person_id
and l_effective_date between effective_start_date and effective_end_date;
select paaf.assignment_id
from per_all_people_f peo,
per_all_assignments_f paaf,
per_all_assignments_f paaf1,
per_assignment_status_types past,
per_assignment_status_types past1,
pay_all_payrolls_f papf ,
hr_soft_coding_keyflex flex
where
peo.person_id = p_person_id
and paaf.person_id = peo.person_id
and paaf.assignment_id <> p_assignment_id
and paaf1.assignment_id = p_assignment_id
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paaf1.assignment_status_type_id = past1.assignment_status_type_id
and past1.per_system_status in ('TERM_ASSIGN')
and papf.payroll_id = paaf.payroll_id
and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
and upper(l_tax_ref) = upper(flex.segment1)
and paaf.effective_start_date = paaf1.effective_start_date;
select max(effective_end_date)
from per_all_assignments_f paaf ,
per_assignment_status_types past
where assignment_id = p_asg_id
--and paaf.assignment_status_type_id in ( 1,2)
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paaf.effective_start_date <= l_effective_date;
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 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 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL')) payroll_id,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EMPID'),1,20) emp_num,
effective_date,
business_group_id
from pay_payroll_actions
where payroll_action_id = pactid;
select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,trim(pap.per_information10) per_agg_flag,
pap.person_id person_id,asg.effective_start_date effective_start_date
from per_all_people_f pap,
per_all_assignments_f asg,
per_periods_of_service serv,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck,
per_people_extra_info ppei
where pap.person_id between stperson and endperson
and pap.person_id =ppei.person_id (+)
and nvl(ppei.pei_information_category,'RTI_NINO') = 'RTI_NINO'
and nvl(ppei.pei_information1,'No') = 'No'
and ppei.pei_information5 is null
--and pap.current_employee_flag = 'Y'
--and nvl(pap.current_employee_flag,'N') = decode(l_emp_num,null,'Y', nvl(pap.current_employee_flag,'N'))
and asg.primary_flag = 'Y'
and nvl(pap.current_employee_flag,'N') = decode(l_emp_num, null,decode(l_assignment_set,null,'Y',nvl(pap.current_employee_flag,'N')),nvl(pap.current_employee_flag,'N'))
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 not exists (select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = l_assignment_set
and hasa.assignment_id = asg.assignment_id
and hasa.include_or_exclude = 'E')
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
order by person_id,effective_start_date;
select nvl(include_or_exclude,'I')
from hr_assignment_set_amendments
where assignment_set_id = l_assignment_set;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select
nvl(UPPER(hoi.org_information11),' ') sender_id,
decode(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
fnd_number.number_to_canonical(pact.request_id) request_id,
to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date,
nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
lpad(nvl(substr(hoi.org_information1,1,3),' '),3,0) tax_office_no,
decode(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
nvl(upper(substr(hoi.org_information6,1,13)),' ') acc_ref_no,
substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'NO_OF_PARTS'),1,2) no_of_parts,
substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'PERMIT'),1,12) unique_part_id,
report_type,
business_group_id
from pay_payroll_actions pact,
hr_organization_information hoi
where pact.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 pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF')
from pay_payroll_actions pact
where payroll_action_id = pactid;
select report_type,paa.payroll_action_id,BUSINESS_GROUP_ID,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
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;
select min(paa.assignment_action_id)
from pay_assignment_actions paa
where
paa.payroll_action_id = p_pact_id
and paa.action_status = 'C'
and paa.assignment_id in(
select assignment_id
from per_all_assignments_f paaf
where paaf.person_id = p_person_id
and paaf.business_group_id = p_business_group_id
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 <= p_effective_date));
select distinct ppa.payroll_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f asg1,
per_all_assignments_f asg2
where asg1.assignment_id = p_assignment_id
and asg1.person_id = asg2.person_id
and asg1.assignment_id <> asg2.assignment_id
and asg1.business_group_id = p_business_group_id
and asg2.business_group_id = asg1.business_group_id
and paa.assignment_id = asg2.assignment_id
and paa.ACTION_STATUS='C'
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = asg1.business_group_id
and ppa.REPORT_TYPE='RTI_EAS_REP_13'
and substr(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'TAX_REF'),1,20) = substr(p_tax_ref,1,20)
and asg1.effective_start_date =
(select max(paaf1.effective_start_date)
from per_all_assignments_f paaf1 where
paaf1.assignment_id = asg1.assignment_id
and paaf1.assignment_type = 'E'
and paaf1.effective_start_date <= p_effective_date)
and asg2.effective_start_date =
(select max(paaf2.effective_start_date)
from per_all_assignments_f paaf2 where
paaf2.assignment_id = asg2.assignment_id
and paaf2.assignment_type = 'E'
and paaf2.effective_start_date <= p_effective_date);
select asg.assignment_id
from per_all_assignments_f asg,
pay_payrolls_f pay,
hr_soft_coding_keyflex sck
where asg.person_id = p_person_id
and asg.business_group_id = p_business_group_id
and pay.payroll_id=asg.payroll_id
and pay.SOFT_CODING_KEYFLEX_ID=sck.SOFT_CODING_KEYFLEX_ID
and sck.SEGMENT1=p_tax_ref
and p_effective_date between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE
and asg.effective_start_date =
(select max(paaf1.effective_start_date)
from per_all_assignments_f paaf1 where
paaf1.assignment_id = asg.assignment_id
and paaf1.assignment_type = 'E'
and paaf1.effective_start_date <= p_effective_date);
insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
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
nvl(UPPER(hoi.org_information11),' ') sender_id,
decode(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
fnd_number.number_to_canonical(pact.request_id) request_id,
nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'NO_OF_PARTS'),1,2) no_of_parts,
substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'PERMIT'),1,12) unique_part_id,
lpad(substr(hoi.ORG_INFORMATION1,0,instr(hoi.ORG_INFORMATION1,'/')-1),3,0) tax_office_no,
decode(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
(lpad(substr(hoi.ORG_INFORMATION6,0,instr(hoi.ORG_INFORMATION6,'P')-1),3,0)
|| 'P'
|| substr(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P')+1,1)
|| lpad(substr(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P')+2,length(hoi.ORG_INFORMATION6)-3-(instr(hoi.ORG_INFORMATION6,'P')-1)),7,0)
|| substr(hoi.ORG_INFORMATION6,length(hoi.ORG_INFORMATION6),1)
)
as acc_ref_no,
pact.business_group_id bus_grp_id,
pact.action_parameter_group_id act_param_grp_id
from pay_payroll_actions pact,
hr_organization_information hoi
where pact.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 legislative_parameters para,
fnd_number.number_to_canonical(request_id) control_id,
report_type,
business_group_id
from pay_payroll_actions pact
where payroll_action_id = pactid;
select to_char(to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD'),'DD-MON-RRRR') effective_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
to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date
from pay_payroll_actions pact,
hr_organization_information hoi
where pact.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 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 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 = 'E'
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 effective_date
from pay_payroll_actions
where payroll_action_id = pactid;
select pai_emp.action_information3 l_name,
pai_emp.action_information4 f_name,
pai_emp.action_information5 m_name,
pai_emp.action_information6 title,
pai_emp.action_information17 emp_no,
nvl(pai_emp.action_information7,' ')ni_no,
pai_emp.action_information17 emp_no1,
to_char(to_date(substr(pai_asg.action_information5,1,10),'YYYY-MM-DD'),'DD-MON-RRRR') start_date,
to_char(to_date(substr(pai_asg.action_information6,1,10),'YYYY-MM-DD'),'DD-MON-RRRR') end_date,
pai_emp.action_information17 emp_no2
from pay_action_information pai_emp,
pay_action_information pai_asg,
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 RTI EMPLOYEE DETAILS'
and pai_emp.action_context_type = 'AAP'
and pai_asg.action_context_id = paa.assignment_action_id
and pai_asg.action_information_category = 'GB RTI ASG DETAILS'
and pai_asg.action_context_type = 'AAP';
select distinct pap.first_name f_name ,
pap.middle_names m_name,
pap.last_name l_name,
pap.title title,
paa.assignment_number emp_no,
nvl(pap.national_identifier,' ')ni_no,
nvl(pap.employee_number,' ') employee_number,
to_char(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
pap.person_id
from per_all_assignments_f paa,
per_assignment_status_types past,
per_all_people_f pap
,pay_payroll_actions ppa
where paa.person_id = pap.person_id
and paa.assignment_id = c_assignment_id
and past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
-- and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') -- Added for Bug#13626488
AND ppa.payroll_action_id = pactid
AND p_eff_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND paa.assignment_type = 'E'
AND paa.effective_start_date =
(
SELECT MAX(paa2.effective_start_date)
FROM per_all_assignments_f paa2
WHERE paa2.assignment_id = paa.assignment_id
AND paa2.effective_start_date <= p_eff_date
)
order by end_date desc;
select distinct pap.first_name f_name ,
pap.middle_names m_name,
pap.last_name l_name,
pap.title title,
paa.assignment_number emp_no,
nvl(substr(pap.national_identifier,1,9),' ')ni_no,
nvl(pap.employee_number,' ') employee_number,
to_char(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
pap.person_id
from per_all_assignments_f paa,
per_assignment_status_types past,
per_all_people_f pap
where paa.person_id = pap.person_id
and paa.assignment_id = c_assignment_id
and past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and l_nino_eff_date between pap.EFFECTIVE_START_DATE and pap.EFFECTIVE_END_DATE
order by end_date desc;
select distinct line_text from pay_message_lines where source_id = asg_action_id and payroll_id = 100;
select distinct line_text from pay_message_lines where source_id = asg_action_id and payroll_id = 200;
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 ('RTI PAYROLL INFO');
select pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST'),
NVL(fnd_date.canonical_to_date(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'START')),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;
select paaf.effective_start_date from
per_all_assignments_f paaf,
pay_all_payrolls_f papf ,
hr_soft_coding_keyflex flex,
per_assignment_status_types past
where paaf.assignment_id = asg_id and paaf.effective_end_date = eff_date - 1
and papf.payroll_id = paaf.payroll_id
and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
and upper(l_tax_ref) = upper(flex.segment1)
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
select paaf.effective_end_date
from
per_all_assignments_f paaf,
pay_all_payrolls_f papf ,
hr_soft_coding_keyflex flex,
per_assignment_status_types past
where paaf.assignment_id = asg_id and paaf.effective_start_date = eff_date + 1
and papf.payroll_id = paaf.payroll_id
and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
and upper(l_tax_ref) = upper(flex.segment1)
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');