Database Table: ORDERITEMS

Each row of this table represents an OrderItem in an Order.

Column Name Column Type Column Description
ADDRESS_ID BIGINT
NULL
The shipping Address, if any, for this OrderItem.
ALLOCADDRESS_ID BIGINT
NULL
The shipping Address used when inventory for this OrderItem was allocated or backordered.
ALLOCATIONGROUP BIGINT
NULL
Reserved for IBM internal use.
ALLOCFFMC_ID INTEGER
NULL
The FulfillmentCenter from which inventory for this OrderItem is allocated or backordered.
ALLOCQUANTITY INTEGER
NOT NULL, DEFAULT 0
Quantity allocated or backordered for this OrderItem. The quantity in BASEITEM.QUANTITYMEASURE units can be calculated by multiplying this value by BASEITEM.QUANTITYMULTIPLE, for the BaseItem of the specified item indicated by ITEMSPC_ID.
AVAILQUANTITY INTEGER
NULL
If specified, indicates the quantity available for purchase.
BASECURRENCY CHARACTER (3)
NULL
The currency of BASEPRICE.
BASEPRICE DECIMAL (20,5)
NULL
If PRICE was converted from a currency different from the OrderItem currency, BASEPRICE is the price that was converted to arrive at PRICE.
CATENTRY_ID BIGINT
NULL
The CatalogEntry, if any, of the product being purchased.
COMMENTS VARCHAR (254)
NULL
Comments from the customer, such as a greeting for a gift.
CONFIGURATIONID VARCHAR (128)
NULL
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.
CORRELATIONGROUP BIGINT
NULL
Normally this is the same as ORDERITEMS_ID, except:

1. when an OrderItem is split by the AllocateInventory task command, the newly created OrderItem inherits the CORRELATIONGROUP value from the original OrderItem.

2. when the PREPAREFLAGS column indicates "quotation", the OrderItem inherits the CORRELATIONGROUP value from the corresponding OrderItem in the parent Order.
CURRENCY CHARACTER (10)
NULL
The currency of OrderItem monetary amounts other than BASEPRICE. This is the same as the currency of the order, ORDERS.CURRENCY. This is a currency code as per ISO 4217 standards.
DESCRIPTION VARCHAR (254)
NULL
A mnemonic description of the OrderItem, 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.
ESTAVAILTIME TIMESTAMP
NULL
An estimate of when sufficient inventory will be available to fulfill this OrderItem. This estimate does not include the shipping offset.
FFMCENTER_ID INTEGER
NULL
The FulfillmentCenter, if known, from which the product will ship.
FIELD1 INTEGER
NULL
Customizable.
FIELD2 VARCHAR (254)
NULL
Customizable.
FULFILLMENTSTATUS CHARACTER (4)
NOT NULL, DEFAULT 'INT'
The fulfillment status of the OrderItem:
INT = not yet released for fulfillment.
OUT = released for fulfillment.
SHIP = shipment confirmed.
INVENTORYSTATUS CHARACTER (4)
NOT NULL, DEFAULT 'NALC'
The allocation status of inventory for this OrderItem:
NALC = inventory is neither allocated nor backordered.
BO = inventory is backordered.
ALLC = inventory is allocated.
FUL = inventory has been released for fulfillment.
ITEMSPC_ID BIGINT
NULL
The specified item to be allocated from available inventory and shipped to the customer.
LASTALLOCUPDATE TIMESTAMP
NULL
The most recent time inventory was checked (for unallocated OrderItems), allocated, or backordered, for this OrderItem.
LASTCREATE TIMESTAMP
NULL
The time this OrderItem was created.
LASTUPDATE TIMESTAMP
NULL
The most recent time this OrderItem was updated. Changing inventory allocation related information does not cause this timestamp to be updated (refer to the LASTALLOCUPDATE column).
LINEITEMTYPE CHARACTER (4)
NULL
If specified, indicates the type of the OrderItem.

ALT = the OrderItem represents an alternative item (may not be exactly what the customer requested).
MEMBER_ID BIGINT
NOT NULL
The customer of the OrderItem (which is the same as the customer of the Order).
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.
OFFER_ID BIGINT
NULL
The Offer, if any, and if it still exists, from which PRICE was obtained.
ORDERITEMS_ID BIGINT
NOT NULL
Generated unique key.
ORDERS_ID BIGINT
NOT NULL
The Order this OrderItem is part of.
ORDRELEASENUM INTEGER
NULL
The associated OrderRelease, if any.
OUTPUTQ_ID BIGINT
NULL
Reserved for IBM internal use.
PARTNUM VARCHAR (64)
NULL
The part number of the CatalogEntry (CATENTRY.PARTNUMBER) for the product.
PREPAREFLAGS INTEGER
NOT NULL, DEFAULT 0
Contains the following bit flags indicating special processing to be performed by the OrderPrepare command:
1 = generated - the OrderItem was generated during a previous execution of the OrderPrepare command. The next time the OrderPrepare command is run, it first removes all generated OrderItems, so they can be re-generated if and as applicable.
2 = priceOverride - the price of the OrderItem has been manually entered, and will not be changed by customer commands.
4 = fulfillmentCenterOverride - the FulfillmentCenter has been manually specified, and will not be changed by customer commands.
8 = directCalculationCodeAttachment - CalculationCodes may be directly attached to the OrderItem. The default CalculationCodeCombineMethod will not look for direct attachments unless this flag is true.
16 = shippingChargeByCarrier - The contract for this OrderItem indicates that no shipping charges will be calculated by WebSphere Commerce. It may be calculated and charged by the carrier upon fulfillment.
32 = quotation - the OrderItem was obtained from a quotation. The price will not be automatically refreshed by customer commands.
64 = notConfigured - Price lookup and inventory allocation for this OrderItem should not be done using the component items found in the OICOMPLIST table for this OrderItem. For backward compatibility, this flag does not need to be set for OrderItems whose configurationId column value is null.
128 = autoAdd - OrderItem was added
PRICE DECIMAL (20,5)
NULL
The price for the nominal quantity of the product (CATENTSHIP.NOMINALQUANTITY).
PROMISEDAVAILTIME TIMESTAMP
NULL
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.
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.
SHIPCHARGE DECIMAL (20,5)
NULL
The shipping charge associated with this OrderItem, in the currency specified by CURRENCY.
SHIPMODE_ID INTEGER
NULL
The ShippingMode, if still known.
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.
SHIPTAXAMOUNT DECIMAL (20,5)
NULL
The total shipping taxes associated with this OrderItem, in the Currency specified by CURRENCY.
STATUS CHARACTER (1)
NOT NULL
All OrderItems for an Order hold a copy of the Order Status. See the description of the STATUS column for the ORDERS table.
STOREENT_ID INTEGER
NOT NULL
The StoreEntity the Order (this OrderItem is part of) is part of. This is normally a Store unless STATUS is Q, in which case it is normally a StoreGroup.
SUPPLIERDATA VARCHAR (254)
NULL
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)
NULL
The supplier part number, if known. Suitable for display to the customer.
TAXAMOUNT DECIMAL (20,5)
NULL
The total sales taxes associated with this OrderItem, in the Currency specified by CURRENCY.
TERMCOND_ID BIGINT
NULL
The TermAndCondition, if known, that determined the price for this OrderItem.
TIMERELEASED TIMESTAMP
NULL
The time this OrderItem was released for fulfillment.
TIMESHIPPED TIMESTAMP
NULL
The time this OrderItem was manifested for shipment.
TOTALADJUSTMENT DECIMAL (20,5)
NULL, DEFAULT 0
The total of the monetary amounts of the OrderItemAdjustments for this OrderItem, in the Currency specified by CURRENCY.
TOTALPRODUCT DECIMAL (20,5)
NULL, DEFAULT 0
PRICE times QUANTITY.
TRACKDATE TIMESTAMP
NULL
Reserved for IBM internal use.
TRACKNUMBER VARCHAR (64)
NULL
Reserved for IBM internal use.
TRADING_ID BIGINT
NULL
The TradingAgreement, if known, that determines the TermAndCondition objects (including how the price is determined) that apply to this OrderItem.
Indexes:
Index Name Indexed Column Names Index Type
I0000173 ORDERITEMS_ID, TIMERELEASED, TIMESHIPPED, INVENTORYSTATUS Non-Unique Index
I172138 ORDERS_ID, ORDRELEASENUM, STOREENT_ID, FFMCENTER_ID Non-Unique Index
SQL030416035017290 ORDERITEMS_ID Primary Key
Constraints:
Constraint Name Column Names Foreign Table Name Foreign Column Names Constraint Type
F_496 ORDERS_ID ORDERS ORDERS_ID Cascade
F_497 TERMCOND_ID TERMCOND TERMCOND_ID Cascade
F_498 TRADING_ID TRADING TRADING_ID Cascade
F_499 ALLOCADDRESS_ID ADDRESS ADDRESS_ID Cascade
F_500 ALLOCFFMC_ID FFMCENTER FFMCENTER_ID Cascade
F_501 OUTPUTQ_ID OUTPUTQ OUTPUTQ_ID Cascade
F_502 ITEMSPC_ID ITEMSPC ITEMSPC_ID Cascade
F_503 ADDRESS_ID ADDRESS ADDRESS_ID Cascade
F_504 MEMBER_ID MEMBER MEMBER_ID Cascade
F_505 CATENTRY_ID CATENTRY CATENTRY_ID Cascade
F_506 OFFER_ID OFFER OFFER_ID Cascade
F_507 STOREENT_ID STOREENT STOREENT_ID Cascade
F_508 FFMCENTER_ID FFMCENTER FFMCENTER_ID Cascade
F_509 SHIPMODE_ID SHIPMODE SHIPMODE_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_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_528 ORDERITEMS_ID ORDITAX 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_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
F_941 ORDERITEMS_ID ORDISTAT ORDERITMES_ID Cascade
F_987 ORDERITEMS_ID PATTRVALUE ORDERITEMS_ID Cascade

PREPAREFLAGS column

Contains the following bit flags indicating special processing associated with this OrderItem:

1
generated

The OrderItem was generated during a previous execution of the OrderPrepare command. The next time the OrderPrepare command is run, it first removes all generated OrderItems, so they can be re-generated if and as applicable.

2
priceOverride

The price of the OrderItem has been manually entered, and will not be changed by customer commands.

4
fulfillmentCenterOverride

The FulfillmentCenter has been manually specified, and will not be changed by customer commands.

8
directCalculationCodeAttachment

CalculationCodes may be directly attached to the OrderItem. The default CalculationCodeCombineMethod will not look for direct attachments unless this flag is true.

16
shippingChargeByCarrier

The Contract for this OrderItem indicates that no shipping charge will be calculated by WebSphere Commerce. It may be calculated and charged by the carrier upon fulfillment.

32
quotation

The OrderItem was obtained from a quotation. The price will not be automatically refreshed by customer commands.

64
notConfigured

Price lookup and inventory allocation for this OrderItem is not done using the component items found in the OICOMPLIST table. This flag does not need to be set for OrderItems whose CONFIGURATIONID column value is null.

128
autoAdd

This OrderItem was automatically added to the Order by the OrderCalculate command. This flag is for information display purposes only, and does not affect price calculations.