Finding Guarantors Case Folder for a Parent case folder

Below is the query to find Guarantors/Child case folder for a Parent/Dealer case folder along credit analyst assigned to it.



select child_cf.case_folder_number child_case_folder, parent_cf.case_folder_number  parent_case_folder,  res.source_name
from
ar_cmgt_credit_requests re,
ar_cmgt_case_folders child_cf,
ar_cmgt_case_folders parent_cf,
jtf_rs_resource_extns res
where
parent_credit_request_id = parent_cf.credit_request_id
--and parent_cf.case_folder_number = '14225'
and res.source_name = 'Credit Analyst Name Here'
and parent_cf.review_type is not null
and res.resource_id = parent_cf.credit_analyst_id
and child_cf.credit_request_id = re.credit_request_id
and child_cf.review_type is not null
order by 2 desc

Finding Credit Analyst Name in Oracle Credit Management

-- To find Credit analyst name for the given case folder name. ------

select source_name credit_analyst_ name, source_job_title Job_Title, case_folder_number, usr.user_name User, usr.description
from jtf_rs_resource_extns res ,
ar_cmgt_case_folders cf,
fnd_user usr
where
res.resource_id = cf.credit_analyst_id
and case_folder_number = '' 
and res.user_id = usr.user_id


  -- ====== to find out credit analyst  assigned to Rule Name -----

select rule_name, result_value , usr.user_name credit_analyst_name , res.resource_id
from fun_rule_details  fun, jtf_rs_resource_extns res, fnd_user usr
where rule_name = '' and
fun.result_value = res.resource_id
and res.user_id = usr.user_id

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...

OCM - Dealer Names and Its Guarantor Names

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
from hz_parties p, hz_relationships hrel, hz_parties orgp
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

OCM - How to extract Dealer names and its related Owner names

Select r.party_id party_id, p.party_number dealer_num, p.party_name dealer_name, sub_party.party_name owner_name
from hz_relationships r , hz_parties p, hz_parties sub_party,
where r.subject_id = sub_party.party_id
and r.object_id = p.party_id
and ((r.end_date is null) or (r.end_date > sysdate)) and r.status = 'A'

How to list down all the attachments attached to a vendor or party

Here is a way to find out all the attachments attached to a specific vendor or specific customer/party etc.

#1. To list down all the attachments attached to each supplier/vendor, following query can be used.

select segment1 vendor_num, vendor_name,
fdoc.title file_title, fdoc.file_name
from ap_suppliers sup, fnd_attached_documents doc, fnd_documents_vl fdoc
where
doc.pk1_value = sup.vendor_id and doc.entity_name = 'PO_VENDORS'
and doc.document_id =  fdoc.document_id
order by vendor_name

See the screenshot for  sample attachment for one vendor.

#2. In a similar way, to find out attachments for parties..

 select hzp.party_name Owner_name, hzp.party_number Owner_number, hzp.party_type, docs.creation_date doc_created_date , fdoc.title file_title, fdoc.file_name
from FND_ATTACHED_DOCUMENTS docs, hz_parties hzp,  fnd_documents_vl fdoc
where entity_name like 'HZ_PARTIES' and  pk1_value = hzp.party_id
and docs.document_id =  fdoc.document_id
order by 1

Thanks,
Chandra.