不能创建会计分录
发表于:2024-09-21 作者:千家信息网编辑
千家信息网最后更新 2024年09月21日,转自:http://blog.csdn.net/yy5512/article/details/4404772不能创建会计分录--------------------------------------
千家信息网最后更新 2024年09月21日不能创建会计分录转自:http://blog.csdn.net/yy5512/article/details/4404772
不能创建会计分录
--------------------------------------------------------------------------------
打补丁:6826219和6901404
处理原理:
如果创建会计分录失败,只要把xla_events.event_status_code和xla_events.process_status_code都改成'U',
然后提交<<创建会计科目>>请求或者直接在**界面创建会计分录,
创建会计分录程序不管xla_ae_headers和xla_ae_lines是否有与xla_events相应记录,
都会重新创建xla_ae_headers和xla_ae_lines记录
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子一:
create table xla_events_bkp
as select * from xla_events
where event_id in (select event_id from xla_events e
where e.application_id = 200
and e.event_status_code ='P'
and not exists ( select 1 from xla_ae_headers h
where e.event_id = h.event_id ));
UPDATE xla_events
SET event_status_code = 'U',
process_status_code = 'U'
WHERE event_id IN (select event_id from xla_events e
where e.application_id = 200
and e.event_status_code ='P'
and not exists ( select 1 from xla_ae_headers h
where e.event_id = h.event_id ));
运行<<创建会计科目>>请求
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子二:
**号200803005(已取消) 部分创建会计分录,付款号1102000006 不能创建会计分录
因为先有**再有付款,所以思路
1.先试图创建**的会计分录
SELECT invoice_num,invoice_id,doc_sequence_id,doc_sequence_value FROM ap_invoices_all
WHERE invoice_num = '200803005'
select event_id,ae_header_id,a.* from xla_ae_headers a
WHERE doc_sequence_id = 130
AND doc_sequence_value = '210401983'
SELECT event_id,a.* FROM xla_ae_headers a
WHERE event_id IN (70300,80395)
找到那条xla_ae_headers记录没有创建会计分录的event_id=70300,ae_header_id=57016
DELETE FROM xla_ae_lines
WHERE ae_header_id = 57016
DELETE FROM xla_ae_headers
WHERE ae_header_id = 57016
UPDATE xla_events
SET event_status_code = 'U',
process_status_code = 'U'
WHERE event_id = 70300
运行<<创建会计科目>>请求
2.再试图创建付款的会计分录
SELECT * FROM xla_ae_headers
WHERE DESCRIPTION LIKE '02000006%'
AND je_category_name = 'Payments'
DELETE xla_ae_lines
WHERE ae_header_id IN (101270,101271)
DELETE xla_ae_headers
WHERE ae_header_id IN (101270,101271)
UPDATE xla_events
SET event_status_code = 'U',
process_status_code = 'U'
WHERE event_id IN (70302,80394)
运行<<创建会计科目>>请求
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子三
ET1104应付 **号8000480849/859/862没有创建会计分录的原因是:
该**行1和 **行2 已放弃.
损益行中的帐户无效。如果您已定义损益日记帐行类型,请在日记帐行定义中检查附加至该类型的帐户推导规则。否则,请检查映射至会计属性"汇兑收益帐户"和"汇兑损失帐户"
的来源值。
无法创建帐户,因为 &OWNER 拥有的帐户推导规则 &COMPONENT_NAME 的所有条件均不满足。请更新 &COMPONENT_NAME 的条件,或分配其它帐户推导规则至 &PAD_OWNER 拥有的
应用产品会计定义 &PAD_NAME.
子分类帐会计无法使用 &OWNER 拥有的帐户推导规则 &COMPONENT_NAME 导出会计科目代码组合。请复核帐户推导规则,并确保对于为此事务处理传送的来源值,此规则可以导出
有效的会计科目弹性域组合。
请根据以上错误信息,检查 当**行被放弃时,汇兑损益 相关的帐户推导规则 ,映射至会计属性"汇兑收益帐户"和"汇兑损失帐户"的来源值,
但是顾问没有检查出来设置是否有问题。这也许是oracle的一个bug.
临时处理方法:
直接在表中删除了放弃行的**分配行和**行
CREATE TABLE AP_INVOICE_DISTRIBUTIONS_0427
AS SELECT * FROM AP_INVOICE_DISTRIBUTIONS_all
WHERE invoice_id = 15761 and invoice_line_number IN (1,2)
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_0427
CREATE TABLE AP_INVOICE_LINES_0427 as
SELECT * FROM AP_INVOICE_LINES_all
WHERE invoice_id = 15761
AND line_number IN (1,2)
SELECT * FROM AP_INVOICE_LINES_0427
DELETE FROM AP_INVOICE_DISTRIBUTIONS_all
WHERE invoice_id = 15761 and invoice_line_number IN (1,2)
DELETE FROM AP_INVOICE_LINES_all
WHERE invoice_id = 15761
AND line_number IN (1,2)
直接在**界面创建会计分录
最终处理方法:建议用户提tar由oracle官方解决
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子四:
这张**2008030888.不能创建会计分录的原因是 没有录入**的分配信息
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子五:
标准**核销预付款**后,部分会计分录不能创建。已入帐显示:部分
措施:
先在**界面上验证相关**,然后在**界面上创建会计分录
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子六:
骆勇平员工由于EBS bug,再导入费用报表后,产生两个骆勇平供应商,经过删除其中一个供应商和供应商地点后
产生的出来会计分录信息居然还有被删除供应商的ID,供应商地点ID
措施:
找到了xla_ae_lines.party_id(这里找到有些会计分录的创建是根据以前会计分录记录来创建的,比如ref_event_id,ref_ae_header_id)
于是将xla_ae_lines.party_id,xla_ae_lines.party_site_id统统改成了现在的骆勇平供应商ID和供应商地点ID
运行cux_fix_invoice_accounting_pkg.fix_invoices程序,修正数据
运行<<创建会计科目>>请求
----------------------------------------------------------------------------
修正数据的程序包cux_fix_invoice_accounting_pkg:
CREATE OR REPLACE PACKAGE cux_fix_invoice_accounting_pkg IS
PROCEDURE fix_invoices(p_invoice_id IN NUMBER);
PROCEDURE fix_payments(p_payment_num IN VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY cux_fix_invoice_accounting_pkg IS
PROCEDURE fix_invoices(p_invoice_id IN NUMBER) IS
CURSOR cur_invoice(i_cur_invoice_id IN NUMBER) IS
SELECT doc_sequence_id,doc_sequence_value FROM ap_invoices_all
WHERE invoice_id = i_cur_invoice_id;
CURSOR cur_xla_ae_headers(i_cur_doc_sequence_id IN NUMBER,i_cur_doc_sequence_value IN NUMBER) IS
SELECT event_id,ae_header_id from xla_ae_headers
WHERE doc_sequence_id = i_cur_doc_sequence_id
AND doc_sequence_value = i_cur_doc_sequence_value;
BEGIN
FOR rec_invoice IN cur_invoice(p_invoice_id) LOOP
FOR rec_xla_ae_headers IN cur_xla_ae_headers(rec_invoice.doc_sequence_id,rec_invoice.doc_sequence_value) LOOP
INSERT INTO xla_ae_lines080421
SELECT * FROM xla_ae_lines
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
INSERT INTO xla_ae_headers080421
SELECT * FROM xla_ae_headers
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
DELETE xla_ae_lines
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
DELETE xla_ae_headers
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
END LOOP;
END LOOP;
UPDATE xla_events
SET event_status_code = 'U',process_status_code = 'U'
WHERE event_id IN (SELECT event_id FROM xla_events e
WHERE e.application_id = 200
AND e.event_status_code ='P'
AND NOT EXISTS (SELECT 1 FROM xla_ae_headers h
WHERE e.event_id = h.event_id ));
COMMIT;
END;
PROCEDURE fix_payments(p_payment_num IN VARCHAR2) IS
CURSOR cur_xla_ae_headers(i_cur_payment_num IN NUMBER) IS
SELECT event_id,ae_header_id from xla_ae_headers
WHERE description LIKE '%'||i_cur_payment_num||'%'
AND je_category_name = 'Payments';
BEGIN
FOR rec_xla_ae_headers IN cur_xla_ae_headers(p_payment_num) LOOP
INSERT INTO xla_ae_lines080421
SELECT * FROM xla_ae_lines
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
INSERT INTO xla_ae_headers080421
SELECT * FROM xla_ae_headers
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
DELETE xla_ae_lines
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
DELETE xla_ae_headers
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
END LOOP;
UPDATE xla_events
SET event_status_code = 'U',process_status_code = 'U'
WHERE event_id IN (SELECT event_id FROM xla_events e
WHERE e.application_id = 200
AND e.event_status_code ='P'
AND NOT EXISTS (SELECT 1 FROM xla_ae_headers h
WHERE e.event_id = h.event_id ));
COMMIT;
END;
END;
/
--=============================================================================
处理应收事务处理不能创建会计分录例子:
贷项通知单11040000002921不能创建会计分录
通过界面找到贷项通知单11040000002921对应的原始**11040000002801,
发现11040000002801没有创建会计分录,在界面上将11040000002801创建会计分录成功。
贷项通知单11040000002921按完成按钮后,发现不能保存,做跟踪约束值,
发现跟踪文件的最后几句
*******************************************************************************
SELECT AE.EVENT_ID , AE.EVENT_DATE , AE.EVENT_STATUS_CODE, AE.EVENT_TYPE_CODE
FROM
XLA_EVENTS AE, XLA_TRANSACTION_ENTITIES_UPG XT, RA_CUSTOMER_TRX_ALL TRX
WHERE TRX.CUSTOMER_TRX_ID = :B3 AND NVL(XT.SOURCE_ID_INT_1,-99) =
TRX.CUSTOMER_TRX_ID AND XT.ENTITY_CODE = 'TRANSACTIONS' AND XT.LEDGER_ID =
TRX.SET_OF_BOOKS_ID AND XT.ENTITY_ID = AE.ENTITY_ID AND XT.APPLICATION_ID =
222 AND AE.APPLICATION_ID = 222 AND NVL(AE.EVENT_DATE, TO_DATE('01-01-1900',
'DD-MM-YYYY')) = :B2 AND AE.EVENT_STATUS_CODE <> 'P' AND :B1 =
AE.EVENT_TYPE_CODE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 11 2 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 11 2 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44 (recursive depth: 1)
*******************************************************************************
SELECT MESSAGE_TEXT, MESSAGE_NUMBER, TYPE, FND_LOG_SEVERITY, CATEGORY,
SEVERITY
FROM
FND_NEW_MESSAGES M, FND_APPLICATION A WHERE :B3 = M.MESSAGE_NAME AND :B2 =
M.LANGUAGE_CODE AND :B1 = A.APPLICATION_SHORT_NAME AND M.APPLICATION_ID =
A.APPLICATION_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44 (recursive depth: 1)
********************************************************************************
ROLLBACK TO AR_PAYMENT_SCHEDULE
SELECT AE.EVENT_ID , AE.EVENT_DATE , AE.EVENT_STATUS_CODE, AE.EVENT_TYPE_CODE FROM XLA_EVENTS AE, XLA_TRANSACTION_ENTITIES_UPG XT, RA_CUSTOMER_TRX_ALL TRX WHERE TRX.CUSTOMER_TRX_ID = :B3 AND NVL(XT.SOURCE_ID_INT_1,-99) = TRX.CUSTOMER_TRX_ID AND XT.ENTITY_CODE = 'TRANSACTIONS' AND XT.LEDGER_ID = TRX.SET_OF_BOOKS_ID AND XT.ENTITY_ID = AE.ENTITY_ID AND XT.APPLICATION_ID = 222 AND AE.APPLICATION_ID = 222 AND NVL(AE.EVENT_DATE, TO_DATE('01-01-1900','DD-MM-YYYY')) = :B2 AND AE.EVENT_STATUS_CODE <> 'P' AND :B1 = AE.EVENT_TYPE_CODE
END OF STMT
PARSE #358:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4237611529714
BINDS #358:
kkscoacd
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110ad52a0 bln=22 avl=04 flg=09
value=22716
Bind#1
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=110ad52d0 bln=07 avl=07 flg=09
value="5/23/2008 0:0:0"
Bind#2
oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=871 siz=32 off=0
kxsbbbfp=110ad4a60 bln=32 avl=09 flg=09
value="CM_CREATE"
将具体参数值代入sql语句,得到:
SELECT AE.EVENT_ID, AE.EVENT_DATE, AE.EVENT_STATUS_CODE, AE.EVENT_TYPE_CODE
FROM XLA_EVENTS AE,
XLA_TRANSACTION_ENTITIES_UPG XT,
RA_CUSTOMER_TRX_ALL TRX
WHERE TRX.CUSTOMER_TRX_ID = 22716
AND NVL(XT.SOURCE_ID_INT_1, -99) = TRX.CUSTOMER_TRX_ID
AND XT.ENTITY_CODE = 'TRANSACTIONS'
AND XT.LEDGER_ID = TRX.SET_OF_BOOKS_ID
AND XT.ENTITY_ID = AE.ENTITY_ID
AND XT.APPLICATION_ID = 222
AND AE.APPLICATION_ID = 222
AND NVL(AE.EVENT_DATE, TO_DATE('01-01-1900', 'DD-MM-YYYY')) = to_date('2008-05-23 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND AE.EVENT_STATUS_CODE <> 'P'
AND 'CM_CREATE'= AE.EVENT_TYPE_CODE
也就是说在按完成按钮是不应该出现以上SQL语句有值,即XLA_EVENTS.EVENT_STATUS_CODE <> 'P'的记录应该不存在
措施:
1.AE.EVENT_STATUS_CODE,ae.process_status_code暂时都改为'P',
然后再按完成按钮,这次保存成功。
2.将AE.EVENT_STATUS_CODE,ae.process_status_code都改回'U',
检查 xla_ae_headers,xla_ae_lines是否有数据,
SELECT * FROM xla_ae_headers--no row
WHERE event_id IN (162517,162518)
SELECT * FROM xla_ae_lines--no row
在界面上将11040000002801创建会计分录成功
不能创建会计分录
--------------------------------------------------------------------------------
打补丁:6826219和6901404
处理原理:
如果创建会计分录失败,只要把xla_events.event_status_code和xla_events.process_status_code都改成'U',
然后提交<<创建会计科目>>请求或者直接在**界面创建会计分录,
创建会计分录程序不管xla_ae_headers和xla_ae_lines是否有与xla_events相应记录,
都会重新创建xla_ae_headers和xla_ae_lines记录
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子一:
create table xla_events_bkp
as select * from xla_events
where event_id in (select event_id from xla_events e
where e.application_id = 200
and e.event_status_code ='P'
and not exists ( select 1 from xla_ae_headers h
where e.event_id = h.event_id ));
UPDATE xla_events
SET event_status_code = 'U',
process_status_code = 'U'
WHERE event_id IN (select event_id from xla_events e
where e.application_id = 200
and e.event_status_code ='P'
and not exists ( select 1 from xla_ae_headers h
where e.event_id = h.event_id ));
运行<<创建会计科目>>请求
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子二:
**号200803005(已取消) 部分创建会计分录,付款号1102000006 不能创建会计分录
因为先有**再有付款,所以思路
1.先试图创建**的会计分录
SELECT invoice_num,invoice_id,doc_sequence_id,doc_sequence_value FROM ap_invoices_all
WHERE invoice_num = '200803005'
select event_id,ae_header_id,a.* from xla_ae_headers a
WHERE doc_sequence_id = 130
AND doc_sequence_value = '210401983'
SELECT event_id,a.* FROM xla_ae_headers a
WHERE event_id IN (70300,80395)
找到那条xla_ae_headers记录没有创建会计分录的event_id=70300,ae_header_id=57016
DELETE FROM xla_ae_lines
WHERE ae_header_id = 57016
DELETE FROM xla_ae_headers
WHERE ae_header_id = 57016
UPDATE xla_events
SET event_status_code = 'U',
process_status_code = 'U'
WHERE event_id = 70300
运行<<创建会计科目>>请求
2.再试图创建付款的会计分录
SELECT * FROM xla_ae_headers
WHERE DESCRIPTION LIKE '02000006%'
AND je_category_name = 'Payments'
DELETE xla_ae_lines
WHERE ae_header_id IN (101270,101271)
DELETE xla_ae_headers
WHERE ae_header_id IN (101270,101271)
UPDATE xla_events
SET event_status_code = 'U',
process_status_code = 'U'
WHERE event_id IN (70302,80394)
运行<<创建会计科目>>请求
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子三
ET1104应付 **号8000480849/859/862没有创建会计分录的原因是:
该**行1和 **行2 已放弃.
损益行中的帐户无效。如果您已定义损益日记帐行类型,请在日记帐行定义中检查附加至该类型的帐户推导规则。否则,请检查映射至会计属性"汇兑收益帐户"和"汇兑损失帐户"
的来源值。
无法创建帐户,因为 &OWNER 拥有的帐户推导规则 &COMPONENT_NAME 的所有条件均不满足。请更新 &COMPONENT_NAME 的条件,或分配其它帐户推导规则至 &PAD_OWNER 拥有的
应用产品会计定义 &PAD_NAME.
子分类帐会计无法使用 &OWNER 拥有的帐户推导规则 &COMPONENT_NAME 导出会计科目代码组合。请复核帐户推导规则,并确保对于为此事务处理传送的来源值,此规则可以导出
有效的会计科目弹性域组合。
请根据以上错误信息,检查 当**行被放弃时,汇兑损益 相关的帐户推导规则 ,映射至会计属性"汇兑收益帐户"和"汇兑损失帐户"的来源值,
但是顾问没有检查出来设置是否有问题。这也许是oracle的一个bug.
临时处理方法:
直接在表中删除了放弃行的**分配行和**行
CREATE TABLE AP_INVOICE_DISTRIBUTIONS_0427
AS SELECT * FROM AP_INVOICE_DISTRIBUTIONS_all
WHERE invoice_id = 15761 and invoice_line_number IN (1,2)
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_0427
CREATE TABLE AP_INVOICE_LINES_0427 as
SELECT * FROM AP_INVOICE_LINES_all
WHERE invoice_id = 15761
AND line_number IN (1,2)
SELECT * FROM AP_INVOICE_LINES_0427
DELETE FROM AP_INVOICE_DISTRIBUTIONS_all
WHERE invoice_id = 15761 and invoice_line_number IN (1,2)
DELETE FROM AP_INVOICE_LINES_all
WHERE invoice_id = 15761
AND line_number IN (1,2)
直接在**界面创建会计分录
最终处理方法:建议用户提tar由oracle官方解决
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子四:
这张**2008030888.不能创建会计分录的原因是 没有录入**的分配信息
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子五:
标准**核销预付款**后,部分会计分录不能创建。已入帐显示:部分
措施:
先在**界面上验证相关**,然后在**界面上创建会计分录
-----------------------------------------------------------------------------
处理应付**不能创建会计分录例子六:
骆勇平员工由于EBS bug,再导入费用报表后,产生两个骆勇平供应商,经过删除其中一个供应商和供应商地点后
产生的出来会计分录信息居然还有被删除供应商的ID,供应商地点ID
措施:
找到了xla_ae_lines.party_id(这里找到有些会计分录的创建是根据以前会计分录记录来创建的,比如ref_event_id,ref_ae_header_id)
于是将xla_ae_lines.party_id,xla_ae_lines.party_site_id统统改成了现在的骆勇平供应商ID和供应商地点ID
运行cux_fix_invoice_accounting_pkg.fix_invoices程序,修正数据
运行<<创建会计科目>>请求
----------------------------------------------------------------------------
修正数据的程序包cux_fix_invoice_accounting_pkg:
CREATE OR REPLACE PACKAGE cux_fix_invoice_accounting_pkg IS
PROCEDURE fix_invoices(p_invoice_id IN NUMBER);
PROCEDURE fix_payments(p_payment_num IN VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY cux_fix_invoice_accounting_pkg IS
PROCEDURE fix_invoices(p_invoice_id IN NUMBER) IS
CURSOR cur_invoice(i_cur_invoice_id IN NUMBER) IS
SELECT doc_sequence_id,doc_sequence_value FROM ap_invoices_all
WHERE invoice_id = i_cur_invoice_id;
CURSOR cur_xla_ae_headers(i_cur_doc_sequence_id IN NUMBER,i_cur_doc_sequence_value IN NUMBER) IS
SELECT event_id,ae_header_id from xla_ae_headers
WHERE doc_sequence_id = i_cur_doc_sequence_id
AND doc_sequence_value = i_cur_doc_sequence_value;
BEGIN
FOR rec_invoice IN cur_invoice(p_invoice_id) LOOP
FOR rec_xla_ae_headers IN cur_xla_ae_headers(rec_invoice.doc_sequence_id,rec_invoice.doc_sequence_value) LOOP
INSERT INTO xla_ae_lines080421
SELECT * FROM xla_ae_lines
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
INSERT INTO xla_ae_headers080421
SELECT * FROM xla_ae_headers
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
DELETE xla_ae_lines
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
DELETE xla_ae_headers
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
END LOOP;
END LOOP;
UPDATE xla_events
SET event_status_code = 'U',process_status_code = 'U'
WHERE event_id IN (SELECT event_id FROM xla_events e
WHERE e.application_id = 200
AND e.event_status_code ='P'
AND NOT EXISTS (SELECT 1 FROM xla_ae_headers h
WHERE e.event_id = h.event_id ));
COMMIT;
END;
PROCEDURE fix_payments(p_payment_num IN VARCHAR2) IS
CURSOR cur_xla_ae_headers(i_cur_payment_num IN NUMBER) IS
SELECT event_id,ae_header_id from xla_ae_headers
WHERE description LIKE '%'||i_cur_payment_num||'%'
AND je_category_name = 'Payments';
BEGIN
FOR rec_xla_ae_headers IN cur_xla_ae_headers(p_payment_num) LOOP
INSERT INTO xla_ae_lines080421
SELECT * FROM xla_ae_lines
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
INSERT INTO xla_ae_headers080421
SELECT * FROM xla_ae_headers
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
DELETE xla_ae_lines
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
DELETE xla_ae_headers
WHERE ae_header_id = rec_xla_ae_headers.ae_header_id;
END LOOP;
UPDATE xla_events
SET event_status_code = 'U',process_status_code = 'U'
WHERE event_id IN (SELECT event_id FROM xla_events e
WHERE e.application_id = 200
AND e.event_status_code ='P'
AND NOT EXISTS (SELECT 1 FROM xla_ae_headers h
WHERE e.event_id = h.event_id ));
COMMIT;
END;
END;
/
--=============================================================================
处理应收事务处理不能创建会计分录例子:
贷项通知单11040000002921不能创建会计分录
通过界面找到贷项通知单11040000002921对应的原始**11040000002801,
发现11040000002801没有创建会计分录,在界面上将11040000002801创建会计分录成功。
贷项通知单11040000002921按完成按钮后,发现不能保存,做跟踪约束值,
发现跟踪文件的最后几句
*******************************************************************************
SELECT AE.EVENT_ID , AE.EVENT_DATE , AE.EVENT_STATUS_CODE, AE.EVENT_TYPE_CODE
FROM
XLA_EVENTS AE, XLA_TRANSACTION_ENTITIES_UPG XT, RA_CUSTOMER_TRX_ALL TRX
WHERE TRX.CUSTOMER_TRX_ID = :B3 AND NVL(XT.SOURCE_ID_INT_1,-99) =
TRX.CUSTOMER_TRX_ID AND XT.ENTITY_CODE = 'TRANSACTIONS' AND XT.LEDGER_ID =
TRX.SET_OF_BOOKS_ID AND XT.ENTITY_ID = AE.ENTITY_ID AND XT.APPLICATION_ID =
222 AND AE.APPLICATION_ID = 222 AND NVL(AE.EVENT_DATE, TO_DATE('01-01-1900',
'DD-MM-YYYY')) = :B2 AND AE.EVENT_STATUS_CODE <> 'P' AND :B1 =
AE.EVENT_TYPE_CODE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 11 2 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 11 2 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44 (recursive depth: 1)
*******************************************************************************
SELECT MESSAGE_TEXT, MESSAGE_NUMBER, TYPE, FND_LOG_SEVERITY, CATEGORY,
SEVERITY
FROM
FND_NEW_MESSAGES M, FND_APPLICATION A WHERE :B3 = M.MESSAGE_NAME AND :B2 =
M.LANGUAGE_CODE AND :B1 = A.APPLICATION_SHORT_NAME AND M.APPLICATION_ID =
A.APPLICATION_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44 (recursive depth: 1)
********************************************************************************
ROLLBACK TO AR_PAYMENT_SCHEDULE
SELECT AE.EVENT_ID , AE.EVENT_DATE , AE.EVENT_STATUS_CODE, AE.EVENT_TYPE_CODE FROM XLA_EVENTS AE, XLA_TRANSACTION_ENTITIES_UPG XT, RA_CUSTOMER_TRX_ALL TRX WHERE TRX.CUSTOMER_TRX_ID = :B3 AND NVL(XT.SOURCE_ID_INT_1,-99) = TRX.CUSTOMER_TRX_ID AND XT.ENTITY_CODE = 'TRANSACTIONS' AND XT.LEDGER_ID = TRX.SET_OF_BOOKS_ID AND XT.ENTITY_ID = AE.ENTITY_ID AND XT.APPLICATION_ID = 222 AND AE.APPLICATION_ID = 222 AND NVL(AE.EVENT_DATE, TO_DATE('01-01-1900','DD-MM-YYYY')) = :B2 AND AE.EVENT_STATUS_CODE <> 'P' AND :B1 = AE.EVENT_TYPE_CODE
END OF STMT
PARSE #358:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4237611529714
BINDS #358:
kkscoacd
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110ad52a0 bln=22 avl=04 flg=09
value=22716
Bind#1
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=110ad52d0 bln=07 avl=07 flg=09
value="5/23/2008 0:0:0"
Bind#2
oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=871 siz=32 off=0
kxsbbbfp=110ad4a60 bln=32 avl=09 flg=09
value="CM_CREATE"
将具体参数值代入sql语句,得到:
SELECT AE.EVENT_ID, AE.EVENT_DATE, AE.EVENT_STATUS_CODE, AE.EVENT_TYPE_CODE
FROM XLA_EVENTS AE,
XLA_TRANSACTION_ENTITIES_UPG XT,
RA_CUSTOMER_TRX_ALL TRX
WHERE TRX.CUSTOMER_TRX_ID = 22716
AND NVL(XT.SOURCE_ID_INT_1, -99) = TRX.CUSTOMER_TRX_ID
AND XT.ENTITY_CODE = 'TRANSACTIONS'
AND XT.LEDGER_ID = TRX.SET_OF_BOOKS_ID
AND XT.ENTITY_ID = AE.ENTITY_ID
AND XT.APPLICATION_ID = 222
AND AE.APPLICATION_ID = 222
AND NVL(AE.EVENT_DATE, TO_DATE('01-01-1900', 'DD-MM-YYYY')) = to_date('2008-05-23 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND AE.EVENT_STATUS_CODE <> 'P'
AND 'CM_CREATE'= AE.EVENT_TYPE_CODE
也就是说在按完成按钮是不应该出现以上SQL语句有值,即XLA_EVENTS.EVENT_STATUS_CODE <> 'P'的记录应该不存在
措施:
1.AE.EVENT_STATUS_CODE,ae.process_status_code暂时都改为'P',
然后再按完成按钮,这次保存成功。
2.将AE.EVENT_STATUS_CODE,ae.process_status_code都改回'U',
检查 xla_ae_headers,xla_ae_lines是否有数据,
SELECT * FROM xla_ae_headers--no row
WHERE event_id IN (162517,162518)
SELECT * FROM xla_ae_lines--no row
在界面上将11040000002801创建会计分录成功
会计
帐户
处理
例子
供应商
界面
规则
供应
检查
运行
成功
信息
地点
按钮
损益
措施
数据
来源
程序
通知单
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
国家网络安全问题有哪些
中金金采网络技术公司
网络安全法禁止危害网络安全
哪里有网络安全的学校吗
软件开发需求分析流程图
广西农村信用社软件开发
弹性云服务器怎么防护
国泰安数据库怎么查公司董事信息
包头网络技术哪家强
湖北通用软件开发要多少钱
建立人事档案数据库
网络安全隔离要求
网络安全周手抄报简笔画
在公安局网络安全部门
服务器获得客户端ip
徐州信息网络技术报价
知原服务器租赁
维护网络安全的政治教育
医鹿九价提示服务器被挤爆了
山西定制网络技术服务售价
留言板部分需要设计数据库吗
关系数据库怎么取空值
网络安全教程免费获取网站
服务器的配置与个人的电脑相比
邮件群发服务器
部队网络安全教育视频
论坛数据库表
什么是双网卡服务器
hibernate数据库表
网络技术公司开多久才能赚钱