ARF_LINE_ITEM

This table contains one row for every line item in every order placed by customers. Note the following:

  • Discount refers to any amount subtracted from the price of the item or the order, such as coupons.

  • Tax and shipping costs are applied to an order. The pipeline processors that populate the data warehouse allocate these costs across line items. See the ATG Commerce Programming Guide for information on these processors.

  • Local currency refers to the currency in which the customer pays; standard currency is the currency used for reporting.

    Column Name

    Type

    Length

    Null Allowed

    Description

    SUBMIT_TIMESTAMP

    timestamp

    Yes

    The date and time an order was submitted.

    SUBMIT_DAY_ID

    varchar

    40

    No

    Day order was submitted. Foreign key to ARF_TIME_DAY.ID column.

    SUBMIT_TIME_ID

    int

    No

    Time the order was submitted. Foreign key to ARF_TIME_TOD.ID column.

    SKU_ID

    int

    No

    Identifier of SKU ordered. Foreign key to ARF_SKU.ID column.

    PRODUCT_ID

    int

    No

    Identifier for item’s product. Foreign key to ARF_PRODUCT.ID column.

    CATEGORY_ID

    int

    No

    Identifier for item’s category. Foreign key to ARF_CATEGORY.ID column.

    CUSTOMER_ID

    int

    No

    Identifier for the customer who made the purchase. Foreign key to ARF_USER.ID column.

    NCUSTOMER_ID

    varchar

    40

    Yes

    The natural identifier of the customer.

    AGENT_ID

    varchar

    40

    Yes

    Identifier for the agent, if any, who participated in the order. Foreign key to ARF_IU_USER.ID column.

    ORIGIN_SALES_CHANNEL_ID

    numeric or tinyint

    1

    Yes

    Identifier for sales channel that originated the order. Foreign key to ARF_SALES_CHANNEL.ID column.

    SUBMIT_SALES_CHANNEL_ID

    numeric or tinyint

    1

    Yes

    Identifier for sales channel that completed the order. Foreign key to ARF_SALES_CHANNEL.ID column.

    STIM_GRP_ID

    smallint

    No

    Reference to the group of stimuli that may have stimulated the order. Foreign key to ARF_STIMGRP.ID column.

    SEGCLSTR_ID

    int

    No

    Identifier of segment cluster of which the customer is a member. Foreign key to ARF_SEGCLSTR.ID column.

    PROMOGRP_ID

    int

    No

    Identifier of promotion group that applies to the line item. Foreign key to ARF_PROMOGRP.ID column.

    BILLING_REGION_ID

    smallint

    No

    Identifier for the region of the customer’s billing address. Foreign key to ARF_GEO_REGION.ID column.

    SHIPPING_REGION_ID

    smallint

    No

    Identifier for the region of the customer’s shipping address. Foreign key to ARF_GEO_REGION.ID column.

    LOCAL_CURRENCY_ID

    smallint

    No

    Currency in which the line item was purchased. Foreign key to ARF_CURRENCY.ID column.

    DEMOGRAPHIC_ID

    smallint

    No

    Foreign key to ARF_DEMOGRAPHIC_ID column, which provides references to age, marital status, etc.

    SITE_VISIT_ID

    bigint

    No

    ID of site visit.

    ORDER_ID

    int

    No

    ID of order. Surrogate key.

    LINE_ITEM_ID

    bigint

    No

    Identifier; primary key. Surrogate key.

    NORDER_ID

    varchar

    40

    No

    Natural key of order from the Order repository.

    NLINE_ITEM_ID

    varchar

    40

    No

    Natural key of line item from the Order repository.

    SESSION_ID

    varchar

    128

    Yes

    ID of the session in which the order was placed.

    QUESTION_ID

    int

    No

    If the order in which the line item occurs was placed as the result of a search performed on the site, this column records the question searched on. There is a row to represent an unspecified question. Foreign key to ARF_QUESTION.ID column.

    QUANTITY

    int

    No

    Quantity of this line item ordered.

    IS_MARKDOWN

    one-digit

    No

    Identifies whether the item is subject to a markdown. A marked down item is sold at a discount that is not part of a promotion.

    1 = Yes

    0 = No

    Defaults to 0.

    LOCAL_UNIT_PRICE

    double

    No

    Unit price in the local currency. The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_GROSS_REVENUE

    double

    No

    Revenue before adjustments due to discounts, taxes, and shipping, in the local currency. Equal to unit price * quantity.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_DISCOUNT_AMOUNT

    double

    No

    Any discount applied to this specific line item, in the local currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_MARKDOWN_DISC_AMOUNT

    double

    No

    The amount of markdown discount applied to this line item in the local currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_ORDER_TAX_ALLOC

    double

    No

    Amount of tax on the order that is allocated to this line item, in the local currency. The allocation is determined by the processing pipeline.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_ORDER_SHIPPING_ALLOC

    double

    No

    Amount of shipping on the order that is allocated to this line item. The allocation is determined by the processing pipeline.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_ORDER_DISCOUNT_ALLOC

    double

    No

    Amount of any order-level discounts that are allocated to this line item, in the local currency. The allocation is determined by the processing pipeline.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_NET_REVENUE

    double

    No

    Revenue after adjustments due to discounts, taxes, and shipping, in the local currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_ORDER_NET_REVENUE

    double

    No

    Revenue for the entire order of which this line item is a part, after adjustments due to discounts, taxes, and shipping, in the local currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_APPSMT_DBT_ALLOC_AMT

    double

    Yes

    The amount allocated to this line item from a debit to the order made by a Commerce Service Center agent. The amount is in the local currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_APPSMT_CDT_ALLOC_AMT

    double

    Yes

    The amount allocated to this line item from a credit to the order made by a Commerce Service Center agent. The amount is in the local currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    LOCAL_PRICE_OVERRIDE_AMT

    double

    Yes

    Price override performed by a Commerce Service Center agent on a commerce item in the order, in the local currency.

    STANDARD_UNIT_PRICE

    double

    No

    Unit price in the reporting currency. The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_GROSS_REVENUE

    double

    No

    Revenue before adjustments due to discounts, taxes, and shipping, in the reporting currency. Equal to unit price * quantity.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_DISCOUNT_AMOUNT

    double

    No

    Any discount applied to this specific line item, in the reporting currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_MARKDOWN_DISC_AMOUNT

    double

    No

    The amount of markdown discount applied to this line item in the standard currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_ORDER_TAX_ALLOC

    double

    No

    Amount of tax on the order that is allocated to this line item, in the reporting currency. The allocation is determined by the processing pipeline.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_ORDER_SHIPPING_ALLOC

    double

    No

    Amount of shipping on the order that is allocated to this line item, in the reporting currency. The allocation is determined by the processing pipeline.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_ORDER_DISCOUNT_ALLOC

    double

    No

    Amount of any order-level discount that is allocated to this line item, in the reporting currency. The allocation is determined by the processing pipeline.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_NET_REVENUE

    double

    No

    Revenue after adjustments due to discounts, taxes, and shipping, in the reporting currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_ORDER_NET_REVENUE

    double

    No

    Revenue for the entire order of which this line item is a part, after adjustments due to discounts, taxes, and shipping, in the reporting currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_APPSMT_DBT_ALLOC_AMT

    double

    Yes

    The amount allocated to this line item from a debit to the order made by a Commerce Service Center user. The amount is in the reporting currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_APPSMT_CDT_ALLOC_AMT

    double

    Yes

    The amount allocated to this line item from a credit to the order made by a Commerce Service Center user. The amount is in the reporting currency.

    The field length is 19 digits, with 7 digits to the left of the decimal and 12 to the right.

    STANDARD_PRICE_OVERRIDE_AMT

    double

    Yes

    Price override performed by a Commerce Service Center agent on a commerce item in the order, in the reporting currency

    SUBMITTED_SITE_ID

    smallint

    No

    The site on which the order was submitted. Foreign key to ARF_SITE.ID.

    ORIGIN_SITE_ID

    smallint

    No

    The site on which the order was originally created. Foreign key to ARF_SITE.ID.

    ITEM_SITE_ID

    smallint

    No

    The site on which the item was added to the order. Foreign key to ARF_SITE.ID.

    IS_COUPON_APPLIED

    one-digit

    No

    A boolean value that indicates whether a coupon was applied to the purchase transaction

    0 = false

    1 = true