|
Ordinal
|
Data
Description
|
Data
Type
|
Data
Length
|
Required
|
Check
Constraints
|
Comments
|
|
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
|