Reference > Database > WebSphere Commerce database tables > O


WebSphere Commerce database table: ORDERITEMS

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 The status for the order item. It may not be the same as the status in the order.
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 not allocated nor on back-order.

BO

Inventory is on back-order.

ALLC

Inventory is allocated.

FUL

Inventory has been released for fulfillment.

AVL

Inventory is available.
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.
HOLD = a temporary status between INT and OUT status.
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 it 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. This column also includes all kinds of shipping charge adjustments like discount, coupon, shipping adjustment and surcharge.
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 Item, 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 base shipping charge associated with the order item, in the currency specified by CURRENCY, it is the shipping charge before any adjustments. The shipping charge adjustment will be persisted in the ORDERITEMS.TOTALADJUSTMENT column with other adjustments including discount and surcharge. The total shipping charge is the sum of base shipping charge in ORDERITEMS.SHIPCHARGE column and the shipping charge adjustment in ORDERITEMS.TOTALADJUSTMENT column.
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 bit flags indicating special processing to be performed by the OrderPrepare command.

PREPAREFLAGS column
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).

Indexes
Index Name Indexed Column Names Index Type
SQL100520030401410 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

Constraints
Constraint Name Column Names Foreign Table Name Foreign Column Names 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

Referenced By
Constraint Name Referenced Column Name Foreign Table Name Foreign Column Names 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_3474 ORDERITEMS_ID ORDITEMCONF 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.

256

hasPersonalizationAttributes

The order item contains personalization attributes.

512

skipRepricing

The order item was marked as not to be repriced. For example, it is set as skipRepricing when a quote is generated into an order if the quote has not expired.

1024

shippingAdjusted

The order item's shipping charge is manually adjusted.

2048

priceRefreshed

The order item's price has already been refreshed.


Related reference

_vcc_freeselect_bottom_


+

Search Tips   |   Advanced Search