Sales Order import utility to facilitate 3rd party billing system and inject those imports into eAuto 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 eAuto 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 eAuto Item you created with the imported 'item'. That is a requirement, that the imported 'item' must match to an existing eAuto 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: Hourly
Type of Output: Email
* * *
Sample
Sample
* * *
Variables
Variables
Variable 12: 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: If you would like to have the process update the sales order pop up messages, indicate here the concatenation of the three 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 1: The import table allows for you to import an 'order type' for each of your imported records. If that order type column is left blank, then indicate here the default order type to be used for those imported records. **This is a required field for the integration to work.
Variable 2: Enter the department code to associate to the sales orders that are created
Variable 3: If you wish to place imported orders/quotes converted into sales order on-hold, specify the on-hold code you wish to use in this variable.
Variable 4: The default order status here is 'Pending', If you prefer a different order status on the orders created, enter that actual 'Status' from the available eAutomate order statuses that you want to apply in lieu of 'Pending'. **This is a required field for the integration to work.
Variable 5: If you have imported an invoice number from an external system and you desire that the EAutomate Invoice# match the 3rd party system then enter Yes in the variable.
Variable 6: Enter in 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: Enter in the default drop ship ship method code to use if the imported ship method code = "Drop". **This is a required field for the integration to work.
Variable 8: Enter here the specific OnHoldCode you wish us to use when we identify errors with the sales order being imported. We will import the sales order with whatever we can using defaults with the expectation that when we send the email notification of the errors, someone will edit the sales order in EA and make all the necessary corrections. **This is a required field for the integration to work.
Variable 9: 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. **This is a required field for the integration to work.
Variable 10: If the process is trying to import a line item for the order, but the Item# on the import is not valid, we need the item# you set up (as an expense item) to use on the order. We will write the actual item description from the import table to the sales order line item, but those line items on the sales order using this ErrorItem number would need to be corrected. Example Item#: **ItemError. **This is a required field for the integration to work.
Variable 11: Enter here the on-hold code that will be used to indicate that the sales order is in the process of being imported and should not be opened by users. **This is a required field for the integration to work.
Variable Y: Enter here the third-party integration you are using to create the items from.
Variable X: Enter here if you want the integration to create one sales order per ship to location or one per 'Host' configuration. **This is a required field for the integration to work.
* * *
Alert Functionality
Alert Functionality
(1) Clients can use ETL utility called SKYVVA for integration to SalesForce. For other CRM's, the CRM already has access to their clients EAutomate database and they are writing into our ID634 table directly.
(2) Below is list of table names we need to bring in and other optional tables we can add if your 3rd Party API provides:
[ImpSONumber] - Imported Sales Order Number (if you want that used in eAuto) -OPTIONAL
[ImpInvNumber] - Imported Sales Invoice Number (if you want that used in eAuto) -OPTIONAL
[Description] - Sales Order Description -OPTIONAL
[ImpCustomerNumber] - Must correspond to your eAuto Customer Number - REQUIRED
[ImpShipToCustomerNumber] - If none 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 attached to the SO, else we default to Customer
[BranchNumber] - Not used, we need to apply the Customer BranchID to make e-auto happy
[PONumber] - If provided, wil be entered
[Item] - Must correspond to eAuto, or we will create based on templates you use
[DetailDesc] - Line Item Description (not Item Description) - OPTIONAL
[Qty] - 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 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 default - OPTIONAL
[BinNumber] - let us know if we should use something other than default - OPTIONAL
[OutCost] - let us know if we should use something other than 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 quote in eAuto for you
[QuoteDetailID] - REQUIRED if we need to create quote in eAuto for you
[MailtoAttn] - let us know if we should use something other than default - OPTIONAL
[MailToName] - let us know if we should use something other than default - OPTIONAL
[MailToAddress] - let us know if we should use something other than default - OPTIONAL
[MailToCity] - let us know if we should use something other than default - OPTIONAL
[MailToState] - let us know if we should use something other than default - OPTIONAL
[MailToZip] - let us know if we should use something other than default - OPTIONAL
[MailToCountry] - let us know if we should use something other than default - OPTIONAL
[ShipToAttn] - let us know if we should use something other than default - OPTIONAL
[ShipToName] - let us know if we should use something other than default - OPTIONAL
[ShipToAddress] - let us know if we should use something other than default - OPTIONAL
[ShipToCity] - let us know if we should use something other than default - OPTIONAL
[ShipToState] - let us know if we should use something other than default - OPTIONAL
[ShipToZip] - let us know if we should use something other than default - OPTIONAL
[ShipToCountry] - let us know if we should use something other than 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] - eAuto Contact Number, if available - OPTIONAL
[ReqDate] - let us know if we should use something other than default - OPTIONAL
[ShipMethod] - let us know if we should use something other than default - OPTIONAL
[DetailParentID] - allows 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
* * *
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