CREATE PROC [dbo].[ExclusiveInsuranceCode_Load]
    @ComputerName NVARCHAR(MAX),
    @GoodsCode VARCHAR(17),
	@JustLoad BIT 
AS
IF @JustLoad = 0 
Begin

IF NOT EXISTS
(
    SELECT name
    FROM tempdb.dbo.sysobjects
    WHERE name = '##ExclusiveInsuranceCode'
)
BEGIN
    CREATE TABLE ##ExclusiveInsuranceCode
    (
        ComputerName NVARCHAR(MAX) COLLATE Arabic_CI_AS,
        Id UNIQUEIDENTIFIER,
        Code VARCHAR(50),
        GoodsCode VARCHAR(15) COLLATE Arabic_CI_AS,
        InsuranceCode VARCHAR(3) COLLATE Arabic_CI_AS,
        SalesCeiling INT
            DEFAULT (0)
    ) ON [PRIMARY];
END;
ELSE
    DELETE FROM dbo.##ExclusiveInsuranceCode
    WHERE ComputerName = @ComputerName;

INSERT INTO ##ExclusiveInsuranceCode
(
    ComputerName,
    Id,
    Code,
    GoodsCode,
    InsuranceCode,
    SalesCeiling
)
SELECT @ComputerName,
       EIC.Id,
       EIC.Code,
       @GoodsCode,
       InsuranceCode,
       EIC.SalesCeiling
FROM ExclusiveInsuranceCode EIC
    JOIN dbo.Sahmiyeh I
        ON I.Sazman_Code = EIC.InsuranceCode
           AND I.Sazman_Code <> '00'
WHERE EIC.GoodsCode = @GoodsCode;
--------------------------------
INSERT INTO ##ExclusiveInsuranceCode
(
    ComputerName,
    GoodsCode,
    InsuranceCode
)
SELECT @ComputerName,
       @GoodsCode,
       I.Sazman_Code ---,
FROM dbo.Sahmiyeh I
WHERE I.Sazman_Code NOT IN
      (
          SELECT EIC.InsuranceCode
          FROM ##ExclusiveInsuranceCode EIC
          WHERE EIC.GoodsCode = @GoodsCode
                AND EIC.ComputerName = @ComputerName
      )
      AND I.Sazman_Code <> '00';
END 
--------------------------------
SELECT EIC.ComputerName,
       EIC.Id,
       EIC.Code,
       EIC.GoodsCode,
       EIC.InsuranceCode,
       I.Sa_Name InsuranceName,
       SalesCeiling,
       dbo.Find_Status(G.Darou_Flag) DrugStatus,
       InsuranceDrugStatus = CASE
                                 WHEN G.Darou_Flag = 6 THEN
                                     dbo.Find_Status([IS].Status)
                                 ELSE
                                     dbo.Find_Status(G.Darou_Flag)
                             END
FROM ##ExclusiveInsuranceCode EIC
    JOIN dbo.Sahmiyeh I
        ON I.Sazman_Code = EIC.InsuranceCode
    JOIN dbo.KalaId G
        ON G.K_Code = @GoodsCode
    LEFT JOIN dbo.Price_Sazman [IS]
        ON [IS].K_Code = G.K_Code
           AND G.Darou_Flag = 6
		   AND [IS].Sazman_Code = EIC.InsuranceCode
WHERE I.Sazman_Code <> '00'
      AND EIC.ComputerName = @ComputerName
      AND EIC.GoodsCode = @GoodsCode
ORDER BY EIC.InsuranceCode;