View Suresh Vaishya's profile
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:
Hi,
I am looking for APIs for adding new lines to existing receipt.
Thank You!
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.
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
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.
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
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
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
Useful query....
Bom tables can be found @ http://oracleappsquery.com/?q=oracle-apps-bills-of-material-tables-views-bom
http://www.oracleappsquery.com/?q=oracle-apps-r12-bom-bills-of-material-open-interfaces
Bom Interfaces ...useful link
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.
Hi Tech gurus,
Done my self, ignore my request.
Thank you.
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
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
Please tell me why do we use distinct in the select.
Post a Comment