USP_DATAFORMTEMPLATE_VIEW_GIFTFEEGLMAPPINGCALCULATE

The load procedure used by the view dataform template "Gift Fee GL Distribution Calculate Mapping View"
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.
@CALCULATIONS	xml	INOUT	Calculations
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_GIFTFEEGLMAPPINGCALCULATE
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @CALCULATIONS xml = null output
                )
                as
                    set nocount on

                    set @DATALOADED = 0;

                    declare @ACCOUNTID uniqueidentifier
                    select @ACCOUNTID = T1.ID from dbo.GLACCOUNT T1
                        inner join dbo.GLTRANSACTION T2 on T2.GLACCOUNTID = T1.ID
                        inner join dbo.GIFTFEEGLDISTRIBUTION T3 on T3.GLTRANSACTIONID = T2.ID
                    where T3.ID = @ID

                    declare @ttable table (SEGMENTNAME nvarchar(100), SHORTDESCRIPTION nvarchar(30), SEGMENTVALUE nvarchar(max), SEQUENCE int)
                    declare @ttable2 table (SEGMENTNAME nvarchar(100), SHORTDESCRIPTION nvarchar(30), SEGMENTVALUE nvarchar(512), SEQUENCE int, RowNum int)

                    insert into @ttable2 (SEGMENTNAME, SHORTDESCRIPTION, SEGMENTVALUE, SEQUENCE, RowNum)
                    select 
                        T4.DESCRIPTION as SEGMENTNAME, 
                        T2.SHORTDESCRIPTION,
                        case SEGMENTTYPE
                            when 1 then dbo.UFN_PDACCOUNTCODEMAP_GETGIFTFEEDESCRIPTION(@ID)
                            when 2 then 'Unrestricted'
                            when 3 then case t4.ISCONSTANTVALUE when 1 then '<Constant Value>' else  T3.LONGDESCRIPTION end 
                            when 4 then T5.NAME
                            end as SEGMENTVALUE, 
                        T4.SEQUENCE, row_number() over (order by SEQUENCE)
                    from dbo.PDACCOUNTSEGMENT T1
                        inner join dbo.PDACCOUNTSTRUCTURE T4 on T1.PDACCOUNTSTRUCTUREID = T4.ID
                        left join dbo.PDACCOUNTSEGMENTVALUE T2 on T1.PDACCOUNTSEGMENTVALUEID = T2.ID
                        left join dbo.PDACCOUNTSEGMENTMAPPING T3 on T3.PDACCOUNTSEGMENTVALUEID = T2.ID
                        left join dbo.PDCOMPOSITESEGMENTMAPPING T5 on T5.PDACCOUNTSEGMENTVALUEID = T2.ID
                    where T1.GLACCOUNTID = @ACCOUNTID

                    if exists (select SEQUENCE from @ttable2 group by SEQUENCE having count(*) > 1)
                        begin
                        declare @csv nvarchar(max)
                        declare @counter int = 0
                        declare @oldSegName nvarchar(512)=''
                        declare @oldShortDesc nvarchar(30)=''
                        declare @oldSEQ int=0

                        while @counter < (select count(*) from @ttable2)
                            begin
                            set @counter+=1
                            if (select SEQUENCE from @ttable2 where RowNum = @counter) != @oldSEQ
                                begin
                                if @oldSEQ > 0 
                                    insert into @ttable (SEGMENTNAME, SHORTDESCRIPTION, SEGMENTVALUE, SEQUENCE)
                                    values (@oldSegName, @oldShortDesc, @csv, @oldSEQ)

                                select @oldSEQ = SEQUENCE, @oldShortDesc = SHORTDESCRIPTION, @oldSegName = SEGMENTNAME, @csv = SEGMENTVALUE from @ttable2 where RowNum = @counter
                                end
                            else
                                select @csv = @csv + ', ' + SEGMENTVALUE from @ttable2 where RowNum = @counter
                            end    
                            insert into @ttable (SEGMENTNAME, SHORTDESCRIPTION, SEGMENTVALUE, SEQUENCE)
                            values (@oldSegName, @oldShortDesc, @csv, @oldSEQ)

                        end
                    else
                        insert into @ttable(SEGMENTNAME, SHORTDESCRIPTION, SEGMENTVALUE, SEQUENCE)
                        select SEGMENTNAME, SHORTDESCRIPTION, SEGMENTVALUE, SEQUENCE from @ttable2

                    set @CALCULATIONS =
                        (
                        select 
                            SEGMENTNAME, 
                            SHORTDESCRIPTION + N' - ' + SEGMENTVALUE as SEGMENTVALUE, SEQUENCE
                        from @ttable
                        order by SEQUENCE
                        for xml raw('ITEM'),elements,type,root('CALCULATIONS'),binary base64
                        )    


                    if @CALCULATIONS is not null
                        set @DATALOADED = 1

                    return 0