[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 pbt.balance_type_id
,pbt.object_version_number
FROM pay_balance_types pbt
WHERE pbt.balance_name = p_professional_body_level_bal
AND pbt.business_group_id = p_bg_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 = c_element_type_id
AND siv.name = c_inputvalue_name;
SELECT hou.organization_id
FROM hr_all_organization_units hou
WHERE hou.name = c_org_name
AND (hou.business_group_id = p_bg_id OR
hou.business_group_id is null);
SELECT balance_category_id
FROM pay_balance_categories_f
WHERE category_name = c_category_name
AND legislation_code = 'GB'
AND p_ele_eff_start_date BETWEEN effective_start_date
AND effective_end_date;
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;
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_bg_id)
OR
(business_group_id is null and legislation_code = 'GB'));
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;
SELECT ptco.core_object_id
FROM pay_template_core_objects ptco
WHERE ptco.template_id = l_template_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_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_processing_type => p_ele_processing_type
,p_third_party_pay_only_flag => p_ele_third_party_payment
,p_object_version_number => l_ele_obj_ver_number
);
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_bg_id -- NUMBER
,X_Legislation_Code => NULL -- VARCHAR2
,X_Currency_Code => 'GBP' -- VARCHAR2
,X_Assignment_Remuneration_Flag => 'N' -- VARCHAR2
,X_Balance_Name => p_professional_body_level_bal -- VARCHAR2
,X_Base_Balance_Name => p_professional_body_level_bal -- VARCHAR2
,X_Balance_Uom => 'M' -- VARCHAR2
,X_Comments => 'Professional body Level balance for '||
p_professional_body_name -- VARCHAR2
,X_Legislation_Subgroup => NULL -- VARCHAR2
,X_Reporting_Name => p_professional_body_level_bal -- 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_bg_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_pbdbal_id -- IN OUT NUMBER,
,x_effective_start_date => p_ele_eff_start_date -- DATE,
,x_effective_end_date => p_ele_eff_end_date -- DATE,
,x_business_group_id => p_bg_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_professional_body_name in varchar2
,p_professional_body_level_bal in varchar2
,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_professional_body_temp.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';
SELECT usr_others.template_id
FROM pay_element_templates usr_this
,pay_element_templates usr_others
WHERE usr_this.template_id = c_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) pbd_org_id
FROM pay_element_templates pets
,pay_shadow_element_types pset
,pay_template_core_objects ptco
,pay_element_type_extra_info peei
WHERE pets.template_id = c_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 = 'PQP_PROFESSIONAL_BODY_INFO' -- find the eei info
;
SELECT horg.organization_id
FROM hr_all_organization_units horg
WHERE horg.organization_id = c_pb_orgid
AND horg.name = p_professional_body_name
AND ( horg.business_group_id = p_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_professional_body_level_bal
AND pbts.business_group_id = p_business_group_id
AND pbts.legislation_code IS NULL;
pay_balance_types_pkg.delete_row
(x_rowid => csr_get_pb_balid_rec.rowid -- VARCHAR2
,x_balance_type_id => csr_get_pb_balid_rec.balance_type_id -- NUMBER
);
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;