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

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.