You cannot do this through the user interface. Agile explicitly prevents modification of the item (and it’s revision) once it is released. It is possible to do this in the database itself, but you had best know what you are doing!!

Get the ID in the ITEM table for the item number. Get the ID for the ECO that released the wrong revision from the CHANGE table. Both can be done with

select ID from <TBL> where item/change_number = '<number>';

Then use those ID values to get the single record in REV that needs to be modified. Easiest would be the following :

update REV set rev_number='<new rev>' where item = '<ITEM ID>' and change = '<CHNG ID>';

Make darn sure it only updates a single record before you commit. You should do this when the Agile is not running (like just before you run Averify, for example). Once done, you should be able to see the new revision value.

Please note, this is NOT something to be done often, and in fact this should be the ONLY time you ever do it. Make sure your process is modified to include verification that the revision is correct before it is released, so that this does not happen again.

Or use FileLoad and set the “Attach Files to Incorporated Items” option. Of course, this also requires that you have privilege to run FileLoad as well.

A bit of a PITA if all you need to do is add a single file, but it will work.

No.  The database schema is specifically set up to NOT allow duplicate part/document numbers, change numbers, manufacturer names or manufacturer part numbers (in the PC module, but this also applies to  most objects created in the PQM and PG&C modules).

It is not good business practice to name things (even related ones) to all have the same number in a PLM database. If someone gives you a number, which record should you look at?? The part, the design document, the manufacturer part? Each object having a distinct number makes the system easier to search for things (seeing 11 records that *all* show the same number can be confusing). And since ITEM_NUMBER is usually indexed as it is searched on quite often, making sure it is unique makes the database much more efficient.

You could drop the constraints in the database, but I would highly recommend that you DO NOT do that. A much better solution would be to configure a “Related Object” field in P2 that could then be set to show the “parent” object that carries the number you want as the “master” value. Or use the same base number plus a suffix that designates what the subordinate object is (like “CD” for Configuration Document, so the document number would be “XYZ1234-CD”). Or use the Relationship tab to link related things to the parent object (the part, in most cases).

Having the exact same number all over the place might sound simple, but in fact it can be both confusing and inefficient.

Try using Import. Yes, it can handle data that is in Excel, or just text files. You will have to map what is in the Excel file to what you have configured in Agile, but that is probably a 1-time deal (mapping files can be saved and re-used), if all of your data looks the same.

But it seems that you are asking if Agile has a user interface that “looks” like Excel. And the answer is no. What you see is what you get. But if you can put together an Excel template file that users can fill out and then have someone run it through Import, that might actually work fairly well. And while they are doing that, have your users look at the data in Agile, and they might actually start *using* Agile because it is fairly simple and works very well.

To see what the definitions for PROPERTYID are, run the query :

select entryid,entryvalue from listentry where parentid=181;

PROPERTYTABLE is used to define the “properties” of various types of things in NODETABLE. PROPERTYID lets you know which property is being defined. The string that is in VALUE is what the property is (like ITEM.CATEGORY being the database attribute, propertyid=10). Except for propertyid=15, where the ID of the list assigned to the attribute is actually in SELECTION, not VALUE.

I once tried to just show the BOM for the latest revision of an assembly in a single query and I got it to work. But it was so messy and kludgy, I threw it away – it was not worth keeping. It is the list of change IDs that is required to be used  for each revision that is a pain. It is far easier to create a table for your data, write a PL/SQL block to process things, and be able to see what is going on for each attribute, and each step of the processing.

To get complete data for each revision of an assembly, you need to get the list of the database IDs for ALL changes that affect the assembly. Then loop through them and set up the criteria for your main query against the BOM table as follows :

1. For the introductory revision (if it has BOM data), specify “where change_in = 0 and change_out is not null”
2. For each released revision (ordered by release date), specify “where change_in in (0,<all change IDs processed so far including the current revision>) and (change_out=0 OR change_out NOT IN (<all change IDs processed so far including current revision>) “;

So you will execute your main query getting the various fields you wish (find_number, quantity, etc.) from the BOM table N+1 times, where N is the number of revisions the assembly has released. Processing un-released revisions is not advised, as the data might change. Otherwise, just process them like a released revision but make sure to show the revision as enclosed in parentheses, like “(F)”, so that users know it is un-released.

If you need to include change-controlled attributes (other than the assembly revision), then you need to include subqueries against REDLINE_ATTRIBUTE and/or REDLINE_MSATTRIBUTE for each change-controlled attribute. Something on the order of “(select new_text from REDLINE_ATTRIBUTE where object_id= BOM.item and change_id = <ID of the change number linked to the current assembly revision> and attid = <ID for the attribute needed>)”. The sub-query shown would be for a text or multi-list attribute (NEW_DATE would be for a date value, NEW_NUMBER1 would for a numeric or list attribute). Note that if the value had not changed for the given revision, there will be no record in the table. Hopefully you do not use change-controlled attributes.

I would very Very VERY strongly advise you to not try to do this in a single query. Is it doable? For a very simple BOM configuration and for a single revision of the assembly, maybe. For all revisions and if there are change-controlled attributes involved, no. There are too many things going on. I can guarantee that the easiest way to do it is to build a table with the all the attributes you want in it, and fill it in for each assembly revision (using the where clauses I provided above), and THEN handling list attributes as well as change-controlled attributes.

As Patrick stated, if issues are found, they will be patched. But that is it. In fact, you can’t even purchase Agile PLM any more, so far as I know (for at least a year).

For the past few years (early 2017 on), you could only buy Agile PLM if you *also* purchased some Cloud PLM parts. This was all based on Oracle having changed how much commission sales folks got on various things. Anything Cloud (PLM or not) got 7x. Agile PLM was 1x. Would you only sell Agile PLM??

There will never be a 9.3.7.0 release of Oracle Agile PLM, unless something drastic changes. I have not seen a new bug patch in quite a few months, but that is not counting one-offs. The current release is 9.3.6.9, and that has been out for a while. And it requires a number of patches to be fully installed.

A query that will point out where there is a difference would be :

select i.item_number ASSEMBLY, r.rev_number, b.item_number COMPONENT, b.quantity, (select count(*) from refdesig where bom = b.id) RD_CNT
from bom b, item i, rev r
where b.item = i.id and b.item = r.item and b.change_in = r.change
and to_number(b.quantity) <> (select count(*) from refdesig where bom = b.id)
and exists (select null from refdesig where bom = b.id);

This will list the assembly number, revision, component along with the BOM quantity and count of ref-des. Note that this will not handle ranges of RefDes (like “A1-3”) or much of anything other than simple values (which are stored separately in the REFDSIG table). You may well get a LOT of records that are not quite an issue, but I did exclude all records that do not have any RefDes at all. Also note that QUANTITY is a *text* field, so I had to use TO_NUMBER in the query. If you have strange characters in there (most do not, but….) the query will fail.

Something like this should work :

select c.change_number, u.first_name || ‘ ‘ || u.last_name || ‘ (‘ || u.loginid || ‘)’
from change c, agileuser u, signoff s
where c.statustype not in (3,4)
and c.id = s.change_id and s.signoff_status = 0
and s.user_assigned = u.id and u.enabled = 0;

STATUSTYPE is used to define which state of a workflow the change is in, 3=Released and 4 = Implemented. It is not released if it is not in one of those values. Note that 0=Pending, 1 = Submitted, 2 = Review, 5 = Hold and 6 = Cancel. For SIGNOFF_STATUS, 0 means that the workflow is awaiting approval from the given user. And for users, ENABLED indicates if a user is active or not (0=Disabled, 1 = Enabled).

Sample code to do what (and for what)?? You could write an SDK application to extract data from Agile Advantage and then use either SDK or Import to migrate it into the Agile PLM 9.3.x environment. But the documentation for Agile Advantage would be found on the Oracle documentation website, if anywhere (the software is over 10 years old). So far as I know, there is no code/SQL to extract data or load data available (heck, there is no documentation on the database schema).

Note that data extraction would be fairly easy using either ChangeCAST or an SDK app, but loading the data (using either Import or an SDK app) would not be easy. Since you are going through the Agile Application Server, you cannot set the create or release dates for a change, as they are always set by the Application Server. You can modify date values in the database using SQL, but you better know what you are doing or it won’t be correct. Also, are you planning on doing the data migration  in one shot, or a few parts/assemblies/changes at a time??

If you need to do it in 1 shot, the best method would be to extract the data from the Agile Advantage database into Oracle tables and then use DataLoad to migrate the data into  the Agile PLM 9.3.x database. Since DataLoad does not go through the Application Server, it directly sets the dates and status values in the database so they will be as they should be. I can extract the data and also use DataLoad to do this. In addition, I can migrate object history, and either load it into the history tables or attach it as a file to the objects. Contact me at cummingsplm@gmail.com if you wish to discuss further.