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
| CREATE TABLE [man_monthly_settle] (
| [id] bigint NOT NULL IDENTITY(1,1),
| [settle_no] varchar(255) NOT NULL,
| [start_date] datetime NOT NULL,
| [end_date] datetime NOT NULL,
| [status] int NOT NULL DEFAULT 0,
| [total_in_qty] decimal(24,9) DEFAULT 0.00,
| [total_out_qty] decimal(24,9) DEFAULT 0.00,
| [total_materials] int DEFAULT 0,
| [memo] varchar(255) DEFAULT NULL,
| [create_by] bigint DEFAULT NULL,
| [create_time] datetime DEFAULT NULL,
| [update_by] bigint DEFAULT NULL,
| [update_time] datetime DEFAULT NULL,
| [is_deleted] int NOT NULL DEFAULT 0,
| PRIMARY KEY ([id]),
| CONSTRAINT [uk_settle_no] UNIQUE ([settle_no])
| );
| CREATE INDEX [idx_start_date] ON [man_monthly_settle] ([start_date]);
| CREATE INDEX [idx_end_date] ON [man_monthly_settle] ([end_date]);
| CREATE INDEX [idx_status] ON [man_monthly_settle] ([status]);
|
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结主表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'id';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结编号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'settle_no';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'起始日期', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'start_date';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'结束日期', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'end_date';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'状态 0:待月结 1:已月结', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'status';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'总入库数量', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'total_in_qty';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'总出库数量', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'total_out_qty';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'物料种类数', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'total_materials';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'备注', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'memo';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'创建人员', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'create_by';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'创建时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'create_time';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'修改人员', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'update_by';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'修改时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'update_time';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'删除标记 0:未删除 1:已删除', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle', @level2type = N'COLUMN', @level2name = N'is_deleted';
|
| CREATE TABLE [man_monthly_settle_detail] (
| [id] bigint NOT NULL IDENTITY(1,1),
| [settle_id] bigint NOT NULL,
| [settle_no] varchar(255) NOT NULL,
| [matnr] varchar(255) NOT NULL,
| [batch] varchar(255) DEFAULT NULL,
| [maktx] varchar(255) DEFAULT NULL,
| [brand] varchar(255) DEFAULT NULL,
| [beginning_qty] decimal(24,9) NOT NULL,
| [in_qty] decimal(24,9) NOT NULL,
| [out_qty] decimal(24,9) NOT NULL,
| [ending_qty] decimal(24,9) NOT NULL,
| [stock_qty] decimal(24,9) NOT NULL,
| [diff_qty] decimal(24,9) NOT NULL,
| [create_time] datetime DEFAULT NULL,
| [is_deleted] int NOT NULL DEFAULT 0,
| PRIMARY KEY ([id])
| );
| CREATE INDEX [idx_settle_id] ON [man_monthly_settle_detail] ([settle_id]);
| CREATE INDEX [idx_settle_no] ON [man_monthly_settle_detail] ([settle_no]);
| CREATE INDEX [idx_matnr] ON [man_monthly_settle_detail] ([matnr]);
|
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结明细表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'id';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结主表ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'settle_id';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结编号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'settle_no';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'物料编码', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'matnr';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'批次（订单明细批次）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'batch';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'物料名称（月结时从订单明细表获取）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'maktx';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'品牌（月结时从订单明细表获取）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'brand';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'期初库存（上期结余）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'beginning_qty';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'本期入库数量', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'in_qty';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'本期出库数量', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'out_qty';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'期末库存（期初+入库-出库）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'ending_qty';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'当前实际库存数量', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'stock_qty';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'差异数量（期末库存-期初库存）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'diff_qty';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'创建时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'create_time';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'删除标记 0:未删除 1:已删除', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_monthly_settle_detail', @level2type = N'COLUMN', @level2name = N'is_deleted';
|
| ALTER TABLE [man_order_log_pakin] ADD [monthly_settle_id] bigint DEFAULT NULL;
| ALTER TABLE [man_order_log_pakin] ADD [monthly_settle_no] varchar(255) DEFAULT NULL;
| CREATE INDEX [idx_monthly_settle_id] ON [man_order_log_pakin] ([monthly_settle_id]);
|
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_log_pakin', @level2type = N'COLUMN', @level2name = N'monthly_settle_id';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结编号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_log_pakin', @level2type = N'COLUMN', @level2name = N'monthly_settle_no';
|
| ALTER TABLE [man_order_log_pakout] ADD [monthly_settle_id] bigint DEFAULT NULL;
| ALTER TABLE [man_order_log_pakout] ADD [monthly_settle_no] varchar(255) DEFAULT NULL;
| CREATE INDEX [idx_monthly_settle_id] ON [man_order_log_pakout] ([monthly_settle_id]);
|
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_log_pakout', @level2type = N'COLUMN', @level2name = N'monthly_settle_id';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结编号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_log_pakout', @level2type = N'COLUMN', @level2name = N'monthly_settle_no';
|
| -- 为 man_order_pakin 表添加月结参数字段
| ALTER TABLE [man_order_pakin] ADD [monthly_settle_id] bigint DEFAULT NULL;
| ALTER TABLE [man_order_pakin] ADD [monthly_settle_no] varchar(255) DEFAULT NULL;
| CREATE INDEX [idx_monthly_settle_id] ON [man_order_pakin] ([monthly_settle_id]);
|
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_pakin', @level2type = N'COLUMN', @level2name = N'monthly_settle_id';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结编号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_pakin', @level2type = N'COLUMN', @level2name = N'monthly_settle_no';
|
| -- 为 man_order_pakout 表添加月结参数字段
| ALTER TABLE [man_order_pakout] ADD [monthly_settle_id] bigint DEFAULT NULL;
| ALTER TABLE [man_order_pakout] ADD [monthly_settle_no] varchar(255) DEFAULT NULL;
| CREATE INDEX [idx_monthly_settle_id] ON [man_order_pakout] ([monthly_settle_id]);
|
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_pakout', @level2type = N'COLUMN', @level2name = N'monthly_settle_id';
| EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'月结编号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'man_order_pakout', @level2type = N'COLUMN', @level2name = N'monthly_settle_no';
|
|
