CST Item Cost Reports

Description
Categories: Enginatics, Financials, Manufacturing
Based on Oracle standard's item cost reports CSTRFICRG_XML
select distinct
gl.name ledger,
mp.organization_code,
msiv.concatenated_segments item,
msiv.description item_description,
mck.concatenated_segments category,
misv.inventory_item_status_code_tl item_status,
muot.unit_of_measure_tl primary_uom,
&columns
round(sum(decode(cdcv.level_type,1,cdcv.item_cost,0)) over (partition by cdcv.organization_id, cdcv.inventory_item_id, &partition_by), fc.extended_precision) this_level,
round(sum(decode(cdcv.level_type,2,cdcv.item_cost,0)) over (partition by cdcv.organization_id, cdcv.inventory_item_id, &partition_by), fc.extended_precision) prev_level,
round(sum(cdcv.item_cost) over (partition by cdcv.organization_id, cdcv.inventory_item_id, &partition_by), fc.extended_precision) item_cost,
100*sum(cdcv.item_cost) over (partition by cdcv.organization_id, cdcv.inventory_item_id, &partition_by)/xxen_util.zero_to_null(sum(cdcv.item_cost) over (partition by cdcv.organization_id, cdcv.inventory_item_id)) percentage,
round(sum(cdcv.item_cost) over (partition by cdcv.organization_id, cdcv.inventory_item_id), fc.extended_precision) item_cost_total
from
gl_ledgers gl,
fnd_currencies fc,
org_organization_definitions ood,
mtl_parameters mp,
mtl_system_items_vl msiv,
mtl_units_of_measure_tl muot,
mtl_item_status_vl misv,
cst_detail_cost_view cdcv,
cst_cost_types cct,
mtl_category_sets_v mcsv,
mtl_item_categories mic,
mtl_categories_kfv mck
where
1=1 and
mcsv.category_set_name=:category_set_name and
gl.currency_code=fc.currency_code and
fc.enabled_flag='Y' and
gl.ledger_id=ood.set_of_books_id and
ood.organization_id=mp.organization_id and
ood.organization_id=msiv.organization_id and
msiv.primary_uom_code=muot.uom_code(+) and
muot.language(+)=userenv('lang') and
msiv.inventory_item_status_code=misv.inventory_item_status_code(+) and
msiv.inventory_item_id=cdcv.inventory_item_id and
mp.cost_organization_id=cdcv.organization_id and
cdcv.cost_type_id=cct.cost_type_id and
mcsv.category_set_id=mic.category_set_id and
msiv.inventory_item_id=mic.inventory_item_id and
msiv.organization_id=mic.organization_id and 
mic.category_id=mck.category_id
order by
ledger,
organization_code,
&order_by
Parameter Name SQL text Validation
Item To
msiv.concatenated_segments<=:item_to
LOV
Item From
msiv.concatenated_segments>=:item_from
LOV
Organization Code
mp.organization_code=:organization_code
LOV
Chart of Accounts
gl.chart_of_accounts_id in (select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts)
LOV
Ledger
gl.name=:ledger_name
LOV
Category To
mck.concatenated_segments<=:category_to
LOV
Category From
mck.concatenated_segments>=:category_from
LOV
Sort Option
category,
item
Report Name
select crt.column1||', '||crt.column2 from cst_report_types crt where crt.unique_id=:unique_id
Sort Option
item
LOV Oracle
Category Set
 
LOV
Cost Type
cct.cost_type=:cost_type
LOV
Report Name
select crt.column1||' "'||crt.column1_heading||'",' from cst_report_types crt where crt.unique_id=:unique_id union all
select crt.column2||' "'||crt.column2_heading||'",' from cst_report_types crt where crt.unique_id=:unique_id
LOV Oracle
Ask a question