[Home] [Help]
80: --
81: BEGIN
82: IF g_debug THEN
83: l_proc := g_package||'get_effective_rows';
84: hr_utility.Set_Location('Entering:'||l_proc, 5);
85: END IF;
86: -- If the p_key_value is null and we are locking then we must not
87: -- process the sql as it could be a nullable column.
88: IF (p_base_key_value IS NULL AND p_lock_rows) THEN
86: -- If the p_key_value is null and we are locking then we must not
87: -- process the sql as it could be a nullable column.
88: IF (p_base_key_value IS NULL AND p_lock_rows) THEN
89: IF g_debug THEN
90: hr_utility.Set_Location('Leaving :'||l_proc, 10);
91: -- the resulting p_effective_rows and p_date_from_row_idx will be NULL
92: END IF;
93: RETURN;
94: END IF;
154: -- clear the returning OUTs
155: p_effective_rows := dt_api.g_empty_effective_rows;
156: p_date_from_row_idx := NULL;
157: -- As no rows were returned we must error
158: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
159: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
160: hr_utility.set_message_token('SESSION_DATE',
161: fnd_date.date_to_chardate(p_date_from,1));
162: hr_utility.raise_error;
155: p_effective_rows := dt_api.g_empty_effective_rows;
156: p_date_from_row_idx := NULL;
157: -- As no rows were returned we must error
158: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
159: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
160: hr_utility.set_message_token('SESSION_DATE',
161: fnd_date.date_to_chardate(p_date_from,1));
162: hr_utility.raise_error;
163: END IF;
156: p_date_from_row_idx := NULL;
157: -- As no rows were returned we must error
158: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
159: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
160: hr_utility.set_message_token('SESSION_DATE',
161: fnd_date.date_to_chardate(p_date_from,1));
162: hr_utility.raise_error;
163: END IF;
164: IF g_debug THEN
158: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
159: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
160: hr_utility.set_message_token('SESSION_DATE',
161: fnd_date.date_to_chardate(p_date_from,1));
162: hr_utility.raise_error;
163: END IF;
164: IF g_debug THEN
165: hr_utility.Set_Location('Leaving :'||l_proc, 15);
166: END IF;
161: fnd_date.date_to_chardate(p_date_from,1));
162: hr_utility.raise_error;
163: END IF;
164: IF g_debug THEN
165: hr_utility.Set_Location('Leaving :'||l_proc, 15);
166: END IF;
167: EXCEPTION
168: WHEN NO_DATA_FOUND THEN
169: -- close the cursor if open
174: p_effective_rows := dt_api.g_empty_effective_rows;
175: p_date_from_row_idx := NULL;
176: -- no rows exist as of the date passed in
177: -- therefore a serious integrity problem has ocurred
178: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
179: hr_utility.set_message_token('ARGUMENT', UPPER(p_base_key_column));
180: hr_utility.raise_error;
181: WHEN hr_api.object_locked THEN
182: -- close the cursor if open
175: p_date_from_row_idx := NULL;
176: -- no rows exist as of the date passed in
177: -- therefore a serious integrity problem has ocurred
178: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
179: hr_utility.set_message_token('ARGUMENT', UPPER(p_base_key_column));
180: hr_utility.raise_error;
181: WHEN hr_api.object_locked THEN
182: -- close the cursor if open
183: IF l_csr%ISOPEN THEN
176: -- no rows exist as of the date passed in
177: -- therefore a serious integrity problem has ocurred
178: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
179: hr_utility.set_message_token('ARGUMENT', UPPER(p_base_key_column));
180: hr_utility.raise_error;
181: WHEN hr_api.object_locked THEN
182: -- close the cursor if open
183: IF l_csr%ISOPEN THEN
184: CLOSE l_csr;
187: p_effective_rows := dt_api.g_empty_effective_rows;
188: p_date_from_row_idx := NULL;
189: -- The object is locked therefore we need to supply a meaningful
190: -- error message.
191: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
192: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
193: hr_utility.raise_error;
194: WHEN OTHERS THEN
195: -- close the cursor if open
188: p_date_from_row_idx := NULL;
189: -- The object is locked therefore we need to supply a meaningful
190: -- error message.
191: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
192: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
193: hr_utility.raise_error;
194: WHEN OTHERS THEN
195: -- close the cursor if open
196: IF l_csr%ISOPEN THEN
189: -- The object is locked therefore we need to supply a meaningful
190: -- error message.
191: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
192: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
193: hr_utility.raise_error;
194: WHEN OTHERS THEN
195: -- close the cursor if open
196: IF l_csr%ISOPEN THEN
197: CLOSE l_csr;
256: --
257: BEGIN
258: IF g_debug THEN
259: l_proc := g_package||'return_parent_min_date';
260: hr_utility.Set_Location('Entering:'||l_proc, 5);
261: END IF;
262: -- setup the table with info
263: FOR l_counter IN 1..10 LOOP
264: IF (l_counter = 1) THEN
368: END IF;
369: END IF;
370: END LOOP;
371: IF g_debug THEN
372: hr_utility.Set_Location('Leaving:'||l_proc, 10);
373: END IF;
374: RETURN(l_return_date);
375: EXCEPTION
376: WHEN NO_DATA_FOUND THEN
380: END IF;
381: -- The parental row specified does not exist as of the effective date
382: -- therefore a serious integrity problem has ocurred
383: -- bug 3788667
384: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
385: hr_utility.set_message_token('ARGUMENT', UPPER(l_key_column));
386: hr_utility.raise_error;
387: WHEN hr_api.object_locked THEN
388: -- CLOSE the cursor if its open
381: -- The parental row specified does not exist as of the effective date
382: -- therefore a serious integrity problem has ocurred
383: -- bug 3788667
384: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
385: hr_utility.set_message_token('ARGUMENT', UPPER(l_key_column));
386: hr_utility.raise_error;
387: WHEN hr_api.object_locked THEN
388: -- CLOSE the cursor if its open
389: IF l_csr%ISOPEN THEN
382: -- therefore a serious integrity problem has ocurred
383: -- bug 3788667
384: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
385: hr_utility.set_message_token('ARGUMENT', UPPER(l_key_column));
386: hr_utility.raise_error;
387: WHEN hr_api.object_locked THEN
388: -- CLOSE the cursor if its open
389: IF l_csr%ISOPEN THEN
390: CLOSE l_csr;
391: END IF;
392: -- bug 3788667
393: -- The object is locked therefore we need to supply a meaningful
394: -- error message.
395: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
396: hr_utility.set_message_token('TABLE_NAME', UPPER(l_table_name));
397: hr_utility.raise_error;
398: WHEN OTHERS THEN
399: -- CLOSE the cursor if its open
392: -- bug 3788667
393: -- The object is locked therefore we need to supply a meaningful
394: -- error message.
395: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
396: hr_utility.set_message_token('TABLE_NAME', UPPER(l_table_name));
397: hr_utility.raise_error;
398: WHEN OTHERS THEN
399: -- CLOSE the cursor if its open
400: IF l_csr%ISOPEN THEN
393: -- The object is locked therefore we need to supply a meaningful
394: -- error message.
395: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
396: hr_utility.set_message_token('TABLE_NAME', UPPER(l_table_name));
397: hr_utility.raise_error;
398: WHEN OTHERS THEN
399: -- CLOSE the cursor if its open
400: IF l_csr%ISOPEN THEN
401: CLOSE l_csr;
418: l_proc VARCHAR2(72);
419: BEGIN
420: IF g_debug THEN
421: l_proc := g_package||'Effective_Date_Valid';
422: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
423: END IF;
424: -- Ensure that all the mandatory arguments are not null
425: -- [ start of change 30.14 ]
426: IF p_effective_date IS NULL THEN
429: p_argument_value => p_effective_date);
430: END IF;
431: -- [ end of change 30.14 ]
432: IF (p_effective_date < dt_api.g_sot) THEN
433: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
434: hr_utility.set_message_token('PROCEDURE', l_proc);
435: hr_utility.set_message_token('STEP','10');
436: hr_utility.raise_error;
437: END IF;
430: END IF;
431: -- [ end of change 30.14 ]
432: IF (p_effective_date < dt_api.g_sot) THEN
433: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
434: hr_utility.set_message_token('PROCEDURE', l_proc);
435: hr_utility.set_message_token('STEP','10');
436: hr_utility.raise_error;
437: END IF;
438: -- Ensure p_effective_date is not later than dt_api.g_eot end of time
431: -- [ end of change 30.14 ]
432: IF (p_effective_date < dt_api.g_sot) THEN
433: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
434: hr_utility.set_message_token('PROCEDURE', l_proc);
435: hr_utility.set_message_token('STEP','10');
436: hr_utility.raise_error;
437: END IF;
438: -- Ensure p_effective_date is not later than dt_api.g_eot end of time
439: IF (p_effective_date > dt_api.g_eot) THEN
432: IF (p_effective_date < dt_api.g_sot) THEN
433: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
434: hr_utility.set_message_token('PROCEDURE', l_proc);
435: hr_utility.set_message_token('STEP','10');
436: hr_utility.raise_error;
437: END IF;
438: -- Ensure p_effective_date is not later than dt_api.g_eot end of time
439: IF (p_effective_date > dt_api.g_eot) THEN
440: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
436: hr_utility.raise_error;
437: END IF;
438: -- Ensure p_effective_date is not later than dt_api.g_eot end of time
439: IF (p_effective_date > dt_api.g_eot) THEN
440: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
441: hr_utility.set_message_token('PROCEDURE', l_proc);
442: hr_utility.set_message_token('STEP','20');
443: hr_utility.raise_error;
444: END IF;
437: END IF;
438: -- Ensure p_effective_date is not later than dt_api.g_eot end of time
439: IF (p_effective_date > dt_api.g_eot) THEN
440: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
441: hr_utility.set_message_token('PROCEDURE', l_proc);
442: hr_utility.set_message_token('STEP','20');
443: hr_utility.raise_error;
444: END IF;
445: -- Check that effective_date does not include a time component. If set
438: -- Ensure p_effective_date is not later than dt_api.g_eot end of time
439: IF (p_effective_date > dt_api.g_eot) THEN
440: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
441: hr_utility.set_message_token('PROCEDURE', l_proc);
442: hr_utility.set_message_token('STEP','20');
443: hr_utility.raise_error;
444: END IF;
445: -- Check that effective_date does not include a time component. If set
446: -- then raise an error because it should have been truncated to just a day,
439: IF (p_effective_date > dt_api.g_eot) THEN
440: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
441: hr_utility.set_message_token('PROCEDURE', l_proc);
442: hr_utility.set_message_token('STEP','20');
443: hr_utility.raise_error;
444: END IF;
445: -- Check that effective_date does not include a time component. If set
446: -- then raise an error because it should have been truncated to just a day,
447: -- month year value before the DT logic is called.
445: -- Check that effective_date does not include a time component. If set
446: -- then raise an error because it should have been truncated to just a day,
447: -- month year value before the DT logic is called.
448: IF p_effective_date <> TRUNC(p_effective_date) THEN
449: hr_utility.set_message(801, 'HR_51322_DT_TIME_SET');
450: hr_utility.raise_error;
451: END IF;
452: IF g_debug THEN
453: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
446: -- then raise an error because it should have been truncated to just a day,
447: -- month year value before the DT logic is called.
448: IF p_effective_date <> TRUNC(p_effective_date) THEN
449: hr_utility.set_message(801, 'HR_51322_DT_TIME_SET');
450: hr_utility.raise_error;
451: END IF;
452: IF g_debug THEN
453: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
454: END IF;
449: hr_utility.set_message(801, 'HR_51322_DT_TIME_SET');
450: hr_utility.raise_error;
451: END IF;
452: IF g_debug THEN
453: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
454: END IF;
455: END Effective_Date_Valid;
456: -- ----------------------------------------------------------------------------
457: -- |------------------------< Return_Max_End_Date >---------------------------|
474: l_proc VARCHAR2(72);
475: l_max_date DATE;
476: --
477: BEGIN
478: g_debug := hr_utility.debug_enabled;
479: IF g_debug THEN
480: l_proc := g_package||'return_max_end_date';
481: hr_utility.set_location('Entering:'||l_proc, 5);
482: END IF;
477: BEGIN
478: g_debug := hr_utility.debug_enabled;
479: IF g_debug THEN
480: l_proc := g_package||'return_max_end_date';
481: hr_utility.set_location('Entering:'||l_proc, 5);
482: END IF;
483: -- Ensure that all the mandatory arguments are not null
484: -- [ start of change 30.14 ]
485: IF p_base_table_name IS NULL OR p_base_key_column IS NULL OR
509: INTO l_max_date
510: USING p_base_key_value;
511: --
512: IF g_debug THEN
513: hr_utility.set_location('Leaving :'||l_proc, 10);
514: END IF;
515: RETURN(l_max_date);
516: END return_max_end_date;
517: -- ----------------------------------------------------------------------------
552: --
553: BEGIN
554: IF g_debug THEN
555: l_proc := g_package||'lck_child';
556: hr_utility.set_location('Entering:'||l_proc, 5);
557: END IF;
558: --
559: -- Ensure that all the required parameters exist
560: -- Note: we don't check the p_parent_key_value argument
626: -- greater than the validation start date
627: IF (l_date_tab(l_counter) >= p_validation_start_date) THEN
628: -- The maximum end date is greater than or equal to the
629: -- validation start date therefore we must error
630: hr_utility.set_message(801, 'HR_7201_DT_NO_DELETE_CHILD');
631: hr_utility.raise_error;
632: END IF;
633: END LOOP;
634: IF g_debug THEN
627: IF (l_date_tab(l_counter) >= p_validation_start_date) THEN
628: -- The maximum end date is greater than or equal to the
629: -- validation start date therefore we must error
630: hr_utility.set_message(801, 'HR_7201_DT_NO_DELETE_CHILD');
631: hr_utility.raise_error;
632: END IF;
633: END LOOP;
634: IF g_debug THEN
635: hr_utility.set_location(' Leaving:'||l_proc, 35);
631: hr_utility.raise_error;
632: END IF;
633: END LOOP;
634: IF g_debug THEN
635: hr_utility.set_location(' Leaving:'||l_proc, 35);
636: END IF;
637: EXCEPTION
638: WHEN hr_api.object_locked THEN
639: IF l_cursor%ISOPEN THEN
640: CLOSE l_cursor;
641: END IF;
642: -- The object is locked therefore we need to supply a meaningful
643: -- error message.
644: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
645: hr_utility.set_message_token('TABLE_NAME', p_child_table_name);
646: hr_utility.raise_error;
647: WHEN OTHERS THEN
648: IF l_cursor%ISOPEN THEN
641: END IF;
642: -- The object is locked therefore we need to supply a meaningful
643: -- error message.
644: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
645: hr_utility.set_message_token('TABLE_NAME', p_child_table_name);
646: hr_utility.raise_error;
647: WHEN OTHERS THEN
648: IF l_cursor%ISOPEN THEN
649: CLOSE l_cursor;
642: -- The object is locked therefore we need to supply a meaningful
643: -- error message.
644: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
645: hr_utility.set_message_token('TABLE_NAME', p_child_table_name);
646: hr_utility.raise_error;
647: WHEN OTHERS THEN
648: IF l_cursor%ISOPEN THEN
649: CLOSE l_cursor;
650: END IF;
680: l_override_start_date DATE;
681: l_override_end_date DATE;
682: --
683: BEGIN
684: g_debug := hr_utility.debug_enabled;
685: IF g_debug THEN
686: l_proc := g_package||'Find_DT_Upd_Modes';
687: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
688: END IF;
683: BEGIN
684: g_debug := hr_utility.debug_enabled;
685: IF g_debug THEN
686: l_proc := g_package||'Find_DT_Upd_Modes';
687: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
688: END IF;
689: find_dt_upd_modes_and_dates
690: (p_effective_date => p_effective_date
691: ,p_base_table_name => p_base_table_name
705: ,p_override_end_date => l_override_end_date
706: );
707:
708: IF g_debug THEN
709: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
710: END IF;
711: EXCEPTION
712: WHEN OTHERS THEN
713: -- set all OUTs to NULL and RAISE
777: l_del_next_start_date DATE;
778: l_del_next_end_date DATE;
779: --
780: BEGIN
781: g_debug := hr_utility.debug_enabled;
782: IF g_debug THEN
783: l_proc := g_package||'Find_DT_Del_Modes';
784: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
785: END IF;
780: BEGIN
781: g_debug := hr_utility.debug_enabled;
782: IF g_debug THEN
783: l_proc := g_package||'Find_DT_Del_Modes';
784: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
785: END IF;
786: find_dt_del_modes_and_dates
787: (p_effective_date => p_effective_date
788: ,p_base_table_name => p_base_table_name
832: ,p_del_next_end_date => l_del_next_end_date
833: );
834:
835: IF g_debug THEN
836: Hr_Utility.Set_Location('Leaving :'||l_proc, 25);
837: END IF;
838: EXCEPTION
839: WHEN OTHERS THEN
840: p_zap := NULL;
897: --
898: BEGIN
899: IF g_debug THEN
900: l_proc := g_package||'Get_Insert_Dates';
901: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
902: END IF;
903: -- set the returning validation start and end dates
904: p_validation_start_date := p_effective_date;
905: p_validation_end_date :=
936: p_table_name10 => p_parent_table_name10,
937: p_key_column10 => p_parent_key_column10,
938: p_key_value10 => p_parent_key_value10);
939: IF g_debug THEN
940: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
941: END IF;
942: EXCEPTION
943: WHEN OTHERS THEN
944: -- reset the OUT params to NULL to cater for NOCOPY
968: --
969: BEGIN
970: IF g_debug THEN
971: l_proc := g_package||'Get_Correction_Dates';
972: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
973: END IF;
974: -- Define dynamic sql text with substitution tokens
975: g_dynamic_sql :=
976: 'SELECT t.effective_start_date,t.effective_end_date '||
989: ,p_validation_end_date
990: USING p_base_key_value
991: ,p_effective_date;
992: IF g_debug THEN
993: hr_utility.set_location('Leaving :'||l_proc, 10);
994: END IF;
995: EXCEPTION
996: WHEN NO_DATA_FOUND THEN
997: -- reset the OUT params to NULL to cater for NOCOPY
997: -- reset the OUT params to NULL to cater for NOCOPY
998: p_validation_start_date := NULL;
999: p_validation_end_date := NULL;
1000: -- As no rows were returned we must error
1001: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
1002: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
1003: hr_utility.set_message_token
1004: ('SESSION_DATE'
1005: ,fnd_date.date_to_chardate(p_effective_date,1)
998: p_validation_start_date := NULL;
999: p_validation_end_date := NULL;
1000: -- As no rows were returned we must error
1001: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
1002: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
1003: hr_utility.set_message_token
1004: ('SESSION_DATE'
1005: ,fnd_date.date_to_chardate(p_effective_date,1)
1006: );
999: p_validation_end_date := NULL;
1000: -- As no rows were returned we must error
1001: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
1002: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
1003: hr_utility.set_message_token
1004: ('SESSION_DATE'
1005: ,fnd_date.date_to_chardate(p_effective_date,1)
1006: );
1007: hr_utility.raise_error;
1003: hr_utility.set_message_token
1004: ('SESSION_DATE'
1005: ,fnd_date.date_to_chardate(p_effective_date,1)
1006: );
1007: hr_utility.raise_error;
1008: WHEN TOO_MANY_ROWS THEN
1009: -- reset the OUT params to NULL to cater for NOCOPY
1010: p_validation_start_date := NULL;
1011: p_validation_end_date := NULL;
1008: WHEN TOO_MANY_ROWS THEN
1009: -- reset the OUT params to NULL to cater for NOCOPY
1010: p_validation_start_date := NULL;
1011: p_validation_end_date := NULL;
1012: hr_utility.set_message(801, 'HR_7181_DT_OVERLAP_ROWS');
1013: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
1014: hr_utility.set_message_token
1015: ('SESSION_DATE'
1016: ,fnd_date.date_to_chardate(p_effective_date,1)
1009: -- reset the OUT params to NULL to cater for NOCOPY
1010: p_validation_start_date := NULL;
1011: p_validation_end_date := NULL;
1012: hr_utility.set_message(801, 'HR_7181_DT_OVERLAP_ROWS');
1013: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
1014: hr_utility.set_message_token
1015: ('SESSION_DATE'
1016: ,fnd_date.date_to_chardate(p_effective_date,1)
1017: );
1010: p_validation_start_date := NULL;
1011: p_validation_end_date := NULL;
1012: hr_utility.set_message(801, 'HR_7181_DT_OVERLAP_ROWS');
1013: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
1014: hr_utility.set_message_token
1015: ('SESSION_DATE'
1016: ,fnd_date.date_to_chardate(p_effective_date,1)
1017: );
1018: hr_utility.set_message_token('PRIMARY_VALUE', to_char(p_base_key_value));
1014: hr_utility.set_message_token
1015: ('SESSION_DATE'
1016: ,fnd_date.date_to_chardate(p_effective_date,1)
1017: );
1018: hr_utility.set_message_token('PRIMARY_VALUE', to_char(p_base_key_value));
1019: hr_utility.raise_error;
1020: WHEN OTHERS THEN
1021: -- reset the OUT params to NULL to cater for NOCOPY
1022: p_validation_start_date := NULL;
1015: ('SESSION_DATE'
1016: ,fnd_date.date_to_chardate(p_effective_date,1)
1017: );
1018: hr_utility.set_message_token('PRIMARY_VALUE', to_char(p_base_key_value));
1019: hr_utility.raise_error;
1020: WHEN OTHERS THEN
1021: -- reset the OUT params to NULL to cater for NOCOPY
1022: p_validation_start_date := NULL;
1023: p_validation_end_date := NULL;
1047: --
1048: BEGIN
1049: IF g_debug THEN
1050: l_proc := g_package||'Get_Update_Dates';
1051: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1052: END IF;
1053: -- get the effective rows
1054: get_effective_rows
1055: (p_date_from => p_effective_date,
1062: p_date_from_row_idx => l_date_from_row_idx);
1063: -- check to ensure that no future rows exist
1064: IF l_effective_rows.COUNT > l_date_from_row_idx THEN
1065: -- future rows exist so error
1066: hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1067: hr_utility.raise_error;
1068: ELSE
1069: -- future rows do no exist
1070: -- providing the current effective start date is not equal to the effective
1063: -- check to ensure that no future rows exist
1064: IF l_effective_rows.COUNT > l_date_from_row_idx THEN
1065: -- future rows exist so error
1066: hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1067: hr_utility.raise_error;
1068: ELSE
1069: -- future rows do no exist
1070: -- providing the current effective start date is not equal to the effective
1071: -- date we must return the the validation start and end dates
1076: l_effective_rows(l_date_from_row_idx).effective_end_date;
1077: ELSE
1078: -- we cannot perform a DateTrack update operation where the effective
1079: -- date is the same as the current effective end date
1080: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1081: hr_utility.raise_error;
1082: END IF;
1083: END IF;
1084: IF g_debug THEN
1077: ELSE
1078: -- we cannot perform a DateTrack update operation where the effective
1079: -- date is the same as the current effective end date
1080: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1081: hr_utility.raise_error;
1082: END IF;
1083: END IF;
1084: IF g_debug THEN
1085: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1081: hr_utility.raise_error;
1082: END IF;
1083: END IF;
1084: IF g_debug THEN
1085: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1086: END IF;
1087: EXCEPTION
1088: WHEN OTHERS THEN
1089: -- reset the OUT params to NULL to cater for NOCOPY
1115: --
1116: Begin
1117: IF g_debug THEN
1118: l_proc := g_package||'Get_Update_Override_Dates';
1119: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1120: END IF;
1121: -- get and lock the effective rows
1122: get_effective_rows
1123: (p_date_from => p_effective_date,
1139: p_validation_start_date := p_effective_date;
1140: p_validation_end_date :=
1141: l_effective_rows(l_effective_rows.LAST).effective_end_date;
1142: ELSE
1143: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1144: hr_utility.set_message_token('DT_MODE', 'update override');
1145: hr_utility.raise_error;
1146: END IF;
1147: ELSE
1140: p_validation_end_date :=
1141: l_effective_rows(l_effective_rows.LAST).effective_end_date;
1142: ELSE
1143: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1144: hr_utility.set_message_token('DT_MODE', 'update override');
1145: hr_utility.raise_error;
1146: END IF;
1147: ELSE
1148: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1141: l_effective_rows(l_effective_rows.LAST).effective_end_date;
1142: ELSE
1143: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1144: hr_utility.set_message_token('DT_MODE', 'update override');
1145: hr_utility.raise_error;
1146: END IF;
1147: ELSE
1148: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1149: hr_utility.raise_error;
1144: hr_utility.set_message_token('DT_MODE', 'update override');
1145: hr_utility.raise_error;
1146: END IF;
1147: ELSE
1148: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1149: hr_utility.raise_error;
1150: END IF;
1151: IF g_debug THEN
1152: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1145: hr_utility.raise_error;
1146: END IF;
1147: ELSE
1148: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1149: hr_utility.raise_error;
1150: END IF;
1151: IF g_debug THEN
1152: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1153: END IF;
1148: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1149: hr_utility.raise_error;
1150: END IF;
1151: IF g_debug THEN
1152: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1153: END IF;
1154: EXCEPTION
1155: WHEN OTHERS THEN
1156: -- reset the OUT params to NULL to cater for NOCOPY
1182: --
1183: BEGIN
1184: IF g_debug THEN
1185: l_proc := g_package||'Get_Update_Change_Insert_Dates';
1186: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1187: END IF;
1188: -- get the effective rows
1189: get_effective_rows
1190: (p_date_from => p_effective_date,
1206: p_validation_start_date := p_effective_date;
1207: p_validation_end_date :=
1208: l_effective_rows(l_date_from_row_idx).effective_end_date;
1209: ELSE
1210: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1211: hr_utility.set_message_token('DT_MODE', 'update change insert');
1212: hr_utility.raise_error;
1213: END IF;
1214: ELSE
1207: p_validation_end_date :=
1208: l_effective_rows(l_date_from_row_idx).effective_end_date;
1209: ELSE
1210: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1211: hr_utility.set_message_token('DT_MODE', 'update change insert');
1212: hr_utility.raise_error;
1213: END IF;
1214: ELSE
1215: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1208: l_effective_rows(l_date_from_row_idx).effective_end_date;
1209: ELSE
1210: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1211: hr_utility.set_message_token('DT_MODE', 'update change insert');
1212: hr_utility.raise_error;
1213: END IF;
1214: ELSE
1215: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1216: hr_utility.raise_error;
1211: hr_utility.set_message_token('DT_MODE', 'update change insert');
1212: hr_utility.raise_error;
1213: END IF;
1214: ELSE
1215: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1216: hr_utility.raise_error;
1217: END IF;
1218: IF g_debug THEN
1219: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1212: hr_utility.raise_error;
1213: END IF;
1214: ELSE
1215: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1216: hr_utility.raise_error;
1217: END IF;
1218: IF g_debug THEN
1219: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1220: END IF;
1215: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1216: hr_utility.raise_error;
1217: END IF;
1218: IF g_debug THEN
1219: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1220: END IF;
1221: EXCEPTION
1222: WHEN OTHERS THEN
1223: -- reset the OUT params to NULL to cater for NOCOPY
1249: --
1250: BEGIN
1251: IF g_debug THEN
1252: l_proc := g_package||'Get_Zap_Dates';
1253: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1254: END IF;
1255: -- get and lock the effective rows from the start of time
1256: get_effective_rows
1257: (p_date_from => dt_api.g_sot,
1269: l_effective_rows(l_effective_rows.FIRST).effective_start_date;
1270: p_validation_end_date :=
1271: l_effective_rows(l_effective_rows.LAST).effective_end_date;
1272: IF g_debug THEN
1273: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1274: END IF;
1275: EXCEPTION
1276: WHEN OTHERS THEN
1277: -- reset the OUT params to NULL to cater for NOCOPY
1338: --
1339: BEGIN
1340: IF g_debug THEN
1341: l_proc := g_package||'Get_Delete_Dates';
1342: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1343: END IF;
1344: -- get and lock the effective rows
1345: get_effective_rows
1346: (p_date_from => p_effective_date,
1426: l_effective_rows(l_effective_rows.LAST).effective_end_date;
1427: ELSE
1428: -- We cannot perform a DateTrack delete operation where the effective date
1429: -- is the same as the maximum effective end date.
1430: hr_utility.set_message(801, 'HR_7185_DT_DEL_NOT_ALLOWED');
1431: hr_utility.raise_error;
1432: END IF;
1433: IF g_debug THEN
1434: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1427: ELSE
1428: -- We cannot perform a DateTrack delete operation where the effective date
1429: -- is the same as the maximum effective end date.
1430: hr_utility.set_message(801, 'HR_7185_DT_DEL_NOT_ALLOWED');
1431: hr_utility.raise_error;
1432: END IF;
1433: IF g_debug THEN
1434: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1435: END IF;
1430: hr_utility.set_message(801, 'HR_7185_DT_DEL_NOT_ALLOWED');
1431: hr_utility.raise_error;
1432: END IF;
1433: IF g_debug THEN
1434: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1435: END IF;
1436: EXCEPTION
1437: WHEN OTHERS THEN
1438: -- reset the OUT params to NULL to cater for NOCOPY
1495: --
1496: BEGIN
1497: IF g_debug THEN
1498: l_proc := g_package||'Get_Future_Change_Dates';
1499: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1500: END IF;
1501: -- get and lock the effective rows
1502: get_effective_rows
1503: (p_date_from => p_effective_date,
1551: -- then we must error as we cannot extend the end date of the current
1552: -- row
1553: IF (l_validation_end_date <=
1554: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
1555: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1556: hr_utility.set_message_token('DT_MODE', ' future changes');
1557: hr_utility.raise_error;
1558: ELSE
1559: -- set the validation_start/end_date OUT params
1552: -- row
1553: IF (l_validation_end_date <=
1554: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
1555: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1556: hr_utility.set_message_token('DT_MODE', ' future changes');
1557: hr_utility.raise_error;
1558: ELSE
1559: -- set the validation_start/end_date OUT params
1560: p_validation_start_date :=
1553: IF (l_validation_end_date <=
1554: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
1555: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1556: hr_utility.set_message_token('DT_MODE', ' future changes');
1557: hr_utility.raise_error;
1558: ELSE
1559: -- set the validation_start/end_date OUT params
1560: p_validation_start_date :=
1561: l_effective_rows(l_date_from_row_idx).effective_end_date + 1;
1563: END IF;
1564: ELSE
1565: -- The current effective end date is alreay the end of time therefore
1566: -- we cannot extend the end date
1567: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1568: hr_utility.raise_error;
1569: END IF;
1570: IF g_debug THEN
1571: Hr_Utility.Set_Location(' Leaving:'||l_proc, 15);
1564: ELSE
1565: -- The current effective end date is alreay the end of time therefore
1566: -- we cannot extend the end date
1567: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1568: hr_utility.raise_error;
1569: END IF;
1570: IF g_debug THEN
1571: Hr_Utility.Set_Location(' Leaving:'||l_proc, 15);
1572: END IF;
1567: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1568: hr_utility.raise_error;
1569: END IF;
1570: IF g_debug THEN
1571: Hr_Utility.Set_Location(' Leaving:'||l_proc, 15);
1572: END IF;
1573: EXCEPTION
1574: WHEN OTHERS THEN
1575: -- reset the OUT params to NULL to cater for NOCOPY
1633: --
1634: Begin
1635: IF g_debug THEN
1636: l_proc := g_package||'Get_Delete_Next_Change_Dates';
1637: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1638: END IF;
1639: -- get and lock the effective rows
1640: get_effective_rows
1641: (p_date_from => p_effective_date,
1707: -- then we must error as we cannot extend the end date of the current
1708: -- row
1709: IF (l_validation_end_date <=
1710: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
1711: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1712: hr_utility.set_message_token('DT_MODE', ' delete next change');
1713: hr_utility.raise_error;
1714: ELSE
1715: -- set the OUT validation params
1708: -- row
1709: IF (l_validation_end_date <=
1710: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
1711: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1712: hr_utility.set_message_token('DT_MODE', ' delete next change');
1713: hr_utility.raise_error;
1714: ELSE
1715: -- set the OUT validation params
1716: -- set the validation start date to the current effective end date + 1
1709: IF (l_validation_end_date <=
1710: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
1711: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1712: hr_utility.set_message_token('DT_MODE', ' delete next change');
1713: hr_utility.raise_error;
1714: ELSE
1715: -- set the OUT validation params
1716: -- set the validation start date to the current effective end date + 1
1717: p_validation_start_date :=
1720: END IF;
1721: ELSE
1722: -- the current effective end date is alreay the end of time therefore
1723: -- we cannot extend the end date
1724: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1725: hr_utility.raise_error;
1726: END IF;
1727: IF g_debug THEN
1728: Hr_Utility.Set_Location(' Leaving:'||l_proc, 25);
1721: ELSE
1722: -- the current effective end date is alreay the end of time therefore
1723: -- we cannot extend the end date
1724: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1725: hr_utility.raise_error;
1726: END IF;
1727: IF g_debug THEN
1728: Hr_Utility.Set_Location(' Leaving:'||l_proc, 25);
1729: END IF;
1724: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1725: hr_utility.raise_error;
1726: END IF;
1727: IF g_debug THEN
1728: Hr_Utility.Set_Location(' Leaving:'||l_proc, 25);
1729: END IF;
1730: EXCEPTION
1731: WHEN OTHERS THEN
1732: -- reset the OUT params to NULL to cater for NOCOPY
1853: l_proc VARCHAR2(72);
1854: l_datetrack_mode VARCHAR2(30);
1855: --
1856: BEGIN
1857: g_debug := hr_utility.debug_enabled;
1858: IF g_debug THEN
1859: l_proc := g_package||'Validate_DT_Mode';
1860: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1861: END IF;
1856: BEGIN
1857: g_debug := hr_utility.debug_enabled;
1858: IF g_debug THEN
1859: l_proc := g_package||'Validate_DT_Mode';
1860: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1861: END IF;
1862: -- upper the datetrack mode
1863: l_datetrack_mode := upper(p_datetrack_mode);
1864: -- check the effective date
2095: p_parent_key_value10 => p_parent_key_value10,
2096: p_validation_start_date => p_validation_start_date,
2097: p_validation_end_date => p_validation_end_date);
2098: ELSE
2099: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
2100: hr_utility.set_message_token('DT_MODE', l_datetrack_mode);
2101: hr_utility.raise_error;
2102: END IF;
2103: IF g_debug THEN
2096: p_validation_start_date => p_validation_start_date,
2097: p_validation_end_date => p_validation_end_date);
2098: ELSE
2099: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
2100: hr_utility.set_message_token('DT_MODE', l_datetrack_mode);
2101: hr_utility.raise_error;
2102: END IF;
2103: IF g_debug THEN
2104: Hr_Utility.Set_Location(' Leaving:'||l_proc, 55);
2097: p_validation_end_date => p_validation_end_date);
2098: ELSE
2099: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
2100: hr_utility.set_message_token('DT_MODE', l_datetrack_mode);
2101: hr_utility.raise_error;
2102: END IF;
2103: IF g_debug THEN
2104: Hr_Utility.Set_Location(' Leaving:'||l_proc, 55);
2105: END IF;
2100: hr_utility.set_message_token('DT_MODE', l_datetrack_mode);
2101: hr_utility.raise_error;
2102: END IF;
2103: IF g_debug THEN
2104: Hr_Utility.Set_Location(' Leaving:'||l_proc, 55);
2105: END IF;
2106: EXCEPTION
2107: WHEN OTHERS THEN
2108: -- reset the OUT params to NULL to cater for NOCOPY
2123: --
2124: l_proc VARCHAR2(72);
2125: --
2126: BEGIN
2127: g_debug := hr_utility.debug_enabled;
2128: IF g_debug THEN
2129: l_proc := g_package||'Validate_DT_Upd_Mode';
2130: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
2131: END IF;
2126: BEGIN
2127: g_debug := hr_utility.debug_enabled;
2128: IF g_debug THEN
2129: l_proc := g_package||'Validate_DT_Upd_Mode';
2130: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
2131: END IF;
2132: IF p_datetrack_mode IS NULL THEN
2133: -- Ensure that the mode is not null
2134: hr_api.mandatory_arg_error(p_api_name => l_proc,
2140: p_datetrack_mode = dt_api.g_update OR
2141: p_datetrack_mode = dt_api.g_update_override OR
2142: p_datetrack_mode = dt_api.g_update_change_insert) THEN
2143: IF g_debug THEN
2144: Hr_Utility.Set_Location(' Leaving:'||l_proc, 10);
2145: END IF;
2146: RETURN(TRUE);
2147: ELSE
2148: IF g_debug THEN
2145: END IF;
2146: RETURN(TRUE);
2147: ELSE
2148: IF g_debug THEN
2149: Hr_Utility.Set_Location(' Leaving:'||l_proc, 15);
2150: END IF;
2151: RETURN(FALSE);
2152: END If;
2153: END Validate_DT_Upd_Mode;
2164: --
2165: l_proc VARCHAR2(72);
2166: --
2167: BEGIN
2168: g_debug := hr_utility.debug_enabled;
2169: IF g_debug THEN
2170: l_proc := g_package||'Validate_DT_Del_Mode';
2171: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
2172: END IF;
2167: BEGIN
2168: g_debug := hr_utility.debug_enabled;
2169: IF g_debug THEN
2170: l_proc := g_package||'Validate_DT_Del_Mode';
2171: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
2172: END IF;
2173: IF p_datetrack_mode IS NULL THEN
2174: -- Ensure that the mode is not null
2175: hr_api.mandatory_arg_error(p_api_name => l_proc,
2181: p_datetrack_mode = dt_api.g_delete OR
2182: p_datetrack_mode = dt_api.g_future_change OR
2183: p_datetrack_mode = dt_api.g_delete_next_change) THEN
2184: IF g_debug THEN
2185: Hr_Utility.Set_Location(' Leaving:'||l_proc, 10);
2186: END IF;
2187: RETURN(TRUE);
2188: ELSE
2189: IF g_debug THEN
2186: END IF;
2187: RETURN(TRUE);
2188: ELSE
2189: IF g_debug THEN
2190: Hr_Utility.Set_Location(' Leaving:'||l_proc, 10);
2191: END IF;
2192: RETURN(FALSE);
2193: END IF;
2194: END Validate_DT_Del_Mode;
2205: --
2206: l_proc VARCHAR2(72);
2207: --
2208: BEGIN
2209: g_debug := hr_utility.debug_enabled;
2210: IF g_debug THEN
2211: l_proc := g_package||'Validate_DT_Upd_Mode';
2212: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
2213: END IF;
2208: BEGIN
2209: g_debug := hr_utility.debug_enabled;
2210: IF g_debug THEN
2211: l_proc := g_package||'Validate_DT_Upd_Mode';
2212: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
2213: END IF;
2214: -- Check the mode is valid
2215: IF (NOT Validate_DT_Upd_Mode(p_datetrack_mode => p_datetrack_mode)) THEN
2216: -- The datetrack mode is invalid therefore we must error
2213: END IF;
2214: -- Check the mode is valid
2215: IF (NOT Validate_DT_Upd_Mode(p_datetrack_mode => p_datetrack_mode)) THEN
2216: -- The datetrack mode is invalid therefore we must error
2217: hr_utility.set_message(801, 'HR_7203_DT_UPD_MODE_INVALID');
2218: hr_utility.raise_error;
2219: END IF;
2220: IF g_debug THEN
2221: Hr_Utility.Set_Location(' Leaving:'||l_proc, 10);
2214: -- Check the mode is valid
2215: IF (NOT Validate_DT_Upd_Mode(p_datetrack_mode => p_datetrack_mode)) THEN
2216: -- The datetrack mode is invalid therefore we must error
2217: hr_utility.set_message(801, 'HR_7203_DT_UPD_MODE_INVALID');
2218: hr_utility.raise_error;
2219: END IF;
2220: IF g_debug THEN
2221: Hr_Utility.Set_Location(' Leaving:'||l_proc, 10);
2222: END IF;
2217: hr_utility.set_message(801, 'HR_7203_DT_UPD_MODE_INVALID');
2218: hr_utility.raise_error;
2219: END IF;
2220: IF g_debug THEN
2221: Hr_Utility.Set_Location(' Leaving:'||l_proc, 10);
2222: END IF;
2223: END Validate_DT_Upd_Mode;
2224: -- ----------------------------------------------------------------------------
2225: -- |------------------------< Validate_DT_Del_Mode >--------------------------|
2234: --
2235: l_proc VARCHAR2(72);
2236: --
2237: BEGIN
2238: g_debug := hr_utility.debug_enabled;
2239: IF g_debug THEN
2240: l_proc := g_package||'Validate_DT_Del_Mode';
2241: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
2242: END IF;
2237: BEGIN
2238: g_debug := hr_utility.debug_enabled;
2239: IF g_debug THEN
2240: l_proc := g_package||'Validate_DT_Del_Mode';
2241: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
2242: END IF;
2243: -- Check the mode is valid
2244: IF (NOT Validate_DT_Del_Mode(p_datetrack_mode => p_datetrack_mode)) THEN
2245: -- The datetrack mode is invalid therefore we must error
2242: END IF;
2243: -- Check the mode is valid
2244: IF (NOT Validate_DT_Del_Mode(p_datetrack_mode => p_datetrack_mode)) THEN
2245: -- The datetrack mode is invalid therefore we must error
2246: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
2247: hr_utility.raise_error;
2248: END IF;
2249: IF g_debug THEN
2250: Hr_Utility.Set_Location(' Leaving:'||l_proc, 10);
2243: -- Check the mode is valid
2244: IF (NOT Validate_DT_Del_Mode(p_datetrack_mode => p_datetrack_mode)) THEN
2245: -- The datetrack mode is invalid therefore we must error
2246: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
2247: hr_utility.raise_error;
2248: END IF;
2249: IF g_debug THEN
2250: Hr_Utility.Set_Location(' Leaving:'||l_proc, 10);
2251: END IF;
2246: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
2247: hr_utility.raise_error;
2248: END IF;
2249: IF g_debug THEN
2250: Hr_Utility.Set_Location(' Leaving:'||l_proc, 10);
2251: END IF;
2252: End Validate_DT_Del_Mode;
2253: -- ----------------------------------------------------------------------------
2254: -- |-----------------------< Get_Object_Version_Number >----------------------|
2273: l_proc VARCHAR2(72);
2274: l_object_num NUMBER; -- Holds new object version number
2275: --
2276: BEGIN
2277: g_debug := hr_utility.debug_enabled;
2278: IF g_debug THEN
2279: l_proc := g_package||'get_object_version_number';
2280: hr_utility.set_location('Entering:'||l_proc, 5);
2281: END IF;
2276: BEGIN
2277: g_debug := hr_utility.debug_enabled;
2278: IF g_debug THEN
2279: l_proc := g_package||'get_object_version_number';
2280: hr_utility.set_location('Entering:'||l_proc, 5);
2281: END IF;
2282: -- Ensure that all the mandatory arguments are not null
2283: -- [ start of change 30.14 ]
2284: IF p_base_table_name IS NULL OR
2310: EXECUTE IMMEDIATE g_dynamic_sql
2311: INTO l_object_num
2312: USING p_base_key_value;
2313: IF g_debug THEN
2314: hr_utility.set_location('Leaving :'||l_proc, 10);
2315: END IF;
2316: RETURN(l_object_num);
2317: END get_object_version_number;
2318: -- ----------------------------------------------------------------------------
2339: l_proc VARCHAR2(72);
2340: l_ret_column number(1); -- Returning Sql Column
2341: --
2342: BEGIN
2343: g_debug := hr_utility.debug_enabled;
2344: IF g_debug THEN
2345: l_proc := g_package||'rows_exist';
2346: hr_utility.set_location('Entering:'||l_proc, 5);
2347: END IF;
2342: BEGIN
2343: g_debug := hr_utility.debug_enabled;
2344: IF g_debug THEN
2345: l_proc := g_package||'rows_exist';
2346: hr_utility.set_location('Entering:'||l_proc, 5);
2347: END IF;
2348: IF (p_base_key_value IS NOT NULL) THEN
2349: IF p_base_table_name IS NULL OR
2350: p_base_key_column IS NULL OR
2387: INTO l_ret_column
2388: USING p_base_key_value, p_to_date, p_from_date;
2389: -- one row exists so return true
2390: IF g_debug THEN
2391: hr_utility.set_location('Leaving:'||l_proc, 10);
2392: END IF;
2393: RETURN(TRUE);
2394: END IF;
2395: EXCEPTION
2394: END IF;
2395: EXCEPTION
2396: WHEN NO_DATA_FOUND THEN
2397: IF g_debug THEN
2398: hr_utility.set_location('Leaving:'||l_proc, 15);
2399: END IF;
2400: -- return false as no rows exist
2401: RETURN(FALSE);
2402: WHEN TOO_MANY_ROWS THEN
2400: -- return false as no rows exist
2401: RETURN(FALSE);
2402: WHEN TOO_MANY_ROWS THEN
2403: IF g_debug THEN
2404: hr_utility.set_location('Leaving:'||l_proc, 20);
2405: END IF;
2406: -- return TRUE because more than one row exists
2407: RETURN(TRUE);
2408: WHEN OTHERS THEN
2432: l_min_date DATE;
2433: l_max_date DATE;
2434: --
2435: BEGIN
2436: g_debug := hr_utility.debug_enabled;
2437: IF g_debug THEN
2438: l_proc := g_package||'check_min_max_dates';
2439: hr_utility.set_location('Entering:'||l_proc, 5);
2440: END IF;
2435: BEGIN
2436: g_debug := hr_utility.debug_enabled;
2437: IF g_debug THEN
2438: l_proc := g_package||'check_min_max_dates';
2439: hr_utility.set_location('Entering:'||l_proc, 5);
2440: END IF;
2441: IF (p_base_key_value IS NOT NULL) THEN
2442: IF p_base_table_name IS NULL OR
2443: p_base_key_column IS NULL OR
2481: -- If they are null then no rows were found therefore we must error
2482: -- as either the table name, base key column name or base key value
2483: -- was incorrectly specified.
2484: IF (l_min_date IS NULL AND l_max_date IS NULL) THEN
2485: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2486: hr_utility.set_message_token('PROCEDURE', l_proc);
2487: hr_utility.set_message_token('STEP','40');
2488: hr_utility.raise_error;
2489: ELSE
2482: -- as either the table name, base key column name or base key value
2483: -- was incorrectly specified.
2484: IF (l_min_date IS NULL AND l_max_date IS NULL) THEN
2485: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2486: hr_utility.set_message_token('PROCEDURE', l_proc);
2487: hr_utility.set_message_token('STEP','40');
2488: hr_utility.raise_error;
2489: ELSE
2490: -- Ensure that the min and max dates fall within the from and to
2483: -- was incorrectly specified.
2484: IF (l_min_date IS NULL AND l_max_date IS NULL) THEN
2485: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2486: hr_utility.set_message_token('PROCEDURE', l_proc);
2487: hr_utility.set_message_token('STEP','40');
2488: hr_utility.raise_error;
2489: ELSE
2490: -- Ensure that the min and max dates fall within the from and to
2491: -- dates
2484: IF (l_min_date IS NULL AND l_max_date IS NULL) THEN
2485: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2486: hr_utility.set_message_token('PROCEDURE', l_proc);
2487: hr_utility.set_message_token('STEP','40');
2488: hr_utility.raise_error;
2489: ELSE
2490: -- Ensure that the min and max dates fall within the from and to
2491: -- dates
2492: IF (p_from_date >= l_min_date AND p_to_date <= l_max_date) THEN
2490: -- Ensure that the min and max dates fall within the from and to
2491: -- dates
2492: IF (p_from_date >= l_min_date AND p_to_date <= l_max_date) THEN
2493: IF g_debug THEN
2494: hr_utility.set_location(' Leaving:'||l_proc, 10);
2495: END IF;
2496: RETURN(TRUE);
2497: ELSE
2498: IF g_debug THEN
2495: END IF;
2496: RETURN(TRUE);
2497: ELSE
2498: IF g_debug THEN
2499: Hr_utility.set_location(' Leaving:'||l_proc, 15);
2500: END IF;
2501: RETURN(FALSE);
2502: END IF;
2503: END IF;
2502: END IF;
2503: END IF;
2504: ELSE
2505: IF g_debug THEN
2506: hr_utility.set_location(' Leaving:'||l_proc, 20);
2507: END IF;
2508: -- The key value has not been specified
2509: RETURN(TRUE);
2510: END IF;
2542: l_date_from_row_idx BINARY_INTEGER;
2543: l_proc varchar2(80) ;
2544:
2545: begin
2546: g_debug := hr_utility.debug_enabled;
2547: IF g_debug THEN
2548: l_proc := g_package||'find_dt_upd_modes_and_dates';
2549: hr_utility.set_location('Entering: '||l_proc,10);
2550: END IF;
2545: begin
2546: g_debug := hr_utility.debug_enabled;
2547: IF g_debug THEN
2548: l_proc := g_package||'find_dt_upd_modes_and_dates';
2549: hr_utility.set_location('Entering: '||l_proc,10);
2550: END IF;
2551: Effective_Date_Valid(p_effective_date => p_effective_date);
2552: -- Ensure that all the mandatory arguments are not null
2553: -- [ start of change 30.14 ]
2589: -- As future rows exist we must set:
2590: -- 1) UPDATE must be set to FALSE
2591: -- 2) UPDATE_OVERRIDE and UPDATE_CHANGE_INSERT must be set TRUE
2592: IF g_debug THEN
2593: hr_utility.set_location(l_proc,20);
2594: END IF;
2595: p_update := FALSE;
2596: p_update_start_date := NULL;
2597: p_update_end_date := NULL;
2607: -- As future rows don't exist we must set:
2608: -- 1) UPDATE must be set to TRUE
2609: -- 2) UPDATE_OVERRIDE and UPDATE_CHANGE_INSERT must be set FALSE
2610: IF g_debug THEN
2611: hr_utility.set_location(l_proc,30);
2612: END IF;
2613: p_update := TRUE;
2614: p_update_start_date := p_effective_date;
2615: p_update_end_date :=
2626: -- we must set:
2627: -- 1) UPDATE must be set to FALSE
2628: -- 2) UPDATE_OVERRIDE and UPDATE_CHANGE_INSERT must be set FALSE
2629: IF g_debug THEN
2630: hr_utility.set_location(l_proc,40);
2631: END IF;
2632: p_update := FALSE;
2633: p_update_start_date := NULL;
2634: p_update_end_date := NULL;
2642:
2643: --Set validation start date and end date for each of the modes
2644: IF p_correction = TRUE THEN
2645: IF g_debug THEN
2646: hr_utility.set_location(l_proc,50);
2647: END IF;
2648:
2649: p_correction_start_date :=
2650: l_effective_rows(l_date_from_row_idx).effective_start_date;
2651: p_correction_end_date :=
2652: l_effective_rows(l_date_from_row_idx).effective_end_date;
2653: END IF;
2654: IF g_debug THEN
2655: hr_utility.set_location('Leaving :'||l_proc, 60);
2656: END IF;
2657: EXCEPTION
2658: WHEN OTHERS THEN
2659: -- set all OUTs to NULL and RAISE
2732: l_validation_end_date DATE;
2733: l_parent_min_date DATE;
2734: l_proc varchar2(80) ;
2735: begin
2736: g_debug := hr_utility.debug_enabled;
2737: if g_debug then
2738: l_proc := g_package||'get_update_modes_for_oaf';
2739: hr_utility.set_location('Entering: '||l_proc,10);
2740: end if;
2735: begin
2736: g_debug := hr_utility.debug_enabled;
2737: if g_debug then
2738: l_proc := g_package||'get_update_modes_for_oaf';
2739: hr_utility.set_location('Entering: '||l_proc,10);
2740: end if;
2741:
2742: Effective_Date_Valid(p_effective_date => p_effective_date);
2743: -- Ensure that all the mandatory arguments are not null
2794: END LOOP;
2795: IF l_date_from_row_idx = 0 THEN
2796: -- a serious internal error has occured. Thisis defensive as this
2797: -- error should not be raised here
2798: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
2799: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
2800: hr_utility.set_message_token('SESSION_DATE',
2801: fnd_date.date_to_chardate(p_effective_date,1));
2802: hr_utility.raise_error;
2795: IF l_date_from_row_idx = 0 THEN
2796: -- a serious internal error has occured. Thisis defensive as this
2797: -- error should not be raised here
2798: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
2799: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
2800: hr_utility.set_message_token('SESSION_DATE',
2801: fnd_date.date_to_chardate(p_effective_date,1));
2802: hr_utility.raise_error;
2803: END IF;
2796: -- a serious internal error has occured. Thisis defensive as this
2797: -- error should not be raised here
2798: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
2799: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
2800: hr_utility.set_message_token('SESSION_DATE',
2801: fnd_date.date_to_chardate(p_effective_date,1));
2802: hr_utility.raise_error;
2803: END IF;
2804: -- DELETE: always set to TRUE if the maximum effective end date is not the
2798: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
2799: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
2800: hr_utility.set_message_token('SESSION_DATE',
2801: fnd_date.date_to_chardate(p_effective_date,1));
2802: hr_utility.raise_error;
2803: END IF;
2804: -- DELETE: always set to TRUE if the maximum effective end date is not the
2805: -- same as the effective date and the effective date if not the
2806: -- end of time
2887: l_effective_rows(l_effective_rows.LAST).effective_end_date;
2888: ELSE
2889: -- We cannot perform a DateTrack delete operation where the effective date
2890: -- is the same as the maximum effective end date.
2891: hr_utility.set_message(801, 'HR_7185_DT_DEL_NOT_ALLOWED');
2892: hr_utility.raise_error;
2893: END IF;
2894: END IF;
2895:
2888: ELSE
2889: -- We cannot perform a DateTrack delete operation where the effective date
2890: -- is the same as the maximum effective end date.
2891: hr_utility.set_message(801, 'HR_7185_DT_DEL_NOT_ALLOWED');
2892: hr_utility.raise_error;
2893: END IF;
2894: END IF;
2895:
2896: IF p_future_change = TRUE THEN
2904: -- row
2905: l_validation_end_date := l_parent_min_date;
2906: IF (l_validation_end_date <=
2907: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
2908: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
2909: hr_utility.set_message_token('DT_MODE', ' future changes');
2910: hr_utility.raise_error;
2911: ELSE
2912: -- set the validation_start/end_date OUT params
2905: l_validation_end_date := l_parent_min_date;
2906: IF (l_validation_end_date <=
2907: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
2908: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
2909: hr_utility.set_message_token('DT_MODE', ' future changes');
2910: hr_utility.raise_error;
2911: ELSE
2912: -- set the validation_start/end_date OUT params
2913: p_del_future_start_date :=
2906: IF (l_validation_end_date <=
2907: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
2908: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
2909: hr_utility.set_message_token('DT_MODE', ' future changes');
2910: hr_utility.raise_error;
2911: ELSE
2912: -- set the validation_start/end_date OUT params
2913: p_del_future_start_date :=
2914: l_effective_rows(l_date_from_row_idx).effective_end_date + 1;
2916: END IF;
2917: ELSE
2918: -- The current effective end date is alreay the end of time therefore
2919: -- we cannot extend the end date
2920: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
2921: hr_utility.raise_error;
2922: END IF;
2923: END IF;
2924:
2917: ELSE
2918: -- The current effective end date is alreay the end of time therefore
2919: -- we cannot extend the end date
2920: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
2921: hr_utility.raise_error;
2922: END IF;
2923: END IF;
2924:
2925: IF p_delete_next_change = TRUE THEN
2951: -- then we must error as we cannot extend the end date of the current
2952: -- row
2953: IF (l_validation_end_date <=
2954: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
2955: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
2956: hr_utility.set_message_token('DT_MODE', ' delete next change');
2957: hr_utility.raise_error;
2958: ELSE
2959: -- set the OUT validation params
2952: -- row
2953: IF (l_validation_end_date <=
2954: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
2955: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
2956: hr_utility.set_message_token('DT_MODE', ' delete next change');
2957: hr_utility.raise_error;
2958: ELSE
2959: -- set the OUT validation params
2960: -- set the validation start date to the current effective end date + 1
2953: IF (l_validation_end_date <=
2954: l_effective_rows(l_date_from_row_idx).effective_end_date) THEN
2955: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
2956: hr_utility.set_message_token('DT_MODE', ' delete next change');
2957: hr_utility.raise_error;
2958: ELSE
2959: -- set the OUT validation params
2960: -- set the validation start date to the current effective end date + 1
2961: p_del_next_start_date :=
2964: END IF;
2965: ELSE
2966: -- the current effective end date is alreay the end of time therefore
2967: -- we cannot extend the end date
2968: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
2969: hr_utility.raise_error;
2970: END IF;
2971: END IF;
2972:
2965: ELSE
2966: -- the current effective end date is alreay the end of time therefore
2967: -- we cannot extend the end date
2968: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
2969: hr_utility.raise_error;
2970: END IF;
2971: END IF;
2972:
2973:
2971: END IF;
2972:
2973:
2974: IF g_debug THEN
2975: Hr_Utility.Set_Location('Leaving :'||l_proc, 30);
2976: END IF;
2977:
2978: exception
2979: when others then