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_INTERFACE SQL Statements

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

Line: 22


PROCEDURE Update_Shipping_Attributes(
  p_source_code			IN	 VARCHAR2
, p_changed_attributes	 IN	 ChangedAttributeTabType
, x_return_status		  OUT NOCOPY 	VARCHAR2
, p_log_level			  IN	 NUMBER   -- log level fix
)
IS
  l_interface_flag VARCHAR2(1);

Line: 37


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

Line: 91


      WSH_DEBUG_SV.logmsg(l_module_name, 'INSIDE WSH_INTERFACE.UPDATE_SHIPPING_ATTRIBUTES'  );

Line: 141


	      WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_INV_PVT.UPDATE_INVENTORY_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 144


	  WSH_USA_INV_PVT.Update_Inventory_Info(
			 p_changed_attributes => p_changed_attributes,
			 x_return_status	  => l_rs);

Line: 209


	      WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_INTERFACE.PROCESS_RECORDS / UPDATE_INVENTORY_INFO'  );

Line: 214


	END IF; -- return status after Update_INV and Process_Records

Line: 361


	  wsh_util_core.default_handler('WSH_INTERFACE.Update_Shipping_Attributes');

Line: 399


END Update_Shipping_Attributes;

Line: 423


   select	dd.delivery_detail_id,
		 s.stop_id,
		 s.actual_departure_date,
		 nd.name,
		 dd.shipped_quantity
   from	  wsh_delivery_Details dd,
		 wsh_trip_stops s,
		 wsh_delivery_legs dl,
		 wsh_delivery_assignments_v da,
		 wsh_new_deliveries  nd
--		 wsh_delivery_line_status_v ds
   where	 s.stop_id = dl.pick_up_stop_id
   and	   dl.delivery_id = nd.delivery_id
   and	   nd.delivery_id = da.delivery_id
--   and	   dd.delivery_detail_id = ds.delivery_detail_id
   and	   da.delivery_detail_id = dd.delivery_detail_id
   and	   s.stop_location_id = nd.initial_pickup_location_id
   and	   dd.customer_id = p_customer_id
   and	   dd.ship_to_location_id = l_ship_to_location_id
   and	   dd.ship_from_location_id = l_ship_from_location_id
   and	   dd.inventory_item_id = p_inventory_item_id
   and	  dd.source_header_id = p_order_header_id
--   and	   ds.delivery_status in ('CL', 'IT','CO')
   and    NVL(nd.shipment_direction, 'O') IN ('O', 'IO')
   and	  NVL(dd.customer_prod_seq,'*') = NVL(NVL(p_cust_production_seq_num,dd.customer_prod_seq), '*');

Line: 821


SELECT mci.master_container_item_id, mci.detail_container_item_id
FROM mtl_customer_items mci, wsh_delivery_details dd, oe_order_lines_all ool
WHERE dd.delivery_detail_id = p_delivery_detail_id AND
	  dd.source_line_id = ool.line_id AND
	  mci.customer_item_id(+) =  ool.ordered_item_id AND
          ool.item_identifier_type = 'CUST';

Line: 863


	  UPDATE wsh_delivery_details
	  SET master_container_item_id = l_master_container_item_id,
		  detail_container_item_id = l_detail_container_item_id
	  WHERE delivery_detail_id = p_delivery_detail_id;

Line: 1078


            p_cancel_delete_flag IN  VARCHAR2,
            x_return_status      OUT NOCOPY  VARCHAR2) IS

CURSOR c_assignment(c_detail_id NUMBER) IS
SELECT delivery_assignment_id,
       parent_delivery_detail_id,
       delivery_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = c_detail_id;

Line: 1091


SELECT delivery_detail_id,
       organization_id,
       ship_from_location_id,
       inventory_item_id,
       requested_quantity,
       picked_quantity,
       requested_quantity2,
       picked_quantity2,
       move_order_line_id,
       released_status,
       source_code,
       container_flag,
       source_line_id -- Column added for Bug 5741373
FROM wsh_delivery_details
WHERE delivery_detail_id = c_detail_id;

Line: 1140


WSH_DELETE_DETAIL_FAILED    EXCEPTION;

Line: 1170


     WSH_DEBUG_SV.log(l_module_name,'p_cancel_delete_flag',p_cancel_delete_flag);

Line: 1190


       GOTO loop_end;  -- maybe already deleted.

Line: 1198


       SELECT container_name
       INTO l_container_name
       FROM wsh_delivery_details
       WHERE delivery_detail_id = l_assign_rec.parent_delivery_detail_id;

Line: 1243


       SELECT planned_flag
       INTO l_planned_flag
       FROM wsh_new_deliveries
       WHERE delivery_id = l_assign_rec.delivery_id;

Line: 1301


         l_trolin_tbl(l_trolin_table_id).OPERATION := INV_GLOBALS.G_OPR_DELETE;

Line: 1305


           WSH_DEBUG_SV.logmsg(l_module_name,  'DELETE MOVE ORDER LINE '||L_TROLIN_REC.LINE_ID  );

Line: 1332


     /* H integration: 940/945 cancel 'WSH' line, not delete it wrudge */
     IF l_detail_rec.source_code = 'WSH' AND
        l_detail_rec.container_flag = 'N'  THEN

        l_cancel_dds( l_cancel_dds.count+1 ) := p_details_id(i);

Line: 1379


       IF (p_cancel_delete_flag = 'C') THEN
         IF l_debug_on THEN
           WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS to CANCEL',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1384


         WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
           p_delivery_detail_id => p_details_id(i),
           p_cancel_flag        => 'Y',
           x_return_status      => l_return_status );

Line: 1389


       ELSE -- delete details
         IF l_debug_on THEN
           WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS to DELETE',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1394


         WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
           p_delivery_detail_id => p_details_id(i),
           p_cancel_flag        => 'N',
           x_return_status      => l_return_status );

Line: 1406


           WSH_DEBUG_SV.logmsg(l_module_name,  'DELETE DELIVERY DETAIL '|| P_DETAILS_ID ( I ) || ' FAILED'  );

Line: 1408


         raise WSH_DELETE_DETAIL_FAILED;

Line: 1444


         p_delete_reservations => 'Y',
         p_txn_source_line_id  => l_source_line_ids(i) );

Line: 1476


       UPDATE wsh_delivery_details
       SET move_order_line_id = NULL ,
           released_status = 'D',
           cycle_count_quantity = NULL,
           cycle_count_quantity2 = NULL,
           shipped_quantity = NULL,
           shipped_quantity2 = NULL,
           picked_quantity = NULL,
           picked_quantity2 = NULL,
           subinventory = NULL,
           inv_interfaced_flag = NULL,
           oe_interfaced_flag  = NULL,
           locator_id = NULL,
           preferred_grade = NULL,
-- HW OPMCONV - No need for sublot_number
--         sublot_number = NULL,
           lot_number = NULL,
           revision   = null ,
           tracking_number = NULL
       WHERE delivery_detail_id = l_cancel_dds(i);

Line: 1506


     WSH_INTEGRATION.DBI_Update_Detail_Log
       (p_delivery_detail_id_tab => l_cancel_dds,
        p_dml_type               => 'UPDATE',
        x_return_status          => l_dbi_rs);

Line: 1574


   WHEN WSH_DELETE_DETAIL_FAILED THEN
     x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;

Line: 1610


       WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DELETE_DETAIL_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);

Line: 1611


       WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DELETE_DETAIL_FAILED');

Line: 1723


PROCEDURE Delete_Details(
  p_details_id    IN  WSH_UTIL_CORE.Id_Tab_Type,
  x_return_status OUT NOCOPY VARCHAR2) IS

l_debug_on BOOLEAN;

Line: 1728


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

Line: 1744


    p_cancel_delete_flag => 'D',
    x_return_status      => x_return_status);

Line: 1754


     wsh_util_core.default_handler('WSH_INTERFACE.DELETE_DETAILS');

Line: 1761


END Delete_Details;

Line: 1804


    p_cancel_delete_flag => 'C',
    x_return_status      => x_return_status);

Line: 1924


		select detail_container_item_id , inventory_item_id  , organization_id
		from wsh_delivery_Details
		where move_order_line_id = p_move_order_line_id
                and   nvl(line_direction, 'O') IN ('O', 'IO')
		and   rownum = 1 ;

Line: 1973


		  select max_load_quantity
		  into   l_max_load_quantity
		  from   wsh_container_items
		  where  container_item_id = l_detail_rec.detail_container_item_id
		  and	nvl ( load_item_id , l_detail_rec.inventory_item_id ) =  l_detail_rec.inventory_item_id
		  and	master_organization_id = l_detail_rec.organization_id
		  and	rownum = 1 ;

Line: 1983


		  select max_load_quantity  , container_item_id
		  into   l_max_load_quantity , l_container_item_id
		  from   wsh_container_items
		  where  load_item_id =  l_detail_rec.inventory_item_id
		  and	preferred_flag ='Y'
		  and	master_organization_id = l_detail_rec.organization_id
		  and	rownum = 1 ;

Line: 2064


  SELECT wdd.delivery_detail_id
  FROM  wsh_delivery_details	 wdd
  WHERE wdd.source_line_id	 IN  (x_source_line_id, x_neg_source_line_id)
  AND   wdd.source_code		=  x_source_code
  AND   wdd.container_flag	 =  'N'
  AND   wdd.released_status	<> 'D';

Line: 2179


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

Line: 2233


  select ship_set_id
  from wsh_delivery_details
  where source_header_id = c_source_header_id
  and   ship_set_id = c_ship_set
  and   source_code = p_source_code
  and   oe_interfaced_flag = 'P'
  and   rownum = 1;

Line: 2279


  SELECT wdd.oe_interfaced_flag,
         wdd.released_status,
         wdd.ship_set_id,
         wdd.source_header_id,
         wda.delivery_id
  INTO   l_interfaced_flag,
         l_det_status_code,
         l_ship_set_id,
         l_source_header_id,
         l_dummy_id
  FROM   wsh_delivery_details wdd,
         wsh_delivery_assignments_v wda
  WHERE  wdd.delivery_detail_id = p_delivery_detail_id
  AND    wdd.delivery_detail_id = wda.delivery_detail_id
  AND    wdd.container_flag = 'N'
  FOR    UPDATE NOWAIT;

Line: 2307


      SELECT wnd.status_code
      INTO   l_del_status_code
      FROM   wsh_new_deliveries wnd
      WHERE  wnd.delivery_id = l_dummy_id;

Line: 2321


      SELECT wnd.status_code
      INTO   l_del_status_code
      FROM   wsh_new_deliveries wnd
      WHERE  wnd.delivery_id = l_dummy_id
      FOR    UPDATE NOWAIT;

Line: 2410


l_update_allowed  VARCHAR2(1);

Line: 2454


    **   During OM Interface, allow updates/splits to happen.
    **   Otherwise, check if changes are allowed.
    */
    IF p_interface_flag <> 'Y' THEN
       --
       -- Debug Statements
       --
       IF l_debug_on THEN
           WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_DELIVERY_UTIL.CHECK_UPDATES_ALLOWED'  );

Line: 2469


           WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_UTIL.CHECK_UPDATES_ALLOWED',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 2472


       WSH_DELIVERY_UTIL.Check_Updates_Allowed(
                p_changed_attributes => p_changed_attributes,
                p_source_code        => p_source_code,
                x_update_allowed     => l_update_allowed,
                x_return_status      => l_rs);

Line: 2481


           WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING CHECK_UPDATES_ALLOWED ' || L_RS  );

Line: 2546


	    WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_USA_ACTIONS_PVT.UPDATE_RECORDS'  );

Line: 2553


	    WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_ACTIONS_PVT.UPDATE_RECORDS',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 2556


	WSH_USA_ACTIONS_PVT.Update_Records(
	  p_source_code		=> p_source_code,
	  p_changed_attributes => p_changed_attributes,
	  p_interface_flag	 => p_interface_flag,
	  x_return_status	  => l_rs);

Line: 2571


           WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_USA_QUANTITY_PVT.UPDATE_ORDERED_QUANTITY'  );

Line: 2578


           WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_QUANTITY_PVT.UPDATE_ORDERED_QUANTITY',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 2581


       WSH_USA_QUANTITY_PVT.Update_Ordered_Quantity(
                p_changed_attribute  =>p_changed_attributes(l_counter),
                p_source_code        =>p_source_code,
                p_action_flag        => 'D',
                x_return_status      => l_rs);

Line: 2697


    SELECT wdd.delivery_detail_id,
           wdd.requested_quantity,
           wdd.shipped_quantity,
           wdd.picked_quantity,
           wdd.gross_weight,
           wdd.net_weight,
           wdd.weight_uom_code,
           wdd.volume,
           wdd.volume_uom_code,
           wda.delivery_id,
           wda.parent_delivery_detail_id,
           wdd.released_status
      FROM wsh_delivery_details wdd,
           wsh_delivery_assignments_v wda
     WHERE wdd.delivery_detail_id = v_delivery_detail_id
       AND wdd.delivery_detail_id = wda.delivery_detail_id;

Line: 2718


    SELECT wdd.delivery_detail_id,
           wdd.requested_quantity,
           wdd.shipped_quantity,
           wdd.picked_quantity,
           wdd.gross_weight,
           wdd.net_weight,
           wdd.weight_uom_code,
           wdd.volume,
           wdd.volume_uom_code,
           wda.delivery_id,
           wda.parent_delivery_detail_id,
           wdd.released_status
      FROM wsh_delivery_details wdd,
           wsh_delivery_assignments_v wda
     WHERE wdd.source_line_id = v_source_line_id
       AND wdd.delivery_detail_id = wda.delivery_detail_id
       AND nvl(wdd.line_direction, 'O') IN ('O', 'IO');

Line: 2764


  l_dd_tab.delete;