[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
115.14 02/02/10 rlingama Bug 9278271 - Replaced the select query with cursor cur_get_paye_agg to avoid
technical errors.
115.15 05/07/11 pbalu Bug 12662579 - PAYE reference change - Validations made more stringent.
115.16 21/07/11 pbalu Bug 12662579 - PAYE reference change - Validations made more stringent.
115.17 27/03/13 sampmand Bug 16489664 - Multiple PAYE Ref change validation switched off for DWP Retro-process.
*/
/* Calculate free pay for given amount */
function free_pay
( p_amount IN NUMBER,
p_tax_code IN VARCHAR2,
p_tax_basis IN VARCHAR2,
p_stat_annual_periods IN NUMBER,
p_current_period IN NUMBER)
return NUMBER
as
l_tax_code VARCHAR2(8);
select pur.row_low_range_or_name,
pur.row_high_range,
puci.value
from pay_user_tables put,
pay_user_rows_f pur,
pay_user_columns puc,
pay_user_column_instances_f puci
where put.user_table_id = puc.user_table_id
and pur.user_table_id = puc.user_table_id
and puci.user_row_id = pur.user_row_id
and puci.user_column_id = puc.user_column_id
and upper(puc.user_column_name) = upper('paye_percentage')
and put.legislation_code = 'GB'
and upper(put.user_table_name) = upper('PAYE')
and p_session_date between puci.effective_start_date and puci.effective_end_date
and p_session_date between pur.effective_start_date and pur.effective_end_date
order by pur.row_low_range_or_name;
select pur.row_low_range_or_name,
pur.row_high_range,
puci.value
from pay_user_tables put,
pay_user_rows_f pur,
pay_user_columns puc,
pay_user_column_instances_f puci
where put.user_table_id = puc.user_table_id
and pur.user_table_id = puc.user_table_id
and puci.user_row_id = pur.user_row_id
and puci.user_column_id = puc.user_column_id
and upper(puc.user_column_name) = upper('paye_percentage_svr')
and put.legislation_code = 'GB'
and upper(put.user_table_name) = upper('PAYE')
and p_session_date between puci.effective_start_date and puci.effective_end_date
and p_session_date between pur.effective_start_date and pur.effective_end_date
order by pur.row_low_range_or_name;
select request_id from fnd_concurrent_requests
where request_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT 1
INTO l_retro_status
FROM PAY_PAYROLL_ACTIONS
WHERE request_id = l_request_id
AND ACTION_TYPE IN ('L','G','O');
select min(effective_start_date) effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
;
select ppa.payroll_id old_payroll_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.payroll_action_id <> p_payroll_action_id
and ppa.action_type in ('Q', 'R', 'B', 'I' , 'V')
and ppa.effective_date >= l_date_soy
and ppa.effective_date <= l_date_eoy ;
select count(*) l_exist -- if this cursor fetches '1', that means new payroll is valid.
from pay_all_payrolls_f pap
,hr_soft_coding_keyflex scl
where pap.payroll_id = p_new_payroll_id
and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment1 in
(
select distinct scl.segment1
from pay_all_payrolls_f pap
,hr_soft_coding_keyflex scl
where
pap.payroll_id = p_old_payroll_id
and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and pap.effective_start_date <= l_effective_date
and pap.effective_end_date >= l_effective_date
)
;
select count(*) l_exist -- if this cursor fetches '1', that means new payroll is valid.
from pay_all_payrolls_f pap
,hr_soft_coding_keyflex scl
where pap.payroll_id = p_new_payroll_id
and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment1 = p_old_tax_reference
and l_effective_date between pap.effective_start_date and pap.effective_end_date;
select count (distinct nvl(per_information10,'N') ) l_count
from per_all_people_f papf , per_all_assignments_f paaf
where paaf.assignment_id=p_assignment_id
and papf.person_id=paaf.person_id
and papf.effective_start_date > l_date_soy
and papf.effective_start_date < l_date_eoy ;
select hsck.segment1
from pay_all_payrolls_f papf,
hr_soft_coding_keyflex hsck
where
papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
and papf.payroll_id=c_payroll_id;
select count(*)
from
(
select
distinct
ppev.INPUT_VALUE_ID1,
ppev.TAX_CODE,
ppev.INPUT_VALUE_ID2,
ppev.D_TAX_BASIS,
ppev.INPUT_VALUE_ID4,
ppev.D_PAY_PREVIOUS,
ppev.INPUT_VALUE_ID5,
ppev.D_TAX_PREVIOUS,
ppev.INPUT_VALUE_ID3,
ppev.D_REFUNDABLE,
ppev.INPUT_VALUE_ID6,
ppev.D_AUTHORITY
--ppev.entry_information1,
--ppev.entry_information2
from
(
SELECT ele.rowid ROW_ID, ele.element_entry_id, min(decode(inv.name, 'Tax Code', eev.input_value_id, null)) INPUT_VALUE_ID1,
min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) Tax_Code,
min(decode(inv.name, 'Tax Basis', eev.input_value_id, null)) INPUT_VALUE_ID2,
min(decode(inv.name, 'Tax Basis', substr(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',eev.screen_entry_value),1,80),null)) D_Tax_Basis,
min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) Tax_Basis, min(decode(inv.name, 'Refundable',
eev.input_value_id, null)) INPUT_VALUE_ID3,
min(decode(inv.name, 'Refundable', substr(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',eev.screen_entry_value),1,80),null)) D_Refundable,
--min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) Refundable,
min(decode(inv.name, 'Pay Previous', eev.input_value_id, null)) INPUT_VALUE_ID4,
hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Pay_Previous,
--min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)) Pay_Previous,
min(decode(inv.name, 'Tax Previous', eev.input_value_id, null)) INPUT_VALUE_ID5,
hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Tax_Previous,
--min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)) Tax_Previous,
min(decode(inv.name, 'Authority', eev.input_value_id, null)) INPUT_VALUE_ID6,
min(decode(inv.name, 'Authority', substr(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',eev.screen_entry_value),1,80),null)) D_AUTHORITY,
--min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) Authority,
ele.assignment_id,
ele.effective_start_date,
ele.effective_end_date,
ele.entry_information_category,
ele.entry_information1,
ele.entry_information2
from
pay_element_entries_f ele,
pay_element_entry_values_f eev,
pay_input_values_f inv,
pay_element_links_f lnk,
pay_element_types_f elt,
pay_all_payrolls_f papf,
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
where ele.element_entry_id = eev.element_entry_id
AND c_pay_run_date between ele.effective_start_date and ele.effective_end_date
AND eev.input_value_id + 0 = inv.input_value_id
AND c_pay_run_date between eev.effective_start_date and eev.effective_end_date
AND inv.element_type_id = elt.element_type_id
AND c_pay_run_date between inv.effective_start_date and inv.effective_end_date
AND ele.element_link_id = lnk.element_link_id
AND elt.element_type_id = lnk.element_type_id
AND c_pay_run_date between lnk.effective_start_date and lnk.effective_end_date
AND elt.element_name = 'PAYE Details'
AND c_pay_run_date between elt.effective_start_date and elt.effective_end_date
AND c_pay_run_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND c_pay_run_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND ele.assignment_id=paaf.assignment_id
AND papf.payroll_id=paaf.payroll_id
AND papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND hsck.segment1=c_tax_reference
AND paaf.person_id = (select distinct pap.person_id
from per_all_people_f pap,
per_all_assignments_f paaf1
where paaf1.person_id=pap.person_id
and paaf1.assignment_id=c_assignment_id)
AND pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, c_tax_reference,c_pay_run_date)
= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_reference,c_pay_run_date)
AND pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_tax_reference,c_pay_run_date)
= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_reference,c_pay_run_date)
-- Bug 7601088 Ignore the P45 issued assignments while checking the aggregated assignment PAYE details with in CPE.
AND pay_p45_pkg.paye_return_p45_issued_flag(c_assignment_id,p_payroll_action_id) = 'N'
AND pay_p45_pkg.paye_return_p45_issued_flag(paaf.assignment_id,p_payroll_action_id) = 'N'
/*Bug 7389532 - Added the distinct and null condition */
AND paaf.period_of_service_id = (select distinct period_of_service_id
from per_all_assignments_f paaf2
where paaf2.assignment_id=c_assignment_id
and paaf.person_id =paaf2.person_id
and period_of_service_id is not null)
group by ele.rowid, ele.element_entry_id, ele.assignment_id, ele.entry_information_category,
ele.entry_information1, ele.entry_information2, ele.effective_start_date, ele.effective_end_date
) ppev
);
select nvl(PER_INFORMATION10,'N')
from per_all_people_f
where person_id = (select distinct papf.person_id
from per_all_people_f papf, per_all_assignments_f paaf1
where papf.person_id=paaf1.person_id
and paaf1.assignment_id= p_assignment_id)
and p_pay_run_date between effective_start_date and effective_end_date;
select regular_payment_date into l_pay_run_date
from per_time_periods ptp ,
pay_payroll_actions ppa
where ptp.time_period_id=ppa.time_period_id
and ppa.payroll_action_id=p_payroll_action_id;
/* select nvl(PER_INFORMATION10,'N') into l_aggregated_asg
from per_all_people_f
where person_id = (select distinct papf.person_id
from per_all_people_f papf, per_all_assignments_f paaf1
where papf.person_id=paaf1.person_id
AND paaf1.assignment_id= p_assignment_id
)
and l_pay_run_date between effective_start_date and effective_end_date;*/
select effective_date into l_effective_date
from pay_payroll_actions ppa
where ppa.payroll_action_id=p_payroll_action_id;
select distinct scl.segment1 into l_old_tax_reference
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_all_payrolls_f pap,
hr_soft_coding_keyflex scl
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.payroll_action_id <> p_payroll_action_id
and ppa.action_type in ('Q', 'R', 'B', 'I' , 'V')
and ppa.effective_date >= l_date_soy
and ppa.effective_date <= l_date_eoy
and pap.payroll_id = ppa.payroll_id
and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and ppa.effective_date between pap.effective_start_date and pap.effective_end_date;
select 1 into l_record
from PAY_PATCH_STATUS
where PATCH_NAME = 'DWP_RETRO_VAL_SKIP';
select PEOPLE.DATE_OF_DEATH
from per_all_assignments_f ASSIGN
,per_all_people_f PEOPLE
,fnd_sessions SES
where SES.effective_date BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and SES.effective_date BETWEEN PEOPLE.effective_start_date
AND PEOPLE.effective_end_date
and ASSIGN.assignment_id = p_assignmnet_id
and PEOPLE.person_id = ASSIGN.person_id
and PEOPLE.per_information4 ='Y'
and PEOPLE.DATE_OF_DEATH is not null
and SES.session_id = USERENV('sessionid') ;