Order Management Delivery Details SQL script

Overview:

  By using below SQL script you get all records (Open and Closed) of Delivery Details of Order management.


SELECT DISTINCT wnd.name "Delivery number",
             party.party_name "Consignee",
             (wloc1.location_code||' '||wloc1.address1||' '||wloc1.city||' '||wloc1.postal_code||' '||wloc1.country) "Ultimate Ship to",
             (SELECT meaning
      FROM fnd_lookup_values
              WHERE UPPER(lookup_type) = 'DELIVERY_STATUS'
                    AND lookup_code = wnd.status_code
                    AND language = 'US') "Delivery Status",
             (SELECT meaning
              FROM fnd_lookup_values
              WHERE UPPER(lookup_type) = 'DELIVERY_PLANNED_FLAG'
                    AND lookup_code = wnd.planned_flag
                    AND language = 'US') "Firm Status",
             wnd.attribute1 "Exceptions",
             (wloc2.location_code||' '||wloc2.address1||' '||wloc2.city||' '||wloc2.postal_code||' '||wloc2.country)  "Initial Ship from",
             wnd.initial_pickup_date "Initial Pick-Up Date",
             ood.organization_code "Org Code",
            (SELECT meaning
             FROM fnd_lookup_values
             WHERE lookup_type ='FREIGHT TERMS'
                  AND language = 'US'
                  AND lookup_code = wnd.FREIGHT_TERMS_CODE) "Freight Terms",
            (SELECT meaning
             FROM fnd_lookup_values
             WHERE lookup_type ='FOB'
                  AND language = 'US'
                  AND lookup_code = wnd.FOB_CODE
                  AND rownum = 1) "FOB",
          wnd.gross_weight      "Gross Weight"
          (wnd.gross_weight - wnd.net_weight) "Tare Weight",
          wnd.net_weight        "Net Weight"
          wnd.ship_method_code  "Ship Method",
          carri.carrier_name    "Carrier",
          wnd.service_level     "Service Level",
          wnd.mode_of_transport "Mode of Transport",
          wnd.number_of_lpn     "Number of LPNs",
          wnd.routing_instructions "Routing Instructions",
          wdoc.sequence_number     "Packing Slip"
    FROM wsh_new_deliveries       wnd,
         wsh_delivery_assignments wda,
         wsh_delivery_details    wdd,
         oe_order_headers_all    ooha,
         oe_order_lines_all      oola,
         hz_cust_accounts        cust_acct,
         hz_parties              party,
         org_organization_definitions ood,
         wsh_carriers_v         carri,
         wsh_document_instances wdoc,
         wsh_locations          wloc1,
         wsh_locations          wloc2
   WHERE     ooha.sold_to_org_id    = cust_acct.cust_account_id
         AND cust_acct.party_id     = party.party_id
         AND wnd.delivery_id        = wda.delivery_id
         AND wda.delivery_detail_id = wdd.delivery_detail_id
         AND ooha.header_id         = wdd.source_header_id
         AND oola.line_id           = wdd.source_line_id
         AND wnd.carrier_id         = carri.carrier_id
         AND wnd.ultimate_dropoff_location_id = wloc1.wsh_location_id(+)
         AND wnd.initial_pickup_location_id   = wloc2.wsh_location_id(+)
         AND wnd.delivery_id        = wdoc.entity_id(+)
         AND wdoc.entity_name(+)    = 'WSH_NEW_DELIVERIES'
         AND wdoc.document_type(+)  = 'PACK_TYPE'
         AND wdd.org_id             = 235   -- Operating Unit wise records
         AND wnd.organization_id    = ood.organization_id
   ORDER BY wnd.name;