Find ORGANIZATION TYPE Party info :

SELECT hp.party_id, hp.party_number, hp.party_name, hca.account_number,

hca.cust_account_id, hop.organization_profile_id,

hop.effective_start_date, hop.effective_end_date,

hop.organization_name, hop.duns_number, hop.enquiry_duns,

hop.ceo_name, hop.ceo_title, hop.principal_name, hop.principal_title,

hop.legal_status, hop.control_yr, hop.employees_total,

hop.hq_branch_ind, hop.branch_flag, hop.oob_ind,

hop.line_of_business

FROM hz_organization_profiles hop, hz_cust_accounts hca, hz_parties hp

WHERE hp.party_name = 'xxxx'

AND hp.party_id = hca.party_id

AND hp.party_id = hop.party_id

AND SYSDATE BETWEEN hop.effective_start_date

AND NVL (hop.effective_end_date, SYSDATE + 1)

ORDER BY hca.account_number

/