Home » Applications » Oracle Fusion Apps & E-Business Suite » To get manufacturer name join - subqueries (12.1 windows )
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



Previous Topic: Forms Configurator - DFF Error?
Next Topic: deleting receipt method
Goto Forum:
  


Current Time: Sun Dec 04 22:47:34 CST 2016

Total time taken to generate the page: 0.08685 seconds