Artikkeliin on koottu tyypillinen palkkaliittymän malli. Tässä mallissa on esitetty parhaat käytännöt palkkaliittymän määrittelyyn, jossa yhdellä säännöllä poimitaan kerralla kokonaisuus - esimerkiksi kaikki ylityöt. Säännöt voidaan hyödyntää sellaisenaan palkkaliittymissä, mutta niihin tulee huomioida kohdejärjestelmän vaatimukset esimerkiksi tiedon ryhmittelyn ja mahdollisten järjestelmäkohtaisten tietokenttien osalta.
Löydät tietokantataulujen sarakekuvaukset täältä.
Poissaololiittymän poiminnan malli on tässä artikkelissa, projektirakenteen tai projektien huomioiden poiminta tässä artikkelissa ja henkilötietojen poiminnan malli tässä artikkelissa.
Poiminnan periaatteet
Poiminnan ryhmittely
Säännöllä tehtyä poimintaa voidaan ryhmitellä GROUP BY - lausekkeella. Ryhmittely laskee yhteen ne rivit, joissa poiminnan ryhmittelyyn määritetyt tiedot ovat samanlaisia.
Pääsääntöisesti palkkajärjestelmiin vielään palkkaliittymässä aineisto siten, että se on ryhmitelty per säännöllä muodostettu palkkalaji (OverrideSalaryCode), per päivä (SalaryRenderingDate) ja mahdollisesti per projektit (ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6,). Mikäli palkkajärjestelmässä on tärkeää eritellä tapahtumalle tehty kuvaus, niin siinä tapauksessa ryhmitellään vielä per kuvaus (Comment). Alla oleva malli on kirjoitettu sisältäen nämä kaikki ryhmittelyt.
Poiminnassa käytettävä henkilön tunniste
Aineistossa muodostuu automaattisesti henkilön tunniste sen mukaisena kuin se on kuvattu järjestelmäkohtaisessa aineistokuvauksessa. Tämä tunniste voidaan korvata poimimalla säännössä toinen tunniste sarakkeeksi, esimerkiksi 'AS OverridePersonnelNumber'.
Poiminnan tuottamat palkkalajit
Palkkalaji on määritelty poimintasäännössä, tai se tulee esimerkiksi työlisän tai työkorotuksen asetuksista. Poiminnoissa on määritetty, että palkkalajia 0 ei muodosteta aineistoon (HAVING OverrideSalaryCode != 0). Asettamalla työkorotuksen-, työlisän- tai säännöstä muodostuvan palkkalajin nollaksi, se jätetään pois aineistosta.
Jos on tarve rajata aineiston ulkopuolelle sellaisia tapahtumia tai esim. lisiä, joissa palkkalajikoodia ei ole asetettu, tällöin tulee sääntöön lisätä WHERE-ehtoon
AND OverrideSalaryCode IS NOT NULL
Henkilötietojen (UserInfoData) pominta
Henkilötietojen poimintaan ei ole suositeltavaa käyttää JOIN-lauseketta. Jos henkilötieto liitetään JOIN-lausekkeella väärin, se helposti johtaa palkka-aineiston tuntimäärien moninkertaistumiseen.
Tämän välttämiseen on suositeltavaa käyttää seuraavaa tapaa, jolla on myös helpompi poimia useampi henkilötieto:
SELECT *, SUM(Amount) AS OverrideAmount,
-- Henkilöryhmän koodi, valittu CustomData1 -tietoon
IFNULL(
(
SELECT ui.Value FROM UserInfoData ui
WHERE ui.InfoTypeName = 'UserGroupCode_SDSQL' AND
datetime(UserSalaryData.SalaryRenderingDate) BETWEEN ui.ValidFrom AND ui.ValidTo
LIMIT 1
),
''
) AS CustomData1,
-- Maksuryhmän koodi, valittu CustomData2 -tietoon
IFNULL(
(
SELECT ui.Value FROM UserInfoData ui
WHERE ui.InfoTypeName = 'NeptonPaymentGroupCode_SDSQL' AND
datetime(UserSalaryData.SalaryRenderingDate) BETWEEN ui.ValidFrom AND ui.ValidTo
LIMIT 1
),
''
) AS CustomData2
FROM UserSalaryData
GROUP BY SalaryRenderingDate, CompensationType, ProjectCode1
Sääntöjoukon malli
Sääntöjoukolle perustetaan mallin mukaisesti seuraavat poimintasäännöt. Poimintasäännöt ovat ryhmitelty siten, että kukin sääntö poimii aineistokokonaisuuden kerralla, kuten ylityöt tai sairauspoissaolot. Mallin mukaista sääntöä tulee muokata siten, että se sisältää oikeat järjestelmä- tai asiakaskohtaiset palkkalajit ja halutun ryhmittelyn.
Mikäli poimittu aineisto sisältää sellaisia rivejä, joiden palkkalajia ei ole otettu huomioon aineistossa niin ne tulevat palkkalajilla 'NOT_DEFINED'
Toteutunut työ ilman ylitöitä
Poimitaan kaikki tapahtumat, jotka huomioidaan säännölliseen työaikaan (CompensationType = 'BasicTime'), sekä saldosta vähennetty aika.
SELECT *, SUM(Amount) As OverrideAmount
-- put below this line any changes to the columns queried / overrides
-- put above this line any changes to the columns queried / overrides
,CASE
WHEN ActivityTypeCode = 1 THEN 'TYÖ_PALKKALAJI' -- työ
WHEN ActivityTypeCode = 18 THEN 'TYÖMUUALLA_PALKKALAJI' -- työ muualla
WHEN ActivityTypeCode = 84 THEN 'ETÄTYÖ_PALKKALAJI' -- etätyö
WHEN ActivityTypeCode = 6 THEN 'PALKALLINEN_RUOKATAUKO_PALKKALAJI' -- palkallinen ruokatauko
WHEN ActivityTypeCode = 31 THEN 'KOKOUS_PALKKALAJI' -- kokous
WHEN ActivityTypeCode = 25 THEN 'KOULUTUS_PALKKALAJI' -- koulutus
WHEN ActivityTypeCode = 26 THEN 'MATKA_AIKA_PALKKALAJI' -- matka-aika
WHEN ActivityTypeCode = 5 THEN 'LUOTTAMUSMIESTYÖ_PALKKALAJI' -- Luottamusmiestyö
WHEN ActivityTypeCode = 17 THEN 'TERVEYDENHOITO_PALKKALAJI' -- Terveydenhoito työajalla
WHEN ActivityTypeCode = 14 THEN 'TYÖSUOJELUTYÖ_PALKKALAJI' -- Työsuojelu
WHEN ActivityTypeCode IS NULL AND IsTimeBorrowedFromBalance = 'Y' THEN 'SALDOSTA_VAHENNETTY_PALKKALAJI' -- Saldosta vähennetty aika
-- put below this line any customer specific event codes
--put above this line any customer specific event codes
ELSE
'NOT_DEFINED'
END AS OverrideSalaryCode
FROM UserSalaryData
WHERE CompensationType = 'BasicTime'
AND SickLeaveTypeId IS NULL
AND AbsenceTypeID IS NULL
AND BasePartTarget IN ('salary')
AND IsApproved = 'Y'
AND (OvertimeTarget IS NULL
OR OvertimeTarget = 'uncompensated')
GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
HAVING Overridesalarycode != 0
Toteutunut ylityö
Poimitaan kaikki tapahtumat, joissa muodostuu ylityötä, jonka perusosa sekä korotusosa maksetaan palkkana.
SELECT *, SUM(Amount) As OverrideAmount
-- put below this line any changes to the columns queried / overrides
-- put above this line any changes to the columns queried / overrides
,CASE
-- TES ylityöt
WHEN CompensationType = 'AdditionalWork' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'LISÄTYÖ_PALKKALAJI' -- lisätyö
WHEN CompensationType = 'AdditionalWork' AND overtimepercentage = 50 AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'LISÄTYÖ50%_PALKKALAJI' -- lisätyö50%
WHEN CompensationType = 'OvertimeDaily50' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_VRK50_PALKKALAJI' -- vrk50
WHEN CompensationType = 'OvertimeDaily100' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_VRK100_PALKKALAJI' -- vrk100
WHEN CompensationType = 'OvertimeWeekly50' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_VK50_PALKKALAJI' -- vk50
WHEN CompensationType = 'OvertimeWeekly100' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_VK100_PALKKALAJI' -- vk100
WHEN CompensationType = 'OvertimeSunday50' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_SUNNUNTAI50_PALKKALAJI' -- Sunnuntai50
WHEN CompensationType = 'OvertimeSunday100' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_SUNNUNTAI100_PALKKALAJI' -- Sunnuntai100
WHEN CompensationType = 'TESOvertime50' AND OvertimeTarget = 'salary' THEN 'TES50_PALKKALAJI' -- TES50
WHEN CompensationType = 'TESOvertime100' AND OvertimeTarget = 'salary' THEN 'TES100_PALKKALAJI' -- TES100
-- Muut kuin TES-ylityöt
WHEN CompensationType = 'AdditionalWork' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'LISÄTYÖ_PALKKALAJI' -- lisätyö
WHEN CompensationType = 'AdditionalWork' AND overtimepercentage = 50 AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'LISÄTYÖ50%_PALKKALAJI' -- lisätyö50%
WHEN CompensationType = 'OvertimeDaily50' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'VRK50_PALKKALAJI' -- vrk50
WHEN CompensationType = 'OvertimeDaily100' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'VRK100_PALKKALAJI' -- vrk100
WHEN CompensationType = 'OvertimeWeekly50' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'VK50_PALKKALAJI' -- vk50
WHEN CompensationType = 'OvertimeWeekly100' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'VK100_PALKKALAJI' -- vk100
WHEN CompensationType = 'OvertimeSunday50' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'SUNNUNTAI50_PALKKALAJI' -- Sunnuntai50
WHEN CompensationType = 'OvertimeSunday100' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'SUNNUNTAI100_PALKKALAJI' -- Sunnuntai100
WHEN CompensationType = 'OvertimeBaseFixed' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'BASEFIXED_PALKKALAJI' -- Base fixed
WHEN CompensationType = 'OvertimeExtraFixed' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'EXTRAFIXED_PALKKALAJI' -- Extra fixed
WHEN CompensationType = 'LevellingPeriodAdditional' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'TASOITTUMISJAKSOLISATYO_PALKKALAJI' -- Levelling period additional work
WHEN CompensationType = 'LevellingPeriodOvertime50' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'TASOITTUMISJAKSOYLITYO50_PALKKALAJI' -- Levelling period overtime 50
WHEN CompensationType = 'LevellingPeriodOvertime100' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'TASOITTUMISJAKSOYLITYO100_PALKKALAJI' -- Levelling period overtime 100
-- put below this line any customer specific rules
--put above this line any customer specific rules
ELSE
'NOT_DEFINED'
END AS OverrideSalaryCode
FROM UserSalaryData
WHERE CompensationType IN ('AdditionalWork','OvertimeDaily50','OvertimeDaily100',
'OvertimeWeekly50','OvertimeWeekly100','OvertimeSunday50','OvertimeSunday100',
'OvertimeBaseFixed','OvertimeExtraFixed','TESOvertime50','TESOvertime100',
'LevellingPeriodAdditional','LevellingPeriodOvertime50','LevellingPeriodOvertime100')
AND BasePartTarget IN ('salary')
AND IsApproved = 'Y'
GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
HAVING Overridesalarycode != 0
Työkorotukset
Poimitaan kaikki työkorotukset ja asetetaan työkorotuksen palkkalajiksi korotukselle asetettu palkkalajikoodi. Työkorotuksen koodiksi on mahdollista asettaa palkkalaji / - viivalla jaoteltuna siten, että
- ensimmäinen osa kuvaa palkkalajia säännöllisellä työajalla
- toinen osa kuvaa palkkalajia 50% ylityön ajalta
- kolmas osa kuvaa palkkaljia 100% ylityön ajalta
Työkorotuksen koodia ei kuitenkaan tarvitse jakaa tällä tavalla, vaan sääntö poimii kaikissa tapauksissa koodin sellaisenaan, vaikka jaottelua ei ole tehty.
SELECT *, SUM(Amount) As OverrideAmount,
CASE
WHEN instr(WorkRiseSalaryCode,'/') = 0 OR WorkRiseEarnedAtCompensationType = 'BasicTime' THEN substring_part(WorkRiseSalaryCode, '/', 1)
WHEN WorkRiseEarnedAtCompensationType IN ('OvertimeDaily50','OvertimeWeekly50','OvertimeSunday50') THEN substring_part(WorkRiseSalaryCode, '/', 2)
ELSE substring_part(WorkRiseSalaryCode, '/', 3)
-- put below this line any changes to the columns queried / overrides
-- put above this line any changes to the columns queried / overrides
END AS OverrideSalaryCode
FROM UserSalaryData
WHERE CompensationType = 'WorkRise'
AND workrisepaytarget = 'Salary'
AND IsApproved = 'Y'
GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
HAVING Overridesalarycode != 0
Työlisät
Poimitaan kaikki työlisät ja asetetaan työlisän palkkalajiksi korotukselle asetettu palkkalajikoodi.
SELECT *, SUM(Amount) As OverrideAmount,
WorkIncrementCode as OverrideSalaryCode
-- put below this line any changes to the columns queried / overrides
-- put above this line any changes to the columns queried / overrides
FROM UserSalaryData
WHERE CompensationType = 'WorkIncrement'
AND IsApproved = 'Y'
GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
HAVING Overridesalarycode != 0
Osapäivän sairauspoissaolot
Poimitaan kaikki osapäivän sairauspoissaolot
SELECT *, SUM(Amount) As OverrideAmount
-- put below this line any changes to the columns queried / overrides
-- put above this line any changes to the columns queried / overrides
,CASE WHEN SickLeaveTypeId = 1 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_ITSESAIRAS' -- palkaton itse sairas WHEN SickLeaveTypeId = 2 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_LAPSISAIRAS' -- palkaton lapsi sairas WHEN SickLeaveTypeId = 3 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_TYOTAPATURMA_TOISSA' -- palkaton työtapaturma työpaikalla WHEN SickLeaveTypeId = 4 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_TYOTAPATURMA_TYOMATKALLA' -- palkaton työtapaturma työmatkalla WHEN SickLeaveTypeId = 5 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_TAPATURMA_VAPAALLA' --palkaton vapaa-ajan tapaturma WHEN SickLeaveTypeId = 1 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_ITSESAIRAS' -- palkallinen itse sairas WHEN SickLeaveTypeId = 2 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_LAPSISAIRAS' -- palkallinen lapsi sairas WHEN SickLeaveTypeId = 3 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_TYOTAPATURMA_TOISSA' -- palkallinen työtapaturma työpaikalla WHEN SickLeaveTypeId = 4 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_TYOTAPATURMA_TYOMATKALLA' -- palkallinen työtapaturma työmatkalla WHEN SickLeaveTypeId = 5 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_TAPATURMA_VAPAALLA' --palkallinen vapaa-ajan tapaturma
-- put below this line any customer specific absence events
--put above this line any customer specific absence events
WHEN SickLeaveTypeId IS NOT NULL THEN ('Sairauspoissaolo: ' || SickLeaveTypeId || ' - ' || CompensationType) --this row picks up all sick leaves´types that are not picked up by the rule already
ELSE
'NOT DEFINED'
END AS OverrideSalaryCode
FROM UserSalaryData
WHERE CompensationType IN ('BasicTime','UnpaidBasicTime')
AND
SickLeaveTypeId IS NOT NULL
AND IsApproved = 'Y'
--
-- Alla oleva ehto jättää tuloksista pois ne päivät, joissa työaikaa on korvattu jonain muuna kuin sairauspoissaolona
AND
SalaryRenderingDate IN (SELECT SalaryRenderingDate
FROM UserSalaryData
WHERE SickLeaveTypeId IS NULL
AND CompensationType IN ('BasicTime', 'ReducedRateTime', 'UnpaidBasicTime', 'AdditionalWork', 'OvertimeDaily50', 'OvertimeDaily100', 'OvertimeWeekly50', 'OvertimeWeekly100', 'OvertimeSunday50', 'OvertimeSunday100', 'PublicHoliday', 'YearLeave'))
--
GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
HAVING Overridesalarycode != 0
Osapäivän poissaolot
Poimitaan kaikki osapäivän poissaolot
SELECT *, SUM(Amount) As OverrideAmount
-- put below this line any changes to the columns queried / overrides
-- put above this line any changes to the columns queried / overrides
,CASE
WHEN AbsenceTypeId = 25 AND CompensationType = 'UnpaidBasicTime' THEN 'AMMATTIYHDISTYSTOIMINTA_PALKATON' -- Palkaton ammattiyhdistystoiminta
WHEN AbsenceTypeId = 10 AND CompensationType = 'UnpaidBasicTime' THEN 'ASEPALVELUS_PALKATON' -- Palkaton ase- tai siviilipalvelusaika
WHEN AbsenceTypeId = 1 AND CompensationType = 'UnpaidBasicTime' THEN 'ERITYISAITIYSVAPAA_PALKATON'-- Palkaton erityisäitiysvapaa
WHEN AbsenceTypeId = 5 AND CompensationType = 'UnpaidBasicTime' THEN 'HOITOVAPAA_PALKATON'-- Palkaton hoitovapaa
WHEN AbsenceTypeId = 3 AND CompensationType = 'UnpaidBasicTime' THEN 'ISYYSVAPAA_PALKATON'-- Palkaton isyysvapaa
WHEN AbsenceTypeId = 11 AND CompensationType = 'UnpaidBasicTime' THEN 'KERTAUS_PALKATON'-- Palkaton kertausharjoitus
WHEN AbsenceTypeId = 27 AND CompensationType = 'UnpaidBasicTime' THEN 'KUNTOUTUS_PALKATON' -- Palkaton kuntoutus
WHEN AbsenceTypeId = 18 AND CompensationType = 'UnpaidBasicTime' THEN 'KUTSUNTA_PALKATON' -- Palkaton kutsuntatilaisuus
WHEN AbsenceTypeId = 13 AND CompensationType = 'UnpaidBasicTime' THEN 'MUU_POISSAOLO_PALKATON' -- Palkaton muu poissaolo
WHEN AbsenceTypeId = 26 AND CompensationType = 'UnpaidBasicTime' THEN 'MUUTTOVAPAA_PALKATON' -- Palkaton muuttovapaa
WHEN AbsenceTypeId = 452 AND CompensationType = 'UnpaidBasicTime' THEN 'OMAISHOITOVAPAA_PALKATON' -- Palkaton omaishoitovapaa
WHEN AbsenceTypeId = 17 AND CompensationType = 'UnpaidBasicTime' THEN 'OMAT50_60_JUHLAT_PALKATON' -- Palkaton oma 50- tai 60-vuotisjuhla
WHEN AbsenceTypeId = 16 AND CompensationType = 'UnpaidBasicTime' THEN 'PARISUHTEEN_REKISTEROINTI_PALKATON' -- Palkaton omat vihkiäiset / parisuhteen rekisteröinti
WHEN AbsenceTypeId = 8 AND CompensationType = 'UnpaidBasicTime' THEN 'OPINTOVAPAA_PALKATON' -- Palkaton opintovapaa
WHEN AbsenceTypeId = 22 AND CompensationType = 'UnpaidBasicTime' THEN 'OSA_AIKAELAKE_PALKATON' -- Palkaton osa-aikaeläke
WHEN AbsenceTypeId = 31 AND CompensationType = 'UnpaidBasicTime' THEN 'OSA-AIKAISUUS_PALKATON' -- Palkaton osa-aikaisuus
WHEN AbsenceTypeId = 14 AND CompensationType = 'UnpaidBasicTime' THEN 'PERHEENJASENEN_KUOLEMA_PALKATON' -- Palkaton perheenjäsenen kuolema
WHEN AbsenceTypeId = 15 AND CompensationType = 'UnpaidBasicTime' THEN 'HAUTAJAISET_PALKATON' -- Palkaton perheenjäsenen tai lähiomaisen hautajaiset
WHEN AbsenceTypeId = 20 AND CompensationType = 'UnpaidBasicTime' THEN 'PERHEENJASENEN_SAIRASTUMINEN_PALKATON' -- Palkaton perheenjäsenen äkillinen sairastuminen
WHEN AbsenceTypeId = 7 AND CompensationType = 'UnpaidBasicTime' THEN 'PAKOTTAVAT_PERHESYYT_PALKATON' -- Palkaton poissaolo pakottavien perhesyiden takia
WHEN AbsenceTypeId = 450 AND CompensationType = 'UnpaidBasicTime' THEN 'RASKAUSVAPAA_PALKATON' -- Palkaton raskausvapaa
WHEN AbsenceTypeId = 6 AND CompensationType = 'UnpaidBasicTime' THEN 'TILAPAINEN_HOITOVAPAA_PALKATON' -- Palkaton tilapäinen hoitovapaa
WHEN AbsenceTypeId = 19 AND CompensationType = 'UnpaidBasicTime' THEN 'TOIMIVAPAA_PALKATON' -- Palkaton toimivapaa
WHEN AbsenceTypeId = 12 AND CompensationType = 'UnpaidBasicTime' THEN 'TYONTEKIJAN_PYYNTO_PALKATON' -- Palkaton työntekijän oma pyyntö
WHEN AbsenceTypeId = 451 AND CompensationType = 'UnpaidBasicTime' THEN 'VANHEMPAINVAPAA_PALKATON' -- Palkaton vanhempainvapaa
WHEN AbsenceTypeId = 4 AND CompensationType = 'UnpaidBasicTime' THEN 'VANHEMPAINVAPAA_PALKATON'-- Palkaton vanhempainvapaa
WHEN AbsenceTypeId = 23 AND CompensationType = 'UnpaidBasicTime' THEN 'VIRKAPAVAA_PALKATON' -- Palkaton virkavapaa
WHEN AbsenceTypeId = 9 AND CompensationType = 'UnpaidBasicTime' THEN 'VUOROTTELUVAPAA_PALKATON' -- Palkaton vuorotteluvapaa
WHEN AbsenceTypeId = 2 AND CompensationType = 'UnpaidBasicTime' THEN 'AITIYSVAPAA_PALKATON' -- Palkaton äitiysvapaa
WHEN AbsenceTypeId = 25 AND CompensationType = 'BasicTime' THEN 'AMMATTIYHDISTYSTOIMINTA_PALKALLINEN' -- Palkallinen ammattiyhdistystoiminta
WHEN AbsenceTypeId = 10 AND CompensationType = 'BasicTime' THEN 'ASEPALVELUS_PALKALLINEN' -- Palkallinen ase- tai siviilipalvelusaika
WHEN AbsenceTypeId = 1 AND CompensationType = 'BasicTime' THEN 'ERITYISAITIYSVAPAA_PALKALLINEN'-- Palkallinen erityisäitiysvapaa
WHEN AbsenceTypeId = 5 AND CompensationType = 'BasicTime' THEN 'HOITOVAPAA_PALKALLINEN'-- Palkallinen hoitovapaa
WHEN AbsenceTypeId = 3 AND CompensationType = 'BasicTime' THEN 'ISYYSVAPAA_PALKALLINEN'-- Palkallinen isyysvapaa
WHEN AbsenceTypeId = 11 AND CompensationType = 'BasicTime' THEN 'KERTAUS_PALKALLINEN'-- Palkallinen kertausharjoitus
WHEN AbsenceTypeId = 27 AND CompensationType = 'BasicTime' THEN 'KUNTOUTUS_PALKALLINEN' -- Palkallinen kuntoutus
WHEN AbsenceTypeId = 18 AND CompensationType = 'BasicTime' THEN 'KUTSUNTA_PALKALLINEN' -- Palkallinen kutsuntatilaisuus
WHEN AbsenceTypeId = 13 AND CompensationType = 'BasicTime' THEN 'MUU_POISSAOLO_PALKALLINEN' -- Palkallinen muu poissaolo
WHEN AbsenceTypeId = 26 AND CompensationType = 'BasicTime' THEN 'MUUTTOVAPAA_PALKALLINEN' -- Palkallinen muuttovapaa
WHEN AbsenceTypeId = 452 AND CompensationType = 'BasicTime' THEN 'OMAISHOITOVAPAA_PALKALLINEN' -- Palkallinen omaishoitovapaa
WHEN AbsenceTypeId = 17 AND CompensationType = 'BasicTime' THEN 'OMAT50_60_JUHLAT_PALKALLINEN' -- Palkallinen oma 50- tai 60-vuotisjuhla
WHEN AbsenceTypeId = 16 AND CompensationType = 'BasicTime' THEN 'PARISUHTEEN_REKISTEROINTI_PALKALLINEN' -- Palkallinen omat vihkiäiset / parisuhteen rekisteröinti
WHEN AbsenceTypeId = 8 AND CompensationType = 'BasicTime' THEN 'OPINTOVAPAA_PALKALLINEN' -- Palkallinen opintovapaa
WHEN AbsenceTypeId = 22 AND CompensationType = 'BasicTime' THEN 'OSA_AIKAELAKE_PALKALLINEN' -- Palkallinen osa-aikaeläke
WHEN AbsenceTypeId = 31 AND CompensationType = 'BasicTime' THEN 'OSA-AIKAISUUS_PALKALLINEN' -- Palkallinen osa-aikaisuus
WHEN AbsenceTypeId = 14 AND CompensationType = 'BasicTime' THEN 'PERHEENJASENEN_KUOLEMA_PALKALLINEN' -- Palkallinen perheenjäsenen kuolema
WHEN AbsenceTypeId = 15 AND CompensationType = 'BasicTime' THEN 'HAUTAJAISET_PALKALLINEN' -- Palkallinen perheenjäsenen tai lähiomaisen hautajaiset
WHEN AbsenceTypeId = 20 AND CompensationType = 'BasicTime' THEN 'PERHEENJASENEN_SAIRASTUMINEN_PALKALLINEN' -- Palkallinen perheenjäsenen äkillinen sairastuminen
WHEN AbsenceTypeId = 7 AND CompensationType = 'BasicTime' THEN 'PAKOTTAVAT_PERHESYYT_PALKALLINEN' --Palkallinen poissaolo pakottavien perhesyiden takia
WHEN AbsenceTypeId = 450 AND CompensationType = 'BasicTime' THEN 'RASKAUSVAPAA_PALKALLINEN' --Palkallinen raskausvapaa
WHEN AbsenceTypeId = 6 AND CompensationType = 'BasicTime' THEN 'TILAPAINEN_HOITOVAPAA_PALKALLINEN' -- Palkallinen tilapäinen hoitovapaa
WHEN AbsenceTypeId = 19 AND CompensationType = 'BasicTime' THEN 'TOIMIVAPAA_PALKALLINEN' -- Palkallinen toimivapaa
WHEN AbsenceTypeId = 12 AND CompensationType = 'BasicTime' THEN 'TYONTEKIJAN_PYYNTO_PALKALLINEN' -- Palkallinen työntekijän oma pyyntö
WHEN AbsenceTypeId = 451 AND CompensationType = 'BasicTime' THEN 'VANHEMPAINVAPAA_PALKALLINEN' -- Palkallinen vanhempainvapaa
WHEN AbsenceTypeId = 4 AND CompensationType = 'BasicTime' THEN 'VANHEMPAINVAPAA_PALKALLINEN'-- Palkallinen vanhempainvapaa
WHEN AbsenceTypeId = 23 AND CompensationType = 'BasicTime' THEN 'VIRKAPAVAA_PALKALLINEN' -- Palkallinen virkavapaa
WHEN AbsenceTypeId = 9 AND CompensationType = 'BasicTime' THEN 'VUOROTTELUVAPAA_PALKALLINEN' -- Palkallinen vuorotteluvapaa
WHEN AbsenceTypeId = 2 AND CompensationType = 'BasicTime' THEN 'AITIYSVAPAA_PALKALLINEN' -- Palkallinen äitiysvapaa
-- put below this line any customer specific absence events
--put above this line any customer specific absence events
WHEN AbsenceTypeId IS NOT NULL THEN ('Poissaolo: ' || AbsenceTypeId || ' - ' || CompensationType) --this row picks up all absence types set up by the customer without a specific salary code set
ELSE
'NOT DEFINED'
END AS OverrideSalaryCode
FROM UserSalaryData
WHERE CompensationType IN ('BasicTime','UnpaidBasicTime')
AND AbsenceTypeID IS NOT NULL
AND IsApproved = 'Y'
--
-- Alla oleva ehto jättää tuloksista pois ne päivät, joissa työaikaa on korvattu jonain muuna kuin poissaolona
AND
SalaryRenderingDate IN
(SELECT SalaryRenderingDate
FROM UserSalaryData
WHERE AbsenceTypeId IS NULL
AND CompensationType IN ('BasicTime', 'ReducedRateTime', 'UnpaidBasicTime', 'AdditionalWork', 'OvertimeDaily50', 'OvertimeDaily100', 'OvertimeWeekly50', 'OvertimeWeekly100', 'OvertimeSunday50', 'OvertimeSunday100', 'PublicHoliday', 'YearLeave'))
--
GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
HAVING Overridesalarycode != 0
Viikkolepo, arkipyhä
Poimitaan viikkolepo, arkipyhä
SELECT *, SUM(Amount) As OverrideAmount,
CASE
WHEN CompensationType = 'WeeklyRestTime' THEN 'VIIKKOLEPO'
WHEN CompensationType = 'PublicHoliday' THEN 'ARKIPYHÄ'
END AS OverrideSalaryCode
-- put below this line any changes to the columns queried / overrides
-- put above this line any changes to the columns queried / overrides
FROM UserSalaryData
WHERE CompensationType IN ('WeeklyRestTime', 'PublicHoliday')
AND IsApproved = 'Y'
GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
HAVING Overridesalarycode != 0
Varallaolo vähennettynä varallaoloaikana tehdyllä työllä
Alla oleva kysely poimii varallaoloajan. Varallaoloajan päälle kirjatut tapahtumat vähennetään varallaoloajasta, mutta järjestelmän asetusten kautta on mahdollista määrittää että tietyt tapahtumatyypit eivät vähennä varallaoloaikaa. Varallaolon asetuksista voit lukea lisää täältä
Tarvittaessa voit lisätä kyselyyn ryhmittelyn (GROUP BY) ja määrän yhteenlaskun mikäli et halua jokaista yksittäistä kestoa ja ajanjaksoa.
Huom. Tämä esimerkki muuttui järjestelmän 2.12.0 päivityksen myötä. Vanhentunut malli toimii edelleen mutta sen käyttämista uusille liittymille ei suositella. Vanhan mallin mukaisen esimerkin löydät täältä
SELECT *
FROM UserSalaryData
WHERE CompensationType = 'OnCall'
Aloitus- ja lopetustyö
SELECT *
FROM UserSalaryData
WHERE CompensationType = 'StartupWork'
SELECT *
FROM UserSalaryData
WHERE CompensationType = 'TerminationWork'
Muita esimerkkipoimintoja
Esimerkkipoiminnoissa on yksittäisiä esimerkkejä erilaisiin erikoistilanteisiin. Näitä esimerkkejä voi soveltaa mallissa, mikäli asiakaskohtainen toteutus vaatii esimerkissä kuvatun kaltaisia poikkeuksia.
Poimitaan työaika siten, että muodostetaan eri kustannuspaikan aineisto riippuen siitä päivästä milloin aineisto muodostetaan
Where - ehdossa on kuvattu että 6. päivä poimitaan kustannuspaikan 10 henkilöt ja 14. päivä poimitaan kustannuspaikan 20 henkilöt.
SELECT *, SUM(Amount) As OverrideAmount
FROM UserSalaryData
WHERE CompensationType = 'BasicTime'
AND CASE --in here define the list of day numbers and the corresponding UserCostGroup we wish to pick
WHEN strftime('%d', 'now') = '06' THEN UserCostGroup = '10'
WHEN strftime('%d','now') = '14' THEN UserCostGroup = '20'
END
GROUP BY SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
Poimitaan kertymän arvo kuukauden viimeisenä päivänä
Where - ehdossa on kuvattu, että laskentapäivän tulee olla kuukauden viimeinen päivä. Vain silloin muodostuu tulos, johon poimitaan saldo-kertymän arvo.
SELECT *, SUM(Amount) As OverrideAmount
FROM UserSalaryData
WHERE
CompensationType = 'Accrual'
AND AccrualType = 'Balance'
AND SalaryRenderingDate = date(salaryrenderingDate, 'start of month', '+1 month', '-1 day') -- this rule calculates the last day of the month
GROUP BY SalaryRenderingDate
Poimitaan henkilöryhmät mukaan aineistoon
Where - ehdossa on kuvattu että poimitaan henkilöryhmät A100, B100, C100, D100.
SELECT ...,
IFNULL(
(
SELECT ui.Value FROM UserInfoData ui
WHERE ui.InfoTypeName = 'UserGroupCode_SDSQL' AND
datetime(UserSalaryData.SalaryRenderingDate) BETWEEN ui.ValidFrom AND ui.ValidTo
LIMIT 1
),
''
) AS _UserGroupCode,
FROM UserSalaryData
WHERE _UserGroupCode IN ('A100', 'B100', 'C100', 'D100')
Poimitaan määräksi se tieto kuinka monena päivänä henkilö on tehnyt säännöllistä työaikaa tai ylitöitä
SELECT *, COUNT(DISTINCT(SalaryRenderingDate)) * 3600 As OverrideAmount -- kerrotaan tulos arvolla 3600 jotta liittymässä 1 päivä = 1 tunti (3600 sekuntia)
FROM UserSalaryData
WHERE CompensationType IN ('BasicTime', 'AdditionalWork', 'OvertimeDaily50',
'OvertimeDaily100', 'OvertimeWeekly50', 'OvertimeWeekly100', 'OvertimeSunday50',
'OvertimeSunday100')
AND BasePartTarget IN ('salary','bank','balance','flexiblehours')
AND ActivityTypeCode = '1'
GROUP BY UserId HAVING OverrideAmount > 0
Poimitaan tietoa siten, että projektikoodiksi voidaan määritellä arvo perustuen työlisän numeroon
SELECT *, SUM(amount) AS OverrideAmount,
CASE
WHEN WorkIncrementNumber = 10 THEN '5000'
WHEN WorkIncrementNumber = 30 THEN '6000'
WHEN WorkIncrementNumber = 50 THEN '7000'
ELSE
'0'
)
END AS OverrideProjectCode1
FROM UserSalaryData WHERE ActivityTypeCode = 13
Poimitaan tietoa siten, että projektikoodiksi määritellään projektin ulkoinen tunniste
SELECT *, SUM(amount) AS OverrideAmount,
ProjectExternalIdentifier1 AS OverrideProjectCode1
FROM UserSalaryData WHERE ActivityTypeCode = 13
Poimitaan 'saldon muutos' tapahtumat ja siirretään arvona muutoksen itseisarvo
SELECT *, ABS(amount) AS OverrideAmount
FROM UserEventData WHERE ActivityTypeCode = '13'
Poimitaan matkalasku (myös muut työlisät poimitaan, ellei WorkIncrementType-kohtaa päivitetä, kts. alla)
SELECT
*,
SUM(amount) as OverrideAmount,
WorkIncrementCode as OverrideSalaryCode -- Use the salary code from the Työlisät settings
FROM
UserSalaryData
WHERE
CompensationType = 'WorkIncrement'
AND WorkIncrementCode IS NOT NULL -- Export all supplements unless they have been explicitly set not to be exported
AND WorkIncrementType = ??? -- If you ONLY want travel invoice supplements you can specify the types you wish here
GROUP BY
SalaryRenderingDate,
Workincrementcode,
UnitType -- Ensures that if the supplement type changes, the old and new values aren't summed together
Poimitaan kertymät
SELECT *, Amount / 60 AS OverrideAmount
FROM UserSalaryData
WHERE compensationType = 'Accrual'
AND AccrualType = 'balance' --Tässä poimitaan ainoastaan Saldo
Poimitaan lomarahavapaa myös lauantailta, jolla ei työvelvoitetta
Tässä säännössä poimitaan poikkeuksellisesti merkittyä työaikaa (''MarkedTime'') päivinä. Sääntöön on tehty myös poikkeus, että sunnuntaita ei oteta mukaan lukuun eli aineistolle nousee ainoastaan päivät maanantaista lauantaihin.
SELECT
*,
COUNT(
DISTINCT(SalaryRenderingDate)
) AS OverrideAmount,
'J' AS OverrideUnitType
FROM
UserSalaryData
WHERE
ActivityTypeCode = 164 --Lomarahavapaa-tapahtumatyypin koodi
AND CompensationType = 'MarkedTime'
AND Weekday(StartDateTime) != 6
GROUP BY
userid,
ProjectCode1,
salaryrenderingdate
Poimitaan työpäivät
Poimitaan määräksi summa niistä palkkapäivistä, joille on syntynyt korvattavaa aikaa. Palkkalajiksi muodostuu arvo '1' silloin kun käsiteltävä päivä on samalla kuukaudella kuin käsiteltävän jakson viimeinen päivä. Muulloin palkkalajiksi muodostuu arvo '2'.
SELECT (
CASE
WHEN ( SELECT strftime('%m', MAX(salaryrenderingdate))
FROM UserSalaryData ) = strftime('%m', salaryrenderingdate) THEN 1
ELSE 2 END) as OverrideSalaryCode,
*,
COUNT(DISTINCT(SalaryRenderingDate)) As OverrideAmount
FROM UserSalaryData
WHERE compensationtype IN ('BasicTime', 'AdditionalWork', 'OvertimeDaily50',
'OvertimeDaily100', 'OvertimeWeekly50', 'OvertimeWeekly100')
AND BasePartTarget IN ('salary','bank','balance','flexiblehours')
AND EmployeeSalaryType = 'hourly'
group by overridesalarycode
Poimitaan matkalaskun matkojen tietoja
Poiminnassa oleva char(10) on rivinvaihtomerkki LF.
SELECT e.*,
t.StartDateTime AS OverrideStartDatetime,
t.EndDateTime AS OverrideEndDatetime,
t.StartLocation || ' - ' || t.EndLocation || char(10) || t.ReasonForTrip AS OverrideComment
FROM UserEventData e
JOIN UserTravelExpenseTripData t ON (t.ActivityId = e.ActivityId)
ORDER BY e.StartDateTime, t.TripNumber