CST Layer Inventory Value

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Layer Inventory Value Report
Application: Bills of Material
Source: Layer Inventory Value Report (XML)
Short Name: CSTRLIVR_XML
DB package: BOM_CSTRLIVR_XMLP_PKG
Run CST Layer Inventory Value and other Oracle EBS reports with Blitz Report™ on our demo environment
select
CCG.cost_group cost_group,
null category,
null item,
MSI.description description,
MSI.primary_uom_code uom,
CIL.inv_layer_id inv_layer_id,
CIL.transaction_source transaction_source_id,
MTST.transaction_source_type_name transaction_source_name,
CIL.layer_quantity layer_quantity,
CIL.layer_cost*:P_EXCHANGE_RATE_CHAR layer_cost,
round(CIL.layer_cost*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, :p_ext_prec) extended_value,
round(sum(decode(CILCD.cost_element_id, 1, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) material_cost,
round(sum(decode(CILCD.cost_element_id, 2, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) material_overhead_cost,
round(sum(decode(CILCD.cost_element_id, 3, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) resource_cost,
round(sum(decode(CILCD.cost_element_id, 4, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) outside_processing_cost,
round(sum(decode(CILCD.cost_element_id, 5, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) overhead_cost,
round(sum(nvl(CILCD.layer_cost,0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR),:p_qty_precision) total_cost, 
	decode(:P_SORT_OPTION,1,fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),null) CF_ORDER, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY_SEGMENT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_SEGMENT
from
cst_cost_groups CCG,
mtl_system_items_vl MSI,
mtl_item_categories MIC,
mtl_categories MC,
cst_quantity_layers CQL,
cst_inv_layers CIL,
mtl_txn_source_types MTST,
cst_inv_layer_cost_details CILCD
where
CIL.transaction_source_type_id = MTST.transaction_source_type_id AND
CCG.cost_group_id = CQL.cost_group_id AND
CCG.cost_group_id = decode (:P_COST_GROUP_OPTION_ID, 1, CCG.cost_group_id, :P_COST_GROUP_ID) AND
MSI.inventory_item_id(+) = CIL.inventory_item_id AND
MSI.inventory_asset_flag ='Y' AND
MSI.organization_id = :P_ORG_ID AND
MIC.organization_id = MSI.organization_id AND
MIC.inventory_item_id = MSI.inventory_item_id AND
MIC.category_set_id = :P_CATEGORY_SET AND
MC.category_id = MIC.category_id AND
CQL.organization_id = :P_ORG_ID AND
CIL.layer_id = CQL.layer_id AND
CILCD.layer_id = CIL.layer_id AND
CILCD.inv_layer_id = CIL.inv_layer_id AND
&P_ZERO_QTY_WHERE AND
&P_ZERO_COST_WHERE AND
&P_ITEM_WHERE AND
&P_CAT_WHERE
group by
CCG.cost_group, fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'), MSI.description, MSI.primary_uom_code, CIL.inv_layer_id, CIL.transaction_source, MTST.transaction_source_type_name, CIL.layer_quantity, CIL.layer_cost, CIL.layer_cost*CIL.layer_quantity
	ORDER BY 1 ASC,ITEM_SEGMENT ASC
Parameter Name SQL text Validation
Cost Group Option
 
LOV Oracle
Specific Cost Group
 
LOV Oracle
Report Option
 
LOV Oracle
Sort Option
 
LOV Oracle
Item From
 
Char
Item To
 
Char
Category Set
 
LOV Oracle
Category From
 
Char
Category To
 
Char
Currency
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Include Zero Cost Layers
 
LOV Oracle
Include Zero Quantity Layer
 
LOV Oracle
Blitz Report™