方案设计
每日凌晨获取当前即时库存明细的库存作为昨日的库存结余记录下来。
详细设计
创建存储过程
CREATE PROC XXXX_P_STK_HisInventoryInfo AS BEGIN INSERT INTO XXXX_T_STK_HisInventory (FID,F_XXXX_Date,FBILLNO,FDOCUMENTSTATUS,F_XXXX_StockOrgId,F_XXXX_StockId ,F_XXXX_MATERIALID,F_XXXX_StockQty,F_XXXX_SysLockQty ,F_XXXX_CreatorId,F_XXXX_CreateDate,F_XXXX_ModifierId,F_XXXX_ModifyDate,F_XXXX_ApproverId,F_XXXX_ApproveDate) SELECT ROW_NUMBER() OVER ( ORDER BY T.FMATERIALID,T.FSTOCKID ) +(SELECT ISNULL(MAX(FID),0) FROM XXXX_T_STK_HisInventory) AS FID, DATEADD(DD,-1,GETDATE()) FDate, CONVERT( VARCHAR(40), 'LSKC'+CONVERT(VARCHAR(8),DATEADD(DD,-1,GETDATE()),112) --+ CONVERT(VARCHAR(2), MONTH(GETDATE() -1)) + CONVERT(VARCHAR(2), DAY(GETDATE() -1)) +CONVERT(VARCHAR(10),T.FSTOCKID) + CONVERT(VARCHAR(10), T.FMATERIALID)) FBillNo ,'C' ,T.FSTOCKORGID ,T.FSTOCKID ,T.FMATERIALID ,T.FQty ,T.FSysLockQty ,16394 AS F_XXXX_CreatorId ,GETDATE() F_XXXX_CreateDate ,16394 AS F_XXXX_ModifierId ,GETDATE() F_XXXX_ModifyDate ,16394 AS F_XXXX_ApproverId ,GETDATE() F_XXXX_ApproveDate FROM ( SELECT IT.FSTOCKORGID,IT.FSTOCKID,IT.FMATERIALID, ISNULL(SUM(IT.FBaseQty), 0) AS FQty, ISNULL(SUM(tlk.fbaselockqty), 0) AS FSysLockQty FROM t_stk_Inventory IT--《即时库存》 JOIN t_BD_Stock f ON f.FSTOCKID = IT.FSTOCKID LEFT OUTER JOIN ( --《库存锁库》 SELECT TKE.FSUPPLYINTERID, SUM(ISNULL(TKE.FBASEQTY, 0)) fbaselockqty FROM T_PLN_RESERVELINKENTRY TKE INNER JOIN T_PLN_RESERVELINK TKH ON TKE.FID = TKH.FID WHERE TKE.FSUPPLYFORMID = 'STK_Inventory' GROUP BY TKE.FSUPPLYINTERID ) tlk ON IT.FID = TLK.FSUPPLYINTERID WHERE IT.FSTOCKORGID=100006--IT.FSTOCKID=493513 AND GROUP BY IT.FSTOCKORGID,IT.FSTOCKID,IT.FMATERIALID ) t WHERE t.FQty>0 OR t.FSysLockQty>0 END GOXXXX_P_STK_HisInventoryInfo
创建执行计划插件
新建类HisInventoryStock,继承IScheduleService
添加引用
using Kingdee.BOS; using Kingdee.BOS.App.Data; using Kingdee.BOS.Contracts; using Kingdee.BOS.Core;
实现run方法
public void Run(Context ctx, Schedule schedule) { try { var updatCount=DBUtils.ExecuteDataSet(ctx, System.Data.CommandType.StoredProcedure, string.Format(@"{0}XXXX_P_STK_HisInventoryInfo",OtherConst.DIALECT), null); } catch (Exception ee ) { Kingdee.BOS.Log.Logger.Error("库存管理", "每天凌晨记录前一天的历史库存信息异常:" + ee.Message, null); } }
创建执行计划
服务插件:Krystal.K3cloud.SCM.Stock.Business.PlugIn.LockOperate,Krystal.K3cloud.SCM.Stock.Business.PlugIn
标签:CONVERT,库存,XXXX,金蝶,信息,FID,FSTOCKID,GETDATE From: https://www.cnblogs.com/lanrenka/p/18334554