ShipFromWithOrderNUmber

SELECT
       translate(hou.name, '"?', ' ')                                       ship_from_org
     , translate(hll.address_line_1, '"?', ' ')                             ship_from_address_line1
     , translate(hll.address_line_2, '"?', ' ')                             ship_from_address_line2
     , translate(hll.town_or_city, '"?', ' ')                               ship_from_town_or_city
     , translate(hll.region_2, '"?', ' ')                                   ship_from_state
     , translate(hll.postal_code, ',.-~`!@#$%^&*??<>:"?|}{() ', ' ')        ship_from_postal_code
     , translate(hll.country, '"?', ' ')                                    ship_from_country
     , translate(hll.telephone_number_1, ',.~`!@#$%^&*??<>:"?|}{ ', ' ')    ship_from_phone_number1
     , translate(hll.telephone_number_2, ',.-~`!@#$%^&*??<>:"?|}{() ', ' ') ship_from_phone_number2
FROM
       apps.wsh_delivery_details     wdd
     , apps.oe_order_headers_all     ooha
     , apps.oe_order_lines_all       oola
     , apps.wsh_new_deliveries       wnd
     , apps.wsh_delivery_assignments wda
     , apps.hz_cust_acct_sites_all   hcs
     , apps.hz_cust_site_uses_all    hsu
     , apps.hz_cust_site_uses_all    hsu1
     , apps.hz_cust_accounts         hca
     , apps.hz_parties               hp
     , apps.wsh_carrier_services     wcs
     , aasc_ship_method_mapping      asmm
     , apps.hr_locations_all         hll
     , apps.hr_organization_units    hou
     , apps.oe_transaction_types_tl  ottt
     , apps.fnd_territories          ft_shipto
     , apps.fnd_territories          ft_shipfrom
     , fnd_territories_tl            ft_shipto_na
     , --**dhl
       fnd_territories_tl ft_shipfrom_na
WHERE
       1                          = 1
       AND ooha.header_id         = oola.header_id
       AND oola.header_id         = wdd.source_header_id
       AND oola.line_id           = wdd.source_line_id
       AND wdd.delivery_detail_id = wda.delivery_detail_id
       AND wda.delivery_id        = wnd.delivery_id
       AND wdd.organization_id    = wnd.organization_id
       AND hsu.site_use_id        = oola.ship_to_org_id
       AND hsu1.site_use_id (+)   = nvl2(NULL, oola.ship_to_org_id, oola.deliver_to_org_id)
       AND hcs.cust_acct_site_id  = hsu.cust_acct_site_id
       AND hcs.cust_account_id    = hca.cust_account_id
       AND hca.party_id           = hp.party_id
       AND hcs.org_id             = hsu.org_id
       AND hcs.org_id             = oola.org_id
       AND wdd.released_status IN ('Y'
                                 ,'S')
       -- AND ooha.order_number = 72393
       AND wcs.ship_method_code (+)   = wnd.ship_method_code
       AND asmm.shipping_method (+)   = wnd.ship_method_code
       AND hll.location_id            = wdd.ship_from_location_id
       AND hou.organization_id        = wdd.organization_id
       AND ooha.org_id                = oola.org_id
       AND ottt.transaction_type_id   = ooha.order_type_id ft_shipfrom.territory_code = hll.country
       AND ft_shipto_na.language      = 'US'
       AND ft_shipfrom.territory_code = ft_shipfrom_na.territory_code--**dhl
       AND ft_shipfrom_na.language    = 'US'
       and wda.delivery_id            = :IP_DELIVERY_ID
       AND ROWNUM                     = 1
;