223: CURSOR c_check_profile (
224: p_cust_account_id NUMBER
225: ) IS
226: SELECT credit_hold
227: FROM hz_customer_profiles
228: WHERE cust_account_id = p_cust_account_id;
229:
230: BEGIN
231:
1046: l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1047:
1048: l_cnt number := 1;
1049: l_cust_acct_id number;
1050: -- Bug 8970479 : Cursor to get cust_account_profile_id from table hz_customer_profiles
1051: -- if record already exists for this site with 'DUNNING' or 'STATEMENTS' site_use_id
1052: CURSOR c_check_site_use_id IS
1053: SELECT hcp.cust_account_profile_id
1054: FROM hz_customer_profiles hcp,
1050: -- Bug 8970479 : Cursor to get cust_account_profile_id from table hz_customer_profiles
1051: -- if record already exists for this site with 'DUNNING' or 'STATEMENTS' site_use_id
1052: CURSOR c_check_site_use_id IS
1053: SELECT hcp.cust_account_profile_id
1054: FROM hz_customer_profiles hcp,
1055: hz_cust_site_uses_all hcsu
1056: WHERE hcsu.cust_acct_site_id = p_cust_site_use_rec.cust_acct_site_id
1057: AND hcp.site_use_id = hcsu.site_use_id
1058: AND hcsu.site_use_code IN ('DUN','STMTS');
1056: WHERE hcsu.cust_acct_site_id = p_cust_site_use_rec.cust_acct_site_id
1057: AND hcp.site_use_id = hcsu.site_use_id
1058: AND hcsu.site_use_code IN ('DUN','STMTS');
1059:
1060: l_cust_acct_prof_id HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_PROFILE_ID%TYPE ;
1061: BEGIN
1062:
1063: -- Debug info.
1064: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1357: p_msg_level=>fnd_log.level_procedure);
1358: END IF;
1359:
1360: /*Bug 8970479 :
1361: Updating tables hz_customer_profiles and hz_cust_profile_amts if bill-to business
1362: purpose is created and if these 2 tables contains site_use_id of 'DUNNING' or 'STATEMENTS'
1363: business purposes for current site*/
1364:
1365: IF (p_cust_site_use_rec.site_use_code = 'BILL_TO') THEN
1368: FETCH c_check_site_use_id INTO l_cust_account_profile_id ;
1369:
1370: IF (c_check_site_use_id%FOUND) THEN
1371:
1372: UPDATE hz_customer_profiles
1373: SET site_use_id = p_cust_site_use_rec.site_use_id
1374: WHERE cust_account_profile_id = l_cust_account_profile_id ;
1375:
1376: UPDATE hz_cust_profile_amts
1378: WHERE cust_account_profile_id = l_cust_account_profile_id ;
1379:
1380: -- Debug info.
1381: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1382: hz_utility_v2pub.debug(p_message=>'Updated hz_customer_profiles and hz_cust_profiles/amts with site_use_id of DUNNING or STATEMENTS' ,
1383: p_prefix =>l_debug_prefix,
1384: p_msg_level=>fnd_log.level_statement);
1385: END IF;
1386: -- Debug info.
3428:
3429: BEGIN
3430: -- Fetch customer profile id.
3431: SELECT CUST_ACCOUNT_PROFILE_ID INTO l_cust_account_profile_id
3432: FROM HZ_CUSTOMER_PROFILES
3433: WHERE SITE_USE_ID = p_site_use_id;
3434:
3435: HZ_CUSTOMER_PROFILE_V2PUB.get_customer_profile_rec (
3436: p_cust_account_profile_id => l_cust_account_profile_id,