Introduction:
This Post illustrates the steps required to Fetch Item with BOM Details.
Script to Fetch the Item with BOM Details
SELECT msi.segment1 item, msi.description item_description,
msi.organization_id, msi.primary_unit_of_measure primary_uom,
msi.item_type, msi.inventory_item_status_code item_status,
micv1.category_concat_segs product_category, micv1.category_set_name,
micv1.category_id product_category_id,
gcc.concatenated_segments cogs_account,
gcc1.concatenated_segments sales_aacount, msi.weight_uom_code,
msi.unit_weight, msi.volume_uom_code, msi.unit_volume,
msi.dimension_uom_code, msi.unit_length, msi.unit_width,
msi.unit_height, msi.indivisible_flag, msi.planner_code,
DECODE (msi.planning_make_buy_code, ‘1’, ‘Make’, ‘Buy’) AS make_buy,
msi.preprocessing_lead_time lead_preprocessing,
msi.full_lead_time lead_processing,
msi.postprocessing_lead_time lead_postprocessing,
msi.cumulative_total_lead_time cumulative_total,
msi.lead_time_lot_size, bic.item_num item_sequence,
bic.operation_seq_num operation_sequence,
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = bic.component_item_id
AND organization_id = bom.organization_id) component,
bic.description comp_description,
bic.primary_uom_code component_primary_uom_code,
bic.component_quantity, bic.effectivity_date effectivity_date_from,
bic.disable_date effectivity_date_to,
bic.attribute1 unit_in_assortment, msi.last_update_date,
muc.conversion_rate, msi.inventory_item_id, bic.component_item_id,
msi.organization_id ORGANIZATION
FROM mtl_item_categories_v micv1,
mtl_system_items_b msi,
gl_code_combinations_kfv gcc,
gl_code_combinations_kfv gcc1,
bom_bill_of_materials bom,
bom_inventory_components_v bic,
mtl_uom_conversions muc
WHERE msi.organization_id = micv1.organization_id(+)
AND msi.inventory_item_id = micv1.inventory_item_id(+)
AND msi.inventory_item_id = muc.inventory_item_id(+)
AND micv1.category_set_name(+) = ‘Inventory’
AND gcc.code_combination_id(+) = msi.cost_of_sales_account
AND gcc1.code_combination_id(+) = msi.sales_account
AND bom.assembly_item_id(+) = msi.inventory_item_id
AND bom.organization_id(+) = msi.organization_id
AND bom.bill_sequence_id = bic.bill_sequence_id(+)
AND bic.component_item_id IS NOT NULL
Do drop a note by writing us at venkatesh.b@staging.doyensys.com or use the comment section below to ask your questions
