VIEW: APPS.OKI_HDR_ADDRESSES_V

Source

View Text - Preformatted

SELECT h.chr_id ,h.contract_number ,'BILL_TO' address_use , OKCH.bill_to_site_use_id site_use_id , cac.account_name || ' / ' || hz.party_name party_account , su.location , hl.address1 , hl.address2 , hl.address3 , hl.city , hl.postal_code , hl.state , hl.country FROM HZ_CUST_ACCT_SITES_ALL CAS ,HZ_CUST_ACCOUNTS CAC ,HZ_LOCATIONS HL ,HZ_PARTY_SITES PS ,HZ_PARTIES HZ ,HZ_CUST_SITE_USES_ALL SU ,OKI_SALES_K_HDRS H ,OKC_K_HEADERS_B OKCH WHERE okch.id = h.chr_id and su.site_use_id = OKCH.BILL_TO_SITE_USE_ID and cas.cust_acct_site_id = su.cust_acct_site_id and cac.cust_account_id = cas.cust_account_id and hz.party_id = cac.party_id and ps.party_site_id = cas.party_site_id and hl.location_id = ps.location_id UNION SELECT h.chr_id ,h.contract_number ,'SHIP_TO' address_use , OKCH.ship_to_site_use_id site_use_id , cac.account_name || ' / ' || hz.party_name party_account , su.location , hl.address1 , hl.address2 , hl.address3 , hl.city , hl.postal_code , hl.state , hl.country FROM HZ_CUST_ACCT_SITES_ALL CAS ,HZ_CUST_ACCOUNTS CAC ,HZ_LOCATIONS HL ,HZ_PARTY_SITES PS ,HZ_PARTIES HZ ,HZ_CUST_SITE_USES_ALL SU ,OKI_SALES_K_HDRS H ,OKC_K_HEADERS_B OKCH WHERE okch.id = h.chr_id and su.site_use_id = OKCH.SHIP_TO_SITE_USE_ID and cas.cust_acct_site_id = su.cust_acct_site_id and cac.cust_account_id = cas.cust_account_id and hz.party_id = cac.party_id and ps.party_site_id = cas.party_site_id and hl.location_id = ps.location_id

View Text - HTML Formatted

SELECT H.CHR_ID
, H.CONTRACT_NUMBER
, 'BILL_TO' ADDRESS_USE
, OKCH.BILL_TO_SITE_USE_ID SITE_USE_ID
, CAC.ACCOUNT_NAME || ' / ' || HZ.PARTY_NAME PARTY_ACCOUNT
, SU.LOCATION
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.CITY
, HL.POSTAL_CODE
, HL.STATE
, HL.COUNTRY
FROM HZ_CUST_ACCT_SITES_ALL CAS
, HZ_CUST_ACCOUNTS CAC
, HZ_LOCATIONS HL
, HZ_PARTY_SITES PS
, HZ_PARTIES HZ
, HZ_CUST_SITE_USES_ALL SU
, OKI_SALES_K_HDRS H
, OKC_K_HEADERS_B OKCH
WHERE OKCH.ID = H.CHR_ID
AND SU.SITE_USE_ID = OKCH.BILL_TO_SITE_USE_ID
AND CAS.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND CAC.CUST_ACCOUNT_ID = CAS.CUST_ACCOUNT_ID
AND HZ.PARTY_ID = CAC.PARTY_ID
AND PS.PARTY_SITE_ID = CAS.PARTY_SITE_ID
AND HL.LOCATION_ID = PS.LOCATION_ID UNION SELECT H.CHR_ID
, H.CONTRACT_NUMBER
, 'SHIP_TO' ADDRESS_USE
, OKCH.SHIP_TO_SITE_USE_ID SITE_USE_ID
, CAC.ACCOUNT_NAME || ' / ' || HZ.PARTY_NAME PARTY_ACCOUNT
, SU.LOCATION
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.CITY
, HL.POSTAL_CODE
, HL.STATE
, HL.COUNTRY
FROM HZ_CUST_ACCT_SITES_ALL CAS
, HZ_CUST_ACCOUNTS CAC
, HZ_LOCATIONS HL
, HZ_PARTY_SITES PS
, HZ_PARTIES HZ
, HZ_CUST_SITE_USES_ALL SU
, OKI_SALES_K_HDRS H
, OKC_K_HEADERS_B OKCH
WHERE OKCH.ID = H.CHR_ID
AND SU.SITE_USE_ID = OKCH.SHIP_TO_SITE_USE_ID
AND CAS.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND CAC.CUST_ACCOUNT_ID = CAS.CUST_ACCOUNT_ID
AND HZ.PARTY_ID = CAC.PARTY_ID
AND PS.PARTY_SITE_ID = CAS.PARTY_SITE_ID
AND HL.LOCATION_ID = PS.LOCATION_ID