Query to show purchase order full details

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 AS "Bill To",ct.componenttypename "Product Type", cd.componentname "Product", poi.description "Additional information about the product", poi.price "Price", poi.quantityordered "Quantity", sum(poi.quantityordered*poi.Price) "Amount", sdu.firstname "Requested By", pogl.GLCODE AS "GL Code", LONGTODATE(po.DATEORDERED) AS "PO Created Date", LONGTODATE(po.DATEORDERED) AS "Ordered Date", ad.ORG_NAME AS "Account" 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 INNER JOIN POAccMapping poacc ON po.PURCHASEORDERID=poacc.PURCHASEORDERID INNER JOIN AccountDefinition ad ON poacc.ACCOUNTID=ad.ORG_ID LEFT JOIN PurchaseOrderItem poi ON po.purchaseorderid=poi.purchaseorderid LEFT JOIN SDUSER sdu on po.requesterid=sdu.userid LEFT JOIN ComponentDefinition cd ON poi.componentid=cd.componentid LEFT JOIN Componenttype ct ON cd.componenttypeid=ct.componenttypeid  LEFT JOIN PurchaseOrder_Fields poaddfield ON po.PURCHASEORDERID=poaddfield.PURCHASEORDERID LEFT JOIN Povendorcurrency poc ON po.purchaseorderid=poc.purchaseorderid LEFT JOIN Currency cy ON poc.currencyid=cy.currencyid GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,16,17,18,19,20 ORDER BY 1, 2
          • Related Articles

          • Report on Purchase order and approval details

            Kindly execute the query under Report -> New Query Report 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",       ...
          • 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 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 ...
          • Contract and Service Plans details - Query Report

            PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill Cycle",fixedmonthlycharges "Fixed Base Charge",sp.fixedmonthlyunits ...
          • Query to show technicians and associated groups_PGSQL

            SELECT AaaUser.FIRST_NAME "Technician Name", (sdo.NAME) "Associated Site", array_to_string(array_agg(distinct(qd.queuename)), ',') "Associated Group" FROM AaaUser  left JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID  inner JOIN HelpDeskCrew ON ...