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