There is an example of each sub-views for UNIONed final view time_slices.

• Prepare DB Views for CzechIdM. We will use these views to synchronize data from HR system VEMA to IdM(Database Table Connector).
• The main time\_slices view is build up from 3 views(pzarazx, smlouva and vynetix). These views are made from several tables mentioned in this article and linked articles.

• Finished task Vema cloud setup
• Exported data from VEMA cloud/application(Python script) to MSSQL DB(2012+)
• Access to MSSQL

• List of attributes with description in IDM Time slice entity - useful in process of mapping attributes Contract Attributes mapping

1. Check/create tables in MSSQL for receiving data from VEMA (for example osobyt, smlouva, pzaraz, tutv, todz…)
2. Create 3 main views pzaraz slices, smlouva slices and vyneti slices based on the examples below
3. Check and correct views with your table names and columns there might be differenes and each company can use slightly different tables in VEMA
4. Manually check few users and their exclusion from being active/enabled (marked by column vyneti true)
5. Check for duplicates of time slices - unique dates in column slice for one person

1. Each row is a time slice. Each row means a snapshot of the current state of the employee at that time. In other words it is a date when there is any change within its contract(start, end), organization changes or temporary leave(maternity).

Column slice is unique date for that specific employee and only one change happens on one day. That also means beginning of the work contract one row, end of the work contract new separate row.

1. columns ppvza and ppvko - means start and date of work contract
2. column vyneti is true if the employee is on maternity leave(or other temporary leave)
3. column zamest/povfct is organizational information for position name.

• We can see that employee has 1 contract which ends on 2015-10-01(ppvko). Within this contract there is a temporary leave(possibly maternity) based on column vyneti(true) from 2013-03-13 to 2015-10-01. 2015-10-01 is end of temporary leave and also end of contract/break.
• 2016-09-19 contract gets renewed and is valid till 2018-03-31(ppvko) and has few organizational changes with dates. Each row is a organization change/contract, start of it or end of change/contract.

1. Start of work contract
2. Change in organization
3. Start of temporary leave (Exclusion from work - usually no access in IDM, but keep account)
4. End of temporary leave+contract end/break
5. Start on new position, start of contract
6. Change of the position
7. End of the contract

This picture is simplified version of DB view timeslices

Source data are the data exported in the requirements tasks.

table smlouvax

• Contains data about start and end dates of work contracts
• Important columns: personal number(oscis), number of contract(cicin), start of contract(ppvza), end of contract(ppvko)

table pzarazx

• contains info about date of start/change of position name/organizational position
• IMportant columns: personal number(oscis), number of contract(cicin), start of the new position/organizational position (zauc), name of funtion(todz.zames or todp.povfc), information about main/part time contract (ppvdr)

table vynetix(or obrvyn)

• Contains data about exclusion dates from work
• Exclusion is paired with users and therefore all of employees contracts
• IMportant columns: personal number(oscis), start of temporary leave-exclusion(vynz), end of temporary leave-exclusion(vynk)

table osobyx(or osobyt)

• Contains information about employees
• Organization position is paired with employee not with his contract
• Important columns: personal number(oscis), home organization/department (rosu/utvar), last change(greatest_timestamp)

table todz and czisco or table todp

• Contains text version of work position name, joining table for pzaraz.
• Either connect with todp.povfc or todz.zames, depends on what each company use

table tutv

• Contains information about department and in idm this is used for organization/treeNode, column pzarazx.utvar

IDM Entity basic columns, these should be selected in your SQL:

• contract\_id, slice, slice\_id, ppvza, ppvko, oscis, cicin, vyneti, position, utvar, ppvdr
• EAV columns are optional

As mentioned before time slices are made from 3 main views/tables(smlouvax\_slices, vynetix\_slices and pzarazx\_slices) and connected with "oscis" which is unique personal number. All views are connected with UNION command, therefore all views must have same columns in the same order.

1. Pzarazx - most of the columns are connected to this view. Table is about, where the person works and linking columns to other tables.
2. Smlouvyx - contracts info mainly start and end of contract
3. Vynetix - exclusion date ranges from work, usually maternity leave dates - start and end

Notes for code example below:

• Displayed code example is for DB view of contracts/smlouvyx.
• Comments behind columns are for mapping fields in IDM Entity called Time Slices. EAVs can be extra columns for specific requirements and functions.
• In general the short names for JOINed tables are "s" like smlouvy(contracts), "z" like pzaraz(pracovni zarazeni - work position), v as vynetix (exlusion from work). From those letter you can see from which table the columns come from.
• There are additional tables for connecting to other tables like f and u, these are for position name and organization structure.

Goal of this DB view is to get rows for each start and end date of contracts

SELECT
s.slice,        -- valid from of slice
s.slice_id,     -- unique ID, you can see below it is concatenated String
s.contract_id,  -- Code of the parent contract
s.oscis,        -- Owner - personal number or username
s.cicin,        -- not mapped, but it is person's contract number used for making slice_id and contract_id
z.ppvdr,        -- Main relation - type of contract
s.ppvza,        -- valid from of contract
s.ppvko,        -- valid till of contract
f.povfct,       -- Name of position
f.povfct1,      -- EAV abbreviation for name of position
f.povfct2,      -- EAV extended name of position
z.utvar,        -- Work position(code) IdmTreeNode
p.sk_pzar,      -- EAV zarazeni extra work position and segmentation
CASE WHEN v.oscis IS NULL THEN 'false' ELSE 'true' END AS vyneti, -- if true contract is not active during this period of time slice
z.kateg,        -- EAV
z.pzar,         -- EAV id for extra work position and segmentation
z.pracvz,       --EAV extra work position and segmentation
o.greatest_timestamp -- timestamp for changes
-- creating slice_id and contract_id with for the time slice. In this case any start(valid from) of the contract.
FROM (
   SELECT
      sm.ppvza AS slice,
      concat(sm.oscis, '_', sm.cicin, '_', YEAR(sm.ppvza), '_', MONTH(sm.ppvza), '_',DAY(sm.ppvza)) AS slice_id, -- beginning of contract as a slice_id, notice smlouvy.ppvza - which is the valid from of contract
      concat(sm.oscis, '_', sm.cicin) AS contract_id, -- combination of personal number and number of work contract
      sm.oscis, sm.cicin, sm.ppvza, sm.ppvko
   FROM smlouvax sm
) AS s
-- pzarazx LEAD() function for next date comparassion and other info for connecting other tables
LEFT JOIN (
   SELECT p.oscis, p.cicin, p.zauc, povfc, LEAD(zauc, 1) OVER (PARTITION BY oscis, cicin ORDER BY zauc ASC) AS next_zauc, p.utvar, p.ppvdr, pzar, kateg, pracvz 
   FROM pzarazx p
   ) z
ON s.oscis = z.oscis AND s.cicin = z.cicin AND z.zauc <= s.slice AND (z.next_zauc IS NULL OR z.next_zauc > s.slice)
-- linked with other tables to load the data
LEFT JOIN osobyx o ON s.oscis = o.oscis
LEFT JOIN todp f ON f.povfc = z.povfc 
LEFT JOIN tutv u ON u.utvar = z.utvar 
LEFT JOIN pzar p ON p.pzar = z.pzar
-- for purpose of column "vyneti" state of the time slice.
LEFT JOIN vynetix v ON v.oscis = s.oscis AND v.vynz <= s.slice AND (v.vynk > s.slice OR v.vynk IS NULL)

Goal of this view is to generate rows which contain start and enddate of temporary leave. If leave is valid, column vyneti = true. When temporary leave ends, column vyneti = false. This temporary leave is generated for each contract, that means if employee has 2 valid contracts, there will be 4 rows for 1 temporary leave. 2 starts(1 for each contract) and 2 end dates for each contract with same dates. This is to ensure that employees break applies to all of his/her contracts.

Pokud má vynětí stejné datum začátku jako datum konce, pak se vygenerují dva řezy s duplicitním ID. Tato situace není ošetřena, protože to ukazuje spíš na chybu v datech.

CREATE VIEW vynetix_slices AS 
-- rows with start of temporary leaves
SELECT
   vyn.slice, vyn.slice_id, contract_id, vyn.oscis,
   vyn.cicin,
   vyn.ppvza, vyn.ppvko,
   zar.zames,   
   c.praczar, c.praczarzk,
   'true' AS vyneti,
   o.rosu, o.greatest_timestamp
FROM (
   SELECT
      v.vynz AS slice,
      concat(v.oscis, '_', s.cicin, '_', YEAR(v.vynz), '_', MONTH(v.vynz), '_',DAY(v.vynz)) AS slice_id,
      concat(v.oscis, '_', s.cicin) AS contract_id,
      v.oscis, s.cicin, s.ppvza, s.ppvko
   FROM vynetix v
   -- Vygenerovani rezu pro kazdy uvazek, ktery je v dobe vyneti platny
   INNER JOIN smlouvax s ON v.oscis = s.oscis AND v.vynz >= s.ppvza AND (v.vynz <= s.ppvko OR s.ppvko IS NULL)
) vyn
LEFT JOIN (
   -- Vyhledani pracovnich zarazeni - next_zauc odpovida konci platnosti zarazeni (protoze to LEAD najde jako zacatek ucinnosti nasledujiciho zarazeni).
   SELECT p.oscis, p.cicin, p.zauc, zames, LEAD(zauc, 1) OVER (PARTITION BY oscis, cicin ORDER BY zauc ASC) AS next_zauc
   FROM pzarazx p
   ) zar
-- Vyfiltrovani platneho zarazeni k datu rezu. Zacatek zarazeni je pred datem rezu, konec neni definovan nebo je po datu rezu.
ON vyn.oscis = zar.oscis AND vyn.cicin = zar.cicin AND zar.zauc <= vyn.slice AND (zar.next_zauc IS NULL OR zar.next_zauc > vyn.slice)
-- Dotahneme zbyvajici informace z ciselniku
LEFT JOIN osobyx o ON vyn.oscis = o.oscis
LEFT JOIN todz t ON zar.zames = t.zames
LEFT JOIN czisco c ON t.czisco = c.czisco
-- Tento union zajisti i odmazani duplicit, pokud ve stejny den zacina vic ruznych druhu vyneti
UNION
-- rows with end dates of leaves
SELECT
   vyn.slice, vyn.slice_id, contract_id, vyn.oscis,
   vyn.cicin,
   vyn.ppvza,
   vyn.ppvko,
   zar.zames,   
   c.praczar,
   c.praczarzk,
   'false' AS vyneti,
   o.rosu,
   o.greatest_timestamp
FROM (
   SELECT
      v.vynk AS slice,
      concat(v.oscis, '_', s.cicin, '_', YEAR(v.vynk), '_', MONTH(v.vynk), '_',DAY(v.vynk)) AS slice_id,
      concat(v.oscis, '_', s.cicin) AS contract_id,
      v.oscis, s.cicin, s.ppvza, s.ppvko
   FROM vynetix v
   INNER JOIN smlouvax s ON v.oscis = s.oscis AND v.vynk >= s.ppvza AND (v.vynk <= s.ppvko OR s.ppvko IS NULL)
) vyn
LEFT JOIN (
   SELECT p.oscis, p.cicin, p.zauc, zames, LEAD(zauc, 1) OVER (PARTITION BY oscis, cicin ORDER BY zauc ASC) AS next_zauc
   FROM pzarazx p
   ) zar
ON vyn.oscis = zar.oscis AND vyn.cicin = zar.cicin AND zar.zauc <= vyn.slice AND (zar.next_zauc IS NULL OR zar.next_zauc > vyn.slice)
LEFT JOIN osobyx o ON vyn.oscis = o.oscis
LEFT JOIN todz t ON zar.zames = t.zames
LEFT JOIN czisco c ON t.czisco = c.czisco
-- Konce vyneti mohou byt null, proto je odfiltrujeme
WHERE vyn.slice IS NOT NULL;

Cílem je získat řez pro každý den, kdy nastává začátek účinnosti (zauc) nějakého pracovního zařazení.

Pokud ke dni zauc neexistuje žádná platná smlouva, takový řez se nebude publikovat (není smlouva = není úvazek). Až v den začátku smlouvy se objeví řez, který pochází z řezů pro smlouvy (viz výše).

K řezu se pak dotáhnou zbývající informace (organizační zařazení, zda je zaměstnanec zrovna vyňatý).

CREATE VIEW pzarazx_slices AS 
SELECT 
    z.zauc AS slice,
    concat(z.oscis, '_', z.cicin, '_', YEAR(z.zauc), '_', MONTH(z.zauc), '_',DAY(z.zauc)) AS slice_id,
    concat(z.oscis, '_', z.cicin) AS contract_id,
    z.oscis,
    z.cicin,
    s.ppvza,
    s.ppvko,
    z.zames,   
    c.praczar,
    c.praczarzk,
    CASE WHEN v.oscis IS NULL THEN 'false' ELSE 'true' END AS vyneti,
    o.rosu,
    o.greatest_timestamp
FROM pzarazx z
-- Vyhledani platne smlouvy k datu rezu. Pokud zadna platna smlouva neni, takovy rez nebudeme publikovat
INNER JOIN smlouvax s ON z.oscis = s.oscis AND z.cicin = s.cicin AND z.zauc >= s.ppvza AND (z.zauc <= s.ppvko OR s.ppvko IS NULL)
LEFT JOIN osobyx o ON z.oscis = o.oscis
LEFT JOIN todz t ON z.zames = t.zames
LEFT JOIN czisco c ON t.czisco = c.czisco
LEFT JOIN vynetix v ON v.oscis = z.oscis AND v.vynz <= z.zauc AND (v.vynk > z.zauc OR v.vynk IS NULL)

• column zames/zamest is null

Use columns povfc/povfct from table pzaraz + todp

• column povfc/povfct is null

Use columns zames/zamest from table pzaraz + todz

Check for duplicated rows with same identifier slice\_id. Duplicates are sometimes created due to 1 day leave or with same start date of contract. Better to check the users after synchronization is finished.

SELECT COUNT(*), slice_id FROM time_slices GROUP BY slice_id HAVING COUNT(*) > 1