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: 245


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
                wdd.client_id, -- LSP PROJECT
                nvl(wdd.consignee_flag, 'C')  -- RTV changes
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: 288


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
                wnd.client_id,  -- LSP PROJECT
                nvl(wnd.consignee_flag, 'C')   -- RTV changes
FROM      wsh_new_deliveries wnd
WHERE     wnd.delivery_id = p_del_id;

Line: 665


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: 673


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

Line: 747


        WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_AUTOCREATE.Create_Update_Hash');

Line: 760


END Create_Update_Hash;

Line: 975


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

Line: 1100


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: 1128


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')
and   NVL(wnd.consignee_flag,'C') = NVL(p_consignee_flag,'C');  --RTV changes

Line: 1155


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: 1179


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')
and   NVL(wnd.client_id,-1) = NVL(p_client_id,-1);

Line: 1208


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')
and   NVL(wnd.client_id,-1) = NVL(p_client_id,-1);

Line: 1236


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')
and   NVL(wnd.client_id,-1) = NVL(p_client_id,-1);

Line: 1255


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: 1265


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

Line: 1601


       delete from wsh_tmp;

Line: 1604


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

Line: 1631


       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: 2011


SELECT container_flag
FROM  wsh_delivery_details
WHERE delivery_detail_id = p_entity_id;

Line: 2022


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: 2071


l_del_select_carrier  wsh_util_core.id_tab_type;

Line: 2190


  l_del_select_carrier.delete;

Line: 2191


  l_del_rate.delete;

Line: 2192


  l_del_rate_location.delete;

Line: 2582


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

Line: 2692


       (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: 2703


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

Line: 2707


      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: 2719


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

Line: 2741


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

Line: 3094


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

Line: 3152


         l_del_rows.delete;

Line: 3208


         temp_ids.delete;

Line: 3245


      l_del_rows.delete;

Line: 3289


      temp_ids.delete;

Line: 3357


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

Line: 3362


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

Line: 3406


      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: 3417


      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: 3429


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

Line: 3446


    wsh_util_core.default_handler('WSH_DELIVERY_AUTOCREATE.DELETE_EMPTY_DELIVERIES');

Line: 3456


END DELETE_EMPTY_DELIVERIES;

Line: 3498


SELECT delivery_id,
       parent_delivery_detail_id
FROM   wsh_delivery_assignments_v
where  delivery_detail_id = p_detail_id;

Line: 3557


         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,
                wdd.client_id                  = NULL -- LSP PROJECT
         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: 3592


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

Line: 3621


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

Line: 3628


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

Line: 3651


          l_operation_type := 'UPDATE';

Line: 3834


        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: 3840


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

Line: 3848


         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: 3870


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

Line: 4060


delete from wsh_tmp;

Line: 4092


 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: 4101


 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: 4257


      l_del_tab.delete;

Line: 4472


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

Line: 4559


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