USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMMEMBERSHIP_PRELOAD
The load procedure used by the edit dataform template "Sales Order Item Membership Add Data Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SALESORDERID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @ORDERCONSTITUENTID | uniqueidentifier | INOUT | |
| @ORDERCONSTITUENTNAME | nvarchar(154) | INOUT | |
| @MEMBERSHIPS | xml | INOUT | |
| @TRANSACTIONDATE | datetime | INOUT | Transaction date |
| @INITIALMEMBERSHIPID | uniqueidentifier | INOUT | |
| @GIFTMEMBERSHIPRECIPIENTID | uniqueidentifier | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMMEMBERSHIP_PRELOAD
(
@SALESORDERID uniqueidentifier,
@ORDERCONSTITUENTID uniqueidentifier = null output,
@ORDERCONSTITUENTNAME nvarchar(154) = null output,
@MEMBERSHIPS xml = null output,
@TRANSACTIONDATE datetime = null output,
@INITIALMEMBERSHIPID uniqueidentifier = null output,
@GIFTMEMBERSHIPRECIPIENTID uniqueidentifier = null output
)
as
set nocount on;
declare @MEMBERSHIPPROGRAMID uniqueidentifier;
set @TRANSACTIONDATE = getdate();
if @ORDERCONSTITUENTID is null
select
@ORDERCONSTITUENTID = CONSTITUENT.ID,
@ORDERCONSTITUENTNAME = CONSTITUENT.NAME
from
dbo.SALESORDER
inner join
dbo.CONSTITUENT on SALESORDER.CONSTITUENTID = CONSTITUENT.ID
where
SALESORDER.ID = @SALESORDERID;
else
select @ORDERCONSTITUENTNAME = CONSTITUENT.NAME
from dbo.CONSTITUENT
where CONSTITUENT.ID = @ORDERCONSTITUENTID;
if (select count(ID) from dbo.MEMBERSHIPPROGRAM) = 1
select @MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAMID from dbo.MEMBERSHIP;
set @MEMBERSHIPS = (
select
@ORDERCONSTITUENTID [ORDERCONSTITUENTID],
*,
case MEMBERSHIP.ID
when '00000000-0000-0000-0000-000000000000' then
case
when @ORDERCONSTITUENTID is not null then
(
select
newid() [ID],
null [MEMBERID],
@ORDERCONSTITUENTID [CONSTITUENTID],
1 [ISPRIMARY],
(
select
newid() [ID],
null [MEMBERSHIPCARDID],
@ORDERCONSTITUENTNAME [NAMEONCARD],
null [EXPIRATIONDATE]
for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),binary base64
),
0 [ISCAREGIVER]
for xml raw('ITEM'),type,elements,root('MEMBERS'),binary base64
)
else
null
end
else
(
select
newid() [ID],
MEMBER.ID [MEMBERID],
MEMBER.CONSTITUENTID,
MEMBER.ISPRIMARY,
case when exists(select * from dbo.MEMBERSHIPCARD
where MEMBER.ID = MEMBERSHIPCARD.MEMBERID
and MEMBERSHIPCARD.STATUSCODE <> 2)
then
(
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
)
else
(
select
newid() [ID],
null [MEMBERSHIPCARDID],
case
when MEMBER.CONSTITUENTID = @ORDERCONSTITUENTID then @ORDERCONSTITUENTNAME
else (select top 1 name from dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID))
end as [NAMEONCARD],
null [EXPIRATIONDATE]
for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),binary base64
)
end,
MEMBER.ISCAREGIVER
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,
0 [ISGIFT],
MEMBERSHIP.SENDRENEWALCODE,
null [GIVENBYID],
MEMBERSHIP.STATUSCODE,
MEMBERSHIP.NUMBEROFADDONADULTS,
MEMBERSHIP.NUMBEROFADDONGUESTS
from
dbo.MEMBERSHIP
inner join
dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
where
MEMBER.CONSTITUENTID = @ORDERCONSTITUENTID
and MEMBER.ISDROPPED = 0
union all
select
MEMBERSHIP.ID,
dbo.UFN_CONSTITUENT_BUILDNAME(MEMBER.CONSTITUENTID) + ': ' + dbo.UFN_MEMBERSHIP_GETDESCRIPTION(MEMBERSHIP.ID) [DESCRIPTION],
MEMBERSHIP.MEMBERSHIPPROGRAMID,
MEMBERSHIP.MEMBERSHIPLEVELID,
MEMBERSHIP.MEMBERSHIPLEVELTERMID,
MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
MEMBERSHIP.EXPIRATIONDATE,
MEMBERSHIP.NUMBEROFCHILDREN,
MEMBERSHIP.COMMENTS,
case
when @ORDERCONSTITUENTID is null then
0
else
1
end [ISGIFT],
MEMBERSHIP.SENDRENEWALCODE,
@ORDERCONSTITUENTID [GIVENBY],
MEMBERSHIP.STATUSCODE,
MEMBERSHIP.NUMBEROFADDONADULTS,
MEMBERSHIP.NUMBEROFADDONGUESTS
from
dbo.MEMBERSHIP
inner join
dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID and MEMBER.ISPRIMARY = 1
where
MEMBERSHIP.GIVENBYID = @ORDERCONSTITUENTID
and @ORDERCONSTITUENTID not in (select CONSTITUENTID from dbo.MEMBER where MEMBERSHIPID = MEMBERSHIP.ID)
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],
0 [NUMBEROFADDONADULTS],
0 [NUMBEROFADDONGUESTS]
) [MEMBERSHIP]
for xml raw('ITEM'),type,elements,root('MEMBERSHIPS'),binary base64
);
select top 1
@INITIALMEMBERSHIPID = MEMBERSHIPID
from
dbo.MEMBER
where
CONSTITUENTID = @ORDERCONSTITUENTID
and ISDROPPED = 0;
if @INITIALMEMBERSHIPID is null
set @INITIALMEMBERSHIPID = '00000000-0000-0000-0000-000000000000';
return 0;