Indented Bom's to csv or Excel? 2

[ifrydr]

Ok, I have asked a similar question but now I have a different need.

We have a list of bom's that contain sub assemblies, when I "print" a bom from the indented bom module it prints great or I can print to a file and it will output to txt in the default Macola directory. Is there a way to output a "full" bom to Excel or csv or maybe a way to generate a crystal report to pull the bom with all sub assemblies without having to know the "child" numbers? Hopefully this makes sense.

Thank you.

 

[kscheeler]

What I've done in the past is to create a stored procedure that looks at the top level bom with the parent, component, level qty etc.. and put it into a temporary table. Then look at the BOM table again linking it to the temporary table and then only look at components. Do this 10 more times and the temporary table will have an indented BOM.

You can then write a crystal report against the stored procedure or just execute the stored procedure in Query Analyzer.

Kevin Scheeler

 

[TLeaders]

Run this sql statement in Query analyzer

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]



good luck
Tom

 

[ifrydr]

Thank you very much for the replies! Is there any way you can walk me through some of this, or maybe point me to a link somewhere with more help on how to "execute" these? I am somewhat savvy but nowhere near where you are with sql etc. but would like to utilize this functionality.

(Background: PCB engineer that has "inherited" the Macola\Progression support.... scary, I know.)

Thanks again.

 

[TLeaders]

Ifyrdr

What I posted was 2 blocks of code. The second set creates a user defined sql function that will explode a multi level bill and return all the components. The first code block calls/runs that function.

Start Query Analyzer
Connect to the Macola data base.
Copy the second code block and paste it into the Query Analyzer code window.
Execute this code (press the green arrow)
The function will be added to that specific data base.

Open a new query window (press the new query icon or ctl+N)
Copy the first code block and paste it into the Query Analyzer code window.
select * from fn_Explode_Bill('MONITOR 40') ORDER BY KEYVALUE
change 'MONITOR 40' to the parent item that you want to explode
Execute this code (press the green arrow)

To send the results to a file under the menu item query select “Results to file”.


This is the cook book version of the process. If you are still having problems I would recommend contacting your Business Partner or Exact for additional help.

Tom

 

[ifrydr]

Tom,

Thank you very much for the detailed help and instructions.

This is exactly what we need to do, at some point it might be nice for me to add the item descripiton field from Progression. I copy/pasted into excel and it worked great, 'Results to File' created a report that would not open in Crystal, (Invalid TLV Record)... not sure what that is all about.

Thanks again for your help it is very much appreciated!