[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 balance_type_id, object_version_number
FROM pay_shadow_balance_types
WHERE template_id = l_template_id AND balance_name = c_bal_name;
SELECT 'X'
FROM pay_element_templates
WHERE template_name = g_template_name
AND business_group_id = p_business_group_id
AND template_type = 'U'
AND UPPER (base_name) = UPPER (p_ele_base_name);
SELECT template_id
FROM pay_element_templates
WHERE template_name = g_template_name
AND legislation_code = p_legislation_code
AND template_type = 'T'
AND business_group_id IS NULL;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_object_name
AND business_group_id = p_business_group_id
AND p_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
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 = p_object_name
AND ptco.template_id = psbt.template_id
AND ptco.shadow_object_id = psbt.balance_type_id;
PROCEDURE update_ipval_defval (
p_ele_name IN VARCHAR2
,p_ip_name IN VARCHAR2
,p_def_value IN VARCHAR2
)
IS
CURSOR csr_getinput (c_ele_name VARCHAR2, c_iv_name VARCHAR2)
IS
SELECT piv.input_value_id, piv.NAME, piv.element_type_id
FROM pay_input_values_f piv, pay_element_types_f pet
WHERE element_name = c_ele_name
AND piv.element_type_id = pet.element_type_id
AND piv.business_group_id = p_business_group_id
AND piv.NAME = c_iv_name;
SELECT ROWID
FROM pay_input_values_f
WHERE input_value_id = c_ip_id
AND element_type_id = c_element_type_id
FOR UPDATE NOWAIT;
|| 'update_ipval_defval';
UPDATE pay_input_values_f
SET default_value = p_def_value
WHERE ROWID = csr_updinput_rec.ROWID;
END update_ipval_defval;
SELECT fra.formula_id, fra.formula_name, fty.formula_type_id
,fty.formula_type_name
FROM ff_formulas_f fra
,ff_formula_types fty
,pay_status_processing_rules_f spr
WHERE fty.formula_type_id = fra.formula_type_id
AND fra.formula_id = spr.formula_id
AND spr.assignment_status_type_id IS NULL
AND spr.element_type_id = c_element_type_id
AND p_effective_start_date BETWEEN fra.effective_start_date
AND fra.effective_end_date
AND p_effective_start_date BETWEEN spr.effective_start_date
AND spr.effective_end_date;
SELECT balance_name
FROM pay_balance_types
WHERE balance_type_id = p_balance_type_id
AND ( ( business_group_id IS NOT NULL
AND business_group_id = p_business_group_id
)
OR ( legislation_code IS NOT NULL
AND legislation_code = g_template_leg_code
)
OR (business_group_id IS NULL AND legislation_code IS NULL)
);
SELECT classification_name, default_priority
FROM pay_element_classifications
WHERE classification_id = p_classification_id;
SELECT relative_processing_priority
FROM pay_shadow_element_types
WHERE element_type_id = p_element_type_id;
SELECT pension_type_id, pension_type_name, effective_start_date
,effective_end_date, pension_category
,ee_contribution_percent, er_contribution_percent
,ee_contribution_fixed_rate, er_contribution_fixed_rate
,ee_contribution_bal_type_id, er_contribution_bal_type_id
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND p_effective_start_date BETWEEN effective_start_date
AND effective_end_date
AND ( ( business_group_id IS NOT NULL
AND business_group_id = p_business_group_id
)
OR ( legislation_code IS NOT NULL
AND legislation_code = g_template_leg_code
)
OR (business_group_id IS NULL AND legislation_code IS NULL)
);
PROCEDURE insert_validate
IS
--
-- Cursor to check whether provider exists
CURSOR csr_chk_provider_exists
IS
SELECT 'X'
FROM hr_all_organization_units hou
,hr_organization_information hoi
WHERE hou.organization_id = p_pension_provider_id
AND ( hou.business_group_id = p_business_group_id
OR hou.business_group_id IS NULL
)
AND p_effective_start_date BETWEEN date_from
AND NVL (
date_to
,p_effective_start_date
)
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'FR_PENSION'
AND hoi.org_information2 = 'Y';
SELECT 'X'
FROM hr_organization_information
WHERE organization_id = p_pension_provider_id
AND org_information_context = 'PQP_GB_PENSION_TYPES_INFO'
AND org_information1 = p_pension_type_id;
SELECT 'X'
FROM pay_element_type_extra_info eeit, pay_element_types_f pet
WHERE pet.element_type_id = c_element_type_id
AND ( ( pet.business_group_id IS NOT NULL
AND pet.business_group_id = p_business_group_id
)
OR ( pet.legislation_code IS NOT NULL
AND pet.legislation_code = g_template_leg_code
)
OR ( pet.business_group_id IS NULL
AND pet.legislation_code IS NULL
)
)
AND p_effective_start_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND eeit.element_type_id = pet.element_type_id
AND eeit.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
AND eeit.eei_information4 = 'OCP'
AND eeit.eei_information12 IS NULL;
SELECT org_information7
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'Tax Details References';
SELECT TRANSLATE (
UPPER (c_number)
,'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
,'AAAAAAAAAAAAAAAAAAAAAAAAAA9999999999'
)
FROM DUAL;
|| 'insert_validate';
END insert_validate;
SELECT formula_id
FROM pay_shadow_formulas
WHERE formula_name = p_formula_name
AND business_group_id = p_business_group_id
AND template_type = 'U';
PROCEDURE update_shadow_formula (
p_formula_id IN NUMBER
,p_search_string IN VARCHAR2
,p_replace_string IN VARCHAR2
)
IS
--
-- Cursor to retrieve the formula information
CURSOR csr_get_formula_info
IS
SELECT formula_text
FROM pay_shadow_formulas
WHERE formula_id = p_formula_id;
|| 'update_shadow_formula';
UPDATE pay_shadow_formulas
SET formula_text = l_formula_text
WHERE formula_id = p_formula_id;
END update_shadow_formula;
SELECT input_value_id
FROM pay_input_values_f
WHERE NAME = p_input_value_name
AND element_type_id = c_element_type_id
AND p_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
PROCEDURE update_ipv_mandatory_flag (
p_input_value_name IN VARCHAR2
,p_element_type_id IN NUMBER
,p_mandatory_flag IN VARCHAR2
)
IS
--
l_proc_name VARCHAR2 (80)
:= g_proc_name
|| 'update_ipv_mandatory_flag';
UPDATE pay_input_values_f
SET mandatory_flag = p_mandatory_flag
WHERE input_value_id = l_input_value_id
AND p_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
END update_ipv_mandatory_flag;
pay_balance_feeds_f_pkg.insert_row (
x_rowid => l_row_id
,x_balance_feed_id => l_balance_feed_id
,x_effective_start_date => p_effective_start_date
,x_effective_end_date => hr_api.g_eot
,x_business_group_id => p_business_group_id
,x_legislation_code => NULL
,x_balance_type_id => p_balance_type_id
,x_input_value_id => l_input_value_id
,x_scale => p_scale
,x_legislation_subgroup => NULL
);
SELECT eei_information18
FROM pay_element_type_extra_info
WHERE element_type_id = p_element_type_id
AND information_type = 'PQP_GB_PENSION_SCHEME_INFO';
SELECT classification_id
FROM pay_element_classifications
WHERE classification_name = p_classification_name
AND legislation_code = g_template_leg_code
AND business_group_id IS NULL;
pay_sub_class_rules_pkg.insert_row (
p_rowid => l_rowid
,p_sub_classification_rule_id => l_sub_class_rule_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => hr_api.g_eot
,p_element_type_id => p_element_type_id
,p_classification_id => l_classification_id
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_creation_date => SYSDATE
,p_created_by => l_user_id
,p_last_update_date => SYSDATE
,p_last_updated_by => l_user_id
,p_last_update_login => l_login_id
);
insert_validate;
pay_shadow_element_api.update_shadow_element (
p_validate => FALSE
,p_effective_date => p_effective_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_ele_name (i)
,p_reporting_name => p_ele_reporting_name
,p_description => p_ele_description
,p_classification_name => l_ele_class_name
,p_skip_formula => l_skip_formula
,p_third_party_pay_only_flag => p_third_party_only_flag
,p_iterative_flag => l_iterative_flag
,p_iterative_priority => l_iterative_priority
,p_iterative_formula_name => l_iterative_formula
,p_standard_link_flag => l_standard_link
,p_post_termination_rule => p_termination_rule
,p_object_version_number => l_ele_obj_ver_number
);
pay_shadow_element_api.update_shadow_element (
p_validate => FALSE
,p_effective_date => p_effective_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_ele_new_name (i)
,p_third_party_pay_only_flag => p_third_party_only_flag
,p_standard_link_flag => l_standard_link
,p_post_termination_rule => p_termination_rule
,p_object_version_number => l_ele_obj_ver_number
);
pay_shadow_element_api.update_shadow_element (
p_validate => FALSE
,p_effective_date => p_effective_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_ele_name (i)
,p_classification_name => l_ele_class_name
,p_third_party_pay_only_flag => p_third_party_only_flag
,p_iterative_flag => l_iterative_flag
,p_iterative_priority => l_iterative_priority
,p_iterative_formula_name => l_iterative_formula
,p_standard_link_flag => l_standard_link
,p_post_termination_rule => p_termination_rule
,p_object_version_number => l_ele_obj_ver_number
);
update_shadow_formula (
p_formula_id => l_ees_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_ers_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_ers_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_ers_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_ers_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_ees_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_ers_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_adl_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_ayr_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_fwd_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_shadow_formula (
p_formula_id => l_bb_fwc_cont_formula_id
,p_search_string => l_search_string
,p_replace_string => l_replace_string
);
update_ipval_defval (
p_ele_name => l_ele_name (i)
, -- base element name
p_ip_name => l_cont_iv_name
,p_def_value => l_iv_default_value
);
update_ipval_defval (
p_ele_name => l_ele_name (
i
) -- employer contribution %
,p_ip_name => 'Contribution Percent'
,p_def_value => g_tab_pension_types_info (
p_pension_type_id
).er_contribution_percent
);
update_ipval_defval (
p_ele_name => l_ele_name (
i
) -- employer contribution FR
,p_ip_name => 'Contribution Amount'
,p_def_value => g_tab_pension_types_info (
p_pension_type_id
).er_contribution_fixed_rate
);
g_tab_formula_ids.DELETE;
SELECT fnd_number.canonical_to_number (eei_information17)
FROM pay_element_type_extra_info
WHERE element_type_id = c_element_type_id
AND information_type = 'PQP_GB_PENSION_SCHEME_INFO';
SELECT 'X'
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM pay_element_type_extra_info eei
,pay_element_types_f pet
WHERE pet.element_type_id = eei.element_type_id
AND pet.business_group_id =
p_business_group_id
AND eei.information_type =
'PQP_GB_PENSION_SCHEME_INFO'
AND UPPER (eei.eei_information1) =
UPPER (p_pension_scheme_name)
AND eei.eei_information12 IS NULL);
g_tab_pension_types_info.DELETE;
PROCEDURE delete_user_template (
p_business_group_id IN NUMBER
,p_ele_base_name IN VARCHAR2
,p_element_type_id IN NUMBER
,p_effective_date IN DATE
)
IS
--
l_template_id NUMBER;
|| 'delete_user_template';
SELECT template_id
FROM pay_element_templates
WHERE base_name = p_ele_base_name
AND template_name = g_template_name
AND business_group_id = p_business_group_id
AND template_type = 'U';
SELECT element_type_id
FROM pay_template_core_objects pet, pay_element_types_f petf
WHERE pet.template_id = c_template_id
AND petf.element_type_id = pet.core_object_id
AND pet.core_object_type = 'ET';
SELECT element_type_extra_info_id
FROM pay_element_type_extra_info petei
WHERE element_type_id = c_element_type_id;
SELECT sub.ROWID, sub.sub_classification_rule_id
FROM pay_sub_classification_rules_f sub
,pay_element_classifications pec
WHERE element_type_id = p_element_type_id
AND sub.classification_id = pec.classification_id
AND pec.classification_name IN
('Pre Tax Employee Pension COSR'
,'Pre Tax Employee Pension COMP'
)
AND pec.legislation_code = g_template_leg_code;
SELECT iterative_rule_id, object_version_number
FROM pay_iterative_rules_f
WHERE element_type_id = c_element_type_id;
SELECT eei_information4, eei_information6
FROM pay_element_type_extra_info
WHERE element_type_id = p_element_type_id
AND information_type = 'PQP_GB_PENSION_SCHEME_INFO';
SELECT 'X'
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM pqp_gb_pension_schemes_v
WHERE ( associated_ocp_ele_type_id IS NOT NULL
AND associated_ocp_ele_type_id =
p_element_type_id
)
AND business_group_id = p_business_group_id);
SELECT 'X'
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM pay_element_templates
WHERE base_name = p_ele_base_name
|| ' Fixed'
AND template_name = g_template_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_sub_class_rules_pkg.delete_row (
p_rowid => l_rowid
,p_sub_classification_rule_id => l_sub_classification_rule_id
,p_delete_mode => 'ZAP'
,p_validation_start_date => p_effective_date
,p_validation_end_date => p_effective_date
);
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;
PROCEDURE delete_user_template_swi (
p_business_group_id IN NUMBER
,p_ele_base_name IN VARCHAR2
,p_element_type_id IN NUMBER
,p_effective_date IN DATE
)
IS
--
-- Variables for API Boolean parameters
l_validate BOOLEAN;
|| 'delete_user_template_swi';
SAVEPOINT delete_user_template_swi;
delete_user_template (
p_business_group_id => p_business_group_id
,p_ele_base_name => p_ele_base_name
,p_element_type_id => p_element_type_id
,p_effective_date => p_effective_date
);
ROLLBACK TO delete_user_template_swi;
ROLLBACK TO delete_user_template_swi;
END delete_user_template_swi;