[Home] [Help]
PACKAGE BODY: APPS.PAY_USER_TABLE_DETAILS_PKG
Source
1 PACKAGE BODY pay_user_table_details_pkg AS
2 /* $Header: pyutabdp.pkb 120.2 2007/12/19 07:39:11 rsaharay noship $ */
3 g_product_code VARCHAR2(5);
4
5 PROCEDURE perform_validations
6 (
7 X_VIEW_NAME IN VARCHAR2,
8 X_PRODUCT_CODES IN VARCHAR2,
9 X_LEGISLATION_CODE IN VARCHAR2,
10 X_USER_TABLE_NAME IN VARCHAR2
11 ) IS
12 CURSOR get_leg_view(X_VIEW_NAME VARCHAR2)
13 IS
14 SELECT 'Y'
15 FROM user_views
16 WHERE view_name = UPPER(X_VIEW_NAME);
17
18 CURSOR chk_installation(p_start NUMBER)
19 IS
20 SELECT 'Y',SUBSTR(X_PRODUCT_CODES,p_start,3)
21 FROM hr_legislation_installations
22 WHERE legislation_code = X_LEGISLATION_CODE
23 AND application_short_name = SUBSTR(X_PRODUCT_CODES,p_start,3);
24
25 CURSOR chk_table_at_bg
26 IS
27 SELECT user_table_id
28 FROM pay_user_tables
29 WHERE user_table_name = X_USER_TABLE_NAME
30 AND business_group_id IS NOT NULL;
31
32 l_temp VARCHAR2(1);
33 l_user_table_id NUMBER;
34
35 BEGIN
36 IF (x_view_name IS NOT NULL)
37 THEN
38 --
39 -- is running in hrglobal, so run the legislation view check
40 --
41 OPEN get_leg_view(X_VIEW_NAME);
42 FETCH get_leg_view INTO l_temp;
43 IF (get_leg_view%FOUND)
44 THEN
45 g_upload := TRUE;
46 ELSE
47 g_upload := FALSE;
48 END IF;
49 CLOSE get_leg_view;
50 ELSE
51 -- Not Running from hrglobal
52 g_upload := TRUE;
53 END IF;
54 -- Now check the Products installed for this legislation
55 IF (g_upload)
56 THEN
57 l_temp := 'N';
58 OPEN chk_installation(0);
59 FETCH chk_installation INTO l_temp,g_product_code;
60 CLOSE chk_installation;
61
62 IF (l_temp = 'N')
63 THEN
64 OPEN chk_installation(5);
65 FETCH chk_installation INTO l_temp,g_product_code;
66 CLOSE chk_installation;
67 END IF;
68
69 IF (l_temp = 'N')
70 THEN
71 OPEN chk_installation(9);
72 FETCH chk_installation INTO l_temp,g_product_code;
73 CLOSE chk_installation;
74 END IF;
75
76 IF (l_temp = 'N')
77 THEN
78 g_upload := FALSE;
79 ELSE
80 OPEN chk_table_at_bg;
81 FETCH chk_table_at_bg INTO l_user_table_id;
82 CLOSE chk_table_at_bg;
83
84 IF (l_user_table_id IS NOT NULL)
85 THEN
86 INSERT INTO hr_stu_exceptions(TABLE_NAME,SURROGATE_ID, EXCEPTION_TEXT,TRUE_KEY)
87 VALUES(X_USER_TABLE_NAME,l_user_table_id,'User Table: '|| X_USER_TABLE_NAME ||' already exists at BG level.',NULL);
88 g_upload := FALSE;
89 ELSE
90 g_upload := TRUE;
91 g_user_table_name := X_USER_TABLE_NAME;
92 END IF;
93 END IF;
94 END IF;
95
96 IF (g_upload)
97 THEN
98 hr_startup_data_api_support.enable_startup_mode('STARTUP');
99 hr_startup_data_api_support.delete_owner_definitions;
100 hr_startup_data_api_support.create_owner_definition(g_product_code);
101 END IF;
102 END perform_validations;
103
104 PROCEDURE user_table_upd_ins
105 (
106 X_USER_TABLE_NAME IN VARCHAR2,
107 X_USER_ROW_TITLE IN VARCHAR2,
108 X_LEGISLATION_CODE IN VARCHAR2,
109 X_RANGE_OR_MATCH IN VARCHAR2,
110 X_USER_KEY_UNITS IN VARCHAR2,
111 X_OWNER IN VARCHAR2,
112 X_LEG_VIEW IN VARCHAR2,
113 X_PRODUCT_CODE IN VARCHAR2
114 ) IS
115
116 l_object_version_number NUMBER;
117 l_user_table_id NUMBER;
118 table_at_bg_exists EXCEPTION;
119
120 BEGIN
121 perform_validations(X_LEG_VIEW,
122 X_PRODUCT_CODE,
123 X_LEGISLATION_CODE,
124 X_USER_TABLE_NAME
125 );
126 IF (g_upload AND (g_user_table_name = X_USER_TABLE_NAME))
127 THEN
128
129 SELECT user_table_id
130 ,object_version_number
131 INTO l_user_table_id
132 ,l_object_version_number
133 FROM pay_user_tables
134 WHERE user_table_name = X_USER_TABLE_NAME
135 AND (
136 legislation_code = X_LEGISLATION_CODE
137 OR
138 legislation_code IS NULL
139 )
140 AND business_group_id IS NULL;
141
142 pay_user_table_api.update_user_table
143 (p_validate => FALSE
144 ,p_user_table_id => l_user_table_id
145 ,p_effective_date => SYSDATE
146 ,p_user_table_name => X_USER_TABLE_NAME
147 ,p_user_row_title => X_USER_ROW_TITLE
148 ,p_object_version_number => l_object_version_number
149 );
150 END IF ;
151 EXCEPTION
152 WHEN NO_DATA_FOUND
153 THEN
154 pay_user_table_api.create_user_table
155 (p_validate => FALSE
156 ,p_effective_date => sysdate
157 ,p_business_group_id => NULL
158 ,p_legislation_code => X_LEGISLATION_CODE
159 ,p_range_or_match => X_RANGE_OR_MATCH
160 ,p_user_key_units => X_USER_KEY_UNITS
161 ,p_user_table_name => X_USER_TABLE_NAME
162 ,p_user_row_title => X_USER_ROW_TITLE
163 ,p_user_table_id => l_user_table_id
164 ,p_object_version_number => l_object_version_number
165 );
166 END user_table_upd_ins;
167
168
169 PROCEDURE user_row_upd_ins
170 (
171 X_USER_TABLE_NAME IN VARCHAR2,
172 X_LEGISLATION_CODE IN VARCHAR2,
173 X_ROW_LOW_RANGE_OR_NAME IN VARCHAR2,
174 X_ROW_HIGH_RANGE IN VARCHAR2,
175 X_EFFECTIVE_START_DATE IN VARCHAR2,
176 X_EFFECTIVE_END_DATE IN VARCHAR2,
177 X_DISPLAY_SEQUENCE IN VARCHAR2,
178 X_OWNER IN VARCHAR2,
179 X_LEG_VIEW IN VARCHAR2
180 ) IS
181 CURSOR c_table_id
182 IS
183 SELECT user_table_id
184 FROM pay_user_tables
185 WHERE user_table_name = X_USER_TABLE_NAME
186 AND (
187 legislation_code = X_LEGISLATION_CODE
188 OR
189 legislation_code IS NULL
190 )
191 AND business_group_id IS NULL;
192
193
194 CURSOR c_get_col_instance_id (p_user_row_id NUMBER)
195 IS
196 SELECT val.user_column_instance_id column_instance_id,
197 val.object_version_number object_version_number
198 FROM pay_user_tables put,
199 pay_user_rows_f pur,
200 pay_user_columns puc,
201 pay_user_column_instances_f val
202 WHERE val.user_row_id = pur.user_row_id
203 AND val.user_row_id = p_user_row_id
204 AND val.user_column_id = puc.user_column_id
205 AND pur.user_table_id = put.user_table_id
206 AND puc.user_table_id = put.user_table_id
207 AND (
208 pur.row_low_range_or_name = X_ROW_LOW_RANGE_OR_NAME
209 AND
210 NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
211 )
212 AND put.user_table_name = X_USER_TABLE_NAME
213 AND(
214 (
215 put.legislation_code IS NULL
216 AND val.legislation_code IS NULL
217 AND pur.legislation_code IS NULL
218 )
219 OR
220 (
221 put.legislation_code = X_LEGISLATION_CODE
222 AND val.legislation_code = X_LEGISLATION_CODE
223 AND pur.legislation_code = X_LEGISLATION_CODE
224 )
225 )
226 AND fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE) < val.effective_end_date;
227
228
229
230 l_user_row_id NUMBER;
231 l_display_sequence NUMBER;
232 l_user_table_id NUMBER;
233 l_start_date DATE;
234 l_end_date DATE;
235 l_effective_start_date DATE;
236 l_effective_end_date DATE;
237 l_object_version_number NUMBER;
238
239 BEGIN
240 l_display_sequence:=X_DISPLAY_SEQUENCE;
241
242 IF (g_upload AND (g_user_table_name = X_USER_TABLE_NAME))
243 THEN
244
245
246 OPEN c_table_id;
247 FETCH c_table_id INTO l_user_table_id;
248 CLOSE c_table_id;
249
250 SELECT user_row_id
251 ,object_version_number
252 ,effective_start_date
253 ,effective_end_date
254 INTO l_user_row_id
255 ,l_object_version_number
256 ,l_effective_start_date
257 ,l_effective_end_date
258 FROM pay_user_rows_f
259 WHERE (
260 row_low_range_or_name = X_ROW_LOW_RANGE_OR_NAME
261 AND
262 NVL(row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
263 )
264 AND (
265 legislation_code = X_LEGISLATION_CODE
266 OR
267 legislation_code IS NULL
268 )
269 AND business_group_id IS NULL
270 AND user_table_id = l_user_table_id
271 AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE) BETWEEN effective_start_date AND effective_end_date;
272
273
274 IF (l_effective_start_date <> fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE))
275 THEN
276 pay_user_row_api.update_user_row
277 (p_validate => FALSE
278 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
279 ,p_datetrack_update_mode => hr_api.g_update
280 ,p_user_row_id => l_user_row_id
281 ,p_display_sequence => l_display_sequence
282 ,p_object_version_number => l_object_version_number
283 ,p_row_low_range_or_name => X_ROW_LOW_RANGE_OR_NAME
284 ,p_base_row_low_range_or_name => X_ROW_LOW_RANGE_OR_NAME
285 ,p_disable_range_overlap_check => TRUE
286 ,p_disable_units_check => FALSE
287 ,p_row_high_range => X_ROW_HIGH_RANGE
288 ,p_effective_start_date => l_start_date
289 ,p_effective_end_date => l_end_date
290 );
291
292 END IF;
293
294 IF (l_effective_end_date <> fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE))
295 THEN
296 FOR c_rec IN c_get_col_instance_id(l_user_row_id)
297 LOOP
298 pay_user_column_instance_api.delete_user_column_instance
299 (p_validate => FALSE
300 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
301 ,p_user_column_instance_id => c_rec.column_instance_id
302 ,p_datetrack_update_mode => hr_api.g_delete
303 ,p_object_version_number => c_rec.object_version_number
304 ,p_effective_start_date => l_start_date
305 ,p_effective_end_date => l_end_date
306 );
307 END LOOP;
308
309 pay_user_row_api.delete_user_row
310 (p_validate => FALSE
311 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
312 ,p_datetrack_update_mode => hr_api.g_delete
313 ,p_user_row_id => l_user_row_id
314 ,p_object_version_number => l_object_version_number
315 ,p_disable_range_overlap_check => FALSE
316 ,p_effective_start_date => l_start_date
317 ,p_effective_end_date => l_end_date
318 );
319 END IF;
320
321 END IF;
322
323 EXCEPTION
324 WHEN NO_DATA_FOUND
325 THEN
326 l_display_sequence := X_DISPLAY_SEQUENCE;
327
328 pay_user_row_api.create_user_row
329 (p_validate => FALSE
330 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
331 ,p_user_table_id => l_user_table_id
332 ,p_row_low_range_or_name => X_ROW_LOW_RANGE_OR_NAME
333 ,p_display_sequence => l_display_sequence
334 ,p_business_group_id => NULL
335 ,p_legislation_code => X_LEGISLATION_CODE
336 ,p_disable_range_overlap_check => TRUE
337 ,p_disable_units_check => FALSE
338 ,p_row_high_range => X_ROW_HIGH_RANGE
339 ,p_user_row_id => l_user_row_id
340 ,p_object_version_number => l_object_version_number
341 ,p_effective_start_date => l_start_date
342 ,p_effective_end_date => l_end_date
343 );
344
345 IF (SUBSTR(X_EFFECTIVE_END_DATE,0,4) <> '4712')
346 THEN
347 pay_user_row_api.delete_user_row
348 (p_validate => FALSE
349 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
350 ,p_datetrack_update_mode => hr_api.g_delete
351 ,p_user_row_id => l_user_row_id
352 ,p_object_version_number => l_object_version_number
353 ,p_disable_range_overlap_check => FALSE
354 ,p_effective_start_date => l_start_date
355 ,p_effective_end_date => l_end_date
356 );
357
358 END IF;
359
360 END user_row_upd_ins;
361
362 PROCEDURE column_row_upd_ins
363 (
364 X_USER_TABLE_NAME IN VARCHAR2,
365 X_LEGISLATION_CODE IN VARCHAR2,
366 X_USER_COLUMN_NAME IN VARCHAR2,
367 X_FORMULA_NAME IN VARCHAR2,
368 X_FORMULA_LEG_CODE IN VARCHAR2,
369 X_OWNER IN VARCHAR2,
370 X_LEG_VIEW IN VARCHAR2
371 ) IS
372 CURSOR c_table_id
373 IS
374 SELECT user_table_id
375 FROM pay_user_tables
376 WHERE user_table_name = X_USER_TABLE_NAME
377 AND (
378 legislation_code = X_LEGISLATION_CODE
379 OR
380 legislation_code IS NULL
381 );
382
383 CURSOR c_get_formula_id
384 IS
385 SELECT ff.formula_id
386 FROM ff_formula_types fft
387 ,ff_formulas_f ff
388 WHERE fft.formula_type_name = 'User Table Validation'
389 AND fft.formula_type_id = ff.formula_type_id
390 AND ff.formula_name = X_FORMULA_NAME
391 AND (
392 X_FORMULA_LEG_CODE IS NULL
393 OR
394 ff.legislation_code = X_FORMULA_LEG_CODE
395 );
396
397 l_warning BOOLEAN;
398 l_user_column_id NUMBER;
399 l_user_table_id NUMBER;
400 l_formula_id NUMBER := NULL;
401 l_object_version_number NUMBER;
402
403 BEGIN
404
405 IF (g_upload AND (g_user_table_name = X_USER_TABLE_NAME))
406 THEN
407
408 OPEN c_table_id;
409 FETCH c_table_id INTO l_user_table_id;
410 CLOSE c_table_id;
411
412 IF (X_FORMULA_NAME IS NOT NULL)
413 THEN
414 OPEN c_get_formula_id;
415 FETCH c_get_formula_id INTO l_formula_id;
416 CLOSE c_get_formula_id;
417 END IF;
418
419 SELECT user_column_id
420 ,object_version_number
421 INTO l_user_column_id
422 ,l_object_version_number
423 FROM pay_user_columns
424 WHERE user_column_name = X_USER_COLUMN_NAME
425 AND (
426 legislation_code = X_LEGISLATION_CODE
427 OR
428 legislation_code IS NULL
429 )
430 AND user_table_id = l_user_table_id
431 AND business_group_id IS NULL;
432
433 pay_user_column_api.update_user_column
434 (p_validate => FALSE
435 ,p_user_column_id => l_user_column_id
436 ,p_user_column_name => X_USER_COLUMN_NAME
437 ,p_formula_id => l_formula_id
438 ,p_object_version_number => l_object_version_number
439 ,p_formula_warning => l_warning
440 );
441
442 END IF;
443
444 EXCEPTION
445 WHEN NO_DATA_FOUND
446 THEN
447
448 pay_user_column_api.create_user_column
449 (p_validate => FALSE
450 ,p_business_group_id => NULL
451 ,p_legislation_code => X_LEGISLATION_CODE
452 ,p_user_table_id => l_user_table_id
453 ,p_formula_id => l_formula_id
454 ,p_user_column_name => X_USER_COLUMN_NAME
455 ,p_user_column_id => l_user_column_id
456 ,p_object_version_number => l_object_version_number
457 );
458
459 END column_row_upd_ins;
460
461 PROCEDURE column_instance_upd_ins
462 (
463 X_USER_TABLE_NAME IN VARCHAR2,
464 X_USER_COLUMN_NAME IN VARCHAR2,
465 X_ROW_LOW_RANGE_OR_NAME IN VARCHAR2,
466 X_ROW_HIGH_RANGE IN VARCHAR2,
467 X_LEGISLATION_CODE IN VARCHAR2,
468 X_VALUE IN VARCHAR2,
469 X_EFFECTIVE_START_DATE IN VARCHAR2,
470 X_EFFECTIVE_END_DATE IN VARCHAR2,
471 X_OWNER IN VARCHAR2,
472 X_LEG_VIEW IN VARCHAR2
473 ) IS
474
475 CURSOR c_row_col_details
476 IS
477 SELECT pur.user_row_id,
478 puc.user_column_id,
479 pur.effective_start_date
480 FROM pay_user_tables put,
481 pay_user_rows_f pur,
482 pay_user_columns puc
483 WHERE pur.user_table_id = put.user_table_id
484 AND puc.user_table_id = put.user_table_id
485 AND pur.row_low_range_or_name = X_ROW_LOW_RANGE_OR_NAME
486 AND NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
487 AND puc.user_column_name = X_USER_COLUMN_NAME
488 AND put.user_table_name = X_USER_TABLE_NAME
489 AND put.legislation_code = X_LEGISLATION_CODE
490 AND pur.legislation_code = X_LEGISLATION_CODE
491 AND puc.legislation_code = X_LEGISLATION_CODE
492 AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
493 BETWEEN pur.effective_start_date AND pur.effective_end_date ;
494
495 l_user_table_id NUMBER;
496 l_user_row_id NUMBER;
497 l_user_column_id NUMBER;
498 l_user_col_instance_id NUMBER;
499 l_object_version_number NUMBER;
500 l_effective_start_date DATE;
501 l_effective_end_date DATE;
502 l_start_date DATE;
503 l_end_date DATE;
504
505 BEGIN
506
507 IF (g_upload AND (g_user_table_name = X_USER_TABLE_NAME))
508 THEN
509
510 SELECT put.user_table_id,
511 pur.user_row_id,
512 puc.user_column_id,
513 val.user_column_instance_id,
514 val.object_version_number,
515 val.effective_start_date,
516 val.effective_end_date
517 INTO l_user_table_id,
518 l_user_row_id,
519 l_user_column_id,
520 l_user_col_instance_id,
521 l_object_version_number,
522 l_effective_start_date,
523 l_effective_end_date
524 FROM pay_user_tables put,
525 pay_user_rows_f pur,
526 pay_user_columns puc,
527 pay_user_column_instances_f val
528 WHERE val.user_row_id = pur.user_row_id
529 AND val.user_column_id = puc.user_column_id
530 AND pur.user_table_id = put.user_table_id
531 AND puc.user_column_name = X_USER_COLUMN_NAME
532 AND (
533 pur.row_low_range_or_name = X_ROW_LOW_RANGE_OR_NAME
534 AND
535 NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
536 )
537 AND put.user_table_name = X_USER_TABLE_NAME
538 AND(
539 (
540 put.legislation_code IS NULL
541 AND val.legislation_code IS NULL
542 AND pur.legislation_code IS NULL
543 )
544 OR
545 (
546 put.legislation_code = X_LEGISLATION_CODE
547 AND val.legislation_code = X_LEGISLATION_CODE
548 AND pur.legislation_code = X_LEGISLATION_CODE
549 )
550 )
551 AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
552 BETWEEN val.effective_start_date AND val.effective_end_date
553 AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
554 BETWEEN pur.effective_start_date AND pur.effective_end_date;
555
556 IF (l_effective_start_date <> fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE))
557 THEN
558
559 pay_user_column_instance_api.update_user_column_instance
560 (p_validate => FALSE
561 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
562 ,p_user_column_instance_id => l_user_col_instance_id
563 ,p_datetrack_update_mode => hr_api.g_update
564 ,p_value => X_VALUE
565 ,p_object_version_number => l_object_version_number
566 ,p_effective_start_date => l_start_date
567 ,p_effective_end_date => l_end_date
568 );
569
570 END IF;
571
572 IF (l_effective_end_date <> fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE))
573 THEN
574
575 pay_user_column_instance_api.delete_user_column_instance
576 (p_validate => FALSE
577 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
578 ,p_user_column_instance_id => l_user_col_instance_id
579 ,p_datetrack_update_mode => hr_api.g_delete
580 ,p_object_version_number => l_object_version_number
581 ,p_effective_start_date => l_start_date
582 ,p_effective_end_date => l_end_date
583 );
584
585 END IF;
586
587
588 END IF;
589
590 EXCEPTION
591 WHEN NO_DATA_FOUND
592 THEN
593
594 OPEN c_row_col_details;
595 FETCH c_row_col_details INTO l_user_row_id, l_user_column_id,l_effective_start_date;
596 CLOSE c_row_col_details;
597
598 pay_user_column_instance_api.create_user_column_instance
599 (p_validate => FALSE
600 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
601 ,p_user_row_id => l_user_row_id
602 ,p_user_column_id => l_user_column_id
603 ,p_value => X_VALUE
604 ,p_business_group_id => NULL
605 ,p_legislation_code => X_LEGISLATION_CODE
606 ,p_user_column_instance_id => l_user_col_instance_id
607 ,p_object_version_number => l_object_version_number
608 ,p_effective_start_date => l_start_date
609 ,p_effective_end_date => l_end_date
610 );
611
612
613
614 IF (SUBSTR(X_EFFECTIVE_END_DATE,0,4) <> TO_CHAR(l_end_date,'YYYY'))
615 THEN
616 pay_user_column_instance_api.delete_user_column_instance
617 (p_validate => FALSE
618 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
619 ,p_user_column_instance_id => l_user_col_instance_id
620 ,p_datetrack_update_mode => hr_api.g_delete
621 ,p_object_version_number => l_object_version_number
622 ,p_effective_start_date => l_start_date
623 ,p_effective_end_date => l_end_date
624 );
625 END IF;
626
627
628
629
630
631 END column_instance_upd_ins;
632
633
634 END pay_user_table_details_pkg;
635