USP_FINANCIALTRANSACTIONLINEITEM_DELETESPLITSANDMARKORPHANED

            create procedure dbo.USP_FINANCIALTRANSACTIONLINEITEM_DELETESPLITSANDMARKORPHANED
            (
                @SPLITSTODELETE xml,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime
            )
            as
            begin
                declare @SPLITSTABLE table (ID uniqueidentifier)
                insert into @SPLITSTABLE(ID)
                select T.c.value('(ID)[1]', 'uniqueidentifier') as ID
                from @SPLITSTODELETE.nodes('/SPLITSTODELETE/ITEM') T(c)

                declare @DeletedIDs table (ID uniqueidentifier)
                insert into @DeletedIDs (ID)
                select ID
                from
                (merge dbo.FINANCIALTRANSACTIONLINEITEM as target
                using (select SplitsTable.ID,  FTLI2.POSTSTATUSCODE, FTLI3.REVERSEDLINEITEMID ,FTLI4.SOURCELINEITEMID 
                        from @SPLITSTABLE as SplitsTable 
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2 on SplitsTable.ID = FTLI2.ID 
                        left outer join dbo.FINANCIALTRANSACTIONLINEITEM FTLI3 on FTLI3.REVERSEDLINEITEMID = FTLI2.ID  
                        left outer join dbo.FINANCIALTRANSACTIONLINEITEM FTLI4 on FTLI4.SOURCELINEITEMID = FTLI2.ID  
                        group by SplitsTable.id,  FTLI2.POSTSTATUSCODE, FTLI3.REVERSEDLINEITEMID,FTLI4.SOURCELINEITEMID) as source
                on (target.ID = source.ID)
                when matched and (source.POSTSTATUSCODE = 2 or source.REVERSEDLINEITEMID is not null  or source.SOURCELINEITEMID is not null)
                    then update set target.DELETEDON = @CHANGEDATE,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CHANGEDATE,
                    target.TYPECODE = 99
                when matched
                    then delete
                output deleted.ID, $action) as Changes (ID, Action)
                where Action = 'UPDATE';

                --Cascading deletes for the RevenueSplits that get marked as deleted instead of deleted            

                delete from dbo.INSTALLMENTSPLITPAYMENT where PAYMENTID in (select ID from @DeletedIDs)
                delete from dbo.AUCTIONITEMPURCHASE where PURCHASEID in (select ID from @DeletedIDs)
                delete from dbo.EVENTREGISTRANTPAYMENT where PAYMENTID in (select ID from @DeletedIDs)
                delete from dbo.NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT where REVENUESPLITID in (select ID from @DeletedIDs)
                delete from dbo.ORDERPAYMENTSPLIT where PAYMENTID in (select ID from @DeletedIDs)
                delete from dbo.PLANNEDGIFTREVENUESPLIT where REVENUESPLITID in (select ID from @DeletedIDs)
                delete from dbo.R68REFUNDDETAIL where REVENUESPLITID in (select ID from @DeletedIDs)
                -- This table does not exist in all databases.

                --delete from dbo.RE7MAPGIFTSPLIT where ID in (select ID from @DeletedIDs)

                delete from dbo.REVENUECATEGORY where ID in (select ID from @DeletedIDs)
                delete from dbo.REVENUEOPPORTUNITY where ID in (select ID from @DeletedIDs)
                delete from dbo.REVENUERECOGNITION where REVENUESPLITID in (select ID from @DeletedIDs)
                delete from dbo.REVENUESOLICITOR where REVENUESPLITID in (select ID from @DeletedIDs)
                delete from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITID in (select ID from @DeletedIDs)
                delete from dbo.REVENUESPLITCAMPAIGN where REVENUESPLITID in (select ID from @DeletedIDs)
                delete from dbo.REVENUESPLITGIFTAID where ID in (select ID from @DeletedIDs)
                delete from dbo.REVENUESPLITGIFTFEE where ID in (select ID from @DeletedIDs)
                delete from dbo.REVENUESPLITORDER where ID in (select ID from @DeletedIDs)
                delete from dbo.REVENUESPLITOTHER where ID in (select ID from @DeletedIDs)
     delete from dbo.SPONSORSHIPPAYMENT where ID in (select ID from @DeletedIDs)

            end