[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tms_version_number,name,status_code
FROM wsh_new_deliveries
WHERE delivery_id = c_delivery_id;
SAVEPOINT before_insert;
l_delivery_tab.DELETE;
l_local_release_tab.DELETE;
l_del_int_id_tab.DELETE;
l_release_xid.DELETE;
l_ship_from_location_xid.DELETE;
l_ship_to_location_xid.DELETE;
l_release_refnum.DELETE;
l_earliest_pickup_date.DELETE;
l_latest_pickup_date.DELETE;
l_earliest_dropoff_date.DELETE;
l_latest_dropoff_date.DELETE;
l_release_freight_cost.delete;
l_release_freight_currency.delete;
l_interface_act_code := WSH_TMS_RELEASE.g_tms_delete_code;
END IF; -- Transaction_code <> 'DELETE'
WSH_DEBUG_SV.logmsg(l_module_name,'Bulk Inserting into wsh_new_del_interface ');
Insert into WSH_NEW_DEL_INTERFACE
(DELIVERY_INTERFACE_ID,
INTERFACE_ACTION_CODE,
DELIVERY_ID,
NAME,
PLANNED_FLAG,
STATUS_CODE,
INITIAL_PICKUP_LOCATION_ID,
ULTIMATE_DROPOFF_LOCATION_ID,
EARLIEST_PICKUP_DATE,
LATEST_PICKUP_DATE,
EARLIEST_DROPOFF_DATE,
LATEST_DROPOFF_DATE,
DELIVERY_TYPE,
TMS_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
Values (WSH_NEW_DEL_INTERFACE_S.nextval,
l_interface_act_code,
l_release_xid(j),
l_delivery_name(j),
'Y',
'OP',
l_ship_from_location_xid(j),
l_ship_to_location_xid(j),
l_earliest_pickup_date(j),
l_latest_pickup_date(j),
l_earliest_dropoff_date(j),
l_latest_dropoff_date(j),
'STANDARD',
l_release_refnum(j),
SYSDATE,
1,
SYSDATE,
1)
RETURNING DELIVERY_INTERFACE_ID BULK COLLECT INTO l_del_int_id_tab;
WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into wsh_freight_costs_inetrface ');
Insert into WSH_FREIGHT_COSTS_INTERFACE
( FREIGHT_COST_INTERFACE_ID,
INTERFACE_ACTION_CODE,
UNIT_AMOUNT,
TOTAL_AMOUNT,
CURRENCY_CODE,
DELIVERY_ID,
DELIVERY_INTERFACE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
( WSH_FREIGHT_COSTS_INTERFACE_S.nextval,
l_interface_act_code,
l_release_freight_cost(j),
l_release_freight_cost(j),
l_release_freight_currency(j),
l_release_freight_del_id(j),
l_release_freight_dint_id(j),
SYSDATE,
1,
SYSDATE,
1);
END IF; -- Transaction_code <> 'DELETE'
WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into wsh_trips interface ');
Insert into WSH_TRIPS_INTERFACE
(TRIP_INTERFACE_ID,
INTERFACE_ACTION_CODE,
NAME,
GROUP_ID,
TP_PLAN_NAME,
CARRIER_ID,
MODE_OF_TRANSPORT,
SERVICE_LEVEL,
FREIGHT_TERMS_CODE,
VEHICLE_ITEM_NAME,
VEHICLE_NUMBER,
VEHICLE_NUM_PREFIX,
--VESSEL,
--VOYAGE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
Values ( WSH_TRIPS_INTERFACE_S.nextval,
l_interface_act_code,
NULL,
nvl(l_group_id,WSH_TRIPS_INTERFACE_GROUP_S.nextval),
l_tp_plan_name(i),
l_carrier_id(i),
l_mode_of_transport(i),
l_service_level(i),
l_freight_terms_code(i),
l_vehicle_item_name(i),
l_vehicle_number(i),
l_vehicle_number_prefix(i),
--l_vessel(i),
--l_voyage_number(i),
SYSDATE,
1,
SYSDATE,
1)
RETURNING TRIP_INTERFACE_ID,GROUP_ID INTO l_trip_int_id(i),l_group_id;
l_stop_loc_tab.DELETE;
l_stop_int_id_tab.DELETE;
l_stop_location_id.DELETE;
l_stop_sequence_number.DELETE;
l_stop_trip_int_id.DELETE;
l_stop_pa_date.DELETE;
l_stop_pd_date.DELETE;
l_stop_aa_date.DELETE;
l_stop_ad_date.DELETE;
l_stop_dock_door_id.DELETE;
l_stop_start_time.DELETE;
l_stop_end_time.DELETE;
WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into wsh_trip_stops_interface ');
-- Inserting columns dock_name, start_time and end_time
Insert into WSH_TRIP_STOPS_INTERFACE
(STOP_INTERFACE_ID,
INTERFACE_ACTION_CODE,
TRIP_INTERFACE_ID,
STOP_LOCATION_ID,
STOP_SEQUENCE_NUMBER,
TIMEZONE_CODE,
PLANNED_ARRIVAL_DATE,
PLANNED_DEPARTURE_DATE,
DOCK_NAME,
START_TIME,
END_TIME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
Values (WSH_TRIP_STOPS_INTERFACE_S.nextval,
l_interface_act_code,
l_stop_trip_int_id(j),
l_stop_location_id(j),
l_stop_sequence_number(j),
l_stop_timezone_code(j),
l_stop_pa_date(j),
l_stop_pd_date(j),
l_stop_dock_door_id(j),
l_stop_start_time(j),
l_stop_end_time(j),
SYSDATE,
1,
SYSDATE,
1)
Returning STOP_INTERFACE_ID BULK COLLECT into l_stop_int_id_tab;
END IF; -- Transaction_code <> 'DELETE'
WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into wsh_del_legs_interface ');
Insert into WSH_DEL_LEGS_INTERFACE
(DELIVERY_LEG_INTERFACE_ID,
INTERFACE_ACTION_CODE,
DELIVERY_ID,
DELIVERY_INTERFACE_ID,
PICK_UP_STOP_INTERFACE_ID,
DROP_OFF_STOP_INTERFACE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
Values (WSH_DEL_LEGS_INTERFACE_S.nextval,
l_dleg_int_act_code(j),
l_dleg_del_id(j),
l_dleg_del_int_id(j),
l_dleg_pustop_id(j),
l_dleg_dostop_id(j),
SYSDATE,
1,
SYSDATE,
1);
ROLLBACK TO SAVEPOINT before_insert;
ROLLBACK TO SAVEPOINT before_insert;