Indented Bom's to csv or Excel store procedure?

[jclng]

The store procedure provided by tleaders is very helpful.
but i find a BOM scenario where the the procedure did not multiply the qty correctly

For example:

Level 1 qty = 1
Level 2 qty = 2
Level 3 qty = 1
Level 4 qty = 0.25

when running the code below the level 4 extended qty = .025 rather than .50
Please help
Thanks

select * from  fn_Explode_Bill('MONITOR 40') ORDER BY KEYVALUE 
[\code]

After you run this code to create a function

[code]
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME = 'fn_Explode_Bill' AND TYPE = 'TF')
drop function fn_Explode_Bill
go


create function fn_Explode_Bill
(
@STARTINGPARENT AS VARCHAR(100)
)
returns  @TESTstructure table (
low_level int,
Par_item_no VARCHAR (100),
Comp_Item_no VARCHAR (100),
WKQTYPER float,
Extended_WKQTYPER float,
Process_flag char (1),
keyvalue VARCHAR(2000),
idi numeric identity
)
as
begin

declare @ssql as nVARCHAR (2000)
DECLARE @RowsAdded int
declare @low_level int

set @low_level = 0
insert into @TESTstructure (low_level,Par_item_no,Comp_Item_no,WKQTYPER,Extended_WKQTYPER,Process_flag,keyvalue)
values (@low_level,@STARTINGPARENT,@STARTINGPARENT,1,1,'Y','')

   SET @RowsAdded = @@rowcount

   WHILE @RowsAdded > 0 and  @low_level <= 500
    BEGIN
    set @low_level = @low_level + 1
    insert into @TESTstructure (low_level,Par_item_no,Comp_Item_no,WKQTYPER,Extended_WKQTYPER,Process_flag,keyvalue)
    Select @low_level,ITEM_NO,A.COMP_ITEM_NO,QTY_PER_PAR,QTY_PER_PAR * WKQTYPER,'Y',
          CASE WHEN DATALENGTH(B.KEYVALUE) < 1800 THEN B.KEYVALUE+A.item_no + str(seq_no,4) ELSE B.KEYVALUE END
        FROM BMPRDSTR_SQL a 
        inner join @TESTstructure b on b.process_flag = 'Y' and b.Comp_Item_no = ITEM_NO
--          FOR PHANTOM LOGIC ADD JOIN TO IMITMIDX_SQL HERE
        SET @RowsAdded = @@rowcount


    update @TESTstructure
      set process_flag = 'N'
       where low_level <> @low_level
             and process_flag = 'Y'
    update @TESTstructure
      set comp_item_no = ''
       where low_level = 0 
         and par_item_no = comp_item_no
             and comp_item_no <>  ''
     END

IF @low_level >= 500 
BEGIN 
  insert into @TESTstructure (low_level,Par_item_no) values (@low_level,'Recursion')
END 
return 
end 
go 


[\code]

 

[TLeaders]

Sorry I cannot run down the issue, I no longer have the macola data base on my laptop. It is has been 10 years since I used it so I don’t remember the details any more.
Having said that I think the most likely issue is that the same item occurs multiple times in the structure. You may find this by changing the temp work table to a normal table, and review the entries. If you see multiple entries for the same item at the same level this could be the problem. Or you could just dump the table at the end.

This routine is a small part of a “what can I make” routine for another system. I stripped most of the code from that routine and changed the table and field names. So it received very minimal testing on Macola.

Tom

 

[jclng]

Thanks for the reply.
I checked the component in question and it only has 1 entry in the whole bill.
Not experience enough to write or alternate the table.
Any help would be appreciated.