Python 账表插件，处理生产退料单0成本示例

Python 账表插件，处理生产退料单0成本示例原创

╄秋メ凋零

4人赞赏了该文章 306次浏览编辑于2024年09月02日 10:41:43

摘要由AI智能服务提供

本文讨论了因WMS系统限制导致生产退料单无法正确关联成本的问题，并给出了解决方案。通过在BOS开发平台中添加按钮和Python表单插件，执行SqlScript自动获取并更新生产退料单的成本信息，解决了手动处理繁琐的问题。同时，为预防数据出错，还创建了记录表用于数据恢复。文中详细说明了处理逻辑、SqlScript脚本及Python插件的编写和注册过程。

场景

因使用了WMS系统，无法通过《生产领料单》去下推《生产退料单》以致生产退料只能从《生产用料清单》下推生成，并且对应的物料仓库需要退到指定的《不良品仓库》与领料单的《原材料仓库》也不对应，从而对应的物料在核算的时候就没有对应的成本。

解决思路

通过BOS开发平台，在《核算单据查询》账表，菜单栏添加《按钮》，添加注册Python 表单插件，执行SqlScript获取成本，更新对应的《生产退料单》，实现让用户自己点击功能按钮后，自动化处理数据，解决手工繁复处理的问题。

生产退料单没有成本，处理逻辑

1、获取成本：《生产退料单》关联《生产用料清单》，并关联《生产领料单》获取对应物料成本。

2、更新条件：根据未关联《业务凭证》的《生产退料单》并且《生产退料单》单据日期大于《存货核算期末关账》关账日期，且成本为0的物料ID更新。

3、BOS开发平台添加按钮，添加Python 账表插件执行Sql获取成本，通过用户点击按钮后，实现自动化处理。

4、为了预防数据出问题，创建一张《T_SCTL_Record》表，作为更新前的记录表，可用作做更新后的数据恢复，也可以单独发布为《直接Sql账表》作为更新操作的记录查询。

如何发布Sql账表：

https://vip.kingdee.com/article/94805228373112064?lang=zh-CN&productLineId=1&isKnowledge=2

--Sql Script

/*----------------

Select b.FFINORGID as FORGID,MAX(a.FCHCKOUTDATE)as FCLOSEDATE

Into #TempSCTL20240830--结账日期临时表

From T_HS_OUTACCTG a --结账表

inner join T_HS_CALDIMENSIONS b on a.FDIMENSIONID =b.FDIMENSIONID --结账子表

inner join T_ORG_ORGANIZATIONS c on b.FFINORGID =c.FORGID --组织表

inner join T_ORG_ACCOUNTSYSTEM d on b.FACCTSYSTEMID=d.FACCTSYSTEMID --会计核算体系

inner join T_FA_ACCTPOLICY e on b.FACCTPOLICYID=e.FACCTPOLICYID --会计政策编码

group by b.FFINORGID

--如果要以结账日期做关联取这里的<结账日期临时表>

----------------*/

--创建生产退料零成本记录表，预防万一，可以做数据恢复

Create table

T_SCTL_Record

(FID INT,FENTRYID INT,FMATERIALID INT,FBILLNO NVARCHAR(255),

FNUMBER NVARCHAR(800),FPrice decimal(24,4),FAmount decimal(24,4),FDATE date)

-----------------------------------------------------------------------------------------------------------------------------

--创建处理的存储过程

Create Procedure Proc_Update_RETURNMTRLENTRY_Price

Begin

declare @count int

insert into T_SCTL_Record --记录原来0成本的生产退料明细

Select distinct T1.FID,T1.FENTRYID,T1.FMATERIALID,T0.FBILLNO,W1.FNUMBER,T1.FPrice,T1.FAmount,T0.FDATE

From T_PRD_RETURNMTRLENTRY T1

inner join T_PRD_PPBOM T2 on T2.FBILLNO =T1.FPPBOMBILLNO

inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO = T3.FPPBOMBILLNO And T3.FMATERIALID = T1.FMATERIALID

inner join T_PRD_RETURNMTRLENTRY_C T01 on T1.FENTRYID = T01.FENTRYID

inner join T_PRD_RETURNMTRL T0 on T1.FID = T0.FID

left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO = T0.FBILLNO

--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID

inner join T_BD_MATERIAL W1 on W1.FMATERIALID = T1.FMATERIALID

inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID

inner join (Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID = T0.FSTOCKORGID

Where T1.FPrice = 0 And T01.FPrice = 0 And T0.FDATE > T13.FCLOSEDATE

And T12.FSOURCEBILLNO is null

And L0.FDATE > T13.FCLOSEDATE

And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO

Set @count = (Select @@rowcount) --返回受影响的行

-------------------------------------------------------------------------------------------------------------------------------

--更新生产退料单币别

update T0 Set T0.FCURRID = T30.FCURRID

From T_PRD_RETURNMTRLENTRY T1

inner join T_PRD_PPBOM T2 on T2.FBILLNO =T1.FPPBOMBILLNO

inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO = T3.FPPBOMBILLNO And T3.FMATERIALID = T1.FMATERIALID

inner join T_PRD_PICKMTRL T30 ON T3.FID=T30.FID

inner join T_PRD_RETURNMTRLENTRY_C T01 on T1.FENTRYID = T01.FENTRYID

inner join T_PRD_RETURNMTRL T0 on T1.FID = T0.FID

left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO = T0.FBILLNO

inner join T_BD_MATERIAL W1 on W1.FMATERIALID = T1.FMATERIALID

--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID

inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID

inner join(Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID=T0.FSTOCKORGID --关联存货核算期末结账最新关账日期

Where T1.FPrice = 0 And T01.FPrice = 0 And T0.FDATE > T13.FCLOSEDATE

And T12.FSOURCEBILLNO is null

And T3.FPrice > 0

And L0.FDATE > T13.FCLOSEDATE

And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO

--更新生产退料明细成本

Update T1 Set T1.FPrice=T3.FPrice,T1.FAmount=T3.FPrice*T1.Fqty From T_PRD_RETURNMTRLENTRY T1 --生产退料明细

inner join T_PRD_PPBOM T2 on T2.FBILLNO=T1.FPPBOMBILLNO--用料清单

inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO=T3.FPPBOMBILLNO And T3.FMATERIALID=T1.FMATERIALID--生产领料

inner join T_PRD_RETURNMTRLENTRY_C T01 on T1.FENTRYID=T01.FENTRYID--生产退料明细CE表

inner join T_PRD_RETURNMTRL T0 on T1.FID=T0.FID--生产退料单

left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO=T0.FBILLNO--业务凭证

--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID

inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID

inner join (Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID=T0.FSTOCKORGID --关联存货核算期末关结账最新关账日期

Where T1.FPrice = 0 And T01.FPrice = 0 And T0.FDATE > T13.FCLOSEDATE

And T12.FSOURCEBILLNO is null--不关联业务凭证

And L0.FDATE > T13.FCLOSEDATE

And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO

--更新C表成本

Update T01 set T01.FISACCTGBILL = 1,--更新 _C 表的固定成本标识

T01.FAMOUNT_LC =T1.FAmount,T01.FPrice=T1.FPrice

From T_PRD_RETURNMTRLENTRY T1 --生产退料明细

inner join T_PRD_PPBOM T2 on T2.FBILLNO=T1.FPPBOMBILLNO--用料清单

inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO=T3.FPPBOMBILLNO And T3.FMATERIALID=T1.FMATERIALID--生产领料

inner join T_PRD_RETURNMTRLENTRY_C T01 on T1.FENTRYID=T01.FENTRYID--生产退料明细C表

inner join T_PRD_RETURNMTRL T0 on T1.FID=T0.FID--生产退料单

left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO=T0.FBILLNO--业务凭证

--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID

inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID

inner join(Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID=T0.FSTOCKORGID --关联存货核算期末结账最新关账日期

Where T1.FPrice = 0 And T01.FPrice = 0 And T0.FDATE > T13.FCLOSEDATE

And T12.FSOURCEBILLNO is null--不关联业务凭证

And L0.FDATE > T13.FCLOSEDATE

And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO

--更新CE表成本

Update T01 set T01.FAMOUNT_LC = T1.FAmount

From T_PRD_RETURNMTRLENTRY T1 --生产退料明细

inner join T_PRD_PPBOM T2 on T2.FBILLNO=T1.FPPBOMBILLNO--用料清单

inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO=T3.FPPBOMBILLNO And T3.FMATERIALID=T1.FMATERIALID--生产领料

inner join T_PRD_RETURNMTRLENTRY_CE T01 on T1.FENTRYID=T01.FENTRYID--生产退料明细CE表

inner join T_PRD_RETURNMTRL T0 on T1.FID=T0.FID--生产退料单

left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO=T0.FBILLNO--业务凭证

--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID

inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID

inner join(Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID=T0.FSTOCKORGID --关联存货核算期末结账最新关账日期

Where T1.FPrice = 0 And T01.FAMOUNT_LC = 0 And T0.FDATE > T13.FCLOSEDATE

And T12.FSOURCEBILLNO is null--不关联业务凭证

And L0.FDATE > T13.FCLOSEDATE

And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO

--结束

Select @count as FColumn --返回insert的新增行数

--drop table #TempSCTL20240830 --删除结账日期临时表

End

--将以上SqlScript运行在服务器或者天梯后继续下一步

登录Bos开发平台，扩展《核算单据查询》账表，记住是账表不是动态表单

进入菜单集合

增加按钮

明明好按钮标识与按钮名称

#Python Script

import clr
clr.AddReference("mscorlib")
clr.AddReference("Kingdee.BOS")
clr.AddReference("Kingdee.BOS.Core")
clr.AddReference("Kingdee.BOS.App")
clr.AddReference("System.Data")
from Kingdee.BOS import *
from Kingdee.BOS.App.Data import *
from Kingdee.BOS.Core.DynamicForm.PlugIn import *
from Kingdee.BOS.Core.DynamicForm.PlugIn.Args import *
from Kingdee.BOS.Util import *
from System import *
from System.Collections.Generic import *
from System.Data import *

def BarItemClick(e):
if (e.BarItemKey.Equals("按钮标识", StringComparison.OrdinalIgnoreCase)):#按钮标识：New_tbButton

SqlScript = "/*dialect*/ Execute Proc_Update_RETURNMTRLENTRY_Price";
OrderRow = DBUtils.ExecuteScalar(this.Context,SqlScript,None)#执行SqlScript,返回首行首列的值！

        Msg = "已更新处理《生产退料单》零成本的，共：" + str(OrderRow) + " 行，请再次运行《核算单据查询》核对成本！";
if OrderRow > 0:
            this.View.ShowMessage(Msg);
        else:
            this.View.ShowMessage("《生产退料单》未检测到符合更新条件的数据！");
        return