Tuesday, April 19, 2011

How to fix - Credit usage rule has not been setup

Credit usage rule has not been setup
When creating credit applications, Guarantor or child workflow may throw an error message - Credit usage rule has not been setup.













Reason could be - Credit classification for the Guarantor might not be setup properly.
Following query would help you to find out list of potential guarantors which would cause an issue while creating credit application.












This query list down the information such as Guarantor Name, Dealer Name and the Customer account profile id and object version number.
--
select p.party_type , p.party_number, p.party_name guarantor_name, p.party_id, hrel.object_id, hrel.object_table_name, hrel.object_type,
orgp.party_name dealer_name, orgp.party_number delaer_number,hzp.cust_account_profile_id, hzp.object_version_number,
p.creation_date, p.last_update_date, p.last_updated_by,ppf.full_name user_
from hz_parties p, hz_relationships hrel, hz_parties orgp,  hz_customer_profiles hzp, per_people_f ppf, fnd_user usr
where p.party_name  in (select
party_name
from hz_parties hzp where party_id in (select party_id from hz_customer_profiles hz,hz_cust_profile_classes cp
where cp.name = 'DEFAULT' and cp.profile_class_id = hz.profile_class_id and hz.cust_account_id = -1 )
and hzp.party_type = 'PERSON'    )
and hrel.subject_id = p.party_id
and orgp.party_id = hrel.object_id
and hzp.party_id = p.party_id
and hzp.cust_account_id = -1
and hrel.object_table_name = 'HZ_PARTIES'
and p.party_type = 'PERSON'
and p.last_updated_by = usr.user_id
and ppf.person_id = usr.employee_id

----

Execute following API to set credit classification correctly.

declare
p_customer_profile_rec_type
hz_customer_profile_v2pub.customer_profile_rec_type;
p_cust_account_profile_id number;
p_object_version_number number;
x_return_status varchar2(2000);
x_msg_count number;
x_msg_data varchar2(2000);
begin

fnd_client_info.set_org_context('&Org_Id');
p_customer_profile_rec_type.cust_account_profile_id := '&Cust_Account_Profile_Id';
p_customer_profile_rec_type.profile_class_id := '&New_profile_class_id';
p_object_version_number := '&object_version_number';

hz_customer_profile_v2pub.update_customer_profile( 'T',p_customer_profile_rec_type, p_object_version_number, x_return_status, x_msg_count, x_msg_data);

dbms_output.put_line('x_return_status = '||substr(x_return_status,1,255));
dbms_output.put_line('Object Version Number = '||to_char(p_object_version_number));
dbms_output.put_line('profile_class_id = '||p_customer_profile_rec_type.profile_class_id);
dbms_output.put_line('x_msg_count = '||to_char(x_msg_count));
dbms_output.put_line('x_msg_data = '|| to_char (x_msg_data,1,255));

if x_msg_count >1 then
for i in 1..x_msg_count loop
dbms_output.put_line(i||'.'||substr(fnd_msg_pub.get(p_encoded=> fnd_api.g_false ), 1, 255)); end loop; end if; end;


commit ;

After executing this, you may retest credit application process again and verify the results.
Hope this helps...