How to get level/depth of items returned from an GetItemRepeatConfig AML query?

Hi community,

would be happy if someone can help me with this one:

I have a GetItemRepeatConfig query over a multilevel Part/BOM structure. This query returns all Parts that appear on the various BOM-levels. Depending on the Part, I have around 3-8 levels. 

I can later get the Parts with an Xpath filter. But right now it wasn´t possible for me, do detect the "level" at which one any of the Parts appear in the BOM. Is there any standard query in Aras, that can also return the depth of an element?

Example:

0     Part     1234    My Product
  1   Part     2345    My Sub-Assy
    2 Part     2346    Screw
  1   Part     2345    My Sub-Assy 2

I discovered that QueryDefinitons can return a "depth" attribute. But there are a couple of extra Methods required. It´s a complete overkill solutions and I want to avoid the QueryDefinition.

Does any one know simple way to get the level? Or at least to calculate it from the AML result?

Thanks again!

Angela

 

Parents
  • Angela,

    The Multilevel BOM Report Stylesheet shows a way to separate the items based on the level.  Not sure if that is what you are looking for or not.

    Nate

  • Hi Nathan,

    thanks for your answer! The idea of using QueryDefinition was based on the Multilevel BOM Report.

    I so far discovered we have two variants of the Multilevel BOM Reports:

    1. The Innovator 11 variant where everything was solved with XSL/XSLT
    2. The Innovator 12 variant which mixes XSL/XSLT, QueryDefinition and a few additional Methods to build the Report. 

    In Innovator 12 key element is PE_ExecuteQdReportMethod . It calculates depth and levels, but it´s hardcoded to certain reports. And I don´t want to use 400 lines of code just to get a simple "level" information within a regular AML query.

    I wonder if the Innovator 11 variant is easier to reuse. My problem: I don´t understand the Stylesheet and XSL stuff and I don´t know how to reuse the result in a Method.

    One other totally different variant would not to use an AML query, but an SQL CTE function. This is how MPP calculates levels.

    So I right now have many variants, and all of them are just super hard to do. I am still searching for the holy grail....

  • 0 オフライン in reply to AngelaIp

    Angela,

    Here is the code for the Multilevel BOM Report (v11).  The repeatTimes statement tells it how many times to go through the process.

    AML

    <Item type="Part" action="GetItemRepeatConfig" select="item_number,name" id="{@id}">
    <Relationships>
    <Item type="Part AML" select="state,related_id(item_number,manufacturer)"/>
    <Item type="Part BOM" select="sort_order,quantity,related_id" repeatProp="related_id" repeatTimes="6"/>
    </Relationships>
    </Item>

    The Stylesheet (XML) side is something I have had problems with too.  There isn't any good info on how to structure it for Aras.  I have had to do a lot of searching and deconstructing what was already in my system to make things work.  This shows the use of $Depth to test for information.  You should be able to use this to test for the depth and assign a style to give each depth something different, or put it in a different column.

    Stylesheet (XML)

    <xsl:stylesheet xmlns:xsl="">www.w3.org/.../Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:exslt="">http://exslt.org/common" xmlns:aras="">http://www.aras-corp.com" version="1.0" extension-element-prefixes="exslt">
    <xsl:output method="html" omit-xml-declaration="yes" standalone="yes" indent="yes"/>
    <xsl:template match="/">
    <html>
    <head/>
    <style type="text/css" userData="Global">
    table {empty-cells:show; border-collapse:collapse;}
    th {font-family:helvetica; font-size:8pt; padding:2px; border:1px #000000 solid; background-color:#CCCCCC; text-transform:capitalize;}
    td {font-family:helvetica; font-size:8pt; padding:2px; border:1px #000000 solid;}
    td.noBorder {font-family:helvetica; font-size:8pt; padding:2px; border-width:0;}
    </style>
    <body topmargin="50" leftmargin="50">
    <table border="0" cellspacing="0" cellpadding="0" width="650">
    <tr valign="top">
    <td class="noBorder" align="left" colspan="9">
    <img src="../imagesLegacy/Logos/aras_logo.gif" height="80"/>
    </td>
    </tr>
    <tr valign="bottom">
    <td class="noBorder" colspan="8" style="font-family:helvetica;font-size:15pt;color:#DA1943;padding:2px;" align="left">Bill of Materials Report</td>
    <td class="noBorder" colspan="4" style="font-family:helvetica;font-size:10pt;padding:2px;" align="right">
    Generated on: <script>function m00(r, n){r += ""; if (!n) n = 2; while(r.length &lt; n){r = "0" + r;} return r;} var dt = new Date(); var a = top.opener.top.aras; if (a){var s = m00(dt.getUTCFullYear(),4)+"-"+m00((dt.getUTCMonth()+1))+"-"+m00(dt.getUTCDate())+"T"+m00(dt.getUTCHours())+":"+m00(dt.getUTCMinutes())+":"+m00(dt.getUTCSeconds())+"+0000"; s = a.convertToNeutral(s, "date", "yyyy-MM-ddTHH:mm:sszzz"); s = a.convertFromNeutral(s, "date", "short_date"); document.write(s);}</script>
    </td>
    </tr>
    <tr>
    <th colspan="6">Indenture Level</th>
    <th>Part Number</th>
    <th>Name</th>
    <th>Quantity</th>
    <th>AML Status</th>
    <th>Manufacturer</th>
    <th>Manufacturer Part</th>
    </tr>
    <xsl:call-template name="rootItems"/>
    </table>
    </body>
    <script src="../../javascript/PopupMenu.js"/>
    </html>
    </xsl:template>
    <xsl:template name="rootItems">
    <xsl:apply-templates select="//Result/Item[@type='Part']">
    <xsl:with-param name="Depth" select="0"/>
    </xsl:apply-templates>
    </xsl:template>
    <xsl:template name="getChildItem">
    <xsl:param name="Depth"/>
    <xsl:if test="$Depth!=5">
    <xsl:apply-templates select="Relationships/Item/related_id/Item[@type='Part']">
    <xsl:with-param name="Depth" select="$Depth + 1"/>
    </xsl:apply-templates>
    </xsl:if>
    </xsl:template>
    <xsl:template match="Item[@type='Part']">
    <xsl:param name="Depth"/>
    <xsl:variable name="rowCount">
    <xsl:choose>
    <xsl:when test="count(Relationships/Item[@type='Part AML'])=0">1</xsl:when>
    <xsl:otherwise>
    <xsl:value-of select="count(Relationships/Item[@type='Part AML'])"/>
    </xsl:otherwise>
    </xsl:choose>
    </xsl:variable>
    <tr>
    <td rowspan="{$rowCount}" width="20px" align="center">
    <xsl:if test="$Depth=0">0</xsl:if>
    </td>
    <td rowspan="{$rowCount}" width="20px" align="center">
    <xsl:if test="$Depth=1">1</xsl:if>
    </td>
    <td rowspan="{$rowCount}" width="20px" align="center">
    <xsl:if test="$Depth=2">2</xsl:if>
    </td>
    <td rowspan="{$rowCount}" width="20px" align="center">
    <xsl:if test="$Depth=3">3</xsl:if>
    </td>
    <td rowspan="{$rowCount}" width="20px" align="center">
    <xsl:if test="$Depth=4">4</xsl:if>
    </td>
    <td rowspan="{$rowCount}" width="20px" align="center">
    <xsl:if test="$Depth=5">5</xsl:if>
    </td>
    <td rowspan="{$rowCount}" width="120px">
    <xsl:value-of select="item_number"/>
    </td>
    <td rowspan="{$rowCount}" width="120px">
    <xsl:value-of select="name"/>
    </td>
    <td rowspan="{$rowCount}" width="80px" align="center">
    <xsl:choose>
    <xsl:when test="$Depth=0">1</xsl:when>
    <xsl:when test="$Depth!=0 and (../../quantity)=''">1</xsl:when>
    <xsl:otherwise>
    <xsl:value-of select="../../quantity"/>
    </xsl:otherwise>
    </xsl:choose>
    </td>
    <td width="100px">
    <xsl:value-of select="Relationships/Item[@type='Part AML'][1]/state"/>
    </td>
    <td width="120px">
    <xsl:value-of select="Relationships/Item[@type='Part AML'][1]/related_id/Item/manufacturer/@keyed_name"/>
    </td>
    <td width="120px">
    <xsl:value-of select="Relationships/Item[@type='Part AML'][1]/related_id/Item/item_number"/>
    </td>
    </tr>
    <xsl:apply-templates select="Relationships/Item[@type='Part AML'][position()!=1]"/>
    <xsl:if test="$Depth!=5">
    <xsl:variable name="itemIdCount" select="count(Relationships/Item/related_id[not(child::Item)])"/>
    <xsl:if test="$itemIdCount != 0">
    <xsl:variable name="idForEach" select="Relationships/Item/related_id[not(child::Item)]"/>
    <xsl:for-each select="$idForEach">
    <xsl:variable name="tmpId" select="text()[1]"/>
    <xsl:choose>
    <xsl:when test="function-available('exslt:node-set')">
    <xsl:apply-templates select="//Item[@type='Part' and @id=exslt:node-set($tmpId)]">
    <xsl:with-param name="Depth" select="$Depth + 1"/>
    </xsl:apply-templates>
    </xsl:when>
    <xsl:when test="function-available('msxsl:node-set')">
    <xsl:apply-templates select="//Item[@type='Part' and @id=msxsl:node-set($tmpId)]">
    <xsl:with-param name="Depth" select="$Depth + 1"/>
    </xsl:apply-templates>
    </xsl:when>
    </xsl:choose>
    </xsl:for-each>
    </xsl:if>
    </xsl:if>
    <xsl:call-template name="getChildItem">
    <xsl:with-param name="Depth" select="$Depth"/>
    </xsl:call-template>
    </xsl:template>
    <xsl:template match="Item[@type='Part AML']">
    <tr>
    <td width="100px">
    <xsl:value-of select="state"/>
    </td>
    <td width="120px">
    <xsl:value-of select="related_id/Item/manufacturer/@keyed_name"/>
    </td>
    <td width="120px">
    <xsl:value-of select="related_id/Item/item_number"/>
    </td>
    </tr>
    </xsl:template>
    </xsl:stylesheet>
  • Thanks for digging out the Innovator 11 variant! 

    The GetItemRepeatConfig query is similar to my current start point. 

    Maybe we can mix the Innovator 11 stylesheet with the Innovator 12 approach as seen in PE_execMultilevelRepAndApplyXslt. 

    This Method applies a stylesheet to a QueryDefintiion Result to build the report.

    I want to avoid the QueryDefinition. But maybe I can apply the stylesheet to the GetItemRepeatConfig result in a similiar way. Something like this:

    const res = aras.createXMLDocument();
       res.loadXML(results);

       results = aras.applyXsltString(res, xsl_stylesheet);
    }

    If would allow me to then work with the "result" after the transformation of the data. Not sure if it will work.

Reply Children
No Data