Single Report with PO, Purchase Request, Invoice and Receiving References

If you have ever wanted a single report with the Purchase Order number, the Purchase Request reference, Invoice Reference and Receiving Slip Reference all together, see if this will work for you.

1. Clone the report "All Purchase Orders" found in Asset & Inventory > Receiving > Purchase Orders
2. Edit the report and replace the SQL with the following
   

   SELECT I.Guid AS _ResourceGuid, 
   	IPOD.[Order Number], 
   	dbo.fnLocalizeStringByGuid('item.name', raSup.ChildResourceGuid, '%_culture%') as [Supplier], 
   	SUM(ILIP.Quantity) as [Number of Items], 
   	isnull(Received.[Count],0) as [Number of Received Items], 
   	sum(isnull(ILIP.Quantity,0) * (isnull(ILIP.[Unit Cost],0) + isnull(ILIP.[Tax Amount],0)) ) as [Total Cost], 
   	isnull(reqTab.Name,'') as Requestor, 
   	dbo.fnGetLinkedReceivingItemAsString(I.Guid,'06c97c09-f96d-4260-a119-061396a67eb9') as [PR Reference], 
   	dbo.fnGetLinkedReceivingItemAsString(I.Guid,'22538eed-17fe-42cd-889d-949a15e3cf03') as [Invoice Reference], 
   	dbo.fnGetLinkedReceivingItemAsString(I.Guid,'df2f98f8-b408-4776-88e0-5418d917e833') as [Receiving Slip Reference], 
   	I.CreatedBy, 
   	I.CreatedDate, 
   	I.ModifiedBy, 
   	I.ModifiedDate 
   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 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 
   left join  
   	(select AncestorGuid, sum(Quantity) as [Count] from  
   		(select distinct rih.AncestorGuid, rli.[Line Item ID], rli.Quantity from  
   		fnRx_GetReceivingItemsHierarchy() rih 
   		join vResource vr on vr.Guid=rih._ResourceGuid 
   		join vResourceHierarchy rh on rh.ResourceTypeGuid = vr.ResourceTypeGuid 
   		and rh.BaseResourceTypeGuid = 'df2f98f8-b408-4776-88e0-5418d917e833' -- RS 
   		join Inv_Received_Line_Items rli on rli._ResourceGuid = rih._ResourceGuid 
   		) inner_received 
   	group by AncestorGuid 
   	) Received on Received.AncestorGuid = 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.CreatedBy, I.CreatedDate, I.ModifiedBy, I.ModifiedDate, reqTab.Name, Received.[Count] 
   ORDER BY I.ModifiedDate desc, IPOD.[Order Number] desc
   
   

3. Save the report and run it.

Here is what the report looks like (split in half to fit in the screen shot). There are a couple of other fields on the end like created by and modified by that I didn't show here and could easily be dropped.

Click to view.

Click to view.

I have only tested this report on my own data so I am afraid there are no guarantees.