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


You could create an Event. There is an Event Type called ‘Update Title Block’.  Although, the documentation says it includes edits to the Title Block as well as Page 2 and Page 3 attributes.
So, let’s say someone made an edit to one of these fields and hit Save. Normally, it also gets recorded in the History tab of that object. There are three things that can be defined to trigger an event and execute some code.  First, there is the Event. Then the Event Handler, which contains the Java Groovy code. Then an Event Subscriber that links them together.
The Groovy code can use the Agile SDK API classes to find information. Like, you may be able to use the IHistoryManager interface to find what recent update was made. Your code could also invoke a Notification which you also defined, to send an email.   If there is an easier way, others can reply here. I don’t have any code to share as I have not written any that looks at an object’s History.  On this web page, click on Admin Guide HTML: https://docs.oracle.com/cd/E83928_04/otn/docset.html   and also the SDK Developer Guide.

I have used Agile reports, like the Where Used Report.  You can search for parts that have something in common.  We have a Page Two attribute that is no longer in use.  So, I populate it with a unique value.  If I have 500 or so part numbers, I create a csv file with two columns; one for the item number and the other column has that special unique value.  I then use the Import tool from the web client to update all 500 parts, mapping that unique value to the special Page Two attribute.  Then, I run the report and tell it to select all items having that unique value.   It is true that the format of the report is ugly, but once you get it into Excel, you can use formulas to clean it up.

In my post above, I forgot to define eventInfo.  Add this:

try {

if (req instanceof IObjectEventInfo) {

eventInfo = (IObjectEventInfo)req;

} else {

obj.logMonitor("req IS NOT AN instanceof IObjectEventInfo");

}
if (eventInfo!=null) {

IDataObject dataObject = eventInfo.getDataObject();

IAgileClass relObjectClass = dataObject.getAgileClass();
:

// This was added to the Actions menu of an Item. Groovy script.

// How to come up with a single, unique list of all components in a BOM, including all levels.
import com.agile.agileDSL.ScriptObj.IBaseScriptObj;

import com.agile.agileDSL.ScriptObj.AgileDSLException;

import com.agile.api.*;

import java.util.*;

import com.agile.px.IEventInfo;

import com.agile.px.EventConstants;

// import com.agile.px.IWFChangeStatusEventInfo;
import com.agile.px.IObjectEventInfo;
import java.util.*;

import java.util.regex.*;

import java.time.*;

import java.time.format.DateTimeFormatter;

import javax.activation.*;
void invokeScript(IBaseScriptObj obj) {
IAgileSession session = obj.getAgileSDKSession();

IEventInfo req = obj.getPXEventInfo();

int v_bomcnt = 0;
try {
IDataObject dataObject = eventInfo.getDataObject();

IAgileClass relObjectClass = dataObject.getAgileClass();
str_class = relObjectClass.getName();

obj.logMonitor("The subclass of the Items class is: " + str_class);
IItem pitem = dataObject;

String partnbr = (String) pitem.getValue(ItemConstants.ATT_TITLE_BLOCK_NUMBER);
// ----- Look at the BOM Table
ITable tblBom = pitem.getTable(ItemConstants.TABLE_BOM);

Iterator bomit = tblBom.iterator();

IRow bomrow = null;
while(bomit.hasNext()) {

bomrow = (IRow)bomit.next();

v_bomcnt = v_bomcnt + 1;

}
if( v_bomcnt > 0 ) {

ArrayList compList = []; // https://docs.oracle.com/javase/7/docs/api/java/util/ArrayList.html

ArrayList compListT = [];
// compList will become an array of every component from every level of the BOM, and as a unique list of item numbers.
compListT = explodeBOM(obj, pitem, 1, compListT);
for( String cmTmpVala : compListT ) {

if ( compList.contains( cmTmpVala )) {

// do not add duplicate value

} else {

compList.add(cmTmpVala);

}

}

// Output to the History tab of your item, a list of unique item numbers that are in the BOM of the top level parent.

for( String abcValue : compList ) {

obj.logMonitor("BOM component: " + abcValue );

}

}

catch (MessagingException ex) {

ex.printStackTrace();

obj.logMonitor(ex.getMessage());

// throw new AgileDSLException(ex);

}

}
//

// ------ explodeBOM ------

//

private static ArrayList explodeBOM(IBaseScriptObj obj, IItem item, int level, ArrayList cmList) throws APIException {
// This will look at the BOM for an item and add the components to an array named cmList.

IRow row;

String bomNumber;

ITable table = item.getTable(ItemConstants.TABLE_BOM);

Iterator it = table.iterator();

int addFlag = 1;
while (it.hasNext()) {

row = (IRow)it.next();

bomNumber = (String)row.getValue(ItemConstants.ATT_BOM_ITEM_NUMBER);

obj.logMonitor("level: " + (String)level + " part: " + bomNumber);
cmList.add(bomNumber);

IItem bomItem = (IItem)row.getReferent();
// This is a recursive routine. It calls itself. This way we can explode the BOM for every level. A full BOM explosion.

// The end result will be a unique list of all the component item numbers, from every level, appended to the array named cmList.

// We may end up with duplicate part numbers added to the array, but we will remove the dups later
cmList = explodeBOM(obj, bomItem, level + 1, cmList); // recursive call, add 1 to go to next BOM level

}

return (cmList);

}

I had a similar issue, where data from a ‘Save As’ did not get cleared.  But we decided to do it a different way.  The users were ok with this idea.  Clear a couple of dates on every Affected Item when the ECO went from Pending to Submitted.  This is the script.

// Purpose: Clear the Affected Items 'Effective Date' and 'Obsolete Date' when ECO status goes from Pending to Submitted.
// Event Subscriber: Event Type is 'Change Status for Workflow'. Object Type is 'ECO'
// Event: Workflow: specify the Workflow name. Object Type: 'ECO'. Status - From: 'Pending'. Status - To: 'Submitted'
// Event Handler Type: Script PX, groovy script, using Agile SDK
// -------------------------------------------

import com.agile.agileDSL.ScriptObj.IBaseScriptObj;
import com.agile.agileDSL.ScriptObj.AgileDSLException;
import com.agile.api.*;
import java.util.*;
import com.agile.px.IEventInfo;
import com.agile.px.EventConstants;
import com.agile.px.IWFChangeStatusEventInfo;

void invokeScript(IBaseScriptObj obj) {

IAgileSession msession = obj.getAgileSDKSession();
IEventInfo req = obj.getPXEventInfo();
IWFChangeStatusEventInfo e = null;

try {
  if (req instanceof IWFChangeStatusEventInfo) {
    eventInfo = (IWFChangeStatusEventInfo)req;
  }

// obj.logMonitor( "Chg nbr: " + obj.getObjectNumber());

  IChange pchange = (IChange) msession.getObject(IChange.OBJECT_TYPE, obj.getObjectNumber());

// obj.logMonitor( "ECO: " + (String) pchange.getValue(ChangeConstants.ATT_COVER_PAGE_NUMBER));

  ITable ai = pchange.getTable(ChangeConstants.TABLE_AFFECTEDITEMS);
  Iterator it = ai.iterator();
  IRow row = null;

  while(it.hasNext()) {
    row = (IRow)it.next();
    String m_eff_date = (String) row.getValue(ChangeConstants.ATT_AFFECTED_ITEMS_EFFECTIVE_DATE);
    if ( m_eff_date != null ) {
// obj.logMonitor( "not null Eff Date: " + m_eff_date );
      row.setValue(ChangeConstants.ATT_AFFECTED_ITEMS_EFFECTIVE_DATE, null);
    }

    String m_obs_date = (String) row.getValue(ChangeConstants.ATT_AFFECTED_ITEMS_OBSOLETE_DATE);
    if ( m_obs_date != null ) {
// obj.logMonitor( "not null Obs Date: " + m_obs_date );
      row.setValue(ChangeConstants.ATT_AFFECTED_ITEMS_OBSOLETE_DATE, null);
    }
  }

} catch (APIException ex) {
ex.printStackTrace();
obj.logMonitor(v_progress + ex.getMessage());
}
}

-- First part of select is for individual files on the attach tab (not folders)
SELECT
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000)
then (select change.change_number from agile.change where agile.ATTACHMENT_MAP.PARENT_ID=change.id)
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select item.item_number from agile.item where agile.ATTACHMENT_MAP.PARENT_ID=item.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1442)
then (select manufacturers.name from agile.manufacturers where agile.ATTACHMENT_MAP.PARENT_ID=manufacturers.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1483)
then (select manu_parts.part_number from agile.manu_parts where agile.ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id)
else
null end as Obj_Number,
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select REV.REV_NUMBER from agile.REV where REV.ITEM = agile.ATTACHMENT_MAP.PARENT_ID AND REV.CHANGE = ATTACHMENT_MAP.PARENT_ID2)
WHEN ATTACHMENT_MAP.PARENT_CLASS in (1483)
THEN ( select name from agile.manufacturers where id =
(select manu_id from agile.manu_parts where agile.ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id ) )
ELSE
NULL END AS REV_NBR,
NODETABLE.DESCRIPTION as Obj_Class,
ATTACHMENT.ATTACHMENT_NUMBER,
VERSION_FILE_MAP.DESCRIPTION,
ATTACHMENT.CREATE_DATE AS Att_Created_Date,
ATTACHMENT.ID AS ATT_ID,
VERSION.ID AS VER_ID,
VERSION.VERSION_NUM,
ATTACHMENT_MAP.LATEST_VSN,
FILES.FILENAME,
FILES.FILE_TYPE,
FILES.FILE_SIZE,
FILES.CREATED AS FILE_CREATED,
FILES.LAST_UPD AS FILE_LAST_UPD,
FILES.ID as FILE_ID,
FILE_INFO.IFS_FILEPATH,
FILE_INFO.HFS_FILEPATH
FROM agile.ATTACHMENT
INNER JOIN agile.VERSION
ON VERSION.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.VERSION_FILE_MAP
ON VERSION_FILE_MAP.VERSION_ID = VERSION.ID
INNER JOIN agile.FILES
ON VERSION_FILE_MAP.FILE_ID = FILES.ID
INNER JOIN agile.ATTACHMENT_MAP
ON ATTACHMENT_MAP.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.NODETABLE
ON NODETABLE.ID = ATTACHMENT_MAP.PARENT_CLASS
LEFT OUTER JOIN agile.FILE_INFO
ON FILE_INFO.FILE_ID = FILES.ID
WHERE ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000,9000,10000,1442,1483)
AND VERSION.ID = ATTACHMENT_MAP.LATEST_VSN
AND ATTACHMENT.DELETE_FLAG IS NULL
AND ATTACHMENT_MAP.FILE_ID > 0 -- Include this line for when it is one specific file on the attach tab, not a whole folder
AND VERSION_FILE_MAP.FILE_ID = ATTACHMENT_MAP.FILE_ID -- Include this line for when it is one specific file on the attach tab, not a whole folder
UNION ALL
-- Second part of select is for whole folders that exist on attach tab
SELECT
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000)
then (select change.change_number from agile.change where ATTACHMENT_MAP.PARENT_ID=change.id)
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select item.item_number from agile.item where ATTACHMENT_MAP.PARENT_ID=item.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1442)
then (select manufacturers.name from agile.manufacturers where ATTACHMENT_MAP.PARENT_ID=manufacturers.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1483)
then (select manu_parts.part_number from agile.manu_parts where ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id)
else
null end as Obj_Number,
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select REV.REV_NUMBER from agile.REV where REV.ITEM = ATTACHMENT_MAP.PARENT_ID AND REV.CHANGE = ATTACHMENT_MAP.PARENT_ID2)
WHEN ATTACHMENT_MAP.PARENT_CLASS in (1483)
THEN ( select name from agile.manufacturers where id =
(select manu_id from agile.manu_parts where ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id ) )
ELSE
NULL END AS REV_NBR,
NODETABLE.DESCRIPTION as Obj_Class,
ATTACHMENT.ATTACHMENT_NUMBER,
VERSION_FILE_MAP.DESCRIPTION,
ATTACHMENT.CREATE_DATE AS Att_Created_Date,
ATTACHMENT.ID AS ATT_ID,
VERSION.ID AS VER_ID,
VERSION.VERSION_NUM,
ATTACHMENT_MAP.LATEST_VSN,
FILES.FILENAME,
FILES.FILE_TYPE,
FILES.FILE_SIZE,
FILES.CREATED AS FILE_CREATED,
FILES.LAST_UPD AS FILE_LAST_UPD,
FILES.ID as FILE_ID,
FILE_INFO.IFS_FILEPATH,
FILE_INFO.HFS_FILEPATH
FROM agile.ATTACHMENT
INNER JOIN agile.VERSION
ON VERSION.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.VERSION_FILE_MAP
ON VERSION_FILE_MAP.VERSION_ID = VERSION.ID
INNER JOIN agile.FILES
ON VERSION_FILE_MAP.FILE_ID = FILES.ID
INNER JOIN agile.ATTACHMENT_MAP
ON ATTACHMENT_MAP.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.NODETABLE
ON NODETABLE.ID = ATTACHMENT_MAP.PARENT_CLASS
LEFT OUTER JOIN agile.FILE_INFO
ON FILE_INFO.FILE_ID = FILES.ID
WHERE ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000,9000,10000,1442,1483)
AND VERSION.ID = ATTACHMENT_MAP.LATEST_VSN
AND ATTACHMENT.DELETE_FLAG IS NULL
AND ATTACHMENT_MAP.file_id = 0 -- Include this line for when it IS a whole folder on the attach tab

Thanks for the tips. I created a PX script in the Event Handler and only enabled the Event Subscriber so I could run it once, then disable it.  Here is the script that worked.

// Purpose: Soft delete multiple items

// Agile PLM Groovy script, using Agile SDK

// For a bunch of parts, delete them in Agile. But only delete once because doing it 2nd

// time will permenantly delete it. To permanently delete, log in to Agile, go to the

// Recycle Bin, sort by item description, find those and select that begin with "del",

// and permanently delete multiple items from there.

//

// 1. Only do this for Item Lifecycle Phase = 'Preliminary'

// 2. pitem.isDeleted() should be false, meaning only delete if it has not been marked as deleted yet

// 3. pitem.delete(); delete item and put in Recycle bin

// 4. Append "del " to front of item description so we can find these easy in the recycle bin

// 4.1 Remove last 4 chars from item description before adding "del " to avoid going over char limit
import com.agile.agileDSL.ScriptObj.IBaseScriptObj;

import com.agile.agileDSL.ScriptObj.AgileDSLException;

import com.agile.api.*;

import java.util.*;

import com.agile.px.IEventInfo;

import com.agile.px.EventConstants;

import com.agile.px.IObjectEventInfo;
import java.util.*;

import java.util.regex.*;
void invokeScript(IBaseScriptObj obj) {
IAgileSession session = obj.getAgileSDKSession();

IEventInfo req = obj.getPXEventInfo();

Integer del_cnt = 0;
try {
if (req instanceof IObjectEventInfo) {

eventInfo = (IObjectEventInfo)req;

} else {

obj.logMonitor("req IS NOT AN instanceof IObjectEventInfo");

}
if (eventInfo!=null) {
del_cnt = delItem(obj, session, "09.4761V.001", del_cnt);

del_cnt = delItem(obj, session, "21.I1021.003", del_cnt);

del_cnt = delItem(obj, session, "21.M0630.L01", del_cnt);
// ---- Add more items above, that you wish to flag as deleted and move into Recycle Bin ----
obj.logMonitor("nbr of items flagged as deleted: " + del_cnt);

}
} catch (APIException ex) {
ex.printStackTrace();

obj.logMonitor(ex.getMessage());

}

}
//

// --------- delete item ---------

//

private static int delItem(IBaseScriptObj obj, IAgileSession session, String itemnbr, Integer delcnt) throws APIException {
IItem pitem = (IItem) session.getObject(ItemConstants.CLASS_ITEM_BASE_CLASS, itemnbr);

String lifecyc = (String) pitem.getValue(ItemConstants.ATT_TITLE_BLOCK_LIFECYCLE_PHASE);
String itemdesc = (String) pitem.getValue(ItemConstants.ATT_TITLE_BLOCK_DESCRIPTION);

itemdesc = itemdesc.reverse(); // reverse all the characters, ex: ABCDEFG becomes GFEDCBA

itemdesc = itemdesc.drop(4); // remove the first 4 chars, ex: GFEDCBA becomes CBA

itemdesc = itemdesc.reverse(); // reverse it again, ex: CBA becomes ABC

itemdesc = "del " + itemdesc;
if ( lifecyc == 'Preliminary' ) {

if (!pitem.isDeleted()) {

pitem.setValue(ItemConstants.ATT_TITLE_BLOCK_DESCRIPTION, itemdesc);

pitem.delete();

delcnt = delcnt + 1;

}

}

return (delcnt);

}

I wanted to give an update here, now that I have more or less solved my issue.
The MultiList03 attribute on the item is configured in our system to use a list.
And the list it uses is the Agile ‘User Groups’ list, which is considered a dynamic list.
A simple list is easier to deal with. Let’s say I have a User Group named “Texas”, and another named “Arizona”.
Below is an example of some code that shows how I can replace the current values, with “Texas” and “Arizona”.
This is Groovy code added to the code section of an Event Handler within Agile PLM 936.

// MultiList03 uses the ‘User Groups’ list, which is a dynamic list in Agile, not a simple list
// item is of the type IItem, which we already defined before getting to this point

ICell cell = item.getCell(CommonConstants.ATT_PAGE_TWO_MULTILIST03);
IAgileList values = cell.getAvailableValues();
if (values.getChildNodes() == null) {
IAgileClass cls = cell.getAttribute().getListAgileClass();
obj.logMonitor(“cls: ” + cls); // <– output is — cls: User Groups
if (cls != null) {

// *** I plan to have “Texas” and “Arizona” in a String array, so I will do this differently in a loop
// *** I won’t hard-code these values, because there could be 1 or more. For example: “Texas”, “Ohio”, and “Florida”

Object[] usrGrps = [ (IUserGroup)session.getObject(cls, “Texas”)]; // <– this will end up being an array of objects
usrGrps += [ (IUserGroup)session.getObject(cls, “Arizona”)]; // <– the += helped me here

values.setSelection(usrGrps);
cell.setValue(values); // <– this worked and added the two values
}
}

This select has worked for me, but I don’t think it gets the Mfr info.

v_id NUMBER(10,0) := NULL;
v_class NUMBER(10,0) := NULL;
v_parent VARCHAR2(300 BYTE) := NULL;
v_compo VARCHAR2(300 BYTE) := NULL;
x_id NUMBER(10,0) := NULL;
x_class NUMBER(10,0) := NULL;

— v_id := the id from the item table
— v_class := the class number from the item table

— at the bottom of this select, you will see the nth level is set to 7

SELECT
f.ITEM_ID AS BOM_ITEM_ID
, f.item AS PARENT_ID
, f.component AS COMPONENT_ID
— 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
— , ( select entryvalue from listentry where parentid = 2190144 and langid = 0 and entryid = ( select number from agile_flex where id = f.item and class in ( 9000, 10000 ) and attid = 2198312 )) as XIQ_COMPAT
— 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 AS COMPONENT_NUMBER
, f.FIND_NUMBER
, f.QUANTITY
— ( 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
, f.notes
, f.text05
, f.minimum_number
, f.maximum_number
, f.is_mutually_exclusive
, f.is_optional
— , ( select entryvalue from agile.listentry where parentid = 2196903 and entryid = f.list01 ) as prim_comp_of_bndl
, f.text01 — revenue natural acct
, f.text02 — revenue split percentage
, f.latest_released_eco
FROM
( SELECT
e.id AS ITEM_ID
, e.item
, e.ITEM_NUMBER
, e.FIND_NUMBER
, e.QUANTITY
, e.component
, 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.notes
, e.text05
, e.minimum_number
, e.maximum_number
, e.is_mutually_exclusive
, e.is_optional
, e.list01
, e.text01
, e.text02
, 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.CREATED
, b.LAST_UPD
, b.notes
, b.text05
, b.minimum_number
, b.maximum_number
, b.is_mutually_exclusive
, b.is_optional
, b.list01
, b.text01
, b.text02
, 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
—- Change level <= 1 if you just want the top level
CONNECT BY NOCYCLE PRIOR e.COMPONENT = e.ITEM and level <= 7
START WITH e.ITEM = (select x.id from agile.item x where x.id = v_id AND x.class = v_class)
) f;