CST Temporary Supply Chain Cost Rollup - Print
Description
Categories: BI Publisher
Columns: Comp Rollup Id, Comp Top Inventory Item Id, Comp Top Organization Id, Comp Sort Order, Comp Cost Type, Comp Level Code, Comp Op Seq Num, Comp Name, Comp Desc, Comp Org ...
Columns: Comp Rollup Id, Comp Top Inventory Item Id, Comp Top Organization Id, Comp Sort Order, Comp Cost Type, Comp Level Code, Comp Op Seq Num, Comp Name, Comp Desc, Comp Org ...
Application: Bills of Material
Source: Temporary Supply Chain Cost Rollup - Print Report (XML)
Short Name: CSTRSCCRT_XML
DB package: BOM_CSTRSCCR_XMLP_PKG
Source: Temporary Supply Chain Cost Rollup - Print Report (XML)
Short Name: CSTRSCCRT_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 | |
---|---|---|---|
Description | |||
Cost Type | LOV Oracle | ||
Organization | LOV Oracle | ||
Assignment Set | LOV Oracle | ||
Buy Cost Type | LOV Oracle | ||
Preserve Buy Cost Details | LOV Oracle | ||
Conversion Type | LOV Oracle | ||
Rollup Option | LOV Oracle | ||
Range | LOV Oracle | ||
Report Type | LOV Oracle | ||
Material Detail | LOV Oracle | ||
Material Overhead Detail | LOV Oracle | ||
Routing Detail | LOV Oracle | ||
Report Number of Levels | LOV Oracle | ||
Effective Date | DateTime | ||
Include Unimplemented ECOs | LOV Oracle | ||
Alternate Bill | LOV Oracle | ||
Alternate Routing | LOV Oracle | ||
Engineering Bills | LOV Oracle | ||
Lot Size Option | LOV Oracle | ||
Lot Size Setting | Number | ||
Specific Item | LOV Oracle | ||
Category set | LOV Oracle | ||
Specific Category | |||
Item From | |||
Item To | |||
Rollup lock flag | Number | ||
Default Org ID | Number | ||
CST_SRS_DEFAULT_COST_TYPE_ID | Number | ||
CST_SRS_BILL_LEVEL | Number | ||
CST_SRS_COPY_ITEM_DUMMY | Number | ||
CST_SRS_COPY_CAT_DUMMY | Number | ||
Category validate flag | |||
Category structure | Number | ||
Rollup Report Option | Number | ||
Called By | |||
Quantity Precision | Number |