[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'Fund Selected';
SELECT element_type_id
,object_version_number
FROM pay_shadow_element_types
WHERE template_id = p_te_unnddn_id
AND element_name = p_el_unnddn_nm;
SELECT pbt.balance_type_id
,pbt.object_version_number
FROM pay_balance_types pbt
WHERE pbt.balance_name = p_frm_union_level_balance
AND pbt.business_group_id = p_frm_business_group_id
AND (pbt.legislation_code IS NULL
OR
pbt.legislation_code = 'GB');
SELECT siv.input_value_id
,siv.object_version_number
FROM pay_shadow_input_values siv
WHERE siv.element_type_id = p_el_unnddn_id
AND siv.name = p_iv_payvlu_nm;
SELECT hou.organization_id
FROM hr_all_organization_units hou
WHERE hou.name = p_or_unnorg_nm
AND hou.business_group_id = p_bg_unnddn_id;
SELECT balance_category_id
FROM pay_balance_categories_f
WHERE category_name = c_category_name
AND legislation_code = 'GB'
AND p_frm_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT template_id
FROM pay_element_templates
WHERE template_name = l_te_unnddn_nm
AND legislation_code = p_legislation_code
AND template_type = 'T'
AND business_group_id is NULL;
SELECT lookup_code
,meaning
FROM hr_lookups hrl
WHERE hrl.lookup_type = p_fund_list
AND hrl.enabled_flag = 'Y';
SELECT user_column_id
FROM pay_user_columns
WHERE user_table_id = p_user_table_id
AND user_column_name = p_column_name;
pay_user_columns_pkg.insert_row (
p_rowid => l_column_rowid
,p_user_column_id => l_user_column_id
,p_user_table_id => p_ut_unnudt_id
,p_business_group_id => p_frm_business_group_id
,p_legislation_code => NULL
,p_legislation_subgroup => NULL
,p_user_column_name => l_lookup_value.meaning||' Weekly'
,p_formula_id => NULL
);
pay_user_columns_pkg.insert_row (
p_rowid => l_column_rowid
,p_user_column_id => l_user_column_id
,p_user_table_id => p_ut_unnudt_id
,p_business_group_id => p_frm_business_group_id
,p_legislation_code => NULL
,p_legislation_subgroup => NULL
,p_user_column_name => l_lookup_value.meaning||' Monthly'
,p_formula_id => NULL
);
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE dimension_name = p_dimension_name
AND ((business_group_id is null and legislation_code is null)
OR
(legislation_code is null and business_group_id + 0 =
p_frm_business_group_id)
OR
(business_group_id is null and legislation_code = 'GB'));
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_xx_object_nm
AND business_group_id = p_frm_business_group_id;
SELECT ptco.core_object_id
FROM pay_shadow_balance_types psbt,
pay_template_core_objects ptco
WHERE psbt.template_id = l_te_usrstr_id
AND psbt.balance_name = p_xx_object_nm
AND ptco.template_id = psbt.template_id
AND ptco.shadow_object_id = psbt.balance_type_id;
SELECT ptco.core_object_id
FROM pay_template_core_objects ptco
WHERE ptco.template_id = NVL(p_template_id,l_te_usrstr_id)
AND ptco.shadow_object_id = p_shadow_id
AND ptco.core_object_type = p_object_type;
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_frm_effective_start_date
,p_element_type_id => row_el_unnddn.element_type_id
,p_element_name => p_frm_element_name
,p_description => p_frm_description
,p_object_version_number => row_el_unnddn.object_version_number
,p_processing_type => p_frm_processing_type
,p_reporting_name => p_frm_reporting_name
);
pay_balance_types_pkg.insert_row
(X_Rowid => l_xx_rowid_id -- IN OUT VARCHAR2
,X_Balance_Type_Id => l_bl_core_id -- IN OUT NUMBER
,X_Business_Group_Id => p_frm_business_group_id -- NUMBER
,X_Legislation_Code => NULL -- VARCHAR2
,X_Currency_Code => 'GBP' -- VARCHAR2
,X_Assignment_Remuneration_Flag => 'N' -- VARCHAR2
,X_Balance_Name => p_frm_union_level_balance --VARCHAR2
,X_Base_Balance_Name => p_frm_union_level_balance --VARCHAR2
,X_Balance_Uom => 'M' --VARCHAR2
,X_Comments => 'Union level balance for '||
p_frm_union_name -- VARCHAR2
,X_Legislation_Subgroup => NULL -- VARCHAR2
,X_Reporting_Name => p_frm_union_level_balance --VARCHAR2
,X_Attribute_Category => NULL -- VARCHAR2
,X_Attribute1 => NULL -- VARCHAR2
,X_Attribute2 => NULL -- VARCHAR2
,X_Attribute3 => NULL -- VARCHAR2
,X_Attribute4 => NULL -- VARCHAR2
,X_Attribute5 => NULL -- VARCHAR2
,X_Attribute6 => NULL -- VARCHAR2
,X_Attribute7 => NULL -- VARCHAR2
,X_Attribute8 => NULL -- VARCHAR2
,X_Attribute9 => NULL -- VARCHAR2
,X_Attribute10 => NULL -- VARCHAR2
,X_Attribute11 => NULL -- VARCHAR2
,X_Attribute12 => NULL -- VARCHAR2
,X_Attribute13 => NULL -- VARCHAR2
,X_Attribute14 => NULL -- VARCHAR2
,X_Attribute15 => NULL -- VARCHAR2
,X_Attribute16 => NULL -- VARCHAR2
,X_Attribute17 => NULL -- VARCHAR2
,X_Attribute18 => NULL -- VARCHAR2
,X_Attribute19 => NULL -- VARCHAR2
,X_Attribute20 => NULL -- VARCHAR2
,X_balance_category_id => l_balance_category_id
);
pay_defined_balances_pkg.insert_row
(x_rowid => l_xx_rowid_id -- IN OUT VARCHAR2
,x_defined_balance_id => l_db_core_id -- IN OUT NUMBER
,x_business_group_id => p_frm_business_group_id --NUMBER
,x_legislation_code => NULL -- VARCHAR2
,x_balance_type_id => l_bl_core_id -- NUMBER
,x_balance_dimension_id => l_dm_baldmn_id -- NUMBER
,x_force_latest_balance_flag => NULL -- VARCHAR2
,x_legislation_subgroup => NULL -- VARCHAR2
,x_grossup_allowed_flag => 'N' -- VARCHAR2
);
pay_defined_balances_pkg.insert_row
(x_rowid => l_xx_rowid_id -- IN OUT VARCHAR2
,x_defined_balance_id => l_db_core_id -- IN OUT NUMBER
,x_business_group_id => p_frm_business_group_id --NUMBER
,x_legislation_code => NULL -- VARCHAR2
,x_balance_type_id => l_bl_core_id -- NUMBER
,x_balance_dimension_id => l_dm_baldmn_id -- NUMBER
,x_force_latest_balance_flag => NULL -- VARCHAR2
,x_legislation_subgroup => NULL -- VARCHAR2
,x_grossup_allowed_flag => 'N' -- VARCHAR2
);
pay_defined_balances_pkg.insert_row
(x_rowid => l_xx_rowid_id -- IN OUT VARCHAR2
,x_defined_balance_id => l_db_core_id -- IN OUT NUMBER
,x_business_group_id => p_frm_business_group_id --NUMBER
,x_legislation_code => NULL -- VARCHAR2
,x_balance_type_id => l_bl_core_id -- NUMBER
,x_balance_dimension_id => l_dm_baldmn_id -- NUMBER
,x_force_latest_balance_flag => NULL -- VARCHAR2
,x_legislation_subgroup => NULL -- VARCHAR2
,x_grossup_allowed_flag => 'N' -- VARCHAR2
);
pay_balance_feeds_f_pkg.insert_row
(x_rowid => l_xx_rowid_id --IN OUT VARCHAR2,
,x_balance_feed_id => l_bf_unnbal_id --IN OUT NUMBER,
,x_effective_start_date => p_frm_effective_start_date -- DATE,
,x_effective_end_date => l_frm_effective_end_date -- DATE,
,x_business_group_id => p_frm_business_group_id -- NUMBER,
,x_legislation_code => g_template_leg_code -- VARCHAR2,
,x_balance_type_id => l_bl_core_id -- NUMBER,
,x_input_value_id => l_iv_core_id -- NUMBER,
,x_scale => 1 -- NUMBER,
,x_legislation_subgroup => NULL -- VARCHAR2
);
PROCEDURE delete_user_template
(p_frm_union_name IN VARCHAR2
,p_frm_union_level_balance IN VARCHAR2
,p_frm_element_type_id IN NUMBER
,p_frm_element_name IN VARCHAR2
,p_frm_business_group_id IN NUMBER
,p_frm_effective_date IN DATE
) IS
--
l_proc VARCHAR2(61):= g_proc||'delete_user_template';
l_del_union_level_balance_yn VARCHAR2(1):= 'Y'; --Default delete the balance
SELECT element_type_extra_info_id
FROM pay_element_type_extra_info petei
WHERE element_type_id = p_frm_element_type_id;
SELECT template_id
FROM pay_element_templates
WHERE base_name = p_frm_element_name
AND business_group_id = p_frm_business_group_id
AND template_type = 'U';
SELECT usr_others.template_id
FROM pay_element_templates usr_this
,pay_element_templates usr_others
WHERE usr_this.template_id = p_te_usrstr_id
AND usr_others.template_name = usr_this.template_name
AND usr_others.template_type = 'U'
AND usr_others.template_id <> usr_this.template_id;
SELECT TO_NUMBER(peei.eei_information1) union_org_id
FROM pay_element_templates pets
,pay_shadow_element_types pset
,pay_template_core_objects ptco
,pay_element_type_extra_info peei
-- ,hr_all_organization_units horg
WHERE pets.template_id = p_te_usrstr_id -- For the given user structure
AND pset.template_id = pets.template_id -- find the base element
AND pset.element_name = pets.base_name
AND ptco.template_id = pset.template_id -- For the base element
AND ptco.shadow_object_id = pset.element_type_id -- find the core element
AND ptco.core_object_type = 'ET'
AND ptco.core_object_id = peei.element_type_id -- For the core element
AND peei.information_type = g_element_extra_info_type -- find the eei info
-- AND horg.organization_id = TO_NUMBER(peei.eei_information1)
-- AND horg.name = p_frm_union_name
;
SELECT horg.organization_id
FROM hr_all_organization_units horg
WHERE horg.organization_id = p_or_unnorg_id
AND horg.name = p_frm_union_name
AND ( horg.business_group_id = p_frm_business_group_id
OR horg.business_group_id IS NULL);
SELECT pbts.rowid
,pbts.balance_type_id
FROM pay_balance_types pbts
WHERE pbts.balance_name = p_frm_union_level_balance
AND pbts.business_group_id = p_frm_business_group_id
AND pbts.legislation_code IS NULL;
pay_balance_types_pkg.delete_row
(x_rowid => row_bt_unnbal.rowid -- VARCHAR2
,x_balance_type_id => row_bt_unnbal.balance_type_id -- NUMBER
);
pay_element_extra_info_api.delete_element_extra_info
(p_validate => FALSE
,p_element_type_extra_info_id => l_ee_unnddn_id
,p_object_version_number => l_ee_unnddn_ovn);
pay_element_template_api.delete_user_structure
(p_validate => FALSE
,p_drop_formula_packages => TRUE
,p_template_id => l_te_unnddn_id);
END delete_user_template;