The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT AMS_DM_TARGETS_B_s.NEXTVAL
FROM dual;
SELECT 1
FROM AMS_DM_TARGETS_VL
WHERE TARGET_ID = l_id;
AMS_UTILITY_PVT.debug_message( l_api_name || ' New Target ID to Insert = ' || l_dm_target_rec.TARGET_ID );
AMS_DM_TARGETS_B_PKG.Insert_Row(
px_target_id => l_dm_target_rec.target_id,
p_last_update_date => SYSDATE,
p_last_updated_by => G_USER_ID,
p_creation_date => SYSDATE,
p_created_by => G_USER_ID,
p_last_update_login => G_LOGIN_ID,
px_object_version_number => l_object_version_number,
p_active_flag => l_dm_target_rec.active_flag,
p_model_type => l_dm_target_rec.model_type,
p_data_source_id => l_dm_target_rec.data_source_id,
p_source_field_id => l_dm_target_rec.source_field_id,
p_target_name => l_dm_target_rec.target_name,
p_description => l_dm_target_rec.description,
p_target_source_id => l_dm_target_rec.target_source_id
);
update ams_list_src_types
set enabled_flag = 'Y'
where list_source_type_id = l_dm_target_rec.data_source_id;
PROCEDURE Update_Dmtarget(
p_api_version_number 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_dm_target_rec IN dm_target_rec_type,
x_object_version_number OUT NOCOPY NUMBER
)
IS
CURSOR c_get_dmtarget(p_target_id IN NUMBER) IS
SELECT *
FROM AMS_DM_TARGETS_VL
WHERE target_id = p_target_id;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Dmtarget';
SAVEPOINT UPDATE_Dmtarget_PVT;
AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET', p_token_name => 'INFO', p_token_value => 'Dmtarget') ;
AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING', p_token_name => 'COLUMN', p_token_value => 'Last_Update_Date') ;
p_validation_mode => JTF_PLSQL_API.g_update,
p_dm_target_rec => l_tar_dm_target_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler');
AMS_UTILITY_PVT.debug_message('p_last_update_date = ' || SYSDATE);
AMS_UTILITY_PVT.debug_message('p_last_updated_by = ' || G_USER_ID);
AMS_UTILITY_PVT.debug_message('p_last_update_login = ' || G_LOGIN_ID);
AMS_DM_TARGETS_B_PKG.Update_Row(
p_target_id => l_tar_dm_target_rec.target_id,
p_last_update_date => SYSDATE,
p_last_updated_by => G_USER_ID,
p_creation_date => SYSDATE,
p_created_by => G_USER_ID,
p_last_update_login => G_LOGIN_ID,
p_object_version_number => l_tar_dm_target_rec.object_version_number,
p_active_flag => l_tar_dm_target_rec.active_flag,
p_model_type => l_tar_dm_target_rec.model_type,
p_data_source_id => l_tar_dm_target_rec.data_source_id,
p_source_field_id => l_tar_dm_target_rec.source_field_id,
p_target_name => l_tar_dm_target_rec.target_name,
p_description => l_tar_dm_target_rec.description,
p_target_source_id => l_tar_dm_target_rec.target_source_id
);
ROLLBACK TO UPDATE_Dmtarget_PVT;
ROLLBACK TO UPDATE_Dmtarget_PVT;
ROLLBACK TO UPDATE_Dmtarget_PVT;
End Update_Dmtarget;
PROCEDURE Delete_Dmtarget(
p_api_version_number 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_target_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Dmtarget';
SELECT count(*)
FROM AMS_DM_MODELS_VL
WHERE TARGET_ID = l_id;
SELECT data_source_id
FROM ams_dm_targets_vl
WHERE target_id = l_tgtId;
SELECT count(*)
FROM AMS_DM_TARGETS_VL
WHERE DATA_SOURCE_ID = l_dsId
AND ACTIVE_FLAG = 'Y';
SAVEPOINT DELETE_Dmtarget_PVT;
AMS_UTILITY_PVT.debug_message( 'Private API: Going to Delete Target Values Associated with this Target ');
AMS_Dm_Target_Value_PVT.Delete_TgtValues_For_Target ( p_TARGET_ID );
AMS_UTILITY_PVT.debug_message( 'Private API: Going to Delete Child Data Sources Associated with this Target ');
AMS_Dm_Target_Sources_PVT.delete_tgtsources_for_target ( p_TARGET_ID );
AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
AMS_DM_TARGETS_B_PKG.Delete_Row(
p_TARGET_ID => p_TARGET_ID);
update ams_list_src_types
set enabled_flag = 'N'
where list_source_type_id = l_datasource_id;
ROLLBACK TO DELETE_Dmtarget_PVT;
ROLLBACK TO DELETE_Dmtarget_PVT;
ROLLBACK TO DELETE_Dmtarget_PVT;
End Delete_Dmtarget;
SELECT TARGET_ID
FROM AMS_DM_TARGETS_B
WHERE TARGET_ID = p_TARGET_ID
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
IS SELECT 1 from dual
WHERE EXISTS ( SELECT * from ams_dm_targets_vl
WHERE UPPER(target_name) = UPPER(p_dm_target_rec.target_name)) ;
IS SELECT 1 from dual
WHERE EXISTS ( SELECT * from ams_dm_targets_vl
WHERE UPPER(target_name) = UPPER(p_dm_target_rec.target_name)
AND target_id <> p_dm_target_rec.target_id );
SELECT count(*)
FROM AMS_DM_TARGETS_VL
WHERE DATA_SOURCE_ID = l_dsId
AND SOURCE_FIELD_ID = l_sfId
AND MODEL_TYPE = l_modelType
AND TARGET_ID <> l_targetId;
ams_utility_pvt.debug_message('Private API:check_dm_target_req_items for UPDATE');
IF p_dm_target_rec.last_update_date IS NULL THEN
AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','LAST_UPDATE_DATE');
IF p_dm_target_rec.last_updated_by IS NULL THEN
AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','LAST_UPDATED_BY');
SELECT *
FROM ams_dm_targets_vl
WHERE target_id = p_dm_target_rec.target_id;
IF p_dm_target_rec.last_update_date = FND_API.g_miss_date THEN
x_complete_rec.last_update_date := l_dm_target_rec.last_update_date;
IF p_dm_target_rec.last_updated_by = FND_API.g_miss_num THEN
x_complete_rec.last_updated_by := l_dm_target_rec.last_updated_by;
IF p_dm_target_rec.last_update_login = FND_API.g_miss_num THEN
x_complete_rec.last_update_login := l_dm_target_rec.last_update_login;
IF AMS_Access_PVT.check_update_access (
p_object_id => p_dm_target_rec.target_id,
p_object_type => L_TARGET_QUALIFIER,
p_user_or_role_id => l_owner_user_id,
p_user_or_role_type => L_ACCESS_TYPE_USER) = 'N' THEN
AMS_Utility_PVT.error_message ('AMS_TARGET_NO_UPDATE_ACCESS');
SELECT user_status_id
FROM ams_user_statuses_b
WHERE system_status_type = p_status_type
AND system_status_code = p_status_code
AND default_flag = 'Y'
AND enabled_flag = 'Y'
;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id;
UPDATE ams_dm_targets_b
SET active_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE (data_source_id = p_data_source_id OR target_source_id = p_data_source_id)
AND active_flag = 'Y'
;
UPDATE ams_dm_models_all_b a
SET a.status_code = l_status_code,
a.user_status_id = l_user_status_id,
a.last_update_date = SYSDATE,
a.last_updated_by = FND_GLOBAL.user_id,
a.status_date = SYSDATE
WHERE a.status_code = 'AVAILABLE'
AND (EXISTS
(SELECT 1
from ams_dm_targets_b b
where b.target_id=a.target_id
and b.data_source_id=p_data_source_id)
OR EXISTS
(SELECT 1
from ams_dm_target_sources c
where c.target_id=a.target_id
and c.data_source_id=p_data_source_id));
UPDATE ams_dm_targets_b a
SET a.active_flag = 'Y',
a.last_update_date = SYSDATE,
a.last_updated_by = FND_GLOBAL.user_id
WHERE (a.data_source_id = p_data_source_id OR a.target_source_id = p_data_source_id)
AND EXISTS (SELECT 1 FROM ams_list_src_types b WHERE b.list_source_type_id = a.data_source_id AND b.enabled_flag = 'Y')
AND EXISTS (SELECT 1 FROM ams_list_src_types c WHERE c.list_source_type_id = a.target_source_id AND c.enabled_flag = 'Y')
AND (EXISTS (SELECT 1 FROM ams_list_src_type_assocs d WHERE d.MASTER_SOURCE_TYPE_ID = a.data_source_id AND d.SUB_SOURCE_TYPE_ID = a.target_source_id AND d.enabled_flag = 'Y')
OR a.data_source_id = a.target_source_id)
AND EXISTS (SELECT 1 FROM ams_dm_target_values_b e where e.target_id = a.target_id)
AND a.active_flag = 'N'
;
UPDATE ams_dm_targets_b a
SET a.active_flag = 'Y',
a.last_update_date = SYSDATE,
a.last_updated_by = FND_GLOBAL.user_id
WHERE (a.data_source_id = p_master_source_id AND a.target_source_id = p_sub_source_id)
AND EXISTS (SELECT 1 FROM ams_list_src_types b WHERE b.list_source_type_id = a.data_source_id AND b.enabled_flag = 'Y')
AND EXISTS (SELECT 1 FROM ams_list_src_types c WHERE c.list_source_type_id = a.target_source_id AND c.enabled_flag = 'Y')
AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs d WHERE d.MASTER_SOURCE_TYPE_ID = a.data_source_id AND d.SUB_SOURCE_TYPE_ID = a.target_source_id AND d.enabled_flag = 'Y')
AND EXISTS (SELECT 1 FROM ams_dm_target_values_b e where e.target_id = a.target_id)
AND a.active_flag = 'N'
;
SELECT user_status_id
FROM ams_user_statuses_b
WHERE system_status_type = p_status_type
AND system_status_code = p_status_code
AND default_flag = 'Y'
AND enabled_flag = 'Y'
;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id;
UPDATE ams_dm_targets_b
SET active_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE (data_source_id = p_master_source_id AND target_source_id = p_sub_source_id)
AND active_flag = 'Y'
;
UPDATE ams_dm_models_all_b a
SET a.status_code = l_status_code,
a.user_status_id = l_user_status_id,
a.last_update_date = SYSDATE,
a.last_updated_by = FND_GLOBAL.user_id,
a.status_date = SYSDATE
WHERE a.status_code = 'AVAILABLE'
AND (EXISTS
(SELECT 1
from ams_dm_targets_b b
where b.target_id=a.target_id
and b.data_source_id=p_master_source_id)
OR EXISTS
(SELECT 1
from ams_dm_target_sources c
where c.target_id=a.target_id
and c.data_source_id=p_sub_source_id));
DELETE FROM ams_dm_target_sources
WHERE target_id IN (SELECT target_id FROM ams_dm_targets_b WHERE data_source_id = p_master_source_id AND target_source_id <> p_sub_source_id)
AND data_source_id = p_sub_source_id
;
SELECT active_flag from ams_dm_targets_b where target_id = p_tgt_id;