USP_DATAFORMTEMPLATE_VIEW_CONSITUENTHISTORYEXPANDED_SPLIT_VIEW

The load procedure used by the view dataform template "Constituent Revenue History Expanded Revenue Split View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CAMPAIGNS	nvarchar(max)	INOUT	Campaigns
@APPEAL	nvarchar(100)	INOUT	Appeal
@REVENUECATEGORIES	nvarchar(50)	INOUT	Revenue category
@NAMINGOPPORTUNITIES	nvarchar(max)	INOUT	Naming opportunities
@TRIBUTES	nvarchar(max)	INOUT	Tributes
@GIVENANONYMOUSLY	bit	INOUT	Anonymous
@ACKNOWLEDGEMENTDATE	datetime	INOUT	Last Acknowledgement date
@SITES	nvarchar(max)	INOUT	Sites
@OPPORTUNITYID	uniqueidentifier	INOUT	OPPORTUNITYID
@OPPORTUNITYNAME	nvarchar(300)	INOUT	OPPORTUNITYNAME
@OPPORTUNITYDATE	datetime	INOUT	OPPORTUNITYDATE
@OPPORTUNITYAMOUNT	money	INOUT	OPPORTUNITYAMOUNT
@APPBASECURRENCY	uniqueidentifier	INOUT	APPBASECURRENCY

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSITUENTHISTORYEXPANDED_SPLIT_VIEW
                (
                    @ID uniqueidentifier,    
                    @CURRENTAPPUSERID uniqueidentifier = null,    
                    @DATALOADED bit = 0 output,
                    @CAMPAIGNS nvarchar(max) = null output,
                    @APPEAL nvarchar(100) = null output,
                    @REVENUECATEGORIES nvarchar(50) = null output,
                    @NAMINGOPPORTUNITIES nvarchar(max) = null output,
                    @TRIBUTES nvarchar(max) = null output,
                    @GIVENANONYMOUSLY bit = null output,
                    @ACKNOWLEDGEMENTDATE datetime = null output,
                    @SITES nvarchar(max) = null output,
                    @OPPORTUNITYID uniqueidentifier = null output,
                    @OPPORTUNITYNAME nvarchar(300) = null output,
                    @OPPORTUNITYDATE datetime = null output,
                    @OPPORTUNITYAMOUNT money = null output,
                    @APPBASECURRENCY uniqueidentifier = null output
                )
                as
                set nocount on;

                /*User Transaction instead
                declare @APPUSERBASEID uniqueidentifier;
                set @APPUSERBASEID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)
                set @APPBASECURRENCY = @APPUSERBASEID
                */

                declare @APPEALID uniqueidentifier;
                declare @REVENUEID uniqueidentifier;

                select
                    @APPBASECURRENCY = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                    @APPEAL = APPEAL.NAME,
                    @REVENUECATEGORIES = GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME,
                    @GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY,
                    @ACKNOWLEDGEMENTDATE = (select max(REVENUELETTER.ACKNOWLEDGEDATE)
                                            from dbo.REVENUELETTER 
                                            where REVENUEID = FINANCIALTRANSACTION.ID and ACKNOWLEDGEDATE is not null),
                    @SITES = (select dbo.UDA_BUILDLIST(SITE.NAME)
                                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) RSITE
                                inner join dbo.SITE on SITE.ID = RSITE.SITEID),

                    @OPPORTUNITYID = OPPORTUNITY.ID,
                    @OPPORTUNITYNAME = dbo.UFN_CONSTITUENT_BUILDNAME(PROSPECTPLAN.PROSPECTID),
                    @OPPORTUNITYDATE = OPPORTUNITY.ASKDATE,
                    @OPPORTUNITYAMOUNT = OPPORTUNITY.AMOUNT,
                    @APPEALID = REVENUE_EXT.APPEALID,
                    @REVENUEID = FINANCIALTRANSACTION.ID

                from dbo.FINANCIALTRANSACTION
                inner join dbo.REVENUE_EXT
                  on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM
                  on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                inner join dbo.REVENUESPLIT_EXT
                  on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                left join dbo.APPEAL
                  on APPEAL.ID = REVENUE_EXT.APPEALID
                left join dbo.REVENUECATEGORY
                  on REVENUECATEGORY.ID = FINANCIALTRANSACTIONLINEITEM.ID
                left join dbo.GLREVENUECATEGORYMAPPING
                  on GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID 
                left join dbo.REVENUEOPPORTUNITY
                  on FINANCIALTRANSACTIONLINEITEM.ID = REVENUEOPPORTUNITY.ID
                left join dbo.OPPORTUNITY
                  on REVENUEOPPORTUNITY.OPPORTUNITYID = OPPORTUNITY.ID
                left join dbo.PROSPECTPLAN 
                  on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
                where FINANCIALTRANSACTIONLINEITEM.ID = @ID
                    and FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
              and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

                select 
                    @CAMPAIGNS  = dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME),
                    @NAMINGOPPORTUNITIES = dbo.UDA_BUILDLIST(distinct NAMINGOPPORTUNITY.NAME),
                    @TRIBUTES = dbo.UDA_BUILDLIST(distinct TRIBUTE.TRIBUTETEXT)
                from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT
                  on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                left join dbo.REVENUESPLITCAMPAIGN
                  on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                left join dbo.CAMPAIGN
                  on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                left join dbo.NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT
                  on NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                left join dbo.NAMINGOPPORTUNITYRECOGNITION
                  on NAMINGOPPORTUNITYRECOGNITION.ID = NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.NAMINGOPPORTUNITYRECOGNITIONID
                left join dbo.NAMINGOPPORTUNITY
                  on NAMINGOPPORTUNITY.ID = NAMINGOPPORTUNITYRECOGNITION.NAMINGOPPORTUNITYID
                left join dbo.REVENUETRIBUTE
                  on REVENUETRIBUTE.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                left join dbo.TRIBUTE
                  on TRIBUTE.ID = REVENUETRIBUTE.TRIBUTEID
                where FINANCIALTRANSACTIONLINEITEM.ID = @ID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1


                 if @@ROWCOUNT > 0
                    set @DATALOADED = 1;


                return 0;