[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT element_type_id, object_version_number
FROM pay_shadow_element_types
WHERE template_id = l_template_id
AND element_name = c_ele_name;
SELECT ptco.core_object_id
FROM pay_shadow_element_types psbt,
pay_template_core_objects ptco
WHERE psbt.template_id = l_template_id
AND psbt.element_name = c_element_name
AND ptco.template_id = psbt.template_id
AND ptco.shadow_object_id = psbt.element_type_id
AND ptco.core_object_type = 'ET';
SELECT template_id
FROM pay_element_templates
WHERE template_name = l_template_name
AND legislation_code = p_legislation_code
AND template_type = 'T'
AND business_group_id is NULL;
PROCEDURE update_eletyp_for(l_ele_name IN VARCHAR2
,l_formula_name IN VARCHAR2)
IS
CURSOR c1_getfor(lc_formula_name varchar2) IS
SELECT formula_id
FROM ff_formulas_f
WHERE formula_name = lc_formula_name
AND (business_group_id = p_bg_id OR business_group_id IS NULL)
AND (legislation_code = 'GB' OR legislation_code IS NULL);
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = lc_ele_name
AND (business_group_id = p_bg_id OR business_group_id IS NULL)
AND (legislation_code = 'GB' OR legislation_code IS NULL);
SELECT rowid
FROM pay_element_types_f
WHERE element_type_id = lc_ele_type_id
FOR UPDATE NOWAIT;
UPDATE pay_element_types_f
SET formula_id = c1_rec.formula_id
WHERE rowid = c3_rec.rowid;
END update_eletyp_for;
PROCEDURE update_ipval_defval(l_ele_name IN VARCHAR2
,l_name IN VARCHAR2
,l_value IN NUMBER)
IS
CURSOR c1_getinput(lc_ele_name Varchar2,lc_name varchar2) IS
SELECT input_value_id,
piv.name,
piv.element_type_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE element_name= lc_ele_name
AND piv.element_type_id=pet.element_type_id
AND (piv.business_group_id =p_bg_id OR piv.business_group_id IS NULL)
AND piv.name =lc_name
AND (piv.legislation_code='GB' OR piv.legislation_code IS NULL);
SELECT rowid
FROM pay_input_values_f
WHERE input_value_id = lc_ip_id
AND element_type_id = lc_element_type_id
FOR UPDATE NOWAIT;
UPDATE pay_input_values_f
SET default_value = l_value
WHERE rowid = c2_rec.rowid;
END update_ipval_defval;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = c_object_name
AND business_group_id = p_bg_id;
SELECT ptco.core_object_id
FROM pay_shadow_balance_types psbt,
pay_template_core_objects ptco
WHERE psbt.template_id = l_template_id
AND psbt.balance_name = c_object_name
AND ptco.template_id = psbt.template_id
AND ptco.shadow_object_id = psbt.balance_type_id;
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_element_type_id
,p_element_name => p_ele_name
,p_reporting_name => p_ele_reporting_name
,P_DESCRIPTION => p_ele_description
,p_object_version_number => l_ele_obj_ver_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_element_type_id
,p_element_name => p_ele_name || l_sub(i)
,P_DESCRIPTION => p_ele_description
,p_object_version_number => l_ele_obj_ver_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_avcren
,p_object_version_number => l_ele_obj_ver_number
);
update_eletyp_for(p_ele_name
,'ONCE_EACH_PERIOD');
update_ipval_defval(p_ele_name
,l_name
,p_emp_contribution);
update_ipval_defval(p_ele_name
,l_name
,p_eer_rate);
PROCEDURE delete_user_template
(p_business_group_id in number
,p_ele_type_id in number
,p_ele_name in varchar2
,p_effective_date in date
) IS
--
l_template_id NUMBER(9);
l_proc varchar2(60) :='pqp_gb_pensions_template.delete_user_template';
SELECT element_type_extra_info_id
FROM pay_element_type_extra_info petei
WHERE element_type_id=p_ele_type_id ;
SELECT template_id
FROM pay_element_templates
WHERE base_name = p_ele_name
AND business_group_id = p_business_group_id
AND template_type = 'U';
pay_element_extra_info_api.delete_element_extra_info
(p_validate => FALSE
,p_element_type_extra_info_id => l_eei_info_id
,p_object_version_number => l_ovn_eei);
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
END delete_user_template;