Amazon Marketplace v2 Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8858 of the connector.

Get Started¶

Amazon Marketplace Version Support

The connector leverages the Amazon Marketplace API to enable bidirectional access to Amazon Marketplace Web Services (MWS) API and Amazon Selling Partner (SP) API.

Establish a Connection¶

Connect to Amazon Marketplace¶

Amazon MWS API vs Selling Partner API¶

Amazon MWS (Marketplace Web Services) API is the older API for the Amazon Marketplace while Selling Partner (SP) API provides number of improvements over MWS API including JSON-based REST API design standards and OAuth 2.0. SP-API includes all functionality available in Amazon MWS API.

You may specify which API to connect to by setting Schema. Please be aware that each API has different available connection options as described below.

Connect to Selling Partner API¶

When using the Amazon Selling Partner API to connect to the Amazon Marketplace, the following properties are required:

• Schema: Set this to SellingPartner.
• InitiateOAuth: Set this to GETANDREFRESH.
• Marketplace: Set this to the Marketplace region that you are registered to sell in.

Also, you can use the SellingPartner property to choose Seller or Vendor authentication.

Connect to the MWS API¶

When using the Amazon MWS API to connect to the Amazon Marketplace, SellerId, Marketplace, Marketplace are required connection properties. Set Schema to Marketplace.

Configure Access to Amazon Marketplace¶

To connect to Amazon Marketplace first authorize developer. To do so follow the steps below:

• Using the MWS developer id: 195280669143.
• Go to the Manage your apps page in Seller Central and log into your Amazon seller account as the primary account holder.
• Click the Authorize new developer button and follows the authorization workflow using the developer ID provided by the connector.

Or you can go to Amazon Marketplace Driver and click Authorize Now on the right panel.

Obtain the MWS Auth Token¶

To obtain the MWS Auth Token, follow the steps below:

• Go to the Manage your apps page in Seller Central and log into your Amazon seller account as the primary account holder.
• Find the App.
• Under the MWS Auth Token Column click View.

Obtain the Seller ID¶

To obtain the Seller ID follow the steps below:

• Login to your seller account.
• Select Settings, then Account Info on upper right of screen.
• Under Business Information select "Your Merchant Token".

Authenticate to Amazon Marketplace¶

Amazon Marketplace Accounts¶

Set the following connection properties to connect:

• SellerId: Set the Seller ID of Amazon marketplace web service settings.
• Marketplace: Set the Amazon marketplace location (for example, United States).
• Schema: Set Schema to Marketplace.

OAuth¶

Amazon Marketplace uses the OAuth authentication standard.

To authenticate using OAuth, you must either use the embedded application or create a new custom OAuth app. The embedded application supports desktop applications and headless machines. Web applications require that you create a custom OAuth application.

You can use a custom OAuth application to authenticate with a service account or a user account. See Creating a Custom OAuth App for more information.

Downloading Embedded Credentials¶

Because Amazon Marketplace requires that embedded credentials rotate every six months, credentials are hosted on oa.cdata.com. If you do not specify custom credentials, the embedded credentials are downloaded from our web service and saved in the location specified in OAuthClientLocation by default. NOTE:Make sure your firewall does not block oa.cdata.com.

Desktop Apps¶

You can use the embedded application or create a custom OAuth application. The key difference is that you must set additional connection properties if you use a custom application.

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• Marketplace: Set this to the Marketplace region that you are registered to sell in.
• AppId: Set this to the application ID for the Selling Partner application you created.
• Schema: Set this to SellingPartner to connect to SP-API.
• AWSAccessKey: This is the Access Key tied to the AWS user that is associated with the OAuthClientId.
• AWSSecretKey: This is the Secret Key tied to the AWS user that is associated with the OAuthClientId.
• OAuthClientId (custom applications only): Set this to the client ID assigned when you registered your app.
• OAuthClientSecret (custom applications only): Set this to the client secret assigned when you registered your app.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Refreshes the access token when it expires.
3. Saves OAuth values in OAuthSettingsLocation. These values persist across connections.

Amazon Marketplace Data Retention Policy¶

For security, Amazon Marketplace restricts access to PII (Personally Identifiable Information). You can only retain PII for 30 days after order delivery and only for the purpose of, and as long as is necessary to

• fulfill orders
• calculate and remit taxes
• produce tax invoices
• meet legal requirements, including tax or regulatory requirements.

If you are required by law to retain archival copies of PII for tax or other regulatory purposes, you must store PII as a "cold" or offline encrypted backup (e.g., not available for immediate or interactive use).

This means, for example, that the Amazon Marketplace driver no longer displays customer shipping address information after 30 days. See the Amazon documentation for more information:

Amazon Marketplace Data Protection Policy

Create a Custom OAuth App¶

You can use a custom OAuth app to authenticate a service account or a user account.

Create an OAuth App for User Account Authentication¶

Follow the procedure below to register an app and obtain the OAuthClientId and OAuthClientSecret.

Create a Custom OAuth App¶

1. Log into the Selling Partner Console and open Develop Apps from Apps & Services.

2. Click Add new app client.

3. Provide name for the app and select SP-API as the API type.

4. Provide IAM ARN for the AWS account and Select Sellers.

5. Provide OAuth Login URI and OAuth Redirect URI values. After creating the app, the OAuthClientId and OAuthClientSecret are displayed under LWA credentials.

For a more in depth reading of how to create a custom OAuth app and configure the IAM role, please see the Amazon Selling Partner Guide.

Important Notes¶

Configuration Files and Their Paths¶

• All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

This section details a selection of advanced features of the Amazon Marketplace connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Amazon Marketplace and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views¶

The Jitterbit Connector for Amazon Marketplace allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File¶

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Orders WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements¶

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View¶

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View¶

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View¶

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views¶

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views¶

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration¶

Customize the SSL Configuration¶

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model¶

The Jitterbit Connector for Amazon Marketplace models Amazon Marketplace objects as relational tables and views. An Amazon Marketplace object has relationships to other objects; in the tables, these relationships are expressed through foreign keys. The following sections show the available API objects and provide more information on executing SQL to Amazon Marketplace APIs.

Schemas for most database objects are defined in simple, text-based configuration files.

Using Marketplace API

See Marketplace Data Model for the available entities in the Marketplace API.

Using SellingPartner API

See Selling Partner Data Model for the available entities in the Selling Partner API.

Marketplace Data Model¶

The Jitterbit Connector for Amazon Marketplace models the Amazon Marketplace API as relational views, and stored procedures.

To use Marketplace Data Model, simply set Schema to Marketplace.

Views¶

Views are tables that cannot be modified, such as Orders, Products. Typically, model data that is read-only and cannot be updated are shown as views.

Stored Procedures¶

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information

in the data source.

Using Reports¶

Created reports can be exposed as views by setting IncludeReports connection property to true. Reports can be created with the RequestReport stored procedure.

After a report has been created and finished processing with a status _DONE_ it will be exposed as a view with the name "Report{ReportType}{ReportID}".

Only Tab-Delimited reports can be used as views. You can find a full list of reports here: Report Types.

In order to avoid API throttling, it is suggested to use the CreateSchema stored procedure to create report table schemas.

Views¶

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Jitterbit Connector for Amazon Marketplace Views¶

FeedSubmissionList¶

The GetFeedSubmissionList operation returns a list of feed submissions.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM FeedSubmissionList WHERE FeedSubmissionId = '50377017868'

SELECT * FROM FeedSubmissionList WHERE FeedType = '_POST_PRODUCT_DATA_

SELECT * FROM FeedSubmissionList WHERE SubmittedDate = '2018-12-03T09:42:06'

SELECT * FROM FeedSubmissionList WHERE FeedProcessingStatus = '_DONE_'

Columns¶

FeedSubmissionResult¶

The GetFeedSubmissionResult operation returns the feed processing report for a specific feed.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM FeedSubmissionResult WHERE FeedSubmissionId = '50377017868'

Columns¶

InboundShipmentItems¶

Returns a list of items in a specified inbound shipment.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM InboundShipments WHERE ShipmentId = '503-9993250-1405404'

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

InboundShipments¶

Returns a list of inbound shipments based on criteria that you specify.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM InboundShipments WHERE ShipmentStatus = 'Working'

SELECT * FROM InboundShipments WHERE ShipmentStatus IN ('Working', 'SHIPPED', 'IN_TRANSIT')

SELECT * FROM InboundShipments WHERE ShipmentId = '503-9993250-1405404'

SELECT * FROM InboundShipments WHERE LastUpdatedDate >= '2016-12-12'

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

InventorySupply¶

Returns information about the availability of inventory that a seller has in Amazon's fulfillment network and in current inbound shipments. You can check the current availability status for your Fulfillment by Amazon inventory as well as discover when availability status changes.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM InventorySupply WHERE SellerSKU = 'Stest'

SELECT * FROM InventorySupply WHERE  QueryStartDateTime = '2016-03-16'

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

OrderItems¶

Returns order items based on the Amazon Order ID that you specify.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. OrderItems table supports server side filtering with = and IN operators for AmazonOrderId field. For example, the following query is processed server side:

SELECT * FROM OrderItems WHERE AmazonOrderId = '503-9993250-1405404'

SELECT * FROM OrderItems WHERE AmazonOrderId IN ('503-9993250-1405404', '503-9993250-1405404')

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Orders¶

Returns orders created or updated during a time frame that you specify.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM Orders WHERE AmazonOrderId = '249-7638334-8161403'

SELECT * FROM Orders WHERE LastUpdateDate >= '2016-12-12'

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12'

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12' AND OrderStatus = 'Canceled'

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12' AND FulfillmentChannel = 'MFN'

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12' AND BuyerEmail = 'example@example.com'

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12' AND PaymentMethod = 'Other'

Columns¶

Products¶

The Products Table helps you get information to match your products to existing product listings on Amazon Marketplace websites.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM Products WHERE SearchTerms = '249-7638334-8161403'

SELECT * FROM Products WHERE idType = 'SellerSKU' AND idlist = '{SellerSku code}'

SELECT * FROM Products WHERE idType = 'ASIN' AND idlist = '{ASIN code}'

SELECT * FROM Products WHERE idType = 'GCID' AND idlist = '{GCID code}'

SELECT * FROM Products WHERE idType = 'UPC' AND idlist = '{UPC code}'

SELECT * FROM Products WHERE idType = 'EAN' AND idlist = '{EAN code}'

SELECT * FROM Products WHERE idType = 'ISBN' AND idlist = '{ISBN code}'

SELECT * FROM Products WHERE idType = 'JAN' AND idlist = '{JAN code}'

SELECT * FROM Products WHERE idType = 'JAN' AND idlist IN ('{JAN code}', '{ISBN code}', '{GCID code}')

Columns¶

ReportList¶

Usage information for the operation ReportList.rsd.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM ReportList WHERE ReportType IN '_GET_MERCHANT_LISTINGS_INACTIVE_DATA_'

SELECT * FROM ReportList WHERE ReportRequestId = '50566018029'

SELECT * FROM ReportList WHERE ReportRequestId IN ('50566018029', '50564018026')

SELECT * FROM ReportList WHERE AvailableDate > '2016-12-12' AND AvailableDate < '2018-11-12 12:00:00'

SELECT * FROM ReportList WHERE AvailableDate >= '2016-12-12' AND Acknowledged = 'true'

Columns¶

ReportRequestList¶

Usage information for the operation ReportRequestList.rsd.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM ReportRequestList WHERE ReportType = '_GET_MERCHANT_LISTINGS_INACTIVE_DATA_'

SELECT * FROM ReportRequestList WHERE ReportType IN ('_GET_MERCHANT_LISTINGS_INACTIVE_DATA_', '_GET_V2_SETTLEMENT_REPORT_DATA_FLAT_FILE_')

SELECT * FROM ReportRequestList WHERE ReportRequestId = '50566018029'

SELECT * FROM ReportRequestList WHERE ReportRequestId IN ('50566018029', '50564018026')

SELECT * FROM ReportRequestList WHERE RequestedDate > '2016-12-12'

SELECT * FROM ReportRequestList WHERE RequestedDate > '2016-12-12' AND RequestedDate > '2018-12-12 13:00'

SELECT * FROM ReportRequestList WHERE RequestedDate <= '2016-12-12' AND ReportProcessingStatus = '_DONE_'

SELECT * FROM ReportRequestList WHERE ReportProcessingStatus IN ('_DONE_', '_SUBMITTED_', '_CANCELLED_')

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Stored Procedures¶

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Amazon Marketplace.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Amazon Marketplace, along with an indication of whether the procedure succeeded or failed.

Jitterbit Connector for Amazon Marketplace Stored Procedures¶

CancelFeed¶

The CancelFeed cancels one or more feed submissions and returns a count of the canceled feed submissions and the feed submission information. Note that if you do not specify a FeedSubmmissionId, all feed submissions are canceled.

Input¶

Result Set Columns¶

CancelReportRequests¶

CancelReportRequests operation cancels one or more report requests and returns the number of report requests canceled and report request information.

Input¶

Result Set Columns¶

CreateReportSchema¶

Creates a schema file based on the specified report.

CreateReportSchema¶

CreateReportSchema creates a schema file based on the specified report.

This schema adds a table to your existing list that corresponds with the results of your report, which can then be queried like other tables.

(Reports from the Amazon Marketplace are not modeled by connector as queryable tables by default.)

The generated schema file outlines the metadata for the report, such as columns and column data types. You can edit the file to adjust data types, rename columns, and include or exclude columns.

Updating a Report Schema

In the following example, the SP CreateReportSchema creates a new report using TestReportTest1 as a base template. It appends new columns to TestReportTest1 and creates a new report, named TestReport2. The new report is saved as ...\TestReportTest2.rsd.

EXECUTE [CreateReportSchema]
    [ReportName] = "TestReportTest2",
    [CustomFieldIdsPrimitive] = "1459925,1459928",
    [CustomFieldIdsDropdown] = "1469785",
    [CustomDimensionKeyIds] = "13539564",
    [BaseReportName] = "TestReportTest1",
[FileName] = "...\TestReportTest2.rsd"

Input

Result Set Columns

CreateSchema¶

Creates a schema file for the specified table or view.

CreateSchema¶

Creates a local schema file (.rsd) from an existing table or view in the data model.

The schema file is created in the directory set in the Location connection property when this procedure is executed. You can edit the file to include or exclude columns, rename columns, or adjust column datatypes.

The connector checks the Location to determine if the names of any .rsd files match a table or view in the data model. If there is a duplicate, the schema file will take precedence over the default instance of this table in the data model. If a schema file is present in Location that does not match an existing table or view, a new table or view entry is added to the data model of the connector.

Input

Result Set Columns

GetReport¶

Creates and/or returns data for a specific report.

Execute¶

We can use GetReport stored procedure in two ways:
To create and download a report in which case you must at least set ReportType, StartDate and DownloadPath attributes. The stored procedure will wait until the report is processed server side.

EXEC GetReport
@ReportType = '_GET_FLAT_FILE_ORDERS_DATA_',
@StartDate = '08-27-2020',
@DownloadPath = 'C:\Tests\AmazonMarketplaceTest'

To download an existing report in which case you must at least set ReportID and DownloadPath attributes for ex:

EXEC GetReport
@ReportID = '50815018501',
@DownloadPath = 'C:\Tests\AmazonMarketplaceTest'

Indexed parameters facilitate providing multiple instances a single parameter as inputs for the procedure.

Suppose there is an input parameter named Param#. To input multiple instances of an indexed parameter like this, execute:

EXEC ProcedureName Param#1 = "value1", Param#2 = "value2", Param#3 = "value3"

In the table below, indexed parameters are denoted with a # character at the end of their names.

Input

Result Set Columns

RequestReport¶

The RequestReport operation creates a report request.

Indexed parameters facilitate providing multiple instances a single parameter as inputs for the procedure.

Suppose there is an input parameter named Param#. To input multiple instances of an indexed parameter like this, execute:

EXEC ProcedureName Param#1 = "value1", Param#2 = "value2", Param#3 = "value3"

In the table below, indexed parameters are denoted with a # character at the end of their names.

Input¶

Result Set Columns¶

SubmitImageFeed¶

The Image feed allows you to upload various images for a product. Amazon can display several images for each product.

Execute¶

The stored procedure accepts two aggregated formats: #TEMP tables and XML aggregate

For #TEMP tables

You must include in your query:

INSERT INTO ImageFeedAggregate#TEMP (SKU, ImageType, ImageLocation, OperationType) VALUES ('15700', 'Main', 'https://www.cdata.com/ui/img/home/adapters.png', 'Update')

Then you execute the procedure by specifying the value of ImageFeedAggregate with the name of #TEMP table used ImageFeedAggregate#TEMP.

EXEC SubmitImageFeed ProductImageFeedAggregate = 'ImageFeedAggregate#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. For XML aggregate

The XML aggregate must follow the API structure (https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_4_1/ProductImage.xsd):

<Message>
<MessageID>1</MessageID>
<OperationType>Update</OperationType>
<ProductImage>
<SKU>15700</SKU>
<ImageType>Main</ImageType>
<ImageLocation>https://www.cdata.com/ui/img/home/adapters.png</ImageLocation>
</ProductImage>
</Message>

EXEC SubmitImageFeed ProductImageFeedAggregate = '...(the above XML)...'

Input

Result Set Columns

SubmitInventoryFeed¶

The Inventory feed allows you to update inventory quantities (stock levels) for your items.

Execute¶

The stored procedure accepts two aggregated formats: #TEMP tables and XML aggregate

For #TEMP tables

You must include in your query:

INSERT INTO InventoryFeed#TEMP (SKU, Quantity, FulfillmentLatency) VALUES ('15700', 11, '2')

INSERT INTO InventoryFeed#TEMP (SKU, Quantity, FulfillmentLatency) VALUES ('99987867', 10, '2')

Then you execute the procedure by specifying the value of InventoryFeedAggregate with the name of #TEMP table used InventoryFeed#TEMP.

EXEC SubmitInventoryFeed InventoryFeedAggregate = 'InventoryFeed#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. For XML aggregate

The XML aggregate must follow the API structure (https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_4_1/Inventory.xsd):

<Message>
<MessageID>1</MessageID>
<Inventory>
<SKU>15700</SKU>
<Quantity>11</Quantity>
<FulfillmentLatency>2</FulfillmentLatency>
</Inventory>
</Message>
<Message>
<MessageID>2</MessageID>
<Inventory>
<SKU>99987867</SKU>
<Quantity>10</Quantity>
<FulfillmentLatency>2</FulfillmentLatency>
</Inventory>
</Message>

EXEC SubmitInventoryFeed InventoryFeedAggregate = '...(the above XML)...';

Input

Result Set Columns

SubmitOrderAcknowledgementFeed¶

The Order Acknowledgment feed allows you to acknowledge your success or failure with downloading an order.

Execute¶

The stored procedure accepts two aggregated formats: #TEMP tables and XML aggregate

For #TEMP tables

You must include in your query:

INSERT INTO OrderAcknowledgementFeedAggregate#TEMP (AmazonOrderId, StatusCode, AmazonOrderItemCode, CancelReason) VALUES ('249-6070298-2783041', 'Failure', '25959136016214', 'NoInventory')

Then you execute the procedure by specifying the value of OrderAcknowledgementFeedAggregate with the name of #TEMP table used OrderAcknowledgementFeedAggregate#TEMP.

EXEC SubmitOrderAcknowledgementFeed OrderAcknowledgementFeedAggregate = 'OrderAcknowledgementFeedAggregate#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. For XML aggregate

The XML aggregate must follow the API structure (https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_4_1/OrderAcknowledgement.xsd):

<Message>
<MessageID>1</MessageID>
<OrderAcknowledgement>
<AmazonOrderID>249-6070298-2783041</AmazonOrderID>
<StatusCode>Failure</StatusCode>
<Item>
<AmazonOrderItemCode>25959136016214</AmazonOrderItemCode>
<CancelReason>NoInventory</CancelReason>
</Item>
</OrderAcknowledgement>
</Message>

EXEC SubmitOrderAcknowledgementFeed OrderAcknowledgementFeedAggregate = '...(the above XML)...'

Input

Result Set Columns

SubmitOrderAdjustmentFeed¶

The Order Adjustment feed allows you to issue a refund (adjustment) for an order. You must provide a reason for the adjustment, such as Customer Return, and the adjustment amount, broken down by price component (principle, shipping, tax, and so on).

Execute¶

The stored procedure accepts two aggregated formats: #TEMP tables and XML aggregate

For #TEMP tables

You must include in your query:

INSERT INTO ItemPriceAdjustmentsAggregate#TEMP (ItemPriceAdjustmentsComponentType, ItemPriceAdjustmentsComponentAmount, ItemPriceAdjustmentsComponentAmountCurrency) VALUES ('Shipping', '150', 'JPY')

INSERT INTO AdjustedItemAggregate#TEMP (AdjustedItemAmazonOrderItemCode, AdjustedItemAdjustmentReason, ItemPriceAdjustmentsAggregate) VALUES ('25959136016214', 'CustomerReturn', 'ItemPriceAdjustmentsAggregate#TEMP')

INSERT INTO OrderAdjustmentFeedAggregate#TEMP (AmazonOrderID, OperationType, AdjustedItemAggregate) VALUES ('503-0717426-9080645', 'Update', 'AdjustedItemAggregate#TEMP')

Then you execute the procedure by specifying the value of OrderAdjustmentFeedAggregate with the name of #TEMP table used OrderAdjustmentFeedAggregate#TEMP.

EXEC SubmitOrderAdjustmentFeed OrderAdjustmentFeedAggregate = 'OrderAdjustmentFeedAggregate#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. For XML aggregate

The XML aggregate must follow the API structure (https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_4_1/OrderAdjustment.xsd):

<Message>
<MessageID>1</MessageID>
<OperationType>Update</OperationType>
<OrderAdjustment>
<AmazonOrderID>503-0717426-9080645</AmazonOrderID>
<AdjustedItem>
<AmazonOrderItemCode>25959136016214</AmazonOrderItemCode>
<AdjustmentReason>CustomerReturn</AdjustmentReason>
<ItemPriceAdjustments>
<Component>
<Type>Shipping</Type>
<Amount currency="JPY">150</Amount>
</Component>
</ItemPriceAdjustments>
</AdjustedItem>
</OrderAdjustment>
</Message>

EXEC SubmitOrderAdjustmentFeed OrderAdjustmentFeedAggregate = '...(the above XML)...'

Input

Result Set Columns

SubmitOrderFulfillmentFeed¶

The Order Fulfillment feed allows your system to update Amazon's system with order fulfillment information.

Execute¶

The stored procedure accepts two aggregated formats: #TEMP tables and XML aggregate

For #TEMP tables

You must include in your query:

INSERT INTO ItemAggregate#TEMP (ItemAmazonOrderItemCode, ItemQuantity) VALUES ('25959136016214', '1');

INSERT INTO OrderFulfillmentFeedAggregate#TEMP (AmazonOrderID, FulfillmentDate, FulfillmentDataCarrierName, FulfillmentDataShippingMethod, FulfillmentDataShipperTrackingNumber, ItemAggregate) VALUES ('249-6070298-2783041', '2017-02-01T00:00:00Z', ' Delivery Company', 'Normal Delivery', '1223525345234', 'ItemAggregate#TEMP')

Then you execute the procedure by specifying the value of OrderFulfillmentFeedAggregate with the name of #TEMP table used OrderFulfillmentFeedAggregate#TEMP.

EXEC SubmitOrderFulfillmentFeed OrderFulfillmentFeedAggregate = 'OrderFulfillmentFeedAggregate#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. For XML aggregate

The XML aggregate must follow the API structure (https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_4_1/OrderFulfillment.xsd):

<Message>
<MessageID>1</MessageID>
<OrderFulfillment>
<AmazonOrderID>249-6070298-2783041</AmazonOrderID>
<FulfillmentDate>2017-02-01T00:00:00Z</FulfillmentDate>
<FulfillmentData>
<CarrierName> Delivery Company</CarrierName>
<ShippingMethod>Normal Delivery</ShippingMethod>
<ShipperTrackingNumber>1223525345234</ShipperTrackingNumber>
</FulfillmentData>
<Item>
<AmazonOrderItemCode>25959136016214</AmazonOrderItemCode>
<Quantity>1</Quantity>
</Item>
</OrderFulfillment>
</Message>

EXEC SubmitOrderFulfillmentFeed OrderFulfillmentFeedAggregate = '...(the above XML)...'

Input

Result Set Columns

SubmitOverrideFeed¶

The Override feed allows you to set an exception to your account-level shipping settings for an individual product (SKU).

Execute¶

The stored procedure accepts two aggregated formats: #TEMP tables and XML aggregate

For #TEMP tables

You must include in your query:

INSERT INTO ShippingOverrideAggregate#TEMP (ShipOption, IsShippingRestricted) VALUES ('Std JP Kanto8', 'true')

INSERT INTO ShippingOverrideAggregate#TEMP (ShipOption, Type, ShipAmount, ShipAmountCurrency) VALUES ('Std JP Kantoa8', 'Exclusive', '400.0', 'JPY')

INSERT INTO OverrideFeedAggregate#TEMP (SKU, OperationType, ShippingOverrideAggregate) VALUES ('15700', 'Update', 'ShippingOverrideAggregate#TEMP')

Then you execute the procedure by specifying the value of OverrideFeedAggregate with the name of #TEMP table used OverrideFeedAggregate#TEMP.

EXEC SubmitOverrideFeed OverrideFeedAggregate = 'OverrideFeedAggregate#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. For XML aggregate

The XML aggregate must follow the API structure (https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_4_1/Override.xsd):

<Message>
<MessageID>1</MessageID>
<OperationType>Update</OperationType>
<Override>
<SKU>15700</SKU>
<ShippingOverride>
<ShipOption>Std JP Kanto8</ShipOption>
<IsShippingRestricted>true</IsShippingRestricted>
</ShippingOverride>
<ShippingOverride>
<ShipOption>Std JP Kantoa8</ShipOption>
<Type>Exclusive</Type>
<ShipAmount currency="JPY">400.0</ShipAmount>
</ShippingOverride>
</Override>
</Message>

EXEC SubmitOverrideFeed OverrideFeedAggregate = '...(the above XML)...'

Input

Result Set Columns

SubmitPriceFeed¶

The Price feed allows you to set the current price and sale price (when applicable) for an item.

Execute¶

The stored procedure accepts two aggregated formats: #TEMP tables and XML aggregate

For #TEMP tables

You must include in your query:

INSERT INTO PriceFeedAggregate#TEMP (SKU, StandardPrice, StandardPriceCurrency, MAP, MAPCurrency, SalePrice, SalePriceCurrency, SaleStartDate, SaleEndDate) VALUES ('15700', '134', 'JPY', '100', 'JPY', '150', 'JPY', '2017-02-01T00:00:00Z', '2020-02-01T00:00:00Z')

INSERT INTO PriceFeedAggregate#TEMP (SKU, StandardPrice, StandardPriceCurrency, MAP, MAPCurrency, SalePrice, SalePriceCurrency, SaleStartDate, SaleEndDate) VALUES ('99987867', '134', 'JPY', '100', 'JPY', '150', 'JPY', '2017-02-01T00:00:00Z', '2020-02-01T00:00:00Z')

Then you execute the procedure by specifying the value of PriceFeedAggregate with the name of #TEMP table used PriceFeedAggregate#TEMP.

EXEC SubmitPriceFeed PriceFeedAggregate = 'PriceFeedAggregate#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. For XML aggregate

The XML aggregate must follow the API structure (https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_4_1/Price.xsd):

<Message>
<MessageID>1</MessageID>
<Price>
<SKU>15700</SKU>
<StandardPrice currency="JPY">134</StandardPrice>
<MAP currency="JPY">100</MAP>
<Sale>
<StartDate>2017-02-01T00:00:00Z</StartDate>
<EndDate>2020-02-01T00:00:00Z</EndDate>
<SalePrice currency="JPY">150</SalePrice>
</Sale>
</Price>
</Message>
<Message>
<MessageID>2</MessageID>
<Price>
<SKU>99987867</SKU>
<StandardPrice currency="JPY">134</StandardPrice>
<MAP currency="JPY">100</MAP>
<Sale>
<StartDate>2017-02-01T00:00:00Z</StartDate>
<EndDate>2020-02-01T00:00:00Z</EndDate>
<SalePrice currency="JPY">150</SalePrice>
</Sale>
</Price>
</Message>

EXEC SubmitPriceFeed PriceFeedAggregate = '...(the above XML)...'

Input

Result Set Columns

SubmitProductFeed¶

The Product feed contains descriptive information about the products in your catalog. This information allows Amazon to build a record and assign a unique identifier known as an ASIN (Amazon Standard Item Number) to each product.

Execute¶

The Driver Uses #TEMP tables as fields for aggregate information entered.

SubmitProductFeed Has Two main aggregates that need to be entered.

• ProductDescriptionDataAggregate
• ProductDataAggregate

You must include in your query :

ProductDescriptionDataAggregate information needs to be entered using ProductDescriptionDataAggregate#TEMP temp table.

INSERT INTO ProductDescriptionDataAggregate#TEMP (Title, Brand, Description, BulletPoint, Manufacturer) VALUES ('test Title', 'test Brand', 'test Description', 'test BulletPoint', 'test Manufacturer')

ProductDataAggregate info varies by the Category used. The List of all XSD provided by amazon can be found here https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_1\_9/Product.xsd

This example will be using the Sports category https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_1\_9/Sports.xsd

To add a valid ProductData Sports.xsd must be followed thoroughly and the xml aggregate must be created correctly. This example has the following product Data

• ProductType = SportingGoods
• Parentage = Parent
• VariationTheme = Size (Based on the ProductType selected some of the VariationTheme will be invalid. In these example the Design variation is invalid.)
• Size = S
• MaterialComposition = Metal

By following the order of Sports.xsd XSD file the aggregate is :

<Sports>
    <ProductType>SportingGoods</ProductType>
    <VariationData>
        <Parentage>parent</Parentage>
        <VariationTheme>Size</VariationTheme>
        <Size>S</Size>
    </VariationData>
    <MaterialComposition>Metal</MaterialComposition>
</Sports>

This Aggregate is the value of ProductDataAggregate.

INSERT INTO ProductFeedAggregate#TEMP (SKU, ProductDescriptionDataAggregate, ProductDataAggregate) VALUES ('test SKU', 'ProductDescriptionDataAggregate#TEMP', '<Sports>
    <ProductType>SportingGoods</ProductType>
    <VariationData>
        <Parentage>parent</Parentage>
        <VariationTheme>Size</VariationTheme>
        <Size>S</Size>
    </VariationData>
    <MaterialComposition>Metal</MaterialComposition>
</Sports>')

Then you execute the procedure by specifying the value of ProductFeedAggregate with the name of #TEMP table used ProductFeedAggregate#TEMP.

EXEC SubmitProductFeed ProductFeedAggregate = 'ProductFeedAggregate#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. Input

Result Set Columns

SubmitRelationshipFeed¶

The Relationship feed allows you to set up optional relationships between items in your catalog.

Execute¶

The stored procedure accepts two aggregated formats: #TEMP tables and XML aggregate

For #TEMP tables

You must include in your query:

INSERT INTO RelationAggregate#TEMP (Type, SKU) VALUES ('Variation', '99987867')

INSERT INTO RelationAggregate#TEMP (Type, SKU) VALUES ('Variation', '99987867')

INSERT INTO RelationshipFeedAggregate#TEMP (ParentSKU, RelationAggregate) VALUES ('15700', 'RelationAggregate#TEMP')

Then you execute the procedure by specifying the value of RelationshipFeedAggregate with the name of #TEMP table used RelationshipFeedAggregate#TEMP.

EXEC SubmitRelationshipFeed RelationshipFeedAggregate = 'RelationshipFeedAggregate#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. For XML aggregate

The XML aggregate must follow the API structure (https://images-na.ssl-images-amazon.com/images/G/01/rainier/help/xsd/release_4_1/Relationship.xsd):

<Message>
<MessageID>1</MessageID>
<Relationship>
<ParentSKU>15700</ParentSKU>
<Relation>
<SKU>99987867</SKU>
<Type>Variation</Type>
</Relation>
<Relation>
<SKU>99987867</SKU>
<Type>Variation</Type>
</Relation>
</Relationship>
</Message>

EXEC SubmitRelationshipFeed RelationshipFeedAggregate = '...(the above XML)...'

Input

Result Set Columns

SubmitSourcingOnDemandFeed¶

Usage information for the operation SubmitSourcingOnDemandFeed.rsb.

Execute¶

The stored procedure accepts two aggregated formats: #TEMP tables and XML aggregate

For #TEMP tables

You must include in your query:

INSERT INTO OrderSourcingOnDemandFeedAggregate#TEMP (AmazonOrderID, SKU, EstimatedShipDate) VALUES ('250-4747727-9303810', '15700', '2018-12-08T00:00:00Z');

Then you execute the procedure by specifying the value of OrderSourcingOnDemandFeedAggregate with the name of #TEMP table used OrderSourcingOnDemandFeedAggregate#TEMP.

EXEC SubmitSourcingOnDemandFeed OrderSourcingOnDemandFeedAggregate = 'OrderSourcingOnDemandFeedAggregate#TEMP'

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory. For XML aggregate

The XML aggregate must follow the API structure (https://m.media-amazon.com/images/G/01/rainier/help/xsd/release_4_1/OrderSourcingOnDemand._CB475945388_.xsd):

<Message>
<MessageID>1</MessageID>
<OrderSourcingOnDemand>
<AmazonOrderID>250-4747727-9303810</AmazonOrderID>
<SKU>15700</SKU>
<EstimatedShipDate>2018-12-08T00:00:00Z</EstimatedShipDate>
</OrderSourcingOnDemand>
</Message>

EXEC SubmitSourcingOnDemandFeed OrderSourcingOnDemandFeedAggregate = '...(the above XML)...'

Input

Result Set Columns

Selling Partner Data Model¶

The Jitterbit Connector for Amazon Marketplace models the Selling Partner API as relational views, and stored procedures.

To use Amazon Selling Partner Data Model, simply set Schema to SellingPartner.

Views¶

Views are tables that cannot be modified, such as Orders, Products. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures¶

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information

in the data source.

Using Reports¶

For each report type there is a view exposed. For example, report type FEE_DISCOUNTS_REPORT will be exposed as a view named REPORT_FEE_DISCOUNTS_REPORT. These views can then be queried by using 'DataStartTime' and 'DataEndTime' optional datetime parameters. When both datetime parameters are specified, the driver automatically searches for an existing report that matches the specified interval, and if not found a new report is created. Reports can be manually created with the RequestReport stored procedure. You can also use ReportOptions JSON-aggregate pseudo-column to specify additional fields that may be required depending on report type. For more details about report options please check Amazon Selling-Partner API Documentation

After a report has been created and pushed to the result set, the next time you query this report type with the 'DataStartTime' and 'DataEndTime' same filters, the previously created report is downloaded instead of creating a new report.

Tables¶

The connector models the data in Amazon Marketplace as a list of tables in a relational database that can be queried using standard SQL statements.

Jitterbit Connector for Amazon Marketplace Tables¶

Destinations¶

Returns information about all destinations.

Columns¶

InboundShipments¶

Returns a list of inbound shipments based on criteria that you specify.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• ShipmentId supports the '=' and 'IN' comparisons.
• ShipmentStatus supports the '='and 'IN' comparisons.
• MarketplaceId supports the '=' comparison.
• LastUpdatedDate supports the '=', '<', '>', '<=', '>=' comparisons.

For example, the following query is processed server side:

SELECT * FROM InboundShipments WHERE ShipmentStatus = 'Working'

SELECT * FROM InboundShipments WHERE ShipmentStatus IN ('Working', 'SHIPPED', 'IN_TRANSIT')

SELECT * FROM InboundShipments WHERE ShipmentId = '503-9993250-1405404'

SELECT * FROM InboundShipments WHERE MarketplaceId = 'ATVPDKIKX0DER'

SELECT * FROM InboundShipments WHERE LastUpdatedDate >= '2016-12-12'

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

InboundShipmentTransportDetails¶

Inbound shipment information, including carrier details, shipment status, and the workflow status for a request for shipment with an Amazon-partnered carrier.

Columns¶

ListingsItems¶

Returns details about a listings item for a selling partner.

The following filters are required:

• SKU
• SellerId: You can either specify SellerId as a pseudo-column condition in WHERE filters, or in the connection string.

Some example queries:

SELECT * FROM ListingsItems WHERE SKU = '12345' AND SellerId = 'XXXXXXXXXXXXXX'

INSERT INTO ListingsItems (ProductType, Requirements, Attributes, SKU, SellerId)
VALUES ('product_type', 'LISTING', '{\"AttributeName\": \"test_attribute\", \"AttributeValue\": \"value\"}', '12345', 'XXXXXXXXXXXXXX')

DELETE FROM ListingsItems WHERE SKU = '12345' AND SellerId = 'XXXXXXXXXXXXXX'

When inserting, you can also use temp tables in order to insert multiple attributes, as shown in the example below:

INSERT INTO Attributes#TEMP (AttributeName, AttributeValue) VALUES ('attr1', 'val1')
INSERT INTO Attributes#TEMP (AttributeName, AttributeValue) VALUES ('attr2', 'val2')
INSERT INTO Attributes#TEMP (AttributeName, AttributeValue) VALUES ('attr3', 'val3')

INSERT INTO ListingsItems (ProductType, Requirements, Attributes, SKU, SellerId)
VALUES ('product_type', 'LISTING', 'Attributes#TEMP', '12345', 'XXXXXXXXXXXXXX')

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory.

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ListingsItemsAttributes¶

The GetFeedSubmissionList operation returns a list of feed submissions.

The following filters are required:

• SKU
• SellerId: You can either specify SellerId as a pseudo-column condition in WHERE filters, or in the connection string.

Some example queries:

SELECT * FROM ListingsItemsAttributes WHERE SKU = '12345' AND SellerId = 'XXXXXXXXXXXXXX'
UPDATE ListingsItemsAttributes SET AttributeValue = 'test_value', ProductType = 'LUGGAGE'
WHERE SKU = '12345' AND AttributeName = 'item_name_value'

You can also retrieve all SKU fields from another sub-query, for example:

SELECT * FROM ListingsItems WHERE SellerId = 'XXXXXXXXXXXXXX' AND SKU IN (
        SELECT DISTINCT(SKUIdentifierSellerSKU) FROM CatalogItems WHERE MarketplaceId = 'XXXXXXXXXXXXXX' AND Query = 'test' AND SkuIdentifierSellerSku IS NOT NULL
)

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

OutboundFulfillmentOrders¶

Returns a list of fulfillment orders fulfilled after (or at) a specified date-time, or indicated by the next token parameter.

Columns¶

Subscriptions¶

Returns information about subscriptions of the specified notification type.

Columns¶

VendorShippingLabels¶

This table is part of Vendor Direct Fulfillment Shipping API. Returns a list of shipping labels created during the time frame that you specify. You define that time frame using the createdAfter and createdBefore parameters. The date range to search must not be more than 7 days.

Select

The following fields are filtered server-side:

• PurchaseOrderNumber supports the '=' comparison
• CreatedDate supports all the >, >=, =, <=, < comparisons

Some example queries:

SELECT * FROM VendorShippingLabels WHERE PurchaseOrderNumber = '12345'

SELECT * FROM VendorShippingLabels WHERE CreatedDate > '2022-09-10T12:00:00' AND CreatedDate < '2022-09-15T12:00:00'

INSERT

When inserting, you can also use temp tables in order to insert fields for aggregate objects, as shown in the example below:

INSERT INTO Items#TEMP (ItemSequenceNumber, BuyerProductIdentifier, VendorProductIdentifier, Amount, UnitOfMeasure)
                VALUES (                 1,              'item_id',               'prod_id',     10,        'unit');

INSERT INTO Container#TEMP (ContainerType, ContainerIdentifier, TrackingNumber,  PackedItems)
                    VALUES (     'carton',      'container_id',       'string', 'Items#TEMP');

INSERT INTO Address1#TEMP (  Name,     AddressLine1,          City,           County, CountryCode)
                  VALUES ('Name', 'Fayettville NC', 'Fayettville', 'North Carolina',        'NC');

INSERT INTO TaxTest#TEMP (RegistrationType, RegistrationNumber, RegistrationMessages, RegistrationAddress)
                  VALUES (           'VAT',           'string',             'string',    'Address1#TEMP');

INSERT INTO VendorShippingLabels (PurchaseOrderNumber, SellingPartyPartyId,  SellingPartyTaxRegistrationDetails, ShipFromPartyPartyId,       Containers)
VALUES ('1234567890',                    '11111',                      'TaxTest#TEMP',              '22222', 'Container#TEMP');

In order to get the Transaction ID returned from the API for the INSERT statement, you can issue a select statement from the LastResultInfo temp table, as shown below:

SELECT * FROM LastResultInfo#TEMP

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory.

Container temporary table schema info:

PackedItems temporary table schema info:

TaxRegistrationDetails temporary table schema info:

Address temporary table schema info:

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Views¶

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Jitterbit Connector for Amazon Marketplace Views¶

CatalogItems¶

The Catalog Items table helps you retrieve item details for items in the catalog.

The following filters are required:

• MarketplaceId
• One of the following: Query, SellerSKU, UPC, EAN, ISBN, JAN

For example:

SELECT * FROM CatalogItems WHERE MarketplaceID = 'XXXXXXXXXXXXX' AND ISBN = 'XXXXXXXXXXXXX'

Columns¶

CompetitivePricing¶

Returns competitive pricing information for a seller's offer listings based on seller SKU or ASIN.

Columns¶

Feeds¶

The GetFeedSubmissionList operation returns a list of feed submissions.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

Note: 'FeedType' attribute is required to query the view. You can view available values for 'FeedType' here.

• FeedId supports the '=' comparison.
• FeedType supports the '=' and 'IN' comparisons.
• MarketplaceIds supports the '=' and 'IN' comparisons.
• ProcessingStatus supports the '=' and 'IN' comparisons.
• CreatedTime supports the '=', '<', '>', '<=' and '>=' comparisons.

Following are example queries that are processed server side:

SELECT * FROM Feeds WHERE FeedId = '50950018754'

SELECT * FROM Feeds WHERE FeedType = 'POST_PRODUCT_PRICING_DATA'

SELECT * FROM Feeds WHERE FeedType IN ( 'POST_PRODUCT_PRICING_DATA', 'POST_INVENTORY_AVAILABILITY_DATA' )

SELECT * FROM Feeds WHERE FeedType = 'POST_PRODUCT_PRICING_DATA' AND MarketplaceIds = 'A1VC38T7YXB528'

SELECT * FROM Feeds WHERE FeedType = 'POST_PRODUCT_PRICING_DATA' AND ProcessingStatus = 'DONE'

SELECT * FROM Feeds WHERE FeedType = 'POST_PRODUCT_PRICING_DATA' AND CreatedTime > '2021-06-20' AND CreatedTime < '2021-08-01 12:00:00'

Columns¶

FeesEstimate¶

Returns the estimated fees for the listed products.

Columns¶

InboundItemsGuidance¶

Returns a list of items in a specified inbound shipment.

Columns¶

InboundPreparationInstructions¶

Returns labeling requirements and item preparation instructions to help prepare items for shipment to Amazon's fulfillment network.

Columns¶

InboundShipmentItems¶

Returns a list of items in a specified inbound shipment.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• MarketplaceId supports the '=' comparison.
• ShipmentId supports the '=' comparison.
• LastUpdatedDate supports the '=', '<', '>', '<=', '>=' comparisons.

For example, the following query is processed server side:

SELECT * FROM InboundShipmentItems  WHERE MarketplaceId = 'ATVPDKIKX0DER'

SELECT * FROM InboundShipmentItems  WHERE ShipmentId = 'FBA8J3K9LZPX'

SELECT * FROM InboundShipmentItems  WHERE LastUpdatedDate > '2020-01-01'

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

InboundShipmentPreorders¶

Returns a list of items in a specified inbound shipment.

Columns¶

InventorySupply¶

Returns information about the availability of inventory that a seller has in Amazon's fulfillment network and in current inbound shipments. You can check the current availability status for your Fulfillment by Amazon inventory as well as discover when availability status changes.

Select¶

The connector will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• SellerSKU supports the '=', 'IN' comparisons.
• GranularityType supports the '='comparison.
• GranularityId supports the '=' comparison.
• MarketplaceId supports the '=' comparison.
• StartDateTime supports the '=', '<', '>', '<=', '>=' comparisons.

Following are example queries which are processed server side:

SELECT * FROM InventorySupply WHERE GranularityType = 'Marketplace' AND GranularityId = 'ATVPDKIKX0DER' AND SellerSKU = '123'

SELECT * FROM InventorySupply WHERE GranularityType = 'Marketplace' AND GranularityId = 'ATVPDKIKX0DER' AND StartDateTime > '2020-01-01'

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.