eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WSH_DELIVERY_AUTOCREATE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 196


SELECT          NULL,
                NULL,
                wdd.delivery_detail_id,
                NULL,
                wdd.released_status,
                NULL,
                wdd.ship_to_location_id,
                wdd.ship_from_location_id,
                wdd.customer_id,
                wdd.intmed_ship_to_location_id,
                wdd.fob_code,
                wdd.freight_terms_code,
                wdd.ship_method_code,
                wdd.carrier_id,
                wdd.source_header_id,
                wdd.deliver_to_location_id,
                wdd.organization_id,
                wdd.date_scheduled,
                wdd.date_requested,
                wda.delivery_id,
                NVL(wdd.ignore_for_planning, 'N') ignore_for_planning, --J TP Release
                NVL(wdd.line_direction,'O') line_direction,   -- J-IB-NPARIKH
                wdd.shipping_control,   -- J-IB-NPARIKH
                wdd.vendor_id,   -- J-IB-NPARIKH
                wdd.party_id,   -- J-IB-NPARIKH
                wdd.mode_of_transport,
                wdd.service_level,
                wdd.lpn_id,
                wdd.inventory_item_id,
                wdd.source_code,
                wdd.container_flag,
                NULL,
                NULL,
                NULL  -- X-dock, is_xdocked_flag
FROM      wsh_delivery_details wdd,
          wsh_delivery_assignments_v wda
WHERE     wdd.delivery_detail_id = p_dd_id AND
          wdd.released_status <> 'D'AND
          wda.delivery_detail_id  = wdd.delivery_detail_id;

Line: 237


SELECT          NULL,
                NULL,
                wnd.delivery_id,
                NULL,
                wnd.status_code,
                NULL,
                wnd.ultimate_dropoff_location_id,
                wnd.initial_pickup_location_id,
                wnd.customer_id,
                wnd.intmed_ship_to_location_id,
                wnd.fob_code,
                wnd.freight_terms_code,
                wnd.ship_method_code,
                wnd.carrier_id,
                wnd.source_header_id,
                NULL,
                wnd.organization_id,
                wnd.initial_pickup_date,
                wnd.ultimate_dropoff_date,
                wnd.delivery_id,
                NVL(wnd.ignore_for_planning, 'N') ignore_for_planning, --J TP Release
                NVL(wnd.shipment_direction,'O') line_direction,   -- J-IB-NPARIKH
                wnd.shipping_control,   -- J-IB-NPARIKH
                wnd.vendor_id,   -- J-IB-NPARIKH
                wnd.party_id,   -- J-IB-NPARIKH
                wnd.mode_of_transport,
                wnd.service_level,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL  -- X-dock, is_xdocked_flag
FROM      wsh_new_deliveries wnd
WHERE     wnd.delivery_id = p_del_id;

Line: 595


Procedure Create_Update_Hash(p_delivery_rec IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
                             x_return_status OUT NOCOPY varchar2) IS

l_grp_attr_tab grp_attr_tab_type;

Line: 603


l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_HASH';

Line: 673


        WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_AUTOCREATE.Create_Update_Hash');

Line: 686


END Create_Update_Hash;

Line: 898


       select WSH_MATCH_GROUP_S.nextval into p_group_tab(l_group_index).group_id from dual;

Line: 1022


select delivery_id
from   wsh_new_deliveries wnd
where wnd.hash_value  = p_hash_value
and   wnd.hash_string = p_hash_string
and   wnd.organization_id = p_organization_id
and   (NVL(wnd.planned_flag, 'N') = 'N')
and   NVL(NVL(wnd.carrier_id, p_carrier_id),-1)
                     = NVL(NVL(p_carrier_id, wnd.carrier_id), -1)
and   NVL(NVL(wnd.service_level, p_service_level), -1)
                     = NVL(NVL(p_service_level, wnd.service_level), -1)
and   NVL(NVL(wnd.mode_of_transport, p_mode_of_transport), -1)
                     = NVL(NVL(p_mode_of_transport, wnd.mode_of_transport), -1)
and   NVL(wnd.ship_method_code,-1) = NVL(decode(p_ship_method_grp_flag,'Y',p_ship_method_code,wnd.ship_method_code),-1)
and   wnd.status_code in ('OP', 'SA');

Line: 1049


select delivery_id
from   wsh_new_deliveries wnd
where wnd.hash_value      = p_hash_value
and   wnd.hash_string     = p_hash_string
and   wnd.organization_id = p_organization_id
and   wnd.customer_id     = p_customer_id
and   (NVL(wnd.planned_flag, 'N') = 'N')
and   NVL(NVL(wnd.carrier_id, p_carrier_id),-1)
                     = NVL(NVL(p_carrier_id, wnd.carrier_id), -1)
and   NVL(NVL(wnd.service_level, p_service_level), -1)
                     = NVL(NVL(p_service_level, wnd.service_level), -1)
and   NVL(NVL(wnd.mode_of_transport, p_mode_of_transport), -1)
                     = NVL(NVL(p_mode_of_transport, wnd.mode_of_transport), -1)
and   NVL(wnd.ship_method_code,-1) = NVL(decode(p_ship_method_grp_flag,'Y',p_ship_method_code,wnd.ship_method_code),-1)
and   wnd.status_code in ('OP', 'SA');

Line: 1075


select delivery_id
from wsh_new_deliveries wnd
where wnd.hash_value = p_hash_value
and   wnd.hash_string = p_hash_string
and   wnd.batch_id  = p_batch_id
and   (NVL(wnd.planned_flag, 'N') = 'N')
and   NVL(wnd.source_header_id, -1) = NVL(p_header_id, -1)
and   NVL(NVL(wnd.carrier_id, p_carrier_id), -1) = NVL(NVL(p_carrier_id, wnd.carrier_id), -1)
and   NVL(NVL(wnd.service_level, p_service_level), -1) = NVL(NVL(p_service_level, wnd.service_level), -1)
and   NVL(NVL(wnd.mode_of_transport, p_mode_of_transport), -1) = NVL(NVL(p_mode_of_transport, wnd.mode_of_transport), -1)
and   NVL(wnd.ship_method_code,-1) = NVL(decode(p_ship_method_grp_flag,'Y',p_ship_method_code,wnd.ship_method_code),-1)
and   wnd.status_code in ('OP', 'SA');

Line: 1090


select delivery_id
from wsh_new_deliveries d
where d.status_code IN ('OP','SA')
and   d.planned_flag = 'N'
and   NVL(p_ship_from_loc_id, nvl(d.initial_pickup_location_id, -1))
          = nvl(d.initial_pickup_location_id, -1)
and   NVL(p_organization_id, nvl(d.organization_id, -1))
          = nvl(d.organization_id, -1);

Line: 1100


SELECT container_flag, organization_id, ship_from_location_id, customer_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;

Line: 1376


       delete from wsh_tmp;

Line: 1379


       INSERT INTO wsh_tmp (id) VALUES(l_matched_entities(i));

Line: 1406


       l_query_string := 'select delivery_id '||
                         'from wsh_new_deliveries wnd '||
                         'where wnd.hash_value = :p_hash_value '||
                         'and   wnd.hash_string = :p_hash_string '||
                         'and   wnd.status_code = ''OP'' ';

Line: 1781


SELECT container_flag
FROM  wsh_delivery_details
WHERE delivery_detail_id = p_entity_id;

Line: 1792


select delivery_id, name, rowid
from wsh_new_deliveries wnd
where wnd.hash_value = p_hash_value
and   wnd.hash_string = p_hash_string
and   wnd.batch_id  = p_batch_id
and   (NVL(wnd.planned_flag, 'N') = 'N')
and   NVL(wnd.source_header_id, -1) = NVL(p_header_id, -1)
and   NVL(NVL(wnd.carrier_id, p_carrier_id), -1) = NVL(NVL(p_carrier_id, wnd.carrier_id), -1)
and   NVL(NVL(wnd.service_level, p_service_level), -1) = NVL(NVL(p_service_level, wnd.service_level), -1)
and   NVL(NVL(wnd.mode_of_transport, p_mode_of_transport), -1) = NVL(NVL(p_mode_of_transport, wnd.mode_of_transport), -1)
and   wnd.status_code in ('OP', 'SA');

Line: 1841


l_del_select_carrier  wsh_util_core.id_tab_type;

Line: 1955


  l_del_select_carrier.delete;

Line: 1956


  l_del_rate.delete;

Line: 1957


  l_del_rate_location.delete;

Line: 2332


          l_del_select_carrier(l_del_select_carrier.count+1) := l_delivery_id;

Line: 2442


       (l_delivery_rows.COUNT > 0) AND l_del_select_carrier.count > 0 ) THEN
      --
      -- no batch Id so this is not from pick release
      -- so lets try it
      --

      IF p_caller = 'WSH_AUTO_CREATE_DEL_TRIP' THEN
        l_caller := p_caller;

Line: 2453


        WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.PROCESS_CARRIER_SELECTION',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 2457


      WSH_NEW_DELIVERY_ACTIONS.PROCESS_CARRIER_SELECTION(p_delivery_id_tab => l_del_select_carrier,
                                                         p_batch_id        => null,
                                                         p_form_flag       => 'N',
                                                         p_caller          => l_caller,
                                                         x_return_message  => l_error_text,
                                                         x_return_status   => l_return_status);

Line: 2469


            WSH_DEBUG_SV.log(l_module_name,  'Return status from WSH_NEW_DELIVERY_ACTIONS.Process_Carrier_Selection', l_return_status  );

Line: 2491


      l_in_param_rec.seq_tender_flag  := 'Y'; -- R12 Select Carrier

Line: 2844


         SELECT organization_id
         INTO l_line_warehouse_ids(i).warehouse_id
         FROM wsh_delivery_details
         WHERE delivery_detail_id = p_line_rows(i);

Line: 2898


         l_del_rows.delete;

Line: 2954


         temp_ids.delete;

Line: 2987


      l_del_rows.delete;

Line: 3031


      temp_ids.delete;

Line: 3099


PROCEDURE Delete_Empty_Deliveries(p_batch_id      IN NUMBER,
                                  x_return_status OUT NOCOPY      VARCHAR2 ) IS
                                  --
l_debug_on         BOOLEAN;

Line: 3104


l_module_name      CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_EMPTY_DELIVERIES';

Line: 3148


      DELETE FROM wsh_new_deliveries wnd
      WHERE  batch_id = p_batch_id
      AND    NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)
             = WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
      AND    NOT EXISTS (
             SELECT 'x'
             FROM   wsh_delivery_assignments wda
             WHERE  wda.delivery_id = wnd.delivery_id
             AND    wda.delivery_id IS NOT NULL);

Line: 3159


      DELETE FROM wsh_new_deliveries wnd
      WHERE  batch_id = p_batch_id
      AND    NOT EXISTS (
             SELECT 'x'
             FROM   wsh_delivery_assignments wda
             WHERE  wda.delivery_id = wnd.delivery_id
             AND    wda.delivery_id IS NOT NULL);

Line: 3171


        WSH_DEBUG_SV.logmsg(l_module_name,  'DELETED '||SQL%ROWCOUNT||' EMPTY DELIVERIES AFTER PICK RELEASE'  );

Line: 3188


    wsh_util_core.default_handler('WSH_DELIVERY_AUTOCREATE.DELETE_EMPTY_DELIVERIES');

Line: 3198


END DELETE_EMPTY_DELIVERIES;

Line: 3240


SELECT delivery_id,
       parent_delivery_detail_id
FROM   wsh_delivery_assignments_v
where  delivery_detail_id = p_detail_id;

Line: 3299


         UPDATE wsh_delivery_details wdd
         SET    wdd.customer_id                = NULL,
                wdd.ship_to_location_id        = NULL,
                wdd.intmed_ship_to_location_id = NULL,
                wdd.fob_code                   = NULL,
                wdd.freight_terms_code         = NULL,
                wdd.ship_method_code           = NULL,
                wdd.deliver_to_location_id     = NULL
         WHERE  wdd.delivery_detail_id in (
                SELECT wda.delivery_detail_id
                FROM   wsh_delivery_assignments_v wda
                WHERE  wda.delivery_id = p_delivery_ids(l_counter)
                AND    wda.delivery_id IS NOT NULL
                AND    wda.delivery_detail_id not in (
                       SELECT wda1.delivery_detail_id
                       FROM   wsh_delivery_assignments_v wda1
                       START  WITH   wda1.delivery_detail_id in (
                                     SELECT wda2.delivery_detail_id
                                     FROM   wsh_delivery_details wdd1 ,
                                            wsh_delivery_assignments_v wda2
                                     WHERE  wda2.delivery_id = p_delivery_ids(l_counter)
                                     AND    wda2.delivery_detail_id = wdd1.delivery_detail_id
                                     AND    wdd1.container_flag = 'N')
                       CONNECT BY wda1.delivery_detail_id = prior wda1.parent_delivery_detail_id))
         AND     wdd.container_flag = 'Y'
         RETURNING delivery_detail_id, organization_id, line_direction, gross_weight,
                   net_weight, volume, filled_volume BULK COLLECT into l_del_det_id_tbl,
                   l_organization_id_tbl, l_line_direction_tbl, l_gross_weight_tbl,
                   l_net_weight_tbl, l_volume_tbl, l_filled_volume_tbl; -- LPN CONV. rv

Line: 3333


          WSH_DEBUG_SV.logmsg(l_module_name,  'UPDATED '||SQL%ROWCOUNT||' RECORDS IN WSH_DELIVERY_DETAILS'  );

Line: 3362


                l_sync_tmp_wms_recTbl.operation_type_tbl(l_cnt_wms_counter) := 'UPDATE';

Line: 3369


                l_sync_tmp_inv_recTbl.operation_type_tbl(l_cnt_inv_counter) := 'UPDATE';

Line: 3392


          l_operation_type := 'UPDATE';

Line: 3575


        UPDATE WSH_DELIVERY_ASSIGNMENTS_V
        SET DELIVERY_ID = NULL,
            PARENT_DELIVERY_DETAIL_ID = NULL
        WHERE DELIVERY_DETAIL_ID = l_del_det_id_tbl(l_counter);

Line: 3581


      WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
                       p_detail_id_tab   => l_del_det_id_tbl,
                       x_return_status   => x_return_status);

Line: 3589


         UPDATE wsh_delivery_assignments wda
         SET    wda.parent_delivery_detail_id = null,
                wda.delivery_id               = null
         WHERE  wda.delivery_id = p_delivery_ids(l_counter)
         AND    wda.delivery_id IS NOT NULL
         AND    wda.delivery_detail_id not in(
                SELECT wda1.delivery_detail_id
                FROM   wsh_delivery_assignments wda1
                START  WITH   wda1.delivery_detail_id in (
                       SELECT wda2.delivery_detail_id
                       FROM   wsh_delivery_details wdd ,
                              wsh_delivery_assignments wda2
                       WHERE  wda2.delivery_id = wda.delivery_id
                       AND    wda2.delivery_detail_id = wdd.delivery_detail_id
                       AND    wdd.container_flag = 'N')
                CONNECT BY wda1.delivery_detail_id = prior wda1.parent_delivery_detail_id);

Line: 3611


          WSH_DEBUG_SV.logmsg(l_module_name,  'UPDATED '||SQL%ROWCOUNT||' RECORDS IN WSH_DELIVERY_ASSIGNMENTS'  );

Line: 3801


delete from wsh_tmp;

Line: 3833


 SELECT s1.trip_id,
        NVL(d.ignore_for_planning, 'N')  --OTM R12, delivery ignore same as trip's
 FROM  wsh_delivery_legs l, wsh_trip_stops s1, wsh_new_deliveries d
 WHERE l.delivery_id = p_delivery_id
 AND   d.delivery_id = l.delivery_id
 AND   s1.stop_id = l.pick_up_stop_id
 AND   s1.stop_location_id = d.initial_pickup_location_id;

Line: 3842


 SELECT wts.stop_id
 FROM wsh_trip_stops wts
 WHERE wts.trip_id = p_trip_id
 AND NOT EXISTS (
  SELECT wdl.delivery_leg_id
  FROM wsh_delivery_legs wdl
  WHERE wdl.pick_up_stop_id = wts.stop_id
  OR wdl.drop_off_stop_id = wts.stop_id
  AND rownum = 1);

Line: 3998


      l_del_tab.delete;

Line: 4213


        WSH_UTIL_CORE.Delete(p_type => 'STOP',
                       p_rows => l_empty_stops_tab,
                       x_return_status => l_return_status);

Line: 4300


     update wsh_trips set
     name =  p_trip_prefix ||'-'|| name
     where trip_id = l_trip_id_tab(1);