Reference

Endpoint Get Order Items (For Single Order)

Name

get_order_items

Description

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

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(50) 50
OrderItemId DT_WSTR nvarchar(50) 50
Title DT_WSTR nvarchar(300) 300
ASIN DT_WSTR nvarchar(50) 50
SellerSKU DT_WSTR nvarchar(50) 50
QuantityOrdered DT_I8 bigint
QuantityShipped DT_I8 bigint
Model DT_WSTR nvarchar(100) 100
NumberOfItems DT_WSTR nvarchar(4) 4
PriceCurrencyCode DT_WSTR nvarchar(10) 10
PriceAmount DT_WSTR nvarchar(20) 20
TaxCurrencyCode DT_WSTR nvarchar(10) 10
TaxAmount DT_WSTR nvarchar(20) 20
ResponsibleParty DT_WSTR nvarchar(84) 84
IsBuyerRequestedCancel DT_WSTR nvarchar(20) 20
BuyerCancelReason DT_WSTR nvarchar(80) 80
IsGift DT_WSTR nvarchar(20) 20
ConditionSubtypeId DT_WSTR nvarchar(40) 40
IsTransparency DT_BOOL bit
PromotionDiscountCurrencyCode DT_WSTR nvarchar(10) 10
PromotionDiscountAmount DT_WSTR nvarchar(20) 20
PromotionDiscountTaxCurrencyCode DT_WSTR nvarchar(10) 10
PromotionDiscountTaxAmount DT_WSTR nvarchar(20) 20
ConditionId DT_WSTR nvarchar(20) 20
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

API Source - Amazon Selling Partner (SP-API)
Read and write Amazon Selling Partner (SP-API) data effortlessly. Integrate, manage, and automate listings, orders, payments, and reports — almost no coding required.
Amazon Selling Partner (SP-API)
Get Order Items (For Single Order)
Optional Parameters
AmazonOrderId
SSIS API Source - Read from table or endpoint

API Destination

API Destination - Amazon Selling Partner (SP-API)
Read and write Amazon Selling Partner (SP-API) data effortlessly. Integrate, manage, and automate listings, orders, payments, and reports — almost no coding required.
Amazon Selling Partner (SP-API)
Get Order Items (For Single Order)
Optional Parameters
AmazonOrderId
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

List order items for a single order

<p>Read all order items for a single order. Specify the order ID.</p> 

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

Sandbox: list order items (fake data)

<p>Read sandbox order items with fake data. Use a sandbox order ID and the sandbox service URL for testing.</p> 

SELECT *
FROM get_order_items
--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'
)

SQL Server

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

List order items for a single order

<p>Read all order items for a single order. Specify the order ID.</p> 

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

EXEC (@MyQuery) AT [LS_TO_AMAZON_SELLING_PARTNER_SP_API_IN_GATEWAY];

Sandbox: list order items (fake data)

<p>Read sandbox order items with fake data. Use a sandbox order ID and the sandbox service URL for testing.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_order_items
--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];