[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cr.credit_request_id,
cr.source_name, cr.source_column1,
cr.source_column2, cr.source_column3,
cr.source_user_id,
cr.source_resp_id,
cr.source_resp_appln_id,
cr.source_security_group_id,
cr.source_org_id
FROM ar_cmgt_credit_requests cr,
ar_cmgt_case_folders cf
WHERE case_folder_id = p_cf_id
and cr.credit_request_id = cf.credit_request_id;
l_list.DELETE;
SELECT data_point_id
FROM ar_cmgt_check_list_dtls
WHERE data_point_id between 10000 and 20000
AND check_list_id = p_check_list_id
AND required_flag = 'Y';
SELECT distinct source_table_name
FROM ar_cmgt_dnb_elements_vl
WHERE source_table_name <> 'HZ_FINANCIAL_NUMBERS' -- this table is accessed via hz_financial_reports
and data_element_id in (
SELECT data_element_id
FROM ar_cmgt_dnb_mappings
WHERE data_point_id = l_data_point_id);
SELECT 1
INTO cnt
FROM ar_cmgt_cf_dnb_dtls
WHERE case_folder_id = p_case_folder_id
AND source_table_name = c_dnb_source_table_rec.source_table_name;
SELECT data_point_id, number_of_references
FROM ar_cmgt_check_list_dtls
WHERE check_list_id = p_check_list_id
AND data_point_id IN (86,87,88)
AND required_flag = 'Y';
SELECT COUNT(*)
INTO l_cnt
FROM ar_cmgt_bank_ref_data
WHERE credit_request_id = p_credit_request_id;
SELECT COUNT(*)
INTO l_cnt
FROM ar_cmgt_trade_ref_data
WHERE credit_request_id = p_credit_request_id;
SELECT COUNT(*)
INTO l_cnt
FROM ar_cmgt_guarantor_data
WHERE credit_request_id = p_credit_request_id;
SELECT check_list_id
INTO l_check_list_id
FROM ar_cmgt_case_folders
WHERE case_folder_id = p_case_folder_id
AND type = 'CASE';
SELECT 1 into l_cnt
FROM ar_cmgt_check_list_dtls a, ar_cmgt_cf_dtls b,
ar_cmgt_data_points_vl dp
WHERE a.check_list_id = l_check_list_id
AND b.case_folder_id = p_case_folder_id
AND a.data_point_id = b.data_point_id
AND dp.data_point_id = a.data_point_id
AND dp.data_point_category not in ('AGING','INVOICE')
AND a.required_flag = 'Y'
AND a.data_point_id < 10000 -- eliminate DNB data points
and b.data_point_value is null;
SELECT c.resource_id, c.source_id --employee id
FROM jtf_rs_role_relations a,
jtf_rs_roles_vl b,
jtf_rs_resource_extns_vl c
WHERE a.role_resource_type = 'RS_INDIVIDUAL'
AND a.role_resource_id = c.resource_id
AND a.role_id = b.role_id
AND b.role_code = 'CREDIT_ANALYST'
AND c.category = 'EMPLOYEE'
AND nvl(a.delete_flag,'N') <> 'Y';
SELECT employee_id
INTO l_employee_id
FROM FND_USER
WHERE user_name = wf_engine.context_text;
SELECT c.resource_id
INTO l_resource_id
FROM jtf_rs_role_relations a,
jtf_rs_roles_vl b,
jtf_rs_resource_extns_vl c
WHERE a.role_resource_type = 'RS_INDIVIDUAL'
AND a.role_resource_id = c.resource_id
AND a.role_id = b.role_id
AND b.role_code = 'CREDIT_ANALYST'
AND c.category = 'EMPLOYEE'
AND c.source_id = l_employee_id
AND nvl(a.delete_flag,'N') <> 'Y';
UPDATE ar_cmgt_credit_requests
set credit_analyst_id = l_resource_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE credit_request_id = itemkey
AND credit_analyst_id IS NULL;
UPDATE ar_cmgt_case_folders
set credit_analyst_id = l_resource_id,
last_updated = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE credit_request_id = itemkey
AND credit_analyst_id IS NULL;
SELECT p.country,p.state, p.province,p.sic_code,p.party_name,
p.employees_total, c.credit_classification,
nvl(c.limit_amount,c.trx_amount), c.review_type,
nvl(c.limit_currency,c.trx_currency),
profclass.name, p.postal_code, p.state, p.party_id
INTO l_country, l_state, l_province, l_sic_code,
l_party_name, l_employees_total, l_credit_classification,
l_amount, l_review_type, l_currency,
l_profile_class_name, l_postal_code, l_state, l_party_id
FROM ar_cmgt_credit_requests c,
hz_parties p,
hz_cust_profile_classes profclass,
hz_customer_profiles prof
WHERE c.credit_request_id = p_credit_request_id
AND c.party_id = p.party_id
AND p.party_id = prof.party_id
AND c.cust_account_id = decode(prof.cust_account_id,-1,-99,prof.cust_account_id)
AND c.site_use_id = nvl(prof.site_use_id,-99)
AND prof.profile_class_id = profclass.profile_class_id;
SELECT a.CREDIT_ANALYST_ID
INTO l_credit_analyst_id
FROM hz_customer_profiles a, ar_cmgt_credit_requests b
WHERE b.credit_request_id = itemkey
AND a.party_id = b.party_id
AND a.cust_account_id = decode(b.cust_account_id,-99,-1,b.cust_account_id)
AND nvl(a.site_use_id,-99) = nvl(b.site_use_id, -99);
SELECT 'X'
INTO l_dummy
FROM jtf_rs_role_relations a,
jtf_rs_roles_vl b,
jtf_rs_resource_extns_vl c
WHERE a.role_resource_type = 'RS_INDIVIDUAL'
AND a.role_resource_id = c.resource_id
AND c.resource_id = l_credit_analyst_id
AND a.role_id = b.role_id
AND b.role_code = 'CREDIT_ANALYST'
AND c.category = 'EMPLOYEE'
AND nvl(a.delete_flag,'N') <> 'Y';
SELECT source_id
INTO l_employee_id
FROM jtf_rs_resource_extns_vl
WHERE resource_id = l_credit_analyst_id
AND category = 'EMPLOYEE';
UPDATE ar_cmgt_credit_requests
SET credit_analyst_id = l_credit_analyst_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE credit_request_id = itemkey;
update ar_cmgt_case_folders
set credit_analyst_id = l_credit_analyst_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 = l_case_folder_id;
update ar_cmgt_case_folders
set credit_analyst_id = l_credit_analyst_id,
last_updated = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE credit_request_id = itemkey
AND type = 'DATA';
SELECT credit_classification, party_id, cust_account_id, site_use_id
INTO l_credit_classification, l_party_id, l_cust_account_id, l_site_use_id
FROM ar_cmgt_credit_requests
WHERE credit_request_id = itemkey;
UPDATE ar_cmgt_credit_requests
SET credit_classification = l_credit_classification
WHERE credit_request_id = itemkey;
PROCEDURE UPDATE_CREDIT_REQ_TO_PROCESS (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
BEGIN
IF funcmode = 'RUN'
THEN
UPDATE ar_cmgt_credit_requests
SET status = 'IN_PROCESS',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE credit_request_id = itemkey;
PROCEDURE UPDATE_CREDIT_REQ_TO_SUBMIT (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
BEGIN
IF funcmode = 'RUN'
THEN
UPDATE ar_cmgt_credit_requests
SET status = 'SUBMIT',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE credit_request_id = itemkey;
PROCEDURE UPDATE_CASE_FOLDER_SUBMITTED (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
UPDATE ar_cmgt_case_folders
SET status = 'SUBMITTED',
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 = l_case_folder_id;
SELECT party_id
INTO l_party_id
FROM ar_cmgt_credit_requests
WHERE credit_request_id = itemkey;
SELECT cust_account_profile_id
INTO l_cust_account_profile_id
FROM hz_customer_profiles
WHERE party_id = l_party_id
AND cust_account_id = -1
AND site_use_id IS NULL;
SELECT req.credit_classification, req.review_type,
nvl(req.limit_currency, trx_currency),
nvl(nvl(req.limit_amount,req.trx_amount),0),
req.case_folder_number, req.score_model_id, req.source_name,
req.application_number,
lkp1.meaning classification_meaning,
lkp2.meaning review_type_meaning,
requestor_id,
application_date,
req.party_id,
cust_account_id,
source_column1,
source_column2,
source_column3,
party.party_name,
party.party_number,
req.notes,
req.credit_request_type,
nvl(req.requestor_type, 'EMPLOYEE')
INTO l_credit_classification, l_review_type, l_currency,
l_amount_requested, l_case_folder_number, l_score_model_id,
l_source_name, l_application_number,
l_classification_meaning,
l_review_type_meaning,
l_requestor_id,
l_application_date,
l_party_id,
l_cust_account_id,
l_source_column1,
l_source_column2,
l_source_column3,
l_party_name,
l_party_number,
l_notes,
l_credit_request_type,
l_requestor_type
FROM ar_cmgt_credit_requests req,
ar_lookups lkp1,
ar_lookups lkp2,
hz_parties party
WHERE req.credit_request_id = itemkey
AND req.party_id = party.party_id
AND lkp1.lookup_type = 'AR_CMGT_CREDIT_CLASSIFICATION'
AND lkp1.lookup_code = req.credit_classification
AND lkp2.lookup_type = 'AR_CMGT_REVIEW_TYPE'
AND lkp2.lookup_code = req.review_type;
SELECT req.credit_classification, req.review_type, req.application_number,
req.score_model_id,
application_date,
req.party_id,
cust_account_id,
source_column1,
source_column2,
source_column3,
party.party_name,
party.party_number,
req.notes,
req.requestor_id,
req.source_name,
req.case_folder_number,
nvl(req.limit_currency, trx_currency),
nvl(nvl(req.limit_amount,req.trx_amount),0),
req.credit_request_type,
nvl(req.requestor_type, 'EMPLOYEE')
INTO l_credit_classification, l_review_type, l_application_number,
l_score_model_id,
l_application_date,
l_party_id,
l_cust_account_id,
l_source_column1,
l_source_column2,
l_source_column3,
l_party_name,
l_party_number,
l_notes,
l_requestor_id,
l_source_name,
l_case_folder_number,
l_currency,
l_amount_requested,
l_credit_request_type,
l_requestor_type
FROM ar_cmgt_credit_requests req,
hz_parties party
WHERE credit_request_id = itemkey
AND req.party_id = party.party_id;
SELECT user_name
INTO l_requestor_user_name
FROM fnd_user
WHERE user_id = l_requestor_id;
SELECT ACCOUNT_NUMBER
INTO l_account_number
FROM hz_cust_accounts
WHERE cust_account_id = l_cust_account_id;
SELECT check_list_id, score_model_id
INTO l_check_list_id, l_score_model_id
FROM ar_cmgt_check_lists
WHERE submit_flag = 'Y'
AND credit_classification = l_credit_classification
AND review_type = l_review_type
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
--Update credit request table with checklistid
UPDATE ar_cmgt_credit_requests
set check_list_id = l_check_list_id
WHERE credit_request_id = itemkey;
SELECT score_model_id
INTO l_score_model_id_1
FROM ar_cmgt_scores
WHERE score_model_id = l_score_model_id
AND submit_flag = 'Y'
AND TRUNC(sysdate) between TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
SELECT currency
INTO l_score_currency
FROM ar_cmgt_scores
WHERE score_model_id = l_score_model_id
and submit_flag = 'Y'
and TRUNC(nvl(end_date, SYSDATE)) >= TRUNC(sysdate)
and currency = l_limit_currency;
delete ar_cmgt_case_folders
WHERE case_folder_id = case_folder_id;
delete ar_cmgt_cf_dtls
WHERE case_folder_id = case_folder_id;
l_case_folder_date ar_cmgt_case_folders.last_updated%type;
SELECT credit_classification, review_type,
cust_account_id, party_id, site_use_id, nvl(limit_currency,trx_currency),
source_org_id, case_folder_number
INTO l_credit_classification, l_review_type, l_cust_account_id,
l_party_id, l_cust_acct_site_id, l_trx_currency, l_org_id,
l_case_folder_number
FROM ar_cmgt_credit_requests
WHERE credit_request_id = itemkey;
SELECT case_folder_number, last_updated
INTO l_case_folder_number, l_case_folder_date
FROM ar_cmgt_case_folders
WHERE case_folder_id = l_case_folder_id;
SELECT score_model_id
INTO l_score_model_id_1
FROM ar_cmgt_scores
WHERE score_model_id = l_score_model_id
AND submit_flag = 'Y'
AND TRUNC(sysdate) between TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
SELECT auto_rules_id
INTO l_auto_rules_id
FROM ar_cmgt_auto_rules
WHERE score_model_id = l_score_model_id
AND submit_flag = 'Y'
AND TRUNC(sysdate) between TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
SELECT override_checklist_flag, skip_approval_flag
INTO l_override_checklist, l_skip_approval
FROM ar_cmgt_auto_rule_dtls
WHERE auto_rules_id = l_auto_rules_id
AND l_score between credit_score_low and credit_score_high
AND currency = l_currency;
select distinct data_point_id
from ar_cmgt_score_dtls
where score_model_id= l_score_model_id;
SELECT nvl(null_zero_flag,'N')
INTO l_null_zero_flag
FROM ar_cmgt_scores
WHERE score_model_id = l_score_model_id;
SELECT RETURN_DATA_TYPE, data_point_code
INTO l_data_point_type, l_data_point_code
FROM AR_CMGT_SCORABLE_DATA_POINTS_V
WHERE DATA_POINT_ID = dp_id_collec_rec.data_point_id;
SELECT case1.data_point_value, case1.data_point_id
INTO l_data_point_value, l_data_point_id
FROM ar_cmgt_score_dtls score,
ar_cmgt_cf_dtls case1
WHERE score.score_model_id = l_score_model_id
AND case1.case_folder_id = l_case_folder_id
AND score.data_point_id = case1.data_point_id
AND score.data_point_id = dp_id_collec_rec.data_point_id
AND case1.data_point_value IS NULL;
procedure UPDATE_RECOMMENDATION
( p_party_id IN NUMBER,
p_cust_account_id IN NUMBER,
p_site_use_id IN NUMBER default null,
p_credit_recommendation IN VARCHAR2,
p_reco_value1 IN VARCHAR2,
p_reco_value2 IN VARCHAR2) IS
l_sql_statement VARCHAR2(2000);
' UPDATE hz_customer_profiles ' ||
' set credit_classification = :1 ,'||
' last_update_date = sysdate ,'||
' last_updated_by = fnd_global.user_id, '||
' last_update_login = fnd_global.login_id '||
' where party_id = :2 '||
' and cust_account_id = :3 '||
' and site_use_id IS NULL' ;
' UPDATE hz_customer_profiles ' ||
' set credit_classification = :1, '||
' last_update_date = sysdate ,'||
' last_updated_by = fnd_global.user_id, '||
' last_update_login = fnd_global.login_id '||
' where party_id = :2 '||
' and cust_account_id = :3 '||
' and site_use_id = :4 ';
UPDATE hz_cust_profile_amts
set trx_credit_limit = p_reco_value2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE cust_account_profile_id = (
select cust_account_profile_id
from hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id IS NULL )
AND currency_code = p_reco_value1;
UPDATE hz_cust_profile_amts
set trx_credit_limit = p_reco_value2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE cust_account_profile_id = (
select cust_account_profile_id
from hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id = p_site_use_id )
AND currency_code = p_reco_value1;
UPDATE hz_cust_profile_amts
set overall_credit_limit = p_reco_value2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE cust_account_profile_id = (
select cust_account_profile_id
from hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id IS NULL )
AND currency_code = p_reco_value1;
UPDATE hz_cust_profile_amts
set overall_credit_limit = p_reco_value2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE cust_account_profile_id = (
select cust_account_profile_id
from hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id = p_site_use_id )
AND currency_code = p_reco_value1;
UPDATE hz_customer_profiles
set credit_hold = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id IS NULL;
UPDATE hz_customer_profiles
set credit_hold = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id = p_site_use_id;
UPDATE hz_customer_profiles
set credit_hold = 'N',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id IS NULL;
UPDATE hz_customer_profiles
set credit_hold = 'N',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id = p_site_use_id;
UPDATE hz_cust_profile_amts
set overall_credit_limit = (overall_credit_limit +
( overall_credit_limit * p_reco_value2/100)),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE cust_account_profile_id = (
select cust_account_profile_id
from hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id IS NULL )
AND currency_code = p_reco_value1;
UPDATE hz_cust_profile_amts
set overall_credit_limit = (overall_credit_limit +
( overall_credit_limit * p_reco_value2/100)),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE cust_account_profile_id = (
select cust_account_profile_id
from hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id = p_site_use_id )
AND currency_code = p_reco_value1;
UPDATE hz_cust_profile_amts
set trx_credit_limit = (trx_credit_limit +
( trx_credit_limit * p_reco_value2/100)),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE cust_account_profile_id = (
select cust_account_profile_id
from hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id IS NULL )
AND currency_code = p_reco_value1;
UPDATE hz_cust_profile_amts
set trx_credit_limit = (trx_credit_limit +
( trx_credit_limit * p_reco_value2/100)),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE cust_account_profile_id = (
select cust_account_profile_id
from hz_customer_profiles
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id = p_site_use_id )
AND currency_code = p_reco_value1;
insert_failure EXCEPTION;
SELECT a.credit_recommendation, a.recommendation_value1,
a.recommendation_value2
FROM ar_cmgt_auto_recommends a, ar_cmgt_auto_rule_dtls b
WHERE a.auto_rule_details_id = b.auto_rule_details_id
AND l_score between b.credit_score_low and b.credit_score_high
AND a.credit_type = l_credit_type
AND b.auto_rules_id = l_auto_rules_id;
SELECT CREDIT_TYPE, credit_request_type
INTO l_credit_type, l_credit_request_type
FROM ar_cmgt_credit_requests
WHERE credit_request_id = itemkey;
raise insert_failure;
WHEN insert_failure THEN
wf_core.context('AR_CMGT_WF_ENGINE','GENERATE_RECOMMENDATION',itemtype,
itemkey,
'Error while inserting into ar_cmgt_cf_recommends',
sqlerrm);
procedure UPDATE_AME_APPROVE(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_case_folder_id number;
ame_api.updateApprovalStatus2(applicationIdIn => 222,
transactionIdIn => l_case_folder_id,
approvalStatusIn =>
AME_UTIL.approvedStatus,
approverPersonIdIn => l_approver_id,
transactionTypeIn => 'ARCMGTAP');
procedure UPDATE_AME_REJECT(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_case_folder_id number;
ame_api.updateApprovalStatus2(applicationIdIn => 222,
transactionIdIn => l_case_folder_id,
approvalStatusIn => AME_UTIL.rejectStatus,
approverPersonIdIn => l_approver_id,
transactionTypeIn => 'ARCMGTAP');
Update ar_cmgt_cf_recommends
set status = 'R',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE case_folder_id = l_case_folder_id;
Update ar_cmgt_case_folders
set status = 'CLOSED',
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 = l_case_folder_id;
Update ar_cmgt_credit_requests
set status = 'PROCESSED',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE credit_request_id = itemkey;
SELECT credit_recommendation, recommendation_value1,
recommendation_value2
FROM ar_cmgt_cf_recommends
WHERE case_folder_id = l_case_folder_id
AND credit_type = l_credit_type;
SELECT party_id, decode(cust_account_id,-99,-1,cust_account_id),
decode(site_use_id,-99,null,site_use_id), credit_type
INTO l_party_id, l_cust_account_id, l_site_use_id, l_credit_type
FROM ar_cmgt_credit_requests
WHERE credit_request_id = itemkey;
UPDATE_RECOMMENDATION
( p_party_id => l_party_id,
p_cust_account_id => l_cust_account_id,
p_site_use_id => l_site_use_id,
p_credit_recommendation => c_reco_rec.credit_recommendation,
p_reco_value1 => c_reco_rec.recommendation_value1,
p_reco_value2 => trunc(c_reco_rec.recommendation_value2));
SELECT recommendation_value1,RECOMMENDATION_ID
INTO l_dayz,l_reco_id
FROM ar_cmgt_cf_recommends
WHERE CASE_FOLDER_ID = l_case_folder_id
AND credit_recommendation = 'AUTHORIZE_APPEAL';
--update number of days in ar_cmgt_cf_recommends
UPDATE AR_CMGT_CF_RECOMMENDS
SET RECOMMENDATION_VALUE2 = fnd_date.DATE_TO_CANONICAL(trunc(sysdate) + to_number(trunc(l_dayz))),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE RECOMMENDATION_ID = l_reco_id;
SELECT LAST_CREDIT_REVIEW_DATE
INTO l_last_revw_date
FROM hz_customer_profiles
WHERE party_id = l_party_id
AND cust_account_id = l_cust_account_id
AND site_use_id IS NULL;
UPDATE hz_customer_profiles
SET REVIEW_CYCLE = c_reco_rec.recommendation_value1,
NEXT_CREDIT_REVIEW_DATE = DECODE(c_reco_rec.recommendation_value1,
'YEARLY', (l_last_revw_date + 365),
'HALF_YEARLY', (l_last_revw_date + 180),
'QUARTERLY', (l_last_revw_date + 90),
'MONTHLY', (l_last_revw_date + 30),
'WEEKLY', (l_last_revw_date + 7),
l_last_revw_date + 1)
WHERE party_id = l_party_id
AND cust_account_id = l_cust_account_id
AND site_use_id IS NULL;
SELECT LAST_CREDIT_REVIEW_DATE
INTO l_last_revw_date
FROM hz_customer_profiles
WHERE party_id = l_party_id
AND cust_account_id = l_cust_account_id
AND site_use_id = l_site_use_id;
itemkey,'No data found in cust profile for update',
sqlerrm);
UPDATE hz_customer_profiles
SET REVIEW_CYCLE = c_reco_rec.recommendation_value1,
NEXT_CREDIT_REVIEW_DATE = DECODE(review_cycle,
'YEARLY', (l_last_revw_date + 365),
'HALF_YEARLY', (l_last_revw_date + 180),
'QUARTERLY', (l_last_revw_date + 90),
'MONTHLY', (l_last_revw_date + 30),
'WEEKLY', (l_last_revw_date + 7),
l_last_revw_date + 1)
WHERE party_id = l_party_id
AND cust_account_id = l_cust_account_id
AND site_use_id IS NOT NULL;
Update ar_cmgt_cf_recommends
set status = 'I',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE case_folder_id = l_case_folder_id;
Update ar_cmgt_case_folders
set status = 'CLOSED',
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 = l_case_folder_id;
Update ar_cmgt_credit_requests
set status = 'PROCESSED',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE credit_request_id = itemkey;
SELECT party_id, decode(cust_account_id,-99,-1,cust_account_id),
decode(site_use_id,-99,null,site_use_id)
INTO l_party_id, l_cust_account_id, l_site_use_id
FROM ar_cmgt_credit_requests
WHERE credit_request_id = itemkey;
/* Update the risk factor data point value */
BEGIN
SELECT DECODE(to_number(NVL(cfd.data_point_value,'1')),0,1,NVL(cfd.data_point_value,'1'))
INTO l_exposure
FROM ar_cmgt_cf_dtls cfd
WHERE cfd.case_folder_id = l_case_folder_id
AND cfd.data_point_id = 34;
SELECT nvl(overall_credit_limit,0)
INTO l_credit_limit
FROM hz_cust_profile_amts hzp
WHERE cust_account_profile_id = (
SELECT cust_account_profile_id
FROM hz_customer_profiles
WHERE party_id = l_party_id
AND cust_account_id = l_cust_account_id
AND ( site_use_id IS NULL
OR site_use_id = l_site_use_id))
AND currency_code = l_limit_currency;
AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS (
p_case_folder_id => l_case_folder_id,
p_data_point_id => 182,
p_data_point_value => l_risk_factor,
p_score => NULL,
p_errmsg => l_errmsg,
p_resultout => l_resultout);
SELECT a.parent_credit_request_id
INTO l_parent_credit_request_id
FROM ar_cmgt_credit_requests a, ar_cmgt_credit_requests b
WHERE a.credit_request_id = itemkey
AND a.parent_credit_request_id = b.credit_request_id
AND b.status <> 'PROCESSED';
SELECT CREDIT_RECOMMENDATION,RECOMMENDATION_ID
FROM AR_CMGT_CF_RECOMMENDS
WHERE CREDIT_REQUEST_ID = itemkey;
select case_folder_id
INTO l_case_folder_id
FROM ar_cmgt_case_folders
WHERE credit_request_id = itemkey
and type = 'CASE';
PROCEDURE UPDATE_SKIP_APPROVAL_FLAG (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_failure_function VARCHAR2(60);
PROCEDURE UPDATE_CF_TO_CREATE (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
UPDATE ar_cmgt_case_folders
set status = 'CREATED',
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 = l_case_folder_id;
/* This procedure is used tyo update a particular
** workflow item attribute, for eg. credit_analyst_id */
PROCEDURE UPDATE_WF_ATTRIBUTE (
p_itemkey IN VARCHAR2,
p_attribute_type IN VARCHAR2,
p_attribute_name IN VARCHAR2,
p_attribute_value IN VARCHAR2 ) IS
l_person_id per_people_f.person_id%type;
END UPDATE_WF_ATTRIBUTE;
SELECT r.credit_request_id, r.credit_request_type, r.status
INTO l_credit_request_id, l_credit_request_type, l_status
FROM ar_cmgt_credit_requests r
WHERE r.parent_credit_request_id = itemkey
AND r.status <> 'PROCESSED';
SELECT 'Y'
FROM ar_cmgt_cf_recommends
WHERE CASE_FOLDER_ID=l_case_folder_id;
SELECT 'X'
INTO l_flag
FROM ar_cmgt_cf_recommends cf, ar_cmgt_credit_requests req
WHERE req.credit_request_id = itemkey
AND req.credit_request_id = cf.credit_request_id
AND req.cust_account_id = -99
AND req.site_use_id = -99
AND cf.credit_recommendation = 'CUST_HOLD';
insert_failure EXCEPTION;
SELECT credit_request_type
INTO l_credit_request_type
FROM ar_cmgt_credit_requests
WHERE credit_request_id = itemkey;
SELECT PARENT_CREDIT_REQUEST_ID
INTO l_parent_creq_id
FROM AR_CMGT_CREDIT_REQUESTS
WHERE CREDIT_REQUEST_ID = itemkey;
SELECT CASE_FOLDER_ID
INTO l_parent_cf_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = l_parent_creq_id
and type = 'CASE';
SELECT req.credit_classification, req.review_type,
nvl(req.limit_currency, trx_currency),
nvl(nvl(req.limit_amount,req.trx_amount),0),
req.case_folder_number, req.score_model_id, req.source_name,
req.application_number,
lkp1.meaning classification_meaning,
lkp2.meaning review_type_meaning,
requestor_id,
application_date,
req.party_id,
cust_account_id,
source_column1,
source_column2,
source_column3,
party.party_name,
party.party_number,
req.notes,
nvl(req.requestor_type, 'EMPLOYEE')
INTO l_credit_classification, l_review_type, l_currency,
l_amount_requested, l_case_folder_number, l_score_model_id,
l_source_name, l_application_number,
l_classification_meaning,
l_review_type_meaning,
l_requestor_id,
l_application_date,
l_party_id,
l_cust_account_id,
l_source_column1,
l_source_column2,
l_source_column3,
l_party_name,
l_party_number,
l_notes,
l_requestor_type
FROM ar_cmgt_credit_requests req,
ar_lookups lkp1,
ar_lookups lkp2,
hz_parties party
WHERE req.credit_request_id = itemkey
AND req.party_id = party.party_id
AND lkp1.lookup_type = 'AR_CMGT_CREDIT_CLASSIFICATION'
AND lkp1.lookup_code = req.credit_classification
AND lkp2.lookup_type = 'AR_CMGT_REVIEW_TYPE'
AND lkp2.lookup_code = req.review_type;
SELECT req.credit_classification, req.review_type, req.application_number,
req.score_model_id,
application_date,
req.party_id,
cust_account_id,
source_column1,
source_column2,
source_column3,
party.party_name,
party.party_number,
req.notes,
req.requestor_id,
req.source_name,
req.case_folder_number,
nvl(req.limit_currency, trx_currency),
nvl(nvl(req.limit_amount,req.trx_amount),0),
nvl(req.requestor_type, 'EMPLOYEE')
INTO l_credit_classification, l_review_type, l_application_number,
l_score_model_id,
l_application_date,
l_party_id,
l_cust_account_id,
l_source_column1,
l_source_column2,
l_source_column3,
l_party_name,
l_party_number,
l_notes,
l_requestor_id,
l_source_name,
l_case_folder_number,
l_currency,
l_amount_requested,
l_requestor_type
FROM ar_cmgt_credit_requests req,
hz_parties party
WHERE credit_request_id = itemkey
AND req.party_id = party.party_id;
SELECT user_name
INTO l_requestor_user_name
FROM fnd_user
WHERE user_id = l_requestor_id;
SELECT ACCOUNT_NUMBER
INTO l_account_number
FROM hz_cust_accounts
WHERE cust_account_id = l_cust_account_id;
SELECT case_folder_id, case_folder_number, check_list_id, score_model_id,
limit_currency, creation_date_time
INTO l_case_folder_id, l_case_folder_number, l_check_list_id, l_score_model_id,
l_limit_currency, l_creation_date_time
FROM ar_cmgt_case_folders
WHERE credit_request_id = itemkey
AND type = 'CASE';
select sc.data_point_id, dp.data_point_code
from ar_cmgt_score_dtls sc, ar_cmgt_data_points_vl dp
where sc.score_model_id= l_score_model_id
AND sc.data_point_id = dp.data_point_id
AND dp.data_point_code = 'OCM_EXTERNAL_SCORE';
UPDATE ar_cmgt_case_folders
SET request_id = l_request_id,
status = 'IN_PROCESS',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE case_folder_id = l_case_folder_id;
insert_failure EXCEPTION;
SELECT sum(nvl(SCORE,0))
FROM AR_CMGT_CF_DTLS
WHERE CASE_FOLDER_ID=p_cf_id;
SELECT cf.score_model_id,cr.credit_type
from ar_cmgt_case_folders cf,ar_cmgt_credit_requests cr
where case_folder_id=p_cf_id
and cr.credit_request_id=cf.credit_request_id;
SELECT auto_rules_id
FROM ar_cmgt_auto_rules
WHERE score_model_id=l_score_model_id
AND Trunc(SYSDATE) BETWEEN Trunc(start_date) AND Trunc(Nvl(end_date,SYSDATE))
AND submit_flag='Y';
SELECT a.credit_recommendation, a.recommendation_value1,
a.recommendation_value2
FROM ar_cmgt_auto_recommends a, ar_cmgt_auto_rule_dtls b
WHERE a.auto_rule_details_id = b.auto_rule_details_id
AND l_score between b.credit_score_low and b.credit_score_high
AND a.credit_type = l_credit_type
AND b.auto_rules_id = l_auto_rules_id;
UPDATE ar_cmgt_case_folders
SET request_id = p_request_id,
status = 'IN_PROCESS',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE case_folder_id = p_case_folder_id;