[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 name
FROM CN_OBJECTS
WHERE object_id = x_object_id
AND org_id = x_org_id;
p_table_map_rec.last_update_date := SYSDATE;
SELECT count(1)
INTO l_count
FROM cn_table_maps
WHERE mapping_type = p_table_map_rec.mapping_type
AND org_id = l_org_id;
SELECT repository_id, module_id
INTO l_application_repository_id, l_parent_module_id
FROM cn_modules
WHERE module_type = 'COL'
AND org_id = l_org_id;
SELECT cn_events_s.NEXTVAL
INTO l_event_id
FROM dual;
cn_events_all_pkg.insert_row (
x_rowid => l_rowid,
x_event_id => l_event_id,
x_application_repository_id => l_application_repository_id,
x_description => NULL,
x_name => p_source_name,
x_creation_date => p_table_map_rec.creation_date,
x_created_by => p_table_map_rec.created_by,
x_last_update_date => p_table_map_rec.last_update_date,
x_last_updated_by => p_table_map_rec.last_updated_by,
x_last_update_login => p_table_map_rec.last_update_login,
x_org_id => p_table_map_rec.org_id);
SELECT cn_modules_s.NEXTVAL
INTO p_table_map_rec.module_id
FROM dual;
cn_modules_pkg.insert_row (
x_rowid => l_rowid,
x_module_id => p_table_map_rec.module_id,
x_module_type => p_table_map_rec.mapping_type,
x_repository_id => l_application_repository_id,
x_description => NULL,
x_parent_module_id => l_parent_module_id,
x_source_repository_id => NULL,
x_module_status => 'UNSYNC',
x_event_id => l_event_id,
x_last_modification => SYSDATE,
x_last_synchronization => NULL,
x_output_filename => NULL,
x_collect_flag => 'YES',
x_name => p_source_name,
x_creation_date => p_table_map_rec.creation_date,
x_created_by => p_table_map_rec.created_by,
x_last_update_date => p_table_map_rec.last_update_date,
x_last_updated_by => p_table_map_rec.last_updated_by,
x_last_update_login => p_table_map_rec.last_update_login,
x_org_id => p_table_map_rec.org_id);
SELECT table_id
INTO p_table_map_rec.source_table_id
FROM CN_OBJECTS
WHERE object_type = 'COL'
AND object_id = p_table_map_rec.source_tbl_pkcol_id
AND org_id = p_table_map_rec.org_id;
SELECT object_id
INTO p_table_map_rec.destination_table_id
FROM cn_objects
WHERE name = 'CN_COMM_LINES_API'
AND org_id = p_table_map_rec.org_id;
IF p_table_map_rec.delete_flag IS NULL THEN
p_table_map_rec.delete_flag := 'N';
cn_table_maps_pkg.insert_row(
x_rowid => l_rowid,
x_table_map_id => p_table_map_rec.table_map_id, -- autocreated if left null
x_mapping_type => p_table_map_rec.mapping_type,
x_module_id => p_table_map_rec.module_id,
x_source_table_id => p_table_map_rec.source_table_id,
x_source_tbl_pkcol_id => p_table_map_rec.source_tbl_pkcol_id,
x_destination_table_id => p_table_map_rec.destination_table_id,
x_source_hdr_tbl_pkcol_id => p_table_map_rec.source_hdr_tbl_pkcol_id,
x_source_tbl_hdr_fkcol_id => p_table_map_rec.source_tbl_hdr_fkcol_id,
x_notify_where => p_table_map_rec.notify_where,
x_collect_where => p_table_map_rec.collect_where,
x_delete_flag => p_table_map_rec.delete_flag,
x_creation_date => p_table_map_rec.creation_date,
x_created_by => p_table_map_rec.created_by,
x_org_id => p_table_map_rec.org_id);
(SELECT *
FROM cn_column_maps
WHERE table_map_id = -999
AND org_id = l_org_id) -- MOAC Need To Verify
LOOP
l_column_map_id := NULL; -- set inside the procedure
cn_column_maps_pkg.insert_row (
x_rowid => l_rowid,
x_column_map_id => l_column_map_id,
x_destination_column_id => rec.destination_column_id,
x_table_map_id => p_table_map_rec.table_map_id,
x_expression => rec.expression,
x_editable => rec.editable,
x_modified => rec.modified,
x_update_clause => rec.update_clause,
x_calc_ext_table_id => rec.calc_ext_table_id,
--x_creation_date => p_table_map_rec.creation_date,
x_creation_date => SYSDATE,
x_created_by => p_table_map_rec.created_by,
x_org_id => p_table_map_rec.org_id);
FND_MESSAGE.SET_NAME('CN', 'CN_TRANS_SRC_ATTR_INSERT_NOTE');
FND_MESSAGE.SET_NAME('CN', 'CN_TRANS_SRC_ATTR_INSERT_NOTE');
FND_MESSAGE.SET_NAME('CN', 'CN_TRANS_SRC_ATTR_INSERT_NOTE');
FND_MESSAGE.SET_NAME('CN', 'CN_TRANS_SRC_ATTR_INSERT_NOTE');
PROCEDURE Delete_Map
( 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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_table_map_id IN NUMBER,
p_org_id IN NUMBER -- Added For R12 MOAC
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Map';
SELECT * FROM cn_table_maps_v
WHERE table_map_id = p_table_map_id
AND org_id = p_org_id; -- Added For MOAC
SELECT object_id
FROM cn_objects
WHERE object_type = 'PKS'
AND name = 'cn_collect_'||LOWER(p_mapping_type)||p_org_append
AND org_id = p_org_id; -- Added For MOAC
SELECT object_id
FROM cn_objects
WHERE object_type = 'PKB'
AND name = 'cn_collect_'||LOWER(p_mapping_type)||p_org_append
AND org_id = p_org_id; -- Added For MOAC
SAVEPOINT Delete_Map;
--+ Cannot delete seeded table map
--+
IF p_table_map_id < 0 THEN
RAISE FND_API.G_EXC_ERROR;
--+ Delete Event
--+
cn_events_all_pkg.delete_row (x_event_id => l_table_map_rec.event_id,
x_org_id => p_org_id); -- Added For R12 MOAC
--+ Delete Module
--+
cn_modules_pkg.delete_row (x_module_id => l_table_map_rec.module_id,
x_org_id => p_org_id); -- Added For R12 MOAC
--+ Delete Table Map
--+
cn_table_maps_pkg.delete_row(x_table_map_id => p_table_map_id,
x_org_id => p_org_id); -- Added For R12 MOAC
--+ Delete column-mapping mapping rows
--+
DELETE FROM cn_column_maps
WHERE table_map_id = p_table_map_id
AND org_id = p_org_id; -- Added For R12 MOAC
DELETE FROM cn_source WHERE object_id IN (l_pkg_spec_id, l_pkg_body_id);
DELETE FROM cn_objects WHERE object_id IN (l_pkg_spec_id, l_pkg_body_id);
-- Delete Table Map Objects
--+
FOR rec IN
(SELECT table_map_object_id,
UPPER(tm_object_type) tm_object_type,
object_id
FROM cn_table_map_objects
WHERE table_map_id = p_table_map_id
AND org_id = p_org_id) -- Added For R12 MOAC
LOOP
IF rec.tm_object_type IN ('PARAM','FILTER','PKS','PKB') THEN
--+
-- Object belongs exclusively to Collections and can be deleted
--+
delete_table_map_Object (
p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_table_map_object_id => rec.table_map_object_id,
x_org_id => p_org_id); -- Added For R12 MOAC
-- delete that.
--+
cn_table_map_objects_pkg.delete_row(rec.table_map_object_id,p_org_id);
ROLLBACK TO Delete_Map;
ROLLBACK TO Delete_Map;
ROLLBACK TO Delete_Map;
END Delete_Map;
PROCEDURE Update_Table_Map_Objects
(
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_table_map_id IN NUMBER,
p_delete_flag IN VARCHAR2,
p_object_name IN VARCHAR2,
p_object_id IN NUMBER,
p_object_value IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_org_id IN NUMBER) -- Added For R12 MOAC
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Table_Map_Objects';
SELECT object_version_number
FROM cn_objects
WHERE object_id = p_object_id
AND org_id = x_org_id; -- Added For R12 MOAC
SELECT object_version_number
FROM cn_table_maps
WHERE table_map_id = p_table_map_id
AND org_id = x_org_id; -- Added For R12 MOAC
SAVEPOINT update_trx_source_sv;
IF p_delete_flag = 'Y'
THEN
UPDATE cn_table_maps
SET delete_flag = p_delete_flag,
object_version_number = l_ovn_tbl_number + 1
WHERE table_map_id = p_table_map_id
AND org_id = l_org_id;
UPDATE CN_OBJECTS
SET NAME = p_object_name,
OBJECT_VALUE = p_object_value
WHERE OBJECT_ID = p_object_id
AND org_id = l_org_id;
ROLLBACK TO update_trx_source_sv;
ROLLBACK TO update_trx_source_sv;
ROLLBACK TO update_trx_source_sv;
END Update_Table_Map_Objects;
PROCEDURE Update_Map
(
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_table_map_id IN NUMBER,
p_mapping_type IN VARCHAR2,
p_module_id IN NUMBER,
p_source_table_id IN NUMBER,
p_source_tbl_pkcol_id IN NUMBER,
p_destination_table_id IN NUMBER,
p_source_hdr_tbl_pkcol_id IN NUMBER,
p_source_tbl_hdr_fkcol_id IN NUMBER,
p_notify_where IN VARCHAR2,
p_collect_where IN VARCHAR2,
p_delete_flag IN VARCHAR2,
p_event_id IN NUMBER,
p_event_name IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_org_id IN NUMBER) -- Added For R12 MOAC
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_trx_source';
SELECT object_version_number
FROM cn_table_maps
WHERE table_map_id = p_table_map_id
AND org_id = x_org_id; -- Added For R12 MOAC
SAVEPOINT update_trx_source_sv;
cn_table_maps_pkg.update_row(x_table_map_id=> p_table_map_id,
x_mapping_type => p_mapping_type,
x_module_id => p_module_id,
x_source_table_id => p_source_table_id,
x_source_tbl_pkcol_id => p_source_tbl_pkcol_id,
x_destination_table_id => p_destination_table_id,
x_source_hdr_tbl_pkcol_id => p_source_hdr_tbl_pkcol_id,
x_source_tbl_hdr_fkcol_id => p_source_tbl_hdr_fkcol_id,
x_notify_where => p_notify_where,
x_collect_where => p_collect_where,
x_delete_flag => p_delete_flag,
x_last_update_date => G_LAST_UPDATE_DATE,
x_last_updated_by => G_LAST_UPDATED_BY,
x_last_update_login => g_last_update_login,
x_object_version_number => p_object_version_number,
x_org_id => l_org_id); -- Added For R12 MOAC
cn_events_all_pkg.update_row
(x_event_id => p_event_id,
x_application_repository_id => 100,
x_description => NULL,
x_name => p_event_name,
x_last_update_date => G_LAST_UPDATE_DATE,
x_last_updated_by => G_LAST_UPDATED_BY,
x_last_update_login => G_LAST_UPDATE_LOGIN,
x_org_id => l_org_id,
p_object_version_number => l_evn_object_version_number); -- Added For R12 MOAC
ROLLBACK TO update_trx_source_sv;
ROLLBACK TO update_trx_source_sv;
ROLLBACK TO update_trx_source_sv;
END Update_Map;
SELECT repository_id
INTO l_application_repository_id
FROM cn_modules_all_b
WHERE module_type = 'COL' -- name = 'Collection';
SELECT cn_objects_s.NEXTVAL
INTO x_object_id
FROM dual;
cn_objects_pkg.insert_row(
x_rowid => l_rowid,
x_object_id => x_object_id,
x_dependency_map_complete => 'N',
x_name => p_object_name,
x_object_value => p_object_value,
x_description => 'Custom Data Source Collection Object',
x_object_type => UPPER(p_tm_object_type),
x_repository_id => l_application_repository_id,
x_next_synchronization_date => NULL,
x_synchronization_frequency => NULL,
x_object_status => 'A',
X_org_id => l_org_id);
cn_table_map_objects_pkg.insert_row(
x_rowid => l_rowid,
x_table_map_object_id => x_table_map_object_id, --set inside procedure
x_tm_object_type => UPPER(p_tm_object_type),
x_table_map_id => p_table_map_id,
x_object_id => x_object_id,
x_creation_date => p_creation_date,
x_created_by => p_created_by,
X_org_id => l_org_id);
PROCEDURE Delete_Table_Map_Object (
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_table_map_object_id IN NUMBER,
x_org_id IN NUMBER) IS -- Added For R12 MOAC
l_api_name CONSTANT VARCHAR2(30) := 'Create_Map';
SELECT object_id
FROM cn_table_map_objects
WHERE table_map_object_id = p_table_map_object_id
AND org_id = x_org_id;
SAVEPOINT Delete_Table_Map_Object;
DELETE FROM cn_objects WHERE object_id = del.object_id;
cn_table_map_objects_pkg.delete_row(
x_table_map_object_id => p_table_map_object_id,
x_org_id => x_org_id); -- Added For R12 MOAC
ROLLBACK TO Delete_Table_Map_Object;
ROLLBACK TO Delete_Table_Map_Object;
ROLLBACK TO Delete_Table_Map_Object;
END Delete_Table_Map_Object;
SELECT
tmv.table_map_id,
LOWER(tmv.source_table_name) line_tab_name,
LOWER(NVL(tmv.source_table_alias,tmv.source_table_name)) line_tab_alias,
LOWER(tmv.linepk_name) line_pk_col,
LOWER(tmv.linefk_name) line_fk_col,
LOWER(tmv.header_table_name) hdr_tab_name,
LOWER(NVL(tmv.header_table_alias,tmv.header_table_name)) hdr_tab_alias,
LOWER(tmv.hdrpk_name) hdr_pk_col
FROM
cn_table_maps_v tmv
WHERE
tmv.table_map_id = p_table_map_id
AND tmv.org_id = p_org_id;
(SELECT LOWER(obj.name||' '||NVL(obj.alias,obj.name)) name
FROM cn_table_map_objects tmobj,
cn_objects obj
WHERE tmobj.table_map_id = l_c1_rec.table_map_id
AND tmobj.tm_object_type = 'COLLTAB'
AND obj.object_id = tmobj.object_id
AND obj.org_id = p_org_id AND obj.org_id = tmobj.org_id)
LOOP
x_collect_from := x_collect_from||fnd_global.local_chr(10)||rec.name||',';