USP_DATAFORMTEMPLATE_ADD_PLEDGEWRITEOFF_3

            create procedure dbo.USP_DATAFORMTEMPLATE_ADD_PLEDGEWRITEOFF_3
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier,
                @REVENUEID uniqueidentifier,
                @DATE datetime,
                @WRITEOFFTOTALAMOUNT money = 0,
                @POSTSTATUSCODE tinyint = 1,
                @POSTDATE datetime = null,
                @REASON nvarchar(300) = null,
                @INSTALLMENTS xml,
                @REASONCODEID uniqueidentifier = null,
                @RECOGNITIONCREDITS xml = null,
                @BATCHID uniqueidentifier = null
                )
            as
            begin

                -- This implementation is copied from USP_DATAFORMTEMPLATE_ADD_PLEDGEWRITEOFF2 with batch ID passed to it.


                set nocount on;

                declare @CURRENTDATE datetime;
                declare @PLEDGEID uniqueidentifier;
                declare @PLEDGEAMOUNT money;
                declare @TRANSACTIONTYPECODE tinyint;
                declare @BASECURRENCYID uniqueidentifier;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASEEXCHANGERATEID uniqueidentifier;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                declare @PDACCOUNTSYSTEMID uniqueidentifier;

                if @ID is null
                    set @ID = newid();

                set @CURRENTDATE = getdate();

                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                select 
                    @PLEDGEID = FINANCIALTRANSACTION.ID,
                    @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
                    @PLEDGEAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                    @BASECURRENCYID = CURRENCYSET.BASECURRENCYID,
                    @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                    @BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
                    @ORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID,
                    @PDACCOUNTSYSTEMID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
                from 
                    dbo.FINANCIALTRANSACTION
                         inner join 
                                 dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                         inner join 
                                 dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                         inner join 
                                 dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                where
                    FINANCIALTRANSACTION.ID = @REVENUEID and FINANCIALTRANSACTION.DELETEDON is null;

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                -- Check GL business rule for this account system and set to 'Do not post' if needed.

                -- ****

                declare @ALLOWGLDISTRIBUTIONS bit;
                set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
                if @ALLOWGLDISTRIBUTIONS = 0 
                    begin
                        set @POSTSTATUSCODE = 2        -- Do not post

                        set @POSTDATE = null
                    end
                -- ****


                begin try
                    --Auction donations have more restrictions around write-offs

                    if @TRANSACTIONTYPECODE = 7
                    begin
                        declare @WRITEOFFCOUNT int = 0;
                        select @WRITEOFFCOUNT = count(*) from dbo.WRITEOFF 
                        where WRITEOFF.REVENUEID = @PLEDGEID;

                        if @WRITEOFFCOUNT > 0
                            raiserror('BBERR_AUCTIONDONATION_MULTIPLEWRITEOFFS', 13, 1);

                        if @PLEDGEAMOUNT <> @WRITEOFFTOTALAMOUNT
                            raiserror('BBERR_AUCTIONDONATION_WRITEOFFENTIREAMOUNT', 13, 1);
                    end

                    if @WRITEOFFTOTALAMOUNT < 0
                            raiserror('BBERR_WRITEOFF_VALIDAMOUNT', 13, 1);

                    insert into dbo.FINANCIALTRANSACTION (ID, DATE, PARENTID, POSTSTATUSCODE, POSTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONCURRENCYID, PDACCOUNTSYSTEMID, TYPECODE,       TRANSACTIONAMOUNT,BASEEXCHANGERATEID,ORGEXCHANGERATEID)
                    values(@ID, @DATE, @PLEDGEID, case @POSTSTATUSCODE when 0 then 2 when 1 then 1 when 2 then 3 else 2 end, case @POSTSTATUSCODE when 2 then null else isnull(@POSTDATE,getdate()) end,              @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @TRANSACTIONCURRENCYID, @PDACCOUNTSYSTEMID, 20, @WRITEOFFTOTALAMOUNT,@BASEEXCHANGERATEID,@ORGANIZATIONEXCHANGERATEID)

                    insert into dbo.WRITEOFF_EXT (ID, REASON, REASONCODEID)
                    values (@ID, isnull(@REASON,''), @REASONCODEID)

                    if @TRANSACTIONTYPECODE <> 7
                        exec dbo.USP_INSTALLMENT_WRITEOFFINSTALLMENTS @ID,@WRITEOFFTOTALAMOUNT,@CHANGEAGENTID,@CURRENTDATE,0,@INSTALLMENTS;

                    declare @LineItems table (ID uniqueidentifier, DESIGNATIONID uniqueidentifier, APPLICATIONCODE tinyint default 0, TYPECODE tinyint default 0, BASEAMOUNT money, TRANSACTIONAMOUNT money,      ORGAMOUNT money, SOURCELINEITEMID uniqueidentifier)

                    if @TRANSACTIONTYPECODE <> 7
                        insert into @LineItems (ID, DESIGNATIONID, APPLICATIONCODE, TYPECODE, BASEAMOUNT, TRANSACTIONAMOUNT, ORGAMOUNT, SOURCELINEITEMID)
                        select 
                            newid(), 
                            INSTALLMENTSPLITWRITEOFFBYDESIGNATION.DESIGNATIONID, 
                            REVENUESPLIT_EXT.APPLICATIONCODE,
                            REVENUESPLIT_EXT.TYPECODE,
                            CURRENCYVALUES.BASEAMOUNT,
                            INSTALLMENTSPLITWRITEOFFBYDESIGNATION.TRANSACTIONAMOUNT, 
                            CURRENCYVALUES.ORGANIZATIONAMOUNT, 
                            REVENUESPLIT_EXT.ID
                        from
                            (
                                select
                                    INSTALLMENTSPLIT.REVENUESPLITID,
                                    INSTALLMENTSPLIT.DESIGNATIONID,
                                    sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) TRANSACTIONAMOUNT,
                                    INSTALLMENTSPLIT.PLEDGEID
                                from
                                    dbo.INSTALLMENTSPLITWRITEOFF
                                    inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                where
                                    INSTALLMENTSPLITWRITEOFF.WRITEOFFID = @ID
                                    and INSTALLMENTSPLIT.PLEDGEID = @PLEDGEID
                                group by
                                    INSTALLMENTSPLIT.REVENUESPLITID, INSTALLMENTSPLIT.PLEDGEID,INSTALLMENTSPLIT.DESIGNATIONID
                            ) INSTALLMENTSPLITWRITEOFFBYDESIGNATION
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLITWRITEOFFBYDESIGNATION.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID and INSTALLMENTSPLITWRITEOFFBYDESIGNATION.REVENUESPLITID = REVENUESPLIT_EXT.ID
                            cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2
                                (
                                    INSTALLMENTSPLITWRITEOFFBYDESIGNATION.TRANSACTIONAMOUNT,
                                    null,
                                    @BASECURRENCYID,
                                    @BASEEXCHANGERATEID,
                                    @TRANSACTIONCURRENCYID,
                                    null,
                                    null,
                                    null,
                                    @ORGANIZATIONEXCHANGERATEID,
                                    0
                                ) CURRENCYVALUES
                        where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null

                    else if @TRANSACTIONTYPECODE = 7 --Auction donations do not have installments

                        insert into @LineItems (ID, DESIGNATIONID, BASEAMOUNT, TRANSACTIONAMOUNT, ORGAMOUNT, SOURCELINEITEMID)
                        select top 1 newid(), REVENUESPLIT_EXT.DESIGNATIONID, FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
                        FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT, FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT, FINANCIALTRANSACTIONLINEITEM.ID
                        from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID

                    insert into dbo.FINANCIALTRANSACTIONLINEITEM (ID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, VISIBLE, ADDEDBYID, CHANGEDBYID, 
                        DATEADDED, DATECHANGED,    DESCRIPTION, SEQUENCE, TYPECODE, POSTDATE, POSTSTATUSCODE,  SOURCELINEITEMID, BASEAMOUNT, ORGAMOUNT, BATCHID)
                    select ID, @ID, TRANSACTIONAMOUNT, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, '', row_number() over (order by TRANSACTIONAMOUNT),
                        0, case @POSTSTATUSCODE when 2 then @DATE else @POSTDATE end, case @POSTSTATUSCODE when 0 then 1 when 2 then 3 else 1 end,
                        SOURCELINEITEMID, BASEAMOUNT, ORGAMOUNT, @BATCHID
                    from @LineItems

                    insert into dbo.REVENUESPLIT_EXT (ID, DESIGNATIONID, APPLICATIONCODE, TYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select ID, DESIGNATIONID, APPLICATIONCODE, TYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                    from @LineItems


                    declare @WRITEOFFTRANSACTIONAMOUNT money;
                    declare @WRITEOFFBASEAMOUNT  money;
                    declare @WRITEOFFORGAMOUNT money;

                    select  
                        @WRITEOFFTRANSACTIONAMOUNT = sum(T2.TRANSACTIONAMOUNT),
                        @WRITEOFFBASEAMOUNT = sum(T2.BASEAMOUNT),
                        @WRITEOFFORGAMOUNT = sum(T2.ORGAMOUNT)
                    from dbo.FINANCIALTRANSACTION T1 
                        inner join  dbo.FINANCIALTRANSACTIONLINEITEM T2 on T1.ID = T2.FINANCIALTRANSACTIONID 
                        where T1.TYPECODE = 20 and T1.ID = @ID

                    update     dbo.FINANCIALTRANSACTION 
                        set TRANSACTIONAMOUNT = isnull(@WRITEOFFTRANSACTIONAMOUNT,0),    
                            BASEAMOUNT = isnull(@WRITEOFFBASEAMOUNT,0),        
                            ORGAMOUNT = isnull(@WRITEOFFORGAMOUNT,0)    
                        where ID = @ID

                    --Save the write-off GL distributions

                    if @POSTSTATUSCODE <> 2 and @TRANSACTIONTYPECODE in (1,15) and dbo.UFN_VALID_BASICGL_INSTALLED() = 1 
                    begin
                        -- Using a table to support redistributing across multiple write-offs, e.g. editing a pledge designation

                        declare @WRITEOFFIDTABLE UDT_GENERICID;
                        insert into @WRITEOFFIDTABLE values (@ID);
                        --Write-off for pledge 

                        exec dbo.USP_SAVE_PLEDGEWRITEOFFGLDISTRIBUTION @PLEDGEID, @WRITEOFFIDTABLE, @CHANGEAGENTID, @CURRENTDATE
                    end
                    else if @POSTSTATUSCODE <> 2
                        exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @PLEDGEID, @CHANGEAGENTID, @CURRENTDATE;


                    -- Update recognition credit amounts

                    update dbo.REVENUERECOGNITION set
                        AMOUNT = UPDATEDRECOGNITIONCREDITS.ADJUSTEDAMOUNT,
                        ORGANIZATIONAMOUNT =
                            case
                                when @BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                                    then dbo.UFN_CURRENCY_CONVERT(UPDATEDRECOGNITIONCREDITS.ADJUSTEDAMOUNT, @ORGANIZATIONEXCHANGERATEID)
                                else
                                    UPDATEDRECOGNITIONCREDITS.ADJUSTEDAMOUNT
                            end,
                        DATECHANGED = @CURRENTDATE,
                        CHANGEDBYID = @CHANGEAGENTID
                    from
                        dbo.REVENUERECOGNITION
                        inner join
                            (
                                select
                                    T.c.value('(ID)[1]','uniqueidentifier') as ID,
                                    T.c.value('(ADJUSTEDAMOUNT)[1]','money') as ADJUSTEDAMOUNT
                                from @RECOGNITIONCREDITS.nodes('/RECOGNITIONCREDITS/ITEM') T(c)
                            ) UPDATEDRECOGNITIONCREDITS on REVENUERECOGNITION.ID = UPDATEDRECOGNITIONCREDITS.ID;

                        -- Redefault installment receipt amounts

                        exec dbo.USP_INSTALLMENTS_DEFAULTRECEIPTAMOUNTS @PLEDGEID, @CHANGEAGENTID;
                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;

            end