R12月末关帐的异常检查和处理

转贴一个siman.he 的经典帖子
在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 = glr1.application_id
AND glr1.target_ledger_id = xlo.ledger_id
AND xle.application_id = xlo.application_id
AND xlo.capture_event_flag = 'Y'
AND (glr1.target_ledger_id = xte.ledger_id OR
glr1.primary_ledger_id = xte.ledger_id)
AND (glr1.relationship_type_code = 'SUBLEDGER' OR
(glr1.target_ledger_category_code = 'PRIMARY' AND
glr1.relationship_type_code = 'NONE'))
AND glr2.application_id = 101
AND xte.application_id = p_application_id
AND xle.event_status_code IN ('I', 'U');


SELECT xte.*
FROM xla.xla_ae_headers aeh,
xla.xla_transaction_entities xte,
gl_ledger_relationships glr1,
gl_ledger_relationships glr2
WHERE aeh.ledger_id = glr2.target_ledger_id
AND glr2.source_ledger_id = glr1.source_ledger_id
AND glr2.application_id = glr1.application_id
AND glr1.target_ledger_id = p_ledger_id
AND glr1.application_id = 101
AND xte.entity_id = aeh.entity_id
AND xte.application_id = aeh.application_id
AND aeh.gl_transfer_status_code IN ('N', 'E')
AND aeh.accounting_date BETWEEN to_date('2008-07-01', 'YYYY-MM-DD') AND
to_date('2008-07-31', 'YYYY-MM-DD')
AND xte.application_id = p_application_id ;

当上述的2SQL中存在值时，这个时候AP的会计期是关闭不了的。我们可以通过
xla.xla_transaction_entities.source_id_int_1，ENTITY_CODE 去找到相应的子帐数据。
如果ENTITY_CODE = 'AP_PAYMENTS' 则source_id_int_1 = AP_CHECKS_ALL.CHECK_ID
如果ENTITY_CODE = 'AP_INVOICES' 则source_id_int_1 = AP_INVOICES_ALL.INVOICE_ID
如果ENTITY_CODE = 'TRANSACTIONS' 则source_id_int_1 = RA_CUSTOMER_TRX_ALL.customer_trx_id
如果ENTITY_CODE = 'RECEIPTS' 则source_id_int_1 = AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID
如果ENTITY_CODE = 'ADJUSTMENTS' 则source_id_int_1 = AR_ADJUSTMENTS_ALL.ADJUSTMENT_ID

另外Oracle也提供了AP的异常检查的 Doc ID: Note:437422.1
Subject: R12 Troubleshooting Closing Periods in Payables
主要是以下几个方面：
•All payment batches must be confirmed
•All transactions must be accounted
•All accounting entries must be transferred to general ledger
•All future dated payments which have reached maturity in the accounting period must have their
status updated to negotiable and be accounted
详细细节请参考Note:437422.1 以及上面的SQL语句就可以找到问题。其中check AP会计期的程序其实是调用
XLA_EVENTS_PUB_PKG.PERIOD_CLOSE程序真正的检查程序是XLA_PERIOD_CLOSE_EXP_PKG.check_period_close 。



关闭AR的会计期：
关闭AR的会计期时，Oracle没有象关闭AP会计期那样严格，只是检查收款和发票的核销。

/* check for Revenue recognition */
SELECT COUNT(1)
INTO temp
FROM dual
WHERE EXISTS (SELECT /*+ ORDERED USE_NL(sys_org gld ct) */
'x'
FROM (SELECT org_id
FROM ar_system_parameters_all
WHERE set_of_books_id = :control.set_of_books_id
AND accounting_method 'CASH') sys_org,
ra_cust_trx_line_gl_dist_all gld,
ra_customer_trx_all ct
WHERE gld.account_class = 'REC'
AND gld.latest_rec_flag = 'Y'
AND gld.account_set_flag = 'Y'
AND gld.gl_date BETWEEN :gl_period_statuses.start_date AND
:gl_period_statuses.end_date
AND ct.customer_trx_id = gld.customer_trx_id
AND ct.complete_flag = 'Y'
AND gld.org_id = sys_org.org_id
AND ct.org_id = gld.org_id);

/*** 'Unposted Items Exist' ***/
SELECT COUNT(1)
INTO temp
FROM dual
WHERE EXISTS (SELECT 'x'
FROM ar_adjustments_all adj
WHERE adj.posting_control_id = -3
AND adj.gl_date BETWEEN :gl_period_statuses.start_date AND
:gl_period_statuses.end_date
AND nvl(adj.postable, 'Y') = 'Y'
AND adj.org_id IN
(SELECT org_id
FROM ar_system_parameters_all
WHERE set_of_books_id = :control.set_of_books_id
AND accounting_method 'CASH'));

SELECT COUNT(1)
INTO temp
FROM dual
WHERE EXISTS
(SELECT 'x'
FROM ar_misc_cash_distributions_all
WHERE posting_control_id = -3
AND gl_date BETWEEN :gl_period_statuses.start_date AND
:gl_period_statuses.end_date
AND (org_id IS NULL OR
org_id IN
(SELECT org_id
FROM ar_system_parameters_all
WHERE set_of_books_id = :control.set_of_books_id)));

SELECT COUNT(1)
INTO temp
FROM dual
WHERE EXISTS (SELECT /*+ ORDERED USE_NL(sys_org gl ct) */
'x'
FROM (SELECT org_id
FROM ar_system_parameters_all
WHERE set_of_books_id = :control.set_of_books_id
AND accounting_method 'CASH') sys_org,
ra_cust_trx_line_gl_dist_all gl,
ra_customer_trx_all ct
WHERE gl.customer_trx_id = ct.customer_trx_id
AND ct.complete_flag = 'Y'
AND gl.account_set_flag = 'N'
AND gl.gl_date BETWEEN :gl_period_statuses.start_date AND
:gl_period_statuses.end_date
AND gl.posting_control_id = -3
AND gl.set_of_books_id = :control.set_of_books_id
AND ct.org_id = sys_org.org_id
AND gl.org_id = ct.org_id);

SELECT COUNT(1)
INTO temp
FROM dual
WHERE EXISTS (SELECT /*+ ORDERED USE_NL(sys_org ra ct) */
'x'
FROM (SELECT org_id
FROM ar_system_parameters_all
WHERE set_of_books_id = :control.set_of_books_id) sys_org,
ar_receivable_applications_all ra,
ra_customer_trx_all ct
WHERE ra.posting_control_id = -3
AND ra.gl_date BETWEEN :gl_period_statuses.start_date AND
:gl_period_statuses.end_date
AND nvl(ra.postable, 'Y') = 'Y'
AND (ra.customer_trx_id = ct.customer_trx_id OR
ra.applied_customer_trx_id = ct.customer_trx_id)
AND ra.org_id = sys_org.org_id
AND ra.org_id = ct.org_id);

SELECT COUNT(1)
INTO temp
FROM dual
WHERE EXISTS
(SELECT 'x'
FROM ar_cash_receipt_history_all crh
WHERE posting_control_id = -3
AND gl_date BETWEEN :gl_period_statuses.start_date AND
:gl_period_statuses.end_date
AND postable_flag = 'Y'
AND (crh.org_id IS NULL OR
crh.org_id IN
(SELECT org_id
FROM ar_system_parameters_all
WHERE set_of_books_id = :control.set_of_books_id)));

SELECT COUNT(1)
INTO temp
FROM dual
WHERE EXISTS
(SELECT 'x'
FROM ar_interim_cash_receipts_all icr
WHERE icr.gl_date BETWEEN :gl_period_statuses.start_date AND
:gl_period_statuses.end_date
AND (icr.org_id IS NULL OR
icr.org_id IN
(SELECT org_id
FROM ar_system_parameters_all
WHERE set_of_books_id = :control.set_of_books_id)));

SELECT COUNT(1)
INTO temp
FROM dual
WHERE EXISTS
(SELECT 'x'
FROM ar_transaction_history_all trh
WHERE trh.posting_control_id = -3
AND trh.gl_date BETWEEN :gl_period_statuses.start_date AND
:gl_period_statuses.end_date
AND trh.postable_flag = 'Y'
AND (trh.org_id IS NULL OR
trh.org_id IN
(SELECT sp.org_id
FROM ar_system_parameters_all sp
WHERE sp.set_of_books_id = :control.set_of_books_id)));

假如上面的Sql中有任何一个存在值，当期的会计期是关闭不了的。其实这些都是关闭AR
会计期的check条件。


关闭GL的会计期：
关闭GL的会计期，没有AP/AR条件那么苛刻，只要验证以下SQL就可以了

SELECT xte.*
FROM xla.xla_events xle,
xla.xla_transaction_entities xte,
gl_ledger_relationships glr1,
gl_ledger_relationships glr2,
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-07-01', 'YYYY-MM-DD') AND
to_date('2008-07-31', 'YYYY-MM-DD')
AND glr2.target_ledger_id = 2022
AND glr2.source_ledger_id = glr1.source_ledger_id
AND glr2.application_id = glr1.application_id
AND glr1.target_ledger_id = xlo.ledger_id
AND xle.application_id = xlo.application_id
AND xlo.capture_event_flag = 'Y'
AND (glr1.target_ledger_id = xte.ledger_id OR
glr1.primary_ledger_id = xte.ledger_id)
AND (glr1.relationship_type_code = 'SUBLEDGER' OR
(glr1.target_ledger_category_code = 'PRIMARY' AND
glr1.relationship_type_code = 'NONE'))
AND glr2.application_id = 101
AND xle.event_status_code IN ('I', 'U');

以上是在项目月结时碰到的异常情况，希望能对大家有所帮助。另注项目的版本为R12.0.4假如在fin升级到.5版本后部分异常会得到解决。