[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM hxc_absence_type_elements_temp;
INSERT INTO hxc_absence_type_elements_temp
(absence_attendance_type_id
,absence_attendance_type_name
,element_type_id
,element_name
,uom
,alias_value_name
,absence_category
,absence_category_meaning
)
(SELECT /*+ ORDERED */
paatt.absence_attendance_type_id,
paatt.name,
petft.element_type_id,
petft.element_name,
hae.uom,
paatt.name || ' (' || ntl.meaning || ')',
acl.lookup_code absence_category,
acl.meaning absence_category_meaning
FROM hxc_absence_type_elements hae,
per_absence_attendance_types paat,
per_abs_attendance_types_tl paatt,
pay_element_types_f petf,
pay_element_types_f_tl petft,
hr_lookups ntl, -- NAME_TRANSLATIONS
hr_lookups acl -- ABSENCE_CATEGORY
WHERE hae.absence_attendance_type_id = paat.absence_attendance_type_id
AND hae.element_type_id = petf.element_type_id
AND ntl.lookup_type = 'NAME_TRANSLATIONS'
AND ntl.lookup_code = hae.uom
AND acl.lookup_type(+) = 'ABSENCE_CATEGORY'
AND acl.lookup_code(+) = hae.absence_category
AND paatt.LANGUAGE = petft.LANGUAGE
AND paatt.LANGUAGE = userenv('LANG')
AND paatt.absence_attendance_type_id = paat.absence_attendance_type_id
AND petft.element_type_id = petf.element_type_id
AND paat.business_group_id = petf.business_group_id
AND paat.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'));
DELETE hxc_absence_type_elements_temp haet
WHERE to_char(haet.element_type_id) NOT IN
(SELECT hav.attribute1
FROM hxc_alias_definitions_tl hadt,
hxc_alias_definitions had,
hxc_alias_values hav
WHERE had.alias_context_code = 'PAYROLL_ELEMENTS'
AND hadt.alias_definition_id = had.alias_definition_id
AND hadt.alias_definition_id = hav.alias_definition_id
AND hadt.alias_definition_id = l_tc_abs_type_alias_def_id
AND hadt.LANGUAGE = userenv('LANG'));
DELETE hxc_absence_type_elements_temp haet
WHERE haet.element_type_id NOT IN
(SELECT pelf.element_type_id
FROM pay_payrolls_f papf,
pay_element_links_f pelf
WHERE papf.payroll_id = pelf.payroll_id
AND papf.payroll_id = l_tc_abs_payroll_id);
DELETE hxc_absence_type_elements_temp haet
WHERE haet.element_type_id NOT IN
(SELECT petr.element_type_id
FROM pay_element_sets_tl pest,
pay_element_sets pes,
pay_element_type_rules petr
WHERE pest.element_set_id = pes.element_set_id
AND pes.element_set_id = petr.element_set_id
AND pes.element_set_id = l_tc_abs_element_set_id
AND pes.element_set_type = 'R'
AND LANGUAGE = userenv('LANG'));
DELETE hxc_absence_type_elements_temp haet
WHERE haet.element_type_id NOT IN
(SELECT pivf.element_type_id
FROM per_abs_attendance_types_tl paatt,
per_absence_attendance_types paat,
pay_input_values_f pivf
WHERE paatt.absence_attendance_type_id = paat.absence_attendance_type_id
AND paat.absence_attendance_type_id = l_tc_abs_absence_type_id
AND paat.input_value_id = pivf.input_value_id
AND paatt.LANGUAGE = userenv('LANG'));
SELECT alias_type_id
INTO l_alias_type_id
FROM hxc_alias_types
WHERE alias_type = 'OTL_ALT_DDF'
AND reference_object = 'PAYROLL_ELEMENTS';
SELECT 'Y'
INTO l_exists
FROM hxc_alias_definitions_tl
WHERE alias_definition_name = l_alias_definition_name
AND LANGUAGE = userenv('LANG');
SELECT 'Y'
INTO p_alias_value_name_exists
FROM hxc_alias_values_tl havt,
hxc_alias_values hav
WHERE substr(havt.alias_value_name,1,instr(havt.alias_value_name,'(')-2)
= substr(l_alias_value_name,1,instr(havt.alias_value_name,'(')-2)
AND havt.alias_value_id = hav.alias_value_id
AND hav.alias_definition_id = p_alias_definition_id
AND havt.LANGUAGE = userenv('LANG');
SELECT hav.alias_value_id,
hav.object_version_number
INTO l_alias_value_id,
l_alias_value_id_ovn
FROM hxc_alias_values_tl havt,
hxc_alias_values hav
WHERE substr(havt.alias_value_name,1,instr(havt.alias_value_name,'(')-2)
= substr(l_alias_value_name,1,instr(havt.alias_value_name,'(')-2)
AND havt.alias_value_id = hav.alias_value_id
AND hav.alias_definition_id = p_alias_definition_id
AND havt.LANGUAGE = userenv('LANG');
hr_utility.trace('ABS:Calling hxc_alias_values_api.update_alias_value procedure.');
hxc_alias_values_api.update_alias_value(p_alias_value_id => l_alias_value_id,
p_alias_value_name => l_alias_value_name,
p_alias_definition_id => p_alias_definition_id,
p_enabled_flag => 'Y',
p_attribute_category => 'PAYROLL_ELEMENTS',
p_attribute1 => p_attribute1,
p_date_from => to_date(p_date_from,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')),
p_date_to => to_date(p_date_to,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')),
p_object_version_number => l_alias_value_id_ovn);
hr_utility.trace('ABS:End of hxc_alias_values_api.update_alias_value procedure.');
SELECT 'Y'
INTO l_exists
FROM hxc_time_categories
WHERE time_category_name = p_time_category_name;
select mapping_component_id
into p_component_type_id
from hxc_mapping_components
where field_name = 'Dummy Element Context';
select mapping_component_id
into l_component_type_id
from hxc_mapping_components
where field_name = 'Dummy Element Context';
SELECT 'Y'
INTO p_time_category_comp_exists
FROM hxc_time_category_comps
WHERE time_category_id = p_time_category_id
AND value_id = p_value_id;
SELECT time_category_comp_id,
object_version_number
INTO l_time_category_comp_id,
l_time_category_comp_ovn
FROM hxc_time_category_comps
WHERE time_category_id = p_time_category_id
AND value_id = p_value_id;
hr_utility.trace('ABS:Calling hxc_time_category_comp_api.update_time_category_comp procedure.');
hxc_time_category_comp_api.update_time_category_comp
(p_time_category_comp_id => l_time_category_comp_id
,p_object_version_number => l_time_category_comp_ovn
,p_time_category_id => p_time_category_id
,p_ref_time_category_id => null
,p_component_type_id => l_component_type_id
,p_flex_value_set_id => -1
,p_value_id => p_value_id
,p_is_null => 'N'
,p_equal_to => 'Y'
,p_type => 'MC' );
hr_utility.trace('ABS:Completed hxc_time_category_comp_api.update_time_category_comp procedure.');