USP_DATAFORMTEMPLATE_EDITLOAD_PLANNEDGIFTREVENUEADJUST

The load procedure used by the edit dataform template "Posted Planned Gift Revenue Edit Form"

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.
@CONSTITUENTID	uniqueidentifier	INOUT
@CONSTITUENTNAME	nvarchar(255)	INOUT	Constituent
@DATE	datetime	INOUT	Date
@AMOUNT	money	INOUT	Amount
@POSTSTATUSCODE	tinyint	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@SPLITS	xml	INOUT	Designations
@FINDERNUMBER	bigint	INOUT	Finder number
@SOURCECODE	nvarchar(50)	INOUT	Source code
@APPEALID	uniqueidentifier	INOUT	Appeal
@BENEFITS	xml	INOUT	Benefits
@BENEFITSWAIVED	bit	INOUT	Benefits waived
@GIVENANONYMOUSLY	bit	INOUT	Planned gift is anonymous
@MAILINGID	uniqueidentifier	INOUT	Effort
@CHANNELCODEID	uniqueidentifier	INOUT	Inbound channel
@DONOTACKNOWLEDGE	bit	INOUT	Do not acknowledge
@PLEDGESUBTYPEID	uniqueidentifier	INOUT	Subtype
@MAXSOFTCREDITAMOUNT	money	INOUT	Max soft credit amount
@MAXSOLICITORAMOUNT	money	INOUT	Max solicitor amount
@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.
@MAINSINGLEDESIGNATIONID	uniqueidentifier	INOUT	Designation
@FINDERNUMBERVALID	bit	INOUT	FINDERNUMBERVALID
@OPPORTUNITYID	uniqueidentifier	INOUT	Opportunity
@OPPORTUNITYCONSTITUENTNAME	nvarchar(154)	INOUT
@OPPORTUNITYASKDATE	datetime	INOUT
@OPPORTUNITYASKAMOUNT	money	INOUT
@MAXSOLICITORSPLITAMOUNTS	xml	INOUT	Max solicitor split amounts
@REFERENCE	nvarchar(255)	INOUT	Reference
@CATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@PERCENTAGEBENEFITS	xml	INOUT	Benefits

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PLANNEDGIFTREVENUEADJUST
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @CONSTITUENTID uniqueidentifier = null output,
  @CONSTITUENTNAME nvarchar(255) = null output,
  @DATE datetime = null output,
  @AMOUNT money = null output,
  @POSTSTATUSCODE tinyint = null output,
  @POSTDATE datetime = null output,
  @SPLITS xml = null output,
  @FINDERNUMBER bigint = null output,
  @SOURCECODE nvarchar(50) = 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,
  @DONOTACKNOWLEDGE bit = null output,
  @PLEDGESUBTYPEID uniqueidentifier = null output,
  @MAXSOFTCREDITAMOUNT money = null output,
  @MAXSOLICITORAMOUNT money = null output,
  @TSLONG bigint = 0 output,
  @MAINSINGLEDESIGNATIONID uniqueidentifier = null output,
  @FINDERNUMBERVALID bit = null output,
  @OPPORTUNITYID uniqueidentifier = null output,
  @OPPORTUNITYCONSTITUENTNAME nvarchar(154) = null output,
  @OPPORTUNITYASKDATE datetime = null output,
  @OPPORTUNITYASKAMOUNT money = null output,
  @MAXSOLICITORSPLITAMOUNTS xml = null output,
  @REFERENCE nvarchar(255) = null output,
  @CATEGORYCODEID uniqueidentifier = null output,
  @PERCENTAGEBENEFITS xml = null output
)
as
begin
  set nocount on;

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

  declare @LASTINSTALLMENT uniqueidentifier;

  select 
    @DATALOADED = 1,
    @CONSTITUENTID = CONSTITUENT.ID,
    @CONSTITUENTNAME = CONSTITUENT.NAME,
    @DATE = REVENUE.DATE,
    @AMOUNT = REVENUE.AMOUNT,
    @POSTSTATUSCODE = 0,
    @MAXSOFTCREDITAMOUNT = 0,
    @MAXSOLICITORAMOUNT = 0,
    @POSTDATE = REVENUE.POSTDATE,
    @TSLONG = REVENUE.TSLONG,                            
    @FINDERNUMBER = FINDERNUMBER,
    @SOURCECODE = SOURCECODE,
    @APPEALID = APPEALID,
    @BENEFITSWAIVED = BENEFITSWAIVED,
    @GIVENANONYMOUSLY = GIVENANONYMOUSLY,
    @MAILINGID = MAILINGID,
    @CHANNELCODEID = CHANNELCODEID,
    @DONOTACKNOWLEDGE = DONOTACKNOWLEDGE,
    @PLEDGESUBTYPEID = PLEDGESUBTYPEID,
    @MAXSOLICITORSPLITAMOUNTS = dbo.UFN_REVENUEDETAIL_GETMAXSOLICITORSPLITAMOUNTS_TOITEMLISTXML(@ID),
    @REFERENCE = REVENUEREFERENCE.REFERENCE,
    @CATEGORYCODEID = (select top 1 GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID where REVENUEID = REVENUE.ID)
  from
    dbo.REVENUE
  left join
    dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
  inner join
    dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
  inner join
    dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
  left join
    dbo.REVENUEREFERENCE on REVENUEREFERENCE.ID = REVENUE.ID
  where
    REVENUE.ID = @ID and REVENUE.TRANSACTIONTYPECODE = 4;

  if @DATALOADED = 1
  begin

    select top 1
      @OPPORTUNITYID = RO.OPPORTUNITYID,
      @OPPORTUNITYCONSTITUENTNAME = OC.NAME,
      @OPPORTUNITYASKDATE = O.ASKDATE,
      @OPPORTUNITYASKAMOUNT = O.ASKAMOUNT
    from
      dbo.REVENUESPLIT
    left outer join
      dbo.REVENUEOPPORTUNITY RO on RO.ID = REVENUESPLIT.ID
    left outer join
      dbo.OPPORTUNITY O on O.ID = RO.OPPORTUNITYID
    left outer join
      dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
    left outer join
      dbo.CONSTITUENT OC on OC.ID = PP.PROSPECTID
    where REVENUESPLIT.REVENUEID = @ID

    set @SPLITS = dbo.UFN_REVENUE_GETSPLITS_TOITEMLISTXML(@ID);
    exec dbo.USP_REVENUE_GETBENEFITSSPLIT @ID, @BENEFITS output, @PERCENTAGEBENEFITS output

    if @FINDERNUMBER = 0
      set @FINDERNUMBER = null;

    if (not @FINDERNUMBER is null)
      set @FINDERNUMBERVALID = 1;
    else
      set @FINDERNUMBERVALID = 0;

  end

  return 0;

end