[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT username
FROM all_users
WHERE username NOT IN
('SYS','SYSTEM', 'APPLSYS', 'APPLSYSPUB', 'APPS_READ_ONLY')
AND username = p_table_rec.schema;
SELECT object_name
FROM all_objects
WHERE owner = p_table_rec.schema
AND object_type IN ('TABLE','VIEW')
AND object_name NOT IN
( select name from cn_obj_tables_v )
and object_name = p_table_rec.name;
IF (p_operation = 'INSERT') THEN
OPEN l_schema_csr;
G_LAST_UPDATE_DATE DATE := Sysdate;
G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
SELECT repository_id
FROM cn_repositories
WHERE repository_id > 0
AND org_id=p_table_rec.org_id
AND application_type = 'CN';
check_table_rec(p_table_rec, 'INSERT');
select cn_objects_s1.nextval
into l_count
from dual;
SELECT nvl(p_table_rec.object_id,cn_objects_s.nextval)
INTO l_object_id
FROM dual;
PROCEDURE Update_Table
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER :=
FND_API.G_VALID_LEVEL_FULL ,
p_table_rec IN OUT NOCOPY table_rec_type ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_api_name CONSTANT VARCHAR2(30)
:= 'Update_Table';
G_LAST_UPDATE_DATE DATE := Sysdate;
G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
SELECT *
FROM cn_obj_tables_v
WHERE table_id = p_table_rec.object_id
and org_id=p_table_rec.ORG_ID;
SELECT repository_id
FROM cn_repositories
WHERE repository_id > 0
and org_id=p_table_rec.ORG_ID
AND application_type = 'CN';
SAVEPOINT update_table_pvt;
/* seeded tables can be updated
if (p_table_rec.object_id < 0) then
fnd_message.set_name('CN', 'CN_SD_TBL_NO_UPD');
check_table_rec(p_table_rec, 'UPDATE');
P_OPERATION => 'UPDATE'
, P_OBJECT_ID => p_table_rec.object_id
, P_NAME => l_row.name
, P_DESCRIPTION => p_table_rec.description
, P_DEPENDENCY_MAP_COMPLETE => 'N'
, P_STATUS => 'A'
, P_REPOSITORY_ID => l_repository_id
, P_ALIAS => l_row.alias
, P_TABLE_LEVEL => NULL
, P_TABLE_TYPE => 'T'
, P_OBJECT_TYPE => 'TBL'
, P_SCHEMA => l_row.schema
, P_CALC_ELIGIBLE_FLAG => p_table_rec.calc_eligible_flag
, P_USER_NAME => p_table_rec.user_name
, p_data_length => NULL
, p_data_type => NULL
, p_calc_formula_flag => NULL
, p_table_id => NULL
, p_column_datatype => NULL
, x_object_version_number =>p_table_rec.object_version_number
, p_org_id =>p_table_rec.ORG_ID
);
ROLLBACK TO update_table_pvt;
ROLLBACK TO update_table_pvt;
ROLLBACK TO update_table_pvt;
END Update_Table;
PROCEDURE Delete_Table
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER :=
FND_API.G_VALID_LEVEL_FULL ,
p_table_rec IN table_rec_type ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_api_name CONSTANT VARCHAR2(30)
:= 'Delete_Table';
G_LAST_UPDATE_DATE DATE := Sysdate;
G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
SAVEPOINT delete_table_pvt;
SELECT COUNT(*)
INTO l_dest_count
FROM cn_table_maps_all
WHERE source_table_id = p_table_rec.object_id
and org_id=p_table_rec.org_id;
SELECT COUNT(*)
INTO l_sorc_count
FROM cn_table_maps_all
WHERE destination_table_id = p_table_rec.object_id
and org_id=p_table_rec.org_id;
DELETE FROM cn_obj_columns_v
WHERE table_id = p_table_rec.object_id;
DELETE FROM cn_obj_tables_v
WHERE table_id = p_table_rec.object_id;
ROLLBACK TO delete_table_pvt;
ROLLBACK TO delete_table_pvt;
ROLLBACK TO delete_table_pvt;
END Delete_Table;
PROCEDURE Update_Column
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER :=
FND_API.G_VALID_LEVEL_FULL ,
p_column_rec IN column_rec_type ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_api_name CONSTANT VARCHAR2(30)
:= 'Update_Column';
G_LAST_UPDATE_DATE DATE := Sysdate;
G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
SELECT cotv.table_id, cotv.name
FROM cn_obj_columns_v cocv, cn_obj_tables_v cotv
WHERE cocv.column_id= p_column_rec.object_id
AND cocv.table_id = cotv.table_id
AND cocv.org_id=p_column_rec.org_id AND
cocv.org_id=cotv.org_id;
SELECT count(user_column_name)
FROM cn_obj_columns_v
WHERE table_id = l_table_id
AND user_column_name = 'Y'
AND org_id=p_column_rec.org_id
AND column_id <> p_column_rec.object_id;
SELECT d.dimension_id
FROM cn_dimensions d, cn_dimension_tables_v dt
WHERE d.dimension_id = dt.dimension_id
AND org_id=p_column_rec.org_id
AND upper(dt.table_name) = l_table_name;
SELECT distinct ruleset_id
FROM cn_attribute_rules
WHERE column_id = p_object_id
AND org_id=p_column_rec.org_id
and dimension_hierarchy_id is null;
SELECT object_id, column_datatype,org_id
FROM cn_objects
WHERE object_id = p_object_id
AND org_id=p_column_rec.org_id
AND table_id = -11803;
SAVEPOINT update_column_pvt;
UPDATE cn_obj_columns_v
SET calc_formula_flag = p_column_rec.usage,
user_name = p_column_rec.user_name,
foreign_key = p_column_rec.foreign_key,
dimension_id = p_column_rec.dimension_id,
user_column_name = p_column_rec.user_column_name,
classification_column = p_column_rec.classification_column,
column_datatype = p_column_rec.column_datatype,
value_set_id = p_column_rec.value_set_id,
primary_key = p_column_rec.primary_key,
position = p_column_rec.position,
custom_call = p_column_rec.custom_call
WHERE column_id = p_column_rec.object_id
AND org_id=p_column_rec.org_id;
ROLLBACK TO update_column_pvt;
ROLLBACK TO update_column_pvt;
ROLLBACK TO update_column_pvt;
END Update_Column;
PROCEDURE Insert_Column
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER :=
FND_API.G_VALID_LEVEL_FULL ,
p_schema_name IN varchar2 ,
p_table_name IN varchar2 ,
p_column_name IN varchar2 ,
p_column_rec IN column_rec_type ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_api_name CONSTANT VARCHAR2(30)
:= 'Insert_Column';
G_LAST_UPDATE_DATE DATE := Sysdate;
G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
SELECT *
FROM cn_objects
WHERE name = p_table_name
AND schema = p_schema_name
AND org_id=p_column_rec.org_id
AND object_type = 'TBL';
SELECT count(*)
FROM all_tab_columns
WHERE owner = p_schema_name
AND table_name = p_table_name
AND column_name = p_column_name
AND NOT EXISTS
(SELECT name
FROM cn_objects
WHERE table_id = p_table_id
AND name = p_column_name
AND org_id=p_column_rec.org_id
AND object_type = 'COL');
SELECT data_type, data_length
FROM all_tab_columns
WHERE owner = p_schema_name
AND table_name = p_table_name
AND column_name = p_column_name
AND data_type IN
('CHAR','NCHAR','VARCHAR2','VARCHAR','NVARCHAR2','LONG','NUMBER','DATE');
SAVEPOINT insert_column_pvt;
SELECT cn_objects_s.nextval
INTO l_column_id
FROM dual;
P_OPERATION => 'INSERT',
P_OBJECT_ID => l_column_id,
P_NAME => p_column_name,
P_DESCRIPTION => l_table_rec.description,
P_DEPENDENCY_MAP_COMPLETE => 'N',
P_STATUS => 'A',
P_REPOSITORY_ID => l_table_rec.repository_id,
P_ALIAS => l_table_rec.ALIAS,
P_TABLE_LEVEL => NULL,
P_TABLE_TYPE => NULL,
P_OBJECT_TYPE => 'COL',
P_SCHEMA => l_table_rec.schema,
P_CALC_ELIGIBLE_FLAG => l_table_rec.calc_eligible_flag,
P_USER_NAME => p_column_name,
p_data_type => l_data_type,
p_data_length => l_data_len,
p_calc_formula_flag => 'N',
p_table_id => l_table_rec.object_id,
p_column_datatype => l_column_data_type,
x_object_version_number => l_object_version_number,
p_org_id => p_column_rec.org_id);
update_column
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_column_rec => insert_column.p_column_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ROLLBACK TO insert_column_pvt;
ROLLBACK TO insert_column_pvt;
ROLLBACK TO insert_column_pvt;
END Insert_Column;
PROCEDURE Delete_Column
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER :=
FND_API.G_VALID_LEVEL_FULL ,
p_column_id IN number ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_api_name CONSTANT VARCHAR2(30)
:= 'Delete_Column';
G_LAST_UPDATE_DATE DATE := Sysdate;
G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
CURSOR l_delete_csr ( p_column_id number ) IS
SELECT table_id
FROM cn_obj_columns_v
WHERE column_id = p_column_id
AND object_type = 'COL';
SAVEPOINT delete_column_pvt;
open l_delete_csr( p_column_id );
fetch l_delete_csr into l_table_id;
close l_delete_csr;
DELETE FROM cn_obj_columns_v
WHERE column_id = p_column_id
AND object_type = 'COL';
ROLLBACK TO delete_column_pvt;
ROLLBACK TO delete_column_pvt;
ROLLBACK TO delete_column_pvt;
END Delete_Column;