Joins to BOM of each Revision
I want to select the BOMs (including the revision and lifecycle of the BOM-Items) for each revision of an item. I believe it has something to do with the three columns “CHANGE_IN”, “CHANGE_OUT” and “PRIOR_BOM” in the BOM-table. Maybe the latest revision is the one with “0” in column “CHANGE_OUT”. But it is still hard to find the join to the other revisions. I saw all the questions regarding similar topics. But I was not able to answer my question. Can someone give me a hint what the correct join is?
Edit:
I looked a little further. I think the revisions of the items in the BOM can be found based on the release date of the parent item.
Also the last BOM seems to be no problem (Change_Out=0).
What remains is the question, how to select the Items of older BOMs. Unfortunately this is my main topic.
Any help is welcome.
After I tested a long time, I think the following is close to the required result.
Only the highlighted part does not always bring the correct data. Can anyone help me with the correct join to the revision of the BOM items?
SELECT
ITEM.ITEM_NUMBER ITEM_PARENT
,REV_PARENT.CHANGE CHANGE_PARENT
,REV_PARENT.REV_NUMBER REV_PARENT
,BOM.ITEM_NUMBER ITEM_CHILD
,ITEM_CHILD.DESCRIPTION DESCRIPTION_CHILD
,BOM.QUANTITY QUANTITY_CHILD
,MAX(REV_CHILD.REV_NUMBER) REV_CHILD
FROM AGILE.REV REV_PARENT
JOIN AGILE.ITEM ITEM ON ITEM.ID=REV_PARENT .ITEM
JOIN AGILE.BOM BOM ON BOM.ITEM=REV_PARENT.ITEM
AND BOM.CHANGE_IN<=REV_PARENT.CHANGE
AND ((BOM.CHANGE_OUT=0) OR (BOM.CHANGE_OUT>REV_PARENT.CHANGE))
JOIN AGILE.REV REV_CHILD ON REV_CHILD.ITEM = BOM.COMPONENT
AND REV_CHILD.LAST_UPD <= REV_PARENT.LAST_UPD
JOIN AGILE.ITEM ITEM_CHILD ON ITEM_CHILD.ID = BOM.COMPONENT
GROUP BY
ITEM.ITEM_NUMBER
,REV_PARENT.CHANGE
,REV_PARENT.REV_NUMBER
,BOM.ITEM_NUMBER
,ITEM_CHILD.DESCRIPTION
,BOM.QUANTITY
ORDER BY REV_PARENT.CHANGE, ITEM.ITEM_NUMBER
Hi,
I just developed below query which works for me.Please try below query.
SELECT DISTINCT c.change_number change_number
, i.item_number Parent_Item
,(SELECT n.description
FROM agile.nodetable@ebstoagile n
WHERE 1 = 1
AND n.id = r.release_type)parent_lifecycle
, r.rev_number Parent_rev
, r.old_revnumber Parent_old_rev
, s.name org
, b.item_number Child_Item
,(SELECT MAX(rev_number)
FROM agile.rev@ebstoagile rc
WHERE 1 = 1
AND rc.site = s.id
AND rc.item = b.component)child_rev
,(SELECT MAX(rc.old_revnumber)
FROM agile.rev@ebstoagile rc
WHERE 1 = 1
AND rc.site = s.id
AND rc.item = b.component)Child_old_rev
, b.quantity
, b.find_number
,(SELECT n.description
FROM agile.rev@ebstoagile r
, agile.nodetable@ebstoagile n
WHERE 1 = 1
AND r.latest_flag = ‘1’
AND r.item = b.component
AND r.site = s.id
AND n.id = r.release_type)Child_lifecycle
FROM agile.bom@ebstoagile b
, agile.item_sites@ebstoagile si
, agile.item_p2@ebstoagile i
, agile.sites@ebstoagile s
, inv.mtl_parameters mp
, agile.rev@ebstoagile r
, agile.change@ebstoagile c
, agile.item_p2@ebstoagile ip2
WHERE 1 = 1
AND ip2.item_number = b.item_number
AND c.id = r.change
AND r.site = s.id
AND((b.change_out = c.id
AND b.change_in <> c.id
AND substr(b.flags, 5, 1) = ‘0’)
OR substr(b.flags, 5, 1) = ‘1’
OR(b.change_in = c.id
AND b.change_out <> c.id
AND c.statustype IN(3, 4)
AND substr(b.flags, 5, 1) = ‘1’)
OR(b.change_in = c.id
AND b.change_out <> c.id
AND c.statustype != 3))
AND r.item = b.item
AND b.item(+) = si.item
AND si.site = s.id
AND s.name = mp.organization_code
AND si.item = i.id
AND c.change_number = :change_number
AND i.item_number = nvl(:item_number, i.item_number)
AND s.name = nvl(:site_name, s.name)
ORDER BY i.item_number
, s.name
, to_number(nvl(find_number, 0))
Thanks,
Vaibhav
