CST Supply Chain Indented Bills of Material Cost

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Supply Chain Indented Bills of Material Cost Report (XML)
Short Name: CSTRSCCRI_XML
DB package: BOM_CSTRSCCR_XMLP_PKG
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
Parameter Name SQL text Validation
Quantity Precision
 
Number
Called By
 
Rollup Report Option
 
Number
Category structure
 
Number
CST_SRS_BILL_LEVEL
 
Number
Report Type
 
Number
Range
 
Number
Rollup Option
 
Number
Conversion Type
 
LOV Oracle
CST_SRS_BOM_OR_ENG
 
Default Org ID
 
Number
Rollup lock flag
 
Number
Category To
 
Category From
 
Category set
 
LOV Oracle
Item To
 
Item From
 
Engineering Bills
 
LOV Oracle
Alternate Bill
 
LOV Oracle
Include Unimplemented ECOs
 
LOV Oracle
Effective Date
 
DateTime
Past Rollup
 
LOV Oracle
Report Number of Levels
 
LOV Oracle
Routing Detail
 
LOV Oracle
Material Overhead Detail
 
LOV Oracle
Material Detail
 
LOV Oracle
Assignment Set
 
LOV Oracle
Organization
 
LOV Oracle
Cost Type
 
LOV Oracle
Description