V_QUERY_SALESORDERITEM
Provides the ability to query sales order items.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| SALESORDERID | uniqueidentifier | yes | Sales order ID |
| TYPE | nvarchar(40) | yes | Type |
| PRICINGSTRUCTURE | nvarchar(40) | yes | Pricing structure |
| DESCRIPTION | nvarchar(358) | yes | Description |
| QUANTITY | decimal(20, 4) | Quantity | |
| PRICE | money | Price | |
| PERCENT | decimal(7, 4) | yes | Tax/Fee percent |
| TOTAL | money | yes | Total |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| DISCOUNTEDAMOUNT | money | ||
| NETAMOUNT | money | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:14:34 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SALESORDERITEM AS
select
[SALESORDERITEM].[ID],
[SALESORDERITEM].[SALESORDERID],
[SALESORDERITEM].[TYPE],
[SALESORDERITEM].[PRICINGSTRUCTURE],
[SALESORDERITEM].[DESCRIPTION],
[SALESORDERITEM].[QUANTITY],
[SALESORDERITEM].[PRICE],
[SALESORDERITEM].[PERCENT],
[SALESORDERITEM].[TOTAL],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[SALESORDERITEM].[DATEADDED],
[SALESORDERITEM].[DATECHANGED],
[SALESORDERITEM].[TSLONG],
isnull(TOTALDISCOUNTED.DISCOUNTEDAMOUNT, 0) as DISCOUNTEDAMOUNT,
isnull([SALESORDERITEM].[TOTAL], 0) - isnull(TOTALDISCOUNTED.DISCOUNTEDAMOUNT, 0) - isnull(MEMBERSHIPPROMOTIONS.TOTALAMOUNT, 0) as NETAMOUNT
/*EXTENSION*/
from dbo.[SALESORDERITEM]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY]
on [SALESORDERITEM].[ADDEDBYID] = [ADDEDBY].[ID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY]
on [SALESORDERITEM].[CHANGEDBYID] = [CHANGEDBY].[ID]
left join
(select
SUM(AMOUNT)as DISCOUNTEDAMOUNT,
SALESORDERITEMID
from
V_QUERY_SALESORDERITEMORDERDISCOUNTDETAIL
group by
SALESORDERITEMID) TOTALDISCOUNTED on TOTALDISCOUNTED.SALESORDERITEMID = SALESORDERITEM.ID
left join --Including membership promotion discounts
(select
SUM(AMOUNT)as TOTALAMOUNT,
SALESORDERITEMID
from
V_QUERY_SALESORDERITEMMEMBERSHIPITEMPROMOTION
group by
SALESORDERITEMID) MEMBERSHIPPROMOTIONS on MEMBERSHIPPROMOTIONS.SALESORDERITEMID = SALESORDERITEM.ID
--Pulling item discounts into order items
union all
select
[SALESORDERITEMITEMDISCOUNT].[ID],
[SALESORDERITEM].[SALESORDERID],
dbo.UFN_SALESORDERITEM_TYPECODE_GETDESCRIPTION(5),
[SALESORDERITEM].[TYPE],
[DISCOUNT].[NAME] + ' - ' + SALESORDERITEM.DESCRIPTION,
1,
[SALESORDERITEMITEMDISCOUNT].[AMOUNT],
null as [PERCENT],
[SALESORDERITEMITEMDISCOUNT].[AMOUNT],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[SALESORDERITEMITEMDISCOUNT].[DATEADDED],
[SALESORDERITEMITEMDISCOUNT].[DATECHANGED],
[SALESORDERITEMITEMDISCOUNT].[TSLONG],
0 as DISCOUNTEDAMOUNT,
[SALESORDERITEMITEMDISCOUNT].[AMOUNT] as NETAMOUNT
from dbo.[SALESORDERITEMITEMDISCOUNT]
left outer join dbo.[DISCOUNT]
on [SALESORDERITEMITEMDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID]
left outer join dbo.[SALESORDERITEM]
on [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY]
on [SALESORDERITEMITEMDISCOUNT].[ADDEDBYID] = [ADDEDBY].[ID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY]
on [SALESORDERITEMITEMDISCOUNT].[CHANGEDBYID] = [CHANGEDBY].[ID]