Kevin Cummings's Profile
Agile Angel
3008
points

Questions
3

Answers
170

I have been working on the Agile PLM space for over 16 years, mostly focusing on data migration and database work. I probably know how the schema works better than most outside of Oracle Engineering, having explored the schema for most of my Agile career
Title Senior Technical Consultant
Company Kalypso Consulting
Agile Version 6.x, 7.x, 8.x, 9.x
  • You cannot do that. There is no way to get Import to add data to the end of an existing value (or set of values). It assumes that the data has the complete value for each attribute that has a value in the data file.
     What I would suggest is to export what the current values are for the parts you need to update, write a macro (or edit the exported data file) to add the value(s) that are needed to the multilist attribute, and to then run that file through Import. That way you will have all the list values that you need.

    • 50 views
    • 2 answers
    • 0 votes
  • Agile Angel Asked on January 9, 2019 in Product Collaboration.

    Okay, to eliminate any pending BOM records from the latest BOM, the query would be as follows :
    select b.item_number, b.find_number, b.quantity
     from bom b
     where b.change_out = 0 and b.item = (select id from item where item_number='<assembly number>’)
       and exists (select null from change where id = b.change_in and statustype in (3,4) );

     The “STATUSTYPE in (3,4)” says to not include anything where the change is not yet released (3) or implemented (4). The “CHANGE_OUT=0” part of the query is simply telling the database that you want any record that has not been modified by a revision change (when this happens, CHANGE_OUT is set to be the rev change ID). The issue with pending changes is that CHANGE_OUT is not set to that ID until the change is released. So to eliminate pending BOM records you need to verify that what is in CHANGE_IN (the rev change that added the component) is released.

     CHANGE_IN and CHANGE_OUT are used to eliminate un-needed BOM records. If you do not modify a component going from rev A to rev B, why do you need a record for it?. CHANGE_IN is the ID of the revision change that added the BOM component record and CHANGE_OUT is the ID of the revision change that modified or removed the component part (a special case is when CHANGE_IN = CHANGE_OUT, which is used to indicate the component has been removed from the BOM). As such, every ID value in CHANGE_IN or CHANGE_OUT in the BOM for an assembly *MUST* be in the CHANGE attribute of the REV table for that assembly. Yes, it is complicated, but the storage savings were once computed at around 70% when an assembly had on average 10 revisions and 25 components, and only 10% of the components in the assembly BOM were modified in some way when a new revision was released.

     To get the BOM records for a specific revision (that is not latest), you would get the list of change IDs for each assembly revision up to and including the one being extracted (in release date order) and specify that CHANGE_IN is “0” or in that set of values. Then you specify that CHANGE_OUT is equal to zero or NOT in the set of revision change IDs that you just put together. That will get you all BOM records for a specific revision of an assembly. Note that for CHANGE_IN = 0, it is just an indicator that the BOM records were added to the Introductory revision of the assembly. Where CHANGE_OUT = 0 it simply means that the BOM record is active or pending.
     A query to get the revision BOM for an assembly would look like :
    select b.item_number, b.find_number, b.quantity
     from bom b
     where b.item = (select id from item where item_number='<assembly number>’)
       and CHANGE_IN in (0,<set of rev change IDs>) and (CHANGE_OUT = 0 or CHANGE_OUT not in (<set of rev change IDs);

     The “<set of rev change IDs>” would be comma-delimited, looking something like “7634589,8834590,10023573”. You do not need to worry about released vs. pending when getting the revision BOM of an assembly, as you are directly specifying which change IDs to look for in the table.

    • 88 views
    • 5 answers
    • 0 votes
  • Agile Angel Asked on January 8, 2019 in Product Collaboration.

    select b.item_number, b.find_number, b.quantity
     from bom b
     where b.change_out = 0 and b.item = (select id from item where item_number='<assembly number>’);

     2 things to note about the above query :
    1) It will select the latest released BOM, unless you have a pending change, in which case it gets latest released BOM and also the pending updates.
    2) You can add other BOM attributes to the query easily, but adding the component description and other attributes would get more complicated.

     So the above query is VERY simple, but should get you the basic BOM data for an assembly.
     You could modify it to get the BOM from a specific revision of the assembly that is not latest, but it gets rather complicated at that point (you would have to understand how CHANGE_IN/CHANGE_OUT works).
     Adding component attributes isn’t very hard (add “, page_two p” to FROM clause,  “and b.component=p.id” to the WHERE clause and then add the attributes you want to the SELECT clause as “p.date01“, for exeample). So long as they are date/numeric/text values, you are fine, but when you get into list/multilist/multitext attributes, it gets even more complicated because you then have to link to LISTENTRY to get the text values (to the point you would probably do it as a PL/SQL procedure).

    • 88 views
    • 5 answers
    • 0 votes
  • Agile Angel Asked on January 4, 2019 in IT and Networking.

    And here are the CHANGE table flag bit positions, as of 9340.

    • 80 views
    • 5 answers
    • 0 votes
  • Agile Angel Asked on January 4, 2019 in IT and Networking.

    And here are the ITEM table flag bit positions, as of 9340.

    • 80 views
    • 5 answers
    • 0 votes
  • Agile Angel Asked on January 4, 2019 in IT and Networking.

    As Raj noted, there is no published documentation on the structure of the FLAGS attribute, for any table. But they are used as 0/1 (or No/Yes) indicators as to specific things about the item/change/revision/bom record that has the FLAGS value. As to why it is in 0/1 notation, it because it is easy to use in SQL queries (as you have done) to get specific things.
     There is a file named “CMType.java” that details all sorts of flag position definitions (and MANY other things). I have attached the bit position definitions for the REV table. Note that position “0” is the first bit in the 32-character string. If I can get this to work, I will also get the ITEM/CHANGE/BOM flag bit positions put up here.

    • 80 views
    • 5 answers
    • 0 votes
  • There are *no* published documents on the Agile database schema. But myAgile is a very good place to ask questions about things that you have not been able to figure out on your own. The link that Arif provided is a good place to start. Also search for questions concerning how to get data for Items, Changes, BOM, Revisions, Page Two/Page Three attributes, etc.
     The basics of the schema are :
    1) CLASS and SUBCLASS are attributes in the base table – ITEM for Parts/Documents, CHANGE for ECO/ECR/MCO/Deviation/Stop Ship, etc. All base attributes are stored in the class base table. But a tab can contain data from many tables – Title Block has data from ITEM, REV, CHANGE shown, at a minimum.
    2) All page two/page three attributes are in the PAGE_TWO and PAGE_THREE tables, and link to the base table using ID and CLASS. CLASS also indicates which base table a record should be linked to.
    3) Almost every record in the database has a unique ID value, within the table (MSATT is an example of a table without a unique ID). Several different sequences are used to do this, so a given ID may not be unique across all tables (but usually is).
    4) The database is highly normalized, as in you rarely see a text value repeated in more than 1 table, but instead link to it using the ID it is assigned in the base table. So, a change number is found in the CHANGE table, but when you link the change to a revision, you see the ID of the change record with that number, not the change number.
    5) List attributes are much the same, the value is found in LISTENTRY along with an ID (ENTRY_ID), and referenced using the ID value in the attribute itself.
    6) Look for the “agile9schema.sql” file in the “$ORACLE_HOME\admin\agile9\create\agile directory. This gives you an easy lookup for what attributes are in a table.
    7) The configuration is held mostly in NODETABLE and PROPERTYTABLE, and is hierarchically arranged (note the PARENTID attribute in both tables). Other tables of interest are ADMINCRITERIA, ADMINMSATT, LISTNAME, LISTENTRY, CRITERIA, QUERY, FOLDERING and FOLDER.
    8) User information is mostly in AGILEUSER and  USER_ASSIGNMENT (PAGE_TWO is also used).

     As noted before, if you have a specific question, feel free to ask on this forum, and tag it as related to the schema. I or someone else will see it and answer to the best of our abilities.

    • 83 views
    • 2 answers
    • 0 votes
  • The first (small) part, covering directions :
    — DIRECTIONS :
    — 1) Put the counts script into a directory on the database server
    — 2) Open an SQL*Plus session and log into the Agile schema account
    — 3) Create a spool/log file for the script results SQL> SPOOL <directory>/agile_counts.LST
    — 4) Run this script SQL> START <directory>/Agile_Object_Counts_9x.sql.txt
    — 5) Close the spool/log file SQL> SPOOL OFF
    — 6) Exit the SQL*Plus session SQL> EXIT

    • 253 views
    • 4 answers
    • 0 votes
    • 253 views
    • 4 answers
    • 0 votes
  • Parts are in the ITEM table (as are Documents), revisions are in REV, ECOs are in CHANGE, Manufacturers are in MANUFACTURERS (and *not* in MANUFACTURER), and suppliers are in ORGANIZATION.
     Attached is a script I send to clients, and it covers all of what you have listed above.

     And I cannot get the file attach to work. I will post it later today.

    This answer accepted by KaustubhPLM. on December 13, 2018 Earned 15 points.

    • 253 views
    • 4 answers
    • 0 votes