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
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
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;
