SP

 

ALTER PROCEDURE [dbo].[PartNumberValidation_SELECT]
  @PartNumber AS VARCHAR(80),
  @PO AS         VARCHAR(80) = NULL,
  @Badge         INT = NULL,
  @OrderType AS  VARCHAR(50) = NULL,
  @Quantity      INT = NULL,
  @Result        BIT output
AS
  BEGIN

    SET nocount ON;

    IF @OrderType IS NULL
     BEGIN

      IF EXISTS ( 1 (/* condition */))

      BEGIN
        SET @Result = 1;
      END
      else
      BEGIN
        SET @Result = 0;
      END
     END

    ELSE
    BEGIN
      IF EXISTS ( 1 )
      BEGIN
        SET @Result = 1;
      END
      else
      BEGIN
        -- Check the second condition
        IF EXISTS ( 1 )
        BEGIN
          SET @Result = 1;
        END
        else
        BEGIN
          SET @Result = 0;
        END
      END
    END
  END


Debug

USE [TeamViewer_DYN];

 

DECLARE @PO NVARCHAR(50) = '000044444447';

 

DECLARE 
    @Material NVARCHAR(50),
    @QuantityCOHV INT,
    @CartType NVARCHAR(50),
    @CartID INT,
    @CartName NVARCHAR(50),
    @CartTypeVal NVARCHAR(50),
    @KItem NVARCHAR(50),
    @ProfitCenter NVARCHAR(50);

 

-- Step 1: Get COHV Order details
SELECT 
    @Material = ItemID,
    @QuantityCOHV = QTY,
    @ProfitCenter = ProfitCenter
FROM EWI_DataWarehouse.dbo.COHV
WHERE PO = @PO;

 

PRINT 'Step 1: Material=' + ISNULL(@Material,'NULL') + 
      ', QuantityCOHV=' + ISNULL(CAST(@QuantityCOHV AS NVARCHAR),'NULL') +
      ', ProfitCenter=' + ISNULL(@ProfitCenter,'NULL');

 

-- Step 2: Get KItem from BOMDTL
SET @KItem = (
    SELECT TOP 1 B.Component
    FROM EWI_DataWarehouse.dbo.BOMDTL B
    WHERE B.Item = @Material AND B.Component LIKE '%-K'
);

 

PRINT 'Step 2: KItem=' + ISNULL(@KItem,'NULL');

 

-- Step 3: Determine CartType
SET @CartType = CASE 
    WHEN @Material LIKE 'N10%' THEN 'Box'
    WHEN @Material LIKE 'N22%' THEN 'Cover'
    ELSE NULL
END;

 

PRINT 'Step 3: CartType=' + ISNULL(@CartType,'NULL');

 

-- Step 4: Get BOM Details into temp table
SELECT DISTINCT
    @Material AS Material,
    @QuantityCOHV AS COHVQty,
    B.Component,
    B.Qty,
    P.Bin AS PickBin
INTO #KitComponents
FROM EWI_DataWarehouse.dbo.BOMDTL B
LEFT JOIN TeamViewer_DYN.dbo.PickBins P
    ON B.Component = P.Material
WHERE B.Item = @KItem;

 

PRINT 'Step 4: Inserted into #KitComponents';
SELECT * FROM #KitComponents; -- Debug view

 

-- Step 5: Get the next available CartID and CartType
SELECT TOP 1
    @CartID = CartID,
    @CartName = CartName,
    @CartTypeVal = CartType
FROM TeamViewer_DYN.dbo.Carts
WHERE inUse = 0 AND CartType = @CartType
ORDER BY CartID;

 

PRINT 'Step 5: CartID=' + ISNULL(CAST(@CartID AS NVARCHAR),'NULL') +
      ', CartName=' + ISNULL(@CartName,'NULL') +
      ', CartTypeVal=' + ISNULL(@CartTypeVal,'NULL');

 

IF @CartID IS NULL
BEGIN
    PRINT 'Step 5: NO CARTS AVAILABLE';
    DROP TABLE #KitComponents;
    RETURN;
END

 

-- Step 6: Insert into ScanOrderHistory
DECLARE @Suffixes TABLE (Suffix CHAR(1));

 

IF @QuantityCOHV = 1
    INSERT INTO @Suffixes VALUES ('A');
ELSE IF @QuantityCOHV = 2
    INSERT INTO @Suffixes VALUES ('A'), ('B');
ELSE IF @QuantityCOHV = 3
    INSERT INTO @Suffixes VALUES ('A'), ('B'), ('C');
ELSE IF @QuantityCOHV = 4
    INSERT INTO @Suffixes VALUES ('A'), ('B'), ('C'), ('D');
ELSE
    INSERT INTO @Suffixes VALUES ('A');

 

PRINT 'Step 6: Suffixes added';
SELECT * FROM @Suffixes;

 

INSERT INTO [TeamViewer_DYN].[dbo].[ScanOrderHistory] (
    PO, Material, COHVQty, Component, BOMQty, CartID, CartType, CartName, CartBin, PickBin, ProfitCenter
)
SELECT 
    @PO,
    kc.Material,
    kc.COHVQty,
    kc.Component,
    kc.Qty,
    @CartID,
    @CartTypeVal,
    @CartName,
    CAST(@CartType + CAST(@CartID AS VARCHAR(10)) AS VARCHAR(20)) + s.Suffix AS CartBin,
    kc.PickBin,
    @ProfitCenter
FROM #KitComponents kc
CROSS JOIN @Suffixes s
WHERE kc.PickBin IS NOT NULL;

 

PRINT 'Step 6: Inserted into ScanOrderHistory';

 

-- Step 7: Mark the cart as InUse
UPDATE TeamViewer_DYN.dbo.Carts
SET inUse = 1
WHERE CartID = @CartID;

 

PRINT 'Step 7: Cart marked as InUse';

 

-- Step 8: Return inserted rows
PRINT 'Step 8: Returning inserted rows';
SELECT * FROM [TeamViewer_DYN].[dbo].[ScanOrderHistory] WHERE PO = @PO;

 

DROP TABLE #KitComponents;