[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
31-Aug-2005 rmakhija 115.2 added not exist clause in insert_retro_
comp_usages procedure to ensure the
process is re-runable
06-Sep-2005 rmakhija 115.3 Fixed delete from pay_leg_field_info
18-OCT-2006 rmakhija 115.5 5609218 Added ADV_RETRO_COMPONENT_USAGE leg
field info again
19-OCT-2006 rmakhija 115.6 5609218 Reversed changes done in previous ver
because the leg field info is not
needed to enable the button,
ADVANCED_RETRO leg rule shd enable it
*/
gv_package_name VARCHAR2(100);
PROCEDURE insert_retro_comp_usages
(p_business_group_id in number,
p_legislation_code in varchar2,
p_retro_component_id in number,
p_creator_id in number,
p_retro_comp_usage_id out nocopy number)
IS
ln_retro_component_usage_id NUMBER;
lv_procedure_name := '.insert_retro_comp_usages';
select pay_retro_component_usages_s.nextval
into ln_retro_component_usage_id
from dual;
insert into pay_retro_component_usages
(retro_component_usage_id, retro_component_id, creator_id, creator_type,
default_component, reprocess_type, business_group_id, legislation_code,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login, object_version_number)
SELECT ln_retro_component_usage_id, p_retro_component_id, p_creator_id,
'ET', 'Y', 'R', p_business_group_id, p_legislation_code,
sysdate, 2, sysdate, 2, 2, 1
FROM dual
WHERE NOT EXISTS ( SELECT 1 FROM pay_retro_component_usages
WHERE retro_component_id = p_retro_component_id
AND creator_id = p_creator_id
AND creator_type = 'ET');
END insert_retro_comp_usages;
PROCEDURE insert_element_span_usages
(p_business_group_id in number,
p_retro_element_type_id in number,
p_legislation_code in varchar2,
p_time_span_id in number,
p_retro_comp_usage_id in number)
IS
lv_procedure_name VARCHAR2(100);
lv_procedure_name := '.insert_element_span_usages';
insert into pay_element_span_usages
(element_span_usage_id, business_group_id, time_span_id,
retro_component_usage_id, retro_element_type_id,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login, object_version_number)
--values
SELECT pay_element_span_usages_s.nextval,
p_business_group_id, p_time_span_id,
p_retro_comp_usage_id, p_retro_element_type_id,
sysdate, 2, sysdate, 2, 2, 1
FROM dual
WHERE not exists ( SELECT 1 FROM pay_element_span_usages pesu
WHERE pesu.business_group_id = p_business_group_id
AND pesu.legislation_code IS NULL
AND pesu.time_span_id = p_time_span_id
AND retro_component_usage_id = p_retro_comp_usage_id);
END insert_element_span_usages;
select pet.classification_id, pet.element_name, pet.legislation_code, pet.business_group_id, pec.classification_name
from pay_element_types_f pet, pay_element_classifications pec
where pet.element_type_id = cp_element_type_id
and pet.classification_id = pec.classification_id;
select legislation_code
from per_business_groups
where business_group_id = cp_business_group_id;
select petr.element_set_id
from pay_element_type_rules petr
where petr.element_type_id = cp_element_type_id
and petr.include_or_exclude = 'I'
union all
select pes.element_set_id
from pay_ele_classification_rules pecr,
pay_element_types_f pet,
pay_element_sets pes
where pet.classification_id = pecr.classification_id
and pes.element_set_id = pecr.element_set_id
and (pes.business_group_id = pet.business_group_id
or pet.legislation_code = cp_legislation_code)
and pet.element_type_id = cp_element_type_id
and pecr.classification_id = cp_classification_id
minus
select petr.element_set_id
from pay_element_type_rules petr
where petr.element_type_id = cp_element_type_id
and petr.include_or_exclude = 'E';
select 1
from pay_payroll_actions ppa
where ppa.action_type = 'L'
and ppa.element_set_id = cp_element_set_id;
select 'Y'
from pay_legislation_rules
where legislation_code = cp_legislation_code
and rule_type = cp_rule_type;
ltt_rule_type(1) := 'RETRO_DELETE';
delete pay_legislation_rules
where legislation_code = lv_legislation_code
and rule_type = ltt_rule_type(i);
INSERT INTO pay_legislation_rules(legislation_code,rule_type,rule_mode)
VALUES( lv_legislation_code,ltt_rule_type(i),ltt_rule_mode(i));
select business_group_id, legislation_code, classification_id,
nvl(retro_summ_ele_id, pet.element_type_id),
element_name
from pay_element_types_f pet
where pet.element_type_id = cp_element_type_id
order by pet.effective_start_date desc;
select legislation_code
from per_business_groups
where business_group_id = cp_business_group_id;
select petr.element_set_id
from pay_element_type_rules petr
where petr.element_type_id = cp_element_type_id
and petr.include_or_exclude = 'I'
union all
select pes.element_set_id
from pay_ele_classification_rules pecr,
pay_element_types_f pet,
pay_element_sets pes
where pet.classification_id = pecr.classification_id
and pes.element_set_id = pecr.element_set_id
and (pes.business_group_id = pet.business_group_id
or pet.legislation_code = cp_legislation_code)
and pet.element_type_id = cp_element_type_id
and pecr.classification_id = cp_classification_id
minus
select petr.element_set_id
from pay_element_type_rules petr
where petr.element_type_id = cp_element_type_id
and petr.include_or_exclude = 'E';
select hoi.organization_id
from hr_organization_information hoi,
hr_organization_information hoi2
where hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_BG'
and hoi.organization_id = hoi2.organization_id
and hoi2.org_information_context = 'Business Group Information'
and hoi2.org_information9 = cp_legislation_code
and exists (select 1 from pay_payroll_actions ppa
where ppa.business_group_id = hoi.organization_id
and ppa.action_type = 'L'
and ppa.element_set_id = cp_element_set_id
);
select retro_component_id, pts.time_span_id
from pay_retro_components prc,
pay_time_spans pts
where pts.creator_id = prc.retro_component_id
and prc.legislation_code = cp_legislation_code
and prc.short_name = 'UK_Enh_Retro';
insert_retro_comp_usages
(p_business_group_id => null
,p_legislation_code => ln_legislation_code
,p_retro_component_id => gn_retro_component_id
,p_creator_id => p_element_type_id
,p_retro_comp_usage_id => ln_retro_comp_usage_id);
insert_element_span_usages
(p_business_group_id => ln_business_group_id
,p_retro_element_type_id => ln_retro_element_type_id
,p_legislation_code => ln_legislation_code
,p_time_span_id => gn_time_span_id
,p_retro_comp_usage_id => ln_retro_comp_usage_id);
insert_retro_comp_usages
(p_business_group_id => ln_ele_business_group_id
,p_legislation_code => null
,p_retro_component_id => gn_retro_component_id
,p_creator_id => p_element_type_id
,p_retro_comp_usage_id => ln_retro_comp_usage_id);
insert_element_span_usages
(p_business_group_id => ln_ele_business_group_id
,p_retro_element_type_id => ln_retro_element_type_id
,p_legislation_code => null
,p_time_span_id => gn_time_span_id
,p_retro_comp_usage_id => ln_retro_comp_usage_id);