APPS.WSH_TRIP_VALIDATIONS SQL Statements

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

Line: 36

SELECT count(*)
FROM   wsh_trip_stops
WHERE  trip_id = p_trip_id;

Line: 41

SELECT vehicle_item_id,
       ship_method_code,
       mode_of_transport
FROM   wsh_trips
WHERE  trip_id = p_trip_id;

Line: 48

SELECT dl.delivery_id
FROM   wsh_trips t,
       wsh_trip_stops st,
       wsh_delivery_legs dg,
       wsh_new_deliveries dl
WHERE  t.trip_id = p_trip_id AND
       st.trip_id = t.trip_id AND
       dg.pick_up_stop_id = st.stop_id AND
       dl.delivery_id = dg.delivery_id;

Line: 59

SELECT stop_id,
	  departure_fill_percent,
       departure_gross_weight,
       departure_volume
FROM   wsh_trip_stops
WHERE  trip_id = p_trip_id;

Line: 67

SELECT msi.minimum_fill_percent,
       msi.maximum_load_weight,
       msi.internal_volume
FROM   wsh_trips t,
       mtl_system_items msi
WHERE  t.trip_id = p_trip_id AND
       t.vehicle_item_id = msi.inventory_item_id AND
       t.vehicle_organization_id = msi.organization_id;

Line: 298

SELECT status_code,
       planned_flag,
       NVL(shipments_type_flag,'O') shipments_type_flag  -- J-IB-NPARIKH
FROM   wsh_trips
WHERE  trip_id = p_trip_id;

Line: 383

SELECT 1 from dual
WHERE exists ( select 1
FROM  wsh_trip_stops ds,
      wsh_delivery_legs dg,
      wsh_new_deliveries dl
WHERE ds.trip_id = p_trip_id
AND   dg.drop_off_stop_id = p_stop_id
AND   dg.delivery_id = dl.delivery_id
AND   dl.status_code IN ('CO','OP','PA'));

Line: 492

SELECT physical_stop_id linked_stop_id,
       1 link_type
FROM wsh_trip_stops
WHERE  stop_id = p_stop_id
AND    physical_stop_id IS NOT NULL
UNION
SELECT stop_id linked_stop_id,
       2 link_type
FROM wsh_trip_stops
WHERE  trip_id = p_trip_id
AND    physical_stop_id = p_stop_id
AND    status_code = 'OP';

Line: 514

SELECT  stop_id,
        status_code,
        stop_sequence_number,
        nvl(shipments_type_flag, 'O') shipments_type_flag
FROM    wsh_trip_stops
WHERE   trip_id = p_trip_id AND
        stop_sequence_number < p_curr_stop_seq  AND
        NVL(physical_stop_id,-1) <> p_stop_id AND --wr
        status_code IN ('OP', 'AR')  --wr
ORDER BY stop_sequence_number desc;

Line: 683

SELECT 'OPEN STOP'
FROM   	wsh_trip_stops st
WHERE  	st.trip_id = v_trip_id
AND     st.status_code <> 'CL';

Line: 768

PROCEDURE Check_Trip_Delete ( p_trip_id 	IN  NUMBER,
		              x_return_status 	OUT NOCOPY  VARCHAR2,
--tkt
                              p_caller        IN      VARCHAR2) IS

CURSOR trip_info IS
SELECT status_code,
       planned_flag
FROM   wsh_trips
WHERE  trip_id = p_trip_id;

Line: 780

SELECT stop_id
FROM   wsh_trip_stops
WHERE  trip_id = p_trip_id;

Line: 785

SELECT freight_cost_id
FROM   wsh_freight_costs
WHERE  trip_id = p_trip_id
FOR UPDATE NOWAIT;

Line: 799

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

Line: 858

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

Line: 870

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

Line: 885

	 check_stop_delete( st.stop_id, l_return_status, 'Y', p_caller);

Line: 913

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

Line: 935

	    wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_TRIP_DELETE');

Line: 946

END Check_Trip_Delete;

Line: 963

SELECT status_code,
       planned_flag
FROM   wsh_trips
WHERE  trip_id = p_trip_id;

Line: 969

SELECT count(*)
FROM   wsh_trip_stops t,
       wsh_delivery_legs dg,
       wsh_new_deliveries dl,
       wsh_shipping_parameters wsp
WHERE  t.stop_id = p_trip_id AND
       dg.pick_up_stop_id = t.stop_id AND
       dl.delivery_id = dg.delivery_id AND
       dl.organization_id = wsp.organization_id AND
       dl.ship_method_code IS NOT NULL AND
       wsp.group_by_ship_method_flag = 'Y';

Line: 1092

SELECT stop_sequence_number,
       status_code,
       trip_id,
       nvl(shipments_type_flag, 'O') shipments_type_flag
FROM   wsh_trip_stops
WHERE  stop_id = p_stop_id;

Line: 1222

SELECT planned_departure_date,
       status_code,
       trip_id
FROM   wsh_trip_stops
WHERE  stop_id = p_stop_id;

Line: 1230

SELECT wnd.delivery_id
FROM   wsh_new_deliveries wnd,
       wsh_delivery_legs  wdl
WHERE  wdl.pick_up_stop_id = p_stop_id
AND    wnd.delivery_id     = wdl.delivery_id
AND    wnd.status_code IN ('OP', 'PA')
AND    rownum = 1;

Line: 1409

PROCEDURE Check_Stop_Delete ( p_stop_id 	  IN  NUMBER,
                              x_return_status  OUT NOCOPY  VARCHAR2,
		              p_trip_flag      IN  VARCHAR2 DEFAULT 'N',
--tkt
                              p_caller        IN      VARCHAR2) IS
CURSOR stop_info IS
SELECT t.status_code,
       t.planned_flag,
       st.status_code,
	t.trip_id
FROM   wsh_trips t,
       wsh_trip_stops st
WHERE  st.stop_id = p_stop_id AND
       st.trip_id = t.trip_id;

Line: 1425

SELECT dg.delivery_id
FROM   wsh_trip_stops t,
       wsh_delivery_legs dg
WHERE  t.stop_id = p_stop_id AND
       dg.pick_up_stop_id = t.stop_id
FOR UPDATE NOWAIT;

Line: 1433

SELECT dg.delivery_id
FROM   wsh_trip_stops t,
       wsh_delivery_legs dg
WHERE  t.stop_id = p_stop_id AND
       dg.drop_off_stop_id = t.stop_id
FOR UPDATE NOWAIT;

Line: 1441

SELECT freight_cost_id
FROM   wsh_freight_costs
WHERE  stop_id = p_stop_id
FOR UPDATE NOWAIT;

Line: 1457

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

Line: 1585

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

Line: 1605

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

Line: 1627

	    wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_STOP_DELETE');

Line: 1638

END Check_Stop_Delete;

Line: 1660

  SELECT status_code, planned_flag, SHIPMENTS_TYPE_FLAG, --3509004:public api changes
         vehicle_organization_id, -- Bug 3599626
         lane_id,
         tp_plan_name -- OTM R12, glog proj
   FROM  wsh_trips
  WHERE  trip_id = p_trip_id;

Line: 1713

		x_disabled_list.delete;

Line: 1993

 SELECT stop_sequence_number,
        trip_id
   FROM wsh_trip_stops
  WHERE stop_id = p_stop_id;

Line: 2000

 SELECT planned_flag
   FROM wsh_trips
  WHERE trip_id = v_trip_id;

Line: 2127

SELECT  wnd.delivery_id,
        wnd.organization_id,
        wnd.status_code,
        wnd.planned_flag,
/*J inbound logistics new column jckwok*/
        NVL(shipment_direction,'O') shipment_direction,
        wnd.delivery_type, --MDC
        NVL(wnd.ignore_for_planning, 'N') ignore_for_planning, -- OTM R12, glog proj
        NVL(wnd.tms_interface_flag,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) tms_interface_flag,  -- OTM R12, glog proj
        wsp.otm_enabled    -- OTM R12, glog proj
FROM    wsh_new_deliveries wnd,
        wsh_delivery_legs wdl,
        wsh_trip_stops wts1,
        wsh_trip_stops wts2,
        wsh_trips wt,
        wsh_shipping_parameters wsp   --  OTM R12, glog proj
WHERE   wnd.delivery_id = wdl.delivery_id
AND     wts1.stop_id = wdl.PICK_UP_STOP_ID
AND     wts2.stop_id = wdl.DROP_OFF_STOP_ID
AND     wts1.trip_id = wt.trip_id
AND     wts2.trip_id = wt.trip_id
AND     wt.trip_id   = p_trip_id
and     wsp.organization_id = wnd.organization_id; --  OTM R12, glog proj

Line: 2257

                      NVL(p_trip_rec_tab(j).load_tender_status,'OPEN') -- 12 SELECT CARRIER
             AND NVL(l_trip_actions_tab(l_loop_counter).caller,l_caller) = l_caller   -- J-IB-NPARIKH
             AND l_trip_actions_tab(l_loop_counter).action_not_allowed = p_action
-- add check to compare shipments_type_flag jckwok
             AND NVL(l_trip_actions_tab(l_loop_counter).shipments_type_flag, NVL(p_trip_rec_tab(j).shipments_type_flag,'O')) = NVL(p_trip_rec_tab(j).shipments_type_flag,'O')
             --OTM R12, glog proj
             AND nvl(l_trip_actions_tab(l_loop_counter).ignore_for_planning, nvl(p_trip_rec_tab(j).ignore_for_planning, 'N')) = nvl(p_trip_rec_tab(j).ignore_for_planning, 'N')
         ) THEN

                IF l_trip_actions_tab(l_loop_counter).message_name IS NOT NULL THEN
                           IF l_debug_on THEN
                             wsh_debug_sv.log(l_module_name, 'Message Name is', l_trip_actions_tab(l_loop_counter).message_name);

Line: 2327

    ELSIF p_action = 'SELECT-CARRIER' THEN

          IF p_trip_rec_tab(j).lane_id IS NOT NULL THEN
             IF l_debug_on THEN
                  wsh_debug_sv.log(l_module_name, 'Trip has lane id : ', p_trip_rec_tab(j).lane_id);

Line: 2431

 SELECT 'X'
 FROM 	wsh_trips
 WHERE  trip_id = cp_arr_after_trip_id
 AND	status_code <> 'CL';

Line: 2437

 SELECT 'X',trip_id
 FROM 	wsh_trips
 WHERE  name = cp_arr_after_trip_name
 AND	status_code <> 'CL';

Line: 3523

  ELSIF p_in_rec.action_code = 'UPDATE' THEN
   --
    l_caller := p_in_rec.caller;

Line: 3690

  END IF; /* if action = 'UPDATE' */

Line: 3863

  IF p_action = 'SELECT-CARRIER' THEN

    i := i + 1;

Line: 3866

    x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';

Line: 3870

    x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';

Line: 3874

    x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';

Line: 3878

    x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';

Line: 3882

    x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';

Line: 3886

    x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';

Line: 3890

    x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';

Line: 4025

    SELECT stop_id
    FROM   wsh_trip_stops
    WHERE  trip_id      = p_trip_id
    AND    stop_id <> NVL(p_stop_id,-9999)
    AND    stop_id <> NVL(p_linked_stop_id,-9999)
    AND    status_code <> 'CL';

Line: 4100

    l_exceptions_tab.delete;

Line: 4323

    SELECT stop_id
    FROM   wsh_trip_stops
    WHERE  trip_id      = p_trip_id
    AND    status_code  = 'CL';

Line: 4399

    l_exceptions_tab.delete;

Line: 4612

      select status_code, name, nvl(planned_flag,'N') planned_flag
      from wsh_trips
      where trip_id = p_trip_id;

Line: 4724

      SELECT 1
      FROM   wsh_delivery_legs wdl,
             wsh_new_deliveries wnd,
             wsh_trip_stops wts
      WHERE  wts.trip_id                      = p_trip_id
      AND    wdl.delivery_id                  = wnd.delivery_id
      AND    NVL(wnd.shipment_direction,'O') IN ( 'O','IO' )
      AND    ( p_stop_id is null  or wts.stop_id = p_stop_id ) --Bugfix 3639920
      AND    (
                    wdl.pick_up_stop_id  = wts.stop_id
                OR  wdl.drop_off_stop_id = wts.stop_id
             );

Line: 4834

      SELECT 1
      FROM   wsh_delivery_legs wdl,
             wsh_new_deliveries wnd,
             wsh_trip_stops wts
      WHERE  wts.trip_id                      = p_trip_id
      AND    wdl.delivery_id                  = wnd.delivery_id
      AND    NVL(wnd.shipment_direction,'O') NOT IN ( 'O','IO' )
      AND    ( p_stop_id is null  or wts.stop_id = p_stop_id )  --Bugfix 3639920
      AND    (
                    wdl.pick_up_stop_id  = wts.stop_id
                OR  wdl.drop_off_stop_id = wts.stop_id
             );

Line: 5349

  IF     p_user_in_rec.LAST_UPDATE_DATE <> FND_API.G_MISS_DATE
      AND NVL(p_user_in_rec.LAST_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.LAST_UPDATE_DATE,TO_DATE('2','j'))
  THEN
       IF l_attributes IS NOT NULL THEN
          l_attributes := l_attributes || ', ';

Line: 5355

       l_attributes := l_attributes || 'LAST_UPDATE_DATE';

Line: 5358

  IF     p_user_in_rec.LAST_UPDATED_BY <> FND_API.G_MISS_NUM
      AND NVL(p_user_in_rec.LAST_UPDATED_BY,-99) <> NVL(p_out_rec.LAST_UPDATED_BY,-99)
  THEN
       IF l_attributes IS NOT NULL THEN
          l_attributes := l_attributes || ', ';

Line: 5364

       l_attributes := l_attributes || 'LAST_UPDATED_BY';

Line: 5367

  IF     p_user_in_rec.LAST_UPDATE_LOGIN <> FND_API.G_MISS_NUM
      AND NVL(p_user_in_rec.LAST_UPDATE_LOGIN,-99) <> NVL(p_out_rec.LAST_UPDATE_LOGIN,-99)
  THEN
       IF l_attributes IS NOT NULL THEN
          l_attributes := l_attributes || ', ';

Line: 5373

       l_attributes := l_attributes || 'LAST_UPDATE_LOGIN';

Line: 5394

  IF     p_user_in_rec.PROGRAM_UPDATE_DATE <> FND_API.G_MISS_DATE
      AND NVL(p_user_in_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j'))
  THEN
       IF l_attributes IS NOT NULL THEN
          l_attributes := l_attributes || ', ';

Line: 5400

       l_attributes := l_attributes || 'PROGRAM_UPDATE_DATE';

Line: 5844

SELECT cur.stop_location_id cur_location_id,
       cur.physical_stop_id,
       cur.planned_arrival_date cur_arr_date,
       cur.planned_departure_date cur_dep_date,
       nxt.stop_location_id nxt_location_id,
       nxt.planned_arrival_date nxt_arr_date
FROM   wsh_trip_stops cur, wsh_trip_stops nxt
WHERE  cur.trip_id = p_trip_id
AND    cur.trip_id = nxt.trip_id
AND    cur.stop_id <> nxt.stop_id
AND (( cur.planned_departure_date > nxt.planned_arrival_date
       AND    cur.planned_arrival_date < nxt.planned_arrival_date
       AND    cur.status_code = 'OP'
       AND    nxt.status_code = 'OP')
     OR ( cur.planned_arrival_date = nxt.planned_arrival_date
       AND    cur.status_code = 'OP'
       AND    nxt.status_code = 'OP')
     OR ( cur.physical_stop_id = nxt.stop_id
          AND cur.stop_sequence_number + 1 <> nxt.stop_sequence_number
        ))
AND    rownum = 1;

Line: 5893

SELECT cur.planned_arrival_date cur_arr_date,
       cur.stop_location_id cur_location_id,
       cur.stop_id          cur_stop_id,
       cur.stop_sequence_number cur_stop_seq_num,
       prv.planned_arrival_date prv_arr_date
FROM   wsh_trip_stops cur,
       wsh_trip_stops prv
WHERE  cur.trip_id = p_trip_id
AND    cur.trip_id = prv.trip_id
AND    cur.stop_id <> prv.stop_id
AND    cur.planned_arrival_date <= prv.planned_arrival_date
AND    cur.status_code = 'OP'
AND    prv.status_code IN ('AR','CL')
AND    cur.stop_sequence_number > prv.stop_sequence_number
AND    rownum = 1;

Line: 5914

SELECT leg.delivery_id
FROM wsh_delivery_legs leg,
     wsh_trip_stops   pickup,
     wsh_trip_stops   dropoff
WHERE pickup.trip_id = p_trip_id
AND   pickup.trip_id = dropoff.trip_id
AND   pickup.stop_id <> dropoff.stop_id
AND   pickup.status_code = 'OP'
AND   dropoff.status_code = 'OP'
AND   leg.pick_up_stop_id = pickup.stop_id
AND   leg.drop_off_stop_id = dropoff.stop_id
AND   dropoff.planned_arrival_date <= pickup.planned_arrival_date;

Line: 5944

  l_del_to_unassign.delete;

Line: 6055

       l_del_to_unassign.delete;