Flow for Securing Race Data

1. A scheduled workflow is created to run at specific times. This workflow triggers an alert. The alert body contains a comma-delimited list of trading entity IDs and a cut-off date. 0 means today, -1 means yesterday, otherwise it is a date in yyyymmdd format.
2. The Heritage Alerts Listener (HAL) Windows service receives the alert. It fires off the extract.
3. The extract writes a new record to the ExtractRequest table of the reporting database with the details of the request, the extract type “Race”, and the status “Start”. It then runs the main inventory and physicals queries and writes the results to the InventoryBalance and OpenPhysicalsExtract tables respectively. If any data already exists for that cut-off date/trading entity, it is deleted first – there can only be one set of data for a cut-off date. While it is reading the data, it updates the status on the ExtractRequest table to be “Read 1” for inventory and “Read 2” for physicals. While it is writing the data, it updates the status on the ExtractRequest table to be “Write 1” for inventory and “Write 2” for physicals, and when it is complete, it updates the status to be “Done”.

Endpoints

There are two endpoints, one for inventory data, and one for physicals data.  Both endpoints return their data in the ODATA format.

The inventory endpoint is InventoryExtract(tradingentityid={tradingentityid}, cutOffDate={cutOffDate}) where

• tradingEntityId is a comma-delimited list of ITAS company mnemonic in upper or lower case.
• cutOffDate is a date in the format yyyymmdd. Passing 0 will run for today, and -1 will run for yesterday.

The physicals endpoint is OpenContractsExtract(tradingentityid={tradingentityid}, cutOffDate={cutOffDate}). It has the same parameters as the inventory endpoint.

Extracted Data

Inventory

Extract inventory data that is open, not sales invoiced as at cut-off date, and warehoused at the cut-off date. For some configurations also exclude inventory that the operating Entity does not have title to. For non-US inventory exclude any inventory marked as EQUITY.

Physicals

Extract physical data where the contracts are open, have quantity, are not sales invoiced as at cut-off, have a contract date not later than the cut-off, and that are not third-party.  Classified between priced and unpriced quantities, as well as operating Entity and Association contracts.  Remove any inventory quantities from the purchases.

Troubleshooting

1. Check that the scheduled workflow is running. This can be done in Traders Desktop in the ITAS Workflow Console screen. Start the workflow if necessary.
2. Check that the HAL Windows service is running. Start the service if necessary.
3. Look at the HAL log in the ITAS folder to see if it is being triggered. If it isn’t being triggered, this suggests there are problems with alerts.
4. Look in the staging database in the ExtractRequest table to see if requests are being made. If there are no entries, it is possible that the extract cannot communicate with the database. If the entries show that the extract hasn’t completed, this suggests it has crashed.
5. If all the data is correct, but the endpoints aren’t returning correct data, check that the connection string for the staging database in the ODATA service is correct.
6. Check that the ODATA web service is running in IIS.

Manual Trigger

Can manually trigger an extract using the ITAS API Events/Publish endpoint, passing in “GF” as trading entity ID, and the new alert ID as the topic. An example message body is:

{
    "TID": "TID-002",
    "InnerBody": {
        "Title": "Project Race Trigger",
        "Message": "Now Trigger",
        "AdditionalParameters": [
        {

            "Key": "TradingEntityIds",
            "Value": "BR, RG",
        },
        {    
            “Key”: “CutOff”,
            "Value”: 0
        }
    ]}
}