From 4d951f910e10fdfac9b927e8d7f54f69204febeb Mon Sep 17 00:00:00 2001
From: zhangchao <zc857179121@qq.com>
Date: 星期六, 23 十一月 2024 09:53:08 +0800
Subject: [PATCH] 库位利用率接口

---
 src/main/java/asr_show_diff.sql |   86 +++++++++++++++++++++++++------------------
 1 files changed, 50 insertions(+), 36 deletions(-)

diff --git a/src/main/java/asr_show_diff.sql b/src/main/java/asr_show_diff.sql
index f06fdb0..4282ce4 100644
--- a/src/main/java/asr_show_diff.sql
+++ b/src/main/java/asr_show_diff.sql
@@ -1,44 +1,58 @@
 SELECT SUM
-       ( asrsQty ) asrsQty,
-       SUM ( erpQty ) erpQty,
-       matnr,
-       specs,
-       maktx
+	( asrsQty ) asrsQty,
+	SUM ( erpQty ) erpQty,
+	matnr,
+	maktx,
+	MIN ( pgNo ) pgNo,
+	MIN ( outOrderNo ) outOrderNo,
+	MIN ( batch ) batch,
+	model,
+	MIN ( specs ) specs
 FROM
-    (
-        SELECT CAST
-               ( SUM ( anfme ) AS INT ) asrsQty,
-               0 AS erpQty,
-               matnr,
-               specs,
-               maktx
-        FROM
-            asr_loc_detl
-        GROUP BY
-            matnr,
-            specs,
-            maktx UNION ALL
-        SELECT
-            0,
-            CAST ( SUM ( numis ) AS INT ),
-            itemid,
-            itemcode,
-            itemname
-        FROM
-            (
-                SELECT
-                    *
-                FROM
-                    OPENROWSET ( 'SQLOLEDB', '192.168.1.147,1800'; 'XGMLK'; 'xunda0413', XDLinkLK.dbo.vwErpLKStorage )
-		WHERE
-			LKName = '涓壃' 
-		) a 
+	(
+	SELECT CAST
+		( SUM ( anfme ) AS INT ) asrsQty,
+		0 AS erpQty,	matnr,
+		model,
+
+		maktx,
+		pg_no AS pgNo,
+		out_order_no AS outOrderNo,
+		batch,
+		specs
+	FROM
+		asr_loc_detl
+	GROUP BY
+		matnr,
+		maktx,
+		pg_no,
+		out_order_no,
+		batch,
+		model,
+		specs UNION ALL
+	SELECT
+		0,
+		CAST ( SUM ( numis ) AS INT ),
+		itemid,
+		itemcode,
+		itemname,
+		'' AS pgNo,
+		'' AS outOrderNo,
+		itembatch AS batch,
+		itemSpc specs
+	FROM
+		( SELECT * FROM OPENROWSET ( 'SQLOLEDB', '192.168.1.147,1800'; 'XGMLK'; 'xunda0413', XDLinkLK.dbo.vwErpLKStorage ) WHERE LKName = '涓壃浜屾湡' ) a
 	GROUP BY
 		itemid,
 		itemcode,
-		itemname 
-	) b WHERE 1=1 and matnr not in (select matnr from man_order_detl)
+		itemname,
+		itembatch,
+		itemSpc
+	) b
+WHERE
+	1 = 1
+	AND matnr NOT IN ( SELECT matnr FROM man_order_detl )
 GROUP BY
 	matnr,
-	specs,
+	model,
 	maktx
\ No newline at end of file

--
Gitblit v1.9.1