EBS gl_balance表记录科目期初金额,本期发生金额。在意外情况下,gl_balance表数据可能与日记账数据对不上,以上是数据修复步骤。(修复脚本经过多个线上环境验证)
查科目余额本期发生额有没有问题
(仅限明细账户)
原理:gl_banlance表本期取正数,日记账发生额取负数,取账户汇总后不为0得即为异常账户
SELECT ledger_id
,code_combination_id
,currency_code
,segment1
,SUM(period_net_dr)
,SUM(period_net_cr)
FROM (SELECT b.ledger_id
,b.currency_code
,b.code_combination_id
,gcc.segment1
,b.period_net_dr_beq period_net_dr
,b.period_net_cr_beq period_net_cr
FROM gl_balances b, gl_code_combinations gcc
WHERE b.period_name = '2023-04'
AND gcc.summary_flag = 'N'
AND b.code_combination_id = gcc.code_combination_id
UNION ALL
SELECT gjh.ledger_id
,gjh.currency_code
,gjl.code_combination_id
,gcc.segment1
,0 - gjl.accounted_dr
,0 - gjl.accounted_cr
FROM gl_je_headers gjh
,gl_je_lines gjl
,gl_code_combinations gcc
,gl_ledgers gle
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.ledger_id = gle.ledger_id
AND gjh.period_name = '2023-04'
AND gcc.summary_flag = 'N'
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.status = 'P')
GROUP BY ledger_id, code_combination_id, currency_code, segment1
HAVING SUM(period_net_dr) <> 0 OR SUM(period_net_cr) <> 0;
汇总账户数据修复
对于汇总账户,数据修复采用删除汇总模板,添加汇总模板,运行请求:程序 - 逐级添加/删除汇总模板
删除模板,然后新建
明细账户修复
需要用到Oracle提供得数据修复脚本,详细见附件