Magento: Sales Order Line Item Schema

@Version 1.0      Table Name: reporting_sales_flat_order_item

This table contains information about each line items as described below. If something does not fit into your ecommerce configuration, do give us a shout at info@suiteweekly.com

Column title e.g value Description Notes
item_id 1280555 Line Item ID This is the ID of the line item and can be ignored
order_id 230368 The Magento OrderID Unique numeric ID for orders in Magento
parent_item_id 1280554 Line Item ID for Parent (Configurable) This can be ignored since we deal with simple products which are sold.
quote_item_id 1304557 ID of the Quote which got converted to this order item. This can be ignores since we do not deal with quotes, just orders.
store_id 6 The Store ID which needs to be mapped to Store Name Please refer to map on the right  —->
created_at 41445.47726 The order created time
updated_at 41444.59064 The order Updated time This can be difference based on  change in status like Refund/Cancelled/Dispatched
product_id 97497 The Magento ProductID Unique numeric ID for products in Magento
product_type simple Product Type Always Simple. This table is normalised to have simple data only.
product_options Product options Can be ignored
weight Product weight
is_virtual 0 For virtual products like PDF, Software We do not use this in A&A today
sku MAY-16205-1AND-12 months SKU of the product
name White Tee & Shorts Set-12 months Name of the product
description Description of the product
applied_rule_ids IDs of the Promotion rules that were applied for the line item.  To report any coupon used, ex. for shipping which is not a line item, Pick up from column coupon_code from reporting_sales_flat_order Important to identify the promotion/coupon applied etc
additional_data Can be ignored Always Null ( empty) value
free_shipping 0 Can be ignored Doesnot work as expected in Magento
is_qty_decimal 0 Can be ignored We do not use decimal quantities/stock
no_discount 0 Can be ignored Always Zero Value
qty_backordered Can be ignored We do not support backordering in the site. Default NULL
qty_canceled 0 Qty Canceled Need to be used for calculations in BIME
qty_invoiced 1 Qty Invoiced Is different from Qty Orderd since magento supports partial invoices for one order
qty_ordered 1 Qty Orderd
qty_refunded 0 Qty Refunded Usually Error  – pick from config(to be fixed)
qty_shipped 0 Qty Shipped
base_cost 8 Cost of the product in base currency. If the cost in the store is not set(NULL) the cost from the default store is taken which is in GBP
price 13 Sale Price in Customer Selected Currency WITHOUT TAX. Usually the Final price from Configurable INTL Site has GBP, EUR and USD(Base)
base_price 13 Sale Price in Store Base Currency WITHOUT TAX. Usually the Final price from Configurable Please refer to map on the right —->
original_price 13 Sale Price in Customer Selected Currency WITH TAX – Usually the price of the simple product BEWARE: Customer Service can change these prices to custom prices in the  admin panel. So always, use the price_incl_tax columns
base_original_price 13 Sale Price in Store Base Currency WITH TAX – Usually the price of the Simple Sale Price :: Can be Price or Special price depending on order time
tax_percent 0 The tax percentage
tax_amount 0 Tax amount in customer selected currency Cancelled orders, payment failure orders have this amount in the tables but these are not invoiced or paid. Hence tax_invoiced and base_tax_invoiced should be always used
base_tax_amount 0 Tax amount in Store Base currency
tax_invoiced 0 Actually Billed TAX Amount in customer selected currency Should be used always
base_tax_invoiced 0 Actually Billed TAX Amount in Store Base Currency
discount_percent 0 Discount Percentage Usually applies on the price with tax : Eg: Original_Price and base_original_price
discount_amount 0 Discount on original_price in customer selected currency Remember you have to handle currency based on store
base_discount_amount 0 Discount on base_original_price in store base currency
discount_invoiced 0 Actually Billed Discount Amount on original_price on customer selected currency Should be used always
base_discount_invoiced 0 Actually Billed Discount Amount on base_original_price in Store Base Currency
amount_refunded 0 Amount Refunded in Customer Selected Currency WITHOUT TAX This number in Magento Order info included TAX
base_amount_refunded 0 Amount Refunded in Store Base Currency WITHOUT TAX This number in Magento Order info included TAX
row_total 13 Line item total WITHOUT TAX in Customer Seleted Currency Strangely This doesnot include discount
base_row_total 13 Line item total WITHOUT TAX in Store Base Currency
row_invoiced 13 Actually Billed : Line item total WITHOUT TAX in Customer Seleted Currency Strangely This doesnot include discount
base_row_invoiced 13 Actually Billed : Line item total WITHOUT TAX in Store Base Currency
row_weight 0 Weight of the line item We can ignore this for now
base_tax_before_discount Not applicable to us. Only 7 rows in 2010 has little data
tax_before_discount Not applicable to us. Only 7 rows in 2010 has little data
ext_order_item_id Not applicable to us. Always NULL
locked_do_invoice Not applicable to us. Always 0 or null
locked_do_ship Shipping not done required in the future
price_incl_tax 13 The price the product was sold at in Customer Selected Currency
base_price_incl_tax 13 The price the product was sold at in Store Base Currency Included any custom pricing if applied by customer service
row_total_incl_tax 13 Revenue Including Tax – Discount Amount(Customer Selected Currency)
base_row_total_incl_tax 13 Revenue Including Tax – Discount Amount(Store Base Currency)
hidden_tax_amount Can be ignored We do not have this in A&A
base_hidden_tax_amount Can be ignored We do not have this in A&A
hidden_tax_invoiced 0 Can be ignored We do not have this in A&A
base_hidden_tax_invoiced 0 Can be ignored We do not have this in A&A
hidden_tax_refunded Can be ignored We do not have this in A&A
base_hidden_tax_refunded Can be ignored We do not have this in A&A
is_nominal 0 Can be ignored We do not have this in A&A
tax_canceled TAX canceled in Store Base Currency Can be ignored since we do not do partial cancellations
hidden_tax_canceled Can be ignored
tax_refunded Tax Refunded in customer selected currency
base_tax_refunded Tax Refunded in Store Base currency
discount_refunded Discount Refunded in Customer Selected Currency
base_discount_refunded Discount Refunded in Store Base Currency
gift_message_id Doesnot work as expected Gift Message existence info can be picked up for the Order Table
gift_message_available 0 Doesnot work as expected Gift Message existence info can be picked up for the Order Table
base_weee_tax_applied_amount 0 Can be ignored Always Zero(0)
base_weee_tax_applied_row_amnt Can be ignored Always Zero(0)
weee_tax_applied_amount 0 Can be ignored Always Zero(0)
weee_tax_applied_row_amount 0 Can be ignored Always Zero(0)
weee_tax_applied a:0:{} Can be ignored Always Zero(0)
weee_tax_disposition 0 Can be ignored Always Zero(0)
weee_tax_row_disposition 0 Can be ignored Always Zero(0)
base_weee_tax_disposition 0 Can be ignored Always Zero(0)
base_weee_tax_row_disposition 0 Can be ignored Always Zero(0)
configurable_sku MAY-16205-1AND SKU of the parent configurable product, if any
configurable_name White Tee & Shorts Set Name of the parent configurable product, if any
configurable_created_at 6/19/2013 Created at date of the parent configurable
configurable_updated_at 6/19/2013 Updated at date of the parent configurable
configurable_product_id 97490 Product ID of the configurable product
configurable_description Description of the configurable product
configurable_quote_item_id 1304556 Quote ID of the configurable in the Quotes Table Can be ignored

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>