USP_DATAFORMTEMPLATE_EDITLOAD_PLEDGEWRITEOFF2

The load procedure used by the edit dataform template "Pledge Write-off Edit Form 2"

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.
@CONSTITUENTNAME	nvarchar(700)	INOUT
@PLEDGEDATE	datetime	INOUT	Pledged on
@PLEDGEBALANCE	money	INOUT	Pledge balance
@DATE	datetime	INOUT	Date
@WRITEOFFTOTALAMOUNT	money	INOUT	Amount
@POSTSTATUSCODE	tinyint	INOUT	GL post status
@POSTDATE	datetime	INOUT	GL post date
@REASON	nvarchar(300)	INOUT	Details
@INSTALLMENTS	xml	INOUT
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@REASONCODEID	uniqueidentifier	INOUT	Reason code
@TRANSACTIONTYPECODE	tinyint	INOUT	Transaction type code
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@ALLOWGLDISTRIBUTIONS	bit	INOUT	Allow GL distributions

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PLEDGEWRITEOFF2
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @CONSTITUENTNAME nvarchar(700) = null output,
                        @PLEDGEDATE datetime = null output,
                        @PLEDGEBALANCE money = null output,
                        @DATE datetime = null output,
                        @WRITEOFFTOTALAMOUNT money = null output,
                        @POSTSTATUSCODE tinyint = null output,
                        @POSTDATE datetime = null output,
                        @REASON nvarchar(300) = null output,
                        @INSTALLMENTS xml = null output,
                        @TSLONG bigint = 0 output,
                        @REASONCODEID uniqueidentifier = null output,
                        @TRANSACTIONTYPECODE tinyint = null output,
                        @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                        @ALLOWGLDISTRIBUTIONS bit = null output
                    )
                    as
                    begin

                        set nocount on;

                        declare @REVENUEID uniqueidentifier;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        select
                            @CONSTITUENTNAME = NF.NAME,
                            @PLEDGEDATE = FINANCIALTRANSACTION.DATE,
                            @PLEDGEBALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID),
                            @DATE = WRITEOFF.DATE,
                            @WRITEOFFTOTALAMOUNT = (select sum(TRANSACTIONAMOUNT) from dbo.INSTALLMENTWRITEOFF where WRITEOFFID = WRITEOFF.ID),
                            @POSTSTATUSCODE = WRITEOFF.POSTSTATUSCODE,
                            @POSTDATE = WRITEOFF.POSTDATE,
                            @REASON = WRITEOFF.REASON,
                            @TSLONG = WRITEOFF.TSLONG,
                            @DATALOADED = 1,
                            @REASONCODEID = WRITEOFF.REASONCODEID,
                            @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
                            @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                            @REVENUEID = WRITEOFF.REVENUEID,
                            @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(FINANCIALTRANSACTION.PDACCOUNTSYSTEMID) -- Check GL business rule for this account system and set to 'Do not post' if needed.

                        from 
                            dbo.WRITEOFF
                        inner join 
                            dbo.FINANCIALTRANSACTION on WRITEOFF.REVENUEID = FINANCIALTRANSACTION.ID
                        inner join
                            dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
                        left join
                            dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
                        where 
                            WRITEOFF.ID = @ID and FINANCIALTRANSACTION.DELETEDON is null;

                        if @DATALOADED = 1
                            set @INSTALLMENTS =
                                (
                                    select 
                                        ID, 
                                        DATE,
                                        TRANSACTIONAMOUNT as AMOUNT,
                                        BALANCE,
                                        WRITEOFFAMOUNT,
                                        SEQUENCE,
                                        TRANSACTIONCURRENCYID
                                    from dbo.UFN_WRITEOFF_GETINSTALLMENTSFOREDIT(@ID) WRITEOFFINSTALLMENT
                                    order by SEQUENCE
                                    for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
                                );

            return 0;

                    end