USP_DATAFORMTEMPLATE_EDITLOAD_ORDERPATRONCONTACTINFO

The load procedure used by the edit dataform template "Order Patron Contact Info Edit Data Form"

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.
@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.
@ADDRESS_ID	uniqueidentifier	INOUT	Address ID
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	INOUT	Address type
@ADDRESS_COUNTRYID	uniqueidentifier	INOUT	Country
@ADDRESS_STATEID	uniqueidentifier	INOUT	State
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@ADDRESS_CITY	nvarchar(50)	INOUT	City
@ADDRESS_POSTCODE	nvarchar(12)	INOUT	ZIP
@PHONE_ID	uniqueidentifier	INOUT	Phone ID
@PHONE_PHONETYPECODEID	uniqueidentifier	INOUT	Phone type
@PHONE_NUMBER	nvarchar(100)	INOUT	Phone number
@EMAILADDRESS_ID	uniqueidentifier	INOUT	Email address ID
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT	Email type
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent ID
@ISHOUSEHOLDMEMBER	bit	INOUT
@UPDATEMATCHINGHOUSEHOLDADDRESSES	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_ORDERPATRONCONTACTINFO
(
    @ID uniqueidentifier, --Order ID

    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,

    @ADDRESS_ID uniqueidentifier = null output,
    @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null output,
    @ADDRESS_COUNTRYID uniqueidentifier = null output,
    @ADDRESS_STATEID uniqueidentifier = null output,
    @ADDRESS_ADDRESSBLOCK nvarchar(150) = null output,
    @ADDRESS_CITY nvarchar(50) = null output,
    @ADDRESS_POSTCODE nvarchar(12) = null output,

    @PHONE_ID uniqueidentifier = null output,
    @PHONE_PHONETYPECODEID uniqueidentifier = null output,
    @PHONE_NUMBER nvarchar(100) = null output,

    @EMAILADDRESS_ID uniqueidentifier = null output,
    @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null output,
    @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,

    @CONSTITUENTID uniqueidentifier = null output,
    @ISHOUSEHOLDMEMBER bit  = null output,
    @UPDATEMATCHINGHOUSEHOLDADDRESSES bit  = null output
) as
    set nocount on;

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

    declare @ADDRESS_TSLONG bigint;
    set @ADDRESS_TSLONG = 0;

    declare @PHONE_TSLONG bigint;
    set @PHONE_TSLONG = 0;

    declare @EMAILADDRESS_TSLONG bigint;
    set @EMAILADDRESS_TSLONG = 0;

    select @CONSTITUENTID = [CONSTITUENTID] from dbo.[SALESORDER] where [ID] = @ID;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    select
        @DATALOADED = 1,
        @TSLONG = CONSTITUENT.TSLONG,

        @ADDRESS_ID = ADDRESS.ID,
        @ADDRESS_TSLONG = ADDRESS.TSLONG,
        @ADDRESS_ADDRESSTYPECODEID = ADDRESS.ADDRESSTYPECODEID,
        @ADDRESS_COUNTRYID = ADDRESS.COUNTRYID,
        @ADDRESS_STATEID = ADDRESS.STATEID,
        @ADDRESS_ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
        @ADDRESS_CITY = ADDRESS.CITY,
        @ADDRESS_POSTCODE = ADDRESS.POSTCODE,

        @PHONE_ID = PHONE.ID,
        @PHONE_TSLONG = PHONE.TSLONG,
        @PHONE_PHONETYPECODEID = PHONE.PHONETYPECODEID,
        @PHONE_NUMBER = PHONE.NUMBER,

        @EMAILADDRESS_ID = EMAILADDRESS.ID,
        @EMAILADDRESS_TSLONG = EMAILADDRESS.TSLONG,
        @EMAILADDRESS_EMAILADDRESSTYPECODEID = EMAILADDRESS.EMAILADDRESSTYPECODEID,
        @EMAILADDRESS_EMAILADDRESS = EMAILADDRESS.EMAILADDRESS
    from
        dbo.CONSTITUENT
    left outer join
        dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
    left outer join
        dbo.PHONE on CONSTITUENT.ID = PHONE.CONSTITUENTID and PHONE.ISPRIMARY = 1
    left outer join
        dbo.EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID and EMAILADDRESS.ISPRIMARY = 1
    where
        CONSTITUENT.ID = @CONSTITUENTID;

    if @ADDRESS_ID is null
        exec @ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

    -- set @TSLONG to the max value of the child records

    if @ADDRESS_TSLONG > @TSLONG
        set @TSLONG = @ADDRESS_TSLONG;

    if @PHONE_TSLONG > @TSLONG
        set @TSLONG = @PHONE_TSLONG;

    if @EMAILADDRESS_TSLONG > @TSLONG
        set @TSLONG = @EMAILADDRESS_TSLONG;

    set @ISHOUSEHOLDMEMBER = case when exists (
        select 1 
        from dbo.GROUPMEMBER GM 
        left outer join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
        left outer join dbo.GROUPDATA GD on GD.ID = GM.GROUPID
        where GM.MEMBERID = @CONSTITUENTID 
        and GD.GROUPTYPECODE = 0
        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
            or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
    ) then 1 else 0 end;            

    set @UPDATEMATCHINGHOUSEHOLDADDRESSES  = @ISHOUSEHOLDMEMBER;

    return 0;