Skip to Content
0

Query For Stock From One Warehouse

Oct 03, 2017 at 07:05 AM

23

avatar image
Former Member

Hi Experts,

I want to show 'On Hand' as stock from Warehouse 100 only - how would i achieve?

SELECT t3.slpname as "Sales Rep", t0.Cardcode as "Customer#", t0.Cardname as "Customer Name", t0.DocNum as "SO #", t1.ItemCode, t0.DocDate, t0.DocDueDate, Convert(numeric, GetDate())-convert(numeric, T0.DocDueDate ) "Days Overdue", t1.OpenQty, t1.OpenQty*T1.price AS 'Open Value', t2.onhand FROM dbo.ORDR t0 INNER JOIN dbo.RDR1 t1 on t1.docentry = t0.docentry INNER JOIN OITM t2 on t1.itemcode=t2.itemcode Inner join oslp t3 on t0.slpcode = t3.slpcode WHERE t1.LineStatus = 'O' AND t1.OpenQty > 0 AND t2.QryGroup20='Y' and t0.docduedate >= [%1] and t0.docduedate <= [%2]

i have tried what i think was correct, but could not get to show just one warehouse stock.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Nagarajan K Oct 03, 2017 at 01:56 PM
0

Hi,

Try this query,

SELECT t3.slpname as "Sales Rep", t0.Cardcode as "Customer#", t0.Cardname as "Customer Name", t0.DocNum as "SO #", t1.ItemCode, t0.DocDate, t0.DocDueDate, Convert(numeric, GetDate())-convert(numeric, T0.DocDueDate ) "Days Overdue", t1.OpenQty, t1.OpenQty*T1.price AS 'Open Value', t4.onhand FROM dbo.ORDR t0 INNER JOIN dbo.RDR1 t1 on t1.docentry = t0.docentry INNER JOIN OITM t2 on t1.itemcode=t2.itemcode Inner join oslp t3 on t0.slpcode = t3.slpcode inner join OITW T4 on T4.Itemcode = T1.Itemcode and T1.[WhsCode] = T4.[WhsCode] WHERE t1.LineStatus = 'O' AND t1.OpenQty > 0 AND t2.QryGroup20='Y' and t0.docduedate >= [%1] and t0.docduedate <= [%2]

Regards,

Nagarajan

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Many thanks - Works Perfectly!

0
Agustin Marcos Cividanes Oct 03, 2017 at 07:32 AM
0

Hi

you have to add the OITW table in a INNER JOIN with RDR1.itemcode and OITW.whscode = '100'.

Kind regards

Agustín Marcos Cividanes

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Dear Agustin,

Many thanks - could you advise how to do so? I am new to SAP B1 and trying to learn.

thanks,

0