use LA20025
GO

 /**
 ** 出入库视图
 **/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='outInStockRecord') 
   DROP VIEW outInStockRecord 
GO 
CREATE VIEW outInStockRecord
AS

SELECT   task.taskId, task.taskType, task.taskName, task.materialName, task.materialCode, task.quantity, task.createTime,
mater.version,task.sourcePlace,task.toPlace,task.supplier,task.containerCode
FROM      dbo.task as task  WITH(NOLOCK) 
LEFT JOIN dbo.material as mater  WITH(NOLOCK)  ON mater.materialCode=task.materialCode
WHERE   (task.taskType IN (1, 2, 4, 5, 7))
   

GO



 /**
 ** 入库视图
 **/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_inStockOrder') 
   DROP VIEW v_inStockOrder 
GO 
CREATE VIEW v_inStockOrder
AS

SELECT  inOrder.orderCodeId,
inOrder.orderCode,
inOrder.productCode,
inOrder.productName,
inOrder.supplier,
inOrder.quantity,
inOrder.containerCode,
inOrder.orderType,
inOrder.creator,
inOrder.createDate,
inOrder.status,
inOrder.siteCode,
inOrder.version,

task.sourcePlace,
task.middlePlace,
task.toPlace

FROM      inStockOrder as inOrder  WITH(NOLOCK) 
LEFT JOIN dbo.task  WITH(NOLOCK)  ON task.createListCode= inOrder.orderCode

   

GO


 /**
 ** 出库视图
 **/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_outStockList') 
   DROP VIEW v_outStockList 
GO 
CREATE VIEW v_outStockList
AS

SELECT  outOrder.id,
outOrder.outStockOrder,
outOrder.materialCode,
outOrder.materialName,
outOrder.quantity,
outOrder.creator,
outOrder.createTime,
outOrder.version,

task.supplier,
task.containerCode,

task.sourcePlace,
task.middlePlace,
task.toPlace

FROM      outStockList as outOrder  WITH(NOLOCK) 
INNER JOIN dbo.task  WITH(NOLOCK)  ON task.createListCode= outOrder.outStockOrder

   

GO






 /**
 ** 立体库库存
 **/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_srmStock') 
   DROP VIEW v_srmStock 
GO 
CREATE VIEW v_srmStock
AS

SELECT   dbo.productStock.stockId, dbo.position.positionName, dbo.position.isLock, dbo.position.isfree, 
                dbo.material.materialCode, dbo.material.materialName, dbo.productStock.quantity, dbo.productStock.taskType, 
                dbo.productStock.status, dbo.productStock.createTime, dbo.productStock.updateTime, dbo.position.enable, 
                dbo.productStock.positionId, dbo.productStock.materialId, dbo.productStock.productCode, 
                dbo.productStock.containerCode, dbo.position.positionType, dbo.productStock.supplier, dbo.material.version
FROM      dbo.position LEFT  JOIN
                dbo.productStock ON dbo.position.positionId = dbo.productStock.positionId LEFT JOIN
                dbo.material ON dbo.productStock.materialId = dbo.material.materialId
   WHERE [position].row=1 OR  [position].row=2

   

GO



/**
 ** 立体库库存明细（精确到流水号）
 **/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_srmStock_details') 
   DROP VIEW v_srmStock_details 
GO 
CREATE VIEW v_srmStock_details
AS

SELECT  prodList.productName,prodList.serialNumber,

    dbo.productStock.stockId, dbo.position.positionName, dbo.position.isLock, dbo.position.isfree, 
                dbo.material.materialCode, dbo.material.materialName, dbo.productStock.quantity, dbo.productStock.taskType, 
                dbo.productStock.status, dbo.productStock.createTime, dbo.productStock.updateTime, dbo.position.enable, 
                dbo.productStock.positionId, dbo.productStock.materialId, dbo.productStock.productCode, 
                dbo.productStock.containerCode, dbo.position.positionType, dbo.productStock.supplier, dbo.material.version
FROM      dbo.position LEFT  JOIN
                dbo.productStock ON dbo.position.positionId = dbo.productStock.positionId 
				LEFT JOIN productStockList as prodList on prodList.stockId=dbo.productStock.positionId
				LEFT JOIN dbo.material ON dbo.productStock.materialId = dbo.material.materialId
   WHERE [position].row=1 OR  [position].row=2

   

GO