USP_DATAFORMTEMPLATE_ADDLOAD_MEMBERSHIPTRANSFER

The load procedure used by the edit dataform template "Membership Transfer Add Form"

Parameters

Parameter	Parameter Type	Mode	Description
@OLDPRIMARYMEMBERID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@OLDMEMBERNAME	nvarchar(700)	INOUT	Old member name
@EXPIRATIONDATE	datetime	INOUT	Card expiration date
@NUMCARDS	smallint	INOUT	Number of cards
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADDLOAD_MEMBERSHIPTRANSFER
(
    @OLDPRIMARYMEMBERID uniqueidentifier,
    @OLDMEMBERNAME nvarchar(700) = null output,
    @EXPIRATIONDATE datetime = null output,
    @NUMCARDS smallint = null output,
    @MEMBERSHIPPROGRAMID uniqueidentifier = null output
)
as
    set nocount on;

    declare @DATALOADED bit;
    set @DATALOADED = 0;

    declare @ISPRIMARY bit;
    set @ISPRIMARY = 0;

    select top(1)
        @DATALOADED = 1,
        @OLDMEMBERNAME = NF.NAME,
        @EXPIRATIONDATE = MEMBERSHIPCARD.EXPIRATIONDATE,
        @ISPRIMARY = MEMBER.ISPRIMARY,
        @MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID
    from
        dbo.MEMBER
    inner join
        dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    cross apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
    left join
        dbo.MEMBERSHIPCARD on MEMBER.ID = MEMBERSHIPCARD.MEMBERID and MEMBERSHIPCARD.STATUSCODE <> 2  -- Cancelled
    where
        MEMBER.ID = @OLDPRIMARYMEMBERID
    order by
        MEMBERSHIPCARD.EXPIRATIONDATE desc;

    select
        @NUMCARDS = count(*)
    from dbo.MEMBERSHIPCARD
    where MEMBERID = @OLDPRIMARYMEMBERID
    and STATUSCODE <> 2;  -- Cancelled

    if @DATALOADED <> 1
    begin
        raiserror('Member not found.', 13, 1);
        return 1;
    end

    if @ISPRIMARY <> 1
    begin
        raiserror('Membership can only be transferred from the primary member.', 13, 1);
        return 1;
    end