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.
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
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.
I have only tested this report on my own data so I am afraid there are no guarantees.