USP_DATAFORMTEMPLATE_ADD_PRELOAD_BATCHREVENUEMEMBERSHIPAPPEALDEFAULT
The load procedure used by the view dataform template "Batch Revenue Membership Appeal Default View Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | nvarchar(100) | 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. |
| @MEMBERSHIP | nvarchar(203) | INOUT | Membership |
| @PROGRAM | nvarchar(100) | INOUT | Program |
| @TERM | nvarchar(8) | INOUT | Term |
| @EXPIRATIONDATE | datetime | INOUT | Expiration date |
| @AMOUNT | money | INOUT | Amount |
| @MEMBERSHIPPROGRAMID | uniqueidentifier | INOUT | Program |
| @MEMBERSHIPLEVELID | uniqueidentifier | INOUT | Level |
| @MEMBERSHIPLEVELTERMID | uniqueidentifier | INOUT | Term |
| @MEMBERSHIPTYPECODEID | uniqueidentifier | INOUT | Type |
| @NUMBEROFCHILDREN | tinyint | INOUT | No. of children |
| @COMMENTS | nvarchar(1000) | INOUT | Comments |
| @MEMBERS | xml | INOUT | Members |
| @ISGIFT | bit | INOUT | This membership is a gift |
| @SENDRENEWALCODE | tinyint | INOUT | Send renewal notice to |
| @GIVENBYID | uniqueidentifier | INOUT | Given By |
| @MEMBERSHIPID | uniqueidentifier | INOUT | ID |
| @TERMSLIST | xml | INOUT | TERMSLIST |
| @FORCEMANUALDOWNGRADE | bit | INOUT | FORCEMANUALDOWNGRADE |
| @EXPIRESONCODE | tinyint | INOUT | EXPIRESONCODE |
| @CUTOFFDAY | tinyint | INOUT | CUTOFFDAY |
| @CUTOFFDATEFORYEAR | nvarchar(4) | INOUT | CUTOFFDATEFORYEAR |
| @EXPIRATIONDATES | xml | INOUT | EXPIRATIONDATES |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PRELOAD_BATCHREVENUEMEMBERSHIPAPPEALDEFAULT
(
@ID nvarchar(100),
@DATALOADED bit = 0 output,
@MEMBERSHIP nvarchar(203) = null output,
@PROGRAM nvarchar(100) = null output,
@TERM nvarchar(8) = null output,
@EXPIRATIONDATE datetime = null output,
@AMOUNT money = null output,
@MEMBERSHIPPROGRAMID uniqueidentifier = null output,
@MEMBERSHIPLEVELID uniqueidentifier = null output,
@MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
@MEMBERSHIPTYPECODEID uniqueidentifier = null output,
@NUMBEROFCHILDREN tinyint = null output,
@COMMENTS nvarchar(1000) = null output,
@MEMBERS xml = null output,
@ISGIFT bit = null output,
@SENDRENEWALCODE tinyint = null output,
@GIVENBYID uniqueidentifier = null output,
@MEMBERSHIPID uniqueidentifier = null output,
@TERMSLIST xml = null output,
@FORCEMANUALDOWNGRADE bit = null output,
@EXPIRESONCODE tinyint = null output,
@CUTOFFDAY tinyint = null output,
@CUTOFFDATEFORYEAR nvarchar(4) = null output,
@EXPIRATIONDATES xml = null output
)
as
set nocount on;
declare @DEFAULTTERMID uniqueidentifier
declare @TRANSACTIONDATE datetime
declare @CONSTITUENTID uniqueidentifier
set @DEFAULTTERMID = convert(uniqueidentifier, SUBSTRING(@ID, 1, 36))
set @CONSTITUENTID = convert(uniqueidentifier, SUBSTRING(@ID, 38, 36))
set @ID = REPLACE(@ID, LEFT(@ID, 74), '')
set @TRANSACTIONDATE = convert(datetime, @ID)
set @DATALOADED = 0;
select @MEMBERSHIPLEVELID = ML.ID,
@MEMBERSHIPPROGRAMID = MP.ID,
@MEMBERSHIPLEVELTERMID = @DEFAULTTERMID,
@FORCEMANUALDOWNGRADE = ML.FORCEMANUALDOWNGRADES,
@EXPIRESONCODE = MP.EXPIRESONCODE,
@CUTOFFDAY = MP.CUTOFFDAY,
@CUTOFFDATEFORYEAR = MP.CUTOFFDATEFORYEAR
from dbo.MEMBERSHIPLEVELTERM MLT
inner join dbo.MEMBERSHIPLEVEL ML on MLT.LEVELID = ML.ID
inner join dbo.MEMBERSHIPPROGRAM MP on ML.MEMBERSHIPPROGRAMID = MP.ID
where MLT.ID = @DEFAULTTERMID;
select @MEMBERSHIPID = MEMBERSHIP.ID
from dbo.MEMBERSHIP
inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
where MEMBER.CONSTITUENTID = @CONSTITUENTID and MEMBER.ISPRIMARY = 1
and MEMBER.ISDROPPED = 0
and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID;
if @MEMBERSHIPID is null
begin
--get default membership object
select
@DATALOADED = 1,
@MEMBERSHIPID = newid(),
@MEMBERSHIP = MP.NAME + ' - ' + ML.NAME,
@PROGRAM = MP.NAME,
@TERM = MLT.TERM,
@EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @TRANSACTIONDATE),
@AMOUNT = MLT.AMOUNT,
@NUMBEROFCHILDREN = 0,
@ISGIFT = 0,
@SENDRENEWALCODE = 1,
@COMMENTS = ''
from dbo.MEMBERSHIPLEVELTERM MLT
inner join dbo.MEMBERSHIPLEVEL ML on MLT.LEVELID = ML.ID
inner join dbo.MEMBERSHIPPROGRAM MP on ML.MEMBERSHIPPROGRAMID = MP.ID
where MLT.ID = @DEFAULTTERMID;
set @MEMBERS =
(
select
newid() as ID,
@CONSTITUENTID as CONSTITUENTID,
1 as ISPRIMARY
from dbo.MEMBERSHIPPROGRAM M
where ID = @MEMBERSHIPPROGRAMID
for xml raw ('ITEM'), type, elements, root('MEMBERS'),BINARY BASE64
)
end
else
begin
--load membership
select
@DATALOADED = 1,
@MEMBERSHIP = MP.NAME + ' - ' + ML.NAME,
@PROGRAM = MP.NAME,
@TERM = MLT.TERM,
@EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION(MEMBERSHIP.EXPIRATIONDATE,MEMBERSHIP.MEMBERSHIPLEVELTERMID),
@AMOUNT = MLT.AMOUNT,
@MEMBERSHIPTYPECODEID = MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
@NUMBEROFCHILDREN = MEMBERSHIP.NUMBEROFCHILDREN,
@COMMENTS = MEMBERSHIP.COMMENTS,
@ISGIFT = MEMBERSHIP.ISGIFT,
@SENDRENEWALCODE = MEMBERSHIP.SENDRENEWALCODE,
@GIVENBYID = MEMBERSHIP.GIVENBYID
from dbo.MEMBERSHIP
inner join dbo.MEMBERSHIPLEVELTERM MLT on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MLT.ID
inner join dbo.MEMBERSHIPLEVEL ML on MEMBERSHIP.MEMBERSHIPLEVELID = ML.ID
inner join dbo.MEMBERSHIPPROGRAM MP on MEMBERSHIP.MEMBERSHIPPROGRAMID = MP.ID
where MEMBERSHIP.ID = @MEMBERSHIPID;
set @MEMBERS =
(
select
M.ID,
M.CONSTITUENTID,
M.ISPRIMARY,
(
select
MC.ID,
MC.NAMEONCARD,
MC.EXPIRATIONDATE
from dbo.MEMBERSHIPCARD MC
where MC.MEMBERID = M.ID and MC.STATUSCODE <> 2
for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),BINARY BASE64
)
from dbo.MEMBER M
where MEMBERSHIPID = @MEMBERSHIPID and ISDROPPED = 0
for xml raw ('ITEM'), type, elements, root('MEMBERS'),BINARY BASE64
)
end
set @TERMSLIST =
(
select
MEMBERSHIPLEVELTERM.ID,
case when TERMCODE = 0 then 1
when TERMCODE = 1 then 2
when TERMCODE = 2 then 3
when TERMCODE = 3 then 4
when TERMCODE = 4 then 5
when TERMCODE = 5 then 10
else 0
end AS TERM,
AMOUNT,
MEMBERSHIPLEVELTERM.LEVELID
from dbo.MEMBERSHIPLEVELTERM
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
order by MEMBERSHIPLEVELTERM.AMOUNT desc, MEMBERSHIPLEVEL.SEQUENCE desc
for xml raw ('ITEM'), type, elements, root('TERMSLIST'), BINARY BASE64
)
if @EXPIRESONCODE = 4 --specific dates
begin
set @EXPIRATIONDATES =
(
select
ME.ID,
ME.EXPIRATIONDATE,
ME.PUSHNEXTDATE,
ME.SEQUENCE
from
dbo.MEMBERSHIPPROGRAMENDDATE ME
where
MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
for xml raw ('ITEM'), type, elements, root('TERMSLIST'), BINARY BASE64
)
end
return 0;