Overview

This article provides an overview of how to create stored procedures which will send Till Summary information to Next Integration (Concilio).

Prerequisites

• The Operator should be able to access SQL Server Management Studio and have permission to create and execute the query

---

Next Integration (Concilio)

• The SQL Stored Procedures for Concilio allows to send Till Summary information from Bepoz to Concilio 

• As the Till Summary information is sent via the creation of SQL stored procedures, there is no setup required within Bepoz BackOffice

---

Creating the Stored Procedures

• There are two stored procedures that must be run to send the data. They are, ConcilioData and ConcilioMapping

• Follow the below steps to run both stored procedures

  1. After logging into SQL Server Management Studio, select the appropriate Database from the dropdown

  2. Click on the 'New Query' button from the toolbar

  3. Enter the Stored Procedure scripts as given below

  4. Click on the 'Execute' button which creates a stored procedure for Next Integration (Concilio)

  5. Once the stored procedure is created, it returns the till summary information to Next Integration (Concilio)

ConcilioData SQL Script:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

----------------------

--Concilio Data Export

----------------------

CREATE procedure [dbo].[ConcilioData]

@date varchar(20),

@tillid int

as

SET NOCOUNT ON

SET ANSI_NULLS OFF

SET ANSI_WARNINGS OFF

select 

Workstation.WorkstationID 'TillID',

TillSummaries.DrawerID + 1 'DrawerID',

convert(varchar,dateadd(day, cast(LEFT(ShiftID,4) as int), '2001-01-01'),111) 'TradeDate',

TillSummaries.DateUpdated,

right(TillSummaries.ShiftID,1) + 1 'ShiftNumber',

TillSummaries.LastOpID 'OperatorID',

TillSummaries.Cash,

TillSummaries.EFTPOS,

TillSummaries.Card_1,

TillSummaries.Card_2,

TillSummaries.Card_3,

TillSummaries.Card_4,

TillSummaries.Card_5,

TillSummaries.Card_6,

TillSummaries.Card_7,

TillSummaries.Card_8,

TillSummaries.Card_9,

TillSummaries.Card_10,

TillSummaries.Card_11,

TillSummaries.Card_12,

TillSummaries.Card_13,

TillSummaries.Card_14,

TillSummaries.Card_15,

TillSummaries.Card_16,

TillSummaries.Cheques,

TillSummaries.GiftCertRedeem

from TillSummaries

join Workstation on Workstation.WorkstationID = TillSummaries.LastTillID

where Workstation.WorkstationID = @tillid and convert(varchar,dateadd(day, cast(LEFT(ShiftID,4) as int), '2001-01-01'),111) >= convert(datetime,@date) 

---

ConcilioMapping SQL Script:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

------------------------------

--Concilio Workstation Mapping

------------------------------

CREATE procedure [dbo].[ConcilioMapping]

as

SET NOCOUNT ON

SET ANSI_NULLS OFF

SET ANSI_WARNINGS OFF

select

Venue.VenueID,

http://Venue.Name 'VenueName',

store.StoreID,

store.Name 'StoreName',

Workstation.WorkstationID,

Workstation.Name 'TillName',

venue.ShiftName0 'Shift1',

Venue.ShiftName1 'Shift2',

Venue.ShiftName2 'Shift3',

Venue.ShiftName3 'Shift4',

Venue.ShiftName4 'Shift5',

Venue.ShiftName5 'Shift6',

Venue.ShiftName6 'Shift7',

Venue.ShiftName7 'Shift8',

Venue.ShiftName8 'Shift9',

Venue.ShiftName9 'Shift10'

from workstation

join store on store.storeid = Workstation.storeid

join venue on venue.venueid = store.VenueID

order by venueid, storeid, Workstationid

---

Further Reading

• After the Stored Procedures are created, a user can be created with dedicated privilege to run the Stored Procedures 

  • For more information, please follow article Next Integration (Concilio) - SQL User Creation Setup