SQL Query for Oracle OPM Receipe and Formula Dump

SQL Query for Oracle OPM Receipe and Formula Dump

select gmd.RECIPE_NO Receipe_code,RECIPE_DESCRIPTION Receipe_Name ,gmd.RECIPE_VERSION,
(select distinct description from mtl_system_items mtl1,FM_MATL_DTL fd1
where fd1.line_type=1
and fd1.formula_id=fd.formula_id
and fd1.inventory_item_id = mtl1.inventory_item_id
and fd1.organization_id= mtl1.organization_id) product,
fm.formula_no,fm.FORMULA_DESC1,fm.FORMULA_VERS,
decode (gmd.RECIPE_STATUS,'900', 'Frozen','700','Approved for General Use') Rec_Status,
b.segment1||'-'||b.segment2 Item_code,
b.description item_desc,
fd.line_no,
fd.qty
from FM_FORM_MST fm,
FM_MATL_DTL fd,
mtl_system_items b,
GMD_RECIPE_VALIDITY_RULES GV,
gmd_recipes gmd
where  fd.formula_id=fm.formula_id
and fd.organization_id=fm.OWNER_ORGANIZATION_ID
and fm.formula_id =gmd.formula_id
and fm.owner_organization_id=gmd.owner_organization_id
AND GV.RECIPE_ID=GMd.RECIPE_ID
and b.inventory_item_id=fd.inventory_item_id
and b.organization_id=fm.OWNER_ORGANIZATION_ID
and fm.OWNER_ORGANIZATION_ID in (XXXX)
and fd.line_type in (-1,2)
--and fm.FORMULA_STATUS=700
and gmd.RECIPE_STATUS in (700)
--and gmd.RECIPE_NO=''XXXX
order by 1