USP_DATAFORMTEMPLATE_VIEW_REVENUEBATCHCONSTITUENTPRIMARYCONTACTINFO
The load procedure used by the view dataform template "Revenue Batch Constituent Primary Contact Information View Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @ADDRESSTYPELABEL | nvarchar(100) | INOUT | ADDRESSTYPELABEL |
| @ADDRESSTYPEID | uniqueidentifier | INOUT | ADDRESSTYPEID |
| @ADDRESS | nvarchar(150) | INOUT | ADDRESS |
| @CITY | nvarchar(50) | INOUT | CITY |
| @STATEID | uniqueidentifier | INOUT | STATEID |
| @POSTCODE | nvarchar(12) | INOUT | POSTCODE |
| @COUNTRYID | uniqueidentifier | INOUT | COUNTRYID |
| @PHONETYPELABEL | nvarchar(100) | INOUT | PHONETYPELABEL |
| @PHONETYPEID | uniqueidentifier | INOUT | PHONETYPEID |
| @PHONE | nvarchar(100) | INOUT | PHONE |
| @EMAILTYPELABEL | nvarchar(100) | INOUT | EMAILTYPELABEL |
| @EMAILTYPEID | uniqueidentifier | INOUT | EMAILTYPEID |
| UDT_EMAILADDRESS | INOUT | ||
| @WEBADDRESS | UDT_WEBADDRESS | INOUT | WEBADDRESS |
| @DONOTMAIL | bit | INOUT | DONOTMAIL |
| @DONOTMAILREASONCODEID | uniqueidentifier | INOUT | DONOTMAILREASONCODEID |
| @DONOTCALL | bit | INOUT | DONOTCALL |
| @DONOTEMAIL | bit | INOUT | DONOTEMAIL |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUEBATCHCONSTITUENTPRIMARYCONTACTINFO
(
@ID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@DATALOADED bit = 0 output,
@ADDRESSTYPELABEL nvarchar(100) = null output,
@ADDRESSTYPEID uniqueidentifier = null output,
@ADDRESS nvarchar(150) = null output,
@CITY nvarchar(50) = null output,
@STATEID uniqueidentifier = null output,
@POSTCODE nvarchar(12) = null output,
@COUNTRYID uniqueidentifier = null output,
@PHONETYPELABEL nvarchar(100) = null output,
@PHONETYPEID uniqueidentifier = null output,
@PHONE nvarchar(100) = null output,
@EMAILTYPELABEL nvarchar(100) = null output,
@EMAILTYPEID uniqueidentifier = null output,
@EMAIL dbo.UDT_EMAILADDRESS = null output,
@WEBADDRESS dbo.UDT_WEBADDRESS = null output,
@DONOTMAIL bit = null output,
@DONOTMAILREASONCODEID uniqueidentifier = null output,
@DONOTCALL bit = null output,
@DONOTEMAIL bit = null output
)
as
set nocount on;
set @DATALOADED = 1;
-- Check if the ID corresponds to a BATCHREVENUECONSTITUENT record but that record
-- references an existing constituent. If that's the case, set @ID to the CONSTITUENT
-- table ID.
select
@ID = EXISTINGCONSTITUENTID
from dbo.BATCHREVENUECONSTITUENT
where
ID = @ID and
EXISTINGCONSTITUENTID is not null
-- Determine if the constituent is in the CONSTITUENT table or BATCHREVENUECONSTITUENT table
if exists (select ID from dbo.CONSTITUENT where ID = @ID)
begin
-- Check security for this constituent. Using UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT
-- since this form's security is implied through other forms.
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
if @ISADMIN = 0
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
if @ISADMIN = 0 and @APPUSER_IN_NONRACROLE = 0
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
if @ISADMIN = 0 and
@APPUSER_IN_NONRACROLE = 0 and
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, @ID, @APPUSER_IN_NOSECGROUPROLE) = 0
raiserror('BBERR_DB_RECORDSECURITY_PERMISSION_DENIED', 13, 1)
select
@ADDRESSTYPELABEL = ADDRESSTYPECODE.DESCRIPTION,
@ADDRESSTYPEID = ADDRESS.ADDRESSTYPECODEID,
@ADDRESS = ADDRESS.ADDRESSBLOCK,
@CITY = ADDRESS.CITY,
@STATEID = ADDRESS.STATEID,
@POSTCODE = ADDRESS.POSTCODE,
@COUNTRYID = ADDRESS.COUNTRYID,
@DONOTMAIL = ADDRESS.DONOTMAIL,
@DONOTMAILREASONCODEID = ADDRESS.DONOTMAILREASONCODEID
from
dbo.ADDRESS
left join dbo.ADDRESSTYPECODE
on ADDRESS.ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
where
ADDRESS.CONSTITUENTID = @ID
and ADDRESS.ISPRIMARY = 1;
select
@PHONETYPELABEL = PHONETYPECODE.DESCRIPTION,
@PHONETYPEID = PHONE.PHONETYPECODEID,
@PHONE = PHONE.NUMBER,
@DONOTCALL = PHONE.DONOTCALL
from
dbo.PHONE
left join dbo.PHONETYPECODE
on PHONE.PHONETYPECODEID = PHONETYPECODE.ID
where
PHONE.CONSTITUENTID = @ID
and PHONE.ISPRIMARY = 1;
select
@EMAILTYPELABEL = EMAILADDRESSTYPECODE.DESCRIPTION,
@EMAILTYPEID = EMAILADDRESS.EMAILADDRESSTYPECODEID,
@EMAIL = EMAILADDRESS.EMAILADDRESS,
@DONOTEMAIL = EMAILADDRESS.DONOTEMAIL
from
dbo.EMAILADDRESS
left join dbo.EMAILADDRESSTYPECODE
on EMAILADDRESS.EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODE.ID
where
EMAILADDRESS.CONSTITUENTID = @ID
and EMAILADDRESS.ISPRIMARY = 1;
select
@WEBADDRESS = CONSTITUENT.WEBADDRESS
from
dbo.CONSTITUENT
where
CONSTITUENT.ID = @ID;
end
else
begin
select
@ADDRESSTYPELABEL = ATC.DESCRIPTION,
@ADDRESSTYPEID = C.ADDRESSTYPECODEID,
@ADDRESS = C.ADDRESSBLOCK,
@CITY = C.CITY,
@STATEID = C.STATEID,
@POSTCODE = C.POSTCODE,
@COUNTRYID = C.COUNTRYID,
@PHONETYPELABEL = PTC.DESCRIPTION,
@PHONETYPEID = C.PHONETYPECODEID,
@PHONE = C.NUMBER,
@EMAILTYPELABEL = ETC.DESCRIPTION,
@EMAILTYPEID = C.EMAILADDRESSTYPECODEID,
@EMAIL = C.EMAILADDRESS,
@WEBADDRESS = C.WEBADDRESS,
@DONOTMAIL = C.DONOTMAIL,
@DONOTMAILREASONCODEID = C.DONOTMAILREASONCODEID,
@DONOTCALL = C.DONOTCALL,
@DONOTEMAIL = C.DONOTEMAIL
from dbo.BATCHREVENUECONSTITUENT C
left join ADDRESSTYPECODE ATC on C.ADDRESSTYPECODEID = ATC.ID
left join PHONETYPECODE PTC on C.PHONETYPECODEID = PTC.ID
left join EMAILADDRESSTYPECODE ETC on C.EMAILADDRESSTYPECODEID = ETC.ID
where
C.ID = @ID
end