[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT soft_coding_keyflex_id
FROM hr_soft_coding_keyflex
WHERE segment1 = l_tax_ref;
SELECT petf.element_type_id
FROM pay_element_types_f petf
WHERE petf.element_name = 'PAYE Details'
AND petf.legislation_code = 'GB'
AND c_eff_date between petf.effective_start_date and petf.effective_end_date;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = c_ele_type_id
AND legislation_code = 'GB'
AND name = 'Tax Code';
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = c_ele_type_id
AND legislation_code = 'GB'
AND name = 'Tax Basis'; */
SELECT DISTINCT papf.person_id
FROM per_all_people_f papf
,per_periods_of_service ppos
WHERE ppos.person_id = papf.person_id
AND (papf.current_employee_flag = 'Y'
OR
ppos.final_process_date >= c_effective_date
)
AND papf.per_information10 = 'Y'
AND papf.business_group_id = c_bg_id
AND c_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
ORDER BY papf.person_id desc;
SELECT papf.last_name lname,
paaf.person_id pid,
paaf.assignment_id aid,
paaf.assignment_number anum,
pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, c_taxref, c_effective_date) cpe_start,
-- pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_taxref, c_effective_date) cpe_end,
peef.effective_start_date effst,
peef.effective_end_date effend,
peef.object_version_number ovn,
min(decode(pivf.name, 'Tax Code', peevf.screen_entry_value, null)) Tax_Code,
-- min(decode(pivf.name, 'Tax Basis', substr(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',peevf.screen_entry_value),1,80),null)) Tax_Basis,
min(decode(pivf.name, 'Tax Basis',peevf.screen_entry_value,null)) Tax_Basis,
min(decode(pivf.name, 'Refundable', substr(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',peevf.screen_entry_value),1,80),null)) Refundable,
hr_chkfmt.changeformat(nvl(min(decode(pivf.name, 'Pay Previous', peevf.screen_entry_value, null)), 0), 'M', 'GBP') Pay_Previous,
hr_chkfmt.changeformat(nvl(min(decode(pivf.name, 'Tax Previous', peevf.screen_entry_value, null)), 0), 'M', 'GBP') Tax_Previous,
min(decode(pivf.name, 'Authority', substr(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',peevf.screen_entry_value),1,80),null)) Authority
from per_all_people_f papf,
per_all_assignments_f paaf,
pay_element_entries_f peef,
pay_element_entry_values_f peevf,
pay_input_values_f pivf,
-- per_assignment_status_types past,
pay_all_payrolls_f pap,
hr_soft_coding_keyflex hsck
where papf.person_id = c_person_id
and papf.person_id = paaf.person_id
and c_effective_date between papf.effective_start_date and papf.effective_end_date
and paaf.assignment_id = peef.assignment_id
and c_effective_date between paaf.effective_start_date and paaf.effective_end_date
and peef.element_type_id = c_ele_type_id
and peef.element_entry_id = peevf.element_entry_id
and c_effective_date between peef.effective_start_date and peef.effective_end_date
and peevf.input_value_id = pivf.input_value_id
and c_effective_date between peevf.effective_start_date and peevf.effective_end_date
and c_effective_date between pivf.effective_start_date and pivf.effective_end_date
-- AND paaf.assignment_status_type_id = past.assignment_status_type_id
-- AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = pap.payroll_id
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
--AND pap.soft_coding_keyflex_id= c_sft_coding_id
AND pap.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND hsck.segment1 = c_taxref
/*AND c_effective_date between pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, c_taxref,c_effective_date)
AND pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_taxref,c_effective_date) */
AND pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, c_taxref,c_effective_date) = c_cpe_start_date
AND pay_p45_pkg.PAYE_SYNC_P45_ISSUED_FLAG(paaf.assignment_id,l_effective_date) = 'N'
group by papf.last_name,
paaf.person_id,
paaf.assignment_number,
paaf.assignment_id,
peef.effective_start_date,
peef.effective_end_date,
peef.object_version_number,
pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, c_taxref, c_effective_date)
-- pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_taxref, c_effective_date)
order by peef.effective_start_date desc,paaf.assignment_id desc;
SELECT count(1) cnt,cpe_date
FROM (SELECT distinct
ppev.TAX_CODE,
ppev.Tax_Basis,
ppev.Pay_Previous,
ppev.Tax_Previous,
ppev.Refundable,
ppev.Authority,
ppev.cpe_date
FROM (SELECT ele.rowid ROW_ID,
min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) Tax_Code,
min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) Tax_Basis,
min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) Refundable,
min(decode(inv.name, 'Pay Previous', nvl(eev.screen_entry_value,0), null)) Pay_Previous,
min(decode(inv.name, 'Tax Previous', nvl(eev.screen_entry_value,0), null)) Tax_Previous,
min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) Authority,
pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, l_tax_ref,l_effective_date) cpe_date
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 l_effective_date between ele.effective_start_date and ele.effective_end_date
AND eev.input_value_id + 0 = inv.input_value_id
AND l_effective_date between eev.effective_start_date and eev.effective_end_date
AND inv.element_type_id = elt.element_type_id
AND l_effective_date between inv.effective_start_date and inv.effective_end_date
AND ele.element_link_id = lnk.element_link_id
AND l_effective_date between lnk.effective_start_date and lnk.effective_end_date
AND elt.element_type_id = l_ele_type_id
AND l_effective_date between elt.effective_start_date and elt.effective_end_date
AND ele.assignment_id=paaf.assignment_id
AND l_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.payroll_id = papf.payroll_id
AND l_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND hsck.segment1 = l_tax_ref
AND paaf.person_id = c_person_id
AND pay_p45_pkg.PAYE_SYNC_P45_ISSUED_FLAG(paaf.assignment_id,l_effective_date) = 'N'
AND pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, l_tax_ref,l_effective_date) <> l_cpe_start_date
GROUP BY pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, l_tax_ref,l_effective_date),
ele.rowid) ppev )
GROUP BY cpe_date;
Select paaf.assignment_id,
paaf.assignment_number,
pap.last_name
from per_all_people_f pap,
per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex hsck
where paaf.person_id = pap.person_id
and paaf.person_id = c_person_id
and c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
and c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
and paaf.payroll_id = papf.payroll_id
and papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
and hsck.segment1 = c_tax_ref
and c_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date;
select min(ele.effective_start_date)
from pay_element_entries_f ele
where ele.effective_start_date >= c_eff_date
and ele.assignment_id = c_ass_id
and ele.element_type_id = c_ele_type_id;
fnd_file.put(FND_FILE.LOG,'Manual update may be required for this records.');
paye_future_file.delete;
select element_type_id
from pay_element_types_f
where element_name = 'PAYE Details'
and legislation_code = 'GB';
select papf.last_name,
papf.person_id,
paaf.assignment_id,
paaf.assignment_number,
paaf.payroll_id,
peef.effective_start_date,
peef.effective_end_date,
pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, P_IN_TAX_DISTRICT, P_EFF_DATE) cpe_start_date,
--paaf.object_version_number,
peef.element_entry_id,
peef.creator_id,
peef.object_version_number,
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
from per_all_people_f papf,
per_all_assignments_f paaf,
pay_element_entries_f peef,
pay_element_entry_values_f eev,
pay_input_values_f inv,
pay_all_payrolls_f pap,
per_periods_of_service ppos, -- Added for considering Terminated Employees till FPD
hr_soft_coding_keyflex flex
where paaf.assignment_id = P_IN_ASSIGNMENT_ID
and paaf.assignment_type = 'E'
and paaf.person_id = papf.person_id
and paaf.payroll_id = pap.payroll_id
/* Bug Fix to pick assignments that are terminated and before FPD
-- and papf.current_employee_flag = 'Y'
*/
and ppos.person_id = papf.person_id
and (papf.current_employee_flag = 'Y'
OR
ppos.final_process_date >= P_EFF_DATE)
/* End of FPD Bug Fix */
and papf.per_information10 = 'Y'
and flex.soft_coding_keyflex_id = pap.soft_coding_keyflex_id
and flex.segment1 = P_IN_TAX_DISTRICT
and inv.element_type_id = P_IN_ELE_TYP_ID
and inv.input_value_id = eev.input_value_id
and peef.element_type_id = P_IN_ELE_TYP_ID
and peef.assignment_id = paaf.assignment_id
-- and --peef.entry_information_category = 'GB_PAYE'
and eev.element_entry_id=peef.element_entry_id
and P_EFF_DATE between papf.effective_start_date and papf.effective_end_date
and P_EFF_DATE between paaf.effective_start_date and paaf.effective_end_date
and P_EFF_DATE between peef.effective_start_date and peef.effective_end_date
and P_EFF_DATE between eev.effective_start_date and eev.effective_end_date
and P_EFF_DATE between inv.effective_start_date and inv.effective_end_date
and P_EFF_DATE between pap.effective_start_date and pap.effective_end_date
group by papf.last_name,
papf.person_id,
paaf.assignment_id,
paaf.assignment_number,
paaf.payroll_id,
peef.effective_start_date,
peef.effective_end_date,
pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, P_IN_TAX_DISTRICT, P_EFF_DATE),
--paaf.object_version_number,
peef.element_entry_id,
peef.creator_id,
peef.object_version_number;
select argument1, --Tax Reference
fnd_date.canonical_to_date(argument3) --Effective Date
into l_arg1, l_arg2
from fnd_concurrent_requests
where request_id=download_cp_req_id;
/* Read each line from file to get the Records to be updated
* If No Records Found, come out appropriately. */
WHILE TRUE
LOOP
IF (substr(l_present_line,1,41)='Last Name ~Assignment Num ~T_Code ~')
THEN
null;
/* If end of file is reached before reading the records to be updated,
* throw exception.
*/
WHEN NO_DATA_FOUND
THEN
retcode := 2;
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Could not find any records to be updated or the No Records Found message in the mentioned file.');
hr_entry_api.update_element_entry(p_dt_update_mode => 'UPDATE',
p_session_date => download_cp_eff_date,
p_element_entry_id => tab_paye_file(l_count).element_entry_id,
p_input_value_id1 => tab_paye_file(l_count).tax_code_iv_id,
p_input_value_id2 => tab_paye_file(l_count).tax_basis_iv_id,
p_input_value_id3 => tab_paye_file(l_count).pay_previous_iv_id,
p_input_value_id4 => tab_paye_file(l_count).tax_previous_iv_id,
p_input_value_id5 => tab_paye_file(l_count).refundable_iv_id,
p_input_value_id6 => tab_paye_file(l_count).authority_iv_id,
p_entry_value1 => tab_paye_file(l_count).sug_tax_code,
p_entry_value2 => tab_paye_file(l_count).sug_tax_basis,
p_entry_value3 => tab_paye_file(l_count).sug_previous_pay,
p_entry_value4 => tab_paye_file(l_count).sug_previous_tax,
p_entry_value5 => tab_paye_file(l_count).sug_refundable,
p_entry_value6 => tab_paye_file(l_count).sug_authority
);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,rpad(tab_paye_file(l_count).last_name,20,' ')||rpad(tab_paye_file(l_count).assignment_number,20,' ')||'Record Updated');
tab_paye_file.delete(l_count);