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 |
