GRPO Report based upon item

[Former Member]

Hello Experts,

I want a query, in which if i select any item and FromDate and ToDate..

For Ex:-

Item:- Item A

FromDate:- 04/01/2009

ToDate:- 12/31/2009

Then its should display all the GRPO No,GRPO Rate of that Item A between that FromDate and ToDate.

Regards,

AKM

[Former Member]

Thanks

[Former Member]

Are those queries answered your question? Please indicate instead of a thanks for nowhere.

[former_member325312]

Hi

here is ur query i think this will solve ur prob if so pls do close the thread

SELECT T0.[DocNum][PO NO], T2.[DocNum][GRPO NO], T2.[DocDate], T2.[CardCode], T2.[CardName], T3.[ItemCode], T3.[Dscription], T3.[Quantity], T3.[Price], T3.[LineTotal], T2.[DocTotal] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry, OPDN T2 INNER JOIN PDN1 T3 ON T2.DocEntry = T3.DocEntry WHERE T3.[BaseRef] = T0.[DocNum] and T3.[ItemCode] = T1.[ItemCode] and T2.[DocDate] > = [%0] AND T2.[DocDate] < = [%1] and T2.[Series] =[%2] GROUP BY T0.[DocNum], T2.[DocNum], T2.[DocDate], T2.[CardCode], T2.[CardName], T3.[ItemCode], T3.[Dscription], T3.[Quantity], T3.[Price], T3.[LineTotal], T2.[DocTotal]

Regards

Jenny

[Former Member]

Try this one:

SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price

FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocDate Between [%0\] and [%1\] AND T1.ItemCode = '[%2\]'

Thanks,

Gordon

[Former Member]

Thanks both of you.,but i want with PO No also for that correspoing GRPO No.

[Former Member]

You can include T1.[BaseRef] as 'PO Number' in the query.

SELECT T0.DocNum, T1.[BaseRef] as 'PO Number', T0.DocDate, T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocDate >=%0 and T0.DocDate <= %1 and T1.ItemCode = %2

Thanks,

Joseph

Edited by: Joseph Antony on Jun 25, 2010 12:04 PM

[Former Member]

Updated:

SELECT T0.DocNum u2018GRPO#', T0.DocDate, T0.CardCode, T0.CardName, T1.BaseRef 'PO#'', T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price

FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocDate Between [%0\] and [%1\] AND T1.ItemCode = '[%2\]'

[Former Member]

Hi Chintesh,

you may try this:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price] FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <= [%1] and T1.[ItemCode] = [%2]

However, why dfon't you use the purchase analysis report?

Thaks,

Joseph

Edited by: Joseph Antony on Jun 25, 2010 10:51 AM