[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select lkp.lookup_code,
lkp.meaning
from PER_ASSIGNMENTS_F paf,
PER_ADDRESSES pa,
HR_LOOKUPS lkp
where paf.assignment_id = p_assignment_id
and p_session_date between paf.effective_start_date and
paf.effective_end_date
and pa.person_id = paf.person_id
and pa.primary_flag = 'Y'
and p_session_date between pa.date_from and
nvl(pa.date_to,p_session_date)
and lkp.lookup_code = pa.region_1
and lkp.lookup_type = 'CA_PROVINCE';
select lkp.lookup_code,
lkp.meaning
from PER_ASSIGNMENTS_F paf,
HR_LOCATIONS hrl,
HR_LOOKUPS lkp
where paf.assignment_id = p_assignment_id
and p_session_date between paf.effective_start_date and
paf.effective_end_date
and paf.location_id = hrl.location_id
and lkp.lookup_code = hrl.region_1
and lkp.lookup_type = 'CA_PROVINCE';
'INSERT', it will be an 'UPDATE' with proper datetracking
*/
/* get_province_codes(parameter-list) */
begin
l_assignment_id := p_assignment_id;
SELECT fnd_number.canonical_to_number(information_value)
FROM pay_ca_legislation_info pcli
WHERE pcli.information_type = 'BASIC_EXEMPTION_AMOUNT'
AND ((p_province IS NULL and pcli.jurisdiction_code is null)
OR (pcli.jurisdiction_code = p_province))
AND p_effective_date BETWEEN pcli.start_date AND pcli.end_date;
select min(effective_start_date)
from per_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.assignment_type <> 'A';
select
tax_credit_amount
,basic_exemption_flag
,additional_tax
,annual_dedn
,total_expense_by_commission
,total_remnrtn_by_commission
,prescribed_zone_dedn_amt
,fed_override_amount
,fed_override_rate
,fed_lsf_amount
from pay_ca_emp_fed_tax_info_f peft where
peft.assignment_id = p_assignment_id and
p_effective_date between peft.effective_start_date and peft.effective_end_date;
select fnd_number.canonical_to_number(information_value)
into l_tax_credit_amount
from pay_ca_legislation_info pcli
where pcli.information_type = 'BASIC_EXEMPTION_AMOUNT'
and pcli.jurisdiction_code IS NULL
and p_effective_date between pcli.start_date and pcli.end_date;
select
cpp_qpp_exempt_flag
,fed_exempt_flag
,ei_exempt_flag
,tax_calc_method
from pay_ca_emp_fed_tax_info_f peft where
peft.assignment_id = p_assignment_id and
p_effective_date between peft.effective_start_date and peft.effective_end_date;
select pcp.province_abbrev, '70-'||pcp.province_code||'-0000' geocode
from per_assignments_f paf,
hr_locations hl,
pay_ca_provinces_v pcp
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.location_id = hl.location_id
and hl.region_1 = pcp.province_abbrev;
select pcp.province_abbrev, '70-'||pcp.province_code||'-0000' geocode
from pay_ca_emp_fed_tax_info_f pf,
pay_ca_provinces_v pcp
where p_effective_date between pf.effective_start_date
and pf.effective_end_date
and pf.employment_province = pcp.province_abbrev
and pf.assignment_id = p_assignment_id;
select
ca_tax_information1
from pay_ca_emp_fed_tax_info_f peft where
peft.assignment_id = p_assignment_id and
p_effective_date between
peft.effective_start_date and peft.effective_end_date and
ca_tax_information_category = 'FED' ;
select
cpp_election_date
,cpp_revocation_date
from pay_ca_emp_fed_tax_info_f peft where
peft.assignment_id = p_assignment_id and
p_effective_date between peft.effective_start_date and peft.effective_end_date;
select
tax_credit_amount
,basic_exemption_flag
,no_of_infirm_dependants
,no_of_dependants
,annual_dedn
,total_expense_by_commission
,total_remnrtn_by_commission
,prescribed_zone_dedn_amt
,additional_tax
,prov_override_rate
,prov_override_amount
,prov_lsp_amount
from pay_ca_emp_prov_tax_info_f pept where
pept.assignment_id = p_assignment_id and
p_effective_date between pept.effective_start_date and pept.effective_end_date and
pept.province_code = p_province_abbrev;
select fnd_number.canonical_to_number(information_value)
into l_tax_credit_amount
from pay_ca_legislation_info pcli
where pcli.information_type = 'BASIC_EXEMPTION_AMOUNT'
and pcli.jurisdiction_code = p_province_abbrev
and p_effective_date between pcli.start_date and pcli.end_date;
select
jurisdiction_code
, extra_info_not_provided
, marriage_status
, non_resident_status
, disability_status
,prov_exempt_flag
,pmed_exempt_flag
,wc_exempt_flag
,qpp_exempt_flag
,tax_calc_method
,ppip_exempt_flag
from pay_ca_emp_prov_tax_info_f pept where
pept.assignment_id = p_assignment_id and
p_effective_date between pept.effective_start_date and pept.effective_end_date and
pept.province_code = p_province_abbrev;
select
ca_tax_information1,
ca_tax_information2
from pay_ca_emp_prov_tax_info_f pept where
pept.assignment_id = p_assignment_id and
p_effective_date between
pept.effective_start_date and pept.effective_end_date and
-- ca_tax_information_category = p_province_abbrev;
select substr(addr.address_line1,1,37) ,
substr(addr.address_line2,1,37) ,
substr(addr.address_line3,1,37) ,
rtrim(substr(addr.town_or_city,1,23)) ||' '||addr.region_1||' '||addr.postal_code
from per_addresses addr
WHERE addr.person_id = p_person_id
AND addr.primary_flag = 'Y'
AND p_effective_date between
addr.date_from and nvl(addr.date_to, p_effective_date);
select pay_basis from per_pay_bases
where pay_basis_id = l_pay_basis_id;
select decode(instr(peev.screen_entry_value,'.'),
0,
peev.screen_entry_value|| '.00',
peev.screen_entry_value
)
from pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f piv
WHERE l_effective_date
between pee.effective_start_date AND pee.effective_end_date
AND pee.element_entry_id = peev.element_entry_id
AND pee.entry_type = 'E'
AND pee.assignment_id = l_assignment_id
AND l_effective_date
between peev.effective_start_date and peev.effective_end_date
AND peev.input_value_id+0 = piv.input_value_id
AND l_effective_date
between piv.effective_start_date AND piv.effective_end_date
AND piv.input_value_id = l_input_value_id;
select input_value_id from per_pay_bases
where pay_basis_id = l_pay_basis_id;
select decode(p_dimension,'CURRENT',amount_current,'YTD',amount_ytd)
into l_value
from pay_ca_soe_summ_balances_v pcs
where pcs.assignment_action_id = p_assignment_action_id
and pcs.base_bal_name = decode(p_information_type,'GROSS_PAY_SUMM', /*balance_name changed to base_bal_name against bug#5169734*/
'Gross Pay',
'TAXABLE_BENEFIT_SUMM',
'Taxable Benefits',
'GROSS_EARNINGS_SUMM',
'Gross Earnings',
'TAXES_SUMM',
'Tax Deductions',
'NET_PAY_SUMM',
'Payments');
select sum(decode(p_dimension,'CURRENT',amount_current,'YTD',amount_ytd))
into l_value
from pay_ca_soe_summ_balances_v pcs
where pcs.assignment_action_id = p_assignment_action_id
and pcs.balance_name in (
'Pre Tax Deductions',
'Involuntary Deductions',
'Voluntary Deductions'
);
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = l_payroll_action_id;
select nvl(sum(pev.screen_entry_value),0)
from pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_Values_f pev,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_payroll_actions ppa2
where pee.assignment_id = l_assignment_id
and piv.element_type_id = pet.element_type_id
and pee.element_type_id = pet.element_type_id
and pee.element_entry_id = pev.element_Entry_id
and pev.input_value_id = piv.input_value_id
and ppa.date_earned between pee.effective_start_date and pee.effective_end_Date
and ppa.payroll_action_id = l_payroll_action_id
and pet.element_name = l_exemption
and piv.name = 'Basic Exemption'
and pee.creator_type = 'RR'
and paa.assignment_action_id = pee.source_asg_action_id
and paa.payroll_action_id = ppa2.payroll_action_id
and to_char(ppa.effective_date,'YYYY') = to_char(ppa2.effective_date,'YYYY');
select to_number(to_char(effective_date,'YYYY'))
from pay_payroll_actions
where payroll_action_id = l_payroll_action_id;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = l_payroll_action_id;
select effective_date
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select creator_type, source_id, source_asg_action_id,assignment_id
from pay_element_entries_f
where element_entry_id = p_element_entry_id;
select hla.region_1
from per_all_assignments_f paf,
hr_locations_all hla
where paf.assignment_id = l_asst_id
and l_cur_date between paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
and hla.location_id = paf.location_id;
select ppa.effective_date,hla.region_1
from pay_run_results prr,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paf,
hr_locations_all hla
where prr.run_result_id = l_run_result_id
and prr.assignment_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and ppa.effective_date between paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
and hla.location_id = paf.location_id;
select ppa.effective_date,hla.region_1
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paf,
hr_locations_all hla
where paa.assignment_action_id = l_asg_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and ppa.effective_date between paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
and hla.location_id = paf.location_id;
Delete_fed_tax_rule procedure calls
pay_ca_emp_fedtax_inf_api.delete_ca_emp_fedtax_inf procedure for updating
Effective_End_Date of tax records in PAY_CA_EMP_FED_TAX_INFO_F table.
pay_ca_emp_prvtax_inf_api.delete_ca_emp_prvtax_inf procedure for updating
Effective_End_Date of tax records in PAY_CA_EMP_PROV_TAX_INFO_F table.
*****************************************************************************/
procedure delete_fed_tax_rule
(p_effective_date in date
,p_datetrack_delete_mode in varchar2
,p_assignment_id in number
,p_delete_routine in varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number out nocopy number
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := 'delete_fed_tax_rule';
select fed.emp_fed_tax_inf_id, fed.object_version_number
from pay_ca_emp_fed_tax_info_f fed
where fed.assignment_id = p_assignment_id
and l_effective_date between fed.effective_start_date
and fed.effective_end_date;
select sta.emp_province_tax_inf_id, sta.object_version_number
from pay_ca_emp_prov_tax_info_f sta
where sta.assignment_id = p_assignment_id
and l_effective_date between sta.effective_start_date
and sta.effective_end_date;
if p_datetrack_delete_mode NOT IN ('ZAP', 'DELETE') then
hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
if nvl(p_delete_routine,'X') <> 'ASSIGNMENT' then
hr_utility.set_message(801, 'HR_6674_PAY_ASSIGN');
pay_ca_emp_prvtax_inf_api.delete_ca_emp_prvtax_inf(
p_validate => NULL -- check whether NULL is correct.
,p_emp_province_tax_inf_id => l_emp_prov_tax_inf_id
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_object_version_number => l_tmp_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_delete_mode
);
,p_datetrack_mode => p_datetrack_delete_mode
);
pay_ca_emp_fedtax_inf_api.delete_ca_emp_fedtax_inf(
p_validate => NULL -- check whether NULL is correct.
,p_emp_fed_tax_inf_id => l_emp_fed_tax_inf_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_delete_mode
);
end delete_fed_tax_rule;
and calls Delete_fed_tax_rule procedure.
*****************************************************************************/
procedure maintain_ca_employee_taxes
( p_period_of_service_id in number,
p_effective_date in date
,p_datetrack_mode in varchar2 default null
,p_delete_routine in varchar2 default null
) is
TYPE assign_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
select null
from per_assignments_f asg,
hr_organization_information bus
where asg.assignment_id = p_csr_assignment_id
and bus.organization_id = asg.business_group_id
and bus.org_information9 = 'CA'
and bus.org_information_context = 'Business Group Information'
and p_effective_date between asg.effective_start_date
and asg.effective_end_date ;
select asg.assignment_id
from per_assignments_f asg,
per_periods_of_service pps
where asg.person_id = pps.person_id
and pps.period_of_service_id = p_period_of_service_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date;
'DELETE',
'UPDATE',
'CORRECTION',
'UPDATE_OVERRIDE',
'UPDATE_CHANGE_INSERT') then
hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
elsif p_datetrack_mode in ('ZAP', 'DELETE') then
hr_utility.set_location(l_proc, 40);
delete_fed_tax_rule(
p_effective_date => l_effective_date
,p_datetrack_delete_mode => p_datetrack_mode
,p_assignment_id => l_assignment_tbl(l_cnt)
,p_delete_routine => p_delete_routine
,p_effective_start_date => l_fed_eff_start_date
,p_effective_end_date => l_fed_eff_end_date
,p_object_version_number => l_fed_object_version_number
);
procedure delete_tax_record
( p_period_of_service_id in number,
p_final_process_date in date) is
-- Declare cursors and local variables
--
l_proc varchar2(72) := 'delete_tax_record';
p_datetrack_mode => 'DELETE',
p_delete_routine => 'ASSIGNMENT'
);
end delete_tax_record;
select NVL(ei_exempt_flag,'N')
from pay_ca_emp_fed_tax_info_f peft where
peft.assignment_id = l_assignment_id
AND l_roe_end_date between peft.effective_start_date and peft.effective_end_date;