cancel
Showing results for 
Search instead for 
Did you mean: 

Open Purchase Order history report

MukeshSingh
Participant
0 Kudos

Query Expert,

We required Open Purchase Order History Report

Example

I make PO No.- 1 dtd. 15/03/2014 and made GRPO based on PO No.-1 but GRPO date 22/03/2014

When i generate Open PO Report as on 19/03/2014 PO No.-1 not showing in Open PO Report but as on 21/03/2014 PO No.-1 is still open due to based on PO No.-1, GRPO made on 22/03/2014

then why not showing in Open PO list.

Can any body facing this problem, please advise and send correct Open PO report query.

Thanks in advance

Mukesh Singh

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please post screen shot of above report.

Thanks & Regards,

Nagarajan

MukeshSingh
Participant
0 Kudos

Hi Nagarajan Sir,

Report forrmat is simple

PO No. | PO Date | CardName | ItemCode | Item Description | UoM | Order Qty   | Open Qty |

14101     12/03/14   ABC            P0124     TMT BAR 15 MM     MT     121              12

Against this PO no we made 3 GRPO

GRPO Date is different

1 GRPO Date is - 14/03/14   Qty is - 50

2 GRPO Date is - 18/03/14   Qty is - 30

3 GRPO Date is - 24/03/14   Qty is - 20

Suppose we generate report on 22/03/2014 but system date is 01/04/2014 then open Qty is showing 12 only.

As per PO history correct open Qty is - 41 because last GRPO made on 24/03/14.

Please guide me sir how to get correct open PO history

Regards

Mukesh Singh

kothandaraman_nagarajan
Active Contributor

Hi,

1. Open quantity is displayed in report regardless of posting date of GRPO. Because this field is updated by ordered quantity - Received quantity. ( POR1.quantity - PDN1.quantity)

2. There is no relation between system date and posting date. If system allows future posting date, we can modify posting date in GRPO

3. To get correct open quantity, we need to subtract ordered quantity-  sum(received quantity) for particular GRPO posting date.

Run below query to get open quantity:

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],   T1.[Quantity] - sum(t2.quantity) as 'Open quanity'  FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry left join  PDN1 T2 on  T2.[BaseEntry]  = t0.docentry and  T2.[BaseLine]  =  T1.[LineNum] INNER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry WHERE T3.[DocDate] between [%0] and [%1] GROUP BY T0.[DocNum],  T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity]

Thanks & Regards,

Nagarajan


MukeshSingh
Participant
0 Kudos

Hi Nagarajan Sir,

Your query is help full.

Thanks sir

Regards,

Mukesh Singh

Answers (1)

Answers (1)

MukeshSingh
Participant
0 Kudos

can any body facing this problem

Please suggest

Regards

Mukesh Singh