Baanboard.com

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

User login

Frontpage Sponsor

Main

Google search


Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
33%
Installation Wizard into new VRC
39%
Manual into existing VRC
6%
Manual into new VRC
22%
Total votes: 49

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 25th January 2005, 18:15
rmarles's Avatar
rmarles rmarles is offline
Senior Member
 
Join Date: Sep 2003
Location: London, Ontario
Posts: 112
rmarles is on a distinguished road
Baan: 4c4 sp20 - DB: oracle 10g - OS: hpux 11i
solving recursion problems

Hello.

Running Triton 3.1. Any help with the below code is greatly appreciated.

Trying to write a session to explode custom boms and the standard boms contained within (including phantom boms)

Obviously since "Recursion not yet implemented", I need to come up with another way of doing this.

I found one post that suggested using stacks, so I rewrote my code to do so.

I have the session working without getting the dreaded "Recursion" message, but when it explodes a standard BOM, I get a message "Error: Query should be in Exec Status or Eval Status".

It explodes the first component of the standard bom, dumps the error and returns back one level.

The below "report" is a sample output.

Part 123456-L2-4 at position 40 should actually have three BOM lines below it, and it only shows 1.
Code:
Date   : 01-25-05 [11:27]                                     CUSTOMIZED BOMS                                       Page      :    1
                                                                                                                    Company   :  100
                                                                                                          tipcsc944m000/pcsc94411000
------------------------------------------------------------------------------------------------------------------------------------
Project          : 123456                                              Status    : Active
                                                                       Start Date:
Customer         :                                                     Compl.Dte : 01-07-05
Manufactured Item: 1234567890123456                                    Variant   :                                       Unit:
Revision         :
------------------------------------------------------------------------------------------------------------------------------------
Level   |Pos.|Item            |Description       | Revi-|IC|   Length|    Width|No.o|  Net Quantity|Un.|Scp|Wrh|Opr|Ph.|No.|Buyer/
        |    |                |                  |  sion|  |    [   ]|    [   ]| Un.|              |   |[ ]|   |   |tom|Nts|Planner
--------+----+----------------+------------------+------+--+---------+---------+----+--------------+---+---+---+---+---+---+--------
1       |  10|123456-L1-1     |                  |   n/a|Cu|     0.00|     0.00|   0|        1.0000|   |  0|001|  0|Yes|  0|     0
.2      |  10|123456-L2-1     |                  |   n/a|St|     0.00|     0.00|   0|        1.0000|   |  0|001|  0|No |  0|     0
.2      |  20|123456-L2-2     |                  |   n/a|St|     0.00|     0.00|   0|        2.0000|   |  0|001|  0|No |  0|     0
.2      |  30|123456-L2-3     |                  |   n/a|St|     0.00|     0.00|   0|        1.0000|   |  0|001|  0|No |  0|     0
.2      |  40|123456-L2-4     |                  |   n/a|St|     0.00|     0.00|   0|        1.0000|   |  0|001|  0|Yes|  0|     0
..3     |  10|123456-L3-1     |                  |   n/a|St|     0.00|     0.00|   0|        1.0000|   |  0|001|  0|No |  0|     0
.2      |  50|123456-L2-5     |                  |   n/a|St|     0.00|     0.00|   0|        1.0000|   |  0|001|  0|Yes|  0|     0
..3     |  10|123456-L3-1     |                  |   n/a|St|     0.00|     0.00|   0|        1.0000|   |  0|001|  0|No |  0|     0
.2      |  60|123456-L2-6     |                  |   n/a|St|     0.00|     0.00|   0|        1.0000|   |  0|001|  0|Yes|  0|     0
Anyways, here's the code, minus some things such as revision/effectivity checking, etc. I was concentrating more on the basics (getting the BOM to explode all the levels).
Code:
function read.main.table()
{
	l.count = 1
	select	tipcs021.*, tipcs020.*, tccom010.*
	from	tipcs021, tipcs020, tccom010
	where	tipcs021._index1 =
		{:cprj.f, :item.f}
	and	tipcs021.cprj refers to tipcs020
	and	tipcs020.cuno refers to tccom010
	as prepared set
	selectdo
		mitm.cprj = tipcs020.cprj
		mitm.cprj.dsca = tipcs020.dsca
		mitm.item = tipcs021.item
		mitm.dsca = tipcs021.dsca
		mitm.cuno = tipcs020.cuno
		mitm.cuno.nama = tccom010.nama
		mitm.start = tipcs020.sdat
		mitm.close = tipcs020.ddat
		mitm.psts = tipcs020.psts
		
		stack.cprj(1,l.count) = tipcs021.cprj
		stack.item(1,l.count) = tipcs021.item
		read.custom.bom()
		
	endselect
}

function print.line()
{
	b.level = string.set$(".",l.count-2)&str$(l.count-1)
	rprt_send()
}

function read.custom.item()
{
	in.item = stack.item(1,l.count)
	in.cprj = stack.cprj(1,l.count)
	
	select	tipcs021.*
	from	tipcs021
	where	tipcs021._index1 =
		{:in.cprj, :in.item}
	as prepared set
	selectdo
		sitm.dsca = tipcs021.dsca
		sitm.revi = "n/a"
		print.line()
		read.custom.bom()
	endselect
}

function read.standard.item()
{
	in.item = stack.item(1,l.count)
	in.cprj = stack.cprj(1,l.count)
	
	select	tiitm001.*
	from	tiitm001
	where	tiitm001._index1 =
		{:in.item}
	as prepared set
	selectdo
		sitm.dsca = tiitm001.dsca
		sitm.revi = "n/a"
		print.line()
		read.standard.bom()
	endselect
}

function read.custom.bom()
{	
	
	in.item = stack.item(1,l.count)
	in.cprj = stack.cprj(1,l.count)
	
	l.count = l.count + 1
	select	tipcs022.*
	from	tipcs022
	where	tipcs022._index1 =
		{:in.cprj, :in.item}
	as prepared set
	selectdo
		sitm.item = tipcs022.sitm
		sitm.pono = tipcs022.pono
		sitm.opol = tipcs022.opol
		sitm.leng = tipcs022.leng
		sitm.widt = tipcs022.widt
		sitm.noun = tipcs022.noun
		sitm.qana = tipcs022.qana
		sitm.scpf = tipcs022.scpf
		sitm.cwar = tipcs022.cwar
		sitm.opno = tipcs022.opno
		sitm.cpha = tipcs022.cpha
		sitm.nnts = tipcs022.nnts
		
		stack.cprj(1,l.count) = tipcs022.cprj
		stack.item(1,l.count) = tipcs022.sitm
		
		if tipcs022.opol = tcittb.standard then
			sitm.opol = "St"
			read.standard.item()
		else
			sitm.opol = "Cu"
			read.custom.item()
		endif
	endselect
	l.count = l.count - 1
}

function read.standard.bom()
{
	in.item = stack.item(1,l.count)
	in.cprj = stack.cprj(1,l.count)
	
	l.count = l.count + 1
	select	tibom010.*
	from	tibom010
	where	tibom010._index1 =
		{:in.item}
	as prepared set
	selectdo
		sitm.item = tibom010.sitm
		sitm.pono = tibom010.pono
		sitm.opol = "St"
		sitm.leng = tibom010.leng
		sitm.widt = tibom010.widt
		sitm.noun = tibom010.noun
		sitm.qana = tibom010.qana
		sitm.scpf = tibom010.scpf
		sitm.cwar = tibom010.cwar
		sitm.opno = tibom010.opno
		sitm.cpha = tibom010.cpha
		sitm.nnts = tibom010.nnts
		
		stack.cprj(1,l.count) = ""
		stack.item(1,l.count) = tibom010.sitm
				
		read.standard.item()
	endselect
	l.count = l.count - 1
}
Reply With Quote
  #2  
Old 25th January 2005, 18:40
lbencic's Avatar
lbencic lbencic is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, IL, USA
Posts: 1,148
lbencic will become famous soon enough
Baan: 2.2d - LN - DB: most - OS: most
Try a search on recursion, there are several posts on this. Here's a recent one with BOM example.

Link to Recursion post
Reply With Quote
  #3  
Old 25th January 2005, 21:23
rmarles's Avatar
rmarles rmarles is offline
Senior Member
 
Join Date: Sep 2003
Location: London, Ontario
Posts: 112
rmarles is on a distinguished road
Baan: 4c4 sp20 - DB: oracle 10g - OS: hpux 11i
Yay!

Well
I think I have it working. The session explodes the custom boms and the standard boms within.

Thanks to chjagge and his post.

I was able to disect part of what he'd done and apply it here.

I'm working on things like revision control & effectivity now.

Here's the code. Note that effectivity isn't working 100% yet. If you need help deciphering, please send me a message.
Code:
|******************************************************************************
|* tipcsc944  0  VRC 3.1C b  tro1
|* Print Customized Multilevel BOMs
|* ROB MARLES                    
|* 2005-01-24
|* Thanks to chjagge's post on BaanBoard
|* <a href="http://www.baanboard.com/baanboard/showpost.php?p=81285&postcount=7" target="_blank">http://www.baanboard.com/baanboard/s...85&postcount=7</a>
|******************************************************************************
|* Main table tipcs021 Customized Items, Form Type 4
|******************************************************************************
                                                                                
|****************************** declaration section ***************************
declaration:

	table	ttipcs021	| Customized Items
	table	ttipcs020	| Projects
	table	ttiitm001	| Item Data
	table	ttiedm100	| Revision Data
	table	ttccom010	| Customers
	table	ttibom010	| Production BOMs
	table	ttipcs022	| Custom BOMs

	extern	domain	tccprj        	cprj.f	fixed	| form
	extern	domain	tccprj        	cprj.t	fixed	| form
	extern	domain	tcitem        	item.f	fixed	| form
	extern	domain	tcitem        	item.t	fixed	| form
	extern	domain	tidate		eff.date	| form
	extern	domain	tcyesno		exp.non.phan.boms	| form

	extern	domain	tcmcs.str8	b.level		| report 
	extern	domain	tidate    	mitm.close	| report
	extern	domain	tccprj    	mitm.cprj	| report
	extern	domain	tcdsca    	mitm.cprj.dsca	| report
	extern	domain	tccpva    	mitm.cpva	| report
	extern	domain	tccuni    	mitm.cuni	| report
	extern	domain	tccuno    	mitm.cuno	| report
	extern	domain	tcnama    	mitm.cuno.nama	| report
	extern	domain	tcdsca    	mitm.dsca	| report
	extern	domain	tcitem    	mitm.item	| report
	extern	domain	tcpsts    	mitm.psts	| report
	extern	domain	tiedm.revi	mitm.revi	| report
	extern	domain	tidate    	mitm.start	| report
	extern	domain	tcyesno   	sitm.cpha	| report
	extern	domain	tcemno    	sitm.cplb	| report
	extern	domain	tccuni    	sitm.cuni	| report
	extern	domain	tccwar    	sitm.cwar	| report
	extern	domain	tcdsca    	sitm.dsca	| report
	extern	domain	tcitem    	sitm.item	| report
	extern	domain	tcleng    	sitm.leng	| report
	extern	domain	tisfc.nnts	sitm.nnts	| report
	extern	domain	tcnoun    	sitm.noun	| report
	extern	domain	tcopno    	sitm.opno	| report
	extern	domain	tcpono    	sitm.pono	| report
	extern	domain	tcqana    	sitm.qana	| report
	extern	domain	tiedm.revi	sitm.revi	| report
	extern	domain	tcscpf    	sitm.scpf	| report
	extern	domain	tcwidt    	sitm.widt	| report
	extern	domain	tcmcs.str2	sitm.type	| report
	extern	domain	tcittb		sitm.opol	| report
	extern	domain	tccprj		sitm.cprj	| report

		domain	tccprj		stack.cprj(99)	| internal
		domain	tcitem		stack.item(99)	| internal
		domain	tcitem		in.item		| internal
		domain	tccprj		in.cprj		| internal
			long		sql.x		| internal
			long		sql.by.level(99)| internal
			long		l.count		| internal
			
	#DEFINE MAX.BOM.LEVEL 99
			

|****************************** 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.cprj.f:
when.field.changes:
	cprj.t = cprj.f

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


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

functions:

function read.main.table()
{	
	
	
	select	tipcs021.*, tipcs020.*, tccom010.*
	from	tipcs021, tipcs020, tccom010
	where	tipcs021._index1 inrange
		{:cprj.f, :item.f}
	and	{:cprj.t, :item.t}
	and	tipcs021.cprj refers to tipcs020
	and	tipcs020.cuno refers to tccom010
	as prepared set
	selectdo
		reset.variables()
		l.count = 1
		mitm.cprj = tipcs020.cprj
		mitm.cprj.dsca = tipcs020.dsca
		mitm.item = tipcs021.item
		mitm.dsca = tipcs021.dsca
		mitm.cuno = tipcs020.cuno
		mitm.cuno.nama = tccom010.nama
		mitm.start = tipcs020.sdat
		mitm.close = tipcs020.ddat
		mitm.psts = tipcs020.psts
		mitm.cpva = tipcs021.cpvr
		mitm.cuni = tipcs021.cuni
		if tipcs021.eitm = tcyesno.yes then
			mitm.revi = get.item.revision(tipcs021.item)
		else
			mitm.revi = ""
		endif
		
		stack.cprj(1,l.count) = tipcs021.cprj
		stack.item(1,l.count) = tipcs021.item
		read.custom.bom()
		
	endselect
}

function print.line()
|Expands the BOM Level with a decimal leader for the report.
{
	b.level = string.set$(".",l.count-2)&str$(l.count-1)
	rprt_send()
}

function read.custom.item()
|Reads the Custom Item Description for the report.
|This also invokes the next BOM level if there is one.
{
	in.item = stack.item(1,l.count)
	in.cprj = stack.cprj(1,l.count)
	
	select	tipcs021.*
	from	tipcs021
	where	tipcs021._index1 =
		{:in.cprj, :in.item}
	as prepared set
	selectdo
		sitm.dsca = tipcs021.dsca
		sitm.cplb = tipcs021.cplb
		sitm.cuni = tipcs021.cuni
		if tipcs021.eitm = tcyesno.yes then
			sitm.revi = get.item.revision(in.item)
		else
			sitm.revi = ""
		endif
		print.line()
	endselect
	if tipcs021.cpha = tcyesno.no then
		if exp.non.phan.boms = tcyesno.yes then
			read.custom.bom()
		endif
	else
		read.custom.bom()
	endif
}

function read.standard.item()
|Reads the Standard Item Description for the report.
|This also invokes the next BOM level if there is one.
{
	in.item = stack.item(1,l.count)
	in.cprj = stack.cprj(1,l.count)
	
	select	tiitm001.*
	from	tiitm001
	where	tiitm001._index1 =
		{:in.item}
	as prepared set
	selectdo
		sitm.dsca = tiitm001.dsca
		sitm.cplb = tiitm001.cplb
		sitm.cuni = tiitm001.cuni
		if tiitm001.eitm = tcyesno.yes then
			on case tiitm001.kitm
				case tckitm.purchase:
					sitm.revi = 
						get.item.revision(tiitm001.seab)
					break
				case tckitm.manufacture:
					sitm.revi =
						get.item.revision(tiitm001.item)
					break
			endcase
		else
			sitm.revi = ""
		endif
		print.line()
	endselect
	if tipcs021.cpha = tcyesno.no then
		if exp.non.phan.boms = tcyesno.yes then
			read.standard.bom()
		endif
	else
		read.standard.bom()
	endif	
}

function domain tiedm.revi get.item.revision(domain tcitem item.l)
{
	select	tiedm100.*
	from	tiedm100
	where	tiedm100._index1 =
		{:item.l}
	and	tiedm100.indt <= :eff.date
	and 	((tiedm100.exdt > :eff.date)
	or	(tiedm100.exdt = 0))
	order by tiedm100._index1 desc as set with 1 rows
	selectdo
	endselect
	return(tiedm100.revi)
}

function read.custom.bom()
|Reads the Custom Bill and invokes the appropriate "item" function.
{	
	
	in.item = stack.item(1,l.count)
	in.cprj = stack.cprj(1,l.count)
	
	l.count = l.count + 1
	
	sql.x=sql.parse("select tipcs022.cprj, " &
			"tipcs022.sitm, " &
			"tipcs022.pono, " &
			"tipcs022.leng, " &
			"tipcs022.widt, " &
			"tipcs022.noun, " &
			"tipcs022.qana, " &
			"tipcs022.scpf, " &
			"tipcs022.cwar, " &
			"tipcs022.opno, " &
			"tipcs022.cpha, " &
			"tipcs022.opol, " &
			"tipcs022.nnts " &
			"from	tipcs022 " &
			"where	tipcs022._index1 = {:1, :2} " &
			"as prepared set")
			
	sql.where.bind(sql.x,1,in.cprj)
	sql.where.bind(sql.x,2,in.item)
	sql.by.level(l.count) = sql.x
	sql.exec(sql.x)
	while	(sql.fetch(sql.x) = 0)
		sitm.cprj = tipcs022.cprj
		sitm.item = tipcs022.sitm
		sitm.pono = tipcs022.pono
		sitm.leng = tipcs022.leng
		sitm.widt = tipcs022.widt
		sitm.noun = tipcs022.noun
		sitm.qana = tipcs022.qana
		sitm.scpf = tipcs022.scpf
		sitm.cwar = tipcs022.cwar
		sitm.opno = tipcs022.opno
		sitm.cpha = tipcs022.cpha
		sitm.opol = tipcs022.opol
		sitm.nnts = tipcs022.nnts
	
		stack.cprj(1,l.count) = sitm.cprj
		stack.item(1,l.count) = sitm.item
		
		if sitm.opol = tcittb.standard then
			sitm.type = "St"
			read.standard.item()
		else
			sitm.type = "Cu"
			read.custom.item()
		endif
	endwhile
	
	sql.close(sql.x)

	l.count = l.count - 1
	
	sql.x = sql.by.level(l.count)
}

function read.standard.bom()
|Reads the Standard Bill and invokes the appropriate "item" function.
{
	in.item = stack.item(1,l.count)
	in.cprj = stack.cprj(1,l.count)
	
	l.count = l.count + 1

	sql.x=sql.parse("select tibom010.sitm, " &
			"tibom010.pono, " &
			"tibom010.leng, " &
			"tibom010.widt, " &
			"tibom010.noun, " &
			"tibom010.qana, " &
			"tibom010.scpf, " &
			"tibom010.cwar, " &
			"tibom010.opno, " &
			"tibom010.cpha, " &
			"tibom010.nnts, " &
			"tibom010.indt, " &
			"tibom010.exdt " &
			"from	tibom010 " &
			"where	tibom010._index1 = {:1} " &
			"as prepared set")
	sql.where.bind(sql.x,1,in.item)
	sql.by.level(l.count) = sql.x
	sql.exec(sql.x)
	while	(sql.fetch(sql.x) = 0)
		if not is.expired(tibom010.indt, tibom010.exdt) then	
			sitm.item = tibom010.sitm
			sitm.pono = tibom010.pono
			sitm.type = "St"
			sitm.leng = tibom010.leng
			sitm.widt = tibom010.widt
			sitm.noun = tibom010.noun
			sitm.qana = tibom010.qana
			sitm.scpf = tibom010.scpf
			sitm.cwar = tibom010.cwar
			sitm.opno = tibom010.opno
			sitm.cpha = tibom010.cpha
			sitm.nnts = tibom010.nnts
			
			stack.cprj(1,l.count) = ""
			stack.item(1,l.count) = tibom010.sitm
					
			read.standard.item()
		endif
	endwhile
	
	sql.close(sql.x)
	
	l.count = l.count - 1
	
	sql.x = sql.by.level(l.count)
}

function domain tcbool is.expired(domain tcdate indt.x, domain tcdate exdt.x)
|"Stolen" from chjagge's post on BaanBoard
|<a href="http://www.baanboard.com/baanboard/showpost.php?p=81285&postcount=7" target="_blank">http://www.baanboard.com/baanboard/s...85&postcount=7</a>
{
	if ((indt.x <= eff.date) and (eff.date < exdt.x or exdt.x = 0)) then
		return(false)
	else
		return(true)
	endif
}

function reset.variables()
|Incase Choice Again...
{
long	cur.rec

	for cur.rec = 1 to MAX.BOM.LEVEL
		stack.cprj(1,cur.rec) = ""
		stack.item(1,cur.rec) = ""
		sql.by.level(cur.rec) = 0
	endfor
	
	sql.x = 0
	l.count = 0
}

Last edited by rmarles : 26th January 2005 at 16:41. Reason: updated revision control section
Reply With Quote
Sponsored Links
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
Crossed recursion greasedman Tools Development 8 16th October 2007 11:32
Problems with updates zamarlab AFS/DDC/OLE: Function servers 1 24th May 2004 18:51
BIS - Problems kindermann Open World, Portal & Decision Manager 2 24th February 2004 10:38
CODE: Recursion is possible (example) afsfan Code & Utilities 0 11th March 2002 07:12


All times are GMT +2. The time now is 23:46.


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