USP_DATAFORMTEMPLATE_ADD_MERCHANDISEPRODUCTINSTANCE
The save procedure used by the add dataform template "Merchandise Item Instance Add Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @MERCHANDISEPRODUCTID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @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 | uniqueidentifier | IN | Option 1 value |
| @OPTION2VALUE | uniqueidentifier | IN | Option 2 value |
| @OPTION3VALUE | uniqueidentifier | IN | Option 3 value |
| @LOOKUPCODE | nvarchar(25) | IN | UPC |
| @LOOKUPITEMS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MERCHANDISEPRODUCTINSTANCE
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@MERCHANDISEPRODUCTID uniqueidentifier,
@COST money = null,
@SALEPRICE money = null,
@ONHANDQUANTITY integer = 0,
@OPTIONCOUNT integer = 0,
@OPTION1ID uniqueidentifier = null,
@OPTION2ID uniqueidentifier = null,
@OPTION3ID uniqueidentifier = null,
@OPTION1VALUE uniqueidentifier = null,
@OPTION2VALUE uniqueidentifier = null,
@OPTION3VALUE uniqueidentifier = null,
@LOOKUPCODE nvarchar(25) = null,
@LOOKUPITEMS xml = null
)
as
set nocount on;
if @ID is null
set @ID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
if @OPTIONCOUNT > 0
begin
if exists(
select 1
from dbo.MERCHANDISEPRODUCTINSTANCE
where MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = @MERCHANDISEPRODUCTID
and exists(
select 1
from dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE
where MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
and MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTOPTIONVALUEID = @OPTION1VALUE
)
and (@OPTION2ID is null or exists(
select 1
from dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE
where MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
and MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTOPTIONVALUEID = @OPTION2VALUE
))
and (@OPTION3ID is null or exists(
select 1
from dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE
where MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
and MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTOPTIONVALUEID = @OPTION3VALUE
))
)
begin
raiserror('ERR_MERCHANDISEPRODUCTINSTANCE_OPTIONSNOTUNIQUE', 13, 1);
return 1
end
declare @OPTIONTABLE table(OPTIONID uniqueidentifier, OPTIONVALUEID uniqueidentifier);
insert into @OPTIONTABLE values (@OPTION1ID, @OPTION1VALUE)
insert into @OPTIONTABLE values (@OPTION2ID, @OPTION2VALUE)
insert into @OPTIONTABLE values (@OPTION3ID, @OPTION3VALUE)
insert into dbo.MERCHANDISEPRODUCTINSTANCE
(ID, MERCHANDISEPRODUCTID, COST, SALEPRICE, ONHANDQUANTITY, LOOKUPCODE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @MERCHANDISEPRODUCTID, @COST, @SALEPRICE, @ONHANDQUANTITY, @LOOKUPCODE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
insert into dbo.MERCHANDISEPRODUCTINSTANCEVENDOR
(ID, MERCHANDISEPRODUCTINSTANCEID, COST, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(newid(), @ID, @COST, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
--insert lookup codes
exec dbo.USP_MERCHANDISEPRODUCTINSTANCE_GETLOOKUPITEMS_ADDFROMXML @ID, @LOOKUPITEMS, @CHANGEAGENTID
--insert new product instance options
insert into dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE
(ID, MERCHANDISEPRODUCTINSTANCEID, MERCHANDISEPRODUCTOPTIONVALUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
newid(),
@ID,
O.OPTIONVALUEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@OPTIONTABLE O
where O.OPTIONID is not null;
end
else
begin
insert into dbo.MERCHANDISEPRODUCTINSTANCE
(ID, MERCHANDISEPRODUCTID, COST, SALEPRICE, ONHANDQUANTITY, LOOKUPCODE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @MERCHANDISEPRODUCTID, @COST, @SALEPRICE, @ONHANDQUANTITY, @LOOKUPCODE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
insert into dbo.MERCHANDISEPRODUCTINSTANCEVENDOR
(ID, MERCHANDISEPRODUCTINSTANCEID, COST, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(newid(), @ID, @COST, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
--insert lookup codes
exec dbo.USP_MERCHANDISEPRODUCTINSTANCE_GETLOOKUPITEMS_ADDFROMXML @ID, @LOOKUPITEMS, @CHANGEAGENTID
end
if dbo.UFN_MERCHANDISEPRODUCTINSTANCE_CHECKOPTIONCOUNT(@ID) = 0
begin
raiserror('ERR_MERCHANDISEPRODUCTINSTANCE_OPTIONCOUNTNOTCORRECT', 13, 1);
return 1
end
if @ONHANDQUANTITY > 0
begin
declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);
declare @VENDORID uniqueidentifier = null;
select
@VENDORID = VENDORID
from dbo.PRODUCTVENDOR
where PRODUCTID = @MERCHANDISEPRODUCTID;
insert into dbo.MERCHANDISEPRODUCTINSTANCEHISTORY
(ID, SOURCETYPECODE, ACTIONTYPECODE, ACTIONDATE, MERCHANDISEPRODUCTINSTANCEID, QUANTITY, ONHANDQUANTITY, COST, AVERAGECOST, SALEPRICE, BATCHID, VENDORID, MERCHANDISEADJUSTMENTREASONCODEID, COMMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(newID(), 0, 2, @CURRENTDATETIMEOFFSET, @ID, @ONHANDQUANTITY, 0, @COST, @COST, @SALEPRICE, null, @VENDORID, null, '', @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0