APPS.WSH_CONC_UTIL_PKG SQL Statements

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

Line: 28

select wc.freight_code, wc.carrier_id
from  wsh_carriers wc, wsh_carrier_services wcs
where
      wcs.ship_method_code = p_ship_method_code
 and  wcs.carrier_id  = wc.carrier_id;

Line: 37

select wcsm.ship_method_code , wcsm.organization_id , wcsm.freight_code
from wsh_carrier_ship_methods wcsm
where
 not exists (select 'x' from wsh_carriers wc,
                    wsh_carrier_services wcs
             where
                   wc.freight_code = wcsm.freight_code
              and  wc.carrier_id  = wcs.carrier_id
              and  wcs.ship_method_code = wcsm.ship_method_code);

Line: 101

    WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit ad_parallel_updates_pkg.initialize_rowid_range',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 103

 ad_parallel_updates_pkg.initialize_rowid_range(
           ad_parallel_updates_pkg.ROWID_RANGE,
           l_table_owner,
           l_table_name,
           l_script_name,
           l_worker_id,
           l_num_workers,
           l_batch_size, 0);

Line: 113

   WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit ad_parallel_updates_pkg.get_rowid_range',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 115

 ad_parallel_updates_pkg.get_rowid_range(
           l_start_rowid,
           l_end_rowid,
           l_any_rows_to_process,
           l_batch_size,
           TRUE);

Line: 152

        update /*+ ROWID (oel) */ oe_order_lines_all oel
        set oel.freight_carrier_code = l_new_freight_code
        , oel.last_updated_by = -2950631
        , oel.last_update_date = sysdate
        where oel.open_flag = 'N'
        and oel.rowid BETWEEN l_start_rowid AND l_end_rowid
        and nvl(oel.cancelled_flag, 'N') = 'N'
        and oel.shipping_interfaced_flag = 'Y'
        and oel.freight_carrier_code = v_notupg.freight_code
        and oel.shipping_method_code = v_notupg.ship_method_code;

Line: 168

        update  oe_order_headers_all oeh
        set oeh.freight_carrier_code = l_new_freight_code
        , oeh.last_updated_by = -2950631
        , oeh.last_update_date = sysdate
        where oeh.open_flag = 'N'
        and nvl(oeh.cancelled_flag, 'N') = 'N'
        and oeh.freight_carrier_code = v_notupg.freight_code
        and oeh.shipping_method_code = v_notupg.ship_method_code
        and exists (select /*+ ROWID (oel) */ 'x' from oe_order_lines_all oel      -- Getting Only Shpg. Interfaced Lines
                where oel.header_id  = oeh.header_id
                and oel.rowid BETWEEN l_start_rowid AND l_end_rowid
                and oel.open_flag = 'N'
                and nvl(oel.cancelled_flag, 'N') = 'N'
                and oel.SHIPPING_INTERFACED_FLAG = 'Y');

Line: 189

   ad_parallel_updates_pkg.processed_rowid_range(
          l_rows_processed,
          l_end_rowid);

Line: 202

   ad_parallel_updates_pkg.get_rowid_range(
         l_start_rowid,
         l_end_rowid,
         l_any_rows_to_process,
         l_batch_size,
         FALSE);

Line: 287

  SELECT trip_id
  FROM   WSH_TRIPS
  WHERE  status_code = 'OP'
  AND    ship_method_code = x_ship_method_code
  AND    carrier_id       = x_carrier_id
  AND    mode_of_transport IS NULL
  AND    service_level     IS NULL;

Line: 297

  SELECT delivery_id
  FROM   WSH_NEW_DELIVERIES
  WHERE  status_code IN ('OP', 'CO', 'SA', 'SR', 'SC')
  AND    ship_method_code = x_ship_method_code
  AND    carrier_id       = x_carrier_id
  AND    mode_of_transport IS NULL
  AND    service_level     IS NULL;

Line: 307

  SELECT delivery_detail_id
  FROM   WSH_DELIVERY_DETAILS
  WHERE  released_status IN ('N', 'R', 'B', 'X', 'S', 'Y')
  AND    ship_method_code = x_ship_method_code
  AND    carrier_id       = x_carrier_id
  AND    mode_of_transport IS NULL
  AND    service_level     IS NULL;

Line: 316

  SELECT trip_id
  FROM   WSH_TRIPS
  WHERE  trip_id = x_id
  FOR UPDATE NOWAIT;

Line: 322

  SELECT delivery_id
  FROM   WSH_NEW_DELIVERIES
  WHERE  delivery_id = x_id
  FOR UPDATE NOWAIT;

Line: 328

  SELECT delivery_detail_id
  FROM   WSH_DELIVERY_DETAILS
  WHERE  delivery_detail_id = x_id
  FOR UPDATE NOWAIT;

Line: 339

  l_update_ids WSH_UTIL_CORE.ID_TAB_TYPE;

Line: 342

  l_update_index  NUMBER;

Line: 387

  l_update_index := 0;

Line: 424

          l_update_index := l_update_index + 1;

Line: 425

          l_update_ids(l_update_index) := l_work_ids(l_work_index);

Line: 439

    IF    (l_update_index >= G_BULK_SIZE)
       OR ( (NOT l_continue) AND (l_update_index > 0) ) THEN
      IF l_debug_on THEN
        WSH_DEBUG_SV.log(l_module_name, 'l_update_index', l_update_index);

Line: 446

        FORALL i IN 1 .. l_update_index
          UPDATE WSH_TRIPS
          SET    service_level     = p_service_level,
                 mode_of_transport = p_mode_of_transport,
                 last_updated_by   = l_user_id,
                 last_update_date  = sysdate,
                 last_update_login = l_login
          WHERE trip_id = l_update_ids(i)
          AND   ship_method_code = p_ship_method_code
          AND   carrier_id       = p_carrier_id;

Line: 457

        FORALL i IN 1 .. l_update_index
          UPDATE WSH_NEW_DELIVERIES
          SET    service_level     = p_service_level,
                 mode_of_transport = p_mode_of_transport,
                 last_updated_by   = l_user_id,
                 last_update_date  = sysdate,
                 last_update_login = l_login
          WHERE delivery_id = l_update_ids(i)
          AND   ship_method_code = p_ship_method_code
          AND   carrier_id       = p_carrier_id;

Line: 468

        FORALL i IN 1 .. l_update_index
          UPDATE WSH_DELIVERY_DETAILS
          SET    service_level     = p_service_level,
                 mode_of_transport = p_mode_of_transport,
                 last_updated_by   = l_user_id,
                 last_update_date  = sysdate,
                 last_update_login = l_login
          WHERE delivery_detail_id = l_update_ids(i)
          AND   ship_method_code = p_ship_method_code
          AND   carrier_id       = p_carrier_id;

Line: 480

      x_count        := x_count + l_update_index;

Line: 481

      l_update_index := 0; -- instead of l_update_ids.DELETE.

Line: 537

  SELECT update_mot_sl,
         request_id
  FROM   WSH_CARRIER_SERVICES
  WHERE  carrier_service_id = p_id
  AND    update_mot_sl IN ('Y', 'P')
  FOR UPDATE NOWAIT;

Line: 545

  SELECT 1
  FROM fnd_concurrent_requests fcr
  WHERE  fcr.request_id = p_id
  AND    fcr.phase_code = 'C'
  AND    rownum = 1;

Line: 583

     AND l_rec.update_mot_sl = 'P'
     AND l_rec.request_id <> fnd_global.conc_request_id THEN
    -- if this service is pending, check whether its request is still running.

    OPEN  c_request_completed(l_rec.request_id);

Line: 600

    UPDATE WSH_CARRIER_SERVICES
    SET    update_mot_sl          = p_new_state,
           request_id             = fnd_global.conc_request_id,
           last_updated_by        = fnd_global.user_id,
           last_update_date       = sysdate
    WHERE  carrier_service_id = p_carrier_service_id;

Line: 649

PROCEDURE update_ship_method_SRS(
                  errbuf      OUT NOCOPY  VARCHAR2,
                  retcode     OUT NOCOPY  VARCHAR2)
IS

  l_debug_on BOOLEAN;

Line: 656

             'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_SHIP_METHOD_SRS';

Line: 705

    SELECT carrier_service_id,
           ship_method_code,
           carrier_id,
           service_level,
           mode_of_transport
    FROM   WSH_CARRIER_SERVICES
    WHERE  UPDATE_MOT_SL IN ('Y', 'P');

Line: 728

    l_sm_state       WSH_CARRIER_SERVICES.UPDATE_MOT_SL%TYPE;

Line: 791

    FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_COUNTS');

Line: 796

    FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_TRIP_COUNTS');

Line: 797

    FND_MESSAGE.SET_TOKEN('COUNT_UPDATED',     l_count_trips);

Line: 798

    FND_MESSAGE.SET_TOKEN('COUNT_NOT_UPDATED', l_count_trips_nu);

Line: 801

    FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_DEL_COUNTS');

Line: 802

    FND_MESSAGE.SET_TOKEN('COUNT_UPDATED',     l_count_dels);

Line: 803

    FND_MESSAGE.SET_TOKEN('COUNT_NOT_UPDATED', l_count_dels_nu);

Line: 806

    FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_DD_COUNTS');

Line: 807

    FND_MESSAGE.SET_TOKEN('COUNT_UPDATED',     l_count_dds);

Line: 808

    FND_MESSAGE.SET_TOKEN('COUNT_NOT_UPDATED', l_count_dds_nu);

Line: 830

    FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_TRY_AGAIN');

Line: 835

                                                  'update_ship_method_SRS');

Line: 862

                                                     'update_ship_method_SRS');

Line: 875

END update_ship_method_SRS;