Baanboard.com

Baanboard.com (http://www.baanboard.com/baanboard/index.php)
-   Distribution, Transportation & Warehousing (http://www.baanboard.com/baanboard/forumdisplay.php?f=6)
-   -   Query Purchase Order Line (http://www.baanboard.com/baanboard/showthread.php?t=69823)

DatinN 7th March 2017 03:50

Query Purchase Order Line
 
Hello ,

Hi All ..
I need your expert view .
I'm creating query to ease my user checking incomplete delivery .
This because user need to close Purchase Order once over validity period.
The issue now I'm facing, when user has Cancelled Back Order, this query still displaying the said order line.
I have set condition where quantity receipt and quantity delivered below than quantity ordered and not final receipt.
also condition not to print cancelled order line.
I need to rectify this query yet I 'm getting road block.
how to set this query not to print cancelled back order ?

thank you in advance .

The query text as below :


select
tdpur400.orno, | Purchase Order
tccom100.nama, | Name
tccom001.nama, | Name
tdpur401.odat, | Order Date
tdpur401.item, | Item
tdpur401.cwar, | Warehouse
tdpur401.ddta | Planned Receipt Date
from
tccom001, | Employees - General
tccom100, | Business Partners
tdpur400, | Purchase Orders
tdpur401 | Purchase Order Lines
where
tdpur401.odat >= tdpur401.odat.f and
tdpur401.odat <= tdpur401.odat.t and
tdpur401.otbp >= tdpur401.otbp.f and
tdpur401.otbp <= tdpur401.otbp.t and
tdpur400.ccon >= tdpur400.ccon.f and
tdpur400.ccon <= tdpur400.ccon.t and
tdpur400.ccon = tccom001.emno and
tdpur400.hdst <> tdpur.hdst.closed and
tdpur400.hdst <> tdpur.hdst.cancelled and
tdpur400.orno = tdpur401.orno and
tdpur401.clyn = tcyesno.no and
tdpur401.fire = tcyesno.no and
tdpur401.otbp = tccom100.bpid and
tdpur401.qoor > tdpur401.qidl and
tdpur401.qoor > tdpur401.qiap
order by
tdpur401.ddta

sasikanth 22nd March 2017 08:09

Try considering the tdpur401.oltp field in your query.

You should ideally ignore the Line Type - "Total" (tcgen.oltp.total)

Hope this should help you!


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


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