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
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 30th May 2003, 07:15
veyant veyant is offline
Senior Member
 
Join Date: May 2003
Location: Gurgaon
Posts: 107
veyant is on a distinguished road
Baan: Baan IV/V - DB: Oracle - OS: Unix/AIX
Help in recification fo the Script Code

HI ,

Can anybody help me in getting the code given below rectified as it is not working. Actually i am working on areport session in which i have to generate a report on total store credit done in the year of the fG items in monthly buckets.

report will look like..

Item Jan Feb Mar April May ............ dec
A 25 75 30 15 25 70
B
C
D
E

I have written a script for this by using tdinv700, tiitm001 & tfgld005 tables but there is some problem somewhere it is not working fine as it is repeating same values for all items.

code is :


|******************************************************************************
|* tdinv0700sm01 0 VRC B40l c4 hon
|* Store Credit Report
|* applic
|* 28-05-03 [16:24]
|******************************************************************************
|* Script Type: 4
|******************************************************************************

|****************************** DECLARATION SECTION ***************************
declaration:
table ttdinv700
table ttiitm001
table ttfgld005
long monthcounter
| long tq1,tq2,tq3,tq4,tq5,tq6,tq7,tq8,tq9,tq10,tq11,tq12
| string t(12)
| extern domain tcqnty monthcounter
extern domain tcqnty tq1
extern domain tcqnty tq2
extern domain tcqnty tq3
extern domain tcqnty tq4
extern domain tcqnty tq5
extern domain tcqnty tq6
extern domain tcqnty tq7
extern domain tcqnty tq8
extern domain tcqnty tq9
extern domain tcqnty tq10
extern domain tcqnty tq11
extern domain tcqnty tq12
extern domain tcqnty total
extern domain tcdate fromdate
extern domain tcdate enddate
extern domain tcyrno year
|****************************** PROGRAM SECTION ***************************
|****************************** ZOOM FROM SECTION ***************************
|****************************** FORM SECTION ***************************

|****************************** CHOICE SECTION ***************************
choice.cont.process:
on.choice:
t=""
monthcounter=1
tq1=0
tq2=0
tq3=0
tq4=0
tq5=0
tq6=0
tq7=0
tq8=0
tq9=0
tq10=0
tq11=0
tq12=0
total=0
execute(print.data)

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

|****************************** FIELD SECTION ***************************
|****************************** MAIN TABLE SECTION ***************************
|****************************** FUNCTION SECTION ***************************i
functions:

function read.main.table()
{
select tiitm001.* from tiitm001
where tiitm001.cwar = "FG1"
selectdo
compute.month()
rprt_send()
endselect
total = tq1 + tq2 + tq3 + tq4 + tq5 + tq6 + tq7 + tq8+tq9+tq10+tq11+tq12
}

function compute.month()
{
select tfgld005.* from tfgld005
where tfgld005.year = :year
selectdo
fromdate = tfgld005.stdt
select tfgld005.* from tfgld005
where tfgld005.year = :year and
tfgld005.stdt > :fromdate
selectdo
enddate = tfgld005.stdt
break
endselect
calculate.data()
calculate.quantity()
endselect

}

function calculate.data()
{
select tdinv700.* from tdinv700
where tdinv700.trdt >= :fromdate and
tdinv700.trdt < :enddate and
tdinv700.item = :tiitm001.item and
tdinv700.cwar = "FG1" and
tdinv700.kost = tckost.prd.receipt and
( tdinv700.koor = tckoor.wrh.order or
tdinv700.koor = tckoor.act.sfc )
selectdo
total = total + tdinv700.quan
endselect
}


function calculate.quantity()
{
on case monthcounter
case 1: tq1 = total
break
case 2: tq2 = total
break
case 3: tq3 = total
break
case 4: tq4 = total
break
case 5: tq5 = total
break
case 6: tq6 = tq6 + tdinv700.quan
break
case 7: tq7 = tq7 + tdinv700.quan
break
case 8: tq8 = tq8 + tdinv700.quan
break
case 9: tq9 = tq9 + tdinv700.quan
break
case 10: tq10 = tq10 + tdinv700.quan
break
case 11: tq11 = tq11 + tdinv700.quan
break
case 12: tq12 = tq12 + tdinv700.quan
endcase
monthcounter= monthcounter + 1
total =0
}


it is very urgent ..i a working on it from last three days and result is still 0. it can harm by performance in management eyes.

Hope a fast response.
Sandy
__________________
veyant
Reply With Quote
  #2  
Old 30th May 2003, 10:05
NvanBeest's Avatar
NvanBeest NvanBeest is offline
Guru
 
Join Date: May 2003
Location: South Africa
Posts: 521
NvanBeest is on a distinguished road
Baan: BaanIVc4 - DB: Oracle, TBase, SQL Server - OS: AIX, Linux, Window$
It seems that you are not iterating the months. My approach would be:

Code:
select tiitm001.item
where tiitm001.cwar="FG1"
selectdo
   for i = 1 to 12
      compute.month()
   endfor
   total = ...
   rprt_send()
endselect

Also, in compute.month() you do not include the month in the query, thus always getting the first period's start date. Probably best to make the month number a parameter to the function.

Lastly, for performance, do not use all those select * statements, but only select the fields you need!

Regards,
Nico
Reply With Quote
  #3  
Old 30th May 2003, 10:11
isimeon isimeon is offline
Member
 
Join Date: Sep 2001
Posts: 96
isimeon is on a distinguished road
Baan: BAAN IV c3/4 - DB: Oracle 7.x/8.x - OS: Windows NT/2000
Modified function compute.month

Code:
function compute.month()
{
	select tfgld005.* from tfgld005
	where tfgld005.year = :year
	order by tfgld005._index1
	selectdo
		fromdate = tfgld005.stdt
		select tfgld005.*
		from tfgld005
		where tfgld005.year = :year and
		tfgld005.stdt > :fromdate
		order by tfgld005.stdt
		as set with 1 rows
		selectdo
			enddate = tfgld005.stdt
		selectempty
			enddate = date.to.num(year,31,12)
		endselect
		monthcounter=tfgld005.prod
		calculate.data()
		calculate.quantity()
	endselect
}
Reply With Quote
Sponsored Links
  #4  
Old 30th May 2003, 10:19
NvanBeest's Avatar
NvanBeest NvanBeest is offline
Guru
 
Join Date: May 2003
Location: South Africa
Posts: 521
NvanBeest is on a distinguished road
Baan: BaanIVc4 - DB: Oracle, TBase, SQL Server - OS: AIX, Linux, Window$
No! Will still return the first month only! You need:

Code:
select tfgld005.stdt
where tfgld005.year=:year
<b>and tfgld005.prod=:monthcounter</b>
...

Just something interesting: the from <table> is not needed when the query explicitly selects fields specified with <table>.<field>! Only necessary when using aliases:

Code:
select a.stdt
from tfgld005 a
where a.year=...

Regards,
Nico
Reply With Quote
  #5  
Old 30th May 2003, 10:41
isimeon isimeon is offline
Member
 
Join Date: Sep 2001
Posts: 96
isimeon is on a distinguished road
Baan: BAAN IV c3/4 - DB: Oracle 7.x/8.x - OS: Windows NT/2000
I don't think so.

For every year in the table tfgld005 exists 12 records (if you have 12 periods) for every type of period. In my function select will return 36 records. This is wrong and additional where clause needed for fiscal type period only:



Code:
function compute.month()
{
	select tfgld005.*
	from tfgld005
	where tfgld005._index1={tfgld.ptyp.financial,:year}
	order by tfgld005._index1
	selectdo
		fromdate = tfgld005.stdt
		select tfgld005.stdt
		from tfgld005
		where tfgld005.year = :year and
		tfgld005.stdt > :fromdate
		order by tfgld005.stdt
		as set with 1 rows
		selectdo
			enddate = tfgld005.stdt-1
		selectempty
			enddate = date.to.num(year,31,12)
		endselect
		monthcounter=tfgld005.prod
		calculate.data()
		calculate.quantity()
	endselect
}
Reply With Quote
  #6  
Old 30th May 2003, 10:59
NvanBeest's Avatar
NvanBeest NvanBeest is offline
Guru
 
Join Date: May 2003
Location: South Africa
Posts: 521
NvanBeest is on a distinguished road
Baan: BaanIVc4 - DB: Oracle, TBase, SQL Server - OS: AIX, Linux, Window$
Ok, sorry! My mistake. I overlooked the fact that you call the calculate functions from the selectdo. But, the inner query will destroy the outer's result set (same table!), and you might end up with less than twelve iterations? Maybe necessary to add a "as prepared set" to the outer query.

Regards,
Nico
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
Code: Sample Qkey Script mark_h Code & Utilities 7 9th December 2013 15:33
NEW: vbcode CODE extensions patvdv About Baanboard.com 3 13th March 2003 12:19
CODE: Removing unwanted items from Baan Francesco Code & Utilities 0 17th January 2003 20:42
CODE: AWKward Script for Baan Reports' passage to MS Excel sitarammani Code & Utilities 19 31st August 2002 17:50


All times are GMT +2. The time now is 10:13.


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