U8版本:用友U8+16.1
数据库版本:SQL Servewr2016
服务器操作系统:WinServer2019
出纳模块
1、日记账被用户锁定
对出纳日记账进行操作时,提示:
方案1:U8系统管理中,清理异常、清除单据锁定,若无效,执行方案2
方案2:数据库中操作:
-- 清除出纳日记账锁单(前提是没有人使用出纳模块)
-- 查看锁定记录
select * from cn_lockacctbook
-- 备份
select * into cn_lockacctbook_bak20221012 from cn_lockacctbook
-- 清除锁定
delete from cn_lockacctbook
-- 若正常解决,删除备份表
drop table cn_lockacctbook_bak20221012
2、出纳模块点击无反应
在其他环境正常的情况下,某个客户端的出纳模块点击打不开,点击无反应,可尝试在该客户端上修复性能计数器后再去试试:
CMD 命令行窗口执行命令cmd 执行 lodctr /r
3、出纳制单后凭证信息回写异常
出纳制单操作,凭证成功生成到总账,但是凭证信息没有回写到日记账上,导致日记账无法联查到对应的凭证,可在数据库中手动补上缺失的信息
-------------------------------出纳日记账
select
id 日记账ID,
AcctDate 日记账日期,
IsRegGLVouch 是否已制单,
VoucherStr 凭证字号,
VoucherNum 凭证号,
VouchOutSignNum 外部凭证号
from
CN_AcctBook
where
lYear =2022 and
Period = 11 and
AcctID = (select id from CN_AcctInfo where lYear = 2022 and AcctName = '中国农业银行9530') and
AcctDate = '2022-11-30 00:00:00.000' and
CSNCashSign = '付' and
CSNCashID = 610
-----------------------------------------------------日记账对应凭证
select
dbill_date 凭证日期,
csign 类别字,
ino_id 凭证号,
coutno_id 外部凭证业务号,
coutid 外部凭证单据号
from
GL_accvouch
where
iyear = 2022 and
iperiod = 11 and
dbill_date = '2022-11-30 00:00:00.000' and
csign = '付' and
ino_id = 559
--and coutid = 220841 -- 对应日记账ID
----------------------------------------- 修改日记账
-- IsRegGLVouch 是否已制单 0否 1是 改成1表示已制单
-- VoucherStr 凭证字+空格+ 凭证号 [付 204] 对应凭证表的 csign 字段 + " " + 凭证表的ino_id字段
-- VoucherNum 凭证号[204] -- 对应凭证表的 ino_id 字段
-- VouchOutSignNum 外部凭证号 [SC1000023501] 对应凭证表的 coutno_id 字段
select * into bf_CN_AcctBook_20221303 from CN_AcctBook
update CN_AcctBook set IsRegGLVouch = 1 , VoucherStr = '收 980' , VoucherNum = 980 , VouchOutSignNum = 'SC1000025657' where ID = 242460
-- 若正常解决,删除备份表
drop table bf_CN_AcctBook_20221303;
4、出纳日记账无法删除
出纳日记账由收付款单生成,根据收付款单生成凭之后正常删除凭证,由于系统异常,相关表记录回写异常,导致删除出纳日记账时,如下提示:
后台查看出纳日记账上凭证相关字段:
select
id 日记账ID,
AcctDate 日记账日期,
IsRegGLVouch 是否已制单,
VoucherStr 凭证字号,
VoucherNum 凭证号,
VouchOutSignNum 外部凭证号,*
from
CN_AcctBook
where
// 年度
lYear =2022 and
// 月份
Period = 12 and
// 根据出纳账户名查出出纳账户ID
AcctID = (select id from CN_AcctInfo where lYear = 2022 and AcctName like '%出纳账户名%') and
AcctDate = '日期' and
// 日记账收付类别
CSNCashSign = '收' and
// 日记账编号
CSNCashID = 0014
如果凭证已经正常删除,正常情况下,日记账上IsRegGLVouch 应该是0,VoucherStr 、VoucherNum 、VouchOutSignNum 三个字段应为NULL。
因为日记账来源是收付款单,再查看支付金额记录表 CN_PayedRecord 中的 lMakeVouch 字段:
// iAcctBookID 为日记账ID
// lMakeVouch 表示是否生单
select lMakeVouch,* from CN_PayedRecord where iAcctBookID='258279'
根据业务流程,此时的 lMakeVouch 字段值应为0,如果是1,改成0之后即可正常删除日记账。
基础档案
1、新增部门时,提示“需要安装部门新增后事件”插件
若某个客户端出现该情况,解决方案:在API资源管理器中,找到“部门新增后事件”,右键禁用
生产制造
1、找不到请求的服务
保存或者审核物料清单时,提示找不到请求的服务
在U8应用服务器管理器中,重启以下服务:
bat脚本
@echo off
echo 处理U8物料清单找不到服务异常
echo 关闭U8登录状态管理服务
net stop "U8KeyManagePool"
echo 关闭U8制造原程管理服务
net stop "U8MPool"
echo 关闭U8远程代理服务
net stop "U8DispatchService"
timeout 1
echo 启动U8登录状态管理服务
net start "U8KeyManagePool"
echo 启动U8制造原程管理服务
net start "U8MPool"
echo 启动U8远程代理服务
net start "U8DispatchService"
pause
库存管理
1、材料出库单时间制单日期控制
需求:需要控制属于当天的单据,当天录入,不能在今天录入属于昨天的单(当天保存的单据日期必须与当天实际日期一致)
方案1:通过操作权限控制不能修改登录日期,通过数据权限控制不能修改单据日期,制单时系统自动带出的单据日期就是当天的实际日期
方案2:通过触发器控制
CREATE trigger
[dbo].[rdrecords11_rqkz]
on
[dbo].[rdrecords11]
after insert
as
begin
SET NOCOUNT ON;
declare @id int
declare @ddate datetime
declare @ddatetime datetime
select
@ddate=convert(varchar(10),b.dDate ,120),
@ddatetime=convert(varchar(10),b.dnmaketime ,120)
from inserted a inner join rdrecord11 b on a.ID = b.ID
where
-- 指定哪些制单人可以不受控制
b.cMaker !='xxx' and
b.cMaker !='xxx'
begin
if @ddate!=@ddatetime
begin
RAISERROR(N'%s',16,1,'材料出库单保存:日期不合法,只能做当天日期的单据')
end
end
end
2、现存量查询视图
USE [UFDATA_201_2020]
GO
/****** Object: View [dbo].[zjf_query_stock_view] Script Date: 2023-06-13 16:51:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[zjf_query_stock_view] AS (
SELECT
W.cWhCode whCode, -- 仓库编码
W.cWhName whName, -- 仓库名称
I.cInvCode invCode, -- 存货编码
I.cInvName invName, -- 存货名称
I.cInvStd invStd, -- 规格型号
I.cInvCCode invClassCode, -- 存货分类编码
IC.cInvCName invClassName, -- 存货分类名称
CU_M.cComUnitName AS invMainUnit, -- 主计量单位
CASE WHEN I.iGroupType = 0 THEN NULL WHEN I.iGrouptype = 2 THEN CU_A.cComUnitName WHEN I.iGrouptype = 1 THEN CU_G.cComUnitName END AS invAuxUnit, -- 辅计量单位
CONVERT(nvarchar(38),CONVERT(decimal(38,4),CASE WHEN I.iGroupType = 0 THEN NULL WHEN I.iGroupType = 2 THEN (CASE WHEN CS.iQuantity = 0.0 OR CS.iNum = 0.0 THEN NULL ELSE CS.iQuantity/CS.iNum END) WHEN I.iGroupType = 1 THEN CU_G.iChangRate END)) AS conversionRate, -- 换算率
CONVERT(decimal(30,4),iQuantity) AS quantity, -- 现存数量
CONVERT(decimal(30,4),( CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN ISNULL(iNum,0) WHEN iGroupType = 1 THEN convert(float,iQuantity)/ CU_G.iChangRate END)) AS num, -- 现存件数
CONVERT(decimal(30,4),(CASE WHEN CS.bStopFlag = 1 OR CS.bGspStop = 1 THEN iQuantity ELSE IsNull(fStopQuantity,0) END)) AS stopQuantity, -- 冻结数量
CONVERT(decimal(30,4),(CASE WHEN CS.bStopFlag = 1 OR CS.bGspStop = 1 THEN (CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN ISNULL(iNum,0) WHEN iGroupType = 1 THEN convert(float,iQuantity)/ CU_G.iChangRate END) ELSE (CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN ISNULL(fStopNum,0) WHEN iGroupType = 1 THEN convert(float,fStopQuantity)/ CU_G.iChangRate END) END)) AS stopNum, -- 冻结件数
CONVERT(decimal(30,4),fInQuantity) AS finQuantity, -- 到货(在检)数量
CONVERT(decimal(30,4),(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fInNum,0) WHEN iGroupType = 1 THEN convert(float,fInQuantity)/ CU_G.iChangRate END)) AS finNum, -- 到货在检件数
CONVERT(decimal(30,4),fTransInQuantity) AS transInQuantity, -- 调拨在途数量
CONVERT(decimal(30,4),(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fTransInNum,0) WHEN iGroupType = 1 THEN convert(float,fTransInQuantity)/ CU_G.iChangRate END)) AS transInNum, -- 调拨在途件数
CONVERT(decimal(30,4),(ISNULL(fInQuantity,0) + ISNULL(fTransInQuantity,0))) AS inQuantitySum, -- 预计入库数量合计
CONVERT(decimal(30,4),(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fInNum,0) + ISNULL(fTransInNum,0) WHEN iGroupType = 1 THEN convert(float,(ISNULL(fInQuantity,0) + ISNULL(fTransInQuantity,0)))/ CU_G.iChangRate END)) AS inNumSum, -- -- 预计入库件数合计
CONVERT(decimal(30,4),fOutQuantity) AS foutQuantity, -- 待发货数量
CONVERT(decimal(30,4),(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fOutNum,0) WHEN iGroupType = 1 THEN convert(float,fOutQuantity)/ CU_G.iChangRate END)) AS foutNum, -- 待发货件数
CONVERT(decimal(30,4),fTransOutQuantity) AS transOutQuantity, -- 调拨待发数量
CONVERT(decimal(30,4),(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fTransOutNum,0) WHEN iGroupType = 1 THEN convert(float,fTransOutQuantity)/ CU_G.iChangRate END)) AS transOutNum, -- 调拨待发件数
CONVERT(decimal(30,4),(ISNULL(fOutQuantity,0) + ISNULL(fTransOutQuantity,0))) AS outQuantitySum , -- 预计出库数量合计
CONVERT(decimal(30,4),(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fOutNum,0) + ISNULL(fTransOutNum,0) WHEN iGroupType = 1 THEN convert(float,(ISNULL(fOutQuantity,0) + ISNULL(fTransOutQuantity,0)))/ CU_G.iChangRate END)) AS outNumSum, -- 预计出库件数合计
CONVERT(decimal(30,4),fDisableQuantity) AS disableQuantity, -- 不合格品数量
CONVERT(decimal(30,4),(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fDisableNum,0) WHEN iGroupType = 1 THEN convert(float,fDisableQuantity)/ CU_G.iChangRate END)) AS disableNum, -- 不合格品件数
CONVERT(decimal(30,4),ipeqty) AS fpeQuantity, -- PE 预留数量
CONVERT(decimal(30,4),(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(ipenum,0) WHEN iGroupType = 1 THEN convert(float,ipeqty)/ CU_G.iChangRate END)) AS fpeNum, -- PE 预留件数
CONVERT(decimal(30,4),(CASE WHEN bInvBatch=1 THEN CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0)- IsNull(fStopQuantity,0) END - ISNULL(fOutQuantity,0) ELSE CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0)- IsNull(fStopQuantity,0) END - ISNULL(fOutQuantity,0) END)) AS availQuantity, -- 最终可用数量
CONVERT(decimal(30,4),(CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN CASE WHEN bInvBatch=1 THEN CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iNum,0)- IsNull(fStopNum,0) END - ISNULL(fOutNum,0) ELSE CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iNum,0)- IsNull(fStopNum,0) END - ISNULL(fOutNum,0) END WHEN iGroupType = 1 THEN convert(float,(CASE WHEN bInvBatch=1 THEN CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0)- IsNull(fStopQuantity,0) END - ISNULL(fOutQuantity,0) ELSE CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0)- IsNull(fStopQuantity,0) END - ISNULL(fOutQuantity,0) END))/CU_G.iChangRate ELSE NULL END)) AS availNum -- 最终可用件数
FROM
v_ST_currentstockForReport CS
INNER JOIN dbo.Inventory I ON I.cInvCode = CS.cInvCode
LEFT JOIN dbo.InventoryClass IC ON IC.cInvCCode = I.cInvCCode
LEFT OUTER JOIN dbo.ComputationUnit CU_G ON I.cSTComUnitCode =CU_G.cComUnitCode
LEFT OUTER JOIN dbo.ComputationUnit CU_A ON I.cAssComUnitCode = CU_A.cComunitCode
LEFT OUTER JOIN dbo.ComputationUnit CU_M ON I.cComUnitCode = CU_M.cComunitCode
LEFT OUTER JOIN dbo.Warehouse W ON CS.cWhCode = W.cWhCode
LEFT OUTER JOIN dbo.factory ON w.cfactorycode = factory.cfactorycode
LEFT JOIN vendor v1 ON v1.cvencode = cs.cvmivencode
LEFT JOIN v_aa_enum E1 ON E1.enumcode = ISNULL(cs.iExpiratDateCalcu,0) AND E1.enumtype=N'SCM.ExpiratDateCalcu'
LEFT OUTER JOIN dbo.v_aa_enum E with (nolock) on E.enumcode=convert(nchar,CS.cMassUnit) AND E.enumType=N'ST.MassUnit'
)
GO
系统平台
1、某个操作员登录成功后,首页界面加载异常卡慢
原因可能是因为该操作员有很多未读消息任务,每次登录成功后都需要加载这些数据,所以导致卡慢,可在数据库中将未读消息批量设置为已读
select * from UA_Message where bHasRead = 0
update UA_Message set bHasRead = 1 where bHasRead = 0
select count(*) from UA_Log
总账模块
1、凭证界面会计科目名称显示斜杠
应该是点击切换到英文模式了,按 ctrl + f8 切换到中文即可,或者点击凭证界面的这个按钮:
2、科目余额表查询
-- U8利润表科目余额表查询视图
create view zjf_vbs_query_view as (
select
temp.*,
-- 逐行累加实现按月累计发生额
SUM(acc_md) OVER ( partition by temp.iyear,temp.p_ccode order by temp.iyear,temp.p_ccode,temp.iperiod asc ) i_acc_md, -- 借方累计
SUM(acc_sc) OVER ( partition by temp.iyear,temp.p_ccode order by temp.iyear,temp.p_ccode,temp.iperiod asc ) i_acc_sc -- 贷方累计
from (
select top 100 percent
t.iyear,t.iperiod,t.p_ccode,
--
sum(t.sum_md) as acc_md, -- 借方发生额 一级科目累加
sum(t.sum_sc) as acc_sc -- 贷方发生额 一级科目累加
from (
select top 100 percent -- 百分百显示结果集中满足where条件的数据
iyear, -- 会计年度
iperiod, -- 会计期间
ccode, -- 会计科目
SUBSTRING(ccode,1,4) as p_ccode, -- 一级科目
sum(md) as sum_md, -- 借方发生额 末级科目累加
sum(mc) as sum_sc -- 贷方发生额 末级科目累加
from gl_accvouch
-- 只查1级科目 '6001','6051','6401','6402'
where SUBSTRING(ccode,1,4) in( '6001','6051','6401','6402')
group by iyear,ccode,iperiod
order by iyear,ccode,iperiod
)AS t
where (select bflag from GL_mend as gm where gm.iyear = t.iyear and gm.iperiod = t.iperiod) = 1
group by t.iyear,t.p_ccode,t.iperiod
order by t.iyear,t.p_ccode,t.iperiod
)as temp
)
select * from zjf_vbs_query_view
采购模块
1、统计供应商材料采购到货平均周期
-- 统计供应商材料采购平均到货天数
SELECT
DISTINCT
temp.cVenCode 供应商编码,
temp.cVenName 供应商名称,
temp.cInvCode 存货编码,
temp.cInvName 存货名称,
AVG(temp.p_pu) OVER (PARTITION BY temp.cVenCode,temp.cInvName) 平均到货天数
FROM (
SELECT
v.cVenCode,
v.cVenName,
ps.cInvCode,
i.cInvName,
DateDiff(dd,p.dPODate, pu.dDate) p_pu -- 计算到货单日期与订单日期间隔的天数
FROM Vendor v -- 供应商表
LEFT JOIN PO_Pomain p ON v.cVenCode = p.cVenCode -- 采购订单主表
LEFT JOIN PO_Podetails ps ON p.POID = ps.POID -- 采购订单子表
LEFT JOIN PU_ArrivalVouchs pus ON pus.iPOsID = ps.ID -- 采购到货单子表
LEFT JOIN PU_ArrivalVouch pu ON pus.ID = pu.ID -- 采购到货单主表
LEFT JOIN Inventory i ON ps.cInvCode = i.cInvCode -- 存货档案表
WHERE
pu.dDate IS NOT NULL -- 未到货部分不统计
AND DateDiff(dd,p.dPODate, pu.dDate) > 0 -- 到货单日期在订单日期前的属于错误单据,排除
)AS temp
ORDER BY temp.cVenCode,temp.cInvCode
结果: