Report on Purchase order and approval details

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",
       max(pos.STATUSNAME) AS "PO Status",
       max(po.TOTALPRICE) AS "Total Price",
       max(resource.RESOURCENAME) AS "Asset Name",
       max(state.DISPLAYSTATE) AS "Asset State",
       max(rCategory.CATEGORY) AS "Asset Category",
       max(rtype.TYPE) AS "Asset Type",
       max(aaa.FIRST_NAME) "Approver",
       max(li.approvallevel) "Approver Level",
       max(pos1.STATUSNAME) "PO Approval Status",
       LONGTODATE(max(poa1.APPROVAL_DATE)) "Approval Date" 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 Purchaseorderitem pxt ON po.PURCHASEORDERID=pxt.PURCHASEORDERID
LEFT JOIN PurchaseLot plot ON pxt.PURCHASEORDERitemID=plot.PURCHASEORDERitemID
LEFT JOIN Resources RESOURCE ON plot.PURCHASELOTID=resource.PURCHASELOTID
LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN ResourceType rtype ON productType.RESOURCETYPEID=rtype.RESOURCETYPEID
LEFT JOIN ResourceCategory rCategory ON productType.RESOURCECATEGORYID=rCategory.RESOURCECATEGORYID
LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID
LEFT JOIN POLevelInfo pol ON po.PURCHASEORDERID=pol.PURCHASEORDERID
LEFT JOIN PurchaseApprovalDetails poa1 ON pol.LEVELINFOID=poa1.LEVELINFOID
LEFT JOIN AaaUser aaa ON poa1.APPROVERID=aaa.USER_ID
LEFT JOIN levelinfo li ON pol.LEVELINFOID=li.LEVELINFOID
LEFT JOIN POApprovalStatus pos1 ON li.LEVELSTATUSID=pos1.STATUSID
GROUP BY po.POCUSTOMID
ORDER BY 1

All Approver levels, Appprover names and status.

SELECT po.POCUSTOMID AS "PO Number", max(po.PONAME) AS "PO Name", ad.ORG_NAME AS "Account", longtodate(max(po.DATEORDERED)) AS "Created On", (li.approvallevel) "Approver Level", (aaa.FIRST_NAME) "Approver Name", (pos1.STATUSNAME) "Approval Status", max(pos.STATUSNAME) AS "PO Status",
poaddfield.UDF_CHAR1 AS "Legal Entity" 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 Purchaseorderitem pxt ON po.PURCHASEORDERID=pxt.PURCHASEORDERID LEFT JOIN PurchaseLot plot ON pxt.PURCHASEORDERitemID=plot.PURCHASEORDERitemID
LEFT JOIN Resources RESOURCE ON plot.PURCHASELOTID=resource.PURCHASELOTID LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID LEFT JOIN ResourceType rtype ON productType.RESOURCETYPEID=rtype.RESOURCETYPEID
LEFT JOIN ResourceCategory rCategory ON productType.RESOURCECATEGORYID=rCategory.RESOURCECATEGORYID LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID LEFT JOIN POLevelInfo pol ON po.PURCHASEORDERID=pol.PURCHASEORDERID
LEFT JOIN PurchaseApprovalDetails poa1 ON pol.LEVELINFOID=poa1.LEVELINFOID LEFT JOIN AaaUser aaa ON poa1.APPROVERID=aaa.USER_ID
LEFT JOIN levelinfo li ON pol.LEVELINFOID=li.LEVELINFOID LEFT JOIN POApprovalStatus pos1 ON li.LEVELSTATUSID=pos1.STATUSID
INNER JOIN POAccMapping poacc ON po.PURCHASEORDERID=poacc.PURCHASEORDERID  INNER JOIN AccountDefinition ad ON poacc.ACCOUNTID=ad.ORG_ID LEFT JOIN PurchaseOrder_Fields poaddfield ON po.PURCHASEORDERID=poaddfield.PURCHASEORDERID  WHERE ad.ORG_NAME=''Test Account' GROUP BY po.POCUSTOMID, ad.ORG_NAME, aaa.FIRST_NAME, li.approvallevel, pos1.STATUSNAME, poaddfield.UDF_CHAR1 ORDER BY 1

Account name is highlighted which can be modified as per need.



          • 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 ...
          • 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", ...
          • 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 ...
          • Query to list Service Request Approver and Purchase Request Approver

            Postgres & SQL Select aau.first_name "Name", po.approverstatus "Purchase Request Approver", sr.approverstatus "Service Request Approver", adef.org_name "Account" from aaauser aau left join POApproverDetails po on po.approver=aau.user_id left join ...