This provides the Customer Address of an Order

This provides the Customer Address of an Order

Modules Involved : OM
Purpose    : This provides the Customer Address of an Order
Description      : We can get the BILL_TO and SHIP_TO addresses of a given order.
Needs two parmeters(ORDER_NUMBER,ORG_ID) and It provides Bill_To and Ship_To address in two different lines
****************************************************************** */
SELECT OOHA.ORDER_NUMBER
, HP.PARTY_NAME
, HCSUA.SITE_USE_CODE
, HCSUA.location
, hl.ADDRESS1||','||hl.ADDRESS2||' '||hl.ADDRESS3||' '||hl.ADDRESS4||' '||hl.CITY||','||hl.STATE||','
  ||hl.POSTAL_CODE||','||hl.country ADDRESS
FROM OE_ORDER_HEADERS_ALL OOHA
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES_ALL HCASA
, HZ_CUST_SITE_USES_ALL  HCSUA
, HZ_LOCATIONS HL
WHERE 1=1
and ORDER_NUMBER = :ORDER_NUMBER
and OOHA.ORG_ID = :ORG_ID
and (OOHA.INVOICE_TO_ORG_ID = HCSUA.SITE_USE_ID or OOHA.SHIP_TO_ORG_ID = HCSUA.SITE_USE_ID)
--AND OOHA.ORG_ID = HCSUA.ORG_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HP.PARTY_ID = HPS.PARTY_ID
and HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
and HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
and HCSUA.SITE_USE_CODE in ('BILL_TO', 'SHIP_TO')
--AND HCSUA.PRIMARY_FLAG = 'Y'
and HPS.LOCATION_ID  = HL.LOCATION_ID;