USP_DATAFORMTEMPLATE_EDIT_MERCHANDISEPRODUCTINSTANCE

The save procedure used by the edit dataform template "Merchandise Item Instance Edit Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@COST	money	IN	Cost
@SALEPRICE	money	IN	Sale price
@ONHANDQUANTITY	int	IN	Quantity
@OPTIONCOUNT	int	IN	Option count
@OPTION1ID	uniqueidentifier	IN	Option 1 ID
@OPTION2ID	uniqueidentifier	IN	Option 2 ID
@OPTION3ID	uniqueidentifier	IN	Option 3 ID
@OPTION1VALUE	nvarchar(50)	IN	Option 1 value
@OPTION2VALUE	nvarchar(50)	IN	Option 2 value
@OPTION3VALUE	nvarchar(50)	IN	Option 3 value
@INSTANCEOPTION1ID	uniqueidentifier	IN	Option 1 value
@INSTANCEOPTION2ID	uniqueidentifier	IN	Option 2 value
@INSTANCEOPTION3ID	uniqueidentifier	IN	Option 3 value
@LOOKUPCODE	nvarchar(25)	IN	UPC

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MERCHANDISEPRODUCTINSTANCE (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @COST money,
                    @SALEPRICE money,
                    @ONHANDQUANTITY integer,                              
                    @OPTIONCOUNT integer,          
                    @OPTION1ID uniqueidentifier,
                    @OPTION2ID uniqueidentifier,
                    @OPTION3ID uniqueidentifier,
                    @OPTION1VALUE nvarchar(50),
                    @OPTION2VALUE nvarchar(50),
                    @OPTION3VALUE nvarchar(50),
                    @INSTANCEOPTION1ID uniqueidentifier,
                    @INSTANCEOPTION2ID uniqueidentifier,
                    @INSTANCEOPTION3ID uniqueidentifier,
                    @LOOKUPCODE nvarchar(25)
                )
                as

                set nocount on;

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

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate()

                begin try
                    -- handle updating the data
                    update dbo.MERCHANDISEPRODUCTINSTANCE set
                        COST = @COST,
                        SALEPRICE = @SALEPRICE,
                        ONHANDQUANTITY = @ONHANDQUANTITY,
                        LOOKUPCODE = @LOOKUPCODE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @ID

                if @OPTIONCOUNT > 0
                begin
                    declare @OPTIONTABLE table(OPTIONID uniqueidentifier, OPTIONVALUE nvarchar(50), INSTANCEOPTIONID uniqueidentifier)
                    insert into @OPTIONTABLE values (@OPTION1ID, @OPTION1VALUE, @INSTANCEOPTION1ID)
                    insert into @OPTIONTABLE values (@OPTION2ID, @OPTION2VALUE, @INSTANCEOPTION2ID)
                    insert into @OPTIONTABLE values (@OPTION3ID, @OPTION3VALUE, @INSTANCEOPTION3ID)

                    --insert new product options
                    insert into dbo.MERCHANDISEPRODUCTOPTIONVALUE
                        (ID, MERCHANDISEPRODUCTOPTIONID, NAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        newid(),
                        O.OPTIONID,
                        O.OPTIONVALUE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    from
                        @OPTIONTABLE O
                        left outer join dbo.MERCHANDISEPRODUCTOPTIONVALUE on
                            O.OPTIONVALUE = MERCHANDISEPRODUCTOPTIONVALUE.NAME and
                            O.OPTIONID = MERCHANDISEPRODUCTOPTIONVALUE.MERCHANDISEPRODUCTOPTIONID                    
                        where MERCHANDISEPRODUCTOPTIONVALUE.ID is null
                        and O.OPTIONID is not null

                    --update existing product instance options
                    update dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE set
                        MERCHANDISEPRODUCTOPTIONVALUEID = MERCHANDISEPRODUCTOPTIONVALUE.ID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    from
                        dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE 
                        inner join @OPTIONTABLE O on
                            MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.ID = O.INSTANCEOPTIONID
                        inner join dbo.MERCHANDISEPRODUCTOPTIONVALUE on                      
                            O.OPTIONVALUE = MERCHANDISEPRODUCTOPTIONVALUE.NAME and
                            O.OPTIONID = MERCHANDISEPRODUCTOPTIONVALUE.MERCHANDISEPRODUCTOPTIONID                                          

                end

                if dbo.UFN_MERCHANDISEPRODUCTINSTANCE_CHECKOPTIONCOUNT(@ID) = 0
                begin
                    raiserror('ERR_MERCHANDISEPRODUCTINSTANCE_OPTIONCOUNTNOTCORRECT', 13, 1);
                    return 1
                end

                if dbo.UFN_MERCHANDISEPRODUCTINSTANCE_OPTIONSUNIQUE(@ID) = 0
                begin
                    raiserror('ERR_MERCHANDISEPRODUCTINSTANCE_OPTIONSNOTUNIQUE', 13, 1);
                    return 1
                end      

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

                return 0;