set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[P_DJS]
(
@deeplevel int,
@firstcode 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), 
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,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,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,wastage,(select top 1 TAXUP from ANT  where ANT.CODE=#tree.code AND TAXUP > 0 order by DATETIME desc) taxup from #tree  
--order by code


RETURN @@ERROR END

SET NOCOUNT OFF
SET XACT_ABORT OFF

exec P_DJS 1,'WI-EGD-3575'

1    KJ-1111-003    0.00
  2    ZA111100102    0.00
  2    ZA111100403    0.00
  2    ZA111100502    0.00
  2    ZA111100601    0.00
  2    ZC111100701    0.00
    3    COD00000300    3.00
    3    COD00000300    3.00
    3    COD00000600    3.00
    3    COD00000600    3.00
    3    DDD03019800    0.25
    3    EOJ00001400    1.00
    3    EOJ00002000    1.00
    3    EOJ00016400    1.00
    3    JOA01000100    1.00
    3    JOA01000300    1.00
    3    JOA01005700    1.00
    3    JOS00002900    1.00
    3    JOS00012100    1.00
    3    JOS00014900    1.00
    3    PCA00097500    5.00
    3    PCA00098000    5.00
    3    PCA00100800    10.00
    3    POA00007100    2.00
    3    POA00007200    2.00
    3    POA00007600    2.00
    3    POA00007700    2.00
    3    POA00008300    2.00
    3    POA00008400    2.00
    3    POA00008700    2.00
    3    POA00076400    2.00
    3    POA00076500    2.00
    3    POA00076600    2.00
    3    POA00102300    2.00
    3    POA01097300    2.00
    3    POA01097400    2.00
    3    POA01097800    2.00
    3    POA01097900    2.00
    3    POA01151900    2.00
    3    POA10022200    2.00
    3    POA15098200    2.00
    3    POA15139300    2.00
    3    POB00013500    1.00
    3    POB00013500    1.00
    3    POB00013500    1.00
    3    POB00120300    1.00
    3    POB02001600    1.00
    3    PPS15097100    0.00
    3    PPS15097200    0.00
    3    PPS15098100    0.00
    3    PPS15098700    0.00
    3    PPS15139200    0.00
    3    PPS15139400    0.00
    3    PSA07022300    0.00
    3    WAA00001300    1.00
    3    WBH00001500    1.00
    3    WBL00034500    1.00
    3    WOH00002400    1.00
    3    WOH00004400    1.00