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. This quantity should be the result of (ORDERS.BASETOTALSHIPPING - ORDERS.TOTALSHIPADJUST). It is the adjusted amount acually charged to the customer. |
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 | CHAR(1) | The status of a standard order. |
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. |
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. |
MAJORSTATUS | CHAR(3) | The MAJORSTATUS is the status of an advanced order. |
TYPE | CHAR(3) | For an advanced order, TYPE indicates whether it is a regular order, requisition list order, standing order, or profile order. |
VERSION | INTEGER | The VERSION indicates if the order is an advanced order or a standard 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. |
TOTALSHIPADJUST | DECIMAL (20,5) DEFAULT 0 | The sum of ORDERITEMS.TOTALSHIPADJUST for the order items in the order. This is the total of all adjustments give to this order with respect to shipping. |
BLOCKED | SMALLINT DEFAULT 0 | Indicates if this order has a block placed against it or not. |
BASETOTALSHIPPING | DECIMAL (20,5) DEFAULT 0 | The sum of ORDERITEMS.BASETOTALSHIPPING for the order items in the order. This is the unadjusted total shipping amount for this order. |
Index Name | Indexed Column Names | Index Type |
---|---|---|
SQL050212031244300 | 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 |
I173124 | TIMEPLACED | Non-Unique Index |
Constraint Name | Column Name(s) | Foreign Table Name | Foreign Column Name(s) | Constraint Type |
---|---|---|---|---|
F_515 | ADDRESS_ID | ADDRESS | ADDRESS_ID | Cascade |
F_1176 | BUSCHN_ID | BUSCHN | BUSCHN_ID | Cascade |
F_1138 | EDITOR_ID | MEMBER | MEMBER_ID | Cascade |
F_516 | MEMBER_ID | MEMBER | MEMBER_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 |
Constraint Name | Referenced Column Name | Foreign Table Name | Foreign Column Name(s) | 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 |
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_1190 | ORDERS_ID | ORDSERIAL | 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_1054 | ORDERS_ID | PX_COUPON | ORDERS_ID | Cascade |
F_1051 | ORDERS_ID | PX_PROMOARG | 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
Standard orders 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.
- 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.