Magento: Sales Order Schema

@Version 1.0      Table Name: reporting_sales_flat_order

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

sales_flat_order Description
entity_id Order ID in Magento
state The order state describes the position of an order in the workflow.
Can be one of the followingNew
Pending Payment
Processing
Complete
Closed
Canceled
On Hold
Payment Review
status Can be custom based on needsProcessing
Pending Payment
Suspected Fraud
Payment Review
Pending
On Hold
Complete
Closed
Canceled
Pending PayPal
Despatched
coupon_code Coupon Code Used for the order
protect_code Used by Magento while creating a new order
shipping_description Description of Shipping used for this order
is_virtual We do not sell virtual products
store_id ID of the store. Mapping0 Admin
1 INT-en
4 US-en
5 EU-en
6 UK-en
7 SA-ar
8 SA-en
customer_id ID of the customer in Magento
base_discount_amount Discount amount in the base currency of the store
base_discount_canceled Canceled Discount Amount in the base currency
base_discount_invoiced Invoiced Discount in the base currency of the store
base_discount_refunded Refunded Discount in the base currency of the store
base_grand_total Grand Total(in store base currency) is the (Amount + Tax – Discount + Shipping)
base_shipping_amount Shipping Amount(Store Base Currency)
base_shipping_canceled Canceled Shipping Amount(Store Base Currency)
base_shipping_invoiced Invoiced Shipping Amount(Store Base Currency)
base_shipping_refunded Refunded Shipping Amount(Store Base Currency)
base_shipping_tax_amount Tax Amount on Shipping(Store Base Currency) – Currently ‘0’ always
base_shipping_tax_refunded Refunded Tax Amount on Shipping(Store Base Currency) – Currently ‘0’ always
base_subtotal Subtotal without shipping price and without tax (Store Base Currency)
base_subtotal_canceled Canceled Subtotal without shipping price and without tax (Store Base Currency)
base_subtotal_invoiced Invoiced Subtotal without shipping price and without tax (Store Base Currency)
base_subtotal_refunded Refunded Subtotal without shipping price and without tax (Store Base Currency)
base_tax_amount Tax Amount (Store Base Currency)
base_tax_canceled Canceled Tax Amount (Store Base Currency)
base_tax_invoiced Invoiced Tax Amount (Store Base Currency)
base_tax_refunded Refunded Tax Amount (Store Base Currency)
base_to_global_rate Base Currency to Global(GBP) Rate (Ex: For international orders this is USD to GBP Rate)
base_to_order_rate Base Currency to Order Currency(Transaction Currency) Rate (Ex: For international orders in Euros this is EUR to USD Rate)
base_total_canceled
base_total_invoiced
base_total_invoiced_cost
total_cost_admin Actual cost in Admin Site ( usually GBP)
base_total_offline_refunded
base_total_online_refunded
base_total_paid
base_total_qty_ordered
base_total_refunded
discount_amount
discount_canceled
discount_invoiced
discount_refunded
grand_total
shipping_amount
shipping_canceled
shipping_invoiced
shipping_refunded
shipping_tax_amount
shipping_tax_refunded
store_to_base_rate
store_to_order_rate
subtotal
subtotal_canceled
subtotal_invoiced
subtotal_refunded
tax_amount
tax_canceled
tax_invoiced
tax_refunded
total_canceled
total_invoiced
total_offline_refunded
total_online_refunded
total_paid
total_qty_ordered Units of the whole order
total_refunded
can_ship_partially
can_ship_partially_item
customer_is_guest Flag to identify if the user is guest or not
customer_note_notify
billing_address_id Address ID of the Billing Address
customer_group_id ID of the customer group in Magento
edit_increment
email_sent If email was sent to order
forced_shipment_with_invoice
payment_auth_expiration
quote_address_id Address ID of the quote – Can be ignored
quote_id ID of the Quote that got transformed to an order – Can be ignored if we need this quote information?
shipping_address_id Address ID of the Shipping Address
adjustment_negative
adjustment_positive
base_adjustment_negative
base_adjustment_positive
base_shipping_discount_amount
base_subtotal_incl_tax
base_total_due
payment_authorization_amount
shipping_discount_amount
subtotal_incl_tax
total_due
weight Weight of the products
customer_dob Customer DOB
increment_id The Order Number which is seen by the customer
applied_rule_ids Promotion rule ids separated by comma
base_currency_code Base currency code
customer_email Customer Emai
customer_middlename Customer middlename
customer_prefix Customer prefix
customer_suffix Customer suffix
customer_taxvat Customer tax/vat number
customer_created_at Customer Created Date
customer_updated_at Last Customer info updated date
discount_description Description of the discount – Ex. FREESHIP
ext_customer_id Can be ignored – Always null
ext_order_id Can be ignored – Always null
global_currency_code Customer Code for the whole magento installation. Always GBP
hold_before_state State before being put on hold
hold_before_status Status before being put on hold
order_currency_code Currency Code the customer selected placing the order
original_increment_id Original Increment ID of the order if was reordered
relation_child_id EntityID of the child order
relation_child_real_id IncrementID of child
relation_parent_id EntityID of parent
relation_parent_real_id Increment ID of parent
remote_ip IP address of the customer (Has values for 153779/ 162352 orders)
shipping_method Shipping Method used
store_currency_code Base Currency code of the store
store_name Name of the store
x_forwarded_for Forwarded IP – can be ignored
customer_note Note sent to customer
created_at Order Created At
updated_at Order Updated At
total_item_count Number of line items
customer_gender Customer gender if entered
hidden_tax_amount Not used. Can be ignored.
base_hidden_tax_amount Not used. Can be ignored.
shipping_hidden_tax_amount Not used. Can be ignored.
base_shipping_hidden_tax_amnt Not used. Can be ignored.
hidden_tax_invoiced Not used. Can be ignored.
base_hidden_tax_invoiced Not used. Can be ignored.
hidden_tax_refunded Not used. Can be ignored.
base_hidden_tax_refunded Not used. Can be ignored.
shipping_incl_tax Shipping Amount With Tax (Order Currency)
base_shipping_incl_tax Shipping Amount With Tax (Store Base Currency)
coupon_rule_name Coupon rule name
paypal_ipn_customer_notified Records if IPN callback confirmation is sent to customer
gift_message_id ID of the gift message that is entered by customer
billing_address_entity_id
billing_address_order_id
billing_customer_address_id
billing_quote_address_id
billing_region_id
billing_customer_id
billing_region
billing_postcode
billing_street
billing_city
billing_country_id
billing_prefix
billing_middlename
billing_suffix
billing_vat_id
billing_vat_is_valid
billing_vat_request_id
billing_vat_request_date
billing_vat_request_success
shipping_address_entity_id
shipping_address_order_id
shipping_customer_address_id
shipping_quote_address_id
shipping_region_id
shipping_customer_id
shipping_region
shipping_postcode
shipping_street
shipping_city
shipping_country_id
shipping_prefix
shipping_middlename
shipping_suffix
shipping_vat_id
shipping_vat_is_valid
shipping_vat_request_id
shipping_vat_request_date
shipping_vat_request_success
shipping_prefix
shipping_middlename
shipping_suffix
shipping_company
shipping_vat_id
shipping_vat_is_valid
shipping_vat_request_id
shipping_vat_request_date
shipping_vat_request_success

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>