USP_DATAFORMTEMPLATE_ADD_MERCHANDISEPRODUCT
The save procedure used by the add dataform template "Merchandise Item 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. |
| @PICTURE | varbinary | IN | Image |
| @PICTURETHUMBNAIL | varbinary | IN | Image thumbnail |
| @NAME | nvarchar(50) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @MERCHANDISEDEPARTMENTID | uniqueidentifier | IN | Department |
| @MERCHANDISEDEPARTMENTCATEGORYID | uniqueidentifier | IN | Category |
| @VENDORID | uniqueidentifier | IN | Vendor |
| @ISDISCOUNTABLE | bit | IN | Discounts allowed |
| @ONHANDQUANTITY | int | IN | Quantity |
| @SALEPRICE | money | IN | Sale price |
| @COST | money | IN | Cost |
| @HASOPTIONS | bit | IN | Merchandise includes dimensions |
| @OPTIONITEMS | xml | IN | Options |
| @LOOKUPCODE | nvarchar(25) | IN | UPC |
| @PICTURECHANGED | bit | IN | Picture changed |
| @TAXID | uniqueidentifier | IN | |
| @LOOKUPITEMS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MERCHANDISEPRODUCT
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@PICTURE varbinary(max) = null,
@PICTURETHUMBNAIL varbinary(max) = null,
@NAME nvarchar(50) = '',
@DESCRIPTION nvarchar(255) = '',
@MERCHANDISEDEPARTMENTID uniqueidentifier = null,
@MERCHANDISEDEPARTMENTCATEGORYID uniqueidentifier = null,
@VENDORID uniqueidentifier = null,
@ISDISCOUNTABLE bit = 1,
@ONHANDQUANTITY integer = 0,
@SALEPRICE money = 0,
@COST money = 0,
@HASOPTIONS bit = 0,
@OPTIONITEMS xml = null,
@LOOKUPCODE nvarchar(25) = null,
@PICTURECHANGED bit = null,
@TAXID uniqueidentifier = 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 @PICTURECHANGED = 0
begin
set @PICTURE = null
set @PICTURETHUMBNAIL = null
end
if exists(select 1 from dbo.PRODUCT where NAME = @NAME)
raiserror('BBERR_PRODUCTNAME_NOTUNIQUE',13,1)
if @HASOPTIONS = 1
begin
if exists(
select 1 from @OPTIONITEMS.nodes('/OPTIONITEMS/ITEM') T1(items)
where (
select count(*) from @OPTIONITEMS.nodes('/OPTIONITEMS/ITEM') T2(items)
where T2.items.value('(OPTIONGROUP)[1]','nvarchar(50)') = T1.items.value('(OPTIONGROUP)[1]','nvarchar(50)')
) > 1
)
raiserror('BBERR_OPTIONSNOTUNIQUE',13,1)
end
if @ISDISCOUNTABLE = 1 and dbo.UFN_MERCHANDISEDEPARTMENT_ISDISCOUNTABLE(@MERCHANDISEDEPARTMENTID) = 0
raiserror('BBERR_DEPARTMENTNOTDISCOUNTABLE', 13, 1);
insert into dbo.PRODUCT
(ID, NAME, DESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @NAME, @DESCRIPTION, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
insert into dbo.MERCHANDISEPRODUCT
(ID, MERCHANDISEDEPARTMENTID, MERCHANDISEDEPARTMENTCATEGORYID, ONHANDQUANTITY,
PICTURE, PICTURETHUMBNAIL, ISDISCOUNTABLE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TAXID)
values
(@ID, @MERCHANDISEDEPARTMENTID, @MERCHANDISEDEPARTMENTCATEGORYID, @ONHANDQUANTITY,
@PICTURE, @PICTURETHUMBNAIL, @ISDISCOUNTABLE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @TAXID)
insert into dbo.PRODUCTVENDOR
(ID, VENDORID, PRODUCTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(newid(), @VENDORID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
if @HASOPTIONS = 0
begin
declare @MERCHANDISEPRODUCTINSTANCEID uniqueidentifier = newID();
declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);
insert into dbo.MERCHANDISEPRODUCTINSTANCE
(ID, MERCHANDISEPRODUCTID, COST, SALEPRICE, ONHANDQUANTITY, LOOKUPCODE, ISACTIVE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@MERCHANDISEPRODUCTINSTANCEID, @ID, @COST, @SALEPRICE, @ONHANDQUANTITY, @LOOKUPCODE, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
insert into dbo.MERCHANDISEPRODUCTINSTANCEVENDOR
(ID, MERCHANDISEPRODUCTINSTANCEID, COST, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(newID(), @MERCHANDISEPRODUCTINSTANCEID, @COST, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
-- insert lookup codes
exec dbo.USP_MERCHANDISEPRODUCTINSTANCE_GETLOOKUPITEMS_ADDFROMXML @MERCHANDISEPRODUCTINSTANCEID, @LOOKUPITEMS, @CHANGEAGENTID
if @ONHANDQUANTITY > 0
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, @MERCHANDISEPRODUCTINSTANCEID, @ONHANDQUANTITY, 0, @COST, @COST, @SALEPRICE, null, @VENDORID, null, '', @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
else
begin
insert into dbo.MERCHANDISEPRODUCTOPTION
(ID, MERCHANDISEPRODUCTID, NAME, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
newid(),
@ID,
T.items.value('(OPTIONGROUP)[1]','nvarchar(50)'),
isnull(T.items.value('(SEQUENCE)[1]','int'),0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@OPTIONITEMS.nodes('/OPTIONITEMS/ITEM') T(items)
end
-- Set up sales method availability
insert into dbo.MERCHANDISEPRODUCTSALESMETHOD
(ID,MERCHANDISEPRODUCTID,SALESMETHODID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select
newid(),
@ID,
SM.ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.SALESMETHOD SM
-- only configure daily sales for now
where SM.TYPECODE = 0
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0