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 Name	Type	Required	Description
Name	String	True	The seller's name.
AddressLine1	String	True	The first line of the selling party's address.
AddressLine2	String	False	Additional address information (if it is required).
AddressLine3	String	False	Additional address information (if it is required).
City	String	False	The city where the selling party is located.
County	String	False	The county where the selling party is located.
District	String	False	The district where the selling party is located.
StateOrRegion	String	False	The state or region where the selling party is located.
PostalCode	String	False	The postal code for the address. This code consists of a series of letters, digits, or both.
PostalOrZipCode	String	False	The postal or zip code of that address. It contains a series of letters or digits or both, sometimes including spaces or punctuation.
CountryCode	String	False	The two-digit country code in ISO 3166-1 alpha-2 format.
Phone	String	False	The phone number for the selling party that is located at that address.

ShipFromPartyAddress temporary table schema info:


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

ShipToPartyAddress temporary table schema info:


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

ShippedItems temporary table schema info:


Column Name	Type	Required	Description
ItemSequenceNumber	String	True	The line-item sequence number for the item.
AmazonProductIdentifier	String	False	The Amazon Standard Identification Number (ASIN) of an item.
VendorProductIdentifier	String	False	The vendor-selected product identification of the item.
ShippedQuantityAmount	Integer	True	The shipped quantity. This value should not be zero.
ShippedQuantityUnit	String	True	The unit of measure for the shipped quantity.
ShippedQuantityUnitSize	Integer	False	The unit size for the shipped quantity.
PurchaseOrderNumber	String	False	The 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	False	The lot number of the shipped quantity.
ExpiryManufacturerDate	Datetime	False	The 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	False	The 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	False	The unit for the duration after the manufacturing date during which the product is valid for consumption.
ExpiryAfterDurationValue	Integer	False	The value for the duration in terms of the duration unit.
MaximumRetailPriceCurrencyCode	String	False	The three-digit currency code, in ISO 4217 format, for the maximum retail prices.
MaximumRetailPriceAmount	String	False	The maximum retail price for the shipped items as a decimal number with no loss of precision.
HandlingCode	String	False	The code that identifies the instructions about how the specified item, carton, or pallet should be handled.

Cartons temporary table schema info:


Column Name	Type	Required	Description
CartonIdentifiers	String	False	A list of carton identifiers.
CartonSequenceNumber	String	True	The 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	False	The length of the container.
DimensionsWidth	String	False	The width of the container.
DimensionsHeight	String	False	The height of the container.
DimensionsUnit	String	False	The unit of measure for dimensions (In, Ft, Meter, Yard).
WeightValue	String	False	The weight measurement value for the container as a decimal number with no loss of precision.
WeightUnit	String	False	The unit of measure for the weight of the container.
TrackingNumber	String	False	The tracking number for the container. This number is required for every carton in the small-parcel shipments.
Items	String	True	A list of container item details.

Cartons/CartonIdentifiers temporary table schema info:


Column Name	Type	Required	Description
ContainerIdentificationType	String	True	The container identification type.
ContainerIdentificationNumber	String	True	The container identification number that adheres to the definition of the container identification type.

Cartons/CartonsItems temporary table schema info:


Column Name	Type	Required	Description
ItemReference	String	True	The 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	True	The 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	True	The unit of measure for the shipped quantity.
ShippedQuantityUnitSize	Integer	False	The case size (if product is ordered by the case). Otherwise, the value is 1.
PurchaseOrderNumber	String	False	The 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	False	The 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	False	The 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	False	The 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	False	The unit for the duration after the manufacturing date during which the product is valid for consumption.
ExpiryAfterDurationValue	Integer	False	The value for the duration in terms of the duration unit.
MaximumRetailPriceCurrencyCode	String	False	The three-digit currency code, in ISO 4217 format, for the maximum retail prices.
MaximumRetailPriceAmount	String	False	The maximum retail price for the shipped items as a decimal number with no loss of precision.
HandlingCode	String	False	A code that identifies the instructions about how the specified carton should be handled.

Pallets temporary table schema info:


Column Name	Type	Required	Description
PalletIdentifiers	String	True	A list of pallet identifiers.
Tier	Integer	False	The number of layers per pallet.
Block	Integer	False	The number of cartons per layer on the pallet.
DimensionsLength	String	False	The length of the pallet.
DimensionsWidth	String	False	The width of the pallet.
DimensionsHeight	String	False	The height of the pallet.
DimensionsUnit	String	False	The unit of measure for the dimensions of the pallet.
WeightValue	String	False	The weight measurement value for pallet as a decimal number with no loss of precision.
WeightUnit	String	False	The unit of measure for the weight of the pallet.
CartonCount	Integer	False	The number of cartons that are present in the shipment. Provide the carton count only for unpalletized shipments.
CartonReferenceNumbers	String	False	An 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	False	A list of pallet item details.

Pallets/PalletIdentifiers temporary table schema info:


Column Name	Type	Required	Description
ContainerIdenticationType	String	True	The container identification type. Allowed values are SSCC, AMZNCC, GTIN, BPS, and CID.
ContainerIdentificationNumber	String	True	Container identification number that adheres to the definition of the container identification type.

Pallets/PalletsItems temporary table schema info:


Column Name	Type	Required	Description
ItemReference	String	True	The reference number for the item. Provide the item sequence number from the items segment to refer to that item's details here.
ShippedQuantityAmount	Integer	True	The 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	True	The unit of measure for the shipped quantity.
ShippedQuantityUnitSize	Integer	False	The case size (if product is ordered by the case). Otherwise, the value is 1.
PurchaseOrderNumber	String	False	The 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	False	The 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	False	The 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	False	The 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	False	The unit for the duration after the manufacturing date during which the product is valid for consumption.
ExpiryAfterDurationValue	Integer	False	The value for the duration in terms of the duration unit.
MaximumRetailPriceCurrencyCode	String	False	The three-digit currency code, in ISO 4217 format, for the maximum retail prices.
MaximumRetailPriceAmount	String	False	The maximum retail price for the shipped items as a decimal number with no loss of precision.
HandlingCode	String	False	A 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.

JDBC Driver for Amazon Marketplace