USP_BBNC_COMMITMEMBERSHIPADDREVENUE

            CREATE procedure dbo.USP_BBNC_COMMITMEMBERSHIPADDREVENUE
            (
                @ID uniqueidentifier = null output,
                @BATCHNUMBER nvarchar(100),
                @CONSTITUENTID uniqueidentifier,
                @AMOUNT money,
                @GIFTDATE datetime,
                @PAYMENTMETHODCODE tinyint,

                @CREDITTYPECODEID uniqueidentifier,
                @CARDHOLDERNAME nvarchar(255),
                @AUTHORIZATIONCODE nvarchar(100),
                @CREDITCARDNUMBER nvarchar(100),
                @EXPIRATIONDATE dbo.UDT_FUZZYDATE = '00000000',

                @REFERENCENUMBER nvarchar(100),

                @ISANONYMOUS bit = 0,

                @ACCOUNTID uniqueidentifier,

                @NOTETITLE nvarchar(100),
                @COMMENTS nvarchar(max),

                @BBNCTRANSACTIONID int,
                @BBNCPAGENAME nvarchar(50),
                @BBNCPAGEID int,

                @MARKRECEIPTED bit = 0,

                @FINDERNUMBER bigint = 0,

                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime,

                @RECEIPTNUMBER int = 0,

        @ISOCODE nvarchar(3) = '',
        @PDACCOUNTSYSTEMID uniqueidentifier = null,
        @CURRENTAPPUSERID uniqueidentifier = null

            )
            as
            set nocount on;

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

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

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

                if @PAYMENTMETHODCODE <> 2 and @PAYMENTMETHODCODE <> 3
                    raiserror('ERR_INVALID_PAYMETHOD', 13, 1);

                if @AMOUNT < 0 
                    raiserror('ERR_AMOUNT_LESSTHANZERO', 13, 1);

                -- if this household can't be a donor, raise an error

                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);

                declare @REVENUENOTETYPECODEID uniqueidentifier;
                declare @CHANNELCODEID uniqueidentifier;

        if (@PDACCOUNTSYSTEMID is null)
          set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

        if (@PDACCOUNTSYSTEMID is null)
          set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

        -- Multicurrency Info grab

         declare @TRANSACTIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETCURRENCYFROMISO(@ISOCODE);
         declare @BASECURRENCYID uniqueidentifier;
         declare @BASEEXCHANGERATEID uniqueidentifier;
         declare @BASEAMOUNT money;
         declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
         declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
         declare @ORGANIZATIONAMOUNT money;

         --ID where clause is using default, should be using PDACCOUNTSYSTEMID that is selected in the batch.

         select @BASECURRENCYID = CURRENCYSET.BASECURRENCYID from dbo.PDACCOUNTSYSTEM
         inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
         where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID

         set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,@BASECURRENCYID,@GIFTDATE,1,null);   

          select
                        @BASEAMOUNT = BASEAMOUNT,
                        @ORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT,
                        @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
                    from dbo.UFN_CURRENCY_GETCURRENCYVALUES(@AMOUNT, @GIFTDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID);

        declare @MULTICURRENCYENABLED bit;
        set @BASEAMOUNT = dbo.UFN_CURRENCY_CONVERT(@AMOUNT,@BASEEXCHANGERATEID);
        set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
        if @MULTICURRENCYENABLED = 0
        begin
         set @BASEAMOUNT = @AMOUNT;
        end
        if @TRANSACTIONCURRENCYID = @BASECURRENCYID 
        begin
          set @BASEAMOUNT = @AMOUNT;
        end


          -- Ensure that we can add a payment of this transaction currency to the account system.

         if not exists(select 1 from dbo.CURRENCYSETTRANSACTIONCURRENCY
                       inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSETTRANSACTIONCURRENCY.CURRENCYSETID
                       where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID and CURRENCYSETTRANSACTIONCURRENCY.CURRENCYID = @TRANSACTIONCURRENCYID)
         begin
            raiserror('Invalid transaction currency, current account system does not contain this transaction currency',13,1);
         end  



                select top 1 
                    @REVENUENOTETYPECODEID = REVENUENOTETYPECODEID,
                    @CHANNELCODEID = CHANNELCODEID
                from dbo.NETCOMMUNITYDEFAULTCODEMAP;

                declare @RECEIPTAMOUNT money;
                set @RECEIPTAMOUNT = @AMOUNT;

        declare @CONSTITUENTIDFROMFINDERNUMBER uniqueidentifier;
        declare @MAILINGID uniqueidentifier;
        declare @SOURCECODE nvarchar(50);
        declare @APPEALID uniqueidentifier;

        exec dbo.[USP_BBNC_BATCH_FINDERNUMBERLOOKUP] @FINDERNUMBER, @CONSTITUENTIDFROMFINDERNUMBER output, @MAILINGID output, @SOURCECODE output, @APPEALID output;
        if @CONSTITUENTIDFROMFINDERNUMBER is not null 
          if @CONSTITUENTID <> @CONSTITUENTIDFROMFINDERNUMBER
            set @FINDERNUMBER = 0;

                insert into dbo.REVENUE(ID, BATCHNUMBER, CONSTITUENTID, DATE, AMOUNT, TRANSACTIONTYPECODE, RECEIPTAMOUNT, SOURCECODE, APPEALID, BENEFITSWAIVED, GIVENANONYMOUSLY, MAILINGID, CHANNELCODEID, DONOTACKNOWLEDGE, DONOTRECEIPT, DONOTPOST, POSTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BASECURRENCYID,ORGANIZATIONAMOUNT,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,ORGANIZATIONEXCHANGERATEID,BASEEXCHANGERATEID,FINDERNUMBER)
                    values(@ID, @BATCHNUMBER, @CONSTITUENTID, @GIFTDATE, @BASEAMOUNT, 0, @RECEIPTAMOUNT, @SOURCECODE, @APPEALID, 0, @ISANONYMOUS, @MAILINGID, @CHANNELCODEID, 0, 0, 0, @GIFTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@AMOUNT,@TRANSACTIONCURRENCYID,@ORGANIZATIONEXCHANGERATEID,@BASEEXCHANGERATEID,@FINDERNUMBER);

                --Add origination source

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

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

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

                insert into dbo.REVENUEBBNC(ID, NETCOMMUNITYTRANSACTIONID, NETCOMMUNITYPAGENAME, NETCOMMUNITYPAGEID, NETCOMMUNITYAPPEALID, EMAILID, EMAILSUBJECT, EMAILNAME, ISTEAMFUNDRAISINGOFFLINEGIFT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values(@ID, @BBNCTRANSACTIONID, @BBNCPAGENAME, @BBNCPAGEID, null, 0, '', '', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

                declare @CREDITCARDPARTIALNUMBER nvarchar(4)

                if len(@CREDITCARDNUMBER)>4 
                    set @CREDITCARDPARTIALNUMBER = right(@CREDITCARDNUMBER,4)
                else
                    set @CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER

                --Passing in BBNC transaction ID for the BBPS transaction ID since BBNC passes up its own transaction ID to BBPS and so they are the same.

                --The BBNC table is queried directly since TRANSACTIONGUID isn't sent down from BBNC.  This probably should be revisited later so it's 

                --consistent with the other BBNC fields.

                declare @BBNCTRANSACTIONGUID uniqueidentifier;
                select @BBNCTRANSACTIONGUID = TransactionGUID
                from dbo.MembershipTransactions
                where ID = @BBNCTRANSACTIONID;

                exec dbo.USP_REVENUE_ADDPAYMENTDETAILS @ID=@REVENUEPAYMETHODID, @PAYMENTMETHODCODE=@PAYMENTMETHODCODE,  @REFERENCENUMBER=@REFERENCENUMBER, @CONSTITUENTACCOUNTID = @ACCOUNTID, @CARDHOLDERNAME=@CARDHOLDERNAME, @CREDITCARDNUMBER=@CREDITCARDPARTIALNUMBER, @CREDITTYPECODEID=@CREDITTYPECODEID, @AUTHORIZATIONCODE=@AUTHORIZATIONCODE, @EXPIRESON=@EXPIRATIONDATE, @CHANGEAGENTID=@CHANGEAGENTID, @CURRENTDATE=@CHANGEDATE, @KEYALREADYOPEN = 1,@BASECURRENCYID=@BASECURRENCYID,@TRANSACTIONCURRENCYID=@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID=@BASEEXCHANGERATEID,@TRANSACTIONID=@BBNCTRANSACTIONGUID;

                if not @COMMENTS is null and len(@COMMENTS) > 0 
                    insert into dbo.REVENUENOTE(ID, REVENUEID, DATEENTERED, TITLE, TEXTNOTE, REVENUENOTETYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values(newid(), @ID, @GIFTDATE, @NOTETITLE, @COMMENTS, @REVENUENOTETYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

                if @MARKRECEIPTED = 1
                    insert into dbo.REVENUERECEIPT(ID, REVENUEID, RECEIPTNUMBER, RECEIPTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values(newid(), @ID, @RECEIPTNUMBER, @GIFTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);


          -- Add Original Payment

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


        exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE
                      @ID = @ID,
                      @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
                      @CHANGEDATE = @CHANGEDATE,
                      @CHANGEAGENTID = @CHANGEAGENTID    


                exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, 
                                                                 null, 
                                                                  @PAYMENTMETHODCODE, 
                                                                  @CREDITTYPECODEID, 
                                                                  @CHANGEAGENTID, 
                                                                  @GIFTDATE, 
                                                                  0, --revenue transaction type code for payment is 0

                                                                  null, 
                                                                  null;     

            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;