USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTORIGIN

The load procedure used by the view dataform template "Constituent Origin 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.
@INFOSOURCE	nvarchar(100)	INOUT	Constituent source
@DATEADDED	datetime	INOUT	Date added
@REVENUEID	uniqueidentifier	INOUT	Revenue ID
@REVENUE_TYPE	nvarchar(50)	INOUT	Revenue type
@REVENUE_DATE	datetime	INOUT	Revenue date
@REVENUE_AMOUNT	money	INOUT	Revenue amount
@REVENUE_FINDERNUMBER	bigint	INOUT	Revenue finder number
@REVENUE_APPEAL	nvarchar(100)	INOUT	Revenue appeal
@REVENUE_MAILING	nvarchar(100)	INOUT	Revenue mailing
@REVENUE_CHANNEL	nvarchar(100)	INOUT	Revenue channel
@REVENUE_TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Revenue transaction currency ID

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTORIGIN
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @INFOSOURCE nvarchar(100) = null output,
    @DATEADDED datetime = null output,
    @REVENUEID uniqueidentifier = null output,
    @REVENUE_TYPE nvarchar(50) = null output,
    @REVENUE_DATE datetime = null output,
    @REVENUE_AMOUNT money = null output,
    @REVENUE_FINDERNUMBER bigint = null output,
    @REVENUE_APPEAL nvarchar(100) = null output,
    @REVENUE_MAILING nvarchar(100) = null output,
    @REVENUE_CHANNEL nvarchar(100) = null output,
    @REVENUE_TRANSACTIONCURRENCYID uniqueidentifier = null output
) as
    set nocount on;

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @INFOSOURCE = case 
            when [ORIGINCODE] <> 0 then dbo.UFN_CONSTITUENTORIGINATION_ORIGINCODE_GETDESCRIPTION([ORIGINCODE])
            else dbo.UFN_INFOSOURCECODE_GETDESCRIPTION(CONSTITUENTORIGINATION.INFOSOURCECODEID)
        end,
        @DATEADDED = CONSTITUENT.DATEADDED,
        @REVENUEID = CONSTITUENTORIGINATION.REVENUEID,
        @REVENUE_TYPE = FINANCIALTRANSACTION.TYPE,
        @REVENUE_DATE = cast(FINANCIALTRANSACTION.DATE as datetime),
        @REVENUE_AMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
        @REVENUE_FINDERNUMBER = REVENUE_EXT.FINDERNUMBER,
        @REVENUE_APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE_EXT.APPEALID),
        @REVENUE_MAILING = dbo.UFN_MKTSEGMENTATION_GETNAME(REVENUE_EXT.MAILINGID),
        @REVENUE_CHANNEL = dbo.UFN_CHANNELCODE_GETDESCRIPTION(REVENUE_EXT.CHANNELCODEID),
        @REVENUE_TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
    from 
        dbo.CONSTITUENT
    left join 
        dbo.CONSTITUENTORIGINATION on CONSTITUENTORIGINATION.ID = CONSTITUENT.ID
    left join
        dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = CONSTITUENTORIGINATION.REVENUEID and FINANCIALTRANSACTION.DELETEDON is null
    left join
        dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    left join
        dbo.APPEAL on APPEAL.ID = REVENUE_EXT.APPEALID
    where 
        CONSTITUENT.ID = @ID;

    return 0;