问题描述
AR关账时遇到了这个问题,根本原因是,因为用户录入另一个贷项的发票,做过核销,后来又取消了核销,未创建会计分录,未传送总账,不想要这个贷项发票了,前台删除不了,于是就备份后进行了后台删除,删除了如下几个表:
--事务头
DELETE FROM ra_customer_trx_all trx
WHERE trx.customer_trx_id = 871860;
--事务行
DELETE FROM ra_customer_trx_lines_all l
WHERE l.customer_trx_id = 871860;
--分配表
DELETE FROM ra_cust_trx_line_gl_dist_all d
WHERE d.customer_trx_id = 871860
AND d.customer_trx_line_id IN (739451, 739452);
--子分类账事件表
DELETE FROM xla.xla_events xe
WHERE xe.event_id = 3129795;
--子分类账事务表
DELETE FROM xla.xla_transaction_entities xte
WHERE xte.entity_id = 2963232;
其实还有下面两个表里面有残留数据:
付款计划表 AR_PAYMENT_SCHEDULES_ALL
核销表 AR_RECEIVABLE_APPLICATIONS
想到事务主表的数据都已经清理掉了,后面得关账检查报表应该检查不出来,删除后运行关账检查报表,果然没有检查出来,结果关账的时候报了下面的错误:
问题分析
于是就运行了Oracle 提供的分析程序: EBS Receivables Period Close Analyzer (Doc ID 2019636.1)
发现主要是两个影响:
- AR_RECEIVABLE_APPLICATIONS_ALL 核销表有未传送总账的数据
- AR_PAYMENT_SCHEDULES_ALL 里面缺失数据(事务主表有,计划表没有)
第1个问题是数据没清理感觉造成的,看来Oracle 设计的确实比较严谨,不容许有一丁点的错误。
第2个问题 是因为做发票贷项的时候,选择的【事务处理类型】对应的配置上【未结应收款】没有勾选,如图:
导致做完贷项后,AR_PAYMENT_SCHEDULES_ALL 表里面没有自动生成数据,最终导致了贷项通知单核销发票时报错:FRM40738 参数1(用户内部的 NAME_ID 不能为空),如下图:
问题处理
问题1:
备份后,删除核销表 AR_RECEIVABLE_APPLICATIONS_ALL 里面的数据。
问题2
后台更新 ra_cust_trx_types_all 表中对应的字段由N改成Y
往付款计划表 AR_PAYMENT_SCHEDULES_ALL 补录与贷项发票 对应的数据。文章来源:https://www.toymoban.com/news/detail-438218.html
作为上面两步后重新进行关账,就可以顺利进行关账了,原来的贷项通知单不能核销发票的也能进行核销了。文章来源地址https://www.toymoban.com/news/detail-438218.html
附录:相关sql
检查数据完整性的sql
select distinct ct.customer_trx_id, ct.trx_number, ct.trx_date
from ra_customer_trx_all ct ,
ra_cust_trx_types_all ctt ,
ra_cust_trx_line_gl_dist_all gld
where ct.cust_trx_type_id = ctt.cust_trx_type_id
and ctt.type in ('INV', 'DM', 'CM', 'CB')
AND ctt.org_id = ct.org_id
and ctt.accounting_affect_flag = 'Y'
and gld.customer_trx_id = ct.customer_trx_id
and ct.complete_flag = 'Y'
and gld.customer_trx_id = ct.customer_trx_id
and gld.account_class = 'REC'
and gld.account_set_flag = 'N'
and not exists
(select 'x'
from ar_payment_schedules_all ps
where ps.customer_trx_id = ct.customer_trx_id )
and gld.gl_date between to_date('01-APR-2023','DD-MON-YYYY') and
to_date('30-APR-2023','DD-MON-YYYY')
and gld.org_id = 3867
order by 1
检查未过账的逻辑的sql
SELECT ' ' "Unposted Items in AR"
FROM dual
WHERE EXISTS
(SELECT 'Y'
FROM AR_ADJUSTMENTS_ALL
WHERE posting_control_id = -3
AND gl_date BETWEEN to_date('01-APR-2023', 'DD-MON-YYYY') AND to_date('30-APR-2023', 'DD-MON-YYYY')
AND NVL(postable, 'Y') = 'Y'
AND NVL(org_id, -99) = 3867
AND rownum < 2
UNION
SELECT 'Y'
FROM AR_RECEIVABLE_APPLICATIONS_ALL RA
WHERE ra.posting_control_id = -3
AND ra.application_type = 'CM'
AND ra.gl_date BETWEEN to_date('01-APR-2023', 'DD-MON-YYYY') AND to_date('30-APR-2023', 'DD-MON-YYYY')
AND NVL(ra.postable, 'Y') = 'Y'
AND NVL(ra.org_id, -99) = 3867
AND rownum < 2
UNION
SELECT 'Y'
FROM AR_CASH_RECEIPT_HISTORY_ALL a
WHERE a.posting_control_id = -3
AND a.gl_date BETWEEN to_date('01-APR-2023', 'DD-MON-YYYY') AND to_date('30-APR-2023', 'DD-MON-YYYY')
AND a.postable_flag = 'Y'
AND NVL(a.org_id, -99) = 3867
AND rownum < 2
UNION
SELECT 'Y'
FROM RA_CUST_TRX_LINE_GL_DIST_ALL a, RA_CUSTOMER_TRX_ALL b
WHERE a.posting_control_id = -3
AND a.gl_date BETWEEN to_date('01-APR-2023', 'DD-MON-YYYY') AND to_date('30-APR-2023', 'DD-MON-YYYY')
AND b.complete_flag = 'Y'
AND a.account_set_flag = 'N'
AND b.customer_trx_id = a.customer_trx_id
AND NVL(a.org_id, -99) = 3867
AND rownum < 2
UNION
SELECT 'Y'
FROM ar_misc_cash_distributions_all mcd
WHERE mcd.posting_control_id = -3
AND mcd.gl_date BETWEEN to_date('01-APR-2023', 'DD-MON-YYYY') AND to_date('30-APR-2023', 'DD-MON-YYYY')
AND mcd.org_id = 3867
AND rownum < 2
UNION
SELECT 'Y'
FROM AR_RECEIVABLE_APPLICATIONS_ALL RA
WHERE ra.posting_control_id = -3
AND ra.application_type = 'CASH'
AND ra.gl_date BETWEEN to_date('01-APR-2023', 'DD-MON-YYYY') AND to_date('30-APR-2023', 'DD-MON-YYYY')
AND NVL(ra.postable, 'Y') = 'Y'
AND NVL(ra.org_id, -99) = 3867
AND rownum < 2
UNION
SELECT 'Y'
FROM AR_TRANSACTION_HISTORY_ALL a, RA_CUSTOMER_TRX_ALL b
WHERE a.posting_control_id = -3
AND a.gl_date BETWEEN to_date('01-APR-2023', 'DD-MON-YYYY') AND to_date('30-APR-2023', 'DD-MON-YYYY')
AND a.postable_flag = 'Y'
AND b.customer_trx_id = a.customer_trx_id
AND NVL(a.org_id, -99) = 3867
AND rownum < 2)
AND rownum < 2
到了这里,关于应付模块无法关账问题 APP-AR-11332 您必须在关闭此期之前过账其中的所有事务处理的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!