# Orders Lists orders that match specified search criteria. This call returns an array of objects, but detailed information about each object’s attributes might not be included. See https://developer.adobe.com/commerce/webapi/rest/attributes#OrderRepositoryInterface to determine which call to use to get detailed information about all attributes for an object. ## Documentation Lists orders that match specified search criteria. This call returns an array of objects, but detailed information about each object’s attributes might not be included. See https://developer.adobe.com/commerce/webapi/rest/attributes#OrderRepositoryInterface to determine which call to use to get detailed information about all attributes for an object. - Catalog: `Magento` - Schema: `Sales` - Primary Keys: `entity_id` This is a read-only table function. The Magento API may not support changing the data or the Invantive UniversalSQL driver for Magento does not cover it. In the latter case, please use the table NativePlatformScalarRequests to upload data to the Magento API. ## Parameters of Table Function The following parameters can be used to control the behaviour of the table function `Orders`. A value must be provided at all times for required parameters, but optional parameters in general do not need to have a value and the execution will default to a pre-defined behaviour. Values can be specified by position and by name. In both cases, all parameters not specified will be evaluated using their default values. Value specification by position is done by listing all values from the first to the last needed value. For example: a `select * from table(value1, value2, value3)` on a table with four parameters will use the default value for the fourth parameter and the specified values for the first three. Value specification by name is done by listing all values that require a value. For example with `select * from table(name1 => value1, name3 => value3)` on the same table will use the default values for the second and fourth parameters and the specified values for the first and third. | Name | Data Type | Required | Default Value | Documentation | |---|:---:|:---:|:---:|---| | `searchCriteria[currentPage]` | `` | ☐ | | Current page. | | `searchCriteria[filterGroups][0][filters][0][conditionType]` | `` | ☐ | | Condition type | | `searchCriteria[filterGroups][0][filters][0][field]` | `` | ☐ | | Field | | `searchCriteria[filterGroups][0][filters][0][value]` | `` | ☐ | | Value | | `searchCriteria[pageSize]` | `` | ☐ | | Page size. | | `searchCriteria[sortOrders][0][direction]` | `` | ☐ | | Sorting direction. | | `searchCriteria[sortOrders][0][field]` | `` | ☐ | | Sorting field. | ## Columns of Table Function The columns of the table function `Orders` are shown below. Each column has an SQL data type. | Name | Data Type | Label | Required | Documentation | |---|:---:|---|:---:|---| | `adjustment_negative` | `decimal` | | ☐ | Negative adjustment value. | | `adjustment_positive` | `decimal` | | ☐ | Positive adjustment value. | | `applied_rule_ids` | `varchar2` | | ☐ | Applied rule IDs. | | `base_adjustment_negative` | `decimal` | | ☐ | Base negative adjustment value. | | `base_adjustment_positive` | `decimal` | | ☐ | Base positive adjustment value. | | `base_currency_code` | `varchar2` | | ☐ | Base currency code. | | `base_discount_amount` | `decimal` | | ☐ | Base discount amount. | | `base_discount_canceled` | `decimal` | | ☐ | Base discount canceled. | | `base_discount_invoiced` | `decimal` | | ☐ | Base discount invoiced. | | `base_discount_refunded` | `decimal` | | ☐ | Base discount refunded. | | `base_discount_tax_compensation_amount` | `decimal` | | ☐ | Base discount tax compensation amount. | | `base_discount_tax_compensation_invoiced` | `decimal` | | ☐ | Base discount tax compensation invoiced. | | `base_discount_tax_compensation_refunded` | `decimal` | | ☐ | Base discount tax compensation refunded. | | `base_grand_total` | `decimal` | | ☐ | Base grand total. | | `base_shipping_amount` | `decimal` | | ☐ | Base shipping amount. | | `base_shipping_canceled` | `decimal` | | ☐ | Base shipping canceled. | | `base_shipping_discount_amount` | `decimal` | | ☐ | Base shipping discount amount. | | `base_shipping_discount_tax_compensation_amnt` | `decimal` | | ☐ | Base shipping discount tax compensation amount. | | `base_shipping_incl_tax` | `decimal` | | ☐ | Base shipping including tax. | | `base_shipping_invoiced` | `decimal` | | ☐ | Base shipping invoiced. | | `base_shipping_refunded` | `decimal` | | ☐ | Base shipping refunded. | | `base_shipping_tax_amount` | `decimal` | | ☐ | Base shipping tax amount. | | `base_shipping_tax_refunded` | `decimal` | | ☐ | Base shipping tax refunded. | | `base_subtotal_canceled` | `decimal` | | ☐ | Base subtotal canceled. | | `base_subtotal_incl_tax` | `decimal` | | ☐ | Base subtotal including tax. | | `base_subtotal_invoiced` | `decimal` | | ☐ | Base subtotal invoiced. | | `base_subtotal_refunded` | `decimal` | | ☐ | Base subtotal refunded. | | `base_subtotal` | `decimal` | | ☐ | Base subtotal. | | `base_tax_amount` | `decimal` | | ☐ | Base tax amount. | | `base_tax_canceled` | `decimal` | | ☐ | Base tax canceled. | | `base_tax_invoiced` | `decimal` | | ☐ | Base tax invoiced. | | `base_tax_refunded` | `decimal` | | ☐ | Base tax refunded. | | `base_to_global_rate` | `decimal` | | ☐ | Base-to-global rate. | | `base_to_order_rate` | `decimal` | | ☐ | Base-to-order rate. | | `base_total_canceled` | `decimal` | | ☐ | Base total canceled. | | `base_total_due` | `decimal` | | ☐ | Base total due. | | `base_total_invoiced_cost` | `decimal` | | ☐ | Base total invoiced cost. | | `base_total_invoiced` | `decimal` | | ☐ | Base total invoiced. | | `base_total_offline_refunded` | `decimal` | | ☐ | Base total offline refunded. | | `base_total_online_refunded` | `decimal` | | ☐ | Base total online refunded. | | `base_total_paid` | `decimal` | | ☐ | Base total paid. | | `base_total_qty_ordered` | `decimal` | | ☐ | Base total quantity ordered. | | `base_total_refunded` | `decimal` | | ☐ | Base total refunded. | | `billing_address_address_type` | `varchar2` | | ☐ | Address type. | | `billing_address_city` | `varchar2` | | ☐ | City. | | `billing_address_company` | `varchar2` | | ☐ | Company. | | `billing_address_country_id` | `varchar2` | | ☐ | Country ID. | | `billing_address_customer_address_id` | `int64` | | ☐ | Country address ID. | | `billing_address_customer_id` | `int64` | | ☐ | Customer ID. | | `billing_address_email` | `varchar2` | | ☐ | Email address. | | `billing_address_entity_id` | `int64` | | ☐ | Order address ID. | | `billing_address_extension_attributes` | `varchar2` | | ☐ | The JSON text of the nested object. | | `billing_address_fax` | `varchar2` | | ☐ | Fax number. | | `billing_address_firstname` | `varchar2` | | ☐ | First name. | | `billing_address_id` | `int64` | | ☐ | Billing address ID. | | `billing_address_lastname` | `varchar2` | | ☐ | Last name. | | `billing_address_middlename` | `varchar2` | | ☐ | Middle name. | | `billing_address_parent_id` | `int64` | | ☐ | Parent ID. | | `billing_address_postcode` | `varchar2` | | ☐ | Postal code. | | `billing_address_prefix` | `varchar2` | | ☐ | Prefix. | | `billing_address_region_code` | `varchar2` | | ☐ | Region code. | | `billing_address_region_id` | `int64` | | ☐ | Region ID. | | `billing_address_region` | `varchar2` | | ☐ | Region. | | `billing_address_suffix` | `varchar2` | | ☐ | Suffix. | | `billing_address_telephone` | `varchar2` | | ☐ | Telephone number. | | `billing_address_vat_id` | `varchar2` | | ☐ | VAT ID. | | `billing_address_vat_is_valid` | `int64` | | ☐ | VAT-is-valid flag value. | | `billing_address_vat_request_date` | `varchar2` | | ☐ | VAT request date. | | `billing_address_vat_request_id` | `varchar2` | | ☐ | VAT request ID. | | `billing_address_vat_request_success` | `int64` | | ☐ | VAT-request-success flag value. | | `can_ship_partially_item` | `int64` | | ☐ | Can-ship-partially-item flag value. | | `can_ship_partially` | `int64` | | ☐ | Can-ship-partially flag value. | | `coupon_code` | `varchar2` | | ☐ | Coupon code. | | `created_at` | `varchar2` | | ☐ | Created-at timestamp. | | `customer_dob` | `varchar2` | | ☐ | In keeping with current security and privacy best practices, be sure you are aware of any potential legal and security risks associated with the storage of customers’ full date of birth (month, day, year) along with other personal identifiers (e.g., full name) before collecting or processing such data. | | `customer_email` | `varchar2` | | ☐ | Customer email address. | | `customer_firstname` | `varchar2` | | ☐ | Customer first name. | | `customer_gender` | `int64` | | ☐ | Customer gender. | | `customer_group_id` | `int64` | | ☐ | Customer group ID. | | `customer_id` | `int64` | | ☐ | Customer ID. | | `customer_is_guest` | `int64` | | ☐ | Customer-is-guest flag value. | | `customer_lastname` | `varchar2` | | ☐ | Customer last name. | | `customer_middlename` | `varchar2` | | ☐ | Customer middle name. | | `customer_note_notify` | `int64` | | ☐ | Customer-note-notify flag value. | | `customer_note` | `varchar2` | | ☐ | Customer note. | | `customer_prefix` | `varchar2` | | ☐ | Customer prefix. | | `customer_suffix` | `varchar2` | | ☐ | Customer suffix. | | `customer_taxvat` | `varchar2` | | ☐ | Customer value-added tax (VAT). | | `discount_amount` | `decimal` | | ☐ | Discount amount. | | `discount_canceled` | `decimal` | | ☐ | Discount canceled. | | `discount_description` | `varchar2` | | ☐ | Discount description. | | `discount_invoiced` | `decimal` | | ☐ | Discount invoiced. | | `discount_refunded` | `decimal` | | ☐ | Discount refunded amount. | | `discount_tax_compensation_amount` | `decimal` | | ☐ | Discount tax compensation amount. | | `discount_tax_compensation_invoiced` | `decimal` | | ☐ | Discount tax compensation invoiced amount. | | `discount_tax_compensation_refunded` | `decimal` | | ☐ | Discount tax compensation refunded amount. | | `edit_increment` | `int64` | | ☐ | Edit increment value. | | `email_sent` | `int64` | | ☐ | Email-sent flag value. | | `entity_id` | `int64` | | ☐ | Order ID. | | `ext_customer_id` | `varchar2` | | ☐ | External customer ID. | | `ext_order_id` | `varchar2` | | ☐ | External order ID. | | `extension_attributes_converting_from_quote` | `char` | | ☐ | | | `extension_attributes_gift_message_customer_id` | `int64` | | ☐ | Customer ID. Otherwise, null. | | `extension_attributes_gift_message_extension_attributes_entity_id` | `varchar2` | | ☐ | | | `extension_attributes_gift_message_extension_attributes_entity_type` | `varchar2` | | ☐ | | | `extension_attributes_gift_message_gift_message_id` | `int64` | | ☐ | Gift message ID. Otherwise, null. | | `extension_attributes_gift_message_message` | `varchar2` | | ☐ | Message text. | | `extension_attributes_gift_message_recipient` | `varchar2` | | ☐ | Recipient name. | | `extension_attributes_gift_message_sender` | `varchar2` | | ☐ | Sender name. | | `extension_attributes_notification_sent` | `int64` | | ☐ | | | `extension_attributes_pickup_location_code` | `varchar2` | | ☐ | | | `extension_attributes_send_notification` | `int64` | | ☐ | | | `forced_shipment_with_invoice` | `int64` | | ☐ | Forced-shipment-with-invoice flag value. | | `global_currency_code` | `varchar2` | | ☐ | Global currency code. | | `grand_total` | `decimal` | | ☐ | Grand total. | | `hold_before_state` | `varchar2` | | ☐ | Hold before state. | | `hold_before_status` | `varchar2` | | ☐ | Hold before status. | | `increment_id` | `varchar2` | | ☐ | Increment ID. | | `is_virtual` | `int64` | | ☐ | Is-virtual flag value. | | `order_currency_code` | `varchar2` | | ☐ | Order currency code. | | `original_increment_id` | `varchar2` | | ☐ | Original increment ID. | | `payment_account_status` | `varchar2` | | ☐ | Account status. | | `payment_additional_data` | `varchar2` | | ☐ | Additional data. | | `payment_address_status` | `varchar2` | | ☐ | Address status. | | `payment_amount_authorized` | `decimal` | | ☐ | Amount authorized. | | `payment_amount_canceled` | `decimal` | | ☐ | Amount canceled. | | `payment_amount_ordered` | `decimal` | | ☐ | Amount ordered. | | `payment_amount_paid` | `decimal` | | ☐ | Amount paid. | | `payment_amount_refunded` | `decimal` | | ☐ | Amount refunded. | | `payment_anet_trans_method` | `varchar2` | | ☐ | Anet transaction method. | | `payment_auth_expiration` | `int64` | | ☐ | Payment authorization expiration date. | | `payment_authorization_amount` | `decimal` | | ☐ | Payment authorization amount. | | `payment_base_amount_authorized` | `decimal` | | ☐ | Base amount authorized. | | `payment_base_amount_canceled` | `decimal` | | ☐ | Base amount canceled. | | `payment_base_amount_ordered` | `decimal` | | ☐ | Base amount ordered. | | `payment_base_amount_paid_online` | `decimal` | | ☐ | Base amount paid online. | | `payment_base_amount_paid` | `decimal` | | ☐ | Base amount paid. | | `payment_base_amount_refunded_online` | `decimal` | | ☐ | Base amount refunded online. | | `payment_base_amount_refunded` | `decimal` | | ☐ | Base amount refunded. | | `payment_base_shipping_amount` | `decimal` | | ☐ | Base shipping amount. | | `payment_base_shipping_captured` | `decimal` | | ☐ | Base shipping captured amount. | | `payment_base_shipping_refunded` | `decimal` | | ☐ | Base shipping refunded amount. | | `payment_cc_approval` | `varchar2` | | ☐ | Credit card approval. | | `payment_cc_avs_status` | `varchar2` | | ☐ | Credit card avs status. | | `payment_cc_cid_status` | `varchar2` | | ☐ | Credit card CID status. | | `payment_cc_debug_request_body` | `varchar2` | | ☐ | Credit card debug request body. | | `payment_cc_debug_response_body` | `varchar2` | | ☐ | Credit card debug response body. | | `payment_cc_debug_response_serialized` | `varchar2` | | ☐ | Credit card debug response serialized. | | `payment_cc_exp_month` | `varchar2` | | ☐ | Credit card expiration month. | | `payment_cc_exp_year` | `varchar2` | | ☐ | Credit card expiration year. | | `payment_cc_last4` | `varchar2` | | ☐ | Last four digits of the credit card. | | `payment_cc_number_enc` | `varchar2` | | ☐ | Encrypted credit card number. | | `payment_cc_owner` | `varchar2` | | ☐ | Credit card number. | | `payment_cc_secure_verify` | `varchar2` | | ☐ | Credit card secure verify. | | `payment_cc_ss_issue` | `varchar2` | | ☐ | Credit card SS issue. | | `payment_cc_ss_start_month` | `varchar2` | | ☐ | Credit card SS start month. | | `payment_cc_ss_start_year` | `varchar2` | | ☐ | Credit card SS start year. | | `payment_cc_status_description` | `varchar2` | | ☐ | Credit card status description. | | `payment_cc_status` | `varchar2` | | ☐ | Credit card status. | | `payment_cc_trans_id` | `varchar2` | | ☐ | Credit card transaction ID. | | `payment_cc_type` | `varchar2` | | ☐ | Credit card type. | | `payment_echeck_account_name` | `varchar2` | | ☐ | eCheck account name. | | `payment_echeck_account_type` | `varchar2` | | ☐ | eCheck account type. | | `payment_echeck_bank_name` | `varchar2` | | ☐ | eCheck bank name. | | `payment_echeck_routing_number` | `varchar2` | | ☐ | eCheck routing number. | | `payment_echeck_type` | `varchar2` | | ☐ | eCheck type. | | `payment_entity_id` | `int64` | | ☐ | Entity ID. | | `payment_extension_attributes_notification_message` | `varchar2` | | ☐ | | | `payment_extension_attributes_vault_payment_token_created_at` | `varchar2` | | ☐ | Token creation timestamp | | `payment_extension_attributes_vault_payment_token_customer_id` | `int64` | | ☐ | Customer ID. | | `payment_extension_attributes_vault_payment_token_entity_id` | `int64` | | ☐ | Entity ID. | | `payment_extension_attributes_vault_payment_token_expires_at` | `varchar2` | | ☐ | Token expiration timestamp | | `payment_extension_attributes_vault_payment_token_gateway_token` | `varchar2` | | ☐ | Gateway token ID | | `payment_extension_attributes_vault_payment_token_is_active` | `char` | | ☐ | Is active. | | `payment_extension_attributes_vault_payment_token_is_visible` | `char` | | ☐ | Is visible. | | `payment_extension_attributes_vault_payment_token_payment_method_code` | `varchar2` | | ☐ | Payment method code | | `payment_extension_attributes_vault_payment_token_public_hash` | `varchar2` | | ☐ | Public hash | | `payment_extension_attributes_vault_payment_token_token_details` | `varchar2` | | ☐ | Token details | | `payment_extension_attributes_vault_payment_token_type` | `varchar2` | | ☐ | Type | | `payment_extension_attributes_vault_payment_token_website_id` | `int64` | | ☐ | website id. | | `payment_last_trans_id` | `varchar2` | | ☐ | Last transaction ID. | | `payment_method` | `varchar2` | | ☐ | Method. | | `payment_parent_id` | `int64` | | ☐ | Parent ID. | | `payment_po_number` | `varchar2` | | ☐ | PO number. | | `payment_protection_eligibility` | `varchar2` | | ☐ | Protection eligibility. | | `payment_quote_payment_id` | `int64` | | ☐ | Quote payment ID. | | `payment_shipping_amount` | `decimal` | | ☐ | Shipping amount. | | `payment_shipping_captured` | `decimal` | | ☐ | Shipping captured. | | `payment_shipping_refunded` | `decimal` | | ☐ | Shipping refunded. | | `protect_code` | `varchar2` | | ☐ | Protect code. | | `quote_address_id` | `int64` | | ☐ | Quote address ID. | | `quote_id` | `int64` | | ☐ | Quote ID. | | `relation_child_id` | `varchar2` | | ☐ | Relation child ID. | | `relation_child_real_id` | `varchar2` | | ☐ | Relation child real ID. | | `relation_parent_id` | `varchar2` | | ☐ | Relation parent ID. | | `relation_parent_real_id` | `varchar2` | | ☐ | Relation parent real ID. | | `remote_ip` | `varchar2` | | ☐ | Remote IP address. | | `shipping_amount` | `decimal` | | ☐ | Shipping amount. | | `shipping_canceled` | `decimal` | | ☐ | Shipping canceled amount. | | `shipping_description` | `varchar2` | | ☐ | Shipping description. | | `shipping_discount_amount` | `decimal` | | ☐ | Shipping discount amount. | | `shipping_discount_tax_compensation_amount` | `decimal` | | ☐ | Shipping discount tax compensation amount. | | `shipping_incl_tax` | `decimal` | | ☐ | Shipping including tax amount. | | `shipping_invoiced` | `decimal` | | ☐ | Shipping invoiced amount. | | `shipping_refunded` | `decimal` | | ☐ | Shipping refunded amount. | | `shipping_tax_amount` | `decimal` | | ☐ | Shipping tax amount. | | `shipping_tax_refunded` | `decimal` | | ☐ | Shipping tax refunded amount. | | `state` | `varchar2` | | ☐ | State. | | `status` | `varchar2` | | ☐ | Status. | | `store_currency_code` | `varchar2` | | ☐ | Store currency code. | | `store_id` | `int64` | | ☐ | Store ID. | | `store_name` | `varchar2` | | ☐ | Store name. | | `store_to_base_rate` | `decimal` | | ☐ | Store-to-base rate. | | `store_to_order_rate` | `decimal` | | ☐ | Store-to-order rate. | | `subtotal_canceled` | `decimal` | | ☐ | Subtotal canceled amount. | | `subtotal_incl_tax` | `decimal` | | ☐ | Subtotal including tax amount. | | `subtotal_invoiced` | `decimal` | | ☐ | Subtotal invoiced amount. | | `subtotal_refunded` | `decimal` | | ☐ | Subtotal refunded amount. | | `subtotal` | `decimal` | | ☐ | Subtotal. | | `tax_amount` | `decimal` | | ☐ | Tax amount. | | `tax_canceled` | `decimal` | | ☐ | Tax canceled amount. | | `tax_invoiced` | `decimal` | | ☐ | Tax invoiced amount. | | `tax_refunded` | `decimal` | | ☐ | Tax refunded amount. | | `total_canceled` | `decimal` | | ☐ | Total canceled. | | `total_due` | `decimal` | | ☐ | Total due. | | `total_invoiced` | `decimal` | | ☐ | Total invoiced amount. | | `total_item_count` | `int64` | | ☐ | Total item count. | | `total_offline_refunded` | `decimal` | | ☐ | Total offline refunded amount. | | `total_online_refunded` | `decimal` | | ☐ | Total online refunded amount. | | `total_paid` | `decimal` | | ☐ | Total paid. | | `total_qty_ordered` | `decimal` | | ☐ | Total quantity ordered. | | `total_refunded` | `decimal` | | ☐ | Total amount refunded. | | `updated_at` | `varchar2` | | ☐ | Updated-at timestamp. | | `weight` | `decimal` | | ☐ | Weight. | | `x_forwarded_for` | `varchar2` | | ☐ | X-Forwarded-For field value. |