Kevin Cummings's Profile
Agile Angel
2723
points

Questions
3

Answers
145

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 7 days ago in Product Collaboration.

    Make them macadamia/white chocolate cookies, and I am yours.  And Steve is correct, there has *never* been a published Agile schema. Even to consultants.

     The data you need will mostly come from the SIGNOFF, WORKFLOW_PROCESS and CHANGE_HISTORY tables, starting from CHANGE and also pulling in user information from AGILEUSER.  I wrote a simple Change Status Cycle Time report many years ago (by change class and product line), but that was using MSAccess and VB, linking to the Agile database using ODBC. I haven’t even looked at it in over 10 years.

     I do think it would be doable using an PL*SQL script, which would put the information into a table, and that could then be exported using SQL Developer or Toad into a spreadsheet or something else such that it could be formatted as you need. I agree with Steve that there could be lots of different scenarios. and the script would need to handle them all, just in case. Nothing that is rocket science, but to cover the bases. 

     Let me ponder time and effort to do this and discuss some other options with colleagues, and contact me next week, and we can talk more.

    • 130 views
    • 4 answers
    • 0 votes
  • Look at RELEASE_TYPE in the REV table where ITEM is the ID of the item that you want the lifecycle for. If you want this for the revision that a user will see when pulling the item up in Agile, specify where LATEST_FLAG=1, otherwise you will need to specify which revision (and change, really). And of course, the actual text value is in NODETABLE, what is in REV is just the ID of the lifecycle value.

     The query for latest revision would be something like :
    select r.release_type, n.description from rev r, nodetable n
     where r.item = (select id from item where item_number = ‘XYZ’) and r.release_type = n.id and latest_flag=1;

     To get the lifecycle for a specific revision, the query would be like :
    select r.release_type, n.description from rev r, nodetable n
     where r.item = (select id from item where item_number = ‘XYZ’) and r.release_type = n.id
        and r.change = (select id from change where change_number = ‘ABC’) and rev_number = ‘A’; 

    This answer accepted by Swagoto. on September 13, 2018 Earned 15 points.

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

    <sigh>   As usual, operator error (well, kind of).
     Early on, they had only wanted Parts to have the legacy number in the description, so the query to do so had that limitation in the where clause. And then later, I needed to make sure that the description in REV reflected the legacy number as well, but that query did not limit to only Parts. So when viewing the data, the legacy number showed up in the description for both Parts and Documents.  But for documents, it was not in the ITEM.DESCRIPTION field.
     Apparently when running Quick Search, it does not look at the REV.DESCRIPTION field, but only at the ITEM/CHANGE/etc. number and description fields. So you could see the correct values, but Agile would not search on them. I updated the descriptions in the ITEM table, dropped/created the CTX indexes, and now all is well.
     Note to self – Quick Search only looks at object data attributes, and not related data attributes.

    • 45 views
    • 6 answers
    • 0 votes
  • Agile Angel Asked on September 11, 2018 in Product Collaboration.

    And I got a better explanation from the client as to what is really going on.
     If you search for the document number, Quick Search works just fine (as I found out). If you search for the legacy number (yes, they renumbered *everything*), it does not find the document. Note that the legacy number is contained in the document description, so it SHOULD be found using Quick Search, as that field is included in the CTX index. And in fact, Quick Search usually returns 1 or more changes that contain the legacy document number in their description. So it is working as expected for changes.

     No, Keith, the database has not been exported/imported. I plan on trying that at some point in time, but since the load is being done on their system, this is not normally being done as a matter of procedure. But it is worth a try to see if things get cleared up.

     Yes, Matt, I have read through the various documents you showed and they did not offer much. I know the CTX indexes are used by Quick Search, and so they were dropped and recreated using DataLoad. I also tried the scripts I use for importing a database, but no luck there either.

     Yes, Steve, a new document with the same setup (legacy number at  the end of the description, preceded by “Legacy #”) does work with Quick Search And yes, Averify has been run several times, and showed nothing concerning CTX (or any other kind of) indexes.

     The really strange part is that Quick Search works as expected for Parts, but not for Documents. There is no good reason for that, as both are covered by the same CTX index on the ITEM table, which looks at both item_number and description. Then again, description is usually shown from the REV table. If I look there, yes, the description field for the document number does contain the legacy number value (although the description field in ITEM does not – for either Parts or Documents). Since I dropped and recreated all of the CTX indexes, it should not matter.    Which  is why I am scratching my head and asking for help…………..

    • 45 views
    • 6 answers
    • 0 votes
  • Agile Angel Asked on August 6, 2018 in Product Collaboration.

    I would suggest 3 things :
    1) Set the status to be something on the order of “DO NOT USE”. Make it very obvious that the manufacturers should not be used.  (I would also make the status appear in flashing red letters, but Agile isn’t too good at doing that).
    2) Adjust your review process to have the Manufacturing/Purchasing folks take a quick look at any parts with AML data, and verify that no inactivated manufacturers are being used. And slap the hands of engineers that insist on using inactivated manufacturers.
    3) If all else fails, set the manufacturer as deleted. That way it will not show up in searches, but would still be visible where it is used.

     Your users need to know that they should not use these folks anymore. I would think that Manufacturing (and/or Purchasing) would assist in checking for this, as it directly affects them. And it isn’t very hard for them to do.

    • 68 views
    • 4 answers
    • 0 votes
  • Agile Angel Asked on July 24, 2018 in Other APIs.

    CONTENT_URL will not get you the file per se, but it will get you what was indexed from the file, so far as I understand it.
     IFS_FILEPATH should enable you to open the file, but you will have to include the base directory of the file vault to get the full directory path for the file. Other than that, yes, I could see it being used to open a file.

    • 174 views
    • 8 answers
    • 0 votes
  • Agile Angel Asked on July 20, 2018 in Other APIs.

    CONTENT_URL is used to store the link to indexing content for the file. I cannot say for certain that if CONTENT_URL is null then the file has not been indexed, but that is the obvious conclusion. IFS_FILEPATH stores the directory/file name for where the file is located on the primary file manager server. If the file is also located on a distributed/local file manager, there will be a value in HFS_FILEPATH.

    • 174 views
    • 8 answers
    • 0 votes
  • As Steve mentioned, no-one outside of Oracle knows how the script works (and most within don’t either). I have never seen it, and I don’t even know of someone who had it executed on their system. But it *is* a big stick, as penalties are severe.
     As I understand it, simply being able to discover and read things does not constitute usage of a license. Creating, managing and processing PSRs and QCRs through a workflow does constitute usage of a license. Then again, that is just my assumption based on how things were done long ago.
     Since we have no idea how the script works, you might send a question into Support (or if you are friendly with a saleperson, ask them), but I doubt you would get an answer.

    This answer accepted by Matt Paulhus. on July 17, 2018 Earned 15 points.

    • 89 views
    • 2 answers
    • 0 votes
  • Agile Angel Asked on July 4, 2018 in Agile PLM (v9).

    As Swagoto stated, the query to get the history data is against SCHEDULED_EVENT_TRACKING (SET). Information for the event itself is in SCHEDULED_EVENT (SE), and it links to the history data using SE.ID = SET.EVENT_ID.

    • 83 views
    • 2 answers
    • 0 votes
  • Agile Angel Asked on July 2, 2018 in Other APIs.

    Swagoto is correct. You can access the REV table, but it isn’t needed. If neither of the internal change attributes in the ITEM table are set, it is because there are no released changes against the item, and therefore it is at Preliminary status. Also note, there is no “Introductory” revision in the database, just the dummy record where change = 0. The status on that record is always “Preliminary”.

    • 111 views
    • 3 answers
    • 0 votes