委外倒冲入库发料控制方法(1)——存储过程
很多企业对于委外业务中为了将委外在制材料管理起来,一般采用调拨倒冲模式,在做采购入库时倒冲扣减委外仓中的材料。由于种种原因,很多时候倒冲不成功,比如BOM错误、用量不准、调拨数量不够等导致委外仓没有足够材料倒冲生成领料单,只能后续在倒冲平台做事后处理。如果时间稍长,期间关联物料多次出入库,导致即时在倒冲平台也很难处理。
所以有较多的企业希望能在系统中有倒冲物料发料控制,在入库前就能做事前控制,能和其他模式一样能有“不控制、警告、严格控制”以满足不同的场景需求。遗憾的是,这个需求提了很多年,系统并未提供这种功能(见下图)。
那么如何能控制委外仓当不能满足倒冲则终止入库审核并给出提示信息呢?本贴给出的基本解决思路是:
(1)在采购入库单审核按钮上加服务插件;
(2)插件里加校验器,比对委外入库数量折算的材料用量和对应委外仓即时库存,如果库存不足则终止审核,给出提示。如果满足,则审核继续;
(3)校验逻辑基本放到后台存储过程中处理,如果入库单是多行物料入库的,那么只要检测到1行不满足即可返回结果给插件中的校验器,校验器终止审核动作。
以下是MSSQL存储过程全部语句,可能还有一些细节条件需要按各使用场景要自行添加逻辑。
如果返回的@result<0表示库存不足,大于0表示全部行都可以倒冲成功:
CREATE PROCEDURE [dbo].[DEMO_OUTSOURCING_VERIFY]
-- Add the parameters for the stored procedure here
@billNo VARCHAR(20),--本次采购入库单号
@result FLOAT OUTPUT,--比对结果返回值
@FNumber VARCHAR(20) OUTPUT
AS
BEGIN
DECLARE @records int
SET NOCOUNT ON;
DECLARE wwCursor CURSOR FOR --把此入库单下所有行涉及的用量信息提取出来放入游标
SELECT T45.FNUMBER WWZXBM, T45.FMATERIALID WWZXID,T7.FSTOCKID,T1.FREALQTY,T43.FNUMERATOR,T43.FDENOMINATOR,T46.FLOT
FROM T_STK_INSTOCKENTRY T1 --采购入库单明细
INNER JOIN T_STK_INSTOCK T11 ON T11.FID=T1.FID --采购入库单表头
INNER JOIN T_STK_INSTOCKENTRY_LK T12 ON T12.FENTRYID=T1.FENTRYID
AND T12.FSTABLENAME='T_PUR_ReceiveEntry'-- 采购入库单关联信息表;FSTABLENAME:t_PUR_POOrderEntry 采购订单明细(有从采购订单下推的入库单,需要union)
INNER JOIN T_BD_MATERIAL T14 ON T14.FMATERIALID=T1.FMATERIALID
INNER JOIN T_BD_MATERIAL_L T13 ON T13.FMATERIALID=T1.FMATERIALID
INNER JOIN T_BAS_BILLTYPE_L T15 ON T15.FBILLTYPEID=T11.FBILLTYPEID
INNER JOIN T_PUR_ReceiveEntry T2 ON T2.FENTRYID=T12.FSID --采购收料通知单明细 源单内码
INNER JOIN T_PUR_Receive T21 ON T21.FID=T2.FID --采购收料通知单表头
INNER JOIN T_PUR_RECEIVEENTRY_LK T22 ON T22.FENTRYID=T2.FENTRYID --收料关联信息
INNER JOIN T_PUR_POORDERENTRY T3 ON T3.FENTRYID=T22.FSID --采购订单明细 对接收料明细关联表
INNER JOIN T_PUR_POORDER T31 ON T31.FID=T3.FID --采购订单表头
INNER JOIN T_PUR_POORDERENTRY_LK T32 ON T32.FENTRYID=T3.FENTRYID
AND T32.FSTABLENAME='T_SUB_REQORDERENTRY'--采购订单关联信息表;FSTABLENAME:T_PUR_ReqEntry(采购申请)、T_PUR_MRBENTRY(退料)
INNER JOIN T_SUB_REQORDERENTRY T4 ON T4.FENTRYID=T32.FSID --委外订单明细
INNER JOIN T_SUB_REQORDER T41 ON T41.FID=T4.FID --委外订单表头
INNER JOIN T_SUB_PPBOM T42 ON T42.FSUBREQID=T4.FID AND T42.FMATERIALID=T1.FMATERIALID --委外用料清单表头
INNER JOIN T_SUB_PPBOMENTRY T43 ON T42.FID=T43.FID --委外用料清单子项
INNER JOIN T_SUB_PPBOMENTRY_C T46 ON T46.FENTRYID=T43.FENTRYID --委外用料子项物料控制
INNER JOIN T_BD_MATERIAL_L T44 ON T44.FMATERIALID=T43.FMATERIALID
INNER JOIN T_BD_MATERIAL T45 ON T45.FMATERIALID=T43.FMATERIALID
INNER JOIN T_BD_MATERIAL T5 ON T5.FMATERIALID=T1.FMATERIALID --物料
INNER JOIN T_BD_MATERIAL_L T51 ON T51.FMATERIALID=T5.FMATERIALID
INNER JOIN t_BD_Supplier T6 ON T6.FSupplierId=T31.FSUPPLIERID --供应商
INNER JOIN T_SUB_SUPWIPSTK T7 ON T7.FSUPPLIERID=T6.FSUPPLIERID --委外仓库设置
INNER JOIN t_BD_Stock T8 ON T8.FStockId=T7.FSTOCKID --仓库
WHERE T11.FBILLNO=@billNo
and T46.FISSUETYPE=4
UNION ALL
SELECT T45.FNUMBER WWZXBM, T45.FMATERIALID WWZXID,T7.FSTOCKID,T1.FREALQTY,T43.FNUMERATOR,T43.FDENOMINATOR,T46.FLOT
FROM T_STK_INSTOCKENTRY T1 --采购入库单明细
INNER JOIN T_STK_INSTOCK T11 ON T11.FID=T1.FID --采购入库单表头
INNER JOIN T_STK_INSTOCKENTRY_LK T12 ON T12.FENTRYID=T1.FENTRYID
AND T12.FSTABLENAME='t_PUR_POOrderEntry'-- 采购入库单关联信息表;FSTABLENAME:t_PUR_POOrderEntry 采购订单明细(有从采购订单下推的入库单,需要union)
INNER JOIN T_BD_MATERIAL T14 ON T14.FMATERIALID=T1.FMATERIALID
INNER JOIN T_BD_MATERIAL_L T13 ON T13.FMATERIALID=T1.FMATERIALID
INNER JOIN T_BAS_BILLTYPE_L T15 ON T15.FBILLTYPEID=T11.FBILLTYPEID
INNER JOIN T_PUR_POORDERENTRY T3 ON T3.FENTRYID=T12.FSID --采购订单明细 对接收料明细关联表
INNER JOIN T_PUR_POORDER T31 ON T31.FID=T3.FID --采购订单表头
INNER JOIN T_PUR_POORDERENTRY_LK T32 ON T32.FENTRYID=T3.FENTRYID
AND T32.FSTABLENAME='T_SUB_REQORDERENTRY'--采购订单关联信息表;FSTABLENAME:T_PUR_ReqEntry(采购申请)、T_PUR_MRBENTRY(退料)
INNER JOIN T_SUB_REQORDERENTRY T4 ON T4.FENTRYID=T32.FSID --委外订单明细
INNER JOIN T_SUB_REQORDER T41 ON T41.FID=T4.FID --委外订单表头
INNER JOIN T_SUB_PPBOM T42 ON T42.FSUBREQID=T4.FID AND T42.FMATERIALID=T1.FMATERIALID --用料清单父项
INNER JOIN T_SUB_PPBOMENTRY T43 ON T42.FID=T43.FID --委外用料清单子项
INNER JOIN T_SUB_PPBOMENTRY_C T46 ON T46.FENTRYID=T43.FENTRYID --委外用料子项物料控制
INNER JOIN T_BD_MATERIAL_L T44 ON T44.FMATERIALID=T43.FMATERIALID
INNER JOIN T_BD_MATERIAL T45 ON T45.FMATERIALID=T43.FMATERIALID
INNER JOIN t_BD_Supplier T6 ON T6.FSupplierId=T31.FSUPPLIERID --供应商
INNER JOIN T_SUB_SUPWIPSTK T7 ON T7.FSUPPLIERID=T6.FSUPPLIERID --委外仓库设置
INNER JOIN t_BD_Stock T8 ON T8.FStockId=T7.FSTOCKID --仓库
WHERE T11.FBILLNO=@billNo
and T46.FISSUETYPE=4
DECLARE @WWZXBM VARCHAR(20), @WWZXID INT,@FSTOCKID INT,@FREALQTY FLOAT,@FNUMERATOR FLOAT, @FDENOMINATOR FLOAT,@FLOT INT
OPEN wwCursor;
FETCH NEXT FROM wwCursor INTO @WWZXBM, @WWZXID,@FSTOCKID,@FREALQTY,@FNUMERATOR,@FDENOMINATOR,@FLOT
WHILE(@@FETCH_STATUS=0)--按行比对即时库存
BEGIN
IF(@FLOT>0)--有批号
BEGIN
SELECT @result= ISNULL(SUM(FBaseQty),0)-@FREALQTY*@FNUMERATOR/@FDENOMINATOR
FROM T_STK_INVENTORY
WHERE FLOT=@FLOT
AND FMATERIALID=@WWZXID
AND FSTOCKID=@FSTOCKID
GROUP BY FSTOCKID,FMATERIALID
SET @FNumber=@WWZXBM
IF (@result is null) set @result=-@FREALQTY*@FNUMERATOR/@FDENOMINATOR
IF (@result<0) BREAK
END
ELSE
BEGIN
SELECT @result= ISNULL(SUM(FBaseQty),0)-@FREALQTY*@FNUMERATOR/@FDENOMINATOR
FROM T_STK_INVENTORY
WHERE FMATERIALID=@WWZXID
AND FSTOCKID=@FSTOCKID
GROUP BY FSTOCKID,FMATERIALID
SET @FNumber=@WWZXBM
IF (@result is null) set @result=-@FREALQTY*@FNUMERATOR/@FDENOMINATOR
IF (@result<0) BREAK
END
FETCH NEXT FROM wwCursor INTO @WWZXBM, @WWZXID,@FSTOCKID,@FREALQTY,@FNUMERATOR,@FDENOMINATOR,@FLOT
END
CLOSE wwCursor;
IF(@WWZXBM is null )
BEGIN
SET @result=100 --给一个大于0的任意数
END
END
(服务插件代码后续会有给出。链接:https://wenku.my7c.com/article/485386929324606464?fromAction=POST_ARTICLE&productLineId=1)
只能校验其中一行委外单上,一个物料的库存不足吗?
委外倒冲入库发料控制方法(1)——存储过程
本文2024-09-16 18:18:43发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-21319.html