Correcting Item Balance Discrepancies in JD Edwards EnterpriseOne Using the On Hand Inventory Repost Application

Introduction

Most are familiar with the occasional discrepancies between the Item Ledger (F4111) and the On Hand Item Location (F41021); in fact, discussions around the topic go back as early as 2006. As of today,  there isn’t a definitive answer to the cause. It could be timing, it could be record lock, or perhaps an underlying issue with a Master Business Function (Transaction Commit Failures).
What we are certain of is how to fix it:
  1. Have an IT (database) resource change F41021.IBPQOH (via SQL or other database tools) to match the correct quantity on-hand in the location.
  2. Develop a custom interactive program that allows a specific user with special security access to change the value of F41021.IBPQOH for a specific record.
  3. Develop a custom Batch program (UBE) that converts the quantity field on the Item Ledger records (F4111.TQRT) into the Primary Unit of Measure, then sums the primary units of all the Item Ledger records (F4111) and populates that sum in the Item Location Quantity On Hand field (F41021.PQOH).
  4. Enhance the R41544 (Item Balance/Ledger Integrity) to update the F41021.IBPOQH when discrepancies are found.
The good news is that Oracle just released a new Batch application that will repost the Items On Hand using the Item Ledger.

New Application

This new application became available this past January and was delivered in an Electronic Software Update (ESU).
ESU:            JN20155
Objects:      R41995
                     T41995
After installing and deploying the ESU objects, the application can be accessed from Batch Versions:
Screenshot of ESU batch versions

Purpose

The purpose of the program is to fix known data issues in specific Item Location records where the Item Location Quantity On Hand (F41021.IBPQOH) value does not match the sum of the transaction quantities in the Item Ledger table (F4111) or in the Item As Of table (F41112) or both. Due to the large amount of data the job could potentially process, Oracle does not recommend putting it in Scheduler, nor should you run it on a regular basis. The data selection should be restricted to repost only Item Location records known to be inaccurate.

The application is based on a Business View (V41021E) that joins F41021 and F4101 and provides a more complete set of fields for data selection.

Prerequisites

Oracle recommends completing the following before executing the job:

  • Review the Work With F42210 Commitments Recovery (P42210) to make sure there are no orphaned records from the Item Location workfile (F41021WF) that need to be rolled back.
  • Review the Manual Inventory to G/L Reconciliation (P41500) to ensure there are no unreconciled transactions where the Item Ledger amount and General Ledger amount involving the item being reposted are out of balance.
  • Verify that the user-defined code from the UDC table (00/DT) for document type I6 displays the hard-coded value as Y.

Running the On Hand Inventory Repost

The application provides two processing options:

  1. Proof or Final Mode: This will allow you to execute the application in Proof Mode (report only) to review the discrepancies before updating the Item Balance in Final Mode.When you run the program in Final Mode, a record with zero quantity will be inserted into the Item Ledger table (F4111) with a hard coded document type I6 and a unique key ID along with updated audit fields. The Transaction Explanation column of the Item Ledger table is populated with the quantity adjusted in the Item Location file (F41021). This record can be used for auditing purposes.
  2. Calculate Repost On Hand value:If blank, calculate the quantity from the Item Ledger (F4111) and Item ASOF File (F41112) tables.Calculate the quantity from the Item Ledger (F4111) table:Using Blank (F4111 & F41112) will expediate the execution (remember the F41112 is a summary table of the F4111). With this setting the program will:- Process only the records from the F4111 that are not yet posted to the F41112 (ILIPCD <> “X” and ILIPCD <> “Y”) for the Item, Branch/Plant, Location, and Lot Combination.
    – Process records from F41112 for the Item, Branch/Plant, Location, and Lot Combination.Using 1 (F4111) will take longer as it will process all the F4111 records (ILIPCD <> “X”) for the Item, Branch/Plant, Location and Lot Combination.

For the purposes of this article, we ran the versions using the Business Unit = M30 and processing option #2 set as 1 (F4111).

Proof Mode:

Proof mode

Item Ledger: The Sum of the Item Ledger is 210 whereas the Item Balance On Hand is 211.

Item ledger

Final Mode

  • On Hand Quantity has been updated
  • An Item Ledger record has been inserted with I6 as Document Type for auditing purposes

Summary availability

Transaction explanation

For additional information review these MOS documents:
Manual Rollback Process of Item Location (F41021) File (P42210/R42210)
(Doc ID 625445.1)

Inventory to G/L Reconciliation Process (P41500/R41500/R41501)
(Doc ID 1678098.1)

Syntax has over 40 years of Oracle ERP experience and over 25 years providing cloud and managed services for Oracle E-Business Suite and JD Edwards applications. Discover how we can improve the efficiency and effectiveness of your Oracle environments and applications.