USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILADJUSTMENT

Stored procedure to log adjustments to sold gift-in-kind.

Parameters

Parameter	Parameter Type	Mode
@GIFTINKINDPAYMENTMETHODDETAILID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
@DATE	datetime	IN
@POSTDATE	datetime	IN
@ADJUSTMENTREASON	nvarchar(300)	IN
@GIFTINKINDSALEADJUSTMENTIDS	xml	INOUT
@ADJUSTMENTREASONCODEID	uniqueidentifier	IN
@POSTSTATUSCODE	tinyint	IN

Definition

Copy



            CREATE procedure dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILADJUSTMENT
            (
                @GIFTINKINDPAYMENTMETHODDETAILID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null, 
                @DATE datetime = null,
                @POSTDATE datetime = null,
                @ADJUSTMENTREASON nvarchar(300) = null,
                @GIFTINKINDSALEADJUSTMENTIDS  xml = null output,
                @ADJUSTMENTREASONCODEID uniqueidentifier = null,
                @POSTSTATUSCODE tinyint = 1
            )
            with execute as owner
            as
            /*Call this procedure before making changes to the gift-in-kind sale tables.*/

            --JamesWill WI197067 2012-03-08 Ensure that the date used does not have a timestamp

            if not @DATE is null
                set @DATE = dbo.UFN_DATE_GETEARLIESTTIME(@DATE);

            set nocount on;
            declare @GIFTINKINDSALEADJUSTMENTIDSTBL table
            (
                GIFTINKINDSALEID uniqueidentifier,
                GIFTINKINDSALEADJUSTMENTID uniqueidentifier
            )

            declare GIFTINKINDSALECURSOR cursor local fast_forward for
            select
                ID
            from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @GIFTINKINDPAYMENTMETHODDETAILID and SALEPOSTSTATUSCODE = 0

            declare @GIFTINKINDSALEID uniqueidentifier
            open GIFTINKINDSALECURSOR
            fetch next from GIFTINKINDSALECURSOR into @GIFTINKINDSALEID

            while (@@FETCH_STATUS = 0)
            begin
                declare @GIFTINKINDSALEADJUSTMENTID uniqueidentifier
                set @GIFTINKINDSALEADJUSTMENTID = null
                exec dbo.USP_SAVE_GIFTINKINDSALEADJUSTMENT @GIFTINKINDSALEID, @GIFTINKINDSALEADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @DATE, @POSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID, @POSTSTATUSCODE;

                insert into @GIFTINKINDSALEADJUSTMENTIDSTBL (GIFTINKINDSALEID, GIFTINKINDSALEADJUSTMENTID)
                select @GIFTINKINDSALEID, @GIFTINKINDSALEADJUSTMENTID

                fetch next from GIFTINKINDSALECURSOR into @GIFTINKINDSALEID
            end

            close GIFTINKINDSALECURSOR
            deallocate GIFTINKINDSALECURSOR

            if exists (select * from @GIFTINKINDSALEADJUSTMENTIDSTBL)
                set @GIFTINKINDSALEADJUSTMENTIDS =
                    (
                        select GIFTINKINDSALEID, GIFTINKINDSALEADJUSTMENTID
                        from @GIFTINKINDSALEADJUSTMENTIDSTBL
                        for xml raw('ITEM'),type,elements,root('GIFTINKINDSALEADJUSTMENTIDS'),binary base64
                    )