用友U8现存量查询（现存数量/冻结数量/可用数量）

用友U8现存量查询（现存数量/冻结数量/可用数量）

SELECT 
    CS.cInvCode AS 存货编码,
    I.cInvAddCode AS 存货附加编码,
    I.cInvName AS 存货名称,
    I.cInvStd AS 规格型号,
    I.cInvCCode AS 存货分类编码,
    IC.cInvCName AS 存货分类名称,
    W.cWhCode AS 仓库编码,
    W.cWhName AS 仓库名称,
    CU_M.cComUnitName AS 主计量单位,
	/*
    CASE
        WHEN I.iGroupType = 0 THEN NULL
        WHEN I.iGroupType = 2 THEN CU_A.cComUnitName
        WHEN I.iGroupType = 1 THEN CU_G.cComUnitName
    END AS 辅计量单位,
    CONVERT(NVARCHAR(38),
        CONVERT(DECIMAL(38, 2),
            CASE
                WHEN I.iGroupType = 0 THEN NULL
                WHEN I.iGroupType = 2 THEN 
                    CASE 
                        WHEN CS.iQuantity = 0.0 OR CS.inum = 0.0 THEN NULL 
                        ELSE CS.iQuantity / CS.inum 
                    END
                WHEN I.iGroupType = 1 THEN CU_G.iChangRate
            END
        )
    ) AS 换算率,
	*/
    CS.cBatch AS 批号,
    CAST(CS.dMdate AS DATE) AS 生产日期,
    CAST(CS.dVDate AS DATE) AS 失效日期,
    CAST(CS.cExpirationdate AS DATE) AS 有效期至,
    --ISNULL(E1.EnumName, N'') AS 有效期推算方式,
    --CS.cMassUnit AS 保质期单位,
	ISNULL(E.EnumName, N'') AS 保质期单位,
    CS.iMassDate AS 保质期,

    -- 数量字段（主单位）
    CS.iQuantity AS 现存数量,
    CASE
        WHEN CS.bStopFlag = 1 OR CS.BGSPSTOP = 1 THEN CS.iQuantity
        ELSE ISNULL(CS.fStopQuantity, 0)
    END AS 冻结数量,
    CASE
        WHEN CS.bStopFlag = 1 OR CS.BGSPSTOP = 1 THEN 0
        ELSE ISNULL(CS.iQuantity, 0) - ISNULL(CS.fStopQuantity, 0)
    END AS 可用数量

    -- 冻结件数等信息
    --CAST(CS.fStopNum AS INT) AS 冻结件数,
    --CAST(CS.fAvaQuantity AS INT) AS 可用数量
    --CS.dLastCheckDate AS 上次盘点日期
FROM 
    dbo.CurrentStock CS
LEFT JOIN 
    dbo.Inventory I ON CS.cInvCode = I.cInvCode
LEFT JOIN 
    dbo.InventoryClass IC ON IC.cInvCCode = I.cInvCCode
LEFT JOIN 
    dbo.Warehouse W ON W.cWhCode = CS.cWhCode
LEFT JOIN 
    dbo.ComputationUnit CU_M ON I.cComUnitCode = CU_M.cComunitCode
LEFT JOIN 
    dbo.ComputationUnit CU_A ON I.cAssComUnitCode = CU_A.cComunitCode
LEFT JOIN 
    dbo.ComputationUnit CU_G ON I.cSTComUnitCode = CU_G.cComunitCode
LEFT JOIN 
    dbo.v_aa_enum E1 ON E1.EnumCode = ISNULL(CS.iexpiratdatecalcu, 0) AND E1.EnumType = N'SCM.ExpiratDateCalcu'
LEFT JOIN 
    dbo.v_aa_enum E ON E.EnumCode = CONVERT(NCHAR, CS.cMassUnit) AND E.EnumType = N'ST.MassUnit'
WHERE 
    CS.cInvCode LIKE '010%' AND CS.iQuantity <> 0
GROUP BY
    CS.cInvCode,
    I.cInvAddCode,
    I.cInvName,
    I.cInvStd,
    I.cInvCCode,
    IC.cInvCName,
    W.cWhCode,
    W.cWhName,
    CU_M.cComUnitName,
    I.iGroupType,
    CU_A.cComUnitName,
    CU_G.cComUnitName,
    CU_G.iChangRate,
    CS.iQuantity,
    CS.inum,
    CS.cBatch,
    CS.dMdate,
    CS.dVDate,
    CS.cExpirationdate,
    E1.EnumName,
    CS.cMassUnit,
    CS.iMassDate,
    CS.fStopQuantity,
    CS.fStopNum,
    CS.fAvaQuantity,
    E.EnumName,
    CS.bStopFlag,
    CS.BGSPSTOP,
    CS.dLastCheckDate

本内容仅供参考，请谨慎操作，造成数据异常，与此内容无关。