USP_DATAFORMTEMPLATE_EDITLOAD_MGPLEDGE_3

The load procedure used by the edit dataform template "Matching Gift Claim Edit Form 3"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ORIGINALGIFTID	uniqueidentifier	INOUT	Original Gift
@ORIGINALGIFTAMOUNT	money	INOUT	Original gift amount
@ORIGINALRECEIPTAMOUNT	money	INOUT	Original receipt amount
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent ID
@CONSTITUENTNAME	nvarchar(700)	INOUT	Constituent name
@MATCHINGORGANIZATIONID	uniqueidentifier	INOUT	Matching organization
@DATE	datetime	INOUT	Date
@AMOUNT	money	INOUT	Amount
@SPLITS	xml	INOUT	Designations
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@MATCHINGGIFTCONDITIONID	uniqueidentifier	INOUT	Matching gift condition type
@MGCONDITIONTYPE	nvarchar(100)	INOUT	Existing type
@MAXSOLICITORAMOUNT	money	INOUT	Max solicitor amount
@RELATIONSHIPID	uniqueidentifier	INOUT	Relationship
@RELATIONSHIPCONTEXTID	nvarchar(73)	INOUT	Relationship Context ID
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Exchange rate ID
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@HADSPOTRATE	bit	INOUT	Had spot rate
@RATECHANGED	bit	INOUT	Rate changed
@BASEDECIMALDIGITS	tinyint	INOUT	Decimal digits
@BASEROUNDINGTYPECODE	tinyint	INOUT	Rounding type
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT	Transaction currency description
@ORIGINALGIFTTRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Original gift transaction currency
@HASRECOGNITIONCREDIT	bit	INOUT	Has recognition credit
@UPDATERECOGNITIONOPTION	tinyint	INOUT	Recognition update option
@HASNEEDEDEXCHANGERATES	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MGPLEDGE_3
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @ORIGINALGIFTID uniqueidentifier = null output,
  @ORIGINALGIFTAMOUNT money = null output,
  @ORIGINALRECEIPTAMOUNT money = null output,
  @CONSTITUENTID uniqueidentifier = null output,
  @CONSTITUENTNAME nvarchar(700) = null output,
  @MATCHINGORGANIZATIONID uniqueidentifier = null output,
  @DATE datetime = null output,
  @AMOUNT money = null output,
  @SPLITS xml = null output,
  @TSLONG bigint = 0 output,
  @MATCHINGGIFTCONDITIONID uniqueidentifier = null output,
  @MGCONDITIONTYPE nvarchar(100) = null output,
  @MAXSOLICITORAMOUNT money = null output,
  @RELATIONSHIPID uniqueidentifier = null output,
  @RELATIONSHIPCONTEXTID nvarchar(73) = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @TRANSACTIONCURRENCYID uniqueidentifier = null output,
  @BASEEXCHANGERATEID uniqueidentifier = null output,
  @EXCHANGERATE decimal(20,8) = null output,
  @HADSPOTRATE bit = null output,
  @RATECHANGED bit = null output,
  @BASEDECIMALDIGITS tinyint = null output,
  @BASEROUNDINGTYPECODE tinyint = null output,
  @TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output,
  @ORIGINALGIFTTRANSACTIONCURRENCYID uniqueidentifier = null output,
  @HASRECOGNITIONCREDIT bit = null output,
  @UPDATERECOGNITIONOPTION tinyint = null output,
  @HASNEEDEDEXCHANGERATES bit = null output
)
as 
  set nocount on;

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

  select
    @DATALOADED = 1,
    @ORIGINALGIFTID = RMG.MGSOURCEREVENUEID,
    @MATCHINGORGANIZATIONID = REVENUE.CONSTITUENTID,
    @DATE = REVENUE.DATE,
    @AMOUNT = REVENUE.TRANSACTIONAMOUNT,
    @TSLONG = REVENUE.TSLONG,
    @MATCHINGGIFTCONDITIONID = RMG.MATCHINGGIFTCONDITIONID,
    @MGCONDITIONTYPE = dbo.UFN_MATCHINGGIFTCONDITIONTYPECODE_GETDESCRIPTION(MGC.MATCHINGGIFTCONDITIONTYPECODEID),
    @MAXSOLICITORAMOUNT = coalesce((select max(AMOUNT) from dbo.REVENUESOLICITOR where REVENUESPLITID = REVENUE.ID), 0),
    @RELATIONSHIPID = RMG.RELATIONSHIPID,
    @BASECURRENCYID = REVENUE.BASECURRENCYID,
    @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
    @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
    @EXCHANGERATE = 
      case
        when REVENUE.BASEEXCHANGERATEID is not null
          then CURRENCYEXCHANGERATE.RATE
        when REVENUE.TRANSACTIONCURRENCYID = REVENUE.BASECURRENCYID
          then 1
        else 0
      end,
    @HADSPOTRATE =  
      case
        when CURRENCYEXCHANGERATE.TYPECODE = 2
          then 1
        else 0
      end,
    @RATECHANGED = 0,
    @BASEDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
    @BASEROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
    @TRANSACTIONCURRENCYDESCRIPTION = dbo.UFN_CURRENCY_GETDESCRIPTION(REVENUE.TRANSACTIONCURRENCYID)
  from dbo.REVENUE
    left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
    left join dbo.REVENUEMATCHINGGIFT RMG on REVENUE.ID = RMG.ID
    left join dbo.MATCHINGGIFTCONDITION MGC on RMG.MATCHINGGIFTCONDITIONID = MGC.ID
    inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.BASECURRENCYID
    left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
            where REVENUE.ID = @ID and REVENUE.TRANSACTIONTYPECODE = 3;

  set @SPLITS = (
    select  
      SPLITS.ID, 
      SPLITS.DESIGNATIONID,
      SPLITS.TRANSACTIONAMOUNT AMOUNT, 
      @ID REVENUEID,
      SPLITS.APPLICATIONCODE,  
      SPLITS.TYPECODE,
      SPLITS.TRANSACTIONCURRENCYID,
      SPLITS.BASEEXCHANGERATEID
    from dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLITS
    for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
  );

  select 
    @ORIGINALGIFTAMOUNT = REVENUE.TRANSACTIONAMOUNT,
    @ORIGINALRECEIPTAMOUNT = REVENUE.RECEIPTAMOUNT,
    @CONSTITUENTID = REVENUE.CONSTITUENTID,
    @CONSTITUENTNAME = NF.NAME,
    @ORIGINALGIFTTRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
  from dbo.REVENUE
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
  where REVENUE.ID = @ORIGINALGIFTID

  set @RELATIONSHIPCONTEXTID = cast(@CONSTITUENTID as nvarchar(36)) + cast(@MATCHINGORGANIZATIONID as nvarchar(36)) + cast(1 as nvarchar(1))

  select @HASRECOGNITIONCREDIT = count(1) 
  from dbo.REVENUERECOGNITION
    inner join dbo.REVENUESPLIT on REVENUERECOGNITION.REVENUESPLITID = REVENUESPLIT.ID
  where REVENUESPLIT.REVENUEID = @ID;
  set @UPDATERECOGNITIONOPTION = 2;   --update based on defaults


  set @HASNEEDEDEXCHANGERATES = dbo.UFN_REVENUE_HASNEEDEDRATES(@ORIGINALGIFTID);

  return 0;