USP_DATAFORMTEMPLATE_VIEW_PLANNEDGIFTPAYOUTGLMAPPINGCALCULATE

The load procedure used by the view dataform template "Planned Gift Payout 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_PLANNEDGIFTPAYOUTGLMAPPINGCALCULATE
(
    @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.PLANNEDGIFTPAYOUTGLDISTRIBUTION 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_GETPAYOUTDESCRIPTION(@ID)
            when 2 then 'Unrestricted'
            when 3 then T3.LONGDESCRIPTION 
            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