USP_DATAFORMTEMPLATE_ADD_PROSPECTRESEARCHREQUESTCONSTITUENT

The save procedure used by the add dataform template "Prospect Research Request Constituent Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@RECORDTYPECODE	tinyint	IN	Record type
@STATUSCODE	tinyint	IN	Status
@CONSTITUENTS	xml	IN	Prospects to add
@CONSTITUENTNAMES	xml	IN	Prospects to add
@PROSPECTRESEARCHREQUESTPRIORITYCODEID	uniqueidentifier	IN	Priority
@DUEDATE	date	IN	Due date
@PROSPECTRESEARCHREQUESTTYPECODEID	uniqueidentifier	IN	Research type
@PROSPECTRESEARCHREQUESTREASONCODEID	uniqueidentifier	IN	Request reason
@NOTES	nvarchar(max)	IN	Notes
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PROSPECTRESEARCHREQUESTCONSTITUENT
(
    @ID uniqueidentifier = null output,
    @CONTEXTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @RECORDTYPECODE tinyint = null,
    @STATUSCODE tinyint = null,
    @CONSTITUENTS xml = null,
    @CONSTITUENTNAMES xml = null,
    @PROSPECTRESEARCHREQUESTPRIORITYCODEID uniqueidentifier = null,
    @DUEDATE date = null,
    @PROSPECTRESEARCHREQUESTTYPECODEID uniqueidentifier = null,
    @PROSPECTRESEARCHREQUESTREASONCODEID uniqueidentifier = null,
    @NOTES nvarchar(max) = null,
    @CURRENTAPPUSERID uniqueidentifier
)
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 @REQUESTNO int
select @REQUESTNO = REQUESTNO from dbo.PROSPECTRESEARCHREQUEST PRR where PRR.ID = @CONTEXTID

declare @CURRENTCOUNT int
select @CURRENTCOUNT = count(PRRC.ID) from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT PRRC where PRRC.PROSPECTRESEARCHREQUESTID = @CONTEXTID

set @STATUSCODE = 1

begin try
    if coalesce((select count(distinct CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_FROMITEMLISTXML(@CONSTITUENTS)), 0)
       <> coalesce((select count(CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_FROMITEMLISTXML(@CONSTITUENTS)), 0) or
       coalesce((select count(distinct CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTNAMES_FROMITEMLISTXML(@CONSTITUENTNAMES)), 0)
       <> coalesce((select count(CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTNAMES_FROMITEMLISTXML(@CONSTITUENTNAMES)), 0)
    begin
        raiserror('BBERR_PROSPECTRESEARCHREQUESTCONSTITUENT_DUPLICATECONSTITUENTS', 13, 1);
        return 1;
    end
    else if (select count(CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_FROMITEMLISTXML(@CONSTITUENTS)) = 0 and 
        (select count(CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTNAMES_FROMITEMLISTXML(@CONSTITUENTNAMES)) = 0 begin
        raiserror('BBERR_PROSPECTRESEARCHREQUESTCONSTITUENT_NOCONSTITUENTS', 13, 1);
        return 1;
    end

    declare @CONSTITUENTSTOADD table (
        ID uniqueidentifier,
        CONSTITUENTID uniqueidentifier
    );

    if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 3
        insert into @CONSTITUENTSTOADD (
            ID,
            CONSTITUENTID
        )
        select
            ID,
            CONSTITUENTID
        from
            dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_FROMITEMLISTXML(@CONSTITUENTS)
        where
            coalesce(CONSTITUENTID, '00000000-0000-0000-0000-000000000000') <> '00000000-0000-0000-0000-000000000000';
    else
        insert into @CONSTITUENTSTOADD (
            ID,
            CONSTITUENTID
        )
        select
            ID,
            CONSTITUENTID
        from
            dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTNAMES_FROMITEMLISTXML(@CONSTITUENTNAMES)
        where
            coalesce(CONSTITUENTID, '00000000-0000-0000-0000-000000000000') <> '00000000-0000-0000-0000-000000000000';

    insert into dbo.PROSPECTRESEARCHREQUESTCONSTITUENT
        (ID, PROSPECTRESEARCHREQUESTID, 
        REQUESTNO, CONSTITUENTID, STATUSCODE, PROSPECTRESEARCHREQUESTPRIORITYCODEID, DUEDATE, 
        PROSPECTRESEARCHREQUESTTYPECODEID, PROSPECTRESEARCHREQUESTREASONCODEID, NOTES, 
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
        ID, @CONTEXTID, 
        coalesce(convert(varchar, @REQUESTNO) + '-' + convert(varchar, row_number() over(order by CONSTITUENTID) + @CURRENTCOUNT), ''),
        CONSTITUENTID, @STATUSCODE, @PROSPECTRESEARCHREQUESTPRIORITYCODEID, @DUEDATE, 
        @PROSPECTRESEARCHREQUESTTYPECODEID, @PROSPECTRESEARCHREQUESTREASONCODEID, @NOTES, 
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from
        @CONSTITUENTSTOADD;

    --Send alert for each constituent request added
    declare @PROSPECTRESEARCHREQUESTCONSTITUENTID uniqueidentifier;
    declare PROSPECTRESEARCHREQUESTCONSTITUENTCURSOR cursor local fast_forward for
    select
        ID
    from
        @CONSTITUENTSTOADD;

    open PROSPECTRESEARCHREQUESTCONSTITUENTCURSOR;
    fetch next from PROSPECTRESEARCHREQUESTCONSTITUENTCURSOR into @PROSPECTRESEARCHREQUESTCONSTITUENTID;
    while @@FETCH_STATUS = 0 begin
        exec dbo.USP_PROSPECTRESEARCHREQUESTCONSTITUENT_CREATEDALERT_SEND @CURRENTAPPUSERID, @PROSPECTRESEARCHREQUESTCONSTITUENTID;
        exec dbo.USP_PROSPECTRESEARCHREQUESTCONSTITUENT_UNASSIGNEDPENDINGALERT_SEND @CURRENTAPPUSERID, @PROSPECTRESEARCHREQUESTCONSTITUENTID;

        fetch next from PROSPECTRESEARCHREQUESTCONSTITUENTCURSOR into @PROSPECTRESEARCHREQUESTCONSTITUENTID;
    end
    close PROSPECTRESEARCHREQUESTCONSTITUENTCURSOR;
    deallocate PROSPECTRESEARCHREQUESTCONSTITUENTCURSOR;
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0