Query to show PO associated assets with Invoice details (MSSQL & PGSQL)

Query to show PO associated assets with Invoice details (MSSQL & PGSQL)

Tested in build PGSQL (14300) and MSSQL (14306)

SELECT po.Purchaseorderid "PO ID", MAX(po.poname) "PO Name", Max(ci.ciname) "CI Name", MAX(systeminfo.SERVICETAG) "Service Tag", MAX(systeminfo.MANUFACTURER) "Manufacturer", MAX(systeminfo.MODEL) "Model", max(paao.NAME) "Vendor Name", MAX(inde.invoiceid) "Invoice ID",  LONGTODATE(max(inde.DATERECEIVED)) "Invoice Date",  MAX(rtc.TOTALCOST) "Total Cost", longtodate(resource.WARRANTYEXPIRY) "Warranty Expiry Date", 

(cd.COMPONENTNAME) "Item", adef.org_name "Account"

 FROM PurchaseOrder po

LEFT JOIN POStatus pos ON po.STATUSID=pos.STATUSID

LEFT JOIN SDUser por ON po.REQUESTERID=por.USERID

LEFT JOIN AaaUser poa ON por.USERID=poa.USER_ID

LEFT JOIN VendorDefinition pvd ON po.VENDORID=pvd.VENDORID

LEFT JOIN SDOrganization paao ON pvd.VENDORID=paao.ORG_ID

LEFT JOIN Purchaseorderitem poi ON po.purchaseorderid=poi.purchaseorderid

LEFT JOIN Purchaselotext pxt ON poi.PURCHASEORDERITEMID=pxt.PURCHASEORDERITEMID

LEFT JOIN PurchaseLot plot ON pxt.PURCHASELOTID=plot.PURCHASELOTID

LEFT JOIN Resources RESOURCE ON plot.PURCHASELOTID=resource.PURCHASELOTID

LEFT JOIN ComponentDefinition cd ON poi.COMPONENTID=cd.COMPONENTID

LEFT JOIN InvoiceDetails inde ON po.PURCHASEORDERID=inde.PURCHASEORDERID

LEFT JOIN ComponentType productType ON cd.COMPONENTTYPEID=productType.COMPONENTTYPEID

LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID

LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID

LEFT JOIN DepartmentDefinition deptDef ON rOwner.DEPTID=deptDef.DEPTID

LEFT JOIN ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID

LEFT JOIN ResourceToCost rtc ON resource.RESOURCEID=rtc.RESOURCEID 

LEFT JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID

LEFT JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID

LEFT JOIN PaymentDetails pdet ON po.PURCHASEORDERID=pdet.PURCHASEORDERID

LEFT JOIN CI ci ON resource.ciid=ci.ciid

LEFT JOIN Systeminfo ON resource.resourceid=systeminfo.workstationid

LEFT JOIN POAccMapping pom ON po.purchaseorderid=pom.purchaseorderid

LEFT JOIN accountdefinition adef ON pom.accountid=adef.org_id 

WHERE PO.RECEIVEDDATE is NOT NULL

GROUP BY  po.Purchaseorderid, cd.COMPONENTNAME, resource.warrantyexpiry, adef.org_name ORDER BY 1