USP_DATAFORMTEMPLATE_ADD_MERCHANDISE_VENDOR
The save procedure used by the add dataform template "Merchandise Vendor 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. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @WEBADDRESS | UDT_WEBADDRESS | IN | Website |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @ADDRESS_CITY | nvarchar(50) | IN | City |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | ZIP |
| @ADDRESS_STATEID | uniqueidentifier | IN | State |
| @PHONENUMBER | nvarchar(100) | IN | Phone |
| @EMAILADDRESS | nvarchar(100) | IN | |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | Country |
| @PICTURE | varbinary | IN | Image |
| @PICTURETHUMBNAIL | varbinary | IN | Image thumbnail |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | Address type |
| @PHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @EMAILTYPECODEID | uniqueidentifier | IN | Email type |
| @PRIMARYCONTACTID | uniqueidentifier | IN | Full name |
| @PRIMARYCONTACTLASTNAME | nvarchar(100) | IN | Last name |
| @PRIMARYCONTACTFIRSTNAME | nvarchar(50) | IN | First name |
| @PRIMARYCONTACTMIDDLENAME | nvarchar(50) | IN | Middle name |
| @PRIMARYCONTACTCOUNTRYID | uniqueidentifier | IN | Country |
| @PRIMARYCONTACTADDRESS | nvarchar(150) | IN | Address |
| @PRIMARYCONTACTCITY | nvarchar(50) | IN | City |
| @PRIMARYCONTACTSTATEID | uniqueidentifier | IN | State |
| @PRIMARYCONTACTPOSTCODE | nvarchar(12) | IN | Postcode |
| @PRIMARYCONTACTPHONENUMBER | nvarchar(100) | IN | Phone |
| @PRIMARYCONTACTEMAILADDRESS | nvarchar(100) | IN | |
| @PRIMARYCONTACTADDRESSTYPECODEID | uniqueidentifier | IN | Address type |
| @PRIMARYCONTACTPHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @PRIMARYCONTACTEMAILTYPECODEID | uniqueidentifier | IN | Email type |
| @PRIMARYCONTACTRELATIONSHIPTYPECODEID | uniqueidentifier | IN | Vendor is the |
| @PRIMARYCONTACTRECIPROCALTYPECODEID | uniqueidentifier | IN | Contact is the |
| @PRIMARYCONTACTTYPECODEID | uniqueidentifier | IN | Contact type |
| @PICTURECHANGED | bit | IN | Picture changed |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MERCHANDISE_VENDOR
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100) = '',
@DESCRIPTION nvarchar(255) = '',
@WEBADDRESS dbo.UDT_WEBADDRESS = '',
@ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
@ADDRESS_CITY nvarchar(50) = '',
@ADDRESS_POSTCODE nvarchar(12) = '',
@ADDRESS_STATEID uniqueidentifier = null,
@PHONENUMBER nvarchar(100) = '',
@EMAILADDRESS nvarchar(100) = '',
@ADDRESS_COUNTRYID uniqueidentifier = null,
@PICTURE varbinary(max) = null,
@PICTURETHUMBNAIL varbinary(max) = null,
@ADDRESSTYPECODEID uniqueidentifier = null,
@PHONETYPECODEID uniqueidentifier = null,
@EMAILTYPECODEID uniqueidentifier = null,
@PRIMARYCONTACTID uniqueidentifier = null,
@PRIMARYCONTACTLASTNAME nvarchar(100) = '',
@PRIMARYCONTACTFIRSTNAME nvarchar(50) = '',
@PRIMARYCONTACTMIDDLENAME nvarchar(50) = '',
@PRIMARYCONTACTCOUNTRYID uniqueidentifier = null,
@PRIMARYCONTACTADDRESS nvarchar(150) = '',
@PRIMARYCONTACTCITY nvarchar(50) = '',
@PRIMARYCONTACTSTATEID uniqueidentifier = null,
@PRIMARYCONTACTPOSTCODE nvarchar(12) = '',
@PRIMARYCONTACTPHONENUMBER nvarchar(100) = '',
@PRIMARYCONTACTEMAILADDRESS nvarchar(100) = '',
@PRIMARYCONTACTADDRESSTYPECODEID uniqueidentifier = null,
@PRIMARYCONTACTPHONETYPECODEID uniqueidentifier = null,
@PRIMARYCONTACTEMAILTYPECODEID uniqueidentifier = null,
@PRIMARYCONTACTRELATIONSHIPTYPECODEID uniqueidentifier = null,
@PRIMARYCONTACTRECIPROCALTYPECODEID uniqueidentifier = null,
@PRIMARYCONTACTTYPECODEID uniqueidentifier = null,
@PICTURECHANGED bit = 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()
declare @PRIMARYCONTACTADDRESSID uniqueidentifier
declare @PRIMARYCONTACTPHONEID uniqueidentifier
declare @PRIMARYCONTACTEMAILID uniqueidentifier
begin try
if @PICTURECHANGED = 0
begin
set @PICTURE = null
set @PICTURETHUMBNAIL = null
end
-------------------------------------------------------------------------
--------------------------------- VENDOR --------------------------------
-------------------------------------------------------------------------
-- Constituent record
insert into dbo.CONSTITUENT
(ID, KEYNAME, WEBADDRESS, ISORGANIZATION, PICTURE, PICTURETHUMBNAIL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @NAME, @WEBADDRESS, 1, @PICTURE, @PICTURETHUMBNAIL, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
-- Vendor record
insert into dbo.VENDOR
(ID, DESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @DESCRIPTION, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
-- Address record
if @ADDRESS_ADDRESSBLOCK <> '' or @ADDRESS_STATEID is not null or @ADDRESS_CITY <> '' or @ADDRESS_POSTCODE <> ''
begin
insert into dbo.[ADDRESS]
(ID, CONSTITUENTID, ADDRESSBLOCK, CITY, POSTCODE, STATEID, COUNTRYID, ISPRIMARY, ADDRESSTYPECODEID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(newid(), @ID, @ADDRESS_ADDRESSBLOCK, @ADDRESS_CITY, @ADDRESS_POSTCODE, @ADDRESS_STATEID, @ADDRESS_COUNTRYID, 1, @ADDRESSTYPECODEID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
if @PHONENUMBER <> ''
begin
insert into dbo.PHONE
(ID, CONSTITUENTID, NUMBER, ISPRIMARY, PHONETYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(newid(), @ID, @PHONENUMBER, 1, @PHONETYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
if @EMAILADDRESS <> ''
begin
insert into dbo.EMAILADDRESS
(ID, CONSTITUENTID, EMAILADDRESS, ISPRIMARY, EMAILADDRESSTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(newid(), @ID, @EMAILADDRESS, 1, @EMAILTYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
-------------------------------------------------------------------------
--------------------------------- CONTACT -------------------------------
-------------------------------------------------------------------------
if @PRIMARYCONTACTID is null
begin
if @PRIMARYCONTACTLASTNAME <> ''
begin
set @PRIMARYCONTACTID = newid()
insert into dbo.[CONSTITUENT]
(
[ID],
[ISORGANIZATION],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@PRIMARYCONTACTID,
0,
@PRIMARYCONTACTLASTNAME,
@PRIMARYCONTACTFIRSTNAME,
@PRIMARYCONTACTMIDDLENAME,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @PRIMARYCONTACTADDRESS <> '' or
@PRIMARYCONTACTSTATEID is not null or
@PRIMARYCONTACTCITY <> '' or
@PRIMARYCONTACTPOSTCODE <> ''
insert into dbo.[ADDRESS]
(
[CONSTITUENTID],
[ISPRIMARY],
[ADDRESSTYPECODEID],
[COUNTRYID],
[ADDRESSBLOCK],
[CITY],
[STATEID],
[POSTCODE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
) values (
@PRIMARYCONTACTID,
1,
@PRIMARYCONTACTADDRESSTYPECODEID,
@PRIMARYCONTACTCOUNTRYID,
@PRIMARYCONTACTADDRESS,
@PRIMARYCONTACTCITY,
@PRIMARYCONTACTSTATEID,
@PRIMARYCONTACTPOSTCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @PRIMARYCONTACTPHONENUMBER <> ''
insert into dbo.[PHONE]
(
[CONSTITUENTID],
[PHONETYPECODEID],
[NUMBER],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@PRIMARYCONTACTID,
@PRIMARYCONTACTPHONETYPECODEID,
@PRIMARYCONTACTPHONENUMBER,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @PRIMARYCONTACTEMAILADDRESS <> ''
insert into dbo.[EMAILADDRESS]
(
[CONSTITUENTID],
[EMAILADDRESSTYPECODEID],
[EMAILADDRESS],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@PRIMARYCONTACTID,
@PRIMARYCONTACTEMAILTYPECODEID,
@PRIMARYCONTACTEMAILADDRESS,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end
if @PRIMARYCONTACTID is not null
begin
-- create the primary contact relationship
declare @RELATIONSHIPID uniqueidentifier = newid();
declare @RELATIONSHIPSETID uniqueidentifier = newid();
insert into dbo.[RELATIONSHIPSET]
(
[ID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@RELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.[RELATIONSHIP]
(
[ID],
[RELATIONSHIPCONSTITUENTID],
[RECIPROCALCONSTITUENTID],
[RELATIONSHIPTYPECODEID],
[RECIPROCALTYPECODEID],
[ISCONTACT],
[ISPRIMARYCONTACT],
[CONTACTTYPECODEID],
[RELATIONSHIPSETID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@RELATIONSHIPID,
@ID,
@PRIMARYCONTACTID,
@PRIMARYCONTACTRELATIONSHIPTYPECODEID,
@PRIMARYCONTACTRECIPROCALTYPECODEID,
1,
1,
@PRIMARYCONTACTTYPECODEID,
@RELATIONSHIPSETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @PRIMARYCONTACTID, @PRIMARYCONTACTRELATIONSHIPTYPECODEID, @PRIMARYCONTACTRECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
set @PRIMARYCONTACTADDRESSID = (select ID from dbo.ADDRESS where CONSTITUENTID = @PRIMARYCONTACTID and ISPRIMARY = 1);
set @PRIMARYCONTACTPHONEID = (select ID from dbo.PHONE where CONSTITUENTID = @PRIMARYCONTACTID and ISPRIMARY = 1);
set @PRIMARYCONTACTEMAILID = (select ID from dbo.EMAILADDRESS where CONSTITUENTID = @PRIMARYCONTACTID and ISPRIMARY = 1);
update dbo.ADDRESS
set
RELATIONSHIPID = @RELATIONSHIPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @PRIMARYCONTACTADDRESSID
update dbo.PHONE
set
RELATIONSHIPID = @RELATIONSHIPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @PRIMARYCONTACTPHONEID
update dbo.EMAILADDRESS
set
RELATIONSHIPID = @RELATIONSHIPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @PRIMARYCONTACTEMAILID
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0