CREDITITEM
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| CREDITID | uniqueidentifier | yes | |
| SALESORDERITEMID | uniqueidentifier | yes | |
| ADDEDBYID | uniqueidentifier | ||
| CHANGEDBYID | uniqueidentifier | ||
| DATEADDED | datetime | ||
| DATECHANGED | datetime | ||
| TS | timestamp | ||
| TSLONG | bigint | yes | |
| PRICE | money | ||
| FEES | money | ||
| QUANTITY | decimal(20, 4) | ||
| DISCOUNTS | money | ||
| REVENUESPLITID | uniqueidentifier | yes | |
| TYPECODE | tinyint | ||
| DESCRIPTION | nvarchar(700) | ||
| GROUPID | uniqueidentifier | yes | |
| GROUPTYPECODE | tinyint | ||
| GROUPTYPE | nvarchar(18) | yes | |
| TOTAL | decimal(38, 6) | yes | |
| TYPE | nvarchar(40) | yes |
Definition
Copy
CREATE view dbo.CREDITITEM with schemabinding as
--Refund's Credit Items
select
LI.ID
,FT.ID [CREDITID]
,EXT.SALESORDERITEMID
,LI.ADDEDBYID
,LI.CHANGEDBYID
,LI.DATEADDED
,LI.DATECHANGED
,LI.TS
,LI.TSLONG
,LI.UNITVALUE [PRICE]
,EXT.FEES
,LI.QUANTITY
,EXT.DISCOUNTS
,LI.SOURCELINEITEMID [REVENUESPLITID]
,EXT.TYPECODE
,LI.[DESCRIPTION]
,EXT.GROUPID
,EXT.GROUPTYPECODE
,EXT.GROUPTYPE
,((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) [TOTAL]
,EXT.[TYPE]
from dbo.FINANCIALTRANSACTIONLINEITEM LI
inner join dbo.CREDITITEM_EXT EXT on EXT.ID = LI.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
where FT.TYPECODE = 23
union all
--Discount's Credit Items
select
LI.ID
,EXT.CREDITID
,null [SALESORDERITEMID]
,LI.ADDEDBYID
,LI.CHANGEDBYID
,LI.DATEADDED
,LI.DATECHANGED
,LI.TS
,LI.TSLONG
,LI.UNITVALUE [PRICE]
,0 [FEES]
,LI.QUANTITY
,0 [DISCOUNTS]
,LI.SOURCELINEITEMID [REVENUESPLITID]
,EXT.TYPECODE
,LI.[DESCRIPTION]
,EXT.GROUPID
,EXT.GROUPTYPECODE
,EXT.GROUPTYPE
,((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) [TOTAL]
,EXT.[TYPE]
from dbo.FINANCIALTRANSACTIONLINEITEM LI
inner join dbo.CREDITITEM_EXT EXT on EXT.ID = LI.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
where LI.TYPECODE = 5 and FT.TYPECODE in (5, 99);