[Home] [Help]
PACKAGE BODY: APPS.PAY_IP_STARTUP_UTIL
Source
1 PACKAGE BODY pay_ip_startup_util AS
2 /* $Header: pyintstu.pkb 120.3 2006/01/10 03:20:36 sspratur noship $ */
3
4 -- ---------------------------------------------------------------------
5 -- Procedure to write Output and Log files. It can handle up to two
6 -- tokens.
7 -- ---------------------------------------------------------------------
8
9 g_logging VARCHAR2(1) := 'N';
10 g_start_of_time CONSTANT DATE := TO_DATE('01/01/0001','DD/MM/YYYY');
11
12 FUNCTION logging(p_action_parameter_group_id NUMBER) RETURN VARCHAR2 IS
13 CURSOR csr_logging IS
14 SELECT *
15 FROM pay_action_parameters
16 WHERE parameter_name = 'LOGGING';
17 l_rec csr_logging%ROWTYPE;
18 BEGIN
19 pay_core_utils.set_pap_group_id(p_action_parameter_group_id);
20 OPEN csr_logging;
21 FETCH csr_logging INTO l_rec;
22 IF csr_logging%NOTFOUND THEN
23 CLOSE csr_logging;
24 RETURN 'N';
25 ELSE
26 CLOSE csr_logging;
27 RETURN 'Y';
28 END IF;
29 END logging;
30
31 -- -------------------------------------------------------------
32 -- This
33 PROCEDURE write_log
34 (p_file_type VARCHAR2,
35 p_message VARCHAR2,
36 p_token1 VARCHAR2,
37 p_token2 VARCHAR2) IS
38
39 BEGIN
40 hr_utility.set_location('pay_ip_startup_util.write_log',10);
41 IF p_message IS NOT NULL THEN
42 fnd_message.set_name('PAY', p_message);
43
44 IF p_token1 IS NOT NULL THEN
45 fnd_message.set_token('1', p_token1);
46 END IF;
47
48 IF p_token2 IS NOT NULL THEN
49 fnd_message.set_token('2', p_token2);
50 END IF;
51 IF p_file_type ='LOG' and g_logging = 'Y' THEN
52 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
53 ELSIF p_file_type ='OUTPUT' THEN
54 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
55 ELSE
56 NULL;
57 END IF;
58 ELSE
59 IF p_file_type ='LOG' AND g_logging = 'Y' THEN
60 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
61 ELSIF p_file_type ='OUTPUT' THEN
62 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
63 ELSE
64 NULL;
65 END IF;
66 END IF;
67 hr_utility.set_location('pay_ip_startup_util.write_log',20);
68 END;
69
70 -- ----------------------------------------------------------------
71 -- Procedure to write into output file.
72 -- ----------------------------------------------------------------
73
74 PROCEDURE write_out IS
75
76 CURSOR get_element_class_csr IS
77 SELECT distinct classification_id, legislation_code, classification_name
78 FROM hr_s_element_classifications pec
79 ,hr_s_application_ownerships ao
80 ,fnd_product_installations b
81 ,fnd_application c
82 WHERE nvl(legislation_code,'X') = 'ZZ'
83 AND ao.key_name = 'CLASSIFICATION_ID'
84 AND TO_NUMBER(ao.key_value) = pec.classification_id
85 AND ao.product_name = c.application_short_name
86 AND c.application_id = b.application_id
87 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
88 OR
89 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
90
91 CURSOR get_balance_type_csr IS
92 SELECT distinct balance_type_id, currency_code, balance_name
93 FROM hr_s_balance_types pbt
94 ,hr_s_application_ownerships ao
95 ,fnd_product_installations b
96 ,fnd_application c
97 WHERE pbt.legislation_code = 'ZZ'
98 AND ao.key_name = 'BALANCE_TYPE_ID'
99 AND TO_NUMBER(ao.key_value) = pbt.balance_type_id
100 AND ao.product_name = c.application_short_name
101 AND c.application_id = b.application_id
102 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
103 OR
104 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
105
106 CURSOR get_defined_balances_csr IS
107 SELECT distinct defined_balance_id, pbt.balance_name bname
108 FROM hr_s_defined_balances pdb
109 ,hr_s_balance_types pbt
110 ,hr_s_application_ownerships ao
111 ,fnd_product_installations b
112 ,fnd_application c
113 WHERE pdb.legislation_code ='ZZ'
114 AND ao.key_name = 'DEFINED_BALANCE_ID'
115 AND pbt.balance_type_id = pdb.balance_type_id
116 AND TO_NUMBER(ao.key_value) = pdb.defined_balance_id
117 AND ao.product_name = c.application_short_name
118 AND c.application_id = b.application_id
119 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
120 OR
121 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
122
123 CURSOR get_balance_dimensions_csr IS
124 SELECT distinct balance_dimension_id, dimension_name
125 FROM hr_s_application_ownerships ao
126 ,hr_s_balance_dimensions pbd
127 ,fnd_product_installations b
128 ,fnd_application c
129 WHERE pbd.legislation_code ='ZZ'
130 AND ao.key_name = 'BALANCE_DIMENSION_ID'
131 AND TO_NUMBER(ao.key_value) = pbd.balance_dimension_id
132 AND ao.product_name = c.application_short_name
133 AND c.application_id = b.application_id
134 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
135 OR
136 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
137
138
139 CURSOR get_routes_csr IS
140 SELECT distinct fr.route_id, route_name
141 FROM hr_s_application_ownerships ao
142 ,hr_s_routes fr
143 ,hr_s_balance_dimensions pbd
144 ,fnd_product_installations b
145 ,fnd_application c
146 WHERE pbd.legislation_code ='ZZ'
147 AND ao.key_name = 'ROUTE_ID'
148 AND TO_NUMBER(ao.key_value) = fr.route_id
149 AND fr.route_id = pbd.route_id
150 AND ao.product_name = c.application_short_name
151 AND c.application_id = b.application_id
152 AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
153 OR
154 (b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
155
156 CURSOR get_leg_field_info_csr IS
157 SELECT field_name
158 FROM hr_s_legislative_field_info
159 WHERE legislation_code = 'ZZ';
160
161
162 CURSOR get_leg_rules_csr IS
163 SELECT rule_type
164 FROM hr_s_legislation_rules
165 WHERE legislation_code = 'ZZ';
166
167 CURSOR get_balance_class_csr IS
168 SELECT distinct pbc.balance_classification_id, pbt.balance_name bname
169 FROM hr_s_balance_classifications pbc
170 ,hr_s_balance_types pbt
171 WHERE pbc.legislation_code ='ZZ'
172 AND pbc.balance_type_id = pbt.balance_type_id;
173
174 BEGIN
175
176 -- write output file for Element Classifications to be Installed.
177 write_log('OUTPUT',NULL,NULL,NULL);
178 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Element Classifications', 'HR_S_ELEMENT_CLASSIFICATIONS');
179
180 FOR rec IN get_element_class_csr LOOP
181 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Element Classification', rec.classification_name);
182 END LOOP;
183
184 -- write output file for Balance Types to be Installed.
185
186 write_log('OUTPUT',NULL,NULL,NULL);
187 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Types', 'HR_S_BALANCE_TYPES');
188
189 FOR rec IN get_balance_type_csr LOOP
190 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Balance Types', rec.balance_name);
191 END LOOP;
192
193 -- write output file for Defined Balance to be Installed.
194
195 write_log('OUTPUT',NULL,NULL,NULL);
196 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Defined Balances', 'HR_S_DEFINED_BALANCES');
197
198 FOR rec IN get_defined_balances_csr LOOP
199 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Defined Balances for Balance Type', rec.bname);
200 END LOOP;
201
202 -- write output file for Balance Dimensions to be Installed.
203
204 write_log('OUTPUT',NULL,NULL,NULL);
205 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Dimensions', 'HR_S_BALANCE_DIMENSIONS');
206
207 FOR rec IN get_balance_dimensions_csr LOOP
208 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Balance Dimension', rec.dimension_name);
209 END LOOP;
210
211 -- write output file for Balance Dimensions to be Installed.
212
213 write_log('OUTPUT',NULL,NULL,NULL);
214 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Routes', 'HR_S_ROUTES');
215
216 FOR rec IN get_routes_csr LOOP
217 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Route', rec.route_name);
218 END LOOP;
219
220 write_log('OUTPUT',NULL,NULL,NULL);
221 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Legislative Field Info', 'HR_S_LEGISLATIVE_FIELD_INFO');
222
223 FOR rec IN get_leg_field_info_csr LOOP
224 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Legislative Field Info', rec.field_name);
225 END LOOP;
226
227 write_log('OUTPUT',NULL,NULL,NULL);
228 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Legislation Rules', 'HR_S_LEGISLATION_RULES');
229
230 FOR rec IN get_leg_rules_csr LOOP
231 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Legislation Rule', rec.rule_type);
232 END LOOP;
233
234 write_log('OUTPUT',NULL,NULL,NULL);
235 --write_log ('OUTPUT','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Classifications', 'HR_S_BALANCE_CLASSIFICATIONS');
236
237 FOR rec IN get_balance_class_csr LOOP
238 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Balance Classification for balance', rec.bname);
239 END LOOP;
240
241
242 END write_out;
243
244
245 -- ---------------------------------------------------------------------
246 -- This procedure inserts the Ownership in hr_s_application_ownerships
247 -- table. It takes the required values from where it is being called.
248 -- ---------------------------------------------------------------------
249 PROCEDURE insert_ownership(p_key_name IN VARCHAR2,
250 p_product_name IN VARCHAR2,
251 p_key_value IN VARCHAR2) AS
252 BEGIN
253
254 null;
255
256 /*hr_utility.set_location('--pay_ip_startup_util.insert_ownership',10);
257 INSERT INTO hr_s_application_ownerships
258 ( key_name
259 ,product_name
260 ,key_value)
261 SELECT
262 p_key_name
263 ,p_product_name
264 ,p_key_value
265 FROM dual
266 WHERE NOT EXISTS (SELECT NULL
267 FROM hr_s_application_ownerships
268 WHERE product_name = p_product_name
269 AND key_name = p_key_name
270 AND key_value = p_key_value);
271
272
273 hr_utility.set_location('--pay_ip_startup_util.insert_ownership',20); */
274 END insert_ownership;
275 -- ---------------------------------------------------------------------
276 -- Function to check if
277 -- <i> Localisation is available for the given legislation
278 -- <ii> HRGLOBAL is currently running
279 --<iii> Reference data is not available
280 -- In all the above three cases the program exits giving proper Log
281 -- information. Else it proceeds to create the required values.
282 -- ---------------------------------------------------------------------
283
284 FUNCTION check_to_install (
285 p_legislation_code IN VARCHAR2) RETURN BOOLEAN IS
286
287 v_check_installation boolean := TRUE;
288 l_Installed number := 0;
289 l_reference number := 0;
290
291 BEGIN
292 hr_utility.set_location('pay_ip_startup_util.check_to_install',10);
293 --Returns TRUE if the HR_LEGISLATION_INSTALLATIONS do not have PAY or PER
294 --for the given legislation and if no other patch is getting applied
295 --and reference data is available.
296
297 SELECT count(*)
298 INTO l_Installed
299 FROM hr_legislation_installations
300 WHERE application_short_name IN('PAY','PER')
301 AND legislation_code = p_legislation_code;
302
303 IF l_installed > 0 THEN
304 g_logging := 'Y';
305 write_log ('LOG','PAY_34020_IP_LOCAL_SUPPORT',NULL,NULL);
306 v_check_installation := FALSE;
307 RETURN v_check_installation ;
308 END IF;
309
310 SELECT count(*)
311 INTO l_Installed
312 FROM hr_legislation_installations
313 WHERE action IS NOT NULL;
314
315 IF l_installed > 0 THEN
316 g_logging := 'Y';
317 write_log ('LOG','PAY_34019_IP_HRGLOBAL_RUNNING',NULL,NULL);
318 v_check_installation := FALSE;
319 RETURN v_check_installation ;
320 END IF;
321
322 BEGIN
323 SELECT 1
324 INTO l_reference
325 FROM dual WHERE EXISTS (SELECT NULL FROM pay_element_classifications
326 WHERE nvl(legislation_code,'X') = 'ZZ'
327 AND business_group_id IS NULL);
328
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 NULL;
332 END;
333
334 IF l_reference = 0 THEN
335 g_logging := 'Y';
336 write_log ('LOG','PAY_34021_IP_NO_REF_DATA',NULL,NULL);
337 v_check_installation := FALSE;
338 RETURN v_check_installation ;
339 END IF;
340
341 RETURN v_check_installation ;
342 hr_utility.set_location('pay_ip_startup_util.check_to_install',20);
343 END check_to_install;
344
345 -- ---------------------------------------------------------------------
346 -- Procedure to clear all HR_S tables
347 -- ---------------------------------------------------------------------
348 PROCEDURE clear_shadow_tables IS
349
350 BEGIN
351 hr_utility.set_location('pay_ip_startup_util.clear_shadow_table',10);
352 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FORMULA_TYPES',NULL);
353 DELETE hr_s_formula_types;
354 COMMIT;
355
356 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FTYPE_CONTEXT_USAGES',NULL);
357 DELETE hr_s_ftype_context_usages;
358 COMMIT;
359
360 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FORMULAS_F',NULL);
361 DELETE hr_s_formulas_f;
362 COMMIT;
363
364 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ROUTES',NULL);
365 DELETE hr_s_routes;
366 COMMIT;
367
368 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ROUTE_CONTEXT_USAGES',NULL);
369 DELETE hr_s_route_context_usages;
370 COMMIT;
371
372 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_CONTEXTS',NULL);
373 DELETE hr_s_contexts;
374 COMMIT;
375
376 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ROUTE_PARAMETERS',NULL);
377 DELETE hr_s_route_parameters;
378 COMMIT;
379
380 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_ENTITIES',NULL);
381 DELETE hr_s_user_entities;
382 COMMIT;
383
384 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_DATABASE_ITEMS',NULL);
385 DELETE hr_s_database_items;
386 COMMIT;
387
388 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ROUTE_PARAMETER_VALUES',NULL);
389 DELETE hr_s_route_parameter_values;
390 COMMIT;
391
392 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FUNCTIONS',NULL);
393 DELETE hr_s_functions;
394 COMMIT;
395
396 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FUNCTION_PARAMETERS',NULL);
397 DELETE hr_s_function_parameters;
398 COMMIT;
399
400 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FUNCTION_CONTEXT_USAGES',NULL);
401 DELETE hr_s_function_context_usages;
402 COMMIT;
403
404 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ASSIGNMENT_STATUS_TYPES',NULL);
405 DELETE hr_s_assignment_status_types;
406 COMMIT;
407
408 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELEMENT_CLASSIFICATIONS',NULL);
409 DELETE hr_s_element_classifications;
410 COMMIT;
411
412 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELEMENT_TYPES_F',NULL);
413 DELETE hr_s_element_types_f;
414 COMMIT;
415
416 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_INPUT_VALUES_F',NULL);
417 DELETE hr_s_input_values_f;
418 COMMIT;
419
420 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_STATUS_PROCESSING_RULES_F',NULL);
421 DELETE hr_s_status_processing_rules_f;
422 COMMIT;
423
424 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_FORMULA_RESULT_RULES_F',NULL);
425 DELETE hr_s_formula_result_rules_f;
426 COMMIT;
427
428 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_SUB_CLASSN_RULES_F',NULL);
429 DELETE hr_s_sub_classn_rules_f;
430 COMMIT;
431
432 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BALANCE_TYPES',NULL);
433 DELETE hr_s_balance_types;
434 COMMIT;
435
436 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BALANCE_CLASSIFICATIONS',NULL);
437 DELETE hr_s_balance_classifications;
438 COMMIT;
439
440 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_DEFINED_BALANCES',NULL);
441 DELETE hr_s_defined_balances;
442 COMMIT;
443
444 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BALANCE_FEEDS_F',NULL);
445 DELETE hr_s_balance_feeds_f;
446 COMMIT;
447
448 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BALANCE_DIMENSIONS',NULL);
449 DELETE hr_s_balance_dimensions;
450 COMMIT;
451
452 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELEMENT_SETS',NULL);
453 DELETE hr_s_element_sets;
454 COMMIT;
455
456 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELEMENT_TYPE_RULES',NULL);
457 DELETE hr_s_element_type_rules;
458 COMMIT;
459
460 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ELE_CLASSN_RULES',NULL);
461 DELETE hr_s_ele_classn_rules;
462 COMMIT;
463
464 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_TABLES',NULL);
465 DELETE hr_s_user_tables;
466 COMMIT;
467
468 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_COLUMNS',NULL);
469 DELETE hr_s_user_columns;
470 COMMIT;
471
472 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_ROWS_F',NULL);
473 DELETE hr_s_user_rows_f;
474 COMMIT;
475
476 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_USER_COLUMN_INSTANCES_F',NULL);
477 DELETE hr_s_user_column_instances_f;
478 COMMIT;
479
480 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_QP_REPORTS',NULL);
481 DELETE hr_s_qp_reports;
482 COMMIT;
483
484 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ORG_INFORMATION_TYPES',NULL);
485 DELETE hr_s_org_information_types;
486 COMMIT;
487
488 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ORG_INFO_TYPES_BY_CLASS',NULL);
489 DELETE hr_s_org_info_types_by_class;
490 COMMIT;
491
492 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_ASSIGNMENT_INFO_TYPES',NULL);
493 DELETE hr_s_assignment_info_types;
494 COMMIT;
495
496 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_GLOBALS_F',NULL);
497 DELETE hr_s_globals_f;
498 COMMIT;
499
500 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_LEGISLATIVE_FIELD_INFO',NULL);
501 DELETE hr_s_legislative_field_info;
502 COMMIT;
503
504 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_LEGISLATION_SUBGROUPS',NULL);
505 DELETE hr_s_legislation_subgroups;
506 COMMIT;
507
508 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_APPLICATION_OWNERSHIPS',NULL);
509 DELETE hr_s_application_ownerships;
510 COMMIT;
511
512 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_PAYMENT_TYPES',NULL);
513 DELETE hr_s_payment_types;
514 COMMIT;
515
516 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_BENEFIT_CLASSIFICATIONS',NULL);
517 DELETE hr_s_benefit_classifications;
518 COMMIT;
519
520 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_COBRA_QFYING_EVENTS_F',NULL);
521 DELETE hr_s_cobra_qfying_events_f;
522 COMMIT;
523
524 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_VALID_DEPENDENT_TYPES',NULL);
525 DELETE hr_s_valid_dependent_types;
526 COMMIT;
527
528 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_HISTORY',NULL);
529 DELETE hr_s_history;
530 COMMIT;
531
532 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_STATE_RULES',NULL);
533 DELETE hr_s_state_rules;
534 COMMIT;
535
536 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_TAXABILITY_RULES',NULL);
537 DELETE hr_s_taxability_rules;
538 COMMIT;
539
540 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_MONETARY_UNITS',NULL);
541 DELETE hr_s_monetary_units;
542 COMMIT;
543
544 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_WC_STATE_SURCHARGES',NULL);
545 DELETE hr_s_wc_state_surcharges;
546 COMMIT;
547
548 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_LEGISLATION_RULES',NULL);
549 DELETE hr_s_legislation_rules;
550 COMMIT;
551
552 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_TAXABILITY_RULES_DATES',NULL);
553 DELETE HR_S_TAXABILITY_RULES_DATES;
554 COMMIT;
555
556 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_MAGNETIC_RECORDS',NULL);
557 DELETE HR_S_MAGNETIC_RECORDS;
558 COMMIT;
559
560 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_MAGNETIC_BLOCKS',NULL);
561 DELETE HR_S_MAGNETIC_BLOCKS;
562 COMMIT;
563
564 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_REPORT_FORMAT_MAPPINGS_F',NULL);
565 DELETE HR_S_REPORT_FORMAT_MAPPINGS_F;
566 COMMIT;
567
568 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_CITY_TAX_INFO_F',NULL);
569 DELETE HR_S_US_CITY_TAX_INFO_F;
570 COMMIT;
571
572 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_COUNTY_TAX_INFO_F',NULL);
573 DELETE HR_S_US_COUNTY_TAX_INFO_F;
574 COMMIT;
575
576 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_STATE_TAX_INFO_F',NULL);
577 DELETE HR_S_US_STATE_TAX_INFO_F;
578 COMMIT;
579
580 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_FEDERAL_TAX_INFO_F',NULL);
581 DELETE HR_S_US_FEDERAL_TAX_INFO_F;
582 COMMIT;
583
584 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_GARN_EXEMPTION_RULES_F',NULL);
585 DELETE HR_S_US_GARN_EXEMPTION_RULES_F;
586 COMMIT;
587
588 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_GARN_LIMIT_RULES_F',NULL);
589 DELETE HR_S_US_GARN_LIMIT_RULES_F;
590 COMMIT;
591
592 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_US_GARN_FEE_RULES_F',NULL);
593 DELETE HR_S_US_GARN_FEE_RULES_F;
594 COMMIT;
595
596 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_REPORT_LOOKUPS',NULL);
597 DELETE HR_S_REPORT_LOOKUPS;
598 COMMIT;
599
600 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_REPORT_FORMAT_ITEMS_F',NULL);
601 DELETE HR_S_REPORT_FORMAT_ITEMS_F;
602 COMMIT;
603
604 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S_STATE_RULES',NULL);
605 DELETE HR_S_STATE_RULES;
606 COMMIT;
607 hr_utility.set_location('pay_ip_startup_util.clear_shadow_table',20);
608 END clear_shadow_tables;
609
610
611 -- -----------------------------------------------------------------------
612 -- This procedure will move the data with dummy legislation to HR_S
613 -- tables from LIVE HRMS tables. The ownership for Elemnet Classifications
614 -- Balance types, Balance Dimensions, Routes arealso inserted.
615 -- -----------------------------------------------------------------------
616 PROCEDURE move_to_shadow_tables (p_legislation_code IN VARCHAR2,
617 p_install_tax_unit IN VARCHAR2) IS
618
619
620
621 --
622 CURSOR get_element_class_csr IS
623 SELECT
624 classification_id,business_group_id,legislation_code,classification_name,description,
625 legislation_subgroup,costable_flag,default_high_priority,default_low_priority,
626 default_priority,distributable_over_flag,non_payments_flag,costing_debit_or_credit,
627 parent_classification_id,create_by_default_flag,last_update_date,last_updated_by,
628 last_update_login,created_by,creation_date,balance_initialization_flag,object_version_number
629 FROM pay_element_classifications
630 WHERE nvl(legislation_code,'X') = 'ZZ'
631 AND business_group_id IS NULL;
632
633 CURSOR get_balance_type_csr IS
634 SELECT
635 balance_type_id, business_group_id, legislation_code, currency_code,
636 assignment_remuneration_flag, balance_name,balance_uom, NULL comments, jurisdiction_level,
637 legislation_subgroup, reporting_name, tax_type, attribute_category,
638 attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
639 attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14,
640 attribute15, attribute16, attribute17, attribute18, attribute19, attribute20,
641 last_update_date, last_updated_by, last_update_login, created_by, creation_date
642 FROM pay_balance_types
643 WHERE nvl(legislation_code,'X') = 'ZZ'
644 AND business_group_id IS NULL;
645
646 CURSOR get_defined_balances_csr IS
647 SELECT
648 d.defined_balance_id, d.business_group_id, d.legislation_code, d.balance_type_id,
649 d.balance_dimension_id, d.force_latest_balance_flag, d.legislation_subgroup,
650 d.last_update_date, d.last_updated_by, d.last_update_login, d.created_by, d.creation_date,
651 d.object_version_number, d.grossup_allowed_flag, b.balance_name bname
652 FROM pay_defined_balances d, pay_balance_types b
653 WHERE d.balance_type_id = b.balance_type_id
654 AND EXISTS (SELECT NULL FROM hr_s_balance_types b
655 WHERE d.balance_type_id = b.balance_type_id);
656
657
658 --Cursors to install route and dimension if install_tax_unit is true.
659
660 CURSOR get_balance_dimensions_csr IS
661 SELECT
662 balance_dimension_id, business_group_id, legislation_code, route_id,
663 database_item_suffix, dimension_name, dimension_type, description,
664 feed_checking_code, feed_checking_type, legislation_subgroup, payments_flag,
665 expiry_checking_code, expiry_checking_level, dimension_level, period_type
666 FROM pay_balance_dimensions
667 WHERE nvl(legislation_code,'X') = 'ZZ'
668 AND business_group_id IS NULL;
669
670
671 CURSOR get_routes_csr IS
672 SELECT
673 route_id, route_name, user_defined_flag, description, text, last_update_date,
674 last_updated_by, last_update_login, created_by, creation_date
675 FROM ff_routes a
676 WHERE EXISTS (SELECT NULL
677 FROM pay_balance_dimensions c
678 WHERE c.route_id = a.route_id
679 AND c.legislation_code = 'ZZ');
680
681
682 CURSOR get_balance_class_csr IS
683 SELECT
684 balance_classification_id, business_group_id, legislation_code, balance_type_id,
685 classification_id, scale, legislation_subgroup, last_update_date, last_updated_by,
686 last_update_login, created_by, creation_date, object_version_number
687 FROM pay_balance_classifications
688 WHERE nvl(legislation_code,'X') = 'ZZ'
689 AND business_group_id IS NULL;
690
691
692
693 BEGIN
694 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',10);
695 --Legislation Rules
696 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE','Legislation Rules', 'HR_S_LEGISLATION_RULES');
697
698 INSERT INTO hr_s_application_ownerships
699 (key_name
700 ,product_name
701 ,key_value)
702 SELECT ao.key_name
703 ,ao.product_name
704 ,ao.key_value
705 FROM hr_application_ownerships ao
706 ,pay_element_classifications pec
707 WHERE pec.legislation_code = 'ZZ'
708 AND ao.key_name = 'CLASSIFICATION_ID'
709 AND TO_NUMBER(ao.key_value) = pec.classification_id
710 UNION ALL
711 SELECT ao.key_name
712 ,ao.product_name
713 ,ao.key_value
714 FROM hr_application_ownerships ao
715 ,pay_balance_types pbt
716 WHERE pbt.legislation_code = 'ZZ'
717 AND ao.key_name = 'BALANCE_TYPE_ID'
718 AND TO_NUMBER(ao.key_value) = pbt.balance_type_id
719 UNION ALL
720 SELECT ao.key_name
721 ,ao.product_name
722 ,ao.key_value
723 FROM hr_application_ownerships ao
724 ,pay_balance_dimensions pbd
725 WHERE pbd.legislation_code ='ZZ'
726 AND ao.key_name = 'BALANCE_DIMENSION_ID'
727 AND TO_NUMBER(ao.key_value) = pbd.balance_dimension_id
728 UNION ALL
729 SELECT ao.key_name
730 ,ao.product_name
731 ,ao.key_value
732 FROM hr_application_ownerships ao
733 ,pay_defined_balances pdb
734 WHERE pdb.legislation_code ='ZZ'
735 AND ao.key_name = 'DEFINED_BALANCE_ID'
736 AND TO_NUMBER(ao.key_value) = pdb.defined_balance_id
737 UNION ALL
738 SELECT ao.key_name
739 ,ao.product_name
740 ,ao.key_value
741 FROM hr_application_ownerships ao
742 ,ff_routes fr
743 ,pay_balance_dimensions pbd
744 WHERE pbd.legislation_code ='ZZ'
745 AND ao.key_name = 'ROUTE_ID'
746 AND TO_NUMBER(ao.key_value) = fr.route_id
747 AND fr.route_id = pbd.route_id;
748
749
750 -- Element Classifictions
751
752 write_log('LOG',NULL,NULL,NULL);
753 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Element Classifications', 'HR_S_ELEMENT_CLASSIFICATIONS');
754
755 FOR rec IN get_element_class_csr LOOP
756 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Element Classification', rec.classification_name);
757
758 INSERT INTO hr_s_element_classifications
759 ( classification_id
760 ,business_group_id
761 ,legislation_code
762 ,classification_name
763 ,description
764 ,legislation_subgroup
765 ,costable_flag
766 ,default_high_priority
767 ,default_low_priority
768 ,default_priority
769 ,distributable_over_flag
770 ,non_payments_flag
771 ,costing_debit_or_credit
772 ,parent_classification_id
773 ,create_by_default_flag
774 ,last_update_date
775 ,last_updated_by
776 ,last_update_login
777 ,created_by
778 ,creation_date
779 ,balance_initialization_flag
780 ,object_version_number
781 )
782
783 VALUES
784 ( rec.classification_id
785 ,rec.business_group_id
786 ,rec.legislation_code
787 ,rec.classification_name
788 ,rec.description
789 ,rec.legislation_subgroup
790 ,rec.costable_flag
791 ,rec.default_high_priority
792 ,rec.default_low_priority
793 ,rec.default_priority
794 ,rec.distributable_over_flag
795 ,rec.non_payments_flag
796 ,rec.costing_debit_or_credit
797 ,rec.parent_classification_id
798 ,rec.create_by_default_flag
799 ,rec.last_update_date
800 ,rec.last_updated_by
801 ,rec.last_update_login
802 ,rec.created_by
803 ,rec.creation_date
804 ,rec.balance_initialization_flag
805 ,rec.object_version_number
806 );
807
808 write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Element Classification', rec.classification_name);
809 ----pay_ip_startup_util.insert_ownership('CLASSIFICATION_ID','PER',rec.classification_id);
810 ----pay_ip_startup_util.insert_ownership('CLASSIFICATION_ID','PAY',rec.classification_id);
811
812 END LOOP;
813
814 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables',20);
815 --Balance Types
816 write_log('LOG',NULL,NULL,NULL);
817 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Types', 'HR_S_BALANCE_TYPES');
818 FOR rec IN get_balance_type_csr LOOP
819 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Balance Types', rec.balance_name);
820
821 INSERT INTO hr_s_balance_types
822 ( balance_type_id
823 ,business_group_id
824 ,legislation_code
825 ,currency_code
826 ,assignment_remuneration_flag
827 ,balance_name
828 ,balance_uom
829 ,comments
830 ,jurisdiction_level
831 ,legislation_subgroup
832 ,reporting_name
833 ,tax_type
834 ,attribute_category
835 ,attribute1
836 ,attribute2
837 ,attribute3
838 ,attribute4
839 ,attribute5
840 ,attribute6
841 ,attribute7
842 ,attribute8
843 ,attribute9
844 ,attribute10
845 ,attribute11
846 ,attribute12
847 ,attribute13
848 ,attribute14
849 ,attribute15
850 ,attribute16
851 ,attribute17
852 ,attribute18
853 ,attribute19
854 ,attribute20
855 ,last_update_date
856 ,last_updated_by
857 ,last_update_login
858 ,created_by
859 ,creation_date
860 )
861
862 VALUES
863 ( rec.balance_type_id
864 ,rec.business_group_id
865 ,rec.legislation_code
866 ,rec.currency_code
867 ,rec.assignment_remuneration_flag
868 ,rec.balance_name
869 ,rec.balance_uom
870 ,rec.comments
871 ,rec.jurisdiction_level
872 ,rec.legislation_subgroup
873 ,rec.reporting_name
874 ,rec.tax_type
875 ,rec.attribute_category
876 ,rec.attribute1
877 ,rec.attribute2
878 ,rec.attribute3
879 ,rec.attribute4
880 ,rec.attribute5
881 ,rec.attribute6
882 ,rec.attribute7
883 ,rec.attribute8
884 ,rec.attribute9
885 ,rec.attribute10
886 ,rec.attribute11
887 ,rec.attribute12
888 ,rec.attribute13
889 ,rec.attribute14
890 ,rec.attribute15
891 ,rec.attribute16
892 ,rec.attribute17
893 ,rec.attribute18
894 ,rec.attribute19
895 ,rec.attribute20
896 ,rec.last_update_date
897 ,rec.last_updated_by
898 ,rec.last_update_login
899 ,rec.created_by
900 ,rec.creation_date
901 );
902
903 write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Type', rec.balance_name);
904 ----pay_ip_startup_util.insert_ownership('BALANCE_TYPE_ID','PER',rec.balance_type_id);
905 --pay_ip_startup_util.insert_ownership('BALANCE_TYPE_ID','PAY',rec.balance_type_id);
906 END LOOP;
907
908 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',30);
909
910 --Defined Balance
911 write_log('LOG',NULL,NULL,NULL);
912 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Defined Balances', 'HR_S_DEFINED_BALANCES');
913 FOR rec IN get_defined_balances_csr LOOP
914 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Defined Balances for Balance Type', rec.bname);
915 INSERT INTO hr_s_defined_balances
916 ( defined_balance_id
917 ,business_group_id
918 ,legislation_code
919 ,balance_type_id
920 ,balance_dimension_id
921 ,force_latest_balance_flag
922 ,legislation_subgroup
923 ,last_update_date
924 ,last_updated_by
925 ,last_update_login
926 ,created_by
927 ,creation_date
928 ,object_version_number
929 ,grossup_allowed_flag
930 )
931 VALUES
932 ( rec.defined_balance_id
933 ,rec.business_group_id
934 ,rec.legislation_code
935 ,rec.balance_type_id
936 ,rec.balance_dimension_id
937 ,rec.force_latest_balance_flag
938 ,rec.legislation_subgroup
939 ,rec.last_update_date
940 ,rec.last_updated_by
941 ,rec.last_update_login
942 ,rec.created_by
943 ,rec.creation_date
944 ,rec.object_version_number
945 ,rec.grossup_allowed_flag
946 );
947
948 END LOOP;
949
950 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',40);
951
952 --Balance Dimensions
953
954 write_log('LOG',NULL,NULL,NULL);
955 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Dimensions', 'HR_S_BALANCE_DIMENSIONS');
956 FOR rec IN get_balance_dimensions_csr LOOP
957 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Balance Dimension', rec.dimension_name);
958
959 INSERT INTO hr_s_balance_dimensions
960 ( balance_dimension_id
961 ,business_group_id
962 ,legislation_code
963 ,route_id
964 ,database_item_suffix
965 ,dimension_name
966 ,dimension_type
967 ,description
968 ,feed_checking_code
969 ,feed_checking_type
970 ,legislation_subgroup
971 ,payments_flag
972 ,expiry_checking_code
973 ,expiry_checking_level
974 ,dimension_level
975 , period_type
976 )
977
978 VALUES
979 ( rec.balance_dimension_id
980 ,rec.business_group_id
981 ,rec.legislation_code
982 ,rec.route_id
983 ,rec.database_item_suffix
984 ,rec.dimension_name
985 ,rec.dimension_type
986 ,rec.description
987 ,rec.feed_checking_code
988 ,rec.feed_checking_type
989 ,rec.legislation_subgroup
990 ,rec.payments_flag
991 ,rec.expiry_checking_code
992 ,rec.expiry_checking_level
993 ,rec.dimension_level
994 ,rec.period_type
995 );
996
997 write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Dimension', rec.dimension_name);
998
999 END LOOP;
1000
1001 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',50);
1002
1003 --Routes
1004
1005 write_log('LOG',NULL,NULL,NULL);
1006 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Routes', 'HR_S_ROUTES');
1007 FOR rec IN get_routes_csr LOOP
1008 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Route', rec.route_name);
1009
1010 INSERT INTO hr_s_routes
1011 ( route_id
1012 ,route_name
1013 ,user_defined_flag
1014 ,description
1015 ,text
1016 ,last_update_date
1017 ,last_updated_by
1018 ,last_update_login
1019 ,created_by
1020 ,creation_date
1021 )
1022
1023 VALUES
1024 ( rec.route_id
1025 ,rec.route_name
1026 ,rec.user_defined_flag
1027 ,rec.description
1028 ,rec.text
1029 ,rec.last_update_date
1030 ,rec.last_updated_by
1031 ,rec.last_update_login
1032 ,rec.created_by
1033 ,rec.creation_date
1034 );
1035
1036 write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Route', rec.route_name);
1037
1038 END LOOP;
1039
1040
1041 IF p_install_tax_unit = 'N' THEN
1042
1043 DELETE FROM hr_s_application_ownerships
1044 WHERE key_name = 'BALANCE_DIMENSION_ID'
1045 AND TO_NUMBER(key_value) IN (SELECT balance_dimension_id
1046 FROM hr_s_balance_dimensions
1047 WHERE legislation_code = 'ZZ'
1048 AND INSTR(database_item_suffix,'_TU_') > 0);
1049
1050 END IF;
1051
1052
1053
1054 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',60);
1055 -- Route Parameters
1056 write_log('LOG',NULL,NULL,NULL);
1057 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Route Parameters', 'HR_S_ROUTE_PARAMETERS');
1058 INSERT INTO hr_s_route_parameters
1059 (SELECT
1060 route_parameter_id
1061 ,route_id
1062 ,data_type
1063 ,parameter_name
1064 ,sequence_no
1065 FROM ff_route_parameters a
1066 WHERE EXISTS ( SELECT NULL
1067 FROM hr_s_routes b
1068 WHERE b.route_id = a.route_id));
1069 write_log('LOG',NULL,NULL,NULL);
1070 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Route Context Usages', 'HR_S_ROUTE_CONTEXT_USAGES');
1071 INSERT INTO hr_s_route_context_usages
1072 (SELECT
1073 route_id
1074 ,context_id
1075 ,sequence_no
1076 FROM ff_route_context_usages a
1077 WHERE EXISTS ( SELECT NULL
1078 FROM hr_s_routes b
1079 WHERE b.route_id = a.route_id));
1080
1081 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',70);
1082
1083 -- Used column to column mapping in the insert statement to remove
1084 -- error caused by mismatch in number of fields. Bug No 3720975.
1085
1086 INSERT into hr_s_legislative_field_info
1087 (FIELD_NAME,
1088 LEGISLATION_CODE,
1089 PROMPT,
1090 VALIDATION_NAME,
1091 VALIDATION_TYPE,
1092 TARGET_LOCATION,
1093 RULE_TYPE,
1094 RULE_MODE)
1095 (SELECT
1096 FIELD_NAME,
1097 LEGISLATION_CODE,
1098 PROMPT,
1099 VALIDATION_NAME,
1100 VALIDATION_TYPE,
1101 TARGET_LOCATION,
1102 RULE_TYPE,
1103 RULE_MODE
1104 FROM pay_legislative_field_info
1105 WHERE nvl(legislation_code,'X') = 'ZZ');
1106
1107 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',80);
1108
1109 --Balance Classifications
1110
1111 write_log('LOG',NULL,NULL,NULL);
1112 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Classifications', 'HR_S_BALANCE_CLASSIFICATIONS');
1113 FOR rec IN get_balance_class_csr LOOP
1114 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Balance Classifications', rec.balance_classification_id);
1115
1116 INSERT INTO hr_s_balance_classifications
1117 ( balance_classification_id
1118 ,business_group_id
1119 ,legislation_code
1120 ,balance_type_id
1121 ,classification_id
1122 ,scale
1123 ,legislation_subgroup
1124 ,last_update_date
1125 ,last_updated_by
1126 ,last_update_login
1127 ,created_by
1128 ,creation_date
1129 ,object_version_number
1130 )
1131 VALUES
1132 ( rec.balance_classification_id
1133 ,rec.business_group_id
1134 ,rec.legislation_code
1135 ,rec.balance_type_id
1136 ,rec.classification_id
1137 ,rec.scale
1138 ,rec.legislation_subgroup
1139 ,rec.last_update_date
1140 ,rec.last_updated_by
1141 ,rec.last_update_login
1142 ,rec.created_by
1143 ,rec.creation_date
1144 ,rec.object_version_number
1145 );
1146
1147 write_log ('LOG','PAY_34013_IP_INS_OWNERSHIP', 'Balance Classifications', rec.balance_classification_id);
1148
1149 END LOOP;
1150
1151 hr_utility.set_location('pay_ip_startup_util.move_to_shadow_tables ',90);
1152
1153
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 IF get_element_class_csr%ISOPEN THEN
1157 CLOSE get_element_class_csr;
1158 END IF;
1159 IF get_balance_type_csr%ISOPEN THEN
1160 CLOSE get_balance_type_csr;
1161 END IF;
1162 IF get_defined_balances_csr%ISOPEN THEN
1163 CLOSE get_defined_balances_csr;
1164 END IF;
1165 IF get_balance_dimensions_csr%ISOPEN THEN
1166 CLOSE get_balance_dimensions_csr;
1167 END IF;
1168 IF get_routes_csr%ISOPEN THEN
1169 CLOSE get_routes_csr;
1170 END IF;
1171 IF get_balance_class_csr%ISOPEN THEN
1172 CLOSE get_balance_class_csr;
1173 END IF;
1174
1175 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1176
1177 END move_to_shadow_tables;
1178 -- ---------------------------------------------------------------------
1179 -- Function to create Bank Key Flexfield , It will be created as
1180 -- " <legislation_code>_BANK_DETAILS "
1181 -- ---------------------------------------------------------------------
1182
1183 FUNCTION create_key_flexfield
1184 (p_appl_Short_Name IN VARCHAR2,
1185 p_flex_code IN VARCHAR2,
1186 p_structure_code IN VARCHAR2,
1187 p_structure_title IN VARCHAR2,
1188 p_description IN VARCHAR2,
1189 p_view_name IN VARCHAR2,
1190 p_freeze_flag IN VARCHAR2,
1191 p_enabled_flag IN VARCHAR2,
1192 p_cross_val_flag IN VARCHAR2,
1193 p_freeze_rollup_flag IN VARCHAR2,
1194 p_dynamic_insert_flag IN VARCHAR2,
1195 p_shorthand_enabled_flag IN VARCHAR2,
1196 p_shorthand_prompt IN VARCHAR2,
1197 p_shorthand_length IN NUMBER) RETURN NUMBER IS
1198
1199 l_flexfield fnd_flex_key_api.flexfield_type;
1200 l_structure fnd_flex_key_api.structure_type;
1201 l_application_id NUMBER(15);
1202 l_exists varchar2(1);
1203
1204
1205
1206 CURSOR duplicate_structure_check (p_application_id NUMBER,
1207 p_flexfield_code VARCHAR2,
1208 p_structure_title VARCHAR2) IS
1209 SELECT null
1210 FROM fnd_id_flex_structures_vl
1211 WHERE application_id = p_application_id
1212 AND id_flex_code = p_flexfield_code
1213 AND id_flex_structure_name = p_structure_title;
1214
1215
1216 BEGIN
1217
1218 SELECT application_id
1219 INTO l_application_id
1220 FROM FND_APPLICATION
1221 WHERE application_short_name = p_appl_Short_Name;
1222
1223 hr_utility.set_location('pay_ip_startup_util.create_key_flexfield ',10);
1224 fnd_flex_key_api.set_session_mode('seed_data');
1225
1226 l_flexfield := fnd_flex_key_api.find_flexfield
1227 ( appl_short_name => p_appl_short_name,
1228 flex_code => p_flex_code );
1229
1230 hr_utility.set_location('pay_ip_startup_util.create_key_flexfield ',20);
1231 BEGIN
1232
1233 l_structure := fnd_flex_key_api.find_structure
1234 ( flexfield => l_flexfield,
1235 structure_code => p_structure_code );
1236
1237 return l_structure.structure_number;
1238 hr_utility.set_location('pay_ip_startup_util.create_key_flexfield ',30);
1239 EXCEPTION
1240 WHEN NO_DATA_FOUND THEN
1241
1242 -- Bug 4544374. Check if the structure code exists with this title already.
1243 OPEN duplicate_structure_check(l_application_id, l_flexfield.flex_code, p_structure_title);
1244 FETCH duplicate_structure_check INTO l_exists;
1245
1246 IF duplicate_structure_check%FOUND then
1247 close duplicate_structure_check;
1248 fnd_message.set_name('PAY', 'PAY_34291_IP_BANK_STRUCT_EXIST');
1249 fnd_message.set_token('TITLE', p_structure_title);
1250 fnd_message.raise_error;
1251 END IF;
1252
1253 CLOSE duplicate_structure_check;
1254
1255 l_structure:=fnd_flex_key_api.new_structure
1256 (flexfield => l_flexfield,
1257 structure_code => p_structure_code,
1258 structure_title => p_structure_title,
1259 description => p_description,
1260 view_name => p_view_name,
1261 freeze_flag => p_freeze_flag,
1262 enabled_flag => p_enabled_flag,
1263 segment_separator => '.',
1264 cross_val_flag => p_cross_val_flag,
1265 freeze_rollup_flag => p_freeze_rollup_flag,
1266 dynamic_insert_flag => p_dynamic_insert_flag,
1267 shorthand_enabled_flag => p_shorthand_enabled_flag,
1268 shorthand_prompt => p_shorthand_prompt,
1269 shorthand_length => p_shorthand_length);
1270
1271 SELECT application_id
1272 INTO l_application_id
1273 FROM FND_APPLICATION
1274 WHERE application_short_name = p_appl_short_name;
1275
1276 SELECT NVL(MAX(ifs.id_flex_num),0) + 1
1277 INTO l_structure.structure_number
1278 FROM fnd_id_flex_structures ifs
1279 WHERE ifs.application_id = l_application_id
1280 AND ifs.id_flex_code = p_flex_code
1281 AND ifs.id_flex_num < 101;
1282
1283 fnd_flex_key_api.add_structure
1284 ( flexfield => l_flexfield,
1285 structure => l_structure );
1286
1287 RETURN l_structure.structure_number;
1288 END;
1289 hr_utility.set_location('pay_ip_startup_util.create_key_flexfield ',40);
1290 END create_key_flexfield;
1291
1292 -- ---------------------------------------------------------------------
1293 -- Procedure for creating the Flex field segments
1294 -- ---------------------------------------------------------------------
1295
1296 PROCEDURE create_flex_segments
1297 (p_appl_Short_Name IN VARCHAR2,
1298 p_flex_code IN VARCHAR2,
1299 p_structure_code IN VARCHAR2,
1300 p_segment_name IN VARCHAR2,
1301 p_column_name IN VARCHAR2,
1302 p_segment_number IN VARCHAR2,
1303 p_enabled_flag IN VARCHAR2,
1304 p_displayed_flag IN VARCHAR2,
1305 p_indexed_flag IN VARCHAR2,
1306 p_value_set IN VARCHAR2,
1307 p_display_size IN NUMBER,
1308 p_description_size IN NUMBER,
1309 p_concat_size IN NUMBER,
1310 p_lov_prompt IN VARCHAR2,
1311 p_window_prompt IN VARCHAR2
1312 ) IS
1313
1314 l_flexfield fnd_flex_key_api.flexfield_type;
1315 l_structure fnd_flex_key_api.structure_type;
1316 l_application_id NUMBER(15);
1317 l_flex_num NUMBER(15);
1318 l_segment fnd_flex_key_api.segment_type;
1319 BEGIN
1320
1321 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',10);
1322 fnd_flex_key_api.set_session_mode('seed_data');
1323 l_flexfield := fnd_flex_key_api.find_flexfield
1324 ( appl_short_name => p_appl_short_name,
1325 flex_code => p_flex_code );
1326
1327 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',11);
1328
1329 l_structure := fnd_flex_key_api.find_structure
1330 ( flexfield => l_flexfield,
1331 structure_code => p_structure_code );
1332
1333 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',12);
1334 begin
1335
1336 hr_utility.trace(p_segment_name);
1337 l_segment := fnd_flex_key_api.find_segment
1338 (
1339 flexfield => l_flexfield
1340 ,structure => l_structure
1341 ,segment_name => p_segment_name
1342 );
1343 exception
1344 when no_data_found then
1345 l_segment:= fnd_flex_key_api.new_segment
1346 (
1347 flexfield => l_flexfield
1348 ,structure => l_structure
1349 ,segment_name => p_segment_name
1350 ,description => null
1351 ,column_name => p_column_name
1352 ,segment_number => p_segment_number
1353 ,enabled_flag => p_enabled_flag
1354 ,displayed_flag => p_displayed_flag
1355 ,indexed_flag => p_indexed_flag
1356 ,value_set => p_value_set
1357 ,default_type => null
1358 ,default_value => null
1359 ,required_flag => 'N'
1360 ,security_flag => 'N'
1361 ,display_size => p_display_size
1362 ,description_size => p_description_size
1363 ,concat_size => p_concat_size
1364 ,lov_prompt => p_lov_prompt
1365 ,window_prompt => p_window_prompt
1366 );
1367
1368 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',13);
1369
1370 hr_utility.trace(p_segment_name);
1371 begin
1372 fnd_flex_key_api.add_segment
1373 (
1374 flexfield => l_flexfield
1375 ,structure => l_structure
1376 ,segment => l_segment
1377 );
1378 exception
1379 when others then
1380 hr_utility.trace(substr(fnd_flex_key_api.message,1,256));
1381 end;
1382 hr_utility.set_location('pay_ip_startup_util.create_key_segments ',14);
1383 fnd_flex_key_api.assign_qualifier
1384 (
1385 flexfield => l_flexfield
1386 ,structure => l_structure
1387 ,segment => l_segment
1388 ,flexfield_qualifier => 'ASSIGNMENT'
1389 ,enable_flag => 'Y'
1390 );
1391
1392 end;
1393 END create_flex_segments;
1394
1395
1396 -- ---------------------------------------------------------------------
1397 -- Procedure for creating the rule for Bank key flexfield.
1398 -- ---------------------------------------------------------------------
1399 PROCEDURE create_leg_rule
1400 (p_legislation_code IN VARCHAR2,
1401 p_Rule_Type IN VARCHAR2,
1402 p_Rule_mode IN VARCHAR2) IS
1403
1404 BEGIN
1405 hr_utility.set_location('pay_ip_startup_util.create_flex_leg_rule ',10);
1406
1407 INSERT INTO hr_s_legislation_rules
1408 ( legislation_code
1409 ,rule_type
1410 ,rule_mode)
1411 SELECT
1412 'ZZ'
1413 ,p_rule_type
1414 ,p_rule_mode
1415 FROM dual
1416 WHERE NOT EXISTS (SELECT NULL
1417 FROM hr_s_legislation_rules
1418 WHERE legislation_code = 'ZZ'
1419 AND rule_type = p_rule_type);
1420 IF SQL%NOTFOUND THEN
1421 UPDATE hr_s_legislation_rules SET
1422 rule_mode = p_rule_mode
1423 WHERE legislation_code = 'ZZ'
1424 AND rule_type = p_rule_type;
1425 END IF;
1426 hr_utility.set_location('pay_ip_startup_util.create_flex_leg_rule ',20);
1427 END;
1428
1429 -- ---------------------------------------------------------------------
1430 -- HR_S Tables are updated to the choosen legislation_code and currency
1431 -- ---------------------------------------------------------------------
1432 PROCEDURE update_shadow_tables
1433 (p_legislation_code IN VARCHAR2,
1434 p_currency_code IN VARCHAR2) IS
1435
1436 BEGIN
1437 hr_utility.set_location('pay_ip_startup_util.update_shadow_tables ',10);
1438 --Updating Element Classifications Table
1439
1440 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_ELEMENT_CLASSIFICATIONS',NULL);
1441 UPDATE hr_s_element_classifications
1442 SET legislation_code = p_legislation_code
1443 WHERE legislation_code = 'ZZ';
1444
1445 --Updating Balance Types Table
1446
1447 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_BALANCE_TYPES',NULL);
1448 UPDATE hr_s_BALANCE_TYPES
1449 SET legislation_code = p_legislation_code,
1450 currency_code = p_currency_code
1451 WHERE legislation_code = 'ZZ';
1452
1453 --Updating Defined Balances Table
1454
1455 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_DEFINED_BALANCES',NULL);
1456 UPDATE hr_s_defined_balances
1457 SET legislation_code = p_legislation_code
1458 WHERE legislation_code = 'ZZ';
1459
1460
1461 --Updating Balance Dimensions Table
1462
1463 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_BALANCE_DIMENSIONS',NULL);
1464 UPDATE hr_s_balance_dimensions
1465 SET legislation_code = p_legislation_code
1466 WHERE legislation_code = 'ZZ';
1467
1468 --Updating Legislation Rules Table
1469
1470 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_LEGISLATION_RULES',NULL);
1471 UPDATE hr_s_legislation_rules
1472 SET legislation_code = p_legislation_code
1473 WHERE legislation_code = 'ZZ';
1474
1475 --Unpadting Legislative Field Info
1476
1477 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_LEGISLATIVE_FIELD_INFO',NULL);
1478 UPDATE hr_s_legislative_field_info
1479 SET legislation_code = p_legislation_code
1480 WHERE legislation_code = 'ZZ';
1481
1482 --Updating Balance Classifications
1483
1484 write_log('LOG','PAY_34015_IP_UPD_TABLE','HR_S_BALANCE_CLASSIFICATIONS',NULL);
1485 UPDATE hr_s_balance_classifications
1486 SET legislation_code = p_legislation_code
1487 WHERE legislation_code = 'ZZ';
1488
1489 hr_utility.set_location('pay_ip_startup_util.update_shadow_tables ',20);
1490
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1494 END update_shadow_tables;
1495
1496 -- ---------------------------------------------------------------------
1497 -- A record for the choosen legislation rule is inserted in to History
1498 -- table as HR_LEGISLATION.INSTALL picks up the legislation_code from
1499 -- this history table
1500 -- ---------------------------------------------------------------------
1501 PROCEDURE insert_history_table
1502 (p_legislation_code IN VARCHAR2) IS
1503
1504 BEGIN
1505 hr_utility.set_location('pay_ip_startup_util.insert_history_table',10);
1506 INSERT INTO hr_s_history
1507 ( package_name
1508 ,date_of_export
1509 ,date_of_import
1510 ,status
1511 ,legislation_code)
1512 VALUES
1513 ( TO_CHAR(SYSDATE,'ddMonyyyy-hh:rr:ss') || '[' || p_legislation_code || ']'
1514 ,sysdate
1515 ,sysdate
1516 ,'HR_S tabes copied from reference account'
1517 ,p_legislation_code);
1518 hr_utility.set_location('pay_ip_startup_util.insert_history_table',20);
1519 END insert_history_table ;
1520
1521 -- ---------------------------------------------------------------------
1522 -- The data from Shadow tables are moved in to the main tables with the
1523 -- required legislation_code and currency, by HR_LEGISLATION.INSTALL
1524 -- ---------------------------------------------------------------------
1525 PROCEDURE move_to_main_tables IS
1526
1527 BEGIN
1528 hr_utility.set_location('pay_ip_startup_util.move_to_main_tables',10);
1529 hr_legislation.install;
1530
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1534
1535 hr_utility.set_location('pay_ip_startup_util.move_to_main_tables',20);
1536 END move_to_main_tables;
1537
1538 -- ---------------------------------------------------------------------
1539 -- Procedure to update the TL tables with the translated values.
1540 -- ---------------------------------------------------------------------
1541
1542 -- Updating Element Classifications TL Table
1543
1544 PROCEDURE update_ele_class_tl
1545 (p_legislation_code IN VARCHAR2) IS
1546
1547 CURSOR get_classid_btable_csr IS
1548 SELECT b.classification_id bid , b.classification_name bname,
1549 t.language, t.classification_name tname, t.description, t.source_lang
1550 FROM pay_element_classifications_tl t, pay_element_classifications b
1551 WHERE b.classification_id = t.classification_id
1552 AND b.legislation_code = 'ZZ'
1553 AND b.business_group_id IS NULL;
1554
1555
1556 CURSOR get_classid_tltable_csr(l_legislation_code VARCHAR2 , l_name VARCHAR2 , l_language VARCHAR2) IS
1557 SELECT t.classification_id tlid
1558 FROM pay_element_classifications_tl t, pay_element_classifications b
1559 WHERE b.classification_name = l_name
1560 AND b.legislation_code = l_legislation_code
1561 AND b.business_group_id is NULL
1562 AND t.classification_id = b.classification_id
1563 AND t.language = l_language;
1564
1565 rec_tltable_csr get_classid_tltable_csr%ROWTYPE;
1566
1567 BEGIN
1568
1569 hr_utility.set_location('pay_ip_startup_util.update_ele_class_tl',10);
1570
1571 FOR l_record in get_classid_btable_csr LOOP
1572
1573 OPEN get_classid_tltable_csr(p_legislation_code, l_record.bname, l_record.language);
1574 fetch get_classid_tltable_csr INTO rec_tltable_csr ;
1575
1576 if get_classid_tltable_csr%found then
1577
1578 UPDATE pay_element_classifications_tl
1579 SET classification_name = l_record.tname,
1580 description = l_record.description,
1581 source_lang = l_record.source_lang
1582 WHERE classification_id = rec_tltable_csr.tlid
1583 AND language = l_record.language;
1584
1585 end if;
1586
1587 CLOSE get_classid_tltable_csr;
1588
1589 END LOOP;
1590
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 IF get_classid_btable_csr%ISOPEN THEN
1594 CLOSE get_classid_btable_csr;
1595 END IF;
1596 IF get_classid_tltable_csr%ISOPEN THEN
1597 CLOSE get_classid_tltable_csr;
1598 END IF;
1599 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1600 hr_utility.set_location('pay_ip_startup_util.update_ele_class_tl',20);
1601 END update_ele_class_tl;
1602
1603 -- Updating Balance Types TL Table
1604
1605 PROCEDURE update_bal_type_tl
1606 (p_legislation_code IN VARCHAR2) IS
1607
1608 --old reference data ids
1609 CURSOR get_balid_btable_csr IS
1610 SELECT b.balance_type_id bid , b.balance_name bname ,
1611 t.language, t.balance_name tname, t.reporting_name, t.source_lang
1612 FROM pay_balance_types_tl t, pay_balance_types b
1613 WHERE t.balance_type_id = b.balance_type_id
1614 AND b.legislation_code = 'ZZ'
1615 AND b.business_group_id IS NULL ;
1616
1617 --ids for newly created data
1618 CURSOR get_balid_tltable_csr(l_legislation_code VARCHAR2, l_name VARCHAR2 , l_language VARCHAR2 ) IS
1619 SELECT t.balance_type_id tlid
1620 FROM pay_balance_types_tl t, pay_balance_types b
1621 WHERE b.balance_name = l_name
1622 AND b.legislation_code = l_legislation_code
1623 AND b.business_group_id IS NULL
1624 AND b.balance_type_id = t.balance_type_id
1625 AND t.language = l_language;
1626
1627 rec_tltable_csr get_balid_tltable_csr%ROWTYPE;
1628
1629 BEGIN
1630 hr_utility.set_location('pay_ip_startup_util.update_bal_type_tl',10);
1631
1632 FOR l_record in get_balid_btable_csr LOOP
1633
1634 OPEN get_balid_tltable_csr(p_legislation_code, l_record.bname, l_record.language);
1635 fetch get_balid_tltable_csr INTO rec_tltable_csr;
1636
1637 if get_balid_tltable_csr%found then
1638
1639 UPDATE pay_balance_types_tl
1640 SET balance_name = l_record.tname,
1641 reporting_name = l_record.reporting_name,
1642 source_lang = l_record.source_lang
1643 WHERE balance_type_id = rec_tltable_csr.tlid
1644 AND language = l_record.language;
1645
1646 end if;
1647
1648 CLOSE get_balid_tltable_csr;
1649
1650 END LOOP;
1651
1652 EXCEPTION
1653 WHEN OTHERS THEN
1654 IF get_balid_btable_csr%ISOPEN THEN
1655 CLOSE get_balid_btable_csr;
1656 END IF;
1657 IF get_balid_tltable_csr%ISOPEN THEN
1658 CLOSE get_balid_tltable_csr;
1659 END IF;
1660
1661 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1662
1663 hr_utility.set_location('pay_ip_startup_util.update_bal_type_tl',20);
1664
1665 END update_bal_type_tl;
1666
1667 PROCEDURE create_runtype
1668 (p_legislation_code IN VARCHAR2) IS
1669
1670 CURSOR run_type_csr IS
1671 SELECT
1672 run_type_id, run_type_name, run_method, effective_start_date, effective_end_date,
1673 business_group_id, legislation_code, shortname, last_update_date, last_updated_by,
1674 last_update_login, created_by, creation_date, object_version_number
1675 FROM pay_run_types_f
1676 WHERE nvl(legislation_code,'X') = 'ZZ'
1677 AND business_group_id IS NULL and sysdate between effective_start_date and effective_end_date;
1678
1679
1680 Cursor run_type_parent_csr (l_legislation_code IN VARCHAR2) IS
1681 SELECT run_type_id parent_id
1682 FROM pay_run_types_f
1683 WHERE (run_type_name,shortname) IN (SELECT RUN_TYPE_NAME, shortname from pay_run_types_f
1684 WHERE RUN_TYPE_ID IN (SELECT PARENT_RUN_TYPE_ID
1685 FROM pay_run_type_usages_f
1686 WHERE LEGISLATION_CODE = 'ZZ'
1687 AND sysdate BETWEEN EFFECTIVE_START_DATE
1688 AND EFFECTIVE_END_DATE
1689 AND business_group_id is null
1690 )
1691 AND sysdate BETWEEN effective_start_date AND effective_end_date
1692 )
1693 AND sysdate BETWEEN effective_start_date
1694 AND effective_end_date
1695 AND legislation_code = l_legislation_code
1696 AND business_group_id is NULL;
1697
1698 Cursor run_type_child_csr (l_legislation_code IN VARCHAR2) IS
1699 SELECT prtf1.run_type_id child_id , prtuf.sequence sequence , prtf1.run_type_name run_type_name
1700 FROM pay_run_types_f prtf1, pay_run_types_f prtf2 , pay_run_type_usages_f prtuf
1701 WHERE (prtf1.run_type_name,prtf1.shortname) IN (SELECT RUN_TYPE_NAME, shortname
1702 FROM pay_run_types_f
1703 WHERE RUN_TYPE_ID IN (SELECT child_RUN_TYPE_ID
1704 FROM pay_run_type_usages_f
1705 WHERE parent_run_type_id in (SELECT distinct PARENT_RUN_TYPE_ID
1706 FROM pay_run_type_usages_f
1707 WHERE LEGISLATION_CODE = 'ZZ'
1708 AND sysdate BETWEEN EFFECTIVE_START_DATE
1709 AND EFFECTIVE_END_DATE
1710 AND business_group_id is null)
1711 AND legislation_code = 'ZZ'
1712 AND sysdate BETWEEN EFFECTIVE_START_DATE
1713 AND EFFECTIVE_END_DATE
1714 AND business_group_id is null
1715 )
1716 AND sysdate BETWEEN effective_start_date AND effective_end_date
1717 )
1718 AND sysdate BETWEEN prtf1.effective_start_date
1719 AND prtf1.effective_end_date
1720 AND prtf1.legislation_code = l_legislation_code
1721 AND prtf1.business_group_id is NULL
1722 AND prtuf.legislation_code = 'ZZ'
1723 AND prtf2.RUN_TYPE_NAME = prtf1.RUN_TYPE_NAME
1724 AND prtf2.SHORTNAME = prtf1.SHORTNAME
1725 AND prtf2.business_group_id is NULL
1726 AND prtuf.legislation_code = prtf2.legislation_code
1727 AND prtf2.run_type_id in (prtuf.parent_run_type_id, prtuf.child_run_type_id);
1728
1729
1730 --local variables for Run Types
1731
1732 l_rt_id pay_run_types_f.run_type_id%TYPE;
1733 l_rt_ovn pay_run_types_f.object_version_number%TYPE;
1734 l_rt_eff_start_date pay_run_types_f.effective_start_date%TYPE;
1735 l_rt_eff_end_date pay_run_types_f.effective_end_date%TYPE;
1736
1737 --local variables for Run Type Usages
1738
1739 l_rtu_id pay_run_type_usages_f.run_type_usage_id%TYPE;
1740 l_rtu_ovn pay_run_type_usages_f.object_version_number%TYPE;
1741 l_rtu_eff_start_date pay_run_type_usages_f.effective_start_date%TYPE;
1742 l_rtu_eff_end_date pay_run_type_usages_f.effective_end_date%TYPE;
1743 l_process VARCHAR2(100);
1744
1745
1746 BEGIN
1747 -- Run Types
1748
1749 hr_utility.set_location('pay_ip_startup_util.create_runtype',10);
1750
1751 write_log('LOG',NULL,NULL,NULL);
1752 write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_RUN_TYPE_API',NULL);
1753
1754 hr_startup_data_api_support.enable_startup_mode('STARTUP');
1755 hr_startup_data_api_support.create_owner_definition('PAY');
1756
1757 FOR rec IN run_type_csr LOOP
1758
1759 BEGIN
1760 SELECT run_type_id, object_version_number
1761 INTO l_rt_id, l_rt_ovn
1762 FROM pay_run_types_f
1763 WHERE run_type_name = rec.run_type_name
1764 AND shortname = rec.shortname
1765 AND legislation_code = p_legislation_code
1766 AND sysdate between effective_start_date and effective_end_date
1767 AND business_group_id IS NULL;
1768
1769
1770 EXCEPTION
1771 WHEN NO_DATA_FOUND THEN
1772 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Run Type ', rec.run_type_name);
1773 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Run Type ', rec.run_type_name);
1774 pay_run_type_api.create_run_type (
1775 p_effective_date => g_start_of_time
1776 ,p_run_type_name => rec.run_type_name
1777 ,p_run_method => rec.run_method
1778 ,p_business_group_id => NULL
1779 ,p_legislation_code => p_legislation_code
1780 ,p_shortname => rec.shortname
1781 ,p_run_type_id => l_rt_id
1782 ,p_effective_start_date => l_rt_eff_start_date
1783 ,p_effective_end_date => l_rt_eff_end_date
1784 ,p_object_version_number => l_rt_ovn
1785 ) ;
1786 END;
1787
1788 END LOOP;
1789
1790 -- Run Type Usages
1791
1792 hr_utility.set_location('pay_ip_startup_util.create_runtype',20);
1793
1794 write_log('LOG',NULL,NULL,NULL);
1795 write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_RUN_TYPE_USAGE_API',NULL);
1796
1797 FOR rec_parent IN run_type_parent_csr (p_legislation_code) LOOP
1798 FOR rec_child IN run_type_child_csr(p_legislation_code) LOOP
1799
1800
1801 BEGIN
1802 SELECT run_type_usage_id, object_version_number
1803 INTO l_rtu_id, l_rtu_ovn
1804 FROM pay_run_type_usages_f
1805 WHERE parent_run_type_id = rec_parent.parent_id
1806 AND child_run_type_id = rec_child.child_id
1807 AND legislation_code = p_legislation_code
1808 AND sysdate between effective_start_date and effective_end_date
1809 AND business_group_id IS NULL;
1810 l_process := 'Run Type Usage :' || rec_child.run_type_name;
1811 write_log('LOG','PAY_34015_IP_UPD_TABLE',l_process,NULL);
1812 pay_run_type_usage_api.update_run_type_usage (
1813 p_effective_date => g_start_of_time
1814 ,p_datetrack_update_mode => 'CORRECTION'
1815 ,p_run_type_usage_id => l_rtu_id
1816 ,p_object_version_number => l_rtu_ovn
1817 ,p_sequence => rec_child.sequence
1818 ,p_business_group_id => NULL
1819 ,p_legislation_code => p_legislation_code
1820 ,p_effective_start_date => l_rtu_eff_start_date
1821 ,p_effective_end_date => l_rtu_eff_end_date
1822 ) ;
1823 EXCEPTION
1824 WHEN NO_DATA_FOUND THEN
1825 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Run Type Usage', rec_child.run_type_name);
1826 write_log ('OUTPUT','PAY_34012_IP_INS_DATA', 'Run Type Usage', rec_child.run_type_name);
1827 pay_run_type_usage_api.create_run_type_usage (
1828 p_effective_date => g_start_of_time
1829 ,p_parent_run_type_id => rec_parent.parent_id
1830 ,p_child_run_type_id => rec_child.child_id
1831 ,p_sequence => rec_child.sequence
1832 ,p_business_group_id => NULL
1833 ,p_legislation_code => p_legislation_code
1834 ,p_run_type_usage_id => l_rtu_id
1835 ,p_effective_start_date => l_rtu_eff_start_date
1836 ,p_effective_end_date => l_rtu_eff_end_date
1837 ,p_object_version_number => l_rtu_ovn
1838 );
1839 END;
1840
1841 END LOOP; --child
1842 END LOOP; --parent
1843
1844 hr_utility.set_location('pay_ip_startup_util.create_runtype',30);
1845
1846 EXCEPTION
1847 WHEN OTHERS THEN
1848 IF run_type_csr%ISOPEN THEN
1849 CLOSE run_type_csr;
1850 END IF;
1851
1852 IF run_type_parent_csr%ISOPEN THEN
1853 CLOSE run_type_parent_csr;
1854 END IF;
1855
1856 IF run_type_child_csr%ISOPEN THEN
1857 CLOSE run_type_child_csr;
1858 END IF;
1859
1860 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1861 END create_runtype;
1862 --
1863
1864 -- Bug 4159036. Create Balance Attribute Definitions
1865
1866 PROCEDURE create_bal_att_def
1867 (p_legislation_code in varchar2) IS
1868 --
1869 CURSOR get_bal_att_def IS
1870 SELECT
1871 attribute_name, alterable, user_attribute_name
1872 FROM pay_bal_attribute_definitions
1873 WHERE nvl(legislation_code,'X') = 'ZZ'
1874 AND business_group_id IS NULL;
1875 --
1876 BEGIN
1877 --
1878 hr_utility.set_location('pay_ip_startup_util.create_bal_att_def',10);
1879
1880 write_log('LOG',NULL,NULL,NULL);
1881 write_log ('LOG','PAY_34011_IP_INS_DATA_IN_TABLE', 'Balance Attribute Definitions', 'PAY_BAL_ATTRIBUTE_DEFINITIONS');
1882
1883 FOR rec IN get_bal_att_def LOOP
1884
1885 write_log ('LOG','PAY_34012_IP_INS_DATA', 'Balance Attribute Definition', rec.attribute_name);
1886
1887 PAY_BALANCES_UPLOAD_PKG.PAY_BAL_ADE_LOAD_ROW
1888 (
1889 p_ATTRIBUTE_NAME => rec.attribute_name
1890 ,p_LEGISLATION_CODE => p_legislation_code
1891 ,p_BUSINESS_GROUP_NAME => null
1892 ,p_ALTERABLE => rec.alterable
1893 ,p_user_attribute_name => rec.user_attribute_name
1894 ,p_OWNER => 'SEED'
1895 );
1896
1897 END LOOP;
1898
1899 hr_utility.set_location('pay_ip_startup_util.create_bal_att_def',20);
1900 --
1901 EXCEPTION
1902 when others then
1903 if get_bal_att_def%isopen then
1904 close get_bal_att_def;
1905 end if;
1906
1907 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1908 --
1909 END create_bal_att_def;
1910 --
1911
1912 -- Updating Run Types TL Table
1913
1914 PROCEDURE update_run_type_tl
1915 (p_legislation_code IN VARCHAR2) IS
1916
1917 CURSOR get_runid_btable_csr IS
1918 SELECT b.run_type_id bid , b.run_type_name bname,
1919 t.language, t.run_type_name tname, t.shortname, t.source_lang
1920 FROM pay_run_types_f_tl t, pay_run_types_f b
1921 WHERE t.run_type_id = b.run_type_id
1922 AND b.legislation_code = 'ZZ'
1923 AND b.business_group_id IS NULL
1924 AND sysdate BETWEEN b.effective_start_date AND b.effective_end_date ;
1925
1926 CURSOR get_runid_tltable_csr(l_legislation_code VARCHAR2, l_name VARCHAR2, l_language VARCHAR2) IS
1927 SELECT t.run_type_id tlid
1928 FROM pay_run_types_f_tl t, pay_run_types_f b
1929 WHERE b.run_type_name = l_name
1930 AND b.legislation_code = l_legislation_code
1931 AND b.business_group_id IS NULL
1932 AND t.run_type_id = b.run_type_id
1933 AND t.language = l_language ;
1934
1935 rec_tltable_csr get_runid_tltable_csr%ROWTYPE;
1936
1937 BEGIN
1938
1939 hr_utility.set_location('pay_ip_startup_util.update_run_type_tl',10);
1940
1941 FOR l_record in get_runid_btable_csr LOOP
1942
1943 OPEN get_runid_tltable_csr(p_legislation_code, l_record.bname, l_record.language) ;
1944 fetch get_runid_tltable_csr INTO rec_tltable_csr ;
1945
1946 if get_runid_tltable_csr%found then
1947
1948 UPDATE pay_run_types_f_tl
1949 SET run_type_name = l_record.tname,
1950 shortname = l_record.shortname,
1951 source_lang = l_record.source_lang
1952 WHERE run_type_id = rec_tltable_csr.tlid
1953 AND language = l_record.language;
1954
1955 end if;
1956
1957 CLOSE get_runid_tltable_csr;
1958
1959 END LOOP;
1960
1961 hr_utility.set_location('pay_ip_startup_util.update_run_type_tl',20);
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 IF get_runid_btable_csr%ISOPEN THEN
1965 CLOSE get_runid_btable_csr;
1966 END IF;
1967 IF get_runid_tltable_csr%ISOPEN THEN
1968 CLOSE get_runid_tltable_csr;
1969 END IF;
1970 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
1971
1972 END update_run_type_tl;
1973
1974 -- ----------------------------------------------------------------------
1975 -- Main Procedure through which all process is done in the required order
1976 -- This Setup gets the values for legislation_code and Currency from
1977 -- the concurrent request and creates the required data.
1978 -- ----------------------------------------------------------------------
1979
1980 PROCEDURE setup (p_errbuf OUT NOCOPY VARCHAR2,
1981 p_retcode OUT NOCOPY NUMBER,
1982 p_legislation_code IN VARCHAR2,
1983 p_currency_code IN VARCHAR2,
1984 p_Tax_Year IN VARCHAR2,
1985 p_install_tax_unit IN VARCHAR2,
1986 p_action_parameter_group_id IN NUMBER) IS
1987
1988 l_id_flex_num NUMBER(15);
1989 l_Tax_Year DATE;
1990 l_territory FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%TYPE;
1991 l_territory_with_code VARCHAR2(100);
1992 l_payroll_installed VARCHAR2(1);
1993 l_segment_used NUMBER;--Bug#4938455. Changed varchar2 to number.
1994 l_structure_code fnd_id_flex_structures.id_flex_structure_code%type;
1995
1996 CURSOR csr_payroll_installed is
1997 select 1 from fnd_product_installations
1998 where application_id = 801
1999 and status = 'I';
2000
2001 cursor csr_flex_struct (p_id_flex_num number) is
2002 select id_flex_structure_code
2003 from fnd_id_flex_structures
2004 where id_flex_code = 'SCL'
2005 and id_flex_num = p_id_flex_num;
2006
2007 BEGIN
2008
2009 hr_utility.set_location('pay_ip_startup_util.setup',10);
2010 IF check_to_install(p_legislation_code) THEN
2011
2012 -- Check if logging of message is required.
2013 g_logging := logging(p_action_parameter_group_id);
2014
2015 SELECT territory_short_name, territory_short_name || ' (' || territory_code || ')'
2016 INTO l_territory, l_territory_with_code
2017 FROM fnd_territories_vl
2018 WHERE territory_code = p_legislation_code;
2019
2020
2021 --Clearing all HRMS HR_S tables
2022 write_log('OUTPUT','PAY_34022_IP_LEG_INS_BEGINS', l_territory_with_code ,to_char(sysdate,'dd-Mon-yyyy hh:mi:ss'));
2023 hr_utility.set_location('pay_ip_startup_util.setup',20);
2024 write_log('LOG',NULL,NULL,NULL);
2025 write_log('LOG','PAY_34000_IP_TRUNCATE_TABLES','HR_S%',NULL);
2026 clear_shadow_tables;
2027
2028 --Moving data to shadow table
2029
2030 hr_utility.set_location('pay_ip_startup_util.setup',30);
2031 write_log('LOG',NULL,NULL,NULL);
2032 write_log('LOG','PAY_34009_IP_MOVE_TO_HR_S',NULL,NULL);
2033 move_to_shadow_tables(p_legislation_code, p_install_tax_unit);
2034
2035 l_Tax_Year := fnd_date.canonical_to_date(p_Tax_Year);
2036
2037 write_log('LOG',NULL,NULL,NULL);
2038 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'Tax Year', NULL);
2039 create_leg_rule(p_legislation_code => p_legislation_code,
2040 p_rule_type => 'L',
2041 p_rule_mode => to_char(l_Tax_Year,'dd/mm'));
2042 --
2043 -- Inserted legislation rule for currency. Bug No 3720975.
2044 --
2045 write_log('LOG',NULL,NULL,NULL);
2046 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'DC', NULL);
2047 create_leg_rule(p_legislation_code => p_legislation_code,
2048 p_rule_type => 'DC',
2049 p_rule_mode => p_currency_code);
2050
2051
2052 open csr_payroll_installed;
2053 fetch csr_payroll_installed into l_payroll_installed;
2054
2055 if csr_payroll_installed%found then
2056 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'I', NULL);
2057 create_leg_rule(p_legislation_code => p_legislation_code,
2058 p_rule_type => 'I',
2059 p_rule_mode => 'N');
2060 end if;
2061
2062 close csr_payroll_installed;
2063
2064 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'Run Type', NULL);
2065 create_leg_rule(p_legislation_code => p_legislation_code,
2066 p_rule_type => 'RUN_TYPE_FLAG',
2067 p_rule_mode => 'Y');
2068
2069 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'Tax Unit', NULL);
2070 create_leg_rule(p_legislation_code => p_legislation_code,
2071 p_rule_type => 'TAX_UNIT',
2072 p_rule_mode => 'N');
2073
2074 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'BAL_INIT_VALIDATION', NULL);
2075 create_leg_rule(p_legislation_code => p_legislation_code,
2076 p_rule_type => 'BAL_INIT_VALIDATION',
2077 p_rule_mode => 'N');
2078
2079 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'ACTION_CONTEXTS', NULL);
2080 create_leg_rule(p_legislation_code => p_legislation_code,
2081 p_rule_type => 'ACTION_CONTEXTS',
2082 p_rule_mode => 'Y');
2083
2084 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'PAYWSACT_SOE', NULL);
2085 create_leg_rule(p_legislation_code => p_legislation_code,
2086 p_rule_type => 'PAYWSACT_SOE',
2087 p_rule_mode => 'N');
2088
2089 -- Setting the rule mode of PAYWSRQP_DS and SOE to 'Y' for bug 3286741
2090
2091 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'PAYWSRQP_DS', NULL);
2092 create_leg_rule(p_legislation_code => p_legislation_code,
2093 p_rule_type => 'PAYWSRQP_DS',
2094 p_rule_mode => 'Y');
2095
2096 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'SOE', NULL);
2097 create_leg_rule(p_legislation_code => p_legislation_code,
2098 p_rule_type => 'SOE',
2099 p_rule_mode => 'Y');
2100
2101 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'RETROELEMENT_CHECK', NULL);
2102 create_leg_rule(p_legislation_code => p_legislation_code,
2103 p_rule_type => 'RETROELEMENT_CHECK',
2104 p_rule_mode => 'Y');
2105
2106 IF p_install_tax_unit = 'Y' THEN
2107
2108 create_leg_rule(p_legislation_code => p_legislation_code,
2109 p_rule_type => 'TAX_UNIT',
2110 p_rule_mode => 'Y');
2111
2112 hr_utility.set_location('pay_ip_startup_util.setup',40);
2113 write_log('LOG',NULL,NULL,NULL);
2114 write_log('LOG','PAY_34007_IP_CREATE_FLEX',p_Legislation_Code ||
2115 '_STATUTORY_INFO', NULL);
2116
2117 /* Identify if a non standard structure has already been
2118 created for this legislation (Bugfix 3070623). If so, then
2119 skip the creation of the flexfields and set a flag to ensure the
2120 segment creation is also skipped */
2121
2122 SELECT MIN(id_flex_num)
2123 INTO l_id_flex_num
2124 FROM fnd_id_flex_structures
2125 WHERE id_flex_code = 'SCL'
2126 AND id_flex_structure_code like
2127 p_legislation_code||'_STATUTORY_INFO'||'%';
2128
2129 IF l_id_flex_num IS NULL THEN
2130 l_id_flex_num := create_key_flexfield
2131 (p_appl_short_name => 'PER',
2132 p_flex_code => 'SCL',
2133 p_structure_code => p_legislation_code || '_STATUTORY_INFO',
2134 p_structure_title => p_legislation_code || ' Statutory Info.',
2135 p_description => 'SCL KeyFlex Structure for ' || l_territory,
2136 p_view_name => '',
2137 p_freeze_flag => 'Y',
2138 p_enabled_flag => 'Y',
2139 p_cross_val_flag => 'Y',
2140 p_freeze_rollup_flag => 'N',
2141 p_dynamic_insert_flag => 'Y',
2142 p_shorthand_enabled_flag => 'N',
2143 p_shorthand_prompt => '',
2144 p_shorthand_length => 10);
2145 END IF;
2146
2147 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'S', NULL);
2148 create_leg_rule
2149 (p_legislation_code => p_legislation_code,
2150 p_rule_type => 'S',
2151 p_rule_mode => l_id_flex_num);
2152
2153 write_log('LOG','PAY_34008_IP_INS_LEG_RULE', 'SDL', NULL);
2154 create_leg_rule
2155 (p_legislation_code => p_legislation_code,
2156 p_rule_type => 'SDL',
2157 p_rule_mode => 'A');
2158
2159 /* Only attempt the creation of the Tax Unit in Segment1 if this
2160 segment is not already in use */
2161
2162 select min(1)
2163 into l_segment_used
2164 FROM fnd_id_flex_segments
2165 WHERE id_flex_num = l_id_flex_num
2166 AND id_flex_code = 'SCL'
2167 AND application_column_name = 'SEGMENT1';
2168
2169 IF l_segment_used IS NULL THEN
2170
2171 -- Bug 4544374. The structure code needs to be derived
2172 -- since the process may be trying to re-use the customer defined
2173 -- structure.
2174
2175 open csr_flex_struct(p_id_flex_num => l_id_flex_num);
2176 fetch csr_flex_struct into l_structure_code;
2177 close csr_flex_struct;
2178
2179 create_flex_segments
2180 (p_appl_Short_Name => 'PER',
2181 p_flex_code => 'SCL',
2182 p_structure_code => l_structure_code,
2183 p_segment_name => 'Tax_Unit',
2184 p_column_name => 'SEGMENT1',
2185 p_segment_number => 1,
2186 p_enabled_flag => 'Y',
2187 p_displayed_flag => 'Y',
2188 p_indexed_flag => 'Y',
2189 p_value_set => 'HR_TAX_UNIT_NAME',
2190 p_display_size => 25,
2191 p_description_size => 25,
2192 p_concat_size => 25,
2193 p_lov_prompt => 'Tax Unit',
2194 p_window_prompt => 'Tax Unit');
2195 END IF;
2196
2197 hr_utility.set_location('pay_ip_startup_util.setup',50);
2198 END IF;
2199
2200 --Create FlexField
2201
2202 hr_utility.set_location('pay_ip_startup_util.setup',40);
2203 write_log('LOG',NULL,NULL,NULL);
2204 write_log('LOG','PAY_34007_IP_CREATE_FLEX',p_Legislation_Code || '_BANK_DETAILS', NULL);
2205 l_id_flex_num := create_key_flexfield
2206 (p_appl_short_name => 'PAY',
2207 p_flex_code => 'BANK',
2208 p_structure_code => p_legislation_code || '_BANK_DETAILS',
2209 p_structure_title => p_legislation_code || ' Bank Details',
2210 p_description => p_legislation_code || ' Bank Details',
2211 p_view_name => '',
2212 p_freeze_flag => 'Y',
2213 p_enabled_flag => 'Y',
2214 p_cross_val_flag => 'Y',
2215 p_freeze_rollup_flag => 'N',
2216 p_dynamic_insert_flag => 'Y',
2217 p_shorthand_enabled_flag => 'N',
2218 p_shorthand_prompt => '',
2219 p_shorthand_length => 10);
2220
2221 hr_utility.set_location('pay_ip_startup_util.setup',50);
2222 write_log('LOG','PAY_34008_IP_INS_LEG_RULE',p_Legislation_Code || '_BANK_DETAILS', NULL);
2223 create_leg_rule
2224 (p_legislation_code => p_legislation_code,
2225 p_rule_type => 'E',
2226 p_rule_mode => l_id_flex_num);
2227
2228 write_out;
2229
2230 --Updating shadow table
2231
2232 hr_utility.set_location('pay_ip_startup_util.setup',60);
2233 write_log('LOG',NULL,NULL,NULL);
2234 write_log('LOG','PAY_34014_IP_UPD_LEG_CURR',p_Legislation_Code,p_currency_code);
2235 update_shadow_tables(p_legislation_code, p_currency_code);
2236
2237 -- Inserting in to HR_S_HISTORY table
2238
2239 hr_utility.set_location('pay_ip_startup_util.setup',70);
2240 write_log('LOG',NULL,NULL,NULL);
2241 write_log('LOG','PAY_34011_IP_INS_DATA_IN_TABLE','record','HR_S_HISTORY');
2242 insert_history_table(p_legislation_code);
2243
2244 -- Moving to Main Tables
2245 hr_utility.set_location('pay_ip_startup_util.setup',80);
2246 write_log('LOG',NULL,NULL,NULL);
2247 write_log('LOG','PAY_34016_IP_CALL_PROC','HR_LEGISLATION.INSTALL',NULL);
2248 BEGIN
2249 move_to_main_tables;
2250 EXCEPTION
2251 WHEN OTHERS THEN
2252 g_logging := 'Y';
2253 write_log('LOG','PAY_34018_IP_HR_STU_EXCEPTION',NULL,NULL);
2254 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
2255 END;
2256
2257 write_log('LOG',NULL,NULL,NULL);
2258 write_log('LOG','PAY_34017_IP_UPD_TL_TABLE',NULL,NULL);
2259
2260 hr_utility.set_location('pay_ip_startup_util.setup',90);
2261 write_log('LOG','PAY_34015_IP_UPD_TABLE','PAY_ELEMENT_CLASSIFICATIONS_TL',NULL);
2262 update_ele_class_tl(p_legislation_code);
2263
2264 hr_utility.set_location('pay_ip_startup_util.setup',100);
2265 write_log('LOG','PAY_34015_IP_UPD_TABLE','PAY_BALANCE_TYPES_TL',NULL);
2266 update_bal_type_tl(p_legislation_code);
2267
2268 --Run Types
2269 hr_utility.set_location('pay_ip_startup_util.setup',110);
2270 create_runtype(p_legislation_code);
2271
2272 hr_utility.set_location('pay_ip_startup_util.setup',120);
2273 write_log('LOG','PAY_34015_IP_UPD_TABLE','PAY_RUN_TYPES_TL',NULL);
2274 update_run_type_tl(p_legislation_code);
2275
2276 -- Bug 4159036. Deliver Balance Attribute Definitions
2277
2278 -- Balance Attribute Definitions
2279 hr_utility.set_location('pay_ip_startup_util.setup',130);
2280 create_bal_att_def(p_legislation_code);
2281
2282 -- Element Templates and other data needed for the legislation to use
2283 -- Element Design Wizard(EDW)
2284
2285 hr_utility.set_location('pay_ip_startup_util.setup', 135);
2286 pay_create_elemnt_tmplt_record.create_all_templates
2287 (p_legislation_code,p_currency_code);
2288
2289 p_retcode := 0;
2290
2291 COMMIT;
2292
2293 write_log('OUTPUT',NULL,NULL,NULL);
2294 write_log('OUTPUT','PAY_34023_IP_LEG_INS_ENDS',NULL,NULL);
2295
2296 hr_utility.set_location('pay_ip_startup_util.setup',140);
2297
2298 ELSE
2299 p_retcode := 2;
2300 END IF;
2301
2302 hr_utility.set_location('pay_ip_startup_util.setup',150);
2303
2304 EXCEPTION
2305 WHEN OTHERS THEN
2306 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
2307 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
2308 ROLLBACK;
2309 p_errbuf := NULL;
2310 p_retcode := 2;
2311 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
2312 END setup;
2313
2314 END pay_ip_startup_util;