[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_USER_TABLE_DETAILS_PKG
Source
1 PACKAGE BODY pay_user_table_details_pkg AS
2 /* $Header: pyutabdp.pkb 120.6.12020000.2 2013/03/27 14:26:51 ranarra ship $ */
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 l_temp := 'Y';
54 g_user_table_name := X_USER_TABLE_NAME;
55 g_product_code := 'PAY';
56 END IF;
57 -- Now check the Products installed for this legislation
58 IF (g_upload) and x_view_name IS NOT NULL
59 THEN
60 l_temp := 'N';
61 OPEN chk_installation(0);
62 FETCH chk_installation INTO l_temp,g_product_code;
63 CLOSE chk_installation;
64
65 IF (l_temp = 'N')
66 THEN
67 OPEN chk_installation(5);
68 FETCH chk_installation INTO l_temp,g_product_code;
69 CLOSE chk_installation;
70 END IF;
71
72 IF (l_temp = 'N')
73 THEN
74 OPEN chk_installation(9);
75 FETCH chk_installation INTO l_temp,g_product_code;
76 CLOSE chk_installation;
77 END IF;
78
79 IF (l_temp = 'N')
80 THEN
81 g_upload := FALSE;
82 ELSE
83 OPEN chk_table_at_bg;
84 FETCH chk_table_at_bg INTO l_user_table_id;
85 CLOSE chk_table_at_bg;
86
87 IF (l_user_table_id IS NOT NULL)
88 THEN
89 INSERT INTO hr_stu_exceptions(TABLE_NAME,SURROGATE_ID, EXCEPTION_TEXT,TRUE_KEY)
90 VALUES(X_USER_TABLE_NAME,l_user_table_id,'User Table: '|| X_USER_TABLE_NAME ||' already exists at BG level.',NULL);
91 g_upload := FALSE;
92 ELSE
93 g_upload := TRUE;
94 g_user_table_name := X_USER_TABLE_NAME;
95 END IF;
96 END IF;
97 END IF;
98
99 IF (g_upload)
100 THEN
101 hr_startup_data_api_support.enable_startup_mode('STARTUP');
102 hr_startup_data_api_support.delete_owner_definitions;
103 hr_startup_data_api_support.create_owner_definition(g_product_code);
104 END IF;
105 END perform_validations;
106
107 PROCEDURE user_table_upd_ins
108 (
109 X_USER_TABLE_NAME IN VARCHAR2,
110 X_USER_ROW_TITLE IN VARCHAR2,
111 X_LEGISLATION_CODE IN VARCHAR2,
112 X_RANGE_OR_MATCH IN VARCHAR2,
113 X_USER_KEY_UNITS IN VARCHAR2,
114 X_OWNER IN VARCHAR2,
115 X_LEG_VIEW IN VARCHAR2,
116 X_PRODUCT_CODE IN VARCHAR2
117 ) IS
118
119 l_object_version_number NUMBER;
120 l_user_table_id NUMBER;
121 table_at_bg_exists EXCEPTION;
122
123 BEGIN
124 perform_validations(X_LEG_VIEW,
125 X_PRODUCT_CODE,
126 X_LEGISLATION_CODE,
127 X_USER_TABLE_NAME
128 );
129 IF (g_upload AND (g_user_table_name = X_USER_TABLE_NAME))
130 THEN
131
132 SELECT user_table_id
133 ,object_version_number
134 INTO l_user_table_id
135 ,l_object_version_number
136 FROM pay_user_tables
137 WHERE user_table_name = X_USER_TABLE_NAME
138 AND (
139 legislation_code = X_LEGISLATION_CODE
140 OR
141 legislation_code IS NULL
142 )
143 AND business_group_id IS NULL;
144
145 pay_user_table_api.update_user_table
146 (p_validate => FALSE
147 ,p_user_table_id => l_user_table_id
148 ,p_effective_date => SYSDATE
149 ,p_user_table_name => X_USER_TABLE_NAME
150 ,p_user_row_title => X_USER_ROW_TITLE
151 ,p_object_version_number => l_object_version_number
152 );
153 END IF ;
154 EXCEPTION
155 WHEN NO_DATA_FOUND
156 THEN
157 pay_user_table_api.create_user_table
158 (p_validate => FALSE
159 ,p_effective_date => sysdate
160 ,p_business_group_id => NULL
161 ,p_legislation_code => X_LEGISLATION_CODE
162 ,p_range_or_match => X_RANGE_OR_MATCH
163 ,p_user_key_units => X_USER_KEY_UNITS
164 ,p_user_table_name => X_USER_TABLE_NAME
165 ,p_user_row_title => X_USER_ROW_TITLE
166 ,p_user_table_id => l_user_table_id
167 ,p_object_version_number => l_object_version_number
168 );
169 END user_table_upd_ins;
170
171
172 PROCEDURE user_row_upd_ins
173 (
174 X_USER_TABLE_NAME IN VARCHAR2,
175 X_LEGISLATION_CODE IN VARCHAR2,
176 X_ROW_LOW_RANGE_OR_NAME IN VARCHAR2,
177 X_ROW_HIGH_RANGE IN VARCHAR2,
178 X_EFFECTIVE_START_DATE IN VARCHAR2,
179 X_EFFECTIVE_END_DATE IN VARCHAR2,
180 X_DISPLAY_SEQUENCE IN VARCHAR2,
181 X_OWNER IN VARCHAR2,
182 X_LEG_VIEW IN VARCHAR2
183 ) IS
184 CURSOR c_table_id
185 IS
186 SELECT user_table_id, user_key_units
187 FROM pay_user_tables
188 WHERE user_table_name = X_USER_TABLE_NAME
189 AND (
190 legislation_code = X_LEGISLATION_CODE
191 OR
192 legislation_code IS NULL
193 )
194 AND business_group_id IS NULL;
195
196
197 CURSOR c_get_col_instance_id (p_user_row_id NUMBER)
198 IS
199 SELECT val.user_column_instance_id column_instance_id,
200 val.object_version_number object_version_number
201 FROM pay_user_tables put,
202 pay_user_rows_f pur,
203 pay_user_columns puc,
204 pay_user_column_instances_f val
205 WHERE val.user_row_id = pur.user_row_id
206 AND val.user_row_id = p_user_row_id
207 AND val.user_column_id = puc.user_column_id
208 AND pur.user_table_id = put.user_table_id
209 AND puc.user_table_id = put.user_table_id
210 -- bug 9234524 convert to user key units when matching
211 and decode
212 (put.user_key_units,
213 'D', to_char(fnd_date.canonical_to_date(X_ROW_LOW_RANGE_OR_NAME)),
214 'N', to_char(fnd_number.canonical_to_number(X_ROW_LOW_RANGE_OR_NAME)),
215 'T', upper (X_ROW_LOW_RANGE_OR_NAME),
216 null) =
217 decode
218 (put.user_key_units,
219 'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
220 'N', to_char(fnd_number.canonical_to_number(pur.row_low_range_or_name)),
221 'T', upper (pur.row_low_range_or_name),
222 null)
223 and ( NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
224 OR decode
225 (put.user_key_units,
226 'D', to_char(fnd_date.canonical_to_date(X_ROW_HIGH_RANGE)),
227 'N', to_char(fnd_number.canonical_to_number(X_ROW_HIGH_RANGE)),
228 'T', upper (X_ROW_HIGH_RANGE),
229 null) =
230 decode
231 (put.user_key_units,
232 'D', to_char(fnd_date.canonical_to_date(pur.row_high_range)),
233 'N', to_char(fnd_number.canonical_to_number(pur.row_high_range)),
234 'T', upper (pur.row_high_range),
235 null))
236 -- end bug 9234524
237 AND put.user_table_name = X_USER_TABLE_NAME
238 AND(
239 (
240 put.legislation_code IS NULL
241 AND val.legislation_code IS NULL
242 AND pur.legislation_code IS NULL
243 )
244 OR
245 (
246 put.legislation_code = X_LEGISLATION_CODE
247 -- AND val.legislation_code = X_LEGISLATION_CODE /* Bug 16484589 */
248 AND pur.legislation_code = X_LEGISLATION_CODE
249 )
250 )
251 AND fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE) < val.effective_end_date;
252
253
254
255 l_user_row_id NUMBER;
256 l_display_sequence NUMBER;
257 l_user_table_id NUMBER;
258 l_user_key_units pay_user_tables.USER_KEY_UNITS%type;
259 l_start_date DATE;
260 l_end_date DATE;
261 l_effective_start_date DATE;
262 l_effective_end_date DATE;
263 l_object_version_number NUMBER;
264 l_datetrack_mode VARCHAR2(30);
265 l_effective_date DATE;
266
267 BEGIN
268 l_display_sequence:=X_DISPLAY_SEQUENCE;
269
270 IF (g_upload AND (g_user_table_name = X_USER_TABLE_NAME))
271 THEN
272
273
274 OPEN c_table_id;
275 FETCH c_table_id INTO l_user_table_id,l_user_key_units;
276 CLOSE c_table_id;
277
278 SELECT user_row_id
279 ,object_version_number
280 ,effective_start_date
281 ,effective_end_date
282 INTO l_user_row_id
283 ,l_object_version_number
284 ,l_effective_start_date
285 ,l_effective_end_date
286 FROM pay_user_rows_f pur
287 -- bug 9234524 convert to user key units when matching
288 WHERE decode
289 (l_user_key_units,
290 'D', to_char(fnd_date.canonical_to_date(X_ROW_LOW_RANGE_OR_NAME)),
291 'N', to_char(fnd_number.canonical_to_number(X_ROW_LOW_RANGE_OR_NAME)),
292 'T', upper (X_ROW_LOW_RANGE_OR_NAME),
293 null) =
294 decode
295 (l_user_key_units,
296 'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
297 'N', to_char(fnd_number.canonical_to_number(pur.row_low_range_or_name)),
298 'T', upper (pur.row_low_range_or_name),
299 null)
300 and ( NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
301 OR decode
302 (l_user_key_units,
303 'D', to_char(fnd_date.canonical_to_date(X_ROW_HIGH_RANGE)),
304 'N', to_char(fnd_number.canonical_to_number(X_ROW_HIGH_RANGE)),
305 'T', upper (X_ROW_HIGH_RANGE),
306 null) =
307 decode
308 (l_user_key_units,
309 'D', to_char(fnd_date.canonical_to_date(pur.row_high_range)),
310 'N', to_char(fnd_number.canonical_to_number(pur.row_high_range)),
311 'T', upper (pur.row_high_range),
312 null))
313 -- end bug 9234524 changes
314 AND (
315 legislation_code = X_LEGISLATION_CODE
316 OR
317 legislation_code IS NULL
318 )
319 AND business_group_id IS NULL
320 AND user_table_id = l_user_table_id
321 AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE) BETWEEN effective_start_date AND effective_end_date;
322
323
324 IF (l_effective_start_date <> fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE))
325 THEN
326 pay_user_row_api.update_user_row
327 (p_validate => FALSE
328 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
329 ,p_datetrack_update_mode => hr_api.g_update
330 ,p_user_row_id => l_user_row_id
331 ,p_display_sequence => l_display_sequence
332 ,p_object_version_number => l_object_version_number
333 ,p_row_low_range_or_name => X_ROW_LOW_RANGE_OR_NAME
334 ,p_base_row_low_range_or_name => X_ROW_LOW_RANGE_OR_NAME
335 ,p_disable_range_overlap_check => TRUE
336 ,p_disable_units_check => FALSE
337 ,p_row_high_range => X_ROW_HIGH_RANGE
338 ,p_effective_start_date => l_start_date
339 ,p_effective_end_date => l_end_date
340 );
341
342 END IF;
343
344 IF (l_effective_end_date <> fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE))
345 THEN
346 if l_effective_end_date < fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
347 then l_datetrack_mode := hr_api.g_future_change;
348 l_effective_date := fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE);
349 else l_datetrack_mode := hr_api.g_delete;
350 l_effective_date := fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE);
351 end if;
352 hr_startup_data_api_support.enable_startup_mode('DELIVERY');
353 FOR c_rec IN c_get_col_instance_id(l_user_row_id)
354 LOOP
355 pay_user_column_instance_api.delete_user_column_instance
356 (p_validate => FALSE
357 ,p_effective_date => l_effective_date
358 ,p_user_column_instance_id => c_rec.column_instance_id
359 ,p_datetrack_update_mode => l_datetrack_mode
360 ,p_object_version_number => c_rec.object_version_number
361 ,p_effective_start_date => l_start_date
362 ,p_effective_end_date => l_end_date
363 );
364 END LOOP;
365 hr_startup_data_api_support.enable_startup_mode('STARTUP');
366
367 pay_user_row_api.delete_user_row
368 (p_validate => FALSE
369 ,p_effective_date => l_effective_date
370 ,p_datetrack_update_mode => l_datetrack_mode
371 ,p_user_row_id => l_user_row_id
372 ,p_object_version_number => l_object_version_number
373 ,p_disable_range_overlap_check => FALSE
374 ,p_effective_start_date => l_start_date
375 ,p_effective_end_date => l_end_date
376 );
377 END IF;
378
379 END IF;
380
381 EXCEPTION
382 WHEN NO_DATA_FOUND
383 THEN
384 l_display_sequence := X_DISPLAY_SEQUENCE;
385
386 pay_user_row_api.create_user_row
387 (p_validate => FALSE
388 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
389 ,p_user_table_id => l_user_table_id
390 ,p_row_low_range_or_name => X_ROW_LOW_RANGE_OR_NAME
391 ,p_display_sequence => l_display_sequence
392 ,p_business_group_id => NULL
393 ,p_legislation_code => X_LEGISLATION_CODE
394 ,p_disable_range_overlap_check => TRUE
395 ,p_disable_units_check => FALSE
396 ,p_row_high_range => X_ROW_HIGH_RANGE
397 ,p_user_row_id => l_user_row_id
398 ,p_object_version_number => l_object_version_number
399 ,p_effective_start_date => l_start_date
400 ,p_effective_end_date => l_end_date
401 );
402
403 IF (SUBSTR(X_EFFECTIVE_END_DATE,0,4) <> '4712')
404 THEN
405 pay_user_row_api.delete_user_row
406 (p_validate => FALSE
407 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
408 ,p_datetrack_update_mode => hr_api.g_delete
409 ,p_user_row_id => l_user_row_id
410 ,p_object_version_number => l_object_version_number
411 ,p_disable_range_overlap_check => FALSE
412 ,p_effective_start_date => l_start_date
413 ,p_effective_end_date => l_end_date
414 );
415
416 END IF;
417
418 END user_row_upd_ins;
419
420 PROCEDURE column_row_upd_ins
421 (
422 X_USER_TABLE_NAME IN VARCHAR2,
423 X_LEGISLATION_CODE IN VARCHAR2,
424 X_USER_COLUMN_NAME IN VARCHAR2,
425 X_FORMULA_NAME IN VARCHAR2,
426 X_FORMULA_LEG_CODE IN VARCHAR2,
427 X_OWNER IN VARCHAR2,
428 X_LEG_VIEW IN VARCHAR2
429 ) IS
430 CURSOR c_table_id
431 IS
432 SELECT user_table_id
433 FROM pay_user_tables
434 WHERE user_table_name = X_USER_TABLE_NAME
435 AND (
436 legislation_code = X_LEGISLATION_CODE
437 OR
438 legislation_code IS NULL
439 );
440
441 CURSOR c_get_formula_id
442 IS
443 SELECT ff.formula_id
444 FROM ff_formula_types fft
445 ,ff_formulas_f ff
446 WHERE fft.formula_type_name = 'User Table Validation'
447 AND fft.formula_type_id = ff.formula_type_id
448 AND ff.formula_name = X_FORMULA_NAME
449 AND (
450 X_FORMULA_LEG_CODE IS NULL
451 OR
452 ff.legislation_code = X_FORMULA_LEG_CODE
453 );
454
455 l_warning BOOLEAN;
456 l_user_column_id NUMBER;
457 l_user_table_id NUMBER;
458 l_formula_id NUMBER := NULL;
459 l_object_version_number NUMBER;
460
461 BEGIN
462
463 IF (g_upload AND (g_user_table_name = X_USER_TABLE_NAME))
464 THEN
465
466 OPEN c_table_id;
467 FETCH c_table_id INTO l_user_table_id;
468 CLOSE c_table_id;
469
470 IF (X_FORMULA_NAME IS NOT NULL)
471 THEN
472 OPEN c_get_formula_id;
473 FETCH c_get_formula_id INTO l_formula_id;
474 CLOSE c_get_formula_id;
475 END IF;
476
477 SELECT user_column_id
478 ,object_version_number
479 INTO l_user_column_id
480 ,l_object_version_number
481 FROM pay_user_columns
482 WHERE user_column_name = X_USER_COLUMN_NAME
483 AND (
484 legislation_code = X_LEGISLATION_CODE
485 OR
486 legislation_code IS NULL
487 )
488 AND user_table_id = l_user_table_id
489 AND business_group_id IS NULL;
490
491 pay_user_column_api.update_user_column
492 (p_validate => FALSE
493 ,p_user_column_id => l_user_column_id
494 ,p_user_column_name => X_USER_COLUMN_NAME
495 ,p_formula_id => l_formula_id
496 ,p_object_version_number => l_object_version_number
497 ,p_formula_warning => l_warning
498 );
499
500 END IF;
501
502 EXCEPTION
503 WHEN NO_DATA_FOUND
504 THEN
505
506 pay_user_column_api.create_user_column
507 (p_validate => FALSE
508 ,p_business_group_id => NULL
509 ,p_legislation_code => X_LEGISLATION_CODE
510 ,p_user_table_id => l_user_table_id
511 ,p_formula_id => l_formula_id
512 ,p_user_column_name => X_USER_COLUMN_NAME
513 ,p_user_column_id => l_user_column_id
514 ,p_object_version_number => l_object_version_number
515 );
516
517 END column_row_upd_ins;
518
519 PROCEDURE column_instance_upd_ins
520 (
521 X_USER_TABLE_NAME IN VARCHAR2,
522 X_USER_COLUMN_NAME IN VARCHAR2,
523 X_ROW_LOW_RANGE_OR_NAME IN VARCHAR2,
524 X_ROW_HIGH_RANGE IN VARCHAR2,
525 X_LEGISLATION_CODE IN VARCHAR2,
526 X_VALUE IN VARCHAR2,
527 X_EFFECTIVE_START_DATE IN VARCHAR2,
528 X_EFFECTIVE_END_DATE IN VARCHAR2,
529 X_OWNER IN VARCHAR2,
530 X_LEG_VIEW IN VARCHAR2
531 ) IS
532
533 CURSOR c_row_col_details
534 IS
535 SELECT pur.user_row_id,
536 puc.user_column_id,
537 pur.effective_start_date
538 FROM pay_user_tables put,
539 pay_user_rows_f pur,
540 pay_user_columns puc
541 WHERE pur.user_table_id = put.user_table_id
542 AND puc.user_table_id = put.user_table_id
543 -- bug 9234524 convert to user key units when matching
544 AND decode
545 (put.user_key_units,
546 'D', to_char(fnd_date.canonical_to_date(X_ROW_LOW_RANGE_OR_NAME)),
547 'N', to_char(fnd_number.canonical_to_number(X_ROW_LOW_RANGE_OR_NAME)),
548 'T', upper (X_ROW_LOW_RANGE_OR_NAME),
549 null) =
550 decode
551 (put.user_key_units,
552 'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
553 'N', to_char(fnd_number.canonical_to_number(pur.row_low_range_or_name)),
554 'T', upper (pur.row_low_range_or_name),
555 null)
556 AND ( NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
557 OR decode
558 (put.user_key_units,
559 'D', to_char(fnd_date.canonical_to_date(X_ROW_HIGH_RANGE)),
560 'N', to_char(fnd_number.canonical_to_number(X_ROW_HIGH_RANGE)),
561 'T', upper (X_ROW_HIGH_RANGE),
562 null) =
563 decode
564 (put.user_key_units,
565 'D', to_char(fnd_date.canonical_to_date(pur.row_high_range)),
566 'N', to_char(fnd_number.canonical_to_number(pur.row_high_range)),
567 'T', upper (pur.row_high_range),
568 null))
569 -- bug 9234524 changes end
570 AND puc.user_column_name = X_USER_COLUMN_NAME
571 AND put.user_table_name = X_USER_TABLE_NAME
572 AND put.legislation_code = X_LEGISLATION_CODE
573 AND pur.legislation_code = X_LEGISLATION_CODE
574 AND puc.legislation_code = X_LEGISLATION_CODE
575 AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
576 BETWEEN pur.effective_start_date AND pur.effective_end_date ;
577
578 l_user_table_id NUMBER;
579 l_user_row_id NUMBER;
580 l_user_column_id NUMBER;
581 l_user_col_instance_id NUMBER;
582 l_object_version_number NUMBER;
583 l_effective_start_date DATE;
584 l_effective_end_date DATE;
585 l_start_date DATE;
586 l_end_date DATE;
587 l_datetrack_mode VARCHAR2(30);
588 l_effective_date DATE;
589
590 BEGIN
591
592 IF (g_upload AND (g_user_table_name = X_USER_TABLE_NAME))
593 THEN
594
595 SELECT put.user_table_id,
596 pur.user_row_id,
597 puc.user_column_id,
598 val.user_column_instance_id,
599 val.object_version_number,
600 val.effective_start_date,
601 val.effective_end_date
602 INTO l_user_table_id,
603 l_user_row_id,
604 l_user_column_id,
605 l_user_col_instance_id,
606 l_object_version_number,
607 l_effective_start_date,
608 l_effective_end_date
609 FROM pay_user_tables put,
610 pay_user_rows_f pur,
611 pay_user_columns puc,
612 pay_user_column_instances_f val
613 WHERE val.user_row_id = pur.user_row_id
614 AND val.user_column_id = puc.user_column_id
615 AND pur.user_table_id = put.user_table_id
616 AND puc.user_column_name = X_USER_COLUMN_NAME
617 -- bug 9234524 convert to user key units when matching
618 AND decode
619 (put.user_key_units,
620 'D', to_char(fnd_date.canonical_to_date(X_ROW_LOW_RANGE_OR_NAME)),
621 'N', to_char(fnd_number.canonical_to_number(X_ROW_LOW_RANGE_OR_NAME)),
622 'T', upper (X_ROW_LOW_RANGE_OR_NAME),
623 null) =
624 decode
625 (put.user_key_units,
626 'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
627 'N', to_char(fnd_number.canonical_to_number(pur.row_low_range_or_name)),
628 'T', upper (pur.row_low_range_or_name),
629 null)
630 AND ( NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
631 OR decode
632 (put.user_key_units,
633 'D', to_char(fnd_date.canonical_to_date(X_ROW_HIGH_RANGE)),
634 'N', to_char(fnd_number.canonical_to_number(X_ROW_HIGH_RANGE)),
635 'T', upper (X_ROW_HIGH_RANGE),
636 null) =
637 decode
638 (put.user_key_units,
639 'D', to_char(fnd_date.canonical_to_date(pur.row_high_range)),
640 'N', to_char(fnd_number.canonical_to_number(pur.row_high_range)),
641 'T', upper (pur.row_high_range),
642 null))
643 -- bug 9234524 changes end
644 AND put.user_table_name = X_USER_TABLE_NAME
645 AND(
646 (
647 put.legislation_code IS NULL
648 AND val.legislation_code IS NULL
649 AND pur.legislation_code IS NULL
650 )
651 OR
652 (
653 put.legislation_code = X_LEGISLATION_CODE
654 AND val.legislation_code = X_LEGISLATION_CODE
655 AND pur.legislation_code = X_LEGISLATION_CODE
656 )
657 )
658 AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
659 BETWEEN val.effective_start_date AND val.effective_end_date
660 AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
661 BETWEEN pur.effective_start_date AND pur.effective_end_date;
662
663 IF (l_effective_start_date <> fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE))
664 THEN
665
666 pay_user_column_instance_api.update_user_column_instance
667 (p_validate => FALSE
668 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
669 ,p_user_column_instance_id => l_user_col_instance_id
670 ,p_datetrack_update_mode => hr_api.g_update
671 ,p_value => X_VALUE
672 ,p_object_version_number => l_object_version_number
673 ,p_effective_start_date => l_start_date
674 ,p_effective_end_date => l_end_date
675 );
676
677 END IF;
678
679 IF (l_effective_end_date <> fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE))
680 THEN
681 if l_effective_end_date < fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
682 then l_datetrack_mode := hr_api.g_future_change;
683 l_effective_date := fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE);
684 else l_datetrack_mode := hr_api.g_delete;
685 l_effective_date := fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE);
686 end if;
687
688 pay_user_column_instance_api.delete_user_column_instance
689 (p_validate => FALSE
690 ,p_effective_date => l_effective_date
691 ,p_user_column_instance_id => l_user_col_instance_id
692 ,p_datetrack_update_mode => l_datetrack_mode
693 ,p_object_version_number => l_object_version_number
694 ,p_effective_start_date => l_start_date
695 ,p_effective_end_date => l_end_date
696 );
697
698 END IF;
699
700
701 END IF;
702
703 EXCEPTION
704 WHEN NO_DATA_FOUND
705 THEN
706
707 OPEN c_row_col_details;
708 FETCH c_row_col_details INTO l_user_row_id, l_user_column_id,l_effective_start_date;
709 CLOSE c_row_col_details;
710
711 pay_user_column_instance_api.create_user_column_instance
712 (p_validate => FALSE
713 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
714 ,p_user_row_id => l_user_row_id
715 ,p_user_column_id => l_user_column_id
716 ,p_value => X_VALUE
717 ,p_business_group_id => NULL
718 ,p_legislation_code => X_LEGISLATION_CODE
719 ,p_user_column_instance_id => l_user_col_instance_id
720 ,p_object_version_number => l_object_version_number
721 ,p_effective_start_date => l_start_date
722 ,p_effective_end_date => l_end_date
723 );
724
725
726
727 IF (SUBSTR(X_EFFECTIVE_END_DATE,0,4) <> TO_CHAR(l_end_date,'YYYY'))
728 THEN
729 pay_user_column_instance_api.delete_user_column_instance
730 (p_validate => FALSE
731 ,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
732 ,p_user_column_instance_id => l_user_col_instance_id
733 ,p_datetrack_update_mode => hr_api.g_delete
734 ,p_object_version_number => l_object_version_number
735 ,p_effective_start_date => l_start_date
736 ,p_effective_end_date => l_end_date
737 );
738 END IF;
739
740
741
742
743
744 END column_instance_upd_ins;
745
746
747 END pay_user_table_details_pkg;
748