Application: Field Sales

Data Model for Application "SLA.Field Sales"

Overview

The document contains information about the entities(tables) used by the Dynamics Mobile Field Sales mobile application. This information can be used by the ERP integrators to properly develop and construct the Synchronization Packets in both directions.

The entities are separated in 2 sections:

• Pull entities - entities sent by the ERP to the mobile device ( e.g. master data )

• Push entities - entities sent by the mobile app to the ERP - e.g. they must be received and processed by the ERP

Entity Mappings with Microsoft Business Central

Push entities (must be sent by the ERP)

CompanyInfo (required)

Supposed to contain only 1 record. Contains information about the current company. Used mostly for displaying and printing of company related information.

Sync Name	Type	Required	Size	Help
DMS_ROWID	String			guid, may be omitted
Code	String	Yes	20	unique company code
Name	String	Yes	100	name of the company
Address	String		100	address of the company
PhoneNo	String		100	phone number of the company
Fax	String		100	fac number of the company
Country	String		100	country of the company
City	String		100	city of the company
CountryRegionId	String		30	region of the company
County	String		30	county of the company
PrimaryFax	String		30	primary fax of the company
PrimaryPhone	String		30	primary phone number of the company
VATNum	String		30	VAT number of the company

Customer (required)

Contains details for the customers. Used in almost any customer related operation.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
No	String	Yes		customer unique number
Name	String	Yes	100	customer name
SearchName	String		200	additional name used for search
Address	String		200	the primary address
City	String		30	the primary city
Country	String		30	the primary country
Email	String		70	customer email
HomePage	String		200	customer primary website
PhoneNo	String		30	customer primary phone number
LocationCode	String		30	customer location code
CreditLimitLCY	Decimal	Yes		credit limit in primary currency
BalanceLCY	Decimal	Yes		balance in primary currency
OverdueAmount	Decimal	Yes		overdue amount in primary currency
Blocked	Boolean	Yes		indicates if the customer is blocked
Longitude	Decimal			primary address longitude
Latitude	Decimal			primary address latitude
CustomerPriceGroup	String			group used for unit price calculation
CustomerDiscGroup	String			group used for line discount calculation
OrgNumber	String			government provided organization number
PaymentMethodCode	String			default payment method
PaymTermId	String			default payment terms
VATBusPostingGroup	String			group used for VAT/Tax calculation
SupplementaryItem	String			group used for bonus product calculation
InvoiceAccount	String			(another) customer number used for invoicing

DeviceSetup (required)

Supposed to contain 1 record only. Stores the details of the current user and device

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
Code	String	Yes	50	unique primary person code
SalespersonCode	String	Yes	50	primary person code
SalespersonName	String		50	primary person name for displaying and printing purposes
SalespersonPhone	String		50	primary person phone number for displaying and printing purposes
SupportPersonName	String		50	support person phone number for displaying and printing purposes
InventSiteId	String		30	inventory site identifier
InventWarehouseId	String		30	inventory warehouse identifier
InventLocationId	String		30	inventory location identifier ( a.k.a. BIN )
LoadSiteId	String		30	site identifier used for inventory load
LoadWarehouseId	String		30	warehouse identifier used for inventory load
LoadLocationId	String		30	location (BIN ) identifier used for inventory load
UnloadSiteId	String		30	site identifier used for inventory unload
UnloadWarehouseId	String		30	warehouse identifier used for inventory unload
UnloadLocationId	String		30	location (BIN) identifier used for inventory unload
DamageSiteId	String		30	site identifier used for damaged inventory
DamageWarehouseId	String		30	warehouse identifier used for damaged inventory
DamageLocationId	String		30	location identifier used for damaged inventory
VehicleNo	String		30	Vehicle license plate

Inventory (optional)

Contains information about the product inventory used for Sales (invoicing), and New Delivery.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
ItemId	String		30	product unique code
SumOfAvailPhysical	Decimal			inventory quantity
InventSiteId	String		50	inventory site id
InventWarehouseId	String		50	inventory warehouse id
InventLocationId	String		50	inventory location id (bin)
InventBatchId	String		30	inventory batch id ( lot/expiration)
InventLicensePlate	String		30	inventory license plate
InventSerial	String		50	inventory serial number

Item (required)

Contains definitions for every product used across the application.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
No	String	Yes	20	product unique identifier
Description	String	Yes	100	product name
BaseUnitId	String		20	inventory unit of measure code
SalesUnitId	String		20	sales unit of measure
VATProdPostingGroup	String		20	group used for VAT/Tax calculation
SuppItemGroupId	String		20	group used for bonus item calculation
UnitPrice	Decimal			default unit price
ItemCategoryCode	String		30	product category
ProductGroupCode	String		30	product group

ItemCrossReference (optional)

Contains information about the barcodes and their mappings to specific products.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
ItemNo	String	Yes	50	unique product identifier
UnitOfMeasureCode	String	Yes	50	unit of measure
CrossReferenceNo	String(unique)	Yes	50	unique barcode

CustLedgerEntry (optional)

Contains the history of all customer related transactions - e.g. old invoices

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
RecId	String		30
SalesId	String		30
Invoice	String		30
AccountNum	String		20
DocumentNo	String		20
TransDate	DateTime
DueDate	DateTime
OriginalAmnt	Decimal
OpenAmountCur	Decimal
TotalAmount	Decimal

PaymentMethod (optional)

Contains the list of the available payment methods used across the application

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
Code	String	Yes	30	unique payment method code
PaymentType	Int32	Yes
Description	String	Yes	50	payment method display name

ReturnReason (optional)

Contains list of reasons used during New Return to provide the reason for the goods returning

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
Code	String	Yes	30	unique identifier
Label	String	Yes	50	display name

SalesLineDiscount (optional)

Contains the rules for sales discounts applied for each Sale ( or order ) line.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
Code	String		20	unique code
SalesCode	String		20	customer number or customer discount group or null
StartingDate	DateTime			the discount is valid after this date
LineDiscountPercent	Decimal			the given discount in percent
SalesType	Int32
minQuantity	Decimal			the discount is valid if bought quantity is more
EndingDate	DateTime			the discount is valid before this date
UnitofMeasureCode	String		20	the discount is valid only if the sale is in this unit of measure
maxQuantity	Decimal			the discount is valid if bought quantity is less
LineDiscountAmount	Decimal			the given discount in amount

SalesPrice (optional)

Contains the rules for applying unit prices for each line during sale or order creation

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
SRT	Int32	Yes
SRC	String		20
UP	Decimal	Yes		the given unit price in primary currency
SD	DateTime	Yes		the price is valid after this date
ITRC	String	Yes	30
MINQ	Decimal			the price is valid if quantity bought is more
MAXQ	Decimal			the price is valid if quantity bought is less
ED	DateTime			the price is valid before this date
inventLocationId	String		50
UOC	String	Yes	20

SkipReason (optional)

Cotnains list of reasons used by the user to provide reasons why specific customer can not be visited and during Route Close.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
Description	String	Yes	100	display name
Code	String	Yes	20	unique identifier

SuppItemTable (optional)

Contains information about bonus items - e.g. Buy One, Get Four type of product bonuses given during sale or order taking.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
AccountCode	String	Yes	20
AccountRelation	String	Yes	20
FromDate	DateTime	Yes		the bonus promotion is valid from this date
ToDate	DateTime	Yes		the bonus promotion is valid before this date
ItemCode	String	Yes	50	bought product
ItemRelation	String	Yes	20
MaximumQty	Decimal	Yes		bonus is valid if quantity bought is less or equal
MinimumQty	Decimal	Yes		bonus is valid if quantity bought is more or equal
SuppLimitType	Int32	Yes
MultipleQty	Decimal	Yes
SuppDiscPercent	Decimal	Yes		line discount percent applied to the bonus product
SuppItemOnlyOnce	Int32	Yes
SuppItemFree	Int32	Yes		defines if the bonus item is free or paid
SuppItemId	String	Yes	20	the product identifier of the bonus product
SuppItemOptional	Int32	Yes		0 means that the bonus is optional, 1 means the bonus is required
SuppItemQty	Decimal	Yes		defines the quantity of the bonus product
SuppTaxItemGroup	String	Yes	20	the VAT/Tax group of the bonus product
UnitId	String	Yes	20	the unit of measure of the bonus product

UOMConversion (optinal )

Contains information about the unit of measure conversions.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
ItemNo	String		50	product unique identifier
Code	String		10	unit of measure code ( unique )
Factor	Decimal			conversion factor
Denominator	Decimal
Numerator	Decimal
FromUnitSymbol	String		20	source unit of measure
ToUnitSymbol	String		20	target unit of measure

VATPostingSetup (optional)

Contains information about the VAT/Taxation applied during sales/order taking

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
TaxBase	Int32	Yes
BPG	String	Yes	30
PPG	String	Yes	30
VAT	Decimal	Yes

Pull entities (must be received by the ERP)

CustomerActivity

Sent by the mobile app to describe important event. USed by Dynamics Mobile Platform to build reports and analytics about the fleet activities. Can be used by the ERP for reporting purposes.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
CustomerNo	String		10	The number of the customer/outlet
CustomerTitle	String		10	The name of the customer/outlet
Comment	String		100
DocumentNo	String			The unique document number related to the event
IsDistanceValid	Boolean
VisitNo	String			The unique identifier of the related visit
OnDate	DateTime			The date when the event happened
InRoute	Int32			If the event was planned in advance
Distance	Decimal			The distance from the customer/outlet coordinates where the event happened
cLong	Decimal			geo-location longitude
Lat	Decimal			geo-location latitude
Action	CustomerActivity_Action_enum (Int32)			The type of the event
Amount	Decimal			the amount(money) related to the event
Duration	Int32			the duration of the visit.

PaymentHeader

Sent by the mobile app ,whenever the user registers a payment. It contains the overall information about the payment.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
No	String	Yes	30
documentType	PaymentHeader_documentType_enum (Int32)
CustomerNo	String		30
CustomerName	String		30
PaymentType	PaymentHeader_paymentType_enum (Int32)
Amount	Decimal
PaidAmount	Decimal
TotalAmount	Decimal
VisitNo	String	Yes	30
Bank	String		30
AccountName	String		30
checkNumber	String		30
CheckDueDate	String		30
Delay	DateTime
CashDiscountAmount	Decimal
CashFeeAmount	Decimal
ChequeDiscountAmount	Decimal
CurrencyCode	String		30
AmountMainCurrency	Decimal
DateCreated	DateTime

PaymentLine

Sent by the mobile app ,whenever the user registers a payment. It may contain 1 or more records where each record describes a payment towards open invoice. This is always combined with one record in PaymentHeader

Sync Name	Type	Required	Size	Label	Help
DMS_ROWID	Binary			Id
dateCreated	DateTime			dateCreated
PaymentId	String			paymentId
DocNo	String		20	docNo
TransId	String		50	transId
Amount	Decimal			amount
OriginalAmount	Decimal			originalAmount
VisitNo	String	Yes		visitNo
CustomerNo	String			customerNo

SalesHeader

Sent by the mobile app when the user submits Sales Order , Sale (invoice) , New Delivery. Contains the information about the document being submitted. Usually there is only one record if this type in the Synchronization packet

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
No	String(PK)	Yes	20
DocumentType	SalesHeader_documentType_enum (Int32)	Yes
DateCreated	DateTime	Yes
amount	Decimal	Yes	100
vatAmount	Decimal		200
totalAmount	Decimal		200
Reason	String		30
customerNo	String		30
CustomerName	String		200
VisitNo	String		20
DiscountAmount	Decimal		200
DiscountPercent	Decimal		200
ImagePath	String		5000
signature	String		5000
fileName	String		200

SalesLine

Sent by the mobile application when the user submits Sales Order ,Sale ( invoice), New Delivery. There might 1 or more records if this type in the Synchronization packet.They are always combined with SalesHeader record in the same Synchronization packet.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
dateCreated	DateTime
No	String		20
LineNo	Int32 (PK)
DocumentNo	String(PK)		20
Name	String	Yes	100
Quantity	Decimal
UnitOfMeasure	String		10
SuppItemGroupId	String		30
UnitPrice	Decimal
Amount	Decimal	Yes
VatAmount	Decimal		200
LineDiscountPercent	Decimal
LineDiscountAmount	Decimal
VatPercent	Decimal
TotalAmount	Decimal
IsSuppItem	Boolean
SalesUnitFactor	Decimal
IsSuppItemFree	Decimal
SuppDiscPercent	Decimal

SalesLineInventory

Sent by the mobile application when the user submits Sale ( invoice), New Delivery. It describes the inventory being consumed from the mobile app inventory because of a sale. There might 1 or more records if this type in the Synchronization packet.They are always combined with SalesHeader and SalesLines records in the same Synchronization packet. Each record in SalesLineInventory corresponds to a record in SalesLines.

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary(PK)
dateCreated	DateTime
No	String
LineNo	Int32
DocumentNo	String
Quantity	Decimal
inventoryWarehouseId	String
inventorySiteId	String
inventLocationId	String
inventoryBatchId	String
inventoryLicensePlate	String
inventSerial	String

TransferHeader

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
No	String		30
VisitNo	String		30
SourceInventSiteId	String		30
SourceInventWarehouseId	String		30
InventLocationIdTo	String		30
TargetInventSiteId	String		30
TargetInventWarehouseId	String		30
TargetInventLocationId	String		30
DocumentType	TransferHeader_documentType_enum (Int32)
DateCreated	DateTime	Yes

TransferLine

Sync Name	Type	Required	Size	Help
DMS_ROWID	Binary
dateCreated	DateTime
No	String
LineNo	Int32
DocumentNo	String
Name	String	Yes	100
Quantity	Decimal
UnitOfMeasure	String