USE [LegitimateKZN_QA_SO]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [App].[Fn_IsReceiptRequired] 
(
    @ApplicationRequestDetailID BIGINT,
    @ApplicationRequestTypeID INT
)
RETURNS BIT
AS
BEGIN
    DECLARE @IsRequired BIT = 0;
    DECLARE @OriginalReceiptCost MONEY;
    DECLARE @CorrectReceiptCost MONEY;
    DECLARE @NewReceiptCost MONEY;
    DECLARE @OldApplicationTypeID BIGINT;

    SELECT @CorrectReceiptCost = App.Fn_GetReceiptCost(
                                        @ApplicationRequestDetailID, 
                                        @ApplicationRequestTypeID,
                                        Ard.IsRenewal,
                                        Ard.IsLtpsConversion,
                                        Ard.VehicleCount)
    FROM App.ApplicationRequestDetails Ard
    WHERE ApplicationRequestDetailID = @ApplicationRequestDetailID;
	
    SELECT @OriginalReceiptCost = 
    (
        SELECT SUM(Rd.TotalCost)
        FROM App.ReceiptDetails Rd
        WHERE Rd.ApplicationRequestDetailID = @ApplicationRequestDetailID
    )
    FROM App.ApplicationRequestDetails Ard
    INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
    WHERE Ard.ApplicationRequestDetailID = @ApplicationRequestDetailID;

    SET @OriginalReceiptCost = ISNULL(@OriginalReceiptCost, 0); 
		
    -- change as per SUP4549. Allowing all apps to be receipted again
    -- If the correct cost is higher than what has already been receipted
    -- (excluding Data Purification) and it is after the price change boundary,
    -- then a receipt is required.
    IF @ApplicationRequestTypeID NOT IN (12 /*Data Purification*/) 
        AND @CorrectReceiptCost > @OriginalReceiptCost
        AND @ApplicationRequestDetailID > 179377 -- last app on 5 Aug 2024 before price change
    BEGIN
        SET @IsRequired = 1;
    END
    ELSE
    BEGIN
        -- COV ZN R0 corner case removed: COV ZN changes are now charged normally.
        SET @IsRequired = 0;
    END;

    RETURN @IsRequired;
END;
GO


