Contact details at party or customer level

-- Show Contact details at party/customer account level for all Bill To sites
-- Enter value - Customer Number
---------------------------------------------------------------------------------------------------------------------------
SELECT  hca.account_name  "Account Name"
       ,hca.account_number  "Account Number"
       ,Acct_contact.cust_contact "Account Level Contact"
       ,(select name from hr_operating_units where organization_id = hcasa.org_id) "Operating Unit"
       ,hps.party_site_number "Bill To Site Number"
       ,(SELECT address1||','||address2||','||address3||','|| address4||','|| city||','|| state||','||postal_code
           FROM hz_locations
          WHERE location_id = hps.location_id) "Bill To Address"
       , acct_contact.email_address "Contact Email Address"
         ,DECODE((select distinct responsibility_type from
         hz_cust_account_roles a, hz_role_responsibility b, hz_cust_acct_sites_all c
         where
          a.cust_account_id = c.cust_account_id
         and a.cust_account_role_id = b.cust_account_role_id
         and a.cust_account_role_id = acct_contact.cust_account_role_id
         and responsibility_type = 'INV'),'INV','Y','N' ) "Invoice Role"
            ,acct_contact.contact_type "Contact Type"
        FROM hz_parties hp,
        hz_party_sites hps,
        hz_cust_accounts_all hca,
        hz_cust_acct_sites_all hcasa,
        (select hp.party_name Cust_contact
              , hcar.cust_account_id
              , hcar.cust_account_role_id
              , NVL(rel_hp.EMAIL_ADDRESS, hoc.attribute7) EMAIL_ADDRESS
              , hoc.attribute6 contact_type
            FROM hz_parties hp,
                   hz_parties rel_hp,
                   hz_person_profiles hpp,
                   hz_relationships hr,
                   hz_org_contacts hoc,
                   hz_cust_account_roles hcar
             WHERE hoc.party_relationship_id = hr.relationship_id
               AND hr.subject_id             = hp.party_id
               AND rel_hp.party_id           = hr.party_id
               AND hp.party_id               = hpp.party_id
               AND hpp.effective_end_date IS NULL
               AND rel_hp.party_id           = hcar.party_id
               AND hoc.party_relationship_id = hr.relationship_id
               AND hr.subject_table_name     = 'HZ_PARTIES'
               AND hr.subject_type           = 'PERSON'
               AND hr.relationship_code      = 'CONTACT_OF'
               AND hcar.cust_acct_site_id   is null ) Acct_contact
  WHERE hp.party_id = hps.party_id
    AND hp.party_id = hca.party_id
    AND hcasa.party_site_id = hps.party_site_id
    AND hca.cust_account_id = hcasa.cust_account_id
    AND hca.account_number = :lv_account_number
    AND hcasa.cust_acct_site_id in (SELECT hcsua.cust_acct_site_id
           FROM hz_cust_site_uses_all hcsua
          WHERE hcsua.cust_acct_site_id =
                                     hcasa.cust_acct_site_id
            AND hcsua.site_use_code = 'BILL_TO')
    AND Acct_contact.cust_account_id = hca.cust_account_id;
---------------------------------------------------------------------------------------------------------------------------