CST Temporary Supply Chain Cost Rollup - Print

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(:p_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(:p_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(:p_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 = :P_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 = :P_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 (:P_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( :p_report_type_type, 1, CSBS.sort_order, 0 ),
  decode( CSBS.assembly_item_id, -1, 1, 2 ), 
  MSI.concatenated_segments,
  MP.organization_code

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(:p_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(:p_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(:p_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 = :P_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 = :P_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 (:P_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( :p_report_type_type, 1, CSBS.sort_order, 0 ), decode( CSBS.assembly_item_id, -1, 1, 2 ), MSI.concatenated_segments, MP.organization_code