:用友U810中現(xiàn)存量查詢的SQL
:/*在常規(guī)情況下,現(xiàn)存量不能按照部門查詢,如想對(duì)多個(gè)倉(cāng)庫(kù)同時(shí)查詢有點(diǎn)麻煩,這條SQL可以解決*/
SELECT dbo.Warehouse.cWhCode AS 倉(cāng)庫(kù)編碼,dbo.Warehouse.cWhName AS 倉(cāng)庫(kù)名稱, dbo.Warehouse.cDepCode AS 部門編碼,Dep.cDepName AS 部門名稱,
dbo.CurrentStock.cInvCode AS 存貨編碼,
dbo.Inventory.cInvAddCode AS 存貨代碼,dbo.InventoryClass.cinvcname AS 存貨分類,dbo.Inventory.cInvStd AS 規(guī)格型號(hào),
dbo.Inventory.cInvName AS 存貨名稱,Unit.cComUnitName AS 計(jì)量單位,
dbo.Inventory.cInvDefine6 AS 自定義項(xiàng)6,dbo.Inventory.iInvSPrice AS 參考成本,
SUM(ISNULL(dbo.CurrentStock.iQuantity, 0)) AS 現(xiàn)存量,
dbo.Inventory.iInvSPrice*SUM(ISNULL(dbo.CurrentStock.iQuantity, 0)) AS 成本價(jià)值
FROM dbo.CurrentStock
INNER JOIN dbo.Inventory ON dbo.CurrentStock.cInvCode = dbo.Inventory.cInvCode
INNER JOIN dbo.Warehouse ON dbo.Warehouse.cWhCode = dbo.CurrentStock.cWhCode
INNER JOIN dbo.ComputationUnit Unit ON dbo.Inventory.cComUnitCode = Unit.cComunitCode
INNER JOIN InventoryClass ON left(Inventory.cInvCCode,6) = InventoryClass.cInvCCode
INNER JOIN dbo.Department Dep ON dbo.Warehouse.cDepCode = Dep.cDepCode
GROUP BY dbo.Warehouse.cWhCode,dbo.Warehouse.cWhName, dbo.Warehouse.cDepCode, Dep.cDepName,
dbo.CurrentStock.cInvCode,
dbo.Inventory.cInvAddCode,dbo.InventoryClass.cinvcname,
dbo.Inventory.cInvName,Unit.cComUnitName,
dbo.Inventory.cInvDefine6,dbo.Inventory.iInvSPrice,
dbo.Inventory.cInvStd,dbo.CurrentStock.cBatch, dbo.CurrentStock.cFree1
having SUM(ISNULL(dbo.CurrentStock.iQuantity, 0)) >0
/*
上海企通數(shù)字科技有限公司,用心打造企業(yè)數(shù)字化!連續(xù)13年蟬聯(lián)用友全國(guó)TOP10服務(wù)商,專業(yè)銷售服務(wù)暢捷通、用友u8、用友NC等軟件,歡迎咨詢,咨詢熱線:400-820-8720,網(wǎng)址:http://www.cotong.com/
上海企通數(shù)字科技有限公司,成立于2003年1月,企通成立20年來(lái)始終專注國(guó)內(nèi)企業(yè)數(shù)字化服務(wù),是一家以管理軟件和互聯(lián)網(wǎng)應(yīng)用的開(kāi)發(fā)、咨詢、營(yíng)銷、培訓(xùn)、實(shí)施、服務(wù)于一體的管理數(shù)字化服務(wù)公司。以“推動(dòng)客戶實(shí)現(xiàn)行業(yè)領(lǐng)先”為己任,以幫助客戶“增加收入、提升效率、降低成本、控制風(fēng)險(xiǎn)”為目標(biāo),為國(guó)內(nèi)企業(yè)提供數(shù)字化建設(shè)...
400-780-9880
marketing@cotong.com
在線服務(wù)
網(wǎng)站地圖
新浪博客