Formula list

 SELECT ffm.formula_no, ffm.formula_desc1 formula_name,

DECODE (fmd.line_type, 1, 'Product', 'Ingredient') TYPE,

msi.segment1 icode, ood.organization_code org, fmd.qty,

fmd.detail_uom, DECODE (ffm.formula_status, 700, 'Active', 'New') status

FROM apps.fm_matl_dtl fmd, apps.fm_form_mst ffm, apps.mtl_system_items_b msi,

apps.org_organization_definitions ood

WHERE fmd.formula_id = ffm.formula_id

AND (fmd.inventory_item_id = msi.inventory_item_id(+)

AND fmd.organization_id = msi.organization_id(+))

AND fmd.organization_id = ood.organization_id

AND fmd.line_type=1 ORDER BY formula_no,

DECODE (fmd.line_type, 1, 'Product', 'Ingredient') DESC;

SELECT ffm.formula_no, ffm.formula_desc1 formula_name,

DECODE (fmd.line_type, 1, 'Product', 'Ingredient') TYPE,

msi.segment1 icode, ood.organization_code org, fmd.qty,

fmd.detail_uom, DECODE (ffm.formula_status, 700, 'Active', 'New') status

FROM apps.fm_matl_dtl fmd, apps.fm_form_mst ffm, apps.mtl_system_items_b msi,

apps.org_organization_definitions ood

WHERE fmd.formula_id = ffm.formula_id

AND (fmd.inventory_item_id = msi.inventory_item_id(+)

AND fmd.organization_id = msi.organization_id(+))

AND fmd.organization_id = ood.organization_id

select s.ORGANIZATION_ID,msi.SEGMENT1,msi.DESCRIPTION from GMD_RECIPE_VALIDITY_RULES s,apps.mtl_system_items_b msi 

where s.ORGANIZATION_ID in (3762,3763) AND msi.ATTRIBUTE1='562' AND s.inventory_item_id = msi.inventory_item_id(+) group by s.ORGANIZATION_ID,msi.SEGMENT1,msi.DESCRIPTION

select distinct s.ORGANIZATION_ID,msi.SEGMENT1,msi.DESCRIPTION,msi.PRIMARY_UOM_CODE UOM

from GMD_RECIPE_VALIDITY_RULES s,apps.mtl_system_items_b msi 

where s.ORGANIZATION_ID in (3762,3763) 

---AND msi.ATTRIBUTE1='562' 

and s.ORGANIZATION_ID = msi.ORGANIZATION_ID

and s.inventory_item_id = msi.inventory_item_id

update----

select decode(x.ORGANIZATION_ID,3762,'PI6',3763,'PI7') ORG ,x.SEGMENT1 ITEM_CODE,x.DESCRIPTION ITEM_NAME,UOM from( 

select distinct s.ORGANIZATION_ID,msi.SEGMENT1,msi.DESCRIPTION,msi.PRIMARY_UOM_CODE UOM

from GMD_RECIPE_VALIDITY_RULES s,apps.mtl_system_items_b msi 

where s.ORGANIZATION_ID in (3762,3763) 

---AND msi.ATTRIBUTE1='562' 

and s.ORGANIZATION_ID = msi.ORGANIZATION_ID

and s.inventory_item_id = msi.inventory_item_id)x

select decode(s.ORGANIZATION_ID,3762,'PI6',3763,'PI7') ORG ,msi.SEGMENT1 ITEM_CODE,msi.DESCRIPTION ITEM_NAME from GMD_RECIPE_VALIDITY_RULES s,apps.mtl_system_items_b msi 

where s.ORGANIZATION_ID in (3762,3763) AND msi.ATTRIBUTE1='562' AND s.inventory_item_id = msi.inventory_item_id(+) group by s.ORGANIZATION_ID,msi.SEGMENT1,msi.DESCRIPTION