SELECT ALL mtl.organization_id, orgs.NAME organization_name,
mtl.inventory_item_id, mtl.segment1 item_number,
mtl.description item_description, mtl.item_type,
mtl.inventory_item_status_code, mtl.primary_uom_code,
mtl.secondary_uom_code,
— get current onhand
( TO_NUMBER ((SELECT SUM (NVL (motv.on_hand, 0))
FROM apps.mtl_onhand_total_v motv
WHERE mtl.inventory_item_id =
motv.inventory_item_id
AND mtl.organization_id = motv.organization_id)
)
+ NVL ((SELECT -1 * SUM (NVL (primary_quantity, 0))
FROM mtl_material_transactions mmt
WHERE mmt.inventory_item_id = mtl.inventory_item_id
AND mmt.organization_id = mtl.organization_id
AND mmt.costed_flag IN (‘N’, ‘E’)
AND mmt.transaction_action_id NOT IN
(24, 40, 41, 50, 51, 52)
AND NVL (mmt.owning_tp_type, 2) = 2
AND mmt.organization_id =
NVL (mmt.owning_organization_id,
mmt.organization_id
)
AND NVL (mmt.logical_transaction, -1) <> 1),
0
)
) current_onhand ,
TO_NUMBER (NVL ((SELECT SUM (cict.item_cost)
FROM apps.cst_item_cost_type_v cict
WHERE mtl.inventory_item_id = cict.inventory_item_id(+)
AND mtl.organization_id = cict.organization_id(+)
AND cict.cost_type = ‘Frozen’),
0
)
) current_cost,
DECODE (mtl.planning_make_buy_code,
2, ‘Buy’,
1, ‘Make’,
‘Other’
) make_buy_code,
(SELECT organization_code
FROM org_organization_definitions ood
WHERE orgs.organization_id =
ood.organization_id)
organization_code
FROM apps.mtl_system_items_b mtl,
apps.hr_all_organization_units orgs,
apps.mtl_item_categories_v cat
WHERE orgs.organization_id = mtl.organization_id
AND ( mtl.inventory_item_id = cat.inventory_item_id(+)
AND mtl.organization_id = cat.organization_id(+)
AND cat.category_set_id = 1
)
AND (SELECT SUM (motv.on_hand)
FROM apps.mtl_onhand_total_v motv
WHERE mtl.inventory_item_id = motv.inventory_item_id
AND mtl.organization_id = motv.organization_id) > 0;
mtl.inventory_item_id, mtl.segment1 item_number,
mtl.description item_description, mtl.item_type,
mtl.inventory_item_status_code, mtl.primary_uom_code,
mtl.secondary_uom_code,
— get current onhand
( TO_NUMBER ((SELECT SUM (NVL (motv.on_hand, 0))
FROM apps.mtl_onhand_total_v motv
WHERE mtl.inventory_item_id =
motv.inventory_item_id
AND mtl.organization_id = motv.organization_id)
)
+ NVL ((SELECT -1 * SUM (NVL (primary_quantity, 0))
FROM mtl_material_transactions mmt
WHERE mmt.inventory_item_id = mtl.inventory_item_id
AND mmt.organization_id = mtl.organization_id
AND mmt.costed_flag IN (‘N’, ‘E’)
AND mmt.transaction_action_id NOT IN
(24, 40, 41, 50, 51, 52)
AND NVL (mmt.owning_tp_type, 2) = 2
AND mmt.organization_id =
NVL (mmt.owning_organization_id,
mmt.organization_id
)
AND NVL (mmt.logical_transaction, -1) <> 1),
0
)
) current_onhand ,
TO_NUMBER (NVL ((SELECT SUM (cict.item_cost)
FROM apps.cst_item_cost_type_v cict
WHERE mtl.inventory_item_id = cict.inventory_item_id(+)
AND mtl.organization_id = cict.organization_id(+)
AND cict.cost_type = ‘Frozen’),
0
)
) current_cost,
DECODE (mtl.planning_make_buy_code,
2, ‘Buy’,
1, ‘Make’,
‘Other’
) make_buy_code,
(SELECT organization_code
FROM org_organization_definitions ood
WHERE orgs.organization_id =
ood.organization_id)
organization_code
FROM apps.mtl_system_items_b mtl,
apps.hr_all_organization_units orgs,
apps.mtl_item_categories_v cat
WHERE orgs.organization_id = mtl.organization_id
AND ( mtl.inventory_item_id = cat.inventory_item_id(+)
AND mtl.organization_id = cat.organization_id(+)
AND cat.category_set_id = 1
)
AND (SELECT SUM (motv.on_hand)
FROM apps.mtl_onhand_total_v motv
WHERE mtl.inventory_item_id = motv.inventory_item_id
AND mtl.organization_id = motv.organization_id) > 0;
Recommended Posts