Search This Blog

Friday, March 22, 2013

Bom Explosion Query

Below query can be handy to explode and Query BOM Details

SELECT DISTINCT LPAD (' ', LEVEL * 2) || LEVEL order_level
              , msib.segment1 assembly_item
              , msib.description assembly_description
              , msib.inventory_item_status_code assembly_item_status
              , SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH
              , msib2.segment1 AS component_item
              , msib2.description component_item_description
              , msib2.inventory_item_status_code component_item_status
              , bic.item_num
              , bic.operation_seq_num
              , bic.component_quantity
FROM            bom.bom_components_b bic
              , bom.bom_structures_b bom
              , inv.mtl_system_items_b msib
              , inv.mtl_system_items_b msib2
              , mtl_parameters mp
WHERE           1 = 1
AND             bic.bill_sequence_id = bom.bill_sequence_id
AND             SYSDATE BETWEEN bic.effectivity_date AND Nvl(bic.disable_date, SYSDATE)
AND             bom.assembly_item_id = msib.inventory_item_id
AND             bom.organization_id = msib.organization_id
AND             bic.component_item_id = msib2.inventory_item_id
AND             bom.organization_id = msib2.organization_id
AND             mp.organization_id = msib.organization_id
AND             mp.organization_code = :p_org_code            /* organization here */
AND             bom.alternate_bom_designator IS NULL
START WITH      msib.segment1 = :p_item_number                /*  component item to be used here */
CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id
ORDER BY        PATH

14 Comments:

Anonymous said...

Hi,
I am looking for APIs for adding new lines to existing receipt.
Thank You!

Sharath said...

Hi Suresh,
This is Sharath, hope you remembered me(Carlsbad).

I'm searching for BOM API. Can you please help me in this.
Routing Setup
Step 1
Desc - Creating Dummy Dept
Step 2
Desc – Creating routing for an item (Ex. Item - ATT.100003832) and associate sequence with the dummy Dept.
Step 3
Now user should be able to select same child item with different qty for the single BOM.
seq item qty
10 ATT.100003832 10
20 ATT.100003832 20

Will this be possible. If so what routing api do we need to use to load bom's with multiple same child item's with different qty.

Anonymous said...

Hi Suresh,

This query is really helpful to get the Parent Child BOM relationship. One question I have is say if a subassembly is having "End of life(EOL)", and I dont want to explode that particular assembly as it is EOL then in the query i added msib.inventory_item_status_code<>'E.O.L.'. This is not giving me that particular assembly line, but it still exploding everything underneath it. Is there anyway I can ignore EOL items.

Ex: LAPTOP123 is made of 2 assemblies and components

1.800-123(EOL)
1.1 800-123-01
1.1.1 600-123
1.2 800-123-02
2.800-456
2.1 800-456-01
2.2 800-456-02

In the above example I dont want to explode 800-123 into parent-child as it is EOL. Can you please suggest me what changes I have to make for this query in order to achieve that.

Thank you for your time

-Shilpa

Himanshu Gupta said...

Hi Shilpa,

Did you got the solution. I am also facing the same kind of trouble in BOM explosion. if I put the condition "where disable_date is null". it is not giving any particular assembly line but still it is exploding everything underneath it.

Please comment if you got the solution.

Anonymous said...

Hi Himanshu,
Even I used disable_date is null caluse, but it still explodes the "End of life" items. I had to work on other things so kept this one in pending for now, will update if I find a solution. Please do the same

Anonymous said...

Hi,

This query is taking lot of time to get BOM DETAILS?Is their any missing link which is missed by me?Please let us know.

Thanks,
Pal

Anonymous said...

Try this if you want to eliminate the Disable one's from BOM explosion:

Note: I have hard coded the values, you can always change it suitable to your requirement.

select distinct level,
(select segment1 from mtl_system_items_b where inventory_item_id = bom.assembly_item_id and organization_id = bom.organization_id) Parent,
(select segment1 from mtl_system_items_b where inventory_item_id = bic.component_item_id and organization_id = bom.organization_id) Child,
SYS_CONNECT_BY_PATH ((select segment1 from mtl_system_items_b where inventory_item_id = bic.component_item_id and organization_id = bom.organization_id), '/') PATH,
bic.creation_date,
bic.effectivity_date,
bic.disable_date
from
(
select assembly_item_id, organization_id, bill_sequence_id
from bom_bill_of_materials
where organization_id = 1390
and ALTERNATE_BOM_DESIGNATOR is null
) bom,
(
select component_item_id, creation_date, effectivity_date, disable_date, bill_sequence_id
from bom_inventory_components
where trunc(sysdate) between trunc(effectivity_date) and trunc(nvl(disable_date, sysdate))
) bic
where bom.bill_sequence_id = bic.bill_sequence_id
and bom.organization_id = 1390
connect by prior bic.component_item_id = bom.assembly_item_id
start with bom.assembly_item_id = 1019601
order by path

Thanks
Kiran S

Anonymous said...

Useful query....

Bom tables can be found @ http://oracleappsquery.com/?q=oracle-apps-bills-of-material-tables-views-bom

Anonymous said...

http://www.oracleappsquery.com/?q=oracle-apps-r12-bom-bills-of-material-open-interfaces


Bom Interfaces ...useful link

VMR Naidu Dudala said...

Hi Tech gurus,
Could you please let me know the query to fetch bom details which is having 1)abc assembly has XYZ component and XYZ assembly has abc component vice-versa. Which is urgent for me .pls help.

VMR Naidu Dudala said...

Hi Tech gurus,
Done my self, ignore my request.

Thank you.

Amar Alam said...

Hi Suresh,

I need to update component quantity in BOM_INVENTORY_COMPONENTS, is there any API or method available to update only specific table.

Can we update table directly in our custom procedure..?

Parameters are ASSEMBLY_ITEM_ID and ORGANIZATION_ID

Thanks
Amarnath

Anonymous said...

Need to pull indented BOMs, up - 4 levels (no all) with cost (Cost type=Frozen) for all ATO models in US1, where ATO Model <>05 .
Structure - Future and Current

Display -
Levels of the BOM (1-4), component type, component description, component item status, Optional flag

uttam said...

Please tell me why do we use distinct in the select.

Copyright (c) All rights reserved. Presented by Suresh Vaishya