[Home] [Help]
160: --
161: l_proc varchar2(72) := g_package||'Effective_Date_Valid';
162: --
163: Begin
164: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
165: --
166: -- Ensure that all the mandatory arguments are not null
167: --
168: hr_api.mandatory_arg_error(p_api_name => l_proc,
169: p_argument => 'p_effective_date',
170: p_argument_value => p_effective_date);
171:
172: If (p_effective_date < Hr_Api.g_sot) then
173: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
174: hr_utility.set_message_token('PROCEDURE', l_proc);
175: hr_utility.set_message_token('STEP','10');
176: hr_utility.raise_error;
177: End If;
170: p_argument_value => p_effective_date);
171:
172: If (p_effective_date < Hr_Api.g_sot) then
173: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
174: hr_utility.set_message_token('PROCEDURE', l_proc);
175: hr_utility.set_message_token('STEP','10');
176: hr_utility.raise_error;
177: End If;
178: --
171:
172: If (p_effective_date < Hr_Api.g_sot) then
173: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
174: hr_utility.set_message_token('PROCEDURE', l_proc);
175: hr_utility.set_message_token('STEP','10');
176: hr_utility.raise_error;
177: End If;
178: --
179: -- Check that effective_date does not include a time component. If set
172: If (p_effective_date < Hr_Api.g_sot) then
173: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
174: hr_utility.set_message_token('PROCEDURE', l_proc);
175: hr_utility.set_message_token('STEP','10');
176: hr_utility.raise_error;
177: End If;
178: --
179: -- Check that effective_date does not include a time component. If set
180: -- then raise an error because it should have been truncated to just a day,
180: -- then raise an error because it should have been truncated to just a day,
181: -- month year value before the DT logic is called.
182: --
183: if p_effective_date <> trunc(p_effective_date) then
184: hr_utility.set_message(801, 'HR_51322_DT_TIME_SET');
185: hr_utility.raise_error;
186: end if;
187: --
188: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
181: -- month year value before the DT logic is called.
182: --
183: if p_effective_date <> trunc(p_effective_date) then
184: hr_utility.set_message(801, 'HR_51322_DT_TIME_SET');
185: hr_utility.raise_error;
186: end if;
187: --
188: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
189: End Effective_Date_Valid;
184: hr_utility.set_message(801, 'HR_51322_DT_TIME_SET');
185: hr_utility.raise_error;
186: end if;
187: --
188: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
189: End Effective_Date_Valid;
190: -- ----------------------------------------------------------------------------
191: -- |----------------------< Return_Effective_Dates >--------------------------|
192: -- ----------------------------------------------------------------------------
207: --
208: l_proc VARCHAR2(72) := g_package||'return_effective_dates';
209: --
210: BEGIN
211: hr_utility.set_location('Entering:'||l_proc, 5);
212: effective_date_valid(p_effective_date => p_effective_date);
213: -- Define dynamic sql text with substitution tokens
214: pay_prt_shd.g_dynamic_sql :=
215: 'select t.effective_start_date, ' ||
224: ,p_effective_end_date
225: USING p_base_key_value
226: ,p_effective_date;
227: --
228: hr_utility.set_location('Leaving :'||l_proc, 45);
229: EXCEPTION
230: WHEN NO_DATA_FOUND THEN
231: -- As no rows were returned we must error
232: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
228: hr_utility.set_location('Leaving :'||l_proc, 45);
229: EXCEPTION
230: WHEN NO_DATA_FOUND THEN
231: -- As no rows were returned we must error
232: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
233: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
234: hr_utility.set_message_token
235: ('SESSION_DATE'
236: ,fnd_date.date_to_chardate(p_effective_date)
229: EXCEPTION
230: WHEN NO_DATA_FOUND THEN
231: -- As no rows were returned we must error
232: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
233: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
234: hr_utility.set_message_token
235: ('SESSION_DATE'
236: ,fnd_date.date_to_chardate(p_effective_date)
237: );
230: WHEN NO_DATA_FOUND THEN
231: -- As no rows were returned we must error
232: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
233: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
234: hr_utility.set_message_token
235: ('SESSION_DATE'
236: ,fnd_date.date_to_chardate(p_effective_date)
237: );
238: hr_utility.raise_error;
234: hr_utility.set_message_token
235: ('SESSION_DATE'
236: ,fnd_date.date_to_chardate(p_effective_date)
237: );
238: hr_utility.raise_error;
239: WHEN TOO_MANY_ROWS THEN
240: hr_utility.set_message(801, 'HR_7181_DT_OVERLAP_ROWS');
241: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
242: hr_utility.set_message_token
236: ,fnd_date.date_to_chardate(p_effective_date)
237: );
238: hr_utility.raise_error;
239: WHEN TOO_MANY_ROWS THEN
240: hr_utility.set_message(801, 'HR_7181_DT_OVERLAP_ROWS');
241: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
242: hr_utility.set_message_token
243: ('SESSION_DATE'
244: ,fnd_date.date_to_chardate(p_effective_date)
237: );
238: hr_utility.raise_error;
239: WHEN TOO_MANY_ROWS THEN
240: hr_utility.set_message(801, 'HR_7181_DT_OVERLAP_ROWS');
241: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
242: hr_utility.set_message_token
243: ('SESSION_DATE'
244: ,fnd_date.date_to_chardate(p_effective_date)
245: );
238: hr_utility.raise_error;
239: WHEN TOO_MANY_ROWS THEN
240: hr_utility.set_message(801, 'HR_7181_DT_OVERLAP_ROWS');
241: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
242: hr_utility.set_message_token
243: ('SESSION_DATE'
244: ,fnd_date.date_to_chardate(p_effective_date)
245: );
246: hr_utility.set_message_token('PRIMARY_VALUE', to_char(p_base_key_value));
242: hr_utility.set_message_token
243: ('SESSION_DATE'
244: ,fnd_date.date_to_chardate(p_effective_date)
245: );
246: hr_utility.set_message_token('PRIMARY_VALUE', to_char(p_base_key_value));
247: hr_utility.raise_error;
248: WHEN OTHERS THEN
249: RAISE;
250: --
243: ('SESSION_DATE'
244: ,fnd_date.date_to_chardate(p_effective_date)
245: );
246: hr_utility.set_message_token('PRIMARY_VALUE', to_char(p_base_key_value));
247: hr_utility.raise_error;
248: WHEN OTHERS THEN
249: RAISE;
250: --
251: END return_effective_dates;
270: l_proc VARCHAR2(72) := g_package||'return_max_end_date';
271: l_max_date DATE;
272: --
273: BEGIN
274: hr_utility.set_location('Entering:'||l_proc, 5);
275: -- Ensure that all the mandatory arguments are not null
276: hr_api.mandatory_arg_error(p_api_name => l_proc,
277: p_argument => 'p_base_table_name',
278: p_argument_value => p_base_table_name);
292: EXECUTE IMMEDIATE pay_prt_shd.g_dynamic_sql
293: INTO l_max_date
294: USING p_base_key_value;
295: --
296: hr_utility.set_location('Leaving :'||l_proc, 10);
297: RETURN(l_max_date);
298: --
299: END return_max_end_date;
300: -- ----------------------------------------------------------------------------
316: l_proc VARCHAR2(72) := g_package||'return_min_start_date';
317: l_min_date DATE;
318: --
319: BEGIN
320: hr_utility.set_location('Entering:'||l_proc, 5);
321: -- Define dynamic sql text with substitution tokens
322: pay_prt_shd.g_dynamic_sql :=
323: 'select min(t.effective_start_date) '||
324: 'from '||p_base_table_name||' t '||
329: USING p_base_key_value;
330: -- Need to ensure that the minimum date is NOT null. If it is then we
331: -- must error
332: IF (l_min_date IS NULL) THEN
333: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
334: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
335: hr_utility.raise_error;
336: END IF;
337: --
330: -- Need to ensure that the minimum date is NOT null. If it is then we
331: -- must error
332: IF (l_min_date IS NULL) THEN
333: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
334: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
335: hr_utility.raise_error;
336: END IF;
337: --
338: hr_utility.set_location('Leaving :'||l_proc, 45);
331: -- must error
332: IF (l_min_date IS NULL) THEN
333: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
334: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
335: hr_utility.raise_error;
336: END IF;
337: --
338: hr_utility.set_location('Leaving :'||l_proc, 45);
339: RETURN(l_min_date);
334: hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
335: hr_utility.raise_error;
336: END IF;
337: --
338: hr_utility.set_location('Leaving :'||l_proc, 45);
339: RETURN(l_min_date);
340: END return_min_start_date;
341: -- ----------------------------------------------------------------------------
342: -- |-------------------------< Future_Rows_Exists >---------------------------|
363: l_effective_end_date date; -- Current effective end date
364: l_max_effective_end_date date; -- Maximum effective end date
365: --
366: Begin
367: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
368: --
369: -- Must ensure that a row exists as of the effective date supplied
370: -- and we need the current effective end date
371: --
392: If (l_max_effective_end_date > l_effective_end_date) then
393: l_boolean := TRUE;
394: End If;
395: --
396: Hr_Utility.Set_Location('Leaving :'||l_proc, 15);
397: Return(l_boolean);
398: --
399: End Future_Rows_Exist;
400: -- ----------------------------------------------------------------------------
454: l_parent_key_column varchar2(30);
455: l_parent_key_value number;
456: --
457: Begin
458: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
459: --
460: -- <
461: -- For l_counter In 1..1 Loop
462: --
532: --
533: -- The parental rows specified do not exist as of the effective date
534: -- therefore a serious integrity problem has ocurred
535: --
536: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
537: hr_utility.set_message_token('ARGUMENT', upper(l_parent_key_column));
538: hr_utility.raise_error;
539: Else
540: --
533: -- The parental rows specified do not exist as of the effective date
534: -- therefore a serious integrity problem has ocurred
535: --
536: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
537: hr_utility.set_message_token('ARGUMENT', upper(l_parent_key_column));
538: hr_utility.raise_error;
539: Else
540: --
541: -- The LEAST function will then compare the working l_min_date with the
534: -- therefore a serious integrity problem has ocurred
535: --
536: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
537: hr_utility.set_message_token('ARGUMENT', upper(l_parent_key_column));
538: hr_utility.raise_error;
539: Else
540: --
541: -- The LEAST function will then compare the working l_min_date with the
542: -- returned maximum effective end date (l_temp_date) and set the
546: End If;
547: End If;
548: -- End Loop;
549: --
550: Hr_Utility.Set_Location('Leaving :'||l_proc, 15);
551: Return(l_min_date);
552: --
553: End Return_Min_Parent_End_Date;
554: -- ----------------------------------------------------------------------------
608: l_parent_key_column varchar2(30);
609: l_parent_key_value number;
610: --
611: Begin
612: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
613: --
614: /*
615: <
616: For l_counter In 1..10 Loop
688: --
689: -- The parental rows specified do not exist as of the effective date
690: -- therefore a serious integrity problem has ocurred
691: --
692: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
693: hr_utility.set_message_token('ARGUMENT', upper(l_parent_key_column));
694: hr_utility.raise_error;
695: Else
696: --
689: -- The parental rows specified do not exist as of the effective date
690: -- therefore a serious integrity problem has ocurred
691: --
692: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
693: hr_utility.set_message_token('ARGUMENT', upper(l_parent_key_column));
694: hr_utility.raise_error;
695: Else
696: --
697: -- The LEAST function will then compare the working l_min_date with the
690: -- therefore a serious integrity problem has ocurred
691: --
692: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
693: hr_utility.set_message_token('ARGUMENT', upper(l_parent_key_column));
694: hr_utility.raise_error;
695: Else
696: --
697: -- The LEAST function will then compare the working l_min_date with the
698: -- returned miniumum effective start date (l_temp_date) and set the
702: End If;
703: End If;
704: -- End Loop;
705: --
706: Hr_Utility.Set_Location('Leaving :'||l_proc, 15);
707: Return(l_min_date);
708: --
709: End Return_Min_Parent_Start_Date;
710: -- ----------------------------------------------------------------------------
731: TYPE l_csr_type IS REF CURSOR; -- define weak REF CURSOR type
732: l_csr l_csr_type;
733: --
734: BEGIN
735: hr_utility.set_location('Entering:'||l_proc, 5);
736: --
737: -- Ensure that all the required parameters exist
738: -- Note: we don't check the p_key_value argument
739: --
767: EXIT WHEN l_csr%NOTFOUND; -- exit loop when last row is fetched
768: END LOOP;
769: CLOSE l_csr;
770: --
771: hr_utility.set_location(' Leaving:'||l_proc, 10);
772: END IF;
773: EXCEPTION
774: WHEN NO_DATA_FOUND THEN
775: -- The parental rows specified do not exist as of the effective date
773: EXCEPTION
774: WHEN NO_DATA_FOUND THEN
775: -- The parental rows specified do not exist as of the effective date
776: -- therefore a serious integrity problem has ocurred
777: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
778: hr_utility.set_message_token('ARGUMENT', upper(p_key_column));
779: hr_utility.raise_error;
780: WHEN hr_api.object_locked THEN
781: -- The object is locked therefore we need to supply a meaningful
774: WHEN NO_DATA_FOUND THEN
775: -- The parental rows specified do not exist as of the effective date
776: -- therefore a serious integrity problem has ocurred
777: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
778: hr_utility.set_message_token('ARGUMENT', upper(p_key_column));
779: hr_utility.raise_error;
780: WHEN hr_api.object_locked THEN
781: -- The object is locked therefore we need to supply a meaningful
782: -- error message.
775: -- The parental rows specified do not exist as of the effective date
776: -- therefore a serious integrity problem has ocurred
777: hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
778: hr_utility.set_message_token('ARGUMENT', upper(p_key_column));
779: hr_utility.raise_error;
780: WHEN hr_api.object_locked THEN
781: -- The object is locked therefore we need to supply a meaningful
782: -- error message.
783: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
779: hr_utility.raise_error;
780: WHEN hr_api.object_locked THEN
781: -- The object is locked therefore we need to supply a meaningful
782: -- error message.
783: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
784: hr_utility.set_message_token('TABLE_NAME', p_table_name);
785: hr_utility.raise_error;
786: WHEN OTHERS THEN
787: RAISE;
780: WHEN hr_api.object_locked THEN
781: -- The object is locked therefore we need to supply a meaningful
782: -- error message.
783: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
784: hr_utility.set_message_token('TABLE_NAME', p_table_name);
785: hr_utility.raise_error;
786: WHEN OTHERS THEN
787: RAISE;
788: --
781: -- The object is locked therefore we need to supply a meaningful
782: -- error message.
783: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
784: hr_utility.set_message_token('TABLE_NAME', p_table_name);
785: hr_utility.raise_error;
786: WHEN OTHERS THEN
787: RAISE;
788: --
789: END lck_future_rows;
805: --
806: l_proc varchar2(72) := g_package||'Lck_Parent';
807: --
808: Begin
809: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
810: --
811: Lck_Future_Rows
812: (p_effective_date => p_effective_date,
813: p_table_name => p_parent_table_name,
813: p_table_name => p_parent_table_name,
814: p_key_column => p_parent_key_column,
815: p_key_value => p_parent_key_value);
816: --
817: Hr_Utility.Set_Location(' Leaving:'||l_proc, 35);
818: End Lck_Parent;
819: -- ----------------------------------------------------------------------------
820: -- |----------------------------< Lck_Child >---------------------------------|
821: -- ----------------------------------------------------------------------------
854: TYPE l_cursor_type IS REF CURSOR; -- define weak REF CURSOR type
855: l_cursor l_cursor_type;
856: --
857: BEGIN
858: hr_utility.set_location('Entering:'||l_proc, 5);
859: --
860: -- Ensure that all the required parameters exist
861: -- Note: we don't check the p_parent_key_value argument
862: --
876: -- If the p_parent_key_value is null then we must not
877: -- process the sql as it could be a nullable column.
878: --
879: IF (p_parent_key_value IS NOT NULL) THEN
880: hr_utility.set_location(l_proc, 10);
881: -- Define dynamic sql text with substitution tokens
882: pay_prt_shd.g_dynamic_sql :=
883: 'select t1.effective_end_date effective_end_date ' ||
884: 'from '||p_child_table_name||' t1 ' ||
896: -- open a cursor
897: OPEN l_cursor
898: FOR pay_prt_shd.g_dynamic_sql
899: USING p_parent_key_value;
900: hr_utility.set_location(l_proc, 15);
901: --
902: LOOP
903: FETCH l_cursor INTO l_lck_date;
904: hr_utility.set_location(l_proc, 20);
900: hr_utility.set_location(l_proc, 15);
901: --
902: LOOP
903: FETCH l_cursor INTO l_lck_date;
904: hr_utility.set_location(l_proc, 20);
905: EXIT WHEN l_cursor%NOTFOUND;
906: -- For each locked row we must ensure that the maximum end date is NOT
907: -- greater than the validation start date
908: IF (l_lck_date >= p_validation_start_date) THEN
907: -- greater than the validation start date
908: IF (l_lck_date >= p_validation_start_date) THEN
909: -- The maximum end date is greater than or equal to the
910: -- validation start date therefore we must error
911: hr_utility.set_location(l_proc, 22);
912: hr_utility.set_message(801, 'HR_7201_DT_NO_DELETE_CHILD');
913: hr_utility.raise_error;
914: END IF;
915: hr_utility.set_location('Entering:'||l_proc, 25);
908: IF (l_lck_date >= p_validation_start_date) THEN
909: -- The maximum end date is greater than or equal to the
910: -- validation start date therefore we must error
911: hr_utility.set_location(l_proc, 22);
912: hr_utility.set_message(801, 'HR_7201_DT_NO_DELETE_CHILD');
913: hr_utility.raise_error;
914: END IF;
915: hr_utility.set_location('Entering:'||l_proc, 25);
916: END LOOP;
909: -- The maximum end date is greater than or equal to the
910: -- validation start date therefore we must error
911: hr_utility.set_location(l_proc, 22);
912: hr_utility.set_message(801, 'HR_7201_DT_NO_DELETE_CHILD');
913: hr_utility.raise_error;
914: END IF;
915: hr_utility.set_location('Entering:'||l_proc, 25);
916: END LOOP;
917: --
911: hr_utility.set_location(l_proc, 22);
912: hr_utility.set_message(801, 'HR_7201_DT_NO_DELETE_CHILD');
913: hr_utility.raise_error;
914: END IF;
915: hr_utility.set_location('Entering:'||l_proc, 25);
916: END LOOP;
917: --
918: CLOSE l_cursor;
919: hr_utility.set_location(' Leaving:'||l_proc, 35);
915: hr_utility.set_location('Entering:'||l_proc, 25);
916: END LOOP;
917: --
918: CLOSE l_cursor;
919: hr_utility.set_location(' Leaving:'||l_proc, 35);
920: END IF;
921: EXCEPTION
922: WHEN hr_api.object_locked THEN
923: IF l_cursor%ISOPEN THEN
924: CLOSE l_cursor;
925: END IF;
926: -- The object is locked therefore we need to supply a meaningful
927: -- error message.
928: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
929: hr_utility.set_message_token('TABLE_NAME', p_child_table_name);
930: hr_utility.raise_error;
931: --
932: WHEN OTHERS THEN
925: END IF;
926: -- The object is locked therefore we need to supply a meaningful
927: -- error message.
928: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
929: hr_utility.set_message_token('TABLE_NAME', p_child_table_name);
930: hr_utility.raise_error;
931: --
932: WHEN OTHERS THEN
933: IF l_cursor%ISOPEN THEN
926: -- The object is locked therefore we need to supply a meaningful
927: -- error message.
928: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
929: hr_utility.set_message_token('TABLE_NAME', p_child_table_name);
930: hr_utility.raise_error;
931: --
932: WHEN OTHERS THEN
933: IF l_cursor%ISOPEN THEN
934: CLOSE l_cursor;
994: l_counter integer; -- Loop counter
995: l_dummy_date date;
996: --
997: Begin
998: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
999: --
1000: -- Step 1: Lock all parent rows specified from the effective date onwards
1001: -- providing that the p_enforce_foreign_locking is TRUE.
1002: --
1149: p_parent_key_column10 => p_parent_key_column10,
1150: p_parent_key_value10 => p_parent_key_value10);
1151: */
1152: --
1153: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1154: --
1155: End Get_Insert_Dates;
1156: -- ----------------------------------------------------------------------------
1157: -- |-----------------------< Get_Correction_Dates >---------------------------|
1175: l_effective_start_date date; -- Holds current effective start date
1176: l_effective_end_date date; -- Holds current effective end date
1177: --
1178: Begin
1179: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1180: Return_Effective_Dates
1181: (p_effective_date => p_effective_date,
1182: p_base_table_name => p_base_table_name,
1183: p_base_key_column => p_base_key_column,
1190: --
1191: p_validation_start_date := l_effective_start_date;
1192: p_validation_end_date := l_effective_end_date;
1193: --
1194: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1195: End Get_Correction_Dates;
1196: -- ----------------------------------------------------------------------------
1197: -- |--------------------------< Get_Update_Dates >----------------------------|
1198: -- ----------------------------------------------------------------------------
1215: l_effective_start_date date; -- Holds current effective start date
1216: l_effective_end_date date; -- Holds current effective end date
1217: --
1218: Begin
1219: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1220: --
1221: -- Determine if any future rows exist
1222: --
1223: If NOT (Future_Rows_Exist
1244: --
1245: -- We cannot perform a DateTrack update operation where the effective
1246: -- date is the same as the current effective end date
1247: --
1248: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1249: hr_utility.raise_error;
1250: End If;
1251: Else
1252: hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1245: -- We cannot perform a DateTrack update operation where the effective
1246: -- date is the same as the current effective end date
1247: --
1248: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1249: hr_utility.raise_error;
1250: End If;
1251: Else
1252: hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1253: hr_utility.raise_error;
1248: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1249: hr_utility.raise_error;
1250: End If;
1251: Else
1252: hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1253: hr_utility.raise_error;
1254: End If;
1255: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1256: --
1249: hr_utility.raise_error;
1250: End If;
1251: Else
1252: hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1253: hr_utility.raise_error;
1254: End If;
1255: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1256: --
1257: End Get_Update_Dates;
1251: Else
1252: hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1253: hr_utility.raise_error;
1254: End If;
1255: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1256: --
1257: End Get_Update_Dates;
1258: -- ----------------------------------------------------------------------------
1259: -- |-------------------< Get_Update_Override_Dates >--------------------------|
1278: l_effective_start_date date; -- Holds current effective start date
1279: l_effective_end_date date; -- Holds current effective end date
1280: --
1281: Begin
1282: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1283: Return_Effective_Dates
1284: (p_effective_date => p_effective_date,
1285: p_base_table_name => p_base_table_name,
1286: p_base_key_column => p_base_key_column,
1307: (p_base_table_name => p_base_table_name,
1308: p_base_key_column => p_base_key_column,
1309: p_base_key_value => p_base_key_value);
1310: Else
1311: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1312: hr_utility.set_message_token('DT_MODE', 'update override');
1313: hr_utility.raise_error;
1314: End If;
1315: Else
1308: p_base_key_column => p_base_key_column,
1309: p_base_key_value => p_base_key_value);
1310: Else
1311: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1312: hr_utility.set_message_token('DT_MODE', 'update override');
1313: hr_utility.raise_error;
1314: End If;
1315: Else
1316: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1309: p_base_key_value => p_base_key_value);
1310: Else
1311: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1312: hr_utility.set_message_token('DT_MODE', 'update override');
1313: hr_utility.raise_error;
1314: End If;
1315: Else
1316: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1317: hr_utility.raise_error;
1312: hr_utility.set_message_token('DT_MODE', 'update override');
1313: hr_utility.raise_error;
1314: End If;
1315: Else
1316: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1317: hr_utility.raise_error;
1318: End If;
1319: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1320: --
1313: hr_utility.raise_error;
1314: End If;
1315: Else
1316: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1317: hr_utility.raise_error;
1318: End If;
1319: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1320: --
1321: End Get_Update_Override_Dates;
1315: Else
1316: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1317: hr_utility.raise_error;
1318: End If;
1319: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1320: --
1321: End Get_Update_Override_Dates;
1322: -- ----------------------------------------------------------------------------
1323: -- |-----------------< Get_Update_Change_Insert_Dates >-----------------------|
1342: l_effective_start_date date; -- Holds current effective start date
1343: l_effective_end_date date; -- Holds current effective end date
1344: --
1345: Begin
1346: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1347: Return_Effective_Dates
1348: (p_effective_date => p_effective_date,
1349: p_base_table_name => p_base_table_name,
1350: p_base_key_column => p_base_key_column,
1368: p_base_key_value => p_base_key_value)) then
1369: p_validation_start_date := p_effective_date;
1370: p_validation_end_date := l_effective_end_date;
1371: Else
1372: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1373: hr_utility.set_message_token('DT_MODE', 'update change insert');
1374: hr_utility.raise_error;
1375: End If;
1376: Else
1369: p_validation_start_date := p_effective_date;
1370: p_validation_end_date := l_effective_end_date;
1371: Else
1372: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1373: hr_utility.set_message_token('DT_MODE', 'update change insert');
1374: hr_utility.raise_error;
1375: End If;
1376: Else
1377: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1370: p_validation_end_date := l_effective_end_date;
1371: Else
1372: hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1373: hr_utility.set_message_token('DT_MODE', 'update change insert');
1374: hr_utility.raise_error;
1375: End If;
1376: Else
1377: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1378: hr_utility.raise_error;
1373: hr_utility.set_message_token('DT_MODE', 'update change insert');
1374: hr_utility.raise_error;
1375: End If;
1376: Else
1377: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1378: hr_utility.raise_error;
1379: End If;
1380: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1381: --
1374: hr_utility.raise_error;
1375: End If;
1376: Else
1377: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1378: hr_utility.raise_error;
1379: End If;
1380: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1381: --
1382: End Get_Update_Change_Insert_Dates;
1376: Else
1377: hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1378: hr_utility.raise_error;
1379: End If;
1380: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1381: --
1382: End Get_Update_Change_Insert_Dates;
1383: -- ----------------------------------------------------------------------------
1384: -- |----------------------------< Get_Zap_Dates >-----------------------------|
1400: --
1401: l_proc varchar2(72) := g_package||'Get_Zap_Dates';
1402: --
1403: Begin
1404: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1405: p_validation_start_date := Return_Min_Start_Date
1406: (p_base_table_name => p_base_table_name,
1407: p_base_key_column => p_base_key_column,
1408: p_base_key_value => p_base_key_value);
1411: (p_base_table_name => p_base_table_name,
1412: p_base_key_column => p_base_key_column,
1413: p_base_key_value => p_base_key_value);
1414: --
1415: Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1416: --
1417: End Get_Zap_Dates;
1418: -- ----------------------------------------------------------------------------
1419: -- |--------------------------< Get_Delete_Dates >----------------------------|
1448: l_child_key_column varchar2(30);
1449: l_child_fk_column varchar2(30);
1450: --
1451: Begin
1452: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1453: --
1454: If p_enforce_foreign_locking Then
1455: <
1456: For l_counter In 1..2 Loop
1510: --
1511: -- We cannot perform a DateTrack delete operation where the effective date
1512: -- is the same as the maximum effective end date.
1513: --
1514: hr_utility.set_message(801, 'HR_7185_DT_DEL_NOT_ALLOWED');
1515: hr_utility.raise_error;
1516: End If;
1517: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1518: --
1511: -- We cannot perform a DateTrack delete operation where the effective date
1512: -- is the same as the maximum effective end date.
1513: --
1514: hr_utility.set_message(801, 'HR_7185_DT_DEL_NOT_ALLOWED');
1515: hr_utility.raise_error;
1516: End If;
1517: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1518: --
1519: End Get_Delete_Dates;
1513: --
1514: hr_utility.set_message(801, 'HR_7185_DT_DEL_NOT_ALLOWED');
1515: hr_utility.raise_error;
1516: End If;
1517: Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1518: --
1519: End Get_Delete_Dates;
1520: -- ----------------------------------------------------------------------------
1521: -- |-----------------------< Get_Future_Change_Dates >------------------------|
1572: l_effective_end_date date; -- Holds current effective end date
1573: l_validation_end_date date;
1574: --
1575: Begin
1576: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1577: Return_Effective_Dates
1578: (p_effective_date => p_effective_date,
1579: p_base_table_name => p_base_table_name,
1580: p_base_key_column => p_base_key_column,
1628: -- then we must error as we cannot extend the end date of the current
1629: -- row
1630: --
1631: If (l_validation_end_date <= l_effective_end_date) then
1632: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1633: hr_utility.set_message_token('DT_MODE', ' future changes');
1634: hr_utility.raise_error;
1635: Else
1636: p_validation_end_date := l_validation_end_date;
1629: -- row
1630: --
1631: If (l_validation_end_date <= l_effective_end_date) then
1632: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1633: hr_utility.set_message_token('DT_MODE', ' future changes');
1634: hr_utility.raise_error;
1635: Else
1636: p_validation_end_date := l_validation_end_date;
1637: End If;
1630: --
1631: If (l_validation_end_date <= l_effective_end_date) then
1632: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1633: hr_utility.set_message_token('DT_MODE', ' future changes');
1634: hr_utility.raise_error;
1635: Else
1636: p_validation_end_date := l_validation_end_date;
1637: End If;
1638: Else
1639: --
1640: -- The current effective end date is alreay the end of time therefore
1641: -- we cannot extend the end date
1642: --
1643: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1644: hr_utility.raise_error;
1645: End If;
1646: --
1647: Hr_Utility.Set_Location(' Leaving:'||l_proc, 15);
1640: -- The current effective end date is alreay the end of time therefore
1641: -- we cannot extend the end date
1642: --
1643: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1644: hr_utility.raise_error;
1645: End If;
1646: --
1647: Hr_Utility.Set_Location(' Leaving:'||l_proc, 15);
1648: --
1643: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1644: hr_utility.raise_error;
1645: End If;
1646: --
1647: Hr_Utility.Set_Location(' Leaving:'||l_proc, 15);
1648: --
1649: End Get_Future_Change_Dates;
1650: -- ----------------------------------------------------------------------------
1651: -- |--------------------< Get_Delete_Next_Change_Dates >----------------------|
1706: l_future_effective_end_date date; -- Holds the end date of next row
1707: l_min_parent_end_date date; -- Holds the min parental end date
1708: --
1709: Begin
1710: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1711: Return_Effective_Dates
1712: (p_effective_date => p_effective_date,
1713: p_base_table_name => p_base_table_name,
1714: p_base_key_column => p_base_key_column,
1795: -- then we must error as we cannot extend the end date of the current
1796: -- row
1797: --
1798: If (l_validation_end_date <= l_effective_end_date) then
1799: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1800: hr_utility.set_message_token('DT_MODE', ' delete next change');
1801: hr_utility.raise_error;
1802: Else
1803: p_validation_end_date := l_validation_end_date;
1796: -- row
1797: --
1798: If (l_validation_end_date <= l_effective_end_date) then
1799: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1800: hr_utility.set_message_token('DT_MODE', ' delete next change');
1801: hr_utility.raise_error;
1802: Else
1803: p_validation_end_date := l_validation_end_date;
1804: End If;
1797: --
1798: If (l_validation_end_date <= l_effective_end_date) then
1799: hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1800: hr_utility.set_message_token('DT_MODE', ' delete next change');
1801: hr_utility.raise_error;
1802: Else
1803: p_validation_end_date := l_validation_end_date;
1804: End If;
1805: Else
1806: --
1807: -- The current effective end date is alreay the end of time therefore
1808: -- we cannot extend the end date
1809: --
1810: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1811: hr_utility.raise_error;
1812: End If;
1813: --
1814: Hr_Utility.Set_Location(' Leaving:'||l_proc, 25);
1807: -- The current effective end date is alreay the end of time therefore
1808: -- we cannot extend the end date
1809: --
1810: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1811: hr_utility.raise_error;
1812: End If;
1813: --
1814: Hr_Utility.Set_Location(' Leaving:'||l_proc, 25);
1815: --
1810: hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1811: hr_utility.raise_error;
1812: End If;
1813: --
1814: Hr_Utility.Set_Location(' Leaving:'||l_proc, 25);
1815: --
1816: End Get_Delete_Next_Change_Dates;
1817: -- ---------------------------------------------------------------------------
1818: -- |-------------------------< validate_dt_mode >----------------------------|
1888: l_proc varchar2(72) := g_package||'Validate_DT_Mode';
1889: l_datetrack_mode varchar2(30);
1890: --
1891: Begin
1892: Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1893: l_datetrack_mode := upper(p_datetrack_mode);
1894: --
1895: Effective_Date_Valid(p_effective_date => p_effective_date);
1896: --
2127: p_validation_start_date => p_validation_start_date,
2128: p_validation_end_date => p_validation_end_date);
2129: --
2130: Else
2131: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
2132: hr_utility.set_message_token('DT_MODE', l_datetrack_mode);
2133: hr_utility.raise_error;
2134: End If;
2135: --
2128: p_validation_end_date => p_validation_end_date);
2129: --
2130: Else
2131: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
2132: hr_utility.set_message_token('DT_MODE', l_datetrack_mode);
2133: hr_utility.raise_error;
2134: End If;
2135: --
2136: Hr_Utility.Set_Location(' Leaving:'||l_proc, 55);
2129: --
2130: Else
2131: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
2132: hr_utility.set_message_token('DT_MODE', l_datetrack_mode);
2133: hr_utility.raise_error;
2134: End If;
2135: --
2136: Hr_Utility.Set_Location(' Leaving:'||l_proc, 55);
2137: --
2132: hr_utility.set_message_token('DT_MODE', l_datetrack_mode);
2133: hr_utility.raise_error;
2134: End If;
2135: --
2136: Hr_Utility.Set_Location(' Leaving:'||l_proc, 55);
2137: --
2138: End Validate_DT_Mode;
2139: -- ----------------------------------------------------------------------------
2140: -- |---------------------------< find_dt_upd_modes >--------------------------|
2150: --
2151: l_proc varchar2(72) := g_package||'find_dt_upd_modes';
2152: --
2153: Begin
2154: hr_utility.set_location('Entering:'||l_proc, 5);
2155: --
2156: -- Call the corresponding datetrack api
2157: --
2158: dt_api.find_dt_upd_modes
2184: if p_update_change_insert then
2185: p_update_change_insert := false;
2186: end if;
2187: --
2188: hr_utility.set_location(' Leaving:'||l_proc, 10);
2189: End find_dt_upd_modes;
2190: --
2191: -- ----------------------------------------------------------------------------
2192: -- |---------------------------< find_dt_del_modes >--------------------------|
2203: l_proc varchar2(72) := g_package||'find_dt_del_modes';
2204: --
2205: --
2206: Begin
2207: hr_utility.set_location('Entering:'||l_proc, 5);
2208: --
2209: -- Call the corresponding datetrack api
2210: --
2211: dt_api.find_dt_del_modes
2218: ,p_future_change => p_future_change
2219: ,p_delete_next_change => p_delete_next_change
2220: );
2221: --
2222: hr_utility.set_location(' Leaving:'||l_proc, 10);
2223: End find_dt_del_modes;
2224: --
2225: -- ----------------------------------------------------------------------------
2226: -- |-----------------------< upd_effective_end_date >-------------------------|
2237: l_proc varchar2(72) := g_package||'upd_effective_end_date';
2238: l_object_version_number number;
2239: --
2240: Begin
2241: hr_utility.set_location('Entering:'||l_proc, 5);
2242: --
2243: -- Because we are updating a row we must get the next object
2244: -- version number.
2245: --
2249: ,p_base_key_column => 'run_type_id'
2250: ,p_base_key_value => p_base_key_value
2251: );
2252: --
2253: hr_utility.set_location(l_proc, 10);
2254: --
2255: -- Update the specified datetrack row setting the effective
2256: -- end date to the specified new effective end date.
2257: --
2262: and p_effective_date
2263: between t.effective_start_date and t.effective_end_date;
2264: --
2265: p_object_version_number := l_object_version_number;
2266: hr_utility.set_location(' Leaving:'||l_proc, 15);
2267: --
2268: End upd_effective_end_date;
2269: --
2270: -- ----------------------------------------------------------------------------
2338: --
2339: --
2340: --
2341: Begin
2342: hr_utility.set_location('Entering:'||l_proc, 5);
2343: --
2344: -- Ensure that all the mandatory arguments are not null
2345: --
2346: hr_api.mandatory_arg_error(p_api_name => l_proc
2441: --
2442: p_validation_start_date := l_validation_start_date;
2443: p_validation_end_date := l_validation_end_date;
2444: --
2445: hr_utility.set_location(' Leaving:'||l_proc, 30);
2446: --
2447: -- We need to trap the ORA LOCK exception
2448: --
2449: Exception