GMF Costed Indented Bill of Materials

Description
Categories: BI Publisher
Application: Process Manufacturing Financials
Source: Costed Indented Bill of Materials Report
Short Name: GMFCIBOM
DB package:
 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,  
					fm_matl_dtl 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
Parameter Name SQL text Validation
Legal Entity
 
LOV Oracle
Calendar
 
LOV Oracle
Period
 
LOV Oracle
Cost Type
 
LOV Oracle
From Organization
 
LOV Oracle
To Organization
 
LOV Oracle
From Item
 
To Item
 
From Cost Category
 
To Cost Category
 
Maximum BOM Levels
 
Number
Indentation Characters
 
Number
Report Type
 
LOV Oracle
Sort By
 
LOV Oracle
Dummay Structure ID
 
LOV Oracle
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: