[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
24-MAY-2007 jdevasah 115.7 5937604 Modified select_tax_interface procedure
and Current_Tax_Interface function for
the new tax interface
Enahced+wage Accumulation.
05-DEC-2007 rnestor 115.8 6658836 PLS-00103 ERROR WHEN COMPILING
Had a Select inside the decode
*/
PROCEDURE create_ele_tp_usg ( p_element_type_id in number
,p_run_type_id in number
,p_element_name in varchar2
,p_run_type_name in varchar2
,p_inclusion_flag in varchar2
,p_effective_date in date
,p_legislation_code in varchar2
,p_business_group_id in number
) IS
cursor c_check_ele_tp_usg ( cp_legislation_code varchar2
,cp_business_group_id number
,cp_element_type_id number
,cp_run_type_id number
,cp_effective_date date ) is
select 1
from pay_element_type_usages_f petu
where petu.element_type_id = cp_element_type_id
and petu.run_type_id = cp_run_type_id
and cp_effective_date between petu.effective_start_date
and petu.effective_end_date
and ( cp_legislation_code is not null and
petu.legislation_code = cp_legislation_code )
and ( cp_business_group_id is not null and
petu.business_group_id = cp_business_group_id );
PROCEDURE delete_ele_type_usages (p_element_name in varchar2,
p_business_group_id in number)
IS
BEGIN
--{
hr_utility.trace('1010 -> Start delete_ele_type_usages ');
delete from PAY_ELEMENT_TYPE_USAGES_F peu1
where EXISTS
(select 'x'
from PAY_ELEMENT_TYPE_USAGES_F peu2,
pay_run_types_f prt,
pay_element_types_F pet
where pet.element_name = p_element_name
and peu2.element_type_id = pet.element_type_id
and peu2.run_type_id = prt.run_type_id
and peu2.ELEMENT_TYPE_USAGE_ID = peu1.ELEMENT_TYPE_USAGE_ID
and peu2.legislation_code IS NULL
and peu2.business_group_id = p_business_group_id
);
hr_utility.trace('1020 -> End delete_ele_type_usages ');
hr_utility.trace('1030 -> WARNING: in delete_ele_type_usages ');
END delete_ele_type_usages;
select 'Y'
into l_payroll_exist
from dual
where exists( select 1
from PAY_PAYROLL_ACTIONS PPA
where PPA.action_type IN ('R','Q')
and PPA.action_status = 'C'
and PPA.business_group_id = p_business_group_id);
select 'Y'
into l_payroll_exist
from PAY_PAYROLL_ACTIONS PPA
where PPA.action_type IN ('R','Q')
and PPA.action_status = 'C'
and PPA.business_group_id = p_business_group_id;
select parameter_value
from pay_action_parameters
where parameter_name = 'WAGE_ACCUMULATION_ENABLED';
Had a select inside a decode statment RLN*/
open c_parm_val_usg;
select --pet.element_name current_interface
decode(p_lookup_code,
'STANDARD', decode(pet.element_name,
'VERTEX','In Use','Not in Use'),
'ENHANCED', decode(pet.element_name,
'US_TAX_VERTEX',decode(l_WAGEACCUM_parm
,'N','In Use','Not In Use')
,'Not in Use'),
'WAGEACCUM', decode(pet.element_name,
'US_TAX_VERTEX',decode(l_WAGEACCUM_parm
,'Y','In Use','Not In Use')
,'Not in Use') ,' ')
into l_ret_value
from pay_element_types_f pet
where pet.element_name IN ('VERTEX',
'US_TAX_VERTEX')
and pet.legislation_code = 'US'
and pet.business_group_id IS NULL
and NOT EXISTS
( select 'x'
from pay_element_type_usages_f petu
,pay_element_types_f pet1
,pay_run_types_f prt
where petu.element_type_id = pet1.element_type_id
and pet1.legislation_code = 'US'
and pet1.business_group_id IS NULL
and prt.legislation_code = 'US'
and prt.business_group_id IS NULL
and petu.run_type_id = prt.run_type_id
and petu.business_group_id = p_business_group_id --IS NULL
and petu.element_Type_id = pet.element_type_id
--and petu.legislation_code = 'US'
and petu.legislation_code IS NULL
and pet1.business_group_id IS NULL
and pet1.legislation_code = 'US'
and prt.legislation_code = 'US'
and exists
( select 'x' from pay_run_types_f prt1
where prt1.run_type_name IN ('Regular Standard Run',
'Separate Payment Run',
'Tax Separate Run',
'Supplemental Standard Run',
'Regular',
'Supplemental')
and prt1.business_group_id IS NULL
and prt1.legislation_code = 'US'
and prt1.run_type_id = petu.run_type_id ));
select decode(p_lookup_code,
'STANDARD', 'In Use','Not in Use')
into l_ret_value
from DUAL;
select decode(p_lookup_code,
'STANDARD', 'In Use','Not in Use')
into l_ret_value
from DUAL;
select 'Y'
into l_element_type_usage_exist
from pay_element_types_f pet
where element_name = p_element_name
and business_group_id IS NULL
and legislation_code = 'US'
and exists
( select 'x'
from pay_element_type_usages_f petu
,pay_element_types_f pet1
,pay_run_types_f prt
where petu.element_type_id = pet1.element_type_id
and pet1.legislation_code = 'US'
and prt.legislation_code = 'US'
and petu.run_type_id = prt.run_type_id
and petu.business_group_id = p_business_group_id --IS NULL
and petu.legislation_code IS NULL --= 'US'
and petu.element_Type_id = pet.element_type_id
and exists
( select 'x' from pay_run_types_f prt1
where prt1.run_type_name IN ('Regular Standard Run',
'Separate Payment Run',
'Tax Separate Run',
'Supplemental Standard Run',
'Regular',
'Supplemental')
and prt1.business_group_id IS NULL
and prt1.legislation_code = 'US'
and prt1.run_type_id = petu.run_type_id ));
PROCEDURE select_tax_interface(errbuf OUT nocopy VARCHAR2,
retcode OUT nocopy NUMBER,
p_business_group_id IN NUMBER,
p_vertex_interface IN VARCHAR2)
IS
--
-- Run Types used for VERTEX or US_TAX_VERTEX Elements
--
CURSOR c_run_type is
select prt.* from pay_run_types_f prt
where prt.run_type_name IN ('Regular Standard Run',
'Separate Payment Run',
'Tax Separate Run',
'Supplemental Standard Run',
'Regular',
'Supplemental')
and prt.business_group_id IS NULL
and prt.legislation_code = 'US';
SELECT pet.ELEMENT_TYPE_ID,
pet.element_name,
pet.business_group_id,
pet.legislation_code,
pet.effective_start_date effective_date
FROM PAY_ELEMENT_TYPES_F pet
WHERE pet.ELEMENT_NAME = c_element_name
AND pet.LEGISLATION_CODE = 'US'
AND pet.business_group_id IS NULL
ORDER by pet.element_name;
SELECT field_name
INTO l_field_name
FROM PAY_LEGISLATIVE_FIELD_INFO
WHERE FIELD_NAME = 'ET_USAGE'
AND LEGISLATION_CODE = 'US';
INSERT INTO PAY_LEGISLATIVE_FIELD_INFO
(FIELD_NAME,
LEGISLATION_CODE,
RULE_TYPE,
RULE_MODE)
VALUES
('ET_USAGE',
'US',
'ENABLE',
'Y');
update pay_action_parameters
set parameter_value= 'N'
where parameter_name='WAGE_ACCUMULATION_ENABLED';
pay_us_vertex_interface.delete_ele_type_usages('VERTEX', p_business_group_id);
pay_us_vertex_interface.delete_ele_type_usages('Workers Compensation', p_business_group_id);
pay_us_vertex_interface.delete_ele_type_usages('VERTEX',p_business_group_id);
pay_us_vertex_interface.delete_ele_type_usages('Workers Compensation',p_business_group_id);
/*Bug#5937604: update pay_action_paramters */
update pay_action_parameters
set parameter_value= 'N'
where parameter_name='WAGE_ACCUMULATION_ENABLED';
/*Bug#5937604: Insert on pay_action_paramters */
select count(*) into l_wage_exists from pay_action_parameters
where parameter_name='WAGE_ACCUMULATION_ENABLED';
insert into pay_action_parameters
(
parameter_name
,parameter_value
)
values
(
'WAGE_ACCUMULATION_ENABLED'
,'Y'
);
update pay_action_parameters
set parameter_value= 'Y'
where parameter_name='WAGE_ACCUMULATION_ENABLED';
pay_us_vertex_interface.delete_ele_type_usages('US_TAX_VERTEX',
p_business_group_id);
pay_us_vertex_interface.delete_ele_type_usages('US_TAX_VERTEX',
p_business_group_id);
END select_tax_interface;