Receivables Customer Account and Site Level Contacts Query

--Account Level --

select hzp.party_name,

hca.account_number,

hca.account_name,

contact_party.party_name contact_name,

resp.responsibility_type,

ctp.contact_point_type,

ctp.email_address,

ctp.phone_number

from hz_parties hzp,

hz_cust_accounts hca,

hz_cust_account_roles role,

hz_relationships rel,

hz_parties contact_party,

hz_role_responsibility resp,

hz_contact_points ctp

where 1 = 1

and hca.party_id = hzp.party_id

and hca.cust_account_id = role.cust_account_id

and role.status = 'a'

and role.role_type = 'contact'

and rel.relationship_id = role.relationship_id

and trunc(sysdate) between rel.start_date

and rel.end_date

and rel.subject_id = contact_party.party_id

and rel.relationship_code = 'contact_of'

and role.cust_account_role_id = resp.cust_account_role_id

and ctp.relationship_id = rel.relationship_id

and ctp.status = 'a'

and resp.status_flag = 'a'

and hca.account_number = :p_account_num

--Site Level--

select distinct hzp.party_name,

hca.account_number,

hca.account_name,

hps.party_site_number,

hps.party_site_name,

contact_party.party_name contact_name,

resp.responsibility_type,

ctp.contact_point_type,

ctp.email_address,

ctp.phone_number

from hz_parties hzp,

hz_cust_accounts hca,

hz_cust_acct_sites_all hcas,

hz_party_sites hps,

hz_cust_account_roles role,

hz_relationships rel,

hz_parties contact_party,

hz_cust_site_uses_all hcs,

hz_role_responsibility resp,

hz_contact_points ctp

where 1 = 1

and hca.party_id = hzp.party_id

and hcas.cust_account_id = hca.cust_account_id

and hps.party_site_id = hcas.party_site_id

and hca.cust_account_id = role.cust_account_id

and hcas.cust_acct_site_id = role.cust_acct_site_id

and role.status = 'a'

and role.role_type = 'contact'

and rel.relationship_id = role.relationship_id

and trunc(sysdate) between rel.start_date

and rel.end_date

and rel.subject_id = contact_party.party_id

and rel.relationship_code = 'contact_of'

and hcs.cust_acct_site_id = hcas.cust_acct_site_id

and role.cust_account_role_id = resp.cust_account_role_id

and ctp.relationship_id = rel.relationship_id

and ctp.status = 'a'

and resp.status_flag = 'a'

and hca.account_number = :p_account_num