Each row of this table represents an order item in an order.
Column Name | Column Type | Description |
---|---|---|
ORDERITEMS_ID | BIGINT NOT NULL | Generated unique key. |
STOREENT_ID | INTEGER NOT NULL | The store entity the order (this order item is part of) is part of. This is normally a store unless STATUS is Q, in which case it is normally a store group. |
ORDERS_ID | BIGINT NOT NULL | The order of which this order item is part. |
TERMCOND_ID | BIGINT | The TermAndCondition, if known, that determined the price for this order item. |
TRADING_ID | BIGINT | The TradingAgreement, if known, that determines the TermAndCondition objects (including how the price is determined) that apply to this order item. |
ITEMSPC_ID | BIGINT | The specified item to be allocated from available inventory and shipped to the customer. |
CATENTRY_ID | BIGINT | The catalog entry, if any, of the product being purchased. |
PARTNUM | VARCHAR(64) | The part number of the catalog entry(CATENTRY.PARTNUMBER) for the product. |
SHIPMODE_ID | INTEGER | The shipping mode, if still known. |
FFMCENTER_ID | INTEGER | The fulfillment center, if known, from which the product will ship. |
MEMBER_ID | BIGINT NOT NULL | The customer of the order item (which is the same as the customer of the order). |
ADDRESS_ID | BIGINT | The shipping address, if any, for this order item. |
ALLOCADDRESS_ID | BIGINT | The shipping address used when inventory for this order item was allocated or backordered. |
PRICE | DECIMAL (20,5) | The price for the nominal quantity of the product (CATENTSHIP.NOMINALQUANTITY). |
LINEITEMTYPE | CHAR(4) | If specified, indicates the type of the order item. ALT = the order item represents an alternative item (might not be exactly what the customer requested). |
STATUS | CHAR(1) NOT NULL | All order items for an order contain a copy of the order status. See the description of the STATUS column for the ORDERS table. |
OUTPUTQ_ID | BIGINT | Reserved for IBM internal use. |
INVENTORYSTATUS | CHAR(4) NOT NULL DEFAULT 'NALC' | The allocation status of inventory for this order item: NALC = inventory is neither allocated nor backordered. BO = inventory is backordered. ALLC = inventory is allocated. FUL = inventory has been released for fulfillment. |
LASTCREATE | TIMESTAMP | The time this order item was created. |
LASTUPDATE | TIMESTAMP | The most recent time this order item was updated. Changing inventory allocation related information does not cause this timestamp to be updated (refer to the LASTALLOCUPDATE column). |
FULFILLMENTSTATUS | CHAR(4) NOT NULL DEFAULT 'INT' | The fulfillment status of the order item: INT = not yet released for fulfillment. OUT = released for fulfillment. SHIP = shipment confirmed. |
LASTALLOCUPDATE | TIMESTAMP | The most recent time inventory was checked (for unallocated order items), allocated, or backordered, for this order item. |
OFFER_ID | BIGINT | The offer, if any, and if it still exists, from which PRICE was obtained. |
TIMERELEASED | TIMESTAMP | The time this order item was released for fulfillment. |
TIMESHIPPED | TIMESTAMP | The time this order item was manifested for shipment. |
CURRENCY | CHAR(10) | The currency of order item monetary amounts other than BASEPRICE. This is the same as the currency of the order, ORDERS.CURRENCY. This is a currency code according to ISO 4217 standards. |
COMMENTS | VARCHAR(254) | Comments from the customer, such as a greeting for a gift. |
TOTALPRODUCT | DECIMAL (20,5) DEFAULT 0 | PRICE times QUANTITY. |
QUANTITY | DOUBLE NOT NULL | The result of multiplying QUANTITY by CATENTSHIP.NOMINALQUANTITY must be a multiple of CATENTSHIP.QUANTITYMULTIPLE, and represents the actual quantity being purchased, in the unit of measurement specified by CATENTSHIP.QUANTITYMEASURE. |
TAXAMOUNT | DECIMAL (20,5) | The total sales taxes associated with this order item, in the currency specified by CURRENCY. |
TOTALADJUSTMENT | DECIMAL (20,5) DEFAULT 0 | The total of the monetary amounts of the order item adjustments for this order item, in the currency specified by CURRENCY. |
SHIPTAXAMOUNT | DECIMAL (20,5) | The total shipping taxes associated with this order item, in the currency specified by CURRENCY. |
ESTAVAILTIME | TIMESTAMP | An estimate of when sufficient inventory will be available to fulfill this order item. This estimate does not include the shipping offset. |
FIELD1 | INTEGER | Customizable. |
DESCRIPTION | VARCHAR(254) | A mnemonic description of the order iItem, suitable for display to the customer. This field is usually NULL when CATENTRY_ID is not NULL, since in that case the CatalogEntry description can be displayed. |
FIELD2 | VARCHAR(254) | Customizable. |
ALLOCATIONGROUP | BIGINT | Reserved for IBM internal use. |
SHIPCHARGE | DECIMAL (20,5) | The shipping charge associated with this order item, in the currency specified by CURRENCY. This quantity should be the result of (ORDERITEMS.BASETOTALSHIPPING - ORDERITEMS.TOTALSHIPADJUST). It is the adjusted amount acually charged to the customer for this orderitem. |
BASEPRICE | DECIMAL (20,5) | If PRICE was converted from a currency different from the order item currency, BASEPRICE is the price that was converted to determine the PRICE. |
BASECURRENCY | CHAR(3) | The currency of BASEPRICE. |
TRACKNUMBER | VARCHAR(64) | Reserved for IBM internal use. |
TRACKDATE | TIMESTAMP | Reserved for IBM internal use. |
PREPAREFLAGS | INTEGER NOT NULL DEFAULT 0 | Contains the following bit flags indicating special processing to be performed by the OrderPrepare command as follows: 1 = generated - the order item was generated during a previous execution of the OrderPrepare command. The next time the OrderPrepare command is run, it first removes all generated order items, so they can be re-generated if and as applicable. 2 = priceOverride - the price of the order item has been manually entered, and will not be changed by shopper commands. 4 = fulfillmentCenterOverride - the fulfillment center has been manually specified, and will not be changed by shopper commands. 8 = directCalculationCodeAttachment - calculation codes can be directly attached to the OrderItem. The default CalculationCodeCombineMethod will not look for direct attachments unless this flag is true. 16 = Reserved for IBM Internal Use. 32 = quotation - the order item was obtained from a quotation. The price will not be automatically refreshed by shopper commands. 64 = notConfigured - Price lookup and inventory allocation for this order item should not be done using the component items found in the OICOMPLIST table for this order item. For backward compatibility, this flag does not need to be set for order items whose configurationId column value is null. 128 = autoAdd - order item was added |
CORRELATIONGROUP | BIGINT | Normally this is the same as ORDERITEMS_ID, except: 1. When an order item is split by the AllocateInventory task command, the newly created OrderItem inherits the CORRELATIONGROUP value from the original order item. 2. when the PREPAREFLAGS column indicates "quotation", the OrderItem inherits the CORRELATIONGROUP value from the corresponding OrderItem in the parent order. |
PROMISEDAVAILTIME | TIMESTAMP | When an order is placed (using the OrderProcess command), this would be set to EstAvailTime. After that it would normally not be updated, although a CSR could manually update this to reflect a verbal commitment made to the customer. |
SHIPPINGOFFSET | INTEGER NOT NULL DEFAULT 0 | An estimate of how many seconds it will take to ship this item once the order is placed and inventory has been allocated. |
NEEDEDQUANTITY | INTEGER NOT NULL DEFAULT 0 | Quantity needed for fulfillment. If CATENTRY is not NULL, this is QUANTITY times CATENTSHIP.NOMINALQUANTITY, converted from CATENTSHIP.QUANTITYMEASURE to BASEITEM.QUANTITYMEASURE, divided by BASEITEM.QUANTITYMULTIPLE and rounded to the nearest integer. |
ALLOCQUANTITY | INTEGER NOT NULL DEFAULT 0 | Quantity allocated or backordered for this order item. The quantity in BASEITEM.QUANTITYMEASURE units can be calculated by multiplying this value by BASEITEM.QUANTITYMULTIPLE, for the base item of the specified item indicated by ITEMSPC_ID. |
ALLOCFFMC_ID | INTEGER | The fulfillment center from which inventory for this order item is allocated or backordered. |
ORDRELEASENUM | INTEGER | The associated order release, if any. |
CONFIGURATIONID | VARCHAR(128) | The identifier that is provided by an external product configurator. This identifier represents a list of order item components that are stored in the OICOMPLIST table. |
SUPPLIERDATA | VARCHAR(254) | Opaque to WebSphere Commerce. This attribute can be returned with a quotation, and sent when an order is placed on an external system. For example, it could contain a supplier distribution center ID. |
SUPPLIERPARTNUMBER | VARCHAR(254) | The supplier part number, if known. Suitable for display to the customer. |
AVAILQUANTITY | INTEGER | If specified, indicates the quantity available for purchase. |
ISEXPEDITED | CHAR(1) NOT NULL DEFAULT 'N' | Indicates that the item should receive expedited handling in fulfillment. Y indicates that it should receive expedited handling. N indicates that it should not (this is the default). |
OPTCOUNTER | SMALLINT | Reserved for IBM internal use. |
REQUESTEDSHIPDATE | TIMESTAMP | The ship date that has been requested for the order item. When no date (null) is specified, the ship date will be whenever the item is available to be shipped. When a date is specified, the ship date will be no sooner than the requested date. This is because a backorder could delay the ship date beyond the requested date. See the definition of ALLOCATIONOFFSET and MAXFOOFFSET in the STORE table. |
TIECODE | SMALLINT | TieCode used for items that are shipping at the same time (Ship Together). |
TOTALSHIPADJUST | DECIMAL (20,5) DEFAULT 0 | This is the total of all adjustments give to this orderitem with respect to shipping. The adjustment value is a summation of contracts, promotional, and service representative adjustments. |
BASESHIPCHARGE | DECIMAL (20,5) DEFAULT 0 | This is the unadjusted total shipping amount for these orderitems. This value is the true cost for shipping these items. This quantity contains no adjustments. To get the true cost the customer is charged you must subract the ORDERITEMS.TOTALSHIPADJUST value. The true cost charged to the customer can also be found in the ORDERITEMS.SHIPCHARGE field without a need for calculation. |
Index Name | Indexed Column Names | Index Type |
---|---|---|
SQL050212031244000 | ORDERITEMS_ID | Primary Key |
I0000173 | ORDERITEMS_ID+TIMERELEASED+TIMESHIPPED+INVENTORYSTATUS | Non-Unique Index |
I0000360 | ORDERITEMS_ID+ORDRELEASENUM+ORDERS_ID | Non-Unique Index |
I0000369 | STOREENT_ID | Non-Unique Index |
I0000639 | ALLOCADDRESS_ID | Non-Unique Index |
I0000640 | TRADING_ID | Non-Unique Index |
I0000641 | TERMCOND_ID | Non-Unique Index |
I0000642 | SHIPMODE_ID | Non-Unique Index |
I0000643 | FFMCENTER_ID | Non-Unique Index |
I0000644 | OFFER_ID | Non-Unique Index |
I0000645 | CATENTRY_ID | Non-Unique Index |
I0000646 | MEMBER_ID | Non-Unique Index |
I0000647 | ADDRESS_ID | Non-Unique Index |
I0000648 | ITEMSPC_ID | Non-Unique Index |
I0000649 | ALLOCFFMC_ID | Non-Unique Index |
I172138 | ORDERS_ID+ORDRELEASENUM+STOREENT_ID+FFMCENTER_ID | Non-Unique Index |
Constraint Name | Column Name(s) | Foreign Table Name | Foreign Column Name(s) | Constraint Type |
---|---|---|---|---|
F_499 | ALLOCADDRESS_ID | ADDRESS | ADDRESS_ID | Cascade |
F_503 | ADDRESS_ID | ADDRESS | ADDRESS_ID | Cascade |
F_505 | CATENTRY_ID | CATENTRY | CATENTRY_ID | Cascade |
F_500 | ALLOCFFMC_ID | FFMCENTER | FFMCENTER_ID | Cascade |
F_508 | FFMCENTER_ID | FFMCENTER | FFMCENTER_ID | Cascade |
F_502 | ITEMSPC_ID | ITEMSPC | ITEMSPC_ID | Cascade |
F_504 | MEMBER_ID | MEMBER | MEMBER_ID | Cascade |
F_506 | OFFER_ID | OFFER | OFFER_ID | Cascade |
F_496 | ORDERS_ID | ORDERS | ORDERS_ID | Cascade |
F_501 | OUTPUTQ_ID | OUTPUTQ | OUTPUTQ_ID | Cascade |
F_509 | SHIPMODE_ID | SHIPMODE | SHIPMODE_ID | Cascade |
F_507 | STOREENT_ID | STOREENT | STOREENT_ID | Cascade |
F_497 | TERMCOND_ID | TERMCOND | TERMCOND_ID | Cascade |
F_498 | TRADING_ID | TRADING | TRADING_ID | Cascade |
Constraint Name | Referenced Column Name | Foreign Table Name | Foreign Column Name(s) | Constraint Type |
---|---|---|---|---|
F_125 | ORDERITEMS_ID | BKORDALLOC | ORDERITEMS_ID | Cascade |
F_126 | ORDERITEMS_ID | BKORDITEM | ORDERITEMS_ID | Cascade |
F_261 | ORDERITEMS_ID | CPITMAP | ORDERITEMS_ID | Cascade |
F_476 | ORDERITEMS_ID | OICOMPLIST | ORDERITEMS_ID | Cascade |
F_1228 | ORDERITEMS_ID | OICOMPREL | ORDERITEMS_ID | Cascade |
F_520 | ORDERITEMS_ID | ORDIADJUST | ORDERITEMS_ID | Cascade |
F_523 | ORDERITEMS_ID | ORDICALCD | ORDERITEMS_ID | Cascade |
F_524 | ORDERITEMS_ID | ORDIMEEXTN | ORDERITEMS_ID | Cascade |
F_526 | ORDERITEMS_ID | ORDIOFFER | ORDERITEMS_ID | Cascade |
F_527 | ORDERITEMS_ID | ORDIPROF | ORDERITEMS_ID | Cascade |
F_941 | ORDERITMES_ID | ORDISTAT | ORDERITEMS_ID | Cascade |
F_528 | ORDERITEMS_ID | ORDITAX | ORDERITEMS_ID | Cascade |
F_1077 | ORDERITEMS_ID | ORDITPROMOCD | ORDERITEMS_ID | Cascade |
F_531 | ORDERITEMS_ID | ORDITRD | ORDERITEMS_ID | Cascade |
F_545 | ORDERITEMS_ID | ORDPICKHST | ORDERITEMS_ID | Cascade |
F_549 | ORDERITEMS_ID | ORDSHIPHST | ORDERITEMS_ID | Cascade |
F_987 | ORDERITEMS_ID | PATTRVALUE | ORDERITEMS_ID | Cascade |
F_654 | ORDERITEMS_ID | RABACKALLO | ORDERITEMS_ID | Cascade |
F_710 | ORDERITEMS_ID | RMAITEM | ORDERITEMS_ID | Cascade |
F_827 | ORDERITEMS_ID | TCPITMAP | ORDERITEMS_ID | Cascade |
F_870 | ORDERITEMS_ID | TRDDEPAMT | ORDERITEMS_ID | Cascade |
F_876 | ORDERITEMS_ID | TRDPURAMT | ORDERITEMS_ID | Cascade |
PREPAREFLAGS column
Contains the following bit flags indicating special processing associated with this order item:
- 1
- generated
The order item was generated during a previous execution of the OrderPrepare command. The next time the OrderPrepare command is run, it first removes all generated order items so that they can be re-generated if and as applicable.
- 2
- priceOverride
The price of the order item has been manually entered and will not be changed by customer commands.
- 4
- fulfillmentCenterOverride
The fulfillment center has been manually specified and will not be changed by customer commands.
- 8
- directCalculationCodeAttachment
CalculationCodes may be directly attached to the order item. The default CalculationCodeCombineMethod will not look for direct attachments unless this flag is true.
- 16
- shippingChargeByCarrier
The contract for this order item indicates that no shipping charge will be calculated by WebSphere Commerce. It may be calculated and charged by the carrier on fulfillment.
- 32
- quotation
The order item was obtained from a quotation. The price will not be automatically refreshed by customer commands.
- 64
- notConfigured
Price lookup and inventory allocation for this order item is not done using the component items found in the OICOMPLIST table. This flag does not need to be set for order items whose CONFIGURATIONID column value is null.
- 128
- autoAdd
This order item was automatically added to the order by the OrderCalculate command. This flag is for information-display purposes only and does not affect price calculations.