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