Reference > Database > WebSphere Commerce database tables > O


WebSphere Commerce database table: ORDERS

Each row in this table represents an order in a store.

Column Name Column Type Description
ORDERS_ID BIGINT NOT NULL Generated unique key.
ORMORDER CHAR(30) A merchant-assigned order reference number, if any.
ORGENTITY_ID BIGINT The immediate parent organization ID of the creator.
TOTALPRODUCT DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.TOTALPRODUCT for the OrderItems in the Order.
TOTALTAX DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.TAXAMOUNT for the OrderItems in the Order.
TOTALSHIPPING DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.SHIPCHARGE for the OrderItems in the Order.
TOTALTAXSHIPPING DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.SHIPTAXAMOUNT for the OrderItems in the Order.
DESCRIPTION VARCHAR(254) A mnemonic description of the order, entered by the customer, suitable for display to the customer.
STOREENT_ID INTEGER NOT NULL The store entity the order is part of. This is normally a store unless STATUS is Q, in which case it is normally a store group.
CURRENCY CHAR(10) The currency for monetary amounts associated with this order. This is the currency code according to ISO 4217 standards.
LOCKED CHAR(1) Reserved for IBM internal use.
TIMEPLACED TIMESTAMP The time this order was processed by the OrderProcess command.
LASTUPDATE TIMESTAMP The time this order was most recently updated.
SEQUENCE DOUBLE NOT NULL DEFAULT 0 Can be used by a user interface to control the sequence of orders in a list.
STATUS VARCHAR(3) The status of the order. This is similar to order states. For specific values of status and their descriptions, refer to the order states page, but omit states 'Y' and 'Z'.
MEMBER_ID BIGINT NOT NULL The customer that placed the order.
FIELD1 INTEGER Customizable.
ADDRESS_ID BIGINT The billing address, if known.
FIELD2 DECIMAL (20,5) Customizable.
PROVIDERORDERNUM INTEGER Reserved for IBM internal use.
SHIPASCOMPLETE CHAR(1) NOT NULL DEFAULT 'Y' Reserved for IBM internal use.
FIELD3 VARCHAR(254) Customizable.
TOTALADJUSTMENT DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.TOTALADJUSTMENT for the order items in the order. This column also includes all kinds of shipping charge adjustments like discount, coupon, shipping adjustment and surcharge.
ORDCHNLTYP_ID BIGINT Reserved for IBM internal use.
COMMENTS VARCHAR(254) Comments from the customer.
NOTIFICATIONID BIGINT 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.
TYPE CHAR(3) For an order, TYPE indicates whether it is a regular order, requisition list order, standing order, quotation order, or profile order.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
EDITOR_ID BIGINT Stores the ID of the person editing the order.
BUSCHN_ID INTEGER The Business Channel ID of the order.
SOURCEID BIGINT This field indicates the sources from which this order came, if any. This field is relevant for orders that were generated from a previously saved quote, for example. In that situation, the QuoteId would be saved here.
EXPIREDATE TIMESTAMP This entry is only relevant when the row refers to a quote, type "QOT". This field then indicates the expiration date for that quote.
BLOCKED SMALLINT DEFAULT 0 Indicates if this order has a block placed against it or not.
OPSYSTEM_ID INTEGER This field indicates the Order Processing system for the order.
TRANSFERSTATUS SMALLINT Order transferring status: 0, it is in the initial status. 1, it is in the transferring status. 2, it is in the transferred status. null, the same as 0.
BUYERPO_ID BIGINT The Buyer PO Number Id related to an order

Indexes
Index Name Indexed Column Names Index Type
SQL100520030401720 ORDERS_ID Primary Key
I0000176 MEMBER_ID+STATUS+STOREENT_ID Non-Unique Index
I0000652 ADDRESS_ID Non-Unique Index
I0000653 ORGENTITY_ID Non-Unique Index
I0000654 STOREENT_ID Non-Unique Index
I0000892 EDITOR_ID Non-Unique Index
I0000933 SOURCEID Non-Unique Index
I0001267 BUYERPO_ID Non-Unique Index
I173124 TIMEPLACED Non-Unique Index

Constraints
Constraint Name Column Names Foreign Table Name Foreign Column Names Constraint Type
F_515 ADDRESS_ID ADDRESS ADDRESS_ID Cascade
F_1176 BUSCHN_ID BUSCHN BUSCHN_ID Cascade
F_9004 BUYERPO_ID BUYERPO BUYERPO_ID Cascade
F_1138 EDITOR_ID MEMBER MEMBER_ID Cascade
F_516 MEMBER_ID MEMBER MEMBER_ID Cascade
R_3455 OPSYSTEM_ID OPSYSTEM OPSYSTEM_ID Cascade
F_514 ORDCHNLTYP_ID ORDCHNLTYP ORDCHNLTYP_ID Cascade
F_513 ORGENTITY_ID ORGENTITY ORGENTITY_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_60 ORDERS_ID ALCHARGE ORDERS_ID Cascade
F_120 ORDERS_ID BIDORDRREL ORDERS_ID Cascade
F_129 ORDERS_ID BUACCTDET ORDERS_ID Cascade
F_1224 ORDERS_ID CALADJUST ORDERS_ID Cascade
F_260 ORDERS_ID CPENDORDER ORDERS_ID Cascade
F_1118 EX_ORD_ID EXCHORDERS ORDERS_ID Cascade
F_397 ORDERS_ID INVOICE ORDERS_ID Cascade
F_423 ORDERS_ID LPOPURAMT ORDERS_ID Cascade
F_477 ORRFNBR ONLOG ORDERS_ID Cascade
F_479 ORRFNBR ONQUEUE ORDERS_ID 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_1219 ORDERS_ID ORDERBLK ORDERS_ID Cascade
R_3458 ORDERS_ID ORDERHIST ORDERS_ID Cascade
F_496 ORDERS_ID ORDERITEMS ORDERS_ID Cascade
F_511 ORDERS_ID ORDERMGP ORDERS_ID Cascade
F_512 OMORNBR ORDERMSG ORDERS_ID Cascade
F_519 ORDERS_ID ORDERTMPL ORDERS_ID Cascade
F_940 ORDERS_ID ORDISTAT 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_1076 ORDERS_ID ORDPROMOCD ORDERS_ID Cascade
F_932 CHILD_ID ORDQUOTREL ORDERS_ID Cascade
F_933 PARENT_ID ORDQUOTREL ORDERS_ID Cascade
F_547 ORDERS_ID ORDRELEASE ORDERS_ID Cascade
F_939 ORDERS_ID ORDSTAT ORDERS_ID Cascade
F_551 ORDERS_ID ORDTAX ORDERS_ID Cascade
F_579 SETSORNBR PAYSTATUS ORDERS_ID Cascade
F_3610 ORDERS_ID PX_CDUSAGE ORDERS_ID Cascade
F_1054 ORDERS_ID PX_COUPON ORDERS_ID Cascade
F_1051 ORDERS_ID PX_PROMOARG ORDERS_ID Cascade
F_3594 ORDERS_ID PX_RWDOPTION ORDERS_ID Cascade
F_1057 ORDERS_ID PX_USAGE ORDERS_ID Cascade
F_672 ORDERS_ID REFUNDMTHD ORDERS_ID Cascade
F_740 ORDERS_ID SCHORDERS ORDERS_ID Cascade
F_1145 ORDERS_ID SHIPINFO 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


STATUS column


Order states:

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.

J

Junk or Deleted Order

L

Low inventory: The customer has initiated payment. A previously allocated (or backordered) order item has become unavailable.

M

Payment initiated: The customer has initiated payment. Authorization is pending.

N

Approval denied: Approval has been denied for some order items.

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 order items have been released for fulfillment.

S

Shipped: All order items have been manifested.

T

Temporary: Used by the Order Management user interface to temporarily back up an order.

W

Waiting for approval: Not all order items 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. Uppercase letters are reserved by IBM.


Related reference

_vcc_freeselect_bottom_


+

Search Tips   |   Advanced Search