-- 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;

In this Oracle docum, scroll down to section 5.7.3.  But, I have not personally tried it.

https://docs.oracle.com/cd/E50306_29/otn/pdf/integration/html_agaap/output/chapter_5.htm

I agree with Raj that the user refresh can happen automatically with LDAP.  I also set up an event that runs once a day and looks for any new users created in Agile, makes them Active and also adds them to a User Group.  I can send you that Groovy script if you wish.  Email me at mathison at hotmail dot com.

Nevermind.  I found an example in the SDK documentation.

Thank you Raj and Kevin.  I added more roles to the Event Handler and then it worked.  And I also needed to change  IAttribute  to  String .  I had found that code somewhere, maybe in one of these forums.  When a user gets created from LDAP, it is automatically giving it the Roles ‘Creator can read and discover object he or she created’ and ‘My User Profile’.  Now, I also want to update the User Category to ‘Concurrent’ and also add the user to a User Group.

For our Agile system, internally, the dates on the database records are always stored in GMT time zone.  So, if you are in Agile, in US Eastern time, and you save a date and time at 11pm on Sep 30, the value in the database record will say 4am (or 5am) on Oct 1.  GMT is 4 or 5 hours ahead of EST.   Your personal user settings time zone  decide how the date is displayed when you look at it in Agile.

I’m not sure if this will help your privilege issue, but I had a question about Groovy, and it is working.  This was my post:  https://myagileplm.com/questions/groovy-script-to-update-an-item/