The common method of BOM related in ERP system

Erp
One: BOM expansion (Press any parent node to the bottom)
The following write a simple, depending on the specific requirements of a little modification can be.
CREATE TABLE Table (Levelid int,levelname char (2), parent int)
Insert Table Select 1, ' AA ', 0
UNION ALL Select 2, ' BB ', 1
UNION ALL Select 3, ' CC ', 1
UNION ALL Select 4, ' DD ', 2
UNION ALL Select 5, ' EE ', 3
UNION ALL Select 6, ' FF ', 5

Create function BOM (@name char (2))
Returns @tb table (Levelid int,levelname char (2), parent int)
As
Begin
Insert @tb Select Levelid,levelname,parent from table where LevelName = @name
While @ @rowcount > 0
Insert @tb Select levelid,levelname,parent from table
where parent in (select Levelid from @tb)
and Levelid not in (select Levelid from @tb)
Return
End

SELECT * from Dbo.bom (' BB ')
Levelid LevelName Parent
----------- --------- -----------
2 BB 1
4 DD 2

(The number of rows affected is 2 rows)



Two: LLC (lowest floor code)
1: There are at least two fields in the material master file
Itemno,llc
There are at least two fields in the 2:bom (tree-like)
Parentitem,itemno
3:
Create Procedure LLC
As
Update Item Master File Set LLC = 0--First Clear LLC to 0
Declare @i tinyint
Set @i = 0
While @i <= 10--Assuming the BOM is up to 10 levels
Begin
Update a Set A.LLC = @i + 1--Sub-node LLC plus 1
From item master file a
Join BOM B on A.itemno = B.itemno
Join Item main file c on c.itemno = B.parentitem
where C.LLC = @i
Set @i = @i + 1
End

/*********** usage:exec LLC *******/