Open GRPO run time correct report

[MukeshSingh]

Query expert,

We are using SAP B1 9.0

We need Open GRPO Report run time query

Example - We made GRPO on 01/09/14 with 5 line items after that we made A/P Invoice on 05/09/14 with 3 line items

If we generate open GRPO report as on 05/09/14 than GRPO showing Open for 2 line item but next date we made again rest 2 line item A/P Invoice

on 08/09/14

Finally after few date we want to generate how many open GRPO as on 05/09/14 than 2 line item data not showing in Open GRPO list but actual 2 line item closes on 08/09/14.

Please help how to made such run time query so that we get correct open GRPO report from SAP.

Thanks

Regards

Mukesh Singh

[kothandaraman_nagarajan]

Hi,

Please check this thread http://scn.sap.com/thread/3585023. Let me know if does not meet your purpose.

Thanks & Regards,

Nagarajan

[MukeshSingh]

Hi Sir,

This query not helpful.

Can you suggest any other way.

Regards

Mukesh Singh

[silambarasan_rajendran]

Hi,

Try this,

/*select from opch a */
Declare @tilldat Datetime
/* where */
set @tilldat=/* a.Docdate  */'[%0]'
SELECT A.DocNum,A.DocDate,B.ItemCode,B.Dscription,B.Quantity,
CASE WHEN B.Quantity - ISNULL(C.Qty,0)=0 THEN 'Closed' else 'Open' End LineStatus,c.Docdate 'InvoiceDat' 
FROM OPDN A INNER JOIN PDN1 B ON A.DocEntry=B.DocEntry
LEFT OUTER JOIN
(SELECT SUM(B.Quantity) Qty,B.BaseEntry,B.BaseLine,Max(A.DocDate) Docdate 
FROM OPCH A INNER JOIN PCH1 B ON A.DocEntry=B.DocEntry WHERE A.DocType='I' AND B.BaseType='20'
GROUP BY B.BaseEntry,B.BaseLine) C ON C.BaseEntry=B.DocEntry AND C.BaseLine=B.LineNum
WHERE A.DocType='I' AND ISNULL(C.Docdate,@tilldat)<=@tilldat

Thanks & Regards,

Silambu