CST Layer Inventory Value
Description
- Categories
- BI Publisher
- Application
- Bills of Material
- Source
- Layer Inventory Value Report (XML)
- Short Name
- CSTRLIVR_XML
- DB package
- BOM_CSTRLIVR_XMLP_PKG
Imported from BI Publisher
Layer Inventory Value Report
Layer Inventory Value Report
SQL 54 lines
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 |
Parameters 13
| Parameter | Type | List of values SQL |
|---|---|---|
| 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 |