GMD Indented Bill Of Materials

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Product Development
Source: Indented Bill Of Materials Report (XML)
Short Name: ICR01USR_XML
DB package: GMD_ICR01USR_XMLP_PKG
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
Parameter Name SQL text Validation
Formula Number
 
LOV Oracle
Ask a question