Sold To, Ship To and Bill To identifiers for a customer.sql

SELECT
	hzp.party_name || ' ' || hzp.party_number
  , hzp.party_id
  , hzps.PARTY_SITE_NUMBER "ShipToPartySiteNumber"
  , HZA.account_number
  , HZA.Account_name
  , hza.CUST_ACCOUNT_ID
  , HZA.status "Account Status"
  , hzp.status "Party Status"
  , hzps.status "Party Site Status"
  , hzps.party_site_id "PARTY SITE ID - for SHIP_TO"
  , hzcasa.status "Account Site Status"
  , hzcsua.site_use_id "Account Site ID - for BILL_TO"
  , hzcasa.start_Date
  , hzcasa.end_Date
  , hzcsua.SITE_USE_CODE
  , hzcasa.BILL_TO_FLAG
  , hzcasa.SHIP_TO_FLAG
  , hzcsua.PRIMARY_FLAG
  , hzcsua.STATUS "Account Site USE Status"
  , hzcsua.LOCATION
  , hzl.ADDRESS1
  , hzl.ADDRESS2
  , hzl.ADDRESS3
  , hzl.ADDRESS4
  , hzl.CITY
  , hzl.POSTAL_CODE
  , hzl.STATE
  , hzl.COUNTRY
  , hzl.LOCATION_ID
FROM
	fusion.HZ_PARTIES             HZP
  , fusion.HZ_PARTY_SITES         hzps
  , fusion.HZ_CUST_ACCOUNTS       HZA
  , fusion.HZ_CUST_ACCT_SITES_ALL hzcasa
  , fusion.HZ_CUST_SITE_USES_ALL  hzcsua
  , fusion.hz_locations           HZL
WHERE
	hzP.party_id                 = HZA.party_id (+)
	AND hza.CUST_ACCOUNT_ID      = hzcasa.CUST_ACCOUNT_ID (+)
	AND hzcasa.party_site_id     = hzps.party_site_id (+)
	AND hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id (+)
	AND hzps.location_id         = hzl.location_id (+)
	--         and       (
	--                      hzcasa.start_Date    <= sysdate
	--                        OR hzcasa.start_Date IS NULL
	--                )
	--                AND
	--                (
	--                        hzcasa.end_date    >= sysdate
	--                        OR hzcasa.end_date IS NULL
	--                )
	AND upper(hzP.party_name) LIKE upper(:Customer_Name)
ORDER BY
	hzp.party_number
  , hza.account_number
  , hzl.LOCATION_ID
;

-- Customer Contact Information:
SELECT DISTINCT
	hp.party_name "Customer Name"
  , hcsua.site_use_code
  , hca.account_number
  , hpc.person_first_name
  , hpc.person_last_name
  , hcp.contact_point_type
  , hcp.email_format
  , hcp.email_address
  , hcp.raw_phone_number
  , hcp.url
  , hcp.phone_area_code
  , hcp.phone_number
  , hcp.phone_extension
  , hcp.phone_line_type
  , hcp.contact_point_purpose
  , hcar.cust_account_role_id
  , HCA.STATUS "Account Status"
  , HCASA.STATUS "Account Site Status"
  , HCSUA.STATUS "Account Site Use Status"
  , HP.STATUS "Party Status"
  , HPS.STATUS "Party Site Status"
  , HR.STATUS "Relationship Status"
  , HCAR.STATUS "Customer Account Relationship Status"
  , HCP.STATUS "Contact Point Status"
FROM
	Fusion.hz_cust_accounts       hca
  , Fusion.hz_cust_acct_sites_all hcasa
  , Fusion.hz_cust_site_uses_all  hcsua
  , Fusion.hz_parties             hp
  , Fusion.hz_parties             hpc
  , Fusion.hz_party_sites         hps
  , Fusion.hz_relationships       hr
  , Fusion.hz_cust_account_roles  hcar
  , Fusion.hz_contact_points      hcp
  , Fusion.HZ_ROLE_RESPONSIBILITY hrr
WHERE
	hca.cust_account_id         = hcar.cust_account_id
	AND hcar.relationship_id    = hr.relationship_id
	AND hr.relationship_id      = hcp.relationship_id
	AND hcasa.cust_account_id   = hca.cust_account_id
	AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
	AND hcar.cust_acct_site_id  = hcasa.cust_acct_site_id
	AND hcasa.party_site_id     = hps.party_site_id
	AND hps.party_id            = hca.party_id
	--AND hcsua.site_use_code = 'SHIP_TO'
	AND hca.party_id              = hp.party_id
	AND hr.object_type            = 'PERSON'
	AND hr.object_table_name      = 'HZ_PARTIES'
	AND hr.object_id              = hpc.party_id
	AND hcar.cust_account_role_id = hrr.cust_account_role_id
	-- AND hrr.responsibility_type IN ('SHIP_TO') -- Contact Responsibility - SHIP_TO
	-- AND hca.account_number = ':Account Number' -- Search by Account Number
	--AND hps.party_site_number = ':Site Number'
	AND upper(hp.party_name) LIKE :Customer_Name ||'%'
ORDER BY
	hp.party_name
  , hcsua.site_use_code