Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
19%
200 - 500 GB
28%
500 - 800 GB
3%
800 - 1200 GB
7%
1200 - 1500 GB
8%
1500 - 2000 GB
12%
> 2000 GB
23%
Total votes: 74

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 16th January 2019, 05:46
pegaga1979 pegaga1979 is offline
Senior Member
 
Join Date: Oct 2008
Posts: 170
pegaga1979 is on a distinguished road
Baan: Baan V - DB: SQL - OS: WINDOWS 2003
Query bom multilevel jumping
Baan: ERP LN 6.1 FP1
C/S: None/Unknown

Hi,

I have create query to print bom multilevel, but the data display is jump.
i wanted to display like this:
ItemA |1. | ItemB
ItemA |1. | ItemC
ItemA | 2. | ItemD
ItemA | 2. | ItemE
ItemA | 3. | ItemF
ItemA | 4. | ItemG
ItemA | 5.| ItemH
ItemA | 3. | ItemI

But resulted:
ItemA | | ItemB
ItemA | | ItemC
ItemA | | ItemE
ItemA | | ItemI

My script is as below:
Code:
declaration:

table	ttibom010




detail.1:

after.layout:
	select tibom010.*
	from tibom010
	where tibom010.mitm=:tibom010.sitm
	selectdo
		layout.again()
	selectempty
	endselect
Does anyone can help me how i can print the right report?
Your help is very much appreciated.
Thank you.
Reply With Quote
  #2  
Old 16th January 2019, 15:11
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,336
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Is there more to this script? I guess if what you posted works then you could just add some kind of sort to the tibom010 select.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #3  
Old 17th January 2019, 01:21
pegaga1979 pegaga1979 is offline
Senior Member
 
Join Date: Oct 2008
Posts: 170
pegaga1979 is on a distinguished road
Baan: Baan V - DB: SQL - OS: WINDOWS 2003
Hi Mark,

my main query as below:
Code:
select
       tibom010.exdt,         | Expiry Date
       tcibd001.item,         | Item
       tcibd001.citg,         | Item Group
       tibom010.pono,         | Position
       tibom010.sitm,         | Item
       tibom010.qana,         | Net Quantity
       tibom010.cwar,         | Warehouse
       tibom010.indt          | Effective Date
from
       tcibd001,              | Items - General
       tibom010               | Bill of Material
where
       tcibd001.item >= tcibd001.item.f and
       tcibd001.item <= tcibd001.item.t and
       tcibd001.citg >= tcibd001.citg.f and
       tcibd001.citg <= tcibd001.citg.t and
       tcibd001.item = tibom010.mitm
my report script:
Code:
declaration:

extern	domain	tcdate		today
	domain	tcdate		expdate
	domain	tcmcs.long	no.of.days
	
long	lng.ddta
long	lng.dtim
long	lng.cdat
long	lng.ctim

table	ttibom010
|extern domain tcbool	print.lay

before.program:
	today = utc.to.local (utc.num (), lng.ddta, lng.dtim)


detail.1:
before.layout:
	
	expdate = utc.to.local (tibom010.exdt, lng.cdat, lng.ctim)
	no.of.days = lng.cdat - lng.ddta

	if no.of.days > 0 then
	lattr.print = true 
	
	else lattr.print = false
	endif
can u help me where i'm missing?
Reply With Quote
  #4  
Old 17th January 2019, 16:04
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,336
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Now I am really confused. The first post shows a layout again and the second post has nothing like that in the report script. So if I go off the second post my first question would be does that main query (assuming from session script) return all the records you are looking for?
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #5  
Old 17th January 2019, 16:31
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,800
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox Tools 10.7 - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
No easy SQL

[quote=pegaga1979;211400]Hi,

I have create query to print bom multilevel, but the data display is jump.
i wanted to display like this:
ItemA |1. | ItemB
ItemA |1. | ItemC
ItemA | 2. | ItemD
ItemA | 2. | ItemE
ItemA | 3. | ItemF
ItemA | 4. | ItemG
ItemA | 5.| ItemH
ItemA | 3. | ItemI
Code:
In print BOM structure , this is the BOM Level, where the item is found (eg).
1  - sitm has own BOM
.2 - sitm at Level 2 has own BOM
..3 - component (sitm) 
..3 - component (sitm) 
..3 - component (sitm) 
.2 - next sitm at BOM Level 2
.2
.2
1
.2
..3
..3
.2
1
This can't be realized with easy SQL.

The stucture in Report must be drilled down by BOM Level
On lower BOM Level, sitm become mitm.

Regards
__________________
//Bernd

Last edited by bdittmar : 17th January 2019 at 16:44.
Reply With Quote
  #6  
Old 21st January 2019, 01:35
pegaga1979 pegaga1979 is offline
Senior Member
 
Join Date: Oct 2008
Posts: 170
pegaga1979 is on a distinguished road
Baan: Baan V - DB: SQL - OS: WINDOWS 2003
Quote:
Originally Posted by mark_h View Post
Now I am really confused. The first post shows a layout again and the second post has nothing like that in the report script. So if I go off the second post my first question would be does that main query (assuming from session script) return all the records you are looking for?
Hi Mark,
Apologize because missing the bottom script.
Below is the complete code.

Code:
declaration:

extern	domain	tcdate		today
	domain	tcdate		expdate
	domain	tcmcs.long	no.of.days
	
	

long	lng.ddta
long	lng.dtim
long	lng.cdat
long	lng.ctim

table	ttibom010
|extern domain tcbool	print.lay



before.program:
	today = utc.to.local (utc.num (), lng.ddta, lng.dtim)


detail.1:
before.layout:
	
	expdate = utc.to.local (tibom010.exdt, lng.cdat, lng.ctim)
	no.of.days = lng.cdat - lng.ddta

	if no.of.days > 0 then
	lattr.print = true 
	
	else lattr.print = false
	endif



after.layout:
	select tibom010.*
	from tibom010
	where tibom010.mitm=:tibom010.sitm
	selectdo
		layout.again()
	selectempty
	endselect
Reply With Quote
Sponsored Links
  #7  
Old 21st January 2019, 13:59
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,336
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
This is the part that confuses me"
Code:
after.layout:
	select tibom010.*
	from tibom010
	where tibom010.mitm=:tibom010.sitm
	selectdo
		layout.again()
	selectempty
	endselect
Everytime you hit this section of code you are going to get the first record for what ever is in subitem - assuming it is a make item. So each time you hit it only if the subitem is a make will you get the first item on the BOM. Then the when the subitem becomes a purchase part then you will go get the record the next record on the report. Since I am not sure exactly what you are trying to do I ca't really make suggestions. If you are trying to read an indented bom then I do not know of how to make this work without replicating everything in printing indented boms in the report.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #8  
Old 24th January 2019, 01:54
pegaga1979 pegaga1979 is offline
Senior Member
 
Join Date: Oct 2008
Posts: 170
pegaga1979 is on a distinguished road
Baan: Baan V - DB: SQL - OS: WINDOWS 2003
Hi Mark,

At this moment, I just want to all multilevel bom is printed in report. The dot (to identify the level) is my next stage of report design
Reply With Quote
  #9  
Old 24th January 2019, 17:20
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,336
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
I am just not sure you can achieve this thru a report script. My BOM explosion programs are do a whole lot to decide what to pass to the report. It uses an array of item to determine where it is at in the Bill of materials. If you need something to print an indented bill there should already be some reports in LN. Then depending on what you want to do with the output you can modify those reports.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #10  
Old 4th February 2019, 14:37
Han Brinkman's Avatar
Han Brinkman Han Brinkman is offline
Guru
 
Join Date: Aug 2001
Location: The Netherlands
Posts: 1,161
Han Brinkman has a spectacular aura aboutHan Brinkman has a spectacular aura about
Baan: All - DB: Oracle/ms-sql/db2 - OS: *nix/windows
Since the 4GL language of baan doesn't support recursive programming you need a few arrays to do keep the status of the last line which you handled and come back after you handled the next level of the bom.
That is the way to make it work for all levels.
If that's to complex: you can use different functions to handle each level. However if you have to handle a lot of levels than you wil have to copy a lot of functions ;-)
Reply With Quote
  #11  
Old 4th February 2019, 19:59
vamsi_gujjula's Avatar
vamsi_gujjula vamsi_gujjula is offline
Senior Member
 
Join Date: Nov 2011
Location: hyderabad ; india
Posts: 244
vamsi_gujjula is on a distinguished road
Baan: ERPLN fp3 and 10.4 - DB: oracle - OS: Aix , Redhat linux 6
Recursion

Quote:
Originally Posted by Han Brinkman View Post
Since the 4GL language of baan doesn't support recursive programming you need a few arrays to do keep the status of the last line which you handled and come back after you handled the next level of the bom.
That is the way to make it work for all levels.
If that's to complex: you can use different functions to handle each level. However if you have to handle a lot of levels than you wil have to copy a lot of functions ;-)
I think baan does allow recursion as long as the function doesn't have argument.
__________________
Regards,
Vamsi
Reply With Quote
  #12  
Old 5th February 2019, 11:32
Han Brinkman's Avatar
Han Brinkman Han Brinkman is offline
Guru
 
Join Date: Aug 2001
Location: The Netherlands
Posts: 1,161
Han Brinkman has a spectacular aura aboutHan Brinkman has a spectacular aura about
Baan: All - DB: Oracle/ms-sql/db2 - OS: *nix/windows
Vamsi, looks like you are correct, checked it with a little script.
Reply With Quote
  #13  
Old 6th February 2019, 21:18
hklett's Avatar
hklett hklett is offline
Member
 
Join Date: May 2002
Posts: 55
hklett is on a distinguished road
Its also not allowed to have local variables.
So you have to handle this by global arrays.
With this restrictions the recursive call of functions doesnt help much.
Reply With Quote
  #14  
Old 12th February 2019, 17:23
avpatil avpatil is offline
Guru
 
Join Date: Feb 2002
Posts: 738
avpatil is on a distinguished road
Baan: IVc3 - DB: SQL2000 - OS: Win 2003
Looks like you want to print multilevel BOM but don't want to show items at intermediate level. If you are running this at one top level item at time, then it is very straight forward Take a standard Baan BOM report and just show the main item, current item and show item of current level. Levels are already being calculated by Baan. I think it should be fairly simple to do.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multilevel BOM Sarath Blogs and Web links 0 29th July 2016 07:48
Multilevel BOM net quantity sasank Blogs and Web links 0 20th July 2016 06:55
Multilevel Bom (tibom1411m000) BaaN IV vincent Tools Development 8 6th February 2003 06:10


All times are GMT +2. The time now is 02:54.


©2001-2018 - Baanboard.com - Baanforums.com