Mohammad Chowdhury: Dyn

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

Below is an explanation of the stored procedure [dbo].[SP_EXP_FuelTankPlumbingMatch_KTanks_SELECT]. This procedure is designed to process and match fuel tank plumbing parts against a set of criteria and insert matching records into a target table, then return the results.

Overview

Database: EWI_DataWarehouse
Procedure Purpose:
To process active tank types, match their plumbing properties against bill of materials (BOM) and open Just-In-Time (JIT) orders, insert matching results into a staging table, and finally return those matched results.

Step-by-Step Explanation

1. Preliminary Setup

SQL
SET NOCOUNT ON;
DELETE EXP_FuelTankPlumbingMatch_KTanks

SET NOCOUNT ON disables the message that shows the number of rows affected by SQL statements to reduce network traffic.
The target table EXP_FuelTankPlumbingMatch_KTanks is cleared at the start to ensure fresh data for this run.

2. Cursor to Iterate Over Active Tank Types

SQL
DECLARE @TankTypeId UNIQUEIDENTIFIER;

DECLARE tank_cursor CURSOR FOR
SELECT TankTypeId 
FROM EXP_FuelTankType 
WHERE IsActive = 1;

The procedure retrieves all active tank types (IsActive = 1) from the EXP_FuelTankType table.
A cursor tank_cursor is declared to loop through each active TankTypeId.

3. For Each TankTypeId: Process Matching Properties

Inside the tank cursor loop:

SQL
DECLARE 
    @Id UNIQUEIDENTIFIER,
    @Prefix NVARCHAR(50), 
    @Identifier NVARCHAR(50),
    @IdentifierPosition INT,
    @PartNumberCharacter NVARCHAR(50),
    @Value NVARCHAR(255),
    @TankTypeIdProp UNIQUEIDENTIFIER,
    @IsActive BIT;

DECLARE properties_cursor CURSOR FOR
SELECT 
    Id,
    Prefix,
    Identifier,
    IdentifierPosition,
    PartNumberCharacter,
    Value,
    TankTypeId,
    IsActive
FROM EXP_FuelTankPlumbingMatchProperties
WHERE TankTypeId = @TankTypeId;

For the current TankTypeId, it retrieves all plumbing match properties from EXP_FuelTankPlumbingMatchProperties.
These properties include patterns and identifiers used to match items in the BOM.

4. For Each Matching Property: Find Matching BOM Items

Inside the properties cursor loop:

SQL
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;

For each property, the procedure searches the BOMDTL table joined with OpenJITs for items where the Item matches the Prefix pattern from the property.
The TOP 10 is a temporary limit for testing and should be removed later to process all matches.

5. For Each Matching Item: Extract Identifier and Get Order Details

Inside the item cursor loop:

SQL
DECLARE @IdentifierValue AS NVARCHAR(1) =
    CASE 
        WHEN @Item IS NULL OR LEN(@Item) < 1 THEN '-'
        ELSE SUBSTRING(@Item, @IdentifierPosition, 1)
    END;

Extracts a single character (IdentifierValue) from the Item string at the position specified by IdentifierPosition from the property.
If @Item is null or too short, default IdentifierValue to '-'.

SQL
DECLARE @TankType NVARCHAR(MAX) = (
    SELECT TOP 1 TankType 
    FROM EXP_FuelTankType 
    WHERE TankTypeId = @TankTypeIdProp
);

Retrieves the string name of the tank type for display/debugging.

SQL
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;

Retrieves order-related data for the current item from OpenJITs (orders) joined with BOMDTL.

6. Insert Matching Record into Target Table

SQL
INSERT INTO EXP_FuelTankPlumbingMatch_KTanks
(
    ChassisNbr,
    MatchedLineSeqNbr,
    QtyOrdered,
    TankTypeId,
    Side,
    Part,
    DateShipReq,
    IsMatch,
    OrderStatus
)
VALUES
(
    @ChassisNbr,
    @LineSeqNbr,
    @QtyOrdered,
    @TankTypeId,
    @IdentifierValue,
    @Item,
    @DateShipReq,
    1,
    @OrderStatus
);

Inserts a record indicating a match between BOM item and tank plumbing property, including order and item details.
The Side column stores the extracted IdentifierValue (likely indicating left/right side or some positional attribute).
IsMatch is set to 1 to indicate a successful match.

7. Cleanup Cursors

After processing all items for the current property, the item cursor is closed and deallocated.
After processing all properties for the current tank type, the properties cursor is closed and deallocated.
After processing all tank types, the tank cursor is closed and deallocated.

8. Final Output

SQL
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;

Returns the accumulated matching records joined with tank type details for reporting or further processing.

Summary

This stored procedure:

Iterates over all active tank types.
For each tank type, retrieves plumbing match properties.
For each property, searches BOM items that match the prefix.
For each matching BOM item, extracts a character from the item code and retrieves order info.
Inserts match results into a staging table.
Finally returns all matches with tank type details.

The use of multiple nested cursors allows detailed, row-by-row processing and matching logic which might be complex to express in set-based SQL alone.

Mohammad Chowdhury

Saturday, July 26, 2025

Dyn - SQL