库存会计期间关闭出错,提示未达结束的日期, Error: Period end date has not been reached

- Problem Statement:
On 11.5.10.2 in Production:
When attempting to close May accounting period, May period shows a status of Processing. There are no pending transactions of any kind for May. If user try to change the Status of May, get the message:

ERROR
----------
APP-INV-05203: Closing current accounting period...
followed by
APP-INV-05485: Period Close failed

-- Steps To Reproduce:
The issue can be reproduced at will with the following steps:
1. Responsibility: Inventory
2. Navigation: Accounting Close Cycle 
3. Inventory Accounting Periods 
4. Change Status

 

CAUSE

The concurrent managers going down caused the period close concurrent processing to be stuck as in process and error out with APP-INV-05203

 

SOLUTION

-- To implement the solution, please execute the following steps:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Run the following scripts in a TEST environment first:

a) Backup the table org_acct_periods

create table org_acct_periods_83192
as select * from org_acct_periods;


b) Find affected periods.

select organization_id, acct_period_id, period_close_date
from org_acct_periods
where open_flag = 'P';


c) Verify there are no records in MTL_PER_CLOSE_DTLS, MTL_PERIOD_SUMMARY,
CST_PERIOD_CLOSE_SUMMARY tables
for the affected periods and mark the period as unsummarized.


select count(*)
from MTL_PER_CLOSE_DTLS
where organization_id in (<org_ids from (1b)>)
and acct_period_id in (<period_ids from (1b)>);

select count(*)
from MTL_PERIOD_SUMMARY
where organization_id in (<org_ids from (1b)>)
and acct_period_id in (<period_ids from (1b)>);

select count(*)
from CST_PERIOD_CLOSE_SUMMARY
where organization_id in (<org_ids from (1b)>)
and acct_period_id in (<period_ids from (1b)>);

[If there are records in the above, make a backup copy of the table and
delete the records, first from MTL_PER_CLOSE_DTLS then from
MTL_PERIOD_SUMMARY and then from CST_PERIOD_CLOSE SUMMARY]

Run the following SQL to mark the periods as 'Unsummarized':
.
UPDATE org_acct_periods
SET summarized_flag = 'N'
WHERE organization_id in (<org_ids from (1b)>)
and acct_period_id in (<period_ids from (1b)>);
AND summarized_flag = 'Y';

d) Run the following script to reset the period status so that the Close accounting period process can be re-submitted for them:

update org_acct_periods
set open_flag = 'Y',
period_close_date = NULL
where organization_id in (<org_ids from (1b)>) 
and acct_period_id in (<period_ids from (1b)>) 
and open_flag = 'P';


3. Once the scripts complete, confirm that the data is corrected.
You can use the following SQL to confirm:

select count(*)
from org_acct_periods
where open_flag = 'P';

4. If you are satisfied with the results, issue a commit.

5. Re-submit the Close accounting period process for one organization at a time. Confirm that it
completes successfully. Be sure it launches a Transfer transactions to GL concurrent request which should also complete normally

6. Confirm that the data is corrected when viewed in the Oracle Applications.

You can use the following steps:
Accounting Close Cycle > Inventory Accounting Periods
Inventory Accounting Period


7. Migrate the solution as appropriate to other environments