USP_DATAFORMTEMPLATE_ADD_DAILYSALEITEMMEMBERSHIPFASTTRACK_PRELOAD

The load procedure used by the edit dataform template "Daily Sale Item Membership Fast Track Add Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@SALESORDERID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@LASTNAME	nvarchar(100)	INOUT	Last name
@FIRSTNAME	nvarchar(50)	INOUT	First name
@MIDDLENAME	nvarchar(50)	INOUT	Middle name
@GENDERCODE	tinyint	INOUT	Gender
@BIRTHDATE	UDT_FUZZYDATE	INOUT	Birth date
@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_NUMBER	nvarchar(100)	INOUT	Phone number
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@SPOUSEID	uniqueidentifier	INOUT	Patron
@SPOUSE_LASTNAME	nvarchar(100)	INOUT	Last name
@SPOUSE_FIRSTNAME	nvarchar(50)	INOUT	First name
@SPOUSE_MIDDLENAME	nvarchar(50)	INOUT	Middle name
@SPOUSE_GENDERCODE	tinyint	INOUT	Gender
@SPOUSE_BIRTHDATE	UDT_FUZZYDATE	INOUT	Birth date
@SPOUSE_RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Reciprocal relationship type
@SPOUSE_RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Relationship type
@ORDERCONSTITUENTID	uniqueidentifier	INOUT
@ORDERCONSTITUENTNAME	nvarchar(154)	INOUT
@INITIALMEMBERSHIPID	uniqueidentifier	INOUT
@NUMBEROFMEMBERSALLOWED	int	INOUT
@NUMBEROFCHILDRENALLOWED	int	INOUT
@SPOUSE_PHONE_NUMBER	nvarchar(100)	INOUT	Phone number
@SPOUSE_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_DAILYSALEITEMMEMBERSHIPFASTTRACK_PRELOAD
(
    @SALESORDERID uniqueidentifier,
  -- Primary member variables
  @LASTNAME     nvarchar(100) = null output,
  @FIRSTNAME    nvarchar(50) = null output,
  @MIDDLENAME   nvarchar(50) = null output,
  @GENDERCODE   tinyint = null output,
  @BIRTHDATE    dbo.UDT_FUZZYDATE = null output,

  -- Primary address variables
  @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_NUMBER               nvarchar(100) = null output,
  @EMAILADDRESS_EMAILADDRESS  dbo.UDT_EMAILADDRESS = null output,                        

  -- Spouse variables
    @SPOUSEID                                        uniqueidentifier = null output,
  @SPOUSE_LASTNAME                nvarchar(100) = null output,
  @SPOUSE_FIRSTNAME               nvarchar(50) = null output,
  @SPOUSE_MIDDLENAME              nvarchar(50) = null output,
  @SPOUSE_GENDERCODE              tinyint = null output,
  @SPOUSE_BIRTHDATE               dbo.UDT_FUZZYDATE = null output,
    @SPOUSE_RECIPROCALTYPECODEID    uniqueidentifier = null output,
    @SPOUSE_RELATIONSHIPTYPECODEID  uniqueidentifier = null output,

  @ORDERCONSTITUENTID uniqueidentifier = null output,
  @ORDERCONSTITUENTNAME nvarchar(154) = null output,
  @INITIALMEMBERSHIPID uniqueidentifier = null output,
    @NUMBEROFMEMBERSALLOWED int = null output,
    @NUMBEROFCHILDRENALLOWED int = null output,

    @SPOUSE_PHONE_NUMBER nvarchar(100) = null output,
    @SPOUSE_EMAILADDRESS  dbo.UDT_EMAILADDRESS = null output
)
as
    set nocount on;

    select
        @ORDERCONSTITUENTID = CONSTITUENT.ID,
        @ORDERCONSTITUENTNAME = CONSTITUENT.NAME,

        @LASTNAME = CONSTITUENT.KEYNAME,
        @FIRSTNAME = CONSTITUENT.FIRSTNAME,
        @MIDDLENAME = CONSTITUENT.MIDDLENAME,
        @BIRTHDATE = CONSTITUENT.BIRTHDATE,
        @GENDERCODE = CONSTITUENT.GENDERCODE,
        @ADDRESS_ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
        @ADDRESS_CITY = ADDRESS.CITY,
        @ADDRESS_STATEID = ADDRESS.STATEID,
        @ADDRESS_COUNTRYID = ADDRESS.COUNTRYID,
        @ADDRESS_POSTCODE = ADDRESS.POSTCODE,
        @EMAILADDRESS_EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
        @PHONE_NUMBER = PHONE.NUMBER,
    @SPOUSEID = SPOUSE.ID,
        @SPOUSE_LASTNAME = SPOUSE.KEYNAME,
        @SPOUSE_FIRSTNAME = SPOUSE.FIRSTNAME,
        @SPOUSE_MIDDLENAME = SPOUSE.MIDDLENAME,
        @SPOUSE_BIRTHDATE = SPOUSE.BIRTHDATE,
        @SPOUSE_GENDERCODE = SPOUSE.GENDERCODE,
        @SPOUSE_EMAILADDRESS = SPOUSEEMAILADDRESS.EMAILADDRESS,
        @SPOUSE_PHONE_NUMBER = SPOUSEPHONE.NUMBER,
      @SPOUSE_RECIPROCALTYPECODEID  = RELATIONSHIP.RECIPROCALTYPECODEID,
      @SPOUSE_RELATIONSHIPTYPECODEID = RELATIONSHIP.RELATIONSHIPTYPECODEID

    from
        dbo.SALESORDER
    inner join
        dbo.CONSTITUENT on SALESORDER.CONSTITUENTID = CONSTITUENT.ID
    left join
        dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
    left join
        dbo.EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID and EMAILADDRESS.ISPRIMARY = 1
    left join
        dbo.PHONE on CONSTITUENT.ID = PHONE.CONSTITUENTID and PHONE.ISPRIMARY = 1
    left join 
        (dbo.RELATIONSHIP inner join dbo.CONSTITUENT as SPOUSE on RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSE.ID and ISSPOUSE = 1) on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID 
    left join
        dbo.EMAILADDRESS as SPOUSEEMAILADDRESS on SPOUSE.ID = SPOUSEEMAILADDRESS.CONSTITUENTID and SPOUSEEMAILADDRESS.ISPRIMARY = 1
    left join
        dbo.PHONE as SPOUSEPHONE on SPOUSE.ID = SPOUSEPHONE.CONSTITUENTID and SPOUSEPHONE.ISPRIMARY = 1
    where
        SALESORDER.ID = @SALESORDERID;

    if @INITIALMEMBERSHIPID is null
        set @INITIALMEMBERSHIPID = '00000000-0000-0000-0000-000000000000';

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

  return 0;