USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMGENERIC_MEMBERSHIP_ONLINE
The save procedure used by the add dataform template "Sales Order Item Generic Membership Online Add Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @SALESORDERID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @QUANTITY | decimal(18, 0) | IN | Quantity |
| @AMOUNT | money | IN | Amount |
| @DATA | xml | IN | Data |
| @OPTIONS | xml | IN | Options |
| @CALLBACKURL | nvarchar(255) | IN | Callback URL |
| @SYSTEMTYPENAME | nvarchar(255) | IN | System Type Name |
| @ASSEMBLYNAME | nvarchar(255) | IN | Assembly Name |
| @ATTRIBUTES | xml | IN | Attributes |
| @CATEGORYNAME | nvarchar(255) | IN | Category Name |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @ACKNOWLEDGEMENT | nvarchar(max) | IN | Acknowledgement |
| @MEMBERSHIPID | uniqueidentifier | IN | Membership ID |
| @MEMBERSHIPPROGRAMID | uniqueidentifier | IN | Membership program ID |
| @MEMBERSHIPLEVELID | uniqueidentifier | IN | Membership level ID |
| @MEMBERSHIPLEVELTERMID | uniqueidentifier | IN | Membership level term ID |
| @MEMBERSHIPLEVELTYPEID | uniqueidentifier | IN | Membership level type code ID |
| @MEMBERSHIPADDONS | xml | IN | |
| @NUMBEROFCHILDREN | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMGENERIC_MEMBERSHIP_ONLINE (
@ID uniqueidentifier = null output,
@SALESORDERID uniqueidentifier,
@DESCRIPTION nvarchar(255) = null,
@QUANTITY decimal = 1,
@AMOUNT money,
@DATA xml = null,
@OPTIONS xml = null,
@CALLBACKURL nvarchar(255) = null,
@SYSTEMTYPENAME nvarchar(255) = null,
@ASSEMBLYNAME nvarchar(255) = null,
@ATTRIBUTES xml = null,
@CATEGORYNAME nvarchar(255) = null,
@CHANGEAGENTID uniqueidentifier = null,
@ACKNOWLEDGEMENT nvarchar(max) = null,
@MEMBERSHIPID uniqueidentifier = null,
@MEMBERSHIPPROGRAMID uniqueidentifier,
@MEMBERSHIPLEVELID uniqueidentifier,
@MEMBERSHIPLEVELTERMID uniqueidentifier,
@MEMBERSHIPLEVELTYPEID uniqueidentifier = null,
@MEMBERSHIPADDONS xml= '',
@NUMBEROFCHILDREN 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()
begin try
-- handle inserting the data
insert into dbo.SALESORDERITEM
(
[ID],
[SALESORDERID],
[TYPECODE],
[DESCRIPTION],
[QUANTITY],
[PRICE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[DATA],
[OPTIONS],
[CALLBACKURL],
[SYSTEMTYPENAME],
[ASSEMBLYNAME],
[ATTRIBUTES],
[CATEGORYNAME],
[ACKNOWLEDGEMENT]
)
values
(
@ID,
@SALESORDERID,
1,
@DESCRIPTION,
@QUANTITY,
@AMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@DATA,
@OPTIONS,
@CALLBACKURL,
@SYSTEMTYPENAME,
@ASSEMBLYNAME,
@ATTRIBUTES,
@CATEGORYNAME,
@ACKNOWLEDGEMENT
)
declare @EXPIRATIONDATE datetime;
declare @ACTIONCODE tinyint;
select @ACTIONCODE = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(@MEMBERSHIPID, @MEMBERSHIPLEVELID, @CURRENTDATE)
if @ACTIONCODE = 0 or @ACTIONCODE = 5 --join/rejoin
set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @CURRENTDATE);
else
begin
declare @CURRENTSTATUS tinyint;
select
@EXPIRATIONDATE = [EXPIRATIONDATE],
@CURRENTSTATUS = [STATUSCODE]
from dbo.[MEMBERSHIP]
where ID = @MEMBERSHIPID;
if @CURRENTSTATUS = 2 --pending membership
set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @CURRENTDATE);
else
set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION(@EXPIRATIONDATE, @MEMBERSHIPLEVELTERMID);
end
declare @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null;
if @MEMBERSHIPLEVELTYPEID is not null
select @MEMBERSHIPLEVELTYPECODEID = [LEVELTYPECODEID] from dbo.[MEMBERSHIPLEVELTYPE] where [ID] = @MEMBERSHIPLEVELTYPEID;
insert into dbo.[SALESORDERITEMMEMBERSHIP] (
[ID],
[MEMBERSHIPID],
[MEMBERSHIPPROGRAMID],
[MEMBERSHIPLEVELID],
[MEMBERSHIPLEVELTERMID],
[MEMBERSHIPLEVELTYPECODEID],
[EXPIRATIONDATE],
[NUMBEROFCHILDREN],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values (
@ID,
@MEMBERSHIPID,
@MEMBERSHIPPROGRAMID,
@MEMBERSHIPLEVELID,
@MEMBERSHIPLEVELTERMID,
@MEMBERSHIPLEVELTYPECODEID,
@EXPIRATIONDATE,
@NUMBEROFCHILDREN,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
declare @ADDONS_TABLE table (
[ID] uniqueidentifier,
[ADDONID] uniqueidentifier,
[ADDONNAME] nvarchar(100),
[ADDONTYPECODE] tinyint,
[PRICE] money,
[quantity] smallint
);
insert into @ADDONS_TABLE select
newid(),
ADDON.ID,
ADDON.NAME,
ADDON.ADDONTYPECODE,
T.membershipaddon.value('(PRICE)[1]','money') as 'PRICE',
T.membershipaddon.value('(QUANTITY)[1]','int') as 'NUMBEROFADDONS'
from @MEMBERSHIPADDONS.nodes('/MEMBERSHIPADDONS/ITEM') T(membershipaddon)
inner join dbo.ADDON with (nolock) on
T.membershipaddon.value('(ADDONID)[1]','uniqueidentifier') = ADDON.ID
insert into dbo.SALESORDERITEM
(
ID,
SALESORDERID,
TYPECODE,
[DESCRIPTION],
QUANTITY,
PRICE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
(
select
[ADDONS].ID,
@SALESORDERID,
16,
[ADDONS].ADDONNAME,
[ADDONS].QUANTITY,
[ADDONS].PRICE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ADDONS_TABLE [ADDONS]
);
insert into dbo.SALESORDERITEMMEMBERSHIPADDON
(
ID,
SALESORDERITEMMEMBERSHIPID,
ADDONTYPECODE,
ADDONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
(
select
[ADDONS].ID,
@ID,
[ADDONS].ADDONTYPECODE,
[ADDONS].ADDONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ADDONS_TABLE [ADDONS]
);
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_CALCULATETAXES @SALESORDERID, @CHANGEAGENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;