[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
business_group_id,
rule_set_id,
rule_set_name,
organization_structure_id,
organization_id,
referenced_rule_set_id,
rule_level_cd,
object_version_number,
short_name,
rule_applicability,
rule_category,
starting_organization_id,
seeded_rule_flag,
status
from pqh_rule_sets
where rule_set_id = p_rule_set_id;
select business_group_id,
rule_set_id,
rule_set_name,
organization_structure_id,
organization_id,
referenced_rule_set_id,
rule_level_cd,
object_version_number,
short_name,
rule_applicability,
rule_category,
starting_organization_id,
seeded_rule_flag,
status
from pqh_rule_sets
where rule_set_id = p_rule_set_id
for update nowait;
,p_last_update_date in varchar2
) is
--
l_effective_date date := sysdate ;
l_last_updated_by pqh_rule_sets.last_updated_by%TYPE;
l_last_update_date pqh_rule_sets.last_update_date%TYPE;
l_last_update_login pqh_rule_sets.last_update_login%TYPE;
cursor c1 is select userenv('LANG') from dual ;
select rule_set_id
from pqh_rule_sets
where short_name = p_short_name ;
Cursor C_Sel1 is select pqh_rule_sets_s.nextval from sys.dual;
l_last_updated_by := -1;
l_last_updated_by := -1;
l_last_updated_by := fnd_load_util.owner_id(p_owner);
l_last_update_date := sysdate;
l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
l_last_update_login := 0;
update pqh_rule_sets
set
rule_set_name = p_rule_set_name,
short_name = p_short_name,
referenced_rule_set_id = l_referenced_rule_set_id,
rule_level_cd = p_rule_level_cd,
rule_category = p_rule_category,
rule_applicability = p_rule_applicability,
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login,
seeded_rule_flag = 'Y'
where rule_set_id = l_rule_set_id
and nvl(last_updated_by, -1) in (l_last_updated_by,-1,0,1);
UPDATE pqh_rule_sets_tl
SET rule_set_name = p_rule_set_name,
description = p_description,
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login,
source_lang = userenv('LANG')
WHERE rule_set_id = l_rule_set_id
AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
insert into pqh_rule_sets_tl(
rule_set_id,
rule_set_name,
description,
language,
source_lang,
created_by,
creation_date,
last_updated_by,
last_update_date ,
last_update_login
)
Select
l_rule_set_id,
p_rule_set_name,
p_description,
l.language_code,
l_language ,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login
from fnd_languages l
where l.installed_flag in ('I','B')
and not exists (select null
from pqh_rule_sets_tl rtl
where rtl.rule_set_id = l_rule_set_id
and rtl.language = l.language_code );
insert into pqh_rule_sets(
rule_set_id,
rule_set_name,
short_name,
referenced_rule_set_id,
rule_level_cd,
rule_category,
rule_applicability,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date ,
last_update_login,
seeded_rule_flag
)
Values(
l_rule_set_id,
p_rule_set_name,
p_short_name,
l_referenced_rule_set_id,
p_rule_level_cd,
p_rule_category,
p_rule_applicability,
l_object_version_number,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
'Y'
);
insert into pqh_rule_sets_tl(
rule_set_id,
rule_set_name,
description,
language,
source_lang,
created_by,
creation_date,
last_updated_by,
last_update_date ,
last_update_login
)
Select
l_rule_set_id,
p_rule_set_name,
p_description,
l.language_code,
l_language ,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login
from fnd_languages l
where l.installed_flag in ('I','B')
and not exists (select null
from pqh_rule_sets_tl rtl
where rtl.rule_set_id = l_rule_set_id
and rtl.language = l.language_code );
,p_last_update_date in varchar2
) is
--
l_data_migrator_mode varchar2(1);
,p_last_update_date => p_last_update_date);
delete from PQH_RULE_SETS_TL T
where not exists
(select NULL
from PQH_RULE_SETS B
where B.RULE_SET_ID = T.RULE_SET_ID
);
update PQH_RULE_SETS_TL T set (
RULE_SET_NAME
) = (select
B.RULE_SET_NAME
from PQH_RULE_SETS_TL B
where B.RULE_SET_ID = T.RULE_SET_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.RULE_SET_ID,
T.LANGUAGE
) in (select
SUBT.RULE_SET_ID,
SUBT.LANGUAGE
from PQH_RULE_SETS_TL SUBB, PQH_RULE_SETS_TL SUBT
where SUBB.RULE_SET_ID = SUBT.RULE_SET_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.RULE_SET_NAME <> SUBT.RULE_SET_NAME
));
insert into PQH_RULE_SETS_TL (
RULE_SET_ID,
RULE_SET_NAME,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LANGUAGE,
SOURCE_LANG
) select
B.RULE_SET_ID,
B.RULE_SET_NAME,
B.LAST_UPDATE_DATE,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
B.LAST_UPDATED_BY,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from PQH_RULE_SETS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from PQH_RULE_SETS_TL T
where T.RULE_SET_ID = B.RULE_SET_ID
and T.LANGUAGE = L.LANGUAGE_CODE);