Skip to Content
0

Pending Purchase Order Ageing Report

Feb 11, 2017 at 06:29 AM

72

avatar image

Dear Experts,

We require a Query Report for Pending Purchase Order Ageing Report with variant time period i.e. 0-30 days, 31-60 days, 61-90 day and 91+ dyas for SAP B1 Kindly help for the same.

Warm Regards,

Vijay N. Chavda

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

2 Answers

Nagarajan K Feb 11, 2017 at 11:57 AM
0

Hi,

Try this query,

SELECT T1.[ItemCode], T1.[Dscription], SUM(T1.[Quantity]) as TotalQty ,Case when datediff(d,T0.[DocDate],getdate()) <=30 then '1-30' when datediff(d,T0.[DocDate],getdate()) between 31 and 60 then '31-60' when datediff(d,T0.[DocDate],getdate()) between 61 and 90 then '61-90' when datediff(d,T0.[DocDate],getdate()) between 91 and 120 then '91-120' when datediff(d,T0.[DocDate],getdate()) >=121 then '121+' end days FROM OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] between [%0] and [%1] and T1.Linestatus = 'O' GROUP BY T1.[ItemCode], T0.[DocDate],T1.[Dscription] ORDER BY datepart(d,T0.[DocDate])

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Vijay Chavda Feb 21, 2017 at 12:04 PM
0

Dear Mr. Nagarjan,

Thanks for your response. But report only give data of Item Code, Name, Qty & Days range. Actually we required PO No., Date, Vendor Name, Item Code, Item Name, Qty. Unit Price, 0-30 Days, 31-60 Days, 61-90 Day & 91+ Days columns.

Herewith, take liberty to greet thank in advance for your generous support.

Regards,

Vijay N. Chavda

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

Please try this query,

SELECT T0.DocNum, T0.DocDate, T0.CardName,T1.[ItemCode], T1.[Dscription], SUM(T1.[Quantity]) as TotalQty ,Case when datediff(d,T0.[DocDate],getdate()) <=30 then '1-30' when datediff(d,T0.[DocDate],getdate()) between 31 and 60 then '31-60' when datediff(d,T0.[DocDate],getdate()) between 61 and 90 then '61-90' when datediff(d,T0.[DocDate],getdate()) between 91 and 120 then '91-120' when datediff(d,T0.[DocDate],getdate()) >=121 then '121+' end days FROM OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] between [%0] and [%1] and T1.Linestatus = 'O' GROUP BY T1.[ItemCode], T0.[DocDate],T1.[Dscription],T0.DocNum, T0.DocDate, T0.CardName ORDER BY datepart(d,T0.[DocDate])

0