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';