Reports on Purchase Order Items

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 Date", 
"pos"."STATUSNAME" AS "PO Status",
 "pogl"."GLCODE" AS "GL Code", 
 "pocc"."CODE" AS "Cost Center Code",
  "pocc"."NAME" AS "Cost Center",
   "paao"."NAME" AS "Vendor Name"  ,
   "poi"."itemname" As "Item name",
   pc.CATEGORYNAME As "Items Type",
   poi.PRICE As "Price",
   poi.QUANTITYORDERED As "Quantity" 
     
   FROM "PurchaseOrder" "po" 
   LEFT JOIN "POStatus" "pos" ON "po"."STATUSID"="pos"."STATUSID"
LEFT JOIN "VendorDefinition" "pvd" ON "po"."VENDORID"="pvd"."VENDORID" 
LEFT JOIN "SDOrganization" "paao" ON "pvd"."VENDORID"="paao"."ORG_ID"
  LEFT JOIN "PurchaseGLCodes" "pogl" ON "po"."GLCODE"="pogl"."GLCODEID"
   LEFT JOIN "PurchaseCostCenter" "pocc" ON "po"."COSTCENTER"="pocc"."CCID"
   Left join "PurchaseOrderItem" "poi" ON po.PURCHASEORDERID=poi.PURCHASEORDERID
    left join PurchaseCategory "pc" on poi.CATEGORYID=pc.CATEGORYID

Other format

SELECT "po"."POCUSTOMID" AS "PO Number", LONGTODATE("po"."DATEORDERED") AS "Ordered Date", LONGTODATE("po"."DATERECEIVED") AS "Received Date", "cd"."componentname" AS "Items (Assets)", si.servicename "Items (Services)", "po"."TOTALPRICE" AS "Total Price", "paao"."NAME" AS "Vendor Name", "pogl"."GLCODE" AS "GL Code", "pocc"."NAME" AS "Cost Center"  FROM "PurchaseOrder" "po" 
   LEFT JOIN "VendorDefinition" "pvd" ON "po"."VENDORID"="pvd"."VENDORID" 
   LEFT JOIN "SDOrganization" "paao" ON "pvd"."VENDORID"="paao"."ORG_ID" 
   LEFT JOIN "PurchaseGLCodes" "pogl" ON "po"."GLCODE"="pogl"."GLCODEID" 
   LEFT JOIN "PurchaseCostCenter" "pocc" ON "po"."COSTCENTER"="pocc"."CCID"
   LEFT JOIN "purchaseorderitem" "poi" ON "po"."purchaseorderid"="poi"."purchaseorderid"
   LEFT JOIN "Componentdefinition" "cd" ON "poi"."componentid"="cd"."componentid"
   LEFT JOIN Serviceinfo si ON poi.serviceinfoid=si.serviceinfoid ORDER BY 1




          • Related Articles

          • 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 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 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 ...
          • 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 ...