set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[P_DJP]
(
@deeplevel int,
@firstcode nvarchar(100),
@liqty int,
@gd nvarchar(100)

)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN

create table #tree 
(
bomno nvarchar(100),
code nvarchar(100), 
deeplevel int, 
cbdesc nvarchar(100), 
qty_nee numeric(19,8), 
loc varchar(32),
wastage numeric(6,2),
liqty numeric(19,8),
sh numeric(19,8), 
isLeafnode int,
tree nvarchar(max) default '' 
) 
declare 
@cbdesc varchar(32), 
@QTY_NEED numeric(19,8), 
@loc varchar(32), 
@wastage numeric(6,2)

insert #tree 
select BOMT.BOMNO,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((@liqty+(@liqty*(WASTAGE/100)))*QTY_NEED),(WASTAGE/100),1,
BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on  BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO=@firstcode 


WHILE @@rowcount > 0 
BEGIN
    SET @deeplevel = @deeplevel + 1

    update #tree set isLeafnode= 0 from #tree 
        join BOMT
        on #tree.deeplevel=@deeplevel-1
        and BOMT.BOMNO collate database_default =#tree.code
    insert #tree 
        select @firstcode,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((liqty+(liqty*(WASTAGE/100)))*QTY_NEED),sh+(WASTAGE/100),1,#tree.tree+'_'+BOMT.CODE+left('00000000000000000000',20-len(BOMT.CODE)) 
        from BOMT
        join #tree
        on #tree.deeplevel=@deeplevel-1
        and BOMT.BOMNO collate database_default =#tree.code
        left join MAINBOM 
        on BOMT.CODE=MAINBOM.BOMNO
END


select space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,sh,liqty
 from #tree 

RETURN @@ERROR END

SET NOCOUNT OFF
SET XACT_ABORT OFF

use tempdb
Go
if object_id('#') Is Not Null
    Drop Table #

Create Table #
(
    ID int ,
    ParentID int,
    level smallint,
    value nvarchar(10)
)    
Go
insert into #(ID,ParentID,level,value)
    Select 1,0,1,'Root' Union All
    Select 2,1,2,'A1' Union All
    Select 3,1,2,'B1' Union All
    Select 4,1,2,'C1' Union All
    Select 5,2,3,'A11' Union All
    Select 6,3,3,'B11' Union All
    Select 7,5,4,'A111' Union All
    Select 8,6,4,'B111' Union All
    Select 9,8,5,'B1111'
Go
;With CTE_Seq As
(
    Select ID,ParentID,level,value,convert(nvarchar(200),rtrim(id)) As IDPath From # Where ParentID=0
    Union All
    Select b.ID,b.ParentID,b.level,b.value,convert(nvarchar(200),a.IDPath+'.'+rtrim(b.id)) As IDPath 
        From CTE_Seq  As a
            Inner Join # As b On b.ParentID=a.ID                

)
Select value,level,IDPath
    From CTE_Seq
    Order By IDPath

/*
value      level  IDPath
---------- ------ ----------
Root       1      1
A1         2      1.2
A11        3      1.2.5
A111       4      1.2.5.7
B1         2      1.3
B11        3      1.3.6
B111       4      1.3.6.8
B1111      5      1.3.6.8.9
C1         2      1.4
*/