USP_DATAFORMTEMPLATE_EDIT_RECONCILIATIONCLOSESHIFT

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECONCILIATIONCLOSESHIFT (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @STARTINGCASH money,
                        @ACTUALCASH money,
                        @COMMENT nvarchar(max),
                        @OTHERRECEIPTS xml
                    )
                    as
                        set nocount on;

                        declare @APPUSERID uniqueidentifier;
                        select @APPUSERID = APPUSERID from dbo.RECONCILIATION where ID = @ID;

                        -- Check if there are pending orders with application user
                        if exists (
                            select SALESORDERPAYMENT.ID 
                            from dbo.SALESORDERPAYMENT
                            inner join dbo.SALESORDER 
                                on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
                            where 
                                SALESORDERPAYMENT.RECONCILIATIONID is null 
                                and SALESORDER.STATUSCODE <> 1
                                and SALESORDERPAYMENT.APPUSERID = @APPUSERID
                                and SALESORDER.SALESMETHODTYPECODE <> 3
                        )
                        begin
                            raiserror('ERR_RECONCILIATION_PENDINGORDEREXISTS', 13, 1);
                            return 1;
                        end

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

                        declare @CURRENTDATE datetime = getdate();
                        declare @CLIENTDATETIME datetime = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);

                        begin try

                            -- update RECONCILIATION table
                            update dbo.[RECONCILIATION] 
                            set
                                [STARTINGCASH] = @STARTINGCASH,
                                [ACTUALCASH] = @ACTUALCASH,
                                [COMMENT] = @COMMENT,
                                [RECONCILIATIONDATE] = @CLIENTDATETIME,
                                [CHANGEDBYID] = @CHANGEAGENTID,
                                [DATECHANGED] = @CURRENTDATE
                            where [ID] = @ID;

                            -- update SALESORDERPAYMENT table
                            declare @STATUSCODE tinyint
                            select @STATUSCODE = STATUSCODE 
                            from dbo.RECONCILIATION 
                            where ID = @ID 

                            -- only update SALESORDERPAYMENT table when
                            -- current conciliation is open
                            if @STATUSCODE = 0
                            begin
                                update dbo.SALESORDERPAYMENT with (rowlock)
                                set
                                    RECONCILIATIONID = @ID,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                from dbo.[SALESORDERPAYMENT]
                                where 
                                    SALESORDERPAYMENT.RECONCILIATIONID is null and
                                    SALESORDERPAYMENT.DONOTRECONCILE = 0 and
                                    SALESORDERPAYMENT.APPUSERID = @APPUSERID

                -- update reservation security deposit payment table
                                update dbo.RESERVATIONSECURITYDEPOSITPAYMENT with (rowlock)
                                set
                                    RECONCILIATIONID = @ID,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                from dbo.[RESERVATIONSECURITYDEPOSITPAYMENT]
                                inner join dbo.[SALESORDER]
                                    on [RESERVATIONSECURITYDEPOSITPAYMENT].[RESERVATIONID] = [SALESORDER].[ID]
                                where 
                                    RESERVATIONSECURITYDEPOSITPAYMENT.RECONCILIATIONID is null
                                    and RESERVATIONSECURITYDEPOSITPAYMENT.APPUSERID = @APPUSERID
                                    and [SALESORDER].[SALESMETHODTYPECODE] <> 2;


                                update dbo.[CREDITPAYMENT]
                                set
                                    [RECONCILIATIONID] = @ID,
                                    [CHANGEDBYID] = @CHANGEAGENTID,
                                    [DATECHANGED] = @CURRENTDATE
                                where 
                                    [CREDITPAYMENT].[RECONCILIATIONID] is null and 
                                    [CREDITPAYMENT].[APPUSERID] = @APPUSERID and
                                    [CREDITPAYMENT].[REFUNDPROCESSED] = 1;
                            end

                            -- update RECONCILIATIONDETAIL table

                            -- build a temporary table containing the values from the XML
                            declare @TempTbl table (
                                ID uniqueidentifier default null,
                                PAYMENTMETHODCODE int,
                                CREDITTYPECODEID uniqueidentifier,
                                OTHERPAYMENTMETHODCODEID uniqueidentifier,
                                QUANTITY int,
                                ISREFUND bit
                            );

                            insert into @TempTbl 
                            select 
                                [ID],
                                [PAYMENTMETHODCODE], 
                                [CREDITTYPECODEID],
                                [OTHERPAYMENTMETHODCODEID],
                                [QUANTITY],
                                [ISREFUND]
                            from dbo.[UFN_RECONCILIATION_GETOTHERRECEIPTS_FROMITEMLISTXML](@OTHERRECEIPTS)
                            where 
                                ([QUANTITY] <> 0 or [EXPECTED] <> 0) and
                                [ISLABEL] = 0

                            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
                            update @TempTbl set CREDITTYPECODEID = null where CREDITTYPECODEID = '00000000-0000-0000-0000-000000000000';
                            update @TempTbl set OTHERPAYMENTMETHODCODEID = null where OTHERPAYMENTMETHODCODEID = '00000000-0000-0000-0000-000000000000';

                            if @@Error <> 0
                                return 1;

                            declare @contextCache varbinary(128);
                            declare @e int;

                            -- cache current context information 
                            set @contextCache = CONTEXT_INFO();

                            -- set CONTEXT_INFO to @CHANGEAGENTID 
                            if not @CHANGEAGENTID is null
                                set CONTEXT_INFO @CHANGEAGENTID;

                            -- delete any items that no longer exist in the XML table
                            delete from dbo.[RECONCILIATIONDETAIL] 
                            where [RECONCILIATIONID] = @ID and 
                                [ID] not in (select ID from @TempTbl)    

                            -- delete refund
                            if @ID not in (select [ID] from @TempTbl where [ISREFUND] = 1) and exists (select [ID] from dbo.[RECONCILIATIONCREDITDETAIL] where [ID] = @ID)
                                exec dbo.USP_RECONCILIATIONCREDITDETAIL_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

                            select @e=@@error;

                            -- reset CONTEXT_INFO to previous value 
                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache;

                            if @e <> 0
                                return 2;

                            -- update the items that exist in the XML table and the db
                            update dbo.[RECONCILIATIONDETAIL]
                            set [RECONCILIATIONDETAIL].[QUANTITY]= [temp].QUANTITY,
                                [RECONCILIATIONDETAIL].CHANGEDBYID = @CHANGEAGENTID,
                                [RECONCILIATIONDETAIL].DATECHANGED = @CURRENTDATE 
                            from @TempTbl as [temp]
                            where 
                                [temp].ID = [RECONCILIATIONDETAIL].ID and
                                (
                                    ([RECONCILIATIONDETAIL].[QUANTITY] <> [temp].[QUANTITY]) or 
                                    ([RECONCILIATIONDETAIL].[QUANTITY] is null and [temp].[QUANTITY] is not null) or 
                                    ([RECONCILIATIONDETAIL].[QUANTITY] is not null and [temp].[QUANTITY] is null) 
                                ) and
                                [temp].[ISREFUND] = 0;

                            --Update refund
                            update dbo.[RECONCILIATIONCREDITDETAIL]
                            set 
                                [RECONCILIATIONCREDITDETAIL].[QUANTITY]= [temp].QUANTITY,
                                [RECONCILIATIONCREDITDETAIL].CHANGEDBYID = @CHANGEAGENTID,
                                [RECONCILIATIONCREDITDETAIL].DATECHANGED = @CURRENTDATE 
                            from @TempTbl as [temp]
                            where [temp].ID = [RECONCILIATIONCREDITDETAIL].ID
                                and [temp].[ISREFUND] = 1;

                            if @@Error <> 0
                                return 3;    

                            -- insert new items
                            insert into [RECONCILIATIONDETAIL] (
                                [ID],
                                [RECONCILIATIONID],
                                [PAYMENTMETHODCODE],
                                [QUANTITY],
                                [CREDITTYPECODEID],
                                [OTHERPAYMENTMETHODCODEID],
                                [ADDEDBYID], 
                                [CHANGEDBYID], 
                                [DATEADDED], 
                                [DATECHANGED]
                            )
                            select [ID],
                                @ID, 
                                [PAYMENTMETHODCODE],
                                [QUANTITY],
                                [CREDITTYPECODEID],
                                [OTHERPAYMENTMETHODCODEID],
                                @CHANGEAGENTID, 
                                @CHANGEAGENTID, 
                                @CURRENTDATE, 
                                @CURRENTDATE
                            from @TempTbl as [temp]
                            where 
                                not exists (select ID from dbo.[RECONCILIATIONDETAIL] as data where data.ID = [temp].ID) and
                                [temp].[ISREFUND] = 0

                            --Insert refund
                            if @ID in (select [ID] from @TempTbl where [ISREFUND] = 1) and not exists (select [ID] from dbo.[RECONCILIATIONCREDITDETAIL] where [ID] = @ID)
                                insert into [RECONCILIATIONCREDITDETAIL] (
                                    [ID],
                                    [QUANTITY],
                                    [ADDEDBYID], 
                                    [CHANGEDBYID], 
                                    [DATEADDED], 
                                    [DATECHANGED]
                                )
                                select 
                                    [ID],
                                    [QUANTITY],
                                    @CHANGEAGENTID, 
                                    @CHANGEAGENTID, 
                                    @CURRENTDATE, 
                                    @CURRENTDATE
                                from @TempTbl as [temp]
                                where 
                                    [temp].[ID] = @ID and 
                                    [temp].[ISREFUND] = 1

                            if @@Error <> 0
                                return 4;

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

                    return 0;