# Weekly Dynamics: Count Purchase Orders in Dynamics GP

- URL: https://dynamicaccounting.net/weekly-dynamics-count-purchase-orders-in-dynamics-gp/
- Date: 2010-07-05
- Topics: Sales, Purchasing & Inventory, Tips & Hidden Features
- Dynamics GP versions mentioned: GP 2010
- Type: original article

Last week we looked at counting [Payable Transactions in Dynamics GP by week](http://msdynamicsgp.blogspot.com/2010/06/weekly-dynamic-counting-ap-transactions.html). This week, we are moving on to counting Purchase Orders. The idea behind this series is to give managers a sense of the transaction volume and timing to better manage this function. For AP, a transaction is pretty much a transaction. Not so for P.O.’s. A hundred line P.O. can takes much longer to create than a 10 one line P.O.’s so the measures of volume are number of P.O.’s, number of P.O. lines and number of invoices matched.   
There are 2 views in here, one for P.O.’s  and one for Invoice matching along with 3 queries, P.O’s, P.O. Lines and Invoice Matching. As before, all of these are by week with a week starting on Friday. Simply reorder the days in the view to changes the week starting date.

|  |
| --- |
|  |
| Buy the Microsof Dyamics GP 2010 Cookbook |

This is also available for download at [http://www.box.net/shared/39q0rmbz09](https://app.box.com/shared/39q0rmbz09 "http://www.box.net/shared/39q0rmbz09")  
—*View for Purchase Order Receipts  
–View is needed to combine work and history Receipts*   
*Create view Metrics\_PORec as  
select Case    
when datename(weekday,receiptdate) = ‘Friday’ then receiptdate  
when datename(weekday,receiptdate) = ‘Saturday’ then dateadd(Day,6,receiptdate)  
when datename(weekday,receiptdate) = ‘Sunday’ then dateadd(Day,5,receiptdate)  
when datename(weekday,receiptdate) = ‘Monday’ then dateadd(Day,4,receiptdate)  
when datename(weekday,receiptdate) = ‘Tuesday’ then dateadd(Day,3,receiptdate)  
when datename(weekday,receiptdate) = ‘Wednesday’ then dateadd(Day,2,receiptdate)  
when datename(weekday,receiptdate) = ‘Thursday’ then dateadd(Day,1,receiptdate)  
else ’01/01/1900′ end as WeekEndDate,poprctnm ,  
        receiptdate,   
        ‘work’ as status  
    from pop10300  
    where poptype=1  
union   
select  Case    
when datename(weekday,receiptdate) = ‘Friday’ then receiptdate  
when datename(weekday,receiptdate) = ‘Saturday’ then dateadd(Day,6,receiptdate)  
when datename(weekday,receiptdate) = ‘Sunday’ then dateadd(Day,5,receiptdate)  
when datename(weekday,receiptdate) = ‘Monday’ then dateadd(Day,4,receiptdate)  
when datename(weekday,receiptdate) = ‘Tuesday’ then dateadd(Day,3,receiptdate)  
when datename(weekday,receiptdate) = ‘Wednesday’ then dateadd(Day,2,receiptdate)  
when datename(weekday,receiptdate) = ‘Thursday’ then dateadd(Day,1,receiptdate)  
else ’01/01/1900′ end as WeekEndDate,poprctnm,  
        receiptdate,   
        ‘hist’ as status  
    from pop30300  
    Where Poptype=1  
——————————————————————————————–  
Go*   
*–Get Count of POs Received by Week End Date  
–Uses Metrics\_PORec view*   
*Select WeekEndDate, count(poprctnm) as PORecCount  
from Metrics\_PORec  
Group by WeekEndDate  
Order by WeekEndDate*   
*Go  
———————————————————————————————-  
———————————————————————————————–*   
*–Get Number of PO’s created per week  
–Use where clause on WeekEndDate to limit range  
–PO’s are best viewed in conjunction with PO Lines*   
*Select  
Case    
when datename(weekday,docdate) = ‘Friday’ then Docdate  
when datename(weekday,docdate) = ‘Saturday’ then dateadd(Day,6,docdate)  
when datename(weekday,docdate) = ‘Sunday’ then dateadd(Day,5,docdate)  
when datename(weekday,docdate) = ‘Monday’ then dateadd(Day,4,docdate)  
when datename(weekday,docdate) = ‘Tuesday’ then dateadd(Day,3,docdate)  
when datename(weekday,docdate) = ‘Wednesday’ then dateadd(Day,2,docdate)  
when datename(weekday,docdate) = ‘Thursday’ then dateadd(Day,1,docdate)  
else ’01/01/1900′ end as WeekEndDate, count(ponumber) as POCount  
            from pop10100   
group by  
Case    
when datename(weekday,docdate) = ‘Friday’ then Docdate  
when datename(weekday,docdate) = ‘Saturday’ then dateadd(Day,6,docdate)  
when datename(weekday,docdate) = ‘Sunday’ then dateadd(Day,5,docdate)  
when datename(weekday,docdate) = ‘Monday’ then dateadd(Day,4,docdate)  
when datename(weekday,docdate) = ‘Tuesday’ then dateadd(Day,3,docdate)  
when datename(weekday,docdate) = ‘Wednesday’ then dateadd(Day,2,docdate)  
when datename(weekday,docdate) = ‘Thursday’ then dateadd(Day,1,docdate)  
else ’01/01/1900′ end  
Order by WeekEndDate*   
*Go*   
*———————————————————————————————  
———————————————————————————————*   
*–PO Lines Per Week  
–Get the number of PO lines per week  
–PO Lines are an indicator of volume. In many cases you could have only a few PO’s with lots of line items  
–to enter*   
*SELECT   
Case    
when datename(weekday,docdate) = ‘Friday’ then Docdate  
when datename(weekday,docdate) = ‘Saturday’ then dateadd(Day,6,docdate)  
when datename(weekday,docdate) = ‘Sunday’ then dateadd(Day,5,docdate)  
when datename(weekday,docdate) = ‘Monday’ then dateadd(Day,4,docdate)  
when datename(weekday,docdate) = ‘Tuesday’ then dateadd(Day,3,docdate)  
when datename(weekday,docdate) = ‘Wednesday’ then dateadd(Day,2,docdate)  
when datename(weekday,docdate) = ‘Thursday’ then dateadd(Day,1,docdate)  
else ’01/01/1900′ end as WeekEndDate, Count(pop10110.POnumber) as POLineCount  
FROM         POP10110 INNER JOIN  
                      POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER  
Group By  
Case    
when datename(weekday,docdate) = ‘Friday’ then Docdate  
when datename(weekday,docdate) = ‘Saturday’ then dateadd(Day,6,docdate)  
when datename(weekday,docdate) = ‘Sunday’ then dateadd(Day,5,docdate)  
when datename(weekday,docdate) = ‘Monday’ then dateadd(Day,4,docdate)  
when datename(weekday,docdate) = ‘Tuesday’ then dateadd(Day,3,docdate)  
when datename(weekday,docdate) = ‘Wednesday’ then dateadd(Day,2,docdate)  
when datename(weekday,docdate) = ‘Thursday’ then dateadd(Day,1,docdate)  
else ’01/01/1900′ end   
Order by WeekEndDate*   
*Go  
———————————————————————————-  
———————————————————————————-  
–PO Invoice Match View  
–View needed to merge work and history transaction. This isn’t absolutely necessary.*   
*Create View POInvMatch as*   
*select Case    
when datename(weekday,receiptdate) = ‘Friday’ then receiptdate  
when datename(weekday,receiptdate) = ‘Saturday’ then dateadd(Day,6,receiptdate)  
when datename(weekday,receiptdate) = ‘Sunday’ then dateadd(Day,5,receiptdate)  
when datename(weekday,receiptdate) = ‘Monday’ then dateadd(Day,4,receiptdate)  
when datename(weekday,receiptdate) = ‘Tuesday’ then dateadd(Day,3,receiptdate)  
when datename(weekday,receiptdate) = ‘Wednesday’ then dateadd(Day,2,receiptdate)  
when datename(weekday,receiptdate) = ‘Thursday’ then dateadd(Day,1,receiptdate)  
else ’01/01/1900′ end as WeekEndDate, poprctnm,  
        ‘work’ as status  
    from pop10300  
    where poptype=2  
union   
select   
Case   
when datename(weekday,receiptdate) = ‘Friday’ then receiptdate  
when datename(weekday,receiptdate) = ‘Saturday’ then dateadd(Day,6,receiptdate)  
when datename(weekday,receiptdate) = ‘Sunday’ then dateadd(Day,5,receiptdate)  
when datename(weekday,receiptdate) = ‘Monday’ then dateadd(Day,4,receiptdate)  
when datename(weekday,receiptdate) = ‘Tuesday’ then dateadd(Day,3,receiptdate)  
when datename(weekday,receiptdate) = ‘Wednesday’ then dateadd(Day,2,receiptdate)  
when datename(weekday,receiptdate) = ‘Thursday’ then dateadd(Day,1,receiptdate)  
else ’01/01/1900′ end as WeekEndDate, poprctnm,   
        ‘hist’ as status  
    from pop30300  
    Where Poptype=2  
Go*   
*—————————————————-*   
*–PO Invoice Match Query  
Select WeekEndDate, count(poprctnm) as POInvMatchCount  
From POInvMatch  
Group by WeekEndDate  
Order by WeekEndDate*   
*Go*