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?

        Resources

            • 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 "Requestid", pr.Subject "Subject", pr.Description "Description", longtodate(pr.Requesteddate) "Requesteddate", longtodate(pr.Daterequired) "Daterequired", longtodate(pr.createddate) "createddate", pr.Shipping_details ...
            • 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", ...
            • Contract Details

              SELECT mcdt.Contractid "Contract ID", mcdt.CONTRACTNAME "Contract Name", mcdt.comments "Description", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) "Created Time", LONGTODATE(mcdt.FROMDATE) "From Date", ...