USP_MERCHANDISEPRODUCTINSTANCE_UPDATEINVENTORY

CREATE procedure dbo.USP_MERCHANDISEPRODUCTINSTANCE_UPDATEINVENTORY
(
    @ID uniqueidentifier = null output,
    @VALIDATEONLY bit = 0,
    @CHANGEAGENTID uniqueidentifier = null,
    @MERCHANDISEPRODUCTINSTANCEID uniqueidentifier,
    @SOURCETYPECODE tinyint,
    @ACTIONTYPECODE tinyint,
    @QUANTITY int,
    @COST money,
    @PRICE money,
    @VENDORID uniqueidentifier,
    @LOOKUPCODE nvarchar(25),
    @MERCHANDISEADJUSTMENTREASONCODEID uniqueidentifier,    
    @BATCHID uniqueidentifier,
    @COMMENTS nvarchar(255)
)
as

    /*
        SOURCETYPECODE
            0 - Manual
            1 - Batch

        ACTIONTYPECODE
            0 - Shipment
            1 - Adjustment
            2 - Physical count
    */

    set nocount on;

    if @ID is null
        set @ID = newID();

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

    declare @CURRENTDATE datetime = getdate();
    declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1)

    begin try
        --Clean parameters that shouldn't be set depending on actioncode.

        if @ACTIONTYPECODE <> 1
            set @MERCHANDISEADJUSTMENTREASONCODEID = null

        if @ACTIONTYPECODE <> 0
            begin
                set @PRICE = null
                set @COST = null
                set @VENDORID = null
            end

        if @ACTIONTYPECODE > 2
            raiserror('BBERR_INVALIDACTIONCODE', 13, 1);

        declare @MERCHANDISEPRODUCTID uniqueidentifier;
        declare @CURRENTQUANTITY int = 0;
        declare @CURRENTAVGCOST money = 0;
        declare @AVGCOST money = null;

        select 
            @MERCHANDISEPRODUCTID = MERCHANDISEPRODUCTID,
            @CURRENTQUANTITY = ONHANDQUANTITY,
            @CURRENTAVGCOST = COST
        from dbo.MERCHANDISEPRODUCTINSTANCE
        where ID = @MERCHANDISEPRODUCTINSTANCEID;


        if @ACTIONTYPECODE = 0 
            begin                            
                if @QUANTITY <= 0
                    raiserror('BBERR_QUANTITYMUSTBEGREATERTHANZERO', 13, 1);

                if @COST < 0 
                    raiserror('BBERR_COSTMUSTBEGREATERTHANOREQUALTOZERO', 13, 1);

                if @PRICE < 0
                    raiserror('BBERR_PRICEMUSTBEGREATERTHANOREQUALTOZERO', 13, 1);

                if @VENDORID is null
                    raiserror('BBERR_SHIPMENTVENDORREQUIRED', 13, 1);
            end

        if @ACTIONTYPECODE = 1 
            begin
                if @QUANTITY = 0
                    raiserror('BBERR_QUANTITYMUSTNOTBEZERO', 13, 1);

                if @MERCHANDISEADJUSTMENTREASONCODEID is null
                    raiserror('BBERR_REASONREQUIREDFORADJUSTMENT', 13, 1);
            end

        if @ACTIONTYPECODE = 2 and @QUANTITY < 0
            raiserror('BBERR_PHYSICALCOUNT_QUANTITYINVALID', 13, 1);

        if @VALIDATEONLY = 0
            begin    
                if @ACTIONTYPECODE = 0
                    -- Shipment

                    begin
                        -- Calculate new weighted average cost

                        -- Avg cost = (([Quantity on hand] * [Cost]) + ([Shipment Quantity] * [Shipment per item cost])) / ([Quantity on hand] + [Shipment Quantity])

                        --    Unless [Quantity on hand] is negative

                        --        In this case, ignore this portion of the equation: ([Quantity on hand] * [Cost])

                        if @CURRENTQUANTITY > 0
                            set @AVGCOST = ((@CURRENTQUANTITY * @CURRENTAVGCOST) + (@QUANTITY * @COST)) / (@CURRENTQUANTITY + @QUANTITY);

                        else
                            set @AVGCOST = @COST;                            

                        update dbo.MERCHANDISEPRODUCTINSTANCE set
                            ONHANDQUANTITY = @CURRENTQUANTITY + @QUANTITY,
                            COST = @AVGCOST,
                            SALEPRICE = isnull(@PRICE, SALEPRICE),
                            LOOKUPCODE = coalesce(@LOOKUPCODE, ''),
                            VENDORID = @VENDORID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @MERCHANDISEPRODUCTINSTANCEID;

                        -- Update vendor on merchandise item

                        update dbo.PRODUCTVENDOR set
                            VENDORID = @VENDORID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where PRODUCTID = @MERCHANDISEPRODUCTID;
                    end

                if @ACTIONTYPECODE = 1
                    -- Adjustment

                    update dbo.MERCHANDISEPRODUCTINSTANCE set
                        ONHANDQUANTITY = ONHANDQUANTITY + @QUANTITY,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @MERCHANDISEPRODUCTINSTANCEID;

                if @ACTIONTYPECODE = 2
                    -- Physical count

                    update dbo.MERCHANDISEPRODUCTINSTANCE set
                        ONHANDQUANTITY = @QUANTITY,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @MERCHANDISEPRODUCTINSTANCEID;                    

                insert into dbo.MERCHANDISEPRODUCTINSTANCEHISTORY
                    (ID, SOURCETYPECODE, ACTIONTYPECODE, ACTIONDATE, MERCHANDISEPRODUCTINSTANCEID, QUANTITY, ONHANDQUANTITY, COST, AVERAGECOST, SALEPRICE, BATCHID, VENDORID, MERCHANDISEADJUSTMENTREASONCODEID, COMMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values
                    (@ID, @SOURCETYPECODE, @ACTIONTYPECODE, @CURRENTDATETIMEOFFSET, @MERCHANDISEPRODUCTINSTANCEID, @QUANTITY, @CURRENTQUANTITY, @COST, @AVGCOST, @PRICE, @BATCHID, @VENDORID, @MERCHANDISEADJUSTMENTREASONCODEID, coalesce(@COMMENTS, ''), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

            end    

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0