query for ORDR->OPOR->OPCH

[vasileiosfasolis]

hello to all!

i am interested in finding a query with which i will be able to fine the base document and the target document by inserting the docnum for the above tables

till now i have made the following code

select distinct  t1.cardcode 

,t1.cardname

,CASE when t4.Baseref is not NULL then t1.docnum Else NULL end as '# Εντολής Αγοράς'

,  t1.docdate as 'Ημ/νία Καταχώρησης Εντολής'

,case when t5.trgetentry is not null then t1.docnum Else NULL end as '# Παραγγελία' 

,t2.docdate as 'Ημ/νία Παραλαβής/Ήμ/νία Άφιξης'

from opor t1 

inner join por1 t5 on t5.docentry = t1.docentry 

inner join RDR1 t4 on t5.TrgetEntry =t4.docentry 

inner join ORDR t2 on t4.docentry = t2.docentry

where --t5.TargetType = '18' and

--t1.cardcode ='80000' AND

T1.DOCDATE='2012-05-07'

but it does not work properly. actually it brings me the ordr.docnum but the opor.docnum is wrong

do you have any idea?

thanks a lot for your time!

[former_member204969 · Accepted solution]

Assumming your purchase invoice is derived directly from the purchase order, you can start from this query:

Select so.docnum SalesOrd,So.CardName,po.docnum PurchOrd,Po.CardName,pl.ItemCode,pi.DocNum PurchInv

From ORDR so

  left join POR1 pl on pl.BaseType=17 and pl.BaseEntry=so.DocEntry

  left join OPOR po on pl.DocEntry=po.DocEntry

  left join PCH1 il on il.BaseType=22 and il.BaseEntry=po.DocEntry

  left join OPCH pi on pi.DocEntry=il.DocEntry

[jitin_chawla]

Hi,

Check for your CASE in the select. It bring T1.docnum in both the cases.

Also check if Left Join helps instead of Inner join.

Kind Regards,

Jitin

SAP Business One Forum Team