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 23rd May 2013, 12:42
chris_kzn chris_kzn is offline
Member
 
Join Date: Aug 2005
Posts: 76
chris_kzn is on a distinguished road
Baan: IV - DB: SQL 2000 - OS: WINDOWS 2000
Talking Simple Report Script Date
Baan: Baan IVc4
C/S: Server

I know most of the BaaN gurus will think this is a very simple request. I have a report whereby I want to split the date into three columns, Year, Month, Day. I need this information to split the table field tdsls040.odat into these three columns.

My current report script is such:
declaration:
extern domain tcmcs.long year
extern domain tcmcs.long month
extern domain tcmcs.long date

detail.1:
before.layout:
date = date.to.num(year, month, date)
month = date.to.num(year, month, date)
year = date.to.num(year, month, date)

The table field tdsls040.odat is being passed by the program already, just not sure if I am suppose to have a select statement inside of the report script in order to use it or how would the above variables be able to identify that they need to obtain the information from the table field?

If anyone has a report script to share which reflects how to do this, this would be greatly appreciated, otherwise if anyone can give me the simple instructions I need to accomplish this task, then that would be great as well.

Thanks in advance.
Reply With Quote
  #2  
Old 23rd May 2013, 13:00
RedBatz's Avatar
RedBatz RedBatz is offline
Member
 
Join Date: Oct 2005
Location: Lisbon, Portugal
Posts: 61
RedBatz is on a distinguished road
Baan: Baan IV c4 - DB: MSSQL2005 - OS: W2003
Hi,

detail.1:
before.layout:
num.to.date(tdsls040.odat, year, month, date)


No need for another select if tdsls040.odat is being passed by the program already.

Regards,

RedBatz
Reply With Quote
  #3  
Old 23rd May 2013, 13:01
RedBatz's Avatar
RedBatz RedBatz is offline
Member
 
Join Date: Oct 2005
Location: Lisbon, Portugal
Posts: 61
RedBatz is on a distinguished road
Baan: Baan IV c4 - DB: MSSQL2005 - OS: W2003
correction:

num.to.date(tdsls040.odat, year, month, day)

RedBatz
Reply With Quote
  #4  
Old 23rd May 2013, 13:11
chris_kzn chris_kzn is offline
Member
 
Join Date: Aug 2005
Posts: 76
chris_kzn is on a distinguished road
Baan: IV - DB: SQL 2000 - OS: WINDOWS 2000
perfect

Thank you RedBatz,

This code worked perfectly.

Quote:
Originally Posted by RedBatz View Post
Hi,

detail.1:
before.layout:
num.to.date(tdsls040.odat, year, month, date)


No need for another select if tdsls040.odat is being passed by the program already.

Regards,

RedBatz
Reply With Quote
  #5  
Old 27th May 2013, 12:40
chris_kzn chris_kzn is offline
Member
 
Join Date: Aug 2005
Posts: 76
chris_kzn is on a distinguished road
Baan: IV - DB: SQL 2000 - OS: WINDOWS 2000
Smile ammendment

Ok, so now I have the following script, but the result duplicates the total if there are more than 1 sales order lines, how does one tell BaaN to only reflect one result as opposed to two or more:

declaration:
table ttdsls045
extern domain tcmcs.long year
extern domain tcmcs.long month
extern domain tcmcs.long date
extern domain tcamnt invoice.amnt

detail.1:
before.layout:
num.to.date(tdsls040.odat, year, month, date)

invoice.amnt = 0
select tdsls045.*
from tdsls045
where tdsls045._index1 = {:tdsls040.orno}
and tdsls045.invd > 0
selectdo
invoice.amnt = invoice.amnt + tdsls045.amnt
endselect

I have tried the the sqlcommand "as set with 1 rows" but this then causes the invoice amount to total the last order line and so resulting in an incorrect invoice amount.

Hope someone is able to assist me please?
Reply With Quote
  #6  
Old 27th May 2013, 18:55
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
See if there is a seqn for each line - something like seqn 0 and seqn>0 being something like receipts or invoices. I know for the purchasing receipt table you have to skip a sequence 0. We don't use the sls module, but it might be something like that.
__________________
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
  #7  
Old 28th May 2013, 06:27
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
Yes Mark,

There is seqn for Sales Orders as well. Chris you can try what Mark said and add an additional condition in where class to check if seqn <> 0

In LN we have a different table structure so I am not sure which fields exists in your table.
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #8  
Old 28th May 2013, 10:42
chris_kzn chris_kzn is offline
Member
 
Join Date: Aug 2005
Posts: 76
chris_kzn is on a distinguished road
Baan: IV - DB: SQL 2000 - OS: WINDOWS 2000
Thanks guys, going to have a look.

Ok, had a look, I cannot find anything of an seqn in either table tdsls040 or tdsls045. I have attached a copy of the two tables, including both records from tdsls045 for one specific order which I am noticing is beiing repeated.You guys might be able to spot the difference that I am missing.

Thank you guys in advance.
Attached Files
File Type: pdf tdsls045 and tdsls040 table fields.pdf (217.3 KB, 39 views)

Last edited by chris_kzn : 28th May 2013 at 10:58. Reason: Adding more content
Reply With Quote
  #9  
Old 28th May 2013, 12:24
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
Try this

Hi Chris,

I guess srnb is the field. Try with srnb > 0 or srnb <> 0.

Actually, when the order lines are created, the sequences are also created for delivery lines which are the part of Sequence 0.

For e.g.

If Order Position(pono) is 1 and it has quantity 50 , there might be chances that user split the line into 2 parts to create 2 sequences i.e.

Sequence 1 :- 25
Sequence 2 :- 25

Hence, sequence 0 will have total quantity i.e. 50 and other two sequences will have 25 respectively.

The problem might be that, you are selecting all the sequences including sequence 0 and hence its repeating.

So, try excluding sequence 0 by adding srnb >0 or srnb <> 0.
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #10  
Old 28th May 2013, 15:00
Juergen's Avatar
Juergen Juergen is offline
Guru
 
Join Date: Aug 2001
Location: Germany
Posts: 566
Juergen is on a distinguished road
Baan: IVc4, ERP LN - DB: Oracle - OS: Linux, Unix
Hi Bhushan,

that's not totally correct for BaanIV.

If you will coding tdsls045.srnb <> 0 than based on the attachment by chris nothing will be selected because both delivered and invoiced lines has tdsls045.srnb = 0. How the sequence number will be created depends how the user is handle the order, so it's possible to split-up one oder line into two partial shipments with sequence = 0 (first shipment) and sequence = 1 (second shipment).
Chris is also using tdsls045.invd > 0, so only the real delivered and invoiced lines will be selected.

Chris: What to you want to receive by the select statement, the totals per order or per line?

Regards,
Juergen
Reply With Quote
  #11  
Old 28th May 2013, 19:37
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
Juergen,

Yes. Thanks for correcting me. Again, as I said, in LN we have completely different structure and now I can assume we have a different business process too. So, Chris you can go with the suggestions given by 4c4 experts.
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #12  
Old 29th May 2013, 00:06
chris_kzn chris_kzn is offline
Member
 
Join Date: Aug 2005
Posts: 76
chris_kzn is on a distinguished road
Baan: IV - DB: SQL 2000 - OS: WINDOWS 2000
Hi Juergen,

I wanting to accomplish totals per order please.

Thanking you in advance.
Reply With Quote
  #13  
Old 29th May 2013, 10:10
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,639
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
tdsls045 index1 is orno,pono,srnb

Quote:
Originally Posted by chris_kzn View Post
Hi Juergen,

I wanting to accomplish totals per order please.

Thanking you in advance.
Hello,

table tdsls045 index 1 is orno,pono,srnb

So use something like :

Code:
invoice.amnt = 0
select tdsls045.*
from tdsls045
where tdsls045._index1 inrange {:tdsls040.orno, 0, 0}
and                        {:tdsls040.orno, 9999, 99}
and tdsls045.invd > 0
selectdo
     invoice.amnt = invoice.amnt + tdsls045.amnt 
endselect
Regards
__________________
//Bernd
Reply With Quote
  #14  
Old 29th May 2013, 14:28
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
Quote:
Originally Posted by bdittmar View Post
Code:
invoice.amnt = 0
select tdsls045.*
from tdsls045
where tdsls045._index1 inrange {:tdsls040.orno, 0, 0}
and                        {:tdsls040.orno, 9999, 99}
and tdsls045.invd > 0
selectdo
     invoice.amnt = invoice.amnt + tdsls045.amnt 
endselect
Regards
But Bernd that is exactly what was posted in the first thread.
Code:
invoice.amnt = 0
select tdsls045.*
from tdsls045
where tdsls045._index1 = {:tdsls040.orno}
and tdsls045.invd > 0
selectdo
invoice.amnt = invoice.amnt + tdsls045.amnt 
endselect
The only difference is you made it a range and opened the position and delivery(what it is called on our 4c4 system). The original poster says this was not giving him the right total. I do not know if this table is populated the same as the receipt table tdpur045, but we always use the srnb variable greater than 0. See below.

Code:
| This query gives all receipts for the selected order number
| and date range.
	select	tdpur045.orno, tdpur045.pono, tdpur045.srnb, tdpur045.quap,
		tdpur045.dqua, tdpur045.date, tccom020.nama, tdpur041.oqua
	from	tdpur045, tccom020, tdpur041
	where	tdpur045.orno inrange :orno.f and :orno.t
	and	tdpur045.date inrange :date.f and :date.t
	and	tdpur045.dqua > 0 and tdpur045.srnb > 0
	and	tdpur045.suno refers to tccom020
	and	tdpur041._index1 = {tdpur045.orno, tdpur045.pono}

I was wondering if the delivery (tdsls045.srnb) works the same way. The original poster should be able to look at the table in ttaad4500 and see if that is the case.
__________________
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
Sponsored Links
  #15  
Old 29th May 2013, 16:19
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,639
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
As set with ...

Hello Mark,

the poster say's:
------------------------------------------------------------------------------------
I have tried the the sqlcommand "as set with 1 rows" but this then causes the invoice amount to total the last order line and so resulting in an incorrect invoice amount.
------------------------------------------------------------------------------------
This could'nt give the right amount for the sales order, because only the first recordset is taken.

I think it's better to summarize in an if condition like:

Code:
select ...
from ....
where ...
selectdo
   if tdsls045.invd > 0 then
     invoice.amnt = invoice.amnt + tdsls045.amnt
   endif
Regards
__________________
//Bernd
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
Report Script Issue johnmark Tools Development 4 11th May 2013 06:55
Date pattern crystal report XI vs erpln Hannah T Third Party Products 4 19th September 2012 02:19
difference between session script and report script linyaohui Tools Development 2 18th October 2007 10:36
Use Report Input Field in Report Script peterfarouk Tools Development 13 30th June 2003 15:38


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


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