USP_REVENUECATEGORY_UPDATESPLITS

Inserts or updates rows in REVENUECATEGORY for every revenue split associated with the given revenue.
Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@CATEGORYCODEID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN
Definition

Copy


            CREATE procedure dbo.USP_REVENUECATEGORY_UPDATESPLITS
            (
                @REVENUEID uniqueidentifier,
                @CATEGORYCODEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTDATE datetime
            )
            as
                set nocount on

                if @CATEGORYCODEID is not null
                begin
                    update dbo.REVENUECATEGORY set
                        GLREVENUECATEGORYMAPPINGID = @CATEGORYCODEID,
                        DATECHANGED = @CURRENTDATE,
                        CHANGEDBYID = @CHANGEAGENTID
                    where ID in (select ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = @REVENUEID and DELETEDON is null)

                    insert into dbo.REVENUECATEGORY (ID, GLREVENUECATEGORYMAPPINGID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                    select
                        FINANCIALTRANSACTIONLINEITEM.ID,
                        @CATEGORYCODEID,
                        @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                    where
                        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and
            FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
                        not exists (select 1 from dbo.REVENUECATEGORY where ID = FINANCIALTRANSACTIONLINEITEM.ID)
                end
                else
                begin
                    declare @contextCache varbinary(128);

                    --cache current context information

                    set @contextCache = CONTEXT_INFO();

                    --set CONTEXT_INFO to @CHANGEAGENTID

                    set CONTEXT_INFO @CHANGEAGENTID;

                    delete from dbo.REVENUECATEGORY
                    where ID in (select ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = @REVENUEID and DELETEDON is null)

                    --reset CONTEXT_INFO to previous value

                    if not @contextCache is null
                        set CONTEXT_INFO @contextCache;
                end