Wednesday, June 10, 2015

Oracle Sales Order Joins with Shipping details and Customer data

select
        ship_hzl.address1  "ShipTo Address"
       ,(ship_hzl.city || ',' || ship_hzl.postal_code || ',' || ship_hzl.state || ',' || ship_hzl.country ) "Ship-To Detailed Information"
       ,ship_hcp.phone_area_code "Area Code"
       ,ship_hcp.phone_number
       ,ship_hcp.email_address
       ,dlvr_hzl.address1  "Deliver to Address"
       ,(dlvr_hzl.city || ',' || dlvr_hzl.postal_code || ',' || dlvr_hzl.state || ',' || dlvr_hzl.country ) "Dlvr-To Detailed Information"
       ,dlvr_hcp.phone_area_code "Area Code"
       ,dlvr_hcp.phone_number    "Deliver to PhoneNumber"
       ,dlvr_hcp.email_address  "Deliver to EmailAddress"
       ,ool.ordered_item        "Ordered Item Number"
       ,ool.shipped_quantity    "Shipped Quantity"
       ,(wdd.source_header_number || '/' || wdd.source_line_number) "Order Number / Line Number"
       ,ool.shipment_priority_code "Shipment Priority Code"
       ,ool.shipping_method_code   "Shipping Method Code"
       ,ool.freight_terms_code     "Freigt Terms Code"
       ,ool.freight_carrier_code   "Freight Carrier Code"
       ,ool.fob_point_code         "FOB Code"
       ,ool.shipping_instructions  "Shipping Instructions"
       ,ool.packing_instructions   "Packing Instructions"
from
     oe_order_headers_all       ooh
     ,oe_order_lines_all        ool
     ,wsh_delivery_details      wdd
     ,hz_cust_accounts_all      hcaa
     ,hz_parties                hzp
     ,hz_cust_site_uses_all    ship_hcsu
     ,hz_cust_acct_sites_all   ship_hcas
     ,hz_party_sites           ship_hps
     ,hz_locations             ship_hzl
     ,hz_contact_points        ship_hcp
     ,hz_cust_site_uses_all    dlvr_hcsu
     ,hz_cust_acct_sites_all   dlvr_hcas
     ,hz_party_sites           dlvr_hps
     ,hz_locations             dlvr_hzl
     ,hz_contact_points        dlvr_hcp
     ,hz_cust_accounts_all    sold_hcaa
     ,hz_parties              sold_hzp
     ,hz_party_sites          sold_hps
     ,hz_locations            sold_hzl
where ooh.header_id                   = ool.header_id
  and ooh.header_id                   = wdd.source_header_id
  and ool.line_id                     = wdd.SOURCE_LINE_ID
  and ooh.sold_to_org_id              = hcaa.cust_account_id ---customer name
  and hcaa.party_id                = hzp.party_id
  and ool.ship_to_org_id           = ship_hcsu.site_use_id -- ship to address
  and ship_hcsu.cust_acct_site_id  = ship_hcas.cust_acct_site_id
  and ship_hcas.party_site_id      = ship_hps.party_site_id 
  and ship_hps.location_id         = ship_hzl.location_id
  and hzp.party_id                 = ship_hcp.owner_table_id -- Ship to contact information
  and ship_hcp.email_format        ='MAILHTML'
  and ool.deliver_to_org_id        = dlvr_hcsu.site_use_id   -- Deliver to Address
  and dlvr_hcsu.cust_acct_site_id  = dlvr_hcas.cust_acct_site_id
  and dlvr_hcas.party_site_id      = dlvr_hps.party_site_id
  and dlvr_hps.location_id         = dlvr_hzl.location_id
  and hzp.party_id                 = dlvr_hcp.owner_table_id -- Deliver to Contact Information
  and dlvr_hcp.email_format        = 'MAILHTML'
  and ool.sold_to_org_id           = sold_hcaa.cust_account_id
  and sold_hcaa.party_id           = sold_hzp.party_id
  and sold_hzp.party_id            = sold_hps.party_id
  and sold_hps.identifying_address_flag = 'Y'
  and sold_hps.location_id         = sold_hzl.location_id
  and ool.flow_status_code         = 'SHIPPED'
  and wdd.released_status          = 'C';


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect