1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| SELECT SUM
| ( 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,
| 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,
| itembatch,
| itemSpc
| ) b
| WHERE
| 1 = 1
| AND matnr NOT IN ( SELECT matnr FROM man_order_detl )
| GROUP BY
| matnr,
| model,
| maktx
|
|
