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

合集 - 金蝶云星空开发手册2(30)

1.金蝶云星空表单插件获取单据体数据2024-02-032.金蝶云星空使用插件打开单据列表2024-02-033.金蝶云星空修改单据转换规则后保存报错提示2024-02-034.金蝶云星空多组织下删除基础资料报错2024-02-035.金蝶云星空业务对象扩展2024-02-036.金蝶云星空字段之间连续触发值更新2024-07-027.生产订单执行明细表二开增加字段2024-07-098.呆滞料分析报表二开增加自定义字段2024-08-069.采购订单列表根据多条件设置单元格背景色2024-07-1510.金蝶云星空历史库存信息批量计算生成2024-07-3011.成品库存周转率报表（一）2024-08-01

12.金蝶云星空每日库存信息存储到《历史库存信息》2024-08-08

13.成品库存周转率报表（二）2024-08-0814.委外订单执行明细表增加二开字段2024-08-0615.金蝶云星空生产入库单找仓库仓位编码SQL脚本2024-08-0216.金蝶云星空创建执行计划2024-08-0617.金蝶云星空单据体数量汇总-分组列信息2024-08-0818.采购订单列表根据条件设置行背景色2024-08-0619.采购订单打开单据时表格行设置颜色2024-08-0620.金蝶云星空锁库时同时锁定序列号2024-08-0921.应收单审核中可以上传附件2024-08-1022.金蝶晕云星空表单插件：操作子单据体的删除触发父单据体的字段重算2024-08-1223.金蝶云星空解锁时同时解锁序列号2024-08-1924.金蝶云星空一个业务对象下存在两个扩展时处理方案2024-08-2225.金蝶云星空元数据冲突SVN:replaced,tree conflict树冲突解决过程2024-08-2226.创建结算清单时提示冲突解决方案2024-08-2927.金蝶云星空组织间结算清单增加自定义字段说明2024-08-2928.金蝶云星空点击按钮实现指定文件下载02-1729.C#插件实现单据审核不通过报错提示02-1730.金蝶云星空单据体数量合计设置03-05

收起

 方案设计

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

 

 

详细设计

创建存储过程

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