[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_isupdateable VARCHAR2(1) := 'N';
p_credit_request_rec OCM_CREDIT_REQUEST_UPDATE_PUB.credit_request_rec;
SELECT *
FROM ar_cmgt_setup_options;
SELECT 'x'
FROM PER_ALL_PEOPLE_F
WHERE sysdate between effective_start_date and effective_end_date
and current_employee_flag = 'Y'
and person_id = p_person_id;
SELECT 'x'
FROM fnd_user
WHERE sysdate between start_date and nvl(end_date, sysdate)
and user_id = cp_user_id;
SELECT 'X'
INTO l_char
FROM ar_lookups
WHERE lookup_type = 'OCM_CREDIT_REQUEST_TYPE'
AND lookup_code = l_credit_request_type;
select status
into l_parent_cr_status
from ar_cmgt_credit_requests
where credit_request_id = p_parent_credit_request_id
and party_id = p_party_id
and nvl(cust_account_id,-99) = nvl(p_cust_account_id,-99)
and nvl(site_use_id,-99) = nvl(p_site_use_id,-99)
and nvl(trx_currency,limit_currency) = p_trx_currency
and credit_classification = p_credit_classification
and review_type = p_review_type;
select recommendation_value1,
recommendation_value2
into l_value1,
l_value2
from ar_cmgt_cf_recommends
where credit_request_id = p_parent_credit_request_id
and credit_recommendation = 'AUTHORIZE_APPEAL'
and status = 'I'
and rownum = 1;
SELECT credit_request_id
INTO l_credit_request_id
FROM ar_cmgt_credit_requests
WHERE source_name = p_source_name
AND source_column1 = p_source_column1
AND party_id = p_party_id
AND cust_account_id = nvl(p_cust_account_id, -99)
AND site_use_id = nvl(p_site_use_id, -99)
AND status <> 'PROCESSED';
l_isupdateable := 'Y';
l_isupdateable := 'N';
SELECT max(credit_request_id)
INTO l_credit_request_id
FROM ar_cmgt_credit_requests
WHERE source_name = p_source_name
AND source_column1 = p_source_column1
AND party_id = p_party_id
AND cust_account_id = nvl(p_cust_account_id, -99)
AND site_use_id = nvl(p_site_use_id, -99)
AND status <> 'PROCESSED';
l_isupdateable := 'Y'; -- 7185336
'GETTING_MAX_CREDIT_REQUEST_FOR_UPDATE : '||SQLERRM);
'GETTING_CREDIT_REQUEST_FOR_UPDATE : '||SQLERRM);
determine if we are attempting to update one that is literally
in progress at the time of the call */
SELECT case_folder_id, status
INTO l_case_folder_id, l_case_folder_status
FROM ar_cmgt_case_folders
WHERE credit_request_id = l_credit_request_id
AND type = 'CASE';
UPDATE ar_cmgt_case_folders
SET STATUS = 'REFRESH',
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_updated = sysdate
WHERE case_folder_id = l_case_folder_id
AND status NOT IN ('SUBMITTED','CLOSED');
l_isupdateable := 'N';
l_isupdateable := 'Y';
IF l_isupdateable = 'Y'
THEN
select trx_amount,limit_amount
into l_trx_amount,l_requested_amount
from ar_cmgt_credit_requests
where credit_request_id = l_credit_request_id;
'GETTING_CASE_FOLDER_FOR_UPDATE : '||SQLERRM);
IF l_isupdateable = 'Y'
THEN
-- Now call Update API to update credit request.
-- FOR OM we will be updating only Amount.
p_credit_request_rec.credit_request_id := l_credit_request_id;
OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_credit_request_rec => p_credit_request_rec );
END IF; -- end of l_isupdateable
SELECT AR_CMGT_APPLICATION_NUM_S.NEXTVAL, AR_CMGT_CREDIT_REQUESTS_S.NEXTVAL
INTO l_application_number, l_credit_request_id
FROM DUAL;
SELECT AR_CMGT_CREDIT_REQUESTS_S.NEXTVAL
INTO l_credit_request_id
FROM dual;
SELECT lookup_code INTO l_credit_type
FROM ar_lookups
WHERE lookup_type = 'AR_CMGT_CREDIT_TYPE'
AND lookup_code = p_credit_type;
SELECT 'X'
INTO l_char
FROM ar_cmgt_scores
WHERE score_model_id = p_score_model_id
AND submit_flag = 'Y'
AND sysdate between start_date and
nvl(end_date,sysdate);
INSERT INTO AR_CMGT_CREDIT_REQUESTS
(credit_request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
application_number,
application_date,
requestor_type,
requestor_id,
review_type,
review_cycle,
credit_classification,
check_list_id,
credit_analyst_id,
limit_amount,
limit_currency,
trx_amount,
trx_currency,
credit_check_rule_id,
term_length,
credit_type,
party_id,
cust_account_id,
cust_acct_site_id,
site_use_id,
contact_party_id,
case_folder_number,
score_model_id,
attachment_flag,
status,
source_name,
source_user_id,
source_resp_id,
source_resp_appln_id,
source_security_group_id,
source_org_id,
source_column1,
source_column2,
source_column3,
notes,
request_id,
parent_credit_request_id,
credit_request_type,
RECOMMENDATION_NAME
)
VALUES
(l_credit_request_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_application_number,
l_application_date,
l_requestor_type,
l_requestor_id,
p_review_type,
p_review_cycle,
p_credit_classification,
null,
null,
p_requested_amount,
p_requested_currency,
p_trx_amount,
p_trx_currency,
p_credit_check_rule_id,
p_term_length,
p_credit_type,
p_party_id,
nvl(p_cust_account_id,-99),
nvl(p_cust_acct_site_id,-99),
nvl(p_site_use_id,-99),
p_contact_party_id,
p_case_folder_number,
p_score_model_id,
null,
l_credit_request_status,
p_source_name,
p_source_user_id,
p_source_resp_id,
p_source_appln_id,
p_source_security_group_id,
p_source_org_id,
p_source_column1,
p_source_column2,
p_source_column3,
p_notes,
fnd_global.conc_request_id,
p_parent_credit_request_id,
l_credit_request_type,
p_reco
);
INSERT INTO AR_CMGT_HOLD_DETAILS (HOLD_DETAIL_ID,
CREDIT_REQUEST_ID,
TYPE,
CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (AR_CMGT_HOLD_DTL_S.NEXTVAL,
l_credit_request_id,
'REASON',
l_hold_reason_rec(rec),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id);
SELECT 'x'
FROM ar_cmgt_setup_options;
SELECT 'x'
INTO l_char
FROM ar_cmgt_check_lists
WHERE submit_flag = 'Y';
SELECT application_number
FROM ar_cmgt_credit_requests
WHERE credit_request_id = p_credit_request_id;