Listing Purchase Orders based on items (Query)

[sherif_ahmed]

Hello there

I am using SAP Business one 8.8
I am trying to List all Purchase orders based single item (items and date period) using MSSQL query, similar to the inventory posting list.

would really appreciate some hints of how to achieve that.

Thank you

[Former Member · Accepted solution]

Hi,

Try:

SELECT T0.ITEMCODE, T1.DOCDATE, T1.DOCNUM, T0.WHSCODE,T1.CardCode, T1.CardName,T0.PRICE,T0.QUANTITY,

FROM POR1 T0 INNER JOIN OPOR T1  ON T0.DOCENTRY = T1.DOCENTRY

WHERE T0.ITEMCODE = '[%0]' AND T1.DocDate >= '[%1]' and T1.DocDate <='[%2]'

Thanks,

Gordon

[sherif_ahmed]

Really appreciate it, thank you.

[Former Member]

Please close your thread by marking helpful/correct answer.

[jitin_chawla]

Hi,

Please check this :

select t1.itemcode, t3.ItemName, t0.docdate,t1.WhsCode,t2.whsname, t0.docnum,

t1.price,t1.quantity

from opor t0 inner join por1 t1 on t0.docentry = t1.docentry

inner join OWHS t2 on t1.WhsCode = t2.WhsCode

inner join OITM t3 on t3.itemcode = t1.itemcode

where t1.ItemCode = '[%1]'

and t0.DocDate >= '[%2]' and t0.DocDate <='[%3]'

order by t0.docdate

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hi Sherif Ahmed,

Try following querry

select distinct t0.itemcode, t0.quantity, t0.price, t0.whscode, t1.docentry, t1.docnum from POR1 t0 inner join OPOR t1  on t0.docdate = t1.docdate order by t0.itemcode

Thanks

Prakash

[Former Member]

Hi

Try this:

select t1.itemcode,t0.docdate,t0.docnum,t1.price,t1.quantity from opor t0 inner join por1 t1 on t0.docentry = t1.docentry where t1.itemcode = [%0]

[former_member186095]

Hi,

Try this one :

select distinct t0.itemcode, t0.quantity, t0.price, t0.whscode, t1.docdate, t1.docnum from POR1 t0 inner join OPOR t1  on t0.docentry = t1.docentry where t0.itemcode = '[%0]'

JimM