Rakesh Supply Chain - Draft Works

select
:p_rollup_id rollup_id,
ood.organization_code org_code,
ood.organization_name org_name,
fc.currency_code currency_code,
msib.segment1 comp_name,
msib.description comp_desc,
msib.primary_uom_code comp_uom,
mck.concatenated_segments Category,
csbs.sort_order comp_sort_order,
lpad('.',csbs.bom_level-1,'.')||to_char(csbs.bom_level-1) comp_level_code,
bic.operation_seq_num comp_op_seq_num,
csbs.component_quantity,
decode(csbs.extended_quantity,1, csbs.component_quantity,csbs.extended_quantity) extended_quantity,
csbs.component_revision comp_last_rev,
xxen_util.meaning(csbs.include_in_cost_rollup,'SYS_YES_NO',700) include_in_rollup,
xxen_util.meaning(nvl(cic.based_on_rollup_flag,2),'SYS_YES_NO',700) based_on_rollup,
xxen_util.meaning(nvl(cic.inventory_asset_flag,2),'SYS_YES_NO',700) inventory_asset,
xxen_util.meaning(csbs.phantom_flag,'SYS_YES_NO',700) phantom,
xxen_util.meaning(nvl(bic.basis_type,1),'CST_BASIS',700) || bic.component_yield_factor comp_basis_type,
--xxen_util.meaning(msib.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy,
bic.component_yield_factor component_yield_factor,
bic.planning_factor component_planning_factor,
cic.shrinkage_rate shrinkage_rate,
cic.item_cost,
cce.cost_element,
br.resource_code sub_element,
bd.department_code department,
decode(cicd.cost_element_id,3,xxen_util.meaning(nvl(br.allow_costs_flag,1),'SYS_YES_NO',700),4,xxen_util.meaning(nvl(br.allow_costs_flag,1),'SYS_YES_NO',700),to_char(null)) costed,
xxen_util.meaning(cicd.basis_type,'CST_BASIS_SHORT',700) basis,
br.unit_of_measure,
nvl(cicd.usage_rate_or_amount,0) usage_rate_or_amount,
decode(cicd.cost_element_id,2,csbs.extended_quantity,5,csbs.extended_quantity,1)*cicd.basis_factor*cicd.net_yield_or_shrinkage_factor basis_factor,
decode(cicd.cost_element_id,2,1,5,1,csbs.extended_quantity)*cicd.usage_rate_or_amount ext_usage_rate_or_amount,
decode(cicd.cost_element_id,3,cicd.resource_rate,4,cicd.resource_rate,to_number(NULL)) res_unit_cost,
decode(decode(csbs.phantom_flag,1,1,0)*decode(csbs.assembly_organization_id,csbs.component_organization_id,1,0),1,decode(cicd.level_type,2,1,decode(cicd.cost_element_id,3,0,4,0,5,0,decode(:p_phantom_mat,1,1,0)))*
decode(csbs.extend_cost_flag,2,0,csbs.extended_quantity*decode(cicd.item_cost,cicd.yielded_cost,0,cicd.item_cost)), decode(csbs.extend_cost_flag,2,0,csbs.extended_quantity*cicd.item_cost)) res_extended_cost
from
org_organization_definitions ood,
fnd_currencies fc,
mtl_parameters mp,
mtl_system_items_b msib,
mtl_item_categories mic,
mtl_categories_kfv mck,
mtl_category_sets_tl mcst,
cst_sc_bom_structures csbs,
bom_inventory_components bic,
hr_organization_information hoi,
gl_sets_of_books gsob,
cst_item_costs cic,
cst_cost_elements cce,
cst_item_cost_details cicd,
bom_resources br,
bom_departments bd
where
1=1 and
ood.organization_code='M1' and -- Read this from parameter
ood.organization_id=csbs.top_organization_id and
mp.organization_id=csbs.component_organization_id and
bic.component_sequence_id(+)=csbs.component_sequence_id and
msib.organization_id=mic.organization_id and
msib.inventory_item_id=mic.inventory_item_id and
mic.category_id=mck.category_id and
mic.category_set_id=mcst.category_set_id and
mcst.language=userenv('lang') and
mcst.category_set_name='Inv.Items' and -- Read this from parameter
msib.inventory_item_id=csbs.component_item_id and
msib.organization_id=csbs.component_organization_id and
hoi.organization_id=csbs.component_organization_id and
hoi.org_information_context='Accounting Information' and
gsob.set_of_books_id=to_number(hoi.org_information1) and
fc.currency_code=gsob.currency_code and
cic.inventory_item_id(+)=csbs.component_item_id and
cic.organization_id(+)=csbs.component_organization_id and
cic.cost_type_id=1 and -- Read this from parameter
cic.cost_type_id=cicd.cost_type_id and
cicd.inventory_item_id=csbs.component_item_id and
cicd.organization_id=csbs.component_organization_id and
cicd.resource_id is not null and
cce.cost_element_id(+)=cicd.cost_element_id and 
br.resource_id(+)=cicd.resource_id and
bd.department_id(+)=cicd.department_id and
abs(csbs.rollup_id)=203393 and -- This should come from "Before Report trigger"
csbs.top_inventory_item_id=6068 -- Read from parameter
order by
csbs.rollup_id,
csbs.sort_order,
decode(csbs.assembly_item_id,-1,1,2),
msib.segment1,
mp.organization_code