[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
attribute_id,
attribute_name,
master_attribute_id,
master_table_route_id,
column_name,
column_type,
enable_flag,
width,
object_version_number,
region_itemname,
attribute_itemname,
decode_function_name
from pqh_attributes
where attribute_id = p_attribute_id;
select attribute_id,
attribute_name,
master_attribute_id,
master_table_route_id,
column_name,
column_type,
enable_flag,
width,
object_version_number,
region_itemname,
attribute_itemname,
decode_function_name
from pqh_attributes
where attribute_id = p_attribute_id
for update nowait;
,p_last_update_date in varchar2
,p_owner in varchar2
) is
--
l_data_migrator_mode varchar2(1);
,p_last_update_date => p_last_update_date
,p_owner => p_owner );
,p_last_update_date in varchar2
,p_owner in varchar2
) is
--
--
l_effective_date date := sysdate ;
l_delete_attr_ranges_flag varchar2(30) := 'N';
l_last_updated_by pqh_attributes.last_updated_by%TYPE;
l_last_update_date pqh_attributes.last_update_date%TYPE;
l_last_update_login pqh_attributes.last_update_login%TYPE;
cursor c1 is select userenv('LANG') from dual ;
select attribute_id,last_update_date
from pqh_attributes
where key_column_name = p_key_column_name
and nvl(legislation_code,'$$$') = nvl(p_legislation_code,'$$$')
and nvl(master_table_route_id,-999) = nvl(p_table_id, -999);
select table_route_id
from pqh_table_route
where table_alias = p_table_alias;
l_last_upd_in_db pqh_attributes.last_update_date%TYPE;
l_dummy pqh_attributes.last_update_date%TYPE;
l_last_updated_by := -1;
l_last_update_date := sysdate;
l_last_update_login := 0;
l_last_updated_by := fnd_load_util.owner_id(p_owner);
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));
If l_last_update_date > l_last_upd_in_db then
UPDATE pqh_attributes
SET attribute_name = p_attribute_name,
master_attribute_id = l_master_attribute_id,
master_table_route_id = l_master_table_route_id,
key_column_name = p_key_column_name,
column_name = nvl(p_column_name,p_key_column_name),
column_type = p_column_type,
enable_flag = p_enable_flag,
width = p_width,
refresh_col_name = p_refresh_col_name,
legislation_code = p_legislation_code,
region_itemname = p_region_itemname,
attribute_itemname = p_attribute_itemname,
decode_function_name = p_decode_function_name,
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
WHERE attribute_id = l_attribute_id ;
UPDATE pqh_attributes_tl
SET attribute_name = p_attribute_name,
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 attribute_id = l_attribute_id
AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
insert into pqh_attributes_tl
( attribute_id,
attribute_name,
language,
source_lang,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by
)
Select
l_attribute_id,
p_attribute_name,
L.LANGUAGE_CODE,
userenv('LANG'),
l_creation_date,
l_created_by,
l_last_update_date,
l_last_update_login,
l_last_updated_by
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from pqh_attributes_tl T
where T.attribute_id = l_attribute_id
and T.LANGUAGE = L.LANGUAGE_CODE);
select pqh_attributes_s.nextval into l_attribute_id from dual;
INSERT INTO pqh_attributes
(attribute_id ,
attribute_name,
master_attribute_id,
master_table_route_id ,
key_column_name,
column_name,
column_type,
enable_flag,
width,
refresh_col_name,
legislation_code,
region_itemname,
attribute_itemname,
decode_function_name,
created_by,
creation_date,
last_updated_by,
last_update_date ,
last_update_login,
object_version_number)
VALUES
(l_attribute_id,
p_attribute_name,
l_master_attribute_id,
l_master_table_route_id,
p_key_column_name,
nvl(p_column_name,p_key_column_name),
p_column_type,
p_enable_flag,
p_width,
p_refresh_col_name,
p_legislation_code,
p_region_itemname,
p_attribute_itemname,
p_decode_function_name,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1 );
insert into pqh_attributes_tl
( attribute_id,
attribute_name,
language,
source_lang,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by
)
Select
l_attribute_id,
p_attribute_name,
L.LANGUAGE_CODE,
userenv('LANG'),
l_creation_date,
l_created_by,
l_last_update_date,
l_last_update_login,
l_last_updated_by
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from pqh_attributes_tl T
where T.attribute_id = l_attribute_id
and T.LANGUAGE = L.LANGUAGE_CODE);
delete from PQH_ATTRIBUTES_TL T
where not exists
(select NULL
from PQH_ATTRIBUTES B
where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
);
update PQH_ATTRIBUTES_TL T set (
ATTRIBUTE_NAME
) = (select
B.ATTRIBUTE_NAME
from PQH_ATTRIBUTES_TL B
where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ATTRIBUTE_ID,
T.LANGUAGE
) in (select
SUBT.ATTRIBUTE_ID,
SUBT.LANGUAGE
from PQH_ATTRIBUTES_TL SUBB, PQH_ATTRIBUTES_TL SUBT
where SUBB.ATTRIBUTE_ID = SUBT.ATTRIBUTE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.ATTRIBUTE_NAME <> SUBT.ATTRIBUTE_NAME
));
insert into PQH_ATTRIBUTES_TL (
ATTRIBUTE_ID,
ATTRIBUTE_NAME,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LANGUAGE,
SOURCE_LANG
) select
B.ATTRIBUTE_ID,
B.ATTRIBUTE_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_ATTRIBUTES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from PQH_ATTRIBUTES_TL T
where T.ATTRIBUTE_ID = B.ATTRIBUTE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);