[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION check_delete_info_type(p_info_type_basis VARCHAR2)
return NUMBER is
cursor c_delete(p_info_type_basis in VARCHAR2) is
select bbi.bld_blk_info_type_id
from hxc_mapping_comp_usages mcu,
hxc_mapping_components mc,
hxc_bld_blk_info_types bbi
where mcu.mapping_component_id = mc.mapping_component_id
and mc.bld_blk_info_type_id = bbi.bld_blk_info_type_id
and upper(bld_blk_info_type) like '%'||p_info_type_basis||'%';
open c_delete(p_info_type_basis);
fetch c_delete into l_bld_blk_info_type_id;
if c_delete%NOTFOUND then
l_bld_blk_info_type_id := NULL;
close c_delete;
END check_delete_info_type;
SELECT 'Y'
from hxc_mapping_comp_usages u,
hxc_mappings m
where m.name = p_name
and u.mapping_id = m.mapping_id
and u.mapping_component_id in
(select c2.mapping_component_id
from hxc_mapping_components c1,
hxc_mapping_components c2
where c1.mapping_component_id = p_id
and c2.segment = c1.segment
and c2.field_name = c1.field_name
and c2.bld_blk_info_type_id = c1.bld_blk_info_type_id
and c2.mapping_component_id <> c1.mapping_component_id);
select 'Y'
from hxc_mappings m, hxc_mapping_comp_usages u, hxc_mapping_components c
where m.mapping_id = u.mapping_id
and m.name = p_name
and u.mapping_component_id = c.mapping_component_id
and c.field_name =
(select field_name
from hxc_mapping_components c1
where c1.mapping_component_id = p_id);
select mapping_id
from hxc_mappings m
where m.name = p_mapping_process_name;
select mc.mapping_component_id
from hxc_mapping_components mc,
hxc_bld_blk_info_types bbit
where upper(bbit.bld_blk_info_type) like '%'||upper(p_info_type_basis)||'%'
and bbit.bld_blk_info_type_id = mc.bld_blk_info_type_id;
select mc.mapping_component_id
from hxc_mapping_components mc,
hxc_bld_blk_info_types bbit
where upper(bbit.bld_blk_info_type) like '%'||upper(p_info_type_basis)||'%'
and bbit.bld_blk_info_type_id = mc.bld_blk_info_type_id;
DELETE from hxc_mapping_comp_usages
where mapping_component_id = map_rec.mapping_component_id;
DELETE from HXC_MAPPING_COMPONENTS
where mapping_component_id = map_rec.mapping_component_id;
select bbu.bld_blk_info_type_usage_id
from hxc_bld_blk_info_type_usages bbu,
hxc_bld_blk_info_types bbit
where upper(bbit.bld_blk_info_type) like '%'||upper(p_info_type_basis)||'%'
and bbit.bld_blk_info_type_id = bbu.bld_blk_info_type_id;
DELETE from HXC_BLD_BLK_INFO_TYPE_USAGES
where bld_blk_info_type_usage_id = usage_rec.bld_blk_info_type_usage_id;
select bld_blk_info_type
from hxc_bld_blk_info_types
where upper(bld_blk_info_Type) like '%'||upper(p_flex_context_basis)||'%';
FND_FLEX_DSC_API.delete_context(
APPL_SHORT_NAME => p_appl_short_name,
FLEXFIELD_NAME => p_flexfield_name,
CONTEXT => context_rec.bld_blk_info_type);
DELETE from HXC_BLD_BLK_INFO_TYPES
where upper(bld_blk_info_type) like '%'||upper(p_bld_blk_info_type_basis)||'%';
select c.descriptive_flex_context_code, c.descriptive_flex_context_name
from fnd_descr_flex_contexts_vl c,
fnd_application a
where c.descriptive_flexfield_name = p_flex_name
and a.application_short_name = p_appl_short_name
and a.application_id = c.application_id
and c.descriptive_flex_context_code <> 'Global Data Elements';
insert into hxc_pref_definitions
(PREF_DEFINITION_ID,
CODE,
DESCRIPTIVE_FLEXFIELD_NAME)
select
hxc_pref_definitions_s.nextval,
context_rec.descriptive_flex_context_code,
p_flex_name
from dual
where not exists(
select 'Y'
from hxc_pref_definitions
where code = context_rec.descriptive_flex_context_code
and descriptive_flexfield_name = p_flex_name);
SELECT
1
FROM
hxc_absence_type_elements
WHERE
element_type_id = p_element_type_id;
The logic would be to first delete the records with the
present element type ids and then insert it.
*/
if g_debug then
hr_utility.trace('Entered REMOVE_HXC_ABS_ELEM_INFO');
delete from hxc_absence_type_elements -- hxc_absence_type_elements
where element_type_id=p_element_type_id;
select pet.element_type_id
from pay_element_types_f pet,
pay_element_type_rules per
where per.element_set_id = p_element_set_id
and per.include_or_exclude = 'I'
and per.element_type_id = pet.element_type_id
and multiple_entries_allowed_flag = 'Y'
and p_effective_date between effective_start_date and effective_end_date;
l_bld_blk_info_type_id := check_delete_info_type(l_basis_string);
Call to delete any existing absence info in hxc_absence_type_elements
*/
-- change start
remove_hxc_abs_elem_info(p_err_msg,ele_rec.element_type_id);
l_bld_blk_info_type_id := check_delete_info_type(l_key_flex_code);
select substr(id_flex_structure_code,1,20) into l_prefix
from fnd_id_flex_structures
where id_flex_code = p_flex_code
and id_flex_num = p_flex_num;
select 'Y'
from fnd_id_flex_segments
where id_flex_code = p_id_flex_code
and id_flex_num = p_id_flex_num
and segment_name = p_seg_name
and application_id = p_application_id;
select mapping_component_id,
object_version_number
into p_mp_id,
p_ovn
from hxc_mapping_components
where field_name = p_field_name
and name = p_name
and segment = p_segment;
FUNCTION update_allowed
(p_map_comp_id in HXC_MAPPING_COMPONENTS.MAPPING_COMPONENT_ID%TYPE) RETURN BOOLEAN is
l_dummy VARCHAR2(2);
select 'Y'
into l_dummy
from hxc_mapping_comp_usages
where mapping_component_id = p_map_comp_id
and rownum =1;
END update_allowed;
elsif (update_allowed(l_mapping_component_id)) then
hxc_mapping_component_api.update_mapping_component(
p_validate => FALSE,
p_mapping_component_id => l_mapping_component_id,
p_object_version_number => l_ovn,
p_name => p_name,
p_field_name => p_field_name,
p_bld_blk_info_type_id => p_bld_blk_info_type_id,
p_segment => p_segment);
select bld_blk_info_type_id
from HXC_BLD_BLK_INFO_TYPES
where bld_blk_info_type = p_info_type;
insert into HXC_BLD_BLK_INFO_TYPES(
bld_blk_info_type_id,
legislation_code,
descriptive_flexfield_name,
bld_blk_info_type,
multiple_occurences_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
select
HXC_BLD_BLK_INFO_TYPES_S.NEXTVAL,
p_legislation_code,
p_flexfield_name,
p_bld_blk_info_type,
'N',
0,
sysdate,
0,
sysdate,
0,
1
from sys.dual;
insert into HXC_BLD_BLK_INFO_TYPE_USAGES(
bld_blk_info_type_usage_id,
building_block_category,
bld_blk_info_type_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
select
hxc_bld_blk_info_type_usages_s.nextval,
p_category,
l_bld_blk_info_type_id,
0,
sysdate,
0,
sysdate,
0,
1
from sys.dual
where not exists(
select 'Y'
from hxc_bld_blk_info_type_usages
where bld_blk_info_type_id = l_bld_blk_info_type_id);
select fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE
from fnd_descr_flex_contexts fdfc
where descriptive_flexfield_name = p_flex_name
and fdfc.descriptive_flex_context_code <> 'Global Data Elements'
and not exists (
select 'Y'
from hxc_bld_blk_info_types bbi
where bbi.bld_blk_info_type = fdfc.descriptive_flex_context_code
);
FND_FLEX_DSC_API.delete_context(
APPL_SHORT_NAME => p_appl_short_name,
FLEXFIELD_NAME => p_flexfield_name,
CONTEXT => 'Dummy '||initcap(p_context_name)||' Context');
select max(to_number(substrB(DESCRIPTIVE_FLEX_CONTEXT_CODE,
instr(DESCRIPTIVE_FLEX_CONTEXT_CODE,'-')+2)))
FROM fnd_descr_flex_contexts_vl
WHERE descriptive_flexfield_name = 'OTC Information Types'
AND application_id = 809
AND substrB(DESCRIPTIVE_FLEX_CONTEXT_CODE,0,
instr(DESCRIPTIVE_FLEX_CONTEXT_CODE,'-')-2)
=substrB(DESCRIPTIVE_FLEX_CONTEXT_name,0,
instr(DESCRIPTIVE_FLEX_CONTEXT_name,'-')-2)||'C';
FND_FLEX_DSC_API.delete_context
(APPL_SHORT_NAME => p_otc_appl_short_name,
FLEXFIELD_NAME => p_otc_flex_name,
CONTEXT => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30));
fnd_flex_dsc_api.delete_context
(appl_short_name=> p_otc_appl_short_name,
flexfield_name=> p_otc_flex_name,
CONTEXT=> l_sequence_code
);
FND_FLEX_DSC_API.delete_context
(APPL_SHORT_NAME => p_otc_appl_short_name,
FLEXFIELD_NAME => p_otc_flex_name,
CONTEXT => substr(p_context_prefix||' - GLOBAL',1,30)
);
Call to insert absence info from the element set to hxc_absence_type_elements
*/
--change start
PROCEDURE INSERT_INTO_HXC_ABSENCES(p_error_msg OUT NOCOPY VARCHAR2,
p_abs_info IN hxc_create_flex_mappings.hxc_abs_tab_type)
IS
CURSOR chk_abs_elem_exists(p_absence_attendance_type_id IN NUMBER,
p_element_type_id IN NUMBER)
--p_uom IN VARCHAR2,
--p_absence_category IN VARCHAR2)
IS
SELECT
1
FROM
hxc_absence_type_elements
WHERE
absence_attendance_type_id = p_absence_attendance_type_id AND
element_type_id = p_element_type_id;
The logic would be to first delete the records with the
present element type ids and den insert it.
*/
if g_debug then
hr_utility.trace('Entered INSERT_INTO_HXC_ABSENCES');
hr_utility.trace('Inserting');
insert into hxc_absence_type_elements -- hxc_absence_type_elements
(
ABSENCE_ATTENDANCE_TYPE_ID,
ELEMENT_TYPE_ID,
EDIT_FLAG,
UOM,
ABSENCE_CATEGORY
)
VALUES
(
p_abs_info(tab_count).ABSENCE_ATTENDANCE_TYPE_ID,
p_abs_info(tab_count).ELEMENT_TYPE_ID,
p_abs_info(tab_count).EDIT_FLAG,
p_abs_info(tab_count).UOM,
p_abs_info(tab_count).ABSENCE_CATEGORY
);
UPDATE hxc_absence_type_elements
SET UOM = p_abs_info(tab_count).UOM,
ABSENCE_CATEGORY = p_abs_info(tab_count).ABSENCE_CATEGORY
WHERE absence_attendance_type_id = p_abs_info(tab_count).ABSENCE_ATTENDANCE_TYPE_ID
AND element_type_id = p_abs_info(tab_count).ELEMENT_TYPE_ID;
END; -- insert_into_hxc_absence
select hat.alias_type_id
from hxc_alias_types hat
where reference_object = p_alias_context_code;
select fdfc.descriptive_flex_context_name
from fnd_descr_flex_contexts_vl fdfc
where application_id = 809
and DESCRIPTIVE_FLEXFIELD_NAME = 'OTC Aliases'
and fdfc.descriptive_flex_context_code = p_alias_context_code;
select pet.element_name, pet.element_type_id, pet.reporting_name
from pay_element_types_f pet,
pay_element_type_rules per
where per.element_set_id = p_element_set_id
and per.include_or_exclude = 'I'
and per.element_type_id = pet.element_type_id
and multiple_entries_allowed_flag = 'Y'
and p_effective_date between effective_start_date and effective_end_date;
select 'Y', alias_definition_id
from hxc_alias_definitions
where alias_definition_name = p_an_name
and business_group_id = p_bg_id;
select 'Y'
from hxc_alias_values
where alias_definition_id = p_an_id
and attribute1 = to_char(p_ele_type_id);
select display_sequence, name, input_value_id, mandatory_flag
from pay_input_values_f
where element_type_id = p_element_type_id
and p_effective_date between effective_start_date and effective_end_date
order by display_sequence, name;
select count(*)
from hxc_alias_values
where alias_definition_id = p_an_id
AND (alias_value_name like '% ~ '||p_ele_rep_name or alias_value_name = p_ele_rep_name);
select
pat.absence_attendance_type_id,
pet.element_type_id,
'N' EDIT_FLAG,
decode(piv.UOM, 'ND', 'DAYS'
, 'H_H','HOURS'
, 'H_DECIMAL1','HOURS'
, 'H_DECIMAL2','HOURS'
, 'H_DECIMAL3','HOURS'
, 'H_HHMM','HOURS'
, NULL) UOM,
pat.absence_category
from
pay_element_types_f pet ,
pay_input_values_f piv,
per_absence_attendance_types pat,
pay_element_type_rules per
where
per.element_set_id= p_element_set_id AND
per.include_or_exclude = 'I' AND
per.element_type_id=pet.element_type_id AND
pet.multiple_entries_allowed_flag='Y' AND
p_effective_date between
nvl(pet.effective_start_date,hr_general.start_of_time)
and
nvl(pet.effective_end_date,hr_general.end_of_time) AND
piv.element_type_id=pet.element_type_id AND
p_effective_date between
nvl(piv.effective_start_date,hr_general.start_of_time)
and
nvl(piv.effective_end_date ,hr_general.end_of_time) AND
pat.input_value_id=piv.input_value_id AND
p_effective_date between
nvl(pat.date_effective,hr_general.start_of_time)
and
nvl(pat.date_end ,hr_general.end_of_time);
SELECT element_set_name, business_group_id
INTO l_element_set_name, l_bg_id
FROM pay_element_sets
WHERE element_set_id = p_element_set_id;
FND_FLEX_DSC_API.delete_context(
APPL_SHORT_NAME => l_appl_short_name,
FLEXFIELD_NAME => l_flexfield_name,
CONTEXT => 'ELEMENT - '|| ele_rec.element_type_id);
Insertion into hxc_absence_type_elements
*/
-- change start
IF (p_incl_abs_flg = 'Y' and hxc_abs_tab.COUNT>0) THEN
insert_into_hxc_absences (p_error_msg => l_err_msg,
p_abs_info => hxc_abs_tab);
If the context exists, delete it and recreate otherwise just create it
*/
if FND_FLEX_DSC_API.context_exists(
P_APPL_SHORT_NAME => l_appl_short_name,
P_FLEXFIELD_NAME => l_flexfield_name,
P_CONTEXT_CODE => l_key_flex_code||' - '
||to_char(l_key_structure_list(l_structure_count))
) then
FND_FLEX_DSC_API.delete_context(
APPL_SHORT_NAME => l_appl_short_name,
FLEXFIELD_NAME => l_flexfield_name,
CONTEXT => l_key_flex_code||' - '
||to_char(l_key_structure_list(l_structure_count)));