End of Month processing is an important piece of the production schedule organizations run to “close” a month. Most often, it takes an organization a few days after the official calendar end of a month to finish entering transactions and data for that month. As such, organizations tend to run End of Month processing the first or second weekend of the month. Most organizations run it on the weekend because it takes several hours to complete.
Because of the type of updates End of Month does, it is important that organizations not use Team Approach for ANYTHING else while End of Month is running. Occasionally, because of scheduling snafus, organizations ask us what they can do if EOM is running during the day. Typically, users can go into TA, but should not Post Batches, update or query Account Activities information, etc. But it’s best they don’t use TA at all, if possible. We simply can’t guarantee problems will not occur.
Major, major problems can result if organizations post batches while End of Month is running. End of Month sequentially analyzes records in Account Activities to see if a change in membership status is warranted – for example, moving someone from Active to Lapsed. If a batch posts while End of Month is running, you may end up with 2 accounts, with the exact same expiration date, with 2 different statuses in Account Activities. Very bad.
The good news is that TA prevents this from happening by not allowing batches to post while End of Month is running. It makes batch posting error out with a message along the lines of ‘error posting batches; end of month processing in progress’.
When problems occur with EOM
Here’s where we all come in: Fairly often, organizations don’t notice that End of Month processing does not complete, for example, if it encounters an error, or runs into backups. Then, the organization attempts to post batches, and gets the error. Typically, they then contact support, and these kinds of support requests are often very urgent, typically a priority 1 or 2.
The good news is that they are easy to troubleshoot. The biggest thing to remember is that if EOM did not actually get very far, we can “turn off” the lock that prevents the organization from posting, so they can post their batches, and try EOM again the next night.
Determining where the problem occurred
To see how far it got:
1. check the value of the EOM variables:
Select parameter_name, parameter_value From menu_operation_parameters Where parameter_type = 'SYSTEM' And parameter_name like '%EOM%';
Both select statements will return 2 values: EOM IN PROGRESS – either a Y or an N EOM ACCOUNT ID
The EOM IN PROGRESS flag, when set to Y, prevents batch posting from running. EOM ACCOUNT ID stores the last Account EOM updated. IF THIS VALUE IS 0 or null, EOM either completed the financial portion of its updates, or did not perform any updates.
2. check the listener log record of what the process did
SELECT * FROM schedule_operations_log WHERE menu_operation LIKE '%EOM%' AND run_date>'31-AUG-03'--or the last day of the month before the issue was reported
carefully review the log for EOM. If the log resembles this portion below, then EOM did not make any updates – it died very soon after starting. In this case, if EOM ACCOUNT ID is 0 or null, you can set the EOM IN PROGRESS flag to N and let the client post batches.
Calling PROCEDURE: SYEOMPRC - END OF Month Processing ON 09/09/2003 AT 13:35:39 END OF fiscal month 3/2004 (calendar month 09/03). Setting up data FOR account activities updates (09/09/03 13:35:39)... ORA-01403: no data FOUND
However, if the listener log looks like this, then the financial portion (the important part) has completed, and the organization has closed the month successfully. See the line below reading from the log section 2 paragraphs down:
Finished END OF Month Financial UPDATE WITH no errors. Month IS closed. Fiscal DATE IS now 3/2004 (calendar month 09/03).
In this situation, we want to tell the client that End of Month errored in the non-financial maintenance section, and that, as that section is far less important, it is ok to let them post batches, but that they should not restart EOM as it would start closing the next month (very bad).
Calling PROCEDURE: SYEOMPRC - END OF Month Processing ON 08/05/2003 AT 08:00:06 END OF fiscal month 2/2004 (calendar month 08/03). Setting up data FOR account activities updates (08/05/03 08:00:07)... Beginning account activities updates (08/05/03 08:00:11)... Account activities updated through Account ID 100000 (08/05/03 08:00:11). Account activities updated through Account ID 138940 (08/05/03 08:00:33). Account activities updated through Account ID 1058361 (08/05/03 08:00:44). Account activities updated through Account ID 1532296 (08/05/03 08:00:44). Account activities updated through Account ID 2083785 (08/05/03 08:01:14). Account activities updated through Account ID 2555561 (08/05/03 08:01:14). Account activities updated through Account ID 70002102 (08/05/03 08:01:15). Finished account activities updates (08/05/03 08:01:15). Updated activity TYPE data AND SYSTEM DATE settings. Finished END OF Month Financial UPDATE WITH no errors. Month IS closed. Fiscal DATE IS now 3/2004 (calendar month 09/03). Starting END-OF-Month non-financial maintenance AT 08/05/03 08:01:15. 0 guide interactions deleted. Finished END OF month non-financial maintenance TO age RANGE UPDATE. Error ORA-1562 occurred IN END OF month non-financial maintenance. Unhandled EXCEPTION IN syeomprc: Failed TO extend ROLLBACK SEGMENT. Contact SYSTEM manager. Completed PROCEDURE: SYEOMPRC - END OF Month Processing ON 08/05/2003 AT 08:05:37 Operation rescheduled WITH NEW RUN DATE 09/05/2003 08:00:00. Waiting FOR a request...
3. In some cases we have seen the information in the listener log appears incomplete or inconsistent. You can confirm whether the fiscal months was closed by checking the date updated on the Fiscal Month system parameter.
select * from menu_operation_parameters where parameter_type = 'SYSTEM' and parameter_name = 'FISCAL_MONTH';