Payroll

Setupsource
Sources: OWS_DTP

Workspace: 001 · Payroll Planning (EU)

Activities 4source
Sources: OWS_ATTIVITA, OWS_OPERAZIONE

001

001 — Input Validations

Level 0 · Target: 000016 Input _ Calculations

clears target

3 ops

▾

#	Code	Description	Type	Source	Target / Method	Mappings
0	`000001`	Loading Data	Insert from other dataset	`000001` Roster Planning	—	—
1	`000002`	Date not null as SALINC > 0	Special method	—	`Input Validation` INCDATE / SALINC coherence	—
2	`000003`	FTE greater than 0	Special method	—	`Input Validation 2` FTE greater than 0	—

002

002 — Payroll Engine

Level 1 · Target: 000016 Engine _ Calculations

clears target

6 ops

▾

# Code Description Type Source Target / Method Mappings

000001

Monthly Salary and FTE

Insert from other dataset

000016 Input

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`LIFE`	`LIFE`	—
`FTECHNG`	`FTECHNG`	—
`MED`	`MED`	—
`DEPT`	`DEPT`	—
`INCDATE`	`INCDATE`	—
`EMPL`	`EMPL`	—
`FTE`	`FTE`	—
`BASE`	`BASE`	—
`HIRE`	`HIRE`	—
`POS`	`POS`	—
`SALINC`	`SALINC`	—
`CHNGDATE`	`CHNGDATE`	—
`SAL_07`	`round(case when INCDATE is null or extract(month from INCDATE) > 7 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_09`	`round(case when INCDATE is null or extract(month from INCDATE) > 9 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_01`	`round(case when INCDATE is null or extract(month from INCDATE) > 1 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_04`	`round(case when INCDATE is null or extract(month from INCDATE) > 4 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_03`	`round(case when INCDATE is null or extract(month from INCDATE) > 3 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_08`	`round(case when INCDATE is null or extract(month from INCDATE) > 8 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_02`	`round(case when INCDATE is null or extract(month from INCDATE) > 2 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_06`	`round(case when INCDATE is null or extract(month from INCDATE) > 6 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_12`	`round(case when INCDATE is null or extract(month from INCDATE) > 12 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_10`	`round(case when INCDATE is null or extract(month from INCDATE) > 10 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_05`	`round(case when INCDATE is null or extract(month from INCDATE) > 5 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`SAL_11`	`round(case when INCDATE is null or extract(month from INCDATE) > 11 then BASE/12 else (BASE + (BASE*SALINC))/12 end)`	—
`FTE_01`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 1 then FTE else FTE + FTECHNG end`	—
`FTE_02`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 2 then FTE else FTE + FTECHNG end`	—
`FTE_05`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 5 then FTE else FTE + FTECHNG end`	—
`FTE_04`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 4 then FTE else FTE + FTECHNG end`	—
`FTE_06`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 6 then FTE else FTE + FTECHNG end`	—
`FTE_07`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 7 then FTE else FTE + FTECHNG end`	—
`FTE_08`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 8 then FTE else FTE + FTECHNG end`	—
`FTE_09`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 9 then FTE else FTE + FTECHNG end`	—
`FTE_12`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 12 then FTE else FTE + FTECHNG end`	—
`FTE_11`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 11 then FTE else FTE + FTECHNG end`	—
`FTE_10`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 10 then FTE else FTE + FTECHNG end`	—
`FTE_03`	`case when CHNGDATE is null or extract(month from CHNGDATE) > 3 then FTE else FTE + FTECHNG end`	—

1 000002 Medical Plan Calculation Enrichment with formula — —

Target field	WHEN condition	THEN	ELSE	Grp
`MP_AMOUNT`	—	case when MED = '0' then 800 when MED = '1' then 500 end	—	—

2 000003 Life Plan Calculation Enrichment with formula — —

Target field	WHEN condition	THEN	ELSE	Grp
`LP_AMOUNT`	—	case when LIFE = '0' then 30 when LIFE = '1' then 25 when LIFE = '2' then 8 end	—	—

3 000004 Range Salary Attribution Enrichment with formula — —

Target field	WHEN condition	THEN	ELSE	Grp
`RANGE_TYPE`	—	case when (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) <= 50000 then 'A' when (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) <= 70000 and (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) > 50000 then 'B' when (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) <= 100000 and (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) > 70000 then 'C' end	—	—

4 000005 Range Salary attribution Enrichment with formula — RANGE_TYPE is null

Target filter · RANGE_TYPE is null

Target field	WHEN condition	THEN	ELSE	Grp
`RANGE_TYPE`	—	case when (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) <= 150000 and (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) > 100000 then 'D' when (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) <= 200000 and (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) > 150000 then 'E' when (SAL_01 + SAL_02 + SAL_03 + SAL_04 + SAL_05 + SAL_06 + SAL_07 + SAL_08 + SAL_09 + SAL_10 + SAL_11 + SAL_12) > 200000 then 'F' end	—	—

000006

Labor and Health Insurance, Retirement fund based on salary range

Enrichment with reference dataset

ref 000009

Base Salary Ranges

—

004

004 — Base Salary and FTE

Level 2 · Target: 000013 Calculation Output Salaries and Wages

clears target

12 ops

▾

# Code Description Type Source Target / Method Mappings

000001

Moving Period 1

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`FTE`	`FTE_01`	—
`PER`	`'01'`	—
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SAL`	`SAL_01`	—
`SCE`	`'2019BDG'`	—

000002

Moving Period 2

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SCE`	`'2019BDG'`	—
`SAL`	`SAL_02`	—
`PER`	`'02'`	—
`FTE`	`FTE_02`	—

000003

Moving Period 3

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`EMPL`	`EMPL`	—
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`PER`	`'03'`	—
`SAL`	`SAL_03`	—
`SCE`	`'2019BDG'`	—
`FTE`	`FTE_03`	—

000004

Moving Period 4

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SAL`	`SAL_04`	—
`PER`	`'04'`	—
`SCE`	`'2019BDG'`	—
`FTE`	`FTE_04`	—

000005

Moving Period 5

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SCE`	`'2019BDG'`	—
`SAL`	`SAL_05`	—
`PER`	`'05'`	—
`FTE`	`FTE_05`	—

000006

Moving Period 6

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`EMPL`	`EMPL`	—
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`SCE`	`'2019BDG'`	—
`SAL`	`SAL_06`	—
`PER`	`'06'`	—
`FTE`	`FTE_06`	—

000007

Moving Period 7

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SCE`	`'2019BDG'`	—
`SAL`	`SAL_07`	—
`PER`	`'07'`	—
`FTE`	`FTE_07`	—

000008

Moving Period 8

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SCE`	`'2019BDG'`	—
`SAL`	`SAL_08`	—
`PER`	`'08'`	—
`FTE`	`FTE_08`	—

000009

Moving Period 9

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SCE`	`'2019BDG'`	—
`SAL`	`SAL_09`	—
`PER`	`'09'`	—
`FTE`	`FTE_09`	—

000010

Moving Period 10

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SAL`	`SAL_10`	—
`PER`	`'10'`	—
`FTE`	`FTE_10`	—
`SCE`	`'2019BDG'`	—

000011

Moving Period 11

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SAL`	`SAL_11`	—
`PER`	`'11'`	—
`SCE`	`'2019BDG'`	—
`FTE`	`FTE_11`	—

000012

Moving Period 12

Insert from other dataset

000016 Engine

_ Calculations

—

Target field	Source field	Group by
`ENT`	`ENT`	—
`DEPT`	`DEPT`	—
`EMPL`	`EMPL`	—
`SAL`	`SAL_12`	—
`PER`	`'12'`	—
`SCE`	`'2019BDG'`	—
`FTE`	`FTE_12`	—

005

005 — Health Insurance

Level 3 · Target: 000013 Calculation Output Salaries and Wages

Update Reporting Layer

2 ops

▾

# Code Description Type Source Target / Method Mappings

000001

Monthly Health Insurance

Enrichment from other dataset

000016 Engine

_ Calculations

PER = '01'

Target filter · PER = '01'

Target field	Source field	Group by
`EMPL`	`EMPL`	—
`DEPT`	`DEPT`	—
`ENT`	`ENT`	—
`HEALTH`	`HEALTH(CASE WHEN FTE_01 > 0 THEN 1 ELSE 0 END)0.06`	—

000002

Monthly Health Insurance

Enrichment from other dataset

000016 Engine

_ Calculations

PER = '02'

Target filter · PER = '02'

Target field	Source field	Group by
`DEPT`	`DEPT`	—
`HEALTH`	`HEALTH(CASE WHEN FTE_02> 0 THEN 1 ELSE 0 END)0.06`	—
`ENT`	`ENT`	—
`EMPL`	`EMPL`	—

SetupsourceSources: OWS_DTP

Activities 4sourceSources: OWS_ATTIVITA, OWS_OPERAZIONE

Workflow task lists 1

Setupsource
Sources: OWS_DTP

Activities 4source
Sources: OWS_ATTIVITA, OWS_OPERAZIONE