Get Orders

Parameters

Output Columns

Input Columns

Examples

SSIS

Use Amazon Selling Partner (SP-API) Connector in API Source component to read data or in API Destination component to read/write data:

Read from Orders table using API Destination

This Endpoint belongs to Orders table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

Amazon Selling Partner (SP-API)

Orders

Select

ODBC application

Use these SQL queries in your ODBC application data source:

Get orders

SELECT * FROM get_orders

Read Orders

Read orders with search criteria such as CreatedAfter, CreatedBefore, MarketPlaceIds, OrderStatuses, PaymentType and many more

SELECT * FROM Orders
--WHERE AmazonOrderId='902-1845936-5435065'		
WITH(
	  CreatedAfter='1900-01-01T00:00:00'
--	, CreatedBefore='1900-01-01T00:00:00'
--	, LastUpdatedAfter='1900-01-01T00:00:00'
--	, LastUpdatedBefore='1900-01-01T00:00:00'
--	, OrderStatuses='Pending~Unshipped~PartiallyShipped~PendingAvailability~Shipped~Canceled~Unfulfillable'
--	, MarketplaceIds='ATVPDKIKX0DER~A2Q3Y263D00KWC~A2EUQ1WTGCTBG2'
--	, FulfillmentChannels='AFN~MFN'
--	, PaymentMethods='COD~CVS~Other'
--	, AmazonOrderIds='1111111,222222,333333'
)
--CONNECTION(
--	ServiceUrl='https://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'
--)

Sandbox - Read Orders (Fake data for testing)

Read orders which has fake values (sandbox data)

SELECT *
FROM Orders
--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(
	  CreatedAfter='TEST_CASE_200' 
	  --CreatedAfter='TEST_CASE_200_NEXT_TOKEN'
	, MarketplaceIds='ATVPDKIKX0DER'
)
CONNECTION(
	ServiceUrl='https://sandbox.sellingpartnerapi-na.amazon.com'
)

SQL Server

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

Get orders

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_orders';

EXEC (@MyQuery) AT [LS_TO_AMAZON_SELLING_PARTNER_SP_API_IN_GATEWAY];

Read Orders

Read orders with search criteria such as CreatedAfter, CreatedBefore, MarketPlaceIds, OrderStatuses, PaymentType and many more

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Orders
--WHERE AmazonOrderId=''902-1845936-5435065''		
WITH(
	  CreatedAfter=''1900-01-01T00:00:00''
--	, CreatedBefore=''1900-01-01T00:00:00''
--	, LastUpdatedAfter=''1900-01-01T00:00:00''
--	, LastUpdatedBefore=''1900-01-01T00:00:00''
--	, OrderStatuses=''Pending~Unshipped~PartiallyShipped~PendingAvailability~Shipped~Canceled~Unfulfillable''
--	, MarketplaceIds=''ATVPDKIKX0DER~A2Q3Y263D00KWC~A2EUQ1WTGCTBG2''
--	, FulfillmentChannels=''AFN~MFN''
--	, PaymentMethods=''COD~CVS~Other''
--	, AmazonOrderIds=''1111111,222222,333333''
)
--CONNECTION(
--	ServiceUrl=''https://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];

Sandbox - Read Orders (Fake data for testing)

Read orders which has fake values (sandbox data)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM Orders
--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(
	  CreatedAfter=''TEST_CASE_200'' 
	  --CreatedAfter=''TEST_CASE_200_NEXT_TOKEN''
	, MarketplaceIds=''ATVPDKIKX0DER''
)
CONNECTION(
	ServiceUrl=''https://sandbox.sellingpartnerapi-na.amazon.com''
)';

EXEC (@MyQuery) AT [LS_TO_AMAZON_SELLING_PARTNER_SP_API_IN_GATEWAY];