Kevin Cummings's Profile
Agile Angel



  • Agile Angel Asked on March 11, 2020 in Agile PLM (v9).

    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.

    • 2 answers
    • 0 votes
  • Agile Angel Asked on March 11, 2020 in Agile PLM (v9).

    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.

    • 4 answers
    • 0 votes
  • Agile Angel Asked on March 3, 2020 in Agile PLM (v9).

    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.

    • 3 answers
    • 0 votes
  • Agile Angel Asked on February 20, 2020 in Product Collaboration.

    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 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, and that has been out for a while. And it requires a number of patches to be fully installed.

    • 2 answers
    • 0 votes
  • Agile Angel Asked on February 10, 2020 in Agile PLM (v9).

    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 = RD_CNT
    from bom b, item i, rev r
    where b.item = and b.item = r.item and b.change_in = r.change
    and to_number(b.quantity) <> (select count(*) from refdesig where bom =
    and exists (select null from refdesig where bom =;

    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.

    • 3 answers
    • 0 votes
  • Agile Angel Asked on February 5, 2020 in Agile PLM (v9).

    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 = s.change_id and s.signoff_status = 0
    and s.user_assigned = 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).

    • 1 answers
    • 0 votes
  • Agile Angel Asked on January 10, 2020 in Agile PLM (v9).

    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 if you wish to discuss further.

    • 1 answers
    • 0 votes
  • You can see when a user logged in and logged out using USAGE_HISTORY with the user information being in NODETABLE and PROPERTYTABLE.

    But you cannot determine when or which objects are accessed/viewed. That information is not stored in the database. Given the login/logout date/time values, you could search CHANGE_HISTORY and ITEM_HISTORY to see what activities (if any) the user performed, but these will only be create/modify/delete actions, not what they looked at.

    As for what a user looked at or accessed in attachments, the codes that Keith provided are for CHANGE_HISTORY. The codes for ITEM_HISTORY.ACTION would be : 0 – Print Attachment, 1 – Get Attachment, 13 – View Attachment, 15 – Get File Attachment, 16 – Get Image Attachment, 21 – Checkout Image Attachment, 22 – Checkout File Attachment, 29 – View File Attachment, 30 – View Image Attachment, 36 – Open Attachment.

    • 3 answers
    • 0 votes
  • Agile Angel Asked on December 14, 2019 in Software Development Kit (API).

    What user account is the PX running as??? Does that user have all needed user administrator privileges and roles?

    And I agree with Raj, make darn sure you always add default roles to the user before you make them active.

    • 3 answers
    • 0 votes
  • Agile Angel Asked on December 6, 2019 in Product Collaboration.

    At 17GB, something is definitely wrong.  Interesting that the AGILE_DATA2 tablespace file is so large in comparison to all the others (26GB vs. 2GB for all others). Of course, that is where CHANGE_HISTORY is located. But also PROPERTYTABLE, ATTACHMENTS, ITEM, NOTIFICATION and NOTIFICATION_FIELDS. That is 2 (CH and NF) out of the 4 tables that usually take the most time during import or export (also ITEM_HISTORY and A_DW_TXN_LOG).

    As Keith suggested, try using DataPump and also truncate the A_DW_TXN_LOG table. Also get a count of records from CHANGE_HISTORY, see how large it is. And get a count of records by class from CHANGE_HISTORY (select class_id, count(*) from change_history group by class_id order by class_id). It might be interesting to see if you are getting some weird records in there (like a lot of PDP records or null records). Right now, I think that is a good place to start looking.

    • 6 answers
    • 0 votes