(SQL Server) Walmart V3 API - Get All Released Orders

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.

For more information, see https://developer.walmart.com/#/apicenter/marketPlace/latest#getAllReleasedOrders

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    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'

    DECLARE @success int
    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 sp_OAMethod @http, 'QuickRequest', @resp OUT, 'GET', '/v3/orders/released?createdStartDate={$createdStartDate}&limit={$limit}&shippingProgramType={$shippingProgramType}&shipNodeType={$shipNodeType}'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response Status Code: ' + @iTmp0

    DECLARE @jsonResponse int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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 @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonResponse
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    -- 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 @jsonResponse


END
GO