SQL Server
SQL Server
Walmart V3 API - Get All Released Orders
See more Walmart v3 Examples
Retrieves all the orders with line items that are in the "created" status, that is, these orders have been released from the Walmart Order Management System to the seller for processing. The released orders are the orders that are ready for a seller to fulfill.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
-- Setting the Login and Password properties to the clientId and clientSecret
-- will cause the Basic Auth header to be added (i.e. BaseEncode64(clientId:clientSecret))
EXEC sp_OASetProperty @http, 'Login', 'clientId'
EXEC sp_OASetProperty @http, 'Password', 'clientSecret'
EXEC sp_OASetProperty @http, 'BasicAuth', 1
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'WM_QOS.CORRELATION_ID', 'b3261d2d-028a-4ef7-8602-633c23200af6'
-- Use the access token obtained from this example: Walmart v3 API Get OAuth2 Access Token using Client Credentials
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'WM_SEC.ACCESS_TOKEN', 'eyJraWQiOiIzZjVhYTFmNS1hYWE5LTQzM.....'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'WM_SVC.NAME', 'Walmart Marketplace'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'createdStartDate', '2020-01-16T10:30:15Z'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'limit', '200'
-- Can also be 3PLFulfilled
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'shippingProgramType', 'SellerFulfilled'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'shipNodeType', 'TWO_DAY'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', '/v3/orders/released?createdStartDate={$createdStartDate}&limit={$limit}&shippingProgramType={$shippingProgramType}&shipNodeType={$shipNodeType}', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response Status Code: ' + @iTmp0
DECLARE @jsonResponse int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @jsonResponse, 'Load', @success OUT, @sTmp0
EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonResponse
RETURN
END
-- Sample output...
-- (See the parsing code below..)
--
-- Use the this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "list": {
-- "meta": {
-- "totalCount": 25228,
-- "limit": 10,
-- "nextCursor": "?limit=10&hasMoreElements=true&soIndex=25228&poIndex=10&sellerId=152&createdStartDate=2019-04-02T00:00:00.000Z&createdEndDate=2019-09-26T16:05:49.648Z"
-- },
-- "elements": {
-- "order": [
-- {
-- "purchaseOrderId": "4792701510371",
-- "customerOrderId": "5401973367419",
-- "customerEmailId": "47B81ACD2C62434DBE64F47B9E1F7630@relay.walmart.com",
-- "orderDate": 1569513584000,
-- "shippingInfo": {
-- "phone": "2134488377",
-- "estimatedDeliveryDate": 1569870000000,
-- "estimatedShipDate": 1569553200000,
-- "methodCode": "Express",
-- "postalAddress": {
-- "name": "Julio Hernandez",
-- "address1": "9713 pleasant view dr",
-- "address2": null,
-- "city": "Rancho Cucamonga",
-- "state": "CA",
-- "postalCode": "91701",
-- "country": "USA",
-- "addressType": "RESIDENTIAL"
-- }
-- },
-- "orderLines": {
-- "orderLine": [
-- {
-- "lineNumber": "1",
-- "item": {
-- "productName": "Refurbished Lenovo YB1-X90F Grey Yoga Book with WiFi 10.1\" Touchscreen Tablet Android 6.0.1",
-- "sku": "VO190403007727R"
-- },
-- "charges": {
-- "charge": [
-- {
-- "chargeType": "PRODUCT",
-- "chargeName": "ItemPrice",
-- "chargeAmount": {
-- "currency": "USD",
-- "amount": 259
-- },
-- "tax": {
-- "taxName": "Tax1",
-- "taxAmount": {
-- "currency": "USD",
-- "amount": 20.07
-- }
-- }
-- },
-- {
-- "chargeType": "FEE",
-- "chargeName": "Fee",
-- "chargeAmount": {
-- "currency": "USD",
-- "amount": 0
-- },
-- "tax": {
-- "taxName": "Electronic Waste Recycling Fee",
-- "taxAmount": {
-- "currency": "USD",
-- "amount": 5
-- }
-- }
-- }
-- ]
-- },
-- "orderLineQuantity": {
-- "unitOfMeasurement": "EACH",
-- "amount": "1"
-- },
-- "statusDate": 1569513724000,
-- "orderLineStatuses": {
-- "orderLineStatus": [
-- {
-- "status": "Acknowledged",
-- "statusQuantity": {
-- "unitOfMeasurement": "EACH",
-- "amount": "1"
-- },
-- "cancellationReason": null,
-- "trackingInfo": null,
-- "returnCenterAddress": null
-- }
-- ]
-- },
-- "refund": null,
-- "fulfillment": {
-- "fulfillmentOption": "S2H",
-- "shipMethod": "EXPEDITED",
-- "storeId": null,
-- "pickUpDateTime": 1569870000000,
-- "pickUpBy": null,
-- "shippingProgramType": "TWO_DAY"
-- }
-- }
-- ]
-- },
-- "shipNode": {
-- "type": "3PLFulfilled"
-- }
-- }
-- ]
-- }
-- }
-- }
--
DECLARE @purchaseOrderId nvarchar(4000)
DECLARE @customerOrderId nvarchar(4000)
DECLARE @customerEmailId nvarchar(4000)
DECLARE @orderDate int
DECLARE @shippingInfoPhone nvarchar(4000)
DECLARE @shippingInfoEstimatedDeliveryDate int
DECLARE @shippingInfoEstimatedShipDate int
DECLARE @shippingInfoMethodCode nvarchar(4000)
DECLARE @shippingInfoPostalAddressName nvarchar(4000)
DECLARE @shippingInfoPostalAddressAddress1 nvarchar(4000)
DECLARE @shippingInfoPostalAddressAddress2 nvarchar(4000)
DECLARE @shippingInfoPostalAddressCity nvarchar(4000)
DECLARE @shippingInfoPostalAddressState nvarchar(4000)
DECLARE @shippingInfoPostalAddressPostalCode nvarchar(4000)
DECLARE @shippingInfoPostalAddressCountry nvarchar(4000)
DECLARE @shippingInfoPostalAddressAddressType nvarchar(4000)
DECLARE @shipNodeType nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @lineNumber nvarchar(4000)
DECLARE @itemProductName nvarchar(4000)
DECLARE @itemSku nvarchar(4000)
DECLARE @orderLineQuantityUnitOfMeasurement nvarchar(4000)
DECLARE @orderLineQuantityAmount nvarchar(4000)
DECLARE @statusDate int
DECLARE @refund nvarchar(4000)
DECLARE @fulfillmentFulfillmentOption nvarchar(4000)
DECLARE @fulfillmentShipMethod nvarchar(4000)
DECLARE @fulfillmentStoreId nvarchar(4000)
DECLARE @fulfillmentPickUpDateTime int
DECLARE @fulfillmentPickUpBy nvarchar(4000)
DECLARE @fulfillmentShippingProgramType nvarchar(4000)
DECLARE @k int
DECLARE @count_k int
DECLARE @chargeType nvarchar(4000)
DECLARE @chargeName nvarchar(4000)
DECLARE @chargeAmountCurrency nvarchar(4000)
DECLARE @chargeAmountAmount int
DECLARE @taxTaxName nvarchar(4000)
DECLARE @taxTaxAmountCurrency nvarchar(4000)
DECLARE @taxTaxAmountAmount nvarchar(4000)
DECLARE @status nvarchar(4000)
DECLARE @statusQuantityUnitOfMeasurement nvarchar(4000)
DECLARE @statusQuantityAmount nvarchar(4000)
DECLARE @cancellationReason nvarchar(4000)
DECLARE @trackingInfo nvarchar(4000)
DECLARE @returnCenterAddress nvarchar(4000)
DECLARE @listMetaTotalCount int
EXEC sp_OAMethod @jsonResponse, 'IntOf', @listMetaTotalCount OUT, 'list.meta.totalCount'
DECLARE @listMetaLimit int
EXEC sp_OAMethod @jsonResponse, 'IntOf', @listMetaLimit OUT, 'list.meta.limit'
DECLARE @listMetaNextCursor nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @listMetaNextCursor OUT, 'list.meta.nextCursor'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_i OUT, 'list.elements.order'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'I', @i
EXEC sp_OAMethod @jsonResponse, 'StringOf', @purchaseOrderId OUT, 'list.elements.order[i].purchaseOrderId'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @customerOrderId OUT, 'list.elements.order[i].customerOrderId'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @customerEmailId OUT, 'list.elements.order[i].customerEmailId'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @orderDate OUT, 'list.elements.order[i].orderDate'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoPhone OUT, 'list.elements.order[i].shippingInfo.phone'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @shippingInfoEstimatedDeliveryDate OUT, 'list.elements.order[i].shippingInfo.estimatedDeliveryDate'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @shippingInfoEstimatedShipDate OUT, 'list.elements.order[i].shippingInfo.estimatedShipDate'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoMethodCode OUT, 'list.elements.order[i].shippingInfo.methodCode'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoPostalAddressName OUT, 'list.elements.order[i].shippingInfo.postalAddress.name'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoPostalAddressAddress1 OUT, 'list.elements.order[i].shippingInfo.postalAddress.address1'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoPostalAddressAddress2 OUT, 'list.elements.order[i].shippingInfo.postalAddress.address2'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoPostalAddressCity OUT, 'list.elements.order[i].shippingInfo.postalAddress.city'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoPostalAddressState OUT, 'list.elements.order[i].shippingInfo.postalAddress.state'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoPostalAddressPostalCode OUT, 'list.elements.order[i].shippingInfo.postalAddress.postalCode'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoPostalAddressCountry OUT, 'list.elements.order[i].shippingInfo.postalAddress.country'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shippingInfoPostalAddressAddressType OUT, 'list.elements.order[i].shippingInfo.postalAddress.addressType'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @shipNodeType OUT, 'list.elements.order[i].shipNode.type'
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'list.elements.order[i].orderLines.orderLine'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
EXEC sp_OAMethod @jsonResponse, 'StringOf', @lineNumber OUT, 'list.elements.order[i].orderLines.orderLine[j].lineNumber'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @itemProductName OUT, 'list.elements.order[i].orderLines.orderLine[j].item.productName'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @itemSku OUT, 'list.elements.order[i].orderLines.orderLine[j].item.sku'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @orderLineQuantityUnitOfMeasurement OUT, 'list.elements.order[i].orderLines.orderLine[j].orderLineQuantity.unitOfMeasurement'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @orderLineQuantityAmount OUT, 'list.elements.order[i].orderLines.orderLine[j].orderLineQuantity.amount'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @statusDate OUT, 'list.elements.order[i].orderLines.orderLine[j].statusDate'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @refund OUT, 'list.elements.order[i].orderLines.orderLine[j].refund'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @fulfillmentFulfillmentOption OUT, 'list.elements.order[i].orderLines.orderLine[j].fulfillment.fulfillmentOption'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @fulfillmentShipMethod OUT, 'list.elements.order[i].orderLines.orderLine[j].fulfillment.shipMethod'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @fulfillmentStoreId OUT, 'list.elements.order[i].orderLines.orderLine[j].fulfillment.storeId'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @fulfillmentPickUpDateTime OUT, 'list.elements.order[i].orderLines.orderLine[j].fulfillment.pickUpDateTime'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @fulfillmentPickUpBy OUT, 'list.elements.order[i].orderLines.orderLine[j].fulfillment.pickUpBy'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @fulfillmentShippingProgramType OUT, 'list.elements.order[i].orderLines.orderLine[j].fulfillment.shippingProgramType'
SELECT @k = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_k OUT, 'list.elements.order[i].orderLines.orderLine[j].charges.charge'
WHILE @k < @count_k
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'K', @k
EXEC sp_OAMethod @jsonResponse, 'StringOf', @chargeType OUT, 'list.elements.order[i].orderLines.orderLine[j].charges.charge[k].chargeType'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @chargeName OUT, 'list.elements.order[i].orderLines.orderLine[j].charges.charge[k].chargeName'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @chargeAmountCurrency OUT, 'list.elements.order[i].orderLines.orderLine[j].charges.charge[k].chargeAmount.currency'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @chargeAmountAmount OUT, 'list.elements.order[i].orderLines.orderLine[j].charges.charge[k].chargeAmount.amount'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @taxTaxName OUT, 'list.elements.order[i].orderLines.orderLine[j].charges.charge[k].tax.taxName'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @taxTaxAmountCurrency OUT, 'list.elements.order[i].orderLines.orderLine[j].charges.charge[k].tax.taxAmount.currency'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @taxTaxAmountAmount OUT, 'list.elements.order[i].orderLines.orderLine[j].charges.charge[k].tax.taxAmount.amount'
SELECT @k = @k + 1
END
SELECT @k = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_k OUT, 'list.elements.order[i].orderLines.orderLine[j].orderLineStatuses.orderLineStatus'
WHILE @k < @count_k
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'K', @k
EXEC sp_OAMethod @jsonResponse, 'StringOf', @status OUT, 'list.elements.order[i].orderLines.orderLine[j].orderLineStatuses.orderLineStatus[k].status'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @statusQuantityUnitOfMeasurement OUT, 'list.elements.order[i].orderLines.orderLine[j].orderLineStatuses.orderLineStatus[k].statusQuantity.unitOfMeasurement'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @statusQuantityAmount OUT, 'list.elements.order[i].orderLines.orderLine[j].orderLineStatuses.orderLineStatus[k].statusQuantity.amount'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @cancellationReason OUT, 'list.elements.order[i].orderLines.orderLine[j].orderLineStatuses.orderLineStatus[k].cancellationReason'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @trackingInfo OUT, 'list.elements.order[i].orderLines.orderLine[j].orderLineStatuses.orderLineStatus[k].trackingInfo'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @returnCenterAddress OUT, 'list.elements.order[i].orderLines.orderLine[j].orderLineStatuses.orderLineStatus[k].returnCenterAddress'
SELECT @k = @k + 1
END
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonResponse
END
GO