OPM Formulas

Description
Categories: Draft
Columns: Record Type, Formula No, Formula Vers, Formula Desc1, Formula Desc2, Owner Organization, Total Input Qty, Total Output Qty, Status, Line No ...
Summary listing of all active formulas
SELECT
      'I' record_type
,     frm.formula_no
,     frm.formula_vers
,     frmtl.formula_desc1
,     frmtl.formula_desc2
,     mp.organization_code owner_organization
,     frm.total_input_qty
,     frm.total_output_qty
,     DECODE (frm.formula_status, 700, 'Active', 'New') status
,     dtl.line_no
,     DECODE (dtl.line_type, 1, 'Product', 'Ingredient') TYPE
,     msib.segment1 item_no
,     dtl.qty
,     dtl.detail_uom
,     dtl.release_type
,     dtl.scrap_factor
,     dtl.scale_type scale_type_dtl
,     dtl.cost_alloc
,     dtl.contribute_yield_ind
FROM  fm_form_mst_b frm
,     fm_form_mst_tl frmtl
,     fm_matl_dtl dtl
,     mtl_system_items_b msib
,     mtl_parameters mp
WHERE 1=1
and frm.formula_id = dtl.formula_id
and   frm.formula_id = frmtl.formula_id and frmtl.language = 'US'
and   frm.owner_organization_id = mp.organization_id
and   dtl.inventory_item_id = msib.inventory_item_id 
and   mp.organization_id=msib.organization_id
--AND   frm.formula_vers = '100'
AND   frm.formula_status = '700'
ORDER BY  frm.formula_no, 
dtl.line_type desc, dtl.line_no
Parameter Name SQL text Validation
Organization Code
mp.organization_code=:organization_code
LOV