USP_PAYMENT_LOAD

Stored procedure to load a payment.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@CONSTITUENTNAME	nvarchar(700)	INOUT
@DATE	datetime	INOUT
@AMOUNT	money	INOUT
@APPLICATIONCODE	tinyint	INOUT
@RECEIPTAMOUNT	money	INOUT
@REVENUESTREAMS	xml	INOUT
@SOURCECODE	nvarchar(50)	INOUT
@POSTSTATUSCODE	tinyint	INOUT
@POSTDATE	datetime	INOUT
@APPEALID	uniqueidentifier	INOUT
@BENEFITS	xml	INOUT
@BENEFITSWAIVED	bit	INOUT
@GIVENANONYMOUSLY	bit	INOUT
@MAILINGID	uniqueidentifier	INOUT
@CHANNELCODEID	uniqueidentifier	INOUT
@DONOTRECEIPT	bit	INOUT
@REFERENCE	nvarchar(255)	INOUT
@TSLONG	bigint	INOUT
@DONOTACKNOWLEDGE	bit	INOUT
@FINDERNUMBER	bigint	INOUT
@PERCENTAGEBENEFITS	xml	INOUT
@BASECURRENCYID	uniqueidentifier	INOUT
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT
@BASEEXCHANGERATEID	uniqueidentifier	INOUT
@EXCHANGERATE	decimal(20, 8)	INOUT
@HADSPOTRATE	bit	INOUT
@BASEDECIMALDIGITS	tinyint	INOUT
@BASEROUNDINGTYPECODE	tinyint	INOUT
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT

Definition

Copy



      CREATE procedure dbo.USP_PAYMENT_LOAD
      (
        @ID uniqueidentifier,
        @DATALOADED bit = 0 output,
        @CONSTITUENTID uniqueidentifier = null output,    
        @CONSTITUENTNAME nvarchar(700) = null output,
        @DATE datetime = null output,
        @AMOUNT money = null output,
        @APPLICATIONCODE tinyint = null output,
        @RECEIPTAMOUNT money = null output,
        @REVENUESTREAMS xml = null output,
        @SOURCECODE nvarchar(50) = null output,
        @POSTSTATUSCODE tinyint = null output,
        @POSTDATE datetime = null output,
        @APPEALID uniqueidentifier = null output,
        @BENEFITS xml = null output,
        @BENEFITSWAIVED bit = null output,
        @GIVENANONYMOUSLY bit = null output,
        @MAILINGID uniqueidentifier = null output,
        @CHANNELCODEID uniqueidentifier = null output,
        @DONOTRECEIPT bit = null output,
        @REFERENCE nvarchar(255) = null output,
        @TSLONG bigint = 0 output,
        @DONOTACKNOWLEDGE bit = null output,
        @FINDERNUMBER bigint = null output,
        @PERCENTAGEBENEFITS xml = null output,
        @BASECURRENCYID uniqueidentifier = null output,
        @TRANSACTIONCURRENCYID uniqueidentifier = null output,
        @BASEEXCHANGERATEID uniqueidentifier = null output,
        @EXCHANGERATE decimal(20,8) = null output,
        @HADSPOTRATE bit = null output,
        @BASEDECIMALDIGITS tinyint = null output,
        @BASEROUNDINGTYPECODE tinyint = null output,
        @TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output
      )
      as
      set nocount on;

      set @DATALOADED = 0;
      set @TSLONG = 0;

      select
        @DATALOADED = 1,
        @APPLICATIONCODE = 0,
        @CONSTITUENTID = REVENUE.CONSTITUENTID,
        @CONSTITUENTNAME = NF.NAME,
        @DATE = cast(REVENUE.DATE as datetime),
        @AMOUNT = REVENUE.TRANSACTIONAMOUNT,
        @DONOTRECEIPT = REVENUE_EXT.DONOTRECEIPT,
        @DONOTACKNOWLEDGE = REVENUE_EXT.DONOTACKNOWLEDGE,
        @RECEIPTAMOUNT = REVENUE_EXT.RECEIPTAMOUNT,
        @SOURCECODE = REVENUE_EXT.SOURCECODE,
        @POSTSTATUSCODE = case REVENUE.POSTSTATUSCODE when 3 then 2 when 2 then 0 else 1 end,
        @POSTDATE = REVENUE.POSTDATE,
        @APPEALID = REVENUE_EXT.APPEALID,
        @GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY,
        @MAILINGID = REVENUE_EXT.MAILINGID,
        @CHANNELCODEID = REVENUE_EXT.CHANNELCODEID,
        @BENEFITSWAIVED = REVENUE_EXT.BENEFITSWAIVED,
        @TSLONG = REVENUE.TSLONG,
        @REFERENCE = REVENUE_EXT.REFERENCE,
        @REVENUESTREAMS = dbo.[UFN_REVENUE_GETAPPLICATIONS_2_TOITEMLISTXML](@ID, 1),
        @FINDERNUMBER = REVENUE_EXT.FINDERNUMBER,
        @BASECURRENCYID = V.BASECURRENCYID,
        @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
        @EXCHANGERATE = 
          case
            when REVENUE.BASEEXCHANGERATEID is not null
              then CURRENCYEXCHANGERATE.RATE
            when REVENUE.TRANSACTIONCURRENCYID = V.BASECURRENCYID
              then 1
            else 0
          end,
        @HADSPOTRATE =  
          case
            when CURRENCYEXCHANGERATE.TYPECODE = 2
              then 1
            else 0
          end,
        @BASEDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
        @BASEROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
        @TRANSACTIONCURRENCYDESCRIPTION = dbo.UFN_CURRENCY_GETDESCRIPTION(REVENUE.TRANSACTIONCURRENCYID)
      from dbo.FINANCIALTRANSACTION REVENUE
      inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
      left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
      left join dbo.CURRENCY on CURRENCY.ID = V.BASECURRENCYID
      left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
      where 
        REVENUE.ID = @ID and 
        REVENUE.TYPECODE = 0 and
        REVENUE.DELETEDON is null;

      exec dbo.USP_REVENUE_GETBENEFITSSPLIT @ID, @BENEFITS output, @PERCENTAGEBENEFITS output

      return 0;