校验配置说明
<p>如果单据保存有跟回写相关的量的校验以及次数之类的校验等,则可以在回写的配置文件中进行配置。</p>
<h3>校验配置说明</h3>
<p><strong>- 配置在当前回写过程函数中触发校验的key值</strong></p>
<pre><code>//同步回写采购合同直接入库量
#set($upbillinfoMap=$vs.util.newLinkedHashMap());
#set($upbillinfo=$proc.getInfoModel('opt'));
#set($upbillinfo.billtypecolumn='ENTRYINF_DETAIL_BILLTYPE')//业务单据类型(关联上级单据的单据类型)
#set($upbillinfo.billcodecolumn='ENTRYINF_DETAIL_OPTCODE')//业务单据号(关联上级单据的单据号)
#set($upbillinfo.billbatchcolumn='ENTRYINF_DETAIL_OPTBATCH')//业务单据批号(关联上级单据的批号)
#set($upbillinfo.updatename='入库通知回写采购合同直接入库量');// 回写名称。
//数据源表:按回写的单据批号汇总,将回写的值进行合计。查处的值需要别名和目标单据批号和更新的字段对应。
#set($upbillinfo.updatesorcetable="SELECT b.ENTRYINF_DETAIL_OPTBATCH as GOODS_CONTRACTBATCH,SUM(b.GOODS_NUM) AS CONTRACT_DETAIL_DNUM,SUM(b.GOODS_WEIGHT) AS CONTRACT_DETAIL_DWEIGHT,SUM(b.GOODS_INMONEY) AS CONTRACT_DETAIL_DMONEY FROM WAREHOUSE_ENTRYINF_DETAIL b where b.ENTRYINF_DETAIL_BILLTYPE='BT1139' and 2=2 GROUP BY b.ENTRYINF_DETAIL_OPTBATCH");//回写数据源表或查询
#set($upbillinfo.updatesorcewherecolumn="b.ENTRYINF_DETAIL_OPTBATCH")//加上别名。用于更新时,在数据源后面直接加上查询条件。如果此字段不为空,可以将updatesorcetable条件中的2=2替换成新的条件
#set($upbillinfo.updategoaltypecode='BT1139');//回写目标单据类型
#set($upbillinfo.updategoaltable='purchase_contract_detail');//回写目标表
#set($upbillinfo.updategoalcolumn='CONTRACT_DETAIL_DNUM,CONTRACT_DETAIL_DWEIGHT,CONTRACT_DETAIL_DMONEY');//需要回写更新的字段数组
#set($upbillinfo.updatetype=0);// 更新类型:0同步,1异步。
#set($upbillinfo.updateoperatortypecode='BT1142');// 操作的单据类型,当操作单据类型与此相同时触发回写。为空表示是本单据触发。
#set($upbillinfo.afterupdatecheck='detail_check,detail_all_check');// 执行数据数据检测。
$upbillinfoMap.put('BT1139', $upbillinfo);//采购合同</code></pre>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=edee9db3895688554d417b4023007ae5&file=file.png" alt="" /></p>
<hr />
<h3>- 配置执行回写校验的语句</h3>
<pre><code>#set($billcheckMap=$vs.util.newMap());
#set($billcheck = $vs.util.newMap())
// 检测语句,查询结果为提示的内容。多行则逐行拼接在一起提示。
//按主表控制
#set($billcheck.checksql=`
select
SQLTools.concat('采购合同',t.goods_contract,'限量控制:',
case when t1.NUM_OVERFLOW_CONTROL=1 and round(sum(t.goods_num*(100+ t1.NUM_OVERFLOW_SCALE))/100,0)- sum(SQLTools.isNull(t.CONTRACT_DETAIL_CNUM,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SNUM,0))<0
then
SQLTools.concat('数量超限:合同数量',sum(t.goods_num),',溢短量',round(sum(t.goods_num*t1.NUM_OVERFLOW_SCALE)/100,0),',超过执行数量',sum(SQLTools.isNull(t.CONTRACT_DETAIL_CNUM,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SNUM,0)),';')
else '' end
, case when t1.SHORT_OVERFLOW_CONTROL=1 and round(sum(t.goods_weight*(100+t1.SHORT_OVERFLOW_SCALE))/100,4)- sum(SQLTools.isNull(t.CONTRACT_DETAIL_CWEIGHT,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SWEIGHT,0))<0
then
SQLTools.concat('重量超限:合同重量',sum(t.goods_weight),',溢短量',round(sum(t.goods_weight*t1.SHORT_OVERFLOW_SCALE)/100,4),',超过执行重量',sum(SQLTools.isNull(t.CONTRACT_DETAIL_CWEIGHT,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SWEIGHT,0)),';')
else '' end
,case when t1.MONEY_OVERFLOW_CONTROL=1 and round(sum(t.goods_inmoney*(100+t1.MONEY_OVERFLOW_SCALE))/100,2)- sum(SQLTools.isNull(t.CONTRACT_DETAIL_CMONEY,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SMONEY,0)) < 0
then
SQLTools.concat('金额超限:合同金额',sum(t.goods_inmoney),',溢短量',round(sum(t.goods_inmoney*t1.MONEY_OVERFLOW_SCALE)/100,2),',超过执行金额',sum(SQLTools.isNull(t.CONTRACT_DETAIL_CMONEY,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SMONEY,0)),';')
else '' end ) as ERROR_MSG
from purchase_contract_detail t
inner join purchase_contract t1 on t.goods_contract=t1.goods_contract
where t1.BILLTYPE_CODE='BT1139' and t1.OVERFLOW_CONTROL=1 and (NUM_OVERFLOW_CONTROL=1 or SHORT_OVERFLOW_CONTROL=1 or MONEY_OVERFLOW_CONTROL=1 ) and 2=2
group by t.goods_contract,t1.NUM_OVERFLOW_CONTROL,SHORT_OVERFLOW_CONTROL,MONEY_OVERFLOW_CONTROL
having
round(sum(t.goods_num*(100+ t1.NUM_OVERFLOW_SCALE))/100,0) -sum(SQLTools.isNull(t.CONTRACT_DETAIL_CNUM,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SNUM,0))<0 or
round(sum(t.goods_weight*(100+t1.SHORT_OVERFLOW_SCALE))/100 ,4)- sum(SQLTools.isNull(t.CONTRACT_DETAIL_CWEIGHT,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SWEIGHT,0))<0 or
round(sum(t.goods_inmoney*(100+t1.MONEY_OVERFLOW_SCALE))/100,2) - sum(SQLTools.isNull(t.CONTRACT_DETAIL_CMONEY,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SMONEY,0))<0
`)
#set($billcheck.checkwherecolumn='t.goods_contract');//检测条件字段,包含别名
#set($billcheck.checksourcetable='purchase_contract');//检测表名称,根据此表名称,获取单据号或批号值。如果是主表,取单据号,如果是明细表,取批号
#set($billcheck.checkmod=0);//检测类型:0 提示、1 拦截。默认为0
#set($billcheck.checktime='');//检测时机:默认为空,表示全部操作。save为新增、修改、删除。add 为新增、update为修改,delete为删除。backupdate为回写(回写具体回写的时机,根据回写配置的参数控制。)。
#set($billcheck.checkopenflag='');//检测系统开关 定义应用参数名称和条件值。
$billcheckMap.put('detail_all_check', $billcheck);//检测类型。按整单超限检测
#set($billcheck = $vs.util.newMap())
#set($billcheck.checksql=`
select SQLTools.concat('采购合同',t.goods_contract,'合同批号',t.goods_contractbatch,'限量控制:',
(case when t1.NUM_OVERFLOW_CONTROL=1 and round(t.goods_num*(100+ t1.NUM_OVERFLOW_SCALE)/100,0)- (SQLTools.isNull(t.CONTRACT_DETAIL_CNUM,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SNUM,0))<0
then SQLTools.concat('数量超限:合同数量',t.goods_num,',溢短量',round(t.goods_num*t1.NUM_OVERFLOW_SCALE/100,0),',超过执行数量',(SQLTools.isNull(t.CONTRACT_DETAIL_CNUM,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SNUM,0)),';')
else '' end),
( case when t1.SHORT_OVERFLOW_CONTROL=1 and round(t.goods_weight*(100+t1.SHORT_OVERFLOW_SCALE)/100,4)- (SQLTools.isNull(t.CONTRACT_DETAIL_CWEIGHT,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SWEIGHT,0))<0
then SQLTools.concat('重量超限:合同重量',t.goods_weight,',溢短量',round(t.goods_weight*t1.SHORT_OVERFLOW_SCALE/100,4),',超过执行重量',(SQLTools.isNull(t.CONTRACT_DETAIL_CWEIGHT,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SWEIGHT,0)),';')
else '' end),
(case when t1.MONEY_OVERFLOW_CONTROL=1 and round(t.goods_inmoney*(100+t1.MONEY_OVERFLOW_SCALE)/100,2)- (SQLTools.isNull(t.CONTRACT_DETAIL_CMONEY,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SMONEY,0))<0
then SQLTools.concat('金额超限:合同金额',t.goods_inmoney,',溢短量',round(t.goods_inmoney*t1.MONEY_OVERFLOW_SCALE/100,2),',超过执行金额',(SQLTools.isNull(t.CONTRACT_DETAIL_CMONEY,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SMONEY,0)),';')
else '' end )) as ERROR_MSG
from purchase_contract_detail t
inner join purchase_contract t1 on t.goods_contract=t1.goods_contract
where t1.BILLTYPE_CODE='BT1139' and t1.OVERFLOW_CONTROL=2 and 2=2 and
((NUM_OVERFLOW_CONTROL=1 and round((t.goods_num*(100+ t1.NUM_OVERFLOW_SCALE)/100),0)- (SQLTools.isNull(t.CONTRACT_DETAIL_CNUM,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCNUM,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SNUM,0))<0)
or (SHORT_OVERFLOW_CONTROL=1 and round((t.goods_weight*(100+t1.SHORT_OVERFLOW_SCALE)/100),4)- (SQLTools.isNull(t.CONTRACT_DETAIL_CWEIGHT,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCWEIGHT,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SWEIGHT,0))<0)
or (MONEY_OVERFLOW_CONTROL=1 and round((t.goods_inmoney*(100+t1.MONEY_OVERFLOW_SCALE)/100),2)- (SQLTools.isNull(t.CONTRACT_DETAIL_CMONEY,0) + SQLTools.isNull(t.CONTRACT_DETAIL_DMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SCMONEY,0)+ SQLTools.isNull(t.CONTRACT_DETAIL_SMONEY,0))<0))
`)
#set($billcheck.checkwherecolumn='t.goods_contractbatch');//检测条件字段,包含别名
#set($billcheck.checksourcetable='purchase_contract_detail');//检测表名称,根据此表名称,获取单据号或批号值。如果是主表,取单据号,如果是明细表,取批号
#set($billcheck.checkmod=0);//检测类型:0 提示、1 拦截。默认为0
#set($billcheck.checktime='');//检测时机:默认为空,表示全部操作。save为新增、修改、删除。add 为新增、update为修改,delete为删除。backupdate为回写(回写具体回写的时机,根据回写配置的参数控制。)。
#set($billcheck.checkopenflag='');//检测系统开关 定义应用参数名称和条件值。
$billcheckMap.put('detail_check', $billcheck);//检测类型。按明细限量检测
#set($detailtable.billcheckMap=$billcheckMap);</code></pre>
<table>
<thead>
<tr>
<th>配置字段</th>
<th>配置说明</th>
<th>注意点</th>
<th>涉及操作</th>
</tr>
</thead>
<tbody>
<tr>
<td>$billcheck.checksql</td>
<td>检测语句</td>
<td>检测语句,查询结果为提示的内容。多行则逐行拼接在一起提示,检测语句写完后要取别名为:ERROR_MSG</td>
<td>回写校验</td>
</tr>
<tr>
<td>$billcheck.checkwherecolumn</td>
<td>检测条件字段</td>
<td>写检测字段的时候,要取别名</td>
<td>回写校验</td>
</tr>
<tr>
<td>$billcheck.checksourcetable</td>
<td>检测表名称</td>
<td>根据此表名称,获取单据号或批号值。如果是主表,取单据号,如果是明细表,取批号</td>
<td>回写校验</td>
</tr>
<tr>
<td>$billcheck.checkmod</td>
<td>检测类型:0 提示、1 拦截。默认为0</td>
<td>根据需求定义</td>
<td>回写校验</td>
</tr>
<tr>
<td>$billcheck.checktime</td>
<td>检测时机</td>
<td>默认为空,表示全部操作。save为新增、修改、删除。add 为新增、update为修改,delete为删除。backupdate为回写(回写具体回写的时机,根据回写配置的参数控制</td>
<td>回写校验</td>
</tr>
<tr>
<td>$billcheck.checkopenflag</td>
<td>检测系统开关 定义应用参数名称和条件值</td>
<td></td>
<td>回写校验</td>
</tr>
</tbody>
</table>