1 PACKAGE BODY pay_au_retro_upgrade AS
2 /* $Header: payauretroupg.pkb 120.14 2007/10/23 07:13:22 avenkatk noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 Name : pay_au_retro_upgrade
24
25 Description : This procedure is used to upgrade elements for
26 Enhanced Retropay.
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ----------- ---------- ------ ------- -----------------------------------
32 05-JAN-2006 ksingla 120.0 4753806 Intial Version
33 29-AUG-2006 priupadh 120.2 5461632 Removed Trace on and Off in Package
34 11-Sep-2006 abhargav 120.3 5461629 Added log messages.
35 13-Sep-2006 abhargav 120.4 5461629 Added check whether debug is enabled before printing hr_utility trace messages.
36 18-Sep-2006 abhargav 120.5 5461633 Modified procedure upgrade_element() and function create_element() so that
37 element get upgraded for the cases where retro element for the element
38 has already been upgraded.
39 19-Sep-2006 abhargav 120.7 5461633 Modified the comments.
40 25-Sep-2006 abhargav 120.8 5556042 Added condition so that new time span Start Of Time - End Of time should not be
41 attached while upgradng earning elements.
42 06-Oct-2006 abhargav 120.9 5583165 Created new procedure element_exist_check() and modified fucntion create_element()
43 to make sure element should not be partially upgrade.
44
45 ================================================================================================
46 11i Versions - Package backported to 11i - Bug 5731490
47 ================================================================================================
48 22-Dec-2006 avenkatk 115.0 5731490 (A)Added Function - set_retro_leg_rule
49 Procedure - create_enhanced_retro_defn
50 (B)Added private procedures to create Enh Retropay Components
51 and retro definitions.
52 (C) Modified procedures qualify element and upgrade element to
53 upgrade Non Earnings Standard and Non Pre Tax elements.
54 04-Jan-2007 avenkatk 115.1 5731490 Added Procedure - set_enh_retro_request_group
55 - enable_au_enhanced_retro
56 17-Jan-2007 avenkatk 115.2 5749509 Fixed issues with Null Values in Once_each_period_flag,OVN etc.
57 23-Feb-2007 abhargav 115.3 5731490 Fixed issue for cases where retro element has multiple links. corrected message logic when retro
58 element is included in multiple element sets.
59 26-Feb-2007 priupadh 115.4 5879422 1. Added Warning Message to remove retro pay by element from all customer menus in procedure enable_au_enhanced_retro
60 2. Added Cursor for listing of all business groups in procedure enable_au_enhanced_retro
61
62 26-Feb-2007 abhargav 115.5 5899688 Modified procedure create_input_value() so that display sequence of input value 'Pay Value' get created
63 same as of display sequence of retro element.
64 27-Feb-2007 priupadh 115.6 5879422 Adjusted Spaces in Warning message and added bug references
65
66 22-Oct-2007 avenkatk 115.7 6455303 Added balance feeds for Retro Pre Tax Deductions LT12 Prev and GT12
67 */
68
69 gv_package_name VARCHAR2(100);
70 gn_time_span_id NUMBER;
71 gn_retro_component_id NUMBER;
72 g_legislation_code VARCHAR2(10);
73 g_debug boolean;
74
75 /* Procedure attaches the event group returned by the procedure create_event_group
76 to the element which is retro paid. */
77
78 PROCEDURE insert_event_group(p_business_group_id IN NUMBER
79 ,p_element_type_id IN NUMBER
80 ,p_event_group_id IN NUMBER)
81 IS
82
83 /* Cursor fetches the information of retro element */
84 /* Bug 5749509 - Modified cursor for Time_definition_type.
85 'N' is not a valid value for Time Definition Type, set value as Null
86 */
87 cursor c_get_retro_element_info
88 is
89 select
90 ELEMENT_TYPE_ID,
91 EFFECTIVE_START_DATE,
92 EFFECTIVE_END_DATE,
93 FORMULA_ID,
94 INPUT_CURRENCY_CODE,
95 OUTPUT_CURRENCY_CODE,
96 CLASSIFICATION_ID,
97 BENEFIT_CLASSIFICATION_ID,
98 ADDITIONAL_ENTRY_ALLOWED_FLAG,
99 ADJUSTMENT_ONLY_FLAG,
100 CLOSED_FOR_ENTRY_FLAG,
101 ELEMENT_NAME,
102 REPORTING_NAME,
103 DESCRIPTION,
104 INDIRECT_ONLY_FLAG,
105 MULTIPLE_ENTRIES_ALLOWED_FLAG,
106 MULTIPLY_VALUE_FLAG,
107 POST_TERMINATION_RULE,
108 PROCESS_IN_RUN_FLAG,
109 PROCESSING_PRIORITY,
110 PROCESSING_TYPE,
111 STANDARD_LINK_FLAG,
112 COMMENT_ID,
113 LEGISLATION_SUBGROUP,
114 QUALIFYING_AGE,
115 QUALIFYING_LENGTH_OF_SERVICE,
116 QUALIFYING_UNITS,
117 ELEMENT_INFORMATION_CATEGORY,
118 ELEMENT_INFORMATION1,
119 ELEMENT_INFORMATION2,
120 ELEMENT_INFORMATION3,
121 THIRD_PARTY_PAY_ONLY_FLAG,
122 ITERATIVE_FLAG,
123 ITERATIVE_FORMULA_ID,
124 ITERATIVE_PRIORITY,
125 CREATOR_TYPE,
126 RETRO_SUMM_ELE_ID,
127 GROSSUP_FLAG,
128 PROCESS_MODE,
129 ADVANCE_INDICATOR,
130 ADVANCE_PAYABLE,
131 ADVANCE_DEDUCTION,
132 PROCESS_ADVANCE_ENTRY,
133 PRORATION_GROUP_ID,
134 PRORATION_FORMULA_ID,
135 RECALC_EVENT_GROUP_ID,
136 ONCE_EACH_PERIOD_FLAG,
137 decode(TIME_DEFINITION_TYPE,'N',NULL,TIME_DEFINITION_TYPE) TIME_DEFINITION_TYPE, /* Bug 5749509*/
138 TIME_DEFINITION_ID,
139 OBJECT_VERSION_NUMBER
140 from pay_element_types_f
141 where element_type_id = p_element_type_id
142 and business_group_id = p_business_group_id
143 ORDER BY effective_start_date;
144
145 rec_element_types c_get_retro_element_info%ROWTYPE;
146 l_effective_start_date DATE;
147 l_effective_end_date DATE;
148 l_comment_id NUMBER;
149 lv_procedure_name VARCHAR2(50);
150 l_ovn NUMBER;
151 l_processing_priority_warning BOOLEAN ;
152 l_element_name_warning BOOLEAN;
153 l_element_name_change_warning BOOLEAN;
154
155 BEGIN
156 g_debug := hr_utility.debug_enabled;
157 lv_procedure_name := 'insert_event_group';
158
159 IF g_debug THEN
160 hr_utility.trace('Entering ' || lv_procedure_name);
161 END if;
162
163
164 open c_get_retro_element_info;
165 LOOP
166 fetch c_get_retro_element_info into rec_element_types;
167
168 IF c_get_retro_element_info%NOTFOUND THEN
169 EXIT;
170 END IF;
171
172 IF rec_element_types.RECALC_EVENT_GROUP_ID IS NULL THEN
173
174 /* Bug 5749509 - Set OVN as 1 if its found to be NULL */
175
176 l_ovn := NVL(rec_element_types.OBJECT_VERSION_NUMBER,1);
177
178 /* Bug 5749509 - ONCE_EACH_PERIOD_FLAG - Specify default of 'N' if existing value is Null
179 - TIME_DEFINITION_TYPE,TIME_DEFINITION_TYPE added in API call
180 - If TIME_DEFINITION_TYPE is 'N', set it as Null
181 */
182 PAY_ELEMENT_TYPES_API.UPDATE_ELEMENT_TYPE
183 (p_effective_date => rec_element_types.EFFECTIVE_START_DATE
184 ,p_datetrack_update_mode => 'CORRECTION'
185 ,p_element_type_id => rec_element_types.ELEMENT_TYPE_ID
186 ,p_object_version_number => l_ovn
187 ,p_recalc_event_group_id => p_event_group_id
188 ,p_formula_id => rec_element_types.FORMULA_ID
189 ,p_benefit_classification_id => rec_element_types.BENEFIT_CLASSIFICATION_ID
190 ,p_additional_entry_allowed_fla => rec_element_types.ADDITIONAL_ENTRY_ALLOWED_FLAG
191 ,p_adjustment_only_flag => rec_element_types.ADJUSTMENT_ONLY_FLAG
192 ,p_closed_for_entry_flag => rec_element_types.CLOSED_FOR_ENTRY_FLAG
193 ,p_element_name => rec_element_types.ELEMENT_NAME
194 ,p_reporting_name => rec_element_types.REPORTING_NAME
195 ,p_description => rec_element_types.DESCRIPTION
196 ,p_indirect_only_flag => rec_element_types.INDIRECT_ONLY_FLAG
197 ,p_multiple_entries_allowed_fla => rec_element_types.MULTIPLE_ENTRIES_ALLOWED_FLAG
198 ,p_multiply_value_flag => rec_element_types.MULTIPLY_VALUE_FLAG
199 ,p_post_termination_rule => rec_element_types.POST_TERMINATION_RULE
200 ,p_process_in_run_flag => rec_element_types.PROCESS_IN_RUN_FLAG
201 ,p_processing_priority => rec_element_types.PROCESSING_PRIORITY
202 ,p_standard_link_flag => rec_element_types.STANDARD_LINK_FLAG
203 ,p_third_party_pay_only_flag => rec_element_types.THIRD_PARTY_PAY_ONLY_FLAG
204 ,p_iterative_flag => rec_element_types.ITERATIVE_FLAG
205 ,p_iterative_formula_id => rec_element_types.ITERATIVE_FORMULA_ID
206 ,p_iterative_priority => rec_element_types.ITERATIVE_PRIORITY
207 ,p_creator_type => rec_element_types.CREATOR_TYPE
208 ,p_retro_summ_ele_id => rec_element_types.RETRO_SUMM_ELE_ID
209 ,p_grossup_flag => rec_element_types.GROSSUP_FLAG
210 ,p_process_mode => rec_element_types.PROCESS_MODE
211 ,p_advance_indicator => rec_element_types.ADVANCE_INDICATOR
212 ,p_advance_payable => rec_element_types.ADVANCE_PAYABLE
213 ,p_advance_deduction => rec_element_types.ADVANCE_DEDUCTION
214 ,p_process_advance_entry => rec_element_types.PROCESS_ADVANCE_ENTRY
215 ,p_proration_group_id => rec_element_types.PRORATION_GROUP_ID
216 ,p_proration_formula_id => rec_element_types.PRORATION_FORMULA_ID
217 ,p_qualifying_age => rec_element_types.QUALIFYING_AGE
218 ,p_qualifying_length_of_service => rec_element_types.QUALIFYING_LENGTH_OF_SERVICE
219 ,p_qualifying_units => rec_element_types.QUALIFYING_UNITS
220 ,p_element_information_category => rec_element_types.ELEMENT_INFORMATION_CATEGORY
221 ,p_element_information1 => rec_element_types.ELEMENT_INFORMATION1
222 ,p_element_information2 => rec_element_types.ELEMENT_INFORMATION2
223 ,p_element_information3 => rec_element_types.ELEMENT_INFORMATION3
224 ,p_once_each_period_flag => nvl(rec_element_types.ONCE_EACH_PERIOD_FLAG,'N')
225 ,p_time_definition_type => rec_element_types.TIME_DEFINITION_TYPE
226 ,p_time_definition_id => rec_element_types.TIME_DEFINITION_ID
227 ,p_effective_start_date => l_effective_start_date
228 ,p_effective_end_date => l_effective_end_date
229 ,p_comment_id => l_comment_id
230 ,p_processing_priority_warning => l_processing_priority_warning
231 ,p_element_name_warning => l_element_name_warning
232 ,p_element_name_change_warning => l_element_name_change_warning
233 );
234
235 IF g_debug THEN
236 hr_utility.trace('Updated Event Group for Element: ' || p_element_type_id);
237 END if;
238
239 END IF;
240
241 END LOOP;
242
243 close c_get_retro_element_info;
244
245 IF g_debug THEN
246 hr_utility.trace('Leaving ' || lv_procedure_name);
247 END if;
248
249 exception
250 when others then
251 IF g_debug THEN
252 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
253 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
254 End if;
255
256 raise;
257
258 END;
259
260 /* Procedure creates the event group of name "AU Enhanced Retro Event Group"
261 and returns the event group id. */
262
263 PROCEDURE create_event_group(p_business_group_id IN NUMBER,
264 p_event_group_id OUT NOCOPY NUMBER)
265 IS
266
267 /* Checks whehter AU Enhanced Retro Event Group already exist */
268
269 CURSOR c_get_event_group_id
270 IS
271 SELECT event_group_id
272 FROM pay_event_groups
273 WHERE business_group_id = p_business_group_id
274 AND event_group_name = 'AU Enhanced Retro Event Group';
275
276 /* Gets the dated_table_id for tables PAY_ELEMENT_ENTRIES_F and PAY_ELEMENT_ENTRY_VALUES_F */
277 CURSOR c_get_dated_table_id(c_table_name pay_dated_tables.table_name%TYPE)
278 IS
279 select dated_table_id
280 from pay_dated_tables
281 where table_name = c_table_name;
282
283 l_event_group_id NUMBER;
284 l_effective_date DATE;
285 l_ovn NUMBER;
286 lv_procedure_name VARCHAR2(50);
287 l_datetracked_event_id NUMBER;
288 l_ele_entry_table_id NUMBER;
289 l_ele_entry_value_table_id NUMBER;
290
291
292 BEGIN
293 g_debug := hr_utility.debug_enabled;
294 l_effective_date := to_date('1900/01/01','YYYY/MM/DD');
295
296 lv_procedure_name := 'create_event_group';
297
298 IF g_debug THEN
302 /* Checks whether event group already exist */
299 hr_utility.trace('Entering ' || lv_procedure_name);
300 END if;
301
303 OPEN c_get_event_group_id;
304 FETCH c_get_event_group_id INTO l_event_group_id;
305 IF c_get_event_group_id%NOTFOUND THEN
306 pay_event_groups_api.create_event_group(p_effective_date => l_effective_date
307 ,p_event_group_name => 'AU Enhanced Retro Event Group'
308 ,p_event_group_type => 'R'
309 ,p_business_group_id => p_business_group_id
310 ,p_event_group_id => l_event_group_id
311 ,p_object_version_number => l_ovn
312 );
313 OPEN c_get_dated_table_id('PAY_ELEMENT_ENTRIES_F');
314 FETCH c_get_dated_table_id INTO l_ele_entry_table_id;
315 CLOSE c_get_dated_table_id;
316
317 OPEN c_get_dated_table_id('PAY_ELEMENT_ENTRY_VALUES_F');
318 FETCH c_get_dated_table_id INTO l_ele_entry_value_table_id;
319 CLOSE c_get_dated_table_id;
320
321 /* Creates Date Tracked event of Type Update on column EFFECTIVE_START_DATE of table PAY_ELEMENT_ENTRIES_F */
322 pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
323 ,p_event_group_id => l_event_group_id
324 ,p_dated_table_id => l_ele_entry_table_id
325 ,p_update_type => 'U'
326 ,p_column_name => 'EFFECTIVE_START_DATE'
327 ,p_business_group_id => p_business_group_id
328 ,p_legislation_code => NULL
329 ,p_datetracked_event_id => l_datetracked_event_id
330 ,p_object_version_number => l_ovn
331 );
332 /* Creates Date Tracked event of Type Update on column EFFECTIVE_END_DATE of table PAY_ELEMENT_ENTRIES_F */
333 pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
334 ,p_event_group_id => l_event_group_id
335 ,p_dated_table_id => l_ele_entry_table_id
336 ,p_update_type => 'U'
337 ,p_column_name => 'EFFECTIVE_END_DATE'
338 ,p_business_group_id => p_business_group_id
339 ,p_legislation_code => NULL
340 ,p_datetracked_event_id => l_datetracked_event_id
341 ,p_object_version_number => l_ovn
342 );
343 /* Creates Date Tracked event of Type End Date on table PAY_ELEMENT_ENTRIES_F */
344 pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
345 ,p_event_group_id => l_event_group_id
346 ,p_dated_table_id => l_ele_entry_table_id
347 ,p_update_type => 'E'
348 ,p_column_name => NULL
349 ,p_business_group_id => p_business_group_id
350 ,p_legislation_code => NULL
351 ,p_datetracked_event_id => l_datetracked_event_id
352 ,p_object_version_number => l_ovn
353 );
354 /* Creates Date Tracked event of Type insert on table PAY_ELEMENT_ENTRIES_F */
355
356 pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
357 ,p_event_group_id => l_event_group_id
358 ,p_dated_table_id => l_ele_entry_table_id
359 ,p_update_type => 'I'
360 ,p_column_name => NULL
361 ,p_business_group_id => p_business_group_id
362 ,p_legislation_code => NULL
363 ,p_datetracked_event_id => l_datetracked_event_id
364 ,p_object_version_number => l_ovn
365 );
366 /* Creates Date Tracked event of Type delete on table PAY_ELEMENT_ENTRIES_F */
370 ,p_update_type => 'D'
367 pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
368 ,p_event_group_id => l_event_group_id
369 ,p_dated_table_id => l_ele_entry_table_id
371 ,p_column_name => NULL
372 ,p_business_group_id => p_business_group_id
373 ,p_legislation_code => NULL
374 ,p_datetracked_event_id => l_datetracked_event_id
375 ,p_object_version_number => l_ovn
376 );
377 /* Creates Date Tracked event of Type Correction on table PAY_ELEMENT_ENTRY_VALUES_F */
378 pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
379 ,p_event_group_id => l_event_group_id
380 ,p_dated_table_id => l_ele_entry_value_table_id
381 ,p_update_type => 'C'
382 ,p_column_name => 'SCREEN_ENTRY_VALUE'
383 ,p_business_group_id => p_business_group_id
384 ,p_legislation_code => NULL
385 ,p_datetracked_event_id => l_datetracked_event_id
386 ,p_object_version_number => l_ovn
387 );
388
389 END IF;
390
391 p_event_group_id := l_event_group_id;
392
393 IF g_debug THEN
394 hr_utility.trace('p_event_group_id: ' || p_event_group_id);
395 hr_utility.trace('Leaving ' || lv_procedure_name);
396 End If;
397
398 exception
399 when others then
400 IF g_debug THEN
401 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
402 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
403 End If;
404
405 raise;
406 END;
407
408 /* procedure attaches the formula AU_RETRO_PROCESSED_COUNT to the created elements and also create the required formula result rules.
409 Following parameters are passed to the procedure:
410 a) Business Group ID
411 b) Retro Type - This parameter can have three values: "GT12", "LT12 Prev", and "LT12 Curr".
412 c) Retro Element Type ID: In parameter to hold the element type id of the retro element created. */
413 PROCEDURE create_ff_results(p_business_group_id IN NUMBER,
414 p_retro_type IN VARCHAR2,
415 p_element_type_id IN NUMBER)
416 IS
417
418 /* Gets the formula id for formula AU_RETRO_PROCESSED_COUNT*/
419
420 CURSOR c_get_formula_id
421 IS
422 SELECT ff.formula_id
423 FROM ff_formulas_f ff
424 WHERE ff.formula_name = 'AU_RETRO_PROCESSED_COUNT'
425 AND ff.legislation_code = 'AU';
426
427 /* Gets the processing rule id for element */
428 CURSOR c_get_formula_results(c_element_type_id pay_element_types_f.element_type_id%type)
429 IS
430 SELECT pspr.status_processing_rule_id
431 FROM pay_status_processing_rules_f pspr
432 WHERE pspr.business_group_id = p_business_group_id
433 AND pspr.element_type_id = c_element_type_id;
434
435 /* Checks whether formula result rule exist for the element */
436 CURSOR c_get_formula_result_rules(c_element_type_id pay_element_types_f.element_type_id%type,
437 c_result_name pay_formula_result_rules_f.result_name%type,
438 c_result_rule_type pay_formula_result_rules_f.result_rule_type%type,
439 c_input_value_id pay_formula_result_rules_f.input_value_id%type)
440 IS
441 select count(*)
442 from pay_status_processing_rules_f pssp,
443 pay_formula_result_rules_f pfrr
444 where pssp.element_type_id = c_element_type_id
445 and pfrr.status_processing_rule_id = pssp.status_processing_rule_id
446 AND pfrr.result_name = c_result_name
447 AND pfrr.result_rule_type = c_result_rule_type
448 AND DECODE(c_result_rule_type, 'M', '999', pfrr.input_value_id) = DECODE(c_result_rule_type, 'M', '999', c_input_value_id);
449
450 /* Fetches the input value id for seeded Retropay elements */
451
452 CURSOR c_get_input_value_id(c_element_name pay_element_types_f.element_name%type, c_name pay_input_values_f.name%type)
453 IS
454 SELECT DISTINCT pet.element_type_id, piv.input_value_id
455 FROM pay_input_values_f piv,
456 pay_element_types_f pet
457 WHERE pet.element_name = c_element_name
458 AND pet.legislation_code = 'AU'
459 AND piv.element_type_id = pet.element_type_id
460 AND piv.NAME = c_name
461 AND piv.legislation_code = 'AU';
462
463 l_status_processing_rule_id NUMBER;
464 l_legislation_code VARCHAR2(10);
465 l_effective_start_date DATE ;
466 l_effective_end_date DATE ;
467 l_processing_rule VARCHAR2(2);
468 l_formula_id NUMBER;
472 l_input_value_id NUMBER;
469 l_result_name VARCHAR2(30);
470 l_result_element_name VARCHAR2(50);
471 l_result_rule_type VARCHAR2(10);
473 l_rules_exists NUMBER;
474 l_rowid VARCHAR2(100);
475 l_formula_result_rule_id NUMBER;
476 l_element_type_id NUMBER;
477 lv_procedure_name VARCHAR2(50);
478
479 BEGIN
480 g_debug := hr_utility.debug_enabled;
481 lv_procedure_name := 'create_ff_results';
482
483 IF g_debug THEN
484 hr_utility.trace('Entering ' || lv_procedure_name);
485 End if;
486
487
488 l_legislation_code := 'AU';
489 l_effective_start_date := to_date('2005/07/01','YYYY/MM/DD');
490 l_effective_end_date := to_date('4712/12/31','YYYY/MM/DD');
491 l_processing_rule := 'P';
492 l_result_name := 'L_DUMMY';
493 l_result_rule_type := 'I';
494
495 IF g_debug THEN
496 hr_utility.trace('p_business_group_id: ' || p_business_group_id);
497 hr_utility.trace('p_retro_type: ' || p_retro_type);
498 hr_utility.trace('p_element_type_id: ' || p_element_type_id);
499 End if;
500
501 /* Gets the processing rule id for element */
502 OPEN c_get_formula_results(p_element_type_id);
503 FETCH c_get_formula_results INTO l_status_processing_rule_id;
504 CLOSE c_get_formula_results;
505
506 /* Gets the formula id for formula AU_RETRO_PROCESSED_COUNT*/
507 OPEN c_get_formula_id;
508 FETCH c_get_formula_id INTO l_formula_id;
509 CLOSE c_get_formula_id;
510
511 IF g_debug THEN
512 hr_utility.trace('l_formula_id: ' || l_formula_id);
513 End if;
514
515 IF nvl(l_status_processing_rule_id,9999) = 9999 THEN
516
517 l_Status_Processing_Rule_Id := pay_formula_results.ins_stat_proc_rule(
518 p_legislation_code => l_legislation_code,
519 p_effective_start_date => l_effective_start_date,
520 p_effective_end_date => l_effective_end_date,
521 p_element_type_id => p_element_type_id,
522 p_formula_id => l_formula_id,
523 p_processing_rule => l_processing_rule);
524 END IF;
525
526
527 IF p_retro_type = 'GT12' THEN
528
529 l_result_element_name := 'Retropay GT 12 Mths Amount';
530 OPEN c_get_input_value_id('Retropay GT 12 Mths Amount','GT_12_Mths_Amount');
531 FETCH c_get_input_value_id INTO l_element_type_id, l_input_value_id;
532 CLOSE c_get_input_value_id;
533
534 ELSIF p_retro_type = 'LT12 Prev' THEN
535
536 l_result_element_name := 'Retropay LT 12 Mths Prev Yr Amount';
537 OPEN c_get_input_value_id('Retropay LT 12 Mths Prev Yr Amount','LT_12_Mths_Prev_Yr_Amount');
538 FETCH c_get_input_value_id INTO l_element_type_id, l_input_value_id;
539 CLOSE c_get_input_value_id;
540
541 ELSIF p_retro_type = 'LT12 Curr' THEN
542
543 l_result_element_name := 'Retropay LT 12 Mths Curr Yr Amount';
544 OPEN c_get_input_value_id('Retropay LT 12 Mths Curr Yr Amount','LT_12_Mths_Curr_Yr_Amount');
545 FETCH c_get_input_value_id INTO l_element_type_id, l_input_value_id;
546 CLOSE c_get_input_value_id;
547
548 END IF;
549 /* Checks whether formula result rule exists for the retro element */
550
551 OPEN c_get_formula_result_rules(p_element_type_id,
552 l_result_name,
553 l_result_rule_type,
554 l_input_value_id);
555 FETCH c_get_formula_result_rules INTO l_rules_exists;
556 CLOSE c_get_formula_result_rules;
557
558 IF l_rules_exists = 0 THEN
559
560 l_formula_result_rule_id := pay_formula_results.ins_form_res_rule
561 (
562 p_business_group_id => p_business_group_id,
563 p_effective_start_date => l_effective_start_date,
564 p_effective_end_date => l_effective_end_date,
565 p_status_processing_rule_id => l_status_processing_rule_id,
566 p_input_value_id => l_input_value_id,
567 p_result_name => l_result_name,
568 p_result_rule_type => l_result_rule_type,
569 p_element_type_id => l_element_type_id
570 );
571 END IF;
572
573 IF g_debug THEN
574 hr_utility.trace('Leaving ' || lv_procedure_name);
575 End if;
576
577 exception
578 when others then
579 IF g_debug THEN
580 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
581 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
582 End if;
583 raise;
584 END;
585
586 /* Procedure creates the balance feeds for the created elements.
587 Following parameters are passed to the above procedure:
588 a) Business Group ID
589 b) Element Type ID - Element Type ID of the element for which the Retro element is created
590 c) Retro Type - This parameter can have three values: "GT12", "LT12 Prev", and "LT12 Curr".
591 d) Retro Element Type ID: In parameter to hold the element type id of the retro element created.
592 e) Balance Type Id - Balance Type Id of the seeded balance
593 f) Scale - This parameter decides whether the pay value of the retro element should add or subtract to
594 the seeded balance based on element classification. */
595
596 procedure create_balance_feeds(p_business_group_id IN NUMBER,
597 p_retro_element_id IN NUMBER,
598 p_retro_type IN VARCHAR2,
602 IS
599 p_element_type_id IN NUMBER,
600 p_balance_type_id IN NUMBER,
601 p_scale IN NUMBER)
603
604 /* Gets the input value id of Retro Element */
605 CURSOR c_get_input_values
606 IS
607 SELECT name,
608 input_value_id
609 FROM pay_input_values_f
610 WHERE business_group_id = p_business_group_id
611 AND element_type_id = p_retro_element_id;
612
613 /* Gets the balance feeds attached with the input value of retro element*/
614 CURSOR c_get_balance_feeds(c_input_value_id pay_input_values_f.input_value_id%TYPE)
615 IS
616 SELECT balance_type_id, scale
617 FROM pay_balance_feeds_f
618 WHERE input_value_id = c_input_value_id
619 AND business_group_id = p_business_group_id;
620
621 /* Checks whether balance feed exist for element created by upgrade process */
622 cursor check_feed_exists(c_balance_type_id pay_balance_feeds_f.balance_type_id%type
623 ,c_input_value_id pay_balance_feeds_f.input_value_id%type)
624 is
625 select count(*)
626 from pay_balance_feeds_f
627 where balance_type_id = c_balance_type_id
628 and input_value_id = c_input_value_id;
629
630 /* Gets the input value id of Element created by Upgrade Process*/
631 CURSOR c_get_input_value_id(c_name pay_input_values_f.name%type)
632 IS
633 SELECT input_value_id
634 FROM pay_input_values_f
635 WHERE NAME = c_name
636 AND element_type_id = p_element_type_id
637 AND business_group_id = p_business_group_id;
638
639 l_input_value_id NUMBER;
640 l_exists NUMBER;
641 l_effective_date DATE;
642 lv_procedure_name VARCHAR2(50);
643
644 BEGIN
645 g_debug := hr_utility.debug_enabled;
646
647 lv_procedure_name := 'create_balance_feeds';
648
649 IF g_debug THEN
650 hr_utility.trace('Entering ' || lv_procedure_name);
651 End if;
652
653 l_effective_date := to_date('2005/07/01','YYYY/MM/DD');
654
655 /* Gets the input value id of Retro Element */
656 FOR csr_rec_iv IN c_get_input_values
657 LOOP
658 /* Gets the balance feed attached with the input value */
659 FOR csr_rec_bf IN c_get_balance_feeds(csr_rec_iv.input_value_id)
660 LOOP
661 /* Gets the input value id of Element created by Upgrade Process*/
662 OPEN c_get_input_value_id(csr_rec_iv.name);
663 FETCH c_get_input_value_id INTO l_input_value_id;
664 CLOSE c_get_input_value_id;
665
666 IF g_debug THEN
667 hr_utility.trace('Creating Balance Feed for input value ' || csr_rec_iv.name);
668 hr_utility.trace('Input Value ID: ' || l_input_value_id);
669 End if;
670
671 IF UPPER(csr_rec_iv.NAME) = UPPER('Pay Value') THEN
672 /* Checks whether seeded balance feed exist for element created by upgrade process */
673 OPEN check_feed_exists(p_balance_type_id, l_input_value_id);
674 FETCH check_feed_exists INTO l_exists;
675 CLOSE check_feed_exists;
676 /* Checks whether balance feed does not exist and element classification is not of type Pre Tax Deductions */
677 IF l_exists = 0 AND p_scale <> 0 THEN
678
679 IF g_debug THEN
680 hr_utility.trace('Create Seeded Balance Feed');
681 End if;
682 /* Creates balance feeds for the elements created by upgrade process */
683 hr_balances.ins_balance_feed(
684 p_option => 'INS_MANUAL_FEED',
685 p_input_value_id => l_input_value_id,
686 p_element_type_id => NULL,
687 p_primary_classification_id => NULL,
688 p_sub_classification_id => NULL,
689 p_sub_classification_rule_id => NULL,
690 p_balance_type_id => p_balance_type_id,
691 p_scale => to_char(p_scale),
692 p_session_date => l_effective_date,
693 p_business_group => p_business_group_id,
694 p_legislation_code => NULL,
695 p_mode => 'USER');
696 IF g_debug THEN
697 hr_utility.trace('Created Seeded Balance Feed');
698 End if;
699 END IF;
700 END IF;
701 /* Checks whether user balance feed exist for element created by upgrade process */
702 OPEN check_feed_exists(csr_rec_bf.balance_type_id, l_input_value_id);
703 FETCH check_feed_exists INTO l_exists;
704 CLOSE check_feed_exists;
705
706 IF l_exists = 0 THEN
707
708 IF g_debug THEN
709 hr_utility.trace('Create User Balance Feed FOR balance TYPE id: ' || csr_rec_bf.balance_type_id);
710 End if;
711
712 hr_balances.ins_balance_feed(
713 p_option => 'INS_MANUAL_FEED',
714 p_input_value_id => l_input_value_id,
715 p_element_type_id => NULL,
716 p_primary_classification_id => NULL,
717 p_sub_classification_id => NULL,
718 p_sub_classification_rule_id => NULL,
722 p_business_group => p_business_group_id,
719 p_balance_type_id => csr_rec_bf.balance_type_id,
720 p_scale => to_char(csr_rec_bf.scale),
721 p_session_date => l_effective_date,
723 p_legislation_code => NULL,
724 p_mode => 'USER');
725 IF g_debug THEN
726 hr_utility.trace('Created User Balance Feed');
727 End if;
728
729 END IF;
730
731 END LOOP;
732
733 END LOOP;
734
735 IF g_debug THEN
736 hr_utility.trace('Leaving ' || lv_procedure_name);
737 End if;
738 exception
739 when others then
740 IF g_debug THEN
741 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
742 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
743 End if;
744 raise;
745 END;
746
747 /* procedure creates the input value for the retro elements created.
748 Following parameters are passed to the above procedure:
749 a) Business Group ID
750 b) Element Type ID - Element Type ID of the element for which the Retro element is created
751 c) Retro Type - This parameter can have three values: "GT12", "LT12 Prev", and "LT12 Curr"
752 d) Retro Element Type ID: In parameter to hold the element type id of the retro element created. */
753 procedure create_input_value(p_business_group_id IN NUMBER,
754 p_retro_element_id IN NUMBER,
755 p_retro_type IN VARCHAR2,
756 p_element_type_id IN NUMBER)
757 is
758 /* Fetches the details of input value of retro element */
759 cursor c_get_input_value_info
760 is
761 select
762 LOOKUP_TYPE,
763 BUSINESS_GROUP_ID,
764 FORMULA_ID,
765 DISPLAY_SEQUENCE,
766 GENERATE_DB_ITEMS_FLAG,
767 HOT_DEFAULT_FLAG,
768 MANDATORY_FLAG,
769 NAME,
770 UOM,
771 DEFAULT_VALUE,
772 MAX_VALUE,
773 MIN_VALUE,
774 WARNING_OR_ERROR,
775 VALUE_SET_ID
776 from pay_input_values_f
777 where element_type_id = p_retro_element_id
778 and business_group_id = p_business_group_id;
779
780 rec_input_values c_get_input_value_info%ROWTYPE;
781 l_input_value_id NUMBER;
782 l_ovn NUMBER ;
783 l_effective_start_date date;
784 l_effective_end_date date;
785 l_val_warning boolean;
786 l_max_warning boolean;
787 l_basis_warning boolean;
788 l_formula_warning boolean;
789 l_asg_warning boolean;
790 l_message VARCHAR2(100);
791 l_effective_date date;
792
793 /* Checks whether input value already exist for the element created by Upgrade process */
794
795 CURSOR c_input_exists(c_input_value_name VARCHAR2)
796 IS
797 SELECT piv.input_value_id,effective_start_date,object_version_number
798 FROM pay_input_values_f piv
799 WHERE piv.element_type_id = p_element_type_id
800 AND piv.NAME = c_input_value_name
801 AND piv.business_group_id = p_business_group_id;
802
803 lv_procedure_name VARCHAR2(50);
804
805 /* Bug#5899688 */
806 l_EFFECTIVE_START_DATE_invl date;
807 l_EFFECTIVE_END_DATE_invl date;
808 l_DEFAULT_VAL_WARNING_invl boolean;
809 l_MIN_MAX_WARNING_invl boolean;
810 l_LINK_INP_VAL_WARNING_invl boolean;
811 l_PAY_BASIS_WARNING_invl boolean;
812 l_FORMULA_WARNING_invl boolean;
813 l_ASSIGNMENT_ID_WARNING_invl boolean;
814 l_FORMULA_MESSAGE_invl varchar2(100);
815 lv_ovn_invl number;
816
817 rec_input_values_user c_input_exists%ROWTYPE;
818 begin
819 g_debug := hr_utility.debug_enabled;
820 lv_procedure_name := 'create_input_value';
821
822 IF g_debug THEN
823 hr_utility.trace('Entering ' || lv_procedure_name);
824 End if;
825
826 l_effective_date := to_date('2005/07/01','YYYY/MM/DD');
827
828 IF g_debug THEN
829 hr_utility.trace(p_business_group_id || ' p_business_group_id');
830 hr_utility.trace(p_retro_element_id || ' p_retro_element_id');
831 hr_utility.trace(p_element_type_id || ' p_element_type_id');
832 hr_utility.trace(p_retro_type || ' p_retro_type');
833 End if;
834
835 /* Fetches the details of input value of retro element */
836 open c_get_input_value_info;
837 loop
838 fetch c_get_input_value_info into rec_input_values;
839 /* Exit if retro element does not exist */
840 IF c_get_input_value_info%NOTFOUND THEN
841 exit;
842 END IF;
843 /* Checks whether input value already exist for the element created by Upgrade process */
844 open c_input_exists(rec_input_values.NAME);
845 fetch c_input_exists into rec_input_values_user;
846 /* Bug#5899688 checking whether sequence of input value 'Pay Value' of GT12, LT12 elements
847 is different from retro element */
848 if c_input_exists%found and rec_input_values.name='Pay Value'
849 and rec_input_values.DISPLAY_SEQUENCE <> 1 then
850
851 lv_ovn_invl := rec_input_values_user.object_version_number;
852 /* Bug#5899688 updates display sequence of input value 'Pay Value' of GT12, LT12 elements
853 if sequence is different from retro element's input value */
857 ,P_INPUT_VALUE_ID => rec_input_values_user.input_value_id
854 PAY_INPUT_VALUE_API.UPDATE_INPUT_VALUE
855 ( P_EFFECTIVE_DATE => rec_input_values_user.effective_start_date
856 ,P_DATETRACK_MODE => 'CORRECTION'
858 ,P_OBJECT_VERSION_NUMBER => lv_ovn_invl
859 ,P_DISPLAY_SEQUENCE => rec_input_values.DISPLAY_SEQUENCE
860 ,P_EFFECTIVE_START_DATE => l_EFFECTIVE_START_DATE_invl
861 ,P_EFFECTIVE_END_DATE => l_EFFECTIVE_END_DATE_invl
862 ,P_DEFAULT_VAL_WARNING => l_DEFAULT_VAL_WARNING_invl
863 ,P_MIN_MAX_WARNING => l_MIN_MAX_WARNING_invl
864 ,P_LINK_INP_VAL_WARNING => l_LINK_INP_VAL_WARNING_invl
865 ,P_PAY_BASIS_WARNING => l_PAY_BASIS_WARNING_invl
866 ,P_FORMULA_WARNING => l_FORMULA_WARNING_invl
867 ,P_ASSIGNMENT_ID_WARNING => l_ASSIGNMENT_ID_WARNING_invl
868 ,P_FORMULA_MESSAGE => l_FORMULA_MESSAGE_invl
869 );
870 end if;
871
872 if c_input_exists%notfound then
873 IF g_debug THEN
874 hr_utility.trace(rec_input_values.NAME || ' does not exists');
875 End if;
876 /* Created input value similar to retro element for the element created by Upgrade process */
877 PAY_INPUT_VALUE_API.CREATE_INPUT_VALUE
878 ( P_EFFECTIVE_DATE => l_effective_date
879 ,P_ELEMENT_TYPE_ID => p_element_type_id
880 ,P_NAME => rec_input_values.NAME
881 ,P_UOM => rec_input_values.UOM
882 ,P_LOOKUP_TYPE => rec_input_values.LOOKUP_TYPE
883 ,P_FORMULA_ID => rec_input_values.FORMULA_ID
884 ,P_VALUE_SET_ID => rec_input_values.VALUE_SET_ID
885 ,P_DISPLAY_SEQUENCE => rec_input_values.DISPLAY_SEQUENCE
886 ,P_GENERATE_DB_ITEMS_FLAG => rec_input_values.GENERATE_DB_ITEMS_FLAG
887 ,P_HOT_DEFAULT_FLAG => rec_input_values.HOT_DEFAULT_FLAG
888 ,P_MANDATORY_FLAG => rec_input_values.MANDATORY_FLAG
889 ,P_DEFAULT_VALUE => rec_input_values.DEFAULT_VALUE
890 ,P_MAX_VALUE => rec_input_values.MAX_VALUE
891 ,P_MIN_VALUE => rec_input_values.MIN_VALUE
892 ,P_WARNING_OR_ERROR => rec_input_values.WARNING_OR_ERROR
893 ,P_INPUT_VALUE_ID => l_input_value_id
894 ,P_OBJECT_VERSION_NUMBER => l_ovn
895 ,P_EFFECTIVE_START_DATE => l_effective_start_date
896 ,P_EFFECTIVE_END_DATE => l_effective_end_date
897 ,P_DEFAULT_VAL_WARNING => l_val_warning
898 ,P_MIN_MAX_WARNING => l_max_warning
899 ,P_PAY_BASIS_WARNING => l_basis_warning
900 ,P_FORMULA_WARNING => l_formula_warning
901 ,P_ASSIGNMENT_ID_WARNING => l_asg_warning
902 ,P_FORMULA_MESSAGE => l_message
903 );
904
905 IF g_debug THEN
906 hr_utility.trace(rec_input_values.NAME || ' Created');
907 End if;
908
909 end if;
910 close c_input_exists;
911
912 end loop;
913
914 close c_get_input_value_info;
915
916 IF g_debug THEN
917 hr_utility.trace('Leaving ' || lv_procedure_name);
918 End if;
919 exception
920 when others then
921 IF g_debug THEN
922 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
923 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
924 End if;
925 raise;
926 end;
927
928 /* procedure creates the Retro elements and the required element links for the different retro types.
929 New elements will be created with the following name: 'Name of the old Retro Element' + <Retro Type>.
930 Reporting name of the element will be same as that of old Retro Element.
931 Following parameters are passed to the procedure:
932 a) Business Group ID
933 b) Element Type ID - Element Type ID of the element for which the Retro element is created
934 c) Retro Type - This parameter can have three values: "GT12", "LT12 Prev", and "LT12 Curr".
935 d) Retro Element Type ID: Out parameter to hold the element type id of the element getting created.
936
937 Bug#5583165 moved some code to element_exist_check() to check GT12,LT12 element already exist.
938 */
939
940 PROCEDURE create_element(
941 p_business_group_id IN NUMBER,
942 p_retro_element_id IN NUMBER,
943 p_retro_type IN VARCHAR2,
944 p_element_type_id OUT NOCOPY NUMBER
945 )
946 IS
947
948 /* Bug 5749509 - Modified cusror for Time_definition_type.
949 'N' is not a valid value for Time Definition Type, set value as Null
950 */
951 /* Gets the details of Retro Element */
952 cursor c_get_retro_element_info
953 is
954 select
955 FORMULA_ID,
956 INPUT_CURRENCY_CODE,
957 OUTPUT_CURRENCY_CODE,
958 CLASSIFICATION_ID,
962 CLOSED_FOR_ENTRY_FLAG,
959 BENEFIT_CLASSIFICATION_ID,
960 ADDITIONAL_ENTRY_ALLOWED_FLAG,
961 ADJUSTMENT_ONLY_FLAG,
963 ELEMENT_NAME,
964 REPORTING_NAME,
965 DESCRIPTION,
966 INDIRECT_ONLY_FLAG,
967 MULTIPLE_ENTRIES_ALLOWED_FLAG,
968 MULTIPLY_VALUE_FLAG,
969 POST_TERMINATION_RULE,
970 PROCESS_IN_RUN_FLAG,
971 PROCESSING_PRIORITY,
972 PROCESSING_TYPE,
973 STANDARD_LINK_FLAG,
974 COMMENT_ID,
975 LEGISLATION_SUBGROUP,
976 QUALIFYING_AGE,
977 QUALIFYING_LENGTH_OF_SERVICE,
978 QUALIFYING_UNITS,
979 ELEMENT_INFORMATION_CATEGORY,
980 ELEMENT_INFORMATION1,
981 ELEMENT_INFORMATION2,
982 ELEMENT_INFORMATION3,
983 THIRD_PARTY_PAY_ONLY_FLAG,
984 ITERATIVE_FLAG,
985 ITERATIVE_FORMULA_ID,
986 ITERATIVE_PRIORITY,
987 CREATOR_TYPE,
988 RETRO_SUMM_ELE_ID,
989 GROSSUP_FLAG,
990 PROCESS_MODE,
991 ADVANCE_INDICATOR,
992 ADVANCE_PAYABLE,
993 ADVANCE_DEDUCTION,
994 PROCESS_ADVANCE_ENTRY,
995 PRORATION_GROUP_ID,
996 PRORATION_FORMULA_ID,
997 RECALC_EVENT_GROUP_ID,
998 ONCE_EACH_PERIOD_FLAG,
999 decode(TIME_DEFINITION_TYPE,'N',NULL,TIME_DEFINITION_TYPE) TIME_DEFINITION_TYPE,
1000 TIME_DEFINITION_ID
1001 from pay_element_types_f
1002 where element_type_id = p_retro_element_id
1003 and business_group_id = p_business_group_id
1004 ORDER BY effective_start_date desc;
1005
1006 rec_element_types c_get_retro_element_info%ROWTYPE;
1007 l_effective_date DATE;
1008 l_effective_start_date DATE;
1009 l_effective_end_date DATE;
1010 l_ovn NUMBER ;
1011 l_comment_id NUMBER;
1012 l_warning BOOLEAN;
1013 l_element_type_id NUMBER;
1014 l_element_link_id NUMBER;
1015 l_eei_info_id Number;
1016 l_ovn_eei Number;
1017
1018 /* Checks whether retro element (LT12,GT12...) already exist before creating the element*/
1019 CURSOR c_element_exists
1020 IS
1021 SELECT pet.element_type_id
1022 FROM pay_element_types_f pet
1023 WHERE pet.element_name = rec_element_types.ELEMENT_NAME || ' ' || p_retro_type
1024 AND pet.business_group_id = p_business_group_id;
1025
1026
1027 /* Gets the details of the element link of retro element */
1028 /* 5731490 Fetches all the links associated with retro elements*/
1029 CURSOR c_get_element_links(c_element_type_id pay_element_types_f.element_type_id%type, p_effective_date date)
1030 IS
1031 SELECT pel.PAYROLL_ID,
1032 pel.JOB_ID,
1033 pel.POSITION_ID,
1034 pel.PEOPLE_GROUP_ID,
1035 pel.COST_ALLOCATION_KEYFLEX_ID,
1036 pel.ORGANIZATION_ID,
1037 pel.ELEMENT_TYPE_ID,
1038 pel.LOCATION_ID,
1039 pel.GRADE_ID,
1040 pel.BALANCING_KEYFLEX_ID,
1041 pel.BUSINESS_GROUP_ID,
1042 pel.ELEMENT_SET_ID,
1043 pel.PAY_BASIS_ID,
1044 pel.COSTABLE_TYPE,
1045 pel.LINK_TO_ALL_PAYROLLS_FLAG,
1046 pel.MULTIPLY_VALUE_FLAG,
1047 pel.STANDARD_LINK_FLAG,
1048 pel.TRANSFER_TO_GL_FLAG,
1049 pel.COMMENT_ID,
1050 pel.EMPLOYMENT_CATEGORY,
1051 pel.QUALIFYING_AGE,
1052 pel.QUALIFYING_LENGTH_OF_SERVICE,
1053 pel.QUALIFYING_UNITS,
1054 greatest(pel.EFFECTIVE_START_DATE, to_date('2005/07/01','YYYY/MM/DD')) EFFECTIVE_START_DATE /* 5731490 */
1055 ,pel.EFFECTIVE_END_DATE
1056 from pay_element_links_f pel
1057 where pel.element_type_id = c_element_type_id
1058 and pel.business_group_id = p_business_group_id
1059 and pel.effective_start_date = (
1060 select max(pel.effective_start_date)
1061 from pay_element_links_f pel1
1062 where pel.element_link_id=pel1.element_link_id
1063 ) /* 5731490 */
1064 and ( p_effective_date between pel.effective_start_date and pel.effective_end_date
1065 or pel.effective_start_date > p_effective_date) /* 5731490 */
1066 order by pel.effective_start_date asc;
1067
1068 rec_element_links c_get_element_links%ROWTYPE;
1069 lv_procedure_name VARCHAR2(50);
1070 l_upgraded_element char(1);
1071
1072 /* 5731490 */
1073 l_effective_start_date_li date;
1074 l_effective_end_date_li date;
1075 l_entries_warning_li boolean;
1076 begin
1077 g_debug := hr_utility.debug_enabled;
1078 l_upgraded_element :='N'; /* Flag to check whether element was created by Upgrade process */
1079 l_effective_date := to_date('2005/07/01','YYYY/MM/DD');
1080
1081 lv_procedure_name := 'create_element';
1082
1083 IF g_debug THEN
1084 hr_utility.trace('Entering ' || lv_procedure_name);
1085 End if;
1086
1087 /* Gets the details of Retro Element */
1088 open c_get_retro_element_info;
1089 fetch c_get_retro_element_info into rec_element_types;
1090 close c_get_retro_element_info;
1091
1092 IF g_debug THEN
1093 hr_utility.trace('Retro Element Name: ' || rec_element_types.element_name);
1094 End if;
1095 /* Checks whether element already exist before creating the element <Retro Element> + <Retro Type> */
1096 OPEN c_element_exists;
1097 FETCH c_element_exists INTO l_element_type_id;
1098 IF c_element_exists%NOTFOUND THEN
1099
1100 IF g_debug THEN
1101 hr_utility.trace('Creating New Retro Element: ' || rec_element_types.element_name || ' ' || p_retro_type );
1102 End if;
1103 /* Creates element <Retro Element> + <Retro Type> */
1104 /* Bug 5749509 - ONCE_EACH_PERIOD_FLAG - Specify default of 'N' if existing value is Null
1105 - TIME_DEFINITION_TYPE,TIME_DEFINITION_TYPE added in API call
1106 - If TIME_DEFINITION_TYPE is 'N', set it as Null
1107 */
1111 ,p_classification_id => rec_element_types.CLASSIFICATION_ID
1108
1109 PAY_ELEMENT_TYPES_API.CREATE_ELEMENT_TYPE
1110 (p_effective_date => l_effective_date
1112 ,p_element_name => rec_element_types.ELEMENT_NAME || ' ' || p_retro_type
1113 ,p_input_currency_code => 'AUD'
1114 ,p_output_currency_code => 'AUD'
1115 ,p_multiple_entries_allowed_fla => rec_element_types.MULTIPLE_ENTRIES_ALLOWED_FLAG
1116 ,p_processing_type => rec_element_types.PROCESSING_TYPE
1117 ,p_business_group_id => p_business_group_id
1118 ,p_formula_id => rec_element_types.FORMULA_ID
1119 ,p_benefit_classification_id => rec_element_types.BENEFIT_CLASSIFICATION_ID
1120 ,p_additional_entry_allowed_fla => rec_element_types.ADDITIONAL_ENTRY_ALLOWED_FLAG
1121 ,p_adjustment_only_flag => rec_element_types.ADJUSTMENT_ONLY_FLAG
1122 ,p_closed_for_entry_flag => rec_element_types.CLOSED_FOR_ENTRY_FLAG
1123 ,p_reporting_name => nvl(rec_element_types.REPORTING_NAME, rec_element_types.ELEMENT_NAME)
1124 ,p_description => nvl(rec_element_types.DESCRIPTION, rec_element_types.ELEMENT_NAME) || ' for ' || p_retro_type
1125 ,p_indirect_only_flag => rec_element_types.INDIRECT_ONLY_FLAG
1126 ,p_multiply_value_flag => rec_element_types.MULTIPLY_VALUE_FLAG
1127 ,p_post_termination_rule => rec_element_types.POST_TERMINATION_RULE
1128 ,p_process_in_run_flag => rec_element_types.PROCESS_IN_RUN_FLAG
1129 ,p_processing_priority => rec_element_types.PROCESSING_PRIORITY
1130 ,p_standard_link_flag => rec_element_types.STANDARD_LINK_FLAG
1131 ,p_third_party_pay_only_flag => rec_element_types.THIRD_PARTY_PAY_ONLY_FLAG
1132 ,p_iterative_flag => rec_element_types.ITERATIVE_FLAG
1133 ,p_iterative_formula_id => rec_element_types.ITERATIVE_FORMULA_ID
1134 ,p_iterative_priority => rec_element_types.ITERATIVE_PRIORITY
1135 ,p_creator_type => rec_element_types.CREATOR_TYPE
1136 ,p_retro_summ_ele_id => null
1137 ,p_grossup_flag => rec_element_types.GROSSUP_FLAG
1138 ,p_process_mode => rec_element_types.PROCESS_MODE
1139 ,p_advance_indicator => rec_element_types.ADVANCE_INDICATOR
1140 ,p_advance_payable => rec_element_types.ADVANCE_PAYABLE
1141 ,p_advance_deduction => rec_element_types.ADVANCE_DEDUCTION
1142 ,p_process_advance_entry => rec_element_types.PROCESS_ADVANCE_ENTRY
1143 ,p_proration_group_id => rec_element_types.PRORATION_GROUP_ID
1144 ,p_proration_formula_id => rec_element_types.PRORATION_FORMULA_ID
1145 ,p_recalc_event_group_id => rec_element_types.RECALC_EVENT_GROUP_ID
1146 ,p_legislation_subgroup => null
1147 ,p_qualifying_age => rec_element_types.QUALIFYING_AGE
1148 ,p_qualifying_length_of_service => rec_element_types.QUALIFYING_LENGTH_OF_SERVICE
1149 ,p_qualifying_units => rec_element_types.QUALIFYING_UNITS
1150 ,p_element_information_category => rec_element_types.ELEMENT_INFORMATION_CATEGORY
1151 ,p_element_information1 => rec_element_types.ELEMENT_INFORMATION1
1152 ,p_element_information2 => rec_element_types.ELEMENT_INFORMATION2
1153 ,p_element_information3 => rec_element_types.ELEMENT_INFORMATION3
1154 ,p_once_each_period_flag => NVL(rec_element_types.ONCE_EACH_PERIOD_FLAG,'N')
1155 ,p_time_definition_type => rec_element_types.TIME_DEFINITION_TYPE
1156 ,p_time_definition_id => rec_element_types.TIME_DEFINITION_ID
1157 ,p_element_type_id => p_element_type_id
1158 ,p_effective_start_date => l_effective_start_date
1159 ,p_effective_end_date => l_effective_end_date
1160 ,p_object_version_number => l_ovn
1161 ,p_comment_id => l_comment_id
1162 ,p_processing_priority_warning => l_warning);
1163
1164 /*An entry is created in the table pay_element_type_extra_info table. This
1165 table is used to identify whether a retro element is already created by the
1166 upgrade process. If so, for the given element no corresponding reto elements
1167 are created*/
1168
1169 pay_element_extra_info_api.create_element_extra_info /*Bug# 5461633 */
1170 (p_element_type_id => p_element_type_id
1171 ,p_information_type => 'AU_RETRO_UPGRADE_INFO'
1172 ,p_eei_information_category => 'AU_RETRO_UPGRADE_INFO'
1173 ,p_element_type_extra_info_id => l_eei_info_id
1174 ,p_object_version_number => l_ovn_eei);
1175
1176 IF g_debug THEN
1177 hr_utility.trace('Created New Retro Element: ' || rec_element_types.element_name || ' ' || p_retro_type );
1178 End if;
1179 /* Gets the details of element link of retro element, if link exist
1180 create the similar element link for newly created element */
1181
1182 OPEN c_get_element_links(p_retro_element_id,l_effective_date);
1183 Loop /* 5731490 */
1184 FETCH c_get_element_links INTO rec_element_links;
1185
1186 IF c_get_element_links%NOTFOUND THEN
1187 exit;
1188 ELSE
1189 IF g_debug THEN
1190 hr_utility.trace('Creating Element links for New Retro Element: ' || rec_element_types.element_name || ' ' || p_retro_type );
1191 End if;
1192
1193 pay_element_link_api.create_element_link
1194 (p_effective_date => rec_element_links.effective_start_date /* 5731490 */
1195 ,p_element_type_id => p_element_type_id
1199 ,p_job_id => rec_element_links.JOB_ID
1196 ,p_business_group_id => p_business_group_id
1197 ,p_costable_type => rec_element_links.COSTABLE_TYPE
1198 ,p_payroll_id => rec_element_links.PAYROLL_ID
1200 ,p_position_id => rec_element_links.POSITION_ID
1201 ,p_people_group_id => rec_element_links.PEOPLE_GROUP_ID
1202 ,p_organization_id => rec_element_links.ORGANIZATION_ID
1203 ,p_location_id => rec_element_links.LOCATION_ID
1204 ,p_grade_id => rec_element_links.GRADE_ID
1205 ,p_element_set_id => rec_element_links.ELEMENT_SET_ID
1206 ,p_pay_basis_id => rec_element_links.PAY_BASIS_ID
1207 ,p_link_to_all_payrolls_flag => rec_element_links.LINK_TO_ALL_PAYROLLS_FLAG
1208 ,p_standard_link_flag => rec_element_links.STANDARD_LINK_FLAG
1209 ,p_transfer_to_gl_flag => rec_element_links.TRANSFER_TO_GL_FLAG
1210 ,p_employment_category => rec_element_links.EMPLOYMENT_CATEGORY
1211 ,p_qualifying_age => rec_element_links.QUALIFYING_AGE
1212 ,p_qualifying_length_of_service => rec_element_links.QUALIFYING_LENGTH_OF_SERVICE
1213 ,p_qualifying_units => rec_element_links.QUALIFYING_UNITS
1214 ,p_cost_concat_segments => null
1215 ,p_balance_concat_segments => null
1216 ,p_element_link_id => l_element_link_id
1217 ,p_comment_id => l_comment_id
1218 ,p_object_version_number => l_ovn
1219 ,p_effective_start_date => l_effective_start_date
1220 ,p_effective_end_date => l_effective_end_date
1221 );
1222 IF g_debug THEN
1223 hr_utility.trace('Created Element links for New Retro Element: ' || rec_element_types.element_name || ' ' || p_retro_type );
1224 End if;
1225 /* 5731490 End dates link*/
1226 if to_char(rec_element_links.effective_end_date,'YYYY/MM/DD') <> '4712/12/31' then
1227 pay_element_link_api.delete_element_link
1228 (
1229 p_effective_date => rec_element_links.effective_end_date
1230 ,p_element_link_id => l_element_link_id
1231 ,p_datetrack_delete_mode => 'DELETE'
1232 ,p_object_version_number => l_ovn
1233 ,p_effective_start_date => l_effective_start_date_li
1234 ,p_effective_end_date => l_effective_end_date_li
1235 ,p_entries_warning => l_entries_warning_li
1236 );
1237 end if;
1238
1239 END IF;
1240 End Loop;
1241 CLOSE c_get_element_links;
1242
1243 ELSE
1244 p_element_type_id := l_element_type_id;
1245 END IF;
1246
1247 CLOSE c_element_exists;
1248
1249 IF g_debug THEN
1250 hr_utility.trace('Leaving ' || lv_procedure_name);
1251 End if;
1252 exception
1253 when others then
1254 IF g_debug THEN
1255 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
1256 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
1257 End if;
1258 raise;
1259 end;
1260 /*
1261 Procedure attaches the Retro Componenets with the retro paid element
1262 Following parameter are passed:
1263 a) Business Group id
1264 b) Legislation code
1265 c) Retro Componenet id - Retro Componenet id of seeded Retro Component
1266 d) Creater id Element Type Id of retro element
1267 e) Retro Component Usage id Out prarmeter
1268 */
1269 PROCEDURE insert_retro_comp_usages
1270 (p_business_group_id in number,
1271 p_legislation_code in varchar2,
1272 p_retro_component_id in number,
1273 p_creator_id in number,
1274 p_retro_comp_usage_id out nocopy number)
1275 IS
1276
1277 ln_retro_component_usage_id NUMBER;
1278 lv_procedure_name VARCHAR2(100);
1279
1280
1281 CURSOR c_retro_comp_exists
1282 is
1283 SELECT retro_component_usage_id
1284 FROM pay_retro_component_usages
1285 WHERE creator_id = p_creator_id
1286 AND p_business_group_id = business_group_id
1287 AND retro_component_id = p_retro_component_id;
1288
1289
1290 BEGIN
1291 g_debug := hr_utility.debug_enabled;
1292 lv_procedure_name := '.insert_retro_comp_usages';
1293
1294 IF g_debug THEN
1295 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
1296 End if;
1297 /* Checks whether retro compnoned already exist */
1298 OPEN c_retro_comp_exists;
1299 FETCH c_retro_comp_exists INTO ln_retro_component_usage_id;
1300 IF c_retro_comp_exists%NOTFOUND THEN
1301
1302 select pay_retro_component_usages_s.nextval
1303 into ln_retro_component_usage_id
1304 from dual;
1305
1306 insert into pay_retro_component_usages
1307 (retro_component_usage_id, retro_component_id, creator_id, creator_type,
1308 default_component, reprocess_type, business_group_id, legislation_code,
1309 creation_date, created_by, last_update_date, last_updated_by,
1310 last_update_login, object_version_number)
1311 values
1312 (ln_retro_component_usage_id, p_retro_component_id, p_creator_id,
1313 'ET', 'Y', 'R', p_business_group_id, p_legislation_code,
1314 sysdate, 2, sysdate, 2, 2, 1);
1315
1316 p_retro_comp_usage_id := ln_retro_component_usage_id;
1317
1318 IF g_debug THEN
1322 ELSE
1319 hr_utility.trace('p_retro_comp_usage_id= ' || p_retro_comp_usage_id);
1320 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
1321 End if;
1323 p_retro_comp_usage_id := ln_retro_component_usage_id;
1324 END IF;
1325
1326 exception
1327 when others then
1328 IF g_debug THEN
1329 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
1330 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
1331 End if;
1332
1333 raise;
1334 END insert_retro_comp_usages;
1335
1336 /*
1337 Procedure attaches the Time Span with the retro paid element
1338 Following parameter are passed:
1339 a) Business Group id
1340 b) Retro Element Type Id - Element Type id of retro element
1341 c) Legislation Code
1342 d) Time Span Id
1343 e) Retro Component Usage id of Retro component attached with the retro element
1344 */
1345 PROCEDURE insert_element_span_usages
1346 (p_business_group_id in number,
1347 p_retro_element_type_id in number,
1348 p_legislation_code in varchar2,
1349 p_time_span_id in number,
1350 p_retro_comp_usage_id in number)
1351 IS
1352
1353 lv_procedure_name VARCHAR2(100);
1354
1355 BEGIN
1356 g_debug := hr_utility.debug_enabled;
1357 lv_procedure_name := '.insert_element_span_usages';
1358
1359 IF g_debug THEN
1360 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
1361 hr_utility.trace('p_business_group_id ='|| p_business_group_id);
1362 hr_utility.trace('p_time_span_id ='|| p_time_span_id);
1363 hr_utility.trace('p_retro_comp_usage_id ='|| p_retro_comp_usage_id);
1364 hr_utility.trace('p_retro_element_type_id ='|| p_retro_element_type_id);
1365 End if;
1366
1367 insert into pay_element_span_usages
1368 (element_span_usage_id, business_group_id, time_span_id,
1369 retro_component_usage_id, retro_element_type_id,
1370 creation_date, created_by, last_update_date, last_updated_by,
1371 last_update_login, object_version_number)
1372 values
1373 (pay_element_span_usages_s.nextval, p_business_group_id, p_time_span_id,
1374 p_retro_comp_usage_id, p_retro_element_type_id,
1375 sysdate, 2, sysdate, 2, 2, 1);
1376
1377 IF g_debug THEN
1378 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
1379 End if;
1380
1381 exception
1382 when others then
1383 IF g_debug THEN
1384 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
1385 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
1386 End if;
1387 raise;
1388 END insert_element_span_usages;
1389
1390 -- Bug#5583165
1391 --This procedure checks whether any GT12, LT12 element exist for an element and if GT12
1392 -- or LT12 exist whether these elements were created by upgrade process.
1393 -- Procedure sets p_user_element_exist to 'Y' if GT12,LT12 elements were created by user
1394 -- and sets p_upgrade_element_exis to 'Y' if GT12,LT12 elements were created by upgrade process
1395
1396 Procedure element_exist_check( p_element_type_id in pay_element_types_f.element_type_id%type
1397 ,p_business_group_id in number
1398 ,p_upgrade_element_exist out NOCOPY VARCHAR2
1399 ,p_user_element_exist out NOCOPY VARCHAR2)
1400 is
1401 CURSOR c_element_exists IS
1402 SELECT pet2.element_type_id,pet1.element_name,pet2.element_name
1403 FROM pay_element_types_f pet1,
1404 pay_element_types_f pet2
1405 WHERE pet2.element_name in (pet1.element_name || ' '||'GT12' ,
1406 pet1.element_name || ' '||'LT12 Prev',
1407 pet1.element_name || ' '||'LT12 Curr' )
1408 AND pet1.business_group_id = p_business_group_id
1409 and pet1.business_group_id= pet2.business_group_id
1410 and pet1.element_type_id=p_element_type_id;
1411
1412
1413 CURSOR upgraded_element_check(cp_element_type_id pay_element_types_f.element_type_id%type) IS
1414 select 'Y'
1415 from pay_element_type_extra_info
1416 where element_type_id= cp_element_type_id
1417 and information_type='AU_RETRO_UPGRADE_INFO' ;
1418
1419 l_element_type_id pay_element_types_f.element_type_id%type;
1420 l_upg_element_name pay_element_types_f.element_name%type;
1421 l_retro_element_name pay_element_types_f.element_name%type;
1422 l_upgraded_element char(1);
1423 l_user_element_exist char(1);
1424 l_upgrade_element_exist char(1);
1425 lv_procedure_name varchar2(30);
1426 Begin
1427 g_debug := hr_utility.debug_enabled;
1428
1429 lv_procedure_name := '.element_exist_check';
1430 l_upgraded_element:='N';
1431 l_user_element_exist :='N';
1432 l_upgrade_element_exist := 'N';
1433
1434 -- Checking whether GT12,LT12 elements exist for the retro element
1435 open c_element_exists;
1436 loop
1437 FETCH c_element_exists into l_element_type_id,l_upg_element_name,l_retro_element_name;
1438 IF c_element_exists%NOTFOUND THEN
1439 exit;
1440 END IF;
1441 -- Checking whether GT12,LT12 elements were created by upgrade process
1442 OPEN upgraded_element_check(l_element_type_id);
1443 FETCH upgraded_element_check INTO l_upgraded_element;
1447 fnd_file.put_line( FND_FILE.LOG,'WARNING: User Defined Element ' ||l_retro_element_name || ' already exist, cannot upgrade '|| l_upg_element_name || ' (Element Type ID: ' || p_element_type_id || ').'); /* 5461629 */
1444 CLOSE upgraded_element_check;
1445 -- GT12,LT12 elements were not created by upgrade process
1446 if l_upgraded_element <> 'Y' then
1448 l_user_element_exist :='Y';
1449 l_upgrade_element_exist := 'N';
1450 exit;
1451 else
1452 l_user_element_exist :='N';
1453 l_upgrade_element_exist := 'Y';
1454 end if;
1455
1456 end loop;
1457 close c_element_exists;
1458 p_upgrade_element_exist := l_upgrade_element_exist;
1459 p_user_element_exist := l_user_element_exist;
1460
1461 exception
1462 when others then
1463 IF g_debug THEN
1464 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
1465 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
1466 End if;
1467 raise;
1468 end element_exist_check;
1469
1470
1471 /****************************************************************************
1472 ** Name : qualify_element
1473 **
1474 ** Description: This is the qualifying procedure which determines whether
1475 ** the element passed in as a parameter needs to be migrated.
1476 ** The conditions that are checked here are
1477 ** 1. Element is part of a Retro Set used for Retro
1478 ** 2. Element is of type "Pre Tax Deductions" and "Earnings Standard"
1479 **
1480 ****************************************************************************/
1481 PROCEDURE qualify_element(p_object_id in varchar2
1482 ,p_qualified out nocopy varchar2)
1483 IS
1484
1485 /* This cursor fetches the element information for element if element is of classification
1486 'Earnings' or 'Pre Tax Deduction' */
1487 /* Bug 5731490 - If the element has a retro summary element defined, the element
1488 must be upgraded
1489 Bug 5749509 - Added NOT Exists clause to prevent elements created by UPGRADE to get
1490 upgraded again if process is re-run.
1491 - Added NOT Exists clause to prevent any User defined Retro elements to
1492 get upgraded.
1493 */
1494
1495 cursor c_element_class(cp_element_type_id in number) is
1496 select distinct
1497 pet.classification_id,
1498 pet.element_name,
1499 pet.legislation_code,
1500 pet.business_group_id,
1501 pec.classification_name,
1502 pet.retro_summ_ele_id, /* Bug 5731490 */
1503 decode(instr(pec.classification_name, 'Earnings'), 0, null,pec2.classification_name) ||
1504 decode(instr(pec.classification_name, 'Deductions'), 0, null, pec.classification_name ) label
1505 from pay_element_types_f pet
1506 ,pay_element_classifications pec
1507 ,pay_element_classifications pec2
1508 ,pay_sub_classification_rules_f pscr
1509 where pet.element_type_id = cp_element_type_id
1510 AND pet.classification_id = pec.classification_id
1511 and pec.legislation_code = 'AU'
1512 and (instr(pec.classification_name, 'Earnings') > 0
1513 or instr(pec.classification_name, 'Pre Tax Deductions') > 0
1514 OR pet.retro_summ_ele_id IS NOT NULL ) /* Bug 5731490 */
1515 and pet.element_type_id = pscr.element_type_id (+)
1516 and pscr.classification_id = pec2.classification_id(+)
1517 and pec2.legislation_code (+)= 'AU'
1518 AND NOT EXISTS
1519 (SELECT '1'
1520 FROM pay_element_type_extra_info etei
1521 WHERE etei.element_type_id = pet.element_type_id
1522 AND etei.information_type = 'AU_RETRO_UPGRADE_INFO')
1523 AND NOT EXISTS
1524 ( SELECT '1'
1525 FROM pay_balance_feeds_f pbf,
1526 pay_balance_types pbt,
1527 pay_input_values_f pivf
1528 WHERE pbt.balance_type_id = pbf.balance_type_id
1529 AND pbt.balance_name in ('Retro LT 12 Mths Curr Yr Amount',
1530 'Retro LT 12 Mths Prev Yr Amount',
1531 'Lump Sum E Payments')
1532 AND pbf.input_value_id = pivf.input_value_id
1533 AND pivf.name = 'Pay Value'
1534 AND pivf.element_type_id = pet.element_type_id
1535 ) ;
1536
1537 cursor c_legislation_code(cp_business_group_id in number) is
1538 select legislation_code
1539 from per_business_groups
1540 where business_group_id = cp_business_group_id;
1541
1542 /* Gets all the element sets in which the element is included.*/
1543
1544 cursor c_element_set(cp_element_type_id in number
1545 ,cp_classification_id in number
1546 ,cp_legislation_code in varchar2) is
1547 select petr.element_set_id
1548 from pay_element_type_rules petr
1549 where petr.element_type_id = cp_element_type_id
1550 and petr.include_or_exclude = 'I'
1551 union all
1552 select pes.element_set_id
1553 from pay_ele_classification_rules pecr,
1554 pay_element_types_f pet,
1555 pay_element_sets pes
1556 where pet.classification_id = pecr.classification_id
1557 and pes.element_set_id = pecr.element_set_id
1558 and (pes.business_group_id = pet.business_group_id
1562 minus
1559 or pet.legislation_code = cp_legislation_code)
1560 and pet.element_type_id = cp_element_type_id
1561 and pecr.classification_id = cp_classification_id
1563 select petr.element_set_id
1564 from pay_element_type_rules petr
1565 where petr.element_type_id = cp_element_type_id
1566 and petr.include_or_exclude = 'E'
1567 ;
1568
1569 /* Chechk whether Element set is used for retropayment */
1570
1571 cursor c_element_check(cp_element_set_id in number) is
1572 select 1
1573 from pay_payroll_actions ppa
1574 where ppa.action_type = 'L'
1575 and ppa.element_set_id = cp_element_set_id;
1576
1577 cursor c_retro_rule_check(cp_rule_type in varchar2
1578 ,cp_legislation_code in Varchar2) is
1579 select 'Y'
1580 from pay_legislation_rules
1581 where legislation_code = cp_legislation_code
1582 and rule_type = cp_rule_type;
1583
1584 ln_classification_id NUMBER;
1585 ln_business_group_id NUMBER;
1586 ln_element_set_id NUMBER;
1587 ln_element_used NUMBER;
1588 lv_qualified VARCHAR2(1);
1589 lv_element_name VARCHAR2(100);
1590 lv_classification_name VARCHAR2(100);
1591 lv_label VARCHAR2(100);
1592 lv_procedure_name VARCHAR2(100);
1593 lv_legislation_code VARCHAR2(150);
1594 ln_exists VARCHAR2(1);
1595
1596 TYPE character_data_table IS TABLE OF VARCHAR2(280)
1597 INDEX BY BINARY_INTEGER;
1598
1599 ltt_rule_type character_data_table;
1600 ltt_rule_mode character_data_table;
1601
1602 ln_retro_summ_ele_id NUMBER; /* Bug 5731490 */
1603 ln_element_set_exist boolean; /* Bug 5731490 */
1604
1605 BEGIN
1606 g_debug := hr_utility.debug_enabled;
1607
1608 lv_procedure_name := '.qualify_element';
1609
1610 IF g_debug THEN
1611 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
1612 End if;
1613
1614 lv_qualified := 'N';
1615 lv_legislation_code := null;
1616 ln_business_group_id := null;
1617 ln_classification_id := null;
1618 lv_element_name := null;
1619 ln_element_set_exist := FALSE; /* 5731490 */
1620 /* Fetches the element information for element if element is of classification
1621 'Earnings' or 'Pre Tax Deduction' */
1622 open c_element_class(p_object_id);
1623 fetch c_element_class into ln_classification_id,
1624 lv_element_name,
1625 lv_legislation_code,
1626 ln_business_group_id,
1627 lv_classification_name,
1628 ln_retro_summ_ele_id, /* Bug 5731490 */
1629 lv_label;
1630 close c_element_class;
1631
1632 IF ln_classification_id IS NOT NULL THEN
1633 IF g_debug THEN
1634 hr_utility.trace('ln_classification_id: ' || ln_classification_id);
1635 hr_utility.trace('lv_element_name: ' || lv_element_name);
1636 hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1637 hr_utility.trace('lv_classification_name: ' || lv_classification_name);
1638 hr_utility.trace('lv_label: ' || lv_label);
1639 hr_utility.trace('lv_retro_summ_ele_id: '||ln_retro_summ_ele_id);
1640 End if;
1641
1642 if lv_legislation_code is null and
1643 ln_business_group_id is not null then
1644 open c_legislation_code(ln_business_group_id);
1645 FETCH c_legislation_code into lv_legislation_code;
1646 close c_legislation_code;
1647 end if;
1648
1649 ltt_rule_type(1) := 'RETRO_DELETE';
1650 ltt_rule_mode(1) := 'N';
1651 ltt_rule_type(2) := 'ADVANCED_RETRO';
1652 ltt_rule_mode(2) := 'Y';
1653 /* Checks whether Legislation rules are enabled */
1654 FOR i in 1 ..2 LOOP
1655 OPEN c_retro_rule_check(ltt_rule_type(i),lv_legislation_code) ;
1656 FETCH c_retro_rule_check into ln_exists;
1657 IF c_retro_rule_check%NOTFOUND THEN
1658 INSERT INTO pay_legislation_rules
1659 (legislation_code, rule_type, rule_mode) VALUES
1660 (lv_legislation_code, ltt_rule_type(i), ltt_rule_mode(i));
1661 END IF;
1662 CLOSE c_retro_rule_check;
1663 END LOOP;
1664 /* Gets all the element sets in which the element is included.*/
1665 open c_element_set(p_object_id
1666 ,ln_classification_id
1667 ,lv_legislation_code);
1668 loop
1669 fetch c_element_set into ln_element_set_id;
1670 if c_element_set%notfound then
1671 exit;
1672 end if;
1673 ln_element_set_exist := TRUE; /* 5731490 */
1674 IF g_debug THEN
1675 hr_utility.trace('Element Set ID ' || ln_element_set_id);
1676 End if;
1677 /* Chechk whether Element set is used for retropayment */
1678 /* Bug 5731490 - Check Added for summary element */
1679 open c_element_check(ln_element_set_id);
1680 fetch c_element_check into ln_element_used;
1681 if c_element_check%found AND ((lv_classification_name = 'Earnings' AND lv_label = 'Standard')
1682 OR lv_classification_name = 'Pre Tax Deductions'
1686
1683 OR ln_retro_summ_ele_id IS NOT NULL) then
1684
1685 lv_qualified := 'Y';
1687 IF g_debug THEN
1688 hr_utility.trace('UPGRADE Element ' || lv_element_name ||
1689 '(' || p_object_id || ')');
1690 End if;
1691 exit;
1692 else
1693 lv_qualified := 'N';
1694 IF g_debug THEN
1695 hr_utility.trace('Element ' || lv_element_name ||
1696 '(' || p_object_id || ') does not need to be upgraded');
1697 End if;
1698
1699 end if;
1700 close c_element_check;
1701 end loop;
1702 close c_element_set;
1703 END IF;
1704
1705 /* Bug 5749509 - Moved statement outside IF Block */
1706 p_qualified := lv_qualified;
1707
1708 /* Bug 5731490 - Moved statement outside IF Block */
1709 if ((lv_classification_name = 'Earnings' AND lv_label = 'Standard')
1710 OR lv_classification_name = 'Pre Tax Deductions'
1711 OR ln_retro_summ_ele_id IS NOT NULL) and lv_qualified ='N' and ln_element_set_exist = TRUE then
1712 fnd_file.put_line(FND_FILE.LOG,'MESSAGE: Element ' || lv_element_name || ' (Element Type ID: ' || p_object_id|| ')'|| ' does not require upgrade as not included in a Retro Element set.');
1713 elsif NOT ((lv_classification_name = 'Earnings' AND lv_label = 'Standard')
1714 OR lv_classification_name = 'Pre Tax Deductions'
1715 OR ln_retro_summ_ele_id IS NOT NULL) and ln_element_set_exist = TRUE then
1716 fnd_file.put_line(FND_FILE.LOG,'MESSAGE: Element ' || lv_element_name || ' (Element Type ID: ' || p_object_id|| ')'|| ' with classification ' ||lv_classification_name||' '||lv_label||' does not require upgrade.');
1717 end if;
1718
1719 IF g_debug THEN
1720 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
1721 End if;
1722
1723 exception
1724 when others then
1725 IF g_debug THEN
1726 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
1727 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
1728 End if;
1729
1730 raise;
1731 END qualify_element;
1732
1733 /* All those elements, which are passed by the qualify_element procedure comes to the upgrade procedure */
1734
1735 PROCEDURE upgrade_element(p_element_type_id in number)
1736 IS
1737
1738 /* Gets the details of element */
1739 /* Bug 5731490 - Changed Cursor to include elements with Retro summary element set */
1740 cursor c_element_dtl(cp_element_type_id in number) is
1741 /* select pet.business_group_id, pet.legislation_code, pet.classification_id,
1742 nvl(pet.retro_summ_ele_id, pet.element_type_id),
1743 pet.element_name, classification_name
1744 from pay_element_types_f pet,
1745 pay_element_classifications pec
1746 where pet.element_type_id = cp_element_type_id
1747 AND pet.classification_id = pec.classification_id
1748 AND pec.legislation_code = 'AU'
1749 order by pet.effective_start_date desc;*/
1750 SELECT pet.business_group_id, pet.legislation_code, pet.classification_id,
1751 nvl(pet.retro_summ_ele_id, pet.element_type_id),
1752 pet.element_name, pec.classification_name
1753 ,pet.retro_summ_ele_id
1754 ,decode(instr(pec.classification_name, 'Earnings'), 0, null,pec2.classification_name) label
1755 FROM pay_element_types_f pet,
1756 pay_element_classifications pec
1757 ,pay_element_classifications pec2
1758 ,pay_sub_classification_rules_f pscr
1759 WHERE pet.element_type_id = cp_element_type_id
1760 AND pet.classification_id = pec.classification_id
1761 AND pec.legislation_code = 'AU'
1762 AND pet.element_type_id = pscr.element_type_id (+)
1763 AND pscr.classification_id = pec2.classification_id(+)
1764 AND pec2.legislation_code (+)= 'AU'
1765 ORDER BY pet.effective_start_date DESC;
1766
1767
1768 cursor c_legislation_code(cp_business_group_id in number) is
1769 select legislation_code
1770 from per_business_groups
1771 where business_group_id = cp_business_group_id;
1772
1773 cursor c_element_set(cp_element_type_id in number
1774 ,cp_classification_id in number
1775 ,cp_legislation_code in varchar2) is
1776 select petr.element_set_id
1777 from pay_element_type_rules petr
1778 where petr.element_type_id = cp_element_type_id
1779 and petr.include_or_exclude = 'I'
1780 union all
1781 select pes.element_set_id
1782 from pay_ele_classification_rules pecr,
1783 pay_element_types_f pet,
1784 pay_element_sets pes
1785 where pet.classification_id = pecr.classification_id
1786 and pes.element_set_id = pecr.element_set_id
1787 and (pes.business_group_id = pet.business_group_id
1788 or pet.legislation_code = cp_legislation_code)
1789 and pet.element_type_id = cp_element_type_id
1790 and pecr.classification_id = cp_classification_id
1791 minus
1792 select petr.element_set_id
1793 from pay_element_type_rules petr
1797 cursor c_get_business_group(cp_element_set_id in number
1794 where petr.element_type_id = cp_element_type_id
1795 and petr.include_or_exclude = 'E';
1796
1798 ,cp_legislation_code in varchar2) is
1799 select hoi.organization_id
1800 from hr_organization_information hoi,
1801 hr_organization_information hoi2
1802 where hoi.org_information_context = 'CLASS'
1803 and hoi.org_information1 = 'HR_BG'
1804 and hoi.organization_id = hoi2.organization_id
1805 and hoi2.org_information_context = 'Business Group Information'
1806 and hoi2.org_information9 = cp_legislation_code
1807 and exists (select 1 from pay_payroll_actions ppa
1808 where ppa.business_group_id = hoi.organization_id
1809 and ppa.action_type = 'L'
1810 and ppa.element_set_id = cp_element_set_id
1811 );
1812 /* cursor is used to get retro component info for AU legislation. */
1813 cursor c_retro_info(cp_legislation_code in varchar2) is
1814 select retro_component_id, pts.time_span_id, ptd.short_name, ptd2.short_name
1815 from pay_retro_components prc,
1816 pay_time_spans pts,
1817 pay_time_definitions ptd,
1818 pay_time_definitions ptd2
1819 where pts.creator_id = prc.retro_component_id
1820 and prc.legislation_code = 'AU'
1821 and ptd.legislation_code = 'AU'
1822 and ptd.time_definition_id = pts.start_time_def_id
1823 and ptd2.legislation_code = 'AU'
1824 and ptd2.time_definition_id = pts.end_time_def_id;
1825
1826 /* Checks whether retro component exist for the element */
1827 CURSOR c_get_retro_components
1828 IS
1829 select count(*)
1830 from pay_retro_component_usages prcu,
1831 pay_retro_components prc
1832 where prc.legislation_code = 'AU'
1833 and prc.retro_component_id = prcu.retro_component_id
1834 AND prcu.creator_id = p_element_type_id
1835 order by prcu.creator_id;
1836
1837 CURSOR c_get_balance_type_id(c_name pay_balance_types.balance_name%type)
1838 IS
1839 SELECT balance_type_id
1840 FROM pay_balance_types
1841 WHERE legislation_code = 'AU'
1842 AND balance_name = c_name;
1843
1844 /* Gets the name of the attached retro element */
1845 cursor c_retro_element_name(cp_element_type_id in number) is /* 5461629 */
1846 select pet2.element_name
1847 from pay_element_types_f pet1,
1848 pay_element_types_f pet2
1849 where pet1.element_type_id = cp_element_type_id
1850 AND nvl(pet1.retro_summ_ele_id, pet1.element_type_id) = pet2.element_type_id;
1851
1852
1853 ln_retro_comp_exists NUMBER;
1854 ln_ele_business_group_id NUMBER;
1855 ln_business_group_id NUMBER;
1856 ln_classification_id NUMBER;
1857 ln_legislation_code VARCHAR2(10);
1858 lv_legislation_code VARCHAR2(10);
1859 ln_element_set_id NUMBER;
1860 ln_retro_element_type_id NUMBER;
1861 lv_retro_element_name VARCHAR2(100);
1862 ln_retro_comp_usage_id NUMBER;
1863 retro_element_type_id NUMBER;
1864 ln_count NUMBER;
1865 lv_element_name VARCHAR2(100);
1866 lv_procedure_name VARCHAR2(100);
1867 ln_retro_component_id NUMBER;
1868 ln_time_span_id NUMBER;
1869 lv_start_time_name VARCHAR2(100);
1870 lv_end_time_name VARCHAR2(100);
1871 lv_classification_name VARCHAR2(100);
1872 ln_balance_type_id NUMBER;
1873 l_scale NUMBER;
1874 ln_event_group_id NUMBER;
1875 l_warning_flag NUMBER;
1876 l_retro_element_name VARCHAR2(100);
1877 l_upgraded_element_flag CHAR(1); /* Flag to check whether element was created by Upgrade process */
1878 TYPE numeric_data_table IS TABLE OF NUMBER
1879 INDEX BY BINARY_INTEGER;
1880
1881 ltt_business_group numeric_data_table;
1882 l_user_element_exist char(1);
1883 l_upgrade_element_exist char(1);
1884
1885 /* Bug 5731490 - Added variables */
1886 ln_retro_summ_ele_id pay_element_types_f.retro_summ_ele_id%TYPE;
1887 lv_class_label varchar2(100);
1888
1889 l_migrator_mode_status VARCHAR2(2);
1890
1891 /* Bug 6455303 - Added variable */
1892 l_retro_balance_name VARCHAR2(80);
1893
1894
1895 BEGIN
1896 g_debug := hr_utility.debug_enabled;
1897 lv_procedure_name := '.upgrade_element';
1898 l_warning_flag := -1;
1899
1900 /* Bug 5749509 - Set the data migrator Mode */
1901 l_migrator_mode_status := hr_general.g_data_migrator_mode;
1902 hr_general.g_data_migrator_mode := 'Y';
1903
1904 IF g_debug THEN
1905 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
1906 End if;
1907 /* Gets the details of element */
1908 open c_element_dtl(p_element_type_id);
1909 fetch c_element_dtl into ln_ele_business_group_id, ln_legislation_code,
1910 ln_classification_id, ln_retro_element_type_id,
1911 lv_element_name, lv_classification_name,ln_retro_summ_ele_id,lv_class_label; /* Bug 5731490 */
1912 close c_element_dtl;
1913
1914 IF g_debug THEN
1915 hr_utility.trace('p_element_type_id ='|| p_element_type_id);
1916 hr_utility.trace('lv_element_name ='|| lv_element_name);
1917 hr_utility.trace('ln_ele_business_group_id ='|| ln_ele_business_group_id);
1918 hr_utility.trace('ln_retro_element_type_id ='|| ln_retro_element_type_id);
1922 END IF;
1919 hr_utility.trace('lv_classification_name ='|| lv_classification_name);
1920 hr_utility.trace('l_retro_summ_ele_id ='|| ln_retro_summ_ele_id); /* Bug 5731490 */
1921 hr_utility.trace('lv_class_label ='|| lv_class_label); /* Bug 5731490 */
1923
1924 if ln_legislation_code is null and
1925 ln_ele_business_group_id is not null then
1926 open c_legislation_code(ln_ele_business_group_id);
1927 FETCH c_legislation_code into lv_legislation_code;
1928 close c_legislation_code;
1929 else
1930 lv_legislation_code := ln_legislation_code;
1931 end if;
1932
1933 IF g_debug THEN
1934 hr_utility.trace('lv_legislation_code ='|| lv_legislation_code);
1935 End if;
1936 /* Checks whether retro component exist for the element */
1937 OPEN c_get_retro_components;
1938 FETCH c_get_retro_components INTO ln_retro_comp_exists;
1939 CLOSE c_get_retro_components;
1940
1941 if ln_retro_comp_exists <> 0 then /* 5461629 */
1942 fnd_file.put_line(FND_FILE.LOG,'MESSAGE: Retro Component already exist for Element ' || lv_element_name || ' (Element Type ID: ' || p_element_type_id || ').' );
1943 end if;
1944 /* Bug#5583165 */
1945 if ln_retro_comp_exists = 0 then
1946 -- Procedure sets p_user_element_exist to 'Y' if GT12,LT12 elements of retro element were created by user
1947 -- and sets p_upgrade_element_exis to 'Y' if GT12,LT12 elements of retro element were created by upgrade process
1948 element_exist_check(nvl(ln_retro_element_type_id,p_element_type_id),ln_ele_business_group_id,l_upgrade_element_exist,l_user_element_exist);
1949 end if;
1950
1951 if ln_retro_comp_exists = 0 and l_user_element_exist='N'
1952 AND ( (lv_classification_name = 'Earnings' AND lv_class_label ='Standard')
1953 OR (lv_classification_name = 'Pre Tax Deductions')
1954 ) /* Bug 5731490 - Enter Loop to create Retro components only for Earnings Standard and
1955 Pre Tax Deductions */
1956 THEN /* Bug#5583165 */
1957 /* Get retro component info for AU legislation. */
1958 open c_retro_info(lv_legislation_code);
1959 LOOP
1960 fetch c_retro_info into ln_retro_component_id
1961 ,ln_time_span_id
1962 ,lv_start_time_name
1963 ,lv_end_time_name;
1964
1965 IF c_retro_info%NOTFOUND THEN
1966 exit;
1967 END IF;
1968
1969 IF g_debug THEN
1970 hr_utility.trace('ln_retro_component_id ='|| ln_retro_component_id);
1971 hr_utility.trace('ln_time_span_id ='|| ln_time_span_id);
1972 hr_utility.trace('lv_start_time_name ='|| lv_start_time_name);
1973 hr_utility.trace('lv_end_time_name ='|| lv_end_time_name);
1974 End if;
1975 /* Time spans define retropayment types for greater than 12 months case*/
1976 IF lv_start_time_name = 'START_OF_TIME' AND lv_end_time_name = 'END_OF_12_MONTHS' THEN
1977
1978 create_element(ln_ele_business_group_id,
1979 nvl(ln_retro_element_type_id,p_element_type_id),
1980 'GT12',
1981 retro_element_type_id
1982 );
1983 /* If element <Retro Element> GT12 already exist then
1984 no need to create input value and balance feed
1985 */
1986 IF l_upgrade_element_exist='N' THEN /* Bug#5583165 */
1987
1988 create_input_value(ln_ele_business_group_id,
1989 nvl(ln_retro_element_type_id,p_element_type_id),
1990 'GT12',
1991 retro_element_type_id);
1992
1993 /* Bug 6455303 - Added Check to set Balance name and Scale based on Classfication name */
1994 IF lv_classification_name = 'Pre Tax Deductions'
1995 THEN
1996 l_retro_balance_name := 'Retro Pre Tax GT 12 Mths Amount';
1997 l_scale := 1;
1998 ELSE
1999 l_retro_balance_name := 'Lump Sum E Payments';
2000 l_scale := 1;
2001 END IF;
2002 /* End Bug 6455303 */
2003
2004 OPEN c_get_balance_type_id(l_retro_balance_name);
2005 FETCH c_get_balance_type_id INTO ln_balance_type_id;
2006 CLOSE c_get_balance_type_id;
2007
2008 create_balance_feeds(ln_ele_business_group_id,
2009 nvl(ln_retro_element_type_id,p_element_type_id),
2010 'GT12',
2011 retro_element_type_id,
2012 ln_balance_type_id,
2013 l_scale);
2014
2015 IF lv_classification_name <> 'Pre Tax Deductions' THEN
2016 create_ff_results(ln_ele_business_group_id,
2017 'GT12',
2018 retro_element_type_id);
2019 END IF;
2020 END IF;
2021 /* Time spans define retropayment types for Less then 12 monthe Previous Year case*/
2022 ELSIF lv_start_time_name = 'START_OF_PREV_LT12' AND lv_end_time_name = 'END_OF_PREV_YEAR' THEN
2023
2024 create_element(ln_ele_business_group_id,
2025 nvl(ln_retro_element_type_id,p_element_type_id),
2026 'LT12 Prev',
2030 no need to create input value and balance feed
2027 retro_element_type_id
2028 );
2029 /* If element <Retro Element> LT12 Prev already exist then
2031 */
2032 IF l_upgrade_element_exist='N' THEN /* Bug#5583165 */
2033 create_input_value(ln_ele_business_group_id,
2034 nvl(ln_retro_element_type_id,p_element_type_id),
2035 'LT12 Prev',
2036 retro_element_type_id);
2037
2038 /* Bug 6455303 - Added Check to set Balance name and Scale based on Classfication name */
2039 IF lv_classification_name = 'Pre Tax Deductions'
2040 THEN
2041 l_retro_balance_name := 'Retro Pre Tax LT 12 Mths Prev Yr Amount';
2042 l_scale := 1;
2043 ELSE
2044 l_retro_balance_name := 'Retro LT 12 Mths Prev Yr Amount';
2045 l_scale := 1;
2046 END IF;
2047 /* End Bug 6455303 */
2048
2049 OPEN c_get_balance_type_id(l_retro_balance_name);
2050 FETCH c_get_balance_type_id INTO ln_balance_type_id;
2051 CLOSE c_get_balance_type_id;
2052
2053 create_balance_feeds(ln_ele_business_group_id,
2054 nvl(ln_retro_element_type_id,p_element_type_id),
2055 'LT12 Prev',
2056 retro_element_type_id,
2057 ln_balance_type_id,
2058 l_scale);
2059
2060 IF lv_classification_name <> 'Pre Tax Deductions' THEN
2061 create_ff_results(ln_ele_business_group_id,
2062 'LT12 Prev',
2063 retro_element_type_id);
2064 END IF;
2065 END IF;
2066 /* Time spans define retropayment types for Less then 12 monthe Current Year case*/
2067 ELSIF lv_start_time_name = 'START_OF_CURRENT_YEAR' AND lv_end_time_name = 'END_OF_TIME' THEN
2068
2069 create_element(ln_ele_business_group_id,
2070 nvl(ln_retro_element_type_id,p_element_type_id),
2071 'LT12 Curr',
2072 retro_element_type_id
2073 );
2074
2075 /* If element <Retro Element> LT12 Curr already exist then
2076 no need to create input value and balance feed
2077 */
2078 IF l_upgrade_element_exist='N' THEN /* Bug#5583165 */
2079 create_input_value(ln_ele_business_group_id,
2080 nvl(ln_retro_element_type_id,p_element_type_id),
2081 'LT12 Curr',
2082 retro_element_type_id);
2083
2084 OPEN c_get_balance_type_id('Retro LT 12 Mths Curr Yr Amount');
2085 FETCH c_get_balance_type_id INTO ln_balance_type_id;
2086 CLOSE c_get_balance_type_id;
2087 IF lv_classification_name = 'Pre Tax Deductions' THEN
2088 l_scale := -1;
2089 ELSE
2090 l_scale := 1;
2091 END IF;
2092
2093 create_balance_feeds(ln_ele_business_group_id,
2094 nvl(ln_retro_element_type_id,p_element_type_id),
2095 'LT12 Curr',
2096 retro_element_type_id,
2097 ln_balance_type_id,
2098 l_scale);
2099
2100 IF lv_classification_name <> 'Pre Tax Deductions' THEN
2101 create_ff_results(ln_ele_business_group_id,
2102 'LT12 Curr',
2103 retro_element_type_id);
2104 END IF;
2105 END IF;
2106 END IF;
2107 --
2108 -- Bug#5556042 Skiped Time Span Start of Time - End of Time as this
2109 -- time span should not be attached to Earning Elements.
2110 --
2111 IF lv_start_time_name = 'START_OF_TIME' AND lv_end_time_name = 'END_OF_TIME' THEN
2112 Null;
2113 Else
2114 if ln_legislation_code is null and ln_ele_business_group_id is not null then
2115
2116 IF g_debug THEN
2117 hr_utility.trace('Custom Element');
2118 hr_utility.set_location(gv_package_name || lv_procedure_name, 110);
2119 End if;
2120 /*Creates the retro component usages for the element if its not exist */
2121 insert_retro_comp_usages
2122 (p_business_group_id => ln_ele_business_group_id
2123 ,p_legislation_code => null
2124 ,p_retro_component_id => ln_retro_component_id
2125 ,p_creator_id => p_element_type_id
2126 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
2127 IF g_debug THEN
2128 hr_utility.set_location(gv_package_name || lv_procedure_name, 120);
2129 End if;
2130 /*Creates the element span usages for the element */
2131 insert_element_span_usages
2132 (p_business_group_id => ln_ele_business_group_id
2133 ,p_retro_element_type_id => retro_element_type_id
2134 ,p_legislation_code => null
2138 end if;
2135 ,p_time_span_id => ln_time_span_id
2136 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
2137
2139 End if;
2140 l_warning_flag := 0;
2141 END LOOP;
2142
2143 IF l_warning_flag = 0 THEN
2144
2145 create_event_group(ln_ele_business_group_id,
2146 ln_event_group_id);
2147
2148 insert_event_group(ln_ele_business_group_id
2149 ,p_element_type_id
2150 ,ln_event_group_id);
2151
2152 fnd_file.put_line(FND_FILE.LOG,'MESSAGE: Successfully upgraded Element ' || lv_element_name || ' (Element Type ID: ' || p_element_type_id || ').');
2153
2154 END IF;
2155
2156 close c_retro_info;
2157 /* Bug 65731490 - Added ELSIF Section */
2158 ELSIF (ln_retro_comp_exists = 0 AND l_user_element_exist='N'
2159 AND ln_retro_summ_ele_id IS NOT NULL)
2160 THEN
2161 /* Bug 5731490 -
2162 Cases where other classification Elements have a summary element defined,
2163 Set the Summary element as retro component */
2164
2165 OPEN c_retro_info(lv_legislation_code);
2166 LOOP
2167 FETCH c_retro_info INTO ln_retro_component_id
2168 ,ln_time_span_id
2169 ,lv_start_time_name
2170 ,lv_end_time_name;
2171
2172 IF c_retro_info%NOTFOUND THEN
2173 EXIT;
2174 END IF;
2175
2176 IF (lv_start_time_name ='START_OF_TIME' AND lv_end_time_name = 'END_OF_TIME')
2177 THEN
2178
2179 IF ln_legislation_code IS NULL AND ln_ele_business_group_id IS NOT NULL THEN
2180
2181 IF g_debug THEN
2182 hr_utility.trace('Custom Element');
2183 hr_utility.set_location(gv_package_name || lv_procedure_name, 110);
2184 END IF;
2185 /*Creates the retro component usages for the element if its not exist */
2186 insert_retro_comp_usages
2187 (p_business_group_id => ln_ele_business_group_id
2188 ,p_legislation_code => null
2189 ,p_retro_component_id => ln_retro_component_id
2190 ,p_creator_id => p_element_type_id
2191 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
2192 IF g_debug THEN
2193 hr_utility.set_location(gv_package_name || lv_procedure_name, 120);
2194 End if;
2195 /*Creates the element span usages for the element */
2196 insert_element_span_usages
2197 (p_business_group_id => ln_ele_business_group_id
2198 ,p_retro_element_type_id => ln_retro_summ_ele_id /* Bug 5731490 - retro element is summary element */
2199 ,p_legislation_code => null
2200 ,p_time_span_id => ln_time_span_id
2201 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
2202
2203 l_warning_flag := 0;
2204 END IF;
2205 END IF;
2206 END LOOP;
2207 IF l_warning_flag = 0 THEN
2208
2209 create_event_group(ln_ele_business_group_id,
2210 ln_event_group_id);
2211
2212 insert_event_group(ln_ele_business_group_id
2213 ,p_element_type_id
2214 ,ln_event_group_id);
2215
2216 fnd_file.put_line(FND_FILE.LOG,'MESSAGE: Successfully upgraded Element ' || lv_element_name || ' (Element Type ID: ' || p_element_type_id || ').');
2217
2218 END IF;
2219 CLOSE c_retro_info;
2220
2221 END IF; /*End of Check for Earnings Standard and Pre Tax Deductions */
2222
2223 IF g_debug THEN
2224 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
2225 End if;
2226
2227 /* Bug 5749509 - Reset the data migrator Mode */
2228 hr_general.g_data_migrator_mode := l_migrator_mode_status;
2229
2230 EXCEPTION
2231 WHEN OTHERS THEN
2232 /* Bug 5749509 - Reset the data migrator Mode */
2233 hr_general.g_data_migrator_mode := l_migrator_mode_status;
2234 IF g_debug THEN
2235 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
2236 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
2237 END IF;
2238 raise;
2239 END upgrade_element;
2240
2241 /*--------------------------------------------------------------------------------
2242 Bug 5731490 - Changes for 11i Enhanced Retropay
2243 The following set of Functions/Procedures is used by
2244 Concurrent Program - "Enable Enhanced Retropay for All Australia Business Groups"
2245 --------------------------------------------------------------------------------
2246 */
2247
2248 /*
2249 Function : set_retro_leg_rule
2250 Description : This function is to be used to enable Enhanced Retropay Legislation
2251 Rule.The function will do the following,
2252 (A) If Called from Upgrade process, set the Legislation Rule
2253 (B) If Called from HRGLOBAL, return the Legislation Rule status
2257 'N' - Rule is not set
2254 Inputs : p_calling_form - Values : UPGRADE/HRGLOBAL
2255 Indicates where the function is called from
2256 Returns : 'Y' - Rule is set
2258 */
2259
2260
2261 FUNCTION set_retro_leg_rule(p_calling_form varchar2)
2262 RETURN varchar2
2263 IS
2264
2265 CURSOR get_leg_rule
2266 IS
2267 SELECT rule_mode
2268 FROM pay_legislation_rules
2269 WHERE rule_type = 'ADVANCED_RETRO'
2270 AND legislation_code = 'AU';
2271
2272 CURSOR csr_exists
2273 IS
2274 SELECT count(*)
2275 FROM pay_legislation_rules
2276 WHERE rule_type = 'ADVANCED_RETRO'
2277 AND legislation_code = 'AU';
2278
2279
2280 l_return_flag VARCHAR2(10);
2281 l_adv_retro_rule VARCHAR2(10);
2282 l_exists NUMBER;
2283 l_procedure_name VARCHAR2(80);
2284
2285
2286 BEGIN
2287
2288 g_debug := hr_utility.debug_enabled;
2289
2290 IF g_debug THEN
2291 l_procedure_name := '.set_retro_leg_rule';
2292 hr_utility.set_location('Entering Procedure '||gv_package_name||l_procedure_name,10);
2293 hr_utility.set_location('IN p_calling_form '||p_calling_form,10);
2294 END IF;
2295
2296
2297 l_return_flag := 'N'; /* Default - Rule not enabled */
2298
2299 IF (p_calling_form = 'HRGLOBAL')
2300 THEN
2301 OPEN get_leg_rule;
2302 FETCH get_leg_rule INTO l_adv_retro_rule;
2303 CLOSE get_leg_rule ;
2304
2305 IF NVL(l_adv_retro_rule,'N') = 'Y'
2306 THEN
2307 l_return_flag := 'Y';
2308 ELSE
2309 l_return_flag := 'N';
2310 END IF;
2311 ELSE /* p_calling_form = 'UPGRADE' */
2312
2313 OPEN get_leg_rule;
2314 FETCH get_leg_rule INTO l_adv_retro_rule;
2315 CLOSE get_leg_rule ;
2316
2317 IF NVL(l_adv_retro_rule,'N') = 'Y'
2318 THEN
2319 l_return_flag := 'Y';
2320 ELSE
2321 /* Insert the legislation rule */
2322 OPEN csr_exists;
2323 FETCH csr_exists INTO l_exists;
2324 CLOSE csr_exists;
2325
2326 IF l_exists = 0 THEN
2327 INSERT INTO pay_legislation_rules
2328 (rule_type
2329 ,rule_mode
2330 ,legislation_code)
2331 VALUES
2332 ('ADVANCED_RETRO'
2333 ,'Y'
2334 ,'AU');
2335 l_return_flag := 'Y';
2336 ELSE
2337 UPDATE pay_legislation_rules
2338 SET rule_mode = 'Y'
2339 WHERE rule_type = 'ADVANCED_RETRO'
2340 AND legislation_code = 'AU' ;
2341 l_return_flag := 'Y';
2342 END IF;
2343 END IF;
2344 END IF;
2345
2346 RETURN l_return_flag;
2347 END set_retro_leg_rule;
2348
2349 --------------------------------------------------------------------------
2350 -- Private Function to create retro definitions
2351 -- If the retro shortname already exists for this legislation
2352 -- then it will not be inserted or updated.
2353 --------------------------------------------------------------------------
2354
2355
2356 FUNCTION create_retro_definitions
2357 (p_short_name in pay_retro_definitions.short_name%TYPE
2358 ,p_definition_name in pay_retro_definitions.definition_name%TYPE)
2359 RETURN NUMBER
2360 IS
2361 --
2362 l_retro_definition_id pay_retro_definitions.retro_definition_id%TYPE;
2363 --
2364 CURSOR csr_defn_exists
2365 IS
2366 SELECT retro_definition_id
2367 FROM pay_retro_definitions
2368 WHERE short_name = p_short_name
2369 AND legislation_code = g_legislation_code;
2370 --
2371 CURSOR csr_get_defn_id
2372 IS
2373 SELECT pay_retro_definitions_s.nextval
2374 FROM dual;
2375 --
2376 BEGIN
2377 --
2378 OPEN csr_defn_exists;
2379 FETCH csr_defn_exists INTO l_retro_definition_id;
2380 CLOSE csr_defn_exists;
2381 --
2382 IF l_retro_definition_id IS NULL THEN
2383 --
2384 OPEN csr_get_defn_id;
2385 FETCH csr_get_defn_id INTO l_retro_definition_id;
2386 CLOSE csr_get_defn_id;
2387 --
2388 INSERT INTO pay_retro_definitions
2389 (retro_definition_id
2390 ,short_name
2391 ,definition_name
2392 ,legislation_code)
2393 VALUES
2394 (l_retro_definition_id
2395 ,p_short_name
2396 ,p_definition_name
2397 ,g_legislation_code);
2398 --
2399 END IF;
2400 --
2401 RETURN l_retro_definition_id;
2402 --
2403 EXCEPTION
2404 WHEN OTHERS THEN
2405 hr_utility.trace('Error: ' || sqlerrm);
2406 rollback;
2407 hr_utility.raise_error;
2408 END create_retro_definitions;
2409 --
2410 --------------------------------------------------------------------------
2411 -- Private Function to create retro components
2412 -- If the component shortname already exists for this legislation
2416 FUNCTION create_retro_components
2413 -- then the details will be updated.
2414 -- else a new retro component will be created.
2415 --------------------------------------------------------------------------
2417 (p_short_name in pay_retro_components.short_name%TYPE
2418 ,p_component_name in pay_retro_components.component_name%TYPE
2419 ,p_retro_type in pay_retro_components.retro_type%TYPE
2420 ,p_recalc_style in pay_retro_components.recalculation_style%TYPE
2421 ,p_date_override_proc in pay_retro_components.date_override_procedure%TYPE
2422 )
2423 RETURN NUMBER
2424 IS
2425 --
2426 l_retro_component_id pay_retro_components.retro_component_id%TYPE;
2427 --
2428 CURSOR csr_component_exists
2429 IS
2430 SELECT retro_component_id
2431 FROM pay_retro_components
2432 WHERE short_name = p_short_name
2433 AND legislation_code = g_legislation_code;
2434 --
2435 CURSOR csr_get_comp_id
2436 IS
2437 SELECT pay_retro_components_s.nextval
2438 FROM dual;
2439 --
2440 BEGIN
2441 --
2442 OPEN csr_component_exists;
2443 FETCH csr_component_exists INTO l_retro_component_id;
2444 CLOSE csr_component_exists;
2445 --
2446 IF l_retro_component_id IS NULL THEN
2447 --
2448 OPEN csr_get_comp_id;
2449 FETCH csr_get_comp_id INTO l_retro_component_id;
2450 CLOSE csr_get_comp_id;
2451 --
2452 INSERT INTO pay_retro_components
2453 (retro_component_id
2454 ,short_name
2455 ,component_name
2456 ,retro_type
2457 ,legislation_code
2458 ,recalculation_style
2459 ,date_override_procedure)
2460 VALUES
2461 (l_retro_component_id
2462 ,p_short_name
2463 ,p_component_name
2464 ,p_retro_type
2465 ,g_legislation_code
2466 ,p_recalc_style
2467 ,p_date_override_proc);
2468 --
2469 ELSE
2470 --
2471 UPDATE pay_retro_components
2472 SET component_name = p_component_name
2473 , retro_type = p_retro_type
2474 , recalculation_style = p_recalc_style
2475 , date_override_procedure = p_date_override_proc
2476 WHERE retro_component_id = l_retro_component_id;
2477 --
2478 END IF;
2479 --
2480 RETURN l_retro_component_id;
2481 --
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 hr_utility.trace('Error: ' || sqlerrm);
2485 rollback;
2486 hr_utility.raise_error;
2487 END create_retro_components;
2488 --
2489 --------------------------------------------------------------------------
2490 -- Private Function to create retro definition components
2491 -- If the definition and component combination already exists
2492 -- then the priority will be updated.
2493 -- else a new retro definition component will be created.
2494 --------------------------------------------------------------------------
2495 FUNCTION create_retro_defn_components
2496 (p_retro_definition_id pay_retro_defn_components.retro_definition_id%TYPE
2497 ,p_retro_component_id pay_retro_defn_components.retro_component_id%TYPE
2498 ,p_priority in pay_retro_defn_components.priority%TYPE)
2499 RETURN NUMBER
2500 IS
2501 --
2502 l_definition_component_id pay_retro_defn_components.definition_component_id%TYPE;
2503 --
2504 CURSOR csr_defn_comp_exists
2505 IS
2506 SELECT definition_component_id
2507 FROM pay_retro_defn_components
2508 WHERE retro_definition_id = p_retro_definition_id
2509 AND retro_component_id = p_retro_component_id;
2510 --
2511 CURSOR csr_get_defn_comp_id IS
2512 SELECT pay_retro_defn_components_s.nextval
2513 from dual;
2514 --
2515 BEGIN
2516 --
2517 OPEN csr_defn_comp_exists;
2518 FETCH csr_defn_comp_exists INTO l_definition_component_id;
2519 CLOSE csr_defn_comp_exists;
2520 --
2521 IF l_definition_component_id IS NULL THEN
2522 --
2523 OPEN csr_get_defn_comp_id;
2524 FETCH csr_get_defn_comp_id INTO l_definition_component_id;
2525 CLOSE csr_get_defn_comp_id;
2526 --
2527 INSERT INTO pay_retro_defn_components
2528 (definition_component_id
2529 ,retro_definition_id
2530 ,retro_component_id
2531 ,priority)
2532 VALUES
2533 (l_definition_component_id
2534 ,p_retro_definition_id
2535 ,p_retro_component_id
2536 ,p_priority);
2537 --
2538 ELSE
2539 --
2540 UPDATE pay_retro_defn_components
2541 SET priority = p_priority
2542 WHERE definition_component_id = l_definition_component_id
2543 AND retro_definition_id = p_retro_definition_id
2544 AND retro_component_id = p_retro_component_id;
2545 --
2546 END IF;
2547 --
2548 RETURN l_definition_component_id;
2549 --
2550 EXCEPTION
2551 WHEN OTHERS THEN
2552 hr_utility.trace('Error: ' || sqlerrm);
2553 rollback;
2554 hr_utility.raise_error;
2555 END create_retro_defn_components;
2556
2557 --
2558 --------------------------------------------------------------------------
2559 -- Private Function to create time definitions
2563 --------------------------------------------------------------------------
2560 -- If the short_name and period_type combination already exists
2561 -- then the other fields will be updated.
2562 -- else a new time definition will be created.
2564 FUNCTION create_time_definitions
2565 (p_short_name pay_time_definitions.short_name%TYPE
2566 ,p_definition_name pay_time_definitions.definition_name%TYPE
2567 ,p_period_type pay_time_definitions.period_type%TYPE
2568 ,p_period_unit pay_time_definitions.period_unit%TYPE
2569 ,p_day_adjustment pay_time_definitions.day_adjustment%TYPE
2570 ,p_dynamic_code pay_time_definitions.dynamic_code%TYPE)
2571 RETURN NUMBER
2572 IS
2573 --
2574 l_time_definition_id pay_time_definitions.time_definition_id%TYPE;
2575 --
2576 CURSOR csr_time_definition_exists
2577 IS
2578 SELECT time_definition_id
2579 FROM pay_time_definitions
2580 WHERE short_name = p_short_name
2581 AND period_type = p_period_type
2582 AND legislation_code = g_legislation_code;
2583 --
2584 CURSOR csr_get_time_definition
2585 IS
2586 SELECT pay_time_definitions_s.nextval
2587 from dual;
2588 --
2589 BEGIN
2590 --
2591 OPEN csr_time_definition_exists;
2592 FETCH csr_time_definition_exists INTO l_time_definition_id;
2593 CLOSE csr_time_definition_exists;
2594 --
2595 IF l_time_definition_id IS NULL THEN
2596 --
2597 OPEN csr_get_time_definition;
2598 FETCH csr_get_time_definition INTO l_time_definition_id;
2599 CLOSE csr_get_time_definition;
2600 --
2601 INSERT INTO pay_time_definitions
2602 (time_definition_id
2603 ,short_name
2604 ,definition_name
2605 ,period_type
2606 ,period_unit
2607 ,day_adjustment
2608 ,dynamic_code
2609 ,business_group_id
2610 ,legislation_code)
2611 VALUES
2612 (l_time_definition_id
2613 ,p_short_name
2614 ,p_definition_name
2615 ,p_period_type
2616 ,p_period_unit
2617 ,p_day_adjustment
2618 ,p_dynamic_code
2619 ,null
2620 ,g_legislation_code);
2621 --
2622 ELSE
2623 --
2624 UPDATE pay_time_definitions
2625 SET definition_name = p_definition_name
2626 , period_unit = p_period_unit
2627 , day_adjustment = p_day_adjustment
2628 , dynamic_code = p_dynamic_code
2629 WHERE time_definition_id = l_time_definition_id;
2630 --
2631 END IF;
2632 --
2633 RETURN l_time_definition_id;
2634 --
2635 EXCEPTION
2636 WHEN OTHERS THEN
2637 hr_utility.trace('Error: ' || sqlerrm);
2638 rollback;
2639 hr_utility.raise_error;
2640 END create_time_definitions;
2641
2642 --------------------------------------------------------------------------
2643 -- Private Function to create time spans
2644 -- If the creator_id and creator_type combination already exists
2645 -- then the start and end time definition ids will be updated.
2646 -- else a new time span will be created.
2647 --------------------------------------------------------------------------
2648 FUNCTION create_time_spans
2649 (p_creator_id pay_time_spans.creator_id%TYPE
2650 ,p_creator_type pay_time_spans.creator_type%TYPE
2651 ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2652 ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2653 RETURN NUMBER
2654 IS
2655 --
2656 l_time_span_id pay_time_spans.time_span_id%TYPE;
2657 --
2658 CURSOR csr_time_span_exists
2659 IS
2660 SELECT time_span_id
2661 FROM pay_time_spans
2662 WHERE creator_id = p_creator_id
2663 AND creator_type = p_creator_type
2664 AND start_time_def_id = p_start_time_def_id
2665 AND end_time_def_id = p_end_time_def_id;
2666
2667 CURSOR csr_get_time_span
2668 IS
2669 select pay_time_spans_s.nextval
2670 from dual;
2671 --
2672 BEGIN
2673 --
2674 OPEN csr_time_span_exists;
2675 FETCH csr_time_span_exists INTO l_time_span_id;
2676 CLOSE csr_time_span_exists;
2677 --
2678 IF l_time_span_id IS NULL THEN
2679 --
2680 open csr_get_time_span;
2681 fetch csr_get_time_span into l_time_span_id;
2682 close csr_get_time_span;
2683
2684 INSERT INTO pay_time_spans
2685 (time_span_id
2686 ,creator_id
2687 ,creator_type
2688 ,start_time_def_id
2689 ,end_time_def_id)
2690 VALUES(l_time_span_id
2691 , p_creator_id
2692 , p_creator_type
2693 , p_start_time_def_id
2694 , p_end_time_def_id);
2695 --
2696 ELSE
2697 --
2698 UPDATE pay_time_spans
2699 SET start_time_def_id = p_start_time_def_id
2700 , end_time_def_id = p_end_time_def_id
2701 WHERE time_span_id = l_time_span_id;
2702 --
2703 END IF;
2704
2705 RETURN l_time_span_id;
2706 --
2707 EXCEPTION
2708 WHEN OTHERS THEN
2709 hr_utility.trace('Error: While inserting time spans : ' || sqlerrm);
2713 END create_time_spans;
2710 rollback;
2711 hr_utility.raise_error;
2712 --
2714
2715 --------------------------------------------------------------------------
2716 -- Private Function to create element spans
2717 -- If the creator_id and creator_type combination already exists
2718 -- then the details will not be updated.
2719 --------------------------------------------------------------------------
2720 PROCEDURE create_element_spans
2721 (p_retro_component_usage_id IN pay_retro_component_usages.retro_component_usage_id%TYPE
2722 ,p_retro_component_id IN pay_retro_components.retro_component_id%TYPE
2723 ,p_retro_element_name IN pay_element_types_f.element_name%TYPE
2724 ,p_time_span_id IN pay_element_span_usages.time_span_id%TYPE
2725 )
2726 IS
2727 --
2728 l_time_span_id pay_time_spans.time_span_id%TYPE;
2729 l_element_type_id pay_element_types_f.element_type_id%TYPE;
2730 l_element_span_usage_id pay_element_span_usages.element_span_usage_id%TYPE;
2731 --
2732 CURSOR csr_get_element_type_id IS
2733 SELECT element_type_id
2734 FROM pay_element_types_f
2735 WHERE element_name = p_retro_element_name
2736 AND legislation_code = g_legislation_code;
2737 --
2738 CURSOR csr_exists IS
2739 SELECT element_span_usage_id
2740 FROM pay_element_span_usages pesu
2741 WHERE pesu.retro_component_usage_id = p_retro_component_usage_id
2742 AND pesu.time_span_id = p_time_span_id
2743 AND pesu.adjustment_type IS NULL;
2744 --
2745 BEGIN
2746 --
2747 hr_utility.trace('Fetch the required details');
2748 --
2749 hr_utility.trace('Checking... if it already exists');
2750 --
2751 OPEN csr_exists;
2752 FETCH csr_exists INTO l_element_span_usage_id;
2753 CLOSE csr_exists;
2754 --
2755 IF l_element_span_usage_id is null THEN
2756 --
2757 OPEN csr_get_element_type_id;
2758 FETCH csr_get_element_type_id INTO l_element_type_id;
2759 CLOSE csr_get_element_type_id;
2760 --
2761 INSERT INTO pay_element_span_usages
2762 (ELEMENT_SPAN_USAGE_ID
2763 ,LEGISLATION_CODE
2764 ,TIME_SPAN_ID
2765 ,RETRO_COMPONENT_USAGE_ID
2766 ,RETRO_ELEMENT_TYPE_ID
2767 ,CREATION_DATE
2768 ,CREATED_BY
2769 ,LAST_UPDATE_DATE
2770 ,LAST_UPDATED_BY
2771 ,LAST_UPDATE_LOGIN
2772 ,OBJECT_VERSION_NUMBER)
2773 SELECT pay_element_span_usages_s.nextval
2774 , g_legislation_code
2775 , p_time_span_id
2776 , p_retro_component_usage_id
2777 , l_element_type_id
2778 , sysdate
2779 , 1
2780 , sysdate
2781 , 1
2782 , -1
2783 , 1
2784 FROM dual;
2785 --
2786 hr_utility.trace('Inserted the required element');
2787 END IF;
2788 --
2789 END create_element_spans;
2790
2791 --
2792 --------------------------------------------------------------------------
2793 -- Private Function to create component usages and element spans
2794 -- Uses the supporint procedure create_element_spans
2795 --------------------------------------------------------------------------
2796 PROCEDURE create_comp_usages (p_creator_name IN VARCHAR2
2797 ,p_retro_element_name IN VARCHAR2
2798 ,p_component_name IN VARCHAR2
2799 ,p_reprocess_type IN VARCHAR2
2800 ,p_default_component IN VARCHAR2
2801 ,p_time_span_id IN pay_element_span_usages.time_span_id%TYPE
2802 )
2803 IS
2804 --
2805 l_retro_component_usage_id pay_retro_component_usages.retro_component_usage_id%TYPE;
2806 l_retro_component_id pay_retro_components.retro_component_id%TYPE;
2807 l_element_type_id pay_element_types_f.element_type_id%TYPE;
2808 --
2809 CURSOR csr_get_details IS
2810 SELECT c.retro_component_id
2811 , e.element_type_id
2812 FROM pay_retro_components c
2813 , pay_element_types_f e
2814 WHERE c.component_name = p_component_name
2815 AND e.element_name = p_creator_name
2816 AND e.legislation_code = g_legislation_code
2817 AND c.legislation_code = g_legislation_code;
2818 --
2819 CURSOR csr_exists IS
2820 SELECT retro_component_usage_id
2821 FROM pay_retro_component_usages prcu
2822 WHERE prcu.retro_component_id = l_retro_component_id
2823 AND prcu.creator_id = l_element_type_id
2824 AND prcu.creator_type ='ET';
2825 --
2826 BEGIN
2827 --
2828 OPEN csr_get_details;
2829 FETCH csr_get_details INTO l_retro_component_id, l_element_type_id;
2830 IF csr_get_details%NOTFOUND THEN
2831 hr_utility.trace('Invalid component or element type');
2832 hr_utility.raise_error;
2833 END IF;
2834 CLOSE csr_get_details;
2835 --
2836 hr_utility.trace('Valid component and element type');
2837 --
2838 OPEN csr_exists;
2839 FETCH csr_exists into l_retro_component_usage_id;
2840 CLOSE csr_exists;
2841 --
2842 IF l_retro_component_usage_id is null THEN
2843 --
2844 hr_utility.trace('Before inserting data into component usages');
2845 --
2846 INSERT INTO pay_retro_component_usages
2847 (RETRO_COMPONENT_USAGE_ID
2848 ,RETRO_COMPONENT_ID
2849 ,CREATOR_ID
2850 ,CREATOR_TYPE
2854 ,CREATION_DATE
2851 ,DEFAULT_COMPONENT
2852 ,REPROCESS_TYPE
2853 ,LEGISLATION_CODE
2855 ,CREATED_BY
2856 ,LAST_UPDATE_DATE
2857 ,LAST_UPDATED_BY
2858 ,LAST_UPDATE_LOGIN
2859 ,OBJECT_VERSION_NUMBER)
2860 SELECT
2861 pay_retro_component_usages_s.nextval
2862 ,l_retro_component_id
2863 ,l_element_type_id
2864 ,'ET'
2865 ,p_default_component
2866 ,p_reprocess_type
2867 ,g_legislation_code
2868 ,sysdate
2869 ,1
2870 ,sysdate
2871 ,1
2872 ,-1
2873 ,1
2874 FROM dual;
2875 --
2876 SELECT pay_retro_component_usages_s.currval
2877 INTO l_retro_component_usage_id
2878 from dual;
2879 --
2880 hr_utility.trace('Inserted retro component usage: ' || l_retro_component_usage_id);
2881 --
2882 create_element_spans(l_retro_component_usage_id
2883 ,l_retro_component_id
2884 ,p_retro_element_name
2885 ,p_time_span_id);
2886 --
2887 END IF;
2888 --
2889 hr_utility.trace('Inserted retro component: ' || p_retro_element_name);
2890 --
2891 END create_comp_usages;
2892
2893 --------------------------------------------------------------------------
2894 --
2895 -- Private Function to get time definitions
2896 -- Bug#5556042
2897 --------------------------------------------------------------------------
2898 FUNCTION get_time_definitions
2899 (p_short_name pay_time_definitions.short_name%TYPE
2900 )
2901 RETURN NUMBER
2902 IS
2903 --
2904 l_time_definition_id pay_time_definitions.time_definition_id%TYPE;
2905 --
2906 CURSOR csr_get_time_definition
2907 IS
2908 SELECT time_definition_id
2909 FROM pay_time_definitions
2910 WHERE short_name = p_short_name
2911 AND legislation_code = 'AU';
2912 --
2913 --
2914 BEGIN
2915 --
2916 OPEN csr_get_time_definition;
2917 FETCH csr_get_time_definition INTO l_time_definition_id;
2918 CLOSE csr_get_time_definition;
2919 --
2920 RETURN l_time_definition_id;
2921 --
2922 EXCEPTION
2923 WHEN OTHERS THEN
2924 hr_utility.trace('Error: ' || sqlerrm);
2925 rollback;
2926 hr_utility.raise_error;
2927 END get_time_definitions;
2928
2929 /*
2930 Procedure : create_enhanced_retro_defn
2931 Description : This procedure should be used to insert/update the Retro Definitions
2932 and Retro components for using Enhanced Retropay.
2933 */
2934
2935
2936 PROCEDURE create_enhanced_retro_defn
2937 IS
2938
2939 l_retro_defn_id pay_retro_definitions.retro_definition_id%TYPE;
2940 l_corr_up_comp pay_retro_components.retro_component_id%TYPE;
2941 l_back_up_comp pay_retro_components.retro_component_id%TYPE;
2942 l_db_lumpsum_comp pay_retro_components.retro_component_id%TYPE;
2943 l_defn_comp_id pay_retro_defn_components.definition_component_id%TYPE;
2944 l_start_time_id pay_time_definitions.time_definition_id%TYPE;
2945 l_end_time_id pay_time_definitions.time_definition_id%TYPE;
2946 l_time_span_id pay_time_spans.time_span_id%TYPE;
2947
2948 BEGIN
2949
2950 -- Initialize global variables
2951 g_legislation_code := 'AU';
2952 --
2953 -- Insert a new retro definition for Australia
2954 ------------------------------------------------
2955 --
2956 l_retro_defn_id := create_retro_definitions
2957 (p_short_name => 'AU_RETROPAY'
2958 ,p_definition_name => 'Retropay (Australia)');
2959
2960 --
2961 -- Populate the retro components table with the
2962 -- components required for Australia
2963 -----------------------------------------------
2964 --
2965 l_back_up_comp := create_retro_components
2966 (p_short_name => 'AU_BACKDATES'
2967 ,p_component_name => 'Backdated Changes'
2968 ,p_retro_type => 'F'
2969 ,p_recalc_style => null
2970 ,p_date_override_proc => null);
2971 --
2972 --
2973 -- Populate retro_defn_components for the components
2974 -- required for Australia
2975 ----------------------------------------------------
2976 --
2977 l_defn_comp_id := create_retro_defn_components
2978 (l_retro_defn_id
2979 ,l_back_up_comp
2980 ,20);
2981 --
2982 /*
2983 Insert new time definitions and time spans required for Australia
2984 */
2985 --
2986 -- 1. Retro Payments Greater than 12 Months
2987 ---------------------------------------------------------
2988 l_start_time_id := create_time_definitions
2989 (p_short_name => 'START_OF_TIME'
2990 ,p_definition_name => 'Start of Time'
2991 ,p_period_type => 'START_OF_TIME'
2992 ,p_period_unit => '0'
2993 ,p_day_adjustment => 'CURRENT'
2994 ,p_dynamic_code => null);
2995 --
2996 l_end_time_id := create_time_definitions
2997 (p_short_name => 'END_OF_12_MONTHS'
2998 ,p_definition_name => '1 Year Prior To Current Date' /* Bug 5522733 Modified Defn name */
2999 ,p_period_type => 'MONTH'
3000 ,p_period_unit => '-12'
3001 ,p_day_adjustment => 'PRIOR'
3002 ,p_dynamic_code => null);
3003
3007 ,p_start_time_def_id => l_start_time_id
3004 l_time_span_id := create_time_spans
3005 (p_creator_id => l_back_up_comp
3006 ,p_creator_type => 'RC'
3008 ,p_end_time_def_id => l_end_time_id);
3009 --
3010 --
3011 -- 2. Retro Payments Less than 12 Months Previous Year
3012 ---------------------------------------------------------
3013 l_start_time_id := create_time_definitions
3014 (p_short_name => 'START_OF_PREV_LT12'
3015 ,p_definition_name => '1 Year Prior To Current Date ' /* Bug 5522733 Modified Defn name */
3016 ,p_period_type => 'MONTH'
3017 ,p_period_unit => '-12'
3018 ,p_day_adjustment => 'CURRENT'
3019 ,p_dynamic_code => null);
3020 --
3021 l_end_time_id := create_time_definitions
3022 (p_short_name => 'END_OF_PREV_YEAR'
3023 ,p_definition_name => 'End of Previous Year'
3024 ,p_period_type => 'TYEAR'
3025 ,p_period_unit => '0'
3026 ,p_day_adjustment => 'PRIOR'
3027 ,p_dynamic_code => null);
3028
3029 l_time_span_id := create_time_spans
3030 (p_creator_id => l_back_up_comp
3031 ,p_creator_type => 'RC'
3032 ,p_start_time_def_id => l_start_time_id
3033 ,p_end_time_def_id => l_end_time_id);
3034 --
3035 --
3036 -- 3. Retro Payments in Current Financial Year
3037 ---------------------------------------------------------
3038 l_start_time_id := create_time_definitions
3039 (p_short_name => 'START_OF_CURRENT_YEAR'
3040 ,p_definition_name => 'Start of Current Year'
3041 ,p_period_type => 'TYEAR'
3042 ,p_period_unit => '0'
3043 ,p_day_adjustment => 'CURRENT'
3044 ,p_dynamic_code => null);
3045 --
3046 l_end_time_id := create_time_definitions
3047 (p_short_name => 'END_OF_TIME'
3048 ,p_definition_name => 'End of Time'
3049 ,p_period_type => 'END_OF_TIME'
3050 ,p_period_unit => '0'
3051 ,p_day_adjustment => 'CURRENT'
3052 ,p_dynamic_code => null);
3053
3054 l_time_span_id := create_time_spans
3055 (p_creator_id => l_back_up_comp
3056 ,p_creator_type => 'RC'
3057 ,p_start_time_def_id => l_start_time_id
3058 ,p_end_time_def_id => l_end_time_id);
3059
3060 --
3061
3062 -- Create component usages for Tax deductions
3063 ---------------------------------------------
3064 --
3065 create_comp_usages( 'Tax Deduction'
3066 , 'Tax Deduction'
3067 , 'Backdated Changes'
3068 , 'R'
3069 , 'Y'
3070 ,l_time_span_id);
3071 --
3072 ---------------------------------------------------------------------------
3073 -- Creating Time span Start Of Time - End Of Time for non earning elements
3074 -----------------------------------------------------------------------------
3075 --
3076 l_start_time_id := get_time_definitions(p_short_name => 'START_OF_TIME');
3077 l_end_time_id := get_time_definitions(p_short_name => 'END_OF_TIME');
3078
3079 l_time_span_id := create_time_spans
3080 (p_creator_id => l_back_up_comp
3081 ,p_creator_type => 'RC'
3082 ,p_start_time_def_id => l_start_time_id
3083 ,p_end_time_def_id => l_end_time_id);
3084
3085
3086 END create_enhanced_retro_defn;
3087
3088 /*
3089 Procedure : set_enh_retro_request_group
3090 Description : This procedure sets up the Request Group for Australia
3091 customers using Enhanced Retropay. It does the following
3092 (A) Add Programs
3093 - Retropay (Enhanced) (RETROENH)
3094 - Retro-Notifications Report (Enhanced) (PAYRPRNP2)
3095 - Retro-Notifications Report (Enhanced) - PDF (PYXMLRNP2)
3096 (B) Remove Programs
3097 - RetroPay By Element (RETROELE)
3098 - Retro-Notifications Report (PAYRPRNP)
3099 */
3100
3101 PROCEDURE set_enh_retro_request_group
3102 IS
3103
3104 TYPE char_tab_type is TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
3105
3106 l_req_grp_name varchar2(80);
3107 l_grp_app_name varchar2(10);
3108
3109 l_add_prog_name char_tab_type;
3110 l_add_prog_app_name varchar2(10);
3111
3112 l_del_prog_name char_tab_type;
3113 l_del_prog_app_name varchar2(10);
3114
3115 l_proc_name varchar2(80);
3116 l_exists boolean;
3117
3118 BEGIN
3119 g_debug := hr_utility.debug_enabled;
3120
3121 IF g_debug
3122 THEN
3123 l_proc_name := '.set_enh_retro_request_group';
3124 hr_utility.trace('Entering '||gv_package_name||l_proc_name);
3125 END IF;
3126
3127 l_req_grp_name := 'AU HRMS Reports and Processes';
3128 l_grp_app_name := 'PER';
3129
3130 /* Add Concurrent Programs */
3131 l_add_prog_app_name := 'PAY' ;
3132
3133 l_add_prog_name(1) := 'RETROENH'; /* Program: Retropay (Enhanced) */
3134 l_add_prog_name(2) := 'PAYRPRNP2'; /* Program: Retro-Notifications Report (Enhanced) */
3135 l_add_prog_name(3) := 'PYXMLRNP2'; /* Program: Retro-Notifications Report (Enhanced) - PDF */
3136
3137
3138 FOR i IN l_add_prog_name.FIRST..l_add_prog_name.LAST
3139 LOOP
3140
3141 l_exists := fnd_program.program_in_group(
3142 program_short_name => l_add_prog_name(i),
3143 program_application => l_add_prog_app_name,
3144 request_group => l_req_grp_name,
3145 group_application => l_grp_app_name);
3146
3147 IF (NOT l_exists)
3148 THEN
3149
3150 fnd_program.add_to_group(
3151 program_short_name => l_add_prog_name(i),
3152 program_application => l_add_prog_app_name,
3153 request_group => l_req_grp_name,
3154 group_application => l_grp_app_name);
3155
3156 IF g_debug
3157 THEN
3158 hr_utility.trace('Program Added to Request Group '||l_add_prog_name(i));
3159 END IF;
3160 END IF;
3161 END LOOP;
3162
3163 /* Delete Concurrent programs */
3164 l_del_prog_app_name := 'PAY';
3165
3166 l_del_prog_name(1) := 'RETROELE'; /* Program: Retropay by element */
3167 l_del_prog_name(2) := 'PAYRPRNP'; /* Program: Retro-Notifications Report (PAYRPRNP) */
3168
3169 FOR i IN l_del_prog_name.FIRST..l_del_prog_name.LAST
3170 LOOP
3171
3172 l_exists := fnd_program.program_in_group(
3173 program_short_name => l_del_prog_name(i),
3174 program_application => l_add_prog_app_name,
3175 request_group => l_req_grp_name,
3176 group_application => l_grp_app_name);
3177
3178 IF (l_exists)
3179 THEN
3180
3181 fnd_program.remove_from_group(
3182 program_short_name => l_del_prog_name(i),
3183 program_application => l_add_prog_app_name,
3184 request_group => l_req_grp_name,
3185 group_application => l_grp_app_name);
3186
3187 IF g_debug
3188 THEN
3189 hr_utility.trace('Program Removed from Request Group '||l_del_prog_name(i));
3190 END IF;
3191 END IF;
3192 END LOOP;
3193
3194 IF g_debug
3195 THEN
3196 l_proc_name := '.set_enh_retro_request_group';
3197 hr_utility.trace('Leaving '||gv_package_name||l_proc_name);
3198 END IF;
3199 END set_enh_retro_request_group;
3200
3201 /*
3202 Procedure : enable_au_enhanced_retro
3203 Description : This stored procedure is registered as concurrent program executable.
3204 Procedure Enables Enhanced Retropay for Australia.
3205 (A) Enh Retro Legislation Rule is defined
3206 (B) Retro Defintions/Components/Time Spans are defined for Australia
3207 (C) Australia Request Group set up to reflect Enh retro
3208 Concurrent programs
3209 Inputs : p_business_group_id - Business Group ID
3210 Outputs : errbuf - Return Error Messages
3211 retcode - Return Completion Status
3212 */
3213
3214 PROCEDURE enable_au_enhanced_retro(
3215 errbuf OUT NOCOPY VARCHAR2
3216 ,retcode OUT NOCOPY NUMBER
3217 )
3218 IS
3219
3220 /*Bug 5879422 */
3221 CURSOR csr_get_business_group_name IS
3222 SELECT rownum ROW_NUM,pbg.name BUS_GROUP_NAME
3223 FROM per_business_groups pbg
3224 WHERE pbg.legislation_code = g_legislation_code;
3225
3226 l_leg_rule VARCHAR2(20);
3227
3228 l_procedure_name VARCHAR2(80);
3229
3230 l_bg_leg_code VARCHAR2(10);
3231
3232 BEGIN
3233
3234 g_debug := hr_utility.debug_enabled;
3235 g_legislation_code := 'AU';
3236
3237 IF g_debug THEN
3238 l_procedure_name := '.enable_au_enhanced_retro';
3239 hr_utility.trace('Entering '||gv_package_name||l_procedure_name);
3240 END IF;
3241
3242 l_leg_rule := pay_au_retro_upgrade.set_retro_leg_rule(p_calling_form => 'UPGRADE');
3243
3244 IF ( l_leg_rule = 'Y')
3245 THEN
3246 fnd_file.put_line(FND_FILE.LOG,' MESSAGE: Enhanced Retropay Rule Enabled ');
3247 pay_au_retro_upgrade.create_enhanced_retro_defn;
3248 fnd_file.put_line(FND_FILE.LOG,' MESSAGE: Enhanced Retropay Retro Definitions Created ');
3249 pay_au_retro_upgrade.set_enh_retro_request_group;
3250 fnd_file.put_line(FND_FILE.LOG,' MESSAGE: Request Group - programs for Enhanced Retropay added ');
3251 fnd_file.put_line(FND_FILE.LOG,' MESSAGE: Enhanced Retropay is enabled for the following Australian Business Groups :');
3252
3253 FOR i IN csr_get_business_group_name /*Bug 5879422 */
3254 LOOP
3255 fnd_file.put_line(FND_FILE.LOG,' '||to_char(i.ROW_NUM)||') '||i.BUS_GROUP_NAME);
3256 END LOOP; /*Bug 5879422 */
3257
3258 fnd_file.put_line(FND_FILE.LOG,' WARNING: Customers who have enabled Enhanced Retro Pay should note that Retro Pay by Element should no longer be used and should be removed from all customer menus.');/*Bug 5879422 */
3259 ELSE
3260 fnd_file.put_line(fnd_file.output,' MESSAGE: Enhanced Retropay Rule NOT Enabled ');
3261 END IF;
3262
3263 END enable_au_enhanced_retro;
3264
3265 BEGIN
3266
3267 gv_package_name := 'pay_au_retro_upgrade';
3268 g_legislation_code := 'AU';
3269
3270 END pay_au_retro_upgrade;
[Home] [Help]