CST Supply Chain Consolidated Bills of Material Cost

select
  abs(CSBS.rollup_id)                   comp_rollup_id,
  CSBS.top_inventory_item_id       comp_top_inventory_item_id,
  CSBS.top_organization_id         comp_top_organization_id,
  CSBS.sort_order                  comp_sort_order,
  CIC.cost_type_id                  comp_cost_type,
  decode(:lp_report_type_type, 1, lpad('.',CSBS.bom_level-1,'.')||to_char(CSBS.bom_level-1), null) comp_level_code,
  BIC.operation_seq_num            comp_op_seq_num,
  MSI.concatenated_segments        comp_name,
  MSI.description                  comp_desc,
  MP.organization_code            comp_org,
  CSBS.component_revision                     revision,
  LU1.meaning                      make_buy,
  LU2.meaning                      include_in_rollup,
  LU3.meaning                      based_on_rollup,
  LU4.meaning                      inventory_asset,
  LU5.meaning                      phantom,
  BIC.component_yield_factor       component_yield_factor,
  BIC.planning_factor                     component_planning_factor,
  MSI.primary_uom_code             component_uom,
  decode(:lp_report_type_type, 1, CSBS.component_quantity, null)          component_quantity,
  CIC.shrinkage_rate               shrinkage_rate,
  CSBS.extended_quantity           extended_quantity,
  nvl( CIC.item_cost, 0 )                    item_cost,
  decode( CSBS.extend_cost_flag, 2, 0, 
    (
      decode( :P_MATERIAL_DTL_FLAG, 1, 0, 
        decode( CSBS.bom_level, :LP_REPORT_LEVEL, nvl( CIC.pl_material, 0 ), 0 ) +
         decode( 
            decode( :p_phantom_mat, 1, 0, 1 ) * decode(CSBS.bom_level, 1, 0, 1) *
            decode( CSBS.phantom_flag, 1, 1, 0 ),
            1, 0, 
            nvl( CIC.tl_material, 0 ) 
        )
      ) +
      decode( :P_MATERIAL_OVERHEAD_DTL_FLAG, 1, 0, 
        decode( CSBS.bom_level, :LP_REPORT_LEVEL, nvl( CIC.pl_material_overhead, 0 ), 0 ) +
         decode( 
            decode( :p_phantom_mat, 1, 0, 1 ) * decode(CSBS.bom_level, 1, 0, 1) *
            decode( CSBS.phantom_flag, 1, 1, 0 ),
            1, 0, 
            nvl( CIC.tl_material_overhead, 0 ) 
        )
      ) +
      decode( 
         decode( nvl( BP.use_phantom_routings, 2 ), 1, 1, 0 ) *
         decode( CSBS.phantom_flag, 1, 1, 0 ),
        1, 0,
        decode( :P_ROUTING_DTL_FLAG, 1, 0, 
          decode( CSBS.bom_level, 
            :LP_REPORT_LEVEL, 
              nvl( CIC.pl_resource, 0 ) +
              nvl( CIC.pl_outside_processing, 0 ) +
              nvl( CIC.pl_overhead, 0 ), 
            0 
          ) +
         decode( 
            decode( nvl( BP.use_phantom_routings, 2 ), 1, 0, 1 ) *
            decode( CSBS.phantom_flag, 1, 1, 0 ),
            1, 0, 
             nvl( CIC.tl_resource, 0 ) +
             nvl( CIC.tl_outside_processing, 0 ) +
             nvl( CIC.tl_overhead, 0 ) 
           ) 
         )
      )
    )
  ) * CSBS.extended_quantity         comp_ext_cost,
  FC.currency_code                   comp_currency_code,
  FC.extended_precision              comp_ext_precision ,
  LU6.meaning                            comp_basis_type,
  bom_common_xmlp_pkg.get_precision(FC.extended_precision) COMP_PRECISION
from
  cst_sc_bom_structures    CSBS,
  bom_parameters           BP,
  bom_inventory_components BIC,
  mtl_system_items_kfv     MSI,
  hr_organization_information HOI,
  gl_sets_of_books         SOB,
  fnd_currencies           FC,
  cst_item_costs           CIC,
  mfg_lookups              LU1,
  mfg_lookups              LU2,
  mfg_lookups              LU3,
  mfg_lookups              LU4,
  mfg_lookups              LU5,
  mtl_parameters         MP,
  mfg_lookups              LU6 
where
  CSBS.rollup_id                = decode(:lp_report_type_type, 1, :p_rollup_id, -1*:p_rollup_id)    and
  CSBS.bom_level                <= :LP_REPORT_LEVEL               and
  BP.organization_id (+)           = CSBS.component_organization_id and 
  BIC.component_sequence_id(+)  = CSBS.component_sequence_id     and
  MSI.inventory_item_id         = CSBS.component_item_id         and
  MSI.organization_id           = CSBS.component_organization_id and
  HOI.organization_id = CSBS.component_organization_id and
  HOI.org_information_context = 'Accounting Information' and
  SOB.set_of_books_id           = to_number(HOI.org_information1)            and
  FC.currency_code              = SOB.currency_code              and
  CIC.inventory_item_id (+)        = CSBS.component_item_id         and
  CIC.organization_id (+)          = CSBS.component_organization_id and
  ( CIC.cost_type_id = :LP_COST_TYPE_ID or
     ( CIC.cost_type_id = :P_DEFAULT_COST_TYPE_ID 
        and not exists (
        select 'x' from CST_ITEM_COSTS CIC1
        where CIC1.inventory_item_id = CSBS.component_item_id and
        CIC1.organization_id = CSBS.component_organization_id and
        CIC1.cost_type_id = :LP_COST_TYPE_ID) 
     ) or 
     ( CIC.cost_type_id = MP.primary_cost_method
        and not exists (
        select 'x' from CST_ITEM_COSTS CIC2
        where CIC2.inventory_item_id = CSBS.component_item_id and
        CIC2.organization_id = CSBS.component_organization_id and
        CIC2.cost_type_id in (:LP_COST_TYPE_ID, :P_DEFAULT_COST_TYPE_ID))
     ) or
     ( nvl(CIC.cost_type_id, -1) = -1
       and not exists (
       select 'x' from CST_ITEM_COSTS CIC3
       where CIC3.inventory_item_id = CSBS.component_item_id and
       CIC3.organization_id = CSBS.component_organization_id and
       CIC3.cost_type_id in (:P_COST_TYPE_ID, :P_DEFAULT_COST_TYPE_ID, MP.primary_cost_method))
     )
  ) and 
  MP.organization_id = CSBS.component_organization_id    and 
  LU1.lookup_type (+) = 'MTL_PLANNING_MAKE_BUY'                 and
  LU1.lookup_code (+) = MSI.planning_make_buy_code              and
  LU2.lookup_type (+) = 'SYS_YES_NO'                            and
  LU2.lookup_code (+) = CSBS.include_in_cost_rollup             and
  LU3.lookup_type = 'SYS_YES_NO'                                and
  LU3.lookup_code = nvl( CIC.based_on_rollup_flag, 2 )          and
  LU4.lookup_type = 'SYS_YES_NO'                                and
  LU4.lookup_code = nvl( CIC.inventory_asset_flag, 2 )                    and
  LU5.lookup_type = 'SYS_YES_NO'                                and
  LU5.lookup_code = CSBS.phantom_flag     	and
  LU6.lookup_type = 'CST_BASIS'    	and
  LU6.lookup_code = NVL(BIC.basis_type, 1) 
 and abs ( CSBS.rollup_id )=:assm_rollup_id 
 and CSBS.top_inventory_item_id=:assm_top_inventory_item_id 
 and CSBS.top_organization_id=:assm_top_organization_id
order by
  CSBS.rollup_id,
  decode( :lp_report_type_type, 1, CSBS.sort_order, 0 ),
  decode( CSBS.assembly_item_id, -1, 1, 2 ), 
  MSI.concatenated_segments,
  MP.organization_code