USP_SAVE_STOCKDETAILADJUSTMENT

Stored procedure to log adjustments to sold stock.

Parameters

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

Definition

Copy



            CREATE procedure dbo.USP_SAVE_STOCKDETAILADJUSTMENT
            (
                @STOCKDETAILID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null, 
                @DATE datetime = null,
                @POSTDATE datetime = null,
                @ADJUSTMENTREASON nvarchar(300) = null,
                @STOCKSALEADJUSTMENTIDS  xml = null output,
                @ADJUSTMENTREASONCODEID uniqueidentifier = null,
                @POSTSTATUSCODE tinyint = 1
            )
            with execute as owner
            as
            /*call this procedure before making changes to the stock sale tables.*/

            set nocount on;
            declare @STOCKSALEADJUSTMENTIDSTBL table
            (
                STOCKSALEID uniqueidentifier,
                STOCKSALEADJUSTMENTID uniqueidentifier
            )

            declare STOCKSALECURSOR cursor local fast_forward for
            select
                ID
            from STOCKSALE where STOCKDETAILID = @STOCKDETAILID and SALEPOSTSTATUSCODE = 0

            declare @STOCKSALEID uniqueidentifier
            open STOCKSALECURSOR
            fetch next from STOCKSALECURSOR into @STOCKSALEID

            --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);

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

                insert into @STOCKSALEADJUSTMENTIDSTBL (STOCKSALEID, STOCKSALEADJUSTMENTID)
                select @STOCKSALEID, @STOCKSALEADJUSTMENTID

                fetch next from STOCKSALECURSOR into @STOCKSALEID
            end

            close STOCKSALECURSOR
            deallocate STOCKSALECURSOR

            if exists (select * from @STOCKSALEADJUSTMENTIDSTBL)
                set @STOCKSALEADJUSTMENTIDS = (    select STOCKSALEID, STOCKSALEADJUSTMENTID
                                                    from @STOCKSALEADJUSTMENTIDSTBL
                                                    for xml raw('ITEM'),type,elements,root('STOCKSALEADJUSTMENTIDS'),binary base64
                                                )