CST Periodic Inventory Value

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Periodic Inventory Value Report (XML)
Short Name: CSTRPICR_XML
DB package: BOM_CSTRPICR_XMLP_PKG
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
View Cost Privilege
 
Number
Quantity Precision
 
Number
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Category Structure
 
Number
CST_SRS_PAC_SOB
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Category Organization
 
LOV Oracle
Category To
 
Category From
 
Category Set
 
LOV Oracle
Item To
 
Item From
 
Report Option
 
LOV Oracle
Sort Option
 
LOV Oracle
Cost Group
 
LOV Oracle
Period
 
LOV Oracle
Cost Type
 
LOV Oracle
Legal Entity
 
LOV Oracle