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

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

Line: 151


select delivery_id
from
wsh_delivery_legs
where pick_up_stop_id = p_stop_id;

Line: 158


select delivery_id
from
wsh_delivery_legs
where drop_off_stop_id = p_stop_id;

Line: 165


SELECT stop_id,'ASR' tms_iface_new_status
FROM wsh_trip_stops
WHERE trip_id = c_trip_id;

Line: 218


	'select '||
	'WSH_OTM_TRIP_OBJ(WT.TP_PLAN_NAME,  '||
	'NAME    ,'||
	'null,'||
	'''RC'','||
	'WT.carrier_id,'||
	'null,'||
	'WT.MODE_OF_TRANSPORT,'||
	'null,'||
	'null,'|| --weight_uom
	'null,'|| --Volume
	'null,'|| -- volume_uom
	'null,'|| --lpn count
	'WT.FREIGHT_TERMS_CODE,'||
	'null,'|| -- stop count
	'null,'|| -- release count
	'null,'||
	'null,'||
	'WT.VEHICLE_ITEM_ID,'||
	'WT.VEHICLE_NUM_PREFIX,'||
	'WT.VEHICLE_NUMBER,'||
	'null,'||
	'WT.VEHICLE_ORGANIZATION_ID,'||
	'null,'||
	'WT.SEAL_CODE,'||
	'sequence_number,'|| -- master_bol_number
	'WT.PLANNED_FLAG,'||
	'WT.ROUTING_INSTRUCTIONS,'||
	'null, '||-- gross weight
	'null, '||-- net_weight
	'wt.BOOKING_NUMBER    ,    '||
	'null,'||
	'WT.TRIP_ID,'||
	'nvl(WT.IGNORE_FOR_PLANNING,''N''),'||
	'WT.OPERATOR,'||
	'null,'|| -- Manual Freight cost
	'null,'|| -- Currency Code
	'null, '||
	'''TRIP_ID'','||
	'''MBOL_NUMBER'','||
	'''PLANNED_TRIP'','||
	'''MANUAL_FREIGHT_COSTS'','||
	'''MAN_FREIGHT_COST_CUR'','||
	'''OPERATOR'','||
	'''ROUTING_INSTR'','||
	'null,'|| -- Stops
	'null ,null,null,null,null), '||
	'WSH_OTM_STOP_OBJ(wts.STOP_ID , '||
		'WTS.STOP_SEQUENCE_NUMBER,null,null,'||
		'TO_CHAR(WTS.PLANNED_ARRIVAL_DATE,''YYYYMMDDHH24MISS''),'||
		'TO_CHAR(WTS.PLANNED_DEPARTURE_DATE,''YYYYMMDDHH24MISS''),'||
		'TO_CHAR(WTS.ACTUAL_ARRIVAL_DATE,''YYYYMMDDHH24MISS''),'||
		'TO_CHAR(WTS.ACTUAL_DEPARTURE_DATE,''YYYYMMDDHH24MISS''),'||
--		'TO_CHAR(WTS.PLANNED_ARRIVAL_DATE,''DD-MON-YYYY HH24:MI:SS''),'||
--		'TO_CHAR(WTS.PLANNED_DEPARTURE_DATE,''DD-MON-YYYY HH24:MI:SS''),'||
--		'TO_CHAR(WTS.ACTUAL_ARRIVAL_DATE,''DD-MON-YYYY HH24:MI:SS''),'||
--		'TO_CHAR(WTS.ACTUAL_DEPARTURE_DATE,''DD-MON-YYYY HH24:MI:SS''),'||
		'WTS.loading_end_datetime -  WTS.loading_start_datetime,'||
		'WTS.DEPARTURE_SEAL_CODE,''DEPARTURE_SEAL_CODE'', null, null, null, null, null), '||
		'nvl(WTS.DEPARTURE_GROSS_WEIGHT,0),'||
		'nvl(WTS.DEPARTURE_NET_WEIGHT,0),'||
		'nvl(WTS.DEPARTURE_VOLUME,0),	'||
		'WTS.WEIGHT_UOM_CODE,'||
		'WTS.VOLUME_UOM_CODE '||
  ' from wsh_trips wt , wsh_document_instances wdi, wsh_trip_stops wts '||
  ' where wt.trip_id = wts.trip_id '||
  ' and wdi.entity_name(+) = ''WSH_TRIPS'''||
  ' and wdi.entity_id(+) = wt.trip_id '||
  ' and wts.physical_stop_id is null '||
  ' and wts.tms_interface_flag = ''ASP'''||
  ' and wt.trip_id in (';

Line: 394


		    --l_trip_tab.delete(l_trip_tab.COUNT);

Line: 509


	SELECT currency_code INTO l_currency_code
	FROM GL_SETS_OF_BOOKS
	WHERE set_of_books_id = l_sob_id;

Line: 526


	    l_trip_tab.delete(i);

Line: 642


	  l_trip_tab.delete(i);

Line: 763


              WSH_DEBUG_SV.log(l_sub_module_name,'Calling WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG Total stops ids',l_upd_trip_stops.COUNT);

Line: 766


           WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG
                    (P_STOP_ID_TAB=>l_upd_trip_stops,
                     P_TMS_INTERFACE_FLAG_TAB =>l_upd_tms_interface_flags,
                     X_RETURN_STATUS   =>l_return_status);

Line: 772


                   WSH_DEBUG_SV.log(l_sub_module_name,'failed in UPDATE_TMS_INTERFACE_FLAG');

Line: 928


   SELECT wdd.customer_id, count(*) cnt
   FROM   wsh_delivery_assignments wda,
          wsh_delivery_details wdd
   WHERE  wdd.delivery_detail_id = wda.delivery_detail_id
   AND    wda.delivery_id        =  p_delivery_id
   AND    wdd.container_flag     = 'N'
   GROUP BY customer_id
   ORDER BY cnt DESC;

Line: 938


   SELECT organization_code
   FROM   mtl_parameters mp, hr_organization_units hou
   WHERE  mp.organization_id = hou.organization_id
   AND    hou.location_id = p_location_id;

Line: 946


 SELECT CONTAINER_TYPE_CODE,
	UNIT_LENGTH ,
	UNIT_HEIGHT  ,
	UNIT_WIDTH   ,
	DIMENSION_UOM_CODE
 FROM mtl_system_items
 WHERE inventory_item_id = p_inventory_item_id
 AND   organization_id = p_organization_id;

Line: 957


 SELECT delivery_id, decode(tms_interface_flag, 'CP', 'CR', 'UP', 'UR', 'DP',
  'DR', tms_interface_flag )  tms_iface_new_status
  FROM wsh_new_deliveries
  WHERE delivery_id = c_dlvy_id
 FOR UPDATE OF tms_interface_flag NOWAIT;

Line: 1013


 'select '
 ||'wsh_otm_dlv_obj( '
 ||'decode(wnd.tms_interface_flag,''CP'',''RC'',''UP'',''RC'',''DP'',''D''),'
 --||'''RC'','
 ||'wnd.delivery_id,'
 ||'wnd.name,'
 ||'wnd.freight_terms_code,'
 ||'wnd.fob_code,'
 ||'wnd.carrier_id,'
 ||'wnd.service_level,'
 ||'wnd.mode_of_transport,'
 ||'wnd.organization_id||''-''|| wnd.INITIAL_PICKUP_LOCATION_ID,'
 ||'wnd.customer_id ||''-''||wnd.ULTIMATE_DROPOFF_LOCATION_ID,'
 ||'wnd.EARLIEST_PICKUP_DATE,'
 ||'wnd.LATEST_PICKUP_DATE,'
 ||'wnd.EARLIEST_DROPOFF_DATE,'
 ||'wnd.LATEST_DROPOFF_DATE,'
 ||'nvl(wnd.GROSS_WEIGHT,0),'
 ||'wnd.WEIGHT_UOM_CODE,'
 ||'nvl(wnd.VOLUME,0),'
 ||'wnd.VOLUME_UOM_CODE,'
 ||'nvl(wnd.NET_WEIGHT,0),'
 ||'wnd.TMS_VERSION_NUMBER,'   -- revision number
 ||'wnd.REASON_OF_TRANSPORT,'
 ||'wnd.DESCRIPTION,'
 ||'wnd.ADDITIONAL_SHIPMENT_INFO,'
 ||'wnd.ROUTING_INSTRUCTIONS,'
 ||'null,''REVNUM'',''TRSP_REASON'',''DEL_DESCRIPTION'',''ADD_INFOS'',''ROUTING_INSTR'',null,null),'
 ||' wsh_otm_det_obj(wdd.delivery_detail_id,'
   		||'wdd.lot_number,'
     		||'wdd.serial_number,'
     		||'wdd.to_serial_number,'
   		||'nvl(wdd.GROSS_WEIGHT,0),'
   		||'wdd.WEIGHT_UOM_CODE	,'
   		||'nvl(wdd.VOLUME,0)	,'
   		||'wdd.VOLUME_UOM_CODE,'
   		||'wdd.REQUESTED_QUANTITY,'
   		||'wdd.SHIPPED_QUANTITY,'
   		||'wdd.organization_id || ''-'' || wdd.INVENTORY_ITEM_ID,'
   		||'wdd.container_flag,'
   		||'wda.parent_delivery_detail_id,'
   		||'wdd.cust_po_number,'
  		||'wdd.source_header_number,''CUST_PO'',''SO_NUM'', wda.delivery_id,nvl(wdd.NET_WEIGHT,0))'
 ||'  from wsh_new_deliveries wnd, wsh_delivery_details wdd  , wsh_delivery_assignments wda'
 ||' where wdd.delivery_detail_id(+) = wda.delivery_detail_id '
 ||' and wnd.delivery_id = wda.delivery_id(+) '
 ||' and wnd.delivery_id in (';

Line: 1063


     l_deliveries_sql := l_deliveries_sql ||' select wdl.delivery_id from wsh_delivery_legs wdl, wsh_trip_stops wts where wdl.pick_up_stop_id = wts.stop_id and wts.trip_id = :1 ' ;

Line: 1540


               WSH_DEBUG_SV.log(l_sub_module_name,'Calling WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG');

Line: 1543


             WSH_NEW_DELIVERIES_PVT.Update_Tms_interface_flag
                ( p_delivery_id_tab        => l_upd_err_dlvys,
                  p_tms_interface_flag_tab => l_upd_tms_interface_flags,
                  x_return_status          => l_return_status );

Line: 1550


                        WSH_DEBUG_SV.log(l_sub_module_name,'failed in WND_PVT.UPDATE_TMS_INTERFACE_FLAG');

Line: 1661


1.Concurrent Request --TripStop and Delivery TMS_INTERFACE_FLAG is updated to newStatus = X_IN_PROCESS.
2.WSH_GLOG_OUTBOUND.GET_TRIP_OBJECTS - TripStop and Delivery TMS_INTERFACE_FLAG is updated to newStatus = AWAITING_ANSWER.
3.WSH_GLOG_OUTBOUND.GET_DELIVERY_OBJECTS - TripStop and Delivery TMS_INTERFACE_FLAG is updated to newStatus = AWAITING_ANSWER.
*/



-- +======================================================================+
PROCEDURE UPDATE_ENTITY_INTF_STATUS(
           x_return_status   OUT NOCOPY   VARCHAR2,
           p_entity_type     IN VARCHAR2,
           p_new_intf_status IN VARCHAR2,
           p_userId          IN    NUMBER DEFAULT NULL,
           p_respId          IN    NUMBER DEFAULT NULL,
           p_resp_appl_Id    IN    NUMBER DEFAULT NULL,
           p_entity_id_tab   IN OUT NOCOPY WSH_OTM_ID_TAB,
           p_error_id_tab    IN OUT NOCOPY WSH_OTM_ID_TAB
      ) IS

-- Declare local variables

l_sub_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ENTITY_INTF_STATUS';

Line: 1706


       select TMS_INTERFACE_FLAG,status_code,nvl(ignore_for_planning,'N') from wsh_new_deliveries
       where delivery_id = c_delivery_id;

Line: 1711


       select stop_id,TMS_INTERFACE_FLAG from wsh_trip_stops
       where trip_id = c_trip_id;

Line: 1716


  SAVEPOINT  UPDATE_ENTITY_INTF_STATUS;

Line: 1901


           WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Calling WSH API to update status-Del-Count',l_id_tab.COUNT);

Line: 1905


        WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG
              (P_DELIVERY_ID_TAB=>l_id_tab,
               P_TMS_INTERFACE_FLAG_TAB =>l_status_tab,
               X_RETURN_STATUS   =>l_return_status);

Line: 1913


           WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Calling WSH API to update status-Success-Del-Count',l_id_tab.COUNT);

Line: 2004


           WSH_DEBUG_SV.log(l_sub_module_name,'Calling WSH API-UPDATE_TMS_INTERFACE_FLAG Total stops ids',l_id_tab.COUNT);

Line: 2007


        WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG
            (P_STOP_ID_TAB=>l_id_tab,
             P_TMS_INTERFACE_FLAG_TAB =>l_status_tab,
             X_RETURN_STATUS   =>l_return_status);

Line: 2015


           WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip Calling WSH API to update status-Success-Trip-Stops-Count',l_id_tab.COUNT);

Line: 2037


        ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;

Line: 2045


        ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;

Line: 2053


        ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;

Line: 2061


        ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;

Line: 2069


        ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;

Line: 2077


        ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;

Line: 2088


        wsh_util_core.default_handler('WSH_OTM_OUTBOUND.UPDATE_ENTITY_INTF_STATUS');