As noted by stevend17, AGILEOBJECTIDSEQUENCE was used to generate the ID for all tables in the database. For everything up to and including Agile 6.x, 7.x, 8.x and 9.0.x.

Somewhere in there (9.2.x?, 9.3.x?), they introduced ITEM_HISTORY_ID_SEQ, which is only used for the ITEM_HISTORY table. It starts at  -2147000000 and goes up to +2147000000. So it gives a lot more range and is used exclusively for the Item history table. CHANGE_HISTORY_ID_SEQ works the same way for the CHANGE_HISTORY table.

Hoo boy, you aren’t asking for much, are you?? I would venture that it cannot be done as “one query result”. As a PL/SQL procedure, certainly. But it would most likely not be a single procedure but a group of procedures and functions.

Anyway, you would have to go through all of the ECO revisions of the assembly, and for each one, go out and find the component parts that are in the BOM for that specific assembly revision. And then for each component part in the assembly revision, you would have to get the component revision that was released most-recently previous to it (or that is how I *think* it always works – the reason I am here is because I need to find out if there is a configuration setting for that).

Given that the query for each assembly revision in the BOM looks like “select item_number, find_number, quantity from BOM where item=XYZ and change_in IN (0,<list of change IDs for all processed assembly revisions>) and (change_out = 0 OR change_out NOT IN (<list of change IDs for all processed assembly revisions>) )”, you have to *build* the query for each assembly revision.

No matter how you try it, not an easy task.

What version of Agile is this?

Do the objects *stay* missing out of Simple Search, or do they eventually start showing up?

It almost sounds like a DB issue, in that the CTX indexes are not getting updated very quickly. Verify that your CTX schema is good. Run Averify and see if it has anything to say about how the database looks.

There is no way to search for mfr parts in which the Where Used is null (you can do that for parts and documents).

The only good way is to execute the following query in the database :

select m.name MANUFACTURER, mp.part_number from manufacturers m, manu_parts mp

where mp.manufacturer = m.id and not exists (select null from manu_by where manu_part = mp.id);

This will list the mfr parts that are not (and never have been) used in any AML for any part.

Is the CO that releases rev 02 of ABC (and XYZ) released?? If not, then ABC is *still* at rev 01.

Since you are using the same CO to revise both the assembly and the component part, you will not be able to get the correct BOM for the assembly (revision-wise) *UNTIL* the CO is released.

Look in SCHEDULED_EVENT, where OWNER is the ID of the inactive user. I suspect that is where scheduled reports would be. Simply delete the record or set ACTIVE to be zero, and the report will no longer run.

Anything starting with LIST are just list attributes. If they have values, look in LISTENTRY where ENTRYID = LIST01 (for example). CRITERIAMET is a list attribute as well, and uses the Yes/No list, parentid=451 in LISTENTRY. If you ever get more than 1 record for a specific list value ID, you will need to also specify the parentid for the list that is assigned to the attribute. Th list parent ID can be found in PROPERTYTABLE.SELECTION using PROPERTYID=15 and the attribute ID as PARENTID.

RULE is something of a composite attribute, and I have never seen it used. It might be multi-text and therefore in AGILE_FLEX (see below). “Type (Image)” is not stored as it is an image linked to other attributes.

The 3 MULTI-TEXT attributes are actually stored in AGILE_FLEX, where ATTID is the ID assigned to the relationship attribute in NODETABLE (and as shown in the Agile Classes Report, which has just about all the information you need to do most of this). Same for NOTES and NOTES1.

Everything else should be fairly straightforward.

Do you actually use the attributes in the RELATIONSHIP table?? Note that if all you are you using are the default attributes, the query is simple :

select c.change_number, i.item_number NAME, i.description, n.description “CURRENT STATUS”, (select entryvalue from listentry where parentid=2000007913 and langid=0 and entryid=r.rel_type) TYPE
from relationship r, change c, item i, rev rv, nodetable n
where r.ctr_objid = c.id and r.ctr_objtype =6000 and r.eff_objid = i.id and r.eff_objtype in (9000,10000)
and i.id = rv.item and rv.latest_rev=1 and rv.release_type = n.id order by 1, 2;

The above query will show all parts/documents (eff_objtype in 9000,10000) linked to ECOs (ctr_objtype=6000)in the database.

If you actually use the list/text/multilist attributes in the RELATIONSHIP table, it gets more complicated. For text, just include them in the query. For multitext, link to AGILE_FLEX and *know* the attributes ID you need, link using the C.ID = AGILE_FLEX.ID and R.ID = AGILE_FLEX.ROW_ID. For list, you can just have a sub-query that gets the value, similar to what the above query does for REL_TYPE. Multilist attributes are not anywhere as easy, so I hope you are not using them.

Note that the only attributes listed for the related object are number, description and current status (which is blank for everything *but* parts/documents). All other available attributes are from the RELATIONSHIP table.

The short answer is yes.  If the value in CHANGE_HISTORY.CHANGE_ID does not match anything in CHANGE (or others, see below), it is an orphan record and can (and really *should*) be removed. Heck, you can even compare the timestamp on the CH record to the create date on the change, and know that it is an old one and so it can be removed as well. I would also look at ITEM_HISTORY and ITEM_HISTORY2 (just in case) as well as SIGNOFF and WORKFLOW_PROCESS (2 other tables intimately linked to changes).

Note that for both of the basic history tables, they are not only history for the ITEM and CHANGE tables. ITEM_HISTORY holds data for non-routable objects (parts, documents, users, mfr parts, manufacturers, sites, customers, etc.) and CHANGE_HISTORY holds data for routable objects (ECO, ECR, MCO, PSR, QCR, Declaration, etc.). Look at CHANGE_HISTORY.CLASS_ID and ITEM_HISTORY.CLASS to see what type of object each record is related to, and therefore which table you should check to make sure that the parent object exists. A bit complicated, but not real hard.  Get a list of the class values in each table, find out what they are from NODETABLE (4983, for example, is Customer in the PQM module) and then write a script to check against the correct table for each class ID. Neither SIGNOFF nor WORKFLOW_PROCESS track the class of the object they are related to, so you  might have to link through CHANGE_HISTORY or do something else to make sure you do not remove any good records. Or if you ONLY use the PC module, most of the above is all hash anyway.

Good luck.

Steve is correct in that the lifecycle ID and text values can be found in NODETABLE (parentid = 1514 or 9045 for parts, 1514 or 9043 for documents). But the ID values are stored in the REV table, in RELEASE_TYPE. A lifecycle is assigned to the revision of  a part and/or document, not to the part/document itself.