CREATE PROC [dbo].[EPMCalculateSupplementaryInsurance]
    @PrescriptionType TINYINT,
    @InsuranceCode VARCHAR(3),
    @FormulaDefinition VARCHAR(MAX) = NULL,
    @PrescriptionId UNIQUEIDENTIFIER,
    @PyramidPrescriptionTempId INT = NULL
AS
BEGIN
    /*--------------------------------------------------------------------------------------- Declare Variable*/
    DECLARE @NonInsuranceFlag BIT;
    DECLARE @PatientFlag BIT;
    DECLARE @GoodsFlag BIT;
    DECLARE @DifferFlag BIT;
    DECLARE @SupplementaryTransactionId SMALLINT;
    DECLARE @IncurableTransactionId SMALLINT;
    /*--------------------------------------------------------------------------------------- Get Tamin Incurable Transaction Id */
    IF EXISTS
    (
        SELECT 1
        FROM dbo.GeneralSetting
        WHERE Parameter = 'TAMIN_Settings_ActiveSaveSupport'
              AND Value = 'True'
    )
       AND @PrescriptionType IN ( 0, 4 )
        SELECT @IncurableTransactionId = CAST(ISNULL(Value, 0) AS SMALLINT)
        FROM dbo.GeneralSetting
        WHERE Parameter = 'TAMIN_Settings_TransactionCodeSupport';

    /*--------------------------------------------------------------------------------------- Declare Prescription Detail*/
    DECLARE @PrescriptionDetail AS TABLE
    (
        Id UNIQUEIDENTIFIER NOT NULL,
        RowIndex TINYINT
            DEFAULT (1) NULL,
        GoodsCode VARCHAR(15) NOT NULL,
        GenericCode VARCHAR(6) NULL,
        IRCCode VARCHAR(20) NULL,
        InsuredFranshiz FLOAT
            DEFAULT (0),         /* Insured Franshiz Percent */
        StatusCode TINYINT,
        CoverCount FLOAT,        /*insurance Coverd Count Amount*/
        DeliverCount FLOAT,
        SalesPriceAmount MONEY,  /* Sales Price Fee */
        IncurablePaidAmount MONEY,
        InsuredAmount MONEY,     /* Insurer Insured Amount */
        NonInsuranceAmount MONEY,
        EquipmentAmount MONEY,
        GoodsAmount MONEY,
        DifferenceAmount MONEY,
        InsuredPaidAmount MONEY, /* Total Insured Pay Amount ( Insured Amount + Differ Amount )*/
        TotalPrice MONEY,        /* Pyramid Total Sales Price Amount  */
        IsIncludeSupplementaryInsurance BIT
            DEFAULT (1)
    );
    /*--------------------------------------------------------------------------------------- Declare Temp Table for Calculate Supplementary Insurance */
    DECLARE @SupplementaryInsurance AS TABLE
    (
        Id UNIQUEIDENTIFIER NOT NULL,
        GoodsCode VARCHAR(15) NOT NULL,
        DifferenceAmount MONEY
            DEFAULT (0),
        DifferencePaidAmount MONEY
            DEFAULT (0),
        PatientAmount MONEY
            DEFAULT (0),
        PatientPaidAmount MONEY
            DEFAULT (0),
        GoodsAmount MONEY
            DEFAULT (0),
        GoodsPaidAmount MONEY
            DEFAULT (0),
        NonInsuranceAmount MONEY,
        NonInsurancePaidAmount MONEY,
        EquipmentAmount MONEY,
        EquipmentPaidAmount MONEY,
        SupplementaryAmount MONEY
            DEFAULT (0),
        StatusCode TINYINT,
        IsMultipleDiffer BIT,
        IsMultiplePatient BIT,
        IsMultipleGoods BIT,
        IsMultipleNonInsurance BIT,
        IsSpecialDrug BIT
            DEFAULT (0),
        MessageBody NVARCHAR(MAX)
    );
    /*--------------------------------------------------------------------------------------- Declare Temp Table for Calculate Supportive Companies*/
    DECLARE @SupportiveInsuranceTransaction AS TABLE
    (
        RowId UNIQUEIDENTIFIER NOT NULL,
        Id SMALLINT NOT NULL,
        InsuranceName NVARCHAR(30) NULL,
        PatientPaidAmount MONEY
            DEFAULT (0),
        DifferencePaidAmount MONEY
            DEFAULT (0),
        TransactionId SMALLINT,
        Kind TINYINT NULL
    );

    /*--------------------------------------------------------------------------------------- Add Subscriptions item to Declared Table - Prepare Prescription Detail */
    INSERT INTO @PrescriptionDetail
    EXEC [dbo].[EPMPreCalculateSupplementaryInsurance] @PrescriptionType = @PrescriptionType,
                                                       @PrescriptionId = @PrescriptionId,
                                                       @PyramidPrescriptionTempId = @PyramidPrescriptionTempId,
                                                       @IncurableTransactionId = @IncurableTransactionId;
    --SELECT * FROM @PrescriptionDetail
    /*--------------------------------------------------------------------------------------- Currect (-1) Diifer of Insured  Amount  */


    UPDATE @PrescriptionDetail
    SET InsuredPaidAmount = ROUND(InsuredAmount, 0)
    WHERE ROUND(InsuredAmount - InsuredPaidAmount, 0) = 1;


    /*--------------------------------------------------------------------------------------- Get Supplementary Insurance Rules*/
    SELECT @NonInsuranceFlag = NonInsuranceFlag,
           @PatientFlag = PatientFlag,
           @GoodsFlag = GoodsFlag,
           @DifferFlag = DifferFlag,
           @SupplementaryTransactionId = TransactionId
    FROM dbo.SupplementaryInsurance
    WHERE Code = @InsuranceCode;


    IF (@FormulaDefinition IS NOT NULL AND @FormulaDefinition <> '')
    BEGIN
        /*--------------------------------------------------------------------------------------- Calc By Supplementary Insurance Pharmacopoeia Rules*/
        INSERT INTO @SupplementaryInsurance
        (
            Id,
            GoodsCode,
            DifferenceAmount,
            PatientAmount,
            GoodsAmount,
            NonInsuranceAmount,
            EquipmentAmount,
            SupplementaryAmount,
            StatusCode,
            IsMultipleDiffer,
            IsMultiplePatient,
            IsMultipleGoods,
            IsMultipleNonInsurance,
            IsSpecialDrug,
            MessageBody
        )
        SELECT Id,
               GoodsCode,
               ISNULL(DifferAmount, 0),
               ISNULL(PatientAmount, 0),
               ISNULL(GoodsAmount, 0),
               ISNULL(NonInsuranceAmount, 0),
               ISNULL(EquipmentAmount, 0),
               ISNULL(SupplementaryAmount, 0),
               StatusCode,
               IsMultipleDiffer,
               IsMultiplePatient,
               ISMultipleGoods,
               ISMultipleNonInsurance,
               ISNULL(IsSpecialDrug, 0),
               MessageBody
        FROM
        (
            SELECT D.Id,
                   S.InsuranceCode,
                   D.GoodsCode,
                   CASE /*------------------------------------------------------------------- Calc Differ Amount*/
                       WHEN ISNULL(S.DifferAmount, 0) = 0 THEN
                           0
                       WHEN ISNULL(S.DifferAmount, 0) > 99
                            AND D.DifferenceAmount > 0 THEN
                   (CASE
                        WHEN (ISNULL(S.DifferAmount, 0) <= (D.DifferenceAmount / D.DeliverCount))
                             AND
                             (
                                 S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount
                             ) THEN
                            ISNULL(S.DifferAmount, 0) * D.DeliverCount
                        WHEN (ISNULL(S.DifferAmount, 0) <= (D.DifferenceAmount / D.DeliverCount))
                             AND
                             (
                                 S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount
                             ) THEN
                            ISNULL(S.DifferAmount, 0) * S.Ceilling
                        ELSE
                            D.DifferenceAmount
                    END
                   )
                       WHEN ISNULL(S.DifferAmount, 0) = 1
                            AND D.DifferenceAmount > 0 THEN
                   (CASE
                        WHEN S.Ceilling = 0
                             OR S.Ceilling >= D.DeliverCount THEN
                            D.DifferenceAmount
                        WHEN S.Ceilling <> 0
                             AND S.Ceilling < D.DeliverCount THEN
                            S.Ceilling * (D.DifferenceAmount / D.DeliverCount)
                        ELSE
                            0
                    END
                   )
                       WHEN ISNULL(S.DifferAmount, 0)
                            BETWEEN 2 AND 99
                            AND D.DifferenceAmount > 0 THEN
                   (CASE
                        WHEN S.Ceilling = 0
                             OR S.Ceilling >= D.DeliverCount THEN
                            S.DifferAmount * D.DifferenceAmount * .01
                        WHEN S.Ceilling <> 0
                             AND S.Ceilling < D.DeliverCount THEN
                            S.Ceilling * ((S.DifferAmount * D.DifferenceAmount) * .01 / D.DeliverCount)
                        ELSE
                            0
                    END
                   )
                       ELSE
                           0
                   END AS DifferAmount,
                   CASE
                       WHEN D.StatusCode = 2 THEN
                           0
                       ELSE
                           D.InsuredAmount
                   END InsuredAmount,
                   CASE
                       WHEN D.StatusCode <> 2 THEN
                           CASE /*--------------------------------------------------------------- Calc Patient Amount*/
                               WHEN ISNULL(S.PatientAmount, 0) = 0 THEN
                                   0
                               WHEN ISNULL(S.PatientAmount, 0) > 99
                                    AND ISNULL(S.PatientAmount, 0) <> 1 THEN
                   (CASE /*Start Case 01*/
                        WHEN (S.PatientAmount <= D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0))
                             AND
                             (
                                 S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount
                             )
                             AND
                             (
                                 (
                                     S.Ceilling = 0
                                     AND S.PatientAmount * D.DeliverCount <= D.InsuredAmount
                                 )
                                 OR
                                 (
                                     S.Ceilling >= D.DeliverCount
                                     AND S.PatientAmount * D.DeliverCount <= D.InsuredAmount
                                 )
                             ) THEN
                            S.PatientAmount * D.DeliverCount
                        WHEN (S.PatientAmount <= D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0))
                             AND
                             (
                                 S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount
                             )
                             AND
                             (
                                 (
                                     S.Ceilling <> 0
                                     AND S.PatientAmount * S.Ceilling <= D.InsuredAmount
                                 )
                                 OR
                                 (
                                     S.Ceilling >= D.DeliverCount
                                     AND S.PatientAmount * S.Ceilling <= D.InsuredAmount
                                 )
                             ) THEN
                            S.PatientAmount * S.Ceilling
                        ELSE
                            D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0)
                    END /*End Case 01*/
                   )
                               WHEN ISNULL(S.PatientAmount, 0) = 1 THEN /*------------------- Full Pay - 100 Percent Pay*/
                   (CASE /*Start Case 02*/
                        WHEN S.Ceilling = 0
                             OR S.Ceilling >= D.DeliverCount THEN
                            D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0) -----1403
                        WHEN S.Ceilling <> 0
                             AND S.Ceilling < D.DeliverCount
                             AND (D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0)) * S.PatientAmount * .01 >= D.InsuredAmount THEN
                            D.InsuredAmount
                        WHEN S.Ceilling <> 0
                             AND S.Ceilling < D.DeliverCount THEN
                            D.SalesPriceAmount * S.Ceilling * D.InsuredFranshiz * .01
                            - (ISNULL(D.IncurablePaidAmount, 0) / D.DeliverCount)
                        ELSE
                            0
                    END /*End Case 02*/
                   )
                               WHEN ISNULL(S.PatientAmount, 0)/*-------------------------   Pay Declared Price Amount */
                                    BETWEEN 2 AND 99 THEN
                   (CASE /*Start Case 03*/
                        WHEN S.Ceilling = 0
                             OR S.Ceilling >= D.DeliverCount THEN
                   (D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0)) * S.PatientAmount * .01
                        WHEN S.Ceilling <> 0
                             AND S.Ceilling < D.DeliverCount THEN
                   (D.SalesPriceAmount * S.Ceilling * D.InsuredFranshiz * .01
                    - (ISNULL(D.IncurablePaidAmount, 0) / D.DeliverCount)
                   ) * S.PatientAmount * .01
                        ELSE
                            0
                    END /*End Case 03*/
                   )
                               ELSE
                                   0
                           END
                       ELSE
                           0
                   END AS PatientAmount,
                   CASE /*------------------------------------------------------------------Calc Goods Amount*/
                       WHEN D.StatusCode = 1 THEN
                   (CASE
                        WHEN ISNULL(S.GoodsAmount, 0) = 0 THEN
                            0
                        WHEN ISNULL(S.GoodsAmount, 0) > 99 THEN
                   (CASE
                        WHEN (ISNULL(S.GoodsAmount, 0) <= D.TotalPrice)
                             AND
                             (
                                 S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount
                             ) THEN
                            ISNULL(S.GoodsAmount, 0) * D.DeliverCount
                        WHEN (ISNULL(S.GoodsAmount, 0) <= D.TotalPrice)
                             AND
                             (
                                 S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount
                             ) THEN
                            ISNULL(S.GoodsAmount, 0) * S.Ceilling
                        ELSE
                            D.TotalPrice
                    END
                   )
                        WHEN ISNULL(S.GoodsAmount, 0) = 1 THEN
                   (CASE
                        WHEN S.Ceilling = 0
                             OR S.Ceilling >= D.DeliverCount THEN
                            D.TotalPrice
                        WHEN S.Ceilling <> 0
                             AND S.Ceilling < D.DeliverCount THEN
                            S.Ceilling * D.SalesPriceAmount
                        ELSE
                            0
                    END
                   )
                        WHEN ISNULL(S.GoodsAmount, 0) ------>Yazdian
                             BETWEEN 2 AND 99 THEN
                   (CASE
                        WHEN S.Ceilling = 0
                             OR S.Ceilling >= D.DeliverCount THEN
                            D.TotalPrice * S.GoodsAmount * .01
                        WHEN S.Ceilling <> 0
                             AND S.Ceilling < D.DeliverCount THEN
                            D.SalesPriceAmount * S.Ceilling * S.GoodsAmount * .01
                        ELSE
                            0
                    END
                   )
                        ELSE
                            0
                    END
                   )
                       ELSE
                           0
                   END AS GoodsAmount,
                   CASE /*----------------------------------------------------------Calc NonInsurance Amount  */
                       WHEN D.StatusCode = 0 THEN
                   (CASE
                        WHEN ISNULL(S.NonInsuranceAmount, 0) = 0 THEN
                            0
                        WHEN ISNULL(S.NonInsuranceAmount, 0) > 99 THEN
                   (CASE
                        WHEN (ISNULL(S.NonInsuranceAmount, 0) <= D.TotalPrice)
                             AND
                             (
                                 S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount
                             ) THEN
                            ISNULL(S.NonInsuranceAmount, 0) * D.DeliverCount
                        WHEN (ISNULL(S.NonInsuranceAmount, 0) <= D.TotalPrice)
                             AND
                             (
                                 S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount
                             ) THEN
                            ISNULL(S.NonInsuranceAmount, 0) * S.Ceilling
                        ELSE
                            D.TotalPrice
                    END
                   )
                        WHEN ISNULL(S.NonInsuranceAmount, 0) = 1 THEN
                   (CASE
                        WHEN S.Ceilling = 0
                             OR S.Ceilling >= D.DeliverCount THEN
                            D.TotalPrice
                        WHEN S.Ceilling <> 0
                             AND S.Ceilling < D.DeliverCount THEN
                            S.Ceilling * D.SalesPriceAmount
                        ELSE
                            0
                    END
                   )
                        WHEN ISNULL(S.NonInsuranceAmount, 0)
                             BETWEEN 2 AND 99 THEN
                   (CASE
                        WHEN S.Ceilling = 0
                             OR S.Ceilling >= D.DeliverCount THEN
                            D.TotalPrice * S.NonInsuranceAmount * .01
                        WHEN S.Ceilling <> 0
                             AND S.Ceilling < D.DeliverCount THEN
                            D.SalesPriceAmount * S.Ceilling * S.NonInsuranceAmount * .01
                        ELSE
                            0
                    END
                   )
                        ELSE
                            0
                    END
                   )
                       ELSE
                           0
                   END AS NonInsuranceAmount,
                   CASE
                       WHEN D.StatusCode = 2 THEN
                           D.InsuredPaidAmount
                       ELSE
                           0
                   END AS EquipmentAmount,
                   0 SupplementaryAmount,
                   D.StatusCode,
                   CASE
                       WHEN ISNULL(S.DifferAmount, 0)
                            BETWEEN 2 AND 99 THEN
                           1
                       ELSE
                           0
                   END AS IsMultipleDiffer,
                   CASE
                       WHEN ISNULL(S.PatientAmount, 0)
                            BETWEEN 2 AND 99 THEN
                           1
                       ELSE
                           0
                   END AS IsMultiplePatient,
                   CASE
                       WHEN ISNULL(S.GoodsAmount, 0)
                            BETWEEN 2 AND 99 THEN
                           1
                       ELSE
                           0
                   END AS ISMultipleGoods,
                   CASE
                       WHEN ISNULL(S.NonInsuranceAmount, 0)
                            BETWEEN 2 AND 99 THEN
                           1
                       ELSE
                           0
                   END AS ISMultipleNonInsurance,
                   S.IsSpecialDrug,
                   M.[Message] MessageBody
            FROM @PrescriptionDetail D
                INNER JOIN dbo.SupplementaryInsurance_DrugCondition S
                    ON S.InsuranceCode = @InsuranceCode
                       AND S.GoodsCode = D.GoodsCode
                       AND D.IsIncludeSupplementaryInsurance = 1
                LEFT JOIN dbo.OrganizationsMessage M
                    ON M.Id = S.MessageId
                       AND ISNULL(M.IsActive, 0) = 1
        ) AS SupplementaryInsurance;
        /*--------------------------------------------------------------------------------------- Calc By Supplementary Insurance  Rules*/
        INSERT INTO @SupplementaryInsurance
        (
            Id,
            GoodsCode,
            DifferenceAmount,
            PatientAmount,
            GoodsAmount,
            NonInsuranceAmount,
            EquipmentAmount,
            SupplementaryAmount,
            StatusCode,
            IsMultipleDiffer,
            IsMultiplePatient,
            IsMultipleGoods,
            IsMultipleNonInsurance,
            IsSpecialDrug
        )
        SELECT D.Id,
               D.GoodsCode,
               CASE
                   WHEN D.StatusCode = 7 THEN
                       0
                   WHEN @DifferFlag = 0
                        AND D.DifferenceAmount > 0 THEN
                       0
                   WHEN @DifferFlag = 1
                        AND DifferenceAmount > 0 THEN
                       DifferenceAmount
                   ELSE
                       0
               END DifferAmount,
               CASE
                   WHEN D.StatusCode = 7 THEN
                       0
                   WHEN @PatientFlag = 0
                        AND D.StatusCode IN ( 5, 6, 8, 9 ) THEN
                       0
                   WHEN @PatientFlag = 1
                        AND D.StatusCode IN ( 5, 6, 8, 9 ) THEN
                       D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0)
                   ELSE
                       0
               END PatientAmount,
               CASE
                   WHEN D.StatusCode = 7 THEN
                       0
                   WHEN @GoodsFlag = 0
                        AND D.StatusCode = 1 THEN
                       0
                   WHEN @GoodsFlag = 1
                        AND D.StatusCode = 1 THEN
                       D.TotalPrice
                   ELSE
                       0
               END GoodsAmount,
               CASE
                   WHEN D.StatusCode = 7 THEN
                       0
                   WHEN @NonInsuranceFlag = 0
                        AND D.StatusCode = 0 THEN
                       0
                   WHEN @NonInsuranceFlag = 1
                        AND D.StatusCode = 0 THEN
                       D.TotalPrice
                   ELSE
                       0
               END NonInsuranceAmount,
               CASE
                   WHEN D.StatusCode = 2 THEN
                       D.InsuredPaidAmount
                   ELSE
                       0
               END EquipmentAmount,
               0,
               D.StatusCode,
               0,
               0,
               0,
               0,
               0
        FROM @PrescriptionDetail D
        WHERE D.Id NOT IN
              (
                  SELECT Id
                  FROM @SupplementaryInsurance
                  WHERE D.IsIncludeSupplementaryInsurance = 1
              )
              AND D.IsIncludeSupplementaryInsurance = 1;

        --SELECT *
        --FROM @PrescriptionDetail;


        /*--------------------------------------------------------------------------------------- Update by Calculated Price */
        UPDATE @SupplementaryInsurance
        SET NonInsurancePaidAmount = ROUND(Calc.NonInsurancePaidAmount, 0),
            GoodsPaidAmount = ROUND(Calc.GoodsPaidAmount, 0),
            DifferencePaidAmount = ROUND(Calc.DifferencePaidAmount, 0),
            PatientPaidAmount = ROUND(Calc.PatientPaidAmount, 0),
            EquipmentPaidAmount = ROUND(Calc.EquipmentPaidAmount, 0),
            SupplementaryAmount = Calc.NonInsurancePaidAmount + Calc.GoodsPaidAmount + Calc.DifferencePaidAmount
                                  + Calc.PatientPaidAmount + Calc.EquipmentPaidAmount
        FROM @SupplementaryInsurance S
            INNER JOIN
            (
                SELECT StatusCode,
                       GoodsCode,
                       Id,
                       CASE
                           WHEN IsMultipleNonInsurance = 1 THEN
                               ISNULL(NonInsuranceAmount, 0)
                           ELSE
                               REPLACE(
                                          SUBSTRING(@FormulaDefinition, 0, CHARINDEX('A', @FormulaDefinition) - 1),
                                          '0.',
                                          ''
                                      ) * ISNULL(NonInsuranceAmount, 0) * .01
                       END NonInsurancePaidAmount,
                       CASE
                           WHEN IsMultipleGoods = 1 THEN
                               GoodsAmount
                           ELSE
                               REPLACE(
                                          SUBSTRING(
                                                       @FormulaDefinition,
                                                       CHARINDEX('A', @FormulaDefinition) + 2,
                                                       CHARINDEX('B', @FormulaDefinition)
                                                       - CHARINDEX('A', @FormulaDefinition) - 3
                                                   ),
                                          '0.',
                                          ''
                                      ) * ISNULL(GoodsAmount, 0) * .01
                       END GoodsPaidAmount,
                       CASE
                           WHEN IsMultipleDiffer = 1 THEN
                               DifferenceAmount
                           ELSE
                               REPLACE(
                                          SUBSTRING(
                                                       @FormulaDefinition,
                                                       CHARINDEX('B', @FormulaDefinition) + 2,
                                                       CHARINDEX('C', @FormulaDefinition)
                                                       - CHARINDEX('B', @FormulaDefinition) - 3
                                                   ),
                                          '0.',
                                          ''
                                      ) * ISNULL(DifferenceAmount, 0) * .01
                       END DifferencePaidAmount,
                       CASE
                           WHEN IsMultiplePatient = 1 THEN
                               PatientAmount
                           ELSE
                               REPLACE(
                                          SUBSTRING(
                                                       @FormulaDefinition,
                                                       CHARINDEX('C', @FormulaDefinition) + 2,
                                                       CHARINDEX('D', @FormulaDefinition)
                                                       - CHARINDEX('C', @FormulaDefinition) - 3
                                                   ),
                                          '0.',
                                          ''
                                      ) * ISNULL(PatientAmount, 0) * .01
                       END PatientPaidAmount,
                       REPLACE(
                                  SUBSTRING(
                                               @FormulaDefinition,
                                               CHARINDEX('D', @FormulaDefinition) + 2,
                                               CHARINDEX('E', @FormulaDefinition) - CHARINDEX('D', @FormulaDefinition)
                                               - 3
                                           ),
                                  '0.',
                                  ''
                              ) * ISNULL(EquipmentAmount, 0) * .01 EquipmentPaidAmount
                FROM @SupplementaryInsurance
            ) AS Calc
                ON Calc.GoodsCode = S.GoodsCode
                   AND Calc.Id = S.Id
                   AND S.StatusCode = Calc.StatusCode;

    END;

    /*----------------------------------------------------------------------------------------- Calculate Supportive Companies Row */
    INSERT INTO @SupportiveInsuranceTransaction
    (
        RowId,
        Id,
        InsuranceName,
        TransactionId,
        PatientPaidAmount,
        DifferencePaidAmount
    )
    SELECT D.Id,
           T.AutoId,
           T.[Name],
           S.IncrementalDeductionId,
           SUM(ROUND(
                        CASE
                            WHEN S.PatientPercent > 0 /*------------------------------------ Calc By Percent */
                                 AND D.InsuredFranshiz <> 100 THEN
                                CASE
                                    WHEN D.InsuredFranshiz = 0
                                         AND D.StatusCode = 0 THEN
                        ((D.SalesPriceAmount * D.DeliverCount)
                         - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                        )
                        * S.PatientPercent * .01
                                    WHEN D.InsuredFranshiz = 0
                                         AND D.StatusCode IN ( 4, 5, 6 ) THEN
                                        0
                                    ELSE
                        ((D.SalesPriceAmount * D.DeliverCount * D.InsuredFranshiz * .01)
                         - (ISNULL(SI.PatientPaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                        ) * S.PatientPercent
                        * .01
                                END
                            WHEN ISNULL(S.PatientPrice, 0) > 0 /*------------------------------------ Calc By Price  */
                                 AND D.InsuredFranshiz <> 100 THEN
                                CASE
                                    WHEN D.InsuredFranshiz = 0 /*---------------------------- Total Insured Remain Amount VS Patient Price Of Supportive*/
                                         AND D.StatusCode = 0
                                         AND D.InsuredPaidAmount
                                             - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0)) < S.PatientPrice THEN
                                        D.InsuredPaidAmount
                                        - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                                    WHEN D.InsuredFranshiz = 0 /*---------------------------- Total Insured Remain Amount VS Total Patient Price Of Supportive*/
                                         AND D.StatusCode = 0
                                         AND D.InsuredPaidAmount
                                             - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0)) < S.PatientPrice
                                                                                                                           * D.DeliverCount THEN
                                        D.InsuredPaidAmount
                                        - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                                    WHEN D.InsuredFranshiz = 0
                                         AND D.StatusCode = 0
                                         AND D.InsuredPaidAmount
                                             - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0)) >= S.PatientPrice
                                                                                                                            * D.DeliverCount THEN
                                        S.PatientPrice * D.DeliverCount
                                    WHEN D.InsuredFranshiz = 0
                                         AND D.StatusCode = 0
                                         AND D.InsuredPaidAmount
                                             - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0)) >= S.PatientPrice THEN
                                        S.PatientPrice
                                    WHEN D.InsuredFranshiz = 0
                                         AND D.StatusCode IN ( 4, 5, 6 ) THEN
                                        0
                                    WHEN (D.SalesPriceAmount * D.DeliverCount * D.InsuredFranshiz * .01)
                                         - (ISNULL(SI.PatientPaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0)) > S.PatientPrice
                                                                                                                  * D.DeliverCount THEN
                                        S.PatientPrice * D.DeliverCount
                                    ELSE
                        (D.SalesPriceAmount * D.DeliverCount * D.InsuredFranshiz * .01)
                        - (ISNULL(SI.PatientPaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                                END
                            ELSE
                                0
                        END,
                        0
                    )
              ) AS PatientPaidAmount,
           SUM(ROUND(
                        CASE /*------------------------------------ Calc Difference Price Amnount */
                            WHEN S.DifferPercent > 0
                                 AND D.DifferenceAmount > 0 THEN
                                CASE
                                    WHEN S.DifferPercent * D.DifferenceAmount * .01 >= D.DifferenceAmount THEN
                                        D.DifferenceAmount - ISNULL(SI.DifferencePaidAmount, 0)
                                    ELSE
                        (D.DifferenceAmount - ISNULL(SI.DifferencePaidAmount, 0)) * S.DifferPercent * .01
                                END
                            WHEN S.DifferPrice > 0
                                 AND D.DifferenceAmount > 0 THEN
                                CASE
                                    WHEN (S.DifferPrice * D.DeliverCount) <= D.DifferenceAmount
                                                                             - ISNULL(SI.DifferencePaidAmount, 0) THEN
                                        S.DifferPrice * D.DeliverCount
                                    ELSE
                                        D.DifferenceAmount - ISNULL(SI.DifferencePaidAmount, 0)
                                END
                            ELSE
                                0
                        END,
                        0
                    )
              ) AS DifferencePaidAmount
    FROM @PrescriptionDetail D
        JOIN dbo.GoodsSupportiveCompanies S
            ON S.GoodsCode = D.GoodsCode
        JOIN dbo.Over_Under T
            ON T.AutoId = S.IncrementalDeductionId
        LEFT JOIN
        (
            SELECT Id,
                   GoodsCode,
                   SUM(PatientPaidAmount) PatientPaidAmount,
                   SUM(DifferencePaidAmount) DifferencePaidAmount,
                   SUM(NonInsurancePaidAmount) NonInsurancePaidAmount
            FROM @SupplementaryInsurance
            GROUP BY Id,
                     GoodsCode
        ) AS SI
            ON SI.Id = D.Id
               AND SI.GoodsCode = D.GoodsCode
    WHERE D.StatusCode NOT IN ( 3, 8 )
          AND D.StatusCode > CASE
                                 WHEN S.Kind = 0 THEN
                                     -1
                                 ELSE
                                     0
                             END
    GROUP BY D.Id,
             T.[Name],
             T.AutoId,
             S.IncrementalDeductionId;

    /*--------------------------------------------------------------------------------------- Currect (-1) after round calculated value  in  Supportive Insurance Transaction*/
    UPDATE @SupportiveInsuranceTransaction
    SET PatientPaidAmount = 0
    WHERE PatientPaidAmount = -1;

    UPDATE @SupportiveInsuranceTransaction
    SET DifferencePaidAmount = 0
    WHERE DifferencePaidAmount = -1;
    --SELECT * FROM @PrescriptionDetail
    --SELECT * FROM @SupplementaryInsurance
    /*--------------------------------------------------------------------------------------- Select Final Result*/
    IF @PrescriptionType = 4
    BEGIN
        INSERT INTO dbo.Tmp_Over_Under_Row_Drug
        (
            PrescriptionId,
            Code_Over_Under,
            Code_Act,
            Kind_Act,
            Price,
            [Percent],
            [Name],
            [Type]
        )
        SELECT FinalCalculated.RowId,
               FinalCalculated.TransactionId,
               10,
               10,
               FinalCalculated.Amount,
               0,
               TR.[Name] TransactionName,
               0
        FROM
        (
            SELECT CAST(D.NonInsuranceAmount AS INT) RowId,
                   D.GoodsCode,
                   G.Name1 DrugName,
                   Amount = CASE Crossed.Counting
                                WHEN 1 THEN
                                    ISNULL(S.Amount, 0)
                                WHEN 2 THEN
                                    SI.Amount
                                ELSE
                                    0
                            END,
                   TransactionId = CASE Crossed.Counting
                                       WHEN 1 THEN
                                           @SupplementaryTransactionId
                                       WHEN 2 THEN
                                           SI.TransactionId
                                       ELSE
                                           0
                                   END
            FROM @PrescriptionDetail D
                JOIN dbo.KalaId G
                    ON G.K_Code = D.GoodsCode
                OUTER APPLY
            (
                SELECT S.SupplementaryAmount Amount
                FROM @SupplementaryInsurance S
                WHERE S.Id = D.Id
            ) AS S
                OUTER APPLY
            (
                SELECT T.TransactionId,
                       ISNULL(T.PatientPaidAmount, 0) + ISNULL(T.DifferencePaidAmount, 0) Amount
                FROM @SupportiveInsuranceTransaction T
                WHERE T.RowId = D.Id
            ) AS SI
                CROSS JOIN
                (SELECT 1 UNION SELECT 2) AS Crossed(Counting)
        ) AS FinalCalculated
            JOIN dbo.Over_Under TR
                ON TR.AutoId = FinalCalculated.TransactionId
        WHERE FinalCalculated.Amount > 0;
        /*--------------------------------------------------------------------------------------- Update Header of Transaction From Detail*/
        UPDATE TransactionInvoice
        SET Price = Summary.Amount
        FROM dbo.Tmp_Over_Under_Factor_Drug TransactionInvoice
            JOIN
            (
                SELECT SUM(Price) Amount,
                       MIN(Code_Over_Under) Code
                FROM dbo.Tmp_Over_Under_Row_Drug
                WHERE PrescriptionId IN
                      (
                          SELECT AutoId
                          FROM dbo.TmpDrugHavaleh
                          WHERE Id_Havaleh = @PyramidPrescriptionTempId
                      )
                      AND
                      (
                          (
                              Kind_Act = 10
                              AND Code_Act = 10
                              AND Code_Over_Under <> ISNULL(@SupplementaryTransactionId, 0)
                          )
                          OR (Code_Over_Under = ISNULL(@IncurableTransactionId, 0))
                      )
                      AND Price > 0
                GROUP BY Code_Over_Under
            ) AS Summary
                ON Summary.Code = TransactionInvoice.Code_Over_Under
                   AND TransactionInvoice.Id_Havaleh = @PyramidPrescriptionTempId;
        /*--------------------------------------------------------------------------------------- Select Result Value*/
        SELECT ISNULL(SUM(D.IncurablePaidAmount), 0) IncurableTotalAmount,
               ROUND(ISNULL(SUM(S.Amount), 0), 0) SupplementaryTotalAmount,
               ISNULL(SUM(SI.Amount), 0) SupportiveInsuranceTotalAmount,
               @SupplementaryTransactionId SupplementaryTransactionId,
               @IncurableTransactionId IncurableTransactionId
        FROM @PrescriptionDetail D
            OUTER APPLY
        (
            SELECT S.SupplementaryAmount Amount
            FROM @SupplementaryInsurance S
            WHERE S.Id = D.Id
        ) AS S
            OUTER APPLY
        (
            SELECT T.TransactionId,
                   ISNULL(T.PatientPaidAmount, 0) + ISNULL(T.DifferencePaidAmount, 0) Amount
            FROM @SupportiveInsuranceTransaction T
                JOIN dbo.GoodsSupportiveCompanies GT
                    ON GT.GoodsCode = D.GoodsCode
            WHERE T.RowId = D.Id
        ) AS SI;
    END;
    ELSE
    BEGIN
        /*-------------------------------------------------------------Calculate On detail */

        UPDATE @PrescriptionDetail
        SET InsuredAmount = CASE
                                WHEN D.StatusCode = 2 THEN
                                    0
                                ELSE
                                    D.InsuredAmount
                            END,
            NonInsuranceAmount = CASE
                                     WHEN D.InsuredPaidAmount - D.DifferenceAmount - D.InsuredAmount < 0
                                          OR D.StatusCode IN ( 1, 2 ) THEN
                                         0
                                     ELSE
                                         D.InsuredPaidAmount - D.DifferenceAmount - D.InsuredAmount
                                 END,
            EquipmentAmount = CASE
                                  WHEN D.StatusCode = 2 THEN
                                      D.InsuredPaidAmount
                                  ELSE
                                      0
                              END,
            GoodsAmount = CASE
                              WHEN D.StatusCode = 1 THEN
                                  D.InsuredPaidAmount
                              ELSE
                                  0
                          END
        FROM @PrescriptionDetail D;

        SELECT D.Id,
               @PrescriptionId PrescriptionId,
               CAST(D.RowIndex AS INT) RowIndex,
               D.IsIncludeSupplementaryInsurance,
               D.GoodsCode,
               CASE
                   WHEN D.GenericCode IS NULL
                        OR D.GenericCode = '' THEN
                       G.Meli_Code
                   ELSE
                       D.GenericCode
               END GenericCode,
               CASE
                   WHEN D.IRCCode IS NULL
                        OR D.IRCCode = '' THEN
                       G.Barcode
                   ELSE
                       D.IRCCode
               END IRCCode,
               G.Name1 DrugName,
               MAX(D.InsuredFranshiz) InsuredFranshiz,
               SUM(D.TotalPrice) TotalPriceAmount,
               CAST(SUM(D.DeliverCount) AS DEC(5, 2)) DeliverCount,
               CAST(MAX(D.CoverCount) AS DEC(5, 0)) CoverCount,
               MAX(D.DifferenceAmount) DifferenceAmount,
               MAX(D.SalesPriceAmount) SalesPriceAmount,
               MAX(D.InsuredAmount) InsuredAmount,
               MAX(D.NonInsuranceAmount) NonInsuranceAmount,
               MAX(D.EquipmentAmount) EquipmentAmount,
               MAX(D.GoodsAmount) GoodsAmount,
               MAX(D.InsuredPaidAmount) InsuredPaidAmount,
               MAX(D.IncurablePaidAmount) IncurablePaidAmount,
               @IncurableTransactionId IncurableTransactionId,
               MAX(S.DifferencePaidAmount) SupplementaryDifferencePaidAmount,
               MAX(S.PatientPaidAmount) SupplementaryPatientPaidAmount,
               MAX(S.GoodsPaidAmount) SupplementaryGoodsPaidAmount,
               MAX(S.NonInsurancePaidAmount) SupplementaryNonInsurancePaidAmount,
               MAX(S.EquipmentPaidAmount) SupplementaryEquipmentPaidAmount,
               MAX(D.StatusCode) DrugStatusCode,
               S.IsSpecialDrug,
               ISNULL(S.MessageBody, '') MessageBody,
               MAX(S.SupplementaryAmount) SupplementaryAmount,
               ISNULL(SI.InsuranceName, '') SupportiveInsuranceName,
               ISNULL(SI.TransactionId, 0) SupportiveInsuranceTransactionId,
               MAX(ISNULL(SI.PatientPaidAmount, 0)) SupportiveInsurancePatientPaidAmount,
               MAX(ISNULL(SI.DifferencePaidAmount, 0)) SupportiveInsuranceDifferencePaidAmount,
               MAX(D.InsuredPaidAmount) - ROUND(MAX(ISNULL(S.SupplementaryAmount, 0)), 0)
               - (MAX(ISNULL(SI.DifferencePaidAmount, 0)) + MAX(ISNULL(SI.PatientPaidAmount, 0)))
               - MAX(ISNULL(D.IncurablePaidAmount, 0)) ExcessAmount,
               MAX(D.DifferenceAmount) - MAX(ISNULL(S.DifferencePaidAmount, 0))
               - MAX(ISNULL(SI.DifferencePaidAmount, 0)) ExcessDifferenceAmount,
               CASE
                   WHEN MAX(D.InsuredAmount) > 0 THEN
                       MAX(D.InsuredAmount) - MAX(ISNULL(D.IncurablePaidAmount, 0))
                       - MAX(ISNULL(S.PatientPaidAmount, 0)) - MAX(ISNULL(SI.PatientPaidAmount, 0))
                   ELSE
                       0
               END ExcessInsuredPaidAmount,
               CASE
                   WHEN MAX(D.GoodsAmount) > 0 THEN
                       MAX(D.GoodsAmount) - MAX(ISNULL(S.GoodsPaidAmount, 0)) - MAX(ISNULL(SI.PatientPaidAmount, 0))
                   ELSE
                       0
               END ExcessGoodsPaidAmount,
               CASE
                   WHEN MAX(D.EquipmentAmount) > 0 THEN
                       MAX(D.EquipmentAmount) - MAX(ISNULL(S.EquipmentPaidAmount, 0))
                       - MAX(ISNULL(SI.PatientPaidAmount, 0))
                   ELSE
                       0
               END ExcessEquipmentPaidAmount,
               CASE
                   WHEN MAX(D.NonInsuranceAmount) > 0 THEN
                       MAX(D.NonInsuranceAmount) - MAX(ISNULL(S.NonInsurancePaidAmount, 0))
                       - MAX(ISNULL(SI.PatientPaidAmount, 0))
                   ELSE
                       0
               END ExcessNonInsurancePaidAmount
        FROM @PrescriptionDetail D
            JOIN dbo.KalaId G
                ON G.K_Code = D.GoodsCode
            OUTER APPLY
        (
            SELECT SUM(S.PatientAmount) PatientAmount,
                   SUM(S.DifferencePaidAmount) DifferencePaidAmount,
                   SUM(S.PatientPaidAmount) PatientPaidAmount,
                   SUM(S.GoodsPaidAmount) GoodsPaidAmount,
                   SUM(S.NonInsurancePaidAmount) NonInsurancePaidAmount,
                   SUM(S.EquipmentPaidAmount) EquipmentPaidAmount,
                   MAX(S.StatusCode) DrugStatusCode,
                   S.IsSpecialDrug,
                   S.MessageBody,
                   SUM(S.SupplementaryAmount) SupplementaryAmount
            FROM @SupplementaryInsurance S
            WHERE S.Id = D.Id
            GROUP BY S.Id,
                     S.GoodsCode,
                     S.IsSpecialDrug,
                     S.MessageBody
        ) AS S /*------------------------ Join to Calculated Supplementary Insurance */
            OUTER APPLY
        (
            SELECT T.InsuranceName,
                   T.TransactionId,
                   ISNULL(T.PatientPaidAmount, 0) PatientPaidAmount,
                   ISNULL(T.DifferencePaidAmount, 0) DifferencePaidAmount
            FROM @SupportiveInsuranceTransaction T
            WHERE T.RowId = D.Id
        ) AS SI /*-------------------- Calculated Supportive Companies Insurance Saved*/
        GROUP BY D.Id,
                 D.IsIncludeSupplementaryInsurance,
                 D.GoodsCode,
                 D.GenericCode,
                 G.Barcode,
                 G.Name1,
                 D.RowIndex,
                 G.Meli_Code,
                 D.IRCCode,
                 S.IsSpecialDrug,
                 S.MessageBody,
                 SI.InsuranceName,
                 SI.TransactionId
        ORDER BY D.RowIndex;
    END;
END;