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