HomeGuides
Submit Documentation FeedbackJoin Developer CommunityLog In

InsiteConnect for IFS reference

This topic describes the InsiteConnect for IFS reference.

Detailed mapping and tables

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 ISC. It is also required if using the Request for Quote functionality in ISC.

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

Field Mapping: Salesperson Refresh
Field NameERP Source (contacts)ISC Destination (Salesperson)Notes
Salesperson NumberContact_salesrep.salesrep_idSalespersonNumberSelect only records from contact_salesrep where delete_flag = ‘N’
NameFirst_name + ‘ ‘ + last_nameName 
Email Addressemail_addressEmail 
TitletitleTitle 
Phone numberdirect_phonePhone1 
Manager Numbersales_manager_idSalesManagerIf provided, we can track the manager of the sales rep which allows access to the sales rep’s accounts and quotes

Payment Methods

The payment terms refresh populates the payment methods table in ISC, 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 ISC 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.

Field Mapping: Payment Terms Refresh
Field NameERP Source (sasta)ISC Destination (PaymentMethod)Notes

Company

Cono

N/A

Used for filtering the correct records only.

Obtain value from the system setting

Record Type

Codeiden = ‘T’

 

Using code ‘T’ to pull in the terms codes from the system admin table

Terms Code

Codeval

Name

 

Description

descrip

Description

 

COD/Cash

n/a

IsCreditCard

This terms code requires payment by credit card on the site

Active Indicator

 

ActivateOn

Not overwritten – set on initial setup to current date

Product tables

Products

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

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

Field Mapping: Product Refresh – Product
Field NameERP Source (inv_mast)ISC Destination (Product)Notes

ERP Item #

item_id

Name

ERPNumber

This is the primary natural key to the table

Item Title &

Item Description

extended_desc

ShortDescription

ERPDescription

Short description will not be overwritten – it is expected to be maintained in the application or from a PIM

URL Segment

item_id

URLSegment

Appending the item number to ensure uniqueness

Tax Code/Class

item_sales_tax_class

TaxCode1

 

Unit of Measure

base_unit

UnitOfMeasure

This is the stocking unit of measure to properly handle conversions

Unit Weight

net_weight

ShippingWeight

 

UNSPSC

unspsc_code

UNSPSC

Not mapped: Used for punchout and needs to be enabled in the Application Dictionary

UPC Code

upc_or_ean_id

UPCCode

Not mapped: Used for punchout and needs to be enabled in the Application Dictionary

Base Unit Price

price1

BasicListPrice

 

Tax Class

item_sales_tax_class

TaxCode1

 

Product Cross-Sell

ISC retrieves Product Cross-Sells via a direct call to the P21 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 P21.

Field Mapping: Product Cross-Sells
Field NameERP SourceISC Destination (ProductRelatedProduct)Notes
Primary Iteminv_mast.inv_mast_UIDProductIDThis is joined to ony match records of the inv_mast_uid field in inv_sub.
Related Iteminv_sub.item_idRelatedProductIDThis is joined to ony match records of the sub_inv_mast_uid field in inv_sub in addition to the above join.

Product Alternate Unit of Measure

ISC retrieves Alternate Units of Measure via a direct call to the P21 database.  This refresh is done as a separate step in the Product Refresh

Deletion Strategy:  ISC uses the Delete option for this data.

Field Mapping: Product Alternate Unit of Measure
Field NameERP SourceISC Destination (ProductUnitOfMeasure)Notes
Product #inv_mast.item_idProduct.ERPNumberLookup to Product
Unit of Measureitem_uom.unit_of_measureUnitOfMeasure 
Conversion Factoritem_uom.unit_sizeQtyPerBaseUnitOfMeasure 

Inventory tables

ISC 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: ISC retrieves all customer records and physically sets the IsActive flag directly from the data, so the Ignore delete action will be used.  ISC must use Ignore, since the data will be retrieved in two separate sweeps.  Delta Dataset option will be disabled.

Field Mapping: Customer (Bill-To)
Field NameERP Source (customer)ISC Destination (Customer)Notes

Customer #

customer_id

CustomerNumber

ERPNumber

 

Ship-To #

 

CustomerSequence

Static Value = Blank

Customer Name

customer_name

Company

 

Address Line 1-3

phys_address[1..3]

Address1..3

 

City

phys_city

City

 

State

phys_state

StateId

Lookup being used – must exist in ISC

Country

phys_country

CountryId

Must match country abbreviation to be valid, uses lookup

Postal Code

phys_postal_code

PostalCode

 

Customer Type

 

CustomerType

Optional field

Email

email_address

Email

Must match proper email format or an error will be generated - default job ignores nulls and "DNE"

Phone 1

central_phone_number

Phone

 

Fax

 

Fax

 

Terms Code

terms_id

TermsCode

Should match a valid payment method;not enforced on refresh

Tax Code 1

 

TaxCode1

Static Value = Blank; Relying on calls to API to determine the tax amount in cart.

Tax Code 2

 

TaxCode2

Currency Code

currency_id

CurrencyId

Must match a valid currency code

Outside Sales Rep

salesrep_id

PrimarySalespersonId

Must match a valid sales rep

Inside Sales Rep

 

 

If desired, can be mapped to Salesperson instead of the outside sales rep.  We only support a single, assigned sales rep to the account.

Credit Limit

credit_limit

CreditLimit

Not enforced as standard feature

Active

delete_flag

IsActive

Y = 0

All others = 1

Field Mapping: Customer (Ship-To)
Field NameERP Source (customer)ISC Destination (Customer)Notes

Customer #

customer_id

CustomerNumber

ERPNumber

 

Ship-To #

 

CustomerSequence

 

Customer Name

customer_name

Company

 

Address Line 1-3

phys_address[1..3]

Address1..3

 

City

phys_city

City

 

State

phys_state

StateId

Lookup – must match a valid state for the country supplied

Country

phys_country

CountryId

Lookup – must match a valid country.

Postal Code

phys_postal_code

PostalCode

 

Customer Type

arsc.custtype

CustomerType

 

Email

email_address

Email

Validated against standard email formats and will error if incorrect.

Phone 1

central_phone_number

Phone

 

Fax

 

Fax

 

Tax Code 1

 

TaxCode1

Static Value = Blank; Relying on calls to API to determine the tax amount in cart.

Tax Code 2

 

TaxCode2

Currency Code

currency_id

CurrencyId

Must match valid currency code

Outside Sales Rep

salesrep_id

PrimarySalespersonId

Must match valid sales rep

Inside Sales Rep

 

PrimarySalespersonId

If desired, can be mapped to Salesperson instead of the outside sales rep.  We only support a single, assigned sales rep to the account.

Credit Limit

credit_limit

CreditLimit

Typically enforced only at customer level but not a standard function

Active

delete_flag

IsActive

Y = 0

All others = 1

Customer Products

Customer-specific product data will be retrieved by ISC via a direct call to the P21 database.

Deletion Strategy:  ISC uses the Delete option to physically remove records that are no longer valid.  ISC also uses Delta Datasets.

Field Mapping: Customer Product
Field NameERP SourceISC Destination (CustomerProduct)Notes
ERP Part #inv_xref.item_idProductIdLookup to Product table
Customer #customer.customer_idCustomerIdOnly pull records WHERE icsec.shipto is blank (bill-to level).  Lookup to customer table.
Customer Part #inv_xref.their_item_idNameCustomer’s product number

Pricing tables

Pricing API

ISC retrieves pricing from P21 via real-time calls, so no refresh is needed.  The API used for this function is GetItemPrice.

Field Mapping: Pricing & Availability
Field NameCommerce Table.FieldERP Web Service FieldNotes
 N/A<B2BSellerVersion>Defaults to MajorVersion of 5, MinorVersion of 11, BuildNumber of 100.
Customer #CustomerNumber <CustomerCode> 
  <StoreName> 
  <LocationID>  
  <ListOfItems> This contains some number of line items as children, each in item tags.
<Item>Information in this tag is for a single item.
Product #Product.ERPNumber<ItemID> 
 Qty Ordered"1" <Quantity>We use this normally to get the default price ofr a quantity of 1.
Unit of Measure  Product.UnitOfMeasure<UnitName> 
</Item>End of Item
</ListofItems>
End of List

Tax (order simulation)

Tax Calculation

Field Mapping: Order Submit – Header
   <B2BSellerVersion>Defaults to MajorVersion of 5, MinorVersion of 11, BuildNumber of 100.
 Customer #CustomerNumber<CustomerCode> 
  <StoreName>  
 Web Order #OrderNumber<WebReferenceNumber>  
  <Anonymous>Hard-coded to N.
 Customer POCustomerPo <PONumber> 
  <NotePadText>  
  "False"<UseContractAddress> Hard-coded to False
  <FreightCode> 
  <ContactID>  
<CustomerShipTo>Contains ShipToIDs and ShipToAddresses>
 Ship-To #CustomerSequence<ShipToID> 
<ShipToAddress>Contains Below Tags
Ship-To Name STCompanyName<ShipToCompanyName> 
 Ship-To Address 1STAddress1 <ShipToAddress1> 
Ship-To Address 2 STAddress2 <ShipToAddress2> 
Ship-to Address 3 STAddress3 <ShipToAddress3> 
Ship-to City STCity <ShipToCity> 
Ship-to State STState <ShipToState> 
Ship-to Postal Code STPostalCode <ShipToZip> 
Ship-to Country STCountry <ShipToCountry> 
</ShipToAddress> 
 Ship-To Contact First Name STFirstName<ShiptoContactFirstName> 
Ship-To Contact Last Name STLastName <ShipToContactLastName> 
Ship-To Phone STPhone <ShipToPhone> 
 Ship-To EmailSTEmail<ShipToEMail> 
 Ship Via ShipVia.ERPShipCode<ShipToCarrierID> 
</CustomerShipTo> 
<CreditCard>This entire envelope is only required for credit card orders.
</CreditCard> 
<ListOfMerchandiseCredits>Contains Merchandise Credit Lines
<MerchandiseCredit>Contains MerchandiseCredit # and Amount
  <MerchandiseCreditNumber> 
  <Amount> 
</MerchandiseCredit> 
</ListofMerchandiseCredits> 
<ListOfLineItems>Contains Line Items
<LineItem>Contains Individual line item information.
 ERP Part #EPRNumber<ItemID> 
 Quantity OrderedQtyOrdered<OrderQuantity> 
 Unit of MeasureUnitOfMeasure<UnitName> 
 Unit Sell PriceNetUnitPrice<UnitPrice> 
 WarehouseWarehouse<SourceLocation> 
  <NotepadText> 
</LineItem> 
</ListOfLineItems> 
<ListOfCoupons> 
</ListOfCoupons> 

Order submission

Order Submit API

ISC submits order to P21 via real-time calls.  The API used for this function is OrderImport.

Field Mapping: Order Submit – Header
   <B2BSellerVersion>Defaults to MajorVersion of 5, MinorVersion of 11, BuildNumber of 100.
 Customer #CustomerNumber<CustomerCode> 
  <StoreName>  
 Web Order #OrderNumber<WebReferenceNumber>  
  <Anonymous>Hard-coded to N.
 Customer POCustomerPo <PONumber> 
  <NotePadText>  
  <UseContractAddress> Hard-coded to False
  <FreightCode> 
  <ContactID>  
<CustomerShipTo>Contains ShipToIDs and ShipToAddresses>
 Ship-To #CustomerSequence<ShipToID> 
<ShipToAddress>Contains Below Tags
Ship-To Name STCompanyName<ShipToCompanyName> 
 Ship-To Address 1STAddress1 <ShipToAddress1> 
Ship-To Address 2 STAddress2 <ShipToAddress2> 
Ship-to Address 3 STAddress3 <ShipToAddress3> 
Ship-to City STCity <ShipToCity> 
Ship-to State STState <ShipToState> 
Ship-to Postal Code STPostalCode <ShipToZip> 
Ship-to Country STCountry <ShipToCountry> 
</ShipToAddress> 
 Ship-To Contact First Name STFirstName<ShiptoContactFirstName> 
Ship-To Contact Last Name STLastName <ShipToContactLastName> 
Ship-To Phone STPhone <ShipToPhone> 
 Ship-To EmailSTEmail<ShipToEMail> 
 Ship Via ShipVia.ERPShipCode<ShipToCarrierID> 
</CustomerShipTo> 
<CreditCard>This entire envelope is only required for credit card orders.
</CreditCard> 
<ListOfMerchandiseCredits>Contains Merchandise Credit Lines
<MerchandiseCredit>Contains MerchandiseCredit # and Amount
  <MerchandiseCreditNumber> 
  <Amount> 
</MerchandiseCredit> 
</ListofMerchandiseCredits> 
<ListOfLineItems>Contains Line Items
<LineItem>Contains Individual line item information.
 ERP Part #EPRNumber<ItemID> 
 Quantity OrderedQtyOrdered<OrderQuantity> 
 Unit of MeasureUnitOfMeasure<UnitName> 
 Unit Sell PriceNetUnitPrice<UnitPrice> 
 WarehouseWarehouse<SourceLocation> 
  <NotepadText> 
</LineItem> 
</ListOfLineItems> 
<ListOfCoupons> 
</ListOfCoupons> 

Credit card processing

ISC calls the payment gateway directly to authorize a user’s credit card.  The authorization information is passed into P21 via the order submission process using an authorization token returned by the payment gateway.

A/R aging balances (invoices)

A/R Aging Balances API

ISC 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 GetMyAccountOpenAR.

Existing orders

Order History

ISC retrieves Order history data from P21 via a direct call to the ERP database.  The data is stored within the ISC 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 P21 order header transaction date AND the order line transaction date.

Orders placed via ISC are immediately added to the ISC order history tables.  This ensures that a user will immediately see their orders on the Order History pages under My Account. Because the P21 connector order submission uses the real-time API, the ERP order # should also be present.

Field Mapping: Order History - Header
Field NameERP Table.Field (oe_hdr)Commerce Table.Field (OrderHistory)Notes

ERP Order #

Order_no

ERPOrderNumber

 

Order Status

Completed

Status

Value - Description

Y - Completed

Any other value - Open

Order Date

order_date

OrderDate

 

Customer #

customer_id

CustomerNumber

 

Customer PO #

po_no

CustomerPO

 

Terms Code

terms

Terms

 

Ship Via

carrier_id

ShipCode

 

Requested Ship Date

requested_date

RequestedDeliveryDate

 

Billing Information: Company/Name

customer_name

BTCompanyName

 

Billing Information: Address

phys_address[1..2]

BTAddress1..2

 

Billing Information: City

phys_city

BTCity

 

Billing Information: State

phys_state

BTState

 

Billing Information : Zip

phys_postal_code

BTPostalCode

 

Billing Information Country

phys_country

BTCountry

 

Shipping Information: Company/Name

ship2_name

STCompanyName

 

Shipping Information: Address

ship2_add[1..2]

STAddress1..2

 

Shipping Information: City

ship2_city

STCity

 

Shipping Information: State

ship2_state

STState

 

Shipping Information: Postal Code

ship2_zip

STPostalCode

 

Shipping Information: Country

ship2_country

STCountry

 

Subtotal

oe_line.extended_price

ProductTotal

This is calculated by summing all of the extended_price lines from the oe_line table for this order.

Sales Tax

oe_line.sales_tax

TaxAmount

This is calculated by summing all of the sales_tax lines from the oe_line table for this order.

Order Total

Calculated

OrderTotal

SUM(Subtotal + Sales Tax)

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, the integration process will use a lookback period when constructing the query. 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.

Field Mapping: Order History - Shipment
Field NameERP Table.Field (oe_pick_ticket)Commerce Table.Field (Shipment)Notes
ERP Order NumberOe_hdr.order_noERPOrderNumber 
Shipment #pick_ticket_noShipmentNumber 
Shipment Dateship_dateShipmentDate 
Field Mapping: Order History – Shipment Packages
Field NameERP Table.Field (oe_pick_ticket)Commerce Table.Field (ShipmentPackage)Notes
Shipment #pick_ticket_noShipmentID 
Ship Viacarrier_idCarrier 
Tracking #tracking_noTrackingNumber 
Freight Amountfreight_outFreight 
Package #line_noPackageNumber 

Invoices

Invoice data is retrieved from P21 via a direct call to ERP database. The data is stored within the ISC 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.

Field Mapping: Invoice History - Header
Field NameERP Table.Field (invoice_hdr)Commerce Table.Field (InvoiceHistory)Notes

Invoice #

invoice_no

InvoiceNumber

 

Invoice Date

invoice_date

InvoiceDate

 

Invoice Due Date

net_due_date

DueDate

 

Invoice Type

 

InvoiceType

Static value = ‘Invoice’

Open Invoice Flag

 pain_in_full_flag

IsOpen

"N" = 1

All other values = 0

Customer #

customer_id

CustomerNumber

 

Ship-To #

ship_to_id

CustomerSequence

 

Customer PO #

po_no

CustomerPO

 

Terms

terms_id

Terms

 

Ship Code

carrier_name

ShipCode

 

Salesperson

salesrep_id

Salesperson

 

Subtotal

Calculated

ProductTotal

total_amount - other_charge_amount - tax_amount

Sales Tax

tax_amount

TaxAmount

 

Shipping

other_charge_amount

ShippingAndHandling

 

Invoice Total

total_amount

InvoiceTotal

 

Current Balance

calculated

CurrentBalance

total_amount - amount_paid

Billing Information: Company/Name

bill2_name

BTCompanyName

 

Billing Information: Address

bill2_address[1..2]

BTAddress1..2

 

Billing Information: City

bill2_city

BTCity

 

Billing Information: Country

bill2_country

BTCountry

 

Billing Information: State

bill2_state

BTState

 

Billing Information: Zip

bill2_postal-code

BTPostalCode

 

Ship-To Information: Company/Name

ship2_name

STCompanyName

 

Ship-To Information: Address

ship2_address[1..2]

STAddress1..2

 

Ship-To Information: City

ship2_city

STCity

 

Ship-To Information: State

ship2_state

STState

 

Ship-To Information : Zip

ship2_postal_code

STPostalCode

 

Ship-To Information : Country

ship2_country

STCountry

 

Field Mapping: Invoice History - Detail
Field NameERP Web Service Field (invoice_line)Commerce Table.Field (InvoiceHistoryLine)Notes
Invoice #invoice_hdr.invoice_noInvoiceHistoryIdconcatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99")
Line Numberline_noLineNumber 
Release Number ReleaseNumberStatic value = 0
Item #item_idProductERPNumber 
Item Descriptionitem_descDescription 
Unit of Measureunit_of_measureUnitOfMeasure 
Qty Invoicedqty_shippedQtyInvoiced 
Unit Priceunit_priceUnitPrice 
Extended Priceextended_priceLineTotal 

Did this page help you?