探索按文本内容进行预算控制

栏目：yonbip/nccloud知识作者：用友来源：用友发布：2024-08-23浏览：1

探索按文本内容进行预算控制

1.问题描述和业务场景

业务人员编制预算时，希望直接填写文本内容，之后单据里也填写相同的文本内容进行管控。如下方预算编制表格，希望直接填写预算事项。之后填写单据的时候按预算事项进行控制。确认同一个部门、同一年、同一个费用类别里，不会填写相同的预算事项。

填写比如报销单据的时候，也不参照档案，手工填写。

2.解决方案

2.1问题分析

产品无法根据文本控制单据的填写，那么还是考虑如何将预算事项变换成维度的组合。

因为预算事项档案相对来说不会重复，所以单据在填写预算编制表里存在的“预算事项”后，可以利用公式将单据上的“费用类别”和“序号”给带出来。这样就可以和预算编制的“费用类别”和“序号”维度对应进行控制。

问题关键是如何在填写“预算事项后”，将单据上的“费用类别”和“序号”自动取出来。单据模板可以使用getcolvalue函数从系统的数据库表里取数。而预算数据在数据库里的表名是tb_cube_应用模型编码。逻辑上是可以取出的。

应用模型编号是fscm01，所以相关的表就是tb_cube_fscm01。

2.2预算编制表设计

预算实现设计为可以填写文本的数据区。除了费用类别外，加入预算序号的设计，使每一个预算事项对应的不同的维度。

并且应用模型增加了表单维度，这张表上所有数据按表单维度做了隔离。这步操作直接关系到后续单据模板上，编辑公式的取数效率和正确率。

填制后数据情况如下，每个预算事项对应不同费用类别和预算序号的组合。

通过报表平台，根据select * from tb_cube_fscm01可以取到表中的数据进行具体分析。

提炼“购买办公用水100箱”这列预算事项在数据库里存储的信息。目前费用类别用的就是收支项目档案，所以要取出来的档案就是收支项目PK（绿色）和预算编号PK（绿色）的值。在单据模板编辑公式设置的时候可以参考。

2.3单据模板的设计

单据模板表体增加 “收支项目”和“序号”项目。

报销事项是：defitem1，收支项目系统预置：szxmid，预算序列号是：defitem9

以下是一系列的测试和分析过程：

因为限定条件很多，所以getcolvaluemore函数里，所有都用数据库里查询到的固定值，先保证可以从tb_cube_fscm01表取到序号PK_YSXH的值。

arap_bxbusitem.defitem9->getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY","1001A11000000000K9CF","PK_YEAR","2022","TXTVALUE","购买办公用水100箱","PK_MEASURE","1001A11000000001FZLR","CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001")

使用固定值成功取到序号PK后，就将固定值替换成表单上的项目。设置TXTVALUE取单据arap_bxbusitem.defitem1字段，测试能成功取值。

arap_bxbusitem.defitem8->getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY","1001A11000000000K9CF","PK_YEAR","2022","TXTVALUE",arap_bxbusitem.defitem1,"PK_MEASURE","1001A11000000001FZLR","CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001");

进一步将报销单费用承担部门head.fydeptid字段设置给了PK_ENTITY字段，测试成功。

getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY",head.fydeptid,"PK_YEAR","2022","TXTVALUE",arap_bxbusitem.defitem1,"PK_MEASURE","1001A11000000001FZLR","CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001");

将单据日期字段加工后，作为PK_YEAR年维度，测试成功。

djyear->left(tostring(head.djrq),4);

arap_bxbusitem.defitem8->getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY",head.fydeptid,"PK_YEAR",djyear,"TXTVALUE",arap_bxbusitem.defitem1,"PK_MEASURE","1001A11000000001FZLR","CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001");

将单据的收支项目arap_bxbusitem.szxmid作为PK_MEASURE指标维度。

djyear->left(tostring(head.djrq),4);

arap_bxbusitem.defitem8->getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY",head.fydeptid,"PK_YEAR",djyear,"TXTVALUE",arap_bxbusitem.defitem1,"PK_MEASURE",arap_bxbusitem.szxmid,"CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001");

到这里所有变化的维度都做了变量，而以下项目都是固定值，不用变化。

"CODE_MVTYPE","Budget" 业务方案

"CODE_VERSION","v0" 版本

"CODE_CURR","CNY" 币种

"PK_BD","1001A11000000001FZGZ" 表单维（表样设计时专门添加）

"CODE_ASSDIM","JS001001" 辅助维（JS001001是预算事项的编码）

之前公式都将费用项目（收支项目）作为了条件，业务员填写单据的时候，很可能还没有填写收支项目，所以不把收支项目作为条件的时候，公式如下。

djyear->left(tostring(head.djrq),4);

arap_bxbusitem.defitem8->getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY",head.fydeptid,"PK_YEAR",djyear,"TXTVALUE",arap_bxbusitem.defitem1, "CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001");

根据以上分析测试后，在报销单表体的费用事项上，设置编辑公式，回写的是收支项目和预算序列号，测试成功。

djyear->left(tostring(head.djrq),4);

arap_bxbusitem.szxmid->getcolvaluemore("tb_cube_fscm01","PK_MEASURE","PK_ENTITY",head.fydeptid,"PK_YEAR",djyear,"TXTVALUE",arap_bxbusitem.defitem1, "CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001");

arap_bxbusitem.defitem9->getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY",head.fydeptid,"PK_YEAR",djyear,"TXTVALUE",arap_bxbusitem.defitem1, "CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001");

在报销单表体的收支项目上，设置编辑公式，回写预算序列号测试成功。

djyear->left(tostring(head.djrq),4);

arap_bxbusitem.defitem9->getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY",head.fydeptid,"PK_YEAR",djyear,"TXTVALUE",arap_bxbusitem.defitem1, "PK_MEASURE",arap_bxbusitem.szxmid,"CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001");

最后加入校验，判断单据报销事项为空值时的情况。

在报销单表体的费用事项上，设置编辑公式如下：。

djyear->left(tostring(head.djrq),4);

arap_bxbusitem.szxmid->iif(arap_bxbusitem.defitem1=="","",getcolvaluemore("tb_cube_fscm01","PK_MEASURE","PK_ENTITY",head.fydeptid,"PK_YEAR",djyear,"TXTVALUE",arap_bxbusitem.defitem1, "CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001"));

arap_bxbusitem.defitem9->iif(arap_bxbusitem.defitem1=="","",getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY",head.fydeptid,"PK_YEAR",djyear,"TXTVALUE",arap_bxbusitem.defitem1, "CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001"));

在报销单表体的收支项目上，设置编辑公式如下：。

djyear->left(tostring(head.djrq),4);

arap_bxbusitem.defitem9->iif(arap_bxbusitem.defitem1=="","",getcolvaluemore("tb_cube_fscm01","PK_YSXH","PK_ENTITY",head.fydeptid,"PK_YEAR",djyear,"TXTVALUE",arap_bxbusitem.defitem1, "PK_MEASURE",arap_bxbusitem.szxmid,"CODE_MVTYPE","Budget","CODE_VERSION","v0","CODE_CURR","CNY","PK_BD","1001A11000000001FZGZ","CODE_ASSDIM","JS001001"));

验证输入档案测试成功。