USP_DATAFORMTEMPLATE_EDITLOAD_RELATIONSHIP_INDTOORG_3

The load procedure used by the edit dataform template "Relationship Individual to Organization Edit Form 3"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CONSTITUENTNAME	nvarchar(700)	INOUT
@RECIPROCALCONSTITUENTID	uniqueidentifier	INOUT	Related constituent
@RELATIONSHIPCONSTITUENTID	uniqueidentifier	INOUT
@RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Relationship type
@RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Reciprocal relationship type
@RECIPROCALCONSTITUENTNAME	nvarchar(700)	INOUT	Related constituent
@RELATIONSHIPSTARTDATE	datetime	INOUT	Start date
@RELATIONSHIPENDDATE	datetime	INOUT	End date
@ISCONTACT	bit	INOUT	The individual is a contact for this organization
@ISPRIMARYCONTACT	bit	INOUT	Primary contact
@CONTACTTYPECODEID	uniqueidentifier	INOUT	Contact type
@POSITION	nvarchar(100)	INOUT	Position
@ISPRIMARYBUSINESS	bit	INOUT	This is the primary business
@ISMATCHINGGIFTRELATIONSHIP	bit	INOUT	The organization will match contributions for this relationship
@RELATIONADDRESSID	uniqueidentifier	INOUT	Address ID
@ORGPRIMARYADDRESSID	uniqueidentifier	INOUT	Org primary address ID
@ADDADDRESS	bit	INOUT	Add Address
@EDITADDRESS	bit	INOUT	Edit Address
@ADDRESSTYPECODEID	uniqueidentifier	INOUT	Type
@PRIMARY	bit	INOUT	Set as primary address
@DONOTMAIL	bit	INOUT	Do not send mail to this address
@STARTDATE	UDT_MONTHDAY	INOUT	Start date
@ENDDATE	UDT_MONTHDAY	INOUT	End date
@COUNTRYID	uniqueidentifier	INOUT	Country
@STATEID	uniqueidentifier	INOUT	State
@ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@CITY	nvarchar(50)	INOUT	City
@POSTCODE	nvarchar(12)	INOUT	ZIP
@CART	nvarchar(10)	INOUT	CART
@DPC	nvarchar(8)	INOUT	DPC
@LOT	nvarchar(5)	INOUT	LOT
@SPOUSENAME	nvarchar(700)	INOUT
@SPOUSEHASMATCHINGADDRESSES	bit	INOUT
@UPDATEMATCHINGSPOUSEADDRESSES	bit	INOUT	Update matching address information for spouse
@OMITFROMVALIDATION	bit	INOUT	Omit this address from validation
@COUNTYCODEID	uniqueidentifier	INOUT	County
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT	Congressional district
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	INOUT	State house district
@STATESENATEDISTRICTCODEID	uniqueidentifier	INOUT	State senate district
@LOCALPRECINCTCODEID	uniqueidentifier	INOUT	Local precinct
@INFOSOURCECODEID	uniqueidentifier	INOUT	Info source
@REGIONCODEID	uniqueidentifier	INOUT	Region
@LASTVALIDATIONATTEMPTDATE	datetime	INOUT	Last attempt
@VALIDATIONMESSAGE	nvarchar(200)	INOUT	Validation message
@CERTIFICATIONDATA	int	INOUT
@NCOALASTSUBMITDATE	datetime	INOUT	Last submit date
@NCOARETURN	nvarchar(150)	INOUT	Return code
@NCOAFOOTNOTE	nvarchar(150)	INOUT	Footnote
@NCOADPVFOOTNOTE	nvarchar(150)	INOUT	DPV footnote
@NCOAMOVEDATE	UDT_FUZZYDATE	INOUT	Move date
@NCOADMASUPPRESSION	bit	INOUT	DMA suppression
@NCOAMAILGRADE	nvarchar(150)	INOUT	Mail grade
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@VALIDATIONCOUNTRIES	xml	INOUT
@ZIPLOOKUPCOUNTRIES	xml	INOUT
@UPDATECONTACTS	bit	INOUT	Update contacts that use this address
@HASCONTACTS	bit	INOUT	Has contacts
@DONOTMAILREASONCODEID	uniqueidentifier	INOUT	Reason
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT	Comments
@COMMENTS	nvarchar(max)	INOUT	Comments
@CONSTITUENTTYPE	int	INOUT
@RELATIONPHONEID	uniqueidentifier	INOUT
@ADDPHONE	bit	INOUT
@EDITPHONE	bit	INOUT
@PHONETYPECODEID	uniqueidentifier	INOUT	Type
@NUMBER	nvarchar(100)	INOUT	Number
@PHONECOUNTRYID	uniqueidentifier	INOUT	Country
@PHONESTARTTIME	UDT_HOURMINUTE	INOUT	Call after
@PHONEENDTIME	UDT_HOURMINUTE	INOUT	Call before
@PHONESTARTDATE	date	INOUT	Start date
@PHONEENDDATE	date	INOUT	End date
@PHONEPRIMARY	bit	INOUT	Set as primary phone number
@DONOTCALL	bit	INOUT	Do not call this phone number
@DONOTCALLREASONCODEID	uniqueidentifier	INOUT	Reason
@PHONEISCONFIDENTIAL	bit	INOUT	This phone number is confidential
@PHONEINFOSOURCECODEID	uniqueidentifier	INOUT	Information source
@PHONEINFOSOURCECOMMENTS	nvarchar(256)	INOUT	Comments
@RELATIONEMAILADDRESSID	uniqueidentifier	INOUT
@ADDEMAILADDRESS	bit	INOUT
@EDITEMAILADDRESS	bit	INOUT
@EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT	Type
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@EMAILADDRESSPRIMARY	bit	INOUT	Set as primary email address
@DONOTEMAIL	bit	INOUT	Do not send email to this address
@EMAILADDRESSINFOSOURCECODEID	uniqueidentifier	INOUT	Information source
@EMAILADDRESSINFOSOURCECOMMENTS	nvarchar(256)	INOUT	Comments
@RELATIONSHIPADDRESSHASSMAILPREFRENCE	bit	INOUT	Relationship address has mail preference
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply to constituent for revenue from organization
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition credit match percent
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply to organization for revenue from constituent
@RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition credit match percent
@PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition credit type
@RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition credit type
@PRIMARYISCONSTITUENT	bit	INOUT
@HISTORICALSTARTDATE	date	INOUT
@HISTORICALENDDATE	date	INOUT
@EMAILSTARTDATE	date	INOUT
@EMAILENDDATE	date	INOUT
@SYNCENDDATETOHISTORY	bit	INOUT
@HASEMPLOYMENTHISTORY	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RELATIONSHIP_INDTOORG_3
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @CONSTITUENTNAME nvarchar(700) = null output,
  @RECIPROCALCONSTITUENTID uniqueidentifier = null output,
  @RELATIONSHIPCONSTITUENTID uniqueidentifier = null output,
  @RELATIONSHIPTYPECODEID uniqueidentifier = null output,
  @RECIPROCALTYPECODEID uniqueidentifier = null output,
  @RECIPROCALCONSTITUENTNAME nvarchar(700) = null output,
  @RELATIONSHIPSTARTDATE datetime = null output,
  @RELATIONSHIPENDDATE datetime = null output,
  @ISCONTACT bit = null output,
  @ISPRIMARYCONTACT bit = null output,
  @CONTACTTYPECODEID uniqueidentifier = null output,
  @POSITION nvarchar(100) = null output,
  @ISPRIMARYBUSINESS bit = null output,
  @ISMATCHINGGIFTRELATIONSHIP bit = null output,
  @RELATIONADDRESSID uniqueidentifier = null output,
  @ORGPRIMARYADDRESSID uniqueidentifier = null output,
  @ADDADDRESS bit = null output,
  @EDITADDRESS bit = null output,
  @ADDRESSTYPECODEID uniqueidentifier = null output,
  @PRIMARY bit = null output,
  @DONOTMAIL bit = null output,
  @STARTDATE dbo.UDT_MONTHDAY = null output,
  @ENDDATE dbo.UDT_MONTHDAY = null output,
  @COUNTRYID uniqueidentifier = null output,
  @STATEID uniqueidentifier = null output,
  @ADDRESSBLOCK nvarchar(150) = null output,
  @CITY nvarchar(50) = null output,
  @POSTCODE nvarchar(12) = null output,
  @CART nvarchar(10) = null output,
  @DPC nvarchar(8) = null output,
  @LOT nvarchar(5) = null output,
  @SPOUSENAME nvarchar(700) = null output,
  @SPOUSEHASMATCHINGADDRESSES bit = null output,
  @UPDATEMATCHINGSPOUSEADDRESSES bit = null output,
  @OMITFROMVALIDATION bit = null output,
  @COUNTYCODEID uniqueidentifier = null output,
  @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
  @STATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
  @STATESENATEDISTRICTCODEID uniqueidentifier = null output,
  @LOCALPRECINCTCODEID uniqueidentifier = null output,
  @INFOSOURCECODEID uniqueidentifier = null output,
  @REGIONCODEID uniqueidentifier = null output,
  @LASTVALIDATIONATTEMPTDATE datetime = null output,
  @VALIDATIONMESSAGE nvarchar(200) = null output,
  @CERTIFICATIONDATA integer = null output,
  @NCOALASTSUBMITDATE datetime = null output,
  @NCOARETURN nvarchar(150) = null output,
  @NCOAFOOTNOTE nvarchar(150) = null output,
  @NCOADPVFOOTNOTE nvarchar(150) = null output,
  @NCOAMOVEDATE UDT_FUZZYDATE = null output,
  @NCOADMASUPPRESSION bit = null output,
  @NCOAMAILGRADE nvarchar(150) = null output,
  @TSLONG bigint = 0 output,
  @VALIDATIONCOUNTRIES xml = null output,
  @ZIPLOOKUPCOUNTRIES xml = null output,
  @UPDATECONTACTS bit = null output,
  @HASCONTACTS bit = null output,
  @DONOTMAILREASONCODEID uniqueidentifier = null output,
  @INFOSOURCECOMMENTS nvarchar(256) = null output,
  @COMMENTS nvarchar(max) = null output,
  @CONSTITUENTTYPE int = null output,
  @RELATIONPHONEID uniqueidentifier = null output,
  @ADDPHONE bit = null output,
  @EDITPHONE bit = null output,
  @PHONETYPECODEID uniqueidentifier = null output,
  @NUMBER nvarchar(100) = null output,
  @PHONECOUNTRYID uniqueidentifier = null output,
  @PHONESTARTTIME dbo.UDT_HOURMINUTE = null output,
  @PHONEENDTIME dbo.UDT_HOURMINUTE = null output,
  @PHONESTARTDATE date = null output,
  @PHONEENDDATE date = null output,
  @PHONEPRIMARY bit = null output,
  @DONOTCALL bit = null output,
  @DONOTCALLREASONCODEID uniqueidentifier = null output,
  @PHONEISCONFIDENTIAL bit = null output,
  @PHONEINFOSOURCECODEID uniqueidentifier = null output,
  @PHONEINFOSOURCECOMMENTS nvarchar(256) = null output,
  @RELATIONEMAILADDRESSID uniqueidentifier = null output,
  @ADDEMAILADDRESS bit = null output,
  @EDITEMAILADDRESS bit = null output,
  @EMAILADDRESSTYPECODEID uniqueidentifier = null output,
  @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
  @EMAILADDRESSPRIMARY bit = null output,
  @DONOTEMAIL bit = null output,
  @EMAILADDRESSINFOSOURCECODEID uniqueidentifier = null output,
  @EMAILADDRESSINFOSOURCECOMMENTS nvarchar(256) = null output,
  @RELATIONSHIPADDRESSHASSMAILPREFRENCE bit = null output,
  @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = null output,
  @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
  @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = null output,
  @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
  @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null output,
  @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null output,
  @PRIMARYISCONSTITUENT bit = null output,
  @HISTORICALSTARTDATE date = null output,
  @HISTORICALENDDATE date = null output,
  @EMAILSTARTDATE date = null output,
  @EMAILENDDATE date = null output,
  @SYNCENDDATETOHISTORY bit = null output,
  @HASEMPLOYMENTHISTORY bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  set @CONSTITUENTTYPE = 0;
  set @RELATIONSHIPADDRESSHASSMAILPREFRENCE = 0;
  set @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 0;
  set @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 0;

  select 
    @DATALOADED = 1,
    @ADDADDRESS = 0,
    @EDITADDRESS = 0,
    @CONSTITUENTNAME = CONSTIT1_NF.NAME,
    @PRIMARYISCONSTITUENT = CONSTIT1.ISCONSTITUENT,
    @RECIPROCALCONSTITUENTNAME = CONSTIT2_NF.NAME,
    @RECIPROCALCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID,
    @RELATIONSHIPCONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
    @RELATIONSHIPTYPECODEID = RELATIONSHIP.RELATIONSHIPTYPECODEID,
    @RECIPROCALTYPECODEID = RELATIONSHIP.RECIPROCALTYPECODEID,                             
    @RELATIONSHIPSTARTDATE = RELATIONSHIP.STARTDATE,
    @RELATIONSHIPENDDATE = RELATIONSHIP.ENDDATE,
    @ISCONTACT = RELATIONSHIP.ISCONTACT,
    @ISPRIMARYCONTACT = RELATIONSHIP.ISPRIMARYCONTACT,
    @CONTACTTYPECODEID = RELATIONSHIP.CONTACTTYPECODEID,
    @POSITION = RELATIONSHIP.POSITION,
    @ISPRIMARYBUSINESS = RELATIONSHIP.ISPRIMARYBUSINESS,
    @ISMATCHINGGIFTRELATIONSHIP = RELATIONSHIP.ISMATCHINGGIFTRELATIONSHIP,
    @TSLONG = RELATIONSHIP.TSLONG,
    @COMMENTS = RELATIONSHIP.COMMENTS,
    @SYNCENDDATETOHISTORY = 1,
    @HASEMPLOYMENTHISTORY = case when RELATIONSHIPJOBINFO.ID is null then 0 else 1 end
  from
    dbo.RELATIONSHIP
  left join
    dbo.CONSTITUENT as CONSTIT1 on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTIT1.ID
  left join
    dbo.RELATIONSHIPJOBINFO on RELATIONSHIP.RELATIONSHIPSETID = RELATIONSHIPJOBINFO.RELATIONSHIPSETID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RELATIONSHIPCONSTITUENTID) CONSTIT1_NF
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) CONSTIT2_NF    
  where
    RELATIONSHIP.ID = @ID;

  declare @RECIPROCALRELATIONSHIPID uniqueidentifier;

  select
    @RECIPROCALRELATIONSHIPID = ID
  from
    dbo.RELATIONSHIP
  where
    RELATIONSHIPCONSTITUENTID = @RECIPROCALCONSTITUENTID and
    RECIPROCALCONSTITUENTID = @RELATIONSHIPCONSTITUENTID and
    RELATIONSHIPTYPECODEID = @RECIPROCALTYPECODEID and
    RECIPROCALTYPECODEID = @RELATIONSHIPTYPECODEID;

  select @RELATIONADDRESSID = ID from dbo.ADDRESS where RELATIONSHIPID = @RECIPROCALRELATIONSHIPID;

  select @ORGPRIMARYADDRESSID = ID from dbo.ADDRESS where CONSTITUENTID = @RECIPROCALCONSTITUENTID and ISPRIMARY = 1;

  exec dbo.USP_ADDRESS_UPDATE_LOAD @RELATIONADDRESSID, null, @ADDRESSTYPECODEID output, @PRIMARY output, @DONOTMAIL output,
    @STARTDATE output, @ENDDATE output, @COUNTRYID output, @STATEID output, @ADDRESSBLOCK output, @CITY output,
    @POSTCODE output, @CART output, @DPC output, @LOT output, @SPOUSENAME output, @SPOUSEHASMATCHINGADDRESSES output,
    @UPDATEMATCHINGSPOUSEADDRESSES output, @OMITFROMVALIDATION output, @COUNTYCODEID output, @CONGRESSIONALDISTRICTCODEID output,
    @STATEHOUSEDISTRICTCODEID output, @STATESENATEDISTRICTCODEID output, @LOCALPRECINCTCODEID output, @INFOSOURCECODEID output,
    @REGIONCODEID output, @LASTVALIDATIONATTEMPTDATE output, @VALIDATIONMESSAGE output,    @CERTIFICATIONDATA output,
    @NCOALASTSUBMITDATE output,    @NCOARETURN output,    @NCOAFOOTNOTE output, @NCOADPVFOOTNOTE output, @NCOAMOVEDATE output,
    @NCOADMASUPPRESSION output,    @NCOAMAILGRADE output, @TSLONG output, @VALIDATIONCOUNTRIES output,    @ZIPLOOKUPCOUNTRIES output,
    @UPDATECONTACTS output,    @HASCONTACTS output, @DONOTMAILREASONCODEID output, @HISTORICALSTARTDATE output, @HISTORICALENDDATE output,
    default, @INFOSOURCECOMMENTS output;

  if @RELATIONADDRESSID is not null 
    if exists(select top 1 ID from dbo.MAILPREFERENCE where ADDRESSID = @RELATIONADDRESSID)
      set @RELATIONSHIPADDRESSHASSMAILPREFRENCE = 1;

  select @RELATIONPHONEID = ID from dbo.PHONE where RELATIONSHIPID = @RECIPROCALRELATIONSHIPID;

  exec dbo.USP_PHONE_UPDATE_LOAD @RELATIONPHONEID, null, null, @PHONETYPECODEID output,
    @NUMBER output, @PHONEPRIMARY output, @DONOTCALL output, null, null, null, null, @PHONESTARTTIME output, @PHONEENDTIME output,
    @PHONEINFOSOURCECODEID output, @PHONEINFOSOURCECOMMENTS output, @PHONECOUNTRYID output, @PHONESTARTDATE output, @PHONEENDDATE output,
    @DONOTCALLREASONCODEID output, @PHONEISCONFIDENTIAL output;

  select @RELATIONEMAILADDRESSID = ID from dbo.EMAILADDRESS where RELATIONSHIPID = @RECIPROCALRELATIONSHIPID;

  exec dbo.USP_EMAILADDRESS_UPDATE_LOAD @RELATIONEMAILADDRESSID, null, null, @EMAILADDRESSTYPECODEID output,
    @EMAILADDRESS output, @EMAILADDRESSPRIMARY output, @DONOTEMAIL output, null, null,
    null, null, @EMAILADDRESSINFOSOURCECODEID output, @EMAILADDRESSINFOSOURCECOMMENTS output, null,
    @EMAILSTARTDATE output, @EMAILENDDATE output;

  select
    @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
    @PRIMARYSOFTCREDITMATCHFACTOR = MATCHFACTOR,
    @PRIMARYRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
  from
    dbo.REVENUERECOGNITIONDEFAULT
  where
    SOURCECONSTITUENTID = @RELATIONSHIPCONSTITUENTID and
    RECIPIENTCONSTITUENTID = @RECIPROCALCONSTITUENTID and
    PREVENTRECOGNITIONSDEFAULTING = 0;

  select
    @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
    @RECIPROCALSOFTCREDITMATCHFACTOR = MATCHFACTOR,
    @RECIPROCALRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
  from
    dbo.REVENUERECOGNITIONDEFAULT
  where
    SOURCECONSTITUENTID = @RECIPROCALCONSTITUENTID and
    RECIPIENTCONSTITUENTID = @RELATIONSHIPCONSTITUENTID and
    PREVENTRECOGNITIONSDEFAULTING = 0;

  set @PRIMARYSOFTCREDITMATCHFACTOR = coalesce(@PRIMARYSOFTCREDITMATCHFACTOR, 100);
  set @RECIPROCALSOFTCREDITMATCHFACTOR = coalesce(@RECIPROCALSOFTCREDITMATCHFACTOR, 100);

  return 0;