USP_PAYMENT_ADDBASE

Stored procedure to add payment base information.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN
@CONSTITUENTID	uniqueidentifier	IN
@DATE	datetime	IN
@AMOUNT	money	IN
@PAYMENTMETHODCODE	tinyint	IN
@CHECKDATE	UDT_FUZZYDATE	IN
@CHECKNUMBER	nvarchar(20)	IN
@REFERENCEDATE	UDT_FUZZYDATE	IN
@REFERENCENUMBER	nvarchar(20)	IN
@CARDHOLDERNAME	nvarchar(255)	IN
@CREDITCARDNUMBER	nvarchar(4)	IN
@CREDITTYPECODEID	uniqueidentifier	IN
@AUTHORIZATIONCODE	nvarchar(20)	IN
@EXPIRESON	UDT_FUZZYDATE	IN
@ISSUER	nvarchar(100)	IN
@NUMBEROFUNITS	decimal(20, 3)	IN
@SYMBOL	nvarchar(25)	IN
@MEDIANPRICE	decimal(19, 4)	IN
@GIFTINKINDSUBTYPECODEID	uniqueidentifier	IN
@PROPERTYSUBTYPECODEID	uniqueidentifier	IN
@RECEIPTAMOUNT	money	IN
@CONSTITUENTACCOUNTID	uniqueidentifier	IN
@POSTSTATUSCODE	tinyint	IN
@POSTDATE	datetime	IN
@FINDERNUMBER	bigint	IN
@SOURCECODE	nvarchar(50)	IN
@APPEALID	uniqueidentifier	IN
@BENEFITS	xml	IN
@BENEFITSWAIVED	bit	IN
@GIVENANONYMOUSLY	bit	IN
@MAILINGID	uniqueidentifier	IN
@CHANNELCODEID	uniqueidentifier	IN
@DONOTACKNOWLEDGE	bit	IN
@DONOTRECEIPT	bit	IN
@BATCHNUMBER	nvarchar(100)	IN
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	IN
@REFERENCE	nvarchar(255)	IN
@TRIBUTEID	uniqueidentifier	IN
@LETTERCODEID	uniqueidentifier	IN
@DIRECTDEBITRESULTCODE	nvarchar(10)	IN
@LOWPRICE	decimal(19, 4)	IN
@HIGHPRICE	decimal(19, 4)	IN
@GIFTINKINDITEMNAME	nvarchar(100)	IN
@GIFTINKINDDISPOSITIONCODE	tinyint	IN
@GIFTINKINDNUMBEROFUNITS	int	IN
@GIFTINKINDFAIRMARKETVALUE	money	IN
@PERCENTAGEBENEFITS	xml	IN
@TRANSACTIONCURRENCYID	uniqueidentifier	IN
@BASECURRENCYID	uniqueidentifier	IN
@BASEEXCHANGERATEID	uniqueidentifier	INOUT
@EXCHANGERATE	decimal(20, 8)	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@SEPAMANDATEID	uniqueidentifier	IN
@TRANSACTIONID	uniqueidentifier	IN
@MERCHANTACCOUNTID	uniqueidentifier	IN
@VENDORID	nvarchar(50)	IN
Definition

Copy


            CREATE procedure dbo.USP_PAYMENT_ADDBASE
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTDATE datetime,
                @CONSTITUENTID uniqueidentifier,
                @DATE datetime,
                @AMOUNT money = 0,
                @PAYMENTMETHODCODE tinyint = 1,
                @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
                @CHECKNUMBER nvarchar(20) = '',
                @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
                @REFERENCENUMBER nvarchar(20) = '',
                @CARDHOLDERNAME nvarchar(255) = '',
                @CREDITCARDNUMBER nvarchar(4) = '',
                @CREDITTYPECODEID uniqueidentifier = null,
                @AUTHORIZATIONCODE nvarchar(20) = '',
                @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
                @ISSUER nvarchar(100) = '',
                @NUMBEROFUNITS decimal(20,3) = 0,
                @SYMBOL nvarchar(25) = '',
                @MEDIANPRICE decimal(19,4) = 0,
                @GIFTINKINDSUBTYPECODEID uniqueidentifier = null,
                @PROPERTYSUBTYPECODEID uniqueidentifier = null,
                @RECEIPTAMOUNT money = 0,
                @CONSTITUENTACCOUNTID uniqueidentifier = null,
                @POSTSTATUSCODE tinyint = 1,
                @POSTDATE datetime = null,
                @FINDERNUMBER bigint = null,
                @SOURCECODE nvarchar(50) = null,
                @APPEALID uniqueidentifier = null,
                @BENEFITS xml = null,
                @BENEFITSWAIVED bit = 0,
                @GIVENANONYMOUSLY bit = null,
                @MAILINGID uniqueidentifier = null,
                @CHANNELCODEID uniqueidentifier = null,
                @DONOTACKNOWLEDGE bit = 0,
                @DONOTRECEIPT bit = 0,
                @BATCHNUMBER nvarchar(100) = '',
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
                @REFERENCE nvarchar(255) = null,
                @TRIBUTEID uniqueidentifier = null,
                @LETTERCODEID uniqueidentifier = null,
                @DIRECTDEBITRESULTCODE nvarchar(10) = '',
                @LOWPRICE decimal(19,4) = 0,
                @HIGHPRICE decimal(19,4) = 0,
                @GIFTINKINDITEMNAME nvarchar(100) = '',
                @GIFTINKINDDISPOSITIONCODE tinyint = 0,
                @GIFTINKINDNUMBEROFUNITS int = 0,
                @GIFTINKINDFAIRMARKETVALUE money = 0,
                @PERCENTAGEBENEFITS xml = null,
                @TRANSACTIONCURRENCYID uniqueidentifier = null,
                @BASECURRENCYID uniqueidentifier = null,
                @BASEEXCHANGERATEID uniqueidentifier = null output,
                @EXCHANGERATE decimal(20,8) = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SEPAMANDATEID uniqueidentifier = null,
                @TRANSACTIONID uniqueidentifier = null,
                @MERCHANTACCOUNTID uniqueidentifier = null,
                @VENDORID nvarchar(50) = ''
            )
            as
            set nocount on;
            declare @DESIGNATIONS xml;

            declare @TYPECODE tinyint;

            declare @SUM money;
            declare @APPLIEDTOPLEDGES money;
            declare @SPLITCOUNT int;

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

            set @TYPECODE = 0;

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

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

            --Set currency parameters for backwards compatibility

            if @TRANSACTIONCURRENCYID is null
                set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

            if @BASECURRENCYID is null
                set @BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

            set @APPLIEDTOPLEDGES = 0;
            set @SPLITCOUNT = 0;
            set @VENDORID = coalesce(@VENDORID, '');

            begin try

                --Multicurrency - RobertDi 6/3/10 - Add a spot exchange rate if an existing rate hasn't

                --been selected, the base and transaction currencies are different, and the rate

                --entered isn't zero (which indicates that the user wants to enter the record without a rate).

                if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
                begin
                    set @BASEEXCHANGERATEID = newid()

                    --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

                    /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
                        and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                    begin
                        raiserror('User does not have the right to add a new spot rate.', 13, 1);
                        return 1;
                    end*/

                    insert into dbo.CURRENCYEXCHANGERATE(
                        ID, 
                        FROMCURRENCYID,
                        TOCURRENCYID,
                        RATE,
                        ASOFDATE,
                        TYPECODE,
                        SOURCECODEID,
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED
                    )
                    values(
                        @BASEEXCHANGERATEID,
                        @TRANSACTIONCURRENCYID,
                        @BASECURRENCYID,
                        @EXCHANGERATE,
                        @DATE,
                        2,
                        null,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    );
                end

                if @FINDERNUMBER is null
                    set @FINDERNUMBER = 0;

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

                --if we have a finder number make sure the constituent has been added to the tables

                if @FINDERNUMBER > 0
                    exec dbo.[USP_DATAFORMTEMPLATE_ADD_ACQUISITIONLISTMEMBER] @CONSTITUENTID, @CHANGEAGENTID, @FINDERNUMBER;

                -- If the system has set that households can't be donors, verify that constituent isn't a household

                if dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS() = 0 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
                    raiserror('HOUSEHOLDSCANNOTBEDONORS', 13, 1);

                -- if the group type can't be a donor, raise an error

                if exists(select GD.ID from dbo.GROUPDATA GD inner join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID where GD.ID = @CONSTITUENTID and GT.CANBEDONOR = 0)
                    raiserror('GROUPCANNOTBEDONOR', 13, 1);

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

                declare @RECEIPTTYPECODE tinyint;
                set @RECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID,@TYPECODE);

                --Get Multicurrency values.

                declare @BASEAMOUNT money;
                declare @ORGANIZATIONAMOUNT money;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                declare @BASETOORGANIZATIONEXCHANGERATEID uniqueidentifier;

                exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1, @BASETOORGANIZATIONEXCHANGERATEID;

        declare @PDAS uniqueidentifier;
        select top 1 @PDAS = PDAS.ID from dbo.PDACCOUNTSYSTEM as PDAS 
        inner join dbo.CURRENCYSET as CS on PDAS.CURRENCYSETID = CS.ID
        where @BASECURRENCYID = CS.BASECURRENCYID
        order by PDAS.ISDEFAULT desc

                --Add new Revenue Transaction

                insert into dbo.FINANCIALTRANSACTION (
                  ID
                  , CONSTITUENTID
                  , DATE
                  , POSTDATE
                  , POSTSTATUSCODE
                  , BASEAMOUNT
                  , TYPECODE
                  , PDACCOUNTSYSTEMID -- @PDAS gets updated again if new code, otherwise we have the one matching with the currency 

                  , TRANSACTIONAMOUNT
                  , TRANSACTIONCURRENCYID
                  , BASEEXCHANGERATEID
                  , ORGAMOUNT                  
                  , ORGEXCHANGERATEID
                  , ADDEDBYID
                  , CHANGEDBYID
                  , DATEADDED
                  , DATECHANGED
                  )
                    values (
                      @ID
                      , @CONSTITUENTID
                      , @DATE
                      , @POSTDATE
                      , case @POSTSTATUSCODE when 2 then 3 else 1 end
                      , @BASEAMOUNT
                      , 0
                      , @PDAS
                      , @AMOUNT
                      , @TRANSACTIONCURRENCYID
                      , @BASEEXCHANGERATEID
                      , @ORGANIZATIONAMOUNT
                      , @ORGANIZATIONEXCHANGERATEID
                      , @CHANGEAGENTID
                      , @CHANGEAGENTID
                      , @CURRENTDATE
                      , @CURRENTDATE
                    );

                    insert into dbo.REVENUE_EXT (
                  ID
                  , REFERENCE
                  , BATCHNUMBER
                  , RECEIPTAMOUNT
                  , FINDERNUMBER
                  , SOURCECODE
                  , APPEALID
                  , GIVENANONYMOUSLY
                  , DONOTACKNOWLEDGE
                  , DONOTRECEIPT
                  , BENEFITSWAIVED
                  , MAILINGID
                  , CHANNELCODEID
                  , RECEIPTTYPECODE
                  , ADDEDBYID
                  , CHANGEDBYID
                 , DATEADDED
                  , DATECHANGED
                  )
                    values (
                      @ID
                      , isnull(@REFERENCE,'')
                      , isnull(@BATCHNUMBER, '')
                      , @RECEIPTAMOUNT
                      , @FINDERNUMBER
                      , isnull(@SOURCECODE,'')
                      , @APPEALID
                      , coalesce(@GIVENANONYMOUSLY, 0)
                      , @DONOTACKNOWLEDGE
                      , @DONOTRECEIPT
                      , @BENEFITSWAIVED
                      , @MAILINGID
                      , @CHANNELCODEID
                      , @RECEIPTTYPECODE
                      , @CHANGEAGENTID
                      , @CHANGEAGENTID
                      , @CURRENTDATE
                      , @CURRENTDATE
                    );


                --Add origination source

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

                --Add payment original amount    

        --Since orgamount and base amount are summarized from lineitems in FTM, add this directly using values we have

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

        if not exists (select * from dbo.PAYMENTORIGINALAMOUNT where ID = @ID)
          insert into dbo.PAYMENTORIGINALAMOUNT(ID, ORIGINALAMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values (@ID, @BASEAMOUNT, @AMOUNT, @ORGANIZATIONAMOUNT, @BASECURRENCYID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE) 

                declare @REVENUEPAYMETHODID uniqueidentifier

                set @REVENUEPAYMETHODID = newid();

                -- Note that REVENUEPAYMENTMETHOD doesn't need multicurrency values; see comment on the table spec.

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

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


                exec dbo.USP_REVENUE_ADDPAYMENTDETAILS    @REVENUEPAYMETHODID, @PAYMENTMETHODCODE, @CHECKDATE,
                                                        @CHECKNUMBER, @CONSTITUENTACCOUNTID, @REFERENCEDATE,
                                                        @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER,
                                                        @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON,
                                                        @ISSUER, @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE,
                                                        null, null, null, null, null, @PROPERTYSUBTYPECODEID,
                                                        @GIFTINKINDSUBTYPECODEID,
                                                        @CHANGEAGENTID,@CURRENTDATE, 0,
                                                        @OTHERPAYMENTMETHODCODEID, @DIRECTDEBITRESULTCODE,
                                                        @LOWPRICE, @HIGHPRICE, 0, 0, @TRANSACTIONID, @AMOUNT,
                                                        @GIFTINKINDITEMNAME, @GIFTINKINDDISPOSITIONCODE,
                                                        @GIFTINKINDNUMBEROFUNITS, @GIFTINKINDFAIRMARKETVALUE,
                                                        0, @BASECURRENCYID, @TRANSACTIONCURRENCYID,
                                                        @BASEEXCHANGERATEID, 
                                                        @MERCHANTACCOUNTID,
                                                        0, --SALE_LOWPRICE

                                                        0, --SALE_MEDIANPRICE

                                                        0, --SALE_HIGHPRICE

                                                        @VENDORID,
                                                        @SEPAMANDATEID;

                --If a SEPA mandate is used, mark the mandate record to show that a payment has been made towards it.

                if @SEPAMANDATEID is not null
                    exec dbo.USP_SEPAMANDATE_PAYMENTMADE @SEPAMANDATEID,null,null, @CHANGEAGENTID;

                -- add benefits

                declare @TOTALBENEFITS xml;
                set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);

                --Remove benefits if they are waived

                if @BENEFITSWAIVED = 1
                    set @TOTALBENEFITS = null;

                --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.

                set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2(@TOTALBENEFITS,@TRANSACTIONCURRENCYID,@BASECURRENCYID);
                exec dbo.USP_REVENUE_GETBENEFITS_4_UPDATEFROMXML @ID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE;


                --insert tribute

                if not @TRIBUTEID is null
                begin
                        declare @RTID uniqueidentifier;
                        set @RTID = newid()

                        insert into dbo.REVENUETRIBUTE (ID, REVENUEID, TRIBUTEID, AMOUNT, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values (@RTID, @ID, @TRIBUTEID, @BASEAMOUNT, @BASECURRENCYID, @ORGANIZATIONAMOUNT, @BASETOORGANIZATIONEXCHANGERATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                        -- insert default revenue tribute letters for any acknowledgees that have corresponding tribute letter codes

                        insert into dbo.REVENUETRIBUTELETTER(REVENUETRIBUTEID,CONSTITUENTID,TRIBUTELETTERCODEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                            select @RTID,CONSTITUENTID,TRIBUTELETTERCODEID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
                            from dbo.TRIBUTEACKNOWLEDGEE
                            where TRIBUTEID = @TRIBUTEID
                            and TRIBUTEACKNOWLEDGEE.TRIBUTELETTERCODEID is not null;

                end

                --insert letter

                if not @LETTERCODEID is null
                        insert into dbo.REVENUELETTER (REVENUEID, LETTERCODEID, ACKNOWLEDGEEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values (@ID, @LETTERCODEID, @CONSTITUENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

            end try

            begin catch
                exec dbo.USP_RAISE_ERROR;

                return 1;
            end catch

            return 0;