Database Table: ORDERS

Each row in this table represents an Order in a Store.

Column Name Column Type Column Description
ADDRESS_ID BIGINT
NULL
The billing address, if known.
COMMENTS VARCHAR (254)
NULL
Comments from the customer.
CURRENCY CHARACTER (10)
NULL
The Currency for monetary amounts associated with this Order. This is a currency code as per ISO 4217 standards.
DESCRIPTION VARCHAR (254)
NULL
A mnemonic description of the Order, entered by the customer, suitable for display to the customer.
FIELD1 INTEGER
NULL
Customizable.
FIELD2 DECIMAL (20,5)
NULL
Customizable.
FIELD3 VARCHAR (254)
NULL
Customizable.
LASTUPDATE TIMESTAMP
NULL
The time this Order was most recently updated.
LOCKED CHARACTER (1)
NULL
Reserved for IBM internal use.
MEMBER_ID BIGINT
NOT NULL
The customer of the Order.
NOTIFICATIONID BIGINT
NULL
Notification identifier referring to the rows in the NOTIFY table that store notification attributes. These attributes override the defaults for notifications related to this order.
ORDCHNLTYP_ID BIGINT
NULL
Reserved for IBM internal use.
ORDERS_ID BIGINT
NOT NULL
Generated unique key.
ORGENTITY_ID BIGINT
NULL
The immediate parent organization ID of the creator.
ORMORDER CHARACTER (30)
NULL
A merchant assigned order reference number, if any.
PROVIDERORDERNUM INTEGER
NULL
Reserved for IBM internal use.
SEQUENCE DOUBLE
NOT NULL, DEFAULT 0
May be used by a user interface to control the sequence of Orders in a list.
SHIPASCOMPLETE CHARACTER (1)
NOT NULL, DEFAULT 'Y'
Reserved for IBM internal use.
STATUS CHARACTER (1)
NULL
The status of the Order.
STOREENT_ID INTEGER
NOT NULL
The StoreEntity the Order is part of. This is normally a Store unless STATUS is Q, in which case it is normally a StoreGroup.
TIMEPLACED TIMESTAMP
NULL
The time this Order was processed by the OrderProcess command.
TOTALADJUSTMENT DECIMAL (20,5)
NULL, DEFAULT 0
The sum of ORDERITEMS.TOTALADJUSTMENT for the OrderItems in the Order.
TOTALPRODUCT DECIMAL (20,5)
NULL, DEFAULT 0
The sum of ORDERITEMS.TOTALPRODUCT for the OrderItems in the Order.
TOTALSHIPPING DECIMAL (20,5)
NULL, DEFAULT 0
The sum of ORDERITEMS.SHIPCHARGE for the OrderItems in the Order.
TOTALTAX DECIMAL (20,5)
NULL, DEFAULT 0
The sum of ORDERITEMS.TAXAMOUNT for the OrderItems in the Order.
TOTALTAXSHIPPING DECIMAL (20,5)
NULL, DEFAULT 0
The sum of ORDERITEMS.SHIPTAXAMOUNT for the OrderItems in the Order.
Indexes:
Index Name Indexed Column Names Index Type
I0000176 MEMBER_ID, STATUS, STOREENT_ID Non-Unique Index
I173124 TIMEPLACED Non-Unique Index
SQL030416035018020 ORDERS_ID Primary Key
Constraints:
Constraint Name Column Names Foreign Table Name Foreign Column Names Constraint Type
F_513 ORGENTITY_ID ORGENTITY ORGENTITY_ID Cascade
F_514 ORDCHNLTYP_ID ORDCHNLTYP ORDCHNLTYP_ID Cascade
F_515 ADDRESS_ID ADDRESS ADDRESS_ID Cascade
F_516 MEMBER_ID MEMBER MEMBER_ID Cascade
F_517 STOREENT_ID STOREENT STOREENT_ID Cascade

Referenced By:
Constraint Name Referenced Column Name Foreign Table Name Foreign Column Names Constraint Type
F_120 ORDERS_ID BIDORDRREL ORDERS_ID Cascade
F_129 ORDERS_ID BUACCTDET ORDERS_ID Cascade
F_260 ORDERS_ID CPENDORDER ORDERS_ID Cascade
F_397 ORDERS_ID INVOICE ORDERS_ID Cascade
F_423 ORDERS_ID LPOPURAMT ORDERS_ID Cascade
F_477 ORDERS_ID ONLOG ORRFNBR Cascade
F_479 ORDERS_ID ONQUEUE ORRFNBR Cascade
F_483 ORDERS_ID ORCOMMENT ORDERS_ID Cascade
F_485 ORDERS_ID ORCPMAP ORDERS_ID Cascade
F_490 ORDERS_ID ORDADJUST ORDERS_ID Cascade
F_493 ORDERS_ID ORDBTB ORDERS_ID Cascade
F_494 ORDERS_ID ORDCALCD ORDERS_ID Cascade
F_496 ORDERS_ID ORDERITEMS ORDERS_ID Cascade
F_511 ORDERS_ID ORDERMGP ORDERS_ID Cascade
F_512 ORDERS_ID ORDERMSG OMORNBR Cascade
F_519 ORDERS_ID ORDERTMPL ORDERS_ID Cascade
F_532 ORDERS_ID ORDMEEXTN ORDERS_ID Cascade
F_533 ORDERS_ID ORDOPTIONS ORDERS_ID Cascade
F_534 ORDERS_ID ORDPAYINFO ORDERS_ID Cascade
F_542 ORDERS_ID ORDPAYMTHD ORDERS_ID Cascade
F_547 ORDERS_ID ORDRELEASE ORDERS_ID Cascade
F_551 ORDERS_ID ORDTAX ORDERS_ID Cascade
F_579 ORDERS_ID PAYSTATUS SETSORNBR Cascade
F_60 ORDERS_ID ALCHARGE ORDERS_ID Cascade
F_672 ORDERS_ID REFUNDMTHD ORDERS_ID Cascade
F_740 ORDERS_ID SCHORDERS ORDERS_ID Cascade
F_813 ORDERS_ID SUBORDERS ORDERS_ID Cascade
F_863 ORDERS_ID TORCPMAP ORDERS_ID Cascade
F_871 ORDERS_ID TRDDEPAMT ORDERS_ID Cascade
F_877 ORDERS_ID TRDPURAMT ORDERS_ID Cascade
F_880 ORDERS_ID TRDREFAMT ORDERS_ID Cascade
F_932 ORDERS_ID ORDQUOTREL CHILD_ID Cascade
F_933 ORDERS_ID ORDQUOTREL PARENT_ID Cascade
F_939 ORDERS_ID ORDSTAT ORDERS_ID Cascade
F_940 ORDERS_ID ORDISTAT ORDERS_ID Cascade

STATUS column

The order status as follows:

A
payment authorization requires review - payment authorization has encountered an unusual circumstance, such as an address verification warning. The payment authorization should be reviewed and accepted, or the Order canceled, using the Order Management user interface. If the authorization is accepted, then the user interface will change the Order Status to either 'B' or 'C' as appropriate.
B
backordered - an initial authorization has been performed. A re-authorization for the full amount will be done when all backordered items are allocated.
C
complete - payment for the full amount has been authorized.
D
deposited - payment has been captured.
E
CSR edit - a Customer Service Representative is working with the Order.
F
ready for remote fulfillment - the Order is ready to be sent to a remote system for fulfillment. This status is used by the MQAdapter feature and the TransferOrder task command.
G
waiting for remote fulfillment - the Order has been sent to a remote system for fulfillment. This status is used by the MQAdapter feature and the TransferOrder task command.
H
error in remote fulfillment - this status is associated with these conditions:

(1) An order has been submitted for transfer and the distributor responded with a 'failed transfer' confirmation. The failure may be due to an invalid user ID or password. The error code for the transfer is stored in the ORDSTAT table OSCODE column and the error status message can be located in the ORDSTAT table OSCMT column.

(2) An empty quotation order arrived due to quotation failure.

I
submitted - the customer has submitted the Order, but has not yet initiated payment.
L
low inventory - the customer has initiated payment. A previously allocated (or backordered) OrderItem has become unavailable.
M
payment initiated - the customer has initiated payment. Authorization is pending.
N
approval denied - approval has been denied for some OrderItems.
P
pending - the customer can modify this Order.
Q
quick order profile - the Order contains default information for a customer such as shipping address and payment information that can be copied when creating a new pending Order.
R
released - all OrderItems have been released for fulfillment.
S
shipped - all OrderItems have been manifested.
T
temporary - used by the Order Management user interface to temporarily back-up an Order.
W
waiting for approval - not all OrderItems have obtained approval.
X
canceled - the Order has been canceled.
Y
private requisition list - the Order is a private requisition list.
Z
shareable requisition list - the Order is a shareable requisition list.

Other values are possible. Upper case letters are reserved by IBM.