USP_DATAFORMTEMPLATE_VIEW_REVENUEPROFILE

The load procedure used by the view dataform template "Revenue Profile View 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.
@TRANSACTIONTYPECODE	tinyint	INOUT	Transaction type code
@TRANSACTIONTYPE	nvarchar(28)	INOUT	Transaction type
@PAYMENTAMOUNT	money	INOUT	Payment amount
@AMOUNT	money	INOUT	Amount
@BALANCE	money	INOUT	Balance
@TOTALPAID	money	INOUT	Total paid
@PASTDUE	money	INOUT	Past due
@MATCHEDREVENUE	nvarchar(255)	INOUT	Matched revenue
@MATCHEDREVENUEID	uniqueidentifier	INOUT	Matched revenue ID
@PAYMENTID	uniqueidentifier	INOUT	Payment ID
@NEXTTRANSACTION	datetime	INOUT	Next transaction
@ISPENDING	bit	INOUT	Is pending
@PENDINGBATCHNUMBER	nvarchar(100)	INOUT	Pending batch number
@DATE	datetime	INOUT	Date
@POSTDATE	datetime	INOUT	Post date
@POSTSTATUS	nvarchar(50)	INOUT	Post status
@BATCHNUMBER	nvarchar(100)	INOUT	Batch number
@APPEAL	nvarchar(100)	INOUT	Appeal
@SENDPLEDGEREMINDER	bit	INOUT	Send reminders
@FREQUENCY	nvarchar(100)	INOUT	Frequency
@ENDDATE	datetime	INOUT	End date
@STARTDATE	datetime	INOUT	Start date
@STATUS	nvarchar(255)	INOUT	Status
@SOURCECODE	nvarchar(50)	INOUT	Source code
@RECEIPTAMOUNT	money	INOUT	Receipt amount
@GIVENANONYMOUSLY	bit	INOUT	Given anonymously
@MAILING	nvarchar(100)	INOUT	Effort
@CHANNEL	nvarchar(100)	INOUT	Inbound channel
@PLEDGESUBTYPE	nvarchar(100)	INOUT	Subtype
@RECEIPTNUMBER	int	INOUT	Receipt number
@RECEIPTSTATUS	nvarchar(20)	INOUT	Receipt status
@RECEIPTDATE	datetime	INOUT	Receipt date
@ACKNOWLEDGESTATUS	nvarchar(20)	INOUT	Acknowledged status

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUEPROFILE
(
  @ID uniqueidentifier,    
  @DATALOADED bit = 0 output,
  @TRANSACTIONTYPECODE tinyint = null output,
  @TRANSACTIONTYPE nvarchar(28) = null output,
  @PAYMENTAMOUNT money = null output,
  @AMOUNT money = null output,
  @BALANCE money = null output,
  @TOTALPAID money = null output,
  @PASTDUE money = null output,
  @MATCHEDREVENUE nvarchar(255) = null output,
  @MATCHEDREVENUEID uniqueidentifier = null output,
  @PAYMENTID uniqueidentifier = null output,
  @NEXTTRANSACTION datetime = null output,
  @ISPENDING bit = null output,
  @PENDINGBATCHNUMBER nvarchar(100) = null output,
  @DATE datetime = null output,
  @POSTDATE datetime = null output,
  @POSTSTATUS nvarchar(50) = null output,
  @BATCHNUMBER nvarchar(100) = null output,
  @APPEAL nvarchar(100) = null output,
  @SENDPLEDGEREMINDER bit = null output,
  @FREQUENCY nvarchar(100) = null output,
  @ENDDATE datetime = null output,
  @STARTDATE datetime = null output,
  @STATUS nvarchar(255) = null output,
  @SOURCECODE nvarchar(50) = null output,
  @RECEIPTAMOUNT money = null output,
  @GIVENANONYMOUSLY bit = null output,
  @MAILING nvarchar(100) = null output,
  @CHANNEL nvarchar(100) = null output,
  @PLEDGESUBTYPE nvarchar(100) = null output,
  @RECEIPTNUMBER int = null output,
  @RECEIPTSTATUS nvarchar(20) = null output,
  @RECEIPTDATE datetime = null output,
  @ACKNOWLEDGESTATUS nvarchar(20) = null output
)
as

  set nocount on;

  set @DATALOADED = 0;

  select 
    @TRANSACTIONTYPECODE = TRANSACTIONTYPECODE 
  from dbo.REVENUE 
  where ID = @ID;

  --Get general fields that are valid for all views

  select
    @DATALOADED = 1,
    @DATE = cast(FINANCIALTRANSACTION.DATE as datetime), 
    @TRANSACTIONTYPE = FINANCIALTRANSACTION.TYPE,
    @AMOUNT = FINANCIALTRANSACTION.BASEAMOUNT,
    @POSTDATE = cast(FINANCIALTRANSACTION.POSTDATE as datetime),
    @POSTSTATUS = case 
              when REVENUEPOSTED.ID is not null and ADJUSTMENT.POSTSTATUSCODE = 1 then 'Posted (adjustment pending)'
              when REVENUEPOSTED.ID is not null then 'Posted'
              when FINANCIALTRANSACTION.POSTSTATUSCODE = 3 then 'Do not post'
              else 'Not posted'
            end,
    @BATCHNUMBER = REVENUE_EXT.BATCHNUMBER,
    @RECEIPTAMOUNT = REVENUE_EXT.RECEIPTAMOUNT,
    @APPEAL = '',
    @RECEIPTNUMBER = case dbo.UFN_REVENUE_GETRECEIPTSTATUS(FINANCIALTRANSACTION.ID)
      when 'Receipted' then REVENUERECEIPT.RECEIPTNUMBER
      else null end,
    @RECEIPTDATE = case dbo.UFN_REVENUE_GETRECEIPTSTATUS(FINANCIALTRANSACTION.ID)
      when 'Receipted' then REVENUERECEIPT.RECEIPTDATE
      else null end,
    @RECEIPTSTATUS = dbo.UFN_REVENUE_GETRECEIPTSTATUS(FINANCIALTRANSACTION.ID),
    @ACKNOWLEDGESTATUS = dbo.UFN_REVENUE_GETACKNOWLEDGESTATUS(FINANCIALTRANSACTION.ID)
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  left join dbo.ADJUSTMENT on FINANCIALTRANSACTION.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
  left join dbo.REVENUERECEIPT on FINANCIALTRANSACTION.ID = REVENUERECEIPT.ID
  left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = FINANCIALTRANSACTION.ID
  where FINANCIALTRANSACTION.ID = @ID
    and FINANCIALTRANSACTION.DELETEDON is null

  if @TRANSACTIONTYPECODE = 1 -- Pledge

  begin
    select 
      @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID),
      @TOTALPAID = dbo.UFN_PLEDGE_GETAMOUNTPAID(FINANCIALTRANSACTION.ID),
      @PASTDUE = dbo.UFN_PLEDGE_GETPASTDUEAMOUNT(FINANCIALTRANSACTION.ID),
      @ISPENDING = REVENUESCHEDULE.ISPENDING,
      @SENDPLEDGEREMINDER = REVENUESCHEDULE.SENDPLEDGEREMINDER,
      @APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE_EXT.APPEALID),
      @SOURCECODE = REVENUE_EXT.SOURCECODE,
      @GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY,
      @MAILING = dbo.UFN_MKTSEGMENTATION_GETNAME(REVENUE_EXT.MAILINGID),
      @CHANNEL = CHANNELCODE.DESCRIPTION,
      @PLEDGESUBTYPE = PLEDGESUBTYPE.NAME
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
    left join dbo.PLEDGESUBTYPE on PLEDGESUBTYPE.ID = REVENUESCHEDULE.PLEDGESUBTYPEID
    left join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE_EXT.CHANNELCODEID
    where FINANCIALTRANSACTION.ID = @ID
        and FINANCIALTRANSACTION.DELETEDON is null;

    if @ISPENDING = 1
      select top 1
        @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER 
      from dbo.BATCH
      inner join dbo.BATCHREVENUE on BATCHREVENUE.BATCHID = BATCH.ID
      inner join dbo.BATCHREVENUEAPPLICATION as [APP] on [APP].BATCHREVENUEID = BATCHREVENUE.ID and [APP].WASGENERATED = 1
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = [APP].REVENUEID
      inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
      where BATCH.STATUSCODE = 0 and FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.DELETEDON is null;
  end

  if @TRANSACTIONTYPECODE = 3
  begin
    select 
      @DATALOADED = 1,
      @TOTALPAID = coalesce((select sum(INSTALLMENTPAYMENT.AMOUNT) from dbo.INSTALLMENTPAYMENT where INSTALLMENTPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID), 0),
      @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID),
      @MATCHEDREVENUEID = RMG.MGSOURCEREVENUEID,
      @ISPENDING = REVENUESCHEDULE.ISPENDING,
      @APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE_EXT.APPEALID),
      @SOURCECODE = REVENUE_EXT.SOURCECODE
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    inner join dbo.REVENUEMATCHINGGIFT RMG on FINANCIALTRANSACTION.ID = RMG.ID
    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
    where FINANCIALTRANSACTION.ID = @ID
        and FINANCIALTRANSACTION.DELETEDON is null;

    select
      @MATCHEDREVENUE = FINANCIALTRANSACTION.TYPE + ' for ' + CONSTITUENT.NAME
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
    where FINANCIALTRANSACTION.ID = @MATCHEDREVENUEID and FINANCIALTRANSACTION.DELETEDON is null

    select
      top 1 
      @PAYMENTID = FINANCIALTRANSACTION.ID 
    from dbo.INSTALLMENTPAYMENT
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = INSTALLMENTPAYMENT.PAYMENTID
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    order by cast(FINANCIALTRANSACTION.DATE as datetime) desc

    if @ISPENDING = 1
      select top 1
        @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER 
      from dbo.BATCH
      inner join dbo.BATCHREVENUE on BATCHREVENUE.BATCHID = BATCH.ID
      inner join dbo.BATCHREVENUEAPPLICATION as [APP] on [APP].BATCHREVENUEID = BATCHREVENUE.ID and [APP].WASGENERATED = 1
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = [APP].REVENUEID
      inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
      where BATCH.STATUSCODE = 0 and FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.DELETEDON is null
  end

  if @TRANSACTIONTYPECODE = 2
  begin
    select
      @DATALOADED = 1,
      @NEXTTRANSACTION = case when REVENUESCHEDULE.NEXTTRANSACTIONDATE > REVENUESCHEDULE.ENDDATE then null else REVENUESCHEDULE.NEXTTRANSACTIONDATE end,
      @TOTALPAID = (select sum(AMOUNT) from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = FINANCIALTRANSACTION.ID and TYPECODE = 0),
      @FREQUENCY = REVENUESCHEDULE.FREQUENCY,
      @STARTDATE = REVENUESCHEDULE.STARTDATE,
      @ENDDATE = REVENUESCHEDULE.ENDDATE,
      @STATUS = REVENUESCHEDULE.STATUS,
      @ISPENDING = REVENUESCHEDULE.ISPENDING,
      @APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE_EXT.APPEALID),
      @SOURCECODE = REVENUE_EXT.SOURCECODE,
      @GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY,
      @MAILING = dbo.UFN_MKTSEGMENTATION_GETNAME(REVENUE_EXT.MAILINGID),
      @CHANNEL = CHANNELCODE.DESCRIPTION
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
    left join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE_EXT.CHANNELCODEID
    where FINANCIALTRANSACTION.ID = @ID
        and FINANCIALTRANSACTION.DELETEDON is null;

    if @ISPENDING = 1
      select top 1
        @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER 
      from dbo.BATCH
      inner join dbo.BATCHREVENUE on BATCHREVENUE.BATCHID = BATCH.ID
      inner join dbo.BATCHREVENUEAPPLICATION as [APP] on [APP].BATCHREVENUEID = BATCHREVENUE.ID  and [APP].WASGENERATED = 1
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = [APP].REVENUEID
      inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
      where BATCH.STATUSCODE = 0 and FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.DELETEDON is null
  end

  return 0;