| To get manufacturer name join - subqueries [message #446004] |
Fri, 05 March 2010 02:07 |
sudharshan
Messages: 48
Registered: November 2006
|
Member |
|
|
i want to get the on hand quantity for an item along with the manufacturer details for all the organizations
To get the onhand quantity
SELECT DISTINCT msi.segment1, msi.description, moq.subinventory_code,
ood.organization_code, ood.organization_name,
SUM (moq.transaction_quantity)
FROM apps.mtl_onhand_quantities moq,
apps.mtl_system_items msi,
apps.org_organization_definitions ood
WHERE moq.inventory_item_id = msi.inventory_item_id
AND moq.organization_id = msi.organization_id
AND ood.organization_id = moq.organization_id
AND msi.segment1 = :item_code
GROUP BY msi.segment1,
msi.description,
moq.subinventory_code,
ood.organization_code,
ood.organization_name
ORDER BY msi.segment1
To get the manufacturer details
Note: the manufacturer details are stored in item master organization only.
SELECT msi.segment1 item_code, msi.description, mfg.mfg_part_num,
mf_name.manufacturer_name
FROM mtl_system_items_b msi,
mtl_mfg_part_numbers mfg,
mtl_manufacturers mf_name
WHERE msi.inventory_item_id = mfg.inventory_item_id(+)
AND msi.organization_id = mfg.organization_id(+)
AND mfg.manufacturer_id = mf_name.manufacturer_id(+)
AND msi.organization_id = :item_organization
ORDER BY msi.segment1
Question
----------
How do i join both the queries to get onhand along with the manufacturer details?
Regards
Sudharshan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
