Baanboard.com

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

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 29th October 2002, 23:06
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
Question report script problem

I am trying to get data twice from the same table. I am trying to get the subassembly(subass) where a selected purcased item is used. Then I am trying to get the main item(test) that the subassembly is used in.

The system is returning the subass as the test.
Code:
declaration:
	table ttiitm001
	table ttibom010

	extern domain tcitem subass, test
		
detail.1:
before.layout:
	select tibom010.*
	from tibom010, tiitm001
	where tibom010.sitm = :tdpsc001.item
	and tibom010.sitm = tiitm001.item
	and tiitm001.kitm =2
	and tibom010.exdt =0 	
	selectdo	
	endselect
	
	select tiitm001.*
	from tiitm001
	where tiitm001.item = :tibom010.sitm
	selectdo
		subass = tibom010.mitm
	endselect
		
	select tibom010.*
	from tibom010
	where tibom010.mitm = :subass
	selectdo
		test = tibom010.mitm
	endselect
Reply With Quote
  #2  
Old 30th October 2002, 02:31
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Not sure

I am not sure I understand why you are doing this.
To get the make components for a sub-component you only need one query.

Code:
        select tibom010.mitm,tibom010.sitm,tiitm001.dsca
        from tibom010, tiitm001
        where tibom010.sitm = :tdpsc001.item
        and tibom010.sitm refers to tiitm001.item
        and tiitm001.kitm =2
        and tibom010.exdt =0    
        selectdo        
        endselect

This would return ALL make items for a purchase part. But because of the selectdo/endselect you would only get the last make item. Are you sure you want to do this in the script? I would expect some kind of spool commands in the select do to print results.

I do not understand the other queries at all. The second query sets subass to tibom010.mitm, but the query is on tiitm001. In this case the subass will be set to the LAST make item from the first query. Then you turn around and find the make item on the tibom010 again. This would find all components of the make item, but it would only set it for the last sub-items.

It sounds like you are passing a purchase item to the report and want to print all of the assemblies that this item is used on? If true then you could use the query above(with spool commands) to print all of the assemblies. Just not sure what you are trying to do.

Mark
Reply With Quote
  #3  
Old 30th October 2002, 02:39
Paul P's Avatar
Paul P Paul P is offline
Guru
 
Join Date: Jul 2002
Location: Jakarta, Indonesia
Posts: 797
Paul P is on a distinguished road
Baan: BaanIV, BaanERP - DB: SQL Server, Informix, Oracle - OS: Windows, HP-UX
Smile Change WHERE clause in last query

Dear Itannous,

You have to change the WHERE clause in the last query, ie line 4 from the bottom, to:

Code:
where tibom010.sitm=:subass

and you should get the right answer.

By the way, to speed up query you might want to use table indices. Particularly the index2 of table tibom010 because it contains tibom010.sitm

Rgds,
Paul
Reply With Quote
  #4  
Old 30th October 2002, 21:03
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
how do I not print the details ?

Thanks Paul, that worked. How can I print a summary. I have in the past just deleted the detail layout and added the after field, but that is not working this time. Any ideas.
Reply With Quote
  #5  
Old 31st October 2002, 02:11
Paul P's Avatar
Paul P Paul P is offline
Guru
 
Join Date: Jul 2002
Location: Jakarta, Indonesia
Posts: 797
Paul P is on a distinguished road
Baan: BaanIV, BaanERP - DB: SQL Server, Informix, Oracle - OS: Windows, HP-UX
Smile after.field without detail

Dear Itannous,

You can print after.field kind of summary without putting any detail section if the number you want to summarise has been passed from program script to report engine. Say your prog script already send the purchase amount per PO line to report engine, then you can omit detail section and summarise purchase amount per PO just using after.field

However, if you need to calculate something based on data thrown by program script and summarise this calculated data, you'd have to have detail section. For example, prog script send purchase amount per PO line in purchase currency only and you need the purchase amount in Thailand Baht. Then you'd need to convert it in detail section before summarising in after.field section. Of course you can use lattr.print=false after calculation in detail section, so the calc will still be performed but the detail section won't show up. This way, you can still have summary report of numbers that need additional calc

Rgds,
Paul
Reply With Quote
  #6  
Old 31st October 2002, 04:40
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
Thumbs up That worked GREAT!!

Thanks for you help Paul
Reply With Quote
  #7  
Old 1st November 2002, 13:53
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
Angry Report Script Still not working Properly

I thought it had worked, but when I reviewed the details of the report, it is only writing the last main item(mitm) for the subassembly (sitm). It only writes the sitm once, even though it appears in many main items.
How can I write the details many times.

This is the report script
Code:
declaration:
	table ttimps200	
	table ttimps300
	table ttiitm001
	table ttibom010
	extern domain tcitem subass, test
	extern domain tcqiv1 unosub, usgmtm, quan1, quan2, val1, val2	
	extern domain tccuni unomea, unmain
	extern domain tcdate   date.f       
	extern domain tcdate   date.t     
	extern domain timps.plnc plnc.f
	extern domain timps.plnc plnc.t
	extern domain timps.plvl plvl.f
	extern domain timps.plvl plvl.t
        extern domain timps.plnc livec
	extern domain timps.plvl livel

detail.1:
before.layout:
	select tibom010.*
	from tibom010, tiitm001
	where tibom010.sitm = :tdpsc001.item
	and tibom010.sitm = tiitm001.item
	and tiitm001.kitm =2
	and tibom010.exdt =0 	
	order by tibom010._index2
	selectdo
	endselect
	
	select tiitm001.*
	from tiitm001
	where tiitm001.item = :tibom010.sitm
	selectdo
		unomea = tiitm001.cuni		
		unosub = tibom010.qana
		subass = tibom010.mitm
	endselect

        select  tiitm001.*
	from tiitm001
	where tiitm001.item = :tibom010.mitm
	selectdo
		unmain= tiitm001.cuni 
	endselect	
	
	select tibom010.*
	from tibom010
	where tibom010.sitm = :subass
	and tibom010.exdt = 0	
	selectdo
		test = tibom010.mitm
		usgmtm = tibom010.qana
	endselect

	select sum (timps300.demf)
	from timps300, timps200
	where timps300.plnc inrange :plnc.f and :plnc.t		|= "300"
	and timps300.plvl inrange :plvl.f and :plvl.t 		|= 99
	and timps300.plni = timps200.plni
	and timps200.plnc = :livec
	and timps200.plvl = :livel
	and timps300.plni = :test
	and timps300.demf > 0
	and timps300.pdat inrange :date.f and :date.t
	selectdo
		quan1 = timps300.demf
	endselect
		
	select sum(timps300.demf)
	from timps300, timps200
	where timps300.plnc inrange :plnc.f and :plnc.t		|= "300"
	and timps300.plvl inrange :plvl.f and :plvl.t		|= 99
	and timps300.plni = timps200.plni
	and timps200.plnc = :livec
	and timps200.plvl = :livel
	and timps300.plni = :subass
	and timps300.demf > 0
	and timps300.pdat inrange :date.f and :date.t

	selectdo	
		quan2 = timps300.demf
	endselect
	
	val1 = unosub * quan2
	val2 = quan1 * usgmtm * unosub 
	
after.tdpsc001.item.2:
before.layout:
	select tiitm001.*
	from tiitm001
	where tiitm001.item = :tibom010.sitm
	selectdo
		unomea = tiitm001.cuni
	endselect
Reply With Quote
Sponsored Links
  #8  
Old 2nd November 2002, 03:23
Paul P's Avatar
Paul P Paul P is offline
Guru
 
Join Date: Jul 2002
Location: Jakarta, Indonesia
Posts: 797
Paul P is on a distinguished road
Baan: BaanIV, BaanERP - DB: SQL Server, Informix, Oracle - OS: Windows, HP-UX
Smile tibom1413m000, Print where used BOM components (multi level)

Dear Itannous,

Oh, I see you are trying to make a report that shows the demand forecast of sub assemblies and finished goods that needs a certain item as base component. I think it would be better then if you customise the report of the session tibom1413m000, Print where used BOM components (multi level). In this session, Baan will already find out for you what sub assemblies and finished goods that needs the item as component. You then would just have to find the demand forecast for every sub assembly and FG that Baan finds. Much easier, ay?

Rgds,
Paul
Reply With Quote
  #9  
Old 3rd November 2002, 01:34
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
That is a possibility, but

I dont have the source code for this session and I need to add additional selection options
Reply With Quote
  #10  
Old 4th November 2002, 02:46
Paul P's Avatar
Paul P Paul P is offline
Guru
 
Join Date: Jul 2002
Location: Jakarta, Indonesia
Posts: 797
Paul P is on a distinguished road
Baan: BaanIV, BaanERP - DB: SQL Server, Informix, Oracle - OS: Windows, HP-UX
Smile No dev lic?

Dear Itannous,

Wow, now this is a tough case. I'm assuming you don't have dev license and are currently using SQL query then to find the item list and other data before throwing them to report engine. Am I right?

If you wanna keep the SQL query, one alternative is to use arrays on your report script. That is, array of sub assembly, array of sub assy demand forecast, array of FG, and array of FG demand forecast. But this is gonna be quite a complicated programming.
Would it be possible at all to move part of your query from your report script to your SQL query? For example, could you move the query for sub assy from report script to SQL query. That would eradicate the need for sub assy and sub assy demand forecast arrays.

I'm just wondering, do you have a base component used in 2 different sub assy, and later these 2 are assembled into one FG. If you do, this indeed is going to complicate things even more because you have to prevent this FG from appearing twice

One last thing, do you find the rough material requirements by critical item report unusable for your case? Just checking

Surely having development license would help a lot since you can just push all the sub assy and FG query right to prog script. Anybody else has another method ?

Rgds,
Paul
Reply With Quote
  #11  
Old 4th November 2002, 04:38
evertsen's Avatar
evertsen evertsen is offline
Guru
 
Join Date: Jun 2002
Location: Canada
Posts: 308
evertsen is on a distinguished road
Baan: IVc4 - DB: Oracle - OS: HP-UX
new session?

Paul P,

Looks like Itannous does have a development license or I don't think he would be able to write a report script (although I don't know this for fact). I believe the best course of action would be to write another session to meet the needs of this report. This is not a very hard thing to do. The hard part is already done because the current report script could be used as the program script for the new session (as you suggest) and probably even simplified. That would be my advice anyway, as I don't have source either and have come across similar problems trying to use a standard session to print a more complicated report than was originally written for it.
__________________
Hope this helps,
Ev
Reply With Quote
  #12  
Old 4th November 2002, 18:15
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
Development License

We do have a license, but I don't have source code for the session Pual metioned.

I have created the session script to gather the initial data and also included the report script below.

We did not want to set up these as critical items because we need to run this for various items and do not want it to effect our mrp/mps run.
The sub assembly is used once in a f.g. item. But it can be used in many f.goods.


Here is the session script:
Code:
declaration:

	table	ttdpsc001	| Purchase Contracts
	table	ttdpsc013	| Supplier Profile
	table	ttiitm001	| Items
	table	ttcmcs072	| Contract Analysis Codes
	table	ttcmcs002	| Currencies
	table	ttcmcs001	| Units
	table	ttcmcs070	| Contract Status Codes
	table	ttcmcs003	| Warehouses
	table	ttcmcs071	| Label Types
	table	ttcmcs046	| Languages
	table	ttcmcs074	| Receiving Pattern Descriptions
	table	ttccom001	| Employees
	table   ttibom010	| BOMs
	extern	domain	ttyeno        	txta.txt
	extern	domain	ttyeno        	txti.txt

	extern	domain	tcitem        	item.f	fixed
	extern	domain	tcitem        	item.t	fixed
	extern	domain	tcsuno        	suno.f	fixed
	extern	domain	tcsuno        	suno.t	fixed
	extern	domain	tccono        	cont.f
	extern	domain	tccono        	cont.t
	extern	domain	tdpsc.pono    	pono.f
	extern	domain	tdpsc.pono    	pono.t
	extern	domain	ttyeno        	txta.txt
	extern	domain	ttyeno        	txti.txt
	extern	domain	tcdsca        	item.dsca    
	extern	domain	tcdsca        	anl1.dsca    
	extern	domain	tcdsca        	anl2.dsca    
	extern	domain	tcdsca        	cuqp.dsca    
	extern	domain	tcdsca        	cupp.dsca    
	extern	domain	tcdsca        	atyp.dsca    
	extern	domain	tcdsca        	btyp.dsca    
	extern	domain	tcdsca        	mtyp.dsca    
	extern	domain	tcdsca        	utyp.dsca    
	extern	domain	tcdsca        	aodl.dsca    
	extern	domain	tcdsca        	bodl.dsca    
	extern	domain	tcdsca        	modl.dsca    
	extern	domain	tcdsca        	uodl.dsca    
	extern  domain  tcdate 		date.f
	extern  domain  tcdate          date.t
	extern  domain  timps.plnc      plnc.f
	extern  domain  timps.plnc	plnc.t
	extern  domain  timps.plvl	plvl.f
	extern  domain  timps.plvl	plvl.t
	extern  domain  timps.plvl      livel
	extern  domain  timps.plnc      livec



|****************************** form section **********************************

form.1:
init.form:
	get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:
	execute(print.data)

choice.print.data:
on.choice:
	if rprt_open() then
		read.main.table()
		rprt_close()
	else
		choice.again()
	endif


|****************************** field section *********************************

field.item.f:
when.field.changes:
	item.t = item.f

before.zoom:
	tiitm001.item = item.f

field.item.t:
before.zoom:
	tiitm001.item = item.t

field.suno.f:
when.field.changes:
	suno.t = suno.f

field.cont.f:
when.field.changes:
	cont.t = cont.f

field.pono.f:
when.field.changes:
	pono.t = pono.f


field.plnc.f:
when.field.changes:
 
	plnc.t = plnc.f


field.plvl.f:
when.field.changes:
	
	plvl.t = plvl.f








|****************************** function section ******************************

functions:

function read.main.table()
{
	select	tdpsc001.*, tdpsc013.*, titem.dsca:item.dsca, tanl1.dsca:anl1.dsca, 
		tanl2.dsca:anl2.dsca, tcmcs002.*, tcuqp.dsca:cuqp.dsca, 
		tcupp.dsca:cupp.dsca, tatyp.dsca:atyp.dsca, tbtyp.dsca:btyp.dsca, 
		tmtyp.dsca:mtyp.dsca, tutyp.dsca:utyp.dsca, tcmcs070.*, 
		tcmcs003.*, taodl.dsca:aodl.dsca, tbodl.dsca:bodl.dsca, 
		tmodl.dsca:modl.dsca, tuodl.dsca:uodl.dsca, tcmcs046.*, 
		tcmcs074.*, tccom001.*, tibom010.*
	from	tdpsc001, tdpsc013, tiitm001 titem, tcmcs072 tanl1, 
		tcmcs072 tanl2, tcmcs002, tcmcs001 tcuqp, tcmcs001 tcupp, 
		tiitm001 tatyp, tiitm001 tbtyp, tiitm001 tmtyp, tiitm001 tutyp, 
		tcmcs070, tcmcs003, tcmcs071 taodl, tcmcs071 tbodl, tcmcs071 tmodl, 
		tcmcs071 tuodl, tcmcs046, tcmcs074, tccom001, tibom010
	where   tdpsc001._index3 inrange {:item.f, :suno.f, :cont.f, 
		:pono.f}
	                     and {:item.t, :suno.t, :cont.t, :pono.t}
	and	tdpsc001.suno refers to tdpsc013
	and 	tdpsc001.item refers to titem
	and 	tdpsc001.anl1 refers to tanl1
	and 	tdpsc001.anl2 refers to tanl2
	and	tdpsc001.ccur refers to tcmcs002
	and 	tdpsc001.cuqp refers to tcuqp
	and 	tdpsc001.cupp refers to tcupp
	and 	tdpsc001.atyp refers to tatyp
	and 	tdpsc001.btyp refers to tbtyp
	and 	tdpsc001.mtyp refers to tmtyp
	and 	tdpsc001.utyp refers to tutyp
	and	tdpsc001.stat refers to tcmcs070
	and	tdpsc001.cwar refers to tcmcs003
	and 	tdpsc001.aodl refers to taodl
	and 	tdpsc001.bodl refers to tbodl
	and 	tdpsc001.modl refers to tmodl
	and 	tdpsc001.uodl refers to tuodl
	and	tdpsc001.clan refers to tcmcs046
	and	tdpsc001.ship refers to tcmcs074
	and	tdpsc001.cpln refers to tccom001
	and	tibom010.sitm = tdpsc001.item	
	and     tdpsc001.stat = "LV"
	order by tdpsc001._index3
	selectdo
		rprt_send()
	endselect
}
Reply With Quote
  #13  
Old 5th November 2002, 02:57
Paul P's Avatar
Paul P Paul P is offline
Guru
 
Join Date: Jul 2002
Location: Jakarta, Indonesia
Posts: 797
Paul P is on a distinguished road
Baan: BaanIV, BaanERP - DB: SQL Server, Informix, Oracle - OS: Windows, HP-UX
Smile Move some codes from rep script to prog script

Dear Itannous,

Well, it's great then! All you have to do is

- Move the double query on tibom010 from report script to selectdo of your prog script. This will make prog script look for all sub assy and FG you want before sending to report engine

- Move the second query on tibom010 (finding FG) inside the first query on tibom010 (finding sub assy). This will make sure you find every combination of sub assy and FG used by the component

- Move the rprt_send inside the second query to tibom010 (finding FG). Hence you will be sending every time new combination of sub assy and FG is found (this is what you want, right?)

- As a result, every time a particular detail section, say detail.1, is printed, it will be on new component-sub assy-FG combo. You can then just leave demand forecast calc of the FG in the report script of that detail section.

After all of this, you'll have something very similar to rough material requirements by critical item that Baan provides. Are you sure you don't want to use that instead? Pls let know how it work out. Thanks

Rgds,
Paul

PS.
Ev, anything I miss that you might want to add? Thanks
Reply With Quote
  #14  
Old 5th November 2002, 16:02
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
Paul - This session is getting looped.

Not sure if I followed your insturctions exactly. This is what I have added to the session script

I will send as an attachment
Attached Files
File Type: txt script.txt (5.8 KB, 54 views)
Reply With Quote
  #15  
Old 6th November 2002, 02:10
Paul P's Avatar
Paul P Paul P is offline
Guru
 
Join Date: Jul 2002
Location: Jakarta, Indonesia
Posts: 797
Paul P is on a distinguished road
Baan: BaanIV, BaanERP - DB: SQL Server, Informix, Oracle - OS: Windows, HP-UX
Smile Already query for sub assy in main select

Dear Itannous,

I've just started to look at your main select. You selected the sub assy already in there. I saw that you already include tibom010 in the main select. Then all you have to do is have one select inside the main select to find the FG, not double select. BTW, you made sure that all your BOMs are 2 levels only (base comp->sub assy->FG), right? Also, you made sure that only base component will be passed to the main select, right? I don't know the table structure of the customised PSC module, hence I don't know whether these checks have been performed

Also pls remove the additional tiitm001 query on base component's inventory unit in selectdo of main select. You can perform this right in the main select since you already include tiitm001 of base component there.

It's now getting a bit too complicated, don't you think? What do you think about giving OmeLuuk's suggestion a try, ie get the source code of tibom1413m000 and start from there?

Rgds,
Paul
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
Variable value different in pgm. script & report script ? PacSun Tools Development 5 28th June 2006 19:09
Problem in Compiling the Script of a report Sudipta Tools Development 4 20th October 2004 07:47
problem using 'IN' in report script dansimmo Tools Development 3 9th January 2003 00:08
report script problem evertsen Tools Development 8 5th October 2002 06:00


All times are GMT +2. The time now is 15:36.


©2001-2017 - Baanboard.com - Baanforums.com