Customer, SHIP_TO and BILL_TO Information on an Order.sql

-- The following SQL can be run from BI to obtain Customer information.
SELECT
	dha.ORDER_NUMBER
  , dha.source_order_number
  , dha.SOLD_TO_PARTY_ID
  , dha.STATUS_CODE
  , hz.PARTY_ID
  , hz.PARTY_NUMBER
  , hz.PARTY_NAME
FROM
	fusion.doo_headers_all dha
  , fusion.HZ_PARTIES      HZ
WHERE
	dha.SOURCE_ORDER_NUMBER = :SOURCE_ORDER_NUMBER
	--        AND status_code <> 'DOO_REFERENCE'
	--        AND Submitted_Flag = 'Y'            -- is this the active/submitted version
	and hz.PARTY_ID =dha.SOLD_TO_PARTY_ID
/
-- Find SHIP TO information on Order Header
SELECT
	SOURCE_ORDER_NUMBER
  , SOLD_TO_CUSTOMER_ID
  , SOLD_TO_PARTY_ID
  , HZP.PARTy_name || ' ' || HZP.PARTY_NUMBER "Sold to Customer"
  , DOA.ADDRESS_USE_TYPE
  , hza.account_number
  , hzp_ship_to.party_name
  , hza.account_name
  , doa.PARTY_SITE_ID
  , hzl.ADDRESS1
  , hzl.ADDRESS2
  , hzl.ADDRESS3
  , hzl.ADDRESS4
  , hzl.CITY
  , hzl.POSTAL_CODE
  , hzl.STATE
  , hzl.COUNTRY
FROM
	HZ_PARTIES             HZP
  , HZ_PARTIES             HZP_SHIP_TO
  , DOO_HEADERS_aLL        DHA
  , DOO_ORDER_ADDRESSES    DOA
  , HZ_CUST_ACCOUNTS       HZA
  , HZ_CUST_ACCT_SITES_ALL hzcasa
  , HZ_PARTY_SITES         hzps
  , hz_locations           HZL
WHERE
	HZP.PARTY_ID      = DHA.SOLD_TO_PARTY_ID
	AND dha.header_id = doa.header_id (+)
	AND
	(
		doa.ADDRESS_USE_TYPE          = 'SHIP_TO'
		OR doa.ADDRESS_USE_TYPE IS NULL
	)
	AND doa.party_site_id        = hzps.party_site_id (+)
	AND hzcasa.PARTY_SITE_ID (+) = hzps.PARTY_SITE_ID
	AND hzps.party_id            = hzp_ship_to.party_id (+)
	AND HZcasa.CUST_ACCounT_ID   = hza.CUST_ACCOUNT_ID (+)
	AND hzps.location_id         = hzl.location_id (+)
	AND DHA.SOURCE_ORDER_NUMBER  = :SOURCE_ORDER_NUMBER
	AND DHA.status_code         <> 'DOO_REFERENCE'
	AND DHA.Submitted_Flag       = 'Y' -- is this the active/submitted version
	/
-- Find BILL TO information on Order Header
SELECT
	SOURCE_ORDER_NUMBER
  , SOLD_TO_CUSTOMER_ID
  , SOLD_TO_PARTY_ID
  , HZP.PARTy_name || ' ' || HZP.PARTY_NUMBER "Sold to Customer"
  , DOA.ADDRESS_USE_TYPE
  , hza.account_number
  , hza.account_name
  , doa.CUST_ACCT_ID
  , doa.CUST_ACCT_SITE_USE_ID
  , hzl.ADDRESS1
  , hzl.ADDRESS2
  , hzl.ADDRESS3
  , hzl.ADDRESS4
  , hzl.CITY
  , hzl.POSTAL_CODE
  , hzl.STATE
  , hzl.COUNTRY
FROM
	FUSION.HZ_PARTIES             HZP
  , FUSION.DOO_HEADERS_aLL        DHA
  , fusion.DOO_ORDER_ADDRESSES    DOA
  , fusion.HZ_CUST_ACCOUNTS       HZA
  , fusion.HZ_CUST_SITE_USES_ALL  hzcsua
  , fusion.HZ_CUST_ACCT_SITES_ALL hzcasa
  , fusion.HZ_PARTY_SITES         hzps
  , fusion.hz_locations           HZL
WHERE
	HZP.PARTY_ID      = DHA.SOLD_TO_PARTY_ID
	AND dha.header_id = doa.header_id (+)
	AND
	(
		doa.ADDRESS_USE_TYPE          = 'BILL_TO'
		OR doa.ADDRESS_USE_TYPE IS NULL
	)
	AND DOA.CUST_ACCT_ID          = hza.CUST_ACCOUNT_ID (+)
	AND DOA.CUST_ACCT_SITE_USE_ID = hzcsua.SITE_USE_ID(+)
	AND hzcsua.CUST_ACCT_SITE_ID  = hzcasa.CUST_ACCT_SITE_ID (+)
	AND hzcasa.PARTY_SITE_ID      = hzps.PARTY_SITE_ID (+)
	AND hzps.location_id          = hzl.location_id (+)
	AND DHA.SOURCE_ORDER_NUMBER   = :SOURCE_ORDER_NUMBER
	--        AND DHA.status_code          <> 'DOO_REFERENCE'
	--        AND DHA.Submitted_Flag         = 'Y' -- is this the active/submitted version
	/
-- Find BILL TO  and SHIP TO information on Order Line
SELECT
	dha.Source_order_number
  , dha.order_number
  , dha.submitted_Flag
  , dfla.SHIP_TO_PARTY_ID
  , dfla.SHIP_TO_PARTY_SITE_ID
  , dfla.BILL_TO_CUSTOMER_ID
  , dfla.BILL_TO_SITE_USE_ID
FROM
	Fusion.DOO_headers_all       dha
  , Fusion.DOO_fulfill_lines_all dfla
WHERE
	dha.header_id               = dfla.header_id
	AND dha.source_order_number = :SOURCE_ORDER_NUMBER