USP_BBNC_COMMITPROFILEUPDATEALUMNIBUILTINATTRIBUTES
Updates a constituent's education history record's built-in attribute information from a Blackbaud Internet Solutions profile transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @EDUCATIONALHISTORYID | uniqueidentifier | IN | |
| @XMLITEMS | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEALUMNIBUILTINATTRIBUTES
(
@EDUCATIONALHISTORYID uniqueidentifier = null,
@XMLITEMS xml = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
) with execute as owner
as
set nocount on;
declare @TOTALROWS int;
declare @ROWCOUNT int;
declare @ATTRIBUTETYPEID int;
declare @DATATYPECODE tinyint;
declare @ALLOWMULTIPLES bit;
declare @TABLENAME nvarchar(100);
declare @ATTRIBUTENAME nvarchar(100);
declare @CODETABLENAME nvarchar(100);
declare @ATTRIBUTECOUNT int;
declare @MSG nvarchar(255);
declare @SQL nvarchar(max);
declare @PARAMS nvarchar(max);
declare @MATCHES int;
declare @ISCODETABLE bit;
declare @ISYESNO bit;
declare @ISCURRENCY bit;
declare @DELETEATTRIBUTE bit;
declare @ATTRIBUTES table
(
[ATTRIBUTETYPEID] int,
[VALUE] nvarchar(max),
[TABLEENTRYID] uniqueidentifier,
[BOOLEANCODE] tinyint,
[CURRENCYID] uniqueidentifier
) ;
declare @TABLES table
(
ROWNUM int identity (1, 1) primary key not null ,
[ATTRIBUTETYPEID] int,
[DATATYPECODE] tinyint,
[ALLOWMULTIPLES] bit,
[TABLENAME] nvarchar(100),
[ATTRIBUTENAME] nvarchar(100),
[CODETABLENAME] nvarchar(100),
[CURRENCYID] uniqueidentifier
) ;
if @EDUCATIONALHISTORYID is null
begin
raiserror('The educational history ID is required',16,1);
return -2;
end
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
set @XMLITEMS =
(
select
ITEM.ELEMENT.query('*[local-name() != "CURRENCYID"]'),
case
when ITEM.ELEMENT.exist('CURRENCYID[1]') = 0
then @ORGANIZATIONCURRENCYID
when len(ITEM.ELEMENT.value('CURRENCYID[1]', 'nvarchar(max)')) = 0
then @ORGANIZATIONCURRENCYID
when ITEM.ELEMENT.value('CURRENCYID[1]', 'uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
then @ORGANIZATIONCURRENCYID
else
ITEM.ELEMENT.value('CURRENCYID[1]', 'uniqueidentifier')
end as CURRENCYID
from
@XMLITEMS.nodes('/ATTRIBUTES/ITEM') ITEM(ELEMENT)
for xml raw('ITEM'),type,elements,root('ATTRIBUTES'),BINARY BASE64
);
-- Build a temporary table to hold the values from XMLITEMS
insert into @ATTRIBUTES
select
[ATTRIBUTETYPEID],
[VALUE],
[TABLEENTRYID],
[BOOLEANCODE],
[CURRENCYID]
from
dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS)
where
[ATTRIBUTETYPEID] >= 500;
-- Build a distinct list of each attribute type to be processed
insert into @TABLES ([ATTRIBUTETYPEID], [DATATYPECODE], [ALLOWMULTIPLES], [TABLENAME], [ATTRIBUTENAME], [CODETABLENAME], [CURRENCYID])
select distinct
ATTRIBUTES.ATTRIBUTETYPEID,
ATTRIBUTECATEGORY.DATATYPECODE + 1,
case when ATTRIBUTECATEGORY.ONLYALLOWONEPERRECORD = 1 then 0 else 1 end as ALLOWMULTIPLES,
TABLECATALOG.TABLENAME,
ATTRIBUTECATEGORY.NAME,
CODETABLECATALOG.DBTABLENAME,
ATTRIBUTES.CURRENCYID
from
@ATTRIBUTES ATTRIBUTES
inner join
dbo.BBNCATTRIBUTECATEGORYIDMAP on ATTRIBUTES.ATTRIBUTETYPEID = BBNCATTRIBUTECATEGORYIDMAP.ID
inner join
dbo.ATTRIBUTECATEGORY on BBNCATTRIBUTECATEGORYIDMAP.ATTRIBUTECATEGORYID = ATTRIBUTECATEGORY.ID
inner join
dbo.TABLECATALOG on ATTRIBUTECATEGORY.TABLECATALOGID = TABLECATALOG.ID
left join
dbo.CODETABLECATALOG on ATTRIBUTECATEGORY.CODETABLECATALOGID = CODETABLECATALOG.ID;
select @TOTALROWS = count(*) from @TABLES;
if @TOTALROWS > 0
begin
set @ROWCOUNT = 1;
begin try
while @ROWCOUNT <= @TOTALROWS
begin
select
@ATTRIBUTETYPEID = ATTRIBUTETYPEID,
@DATATYPECODE = DATATYPECODE,
@ALLOWMULTIPLES = ALLOWMULTIPLES,
@TABLENAME = TABLENAME,
@ATTRIBUTENAME = ATTRIBUTENAME,
@CODETABLENAME = CODETABLENAME
from
@TABLES
where
ROWNUM = @ROWCOUNT;
if @DATATYPECODE = 6 and @CODETABLENAME is not null
set @ISCODETABLE = 1
else
set @ISCODETABLE = 0;
if @DATATYPECODE = 5
set @ISYESNO = 1
else
set @ISYESNO = 0;
if @DATATYPECODE = 4
set @ISCURRENCY = 1
else
set @ISCURRENCY = 0;
if @ALLOWMULTIPLES = 0
begin
select
@ATTRIBUTECOUNT = count(*)
from
@ATTRIBUTES
where
[ATTRIBUTETYPEID] = @ATTRIBUTETYPEID;
if @ATTRIBUTECOUNT > 1
begin
set @MSG = N'The ' + @ATTRIBUTENAME + N' attribute only permits a single entry per educational history record.';
raiserror(@MSG,16,1);
return -2;
end
if @ISCODETABLE = 1
if
(
select top (1)
TABLEENTRYID
from
dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS)
where
[ATTRIBUTETYPEID] = @ATTRIBUTETYPEID
) is null
set @DELETEATTRIBUTE = 1;
else
set @DELETEATTRIBUTE = 0;
else if @ISYESNO = 1
if
(
select top (1)
BOOLEANCODE
from
dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS)
where
[ATTRIBUTETYPEID] = @ATTRIBUTETYPEID
) is null
set @DELETEATTRIBUTE = 1;
else
set @DELETEATTRIBUTE = 0;
else
if
(
select top (1)
VALUE
from
dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS)
where
[ATTRIBUTETYPEID] = @ATTRIBUTETYPEID
and len(VALUE) > 0 --TMV 04/16/2007 CR272679-041207 Text, number, date, and currency attributes cannot have zero-length values
) is null
set @DELETEATTRIBUTE = 1;
else
set @DELETEATTRIBUTE = 0;
set @SQL = N'select @MATCHES = count(*) from dbo.' + @TABLENAME + N' where ID = @EDUCATIONALHISTORYID';
set @PARAMS = N'@EDUCATIONALHISTORYID uniqueidentifier, @MATCHES int output';
exec sp_executesql @SQL, @PARAMS, @EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID, @MATCHES = @MATCHES output;
if @MATCHES > 0
begin
if @DELETEATTRIBUTE = 1
begin
-- Delete
set @SQL = N'delete from dbo.' + @TABLENAME + N' where ID = @EDUCATIONALHISTORYID';
set @PARAMS = N'@EDUCATIONALHISTORYID uniqueidentifier';
exec sp_executesql @SQL, @PARAMS, @EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID;
end
else
begin
-- Update
if @ISCURRENCY = 1
begin
set @SQL = N'with CONVERTEDATTRIBUTE as (select CURRENCYVALUES.BASEAMOUNT as VALUE, CURRENCYVALUES.TRANSACTIONCURRENCYID as CURRENCYID, CURRENCYVALUES.ORGANIZATIONEXCHANGERATEID, CURRENCYVALUES.ORGANIZATIONAMOUNT ';
set @SQL = @SQL + N'from dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(';
set @SQL = @SQL + N'(select top 1 VALUE from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID), ';
set @SQL = @SQL + N'(select top 1 DATEADDED from dbo.' + @TABLENAME + N' where ID = @EDUCATIONALHISTORYID), ';
set @SQL = @SQL + N'(select top 1 CURRENCYID from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID), ';
set @SQL = @SQL + N'null, null, null, null, null, ';
set @SQL = @SQL + N'(select top 1 ORGANIZATIONEXCHANGERATEID from dbo.' + @TABLENAME + N' as ATTRIBUTETABLE ';
set @SQL = @SQL + N'inner join dbo.CURRENCYEXCHANGERATE on ATTRIBUTETABLE.ORGANIZATIONEXCHANGERATEID = CURRENCYEXCHANGERATE.ID ';
set @SQL = @SQL + N'where ATTRIBUTETABLE.ID = @EDUCATIONALHISTORYID and CURRENCYEXCHANGERATE.FROMCURRENCYID = ATTRIBUTETABLE.CURRENCYID and CURRENCYEXCHANGERATE.TOCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()), 1) as CURRENCYVALUES)';
set @SQL = @SQL + N'update dbo.' + @TABLENAME + N' set ';
set @SQL = @SQL + N'VALUE = (select top 1 VALUE from CONVERTEDATTRIBUTE),';
set @SQL = @SQL + N'CURRENCYID = (select top 1 CURRENCYID from CONVERTEDATTRIBUTE),';
set @SQL = @SQL + N'ORGANIZATIONEXCHANGERATEID = (select top 1 ORGANIZATIONEXCHANGERATEID from CONVERTEDATTRIBUTE),';
set @SQL = @SQL + N'CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE ';
set @SQL = @SQL + N'where ID = @EDUCATIONALHISTORYID';
end
else
begin
set @SQL = N'update dbo.' + @TABLENAME + N' set ';
if @ISCODETABLE = 1
set @SQL = @SQL + @CODETABLENAME + N'ID = (select top 1 TABLEENTRYID from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID),'
else if @ISYESNO = 1
set @SQL = @SQL + N'BOOLEANCODE = (select top 1 BOOLEANCODE from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID),';
else
set @SQL = @SQL + N'VALUE = (select top 1 VALUE from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID),';
set @SQL = @SQL + N'CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE ';
set @SQL = @SQL + N'where ID = @EDUCATIONALHISTORYID';
end
set @PARAMS = N'@XMLITEMS xml, @ATTRIBUTETYPEID int, @CHANGEAGENTID uniqueidentifier, @CHANGEDATE datetime, @EDUCATIONALHISTORYID uniqueidentifier';
exec sp_executesql @SQL, @PARAMS, @XMLITEMS = @XMLITEMS, @ATTRIBUTETYPEID = @ATTRIBUTETYPEID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE, @EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID;
end;
end
else
if @DELETEATTRIBUTE = 0
begin
-- Insert
set @SQL = N'insert into dbo.' + @TABLENAME + N' (ID, ';
if @ISCODETABLE = 1
begin
set @SQL = @SQL + @CODETABLENAME + N'ID, ';
set @SQL = @SQL + N'ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) ';
set @SQL = @SQL + N'select top 1 @EDUCATIONALHISTORYID, TABLEENTRYID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID';
end
else if @ISYESNO = 1
begin
set @SQL = @SQL + N'[BOOLEANCODE], ';
set @SQL = @SQL + N'ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) ';
set @SQL = @SQL + N'select top 1 @EDUCATIONALHISTORYID, BOOLEANCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID';
end
else if @ISCURRENCY = 1
begin
set @SQL = @SQL + N'[VALUE], [CURRENCYID], [ORGANIZATIONEXCHANGERATEID], [ORGANIZATIONVALUE], ';
set @SQL = @SQL + N'ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) ';
set @SQL = @SQL + N'select top 1 @EDUCATIONALHISTORYID, ATTRIBUTEITEM.VALUE, ATTRIBUTEITEM.CURRENCYID, CURRENCYVALUES.ORGANIZATIONEXCHANGERATEID, CURRENCYVALUES.ORGANIZATIONAMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE ';
set @SQL = @SQL + N'from (select top 1 VALUE, CURRENCYID from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID) as ATTRIBUTEITEM ';
set @SQL = @SQL + N'outer apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(VALUE, @CHANGEDATE, CURRENCYID, null, null, null, null, null, null, 1) as CURRENCYVALUES';
end
else
begin
set @SQL = @SQL + N'[VALUE], ';
set @SQL = @SQL + N'ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) ';
set @SQL = @SQL + N'select top 1 @EDUCATIONALHISTORYID, VALUE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID';
end;
set @PARAMS = N'@EDUCATIONALHISTORYID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CHANGEDATE datetime, @XMLITEMS xml, @ATTRIBUTETYPEID int';
exec sp_executesql @SQL, @PARAMS, @EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE, @XMLITEMS = @XMLITEMS, @ATTRIBUTETYPEID = @ATTRIBUTETYPEID;
end;
end
else
begin
-- Add any attribute values that don't exist yet
set @SQL = N'insert into dbo.' + @TABLENAME + N' (ID, EDUCATIONALHISTORYID, ';
if @ISCODETABLE = 1
begin
set @SQL = @SQL + @CODETABLENAME + N'ID, ';
set @SQL = @SQL + N'ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) select newid(), @EDUCATIONALHISTORYID, TABLEENTRYID, ';
end
else if @ISYESNO = 1
begin
set @SQL = @SQL + N'[BOOLEANCODE], ';
set @SQL = @SQL + N'ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) select newid(), @EDUCATIONALHISTORYID, [BOOLEANCODE], ';
end
else
begin
set @SQL = @SQL + N'[VALUE], ';
set @SQL = @SQL + N'ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) select newid(), @EDUCATIONALHISTORYID, [VALUE], ';
end;
set @SQL = @SQL + N'@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE ';
set @SQL = @SQL + N'from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) as NEWVALUES where [ATTRIBUTETYPEID] = @ATTRIBUTETYPEID ';
set @SQL = @SQL + N'and not exists (select ID from dbo.' + @TABLENAME + N' where EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID and ';
if @ISCODETABLE = 1
set @SQL = @SQL + @CODETABLENAME + N'ID = NEWVALUES.TABLEENTRYID)';
else if @ISYESNO = 1
set @SQL = @SQL + @TABLENAME + N'.[BOOLEANCODE] = NEWVALUES.[BOOLEANCODE])';
else
set @SQL = @SQL + @TABLENAME + N'.[VALUE] = NEWVALUES.[VALUE])';
set @SQL = @SQL + N' and ';
if @ISCODETABLE = 1
set @SQL = @SQL + N'NEWVALUES.TABLEENTRYID';
else if @ISYESNO = 1
set @SQL = @SQL + N'NEWVALUES.[BOOLEANCODE]';
else
set @SQL = @SQL + N'NEWVALUES.[VALUE]';
set @SQL = @SQL + N' is not null ';
set @PARAMS = N'@EDUCATIONALHISTORYID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CHANGEDATE datetime, @XMLITEMS xml, @ATTRIBUTETYPEID int';
exec sp_executesql @SQL, @PARAMS, @EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE, @XMLITEMS = @XMLITEMS, @ATTRIBUTETYPEID = @ATTRIBUTETYPEID;
-- Remove attribute values that no longer exist
set @SQL = N'delete from dbo.' + @TABLENAME + N' where EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID and ';
if @ISCODETABLE = 1
set @SQL = @SQL + @CODETABLENAME + N'ID not in (select [TABLEENTRYID] ';
else if @ISYESNO = 1
set @SQL = @SQL + N'[BOOLEANCODE] not in (select [BOOLEANCODE] ';
else
set @SQL = @SQL + N'[VALUE] not in (select [VALUE] ';
set @SQL = @SQL + N'from dbo.UFN_BBNC_ATTRIBUTES_FROMITEMLISTXML(@XMLITEMS) where ATTRIBUTETYPEID = @ATTRIBUTETYPEID and ';
if @ISCODETABLE = 1
set @SQL = @SQL + N'[TABLEENTRYID]';
else if @ISYESNO = 1
set @SQL = @SQL + N'[BOOLEANCODE]';
else
set @SQL = @SQL + N'[VALUE]';
set @SQL = @SQL + N' is not null)';
set @PARAMS = N'@EDUCATIONALHISTORYID uniqueidentifier, @XMLITEMS xml, @ATTRIBUTETYPEID int';
exec sp_executesql @SQL, @PARAMS, @EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID, @XMLITEMS = @XMLITEMS, @ATTRIBUTETYPEID = @ATTRIBUTETYPEID;
end;
set @ROWCOUNT = @ROWCOUNT + 1;
end;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
end
return 0;