USP_DATAFORMTEMPLATE_VIEW_SALESORDERMEMBERSHIPSBYCONSTITUENTID
The load procedure used by the view dataform template "Sales Order Membership By Constituent ID View Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | 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. |
| @MEMBERSHIPS | xml | INOUT | MEMBERSHIPS |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SALESORDERMEMBERSHIPSBYCONSTITUENTID
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@MEMBERSHIPS xml = null output
)
as
set nocount on;
set @DATALOADED = 0;
declare @TRANSACTIONDATE datetime;
declare @CONSTITUENTNAME nvarchar(154);
declare @MEMBERSHIPPROGRAMID uniqueidentifier;
set @TRANSACTIONDATE = getdate();
select
@DATALOADED = 1,
@CONSTITUENTNAME = NAME
from
dbo.CONSTITUENT
where
ID = @ID;
if (select count(ID) from dbo.MEMBERSHIPPROGRAM) = 1
select @MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAMID from dbo.MEMBERSHIP;
set @MEMBERSHIPS = (
select
*,
case MEMBERSHIP.ID
when '00000000-0000-0000-0000-000000000000' then
(
select
newid() [ID],
null [MEMBERID],
@ID [CONSTITUENTID],
1 [ISPRIMARY],
(
select
newid() [ID],
null [MEMBERSHIPCARDID],
@CONSTITUENTNAME [NAMEONCARD],
null [EXPIRATIONDATE]
for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),binary base64
)
for xml raw('ITEM'),type,elements,root('MEMBERS'),binary base64
)
else
(
select
newid() [ID],
MEMBER.ID [MEMBERID],
MEMBER.CONSTITUENTID,
MEMBER.ISPRIMARY,
(
select
newid() [ID],
MEMBERSHIPCARD.ID [MEMBERSHIPCARDID],
MEMBERSHIPCARD.NAMEONCARD [NAMEONCARD],
MEMBERSHIPCARD.EXPIRATIONDATE [EXPIRATIONDATE]
from
dbo.MEMBERSHIPCARD
where
MEMBER.ID = MEMBERSHIPCARD.MEMBERID
and MEMBERSHIPCARD.STATUSCODE <> 2
for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),binary base64
)
from
dbo.MEMBER
where
MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
and MEMBER.ISDROPPED = 0
for xml raw('ITEM'),type,elements,root('MEMBERS'),binary base64
)
end,
(
select
MEMBERSHIPLEVEL.ID,
dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(MEMBERSHIP.ID, MEMBERSHIPLEVEL.ID, @TRANSACTIONDATE) [ACTIONCODE]
from
dbo.MEMBERSHIPLEVEL
where
MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
for xml raw('ITEM'),type,elements,root('MEMBERSHIPLEVELS'),binary base64
)
from (
select
MEMBERSHIP.ID,
dbo.UFN_MEMBERSHIP_GETDESCRIPTION(MEMBERSHIP.ID) [DESCRIPTION],
MEMBERSHIP.MEMBERSHIPPROGRAMID,
MEMBERSHIP.MEMBERSHIPLEVELID,
MEMBERSHIP.MEMBERSHIPLEVELTERMID,
MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
MEMBERSHIP.EXPIRATIONDATE,
MEMBERSHIP.NUMBEROFCHILDREN,
MEMBERSHIP.COMMENTS,
MEMBERSHIP.ISGIFT,
MEMBERSHIP.SENDRENEWALCODE,
MEMBERSHIP.GIVENBYID,
MEMBERSHIP.STATUSCODE
from
dbo.MEMBERSHIP
inner join
dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
where
MEMBER.CONSTITUENTID = @ID
and MEMBER.ISDROPPED = 0
union all
select
'00000000-0000-0000-0000-000000000000' [ID],
'<New membership>' [DESCRIPTION],
@MEMBERSHIPPROGRAMID [MEMBERSHIPPORGRAMID],
null [MEMBERSHIPLEVELID],
null [MEMBERSHIPLEVELTERMID],
null [MEMBERSHIPLEVELTYPECODEID],
null [EXPIRATIONDATE],
0 [NUMBEROFCHILDREN],
'' [COMMENTS],
0 [ISGIFT],
1 [SENDRENEWALCODE],
null [GIVENBYID],
0 [STATUSCODE]
) [MEMBERSHIP]
for xml raw('ITEM'),type,elements,root('MEMBERSHIPS'),binary base64
);
return 0;