Aim :
The below is the query to get the Inventory Aging Report and the aging has been calculated with seven different bucket periods, with sub inventory details, unit cost and organization etc.
Script:
SELECT
name,
organization_name,
item_code,
item_type,
uom,
subinventory_code,
description,
inventory_item_id,
organization_id,
item_cost unit_cost,
on_hand_quantity,
CASE
WHEN b1 < 0 THEN 0
ELSE b1
END
b1,
CASE
WHEN b2 < 0 THEN 0
ELSE b2
END
b2,
CASE
WHEN b3 < 0 THEN 0
ELSE b3
END
b3,
CASE
WHEN b4 < 0 THEN 0
ELSE b4
END
b4,
CASE
WHEN b5 < 0 THEN 0
ELSE b5
END
b5,
CASE
WHEN b6 < 0 THEN 0
ELSE b6
END
b6,
CASE
WHEN b7 < 0 THEN 0
ELSE b7
END
b7,
total total_cost,
product_category,
(
SELECT
creation_date
FROM
mtl_material_transactions
WHERE
transaction_id = (
SELECT
MAX(transaction_id)
FROM
mtl_material_transactions
WHERE
inventory_item_id = m_out.inventory_item_id
AND organization_id = m_out.organization_id
AND transaction_quantity > 0
AND subinventory_code = m_out.subinventory_code
)
) date_item_last_received,
(
SELECT
creation_date
FROM
mtl_material_transactions
WHERE
transaction_id = (
SELECT
MAX(transaction_id)
FROM
mtl_material_transactions
WHERE
inventory_item_id = m_out.inventory_item_id
AND organization_id = m_out.organization_id
AND transaction_quantity < 0
AND subinventory_code = m_out.subinventory_code
)
) date_item_last_issued,
DECODE(open_po,’0′,’No’,’Yes’) open_po_status,
item_creation_date
FROM ( SELECT
name,
organization_name,
item_code,
item_type,
uom,
subinventory_code,
description,
inventory_item_id,
organization_id,
item_cost,
on_hand_quantity,
issued_qty,
(
CASE
WHEN ( bucket2 + bucket3 + bucket4 + bucket5 + bucket6 + bucket7 + issued_qty ) < 0 THEN ( bucket1 + bucket2 + bucket3 + bucket4 + bucket5 + bucket6 + bucket7 + issued_qty
)
ELSE bucket1
END
) b1,
(
CASE
WHEN ( bucket3 + bucket4 + bucket5 + bucket6 + bucket7 + issued_qty ) < 0 THEN ( bucket2 + bucket3 + bucket4 + bucket5 + bucket6 + bucket7 + issued_qty )
ELSE bucket2
END
) b2,
(
CASE
WHEN ( bucket4 + bucket5 + bucket6 + bucket7 + issued_qty ) < 0 THEN ( bucket3 + bucket4 + bucket5 + bucket6 + bucket7 + issued_qty )
ELSE bucket3
END
) b3,
(
CASE
WHEN ( bucket5 + bucket6 + bucket7 + issued_qty ) < 0 THEN ( bucket4 + bucket5 + bucket6 + bucket7 + issued_qty )
ELSE bucket4
END
) b4,
(
CASE
WHEN ( bucket6 + bucket7 + issued_qty ) < 0 THEN ( bucket5 + bucket6 + bucket7 + issued_qty )
ELSE bucket5
END
) b5,
(
CASE
WHEN ( bucket7 + issued_qty ) < 0 THEN ( bucket6 + bucket7 + issued_qty )
ELSE bucket6
END
) b6,
( bucket7 + issued_qty ) b7,
total,
product_category,
(
SELECT
COUNT(1)
FROM
po_lines_all
WHERE
item_id = p_out.inventory_item_id
–AND closed_code NOT IN (‘CLOSED’)
AND closed_code = ‘OPEN’
AND org_id = (
SELECT
organization_id
FROM
hr_operating_units
WHERE
name = p_out.name
)
) open_po,
item_creation_date
FROM ( SELECT
name,
organization_name,
item_code,
item_type,
uom,
subinventory_code,
description,
inventory_item_id,
organization_id,
item_cost,
on_hand_quantity,
bucket1,
bucket2,
bucket3,
bucket4,
bucket5,
bucket6,
bucket7,
(
SELECT
nvl(SUM(mmt.primary_quantity),0)
FROM
mtl_material_transactions mmt
WHERE
1 = 1
AND mmt.organization_id = z.organization_id
AND mmt.inventory_item_id = z.inventory_item_id
AND mmt.subinventory_code = z.subinventory_code
AND mmt.primary_quantity < 0
AND trunc(mmt.transaction_date) <= TO_DATE(TO_CHAR(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’)
) issued_qty,
total,
product_category,
item_creation_date
FROM ( SELECT
a.name,
a.organization_name,
a.item_code,
a.item_type,
a.uom,
a.subinventory_code,
a.description,
a.inventory_item_id,
a.organization_id,
a.item_cost,
a.on_hand_quantity,
–(a.item_cost * a.on_hand_quantity) total
total,
product_category,
item_creation_date,
( (
SELECT
nvl(SUM(primary_quantity),0)
FROM
mtl_material_transactions b
WHERE
1 = 1
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code = a.subinventory_code
AND b.primary_quantity > 0
AND b.transaction_action_id NOT IN (
24,
30
)
AND b.transaction_type_id NOT IN (
10008
)
AND trunc(transaction_date) BETWEEN TO_DATE(TO_CHAR(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 30 AND TO_DATE(TO_CHAR
(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’)
) ) bucket1,
( (
SELECT
nvl(SUM(primary_quantity),0)
FROM
mtl_material_transactions b
WHERE
1 = 1
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code = a.subinventory_code
AND b.primary_quantity > 0
AND b.transaction_action_id NOT IN (
24,
30
)
AND b.transaction_type_id NOT IN (
10008
)
AND trunc(transaction_date) BETWEEN TO_DATE(TO_CHAR(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 60 AND TO_DATE(TO_CHAR
(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 31
) ) bucket2,
(
SELECT
nvl(SUM(primary_quantity),0)
FROM
mtl_material_transactions b
WHERE
1 = 1
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code = a.subinventory_code
AND b.primary_quantity > 0
AND b.transaction_action_id NOT IN (
24,
30
)
AND b.transaction_type_id NOT IN (
10008
)
AND trunc(transaction_date) BETWEEN TO_DATE(TO_CHAR(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 90 AND TO_DATE(TO_CHAR
(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 61
) bucket3,
(
SELECT
nvl(SUM(primary_quantity),0)
FROM
mtl_material_transactions b
WHERE
1 = 1
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code = a.subinventory_code
AND b.primary_quantity > 0
AND b.transaction_action_id NOT IN (
24,
30
)
AND b.transaction_type_id NOT IN (
10008
)
AND trunc(transaction_date) BETWEEN TO_DATE(TO_CHAR(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 180 AND TO_DATE(TO_CHAR
(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 91
) bucket4,
(
SELECT
nvl(SUM(primary_quantity),0)
FROM
mtl_material_transactions b
WHERE
1 = 1
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code = a.subinventory_code
AND b.primary_quantity > 0
AND b.transaction_action_id NOT IN (
24,
30
)
AND b.transaction_type_id NOT IN (
10008
)
AND trunc(transaction_date) BETWEEN TO_DATE(TO_CHAR(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 240 AND TO_DATE(TO_CHAR
(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 181
) bucket5,
(
SELECT
nvl(SUM(primary_quantity),0)
FROM
mtl_material_transactions b
WHERE
1 = 1
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code = a.subinventory_code
AND b.primary_quantity > 0
AND b.transaction_action_id NOT IN (
24,
30
)
AND b.transaction_type_id NOT IN (
10008
)
AND trunc(transaction_date) BETWEEN TO_DATE(TO_CHAR(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 365 AND TO_DATE(TO_CHAR
(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 241
) bucket6,
(
SELECT
nvl(SUM(primary_quantity),0)
FROM
mtl_material_transactions b
WHERE
1 = 1
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code = a.subinventory_code
AND b.primary_quantity > 0
AND b.transaction_action_id NOT IN (
24,
30
)
AND b.transaction_type_id NOT IN (
10008
)
AND trunc(transaction_date) < TO_DATE(TO_CHAR(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’) – 365
) bucket7
FROM ( SELECT
msi.concatenated_segments item_code,
–msi.item_type,
(
SELECT
flv.meaning
FROM
apps.mtl_system_items_b msb,
apps.fnd_lookup_values flv
WHERE
lookup_type = ‘ITEM_TYPE’
AND msb.item_type = flv.lookup_code
AND msb.organization_id = msi.organization_id
AND msb.inventory_item_id = msi.inventory_item_id
) item_type,
hou.name,
ood.organization_name,
msi.primary_unit_of_measure uom,
mmt.subinventory_code,
msi.description,
msi.inventory_item_id,
msi.organization_id,
cic.item_cost,
SUM(mmt.primary_quantity) on_hand_quantity,
( SUM(mmt.primary_quantity) * cic.item_cost ) total,
(
SELECT
rtrim(XMLAGG(XMLELEMENT(
e,m_c.segment1
|| ”
|| m_c.segment2,’,’
)
ORDER BY
m_c.segment1
|| ”
|| m_c.segment2
).extract(‘//text()’).getclobval(),’,’)
FROM
apps.mtl_system_items_b item,
apps.mtl_item_categories item_c,
apps.mtl_categories m_c,
apps.mtl_category_sets m_c_s
WHERE
item_c.inventory_item_id = msi.inventory_item_id
AND item.organization_id = item_c.organization_id
AND item.inventory_item_id = item_c.inventory_item_id
AND item_c.category_id = m_c.category_id
AND item.organization_id = msi.organization_id
AND item_c.category_set_id = m_c_s.category_set_id
AND upper(m_c_s.category_set_name) = upper(‘Humanscale OM Pricing Category’)
)
— ‘Purchasing Category Set’)
product_category,
TO_CHAR(msi.creation_date,’MM/DD/RRRR’) item_creation_date
FROM
apps.mtl_system_items_kfv msi,
mtl_material_transactions mmt,
cst_item_costs cic,
org_organization_definitions ood,
hr_operating_units hou
WHERE
msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id
AND cic.inventory_item_id = msi.inventory_item_id
AND cic.organization_id = msi.organization_id
AND cic.cost_type_id = 1
AND mmt.subinventory_code IS NOT NULL
AND ood.organization_id = mmt.organization_id
AND ood.operating_unit = hou.organization_id
AND mmt.transaction_action_id NOT IN (
24,
30
)
AND mmt.transaction_type_id NOT IN (
10008
)
AND hou.organization_id = :p_oper_unit
AND msi.organization_id = nvl(:p_inv_org,msi.organization_id)
AND mmt.transaction_date <= TO_DATE(TO_CHAR(SYSDATE,’MM/DD/RRRR HH:MI:SS AM’),’MM/DD/RRRR HH:MI:SS AM’)
HAVING
SUM(mmt.primary_quantity) IS NOT NULL
GROUP by msi.concatenated_segments,
msi.description,
msi.inventory_item_id,
msi.organization_id,
ood.organization_name,
msi.primary_unit_of_measure,
mmt.subinventory_code,
msi.item_type,
hou.name,
msi.creation_date,
cic.item_cost) a ) z ) p_out ) m_out
ORDER BY
subinventory_code,
inventory_item_id
For any queries, you can message us at contact@doyensys.com
