隨手筆記

SELECT hcasa.org_id,
       role_acct.account_number,
       AR.CUSTOMER_NAME,
       hcsua.tax_reference,       
       party.person_first_name || party.person_middle_name || party.person_last_name contact_first_name,
       cont_point.phone_area_code || ' ' || cont_point.phone_number phone,
       cont_point.email_address,
       hcsua.site_use_code
  FROM hz_contact_points       cont_point,
       hz_cust_account_roles   acct_role,
       hz_parties              party,
       hz_parties              rel_party,
       hz_relationships        rel,
       hz_org_contacts         org_cont,
       hz_cust_accounts        role_acct,
       hz_contact_restrictions cont_res,
       hz_person_language      per_lang,
       hz_cust_acct_sites_all  hcasa,
       hz_cust_site_uses_all   hcsua,
       AR_CUSTOMERS            AR
 WHERE acct_role.party_id = rel.party_id
   and hcasa.Cust_acct_site_id = hcsua.Cust_acct_site_id
   and hcsua.site_use_code = 'BILL_TO'
   and acct_role.role_type = 'CONTACT'
   and org_cont.party_relationship_id = rel.relationship_id
   and rel.subject_id = party.party_id
   and rel_party.party_id = rel.party_id
   and cont_point.owner_table_id(+) = rel_party.party_id
   and cont_point.contact_point_type(+) IN ('EMAIL', 'PHONE')
   and cont_point.primary_flag(+) = 'Y'
   and acct_role.cust_account_id = role_acct.cust_account_id
   and acct_role.cust_account_id = AR.CUSTOMER_ID
   and role_acct.party_id = rel.object_id
   and party.party_id = per_lang.party_id(+)
   and per_lang.native_language(+) = 'Y'
   and party.party_id = cont_res.subject_id(+)
   and cont_res.subject_table(+) = 'HZ_PARTIES'
   and role_acct.cust_account_id = hcasa.cust_account_id
   and hcasa.cust_acct_site_id = acct_role.cust_acct_site_id
   and hcasa.org_id = :P_ORG_ID
  order by role_acct.account_number