CST Detailed Item Cost

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Detailed Item Cost Report (XML)
Short Name: CSTRDICRG_XML
DB package: BOM_CSTRDICR_XMLP_PKG
select 
       null ITEM_NUM,
       msi.primary_uom_code UOM,
       msi.description DESCRIP,
       null CATEGORY,
       cic.shrinkage_rate SHRINKAGE,
       cdcv.operation_seq_num OP_SEQ,
       cdcv.cost_level OP_LEVEL,
       substr(cdcv.cost_element,1,13) CST_ELEM,
       cdcv.resource_code CST_CODE,
       cdcv.unit_of_measure UOM2,
       substr(cdcv.basis,1,11) BASIS,
       cdcv.cost_source_type source_type,
       cic.lot_size LOT_SIZE,
       round(cdcv.basis_factor, :P_qty_precision) BAS_FACTOR,
       round(cdcv.resource_rate, :P_qty_precision) RES_CST,
       round(decode(cdcv.cost_element_id,
              3, cdcv.usage_rate_or_amount,
              4, cdcv.usage_rate_or_amount,
             null), :P_qty_precision)               RATE,
       round(decode(cdcv.cost_element_id,
              1, cdcv.usage_rate_or_amount,
              3, null,  
              4,null,
              cdcv.usage_rate_or_amount), :P_qty_precision) MATL_OVHD,
       round(cdcv.item_cost, :ext_precision)            UNIT_CST,
       cdcv.activity ACTIVITY,
       cdcv.output_uom OUTPUT_MEAS,
       cdcv.item_units NUM_ITEMS,
       cdcv.activity_units ACT_PER_ITEM,
       round(decode(cdcv.cost_element_id,
                    1,cdcv.item_cost,0), :ext_precision) C_MATL,
       round(decode(cdcv.cost_element_id,
                   2, cdcv.item_cost,0),
                                               :ext_precision) C_MATL_OVHD,
       round(decode(cdcv.cost_element_id,
                   3,cdcv.item_cost,0),:ext_precision) C_RESOURCE,
       round(decode(cdcv.cost_element_id,
                    4, cdcv.item_cost,0),
                                               :ext_precision) C_OUTSIDE,
       round(decode(cdcv.cost_element_id,
                    5,cdcv.item_cost,0), :ext_precision) C_OVHD, 
	BOM_CSTRDICR_XMLP_PKG.order_func(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexpad_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'), fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexpad_cat', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE'), msi.primary_uom_code) C_ORDER, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexpad_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_FLEXPAD_ITEM, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexpad_cat', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') C_FLEXPAD_CAT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_num_flex', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_NUM_FLEX, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_flex', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_CAT_FLEX
from 
     mtl_system_items_vl msi,
     cst_item_costs cic,
     cst_detail_cost_view cdcv, 
     mtl_parameters mp, 
     mtl_categories mc,
     mtl_item_categories mic
where
         mp.organization_id = :P_ORG_ID
and      msi.organization_id = :P_ORG_ID
and      cic.inventory_item_id = msi.inventory_item_id
and      mp.cost_organization_id = cic.organization_id
and      cic.cost_type_id = :P_CST_TYPE
and      cdcv.inventory_item_id = cic.inventory_item_id
and      cdcv.organization_id = mp.cost_organization_id
and      cdcv.cost_type_id = :P_CST_TYPE     
and      mic.inventory_item_id = cic.inventory_item_id
and      mic.organization_id = :P_ORG_ID
and      mic.category_set_id = :P_CATEGORY_SET
and      mic.category_id = mc.category_id
and      mc.structure_id = :P_STRUCT_NUM  
and      &P_WHERE_ITEM 
and      &P_WHERE_CAT   
order by 28, 6,  7 desc, 8, 9
Ask a question
Parameter Name SQL text Validation
View Cost Privilege
 
Number
Category Structure
 
Number
CST_SRS_BOM_OR_ENG
 
Quantity Precision
 
Number
Organization Id
 
Number
Summary by Element
 
LOV Oracle
To
 
Category From
 
Category Set
 
LOV Oracle
To
 
Item From
 
Cost Type
 
LOV Oracle
Report Sort Option
 
LOV Oracle