USP_SALESORDERITEMMEMBERSHIP_ADD2
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @SALESORDERID | uniqueidentifier | IN | |
| @MEMBERSHIPID | uniqueidentifier | IN | |
| @MEMBERSHIPPROGRAMID | uniqueidentifier | IN | |
| @MEMBERSHIPLEVELID | uniqueidentifier | IN | |
| @MEMBERSHIPLEVELTERMID | uniqueidentifier | IN | |
| @MEMBERSHIPLEVELTYPECODEID | uniqueidentifier | IN | |
| @NUMBEROFCHILDREN | tinyint | IN | |
| @COMMENTS | nvarchar(1000) | IN | |
| @ISGIFT | bit | IN | |
| @SENDRENEWALCODE | tinyint | IN | |
| @EXPIRATIONDATE | datetime | IN | |
| @MEMBERS | xml | IN | |
| @MEMBERSHIPCARDS | xml | IN | |
| @MEMBERSHIPPROGRAMADDON | xml | IN | |
| @GIVENBYID | uniqueidentifier | IN | |
| @ISEDIT | bit | IN | |
| @PRICE | money | IN | |
| @SALESORDERITEMMEMBERSHIPTYPECODE | smallint | IN | |
| @TOTALADDITIONALMEMBERS | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_SALESORDERITEMMEMBERSHIP_ADD2
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@SALESORDERID uniqueidentifier = null,
@MEMBERSHIPID uniqueidentifier = null,
@MEMBERSHIPPROGRAMID uniqueidentifier = null,
@MEMBERSHIPLEVELID uniqueidentifier = null,
@MEMBERSHIPLEVELTERMID uniqueidentifier = null,
@MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null,
@NUMBEROFCHILDREN tinyint = 0,
@COMMENTS nvarchar(1000) = null,
@ISGIFT bit = 0,
@SENDRENEWALCODE tinyint = null,
@EXPIRATIONDATE datetime = null,
@MEMBERS xml = null,
@MEMBERSHIPCARDS xml = null,
@MEMBERSHIPPROGRAMADDON xml = null,
@GIVENBYID uniqueidentifier = null,
@ISEDIT bit = 0,
@PRICE money = null,
@SALESORDERITEMMEMBERSHIPTYPECODE smallint = 1,
@TOTALADDITIONALMEMBERS tinyint = 0
)
as
set nocount on;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @CURRENTMEMBERSHIPPROGRAMID uniqueidentifier;
if @ISEDIT = 1
begin
select @SALESORDERID = SALESORDERID from dbo.SALESORDERITEM where ID = @ID;
select
@MEMBERSHIPID = MEMBERSHIPID,
@CURRENTMEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAMID
from
dbo.SALESORDERITEMMEMBERSHIP
where
ID = @ID;
end
begin try
if exists(select 1 from dbo.SALESORDER where ID = @SALESORDERID and STATUSCODE not in (0, 6, 7))
raiserror('ERR_ORDERNOTPENDING', 13, 1);
-- Number of members validation
if @MEMBERS is null
raiserror('ERR_ATLEASTONEMEMBER', 13, 1);
declare @MEMBERS_TABLE table (
[ID] uniqueidentifier,
[CONSTITUENTID] uniqueidentifier,
[ISPRIMARY] bit,
[MEMBERID] uniqueidentifier,
[ISCAREGIVER] bit
);
insert into @MEMBERS_TABLE select
T.members.value('(ID)[1]','uniqueidentifier') as 'ID',
T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',
T.members.value('(ISPRIMARY)[1]','bit') as 'ISPRIMARY',
T.members.value('(MEMBERID)[1]','uniqueidentifier') as 'MEMBERID',
0 as 'ISCAREGIVER'
from
@MEMBERS.nodes('/MEMBERS/ITEM') T(members)
update @MEMBERS_TABLE set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
declare @CARDS_TABLE table (
[ID] uniqueidentifier,
[MEMBERSHIPCARDID] uniqueidentifier,
[NAMEONCARD] nvarchar(700),
[EXPIRATIONDATE] datetime,
[MEMBERID] uniqueidentifier);
insert into @CARDS_TABLE select
T.membershipcards.value('(ID)[1]','uniqueidentifier') as 'ID',
T.membershipcards.value('(MEMBERSHIPCARDID)[1]','uniqueidentifier') as 'MEMBERSHIPCARDID',
T.membershipcards.value('(NAMEONCARD)[1]','nvarchar(700)') as 'NAMEONCARD',
T.membershipcards.value('(EXPIRATIONDATE)[1]','datetime') as 'EXPIRATIONDATE',
T.membershipcards.value('(MEMBERID)[1]','uniqueidentifier') as 'MEMBERID'
from
@MEMBERSHIPCARDS.nodes('/MEMBERSHIPCARDS/ITEM') T(membershipcards)
update @CARDS_TABLE set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
declare @ADDONS_TABLE table (
[ID] uniqueidentifier,
[APPLY] bit,
[ADDONID] uniqueidentifier,
[ADDONTYPE] tinyint,
[PRICE] money,
[NUMBEROFADDONS] smallint,
[MULTIPLEALLOWED] bit
);
insert into @ADDONS_TABLE select
T.membershipprogramaddon.value('(ID)[1]','uniqueidentifier') as 'ID',
T.membershipprogramaddon.value('(APPLY)[1]','bit') as 'APPLY',
T.membershipprogramaddon.value('(ADDONID)[1]','uniqueidentifier') as 'ADDONID',
T.membershipprogramaddon.value('(ADDONTYPE)[1]','tinyint') as 'ADDONTYPE',
T.membershipprogramaddon.value('(PRICE)[1]','money') as 'PRICE',
T.membershipprogramaddon.value('(NUMBEROFADDONS)[1]','smallint') as 'NUMBEROFADDONS',
T.membershipprogramaddon.value('(MULTIPLEALLOWED)[1]','bit') as 'MULTIPLEALLOWED'
from
@MEMBERSHIPPROGRAMADDON.nodes('/MEMBERSHIPPROGRAMADDON/ITEM') T(membershipprogramaddon)
update @ADDONS_TABLE set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
declare @ADDONSCOUNT smallint = 0;
select
@ADDONSCOUNT = COUNT(ADDONID)
from
@ADDONS_TABLE
where
APPLY = 1
-- Check to make sure at least one member is the primary member
if not exists
(
select 1
from @MEMBERS_TABLE
where ISPRIMARY = 1
)
begin
raiserror('ERR_NOPRIMARYMEMBER',13,1)
end
--duplicate constituent check
if exists
(
select count(CONSTITID)
from
(
select CONSTITUENTID CONSTITID
from @MEMBERS_TABLE
) Result
group by CONSTITID
having count(*) > 1
)
raiserror('ERR_CONSTITUENTMORETHANONCE', 13,1);
-- Check for correct number of children, members, and cards
declare @NUMBEROFMEMBERS smallint;
declare @CHILDREN smallint;
declare @CARDSALLOWED smallint;
set @NUMBEROFMEMBERS = 0;
set @CHILDREN = 0;
set @CARDSALLOWED = 0;
select
@CHILDREN = MEMBERSHIPLEVEL.CHILDRENALLOWED,
@NUMBEROFMEMBERS = MEMBERSHIPLEVEL.MEMBERSALLOWED + @TOTALADDITIONALMEMBERS,
@CARDSALLOWED = MEMBERSHIPLEVEL.CARDSALLOWED
from
dbo.MEMBERSHIPLEVEL
inner join
dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
where
MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID;
-- Set the price if it isn't manually set
if @PRICE is null
begin
set @PRICE = 0;
select
@PRICE = isnull(MEMBERSHIPLEVELTERM.AMOUNT, 0)
from dbo.MEMBERSHIPLEVELTERM
where MEMBERSHIPLEVELTERM.ID = @MEMBERSHIPLEVELTERMID
end
declare @ERRORMSG nvarchar(52);
set @ERRORMSG = 'Only ' +convert(nvarchar(6), @NUMBEROFMEMBERS)+ ' member(s) are allowed for this membership.';
declare @MEMBERSCOUNT integer = 0
select @MEMBERSCOUNT = count(CONSTITUENTID)
from @MEMBERS_TABLE
if @NUMBEROFMEMBERS > 0 and @NUMBEROFMEMBERS < @MEMBERSCOUNT
begin
raiserror(@ERRORMSG, 13, 1);
end
set @ERRORMSG = 'The number of children may not be larger than '+convert(nvarchar(6), @CHILDREN)+'.'
-- Number of children validation
if @CHILDREN < @NUMBEROFCHILDREN and @CHILDREN > 0
begin
raiserror(@ERRORMSG, 13, 1);
end
--only validate cards if a number is set
--if @CARDSALLOWED > 0
--begin
declare @CARDCOUNT smallint;
select @CARDCOUNT = count(ID)
from @CARDS_TABLE;
-- Number of cards validation
set @ERRORMSG = 'This membership level only allows up to '+convert(nvarchar(6),@CARDSALLOWED)+' card(s).';
if @CARDCOUNT > @CARDSALLOWED
begin
raiserror(@ERRORMSG, 13,1);
end
--end
declare @ACTIONCODE tinyint;
declare @ACTION nvarchar(25);
select @ACTIONCODE = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(@MEMBERSHIPID, @MEMBERSHIPLEVELID, @CURRENTDATE);
declare @ORDERITEMDESCRIPTION nvarchar(255);
set @ORDERITEMDESCRIPTION = isnull(dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(@MEMBERSHIPPROGRAMID) + ' - ' +
dbo.UFN_MEMBERSHIPLEVEL_GETNAME(@MEMBERSHIPLEVELID) + ' (' +
dbo.UFN_MEMBERSHIPLEVELTERM_GETVALUE(@MEMBERSHIPLEVELTERMID) + '): ', '');
declare @STATUSCODE tinyint;
select @STATUSCODE = STATUSCODE from dbo.MEMBERSHIP where ID = @MEMBERSHIPID;
if @ACTIONCODE = 0 or @STATUSCODE = 2
set @ACTION = 'Join';
else if @ACTIONCODE = 1
set @ACTION = 'Renew';
else if @ACTIONCODE = 2
set @ACTION = 'Upgrade';
else if @ACTIONCODE = 3
set @ACTION = 'Downgrade';
else if @ACTIONCODE = 5
set @ACTION = 'Rejoin';
else
set @ACTION = 'Unknown';
set @ORDERITEMDESCRIPTION = @ORDERITEMDESCRIPTION + @ACTION;
declare @SALESORDERITEMADDONADULTID uniqueidentifier;
declare @ADDONADULTSPRICE money;
declare @SALESORDERITEMADDONGUESTID uniqueidentifier;
declare @ADDONGUESTSPRICE money;
if @ISEDIT = 0
begin
declare @CONSTITUENTID uniqueidentifier;
if @GIVENBYID is not null
set @CONSTITUENTID = @GIVENBYID;
else
select
@CONSTITUENTID = CONSTITUENTID
from
@MEMBERS_TABLE
where
ISPRIMARY = 1;
if @CONSTITUENTID is not null
update
dbo.SALESORDER
set
CONSTITUENTID = @CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @SALESORDERID
and CONSTITUENTID is null;
--Make the CONSTITUENTID the recipient of the order if there is no recipient
if @CONSTITUENTID is not null and (select [RECIPIENTID] from dbo.[SALESORDER] where [ID] = @SALESORDERID) is null
begin
update dbo.[SALESORDER]
set
[RECIPIENTID] = @CONSTITUENTID,
[ADDRESSID] = (select top(1) [ID] from dbo.[ADDRESS] where [CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1 and [DONOTMAIL] = 0),
[PHONEID] = (select top(1) [ID] from dbo.[PHONE] where [PHONE].[CONSTITUENTID] = @CONSTITUENTID and [PHONE].[ISPRIMARY] = 1 and [DONOTCALL] = 0),
[EMAILADDRESSID] = (select top(1) [ID] from dbo.[EMAILADDRESS] where [CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1 and [DONOTEMAIL] = 0),
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @SALESORDERID
end
insert into dbo.SALESORDERITEM
(
ID,
SALESORDERID,
TYPECODE,
[DESCRIPTION],
QUANTITY,
PRICE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@SALESORDERID,
1,
@ORDERITEMDESCRIPTION,
1,
@PRICE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.SALESORDERITEMMEMBERSHIP
(
ID,
MEMBERSHIPID,
MEMBERSHIPPROGRAMID,
MEMBERSHIPPROGRAMNAME,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
MEMBERSHIPLEVELTYPECODEID,
NUMBEROFCHILDREN,
COMMENTS,
ISGIFT,
SENDRENEWALCODE,
EXPIRATIONDATE,
GIVENBYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
TYPECODE
)
values
(
@ID,
@MEMBERSHIPID,
@MEMBERSHIPPROGRAMID,
coalesce((select [MEMBERSHIPPROGRAM].[NAME] from dbo.[MEMBERSHIPPROGRAM] where [MEMBERSHIPPROGRAM].[ID] = @MEMBERSHIPPROGRAMID),''),
@MEMBERSHIPLEVELID,
@MEMBERSHIPLEVELTERMID,
@MEMBERSHIPLEVELTYPECODEID,
@NUMBEROFCHILDREN,
@COMMENTS,
@ISGIFT,
@SENDRENEWALCODE,
@EXPIRATIONDATE,
@GIVENBYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@SALESORDERITEMMEMBERSHIPTYPECODE
);
if @ADDONSCOUNT > 0
begin
insert into dbo.SALESORDERITEM
(
ID,
SALESORDERID,
TYPECODE,
[DESCRIPTION],
QUANTITY,
PRICE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
(
select
ID,
@SALESORDERID,
16,
(select ADDON.NAME from dbo.ADDON where ADDON.ID = ADDONID),
NUMBEROFADDONS,
PRICE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@ADDONS_TABLE
where
APPLY = 1
);
insert into dbo.SALESORDERITEMMEMBERSHIPADDON
(
ID,
MEMBERSHIPID,
SALESORDERITEMMEMBERSHIPID,
ADDONTYPECODE,
ADDONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
(
select
ID,
@MEMBERSHIPID,
@ID,
ADDONTYPE,
ADDONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@ADDONS_TABLE
where
APPLY = 1
);
end
-- The following commented out execute is refactored to be inline below.
--exec dbo.USP_SALESORDERITEMMEMBERSHIP_GETMEMBERS_ADDFROMXML @ID, @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;
insert into dbo.[SALESORDERITEMMEMBER]
([SALESORDERITEMMEMBERSHIPID],
[CONSTITUENTID],
[ID],
[ISCAREGIVER],
[ISPRIMARY],
[MEMBERID],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select
@ID,
[CONSTITUENTID],
[ID],
[ISCAREGIVER],
[ISPRIMARY],
[MEMBERID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERS_TABLE;
-- The following commented out execute is refactored to be inline below.
--exec dbo.USP_SALESORDERITEMMEMBERSHIP_GETMEMBERSHIPCARDS_ADDFROMXML @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;
insert into dbo.[SALESORDERITEMMEMBERSHIPCARD]
(
[ID],
[MEMBERSHIPCARDID],
[SALESORDERITEMMEMBERID],
[NAMEONCARD],
[EXPIRATIONDATE],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
[ID],
[MEMBERSHIPCARDID],
[MEMBERID],
[NAMEONCARD],
[EXPIRATIONDATE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@CARDS_TABLE;
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_CALCULATETAXES @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_UPDATEMEMBERSHIPPROMOS @SALESORDERID, @CHANGEAGENTID;
end
--edit begins here
else begin
if @MEMBERSHIPID is not null and (@MEMBERSHIPPROGRAMID is null or @CURRENTMEMBERSHIPPROGRAMID <> @MEMBERSHIPPROGRAMID)
raiserror('ERR_PROGRAMCHANGED', 13, 1);
-- Check if allow multiple memberships constraint is satisfied
if exists(
select *
from dbo.UFN_SALESORDERITEMMEMBERSHIP_GETMEMBERS(@ID)
where dbo.UFN_SALESORDERITEMMEMBERSHIP_VALIDALLOWMULTIPLEMEMBERSHIPS(CONSTITUENTID, @ID, @MEMBERSHIPPROGRAMID) <> 1)
raiserror('ERR_VALIDALLOWMULTIPLEMEMBERSHIPS', 13, 1);
--update add-ons
delete
from
dbo.SALESORDERITEM
where
ID in
(
select
A.ID
from
@ADDONS_TABLE A
where
A.APPLY = 0
);
update
dbo.SALESORDERITEM
set
SALESORDERITEM.QUANTITY = A.NUMBEROFADDONS,
SALESORDERITEM.CHANGEDBYID = @CHANGEAGENTID,
SALESORDERITEM.DATECHANGED = @CURRENTDATE
from
@ADDONS_TABLE A
inner join
dbo.SALESORDERITEM on SALESORDERITEM.ID = A.ID
where
SALESORDERITEM.ID = A.ID;
insert into dbo.SALESORDERITEM
(
ID,
SALESORDERID,
TYPECODE,
[DESCRIPTION],
QUANTITY,
PRICE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
(
select
A.ID,
@SALESORDERID,
16,
(select ADDON.NAME from dbo.ADDON where ADDON.ID = A.ADDONID),
A.NUMBEROFADDONS,
A.PRICE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@ADDONS_TABLE A
left join
dbo.SALESORDERITEM on SALESORDERITEM.ID = A.ID
where
A.APPLY = 1
and SALESORDERITEM.ID is null
);
insert into dbo.SALESORDERITEMMEMBERSHIPADDON
(
ID,
MEMBERSHIPID,
SALESORDERITEMMEMBERSHIPID,
ADDONTYPECODE,
ADDONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
(
select
A.ID,
@MEMBERSHIPID,
@ID,
A.ADDONTYPE,
A.ADDONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@ADDONS_TABLE A
left join
dbo.SALESORDERITEMMEMBERSHIPADDON on SALESORDERITEMMEMBERSHIPADDON.ID = A.ID
where
A.APPLY = 1
and SALESORDERITEMMEMBERSHIPADDON.ID is null
);
update
dbo.SALESORDERITEM
set
[DESCRIPTION] = @ORDERITEMDESCRIPTION,
PRICE = @PRICE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
update
dbo.SALESORDERITEMMEMBERSHIP
set
MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID,
MEMBERSHIPPROGRAMNAME = coalesce((select [MEMBERSHIPPROGRAM].[NAME] from dbo.[MEMBERSHIPPROGRAM] where [MEMBERSHIPPROGRAM].[ID] = @MEMBERSHIPPROGRAMID),''),
MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID,
MEMBERSHIPLEVELTYPECODEID = @MEMBERSHIPLEVELTYPECODEID,
NUMBEROFCHILDREN = @NUMBEROFCHILDREN,
COMMENTS = @COMMENTS,
ISGIFT = @ISGIFT,
SENDRENEWALCODE = @SENDRENEWALCODE,
EXPIRATIONDATE = @EXPIRATIONDATE,
GIVENBYID = @GIVENBYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
-- The following commented out execute is refactored to be inline below.
--exec dbo.USP_SALESORDERITEMMEMBERSHIP_GETMEMBERS_UPDATEFROMXML @ID, @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;
declare @contextCache varbinary(128);
-- cache current context information
set @contextCache = CONTEXT_INFO();
-- set CONTEXT_INFO to @CHANGEAGENTID
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- delete any items that no longer exist in the XML table
delete from dbo.[SALESORDERITEMMEMBER] where [SALESORDERITEMMEMBER].ID in
(select ID from dbo.UFN_SALESORDERITEMMEMBERSHIP_GETMEMBERS
(
@ID
)
EXCEPT select ID from @MEMBERS_TABLE)
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
-- update the items that exist in the XML table and the db
update dbo.[SALESORDERITEMMEMBER]
set [SALESORDERITEMMEMBER].[CONSTITUENTID]=temp.[CONSTITUENTID],
[SALESORDERITEMMEMBER].[ISCAREGIVER]=temp.[ISCAREGIVER],
[SALESORDERITEMMEMBER].[ISPRIMARY]=temp.[ISPRIMARY],
[SALESORDERITEMMEMBER].CHANGEDBYID = @CHANGEAGENTID,
[SALESORDERITEMMEMBER].DATECHANGED = @CURRENTDATE
from dbo.[SALESORDERITEMMEMBER] inner join @MEMBERS_TABLE as [temp] on [SALESORDERITEMMEMBER].ID = [temp].ID
where ([SALESORDERITEMMEMBER].[CONSTITUENTID]<>temp.[CONSTITUENTID]) or
([SALESORDERITEMMEMBER].[CONSTITUENTID] is null and temp.[CONSTITUENTID] is not null) or
([SALESORDERITEMMEMBER].[CONSTITUENTID] is not null and temp.[CONSTITUENTID] is null) or
([SALESORDERITEMMEMBER].[ID]<>temp.[ID]) or
([SALESORDERITEMMEMBER].[ID] is null and temp.[ID] is not null) or
([SALESORDERITEMMEMBER].[ID] is not null and temp.[ID] is null) or
([SALESORDERITEMMEMBER].[ISCAREGIVER]<>temp.[ISCAREGIVER]) or
([SALESORDERITEMMEMBER].[ISCAREGIVER] is null and temp.[ISCAREGIVER] is not null) or
([SALESORDERITEMMEMBER].[ISCAREGIVER] is not null and temp.[ISCAREGIVER] is null) or
([SALESORDERITEMMEMBER].[ISPRIMARY]<>temp.[ISPRIMARY]) or
([SALESORDERITEMMEMBER].[ISPRIMARY] is null and temp.[ISPRIMARY] is not null) or
([SALESORDERITEMMEMBER].[ISPRIMARY] is not null and temp.[ISPRIMARY] is null) or
([SALESORDERITEMMEMBER].[MEMBERID]<>temp.[MEMBERID]) or
([SALESORDERITEMMEMBER].[MEMBERID] is null and temp.[MEMBERID] is not null) or
([SALESORDERITEMMEMBER].[MEMBERID] is not null and temp.[MEMBERID] is null)
-- insert new items
insert into dbo.[SALESORDERITEMMEMBER]
([SALESORDERITEMMEMBERSHIPID],
[CONSTITUENTID],
[ID],
[ISCAREGIVER],
[ISPRIMARY],
[MEMBERID],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select @ID,
[CONSTITUENTID],
[ID],
[ISCAREGIVER],
[ISPRIMARY],
[MEMBERID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERS_TABLE as [temp]
where not exists (select ID from dbo.[SALESORDERITEMMEMBER] as data where data.ID = [temp].ID)
-- The following commented out execute is refactored to be inline below.
-- exec dbo.USP_SALESORDERITEMMEMBERSHIP_GETMEMBERSHIPCARDS_UPDATEFROMXML @ID, @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;
-- cache current context information
set @contextCache = CONTEXT_INFO();
-- set CONTEXT_INFO to @CHANGEAGENTID
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- delete any items that no longer exist in the XML table
delete from
dbo.[SALESORDERITEMMEMBERSHIPCARD]
where
ID in
(
select
SALESORDERITEMMEMBERSHIPCARD.ID
from
dbo.SALESORDERITEMMEMBERSHIPCARD
inner join
dbo.SALESORDERITEMMEMBER on SALESORDERITEMMEMBER.ID = SALESORDERITEMMEMBERSHIPCARD.SALESORDERITEMMEMBERID
where
SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID = @ID
except select ID from @CARDS_TABLE
);
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
-- update the items that exist in the XML table and the db
update
dbo.[SALESORDERITEMMEMBERSHIPCARD]
set
[SALESORDERITEMMEMBERSHIPCARD].[NAMEONCARD]=temp.[NAMEONCARD],
[SALESORDERITEMMEMBERSHIPCARD].[EXPIRATIONDATE]=temp.[EXPIRATIONDATE],
[SALESORDERITEMMEMBERSHIPCARD].[SALESORDERITEMMEMBERID]=temp.[MEMBERID],
[SALESORDERITEMMEMBERSHIPCARD].CHANGEDBYID = @CHANGEAGENTID,
[SALESORDERITEMMEMBERSHIPCARD].DATECHANGED = @CURRENTDATE
from
dbo.[SALESORDERITEMMEMBERSHIPCARD]
inner join
@CARDS_TABLE as [temp] on [SALESORDERITEMMEMBERSHIPCARD].ID = [temp].ID
where
([SALESORDERITEMMEMBERSHIPCARD].[ID]<>temp.[ID]) or
([SALESORDERITEMMEMBERSHIPCARD].[ID] is null and temp.[ID] is not null) or
([SALESORDERITEMMEMBERSHIPCARD].[ID] is not null and temp.[ID] is null) or
([SALESORDERITEMMEMBERSHIPCARD].[MEMBERSHIPCARDID]<>temp.[MEMBERSHIPCARDID]) or
([SALESORDERITEMMEMBERSHIPCARD].[MEMBERSHIPCARDID] is null and temp.[MEMBERSHIPCARDID] is not null) or
([SALESORDERITEMMEMBERSHIPCARD].[MEMBERSHIPCARDID] is not null and temp.[MEMBERSHIPCARDID] is null) or
([SALESORDERITEMMEMBERSHIPCARD].[NAMEONCARD]<>temp.[NAMEONCARD]) or
([SALESORDERITEMMEMBERSHIPCARD].[NAMEONCARD] is null and temp.[NAMEONCARD] is not null) or
([SALESORDERITEMMEMBERSHIPCARD].[NAMEONCARD] is not null and temp.[NAMEONCARD] is null) or
([SALESORDERITEMMEMBERSHIPCARD].[EXPIRATIONDATE]<>temp.[EXPIRATIONDATE]) or
([SALESORDERITEMMEMBERSHIPCARD].[EXPIRATIONDATE] is null and temp.[EXPIRATIONDATE] is not null) or
([SALESORDERITEMMEMBERSHIPCARD].[EXPIRATIONDATE] is not null and temp.[EXPIRATIONDATE] is null) or
([SALESORDERITEMMEMBERSHIPCARD].[SALESORDERITEMMEMBERID]<>temp.[MEMBERID]) or
([SALESORDERITEMMEMBERSHIPCARD].[SALESORDERITEMMEMBERID] is null and temp.[MEMBERID] is not null) or
([SALESORDERITEMMEMBERSHIPCARD].[SALESORDERITEMMEMBERID] is not null and temp.[MEMBERID] is null);
-- insert new items
insert into dbo.[SALESORDERITEMMEMBERSHIPCARD]
(
[ID],
[MEMBERSHIPCARDID],
[SALESORDERITEMMEMBERID],
[NAMEONCARD],
[EXPIRATIONDATE],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
[ID],
[MEMBERSHIPCARDID],
[MEMBERID],
[NAMEONCARD],
[EXPIRATIONDATE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@CARDS_TABLE as [temp]
where
not exists (select ID from dbo.[SALESORDERITEMMEMBERSHIPCARD] as data where data.ID = [temp].ID);
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_CALCULATETAXES @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_UPDATEMEMBERSHIPPROMOS @SALESORDERID, @CHANGEAGENTID;
declare @TOTALAPPLIEDTICKETDISCOUNT as money = 0
declare @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID as uniqueidentifier
select
@SALESORDERITEMMEMBERSHIPITEMPROMOTIONID = ID,
@TOTALAPPLIEDTICKETDISCOUNT = AMOUNT
from
dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
where
SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = @ID
if @PRICE < @TOTALAPPLIEDTICKETDISCOUNT
update dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION set
AMOUNT = @PRICE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID = @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;