Help retreiving BOM comments via SQL
Even though the field exists in the BOM table, it is not used as such. The value will actually come from AGILE_FLEX, as per the following query :
select b.find_number, b.item_number, b.quantity, f.notes from bom b, agile_flex f where b.item=NNNN and b.item = f.id(+) and b.id = f.row_id(+) and 1036 = f.attid(+);
Note that *all* of the links from BOM to AGILE_FLEX have to be an outer join, as a record may or may not exist. If you do not outer join on ATTID, it will not give you anything. 1036 is the attribute ID for “Notes” per the configuration. Things will also work this way for MULTITEXT30 (1341), MULTITEXT31 (1342) and DESCRIPTION (1020). If you are running Agile 9.3.3.0 or later., there is also MULTITEXT32-44 (2000019524-2000019536). Those are not often used, but they are there.
Is LIST02 seriously used as Find Number???? What do the list values look like???
Also note that so long as CHANGE_OUT = 0, the component stays active in the assembly BOM until a new revision modifies a BOM value or replaces the component with a different one. Currently it appears that your query is only intended to get the latest released BOM for an assembly???
Hi Kevin – Thanks for the info. I am curious as to how you learned all of this stuff….anyway, I am off work already, so I can’t test this until tomorrow.
Concerning the LIST02 as find number, yes, we hijacked that field. We use it as an indicator for where that item is on a larger assembly. We have left the stock agile find number as blank/unused. So for example, “180 FRONT WHEEL ASSEMBLY”, indicates this item is used somewhere in the front wheel assembly area. We make motorcycles.
On the last note, yes, I only want the latest released BOM. Making an excel file which queries our Agile database and ERP database, both pulling BOM data, so I can make sure they match.
Nick
SELECT find_number,
b.item_number,
i.description,
f.text AS BOM_notes,
date01 AS phased_in,
date02 AS phased_out
FROM agile.bom b
LEFT OUTER JOIN agile.agile_flex f
ON b.item = f.id
AND f.attid = 1036
AND b.id = f.row_id
JOIN agile.item i
ON b.component = i.id
WHERE b.item = (SELECT id
FROM agile.item
WHERE item_number = ?)
AND change_out = ‘0’
ORDER BY To_number(find_number),
b.item_number;
Nick,
Oracle don’t publish the database schema for Agile, so I expect Kevin has just done the graft to figure out the relationships between tables. As you will have seen from their naming most table do what they say in their name. As you are finding there are a few key tables where you need to look to relate an ID to a meaningful value.
Now given you have mentioned the magic word ‘Motorcycle’ which manufacturer do you work for and what discounts are available for the help tendered so far 😉
Adrian
I’m with Adrian, who do you work for (Zero??) and will there be discounts available?? ROTFL
That said, I started working for Agile Software in 2000 and have always been working with the database, mostly using DataLoad. But I really started to learn how things worked in there when I had to extract (and then load) data for a database merge project. I’ve just been keeping up ever since then. So I’ve had lots of on-the-job training, as well as access to internal stuff and people who knew more than me. Well, up until a few years ago when I left Oracle.
Agile actually had a clause in their software warranty that specified that if a customer used SQL to alter anything in the database, the warranty was null and void. If someone screwed things up (and some did!!!), they would have to pay Agile (and folks like me) lots of money to fix it back up Agile never published anything on the database schema (and neither has Oracle), mostly so that folks wouldn’t be unduly tempted to work around the application itself and do things directly in the database (damned engineers!!!). So for someone to try and figure things out isn’t easy, and it usually takes a lot of trial and error.
If you know anything about relational databases, this schema is the closest to fifth normal form I have ever seen. It is about as compact (data-wise) as you can get. And yet it works very well, even for huge databases (can you say multi-terrabyte-sized dump files?). The folks who put the original schema together (whom I met a long time ago, but who have long-since retired) really knew their stuff. And the follow-on folks haven’t screwed it up (well, a time or 2, but those got fixed back up right). Then there is the PG&C stuff, and the PPM stuff…….. Don’t go there. Complicated, believe me.