mrmathison's Profile
Agile Talent
231
Points

Questions
27

Answers
35

  • Agile Talent Asked on January 7, 2022 in Product Collaboration.

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

    • 88 views
    • 1 answers
    • 0 votes
  • Agile Talent Asked on June 23, 2021 in Agile PLM (v9).

    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);
    
    }
    
    
    • 1547 views
    • 3 answers
    • 0 votes
  • Agile Talent Asked on February 20, 2021 in Software Development Kit (API).

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

    • 432 views
    • 1 answers
    • 0 votes
  • Agile Talent Asked on February 19, 2021 in Agile PLM (v9).

    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;

    • 627 views
    • 2 answers
    • 0 votes
  • Agile Talent Asked on February 11, 2021 in Agile PLM (v9).

    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

     

    • 369 views
    • 1 answers
    • 0 votes
  • Agile Talent Asked on February 3, 2021 in Agile PLM (v9).

    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.

    • 514 views
    • 3 answers
    • 0 votes
  • Agile Talent Asked on December 18, 2020 in Product Collaboration.

    Nevermind.  I found an example in the SDK documentation.

    explodeBOM(obj, pitem, 1);
    private static void explodeBOM(IBaseScriptObj obj, IItem item, int level) throws APIException {
    IRow     row;
    String   bomNumber;
    ITable   table = item.getTable(ItemConstants.TABLE_BOM);
    Iterator it    = table.iterator();
        while (it.hasNext()) {
    row = (IRow)it.next();
    bomNumber = (String)row.getValue(ItemConstants.ATT_BOM_ITEM_NUMBER);
    obj.logMonitor(“level: ” + (String)level + ”  part: ” + bomNumber);
    IItem bomItem = (IItem)row.getReferent();
    explodeBOM(obj, bomItem, level + 1);
    }
    return null;
    }
    • 487 views
    • 1 answers
    • 0 votes
  • Agile Talent Asked on December 14, 2019 in Software Development Kit (API).

    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.

    • 796 views
    • 3 answers
    • 0 votes
  • Agile Talent Asked on October 1, 2019 in Other APIs.

    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.

    • 823 views
    • 3 answers
    • 0 votes
  • Agile Talent Asked on May 1, 2019 in Agile PLM (v9).

    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/

    This answer accepted by stevend17. on May 1, 2019 Earned 15 points.

    • 1170 views
    • 2 answers
    • 0 votes