USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPPROGRAMRENEWAL

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@MEMBERSHIPTERMTYPE	tinyint	INOUT
@DUESBASEDEXPIRATIONDATETYPE	tinyint	INOUT
@EXPIRATIONENDOFPRIORMONTH	bit	INOUT
@EXPIRATIONENDOFPRIORMONTHCUTOFF	tinyint	INOUT
@RENEWALWINDOWSTART	tinyint	INOUT
@RENEWALWINDOWEND	tinyint	INOUT
@AUTOMATICRENEWALRADIO	tinyint	INOUT
@AUTOMATICRENEWALTIMETYPE	tinyint	INOUT
@RENEWALWINDOWSTARTCUTOFFDAY	tinyint	INOUT
@AFTEREXPIRATIONTIMEPERIOD1	tinyint	INOUT
@AFTEREXPIRATIONCLASSIFICATION1	tinyint	INOUT
@AFTEREXPIRATIONTIMEPERIOD2	tinyint	INOUT
@AFTEREXPIRATIONCLASSIFICATION2	tinyint	INOUT
@AFTEREXPIRATIONTIMEPERIOD3	tinyint	INOUT
@AFTEREXPIRATIONCLASSIFICATION3	tinyint	INOUT
@CLASSIFICATIONAFTERRENEWALWINDOWTYPE	tinyint	INOUT
@REVENUEAFTERRENEWALWINDOWTYPE	tinyint	INOUT
@DUESBASEDEXPIRATIONDATE	UDT_MONTHDAY	INOUT
@AUTOMATICRENEWALNUMBERDAY	tinyint	INOUT
@AUTOMATICRENEWALNUMBERMONTH	tinyint	INOUT
@AUTOMATICRENEWALDAYOFMONTH	int	INOUT
@PROGRAMBASEDONCODE	tinyint	INOUT
@EXPIRATIONDATES	xml	INOUT
@ELIGIBLEFORSPECIFICDATES	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPPROGRAMRENEWAL(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @MEMBERSHIPTERMTYPE tinyint = null output,
    @DUESBASEDEXPIRATIONDATETYPE tinyint = null output,
    @EXPIRATIONENDOFPRIORMONTH bit = null output,
    @EXPIRATIONENDOFPRIORMONTHCUTOFF tinyint = null output,
    @RENEWALWINDOWSTART tinyint = null output,
    @RENEWALWINDOWEND tinyint = null output,
    @AUTOMATICRENEWALRADIO tinyint = null output,
    @AUTOMATICRENEWALTIMETYPE tinyint = null output,
    @RENEWALWINDOWSTARTCUTOFFDAY tinyint = null output,
    @AFTEREXPIRATIONTIMEPERIOD1 tinyint = null output,
    @AFTEREXPIRATIONCLASSIFICATION1 tinyint = null output,
    @AFTEREXPIRATIONTIMEPERIOD2 tinyint = null output,
    @AFTEREXPIRATIONCLASSIFICATION2 tinyint = null output,
    @AFTEREXPIRATIONTIMEPERIOD3 tinyint = null output,
    @AFTEREXPIRATIONCLASSIFICATION3 tinyint = null output,
    @CLASSIFICATIONAFTERRENEWALWINDOWTYPE tinyint = null output,
    @REVENUEAFTERRENEWALWINDOWTYPE tinyint = null output,
    @DUESBASEDEXPIRATIONDATE dbo.UDT_MONTHDAY = null output,
    @AUTOMATICRENEWALNUMBERDAY tinyint = null output,
    @AUTOMATICRENEWALNUMBERMONTH tinyint = null output,
    @AUTOMATICRENEWALDAYOFMONTH int = null output,
    @PROGRAMBASEDONCODE tinyint = null output,
    @EXPIRATIONDATES xml = null output,
    @ELIGIBLEFORSPECIFICDATES bit = null output
)
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0
    set @TSLONG = 0

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.
    set @EXPIRATIONDATES = dbo.UFN_MEMBERSHIPPROGRAM_GETENDDATE_TOITEMLISTXML(@ID);
  SELECT
    @DATALOADED = 1,
    @TSLONG = MP.TSLONG,
      @MEMBERSHIPTERMTYPE = ML.MEMBERSHIPTERMTYPECODE,
      @DUESBASEDEXPIRATIONDATETYPE = MP.EXPIRESONCODE,
    @EXPIRATIONENDOFPRIORMONTH = MP.BACKDATEMEMBERSHIPS,
    @EXPIRATIONENDOFPRIORMONTHCUTOFF = MP.CUTOFFDAY,
      @RENEWALWINDOWSTART = ML.BEFOREEXPIRATION,
      @RENEWALWINDOWEND = ML.AFTEREXPIRATION,
    @AUTOMATICRENEWALRADIO = ML.RENEWALWINDOWSTARTTYPECODE,
    @AUTOMATICRENEWALTIMETYPE = ML.RENEWALWINDOWSTARTINTERVALCODE,
    @RENEWALWINDOWSTARTCUTOFFDAY = ML.RENEWALWINDOWSTARTCUTOFFDAY,
      @AFTEREXPIRATIONTIMEPERIOD1 = MLR1.INTERVALCODE,
      @AFTEREXPIRATIONCLASSIFICATION1 = MLR1.STATUSCODE,
      @AFTEREXPIRATIONTIMEPERIOD2 = MLR2.INTERVALCODE,
      @AFTEREXPIRATIONCLASSIFICATION2 = MLR2.STATUSCODE,
      @AFTEREXPIRATIONTIMEPERIOD3 = MLR3.INTERVALCODE,
      @AFTEREXPIRATIONCLASSIFICATION3 = MLR3.STATUSCODE,
      @CLASSIFICATIONAFTERRENEWALWINDOWTYPE = ML.NONRENEWALACTIONTYPECODE,
      @REVENUEAFTERRENEWALWINDOWTYPE = ML.RENEWALWINDOWREVENUETYPECODE,
    @AUTOMATICRENEWALNUMBERDAY = case when ML.RENEWALWINDOWSTARTINTERVALCODE = 0 then ML.RENEWALWINDOWSTARTTIMECODE else null end,
    @AUTOMATICRENEWALNUMBERMONTH = case when ML.RENEWALWINDOWSTARTINTERVALCODE = 0 then null else ML.RENEWALWINDOWSTARTTIMECODE end,
    @AUTOMATICRENEWALDAYOFMONTH = ML.RENEWALWINDOWSTARTTIMECODE,
    @PROGRAMBASEDONCODE = MP.PROGRAMBASEDONCODE
  FROM dbo.MEMBERSHIPPROGRAM MP
  INNER JOIN MEMBERSHIPLEVEL ML ON ML.MEMBERSHIPPROGRAMID = MP.ID
  LEFT JOIN MEMBERSHIPLEVELRENEWAL MLR1 ON ML.MEMBERSHIPLEVELRENEWAL1ID = MLR1.ID
  LEFT JOIN MEMBERSHIPLEVELRENEWAL MLR2 ON ML.MEMBERSHIPLEVELRENEWAL2ID = MLR2.ID
  LEFT JOIN MEMBERSHIPLEVELRENEWAL MLR3 ON ML.MEMBERSHIPLEVELRENEWAL3ID = MLR3.ID
    WHERE MP.ID = @ID
    -- This program cannot use specific date expiration with monthly terms
    if exists
    (
        select 1
        from dbo.MEMBERSHIPLEVELTERM inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
        where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID and TERMLENGTHCODE <> 1 -- Years
    )
    begin
        set @ELIGIBLEFORSPECIFICDATES = 0
    end
    else
    begin
        set @ELIGIBLEFORSPECIFICDATES = 1
    end

    return 0;