Sales Order import utility to facilitate 3rd party billing system and inject those imports into e-automate as sales orders.
Jump to: Overview | Samples | Variables | Alert Functionality | Best Practices & Tips | Related Alerts | Webinar
Overview
Overview
This alert was built to use a custom CEOJuice table that would be populated via import or direct SQL feed from 3rd party billing system and inject those imports into e-automate as sales orders. As this will require SQL expertise from your internal IT and/or 3rd party provider to write the required data into our custom table, you will need to coordinate with our team to make sure that table is populated accordingly.
The records imported are the detail line items for the order(s) to be created, and our process will calculate the order total / tax / freight accordingly using the matching e-automate Item you created with the imported 'item'. That is a requirement, that the imported 'item' must match to an existing e-automate item (whether that be an expense code item or an actual inventory item). This match is required so that the tax is calculated accurately according the the taxcode flags for the customers tax authority.
Run Schedule: Every 15 minutes
Type of Output: Email
* * *
Sample
Sample
* * *
Variables
Variables
Variable 12: Group Renumber - Set the variable to yes if you are importing multiple orders that are related (for example, one order per host equipment for a multi-equipment order) and wish to renumber all the sales orders to be sequential with the same main sales order number.
Variable W: Sales Messages - If you would like to have the process update the sales order pop-up messages, indicate here the concatenation of the possible combinations to incorporate into the message:
SourceID - ID# from the host system
PriceBookName - if your integration has special pricing options enabled on quotes and they are populating our column PriceBookName.
Imported SONumber from CRM
Imported Description
Imported Quote Number
Variable X: One order per - Option to have one sales order per ship to location or one sales order per 'Host' configuration. **This is a required field for the integration to work.
Variable Y: 3rd party integrations you will use - You must specify at least one integration from the list for the task to process.
Variable Z: Branch Source - Indicate whether the branch on created orders should pull from the customer record or the sales rep record in e-automate.
Variable 1: Order Type Default - Indicate the default order type to be used when sales orders are created. **This is a required field for the integration to work.
Variable 2: Department - Enter the department code to use on orders created by the sync.
Variable 3: On Hold Code - Optional to specify an on-hold code to be usedfor all orders created with no errors. If left blank, orders with no errors will not be placed on hold.
Variable 4: Default Order Status - Enter the default status to be used on created orders. **This is a required field for the integration to work.
Variable 5: Renumber Invoices -Enter yes to renumber the e-automate sales invoice to match the invoice number from the external system.
Variable 6: Default Ship Method - Enter the ship method code you wish to you on non-drop ship orders if the default ship method on the customer is not set. **This is a required field for the integration to work.
Variable 7: Default Drop Ship Ship Method - Enter the default drop-ship shipping method code to use if the imported ship method code = "Drop". **This is a required field for the integration to work.
Variable 8: On Hold Code for Errors - Enter here the specific On Hold code to be used when there are errors on the order. **This is a required field for the integration to work.
Variable 9: Default Customer - By default, the process will use the customer number supplied on the import table. But if the customer number imported is invalid, then we need to have a default customer to use for the sales order we create. **Only available if you are running version 20.1 or higher of e-automate. **This is a required field for the integration to work.
Variable 10: Error Item Number - Enter an item number to be used on the sales order when the sync can't find a matching item in e-automate to use. Example Item#: **ItemError. **This is a required field for the integration to work.
Variable 11: In process On Hold Code - Specify an on hold code to be used while the order is being created by the sync. **This is a required field for the integration to work.
* * *
Alert Functionality
Alert Functionality
Data from third-party integrations is pushed to the CEOJuice background table and used to create sales orders in e-automate.
Jump to: Import Table | Customers | Order Number | Order Type | Sales Reps | Branch | Warehouse | Order Status | Error Item | Ship Methods | On Hold Codes | Parent/Child Line Items
Import Table
Import Table
Clients can use an ETL utility called SKYVVA for integration from SalesForce. For other CRM's, the CRM already has access to their clients' e-automate database and they are writing into our ID634 table directly.
Below is a list of the column names in the import table that are either required to be populated or are optional to be populated in the table. This information is also included in a spreadsheet you can find at the bottom of this post.
[ImpSONumber] - Imported Sales Order Number (if you want that used in e-automate) -OPTIONAL
[ImpInvNumber] - Imported Sales Invoice Number (if you want that used in e-automate) -OPTIONAL
[Description] - Sales Order Description -OPTIONAL
[ImpCustomerNumber] - Must correspond to your e-automate Customer Number - REQUIRED
[ImpShipToCustomerNumber] - If none is provided, then we will use ImpCustomerNumber
[SODate] - Will use today/date of import unless you tell us otherwise - OPTIONAL
[SalesRepNumber] - Push the AgentNumber you want used as the sales rep on the order, else it defaults to the rep assigned to the customer.
[BranchNumber] - Not used, we need to apply the Customer BranchID from e-automate.
[PONumber] - If provided, will be entered.
[Item] - Must correspond to an item number in e-automate, or we will create based on templates you use.
[DetailDesc] - Line Item Description (not Item Description) - OPTIONAL
[Qty] - Item quantity. REQUIRED
[UnitPrice] - Specify or we will default to price on Item
[LineExtTotal] - -OPTIONAL
[GLAcctNumber] - let us know if we need to use something different than default from Item - OPTIONAL
[GLDeptNumber] - REQUIRED will default to Variable2
[TaxFlag] - let us know if we need to use something different than the default - OPTIONAL
[EquipmentNumber] - let us know if we need to assign - OPTIONAL
[ContractNumber] - let us know if we need to assign - OPTIONAL
[Remarks] - end up on the SO header in that field.
[Notes] - end up on the SO header in that field.
[WarehouseNumber] - let us know if we should use something other than the default - OPTIONAL
[BinNumber] - let us know if we should use something other than the default - OPTIONAL
[OutCost] - let us know if we should use something other than the default - OPTIONAL
[ParentID] - only required for accessories on equipment
[ImpBillToCustomerNumber] - Must correspond to your eAuto Bill To Customer Number - OPTIONAL
[QuoteNumber] - REQUIRED if we need to create a quote in e-automate for you
[QuoteDetailID] - REQUIRED if we need to create a quote in e-automate for you
[MailtoAttn] - let us know if we should use something other than the default - OPTIONAL
[MailToName] - let us know if we should use something other than the default - OPTIONAL
[MailToAddress] - let us know if we should use something other than the default - OPTIONAL
[MailToCity] - let us know if we should use something other than the default - OPTIONAL
[MailToState] - let us know if we should use something other than the default - OPTIONAL
[MailToZip] - let us know if we should use something other than the default - OPTIONAL
[MailToCountry] - let us know if we should use something other than the default - OPTIONAL
[ShipToAttn] - let us know if we should use something other than the default - OPTIONAL
[ShipToName] - let us know if we should use something other than the default - OPTIONAL
[ShipToAddress] - let us know if we should use something other than the default - OPTIONAL
[ShipToCity] - let us know if we should use something other than the default - OPTIONAL
[ShipToState] - let us know if we should use something other than the default - OPTIONAL
[ShipToZip] - let us know if we should use something other than the default - OPTIONAL
[ShipToCountry] - let us know if we should use something other than the default - OPTIONAL
[OnHoldCode] - REQUIRED if we need to put On Hold, otherwise will default to Variable3
[OrderType] - REQUIRED if we need to put On Hold, otherwise will default to Variable1
[ShipToTypeID] - Not used we default to location info per the customer
[ContactNumber] - The e-automate Contact Number, if available - OPTIONAL
[ReqDate] - let us know if we should use something other than the default - OPTIONAL
[ShipMethod] - let us know if we should use something other than the default - OPTIONAL
[DetailParentID] - allows the order of Sales Order Line Items (i.e. 1.0, 1.2. 1.3, 2.0, 2.1, etc.) - OPTIONAL
[VendorItem] - if wanting to link to PO, then REQUIRED
[VendorCost] - if wanting to link to PO, then REQUIRED
[VendorExternalLinkedPO] - if wanting to link to PO, then REQUIRED
[UOM] Unit of Measurement - if wanting to link to PO, then REQUIRED
[DefaultPrice] - if wanting to link to PO, then REQUIRED
[VendorNumber] - if wanting to link to PO, then REQUIRED
Customers
Customers
The customer number imported into the ImpCustomerNumber column of the import table must match the customer number in e-automate. If you are on a version of e-automate higher than 20.1, you can choose to use a default customer to use on orders where there is no match to an e-automate customer number.
Order Number
Order Number
If an order number is specified in the ImpSONumber column of the table, that number will be used as the sales order number in e-automate. If no order number is specified, the next sales order number from e-automate will be used.
Additionally, you have the option to group renumber the orders in e-automate when variable 212 is set to yes. This is used when you have one order from the CRM that is split by ship to location or host, the group renumber will renumber the created sales orders with a -1, -2, etc, so that all orders are linked in e-automate.
Order Type
Order Type
Order Types can be found in e-automate in Tools - Lists and Codes - Sales order types:
Sales Reps
Sales Reps
To link the sales rep from the CRM order to the created sales order in e-automate, you'll need to populate the SalesRepNumber column with the employee number from e-automate:
Branch
Branch
In variable Z, you can specify if the branch on the sales order is pulled from the customer record or the sales rep record.
Customer Branch:
Sales Rep Branch:
Warehouse
Warehouse
The logic follows this sequence to find the warehouse to use on the order if nothing is specified in the import table:
1. The default warehouse for the branch used on the order.
2. If there is no value for the warehouse at this branch, the logic moves to the default warehouse for the customer's branch.
3. If there is no value for the warehouse for the customer's branch, the logic moves to the warehouse set for the main branch.
Order Status
Order Status
The order status specified in variable four will be used on all orders created by ID634:
Error Item
Error Item
An error item is required for the sync to work, it's used whenever the sync is unable to either create a new item, or find a matching item in e-automate. The error item needs to have either an inventory or expense code and a sales code:
The description, quantity, and pricing on the order will match the imported details:
You'll need to cancel the error item from the order and add the proper item once it has been created.
Ship Methods
Ship Methods
The default ship method that will be used on the sales orders will be the default setting on the customer:
If there is no ship method set on the customer record, the default standard shipping method set in variable 6 will be used. If the order is set as a drop ship order, the sync will use the default drop-ship method.
Shipping methods are found in e-automate in Lists and Codes - Shipping Methods:
On Hold Codes
On Hold Codes
There are two required on hold codes for the order import and one optional on hold code:
In progress on hold code: While the import is creating the order, this hold code will be applied to the order. This indicates that the order should not be edited as it's still in the process of being created. This is specified in variable 11.
Error on hold code: This hold code indicates that there was an error on the order that needs to be resolved. This hold code is set in variable 8.
On hold code: There is an option to place all orders that do not have any errors on hold. This is done by specifying a hold code in variable 3.
In e-automate, on hold codes are found in Lists and Codes - On Hold Codes
Parent Child Assoc
Parent / Child Line Item Association
The order import can assign parent/child relationships on the order in e-automate based on the sequence of the items in the import table.
* * *
Best Practices & Tips
Best Practices & Tips
Custom eView
As part of the installation of the sales order import, we will install a custom eView showing the details of the imported orders. You'll find this eView in the System section of eViews. The included columns are:
[SourceID] - This is the value from the 3rd part importing, typically this is the overall "DEAL" identifier.
[ImportedNumber] - If the 3rd party is pushing an order# to use this is the value they pushed. The process will use this as the EA Order# (if the imported number doesn't already exist)
[OrderCustomer] - This is the EA Customer name matched to the imported value in the ImpCustomerNumber column.
[OrderBillTo] - This is the EA Customer name matched to the imported value in the ImpBillToCustomerNumber column.
[OrderShipTo] - This is the EA Customer name matched to the imported value in the ImpShipToCustomerNumber column.
SO Number - If the order was successfully created in EA , this will be the SONumber from that order. A blank value indicates either there was an error preventing the order from being created, or the process hasn't triggered to process this order (runs every 15 min).
Order status - The current order status of the EA sales order.
Item - This is the EA Item# matched to the ImpItem value. Blank values indicate the imported item# cannot be found in EA
[IimpItem] - The Item# imported from the 3rd party source
Item description - The EA item description (if ImpItem matched to an EA Item#)
[ImpItemDescription] - The line item description imported from the 3rd party source.
[ImpQty] - The Qty imported to use for the order line item
[ImpUnitPrice] - Unit price imported to use as the unit price on the sales order line item
[ImpOutCost] - OutCost value from 3rd party to use on the imported line item details (will override the Eautomate default outcost value on the item)
[ImpOrderDescription] - Imported value to use as the sales order description (the process will also pre-pend the imported description with "SourceID:" and the value from the SourceID column if it's provided. This is to help users visually match & sort orders by the visible DealID value imported.
[ImpOrderRemarks] - Imported values to insert into the sales order remarks.
[ImpSourrce] - Indicates the "source" of the data for this row. (SFAD for AgentDealer, SalesChain for SalesChain, EAQuote for eautomate quote conversions, etc)
[ImpIsDevice] - This column was inserted to help identify 'equipment/accy' items from misc non-accy items. 1 means the item on this row is a machine/device, 0 means it's not
[ConfigID] - This is the configuration master ID for imported quote details and is used to 'group' the parent-child relationships. All rows with the same ConfigID are part of the same configuration grouping
[SODetailID] - If the row was processed into an EA order successfully, this is the EA SOOrderDetails DetailID
[DataErrors] - If there were any errors on this row (customer number not found, bill to not found, ship to not found, item not found, rep not found) will contain the value(s) that were not matched in EA at the time this row was processed
[DateImported] - This is the date time stamp of when this row was inserted into our import table from the 3rd party source
[ImpPriceBook] - If the 3rd party allows for reps to select special pricing books to be used for ordering, this is the value of the PriceBookName they inserted
[ImpRollUpPrice] - If this 'row's price should be rolled up into the Parent line item on the order details , then the value imported should be "1". If the item should not rollup, the value should be "0"
[ImpHideLine] - If this line should be 'hidden' when the EA order prints, this value imported s/b "1". If not to be hidden, value s/b "0". (this is the checkbox on the EA SalesOrder line item edit screen "Hide on sales order/invoice". If this value is "1" (hide), then the ImpRollUpPrice column must be "1" as well.
[ImpJobNumber] - The EAJobNumber to associated with this sales order. The JobNumber must exist in EA before the deal is pushed down if you intend to use Job#'s.
[ImpDeptNumberr] - The Eautomate GL department number to be associated with the sales order line items (requires version 20.x of EA or higher!)
[ImpBranchNumber] - The Eautomate BranchNumber to associate to the sales order. If this value is not imported then the process will use either the Branch assigned to the Customer record, or the SalesReps' branch based upon your settings in the ID634 subscription for VariableZ "BranchSource"
[ImpSalesRepNumber] - The imported value that should match to the EA EmployeeNumber for the SalesRep to assign to the sales order
Sales rep - If the Imported value in ImpSalesRepNumber matched successfully to an EA employee number, the EA employee name will display here.
EAQuoteNumber - If this row came from our process to convert EA Quotes to sales orders, this column is the EAQuote# it came from
ImpQuoteNumber - This is the imported value of the EAQuote# to convert
ImpQuoteID - This is the QuoteID from SOQuotes for our quote conversion imports
ImpQuoteDetailID - This is the DetailID from SOQuoteDetailss for our quote conversion imports
* * *
Related Alerts
Related Alerts
ID966 - ConnectWise Sales Order Sync
* * *
0 Comments