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