CREATE PROC [dbo].[SupplementaryInsurance_LoadDrugCondition]
    @ComputerName NVARCHAR(MAX),
    @GoodsCode VARCHAR(17)
AS
IF NOT EXISTS
(
    SELECT name
    FROM tempdb.dbo.sysobjects
    WHERE name = '##DrugCondition'
)
BEGIN
    CREATE TABLE ##DrugCondition
    (
        ComputerName NVARCHAR(MAX) COLLATE Arabic_CI_AS,
        GoodsCode VARCHAR(15) COLLATE Arabic_CI_AS,
        InsuranceCode VARCHAR(3) COLLATE Arabic_CI_AS,
        InsuranceName NVARCHAR(50) COLLATE Arabic_CI_AS,
        NonInsuranceAmount MONEY
            DEFAULT (0),
        PatientAmount MONEY
            DEFAULT (1),
        DifferAmount MONEY
            DEFAULT (0),
        GoodsAmount MONEY
            DEFAULT (0),
        Ceilling INT
            DEFAULT (0),
        MessageId INT
            DEFAULT (0)
    ) ON [PRIMARY];
END;
ELSE
    DELETE FROM dbo.##DrugCondition
    WHERE ComputerName = @ComputerName;

INSERT INTO ##DrugCondition
(
    InsuranceCode,
    GoodsCode,
    InsuranceName,
    NonInsuranceAmount,
    PatientAmount,
    DifferAmount,
    GoodsAmount,
    Ceilling,
    MessageId,
    ComputerName
)
SELECT dc.InsuranceCode,
       dc.GoodsCode,
       i.Name,
       dc.NonInsuranceAmount,
       dc.PatientAmount,
       dc.DifferAmount,
       dc.GoodsAmount,
       dc.Ceilling,
       dc.MessageId,
       @ComputerName
FROM dbo.SupplementaryInsurance_DrugCondition dc
    JOIN dbo.SupplementaryInsurance i
        ON i.Code = dc.InsuranceCode
    LEFT JOIN OrganizationsMessage msg
        ON msg.Id = dc.MessageId
WHERE dc.GoodsCode = @GoodsCode;
-----------------------------------------
INSERT INTO ##DrugCondition
(
    InsuranceCode,
    GoodsCode,
    InsuranceName,
    NonInsuranceAmount,
    PatientAmount,
    DifferAmount,
    GoodsAmount,
    Ceilling,
    MessageId,
    ComputerName
)
SELECT i.Code,
       @GoodsCode,
       i.Name,
       ISNULL(i.NonInsuranceFlag, 0) NonInsuranceAmount,
       ISNULL(i.PatientFlag, 1) PatientAmount,
       ISNULL(i.DifferFlag, 0) DifferAmount,
       ISNULL(i.GoodsFlag, 0) GoodsAmount,
       0,
       0,
       @ComputerName COLLATE Arabic_CI_AS
FROM dbo.SupplementaryInsurance i
WHERE i.Code NOT IN (
                        SELECT InsuranceCode COLLATE Arabic_CI_AS
                        FROM ##DrugCondition
                        WHERE ComputerName = @ComputerName
                              AND GoodsCode = @GoodsCode
                    );
----------------------------------------
SELECT dg.ComputerName,
       dg.GoodsCode,
       dg.InsuranceCode,
       dg.InsuranceName,
       dg.NonInsuranceAmount,
       dg.PatientAmount,
       dg.DifferAmount,
       dg.GoodsAmount,
       dg.Ceilling,
       dg.MessageId,
       om.[Title]
FROM ##DrugCondition dg
    LEFT JOIN dbo.OrganizationsMessage om
        ON om.Id = dg.MessageId
WHERE ComputerName = @ComputerName
      AND dg.GoodsCode = @GoodsCode
ORDER BY InsuranceCode;