How to fectch redline bom table of a affected item in a change using DB query

Hi,

I want to fetch the “Red line” BOM values of a affected item in a change using DB query, I am not ale to find the correct Data base table and query, can anyone help on this please.

Thanks,
Surya

Agile Talent Asked on October 23, 2018 in Agile PLM (v9),   Product Collaboration.
Add Comment
3 Answer(s)

Hi Can any one please help me with above request

Agile User Answered on March 2, 2020.
Add Comment

Not sure, where this query disappear.  Try this out:

 

SELECT ch.change_number AS change_number,
itp.item_number part_number,
bom.item_number AS component_part_number,
itc.description,
quantity,
CASE
WHEN prior_bom = 0
THEN ‘Added’
WHEN change_in <> change_out
AND prior_bom <> 0
THEN ‘Modified’
WHEN prior_bom <> 0
AND change_in = change_out
THEN ‘Delete’
END AS redline_action,
CASE
WHEN change_in <> change_out
AND prior_bom <> 0
THEN
(SELECT quantity FROM agile.bom WHERE id = bom.prior_bom
)
END prev_qty,
bom.change_in,
bom.change_out,
bom.prior_bom
FROM agile.bom bom
INNER JOIN agile.item itp
ON itp.id = bom.item
INNER JOIN agile.item itc
ON itc.id = bom.component
INNER JOIN agile.change ch
ON ch.id = bom.change_in
WHERE ch.change_number = ‘YourChangeNumber’;

 

Regards,

Arif

Agile Angel Answered on March 2, 2020.

Hi Arif,

Thanks for the query.

But how we can get bomitems which are not redlined in same query.

on March 3, 2020.

Not sure how useful it will be but you can try this:

Select distinct c.change_number,i.item_number Parent_Item,b.item_number Component_Item,
–b.change_in,
–b.change_out,
–b.prior_bom,
CASE
WHEN prior_bom = 0
THEN ‘Added’
WHEN change_in <> change_out
AND prior_bom <> 0
THEN ‘Modified’
WHEN prior_bom <> 0
AND change_in = change_out
THEN ‘Delete’
WHEN prior_bom is null and change_in is null
THEN ‘No Change’
END AS redline_action,
CASE
WHEN change_in <> change_out
AND prior_bom <> 0
THEN
(SELECT quantity FROM agile.bom WHERE id = bom.prior_bom
)
END prev_qty,
b.quantity
from rev r, change c, item i, bom b
where
r.change=c.id (+)
and r.item=i.id(+)
and i.id=b.item(+)
and c.id=b.change_in(+)
and c.change_number=’YourChange’
order by REDLINE_ACTION;

on March 10, 2020.
Add Comment
Agile Angel Answered on October 23, 2018.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.