USP_CONSITUENTREVENUEHISTORYEXPANDEDVIEW_GET

            CREATE procedure dbo.USP_CONSITUENTREVENUEHISTORYEXPANDEDVIEW_GET
            (
                @ID uniqueidentifier,    
                @CURRENTAPPUSERID uniqueidentifier = null,    
                @DATALOADED bit = 0 output,
                @CAMPAIGNS nvarchar(max) = null output,
                @APPEAL nvarchar(100) = null output,
                @REVENUECATEGORIES nvarchar(max) = 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,
                @OPPORTUNITYCOUNT integer = null output,
                @OPPORTUNITYID uniqueidentifier = null output,
                @OPPORTUNITYNAME nvarchar(300) = null output,
                @OPPORTUNITYDATE datetime = null output,
                @OPPORTUNITYAMOUNT money = null output,
                @APPBASECURRENCY uniqueidentifier = null output
            )
            as
            begin
                declare @APPEALID uniqueidentifier;                

                select 
                    @APPBASECURRENCY = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                    @CAMPAIGNS  = dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME),
                    @APPEAL = APPEAL.NAME,
                    @REVENUECATEGORIES = dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME),
                    @NAMINGOPPORTUNITIES = dbo.UDA_BUILDLIST(distinct NAMINGOPPORTUNITY.NAME),
                    @TRIBUTES = dbo.UDA_BUILDLIST(distinct TRIBUTE.TRIBUTETEXT),
                    @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_REVENUEID(FINANCIALTRANSACTION.ID) RSITE
                                inner join dbo.SITE on SITE.ID = RSITE.SITEID),
                    @APPEALID = REVENUE_EXT.APPEALID
                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.DESIGNATION
                  on DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID
                left join dbo.REVENUESPLITCAMPAIGN
                  on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                left join dbo.CAMPAIGN
                  on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                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.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 = FINANCIALTRANSACTION.ID
                left join dbo.TRIBUTE
                  on TRIBUTE.ID = REVENUETRIBUTE.TRIBUTEID
                where FINANCIALTRANSACTION.ID = @ID
                    and FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                group by FINANCIALTRANSACTION.ID,APPEAL.NAME,REVENUE_EXT.GIVENANONYMOUSLY,FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,REVENUE_EXT.APPEALID

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

                select 
                    @OPPORTUNITYCOUNT = COUNT(distinct REVENUEOPPORTUNITY.OPPORTUNITYID)
                from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT
                    on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                left join dbo.REVENUEOPPORTUNITY
                  on FINANCIALTRANSACTIONLINEITEM.ID = REVENUEOPPORTUNITY.ID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

                if @OPPORTUNITYCOUNT = 1
                  select top 1
                      @OPPORTUNITYID = OPPORTUNITY.ID,
                      @OPPORTUNITYNAME = dbo.UFN_CONSTITUENT_BUILDNAME(PROSPECTPLAN.PROSPECTID),
                      @OPPORTUNITYDATE = OPPORTUNITY.ASKDATE,
                      @OPPORTUNITYAMOUNT = OPPORTUNITY.TRANSACTIONAMOUNT
                  from dbo.FINANCIALTRANSACTIONLINEITEM
                  inner join dbo.REVENUESPLIT_EXT
                    on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                  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.FINANCIALTRANSACTIONID = @ID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

                return 0;                
            end