Reference

Endpoint Get Order (Single)

Name

get_order

Description

Returns the order information for a specified OrderId [API reference]

Related Tables

Orders

Parameters

Parameter Required Options
Name: AmazonOrderId

Label: AmazonOrderId

Filters purchase orders based on the specified orderid.

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
AmazonOrderId DT_WSTR nvarchar(500) 500
PurchaseDate DT_DBTIMESTAMP datetime
LastUpdateDate DT_DBTIMESTAMP datetime
OrderStatus DT_WSTR nvarchar(100) 100
FulfillmentChannel DT_WSTR nvarchar(50) 50
SalesChannel DT_WSTR nvarchar(100) 100
ShipServiceLevel DT_WSTR nvarchar(500) 500
CurrencyCode DT_WSTR nvarchar(50) 50
Amount DT_WSTR nvarchar(50) 50
NumberOfItemsShipped DT_I4 int
NumberOfItemsUnshipped DT_I4 int
PaymentMethod DT_WSTR nvarchar(50) 50
PaymentMethodDetails DT_WSTR nvarchar(500) 500
IsReplacementOrder DT_BOOL bit
MarketplaceId DT_WSTR nvarchar(500) 500
ShipmentServiceLevelCategory DT_WSTR nvarchar(100) 100
OrderType DT_WSTR nvarchar(500) 500
EarliestShipDate DT_DBTIMESTAMP datetime
LatestShipDate DT_DBTIMESTAMP datetime
EarliestDeliveryDate DT_DBTIMESTAMP datetime
LatestDeliveryDate DT_DBTIMESTAMP datetime
IsBusinessOrder DT_BOOL bit
IsPrime DT_BOOL bit
IsGlobalExpressEnabled DT_BOOL bit
IsPremiumOrder DT_BOOL bit
IsSoldByAB DT_BOOL bit
IsIBA DT_BOOL bit
DefaultShipFromName DT_WSTR nvarchar(500) 500
DefaultShipAddressLine1 DT_WSTR nvarchar(500) 500
DefaultShipFromCity DT_WSTR nvarchar(100) 100
DefaultShipFromStateOrRegion DT_WSTR nvarchar(50) 50
DefaultShipFromPostalCode DT_WSTR nvarchar(100) 100
DefaultShipFromCountryCode DT_WSTR nvarchar(50) 50
DefaultShipFromPhone DT_WSTR nvarchar(500) 500
DefaultShipFromAddressType DT_WSTR nvarchar(100) 100
FulfillmentSupplySourceId DT_WSTR nvarchar(500) 500
IsISPU DT_BOOL bit
IsAccessPointOrder DT_BOOL bit
HasAutomatedShippingSettings DT_BOOL bit
EasyShipShipmentStatus DT_WSTR nvarchar(500) 500
ElectronicInvoiceStatus DT_WSTR nvarchar(500) 500
If the column you are looking for is missing, consider customizing Amazon Selling Partner (SP-API) Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use Amazon Selling Partner (SP-API) Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the Orders table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - Amazon Selling Partner (SP-API)
Amazon Selling Partner Connector (SP-API) can be used to integrated SP-API that helps Amazon sellers to programmatically exchange data on listings, orders, payments, reports, and more.
Amazon Selling Partner (SP-API)
Orders
Optional Parameters
AmazonOrderId
SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the Orders table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to get order:

API Destination - Amazon Selling Partner (SP-API)
Amazon Selling Partner Connector (SP-API) can be used to integrated SP-API that helps Amazon sellers to programmatically exchange data on listings, orders, payments, reports, and more.
Amazon Selling Partner (SP-API)
Orders
Lookup
Optional Parameters
AmazonOrderId
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Sandbox - Read Single Order (Fake data for testing)

Read single order with orderid which has fake values (sandbox data) 

SELECT *
FROM get_order
--DONOT try WHERE AmazonOrderId='TEST_CASE_200' (WHERE clause) for sandbox endpoint, it will return empty row. If you try in Live API then should work. 
WITH(
	AmazonOrderId='TEST_CASE_200'
--  AmazonOrderId='TEST_CASE_IBA_200'	
)
CONNECTION(
	ServiceUrl='https://sandbox.sellingpartnerapi-na.amazon.com'
)

Read Single Order

Read single order by orderid 

SELECT * FROM Orders 
Where AmazonOrderId='902-1845936-5435065'
--CONNECTION(
--	ServiceUrl='https://sellingpartnerapi-na.amazon.com'
--)

get_order endpoint belongs to Orders table(s), and can therefore be used via those table(s).

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Sandbox - Read Single Order (Fake data for testing)

Read single order with orderid which has fake values (sandbox data) 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_order
--DONOT try WHERE AmazonOrderId=''TEST_CASE_200'' (WHERE clause) for sandbox endpoint, it will return empty row. If you try in Live API then should work. 
WITH(
	AmazonOrderId=''TEST_CASE_200''
--  AmazonOrderId=''TEST_CASE_IBA_200''	
)
CONNECTION(
	ServiceUrl=''https://sandbox.sellingpartnerapi-na.amazon.com''
)';

EXEC (@MyQuery) AT [LS_TO_AMAZON_SELLING_PARTNER_SP_API_IN_GATEWAY];

Read Single Order

Read single order by orderid 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Orders 
Where AmazonOrderId=''902-1845936-5435065''
--CONNECTION(
--	ServiceUrl=''https://sellingpartnerapi-na.amazon.com''
--)';

EXEC (@MyQuery) AT [LS_TO_AMAZON_SELLING_PARTNER_SP_API_IN_GATEWAY];

get_order endpoint belongs to Orders table(s), and can therefore be used via those table(s).