Dyncraft_Barcode

As many of you are aware or are noticing, each sprint the scrum masters are planning a variety or projects/programs.  As we presented a few weeks ago at our all-hands, we are doing our best to adjust but yet we have additional work coming at us from ITD Security team.  Adjustments have been made and the Scrum masters are aware - please do what we can to get these requests groomed and worked as quickly as possible.  Going forward, please only work on IR's that are P1 or P2, for the application team.  All others will be planned during sprint planning.  For Operations continue to focus as a first in first out process (FIFO) by priority (meaning P3's should be handled first).  At this time, SR's will remain with a 10 day SLA.  The management team continues to monitor the SR efforts so we can adjust based on customer asks.  Overall this means that you need to focus on your sprint items, so we can support the business requests (SR/IR's) later in the sprints.

Text fields on the label: Date of programming, Truck Plant, Chassis #, Sequence number, Firmware Version, test result

Label should be triggered by DAVIE flashing process.

Label dimensions must fit within X by X.

DMB can transmit Chassis information.
Requirement on the drawing to have a removable label.

Notes:

Truck plant is the second character in the chassis number prefix.  DAVIE has the two-character prefix in the chassis.

Spike needed with Joonbo- Options for triggering a label from DAVIE

Dependency - DAVIE backoffice needed before this story can be worked.

There will only be one printer and one flash station.

Order Type: ZCAS

 

USE [EWI_Admin]

GO

/****** Object:  StoredProcedure [dbo].[DynaSequencerFuelTank_PrintLabelK1]    Script Date: 7/25/2024 4:32:46 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Brad Schmidt

-- Create date: 11/15/2019

-- Modified: 2.3.20 added import and print date cols, update print col

-- 2.5.20 move all changes from the testing script,  

-- 10.28.20 split fuel side right was showing hydraulic for engraver field

-- -- IR749285 - Printer machine is changed from DYNMCKZ015 to DYNMCKZ017

--

-- Description: Prints single fuel tank label from EWI sequencer screen

-- NOTE: for split tanks if "A" postion is 1, Hydraulic tanks is 1st(left) and Fuel tanks is 2nd(right)

--   if "A" position is 2, its reversed, fuel 1st/left and hydro 2nd/right

-- =============================================

ALTER PROCEDURE [dbo].[DynaSequencerFuelTank_PrintLabelK1]

@id INT

AS

BEGIN

    SET NOCOUNT ON;

DECLARE @result BIT,

@Salvagnini VARCHAR(20),

@SalvagniniL2 VARCHAR(20),

@Seam VARCHAR(20),

@SeamL2 VARCHAR(20),

@LongSeam INT,

@Raw VARCHAR(20),

@RawL2 VARCHAR(20),

@Shell VARCHAR(20),

@ShellL2 VARCHAR(20),

@Assembly VARCHAR(25),

@Chassis VARCHAR(10),

@Sequence VARCHAR(10),

@Motoman VARCHAR(35),

@MotomanL2 VARCHAR(35),

@Engraver VARCHAR(40),

@EngraverL2 VARCHAR(40),

@TankType CHAR(1),

@Title VARCHAR(40),

@TitleL2 VARCHAR(40),

@EXP VARCHAR(200)

--@K2Fitting VARCHAR(40);

--SELECT @Salvagnini = seq.PartNumber

-- ,@SalvagniniL2 = seq.PartNumber

SELECT @Salvagnini = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(seq.PartNumber, '_H')

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN seq.PartNumber

ELSE 'NA'

END

,@SalvagniniL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(seq.PartNumber, '_H')

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN seq.PartNumber

ELSE 'NA'

END

,@Seam = CASE ---check with tim if keep it the same...

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN CONCAT(kf.TankDiameter, '/L', kf.TankLength) 

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(ksh.TankDiameter, '/L', ksh.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(ksf.TankDiameter, '/L', ksf.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN CONCAT(kh.TankDiameter, '/L', kh.TankLength)

ELSE 'NA'

END

,@LongSeam = CASE ---check with tim if keep it the same...

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN     

--CONCAT(kf.TankDiameter, '/L', kf.TankLength) 

(SELECT [LSWid] FROM [EWI_Admin].[dbo].[FuelTankLongSeam]  WHERE  [Diameter]=kf.TankDiameter AND [Length]=kf.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN 

--CONCAT(ksh.TankDiameter, '/L', ksh.TankLength)

(SELECT [LSWid] FROM [EWI_Admin].[dbo].[FuelTankLongSeam]  WHERE  [Diameter]=ksh.TankDiameter AND [Length]=ksh.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN 

--CONCAT(ksf.TankDiameter, '/L', ksf.TankLength)

(SELECT [LSWid] FROM [EWI_Admin].[dbo].[FuelTankLongSeam]  WHERE  [Diameter]=ksf.TankDiameter AND [Length]=ksf.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN 

--CONCAT(kh.TankDiameter, '/L', kh.TankLength)

(SELECT [LSWid] FROM [EWI_Admin].[dbo].[FuelTankLongSeam]  WHERE  [Diameter]=kh.TankDiameter AND [Length]=kh.TankLength)

ELSE 'NA'

END

,@SeamL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN NULL

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(ksf.TankDiameter, '/L', ksf.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(ksh.TankDiameter, '/L', ksh.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN NULL

ELSE 'NA'

END

,@Raw = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN kf.RawFamily 

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN ksh.RawFamily

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN ksf.RawFamily

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN kh.RawFamily

ELSE 'NA'

END

,@RawL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN NULL

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN ksf.RawFamily

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN ksh.RawFamily

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN NULL

ELSE 'NA'

END

,@Shell = 'NA'

,@Assembly = seq.PartNumber

,@Sequence = seq.SequenceNumber

,@Chassis = seq.ChassisNumber

--,@Motoman = seq.PartNumber ---confirm just want assembly instead of: CONCAT(RIGHT('   ' + CAST(ld.TankCapacity AS VARCHAR(3)), 3), '.F.', seq.PartNumber)

,@Motoman = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN 'N/A'

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN 'N/A'

ELSE 'NA'

END

,@MotomanL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN 'N/A'

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN 'N/A'

ELSE 'NA'

END

,@Engraver = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN CONCAT(LEFT(CAST(kf.TankCapacity AS VARCHAR(3)) + '   ', 3), '.F .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(ksh.TankCapacity, '.H .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(LEFT(CAST(ksf.TankCapacity AS VARCHAR(3)) + '   ', 3), '.F .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN CONCAT(kh.TankCapacity, '.H .',seq.PartNumber)

ELSE 'NA'

END

,@EngraverL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN NULL

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(LEFT(CAST(ksf.TankCapacity AS VARCHAR(3)) + '   ', 3), '.F .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(ksh.TankCapacity, '.H .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN NULL

ELSE 'NA'

END

,@TankType = SUBSTRING(seq.PartNumber,4,1)

,@Title = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN 'FUEL'

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN 'SPLIT-HYDR(Left)'

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN 'SPLIT-FUEL(Left)'

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN 'HYDRAULIC'

ELSE 'NA'

END

,@TitleL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN NULL

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN 'SPLIT-FUEL(Right)'

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN 'SPLIT-HYDR(Right)'

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN NULL

ELSE 'NA'

END

--EXP barcode for chassis, sequence, K1 part, and K2 fitting

,@EXP = CONCAT(seq.ChassisNumber,'.', seq.SequenceNumber,'.', seq.PartNumber,'.',ftpm.K2Part)

--,@K2Fitting = etaf.FittingPN

  

--,ksh.*

--,ksf.*

--,seq.*

  FROM [EWI_Admin].[dbo].DynaSequencerFuelTank seq

  LEFT JOIN [EWI_Admin].[dbo].DynaK1FuelTankRef kf

ON SUBSTRING(seq.PartNumber,4,1) = '0'

   AND SUBSTRING(seq.PartNumber,5,1) = kf.Map

   AND kf.[Option] = 'B'

  LEFT JOIN [EWI_Admin].[dbo].DynaK1FuelTankRef ksh

ON SUBSTRING(seq.PartNumber,4,1) IN('1','2')

   AND SUBSTRING(seq.PartNumber,12,1) = ksh.Map

   AND ksh.[Option] = 'J'

  LEFT JOIN [EWI_Admin].[dbo].DynaK1FuelTankRef ksf

ON SUBSTRING(seq.PartNumber,4,1) IN('1','2')

   AND SUBSTRING(seq.PartNumber,5,1) = ksf.Map

   AND ksf.[Option] = 'B'

  LEFT JOIN [EWI_Admin].[dbo].DynaK1FuelTankRef kh

ON SUBSTRING(seq.PartNumber,4,1) = '3'

   AND SUBSTRING(seq.PartNumber,5,1) = kh.Map

   AND kh.[Option] = 'B'

   --Tanks and fittings join for K2 part number

   LEFT JOIN EWI_DataWarehouse.dbo.EXP_FuelTankPlumbingMatch AS ftpm

ON ftpm.K1Part = seq.PartNumber AND ftpm.MatchedLineSeqNbr = seq.SequenceNumber

WHERE seq.DynaSequencerFuelTankID = @id

IF (@Sequence IS NOT NULL)

BEGIN

--SELECT @Salvagnini Salvagnini,

-- @Seam Seam,

-- @Raw Raw,

-- @Shell Shell,

-- @Assembly [Assembly],

-- @Sequence [Sequence],

-- @Chassis Chassis,

-- @Motoman Motoman,

-- @Engraver Engraver,

-- @TankType TankType,

-- @Title Title,

-- @EXP [EXP]

DECLARE @FileName VARCHAR(MAX),

@FileText VARCHAR(MAX)

SET @FileName = CONCAT('\\DYNMCKMVPP1801\Bartender\Fuel_Tank\', @Chassis,'-',@Sequence,'-',@Assembly,'.ztt')

-- IR749285 - Printer machine is changed from DYNMCKZ015 to DYNMCKZ017

SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks_DEV.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ017" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z017 is fuel by Mo

--SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ015" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z015 is fuel by Mo

--SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ023" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z023 is fab, far corner

@Salvagnini,'|',@Seam,'|',@LongSeam,'|',@Raw,'|',@Shell,'|',@Assembly,'|',@Sequence,'|',@Chassis,'|',@Motoman,'|',@Engraver,'|',@Title,'|',@EXP)

--SELECT @filename [filename], @filetext filetext

------------------------------------------------

EXEC dbo.WriteToFile @FileName, @FileText;

------------------------------------------------

IF @TankType IN('1','2')

BEGIN

--SELECT @SalvagniniL2 SalvagniniL2,

-- @SeamL2 SeamL2,

-- @RawL2 RawL2,

-- @ShellL2 ShellL2,

-- @Assembly [Assembly],

-- @Sequence [Sequence],

-- @Chassis Chassis,

-- @MotomanL2 MotomanL2,

-- @EngraverL2 EngraverL2,

-- @TankType TankType,

-- @TitleL2 TitleL2,

-- @EXP [EXP]

SET @FileName = CONCAT('\\DYNMCKMVPP1801\Bartender\Fuel_Tank\', @Chassis,'-',@Sequence,'-',@Assembly,'-L2.ztt')

SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks_DEV.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ017" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z017 is by brandon, 15 is the one by fuel...

--SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ015" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z022 is by brandon, 15 is the one by fuel...

--SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ023" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z023 is fab, far corner

@SalvagniniL2,'|',@SeamL2,'|','|',@LongSeam,'|',@RawL2,'|',@ShellL2,'|',@Assembly,'|',@Sequence,'|',@Chassis,'|',@MotomanL2,'|',@EngraverL2,'|',@TitleL2,'|',@EXP)

--SELECT @filename [filename], @filetext filetext

------------------------------------------------

EXEC dbo.WriteToFile @FileName, @FileText;

------------------------------------------------

END 

        UPDATE dbo.DynaSequencerFuelTank

        SET IsLabelPrinted = 1,

PrintDate = GETDATE()

        WHERE DynaSequencerFuelTankID = @id;

        SET @result = 1;

    END;

    ELSE

    BEGIN

        SET @result = 0;

    END;

    --SELECT @result;

    RETURN @result;

END;