USP_DATAFORMTEMPLATE_VIEW_SALESORDERITEM
The load procedure used by the view dataform template "Sales Order Item 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. |
| @DESCRIPTION | nvarchar(100) | INOUT | Description |
| @QUANTITY | int | INOUT | Quantity |
| @PRICE | money | INOUT | Price |
| @TYPECODE | tinyint | INOUT | Type code |
| @TOTAL | money | INOUT | Total |
| @FEES | money | INOUT | Fees |
| @NAME | nvarchar(100) | INOUT | Name |
| @PRICETYPE | nvarchar(100) | INOUT | Price type |
| @STARTDATE | datetime | INOUT | Date |
| @STARTTIME | nvarchar(4) | INOUT | Time |
| @LOCATION | nvarchar(100) | INOUT | Location |
| @MEMBERSHIPEXPIRATIONDATE | datetime | INOUT | Expires on |
| @MEMBERSHIPLEVELTYPECODE | nvarchar(100) | INOUT | Type |
| @MEMBERSHIPNUMBEROFCHILDREN | smallint | INOUT | Children |
| @MEMBERSHIPGIVENBY | nvarchar(154) | INOUT | Given by |
| @MEMBERSHIPMEMBERS | xml | INOUT | Members |
| @PROMOTIONS | money | INOUT | Promotions |
| @MEMBERSHIPADDONS | xml | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SALESORDERITEM
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@DESCRIPTION nvarchar(100) = null output,
@QUANTITY int = null output,
@PRICE money = null output,
@TYPECODE tinyint = null output,
@TOTAL money = null output,
@FEES money = null output,
@NAME nvarchar(100) = null output,
@PRICETYPE nvarchar(100) = null output,
@STARTDATE datetime = null output,
@STARTTIME nvarchar(4) = null output,
@LOCATION nvarchar(100) = null output,
@MEMBERSHIPEXPIRATIONDATE datetime = null output,
@MEMBERSHIPLEVELTYPECODE nvarchar(100) = null output,
@MEMBERSHIPNUMBEROFCHILDREN smallint = null output,
@MEMBERSHIPGIVENBY nvarchar(154) = null output,
@MEMBERSHIPMEMBERS xml = null output,
@PROMOTIONS money = null output,
@MEMBERSHIPADDONS xml = null output
)
as
set nocount on;
set @DATALOADED = 0;
select
@DATALOADED = 1,
@DESCRIPTION = DESCRIPTION,
@QUANTITY = QUANTITY,
@PRICE = PRICE,
@TYPECODE = TYPECODE,
@TOTAL = TOTAL
from dbo.SALESORDERITEM
where ID = @ID
if @TYPECODE = 0
begin
select @NAME = coalesce(EVENT.NAME, PROGRAM.NAME, ''),
@PRICETYPE = PRICETYPECODE.DESCRIPTION,
@STARTDATE = EVENT.STARTDATE,
@STARTTIME = EVENT.STARTTIME,
@LOCATION = dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID)
from dbo.SALESORDERITEMTICKET
inner join dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
left join dbo.EVENT on SALESORDERITEMTICKET.EVENTID = EVENT.ID
left join dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
where SALESORDERITEMTICKET.ID = @ID;
declare @FEEDOLLARAMOUNT money = 0;
select
@FEEDOLLARAMOUNT = sum(TOTAL)
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMFEE on
SALESORDERITEM.ID = SALESORDERITEMFEE.ID
where SALESORDERITEMFEE.SALESORDERITEMID = @ID
if @FEEDOLLARAMOUNT is not null
set @FEES = @FEEDOLLARAMOUNT
else
set @FEES = 0.0
set @TOTAL = @TOTAL + @FEES
end
else if @TYPECODE = 1
begin
select
@PROMOTIONS = sum(AMOUNT)
from
dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
where
SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = @ID
select
@MEMBERSHIPEXPIRATIONDATE = SALESORDERITEMMEMBERSHIP.EXPIRATIONDATE,
@MEMBERSHIPLEVELTYPECODE = dbo.UFN_MEMBERSHIPLEVELTYPECODE_GETDESCRIPTION(SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTYPECODEID),
@MEMBERSHIPNUMBEROFCHILDREN = SALESORDERITEMMEMBERSHIP.NUMBEROFCHILDREN,
@MEMBERSHIPGIVENBY = dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDERITEMMEMBERSHIP.GIVENBYID),
@MEMBERSHIPMEMBERS = (
select dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDERITEMMEMBER.CONSTITUENTID) [MEMBERNAME]
from dbo.SALESORDERITEMMEMBER
where SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID
for xml raw('ITEM'),type,elements,root('MEMBERSHIPMEMBERS'),binary base64
)
from
dbo.SALESORDERITEMMEMBERSHIP
where
ID = @ID;
--All add-ons that have not been refunded
select
@MEMBERSHIPADDONS = (
select
ADDON.NAME as ADDONNAME,
SALESORDERITEM.QUANTITY - coalesce(sum(FTLI.QUANTITY), 0) as QUANTITY,
SALESORDERITEM.PRICE,
SALESORDERITEM.TOTAL - coalesce(sum(FTLI.ORGAMOUNT), 0) as TOTAL
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMMEMBERSHIPADDON SOIMA
on SALESORDERITEM.ID = SOIMA.ID
inner join dbo.ADDON
on SOIMA.ADDONID = ADDON.ID
left join dbo.CREDITITEM_EXT
on CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEM.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = CREDITITEM_EXT.ID
where SALESORDERITEM.TYPECODE = 16
and SOIMA.SALESORDERITEMMEMBERSHIPID = @ID
and SALESORDERITEM.QUANTITY - coalesce(FTLI.QUANTITY, 0) > 0
group by
SALESORDERITEM.ID,
ADDON.NAME,
SALESORDERITEM.QUANTITY,
SALESORDERITEM.PRICE,
SALESORDERITEM.TOTAL
for xml raw('ITEM'),type,elements,root('MEMBERSHIPADDONS'),binary base64
);
end
else if @TYPECODE = 16
begin
--Since we only pass in the id of one add-on, we have to get all the other
--refunded addons too.
declare @SALESORDERITEMMEMBERSHIPID uniqueidentifier
select
@SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIPID
from dbo.SALESORDERITEMMEMBERSHIPADDON
where ID = @ID
select
@MEMBERSHIPADDONS = (
select
ADDON.NAME as ADDONNAME,
sum(FINANCIALTRANSACTIONLINEITEM.QUANTITY) as QUANTITY,
FINANCIALTRANSACTIONLINEITEM.UNITVALUE as PRICE,
sum(FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT) as TOTAL
from dbo.SALESORDERITEMMEMBERSHIPADDON
inner join CREDITITEM_EXT on CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEMMEMBERSHIPADDON.ID
inner join dbo.ADDON on ADDON.ID = SALESORDERITEMMEMBERSHIPADDON.ADDONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = CREDITITEM_EXT.ID
where SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @SALESORDERITEMMEMBERSHIPID
group by SALESORDERITEMMEMBERSHIPADDON.ID, ADDON.NAME, FINANCIALTRANSACTIONLINEITEM.UNITVALUE
for xml raw('ITEM'),type,elements,root('MEMBERSHIPADDONS'),binary base64
)
end
return 0;