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

 方案设计

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

 

 

详细设计

创建存储过程

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    
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