📘

Note

As of January 2023, Optimizely no longer adds new functionality to ERP connectors. Instead, partners can build customized solutions for clients. Optimizely continues to fix ERP connector bugs only if there are no workarounds or extensibility solutions available.

## Supporting tables

### Salespeople

The salesperson information is important so that we can set the primary sales rep on the customer records.  This allows your customers to know who their sales reps are, and allows your sales reps to view customer data in Optimizely <<product-name>>. It is also required if using the Request for Quote functionality in <<product-name>>.

**Deletion Strategy** – We will use Ignore and not delete any salesperson records automatically during the refresh.

### Payment methods

The payment terms refresh populates the payment methods table in <<product-name>>, which is referenced in history tables and sets the default value in the customer table used in order submission.    Typically, once this is run, customers can update the descriptions in the Admin Console to reflect the values displayed to the end user, such as ‘Terms’.

**Deletion Strategy** – We will use Ignore and not delete any payment method records automatically during the refresh.

<table class="TableStyle-Borders" data-cellspacing="0"> <colgroup> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> </colgroup> <thead> <tr class="header TableStyle-Borders-Head-Header1"> <th colspan="4" class="TableStyle-Borders-HeadD-Regular-Header1">Field Mapping: Payment Terms Refresh</th> </tr> <tr class="odd TableStyle-Borders-Head-Header1"> <th class="TableStyle-Borders-HeadE-Regular-Header1">Field Name</th> <th class="TableStyle-Borders-HeadE-Regular-Header1">ERP Source (sasta)</th> <th class="TableStyle-Borders-HeadE-Regular-Header1">Destination (PaymentMethod)</th> <th class="TableStyle-Borders-HeadD-Regular-Header1">Notes</th> </tr> </thead> <tbody> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>Company</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>Cono</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>N/A</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1"><p>Used for filtering the correct records only.</p> <p>Obtain value from the system setting</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>Record Type</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>Codeiden = ‘T’</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p> </p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1"><p>Using code ‘T’ to pull in the terms codes from the system admin table</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>Terms Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>Codeval</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>Name</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>Description</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>descrip</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>Description</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>COD/Cash</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>n/a</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1"><p>IsCreditCard</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1"><p>This terms code requires payment by credit card on the site</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyB-Regular-Row1"><p>Active Indicator</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1"><p> </p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1"><p>ActivateOn</p></td> <td class="TableStyle-Borders-BodyA-Regular-Row1"><p>Not overwritten – set on initial setup to current date</p></td> </tr> </tbody> </table>

## Product tables

### Products

The Product Refresh process retrieves item/product information directly from the Prophet 21 ERP database (inv_mast).

**Deletion Strategy** – We will set the Deactivate On flag for the item record.

<table class="TableStyle-Borders" data-cellspacing="0"> <colgroup> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> </colgroup> <thead> <tr class="header TableStyle-Borders-Head-Header1"> <th colspan="4" class="TableStyle-Borders-HeadD-Regular-Header1" width="658">Field Mapping: Product Refresh – Product</th> </tr> <tr class="odd TableStyle-Borders-Head-Header1"> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="125">Field Name</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="124">ERP Source (inv_mast)</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="155">Destination (Product)</th> <th class="TableStyle-Borders-HeadD-Regular-Header1" width="255">Notes</th> </tr> </thead> <tbody> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="125"><p>ERP Item #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="124"><p>item_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="155"><p>Name</p> <p>ERPNumber</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="255"><p>This is the primary natural key to the table</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="125"><p>Item Title &amp;</p> <p>Item Description</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="124"><p>extended_desc</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="155"><p>ShortDescription</p> <p>ERPDescription</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="255"><p>Short description will not be overwritten – it is expected to be maintained in the application or from a PIM</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="125"><p>URL Segment</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="124"><p>item_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="155"><p>URLSegment</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="255"><p>Appending the item number to ensure uniqueness</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="125"><p>Tax Code/Class</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="124"><p>item_sales_tax_class</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="155"><p>TaxCode1</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="255"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="125"><p>Unit of Measure</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="124"><p>base_unit</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="155"><p>UnitOfMeasure</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="255"><p>This is the stocking unit of measure to properly handle conversions</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="125"><p>Unit Weight</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="124"><p>net_weight</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="155"><p>ShippingWeight</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="255"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="125"><p>UNSPSC</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="124"><p>unspsc_code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="155"><p>UNSPSC</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="255"><p>Not mapped: Used for punchout and needs to be enabled in the Application Dictionary</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="125"><p>UPC Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="124"><p>upc_or_ean_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="155"><p>UPCCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="255"><p>Not mapped: Used for punchout and needs to be enabled in the Application Dictionary</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="125"><p>Base Unit Price</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="124"><p>price1</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="155"><p>BasicListPrice</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="255"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="125"><p>Tax Class</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="124"><p>item_sales_tax_class</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="155"><p>TaxCode1</p></td> <td class="TableStyle-Borders-BodyA-Regular-Row1" width="255"><p> </p></td> </tr> </tbody> </table>

### Product Cross-Sell

<<product-name>> retrieves Product Cross-Sells via a direct call to the Prophet 21 database. This refresh can be modified or copied to use specific record types or relationships needed for a given implementation.

**Deletion Strategy** – This refresh uses the Ignore option. Users may create manual entries for different types of relationships that are not managed in Prophet 21.

### Product Alternate Unit of Measure

<<product-name>> retrieves Alternate Units of Measure via a direct call to the Prophet 21 database.  This refresh is done as a separate step in the Product Refresh

**Deletion Strategy** – <<product-name>> uses the Delete option for this data.

## Inventory tables

<<product-name>> uses real-time calls to obtain pricing and availability via real-time calls. No refresh of inventory will be used.  See the section on Pricing for the API call mapping.

## Customer tables

### Customer

The customer refresh pulls both Bill-To and Ship-to information into a common customer table.

**Deletion Strategy** – <<product-name>> retrieves all customer records and physically sets the IsActive flag directly from the data, so the Ignore delete action will be used. <<product-name>> must use Ignore, since the data will be retrieved in two separate sweeps.

<table class="TableStyle-Borders" data-cellspacing="0"> <colgroup> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> </colgroup> <thead> <tr class="header TableStyle-Borders-Head-Header1"> <th colspan="4" class="TableStyle-Borders-HeadD-Regular-Header1" width="658">Field Mapping: Customer (Bill-To)</th> </tr> <tr class="odd TableStyle-Borders-Head-Header1"> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="102">Field Name</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="110">ERP Source (customer)</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="171">Destination (Customer)</th> <th class="TableStyle-Borders-HeadD-Regular-Header1" width="276">Notes</th> </tr> </thead> <tbody> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Customer #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>customer_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>CustomerNumber</p> <p>ERPNumber</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Ship-To #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>CustomerSequence</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Static Value = Blank</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Customer Name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>customer_name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>Company</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Address Line 1-3</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>phys_address[1..3]</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>Address1..3</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>City</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>phys_city</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>City</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>State</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>phys_state</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>StateId</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Lookup being used – must exist in <<product-name>></p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>phys_country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>CountryId</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Must match country abbreviation to be valid, uses lookup</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Postal Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>phys_postal_code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>PostalCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Customer Type</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>CustomerType</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Optional field</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Email</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>email_address</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>Email</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Must match proper email format or an error will be generated - default job ignores nulls and "DNE"</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Phone 1</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>central_phone_number</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>Phone</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Fax</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>Fax</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Terms Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>terms_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>TermsCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Should match a valid payment method;not enforced on refresh</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Tax Code 1</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>TaxCode1</p></td> <td rowspan="2" class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Static Value = Blank; Relying on calls to API to determine the tax amount in cart.</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Tax Code 2</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>TaxCode2</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Currency Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>currency_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>CurrencyId</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Must match a valid currency code</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Outside Sales Rep</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>salesrep_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>PrimarySalespersonId</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Must match a valid sales rep</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Inside Sales Rep</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p> </p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>If desired, can be mapped to Salesperson instead of the outside sales rep.  We only support a single, assigned sales rep to the account.</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="102"><p>Credit Limit</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="110"><p>credit_limit</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="171"><p>CreditLimit</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="276"><p>Not enforced as standard feature</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="102"><p>Active</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="110"><p>delete_flag</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="171"><p>IsActive</p></td> <td class="TableStyle-Borders-BodyA-Regular-Row1" width="276"><p>Y = 0</p> <p>All others = 1</p></td> </tr> </tbody> </table>

<table class="TableStyle-Borders" data-cellspacing="0"> <colgroup> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> </colgroup> <thead> <tr class="header TableStyle-Borders-Head-Header1"> <th colspan="4" class="TableStyle-Borders-HeadD-Regular-Header1" width="658">Field Mapping: Customer (Ship-To)</th> </tr> <tr class="odd TableStyle-Borders-Head-Header1"> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="129">Field Name</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="130">ERP Source (customer)</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="183">Destination (Customer)</th> <th class="TableStyle-Borders-HeadD-Regular-Header1" width="216">Notes</th> </tr> </thead> <tbody> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Customer #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>customer_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>CustomerNumber</p> <p>ERPNumber</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Ship-To #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>CustomerSequence</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Customer Name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>customer_name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>Company</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Address Line 1-3</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>phys_address[1..3]</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>Address1..3</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>City</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>phys_city</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>City</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>State</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>phys_state</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>StateId</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p>Lookup – must match a valid state for the country supplied</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>phys_country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>CountryId</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p>Lookup – must match a valid country.</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Postal Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>phys_postal_code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>PostalCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Customer Type</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>arsc.custtype</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>CustomerType</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Email</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>email_address</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>Email</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p>Validated against standard email formats and will error if incorrect.</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Phone 1</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>central_phone_number</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>Phone</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Fax</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>Fax</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Tax Code 1</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>TaxCode1</p></td> <td rowspan="2" class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p>Static Value = Blank; Relying on calls to API to determine the tax amount in cart.</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Tax Code 2</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>TaxCode2</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Currency Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>currency_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>CurrencyId</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p>Must match valid currency code</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Outside Sales Rep</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>salesrep_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>PrimarySalespersonId</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p>Must match valid sales rep</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Inside Sales Rep</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>PrimarySalespersonId</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p>If desired, can be mapped to Salesperson instead of the outside sales rep.  We only support a single, assigned sales rep to the account.</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"><p>Credit Limit</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"><p>credit_limit</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"><p>CreditLimit</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"><p>Typically enforced only at customer level but not a standard function</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="129"><p>Active</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="130"><p>delete_flag</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="183"><p>IsActive</p></td> <td class="TableStyle-Borders-BodyA-Regular-Row1" width="216"><p>Y = 0</p> <p>All others = 1</p></td> </tr> </tbody> </table>

### Customer Products

Customer-specific product data will be retrieved by <<product-name>> via a direct call to the Prophet 21 database.

**Deletion Strategy** – <<product-name>> uses the Delete option to physically remove records that are no longer valid. <<product-name>> also uses Delta Datasets.

## Pricing tables

### Pricing API

<<product-name>> retrieves pricing from Prophet 21 via real-time calls, so no refresh is needed.  The API used for this function is <span class="wysiwyg-color-black">GetItemPrice</span>.

<table class="TableStyle-Borders" data-cellspacing="0"> <colgroup> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> </colgroup> <thead> <tr class="header TableStyle-Borders-Head-Header1"> <th colspan="4" class="TableStyle-Borders-HeadD-Regular-Header1" width="658">Field Mapping: Pricing &amp; Availability</th> </tr> <tr class="odd TableStyle-Borders-Head-Header1"> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="129">Field Name</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="130">Commerce Table.Field</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="183">ERP Web Service Field</th> <th class="TableStyle-Borders-HeadD-Regular-Header1" width="216">Notes</th> </tr> </thead> <tbody> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"> </td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130">N/A</td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183">&lt;B2BSellerVersion&gt;</td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216">Defaults to MajorVersion of 5, MinorVersion of 11, BuildNumber of 100.</td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129">Customer #</td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130">CustomerNumber</td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183"> &lt;CustomerCode&gt;</td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"> </td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"> </td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"> </td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183">&lt;StoreName&gt;</td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"> </td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"> </td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"> </td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183">&lt;LocationID&gt; </td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"> </td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td colspan="3" class="TableStyle-Borders-BodyE-Regular-Row1" width="129">  &lt;ListOfItems&gt; </td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216">This contains some number of line items as children, each in item tags.</td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td colspan="3" class="TableStyle-Borders-BodyE-Regular-Row1" width="129">&lt;Item&gt;</td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216">Information in this tag is for a single item.</td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129">Product #</td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130">Product.ERPNumber</td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183">&lt;ItemID&gt;</td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"> </td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129"> Qty Ordered</td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130">"1" </td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183">&lt;Quantity&gt;</td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216">We use this normally to get the default price ofr a quantity of 1.</td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="129">Unit of Measure </td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="130"> Product.UnitOfMeasure</td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="183">&lt;UnitName&gt;</td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216"> </td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td colspan="3" class="TableStyle-Borders-BodyE-Regular-Row1" width="129">&lt;/Item&gt;</td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="216">End of Item</td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td colspan="3" class="TableStyle-Borders-BodyB-Regular-Row1" width="129"><table> <tbody> <tr class="odd"> <td width="183">&lt;/ListofItems&gt;</td> </tr> </tbody> </table></td> <td class="TableStyle-Borders-BodyA-Regular-Row1" width="216">End of List</td> </tr> </tbody> </table>

## Tax (order simulation)

### Tax Calculation

Use an API to calculate if the tax calculator is set to Prophet 21 and uses the API GetOrderSummary.

## Order submission

### Order Submit API

<<product-name>> submits order to Prophet 21 via real-time calls.  The API used for this function is <span class="wysiwyg-color-black">OrderImport</span>.

## Credit card processing

<<product-name>> calls the payment gateway directly to authorize a user’s credit card.  The authorization information is passed into Prophet 21 via the order submission process using an authorization token returned by the payment gateway.

## A/R aging balances (invoices)

### A/R Aging Balances API

<<product-name>> uses a standard direct call to an API endpoint to display the A/R aging information on the Invoice History page.  The standard aging buckets must be set up as global options in Settings.  We do not use the labels returned from the API, only the balances themselves.

The API used is <span class="wysiwyg-color-black">GetMyAccountOpenAR</span>.

## Existing orders

### Order History

<<product-name>> retrieves Order history data from Prophet 21 via a direct call to the ERP database. The data is stored within the <<product-name>> database and refreshed on a regular interval, typically once a day.

To limit the number of order records analyzed, the integration process uses a lookback period in the query.  This query is performed against the Prophet 21 order header transaction date AND the order line transaction date.

Orders placed in <<product-name>> are immediately added to the order history tables.  This ensures that a user will immediately see their orders on the Order History pages under My Account. Because Prophet 21’s Order Submit API does not dynamically create the order, we had to create a batch process to find orders that were created from the Web and update them in <<product-name>> prior to running Order History. It is imperative that the Order Assignment job is run first or else the order history records for orders that started on the web will be duplicated in history since the ERP Number is the data we anchor to for creating or updating the information. This job specifically looks back 5 days (defined in the job’s where clause and can be adjusted) to find web-based orders (based on the ‘taker’ field = ‘ESTORE’) and update them in <<product-name>>.

<table class="TableStyle-Borders" width="673" data-cellspacing="0"> <colgroup> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> </colgroup> <thead> <tr class="header TableStyle-Borders-Head-Header1"> <th colspan="4" class="TableStyle-Borders-HeadD-Regular-Header1" width="673">Field Mapping: Order History - Header</th> </tr> <tr class="odd TableStyle-Borders-Head-Header1"> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="141">Field Name</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="159">ERP Table.Field (oe_hdr)</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="144">Commerce Table.Field (OrderHistory)</th> <th class="TableStyle-Borders-HeadD-Regular-Header1" width="229">Notes</th> </tr> </thead> <tbody> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>ERP Order #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>Order_no</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>ERPOrderNumber</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Order Status</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>Completed</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>Status</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p>Value - Description</p> <p>Y - Completed</p> <p>Any other value - Open</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Order Date</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>order_date</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>OrderDate</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Customer #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>customer_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>CustomerNumber</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Customer PO #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>po_no</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>CustomerPO</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Terms Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>terms</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>Terms</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Ship Via</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>carrier_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>ShipCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Requested Ship Date</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>requested_date</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>RequestedDeliveryDate</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"> </td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Billing Information: Company/Name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>customer_name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>BTCompanyName</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Billing Information: Address</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>phys_address[1..2]</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>BTAddress1..2</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Billing Information: City</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>phys_city</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>BTCity</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Billing Information: State</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>phys_state</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>BTState</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Billing Information : Zip</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>phys_postal_code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>BTPostalCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Billing Information Country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>phys_country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>BTCountry</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Shipping Information: Company/Name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>ship2_name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>STCompanyName</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Shipping Information: Address</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>ship2_add[1..2]</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>STAddress1..2</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Shipping Information: City</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>ship2_city</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>STCity</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Shipping Information: State</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>ship2_state</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>STState</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Shipping Information: Postal Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>ship2_zip</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>STPostalCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Shipping Information: Country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>ship2_country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>STCountry</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Subtotal</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>oe_line.extended_price</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>ProductTotal</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p>This is calculated by summing all of the extended_price lines from the oe_line table for this order.</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="141"><p>Sales Tax</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="159"><p>oe_line.sales_tax</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="144"><p>TaxAmount</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="229"><p>This is calculated by summing all of the sales_tax lines from the oe_line table for this order.</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="141"><p>Order Total</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="159"><p>Calculated</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="144"><p>OrderTotal</p></td> <td class="TableStyle-Borders-BodyA-Regular-Row1" width="229"><p>SUM(Subtotal + Sales Tax)</p></td> </tr> </tbody> </table>

### Shipments

The shipment refresh is dependent on having shipment data in the ERP from which we can obtain tracking information.  This refresh is typically run once per day and also uses a lookback period from which to run the query to limit the number of order records analyzed. This query is used against the order header transaction date only since we do not expect shipment records to change once generated.

**Deletion Strategy** – Since this is not a full snapshot and this data is highly unlikely to change once processed, the Ignore delete action is used.

### Invoices

Invoice data is retrieved from Prophet 21 via a direct call to ERP database. The data is stored within the <<product-name>> database and refreshed on a regular interval, typically once a day.

To limit the number of records analyzed, the integration process will use a lookback period when constructing the query. This query is used against the invoice date since we do not expect records to change once invoiced.

Refer to the Implementation Notes in Order History Refresh for additional information.

**Deletion Strategy** –  We will employ the Ignore strategy, as we will only be taking a snapshot and do not expect line information to change once the status of the order is changed to Invoiced.

<table class="TableStyle-Borders" data-cellspacing="0"> <colgroup> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> <col style="width: 25%" /> </colgroup> <thead> <tr class="header TableStyle-Borders-Head-Header1"> <th colspan="4" class="TableStyle-Borders-HeadD-Regular-Header1" width="658">Field Mapping: Invoice History - Header</th> </tr> <tr class="odd TableStyle-Borders-Head-Header1"> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="131">Field Name</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="126">ERP Table.Field (invoice_hdr)</th> <th class="TableStyle-Borders-HeadE-Regular-Header1" width="150">Commerce Table.Field (InvoiceHistory)</th> <th class="TableStyle-Borders-HeadD-Regular-Header1" width="252">Notes</th> </tr> </thead> <tbody> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Invoice #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>invoice_no</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>InvoiceNumber</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"> </td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Invoice Date</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>invoice_date</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>InvoiceDate</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Invoice Due Date</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>net_due_date</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>DueDate</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"> </td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Invoice Type</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p> </p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>InvoiceType</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p>Static value = ‘Invoice’</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Open Invoice Flag</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p> pain_in_full_flag</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>IsOpen</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p>"N" = 1</p> <p>All other values = 0</p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Customer #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>customer_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>CustomerNumber</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Ship-To #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>ship_to_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>CustomerSequence</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Customer PO #</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>po_no</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>CustomerPO</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Terms</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>terms_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>Terms</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Ship Code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>carrier_name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>ShipCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Salesperson</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>salesrep_id</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>Salesperson</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Subtotal</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>Calculated</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>ProductTotal</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p>total_amount - other_charge_amount - tax_amount</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Sales Tax</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>tax_amount</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>TaxAmount</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Shipping</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>other_charge_amount</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>ShippingAndHandling</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Invoice Total</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>total_amount</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>InvoiceTotal</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"> </td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Current Balance</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>calculated</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>CurrentBalance</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p>total_amount - amount_paid</p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Billing Information: Company/Name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>bill2_name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>BTCompanyName</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Billing Information: Address</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>bill2_address[1..2]</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>BTAddress1..2</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Billing Information: City</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>bill2_city</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>BTCity</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Billing Information: Country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>bill2_country</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>BTCountry</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Billing Information: State</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>bill2_state</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>BTState</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Billing Information: Zip</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>bill2_postal-code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>BTPostalCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Ship-To Information: Company/Name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>ship2_name</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>STCompanyName</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Ship-To Information: Address</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>ship2_address[1..2]</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>STAddress1..2</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Ship-To Information: City</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>ship2_city</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>STCity</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Ship-To Information: State</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>ship2_state</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>STState</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="odd TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="131"><p>Ship-To Information : Zip</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="126"><p>ship2_postal_code</p></td> <td class="TableStyle-Borders-BodyE-Regular-Row1" width="150"><p>STPostalCode</p></td> <td class="TableStyle-Borders-BodyD-Regular-Row1" width="252"><p> </p></td> </tr> <tr class="even TableStyle-Borders-Body-Row1"> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="131"><p>Ship-To Information : Country</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="126"><p>ship2_country</p></td> <td class="TableStyle-Borders-BodyB-Regular-Row1" width="150"><p>STCountry</p></td> <td class="TableStyle-Borders-BodyA-Regular-Row1" width="252"><p> </p></td> </tr> </tbody> </table>