USP_DATAFORMTEMPLATE_EDIT_PROSPECTSEGMENTATION
The save procedure used by the edit dataform template "Prospect Segmentation Edit 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. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @OTHERSCANMODIFY | bit | IN | Others can modify |
| @RESEARCHGROUPID | uniqueidentifier | IN | Research group |
| @IDSETREGISTERID | uniqueidentifier | IN | Selection |
| @PRIMARYVARIABLE | tinyint | IN | Select score/rating |
| @SECONDARYVARIABLE1 | tinyint | IN | Select score/rating |
| @SECONDARYVARIABLE2 | tinyint | IN | Secondary variable 2 |
| @PRIMARYSECTIONS | xml | IN | |
| @SECONDARYSECTIONS1 | xml | IN | |
| @SECONDARYSECTIONS2 | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PROSPECTSEGMENTATION (
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier,
@NAME nvarchar(100),
@DESCRIPTION nvarchar(255),
@OTHERSCANMODIFY bit,
@RESEARCHGROUPID uniqueidentifier,
@IDSETREGISTERID uniqueidentifier,
@PRIMARYVARIABLE tinyint,
@SECONDARYVARIABLE1 tinyint,
@SECONDARYVARIABLE2 tinyint,
@PRIMARYSECTIONS xml,
@SECONDARYSECTIONS1 xml,
@SECONDARYSECTIONS2 xml
) as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare
@InfoMsg nvarchar(100),
@ErrorMessage nvarchar(1000),
@ErrorSeverity int,
@ErrorState int;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @USERCANMODIFY bit;
set @USERCANMODIFY = 0;
select
@USERCANMODIFY = 1
from
dbo.PROSPECTSEGMENTATION PS
left join
dbo.APPUSER AU on AU.ID = PS.OWNERID
where
PS.ID = @ID and
(PS.OTHERSCANMODIFY = 1 or
PS.OWNERID = @CURRENTAPPUSERID or
PS.OWNERID is null or
dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1);
begin try
if @USERCANMODIFY = 1
begin
--Validate the Section Counts
declare @PRIMARYSECTIONSCOUNT int;
declare @SECONDARYSECTIONS1COUNT int;
declare @SECONDARYSECTIONS2COUNT int;
select
@PRIMARYSECTIONSCOUNT = COUNT(T.c.value('(SEQUENCE)[1]','int'))
from
@PRIMARYSECTIONS.nodes('/PRIMARYSECTIONS/ITEM') T(c)
select
@SECONDARYSECTIONS1COUNT = COUNT(T.c.value('(SEQUENCE)[1]','int'))
from
@SECONDARYSECTIONS1.nodes('/SECONDARYSECTIONS1/ITEM') T(c)
select
@SECONDARYSECTIONS2COUNT = COUNT(T.c.value('(SEQUENCE)[1]','int'))
from
@SECONDARYSECTIONS2.nodes('/SECONDARYSECTIONS2/ITEM') T(c)
if @PRIMARYSECTIONSCOUNT < 1
raiserror ('ERR_PRIMARYSECTIONS_EMPTY',13,1);
if @SECONDARYSECTIONS1COUNT < 1
raiserror ('ERR_SECONDARYSECTIONS1_EMPTY',13,1);
if @SECONDARYSECTIONS2COUNT < 1 and @SECONDARYVARIABLE2 > 0
raiserror ('ERR_SECONDARYSECTIONS2_EMPTY',13,1);
if @SECONDARYSECTIONS1COUNT < @SECONDARYSECTIONS2COUNT
raiserror ('ERR_SECONDARYSECTIONS2_TOOMANY',13,1);
--Clear out any existing segment members
exec dbo.USP_PROSPECTSEGMENTATION_PROSPECTS_CLEAR @ID, @CHANGEAGENTID;
--Update Primary Variable
declare @PRIMARYVARIABLEID uniqueidentifier
select
@PRIMARYVARIABLEID = ID
from
PROSPECTSEGMENTATIONVARIABLE
where
PROSPECTSEGMENTATIONID = @ID and
ISPRIMARY = 1
update
dbo.PROSPECTSEGMENTATIONVARIABLE
set
TYPECODE = @PRIMARYVARIABLE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @PRIMARYVARIABLEID
set @PRIMARYSECTIONS =
(select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
T.c.value('(LABEL)[1]','nvarchar(20)') AS 'LABEL',
isnull(T.c.value('(RANGELOW)[1]','int'),-1) AS 'RANGELOW',
isnull(T.c.value('(RANGEHIGH)[1]','int'), -1) AS 'RANGEHIGH',
T.c.value('(ESTIMATEDWEALTHLOWID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHLOWID',
T.c.value('(ESTIMATEDWEALTHHIGHID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHHIGHID',
T.c.value('(MAJORGIVINGCAPACITYLOWID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYLOWID',
T.c.value('(MAJORGIVINGCAPACITYHIGHID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYHIGHID',
T.c.value('(TARGETGIFTRANGELOWID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGELOWID',
T.c.value('(TARGETGIFTRANGEHIGHID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGEHIGHID',
T.c.value('(WEALTHESTIMATORRATINGLOWID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGLOWID',
T.c.value('(WEALTHESTIMATORRATINGHIGHID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGHIGHID'
from
@PRIMARYSECTIONS.nodes('/PRIMARYSECTIONS/ITEM') T(c)
for
xml raw('ITEM'),type,elements,root('PRIMARYSECTIONS'),BINARY BASE64
)
begin try
exec dbo.USP_PROSPECTSEGMENTATION_SECTIONS_PRIMARY_UPDATEFROMXML @PRIMARYVARIABLEID,
@PRIMARYSECTIONS,
@CHANGEAGENTID;
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50001';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
--Update Secondary Variable
declare @SECONDARYVARIABLEID uniqueidentifier
select
@SECONDARYVARIABLEID = ID
from
PROSPECTSEGMENTATIONVARIABLE
where
PROSPECTSEGMENTATIONID = @ID and
ISPRIMARY = 0
update
dbo.PROSPECTSEGMENTATIONVARIABLE
set
TYPECODE = @SECONDARYVARIABLE1,
TYPE2CODE = @SECONDARYVARIABLE2,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @SECONDARYVARIABLEID
declare @SECONDARYSECTIONS xml
set @SECONDARYSECTIONS =
(select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
T.c.value('(LABEL)[1]','nvarchar(20)') AS 'LABEL',
isnull(T.c.value('(RANGELOW)[1]','int'),-1) AS 'RANGELOW',
isnull(T.c.value('(RANGEHIGH)[1]','int'), -1) AS 'RANGEHIGH',
T.c.value('(ESTIMATEDWEALTHLOWID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHLOWID',
T.c.value('(ESTIMATEDWEALTHHIGHID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHHIGHID',
T.c.value('(MAJORGIVINGCAPACITYLOWID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYLOWID',
T.c.value('(MAJORGIVINGCAPACITYHIGHID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYHIGHID',
T.c.value('(TARGETGIFTRANGELOWID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGELOWID',
T.c.value('(TARGETGIFTRANGEHIGHID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGEHIGHID',
T.c.value('(WEALTHESTIMATORRATINGLOWID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGLOWID',
T.c.value('(WEALTHESTIMATORRATINGHIGHID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGHIGHID',
isnull(T2.c.value('(LABEL)[1]','nvarchar(20)'),'') AS 'LABEL2',
isnull(T2.c.value('(RANGELOW)[1]','int'),-1) AS 'RANGE2LOW',
isnull(T2.c.value('(RANGEHIGH)[1]','int'), -1) AS 'RANGE2HIGH',
T2.c.value('(ESTIMATEDWEALTHLOWID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTH2LOWID',
T2.c.value('(ESTIMATEDWEALTHHIGHID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTH2HIGHID',
T2.c.value('(MAJORGIVINGCAPACITYLOWID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITY2LOWID',
T2.c.value('(MAJORGIVINGCAPACITYHIGHID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITY2HIGHID',
T2.c.value('(TARGETGIFTRANGELOWID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGE2LOWID',
T2.c.value('(TARGETGIFTRANGEHIGHID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGE2HIGHID',
T2.c.value('(WEALTHESTIMATORRATINGLOWID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATING2LOWID',
T2.c.value('(WEALTHESTIMATORRATINGHIGHID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATING2HIGHID'
from
@SECONDARYSECTIONS1.nodes('/SECONDARYSECTIONS1/ITEM') T(c)
left join
@SECONDARYSECTIONS2.nodes('/SECONDARYSECTIONS2/ITEM') T2(c)
on
T.c.value('(SEQUENCE)[1]','int') = T2.c.value('(SEQUENCE)[1]','int')
for
xml raw('ITEM'),type,elements,root('SECONDARYSECTIONS'),BINARY BASE64
)
begin try
exec dbo.USP_PROSPECTSEGMENTATION_SECTIONS_SECONDARY_UPDATEFROMXML @SECONDARYVARIABLEID,
@SECONDARYSECTIONS,
@CHANGEAGENTID;
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50002';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
update
dbo.PROSPECTSEGMENTATION
set
NAME = @NAME,
DESCRIPTION = @DESCRIPTION,
OTHERSCANMODIFY = @OTHERSCANMODIFY,
RESEARCHGROUPID = @RESEARCHGROUPID,
IDSETREGISTERID = @IDSETREGISTERID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
end
else begin
raiserror ('ERR_PROSPECTSEGMENTATION_CANNOTMODIFY',13,1);
return 0;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;