Monitoring Services: Sequences in MRP/CRP Are Approaching MAX_VALUE And Need To Be Reset To Avoid Errors in MRP Applications (Doc ID 2341211.1)

Last updated on SEPTEMBER 07, 2023

Applies to:

Symptoms

This monitoring service script checks for Sequences that are approaching MAX_VALUE in the Material Requirements Planning (MRP) application.

These are primarily processes that are run from Material Planner or Supply Chain Planning responsibilities - this is not for ASCP processes that run under the MSC schema /Application except when using Push Plan Information request to move data from MSC schema to MRP schema.

Why do we check these sequences?

• Sequences are used to create unique key on a table.
  e.g. Each DEMAND in an MRP plan has a unique DEMAND_ID in MRP_GROSS_REQUIREMENTS
• MRP plans are run per your business requirements and over a number of years, the sequence MRP_GROSS_REQUIREMENTS_S will increase until it reaches the MAX_VALUE of 2147483647 (2gb)
  This could happen every year in very large, active organizations or could take 10 or more years in smaller organizations.
• Once the MAX_VALUE is reached, then plan or process using the sequence will fail and generate error in the log file

 What is purpose of this Monitoring Service?

• Over many years, customers have raised issues with these particular sequences and there are notes on how to repair and reset system to avoid the errors.
  This Monitoring Unit service will help prevent future errors by warning that clean up and maintenance is required and providing outputs for analysis - Plus steps to perform that will prevent the error condition from causing failures in business processes.
• By checking the sequence LAST_NUMBER and comparing to MAX_VALUE we can determine the sequence has used about 90% of MAX_VALUE
• The check will trigger this event when the sequence.LAST_NUMBER > 194748364 - which means the sequence has 200,000,000 values left to consume before entering the error condition.

This information will raise the question -- How long will it take for the sequence to use up last 200 million values and reach MAX_VALUE?

The time horizon to MAX_VALUE cannot be answered without tracking the sequence usage over time.
But generally 200,000,000 should allow for sufficient time to perform the steps below to clean up old data, then reset the sequence and re-load/re-run current active schedules and plans
And the SQL to check data and monitor sequence is included in the solution notes.

Changes

 N/A

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.