[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
26-MAR-2007 K.Thampan 115.87 5953974 Amended update_record procedure
23-APR-2007 K.Thampan 115.88 5962025 Performance fix.
30-NOV-2007 Dinesh C. 115.94 6450573 Change for SOY 08-09.
04-MAR-2008 Rajesh L. 115.96 6741064 Modifed cursors csr_mode12,csr_mode34
as orderby fullname
26-MAR-2008 Rajesh L. 115.97 6864422 Reverted the fix 6741064 as it was reported
by one cusotomer.
Modified p_m34_rec.full_name to substr
of peo.last_name length
09-APR-2008 Rajesh.L 115.98 6957644 Modifed cursors csr_mode12,csr_mode34
as orderby fullname
08-SEP-2008 emunisek 115.99 7373763 Modified procedure process_record to prevent
the false entries in the report of SOY changes
and to prevent date tracks created for unmodified
tax codes
3-Oct-2008 apmishra 115.101 7373763 Re arcs in the file so as to enable the dual maintainence
The earlier version of the branch file did not contain the fix,
hence rearcs in.
------------------------------------------------------------------------*/
-----------------------------------------------------
-- Constant variable --
-----------------------------------------------------
err_emp_not_found constant varchar2(255) := 'Emp Data in EDI file does not match application data, or employee is terminated.';
err_mode34_ex_emp constant varchar2(255) := 'No update for Ex-Employee, manual update may be required.';
err_mode2_ex_emp constant varchar2(255) := 'No bulk uplift for Ex-Employee, manual update may be required.';
err_multi_fchanges constant varchar2(255) := 'Multiple future changes detected, manual update may be required.';
err_multi_asg constant varchar2(255) := 'Record not updated as assignment number is not supplied and multiple assignments exist, manual update may be required.';
err_future_asg constant varchar2(255) := 'Record not updated as future assignment exists under the same tax district, manual update may be required.';
warning_msg constant varchar2(255) := 'Tax details updated for assignment with future termination details present.'; /*Added soy 08-09*/
update_mode constant varchar2(20) := 'UPDATE';
dt_update_mode varchar2(25),
creator_id number);
g_update_count number;
select element_type_id
from pay_element_types_f
where element_name = 'PAYE Details';
select /*+ ORDERED
INDEX(ppt, PER_PERSON_TYPES_PK) */
max(peo.person_id) p_id,
peo.full_name,
peo.national_identifier,
nvl(peo.per_information10,'N') agg_flag,
nvl(peo.per_information2, 'N') dir_flag,
ppt.system_person_type,
pps.actual_termination_date,
pps.last_standard_process_date,
g_effective_date,
peo.effective_start_date,
peo.effective_end_date
from per_all_people_f peo,
per_all_assignments_f asg,
per_periods_of_service pps,
per_person_type_usages_f ptu,
per_person_types ppt
where peo.person_id = asg.person_id
and peo.business_group_id = g_business_group_id
and (g_payroll_id is null or
asg.payroll_id = g_payroll_id)
and peo.person_id = ptu.person_id
and ptu.person_type_id = ppt.person_type_id
and pps.period_of_service_id = asg.period_of_service_id
and asg.payroll_id is not null
and g_effective_date between peo.effective_start_date and peo.effective_end_date
and g_effective_date between asg.effective_start_date and asg.effective_end_date
and g_effective_date between ptu.effective_start_date and ptu.effective_end_date
and ( (pps.final_process_date is not null and
pps.final_process_date > g_effective_date)
or
(pps.final_process_date is null))
group by peo.full_name, peo.national_identifier, peo.per_information10, peo.per_information2,
ppt.system_person_type, pps.actual_termination_date, pps.last_standard_process_date,
g_effective_date, peo.effective_start_date, peo.effective_end_date
-- Bug 6957644 modified p_id to upper(peo.full_name) to report order by name
--Bug 6741064 modified p_id to upper(peo.full_name) to report order by name
order by upper(peo.full_name), decode(system_person_type,'EMP',1,'EMP_APL',2,3);
select tci.employee_name full_name,
tci.national_insurance_number national_identifier,
tci.works_number assignment_number,
tci.employer_reference payroll_name,
nvl(tci.effective_date,g_effective_date) effective_date,
tci.date_of_message date_of_message,
tci.issue_date issue_date,
ltrim(rtrim(tci.tax_code)) tax_code_sv,
ltrim(rtrim(tci.non_cumulative_flag)) tax_basis_sv,
to_char(nvl((tci.tot_pay_prev_emp/100),'')) pay_previous_sv,
to_char(nvl((tci.tot_tax_prev_emp/100),'')) tax_previous_sv,
upper(nvl(tci.form_type,'')) authority_sv,
ltrim(rtrim(to_char(tci.district_number,'000'))) || '/' || ltrim(rtrim(tci.employer_reference)) paye_ref,
tci.rowid row_id
from pay_gb_tax_code_interface tci
where tci.processed_flag is null
and ( tci.request_id is null
or tci.request_id = g_p6_request_id)
-- Bug 6957644 modified 6,2 to date_of_message,upper(full_name) to report order by name
--Bug 6741064 modified 6,2 to date_of_message,upper(full_name) to report order by name
order by date_of_message,upper(full_name);
select assignment_id,
pay.payroll_id,
assignment_number,
per_system_status asg_status,
sck.segment1 tax_ref
from per_all_assignments_f asg,
pay_all_payrolls_f pay,
per_assignment_status_types pat,
hr_soft_coding_keyflex sck
where asg.person_id = p_person_id
and ( -- no need to fetch payroll, but do check the tax ref
(p_aggr_flag = 'Y' and
((g_payroll_id is not null and sck.segment1 = g_tax_ref)
or
(g_payroll_id is null and p_tax_ref is not null and sck.segment1 = p_tax_ref)
or
(g_payroll_id is null and p_tax_ref is null)))
or -- not aggregate then we have to check the payroll is matched.
(p_aggr_flag = 'N' and
((g_payroll_id is null and p_asg_no is null and (p_tax_ref is null or p_tax_ref = sck.segment1) )
or
(g_payroll_id is null and p_asg_no is not null and asg.assignment_number = p_asg_no and (p_tax_ref is null or p_tax_ref = sck.segment1))
or
(g_payroll_id is not null and asg.payroll_id = g_payroll_id and (p_tax_ref is null or p_tax_ref = sck.segment1) )))
)
and asg.payroll_id = pay.payroll_id
and sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
and asg.assignment_status_type_id = pat.assignment_status_type_id
and p_date between asg.effective_start_date and asg.effective_end_date
and p_date between pay.effective_start_date and pay.effective_end_date
order by assignment_id, decode(per_system_status,'ACTIVE_ASSIGN',1,'SUSP_ASSIGN',2,'ACTIVE_APL',3,4), sck.segment1;
select 1
from dual
where exists (select fcr.request_id
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.request_id = c_creator_id
and fcr.request_id < g_current_req_id
and fcr.request_id >= g_request_id
and ( fcp.concurrent_program_name = 'PYUDET'
or fcp.concurrent_program_name = 'PYUDET_R'));
select sck.segment1, payroll_name
from hr_soft_coding_keyflex sck,
pay_all_payrolls_f pay
where sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
and pay.payroll_id = g_payroll_id
and g_effective_date between pay.effective_start_date and pay.effective_end_date;
select assignment_id
from per_all_assignments_f asg,
pay_all_payrolls_f pay,
per_assignment_status_types pat,
hr_soft_coding_keyflex sck
where asg.person_id = p_person_id
and sck.segment1 = p_paye_ref
and asg.payroll_id = pay.payroll_id
and sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
and asg.assignment_status_type_id = pat.assignment_status_type_id
and p_date between asg.effective_start_date and asg.effective_end_date
and p_date between pay.effective_start_date and pay.effective_end_date
AND per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') ;
g_update_count := 0;
hr_utility.fnd_insert(g_effective_date);
select decode(max(decode(type,'E',line_no,0)),
null, 0,
max(decode(type,'E',line_no,0))),
decode(max(decode(type,'P',line_no,0)),
null, 0,
max(decode(type,'P',line_no,0))),
decode(max(decode(type,'T',line_no,0)),
null, 0,
max(decode(type,'T',line_no,0)))
into g_E_line_count,
g_P_line_count,
g_T_line_count
from pay_gb_soy_outputs
where request_id = g_request_id;
select effective_start_date
from PER_ALL_ASSIGNMENTS_F paaf,
per_assignment_status_types past
where paaf.effective_start_date >= p_effective_date
and paaf.assignment_id = p_assignment_id
and paaf.assignment_status_type_id =past.assignment_status_type_id
and past.per_system_status IN ('TERM_ASSIGN')
and paaf.business_group_id = g_business_group_id;
select max(effective_end_date)
from PER_ALL_ASSIGNMENTS_F paaf
where paaf.effective_end_date >= p_effective_date
and paaf.assignment_id = p_assignment_id
and paaf.business_group_id = g_business_group_id;
if (mod(g_update_count, l_commit_point) = 0) then
return(TRUE);
PROCEDURE conv_to_table_ni(p_process_type_updated in varchar2 ,
p_input_value_id in number ,
p_num_entry_values in out nocopy number,
p_input_value_id_tbl in out nocopy hr_entry.number_table,
p_entry_value_tbl in out nocopy hr_entry.varchar2_table)
IS
l_index number := 0;
p_entry_value_tbl(l_index) := p_process_type_updated;
insert into pay_gb_soy_outputs(request_id, type, line_no, text)
values(l_request_id, p_type, p_line_no, p_text);
select count(*)
from pay_gb_tax_code_interface
where request_id = g_p6_request_id;
l_validate_mode :=rpad('Validate Mode',21) || 'Validate Only - Updates Not Applied To The Database';
l_line2 :='Assignment Old New Code Basis Gross/Tax Update/ Director Update ';
l_mode := initcap(lower(p_new_paye_rec.dt_update_mode));
update pay_gb_tax_code_interface
set processed_flag = 'R'
where rowid = p_m34_rec.row_id;
select count(*)
from pay_gb_tax_code_interface
where request_id = g_p6_request_id
and processed_flag = 'P';
file_output('P', g_P_line_count, 'Records Updated (inc Multi Assignment) = '|| to_char(g_update_count), g_request_id);
select lku.meaning
from hr_lookups lku
where lku.lookup_type = p_lookup_type
and lku.lookup_code = p_lookup_code;
select formula_id,
effective_start_date
into l_formula_id,
l_effective_start_date
from ff_formulas_f
where formula_name='TAX_CODE'
and business_group_id is null
and legislation_code='GB'
and p_effective_date between effective_start_date and effective_end_date;
select usr.row_low_range_or_name suffix,
fnd_number.canonical_to_number(uci.value) value
from pay_user_tables ust,
pay_user_columns usc,
pay_user_rows_f usr,
pay_user_column_instances_f uci
where ust.user_table_name = 'TAX_CODE_UPLIFT_VALUES'
and ust.user_table_id = usc.user_table_id
and ust.user_table_id = usr.user_table_id
and usc.user_column_name = 'UPLIFT_VALUE'
and usc.user_column_id = uci.user_column_id
and usr.user_row_id = uci.user_row_id
and uci.business_group_id = g_business_group_id
and usr.business_group_id = g_business_group_id
and g_effective_date between usr.effective_start_date and usr.effective_end_date
and g_effective_date = uci.effective_start_date;
select ele.effective_start_date
into l_future_date
from pay_element_entries_f ele
where ele.effective_start_date >= p_effective_date
and ele.assignment_id = p_assignment_id
and ele.element_type_id = g_element_type_id
order by ele.effective_start_date asc;
l_process_type_updated boolean;
select element_entry_id,
process_type,
input_value_id5
from PAY_NI_ELEMENT_ENTRIES_V pneev
where pneev.assignment_id = p_assignment_id
and g_effective_date between pneev.effective_start_date and pneev.effective_end_date;
l_process_type_updated := FALSE;
l_process_type_updated := TRUE;
l_process_type_updated := TRUE;
if l_process_type_updated then
conv_to_table_ni(l_process_type_new,
l_input_value_id,
l_num_entry_values,
l_input_value_id_tbl,
l_entry_value_tbl) ;
hr_entry_api.update_element_entry (
p_dt_update_mode => 'UPDATE',
p_session_date => g_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => l_num_entry_values,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_entry_value_tbl );
return (l_process_type || ' ' || l_process_type_new_code || ' Update');
PROCEDURE update_p45_taxbasis(p_asg_typ in varchar2,
p_paye_rec in out nocopy g_typ_paye_record,
p_aggregate_flag varchar2)
IS
BEGIN
if p_aggregate_flag ='Y' then
if p_paye_rec.tax_code_sv not in ('D0','SD0') then /*Added terminated for soy 08-09*/
if p_asg_typ in ('ACTIVE_ASSIGN','ACTIVE_APL','SUSP_ASSIGN','TERM_ASSIGN') and g_cpe_flag ='Y' then
if p_paye_rec.tax_basis_sv = 'Non Cumulative' then
p_paye_rec.tax_basis_sv := 'Cumulative';
hr_utility.trace(' Tax Basis update');
hr_utility.trace(' Tax Basis update');
hr_utility.trace(' P45 Update');
END update_p45_taxbasis;
update_p45_taxbasis(p_asg_typ,p_paye_rec, p_aggregate_flag);
p_paye_rec.dt_update_mode := correct_mode;
p_paye_rec.dt_update_mode := correct_mode;
p_paye_rec.dt_update_mode := update_mode;
if g_mode in (1,2) and p_paye_rec.dt_update_mode = update_mode and g_cpe_flag ='Y'then
uplift_taxcode(p_asg_typ, p_paye_rec,p_aggregate_flag);
select ee.element_entry_id ,
ee.effective_start_date ,
ee.effective_end_date ,
min(decode(inv.name, 'Tax Code', eev.input_value_id, null)) tax_code_id ,
min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) tax_code_sv ,
min(decode(inv.name, 'Tax Basis', eev.input_value_id, null)) tax_basis_id ,
min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) tax_basis_sv ,
min(decode(inv.name, 'Pay Previous', eev.input_value_id, null)) pay_previous_id ,
min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)) pay_previous_sv ,
min(decode(inv.name, 'Tax Previous', eev.input_value_id, null)) tax_previous_id ,
min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)) tax_previous_sv ,
min(decode(inv.name, 'Authority', eev.input_value_id, null)) authority_id ,
min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) authority_sv ,
min(decode(inv.name, 'Refundable', eev.input_value_id, null)) refundable_id ,
min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) refundable_sv,
ee.creator_id
from pay_element_entries_f ee,
pay_element_entry_values_f eev,
pay_input_values_f inv
where ee.assignment_id = p_assignment_id
and ee.element_type_id = g_element_type_id
and ee.element_entry_id = eev.element_entry_id
and eev.input_value_id = inv.input_value_id
and p_date between ee.effective_start_date and ee.effective_end_date
and p_date between eev.effective_start_date and eev.effective_end_date
and p_date between inv.effective_start_date and inv.effective_end_date
group by ee.element_entry_id, ee.effective_start_date, ee.effective_end_date,
ee.creator_id;
select max(peo.person_id) p_id,
peo.last_name,
peo.full_name,
peo.national_identifier,
nvl(peo.per_information10,'N') agg_flag,
'EMP', --ppt.system_person_type,
pps.actual_termination_date,
pps.last_standard_process_date,
p_m34_rec.effective_date
from per_all_people_f peo,
per_all_assignments_f asg,
per_periods_of_service pps
where asg.business_group_id = g_business_group_id
and peo.business_group_id = g_business_group_id
and peo.person_id = asg.person_id
-- Bug 6864422 modified p_m34_rec.full_name to substr of peo.last_name length
--and upper(peo.last_name) like upper(substr(rpad(p_m34_rec.full_name,5,'%'), 1, 5))||'%'
and upper(peo.last_name) like upper(substr(rpad(substr(p_m34_rec.full_name,1,length(peo.last_name)),5,'%'), 1, 5))||'%'
--and upper(substr(rpad(peo.last_name,5,' '),1,5)) = upper(substr(rpad(p_m34_rec.full_name,5,' '), 1, 5))
and (p_m34_rec.assignment_number is null or
asg.assignment_number = p_m34_rec.assignment_number)
and (p_m34_rec.national_identifier is null or
peo.national_identifier like substr(p_m34_rec.national_identifier,1,8)||'%')
and pps.period_of_service_id = asg.period_of_service_id
and p_m34_rec.effective_date between asg.effective_start_date and asg.effective_end_date
and p_m34_rec.effective_date between peo.effective_start_date and peo.effective_end_date
and (pps.actual_termination_date is null or
p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
and exists (select 1
from per_person_type_usages_f ptu,
per_person_types ppt
where ptu.person_id = peo.person_id
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP','EMP_APL')
and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)
group by peo.last_name, peo.full_name, peo.national_identifier, peo.per_information10,
'EMP' /*ppt.system_person_type*/, pps.actual_termination_date, pps.last_standard_process_date
order by p_id;
select count(*)
from per_all_assignments_f asg,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where asg.person_id = p_person_id
and asg.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and sck.segment1 = p_tax_district
and (p_asg_no is null or
asg.assignment_number = p_asg_no
)
and p_date between asg.effective_start_date and asg.effective_end_date
and p_date between pay.effective_start_date and pay.effective_end_date;
select count(*)
from per_all_people_f peo,
per_all_assignments_f asg,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where peo.person_id = p_person_id
and asg.person_id = peo.person_id
and asg.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and sck.segment1 = p_tax_district
and nvl(peo.per_information10,'N') = 'Y'
and asg.effective_start_date > p_date
and p_date between pay.effective_start_date and pay.effective_end_date
and asg.effective_start_date between peo.effective_start_date and peo.effective_end_date
and asg.assignment_id not in (select assignment_id
from per_all_assignments_f asg,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where asg.person_id = p_person_id
and asg.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and sck.segment1 = p_tax_district
and p_date between asg.effective_start_date and asg.effective_end_date
and p_date between pay.effective_start_date and pay.effective_end_date);
FUNCTION update_record(p_paye_rec IN g_typ_paye_record,
p_per_rec IN g_typ_per_record,
p_m34_rec IN g_tax_code_interface default null) return VARCHAR2
IS
l_issue_date date;
select rowid from pay_gb_tax_code_interface
where rowid = c_row_id
for update;
hr_entry_api.update_element_entry(
p_dt_update_mode => rtrim(p_paye_rec.dt_update_mode),
p_session_date => p_per_rec.effective_date,
p_element_entry_id => p_paye_rec.element_entry_id,
p_num_entry_values => l_num_entry_values,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_entry_value_tbl,
p_entry_information_category => 'GB_PAYE',
p_entry_information1 => fnd_date.date_to_canonical(l_issue_date),
p_entry_information2 => fnd_date.date_to_canonical(l_message_date));
update pay_gb_tax_code_interface
set processed_flag = 'P'
where current of c1;
update pay_element_entries_f pef
set pef.creator_id = nvl(g_request_id,g_current_req_id)
where pef.element_entry_id = p_paye_rec.element_entry_id
and pef.effective_start_date = p_per_rec.effective_date;
g_update_count := g_update_count + 1;
END update_record;
hr_utility.trace(' Check if we need to do any update');
hr_utility.trace(' Calling update_record to update PAYE');
l_msg := update_record(l_paye_rec, l_per_record, l_m34_rec);
hr_utility.trace(' Check if we need to do any update');
hr_utility.trace(' Calling update_record to update PAYE');
l_msg := update_record(l_paye_rec, l_per_record, l_m34_rec);
l_msg := update_record(l_paye_rec, l_per_record, l_m34_rec);
delete pay_gb_tax_code_interface
where (request_id is null or request_id = g_p6_request_id)
and processed_flag = 'P'; /*Added soy 08-09*/