[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Create_Update_Stop
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_action_code IN VARCHAR2,
p_trip_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_trip_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_stop_location_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_stop_location_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_planned_dep_date IN DATE DEFAULT FND_API.G_MISS_DATE,
x_stop_id OUT NOCOPY NUMBER,
pp_STOP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_TRIP_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_STOP_LOCATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_STOP_LOCATION_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_PLANNED_ARRIVAL_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_PLANNED_DEPARTURE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_ACTUAL_ARRIVAL_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_ACTUAL_DEPARTURE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_DEPARTURE_GROSS_WEIGHT IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_DEPARTURE_NET_WEIGHT IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_WEIGHT_UOM_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_WEIGHT_UOM_DESC IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_DEPARTURE_VOLUME IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_VOLUME_UOM_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_VOLUME_UOM_DESC IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_DEPARTURE_SEAL_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_DEPARTURE_FILL_PERCENT IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_STOP_SEQUENCE_NUMBER IN NUMBER DEFAULT FND_API.G_MISS_NUM,
pp_LOCK_STOP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
pp_STATUS_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_PENDING_INTERFACE_FLAG IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_TRANSACTION_HEADER_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
pp_WSH_LOCATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
pp_TRACKING_DRILLDOWN_FLAG IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_TRACKING_REMARKS IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_CARRIER_EST_DEPARTURE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_CARRIER_EST_ARRIVAL_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_LOADING_START_DATETIME IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_LOADING_END_DATETIME IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_UNLOADING_START_DATETIME IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_UNLOADING_END_DATETIME IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_TP_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE1 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_TP_ATTRIBUTE2 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE3 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE4 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE5 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE6 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_TP_ATTRIBUTE7 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_TP_ATTRIBUTE8 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE9 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE10 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE11 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_TP_ATTRIBUTE12 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE13 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_TP_ATTRIBUTE14 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_TP_ATTRIBUTE15 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_ATTRIBUTE1 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE2 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_ATTRIBUTE3 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE4 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE5 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_ATTRIBUTE6 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_ATTRIBUTE7 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE8 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE9 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE10 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE11 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE12 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE13 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE14 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR ,
pp_ATTRIBUTE15 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
pp_CREATION_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_CREATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_LAST_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_LAST_UPDATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_LAST_UPDATE_LOGIN IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_PROGRAM_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM ,
pp_PROGRAM_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
pp_REQUEST_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_new_stop_sequence IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_is_temp IN VARCHAR2 DEFAULT 'N',
p_wkend_layover_stops IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_wkday_layover_stops IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_shipments_type_flag IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
) IS
--
p_stop_info WSH_TRIP_STOPS_PVT.Trip_Stop_Rec_Type;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_STOP';
SAVEPOINT CREATE_UPDATE_STOP_PUB;
p_stop_info.LAST_UPDATE_DATE := pp_LAST_UPDATE_DATE;
p_stop_info.LAST_UPDATED_BY := pp_LAST_UPDATED_BY;
p_stop_info.LAST_UPDATE_LOGIN := pp_LAST_UPDATE_LOGIN;
p_stop_info.PROGRAM_UPDATE_DATE := pp_PROGRAM_UPDATE_DATE;
WSH_INTERFACE_GRP.Create_Update_Stop(p_api_version_number=>p_api_version_number,
p_init_msg_list=>FND_API.G_FALSE,
p_commit=>p_commit,
p_in_rec=>p_in_rec,
p_rec_attr_tab=>l_rec_attr_tab,
x_stop_out_tab=> l_stop_out_tab,
x_return_status=>x_return_status,
x_msg_count=>x_msg_count,
x_msg_data=>x_msg_data
);
-- update carrier est dates
IF (p_action_code = 'UPDATE') THEN
UPDATE WSH_TRIP_STOPS
SET CARRIER_EST_DEPARTURE_DATE = pp_CARRIER_EST_DEPARTURE_DATE,
CARRIER_EST_ARRIVAL_DATE = pp_CARRIER_EST_ARRIVAL_DATE
WHERE STOP_ID = pp_STOP_ID;
'FTE_MLS_WRAPPER.CREATE_UPDATE_STOP x_return_status',x_return_status);
'FTE_MLS_WRAPPER.CREATE_UPDATE_STOP Stop Seq Number ',
l_stop_seq);
'FTE_MLS_WRAPPER.CREATE_UPDATE_STOP New Stop Seq Number ',
p_new_stop_sequence);
'FTE_MLS_WRAPPER.CREATE_UPDATE_STOP Stop stop id ',
p_stop_info.stop_id);
'FTE_MLS_WRAPPER.CREATE_UPDATE_STOP Adding to temp table');
ROLLBACK TO CREATE_UPDATE_STOP_PUB;
ROLLBACK TO CREATE_UPDATE_STOP_PUB;
ROLLBACK TO CREATE_UPDATE_STOP_PUB;
END Create_Update_Stop;
p_action_prms.action_code := 'UPDATE-STATUS';
SELECT delivery_leg_id, sequence_number
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wts.trip_id = p_asg_trip_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wdl.pick_up_stop_id = p_asg_pickup_stop_id
AND wdl.delivery_id = p_delivery_id;
PROCEDURE Create_Update_Trip
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_trip_id OUT NOCOPY NUMBER,
x_trip_name OUT NOCOPY VARCHAR2,
p_action_code IN VARCHAR2,
p_rec_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ARRIVE_AFTER_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_ARRIVE_AFTER_TRIP_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_VEHICLE_ITEM_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_VEHICLE_ITEM_DESC IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_VEHICLE_ORGANIZATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_VEHICLE_ORGANIZATION_COD IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_VEHICLE_NUMBER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_VEHICLE_NUM_PREFIX IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CARRIER_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_SHIP_METHOD_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_SHIP_METHOD_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ROUTE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_ROUTING_INSTRUCTIONS IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE1 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE2 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE3 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE4 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE5 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE6 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE7 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE8 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE9 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE10 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE11 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE12 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE13 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE14 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE15 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_SERVICE_LEVEL IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_MODE_OF_TRANSPORT IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CONSOLIDATION_ALLOWED IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_PLANNED_FLAG IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_STATUS_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_FREIGHT_TERMS_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_LOAD_TENDER_STATUS IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ROUTE_LANE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_LANE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_SCHEDULE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_BOOKING_NUMBER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CREATION_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
p_rec_CREATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_LAST_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
p_rec_LAST_UPDATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_LAST_UPDATE_LOGIN IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_PROGRAM_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_PROGRAM_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
p_rec_REQUEST_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_trip_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_carrier_contact_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_carrier_contact_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_shipper_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_shipper_wait_time IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_wait_time_uom IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_wf_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_wf_process_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_wf_item_key IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_load_tender_number IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_action IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_autoaccept IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_url IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_carrier_remarks IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_operator IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_IGNORE_FOR_PLANNING IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CONSIGNEE_CAR_AC_NO IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CARRIER_REF_NUMBER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ROUTING_RULE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_APPEND_FLAG IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_RANK_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM
)
IS
l_creation_date DATE;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SAVEPOINT CREATE_UPDATE_TRIP_OLD_PUB;
Create_Update_Trip(p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
x_trip_id => l_trip_id ,
x_trip_name => x_trip_name ,
x_CREATION_DATE => l_CREATION_DATE ,
x_CREATED_BY => l_CREATED_BY ,
x_LAST_UPDATE_DATE => l_LAST_UPDATE_DATE ,
x_LAST_UPDATED_BY => l_LAST_UPDATED_BY ,
x_LAST_UPDATE_LOGIN => l_LAST_UPDATE_LOGIN ,
p_action_code => p_action_code ,
p_rec_TRIP_ID => p_rec_TRIP_ID,
p_rec_NAME => p_rec_NAME ,
p_rec_ARRIVE_AFTER_TRIP_ID => p_rec_ARRIVE_AFTER_TRIP_ID ,
p_rec_ARRIVE_AFTER_TRIP_NAME => p_rec_ARRIVE_AFTER_TRIP_NAME,
p_rec_VEHICLE_ITEM_ID => p_rec_VEHICLE_ITEM_ID ,
p_rec_VEHICLE_ITEM_DESC => p_rec_VEHICLE_ITEM_DESC ,
p_rec_VEHICLE_ORGANIZATION_ID => p_rec_VEHICLE_ORGANIZATION_ID,
p_rec_VEHICLE_ORGANIZATION_COD => p_rec_VEHICLE_ORGANIZATION_COD,
p_rec_VEHICLE_NUMBER => p_rec_VEHICLE_NUMBER ,
p_rec_VEHICLE_NUM_PREFIX => p_rec_VEHICLE_NUM_PREFIX ,
p_rec_CARRIER_ID => p_rec_CARRIER_ID ,
p_rec_SHIP_METHOD_CODE => p_rec_SHIP_METHOD_CODE ,
p_rec_SHIP_METHOD_NAME => p_rec_SHIP_METHOD_NAME ,
p_rec_ROUTE_ID => p_rec_ROUTE_ID ,
p_rec_ROUTING_INSTRUCTIONS => p_rec_ROUTING_INSTRUCTIONS ,
p_rec_ATTRIBUTE_CATEGORY => p_rec_ATTRIBUTE_CATEGORY ,
p_rec_ATTRIBUTE1 => p_rec_ATTRIBUTE1 ,
p_rec_ATTRIBUTE2 => p_rec_ATTRIBUTE2 ,
p_rec_ATTRIBUTE3 => p_rec_ATTRIBUTE3 ,
p_rec_ATTRIBUTE4 => p_rec_ATTRIBUTE4 ,
p_rec_ATTRIBUTE5 => p_rec_ATTRIBUTE5 ,
p_rec_ATTRIBUTE6 => p_rec_ATTRIBUTE6 ,
p_rec_ATTRIBUTE7 => p_rec_ATTRIBUTE7 ,
p_rec_ATTRIBUTE8 => p_rec_ATTRIBUTE8 ,
p_rec_ATTRIBUTE9 => p_rec_ATTRIBUTE9 ,
p_rec_ATTRIBUTE10 => p_rec_ATTRIBUTE10 ,
p_rec_ATTRIBUTE11 => p_rec_ATTRIBUTE11 ,
p_rec_ATTRIBUTE12 => p_rec_ATTRIBUTE12 ,
p_rec_ATTRIBUTE13 => p_rec_ATTRIBUTE13 ,
p_rec_ATTRIBUTE14 => p_rec_ATTRIBUTE14 ,
p_rec_ATTRIBUTE15 => p_rec_ATTRIBUTE15 ,
p_rec_SERVICE_LEVEL => p_rec_SERVICE_LEVEL ,
p_rec_MODE_OF_TRANSPORT => p_rec_MODE_OF_TRANSPORT ,
p_rec_CONSOLIDATION_ALLOWED => p_rec_CONSOLIDATION_ALLOWED ,
p_rec_PLANNED_FLAG => p_rec_PLANNED_FLAG ,
p_rec_STATUS_CODE => p_rec_STATUS_CODE ,
p_rec_FREIGHT_TERMS_CODE => p_rec_FREIGHT_TERMS_CODE ,
p_rec_LOAD_TENDER_STATUS => p_rec_LOAD_TENDER_STATUS ,
p_rec_ROUTE_LANE_ID => p_rec_ROUTE_LANE_ID ,
p_rec_LANE_ID => p_rec_LANE_ID ,
p_rec_SCHEDULE_ID => p_rec_SCHEDULE_ID ,
p_rec_BOOKING_NUMBER => p_rec_BOOKING_NUMBER ,
p_rec_CREATION_DATE => p_rec_CREATION_DATE ,
p_rec_CREATED_BY => p_rec_CREATED_BY ,
p_rec_LAST_UPDATE_DATE => p_rec_LAST_UPDATE_DATE ,
p_rec_LAST_UPDATED_BY => p_rec_LAST_UPDATED_BY ,
p_rec_LAST_UPDATE_LOGIN => p_rec_LAST_UPDATE_LOGIN ,
p_rec_PROGRAM_APPLICATION_ID => p_rec_PROGRAM_APPLICATION_ID,
p_rec_PROGRAM_ID => p_rec_PROGRAM_ID ,
p_rec_PROGRAM_UPDATE_DATE => p_rec_PROGRAM_UPDATE_DATE ,
p_rec_REQUEST_ID => p_rec_REQUEST_ID ,
p_carrier_contact_id => p_carrier_contact_id ,
p_shipper_name => p_shipper_name ,
p_shipper_wait_time => p_shipper_wait_time ,
p_wait_time_uom => p_wait_time_uom ,
p_action => p_action ,
p_carrier_remarks => p_carrier_remarks ,
p_operator => p_operator,
p_rec_IGNORE_FOR_PLANNING => p_rec_IGNORE_FOR_PLANNING ,
p_rec_CONSIGNEE_CAR_AC_NO => p_rec_CONSIGNEE_CAR_AC_NO ,
p_rec_CARRIER_REF_NUMBER => p_rec_CARRIER_REF_NUMBER ,
p_rec_ROUTING_RULE_ID => p_rec_ROUTING_RULE_ID ,
p_rec_APPEND_FLAG => p_rec_APPEND_FLAG ,
p_rec_RANK_ID => p_rec_RANK_ID );
ROLLBACK TO CREATE_UPDATE_TRIP_OLD_PUB;
ROLLBACK TO CREATE_UPDATE_TRIP_OLD_PUB;
ROLLBACK TO CREATE_UPDATE_TRIP_OLD_PUB;
END Create_Update_Trip;
PROCEDURE Create_Update_Trip
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_trip_id OUT NOCOPY NUMBER,
x_trip_name OUT NOCOPY VARCHAR2,
x_CREATION_DATE OUT NOCOPY DATE,
x_CREATED_BY OUT NOCOPY NUMBER,
x_LAST_UPDATE_DATE OUT NOCOPY DATE,
x_LAST_UPDATED_BY OUT NOCOPY NUMBER,
x_LAST_UPDATE_LOGIN OUT NOCOPY NUMBER,
p_action_code IN VARCHAR2,
p_rec_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ARRIVE_AFTER_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_ARRIVE_AFTER_TRIP_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_VEHICLE_ITEM_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_VEHICLE_ITEM_DESC IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_VEHICLE_ORGANIZATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_VEHICLE_ORGANIZATION_COD IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_VEHICLE_NUMBER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_VEHICLE_NUM_PREFIX IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CARRIER_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_SHIP_METHOD_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_SHIP_METHOD_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ROUTE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_ROUTING_INSTRUCTIONS IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE1 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE2 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE3 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE4 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE5 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE6 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE7 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE8 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE9 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE10 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE11 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE12 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE13 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE14 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ATTRIBUTE15 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_SERVICE_LEVEL IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_MODE_OF_TRANSPORT IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CONSOLIDATION_ALLOWED IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_PLANNED_FLAG IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_STATUS_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_FREIGHT_TERMS_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_LOAD_TENDER_STATUS IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ROUTE_LANE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_LANE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_SCHEDULE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_BOOKING_NUMBER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CREATION_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
p_rec_CREATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_LAST_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
p_rec_LAST_UPDATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_LAST_UPDATE_LOGIN IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_PROGRAM_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_PROGRAM_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
p_rec_REQUEST_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_carrier_contact_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_shipper_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_shipper_wait_time IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_wait_time_uom IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_action IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_carrier_remarks IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_operator IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_IGNORE_FOR_PLANNING IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CONSIGNEE_CAR_AC_NO IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_CARRIER_REF_NUMBER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_ROUTING_RULE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_rec_APPEND_FLAG IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_rec_RANK_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM
) IS
--
p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TRIP';
SAVEPOINT CREATE_UPDATE_TRIP_PUB;
p_trip_info.LAST_UPDATE_DATE := p_rec_LAST_UPDATE_DATE;
p_trip_info.LAST_UPDATED_BY := p_rec_LAST_UPDATED_BY;
p_trip_info.LAST_UPDATE_LOGIN := p_rec_LAST_UPDATE_LOGIN;
p_trip_info.PROGRAM_UPDATE_DATE := p_rec_PROGRAM_UPDATE_DATE;
IF (p_action_code = 'UPDATE')
THEN
--{
-- Get Current load tender status of trip
-- Modified for REL12 HBHAGAVA
SELECT LOAD_TENDER_STATUS, LANE_ID INTO l_db_tender_status, l_db_lane_id
FROM WSH_TRIPS
WHERE TRIP_ID = p_rec_TRIP_ID;
-- In packj User can update trip name/ routing instructions after tendered.
-- In order to retain teneder parameters we have to set these values.
-- added by HBHAGAVA
p_trip_info.load_tender_status := FND_API.G_MISS_CHAR;
IF (p_action IS NULL OR p_action <> FTE_TENDER_PVT.S_SHIPPER_UPDATED)
THEN
--call wsh public API
WSH_INTERFACE_GRP.Create_Update_Trip
(
p_api_version_number =>p_api_version_number,
p_init_msg_list =>FND_API.G_FALSE,
p_commit =>p_commit,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_trip_info_tab =>p_trip_info_tab,
p_in_rec =>p_trip_in_rec,
x_out_tab =>x_out_tab
);
WSH_INTERFACE_GRP.Create_Update_Trip ' ||
l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY,LAST_UPDATE_LOGIN
INTO x_CREATION_DATE,x_CREATED_BY,x_LAST_UPDATE_DATE,
x_LAST_UPDATED_BY, x_LAST_UPDATE_LOGIN
FROM WSH_TRIPS
WHERE TRIP_ID = p_rec_TRIP_ID;
ROLLBACK TO CREATE_UPDATE_TRIP_PUB;
ROLLBACK TO CREATE_UPDATE_TRIP_PUB;
ROLLBACK TO CREATE_UPDATE_TRIP_PUB;
END Create_Update_Trip;
SELECT CARRIER_ID, NAME, LANE_ID, MODE_OF_TRANSPORT, SERVICE_LEVEL, SHIPMENTS_TYPE_FLAG,
LOAD_TENDER_STATUS
FROM WSH_TRIPS
WHERE TRIP_ID = p_tripID;
SELECT 'Y', decode(ENABLE_AUTO_TENDER,null,'N','N','N','Y') AUTO_TENDER,
party_name carrier_name
FROM WSH_CARRIER_SITES sites, HZ_PARTIES parties
WHERE CARRIER_ID = p_CarrierID
AND parties.party_id = carrier_id
AND (sites.EMAIL_ADDRESS IS NOT NULL OR TENDER_TRANSMISSION_METHOD IS NOT NULL)
AND ROWNUM = 1;
SELECT COUNT(LEGS.DELIVERY_ID)
FROM WSH_DELIVERY_LEGS LEGS,
WSH_TRIP_STOPS ST
WHERE
ST.STOP_ID = LEGS.PICK_UP_STOP_ID
AND ST.TRIP_ID = p_TripID;
x_LAST_UPDATE_DATE DATE;
x_LAST_UPDATED_BY NUMBER;
x_LAST_UPDATE_LOGIN NUMBER;
SELECT STOP_LOCATION_ID
FROM WSH_TRIP_STOPS
WHERE TRIP_ID = p_tripID
ORDER BY PLANNED_ARRIVAL_DATE,
STOP_SEQUENCE_NUMBER,
STOP_ID;
SELECT STOP_LOCATION_ID
FROM WSH_TRIP_STOPS
WHERE TRIP_ID = p_tripID
ORDER BY PLANNED_ARRIVAL_DATE DESC,
STOP_SEQUENCE_NUMBER DESC,
STOP_ID DESC;
SELECT stop_id,
departure_gross_weight,departure_volume,weight_uom_code,volume_uom_code
FROM WSH_TRIP_STOPS
WHERE TRIP_ID = p_tripID
ORDER BY PLANNED_ARRIVAL_DATE DESC,
STOP_SEQUENCE_NUMBER DESC,
STOP_ID DESC;
SELECT ORG_DEF.ORGANIZATION_ID
FROM
ORG_ORGANIZATION_DEFINITIONS ORG_DEF,
HR_ALL_ORGANIZATION_UNITS HR_ALL_ORG_UNT
WHERE
ORG_DEF.ORGANIZATION_ID = HR_ALL_ORG_UNT.ORGANIZATION_ID
AND HR_ALL_ORG_UNT.LOCATION_ID = p_stopID;
SELECT
REL.PARTY_ID CONTACT_PARTY_ID,
PARTY.PARTY_NAME NAME,
CAR_SITES.TENDER_WAIT_TIME SHIPPER_WAIT_TIME,
CAR_SITES.WAIT_TIME_UOM WAIT_TIME_UOM,
CAR_SITES.AUTO_ACCEPT_LOAD_TENDER AUTO_ACCEPT_LOAD_TENDER
FROM
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_PARTY_SITES SITES,
HZ_ORG_CONTACTS CONT,
HZ_CONTACT_POINTS POINTS,
--HZ_CONTACT_POINTS PHONE,
WSH_CARRIER_SITES CAR_SITES,
WSH_LOCATIONS WL
WHERE
REL.OBJECT_ID = PARTY.PARTY_ID
AND REL.SUBJECT_TYPE = 'ORGANIZATION'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND SITES.PARTY_ID = REL.SUBJECT_ID
AND CONT.PARTY_SITE_ID = SITES.PARTY_SITE_ID
AND CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND POINTS.OWNER_TABLE_ID = REL.PARTY_ID
AND POINTS.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND POINTS.CONTACT_POINT_TYPE = 'EMAIL'
--AND PHONE.OWNER_TABLE_ID(+) = REL.PARTY_ID
--AND PHONE.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
--AND PHONE.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CAR_SITES.CARRIER_SITE_ID = SITES.PARTY_SITE_ID
AND WL.WSH_LOCATION_ID = SITES.LOCATION_ID
AND (DECODE(CAR_SITES.EMAIL_ADDRESS, POINTS.EMAIL_ADDRESS,'Y','N') = 'Y'
OR CAR_SITES.TENDER_TRANSMISSION_METHOD IS NOT NULL)
AND CAR_SITES.CARRIER_ID = p_CarrierID;
SELECT
REL.PARTY_ID CONTACT_PARTY_ID,
PARTY.PARTY_NAME NAME,
CAR_SITES.TENDER_WAIT_TIME SHIPPER_WAIT_TIME,
CAR_SITES.WAIT_TIME_UOM WAIT_TIME_UOM,
CAR_SITES.AUTO_ACCEPT_LOAD_TENDER AUTO_ACCEPT_LOAD_TENDER
FROM
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_PARTY_SITES SITES,
HZ_ORG_CONTACTS CONT,
HZ_CONTACT_POINTS POINTS,
--HZ_CONTACT_POINTS PHONE,
WSH_CARRIER_SITES CAR_SITES,
WSH_LOCATIONS WL,
WSH_ORG_CARRIER_SITES ORG_CAR_SITES
WHERE
REL.OBJECT_ID = PARTY.PARTY_ID
AND REL.SUBJECT_TYPE = 'ORGANIZATION'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND SITES.PARTY_ID = REL.SUBJECT_ID
AND CONT.PARTY_SITE_ID = SITES.PARTY_SITE_ID
AND CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND POINTS.OWNER_TABLE_ID = REL.PARTY_ID
AND POINTS.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND POINTS.CONTACT_POINT_TYPE = 'EMAIL'
--AND PHONE.OWNER_TABLE_ID(+) = REL.PARTY_ID
--AND PHONE.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
--AND PHONE.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CAR_SITES.CARRIER_SITE_ID = SITES.PARTY_SITE_ID
AND WL.WSH_LOCATION_ID = SITES.LOCATION_ID
AND (DECODE(CAR_SITES.EMAIL_ADDRESS, POINTS.EMAIL_ADDRESS,'Y','N') = 'Y'
OR CAR_SITES.TENDER_TRANSMISSION_METHOD IS NOT NULL)
AND CAR_SITES.CARRIER_ID = p_CarrierID
AND ORG_CAR_SITES.ENABLED_FLAG = 'Y'
AND ORG_CAR_SITES.ORGANIZATION_ID = p_orgID;
WSH_DEBUG_SV.logmsg(l_module_name,'Updated the Trip ' || l_return_status || ' l_msg_data = ' || l_msg_data);
WSH_DEBUG_SV.logmsg(l_module_name,'Failed to update the trip. ' || l_msg_data);
SELECT NAME, PLANNED_FLAG, LOAD_TENDER_STATUS,
CARRIER_ID, CARRIER_CONTACT_ID, WF_ITEM_KEY, LANE_ID
FROM WSH_TRIPS
WHERE TRIP_ID = p_tripID;
x_LAST_UPDATE_DATE DATE;
x_LAST_UPDATED_BY NUMBER;
x_LAST_UPDATE_LOGIN NUMBER;
WSH_DEBUG_SV.logmsg(l_module_name,'Updated the Trip ' || l_return_status || ' l_msg_data = ' || l_msg_data);
WSH_DEBUG_SV.logmsg(l_module_name,'Failed to update the trip.' || l_msg_data);
WSH_DEBUG_SV.logmsg(l_module_name,'Updated the trip successfully.' || l_msg_data);
FTE_TENDER_PVT.DELETE_TENDER_SNAPSHOT(
p_init_msg_list => FND_API.G_FALSE,
p_tender_id => p_trip_id_tab(i),
x_return_status => l_return_status,
x_msg_count => l_number_of_errors,
x_msg_data => l_msg_data);
-- 'DELETE'
*/
l_wsh_action_prms.action_code := p_action_prms.action_code;
-- Step 4.3 copy selection issue flag of l_wsh_out_rec
-- to selection issue flag of x_action_out_rec
-- no need to copy this value since it is used by shipping transaction form
-- x_action_out_rec.selection_issue_flag := l_wsh_out_rec.selection_issue_flag;
FTE_MLS_WRAPPER.G_STOPS_TAB_REC.delete;
FTE_MLS_WRAPPER.G_STOPS_SEQ_TAB.delete;
WSH_DEBUG_SV.logmsg(l_module_name, ' deleteing G_STOPS_TAB_REC ' );
WSH_DEBUG_SV.logmsg(l_module_name, ' deleteing G_STOPS_SEQ_TAB ' );
l_action_code := 'UPDATE';
Create_Update_Stop(p_api_version_number => p_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
p_in_rec => l_in_rec,
p_rec_attr_tab => l_rec_attr_tab,
x_stop_out_tab => l_stop_out_tab,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
PROCEDURE CREATE_UPDATE_STOP(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_in_rec IN WSH_TRIP_STOPS_GRP.stopInRecType,
p_rec_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
x_stop_out_tab OUT NOCOPY WSH_TRIP_STOPS_GRP.stop_out_tab_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_STOP';
SAVEPOINT CREATE_UPDATE_STOP_PUB;
WSH_INTERFACE_GRP.Create_Update_Stop(p_api_version_number=>p_api_version_number,
p_init_msg_list=>FND_API.G_FALSE,
p_commit=>p_commit,
p_in_rec=>p_in_rec,
p_rec_attr_tab=>p_rec_attr_tab,
x_stop_out_tab=> x_stop_out_tab,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data
);
ROLLBACK TO CREATE_UPDATE_STOP_PUB;
ROLLBACK TO CREATE_UPDATE_STOP_PUB;
ROLLBACK TO CREATE_UPDATE_STOP_PUB;
END CREATE_UPDATE_STOP;
l_selection_issue_flag VARCHAR(1);
-- set selection issue flag
l_selection_issue_flag := l_detail_out_rec.selection_issue_flag;
PROCEDURE Create_Update_Delivery_Detail
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_detail_info_tab IN FTE_DDL_ATTR_TAB_TYPE,
p_action_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_detail_id_tab OUT NOCOPY FTE_ID_TAB_TYPE
) IS
l_detail_attr_tab_type wsh_glbl_var_strct_grp.delivery_details_Attr_tbl_Type;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_DDL';
SAVEPOINT DDL_CREATE_UPDATE_PUB;
l_detail_attr_rec.last_update_date := l_fte_detail_attr_rec.last_update_date;
l_detail_attr_rec.last_update_login := l_fte_detail_attr_rec.last_update_login;
l_detail_attr_rec.last_updated_by := l_fte_detail_attr_rec.last_updated_by;
l_detail_attr_rec.program_update_date := l_fte_detail_attr_rec.program_update_date;
-- Call WSH's Create_Update_DDL API
WSH_INTERFACE_GRP.Create_Update_Delivery_Detail (
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_detail_info_tab => l_detail_attr_tab_type,
p_IN_rec => l_detail_in_rec,
x_OUT_rec => l_detail_out_rec -- WSH_UTIL_CORE.Id_Tab_Type
);
ROLLBACK TO DDL_CREATE_UPDATE_PUB;
ROLLBACK TO DDL_CREATE_UPDATE_PUB;
ROLLBACK TO DDL_CREATE_UPDATE_PUB;
END Create_Update_Delivery_Detail;
l_selection_issue_flag VARCHAR(1);
SELECT stop_id, trip_id, planned_arrival_Date,planned_departure_date
FROM wsh_trip_stops
WHERE trip_id = p_tripId;
-- set selection issue flag
l_selection_issue_flag := l_delivery_out_rec.selection_issue_flag;
l_selection_issue_flag VARCHAR(1);
-- set selection issue flag
l_selection_issue_flag := l_delivery_out_rec.selection_issue_flag;
PROCEDURE Create_Update_Delivery
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_dlvy_info_tab IN FTE_DLV_ATTR_TAB_TYPE,
p_action_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_dlvy_id_tab OUT NOCOPY FTE_ID_TAB_TYPE
) IS
l_dlvy_attr_tab_type WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_DLVY';
SAVEPOINT DELIVERY_CREATE_UPDATE_PUB;
l_dlvy_attr_rec.LAST_UPDATE_DATE := l_fte_dlvy_attr_rec.LAST_UPDATE_DATE;
l_dlvy_attr_rec.LAST_UPDATED_BY := l_fte_dlvy_attr_rec.LAST_UPDATED_BY;
l_dlvy_attr_rec.LAST_UPDATE_LOGIN := l_fte_dlvy_attr_rec.LAST_UPDATE_LOGIN;
l_dlvy_attr_rec.PROGRAM_UPDATE_DATE := l_fte_dlvy_attr_rec.PROGRAM_UPDATE_DATE;
-- Call WSH's Create_Update_DLVY API
WSH_INTERFACE_GRP.Create_Update_Delivery (
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_in_rec => l_dlvy_in_rec,
p_rec_attr_tab => l_dlvy_attr_tab_type,
x_del_out_rec_tab => l_dlvy_out_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO DELIVERY_CREATE_UPDATE_PUB;
ROLLBACK TO DELIVERY_CREATE_UPDATE_PUB;
ROLLBACK TO DELIVERY_CREATE_UPDATE_PUB;
END CREATE_UPDATE_DELIVERY;
p_action_code => 'UPDATE',
p_tender_action => p_action_prms.action_code,
p_trip_name => p_trip_info_rec.TRIP_NAME);
SELECT SHIP_METHOD_CODE,MODE_OF_TRANSPORT
INTO l_db_sm_code,db_mode_of_transport FROM WSH_TRIPS
WHERE TRIP_ID = p_trip_info_rec.trip_id;
-- Update Trip information
INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
p_trip_in_rec.action_code :='UPDATE';
WSH_INTERFACE_GRP.Create_Update_Trip
(
p_api_version_number =>p_api_version_number,
p_init_msg_list =>FND_API.G_FALSE,
p_commit =>FND_API.G_FALSE,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_trip_info_tab =>p_trip_info_tab,
p_in_rec =>p_trip_in_rec,
x_out_tab =>x_out_tab
);
UPDATE WSH_TRIPS
SET shipper_wait_time = l_trip_info_rec.SHIP_WAIT_TIME,
wait_time_uom = l_trip_info_rec.SHIP_TIME_UOM,
wf_name = l_trip_info_rec.wf_name,
wf_process_name = l_trip_info_rec.wf_process_name,
wf_item_key = l_trip_info_rec.wf_item_key,
load_Tender_number = l_trip_info_rec.trip_id,
load_tender_status = l_trip_info_rec.tender_status,
carrier_contact_id = l_trip_info_rec.car_contact_id,
carrier_response = null,
carrier_reference_number = null,
operator = null,
load_Tendered_time = SYSDATE
WHERE trip_id = l_trip_info_rec.trip_id;
WSH_DEBUG_SV.logmsg(l_module_name,' WSH_INTERFACE_GRP.CREATE_UPDATE_TRIP return status '
|| l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
select wf_item_key into l_item_key from wsh_trips
where trip_id = p_trip_info_rec.trip_id;
-- Update trip information
p_trip_info.TRIP_ID := p_trip_info_rec.trip_id;
p_trip_in_rec.action_code :='UPDATE';
WSH_INTERFACE_GRP.Create_Update_Trip
(
p_api_version_number =>p_api_version_number,
p_init_msg_list =>FND_API.G_FALSE,
p_commit =>FND_API.G_FALSE,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_trip_info_tab =>p_trip_info_tab,
p_in_rec =>p_trip_in_rec,
x_out_tab =>x_out_tab
);
WSH_DEBUG_SV.logmsg(l_module_name,' REturn value from Create update trip ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
-- Update trip information
p_trip_info.TRIP_ID := p_trip_info_rec.trip_id;
p_trip_in_rec.action_code :='UPDATE';
WSH_INTERFACE_GRP.Create_Update_Trip
(
p_api_version_number =>p_api_version_number,
p_init_msg_list =>FND_API.G_FALSE,
p_commit =>FND_API.G_FALSE,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_trip_info_tab =>p_trip_info_tab,
p_in_rec =>p_trip_in_rec,
x_out_tab =>x_out_tab
);
WSH_DEBUG_SV.logmsg(l_module_name,' REturn value from Create update trip ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
select wf_item_key into l_item_key from wsh_trips
where trip_id = p_trip_info_rec.trip_id;
ELSIF (l_tender_action = FTE_TENDER_PVT.S_SHIPPER_UPDATED) THEN
--{
IF l_debug_on
THEN
WSH_DEBUG_SV.logmsg(l_module_name,' Action ' || l_tender_action,
WSH_DEBUG_SV.C_PROC_LEVEL);
select wf_item_key into l_item_key from wsh_trips
where trip_id = p_trip_info_rec.trip_id;
FTE_TENDER_PVT.S_SHIPPER_UPDATED, -- status
null,-- car_contact_id
null, -- car contact name
null, -- auto_accept
null, -- auto tender
null, -- ship wait time
null, -- ship time uom
'FTETEREQ', -- wf name
'TENDER_UPDATE_PROCESS', -- wf process name
l_item_key, --wf item key
null,
null,null,null,null,
null,null,
null,null);
FTE_TENDER_PVT.HANDLE_UPDATE_TENDER(
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trip_info => l_trip_info_rec);
-- other values are not updated by calling application,
-- we are going to default them from the first
-- found carrier site.
IF FND_API.to_Boolean( p_init_msg_list )
THEN
FND_MSG_PUB.initialize;
PROCEDURE UPDATE_SERVICE_ON_TRIP
(
p_API_VERSION_NUMBER IN NUMBER,
p_INIT_MSG_LIST IN VARCHAR2,
p_COMMIT IN VARCHAR2,
p_CALLER IN VARCHAR2,
p_SERVICE_ACTION IN VARCHAR2,
p_DELIVERY_ID IN NUMBER,
p_DELIVERY_LEG_ID IN NUMBER,
p_TRIP_ID IN NUMBER,
p_LANE_ID IN NUMBER,
p_SCHEDULE_ID IN NUMBER,
p_CARRIER_ID IN NUMBER,
p_SERVICE_LEVEL IN VARCHAR2,
p_MODE_OF_TRANSPORT IN VARCHAR2,
p_VEHICLE_ITEM_ID IN NUMBER,
p_VEHICLE_ORG_ID IN NUMBER,
p_CONSIGNEE_CARRIER_AC_NO IN VARCHAR2,
p_FREIGHT_TERMS_CODE IN VARCHAR2,
x_RETURN_STATUS OUT NOCOPY VARCHAR2,
x_MSG_COUNT OUT NOCOPY NUMBER,
x_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
l_ret_trip_id NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.'|| G_PKG_NAME ||'.'||'UPDATE_SERVICE_ON_TRIP';
CREATE_UPDATE_TRIP(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_trip_id => l_ret_trip_id,
x_trip_name => l_ret_trip_name,
p_action_code => 'UPDATE',
p_rec_TRIP_ID => p_trip_id,
p_rec_LANE_ID => p_lane_id,
p_rec_SCHEDULE_ID => p_schedule_id,
p_rec_CARRIER_ID => p_carrier_id,
p_rec_SERVICE_LEVEL => p_service_level,
p_rec_MODE_OF_TRANSPORT => p_mode_of_transport,
p_rec_SHIP_METHOD_CODE => NULL,
p_rec_VEHICLE_ORGANIZATION_ID => p_vehicle_org_id,
p_rec_VEHICLE_ITEM_ID => p_vehicle_item_id,
p_rec_CONSIGNEE_CAR_AC_NO => p_CONSIGNEE_CARRIER_AC_NO,
p_rec_FREIGHT_TERMS_CODE => p_FREIGHT_TERMS_CODE);
-- If service action is UPDATE,
-- raise a cancel service event first,
-- then raise a select service event
-- Else raise only a select service event
/* R12 Hiding Project
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name, 'Service Action:'||p_service_action);
IF ( p_service_action = 'UPDATE' OR p_service_action = 'REMOVE') THEN
FTE_WORKFLOW_UTIL.TRIP_CANCEL_SERVICE(
p_trip_id => p_trip_id,
x_return_status => l_return_status);
IF (p_service_action = 'ADD_NEW' OR p_service_action = 'UPDATE') THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name, 'Raising Select Trip Service Event');
FTE_WORKFLOW_UTIL.TRIP_SELECT_SERVICE_INIT(
p_trip_id => p_trip_id,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'API:FTE_WORKFLOW_UTIL: SELECT SERVICE');
END UPDATE_SERVICE_ON_TRIP;