[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_b
WHERE system_status_type = p_status_type
AND user_status_id = p_status_id
;
x_selections_changed_flag OUT NOCOPY VARCHAR2
);
SELECT score_id
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
SELECT score_id
FROM ams_dm_scores_all_tl
WHERE score_id = p_score_id
AND USERENV('LANG') IN (language, source_lang)
FOR UPDATE NOWAIT;
SELECT ams_dm_scores_all_b_s.NEXTVAL
FROM dual;
SELECT 1
FROM ams_dm_scores_all_b
WHERE score_id = l_id;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id;
SELECT custom_setup_id
FROM ams_custom_setups_b
WHERE object_type = G_OBJECT_TYPE_SCORE
AND activity_type_code IS NULL
AND enabled_flag = 'Y'
;
SELECT custom_setup_id
FROM ams_custom_setups_b
WHERE object_type = G_OBJECT_TYPE_SCORE
AND activity_type_code = G_OTGT_ACTIVITY_TYPE
AND enabled_flag = 'Y'
;
SELECT model_type
FROM ams_dm_models_all_b
WHERE model_id = l_model_id
;
AMS_DMSelection_PVT.is_org_prod_affn(
p_model_id => l_score_rec.model_id,
x_is_org_prod => l_is_org_prod
);
IF NVL (l_score_rec.row_selection_type, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
l_score_rec.row_selection_type := 'STANDARD';
AMS_DM_scoreS_B_PKG.Insert_Row(
p_score_id => l_score_rec.score_ID,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_object_version_number => l_object_version_number,
p_model_id => l_score_rec.model_id,
p_user_status_id => l_score_rec.user_status_id,
p_status_code => l_score_rec.status_code,
p_status_date => l_score_rec.status_date,
p_owner_user_id => l_score_rec.owner_user_id,
p_results_flag => NVL (l_score_rec.results_flag,'N'),
p_logs_flag => NVL (l_score_rec.logs_flag, 'N'),
p_scheduled_date => l_score_rec.scheduled_date,
p_scheduled_timezone_id => l_score_rec.scheduled_timezone_id,
p_score_date => l_score_rec.score_date,
p_total_records => l_score_rec.total_records,
p_total_positives => l_score_rec.total_positives,
p_expiration_date => l_score_rec.expiration_date,
p_min_records => l_score_rec.min_records,
p_max_records => l_score_rec.max_records,
p_row_selection_type => l_score_rec.row_selection_type,
p_every_nth_row => l_score_rec.every_nth_row,
p_pct_random => l_score_rec.pct_random,
p_custom_setup_id => l_score_rec.custom_setup_id,
p_country_id => l_score_rec.country_id,
p_wf_itemkey => l_score_rec.wf_itemkey,
p_score_name => l_score_rec.score_name,
p_description => l_score_rec.description,
p_attribute_category => l_score_rec.attribute_category,
p_attribute1 => l_score_rec.attribute1,
p_attribute2 => l_score_rec.attribute2,
p_attribute3 => l_score_rec.attribute3,
p_attribute4 => l_score_rec.attribute4,
p_attribute5 => l_score_rec.attribute5,
p_attribute6 => l_score_rec.attribute6,
p_attribute7 => l_score_rec.attribute7,
p_attribute8 => l_score_rec.attribute8,
p_attribute9 => l_score_rec.attribute9,
p_attribute10 => l_score_rec.attribute10,
p_attribute11 => l_score_rec.attribute11,
p_attribute12 => l_score_rec.attribute12,
p_attribute13 => l_score_rec.attribute13,
p_attribute14 => l_score_rec.attribute14,
p_attribute15 => l_score_rec.attribute15);
l_access_rec.delete_flag := 'N';
PROCEDURE Update_Score(
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_score_rec IN Score_Rec_Type,
x_object_version_number OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Score';
l_selections_changed_flag VARCHAR2(1);
SELECT *
FROM ams_dm_scores_vl
WHERE score_id = p_score_id;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_id;
SELECT m.target_id from ams_dm_models_all_b m,ams_dm_scores_all_b s
WHERE m.model_id = s.model_id
AND s.score_id = p_score_id
;
SAVEPOINT UPDATE_SCORE_PVT;
l_selections_changed_flag := 'N';
FND_MESSAGE.Set_Name('AMS', 'API_MISSING_UPDATE_TARGET');
AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Score');
AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Score');
AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Score');
AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Build');
l_selections_changed_flag);
IF l_selections_changed_flag = 'Y' THEN
l_tar_score_rec.status_code := G_STATUS_INVALID;
p_validation_mode => JTF_PLSQL_API.g_update,
p_score_rec => l_tar_score_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',FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
AMS_DM_scoreS_B_PKG.Update_Row(
p_score_ID => l_tar_score_rec.score_id,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_OBJECT_VERSION_NUMBER => l_tar_score_rec.OBJECT_VERSION_NUMBER,
p_MODEL_ID => l_tar_score_rec.MODEL_ID,
p_USER_STATUS_ID => l_tar_score_rec.USER_STATUS_ID,
p_STATUS_CODE => l_tar_score_rec.status_code,
p_STATUS_DATE => l_tar_score_rec.status_date,
p_OWNER_USER_ID => l_tar_score_rec.OWNER_USER_ID,
p_RESULTS_FLAG => l_tar_score_rec.RESULTS_FLAG,
p_logs_flag => l_tar_score_rec.logs_flag,
p_SCHEDULED_DATE => l_tar_score_rec.SCHEDULED_DATE,
p_SCHEDULED_TIMEZONE_ID => l_tar_score_rec.SCHEDULED_TIMEZONE_ID,
p_SCORE_DATE => l_tar_score_rec.SCORE_DATE,
p_total_records => l_tar_score_rec.total_records,
p_total_positives => l_tar_score_rec.total_positives,
p_EXPIRATION_DATE => l_tar_score_rec.EXPIRATION_DATE,
p_min_records => l_tar_score_rec.min_records,
p_max_records => l_tar_score_rec.max_records,
p_row_selection_type => l_tar_score_rec.row_selection_type,
p_every_nth_row => l_tar_score_rec.every_nth_row,
p_pct_random => l_tar_score_rec.pct_random,
p_custom_setup_id => l_tar_score_rec.custom_setup_id,
p_country_id => l_tar_score_rec.country_id,
p_wf_itemkey => l_tar_score_rec.wf_itemkey,
P_score_NAME => l_tar_score_rec.score_NAME,
p_DESCRIPTION => l_tar_score_rec.DESCRIPTION,
p_ATTRIBUTE_CATEGORY => l_tar_score_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_tar_score_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_tar_score_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_tar_score_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_tar_score_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_tar_score_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_tar_score_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_tar_score_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_tar_score_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_tar_score_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_tar_score_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_tar_score_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_tar_score_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_tar_score_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_tar_score_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_tar_score_rec.ATTRIBUTE15
);
ROLLBACK TO UPDATE_Score_PVT;
ROLLBACK TO UPDATE_Score_PVT;
ROLLBACK TO UPDATE_Score_PVT;
End Update_Score;
PROCEDURE Delete_Score(
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_score_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
CURSOR c_obj_version(c_id NUMBER) IS
SELECT object_version_number
FROM ams_dm_scores_all_b
WHERE score_id = c_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score';
SAVEPOINT DELETE_Score_PVT;
AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
FND_MESSAGE.Set_Name('AMS', 'API_MISSING_DELETE_TARGET');
AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
AMS_DM_scoreS_B_PKG.Delete_Row(
p_score_ID => p_score_id);
ROLLBACK TO DELETE_Score_PVT;
ROLLBACK TO DELETE_Score_PVT;
ROLLBACK TO DELETE_Score_PVT;
End Delete_Score;
IS SELECT 1 from dual
WHERE EXISTS ( SELECT * from ams_dm_scores_vl
WHERE UPPER(score_name) = UPPER(p_score_rec.score_name)) ;
IS SELECT 1 from dual
WHERE EXISTS ( SELECT * from ams_dm_scores_vl
WHERE UPPER(score_name) = UPPER(p_score_rec.score_name)
AND score_id <> p_score_rec.score_id );
ELSE -- update operation
IF p_score_rec.score_id IS NULL THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
THEN
FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_NO_ID');
SELECT *
FROM ams_dm_scores_vl
WHERE score_id = p_score_rec.score_id;
IF p_score_rec.row_selection_type = FND_API.g_miss_char THEN
x_complete_rec.row_selection_type := l_score_rec.row_selection_type;
SELECT owner_user_id
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id;
IF p_validation_mode = JTF_PLSQL_API.g_update THEN
l_context_resource_id := AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
IF p_score_rec.row_selection_type = 'NTH_RECORD' THEN
IF p_score_rec.every_nth_row IS NULL OR
p_score_rec.every_nth_row = FND_API.g_miss_num THEN
AMS_Utility_PVT.error_message ('AMS_DM_NO_NTH_RECORD');
IF p_score_rec.row_selection_type = 'RANDOM' THEN
IF p_score_rec.pct_random IS NULL OR
p_score_rec.pct_random = FND_API.g_miss_num THEN
AMS_Utility_PVT.error_message ('AMS_DM_NO_PCT_RANDOM');
IF AMS_Access_PVT.check_update_access (
p_object_id => p_score_rec.score_id,
p_object_type => G_OBJECT_TYPE_SCORE,
p_user_or_role_id => AMS_Utility_PVT.get_resource_id (FND_GLOBAL.user_id),
p_user_or_role_type => L_ACCESS_TYPE_USER) = 'N' THEN
AMS_Utility_PVT.error_message ('AMS_SCOR_NO_UPDATE_ACCESS');
SELECT *
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id
;
UPDATE ams_dm_models_all_b
SET last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
, object_version_number = object_version_number + 1
, status_code = G_STATUS_AVAILABLE
, user_status_id = l_model_status_id
, status_date = SYSDATE
WHERE model_id = l_score_rec.model_id;
UPDATE ams_dm_scores_all_b
SET last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
, object_version_number = object_version_number + 1
, status_code = p_status_code
, user_status_id = l_user_status_id
, status_date = SYSDATE
WHERE score_id = p_score_id;
SELECT *
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id
;
UPDATE ams_dm_models_all_b
SET last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
, object_version_number = object_version_number + 1
, status_code = G_STATUS_AVAILABLE
, user_status_id = l_model_status_id
, status_date = SYSDATE
WHERE model_id = l_score_rec.model_id;
UPDATE ams_dm_scores_all_b
SET object_version_number = object_version_number + 1
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, status_date = SYSDATE
, status_code = p_status_code
, user_status_id = l_user_status_id
, score_date = SYSDATE
, results_flag = 'Y'
WHERE score_id = p_score_id;
SELECT *
FROM ams_dm_scores_vl
WHERE score_id = p_score_id
;
SELECT m.target_id from ams_dm_models_all_b m,ams_dm_scores_all_b s
WHERE m.model_id = s.model_id
AND s.score_id = p_score_id
;
IF AMS_Access_PVT.check_update_access (
p_object_id => p_score_id,
p_object_type => L_SCORE_QUALIFIER,
p_user_or_role_id => l_owner_user_id,
p_user_or_role_type => L_ACCESS_TYPE_USER) = 'N' THEN
x_return_status := 'A';
AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Preview');
UPDATE ams_dm_scores_all_b
SET logs_flag = 'Y',
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
status_date = SYSDATE,
status_code = l_tar_score_rec.status_code,
user_status_id = l_tar_score_rec.user_status_id,
wf_itemkey = l_tar_score_rec.wf_itemkey
WHERE score_id = p_score_id;
SELECT *
FROM ams_dm_scores_vl
WHERE score_id = p_score_id
;
SELECT t.DATA_SOURCE_ID , t.target_id
FROM ams_dm_models_all_b m,ams_dm_targets_b t
WHERE m.model_id = p_model_id
AND m.target_id = t.target_id
;
l_score_rec.row_selection_type := l_reference_rec.row_selection_type;
IF l_score_rec.row_selection_type = 'NTH_RECORD' THEN
l_score_rec.every_nth_row := l_reference_rec.every_nth_row;
IF l_score_rec.row_selection_type = 'RANDOM' THEN
l_score_rec.pct_random := l_reference_rec.pct_random;
AMS_CopyElements_PVT.copy_list_select_actions (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_object_type => G_OBJECT_TYPE_SCORE,
p_src_object_id => p_source_object_id,
p_tar_object_id => l_new_score_id
);
SELECT *
FROM ams_dm_models_all_b
WHERE model_id = p_model_id;
SELECT *
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id;
UPDATE ams_dm_models_all_b
SET last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
, object_version_number = object_version_number + 1
, status_code = G_STATUS_SCORING
, user_status_id = l_model_status_id
, status_date = SYSDATE
WHERE model_id = l_model_rec.model_id;
UPDATE ams_dm_scores_all_b
SET last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
, object_version_number = object_version_number + 1
, status_code = G_STATUS_SCORING
, user_status_id = l_user_status_id
, status_date = SYSDATE
WHERE score_id = p_score_id;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM ams_dm_scores_all_b
WHERE model_id = p_model_id
AND status_code = G_STATUS_SCORING
AND score_id <> p_current_score_id)
;
p_select_list => NULL,
x_itemkey => x_tar_score_rec.wf_itemkey
);
SELECT *
FROM ams_dm_scores_vl
WHERE score_id = p_score_id
;
UPDATE ams_dm_scores_all_b
SET object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
status_date = SYSDATE,
status_code = l_ref_score_rec.status_code,
user_status_id = l_ref_score_rec.user_status_id,
wf_itemkey = l_ref_score_rec.wf_itemkey,
results_flag = l_ref_score_rec.results_flag
WHERE score_id = p_score_id;
UPDATE ams_dm_models_all_b
SET last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
, object_version_number = object_version_number + 1
, status_code = G_STATUS_AVAILABLE
, user_status_id = l_model_status_id
, status_date = SYSDATE
WHERE model_id = l_ref_score_rec.model_id;
p_select_list => NULL,
x_itemkey => x_tar_score_rec.wf_itemkey
);
delete /*+ index(AMS_DM_SOURCE AMS_DM_SOURCE_U2) */ from ams_dm_source
where arc_used_for_object = G_OBJECT_TYPE_SCORE
and used_for_object_id = p_score_ID;
delete from ams_dm_score_results
where score_id = p_score_id;
delete from ams_dm_score_pct_results
where score_id = p_score_id;
UPDATE ams_dm_scores_all_b
SET results_flag = 'N'
WHERE score_id = p_score_id;
l_data_selections_count NUMBER;
CURSOR l_dataSelectionsExist (p_score_id IN NUMBER) IS
SELECT count(*)
FROM ams_list_select_actions
WHERE arc_action_used_by = 'SCOR'
AND action_used_by_id = p_score_id;
SELECT model_id
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id;
l_data_selections_count := 0;
OPEN l_dataSelectionsExist (p_score_id);
FETCH l_dataSelectionsExist INTO l_data_selections_count;
CLOSE l_dataSelectionsExist;
IF l_data_selections_count IS NULL or l_data_selections_count = 0 THEN
x_data_exists_flag := 'N';
SELECT status_code, user_status_id
FROM ams_dm_scores_vl
WHERE score_id = p_score_id
;
x_selections_changed_flag OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'check_data_size_changes';
SELECT *
FROM ams_dm_scores_vl
WHERE score_id = p_score_id
;
x_selections_changed_flag := 'N';
x_selections_changed_flag := 'Y';
x_selections_changed_flag := 'Y';
IF (l_ref_score_rec.row_selection_type IS NULL AND p_input_score_rec.row_selection_type IS NOT NULL) OR
(l_ref_score_rec.row_selection_type <> p_input_score_rec.row_selection_type) THEN
x_selections_changed_flag := 'Y';
x_selections_changed_flag := 'Y';
x_selections_changed_flag := 'Y';
AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End. Selections Changed Flag = ' || x_selections_changed_flag);
SELECT *
FROM ams_dm_models_all_b
WHERE model_id = p_model_id;
SELECT *
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id;
PROCEDURE handle_data_selection_changes(
p_score_id IN NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'handle_data_selection_changes';
SELECT *
FROM ams_dm_scores_vl
WHERE score_id = p_score_id
;
UPDATE ams_dm_scores_all_b
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
status_date = SYSDATE,
status_code = G_STATUS_INVALID,
user_status_id = l_status_id
WHERE score_id = p_score_id;
END handle_data_selection_changes;