Untitled diff
111 removals
249 lines
22 additions
164 lines
USE [AAD]
USE [AAD]
GO
GO
/****** Object: StoredProcedure [dbo].[usp_vo_get_destination_location] Script Date: 10/08/2013 14:42:08 ******/
/****** Object: StoredProcedure [dbo].[usp_vo_assign_user_trolley] Script Date: 10/08/2013 14:39:29 ******/
SET ANSI_NULLS ON
SET ANSI_NULLS ON
GO
GO
SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER ON
GO
GO
/*
/*
BTH 10-11-2013
BTH
Update the trolley's entry in t_hu_master by settings it's user_id to the current operator.
Get the destination put location on the operator's current carton.
If the location is in the operator's current zone, return check digits.
If not in the zone, check the lane. A lane may or may not encompass
multiple zones (this depends on the area of the warehouse). If the
location exists in the lane, return code to prompt user to pass carton
to next zone. If not in the lane, return data error.
*/
*/
CREATE PROCEDURE [dbo].[usp_vo_get_destination_location]
CREATE PROCEDURE [dbo].[usp_vo_assign_user_trolley]
@in_time_stamp DATETIME,
@in_time_stamp DATETIME,
@in_device_serial NVARCHAR(30),
@in_device_serial NVARCHAR(30),
@in_operator NVARCHAR(30),
@in_operator NVARCHAR(30),
@in_msg_id NVARCHAR(100),
@in_msg_id NVARCHAR(100),
@in_language_code NVARCHAR(5),
@in_language_code NVARCHAR(5),
@in_wh_id NVARCHAR(10),
@in_wh_id NVARCHAR(10),
@in_hu_id NVARCHAR(50),
@in_trolley_hu_id NVARCHAR(50)
@in_zone NVARCHAR(50),
@in_lane NVARCHAR(20)
AS
AS
/**********************
/**********************
Sample Call
Sample Call
************************/
************************/
DECLARE @errorcode INT
DECLARE @errorcode INT
DECLARE @message NVARCHAR(250)
DECLARE @message NVARCHAR(250)
DECLARE @errorname NVARCHAR (30)
DECLARE @errorname NVARCHAR (30)
DECLARE @DEADLOCK NVARCHAR(20),
DECLARE @DEADLOCK NVARCHAR(20),
@SQLERROR NVARCHAR(20),
@SQLERROR NVARCHAR(20),
@NOPUTLOCCART NVARCHAR(50),
@PASSTONEXTZONE NVARCHAR(50),
@PUTLOCNOTINLANE NVARCHAR(50),
--Error messages
--Error messages
@current_xml_response XML
@current_xml_response XML
DECLARE @destination_location NVARCHAR(100),
@destination_zone NVARCHAR(50),
@destination_check_digits NVARCHAR(10)
SELECT
SELECT
@DEADLOCK = 'DEADLOCK',
@DEADLOCK = 'DEADLOCK',
@SQLERROR = 'SQL_ERROR',
@SQLERROR = 'SQL_ERROR'
@NOPUTLOCCART = 'NO_PUT_LOC_CART',
@PASSTONEXTZONE = 'PASS_TO_NEXT_ZONE',
@PUTLOCNOTINLANE = 'PUT_LOC_NOT_IN_LANE'
SET @errorcode = 0
SET @errorcode = 0
SET @message = ''
SET @message = ''
DECLARE @process_name NVARCHAR(100)
DECLARE @process_name NVARCHAR(100)
SET @process_name = 'usp_vo_get_destination_location'
SET @process_name = 'usp_vo_assign_user_trolley'
--Standard Pre Execution Setup Steps Here
--Standard Pre Execution Setup Steps Here
DECLARE @device_number NVARCHAR(30)
DECLARE @device_number NVARCHAR(30)
EXEC usp_vo_get_device_number @in_device_serial, @device_number OUTPUT
EXEC usp_vo_get_device_number @in_device_serial, @device_number OUTPUT
--End Standard Setup Steps
--End Standard Setup Steps
DECLARE @numtries INT, @jumpto NVARCHAR(50)
DECLARE @numtries INT, @jumpto NVARCHAR(50)
SELECT @numtries = next_value, @jumpto = 'START'
SELECT @numtries = next_value, @jumpto = 'START'
FROM t_control (NOLOCK)
FROM t_control (NOLOCK)
WHERE control_type = 'MAX_DEADLOCK_RETRY'
WHERE control_type = 'MAX_DEADLOCK_RETRY'
START:
START:
BEGIN TRY
BEGIN TRY
DECLARE @RC int,
DECLARE @RC INT,
@out_device_number NVARCHAR(10),
@out_device_number NVARCHAR(10),
@out_duplicate_flag BIT,
@out_duplicate_flag BIT,
@out_last_tran_datetime DATETIME,
@out_last_tran_datetime DATETIME,
@out_xml_response XML,
@out_xml_response XML,
@wh_id NVARCHAR (10),
@wh_id NVARCHAR (10),
@lane NVARCHAR (10)
@lane NVARCHAR (10)
EXECUTE @RC = usp_vo_core_pre_voice
EXECUTE @RC = usp_vo_core_pre_voice
@process_name
@process_name
,@in_device_serial
,@in_device_serial
,@in_msg_id
,@in_msg_id
,@out_device_number OUTPUT
,@out_device_number OUTPUT
,@out_duplicate_flag OUTPUT
,@out_duplicate_flag OUTPUT
,@out_last_tran_datetime OUTPUT
,@out_last_tran_datetime OUTPUT
,@out_xml_response OUTPUT
,@out_xml_response OUTPUT
--In Pallet Building, there will be a single destination put location associated with a carton.
UPDATE t_hu_master
SET @destination_location = ( SELECT TOP 1 pkd.planned_put_loc
SET user_id = @in_operator
FROM t_hu_master hum (NOLOCK)
WHERE hu_id = @in_trolley_hu_id
INNER JOIN t_pick_detail pkd (NOLOCK)
AND wh_id = @in_wh_id
ON pkd.container_id = hum.hu_id
AND pkd.wh_id = hum.wh_id
WHERE hum.hu_id = @in_hu_id
)
IF @destination_location IS NULL
BEGIN
--no put location for carton
SET @errorname = @NOPUTLOCCART
SET @errorcode = 2
GOTO ErrorHandler
END
--The destination location may be linked to multiple locations with a matching c1 field.
--Check if the destination location is in the same zone as the operator.
IF EXISTS( SELECT *
FROM t_zone_loca zl WITH (NOLOCK)
INNER JOIN t_location loc WITH (NOLOCK)
ON loc.location_id = zl.location_id
AND loc.wh_id = zl.wh_id
WHERE zl.zone= @in_zone
AND zl.wh_id = @in_wh_id
AND loc.c1 = @destination_location
)
BEGIN
--The prompting for check digits of a store will require the carton and operator be in
--the same zone.
SET @destination_check_digits = ( SELECT loc.check_digits
FROM t_zone_loca tzl (nolock)
inner join t_location loc (nolock)
ON loc.location_id = tzl.location_id
AND loc.wh_id = tzl.wh_id
WHERE tzl.zone = @in_zone
and loc.c1 = @destination_location
and tzl.wh_id = loc.wh_id
)
END
ELSE
--Destination location is in another zone. Check lane.
IF EXISTS ( SELECT *
FROM t_zone_loca zl WITH (NOLOCK)
INNER JOIN t_location loc WITH (NOLOCK)
ON loc.location_id = zl.location_id
AND loc.wh_id = zl.wh_id
WHERE lane = @in_lane
AND loc.c1 = @destination_location
AND zl.zone != 'ALL'
AND zl.wh_id = @in_wh_id
)
BEGIN
SET @errorname = @PASSTONEXTZONE
SET @errorcode = 3
GOTO ErrorHandler
END
ELSE
--ERROR: WRONG_SHIPPING_SPUR
SET @errorname = @PUTLOCNOTINLANE
SET @errorcode = 4
GOTO ErrorHandler
GOTO ExitLabel
GOTO ExitLabel
END TRY
END TRY
BEGIN CATCH
BEGIN CATCH
IF @@TRANCOUNT>0
IF @@TRANCOUNT>0
ROLLBACK TRAN
ROLLBACK TRAN
--Catch deadlock error and try again
--Catch deadlock error and try again
IF ERROR_NUMBER() = '1205' AND @numtries > 0
IF ERROR_NUMBER() = '1205' AND @numtries > 0
BEGIN
BEGIN
SET @numtries = @numtries - 1
SET @numtries = @numtries - 1
GOTO START
GOTO START
END
END
IF ERROR_NUMBER() = '1205'
IF ERROR_NUMBER() = '1205'
BEGIN
BEGIN
SET @errorname=@DEADLOCK
SET @errorname=@DEADLOCK
SET @errorcode = 1205
SET @errorcode = 1205
END
END
SET @errorname = ISNULL(@errorname,@SQLERROR)
SET @errorname = ISNULL(@errorname,@SQLERROR)
GOTO ErrorHandler
GOTO ErrorHandler
END CATCH
END CATCH
ErrorHandler:
ErrorHandler:
IF @errorcode = 0
IF @errorcode = 0
SET @errorcode = 1
SET @errorcode = 1
EXEC usp_vo_core_get_error_message_embedded 'ERROR',@errorname,'en_US',@message OUTPUT
EXEC usp_vo_core_get_error_message_embedded 'ERROR',@errorname,'en_US',@message OUTPUT
IF @@ERROR <> 0
IF @@ERROR <> 0
BEGIN
BEGIN
SET @message = 'unknown error'
SET @message = 'unknown error'
END
END
ExitLabel:
ExitLabel:
SELECT @current_xml_response = (
SELECT @current_xml_response = (
SELECT
SELECT
@destination_location as destination_location,
--When a destination location is found in a different zone (but the same lane),
--the user is prompted to pass the carton to that zone.
@destination_zone as destination_zone,
@destination_check_digits as destination_check_digits,
@errorcode as error_code,
@errorcode as error_code,
@message as error_msg
@message as error_msg
FOR XML RAW
FOR XML RAW
)
)
SELECT @destination_location, @destination_zone, @destination_check_digits, @errorcode, @message
SELECT @errorcode,@message
--Update Device Status
--Update Device Status
DECLARE @in_tran_date DATETIME,
DECLARE @in_tran_date datetime,
@in_break_flag NVARCHAR(10),
@in_break_flag nvarchar(10),
@in_wh_id_2 NVARCHAR(10),
@in_wh_id_2 nvarchar(10),
@in_vehicle_2 NVARCHAR(10)
@in_vehicle_2 nvarchar(10)
SELECT @in_tran_date = NULL,
SELECT @in_tran_date = NULL,
@in_break_flag = 0,
@in_break_flag = 0,
@in_wh_id_2 = NULL,
@in_wh_id_2 = NULL,
@in_vehicle_2 = NULL
@in_vehicle_2 = NULL
EXECUTE @RC = usp_vo_core_post_voice
EXECUTE @RC = usp_vo_core_post_voice
@process_name
@process_name
,@out_device_number
,@out_device_number
,@in_msg_id
,@in_msg_id
,@current_xml_response
,@current_xml_response
,@in_tran_date
,@in_tran_date
,@in_operator
,@in_operator
,@in_break_flag
,@in_break_flag
,@in_wh_id_2
,@in_wh_id_2
RETURN
RETURN
GO
GO