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

栏目：云星空知识作者：金蝶来源：金蝶云社区发布：2024-09-16浏览：1

场景：

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

解决思路：

使用Sql获取成本，更新到对应的《生产退料单》。

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

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

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

--Sql Script

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

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

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

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

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

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

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

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--业务凭证

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--不关联业务凭证

--更新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--业务凭证

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--不关联业务凭证

--更新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--业务凭证

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--不关联业务凭证

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

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) + " 行，请再次运行《核算单据查询》核对成本！";
        this.View.ShowMessage(Msg)
        return

#开发完成！回到前台打开单据即可看到你新增的按钮点击即可执行！