[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(scores)
INTO l_max_score
FROM ar_cmgt_score_dtls
WHERE score_model_id = p_score_model_id
AND data_point_id = p_data_point_id;
SELECT RETURN_DATA_TYPE,RETURN_DATE_FORMAT
INTO l_data_point_type,l_date_format
FROM AR_CMGT_SCORABLE_DATA_POINTS_V
WHERE DATA_POINT_ID = p_data_point_id;
--change for selecting 0 in case weight is null i.e. not assigned
-- IF the user has choosen convert the null value to zero.
IF p_data_point_value IS NULL
THEN
BEGIN
SELECT NULL_ZERO_FLAG
INTO NULL_ZERO_CONVR_IND
FROM AR_CMGT_SCORES
WHERE SCORE_MODEL_ID = p_score_model_id;
SELECT score.scores, NVL(weight.weight,0)
INTO l_scores, l_weight
FROM ar_cmgt_score_dtls score,
AR_CMGT_SCORE_WEIGHTS weight
WHERE score.score_model_id = p_score_model_id
AND score.data_point_id = p_data_point_id
AND score.score_model_id = weight.score_model_id
AND score.data_point_id = weight.data_point_id
-- AND score.num_char_flag = 'N'
AND to_number(l_data_point_value) between score.range_from and score.range_to;
--output.put_line('score selected = ' ||l_scores);
--output.put_line('weight selected = ' || l_weight);
SELECT score.scores, NVL(weight.weight,0)
INTO l_scores, l_weight
FROM ar_cmgt_score_dtls score,
AR_CMGT_SCORE_WEIGHTS weight
WHERE score.score_model_id = p_score_model_id
AND score.data_point_id = p_data_point_id
AND score.score_model_id = weight.score_model_id
AND score.data_point_id = weight.data_point_id
-- AND score.num_char_flag = 'D'
AND to_date(p_data_point_value,l_date_format) between
to_date(score.range_from,l_date_format)
and to_date(score.range_to,l_date_format);
SELECT score.scores, NVL(weight.weight,0)
INTO l_scores, l_weight
FROM ar_cmgt_score_dtls score,
AR_CMGT_SCORE_WEIGHTS weight
WHERE score.score_model_id = p_score_model_id
AND score.data_point_id = p_data_point_id
AND score.score_model_id = weight.score_model_id
AND score.data_point_id = weight.data_point_id
-- AND score.num_char_flag = 'C'
AND p_data_point_value between score.range_from and score.range_to;
SELECT 'X'
INTO l_result
FROM ar_cmgt_scores score, ar_cmgt_case_folders case1
WHERE case1.case_folder_id = p_case_folder_id
AND score.score_model_id = p_score_model_id
AND case1.limit_currency = score.currency
AND trunc(sysdate) between trunc(score.start_date) and
nvl(trunc(score.end_date), trunc(sysdate));
Select data_point_category
into l_category
from ar_cmgt_scorable_data_points_v
where data_point_id =p_data_point_id;
SELECT INCLUDED_IN_CHECKLIST
INTO l_chk_list
FROM AR_CMGT_CF_DTLS
WHERE CASE_FOLDER_ID=p_case_folder_id
AND DATA_POINT_ID=p_data_point_id;
--only update the other data points
--i.e. 'ADDITIONAL' data points are
--not updated from PLSQL.
if l_updt_flag ='Y' THEN
Update ar_cmgt_cf_dtls
set data_point_value = p_data_point_value,
score = l_score
WHERE data_point_id = p_data_point_id
AND case_folder_id = p_case_folder_id;
SELECT source_table_name, source_column_name
INTO l_source_table_name, l_source_column_name
FROM ar_cmgt_dnb_elements_vl
WHERE data_element_id = p_data_point_id;
SELECT cfd.source_key, cfd.source_key_type, cfd.source_key_column_name,
cfd.source_key_column_type_name
INTO l_source_key, l_source_key_type, l_source_key_column_name,
l_source_key_column_type
FROM ar_cmgt_cf_dnb_dtls cfd
WHERE cfd.case_folder_id = p_case_folder_id
AND cfd.source_table_name = l_source_table_name;
queryStr := 'SELECT '|| ':l_source_column_name' ||
' FROM '|| ':l_source_table_name' ||
' WHERE '|| ':l_source_key_column_name' || ' = :l_source_key';
queryStr := 'SELECT '||':l_source_column_name' ||
' FROM '|| ':l_source_table_name' ||
' WHERE '|| ':l_source_key_column_name' || ' = :l_source_key '||
' AND ' || ':l_source_key_column_type' ||' = || :l_source_key_type';
SELECT data_point_category
INTO l_category
FROM ar_cmgt_scorable_data_points_v
where data_point_id = p_data_point_id;
SELECT data1.case_folder_id
INTO g_data_case_folder_id
FROM ar_cmgt_case_folders data1, ar_cmgt_case_folders case1
WHERE data1.type = 'DATA'
and case1.case_folder_id = p_case_folder_id
and case1.party_id = data1.party_id
and case1.cust_account_id = data1.cust_account_id
and case1.site_use_id = data1.site_use_id;
UPDATE ar_cmgt_cf_dtls
SET score = null,
last_updated_by = fnd_global.user_id, last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE case_folder_id = g_data_case_folder_id;
SELECT data_point_value
INTO l_data_point_value
FROM ar_cmgt_cf_dtls
WHERE case_folder_id = p_case_folder_id
AND data_point_id = p_data_point_id;
SELECT data_point_value
INTO l_data_point_value
FROM ar_cmgt_cf_dtls
WHERE case_folder_id = p_case_folder_id
AND data_point_id = p_data_point_id;
This code is chanded to update only in case the data point category is not
ADDITIONAL.*/
IF l_updt_flg ='Y'
THEN
AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS
( p_case_folder_id => p_case_folder_id,
p_data_point_id => p_data_point_id,
p_data_point_value => l_data_point_value,
p_score => l_score,
p_errmsg => p_error_msg,
p_resultout => p_resultout);
AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS
( p_case_folder_id => g_data_case_folder_id,
p_data_point_id => p_data_point_id,
p_data_point_value => l_data_point_value,
p_score => l_score,
p_errmsg => p_error_msg,
p_resultout => p_resultout);
SELECT distinct score.data_point_id, score.score_model_id
FROM ar_cmgt_score_dtls score,
ar_cmgt_case_folders case1
WHERE case_folder_id = p_case_folder_id
AND case1.score_model_id = score.score_model_id;
UPDATE ar_cmgt_cf_dtls
SET score = null,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE case_folder_id = p_case_folder_id;
SELECT distinct score.data_point_id, score.score_model_id
FROM ar_cmgt_score_dtls score
WHERE score_model_id = p_score_model_id;
SELECT 'X'
INTO l_result
FROM ar_cmgt_scores score, ar_cmgt_case_folders case1
WHERE case1.case_folder_id = p_case_folder_id
AND score.score_model_id = p_score_model_id
AND case1.limit_currency = score.currency
AND trunc(sysdate) between trunc(score.start_date) and
nvl(trunc(score.end_date), trunc(sysdate));
UPDATE ar_cmgt_cf_dtls
SET score = null,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE case_folder_id = p_case_folder_id;
/* UPDATE ar_cmgt_case_folders
set score_model_id = p_score_model_id,
last_updated = SYSDATE,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE case_folder_id = p_case_folder_id; */