Purchase details

Purchase details




SELECT po.POCUSTOMID "PO Number",
       max(po.PONAME) "PO Name",
       max(po.TERMS) "PO Terms",
       LONGTODATE(max(po.DATEORDERED)) "Ordered Date",
       max(pos.STATUSNAME) "PO Status",
       max(poa.FIRST_NAME) "Purchase Requester",
       max(po.SHIPTO) "Ship To",
       max(paao.NAME) "Vendor Name",
       max(inde.INVOICEID) "Invoice Number",
       LONGTODATE(max(inde.DATERECEIVED)) "Invoice Date",
       LONGTODATE(max(pdet.PAYMENTDATE)) "PaymentDate",
       max(pdet.AMOUNTPAID) "Amount Paid",
       max(pxt.PRICE) "ItemPrice",
       max(po.TOTALPRICE) "Total Price",
       max(po.SALESTAX) "Sales Tax",
       max(po.SHIPPINGPRICE) "Shipping Price",
       max(pxt.QUANTITYORDERED) "Quantity ofItems",
       (cd.COMPONENTNAME) "Item ",
       max(cd.PARTNO) "Item PartNumber",
       max(po.remarks) "Remarks",
       max(productType.Componenttypename) "Product Type" 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 VendorDefinition pvd ON po.VENDORID=pvd.VENDORID
LEFT JOIN SDOrganization paao ON pvd.VENDORID=paao.ORG_ID
LEFT JOIN Purchaseorderext pxt ON po.PURCHASEORDERID=pxt.PURCHASEORDERID
LEFT JOIN PurchaseLot plot ON pxt.PURCHASEORDEREXTID=plot.PURCHASEORDEREXTID
LEFT JOIN Resources RESOURCE ON plot.PURCHASELOTID=resource.PURCHASELOTID
LEFT JOIN ComponentDefinition cd ON pxt.COMPONENTID=cd.COMPONENTID
LEFT JOIN InvoiceDetails inde ON po.PURCHASEORDERID=inde.PURCHASEORDERID
LEFT JOIN ComponentType productType ON cd.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID
LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID
LEFT JOIN DepartmentDefinition deptDef ON rOwner.DEPTID=deptDef.DEPTID
LEFT JOIN ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID
LEFT JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID
LEFT JOIN PaymentDetails pdet ON po.PURCHASEORDERID=pdet.PURCHASEORDERID
GROUP BY po.POCUSTOMID,
         cd.COMPONENTNAME
ORDER BY 1


                  New to ADSelfService Plus?

                    • Related Articles

                    • 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", ...
                    • Purchase Request details

                      Select pr.REQUESTID AS "Request Id", pr.SUBJECT AS "PR Subject", pr.DESCRIPTION AS "Description", LONGTODATE(pr.REQUESTEDDATE) AS "Requested Date", LONGTODATE(pr.DATEREQUIRED) AS "Date Required", LONGTODATE(pr.CREATEDDATE) AS "Created Date", ...
                    • 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 Request Approval

                      SELECT pr.Requestid "Requestid", pr.Subject "Subject", pr.Description "Description", longtodate(pr.Requesteddate) "Requesteddate", longtodate(pr.Daterequired) "Daterequired", longtodate(pr.createddate) "createddate", pr.Shipping_details ...
                    • Requester details

                      This report is used to find the Requester complete details.  SELECT AaaUser.USER_ID, AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "Email", DepartmentDefinition.DEPTNAME "Department", ...