[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.
11-Nov_2008 dchindar 115.102 7532289 Changes has been done in cursor csr_mode12, so that process will
now update all eligible Tax Code For a employee having more than one-person
record with same NI number and other person details.
23-DEC-2008 dwkrishn 115.105 7649174 Modified the cursors/conditions for 3 cases
csr_fetch_asg_asgno Pass assignment number , Ni always Null
csr_fetch_asg_natid -- pass Ni ,Assignment always Null
csr_fetch_asg_other -- Pass Both
Both Null Errors
11-MAY-2009 jvaradra 115.106 8485686 Variable pqp_gb_ad_ee.g_global_paye_validation is
intialized to 'N' before calling hr_entry_api
and reset to 'Y' at the end to ensure row handler validations
are not fired when p6/p9/SOY process are submitted.
21-MAY-2009 rlingama 115.107 8497477 Added Employer's PAYE Reference on the output.
Earlier the report was sorted by person full name.
Now the report would sort by person name with in the individual tax references.
05-OCT-2009 dwkrishn 115.108 8785270 PAYE changes 2009. D0 can be cumulative from 6th APR 2010
21-OCT-2009 rlingama 115.109 8976778 Modified the logic to ensure, If TAX1 77 and TAX1 81 record
identifiers exist on an incoming P6/P9 file, we should apply
the value which will be either 0.00 or a positive value.
11-NOV-2009 rlingama 115.110 8510399 Added logic to ensure all the aggregated assignments are updated
or ignored.
8505085 We would stamp the authority code even P6/P9 file is received
with values which are same as before.
11-NOV-2009 rlingama 115.111 8510399 Incorporated the code review comments.
18-Dec-2009 rlingama 115.112 9215663 Modified the code to ensure that the P6/P9 process changes the tax bais form
Non Cumulative to Cumulative after 6th Apr 2010.
18-Jan-2009 rlingama 115.113 9253974 Extended the "Future change effective: DATE" validation to check against sepecial
authority code if "HR: GB Override SOY Authority" profile value is set to Override is allowed.
Modified the code to ensure, for Non aggregated assignments, SOY process updates PAYE details
even though other assignments of the person has future changes.
31-Mar-2011 pprvenka 115.114 11886669 Included the person_id in the order by clause for the cursor csr_mode12 for the bug :11886669
because for the EMP_APL Employees with same full name, records are reported in both
processed and error portion.
14-Sep-2011 pprvenka 115.115 12830138 Modified the maximum length of the global g_authority to 15.
01-Nov-2011 pprvenka 115.116 12649175 Updated the previous pay/tax info, if either of them is present.
01-Nov-2011 pprvenka 115.116 12649175 Updated the previous pay/tax info, if either of them is present.
22-Jan-2013 ssarap 115.118 16201197 Debug package for processing p6/p9 file considering the payroll id for RTI live employees.
------------------------------------------------------------------------*/
-----------------------------------------------------
-- 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_agg_asg constant varchar2(255) := 'No update due to failure of aggregated assignment/s.'; -- bug 8510399
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*/
err_emp_not_found_payroll constant varchar2(255) := 'Record not updated as payroll id supplied does not exists in the application.';
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.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,
scl.segment1 -- Bug#8497477
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,
pay_all_payrolls_f ppf, -- Bug#8497477
hr_soft_coding_keyflex scl -- Bug#8497477
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
-- Start bug#8497477 : added pay_all_payrolls_f and hr_soft_coding_keyflex for tax reference check
and ppf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
and ppf.payroll_id = asg.payroll_id
and g_effective_date between ppf.effective_start_date and ppf.effective_end_date
-- End bug#8497477
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))
-- Bug#8497477: changed group by tax reference followed by existing group by.
group by scl.segment1,peo.person_id, 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
-- Bug#8497477: changed order by tax reference followed by existing oreder by.
order by scl.segment1,upper(peo.full_name),p_id, 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
-- Bug#8497477: changed order by tax reference followed by existing oreder by.
order by paye_ref,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
-- Bug#8497477: changed order by tax reference followed by existing oreder by.
order by sck.segment1,assignment_id, decode(per_system_status,'ACTIVE_ASSIGN',1,'SUSP_ASSIGN',2,'ACTIVE_APL',3,4);
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;*/
select ele.effective_start_date,HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',eev.screen_entry_value)
into l_future_date, p_auth_code
from
pay_element_entries_f ele,
pay_element_entry_values_f eev,
pay_input_values_f inv
where inv.element_type_id = g_element_type_id
and inv.name = 'Authority'
and eev.input_value_id = inv.input_value_id
and ele.element_type_id = inv.element_type_id
and eev.element_entry_id = ele.element_entry_id
and ele.assignment_id = p_assignment_id
and ele.effective_start_date >= p_effective_date
and eev.effective_start_date >= p_effective_date
and p_effective_date between inv.effective_start_date and inv.effective_end_date
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') OR g_effective_date >= fnd_date.canonical_to_date('2010/04/06 00:00:00') then --Bug 8785270: Paye 2009-2010 Changes
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 /*Added terminated for soy 08-09*/
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'))
or
( g_mode = 1
and (l_future = g_effective_date)
and (l_auth_code = 'Override SOY')
and (g_SOY_override_profile = 'OVERRIDE_YES') )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 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 asg.assignment_number = p_m34_rec.assignment_number
and p_m34_rec.national_identifier is null
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 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
and 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 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 asg.assignment_number = p_m34_rec.assignment_number
and 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;
select paaf.assignment_number,
paaf.assignment_id,
nvl(papf.per_information10,'N')
from per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_assignment_status_types pat,
per_all_people_f papf
where paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
and paei.AEI_INFORMATION3 = p_m34_rec.assignment_number
and paaf.assignment_id = paei.assignment_id
and paaf.assignment_status_type_id = pat.assignment_status_type_id
and pat.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and p_m34_rec.effective_date between paaf.effective_start_date and paaf.effective_end_date
and papf.person_id = paaf.person_id
and p_m34_rec.effective_date between papf.effective_start_date and papf.effective_end_date
and ( nvl(papf.per_information10,'N') <> 'Y' OR paaf.PRIMARY_FLAG = 'Y');
select paaf.assignment_id
from per_all_assignments_f paaf,
pay_payrolls_f pay,
hr_soft_coding_keyflex sck
where paaf.person_id = (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =l_asg_id)
and pay.payroll_id = paaf.payroll_id
and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
and sck.SEGMENT1 = p_m34_rec.paye_ref
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_m34_rec.effective_date
)
and p_m34_rec.effective_date between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE;
select 1 into l_pay_id_found from per_assignment_extra_info paei where assignment_id = asg_rec.assignment_id
and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION' and paei.AEI_INFORMATION3 is not null;
SELECT 1
INTO l_rti_tax_ref
FROM pqp_configuration_values
WHERE business_group_id = g_business_group_id
AND pcv_information_category = 'PAY_GB_RTI_FPS_BACS'
AND pcv_information1 = p_m34_rec.paye_ref
AND p_m34_rec.effective_date >= fnd_date.canonical_to_date(PCV_INFORMATION2);
select 1 into l_pay_id_found from per_assignment_extra_info paei where assignment_id = asg_rec.assignment_id
and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION' and paei.AEI_INFORMATION3 is not null;
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);
tb_write_paye_rec.DELETE;
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*/