物料收發存彙總表
# FastERP 進銷存模板開發 物料收發存彙總表
物料收發存彙總表位於功能表欄中的 [進銷存]-[物料報表]-[物料收發存彙總表]。使用的模組型別為預存程序報表窗體，關於預存程序報表窗體的建立模式請參考 預存程序報表。
# 1. 模組設計
登陸FastERP，打開[主頁]->[平臺設計]->[系統設計]->[模組設計]。在模組設計列表左側中找到 進銷存-物料管理-統計報表-物料收發存彙總表，單擊選擇，點選功能表欄中的 [編輯] 按鈕。打開 模組設計 界面。
界面中需勾選 打開數據、允許導出表格 與 允許定製表格。
在窗體設計-數據集資訊界面中，設定數據集的基礎資訊，主控制元件需設定為 dbGridBand。條件程式碼處的內容可在查詢條件設定完成後進行設定。構建的SQL語句如下：
declare @FDate1 datetime,
@FDate2 datetime,
@FBillID int,
@FStockID int,
@FItemCode varchar(30),
@FItemName varchar(50),
@FBatchNO varchar(50)
select @FDate1 = :FDate1, @FDate2 = :FDate2, @FStockID = :FStockID,
@FItemCode = :FItemCode,
@FItemName = :FItemName,
@FBatchNO = :FBatchNO
select @FItemCode = isnull(@FItemCode,''),
@FItemName = isnull(@FItemName,''),
@FBatchNO = isnull(@FBatchNO,'')
create table #tmp1(
FItemID int,
FItemCode varchar(30),
FItemName varchar(100),
FUnitID int,
FUnitCode varchar(30),
FUnitName varchar(50),
FItemSpec varchar(100),
FBatchNo varchar(100),
FBegQtyPiece float default 0,
FBegQty float default 0,
FBegAmount float default 0,
FBegTaxAmount float default 0,
FInQtyPiece float default 0,
FInQty float default 0,
FInAmount float default 0,
FInTaxAmount float default 0,
FOutQtyPiece float default 0,
FOutQty float default 0,
FOutAmount float default 0,
FOutTaxAmount float default 0,
FEndQtyPiece float default 0,
FEndQty float default 0, -- 期末結存 = 期初 + 本期入庫(不包括盤盈盤虧單) - 本期出庫(不包括盤盈盤虧單)
FEndAmount float default 0,
FEndTaxAmount float default 0,
FCheckQtyPiece float default 0,
FCheckQty float default 0, --盤點數 = 期初 + 本期入庫 - 本期出庫
FCheckAmount float default 0,
FCheckTaxAmount float default 0,
FOverQtyPiece float default 0,
FOverQty float default 0, --盤盈數量
FOverAmount float default 0,
FOverTaxAmount float default 0,
FLowQtyPiece float default 0, --盤虧數量
FLowQty float default 0, --盤虧數量
FLowAmount float default 0,
FLowTaxAmount float default 0
)
--期初 + 本期入庫(不含本期盤點單) - 本期出庫(不含本期盤點單) = 期末結存
--期末結存 + 盤盈入庫 - 盤虧出庫 = 盤點數
insert into #tmp1(FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
FBegQtyPiece, FBegQty, FBegAmount, FBegTaxAmount)
select FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
isnull(sum(FInQtyPiece), 0) - isnull(sum(FOutQtyPiece), 0),
isnull(sum(FInQty), 0) - isnull(sum(FOutQty), 0),
isnull(sum(FInAmount), 0) - isnull(sum(FOutAmount), 0),
isnull(sum(FInTaxAmount), 0) - isnull(sum(FOutTaxAmount), 0)
from V_MatStockDetail
where FDate < @FDate1
and (FStockID = @FStockID or 0 = @FStockID)
and (isnull(FItemCode, '') = @FItemCode or @FItemCode = '')
and (isnull(FBatchNO, '') = @FBatchNO or @FBatchNO = '')
and (isnull(FItemName,'') LIKE '%' + @FItemName + '%'or @FItemName ='')
group by FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo
insert into #tmp1(FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
FInQtyPiece ,FInQty, FInAmount, FInTaxAmount, FOutQtyPiece ,FOutQty, FOutAmount, FOutTaxAmount)
select FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
isnull(sum(FInQtyPiece), 0), isnull(sum(FInQty), 0), isnull(sum(FInAmount), 0), isnull(sum(FInTaxAmount), 0),
isnull(sum(FOutQtyPiece), 0),isnull(sum(FOutQty), 0), isnull(sum(FOutAmount), 0), isnull(sum(FOutTaxAmount), 0)
from V_MatStockDetail
where FBillID not in (24022002, 24022003)
and FDate between @FDate1 and @FDate2
and (FStockID = @FStockID or 0 = @FStockID)
and (isnull(FItemCode, '') = @FItemCode or @FItemCode = '')
and (isnull(FBatchNO, '') = @FBatchNO or @FBatchNO = '')
and (isnull(FItemName,'') LIKE '%' + @FItemName + '%'or @FItemName ='')
group by FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo
insert into #tmp1(FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
FOverQtyPiece ,FOverQty, FOverAmount, FOverTaxAmount, FLowQtyPiece, FLowQty, FLowAmount, FLowTaxAmount)
select FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
isnull(sum(FInQtyPiece), 0), isnull(sum(FInQty), 0), isnull(sum(FInAmount), 0), isnull(sum(FInTaxAmount), 0),
isnull(sum(FOutQtyPiece), 0),isnull(sum(FOutQty), 0), isnull(sum(FOutAmount), 0), isnull(sum(FOutTaxAmount), 0)
from V_MatStockDetail
where FBillID in (24022002, 24022003)
and FDate between @FDate1 and @FDate2
and (FStockID = @FStockID or 0 = @FStockID)
and (isnull(FItemCode, '') = @FItemCode or @FItemCode = '')
and (isnull(FBatchNO, '') = @FBatchNO or @FBatchNO = '')
and (isnull(FItemName,'') LIKE '%' + @FItemName + '%'or @FItemName ='')
group by FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo
delete #tmp1
where FBegQtyPiece=0 and FBegQty = 0 and FBegAmount = 0 and FBegTaxAmount = 0
and FInQtyPiece = 0 and FInQty = 0 and FInAmount = 0 and FInTaxAmount = 0
and FOutQtyPiece = 0 and FOutQty = 0 and FOutAmount = 0 and FOutTaxAmount = 0
and FOverQtyPiece = 0 and FOverQty = 0 and FOverAmount = 0 and FOverTaxAmount = 0
and FLowQtyPiece = 0 and FLowQty = 0 and FLowAmount = 0 and FLowTaxAmount = 0
----插入期末結存的件數/數量
update #tmp1 set
FEndQtyPiece = isnull(FBegQtyPiece, 0) + isnull(FInQtyPiece, 0) - isnull(FOutQtyPiece, 0),
FEndQty = isnull(FBegQty, 0) + isnull(FInQty, 0) - isnull(FOutQty, 0),
FEndAmount = isnull(FBegAmount, 0) + isnull(FInAmount, 0) - isnull(FOutAmount, 0),
FEndTaxAmount = isnull(FBegTaxAmount, 0) + isnull(FInTaxAmount, 0) - isnull(FOutTaxAmount, 0),
FCheckQtyPiece = isnull(FBegQtyPiece, 0) + isnull(FInQtyPiece, 0) - isnull(FOutQtyPiece, 0) + isnull(FOverQtyPiece, 0) - isnull(FLowQtyPiece, 0),
FCheckQty = isnull(FBegQty, 0) + isnull(FInQty, 0) - isnull(FOutQty, 0) + isnull(FOverQty, 0) - isnull(FLowQty, 0),
FCheckAmount = isnull(FBegAmount, 0) + isnull(FInAmount, 0) - isnull(FOutAmount, 0) + isnull(FOverAmount, 0) - isnull(FLowAmount, 0),
FCheckTaxAmount = isnull(FBegTaxAmount, 0) + isnull(FInTaxAmount, 0) - isnull(FOutTaxAmount, 0) + isnull(FOverTaxAmount, 0) - isnull(FLowTaxAmount, 0)
select
a.FItemID, a.FItemCode, a.FItemName,a.FItemSpec, a.FUnitID, a.FUnitCode, a.FUnitName,
Sum(a.FBegQtyPiece) as FBegQtyPiece,Sum(a.FBegQty) as FBegQty,Sum(a.FBegAmount) as FBegAmount,Sum(a.FBegTaxAmount) as FBegTaxAmount,
Sum(a.FInQtyPiece) as FInQtyPiece,Sum(a.FInQty) as FInQty, Sum(a.FInAmount) as FInAmount, Sum(a.FInTaxAmount) as FInTaxAmount,
Sum(a.FOutQtyPiece) as FOutQtyPiece,Sum(a.FOutQty) as FOutQty,Sum(a.FOutAmount) as FOutAmount, Sum(a.FOutTaxAmount) as FOutTaxAmount,
Sum(a.FEndQtyPiece) as FEndQtyPiece,Sum(a.FEndQty) as FEndQty, Sum(a.FEndAmount) as FEndAmount, Sum(a.FEndTaxAmount) as FEndTaxAmount,
Sum(a.FOverQtyPiece) as FOverQtyPiece,Sum(a.FOverQty) as FOverQty,Sum(a.FOverAmount) as FOverAmount, Sum(a.FOverTaxAmount) as FOverTaxAmount,
Sum(a.FLowQtyPiece) as FLowQtyPiece,Sum(a.FLowQty) as FLowQty,Sum(a.FLowAmount) as FLowAmount, Sum(a.FLowTaxAmount) as FLowTaxAmount,
Sum(a.FCheckQtyPiece) as FCheckQtyPiece,Sum(a.FCheckQty) as FCheckQty,Sum(a.FCheckAmount) as FCheckAmount, Sum(a.FCheckTaxAmount) as FCheckTaxAmount
from #tmp1 a
left outer join Basic_Item t1 on t1.FInterID = a.FItemID
Group by a.FItemID, a.FItemCode, a.FItemName,a.FItemSpec, a.FUnitID, a.FUnitCode, a.FUnitName
drop table #tmp1
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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
在欄位資訊選項卡中，點選 [載入欄位]，將SQL語句構造的數據集欄位資訊載入至列表中。分別設定各欄位的欄位標籤與分組名。按照圖中所示的內容進行設定。
查詢條件處按照上圖所示的內容進行設定。
其中 FDate1 需設定預設值型別為 VAR_DATEBEGIN，FDate2 需設定預設值型別為 VAR_DATEEND。
設定完成後點選 [儲存] 按鈕儲存模組設定。將查詢條件的條件程式碼填寫至數據集資訊中的條件程式碼欄位處。
# 2. 設計佈局
點選模組設計功能表欄中的 設計佈局-預存程序報表窗體，在FastERP的主視窗中會顯示一個新建立的標籤頁，點選功能表欄中的 設計-設計佈局，會打開自定義視窗，其中會顯示可使用的元件。將快捷查詢條件與表格分別拖動放置於界面中。使用滑鼠右鍵調整取消部分控制元件的標題顯示。最終設定完成的界面顯示如下：
設定完成後，點選功能表欄中的 [設計]-[儲存佈局]，在彈出的確認儲存佈局視窗中選擇 [確認] 按鈕，等待儲存完成時彈出 儲存成功 的提示，至此完成佈局的儲存。
# 3. 設計功能表
關閉模組設計視窗，點選功能表欄中的 [平臺設計]-[功能表設計]，打開功能表設計界面。示例中的功能表建立于進銷存-物料報表-物料收發存彙總表中，選擇這個功能表項，注意功能表項的設定中模組型別需設定為 打開預存程序窗體，模組編碼選擇建立的模組編碼ReportInvStockSFC。儲存功能表設定。
儲存完成後，可在功能表欄中 [進銷存]-[物料報表]-[物料收發存彙總表] 處打開模組。