USP_ORDERPAYMENT_ADD

CREATE procedure dbo.USP_ORDERPAYMENT_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,
    @ALLOWOVERPAY bit = 0,
    @DONOTRECONCILE bit = 0,
    @VENDORID nvarchar(50) = '',
    @BBPSTRANSACTIONID 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);

    if @BBPSTRANSACTIONID = '00000000-0000-0000-0000-000000000000'
        set @BBPSTRANSACTIONID = null

    declare @ORDERSTATUS tinyint;
    declare @CONSTITUENTID uniqueidentifier;
    declare @ORDERBALANCE money;
    declare @SALESMETHODTYPECODE tinyint;
    declare @TRANSACTIONDATE datetime;
    declare @REVENUEID uniqueidentifier;
    declare @PRICINGCODE tinyint;
    declare @CURRENTTRANSACTIONDATE datetime;
    declare @ALLOWGLDISTRIBUTIONS bit;
    declare @PDACCOUNTSYSTEMID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @REFERENCE nvarchar(255);

    select
        @PDACCOUNTSYSTEMID = PD.ID,
        @ALLOWGLDISTRIBUTIONS = PD.ALLOWGLDISTRIBUTIONS,
        @BASECURRENCYID = CS.BASECURRENCYID 
    from 
        dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM() as PD
    left outer join
        dbo.CURRENCYSET CS on PD.CURRENCYSETID = CS.ID;

    select 
        @ORDERSTATUS = STATUSCODE,
        @CONSTITUENTID = CONSTITUENTID,
        @ORDERBALANCE = dbo.UFN_SALESORDER_GETAMOUNTDUE(@SALESORDERID),
        @SALESMETHODTYPECODE = SALESMETHODTYPECODE,
        @CURRENTTRANSACTIONDATE = TRANSACTIONDATE,
        @REVENUEID = REVENUEID,
        @REFERENCE = 'Payment-Order-' + convert(nvarchar(50),SALESORDER.SEQUENCEID)
    from 
        dbo.SALESORDER with (nolock)
    where 
        ID = @SALESORDERID;

    --- Sponsorship recurring gift payments should use the channel code defined in the web transactions configurations (WI#154664)

    declare @CHANNELCODEID uniqueidentifier = null;

    if exists
    (
        select 
            *
        from 
            dbo.SALESORDERITEMSPONSORSHIP
        inner join 
            dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMSPONSORSHIP.ID
        where 
            SALESORDERITEM.SALESORDERID = @SALESORDERID
    )
    begin
        select top 1 @CHANNELCODEID = CHANNELCODEID from dbo.NETCOMMUNITYDEFAULTCODEMAP
    end

    set @AMOUNT = isnull(round(@AMOUNT, 2), 0);

    begin try
        --Is this payment already in the database?

        if @BBPSTRANSACTIONID is not null begin
            if exists(select 1 from dbo.CREDITCARDPAYMENTMETHODDETAIL where TRANSACTIONID = @BBPSTRANSACTIONID) begin
                raiserror('BBERR_ORDERPAYMENT_DUPLICATEBBPSTRANSACTIONID.', 13, 1);
            end
        end

        if @SALESMETHODTYPECODE = 3 begin -- Group Sales

            select @PRICINGCODE = PRICINGCODE from dbo.RESERVATION where ID = @SALESORDERID;

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

            if @PRICINGCODE = 1 and dbo.UFN_RESERVATIONRATESCALE_ISAPPLIED(@SALESORDERID) = 0 -- Flat rate

                raiserror('BBERR_RESERVATIONNOTAPPLIED', 13, 1);

            -- Raise an error if the reservation is checked in, the payment method is cash, and you're trying to overpay

            if @PAYMENTMETHODCODE = 0 and @ORDERSTATUS = 1 and @AMOUNT > @ORDERBALANCE
                raiserror('BBERR_ORDERPAYMENTADD_COMPLETEDRESERVATIONCASHOVERPAYMENT', 13, 1);
        end
        else if @ALLOWOVERPAY = 0 begin
            -- don't allow overpay unless cash

            if @AMOUNT > @ORDERBALANCE and @PAYMENTMETHODCODE <> 0
                raiserror('BBERR_OVERPAY.', 13, 1);
        end

        if @SALESMETHODTYPECODE in (0,1) begin
            --See if a membership on the order is invalid

            declare @MEMBERSHIPERRORCODE tinyint =  dbo.UFN_SALESORDER_EXISTSINVALIDMEMBERSHIP_CODE(@SALESORDERID)

            --Code 1: No primary member. Doesn't matter here... This won't affect pricing

            if @MEMBERSHIPERRORCODE = 2
                raiserror('BBERR_MEMBERSHIPWITHINACTIVELEVEL', 13, 1);
            else if @MEMBERSHIPERRORCODE = 3
                raiserror('BBERR_MEMBERSHIPWITHINACTIVETERM', 13, 1);
            else if @MEMBERSHIPERRORCODE = 4
                raiserror('BBERR_MEMBERSHIP_INACTIVELEVELANDTERM', 13, 1);
        end

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

        --The date for all payments needs to be the current date

        set @TRANSACTIONDATE = @CURRENTDATETIMEOFFSET;

        --but we only want to update the sales order date if it doesn't already

        --have a transaction date

        if @CURRENTTRANSACTIONDATE is null begin
            update dbo.SALESORDER set 
                    TRANSACTIONDATE = @TRANSACTIONDATE,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
            where
                ID = @SALESORDERID;
        end

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

        --reference WI 88752

        set @TRANSACTIONDATE = cast(@TRANSACTIONDATE as date);

        declare @SALESORDERPAYMENTID uniqueidentifier = null;
        select top 1
            @SALESORDERPAYMENTID = [SALESORDERPAYMENT].[ID]
        from
            dbo.[SALESORDERPAYMENT]
        inner join 
            dbo.[REVENUEPAYMENTMETHOD] on [REVENUEPAYMENTMETHOD].[REVENUEID] = [SALESORDERPAYMENT].[PAYMENTID]
        where 
            [SALESORDERPAYMENT].[SALESORDERID] = @SALESORDERID and
            [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 0 --Cash


        -- if we already have a cash payment, just update it

        -- SALESMETHODTYPECODE = 3 is group sales

        if @PAYMENTMETHODCODE = 0 and @SALESORDERPAYMENTID is not null and @SALESMETHODTYPECODE <> 3 begin
            exec dbo.USP_ORDERPAYMENT_UPDATECASHPAYMENTS @SALESORDERID, @CHANGEAGENTID, @AMOUNT, @CURRENTDATE, @CURRENTDATETIMEOFFSET
        end
        else begin
            declare @CHANGEDUE money;
            declare @AMOUNTTENDERED money;

            set @AMOUNTTENDERED = @AMOUNT;

            set @CHANGEDUE = 0;

            -- Cash

            -- SALESMETHODTYPECODE of 3 is group sales

            -- ORDERSTATUS of 1 is complete

            if @PAYMENTMETHODCODE = 0 and (@SALESMETHODTYPECODE <> 3 or @ORDERSTATUS = 1) begin
                if @AMOUNT > @ORDERBALANCE begin
                    set @CHANGEDUE = @AMOUNT - @ORDERBALANCE;
                    set @AMOUNT = @ORDERBALANCE;
                end
            end

            --insert a new payment

            insert into dbo.FINANCIALTRANSACTION (
                ID,
                CONSTITUENTID,
                TYPECODE,
                TRANSACTIONAMOUNT,
                BASEAMOUNT,
                ORGAMOUNT,
                DATE,
                TRANSACTIONCURRENCYID,
                BASEEXCHANGERATEID,
                ORGEXCHANGERATEID,
                PDACCOUNTSYSTEMID,
                POSTDATE,
                POSTSTATUSCODE,
                USERDEFINEDID,
                -- Boilerplate

                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
            (
                @ID,
                @CONSTITUENTID,
                0, --Payment

                @AMOUNT,
                @AMOUNT,
                @AMOUNT,
                @TRANSACTIONDATE,
                @BASECURRENCYID,
                null,
                null,
                @PDACCOUNTSYSTEMID,
                case 
                    when @ALLOWGLDISTRIBUTIONS = 1 
                    then 
                        @TRANSACTIONDATE 
                    else 
                        null 
                end,
                case 
                    when @ALLOWGLDISTRIBUTIONS = 1 then 
                        1 -- Not Posted

                    else 
                        3 -- Do Not Post

                end,
                '',
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            );

            insert into dbo.REVENUE_EXT 
            (
                ID,
                BATCHNUMBER,
                DONOTRECEIPT,
                RECEIPTAMOUNT,
                SOURCECODE,
                FINDERNUMBER,
                APPEALID,
                MAILINGID,
                CHANNELCODEID,
                GIVENANONYMOUSLY,
                DONOTACKNOWLEDGE,
                BENEFITSWAIVED,
                RECEIPTTYPECODE,
                NEEDSRERECEIPT,
                ELIGIBLEFORMATCHINGGIFTCLAIM,
                ISREIMBURSABLE,
                REFERENCE,
                NONPOSTABLEBASECURRENCYID,
                -- Boilerplate

                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            values
            (
                @ID,
                '',
                0,
                0,
                '',
                0,
                null,
                null,
                @CHANNELCODEID,
                0,
                0,
                0,
                0,
                0,
                0,
                0,
                isnull(@REFERENCE, ''),
                null,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            );

            --Add BBIS origin information if it exists

            declare @SPONSORSHIPTRANDATA xml;

            select @SPONSORSHIPTRANDATA = DATA from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 12 -- Sponsorship sales order item type


            if @SPONSORSHIPTRANDATA is not null begin
                declare @BBNCPAGENAME nvarchar(100);
                declare @BBNCPAGEID int;

                with xmlnamespaces('urn:blackbaud.RE7.XDATA' as bb)
                select @BBNCPAGENAME = T.c.value('bb:PageName[1]','nvarchar(100)'),
                @BBNCPAGEID = T.c.value('bb:PageID[1]','int')
                from @SPONSORSHIPTRANDATA.nodes('/SponsorshipTransactionData/OriginInformation') T(c)

                if isnull(@BBNCPAGENAME,'') <> '' and isnull(@BBNCPAGEID,0) <> 0 begin
                    insert into dbo.REVENUEBBNC (
                        ID, 
                        NETCOMMUNITYPAGENAME, 
                        NETCOMMUNITYPAGEID, 
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    values (
                        @ID, 
                        @BBNCPAGENAME, 
                        @BBNCPAGEID, 
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                    );
                end
            end

            --Add origination source

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

            if not exists (select * from dbo.PAYMENTORIGINALAMOUNT where ID = @ID) begin
                insert into dbo.PAYMENTORIGINALAMOUNT
                (
                    ID, 
                    TRANSACTIONAMOUNT, 
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                values 
                (
                    @ID, 
                    @AMOUNT, 
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                )
            end

            -- link the payment to the sales order

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

            --add payment method details

            declare @REVENUEPAYMETHODID uniqueidentifier
            set @REVENUEPAYMETHODID = 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,
                    @VENDORID=@VENDORID,@BBPSTRANSACTIONID=@BBPSTRANSACTIONID;
        end --if @PAYMENTMETHODCODE = 0 (which is cash)


        if @ORDERSTATUS = 1 begin  -- Completed Order

            --order is complete, so create payment splits

            exec dbo.USP_SALESORDER_ADDPAYMENTREVENUE @SALESORDERID, @REVENUEID, @CHANGEAGENTID, @CURRENTDATE, @ID
        end

        if @SALESMETHODTYPECODE = 3 begin -- Group Sales

            --group sales reservation, progress status

            exec dbo.USP_RESERVATION_UPDATESTATUSANDHISTORY @SALESORDERID, @CHANGEAGENTID

            if @ORDERSTATUS <> 1 begin -- Not Complete

                --create the application for the deposit

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

                insert into dbo.FINANCIALTRANSACTIONLINEITEM (
                    ID,
                    FINANCIALTRANSACTIONID,
                    TYPECODE,
                    BASEAMOUNT,
                    TRANSACTIONAMOUNT,
                    ORGAMOUNT,
                    POSTDATE,
                    POSTSTATUSCODE,
                    SOURCELINEITEMID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                ) 
                values (
                    @SPLITID,
                    @ID,
                    0,  -- Standard

                    @AMOUNT,
                    @AMOUNT,
                    @AMOUNT,
                    case
                        when @ALLOWGLDISTRIBUTIONS = 1 then @TRANSACTIONDATE
                        else null
                    end,
                    case
                        when @ALLOWGLDISTRIBUTIONS = 1 then 1 -- Not Posted

                        else 3 -- Do Not Post

                    end,
                    null,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                );

                insert into dbo.REVENUESPLIT_EXT (
                    ID,
                    APPLICATIONCODE,
                    TYPECODE,
                    DESIGNATIONID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                values (
                    @SPLITID,
                    10,  -- Order

                    19,  -- Unearned revenue

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

                --gl distributions

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

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0;