JDBC Driver for Amazon Marketplace

Build 22.0.8538

SubmitVendorShipmentConfirmations

Submits shipment confirmations for vendor orders.

Execute

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

Below is an example where the usage of all #TEMP tables is covered. The following stored procedure inputs take a temp table: SellingPartyAddress, ShipFromPartyAddress, ShipToPartyAddress, ShippedItems, Cartons, and Pallets. Furthermore, the Cartons and Pallets temp table schemas, have the following inputs that also take temp tables: CartonIdentifiers/PalletIdentifiers and Items.

First, create a ShipFromPartyAddress temporary table for the ShipFromPartyAddress input:

INSERT INTO ShipFromPartyAddress#TEMP (Name, AddressLine1, City, CountryCode) VALUES ('ABC electronics warehouse', 'DEF 1st street', 'Berlin', 'DE')

Then, create a ShippedItems temporary table for the ShippedItems input:

INSERT INTO ShippedItems#TEMP (ItemSequenceNumber, VendorProductIdentifier, ShippedQuantityAmount, ShippedQuantityUnit) VALUES ('001', '9782700001659', 100, 'Eaches')

The Cartons input has a more complex structure. Two of its inputs, CartonIdentifiers and Items need to be built from the CartonIdentifiers#TEMP and CartonItems#TEMP tables respectively:

INSERT INTO CartonIdentifiers#TEMP (ContainerIdentificationType, ContainerIdentificationNumber) VALUES ('SSCC', '00102234567666698888')
INSERT INTO CartonItems#TEMP (ItemReference, ShippedQuantityAmount, ShippedQuantityUnit, ShippedQuantityUnitSize) VALUES ('001', 25, 'Eaches', 1)
INSERT INTO Cartons#TEMP (CartonIdentifiers, CartonSequenceNumber, Items) VALUES ('CartonIdentifiers#TEMP', '001', 'CartonItems#TEMP')

Then, add another row to the Cartons input:

INSERT INTO CartonIdentifiers2#TEMP (ContainerIdentificationType, ContainerIdentificationNumber) VALUES ('SSCC', '00102234567666699999')
INSERT INTO CartonItems2#TEMP (ItemReference, ShippedQuantityAmount, ShippedQuantityUnit, ShippedQuantityUnitSize) VALUES ('002', 50, 'Eaches', 1)
INSERT INTO Cartons#TEMP (CartonIdentifiers, CartonSequenceNumber, Items) VALUES ('CartonIdentifiers2#TEMP', '002', 'CartonItems2#TEMP')

The Pallets input has a similar structure compared to the Cartons one:

INSERT INTO PalletIdentifiers#TEMP (ContainerIdentificationType, ContainerIdentificationNumber) VALUES ('SSCC', '00102234567898098745')
INSERT INTO Pallets#TEMP (PalletIdentifiers, Tier, Block, CartonCount, CartonReferenceNumbers) VALUES ('PalletIdentifiers#TEMP', 2, 2, 4, '001,002,003,004')

After the necessary temporary tables have been created, execute the stored procedure as shown in the example below:

EXECUTE SubmitVendorShipmentConfirmations
		ShipmentIdentifier = '00050003',
		ShipmentConfirmationType = 'Original',
		ShipmentType = 'LessThanTruckLoad',
		ShipmentConfirmationDate = '2022-08-07T19:56:45.632Z',
		SellingPartyId = 'VENDORCODE',
		ShipFromPartyId = 'VENDORWAREHOUSECODE',
		ShipFromPartyAddress = 'ShipFromPartyAddress#TEMP',
		ShipToPartyId = 'AMZWAREHOUSECODE',
		ShippedItems = 'ShippedItems#TEMP',
		Cartons = 'Cartons#TEMP',
		Pallets = 'Pallets#TEMP';

SellingPartyAddress temporary table schema info:

Column NameTypeRequiredDescription
Name String TrueThe seller's name.
AddressLine1 String TrueThe first line of the selling party's address.
AddressLine2 String FalseAdditional address information (if it is required).
AddressLine3 String FalseAdditional address information (if it is required).
City String FalseThe city where the selling party is located.
County String FalseThe county where the selling party is located.
District String FalseThe district where the selling party is located.
StateOrRegion String FalseThe state or region where the selling party is located.
PostalCode String FalseThe postal code for the address. This code consists of a series of letters, digits, or both.
PostalOrZipCode String FalseThe postal or zip code of that address. It contains a series of letters or digits or both, sometimes including spaces or punctuation.
CountryCode String FalseThe two-digit country code in ISO 3166-1 alpha-2 format.
Phone String FalseThe phone number for the selling party that is located at that address.

ShipFromPartyAddress temporary table schema info:

Column NameTypeRequiredDescription
Name String TrueThe shipper's name.
AddressLine1 String TrueThe first line of the shipper's address.
AddressLine2 String FalseAdditional address information (if it is required).
AddressLine3 String FalseAdditional address information (if it is required).
City String FalseThe city where the shipper is located.
County String FalseThe county where the shipper is located.
District String FalseThe district where the shipper is located.
StateOrRegion String FalseThe state or region where the shipper party is located.
PostalCode String FalseThe postal code for the shipper's address. This code consists of a series of letters, digits, or both.
PostalOrZipCode String FalseThe postal or zip code of that address. It contains a series of letters or digits or both, sometimes including spaces or punctuation.
CountryCode String FalseThe two-digit country code in ISO 3166-1 alpha-2 format.
Phone String FalseThe shipper's phone number.

ShipToPartyAddress temporary table schema info:

Column NameTypeRequiredDescription
Name String TrueThe ship-to party's name.
AddressLine1 String TrueThe first line of the ship-to party's address.
AddressLine2 String FalseAdditional address information (if it is required).
AddressLine3 String FalseAdditional address information (if it is required).
City String FalseThe city where the ship-to party is located.
County String FalseThe county where the ship-to party is located.
District String FalseThe district where the ship-to party is located.
StateOrRegion String FalseThe state or region where the ship-to party is located.
PostalCode String FalseThe postal code for the ship-to address. This code consists of a series of letters, digits, or both.
PostalOrZipCode String FalseThe postal or zip code of that address. It contains a series of letters or digits or both, sometimes including spaces or punctuation.
CountryCode String FalseThe two-digit country code in ISO 3166-1 alpha-2 format.
Phone String FalseThe phone number for the ship-to party that is located at that address.

ShippedItems temporary table schema info:

Column NameTypeRequiredDescription
ItemSequenceNumber String TrueThe line-item sequence number for the item.
AmazonProductIdentifier String FalseThe Amazon Standard Identification Number (ASIN) of an item.
VendorProductIdentifier String FalseThe vendor-selected product identification of the item.
ShippedQuantityAmount Integer TrueThe shipped quantity. This value should not be zero.
ShippedQuantityUnit String TrueThe unit of measure for the shipped quantity.
ShippedQuantityUnitSize Integer FalseThe unit size for the shipped quantity.
PurchaseOrderNumber String FalseThe Amazon purchase order (PO) number for the shipped item that is being confirmed. The PO number should be formatted as an eight-character, alphanumeric code.
LotNumber String FalseThe lot number of the shipped quantity.
ExpiryManufacturerDate Datetime FalseThe production, packaging, or assembly date that is determined by the manufacturer. This date's meaning is determined based on the trade-item context.
ExpiryDate Datetime FalseThe date that determines the limit of consumption or use of a product. This date's meaning is determined based on the trade item context.
ExpiryAfterDurationUnit Datetime FalseThe unit for the duration after the manufacturing date during which the product is valid for consumption.
ExpiryAfterDurationValue Integer FalseThe value for the duration in terms of the duration unit.
MaximumRetailPriceCurrencyCode String FalseThe three-digit currency code, in ISO 4217 format, for the maximum retail prices.
MaximumRetailPriceAmount String FalseThe maximum retail price for the shipped items as a decimal number with no loss of precision.
HandlingCode String FalseThe code that identifies the instructions about how the specified item, carton, or pallet should be handled.

Cartons temporary table schema info:

Column NameTypeRequiredDescription
CartonIdentifiers String FalseA list of carton identifiers.
CartonSequenceNumber String TrueThe sequence number for the carton. The first carton is numbered 001, the second 002, and so on. This number is used as a reference to refer to the carton from the pallet level.
DimensionsLength String FalseThe length of the container.
DimensionsWidth String FalseThe width of the container.
DimensionsHeight String FalseThe height of the container.
DimensionsUnit String FalseThe unit of measure for dimensions (In, Ft, Meter, Yard).
WeightValue String FalseThe weight measurement value for the container as a decimal number with no loss of precision.
WeightUnit String FalseThe unit of measure for the weight of the container.
TrackingNumber String FalseThe tracking number for the container. This number is required for every carton in the small-parcel shipments.
Items String TrueA list of container item details.

Cartons/CartonIdentifiers temporary table schema info:

Column NameTypeRequiredDescription
ContainerIdentificationType String TrueThe container identification type.
ContainerIdentificationNumber String TrueThe container identification number that adheres to the definition of the container identification type.

Cartons/CartonsItems temporary table schema info:

Column NameTypeRequiredDescription
ItemReference String TrueThe reference number for the item. Be sure to provide the item sequence number from the items segment to refer to that item's details here.
ShippedQuantityAmount Integer TrueThe number of units that are shipped for a specific item at a shipment level. If the item is present only in certain cartons within the shipment, provide this at the appropriate carton level.
ShippedQuantityUnit String TrueThe unit of measure for the shipped quantity.
ShippedQuantityUnitSize Integer FalseThe case size (if product is ordered by the case). Otherwise, the value is 1.
PurchaseOrderNumber String FalseThe Amazon purchase order (PO) number for the shipment that is being confirmed. If the items in this shipment belong to multiple PO numbers that are in particular carton within the shipment, then provide the PO number at the carton level. The PO numbers should be formatted as an eight-character, alphanumeric code.
LotNumber String FalseThe batch or lot number for the cartons. This number associates cartons with information that the manufacturer considers relevant for traceability of the cartons to which the element string is applied. The data might refer to the itself or to items within the carton. This field is mandatory for all perishable items.
ExpiryManufacturerDate Datetime FalseThe production, packaging, or assembly date that is determined by the manufacturer. This date's meaning is determined based on the trade-item context.
ExpiryDate Datetime FalseThe date that determines the limit of consumption or use of a product. This date's meaning is determined based on the trade item context.
ExpiryAfterDurationUnit String FalseThe unit for the duration after the manufacturing date during which the product is valid for consumption.
ExpiryAfterDurationValue Integer FalseThe value for the duration in terms of the duration unit.
MaximumRetailPriceCurrencyCode String FalseThe three-digit currency code, in ISO 4217 format, for the maximum retail prices.
MaximumRetailPriceAmount String FalseThe maximum retail price for the shipped items as a decimal number with no loss of precision.
HandlingCode String FalseA code that identifies the instructions about how the specified carton should be handled.

Pallets temporary table schema info:

Column NameTypeRequiredDescription
PalletIdentifiers String TrueA list of pallet identifiers.
Tier Integer FalseThe number of layers per pallet.
Block Integer FalseThe number of cartons per layer on the pallet.
DimensionsLength String FalseThe length of the pallet.
DimensionsWidth String FalseThe width of the pallet.
DimensionsHeight String FalseThe height of the pallet.
DimensionsUnit String FalseThe unit of measure for the dimensions of the pallet.
WeightValue String FalseThe weight measurement value for pallet as a decimal number with no loss of precision.
WeightUnit String FalseThe unit of measure for the weight of the pallet.
CartonCount Integer FalseThe number of cartons that are present in the shipment. Provide the carton count only for unpalletized shipments.
CartonReferenceNumbers String FalseAn array of reference numbers for the cartons that are part of this pallet and shipment. Provide the carton sequence number from the cartons segment to refer to that carton's details here.
Items String FalseA list of pallet item details.

Pallets/PalletIdentifiers temporary table schema info:

Column NameTypeRequiredDescription
ContainerIdenticationType String TrueThe container identification type. Allowed values are SSCC, AMZNCC, GTIN, BPS, and CID.
ContainerIdentificationNumber String TrueContainer identification number that adheres to the definition of the container identification type.

Pallets/PalletsItems temporary table schema info:

Column NameTypeRequiredDescription
ItemReference String TrueThe reference number for the item. Provide the item sequence number from the items segment to refer to that item's details here.
ShippedQuantityAmount Integer TrueThe number of units that are shipped for a specific item at a shipment level. If the item is present only in certain cartons or pallets within the shipment, provide this at the appropriate carton or pallet level.
ShippedQuantityUnit String TrueThe unit of measure for the shipped quantity.
ShippedQuantityUnitSize Integer FalseThe case size (if product is ordered by the case). Otherwise, the value is 1.
PurchaseOrderNumber String FalseThe Amazon purchase order (PO) number for the shipment that is being confirmed. If the items in this shipment belong to multiple PO numbers that are in particular pallet within the shipment, then provide the PO number at the pallet level. The PO numbers should be formatted as an eight-character, alphanumeric code.
LotNumber String FalseThe batch or lot number for the pallet. This number associates a pallet with information that the manufacturer considers relevant for traceability of the pallet to which the element string is applied. The data might refer to the pallet itself or to items within the pallet. This field is mandatory for all perishable items.
ExpiryManufacturerDate Datetime FalseThe production, packaging, or assembly date that is determined by the manufacturer. This date's meaning is determined based on the trade-item context.
ExpiryDate Datetime FalseThe date that determines the limit of consumption or use of a product. This date's meaning is determined based on the trade item context.
ExpiryAfterDurationUnit String FalseThe unit for the duration after the manufacturing date during which the product is valid for consumption.
ExpiryAfterDurationValue Integer FalseThe value for the duration in terms of the duration unit.
MaximumRetailPriceCurrencyCode String FalseThe three-digit currency code, in ISO 4217 format, for the maximum retail prices.
MaximumRetailPriceAmount String FalseThe maximum retail price for the shipped items as a decimal number with no loss of precision.
HandlingCode String FalseA code that identifies the instructions about how the specified pallet should be handled.

Input

Name Type Required Description
ShipmentIdentifier String True The unique shipment ID.
ShipmentConfirmationType String True The shipment confirmation type. This parameter indicates whether this shipment confirmation is the initial confirmation or whether it is intended to replace a shipment confirmation that is already posted.

The allowed values are Original, Replace.

ShipmentType String False The type of shipment.

The allowed values are TruckLoad, LessThanTruckLoad, SmallParcel.

ShipmentStructure String False The shipment hierarchical structure.

The allowed values are PalletizedAssortmentCase, LooseAssortmentCase, PalletOfItems, PalletizedStandardCase, LooseStandardCase, MasterPallet, MasterCase.

TransportationDetailsCarrierScac String False The code that identifies the carrier for the shipment.
TransportationDetailsCarrierShipmentReferenceNumber String False A unique number that is assigned by the carrier. This field is also known as PRO number.
TransportationDetailsTransportationMode String False The mode of transportation for this shipment.

The allowed values are Road, Air, Ocean.

TransportationDetailsBillOfLadingNumber String False The Bill Of Lading (BOL) number. This number is the unique number that is assigned by the vendor.
AmazonReferenceNumber String False The Amazon Reference Number. This number is a unique identifier that is generated by Amazon for all Collect/WePay shipments.
ShipmentConfirmationDate Datetime True The date on which the shipment confirmation is submitted.
ShippedDate Datetime False The date and time of the departure of the shipment from the vendor's location.
EstimatedDeliveryDate Datetime False The date and time by which the shipment is expected to reach the buyer's warehouse.
SellingPartyId String True The assigned identification for the selling party.
SellingPartyAddress String False An aggregate representation of the address, which can be in the form of a #TEMP table.
SellingPartyTaxRegistrationType String False The tax registration type for the entity.

The allowed values are VAT, GST.

SellingPartyTaxRegistrationNumber String False The tax registration number for the entity (for example, the VAT Id).
ShipFromPartyId String True The assigned identification for the ship-from party.
ShipFromPartyAddress String False The identification of the shipper, by address. This is an aggregate representation of the address, which can be in the form of a #TEMP table.
ShipFromPartyTaxRegistrationType String False The tax registration type for the entity.

The allowed values are VAT, GST.

ShipFromPartyTaxRegistrationNumber String False The tax registration number for the entity (for example, the VAT Id).
ShipToPartyId String True The assigned identification for the ship-to party.
ShipToPartyAddress String False The identification of the ship-to party, by address. This is an aggregate representation of the address, which can be in the form of a #TEMP table.
ShipToPartyTaxRegistrationType String False The tax registration type for the ship-to party.

The allowed values are VAT, GST.

ShipToPartyTaxRegistrationNumber String False The tax registration number for the ship-to party (for example, the VAT Id).
ShipmentMeasurements String False The JSON aggregate representation of the shipment measurement details.
ImportDetails String False The JSON aggregate representation of the import details.
ShippedItems String True An aggregate representation of the items in this shipment, which can be in the form of a #TEMP table.
Cartons String False An aggregate representation of the cartons in this shipment, which can be in the form of a #TEMP table.
Pallets String False An aggregate representation of the pallets in this shipment, which can be in the form of a #TEMP table.

Result Set Columns

Name Type Description
Success String Boolean indicating whether the stored procedure was successfully executed.
TransactionId String GUID to identify this transaction. This value can be used with the CheckVendorTransactionStatus stored procedure to return the status of this transaction.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8538