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
New to ADSelfService Plus?
Related Articles
Query to show purchase order full details (PGSQL)
Tested in PGSQL build (14300) SELECT po.POCUSTOMID AS "Order No.",poi.serialno "S.No", poaddfield.UDF_CHAR1 AS "Legal Entity", po.PONAME AS "PO Name", pos.STATUSNAME AS "PO Status", cy.currencyname "Currency", paao.NAME AS "Vendor Name", po.SHIPTO AS ...Query Reports on Purchase Order Items (MSSQL)
Tested in build MSSQL (14306) MSSQL: 1. Login to SDP MSP as administrator 2. Execute this from SDP MSP application -> Reports -> New Query report Execute below query SELECT "po"."POCUSTOMID" AS "PO Number", "po"."PONAME" AS "PO Name", ...Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...Query to show Contract details with assets associated (PGSQL)
Tested in PGSQL build (14300) SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ...Purchase order approval details
SELECT po.POCUSTOMID AS "PO Number", max(po.PONAME) AS "PO Name", max(poa.FIRST_NAME) AS "Purchase Requester", longtodate(max(po.DATEORDERED)) AS "Ordered Date", longtodate(max(po.DATEREQUIRED)) AS "Required Date", max(pos.STATUSNAME) AS "PO Status", ...