Kevin Cummings's Profile
Agile Angel
3573
Points

Questions
5

Answers
233

  • Agile Angel Asked on July 28, 2016 in Agile PLM (v9).

    Even though the field exists in the BOM table, it is not used as such. The value will actually come from AGILE_FLEX, as per the following query :
    select b.find_number, b.item_number, b.quantity, f.notes from bom b, agile_flex f where b.item=NNNN and b.item  = f.id(+) and b.id = f.row_id(+) and 1036 = f.attid(+);

     Note that *all* of the links from BOM to AGILE_FLEX have to be an outer join, as a record may or may not exist. If you do not outer join on ATTID, it will not give you anything. 1036 is the attribute ID for “Notes” per the configuration. Things will also work this way for MULTITEXT30 (1341), MULTITEXT31 (1342) and DESCRIPTION (1020). If you are running Agile 9.3.3.0 or later., there is also MULTITEXT32-44 (2000019524-2000019536). Those are not often used, but they are there.

     Is LIST02 seriously used as Find Number????  What do the list values look like???

     Also note that so long as CHANGE_OUT = 0, the component stays active in the assembly BOM until a new revision modifies a BOM value or replaces the component with a different one. Currently it appears that your query is only intended to get the latest released BOM for an assembly???

    • 3213 views
    • 3 answers
    • 0 votes
  • Agile Angel Asked on July 27, 2016 in Agile PLM (v9).

    Right idea, wrong table. If you are running Agile 9.x, list values will be in the LISTENTRY table, using ENTRYID to get the ENTRYVALUE value.
     Something along the lines of :
    select b.find_number, b.item_number, b.quantity, l.entryvalue “LIST1”  from bom b, listentry l where b.list01 = l.entryid(+);
     Note the outer join, it is needed even if  you *know* there is always a value in LIST01. Because sometimes, there won’t be a value.
     To be 100% correct, if  the configurations for the Part BOM tab and Document BOM tab are different (list01 is used on one and not the other, or even worse, it is used in both but with different lists), you should include the list parent ID in the query :
    select b.find_number, b.item_number, b.quantity, l.entryvalue “LIST1”  from bom b, listentry l where l.parentid=2471234 and b.list01 = l.entryid(+);
     And then have separate queries in Excel with the correct parent ID, depending on whether you are viewing a Part or Document BOM.
     If you have more than one list attribute in use, you must do a separate join on each one with LISTENTRY to correctly get the list values.

    This answer accepted by Nicho247. on March 28, 2024 Earned 15 points.

    • 2944 views
    • 9 answers
    • 0 votes
  • Agile Angel Asked on July 27, 2016 in Agile PLM (v9).

    Find a distinct ID value from bom.list01 (select distinct list01 form bom). Then “select * from listentry where entryid = <one of the ID values>”.
     Hmmm, look at the configuration for list01 in the BOM tab. Open the attribute, click on “View Detail” for the list and look at the List Type. If it is not Simple, then the above answers will not work, and you will need to use something more complicated. If the list type is Cascade (consists of multiple values) you will need to create a function to correctly get the full list value. If it is Dynamic (it is an object reference), you will need to link to an object table to get the correct “list” value.

    • 2944 views
    • 9 answers
    • 0 votes
  • Agile Angel Asked on July 12, 2016 in Agile PLM (v9).

    Yes, but……….
     Doing things in the database can be rather risky. The schema is very complicated, and it is possible to not merely shoot yourself in the foot, but to blow your entire leg off.
     If you use what I specify below, TRY IT FIRST in a Development or Test environment. Make sure it works there, and develop a procedure of the steps to follow when doing something like this. The first 2 of those steps *will* be stopping Agile and making a backup of the Agile database, before running any script. After the script has been run (and the log file checked for any SQL*Plus issues), then start Agile back up again, and check your results within Agile itself.

     To soft-delete an item, use the following query :
    update item set delete_flag=1 where item_number = ‘<prelim item #>’;

     This will only soft-delete the items. And no, DO NOT just delete the record from the ITEM table, as there are MANY related records that would also need to be deleted. if they are still around, it *WILL* cause issues. 2 more steps would be to run Averify both before and after you run the soft-delete script, just to make sure it is happy with the database (note that Averify *is* your friend). If there are issues reported after running the script that were not there before running it, you did something wrong.

     If I am sounding a bit hard-core here, it is because I have been working in and with the database for over 16 years, and I know it pretty well. I cannot count the times I have seen very well-intentioned folks badly screw a database up (and in one case, with no recent backup). And yes, I have done so too, but I *always* have a backup handy.

    • 2652 views
    • 4 answers
    • 0 votes
  • Agile Angel Asked on July 12, 2016 in Agile PLM (v9).

    Absolutely, a PX would be the best way to approach this. The PX can use the API, which goes through the application server. So there are no issues about what needs to be done as the server knows exactly what to do, and it can hard-delete the prelim items. However, using the API also has the same restrictions as doing it manually, so it should remove the item from the change first, and then hard-delete the item. It can read from an outside text file, so what should be processed is whatever is in the file.

    • 2652 views
    • 4 answers
    • 0 votes
  • You would be far better off creating a table to hold the data you need, and then writing a PL*SQL script to put the data in there. Along the way, it can check for users that haven’t done anything and either directly report them or enter a special record into the table specifically for users that haven’t done anything.
     Start with the ID for all users that are active (select id, loginid, first_name, last_name from agileuser where enabled=1). For each ID value, look in ITEM_HISTORY, ADMIN_HISTORY and CHANGE_HISTORY for the desired time period – the past 2 years in your case (select count(*) from item_history where user_id = UID and timestamp > to_date(’07/11/2016′,’MM/DD/YYYY’) for example). If no records are found in any of those tables, that user hasn’t done anything, and your script can note this.
     If records are found, put them into your table with correct text values. You can then get aggregate stuff from the table you just created (management *loves* statistics) or just dump it out into a spreadsheet. You have the list stuff already in your query, so you know how to do that. Also note that the reason I included the first/last name from AGILEUSER in my main query is that although ITEM/CHANGE_HISTORY both have the full user name values in the table(USER_NAME), ADMIN_HISTORY does not have such an attribute. So it is good to get that from AGILEUSER along with the ID.
     And I am checking all 3 history tables because of you are a change analyst, you may have never modified a part/document field or modified the configuration, but you certainly *did* stuff.  You might also look in USER_USAGE_HISTORY to see if they have even logged in to view stuff (select count(*) from user_usage_history where username = LOGINID).

    This answer accepted by BurhanBKB. on March 28, 2024 Earned 15 points.

    • 2112 views
    • 2 answers
    • 0 votes
  • Agile Angel Asked on July 8, 2016 in Agile PLM (v9).

    A part is something that you (or your company) designs, controls and produces (although you may out-source the manufacturing of it). It is something that usually has it’s design controlled by either a document management system or part lifecycle management system.
     A manufacturer part is usually a part that has it’s parameters specified and controlled by another company or organization, but which you use in making your part/assembly. this can be something specialized (CPU fan), or something very common (nuts and bolts).
     The big difference is that with parts, you control how it is revised because the specifications are controlled by you (or your company). You do not control the specifications for manufacturer parts, you know them and make the decisions to use them in your designs.

    • 1322 views
    • 1 answers
    • 0 votes
  • No, it cannot be disabled. It is considered as essential/almost required for some areas of business that Agile is used in (High Tech, Aerospace/Defense), so it was set up as not being able to make the attribute non-visible.
     That said, you can rename it (to “.”, for example, or “_”) to make it “invisible”, move it to last in the display order, and make sure it is *not* required. You may get a few users who wonder “what the heck is that?”, but most will probably ignore it, and eventually forget about it.

    • 2082 views
    • 2 answers
    • 0 votes
  • It *might* be because of the following : LEFT OUTER JOIN listentry le4 ON le4.entryid = t.LIST03.   Normally, you should always specify the list ID  (i.e. AND le1.parentid = 2249) when getting something from LISTENTRY. And also specifying that ACTIVE in (0,1) and LANGID=0 is a good thing.

     And specifying that “(MB.change_out = 0 AND mb.change_in <> 0)” in your query will only get you data for the latest revision. So trying to get rev data for anything other than the latest rev, you should remove that from the query (and things WILL get much more complicated trying to use CHANGE_IN and CHANGE_OUT, see below).

     I have found that building a table is MUCH easier than trying to put everything together in a single query. I can categorically tell you that that is not how Agile works on the inside, complex queries would slow things down *immensely*. And the application server keeps a good amount of the configuration in memory, for faster conversion of IDs to text values after data has been retrieved from the database. Using PL*SQL, you can set the values for each attribute/chunk of data, in order, with simple/efficient queries. And this also allows not doing anything if the attribute values are null, instead of having a lot of outer joins.

     You have a good start there, but need to be able to add things in easy pieces instead of making what you have more complex (and possibly introducing errors). Create a table to hold the data, convert what you have into adding/updating data values into that table and things should be simpler.

     Note that to correctly use CHANGE_IN/CHANGE_OUT, you need to do something on the order of  “select <attributes> from manu_by where change_in IN (0,<list of chg IDs for current and all previous revs>) and (change_out=0 OR change_out NOT IN (<list of chg IDs for current and all previous revs>) )”.  Build your query and execute using DBMS_SQL.PARSE, and then you can list the AML for every revision of an item, and not just the latest revision.

    • 1814 views
    • 2 answers
    • 0 votes
  • Agile Angel Asked on June 23, 2016 in Agile PLM (v9).

    Are you talking about just changing the file extension in Agile, or actually converting the files from .docx (Office 2007 onward) to .doc (Office 2003)???

     But probably both, so here goes. Converting the file formats would have to be done in the file vault, and probably best done via a script that called MSWord to open each file and and do a save-as using the .doc format. Changing the file extension in Agile, you would want to modify FILES.FILETYPE, which is where just the file extension is stored. The actual file name (FILES.FILENAME) also contains the file extension, so that value would have to be changed as well. And of course, you would then need to eliminate all of the “old” .docx files from your file vault (unless you have lots of disk space) once the “new” files had been created.

     And as Patrick said, *TEST* your process/procedure to do this in your DEV/TEST environment first. And even then, make a full backup of the database *and* file vault before doing this in your production environment. It is far too easy to get yourself where things are so screwed up you may not be able to recover, if you do not plan to fail and take precautions thereof.

    • 1370 views
    • 2 answers
    • 0 votes