在R12版本中月末关帐时经常会出现关不了的情况,而系统的异常报表的信息太过简单且不完全。结合项目本身发生的情况,做了以下的总结,希望能对公司其他R12项目有所启示。
R12月度关帐的要点:
检查SLA传送给GL的数据是否重复和丢失。
-- 检查SLA传送给GL的数据重复
select aeh.gl_transfer_date,
aeh.accounting_date,
aeh.description,
aeh.doc_sequence_value,
cc.concatenated_segments,ael.*
from xla_ae_lines ael, xla_ae_headers aeh, gl_code_combinations_kfv cc
where ael.gl_sl_link_id in
(
select gl_sl_link_id
from APPS.gl_import_references
where je_header_id in
(select je_header_id
from APPS.gl_je_headers
where period_name = '2008-06')
group by gl_sl_link_id
having count(*) = 2
)
and ael.ae_header_id=aeh.ae_header_id
and ael.code_combination_id=cc.code_combination_id
如果存在则为传送给GL的数据重复。
-- 检查SLA传送给GL的数据是否缺失
SELECT xe.event_id, xh.ae_header_id
FROM xla_events xe, xla_ae_headers xh
WHERE xe.event_id = xh.event_id
AND xe.application_id = xh.application_id
AND xh.accounting_entry_status_code = 'F'
AND xe.event_status_code = 'P'
AND xh.application_id = application_id -- (like 200 for AP, 222 for AR etc.,)
AND xh.upg_batch_id IS NULL
AND xh.ledger_id = ledger_id -- give the ledger_id
AND NOT EXISTS
(SELECT 1
FROM xla_ae_lines xl
WHERE xl.ae_header_id = xh.ae_header_id
AND xl.application_id = xh.application_id);
这个问题在oracle Doc ID: Note:729296.1 Subject: SLA: Accounting Data Missing from the SLA Tables
碰到这样的问题需要installed R12 Fin RUP5 or the Subledger Accounting Critical Patches 这个在Doc已经
有了详细的介绍。
关闭AP的会计期:
关闭AP的会计期的check主要是集中在xla这一块。
SELECT xte.*
FROM xla.xla_events xle,
xla.xla_transaction_entities xte,
gl_ledger_relationships glr1,
gl_ledger_relationships glr2,
xla.xla_ledger_options xlo
WHERE xle.entity_id = xte.entity_id
AND xle.application_id = xte.application_id
AND xle.event_date BETWEEN to_date('2008-08-01', 'YYYY-MM-DD') AND
to_date('2008-08-30', 'YYYY-MM-DD')
AND glr2.target_ledger_id = p_ledger_id
AND glr2.source_ledger_id = glr1.source_ledger_id
AND glr2.application_id = g