[Home] [Help]
PACKAGE BODY: APPS.PAY_IP_STARTUP_UTIL
Source
1 PACKAGE BODY pay_ip_startup_util AS
2 /* $Header: pyintstu.pkb 120.7.12020000.28 2013/03/18 12:16:32 rmugloo ship $ */
3
4 TYPE CHAR60_TABLE IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
5
6 l_org_info_type CHAR60_TABLE;
7 l_description CHAR60_TABLE;
8 l_displayed_org_info_type VARCHAR2(50);
9 l_classification CHAR60_TABLE;
10 -- ---------------------------------------------------------------------
11 -- Procedure to write Output and Log files. It can handle up to two
12 -- tokens.
13 -- ---------------------------------------------------------------------
14
15 g_logging VARCHAR2(1) := 'N';
16 g_start_of_time CONSTANT DATE := TO_DATE('01/01/0001','DD/MM/YYYY');
17
18 FUNCTION logging(p_action_parameter_group_id NUMBER) RETURN VARCHAR2 IS
19 CURSOR csr_logging IS
20 SELECT parameter_name
21 FROM pay_action_parameters
22 WHERE parameter_name = 'LOGGING';
23 l_rec pay_action_parameters.parameter_name%TYPE;
24 BEGIN
25 pay_core_utils.set_pap_group_id(p_action_parameter_group_id);
26 OPEN csr_logging;
27 FETCH csr_logging INTO l_rec;
28 IF csr_logging%NOTFOUND THEN
29 CLOSE csr_logging;
30 RETURN 'N';
31 ELSE
32 CLOSE csr_logging;
33 RETURN 'Y';
34 END IF;
35 END logging;
36
37 -- -------------------------------------------------------------
38 -- This
39 PROCEDURE write_log
40 (p_file_type VARCHAR2,
41 p_message VARCHAR2,
42 p_token1 VARCHAR2,
43 p_token2 VARCHAR2) IS
44
45 BEGIN
46 hr_utility.set_location('pay_ip_startup_util.write_log',10);
47 IF p_message IS NOT NULL THEN
48 fnd_message.set_name('PAY', p_message);
49
50 IF p_token1 IS NOT NULL THEN
51 fnd_message.set_token('1', p_token1);
52 END IF;
53
54 IF p_token2 IS NOT NULL THEN
55 fnd_message.set_token('2', p_token2);
56 END IF;
57 IF p_file_type ='LOG' and g_logging = 'Y' THEN
58 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
59 ELSIF p_file_type ='OUTPUT' THEN
60 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
61 ELSE
62 NULL;
63 END IF;
64 ELSE
65 IF p_file_type ='LOG' AND g_logging = 'Y' THEN
66 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
67 ELSIF p_file_type ='OUTPUT' THEN
68 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
69 ELSE
70 NULL;
71 END IF;
72 END IF;
73 hr_utility.set_location('pay_ip_startup_util.write_log',20);
74 END;
75
76 -- ----------------------------------------------------------------
77 -- Procedure to write into output file.
78 -- ----------------------------------------------------------------
79
80 PROCEDURE write_out IS
81
82 CURSOR get_element_class_csr IS
83 SELECT distinct classification_id, legislation_code, classification_name
84 FROM hr_s_element_classifications pec
85 ,hr_s_application_ownerships ao
86 ,fnd_product_installations b
87 ,fnd_application c
88 WHERE nvl(legislation_code,'X') = 'ZZ'
89 AND ao.key_name = 'CLASSIFICATION_ID'
90 AND TO_NUMBER(ao.key_value) = pec.classification_id
91 AND ao.product_name = c.application_short_name
92 AND c.application_id = b.application_id
93 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
94 OR
95 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
96
97 CURSOR get_balance_type_csr IS
98 SELECT distinct balance_type_id, currency_code, balance_name
99 FROM hr_s_balance_types pbt
100 ,hr_s_application_ownerships ao
101 ,fnd_product_installations b
102 ,fnd_application c
103 WHERE pbt.legislation_code = 'ZZ'
104 AND ao.key_name = 'BALANCE_TYPE_ID'
105 AND TO_NUMBER(ao.key_value) = pbt.balance_type_id
106 AND ao.product_name = c.application_short_name
107 AND c.application_id = b.application_id
108 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
109 OR
110 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
111
112 CURSOR get_defined_balances_csr IS
113 SELECT distinct defined_balance_id, pbt.balance_name bname
114 FROM hr_s_defined_balances pdb
115 ,hr_s_balance_types pbt
116 ,hr_s_application_ownerships ao
117 ,fnd_product_installations b
118 ,fnd_application c
119 WHERE pdb.legislation_code ='ZZ'
120 AND ao.key_name = 'DEFINED_BALANCE_ID'
121 AND pbt.balance_type_id = pdb.balance_type_id
122 AND TO_NUMBER(ao.key_value) = pdb.defined_balance_id
123 AND ao.product_name = c.application_short_name
124 AND c.application_id = b.application_id
125 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
126 OR
127 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
128
129 CURSOR get_balance_dimensions_csr IS
130 SELECT distinct balance_dimension_id, dimension_name
131 FROM hr_s_application_ownerships ao
132 ,hr_s_balance_dimensions pbd
133 ,fnd_product_installations b
134 ,fnd_application c
135 WHERE pbd.legislation_code ='ZZ'
136 AND ao.key_name = 'BALANCE_DIMENSION_ID'
137 AND TO_NUMBER(ao.key_value) = pbd.balance_dimension_id
138 AND ao.product_name = c.application_short_name
139 AND c.application_id = b.application_id
140 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
141 OR
142 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
143
144
145 CURSOR get_routes_csr IS
146 SELECT distinct fr.route_id, route_name
147 FROM hr_s_application_ownerships ao
148 ,hr_s_routes fr
149 ,hr_s_balance_dimensions pbd
150 ,fnd_product_installations b
151 ,fnd_application c
152 WHERE pbd.legislation_code ='ZZ'
153 AND ao.key_name = 'ROUTE_ID'
154 AND TO_NUMBER(ao.key_value) = fr.route_id
155 AND fr.route_id = pbd.route_id
156 AND ao.product_name = c.application_short_name
157 AND c.application_id = b.application_id
158 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
159 OR
160 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
161
162 CURSOR get_leg_field_info_csr IS
163 SELECT field_name
164 FROM hr_s_legislative_field_info
165 WHERE legislation_code = 'ZZ';
166
167
168 CURSOR get_leg_rules_csr IS
169 SELECT rule_type
170 FROM hr_s_legislation_rules
171 WHERE legislation_code = 'ZZ';
172
173 CURSOR get_balance_class_csr IS
174 SELECT distinct pbc.balance_classification_id, pbt.balance_name bname
175 FROM hr_s_balance_classifications pbc
176 ,hr_s_balance_types pbt
177 WHERE pbc.legislation_code ='ZZ'
178 AND pbc.balance_type_id = pbt.balance_type_id;
179
180 BEGIN
181
182 -- write output file for Element Classifications to be Installed.
183 write_log('OUTPUT',NULL,NULL,NULL);
184 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Element Classifications', 'HR_S_ELEMENT_CLASSIFICATIONS');
185
186 FOR rec IN get_element_class_csr LOOP
187 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Element Classification', rec.classification_name);
188 END LOOP;
189
190 -- write output file for Balance Types to be Installed.
191
192 write_log('OUTPUT',NULL,NULL,NULL);
193 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Types', 'HR_S_BALANCE_TYPES');
194
195 FOR rec IN get_balance_type_csr LOOP
196 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Balance Types', rec.balance_name);
197 END LOOP;
198
199 -- write output file for Defined Balance to be Installed.
200
201 write_log('OUTPUT',NULL,NULL,NULL);
202 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Defined Balances', 'HR_S_DEFINED_BALANCES');
203
204 FOR rec IN get_defined_balances_csr LOOP
205 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Defined Balances for Balance Type', rec.bname);
206 END LOOP;
207
208 -- write output file for Balance Dimensions to be Installed.
209
210 write_log('OUTPUT',NULL,NULL,NULL);
211 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Dimensions', 'HR_S_BALANCE_DIMENSIONS');
212
213 FOR rec IN get_balance_dimensions_csr LOOP
214 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Balance Dimension', rec.dimension_name);
215 END LOOP;
216
217 -- write output file for Balance Dimensions to be Installed.
218
219 write_log('OUTPUT',NULL,NULL,NULL);
220 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Routes', 'HR_S_ROUTES');
221
222 FOR rec IN get_routes_csr LOOP
223 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Route', rec.route_name);
224 END LOOP;
225
226 write_log('OUTPUT',NULL,NULL,NULL);
227 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Legislative Field Info', 'HR_S_LEGISLATIVE_FIELD_INFO');
228
229 FOR rec IN get_leg_field_info_csr LOOP
230 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Legislative Field Info', rec.field_name);
231 END LOOP;
232
233 write_log('OUTPUT',NULL,NULL,NULL);
234 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Legislation Rules', 'HR_S_LEGISLATION_RULES');
235
236 FOR rec IN get_leg_rules_csr LOOP
237 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Legislation Rule', rec.rule_type);
238 END LOOP;
239
240 write_log('OUTPUT',NULL,NULL,NULL);
241 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Classifications', 'HR_S_BALANCE_CLASSIFICATIONS');
242
243 FOR rec IN get_balance_class_csr LOOP
244 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Balance Classification for balance', rec.bname);
245 END LOOP;
246
247
248 END write_out;
249
250
251 -- ---------------------------------------------------------------------
252 -- This procedure inserts the Ownership in hr_s_application_ownerships
253 -- table. It takes the required values from where it is being called.
254 -- ---------------------------------------------------------------------
255 PROCEDURE insert_ownership(p_key_name IN VARCHAR2,
256 p_product_name IN VARCHAR2,
257 p_key_value IN VARCHAR2) AS
258 BEGIN
259
260 null;
261
262 /*hr_utility.set_location('--pay_ip_startup_util.insert_ownership',10);
263 INSERT INTO hr_s_application_ownerships
264 ( key_name
265 ,product_name
266 ,key_value)
267 SELECT
268 p_key_name
269 ,p_product_name
270 ,p_key_value
271 FROM dual
272 WHERE NOT EXISTS (SELECT NULL
273 FROM hr_s_application_ownerships
274 WHERE product_name = p_product_name
275 AND key_name = p_key_name
276 AND key_value = p_key_value);
277
278
279 hr_utility.set_location('--pay_ip_startup_util.insert_ownership',20); */
280 END insert_ownership;
281 -- ---------------------------------------------------------------------
282 -- Function to check if
283 -- <i> Localisation is available for the given legislation
284 -- <ii> HRGLOBAL is currently running
285 --<iii> Reference data is not available
286 -- In all the above three cases the program exits giving proper Log
287 -- information. Else it proceeds to create the required values.
288 -- ---------------------------------------------------------------------
289
290 FUNCTION check_to_install (
291 p_legislation_code IN VARCHAR2) RETURN BOOLEAN IS
292
293 v_check_installation boolean := TRUE;
294 l_Installed number := 0;
295 l_reference number := 0;
296
297 BEGIN
298 hr_utility.set_location('pay_ip_startup_util.check_to_install',10);
299 --Returns TRUE if the HR_LEGISLATION_INSTALLATIONS do not have PAY or PER
300 --for the given legislation and if no other patch is getting applied
301 --and reference data is available.
302
303 IF pay_ip_utility.IS_release_122_plus THEN
304 per_ad_zd_seed_wrapper.check_patch_edition;
305 END IF;
306
307 SELECT count(application_short_name)
308 INTO l_Installed
309 FROM hr_legislation_installations
310 WHERE application_short_name IN('PAY','PER')
311 AND legislation_code = p_legislation_code;
312
313 IF l_installed > 0 THEN
314 g_logging := 'Y';
315 write_log ('LOG','PAY_34020_IP_LOCAL_SUPPORT',NULL,NULL);
316 v_check_installation := FALSE;
317 RETURN v_check_installation ;
318 END IF;
319
320 SELECT count(application_short_name)
321 INTO l_Installed
322 FROM hr_legislation_installations
323 WHERE action IS NOT NULL;
324
325 IF l_installed > 0 THEN
326 g_logging := 'Y';
327 write_log ('LOG','PAY_34019_IP_HRGLOBAL_RUNNING',NULL,NULL);
328 v_check_installation := FALSE;
329 RETURN v_check_installation ;
330 END IF;
331
332 BEGIN
333 SELECT 1
334 INTO l_reference
335 FROM dual WHERE EXISTS (SELECT NULL FROM pay_element_classifications
336 WHERE nvl(legislation_code,'X') = 'ZZ'
337 AND business_group_id IS NULL);
338
339 EXCEPTION
340 WHEN NO_DATA_FOUND THEN
341 NULL;
342 END;
343
344 IF l_reference = 0 THEN
345 g_logging := 'Y';
346 write_log ('LOG','PAY_34021_IP_NO_REF_DATA',NULL,NULL);
347 v_check_installation := FALSE;
348 RETURN v_check_installation ;
349 END IF;
350
351 RETURN v_check_installation ;
352 hr_utility.set_location('pay_ip_startup_util.check_to_install',20);
353 END check_to_install;
354
355 -- ---------------------------------------------------------------------
356 -- Function to check if
357 -- <i> the install_tax_unit option chosen by user is different from
358 -- the existing one .
359 -- In the above case the program exits giving proper Log
360 -- information. Else it proceeds to create the required values.
361 -- ---------------------------------------------------------------------
362
363 FUNCTION check_to_install (p_legislation_code IN VARCHAR2,
364 p_install_tax_unit IN VARCHAR2) RETURN BOOLEAN IS
365
366 v_check_installation boolean := TRUE;
367 v_install_tax_unit varchar(2);
368
369 BEGIN
370 IF(NOT check_to_install(p_legislation_code)) THEN
371 v_check_installation := FALSE;
372 RETURN v_check_installation;
373 END IF;
374
375 BEGIN
376 SELECT rule_mode
377 INTO v_install_tax_unit
378 FROM pay_legislation_rules
379 WHERE legislation_code = p_legislation_code
380 AND rule_type = 'TAX_UNIT';
381
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 NULL;
385 END;
386
387 IF(v_install_tax_unit IS NOT NULL AND
388 p_install_tax_unit <> v_install_tax_unit) THEN
389 g_logging := 'Y';
390 write_log ('LOG','PAY_34081_IP_INSTALL_TAX_UNIT',NULL,NULL);
391 v_check_installation := FALSE;
392 RETURN v_check_installation;
393 END IF;
394
395 RETURN v_check_installation ;
396 END check_to_install;
397 -- ---------------------------------------------------------------------
398 -- Procedure to clear all HR_S tables
399 -- ---------------------------------------------------------------------
400 PROCEDURE clear_shadow_tables IS
401
402 BEGIN
403 hr_utility.set_location('pay_ip_startup_util.clear_shadow_table',10);
404 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FORMULA_TYPES',NULL);
405 DELETE hr_s_formula_types;
406 COMMIT;
407
408 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FTYPE_CONTEXT_USAGES',NULL);
409 DELETE hr_s_ftype_context_usages;
410 COMMIT;
411
412 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FORMULAS_F',NULL);
413 DELETE hr_s_formulas_f;
414 COMMIT;
415
416 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ROUTES',NULL);
417 DELETE hr_s_routes;
418 COMMIT;
419
420 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ROUTE_CONTEXT_USAGES',NULL);
421 DELETE hr_s_route_context_usages;
422 COMMIT;
423
424 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_CONTEXTS',NULL);
425 DELETE hr_s_contexts;
426 COMMIT;
427
428 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ROUTE_PARAMETERS',NULL);
429 DELETE hr_s_route_parameters;
430 COMMIT;
431
432 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_ENTITIES',NULL);
433 DELETE hr_s_user_entities;
434 COMMIT;
435
436 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_DATABASE_ITEMS',NULL);
437 DELETE hr_s_database_items;
438 COMMIT;
439
440 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ROUTE_PARAMETER_VALUES',NULL);
441 DELETE hr_s_route_parameter_values;
442 COMMIT;
443
444 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FUNCTIONS',NULL);
445 DELETE hr_s_functions;
446 COMMIT;
447
448 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FUNCTION_PARAMETERS',NULL);
449 DELETE hr_s_function_parameters;
450 COMMIT;
451
452 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FUNCTION_CONTEXT_USAGES',NULL);
453 DELETE hr_s_function_context_usages;
454 COMMIT;
455
456 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ASSIGNMENT_STATUS_TYPES',NULL);
457 DELETE hr_s_assignment_status_types;
458 COMMIT;
459
460 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELEMENT_CLASSIFICATIONS',NULL);
461 DELETE hr_s_element_classifications;
462 COMMIT;
463
464 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELEMENT_TYPES_F',NULL);
465 DELETE hr_s_element_types_f;
466 COMMIT;
467
468 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_INPUT_VALUES_F',NULL);
469 DELETE hr_s_input_values_f;
470 COMMIT;
471
472 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_STATUS_PROCESSING_RULES_F',NULL);
473 DELETE hr_s_status_processing_rules_f;
474 COMMIT;
475
476 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FORMULA_RESULT_RULES_F',NULL);
477 DELETE hr_s_formula_result_rules_f;
478 COMMIT;
479
480 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_SUB_CLASSN_RULES_F',NULL);
481 DELETE hr_s_sub_classn_rules_f;
482 COMMIT;
483 /*bug 10212578 Begin */
484
485 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BALANCE_CATEGORIES_F',NULL);
486 DELETE hr_s_balance_categories_f;
487 COMMIT;
488 /*bug 10212578 End */
489
490 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BALANCE_TYPES',NULL);
491 DELETE hr_s_balance_types;
492 COMMIT;
493
494 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BALANCE_CLASSIFICATIONS',NULL);
495 DELETE hr_s_balance_classifications;
496 COMMIT;
497
498 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_DEFINED_BALANCES',NULL);
499 DELETE hr_s_defined_balances;
500 COMMIT;
501
502 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BALANCE_FEEDS_F',NULL);
503 DELETE hr_s_balance_feeds_f;
504 COMMIT;
505
506 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BALANCE_DIMENSIONS',NULL);
507 DELETE hr_s_balance_dimensions;
508 COMMIT;
509
510 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_DIMENSION_ROUTES',NULL);
511 DELETE hr_s_dimension_routes;
512 COMMIT;
513
514 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELEMENT_SETS',NULL);
515 DELETE hr_s_element_sets;
516 COMMIT;
517
518 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELEMENT_TYPE_RULES',NULL);
519 DELETE hr_s_element_type_rules;
520 COMMIT;
521
522 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELE_CLASSN_RULES',NULL);
523 DELETE hr_s_ele_classn_rules;
524 COMMIT;
525
526 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_TABLES',NULL);
527 DELETE hr_s_user_tables;
528 COMMIT;
529
530 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_COLUMNS',NULL);
531 DELETE hr_s_user_columns;
532 COMMIT;
533
534 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_ROWS_F',NULL);
535 DELETE hr_s_user_rows_f;
536 COMMIT;
537
538 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_COLUMN_INSTANCES_F',NULL);
539 DELETE hr_s_user_column_instances_f;
540 COMMIT;
541
542 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_QP_REPORTS',NULL);
543 DELETE hr_s_qp_reports;
544 COMMIT;
545
546 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ORG_INFORMATION_TYPES',NULL);
547 DELETE hr_s_org_information_types;
548 COMMIT;
549
550 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ORG_INFO_TYPES_BY_CLASS',NULL);
551 DELETE hr_s_org_info_types_by_class;
552 COMMIT;
553
554 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ASSIGNMENT_INFO_TYPES',NULL);
555 DELETE hr_s_assignment_info_types;
556 COMMIT;
557
558 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_GLOBALS_F',NULL);
559 DELETE hr_s_globals_f;
560 COMMIT;
561
562 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_LEGISLATIVE_FIELD_INFO',NULL);
563 DELETE hr_s_legislative_field_info;
564 COMMIT;
565
566 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_LEGISLATION_SUBGROUPS',NULL);
567 DELETE hr_s_legislation_subgroups;
568 COMMIT;
569
570 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_APPLICATION_OWNERSHIPS',NULL);
571 DELETE hr_s_application_ownerships;
572 COMMIT;
573
574 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_PAYMENT_TYPES',NULL);
575 DELETE hr_s_payment_types;
576 COMMIT;
577
578 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BENEFIT_CLASSIFICATIONS',NULL);
579 DELETE hr_s_benefit_classifications;
580 COMMIT;
581
582 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_COBRA_QFYING_EVENTS_F',NULL);
583 DELETE hr_s_cobra_qfying_events_f;
584 COMMIT;
585
586 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_VALID_DEPENDENT_TYPES',NULL);
587 DELETE hr_s_valid_dependent_types;
588 COMMIT;
589
590 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_HISTORY',NULL);
591 DELETE hr_s_history;
592 COMMIT;
593
594 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_STATE_RULES',NULL);
595 DELETE hr_s_state_rules;
596 COMMIT;
597
598 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_TAXABILITY_RULES',NULL);
599 DELETE hr_s_taxability_rules;
600 COMMIT;
601
602 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_MONETARY_UNITS',NULL);
603 DELETE hr_s_monetary_units;
604 COMMIT;
605
606 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_WC_STATE_SURCHARGES',NULL);
607 DELETE hr_s_wc_state_surcharges;
608 COMMIT;
609
610 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_LEGISLATION_RULES',NULL);
611 DELETE hr_s_legislation_rules;
612 COMMIT;
613
614 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_TAXABILITY_RULES_DATES',NULL);
615 DELETE HR_S_TAXABILITY_RULES_DATES;
616 COMMIT;
617
618 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_MAGNETIC_RECORDS',NULL);
619 DELETE HR_S_MAGNETIC_RECORDS;
620 COMMIT;
621
622 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_MAGNETIC_BLOCKS',NULL);
623 DELETE HR_S_MAGNETIC_BLOCKS;
624 COMMIT;
625
626 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_REPORT_FORMAT_MAPPINGS_F',NULL);
627 DELETE HR_S_REPORT_FORMAT_MAPPINGS_F;
628 COMMIT;
629
630 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_CITY_TAX_INFO_F',NULL);
631 DELETE HR_S_US_CITY_TAX_INFO_F;
632 COMMIT;
633
634 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_COUNTY_TAX_INFO_F',NULL);
635 DELETE HR_S_US_COUNTY_TAX_INFO_F;
636 COMMIT;
637
638 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_STATE_TAX_INFO_F',NULL);
639 DELETE HR_S_US_STATE_TAX_INFO_F;
640 COMMIT;
641
642 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_FEDERAL_TAX_INFO_F',NULL);
643 DELETE HR_S_US_FEDERAL_TAX_INFO_F;
644 COMMIT;
645
646 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_GARN_EXEMPTION_RULES_F',NULL);
647 DELETE HR_S_US_GARN_EXEMPTION_RULES_F;
648 COMMIT;
649
650 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_GARN_LIMIT_RULES_F',NULL);
651 DELETE HR_S_US_GARN_LIMIT_RULES_F;
652 COMMIT;
653
654 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_GARN_FEE_RULES_F',NULL);
655 DELETE HR_S_US_GARN_FEE_RULES_F;
656 COMMIT;
657
658 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_REPORT_LOOKUPS',NULL);
659 DELETE HR_S_REPORT_LOOKUPS;
660 COMMIT;
661
662 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_REPORT_FORMAT_ITEMS_F',NULL);
663 DELETE HR_S_REPORT_FORMAT_ITEMS_F;
664 COMMIT;
665
666 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_STATE_RULES',NULL);
667 DELETE HR_S_STATE_RULES;
668 COMMIT;
669 hr_utility.set_location('pay_ip_startup_util.clear_shadow_table',20);
670 END clear_shadow_tables;
671
672
673 -- -----------------------------------------------------------------------
674 -- This procedure will move the data with dummy legislation to HR_S
675 -- tables from LIVE HRMS tables. The ownership for Elemnet Classifications
676 -- Balance types, Balance Dimensions, Routes arealso inserted.
677 -- -----------------------------------------------------------------------
678 PROCEDURE move_to_shadow_tables (p_legislation_code IN VARCHAR2,
679 p_install_tax_unit IN VARCHAR2) IS
680
681
682
683 --
684 CURSOR get_element_class_csr IS
685 SELECT
686 classification_id,business_group_id,legislation_code,classification_name,description,
687 legislation_subgroup,costable_flag,default_high_priority,default_low_priority,
688 default_priority,distributable_over_flag,non_payments_flag,costing_debit_or_credit,
689 parent_classification_id,create_by_default_flag,last_update_date,last_updated_by,
690 last_update_login,created_by,creation_date,balance_initialization_flag,object_version_number
691 FROM pay_element_classifications
692 WHERE nvl(legislation_code,'X') = 'ZZ'
693 AND business_group_id IS NULL;
694
695 CURSOR get_balance_type_csr IS
696 SELECT
697 balance_type_id, business_group_id, legislation_code, currency_code,
698 assignment_remuneration_flag, balance_name,balance_uom, NULL comments, jurisdiction_level,
699 legislation_subgroup, reporting_name, tax_type, attribute_category,
700 attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
701 attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14,
702 attribute15, attribute16, attribute17, attribute18, attribute19, attribute20,
703 last_update_date, last_updated_by, last_update_login, created_by, creation_date
704 FROM pay_balance_types
705 WHERE nvl(legislation_code,'X') = 'ZZ'
706 AND business_group_id IS NULL;
707
708 CURSOR get_defined_balances_csr IS
709 SELECT
710 d.defined_balance_id, d.business_group_id, d.legislation_code, d.balance_type_id,
711 d.balance_dimension_id, d.force_latest_balance_flag, d.legislation_subgroup,
712 d.last_update_date, d.last_updated_by, d.last_update_login, d.created_by, d.creation_date,
713 d.object_version_number, d.grossup_allowed_flag, b.balance_name bname
714 FROM pay_defined_balances d, pay_balance_types b
715 WHERE d.balance_type_id = b.balance_type_id
716 AND EXISTS (SELECT NULL FROM hr_s_balance_types b
717 WHERE d.balance_type_id = b.balance_type_id);
718
719
720 --Cursors to install route and dimension if install_tax_unit is true.
721
722 CURSOR get_balance_dimensions_csr IS
723 SELECT
724 balance_dimension_id, business_group_id, legislation_code, route_id,
725 database_item_suffix, dimension_name, dimension_type, description,
726 feed_checking_code, feed_checking_type, legislation_subgroup, payments_flag,
727 expiry_checking_code, expiry_checking_level, dimension_level, period_type,
728 save_run_balance_enabled, database_item_function
729 FROM pay_balance_dimensions
730 WHERE nvl(legislation_code,'X') = 'ZZ'
731 AND business_group_id IS NULL;
732
733
734 CURSOR get_routes_csr IS
735 SELECT
736 route_id, route_name, user_defined_flag, description, text, last_update_date,
737 last_updated_by, last_update_login, created_by, creation_date, optimizer_hint
738 FROM ff_routes a
739 WHERE EXISTS (SELECT NULL
740 FROM pay_balance_dimensions c
741 WHERE c.route_id = a.route_id
742 AND c.legislation_code = 'ZZ'
743 UNION
744 SELECT NULL
745 FROM pay_balance_dimensions pbd,
746 pay_dimension_routes pdr
747 WHERE pbd.legislation_code = 'ZZ'
748 and pdr.balance_dimension_id = pbd.balance_dimension_id
749 and pdr.route_id = a.route_id);
750
751
752 CURSOR get_balance_class_csr IS
753 SELECT
754 balance_classification_id, business_group_id, legislation_code, balance_type_id,
755 classification_id, scale, legislation_subgroup, last_update_date, last_updated_by,
756 last_update_login, created_by, creation_date, object_version_number
757 FROM pay_balance_classifications
758 WHERE nvl(legislation_code,'X') = 'ZZ'
759 AND business_group_id IS NULL;
760
761 /*bug 10212578 Begin */
762 CURSOR get_balance_cat_csr IS
763 SELECT
764 balance_category_id,category_name ,effective_start_date ,effective_end_date ,legislation_code ,
765 business_group_id ,save_run_balance_enabled ,pbc_information_category ,pbc_information1 ,pbc_information2 ,
766 pbc_information3 ,pbc_information4 ,pbc_information5 ,pbc_information6 ,pbc_information7 ,pbc_information8 ,
767 pbc_information9 ,pbc_information10 ,pbc_information11 ,pbc_information12 ,pbc_information13 ,pbc_information14 ,
768 pbc_information15 ,pbc_information16 ,pbc_information17 ,pbc_information18 ,pbc_information19 ,pbc_information20 ,
769 pbc_information21 ,pbc_information22 ,pbc_information23 ,pbc_information24 ,pbc_information25 ,pbc_information26 ,
770 pbc_information27 ,pbc_information28 ,pbc_information29 ,pbc_information30 ,last_update_date ,last_updated_by ,
771 last_update_login ,created_by ,creation_date ,object_version_number ,user_category_name
772 FROM pay_balance_categories_f
773 WHERE nvl(legislation_code,'X') = 'ZZ'
774 AND business_group_id IS NULL;
775
776 /*bug 10212578 End */
777 /* Ref Bug: 13362720 */
778 CURSOR c_get_report_format_mappings IS
779 SELECT report_type, report_qualifier, report_format, effective_start_date, effective_end_date, range_code,
780 assignment_action_code, initialization_code, archive_code, magnetic_code, report_category,
781 report_name, sort_code, updatable_flag, deinitialization_code, legislation_code, last_update_date,
782 last_updated_by, last_update_login, created_by, creation_date, temporary_action_flag
783 FROM pay_report_format_mappings_f
784 WHERE nvl(report_qualifier,'X') = 'ZZ'
785 AND business_group_id IS NULL;
786
787 CURSOR get_dimension_routes_csr IS
788 SELECT
789 pdr.balance_dimension_id ,pdr.route_id ,pdr.route_type ,pdr.priority ,pdr.run_dimension_id ,
790 pdr.balance_type_column ,pdr.decode_required ,pdr.last_update_date ,pdr.last_updated_by ,
791 pdr.last_update_login ,pdr.created_by ,pdr.creation_date ,pdr.object_version_number ,
792 pbd.dimension_name
793 FROM pay_dimension_routes pdr
794 ,pay_balance_dimensions pbd
795 WHERE nvl(pbd.legislation_code,'X') = 'ZZ'
796 AND pbd.business_group_id IS NULL
797 AND pbd.balance_dimension_id = pdr.balance_dimension_id;
798
799
800 BEGIN
801 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',10);
802 --Legislation Rules
803 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE','Legislation Rules', 'HR_S_LEGISLATION_RULES');
804
805 INSERT INTO hr_s_application_ownerships
806 (key_name
807 ,product_name
808 ,key_value)
809 SELECT ao.key_name
810 ,ao.product_name
811 ,ao.key_value
812 FROM hr_application_ownerships ao
813 ,pay_element_classifications pec
814 WHERE pec.legislation_code = 'ZZ'
815 AND ao.key_name = 'CLASSIFICATION_ID'
816 AND TO_NUMBER(ao.key_value) = pec.classification_id
817 UNION ALL
818 SELECT ao.key_name
819 ,ao.product_name
820 ,ao.key_value
821 FROM hr_application_ownerships ao
822 ,pay_balance_types pbt
823 WHERE pbt.legislation_code = 'ZZ'
824 AND ao.key_name = 'BALANCE_TYPE_ID'
825 AND TO_NUMBER(ao.key_value) = pbt.balance_type_id
826 UNION ALL
827 SELECT ao.key_name
828 ,ao.product_name
829 ,ao.key_value
830 FROM hr_application_ownerships ao
831 ,pay_balance_dimensions pbd
832 WHERE pbd.legislation_code ='ZZ'
833 AND ao.key_name = 'BALANCE_DIMENSION_ID'
834 AND TO_NUMBER(ao.key_value) = pbd.balance_dimension_id
835 UNION ALL
836 SELECT ao.key_name
837 ,ao.product_name
838 ,ao.key_value
839 FROM hr_application_ownerships ao
840 ,pay_defined_balances pdb
841 WHERE pdb.legislation_code ='ZZ'
842 AND ao.key_name = 'DEFINED_BALANCE_ID'
843 AND TO_NUMBER(ao.key_value) = pdb.defined_balance_id
844 UNION ALL
845 SELECT ao.key_name
846 ,ao.product_name
847 ,ao.key_value
848 FROM hr_application_ownerships ao
849 ,ff_routes fr
850 ,pay_balance_dimensions pbd
851 WHERE pbd.legislation_code ='ZZ'
852 AND ao.key_name = 'ROUTE_ID'
853 AND TO_NUMBER(ao.key_value) = fr.route_id
854 AND fr.route_id = pbd.route_id
855 UNION ALL
856 SELECT ao.key_name
857 ,ao.product_name
858 ,ao.key_value
859 FROM hr_application_ownerships ao
860 ,ff_routes fr
861 ,pay_balance_dimensions pbd
862 ,pay_dimension_routes pdr
863 WHERE pbd.legislation_code ='ZZ'
864 AND pdr.balance_dimension_id = pbd.balance_dimension_id
865 AND ao.key_name = 'ROUTE_ID'
866 AND TO_NUMBER(ao.key_value) = fr.route_id
867 AND fr.route_id = pdr.route_id
868 UNION ALL
869 SELECT ao.key_name
870 ,ao.product_name
871 ,ao.key_value
872 FROM hr_application_ownerships ao,
873 pay_balance_categories pbc
874 WHERE pbc.legislation_code ='ZZ'
875 AND ao.key_name = 'BALANCE_CATEGORY_ID'
876 AND TO_NUMBER(ao.key_value) = pbc.balance_category_id;
877
878
879 -- Element Classifictions
880
881 write_log('LOG',NULL,NULL,NULL);
882 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Element Classifications', 'HR_S_ELEMENT_CLASSIFICATIONS');
883
884 FOR rec IN get_element_class_csr LOOP
885 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Element Classification', rec.classification_name);
886
887 INSERT INTO hr_s_element_classifications
888 ( classification_id
889 ,business_group_id
890 ,legislation_code
891 ,classification_name
892 ,description
893 ,legislation_subgroup
894 ,costable_flag
895 ,default_high_priority
896 ,default_low_priority
897 ,default_priority
898 ,distributable_over_flag
899 ,non_payments_flag
900 ,costing_debit_or_credit
901 ,parent_classification_id
902 ,create_by_default_flag
903 ,last_update_date
904 ,last_updated_by
905 ,last_update_login
906 ,created_by
907 ,creation_date
908 ,balance_initialization_flag
909 ,object_version_number
910 )
911
912 VALUES
913 ( rec.classification_id
914 ,rec.business_group_id
915 ,rec.legislation_code
916 ,rec.classification_name
917 ,rec.description
918 ,rec.legislation_subgroup
919 ,rec.costable_flag
920 ,rec.default_high_priority
921 ,rec.default_low_priority
922 ,rec.default_priority
923 ,rec.distributable_over_flag
924 ,rec.non_payments_flag
925 ,rec.costing_debit_or_credit
926 ,rec.parent_classification_id
927 ,rec.create_by_default_flag
928 ,rec.last_update_date
929 ,rec.last_updated_by
930 ,rec.last_update_login
931 ,rec.created_by
932 ,rec.creation_date
933 ,rec.balance_initialization_flag
934 ,rec.object_version_number
935 );
936
937 ----write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Element Classification', rec.classification_name);
938 ----pay_ip_startup_util.insert_ownership('CLASSIFICATION_ID','PER',rec.classification_id);
939 ----pay_ip_startup_util.insert_ownership('CLASSIFICATION_ID','PAY',rec.classification_id);
940
941 END LOOP;
942
943 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables',20);
944 --Balance Types
945 write_log('LOG',NULL,NULL,NULL);
946 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Types', 'HR_S_BALANCE_TYPES');
947 FOR rec IN get_balance_type_csr LOOP
948 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Balance Types', rec.balance_name);
949
950 INSERT INTO hr_s_balance_types
951 ( balance_type_id
952 ,business_group_id
953 ,legislation_code
954 ,currency_code
955 ,assignment_remuneration_flag
956 ,balance_name
957 ,balance_uom
958 ,comments
959 ,jurisdiction_level
960 ,legislation_subgroup
961 ,reporting_name
962 ,tax_type
963 ,attribute_category
964 ,attribute1
965 ,attribute2
966 ,attribute3
967 ,attribute4
968 ,attribute5
969 ,attribute6
970 ,attribute7
971 ,attribute8
972 ,attribute9
973 ,attribute10
974 ,attribute11
975 ,attribute12
976 ,attribute13
977 ,attribute14
978 ,attribute15
979 ,attribute16
980 ,attribute17
981 ,attribute18
982 ,attribute19
983 ,attribute20
984 ,last_update_date
985 ,last_updated_by
986 ,last_update_login
987 ,created_by
988 ,creation_date
989 )
990
991 VALUES
992 ( rec.balance_type_id
993 ,rec.business_group_id
994 ,rec.legislation_code
995 ,rec.currency_code
996 ,rec.assignment_remuneration_flag
997 ,rec.balance_name
998 ,rec.balance_uom
999 ,rec.comments
1000 ,rec.jurisdiction_level
1001 ,rec.legislation_subgroup
1002 ,rec.reporting_name
1003 ,rec.tax_type
1004 ,rec.attribute_category
1005 ,rec.attribute1
1006 ,rec.attribute2
1007 ,rec.attribute3
1008 ,rec.attribute4
1009 ,rec.attribute5
1010 ,rec.attribute6
1011 ,rec.attribute7
1012 ,rec.attribute8
1013 ,rec.attribute9
1014 ,rec.attribute10
1015 ,rec.attribute11
1016 ,rec.attribute12
1017 ,rec.attribute13
1018 ,rec.attribute14
1019 ,rec.attribute15
1020 ,rec.attribute16
1021 ,rec.attribute17
1022 ,rec.attribute18
1023 ,rec.attribute19
1024 ,rec.attribute20
1025 ,rec.last_update_date
1026 ,rec.last_updated_by
1027 ,rec.last_update_login
1028 ,rec.created_by
1029 ,rec.creation_date
1030 );
1031
1032 ----write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Type', rec.balance_name);
1033 ----pay_ip_startup_util.insert_ownership('BALANCE_TYPE_ID','PER',rec.balance_type_id);
1034 --pay_ip_startup_util.insert_ownership('BALANCE_TYPE_ID','PAY',rec.balance_type_id);
1035 END LOOP;
1036
1037 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',30);
1038
1039 --Defined Balance
1040 write_log('LOG',NULL,NULL,NULL);
1041 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Defined Balances', 'HR_S_DEFINED_BALANCES');
1042 FOR rec IN get_defined_balances_csr LOOP
1043 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Defined Balances for Balance Type', rec.bname);
1044 INSERT INTO hr_s_defined_balances
1045 ( defined_balance_id
1046 ,business_group_id
1047 ,legislation_code
1048 ,balance_type_id
1049 ,balance_dimension_id
1050 ,force_latest_balance_flag
1051 ,legislation_subgroup
1052 ,last_update_date
1053 ,last_updated_by
1054 ,last_update_login
1055 ,created_by
1056 ,creation_date
1057 ,object_version_number
1058 ,grossup_allowed_flag
1059 )
1060 VALUES
1061 ( rec.defined_balance_id
1062 ,rec.business_group_id
1063 ,rec.legislation_code
1064 ,rec.balance_type_id
1065 ,rec.balance_dimension_id
1066 ,rec.force_latest_balance_flag
1067 ,rec.legislation_subgroup
1068 ,rec.last_update_date
1069 ,rec.last_updated_by
1070 ,rec.last_update_login
1071 ,rec.created_by
1072 ,rec.creation_date
1073 ,rec.object_version_number
1074 ,rec.grossup_allowed_flag
1075 );
1076
1077 END LOOP;
1078
1079 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',40);
1080
1081 --Balance Dimensions
1082
1083 write_log('LOG',NULL,NULL,NULL);
1084 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Dimensions', 'HR_S_BALANCE_DIMENSIONS');
1085 FOR rec IN get_balance_dimensions_csr LOOP
1086 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Balance Dimension', rec.dimension_name);
1087
1088 INSERT INTO hr_s_balance_dimensions
1089 ( balance_dimension_id
1090 ,business_group_id
1091 ,legislation_code
1092 ,route_id
1093 ,database_item_suffix
1094 ,dimension_name
1095 ,dimension_type
1096 ,description
1097 ,feed_checking_code
1098 ,feed_checking_type
1099 ,legislation_subgroup
1100 ,payments_flag
1101 ,expiry_checking_code
1102 ,expiry_checking_level
1103 ,dimension_level
1104 ,period_type
1105 ,save_run_balance_enabled
1106 ,database_item_function
1107 )
1108
1109 VALUES
1110 ( rec.balance_dimension_id
1111 ,rec.business_group_id
1112 ,rec.legislation_code
1113 ,rec.route_id
1114 ,rec.database_item_suffix
1115 ,rec.dimension_name
1116 ,rec.dimension_type
1117 ,rec.description
1118 ,rec.feed_checking_code
1119 ,rec.feed_checking_type
1120 ,rec.legislation_subgroup
1121 ,rec.payments_flag
1122 ,rec.expiry_checking_code
1123 ,rec.expiry_checking_level
1124 ,rec.dimension_level
1125 ,rec.period_type
1126 ,rec.save_run_balance_enabled
1127 ,rec.database_item_function
1128 );
1129
1130 ----write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Dimension', rec.dimension_name);
1131
1132 END LOOP;
1133
1134 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',50);
1135
1136 --Routes
1137
1138 write_log('LOG',NULL,NULL,NULL);
1139 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Routes', 'HR_S_ROUTES');
1140 FOR rec IN get_routes_csr LOOP
1141 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Route', rec.route_name);
1142
1143 INSERT INTO hr_s_routes
1144 ( route_id
1145 ,route_name
1146 ,user_defined_flag
1147 ,description
1148 ,text
1149 ,last_update_date
1150 ,last_updated_by
1151 ,last_update_login
1152 ,created_by
1153 ,creation_date
1154 ,optimizer_hint
1155 )
1156
1157 VALUES
1158 ( rec.route_id
1159 ,rec.route_name
1160 ,rec.user_defined_flag
1161 ,rec.description
1162 ,rec.text
1163 ,rec.last_update_date
1164 ,rec.last_updated_by
1165 ,rec.last_update_login
1166 ,rec.created_by
1167 ,rec.creation_date
1168 ,rec.optimizer_hint
1169 );
1170
1171 ----write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Route', rec.route_name);
1172
1173 END LOOP;
1174
1175 --Dimension Routes
1176
1177 write_log('LOG',NULL,NULL,NULL);
1178 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Dimension Routes', 'HR_S_DIMENSION_ROUTES');
1179 FOR rec IN get_dimension_routes_csr LOOP
1180
1181
1182 INSERT INTO hr_s_dimension_routes
1183 ( balance_dimension_id
1184 ,route_id
1185 ,route_type
1186 ,priority
1187 ,run_dimension_id
1188 ,balance_type_column
1189 ,decode_required
1190 ,last_update_date
1191 ,last_updated_by
1192 ,last_update_login
1193 ,created_by
1194 ,creation_date
1195 ,object_version_number
1196 )
1197
1198 VALUES
1199 ( rec.balance_dimension_id
1200 ,rec.route_id
1201 ,rec.route_type
1202 ,rec.priority
1203 ,rec.run_dimension_id
1204 ,rec.balance_type_column
1205 ,rec.decode_required
1206 ,rec.last_update_date
1207 ,rec.last_updated_by
1208 ,rec.last_update_login
1209 ,rec.created_by
1210 ,rec.creation_date
1211 ,rec.object_version_number
1212 );
1213
1214
1215 END LOOP;
1216
1217
1218 IF p_install_tax_unit = 'N' THEN
1219
1220 /* IF tax unit is not installed
1221 then update run dimension to _ASG_RUN
1222 */
1223 UPDATE hr_s_dimension_routes
1224 SET run_dimension_id =
1225 (
1226 SELECT balance_dimension_id
1227 FROM hr_s_balance_dimensions
1228 WHERE legislation_code = 'ZZ'
1229 AND database_item_suffix = '_ASG_RUN'
1230 );
1231
1232 DELETE FROM hr_s_application_ownerships
1233 WHERE key_name = 'BALANCE_DIMENSION_ID'
1234 AND TO_NUMBER(key_value) IN (SELECT balance_dimension_id
1235 FROM hr_s_balance_dimensions
1236 WHERE legislation_code = 'ZZ'
1237 AND INSTR(database_item_suffix,'_TU_') > 0);
1238
1239 /* Set the SRB flag only when run balance patch is applied*/
1240 IF pay_ip_utility.check_run_balance_enabled THEN
1241 /* With tax unit not installed update
1242 the SAVE_RUN_BALANCE_ENABLED flag
1243 of _ASG_RUN dimension to 'Y'
1244 */
1245 UPDATE hr_s_balance_dimensions
1246 SET save_run_balance_enabled = 'Y'
1247 WHERE legislation_code = 'ZZ'
1248 AND database_item_suffix = '_ASG_RUN';
1249 END IF;
1250
1251 END IF;
1252
1253
1254
1255 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',60);
1256 -- Route Parameters
1257 write_log('LOG',NULL,NULL,NULL);
1258 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Route Parameters', 'HR_S_ROUTE_PARAMETERS');
1259 INSERT INTO hr_s_route_parameters
1260 (SELECT
1261 route_parameter_id
1262 ,route_id
1263 ,data_type
1264 ,parameter_name
1265 ,sequence_no
1266 FROM ff_route_parameters a
1267 WHERE EXISTS ( SELECT NULL
1268 FROM hr_s_routes b
1269 WHERE b.route_id = a.route_id));
1270 write_log('LOG',NULL,NULL,NULL);
1271 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Route Context Usages', 'HR_S_ROUTE_CONTEXT_USAGES');
1272 INSERT INTO hr_s_route_context_usages
1273 (SELECT
1274 route_id
1275 ,context_id
1276 ,sequence_no
1277 FROM ff_route_context_usages a
1278 WHERE EXISTS ( SELECT NULL
1279 FROM hr_s_routes b
1280 WHERE b.route_id = a.route_id));
1281
1282 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',70);
1283
1284 -- Used column to column mapping in the insert statement to remove
1285 -- error caused by mismatch in number of fields. Bug No 3720975.
1286
1287 INSERT into hr_s_legislative_field_info
1288 (FIELD_NAME,
1289 LEGISLATION_CODE,
1290 PROMPT,
1291 VALIDATION_NAME,
1292 VALIDATION_TYPE,
1293 TARGET_LOCATION,
1294 RULE_TYPE,
1295 RULE_MODE)
1296 (SELECT
1297 FIELD_NAME,
1298 LEGISLATION_CODE,
1299 PROMPT,
1300 VALIDATION_NAME,
1301 VALIDATION_TYPE,
1302 TARGET_LOCATION,
1303 RULE_TYPE,
1304 RULE_MODE
1305 FROM pay_legislative_field_info
1306 WHERE nvl(legislation_code,'X') = 'ZZ');
1307
1308 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',80);
1309
1310 --Balance Classifications
1311
1312 write_log('LOG',NULL,NULL,NULL);
1313 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Classifications', 'HR_S_BALANCE_CLASSIFICATIONS');
1314 FOR rec IN get_balance_class_csr LOOP
1315 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Balance Classifications', rec.balance_classification_id);
1316
1317 INSERT INTO hr_s_balance_classifications
1318 ( balance_classification_id
1319 ,business_group_id
1320 ,legislation_code
1321 ,balance_type_id
1322 ,classification_id
1323 ,scale
1324 ,legislation_subgroup
1325 ,last_update_date
1326 ,last_updated_by
1327 ,last_update_login
1328 ,created_by
1329 ,creation_date
1330 ,object_version_number
1331 )
1332 VALUES
1333 ( rec.balance_classification_id
1334 ,rec.business_group_id
1335 ,rec.legislation_code
1336 ,rec.balance_type_id
1337 ,rec.classification_id
1338 ,rec.scale
1339 ,rec.legislation_subgroup
1340 ,rec.last_update_date
1341 ,rec.last_updated_by
1342 ,rec.last_update_login
1343 ,rec.created_by
1344 ,rec.creation_date
1345 ,rec.object_version_number
1346 );
1347
1348 ----write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Classifications', rec.balance_classification_id);
1349
1350 END LOOP;
1351
1352 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',90);
1353
1354 --Balance Classifications
1355
1356 write_log('LOG',NULL,NULL,NULL);
1357
1358 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Categories', 'HR_S_BALANCE_CATEGORIES_F');
1359 FOR rec IN get_balance_cat_csr LOOP
1360 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Balance Categories', rec.balance_category_id);
1361
1362 INSERT INTO hr_s_balance_categories_f
1363 ( BALANCE_CATEGORY_ID
1364 ,CATEGORY_NAME
1365 ,EFFECTIVE_START_DATE
1366 ,EFFECTIVE_END_DATE
1367 ,LEGISLATION_CODE
1368 ,BUSINESS_GROUP_ID
1369 ,SAVE_RUN_BALANCE_ENABLED
1370 ,PBC_INFORMATION_CATEGORY
1371 ,PBC_INFORMATION1
1372 ,PBC_INFORMATION2
1373 ,PBC_INFORMATION3
1374 ,PBC_INFORMATION4
1375 ,PBC_INFORMATION5
1376 ,PBC_INFORMATION6
1377 ,PBC_INFORMATION7
1378 ,PBC_INFORMATION8
1379 ,PBC_INFORMATION9
1380 ,PBC_INFORMATION10
1381 ,PBC_INFORMATION11
1382 ,PBC_INFORMATION12
1383 ,PBC_INFORMATION13
1384 ,PBC_INFORMATION14
1385 ,PBC_INFORMATION15
1386 ,PBC_INFORMATION16
1387 ,PBC_INFORMATION17
1388 ,PBC_INFORMATION18
1389 ,PBC_INFORMATION19
1390 ,PBC_INFORMATION20
1391 ,PBC_INFORMATION21
1392 ,PBC_INFORMATION22
1393 ,PBC_INFORMATION23
1394 ,PBC_INFORMATION24
1395 ,PBC_INFORMATION25
1396 ,PBC_INFORMATION26
1397 ,PBC_INFORMATION27
1398 ,PBC_INFORMATION28
1399 ,PBC_INFORMATION29
1400 ,PBC_INFORMATION30
1401 ,LAST_UPDATE_DATE
1402 ,LAST_UPDATED_BY
1403 ,LAST_UPDATE_LOGIN
1404 ,CREATED_BY
1405 ,CREATION_DATE
1406 ,OBJECT_VERSION_NUMBER
1407 ,USER_CATEGORY_NAME
1408 )
1409 VALUES
1410 ( rec.balance_category_id
1411 ,rec.category_name
1412 ,rec.effective_start_date
1413 ,rec.effective_end_date
1414 ,rec.legislation_code
1415 ,rec.business_group_id
1416 ,rec.save_run_balance_enabled
1417 ,rec.pbc_information_category
1418 ,rec.pbc_information1
1419 ,rec.pbc_information2
1420 ,rec.pbc_information3
1421 ,rec.pbc_information4
1422 ,rec.pbc_information5
1423 ,rec.pbc_information6
1424 ,rec.pbc_information7
1425 ,rec.pbc_information8
1426 ,rec.pbc_information9
1427 ,rec.pbc_information10
1428 ,rec.pbc_information11
1429 ,rec.pbc_information12
1430 ,rec.pbc_information13
1431 ,rec.pbc_information14
1432 ,rec.pbc_information15
1433 ,rec.pbc_information16
1434 ,rec.pbc_information17
1435 ,rec.pbc_information18
1436 ,rec.pbc_information19
1437 ,rec.pbc_information20
1438 ,rec.pbc_information21
1439 ,rec.pbc_information22
1440 ,rec.pbc_information23
1441 ,rec.pbc_information24
1442 ,rec.pbc_information25
1443 ,rec.pbc_information26
1444 ,rec.pbc_information27
1445 ,rec.pbc_information28
1446 ,rec.pbc_information29
1447 ,rec.pbc_information30
1448 ,rec.last_update_date
1449 ,rec.last_updated_by
1450 ,rec.last_update_login
1451 ,rec.created_by
1452 ,rec.creation_date
1453 ,rec.object_version_number
1454 ,rec.user_category_name
1455 );
1456
1457 ----write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Categories', rec.balance_category_id);
1458
1459 END LOOP;
1460 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',100);
1461
1462 /*Adding entries to pay_report_format_mappings*/
1463 FOR rec IN c_get_report_format_mappings LOOP
1464 fnd_file.put_line(fnd_file.LOG,'Moving report_format_mappings to shadow table');
1465 fnd_file.put_line(fnd_file.OUTPUT,'Moving report_format_mappings to shadow table');
1466
1467 INSERT INTO hr_s_report_format_mappings_f
1468 ( report_type ,
1469 report_qualifier ,
1470 report_format ,
1471 effective_start_date ,
1472 effective_end_date ,
1473 range_code ,
1474 assignment_action_code ,
1475 initialization_code ,
1476 archive_code ,
1477 magnetic_code ,
1478 report_category ,
1479 report_name ,
1480 sort_code ,
1481 updatable_flag ,
1482 deinitialization_code ,
1483 legislation_code ,
1484 last_update_date ,
1485 last_updated_by ,
1486 last_update_login ,
1487 created_by ,
1488 creation_date ,
1489 temporary_action_flag
1490 )
1491 VALUES
1492 ( rec.report_type ,
1493 p_legislation_code ,
1494 rec.report_format ,
1495 rec.effective_start_date ,
1496 rec.effective_end_date ,
1497 rec.range_code ,
1498 rec.assignment_action_code ,
1499 rec.initialization_code ,
1500 rec.archive_code ,
1501 rec.magnetic_code ,
1502 rec.report_category ,
1503 rec.report_name ,
1504 rec.sort_code ,
1505 rec.updatable_flag ,
1506 rec.deinitialization_code ,
1507 rec.legislation_code ,
1508 rec.last_update_date ,
1509 rec.last_updated_by ,
1510 rec.last_update_login ,
1511 rec.created_by ,
1512 rec.creation_date ,
1513 rec.temporary_action_flag
1514 );
1515
1516 END LOOP;
1517 fnd_file.put_line(fnd_file.LOG,'Finished moving report_format_mappings to shadow table');
1518 fnd_file.put_line(fnd_file.OUTPUT,'Finished moving report_format_mappings to shadow table');
1519
1520 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',90);
1521
1522 EXCEPTION
1523 WHEN OTHERS THEN
1524 IF get_element_class_csr%ISOPEN THEN
1525 CLOSE get_element_class_csr;
1526 END IF;
1527 IF get_balance_type_csr%ISOPEN THEN
1528 CLOSE get_balance_type_csr;
1529 END IF;
1530 IF get_defined_balances_csr%ISOPEN THEN
1531 CLOSE get_defined_balances_csr;
1532 END IF;
1533 IF get_balance_dimensions_csr%ISOPEN THEN
1534 CLOSE get_balance_dimensions_csr;
1535 END IF;
1536 IF get_routes_csr%ISOPEN THEN
1537 CLOSE get_routes_csr;
1538 END IF;
1539 IF get_balance_class_csr%ISOPEN THEN
1540 CLOSE get_balance_class_csr;
1541 END IF;
1542
1543 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1544
1545 END move_to_shadow_tables;
1546
1547 -- ---------------------------------------------------------------------
1548 -- Function to create Bank Key Flexfield , It will be created as
1549 -- " <legislation_code>_BANK_DETAILS "
1550 -- ---------------------------------------------------------------------
1551
1552 FUNCTION create_key_flexfield
1553 (p_appl_Short_Name IN VARCHAR2,
1554 p_flex_code IN VARCHAR2,
1555 p_structure_code IN VARCHAR2,
1556 p_structure_title IN VARCHAR2,
1557 p_description IN VARCHAR2,
1558 p_view_name IN VARCHAR2,
1559 p_freeze_flag IN VARCHAR2,
1560 p_enabled_flag IN VARCHAR2,
1561 p_cross_val_flag IN VARCHAR2,
1562 p_freeze_rollup_flag IN VARCHAR2,
1563 p_dynamic_insert_flag IN VARCHAR2,
1564 p_shorthand_enabled_flag IN VARCHAR2,
1565 p_shorthand_prompt IN VARCHAR2,
1566 p_shorthand_length IN NUMBER) RETURN NUMBER IS
1567
1568 l_flexfield fnd_flex_key_api.flexfield_type;
1569 l_structure fnd_flex_key_api.structure_type;
1570 l_application_id NUMBER(15);
1571 l_exists varchar2(1);
1572
1573
1574
1575 CURSOR duplicate_structure_check (p_application_id NUMBER,
1576 p_flexfield_code VARCHAR2,
1577 p_structure_title VARCHAR2) IS
1578 SELECT null
1579 FROM fnd_id_flex_structures_vl
1580 WHERE application_id = p_application_id
1581 AND id_flex_code = p_flexfield_code
1582 AND id_flex_structure_name = p_structure_title;
1583
1584
1585 BEGIN
1586
1587 SELECT application_id
1588 INTO l_application_id
1589 FROM FND_APPLICATION
1590 WHERE application_short_name = p_appl_Short_Name;
1591
1592 hr_utility.set_location('pay_ip_startup_util.create_key_flexfield ',10);
1593 fnd_flex_key_api.set_session_mode('seed_data');
1594
1595 l_flexfield := fnd_flex_key_api.find_flexfield
1596 ( appl_short_name => p_appl_short_name,
1597 flex_code => p_flex_code );
1598
1599 hr_utility.set_location('pay_ip_startup_util.create_key_flexfield ',20);
1600 BEGIN
1601
1602 l_structure := fnd_flex_key_api.find_structure
1603 ( flexfield => l_flexfield,
1604 structure_code => p_structure_code );
1605
1606 return l_structure.structure_number;
1607 hr_utility.set_location('pay_ip_startup_util.create_key_flexfield ',30);
1608 EXCEPTION
1609 WHEN NO_DATA_FOUND THEN
1610
1611 -- Bug 4544374. Check if the structure code exists with this title already.
1612 OPEN duplicate_structure_check(l_application_id, l_flexfield.flex_code, p_structure_title);
1613 FETCH duplicate_structure_check INTO l_exists;
1614
1615 IF duplicate_structure_check%FOUND then
1616 close duplicate_structure_check;
1617 fnd_message.set_name('PAY', 'PAY_34291_IP_BANK_STRUCT_EXIST');
1618 fnd_message.set_token('TITLE', p_structure_title);
1619 fnd_message.raise_error;
1620 END IF;
1621
1622 CLOSE duplicate_structure_check;
1623
1624 l_structure:=fnd_flex_key_api.new_structure
1625 (flexfield => l_flexfield,
1626 structure_code => p_structure_code,
1627 structure_title => p_structure_title,
1628 description => p_description,
1629 view_name => p_view_name,
1630 freeze_flag => p_freeze_flag,
1631 enabled_flag => p_enabled_flag,
1632 segment_separator => '.',
1633 cross_val_flag => p_cross_val_flag,
1634 freeze_rollup_flag => p_freeze_rollup_flag,
1635 dynamic_insert_flag => p_dynamic_insert_flag,
1636 shorthand_enabled_flag => p_shorthand_enabled_flag,
1637 shorthand_prompt => p_shorthand_prompt,
1638 shorthand_length => p_shorthand_length);
1639
1640 SELECT application_id
1641 INTO l_application_id
1642 FROM FND_APPLICATION
1643 WHERE application_short_name = p_appl_short_name;
1644
1645 SELECT NVL(MAX(ifs.id_flex_num),0) + 1
1646 INTO l_structure.structure_number
1647 FROM fnd_id_flex_structures ifs
1648 WHERE ifs.application_id = l_application_id
1649 AND ifs.id_flex_code = p_flex_code
1650 AND ifs.id_flex_num < 101;
1651
1652 fnd_flex_key_api.add_structure
1653 ( flexfield => l_flexfield,
1654 structure => l_structure );
1655
1656 RETURN l_structure.structure_number;
1657 END;
1658 hr_utility.set_location('pay_ip_startup_util.create_key_flexfield ',40);
1659 END create_key_flexfield;
1660
1661 -- ---------------------------------------------------------------------
1662 -- Procedure for creating the Flex field segments
1663 -- ---------------------------------------------------------------------
1664
1665 PROCEDURE create_flex_segments
1666 (p_appl_Short_Name IN VARCHAR2,
1667 p_flex_code IN VARCHAR2,
1668 p_structure_code IN VARCHAR2,
1669 p_segment_name IN VARCHAR2,
1670 p_column_name IN VARCHAR2,
1671 p_segment_number IN VARCHAR2,
1672 p_enabled_flag IN VARCHAR2,
1673 p_displayed_flag IN VARCHAR2,
1674 p_indexed_flag IN VARCHAR2,
1675 p_value_set IN VARCHAR2,
1676 p_display_size IN NUMBER,
1677 p_description_size IN NUMBER,
1678 p_concat_size IN NUMBER,
1679 p_lov_prompt IN VARCHAR2,
1680 p_window_prompt IN VARCHAR2
1681 ) IS
1682
1683 l_flexfield fnd_flex_key_api.flexfield_type;
1684 l_structure fnd_flex_key_api.structure_type;
1685 l_application_id NUMBER(15);
1686 l_flex_num NUMBER(15);
1687 l_segment fnd_flex_key_api.segment_type;
1688 BEGIN
1689
1690 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',10);
1691 fnd_flex_key_api.set_session_mode('seed_data');
1692 l_flexfield := fnd_flex_key_api.find_flexfield
1693 ( appl_short_name => p_appl_short_name,
1694 flex_code => p_flex_code );
1695
1696 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',11);
1697
1698 l_structure := fnd_flex_key_api.find_structure
1699 ( flexfield => l_flexfield,
1700 structure_code => p_structure_code );
1701
1702 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',12);
1703 begin
1704
1705 hr_utility.trace(p_segment_name);
1706 l_segment := fnd_flex_key_api.find_segment
1707 (
1708 flexfield => l_flexfield
1709 ,structure => l_structure
1710 ,segment_name => p_segment_name
1711 );
1712 exception
1713 when no_data_found then
1714 l_segment:= fnd_flex_key_api.new_segment
1715 (
1716 flexfield => l_flexfield
1717 ,structure => l_structure
1718 ,segment_name => p_segment_name
1719 ,description => null
1720 ,column_name => p_column_name
1721 ,segment_number => p_segment_number
1722 ,enabled_flag => p_enabled_flag
1723 ,displayed_flag => p_displayed_flag
1724 ,indexed_flag => p_indexed_flag
1725 ,value_set => p_value_set
1726 ,default_type => null
1727 ,default_value => null
1728 ,required_flag => 'N'
1729 ,security_flag => 'N'
1730 ,display_size => p_display_size
1731 ,description_size => p_description_size
1732 ,concat_size => p_concat_size
1733 ,lov_prompt => p_lov_prompt
1734 ,window_prompt => p_window_prompt
1735 );
1736
1737 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',13);
1738
1739 hr_utility.trace(p_segment_name);
1740 begin
1741 fnd_flex_key_api.add_segment
1742 (
1743 flexfield => l_flexfield
1744 ,structure => l_structure
1745 ,segment => l_segment
1746 );
1747 exception
1748 when others then
1749 hr_utility.trace(substr(fnd_flex_key_api.message,1,256));
1750 end;
1751 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',14);
1752 fnd_flex_key_api.assign_qualifier
1753 (
1754 flexfield => l_flexfield
1755 ,structure => l_structure
1756 ,segment => l_segment
1757 ,flexfield_qualifier => 'ASSIGNMENT'
1758 ,enable_flag => 'Y'
1759 );
1760
1761 end;
1762 END create_flex_segments;
1763
1764
1765
1766
1767 -- ---------------------------------------------------------------------
1768 -- Procedure for creating the rule for Bank key flexfield.
1769 -- ---------------------------------------------------------------------
1770 PROCEDURE create_leg_rule
1771 (p_legislation_code IN VARCHAR2,
1772 p_Rule_Type IN VARCHAR2,
1773 p_Rule_mode IN VARCHAR2) IS
1774
1775 BEGIN
1776 hr_utility.set_location('pay_ip_startup_util.create_flex_leg_rule ',10);
1777
1778 INSERT INTO hr_s_legislation_rules
1779 ( legislation_code
1780 ,rule_type
1781 ,rule_mode)
1782 SELECT
1783 'ZZ'
1784 ,p_rule_type
1785 ,p_rule_mode
1786 FROM dual
1787 WHERE NOT EXISTS (SELECT NULL
1788 FROM hr_s_legislation_rules
1789 WHERE legislation_code = 'ZZ'
1790 AND rule_type = p_rule_type);
1791 IF SQL%NOTFOUND THEN
1792 UPDATE hr_s_legislation_rules SET
1793 rule_mode = p_rule_mode
1794 WHERE legislation_code = 'ZZ'
1795 AND rule_type = p_rule_type;
1796 END IF;
1797 hr_utility.set_location('pay_ip_startup_util.create_flex_leg_rule ',20);
1798 END;
1799
1800 -- ---------------------------------------------------------------------
1801 -- HR_S Tables are updated to the choosen legislation_code and currency
1802 -- ---------------------------------------------------------------------
1803 PROCEDURE update_shadow_tables
1804 (p_legislation_code IN VARCHAR2,
1805 p_currency_code IN VARCHAR2) IS
1806
1807 BEGIN
1808 hr_utility.set_location('pay_ip_startup_util.update_shadow_tables ',10);
1809 --Updating Element Classifications Table
1810
1811 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_ELEMENT_CLASSIFICATIONS',NULL);
1812 UPDATE hr_s_element_classifications
1813 SET legislation_code = p_legislation_code
1814 WHERE legislation_code = 'ZZ';
1815
1816 --Updating Balance Types Table
1817
1818 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_BALANCE_TYPES',NULL);
1819 UPDATE hr_s_BALANCE_TYPES
1820 SET legislation_code = p_legislation_code,
1821 currency_code = p_currency_code
1822 WHERE legislation_code = 'ZZ';
1823
1824 --Updating Defined Balances Table
1825
1826 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_DEFINED_BALANCES',NULL);
1827 UPDATE hr_s_defined_balances
1828 SET legislation_code = p_legislation_code
1829 WHERE legislation_code = 'ZZ';
1830
1831
1832 --Updating Balance Dimensions Table
1833
1834 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_BALANCE_DIMENSIONS',NULL);
1835 UPDATE hr_s_balance_dimensions
1836 SET legislation_code = p_legislation_code
1837 WHERE legislation_code = 'ZZ';
1838
1839 --Updating Legislation Rules Table
1840
1841 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_LEGISLATION_RULES',NULL);
1842 UPDATE hr_s_legislation_rules
1843 SET legislation_code = p_legislation_code
1844 WHERE legislation_code = 'ZZ';
1845
1846 --Unpadting Legislative Field Info
1847
1848 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_LEGISLATIVE_FIELD_INFO',NULL);
1849 UPDATE hr_s_legislative_field_info
1850 SET legislation_code = p_legislation_code
1851 WHERE legislation_code = 'ZZ';
1852
1853 --Updating Balance Classifications
1854
1855 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_BALANCE_CLASSIFICATIONS',NULL);
1856 UPDATE hr_s_balance_classifications
1857 SET legislation_code = p_legislation_code
1858 WHERE legislation_code = 'ZZ';
1859
1860 --Updating Report Format Mapping
1861
1862 UPDATE hr_s_report_format_mappings_f
1863 SET report_qualifier = p_legislation_code
1864 WHERE report_qualifier = 'ZZ';
1865
1866 --Updating Balance Categories
1867 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_BALANCE_CATEGORIES_F',NULL);
1868 UPDATE hr_s_balance_categories_f
1869 SET legislation_code = p_legislation_code
1870 WHERE legislation_code = 'ZZ';
1871
1872 hr_utility.set_location('pay_ip_startup_util.update_shadow_tables ',20);
1873
1874 EXCEPTION
1875 WHEN OTHERS THEN
1876 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1877 END update_shadow_tables;
1878
1879 -- ---------------------------------------------------------------------
1880 -- A record for the choosen legislation rule is inserted in to History
1881 -- table as HR_LEGISLATION.INSTALL picks up the legislation_code from
1882 -- this history table
1883 -- ---------------------------------------------------------------------
1884 PROCEDURE insert_history_table
1885 (p_legislation_code IN VARCHAR2) IS
1886
1887 BEGIN
1888 hr_utility.set_location('pay_ip_startup_util.insert_history_table',10);
1889 INSERT INTO hr_s_history
1890 ( package_name
1891 ,date_of_export
1892 ,date_of_import
1893 ,status
1894 ,legislation_code)
1895 VALUES
1896 ( TO_CHAR(SYSDATE,'ddMonyyyy-hh:rr:ss') || '[' || p_legislation_code || ']'
1897 ,sysdate
1898 ,sysdate
1899 ,'HR_S tabes copied from reference account'
1900 ,p_legislation_code);
1901 hr_utility.set_location('pay_ip_startup_util.insert_history_table',20);
1902 END insert_history_table ;
1903
1904 PROCEDURE create_report_format_param(p_legislation_code IN VARCHAR2) IS
1905
1906 CURSOR c_report_format_mapping IS
1907 SELECT report_format_mapping_id
1908 FROM pay_report_format_mappings_f
1909 WHERE report_qualifier = p_legislation_code
1910 AND report_type = 'IP_PAYROLL_ARCHIVE'
1911 AND report_format = 'IP_PAYROLL_ARCHIVE'
1912 AND sysdate BETWEEN effective_start_date AND effective_end_date;
1913
1914 CURSOR c_report_format_parameters IS
1915 SELECT prfp.parameter_name, prfp.parameter_value
1916 FROM pay_report_format_parameters prfp, pay_report_format_mappings_f prfm
1917 WHERE prfp.report_format_mapping_id = prfm.report_format_mapping_id
1918 AND prfm.report_qualifier = 'ZZ'
1919 AND prfm.business_group_id IS NULL;
1920
1921 ln_report_mapping_id NUMBER;
1922 lv_legislation_code VARCHAR2(4);
1923
1924 BEGIN
1925 hr_utility.set_location('pay_ip_startup_util.create_report_format_param',10);
1926
1927 OPEN c_report_format_mapping;
1928 FETCH c_report_format_mapping INTO ln_report_mapping_id;
1929
1930 IF ln_report_mapping_id IS NULL THEN
1931
1932 SELECT PAY_REPORT_FORMAT_MAPPINGS_S.nextval
1933 INTO ln_report_mapping_id FROM dual;
1934
1935 UPDATE pay_report_format_mappings_f
1936 SET report_format_mapping_id = ln_report_mapping_id
1937 WHERE report_qualifier = p_legislation_code
1938 AND report_type = 'IP_PAYROLL_ARCHIVE'
1939 AND report_format = 'IP_PAYROLL_ARCHIVE'
1940 AND sysdate BETWEEN effective_start_date AND effective_end_date;
1941 END IF;
1942 CLOSE c_report_format_mapping;
1943
1944 FOR rec IN c_report_format_parameters
1945 LOOP
1946
1947 UPDATE pay_report_format_parameters
1948 SET parameter_value = rec.parameter_value
1949 WHERE report_format_mapping_id = ln_report_mapping_id
1950 AND parameter_name = rec.parameter_name;
1951
1952 IF SQL%ROWCOUNT = 0 THEN
1953 INSERT INTO pay_report_format_parameters
1954 (report_format_mapping_id, parameter_name, parameter_value)
1955 VALUES
1956 (ln_report_mapping_id, rec.parameter_name, rec.parameter_value);
1957 END IF;
1958
1959 END LOOP;
1960 hr_utility.set_location('pay_ip_startup_util.create_report_format_param',20);
1961
1962 END create_report_format_param;
1963
1964 -- ---------------------------------------------------------------------
1965 -- The data from Shadow tables are moved in to the main tables with the
1966 -- required legislation_code and currency, by HR_LEGISLATION.INSTALL
1967 -- ---------------------------------------------------------------------
1968 PROCEDURE move_to_main_tables IS
1969
1970 BEGIN
1971 hr_utility.set_location('pay_ip_startup_util.move_to_main_tables',10);
1972 hr_legislation.install;
1973
1974 EXCEPTION
1975 WHEN OTHERS THEN
1976 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1977
1978 hr_utility.set_location('pay_ip_startup_util.move_to_main_tables',20);
1979 END move_to_main_tables;
1980
1981 -- ---------------------------------------------------------------------
1982 -- Procedure to update the TL tables with the translated values.
1983 -- ---------------------------------------------------------------------
1984
1985 -- Updating Element Classifications TL Table
1986
1987 PROCEDURE update_ele_class_tl
1988 (p_legislation_code IN VARCHAR2) IS
1989
1990 CURSOR get_classid_btable_csr IS
1991 SELECT b.classification_id bid , b.classification_name bname,
1992 t.language, t.classification_name tname, t.description, t.source_lang
1993 FROM pay_element_classifications_tl t, pay_element_classifications b
1994 WHERE b.classification_id = t.classification_id
1995 AND b.legislation_code = 'ZZ'
1996 AND b.business_group_id IS NULL;
1997
1998
1999 CURSOR get_classid_tltable_csr(l_legislation_code VARCHAR2 , l_name VARCHAR2 , l_language VARCHAR2) IS
2000 SELECT t.classification_id tlid
2001 FROM pay_element_classifications_tl t, pay_element_classifications b
2002 WHERE b.classification_name = l_name
2003 AND b.legislation_code = l_legislation_code
2004 AND b.business_group_id is NULL
2005 AND t.classification_id = b.classification_id
2006 AND t.language = l_language;
2007
2008 rec_tltable_csr get_classid_tltable_csr%ROWTYPE;
2009
2010 BEGIN
2011
2012 hr_utility.set_location('pay_ip_startup_util.update_ele_class_tl',10);
2013
2014 FOR l_record in get_classid_btable_csr LOOP
2015
2016 OPEN get_classid_tltable_csr(p_legislation_code, l_record.bname, l_record.language);
2017 fetch get_classid_tltable_csr INTO rec_tltable_csr ;
2018
2019 if get_classid_tltable_csr%found then
2020
2021 UPDATE pay_element_classifications_tl
2022 SET classification_name = l_record.tname,
2023 description = l_record.description,
2024 source_lang = l_record.source_lang
2025 WHERE classification_id = rec_tltable_csr.tlid
2026 AND language = l_record.language;
2027
2028 end if;
2029
2030 CLOSE get_classid_tltable_csr;
2031
2032 END LOOP;
2033
2034 EXCEPTION
2035 WHEN OTHERS THEN
2036 IF get_classid_btable_csr%ISOPEN THEN
2037 CLOSE get_classid_btable_csr;
2038 END IF;
2039 IF get_classid_tltable_csr%ISOPEN THEN
2040 CLOSE get_classid_tltable_csr;
2041 END IF;
2042 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
2043 hr_utility.set_location('pay_ip_startup_util.update_ele_class_tl',20);
2044 END update_ele_class_tl;
2045
2046 PROCEDURE update_bal_categories_tl
2047 (p_legislation_code IN VARCHAR2) IS
2048
2049 CURSOR get_balcat_btable_csr IS
2050 SELECT b.balance_category_id bid , t.user_category_name tname,b.user_category_name bname,
2051 t.language, t.source_lang
2052 FROM pay_balance_categories_f_tl t, pay_balance_categories_f b
2053 WHERE b.balance_category_id = t.balance_category_id
2054 AND b.legislation_code = 'ZZ'
2055 AND b.business_group_id IS NULL;
2056
2057
2058 CURSOR get_balcat_tltable_csr(l_legislation_code VARCHAR2 , l_name VARCHAR2 , l_language VARCHAR2) IS
2059 SELECT t.balance_category_id tlid
2060 FROM pay_balance_categories_f b, pay_balance_categories_f_tl t
2061 WHERE b.user_category_name = l_name
2062 AND b.legislation_code = l_legislation_code
2063 AND b.business_group_id is NULL
2064 AND t.balance_category_id = b.balance_category_id
2065 AND t.language = l_language;
2066
2067 rec_tltable_csr get_balcat_tltable_csr%ROWTYPE;
2068
2069 BEGIN
2070
2071 hr_utility.set_location('pay_ip_startup_util.update_bal_categories_tl',10);
2072
2073 FOR l_record in get_balcat_btable_csr LOOP
2074
2075 OPEN get_balcat_tltable_csr(p_legislation_code, l_record.bname, l_record.language);
2076 fetch get_balcat_tltable_csr INTO rec_tltable_csr ;
2077
2078 if get_balcat_tltable_csr%found then
2079
2080 UPDATE pay_balance_categories_f_tl
2081 SET user_category_name = l_record.tname,
2082 source_lang = l_record.source_lang
2083 WHERE balance_category_id = rec_tltable_csr.tlid
2084 AND language = l_record.language;
2085
2086 end if;
2087
2088 CLOSE get_balcat_tltable_csr;
2089
2090 END LOOP;
2091
2092 EXCEPTION
2093 WHEN OTHERS THEN
2094 IF get_balcat_btable_csr%ISOPEN THEN
2095 CLOSE get_balcat_btable_csr;
2096 END IF;
2097 IF get_balcat_tltable_csr%ISOPEN THEN
2098 CLOSE get_balcat_tltable_csr;
2099 END IF;
2100 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
2101 hr_utility.set_location('pay_ip_startup_util.update_bal_categories_tl',20);
2102 END update_bal_categories_tl;
2103
2104 -- Updating Balance Types TL Table
2105
2106 PROCEDURE update_bal_type_tl
2107 (p_legislation_code IN VARCHAR2) IS
2108
2109 --old reference data ids
2110 CURSOR get_balid_btable_csr IS
2111 SELECT b.balance_type_id bid , b.balance_name bname ,
2112 t.language, t.balance_name tname, t.reporting_name, t.source_lang
2113 FROM pay_balance_types_tl t, pay_balance_types b
2114 WHERE t.balance_type_id = b.balance_type_id
2115 AND b.legislation_code = 'ZZ'
2116 AND b.business_group_id IS NULL ;
2117
2118 --ids for newly created data
2119 CURSOR get_balid_tltable_csr(l_legislation_code VARCHAR2, l_name VARCHAR2 , l_language VARCHAR2 ) IS
2120 SELECT t.balance_type_id tlid
2121 FROM pay_balance_types_tl t, pay_balance_types b
2122 WHERE b.balance_name = l_name
2123 AND b.legislation_code = l_legislation_code
2124 AND b.business_group_id IS NULL
2125 AND b.balance_type_id = t.balance_type_id
2126 AND t.language = l_language;
2127
2128 rec_tltable_csr get_balid_tltable_csr%ROWTYPE;
2129
2130 BEGIN
2131 hr_utility.set_location('pay_ip_startup_util.update_bal_type_tl',10);
2132
2133 FOR l_record in get_balid_btable_csr LOOP
2134
2135 OPEN get_balid_tltable_csr(p_legislation_code, l_record.bname, l_record.language);
2136 fetch get_balid_tltable_csr INTO rec_tltable_csr;
2137
2138 if get_balid_tltable_csr%found then
2139
2140 UPDATE pay_balance_types_tl
2141 SET balance_name = l_record.tname,
2142 reporting_name = l_record.reporting_name,
2143 source_lang = l_record.source_lang
2144 WHERE balance_type_id = rec_tltable_csr.tlid
2145 AND language = l_record.language;
2146
2147 end if;
2148
2149 CLOSE get_balid_tltable_csr;
2150
2151 END LOOP;
2152
2153 EXCEPTION
2154 WHEN OTHERS THEN
2155 IF get_balid_btable_csr%ISOPEN THEN
2156 CLOSE get_balid_btable_csr;
2157 END IF;
2158 IF get_balid_tltable_csr%ISOPEN THEN
2159 CLOSE get_balid_tltable_csr;
2160 END IF;
2161
2162 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
2163
2164 hr_utility.set_location('pay_ip_startup_util.update_bal_type_tl',20);
2165
2166 END update_bal_type_tl;
2167
2168 PROCEDURE create_runtype
2169 (p_legislation_code IN VARCHAR2) IS
2170
2171 CURSOR run_type_csr IS
2172 SELECT
2173 run_type_id, run_type_name, run_method, effective_start_date, effective_end_date,
2174 business_group_id, legislation_code, shortname, last_update_date, last_updated_by,
2175 last_update_login, created_by, creation_date, object_version_number
2176 FROM pay_run_types_f
2177 WHERE nvl(legislation_code,'X') = 'ZZ'
2178 AND business_group_id IS NULL and sysdate between effective_start_date and effective_end_date;
2179
2180
2181 Cursor run_type_parent_csr (l_legislation_code IN VARCHAR2) IS
2182 SELECT run_type_id parent_id
2183 FROM pay_run_types_f
2184 WHERE (run_type_name,shortname) IN (SELECT RUN_TYPE_NAME, shortname from pay_run_types_f
2185 WHERE RUN_TYPE_ID IN (SELECT PARENT_RUN_TYPE_ID
2186 FROM pay_run_type_usages_f
2187 WHERE LEGISLATION_CODE = 'ZZ'
2188 AND sysdate BETWEEN EFFECTIVE_START_DATE
2189 AND EFFECTIVE_END_DATE
2190 AND business_group_id is null
2191 )
2192 AND sysdate BETWEEN effective_start_date AND effective_end_date
2193 )
2194 AND sysdate BETWEEN effective_start_date
2195 AND effective_end_date
2196 AND legislation_code = l_legislation_code
2197 AND business_group_id is NULL;
2198
2199 Cursor run_type_child_csr (l_legislation_code IN VARCHAR2) IS
2200 SELECT prtf1.run_type_id child_id , prtuf.sequence sequence , prtf1.run_type_name run_type_name
2201 FROM pay_run_types_f prtf1, pay_run_types_f prtf2 , pay_run_type_usages_f prtuf
2202 WHERE (prtf1.run_type_name,prtf1.shortname) IN (SELECT RUN_TYPE_NAME, shortname
2203 FROM pay_run_types_f
2204 WHERE RUN_TYPE_ID IN (SELECT child_RUN_TYPE_ID
2205 FROM pay_run_type_usages_f
2206 WHERE parent_run_type_id in (SELECT distinct PARENT_RUN_TYPE_ID
2207 FROM pay_run_type_usages_f
2208 WHERE LEGISLATION_CODE = 'ZZ'
2209 AND sysdate BETWEEN EFFECTIVE_START_DATE
2210 AND EFFECTIVE_END_DATE
2211 AND business_group_id is null)
2212 AND legislation_code = 'ZZ'
2213 AND sysdate BETWEEN EFFECTIVE_START_DATE
2214 AND EFFECTIVE_END_DATE
2215 AND business_group_id is null
2216 )
2217 AND sysdate BETWEEN effective_start_date AND effective_end_date
2218 )
2219 AND sysdate BETWEEN prtf1.effective_start_date
2220 AND prtf1.effective_end_date
2221 AND prtf1.legislation_code = l_legislation_code
2222 AND prtf1.business_group_id is NULL
2223 AND prtuf.legislation_code = 'ZZ'
2224 AND prtf2.RUN_TYPE_NAME = prtf1.RUN_TYPE_NAME
2225 AND prtf2.SHORTNAME = prtf1.SHORTNAME
2226 AND prtf2.business_group_id is NULL
2227 AND prtuf.legislation_code = prtf2.legislation_code
2228 AND prtf2.run_type_id in (prtuf.parent_run_type_id, prtuf.child_run_type_id);
2229
2230
2231 --local variables for Run Types
2232
2233 l_rt_id pay_run_types_f.run_type_id%TYPE;
2234 l_rt_ovn pay_run_types_f.object_version_number%TYPE;
2235 l_rt_eff_start_date pay_run_types_f.effective_start_date%TYPE;
2236 l_rt_eff_end_date pay_run_types_f.effective_end_date%TYPE;
2237
2238 --local variables for Run Type Usages
2239
2240 l_rtu_id pay_run_type_usages_f.run_type_usage_id%TYPE;
2241 l_rtu_ovn pay_run_type_usages_f.object_version_number%TYPE;
2242 l_rtu_eff_start_date pay_run_type_usages_f.effective_start_date%TYPE;
2243 l_rtu_eff_end_date pay_run_type_usages_f.effective_end_date%TYPE;
2244 l_process VARCHAR2(100);
2245
2246
2247 BEGIN
2248 -- Run Types
2249
2250 hr_utility.set_location('pay_ip_startup_util.create_runtype',10);
2251
2252 write_log('LOG',NULL,NULL,NULL);
2253 write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_RUN_TYPE_API',NULL);
2254
2255 hr_startup_data_api_support.enable_startup_mode('STARTUP');
2256 hr_startup_data_api_support.create_owner_definition('PAY');
2257
2258 FOR rec IN run_type_csr LOOP
2259
2260 BEGIN
2261 SELECT run_type_id, object_version_number
2262 INTO l_rt_id, l_rt_ovn
2263 FROM pay_run_types_f
2264 WHERE run_type_name = rec.run_type_name
2265 AND shortname = rec.shortname
2266 AND legislation_code = p_legislation_code
2267 AND sysdate between effective_start_date and effective_end_date
2268 AND business_group_id IS NULL;
2269
2270
2271 EXCEPTION
2272 WHEN NO_DATA_FOUND THEN
2273 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Run Type ', rec.run_type_name);
2274 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Run Type ', rec.run_type_name);
2275 pay_run_type_api.create_run_type (
2276 p_effective_date => g_start_of_time
2277 ,p_run_type_name => rec.run_type_name
2278 ,p_run_method => rec.run_method
2279 ,p_business_group_id => NULL
2280 ,p_legislation_code => p_legislation_code
2281 ,p_shortname => rec.shortname
2282 ,p_run_type_id => l_rt_id
2283 ,p_effective_start_date => l_rt_eff_start_date
2284 ,p_effective_end_date => l_rt_eff_end_date
2285 ,p_object_version_number => l_rt_ovn
2286 ) ;
2287 END;
2288
2289 END LOOP;
2290
2291 -- Run Type Usages
2292
2293 hr_utility.set_location('pay_ip_startup_util.create_runtype',20);
2294
2295 write_log('LOG',NULL,NULL,NULL);
2296 write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_RUN_TYPE_USAGE_API',NULL);
2297
2298 FOR rec_parent IN run_type_parent_csr (p_legislation_code) LOOP
2299 FOR rec_child IN run_type_child_csr(p_legislation_code) LOOP
2300
2301
2302 BEGIN
2303 SELECT run_type_usage_id, object_version_number
2304 INTO l_rtu_id, l_rtu_ovn
2305 FROM pay_run_type_usages_f
2306 WHERE parent_run_type_id = rec_parent.parent_id
2307 AND child_run_type_id = rec_child.child_id
2308 AND legislation_code = p_legislation_code
2309 AND sysdate between effective_start_date and effective_end_date
2310 AND business_group_id IS NULL;
2311 l_process := 'Run Type Usage :' || rec_child.run_type_name;
2312 write_log('LOG','PAY_34015_IP_UPD_TABLE',l_process,NULL);
2313 pay_run_type_usage_api.update_run_type_usage (
2314 p_effective_date => g_start_of_time
2315 ,p_datetrack_update_mode => 'CORRECTION'
2316 ,p_run_type_usage_id => l_rtu_id
2317 ,p_object_version_number => l_rtu_ovn
2318 ,p_sequence => rec_child.sequence
2319 ,p_business_group_id => NULL
2320 ,p_legislation_code => p_legislation_code
2321 ,p_effective_start_date => l_rtu_eff_start_date
2322 ,p_effective_end_date => l_rtu_eff_end_date
2323 ) ;
2324 EXCEPTION
2325 WHEN NO_DATA_FOUND THEN
2326 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Run Type Usage', rec_child.run_type_name);
2327 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Run Type Usage', rec_child.run_type_name);
2328 pay_run_type_usage_api.create_run_type_usage (
2329 p_effective_date => g_start_of_time
2330 ,p_parent_run_type_id => rec_parent.parent_id
2331 ,p_child_run_type_id => rec_child.child_id
2332 ,p_sequence => rec_child.sequence
2333 ,p_business_group_id => NULL
2334 ,p_legislation_code => p_legislation_code
2335 ,p_run_type_usage_id => l_rtu_id
2336 ,p_effective_start_date => l_rtu_eff_start_date
2337 ,p_effective_end_date => l_rtu_eff_end_date
2338 ,p_object_version_number => l_rtu_ovn
2339 );
2340 END;
2341
2342 END LOOP; --child
2343 END LOOP; --parent
2344
2345 hr_utility.set_location('pay_ip_startup_util.create_runtype',30);
2346
2347 EXCEPTION
2348 WHEN OTHERS THEN
2349 IF run_type_csr%ISOPEN THEN
2350 CLOSE run_type_csr;
2351 END IF;
2352
2353 IF run_type_parent_csr%ISOPEN THEN
2354 CLOSE run_type_parent_csr;
2355 END IF;
2356
2357 IF run_type_child_csr%ISOPEN THEN
2358 CLOSE run_type_child_csr;
2359 END IF;
2360
2361 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
2362 END create_runtype;
2363 --
2364
2365 -- Bug 4159036. Create Balance Attribute Definitions
2366
2367 PROCEDURE create_bal_att_def
2368 (p_legislation_code in varchar2) IS
2369 --
2370 CURSOR get_bal_att_def IS
2371 SELECT
2372 attribute_name, alterable, user_attribute_name
2373 FROM pay_bal_attribute_definitions
2374 WHERE nvl(legislation_code,'X') = 'ZZ'
2375 AND business_group_id IS NULL;
2376 --
2377 BEGIN
2378 --
2379 hr_utility.set_location('pay_ip_startup_util.create_bal_att_def',10);
2380
2381 write_log('LOG',NULL,NULL,NULL);
2382 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Attribute Definitions', 'PAY_BAL_ATTRIBUTE_DEFINITIONS');
2383
2384 FOR rec IN get_bal_att_def LOOP
2385
2386 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Balance Attribute Definition', rec.attribute_name);
2387
2388 PAY_BALANCES_UPLOAD_PKG.PAY_BAL_ADE_LOAD_ROW
2389 (
2390 p_ATTRIBUTE_NAME => rec.attribute_name
2391 ,p_LEGISLATION_CODE => p_legislation_code
2392 ,p_BUSINESS_GROUP_NAME => null
2393 ,p_ALTERABLE => rec.alterable
2394 ,p_user_attribute_name => rec.user_attribute_name
2395 ,p_OWNER => 'SEED'
2396 );
2397
2398 END LOOP;
2399
2400 hr_utility.set_location('pay_ip_startup_util.create_bal_att_def',20);
2401 --
2402 EXCEPTION
2403 when others then
2404 if get_bal_att_def%isopen then
2405 close get_bal_att_def;
2406 end if;
2407
2408 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
2409 --
2410 END create_bal_att_def;
2411 --
2412
2413 -- Updating Run Types TL Table
2414
2415 PROCEDURE update_run_type_tl
2416 (p_legislation_code IN VARCHAR2) IS
2417
2418 CURSOR get_runid_btable_csr IS
2419 SELECT b.run_type_id bid , b.run_type_name bname,
2420 t.language, t.run_type_name tname, t.shortname, t.source_lang
2421 FROM pay_run_types_f_tl t, pay_run_types_f b
2422 WHERE t.run_type_id = b.run_type_id
2423 AND b.legislation_code = 'ZZ'
2424 AND b.business_group_id IS NULL
2425 AND sysdate BETWEEN b.effective_start_date AND b.effective_end_date ;
2426
2427 CURSOR get_runid_tltable_csr(l_legislation_code VARCHAR2, l_name VARCHAR2, l_language VARCHAR2) IS
2428 SELECT t.run_type_id tlid
2429 FROM pay_run_types_f_tl t, pay_run_types_f b
2430 WHERE b.run_type_name = l_name
2431 AND b.legislation_code = l_legislation_code
2432 AND b.business_group_id IS NULL
2433 AND t.run_type_id = b.run_type_id
2434 AND t.language = l_language ;
2435
2436 rec_tltable_csr get_runid_tltable_csr%ROWTYPE;
2437
2438 BEGIN
2439
2440 hr_utility.set_location('pay_ip_startup_util.update_run_type_tl',10);
2441
2442 FOR l_record in get_runid_btable_csr LOOP
2443
2444 OPEN get_runid_tltable_csr(p_legislation_code, l_record.bname, l_record.language) ;
2445 fetch get_runid_tltable_csr INTO rec_tltable_csr ;
2446
2447 if get_runid_tltable_csr%found then
2448
2449 UPDATE pay_run_types_f_tl
2450 SET run_type_name = l_record.tname,
2451 shortname = l_record.shortname,
2452 source_lang = l_record.source_lang
2453 WHERE run_type_id = rec_tltable_csr.tlid
2454 AND language = l_record.language;
2455
2456 end if;
2457
2458 CLOSE get_runid_tltable_csr;
2459
2460 END LOOP;
2461
2462 hr_utility.set_location('pay_ip_startup_util.update_run_type_tl',20);
2463 EXCEPTION
2464 WHEN OTHERS THEN
2465 IF get_runid_btable_csr%ISOPEN THEN
2466 CLOSE get_runid_btable_csr;
2467 END IF;
2468 IF get_runid_tltable_csr%ISOPEN THEN
2469 CLOSE get_runid_tltable_csr;
2470 END IF;
2471 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
2472
2473 END update_run_type_tl;
2474
2475 --
2476 --------------------------------------------------------------------------
2477 -- Function to create retro definitions
2478 -- If the retro shortname already exists for this legislation
2479 -- then it will not be inserted or updated.
2480 --------------------------------------------------------------------------
2481 FUNCTION create_retro_definitions
2482 (p_legislation_code in varchar2
2483 ,p_short_name in pay_retro_definitions.short_name%TYPE
2484 ,p_definition_name in pay_retro_definitions.definition_name%TYPE)
2485 RETURN NUMBER
2486 IS
2487 --
2488 l_retro_definition_id pay_retro_definitions.retro_definition_id%TYPE;
2489 --
2490 CURSOR csr_defn_exists
2491 IS
2492 SELECT retro_definition_id
2493 FROM pay_retro_definitions
2494 WHERE short_name = p_short_name
2495 AND legislation_code = p_legislation_code;
2496 --
2497 CURSOR csr_get_defn_id
2498 IS
2499 SELECT pay_retro_definitions_s.nextval
2500 FROM dual;
2501 --
2502 BEGIN
2503 --
2504 OPEN csr_defn_exists;
2505 FETCH csr_defn_exists INTO l_retro_definition_id;
2506 CLOSE csr_defn_exists;
2507 --
2508 IF l_retro_definition_id IS NULL THEN
2509 --
2510 OPEN csr_get_defn_id;
2511 FETCH csr_get_defn_id INTO l_retro_definition_id;
2512 CLOSE csr_get_defn_id;
2513 --
2514 INSERT INTO pay_retro_definitions
2515 (retro_definition_id
2516 ,short_name
2517 ,definition_name
2518 ,legislation_code)
2519 VALUES
2520 (l_retro_definition_id
2521 ,p_short_name
2522 ,p_definition_name
2523 ,p_legislation_code);
2524 --
2525 END IF;
2526 --
2527 RETURN l_retro_definition_id;
2528 --
2529 EXCEPTION
2530 WHEN OTHERS THEN
2531 hr_utility.trace('Error: ' || sqlerrm);
2532 rollback;
2533 hr_utility.raise_error;
2534 END create_retro_definitions;
2535
2536 --------------------------------------------------------------------------
2537 -- Function to create retro components
2538 -- If the component shortname already exists for this legislation
2539 -- then the details will be updated.
2540 -- else a new retro component will be created.
2541 --------------------------------------------------------------------------
2542 FUNCTION create_retro_components
2543 (p_legislation_code VARCHAR2
2544 ,p_short_name in pay_retro_components.short_name%TYPE
2545 ,p_component_name in pay_retro_components.component_name%TYPE
2546 ,p_retro_type in pay_retro_components.retro_type%TYPE
2547 ,p_recalc_style in pay_retro_components.recalculation_style%TYPE
2548 ,p_date_override_proc in pay_retro_components.date_override_procedure%TYPE
2549 )
2550 RETURN NUMBER
2551 IS
2552 --
2553 l_retro_component_id pay_retro_components.retro_component_id%TYPE;
2554 --
2555 CURSOR csr_component_exists
2556 IS
2557 SELECT retro_component_id
2558 FROM pay_retro_components
2559 WHERE short_name = p_short_name
2560 AND legislation_code = p_legislation_code;
2561 --
2562 CURSOR csr_get_comp_id
2563 IS
2564 SELECT pay_retro_components_s.nextval
2565 FROM dual;
2566 --
2567 BEGIN
2568 --
2569 OPEN csr_component_exists;
2570 FETCH csr_component_exists INTO l_retro_component_id;
2571 CLOSE csr_component_exists;
2572 --
2573 IF l_retro_component_id IS NULL THEN
2574 --
2575 OPEN csr_get_comp_id;
2576 FETCH csr_get_comp_id INTO l_retro_component_id;
2577 CLOSE csr_get_comp_id;
2578 --
2579 INSERT INTO pay_retro_components
2580 (retro_component_id
2581 ,short_name
2582 ,component_name
2583 ,retro_type
2584 ,legislation_code
2585 ,recalculation_style
2586 ,date_override_procedure)
2587 VALUES
2588 (l_retro_component_id
2589 ,p_short_name
2590 ,p_component_name
2591 ,p_retro_type
2592 ,p_legislation_code
2593 ,p_recalc_style
2594 ,p_date_override_proc);
2595 --
2596 ELSE
2597 --
2598 UPDATE pay_retro_components
2599 SET component_name = p_component_name
2600 , retro_type = p_retro_type
2601 , recalculation_style = p_recalc_style
2602 , date_override_procedure = p_date_override_proc
2603 WHERE retro_component_id = l_retro_component_id;
2604 --
2605 END IF;
2606 --
2607 RETURN l_retro_component_id;
2608 --
2609 EXCEPTION
2610 WHEN OTHERS THEN
2611 hr_utility.trace('Error: ' || sqlerrm);
2612 rollback;
2613 hr_utility.raise_error;
2614 END create_retro_components;
2615 --
2616 --------------------------------------------------------------------------
2617 -- Function to create retro definition components
2618 -- If the definition and component combination already exists
2619 -- then the priority will be updated.
2620 -- else a new retro definition component will be created.
2621 --------------------------------------------------------------------------
2622 FUNCTION create_retro_defn_components
2623 (p_retro_definition_id pay_retro_defn_components.retro_definition_id%TYPE
2624 ,p_retro_component_id pay_retro_defn_components.retro_component_id%TYPE
2625 ,p_priority in pay_retro_defn_components.priority%TYPE)
2626 RETURN NUMBER
2627 IS
2628 --
2629 l_definition_component_id pay_retro_defn_components.definition_component_id%TYPE;
2630 --
2631 CURSOR csr_defn_comp_exists
2632 IS
2633 SELECT definition_component_id
2634 FROM pay_retro_defn_components
2635 WHERE retro_definition_id = p_retro_definition_id
2636 AND retro_component_id = p_retro_component_id;
2637 --
2638 CURSOR csr_get_defn_comp_id IS
2639 SELECT pay_retro_defn_components_s.nextval
2640 from dual;
2641 --
2642 BEGIN
2643 --
2644 OPEN csr_defn_comp_exists;
2645 FETCH csr_defn_comp_exists INTO l_definition_component_id;
2646 CLOSE csr_defn_comp_exists;
2647 --
2648 IF l_definition_component_id IS NULL THEN
2649 --
2650 OPEN csr_get_defn_comp_id;
2651 FETCH csr_get_defn_comp_id INTO l_definition_component_id;
2652 CLOSE csr_get_defn_comp_id;
2653 --
2654 INSERT INTO pay_retro_defn_components
2655 (definition_component_id
2656 ,retro_definition_id
2657 ,retro_component_id
2658 ,priority)
2659 VALUES
2660 (l_definition_component_id
2661 ,p_retro_definition_id
2662 ,p_retro_component_id
2663 ,p_priority);
2664 --
2665 ELSE
2666 --
2667 UPDATE pay_retro_defn_components
2668 SET priority = p_priority
2669 WHERE definition_component_id = l_definition_component_id
2670 AND retro_definition_id = p_retro_definition_id
2671 AND retro_component_id = p_retro_component_id;
2672 --
2673 END IF;
2674 --
2675 RETURN l_definition_component_id;
2676 --
2677 EXCEPTION
2678 WHEN OTHERS THEN
2679 hr_utility.trace('Error: ' || sqlerrm);
2680 rollback;
2681 hr_utility.raise_error;
2682 END create_retro_defn_components;
2683 --
2684 --------------------------------------------------------------------------
2685 -- Function to create time definitions
2686 -- If the short_name and period_type combination already exists
2687 -- then the other fields will be updated.
2688 -- else a new time definition will be created.
2689 --------------------------------------------------------------------------
2690 FUNCTION create_time_definitions
2691 (p_legislation_code VARCHAR2
2692 ,p_short_name pay_time_definitions.short_name%TYPE
2693 ,p_definition_name pay_time_definitions.definition_name%TYPE
2694 ,p_period_type pay_time_definitions.period_type%TYPE
2695 ,p_period_unit pay_time_definitions.period_unit%TYPE
2696 ,p_day_adjustment pay_time_definitions.day_adjustment%TYPE
2697 ,p_dynamic_code pay_time_definitions.dynamic_code%TYPE)
2698 RETURN NUMBER
2699 IS
2700 --
2701 l_time_definition_id pay_time_definitions.time_definition_id%TYPE;
2702 --
2703 CURSOR csr_time_definition_exists
2704 IS
2705 SELECT time_definition_id
2706 FROM pay_time_definitions
2707 WHERE short_name = p_short_name
2708 AND period_type = p_period_type
2709 AND legislation_code = p_legislation_code;
2710 --
2711 CURSOR csr_get_time_definition
2712 IS
2713 SELECT pay_time_definitions_s.nextval
2714 from dual;
2715 --
2716 BEGIN
2717 --
2718 OPEN csr_time_definition_exists;
2719 FETCH csr_time_definition_exists INTO l_time_definition_id;
2720 CLOSE csr_time_definition_exists;
2721 --
2722 IF l_time_definition_id IS NULL THEN
2723 --
2724 OPEN csr_get_time_definition;
2725 FETCH csr_get_time_definition INTO l_time_definition_id;
2726 CLOSE csr_get_time_definition;
2727 --
2728 INSERT INTO pay_time_definitions
2729 (time_definition_id
2730 ,short_name
2731 ,definition_name
2732 ,period_type
2733 ,period_unit
2734 ,day_adjustment
2735 ,dynamic_code
2736 ,business_group_id
2737 ,legislation_code)
2738 VALUES
2739 (l_time_definition_id
2740 ,p_short_name
2741 ,p_definition_name
2742 ,p_period_type
2743 ,p_period_unit
2744 ,p_day_adjustment
2745 ,p_dynamic_code
2746 ,null
2747 ,p_legislation_code);
2748 --
2749 ELSE
2750 --
2751 UPDATE pay_time_definitions
2752 SET definition_name = p_definition_name
2753 , period_unit = p_period_unit
2754 , day_adjustment = p_day_adjustment
2755 , dynamic_code = p_dynamic_code
2756 WHERE time_definition_id = l_time_definition_id;
2757 --
2758 END IF;
2759 --
2760 RETURN l_time_definition_id;
2761 --
2762 EXCEPTION
2763 WHEN OTHERS THEN
2764 hr_utility.trace('Error: ' || sqlerrm);
2765 rollback;
2766 hr_utility.raise_error;
2767 END create_time_definitions;
2768 --------------------------------------------------------------------------
2769 -- Function to create time spans
2770 -- If the creator_id and creator_type combination already exists
2771 -- then the start and end time definition ids will be updated.
2772 -- else a new time span will be created.
2773 --------------------------------------------------------------------------
2774 FUNCTION create_time_spans
2775 (p_creator_id pay_time_spans.creator_id%TYPE
2776 ,p_creator_type pay_time_spans.creator_type%TYPE
2777 ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2778 ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2779 RETURN NUMBER
2780 IS
2781 --
2782 l_time_span_id pay_time_spans.time_span_id%TYPE;
2783 --
2784 CURSOR csr_time_span_exists
2785 IS
2786 SELECT time_span_id
2787 FROM pay_time_spans
2788 WHERE creator_id = p_creator_id
2789 AND creator_type = p_creator_type
2790 AND start_time_def_id = p_start_time_def_id
2791 AND end_time_def_id = p_end_time_def_id;
2792
2793 CURSOR csr_get_time_span
2794 IS
2795 select pay_time_spans_s.nextval
2796 from dual;
2797 --
2798 BEGIN
2799 --
2800 OPEN csr_time_span_exists;
2801 FETCH csr_time_span_exists INTO l_time_span_id;
2802 CLOSE csr_time_span_exists;
2803 --
2804 IF l_time_span_id IS NULL THEN
2805 --
2806 open csr_get_time_span;
2807 fetch csr_get_time_span into l_time_span_id;
2808 close csr_get_time_span;
2809
2810 INSERT INTO pay_time_spans
2811 (time_span_id
2812 ,creator_id
2813 ,creator_type
2814 ,start_time_def_id
2815 ,end_time_def_id)
2816 VALUES(l_time_span_id
2817 , p_creator_id
2818 , p_creator_type
2819 , p_start_time_def_id
2820 , p_end_time_def_id);
2821 --
2822 ELSE
2823 --
2824 UPDATE pay_time_spans
2825 SET start_time_def_id = p_start_time_def_id
2826 , end_time_def_id = p_end_time_def_id
2827 WHERE time_span_id = l_time_span_id;
2828 --
2829 END IF;
2830
2831 RETURN l_time_span_id;
2832 --
2833 EXCEPTION
2834 WHEN OTHERS THEN
2835 hr_utility.trace('Error: While inserting time spans : ' || sqlerrm);
2836 rollback;
2837 hr_utility.raise_error;
2838 --
2839 END create_time_spans;
2840
2841 -- Creating Retro definition,components and time spans
2842
2843 PROCEDURE create_enh_retro_setup
2844 (p_legislation_code IN VARCHAR2) IS
2845
2846 l_retro_defn_id pay_retro_definitions.retro_definition_id%TYPE;
2847 l_corr_up_comp pay_retro_components.retro_component_id%TYPE;
2848 l_back_up_comp pay_retro_components.retro_component_id%TYPE;
2849 l_db_lumpsum_comp pay_retro_components.retro_component_id%TYPE;
2850 l_defn_comp_id pay_retro_defn_components.definition_component_id%TYPE;
2851 l_start_time_id pay_time_definitions.time_definition_id%TYPE;
2852 l_end_time_id pay_time_definitions.time_definition_id%TYPE;
2853 l_time_span_id pay_time_spans.time_span_id%TYPE;
2854
2855 Begin
2856 --
2857 -- Insert a new retro definition for the new localisation
2858 ----------------------------------------------------------
2859 --
2860 l_retro_defn_id := create_retro_definitions
2861 (p_legislation_code => p_legislation_code
2862 ,p_short_name => p_legislation_code||'_RETROPAY'
2863 ,p_definition_name => 'Retropay ('||p_legislation_code||')');
2864
2865 --
2866 -- Populate the retro components table with the
2867 -- components required for the new localisation
2868 -----------------------------------------------
2869 --
2870 l_back_up_comp := create_retro_components
2871 (p_legislation_code => p_legislation_code
2872 ,p_short_name => p_legislation_code||'_BACKDATES'
2873 ,p_component_name => 'Backdated Changes'
2874 ,p_retro_type => 'F'
2875 ,p_recalc_style => null
2876 ,p_date_override_proc => null);
2877 --
2878 --
2879 -- Populate retro_defn_components for the components
2880 -- required for the new localisation
2881 ----------------------------------------------------
2882 --
2883 l_defn_comp_id := create_retro_defn_components
2884 (l_retro_defn_id
2885 ,l_back_up_comp
2886 ,20);
2887 --
2888
2889 /*
2890 Insert new time definitions and time spans required for the new localisation
2891 */
2892 l_start_time_id := create_time_definitions
2893 (p_legislation_code => p_legislation_code
2894 ,p_short_name => 'START_OF_TIME'
2895 ,p_definition_name => 'Start of Time'
2896 ,p_period_type => 'START_OF_TIME'
2897 ,p_period_unit => '0'
2898 ,p_day_adjustment => 'CURRENT'
2899 ,p_dynamic_code => null);
2900
2901 l_end_time_id := create_time_definitions
2902 (p_legislation_code => p_legislation_code
2903 ,p_short_name => 'END_OF_TIME'
2904 ,p_definition_name => 'End of Time'
2905 ,p_period_type => 'END_OF_TIME'
2906 ,p_period_unit => '0'
2907 ,p_day_adjustment => 'CURRENT'
2908 ,p_dynamic_code => null);
2909
2910 l_time_span_id := create_time_spans
2911 (p_creator_id => l_back_up_comp
2912 ,p_creator_type => 'RC'
2913 ,p_start_time_def_id => l_start_time_id
2914 ,p_end_time_def_id => l_end_time_id);
2915
2916 End;
2917
2918 PROCEDURE create_gen_upg_dfn
2919 (p_legislation_code IN VARCHAR2) IS
2920
2921 Cursor csr_gen_upg_exists (p_short_name varchar2)IS
2922 SELECT 'Y'
2923 FROM pay_upgrade_definitions pud
2924 WHERE pud.short_name = p_short_name
2925 AND pud.legislation_code = p_legislation_code;
2926
2927 Cursor csr_get_gen_upg_values (p_short_name varchar2)IS
2928 SELECT pud.short_name,
2929 pud.name,
2930 pud.description,
2931 pud.upgrade_level,
2932 pud.criticality,
2933 pud.threading_level,
2934 pud.failure_point,
2935 pud.legislatively_enabled,
2936 pud.upgrade_method,
2937 pud.upgrade_procedure,
2938 pud.qualifying_procedure,
2939 pud.owner_application_id
2940 FROM pay_upgrade_definitions pud
2941 WHERE pud.short_name = p_short_name
2942 AND pud.legislation_code = 'ZZ';
2943
2944 TYPE character_data_table IS TABLE OF VARCHAR2(280)
2945 INDEX BY BINARY_INTEGER;
2946
2947 lv_gen_upg_exists varchar2(5);
2948 ltt_short_name character_data_table;
2949 ltt_gen_short_name character_data_table;
2950
2951 --local variables for capturing generic upgrade definition.
2952
2953 ln_upg_id pay_upgrade_definitions.upgrade_definition_id%TYPE;
2954
2955 lv_short_name pay_upgrade_definitions.short_name%TYPE;
2956 lv_name pay_upgrade_definitions.name%TYPE;
2957 lv_description pay_upgrade_definitions.description%TYPE;
2958 lv_upgrade_level pay_upgrade_definitions.upgrade_level%TYPE;
2959 lv_criticality pay_upgrade_definitions.criticality%TYPE;
2960 lv_threading_level pay_upgrade_definitions.threading_level%TYPE;
2961 lv_failure_point pay_upgrade_definitions.failure_point%TYPE;
2962 lv_leg_enabled pay_upgrade_definitions.legislatively_enabled%TYPE;
2963 lv_upgrade_method pay_upgrade_definitions.upgrade_method%TYPE;
2964 lv_upgrade_proc pay_upgrade_definitions.upgrade_procedure%TYPE;
2965 lv_qualifying_proc pay_upgrade_definitions.qualifying_procedure%TYPE;
2966 ln_own_appl_id pay_upgrade_definitions.owner_application_id%TYPE;
2967
2968 BEGIN
2969
2970 hr_utility.set_location('pay_ip_startup_util.create_gen_upg_dfn',10);
2971
2972 ltt_short_name(1) := 'IP_ELEMENT_INFO_CAT_UPGRADE_'||upper(p_legislation_code);
2973 ltt_gen_short_name(1) := 'IP_ELEMENT_INFO_CAT_UPGRADE_ZZ';
2974 ltt_short_name(2) := 'IP_RETRO_ELEMENT_UPGRADE_'||upper(p_legislation_code);
2975 ltt_gen_short_name(2) := 'IP_RETRO_ELEMENT_UPGRADE_ZZ';
2976 ltt_short_name(3) := 'IP_RUN_BALANCE_UPGRADE_'||upper(p_legislation_code);
2977 ltt_gen_short_name(3) := 'IP_RUN_BALANCE_UPGRADE_ZZ';
2978
2979 FOR i in 1 ..3 LOOP
2980
2981 lv_gen_upg_exists := 'N';
2982
2983 OPEN csr_gen_upg_exists (ltt_short_name(i));
2984 FETCH csr_gen_upg_exists INTO lv_gen_upg_exists;
2985 CLOSE csr_gen_upg_exists;
2986
2987 IF lv_gen_upg_exists = 'N' THEN
2988
2989 hr_utility.set_location('pay_ip_startup_util.create_gen_upg_dfn',20);
2990
2991 OPEN csr_get_gen_upg_values (ltt_gen_short_name(i));
2992 FETCH csr_get_gen_upg_values INTO lv_short_name,
2993 lv_name,
2994 lv_description,
2995 lv_upgrade_level,
2996 lv_criticality,
2997 lv_threading_level,
2998 lv_failure_point,
2999 lv_leg_enabled,
3000 lv_upgrade_method,
3001 lv_upgrade_proc,
3002 lv_qualifying_proc,
3003 ln_own_appl_id;
3004 CLOSE csr_get_gen_upg_values;
3005
3006 pay_upgrade_definitions_pkg.insert_row(
3007 P_SHORT_NAME => replace(lv_short_name,'ZZ',upper(p_legislation_code)),
3008 P_NAME => lv_name,
3009 P_DESCRIPTION => lv_description,
3010 P_LEGISLATION_CODE => p_legislation_code,
3011 P_UPGRADE_LEVEL => lv_upgrade_level,
3012 P_CRITICALITY => lv_criticality,
3013 P_FAILURE_POINT => lv_failure_point,
3014 P_LEGISLATIVELY_ENABLED => lv_leg_enabled,
3015 P_UPGRADE_PROCEDURE => lv_upgrade_proc,
3016 P_THREADING_LEVEL => lv_threading_level,
3017 P_UPGRADE_METHOD => lv_upgrade_method,
3018 P_QUALIFYING_PROCEDURE => lv_qualifying_proc,
3019 P_OWNER_APPL_ID => ln_own_appl_id,
3020 P_FIRST_PATCHSET => null,
3021 P_VALIDATE_CODE => null,
3022 P_ADDITIONAL_INFO => null,
3023 P_LAST_UPDATE_DATE => sysdate,
3024 P_LAST_UPDATED_BY => 1,
3025 P_LAST_UPDATE_LOGIN => 1,
3026 P_CREATED_BY => 1,
3027 P_CREATION_DATE => sysdate,
3028 P_UPGRADE_DEFINITION_ID => ln_upg_id);
3029
3030 ELSE
3031
3032 hr_utility.set_location('pay_ip_startup_util.create_gen_upg_dfn',30);
3033
3034 hr_utility.trace ('The Generic Upgrade Mechanism already exists for this localization');
3035
3036 END IF;
3037
3038 END LOOP;
3039
3040 END create_gen_upg_dfn;
3041
3042
3043 /**********************************************************************
3044 ** Name : create_fnd_menu_entry
3045 ** Purpose : Procedure to create an entry into the Menu
3046 HR_<LEGISLATION_CODE>_SS_FUNCTIONS_SEED.
3047 ** Arguments : IN Parameters
3048 ** p_legislation_code -> Legislation Code
3049 **********************************************************************/
3050
3051 PROCEDURE create_fnd_menu_entry
3052 (p_legislation_code IN VARCHAR2
3053 , p_function_name IN VARCHAR2) IS
3054
3055 lv_fnd_menu_name fnd_menus.menu_name%TYPE;
3056 lv_menu_entry_exists char(1);
3057
3058 Cursor csr_menu_entry_exists (cp_menu_name varchar2,cp_function_name varchar2)IS
3059 select 'Y' from
3060 fnd_form_functions fff,
3061 fnd_menu_entries fme,
3062 fnd_menus fmu
3063 where fff.FUNCTION_ID = fme.function_id
3064 and fme.menu_id = fmu.menu_id
3065 and fff.function_name = cp_function_name
3066 and fmu.menu_name = cp_menu_name;
3067
3068 BEGIN
3069 hr_utility.set_location('pay_ip_startup_util.create_fnd_menu_entry ',10);
3070 lv_menu_entry_exists :='N';
3071 lv_fnd_menu_name := 'HR_'||p_legislation_code||'_SS_FUNCTIONS_SEED';
3072
3073 OPEN csr_menu_entry_exists (lv_fnd_menu_name,p_function_name);
3074 FETCH csr_menu_entry_exists INTO lv_menu_entry_exists;
3075 CLOSE csr_menu_entry_exists;
3076
3077 IF lv_menu_entry_exists = 'N' THEN
3078 hr_utility.set_location('pay_ip_startup_util.create_fnd_menu_entry ',20);
3079
3080 /*
3081 Bug#14349456 : Creating Entry for the function PAY_IP_VIEW_EMPBAL_SS -
3082 (View Employee Balance Page).
3083 */
3084 IF p_function_name = 'PAY_IP_VIEW_EMPBAL_SS' THEN
3085 hr_utility.set_location('pay_ip_startup_util.create_fnd_menu_entry ',30);
3086 pay_ip_utility.create_fnd_menu_entry(
3087 p_mode => 'MERGE_NOOVERWRITE',
3088 p_menu_name => lv_fnd_menu_name,
3089 p_sub_menu_name => null,
3090 p_function_name => p_function_name,
3091 p_grant_flag => 'N',
3092 p_prompt => NULL,
3093 p_description => NULL,
3094 p_owner => 'ORACLE');
3095 END IF;
3096 hr_utility.set_location('pay_ip_startup_util.create_fnd_menu_entry ',40);
3097 END IF;
3098
3099 EXCEPTION
3100 WHEN OTHERS THEN
3101 hr_utility.set_location('pay_ip_startup_util.create_fnd_menu_entry ',50);
3102 raise_application_error(-20001, SQLERRM);
3103 END create_fnd_menu_entry;
3104
3105 -- ----------------------------------------------------------------------
3106 -- Main Procedure through which all process is done in the required order
3107 -- This Setup gets the values for legislation_code and Currency from
3108 -- the concurrent request and creates the required data.
3109 -- ----------------------------------------------------------------------
3110
3111 PROCEDURE setup (p_errbuf OUT NOCOPY VARCHAR2,
3112 p_retcode OUT NOCOPY NUMBER,
3113 p_legislation_code IN VARCHAR2,
3114 p_currency_code IN VARCHAR2,
3115 p_Tax_Year IN VARCHAR2,
3116 p_install_tax_unit IN VARCHAR2,
3117 p_action_parameter_group_id IN NUMBER,
3118 p_user_id IN VARCHAR2,
3119 p_resp_id IN VARCHAR2,
3120 p_resp_appl_id IN VARCHAR2) IS
3121
3122 l_id_flex_num NUMBER(15);
3123 l_Tax_Year DATE;
3124 l_territory FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%TYPE;
3125 l_territory_with_code VARCHAR2(100);
3126 l_payroll_installed VARCHAR2(1);
3127 l_segment_used NUMBER;--Bug#4938455. Changed varchar2 to number.
3128 l_structure_code fnd_id_flex_structures.id_flex_structure_code%type;
3129
3130 l_list_of_ctxt_string VARCHAR2(2000);
3131 lv_request_status VARCHAR2(30);
3132
3133
3134 lv_patch_name VARCHAR2(240);-- Patch Name
3135 lv_appl_release VARCHAR2(240);-- Release
3136 lv_status VARCHAR2(2);--Patch Status
3137 ln_patch_number NUMBER;--Patch Number
3138 lv_patch_desc VARCHAR2(240);--Patch Description
3139
3140
3141
3142 CURSOR csr_payroll_installed is
3143 select 1 from fnd_product_installations
3144 where application_id = 801
3145 and status = 'I';
3146
3147 cursor csr_flex_struct (p_id_flex_num number) is
3148 select id_flex_structure_code
3149 from fnd_id_flex_structures
3150 where id_flex_code = 'SCL'
3151 and id_flex_num = p_id_flex_num;
3152
3153 BEGIN
3154 hr_utility.set_location('pay_ip_startup_util.setup',10);
3155
3156 /* Change the values of these patch variables whenever
3157 a patch updating route_id in table pay_balance_dimensions
3158 or pay_dimension_routes for existing balance dimensions
3159 or balance dimension routes is delivered.
3160 */
3161 lv_patch_name := 'ENABLE_RUN_BALANCES';
3162 lv_appl_release := '121';
3163 lv_status := 'C';
3164 ln_patch_number := 13903304;
3165 lv_patch_desc := 'ENABLE_RUN_BALANCES';
3166
3167 IF check_to_install(p_legislation_code,
3168 p_install_tax_unit) THEN
3169
3170 -- Check if logging of message is required.
3171 g_logging := logging(p_action_parameter_group_id);
3172
3173 SELECT territory_short_name, territory_short_name || ' (' || territory_code || ')'
3174 INTO l_territory, l_territory_with_code
3175 FROM fnd_territories_vl
3176 WHERE territory_code = p_legislation_code;
3177
3178 -- Clearing HR_STU_EXCEPTIONS table
3179 DELETE FROM HR_STU_EXCEPTIONS;
3180
3181 --Clearing all HRMS HR_S tables
3182 write_log('OUTPUT','PAY_34022_IP_LEG_INS_BEGINS', l_territory_with_code ,to_char(sysdate,'dd-Mon-yyyy hh:mi:ss'));
3183 hr_utility.set_location('pay_ip_startup_util.setup',20);
3184 write_log('LOG',NULL,NULL,NULL);
3185 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S%',NULL);
3186 clear_shadow_tables;
3187
3188 --Inserting ownerships
3189 hr_utility.set_location('pay_ip_startup_util.setup',25);
3190 write_log('LOG',NULL,NULL,NULL);
3191 pay_ip_utility.insert_all_ownerships;
3192
3193 --Moving data to shadow table
3194
3195 hr_utility.set_location('pay_ip_startup_util.setup',30);
3196 write_log('LOG',NULL,NULL,NULL);
3197 write_log('LOG','PAY_34009_IP_MOVE_TO_HR_S',NULL,NULL);
3198 move_to_shadow_tables(p_legislation_code, p_install_tax_unit);
3199
3200 l_Tax_Year := fnd_date.canonical_to_date(p_Tax_Year);
3201
3202 write_log('LOG',NULL,NULL,NULL);
3203 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'Tax Year', NULL);
3204 create_leg_rule(p_legislation_code => p_legislation_code,
3205 p_rule_type => 'L',
3206 p_rule_mode => to_char(l_Tax_Year,'dd/mm'));
3207 --
3208 -- Inserted legislation rule for currency. Bug No 3720975.
3209 --
3210 write_log('LOG',NULL,NULL,NULL);
3211 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'DC', NULL);
3212 create_leg_rule(p_legislation_code => p_legislation_code,
3213 p_rule_type => 'DC',
3214 p_rule_mode => p_currency_code);
3215
3216
3217 open csr_payroll_installed;
3218 fetch csr_payroll_installed into l_payroll_installed;
3219
3220 if csr_payroll_installed%found then
3221 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'I', NULL);
3222 create_leg_rule(p_legislation_code => p_legislation_code,
3223 p_rule_type => 'I',
3224 p_rule_mode => 'N');
3225 end if;
3226
3227 close csr_payroll_installed;
3228
3229 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'Run Type', NULL);
3230 create_leg_rule(p_legislation_code => p_legislation_code,
3231 p_rule_type => 'RUN_TYPE_FLAG',
3232 p_rule_mode => 'Y');
3233
3234 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'Tax Unit', NULL);
3235 create_leg_rule(p_legislation_code => p_legislation_code,
3236 p_rule_type => 'TAX_UNIT',
3237 p_rule_mode => 'N');
3238
3239 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'BAL_INIT_VALIDATION', NULL);
3240 create_leg_rule(p_legislation_code => p_legislation_code,
3241 p_rule_type => 'BAL_INIT_VALIDATION',
3242 p_rule_mode => 'N');
3243
3244
3245 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'REHIRE_BEFORE_FPD', NULL);
3246 create_leg_rule(p_legislation_code => p_legislation_code,
3247 p_rule_type => 'REHIRE_BEFORE_FPD',
3248 p_rule_mode => 'Y');
3249
3250 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'ACTION_CONTEXTS', NULL);
3251 create_leg_rule(p_legislation_code => p_legislation_code,
3252 p_rule_type => 'ACTION_CONTEXTS',
3253 p_rule_mode => 'Y');
3254
3255 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'PAYWSACT_SOE', NULL);
3256 create_leg_rule(p_legislation_code => p_legislation_code,
3257 p_rule_type => 'PAYWSACT_SOE',
3258 p_rule_mode => 'N');
3259
3260 -- Setting the rule mode of PAYWSRQP_DS and SOE to 'Y' for bug 3286741
3261
3262 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'PAYWSRQP_DS', NULL);
3263 create_leg_rule(p_legislation_code => p_legislation_code,
3264 p_rule_type => 'PAYWSRQP_DS',
3265 p_rule_mode => 'Y');
3266
3267 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'SOE', NULL);
3268 create_leg_rule(p_legislation_code => p_legislation_code,
3269 p_rule_type => 'SOE',
3270 p_rule_mode => 'Y');
3271
3272 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'RETROELEMENT_CHECK', NULL);
3273 create_leg_rule(p_legislation_code => p_legislation_code,
3274 p_rule_type => 'RETROELEMENT_CHECK',
3275 p_rule_mode => 'N');
3276
3277 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'ADVANCED_RETRO', NULL);
3278 create_leg_rule(p_legislation_code => p_legislation_code,
3279 p_rule_type => 'ADVANCED_RETRO',
3280 p_rule_mode => 'Y');
3281 /*bug 13681434 */
3282 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'MAGTAPE_FILE_SAVE', NULL);
3283 create_leg_rule(p_legislation_code => p_legislation_code,
3284 p_rule_type => 'MAGTAPE_FILE_SAVE',
3285 p_rule_mode => 'Y');
3286
3287 /*bug 13743223 */
3288 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'PAYSLIP_MODE', NULL);
3289 create_leg_rule(p_legislation_code => p_legislation_code,
3290 p_rule_type => 'PAYSLIP_MODE',
3291 p_rule_mode => 'XDO');
3292
3293 /*bug 13903304 */
3294 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'SAVE_ASG_RUN_BAL', NULL);
3295 create_leg_rule(p_legislation_code => p_legislation_code,
3296 p_rule_type => 'SAVE_ASG_RUN_BAL',
3297 p_rule_mode => 'Y');
3298
3299 IF p_install_tax_unit = 'Y' THEN
3300
3301 create_leg_rule(p_legislation_code => p_legislation_code,
3302 p_rule_type => 'TAX_UNIT',
3303 p_rule_mode => 'Y');
3304
3305 hr_utility.set_location('pay_ip_startup_util.setup',40);
3306 write_log('LOG',NULL,NULL,NULL);
3307 write_log('LOG','PAY_34007_IP_CREATE_FLEX',p_Legislation_Code ||
3308 '_STATUTORY_INFO', NULL);
3309
3310 /* Identify if a non standard structure has already been
3311 created for this legislation (Bugfix 3070623). If so, then
3312 skip the creation of the flexfields and set a flag to ensure the
3313 segment creation is also skipped */
3314
3315 SELECT MIN(id_flex_num)
3316 INTO l_id_flex_num
3317 FROM fnd_id_flex_structures
3318 WHERE id_flex_code = 'SCL'
3319 AND id_flex_structure_code like
3320 p_legislation_code||'_STATUTORY_INFO'||'%';
3321
3322 IF l_id_flex_num IS NULL THEN
3323 l_id_flex_num := create_key_flexfield
3324 (p_appl_short_name => 'PER',
3325 p_flex_code => 'SCL',
3326 p_structure_code => p_legislation_code || '_STATUTORY_INFO',
3327 p_structure_title => p_legislation_code || ' Statutory Info.',
3328 p_description => 'SCL KeyFlex Structure for ' || l_territory,
3329 p_view_name => '',
3330 p_freeze_flag => 'Y',
3331 p_enabled_flag => 'Y',
3332 p_cross_val_flag => 'Y',
3333 p_freeze_rollup_flag => 'N',
3334 p_dynamic_insert_flag => 'Y',
3335 p_shorthand_enabled_flag => 'N',
3336 p_shorthand_prompt => '',
3337 p_shorthand_length => 10);
3338 END IF;
3339
3340 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'S', NULL);
3341 create_leg_rule
3342 (p_legislation_code => p_legislation_code,
3343 p_rule_type => 'S',
3344 p_rule_mode => l_id_flex_num);
3345
3346 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'SDL', NULL);
3347 create_leg_rule
3348 (p_legislation_code => p_legislation_code,
3349 p_rule_type => 'SDL',
3350 p_rule_mode => 'A');
3351
3352 /* Only attempt the creation of the Tax Unit in Segment1 if this
3353 segment is not already in use */
3354
3355 select min(1)
3356 into l_segment_used
3357 FROM fnd_id_flex_segments
3358 WHERE id_flex_num = l_id_flex_num
3359 AND id_flex_code = 'SCL'
3360 AND application_column_name = 'SEGMENT1';
3361
3362 IF l_segment_used IS NULL THEN
3363
3364 -- Bug 4544374. The structure code needs to be derived
3365 -- since the process may be trying to re-use the customer defined
3366 -- structure.
3367
3368 open csr_flex_struct(p_id_flex_num => l_id_flex_num);
3369 fetch csr_flex_struct into l_structure_code;
3370 close csr_flex_struct;
3371
3372 create_flex_segments
3373 (p_appl_Short_Name => 'PER',
3374 p_flex_code => 'SCL',
3375 p_structure_code => l_structure_code,
3376 p_segment_name => 'Tax_Unit',
3377 p_column_name => 'SEGMENT1',
3378 p_segment_number => 1,
3379 p_enabled_flag => 'Y',
3380 p_displayed_flag => 'Y',
3381 p_indexed_flag => 'Y',
3382 p_value_set => 'HR_TAX_UNIT_NAME',
3383 p_display_size => 25,
3384 p_description_size => 25,
3385 p_concat_size => 25,
3386 p_lov_prompt => 'Tax Unit',
3387 p_window_prompt => 'Tax Unit');
3388 END IF;
3389
3390 hr_utility.set_location('pay_ip_startup_util.setup',50);
3391 END IF;
3392
3393 --Create FlexField
3394
3395 hr_utility.set_location('pay_ip_startup_util.setup',40);
3396 write_log('LOG',NULL,NULL,NULL);
3397 write_log('LOG','PAY_34007_IP_CREATE_FLEX',p_Legislation_Code || '_BANK_DETAILS', NULL);
3398 l_id_flex_num := create_key_flexfield
3399 (p_appl_short_name => 'PAY',
3400 p_flex_code => 'BANK',
3401 p_structure_code => p_legislation_code || '_BANK_DETAILS',
3402 p_structure_title => p_legislation_code || ' Bank Details',
3403 p_description => p_legislation_code || ' Bank Details',
3404 p_view_name => '',
3405 p_freeze_flag => 'Y',
3406 p_enabled_flag => 'Y',
3407 p_cross_val_flag => 'Y',
3408 p_freeze_rollup_flag => 'N',
3409 p_dynamic_insert_flag => 'Y',
3410 p_shorthand_enabled_flag => 'N',
3411 p_shorthand_prompt => '',
3412 p_shorthand_length => 10);
3413
3414 hr_utility.set_location('pay_ip_startup_util.setup',50);
3415 write_log('LOG','PAY_34008_IP_INS_LEG_RULE',p_Legislation_Code || '_BANK_DETAILS', NULL);
3416 create_leg_rule
3417 (p_legislation_code => p_legislation_code,
3418 p_rule_type => 'E',
3419 p_rule_mode => l_id_flex_num);
3420
3421 write_out;
3422
3423 /*Create Further Element Information DFF*/
3424 l_list_of_ctxt_string :='''ZZ_SUPPLEMENTAL EARNINGS'',
3425 ''ZZ_EARNINGS'',
3426 ''ZZ_EMPLOYER CHARGES'',
3427 ''ZZ_TAXABLE BENEFITS'',
3428 ''ZZ_DIRECT PAYMENT'',
3429 ''ZZ_TAX DEDUCTIONS'',
3430 ''ZZ_PRE-TAX DEDUCTIONS'',
3431 ''ZZ_VOLUNTARY DEDUCTIONS'',
3432 ''ZZ_INVOLUNTARY DEDUCTIONS''';
3433
3434 pay_ip_utility.create_ele_info_dff_ctxt(p_legislation_code => p_legislation_code,
3435 p_dff_name =>'Element Developer DF',
3436 p_appl_short_name => 'PAY',
3437 p_list_of_ctxt_string =>l_list_of_ctxt_string);
3438
3439 pay_ip_utility.create_ele_info_dff_ctxt(p_legislation_code => p_legislation_code,
3440 p_dff_name =>'Org Developer DF',
3441 p_appl_short_name => 'PER',
3442 p_list_of_ctxt_string =>'''ZZ_REPORTING_PREFERENCES''');
3443
3444 /* Register Organization Info flexfield */
3445
3446 /* Register Organization Info flexfield */
3447 pay_ip_utility.create_org_info_type
3448 (p_ORG_INFORMATION_TYPE => p_Legislation_Code||'_REPORTING_PREFERENCES'
3449 ,p_DESCRIPTION => 'International Payroll Reporting Preferences'
3450 ,p_DESTINATION => NULL
3451 ,p_DISP_ORG_INFORMATION_TYPE => 'Reporting Preferences'
3452 ,p_LEGISLATION_CODE => p_legislation_code
3453 ,p_APPLICATION_SHORT_NAME => 'PAY'
3454 ,p_NAVIGATION_METHOD => 'GS');
3455
3456 pay_ip_utility.create_org_info_type_by_class
3457 (p_ORG_CLASSIFICATION => 'HR_BG'
3458 ,p_ORG_INFORMATION_TYPE => p_Legislation_Code||'_REPORTING_PREFERENCES'
3459 ,p_MANDATORY_FLAG => 'N');
3460
3461 --Moving Payroll exception reporting dimensions
3462 hr_utility.set_location('pay_ip_startup_util.setup',55);
3463 pay_ip_utility.create_pqp_rep_dim
3464 (p_legislation_code => p_legislation_code,
3465 p_install_tax_unit => p_install_tax_unit,
3466 p_tax_year => p_Tax_Year);
3467
3468 --Updating shadow table
3469 hr_utility.set_location('pay_ip_startup_util.setup',60);
3470 write_log('LOG',NULL,NULL,NULL);
3471 write_log('LOG','PAY_34014_IP_UPD_LEG_CURR',p_Legislation_Code,p_currency_code);
3472 update_shadow_tables(p_legislation_code, p_currency_code);
3473
3474 -- Inserting in to HR_S_HISTORY table
3475
3476 hr_utility.set_location('pay_ip_startup_util.setup',70);
3477 write_log('LOG',NULL,NULL,NULL);
3478 write_log('LOG','PAY_34011_IP_INS_DATA_IN_TABLE','record','HR_S_HISTORY');
3479 insert_history_table(p_legislation_code);
3480
3481 -- Moving to Main Tables
3482 hr_utility.set_location('pay_ip_startup_util.setup',80);
3483 write_log('LOG',NULL,NULL,NULL);
3484 write_log('LOG','PAY_34016_IP_CALL_PROC','HR_LEGISLATION.INSTALL',NULL);
3485 BEGIN
3486 move_to_main_tables;
3487 EXCEPTION
3488 WHEN OTHERS THEN
3489 g_logging := 'Y';
3490 write_log('LOG','PAY_34018_IP_HR_STU_EXCEPTION',NULL,NULL);
3491 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
3492 END;
3493 create_report_format_param(p_legislation_code => p_legislation_code);
3494 write_log('LOG',NULL,NULL,NULL);
3495 write_log('LOG','PAY_34017_IP_UPD_TL_TABLE',NULL,NULL);
3496
3497 hr_utility.set_location('pay_ip_startup_util.setup',90);
3498 write_log('LOG','PAY_34015_IP_UPD_TABLE','PAY_ELEMENT_CLASSIFICATIONS_TL',NULL);
3499 update_ele_class_tl(p_legislation_code);
3500
3501 hr_utility.set_location('pay_ip_startup_util.setup',100);
3502 write_log('LOG','PAY_34015_IP_UPD_TABLE','PAY_BALANCE_TYPES_TL',NULL);
3503 update_bal_type_tl(p_legislation_code);
3504
3505 --Run Types
3506 hr_utility.set_location('pay_ip_startup_util.setup',110);
3507 create_runtype(p_legislation_code);
3508
3509 hr_utility.set_location('pay_ip_startup_util.setup',120);
3510 write_log('LOG','PAY_34015_IP_UPD_TABLE','PAY_RUN_TYPES_TL',NULL);
3511 update_run_type_tl(p_legislation_code);
3512
3513 --Balance Categories
3514 hr_utility.set_location('pay_ip_startup_util.setup',125);
3515 write_log('LOG','PAY_34015_IP_UPD_TABLE','PAY_BALANCE_CATGORIES_F_TL',NULL);
3516 update_bal_categories_tl(p_legislation_code);
3517 -- Bug 4159036. Deliver Balance Attribute Definitions
3518
3519 -- Balance Attribute Definitions
3520 hr_utility.set_location('pay_ip_startup_util.setup',130);
3521
3522 create_bal_att_def(p_legislation_code);
3523
3524 -- Element Templates and other data needed for the legislation to use
3525 -- Element Design Wizard(EDW)
3526
3527 hr_utility.set_location('pay_ip_startup_util.setup', 135);
3528 write_log('LOG',NULL,NULL,NULL);
3529 write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_CREATE_ELEMNT_TMPLT_RECORD.CREATE_ALL_TEMPLATES',NULL);
3530 pay_create_elemnt_tmplt_record.create_all_templates
3531 (p_legislation_code,p_currency_code);
3532
3533 /*bug 10212578 Begin*/
3534 -- Retropay(Enhanced) support for International Localisation
3535 hr_utility.set_location('pay_ip_startup_util.setup', 136);
3536 write_log('LOG',NULL,NULL,NULL);
3537 write_log('LOG','PAY_34016_IP_CALL_PROC','CREATE_ENH_RETRO_SETUP',NULL);
3538 create_enh_retro_setup(p_legislation_code);
3539
3540
3541 /*bug 10212578 End*/
3542
3543
3544 hr_utility.set_location('pay_ip_startup_util.setup', 138);
3545 write_log('LOG',NULL,NULL,NULL);
3546 write_log('LOG','PAY_34016_IP_CALL_PROC','CREATE_GEN_UPG_DFN',NULL);
3547 create_gen_upg_dfn(p_legislation_code);
3548
3549 /* #13772336 added start*/
3550 hr_utility.set_location('pay_ip_startup_util.setup', 140);
3551 write_log('LOG',NULL,NULL,NULL);
3552 write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_IP_UTILITY.INSERT_BAL_LOOKUP_VAL',NULL);
3553 pay_ip_utility.insert_bal_lookup_val(p_legislation_code);
3554 /* #13772336 added end*/
3555
3556 /* Bug#14349456 : Creating a Menu Entry for the Self Service functions
3557 into the Menu HR_<LEGISLATION_CODE>_SS_FUNCTIONS_SEED
3558 to enable function level security.
3559 */
3560 hr_utility.set_location('pay_ip_startup_util.setup', 150);
3561 write_log('LOG',NULL,NULL,NULL);
3562 write_log('LOG','PAY_34016_IP_CALL_PROC','CREATE_FND_MENU_ENTRY',NULL);
3563 create_fnd_menu_entry(p_legislation_code,'PAY_IP_VIEW_EMPBAL_SS');
3564
3565 hr_utility.set_location('pay_ip_startup_util.setup', 160);
3566 write_log('LOG',NULL,NULL,NULL);
3567 write_log('LOG','PAY_34016_IP_CALL_PROC','FND_MENU_ENTRIES_PKG.SUBMIT_COMPILE',NULL);
3568 lv_request_status := fnd_menu_entries_pkg.submit_compile;
3569
3570
3571 hr_utility.set_location('pay_ip_startup_util.setup', 170);
3572 write_log('LOG',NULL,NULL,NULL);
3573 write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_IP_UTILITY.COMPILE_FLEX_FIELD',NULL);
3574
3575 pay_ip_utility.compile_flex_field(p_mode => 'DFF',
3576 p_application_short_name => 'PAY',
3577 p_descriptive_flexfield_name => 'Element Developer DF');
3578
3579
3580 pay_ip_utility.compile_flex_field(p_mode => 'DFF',
3581 p_application_short_name => 'PER',
3582 p_descriptive_flexfield_name => 'Org Developer DF');
3583
3584 IF p_install_tax_unit = 'Y' THEN
3585 hr_utility.set_location('pay_ip_startup_util.setup', 180);
3586
3587 pay_ip_utility.compile_flex_field(
3588 p_mode => 'KFF-STR',
3589 p_application_short_name => 'PER',
3590 p_id_flex_code => 'SCL',
3591 p_id_flex_structure_code =>p_legislation_code || '_STATUTORY_INFO');
3592 END IF;
3593
3594 pay_ip_utility.compile_flex_field(
3595 p_mode => 'KFF-STR',
3596 p_application_short_name => 'PAY',
3597 p_id_flex_code => 'BANK',
3598 p_id_flex_structure_code =>p_legislation_code || '_BANK_DETAILS');
3599
3600
3601 IF pay_ip_utility.check_run_balance_enabled THEN
3602
3603 IF (NOT pay_ip_utility.check_patch_exists
3604 (p_patch_name => lv_patch_name
3605 ,p_patch_status => lv_status
3606 ,p_appl_release => lv_appl_release
3607 ,p_leg_code => p_legislation_code)
3608 ) THEN
3609
3610 hr_utility.set_location('pay_ip_startup_util.setup', 190);
3611 write_log('LOG',NULL,NULL,NULL);
3612 write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_IP_UTILITY.INSERT_PATCH_STATUS',NULL);
3613 pay_ip_utility.insert_patch_status(
3614 p_patch_name => lv_patch_name,
3615 p_patch_number => ln_patch_number,
3616 p_patch_desc => lv_patch_desc,
3617 p_patch_status => lv_status,
3618 p_leg_code => p_legislation_code,
3619 p_appl_release => lv_appl_release);
3620
3621 write_log('LOG',NULL,NULL,NULL);
3622 write_log('LOG','PAY_34085_IP_REFRESH_BAL_DBI',NULL,NULL);
3623 write_log('LOG','PAY_34016_IP_CALL_PROC','HRDYNDBI.REFRESH_DEFINED_BALANCES',NULL);
3624 hrdyndbi.refresh_defined_balances(p_leg_code => p_legislation_code);
3625
3626 write_log('LOG',NULL,NULL,NULL);
3627 write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_IP_UTILITY.COMPILE_FORMULAS',NULL);
3628 pay_ip_utility.compile_formulas(
3629 p_user_id => p_user_id,
3630 p_resp_id => p_resp_id,
3631 p_resp_appl_id => p_resp_appl_id);
3632
3633 END IF;
3634 END IF;
3635
3636 hr_utility.set_location('pay_ip_startup_util.setup', 200);
3637
3638 p_retcode := 0;
3639
3640 COMMIT;
3641
3642 write_log('OUTPUT',NULL,NULL,NULL);
3643 write_log('OUTPUT','PAY_34023_IP_LEG_INS_ENDS',NULL,NULL);
3644
3645 hr_utility.set_location('pay_ip_startup_util.setup',140);
3646
3647 ELSE
3648 p_retcode := 2;
3649 END IF;
3650
3651 hr_utility.set_location('pay_ip_startup_util.setup',150);
3652
3653 EXCEPTION
3654 WHEN OTHERS THEN
3655 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
3656 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
3657 ROLLBACK;
3658 p_errbuf := NULL;
3659 p_retcode := 2;
3660 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
3661 END setup;
3662
3663 END pay_ip_startup_util;