Query Reports on Purchase Order Items (MSSQL)

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", 
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




                New to ADManager Plus?

                  New to ADSelfService Plus?