Kevin Cummings's Profile
Agile Angel
2828
points

Questions
3

Answers
157

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
  • Agile Angel Asked on October 30, 2018 in Product Collaboration.

    Use FileLoad.  How to do so is explained in the Import/Export Guide, with FileLoad being chapter 13 in the 9350 document.
     It isn’t too hard, so long as you understand how to build the index files and what the differences are between the Web client version and the Java client version. If you have the files located on your PC, the Java client would be the one to use. If you can get them to the web proxy server, the Web client would be preferred.
     So far as I know, Import cannot be used to process attachments unless you are using a PDX or aXML source file. A PX can do it, but FileLoad is already there (but does require the necessary roles/privileges to even be able to access it).
     The basic question is, how many files do you need to process, and how often. If the count is over 50, I would recommend you use FileLoad. You will otherwise need to balance what needs to be done to use FileLoad versus what needs to be done to use a PX.
    EDIT : And I forgot to tell you what to do if the count is < 50.   In that case, you can probably do it manually faster than you can train yourself to use FileLoad and get the index files together. Not that using FileLoad is hard in any way, but it does take practice and attention to detail to get correct. Always test using a small file (3-5 records) when doing a new attachment load, just to make sure you have the index format correct, and to make sure everything is running correctly. Also note that FileLoad does not care if the attachment is already there, it WILL attach the files that you tell it to.

    • 78 views
    • 7 answers
    • 0 votes
  • Agile Angel Asked on October 29, 2018 in Product Collaboration.

    Sorry, arbernard, but if you cannot open the criteria in the Java client (and it sounds like you cannot), there is no way to change them such that they will work. But simply deleting them (if possible) should work, and you can then rebuild them correctly.

     If that does not work, removing the subclass followed by removing the 2 criteria, might work. So far as I know, removing the subclass will not also remove the criteria, but they will be incorrect as the object class they affect will be gone, and so you should be able to delete them.

     I would strongly suggest making a copy of your production database and loading it into a test environment, and then trying this. If doing either of the above actions causes worse problems (and that is possible), then your production environment is not harmed, and you can start over in the test environment to do other things.

    • 172 views
    • 21 answers
    • 0 votes
  • Agile Angel Asked on October 24, 2018 in Product Collaboration.

    The criteria stuff is not contained all within a single table. NODETABLE, PROPERTYTABLE, QUERY, ADMINMSATT and CRITERIA can all be involved.
      The SQL query below will list all criteria and the objects that they work with :
    select n.description “CRITERIA”, n2.description “OBJECT_TYPE”
    from nodetable n, propertytable p, nodetable n2
    where n.objtype=111 and n.id=p.parentid
    and p.propertyid=53 and to_number(p.value) = n2.id
    order by n.description;

     You can limit it further by adding the clause “and n.description like ‘%<part of the criteria name>%’” to the above query, so you do not have to wade through everything. The base record for a Criteria is stored in NODETABLE. The properties are stored in PROPERTYTABLE. There will be a “criteria” associated to it, and that will be stored in QUERY, CRITERIA and possibly ADMINMSATT. Oh, and possibly ADMINCRITERIA as well.

     I suspect that the criteria that you are concerned about is ID = 2476386, whereas ID = 1649 indicates that a life-cycle phase for something (probably a part or document) is involved in the criteria. If the specified lifecycle that the criteria references cannot be found anymore, this is the type of error that you would see. And no, Agile does not check to see if a lifecycle is used in a criteria before allowing you to disable and remove it.

    • 172 views
    • 21 answers
    • 0 votes
  • Agile Angel Asked on October 24, 2018 in Product Collaboration.

    Arif is steering you in the correct direction. Averify is a tool you will need, and it should be run at least once a month, and it provides a lot of information if there is a data issue in the database. And if/when you open an SR with Oracle, the latest Averify log file is one of the first things they ask for. Check your Agile application server and/or database server for anything named “averify.properties”, and you have most likely found where it is installed. Open the averify.properties file, and if it says that you are at version 1.7.6 (or above, like 1.7.8), then you are at the correct version for your database.

     The attachment seems to show that the account you are using does not have permission to discover or see whatever is there. That in itself is just a bit strange, as if you are logging into Agile using the admin account (or another account that has the admin role/privilege), you have access to just about everything. And if it is in fact trying to see something outside of the Agile database, that is even more strange.

     A database/SQL fix is risky without looking at it in depth. Things (especially the configuration records) are very interconnected, and telling you to delete record X from a table might well introduce more issues, and not quite fix the issue you are seeing.

    • 172 views
    • 21 answers
    • 0 votes
  • Does the account the the PX uses to log into Agile have access to the site you are trying to use??
     If the user does not have access to the site, then it may not be able to add it to the BOM on a component part.
     And as noted, both the assembly and component part must also list the given site in their Sites tab.

    • 70 views
    • 3 answers
    • 0 votes
  • Agile Angel Asked on October 12, 2018 in Product Collaboration.

    And Arif is correct, LABEL is where the ref-des values are stored in REFDESIG, and the join is done as BOM.ID = REFDESIG.BOM(+).
     The bigger problem for a single query is that the ref-des values are stored as separate values in the table, and not as a single consolidated string. So you WILL get as many records for a single BOM line as there are ref-des values, if you simply link the 2 tables using the outer join.
    The only way around this is to write a function to get the ref-des values and then have your query use it, instead of doing a join to the table. Something along the lines of :
    create or replace function GetRD(bomID IN number) RETURN CLOB IS
        cursor csrSRD is select label from refdesig where bom = bomID order by label;
        rdes       varchar2(50);
        refdes    clob;
    begin
       refdes := null;
       open csrSRD;
       loop
              fetch csrSRD into rdes;
              exit when csrSRD%NOTFOUND;

              if refdes is null then
                    refdes := rdes;
              else
                    refdes := refdes || ‘,’ || rdes;
              end if;
        end loop;
        close csrSRD;

         RETURN refdes;
    end;
    /
    show err

     Note that the above function returns a CLOB value, as you have no idea how long the entire list of ref-des values will be. The above will return anything from a null string to a VERY long string of ref-des values.

    And the function can be used as follows :
    select i.item_number ASSEMBLY, b.item_number COMPONENT, b.find_number, b.quantity, GetRD(b.id) REFDES
     from bom b, item i
     where b.change_out = 0 and b.item = i.id and i.category = (select entryid from listentry where parentid = NNN and entryvalue = ‘11111’);

     Given everything you are trying to do, it might be easier to put all of the data into a table, update the list values and such using SQL, and to then finally just select all records from the table. Joins such are you are doing for LIST06 are far easier/simpler to do as a single query against a destination table than as you are doing. Just about everything in the Agile database (which is NOT documented in any way, shape or form by Oracle) is an ID, that is then linked to another table that contains the actual text value you need. ITEM.CATEGORY is an example, as it is NUMERIC data type, and must be linked to LISTENTRY to get the text value (just like you are doing with LIST06).

    • 61 views
    • 2 answers
    • 0 votes
  • Agile Angel Asked on October 11, 2018 in Product Collaboration.

    There is a flag in the REV table named INCORPORATED that is set when a revision has been incorporated.
     A query to select revisions that are released but not incorporated would be something like :
    select i.item_number, r.rev_number, c.change_number
     from item i, rev r, change c
    where i.id = r.item and (r.incorporated is null or r.incorporated = 0)
        and r.change = c.id and c.statustype in (3,4);

     INCORPORATED can be null, 0 or 1, with 1 indicating that the revision has been incorporated. STATUSTYPE in the CHANGE table indicates the type of status for the change – 0=Pending, 1=Submitted, 2=Review, 3=Released, 4=Implemented, 5=Cancel, 6=Hold.  So the query looks for a revision where INCORPORATED is null or zero, and where the related change has STATUSTYPE equals 3 or 4.

    • 58 views
    • 2 answers
    • 0 votes
  • Agile Angel Asked on October 8, 2018 in Other APIs.

    Not from the API, that I am aware of.  Since the change/assembly revision is not yet released, it is useless to show a component as being used because it might be removed before the assembly BOM is released.

     A query in the database would be as follows :
    select i.item_number ASSEMBLY, b.item_number COMPONENT
     from bom b, item i
     where b.item  = i.id and b.change_in > 0
         and exists (select null from change where id = b.change_in and statustype < 3)
         and b.item_number = <item you are interesting in>;

    • 171 views
    • 2 answers
    • 0 votes
  • I am not 100% certain how PDX handles attachments within the file itself. For aXML, the file is at  the root level of the zip file and it is referenced by name.
     But you have the “file name” without the file extension. I would use the full file name in both places, and then have the file itselfv located inside the PDX file. That way Import will see it there, and hopefully be able to process it.
     I normally always use FileLoad to process attachments, it is more straightforward. Unless the PDX or aXML file you are importing was produced by Agile, I would use FileLoad instead. It is just easier to use.

    This answer accepted by davidvinas. on October 10, 2018 Earned 15 points.

    • 91 views
    • 2 answers
    • 0 votes
  • Agile Angel Asked on October 1, 2018 in Product Collaboration.

    You would have 2 distinct solutions, one for existing parts and one for new parts. Unless you want to run the solution to “fix” existing parts every week or so (NOT recommended).

     The solution to fix existing parts would be to have a program or script create a new part record (no suffix), and then take data from existing records and copy or move it to the new part record. Not very simple, but doable. And probably lots of logic to decide whether something should move or not, given that there are very likely to be overlaps in what each part has. So the programmer will need to know precedence for how to resolve issues.

     The solution for new parts would be to have the integrations between the PDM/ERP systems and Agile not add a suffix to the part number, and always check to see if it exists in Agile first. If it is already there, add data as needed. Otherwise create the part and then add the data that you have. 

     Neither solution will be easy, and each could be rather difficult, depending on what your business rules are for getting the data into Agile and which system has precedence in terms of the data. And there are no easy nor hard-and-fast rules for what needs to be done. A lot of it depends on your business rules for how things should be handled. Start there, and then ask the question again, providing those business rules so we have an idea what needs to be done.

    • 82 views
    • 3 answers
    • 0 votes