R3482 and R3483 MRP Performance and Data Purge

Purpose of Document

Information Center: Overview of JD Edwards EnterpriseOne Requirements Planning > Information Center: Troubleshooting JD Edwards EnterpriseOne Requirements Planning > Note 1268674.1

As transaction volume grows, there is a possible performance degrade in running MRP. This document is to suggest how to archive/purge master/transactional files which are related with MRP processing in running R3482/R3483 - MRP/MPS Requirements Planning/Master Planning Schedule - Multiple Plant.

Before making any changes in data, review - E1: 34: MRP Performance Checklist Document 626043.1

Case studies have shown us that the best performance can be accomplished by:

* Purging data from tables to be read regularly
* Build the best execution plan in Database server system by your in-house DBA

This document shall focus on tables that are read through the MRP transactions below

* Multiple read of same data
* Transactional files

Note: This document is not intended to suggest how to build the best execution plan in your respective database. In supported DB system statistics and SQL Package will hold this information. Refer to Performance and Tuning: UBE Performance and Tuning Document 748333.1

1. How parameters are determined in running R3482/R3483

Whether to cache tables F4101 - Item Master, F4102 - Item Branch File and F4801 - Work Order Master File are determined by the value of Special Handling Code for codes 'DC - Disable Cache Flag' for UDC 34/PC (Planning Control UDC). For further detail, refer to E1: 34: MRP Cached Tables Can Improve MRP Performance Document 657053.1.

Table caching will work fine without issuing a Memory Allocation Failure only when it is handled for active items. In the environment where data is tightly maintained, the performance gain from table caching will be huge. Perform or maintain item information only for active parts.

2. The proper data selection when running R3482/R3483

sql> SELECT IBITM, IBLITM, IBAITM, IBMCU, IBPRP4, IBVEND, IBANPL, IBBUYR, IBROQI, IBRQMX, IBRQMN, IBSAFE, IBLOTS, IBOT1Y, IBSTDP, IBFRMP, IBTHRP, IBFRGD, IBTHGD, IBSTKT, IBMPST, IBMRPD, IBMRPC, IBLTLV, IBOPC, IBOPV, IBLTPU, IBMPSP, IBMRPP, IBMTF1, IBMTF2, IBMTF3, IBMOVD, IBQUED, IBSETL, IBSRNK, IBSRKF, IBTIMB, IBMULT, IBLLX, IBUSER, IBPID, IBJOBN, IBUPMJ, IBTDAY, IBLEDD, IBPEFD FROM CRPDTA.F4102 WHERE (... ) ORDER BY IBMCU ASC,IBLLX ASC,IBITM ASC

It is running based on F4102 so it is important to have smaller set of data from F4102.

Table Purged	Table To Be Validated	Reference for purge
F4102	F41021 - Item Location File F4211 - Sales Order Detail File F4311 - Purchase Order Detail File F43121 - Purchase Order Receiver File F4801 - Work Order Master File F3111 - Work Order Part List F3002 - Bill of Material Master File F4105 - Item Cost File F30026 - Item Cost Component Add-Ons F41002 - Item Unit of Measure Conversion Factors	E1: 41: Files deleted by R4101P/R4102P Document 1322000.1 E1: 40: R4102P and Tables Related per Processing Option Value Document 1125633.1
F4101	F4102 - Item Branch File F41021 - Item Location File F4105 - Item Cost File F4108 - Lot Master F3002 - Bill of Material Master File F3003 - Routing Master File	E1: 41: Files deleted by R4101P/R4102P E1: 41: Inventory Purge Programs (R4101P / R4102P) Document 1322000.1 E1: 41: How R4101P Validates to Delete Item and Considerations Document 1301393.1

3. Tables needed to be read repeatedly and are cached

F0007 - Workday Calendar (Cached through Database Cache)

F34004 - Demand/Supply Inclusion Rules (Cached through JDE APIs in BSFN)

F4102 - Item Branch File (Optional)

F4101 - Item Master (Optional)

F4801 - Work Order Master File (Optional)

and other constant files which are members of P98613 - Database Caching

Note:

Above tables are to be cached either through database cache or application level. Note that database cache has its limit in number of rows and size of memory.
F4801 is header file for detail files (F3111 - Work Order Parts List and F3112 - Work Order Routing), so whenever detail files are read, data from F4801 is to be requested.
Smaller F34004 is very important as it reads transaction files which result in Supply/Demand; then discards them one by one when inclusion rules are not met. Complicated inclusion rules may cause a severe performance delay.
The way transactional files are read is different between Xe, ERP8 and 8.9 and above. The later excludes any transaction which are completed and canceled based on status codes, if available.
Tables F4102, F4101 and F4801 are optional to cache or not, so try both and decide which one to set (Reference E1: 34: MRP Cached Tables Can Improve MRP Performance Document 657053.1 ).
Run the R4102P to purge the F4102/F4101 - Item Branch File/Item Master File.

4. Read data from Supply/Demand Transaction Files

5. Store data read into cache

	BSFN	Table Related	UBE	Reference for Purge
1	B3400400 - MRPWriteOnHandLotQtyToCacheA (MRP, Update Adjusted Sales and Forecast Qty)	F41021 F4108	R4102P - Item Balance Purge (F4102) N/A	E1: 40: R4102P and Tables Related per Processing Option Value Document 1125633.1 Currently we do not have batch purge against F4108 - Lot Master File. Delete it manually through P4108 - Lot Master
2	B3400380 - MRPWriteForecastQtyToCache (MRP,Write Forecast Quantity To Cache)	F3460	N/A	E1: 34: Is it possible to purge old records from the F3460 - Forecast table? Forecast data can be rebuilt. May want to retain actuals, but they could be rebuilt as well.
3	B3400390 - MRPWriteSalesOrderQtyToCache (MRP,Write Sales Order Qty To Cache)	F4211	R4211P - Purge Sales Order Detail (F4211)	E1: 42: Data File Purges in Sales (R42119P/R42199P/R4201P/R4211P/R42800) Document 625602.1
4	B3400400 - MRPUpdateAdjustedSalesAndForecas (MRP, Update Adjusted Sales and Forecast Qty)	Update Cache
5	B3400420 - MRPWritePurchaseOrderQtyToCache (MRP,Write Purchase Order Qty To Cache)	F4311	R4311P - Detail (F4311) Purge R43121P - Receiver (F43121) Purge	E1: 43: Procurement Data Files Purges Document 661344.1
6	B3400440 - MRPWriteWorkOrderQtyIntoCache (MRP, Write Work Order Qty Into Cache)	F4801 & F3111	R4801P - Work Order Purge Program	E1: 31: R4801P Purging Work Order Records Document 630413.1
7	B3400480 - MRPWritePartsListQtyIntoCache (MRP, Write Parts List Qty Into Cache)	F3111	R4801P - Work Order Purge Program	E1: 31: R4801P Work Order Purge FAQ Document 630413.1
8	B3401550 - MRPLoadBOMCache (MRP, Load BOM Cache)	F3002	N/A	Try to maintain smallest size of BOM

<Internal_Only>Note: The way the system reads master or transactional files is similar to the way it reads files in Supply and Demand Inquiry (P4021), except that the P4021 reads the result of MRP. Refer to E1: 34: P4021 - Supply & Demand Technical FAQ Document 952806.1</Internal_Only>

6. Read cache and compare it with Inclusion Rule

7. Write messages

F3411 - MPS/MRP/DRP Message File
F3412 - MPS/MRP/DRP Lower Level Requirements File
F3413 - MPS/MRP/DRP Summary File

Other Considerations:

Note:

Some Purge Reports may not have an archive option, so it is very important to backup before running any purge report through a database tool.
If data is well maintained and well kept, MRP performance should not affect daily business.

To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the E1 Manufacturing Community. To look at upcoming or archived Advisor Webcasts please see Advisor Webcast Details Document 1450327.1. If your topic is not currently scheduled please suggest it.