USP_DATAFORMTEMPLATE_ADD_DESIGNATION
The save procedure used by the add dataform template "Designation Add 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. |
| @DESIGNATIONID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @DESIGNATIONLEVELID | uniqueidentifier | IN | Purpose |
| @LOOKUPID | nvarchar(450) | IN | Lookup ID |
| @VANITYNAME | nvarchar(512) | IN | Public name |
| @DESIGNATIONREPORTCODE1ID | uniqueidentifier | IN | Report code 1 |
| @DESIGNATIONREPORTCODE2ID | uniqueidentifier | IN | Report code 2 |
| @VSECATEGORYID | uniqueidentifier | IN | VSE category |
| @CAMPAIGNID | uniqueidentifier | IN | Campaign |
| @STARTDATE | datetime | IN | Start date |
| @ENDDATE | datetime | IN | End date |
| @DESIGNATIONUSECODEID | uniqueidentifier | IN | Use code |
| @VSESUBCATEGORYID | uniqueidentifier | IN | VSE subcategory |
| @ISREVENUEDESIGNATION | bit | IN | Revenue designation |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_DESIGNATION
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@DESIGNATIONID uniqueidentifier,
@DESIGNATIONLEVELID uniqueidentifier = null,
@LOOKUPID nvarchar(450) = null, --WI150316 Sized down from 512, as LOOKUPID would not fit in an index with that many characters
@VANITYNAME nvarchar(512) = null,
@DESIGNATIONREPORTCODE1ID uniqueidentifier = null,
@DESIGNATIONREPORTCODE2ID uniqueidentifier = null,
@VSECATEGORYID uniqueidentifier = null,
@CAMPAIGNID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@DESIGNATIONUSECODEID uniqueidentifier = null,
@VSESUBCATEGORYID uniqueidentifier = null,
@ISREVENUEDESIGNATION bit = 1
)
as
begin
set nocount on;
declare @CURRENTDATE datetime;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
declare @DESIGNATIONLEVEL1ID uniqueidentifier;
declare @DESIGNATIONLEVEL2ID uniqueidentifier;
declare @DESIGNATIONLEVEL3ID uniqueidentifier;
declare @DESIGNATIONLEVEL4ID uniqueidentifier;
declare @DESIGNATIONLEVEL5ID uniqueidentifier;
select @DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL1ID,
@DESIGNATIONLEVEL2ID = DESIGNATIONLEVEL2ID,
@DESIGNATIONLEVEL3ID = DESIGNATIONLEVEL3ID,
@DESIGNATIONLEVEL4ID = DESIGNATIONLEVEL4ID,
@DESIGNATIONLEVEL5ID = DESIGNATIONLEVEL5ID
from dbo.DESIGNATION
where DESIGNATION.ID = @DESIGNATIONID;
begin try
if @VANITYNAME is null
set @VANITYNAME = '';
if @DESIGNATIONLEVEL1ID is null
set @DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
else
if @DESIGNATIONLEVEL2ID is null
set @DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID
else
if @DESIGNATIONLEVEL3ID is null
set @DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID
else
if @DESIGNATIONLEVEL4ID is null
set @DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID
else
set @DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
if len(@VANITYNAME) = 0
raiserror('ERR_VANITYNAME_REQUIRED', 13, 1);
declare @BASECURRENCYID uniqueidentifier;
select @BASECURRENCYID =
(select BASECURRENCYID from dbo.DESIGNATIONLEVEL where ID = @DESIGNATIONLEVELID)
insert into dbo.DESIGNATION (
ID,
DESIGNATIONLEVEL1ID,
DESIGNATIONLEVEL2ID,
DESIGNATIONLEVEL3ID,
DESIGNATIONLEVEL4ID,
DESIGNATIONLEVEL5ID,
USERID,
VANITYNAME,
DESIGNATIONREPORT1CODEID,
DESIGNATIONREPORT2CODEID,
VSECATEGORYID,
STARTDATE,
ENDDATE,
DESIGNATIONUSECODEID,
VSESUBCATEGORYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ISREVENUEDESIGNATION,
BASECURRENCYID)
VALUES (
@ID,
@DESIGNATIONLEVEL1ID,
@DESIGNATIONLEVEL2ID,
@DESIGNATIONLEVEL3ID,
@DESIGNATIONLEVEL4ID,
@DESIGNATIONLEVEL5ID,
@LOOKUPID,
@VANITYNAME,
@DESIGNATIONREPORTCODE1ID,
@DESIGNATIONREPORTCODE2ID,
@VSECATEGORYID,
@STARTDATE,
@ENDDATE,
@DESIGNATIONUSECODEID,
@VSESUBCATEGORYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ISREVENUEDESIGNATION,
@BASECURRENCYID);
if @CAMPAIGNID is not null
insert into dbo.DESIGNATIONCAMPAIGN (
ID,
DESIGNATIONID,
CAMPAIGNID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
) values (
newid(),
@ID,
@CAMPAIGNID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0
end