2222

schangxiang@126.com

2025-09-02 6bd89520e09dc1c2d3fab72a80c3d01f2df93490

 sql/DBScript/01_DDL/03_DDL_VIEW_YunNeiDongLi.sql

@@ -90,17 +90,13 @@
GO






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

SELECT   dbo.productStock.stockId, dbo.position.positionName, dbo.position.isLock, dbo.position.isfree, 
@@ -108,38 +104,44 @@
                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
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
   WHERE [position].row=1 OR  [position].row=2

   

GO









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

SELECT  prodList.productName,prodList.serialNumber,
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      dbo.position LEFT  JOIN
                dbo.productStock ON dbo.position.positionId = dbo.productStock.positionId 
            LEFT JOIN productStockList as prodList on prodList.stockId=dbo.productStock.positionId
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