Query to show PO associated assets with Invoice details

Query to show PO associated assets with Invoice details

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

          • Related Articles

          • Query to show purchase order full details

            PGSQL: 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 "Ship To", po.BILLTO ...
          • Reports on Purchase Order Items

            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",  LONGTODATE("po"."DATEORDERED") AS "Ordered ...
          • Report on Purchase order and 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",       ...
          • Query to show Contract details with assets associated

            PGSQL: SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ad.ORG_NAME AS "Account",  ...
          • Query to show requesters who are service and PO approvers

            PGSQL & MSSQL: select ad.org_name "Account Name", sdo.name "Site Name", au.first_name "First Name", aci.emailid "Email ID",aci.landline "Telephone", aci.mobile "Mobile",po.approverstatus "Purchase Request Approver", sr.approverstatus "Service Request ...