Chg.[ChangeNum] WHERE Chg.[Id] = @P1 AND AffItm.[Class] = 1 -- exclude documents AND AffItm.[IsHomeItem] = 'true' -- exclude partner parts AND BIR.RChange = Chg.[Id] -- Released iterations AND Chg.[State] >= 10 -- released/completed change UNION ALL SELECT -- Bill of Materials Item Line column headers 'Flag' AS [Flag] -- Required (TEXT): The text "Item" indicating the start of a Bill of Materials Item line. ,'Description' AS [Number] -- Required (TEXT): The description of the the Bill of Materials item. ,'Type' AS [Description] -- Required (TEXT): The type of the Bill of Materials Item. Values must match one of the following: [Finished Good | Raw Good | Repair | Work Order | Note] ,'Part' AS [Type] -- Required (TEXT): The part that is associated with this Bill of Materials Item. ,'Quantity' AS [Revision] -- Required (NMBR): The quantity associated with this Bill of Materials Item. ,'UOM' AS [AutoCreateType] -- Required (TEXT): The unit of measurement to use for this Bill of Materials item. It must match (including case) an existing UOM abbreviation (not the name) in Fishbowl. ,'IsOneTimeItem' AS [NoDataA] -- Optional (BOOL): Determines if this is a one time item. A one time item will not be affected by the quantity of a work order. [true | false] ,'IsStage' AS [NoDataB] -- Optional (BOOL): Determines if the Bill of Materials Item is a stage. [true | false] ,'StageBOMNumber' AS [NoDataC] -- Optional (TEXT): The Bill of Materials number of the stage Bill of Materials. The finished good part number that matches the Part field on this import line. ,'InstructionNote' AS [NoDataD] -- Optional (TEXT): The note that will be displayed when this item is selected in the Bill of Materials instructions. ,'ConfigurationSortOrder' AS [NoDataE] -- Optional (NMBR): The line number of this Bill of Materials item in the Configuration of a Bill of Materials. ,'InstructionSortOrder' AS [NoDataF] -- Optional (NMBR): The row number of this Bill of Materials. >> BOM list row number (FG is 1, RG 2... sorted by Find) ,0 AS [AffectedLine] -- THESE HEADERS MOVE TO FIRST DATA ROW - located here as headers from following results ,0 AS [Find] UNION ALL -- Item: parent as output Finished Good SELECT DISTINCT 'Item' AS [Flag] ,'Create ' + AffItm.[Number] AS [Description] ,'Finished Good' AS [Type] ,AffItm.[Number] AS [Part] ,'1' AS [Quantity] ,AffItm.[DefaultUOM] AS [UOM] ,'' AS [IsOneTimeItem] ,'' AS [IsStage] ,'' AS [StageBOMNumber] ,'' AS [InstructionNote] ,'' AS [ConfigurationSortOrder] ,'1' AS [InstructionSortOrder] ,AI.[LineNumber] AS [AffectedLine] ,0.1 AS [Find] -- place FG row immediately after BOM Item Line header row FROM Change_View Chg INNER JOIN AffectedItem_View AI ON AI.[ChangeAction] = Chg.[Id] INNER JOIN BasicItemRevision_View BIR ON AI.[Item] = BIR.[ItemId] AND BIR.RChange = Chg.[Id] INNER JOIN viewer.ItemView AffItm ON AffItm.[ItemId] = AI.[Item] INNER JOIN StructureItem_View BomList ON BomList.[ParentItem] = AffItm.[ItemId] AND BomList.[FromNum] <= Chg.[ChangeNum] AND BomList.[ToNum] > Chg.[ChangeNum] WHERE Chg.[Id] = @P1 AND AffItm.[Class] = 1 AND AffItm.[IsHomeItem] = 'true' AND Chg.[State] >= 10 UNION ALL -- Item: child component SELECT 'Item' AS [Flag] ,'Add ' + Comp.[Number] AS [Description] ,'Raw Good' AS [Type] ,CASE WHEN LEN(ISNULL(Comp.[Number],'')) = 0 THEN '' -- 'ERROR' ELSE Comp.[Number] END AS [Part] ,CAST(ISNULL(BomList.[QtyInPU], 0) AS nvarchar(10)) AS [Quantity] ,CASE WHEN LEN(ISNULL(Comp.[DefaultUOM],'')) = 0 THEN 'ERROR' ELSE Comp.[DefaultUOM] END AS [UOM] ,CASE WHEN BomList.[BOMQuantityCategory] = 1 THEN 'true' ELSE 'false' END AS [IsOneTimeItem] ,CASE WHEN Comp.[Type] IN (@AssemblyPartTypeName1, @AssemblyPartTypeName2, @AssemblyPartTypeName3, @AssemblyPartTypeName4) THEN 'true' ELSE 'false' END AS [IsStage] ,CASE WHEN Comp.[Type] IN (@AssemblyPartTypeName1, @AssemblyPartTypeName2, @AssemblyPartTypeName3, @AssemblyPartTypeName4) THEN Comp.[Number] ELSE '' END AS [StageBOMNumber] ,LTRIM(RTRIM( ISNULL(BomList.[Notes],'') + CASE WHEN LEN(ISNULL(BomList.[ReferenceDesignator],'')) > 0 THEN ' RefDes: ' + BomList.[ReferenceDesignator] ELSE '' END )) AS [InstructionNote] ,CAST(BomList.[Number] AS varchar) AS [ConfigurationSortOrder] ,CAST((1 + ROW_NUMBER() OVER (PARTITION BY AI.[LineNumber] ORDER BY BomList.[Number])) AS varchar) AS [InstructionSortOrder] ,AI.[LineNumber] AS [AffectedLine] ,BomList.[Number] AS [Find] FROM Change_View Chg INNER JOIN AffectedItem_View AI ON AI.[ChangeAction] = Chg.[Id] INNER JOIN BasicItemRevision_View BIR ON AI.[Item] = BIR.[ItemId] AND BIR.RChange = Chg.[Id] INNER JOIN viewer.ItemView AffItm ON AffItm.[ItemId] = AI.[Item] INNER JOIN StructureItem_View BomList ON BomList.[ParentItem] = AffItm.[ItemId] AND BomList.[FromNum] <= Chg.[ChangeNum] AND BomList.[ToNum] > Chg.[ChangeNum] INNER JOIN BasicItemRevision_View CompRev ON CompRev.[ItemId] = BomList.[BasicItem] AND CompRev.[FromNum] <= Chg.[ChangeNum] AND CompRev.[ToNum] > Chg.[ChangeNum] INNER JOIN ItemLifeCyclePhase_View CompLC ON CompLC.[Id] = CompRev.[LifeCyclePhase] INNER JOIN viewer.ItemView Comp ON Comp.[ItemId] = CompRev.[ItemId] WHERE Chg.[Id] = @P1 AND AffItm.[IsHomeItem] = 'true' AND Chg.[State] >= 10 ) BOMRows ORDER BY [AffectedLine], [Find] ]]> Table2