
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='VproductStock') 
   DROP VIEW VproductStock 
GO 
CREATE VIEW VproductStock
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 INNER JOIN
                dbo.productStock ON dbo.position.positionId = dbo.productStock.positionId INNER JOIN
                dbo.material ON dbo.productStock.materialId = dbo.material.materialId
   

GO









/**
 ** 立体库库存明细（精确到流水号） [Editby shaocx,2025-09-02]
 **/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_srmStockdetails') 
   DROP VIEW v_srmStockdetails 
GO 
CREATE VIEW v_srmStockdetails
AS

SELECT  prodList.stockListId,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      productStockList as prodList 
				LEFT JOIN dbo.position on prodList.stockId=dbo.position.positionId
			    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
    and prodList.stockListId is not null

   

GO