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 4th July 2017, 11:46
DatinN DatinN is offline
Member
 
Join Date: Aug 2014
Posts: 65
DatinN is on a distinguished road
Baan: FP7 - DB: SQL - OS: Windows
Query to extract Import PO
Baan: ERP LN 6.1 FP6
C/S: Both

Hi All,

I need your expertise to jive this query with me.
I need to produce a query in foreign currency .
at this moment, I only match between tdpur400 and tdpur401 because user want it to line items and request to have in home currency .
I use currency from tdpur400 and line items from tdpur401.
at moment, I didn't do anything with exchange rate.
however, I'm stuck because this query print hundred of lines despite 7 lines order. I have rectified but still not resolve.
Please help me at least I can display the order lines correct then we go to forex calculation .
below in text the query

--------------------------------------------------------------

select
tdpur401.orno, | Purchase Order
tdpur401.odat, | Order Date
tccom100.nama, | Name
tdpur401.item, | Item
tcibd001.dsca, | Description
tdpur401.qoor, | Ordered Quantity
tdpur401.cuqp, | Purchase Unit
tdpur401.pric, | Price
tdpur401.oamt, | Order Amount
tdpur400.ccur | Currency
from
tccom100, | Business Partners
tcibd001, | Items - General
tcmcs008, | Currency Rates
tdpur400, | Purchase Orders
tdpur401 | Purchase Order Lines
where
tdpur401.odat >= tdpur401.odat.f and
tdpur401.odat <= tdpur401.odat.t and
tdpur400.ccur = tcmcs008.ccur and
tdpur401.cvat = "NOIMP" and
tdpur401.item = tcibd001.item and
tdpur401.oltp <> tdgen.oltp.detail and
tdpur401.oltp <> tdgen.oltp.backorder and
tdpur401.orno = tdpur400.orno and
tdpur401.otbp = tccom100.bpid

----------------------------------------------------------------------------

Thank you
Reply With Quote
  #2  
Old 4th July 2017, 12:40
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,286
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
Use refers to clause

Use refers to clause when you fetch data from single table and need single record data from other tables.

Try this -

Code:
select
tdpur401.orno, | Purchase Order
tdpur401.odat, | Order Date
tccom100.nama, | Name
tdpur401.item, | Item
tcibd001.dsca, | Description
tdpur401.qoor, | Ordered Quantity
tdpur401.cuqp, | Purchase Unit
tdpur401.pric, | Price
tdpur401.oamt, | Order Amount
tdpur400.ccur | Currency
from
tccom100, | Business Partners
tcibd001, | Items - General
tcmcs008, | Currency Rates
tdpur400, | Purchase Orders
tdpur401 | Purchase Order Lines
where
tdpur401.odat >= tdpur401.odat.f and
tdpur401.odat <= tdpur401.odat.t and
tdpur400.ccur refers to tcmcs008 and
tdpur401.cvat = "NOIMP" and
tdpur401.item refers to tcibd001 and
tdpur401.oltp <> tdgen.oltp.detail and
tdpur401.oltp <> tdgen.oltp.backorder and
tdpur401.orno refers to tdpur400 and
tdpur401.otbp refers to tccom100
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
Sponsored Links
  #3  
Old 6th July 2017, 04:54
DatinN DatinN is offline
Member
 
Join Date: Aug 2014
Posts: 65
DatinN is on a distinguished road
Baan: FP7 - DB: SQL - OS: Windows
Dear Bhushan,

thank you for time and effort sharing knowledge .
your suggestion resolve my issue.

thank you
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
Easy SQL pdf Andrea Rossi Tools Development 2 24th March 2017 07:58
Desktop Query eshirley Tools Administration & Installation 0 10th September 2002 02:41


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


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