[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
19-DEC-2004 fusman 115.2 Added code to insert legislation rules.
29-APR-2005 mmukherj 115.3 Removed hard coded legislation_code
'US' from the package.The
legislation_code are taken from the
legislation_code attached to the
elements,if it is a seeded element or
from the legislation_code
of the Business Group for the elements,
if it is an user defined element.
21-SEP-2005 ahanda 115.3 Changed the insertion of legislation
rules. Data is inserted if it does not
exist.
06-06-2008 svannian 115.4 to avoid unique constraint voilation
error when upgradation is ran for the second time.
*/
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'); /* 7138282 */
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)
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); /* 7138282 */
END insert_element_span_usages;
select classification_id, element_name, legislation_code, business_group_id
from pay_element_types_f
where element_type_id = cp_element_type_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';
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 = 'Retropay';
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);