<Definition targetRelease="9.3.27875.554" createdOn="2014-11-15T22:01:00Z" createdBy="Active Sensing, Inc." createdFor="Export Visio BOM" >
  <dataquery parameterized="true" xmlns="http://www.plmx.org/DataQuery.xsd">
    <choice>
      <sqlquery>
      <sql><![CDATA[
        -- configuration options
        DECLARE @MaxLvl int = 4;     -- maximum number of levels displayed
        DECLARE @NameLimit int = 35; -- max length of part name to fit Visio box
        DECLARE @IdLength int = 10;  -- HashBytes string length to ensure unique ID value
        
        -- calculation for selecting pending or released iteration of selected parent item
        DECLARE @ItemId uniqueidentifier;
        DECLARE @IsPndRev int;
        SELECT @ItemId = ItemId, @IsPndRev = CASE WHEN RevState=0 THEN 1 ELSE 0 END 
         FROM pdxpertdb.dbo.BasicItemRevision_View 
         WHERE Id = @P1;
        DECLARE @Span int = 2147483645 + @IsPndRev; -- ..645=released; ..646=pending

        -- create recursion tree 
        DECLARE @SortPad nvarchar(10) = '000'; -- prefix required for including parent item
        WITH BOM AS
        (
         SELECT * FROM PDXpertDB.dbo.BOMRollup_UDF(@ItemId,@Span,@MaxLvl)
        )

        -- format the output from the item attributes
        SELECT Id AS [Id]
         ,Parent AS [Reports To]
         ,Number + ' Rev ' + Revision AS [Item]
         ,CASE WHEN LEN(Name)>@NameLimit THEN RTrim(Left(Name,@NameLimit-1)) + '…' ELSE Left(Name,@NameLimit) END AS [Name]
        FROM (
            -- parent item attributes
            SELECT Left(CONVERT(NVARCHAR(32),HashBytes('MD5', @SortPad),2),@IdLength) AS Id
             ,'' AS Parent
             ,Owner AS Owner
             ,Type AS Type
             ,Number AS Number
             ,Coalesce(Revision,PendingRevision) AS Revision 
             ,Coalesce(Lifecycle,PendingLifecycle) AS Lifecycle 
             ,Description AS Name
             ,0 AS Find
             ,1 AS Quantity
             ,@SortPad AS TreeId
             ,0 AS Level
            FROM PDXpertDB.viewer.ItemView
            WHERE ItemId = @ItemId

            UNION ALL

            -- lower-level items' attributes
            SELECT Left(CONVERT(NVARCHAR(32),HashBytes('MD5', @SortPad + Replace(B.TreeId,'.','')),2),@IdLength)
             ,Left(CONVERT(NVARCHAR(32),HashBytes('MD5', @SortPad + Replace(Left(B.TreeId,LEN(B.TreeId)-3),'.','')),2),@IdLength)
             ,IVChild.Owner
             ,IVChild.Type
             ,CAST(IVChild.Number AS nvarchar(10))
             ,Coalesce(IVChild.Revision,IVChild.PendingRevision)
             ,Coalesce(IVChild.Lifecycle,IVChild.PendingLifecycle)
             ,IVChild.Description
             ,CAST(SIV.RowId AS nvarchar(10))
             ,Cast(SIV.Quantity as Varchar(20))
             ,TreeId
             ,Level
            FROM BOM B
            INNER JOIN PDXpertDB.dbo.StructureItem_View SIV ON SIV.Id=B.Id
            INNER JOIN PDXpertDB.viewer.ItemView IVChild ON IVChild.ItemId = SIV.BasicItem
        ) MLBOM
        --WHERE Type = 'Assembly'
        ORDER BY TreeId
      ]]></sql>
      </sqlquery>
    </choice>
  </dataquery>
  <Provider>Table2</Provider>
  <settings isImport="false"></settings>
</Definition>