CREATE PROC [dbo].[EPMMakePrescriptionDetailTransaction]
    @PrescriptionKind TINYINT,
    @PrescriptionTempId DECIMAL(16, 0),
    @PrescriptionId UNIQUEIDENTIFIER
AS
BEGIN

    DECLARE @SumOfSupplementaryDetails DEC(16, 0) = 0;
    DECLARE @SupplementaryTransactionId SMALLINT = 0;
    DECLARE @SupplementaryTotalAmount DECIMAL(16, 0) = 0;
    DECLARE @IncurableTotalAmount DECIMAL(16, 0) = 0;
    DECLARE @SupportiveInsuranceTotalAmount DECIMAL(16, 0) = 0;
    DECLARE @PrescriptionTransactionId UNIQUEIDENTIFIER;
    DECLARE @RASAClaimNumber VARCHAR(15);

    SELECT @PrescriptionTransactionId = Id,
           @IncurableTotalAmount = IncurableTotalAmount,
           @SupportiveInsuranceTotalAmount = SupportiveInsuranceTotalAmount,
           @SupplementaryTotalAmount = SupplementaryInsuranceTotalAmount,
           @RASAClaimNumber = RASAClaimNumber
    FROM dbo.EPMPrescriptionTransaction
    WHERE PrescriptionId = @PrescriptionId;

    DELETE T
    FROM dbo.Tmp_Over_Under_Row_Drug T
        JOIN dbo.TmpDrugHavaleh D
            ON D.AutoId = T.PrescriptionId
               AND D.Id_Havaleh = @PrescriptionTempId;

    DELETE FROM dbo.Tmp_Over_Under_Factor_Drug
    WHERE Id_Havaleh = @PrescriptionTempId;

    IF (@PrescriptionKind = 0) /*----------------------------------------------------------------------------  Make Tamin */
    BEGIN
        PRINT 'Make Tamin';
        INSERT INTO dbo.Tmp_Over_Under_Row_Drug
        (
            PrescriptionId,
            Code_Over_Under,
            Code_Act,
            Kind_Act,
            Price,
            [Percent],
            [Type],
            [Name]
        )
        SELECT MAX(TMP.AutoId),
               TD.TransactionId,
               10,
               10,
               MAX(TD.Amount) Amount,
               0,
               '0',
               TR.[Name]
        FROM dbo.EPMPrescriptionTransactionDetail TD
            JOIN dbo.TaminPrescriptionDetail PD
                ON PD.Id = TD.PrescriptionDetailId
            JOIN dbo.TmpDrugHavaleh TMP
                ON TMP.K_Code = PD.GoodsCode
                   AND TMP.Id_Havaleh = @PrescriptionTempId
            JOIN dbo.Over_Under TR
                ON TR.AutoId = TD.TransactionId
        WHERE TD.PrescriptionTransactionId = @PrescriptionTransactionId
        GROUP BY TD.TransactionId,
                 TR.Name,
                 PD.GoodsCode,
                 TD.TransactionType;
    END;
    ELSE IF (@PrescriptionKind = 1) /*----------------------------------------------------------------------------  Make IHIO */
    BEGIN
        PRINT 'Make IHIO';
        INSERT INTO dbo.Tmp_Over_Under_Row_Drug
        (
            PrescriptionId,
            Code_Over_Under,
            Code_Act,
            Kind_Act,
            Price,
            [Percent],
            [Type],
            [Name]
        )
        SELECT MAX(TMP.AutoId),
               TD.TransactionId,
               10,
               10,
               MAX(TD.Amount) Amount,
               0,
               '0',
               TR.[Name]
        FROM dbo.EPMPrescriptionTransactionDetail TD
            JOIN dbo.IHIOPrescriptionDetail PD
                ON PD.Id = TD.PrescriptionDetailId
            JOIN dbo.TmpDrugHavaleh TMP
                ON TMP.K_Code = PD.GoodsCode
                   AND TMP.Id_Havaleh = @PrescriptionTempId
            JOIN dbo.Over_Under TR
                ON TR.AutoId = TD.TransactionId
        WHERE TD.PrescriptionTransactionId = @PrescriptionTransactionId
        GROUP BY TD.TransactionId,
                 TR.Name,
                 PD.GoodsCode,
                 TD.TransactionType;
    END;
    IF (@PrescriptionKind = 3) /*----------------------------------------------------------------------------  Make TTAC */
    BEGIN
        PRINT 'Make TTAC';
        INSERT INTO dbo.Tmp_Over_Under_Row_Drug
        (
            PrescriptionId,
            Code_Over_Under,
            Code_Act,
            Kind_Act,
            Price,
            [Percent],
            [Type],
            [Name]
        )
        SELECT MAX(TMP.AutoId),
               TD.TransactionId,
               10,
               10,
               MAX(TD.Amount) Amount,
               0,
               '0',
               TR.[Name]
        FROM dbo.EPMPrescriptionTransactionDetail TD
            JOIN dbo.TTACPrescriptionDetail PD
                ON PD.Id = TD.PrescriptionDetailId
            JOIN dbo.TmpDrugHavaleh TMP
                ON TMP.K_Code = PD.GoodsCode
                   AND TMP.Id_Havaleh = @PrescriptionTempId
            JOIN dbo.Over_Under TR
                ON TR.AutoId = TD.TransactionId
        WHERE TD.PrescriptionTransactionId = @PrescriptionTransactionId
        GROUP BY TD.TransactionId,
                 TR.Name,
                 PD.GoodsCode,
                 TD.TransactionType;
    END;
    /*----------------------------------------------------------------------------  Make Transaction Header ( Insert Sum of Details )*/

    INSERT INTO dbo.Tmp_Over_Under_Factor_Drug
    (
        Id_Havaleh,
        Name,
        Code_Over_Under,
        Type,
        Price,
        Act_Code,
        Guy_Code,
        Max_Price,
        Max_percent,
        ShowPrice,
        IsDuty
    )
    SELECT @PrescriptionTempId,
           TR.Name,
           TR.Code_Over_Under,
           '0',
           SUM(TR.Price),
           -1,
           3,
           0,
           0,
           0,
           0
    FROM dbo.Tmp_Over_Under_Row_Drug TR
        JOIN dbo.TmpDrugHavaleh D
            ON D.AutoId = TR.PrescriptionId
               AND D.Id_Havaleh = @PrescriptionTempId
    GROUP BY TR.Code_Over_Under,
             TR.Name;

    /*----------------------------------------------------------------------------  Make Technical Amount Differ (Detail with Header) for Supllimentary insurance*/
    SELECT @SumOfSupplementaryDetails = SUM(Amount),
           @SupplementaryTransactionId = MIN(TransactionId)
    FROM dbo.EPMPrescriptionTransactionDetail
    WHERE PrescriptionTransactionId = @PrescriptionTransactionId
          AND TransactionType = 2;

    IF (ISNULL(@SumOfSupplementaryDetails, 0) < ISNULL(@SupplementaryTotalAmount, 0))
    BEGIN
        PRINT 'Contain Differ Amount';
        UPDATE dbo.Tmp_Over_Under_Factor_Drug
        SET Price = Price + (@SupplementaryTotalAmount - @SumOfSupplementaryDetails)
        WHERE Id_Havaleh = @PrescriptionTempId
              AND Code_Over_Under = @SupplementaryTransactionId;
    END;


    SELECT @PrescriptionId,
           @SupplementaryTransactionId,
           @SupplementaryTotalAmount,
           @SupportiveInsuranceTotalAmount,
           @IncurableTotalAmount,
           @RASAClaimNumber;
END;