Weekly Dynamics: Count Purchase Orders in Dynamics GP

Last week we looked at counting Payable Transactions in Dynamics GP by week. 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
--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