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


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