Skip to Content
avatar image
Former Member

Pending Purchase Order Ageing Report

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Feb 11, 2017 at 11:57 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 21, 2017 at 12:04 PM

    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

    Add comment
    10|10000 characters needed 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])