As noted by stevend17, AGILEOBJECTIDSEQUENCE was used to generate the ID for all tables in the database. For everything up to and including Agile 6.x, 7.x, 8.x and 9.0.x.

Somewhere in there (9.2.x?, 9.3.x?), they introduced ITEM_HISTORY_ID_SEQ, which is only used for the ITEM_HISTORY table. It starts at  -2147000000 and goes up to +2147000000. So it gives a lot more range and is used exclusively for the Item history table. CHANGE_HISTORY_ID_SEQ works the same way for the CHANGE_HISTORY table.

Hoo boy, you aren’t asking for much, are you?? I would venture that it cannot be done as “one query result”. As a PL/SQL procedure, certainly. But it would most likely not be a single procedure but a group of procedures and functions.

Anyway, you would have to go through all of the ECO revisions of the assembly, and for each one, go out and find the component parts that are in the BOM for that specific assembly revision. And then for each component part in the assembly revision, you would have to get the component revision that was released most-recently previous to it (or that is how I *think* it always works – the reason I am here is because I need to find out if there is a configuration setting for that).

Given that the query for each assembly revision in the BOM looks like “select item_number, find_number, quantity from BOM where item=XYZ and change_in IN (0,<list of change IDs for all processed assembly revisions>) and (change_out = 0 OR change_out NOT IN (<list of change IDs for all processed assembly revisions>) )”, you have to *build* the query for each assembly revision.

No matter how you try it, not an easy task.

Based on a script I created years ago to correct sequence values after cloning from a production environment to a dev or test environment, numerous tables use the same sequence named AGILEOBJECTIDSEQUENCE. This includes ITEM_HISTORY.

We may not share the same version of Agile, because I cannot find “Complaints” as a class. If indeed Complaints is a class, then you will narrow it down to the subclass in order to get to the Page three attributes. In general however, multitext values are stored in the AGILE.AGILE_FLEX table. They must be referenced by the object’s ID and the attributes base ID. Here’s an example of a sub-query:

(Select Text From Agile.Agile_Flex Where Id = I.Id And Attid = 1568)

Where I.ID is the object’s ID and 1568 can be replaced with the base ID that you’re interested in.

Hope that helps a little. By the way, I doubt that Affected Items will be found in a multitext field unless that is just a term you’re using for this Complaints class. Typically Affected Items are found in the REV table on Change orders or Change Requests.  Attachments are also not in multitext attributes, but again… I’m not familiar with that class.

Svevis,

I’m not certain I can help or not, but to even start I have some questions. What is the input to this query? Are you starting with an item as the parent and providing a specific rev number? Then you want to see all its components at the time and what their rev values were?  Or are you wanting to just provide the parent number, and show ALL revs in one query result? Is this an exploded BOM you’re looking for or just top level under the parent item?

Steve

Rushkar Technology pride itself on being a pioneer in the software development industry. The goal of this software development company Toronto is to design and develop high-quality software products. To do so, the company offers various development services that cater to different project requirements – regardless of how small or big. Their software developers Toronto is equipped and experienced to work with all kinds of development projects, making Rushkar Technology the ideal partner for your upcoming software endeavor.

Clint, I see there’s still no answers on this. Did you find a way to do this? If not, I have questions. First, are you looking to just do a one-time operation of many parts, or are you looking to make a special version of Save-As to do this as needed, part by part? If you are looking to just recreate new versions of the parts, but with a prefix, you might just try exporting all the parts of interest, with all the applicable attributes, then update their part numbers in the excel file to include the prefix, then import them as new parts. Only you might lose is the info in the item’s history that indicates they were saved from another part.

This sounds like an issue with the File Folder Auto Number config.  It may have been reset and is attempting to create File Folder numbers that already exist.  I’d start by changing the File Folder Auto number config with a new number syntax or setting the starting number to a number that doesn’t exist yet.

Here you go below.

https://github.com/vkeygarg/AgilePLM/blob/master/WSX_Agile_Rest_Calls/src/main/java/com/x/agile/plm/rest/service/AgilePLMServiceImpl.java

Replace ‘MY-PART-NUMBER’ at the bottom of this query with your part number.  BOM Notes is the NOTES column.

SELECT
f.ITEM_ID,
f.item,
f.component,
f.root_item_id,
f.CYCLE,
f.LVL,
f.PATH,
( select item_number from AGILE.ITEM g where g.id = f.item ) as PARENT_ITEM_NUMBER,
f.latest_released_eco AS PARENT_LATEST_REL_ECO_ID,
( select h.change_number from agile.change h where h.id = f.latest_released_eco ) AS PARENT_ECO_NUMBER,
( select o.rev_number from agile.rev o where o.item = f.item and o.change = ( select o.change from agile.rev o where o.item = f.item and o.latest_flag = 1 and o.released = 1 ) and o.latest_flag = 1 and o.released = 1 ) as parent_latest_rev,
( select o.release_date from agile.rev o where o.item = f.item and o.change = ( select o.change from agile.rev o where o.item = f.item and o.latest_flag = 1 and o.released = 1 ) and o.latest_flag = 1 and o.released = 1 ) as parent_latest_rel_dt,
( select description from AGILE.ITEM g where g.id = f.item ) as PARENT_DESCRIPTION,
f.ITEM_NUMBER,
f.FIND_NUMBER,
f.QUANTITY,
f.NOTES,
( select o.change from agile.rev o where o.item = f.component and o.latest_flag = 1 and o.released = 1 ) as comp_latest_rel_eco_id,
( select h.change_number from agile.change h where h.id = ( select o.change from agile.rev o where o.item = f.component and o.latest_flag = 1 and o.released = 1 ) ) AS comp_latest_eco,
( select o.rev_number from agile.rev o where o.item = f.component and o.latest_flag = 1 and o.released = 1 ) as comp_latest_rev,
( select o.release_date from agile.rev o where o.item = f.component and o.latest_flag = 1 and o.released = 1 ) as comp_latest_rel_dt,
( select description from agile.nodetable where id = ( select release_type from agile.rev where item = f.component and latest_flag = 1 and released = 1 ) and parentid = 1514 ) as comp_lifecycle_phase,
( select class from agile.item where id = f.component ) as COMP_CLASS_ID,
( select subclass from agile.item where id = f.component ) as COMP_SUBCLASS_ID,
( select description from agile.nodetable where id = ( select subclass from agile.item where id = f.component ) ) as COMP_SUBCLASS,
( select category from agile.item where id = f.component ) as COMP_CATEGORY_ID,
( decode(
(select class from agile.item where id = f.component),
10000,(select entryvalue from agile.listentry where parentid = 311 and entryid = ( select category from agile.item where id = f.component ) ),
9000,(select entryvalue from agile.listentry where parentid = 321 and entryid = ( select category from agile.item where id = f.component ) )
) ) as COMP_CATEGORY,
( select description from AGILE.ITEM g where g.id = f.component ) as COMPONENT_DESCRIPTION
FROM
( SELECT
e.ID AS ITEM_ID,
e.ITEM,
e.ITEM_NUMBER,
e.FIND_NUMBER,
e.QUANTITY,
e.COMPONENT,
e.NOTES,
connect_by_root e.COMPONENT as root_item_id,
CONNECT_BY_ISCYCLE AS CYCLE, -- "Cycle",
level AS LVL,
SYS_CONNECT_BY_PATH(e.COMPONENT, '/') AS PATH, -- "PATH"
e.latest_released_eco
FROM
( SELECT
b.rowid bom_rowid
, c1.rowid c1_rowid
, c2.rowid c2_rowid
, i.rowid i_rowid
, b.ID
, b.ITEM
, b.ITEM_NUMBER
, b.FIND_NUMBER
, b.QUANTITY
, b.DESCRIPTION
, b.CHANGE_IN
, b.CHANGE_OUT
, b.COMPONENT
, b.NOTES
, b.CREATED
, b.LAST_UPD
, i.latest_released_eco
FROM AGILE.BOM b
, AGILE.CHANGE c1
, AGILE.CHANGE c2
, AGILE.ITEM i
WHERE b.change_in = c1.id(+)
AND b.change_out = c2.id(+)
AND (b.change_in=0 OR c1.release_date IS NOT NULL)
AND (b.change_out=0 OR c2.release_date IS NULL)
AND i.id = b.item
AND i.latest_released_eco != 0 ) e
CONNECT BY NOCYCLE PRIOR e.COMPONENT = e.ITEM and level <= 7
START WITH e.ITEM = (select x.id from agile.item x where x.item_number = 'MY-PART-NUMBER')
) f