USP_SALESORDERITEMMERCHANDISE_ADD

            CREATE procedure dbo.USP_SALESORDERITEMMERCHANDISE_ADD
            (
                @ID uniqueidentifier,
                @SALESORDERID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null,
                @MERCHANDISEPRODUCTINSTANCEID uniqueidentifier,
                @QUANTITY integer = 1,
                @DATA xml = null,
                @OPTIONS xml = null,
                @CALLBACKURL nvarchar(255) = '',
                @SYSTEMTYPENAME nvarchar(255) = '',
                @ASSEMBLYNAME nvarchar(255) = '',
                @ATTRIBUTES xml = null,
                @CATEGORYNAME nvarchar(255) = '',
                @EXPIREDCALLBACKURL nvarchar(255) = '',
                @ACKNOWLEDGEMENT nvarchar(max) = ''
            )
            as
            begin
                set nocount on;

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

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate();

                declare @CURRENTDATETIMEWITHOFFSET datetimeoffset = sysdatetimeoffset()

                declare @PRICE money 
                declare @DESCRIPTION nvarchar(150)

                select 
                    @PRICE = SALEPRICE,
                    @DESCRIPTION = ITEMDETAILS
                from dbo.MERCHANDISEPRODUCTINSTANCE 
                where ID = @MERCHANDISEPRODUCTINSTANCEID

                declare @SALESORDERITEMID uniqueidentifier = (
                    select SOI.ID 
                    from dbo.SALESORDERITEMMERCHANDISE SOIM with (nolock)
                    inner join dbo.SALESORDERITEM SOI with (nolock) on SOI.ID = SOIM.ID
                    inner join dbo.SALESORDER SO with (nolock) on SOI.SALESORDERID = SO.ID
                    where SO.ID = @SALESORDERID
                    and SOIM.MERCHANDISEPRODUCTINSTANCEID = @MERCHANDISEPRODUCTINSTANCEID
                )

                -- Selling a new item

                if @SALESORDERITEMID is null
                begin
                    set @SALESORDERITEMID = @ID;
                    insert into dbo.SALESORDERITEM
                    (
                        ID, 
                        SALESORDERID, 
                        TYPECODE, 
                        DESCRIPTION, 
                        QUANTITY, 
                        PRICE, 
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED, 
                        DATA, 
                        OPTIONS, 
                        CALLBACKURL, 
                        SYSTEMTYPENAME, 
                        ASSEMBLYNAME, 
                        ATTRIBUTES, 
                        CATEGORYNAME, 
                        EXPIREDCALLBACKURL, 
                        ACKNOWLEDGEMENT
                    )
                    values
                    (
                        @SALESORDERITEMID, 
                        @SALESORDERID, 
                        14, 
                        @DESCRIPTION, 
                        @QUANTITY, 
                        @PRICE, 
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE, 
                        @DATA, 
                        @OPTIONS, 
                        @CALLBACKURL, 
                        @SYSTEMTYPENAME, 
                        @ASSEMBLYNAME, 
                        @ATTRIBUTES, 
                        @CATEGORYNAME, 
                        @EXPIREDCALLBACKURL,
                        @ACKNOWLEDGEMENT
                    )

                    declare @MERCHANDISEDEPARTMENTID uniqueidentifier
                    declare @MERCHANDISEDEPARTMENTCATEGORYID uniqueidentifier
                    declare @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE1ID uniqueidentifier
                    declare @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE2ID uniqueidentifier
                    declare @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE3ID uniqueidentifier

                    select
                        @MERCHANDISEDEPARTMENTID = MERCHANDISEDEPARTMENTID,
                        @MERCHANDISEDEPARTMENTCATEGORYID = MERCHANDISEDEPARTMENTCATEGORYID
                    from dbo.MERCHANDISEPRODUCT MP
                    inner join dbo.MERCHANDISEPRODUCTINSTANCE MPI on MP.ID = MPI.MERCHANDISEPRODUCTID
                    where MPI.ID = @MERCHANDISEPRODUCTINSTANCEID

                    declare OPTION_VALUES cursor local fast_forward for
                    select MPOV.ID
                    from dbo.MERCHANDISEPRODUCTOPTIONVALUE MPOV
                    inner join dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE MPIOV on MPIOV.MERCHANDISEPRODUCTOPTIONVALUEID = MPOV.ID
                    where MPIOV.MERCHANDISEPRODUCTINSTANCEID = @MERCHANDISEPRODUCTINSTANCEID

                    declare @OPTIONVALUEID uniqueidentifier

                    open OPTION_VALUES
                    fetch next from OPTION_VALUES into @OPTIONVALUEID

                    while @@fetch_status = 0
                    begin
                        if @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE1ID is null
                            set @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE1ID = @OPTIONVALUEID

                        else if @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE2ID is null
                            set @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE2ID = @OPTIONVALUEID

                        else
                            set @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE3ID = @OPTIONVALUEID

                        fetch next from OPTION_VALUES into @OPTIONVALUEID
                    end

                    close OPTION_VALUES
                    deallocate OPTION_VALUES

                    insert into dbo.SALESORDERITEMMERCHANDISE
                    (
                        ID, 
                        MERCHANDISEPRODUCTINSTANCEID, 
                        MERCHANDISEDEPARTMENTID, 
                        MERCHANDISEDEPARTMENTCATEGORYID, 
                        MERCHANDISEPRODUCTINSTANCEOPTIONVALUE1ID, 
                        MERCHANDISEPRODUCTINSTANCEOPTIONVALUE2ID, 
                        MERCHANDISEPRODUCTINSTANCEOPTIONVALUE3ID,
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED
                    )
                    values
                    (
                        @SALESORDERITEMID, 
                        @MERCHANDISEPRODUCTINSTANCEID, 
                        @MERCHANDISEDEPARTMENTID, 
                        @MERCHANDISEDEPARTMENTCATEGORYID, 
                        @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE1ID, 
                        @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE2ID, 
                        @MERCHANDISEPRODUCTINSTANCEOPTIONVALUE3ID,
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE
                    )
                end

                -- Updating an existing item

                else
                begin
                    update dbo.SALESORDERITEM set
                        QUANTITY += @QUANTITY,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @SALESORDERITEMID
                end

                exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
                exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
                exec dbo.USP_SALESORDER_CALCULATETAXES @SALESORDERID, @CHANGEAGENTID;

                -- Update the inventory

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