USP_GLOBALCHANGE_PAPERLESSMANDATESCANCELCOMPLETEDREVENUE

            CREATE procedure dbo.USP_GLOBALCHANGE_PAPERLESSMANDATESCANCELCOMPLETEDREVENUE
            (
                @CHANGEAGENTID uniqueidentifier = null,
                @SELECTIONID uniqueidentifier = null,

                @ASOF as datetime = null,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as
            set nocount on;

            set @NUMBERADDED = 0;
            set @NUMBEREDITED = 0;
            set @NUMBERDELETED = 0;
            set @ASOF = null;

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

                declare @CHANGEDATE datetime;
                set @CHANGEDATE = getdate();

                update REVENUESCHEDULEDIRECTDEBITPAYMENT
                    set SENDPMINSTRUCTION = 1,
                        PMINSTRUCTIONTOSENDCODE = 2,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CHANGEDATE
                from dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = REVENUESCHEDULEDIRECTDEBITPAYMENT.ID
                inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
                inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as [SELECTION] on [SELECTION].ID = REVENUESCHEDULEDIRECTDEBITPAYMENT.ID
                where REVENUESCHEDULEDIRECTDEBITPAYMENT.SENDPMINSTRUCTION = 0 
                    and REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_CANCEL is null
                    and REVENUESCHEDULEDIRECTDEBITPAYMENT.DDISOURCECODEID is not null
                    and 
                    (
                        (FINANCIALTRANSACTION.TYPECODE in (1,15) and dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID) = 0) 
                        or (FINANCIALTRANSACTION.TYPECODE = 2 and (REVENUESCHEDULE.STATUSCODE = 2 or 
                                                                 REVENUESCHEDULE.STATUSCODE = 3 or 
                                                                 REVENUESCHEDULE.STATUSCODE = 4))
                    )
                    and 
                    (
                        dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                        or exists
                        (
                            select 1 
                            from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUESCHEDULEDIRECTDEBITPAYMENT.ID) REVSITES
                            where dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, '3269A1D1-31CB-4D28-945C-B7623A3EFCCA', REVSITES.SITEID) = 1
                        )
                    )
                 and FINANCIALTRANSACTION.DELETEDON is null

                set @NUMBEREDITED = @@ROWCOUNT;

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

            return 0;