SELECT
GMD_ICR01USR_XMLP_PKG.line_nocfformula(a.line_type, a.ITEM_ID, a.formula_id) line_noCF,
GMD_ICR01USR_XMLP_PKG.level_nocfformula(GMD_ICR01USR_XMLP_PKG.line_nocfformula(a.line_type, a.ITEM_ID, a.formula_id), a.level_num) level_noCF,
GMD_ICR01USR_XMLP_PKG.batch_qtycfformula(a.explode_seq) batch_qtyCF,
GMD_ICR01USR_XMLP_PKG.form_qtycfformula(a.explode_seq) form_qtyCF,
GMD_ICR01USR_XMLP_PKG.standard_qtyformula(a.formula_id, a.level_num) standard_qty,
GMD_ICR01USR_XMLP_PKG.uomcfformula(a.explode_seq) uomCF ,
--GMD_ICR01USR_XMLP_PKG.indent1formula(:IndentCF) IndentCF,
a.*
FROM ( SELECT
DISTINCT f.INVENTORY_ITEM_ID item_id ,
LPAD(' ',(level_num - 1) * 2 + 1,' ')||DECODE(f.line_type,2,' Byproduct:',1,' Coproduct:',' ' )||i.concatenated_segments|| ':' ||i.DESCRIPTION Item_Desc,
substr(LPAD(' ',(level_num - 1) * 2 - 1,' ')||' Formula:'||ffm1.formula_no|| ':'||ffm1.formula_vers||' -- '||SUBSTR(ffm1.formula_desc1,1,30) ,1,65) Formula_Dtl,
f.level_num level_num,
f.scale_qty scale_qty2 ,
f.formula_qty formula_qty2,
f.std_qty ,
f.i_qty_uom i_qty_uom2,
f.formula_id formula_id,
f.line_no line_no,
f.line_type line_type,
f.top_formula_id top_formula_id3,
f.line_desc line_desc2,
f.explode_seq
FROM fm_ibom_dtl f,
MTL_SYSTEM_ITEMS_KFV i,
fm_form_mst ffm1
WHERE i.inventory_item_id = f.inventory_item_id
AND f.formula_id = ffm1.formula_id
AND f.top_formula_id = :p_formula_id
and f.top_formula_id=:top_formula_id
ORDER BY f.explode_seq) a |
