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.