Building a Better Purchase Order Report

My Contract Managers really despised the basic "All Purchase Orders" report that automatically appears on the Purchase Order Page. It did not provide any of the birds-eye view data they review when looking at a PO.

So, with their input, I cloned the "All Purchase Order" report and began working away! I have placed my SQL below.

Please note that the following are parameters that I created or were built into the existing All Purchase Order report:

• Date range (DateFrom, DateTo)
• Supplier
• Requestor - custom field
• ItemName (Purchase Order)

One thing that you must be aware of is that I have created 3 custom fields on the Purchase Orders as well that are returned in this report. This was at the request of our Contract Management Team. They are as follows:

• PO Requestor, which is the person who is requesting the PO or receiving the items from the PO. This is a data class that I had to created and then associate.
• Lease/Purchase data class which I created the data class and then associated it to Purchase Order. It is a drop down box for the Contract manager to select whether this PO is for purchased items or for leased items.
• Description which allows the Contract Team to give a brief overview of the PO purpose. This is also a data class which I had to create and then associate to Purchase Order.

I have placed my custom pieces in BOLD so they are easily identifiable and easy for you to remove if not needed. If you would like more information about the data classes and how they were associated please message me or leave a comment. I can supply those if they are needed.

SELECT I.Guid AS _ResourceGuid,
IPOD.[Order Number],
dbo.fnLocalizeStringByGuid('item.name', raSup.ChildResourceGuid, '%_culture%') as [Supplier],
POD.[Description],
PL.[Lease/Purchase],
sum(isnull(ILIP.Quantity,0) * (isnull(ILIP.[Unit Cost],0) + isnull(ILIP.[Tax Amount],0)) ) as [Total Cost],
isnull(reqTab.Name,'') as Requestor,
I.CreatedDate   

FROM vResource v
INNER JOIN Item I ON v.Guid = I.Guid
JOIN ResourceTypeHierarchy rth 
on rth.ResourceTypeGuid = v.ResourceTypeGuid 
AND rth.BaseResourceTypeGuid = 'DF81E731-09AB-4391-B2D9-3B16E9C4AA86' --Purchase Order
left join Inv_Line_Items ILIP 
ON I.[Guid] = ILIP.[_ResourceGuid]
left outer join dbo.Inv_PO_Description POD
ON I.Guid = POD._ResourceGUID
LEFT OUTER JOIN dbo.Inv_Purchase_or_Lease PL  
ON I.GUID = PL._ResourceGUID
left JOIN Inv_Purchase_Order_Information IPOD ON IPOD._ResourceGuid = I.Guid
left join ResourceAssociation raCatForCatI on raCatForCatI.ParentResourceGuid = ILIP.[Catalog Item] and raCatForCatI.ResourceAssociationTypeGuid = '90879BB6-EF1C-4fd0-A8F7-4E68B92B9537' -- Catalog for Catalog Item
left join ResourceAssociation raSup on raSup.ParentResourceGuid = raCatForCatI.ChildResourceGuid and raSup.ResourceAssociationTypeGuid = '97d3522d-0d2e-4dce-9014-e8e4712d59de' -- Catalog To Supplier
left outer join 
(
select r._ResourceGuid, vi.Name as Name from fnRx_GetReceivingItemsHierarchy() r
join ResourceAssociation raReq on r.AncestorGuid = raReq.ParentResourceGuid 
and raReq.ResourceAssociationTypeGuid = 'fd0a2540-c373-45c9-95c8-4e8177bf295b' -- Requestor 
join vItem vi on vi.Guid = raReq.ChildResourceGuid
) reqTab on reqTab._ResourceGuid = I.Guid

where lower(I.Name) like lower('%ItemName%')
AND lower(isnull(reqTab.Name,'')) like lower('%Requestor%')
AND (I.CreatedDate >= %DateFrom% AND I.CreatedDate <= DateAdd(d,1,%DateTo%))
AND ('%Supplier%' = '00000000-0000-0000-0000-000000000000' 
OR '%Supplier%' = raSup.ChildResourceGuid)

group by I.Guid, 
IPOD.[Order Number], 
raSup.ChildResourceGuid,     
I.CreatedDate,       
I.ModifiedDate, 
reqTab.Name,      
PL.[Lease/Purchase],
POD.[Description]

ORDER BY IPOD.[Order Number] asc

Save this Report where you are comfortable finding it. I saved it under the same hierarchy as the "All Purchase Orders" and named it "__All Purchase Orders" (please not the double underscore for the name change).

Once that was completed I had the end users follow the instructions below.

1. Navigate to the Purchase Order Page.
2. Click on the tool icon in the upper right hand corner which opens the Item Selector pop up box.
3. Deselect the "All Purchase Orders" report and select your new report!
4. Click Apply and this will save for that user.

Happy Report Creating!

-d