[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','DNB') -- Added 'DNB' for bug 8632968 in place of commented code below
AND a.required_flag = 'Y'
--AND a.data_point_id < 10000 -- Commented for bug 8632968
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';
mode is Transfer, then there is no need to update the fields as the
same operation will be done by means of Entity Objects(EO). If the
update happens at both the levels (EO and at this API),
FND_RECORD_CHANGED_ERROR will be thrown
*/
IF funcmode <> 'TRANSFER' THEN
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 param_name
FROM fun_rule_crit_params_b param,
fun_rule_objects_vl rule_object
WHERE rule_object.rule_object_name = 'OCM_CREDIT_ANALYST_ASSGN'
AND rule_object.rule_object_id = param.rule_object_id
AND param.parameter_type = 'CUSTOM';
SELECT parent.credit_request_id,
parent.credit_analyst_id
INTO l_parent_request_id,
l_parent_analyst_id
FROM ar_cmgt_credit_requests parent,
ar_cmgt_credit_requests child
WHERE child.credit_request_id = p_credit_request_id
AND child.parent_credit_request_id = parent.credit_request_id
AND child.credit_request_type = 'GUARANTOR';
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, cust_account_id, site_use_id
INTO l_party_id, l_cust_acct_id, l_site_use_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 SYSDATE BETWEEN start_date and 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 recommendation_value2
FROM ar_cmgt_cf_recommends
WHERE credit_recommendation = 'CREDIT_LIMIT'
AND credit_request_id = itemkey;
SELECT recommendation_value2
FROM ar_cmgt_cf_recommends
WHERE credit_recommendation = 'TXN_CREDIT_LIMIT'
AND credit_request_id = itemkey;
SELECT trx_amount
INTO l_trx_amount
FROM ar_cmgt_credit_requests
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) OR (nvl(skip_currency_test_flag, 'N') = 'Y')); -- Added for bug 8600040
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 currency
INTO l_trans_currency
FROM ar_cmgt_scores
where score_model_id = (SELECT score_model_id
FROM ar_cmgt_case_folders
WHERE case_folder_id = l_case_folder_id);
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_trans_currency; -- Fixed for bug 9871620
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;
SELECT recommendation_value2
FROM ar_cmgt_cf_recommends
WHERE credit_recommendation = 'CREDIT_LIMIT'
AND credit_request_id = itemkey;
SELECT recommendation_value2
FROM ar_cmgt_cf_recommends
WHERE credit_recommendation = 'TXN_CREDIT_LIMIT'
AND credit_request_id = itemkey;
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;
SELECT recommendation_value2
FROM ar_cmgt_cf_recommends
WHERE credit_recommendation = 'CREDIT_LIMIT'
AND credit_request_id = itemkey;
SELECT recommendation_value2
FROM ar_cmgt_cf_recommends
WHERE credit_recommendation = 'TXN_CREDIT_LIMIT'
AND credit_request_id = itemkey;
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 => l_amount);
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;
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),
LAST_CREDIT_REVIEW_DATE = l_last_revw_date,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE party_id = l_party_id
AND cust_account_id = l_cust_account_id
AND ((l_site_use_id IS NULL AND site_use_id IS NULL) OR
site_use_id = l_site_use_id);
debug(SQL%ROWCOUNT || ' hz_customer_rofiles row(s) updated.');
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 recommendation_value2
FROM ar_cmgt_cf_recommends
WHERE credit_recommendation = 'CREDIT_LIMIT'
AND credit_request_id = itemkey;
SELECT recommendation_value2
FROM ar_cmgt_cf_recommends
WHERE credit_recommendation = 'TXN_CREDIT_LIMIT'
AND 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;
PROCEDURE update_credit_analyst_info(p_itemkey IN VARCHAR2,
p_credit_analyst_id IN VARCHAR2) IS
l_credit_analyst_id NUMBER(15);
SELECT credit_analyst_id, original_credit_analyst_id,
previous_credit_analyst_id, case_folder_number
FROM
ar_cmgt_case_folders
WHERE credit_request_id = p_itemkey
AND type = 'CASE';
UPDATE ar_cmgt_case_folders
SET original_credit_analyst_id = l_credit_analyst_id,
previous_credit_analyst_id = l_credit_analyst_id
WHERE case_folder_number = l_case_folder_number;
UPDATE ar_cmgt_case_folders
SET original_credit_analyst_id = NULL,
previous_credit_analyst_id = NULL
WHERE case_folder_number = l_case_folder_number;
UPDATE ar_cmgt_case_folders
SET previous_credit_analyst_id = l_credit_analyst_id
WHERE case_folder_number = l_case_folder_number;
END update_credit_analyst_info;
/* 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;
SELECT wfn.notification_id, wfs.assigned_user
FROM wf_item_activity_statuses wfs, wf_notifications wfn
WHERE wfs.item_type = 'ARCMGTAP'
AND wfs.item_key = p_itemkey
AND wfs.notification_id is not null
AND wfs.notification_id = wfn.notification_id
AND wfn.status = 'OPEN';
update_credit_analyst_info(p_itemkey => p_itemkey,
p_credit_analyst_id => p_attribute_value);
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,
last_update_login = fnd_global.login_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;