Click on the banner to learn about and purchase my database training on Azure

How to calculate shipping amount and lead time using Post Office WebService in SQL Server

Views: 5.053 views
Reading Time: 3 minutes

Hello people!
Good afternoon!

In this post, I will demonstrate once again the use of OLE Automation SQL Server to consume data from a Web service. This time, I will consume the Post Office service to calculate shipping time and value (widely used for e-Commerce).

Knowing the Post Office WebService

Before we start creating the Stored Procedure that will query the data, we must first understand the parameters and flags of the Post Office service.

WebService Post Office - Input Parameters

nCdCompanyStringNo. The parameter must be passed even empty.Your administrative code with ECT. The code is available in the body of the contract signed with the Post Office.
sDsPasswordStringNo. The parameter must be passed even empty.Password for access to the service associated with your administrative code. The initial password corresponds to the first 8 CNPJ digits entered in the contract.
nCdServiceStringYesService Code
sCepOriginStringYesSource ZIP Code without hyphen.Example: 05311900
sCepDestinationStringYesDestination ZIP Code without hyphen
nVlWeightDecimalYesOrder weight including its packaging. Weight should be reported in kilograms.
nCdFormatIntYesOrder format (including packaging). 1 - Box / Package Format / 2 - Roll / Prism Format
nVlLengthDecimalYesOrder length (including packaging) in centimeters.
nVlHeightDecimalYesOrder height (including packaging) in centimeters.
nVlWidthDecimalYesOrder width (including packaging) in centimeters.
nVlDiameterDecimalYesOrder diameter (including packaging) in centimeters.
sCdMaoPropriaStringYesIndicates whether the order will be delivered with the additional service by hand. Possible values: Y or N (Y - Yes / N - No)
nVlValueDeclaradoDecimalYesIndicates whether the order will be delivered with the additional service stated value. In this field the desired declared value in Reais must be presented.
sCdWarningReceivingStringYesIndicates whether the order will be delivered with the additional acknowledgment service. Possible values: Y or N (Y - Yes / N - No)
StrReturnStringYesIndicates how to return the query. XML = Result in XML / Popup = Result in a popup window / = Result via post on a requestor's page

WebService Post Offices - Return Parameters

Output ParameterDescription
CodeDelivery Service Code.
ValueValueDeclaredPrice of additional service Declared Value.
ValueTotal price of the order in Reais including the prices of optional services.
ProperMoonValuePrice of additional own-hand service.
AmountWarningReceivingPrice of additional service Notice of Receipt.
DeadlineDeliveryEstimated time in days for product delivery. If the returned value is 0 (zero) it indicates that the deadline was not returned correctly.
Home DeliveryInforms if the informed location has home delivery. If the deadline is not returned correctly the return of this parameter will be empty.
DeliverySaturdayInforms if the informed locality has home delivery on Saturdays. If the deadline is not returned correctly the return of this parameter will be empty.
ErröSame as Web Services.
MsgErrorReturns the description of the generated error.

WebService Post Office - Service Codes

Service Codes
41106PAC without contract
40010SEDEX without contract
40045SEDEX Charge without contract
40126SEDEX Collect on Contract
40215SEDEX 10 without contract
40290SEDEX Today without contract
40096SEDEX with contract
40436SEDEX with contract
40444SEDEX with contract
81019e-SEDEX with contract
41068PAC with contract
40568SEDEX with contract
40606SEDEX with contract
81868(1 Group) e-SEDEX with contract
81833(2 Group) e-SEDEX with contract
81850(3 Group) e-SEDEX with contract

Creating the Stored Procedure to Query the Data

With the code snippet below, we will create the Stored Procedure that we will use to query freight value and lead time data using procedures. OLE Automation to perform the GET request and return the XML from the web, which we will read using XQuery.

Examples of use

Performing a simple test, filling in only the required parameters
WebService Post Office - Simple Test

Performing a more complete test, filling all SP parameters
WebService Post Office - Full Test

Calling SP, but entering an invalid zip code
WebService Post Office - Invalid Zip Code

That's it folks!
Until the next post!