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