[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO OCM_CREDIT_REVIEW_GT (
party_id,
cust_account_id,
site_use_id,
check_list_id,
review_cycle,
next_credit_review_date,
last_credit_review_date,
review_type,
credit_classification,
currency_code )
VALUES
(p_party_id,
p_cust_account_id,
p_site_use_id,
p_check_list_id,
p_review_cycle,
p_next_review_date,
p_last_review_date,
p_review_type,
p_credit_classification,
p_currency_code);
review_cur_str1 := 'SELECT party.party_id party_id,
-99 cust_account_id ,
-99 site_use_id ,
hcp.review_cycle review_cycle,
hcp.NEXT_CREDIT_REVIEW_DATE next_review_date,
NVL(hcp.LAST_CREDIT_REVIEW_DATE, party.creation_date) last_review_date,
checklist.review_type review_type,
checklist.credit_classification credit_classification,
hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
hcp.credit_classification cp_credit_classification
FROM hz_customer_profiles hcp,
ar_cmgt_check_lists checklist,
hz_parties party
WHERE hcp.party_id = NVL(:bnd_party_id ,hcp.party_id)
AND :bnd_cust_account_id IS NULL
AND hcp.cust_account_id = -1
AND hcp.site_use_id IS NULL
AND party.party_id = hcp.party_id
AND party.status = '||''''||'A'||''''||'
AND party.party_type = '||''''||'ORGANIZATION'||''''||'
AND nvl(hcp.credit_classification, -99) = NVL(:bnd_credit_classification,
nvl(hcp.credit_classification,-99))
AND nvl(hcp.profile_class_id,-99) = NVL(:bnd_profile_class_id,nvl(hcp.profile_class_id,-99))
AND checklist.check_list_id = :bnd_check_list_id
AND nvl(hcp.review_cycle,-99) = NVL(:bnd_review_cycle,nvl(hcp.review_cycle,-99))
AND hcp.status ='||'''A''';
review_cur_str2 := 'SELECT party.party_id party_id,
hcp.cust_account_id cust_account_id,
-99 site_use_id,
hcp.review_cycle review_cycle,
hcp.NEXT_CREDIT_REVIEW_DATE next_review_date,
NVL(hcp.LAST_CREDIT_REVIEW_DATE, cust.creation_date) last_review_date,
checklist.review_type review_type,
checklist.credit_classification credit_classification,
hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
hcp.credit_classification cp_credit_classification
FROM hz_parties party,
hz_customer_profiles hcp,
ar_cmgt_check_lists checklist,
hz_cust_accounts cust
WHERE hcp.party_id = NVL(:bnd_party_id ,hcp.party_id)
AND party.party_id = hcp.party_id
AND hcp.CUST_ACCOUNT_ID = NVL(:bnd_cust_account_id ,hcp.CUST_ACCOUNT_ID)
AND hcp.cust_account_id <> -1
and hcp.cust_account_id = cust.cust_account_id
and cust.status = '||''''||'A'||''''||'
AND hcp.site_use_id IS NULL
AND party.party_type = '||''''||'ORGANIZATION'||''''||'
AND nvl(hcp.credit_classification, -99) = NVL(:bnd_credit_classification,
nvl(hcp.credit_classification,-99))
AND nvl(hcp.profile_class_id,-99) = NVL(:bnd_profile_class_id,nvl(hcp.profile_class_id,-99))
AND checklist.check_list_id = :bnd_check_list_id
AND nvl(hcp.review_cycle,-99) = NVL(:bnd_review_cycle,nvl(hcp.review_cycle,-99))
AND hcp.status ='||'''A''';
review_cur_str3 := 'SELECT party.party_id party_id,
hcp.cust_account_id cust_account_id,
hcp.site_use_id site_use_id,
hcp.review_cycle review_cycle,
hcp.NEXT_CREDIT_REVIEW_DATE next_review_date,
NVL(hcp.LAST_CREDIT_REVIEW_DATE, uses.creation_date) last_review_date,
checklist.review_type review_type,
checklist.credit_classification credit_classification,
hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
hcp.credit_classification cp_credit_classification
FROM hz_parties party,
HZ_CUST_SITE_USES_ALL uses ,
hz_customer_profiles hcp,
ar_cmgt_check_lists checklist
WHERE hcp.party_id = NVL(:bnd_party_id,hcp.party_id)
AND hcp.CUST_ACCOUNT_ID = NVL(:bnd_cust_account_id ,hcp.CUST_ACCOUNT_ID)
AND party.party_id = hcp.party_id
AND hcp.cust_account_id <> -1
AND hcp.site_use_id IS NOT NULL
AND hcp.SITE_USE_ID = uses.SITE_USE_ID
AND uses.site_use_code = '||''''||'BILL_TO'||''''||'
and uses.status = '||''''||'A'||''''||'
AND party.party_type = '||''''||'ORGANIZATION'||''''||'
AND nvl(hcp.credit_classification, -99) = NVL(:bnd_credit_classification,
nvl(hcp.credit_classification,-99))
AND nvl(hcp.profile_class_id,-99) = NVL(:bnd_profile_class_id,nvl(hcp.profile_class_id,-99))
AND checklist.check_list_id = :bnd_check_list_id
AND nvl(hcp.review_cycle,-99) = NVL(:bnd_review_cycle,nvl(hcp.review_cycle,-99))
AND hcp.status ='||'''A''';
review_cur_str4 := 'SELECT hcp.party_id party_id,
DECODE(hcp.cust_account_id,-1,-99,hcp.cust_account_id) cust_account_id,
NVL(hcp.site_use_id,-99) site_use_id,
hcp.review_cycle review_cycle,
hcp.NEXT_CREDIT_REVIEW_DATE next_review_date,
NVL(NVL(hcp.LAST_CREDIT_REVIEW_DATE, uses.creation_date), party.creation_date) last_review_date,
checklist.review_type,
checklist.credit_classification,
hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
hcp.credit_classification cp_credit_classification
FROM hz_customer_profiles hcp,
ar_cmgt_check_lists checklist,
hz_parties party,
HZ_CUST_SITE_USES_ALL uses
WHERE party.party_id = hcp.party_id
AND :bnd_party_id IS NULL
AND :bnd_cust_account_id IS NULL
AND party.party_type = '||''''||'ORGANIZATION'||''''||'
AND nvl(hcp.credit_classification, -99) = NVL(:bnd_credit_classification,
nvl(hcp.credit_classification,-99))
AND hcp.profile_class_id = NVL(:bnd_profile_class_id,hcp.profile_class_id)
AND checklist.check_list_id = :bnd_check_list_id
AND nvl(hcp.review_cycle,-99) = NVL(:bnd_review_cycle,nvl(hcp.review_cycle,-99))
AND hcp.site_use_id = uses.site_use_id(+)
and uses.site_use_code(+) = '||''''||'BILL_TO'||''''||'
AND hcp.status ='||'''A''';
SELECT party_id
INTO l_party_id
FROM HZ_CUST_ACCOUNTS
WHERE CUST_ACCOUNT_ID = l_cust_account_id;
SELECT trx_currency
INTO l_exist_trx_currency
FROM ar_cmgt_credit_requests
WHERE party_id = review_rec.party_id
AND cust_account_id = review_rec.cust_account_id
AND site_use_id = review_rec.site_use_id
AND source_name = 'AR_PERIODIC_REVIEW'
-- AND trx_currency = l_currency_code -- Bug 5149880
AND trunc(application_date) between l_last_review_date_for_curr
and l_review_cycle_as_of_date;
UPDATE HZ_CUSTOMER_PROFILES
SET NEXT_CREDIT_REVIEW_DATE =
DECODE(review_cycle,
'YEARLY', (trunc(sysdate) + 365),
'HALF_YEARLY', (trunc(sysdate) + 180),
'QUARTERLY', (trunc(sysdate) + 90),
'MONTHLY', (trunc(sysdate) + 30),
'WEEKLY', (trunc(sysdate) + 7),
(trunc(sysdate) + 1)),
LAST_CREDIT_REVIEW_DATE = trunc(SYSDATE),
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_DATE = sysdate,
last_update_login = fnd_global.login_id
WHERE HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_PROFILE_ID = review_rec.profile_id;