Reference for connectors for A+

Order history

Order history data is pulled from A+ via a direct call to ERP database. The data is stored within the Commerce database and refreshed on a regular interval, typically once a day.

To limit the number of A+ order records analyzed, the integration process will use a lookback period when constructing the query but only against history. Note that there are 2 source tables: orhed is for open orders and hshed is for historical orders. The intent is to pull this data through a union query to present a single dataset. We will pull the entirety of the current order table and only use the lookback for historical orders.

During end of day in A+, invoiced orders are moved from orhed to hshed so the order never lives in both places simultaneously. In orhed, the order is defined by ohcono/ohorno/ohorgn (company, order #, generation) and in history this is company/order/origin and sequence (oahssq) to make it unique. Technically, the sequence itself is unique but it s possible to have more than one although we will not be supporting duplicates.

Note that we understand that, over time, the order number itself can wrap around are be restarted. We are not accounting for this in the standard connector under the premise that we would not store more than, say 3 years of historical data and that this wraparound of the orders will not occur in this timeframe

Orders placed on the site are immediately added to the Commerce order history tables. This ensures that a user will immediately see their orders if they go to Order History pages in the My Account area of the site. If the order submission works in real-time, then the ERP order # should be present as well.

Implementation Note - Performance: Because many A+ customers have very large tables and the system must perform a table scan which can make this refresh take a very long time. For performance purposes, the customer may wish to establish a custom index on hshed.oadndt.

Implementation Note Lookback Days: There is a general strategy to limit the amount of data being reviewed and transmitted to the website for large, history files. The idea is to only look for records that have changed within a relatively short period of time. We use a default of 5 days to cover any missed refreshes, weekends and holidays. The number could be expanded or shortened based on implementation preferences. It is coded directly into the WHERE clauses of the history refreshes. For history, use hshed.oadncc = 20 and hshed.oadndt - #/days (integer field).

Deletion Strategy: There is a special strategy for Order History because we don t want to do a full snapshot of the data but we need to catch any changes to, say, order lines that were deleted. A special option to Delete Children will be used with the delete action set to Delete. This way, any order that is processed will clear and reload the order line data to history.

Field mapping: Order history header

Field Name	ERP Table.Field (orhed/hshed)	Commerce Table.Field (OrderHistory)	Notes
Company	Ohcono/oacono		Lookup to Selecting records based on defined company number in settings Only pull in order types O, R, I, B
ERP Order #	Ohorno + ohorgn/oaorno + oaorgn	ERPOrderNumber	concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99")
Order Status	Ohorst/ I	Status	Value - Description 1 Ready to Print 2 Pick List Printed 3 Ship Confirmed 4 - Invoiced 9 On Hold I - Invoiced
History Sequence #	Hshed.oahssq	N/A	Used to link to history detail
Order Date	Ohetdt (yymmdd)/oaentd(ccyymmdd)	OrderDate
Order Type/ Transaction Type	Ohortp/oaortp		No real landing place for this. Values: O Orders R Returns I = Invoice Only B Entire order on B/O hold
Customer #	Ohcsno/oacsno	CustomerNumber
Ship-To #	Ohshp#/oashp#	CustomerSequence
Customer PO #	Ohcspo/oacspo	CustomerPO
Terms Code	Ohtmcd/oatmcd	Terms
Ship Via		ShipCode
Requested Ship Date		RequestedDeliveryDate	Note that these are not exactly the same
Salesperson		Salesperson
Billing Information: Company/Name	Ohcsnm Hsnam.oyname	BTCompanyName	History to hsnam on oyhssq = oahssq and oynmtp = B
Billing Information: Address	Ohcad1..4 Hsman/oyadd1..4	BTAddress1..2
Billing Information: City	Ohblcy Hsman.oycity	BTCity
Billing Information: State	Ohblst Hsnam.oystat	BTState
Billing Information : Zip	Ohblzp Hsnam.oyzip4	BTPostalCode
Billing Information Country	Ohctid Hsnam/oyctid	BTCountry
Shipping Information: Company/Name	Ohshnm Hsnam.oynam	STCompanyName	History to hsnam on oyhssq = oahssq and oynmtp = S
Shipping Information: Address	Ohsad1..4 Hsnam.oyadd1..4	STAddress1..2
Shipping Information: City	Ohscty Hsnam/oyciity	STCity
Shipping Information: State	Ohshst Hsnam.oystat	STState
Shipping Information: Postal Code	Ohszip Hsnam.oyzip4	STPostalCode
Shipping Information: Country	Ohshci Hsnam.oyctid	STCountry
Subtotal	See notes	ProductTotal	If orhed.ohorst = 1,2,9 then sum(ordet.odinvl) only I line types If ohorst = 3,4 then ohisam If history hshed.oaisam
Order Discount		OrderDiscountAmount
Shipping		ShippingCharges	No separate field
Misc. Charges	Ohscam/oascam	OtherCharges	Includes shipping only open orders with status 3,4
Sales Tax	Ohtxam/oatxam	TaxAmount	Open status 3,4 only
Order Total	Calculated	OrderTotal	If from history, oainam If from open: If ohorst = 3 or 4, then ohinam If ohorst = 1,2,9 then ohorvl
Order Notes		Notes

Field mapping: Order history detail

Field Name	ERP Table.Field (ordet/hsdet)	Commerce Table.Field (OrderHistoryLine)	Notes
Company	Odcono/obcono		Lookup to Selecting records based on defined company number in settings Only select line items where ordet.odlitp = I or hsdet.oblitp = I (line item type)
ERP Order #	Odorno + odorgn/oborno + oborgn	OrderHistoryId	concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99")
Line Number	Odorsq/oborsq	LineNumber
Customer #	Odorno/oborno	CustomerNumber
Ship-To #	Odshp#/obshp#	CustomerSequence
Line Type		LineType	Static Value = 'Product'
Item #	Oditno/obitno	ProductERPNumber
Item Description	Oditd1 + oditd2/obitd1 + oditd2	Description
Warehouse	Odwhid/obwhid	Warehouse
Unit of Measure	See notes	UnitOfMeasure	If (ordet.odcoum <> blank and ordet.odcqto <> 0) then ordet.odcoum else ordet.odunms If (hsdset.obcoum <> blank and hsdet.obcqto <> 0) then hsdet.obcoum else hsdet.obunms
QTY Ordered	See notes	QtyOrdered	Different fields: Obqtor = stk qty ordered Obcqto = customer qty ordered Obcoum = customer u/m ordered Obunms = stock u/m Obprum = pricing u/m If (ordet.odcoum <> blank and ordet.odcqto <> 0) then ordet.odcqto else ordet.odqtor If (hsdet.obcoum <> blank and hsdet.obcqto <> 0) then hsdet.obcqto else hsdet.obqtor
QTY Shipped	See notes	QtyShipped	If (ordet.odcoum <> blank and ordet.odcqto <> 0) then (ordet.odqtsh / ordet.odqtor) * ordet.odcqto else ordet.odqtsh If (hsdet.obcoum <> blank and hsdet.obcqto <> 0) then (hsdet.obqtsh / hsdet.obqtor) * hsdet.obcqto else hsdet.obqtsh
Inventory Qty Ordered		InventoryQtyOrdered	Skip
Inventory Qty Shipped		InventoryQtyShipped	Skip
Unit Price		UnitNetPrice	Open status 1,2,9: Extended price / qty ordered) Open status 3,4 If QtyShipped <> 0 then extended price / qty shipped Else ordet.odlnvl / qty ordered History If qty shipped <> 0 then oblnam / qty shipped Else oblnam / qty ordered Preference to use shipped extension vs ordered
Extended Price		LineTotal	If orhed.ohorst = 1,2,9 then ordet.odlnvl else odinam History = hsdet.oblnam
Ship Date		LastShipDate
Line Notes	Ordet.oditd1 + oditd2 Hsdet.obitd1 + obitd2	Notes	Not including in standard if desired, include this line type. Only line type odlitp = M For history, hsdet.oblitp = M by sequence (obhssq = oahssq)

Invoices

Invoice data is pulled from A+ via a direct call to ERP database. The data is stored within the Commerce database and refreshed on a regular interval, typically once a day.

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

There are 2 files for invoices open (ardhd) and closed (arhhd). Except for general backloading of the data, we will only be pulling from the open file. Additionally, since we will only be displaying invoice summary and A/R information, we will not be obtaining full customer/ship-to addresses and other order-related items. If additional order level information is required, tie to hshed using hshed.oahssq = ardhd.rhhsid. We will use a delta dataset and grab the entire open invoice table on each refresh.

Deletion Strategy: We will employ the Ignore strategy since 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 Name	ERP Table.Field (ardhd)	Commerce Table.Field (InvoiceHistory)	Notes
Company	Rhcono		Lookup to Selecting records based on defined company number in settings
Invoice #	Rhinno	InvoiceNumber
Invoice Date	Rhivdt	InvoiceDate	Yymmdd only
Invoice Due Date	Rhcddt	DueDate	Yymmdd only
Invoice Type	Rhintp	InvoiceType	Blank = invoice, F = finance charge
Invoice Status		Status
Open Invoice Flag		IsOpen	If rhinbl <> 0, open else closed
Customer #	rhcsno	CustomerNumber
Ship-To #		CustomerSequence
Currency		CurrencyCode
Customer PO #		CustomerPO
Terms	Rhtmcd	Terms	Terms description in ORCTL (see Payment Methods refresh)
Ship Code		ShipCode
Salesperson		Salesperson
Subtotal		ProductTotal
Sales Tax		TaxAmount
Shipping		ShippingAndHandling
Discount Amount		DiscountAmount
Misc Charges		OtherCharges
Invoice Total		InvoiceTotal
Current Balance	rhinbl	CurrentBalance	Links from invoice history to A/R using hshed.oahssq = ardhd.rhhsid
Billing Information	Using rhslto	BT fields	See order history
Shipping Information		ST fields	See order history

Existing orders

A/R aging balances API

A standard direct call to the API endpoint will be used on the standard Invoice History page to display the A/R aging information. The standard aging buckets must be set up in Settings as global options for the aging ranges. We will not be using the returned labels from the API, only the balances themselves.

The API used for this information will be ARSummary.

Field mapping: A/R aging

Field Name	Commerce Table.Field (Current Session Context)	ERP Web Service Field	Notes
Company Number		Parameter 1: Company #
Customer #	BillTo.ERPNumber	Parameter 2: Customer #
Aging Period Balances		<AgePeriodXAmt>	X = 1..4
Future Amount		<FutureAmount>

A/R aging balances (invoices)

Commerce will call the payment gateway (CenPOS) directly to authorize a user s credit card. The authorization information is passed into A+ via the order submission process using a token returned by the payment gateway.

Credit card processing

Order submit API

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

Field mapping: Order submit header

Field Name	Commerce Table.Field (CustomerOrder)	ERP Web Service Field	Notes
Customer #	CustomerNumber	<CustomerID>
Customer PO	CustomerPo	<PONumber>
Order Number	OrderNumber	<WebOrderID>
Order Type	Type	<OrderType>
Bill-To Contact	BTFirstName + + BTLastName	<BillToContact>
Bill-To Name	BTCompanyName	<CustomerName>
Bill-To Address Line 1	BTAddress1	<CustomerAddr1>
Bill-To Address Line 2	BTAddress2	<CustomerAddr2>
Bill-To Address Line 3	BTAddress3	<CustomerAddr3>
Bill-To Address Line 4	BTAddress4	<CustomerAddr4>
Bill-To City	BTCity	<BillToCity>
Bill-To State	BTState	<BillToState>
Bill-To Postal Code	BTPostalCode	<BillToZip>
Bill-To Country	BTCountry	<BillToCntryCode>
Bill-To Phone	BTPhone	<BillToPhone>
Ship-To #	CustomerSequence	<ShipToNumber>
Ship-To Contact	STFirstName + + STLastName	<ShipToContact>	For actual order submission, if there is no change to the customer record, we should not populate any of the ship-to address fields to mimic standard behavior. Only populate if it s a 1-time or new address.
Ship-To Name	STCompanyName	<ShipToName>
Ship-To Address Line 1	STAddress1	<ShipToAddr1>
Ship-To Address Line 2	STAddress2	<ShipToAddr2>
Ship-To Address Line 3	STAddress3	<ShipToAddr3>
Ship-To Address Line 4	STAddress4	<ShipToAddr4>
Ship-To City	STCity	<ShipToCity>
Ship-To State	STState	<ShipToState>
Ship-To Postal Code	STPostalCode	<ShipToZip>
Ship-To Country	STCountry	<ShipToCntryCode>	Country code must be derived by finding the B2B country and using the Abbreviation field as the code the API will allow any value but the application itself will not work with bad country codes.
Ship-To Phone	STPhone	<ShipToPhone>
Ship Via	ShipVia.ERPShipCode	<CarrierCode>
Requested Ship Date	RequestedShipDate	<ReqShipDate>
Transaction Type		<WebTransactionType>	Value = LSF (Order Load) to submit order Value = TSF (Order Total) to use for order simulate/tax calculation
Web User	UserName	<WebUserID>

Field mapping: Credit card data

Field Name	Commerce Table.Field	ERP Web Service Field	Notes
CC Customer ID	2 character Company Code + 10 character Customer # (include leading 0's if less than 10)	CCCustomerID
Credit Card #	Static Value = <Blank>	CCCreditCardNbr
Credit Card Payment Type	Static Value = <Blank>	CCPaymentType
Credit Card Exp Date	Static Value = <Blank>	CCCreditCardExp
Card Holder	Static Value = <Blank>	CCCardHolder
CVV	Static Value = <Blank>	CCCVV2
Card Billing Address 1	Static Value = <Blank>	CCAddr1
Card Billing Address 2	Static Value = <Blank>	CCAddr2
Card Billing Address 3	Static Value = <Blank>	CCAddr3
Card Billing Address 4	Static Value = <Blank>	CCAddr4
Card Billing City	Static Value = <Blank>	CCCity
Card Billing State	Static Value = <Blank>	CCState
Card Billing ZIP	Static Value = <Blank>	CCZip
Card Billing Country	Static Value = <Blank>	CCCountry
PO Number	CustomerOrder.CustomerPO	CCPONumber
Ship To Zip	CustomerOrder.STZip	CCShipToZip
Tax Amount	Use value from TFS call (that is order simulation) if CC order	CCTaxAmount
Authorization Amount	CreditCardTransaction.Amount	CCAuthorizationAmount	Note: This value will be the authorization amount and NOT the order total. The value will include the percentage increase.
Merchant ID	Optimizely Application Setting: PaymentGateway_Cenpos_MerchantId	CCMerchantId
Card Number - Masked	CreditCardTransaction. CreditCardNumber	CCMaskedCard	Value will be the masked card # sent in CenPOS response
Card Token	CreditCardTransaction.Token1	CCToken
Authorization Number	CreditCardTransaction.AuthCode	CCAuthorizationNumber
Reference Number	CreditCardTransaction.PNRef	CCReferenceNumber
Credit Card Type	CreditCardTransaction.CardType	CCCardType	Values submitted will be the values obtained from CenPOS: Visa , MasterCard , Amex or Discover
CC End	Static Value = Y	CCEND

Field mapping: Order submit detail/line

Field Name	Commerce Table.Field (OrderLine)	ERP Web Service Field	Notes
Line number	Line	lineIden
ERP Item #	ErpNumber	<ItemId>
Quantity Ordered	QtyOrdered	<OrderQty>
Unit of Measure	UnitOfMeasure	<UnitOfMeasure>
Unit Price	UnitRegularPrice	<ListPrice>
Warehouse	WarehouseId	<WarehouseID>
Description	Description	<ItemDesc1>
Due Date	DueDate	<DueDate>
ActualPrice	ActualPrice	<ActualSellPrice>
Regular Price	RegularPrice	<ListPrice>

As part of the standard connector for A+, there will be a Tax Calculator specific to the connector which will make an API call to A+ to calculate the tax amount and return the tax amount to the website on the Cart (if configured) and Checkout screens. If another tax calculator, such as Avalara, is used, then this Order Simulate function will not be used.

The API CreateOrder will be used for this function.

Tax (Order Simulation)

Pricing API

Pricing and inventory will be done using real-time calls to A+ so no refresh will be needed. The API used for this function is GetAvail.

Pricing: The requested information will be calculated and returned on the basis of customer number, company number, item number, unit of measure, and quantity.

Availability: If it is Default and alternate warehouses then the quantity will be the sum of the default warehouse and the quantity available in the alternate warehouses. If the DftWhsOnly element is N then the quantity will be the sum of the default warehouse and the quantity available in the alternate warehouses. If the DftWhsOnly element is Y then it will only display the quantity available in the default warehouse.

Field mapping: Pricing and availability

Field Name	Commerce Table.Field (ProdDataPrcAvail)	ERP Web Service Field	Notes
Transaction #	"1" (unique string)	<TransactionID>
Request Identification	Company Number to 4 digits plus Customer.ERPNumber formatted to 12 digits	<RequestID>	2 position company number and 10 position customer number
Order Number	Blank	<OrderNumber>	There will be no order number, so a null is sent.
<Items>
Warehouse	Warehouse.Name	<WarehouseID>	The warehouse for which pricing is calculated. If blank, will return all warehouses.
Qty Ordered	"1"	<OrderQuantity>	We use this to get the default price for a qty of 1.
Unit of Measure	Product.UnitofMeasure	<UnitofMeasure>
		<NonStockFlag>
Product #	Product.ERPNumber	<ItemNumber>
Ship To	Customer.ERPSequence	<ShiptoNumber>
		<DftWhyOnly>
		<CalculatePrices>	*see notes above about availability

Pricing tables

Customer

The Customer refresh will be pulling both Customer (cusms) and Ship-To (addr) information over to B2B into a common customer table.

Deletion Strategy: B2B retrieves all customer records and physically sets the IsActive flag directly from the data, so the Ignore delete action will be used. B2B must use Ignore, since the data will be retrieved in two separate sweeps.

Field mapping: Customer (bill-to)

Field Name	ERP Source (cusms)	ISC Destination (Customer)	Notes
Company	cmcono		Lookup to Selecting records based on defined company number in settings
Customer #	cmcsno	CustomerNumber ERPNumber
Ship-To #		CustomerSequence	Static Value = Blank
Customer Name	cmcsnm	Company
Contact Name	cmpcnt	Attention	Map only if exposed
Address Line 1-3	cmcad1..cmcad3	Address1..3
City	cmcity	City
State	cmstat	StateId	Lookup being used - must exist in ISC
Country	cmctid	CountryId	Must match country abbreviation to be valid, uses lookup.
Postal Code	cmzip4	PostalCode
Customer Type	cmcscl	CustomerType	Optional field
Territory	cmtrno	Territory	Not a standard field
Phone #	cmpupn	Phone
Fax	Cmfxpn	Fax	Skipping - not a standard field
Terms Code	Cmpacd	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
Warehouse	cmdfwh	DefaultWarehouseId	Must match a valid warehouse.
Outside Sales Rep	Cmrp#1	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.
Default Ship Via	Cmcad	ShipCode	Should match a valid Carrier/Service code by the ERP ShipCode field not enforced but required to map properly when setting the default carrier/service in the checkout screen
Alternate Pricing Customer	Cmcrid		Optional - represents the corporate group ID
Credit Limit	cmcrlm	CreditLimit	Not enforced as standard feature. Note that open order amount + A/R Balance > credit limit puts a customer on credit hold
PO Required	Cmporq		Future Implementation

Field mapping: Customer (ship-to)

Field Name	ERP Source (addr)	ISC Destination (Customer)	Notes
Company	Sacono		Lookup to Select records based on defined company number in settings
Customer #	Sacsno	CustomerNumber ERPNumber
Ship-To #	Sashp#	CustomerSequence	Cannot be blank - skipping blanks.
Customer Name	Sashm	Company
Attention	Sascnt	Attention
Address Line 1-3	Sasad1..sasad3	Address1..3
City	sascty	City
State	sashst	StateId	Lookup must match a valid state for the country supplied
Country	sactid	CountryId	Lookup must match a valid country. The standard code will default US as the country for any entries without a country supplied this can be changed in the query to match the correct base country
Postal Code	saszip	PostalCode
Territory	Sashtr	Territory	If exposed from dictionary
Phone	sasphn	Phone
Fax	safaxn	Fax
Tax Code 1		TaxCode1	Static Value = Blank; Relying on calls to API to determine the tax amount in cart.
Tax Code 2		TaxCode2
Warehouse	sadfwh	WarehouseID	Must Match Valid Warhouse
Outside Sales Rep	sarp#1	PrimarySalespersonId	Must match valid sales rep
Default Ship Via	Sacacd	ShipCode	Should match a valid Carrier/Service code by the ShipCode field not enforced but required to map properly when setting the default carrier/service in the checkout screen. If none provided, will use the BillTo value
Active	sasusp	IsActive	if any value, inactive customer

Customer products

Customer-specific product data will be pulled into Commerce via a direct call to the A+ database. While A+ supports ship-to level customer-specific products, Commerce integration will only pull the bill-to level records.

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

Field mapping: Customer product

Field Name	ERP Source	ISC Destination (CustomerProduct)	Notes
Company	cicono		Lookup to Select records based on defined company number in settings
ERP Part #	ciitno	ProductId	Lookup to Product table
Customer #	Cicsno	CustomerId	Lookup to customer table. Only valid for bill-to customers.
Customer Part #	cicsit	Name
Unit of Measure	Cdfum.duunms	UnitOfMeasure	This will become the default unit of measure for the customer if specified Retieve this record using: ducono = cicono ducsno = cicsno duitno = ciitno

Customer 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.

Inventory tables

Products

The Product Refresh integration process retrieves item/product information directly from the A+ ERP database. The field itmst.impwbu will be used to determine which products are active.

Deletion Strategy: Since these tables can be large, we do want to only retrieve records that are important to pull into ISC. This job may be run using Delta Datasets to minimize transaction volume coming over. We will use the Set Field option to set the DeactivateOn field automatically based on using a snapshot strategy for data retrieval.

Field mapping: Product refresh product

Field Name	ERP Source (itmst)	ISC Destination (Product)	Notes
Company	Imcono		Selecting records based on defined company number in settings company 0 = global entries
Active/Deactivate	imsusp	ActivateOn DeactivateOn	Only pull records where this field is not S (only pull active products) and Also do not pull products if impwbu = Y
ERP Item #	imitno	Name ERPNumber	This is the primary natural key to the table
Item Title & Item Description	Imitd1 + + imitd2	ShortDescription ERPDescription	Short description will not be overwritten it is expected to be maintained in the application or from a PIM
URL Segment	Imitd1 + + imitd2 + + imitno	URLSegment	Appending the item number to ensure uniqueness
Active Flag	impwbu	DeactivateOn	This will be used for filtering the products selected.
Discontinued Flag	imdsco	IsDiscontinued	Y/N
Default Hide/Show	imrscd	DefaultVisibility	Join to ORCTL: Cccono = 0 Ccapid = OE Ccctlk = * + imrscd Ccdata(31,1) = P (default Show) or A (default Hide)
Manufacturer's Part #	immfno	ManufacturerItem	vendprod from default warehouse
Unit of Measure	imunm1	UnitOfMeasure	This is the stocking unit of measure to properly handle conversions
Rounding Rule	imaum.auaufc imstus	roundingRule, multipleSaleQty	For the primary U/M only if it exists A = Round Up R = Round Y =-No Rounding If imstus <> 0 then set rule to Multiple and set MultipleSaleQty
Multiple Sale Qty	Imstus	multipleSaleQty	Items must be sold in multiples of this value if > 1 then set Rounding Rule = Multiple Only
Unit of Measure Description	see notes		This is rather difficult to retrieve and can be added if needed. Retrieve the record as a subselect to orctl where cccono = company, ccapid = IA , ccctlk = @UOM followed by imunm1. The description is returned in ccdata(1,30)
Allow Backorder	imbocd		Not Used
Catch Weight	imctwt		Y/N, Not Used
Unit Weight	imuwt1	ShippingWeight
UNSPSC	imcmod	UNSPSC	Not mapped: Used for punchout and needs to be enabled in the Application Dictionary
UPC Code	itupc.upbrit	UPCCode	Not mapped: Used for punchout and needs to be enabled in Application dictionary Match to itupc on upitno and upunms (u/m)
Replacement Product	imxrf.mrrpit	ReplacementProductId	Lookup to Product by checking for an item in imxrf where mrpocd = R and mrorit = current product and mrrpit = replacement product
Vendor	imvnno	VendorId	Lookup to Vendor table assuming vendors are set up in ISC
Unit cost	see notes		Not part of the standard connector. Cost options would come from ITBAL and include: IBAVCS average cost IBUSCS user cost UBSTCS standard cost IBLCST last cost
Base Unit Price			not mapped - used for pricing, but default connecotr uses real-time pricing
Tax Class	imitxc	TaxCode1
MSDS Available	Immsdt	HasMsds	Not being mapped not a standard field but available if needed
Special Order Flag	itbal.idbrsh	IsSpecialOrder	Not being mapped not a standard field but available if needed; Mark as special order if D (drop ship) or S cross-dock) would need to use default warehouse to get itbal record if needed
Extended Description	iaeic.incmtx	n/a	NOT STANDARD if long descriptions are kept in A+. Must retrieve and concatenate records using: Inwhid=blank Initno = imitno Inigid = blank Incono = 0 Incsno = 0 Incgid = blank Inedcc/ineddt (end century/date) >= today Instcc/instdt <= today Incmsq = comment sequence

Product cross-sell

ISC retrieves Product Cross-Sells via a direct call to the A+ database. This refresh can be modified or copied to use specific record types or relationships needed for a given implementation.

Deletion Strategy:

We will use the Ignore option users may create manual entries for different types of relationships that are not managed in A+.

The data will be coming from table imxrf with A records mapped to Accessories relationship and C records mapped to Cross-Sells relationship.

Field mapping: Product cross-sells

Field Name	ERP Source (imxrf)	ISC Destination (ProductRelatedProduct)	Notes
Record Filter			Only pull records where imxrf.mrrpcd = A or C A=Accessories C=CrossSells
Primary Item	inv_mast.inv_mast_UID	ProductID	This is joined to ony match records of the inv_mast_uid field in inv_sub.
Related Item	inv_sub.item_id	RelatedProductID	This is joined to ony match records of the sub_inv_mast_uid field in inv_sub in addition to the above join.

Product restriction group

There is the concept of restriction groups within A+ and the following tables can be used to refresh them. Note that we are not supporting the opt-in/opt-out flag in cusms.cmckrs which has a Y/N/S flag indicating if product restrictions are applied or not.

Restriction groups in A+ work as follows:

• The list of groups is in ORCTL and defines the default behavior for the group (that is hide/show)
• The table OECAU defines the relationships
• We will need to create a restriction group with the customer # and optional Ship to # for distinct product restrictions which will be by customer
• If there is an entry in OECAU for a customer and a carscd (restriction code) field, then they override the behavior of the entire group. These customers will be added to the Restriction Group which will have an opposite behavior of the product
• If there is an entry in OECAU for a customer and a caitno field (product), then they override the behavior for just that product. These customers will be added to a customer-specific restriction group with just the products specified and the opposite behavior of the default.

Note that if a customer is assigned to both a restriction group and has overrides to that restriction group, this will only effectively work to HIDE a product, not show one. For example, if the default behavior is HIDE and they are in a restriction group to SHOW with an exception to HIDE, the individual product will be hidden. The guiding rule in restriction groups is that any override with a hide supersedes overrides to show.

On the other hand, if a product is defaulted to SHOW and the customer is in a restriction group to HIDE the product but there is an individual override of the product to SHOW it again, the HIDE on the restriction group will supersede the SHOW of the individual product so that override will have no effect.

The standard integration will set up the restriction groups and overrides correctly, they just, under certain circumstances, may not have the impact desired.

Deletion Strategy: We will use the Delete option for this data (entire restriction group only). Note that there is no current way to synch/delete records in a child collection which all assigned products to the restriction group are. A process will need to be implemented to remove individual products from customer-specific restriction groups or customers from standard restriction groups.

Field mapping: Restriction groups

Field Name	ERP Source (orctl)	ISC Destination (RestrictionGroup)	Notes
Name	Ccctlk(2,6)	Name	Retrieve records from ORCTL Cccono=0 Ccapid = OE Ccctlk = * + restriction group code
Description	Ccdata(1,30)	Description
Behavior	Ccdata(31,1)	DisplayType	P = Show by default, Hide by exception A = Hide by default, Show by exception

Field mapping: Restriction group products

Field Name	ERP Source ()	ISC Destination (RestrictionGroup ProductAddition)	Notes
Company		Cacono	Filter for records to retrieve
Restriction Group	Carscd	RestrictionGroupId	Used for constructing the group name
Customer #	Cacsno	CustomerId	Used for constructing the group name
Ship To #	Cashp#		Used for constructing the group name and assigning the customer
Product #	Caitno	ProductId	Lookup to Product table

Product alternate unit of measure

ISC 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: ISC uses the Delete option for this data.

Field mapping: Product alternate unit of measure

Field Name	ERP Source (imaum)	ISC Destination (ProductUnitOfMeasure)	Notes
Product #	auitno	Product.ERPNumber	Lookup to Product
Unit of Measure	Auunms	UnitOfMeasure
Conversion Factor	Auaumf	QtyPerBaseUnitOfMeasure
Description	Auaumd	Description
Conversion Method	Aucum1, Aucum2		If aucum1 = 1 then auaumf is the right conversion factor for #/stocking units (that is base = EA, alt = CS 10 EA/CS) If aucum2 = 1 then must invert represents #/units per stocking unit (that is base = DZ, alt = EA 12 EA/DZ)
Default Ordering UOM	itbal.ibdoum	IsDefault	Set to 1 if this value matches auunms otherwise 0 Get to itbal using auitno + the default warehouse to 2 digits (that is 01 )
Rounding Method	Auaufc	RoundingRule	A = adjust up to next whole (Round Up) R = round normal (Round) Y = no rounding (No Rounding)

Product 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 Name	ERP Source (repms)	ISC Destination (Salesperson)	Notes
Company #	Smcono		Used for filtering records to the current company
Salesperson Number	Smrp#1	SalespersonNumber	Select only records from contact_salesrep where delete_flag = N
Name	smrpnm	Name

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 B2B 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 Name	ERP Source (orctl)	ISC Destination (PaymentMethod)	Notes
Record Type			Pull records from orctl using: Cccono = default company number Ccapid = AR Ccrccd = AT Ccctlk = @TRMS + terms code
Terms Code	Cctlk(6)	Name
Description	Ccdata(1,30)	Description
COD/Cash		IsCreditCard	This terms code requires payment by credit card on the site
Active Indicator		ActivateOn	Not overwritten set on initial setup to current date

Ship codes

The ship codes are refreshed to help support the default ship via codes for customers which is then surfaced in the review and pay page on the site. B2B has a Carrier/Service structure while most ERPs only have the service. To that end, we will pull in the data and duplicate it between the carrier and the actual service. The end users can then either re-arrange the data as needed or remove the integration steps (Carriers/ShipVias).

Deletion Strategy: We will use Ignore and not delete any ship via records automatically during the refresh due to referential integrity concerns.

Field mapping: Ship codes

Field Name	ERP Source (orctl)	ISC Destination (PaymentMethod)	Notes
Record Type			Pull records from orctl using: Cccono = 0 Ccapid = OE Ccrccd = CR Ccctlk = CC + ship via code
Ship Via Code	Cctlk(3)	Name	Maps to carrier.name, shipvia.shipcode and shipvia.ERPShipCode
Description	Ccdata(1,30)	Description
COD/Cash		IsCreditCard	This terms code requires payment by credit card on the site must be set manually

Vendors

The vendors table is imported just to support the vendor to product relationship if needed.

Deletion Strategy: We will use delete option on the table to remove unneeded vendors. If a vendor is already assigned to a product, then the deletion will fail.

Field mapping: Vendor

Field Name	ERP Source (vendr)	ISC Destination (Vendor)	Notes
Vendor Number	Pvvnno	vendorNumber	Only include vendors that are associated with products
Name	Pvvnm	Name
Address 1..2	Pvvad1,pvvad2	Address1, Address2
City	Pvvcty	City
State	Pvvnst	State
Postal Code	Pvvzip	PostalCode
Country	Pvctcd	Country	Lookup to country table
Phone	Pvphno		Phone
Contact Name	Pvvcnt	contactName