仓库管理

简单的仓库管理—进出

从工业企业进销存是从原材料的采购(进)->到入库(存)->领料加工->产品入库(存)->销售(销)的动态管理过程 —— [ MBA百科 ]

主要思想

其实我并没做销售这一块,但通过这一段时间的学习,我认为仓库管理最重要的几点是:

• 材料跟踪 : 材料从哪来到哪去
• 成本跟踪 : 产品生产成本构成

---

具体程序:

• 数据库设计（部分字段）

数据表名	字段名	作用
Stroe（出入库单号）	Give_Date	交货日期
Stroea（出入库明细）	Acc_Qty	接受数量
Stroea（出入库明细）	Sign_Int	判断出入库
StroeCheck（仓库盘查）	Check_Date	检查时间
StroeCheck（仓库盘查）	FCheck	审查
StroeCheck_Temp（盘查具体材料）	Mat_No，Check_Date	主键

• 原材料出入库：

领料出库库存限制：后台要写触发，如果出库数量大于实际的库存数量，要提示错误，回滚：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Trigger [dbo].[CalQty]
ON [dbo].[Te_Storea]
AFTER insert , update
as
BEGIN
 DECLARE @Qty decimal(18,2)
 DECLARE @StoreQty decimal(18,2)
 DECLARE @ErrorStr varchar(50)
 DECLARE @Sign int
 set @Qty = 0
 set @StoreQty = 0
 select @Qty=Acc_Qty,@Sign=Sign_Int from inserted
 select @ErrorStr=Mat_No from inserted
 if (@Qty > 0) and (@Sign<0)
 begin
  select @StoreQty = cast(SUM(Sign_Int*Acc_Qty) as decimal(18,2)) 
  from Te_Storea
  group by Te_Storea.Mat_No
  having Te_Storea.Mat_No = @ErrorStr
  if (@StoreQty < 0)
    BEGIN
        set @ErrorStr = '它的庫存數量只有' + cast((@StoreQty + @Qty) as varchar)
        RAISERROR (@ErrorStr, 16, 1)
        ROLLBACK TRAN
        RETURN
    END
 end
END

• 仓库盘查点(每月一号)：

到达盘查点时，数据库已经安排了作业放进了排程，会自动执行，部分后台代码（测试代码有错误）：

DECLARE @Check_Date  datetime
SET @Check_Date = '2017-07-01 00:00:00'
select top 1 @Check_Date = Check_Date from dbo.Te_StoreCheckTemp order by Check_Date desc
DECLARE @Now_Date  datetime
SET @Now_Date = '2017-09-01 00:00:00'

insert  dbo.Te_StoreCheckTemp(Mat_No, Mat_Name, Vendor, Check_Date, Store_Sqty, Store_SAccount, Store_IQty,    Store_OQty, Store_Eqty, Store_EAccount)
select Db_MatNumber.Mat_No,Db_MatNumber.Mat_Name,(CASE WHEN store.Vendor IS NULL THEN Storecheck.Vendor ELSE store.Vendor END),@Now_Date,Store_Eqty,Store_EAccount,INumber,ONumber,
(CASE WHEN Store_Eqty IS NULL THEN 0 ELSE Store_Eqty END)+(CASE WHEN StoNumber IS NULL THEN 0 ELSE StoNumber END),
(CASE WHEN Store_EAccount IS NULL THEN 0 ELSE Store_EAccount END)+(CASE WHEN StoAccount IS NULL THEN 0 ELSE StoAccount END)
from 
     (select Mat_No,Vendor,StoNumber,StoAccount
      from (select Mat_No,Vendor,SUM(Sign_Int*Acc_Qty) as StoNumber,cast(SUM(Sign_Int*Acc_Qty*Price) as decimal(18,2)) as StoAccount
           from 
          (select Te_Storea.* 
           from Te_Storea, Te_Store 
           where Te_Store.Give_Date < @Now_Date
                 and Te_Store.Give_Date > @Check_Date
                 and Te_Storea.Str_Auto = Te_Store.Str_Auto) as a
      group by Vendor,Mat_No) as a) as store --獲取期末庫存
 FULL JOIN (select * from dbo.Te_StoreCheckTemp where Check_Date = @Check_Date) as Storecheck -- 獲取期初
 ON Storecheck.Mat_No = store.Mat_No
 FULL JOIN (  select Mat_No,Vendor,SUM(Acc_Qty) as ONumber 
              from 
                  (select Te_Storea.* 
                   from Te_Storea, Te_Store 
                   where Te_Store.Give_Date < @Now_Date
                         and Te_Store.Give_Date > @Check_Date
                         and Te_Storea.Str_Auto = Te_Store.Str_Auto
                         and Sign_Int < 0 ) as b
               group by Vendor,Mat_No) as output --期間出庫數量
  ON output.Mat_No = store.Mat_No
   FULL JOIN (  select Mat_No,Vendor,SUM(Acc_Qty) as INumber 
              from 
                  (select Te_Storea.* 
                   from Te_Storea, Te_Store 
                   where Te_Store.Give_Date < @Now_Date
                         and Te_Store.Give_Date > @Check_Date
                         and Te_Storea.Str_Auto = Te_Store.Str_Auto
                         and Sign_Int > 0 ) as b
               group by Vendor,Mat_No) as input --期間入庫數量
  ON input.Mat_No = store.Mat_No
  LEFT  JOIN Db_MatNumber
  ON store.Mat_No = Db_MatNumber.Mat_No
     OR  Storecheck.Mat_No = Db_MatNumber.Mat_No