USP_RESERVATIONSECURITYDEPOSITCREDIT_ADDREFUND

CREATE procedure dbo.USP_RESERVATIONSECURITYDEPOSITCREDIT_ADDREFUND(
  @ID uniqueidentifier = null output,
  @CREDITPAYMENTID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @RESERVATIONID uniqueidentifier,
  @AMOUNTELIGIBLE money = null,
  @WITHHOLDREASONCODEID uniqueidentifier = null,
  @AMOUNTWITHHELD money = null,
  @REFUNDAMOUNT money = null,
  @REFUNDMETHODID uniqueidentifier = null,
  @COMMENT nvarchar(max) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
    @TRANSACTIONDATE datetime = null
)
as

set nocount on;

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

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

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

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

if @TRANSACTIONDATE is null
    set @TRANSACTIONDATE =  dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

begin try

    -- Withhold the whole security deposit, just change the status

    if (@WITHHOLDREASONCODEID is not null) and @AMOUNTWITHHELD = @AMOUNTELIGIBLE
    begin
        if exists (select * from dbo.RESERVATION where RESERVATION.ID = @RESERVATIONID and SECURITYDEPOSITSTATUSCODE = 1)
            update dbo.RESERVATION 
            set SECURITYDEPOSITSTATUSCODE = 3, 
                SECURITYDEPOSITWITHHOLDREASONCODEID = @WITHHOLDREASONCODEID, 
                DATECHANGED = @CURRENTDATE, 
                CHANGEDBYID = @CHANGEAGENTID
            where RESERVATION.ID = @RESERVATIONID;
        return 0;
    end

    -- If refund by credit card, validate amount

    if @REFUNDMETHODID <> '00000000-0000-0000-0000-000000000001'
    begin
        declare @PAYMENTAMOUNT money
        select @PAYMENTAMOUNT = FINANCIALTRANSACTION.BASEAMOUNT 
            from dbo.FINANCIALTRANSACTION 
            where FINANCIALTRANSACTION.ID = @REFUNDMETHODID
        if @REFUNDAMOUNT > @PAYMENTAMOUNT
        begin
            raiserror('ERR_RESERVATIONSECURITYDEPOSITCREDIT_INVALIDREFUNDAMOUNT', 13, 1);
            return 1;
        end
    end

    declare @REVENUEID uniqueidentifier
    if @REFUNDMETHODID = '00000000-0000-0000-0000-000000000001'
        select @REVENUEID = PAYMENTID
        from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
        where RESERVATIONID = @RESERVATIONID
    else
        set @REVENUEID = @REFUNDMETHODID

    declare @REVENUESPLITID uniqueidentifier
    select @REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
    from dbo.FINANCIALTRANSACTIONLINEITEM 
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID

    if @REFUNDAMOUNT > 0
    begin
        -- Refund

        declare @CONSTITUENTID uniqueidentifier
        select @CONSTITUENTID = CONSTITUENTID from dbo.SALESORDER where ID = @RESERVATIONID

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

        declare @PDACCOUNTSYSTEMID uniqueidentifier;
        declare @ALLOWGLDISTRIBUTIONS bit;

        select
            @PDACCOUNTSYSTEMID = ID,
            @ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS
        from
            dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();

        insert into dbo.FINANCIALTRANSACTION(
            ID
            ,TRANSACTIONAMOUNT
            ,BASEAMOUNT
            ,ORGAMOUNT
            ,TRANSACTIONCURRENCYID
            ,TYPECODE
            ,PDACCOUNTSYSTEMID
            ,DESCRIPTION
            ,DATE
            ,POSTDATE
            ,POSTSTATUSCODE
            ,PARENTID
            ,APPUSERID
            ,CONSTITUENTID
            ---- Boilerplate

            ,DATEADDED ,DATECHANGED ,ADDEDBYID ,CHANGEDBYID)
        values(
            @ID
            ,@REFUNDAMOUNT
            ,@REFUNDAMOUNT
            ,@REFUNDAMOUNT
            ,@CURRENCYID
            ,23 --Refund

            ,@PDACCOUNTSYSTEMID
            ,@COMMENT
            ,@TRANSACTIONDATE
            ,case when @ALLOWGLDISTRIBUTIONS = 1 then cast(@TRANSACTIONDATE as date) else null end
            ,case when @ALLOWGLDISTRIBUTIONS = 1 then 1 else 3 end
            ,(select REVENUEID from dbo.SALESORDER where ID = @RESERVATIONID)
            ,@CURRENTAPPUSERID
            ,@CONSTITUENTID
            ,@CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID);

        insert into dbo.CREDIT_EXT(
            ID
            ,SALESORDERID
            ,CREDITREASONCODEID
            ---- Boilerplate

            ,DATEADDED ,DATECHANGED ,ADDEDBYID ,CHANGEDBYID)
        values (
            @ID
            ,@RESERVATIONID
            ,null
            ,@CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID);

        insert into dbo.CREDITPAYMENT
        (
            ID,
            CREDITID,
            APPUSERID,
            CREDITPAYMENTDATEWITHTIMEOFFSET,
            AMOUNT, 
            PAYMENTMETHODCODE,
            OTHERPAYMENTMETHODCODEID,
            REVENUEID,
            REVENUESPLITID,
            REFUNDPROCESSED,
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        ) values (
            @CREDITPAYMENTID,
            @ID,
            @CURRENTAPPUSERID,
            @CURRENTDATETIMEOFFSET,
            @REFUNDAMOUNT,
            case @REFUNDMETHODID 
                when '00000000-0000-0000-0000-000000000001' then 1
                else 2
            end,
            null,
            @REVENUEID,
            @REVENUESPLITID,
            case @REFUNDMETHODID 
                when '00000000-0000-0000-0000-000000000001' then 1
                else 0
            end,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )

      if @REFUNDMETHODID = '00000000-0000-0000-0000-000000000001'
        insert into dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL(
          ID,
          ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED
        )
        select @CREDITPAYMENTID,
                      @CHANGEAGENTID, 
                      @CHANGEAGENTID, 
                      @CURRENTDATE, 
                      @CURRENTDATE;

        declare @CREDITITEMID uniqueidentifier = NEWID();

        insert into dbo.FINANCIALTRANSACTIONLINEITEM(
            ID
            ,FINANCIALTRANSACTIONID
            ,UNITVALUE
            ,QUANTITY
            ,DESCRIPTION
            ,TRANSACTIONAMOUNT
            ,BASEAMOUNT
            ,ORGAMOUNT
            ,POSTDATE
            ,POSTSTATUSCODE
            ,TYPECODE
            ,SOURCELINEITEMID
            ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (
            @CREDITITEMID
            ,@ID
            ,@REFUNDAMOUNT
            ,1
            ,'Security deposit'
            ,@REFUNDAMOUNT
            ,@REFUNDAMOUNT
            ,@REFUNDAMOUNT
            ,case when @ALLOWGLDISTRIBUTIONS = 1 then cast(@TRANSACTIONDATE as date) else null end
            ,case when @ALLOWGLDISTRIBUTIONS = 1 then 1 else 3 end
            ,0
            ,@REVENUESPLITID
            ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

        insert into dbo.CREDITITEM_EXT(
            ID
            ,CREDITID
            ,DISCOUNTS
            ,FEES
            ,GROUPID
            ,GROUPTYPECODE
            ,SALESORDERITEMID
            ,TYPECODE
            ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (
            @CREDITITEMID
            ,@ID
            ,0
            ,0
            ,null
            ,0
            ,null
            ,12
            ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

    end

    --create GL Distributions

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

    -- Update security deposit status

    if @REFUNDAMOUNT = @AMOUNTELIGIBLE
        update dbo.RESERVATION 
        set SECURITYDEPOSITSTATUSCODE = 2, 
            DATECHANGED = @CURRENTDATE, 
            CHANGEDBYID = @CHANGEAGENTID
        where RESERVATION.ID = @RESERVATIONID

    else
        update dbo.RESERVATION 
        set SECURITYDEPOSITSTATUSCODE = 4, 
            SECURITYDEPOSITWITHHOLDREASONCODEID = @WITHHOLDREASONCODEID, 
            DATECHANGED = @CURRENTDATE, 
            CHANGEDBYID = @CHANGEAGENTID
        where RESERVATION.ID = @RESERVATIONID

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0;