CST Cost Type Comparison

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Cost Type Comparison Report
Application: Bills of Material
Source: Cost Type Comparison Report (XML)
Short Name: CSTRCTCRG_XML
DB package: BOM_CSTRCTCR_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
        decode(:report_sort_opt,
                      1, msi.padded_concatenated_segments ||
                          mc.padded_concatenated_segments ||
                          rpad(msi.primary_uom_code, 3),
                      2,  mc.concatenated_segments || 
                           msi.padded_concatenated_segments ||
                           rpad(msi.primary_uom_code, 3)) sort_order,
       msi.concatenated_segments item_data,
       msi.padded_concatenated_segments padded_item_data,
       mc.concatenated_segments  cat_data,
       mc.padded_concatenated_segments padded_cat_data,
       msi.description item_desc,
       msi.primary_uom_code uom_code,
       &COLUMN1 column1_name,
       round(sum(decode(cdcv.cost_type_id, :cost_type_id1,
                        cdcv.item_cost,0)), :ext_precision_saved) type1,
       round(sum(decode(cdcv.cost_type_id, :cost_type_id2,
                        cdcv.item_cost,0)), :ext_precision_saved) type2,
       round(sum(decode(cdcv.cost_type_id, :cost_type_id2,
                        cdcv.item_cost,0)) -
             sum(decode(cdcv.cost_type_id, :cost_type_id1,
                      cdcv.item_cost,0)), :ext_precision_saved) difference,
       round(decode(sum(decode(cdcv.cost_type_id, :cost_type_id1,
                                cdcv.item_cost,0)), 0,
       (decode(sum(decode(cdcv.cost_type_id, :cost_type_id2,
                                cdcv.item_cost,0)), 0, 0, 100)),
             ((sum(decode(cdcv.cost_type_id, :cost_type_id2,
                                cdcv.item_cost,0)) /
               sum(decode(cdcv.cost_type_id, :cost_type_id1,
                            cdcv.item_cost,0))) - 1) * 100), 2) per_change, 
	BOM_CSTRCTCR_XMLP_PKG.tot_per_changeformula(:tot_item_cost1, :tot_item_cost2, :tot_difference) tot_per_change
from  &P_TABLE_ORDER 
where 
            msi.organization_id = :org_id 
   and   mc.structure_id = :P_STRUCT_NUM  
   and   mic.category_id = mc.category_id
   and   mic.organization_id = :org_id  
   and   mic.category_set_id = :P_CATEGORY_SET
   and   msi.inventory_item_id = mic.inventory_item_id 
   and   cic.inventory_item_id = msi.inventory_item_id
   and   (cic.cost_type_id = :cost_type_id1
   or       (cic.cost_type_id = :cost_type_id2 and exists
                                      (select 'x' from cst_item_costs
                                        where inventory_item_id = cic.inventory_item_id
                                           and  cost_type_id = :cost_type_id1
                                          and   organization_id  = :org_id) )
           )
   and  cic.organization_id = :org_id
   and  cdcv.inventory_item_id = cic.inventory_item_id
   and  cdcv.cost_type_id  =  cic.cost_type_id
   and  cdcv.inventory_item_id = mic.inventory_item_id
   and  msi.inventory_item_id = cdcv.inventory_item_id
   and  cdcv.organization_id = :org_id
   and  &CREATE_WHERE_ITEM1 &P_WHERE_ITEM &CREATE_WHERE_ITEM2
   and  &CREATE_WHERE_CAT1 &P_WHERE_CAT &CREATE_WHERE_CAT2
   --and mc.category_id in (select category_id from mtl_categories MCT where 1 = 1and MCT.structure_id =  101  )
group by msi.concatenated_segments,
                 msi.padded_concatenated_segments,
                 mc.concatenated_segments,
                 mc.padded_concatenated_segments,
                 msi.description,
                 msi.primary_uom_code,
                &COLUMN1
ORDER BY 1 ASC
Parameter Name SQL text Validation
Report Sort Option 
 
 LOV Oracle
Cost Type 1 
 
 LOV Oracle
Cost Type 2 
 
 LOV Oracle
Group by 
 
 LOV Oracle
Item From 
 
 Char
To 
 
 Char
Category Set 
 
 LOV Oracle
Category From 
 
 Char
To 2 
 
 Char
Minimum Percentage Diff 
 
 Number
Minimum Amount Diff 
 
 Number
Minimum Unit Cost 
 
 Number
Download
 
Related Blitz Reports
CAC Material Account Summary CAC ICP PII Material Account Detail CAC ICP PII Material Account Summary CAC Where Used by Cost Type CAC Intercompany SO Price List vs. Item Cost Comparison CAC WIP Resource Efficiency CAC WIP Resource Efficiency with Method and Lot Variances CAC Onhand Lot Value (Real-Time) CAC Manufacturing Variance Summary CST Item Cost Upload
Blitz Report™

Blitz Report™ provides multiple benefits: