Troubleshooting MRP Regeneration Freezes in Dynamics GP: Solutions and Best Practices

Updated: 07 Apr, 2025 • 0 • min read

Table of Contents

The Material Requirements Planning (MRP) Regeneration process within Microsoft Dynamics GP Manufacturing module is crucial for maintaining efficient production planning. However, users may encounter situations where the MRP regeneration process becomes unresponsive, locks up, or fails to complete. This article provides detailed solutions and best practices to troubleshoot and resolve these issues in Dynamics GP.

Symptoms¶

When executing the Material Requirements Planning (MRP) Regeneration process in Microsoft Dynamics GP 9.0 and later, you might experience one or more of the following symptoms:

The MRP Regeneration process halts and becomes unresponsive, appearing to freeze indefinitely.
The MRP Regeneration process locks up, preventing any further interaction with Dynamics GP.
The MRP Regeneration process initiates but does not complete, leaving the MRP planning incomplete.

MRP Regeneration Process

These symptoms can significantly disrupt manufacturing operations, leading to delays in production schedules and inaccurate material planning. Identifying the underlying cause is essential for effective resolution.

Cause¶

Several factors can contribute to MRP Regeneration process freezes in Dynamics GP. These causes can be broadly categorized into data configuration issues, system setup problems, and data integrity concerns. Understanding these potential causes is the first step towards effective troubleshooting.

Incorrect Down Day Constraints in MRP: The configuration of down day constraints in MRP preferences may conflict with the shop calendar settings, leading to processing errors.
Incorrect Low-Level Codes: Inaccurate low-level codes assigned to items can create circular dependencies in the Bill of Materials (BOM) calculations, causing the process to freeze.
Recursive Bill of Materials (BOM): A recursive BOM, where an item is a component of itself directly or indirectly through subassemblies, creates an infinite loop during MRP processing.
Multiple Records in MSSP0230 Table: The MRP Preferences Default table (MSSP0230) should contain only a single record. Multiple records can lead to conflicts and process interruptions.
Incorrect Company ID in MSSP0230 Table: An incorrect Company ID in the MSSP0230 table can prevent the MRP process from accessing the correct company data.
Locked Record in MRP Security: Security locks on MRP related records can block the regeneration process from accessing or modifying necessary data.
Sales, Purchase, or Manufacturing Orders Outside MRP Date Ranges: Orders with required dates falling outside the defined MRP date ranges can cause processing errors and freezes.

Resolution¶

Addressing MRP regeneration freezes requires a systematic approach, examining each potential cause and applying the corresponding resolution. The following sections detail specific resolutions for each cause mentioned above.

Resolution 1: Correcting Down Day Constraints in MRP¶

The Down Day Constraint setting in the MRP Preference Defaults window is critical for determining component lead times based on the shop calendar. A mismatch between this setting and the Company-wide Down Day settings in the Shop Calendar window can cause the MRP Regeneration process to lock up.

This issue arises specifically when:

The None option is selected under Company-wide Down Days settings in the Shop Calendar window.
The Down Day Constraint field in the MRP Preference Defaults window is set to any option other than None.

To resolve this conflict, ensure consistency between these settings based on your Dynamics GP version.

Microsoft Dynamics GP 10.0 and Later¶

Navigate to the Shop Calendar window: Tools > Microsoft Dynamics GP menu > Setup > Manufacturing > System Defaults > Calendar.
Verify the selected option under Company-wide Down Day settings. Note whether None, Sundays Only, or Saturdays and Sundays is selected.
Navigate to the MRP Preference Defaults window: Tools menu > Setup > Manufacturing > System Defaults > MRP.

Examine the Down Day Constraints dropdown list. Ensure the selected option is compatible with the Shop Calendar Down Days setting.

Shop Calendar Down Days	Compatible Down Day Constraints Options
None	None, Saturdays and Sundays, Sundays Only
Sundays Only	Made, Bought, Both, Saturdays and Sundays, Sundays Only
Saturdays and Sundays	Made, Bought, Both, Saturdays and Sundays, Sundays Only

Adjust the Down Day Constraints in the MRP Preference Defaults window to align with the Shop Calendar Down Days setting as per the table above.

Microsoft Dynamics GP 9.0¶

The steps for Microsoft Dynamics GP 9.0 are identical to Dynamics GP 10.0 and later versions for verifying and correcting the Down Day Constraints and Shop Calendar settings. Follow the steps outlined for Dynamics GP 10.0 to resolve this issue in Dynamics GP 9.0.

Resolution 2: Correcting Low-Level Codes¶

Low-level codes in the Item Engineering Data window define the deepest level of an item within any BOM. Incorrect low-level codes can disrupt the MRP process. Regularly running the MRP Low-Level Codes utility is crucial to maintain data integrity. This utility can be executed either within Microsoft Dynamics GP or manually using SQL.

Running the Low-Level Codes Utility in Microsoft GP¶

Access the MRP Low-Level Codes utility based on your Dynamics GP version:
- Microsoft Dynamics GP 10.0 and Later: Tools > Microsoft Dynamics GP menu > Utilities > Manufacturing > MRP Low-Level Codes.
- Microsoft Dynamics GP 9.0: Utilities > Tools menu > Manufacturing > MRP Low-Level Codes.
In the Maximum Number of Levels for any BOM field, enter 110. This value is typically sufficient for most BOM structures.
Click Generate to initiate the utility. The utility will recalculate and update the low-level codes for all items.

Running the Low-Level Code Utility in SQL Query Analyzer or SQL Server Management Studio¶

For manual execution via SQL, use the following script against the company database:

execute mbomLLCUtility 110

This SQL script performs the same function as the utility within Dynamics GP, recalculating and updating low-level codes. Ensure you have appropriate SQL permissions to execute this script.

Resolution 3: Addressing Recursive BOMs¶

A recursive BOM occurs when an item is defined as a component of itself, either directly or indirectly through a subassembly. This creates an infinite loop during MRP processing, leading to freezes. Identifying and resolving recursive BOMs is critical.

Identifying Direct Recursive BOMs¶

Execute the following SQL query against the company database using SQL Query Analyzer or SQL Server Management Studio:
```
Select * from BM010115 where CPN_I=PPN_I
```
This query searches the BOM component table (BM010115) for records where the component item number (CPN_I) is identical to the parent item number (PPN_I), indicating a direct recursive BOM.
If the query returns results, it confirms the existence of a direct recursive BOM. Before proceeding, ensure you have a complete backup of your database.
To remove the direct recursive BOM, execute the following SQL statement:
```
Delete BM010115 where PPN_I = CPN_I
```
This statement deletes the identified recursive BOM component record.

Identifying Indirect Recursive BOMs via Subassemblies¶

First, ensure that the Low-Level Code utility has been run as described in Resolution 2 to ensure accurate low-level codes.
Execute the following SQL query:
```
Select * from IVR10015 where LLC=110
```
This query checks the Item Vendor Relation table (IVR10015) for items with a low-level code (LLC) of 110. A low-level code of 110 (or close to the maximum level set in the Low-Level Code utility) can indicate a deep BOM structure that might contain a recursive loop via a subassembly.
If records are returned, the listed items are potential parent items involved in a recursive BOM through a subassembly.
To identify and remove the problematic subassembly:
1. Navigate to the Bill of Materials window in Dynamics GP: Cards menu > Manufacturing > Bill of Materials.
2. Enter the BOM number returned by the SQL query into the Item Number field.
3. Utilize the Tree View in the BOM window to expand each subassembly and examine the components within.
4. Locate and remove the subassembly that is causing the recursive loop (where the same parent item number appears as a component within a subassembly branch).

Resolution 4: Removing Multiple Records in Table MPPS0230¶

The MPPS0230 table, storing MRP Preference Defaults, should contain only one record per company database. Multiple records can lead to conflicts and MRP process failures.

Execute the following SQL query to check the number of records in MPPS0230:
```
Select * from MPPS0230
```
If the query returns more than one row, identify the incorrect or duplicate records. Carefully review the data in each row to determine which record(s) should be removed.
Delete the incorrect records from the MPPS0230 table using SQL DELETE statements. Exercise caution and ensure you are deleting the intended records.
After deleting extra records, proceed to Resolution 5 to verify the Company ID of the remaining record is correct.

Resolution 5: Correcting Incorrect Company ID in Table MPPS0230¶

An incorrect Company ID in the MPPS0230 table can prevent the MRP process from accessing the correct company-specific MRP preferences.

To determine the correct Company ID for your database, execute the following query against the DYNAMICS database:
```
Select * from SY01500
```
Locate the CMPANYID field corresponding to the CMPNYNAM field that matches the company experiencing MRP issues. This CMPANYID is the correct Company ID.
Execute the following query against the company database (the database experiencing MRP issues):
```
Select * from MPPS0230
```
Verify that the CMPANYID field in the MPPS0230 table matches the correct CMPANYID obtained from the SY01500 table in step 2.

If the Company ID in MPPS0230 is incorrect, execute the provided SQL script against the company database to update Company IDs across relevant tables. This script is comprehensive and updates Company IDs in various tables that might be affected.

/******************************************************************************/
/* Description: */
/* Updates any table that contains a company ID or database name value */
/* with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table */
/* */
/******************************************************************************/
if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables

declare @cStatement varchar(255)

declare G_cursor CURSOR for
select
case
when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else
'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+''''
end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE'

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
insert ##updatedTables select
substring(@cStatement,8,patindex('%set%',@cStatement)-9)
Exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

select [tableName] as 'Tables that were Updated' from ##updatedTables

This script iterates through database tables and updates Company ID and Database Name fields to ensure consistency with the DYNAMICS database settings.

Resolution 6: Removing Locked Records in MRP Security¶

Locked records in MRP security windows can prevent the MRP Regeneration process from proceeding. Regularly checking and clearing unnecessary locked records is a good practice.

Check for locked MRP sessions: Navigate to Transactions menu > Manufacturing > MRP > Security. This window displays active MRP sessions. If no MRP Regeneration process is running, this table should be empty.
If records exist in the MRP Security window and no MRP process is active, delete these records.
Check for locked Planned Orders: Navigate to Transactions menu > Manufacturing > MRP > Planned Order Security. This window shows planned orders locked by other processes.
If records exist in Planned Order Security and no MRP process is active, delete these records.
Verify other Manufacturing security windows for locked records:
- Manufacturing Order Security: Transactions menu > Manufacturing > Manufacturing Order > Security. Delete any inactive manufacturing order security records. Active records represent orders currently being worked on.
- Bill of Materials Security: Transactions menu > Manufacturing > Bill of Materials > Security. Delete any inactive BOM security records. BOMs are locked when being edited or open in a Manufacturing window.
- Routings Security: Transactions menu > Manufacturing > Routings > Security. Delete any inactive routing security records. Routings are locked when being edited or open in a Manufacturing window.
- WIP TRX Security: Transactions menu > Manufacturing > WIP > TRX Security. Delete any inactive WIP transaction security records. WIP transactions are locked when being processed or open in a Manufacturing window.
Only delete records in these security windows if you are certain the corresponding processes or user actions are no longer active and the locks are stale.

Resolution 7: Adjusting MRP Regeneration Date Ranges¶

The MRP Regeneration process relies on the DD040000 (Up_Days_Work) table, which contains valid “up days” (workdays) within a defined date range. If required dates for sales orders, purchase orders, or manufacturing orders fall outside this range, the MRP process can encounter issues and potentially freeze.

The DD040000 table is repopulated each time the MRP Regeneration process completes. The date range in this table is determined by the Run for duration specified in the MRP Regeneration window and the Include Past Due for prior Days setting in MRP System Defaults.

Increase the MRP Regeneration Run Duration:
1. Navigate to the MRP Regeneration window: Transactions menu > Manufacturing > MRP > Regeneration.
2. In the Run for field, increase the number of days, weeks, or months for which MRP is generated (e.g., double the current value).
3. Click Process to run the regeneration with the extended duration. Increasing the run duration will expand the date range in the DD040000 table.
Decrease the Past Due Order Inclusion Period:
1. Navigate to MRP System Defaults: Tools menu > Microsoft Dynamics GP menu > Setup > Manufacturing > System Defaults > MRP. (For Dynamics GP 9.0: Setup > Tools menu > Manufacturing > System Defaults > MRP).
2. In the Include Past Due for prior Days field, reduce the number of past days included in the MRP Regeneration process. Reducing this value limits the historical data considered, potentially resolving date range issues.
3. Run the MRP Regeneration process again after adjusting this setting.

If the issue persists after adjusting date ranges, further investigation using Dexsql.log might be necessary to pinpoint specific date-related errors.

Using Dexsql.log to Identify Invalid Demand Dates:
1. Enable Dexsql logging in the Dex.ini file by setting:
  - SQLLogSQLStmt=TRUE
  - SQLLogODBCMessages=TRUE
2. Run the MRP Regeneration process again.
3. After the process (whether it freezes or completes), locate and open the Dexsql.log file in the GP folder or GP Data folder.
4. Examine the bottom of the Dexsql.log file. Look for repeated SQL SELECT statements similar to the following, which indicate date range issues:
```
SELECT TOP 25 UPDAYS_I,SEQ_I,DEX_ROW_ID FROM WAVE.dbo.DD040000 WHERE (UPDAYS_I < 'YYYY.MM.DD') ORDER BY UPDAYS_I DESC
```
  The date ‘YYYY.MM.DD’ in the script represents a date in the past that is causing the issue. Note this date.
5. Investigate Manufacturing Orders with Start Dates Before the Problem Date: Execute the following SQL query, replacing ‘YYYY-MM-DD’ with the problematic date from the Dexsql.log:
```
Select * from WO010032 where MANUFACTUREORDERST_I<>8 and STRTDATE<'YYYY-MM-DD' and STRTDATE<>'1900-01-01'
```
  This query searches for manufacturing orders (WO010032) with a start date (STRTDATE) earlier than the problematic date and that are not in a status of 8 (Completed or Closed).
6. If results are returned, review the identified manufacturing orders in the Manufacturing Order Entry window (Transactions menu > Manufacturing Orders > Entry) and adjust the Start Date to a more current, valid date.
7. Investigate Manufacturing Order Components with Required Dates Before the Problem Date: Execute the following SQL query, again replacing ‘YYYY-MM-DD’ with the problematic date:
```
Select * from PK010033 where REQDATE<'YYYY-MM-DD' and REQDATE<>'1900-01-01' and MANUFACTUREORDER_I IN (select MANUFACTUREORDER_I from WO010032 where MANUFACTUREORDERST_I<>8)
```
  This query searches for manufacturing order components (PK010033) with a required date (REQDATE) earlier than the problematic date, associated with active manufacturing orders.
8. If results are returned, review the identified components in the Picklist window (Transactions menu > Manufacturing Orders > Picklist) for the corresponding manufacturing orders and adjust the Required Date to a more current, valid date.
9. After resolving the date issues, disable Dexsql logging by setting the following back to FALSE in the Dex.ini file:
  - SQLLogSQLStmt=FALSE
  - SQLLogODBCMessages=FALSE

By systematically addressing these potential causes and applying the resolutions, you can effectively troubleshoot and resolve MRP Regeneration freezes in Dynamics GP, ensuring smooth and accurate manufacturing planning processes.

If you found these troubleshooting steps helpful or have further questions about MRP Regeneration in Dynamics GP, please feel free to leave a comment below! Your experiences and insights are valuable to the Dynamics GP community.

Computacionvinchi