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
Parameters
Output Columns
| Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | Description |
|---|---|---|---|---|---|
| AmazonOrderId |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
| OrderItemId |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
| Title |
DT_WSTR
|
nvarchar(300)
|
300 | False |
|
| ASIN |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
| SellerSKU |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
| QuantityOrdered |
DT_I8
|
bigint
|
False |
||
| QuantityShipped |
DT_I8
|
bigint
|
False |
||
| Model |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
| NumberOfItems |
DT_WSTR
|
nvarchar(4)
|
4 | False |
|
| PriceCurrencyCode |
DT_WSTR
|
nvarchar(10)
|
10 | False |
|
| PriceAmount |
DT_WSTR
|
nvarchar(20)
|
20 | False |
|
| TaxCurrencyCode |
DT_WSTR
|
nvarchar(10)
|
10 | False |
|
| TaxAmount |
DT_WSTR
|
nvarchar(20)
|
20 | False |
|
| ResponsibleParty |
DT_WSTR
|
nvarchar(84)
|
84 | False |
|
| IsBuyerRequestedCancel |
DT_WSTR
|
nvarchar(20)
|
20 | False |
|
| BuyerCancelReason |
DT_WSTR
|
nvarchar(80)
|
80 | False |
|
| IsGift |
DT_WSTR
|
nvarchar(20)
|
20 | False |
|
| ConditionSubtypeId |
DT_WSTR
|
nvarchar(40)
|
40 | False |
|
| IsTransparency |
DT_BOOL
|
bit
|
False |
||
| PromotionDiscountCurrencyCode |
DT_WSTR
|
nvarchar(10)
|
10 | False |
|
| PromotionDiscountAmount |
DT_WSTR
|
nvarchar(20)
|
20 | False |
|
| PromotionDiscountTaxCurrencyCode |
DT_WSTR
|
nvarchar(10)
|
10 | False |
|
| PromotionDiscountTaxAmount |
DT_WSTR
|
nvarchar(20)
|
20 | False |
|
| ConditionId |
DT_WSTR
|
nvarchar(20)
|
20 | False |
Input Columns
| Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | 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 component to read data or in API Destination component to read/write data:
Read from OrderItems table using API Destination
This Endpoint belongs to OrderItems table, therefore you cannot work with it directly. Use this table and table-operation pair instead:
ODBC application
Use these SQL queries in your ODBC application data source:
Get orders items
SELECT * FROM get_order_items_allRead 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:
Get orders items
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_order_items_all';
EXEC (@MyQuery) AT [LS_TO_AMAZON_SELLING_PARTNER_SP_API_IN_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).