1 Package Body hr_fr_mmo as
2 /* $Header: hrfrmmo.pkb 120.0 2005/05/30 21:03:47 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_fr_mmo';
7 --
8 -- ---------------------------------------------------------------------------
9 -- |---------------------< Get_formula >--------------------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 Function Get_formula (p_business_group_id IN Number,
13 p_session_date IN date) Return Number IS
14 --
15 -- Define Local variable
16 ln_formula_id ff_formulas.formula_id%TYPE;
17 --
18 -- local cursor
19 Cursor Get_user_formula_id (p_business_group_id ff_formulas_f.business_group_id%TYPE,
20 p_formula_name ff_formulas_f.formula_name%TYPE) IS
21 SELECT ff.formula_id
22 FROM ff_formulas_f ff
23 WHERE ff.business_group_id = p_business_group_id and
24 ff.formula_name = p_formula_name and
25 (p_session_date BETWEEN ff.effective_start_date and ff.effective_end_date);
26 --
27
28 Cursor Get_template_formula_id (p_legislation_code ff_formulas_f.legislation_code%TYPE,
29 p_formula_name ff_formulas_f.formula_name%TYPE) IS
30 SELECT ff.formula_id
31 FROM ff_formulas_f ff
32 WHERE ff.legislation_code = p_legislation_code and
33 ff.formula_name = p_formula_name and
34 (p_session_date BETWEEN ff.effective_start_date and ff.effective_end_date);
35
36 Begin
37 -- First, check if there is a formula called USER_MMO_REASON for the user's Business_group
38 OPEN Get_user_formula_id (p_business_group_id,'USER_MMO_REASON');
39 FETCH Get_user_formula_id into ln_formula_id;
40 if (Get_user_formula_id%NOTFOUND) Then
41 -- The customized formula has not been found. Get the Template formula_id
42 CLOSE Get_user_formula_id;
43 OPEN Get_template_formula_id ('FR','TEMPLATE_MMO_REASON');
44 FETCH Get_template_formula_id into ln_formula_id;
45 if (Get_template_formula_id%NOTFOUND) Then
46 -- Error. None of the formula has been created
47 ln_formula_id := 0;
48 End if;
49 CLOSE Get_template_formula_id;
50 Else
51 CLOSE Get_user_formula_id;
52 End if;
53
54
55 Return ln_formula_id;
56
57 End Get_formula;
58
59
60 --
61 -- ---------------------------------------------------------------------------
62 -- |---------------------< Get_start_date >-----------------------------------|
63 -- ----------------------------------------------------------------------------
64 --
65 Function Get_start_date
66 (p_person_id IN number
67 ,p_establishment_id IN number
68 ,p_end_date IN date
69 ,p_include_suspended IN varchar
70 ) RETURN date is
71 --
72 -- Define Local Variables
73 --
74 l_proc varchar2(72) := g_package||'Get_start_date';
75 l_date date;
76 --
77 Begin
78
79 /* Find the record which precedes the assignment data block and which does not meet the
80 Assignment Status Type and Establishment criteria
81 */
82 select max(a.effective_end_date) + 1
83 into l_date
84 from per_all_assignments_f a,
85 per_assignment_status_types s
86 where a.person_id = p_person_id
87 AND a.primary_flag = 'Y'
88 AND a.effective_end_date < p_end_date
89 AND a.assignment_status_type_id = s.assignment_status_type_id
90 AND (s.per_system_status not in
91 ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
92 OR a.establishment_id <> p_establishment_id);
93
94 if l_date is null then
95
96 /* If no record is found above then get the latest start date which does meet the criteria
97 for which there is no preceding record which meets the criteria
98 */
99
100 select max(a.effective_start_date)
101 into l_date
102 from per_all_assignments_f a,
103 per_assignment_status_types s
104 where a.person_id = p_person_id
105 AND a.primary_flag = 'Y'
106 AND a.effective_start_date < p_end_date
107 AND a.assignment_status_type_id = s.assignment_status_type_id
108 AND (s.per_system_status in
109 ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
110 AND a.establishment_id = p_establishment_id)
111 and not exists
112 (select null
113 from per_all_assignments_f a2,
114 per_assignment_status_types s2
115 where a2.person_id = p_person_id
116 AND a2.primary_flag = 'Y'
117 AND a2.effective_end_date = a.effective_start_date-1
118 AND a2.assignment_status_type_id = s2.assignment_status_type_id
119 AND (s2.per_system_status in
120 ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
121 AND a2.establishment_id = p_establishment_id));
122
123 end if;
124
125
126 Return l_date;
127 End Get_start_date;
128 --
129 -- ---------------------------------------------------------------------------
130 -- |---------------------< Get_end_date >-------------------------------------|
131 -- ----------------------------------------------------------------------------
132 --
133 Function Get_end_date
134 (p_person_id IN number
135 ,p_establishment_id IN number
136 ,p_start_date IN date
137 ,p_include_suspended IN VARCHAR
138 ) RETURN date is
139 --
140 -- Define Local Variables
141 --
142 l_proc varchar2(72) := g_package||'Get_end_date';
143 l_date date;
144 --
145 Begin
146
147 /* Find the record which succeeds the assignment data block and which does not meet the
148 Assignment Status Type and Establishment criteria
149 */
150 select min(a.effective_start_date) - 1
151 into l_date
152 from per_all_assignments_f a,
153 per_assignment_status_types s
154 where a.person_id = p_person_id
155 AND a.primary_flag = 'Y'
156 AND a.effective_start_date > p_start_date
157 AND a.assignment_status_type_id = s.assignment_status_type_id
158 AND a.assignment_status_type_id = s.assignment_status_type_id
159 AND (s.per_system_status not in
160 ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
161 OR a.establishment_id <> p_establishment_id);
162
163 if l_date is null then
164
165 /* If no record is found above then get the earliest end date which does meet the criteria
166 for which there is no succeeding record which meets the criteria
167 */
168 select min(a.effective_end_date)
169 into l_date
170 from per_all_assignments_f a,
171 per_assignment_status_types s
172 where a.person_id = p_person_id
173 AND a.primary_flag = 'Y'
174 AND a.effective_end_date > p_start_date
175 AND a.assignment_status_type_id = s.assignment_status_type_id
176 AND a.assignment_status_type_id = s.assignment_status_type_id
177 AND (s.per_system_status in
178 ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
179 AND a.establishment_id = p_establishment_id)
180 and not exists
181 (select null
182 from per_all_assignments_f a2,
183 per_assignment_status_types s2
184 where a2.person_id = p_person_id
185 AND a2.primary_flag = 'Y'
186 AND a2.effective_start_date = a.effective_end_date+1
187 AND a2.assignment_status_type_id = s2.assignment_status_type_id
188 AND (s2.per_system_status in
189 ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
190 AND a2.establishment_id = p_establishment_id));
191
192 end if;
193
194 Return l_date;
195
196 End get_end_date;
197 --
198 -- ---------------------------------------------------------------------------
199 -- |--------------------------< Get_reason >----------------------------------|
200 -- comment : the switch_starting_leaving_reason variable can ONLY be set
201 -- with 2 values, "S" to get the Starting reason, and "L" to get
202 -- the leaving reason.
203 -- ----------------------------------------------------------------------------
204 --
205 Function Get_reason
206 (p_assignment_id IN number
207 ,p_starting_date IN varchar2
208 ,p_formula_id IN NUMBER
209 ,p_switch_starting_leaving IN varchar2
210 ) RETURN varchar2 is
211 --
212 -- Define Local Variables
213 --
214 l_proc varchar2(72) := g_package||' Get_reason';
215 l_formula_id ff_formulas_f.formula_id%TYPE;
216 l_effective_start_date ff_formulas_f.effective_start_date%TYPE;
217 l_return_value varchar2(240);
218 l_inputs ff_exec.inputs_t;
219 l_outputs ff_exec.outputs_t;
220 --
221 Begin
222 hr_utility.set_location(' Entering:'||l_proc, 5);
223 --
224 -- Initialize the formula
225 --
226 hr_utility.set_location(' Initialize formula '||l_proc, 5);
227 select formula_id,effective_start_date
228 into l_formula_id,l_effective_start_date
229 from ff_formulas_f
230 where formula_id = p_formula_id;
231
232 hr_utility.set_location('formula id found : ' || to_char(l_formula_id),500);
233
234 ff_exec.init_formula (l_formula_id,
235 l_effective_start_date,
236 l_inputs,
237 l_outputs
238 );
239
240 hr_utility.set_location(' set context variables '||l_proc, 6);
241
242 if (l_inputs.first is not null) and (l_inputs.last is not null)
243 then
244 -- Set up context values for the formula
245 for l_in_cnt in
246 l_inputs.first..l_inputs.last
247 loop
248 hr_utility.set_location(' in the loop ... ' || to_char(l_in_cnt),7);
249 if l_inputs(l_in_cnt).name='ASSIGNMENT_ID' then
250 l_inputs(l_in_cnt).value := p_assignment_id;
251 hr_utility.set_location(' ASSIGNMENT_ID .. done' ,7);
252 end if;
253 if l_inputs(l_in_cnt).name='DATE_EARNED' then
254 l_inputs(l_in_cnt).value := p_starting_date;
255 hr_utility.set_location(' DATE_EARNED .. done' ,7);
256 end if;
257 if l_inputs(l_in_cnt).name='TRANSFER_DATE' then
258 l_inputs(l_in_cnt).value := p_starting_date;
259 hr_utility.set_location(' TRANSFER_DATE .. done' ,7);
260 end if;
261 if l_inputs(l_in_cnt).name='SWITCH_STARTING_LEAVING' then
262 l_inputs(l_in_cnt).value := p_switch_starting_leaving;
263 hr_utility.set_location('SWITCH_STARTING_LEAVING .. done' ,7);
264 end if;
265 end loop;
266 end if;
267
268 --
269 -- Run the formula
270 --
271
272 hr_utility.set_location(' Prior to execute the formula',8);
273 ff_exec.run_formula (l_inputs ,
274 l_outputs
275 );
276
277 hr_utility.set_location(' End run formula',9);
278
279 for l_out_cnt in
280 l_outputs.first..l_outputs.last
281 loop
282 if l_outputs(l_out_cnt).name = 'RETURN_REASON' then
283 l_return_value := l_outputs(l_out_cnt).value;
284 end if;
285 end loop;
286
287 hr_utility.set_location(' After run ..return value = ' || l_return_value,9);
288
289 Return l_return_value;
290 End Get_reason;
291 --
292
293 End hr_fr_mmo;
[Home] [Help]