USP_DATAFORMTEMPLATE_EDIT_POSTTOGLPROCESSTREASURY_4

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(255)	IN
@POSTINGOPTIONCODE	tinyint	IN
@IDSETREGISTERID	uniqueidentifier	IN
@CREATEOUTPUTIDSET	bit	IN
@OUTPUTIDSETNAME	nvarchar(100)	IN
@OVERWRITEOUTPUTIDSET	bit	IN
@MARKASPOSTED	int	IN
@BUSINESSPROCESSVIEWID	uniqueidentifier	IN
@DEPOSITPOSTINGOPTIONCODE	tinyint	IN
@ADJUSTMENTPOSTINGOPTIONCODE	tinyint	IN
@DEPOSITIDSETREGISTERID	uniqueidentifier	IN
@ADJUSTMENTIDSETREGISTERID	uniqueidentifier	IN
@REVENUEPOSTMETHODCODE	tinyint	IN
@REVENUEREFERENCE	nvarchar(255)	IN
@CASHPOSTMETHODCODE	tinyint	IN
@CASHREFERENCE	nvarchar(255)	IN
@ARPOSTMETHODCODE	tinyint	IN
@ARREFERENCE	nvarchar(255)	IN
@SUMMARIZECODE	tinyint	IN
@REVALUATIONGAINLOSSPOSTMETHODCODE	tinyint	IN
@REVALUATIONGAINLOSSREFERENCE	nvarchar(255)	IN
@POSTDATEUPTOCODE	tinyint	IN
@SPECIFICPOSTDATEUPTO	date	IN
@BANKACCOUNTS	xml	IN
@DEPOSITTEMPLATES	xml	IN
@TRANSACTIONTYPES	xml	IN
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_POSTTOGLPROCESSTREASURY_4
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @NAME nvarchar(100), 
                        @DESCRIPTION nvarchar(255),
                        @POSTINGOPTIONCODE tinyint,
                        @IDSETREGISTERID uniqueidentifier, 
                        @CREATEOUTPUTIDSET bit,
                        @OUTPUTIDSETNAME nvarchar(100), 
                        @OVERWRITEOUTPUTIDSET bit,    
                        @MARKASPOSTED int,
                        @BUSINESSPROCESSVIEWID uniqueidentifier,
                        @DEPOSITPOSTINGOPTIONCODE tinyint,
                        @ADJUSTMENTPOSTINGOPTIONCODE tinyint,
                        @DEPOSITIDSETREGISTERID uniqueidentifier,
                        @ADJUSTMENTIDSETREGISTERID uniqueidentifier,

                        --Post Detail Variables

                        @REVENUEPOSTMETHODCODE tinyint,
                        @REVENUEREFERENCE nvarchar(255),
                        @CASHPOSTMETHODCODE tinyint,
                        @CASHREFERENCE nvarchar(255),
                        @ARPOSTMETHODCODE tinyint,
                        @ARREFERENCE nvarchar(255),
                        @SUMMARIZECODE tinyint,
                        @REVALUATIONGAINLOSSPOSTMETHODCODE tinyint,
                        @REVALUATIONGAINLOSSREFERENCE nvarchar(255)

                        ,@POSTDATEUPTOCODE tinyint
                        ,@SPECIFICPOSTDATEUPTO date
                        ,@BANKACCOUNTS xml
                        ,@DEPOSITTEMPLATES xml
                        ,@TRANSACTIONTYPES xml
                    )
                as                
                    set nocount on;
                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

                    begin try

                        if @POSTINGOPTIONCODE = 3 and @DEPOSITPOSTINGOPTIONCODE = 2 and @ADJUSTMENTPOSTINGOPTIONCODE = 2
                        begin
                            raiserror('BBERR_POSTTOGLPROCESS_NOPOSTCODESSELECTED', 13, 1);
                            return 1;
                        end

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

                        --@IDSETREGISTERID only makes sense if the @POSTINGOPTIONCODE = 1 ('Post selected records').

                        if @POSTINGOPTIONCODE <> 1
                            set @IDSETREGISTERID = null;

                        if @DEPOSITPOSTINGOPTIONCODE <> 1
                            set @DEPOSITIDSETREGISTERID = null;

                        if @ADJUSTMENTPOSTINGOPTIONCODE <> 1
                            set @ADJUSTMENTIDSETREGISTERID = null;

                        set @POSTDATEUPTOCODE = isnull(@POSTDATEUPTOCODE, 0)
                        if @POSTDATEUPTOCODE != 6
                            set @SPECIFICPOSTDATEUPTO = null

                        if @DEPOSITPOSTINGOPTIONCODE != 3
                            set @BANKACCOUNTS = null;
                        if @DEPOSITPOSTINGOPTIONCODE != 4
                            set @DEPOSITTEMPLATES = null;
                        if @POSTINGOPTIONCODE != 4
                            set @TRANSACTIONTYPES = null;

                        update
                            dbo.POSTTOGLPROCESS
                        set
                            NAME = @NAME, 
                            POSTINGOPTIONCODE = @POSTINGOPTIONCODE,
                            IDSETREGISTERID = @IDSETREGISTERID, 
                            DESCRIPTION = @DESCRIPTION, 
                            CREATEOUTPUTIDSET = @CREATEOUTPUTIDSET,
                            OUTPUTIDSETNAME = @OUTPUTIDSETNAME, 
                            OVERWRITEOUTPUTIDSET = @OVERWRITEOUTPUTIDSET,
                            MARKASPOSTED = @MARKASPOSTED,
                            BUSINESSPROCESSVIEWID = @BUSINESSPROCESSVIEWID,
                            DEPOSITPOSTINGOPTIONCODE = @DEPOSITPOSTINGOPTIONCODE,
                            ADJUSTMENTPOSTINGOPTIONCODE = @ADJUSTMENTPOSTINGOPTIONCODE,
                            DEPOSITIDSETREGISTERID = @DEPOSITIDSETREGISTERID,
                            ADJUSTMENTIDSETREGISTERID = @ADJUSTMENTIDSETREGISTERID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                            ,POSTDATEUPTOCODE = @POSTDATEUPTOCODE
                            ,SPECIFICPOSTDATEUPTO = @SPECIFICPOSTDATEUPTO
                        where
                            ID = @ID;


                        --Save Post Detail Variables

                        update 
                            dbo.POSTTOGLPROCESSDETAIL
                        set
                            REVENUEPOSTMETHODCODE = @REVENUEPOSTMETHODCODE,
                            REVENUEREFERENCE = @REVENUEREFERENCE,
                            CASHPOSTMETHODCODE = @CASHPOSTMETHODCODE,
                            CASHREFERENCE = @CASHREFERENCE,
                            ARPOSTMETHODCODE = @ARPOSTMETHODCODE,
                            ARREFERENCE = @ARREFERENCE,
                            SUMMARIZECODE = @SUMMARIZECODE,    
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE,
                            REVALUATIONGAINLOSSPOSTMETHODCODE = @REVALUATIONGAINLOSSPOSTMETHODCODE,
                            REVALUATIONGAINLOSSREFERENCE = @REVALUATIONGAINLOSSREFERENCE
                        where
                            ID = @ID;

                        declare @PDACCOUNTSYSTEMID uniqueidentifier
                        select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.POSTTOGLPROCESS where ID = @ID;

                        if @BANKACCOUNTS is not null
                            merge dbo.POSTTOGLPROCESSBANKACCOUNTFILTER as target
                            using (select ID, BANKACCOUNTID from dbo.UFN_POST_BANKACCOUNTS_FROMITEMLISTXML(@BANKACCOUNTS) where INCLUDE = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) as source
                            on target.BANKACCOUNTID = source.BANKACCOUNTID and target.POSTTOGLPROCESSID = @ID
                            when not matched by target then
                                insert (ID, POSTTOGLPROCESSID, BANKACCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values (isnull(source.ID, NEWID()), @ID, source.BANKACCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                            when not matched by source and target.POSTTOGLPROCESSID = @ID then delete;

                        if @DEPOSITTEMPLATES is not null
                            merge dbo.POSTTOGLPROCESSDEPOSITTEMPLATESFILTER as target
                            using (select ID, SALESDEPOSITTEMPLATEID from UFN_POST_DEPOSITTEMPLATES_FROMITEMLISTXML(@DEPOSITTEMPLATES) where INCLUDE = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) as source
                            on target.SALESDEPOSITTEMPLATEID = source.SALESDEPOSITTEMPLATEID and target.POSTTOGLPROCESSID = @ID
                            when not matched by target then
                                insert (ID, POSTTOGLPROCESSID, SALESDEPOSITTEMPLATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values (isnull(source.ID, NEWID()), @ID, source.SALESDEPOSITTEMPLATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                            when not matched by source and target.POSTTOGLPROCESSID = @ID then delete;

                        if @TRANSACTIONTYPES is not null
                            merge dbo.POSTTOGLPROCESSTRANSACTIONTYPEFILTER as target
                            using (select ID, TRANSACTIONTYPECODE from UFN_POST_TRANSACTIONTYPES_FROMITEMLISTXML(@TRANSACTIONTYPES) where INCLUDE = 1) as source
                            on target.TYPECODE = source.TRANSACTIONTYPECODE and target.POSTTOGLPROCESSID = @ID
                            when not matched by target then
                                insert (ID, POSTTOGLPROCESSID, TYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values (isnull(source.ID, NEWID()), @ID, source.TRANSACTIONTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                            when not matched by source and target.POSTTOGLPROCESSID = @ID then delete;

                    end try

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

                    return 0;