USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMRENEWAL

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@MEMBERSHIPTERMTYPECODE	tinyint	INOUT
@EXPIRESONCODE	tinyint	INOUT
@BACKDATEMEMBERSHIPS	tinyint	INOUT
@CUTOFFDAY	tinyint	INOUT
@BEFOREEXPIRATION	tinyint	INOUT
@AFTEREXPIRATION	tinyint	INOUT
@RENEWALWINDOWSTARTTYPECODE	tinyint	INOUT
@RENEWALWINDOWSTARTTIMECODE	tinyint	INOUT
@RENEWALWINDOWSTARTINTERVALCODE	tinyint	INOUT
@RENEWALWINDOWSTARTCUTOFFDAY	tinyint	INOUT
@INTERVALCODE1	tinyint	INOUT
@STATUSCODE1	tinyint	INOUT
@INTERVALCODE2	tinyint	INOUT
@STATUSCODE2	tinyint	INOUT
@INTERVALCODE3	tinyint	INOUT
@STATUSCODE3	tinyint	INOUT
@NONRENEWALACTIONTYPECODE	tinyint	INOUT
@RENEWALWINDOWREVENUETYPECODE	tinyint	INOUT
@DUESBASEDEXPIRATIONDATE	UDT_MONTHDAY	INOUT
@PROGRAMBASEDONCODE	tinyint	INOUT
@EXPIRATIONDATES	xml	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMRENEWAL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @MEMBERSHIPTERMTYPECODE tinyint = null output,
    @EXPIRESONCODE tinyint = null output,
  @BACKDATEMEMBERSHIPS tinyint = null output,
  @CUTOFFDAY tinyint = null output,
    @BEFOREEXPIRATION tinyint = null output,
    @AFTEREXPIRATION tinyint = null output,
    @RENEWALWINDOWSTARTTYPECODE tinyint = null output,
    @RENEWALWINDOWSTARTTIMECODE tinyint = null output,
    @RENEWALWINDOWSTARTINTERVALCODE tinyint = null output,
    @RENEWALWINDOWSTARTCUTOFFDAY tinyint = null output,
    @INTERVALCODE1 tinyint = null output,
    @STATUSCODE1 tinyint = null output,
    @INTERVALCODE2 tinyint = null output,
    @STATUSCODE2 tinyint = null output,
    @INTERVALCODE3 tinyint = null output,
    @STATUSCODE3 tinyint = null output,
    @NONRENEWALACTIONTYPECODE tinyint = null output,
    @RENEWALWINDOWREVENUETYPECODE tinyint = null output,
  @DUESBASEDEXPIRATIONDATE dbo.UDT_MONTHDAY = null output,
  @PROGRAMBASEDONCODE tinyint = null output,
  @EXPIRATIONDATES xml = null output
)
as
    set nocount on;

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

    set @EXPIRATIONDATES = dbo.UFN_MEMBERSHIPPROGRAM_GETENDDATE_TOITEMLISTXML(@ID);

    -- 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.
  SELECT
    @DATALOADED = 1,
      @MEMBERSHIPTERMTYPECODE = ML.MEMBERSHIPTERMTYPECODE,
      @EXPIRESONCODE = MP.EXPIRESONCODE,
    @BACKDATEMEMBERSHIPS = MP.BACKDATEMEMBERSHIPS,
    @CUTOFFDAY = MP.CUTOFFDAY,
      @BEFOREEXPIRATION = ML.BEFOREEXPIRATION,
      @AFTEREXPIRATION = ML.AFTEREXPIRATION,
    @RENEWALWINDOWSTARTTYPECODE = ML.RENEWALWINDOWSTARTTYPECODE,
    @RENEWALWINDOWSTARTTIMECODE = ML.RENEWALWINDOWSTARTTIMECODE,
    @RENEWALWINDOWSTARTINTERVALCODE = ML.RENEWALWINDOWSTARTINTERVALCODE,
    @RENEWALWINDOWSTARTCUTOFFDAY = ML.RENEWALWINDOWSTARTCUTOFFDAY,
      @INTERVALCODE1 = MLR1.INTERVALCODE,
      @STATUSCODE1 = MLR1.STATUSCODE,
      @INTERVALCODE2 = MLR2.INTERVALCODE,
      @STATUSCODE2 = MLR2.STATUSCODE,
      @INTERVALCODE3 = MLR3.INTERVALCODE,
      @STATUSCODE3 = MLR3.STATUSCODE,
      @NONRENEWALACTIONTYPECODE = ML.NONRENEWALACTIONTYPECODE,
      @RENEWALWINDOWREVENUETYPECODE = ML.RENEWALWINDOWREVENUETYPECODE,
    @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

    return 0;