USP_GIFTINKINDAPPRAISAL_DELETE

CREATE procedure dbo.USP_GIFTINKINDAPPRAISAL_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as begin

    declare @USEAPPRAISALTHRESHOLD bit;
    declare @APPRAISALTHRESHOLD money;

    select top 1
        @USEAPPRAISALTHRESHOLD = USEAPPRAISALTHRESHOLD,
        @APPRAISALTHRESHOLD = APPRAISALTHRESHOLD
    from
        dbo.GIFTINKINDAPPRAISALSETTINGS

    if @USEAPPRAISALTHRESHOLD = 1
    begin

        --Check that the threshold has been exceeded and a sale exists for the gift-in-kind appraisal

        if exists 
        (
            select 
                GIFTINKINDPAYMENTMETHODDETAIL.ID 
            from 
                dbo.GIFTINKINDPAYMENTMETHODDETAIL 
            inner join 
                dbo.GIFTINKINDSALE_EXT GIFTINKINDSALE on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
            inner join
                dbo.FINANCIALTRANSACTION FT on GIFTINKINDSALE.ID = FT.ID
            inner join
                dbo.GIFTINKINDAPPRAISAL on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDAPPRAISAL.GIFTINKINDPAYMENTMETHODDETAILID
            where 
                GIFTINKINDAPPRAISAL.ID = @ID
            and
                GIFTINKINDPAYMENTMETHODDETAIL.ORGANIZATIONFAIRMARKETVALUE >  @APPRAISALTHRESHOLD
            and
                GIFTINKINDPAYMENTMETHODDETAIL.DISPOSITIONCODE = 1
            and
                FT.DELETEDON is null
        )
        begin
            raiserror('GIFTINKINDAPPRAISALREQUIREDWITHSALE', 13, 1);
            return 1;
        end

    end

    exec USP_GIFTINKINDAPPRAISAL_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
    return 0;

end