USP_DATALIST_PLEDGEWRITEOFFSBYTRANSACTION

This datalist returns a list of write-offs for a pledge by transaction.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_PLEDGEWRITEOFFSBYTRANSACTION
                (
                    @ID uniqueidentifier
                )
                as

                set nocount on;

                select 
                    WRITEOFF.ID,
                    WRITEOFF.DATE,
                    case 
                        when FINANCIALTRANSACTION.TYPECODE = 7 then --Auction donations do not have installments

                            (select sum(WRITEOFFSPLIT.TRANSACTIONAMOUNT) from dbo.WRITEOFFSPLIT where WRITEOFFID = WRITEOFF.ID)
                        else
                            (select sum(INSTALLMENTWRITEOFF.TRANSACTIONAMOUNT) from dbo.INSTALLMENTWRITEOFF where WRITEOFFID = WRITEOFF.ID)
                    end as AMOUNT,
                    WRITEOFF.POSTSTATUS,
                    WRITEOFF.POSTDATE,
                    WRITEOFF.POSTSTATUSCODE,
                    WRITEOFF.REASON,
                    WRITEOFFREASONCODE.CODE as REASONCODE,
                    WRITEOFFREASONCODE.DESCRIPTION as REASONDESCRIPTION,
                    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
                from 
                    dbo.WRITEOFF
                inner join 
                    dbo.FINANCIALTRANSACTION on WRITEOFF.REVENUEID = FINANCIALTRANSACTION.ID
                inner join
                    dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                left join
                    dbo.WRITEOFFREASONCODE on WRITEOFFREASONCODE.ID = WRITEOFF.REASONCODEID
                where 
                    FINANCIALTRANSACTION.ID = @ID
                    and FINANCIALTRANSACTION.DELETEDON is null
                order by 
                    WRITEOFF.DATE, WRITEOFF.DATEADDED;