USE [EWI_DataWarehouse]
GO
ALTER PROCEDURE [dbo].[SP_EXP_FuelTankPlumbingMatch_KTanks_SELECT]
AS
BEGIN
SET NOCOUNT ON;
DELETE EXP_FuelTankPlumbingMatch_KTanks --CLEANUP
-- Declare variable for TankTypeId
DECLARE @TankTypeId UNIQUEIDENTIFIER;
-- Cursor to loop through active TankTypeIds
DECLARE tank_cursor CURSOR FOR
SELECT TankTypeId
FROM EXP_FuelTankType
WHERE IsActive = 1;
OPEN tank_cursor;
FETCH NEXT FROM tank_cursor INTO @TankTypeId;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Processing TankTypeId: ' + CAST(@TankTypeId AS NVARCHAR(36));
-- Declare variables for the columns of EXP_FuelTankPlumbingMatchProperties
DECLARE
@Id UNIQUEIDENTIFIER,
@Prefix NVARCHAR(50),
@Identifier NVARCHAR(50),
@IdentifierPosition INT,
@PartNumberCharacter NVARCHAR(50),
@Value NVARCHAR(255),
@TankTypeIdProp UNIQUEIDENTIFIER,
@IsActive BIT;
-- Cursor for matching properties
DECLARE properties_cursor CURSOR FOR
SELECT
Id,
Prefix,
Identifier,
IdentifierPosition,
PartNumberCharacter,
Value,
TankTypeId,
IsActive
FROM EXP_FuelTankPlumbingMatchProperties
WHERE TankTypeId = @TankTypeId;
OPEN properties_cursor;
FETCH NEXT FROM properties_cursor INTO
@Id,
@Prefix,
@Identifier,
@IdentifierPosition,
@PartNumberCharacter,
@Value,
@TankTypeIdProp,
@IsActive;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Match found for TankTypeId: ' + CAST(@TankTypeId AS NVARCHAR(36)) +
', Id: ' + CAST(@Id AS NVARCHAR(36)) +
', Prefix: ' + ISNULL(@Prefix, '') +
', Identifier: ' + ISNULL(@Identifier, '') +
', IdentifierPosition: ' + CAST(@IdentifierPosition AS NVARCHAR(10)) +
', PartNumberCharacter: ' + ISNULL(@PartNumberCharacter, '') +
', Value: ' + ISNULL(@Value, '') +
', IsActive: ' + CAST(@IsActive AS NVARCHAR(1));
-- Declare variables for BOM loop
DECLARE @Item VARCHAR(255);
DECLARE @Description VARCHAR(255);
DECLARE item_cursor CURSOR FOR
SELECT TOP 10 -- TEST PURPOSE (Later remove TOP 10)
B.Item,
B.[Description]
FROM [EWI_DataWarehouse].dbo.BOMDTL B
INNER JOIN [EWI_DataWarehouse].dbo.OpenJITs O
ON B.Item = O.ItemId
WHERE B.Item LIKE @Prefix;
OPEN item_cursor;
FETCH NEXT FROM item_cursor INTO @Item, @Description;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Item: ' + @Item + ', Description: ' + @Description;
-- Extract IdentifierValue (example snippet, not used further)
DECLARE @IdentifierValue AS NVARCHAR(1)=
( SELECT
CASE
WHEN @Item IS NULL OR LEN(@Item) < 1
THEN '-'
ELSE SUBSTRING(@Item, @IdentifierPosition, 1)
END AS IdentifierValue
)
-- Get TankType string for current TankTypeId
DECLARE @TankType NVARCHAR(MAX) = (
SELECT TOP 1 TankType
FROM EXP_FuelTankType
WHERE TankTypeId = @TankTypeIdProp
);
PRINT 'TankType:' + ISNULL(@TankType, '') + ' Item:' + @Item + ' Value:' + ISNULL(@Value, '');
-- Declare order-related variables
DECLARE
@ChassisNbr NVARCHAR(36),
@LineSeqNbr NVARCHAR(50),
@QtyOrdered INT,
@DateShipReq DATETIME,
@OrderStatus INT;
SELECT
@ChassisNbr = O.ChassisNbr,
@LineSeqNbr = O.LineSeqNbr,
@QtyOrdered = O.QtyOrdered,
@DateShipReq = O.DateShipReq,
@OrderStatus = O.OrderStatus
FROM [EWI_DataWarehouse].dbo.BOMDTL B
INNER JOIN [EWI_DataWarehouse].dbo.OpenJITs O
ON O.ItemID = B.Item
WHERE B.Item = @Item;
SELECT
[Value]
FROM EXP_FuelTankPlumbingMatchProperties
WHERE TankTypeId=@TankTypeId
AND
Identifier= @IdentifierValue
-- Insert into target table
INSERT INTO EXP_FuelTankPlumbingMatch_KTanks
(
ChassisNbr,
MatchedLineSeqNbr,
QtyOrdered,
TankTypeId,
Side,
Part,
DateShipReq,
IsMatch,
OrderStatus
)
VALUES
(
@ChassisNbr,
@LineSeqNbr,
@QtyOrdered,
@TankTypeId,
@IdentifierValue,
@Item,
@DateShipReq,
1,
@OrderStatus
);
FETCH NEXT FROM item_cursor INTO @Item, @Description;
END
CLOSE item_cursor;
DEALLOCATE item_cursor;
FETCH NEXT FROM properties_cursor INTO
@Id,
@Prefix,
@Identifier,
@IdentifierPosition,
@PartNumberCharacter,
@Value,
@TankTypeIdProp,
@IsActive;
END
CLOSE properties_cursor;
DEALLOCATE properties_cursor;
FETCH NEXT FROM tank_cursor INTO @TankTypeId;
END
CLOSE tank_cursor;
DEALLOCATE tank_cursor;
-- Final SELECT statement to return results
SELECT
FTPM.ChassisNbr AS [CHASSIS],
FTPM.MatchedLineSeqNbr AS [SEQUENCE],
FTPM.Part AS [PART_NUMBER],
TT.TankType AS [TANK_TYPE], --K3
FTPM.Side AS [SIDE], --LEFT/RIGHT
FTPM.DateShipReq AS [DATE_SHIP_REQUIRED],
FTPM.IsMatch AS [IS_MATCH],
FTPM.QtyOrdered AS [QUANTITY],
FTPM.OrderStatus AS [ORDER_STATUS]
FROM EXP_FuelTankPlumbingMatch_KTanks FTPM
INNER JOIN EXP_FuelTankType TT
ON TT.TankTypeId = FTPM.TankTypeId;
END
