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