CST Cost Type Comparison

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Cost Type Comparison Report (XML)
Short Name: CSTRCTCRG_XML
DB package: BOM_CSTRCTCR_XMLP_PKG
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
View Cost Privilege
 
Number
Category Structure
 
Number
CST_SRS_BOM_OR_ENG
 
Quantity Precision
 
Number
Organization Id
 
Number
Minimum Unit Cost
 
Number
Minimum Amount Diff
 
Number
Minimum Percentage Diff
 
Number
To
 
Category From
 
Category Set
 
LOV Oracle
To
 
Item From
 
Group by
 
LOV Oracle
Cost Type 2
 
LOV Oracle
Cost Type 1
 
LOV Oracle
Report Sort Option
 
LOV Oracle