USP_RESERVATIONSECURITYDEPOSITPAYMENT_ADD

CREATE procedure dbo.USP_RESERVATIONSECURITYDEPOSITPAYMENT_ADD
(
    @ID uniqueidentifier = null output,
    @SALESORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @AMOUNT money = 0,
    @PAYMENTMETHODCODE tinyint = 2,
    @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
    @CHECKNUMBER nvarchar(20) = '',
    @CARDHOLDERNAME nvarchar(255) = '',
    @CREDITCARDNUMBER nvarchar(4) = '',
    @CREDITTYPECODEID uniqueidentifier = null,
    @AUTHORIZATIONCODE nvarchar(20) = '',
    @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as
    set nocount on;

    if @ID is null
        set @ID = 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)                

    declare @CONSTITUENTID uniqueidentifier
    declare @SECURITYDEPOSITBALANCE money
    declare @SALESMETHODTYPECODE tinyint = 3
    declare @TRANSACTIONDATE datetime
    declare @ALLOWGLDISTRIBUTIONS bit;
    declare @PDACCOUNTSYSTEMID uniqueidentifier;

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

    select @CONSTITUENTID = CONSTITUENTID,
        @SECURITYDEPOSITBALANCE = dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE(@SALESORDERID)
    from dbo.SALESORDER with (nolock)
    where ID = @SALESORDERID;

    begin try
        if @CONSTITUENTID is null begin
            raiserror('BBERR_CONSTITUENTREQUIRED.', 13, 1);
        end

        set @AMOUNT = ROUND(@AMOUNT, 2);

        --validate payments

        if @AMOUNT <= 0 begin
            raiserror('BBERR_AMOUNTREQUIRED.', 13, 1);
        end

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

        declare @CHANGEDUE money;
        declare @AMOUNTTENDERED money;

        set @AMOUNTTENDERED = @AMOUNT;
        set @CHANGEDUE = 0;

        --temporarily removing time stamp when storing the payment revenue record for consistency

        --reference WI 88752

        set @TRANSACTIONDATE = dbo.UFN_DATE_GETEARLIESTTIME(@TRANSACTIONDATE)

        --insert a new payment

        insert into dbo.REVENUE (
            ID,
            CONSTITUENTID,
            DATE,
            POSTDATE,
            AMOUNT,
            TRANSACTIONTYPECODE,
            DONOTPOST,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values (
            @ID,
            @CONSTITUENTID,
            @TRANSACTIONDATE,
            case when @ALLOWGLDISTRIBUTIONS=1 then @TRANSACTIONDATE else null end,
            @AMOUNT,
            0, --Payment

            case when @ALLOWGLDISTRIBUTIONS=1 then 0 else 1 end,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        )

        --Add origination source

        exec dbo.USP_REVENUE_ADDORIGIN @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

        --Add payment original amount    

        exec dbo.USP_PAYMENT_ADDORIGINALAMOUNT @ID, @CHANGEAGENTID, @CURRENTDATE;

        --Add reference

        declare @REFERENCE nvarchar(255);
        select @REFERENCE = 'Payment-SecurityDeposit-' + convert(nvarchar(50),(select SALESORDER.SEQUENCEID from dbo.SALESORDER with (nolock) where SALESORDER.ID = @SALESORDERID)) 
        exec dbo.USP_REVENUEREFERENCE_ADD @ID, @REFERENCE, @CHANGEAGENTID 

        -- link the payment to the sales order

        insert into dbo.RESERVATIONSECURITYDEPOSITPAYMENT with (rowlock)
            (ID, RESERVATIONID, PAYMENTID, AMOUNTTENDERED, AMOUNT, CHANGEDUE, PAYMENTDATEWITHTIMEOFFSET, APPUSERID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (
            newid(), 
            @SALESORDERID, 
            @ID, 
            @AMOUNTTENDERED, 
            @AMOUNT, 
            @CHANGEDUE,
            @CURRENTDATETIMEOFFSET,
            @CURRENTAPPUSERID,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        );

        --add payment method details

        declare @REVENUEPAYMETHODID uniqueidentifier = newid();

        insert into dbo.REVENUEPAYMENTMETHOD 
            (ID,REVENUEID, PAYMENTMETHODCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (
            @REVENUEPAYMETHODID,
            @ID,
            @PAYMENTMETHODCODE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

        -- update the payment information for each revenue record in the transaction

        exec dbo.USP_REVENUE_UPDATEPAYMENTDETAILS @REVENUEPAYMETHODID, @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER, 
                null, '00000000', '', @CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, 
                @AUTHORIZATIONCODE, @EXPIRESON, null, null, null, null, null, null, null, null, null, null, null, 
                @CHANGEAGENTID,@CURRENTDATE, 0, @OTHERPAYMENTMETHODCODEID,@ORIGINALPAYMENTMETHODCODE=@PAYMENTMETHODCODE;

        insert into dbo.REVENUESPLIT(ID, REVENUEID, APPLICATIONCODE, TYPECODE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (newid(), @ID, 10, 13, @AMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        --gl distributions

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

        -- update security deposit status

        if dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE(@ID) <= 0
            update dbo.RESERVATION set SECURITYDEPOSITSTATUSCODE = 1 where ID = @SALESORDERID
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch                

    return 0