Oracle Applications inventory and database Age calculation method)

This article only discusses the implementation methods of inventory and database age from a technical point of view. These are my summary in my work and I will discuss them with you.
1. Oracle inventory algorithm.

Oracle Applications provides the MTL_ONHAND_QUANTITIES_DETAIL table for inventory calculation. Developers only need to group and sum the tables as required to obtain the inventory quantity. This quantity is the quantity we query in the system. However, if you want developers to provide inventory for the last month or the last quarter, it will be troublesome. Because the MTL_ONHAND_QUANTITIES_DETAIL table does not provide a method for calculating the trace inventory. So how can we solve this problem?
Let's take a look at the relationship between the MTL_ONHAND_QUANTITIES_DETAIL table and the mtl_material_transactions table. Mtl_material_transactions is the base table of Oracle Applications inventory. All inventory-related data must be stored in this table, such as initial inventory import and acceptance of procurement. Sales and manufacturing completion warehouse receiving. Sub-database transfer. The Group sum of mtl_material_transactions should be equal to the group sum of the MTL_ONHAND_QUANTITIES_DETAIL table. However, the records of mtl_material_transactions in the table are generally large and do not obtain the current quantity in the table. Each data change in the mtl_material_transactions table will affect the final result of MTL_ONHAND_QUANTITIES_DETAIL. We found through research. Mtl_material_transactions adds this record to the MTL_ONHAND_QUANTITIES_DETAI table for each incoming record. Before the inventory does not disappear, we can establish a one-to-one correspondence between the TRANSACTION_ID field of the mtl_material_transactions table and the CREATE_TRANSACTION_ID field of the MTL_ONHAND_QUANTITIES_DETAIL table. When there is a warehouse picking record, MTL_ONHAND_QUANTITIES_DETAIL does not have a corresponding warehouse picking record, but deducts the number of original warehouse receiving records according to the logic of the Oracle stack. The direct path is deducted to zero. Oracle Applications regularly clears the zero number in the MTL_ONHAND_QUANTITIES_DETAIL table. Therefore, it is wrong to trace the previous inventory with MTL_ONHAND_QUANTITIES_DETAIL.

2. Library age algorithms:
The Oracle Applications inventory may vary depending on the company's business conditions.
A) Calculation of the aging of materials with batch number and serial number control
If the inventory system has a batch number and serial number control for receiving and receiving specified materials, the database Age algorithm is relatively easy. You can add the batch number, serial number, and other information according to the preceding algorithm SQL. Calculate the material aging based on the information.

B) there is an algorithm to set the raw Receiving date first-in-first-out material aging.
If the profile option of the inventory system is set when the system goes online: 'inv: original receiving date first-in-first-out'
If yes, the inventory system processes the transaction quantity in the FIFO order of the original receiving date when processing the materials. Then, the ORIG_DATE_RECEIVED field in the MTL_ONHAND_QUANTITIES_DETAIL table records the original receipt date until its inventory disappears.
Algorithms such as A) are similar.

C) assuming that the principle of FIFO is the material aging Algorithm
If the inventory system cannot meet the conditions A) and B) above, how can we calculate the material aging? Here we provide a solution based on our company's aging algorithm. First, we assume that the materials are imported to and from the Warehouse Based on the FIFO principle. Then we need to assume an original material aging, and then calculate the new aging Based on the FIFO principle. If the system is just launched, the stock imported at the initial stage is the raw material aging. It can be considered that the material inventory aging at the import time is zero. If the system has been in use for many years, how can we calculate the material aging? Here we have an approximate algorithm. You can use the calculation method of inventory tracing introduced in this article to find the inventory data for one year or two years ago. By default, the material aging is zero. Based on this data, the current material aging is calculated based on the FIFO principle of materials. We generally think that if materials are still in stock for more than 180 days, it will be a dull thing, so one or two years can be done. The implementation method is as follows:
A) Calculate the stock of materials one or two years ago using the tracing algorithm.
By default, the material age is zero. For specific algorithms, see the preceding SQL. It depends on the company's classification requirements for this age, whether it is based on the position or the arrival location, different companies have different requirements. Here we use positions as the basic classification.
B) based on the initial aging of the early stage. To calculate the current material aging.
2.1 create a table to store the initial aging data.
The initial aging record is placed in this table. The later aging calculation is calculated based on the data in this table.
2.2 rolling Calculation of material aging.
Intercept all types of records in the mtl_material_transactions table (except for cost update) within the period from the initial aging time to the current time ). This record is calculated based on the order of each transaction time. Sort by warehouse receiving and warehouse picking. For new warehouse receiving records to the BITC_ITEM_INV_ATUO_AGE table, the original warehouse receiving time is the transaction time of the record. If you want to find the stock of the earliest aging date of the material for warehouse picking. If this stock is not deducted, find the next aging stock of this stock. Until this record is met. This keeps computing until the last one. For each calculation record, we need to mark the operation for this record in the mtl_material_transactions table. Do not repeat the calculation next time. Ensure data accuracy. If it is the material aging table we want to run till now, we can develop a program for regular operation in the background. In this way, the expected material aging table can be obtained in a timely manner.
The specific implementation method is as follows:
1. truncate all types of records in the mtl_material_transactions table (except for cost update) in the period from the initial aging time to the current time ),
2. Sort this segment of records in special order. Sort by TRANSACTION_ID and PRIMARY_QUANTITY. Remember that the inconsistency of units must be converted to consistency. The sub-stock transfer and picking types and inter-organizational transfer types TRANSACTION_ID need to be specially processed, so that the positive number of records of this type of data is placed before the negative number of records. When Oracle processes these types, it first deducts a negative number and deducts the inventory to be transferred from the source position. Then we make a positive order to the inventory of the destination position. To record the inventory source date in the aging table. We need to first import positive to target positions into the inventory. Let the negative number be used to export data from the source warehouse.

3. the records to be imported and exported must be classified. Different types have different processing algorithms.
3.1 warehouse receiving type: 1: PO receiving 2: warehouse receiving after ticket completion, 3: warehouse receiving, 4: return ticket
You need to add the table with the initial aging according to the new warehouse receiving record.
3.2 transfer out of warehouse picking type and stock transfer type. The deduction is based on the Aging order.
3.3 The inventory transfer type warehouse receiving part should record the original warehouse receiving time of this note and the original warehouse receiving time of the original warehouse in the future.