ALTER PROCEDURE [dbo].[Uspgeneratenextimcbsequence](@BCSequenceDetails TVPBCSEQUENCEDETAILS READONLY)
AS
  BEGIN
      SET NOCOUNT ON

      DECLARE @tblQueue TABLE (
        PrefixData   VARCHAR(18),
        NoOfBarcodes INT,
        LastSequence BIGINT,
        Processed    BIT )
      DECLARE @PrefixData VARCHAR(18)
      DECLARE @NoOfBarcodes INT
      DECLARE @LastSequence BIGINT
      DECLARE @RecordCount INT

      INSERT INTO @tblQueue
      SELECT seq.PrefixData,
             seq.NoOfBarcodes,
             0,
             0
      FROM   @BCSequenceDetails seq

      SELECT @RecordCount = Count(*)
      FROM   @tblQueue

      SELECT TOP 1 @PrefixData = q.PrefixData,
                   @NoOfBarcodes = q.NoOfBarcodes
      FROM   @tblQueue q
      WHERE  q.Processed = 0

      WHILE Len(Isnull(@PrefixData, '')) > 0
        BEGIN
            UPDATE BarCodeSequenceForIMcB
            SET    LastSequence = LastSequence + @NoOfBarcodes
            WHERE  PrefixData = @PrefixData

            IF @@ROWCOUNT = 0
              INSERT INTO BarCodeSequenceForIMcB
              SELECT @PrefixData,
                     @NoOfBarcodes

            UPDATE @tblQueue
            SET    Processed = 1
            WHERE  PrefixData = @PrefixData

            SET @PrefixData = ''

            SELECT TOP 1 @PrefixData = q.PrefixData,
                         @NoOfBarcodes = q.NoOfBarcodes
            FROM   @tblQueue q
            WHERE  q.Processed = 0
        END

      SELECT os.PrefixData,
             '',
             seq.NoOfBarcodes,
             os.LastSequence - seq.NoOfBarcodes
      FROM   BarCodeSequenceForIMcB os
             INNER JOIN @BCSequenceDetails seq
               ON seq.PrefixData = os.PrefixData

      SET NOCOUNT OFF
  END 

ALTER PROCEDURE [dbo].[Uspaddcontainersummaryrecordviabulkwithbarcode] @tblCSMDataTable TVPCSMTABLE readonly,
                                                                       @tblBarCodeData  TVPBARCODEDATA readonly,
                                                                       @ErrorVar        INT OUTPUT
AS
  BEGIN
      DECLARE @SequenceStart BIGINT
      DECLARE @MailerId VARCHAR(9)
      DECLARE @ContainerCount INT
      DECLARE @BarCodeType SMALLINT/*0 for None, 1 for IMcB and 2 for IMtB*/
      DECLARE @MailClass CHAR(1)
      DECLARE @ProcessingCategory VARCHAR(2)
      DECLARE @ProcessingCode CHAR(1)
      DECLARE @tblIMtBBarCodeData TVPIMTBBARCODEDATA
      DECLARE @BCSequenceDetails TVPBCSEQUENCEDETAILS
      DECLARE @BCSequenceDetailsOUT TVPBCSEQUENCEDETAILS

      SELECT @MailerId = MailerId
      FROM   @tblBarCodeData

      SELECT TOP 1 @BarCodeType = BarCodeType
      FROM   @tblCSMDataTable

      --MailerID is not null AND bar code is any from IMcB or IMtb
      IF( Len(@MailerId) > 0
          AND @BarCodeType <> 0 )
        BEGIN
            SELECT @ContainerCount = Count(csmContainerID)
            FROM   @tblCSMDataTable

            IF @BarCodeType = 1
              BEGIN
                  INSERT INTO @BCSequenceDetails
                  SELECT '99M' + @MailerId,
                         '',
                         @ContainerCount,
                         0

                  INSERT INTO @BCSequenceDetailsOUT
                  EXEC dbo.Uspgeneratenextimcbsequence @BCSequenceDetails
              END
            ELSE
              IF @BarCodeType = 2
                BEGIN
                    SELECT @MailClass = MailClass,
                           @ProcessingCategory = ProcessingCategory,
                           @ProcessingCode = ProcessingCode
                    FROM   @tblBarCodeData

                    INSERT INTO @tblIMtBBarCodeData
                    SELECT csmContainerDestinationZip,
                           cqtRateCategory,
                           cqtDestinationEntry,
                           ContainerTypeCode,
                           Count(*),
                           NULL
                    FROM   @tblCSMDataTable
                    GROUP  BY csmContainerDestinationZip,
                              cqtRateCategory,
                              cqtDestinationEntry,
                              ContainerTypeCode
                    ORDER  BY csmContainerDestinationZip,
                              cqtRateCategory,
                              cqtDestinationEntry,
                              ContainerTypeCode

                    INSERT INTO @BCSequenceDetails
                    SELECT PrefixData,
                           SuffixData,
                           NoOfBarcodes = Sum(NoOfBarcodes),
                           0
                    FROM   dbo.Getimtbsequencedetails(@tblIMtBBarCodeData, @BarCodeType, @MailerId, @MailClass, @ProcessingCategory, @ProcessingCode)
                    GROUP  BY PrefixData,
                              SuffixData

                    INSERT INTO @BCSequenceDetailsOUT
                    EXEC dbo.Uspgeneratenextimtbsequence @BCSequenceDetails
                END
        END

      INSERT INTO ContainerSummaryRecord
                  (fkJobID,
                   fkSegmentID,
                   csmContainerID,
                   csmContainerType,
                   csmDisplayContainerID,
                   csmContainerDestinationZip,
                   csmContainerLevel,                   
                   csmLabelIMContainerOrIMTrayBarcode
                   )
      SELECT      
      fkJobID,
      fkSegmentID,
      csmContainerID,
      csmContainerType,
      csmDisplayContainerID,
      csmContainerDestinationZip,
      csmContainerLevel,    
      csmLabelIMContainerOrIMTrayBarcode = CASE
                                             WHEN @BarcodeType = 1 THEN Isnull(IMcBs.Barcode, csmLabelIMContainerOrIMTrayBarcode)
                                             WHEN @BarcodeType = 2 THEN Isnull(IMtBs.Barcode, csmLabelIMContainerOrIMTrayBarcode)
                                             ELSE csmLabelIMContainerOrIMTrayBarcode
                                           END
  
      FROM   @tblCSMDataTable CSM
             LEFT JOIN (SELECT *
                        FROM   dbo.Getnewimcbsinbulk(@BCSequenceDetailsOUT, @BarCodeType)
                        WHERE  @ContainerCount > 0
                               AND @BarCodeType = 1)IMcBs
               ON IMcBs.RowNumber = CSM.RecordNumber
             LEFT JOIN (SELECT *
                        FROM   dbo.Getnewimtbsinbulk(@BCSequenceDetailsOUT, @BarCodeType)
                        WHERE  @ContainerCount > 0
                               AND @BarCodeType = 2)IMtBs
               ON IMtBs.RowNumber = CSM.RecordNumber

      IF @@ERROR <> 0
        SET @ErrorVar = -1
  END 

  
  

ALTER PROCEDURE [dbo].[Uspgeneratenextimcbsequence](@BCSequenceDetails TVPBCSEQUENCEDETAILS READONLY) AS
 BEGIN
 SET NOCOUNT ON;

 MERGE INTO BarCodeSequenceForIMcB AS source
 USING @BCSequenceDetails AS target ON source.PrefixData=target.PrefixData
 WHEN matched THEN UPDATE SET LastSequence = LastSequence + target.NoOfBarcodes
 WHEN NOT matched THEN INSERT(PrefixData,NoOfBarcodes) VALUES(PrefixData,NoOfBarcodes);
 
 SELECT os.PrefixData, '', seq.NoOfBarcodes, os.LastSequence - seq.NoOfBarcodes
 FROM BarCodeSequenceForIMcB os INNER JOIN @BCSequenceDetails seq ON seq.PrefixData = os.PrefixData;
 END