If all values have the same number of levels (as in, all list values will have something like A|B|C), then you might be able to do it with a single query. It would be a royal PITA, but I think it could be done. Note that the values are stored hierarchically, and that the leaf values are collected under the parent list value.  Using the “A|B|C” example, C would have the ID for B as it’s parent, and B would have the ID for A as it’s parent. But it can get a bit stranger than that, sometimes.

I show a script below (an attachment would be SO much easier) that uses a PL/SQL procedure that you call with the base ID of the list (in the script, it is 2474530). Update that value, save the script, and execute it in your database. If your values can be longer than 120 characters, update “set line 120” to be something larger such that the values do not wrap. Also note that PL/SQL has a 1MB limit on what it will output, so if your values are long or if you have a LOT of values, the script may error out when it hits a MB of output. If that happens, create a table and save the data into it, and then you can query it any way you want. Note that this will give you entire values, and not the “exploded BOM” view that is what you will see in the Java client. But doing that should be fairly easy to with this script.

set serveroutput on size 1000000
set line 120
set echo off

create or replace procedure GetCVal(pid IN number, prfx IN varchar2, peID IN number) IS

leID number;
leVAL varchar2(500);
prntID number;
pentID number;
lcnt integer;

cursor csrGCL is select entryid,entryvalue,parent_entry from listentry
where parentid = prntID and parent_entry = peID and langid=0 order by entryvalue;

begin
— dbms_output.put_line(‘proc=’ || pid || ‘, pent=’ || peID || ‘ with <‘ || prfx || ‘>’);
begin
select id into prntID from listname where parent_list = pid;
— dbms_output.put_line(‘prnt=’ || prntID || ‘ for ‘ || pid);

select count(*) into lcnt from listentry
where parentid = prntID and parent_entry = peID and langid=0;
if lcnt = 0 then
dbms_output.put_line(‘cval=<‘ || prfx || ‘>, ID=’ || peID);
else
open csrGCL;
loop
fetch csrGCL into leID, leVAL, pentID;
exit when csrGCL%NOTFOUND;

— dbms_output.put_line(‘list id=’ || leID || ‘ –> ‘ || leVAL);
if prfx is null then
GetCVal(prntID, leVAL, leID);
else
GetCVal(prntID, prfx || ‘|’ || leVAL, leID);
end if;
end loop;
close csrGCL;
end if;

exception
when others then
dbms_output.put_line(‘cval=<‘ || prfx || ‘>, ID=’ || peID);
end;
end;
/
show err

declare

begin
GetCVal(2474530, null, 0);

end;
/
show err

drop procedure GetCVal;

set echo off

The “Comments” attribute in the History tab cannot be updated by users, to my knowledge. It simply holds additional information concerning the event that the record specifies for the object record. For non-routable objects (documents, parts, users, manufacturers, customers, etc.), it notes when the object was created, modified and referenced by a change (if an item). For a routable object (change, PSR, QCR, Declaration, etc.), it does the above and also notes when changes occur in the workflow of the object (being promoted or demoted, an approver rejection or approval, etc.).

But I have never seen where the field was available for update, for any object.

Okay, that is strange. The HH24:MM:SS should be showing in values because a date value does contain both date and time. And yes, the timestamp values ARE stored in the ITEM_HISTORY table, values from that table are what is displayed in the History tab for an item.

Use the query I provided above, execute it just before running your select statement, and see if things are okay. I use that in many places, and I always see the full date value. And actually, execute “select sysdate from dual” after setting the NLS_DATE_FORMAT value as I indicated above. There is no way that value will not have hours, minutes and seconds.

And that is what I forgot. The default date string does NOT include the time.

Execute the following statement in SQL*Plus or PL/SQL before running your select statement :

alter session set nls_date_format = ‘DD-MON-YYYY HH24:MI:SS’;

This ensures that the hour:minute:second value is also shown from the TIMESTAMP value. Look up NLS_DATE_FORMAT and find all the other myriad ways to display the date and time in your SQL session. You can even make it a default by setting it just before you start SQL.

Ah, back in the good old days of Agile 8.5. Note that ITEM_HISTORY in the 8.5 schema has a field named TIMESTAMP. This should be what you want. But please note that it might not be quite the value you expect.

I can certify that all date and timestamp attribute values in Agile 9.X are stored as GMT values, and then altered for display by the time zone preference for each user so that the user sees the value that they expect. This allows use across international time zones and for folks to be able to better coordinate things. To extract the data from the database would require specifying a common timezone (PST, for example), and then all values would be shown from that timezone.

I am not so certain that the above applies to the Agile 8.5 schema. As I remember it (from over 15 years ago), date and timestamp values were stored per the timezone of the server, and there was no modification of date or timestamp values as users did not have a timezone assigned. So if I was in CST and logged into a server out on the west coast, I would see PST date/timestamp values and *not* CST values.

To get the create date from the database I would use :

select min(timestamp) from item_history where item = (select id from item where item_ number=’XYZ’) and action=8;

The “action=8” says to get the timestamp for when the part was created (and yes, there can be more than 1 record, so I use the MIN function).

If this does not work, what specifically are you trying to do? And which date values can you get?

As I understand it, that is how they should work. A declaration is a statement that, “at this point in time”, the specified parts/mfr parts/part families comply with the indicated specification and for the specified substances. You can also define an expiration date on it, but if not the declaration is open-ended, until something supersedes it. From what I was able to find, declarations are usually considered static once released, but given that this is the real world, I would expect the occasional need to go back and correct things.

Note that the workflow data (steps, next steps, current status, who/when promoted, etc.) for a given declaration (or change) is in the WORKFLOW_PROCESS table. If the manual next step is not present in there (and it will NOT be for any existing declarations), then you experience exactly what you are seeing. Adding a new Manual Next Step will *not* go back and alter existing workflow records. I suspect that with some time and study, I could figure out what is needed, but it would require access to the database you are using instead of a generic one. I need to see what the records for the workflow looked like before and after the change in the Manual Next Step.

AGILEOBJECTIDSEQUENCE has nothing to do with revisions. It is a defined sequence in the database to give EVERY record a unique ID, across the tables it is used for. So it is not reset for every revision, as ALL records in the REV table will have a unique ID, across all parts/documents. It is used for most (but not all) tables in the database.

And no, there is no published documentation for the Agile schema. Never has been, and since they are no longer selling the software, I doubt there ever will be.

Anything linked to a Specification would be in the COMPLIANCE table. Specifications themselves are in the REGULATION table.

BE VERY CAREFUL in doing this. Use the AGILEOBJECTIDSEQUENCE to set your ID value in COMPLIANCE, and make sure you know the ID values of the specification and the substances you wish to add. Look at the other attributes in the table, and figure out what is needed to make it work. TEST it in your test environment first, and know the ID values of the records you added, so if needed, you can remove them later.

Make sure you have things set up correctly for Oracle. The message is basically telling you that Oracle has no idea what you are trying to connect to.

Can you log into the database on the Agile application server using SQL Plus or SQL Developer?? If not, then you need to use the Network Manager (on the application server)  to correctly define the connection string for the database in it’s new location.

 What is the timezone preference of the user that is used by the program to log into Agile??

 Dates and times are stored in the database as GMT, and then translated using the preferences of the user that is viewing the data. Make sure that the timezone preference of the user that the program uses is defined as PST/PDT. This should then work.