[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.OCM_CREDIT_REQUEST_UPDATE_PUB' );
4. Once the score is updated in 'CASE' type the same score need to be updated
in 'DATA' type record also.
*/
IF pg_debug = 'Y'
THEN
debug ( 'OCM_GET_EXTRL_DECSN_PUB.Get_Score(+)');
select type,
status,
score_model_id,
party_id,
cust_account_id,
site_use_id
into l_case_folder_type,
l_case_folder_status,
l_score_model_id,
l_party_id,
l_cust_account_id,
l_site_use_id
from ar_cmgt_case_folders
where case_folder_id = p_case_folder_id ;
select sc.DATA_POINT_ID, dp.data_point_code
into l_data_point_id, l_data_point_code
from ar_cmgt_score_dtls sc, ar_cmgt_data_points_vl dp
where sc.SCORE_MODEL_ID = p_score_model_id
and sc.data_point_id = dp.data_point_id
and dp.data_point_code = 'OCM_EXTERNAL_SCORE';
update ar_cmgt_cf_dtls
set score = p_score,
data_point_value = p_score
where CASE_FOLDER_ID = p_case_folder_id
and DATA_POINT_ID = l_data_point_id;
update ar_cmgt_cf_dtls
set score = p_score,
data_point_value = p_score
where CASE_FOLDER_ID = ( select CASE_FOLDER_ID
from ar_cmgt_case_folders
where type = 'DATA'
and party_id = l_party_id
and cust_account_id = l_cust_account_id
and site_use_id = l_site_use_id)
and DATA_POINT_ID = l_data_point_id;
select type,
status
into l_case_folder_type,
l_case_folder_status
from ar_cmgt_case_folders
where case_folder_id = p_case_folder_id ;
select DATA_POINT_ID
into l_data_point_id
from ar_cmgt_cf_dtls
where case_folder_id = p_case_folder_id
and DATA_POINT_ID = p_data_point_id(i);
update ar_cmgt_cf_dtls
set included_in_checklist = 'Y'
where case_folder_id = p_case_folder_id
and data_point_id = l_data_point_id;
select ar_cmgt_case_folders.type,
ar_cmgt_case_folders.status,
ar_cmgt_case_folders.credit_request_id,
ar_cmgt_credit_requests.credit_type,
ar_cmgt_credit_requests.SOURCE_RESP_APPLN_ID
into l_case_folder_type,
l_case_folder_status,
l_credit_request_id,
l_credit_type,
l_application_id
from ar_cmgt_case_folders,
ar_cmgt_credit_requests
where ar_cmgt_case_folders.case_folder_id = p_case_folder_id ;
select LOOKUP_TYPE
into l_lookup_type
from fnd_lookups
where lookup_type = p_recommendations_type
and enabled_flag = 'Y'
and rownum =1;
select LOOKUP_TYPE
into l_lookup_type
from fnd_lookups
where lookup_type = p_recommendations_type
and enabled_flag = 'Y'
and lookup_code = p_recommendations_tbl(i).Credit_Recommendation;
INSERT INTO AR_CMGT_CF_RECOMMENDS
(RECOMMENDATION_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
CASE_FOLDER_ID,
CREDIT_REQUEST_ID,
CREDIT_REVIEW_DATE,
CREDIT_RECOMMENDATION,
RECOMMENDATION_VALUE1,
RECOMMENDATION_VALUE2,
STATUS,
CREDIT_TYPE,
RECOMMENDATION_NAME,
APPLICATION_ID)
(SELECT AR_CMGT_CF_RECOMMENDS_S.NEXTVAL,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
SYSDATE,
fnd_global.user_id,
p_case_folder_id,
l_credit_request_id,
SYSDATE,
p_recommendations_tbl(i).Credit_Recommendation,
p_recommendations_tbl(i).Recommendation_value1,
p_recommendations_tbl(i).Recommendation_value2,
'O',
l_credit_type,
p_recommendations_type,
l_application_id
FROM dual
);
3. Will update the status of the case folder to Submitted and workflow
need to be kicked off and will continue for Approval route.
*/
IF pg_debug = 'Y'
THEN
debug ( 'OCM_GET_EXTRL_DECSN_PUB.Submit_Case_Folder(+)');
select type,
status,
credit_request_id
into l_case_folder_type,
l_case_folder_status,
l_credit_request_id
from ar_cmgt_case_folders
where case_folder_id = p_case_folder_id ;
/* Update case folder status to 'SUBMIT' and kick of the worlflow */
update ar_cmgt_case_folders
set status = 'SUBMIT'
where case_folder_id = p_case_folder_id ;