GMF Costed Indented Bill of Materials

 SELECT DISTINCT prod_ind, BEXP.ORDER_LINK,
					LEVEL2 C_LEVEL,
					cpt.cost_cmpntcls_code C_COST_COMPONENT,
	(LEVEL2 || cpt.cost_cmpntcls_code|| cpt.c_usage || cpt.cost_analysis_code || formulaline_id) C_LC,
					cpt.C_USAGE,
					cpt.cost_analysis_code C_ANALYSIS_CODE,
					/* Item_name C_ITEM_NAME, */ /* Commented against BUG#14102431 */
					Decode(prod_ind, 'P', Item_name||' '||rcp, Item_name) C_ITEM_NAME, /* Added against BUG#14102431 */
					/* (TO_NUMBER(SUBSTR(QTY,1,(INSTR(QTY,'/')-1))) * (SUM(cst.cmpnt_cost) OVER (PARTITION BY cst.organization_id,cst.inventory_item_id,LEVEL2,ccm.cost_cmpntcls_code,cst.cost_analysis_code))) C_EXT_COMP_COST, */ /* Commented against BUG#14102431 */
					(TO_NUMBER((SUBSTR(qty,1,(INSTR(qty,'/')-1))+ (SUBSTR(qty,1,(INSTR(qty,'/')-1)) * scrap_factor))) * (cpt.unit_cost1)) C_EXT_COMP_COST, /* Added against BUG#14102431 */
					cpt.unit_cost1 C_COMPONENT_COST,	
					DECODE(LEVEL2,0,(TO_NUMBER((SUBSTR(qty,1,(INSTR(qty,'/')-1))+ (SUBSTR(qty,1,(INSTR(qty,'/')-1)) * scrap_factor))) * (cpt.unit_cost1)),0) C_EXT_COMP_COST1, /* Added against BUG#14102431 */
					DECODE(LEVEL2,0,cpt.unit_cost1,0) C_COMPONENT_COST1,
					/* DECODE(prod_ind,'P',
					(TO_NUMBER(SUBSTR(QTY,1,(INSTR(QTY,'/')-1))) *
					(SUM(cst.cmpnt_cost) OVER (PARTITION BY 
					cst.inventory_item_id,cst.organization_id ))) , 
					(TO_NUMBER(SUBSTR(QTY,1,(INSTR(QTY,'/')-1))) * bexp.cost_alloc *
					(SUM(cst.cmpnt_cost) OVER (PARTITION BY 
					cst.inventory_item_id,cst.organization_id ))) 
					)C_EXT_UNIT_COST, */ /* Commented against BUG#14102431 */
					DECODE(prod_ind,'P',
					(TO_NUMBER((SUBSTR(qty,1,(INSTR(qty,'/')-1))+ (SUBSTR(qty,1,(INSTR(qty,'/')-1)) * scrap_factor))) * (cpt.unit_cost1)), 
					(TO_NUMBER((SUBSTR(qty,1,(INSTR(qty,'/')-1))+ (SUBSTR(qty,1,(INSTR(qty,'/')-1)) * scrap_factor))) * bexp.cost_alloc * (cpt.unit_cost1))
					)C_EXT_UNIT_COST, /* Added against BUG#14102431 */
					cpt.unit_cost1 C_UNIT_COST,
                    /* TO_NUMBER(SUBSTR(QTY,1,(INSTR(QTY,'/')-1))) C_QUANTITY, */ /* Commented against BUG#14102431 */
					TO_NUMBER((SUBSTR(qty,1,(INSTR(qty,'/')-1))+ (SUBSTR(qty,1,(INSTR(qty,'/')-1)) * scrap_factor))) C_QUANTITY, /* Added against BUG#14102431 */
                    LTRIM(SUBSTR(QTY,(INSTR(QTY,'/')+1)),' ') C_UOM,
					DECODE(cpt.unit_cost1,0,0,( ((cpt.unit_cost1)/(cpt.unit_cost1))* 100)) C_PERCENTAGE,
					FORMULA_QTY C_FORMULA_QTY, FORMULA_UOM C_FORMULA_UOM,
					primary_uom_code,secondary_uom_code,
					DECODE(secondary_uom_code,NULL,NULL,
					inv_convert.inv_um_convert(
					bexp.item_id,
					NULL,
					bexp.organization_id,
					5,
					TO_NUMBER(SUBSTR(QTY,1,(INSTR(QTY,'/')-1))),
					bexp.primary_uom_code,
					bexp.secondary_uom_code,
					NULL, NULL) ) C_SECONDARY_QTY
					FROM  
					(SELECT SYS_CONNECT_BY_PATH (ccd.INVENTORY_ITEM_ID,'.') ORDER_LINK,  
					DECODE(fmd.line_type,1,DECODE(grv.inventory_item_id,fmd.inventory_item_id,'P','C'),-1,'I',2,'B')  
					prod_ind,CONNECT_BY_ISLEAF isleaf,  
					DECODE(fmd.line_type,1,(LEVEL-1),LEVEL) LEVEL2,  
					msi.concatenated_segments Item_name,
					msi.primary_uom_code,msi.secondary_uom_code,msi.dual_uom_control,
					ccd.INVENTORY_ITEM_ID PRODUCT_ITEM, ccd.organization_id,  
					ccd.fmeff_id, grv.std_qty, gr.formula_id, ffm.formula_no ||'-VER:' || ffm.formula_vers FORMULA_DTL,  
fmd.line_type,fmd.scrap_factor,fmd.INVENTORY_ITEM_ID ITEM_ID, 
					fmd.qty FORMULA_QTY,fmd.formulaline_id,fmd.detail_uom FORMULA_UOM, /* Added fmd.scrap_factor against BUG#14102431 */
				 gmf_cbom_rep_pkg.Get_cost_alloc(fmd.inventory_item_id,
                                ccd.inventory_item_id,
                                DECODE(fmd.line_type,1,DECODE(grv.inventory_item_id,fmd.inventory_item_id,'P','C'), -1,'I',2,'B'),
								msi.organization_id,
								ccd.cost_type_id,
								ccd.period_id,
								fmd.qty)  Cost_alloc,
					ccd.period_id, ccd.cost_type_id,  
					gr.recipe_id, 'Recipe:' || (gr.RECIPE_NO || '/'|| gr.RECIPE_VERSION) RCP,grv.revision,grv.detail_uom,
					gmf_cbom_rep_pkg.Get_Quantity(fmd.inventory_item_id,
                                ccd.inventory_item_id,
                                DECODE(fmd.line_type,1,DECODE(grv.inventory_item_id,fmd.inventory_item_id,'P','C'), -1,'I',2,'B'),
								msi.organization_id,
								ccd.cost_type_id,
								ccd.period_id,
								fmd.qty) QTY
					FROM  
					(SELECT DISTINCT ORGANIZATION_ID, INVENTORY_ITEM_ID, period_id,  
					cost_type_id, fmeff_id  
					FROM cm_cmpt_dtl  
					WHERE period_id = &P_PERIOD_ID AND cost_type_id = &P_COST_TYPE_ID
					  AND organization_id = &H_ORG_ID) ccd,  
					gmd_recipes_b gr,  
					gmd_recipe_validity_rules grv,  
					(select fmd.* from fm_matl_dtl fmd,fm_form_mst_b ffm 
							where ffm.formula_id = fmd.formula_id 
							AND ffm.FORMULA_STATUS in (700,900)) fmd,  
					fm_form_mst_b ffm,  
					mtl_system_items_b_kfv msi 
					WHERE gr.recipe_id = grv.recipe_id  
					AND   ccd.fmeff_id = grv.recipe_validity_rule_id  
					AND   ccd.INVENTORY_ITEM_ID  = grv.INVENTORY_ITEM_ID  
					AND   ccd.organization_id = NVL(grv.organization_id,ccd.organization_id)  
					AND   msi.INVENTORY_ITEM_ID  = fmd.INVENTORY_ITEM_ID  
					AND gr.recipe_status in (700,900)
					AND   ccd.organization_id = msi.organization_id  
					AND   fmd.formula_id =  gr.formula_id  
					AND   ffm.formula_id = fmd.formula_id  
					AND   ccd.organization_id = &H_ORG_ID  
					  /*26003084 */
					  AND grv.delete_mark = 0
  AND NOT EXISTS
    (SELECT 1
    FROM gmd_recipe_validity_rules grv1
    WHERE grv.preference      > grv1.preference
    AND grv.inventory_item_id = grv1.inventory_item_id
    )
  AND NOT EXISTS
    (SELECT 1
    FROM gmd_recipe_validity_rules grv1
    WHERE grv.recipe_use < grv1.recipe_use
    AND grv.recipe_id    = grv1.recipe_id
    ) 
	/*26003084 */
					START WITH  ccd.inventory_item_id = &H_ITEM_ID AND ccd.fmeff_id IS NOT NULL  
					CONNECT BY PRIOR fmd.INVENTORY_ITEM_ID = ccd.INVENTORY_ITEM_ID AND msi.organization_id = ccd.organization_id  
					AND PRIOR fmd.line_type <> 1  
					) BEXP,
					/* cm_cmpt_mst ccm,
					cm_cmpt_dtl cst */ /* Commented against BUG#14102431 */
					(SELECT ccd.inventory_item_id,ccd.organization_id, ccd.period_id, ccd.cost_type_id, 	ccd.cost_analysis_code,ccm.cost_cmpntcls_code,
						DECODE(ccm.usage_ind, 1, 'Material',2, 'Overhead',3, 'Resource',4, 'Expense Alloc',5, 'Std Cost Adj') C_USAGE,
						SUM(ccd.cmpnt_cost * DECODE(ccm.usage_ind, 1, 1, 0)) material_cost,
						SUM(ccd.cmpnt_cost * DECODE(ccm.usage_ind, 3, 1, 0)) resource_cost,
						SUM(ccd.cmpnt_cost * DECODE(ccm.usage_ind, 2, 1, 0)) fixed_overhead,
						SUM(ccd.cmpnt_cost * DECODE(ccm.usage_ind, 5, 1, 0)) std_cost_adj,
						SUM(ccd.cmpnt_cost) unit_cost1 
						FROM cm_cmpt_dtl ccd, cm_cmpt_mst ccm 
						WHERE ccm.cost_cmpntcls_id = ccd.cost_cmpntcls_id  AND 
							  ccd.period_id = &P_PERIOD_ID AND 
							  ccd.cost_type_id = &P_COST_TYPE_ID  AND 
							  ccd.organization_id = &H_ORG_ID 
						GROUP BY  ccd.inventory_item_id,ccd.organization_id ,ccd.period_id,ccd.cost_type_id,
							  ccd.cost_analysis_code,ccm.cost_cmpntcls_code,ccm.usage_ind) cpt   /* Added cpt against BUG#14102431 */
					WHERE 1 = 1
					    /* DECODE(BEXP.ISLEAF,0,(DECODE(BEXP.PROD_IND,'I',-1,BEXP.PROD_IND)),BEXP.ISLEAF) = 1 */ /* Commented against BUG#14102431 */
					AND BEXP.PROD_IND <> 'C'  
					AND cpt.inventory_item_id = BEXP.item_id  
					AND BEXP.period_id = cpt.period_id  
					AND BEXP.cost_type_id = cpt.cost_type_id  
					AND BEXP.organization_id = cpt.organization_id  
					AND BEXP.LEVEL2 <= NVL(&P_MAX_LEVELS,BEXP.LEVEL2) 
					ORDER BY BEXP.ORDER_LINK,BEXP.LEVEL2,cpt.cost_cmpntcls_code,cpt.c_usage,cpt.cost_analysis_code,C_Item_name