USP_RESERVATIONREFUNDADD_SAVE

CREATE procedure dbo.USP_RESERVATIONREFUNDADD_SAVE
(
    @ID uniqueidentifier = null output,
    @PAYMENTID uniqueidentifier,
    @REFUNDPAYMENTMETHODCODE tinyint,
    @REFUNDAMOUNT money,
    @CREDITREASONCODEID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @CREDITCARDPAYMENTTRANSACTIONID uniqueidentifier = null output,
    @CREDITPAYMENTID uniqueidentifier = null output,
    @REFUNDOTHERPAYMENTMETHODCODEID uniqueidentifier = null    
)
as
begin
    if @ID is null
        set @ID = newid();

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    declare @CONSTITUENTID uniqueidentifier;
    declare @CURRENTDATETIMEOFFSET datetimeoffset;
    set @CURRENTDATETIMEOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);

    declare @PDACCOUNTSYSTEMID uniqueidentifier;
    declare @ALLOWGLDISTRIBUTIONS bit;
    declare @POSTSTATUSCODE tinyint;
    declare @POSTDATE datetime;

    select
        @PDACCOUNTSYSTEMID = ID,
        @ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS,
        @POSTSTATUSCODE = (case when ALLOWGLDISTRIBUTIONS = 1 then 1 else 3 end)
    from
        dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM()

     if @POSTSTATUSCODE = 3
        set @POSTDATE = null
     else
        set @POSTDATE = @CURRENTDATE

    declare @CURRENCYID uniqueidentifier;
    select @CURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;

    begin try
        if (@PAYMENTID is null)
            raiserror('You must enter a payment to refund.', 13, 1);

        if (@REFUNDAMOUNT <= 0)
            raiserror('The amount refunded must be greater than zero.', 13, 1);    

        --    Payment method codes

        --            <EnumValue ID="0" Translation="Cash"/>

        --            <EnumValue ID="1" Translation="Check"/>

        --            <EnumValue ID="2" Translation="Credit card"/>

        --            <EnumValue ID="3" Translation="Direct debit"/>

        --            <EnumValue ID="4" Translation="Stock"/>

        --            <EnumValue ID="5" Translation="Property"/>

        --            <EnumValue ID="6" Translation="Gift-in-kind"/>

        --            <EnumValue ID="9" Translation="None"/>

        --            <EnumValue ID="10" Translation="Other"/>

        --            <EnumValue ID="11" Translation="Standing order"/>



        if (@REFUNDPAYMENTMETHODCODE in (3,4,5,6,9,11))
            raiserror('This refund method cannot be refunded.', 13, 1);

        declare @PAYMENTMETHODCODE tinyint;
        declare @PAYMENTDATEADDED date;
        declare @AMOUNTLEFTTOREFUND money;
        declare @SALESORDERID uniqueidentifier;
        declare @RESERVATIONNAME nvarchar(100);

        select
            @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
            @PAYMENTDATEADDED = REVENUEPAYMENTMETHOD.DATEADDED,
            @SALESORDERID = SALESORDER.ID,
            @AMOUNTLEFTTOREFUND = TOTALS.OVERAGE,
            @CONSTITUENTID = SALESORDER.CONSTITUENTID,
            @RESERVATIONNAME = RESERVATION.NAME
        from
            dbo.SALESORDERPAYMENT
        inner join
            dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
        inner join
            dbo.SALESORDER on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
        inner join
            dbo.RESERVATION on RESERVATION.ID = SALESORDER.ID
        outer apply 
            dbo.UFN_PAYMENT_AMOUNTS(SALESORDERPAYMENT.PAYMENTID) TOTALS
        where 
            SALESORDERPAYMENT.PAYMENTID = @PAYMENTID

        if @PAYMENTMETHODCODE = 2 begin  -- Credit card

            set @CREDITCARDPAYMENTTRANSACTIONID = dbo.UFN_FINANCIALTRANSACTION_GETBBPSTRANSACTIONID(@PAYMENTID);

            if @CREDITCARDPAYMENTTRANSACTIONID is null
                raiserror('The credit card must be processed to issue a refund.', 16, 1);
        end

        if @AMOUNTLEFTTOREFUND <= 0 or @REFUNDAMOUNT > @AMOUNTLEFTTOREFUND
            raiserror('The amount specified to refund is larger than the payment amount left to refund.', 13, 1);

        if @PAYMENTMETHODCODE <> 2 and @REFUNDPAYMENTMETHODCODE = 2
            raiserror('The refunded payment must be a credit card.', 13, 1);

        -- insert into FT, FTL, CIE, CP

        insert into dbo.FINANCIALTRANSACTION
        (
            ID,
            TRANSACTIONAMOUNT,
            BASEAMOUNT,
            ORGAMOUNT,
            TYPECODE,
            PDACCOUNTSYSTEMID,
            TRANSACTIONCURRENCYID,
            DATE,
            POSTDATE,
            POSTSTATUSCODE, 
            PARENTID,
            APPUSERID,
            CONSTITUENTID,
            DATEADDED,
            DATECHANGED,
            ADDEDBYID,
            CHANGEDBYID
        )
        values
        (
            @ID,
            @REFUNDAMOUNT,
            @REFUNDAMOUNT,
            @REFUNDAMOUNT,
            23, --Refund

            @PDACCOUNTSYSTEMID,
            @CURRENCYID,
            @CURRENTDATETIMEOFFSET,
            @POSTDATE,
            @POSTSTATUSCODE,
            (select REVENUEID from dbo.SALESORDER where ID = @SALESORDERID),
            @CURRENTAPPUSERID,
            @CONSTITUENTID,
            @CURRENTDATE, 
            @CURRENTDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID
        );

        insert into dbo.CREDIT_EXT
        (
            ID,
            SALESORDERID,
            CREDITREASONCODEID,
            DATEADDED,
            DATECHANGED,
            ADDEDBYID,
            CHANGEDBYID
        )
        values 
        (
            @ID,
            @SALESORDERID,
            @CREDITREASONCODEID,
            @CURRENTDATE, 
            @CURRENTDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID
        );

        if @CREDITPAYMENTID is null
            set @CREDITPAYMENTID = newid();

        insert into dbo.CREDITPAYMENT
        (
            ID,
            CREDITID,
            APPUSERID,
            CREDITPAYMENTDATEWITHTIMEOFFSET,
            AMOUNT,
            PAYMENTMETHODCODE,
            OTHERPAYMENTMETHODCODEID,
            REVENUEID,
            REVENUESPLITID,
            REFUNDPROCESSED,
        --  DESCRIPTION,

            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @CREDITPAYMENTID,
            @ID,
            @CURRENTAPPUSERID,
            @CURRENTDATETIMEOFFSET,
            @REFUNDAMOUNT,
            @REFUNDPAYMENTMETHODCODE,
            case when @REFUNDPAYMENTMETHODCODE = 10
                then @REFUNDOTHERPAYMENTMETHODCODEID
            else
                null
            end,
            @PAYMENTID,
            null,
            case when @REFUNDPAYMENTMETHODCODE = 2 
                then 0
            else 
                1
            end,
        --    Descriptive stuff goes here

            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

        if @REFUNDPAYMENTMETHODCODE = 1
        begin
            insert into dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL
            (
                ID,
                DATEADDED,
                DATECHANGED,
                ADDEDBYID,
                CHANGEDBYID
            )
            values
            (
                @CREDITPAYMENTID,
                @CURRENTDATE,
                @CURRENTDATE,
                @CHANGEAGENTID,
                @CHANGEAGENTID
            )
        end


        declare    @FINANCIALTRANSACTIONLINEITEMID uniqueidentifier;
        set @FINANCIALTRANSACTIONLINEITEMID = newid();

        declare @SOURCELINEITEMID uniqueidentifier;
        select 
            top 1 @SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID 
        from 
            dbo.FINANCIALTRANSACTIONLINEITEM
        inner join
            dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        where 
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PAYMENTID 
            and FINANCIALTRANSACTIONLINEITEM.VISIBLE = 1 
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and REVENUESPLIT_EXT.TYPECODE = 19; -- unearned revenue



        -- Add Reservation Name to Description

        insert into dbo.FINANCIALTRANSACTIONLINEITEM
        (
            ID,
            FINANCIALTRANSACTIONID,
            UNITVALUE,
            QUANTITY,
            TRANSACTIONAMOUNT,
            BASEAMOUNT,
            ORGAMOUNT,
            TYPECODE,
            POSTDATE,
            SOURCELINEITEMID,
            DESCRIPTION,
            POSTSTATUSCODE,
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        values
        (
            @FINANCIALTRANSACTIONLINEITEMID,
            @ID,
            @REFUNDAMOUNT,
            1,
            @REFUNDAMOUNT,
            @REFUNDAMOUNT,
            @REFUNDAMOUNT,
            0,  -- Standard

            @POSTDATE,
            @SOURCELINEITEMID,
            @RESERVATIONNAME,
            @POSTSTATUSCODE,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        );

        insert into dbo.CREDITITEM_EXT
        (
            ID,
            CREDITID,
            TYPECODE,
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        values
        (
            @FINANCIALTRANSACTIONLINEITEMID,
            @ID,
            255, -- Unearned revenue

            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        );

        if @ALLOWGLDISTRIBUTIONS = 1
            exec dbo.USP_REFUND_CREATEGLDISTRIBUTION_GROUPSALES @CREDITPAYMENTID, @CHANGEAGENTID, @CURRENTDATE

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

end