[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 name,input_value_id,object_version_number
FROM pay_shadow_input_values
WHERE element_type_id= c_element_type_id
AND name IN ('Two Wheeler Type','User Rates Table');
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 delete_balance_feeds(l_sub_type IN VARCHAR2,l_name IN VARCHAR2)
is
l_reg_earn_input_value_id number;
SELECT input_value_id,
piv.name,
piv.element_type_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE element_name= p_ele_name||' '||lc_sub_type
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 pbt.BALANCE_TYPE_ID
FROM pay_balance_types pbt
WHERE pbt.BALANCE_NAME = p_balance_name
AND pbt.lEGISLATION_CODE = 'GB'
AND (pbt.legislation_code='GB' OR pbt.legislation_code IS NULL);
SELECT balance_feed_id
FROM pay_balance_feeds
WHERE input_value_id=p_input
AND balance_type_id=p_bal_type_id
AND (business_group_id =p_bg_id OR business_group_id IS NULL)
AND (legislation_code='GB' OR legislation_code IS NULL);
P_delete_mode => 'DELETE' ,
P_balance_feed_id => c3_rec.balance_feed_id ,
P_input_value_id => c1_rec.input_value_id ,
P_element_type_id => c1_rec.element_type_id ,
P_primary_classification_id => NULL ,
P_sub_classification_id => NULL ,
P_sub_classification_rule_id => NULL ,
P_balance_type_id => c2_rec.BALANCE_TYPE_ID ,
P_session_date => p_ele_eff_start_date ,
P_effective_end_date => p_ele_eff_start_date ,
P_legislation_code => NULL,
P_mode => 'USER');
END delete_balance_feeds;*/
CURSOR c1 IS SELECT input_value_id,piv.name,
element_name
FROM pay_input_values piv,
pay_element_types pet
WHERE element_name IN ( p_ele_name||' NIable'
)
AND piv.element_type_id=pet.element_type_id
AND piv.business_group_id =p_bg_id
AND piv.name IN ('Pay Value');
SELECT pbt.BALANCE_TYPE_ID
FROM pay_balance_types pbt
WHERE pbt.BALANCE_NAME = p_balance_name
AND pbt.lEGISLATION_CODE = 'GB';
SELECT input_value_id,
piv.name,
piv.element_type_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id =p_ele_type
AND piv.element_type_id = pet.element_type_id
AND piv.business_group_id = p_bg_id
AND piv.name =p_inputname;
SELECT formula_id
FROM ff_formulas
WHERE FORMULA_name=p_formula_name
AND p_ele_eff_start_date
BETWEEN effective_start_date
AND effective_end_date
AND legislation_code='GB';
SELECT ffvs.flex_value_set_id
FROM fnd_flex_value_sets ffvs
WHERE flex_value_set_name = 'PQP_PURPOSE_LIST';
UPDATE pay_input_values_f
SET value_set_id=c3_rec.flex_value_set_id,
warning_or_error='W'
WHERE input_value_id=c1_rec.input_value_id
AND element_type_id=p_ele_type;
UPDATE pay_input_values_f
SET formula_id=c2_rec.formula_id,
warning_or_error='E'
WHERE input_value_id=c1_rec.input_value_id
AND element_type_id=p_ele_type;
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_description => p_ele_description
,p_reporting_name =>p_ele_reporting_name
,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_lumptemp
,p_object_version_number => l_ele_obj_ver_number
);
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) :='pay_uk_vehicle_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;