CREATE PROC [dbo].[UpdateNonInsuranceAdvanced]
    @WearhouseCode VARCHAR(MAX),
    @ComputerName VARCHAR(MAX),
    @CodeType SMALLINT,
    @OrgCode VARCHAR(MAX)
AS

BEGIN
    DELETE FROM dbo.Price_Sazman
    WHERE K_Code NOT IN
          (
              SELECT K_Code FROM dbo.KalaId
          );
    DELETE FROM Price_Sazman
    WHERE K_Code IN
          (
              SELECT K_Code FROM dbo.KalaId WHERE Darou_Flag <> 6
          );
    DELETE FROM Price_Sazman
    WHERE Sazman_Code NOT IN
          (
              SELECT Sazman_Code FROM Sahmiyeh
          );
----------------- Prepare Temp Table 
    IF OBJECT_ID('tempdb.dbo.##tmpView') IS NOT NULL
    BEGIN
        DELETE FROM dbo.##tmpView
        WHERE Computer_Name = @ComputerName;
    END;
    ELSE
        CREATE TABLE ##tmpView
        (
            K_Code VARCHAR(15) NOT NULL, --PRIMARY KEY,
            Name1 NVARCHAR(100),
            [Name] NVARCHAR(200),
            M_Code VARCHAR(8),
            Darou_Flag CHAR(1),
            Price MONEY,
            UpdateStatus TINYINT,
            UpdateStatusName NVARCHAR(20),
            Computer_Name NVARCHAR(30),
            Condition NVARCHAR(80),
            [Percent] REAL,
            Brand_Code VARCHAR(6),
            Org_Code VARCHAR(6),
            IsReduce BIT,
            IsIncrease BIT,
            Old_Price MONEY,
            sa_Price MONEY,
            [Status] CHAR(1),
            CurPercent REAL,
            Sazman_Code VARCHAR(3),
            [Type] NVARCHAR(MAX)
        );

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

    IF @CodeType = 4
    BEGIN
        PRINT 'CodeType = > 4 Step 1 ';
        -------------Step 1
        INSERT INTO ##tmpView
        (
            K_Code,
            Sazman_Code,
            Computer_Name,
            [Percent],
            Old_Price,
            Price,
            Darou_Flag,
            UpdateStatus
        )
        SELECT g.K_Code,
               Insurance.Code,
               tmp.Computer_Name,
               MAX(tmp.[Percent]),
               MAX(Anbar.Price_Forosh),
               MAX(tmp.Price),
               g.Darou_Flag,
               0
        FROM ##tmpExcel tmp
            CROSS APPLY
        (
            SELECT splitdata Code
            FROM dbo.fnSplitString(@OrgCode, ',')
        ) AS Insurance
            JOIN dbo.ExclusiveInsuranceCode EIC
                ON @CodeType = 4
                   AND SUBSTRING(EIC.Code, 0, 6) = tmp.ExclusiveCode
                   AND EIC.InsuranceCode = Insurance.Code
            JOIN dbo.KalaId g
                ON g.K_Code = EIC.GoodsCode
            JOIN dbo.Anbar
                ON dbo.Anbar.K_Code = g.K_Code
            LEFT JOIN Price_Sazman OP
                ON OP.K_Code COLLATE Arabic_CI_AS = g.K_Code
                   AND OP.Sazman_Code COLLATE Arabic_CI_AS = EIC.InsuranceCode
        WHERE Computer_Name = @ComputerName
              AND Anbar.A_Code = @WearhouseCode
              AND ISNULL(tmp.Price, 0) > 0
              AND
              (
                  (
                      ISNULL(tmp.[Percent], 0) > 0
                      AND ISNULL(tmp.[Percent], 0) < 100
                  )
                  OR ISNULL(tmp.[Percent], 0) = -100
              )
              AND
              (
                  g.Darou_Flag = 0
                  OR
                  (
                      g.Darou_Flag = 6
                      AND (OP.K_Code IS NULL)
                  )
              )
        GROUP BY g.K_Code,
                 Insurance.Code,
                 tmp.Computer_Name,
                 g.Darou_Flag;
        ---------------Step 2
        PRINT 'CodeType = > 4 Step 2 ';
        INSERT INTO ##tmpView
        (
            K_Code,
            Sazman_Code,
            Computer_Name,
            [Percent],
            Old_Price,
            Price,
            Darou_Flag,
            UpdateStatus
        )
        SELECT g.K_Code,
               Insurance.Code,
               tmp.Computer_Name,
               MAX(tmp.[PERCENT]),
               MAX(Anbar.Price_Forosh),
               MAX(tmp.Price),
               g.Darou_Flag,
               0
        FROM ##tmpExcel tmp
            JOIN dbo.KalaId g
                ON g.Meli_Code = tmp.M_Code
                   AND g.Meli_Code <> ''
            JOIN dbo.Anbar
                ON dbo.Anbar.K_Code = g.K_Code
            CROSS APPLY
        (
            SELECT splitdata Code
            FROM dbo.fnSplitString(@OrgCode, ',')
        ) AS Insurance
            LEFT JOIN Price_Sazman OP
                ON OP.K_Code COLLATE Arabic_CI_AS = g.K_Code
                   AND OP.Sazman_Code COLLATE Arabic_CI_AS = Insurance.Code
        WHERE Computer_Name = @ComputerName
              AND Anbar.A_Code = @WearhouseCode
              AND g.K_Code NOT IN
                  (
                      SELECT K_Code COLLATE Arabic_CI_AS FROM ##tmpView tmp --WHERE tmp.Sazman_Code = op.Sazman_Code
                  )
              AND ISNULL(tmp.Price, 0) > 0
              AND
              (
                  g.Darou_Flag = 0
                  OR
                  (
                      g.Darou_Flag = 6
                      AND (OP.K_Code IS NULL)
                  )
              )
              AND
              (
                  (
                      ISNULL(tmp.[Percent], 0) > 0
                      AND ISNULL(tmp.[Percent], 0) < 100
                  )
                  OR ISNULL(tmp.[Percent], 0) = -100
              )
        GROUP BY g.K_Code,
                 Insurance.Code,
                 tmp.Computer_Name,
                 g.Darou_Flag;
    END;
    ---------------------
    IF @CodeType = 1
    BEGIN
        PRINT 'CodeType = > 1 One Step  ';
        INSERT INTO ##tmpView
        (
            K_Code,
            Computer_Name,
            [Percent],
            Old_Price,
            Price,
            Darou_Flag,
            UpdateStatus
        )
        SELECT G.K_Code,
               tmp.Computer_Name,
               tmp.[PERCENT],
               Anbar.Price_Forosh,
               tmp.Price,
               G.Darou_Flag,
               0
        FROM ##tmpExcel tmp
            JOIN KalaId G
                ON G.Brand_Code COLLATE Arabic_CI_AS = tmp.Brand_Code
                   AND G.Brand_Code <> ''
            JOIN dbo.Anbar
                ON dbo.Anbar.K_Code = G.K_Code
            CROSS APPLY
        (
            SELECT splitdata Code
            FROM dbo.fnSplitString(@OrgCode, ',')
        ) AS Insurance
            LEFT JOIN Price_Sazman OP
                ON OP.K_Code COLLATE Arabic_CI_AS = G.K_Code
                   AND OP.Sazman_Code COLLATE Arabic_CI_AS = Insurance.Code
        WHERE Computer_Name = @ComputerName
              AND Anbar.A_Code = @WearhouseCode
              AND G.K_Code NOT IN
                  (
                      SELECT K_Code COLLATE Arabic_CI_AS FROM ##tmpView
                  )
              AND ISNULL(tmp.Price, 0) > 0
              AND
              (
                  G.Darou_Flag = 0
                  OR
                  (
                      G.Darou_Flag = 6
                      AND (OP.K_Code IS NULL)
                  )
              )
              AND
              (
                  (
                      ISNULL(tmp.[Percent], 0) > 0
                      AND ISNULL(tmp.[Percent], 0) < 100
                  )
                  OR ISNULL(tmp.[Percent], 0) = -100
              );
    END;
END;