1 PACKAGE BODY per_cagr_apply_results_pkg AS
2 /* $Header: pecgrapl.pkb 120.5.12010000.2 2008/08/06 09:06:23 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Package Types and Variables (globals)
6 -- ----------------------------------------------------------------------------
7 --
8
9 TYPE mapping_rec IS RECORD (parameter_name per_cagr_api_parameters.parameter_name%TYPE
10 ,value VARCHAR2(30));
11
12 TYPE asg_rec is record (cagr_entitlement_result_id NUMBER(15)
13 ,assignment_id NUMBER(11)
14 ,cagr_entitlement_item_id NUMBER(11)
15 ,cagr_api_id NUMBER(15)
16 ,cagr_api_param_id NUMBER(15)
17 ,value VARCHAR2(240)
18 ,units_of_measure VARCHAR2(60));
19
20 TYPE pys_rec is record (cagr_entitlement_result_id NUMBER(15)
21 ,assignment_id NUMBER(11)
22 ,cagr_entitlement_item_id NUMBER(11)
23 ,cagr_api_id NUMBER(15)
24 ,cagr_api_param_id NUMBER(15)
25 ,category_name VARCHAR2(30)
26 ,grade_spine_id NUMBER(15)
27 ,parent_spine_id NUMBER(15)
28 ,step_id NUMBER(15)
29 ,from_step_id NUMBER(15)
30 ,to_step_id NUMBER(15)
31 ,value VARCHAR2(240)
32 ,units_of_measure VARCHAR2(60));
33
34 TYPE pay_rec is record (cagr_entitlement_result_id NUMBER(15)
35 ,assignment_id NUMBER(11)
36 ,cagr_entitlement_item_id NUMBER(11)
37 ,category_namec VARCHAR2(30)
38 ,element_type_id NUMBER(10)
39 ,input_value_id NUMBER(10)
40 ,value VARCHAR2(240)
41 ,multiple_entries_allowed_flag VARCHAR2(30));
42
43 TYPE t_ASG_results IS TABLE OF asg_rec
44 INDEX BY BINARY_INTEGER;
45
46 TYPE t_PYS_results IS TABLE OF pys_rec
47 INDEX BY BINARY_INTEGER;
48
49 TYPE t_PAY_results IS TABLE OF pay_rec
50 INDEX BY BINARY_INTEGER;
51
52 TYPE t_mapping_table IS TABLE OF mapping_rec INDEX BY BINARY_INTEGER;
53
54 g_pkg constant varchar2(33) := 'PER_CAGR_APPLY_ENTITLEMENTS_PKG.';
55 g_done_header boolean := FALSE;
56
57 --
58 -- ------------------------------------------------------------------------------
59 -- |------------------------------< initialise >--------------------------------|
60 -- ------------------------------------------------------------------------------
61
62 PROCEDURE initialise (p_params IN OUT NOCOPY per_cagr_evaluation_pkg.control_structure
63 ,p_select_flag IN varchar2 default 'B') IS
64
65 -- (record interface)
66 -- Coordinates denormalisation of per_cagr_entitlement_result records to HRMS tables.
67 -- Called by: PER_CAGR_APPLY_RESULTS_PKG.initialise (individual parameter interface) and
68 -- optionally by PER_CAGR_EVALUATION_PKG.initialise (dependent upon mode).
69 --
70 -- P_SELECT_FLAG now redundant - apply chosen, or beneficial if no chosen.
71 -- Defaults to processing all categories of result records existing in cache
72 -- for asg - cagr - effective_date params supplied, unless a specific category is
73 -- supplied to restrict processing.
74 --
75 -- P_SELECT_FLAG NOW REDUNDANT - apply chosen, or beneficial if no chosen.
76 -- (old processing: Selects the results according to select_flag
77 -- param, either 'B' (benefical_flag = Y) or 'C' (chosen_flag = 'Y').
78 --
79
80 TYPE assignment_rec IS RECORD (assignment_id number(15));
81
82 TYPE assignment_list IS TABLE OF assignment_rec
83 INDEX BY BINARY_INTEGER;
84
85 -- get the assignments for the SC mode process
86 CURSOR csr_assignments_to_process IS
87 SELECT assignment_id
88 FROM per_all_assignments_f asg
89 WHERE asg.collective_agreement_id = p_params.collective_agreement_id
90 AND p_params.effective_date BETWEEN asg.effective_start_date
91 AND asg.effective_end_date
92 AND asg.PRIMARY_FLAG = 'Y';
93
94 l_proc constant VARCHAR2(80) := g_pkg || 'initialise (rec)';
95 t_assignment_list assignment_list;
96 l_counter number(15) := 0;
97 l_parent_request_id number(15) := NULL;
98
99
100 -- ================================================================================================
101 -- == **************** GET_REQUEST_ID ***************** ==
102 -- ================================================================================================
103 FUNCTION get_request_id (p_assignment_id in number
104 ,p_effective_date in date) return number IS
105
106 --
107 -- Returns the request_id for the first result that is found in the cache for the assignment
108 -- on the effective_date or null. (The request_id must be for an 'SA' request only, as only these
109 -- logs are visible from PERWSCAR).
110 --
111 -- Called by initialise during SC mode, to update the correct requests logs .
112
113 CURSOR csr_request IS
114 SELECT cagr_request_id
115 FROM per_cagr_entitlement_results res
116 WHERE res.assignment_id = p_assignment_id
117 AND p_effective_date BETWEEN res.start_date and nvl(res.end_date,hr_general.end_of_time)
118 AND exists (select 'x'
119 from per_cagr_requests req
120 where req.cagr_request_id = cagr_request_id
121 and req.OPERATION_MODE = 'SA')
122 and rownum = 1;
123
124 l_proc constant VARCHAR2(80) := g_pkg || 'get_request_id';
125 l_request per_cagr_requests.cagr_request_id%type;
126
127 BEGIN
128 hr_utility.set_location('Entering: '||l_proc,10);
129
130 open csr_request;
131 fetch csr_request into l_request;
132 close csr_request;
133
134 hr_utility.set_location('Leaving: '||l_proc,40);
135 RETURN l_request;
136
137 END get_request_id;
138
139 -- ================================================================================================
140 -- == **************** GET_NUM_VAL ***************** ==
141 -- ================================================================================================
142
143 FUNCTION get_num_val(p_column_name IN VARCHAR2,
144 p_mapping_table IN OUT NOCOPY t_mapping_table) RETURN NUMBER IS
145
146 -- returns the numeric value from the array for the column_name
147 -- supplied, and deletes the rec.
148 l_proc constant VARCHAR2(80) := g_pkg || 'get_num_val';
149 l_return NUMBER := NULL;
150
151 BEGIN
152 hr_utility.set_location('Entering: '||l_proc,10);
153 FOR i in p_mapping_table.first..p_mapping_table.last LOOP
154 IF p_mapping_table(i).parameter_name = p_column_name THEN
155 -- found the correct column, so return the value
156 l_return := to_number(p_mapping_table(i).value);
157 exit;
158 END If;
159 END LOOP;
160 IF l_return IS NULL THEN
161 l_return := hr_api.g_number;
162 END IF;
163 hr_utility.set_location('Leaving:'||l_proc, 30);
164 RETURN l_return;
165
166 END get_num_val;
167
168 -- ================================================================================================
169 -- == **************** GET_CHAR_VAL ***************** ==
170 -- ================================================================================================
171
172 FUNCTION get_char_val(p_column_name IN VARCHAR2,
173 p_mapping_table IN OUT NOCOPY t_mapping_table) RETURN VARCHAR2 IS
174 -- returns the varchar value from the array for the
175 -- column_name supplied, and deletes the rec.
176 l_proc constant VARCHAR2(80) := g_pkg || 'get_char_val';
177 l_return VARCHAR2(240) := NULL;
178
179 BEGIN
180 hr_utility.set_location('Entering: '||l_proc,10);
181 FOR i in p_mapping_table.first..p_mapping_table.last LOOP
182 IF p_mapping_table(i).parameter_name = p_column_name THEN
183 -- found the correct column, so return the value
184 l_return := p_mapping_table(i).value;
185 exit;
186 END If;
187 END LOOP;
188 IF l_return IS NULL THEN
189 l_return := hr_api.g_varchar2;
190 END IF;
191 hr_utility.set_location('Leaving: '||l_proc,30);
192 RETURN l_return;
193
194 END get_char_val;
195
196 -- ================================================================================================
197 -- == **************** GET_DATE_VAL ***************** ==
198 -- ================================================================================================
199
200 FUNCTION get_date_val(p_column_name IN VARCHAR2
201 ,p_mapping_table IN OUT NOCOPY t_mapping_table) RETURN DATE IS
202 -- returns the date value from the array for the
203 -- column_name supplied, and deletes the rec.
204 l_proc constant VARCHAR2(80) := g_pkg || 'get_date_val';
205 l_return DATE := NULL;
206 BEGIN
207 hr_utility.set_location('Entering: '||l_proc,10);
208 FOR i in p_mapping_table.first..p_mapping_table.last LOOP
209 IF p_mapping_table(i).parameter_name = p_column_name THEN
210 -- found the correct column, so return the value
211 l_return := fnd_date.canonical_to_date(p_mapping_table(i).value);
212 exit;
213 END If;
214 END LOOP;
215 IF l_return IS NULL THEN
216 l_return := hr_api.g_date;
217 END IF;
218 hr_utility.set_location('Leaving: '||l_proc,30);
219 RETURN l_return;
220
221 END get_date_val;
222
223 -- ================================================================================================
224 -- == **************** GET_OVN_AND_MODE ***************** ==
225 -- ================================================================================================
226
227 PROCEDURE get_ovn_and_mode (p_table_name in varchar2
228 ,p_dt_flag in varchar2
229 ,p_pk in varchar2
230 ,p_pk_id in number
231 ,p_effective_date in date
232 ,p_mode out nocopy varchar2
233 ,p_ovn out nocopy number) IS
234 --
235 -- Determines ovn and dt mode (if applicable) for a record
236 --
237 -- DT UPD modes:
238 -- if record started today and no future changes then 'CORRECTION'
239 -- if record started before and no future changes then 'UPDATE'
240 -- if record started today and there are future changes then 'CORRECTION'
241 -- if record started before today and future changes then 'UPDATE_CHANGE_INSERT'.
242 -- note: update override is never used, i.e. we never replace all future changes
243 --
244
245 TYPE dyn_csr IS REF CURSOR;
246
247 l_dyn_csr dyn_csr;
248 l_sql VARCHAR2(240);
249 l_start_date date;
250 l_dummy VARCHAR2(1);
251 l_proc constant VARCHAR2(80) := g_pkg || 'get_ovn_and_mode';
252
253 BEGIN
254 hr_utility.set_location('Entering:'||l_proc, 5);
255
256 if p_dt_flag = 'Y' then
257 l_sql := 'SELECT object_version_number, effective_start_date FROM '||p_table_name||' WHERE '||p_pk||' = :1 ';
258 l_sql := l_sql ||'AND :2 BETWEEN effective_start_date AND nvl(effective_end_date,hr_general.end_of_time)';
259 hr_utility.set_location(l_proc, 10);
260 open l_dyn_csr for l_sql using p_pk_id, p_effective_date;
261 fetch l_dyn_csr into p_ovn, l_start_date;
262 close l_dyn_csr;
263 if l_start_date is null or p_ovn is null then
264 per_cagr_utility_pkg.put_log('ERROR: dynamic sql failed to return a value for OVN or START_DATE',1);
265 else
266 hr_utility.set_location(l_proc, 20);
267 p_mode := 'UPDATE'; -- default to this mode
268 if p_effective_date = l_start_date then
269 -- switch to correction if record started today
270 -- irrespective of any future changes
271 p_mode := 'CORRECTION';
272 else
273 hr_utility.set_location(l_proc, 30);
274 -- check for future updates
275 l_sql := 'SELECT null FROM '||p_table_name||' WHERE '||p_pk||' = :1 and :2 < effective_start_date';
276 open l_dyn_csr for l_sql using p_pk_id, p_effective_date;
277 fetch l_dyn_csr into l_dummy;
278 if l_dyn_csr%found then
279 p_mode := 'UPDATE_CHANGE_INSERT';
280 end if;
281 close l_dyn_csr;
282 end if;
283 end if;
284 else -- not date tracked
285 hr_utility.set_location(l_proc, 40);
286 l_sql := 'SELECT object_version_number FROM '||p_table_name||' WHERE '||p_pk||' = :1 ';
287 l_sql := l_sql || 'AND :2 BETWEEN start_date and nvl(end_date,hr_general.end_of_time)';
288 open l_dyn_csr for l_sql using p_pk_id, p_effective_date;
289 fetch l_dyn_csr into p_ovn;
290 close l_dyn_csr;
291 hr_utility.set_location(l_proc, 50);
292 if p_ovn is null then
293 per_cagr_utility_pkg.put_log('ERROR: dynamic sql failed to return a value for OVN',1);
294 end if;
295 end if;
296
297 hr_utility.set_location('Leaving:'||l_proc, 50);
298
299 END get_ovn_and_mode;
300
301
302 -- ================================================================================================
303 -- == **************** CALL_ASG_API ***************** ==
304 -- ================================================================================================
305
306 PROCEDURE call_ASG_api (p_params IN per_cagr_evaluation_pkg.control_structure
307 ,p_ASG_results IN t_ASG_results) IS
308
309
310 -- get API data (for seeded denormalised columns only)
311 -- i.e. not expecting to be seeding UOM columns separately
312 CURSOR csr_api (v_cagr_api_id in NUMBER) IS
313 SELECT apip.parameter_name
314 ,apip.cagr_api_param_id
315 ,apip.column_type
316 ,apip.uom_parameter
317 ,api.api_name
318 FROM per_cagr_api_parameters apip, per_cagr_apis api
319 WHERE api.cagr_api_id = v_cagr_api_id
320 AND api.cagr_api_id = apip.cagr_api_id;
321
322 TYPE t_api_details IS TABLE OF csr_api%ROWTYPE INDEX BY BINARY_INTEGER;
323
324 l_mapping_table t_mapping_table;
325 l_api_details t_api_details;
326 l_proc constant VARCHAR2(80) := g_pkg || 'call_ASG_api';
327 l_map_count NUMBER(10) := 0;
328 l_count NUMBER(10) := 0;
329
330 l_dt_mode VARCHAR2(30) := 'UPDATE';
331
332 -- local vars to catch out params
333 l_ovn NUMBER(10);
334 l_comment_id NUMBER;
335 l_soft_coding_keyflex_id NUMBER;
336 l_effective_start_date DATE;
337 l_effective_end_date DATE;
338 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
339 l_no_managers_warning BOOLEAN;
340 l_other_manager_warning BOOLEAN;
341 l_CAGR_GRADE_DEF_ID VARCHAR2(30);
342 l_CAGR_CONCATENATED_SEGMENTS VARCHAR2(30);
343
344 l_group_name VARCHAR2(30);
345 l_people_group_id NUMBER;
346 l_org_now_no_manager_warning BOOLEAN;
347 l_spp_delete_warning BOOLEAN;
348 l_entries_changed_warning VARCHAR2(30);
349 l_tax_district_changed_warning BOOLEAN;
350 l_special_ceiling_step_id NUMBER;
351 l_warn_message VARCHAR2(2000);
352
353 BEGIN
354 hr_utility.set_location('Entering:'||l_proc, 5);
355
356 -- build pl/sql table of all seeded api column names for the API id
357 for v_api_details in csr_api(p_ASG_results(1).cagr_api_id) loop
358 l_count := l_count+1;
359 l_api_details(l_count) := v_api_details;
360 end loop;
361 per_cagr_utility_pkg.put_log(' built api_details array of size: '||l_count);
362
363 -- loop thru each ent result and create mapping record(s)
364 for i in p_ASG_results.first..p_ASG_results.last loop
365 l_map_count := l_map_count+1;
366 l_mapping_table(l_map_count).value := p_ASG_results(i).value;
367 -- get the PARAMETER_NAME and UOM column from the API details structure
368 for j in l_api_details.first..l_api_details.last loop
369 if l_api_details(j).cagr_api_param_id = p_ASG_results(i).cagr_api_param_id then
370 l_mapping_table(l_map_count).parameter_name := l_api_details(j).parameter_name;
371 per_cagr_utility_pkg.put_log(' created mapping record: '||
372 l_mapping_table(l_map_count).parameter_name||'='||l_mapping_table(l_map_count).value);
373 -- also create a mapping table record, if there is a uom col
374 if l_api_details(j).uom_parameter is not null then
375 l_map_count := l_map_count+1;
376 l_mapping_table(l_map_count).parameter_name := l_api_details(j).uom_parameter;
377 l_mapping_table(l_map_count).value := p_ASG_results(i).units_of_measure;
378 per_cagr_utility_pkg.put_log(' created mapping record: '||
379 l_mapping_table(l_map_count).parameter_name||'='||l_mapping_table(l_map_count).value);
380 end if;
381 exit;
382 end if;
383 end loop;
384 end loop;
385
386 per_cagr_utility_pkg.put_log(' built mapping array of size: '||l_map_count);
387 if l_mapping_table.count > 0 then
388 if l_api_details(1).api_name = 'HR_ASSIGNMENT_API.UPDATE_EMP_ASG' then
389
390 get_ovn_and_mode (p_table_name => 'PER_ALL_ASSIGNMENTS_F'
391 ,p_dt_flag => 'Y'
392 ,p_pk => 'assignment_id'
393 ,p_pk_id => p_params.assignment_id
394 ,p_effective_date => p_params.effective_date
395 ,p_mode => l_dt_mode
396 ,p_ovn => l_ovn);
397
398 per_cagr_utility_pkg.put_log(' calling HR_ASSIGNMENT_API.UPDATE_EMP_ASG in mode: '||l_dt_mode||' , OVN: '||l_ovn,1);
399 BEGIN
400
401 hr_assignment_api.update_emp_asg
402 (p_effective_date => p_params.effective_date
403 ,p_datetrack_update_mode => l_dt_mode
404 ,p_assignment_id => p_params.assignment_id
405 ,p_object_version_number => l_ovn
406 ,p_normal_hours => get_num_val('NORMAL_HOURS',l_mapping_table)
407 ,p_frequency => get_char_val('FREQUENCY',l_mapping_table)
408 ,p_date_probation_end => null /*Bug 5125705: End date does not get updated, if not passed */
409 ,p_probation_period => get_num_val('PROBATION_PERIOD',l_mapping_table)
410 ,p_probation_unit => get_char_val('PROBATION_UNIT',l_mapping_table)
411 ,p_time_normal_start => get_char_val('TIME_NORMAL_START',l_mapping_table)
412 ,p_time_normal_finish => get_char_val('TIME_NORMAL_FINISH',l_mapping_table)
413 ,p_notice_period => get_num_val('NOTICE_PERIOD',l_mapping_table)
414 ,p_notice_period_uom => get_char_val('NOTICE_PERIOD_UOM',l_mapping_table)
415 ,p_bargaining_unit_code => get_char_val('BARGAINING_UNIT_CODE',l_mapping_table)
416 ,p_labour_union_member_flag => get_char_val('LABOUR_UNION_MEMBER_FLAG',l_mapping_table)
417 ,p_employee_category => get_char_val('EMPLOYEE_CATEGORY',l_mapping_table)
418 ,p_ASS_ATTRIBUTE_CATEGORY => get_char_val('ASS_ATTRIBUTE_CATEGORY',l_mapping_table)
419 ,p_ASS_ATTRIBUTE1 => get_char_val('ASS_ATTRIBUTE1',l_mapping_table)
420 ,p_ASS_ATTRIBUTE2 => get_char_val('ASS_ATTRIBUTE2',l_mapping_table)
421 ,p_ASS_ATTRIBUTE3 => get_char_val('ASS_ATTRIBUTE3',l_mapping_table)
422 ,p_ASS_ATTRIBUTE4 => get_char_val('ASS_ATTRIBUTE4',l_mapping_table)
423 ,p_ASS_ATTRIBUTE5 => get_char_val('ASS_ATTRIBUTE5',l_mapping_table)
424 ,p_ASS_ATTRIBUTE6 => get_char_val('ASS_ATTRIBUTE6',l_mapping_table)
425 ,p_ASS_ATTRIBUTE7 => get_char_val('ASS_ATTRIBUTE7',l_mapping_table)
426 ,p_ASS_ATTRIBUTE8 => get_char_val('ASS_ATTRIBUTE8',l_mapping_table)
427 ,p_ASS_ATTRIBUTE9 => get_char_val('ASS_ATTRIBUTE9',l_mapping_table)
428 ,p_ASS_ATTRIBUTE10 => get_char_val('ASS_ATTRIBUTE10',l_mapping_table)
429 ,p_ASS_ATTRIBUTE11 => get_char_val('ASS_ATTRIBUTE11',l_mapping_table)
430 ,p_ASS_ATTRIBUTE12 => get_char_val('ASS_ATTRIBUTE12',l_mapping_table)
431 ,p_ASS_ATTRIBUTE13 => get_char_val('ASS_ATTRIBUTE13',l_mapping_table)
432 ,p_ASS_ATTRIBUTE14 => get_char_val('ASS_ATTRIBUTE14',l_mapping_table)
433 ,p_ASS_ATTRIBUTE15 => get_char_val('ASS_ATTRIBUTE15',l_mapping_table)
434 ,p_ASS_ATTRIBUTE16 => get_char_val('ASS_ATTRIBUTE16',l_mapping_table)
435 ,p_ASS_ATTRIBUTE17 => get_char_val('ASS_ATTRIBUTE17',l_mapping_table)
436 ,p_ASS_ATTRIBUTE18 => get_char_val('ASS_ATTRIBUTE18',l_mapping_table)
437 ,p_ASS_ATTRIBUTE19 => get_char_val('ASS_ATTRIBUTE19',l_mapping_table)
438 ,p_ASS_ATTRIBUTE20 => get_char_val('ASS_ATTRIBUTE20',l_mapping_table)
439 ,p_ASS_ATTRIBUTE21 => get_char_val('ASS_ATTRIBUTE21',l_mapping_table)
440 ,p_ASS_ATTRIBUTE22 => get_char_val('ASS_ATTRIBUTE22',l_mapping_table)
441 ,p_ASS_ATTRIBUTE23 => get_char_val('ASS_ATTRIBUTE23',l_mapping_table)
442 ,p_ASS_ATTRIBUTE24 => get_char_val('ASS_ATTRIBUTE24',l_mapping_table)
443 ,p_ASS_ATTRIBUTE25 => get_char_val('ASS_ATTRIBUTE25',l_mapping_table)
444 ,p_ASS_ATTRIBUTE26 => get_char_val('ASS_ATTRIBUTE26',l_mapping_table)
445 ,p_ASS_ATTRIBUTE27 => get_char_val('ASS_ATTRIBUTE27',l_mapping_table)
446 ,p_ASS_ATTRIBUTE28 => get_char_val('ASS_ATTRIBUTE28',l_mapping_table)
447 ,p_ASS_ATTRIBUTE29 => get_char_val('ASS_ATTRIBUTE29',l_mapping_table)
448 ,p_ASS_ATTRIBUTE30 => get_char_val('ASS_ATTRIBUTE30',l_mapping_table)
449 ,p_SEGMENT1 => get_char_val('SEGMENT1',l_mapping_table)
450 ,p_SEGMENT2 => get_char_val('SEGMENT2',l_mapping_table)
451 ,p_SEGMENT3 => get_char_val('SEGMENT3',l_mapping_table)
452 ,p_SEGMENT4 => get_char_val('SEGMENT4',l_mapping_table)
453 ,p_SEGMENT5 => get_char_val('SEGMENT5',l_mapping_table)
454 ,p_SEGMENT6 => get_char_val('SEGMENT6',l_mapping_table)
455 ,p_SEGMENT7 => get_char_val('SEGMENT7',l_mapping_table)
456 ,p_SEGMENT8 => get_char_val('SEGMENT8',l_mapping_table)
457 ,p_SEGMENT9 => get_char_val('SEGMENT9',l_mapping_table)
458 ,p_SEGMENT10 => get_char_val('SEGMENT10',l_mapping_table)
459 ,p_SEGMENT11 => get_char_val('SEGMENT11',l_mapping_table)
460 ,p_SEGMENT12 => get_char_val('SEGMENT12',l_mapping_table)
461 ,p_SEGMENT13 => get_char_val('SEGMENT13',l_mapping_table)
462 ,p_SEGMENT14 => get_char_val('SEGMENT14',l_mapping_table)
463 ,p_SEGMENT15 => get_char_val('SEGMENT15',l_mapping_table)
464 ,p_SEGMENT16 => get_char_val('SEGMENT16',l_mapping_table)
465 ,p_SEGMENT17 => get_char_val('SEGMENT17',l_mapping_table)
466 ,p_SEGMENT18 => get_char_val('SEGMENT18',l_mapping_table)
467 ,p_SEGMENT19 => get_char_val('SEGMENT19',l_mapping_table)
468 ,p_SEGMENT20 => get_char_val('SEGMENT20',l_mapping_table)
469 ,p_SEGMENT21 => get_char_val('SEGMENT21',l_mapping_table)
470 ,p_SEGMENT22 => get_char_val('SEGMENT22',l_mapping_table)
471 ,p_SEGMENT23 => get_char_val('SEGMENT23',l_mapping_table)
472 ,p_SEGMENT24 => get_char_val('SEGMENT24',l_mapping_table)
473 ,p_SEGMENT25 => get_char_val('SEGMENT25',l_mapping_table)
474 ,p_SEGMENT26 => get_char_val('SEGMENT26',l_mapping_table)
475 ,p_SEGMENT27 => get_char_val('SEGMENT27',l_mapping_table)
476 ,p_SEGMENT28 => get_char_val('SEGMENT28',l_mapping_table)
477 ,p_SEGMENT29 => get_char_val('SEGMENT29',l_mapping_table)
478 ,p_SEGMENT30 => get_char_val('SEGMENT30',l_mapping_table)
479 ,P_CAGR_GRADE_DEF_ID => l_CAGR_GRADE_DEF_ID
480 ,P_CAGR_CONCATENATED_SEGMENTS => l_CAGR_CONCATENATED_SEGMENTS
481 ,p_comment_id => l_comment_id
482 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
483 ,p_effective_start_date => l_effective_start_date
484 ,p_effective_end_date => l_effective_end_date
485 ,p_concatenated_segments => l_concatenated_segments
486 ,p_no_managers_warning => l_no_managers_warning
487 ,p_other_manager_warning => l_other_manager_warning);
488
489 -- log any warnings
490 if l_no_managers_warning then
491 per_cagr_utility_pkg.put_log(' WARNING: p_no_managers_warning',1);
492 elsif l_other_manager_warning then
493 per_cagr_utility_pkg.put_log(' WARNING: p_other_manager_warning',1);
494 end if;
495 per_cagr_utility_pkg.put_log(' done HR_ASSIGNMENT_API.UPDATE_EMP_ASG, OVN: '||l_ovn,1);
496 EXCEPTION
497 when others then -- log unexpected API error, and continue
498 per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
499 --
500 -- To show the Dictionary message in the log file
501 --
502 per_cagr_utility_pkg.put_log(' Dictionary Message: '||fnd_message.get,1);
503 --
504 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
505 END;
506
507 elsif l_api_details(1).api_name = 'HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA' then
508
509 get_ovn_and_mode (p_table_name => 'PER_ALL_ASSIGNMENTS_F'
510 ,p_dt_flag => 'Y'
511 ,p_pk => 'assignment_id'
512 ,p_pk_id => p_params.assignment_id
513 ,p_effective_date => p_params.effective_date
514 ,p_mode => l_dt_mode
515 ,p_ovn => l_ovn);
516
517 per_cagr_utility_pkg.put_log(' calling HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA in mode: '||l_dt_mode||', OVN: '||l_ovn,1);
518 BEGIN
519 hr_assignment_api.update_emp_asg_criteria
520 (p_effective_date => p_params.effective_date
521 ,p_datetrack_update_mode => l_dt_mode
522 ,p_assignment_id => p_params.assignment_id
523 ,p_object_version_number => l_ovn
524 ,p_grade_id => get_num_val('GRADE_ID',l_mapping_table)
525 ,p_job_id => get_num_val('JOB_ID',l_mapping_table)
526 ,p_payroll_id => get_num_val('PAYROLL_ID',l_mapping_table)
527 ,p_organization_id => get_num_val('ORGANIZATION_ID',l_mapping_table)
528 ,p_employment_category => get_char_val('EMPLOYMENT_CATEGORY',l_mapping_table)
529 ,p_pay_basis_id => get_num_val('PAY_BASIS_ID',l_mapping_table)
530 ,p_special_ceiling_step_id => l_special_ceiling_step_id
531 ,p_group_name => l_group_name
532 ,p_effective_start_date => l_effective_start_date
533 ,p_effective_end_date => l_effective_end_date
534 ,p_people_group_id => l_people_group_id
535 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
536 ,p_other_manager_warning => l_other_manager_warning
537 ,p_spp_delete_warning => l_spp_delete_warning
538 ,p_entries_changed_warning => l_entries_changed_warning
539 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
540
541 -- log any warnings
542 if l_no_managers_warning then
543 per_cagr_utility_pkg.put_log(' WARNING: p_no_managers_warning',1);
544 elsif l_other_manager_warning then
545 per_cagr_utility_pkg.put_log(' WARNING: p_other_manager_warning',1);
546 elsif l_spp_delete_warning then
547 l_warn_message := fnd_message.get_string(
548 APPIN => 'PER'
549 , NAMEIN => 'HR_289826_SPP_DELETE_WARN_API');
550 per_cagr_utility_pkg.put_log(' WARNING: '||l_warn_message,1);
551 end if;
552 per_cagr_utility_pkg.put_log(' done HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA, OVN: '||l_ovn,1);
553 EXCEPTION
554 when others then -- log unexpected API error, and continue
555 per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
556 --
557 -- To show the Dictionary message in the log file
558 --
559 per_cagr_utility_pkg.put_log(' Dictionary Message: '||fnd_message.get,1);
560 --
561 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
562 END;
563
564 end if;
565 end if;
566
567 -- delete pl/sql tables
568 l_api_details.delete;
569 l_mapping_table.delete;
570
571 hr_utility.set_location('Leaving:'||l_proc, 5);
572 END call_ASG_api;
573
574 -- ================================================================================================
575 -- == **************** CALL_PYS_API ***************** ==
576 -- ================================================================================================
577 PROCEDURE call_PYS_api (p_params IN per_cagr_evaluation_pkg.control_structure
578 ,p_PYS_results IN t_PYS_results) IS
579
580 -- Identify which SPP api to call (create or update) and build mapping table from
581 -- result records and invoke the api to apply the result. (Only step_id in phase 1)
582 -- Limitation is that seed data should only allow user to denorm to update data columns
583 -- (as these will always be avail when updating or creating) allowing us to dynamically
584 -- switch from update to create api call, using update entitlement results.
585
586 -- get API data (for seeded denormalised columns only)
587 -- i.e. not expecting to be seeding UOM columns separately
588
589 CURSOR csr_api (v_api_name in varchar2) IS
590 SELECT apip.parameter_name
591 ,apip.cagr_api_param_id
592 ,apip.column_type
593 ,apip.uom_parameter
594 ,api.api_name
595 FROM per_cagr_api_parameters apip, per_cagr_apis api
596 WHERE api.api_name = v_api_name
597 AND api.cagr_api_id = apip.cagr_api_id;
598
599 -- get placement_id rec
600 CURSOR csr_placement_id IS
601 SELECT placement_id
602 FROM per_spinal_point_placements_f
603 WHERE assignment_id = p_params.assignment_id
604 AND p_params.effective_date BETWEEN effective_start_date and nvl(effective_end_date,hr_general.end_of_time);
605
606 TYPE t_api_details IS TABLE OF csr_api%ROWTYPE INDEX BY BINARY_INTEGER;
607
608 l_api_name per_cagr_apis.api_name%type;
609 l_mapping_table t_mapping_table;
610 l_api_details t_api_details;
611 l_proc constant VARCHAR2(80) := g_pkg || 'call_PYS_api';
612 l_map_count NUMBER(10) := 0;
613 l_count NUMBER(10) := 0;
614 l_switched_api BOOLEAN := FALSE;
615
616 l_dt_mode VARCHAR2(30) := 'UPDATE';
617 l_ovn NUMBER(10) := NULL;
618 l_placement_id NUMBER(10) := NULL;
619 l_step_id NUMBER(10) := NULL;
620 l_effective_start_date DATE;
621 l_effective_end_date DATE;
622
623 BEGIN
624 hr_utility.set_location('Entering:'||l_proc, 5);
625
626 -- first check if we need to call update or create api
627
628 open csr_placement_id;
629 fetch csr_placement_id into l_placement_id;
630 close csr_placement_id;
631
632 if l_placement_id is not null then
633 -- we will be updating spp
634 l_api_name := 'HR_SP_PLACEMENT_API.UPDATE_SPP';
635
636 get_ovn_and_mode (p_table_name => 'PER_SPINAL_POINT_PLACEMENTS_F'
637 ,p_dt_flag => 'Y'
638 ,p_pk => 'PLACEMENT_ID'
639 ,p_pk_id => l_placement_id
640 ,p_effective_date => p_params.effective_date
641 ,p_mode => l_dt_mode
642 ,p_ovn => l_ovn);
643 else
644 -- we will be creating spp
645 -- problem as there are no params seeded for it!!!
646 l_api_name := 'HR_SP_PLACEMENT_API.CREATE_SPP';
647 l_switched_api := TRUE;
648 end if;
649
650 -- build pl/sql table of all seeded api column names for the update API name
651 -- (as the create params are a subset of update in this case)
652 -- BUT this will be a problem should any new create params be added that are NOT updateable
653 for v_api_details in csr_api('HR_SP_PLACEMENT_API.UPDATE_SPP') loop
654 l_count := l_count+1;
655 l_api_details(l_count) := v_api_details;
656 end loop;
657 per_cagr_utility_pkg.put_log(' built api_details array of size: '||l_count);
658
659 -- loop thru each ent result and create mapping record(s)
660 for i in p_PYS_results.first..p_PYS_results.last loop
661 l_map_count := l_map_count+1;
662 l_mapping_table(l_map_count).value := p_PYS_results(i).step_id;
663
664 -- get the PARAMETER_NAME and UOM column from the API details structure
665 for j in l_api_details.first..l_api_details.last loop
666 -- the cagr_api_param_id does not necessarily match between results reference update api
667 -- but we have identified that we actually need to use create, so use parameter name instead...
668 if l_api_details(j).cagr_api_param_id = p_PYS_results(i).cagr_api_param_id then
669 l_mapping_table(l_map_count).parameter_name := l_api_details(j).parameter_name;
670 per_cagr_utility_pkg.put_log(' created mapping record: '||
671 l_mapping_table(l_map_count).parameter_name||'='||l_mapping_table(l_map_count).value);
672 -- also create a mapping table record, if there is a uom col
673 if l_api_details(j).uom_parameter is not null then
674 l_map_count := l_map_count+1;
675 l_mapping_table(l_map_count).parameter_name := l_api_details(j).uom_parameter;
676 l_mapping_table(l_map_count).value := p_PYS_results(i).units_of_measure;
677 per_cagr_utility_pkg.put_log(' created mapping record: '||
678 l_mapping_table(l_map_count).parameter_name||'='||l_mapping_table(l_map_count).value);
679 end if;
680 exit;
681 end if;
682 end loop;
683 end loop;
684
685 per_cagr_utility_pkg.put_log(' built mapping array of size: '||l_map_count);
686
687 if l_map_count > 0 then
688 BEGIN
689 if l_api_name = 'HR_SP_PLACEMENT_API.CREATE_SPP' then
690
691 per_cagr_utility_pkg.put_log(' calling HR_SP_PLACEMENT_API.CREATE_SPP',1);
692 l_step_id := get_num_val('STEP_ID',l_mapping_table);
693 if l_step_id is null then
694 -- check other mandatory params are supplied
695 per_cagr_utility_pkg.put_log(' ERROR: Cannot call HR_SP_PLACEMENT_API.CREATE_SPP with null STEP_ID',1);
696 else
697 -- per_cagr_utility_pkg.put_log(' date: '||p_params.effective_date,1);
698 -- per_cagr_utility_pkg.put_log(' BG: '||to_char(p_params.business_group_id),1);
699 -- per_cagr_utility_pkg.put_log(' asg: '||to_char(p_params.assignment_id),1);
700 -- per_cagr_utility_pkg.put_log(' step: '||to_char(l_step_id),1);
701 hr_sp_placement_api.create_spp
702 (p_effective_date => p_params.effective_date
703 ,p_business_group_id => p_params.business_group_id
704 ,p_assignment_id => p_params.assignment_id -- current assignment
705 ,p_step_id => l_step_id
706 ,p_object_version_number => l_ovn
707 ,p_placement_id => l_placement_id
708 ,p_effective_start_date => l_effective_start_date
709 ,p_effective_end_date => l_effective_end_date);
710 per_cagr_utility_pkg.put_log(' done HR_SP_PLACEMENT_API.CREATE_SPP',1);
711 -- per_cagr_utility_pkg.put_log(' placement_id: '||to_char(l_placement_id),1);
712 -- per_cagr_utility_pkg.put_log(' date: '||l_effective_start_date,1);
713 -- per_cagr_utility_pkg.put_log(' date: '||l_effective_end_date,1);
714 end if;
715 elsif l_api_name = 'HR_SP_PLACEMENT_API.UPDATE_SPP' then
716
717 per_cagr_utility_pkg.put_log(' calling HR_SP_PLACEMENT_API.UPDATE_SPP',1);
718 hr_sp_placement_api.update_spp
719 (p_effective_date => p_params.effective_date
720 ,p_datetrack_mode => l_dt_mode
721 ,p_placement_id => l_placement_id
722 ,p_step_id => get_num_val('STEP_ID',l_mapping_table)
723 ,p_object_version_number => l_ovn
724 ,p_effective_start_date => l_effective_start_date
725 ,p_effective_end_date => l_effective_end_date);
726
727 per_cagr_utility_pkg.put_log(' done HR_SP_PLACEMENT_API.UPDATE_SPP',1);
728 end if;
729 EXCEPTION
730 when others then -- log unexpected API error, and continue
731 per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
732 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
733 END;
734
735 end if;
736
737 -- delete pl/sql tables
738 l_api_details.delete;
739 l_mapping_table.delete;
740
741
742 hr_utility.set_location('Leaving:'||l_proc, 5);
743 END call_PYS_api;
744
745
746 -- ================================================================================================
747 -- == **************** CALL_PAY_API ***************** ==
748 -- ================================================================================================
749
750 PROCEDURE call_PAY_api (p_params IN per_cagr_evaluation_pkg.control_structure
751 ,p_PAY_results IN t_PAY_results) IS
752 --
753 -- Accepts structure holding results for a specific element type, and co-ordinates
754 -- denormalisation of those result values to relevant entry_values of any element
755 -- that exists for the element type - asg - effective date combination
756 -- May update (never insert) recurring or non recurring entries.
757 -- May denormalise to more than one element entry record for the element type,
758 -- if there are multiple entries in existence for the type, and the item's flag
759 -- multiple_element_entries_allowed is 'Y'. If flag is 'N' and there are multiple
760 -- entries in existence on the effective date then error is logged.
761 -- (Exception is GB legislation where an element entry can be set as substitute override
762 -- (entry_type = 'S') which means that only that entry is valid for the element type
763 -- on the effective_date, and so only that entry can be updated irrespective of whether multiple
764 -- is allowed or not).
765
766 -- get the legislation_code for bg.
767 CURSOR csr_bg IS
768 SELECT legislation_code
769 FROM per_business_groups_perf
770 WHERE business_group_id = p_params.business_group_id
771 and rownum = 1; -- Added for bug 3387328 to improve performance.
772
773 -- get the details of element entries that exist
774 -- for the element_type and asg on the effective_date
775 -- p_entry_id is only set if override entry (this is a workaround for GB leg)
776 CURSOR csr_entries (p_assignment_id NUMBER
777 ,p_element_type_id NUMBER
778 ,p_entry_id IN NUMBER) IS
779 SELECT ee.element_entry_id, ee.entry_type, el.element_link_id
780 FROM pay_element_links el, pay_element_entries ee
781 WHERE el.ELEMENT_TYPE_ID = p_element_type_id
782 AND ee.ELEMENT_LINK_ID = el.ELEMENT_LINK_ID
783 AND ee.ASSIGNMENT_ID = p_assignment_id
784 AND (p_entry_id is null or (p_entry_id is not null and ee.element_entry_id = p_entry_id))
785 AND p_params.effective_date BETWEEN el.EFFECTIVE_START_DATE
786 AND nvl(el.EFFECTIVE_END_DATE,hr_general.end_of_time)
787 AND p_params.effective_date BETWEEN ee.EFFECTIVE_START_DATE
788 AND nvl(ee.EFFECTIVE_END_DATE,hr_general.end_of_time);
789
790 Cursor csr_entries_not_exists(p_assignment_id NUMBER
791 ,p_element_type_id NUMBER) IS
792 Select distinct cer.element_type_id
793 From per_cagr_entitlement_results cer
794 Where cer.ASSIGNMENT_ID = p_assignment_id
795 and cer.end_date is null --fix for bug 5747086
796 And not exists (select 1
797 From pay_element_entries ee
798 Where cer.assignment_id = ee.assignment_id
799 And cer.element_type_id = ee.element_type_id
800 And ee.element_type_id = p_element_type_id
801 And p_params.effective_date BETWEEN ee.EFFECTIVE_START_DATE
802 AND nvl(ee.EFFECTIVE_END_DATE,hr_general.end_of_time))
803 Order by cer.element_type_id;
804
805
806 Cursor csr_entitlement_items(p_assignment_id NUMBER,
807 p_element_type_id NUMBER) IS
808 select cer.cagr_entitlement_item_id
809 from per_cagr_entitlement_results cer,
810 per_cagr_entitlement_items cei
811 where cer.assignment_id = p_assignment_id
812 and cer.element_type_id = p_element_type_id
813 and cer.cagr_entitlement_item_id = cei.cagr_entitlement_item_id
814 and nvl(cei.auto_create_entries_flag,'N') = 'Y'
815 order by cer.cagr_entitlement_item_id;
816
817
818 -- Bug 6645756
819 Cursor csr_ineligible_entitlements(p_assignment_id number) IS
820 SELECT DISTINCT cagr_entitlement_item_id
821 FROM per_cagr_entitlement_results cer
822 WHERE ASSIGNMENT_ID = p_assignment_id
823 AND NOT EXISTS (SELECT 1 FROM per_cagr_entitlement_results
824 WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
825 AND start_date = trunc(p_params.effective_date)
826 AND ASSIGNMENT_ID = p_assignment_id)
827 AND EXISTS ( SELECT 1 FROM per_cagr_entitlement_results
828 WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
829 AND end_date = trunc(p_params.effective_date) - 1
830 AND ASSIGNMENT_ID = p_assignment_id);
831
832 CURSOR csr_entitlement_element_detail(p_entitlement_id number, p_assignment_id number) IS
833 SELECT cei.element_type_id,cer.start_date,cer.end_date
834 FROM per_cagr_entitlement_results cer,PER_CAGR_ENTITLEMENT_ITEMS cei
835 WHERE cer.ASSIGNMENT_ID = p_assignment_id
836 AND cer.end_date = trunc(p_params.effective_date)-1
837 AND cer.cagr_entitlement_item_id=cei.cagr_entitlement_item_id
838 AND cei.cagr_entitlement_item_id = p_entitlement_id;
839
840 CURSOR csr_ele_entries (p_assignment_id number,p_element_type_id number,p_end_date date) is
841 SELECT element_entry_id,object_version_number
842 FROM pay_element_entries_f
843 WHERE element_type_id=p_element_type_id
844 AND assignment_id=p_assignment_id
845 AND effective_start_date<=p_end_date
846 AND effective_end_date>p_end_date;
847
848 l_element_type_id NUMBER(15);
849 l_start_date DATE;
850 l_end_date DATE;
851 l_entitlement_id per_cagr_entitlement_items.cagr_entitlement_item_id%type;
852 --fix for bug 5557658 ends here.
853
854 l_mapping_table t_mapping_table;
855 l_proc constant VARCHAR2(80) := g_pkg || 'call_PAY_api';
856 l_map_count NUMBER(10) := 0;
857 l_dt_mode VARCHAR2(30) := 'UPDATE';
858 v_entries csr_entries%ROWTYPE;
859 v_entries_not_exists csr_entries_not_exists%ROWTYPE;
860 v_entitlement_items csr_entitlement_items%ROWTYPE;
861
862 -- local vars to catch out params
863 l_ovn NUMBER(10);
864 l_entry_id NUMBER(15);
865 l_element_link_id NUMBER(15);
866 l_element_entry_id NUMBER(15);
867 l_effective_start_date DATE;
868 l_effective_end_date DATE;
869 l_warning BOOLEAN := FALSE;
870 l_auto_entries BOOLEAN := FALSE;
871 l_multi VARCHAR2(1) := 'N';
872 l_too_many_entries VARCHAR2(1) := NULL;
873 l_legislation_code VARCHAR2(2) := NULL;
874
875 BEGIN
876 hr_utility.set_location('Entering:'||l_proc, 5);
877
878
879 -- get the leg_code
880 open csr_bg;
881 fetch csr_bg into l_legislation_code;
882 close csr_bg;
883
884 -- build and populate mapping table with iv_ids and entry values
885 -- from the table of entitlement result records
886 for i in p_PAY_results.first..p_PAY_results.last loop
887 if (l_too_many_entries is null and p_PAY_results(i).multiple_entries_allowed_flag = 'N')
888 or (l_legislation_code = 'GB') then
889 -- here we are doing additional processing if the item uses multiple entries flag,
890 -- or the item is for GB and so can be overriden
891 -- e.t. allows multiple e.e. and the user has set update multi to N on item
892 -- so error log if there are > 1 entries that will be updated
893 open csr_entries(p_pay_results(i).assignment_id
894 ,p_PAY_results(i).element_type_id
895 ,NULL);
896 if l_legislation_code = 'GB' then
897 -- loop through all entries to
898 -- check if there is an override set
899 loop
900 fetch csr_entries into v_entries;
901 exit when csr_entries%notfound;
902 if v_entries.entry_type = 'S' then
903 -- store the override entry id
904 per_cagr_utility_pkg.put_log('Override entry exists - only this will be updated: '||v_entries.element_entry_id);
905 l_entry_id := v_entries.element_entry_id;
906 end if;
907 end loop;
908 else
909 -- just see if there is > 1 entry
910 for i in 1..2 loop -- dummy loop
911 fetch csr_entries into v_entries;
912 exit when csr_entries%notfound;
913 end loop;
914 end if;
915 if csr_entries%rowcount > 1 then
916 close csr_entries;
917 l_too_many_entries := 'Y';
918 else
919 close csr_entries;
920 l_too_many_entries := 'N';
921 end if;
922 end if;
923
924 if l_too_many_entries = 'Y' and p_PAY_results(i).multiple_entries_allowed_flag = 'N'
925 and l_entry_id is null then
926 -- not an override GB element entry so show message
927 per_cagr_utility_pkg.put_log(' iv: '||p_PAY_results(i).input_value_id);
928 per_cagr_utility_pkg.put_log(' ev: '||p_PAY_results(i).value);
929 per_cagr_utility_pkg.put_log(' Error: More than 1 entry found for payroll item on the '
930 ||'effective_date and updating multiple entries is not allowed.',1);
931 per_cagr_utility_pkg.put_log(' Item value will not be applied to element_entry records.',1);
932 else
933 -- build the mapping table for the results
934 per_cagr_utility_pkg.put_log(' iv: '||p_PAY_results(i).input_value_id);
935 l_map_count := l_map_count+1;
936 l_mapping_table(l_map_count).parameter_name := 'INPUT_VALUE_ID'||i;
937 l_mapping_table(l_map_count).value := p_PAY_results(i).input_value_id;
938 l_map_count := l_map_count+1;
939 l_mapping_table(l_map_count).parameter_name := 'ENTRY_VALUE'||i;
940 l_mapping_table(l_map_count).value := p_PAY_results(i).value;
941 per_cagr_utility_pkg.put_log(' ev: '||p_PAY_results(i).value);
942 end if;
943
944 end loop;
945
946 per_cagr_utility_pkg.put_log(' built mapping array of size: '||l_map_count);
947
948 if l_mapping_table.count > 0 then
949
950 -- get all entry recs for the element type on the effective_date
951 -- (if GB and we know the id of the override then restrict to that entry only)
952 open csr_entries (p_pay_results(1).assignment_id
953 ,p_pay_results(1).element_type_id
954 ,l_entry_id);
955
956 loop
957 fetch csr_entries into v_entries;
958 exit when csr_entries%notfound;
959
960 -- determine the ovn, dt mode for the ele entry in this iteration
961 get_ovn_and_mode (p_table_name => 'PAY_ELEMENT_ENTRIES_F'
962 ,p_dt_flag => 'Y'
963 ,p_pk => 'ELEMENT_ENTRY_ID'
964 ,p_pk_id => v_entries.element_entry_id
965 ,p_effective_date => p_params.effective_date
966 ,p_mode => l_dt_mode
967 ,p_ovn => l_ovn);
968
969 -- if the element entry is non recurring then api should be called in CORRECTION mode only.
970 -- check here, or modify get_ovn_and_mode
971
972 per_cagr_utility_pkg.put_log(' calling PY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY in mode: '||l_dt_mode,1);
973 BEGIN
974 py_element_entry_api.update_element_entry(
975 p_datetrack_update_mode => l_dt_mode,
976 p_effective_date => p_params.effective_date,
977 p_business_group_id => p_params.business_group_id,
978 p_element_entry_id => v_entries.element_entry_id,
979 p_object_version_number => l_ovn,
980 p_input_value_id1 => get_num_val('INPUT_VALUE_ID1',l_mapping_table),
981 p_input_value_id2 => get_num_val('INPUT_VALUE_ID2',l_mapping_table),
982 p_input_value_id3 => get_num_val('INPUT_VALUE_ID3',l_mapping_table),
983 p_input_value_id4 => get_num_val('INPUT_VALUE_ID4',l_mapping_table),
984 p_input_value_id5 => get_num_val('INPUT_VALUE_ID5',l_mapping_table),
985 p_input_value_id6 => get_num_val('INPUT_VALUE_ID6',l_mapping_table),
986 p_input_value_id7 => get_num_val('INPUT_VALUE_ID7',l_mapping_table),
987 p_input_value_id8 => get_num_val('INPUT_VALUE_ID8',l_mapping_table),
988 p_input_value_id9 => get_num_val('INPUT_VALUE_ID9',l_mapping_table),
989 p_input_value_id10 => get_num_val('INPUT_VALUE_ID10',l_mapping_table),
990 p_input_value_id11 => get_num_val('INPUT_VALUE_ID11',l_mapping_table),
991 p_input_value_id12 => get_num_val('INPUT_VALUE_ID12',l_mapping_table),
992 p_input_value_id13 => get_num_val('INPUT_VALUE_ID13',l_mapping_table),
993 p_input_value_id14 => get_num_val('INPUT_VALUE_ID14',l_mapping_table),
994 p_input_value_id15 => get_num_val('INPUT_VALUE_ID15',l_mapping_table),
995 p_entry_value1 => get_char_val('ENTRY_VALUE1',l_mapping_table),
996 p_entry_value2 => get_char_val('ENTRY_VALUE2',l_mapping_table),
997 p_entry_value3 => get_char_val('ENTRY_VALUE3',l_mapping_table),
998 p_entry_value4 => get_char_val('ENTRY_VALUE4',l_mapping_table),
999 p_entry_value5 => get_char_val('ENTRY_VALUE5',l_mapping_table),
1000 p_entry_value6 => get_char_val('ENTRY_VALUE6',l_mapping_table),
1001 p_entry_value7 => get_char_val('ENTRY_VALUE7',l_mapping_table),
1002 p_entry_value8 => get_char_val('ENTRY_VALUE8',l_mapping_table),
1003 p_entry_value9 => get_char_val('ENTRY_VALUE9',l_mapping_table),
1004 p_entry_value10 => get_char_val('ENTRY_VALUE10',l_mapping_table),
1005 p_entry_value11 => get_char_val('ENTRY_VALUE11',l_mapping_table),
1006 p_entry_value12 => get_char_val('ENTRY_VALUE12',l_mapping_table),
1007 p_entry_value13 => get_char_val('ENTRY_VALUE13',l_mapping_table),
1008 p_entry_value14 => get_char_val('ENTRY_VALUE14',l_mapping_table),
1009 p_entry_value15 => get_char_val('ENTRY_VALUE15',l_mapping_table),
1010 p_effective_start_date => l_effective_start_date,
1011 p_effective_end_date => l_effective_end_date,
1012 p_update_warning => l_warning);
1013
1014 -- log any warnings
1015 if l_warning then
1016 per_cagr_utility_pkg.put_log(' WARNING: p_update_warning',1);
1017 end if;
1018 per_cagr_utility_pkg.put_log(' done PY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY in mode: '||l_dt_mode,1);
1019 EXCEPTION
1020 when others then -- log unexpected API error, and continue
1021 per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
1022 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1023 END;
1024 end loop;
1025 if csr_entries%rowcount = 0 then
1026 per_cagr_utility_pkg.put_log(' Warning: 0 element entries found for element_type_id '||
1027 p_pay_results(1).element_type_id||' on assignment '||
1028 p_pay_results(1).assignment_id,1);
1029 end if;
1030 close csr_entries;
1031
1032 end if;
1033
1034
1035 -- delete pl/sql tables
1036 l_mapping_table.delete;
1037
1038
1039 /* Enhancements - Create Element Entry if it does not exists and the
1040 Auto Entry Flag for the CAGR Entitlement Item is checked */
1041
1042 l_map_count := 0;
1043 l_warning := FALSE;
1044
1045 per_cagr_utility_pkg.put_log('New EE Entries ',5);
1046 -- build and populate mapping table with iv_ids and entry values
1047 -- from the table of entitlement result records
1048 for i in p_PAY_results.first..p_PAY_results.last loop
1049
1050 -- build the mapping table for the results
1051 l_map_count := l_map_count+1;
1052 per_cagr_utility_pkg.put_log('map count = '||l_map_count,5);
1053 l_mapping_table(l_map_count).parameter_name := 'INPUT_VALUE_ID'||i;
1054 per_cagr_utility_pkg.put_log('input_value_id : '||p_PAY_results(i).input_value_id,5);
1055 l_mapping_table(l_map_count).value := p_PAY_results(i).input_value_id;
1056 l_map_count := l_map_count+1;
1057 per_cagr_utility_pkg.put_log('entry_value : '||p_PAY_results(i).value,5);
1058 l_mapping_table(l_map_count).parameter_name := 'ENTRY_VALUE'||i;
1059 l_mapping_table(l_map_count).value := p_PAY_results(i).value;
1060 per_cagr_utility_pkg.put_log(' ev: '||p_PAY_results(i).value,5);
1061
1062 end loop;
1063
1064 per_cagr_utility_pkg.put_log(' built mapping array of size: '||l_map_count,5);
1065
1066 if l_mapping_table.count > 0 then
1067
1068 -- get all entry recs for the assignment on the effective_date
1069 --
1070 open csr_entries_not_exists (p_pay_results(1).assignment_id
1071 ,p_pay_results(1).element_type_id);
1072
1073 loop
1074 fetch csr_entries_not_exists into v_entries_not_exists;
1075 exit when csr_entries_not_exists%notfound;
1076
1077 open csr_entitlement_items(p_params.assignment_id,
1078 v_entries_not_exists.element_type_id);
1079 fetch csr_entitlement_items into v_entitlement_items;
1080 if csr_entitlement_items%FOUND then
1081 close csr_entitlement_items;
1082
1083 BEGIN
1084 l_element_link_id := hr_entry_api.get_link
1085 (p_assignment_id => p_params.assignment_id,
1086 p_element_type_id => v_entries_not_exists.element_type_id,
1087 p_session_date => p_params.effective_date);
1088
1089 per_cagr_utility_pkg.put_log(' Assignment ID = '||p_params.assignment_id);
1090 per_cagr_utility_pkg.put_log(' Element TypeID = '||v_entries_not_exists.element_type_id);
1091 per_cagr_utility_pkg.put_log(' Element LinkID = '||l_element_link_id);
1092 pay_element_entry_api.create_element_entry(
1093 p_validate => FALSE,
1094 p_effective_date => p_params.effective_date,
1095 p_business_group_id => p_params.business_group_id,
1096 p_assignment_id => p_params.assignment_id,
1097 p_element_link_id => l_element_link_id,
1098 p_entry_type => 'E',
1099 p_input_value_id1 => get_num_val('INPUT_VALUE_ID1',l_mapping_table),
1100 p_input_value_id2 => get_num_val('INPUT_VALUE_ID2',l_mapping_table),
1101 p_input_value_id3 => get_num_val('INPUT_VALUE_ID3',l_mapping_table),
1102 p_input_value_id4 => get_num_val('INPUT_VALUE_ID4',l_mapping_table),
1103 p_input_value_id5 => get_num_val('INPUT_VALUE_ID5',l_mapping_table),
1104 p_input_value_id6 => get_num_val('INPUT_VALUE_ID6',l_mapping_table),
1105 p_input_value_id7 => get_num_val('INPUT_VALUE_ID7',l_mapping_table),
1106 p_input_value_id8 => get_num_val('INPUT_VALUE_ID8',l_mapping_table),
1107 p_input_value_id9 => get_num_val('INPUT_VALUE_ID9',l_mapping_table),
1108 p_input_value_id10 => get_num_val('INPUT_VALUE_ID10',l_mapping_table),
1109 p_input_value_id11 => get_num_val('INPUT_VALUE_ID11',l_mapping_table),
1110 p_input_value_id12 => get_num_val('INPUT_VALUE_ID12',l_mapping_table),
1111 p_input_value_id13 => get_num_val('INPUT_VALUE_ID13',l_mapping_table),
1112 p_input_value_id14 => get_num_val('INPUT_VALUE_ID14',l_mapping_table),
1113 p_input_value_id15 => get_num_val('INPUT_VALUE_ID15',l_mapping_table),
1114 p_entry_value1 => get_char_val('ENTRY_VALUE1',l_mapping_table),
1115 p_entry_value2 => get_char_val('ENTRY_VALUE2',l_mapping_table),
1116 p_entry_value3 => get_char_val('ENTRY_VALUE3',l_mapping_table),
1117 p_entry_value4 => get_char_val('ENTRY_VALUE4',l_mapping_table),
1118 p_entry_value5 => get_char_val('ENTRY_VALUE5',l_mapping_table),
1119 p_entry_value6 => get_char_val('ENTRY_VALUE6',l_mapping_table),
1120 p_entry_value7 => get_char_val('ENTRY_VALUE7',l_mapping_table),
1121 p_entry_value8 => get_char_val('ENTRY_VALUE8',l_mapping_table),
1122 p_entry_value9 => get_char_val('ENTRY_VALUE9',l_mapping_table),
1123 p_entry_value10 => get_char_val('ENTRY_VALUE10',l_mapping_table),
1124 p_entry_value11 => get_char_val('ENTRY_VALUE11',l_mapping_table),
1125 p_entry_value12 => get_char_val('ENTRY_VALUE12',l_mapping_table),
1126 p_entry_value13 => get_char_val('ENTRY_VALUE13',l_mapping_table),
1127 p_entry_value14 => get_char_val('ENTRY_VALUE14',l_mapping_table),
1128 p_entry_value15 => get_char_val('ENTRY_VALUE15',l_mapping_table),
1129 p_effective_start_date => l_effective_start_date,
1130 p_effective_end_date => l_effective_end_date,
1131 p_element_entry_id => l_element_entry_id,
1132 p_object_version_number => l_ovn,
1133 p_create_warning => l_warning);
1134 per_cagr_utility_pkg.put_log(' End of create element entry.');
1135 per_cagr_utility_pkg.put_log(' Element EntryID = '||l_element_entry_id);
1136
1137 -- log any warnings
1138 if l_warning then
1139 per_cagr_utility_pkg.put_log(' WARNING: p_create_warning',1);
1140 end if;
1141 per_cagr_utility_pkg.put_log(' Done PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY '||l_element_entry_id,1);
1142 EXCEPTION
1143 when others then -- log unexpected API error, and continue
1144 per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
1145 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1146 END;
1147
1148 else
1149
1150 close csr_entitlement_items;
1151
1152 per_cagr_utility_pkg.put_log(' WARNING: Auto Entry flag for Entitlement Item is not checked');
1153
1154 end if;
1155
1156 end loop;
1157 if csr_entries_not_exists%rowcount = 0 then
1158 per_cagr_utility_pkg.put_log(' Warning: 0 element entries found for element_type_id '||
1159 p_pay_results(1).element_type_id||' on assignment '||
1160 p_pay_results(1).assignment_id,1);
1161 end if;
1162 close csr_entries_not_exists;
1163
1164 end if;
1165
1166 -- delete pl/sql tables
1167 l_mapping_table.delete;
1168
1169 /* Enhancements End Create Element Entry */
1170
1171 -- Changes done for bug 6645756
1172
1173 OPEN csr_ineligible_entitlements(p_params.assignment_id);
1174 LOOP
1175 FETCH csr_ineligible_entitlements INTO l_entitlement_id;
1176 EXIT WHEN csr_ineligible_entitlements%NOTFOUND;
1177 -- Assignment is ineligible in future for current entitlement, get the element details and end date
1178 -- corresponding assignment element entries.
1179 OPEN csr_entitlement_element_detail(l_entitlement_id, p_params.assignment_id);
1180 /* Get all the records in per_cagr_entitlement_results, which are end dated on the effective date */
1181 loop
1182 fetch csr_entitlement_element_detail into l_element_type_id,l_start_date,l_end_date;
1183 exit when csr_entitlement_element_detail%notfound;
1184
1185 open csr_ele_entries(p_params.assignment_id,l_element_type_id,l_end_date);
1186 loop
1187 fetch csr_ele_entries into l_element_entry_id, l_ovn;
1188 exit when csr_ele_entries%notFOUND ;
1189
1190 py_element_entry_api.delete_element_entry
1191 (p_validate => FALSE
1192 ,p_datetrack_delete_mode => 'DELETE'
1193 ,p_effective_date => l_end_date
1194 ,p_element_entry_id => l_element_entry_id
1195 ,p_object_version_number => l_ovn
1196 ,p_effective_start_date => l_effective_start_date
1197 ,p_effective_end_date => l_effective_end_date
1198 ,p_delete_warning => l_warning
1199 );
1200
1201 end loop;
1202 close csr_ele_entries;
1203 end loop;
1204 close csr_entitlement_element_detail;
1205 END loop;
1206 CLOSE csr_ineligible_entitlements;
1207 -- fix for bug 6645756 ends here.
1208
1209 hr_utility.set_location('Leaving:'||l_proc, 5);
1210 END call_PAY_api;
1211
1212 -- ================================================================================================
1213 -- == **************** do_apply_for_assignment ***************** ==
1214 -- ================================================================================================
1215 PROCEDURE do_apply_for_assignment(p_params IN OUT NOCOPY per_cagr_evaluation_pkg.control_structure
1216 ,p_select_flag IN varchar2) IS
1217 --
1218 -- This routine controls the apply processing for a particular assignment on the effective_date.
1219 --
1220
1221 -- get ASG category results to be applied, for an asg.
1222 -- either chosen or beneficial if none chosen
1223 CURSOR csr_ASG_denorm_results IS
1224 SELECT cagr_entitlement_result_id
1225 ,assignment_id
1226 ,cagr_entitlement_item_id
1227 ,cagr_api_id
1228 ,cagr_api_param_id
1229 ,value
1230 ,units_of_measure
1231 FROM per_cagr_entitlement_results
1232 WHERE category_name = 'ASG'
1233 AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1234 AND (chosen_flag = 'Y'
1235 OR (beneficial_flag = 'Y'
1236 and not exists (select 'X' from per_cagr_entitlement_results res1
1237 where res1.assignment_id = p_params.assignment_id
1238 and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
1239 AND res1.chosen_flag = 'Y'
1240 AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
1241 AND cagr_api_id is not null
1242 AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time)
1243 ORDER BY cagr_api_id;
1244
1245 -- get PYS category results to be applied (step_id, increment_number) for an asg.
1246 CURSOR csr_PYS_denorm_results IS
1247 SELECT cagr_entitlement_result_id
1248 ,assignment_id
1249 ,cagr_entitlement_item_id
1250 ,cagr_api_id
1251 ,cagr_api_param_id
1252 ,category_name
1253 ,grade_spine_id
1254 ,parent_spine_id
1255 ,step_id
1256 ,from_step_id
1257 ,to_step_id
1258 ,value
1259 ,units_of_measure
1260 FROM per_cagr_entitlement_results
1261 WHERE category_name = 'PYS'
1262 AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1263 AND (chosen_flag = 'Y'
1264 OR (beneficial_flag = 'Y'
1265 and not exists (select 'X' from per_cagr_entitlement_results res1
1266 where res1.assignment_id = p_params.assignment_id
1267 and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
1268 AND res1.chosen_flag = 'Y'
1269 AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
1270 AND cagr_api_id is not null
1271 AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time)
1272 ORDER BY cagr_api_id;
1273
1274 -- get PAY category results to be applied (element is set) for an asg.
1275 -- ordered by element_type
1276 CURSOR csr_PAY_denorm_results IS
1277 SELECT cagr_entitlement_result_id
1278 ,assignment_id
1279 ,cagr_entitlement_item_id
1280 ,category_name
1281 ,element_type_id
1282 ,input_value_id
1283 ,value
1284 ,multiple_entries_allowed_flag
1285 FROM per_cagr_entitlement_results
1286 WHERE category_name = 'PAY'
1287 AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1288 AND (chosen_flag = 'Y'
1289 OR (beneficial_flag = 'Y'
1290 and not exists (select 'X' from per_cagr_entitlement_results res1
1291 where res1.assignment_id = p_params.assignment_id
1292 and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
1293 AND res1.chosen_flag = 'Y'
1294 AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
1295 AND element_type_id is not null
1296 AND input_value_id is not null
1297 AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time)
1298 ORDER BY element_type_id;
1299
1300
1301 l_ASG_results t_ASG_results;
1302 l_PYS_results t_PYS_results;
1303 l_PAY_results t_PAY_results;
1304
1305 l_proc constant VARCHAR2(80) := g_pkg || 'do_apply_for_assignment';
1306 l_table_counter NUMBER(15) := 0;
1307 l_old_cagr_api_id per_cagr_entitlement_items.cagr_api_id%TYPE := 0;
1308 l_old_element_type_id per_cagr_entitlement_items.element_type_id%TYPE := 0;
1309
1310
1311 BEGIN
1312
1313 hr_utility.set_location('Entering:'||l_proc, 5);
1314
1315 if p_params.category is null or p_params.category = 'ASG' then
1316 per_cagr_utility_pkg.put_log(' Starting ASSIGNMENT category items...',1);
1317
1318 -- open cursor for ASG results for denormalisation, and collect pl/sql table
1319 for v_ASG_results in csr_ASG_denorm_results loop
1320 per_cagr_utility_pkg.put_log(' found result record for item: '||v_ASG_results.cagr_entitlement_item_id);
1321
1322 if (l_old_cagr_api_id <> 0 and l_old_cagr_api_id <> v_ASG_results.cagr_api_id) then
1323 -- the cagr_api_id has changed so call_the relevant API to denormalise
1324 call_ASG_api(p_params,l_ASG_results);
1325 l_ASG_results.delete;
1326 l_table_counter := 0;
1327 l_old_cagr_api_id := v_ASG_results.cagr_api_id;
1328 end if;
1329 l_table_counter := l_table_counter+1;
1330 l_ASG_results(l_table_counter) := v_ASG_results; -- assign csr_rec to table
1331 l_old_cagr_api_id := v_ASG_results.cagr_api_id;
1332 end loop;
1333 if l_table_counter <> 0 then
1334 -- pass any remaining records to denormalise pkg
1335 call_ASG_api(p_params,l_ASG_results);
1336 l_ASG_results.delete;
1337 l_table_counter := 0;
1338 end if;
1339
1340 -- bug 2289200 reset variable to 0;
1341 l_old_cagr_api_id := 0;
1342
1343 per_cagr_utility_pkg.put_log(' Completed ASSIGNMENT category items.',1);
1344 per_cagr_utility_pkg.put_log(' ');
1345 end if;
1346
1347 if p_params.category is null or p_params.category = 'PYS' then
1348 per_cagr_utility_pkg.put_log(' Starting PAY SCALE category items...',1);
1349
1350 for v_PYS_results in csr_PYS_denorm_results loop
1351 per_cagr_utility_pkg.put_log(' found result record for item: '||v_PYS_results.cagr_entitlement_item_id);
1352
1353 if (l_old_cagr_api_id <> 0 and l_old_cagr_api_id <> v_PYS_results.cagr_api_id) then
1354 -- the cagr_api_id has changed so call_the relevant API to denormalise
1355 call_PYS_api(p_params,l_PYS_results);
1356 l_PYS_results.delete;
1357 l_table_counter := 0;
1358 l_old_cagr_api_id := v_PYS_results.cagr_api_id;
1359 end if;
1360 l_table_counter := l_table_counter+1;
1361 l_PYS_results(l_table_counter) := v_PYS_results; -- assign csr_rec to table
1362 l_old_cagr_api_id := v_PYS_results.cagr_api_id;
1363 end loop;
1364 if l_table_counter <> 0 then
1365 -- pass any remaining records to denormalise pkg
1366 call_PYS_api(p_params,l_PYS_results);
1367 l_PYS_results.delete;
1368 l_table_counter := 0;
1369 end if;
1370
1371 -- bug 2289200 reset variable to 0;
1372 l_old_cagr_api_id := 0;
1373
1374 per_cagr_utility_pkg.put_log(' Completed PAY SCALE category items.',1);
1375 per_cagr_utility_pkg.put_log(' ');
1376 end if;
1377
1378 if p_params.category is null or p_params.category = 'PAY' then
1379 per_cagr_utility_pkg.put_log(' Starting PAYROLL category items...',1);
1380
1381 -- loop through all element results, populating pl/sql table with values
1382 -- and calling denormalise routine for each element type in the cursor.
1383 for v_PAY_results in csr_PAY_denorm_results loop
1384 per_cagr_utility_pkg.put_log(' found result record for item: '||v_PAY_results.cagr_entitlement_item_id);
1385
1386 if (l_old_element_type_id <> 0 and l_old_element_type_id <> v_PAY_results.element_type_id) then
1387 -- the element_type has changed so call element entry API to denormalise
1388 -- entry values for the element_type
1389 if l_PAY_results.count > 15 then
1390 -- error as too many results for the update api call for the element
1391 per_cagr_utility_pkg.put_log(' ERROR: > 15 results for this element_type means data lost, skipping element');
1392 else
1393 call_PAY_api(p_params,l_PAY_results);
1394 l_PAY_results.delete;
1395 l_table_counter := 0;
1396 l_old_element_type_id := v_PAY_results.element_type_id;
1397 end if;
1398 end if;
1399 l_table_counter := l_table_counter+1;
1400 l_PAY_results(l_table_counter) := v_PAY_results; -- assign csr_rec to table
1401 l_old_element_type_id := v_PAY_results.element_type_id;
1402 end loop;
1403 if l_table_counter <> 0 then
1404 -- pass any remaining records to denormalise pkg
1405 call_PAY_api(p_params,l_PAY_results);
1406 l_PAY_results.delete;
1407 l_table_counter := 0;
1408 end if;
1409
1410 -- bug 2289200 reset variable to 0;
1411 l_old_cagr_api_id := 0;
1412
1413 per_cagr_utility_pkg.put_log(' Completed PAYROLL category items.',1);
1414 per_cagr_utility_pkg.put_log(' ');
1415 end if;
1416
1417 hr_utility.set_location('Leaving:'||l_proc, 100);
1418
1419
1420 EXCEPTION
1421 when others then
1422 -- write the log contents
1423 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1424 raise;
1425
1426 END do_apply_for_assignment;
1427
1428 -- ================================================================================================
1429 -- == **************** MAIN_BLOCK ***************** ==
1430 -- ================================================================================================
1431
1432 BEGIN
1433
1434 hr_utility.set_location('Entering:'||l_proc, 5);
1435 --
1436 if not(g_done_header) then
1437 per_cagr_utility_pkg.put_log(per_cagr_evaluation_pkg.g_head_separator,1);
1438 per_cagr_utility_pkg.put_log('-------- Result Population Process Log ('||fnd_date.date_to_canonical(sysdate)||') --------',1);
1439 per_cagr_utility_pkg.put_log(per_cagr_evaluation_pkg.g_head_separator,1);
1440 per_cagr_utility_pkg.put_log('Starting Result Population Process: ('
1441 ||fnd_date.date_to_canonical(sysdate)||')',1);
1442
1443 If p_params.operation_mode = 'SA' then
1444 per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
1445 elsif p_params.operation_mode = 'SC' then
1446 per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
1447 end if;
1448
1449 /* redundant
1450 If p_select_flag = 'B' then
1451 per_cagr_utility_pkg.put_log(' Beneficial values to be applied.',1);
1452 elsif p_select_flag = 'C' then
1453 per_cagr_utility_pkg.put_log(' Chosen values to be applied.',1);
1454 end if;
1455 */
1456 end if;
1457 g_done_header := FALSE;
1458 --
1459 --
1460 if p_params.category is not null then
1461 if p_params.category not in ('ASG','PAY','PYS','ABS') then
1462 per_cagr_utility_pkg.log_and_raise_error('HR_XXXXX_CAGR_INV_CATEGORY',p_params.cagr_request_id);
1463 end if;
1464 end if;
1465
1466
1467 if p_params.operation_mode = 'SA' then
1468 --
1469 -- ********* single assignment *********
1470 --
1471
1472 per_cagr_utility_pkg.put_log(' ',1);
1473 per_cagr_utility_pkg.put_log(' Processing Assignment ID '|| p_params.assignment_id ||
1474 ' during Single Assignment Agreement mode.',1);
1475 do_apply_for_assignment(p_params, p_select_flag);
1476
1477 --
1478 -- Commit, if required.
1479 --
1480 if p_params.commit_flag = 'Y' then
1481 per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
1482 commit;
1483 elsif p_params.commit_flag = 'N' then
1484 per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
1485 rollback;
1486 end if;
1487
1488 elsif p_params.operation_mode = 'SC' then
1489 --
1490 -- ********* single collective agreement *********
1491 --
1492
1493 -- Applies any results found in cache for each asg on the cagr
1494 -- on the effective_date. Processing notes:
1495 -- 1) Results are located, applied and committed for each asg in succession.
1496 -- 2) if cagr_request_id is a parent_request_id then process was called from
1497 -- evaluation code directly, so we re-use that request id as the main
1498 -- request id for this run. Otherwise we use the request id created in
1499 -- intitilize (parameter) as the main request.
1500 -- 3) for each asg, log entries are written under existing request ids (created
1501 -- when the results were evaluated, and are visible from PERWSCAR), but a
1502 -- file of combined parent request and asg denorm log entries is also
1503 -- created when we are running under CM.
1504 --
1505 per_cagr_utility_pkg.put_log(' Identified the following assignments on the collective agreement:',1);
1506 --
1507 -- first load all the assignment ids to be processed into pl/sql table.
1508 --
1509 open csr_assignments_to_process;
1510 loop
1511 l_counter := l_counter+1;
1512 fetch csr_assignments_to_process into t_assignment_list(l_counter);
1513 exit when csr_assignments_to_process%notfound;
1514 per_cagr_utility_pkg.put_log(' '||t_assignment_list(l_counter).assignment_id,1);
1515 end loop;
1516 close csr_assignments_to_process;
1517
1518
1519 -- write the log out and save the request_id
1520 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1521 l_parent_request_id := p_params.cagr_request_id;
1522
1523 -- could now break pl/sql table into varray subsets, ready for multiple threads
1524
1525 -- loop through assignment_id table
1526 FOR k in t_assignment_list.first .. t_assignment_list.last LOOP
1527 Begin
1528
1529 -- set asg and request, then call asg processing
1530 p_params.assignment_id := t_assignment_list(k).assignment_id;
1531 p_params.cagr_request_id := get_request_id(t_assignment_list(k).assignment_id
1532 ,p_params.effective_date);
1533
1534 per_cagr_utility_pkg.put_log(' ',1);
1535 per_cagr_utility_pkg.put_log(per_cagr_evaluation_pkg.g_head_separator,1);
1536 per_cagr_utility_pkg.put_log('-------- Result Population Process Log ('||fnd_date.date_to_canonical(sysdate)||') --------',1);
1537 per_cagr_utility_pkg.put_log(per_cagr_evaluation_pkg.g_head_separator,1);
1538
1539 per_cagr_utility_pkg.put_log(' Processing Assignment ID '|| p_params.assignment_id ||
1540 ' during Single Collective Agreement mode.',1);
1541
1542
1543 if p_params.cagr_request_id is null then
1544 -- there are no SA mode eval logs to update for this asg, so just use parent request id.
1545 p_params.cagr_request_id := l_parent_request_id;
1546 end if;
1547
1548 do_apply_for_assignment(p_params, p_select_flag);
1549
1550 --
1551 -- Commit, if required.
1552 --
1553 if p_params.commit_flag = 'Y' then
1554 per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
1555 commit;
1556 elsif p_params.commit_flag = 'N' then
1557 per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
1558 rollback;
1559 end if;
1560
1561 -- complete the logging for this asg
1562 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1563
1564 Exception
1565 when others then
1566 -- complete the logging for this asg
1567 per_cagr_utility_pkg.put_log('ERROR: '||sqlerrm);
1568 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1569 End;
1570 END LOOP;
1571
1572 -- restore the original request id, now all asgs have completed
1573 p_params.cagr_request_id := l_parent_request_id;
1574
1575 else
1576 null; -- do processing for other modes...
1577 end if;
1578
1579 per_cagr_utility_pkg.put_log('Completed Result Population Process ('
1580 ||fnd_date.date_to_canonical(sysdate)||')',1);
1581 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1582 hr_utility.set_location('Leaving:'||l_proc, 100);
1583
1584
1585 EXCEPTION
1586 when others then
1587 -- write the log contents
1588 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1589 raise;
1590 END initialise;
1591
1592 --
1593 -- ------------------------------------------------------------------------------
1594 -- |-----------------------------< initialise >---------------------------------|
1595 -- ------------------------------------------------------------------------------
1596 --
1597 PROCEDURE initialise (p_process_date in date
1598 ,p_operation_mode in varchar2
1599 ,p_business_group_id in number
1600 ,p_assignment_id in number default null
1601 ,p_assignment_set_id in number default null
1602 ,p_category in varchar2 default null
1603 ,p_collective_agreement_id in number default null
1604 ,p_collective_agreement_set_id in number default null
1605 ,p_person_id in number default null
1606 ,p_entitlement_item_id in number default null
1607 ,p_select_flag in varchar2 default 'B'
1608 ,p_commit_flag in varchar2 default 'N'
1609 ,p_cagr_request_id in out nocopy number) IS
1610
1611 -- (individual parameter interface)
1612 -- This procedure is the main interface to the denormalization process, and is called
1613 -- directly by the concurrent manager and the form PERWSCAR.fmb to apply values to HRMS
1614 -- according to the control parameters supplied.
1615 --
1616 -- It calls PER_CAGR_DENORMALIZE_PKG.initialise (parameter interface) after
1617 -- validating individual parameters, populating the control record structure structure, creating
1618 -- a per_cagr_request record (if required) and initializing logging for the denormalisation run.
1619 --
1620 -- P_SELECT_FLAG IS NOW REDUNDANT - the beneficial flag result will be applied unless there is
1621 -- another result for the entitlement that has been chosen, in which case that will be applied.
1622 -- (old behaviour: process detects if it has been run from concurrent manager or from form and
1623 -- differs in behaviour as follows: If run from form, the form user has chosen a particular record
1624 -- (which may differ from the most beneficial) and so this process will trigger the result with
1625 -- chosen_flag = Y to be applied (ignoring beneficial_flag) for each item. If run from conc manager,
1626 -- of the set of results for each item in the cache the process will select the record with
1627 -- beneficial_flag = 'Y' (if any, and ignoring chosen_flag values) for application to HRMS)
1628 --
1629
1630 l_proc constant varchar2(61) := g_pkg || 'initialise';
1631 l_params per_cagr_evaluation_pkg.control_structure;
1632
1633 BEGIN
1634
1635 hr_utility.set_location('Entering:'||l_proc, 5);
1636
1637 if p_cagr_request_id is null then
1638 --
1639 -- generate a new request for use in this run, if called from SRS or pl/sql module directly
1640 -- (if called from PERWSCAR.fmb or evaluation process we use the request_id
1641 -- that gave rise to the results which we are about to denormalise, so evaluation log records
1642 -- are appended with entitlement population process entries)
1643 --
1644 per_cagr_utility_pkg.create_cagr_request(p_process_date => p_process_date
1645 ,p_operation_mode => p_operation_mode
1646 ,p_business_group_id => p_business_group_id
1647 ,p_assignment_id => p_assignment_id
1648 ,p_assignment_set_id => p_assignment_set_id
1649 ,p_collective_agreement_id => p_collective_agreement_id
1650 ,p_collective_agreement_set_id => p_collective_agreement_set_id
1651 ,p_payroll_id => NULL
1652 ,p_person_id => p_person_id
1653 ,p_entitlement_item_id => p_entitlement_item_id
1654 ,p_parent_request_id => NULL
1655 ,p_commit_flag => p_commit_flag
1656 ,p_denormalise_flag => 'Y'
1657 ,p_cagr_request_id => p_cagr_request_id);
1658 end if;
1659
1660
1661 --
1662 -- Ensure that all the mandatory arguments are not null
1663 --
1664 hr_api.mandatory_arg_error(p_api_name => l_proc
1665 ,p_argument => 'process_date'
1666 ,p_argument_value => p_process_date);
1667 hr_api.mandatory_arg_error(p_api_name => l_proc
1668 ,p_argument => 'operation_mode'
1669 ,p_argument_value => p_operation_mode);
1670
1671 --
1672 -- Output denormalization log header
1673 --
1674 per_cagr_utility_pkg.put_log(per_cagr_evaluation_pkg.g_head_separator,1);
1675 per_cagr_utility_pkg.put_log('-------- Result Population Process Log ('||fnd_date.date_to_canonical(sysdate)||') --------',1);
1676 per_cagr_utility_pkg.put_log(per_cagr_evaluation_pkg.g_head_separator,1);
1677 g_done_header := TRUE;
1678
1679 --
1680 -- validate parameters
1681 --
1682 if not (p_commit_flag in ('N','Y')) then
1683 per_cagr_utility_pkg.log_and_raise_error('HR_289419_CAGR_INV_CFLAG'
1684 ,p_cagr_request_id);
1685 end if;
1686
1687 /* select_flag is redundant
1688 if not (p_select_flag in ('B','C')) then
1689 per_cagr_utility_pkg.log_and_raise_error('HR_XXXXX_CAGR_INV_SFLAG'
1690 ,p_cagr_request_id);
1691 end if;
1692 */
1693
1694 if not(p_operation_mode in ('SA','SC')) then -- just phase 1 denorm modes
1695 -- if not(p_operation_mode in ('SA','SE','SC','SP','BC','BA','BR')) then
1696 per_cagr_utility_pkg.log_and_raise_error('HR_289420_CAGR_INV_MODE'
1697 ,p_cagr_request_id);
1698 end if;
1699
1700 if p_operation_mode = 'SA' and p_assignment_id is null then -- SINGLE ASSIGNMENT
1701 per_cagr_utility_pkg.log_and_raise_error('HR_289421_CAGR_INV_SA_PARAM'
1702 ,p_cagr_request_id);
1703 end if;
1704
1705 if p_operation_mode = 'SC'
1706 and (p_collective_agreement_id is null
1707 or p_assignment_id is not null
1708 or p_collective_agreement_set_id is not null) then -- SINGLE CAGR
1709 per_cagr_utility_pkg.log_and_raise_error('HR_289597_INV_SC_PARAM'
1710 ,p_cagr_request_id);
1711 end if;
1712
1713 --
1714 -- populate the record structure
1715 --
1716 l_params.effective_date := trunc(p_process_date);
1717 l_params.operation_mode := p_operation_mode;
1718 l_params.business_group_id := p_business_group_id;
1719 l_params.assignment_id := p_assignment_id;
1720 l_params.assignment_set_id := p_assignment_set_id;
1721 l_params.collective_agreement_id := p_collective_agreement_id;
1722 l_params.cagr_set_id := p_collective_agreement_set_id;
1723 l_params.cagr_request_id := p_cagr_request_id;
1724 l_params.person_id := p_person_id;
1725 l_params.category := p_category;
1726 l_params.entitlement_item_id := p_entitlement_item_id;
1727 l_params.commit_flag := p_commit_flag;
1728 l_params.denormalise_flag := 'Y';
1729
1730 per_cagr_utility_pkg.put_log(' ',1);
1731 per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
1732 per_cagr_utility_pkg.put_log(' ',1);
1733 per_cagr_utility_pkg.put_log(' Mode: '||l_params.operation_mode,1);
1734 per_cagr_utility_pkg.put_log(' CAGR Request ID: '||l_params.cagr_request_id,1);
1735 per_cagr_utility_pkg.put_log(' Effective Date: '||l_params.effective_date,1);
1736 per_cagr_utility_pkg.put_log(' Business Group ID: '||l_params.business_group_id,1);
1737 per_cagr_utility_pkg.put_log(' Assignment ID: '||l_params.assignment_id,1);
1738 per_cagr_utility_pkg.put_log(' Assignment Set ID: '||l_params.assignment_set_id,1);
1739 per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||l_params.collective_agreement_id,1);
1740 per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||l_params.cagr_set_id,1);
1741 per_cagr_utility_pkg.put_log(' Person ID: '||l_params.person_id,1);
1742 per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||l_params.entitlement_item_id,1);
1743 per_cagr_utility_pkg.put_log(' Category: '||l_params.category,1);
1744
1745 /* redundant now chosen values are stored after refresh
1746 If p_select_flag = 'B' then
1747 per_cagr_utility_pkg.put_log(' Beneficial values to be applied.',1);
1748 elsif p_select_flag = 'C' then
1749 per_cagr_utility_pkg.put_log(' Chosen values to be applied.',1);
1750 end if;
1751 */
1752
1753 per_cagr_utility_pkg.put_log(' Commit values flag: '||l_params.commit_flag,1);
1754 per_cagr_utility_pkg.put_log(' ',1);
1755
1756 --
1757 -- ****** This needs to be converted to a parameter passed to create_request,
1758 -- rather than relying on a public package variable *******
1759 --
1760 if fnd_global.conc_request_id <> -1 then
1761 per_cagr_utility_pkg.put_log(' Executed from concurrent manager');
1762 else
1763 per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
1764 end if;
1765 per_cagr_utility_pkg.put_log(' ',1);
1766 per_cagr_utility_pkg.write_log_file(l_params.cagr_request_id);
1767
1768 --
1769 -- invoke denormalization processing;
1770 --
1771 initialise (p_params => l_params
1772 ,p_select_flag => p_select_flag);
1773
1774 -- complete logging
1775 per_cagr_utility_pkg.put_log(per_cagr_evaluation_pkg.g_separator,1);
1776 per_cagr_utility_pkg.write_log_file(l_params.cagr_request_id);
1777
1778 hr_utility.set_location('Leaving:'||l_proc, 50);
1779
1780 END;
1781
1782 END per_cagr_apply_results_pkg;
[Home] [Help]