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 6 buckets, ie, within last 120 days, between 121-180 days, beween 181-1 year, between 1-2 Years, between 2-3 Years and 3 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.

WITH dataset AS (
    SELECT
        pivot_data.*
    FROM
        (
            SELECT
                inv_data.inventory_item_id,
                inv_data.transaction_quantity,
                inv_data.age_in_days
            FROM
                ( (
                    SELECT
                        moq.inventory_item_id,
                        moq.organization_id,
                        moq.transaction_quantity,
                        CASE
                            WHEN ( trunc(sysdate) - trunc(moq.date_received) ) <= 120                        THEN
                                1
                            WHEN ( trunc(sysdate) - trunc(moq.date_received) ) BETWEEN 121 AND 180           THEN
                                2
                            WHEN ( trunc(sysdate) - trunc(moq.date_received) ) BETWEEN 181 AND 365           THEN
                                3
                            WHEN ( trunc(sysdate) - trunc(moq.date_received) ) BETWEEN 366 AND 730           THEN
                                4
                            WHEN ( trunc(sysdate) - trunc(moq.date_received) ) BETWEEN 731 AND 1095          THEN
                                5
                            WHEN ( trunc(sysdate) - trunc(moq.date_received) ) > 1096                        THEN
                                6
                        END age_in_days
                    FROM
                        mtl_onhand_quantities moq
                    WHERE
                        moq.organization_id IN ( :p_org_id, :p_warehouse_id )
                ) inv_data )
        ) PIVOT (
            SUM ( transaction_quantity )
            FOR age_in_days
            IN ( 1 AS "120 Days", 2 "121-180 Days", 3 "181-365 Days", 4 "1Yr-2Yr", 5 "2Yr-3Yr", 6 "3Yrs+" )
        )
        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