Sunday, March 20, 2016

Query to get the Order number,party name, ship to address and bill to address details


SELECT ooh.order_number,hpb.party_name
     ,hcsuas.location||','|| hls.address1
      ||','||hls.address2
      ||','||hls.address3
      ||','||hls.address4
      ||','||hls.city   
      ||','||hls.state 
       ||','||hls.country
      ||','||hls.postal_code ship_to_address
     , hcsuab.location||','||hlb.address1
      ||','||hlb.address2
      ||','||hlb.address3
      ||','||hlb.address4
      ||','||hlb.city   
      ||','||hlb.state
       ||','||hlb.country
      ||','||hlb.postal_code bill_to_address
FROM   oe_order_headers_all ooh
     , hz_cust_site_uses_all hcsuas
     , hz_cust_acct_sites_all hcasas
     , hz_party_sites hpss
     , hz_parties hps
     , hz_locations hls
     , hz_cust_site_uses_all hcsuab
     , hz_cust_acct_sites_all hcasab
     , hz_party_sites hpsb
     , hz_parties hpb
     , hz_locations hlb
WHERE  1 = 1
and ooh.order_number=45632
AND    ooh.ship_to_org_id = hcsuas.site_use_id
AND    hcsuas.cust_acct_site_id = hcasas.cust_acct_site_id
AND    hcasas.party_site_id = hpss.party_site_id
AND    hpss.party_id = hps.party_id
AND    hpss.location_id = hls.location_id
and    hcsuas. site_use_code='SHIP_TO'
AND    ooh.invoice_to_org_id = hcsuab.site_use_id
AND    hcsuab.cust_acct_site_id = hcasab.cust_acct_site_id
AND    hcasab.party_site_id = hpsb.party_site_id
AND    hpsb.party_id = hpb.party_id
AND    HPSB.LOCATION_ID = HLB.LOCATION_ID
and hcsuab. site_use_code='BILL_TO';

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect