USP_REVENUEUPDATEBATCH_CLEANUPOTHERPAYMENTRECORDS

create procedure dbo.USP_REVENUEUPDATEBATCH_CLEANUPOTHERPAYMENTRECORDS
(
    @REVENUESTREAM xml
)
as
begin
    declare @SPLITSTODELETETABLE table (REVENUESPLITID uniqueidentifier, TYPECODE tinyint)

    insert into @SPLITSTODELETETABLE (REVENUESPLITID, TYPECODE)
    select    T.c.value('(ID)[1]', 'uniqueidentifier') as REVENUESPLITID,
                    T.c.value('(TYPECODE)[1]','tinyint') AS TYPECODE
    from @REVENUESTREAM.nodes('/REVENUESTREAMS/ITEM') T(c)

    delete from dbo.REVENUESPLITOTHER
  where ID in (select REVENUESPLITID
                                from @SPLITSTODELETETABLE SPLITSTODELETETABLE
                                --Check if the revenue split is getting converted from Other Type of payment and converted too is not Other Type

                                where SPLITSTODELETETABLE.TYPECODE != 4)
end