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 (0),
        DifferAmount MONEY
            DEFAULT (0),
        GoodsAmount MONEY
            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,
    MessageId,
    ComputerName
)
SELECT dc.InsuranceCode,
       dc.GoodsCode,
       i.Name,
       dc.NonInsuranceAmount,
       dc.PatientAmount,
       dc.DifferAmount,
       dc.GoodsAmount,
       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,
    MessageId,
    ComputerName
)
SELECT i.Code ,
       @GoodsCode,
       i.Name ,
       0 NonInsuranceAmount,
       0 PatientAmount,
       0 DifferAmount,
       0 GoodsAmount,
       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 ,
       CAST(dg.NonInsuranceAmount AS MONEY ) NonInsuranceAmount,
       CAST(dg.PatientAmount AS MONEY) PatientAmount,
       CAST(dg.DifferAmount AS MONEY ) DifferAmount,
       CAST(dg.GoodsAmount AS MONEY) GoodsAmount,
       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