KS_ORDERS

1

SO Number

string

30

Y

A lookup of order_header.order_no is found based on value

2

Kommerce Server ID

integer

4

This is only necessary when an order was originated in KS, imported into ERP system. The ERP system is required to track the SQL PK value and send it back to KS in this export along the actual ERP order number reference above.

3

Customer Number

string

30

Y

A lookup of business_entity.business_no is found based on value and assumes the business_entity.business_
type = 'C'

4

Order Date

datetime

5

Contact No

string

30

Lookup contact.contact_no based on value=
contact.contact_xref_id

6

Purchase Order

string

50

7

Status

string

1

Y

'O' = Open
'C' = Closed
'X' =Cancelled

8

SO Item Number

integer

4

Y

9

Part Number

string

25

Y

If the item_id does not exist in item_master table, the order_item is still updated but the value is stored in order_item.item_xref_
id column.

10

SO Item Desc

string

100

11

Unit Price

money

12

Schedule Date

datetime

Y

This is used as part of the PK so it must be unique to a sales order line

13

Schedule Qty

integer

Y

This should be the entire scheduled quantity for this line. For 'C' type lines this value should be negative.

14

Ship To Number

** This is no longer supported as the shipping address can be unique to an order and therefore render any reporting by shipping location useless. Send blank for this column as a place holder.

15

Ship To Name

string

100

16

Address Line 1

string

100

17

Address Line 2

string

100

18

Address Line 3

string

100

19

City

string

50

20

State

string

50

21

Zip

string

20

22

Country

string

100

23

Ship Via

string

50

There is a ship_via table in the database but no FK relationship, however it may make sense to send up the related code (char(15)) anyway instead of a text string.

24

Freight Amount

money

25

Sales Tax
Amount

money

26

Tax Code

string

15

27

SalesRep

string

10

28

Order Type

string

1

Y

S' = Sales
order
'C' = 'Credit
(reverse
Sales Order)

29

Line Type

string

1

Y

'S' = shipped line
'R' = received line

Because KS stores Sales Orders, Credits (reverse sales orders), and RMAs in one table set, type fields are used to determine inbound and outbound transactions.

30

Currency
Code

string

10

If left blank, the system assumes the order is in the website functional currency (web setting)

31

Trans
Freight Amount

money

If the order was transacted in a foreign currency, this amount reflects the foreign currency freight amount otherwise it is the same as the Freight Amount

32

Trans Tax
Amount

money

If the order was transacted in a foreign currency, this amount reflects the foreign currency tax amount otherwise it is the same as the Tax Amount

33

Trans Unit Price

money

If the order was transacted in foreign currency, this amount reflects the foreign unit price for the item, otherwise it is the same as the unit price

34

Line Status

string

1

'O' = Open
'C' =Complete
'X' =Cancelled

35

Requested Date

datetime

Customer's request delivery date

36

Service
Log

string

30

A lookup of project_task.task_no is done based on value = project_task.task_xref_id. This is used by the webServiceCenter application.

37

KS
Service ID

integer

4

Used if the service project was originated in KS and the Service Log above may not be known by KS yet. This field is only used for a lookup (no update).

38

RMA Type

string

30

THIS IS NOT SUPPORTED FOR DEBIT/CREDIT SALES ORDERS

39

Failure
Date

datetime

THIS IS NOT SUPPORTED FOR DEBIT/CREDIT SALES ORDERS

40

Failure
Code

string

30

THIS IS NOT SUPPORTED FOR DEBIT/CREDIT SALES ORDERS

41

Price
Code

string

1

'E' Exception Price
'U' Unit Price

This determines whether the normal pricing occurred (U) or if was overridden with exception pricing (E)

42

Attention

string

50

43

Attention Phone

string

25

44

Hold Code

string

1

Indicates order is on hold. Currently no functionality tied to this field in KS

45

Freight Bill Type Code

string

2

PA = Prepay and Add to Invoice
FC = Freight Collect
TP = Third
Party

Indicates how the freight will be/was paid. The normal method is the host company prepays for the freight and then adds the cost to the invoice to the customer (PA). In some cases, Freight Collect (FC) or Third party billing is used in which case a freight account number is required. KS will use historic information to aid in the checkout process by default freight billing option and previously used freight account numbers.

46

Freight Bill Account No

string

50

Only applicable for Freight Collect or Third party freight billing. Used for default for the next order on during checkout.

47

Region

string

30

Xref Lookup to find the right row. This is primarily used for webSalesForce application

48

Special Charge
Code

integer

Y

Lookup based on special_charge.charge_xref_id

49

Special Charge
Order Line

integer

If this field is left blank, then the special charge is assumed to apply to the whole order.

50

Special Charge Amount

money

Y

51

Price UOM

string

10

Y

The price unit of measure for this order item.

52

Email
Address

string

75

53

Customer
Job
Reference

string

75

This is a job reference specified by the customer