lmp api integration - constant contactfiles.constantcontact.com/bf34ca6a001/412939ff-30... ·...

Technical RequirementsWeb API ConnectivitySingle Shelf Price/Price Without Tax

December 2, 2016

LMP API Integration, Web API Connectivity, Version 1.03

1 Document Overview

Who is this document for? This document is intended to be consumed by the business and technical leadership for agency organizations that will be interfacing with the State of Ohio – Division of Liquor Control (DOLC) Liquor Management System.

What is contained in this document? The contents of this document define the process in which the DOLC published sales interface is to be utilized for posting sales to the DOLC system and for retrieving current retail sales price information. Major component areas are listed below.

x Data elements x Frequency the services are to be called x Format of interface messages x Order in which the services are to be called x Error handling functionality

Where did the document originate from? This document has been produced on behalf of the State of Ohio – Division of Liquor Control for publication to state licensed liquor agencies.

When will the document be used? This document is to be used during the agency development process ahead of onboarding to the Liquor Modernization Project Phase 2 delivered solution.

How do we use the document? The technical portion of this documentation is laid out in several sections to assist the agency development teams with the various services published.

x Overviews and Assumptions x Get All Prices service x Daily Sales Transactions x Data Element Definitions


2 Solution Introduction

This document will outline how to connect to and utilize the Web API interface for Point of Sale (POS) integration to the State of Ohio – Division of Liquor Control management systems. The Web API sales interface is an enhanced sales reporting process allowing for synchronous web service calls between the agency retail system and the DOLC Dynamics AX Retail System in an autonomous fashion. The provided APIs are intended to simplify the distribution of price files to the agencies and the delivery of sales data from the agencies to the liquor management system. The sales data will contain detailed transaction information of all liquor sales for a given agency by day. It is expected that sales data for a given agency will be electronically submitted daily after retail sales have completed. A high level overview of what will later be technically defined in this document is listed below for each of the components of the Web API Interface. The solution involves three primary functions:

x Reporting of transaction-level retail sales data x End of day reconciliation x Retail price download


2.1 Web API Interface Services

Master Service Sales Submission Service

The master service is used to load detailed transaction headers, lines, and payment information to Dynamics AX for validation. The service supports a synchronous call and will return any validation errors for correction. If errors are identified, all sales data is rejected and must be reloaded.

Master End of Day (EOD) Submission Service

The master EOD submission is called as a final validation step to ensure that the sales information submitted by the agent matches what was received and loaded into Dynamics AX. This service is to be called once per store after all sales are reported through the Master Service Sales submission process. The service supports a synchronous call and will return any validation errors for correction. If errors are identified, the sales data is rejected and must be reloaded.

Get All Prices Service

The get all prices service is leveraged to collect the latest retail pricing information from the State of Ohio – Division of Liquor Control. This service should be called for latest pricing information daily to ensure latest pricing information is loaded into the agency POS system. This service is also recommended to be called to update agency POS system any time the sales processing interface returns errors related to price mismatches.

WEB API AGENCY

INTERFACE

DOLC CLOUD

INTERNET

WEB API SALES INTERFACE

AGENCY SERVERS

WEB API

DIXF

DYNAMICS AXENTERPRISE PORTAL


2.2 Sales Reporting Process Flow Agency Sales Collection – This node of the flow is an agency process for the collection of the data from the Agency POS and prepared for submission to the DOLC Web API sales interface

Master Service Sales Submission – This node of the flow is where the agency developed application for interfacing with the DOLC Web API sales interface is called and the data streamed from the agency retail systems to the DOLC Dynamics AX retail system.

Data Accepted 1 – The first round of sales data preprocessing occurs to check for errors in the reported data. If no errors are identified, the service proceeds and the data remains loaded in the preprocessor waiting for the EOD submission. If errors are found, the data is purged from the preprocessor and error messages are returned.

Master EOD Submission – This node of the flow represents where the agency developed application for interfacing with the DOLC Web API sales interface is called with the reconciliation information of total sales dollars and total transaction counts for a specific agency to ensure all agency provided sales data matches to what the DOLC service consumed.

Data Accepted 2 – The second round of sales data preprocessing occurs based on the summary information provided to the EOD service in regards to total transactions and total sales amount. If sales transaction counts do not match, the data is purged from the preprocessor and error messages are returned. If total transactions submitted to EOD matches and total sales amounts do not match, the data will still be loaded and a message will be returned indicating the total sales amount mismatch.

Post Sales – At the time of a successful EOD message that passes the second round of preprocessor validation, a submit sales process is called to load the agency sales information into the Dynamics AX Retail System. This process will call the Dynamics AX Retail Statement Processing batch processes to consolidate and post all sales information into the DOLC solution for additional processing.

Auto

mat

ed th

roug

h ba

tch

Start

Agency Sales Collection

End

Master Service Sales Submission

Data accepted 1

Post Sales

Retail Statement

Statement Posting

Sales Order Invoice

No

Return Error Messages

Purge Transaction Data

Yes Master EOD Submission

Data accepted 2 No Return Error

MessagesPurge Transaction

Data

Yes


2.3 Solution Related Assumptions The following list outlines the assumptions around the use of the Web API:

x Agencies that would leverage this API have a Retail POS System that has not been provisioned by the State of Ohio.

x Extraction of the data into the specified formats (see the individual JSON Format sections within the following API descriptions) will be the responsibility of the Agency.

x Agency sales will be uploaded on a daily basis. x Sales Data will be transmitted separately for each Agency for entities with multiple

locations. x The Web API service only supports retail transactions; all wholesale transactions will be

entered through another process defined by the Division of Liquor Control. x All errors will be logged for alerting and reporting. x Messages that do not receive a successful validation will be rejected and not processed

through the Liquor Management System and an error message with detailed information will be returned.

x Authentication to the Web API will be handled through the State of Ohio ADFS. x Web API user credentials and onboarding process will be handled in a separate

onboarding document. x ItemID will be the assigned Brand Code from Division of Liquor Control. x Return transactions will be provided with negative values on the sales transaction line

details: Quantity, NetAmount, NetAmountInclTax, Price, TaxAmount

2.4 Solution Testing Environment The following environment will be setup for development efforts and corresponding unit testing.

Table 1 - Development Environment1

Service Type Web API URL

Master service https://lmpapidev.ohio.gov:2601/api/masterservice

Master end of day https://lmpapidev.ohio.gov:2601/api/mastereod

Get all prices https://lmpapidev.ohio.gov:2601/api/getallprices

1 Actual server names and Endpoints will be made available in the Onboarding Document.

http://host/api/masterservice

https://lmpapidev.ohio.gov:2601/api/mastereod

https://lmpapidev.ohio.gov:2601/api/getallprices


3 Web API Reference

As described in the introduction, the Web API supports two distinct processes related to the distribution of sales prices to the agency and the delivery of sales data from an agency to DOLC. The following sections will outline these two processes in more detail and provide the technical layout on how to interact with these services including how to construct the call and the expected response.

3.1.1 Authentication

Authentication to the web services will be addressed through the State of Ohio ADFS solution. Credentials and onboarding will be handled individually for each agency.

Code sample for authentication

string authority = "https://sts.sol.com/adfs"; string resourceURI = "http://soladfs/webapi"; string clientID = "E1CF1107-FF90-4228-93BF-26052DD2C714"; string clientReturnURI = "http://solretailwebservices/"; AuthenticationContext ac = new AuthenticationContext(authority, false);

AuthenticationResult ar = ac.AcquireToken(resourceURI, clientID, new Uri(clientReturnURI));

string authHeader = ar.CreateAuthorizationHeader(); HttpClient client = new HttpClient();

HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Get, "https://solretailwebservices:44302/api/Values");

request.Headers.TryAddWithoutValidation("Authorization", authHeader); HttpResponseMessage response = await client.SendAsync(request); string responseString = await response.Content.ReadAsStringAsync();

https://sts.sol.com/adfs

http://soladfs/webapi

http://solretailwebservices/

https://solretailwebservices:44302/api/Values


3.1.2 Get All Prices

This service provides the ability for the agency to retrieve active prices for all liquor products allowing for the update of their POS systems with the latest price information. It is recommended that this service is implemented in such a way that it can be invoked anytime in the Agency’s POS system to ensure that prices can be updated on demand.

The following diagram outlines the process flow for the Get All Prices Web Service.

Start EndSubmit

GetAllPrices Web API

Data Returned?Process sales prices in POS

system

Review error messages

Correct request

Yes

No

Figure 1 - Process flow for Get All Prices web service

The GetAllPrices web service will provide feedback to the caller:

x Request to service o Date – date passed will be what current price will be calculated from

x Response with details of all items and current prices o ItemID – state brand code o Description – description of state brand code o UPC – scanning barcode of brand code o Current effective date – current price as of date passed to service o Current effective price – current price as of date passed to service o Next effective date – next effective date, null value if no next effective date defined o Next effective price – next effective price, null value if no next effective price defined

Sample JSON document formats are included to consume the Get All Prices message.


Table 2 - Daily price service call request

JSON Format

{ "getallprices": { "date": "9/4/2017" } }



Table 3 - Daily price service call response - success

JSON Format

{ "getallpricesresult": { "items": { "item": [{ "itemid": "0066B", "description": "Jack Daniels Black", "upc": "1231231234123", "currenteffectivedate": "9/1/2017", "currentprice": "26.80", "nexteffectivedate": "11/1/2017", "nexteffectiveprice": "27.99" }] } } }

Currently the DOLC performs price updates once a month with new prices typically being active on the 1st of the month. The recommended use of this web service is to invoke it daily to ensure the latest prices are always available in the liquor agency POS.

Table 4 – Daily price service call request

JSON Format

{ "getallprices": { "date": "9/13/2016" } }

Table 5 - Daily price service call response – success

JSON Format

{ "getallpricesresult": { "items": { "item": [{ "itemid": "ItemA", "description": "Test Item A", "upc": "1234567890123", "currenteffectivedate": "9/1/2017", "currentprice": "26.00", "nexteffectivedate": "11/1/2017", "nexteffectiveprice": "28.00" }, { "itemid": "ItemB", "description": "Test Item B", "upc": "1234567890124", "currenteffectivedate": "9/1/2017", "currentprice": "29.00", "nexteffectivedate": "11/1/2017", "nexteffectiveprice": "24.00"


}, { "itemid": "ItemC", "description": "Test Item C", "upc": "1234567890125", "currenteffectivedate": "9/1/2017", "currentprice": "29.00", "nexteffectivedate": "", "nexteffectiveprice": "" }] } } }

Table 6 - Daily price service call response – error

JSON Format

{ "getallpricesresult": { "errors": { "error": [{ "errorid": "300", "message": "Back office is not available. Please contact your administrator." }] } } }

Error messaging for Get All Prices service will validate system connectivity with the DOLC Liquor Management System as well as the proper format of the request. The current error handling scenarios and responses are listed in the table below.

Trigger Error Id Error message

Microsoft Dynamics AX not available

300 Back office is not available. Please contact your administrator.

Malformed message 101 Your message is not formatted properly. Please contact your administrator.

Type mismatch 102 Message value %1 is expected to be of type %2 for record %3 according to the data schema.

3.1.3 Daily Sales Transactions

In order to deliver the daily sales transactions to the DOLC Liquor Management System, the Master Service and Master EOD service need to be invoked.


Master Service is the web service, which provides the delivery of the daily sales transactions to the DOLC Liquor Management System. Once this web service is successfully processed, the Master EOD service needs to be invoked to confirm, that all sales transactions for that day has been successfully received by DOLC.

The following diagram outlines the process flow for the Daily Sales Transactions.

Auto

mat

ed th

roug

h ba

tch

Start

Agency Sales Collection

End

Master Service Sales Submission

Data accepted

Post Sales

Retail Statement

Statement Posting

Sales Order Invoice

No

Return Error Messages


Yes Master EOD Submission

Data accepted No Return Error Messages


Yes

Index

Agency

Liquor Management System

Figure 2 - Process flow for daily sales transactions


Master Service

This services provides the ability for the agency to send all sales transactions for a single day to one service.

The Master Service web service will provide feedback to the caller:

x Success x Failure with details of what records failed the validation and a reason (message)

Sample JSON document formats are included to consume the Master Service message.

https://lmpapidev.ohio.gov:2601/api/masterservice

Table 6 – Master Service request

JSON Format

{ "master": { "transactionheader": [{ "ShippingDateRequested": "10/6/2016", "store": "10101", "terminal": "10101-01", "transactionId": "000001", "businessDate": "10/6/2016", "transDate": "10/6/2016", "transTime": "10/6/2016", "salestransactions": { "salestransaction": [{ "lineNum": "001", "ShippingDateRequested": "10/6/2016", "store": "10101", "terminalId": "10101-01", "transactionId": "000001", "businessDate": "10/6/2016", "itemId": "0066B", "netAmount": "25.99", "netAmountInclTax": "27.55", "price": "25.99", "qty": "1", "taxAmount": "1.56", "transDate": "10/6/2016", "transTime": "12:34:56" }, { "lineNum": "002", "ShippingDateRequested": "10/6/2016", "store": "10101", "terminalId": "10101-01", "transactionId": "000001", "businessDate": "10/6/2016", "itemId": "0067B", "netAmount": "12.99", "netAmountInclTax": "13.77", "price": "12.99",

http://host/api/masterservice


"qty": "1", "taxAmount": "0.78", "transDate": "10/6/2016", "transTime": "12:34:56" }] }, "paymenttransactions": { "paymenttransaction": [{ "store": "10101", "terminalId": "10101-01", "transactionId": "000001", "amountCur": "41.32", "businessDate": "10/6/2016", "transDate": "10/6/2016", "transTime": "12:34:56" }] } }, { "ShippingDateRequested": "10/6/2016", "store": "10101", "terminal": "10101-01", "transactionId": "000002", "businessDate": "10/6/2016", "transDate": "10/6/2016", "transTime": "10/6/2016", "salestransactions": { "salestransaction": [{ "lineNum": "001", "ShippingDateRequested": "10/6/2016", "store": "10101", "terminalId": "10101-01", "transactionId": "000002", "businessDate": "10/6/2016", "itemId": "0066B", "netAmount": "25.99", "netAmountInclTax": "27.55", "price": "25.99", "qty": "1", "taxAmount": "1.56", "transDate": "10/6/2016", "transTime": "12:34:56" }, { "lineNum": "002", "ShippingDateRequested": "10/6/2016", "store": "10101", "terminalId": "10101-01", "transactionId": "000002", "businessDate": "10/6/2016", "itemId": "0067B", "netAmount": "12.99", "netAmountInclTax": "13.77", "price": "12.99", "qty": "1", "taxAmount": "0.78", "transDate": "10/6/2016", "transTime": "12:34:56"


}] }, "paymenttransactions": { "paymenttransaction": [{ "store": "10101", "terminalId": "10101-01", "transactionId": "000002", "amountCur": "41.32", "businessDate": "10/6/2016", "transDate": "10/6/2016", "transTime": "12:34:56" }] } }] } }

Table 7 – Master Service response

JSON Format

{ "masterresult": { "success": "xyz", "errors": { "error": [{ "recordId": "xyz", "errorid": "204", "message": "Store 00000 is not valid in company DOLC." }, { "recordId": "xyz", "errorid": "205", "message": "Sales tax validation failed for record %1." }] } } }

Data element definitions can be found in the Web API Data Elements section of this document.

It is recommended that the Master Service be called a single time per day to submit all transactions for one day for one store.

The success node of each returned message will return "true" or "false". A true response indicates the message was received and passed all preprocessing validations. A false response indicates that errors were found when the data received was sent through the preprocessing agent. Error messages on the false response will be found in the error nodes within each response message.

Error messaging for Master Service will validate system connectivity with the DOLC Liquor Management System as well as the proper format of the incoming message. The current error handling scenarios and responses are listed in the table below.








validateCustAccount 200 Customer '%1' is not valid in company %2.

validateItemId 201 Item '%1' is not valid in company %2.

validatePrice 202

203

Item number validation failed for record %1.

Bottle price validation failed for record %1.

validateStore 204 Store '%1' is not valid in company %2.

validateTax 205

206

Sales tax validation failed for record %1.

Excise tax validation failed for record %1.

validateTerminalId 207 Terminal '%1' is not valid in company %2.

Master EOD

The Master End of Day (EOD) service provides the ability for the agency to confirm to the DOLC Liquor Management System that all sales transaction for the day for a store have been properly loaded. By invoking this service, the sales Web API validates that all sales transactions have been delivered and that the DOLC Liquor Management System can start to process the sales transactions. It is required to invoke this service every day for every store to signify sales are ready to be processed.

The Master EOD web service will provide feedback to the caller:

x Success x Failure with a reason (message)

Sample JSON document formats are included to consume the MasterEOD message.



Table 8 – Master EOD request

JSON Format

{ "mastereod": { "store": "xyz", "businessDate": "1/1/2017", "checkTransCount": "485", "checkSalesSum": "5485.42" } }

Table 9 – Master EOD response

JSON Format

{ "mastereodresult": { "success": "xyz", "errors": { "error": [{ "errorid": "250", "message": "Check sales sum does not match with the expected total sales amount for the day." }] } } }

Data element definitions can be found in the Web API Data Elements section of this document.

It is required that the Master EOD service will be called a single time per day to signify that all sales have been loaded for a store and to reconcile sales were properly loaded.

The success node of each returned message will return "true" or "false". A true response indicates the message was received and passed all preprocessing validations. A false response indicates that errors were found when the data received was sent through the preprocessing agent. Error messages on the false response will be found in the error nodes within each response message.

Error messaging for Master EOD service will validate system connectivity with the DOLC Liquor Management System as well as the proper format of the incoming message. The current error handling scenarios and responses are listed in the table below.









validateCheckSalesSum 250 Check sales sum doesn’t match with the expected total sales amount for the day.

validateCheckTransCount 251 Check transactions count doesn’t match with the expected transactions count for the day.


4 Web API Data Elements

The following table outlines the data elements used in the web service calls.

Service Reference DataElemndID Type Size Mandatory DescriptionMasterService Transaction header ShippingDateRequested Date Yes Date of transaction in POSMasterService Transaction header store String 10 Yes Identification of storeMasterService Transaction header terminal String 10 Yes Identification of POS terminalMasterService Transaction header transactionId String 44 Yes Receipt number from POS (unique identifier)MasterService Transaction header businessDate Date Yes Business date of transactionMasterService Transaction header transDate Date Yes Date of transaction in POSMasterService Transaction header transTime Time Yes Time of transaction in POSMasterService Sales transactions lineNum Real Yes Identification of lineMasterService Sales transactions ShippingDateRequested Date Yes Date of transaction in POSMasterService Sales transactions store String 10 Yes Identification of storeMasterService Sales transactions terminalId String 10 Yes Identification of POS terminalMasterService Sales transactions transactionId String 44 Yes Identification of the transactionMasterService Sales transactions businessDate Date Yes Business date of transactionMasterService Sales transactions itemID String 20 Yes Identify the brand code of the item.MasterService Sales transactions netAmount Real Yes Net amount of the sales lineMasterService Sales transactions netAmountInclTax Real Yes Net amount of the sales line including taxMasterService Sales transactions price Real Yes Price of itemMasterService Sales transactions qty Real Yes Quantity sold on receipt lineMasterService Sales transactions taxAmount Real Yes Calculated sales tax amount.MasterService Sales transactions transDate Date Yes Date of transaction in POSMasterService Sales transactions transTime Time Yes Time of transaction in POSMasterService Payment transaction store String 10 Yes Identification of storeMasterService Payment transaction terminalID String 10 Yes Identification of POS terminalMasterService Payment transaction transactionId String 44 Yes Identification of the transactionMasterService Payment transaction amountCur Real Yes The amount in the transaction currencyMasterService Payment transaction businessDate Date Yes Business date of transactionMasterService Payment transaction transDate Date Yes Date of transaction in POSMasterService Payment transaction transTime Time Yes Time of transaction in POSGetAllPrices Get prices ItemID String 20 Yes Identify the brand code of the item.GetAllPrices Get prices Description String 60 Yes Description of the barcode for the item.GetAllPrices Get prices UPC String 80 Yes Digits for the barcode of the itemGetAllPrices Get prices currenteffectivedate Date Yes Price effective date as of the date in the request messageGetAllPrices Get prices currentprice Real Yes Price for the current price effective dateGetAllPrices Get prices nexteffectivedate Date Yes Price effective date for the next price changeGetAllPrices Get prices nexteffectiveprice Real Yes Price for the next price effectivedate


5 Single Shelf Price/Price Without Tax Upon introduction of the Single Shelf Price/Price Without Tax initiative, Agencies which choose to continue to use their existing retail terminal will need to work with their retail terminal providers to ensure the following functions are implemented:

x State-provided liquor products should be identified as taxable at the local effective sales tax rate in the Agent’s retail terminal

x The retail terminal must calculate sales tax at the line item level as is the case for other non-liquor taxable items

x The retail terminal must calculate Cuyahoga County “Sin Tax” (Cuyahoga agents only)

x Retail price must be represented at the line level on the sales receipt

x For tax remittance purposes, the Agency must segregate Liquor Sales Taxes and “Sin Tax” (where applicable) from non-alcohol sales taxes

x The Division of Liquor Control (DOLC) will continue to sweep and remit sales taxes accrued for spirituous liquor, Agencies will continue to be responsible for remittance of non-liquor accrued sales taxes.

The work effort and technical steps to achieve these functions will vary based on the retail terminal used by each agency. Please consult your manufacturer and/or support personnel to ensure your system is compliant. The tentative date for this change becoming effective is Feb. 1, 2017.

These changes are in addition to those required for integration with Phase 2 of the Liquor Modernization Project (LMP) for sales reporting and price retrieval. The technical specs for that process are available in the Web API Connectivity document available to Agencies.

For those Agencies who will be directly integrating, after confirming the decision with DOLC, an onboarding guide will be provided with connectivity information, usernames, passwords and other client-specific data to begin testing.

lmp api integration - constant contactfiles.constantcontact.com/bf34ca6a001/412939ff-30... ·...

Documents