金蝶云星空每日库存信息存储到《历史库存信息》

金蝶云星空每日库存信息存储到《历史库存信息》

方案设计

每日凌晨获取当前即时库存明细的库存作为昨日的库存结余记录下来。

详细设计

创建存储过程

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--AND IT.FSTOCKID=493513 --只存储成品仓的
						GROUP BY
						IT.FSTOCKORGID,IT.FSTOCKID,IT.FMATERIALID 
						) t WHERE t.FQty>0 OR t.FSysLockQty>0
END	
GO

创建执行计划插件

新建类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