USP_DATAFORMTEMPLATE_EDITLOAD_RESERVATIONCONTACTINFORMATION

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@CONTACTCONSTITUENTID	uniqueidentifier	INOUT
@ADDRESSID	uniqueidentifier	INOUT
@PHONEID	uniqueidentifier	INOUT
@EMAILADDRESSID	uniqueidentifier	INOUT
@CONSTITUENTNAME	nvarchar(max)	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@ISORGANIZATION	bit	INOUT
@CONTACTADDRESS	nvarchar(300)	INOUT
@CONTACTPHONE	nvarchar(100)	INOUT
@CONTACTEMAILADDRESS	nvarchar(100)	INOUT
@CONTACTRELATIONSHIPID	uniqueidentifier	INOUT
@RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT
@RECIPROCALTYPECODEID	uniqueidentifier	INOUT
@RELATIONSHIPSTARTDATE	datetime	INOUT
@RELATIONSHIPENDDATE	datetime	INOUT
@ISCONTACT	bit	INOUT
@ISPRIMARYCONTACT	bit	INOUT
@CONTACTTYPECODEID	uniqueidentifier	INOUT
@POSITION	nvarchar(50)	INOUT
@ISPRIMARYBUSINESS	bit	INOUT
@ISMATCHINGGIFTRELATIONSHIP	bit	INOUT
@ADDADDRESS	bit	INOUT
@EDITADDRESS	bit	INOUT
@ADDRESSTYPECODEID	uniqueidentifier	INOUT
@PRIMARY	bit	INOUT
@DONOTMAIL	bit	INOUT
@STARTDATE	UDT_MONTHDAY	INOUT
@ENDDATE	UDT_MONTHDAY	INOUT
@COUNTRYID	uniqueidentifier	INOUT
@STATEID	uniqueidentifier	INOUT
@ADDRESSBLOCK	nvarchar(150)	INOUT
@CITY	nvarchar(50)	INOUT
@POSTCODE	nvarchar(12)	INOUT
@CART	nvarchar(10)	INOUT
@DPC	nvarchar(8)	INOUT
@LOT	nvarchar(5)	INOUT
@SPOUSEHASMATCHINGADDRESSES	bit	INOUT
@UPDATEMATCHINGSPOUSEADDRESSES	bit	INOUT
@OMITFROMVALIDATION	bit	INOUT
@COUNTYCODEID	uniqueidentifier	INOUT
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	INOUT
@STATESENATEDISTRICTCODEID	uniqueidentifier	INOUT
@LOCALPRECINCTCODEID	uniqueidentifier	INOUT
@INFOSOURCECODEID	uniqueidentifier	INOUT
@REGIONCODEID	uniqueidentifier	INOUT
@LASTVALIDATIONATTEMPTDATE	datetime	INOUT
@VALIDATIONMESSAGE	nvarchar(200)	INOUT
@CERTIFICATIONDATA	int	INOUT
@NCOALASTSUBMITDATE	datetime	INOUT
@NCOARETURN	nvarchar(150)	INOUT
@NCOAFOOTNOTE	nvarchar(150)	INOUT
@NCOADPVFOOTNOTE	nvarchar(150)	INOUT
@NCOAMOVEDATE	UDT_FUZZYDATE	INOUT
@NCOADMASUPPRESSION	bit	INOUT
@NCOAMAILGRADE	nvarchar(150)	INOUT
@UPDATECONTACTS	bit	INOUT
@HASCONTACTS	bit	INOUT
@DONOTMAILREASONCODEID	uniqueidentifier	INOUT
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT
@COMMENTS	nvarchar(max)	INOUT
@ADDPHONE	bit	INOUT
@EDITPHONE	bit	INOUT
@PHONETYPECODEID	uniqueidentifier	INOUT
@NUMBER	nvarchar(100)	INOUT
@PHONECOUNTRYID	uniqueidentifier	INOUT
@PHONESTARTTIME	UDT_HOURMINUTE	INOUT
@PHONEENDTIME	UDT_HOURMINUTE	INOUT
@PHONESTARTDATE	date	INOUT
@PHONEENDDATE	date	INOUT
@PHONEPRIMARY	bit	INOUT
@DONOTCALL	bit	INOUT
@DONOTCALLREASONCODEID	uniqueidentifier	INOUT
@PHONEISCONFIDENTIAL	bit	INOUT
@PHONEINFOSOURCECODEID	uniqueidentifier	INOUT
@PHONEINFOSOURCECOMMENTS	nvarchar(256)	INOUT
@ADDEMAILADDRESS	bit	INOUT
@EDITEMAILADDRESS	bit	INOUT
@EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT
@EMAILADDRESSPRIMARY	bit	INOUT
@DONOTEMAIL	bit	INOUT
@EMAILADDRESSINFOSOURCECODEID	uniqueidentifier	INOUT
@EMAILADDRESSINFOSOURCECOMMENTS	nvarchar(256)	INOUT
@RELATIONSHIPADDRESSHASSMAILPREFRENCE	bit	INOUT
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT
@RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT
@PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	INOUT
@RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	INOUT
@PRIMARYISCONSTITUENT	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RESERVATIONCONTACTINFORMATION (
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @CONTACTCONSTITUENTID uniqueidentifier = null output,
    @ADDRESSID uniqueidentifier = null output,
    @PHONEID uniqueidentifier = null output,
    @EMAILADDRESSID uniqueidentifier = null output,
    @CONSTITUENTNAME nvarchar(max) = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @ISORGANIZATION bit = null output,
    @CONTACTADDRESS nvarchar(300) = null output,
    @CONTACTPHONE nvarchar(100) = null output,
    @CONTACTEMAILADDRESS nvarchar(100) = null output,

    -- Relationship fields

    @CONTACTRELATIONSHIPID uniqueidentifier = null output,
    @RELATIONSHIPTYPECODEID uniqueidentifier = null output,
    @RECIPROCALTYPECODEID uniqueidentifier = null output,
    @RELATIONSHIPSTARTDATE datetime = null output,
    @RELATIONSHIPENDDATE datetime = null output,
    @ISCONTACT bit = null output,
    @ISPRIMARYCONTACT bit = null output,
    @CONTACTTYPECODEID uniqueidentifier = null output,
    @POSITION nvarchar(50) = null output,
    @ISPRIMARYBUSINESS bit = null output,
    @ISMATCHINGGIFTRELATIONSHIP bit = 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,

    @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,

    @UPDATECONTACTS bit = null output,
    @HASCONTACTS bit = null output,
    @DONOTMAILREASONCODEID uniqueidentifier = null output,
    @INFOSOURCECOMMENTS nvarchar(256) = null output,
    @COMMENTS nvarchar(max) = 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,

    @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    
)
as
    set nocount on;

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

    select
        @DATALOADED = 1,
        @TSLONG = SALESORDER.TSLONG,
        @CONTACTCONSTITUENTID = SALESORDER.RECIPIENTID,
        @CONTACTRELATIONSHIPID = SALESORDER.CONTACTRELATIONSHIPID,
        @ADDRESSID = SALESORDER.ADDRESSID,
        @PHONEID = SALESORDER.PHONEID,
        @EMAILADDRESSID = SALESORDER.EMAILADDRESSID,
        @CONSTITUENTNAME = NF.NAME,
        @CONSTITUENTID = CONSTITUENT.ID,
        @ISORGANIZATION = CONSTITUENT.ISORGANIZATION
    from dbo.SALESORDER
    inner join dbo.CONSTITUENT
        on SALESORDER.CONSTITUENTID = CONSTITUENT.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.CONSTITUENTID) as NF
    where SALESORDER.ID = @ID;

    if @ISORGANIZATION = 1 and @CONTACTRELATIONSHIPID is not null
        begin

            exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RELATIONSHIP_ORGTOIND_3
                @ID = @CONTACTRELATIONSHIPID,
                @DATALOADED = @DATALOADED output,                    
                @RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID output,
                @RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID output,
                @RELATIONSHIPSTARTDATE = @RELATIONSHIPSTARTDATE output,
                @RELATIONSHIPENDDATE = @RELATIONSHIPENDDATE output,
                @ISCONTACT = @ISCONTACT output,
                @ISPRIMARYCONTACT = @ISPRIMARYCONTACT output,
                @CONTACTTYPECODEID = @CONTACTTYPECODEID output,
                @POSITION = @POSITION output,
                @ISPRIMARYBUSINESS = @ISPRIMARYBUSINESS output,
                @ISMATCHINGGIFTRELATIONSHIP = @ISMATCHINGGIFTRELATIONSHIP output,
                @RELATIONADDRESSID = @ADDRESSID output,
                @ADDADDRESS = @ADDADDRESS output,
                @EDITADDRESS = @EDITADDRESS output,
                @ADDRESSTYPECODEID = @ADDRESSTYPECODEID output,
                @PRIMARY = @PRIMARY output,
                @DONOTMAIL = @DONOTMAIL output,
                @STARTDATE = @STARTDATE output,
                @ENDDATE = @ENDDATE output,
                @COUNTRYID = @COUNTRYID output,
                @STATEID = @STATEID output,
                @ADDRESSBLOCK = @ADDRESSBLOCK output,
                @CITY = @CITY output,
                @POSTCODE = @POSTCODE output,
                @CART = @CART output,
                @DPC = @DPC output,
                @LOT = @LOT output,
                @SPOUSEHASMATCHINGADDRESSES = @SPOUSEHASMATCHINGADDRESSES output,
                @UPDATEMATCHINGSPOUSEADDRESSES = @UPDATEMATCHINGSPOUSEADDRESSES output,
                @OMITFROMVALIDATION = @OMITFROMVALIDATION output,
                @COUNTYCODEID = @COUNTYCODEID output,
                @CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID output,
                @STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID output,
                @STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID output,
                @LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID output,
                @INFOSOURCECODEID = @INFOSOURCECODEID output,
                @REGIONCODEID = @REGIONCODEID output,
                @LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE output,
                @VALIDATIONMESSAGE = @VALIDATIONMESSAGE output,
                @CERTIFICATIONDATA = @CERTIFICATIONDATA output,
                @NCOALASTSUBMITDATE = @NCOALASTSUBMITDATE output,
                @NCOARETURN = @NCOARETURN output,
                @NCOAFOOTNOTE = @NCOAFOOTNOTE output,
                @NCOADPVFOOTNOTE = @NCOADPVFOOTNOTE output,
                @NCOAMOVEDATE = @NCOAMOVEDATE output,
                @NCOADMASUPPRESSION = @NCOADMASUPPRESSION output,
                @NCOAMAILGRADE = @NCOAMAILGRADE output,
                @UPDATECONTACTS = @UPDATECONTACTS output,
                @HASCONTACTS = @HASCONTACTS output,
                @DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID output,
                @INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS output,
                @COMMENTS = @COMMENTS output,
                @RELATIONPHONEID = @PHONEID output,
                @ADDPHONE = @ADDPHONE output,
                @EDITPHONE = @EDITPHONE output,
                @PHONETYPECODEID = @PHONETYPECODEID output,
                @NUMBER = @NUMBER output,
                @PHONECOUNTRYID = @PHONECOUNTRYID output,
                @PHONESTARTTIME = @PHONESTARTTIME output,
                @PHONEENDTIME = @PHONEENDTIME output,
                @PHONESTARTDATE = @PHONESTARTDATE output,
                @PHONEENDDATE = @PHONEENDDATE output,
                @PHONEPRIMARY = @PHONEPRIMARY output,
                @DONOTCALL = @DONOTCALL output,
                @DONOTCALLREASONCODEID = @DONOTCALLREASONCODEID output,
                @PHONEISCONFIDENTIAL = @PHONEISCONFIDENTIAL output,
                @PHONEINFOSOURCECODEID = @PHONEINFOSOURCECODEID output,
                @PHONEINFOSOURCECOMMENTS = @PHONEINFOSOURCECOMMENTS output,
                @RELATIONEMAILADDRESSID = @EMAILADDRESSID output,
                @ADDEMAILADDRESS = @ADDEMAILADDRESS output,
                @EDITEMAILADDRESS = @EDITEMAILADDRESS output,
                @EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID output,
                @EMAILADDRESS = @EMAILADDRESS output,
                @EMAILADDRESSPRIMARY = @EMAILADDRESSPRIMARY output,
                @DONOTEMAIL = @DONOTEMAIL output,
                @EMAILADDRESSINFOSOURCECODEID = @EMAILADDRESSINFOSOURCECODEID output,
                @EMAILADDRESSINFOSOURCECOMMENTS = @EMAILADDRESSINFOSOURCECOMMENTS output,
                @RELATIONSHIPADDRESSHASSMAILPREFRENCE = @RELATIONSHIPADDRESSHASSMAILPREFRENCE output,
                @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @PRIMARYSOFTCREDITRELATIONSHIPEXISTS output,
                @PRIMARYSOFTCREDITMATCHFACTOR = @PRIMARYSOFTCREDITMATCHFACTOR output,
                @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS output,
                @RECIPROCALSOFTCREDITMATCHFACTOR = @RECIPROCALSOFTCREDITMATCHFACTOR output,
                @PRIMARYRECOGNITIONTYPECODEID = @PRIMARYRECOGNITIONTYPECODEID output,
                @RECIPROCALRECOGNITIONTYPECODEID = @RECIPROCALRECOGNITIONTYPECODEID output,
                @PRIMARYISCONSTITUENT = @PRIMARYISCONSTITUENT output;

            if @ADDRESSID is not null
                set @CONTACTADDRESS = dbo.UFN_BUILDFULLADDRESS(@ADDRESSID, @ADDRESSBLOCK, @CITY, @STATEID, @POSTCODE, @COUNTRYID);

            if @PHONEID is not null
                set @CONTACTPHONE = @NUMBER;

            if @EMAILADDRESSID is not null
                set @CONTACTEMAILADDRESS = @EMAILADDRESS;

        end

    return 0;