Navigation: iVend Database Database > Views >
iVend 6.6 Database Help
vw_InventoryMovement View
Collapse All Expand All
iVend Database Database : vw_InventoryMovement View |
Properties
Creation Date |
4/13/2015 12:00 PM |
Is Schema Bound |
|
Encrypted |
|
Ansi Nulls |
|
Columns
|
Column Name |
Description |
Datatype |
Length |
Allow Nulls |
Default |
Formula |
|
Type |
|
VarChar |
23 |
|
|
|
|
SourceKey |
|
BigInt |
8 |
|
|
|
|
ProductKey |
|
BigInt |
8 |
|
|
|
|
WarehouseKey |
|
BigInt |
8 |
|
|
|
|
ProductGroup |
|
VarWChar |
20 |
|
|
|
|
ProductGroupKey |
|
BigInt |
8 |
|
|
|
|
Id |
|
VarWChar |
20 |
|
|
|
|
Name |
|
VarWChar |
100 |
|
|
|
|
Receipt |
|
Numeric |
9 (20,5) |
|
|
|
|
Issue |
|
Numeric |
9 (20,5) |
|
|
|
|
Date |
|
DBTimeStamp |
4 |
|
|
|
|
TotalPostSaleDiscount |
|
Numeric |
9 (38,7) |
|
|
|
|
Tax |
|
Numeric |
9 (38,6) |
|
|
|
|
AverageCost |
|
Numeric |
9 (20,5) |
|
|
|
|
CostingMethod |
|
Integer |
4 |
|
|
|
Objects that vw_InventoryMovement depends on
|
Database Object |
Object Type |
Description |
Dep Level |
|
Table |
Stores the Goods receipt done in the system. |
1 |
|
|
Table |
Defines the details associated with each goods receipt |
1 |
|
|
Table |
Stores the data related to Inventory Goods Return |
1 |
|
|
Table |
Stores the details of inventory goods return |
1 |
|
|
Table |
Stores any Delta changes to the Inventory. Stores the quantity changes along with the reference of the document due to which the inventory got updated. |
1 |
|
|
Table |
This gets affected when the inventory of a particular item is consumed at the store itself for any reason. |
1 |
|
|
Table |
Defines the details of the product on the goods issue note |
1 |
|
|
Table |
Stores the product related details. |
1 |
|
|
Table |
Stores the information when the product is Build or Broken into its components |
1 |
|
|
Table |
Defines the product details which is being built or break. |
1 |
|
|
Table |
Defined the various groups under which the products can be categorised. |
1 |
|
|
Table |
Stores stock transfer details |
1 |
|
|
Table |
Detail table which stores the stock transfer detail information. |
1 |
|
|
Table |
The main table which defined the primary details concerned with every type of transaction. |
1 |
|
|
Table |
Defines the details of all the transactions which have a fulfillment plan attached to them. |
1 |
|
|
Table |
Store layway type of transactions. |
1 |
|
|
Table |
Stores information about various order booked in the system. |
1 |
|
|
Table |
Stores sale/refund/delivery items attached to a transaction |
1 |
View Definition
CREATE View [dbo].[vw_InventoryMovement] as --For iVend 6.2 --This view is used for Costing reports --It will retrun all Inventory movement --It is running on Business Date select 'External' 'Type' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description 'Name' ,T1.InQty Receipt ,T1.OutQty Issue , T1.Created as [Date] ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey WHERE T1.SourceType=-1 UNION ALL select 'Manual' 'Type' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description 'Name' ,T1.InQty Receipt ,T1.OutQty Issue , T1.Created ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey WHERE T1.SourceType=-99 UNION ALL select 'UNKNOWN' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty, T1.Created ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey WHERE T1.SourceType=0 UNION ALL select 'GoodReceipt' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty, T4.BusinessDate ,T1.InQty * T2.Price 'TotalPostSaleDiscount' ,T1.InQty * T2.Price * T2.TaxRate * 0.01 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvGoodReceiptDetail T2 on T2.GoodsReceiptKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.GoodsReceiptDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN InvGoodReceipt T4 on T4.GoodsReceiptKey=T2.GoodsReceiptKey and T4.SourceType=0 WHERE T1.SourceType=1 UNION ALL select 'GoodReceiptPO' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty, T4.BusinessDate ,T1.InQty * T2.Price 'TotalPostSaleDiscount' ,T1.InQty * T2.Price * T2.TaxRate * 0.01 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvGoodReceiptDetail T2 on T2.GoodsReceiptKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.GoodsReceiptDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN InvGoodReceipt T4 on T4.GoodsReceiptKey=T2.GoodsReceiptKey and T4.SourceType=1 WHERE T1.SourceType=2 UNION ALL select 'GoodReceiptSTN' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty,T4.BusinessDate ,T1.InQty * T2.Price 'TotalPostSaleDiscount' ,T1.InQty * T2.Price * T2.TaxRate * 0.01 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvGoodReceiptDetail T2 on T2.GoodsReceiptKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.GoodsReceiptDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN InvGoodReceipt T4 on T4.GoodsReceiptKey=T2.GoodsReceiptKey and T4.SourceType=2 WHERE T1.SourceType=3 UNION ALL select 'StockTransfer' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty, T4.BusinessDate ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvStockTransferDetail T2 on T2.StockTransferKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.StockTransferDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN InvStockTransfer T4 on T4.StockTransferKey=T2.StockTransferKey and T4.SourceType=1 WHERE T1.SourceType=4 UNION ALL select 'GoodsIssue' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T33.Id as ProductGroup ,T33.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty,T4.BusinessDate ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvInventoryTransactionDetail T2 on T1.ProductKey=T2.ProductKey and T2.InventoryTransactionKey=T1.SourceKey and T2.InvInventoryTransactionDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T33 on T3.ProductGroupKey=T33.ProductGroupKey INNER JOIN InvInventoryTransaction T4 on T4.InventoryTransactionKey=T2.InventoryTransactionKey and T4.SourceType=0 WHERE T1.SourceType=6 UNION ALL select 'KitBuildBreak' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty,T4.BusinessDate ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvProductBuildBreakDetail T2 on T2.ProductBuildBreakKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.ProductBuildBreakDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN InvProductBuildBreak T4 on T4.ProductBuildBreakKey=T2.ProductBuildBreakKey and T4.Type=0 WHERE T1.SourceType=5 UNION ALL select 'KitBuildBreak' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty,T2.BusinessDate ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvProductBuildBreak T2 on T2.ProductBuildBreakKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T1.SourceDetailKey =0 and t2.Type=0 INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey WHERE T1.SourceType=5 UNION ALL select 'Sale' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,0 ,T2.Quantity,T4.BusinessDate ,T2.TotalPostSaleDiscount ,T2.Tax ,Convert(Decimal(20,5), T2.AverageCost) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN TrxTransactionSaleItem T2 on T2.TransactionKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.TransactionItemKey=T1.SourceDetailKey and T2.Type=0 INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN TrxTransaction T4 on T4.TransactionKey=T2.TransactionKey WHERE T1.SourceType=7 UNION ALL select 'Sale' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,0 ,T1.OutQty,T4.BusinessDate ,T2.TotalPostSaleDiscount ,T2.Tax ,Convert(Decimal(20,5), T2.AverageCost) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN TrxTransactionSaleItem T2 on T2.TransactionKey=T1.SourceKey and T2.TransactionItemKey=T1.SourceDetailKey and T2.Type=0 and 1=(select IsAssembly from InvProduct where ProductKey=T2.ProductKey ) INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN TrxTransaction T4 on T4.TransactionKey=T2.TransactionKey WHERE T1.SourceType=7 UNION ALL select 'Refunds' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,0,T4.BusinessDate ,T2.TotalPostSaleDiscount ,T2.Tax ,Convert(Decimal(20,5), T2.AverageCost) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN TrxTransactionSaleItem T2 on T2.TransactionKey=T1.SourceKey and T2.TransactionItemKey=T1.SourceDetailKey and T2.Type=1 and 1=(select IsAssembly from InvProduct where ProductKey=T2.ProductKey ) INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN TrxTransaction T4 on T4.TransactionKey=T2.TransactionKey WHERE T1.SourceType=7 UNION ALL select 'Refunds' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T2.Quantity ,0,T4.BusinessDate ,T2.TotalPostSaleDiscount ,T2.Tax ,Convert(Decimal(20,5), T2.AverageCost) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN TrxTransactionSaleItem T2 on T2.TransactionKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.TransactionItemKey=T1.SourceDetailKey and T2.Type=1 INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN TrxTransaction T4 on T4.TransactionKey=T2.TransactionKey WHERE T1.SourceType=7 UNION ALL select 'Layaway' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T5.Id as ProductGroup ,T5.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T2.QuantityFulfilled,T4.BusinessDate ,T6.TotalPostSaleDiscount ,T6.Tax ,Convert(Decimal(20,5), T6.AverageCost) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN TrxTransactionFulfillmentDetail T2 on T2.TransactionKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.SourceDetailKey=T1.SourceDetailKey and T2.QuantityFulfilled >0 INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey INNER JOIN TrxTransaction T4 on T4.TransactionKey=T2.TransactionKey INNER JOIN TrxTransactionLayaway T6 ON T2.SourceDetailKey = T6.TransactionLayawayKey AND T2.SourceType = 2 WHERE T1.SourceType=8 UNION ALL select 'SpecialOrderTransaction' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T33.Id as ProductGroup ,T33.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T2.QuantityFulfilled,T4.BusinessDate ,T5.TotalPostSaleDiscount ,T5.Tax ,Convert(Decimal(20,5), T5.AverageCost) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN TrxTransactionFulfillmentDetail T2 on T2.TransactionKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.SourceDetailKey=T1.SourceDetailKey and T2.QuantityFulfilled >0 INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T33 on T3.ProductGroupKey=T33.ProductGroupKey INNER JOIN TrxTransaction T4 on T4.TransactionKey=T2.TransactionKey INNER JOIN TrxTransactionOrder T5 on T5.TransactionKey=T2.TransactionKey WHERE T1.SourceType=9 UNION ALL select 'GiftCertificateReceipt' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T33.Id as ProductGroup ,T33.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty,T4.BusinessDate ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvGoodReceiptDetail T2 on T2.GoodsReceiptKey=T1.SourceKey and T1.ProductKey=T2.ProductKey and T2.GoodsReceiptDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T33 on T3.ProductGroupKey=T33.ProductGroupKey INNER JOIN InvGoodReceipt T4 on T4.GoodsReceiptKey=T2.GoodsReceiptKey and T4.SourceType=3 WHERE T1.SourceType=10 UNION ALL select 'Goodsreturn ' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T33.Id as ProductGroup ,T33.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty,T4.ReturnDate ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5),0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvGoodsReturnDetail T2 on T1.ProductKey=T2.ProductKey and T2.GoodsReturnKey=T1.SourceKey and T2.GoodsReturnDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T33 on T3.ProductGroupKey=T33.ProductGroupKey INNER JOIN InvGoodsReturn T4 on T4.GoodsReturnKey=T2.GoodsReturnKey and T4.SourceType=1 WHERE T1.SourceType=12 UNION ALL select 'InventoryReconcilation' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T33.Id as ProductGroup ,T33.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty,T4.BusinessDate ,T1.InQty * T2.Price 'TotalPostSaleDiscount' ,T1.InQty * T2.Price * T2.TaxRate * 0.01 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvGoodReceiptDetail T2 on T1.ProductKey=T2.ProductKey and T2.GoodsReceiptKey=T1.SourceKey and T2.GoodsReceiptDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T33 on T3.ProductGroupKey=T33.ProductGroupKey INNER JOIN InvGoodReceipt T4 on T4.GoodsReceiptKey=T2.GoodsReceiptKey and T4.SourceType=5 WHERE T1.SourceType=13 UNION ALL select 'InventoryReconcilation' ,T1.SourceKey ,T3.ProductKey ,T1.WarehouseKey ,T33.Id as ProductGroup ,T33.ProductGroupKey ,T3.Id, T3.Description ,T1.InQty ,T1.OutQty,T4.BusinessDate ,0 'TotalPostSaleDiscount' ,0 'Tax' ,Convert(Decimal(20,5), 0) As AverageCost ,T3.CostingMethod from InvInventoryItemLog T1 INNER JOIN InvInventoryTransactionDetail T2 on T1.ProductKey=T2.ProductKey and T2.InventoryTransactionKey=T1.SourceKey and T2.InvInventoryTransactionDetailKey=T1.SourceDetailKey INNER JOIN InvProduct T3 on T1.ProductKey=T3.ProductKey INNER JOIN InvProductGroup T33 on T3.ProductGroupKey=T33.ProductGroupKey INNER JOIN InvInventoryTransaction T4 on T4.InventoryTransactionKey=T2.InventoryTransactionKey and T4.SourceType=1 WHERE T1.SourceType=6 |
See Also
Related Objects
© 2019 All Rights Reserved.
Send comments on this topic.