Reference

Endpoint Get Orders Items (For All Orders **Slow**)

Name

get_order_items_all

Description

Returns the order items information for a specified order search criterial (All orders by Create/Update date, status etc)

Related Tables

OrderItems

Parameters

Parameter Required Options
Name: MarketplaceIds

Label: MarketplaceIds

 YES
Option Value
United States of America ATVPDKIKX0DER
Brazil A2Q3Y263D00KWC
Canada A2EUQ1WTGCTBG2
Mexico A1AM78C64UM0Y8
Spain A1RKKUPIHCS9HS
United Kingdom A1F83G8C2ARO7P
France A13V1IB3VIYZZH
Belgium AMEN7PMS3EDWL
Netherlands A1805IZSGTT6HS
Germany A1PA6795UKMFR9
Italy APJ6JRA9NG5V4
Sweden A2NODRKZP88ZB9
South Africa AE08WJ6YKNBMC
Poland A1C3SOZRARQ6R3
Egypt ARBP9OOSHTCHU
Turkey A33AVAJ2PDY3EV
Saudi Arabia A17E79C6D8DWNP
United Arab Emirates (U.A.E.) A2VIGQ35RCS4UG
India A21TJRUUN4KGV
Singapore A19VAU5U5O7RUS
Australia A39IBJ37TRP1C6
Japan A1VC38T7YXB528
Name: CreatedAfter

Label: CreatedAfter

A UTC date used for selecting orders created after (or at) a specified time. Date format must be UTC - ISO 8601 format (e.g. short date 2017-02-10 -OR- date and time 2017-02-10T12:10:02). If LastUpdatedAfter is not specified. Specifying both CreatedAfter and LastUpdatedAfter returns an error. You can use static values or date functions. Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
Option Value
2023-12-31 2023-12-31
2023-12-31T00:00:00 2023-12-31T00:00:00
now now
now-24h now-24h
today today
yesterday yesterday
yearstart-10y yearstart-10y
monthstart monthstart
monthend monthend
yearstart yearstart
yearend yearend
weekstart weekstart
weekend weekend
yearstart-1y yearstart-1y
yearend-1y yearend-1y
monthstart-1d monthstart-1d
monthend+1d monthend+1d
monthend+1d-1y monthend+1d-1y
Name: CreatedBefore

Label: CreatedBefore

A UTC date used for selecting orders created before (or at) a specified time. Date format must be UTC - ISO 8601 format (e.g. short date 2017-02-10 -OR- date and time 2017-02-10T12:10:02) You can use static values or date functions. Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
Option Value
2023-12-31 2023-12-31
2023-12-31T00:00:00 2023-12-31T00:00:00
now now
now-24h now-24h
today today
yesterday yesterday
yearstart-10y yearstart-10y
monthstart monthstart
monthend monthend
yearstart yearstart
yearend yearend
weekstart weekstart
weekend weekend
yearstart-1y yearstart-1y
yearend-1y yearend-1y
monthstart-1d monthstart-1d
monthend+1d monthend+1d
monthend+1d-1y monthend+1d-1y
Name: LastUpdatedAfter

Label: LastUpdatedAfter

A UTC date used for selecting orders that were last updated after (or at) a specified time. An update is defined as any change in order status, including the creation of a new order. Includes updates made by Amazon and by the seller. Date format must be UTC - ISO 8601 format (e.g. short date 2017-02-10 -OR- date and time 2017-02-10T12:10:02). If CreatedAfter is not specified.Specifying both CreatedAfter and LastUpdatedAfter returns an error.If LastUpdatedAfter is specified, then BuyerEmail and SellerOrderId cannot be specified. You can use static values or date functions. Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
Option Value
2023-12-31 2023-12-31
2023-12-31T00:00:00 2023-12-31T00:00:00
now now
now-24h now-24h
today today
yesterday yesterday
yearstart-10y yearstart-10y
monthstart monthstart
monthend monthend
yearstart yearstart
yearend yearend
weekstart weekstart
weekend weekend
yearstart-1y yearstart-1y
yearend-1y yearend-1y
monthstart-1d monthstart-1d
monthend+1d monthend+1d
monthend+1d-1y monthend+1d-1y
Name: LastUpdatedBefore

Label: LastUpdatedBefore

A UTC date used for selecting orders that were last updated before (or at) a specified time. An update is defined as any change in order status, including the creation of a new order. Includes updates made by Amazon and by the seller. Date format must be UTC - ISO 8601 format (e.g. short date 2017-02-10 -OR- date and time 2017-02-10T12:10:02) You can use static values or date functions. Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
Option Value
2023-12-31 2023-12-31
2023-12-31T00:00:00 2023-12-31T00:00:00
now now
now-24h now-24h
today today
yesterday yesterday
yearstart-10y yearstart-10y
monthstart monthstart
monthend monthend
yearstart yearstart
yearend yearend
weekstart weekstart
weekend weekend
yearstart-1y yearstart-1y
yearend-1y yearend-1y
monthstart-1d monthstart-1d
monthend+1d monthend+1d
monthend+1d-1y monthend+1d-1y
Name: OrderStatuses

Label: OrderStatuses

A list of OrderStatus values. Used to select orders with a current status that matches one of the status values that you specify.
Option Value
Pending Pending
Unshipped Unshipped
PartiallyShipped PartiallyShipped
PendingAvailability PendingAvailability
Shipped Shipped
Canceled Canceled
Unfulfillable Unfulfillable
Name: FulfillmentChannels

Label: FulfillmentChannels

A list that indicates how an order was fulfilled.
Option Value
Fulfilled by Amazon AFN
Fulfilled by the seller MFN
Name: PaymentMethods

Label: PaymentMethods

A list of PaymentMethod values. Used to select orders paid for with the payment methods that you specify.
Option Value
Cash on delivery COD
Convenience store payment CVS
Any payment method other than COD or CVS Other
Name: BuyerEmail

Label: BuyerEmail

The e-mail address of a buyer. Used to select only the orders that contain the specified e-mail address. If BuyerEmail is specified, then FulfillmentChannel, OrderStatus, PaymentMethod, LastUpdatedAfter, LastUpdatedBefore, and SellerOrderId cannot be specified.
Name: SellerOrderId

Label: SellerOrderId

An order identifier that is specified by the seller. Not an Amazon order identifier. Used to select only the orders that match a seller-specified order identifier. If SellerOrderId is specified, then FulfillmentChannel, OrderStatus, PaymentMethod, LastUpdatedAfter, LastUpdatedBefore, and BuyerEmail cannot be specified.
Name: MaxResultsPerPage

Label: MaxResultsPerPage

A number that indicates the maximum number of orders that can be returned per page. Value must be 1 - 100. Default 100.
Name: EasyShipShipmentStatuses

Label: EasyShipShipmentStatuses

A list of EasyShipShipmentStatus values. Used to select Easy Ship orders with statuses that match the specified values. If EasyShipShipmentStatus is specified, only Amazon Easy Ship orders are returned. Possible values: - PendingSchedule (The package is awaiting the schedule for pick-up.) - PendingPickUp (Amazon has not yet picked up the package from the seller.) - PendingDropOff (The seller will deliver the package to the carrier.) - LabelCanceled (The seller canceled the pickup.) - PickedUp (Amazon has picked up the package from the seller.) - DroppedOff (The package is delivered to the carrier by the seller.) - AtOriginFC (The packaged is at the origin fulfillment center.) - AtDestinationFC (The package is at the destination fulfillment center.) - Delivered (The package has been delivered.) - RejectedByBuyer (The package has been rejected by the buyer.) - Undeliverable (The package cannot be delivered.) - ReturningToSeller (The package was not delivered and is being returned to the seller.) - ReturnedToSeller (The package was not delivered and was returned to the seller.) - Lost (The package is lost.) - OutForDelivery (The package is out for delivery.) - Damaged (The package was damaged by the carrier.)
Option Value
PendingSchedule PendingSchedule
PendingPickUp PendingPickUp
PendingDropOff PendingDropOff
LabelCanceled LabelCanceled
PickedUp PickedUp
DroppedOff DroppedOff
AtOriginFC AtOriginFC
AtDestinationFC AtDestinationFC
Delivered Delivered
RejectedByBuyer RejectedByBuyer
Undeliverable Undeliverable
ReturningToSeller ReturningToSeller
ReturnedToSeller ReturnedToSeller
Lost Lost
OutForDelivery OutForDelivery
Damaged Damaged
Name: ElectronicInvoiceStatuses

Label: ElectronicInvoiceStatuses

A list of ElectronicInvoiceStatus values. Used to select orders with electronic invoice statuses that match the specified values. Possible values: - NotRequired (Electronic invoice submission is not required for this order.) - NotFound (The electronic invoice was not submitted for this order.) - Processing (The electronic invoice is being processed for this order.) - Errored (The last submitted electronic invoice was rejected for this order.) - Accepted (The last submitted electronic invoice was submitted and accepted.)
Option Value
NotRequired NotRequired
NotFound NotFound
Processing Processing
Errored Errored
Accepted Accepted
Name: AmazonOrderIds

Label: AmazonOrderIds

Comma separated list of AmazonOrderId values. An AmazonOrderId is an Amazon-defined order identifier, in 3-7-7 format. Max count : 50
Name: ActualFulfillmentSupplySourceId

Label: ActualFulfillmentSupplySourceId

Denotes the recommended sourceId where the order should be fulfilled from.
Name: IsISPU

Label: IsISPU

When true, this order is marked to be picked up from a store rather than delivered.
Name: StoreChainStoreId

Label: StoreChainStoreId

The store chain store identifier. Linked to a specific store in a store chain.

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

This Endpoint belongs to the OrderItems 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)
OrderItems
There are no parameters to configure.
SSIS API Source - Read from table or endpoint

API Destination

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

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)
OrderItems
Select
There are no parameters to configure.
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read Order Items (For All Orders - Slow)

Read order items with search criteria on orders such as CreatedAfter, CreatedBefore, MarketPlaceIds, OrderStatuses, PaymentType and many more. This is slow way of pulling all items for all orders without reading one by one order. 

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

get_order_items_all endpoint belongs to OrderItems 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:

Read Order Items (For All Orders - Slow)

Read order items with search criteria on orders such as CreatedAfter, CreatedBefore, MarketPlaceIds, OrderStatuses, PaymentType and many more. This is slow way of pulling all items for all orders without reading one by one order. 

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

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