Report History

Just recently a VAR that I have been working with began training one of his resellers’ salespeople on the the custom price multiplier I have been developing. Naturally, the salespeople have lots of wants and desires, and there were a few small features missing that had to be added.

This highly specific customer pricing required a “Price List” report generated from items chosen off the price multiplier screen. These reports would be emailed to the customer contact.

But what if the salesperson was out for vacation and the customer had lost the PDF? Its obvious that a medium-term cache of these price lists was needed.

The sales person generates the Price List as one would create a report normally, using the Reports drop down

When the Report is generated, a record in this table is created, and the file is attached using the PXNoteAttribute functionality so that it is tied to the document management system

Thanks to Sergey for providing extremely helpful explanation on how to do this

Get Report PDF file Programatically
How to pass a Report Parameters from a Screen

Acumatica Test SDK – File Load Exception Solution

I recently downloaded a copy of Acumatica’s Test SDK and began running through the pdf class. After writing the first classes test, I came across a maddening FileLoadException involving TestApiCore.dll

This is due to Windows determining the location of the .dll coming from a internet source, and attempting to sandbox it into running with a limited set of permissions. To temporarily fix it you can right click on the dll and select Unblock under the General tab, but this is inefficient because there are several files that are flagged in this way.

Instead, user the following Powershell command:
dir C:\*YourTestSDKDir*\TestProject\*.* | Unblock-File -confirm

Be sure to use the path pointing to the downloaded files, not to your projects \bin\ folder, because Visual Studio copies several .dlls from the TestProject folder of the TestSDK directory, and the files that overwrite s will continue to have this flag applied

Custom lightweight EDI integration

Product            : Sage MAS500
Description    : Custom EDI integration for orders, shipments and stock status reports
Project Type  : Custom integration
Customized   : None
New Screens : None
Versions           : 6.3 and later

The requirements for this customization was to enable the transmission of EDI documents in .csv format between an originating organization and a MAS500 user who was going to do shipping fulfillment on behalf of them. Documents were transmitted via FTP  between the two sites. On the MAS500 side the EDI documents were imported into MAS500 using a custom EDI connector written in VB.net which moved data from the FTP site .csv files into custom SQL staging tables in MAS500. The standard MAS APIs were then used to create sales orders, pick lists and then shipments. After the shipment were completed a shipment confirmation was then transmitted to the origination company.

 

 

Warehouse Shipment Report

Product : Sage MAS500
Description : Compare sales order shipments and warehouse shipments
Project Type : Custom project
Customized : None
New Screens : Shipment data compare. Excel import
Versions : 7.6 and later


The requirement for this customization was to be able to compare a list of shipments received from an external fulfillment warehouse to the corresponding shipments out of MAS500. Shipments are extracted based on a date range defined by the warehouse’s report date range.

The warehouse’s shipment report is transmitted in Excel format and then imported into MAS500 where it is compared to the shipments recorded for the same date range Variances are calculated as

    1. Differences between matching shipments’ quantities and values.
    2. Shipments recorded in the external warehouse and not in MAS500
    3. Shipments MAS500 and not in the external warehouse.

There is the ability to report all records or just records where there are mismatches. Both the quantity variance and also the dollar impact of the variance is displayed. This modifications typically compares up to 55,000 shipments from 12 warehouses in any month in a matter of minutes.

External Warehouse Stock Status Report

Product : Sage MAS500
Description : Compare stock status report and warehouse quantities
Project Type : Custom project
Customized Screens : None
New Screens : Data compare. Excel import
Versions : 7.6 and later

The requirement for this customization was to be able to compare a stock status report received from an external warehouse to the corresponding stock status report from MAS500.

The warehouse’s report is transmitted in Excel format and then imported into MAS500 where it is compared to the inventory valuation report up to an including the ending date of the warehouse export. Variances are calculated as

  1. Differences between matching items’ onhand quantities in the two warehouses
  2. Items in the external warehouse and not in MAS500
  3. Items in MAS500 and not in the external warehouse.

There is the ability to report all records or just records where there are mismatches. Both the quantity variance and also the dollar impact of the variance is displayed. The modification can import 12,000 items and compare them in less than a minute.

Customer Specific Iteration of ‘On the Fly Pricing’

A reseller came to us with a customer that had a desire for very fine tuned pricing by customer. It was very important for them that the custom system distill the Customer Discounts screen (AR209500) down into an even easier checkbox array to quickly apply the relevant base pricing, and from there use what ended up being called the “Price Multiplier” while the salesperson was on the phone negotiating pricing.

The workflow is as follows:

Modification of AR303000
Custom tab added on AR303000

After entering the customer information a user can navigate to the “Discount Levels Screen”. This customer wanted discount codes to match one for one with their Item Price Classes, with Sequences A, B, C, D and E. A percentage shows the discount percent for that sequence. Currently we have not added any information about quantity breaks but we may add that functionality in at a later date.

The same customer now seen in AR209500 applied to the correct discount

One can then fine tune this discount even further by hitting the ‘Open Price Multiplier’ button on AR303000, and the following custom screen appears. In the screenshot the system automatically has populated the customer and the user has selected a Price Class ID to filter by, and the respective items have been populated in the grid.

The Price Multiplier

Only the last three fields are editable (Margin, Multiplier, Multiplied Price). If a user wants to set a specific margin, the remaining fields are calculated in kind. If the user has a specific factor (they want the price 1.2x more expensive) they use the ‘multiplier column’, and if they simply want to choose any price they can using the final column.

Additionally, ‘Current Price Source’ and ‘Current Price Code’ tell the user where the current pricing has come from, whether it is the list price, list minus a discount code, or from a previous multiplier sheet. A release button makes the open Price Multiplier sheet active, and obsoletes any previous released Price Multiplier sheet with an identical Customer and Price Class ID.

Sales order with a multiplied Item
Creating a sales order

As you can see above, the price of the item for the customer we have been working with throughout all the screenshots shown (ABCVENTURE) reflects the price set in a released price multiplier sheet (it was intentionally set to zero dollars).

While this specific modification is very specific to a single customers needs, I hope it gives you inspiration on how you may apply something similar to another process that is a sticking point for a company you may be working with.

Here’s to a smooth implementation
-Kyle

REST API Web Endpoint Quick Guide

Version 0.4
Written By: Kyle Vanderstoep

This document was intended for quick reference with regards to the most common fields you want to access from the web endpoint. For more in-depth help, take a look at the section titled “Working with the Contract-Based REST API” located here: API HELP

Authentication

Login

Type: POST
Format: JSON
URL: <URL>/entity/auth/login
BODY:

    {
        "name" : "admin",
        "password" : "123",
        "company" :  ""
    }

Logout

Type: POST
Format: JSON
URL: <URL>/entity/auth/logout
BODY: None

Inventory Items

All Stock Items

Type: GET
Format: JSON
URL: <URL>/entity/Default/17.200.001/StockItem

Check an item’s Onhand Quantity

Type: PUT
Format: JSON
URL: <URL>/entity/17.200.001/InventoryAllocationInquiry
BODY(replace “ELEHDD2” with the InventoryID(s) you want to Query):

{
     "InventoryID": {"value": "ELEHDD2"}
}

Add a $select query parameter to the request to select any specific fields from the report you want to narrow down

Customers

Add a Customer

Type: PUT
Format: JSON
URL: <URL>/entity/Default/17.200.001/Customer
Body:
{
  "CustomerID" : {value : "JOHNGOOD" } ,
  "CustomerName" : {value : "John Good" },
  "MainContact" :
   {
      "Email" : {value : "demo@gmail.com" },
      "Address" :
        {
          "AddressLine1" : {value : "4030 Lake Washington Blvd NE" },
          "AddressLine2" : {value : "Suite 100" },
          "City" : {value : "Kirkland" },
          "State" : {value : "WA" },
          "PostalCode" : {value : "98033" }
        }     
    } 
}

Additionally, fields ‘ShippingContact’ and ‘BillingContact’ can be specified separately from ‘MainContact’ shown above. If these are not specified then a new PurchaseOrder will use the MainContact as both.

Retrieving Customer data

Type: GET
Format: JSON
URL: <URL>/entity/Default/17.200.001/Customer

Use query parameter $filter: To specify filtering conditions
Use query parameter $expand: To expand specific detail entities
Otherwise all current customer data will be sent

Update Existing Customer

Type: PUT
Format: JSON
URL: <URL>/entity/Default/17.200.001/Customer

Use parameter $filter: To specify filtering conditions on key fields that identify the record to be updated
Key Field: CustomerID (string(10))
Example:
URL: <URL>/entity/Default/17.200.001/Customer?$filter=CustomerID eq ‘ABARTENDE’
Body:
{
      "CustomerName": {"value": "Stuffffff"}
}

This will update customer ABARTENDE’s name to ‘Stuffff’

Sales Orders

Add a Sales Order

Type: PUT
Format: JSON
URL: <URL>/entity/Default/17.200.001/SalesOrder
Body:
{
  "CustomerID" : {value : "JOHNGOOD"}
}

This will create a new sales order for the CustomerID chosen, and shipping information of their Primary ‘Location’

{
  "CustomerID" : {value :
"ABARTENDE"},
  "LocationID" : {value :
"VEGAS"}
}

This will create a new sales order for the CustomerID, at an alternate Location ID (applies to ‘Ship To Address’, BillToAddress will always default to the default ‘BillToAddress’ specified in the Customer Record (see this document’s relevant section under Customers):

This will create a new sales order for the CustomerID, using a ship to address that is not currently saved as a location under the CustomerID record (the same can be done with ‘BillToAddress’):

{
  "CustomerID" : {value : "ABARTENDE"},
  "ShipToAddressOverride": {"value": true},
  "ShipToAddress" : {
        "AddressLine1" : {value : "TEST 123"},
        "AddressLine2" : {value : "POBOX 123"},
        "City" : {value : "Seattle"},
        "Country" : {value : "US"},
        "PostalCode" : {value : "95073"},
        "State" : {value : "WA"},
  }

Update a Sales Order

Type: PUT
Format: JSON
URL: <URL>/entity/Default/17.200.001/SalesOrder

Use the same format as “Add a Sales Order”, however you must
specify existing records using query parameter $filter, or else a new record
will be added

Example:
PUT to <URL>/entity/Default/17.200.001/SalesOrder?$filter=OrderNbr eq ‘SO004573’

Body:
{
    "CustomerID" : {value : "ABCVENTURE"}
}

This sets Sales order # ‘SO004573’ value of CustomerID to ABCVENTUREKey Field: OrderNbr (string(10)

Status of a Sales Order

Date received, date entered into the system and current status
Open, closed, backordered, shipped
If shipped – shipping information

Cancel a Sales Order

Type: PUT
Format: JSON
URL: <URL>/entity/Default/17.200.001/SalesOrder/CancelSalesOrder

Body:
{
     "entity":
     {
      "OrderNbr": {"value": "SO004543"}
     }
}

You must create a new request in this format for every
SalesOrder you want to cancel

Further Documentation

Download the Acumatica provided swagger.json file for a full openAPI 2.0 documentation of the existing web endpoint here:

You can then use an Open Api GUI of choice to navigate it

Send a GET Request to any Endpoint with the addition of $adHocSchema to get a list of fields associated with it
Example:<URL>/entity/Default/17.200.001/SalesOrder/$adHocSchema