Report a Purchase Order's Assets and Their Locations

Want a report of the assets on a purchase order along with the owner and location? Give this a try.

-- In owner's resource association, parentresourceguid is asset guid and childresourceguid is user guid   
		   
select distinct  
     j.name as 'PO Number',  
     k.name as 'Asset',  
     va.[Serial Number],  
     va.[Asset Type],  
     [Unit Cost] as 'Unit Cost',  
     [Quantity] as 'Quantity',  
     O.ParentResourceGuid as _ResourceGuid,  
     IAL.Name as 'Asset Location', 
     IU.Name as 'Owner', 
     IUL.Name as 'Owner''s Location', 
     va.Status, va.Manufacturer, va.Model, va.[System Number], va.Barcode, va.[Last Barcode Scan], va.[Asset Tag]	 
--     va.*  
from inv_parent_lines rec  
join item i   
 on rec.[_resourceguid] = i.[guid] --receiving slip  
join Inv_Line_Items li  
 on rec.[parent line item id]=li.[line item id] --PO  
join item j   
 on li.[_resourceguid]=j.[guid]   
join vReceivingslipassoresource rs  
 on i.guid=rs.parentresourceguid  
join item k  
 on k.guid=rs.childresourceguid --asset  
join vasset va  
 on rs.childresourceguid=va._Resourceguid  
join vItem vi  
 on vi.guid=va._Resourceguid  
join ResourceAssociation O   
 on k.guid=o.ParentResourceGuid  
--join on the resource type   
inner join vResourceHierarchy vrh on   
--filter the resourceassociation table on the Owner resourceassociation   
		(O.ResourceAssociationTypeGuid = 'ed35a8d1-bf60-4771-9dde-092c146c485a')	   
		and   
		(vrh.Guid = O.ParentResourceGuid)   
--join User location   
left join ResourceAssociation U on   
	(U.ResourceAssociationTypeGuid = '2030c6cd-c049-4c81-957d-34e4dfb23bcf')   
	and   
	(O.ChildResourceGuid = U.ParentResourceGuid)   
--join Asset location   
left join ResourceAssociation A on   
	(A.ResourceAssociationTypeGuid = '05de450f-39ea-4aae-8c5f-77817889c27c')   
	and   
	(O.ParentResourceGuid = A.ParentResourceGuid)   
join Item IA on O.ParentResourceGuid = IA.Guid   
left join Item IAL on A.ChildResourceGuid = IAL.Guid   
join Item IU on O.ChildResourceGuid = IU.Guid   
left join Item IUL on U.ChildResourceGuid = IUL.Guid   
where j.name = '%PONUM%'
order by j.name, k.name	

You need to create a parameter called PONUM and prompt the user at run time to enter a purchase order number. I made mine a Fixed List that was a query of PO's (basically using the first four tables used in the FROM...JOINs from the query above).

Click to view.

Works on my system with demo data but has a lot of tables that have to have data in them such as the receiving slip. Hope it helps you get what you need.