Quite often auditors could come with some strange requirements (especially when they do not understand the business) & we had to formalize on foot couple of years back. From those days, I always wanted to put together few things and post this, which is happening today.

Our requirements were to list the receipts in 7 buckets, ie, within last 120 days, between 121-180 days, beween 181-1 year, between 1-2 Years, between 2-3 Years and 3-5 Years and 5 Years+ (and still in stock!)

Please note, I am using a custom function “omsconcorgqty_f“ (code provided below, that concatenates the subinventory quantities into a single column. This function could raise an exception when the column length exceeds 4000 characters.

Minimum requirements: Your EBS instance must be using Oracle database 11gR2 to use “LISTAGG” that is used with the custom function. In addition to, I am using PIVOT, that is supported from 11g.

The below example considers a situation where business has a WMS enabled organization. You can pass NULL to :P_WAREHOUSE_ID when not applicable.

If your organization uses multiple UOM for items, you must adjust the below query accordingly, especially for the cost part.

The average cost for the item is picked using the following logic.

If the :P_ORG_ID has item quantities, then the cost for the item will be picked from the same organization. If the :P_ORG_ID organization doesn’t have quantities and warehouse has, then the cost from :P_WAREHOUSE_ID organization will be picked. So there could be inventory value differences and you shouldn’t use the inventory values derived from this exercises if the above said conditions apply.

One of the challenges you are going to face is to identifying the transaction types those should be considered as a receipt. For example, we receive materials in stock through the following transactions.

SELECT a.transaction_type_id
       , a.transaction_type_name
       , a.transaction_source_type_id
       , b.transaction_source_type_name
       , a.transaction_action_id
       , c.meaning
    FROM mtl_transaction_types a
       , mtl_txn_source_types b
       , mfg_lookups c
   WHERE a.transaction_source_type_id = b.transaction_source_type_id
     AND a.transaction_action_id = c.lookup_code
     AND c.lookup_type = 'MTL_TRANSACTION_ACTION'
     AND a.transaction_type_id  IN (12,15,18,40,41,42)
ORDER BY transaction_type_id;

If your organization uses additional transaction types for receiving stock to the stores, please include them to the solution. Here the solution is about scanning the entire MTL_MATERIAL_TRANSACTIONS table & based on the volume of data/hardware resources, it could be a very painful ordeal. My suggestion is to run the query when there are least numbers of “issues” are expected.

I will try to explain the approach step by step, for better understanding.

At the first level we will pick all items for the organization(s) from the MTL_ONHAND_QUANTITIES & use this list for picking up items from the MTL_MATERIAL_TRANSACTIONS.

with ohqty_data as (
select inventory_item_id, sum(transaction_quantity) oh_qty from 
mtl_onhand_quantities 
where 
organization_id in (:p_org_id,:p_warehouse_id)
group by inventory_item_id
)

The above code portion will pick all the items from quantities table that has on-hand quantities.

inv_data as (
select inventory_item_id, qty, age_in_days,
sum(qty) over (partition by inventory_item_id order by age_in_days) running_balance,
sum(qty) over (partition by inventory_item_id order by age_in_days)-qty previous_balance
from (
select inventory_item_id, sum(transaction_quantity) qty, 
age_in_days from (
select inventory_item_id, transaction_quantity,
case 
when (trunc (sysdate) - trunc (mmt.transaction_date)) <= 120 then 1
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 121 and 180 then 2
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 181 and 365 then 3
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 366 and 730 then 4
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 731 and 1095 then 5
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 1096 and 1825 then 6
when (trunc (sysdate) - trunc (mmt.transaction_date)) > 1825 then 7
end age_in_days
from mtl_material_transactions mmt
where 
1=1
and inventory_item_id in (select distinct inventory_item_id from ohqty_data)
and organization_id in (:p_org_id,:p_warehouse_id)
and transaction_type_id in (12,15,18,40,41,42)
)
group by inventory_item_id, age_in_days
)
)

This code portion groups data into multiple age buckets, based on business requirements. We will be using few Analytical functions to determine balance in individual age buckets. The final SQL block will look like below.

with ohqty_data as (
select inventory_item_id, sum(transaction_quantity) oh_qty from 
mtl_onhand_quantities 
where 
organization_id in (:p_org_id,:p_warehouse_id)
group by inventory_item_id
),
inv_data as (
select inventory_item_id, qty, age_in_days,
sum(qty) over (partition by inventory_item_id order by age_in_days) running_balance,
sum(qty) over (partition by inventory_item_id order by age_in_days)-qty previous_balance
from (
select inventory_item_id, sum(transaction_quantity) qty, 
age_in_days from (
select inventory_item_id, transaction_quantity,
case 
when (trunc (sysdate) - trunc (mmt.transaction_date)) <= 120 then 1
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 121 and 180 then 2
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 181 and 365 then 3
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 366 and 730 then 4
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 731 and 1095 then 5
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 1096 and 1825 then 6
when (trunc (sysdate) - trunc (mmt.transaction_date)) > 1825 then 7
end age_in_days
from mtl_material_transactions mmt
where 
1=1
and inventory_item_id in (select distinct inventory_item_id from ohqty_data)
and organization_id in (:p_org_id,:p_warehouse_id)
and transaction_type_id in (12,15,18,40,41,42)
)
group by inventory_item_id, age_in_days
)
)
select inventory_item_id, age_in_days, age_bucket_balance from (
select inventory_item_id, age_in_days,
case when (running_balance <= oh_qty) then qty
else nvl(oh_qty - previous_balance, oh_qty)
end age_bucket_balance from( 
select a.*, b.oh_qty from inv_data a
inner join ohqty_data b on b.inventory_item_id = a.inventory_item_id
order by a.inventory_item_id, a.age_in_days
))
where age_bucket_balance > 0;

Problem & The solution.

Oracle inventory issues the quantities following FIFO method. Say you received 10 pieces of a material in January 2021 and additional 10 pieces of the same item on February 2021. Think of a transaction that issues 8 quantities in March 2021. The balance sheet for the item after the transaction will look like below

The given code block does this calculation! I will find some time in near future for step by step explanation for the logic & solution. I’ve many people to thank for this solution, especially my colleague Sherin Thomas Mathew, Iudith Menzel and our sales team that stood with me through a month long experiments and wrong outputs.

Now let us go to the final solution.

Create a table

 CREATE TABLE OMSINVSTAGE 
   (	INVENTORY_ITEM_ID NUMBER, 
	AGE_IN_DAYS NUMBER, 
	CLOSE_BALANCE NUMBER
   )  ;

Let us minutely change the code block with an insert

INSERT INTO OMSINVSTAGE(INVENTORY_ITEM_ID, AGE_IN_DAYS, CLOSE_BALANCE)
with ohqty_data as (
select inventory_item_id, sum(transaction_quantity) oh_qty from 
mtl_onhand_quantities 
where 
organization_id in (:p_org_id,:p_warehouse_id)
group by inventory_item_id
),
inv_data as (
select inventory_item_id, qty, age_in_days,
sum(qty) over (partition by inventory_item_id order by age_in_days) running_balance,
sum(qty) over (partition by inventory_item_id order by age_in_days)-qty previous_balance
from (
select inventory_item_id, sum(transaction_quantity) qty, 
age_in_days from (
select inventory_item_id, transaction_quantity,
case 
when (trunc (sysdate) - trunc (mmt.transaction_date)) <= 120 then 1
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 121 and 180 then 2
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 181 and 365 then 3
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 366 and 730 then 4
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 731 and 1095 then 5
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 1096 and 1825 then 6
when (trunc (sysdate) - trunc (mmt.transaction_date)) > 1825 then 7
end age_in_days
from mtl_material_transactions mmt
where 
1=1
and inventory_item_id in (select distinct inventory_item_id from ohqty_data)
and organization_id in (:p_org_id,:p_warehouse_id)
and transaction_type_id in (12,15,18,40,41,42)
)
group by inventory_item_id, age_in_days
)
)
select inventory_item_id, age_in_days, age_bucket_balance from (
select inventory_item_id, age_in_days,
case when (running_balance <= oh_qty) then qty
else nvl(oh_qty - previous_balance, oh_qty)
end age_bucket_balance from( 
select a.*, b.oh_qty from inv_data a
inner join ohqty_data b on b.inventory_item_id = a.inventory_item_id
order by a.inventory_item_id, a.age_in_days
))
where age_bucket_balance > 0;

Commit & don’t forget to delete rows before NEXT run. Use a global temporary table for best results.

Once the rows are populated to our table, we can execute the final code block to generate the Ageing report.

WITH DATASET AS(
Select pivot_data.* from (
select 
inv_data.INVENTORY_ITEM_ID,
inv_data.close_balance TYPE_QTY,
inv_data.AGE_IN_DAYS from OMSINVSTAGE inv_data
where 
1=1
--inv_data.inventory_item_id = 27626
and inv_data.close_balance > 0
)
PIVOT (sum(type_qty) FOR AGE_IN_DAYS IN (1 as "120 Days",2 "121-180 Days",3 "181-365 Days",4 "1Yr-2Yr",5 "2Yr-3Yr",6 "3Yr-5Yrs",7 "5Yrs+")) pivot_data)
SELECT msi.concatenated_segments,  msi.description,msi.primary_uom_code,
CASE WHEN nvl((Select sum(transaction_quantity) from mtl_onhand_quantities moq where moq.inventory_item_id = msi.inventory_item_id and moq.organization_id=:P_ORG_ID
group by moq.inventory_item_id,moq.organization_id),0) > 0 then (
Select item_cost from cst_item_costs cic 
where cic.inventory_item_id = msi.inventory_item_id and cic.organization_id = msi.organization_id and cic.cost_type_id = 2)
ELSE
(Select item_cost from cst_item_costs cic 
where cic.inventory_item_id = msi.inventory_item_id and cic.organization_id = :P_WAREHOUSE_ID and cic.cost_type_id = 2)
END item_cost,
dataset.*,
OMSCONCORGQTY_f(:P_ORG_ID, :P_WAREHOUSE_ID, dataset.inventory_item_id, msi.primary_uom_code) org_quantity FROM DATASET
INNER JOIN mtl_system_items_kfv MSI ON MSI.INVENTORY_ITEM_ID = DATASET.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID=:P_ORG_ID
ORDER BY  msi.concatenated_segments;

omsconcorgqty_f function

This function adds (W) before the subinventories from :P_WAREHOUSE_ID organization for easier identification.

CREATE OR REPLACE FUNCTION omsconcorgqty_f (
    p_org_id        IN  NUMBER,
    p_warehouse_id  IN  NUMBER,
    p_item_id       IN  NUMBER,
    p_uom           IN  VARCHAR2
) RETURN VARCHAR2 IS
    l_qty_string VARCHAR2(4000);
BEGIN
    SELECT
        LISTAGG(subqty, ',') WITHIN GROUP(
            ORDER BY
                subqty
        )
    INTO l_qty_string
    FROM
        (
            SELECT
                inventory_item_id,
                CASE
                    WHEN organization_id = p_warehouse_id THEN
                        '(W)'
                        || subinventory_code
                        || '('
                        || to_char(SUM(transaction_quantity))
                        || ')'
                    ELSE
                        subinventory_code
                        || '('
                        || to_char(SUM(transaction_quantity))
                        || ')'
                END subqty
            FROM
                mtl_onhand_quantities_detail moq
            WHERE
                    moq.inventory_item_id = p_item_id
                AND moq.transaction_uom_code = p_uom
                AND organization_id IN ( p_org_id, p_warehouse_id )
            GROUP BY
                inventory_item_id,
                subinventory_code,
                organization_id
        )
    GROUP BY
        inventory_item_id;

    RETURN l_qty_string;
END;

Hope this helps