USP_REVENUE_UPDATEPAYMENTDETAILS

      CREATE procedure dbo.USP_REVENUE_UPDATEPAYMENTDETAILS
      (
        @PAYMENTMETHODID uniqueidentifier,
        @PAYMENTMETHODCODE tinyint, 
        @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
        @CHECKNUMBER nvarchar(20) = '',
        @CONSTITUENTACCOUNTID uniqueidentifier = null,
        @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,
        @PROPERTYSUBTYPECODEID uniqueidentifier = null,
        @GIFTINKINDSUBTYPECODEID uniqueidentifier = null,
        @SALEDATE datetime = null,
        @SALEAMOUNT money = 0,
        @BROKERFEE money = 0,
        @SALEPOSTDATE datetime = null,
        @SALEPOSTSTATUSCODE tinyint = 0,
        @CHANGEAGENTID uniqueidentifier,
        @CHANGEDATE datetime,
        @KEYALREADYOPEN bit = 0,
        @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
        @DIRECTDEBITRESULTCODE nvarchar(10) = '',
        @LOWPRICE decimal(19,4) = 0,
        @HIGHPRICE decimal(19,4) = 0,
        @REVENUEAMOUNT money = 0,
        @GIFTINKINDITEMNAME nvarchar(100) = '',
        @GIFTINKINDDISPOSITIONCODE tinyint = 0,
        @GIFTINKINDNUMBEROFUNITS int = 0,
        @GIFTINKINDFAIRMARKETVALUE money = 0,
        @DIRECTDEBITISREJECTED bit = 0,
        @BASECURRENCYID uniqueidentifier = null,
        @TRANSACTIONCURRENCYID uniqueidentifier = null,
        @BASEEXCHANGERATEID uniqueidentifier = null,
        @ORIGINALPAYMENTMETHODCODE tinyint = null,
        @VENDORID nvarchar(50) = '', --Set @OVERRIDESAVEDVENDORID = 1 as well, otherwise null will be interpreted as no change for binary compatibility

        @BBPSTRANSACTIONID uniqueidentifier = null, --Set @OVERRIDESAVEDBBPSTRANSACTIONID = 1 as well, otherwise null will be interpreted as no change for binary compatibility

        @SEPAMANDATEID uniqueidentifier = null, --Set @OVERRIDESAVEDSEPAMANDATEID = 1 as well, otherwise null will be interpreted as no change for binary compatibility

        @OVERRIDESAVEDVENDORID bit = 0,
        @OVERRIDESAVEDBBPSTRANSACTIONID bit= 0,
        @OVERRIDESAVEDSEPAMANDATEID bit = 0
      )
      as 
      set nocount on;

      begin try

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

        declare @REVENUEID uniqueidentifier;
        select @REVENUEID = REVENUEID from dbo.REVENUEPAYMENTMETHOD where ID = @PAYMENTMETHODID;

        declare @BASELOWPRICE decimal(19,4);
        declare @BASEMEDIANPRICE decimal(19,4);
        declare @BASEHIGHPRICE decimal(19,4);
        declare @BASESALEAMOUNT money;
        declare @BASEBROKERFEE money;
        declare @BASEGIFTINKINDFAIRMARKETVALUE money;
        declare @ORGANIZATIONLOWPRICE decimal(19,4);
        declare @ORGANIZATIONMEDIANPRICE decimal(19,4);
        declare @ORGANIZATIONHIGHPRICE decimal(19,4);    
        declare @ORGANIZATIONSALEAMOUNT money;
        declare @ORGANIZATIONBROKERFEE money;
        declare @ORGANIZATIONGIFTINKINDFAIRMARKETVALUE money;

        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

        if @PAYMENTMETHODCODE = 0 --Cash

        begin
          update dbo.CASHPAYMENTMETHODDETAIL
            set
              REFERENCEDATE = @REFERENCEDATE,
              REFERENCENUMBER = @REFERENCENUMBER,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0
            insert into dbo.CASHPAYMENTMETHODDETAIL
              (ID, REFERENCEDATE, REFERENCENUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values (@PAYMENTMETHODID, @REFERENCEDATE, @REFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
        end
        else if @ORIGINALPAYMENTMETHODCODE = 0 or @ORIGINALPAYMENTMETHODCODE is null
          exec dbo.USP_CASHPAYMENTMETHODDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;

        if @PAYMENTMETHODCODE = 1 --Check

        begin
          update dbo.CHECKPAYMENTMETHODDETAIL
            set 
              CHECKNUMBER = @CHECKNUMBER,
              CHECKDATE = @CHECKDATE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0
            insert into dbo.CHECKPAYMENTMETHODDETAIL
              (ID, CHECKNUMBER, CHECKDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values (@PAYMENTMETHODID, @CHECKNUMBER, @CHECKDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

        end
        else if @ORIGINALPAYMENTMETHODCODE = 1 or @ORIGINALPAYMENTMETHODCODE is null
          exec dbo.USP_CHECKPAYMENTMETHODDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;

        if @PAYMENTMETHODCODE = 2 --Credit Card

        begin            

          update dbo.CREDITCARDPAYMENTMETHODDETAIL
            set
              CARDHOLDERNAME = COALESCE(@CARDHOLDERNAME,''),
              CREDITCARDPARTIALNUMBER = COALESCE(@CREDITCARDNUMBER, ''),
              CREDITTYPECODEID = @CREDITTYPECODEID,
              AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
              EXPIRESON = COALESCE(@EXPIRESON, '00000000'),
              VENDORID = 
                case
                  --Related to Bug 320341 Assume the caller doesn't know about the new

                  --@VENDORID parameter if they used the default values

                  --for both of these parameters, and just leave the current table

                  --value alone.

                  when @OVERRIDESAVEDVENDORID = 0 and @VENDORID is null
                    then CREDITCARDPAYMENTMETHODDETAIL.VENDORID
                  else
                    isnull(@VENDORID, '')
                end,
              TRANSACTIONID = 
                case
                  --Bug 320341 Assume the caller doesn't know about the new

                  --@BBPSTRANSACTIONID parameter if they used the default values

                  --for both of these parameters, and just leave the current table

                  --value alone.

                  when @OVERRIDESAVEDBBPSTRANSACTIONID = 0 and @BBPSTRANSACTIONID is null
                    then CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID
                  else
                    @BBPSTRANSACTIONID
                end,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

            if @@ROWCOUNT = 0
              insert into dbo.CREDITCARDPAYMENTMETHODDETAIL
                (ID, CARDHOLDERNAME, CREDITCARDPARTIALNUMBER, CREDITTYPECODEID, AUTHORIZATIONCODE, EXPIRESON, VENDORID, TRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values (@PAYMENTMETHODID, COALESCE(@CARDHOLDERNAME,''), COALESCE(@CREDITCARDNUMBER,''), @CREDITTYPECODEID, @AUTHORIZATIONCODE,COALESCE(@EXPIRESON, '00000000'), isnull(@VENDORID, ''), @BBPSTRANSACTIONID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

        end 
        else if @ORIGINALPAYMENTMETHODCODE = 2 or @ORIGINALPAYMENTMETHODCODE is null
          exec dbo.USP_CREDITCARDPAYMENTMETHODDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;

        if @PAYMENTMETHODCODE = 3 --Direct debit

        begin
          if (@DIRECTDEBITISREJECTED = 1) and (@REVENUEAMOUNT > 0)
          begin
            raiserror('BBERR_REVENUEAMOUNTABOVEZERODIRECTDEBITISREJECTED', 13, 1);
            return 1;
          end

          if @OVERRIDESAVEDSEPAMANDATEID = 0 and @SEPAMANDATEID is null
          begin
            --Related to Bug 320341. Assume the caller doesn't know about the new

            --@SEPAMANDATEID parameter if they used the default values for both of

            --these parameters, and just leave the current table value alone.

            select
              @SEPAMANDATEID = DIRECTDEBITPAYMENTMETHODDETAIL.SEPAMANDATEID
            from
              dbo.DIRECTDEBITPAYMENTMETHODDETAIL
            where
              DIRECTDEBITPAYMENTMETHODDETAIL.ID = @PAYMENTMETHODID;
          end

          --Run this before we update the payment method details, because we need to see if the SEPA Mandate has changed

          exec dbo.USP_SEPAMANDATE_PAYMENTMADE 
            @SEPAMANDATEID, 
            null, --BATCHROWID

            @REVENUEID, 
            @CHANGEAGENTID;

         declare @ORIGINALDIRECTDEBITREJECTED bit = 0;
         select
            @ORIGINALDIRECTDEBITREJECTED = DIRECTDEBITPAYMENTMETHODDETAIL.ISREJECTED
         from
            dbo.DIRECTDEBITPAYMENTMETHODDETAIL
         where
            DIRECTDEBITPAYMENTMETHODDETAIL.ID = @PAYMENTMETHODID;

         if @ORIGINALDIRECTDEBITREJECTED = 0  and @DIRECTDEBITISREJECTED = 1 and @SEPAMANDATEID is not null
         begin
           update dbo.SEPAMANDATE
            set
                PAYMENTCOUNT = PAYMENTCOUNT - 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CHANGEDATE
            where
                SEPAMANDATE.ID =  @SEPAMANDATEID
                and PAYMENTCOUNT > 0;
         end

         if @ORIGINALDIRECTDEBITREJECTED = 1  and @DIRECTDEBITISREJECTED = 0 and @SEPAMANDATEID is not null
         begin
           update dbo.SEPAMANDATE
            set 
              PAYMENTCOUNT = PAYMENTCOUNT + 1,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where 
              SEPAMANDATE.ID = @SEPAMANDATEID;
         end

          update dbo.DIRECTDEBITPAYMENTMETHODDETAIL
            set 
              REFERENCEDATE = @REFERENCEDATE,
              REFERENCENUMBER = @REFERENCENUMBER,
              CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
              DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE,
              ISREJECTED = @DIRECTDEBITISREJECTED,
              SEPAMANDATEID = @SEPAMANDATEID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0
            insert into dbo.DIRECTDEBITPAYMENTMETHODDETAIL
              (ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, DIRECTDEBITRESULTCODE, ISREJECTED, SEPAMANDATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values (@PAYMENTMETHODID, @REFERENCEDATE, @REFERENCENUMBER, @CONSTITUENTACCOUNTID, @DIRECTDEBITRESULTCODE, @DIRECTDEBITISREJECTED, @SEPAMANDATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

        end
        else if @ORIGINALPAYMENTMETHODCODE = 3 or @ORIGINALPAYMENTMETHODCODE is null
          exec dbo.USP_DIRECTDEBITPAYMENTMETHODDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;

        if @PAYMENTMETHODCODE = 4 --Stock

        begin
          -- Verify none of the price per share values are negative

          if @LOWPRICE < 0
          begin
            raiserror('BBERR_LOWPRICEPERSHARENEGATIVE', 13, 1)
            return 1
          end

          if @MEDIANPRICE < 0
          begin
            raiserror('BBERR_MEDIANPRICEPERSHARENEGATIVE', 13, 1)
            return 1
          end

          if @HIGHPRICE < 0
          begin
            raiserror('BBERR_HIGHPRICEPERSHARENEGATIVE', 13, 1)
            return 1
          end

          -- Get currency rounding information.

          declare @DECIMALDIGITS tinyint;
          declare @ROUNDINGTYPECODE tinyint;
          select
            @DECIMALDIGITS = DECIMALDIGITS, 
            @ROUNDINGTYPECODE = ROUNDINGTYPECODE
          from dbo.UFN_CURRENCY_GETPROPERTIES(@TRANSACTIONCURRENCYID);

          -- Verify that the number of units isn't less than the number of units sold

          if @NUMBEROFUNITS < coalesce((select sum(NUMBEROFUNITS) from dbo.STOCKSALE where STOCKDETAILID = @PAYMENTMETHODID), 0)
          begin
            raiserror('BBERR_NUMBEROFUNITSLESSTHANNUMBEROFUNITSSOLD', 13, 1)
            return 1
          end

          -- Convert amounts to base and organization amounts.

          set @LOWPRICE = coalesce(@LOWPRICE, 0);
          set @MEDIANPRICE = coalesce(@MEDIANPRICE, 0);
          set @HIGHPRICE = coalesce(@HIGHPRICE, 0);

          --exec dbo.USP_CURRENCY_GETCURRENCYVALUES @LOWPRICE, @CHANGEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASELOWPRICE output, default, @ORGANIZATIONLOWPRICE output, @ORGANIZATIONEXCHANGERATEID output, 1;

          --exec dbo.USP_CURRENCY_GETCURRENCYVALUES @MEDIANPRICE, @CHANGEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEMEDIANPRICE output, default, @ORGANIZATIONMEDIANPRICE output, @ORGANIZATIONEXCHANGERATEID output, 1;

          --exec dbo.USP_CURRENCY_GETCURRENCYVALUES @HIGHPRICE, @CHANGEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEHIGHPRICE output, default, @ORGANIZATIONHIGHPRICE output, @ORGANIZATIONEXCHANGERATEID output, 1;


          if @BASECURRENCYID <> @TRANSACTIONCURRENCYID
          begin
            set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @BASECURRENCYID, @CHANGEDATE, 1, NULL); 

            declare @BASEROUNDINGTYPECODE tinyint;
            declare @BASERATE decimal(20,8);
            select
              @BASEROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
              @BASERATE = CURRENCYEXCHANGERATE.RATE
            from
              dbo.CURRENCYEXCHANGERATE
              inner join dbo.CURRENCY on CURRENCY.ID = CURRENCYEXCHANGERATE.TOCURRENCYID
            where
              CURRENCYEXCHANGERATE.ID = @BASEEXCHANGERATEID;

            set @BASELOWPRICE =  coalesce(convert(decimal(20,4),@LOWPRICE) * @BASERATE,0);
            set @BASEMEDIANPRICE = coalesce(convert(decimal(20,4),@MEDIANPRICE) * @BASERATE,0);
            set @BASEHIGHPRICE = coalesce(convert(decimal(20,4),@HIGHPRICE) * @BASERATE,0);
          end
          else
          begin
            set @BASELOWPRICE =  @LOWPRICE;
            set @BASEMEDIANPRICE = @MEDIANPRICE;
            set @BASEHIGHPRICE = @HIGHPRICE;
          end

          declare @ORGANIZATIONCURRENCYID uniqueidentifier;
          set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

          if @ORGANIZATIONCURRENCYID <> @BASECURRENCYID
            begin
              set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CHANGEDATE, null, @TRANSACTIONCURRENCYID);                    

              declare @ORGANIZATIONROUNDINGTYPECODE tinyint;
              declare @ORGANIZATIONRATE decimal(20,8);
              select
                @ORGANIZATIONROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
                @ORGANIZATIONRATE = CURRENCYEXCHANGERATE.RATE
              from
                dbo.CURRENCYEXCHANGERATE
                inner join dbo.CURRENCY on CURRENCY.ID = CURRENCYEXCHANGERATE.TOCURRENCYID
              where
                CURRENCYEXCHANGERATE.ID = @ORGANIZATIONEXCHANGERATEID;

              set @ORGANIZATIONLOWPRICE = coalesce(convert(decimal(20,4),@BASELOWPRICE) * @ORGANIZATIONRATE,0);
              set @ORGANIZATIONMEDIANPRICE = coalesce(convert(decimal(20,4),@BASEMEDIANPRICE) * @ORGANIZATIONRATE,0);
              set @ORGANIZATIONHIGHPRICE = coalesce(convert(decimal(20,4),@BASEHIGHPRICE) * @ORGANIZATIONRATE,0);
            end
          else
            begin
              set @ORGANIZATIONLOWPRICE = @BASELOWPRICE;
              set @ORGANIZATIONMEDIANPRICE = @BASEMEDIANPRICE;
              set @ORGANIZATIONHIGHPRICE = @BASEHIGHPRICE;
            end

          if @NUMBEROFUNITS is null
            set @NUMBEROFUNITS = 0;

          update dbo.STOCKDETAIL
            set
              ISSUER = @ISSUER,
              NUMBEROFUNITS = @NUMBEROFUNITS,
              SYMBOL = @SYMBOL,
              MEDIANPRICE = @BASEMEDIANPRICE,
              LOWPRICE = @BASELOWPRICE,
              HIGHPRICE = @BASEHIGHPRICE,
              TRANSACTIONLOWPRICE = @LOWPRICE,
              TRANSACTIONMEDIANPRICE = @MEDIANPRICE,
              TRANSACTIONHIGHPRICE = @HIGHPRICE,
              ORGANIZATIONLOWPRICE = @ORGANIZATIONLOWPRICE,
              ORGANIZATIONMEDIANPRICE = @ORGANIZATIONMEDIANPRICE,
              ORGANIZATIONHIGHPRICE = @ORGANIZATIONHIGHPRICE,
              BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
              ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0
            insert into dbo.STOCKDETAIL
              (ID, ISSUER, NUMBEROFUNITS, SYMBOL, LOWPRICE, MEDIANPRICE, HIGHPRICE, TRANSACTIONLOWPRICE, TRANSACTIONMEDIANPRICE, TRANSACTIONHIGHPRICE, ORGANIZATIONLOWPRICE, ORGANIZATIONMEDIANPRICE, ORGANIZATIONHIGHPRICE, BASECURRENCYID, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values (@PAYMENTMETHODID, @ISSUER, @NUMBEROFUNITS, @SYMBOL, @BASELOWPRICE, @BASEMEDIANPRICE, @BASEHIGHPRICE, @LOWPRICE, @MEDIANPRICE, @HIGHPRICE, @ORGANIZATIONLOWPRICE, @ORGANIZATIONMEDIANPRICE, @ORGANIZATIONHIGHPRICE, @BASECURRENCYID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
        end    
        else if @ORIGINALPAYMENTMETHODCODE = 4 or @ORIGINALPAYMENTMETHODCODE is null
          exec dbo.USP_STOCKDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;

        if @PAYMENTMETHODCODE = 5 --Property

        begin

          update dbo.PROPERTYDETAIL_EXT
            set 
              PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
              where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0
          begin
            -- Check if any of the sold property fields are set

            if @SALEDATE is not null or @SALEAMOUNT <> 0 or @BROKERFEE <> 0 or @SALEPOSTDATE is not null or @SALEPOSTSTATUSCODE <> 1
            begin
              exec dbo.USP_CURRENCY_GETCURRENCYVALUES @SALEAMOUNT, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASESALEAMOUNT output, default, @ORGANIZATIONSALEAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;
              exec dbo.USP_CURRENCY_GETCURRENCYVALUES @BROKERFEE, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEBROKERFEE output, default, @ORGANIZATIONBROKERFEE output, @ORGANIZATIONEXCHANGERATEID output, 1;

              insert into dbo.PROPERTYDETAIL
                (ID,PROPERTYSUBTYPECODEID,SALEDATE,SALEAMOUNT,BROKERFEE,SALEPOSTDATE,SALEPOSTSTATUSCODE,TRANSACTIONSALEAMOUNT, TRANSACTIONBROKERFEE, ORGANIZATIONSALEAMOUNT, ORGANIZATIONBROKERFEE, BASECURRENCYID, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                values (@PAYMENTMETHODID,@PROPERTYSUBTYPECODEID,@SALEDATE,@BASESALEAMOUNT,@BASEBROKERFEE,@SALEPOSTDATE,@SALEPOSTSTATUSCODE,@SALEAMOUNT, @BROKERFEE, @ORGANIZATIONSALEAMOUNT, @ORGANIZATIONBROKERFEE, @BASECURRENCYID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID, @CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE);
            end
            else
              insert into dbo.PROPERTYDETAIL
                (ID,PROPERTYSUBTYPECODEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                values (@PAYMENTMETHODID,@PROPERTYSUBTYPECODEID,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE);
          end
          else
          begin
            if @SALEDATE is not null or @SALEAMOUNT <> 0 or @BROKERFEE <> 0 or @SALEPOSTDATE is not null or @SALEPOSTSTATUSCODE <> 1
            begin
              exec dbo.USP_CURRENCY_GETCURRENCYVALUES @SALEAMOUNT, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASESALEAMOUNT output, default, @ORGANIZATIONSALEAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;
              exec dbo.USP_CURRENCY_GETCURRENCYVALUES @BROKERFEE, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEBROKERFEE output, default, @ORGANIZATIONBROKERFEE output, @ORGANIZATIONEXCHANGERATEID output, 1;

              update dbo.PROPERTYDETAIL set
                SALEDATE = @SALEDATE,
                SALEAMOUNT = @BASESALEAMOUNT,
                BROKERFEE = @BASEBROKERFEE,
                SALEPOSTDATE = @SALEPOSTDATE,
                SALEPOSTSTATUSCODE = @SALEPOSTSTATUSCODE,
                TRANSACTIONSALEAMOUNT = @SALEAMOUNT,
                TRANSACTIONBROKERFEE = @BROKERFEE,
                ORGANIZATIONSALEAMOUNT = @ORGANIZATIONSALEAMOUNT,
                ORGANIZATIONBROKERFEE = @ORGANIZATIONBROKERFEE,
                BASECURRENCYID = @BASECURRENCYID,
                TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CHANGEDATE
              where
                ID = @PAYMENTMETHODID
            end
          end
        end
        else if @ORIGINALPAYMENTMETHODCODE = 5 or @ORIGINALPAYMENTMETHODCODE is null
          exec dbo.USP_PROPERTYDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;

        else if @ORIGINALPAYMENTMETHODCODE = 5 or @ORIGINALPAYMENTMETHODCODE is null
          exec dbo.USP_PROPERTYDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;

        if @PAYMENTMETHODCODE = 6 --Gift in Kind

        begin
          declare @GIFTINKINDVALUE as money = 0;
          -- Catch any overflows

          begin try
            set @GIFTINKINDVALUE = @GIFTINKINDNUMBEROFUNITS * @GIFTINKINDFAIRMARKETVALUE
          end try
          begin catch
            raiserror('BBERR_REVENUEAMOUNTNOTEQUALGIFTINKINDAMOUNT', 13, 1)
            return 1
          end catch
          if (@GIFTINKINDNUMBEROFUNITS <> 0 or @GIFTINKINDFAIRMARKETVALUE <> 0) and (@REVENUEAMOUNT <> @GIFTINKINDVALUE)
          begin
            raiserror('BBERR_REVENUEAMOUNTNOTEQUALGIFTINKINDAMOUNT', 13, 1)
            return 1
          end

          -- Verify that the number of units isn't less than the number of units sold

          if @GIFTINKINDNUMBEROFUNITS < coalesce((select sum(NUMBEROFUNITS) from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @PAYMENTMETHODID), 0)
          begin
            raiserror('BBERR_GIFTINKINDNUMBEROFUNITSLESSTHANNUMBEROFUNITSSOLD', 13, 1)
           return 1
          end

          exec dbo.USP_CURRENCY_GETCURRENCYVALUES @GIFTINKINDFAIRMARKETVALUE, @CHANGEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEGIFTINKINDFAIRMARKETVALUE output, default, @ORGANIZATIONGIFTINKINDFAIRMARKETVALUE output, @ORGANIZATIONEXCHANGERATEID output, 1;

          update dbo.GIFTINKINDPAYMENTMETHODDETAIL
            set 
              GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID,
              ITEMNAME = @GIFTINKINDITEMNAME,
              DISPOSITIONCODE = @GIFTINKINDDISPOSITIONCODE,
              NUMBEROFUNITS = @GIFTINKINDNUMBEROFUNITS,
              FAIRMARKETVALUE = @BASEGIFTINKINDFAIRMARKETVALUE,
              TRANSACTIONFAIRMARKETVALUE = @GIFTINKINDFAIRMARKETVALUE,
              ORGANIZATIONFAIRMARKETVALUE = @ORGANIZATIONGIFTINKINDFAIRMARKETVALUE,
              BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
              ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0 
            insert into dbo.GIFTINKINDPAYMENTMETHODDETAIL
              (ID,GIFTINKINDSUBTYPECODEID,ITEMNAME,DISPOSITIONCODE,NUMBEROFUNITS,FAIRMARKETVALUE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
              values (@PAYMENTMETHODID,@GIFTINKINDSUBTYPECODEID,@GIFTINKINDITEMNAME,@GIFTINKINDDISPOSITIONCODE,@GIFTINKINDNUMBEROFUNITS,@GIFTINKINDFAIRMARKETVALUE,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE);
        end
        else if @ORIGINALPAYMENTMETHODCODE = 6 or @ORIGINALPAYMENTMETHODCODE is null
          exec dbo.USP_GIFTINKINDPAYMENTMETHODDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;

        if @PAYMENTMETHODCODE = 10 --Other

        begin
          update dbo.OTHERPAYMENTMETHODDETAIL
            set
              OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
              REFERENCEDATE = @REFERENCEDATE,
              REFERENCENUMBER = @REFERENCENUMBER,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0
            insert into dbo.OTHERPAYMENTMETHODDETAIL
              (ID, OTHERPAYMENTMETHODCODEID, REFERENCEDATE, REFERENCENUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values (@PAYMENTMETHODID, @OTHERPAYMENTMETHODCODEID, @REFERENCEDATE, @REFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
        end
        else if @ORIGINALPAYMENTMETHODCODE = 10 or @ORIGINALPAYMENTMETHODCODE is null
          exec dbo.USP_OTHERPAYMENTMETHODDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;

        if @PAYMENTMETHODCODE = 11 --Standing Order

        begin
          update dbo.STANDINGORDERPAYMENTMETHODDETAIL
            set 
              REFERENCEDATE = @REFERENCEDATE,
              CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0
            insert into dbo.STANDINGORDERPAYMENTMETHODDETAIL
              (ID, REFERENCEDATE, CONSTITUENTACCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values (@PAYMENTMETHODID, @REFERENCEDATE, @CONSTITUENTACCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

          if not exists(select ID from dbo.REVENUESTANDINGORDER where ID = @REVENUEID)
            insert into dbo.REVENUESTANDINGORDER(ID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values(@REVENUEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
        end
        else if @ORIGINALPAYMENTMETHODCODE = 11 or @ORIGINALPAYMENTMETHODCODE is null
        begin
          exec dbo.USP_REVENUESTANDINGORDER_DELETEBYID_WITHCHANGEAGENTID @REVENUEID, @CHANGEAGENTID;
          exec dbo.USP_STANDINGORDERPAYMENTMETHODDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;
        end

        if @PAYMENTMETHODCODE = 101 -- PayPal

        begin
          update dbo.PAYPALPAYMENTMETHODDETAIL
            set
              REFERENCEDATE = @REFERENCEDATE,
              REFERENCENUMBER = @REFERENCENUMBER,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0
            insert into dbo.PAYPALPAYMENTMETHODDETAIL
            (ID, REFERENCEDATE, REFERENCENUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
            (@PAYMENTMETHODID, @REFERENCEDATE, @REFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
        end
        else if @ORIGINALPAYMENTMETHODCODE = 101 or @ORIGINALPAYMENTMETHODCODE is null
        begin
          exec dbo.USP_PAYPALPAYMENTMETHODDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;
        end

        if @PAYMENTMETHODCODE = 102 -- Venmo

        begin
          update dbo.VENMOPAYMENTMETHODDETAIL
            set
              REFERENCEDATE = @REFERENCEDATE,
              REFERENCENUMBER = @REFERENCENUMBER,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where ID = @PAYMENTMETHODID;

          if @@ROWCOUNT = 0
            insert into dbo.VENMOPAYMENTMETHODDETAIL
            (ID, REFERENCEDATE, REFERENCENUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
            (@PAYMENTMETHODID, @REFERENCEDATE, @REFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
        end
        else if @ORIGINALPAYMENTMETHODCODE = 101 or @ORIGINALPAYMENTMETHODCODE is null
        begin
          exec dbo.USP_VENMOPAYMENTMETHODDETAIL_DELETEBYID_WITHCHANGEAGENTID @PAYMENTMETHODID, @CHANGEAGENTID;
        end
      end try
      begin catch
        exec dbo.USP_RAISE_ERROR;
      end catch