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 ADSelfService Plus?
Related Articles
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) ...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 ...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", ...Query to list Service Request Approver and Purchase Request Approver (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 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 ...Task related reports (MSSQL)
Tested in MSSQL build (14306) Sample Output and headers present in this report Goto reports -> New query report and executes the following SELECT taskdet.TASKID AS "Task ID", taskdet.MODULE AS "Module", taskprior.PRIORITYNAME AS "Priority", ...