Inventory On-Hand Quantity Consolidation
Use the Perform Inventory On-Hand Quantity Consolidation scheduled process to combine on-hand quantity records based on common item attributes and the original receipt date.
Every transaction that increases a good’s on-hand quantity, like a purchase order receipt or miscellaneous receipt, creates a unique entry in the INV_ONHAND_QUANTITIES_DETAIL table. While multiple entries are necessary for many reasons, the existence of multiple records for an item with the same attributes, in the same location, with the same original receipt date can potentially cause performance issues in future transactions that require checking material availability. Consolidating these rows improves future inventory transaction processing.
Here’s an illustrative data sample of the on-hand table prior to consolidation:
| Row | Item | Organization | Subinventory | Receipt Date | Original Receipt Date | On-Hand Quantity |
|---|---|---|---|---|---|---|
| 1 | AS54888 | M1 | Stores | 11-SEP-2022 09:00:00 |
08-SEP-2022 09:00:00 |
10 |
| 2 | AS54888 | M1 | Stores | 11-SEP-2022 10:00:00 |
08-SEP-2022 09:00:00 |
5 |
| 3 | AS54888 | M1 | Stores | 12-SEP-2022 09:00:00 |
12-SEP-2022 09:00:00 |
20 |
| 4 | AS54888 | M1 | FGI | 11-SEP-2022 09:00:00 |
10-SEP-2022 09:00:00 |
25 |
| 5 | AS54888 | M1 | FGI | 13-SEP-2022 09:00:00 |
13-SEP-2022 09:00:00 |
55 |
| 6 | AS54888 | M2 | Stores | 11-SEP-2022 09:00:00 |
10-SEP-2022 09:00:00 |
30 |
| 7 | AS54888 | M2 | Stores | 12-SEP-2022 09:00:00 |
10-SEP-2022 09:00:00 |
25 |
The prior consolidation table shows sample on-hand records as created in the table when different receipts are performed. Notice that the rows 1 and 2 have the same item, organization, subinventory, and receipt date.
Here's an example of the table after consolidation:
| Row | Item | Organization | Subinventory | Receipt Date | Original Receipt Date | On-Hand Quantity |
|---|---|---|---|---|---|---|
| 1 | AS54888 | M1 | Stores | 11-SEP-2022 10:00:00 |
08-SEP-2022 00:00:00 |
15 |
| 2 | AS54888 | M1 | Stores | 12-SEP-2022 09:00:00 |
12-SEP-2022 09:00:00 |
20 |
| 3 | AS54888 | M1 | FGI | 11-SEP-2022 09:00:00 |
10-SEP-2022 09:00:00 |
25 |
| 4 | AS54888 | M1 | FGI | 13-SEP-2022 09:00:00 |
13-SEP-2022 09:00:00 |
55 |
| 5 | AS54888 | M2 | Stores | 12-SEP-2022 09:00:00 |
10-SEP-2022 00:00:00 |
55 |
This table shows how the consolidation groups records after the scheduled process runs. Notice that the On-Hand Quantity column contains the consolidated quantity based on the original receipt date. The Original Receipt Date value is reset to the start of the day (with time as 00:00:00).
In the after consolidation table, the first row now contains a quantity of 15 which is the sum rows 1 and 2 from the before consolidation table. Since the item, organization, subinventory, and receipt date are the same for those two rows, they are consolidated in the after consolidation table. The same is true for rows 6 and 7 in the before consolidation table. They are shown consolidated in the after consolidation table.
Here are important considerations for running the Perform Inventory On-Hand Quantity Consolidation scheduled process:
- On-hand quantity consolidation happens only when the Item, Organization, Revision, Subinventory, Locator, Lot, Project, Task, Country of Origin, and Original Receipt Date attributes are the same.
- Run this process for organizations where receipts are performed more frequently than issues for the same combination of attributes, thereby leading to excessive on-hand records that may impact your transaction processing performance.
- It's recommended to run or schedule the scheduled process only when you observe that Inventory transaction processing (through the user interface, FBDI, REST API, or SOAP API) is taking longer than expected.
- It's preferable not to run the scheduled process for all organizations to ensure it completes quickly.
- It’s preferable to run the scheduled process on weekends or on days when there is minimal transaction processing.