[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ue.business_group_id
, nvl(ue.legislation_code, bg.legislation_code)
into l_business_group_id
, l_legislation_code
from ff_user_entities ue
, per_business_groups_perf bg
where ue.user_entity_id = p_user_entity_id
and bg.business_group_id (+)= ue.business_group_id
;
select /*+ INDEX(ue FF_USER_ENTITIES_N51) */ dbi.user_entity_id
, dbi.user_name
from ff_user_entities ue
, ff_database_items dbi
where ue.creator_type = 'S'
and ue.creator_id = p_global_id
and dbi.user_entity_id = ue.user_entity_id
;
select null into dummy from dual where exists
(select null
from ff_formulas_f a
where a.formula_name = p_item_name
and a.formula_type_id = p_formula_type_id
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select b.legislation_code
from per_business_groups_perf b
where b.business_group_id = a.business_group_id)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_grp
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_leg_code)
)
)
));
select 'X' into dummy from dual where exists
(select null
from ff_formulas_f a,
ff_fdi_usages_f b
where a.formula_id = b.formula_id
and (b.item_name = p_item_name or
b.alternative_item_name = p_item_name)
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_grp
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_leg_code)
)
)
));
select 'X' into dummy from dual where exists
(select null
from ff_fdi_usages_f b,
ff_formulas_f a
where b.alternative_item_name = p_tl_user_name
and a.formula_id = b.formula_id
and a.effective_start_date = b.effective_start_date
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = tmp_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
tmp_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = tmp_bg_id
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and
a.legislation_code = tmp_leg_code)
)
)
)
);
select b.formula_id
, a.formula_name
, b.effective_start_date
, b.effective_end_date
, a.business_group_id
, a.legislation_code
from ff_fdi_usages_f b,
ff_formulas_f a
where b.alternative_item_name = p_tl_user_name
and a.formula_id = b.formula_id
and a.effective_start_date = b.effective_start_date
and a.effective_end_date = b.effective_end_date
and
( p_startup_mode = 'MASTER'
or
( p_startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( p_startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_group_id
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and
a.legislation_code = p_leg_code)
)
)
)
union
select b.formula_id
, a.formula_name
, b.effective_start_date
, b.effective_end_date
, a.business_group_id
, a.legislation_code
from ff_fdi_usages_f b,
ff_formulas_f a
where b.item_name = p_tl_user_name
and a.formula_id = b.formula_id
and a.effective_start_date = b.effective_start_date
and a.effective_end_date = b.effective_end_date
and
( p_startup_mode = 'MASTER'
or
( p_startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( p_startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_group_id
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and
a.legislation_code = p_leg_code)
)
)
)
;
select 'X' into dummy from dual where exists
(select null
from ff_fdi_usages_f b,
ff_formulas_f a
where b.item_name = p_user_name
and a.formula_id = b.formula_id
and a.effective_start_date = b.effective_start_date
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = tmp_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
tmp_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = tmp_bg_id
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and
a.legislation_code = tmp_leg_code)
)
)
)
);
select 'X' into dummy from dual where exists
(select null
from ff_fdi_usages_f b,
ff_formulas_f a
where b.item_name = p_item_name
and b.usage <> 'D'
and a.formula_id = b.formula_id
and a.effective_start_date = b.effective_start_date
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_grp
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and
a.legislation_code = p_leg_code)
)
)
)
);
select null into dummy from dual where exists
(select /*+ ORDERED
INDEX(a FF_DATABASE_ITEMS_PK)
INDEX(b FF_USER_ENTITIES_PK) */ null
from ff_database_items a,
ff_user_entities b
where a.user_entity_id = b.user_entity_id
and a.user_name = p_item_name
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( b.legislation_code = p_leg_code
or
(b.legislation_code is null and b.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = b.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( b.business_group_id = p_bus_grp
or
(b.legislation_code is null and b.business_group_id is null)
or
(b.business_group_id is null and b.legislation_code = p_leg_code)
)
)
));
select null into dummy from dual where exists
(select /*+ ORDERED
INDEX(a FF_DATABASE_ITEMS_TL_N1)
INDEX(b FF_USER_ENTITIES_PK) */ null
from ff_database_items_tl a,
ff_user_entities b
where a.user_entity_id = b.user_entity_id
and a.translated_user_name = p_item_name
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( b.legislation_code = p_leg_code
or
(b.legislation_code is null and b.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = b.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( b.business_group_id = p_bus_grp
or
(b.legislation_code is null and b.business_group_id is null)
or
(b.business_group_id is null and b.legislation_code = p_leg_code)
)
)
));
select null into dummy from dual where exists
(select /*+ ORDERED
INDEX(a FF_DATABASE_ITEMS_PK)
INDEX(b FF_USER_ENTITIES_PK) */ null
from ff_database_items a,
ff_user_entities b
where a.user_name = p_tl_user_name
and a.user_entity_id <> p_user_entity_id
and a.user_entity_id = b.user_entity_id
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( b.legislation_code = p_legislation_code
or
(b.legislation_code is null and b.business_group_id is null)
or
p_legislation_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = b.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( b.business_group_id = p_business_group_id
or
(b.legislation_code is null and b.business_group_id is null)
or
(b.business_group_id is null and b.legislation_code = p_legislation_code)
)
)
));
select null into dummy from dual where exists
(select /*+ ORDERED
INDEX(a FF_DATABASE_ITEMS_TL_N1)
INDEX(b FF_USER_ENTITIES_PK) */ null
from ff_database_items_tl a,
ff_user_entities b
where a.translated_user_name = p_tl_user_name
and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
and a.user_entity_id = b.user_entity_id
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( b.legislation_code = p_legislation_code
or
(b.legislation_code is null and b.business_group_id is null)
or
p_legislation_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = b.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( b.business_group_id = p_business_group_id
or
(b.legislation_code is null and b.business_group_id is null)
or
(b.business_group_id is null and b.legislation_code = p_legislation_code)
)
)
));
select null into dummy from dual where exists
(select null
from ff_globals_f a
where a.global_name = p_item_name
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_grp
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_leg_code)
)
)
));
select null into dummy from dual where exists
(select null
from ff_globals_f a
, ff_globals_f_tl b
where b.global_name = p_item_name
and a.global_id = b.global_id
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_grp
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_leg_code)
)
)
));
select null into dummy from dual where exists
(select null
from ff_globals_f a
where a.global_name = p_item_name
and a.global_id <> p_global_id
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_grp
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_leg_code)
)
)
));
select null into dummy from dual where exists
(select null
from ff_globals_f a
, ff_globals_f_tl b
where b.global_name = p_item_name
and a.global_id = b.global_id
and a.global_id <> p_global_id
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_grp
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_leg_code)
)
)
));
select null into dummy from dual where exists
(select null
from ff_user_entities a
where a.user_entity_name = p_item_name
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_grp
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_leg_code)
)
)
));
select null into dummy from dual where exists
(select null
from ff_functions a
where
(
(a.name = p_item_name and a.class = p_class and p_class = 'U')
or
(a.alias_name = p_item_name)
)
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_leg_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_bus_grp
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_leg_code)
)
)
));
select 'X' into dummy from dual where exists
(select null from ff_contexts where context_name = upper(p_item_name));
select 'X'
from ff_formulas_f ff
where upper(ff.formula_name) = upper(p_formula_name)
and ff.formula_type_id = p_formula_type_id
-- bug 9187920 check should not be date effective (name is reserved)
--and p_effective_start_date between ff.effective_start_date
-- and ff.effective_end_date
and (p_startup_mode = 'MASTER'
or (p_startup_mode = 'SEED' and
(ff.legislation_code = p_leg_code
or
(ff.legislation_code is null and ff.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = ff.business_group_id
)
)
)
or (p_startup_mode = 'NON-SEED'
and (ff.business_group_id = p_bus_grp
or (ff.legislation_code is null
and ff.business_group_id is null)
or (ff.business_group_id is null
and ff.legislation_code = p_leg_code))));
select (min(ff.effective_start_date)-1)
from ff_formulas_f ff
where ff.formula_name = p_formula_name
and ff.formula_type_id = p_formula_type_id
and p_effective_end_date between ff.effective_start_date
and ff.effective_end_date
and (p_startup_mode = 'MASTER'
or (p_startup_mode = 'SEED' and
(ff.legislation_code = p_leg_code
or
(ff.legislation_code is null and ff.business_group_id is null)
or
p_leg_code =
(select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = ff.business_group_id
)
)
)
or (p_startup_mode = 'NON-SEED'
and (ff.business_group_id = p_bus_grp
or (ff.legislation_code is null
and ff.business_group_id is null)
or (ff.business_group_id is null
and ff.legislation_code = p_leg_code))));
select business_group_id, legislation_code
into bg_id, leg_code
from ff_user_entities
where user_entity_id = p_user_entity_id;
select 'X' into dummy from dual
where exists
(
select /*+ ORDERED
INDEX(C FF_USER_ENTITIES_FK1)
INDEX(B FF_DATABASE_ITEMS_FK1)
INDEX(A FF_FDI_USAGES_F_N50) */
null
from ff_user_entities c,
ff_database_items b,
ff_fdi_usages_f a
where a.item_name = b.user_name
and a.usage = 'D'
and b.user_entity_id = c.user_entity_id
and c.route_id = p_route_id
);
select business_group_id, legislation_code
into bg_id, leg_code
from ff_user_entities
where user_entity_id = p_user_entity_id;
select 'X' into dummy from dual
where exists
(
select /*+ ORDERED
INDEX(C FF_DATABASE_ITEMS_FK1)
INDEX(A FF_FDI_USAGES_F_N50)
INDEX(B FF_FORMULAS_F_PK) */ null
from ff_database_items c,
ff_fdi_usages_f a,
ff_formulas_f b
where a.formula_id = b.formula_id
and a.item_name = c.user_name
and a.usage = 'D'
and c.user_entity_id = p_user_entity_id
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( b.legislation_code = leg_code
or
(legislation_code is null and business_group_id is null)
or
leg_code =
(select d.legislation_code
from per_business_groups_perf d
where d.business_group_id = b.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( b.business_group_id = bg_id
or
(b.legislation_code is null and b.business_group_id is null)
or
(b.business_group_id is null and b.legislation_code = leg_code)
)
)
)
);
select creator_id
into l_route_id
from ff_user_entities
where creator_id = p_global_id
and creator_type = 'S';
select route_id
into l_route_id
from ff_routes
where route_name = 'FF_GLOBALS';
insert into ff_routes
(route_id,
route_name,
user_defined_flag,
description,
text,
created_by,
creation_date
)
values
(
ff_routes_s.nextval,
'FF_GLOBALS',
'N',
'Route for globals',
'ff_globals where global_id = &U1',
p_created_by,
p_creation_date
);
insert into ff_route_parameters
(route_parameter_id,
route_id,
data_type,
parameter_name,
sequence_no
)
values
(ff_route_parameters_s.nextval,
ff_routes_s.currval,
'T',
'GLOBAL_ID',
1
);
select ff_routes_s.currval, ff_route_parameters_s.currval
into l_route_id, l_route_parameter_id
from sys.dual;
select route_parameter_id
into l_route_parameter_id
from ff_route_parameters
where route_id = l_route_id;
insert into ff_user_entities
(user_entity_id,
business_group_id,
legislation_code,
route_id,
notfound_allowed_flag,
user_entity_name,
creator_id,
creator_type,
entity_description,
created_by,
creation_date
)
values
(ff_user_entities_s.nextval,
p_business_group_id,
p_legislation_code,
l_route_id,
'N',
p_name||'_GLOBAL_UE',
p_global_id,
'S',
'User entity for global '||p_name,
p_created_by,
p_creation_date
);
insert into ff_route_parameter_values
(route_parameter_id,
user_entity_id,
value,
created_by,
creation_date
)
values
(l_route_parameter_id,
ff_user_entities_s.currval,
p_global_id,
p_created_by,
p_creation_date
);
insert into ff_database_items
(user_name,
user_entity_id,
data_type,
definition_text,
null_allowed_flag,
description,
created_by,
creation_date
)
values
(p_name,
ff_user_entities_s.currval,
p_data_type,
decode(p_data_type,'D',
'FFFUNC.CD(DECODE(DATA_TYPE,''D'',GLOBAL_VALUE,NULL))',
'N',
'FFFUNC.CN(DECODE(DATA_TYPE,''N'',GLOBAL_VALUE,NULL))',
'GLOBAL_VALUE'),
'N',
'Database Item for '||p_name,
p_created_by,
p_creation_date
);
procedure delete_global_dbitem(p_global_id in number) is
begin
-- set error tracking information
hr_utility.set_location('ffdict.delete_global_dbitem',1);
delete from ff_user_entities
where creator_id = p_global_id
and creator_type = 'S';
end delete_global_dbitem;
procedure delete_ftcu_check(p_ftype_id in number,
p_context_id in number) is
dummy varchar2(1);
hr_utility.set_location('ffdict.delete_ftcu_check',1);
select 'X' into dummy from dual
where exists
(
select null from ff_formulas_f a,
ff_fdi_usages_f b,
ff_contexts c
where a.formula_type_id = p_ftype_id
and a.formula_id = b.formula_id
and b.item_name = upper(c.context_name)
and c.context_id = p_context_id
and b.usage = 'U'
);
end delete_ftcu_check;
procedure delete_dbitem_check(p_item_name in varchar2,
p_business_group_id in number,
p_legislation_code in varchar2) is
begin
-- set error tracking information
hr_utility.set_location('ffdict.delete_dbitem_check(2)',1);
end delete_dbitem_check;
procedure delete_dbitem_check(p_item_name in varchar2,
p_user_entity_id in number) is
begin
-- set error tracking information
hr_utility.set_location('ffdict.delete_dbitem_check',1);
end delete_dbitem_check;
procedure update_global_dbitem(p_global_id in number,
p_new_name in varchar2,
p_description in varchar2,
p_source_lang in varchar2,
p_language in varchar2) is
l_dbi_tl_name varchar2(2000);
ff_database_items_pkg.update_tl_row
(x_user_name => g_glb_dbi
,x_user_entity_id => g_glb_ueid
,x_language => p_language
,x_source_lang => p_source_lang
,x_translated_user_name => p_new_name
,x_description => p_description
);
end update_global_dbitem;
select 1
from ff_database_items dbi
where dbi.user_entity_id = p_ff_ue_id
and not exists
( select 1
from ff_user_entities ue
where ue.user_entity_id = p_ff_ue_id
);
delete from ff_database_items where user_entity_id= l_pur_del(i).user_entity_id;
delete from ff_route_parameter_values where user_entity_id= l_pur_del(i).user_entity_id;
l_pur_del.delete;
l_pur_del.delete;