317 lines
12 KiB
PL/PgSQL
317 lines
12 KiB
PL/PgSQL
/* LAYER1_INBOUND_PILOT_V1 - SQL Server 2016 RTM compatible. */
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
IF DB_NAME() <> N'FT546-wms'
|
|
THROW 51100, 'Layer1 pilot installer requires FT546-wms', 1;
|
|
|
|
IF SCHEMA_ID(N'alg') IS NULL
|
|
OR OBJECT_ID(N'alg.shadow_snapshot_state', N'U') IS NULL
|
|
OR OBJECT_ID(N'alg.cargo_space_snapshot', N'U') IS NULL
|
|
OR OBJECT_ID(N'alg.decision_log', N'U') IS NULL
|
|
THROW 51101, 'Shadow v3 snapshot objects are required', 1;
|
|
|
|
IF OBJECT_ID(N'dbo.mydo_job', N'U') IS NULL
|
|
OR OBJECT_ID(N'dbo.mydo_job_detail', N'U') IS NULL
|
|
OR OBJECT_ID(N'dbo.bas_cargo_space', N'U') IS NULL
|
|
OR OBJECT_ID(N'dbo.bas_stock', N'U') IS NULL
|
|
OR OBJECT_ID(N'dbo.bas_led', N'U') IS NULL
|
|
THROW 51102, 'Required Legacy WMS objects are missing', 1;
|
|
|
|
IF OBJECT_ID(N'dbo.proc_allot_layer1_inbound_pilot', N'P') IS NULL
|
|
BEGIN
|
|
EXEC(N'CREATE PROCEDURE dbo.proc_allot_layer1_inbound_pilot AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
/* LAYER1_INBOUND_PILOT_V1 LAYER1_INBOUND_PILOT_STUB */
|
|
THROW 51103, ''Layer1 pilot installation did not complete'', 1;
|
|
END;');
|
|
END
|
|
ELSE IF CHARINDEX(N'LAYER1_INBOUND_PILOT_V1',
|
|
OBJECT_DEFINITION(OBJECT_ID(N'dbo.proc_allot_layer1_inbound_pilot'))) = 0
|
|
THROW 51104, 'Refusing to replace an unowned procedure', 1;
|
|
GO
|
|
|
|
/* LAYER1_INBOUND_PILOT_V1 */
|
|
ALTER PROCEDURE dbo.proc_allot_layer1_inbound_pilot
|
|
@v_task_no varchar(80),
|
|
@v_tray_code varchar(80),
|
|
@v_code varchar(80),
|
|
@v_space_id varchar(80),
|
|
@v_warehouse_id varchar(80),
|
|
@v_floor int,
|
|
@v_snapshot_generation bigint,
|
|
@v_snapshot_max_age_seconds int,
|
|
@v_result int OUTPUT,
|
|
@v_resultmsg varchar(255) OUTPUT,
|
|
@v_final_space_id varchar(80) OUTPUT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
SET @v_result = 90;
|
|
SET @v_resultmsg = 'PILOT_NOT_COMPLETED';
|
|
SET @v_final_space_id = NULL;
|
|
|
|
IF @v_warehouse_id <> '01' OR @v_floor <> 1
|
|
BEGIN
|
|
SET @v_result = 30;
|
|
SET @v_resultmsg = 'PILOT_SCOPE_REJECTED';
|
|
RETURN;
|
|
END;
|
|
IF @v_snapshot_generation <= 0 OR @v_snapshot_max_age_seconds NOT BETWEEN 5 AND 300
|
|
BEGIN
|
|
SET @v_result = 31;
|
|
SET @v_resultmsg = 'PILOT_CONFIGURATION_REJECTED';
|
|
RETURN;
|
|
END;
|
|
|
|
DECLARE @current_generation bigint;
|
|
DECLARE @snapshot_status varchar(20);
|
|
DECLARE @snapshot_time datetime2;
|
|
DECLARE @job_count int;
|
|
DECLARE @tray_job_count int;
|
|
DECLARE @job_id varchar(80);
|
|
DECLARE @existing_space_id varchar(80);
|
|
DECLARE @lane_code varchar(80);
|
|
DECLARE @actual_floor int;
|
|
DECLARE @depth int;
|
|
DECLARE @lane_row_count int;
|
|
DECLARE @start_time varchar(80) = CONVERT(varchar, GETDATE(), 120);
|
|
DECLARE @product_id nvarchar(max);
|
|
DECLARE @product_name nvarchar(max);
|
|
DECLARE @quantity nvarchar(max);
|
|
DECLARE @spec nvarchar(max);
|
|
|
|
BEGIN TRY
|
|
BEGIN TRANSACTION;
|
|
|
|
SELECT @current_generation = snapshot_generation,
|
|
@snapshot_status = snapshot_status,
|
|
@snapshot_time = synchronized_at
|
|
FROM alg.shadow_snapshot_state WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE warehouse_id = @v_warehouse_id;
|
|
|
|
IF @current_generation IS NULL
|
|
OR @current_generation <> @v_snapshot_generation
|
|
OR @snapshot_status <> 'PUBLISHED'
|
|
OR @snapshot_time IS NULL
|
|
OR DATEDIFF(SECOND, @snapshot_time, SYSUTCDATETIME()) NOT BETWEEN 0 AND @v_snapshot_max_age_seconds
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
SET @v_result = 21;
|
|
SET @v_resultmsg = 'SNAPSHOT_STALE_OR_CHANGED';
|
|
RETURN;
|
|
END;
|
|
|
|
SELECT @job_count = COUNT(*),
|
|
@job_id = MAX(job_id),
|
|
@existing_space_id = MAX(NULLIF(LTRIM(RTRIM(cargo_space_id)), ''))
|
|
FROM dbo.mydo_job WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE task_no = @v_task_no
|
|
AND tray_code = @v_tray_code
|
|
AND warehouse_id = @v_warehouse_id
|
|
AND TRY_CONVERT(int, in_or_out) < 20;
|
|
|
|
SELECT @tray_job_count = COUNT(*)
|
|
FROM dbo.mydo_job WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE tray_code = @v_tray_code
|
|
AND TRY_CONVERT(int, in_or_out) < 20;
|
|
|
|
IF @job_count <> 1 OR @tray_job_count <> 1
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
SET @v_result = 20;
|
|
SET @v_resultmsg = 'JOB_OR_TRAY_SCOPE_CHANGED';
|
|
RETURN;
|
|
END;
|
|
|
|
IF @existing_space_id IS NOT NULL
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
SET @v_final_space_id = @existing_space_id;
|
|
SET @v_result = CASE WHEN @existing_space_id = @v_space_id THEN 11 ELSE 12 END;
|
|
SET @v_resultmsg = 'JOB_ALREADY_ALLOCATED';
|
|
RETURN;
|
|
END;
|
|
|
|
SELECT @lane_code = group_coding_no,
|
|
@actual_floor = TRY_CONVERT(int, the_floor),
|
|
@depth = TRY_CONVERT(int, group_coding)
|
|
FROM dbo.bas_cargo_space WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE cargo_space_id = @v_space_id
|
|
AND warehouse_id = @v_warehouse_id
|
|
AND TRY_CONVERT(int, the_floor) = @v_floor
|
|
AND status = '1'
|
|
AND locked = 'N'
|
|
AND NULLIF(LTRIM(RTRIM(tray_code)), '') IS NULL
|
|
AND this_cargo_area IN ('A', 'C');
|
|
|
|
IF @lane_code IS NULL OR @depth IS NULL OR @actual_floor <> @v_floor
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
SET @v_result = 22;
|
|
SET @v_resultmsg = 'SPACE_CONFLICT';
|
|
RETURN;
|
|
END;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM alg.cargo_space_snapshot WITH (READCOMMITTEDLOCK)
|
|
WHERE warehouse_id = @v_warehouse_id
|
|
AND snapshot_generation = @v_snapshot_generation
|
|
AND space_id = @v_space_id
|
|
AND lane_code = @lane_code
|
|
AND TRY_CONVERT(int, floor_no) = @v_floor
|
|
AND space_status = '1'
|
|
AND locked = 'N'
|
|
AND NULLIF(LTRIM(RTRIM(tray_code)), '') IS NULL)
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
SET @v_result = 22;
|
|
SET @v_resultmsg = 'SPACE_NOT_IN_PLANNED_SNAPSHOT';
|
|
RETURN;
|
|
END;
|
|
|
|
/* Lock the complete live lane before checking continuity and obstruction. */
|
|
SELECT @lane_row_count = COUNT(*)
|
|
FROM dbo.bas_cargo_space WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE warehouse_id = @v_warehouse_id AND group_coding_no = @lane_code;
|
|
IF @lane_row_count = 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
SET @v_result = 23;
|
|
SET @v_resultmsg = 'LANE_NOT_FOUND';
|
|
RETURN;
|
|
END;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM dbo.bas_stock WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE cargo_space_id = @v_space_id)
|
|
OR EXISTS (
|
|
SELECT 1 FROM dbo.mydo_job WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE job_id <> @job_id
|
|
AND (cargo_space_id = @v_space_id OR target_cargo_space_id = @v_space_id))
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
SET @v_result = 22;
|
|
SET @v_resultmsg = 'SPACE_ALREADY_OWNED';
|
|
RETURN;
|
|
END;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM dbo.bas_cargo_space WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE warehouse_id = @v_warehouse_id
|
|
AND TRY_CONVERT(int, the_floor) = @v_floor
|
|
AND status = '3')
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM dbo.bas_cargo_space WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE warehouse_id = @v_warehouse_id
|
|
AND group_coding_no = @lane_code
|
|
AND (status IN ('3', '4') OR locked = 'Y'))
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM dbo.mydo_job outbound_job WITH (UPDLOCK, HOLDLOCK)
|
|
JOIN dbo.bas_cargo_space source_space WITH (UPDLOCK, HOLDLOCK)
|
|
ON source_space.cargo_space_id = outbound_job.cargo_space_id
|
|
WHERE source_space.warehouse_id = @v_warehouse_id
|
|
AND source_space.group_coding_no = @lane_code
|
|
AND TRY_CONVERT(int, outbound_job.in_or_out) >= 20)
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
SET @v_result = 23;
|
|
SET @v_resultmsg = 'LANE_OBSTRUCTED_OR_ACTIVE';
|
|
RETURN;
|
|
END;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM dbo.bas_cargo_space inner_space WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE inner_space.warehouse_id = @v_warehouse_id
|
|
AND inner_space.group_coding_no = @lane_code
|
|
AND TRY_CONVERT(int, inner_space.group_coding) < @depth
|
|
AND inner_space.status = '1'
|
|
AND inner_space.locked = 'N'
|
|
AND NULLIF(LTRIM(RTRIM(inner_space.tray_code)), '') IS NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM dbo.bas_stock inner_stock WITH (UPDLOCK, HOLDLOCK)
|
|
WHERE inner_stock.cargo_space_id = inner_space.cargo_space_id))
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
SET @v_result = 23;
|
|
SET @v_resultmsg = 'LANE_INNER_SPACE_STILL_FREE';
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.mydo_job
|
|
SET cargo_space_id = @v_space_id,
|
|
status = '3',
|
|
start_time = @start_time,
|
|
class_id = CONVERT(varchar(5), @actual_floor)
|
|
WHERE job_id = @job_id
|
|
AND task_no = @v_task_no
|
|
AND tray_code = @v_tray_code
|
|
AND warehouse_id = @v_warehouse_id
|
|
AND TRY_CONVERT(int, in_or_out) < 20
|
|
AND NULLIF(LTRIM(RTRIM(cargo_space_id)), '') IS NULL;
|
|
IF @@ROWCOUNT <> 1
|
|
THROW 51110, 'PILOT_JOB_UPDATE_CONFLICT', 1;
|
|
|
|
UPDATE dbo.bas_cargo_space
|
|
SET status = '3'
|
|
WHERE cargo_space_id = @v_space_id
|
|
AND warehouse_id = @v_warehouse_id
|
|
AND status = '1'
|
|
AND locked = 'N'
|
|
AND NULLIF(LTRIM(RTRIM(tray_code)), '') IS NULL;
|
|
IF @@ROWCOUNT <> 1
|
|
THROW 51111, 'PILOT_SPACE_UPDATE_CONFLICT', 1;
|
|
|
|
IF @v_code IN ('L1004', 'L1005')
|
|
BEGIN
|
|
;WITH detail_summary AS (
|
|
SELECT product_id,
|
|
CAST(SUM(in_quantity) AS varchar) AS sum_quantity,
|
|
spec,
|
|
product_name
|
|
FROM dbo.mydo_job_detail
|
|
WHERE job_id = @job_id
|
|
GROUP BY product_id, spec, product_name
|
|
)
|
|
SELECT @product_id = STUFF((SELECT ',' + product_id FROM detail_summary FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, ''),
|
|
@product_name = STUFF((SELECT ',' + product_name FROM detail_summary FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, ''),
|
|
@quantity = STUFF((SELECT ',' + sum_quantity FROM detail_summary FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, ''),
|
|
@spec = STUFF((SELECT ',' + spec FROM detail_summary FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '');
|
|
|
|
UPDATE dbo.bas_led
|
|
SET tray_code = @v_tray_code,
|
|
product_id = @product_id,
|
|
quantity = @quantity,
|
|
spec = @spec,
|
|
send_flag = 0,
|
|
model = 1,
|
|
product_name = @product_name
|
|
WHERE id = CASE WHEN @v_code = 'L1004' THEN 1 ELSE 2 END;
|
|
IF @@ROWCOUNT <> 1
|
|
THROW 51112, 'PILOT_LED_UPDATE_CONFLICT', 1;
|
|
END;
|
|
|
|
SET @v_final_space_id = @v_space_id;
|
|
SET @v_result = 10;
|
|
SET @v_resultmsg = 'SUCCESS';
|
|
COMMIT TRANSACTION;
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF XACT_STATE() <> 0
|
|
ROLLBACK TRANSACTION;
|
|
THROW;
|
|
END CATCH;
|
|
END;
|
|
GO
|
|
|
|
SELECT 'LAYER1_INBOUND_PILOT_INSTALL_OK' AS result;
|
|
GO
|