Query to display BOL, MBOL, Trip details for a Sales Order

Query to display BOL, MBOL, Trip details for a Sales Order

SELECT   ooh.order_number
       , wnd.NAME delivery_name
       , wt.NAME trip_name
       , ool.line_number
       , ool.ordered_item
       , ool.flow_status_code
       , DECODE (wdd.released_status
               , 'R', 'Ready For Release'
               , 'B', 'Back Ordered'
               , 'S', 'Released To Warehouse'
               , 'D', 'Cancelled'
               , 'N', 'Not Ready For Release'
               , 'Y', 'Staged or Pick Confirmed'
               , 'C', 'Interfaced/Shipped'
               , 'I', 'Interfaced/Shipped'
               , 'O', 'Not Shipped'
                ) delivery_status
       , rct.trx_number invoice_number
       , wdd.released_status
       , ood.organization_name || ' (' || ood.organization_code || ')' ship_from_org
       , hp_carrier.party_name carrier_name
       , wdi.sequence_number bol_number
       , wds.departure_net_weight ship_weight
       , wds.actual_departure_date ship_date
       , ooh.cust_po_number
       , SUBSTR (hp.party_name, 1, 30) || ' ' ||
       SUBSTR (hl_ship.address1, 1, 36) || ' ' ||
       SUBSTR (hl_ship.address2, 1, 36) || ' ' ||
       SUBSTR (hl_ship.city, 1, 30) || ' ' ||
       SUBSTR (hl_ship.province , 1 , 2 ) || ' ' ||
       SUBSTR (hl_ship.postal_code , 1 , 8 ) ship_to_address
FROM     apps.hr_locations hl
       , org_organization_definitions ood
       , apps.oe_order_headers_all ooh
       , oe_order_lines_all ool
       , apps.hz_locations hl_ship
       , apps.hz_parties hp
       , apps.hz_party_sites hps
       , apps.hz_cust_acct_sites_all hcas
       , apps.hz_cust_site_uses_all hcsu
       , apps.hz_party_sites hps_bill
       , apps.hz_cust_acct_sites_all hcas_bill
       , apps.hz_cust_site_uses_all hcsu_bill
       , apps.wsh_delivery_details wdd
       , apps.wsh_new_deliveries wnd
       , apps.wsh_delivery_assignments wda
       , apps.wsh_trips wt
       , apps.wsh_delivery_legs wdl
       , apps.wsh_trip_stops wds
       , apps.wsh_document_instances wdi
       , apps.hz_parties hp_carrier
       , ra_customer_trx_all rct
WHERE    1 = 1
AND      ooh.header_id = ool.header_id
AND      ood.organization_id = ool.ship_from_org_id
AND      ooh.ship_from_org_id = hl.inventory_organization_id
AND      hl_ship.location_id = hps.location_id
AND      hp.party_id = hps.party_id
AND      hps.party_site_id = hcas.party_site_id
AND      hps_bill.party_site_id = hcas_bill.party_site_id
AND      hps_bill.party_id = hp.party_id
AND      hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND      hcsu.site_use_id = ooh.ship_to_org_id
AND      hcas_bill.cust_acct_site_id = hcsu_bill.cust_acct_site_id
AND      hcsu_bill.site_use_id = ooh.invoice_to_org_id
AND      ooh.header_id = wdd.source_header_id(+)
AND      wda.delivery_detail_id(+) = wdd.delivery_detail_id
AND      wda.delivery_id = wnd.delivery_id(+)
AND      ool.line_id = wdd.source_line_id
AND      wt.trip_id(+) = wds.trip_id
AND      wds.stop_id(+) = wdl.pick_up_stop_id
AND      wdl.delivery_id(+) = wnd.delivery_id
AND      hp_carrier.party_id(+) = wt.carrier_id
AND      wdi.entity_id(+) = wdl.delivery_leg_id
AND      wdi.entity_name(+) = 'WSH_DELIVERY_LEGS'
AND      TO_CHAR (ooh.order_number) = rct.ct_reference(+)
AND      TO_CHAR (ooh.order_number) = rct.interface_header_attribute1(+)
AND      interface_header_context(+) = 'ORDER ENTRY'
AND      ooh.order_number = :order_number
ORDER BY ool.flow_status_code
       , ooh.order_number
       , ool.line_number