forked from scrambldchannel/tm1-model-pony-music-backup
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Employee.rules
137 lines (116 loc) · 6.93 KB
/
Employee.rules
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
#Region System
FEEDSTRINGS;
SKIPCHECK;
UNDEFVALS;
#EndRegion
#UNDEFVALS;
['MeritValidation']=S:
IF(((DB('Employee',!organization,!EmployeeList,!Year,!Version,'Salary')<>0) & (DB('Employee',!organization,!EmployeeList,!Year,!Version,'Start Pd')@='')& (DB('Employee',!organization,!EmployeeList,!Year,!Version,'FTE')<>0)),
IF(DB('Employee',!organization,!EmployeeList,!Year,!Version,'Merit Pd')@='','Check','OK'),'OK');
['FTEValidation']=S:IF(((DB('Employee',!organization,!EmployeeList,!Year,!Version,'FTE')=0) & (DB('Employee',!organization,!EmployeeList,!Year,!Version,'Start Pd')@='')),
'OK',IF(((DB('Employee',!organization,!EmployeeList,!Year,!Version,'FTE')=0) & (DB('Employee',!organization,!EmployeeList,!Year,!Version,'Start Pd')@<>'')),'Check','OK'));
['Validation']=N:
IF((DB('Employee',!organization,!EmployeeList,!Year,!Version,'MeritValidation')@='Check') % (DB('Employee',!organization,!EmployeeList,!Year,!Version,'FTEValidation')@='Check'),1,0);
['ValidationFlag']=S:IF(['Validation']<>0,'Validation Error - Check highlighted employees','Input Valid');
['RowFormat']=S:IF(ELLEV('EmployeeList',!EmployeeList)=0,
IF(['Validation']<>0,IF((DB('Employee',!organization,!EmployeeList,!Year,!Version,'MeritValidation')@='Check') & (DB('Employee',!organization,!EmployeeList,!Year,!Version,'FTEValidation')@='Check'),'D',
IF(DB('Employee',!organization,!EmployeeList,!Year,!Version,'MeritValidation')@='Check','F','M')),
STR(ELLEV('EmployeeList',!EmployeeList),1,0)),STR(ELLEV('EmployeeList',!EmployeeList),1,0));
#Region Link rule: Emp:JCAsmpt - String
#Source cube: Job Code Assumptions
#Target cube: Employee
#Autogenerated LINK STRING 7D4C696E6B5F456D705F33414A4341736D7074
[] = S:IF(DIMIX('JobCodeAsmpt', !Employee) <> 0, DB('Job Code Assumptions',
DB('Employee', !organization, !EmployeeList, !Year, !Version,
'Job Code'), !Employee, !Year, !Version), CONTINUE);
#EndRegion
#Region Link rule: Emp:JCAsmpt - Numeric
#Source cube: Job Code Assumptions
#Target cube: Employee
#Autogenerated LINK NUMERIC 7D4C696E6B5F456D705F33414A4341736D7074
[] = N:IF(DIMIX('JobCodeAsmpt', !Employee) <> 0, DB('Job Code Assumptions',
DB('Employee', !organization, !EmployeeList, !Year, !Version,
'Job Code'), !Employee, !Year, !Version), CONTINUE);
#EndRegion
#Region Link rule: Emp:JTAsmpt - String
#Source cube: Job Type Assumption
#Target cube: Employee
#Autogenerated LINK STRING 7D4C696E6B5F456D705F33414A5441736D7074
[] = S:IF(DIMIX('JobTypeAssumption', !Employee) <> 0, DB('Job Type Assumption',
DB('Employee', !organization, !EmployeeList, !Year, !Version,
'Job Type'), !Year, !Version, !Employee), CONTINUE);
#EndRegion
#Region Link rule: Emp:JTAsmpt - Numeric
#Source cube: Job Type Assumption
#Target cube: Employee
#Autogenerated LINK NUMERIC 7D4C696E6B5F456D705F33414A5441736D7074
[] = N:IF(DIMIX('JobTypeAssumption', !Employee) <> 0, DB('Job Type Assumption',
DB('Employee', !organization, !EmployeeList, !Year, !Version,
'Job Type'), !Year, !Version, !Employee), CONTINUE);
#EndRegion
#Region Link rule: Emp:Comp - Numeric
#Source cube: Compensation
#Target cube: Employee
#Autogenerated LINK NUMERIC 7D4C696E6B5F456D705F3341436F6D70
['Employee':'Total Cost'] = N:DB('Compensation', !organization,
!EmployeeList, 'Year', !Year, !Version, 'Total Expense');
#EndRegion
#Region N-Calculation rules: Dimension:Employee
#Autogenerated CALC NUMERIC 456D706C6F796565
#Region Dimension:Employee, Member:New Salary
#Autogenerated MEMBERCALC NUMERIC 5B456D706C6F7965655D2E5B4E65772053616C6172795D
['Employee':'New Salary']=N:(['Employee':'Salary']*(1+(['Employee':'Merit % Final']\100)));
#EndRegion
#Region Dimension:Employee, Member:Salary
#Autogenerated MEMBERCALC NUMERIC 5B456D706C6F7965655D2E5B53616C6172795D
['Employee':'Salary']=N:IF((['Employee':'Current Salary']=0),['Employee':'50P Salary'],['Employee':'Current Salary']);
#EndRegion
#EndRegion
#Region C-Calculation rules: Dimension:Employee
#Autogenerated CALC CONSOLIDATED 456D706C6F796565
#Region Dimension:Employee, Member:Merit %
#Autogenerated MEMBERCALC CONSOLIDATED 5B456D706C6F7965655D2E5B4D6572697420255D
['Employee':'Merit %']=C:0;
#EndRegion
#EndRegion
['EmpID']=S:IF(DB('Employee',!organization,!EmployeeList,!Year,!Version,'Name/Desc')@<>'',DB('}ElementAttributes_organization',!organization,'id')|'-'| DB('}ElementAttributes_EmployeeList',!EmployeeList,'Caption_Default'),CONTINUE);
#Region Merit Rate
['Merit % Final']=N:IF(['FTE']<>0,(IF(DB('Employee',!organization,!EmployeeList,!Year,!Version,'Merit Pd')@='None',0,
IF(['Merit Adj %']<>0,['Merit Adj %'],['Merit %']))),STET);
#EndRegion
#Region User rules:Salary by Month: Jan-Dec
[{'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'}]=N:
#Region: Term/Transfer
IF(ATTRN('Month',DB('Employee',!Organization,!EmployeeList,!Year,!Version,'Term/xfer Pd'),'Number')>0,
IF(ATTRN('Month',DB('Employee',!Organization,!EmployeeList,!Year,!Version,'Term/xfer Pd'),'Number')<=ATTRN('Employee',!Employee,'Month'),0,
IF(ATTRN('Month',DB('Employee',!Organization,!EmployeeList,!Year,!Version,'Start Pd'),'Number')<=ATTRN('Employee',!Employee,'Month'),
['Salary']\12*['FTE'],0)),
#EndRegion
#Region:New Hire
IF(ATTRN('Month',DB('Employee',!Organization,!EmployeeList,!Year,!Version,'Start Pd'),'Number')>0,
IF(ATTRN('Month',DB('Employee',!Organization,!EmployeeList,!Year,!Version,'Start Pd'),'Number')<=ATTRN('Employee',!Employee,'Month'),
['Salary']\12*['FTE'],0),
#EndRegion
#Region:Existing Raise
IF(ATTRN('Month',DB('Employee',!Organization,!EmployeeList,!Year,!Version,'Merit Pd'),'Number')>ATTRN('Employee',!Employee,'Month'),
#% (DB('Employee',!Organization,!EmployeeList,!Year,!Version,'Merit Pd')@='None'),
['Salary']\12*['FTE'],
['Salary']\12*(1+DB('Employee',!Organization,!EmployeeList,!Year,!Version,'Merit % Final')\100)*['FTE'])));
#EndRegion
#EndRegion
['EmpID']=S:IF(DB('Employee',!organization,!EmployeeList,!Year,!Version,'Name/Desc')@<>'',DB('}ElementAttributes_organization',!organization,'id')|'-'| DB('}ElementAttributes_EmployeeList',!EmployeeList,'Caption_Default'),CONTINUE);
FEEDERS;
#Region Other Feeders
['FTE']=>['Calc Salary'];
['FTE']=>['Validation'];
['FTE']=>['RowFormat'];
#EndRegion
#Region FEEDER Compensation Calculations
['FTE']=>DB('Compensation',!organization,!EmployeeList,'Year',!Year,!Version,'FTE');
['FTE']=>DB('Compensation',!organization,!EmployeeList,'Year',!Year,!Version,'6000');
['FTE']=>DB('Compensation',!organization,!EmployeeList,'Year',!Year,!Version,'6005');
['FTE']=>DB('Compensation',!organization,!EmployeeList,'Year',!Year,!Version,'Medical Exp');
['FTE']=>DB('Compensation',!organization,!EmployeeList,'Year',!Year,!Version,'Other Benefits Exp');
['FTE']=>DB('Compensation',!organization,!EmployeeList,'Year',!Year,!Version,'FICA Exp');
['FTE']=>DB('Compensation',!organization,!EmployeeList,'Year',!Year,!Version,'Medicare Exp');
#EndRegion