CST Elemental Cost

select
                   null item_data,
                   null cat_data,
                   mc.description cat_desc,
                   msi.description item_desc,
                   msi.primary_uom_code,
                   round(cic.material_cost * :P_EXCHANGE_RATE , :ext_precision_saved)  material_cost,
                   round(cic.material_overhead_cost * :P_EXCHANGE_RATE, :ext_precision_saved) mat_ovhd,
                   round(cic.resource_cost * :P_EXCHANGE_RATE, :ext_precision_saved) resrc_cost,
                   round(cic.outside_processing_cost * :P_EXCHANGE_RATE, :ext_precision_saved) out_proc,
                   round(cic.overhead_cost * :P_EXCHANGE_RATE, :ext_precision_saved) ovhd_cost,
                   round(cic.item_cost* :P_EXCHANGE_RATE , :ext_precision_saved) unit_cost,
                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_flexfield_cat', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD_CAT,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD_ITEM,
                --BOM_CSTRELCR_XMLP_PKG.order_func(:C_FLEXPAD_ITEM, :C_FLEXPAD_CAT) C_ORDER,
                BOM_CSTRELCR_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')) C_ORDER
            from mtl_system_items_vl msi,
                 mtl_parameters mp,
                 mtl_item_categories mic,
                 mtl_categories mc,
                 cst_item_costs cic
            where msi.organization_id = :org_id
            and   mp.organization_id = :org_id
            and   cic.cost_type_id = :cost_type_id
            and   cic.inventory_item_id = msi.inventory_item_id
            and   cic.organization_id = mp.cost_organization_id
            and   mic.inventory_item_id = cic.inventory_item_id
            and   mic.organization_id = :org_id
            and   mic.category_id  = mc.category_id
            and   mic.category_set_id = :P_CATEGORY_SET
            and   &P_WHERE_ITEM
            and   &P_WHERE_CAT
            and   &WHERE_ZERO
            group by
                  &P_FLEXDATA_ITEM, &P_FLEXDATA_CAT,
                  mic.category_id, msi.description,
                  msi.primary_uom_code, cic.material_cost,
                  cic.material_overhead_cost, cic.resource_cost,
                  cic.outside_processing_cost, cic.overhead_cost,
                  cic.item_cost, mc.description,
                  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'),
				  fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield_cat', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
                  fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')
            order by C_ORDER, cat_desc, C_FLEXPAD_ITEM