CST Periodic Inventory Value

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Periodic Inventory Value Report
Application: Bills of Material
Source: Periodic Inventory Value Report (XML)
Short Name: CSTRPICR_XML
DB package: BOM_CSTRPICR_XMLP_PKG
Run CST Periodic Inventory Value and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
  null IC_ITEM_NUMBER
, null IC_CATEGORY
,msi.description IC_DESCRIPTION
,msi.primary_uom_code IC_UOM
, ROUND(NVL(cpic.total_layer_quantity,0),:p_qty_precision) IC_QTY
, ROUND((NVL(cpic.item_cost,0)* :p_exchange_rate),:p_ext_prec) IC_UNIT_COST
, ROUND((NVL(cpic.item_cost,0)* :p_exchange_rate),:p_precision) IC_STDUNIT_COST
, NVL(cpic.item_cost,0)*NVL(cpic.total_layer_quantity,0)*:p_exchange_rate IC_TOTAL_COST
, ROUND((NVL(cpic.material_cost,0)* :p_exchange_rate),:p_ext_prec) IC_MATL_COST
, ROUND((NVL(cpic.material_overhead_cost,0)* :p_exchange_rate),:p_ext_prec) IC_MO_COST
, ROUND((NVL(cpic.resource_cost,0)* :p_exchange_rate),:p_ext_prec) IC_RES_COST
, ROUND((NVL(cpic.overhead_cost,0)* :p_exchange_rate),:p_ext_prec) IC_OVHD_COST
, ROUND((NVL(cpic.outside_processing_cost,0)* :p_exchange_rate),:p_ext_prec) IC_OSP_COST
, NVL(cpic.material_cost,0)*NVL(cpic.total_layer_quantity,0)*:p_exchange_rate IC_TOT_MATL_COST
, NVL(cpic.material_overhead_cost,0)*NVL(cpic.total_layer_quantity,0)*:p_exchange_rate IC_TOT_MO_COST
, NVL(cpic.resource_cost,0)*NVL(cpic.total_layer_quantity,0)*:p_exchange_rate IC_TOT_RES_COST
, NVL(cpic.overhead_cost,0)*NVL(cpic.total_layer_quantity,0)*:p_exchange_rate IC_TOT_OVHD_COST
, NVL(cpic.outside_processing_cost,0)*NVL(cpic.total_layer_quantity,0)*:p_exchange_rate IC_TOT_OSP_COST
, 1 IC_COUNT
, msi.inventory_item_id IC_ITEM_ID_P
, decode(:P_SORT_OPTION,2
        , fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) IC_ORDER 
, fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_cat_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') IC_CAT_PSEG 
, fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') IC_ITEM_PSEG
, fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') IC_CATEGORY_SEGMENT
, fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') IC_ITEM_SEGMENT
, BOM_CSTRPICR_XMLP_PKG.ic_total_cost_rformula(NVL ( cpic.item_cost , 0 ) * NVL ( cpic.total_layer_quantity , 0 ) * :p_exchange_rate) IC_TOTAL_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_res_cost_rformula(ROUND ( ( NVL ( cpic.resource_cost , 0 ) * :p_exchange_rate ) , :p_ext_prec )) IC_RES_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_osp_cost_rformula(ROUND ( ( NVL ( cpic.outside_processing_cost , 0 ) * :p_exchange_rate ) , :p_ext_prec )) IC_OSP_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_ovhd_cost_rformula(ROUND ( ( NVL ( cpic.overhead_cost , 0 ) * :p_exchange_rate ) , :p_ext_prec )) IC_OVHD_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_tot_osp_cost_rformula(NVL ( cpic.outside_processing_cost , 0 ) * NVL ( cpic.total_layer_quantity , 0 ) * :p_exchange_rate) IC_TOT_OSP_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_tot_ovhd_cost_rformula(NVL ( cpic.overhead_cost , 0 ) * NVL ( cpic.total_layer_quantity , 0 ) * :p_exchange_rate) IC_TOT_OVHD_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_matl_cost_rformula(ROUND ( ( NVL ( cpic.material_cost , 0 ) * :p_exchange_rate ) , :p_ext_prec )) IC_MATL_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_tot_res_cost_rformula(NVL ( cpic.resource_cost , 0 ) * NVL ( cpic.total_layer_quantity , 0 ) * :p_exchange_rate) IC_TOT_RES_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_mo_cost_rformula(ROUND ( ( NVL ( cpic.material_overhead_cost , 0 ) * :p_exchange_rate ) , :p_ext_prec )) IC_MO_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_tot_matl_cost_rformula(NVL ( cpic.material_cost , 0 ) * NVL ( cpic.total_layer_quantity , 0 ) * :p_exchange_rate) IC_TOT_MATL_COST_R
, BOM_CSTRPICR_XMLP_PKG.ic_tot_mo_cost_rformula(NVL ( cpic.material_overhead_cost , 0 ) * NVL ( cpic.total_layer_quantity , 0 ) * :p_exchange_rate) IC_TOT_MO_COST_R
FROM
mtl_item_categories mic
, mtl_categories mc
, mtl_system_items msi
,cst_pac_item_costs cpic
WHERE
cpic.pac_period_id = :p_period_id
AND cpic.cost_group_id = :p_cost_group_id
AND cpic.inventory_item_id = mic.inventory_item_id
AND mic.organization_id = :p_item_master_org_id
AND mic.category_set_id = :p_category_set
AND mc.category_id = mic.category_id
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.organization_id = mic.organization_id
AND &p_item_where
AND &p_cat_where
order by 21,25
Parameter Name SQL text Validation
Legal Entity 
 
 LOV Oracle
Cost Type 
 
 LOV Oracle
Period 
 
 LOV Oracle
Cost Group 
 
 LOV Oracle
Sort Option 
 
 LOV Oracle
Report Option 
 
 LOV Oracle
Item From 
 
 Char
Item To 
 
 Char
Category Set 
 
 LOV Oracle
Category From 
 
 Char
Category To 
 
 Char
Category Organization 
 
 LOV Oracle
Currency 
 
 LOV Oracle
Exchange Rate 
 
 LOV Oracle
Download
Related Blitz Reports
CST Detailed Item Cost XXT OF: PO Headers and Lines CAC Inventory Pending Cost Adjustment PO Receiving Transactions Pipeline CAC Calculate ICP PII Item Costs CAC Intercompany SO Price List vs. Item Cost Comparison CST Elemental Cost CAC ICP PII Inventory and Intransit Value (Period-End) CAC Inventory and Intransit Value (Period-End)
Blitz Report™

Blitz Report™ provides multiple benefits: