[Home] [Help]
PACKAGE BODY: APPS.PAY_PRT_SHD
Source
1 Package Body pay_prt_shd as
2 /* $Header: pyprtrhi.pkb 115.13 2003/02/28 15:52:21 alogue noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_prt_shd.'; -- Global package name
9 g_dynamic_sql VARCHAR2(2000); -- dynamic SQL text string
10 --
11 -- ----------------------------------------------------------------------------
12 -- |---------------------------< constraint_error >---------------------------|
13 -- ----------------------------------------------------------------------------
14 Procedure constraint_error
15 (p_constraint_name in all_constraints.constraint_name%TYPE
16 ) Is
17 --
18 l_proc varchar2(72) := g_package||'constraint_error';
19 --
20 Begin
21 --
22 If (p_constraint_name = 'PAY_RUN_TYPES_PK') Then
23 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
24 fnd_message.set_token('PROCEDURE', l_proc);
25 fnd_message.set_token('STEP','5');
26 fnd_message.raise_error;
27 ElsIf (p_constraint_name = 'PAY_RUN_TYPES_UK1') Then
28 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
29 fnd_message.set_token('PROCEDURE', l_proc);
30 fnd_message.set_token('STEP','10');
31 fnd_message.raise_error;
32 Else
33 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
36 fnd_message.raise_error;
37 End If;
38 --
39 End constraint_error;
40 --
41 -- ----------------------------------------------------------------------------
42 -- |-----------------------------< api_updating >-----------------------------|
43 -- ----------------------------------------------------------------------------
44 Function api_updating
45 (p_effective_date in date
46 ,p_run_type_id in number
47 ,p_object_version_number in number
48 ) Return Boolean Is
49 --
50 -- Cursor selects the 'current' row from the HR Schema
51 --
52 Cursor C_Sel1 is
53 select
54 run_type_id
55 ,run_type_name
56 ,run_method
57 ,effective_start_date
58 ,effective_end_date
59 ,business_group_id
60 ,legislation_code
61 ,shortname
62 ,srs_flag
63 ,run_information_category
64 ,run_information1
65 ,run_information2
66 ,run_information3
67 ,run_information4
68 ,run_information5
69 ,run_information6
70 ,run_information7
71 ,run_information8
72 ,run_information9
73 ,run_information10
74 ,run_information11
75 ,run_information12
76 ,run_information13
77 ,run_information14
78 ,run_information15
79 ,run_information16
80 ,run_information17
81 ,run_information18
82 ,run_information19
83 ,run_information20
84 ,run_information21
85 ,run_information22
86 ,run_information23
87 ,run_information24
88 ,run_information25
89 ,run_information26
90 ,run_information27
91 ,run_information28
92 ,run_information29
93 ,run_information30
94 ,object_version_number
95 from pay_run_types_f
96 where run_type_id = p_run_type_id
97 and p_effective_date
98 between effective_start_date and effective_end_date;
99 --
100 l_fct_ret boolean;
101 --
102 Begin
103 --
104 If (p_effective_date is null or
105 p_run_type_id is null or
106 p_object_version_number is null) Then
107 --
108 -- One of the primary key arguments is null therefore we must
109 -- set the returning function value to false
110 --
111 l_fct_ret := false;
112 Else
113 If (p_run_type_id =
114 pay_prt_shd.g_old_rec.run_type_id and
115 p_object_version_number =
116 pay_prt_shd.g_old_rec.object_version_number) Then
117 --
118 -- The g_old_rec is current therefore we must
119 -- set the returning function to true
120 --
121 l_fct_ret := true;
122 Else
123 --
124 -- Select the current row
125 --
126 Open C_Sel1;
127 Fetch C_Sel1 Into pay_prt_shd.g_old_rec;
128 If C_Sel1%notfound Then
129 Close C_Sel1;
130 --
131 -- The primary key is invalid therefore we must error
132 --
133 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
134 fnd_message.raise_error;
135 End If;
136 Close C_Sel1;
137 If (p_object_version_number
138 <> pay_prt_shd.g_old_rec.object_version_number) Then
139 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
140 fnd_message.raise_error;
141 End If;
142 l_fct_ret := true;
143 End If;
144 End If;
145 Return (l_fct_ret);
146 --
147 End api_updating;
148 --
149 -- ----------------------------------------------------------------------------
150 -- |-------------------------< Effective_Date_Valid >-------------------------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- PRIVATE - copied from dt_api
154 --
155 -- Description: Procedure ensures that the effective date is not null and
156 -- exists on or after the start of time.
157 --
158 -- ----------------------------------------------------------------------------
159 Procedure Effective_Date_Valid(p_effective_date in date) Is
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;
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,
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;
190 -- ----------------------------------------------------------------------------
191 -- |----------------------< Return_Effective_Dates >--------------------------|
192 -- ----------------------------------------------------------------------------
193 --
194 -- PRIVATE
195 --
196 -- Description: Procedure which returns the effective start and end dates for
197 -- the specified table and primary key as of session date.
198 --
199 -- ----------------------------------------------------------------------------
200 PROCEDURE return_effective_dates
201 (p_effective_date IN DATE,
202 p_base_table_name IN VARCHAR2,
203 p_base_key_column IN VARCHAR2,
204 p_base_key_value IN NUMBER,
205 p_effective_start_date IN OUT NOCOPY DATE,
206 p_effective_end_date IN OUT NOCOPY DATE) IS
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, ' ||
216 ' t.effective_end_date ' ||
217 'from '||p_base_table_name||' t ' ||
218 'where t.'||p_base_key_column||' = :p_base_key_value ' ||
219 'and :p_effective_date ' ||
220 'between t.effective_start_date and t.effective_end_date';
221 -- native dynamic PL/SQL call
222 EXECUTE IMMEDIATE pay_prt_shd.g_dynamic_sql
223 INTO p_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');
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;
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));
247 hr_utility.raise_error;
248 WHEN OTHERS THEN
249 RAISE;
250 --
251 END return_effective_dates;
252 -- ----------------------------------------------------------------------------
253 -- |------------------------< Return_Max_End_Date >---------------------------|
254 -- ----------------------------------------------------------------------------
255 --
256 -- PRIVATE - copied from dt_api
257 --
258 -- Description: Function returns the maximum effective_end_date for the
259 -- specified table and primary key.
260 -- NOTE: if the maximum end date doesn't exist (i.e. no rows
261 -- exist for the specified table, key values) then we
262 -- return the null value.
263 -- ----------------------------------------------------------------------------
264 FUNCTION return_max_end_date
265 (p_base_table_name IN VARCHAR2,
266 p_base_key_column IN VARCHAR2,
267 p_base_key_value IN NUMBER)
268 RETURN DATE IS
269 --
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);
279 hr_api.mandatory_arg_error(p_api_name => l_proc,
280 p_argument => 'p_base_key_column',
281 p_argument_value => p_base_key_column);
282 hr_api.mandatory_arg_error(p_api_name => l_proc,
283 p_argument => 'p_base_key_value',
284 p_argument_value => p_base_key_value);
285 -- [ end of change 30.14 ]
286 -- Define dynamic sql text with substitution tokens
287 pay_prt_shd.g_dynamic_sql:=
288 'select max(t.effective_end_date) '||
289 'from '||p_base_table_name||' t '||
290 'where t.'||p_base_key_column||' = :p_base_key_value';
291 --
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 -- ----------------------------------------------------------------------------
301 -- |----------------------< Return_Min_Start_Date >---------------------------|
302 -- ----------------------------------------------------------------------------
303 --
304 -- PRIVATE
305 --
306 -- Description: Function returns the minimum effective_start_date for the
307 -- specified table and primary key.
308 --
309 -- ----------------------------------------------------------------------------
310 FUNCTION return_min_start_date
311 (p_base_table_name in varchar2,
312 p_base_key_column in varchar2,
313 p_base_key_value in number)
314 RETURN DATE IS
315 --
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 '||
325 'where t.'||p_base_key_column||' = :p_base_key_value';
326 --
327 EXECUTE IMMEDIATE pay_prt_shd.g_dynamic_sql
328 INTO l_min_date
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 --
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 >---------------------------|
343 -- ----------------------------------------------------------------------------
344 --
345 -- PRIVATE
346 --
347 -- Description: Function returns a boolean value. TRUE will be set if a future
348 -- row exists for the specified table as of effective date else
349 -- FALSE will be returned. A row must exist as of the effective
350 -- date otherwise an error will be returned.
351 --
352 -- ----------------------------------------------------------------------------
353 Function Future_Rows_Exist
354 (p_effective_date in date,
355 p_base_table_name in varchar2,
356 p_base_key_column in varchar2,
357 p_base_key_value in number)
358 Return Boolean Is
359 --
360 l_proc varchar2(72) := g_package||'Future_Rows_Exist';
361 l_boolean boolean := false;
362 l_dummy_esd date; -- Not required
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 --
372 Return_Effective_Dates
373 (p_effective_date => p_effective_date,
374 p_base_table_name => p_base_table_name,
375 p_base_key_column => p_base_key_column,
376 p_base_key_value => p_base_key_value,
377 p_effective_start_date => l_dummy_esd,
378 p_effective_end_date => l_effective_end_date);
379 --
380 -- We must select the maximum effective end date for the datetracked
381 -- rows
382 --
383 l_max_effective_end_date :=
384 Return_Max_End_Date
385 (p_base_table_name => p_base_table_name,
386 p_base_key_column => p_base_key_column,
387 p_base_key_value => p_base_key_value);
388 --
392 If (l_max_effective_end_date > l_effective_end_date) then
389 -- If the maximum effective end date is greater than the current effective
390 -- end date then future rows exist
391 --
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 -- ----------------------------------------------------------------------------
401 -- |--------------------< Return_Min_Parent_End_Date >------------------------|
402 -- ----------------------------------------------------------------------------
403 --
404 -- PRIVATE
405 --
406 -- Description: Function returns the minimum validation end date for all the
407 -- specified parental entitites.
408 --
409 -- ----------------------------------------------------------------------------
410 Function Return_Min_Parent_End_Date
411 (p_effective_date in date,
412 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
413 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
414 p_parent_key_value1 in number default hr_api.g_number)
415 /*
416 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
417 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
418 p_parent_key_value2 in number default hr_api.g_number,
419 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
420 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
421 p_parent_key_value3 in number default hr_api.g_number,
422 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
423 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
424 p_parent_key_value4 in number default hr_api.g_number,
425 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
426 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
427 p_parent_key_value5 in number default hr_api.g_number,
428 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
429 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
430 p_parent_key_value6 in number default hr_api.g_number,
431 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
432 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
433 p_parent_key_value7 in number default hr_api.g_number,
434 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
435 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
436 p_parent_key_value8 in number default hr_api.g_number,
437 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
438 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
439 p_parent_key_value9 in number default hr_api.g_number,
440 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
441 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
442 p_parent_key_value10 in number default hr_api.g_number)
443 */
444 Return Date Is
445 --
446 l_proc varchar2(72) := g_package||
447 'Return_Min_Parent_End_Date';
448 --
449 l_min_date date := hr_api.g_eot; -- End Of Time
450 l_counter integer; -- Loop counter
451 l_temp_date date;
452 --
453 l_parent_table_name varchar2(30);
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 -- <<Loop1>>
461 -- For l_counter In 1..1 Loop
462 --
463 -- Set the current working arguments to the corresponding functional
464 -- argument values
465 --
466 -- If (l_counter = 1) then
467 l_parent_table_name := p_parent_table_name1;
468 l_parent_key_column := p_parent_key_column1;
469 l_parent_key_value := p_parent_key_value1;
470 /*
471 ElsIf (l_counter = 2) then
472 l_parent_table_name := p_parent_table_name2;
473 l_parent_key_column := p_parent_key_column2;
474 l_parent_key_value := p_parent_key_value2;
475 ElsIf (l_counter = 3) then
476 l_parent_table_name := p_parent_table_name3;
477 l_parent_key_column := p_parent_key_column3;
478 l_parent_key_value := p_parent_key_value3;
479 ElsIf (l_counter = 4) then
480 l_parent_table_name := p_parent_table_name4;
481 l_parent_key_column := p_parent_key_column4;
482 l_parent_key_value := p_parent_key_value4;
483 ElsIf (l_counter = 5) then
484 l_parent_table_name := p_parent_table_name5;
485 l_parent_key_column := p_parent_key_column5;
486 l_parent_key_value := p_parent_key_value5;
487 ElsIf (l_counter = 6) then
488 l_parent_table_name := p_parent_table_name6;
489 l_parent_key_column := p_parent_key_column6;
490 l_parent_key_value := p_parent_key_value6;
491 ElsIf (l_counter = 7) then
492 l_parent_table_name := p_parent_table_name7;
493 l_parent_key_column := p_parent_key_column7;
494 l_parent_key_value := p_parent_key_value7;
495 ElsIf (l_counter = 8) then
496 l_parent_table_name := p_parent_table_name8;
497 l_parent_key_column := p_parent_key_column8;
501 l_parent_key_column := p_parent_key_column9;
498 l_parent_key_value := p_parent_key_value8;
499 ElsIf (l_counter = 9) then
500 l_parent_table_name := p_parent_table_name9;
502 l_parent_key_value := p_parent_key_value9;
503 Else
504 l_parent_table_name := p_parent_table_name10;
505 l_parent_key_column := p_parent_key_column10;
506 l_parent_key_value := p_parent_key_value10;
507 End If;
508 */
509 --
510 -- Ensure that all the working parental details have been specified
511 --
512 If NOT ((nvl(l_parent_table_name, hr_api.g_varchar2) =
513 hr_api.g_varchar2) or
514 (nvl(l_parent_key_column, hr_api.g_varchar2) =
515 hr_api.g_varchar2) or
516 (nvl(l_parent_key_value, hr_api.g_number) =
517 hr_api.g_number)) then
518 --
519 -- All the parental arguments have been specified therefore we must get
520 -- the maximum effective end date for the given parent.
521 --
522 l_temp_date := Return_Max_End_Date
523 (p_base_table_name => l_parent_table_name,
524 p_base_key_column => l_parent_key_column,
525 p_base_key_value => l_parent_key_value);
526 --
527 -- If the returned l_temp_date is null or the less than the
528 -- effective_date then error because a parental row does NOT exist.
529 --
530 If ( l_temp_date is null or
531 (l_temp_date < p_effective_date)) then
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 --
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
543 -- l_min_date to the minimum of these dates
544 --
545 l_min_date := least(l_min_date, l_temp_date);
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 -- ----------------------------------------------------------------------------
555 -- |--------------------< Return_Min_Parent_Start_Date >----------------------|
556 -- ----------------------------------------------------------------------------
557 --
558 -- PRIVATE
559 --
560 -- Description: Function returns the minimum validation start date for all the
561 -- specified parental entitites.
562 --
563 -- ----------------------------------------------------------------------------
564 Function Return_Min_Parent_Start_Date
565 (p_effective_date in date,
566 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
567 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
568 p_parent_key_value1 in number default hr_api.g_number)
569 /*
570 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
571 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
572 p_parent_key_value2 in number default hr_api.g_number,
573 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
574 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
575 p_parent_key_value3 in number default hr_api.g_number,
576 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
577 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
578 p_parent_key_value4 in number default hr_api.g_number,
579 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
580 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
581 p_parent_key_value5 in number default hr_api.g_number,
582 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
583 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
584 p_parent_key_value6 in number default hr_api.g_number,
585 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
586 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
587 p_parent_key_value7 in number default hr_api.g_number,
588 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
589 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
590 p_parent_key_value8 in number default hr_api.g_number,
591 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
592 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
593 p_parent_key_value9 in number default hr_api.g_number,
594 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
595 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
596 p_parent_key_value10 in number default hr_api.g_number)
597 */
598 Return Date Is
599 --
600 l_proc varchar2(72) := g_package||
601 'Return_Min_Parent_Start_Date';
602 --
603 l_min_date date := hr_api.g_sot; -- Start Of Time
607 l_parent_table_name varchar2(30);
604 l_counter integer; -- Loop counter
605 l_temp_date date;
606 --
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 <<Loop1>>
616 For l_counter In 1..10 Loop
617 --
618 -- Set the current working arguments to the corresponding functional
619 -- argument values
620 --
621 If (l_counter = 1) then
622 */
623 l_parent_table_name := p_parent_table_name1;
624 l_parent_key_column := p_parent_key_column1;
625 l_parent_key_value := p_parent_key_value1;
626 /*
627 ElsIf (l_counter = 2) then
628 l_parent_table_name := p_parent_table_name2;
629 l_parent_key_column := p_parent_key_column2;
630 l_parent_key_value := p_parent_key_value2;
631 ElsIf (l_counter = 3) then
632 l_parent_table_name := p_parent_table_name3;
633 l_parent_key_column := p_parent_key_column3;
634 l_parent_key_value := p_parent_key_value3;
635 ElsIf (l_counter = 4) then
636 l_parent_table_name := p_parent_table_name4;
637 l_parent_key_column := p_parent_key_column4;
638 l_parent_key_value := p_parent_key_value4;
639 ElsIf (l_counter = 5) then
640 l_parent_table_name := p_parent_table_name5;
641 l_parent_key_column := p_parent_key_column5;
642 l_parent_key_value := p_parent_key_value5;
643 ElsIf (l_counter = 6) then
644 l_parent_table_name := p_parent_table_name6;
645 l_parent_key_column := p_parent_key_column6;
646 l_parent_key_value := p_parent_key_value6;
647 ElsIf (l_counter = 7) then
648 l_parent_table_name := p_parent_table_name7;
649 l_parent_key_column := p_parent_key_column7;
650 l_parent_key_value := p_parent_key_value7;
651 ElsIf (l_counter = 8) then
652 l_parent_table_name := p_parent_table_name8;
653 l_parent_key_column := p_parent_key_column8;
654 l_parent_key_value := p_parent_key_value8;
655 ElsIf (l_counter = 9) then
656 l_parent_table_name := p_parent_table_name9;
657 l_parent_key_column := p_parent_key_column9;
658 l_parent_key_value := p_parent_key_value9;
659 Else
660 l_parent_table_name := p_parent_table_name10;
661 l_parent_key_column := p_parent_key_column10;
662 l_parent_key_value := p_parent_key_value10;
663 End If;
664 */
665 --
666 -- Ensure that all the working parental details have been specified
667 --
668 If NOT ((nvl(l_parent_table_name, hr_api.g_varchar2) =
669 hr_api.g_varchar2) or
670 (nvl(l_parent_key_column, hr_api.g_varchar2) =
671 hr_api.g_varchar2) or
672 (nvl(l_parent_key_value, hr_api.g_number) =
673 hr_api.g_number)) then
674 --
675 -- All the parental arguments have been specified therefore we must get
676 -- the minimum effective start date for the given parent.
677 --
678 l_temp_date := Return_Min_Start_Date
679 (p_base_table_name => l_parent_table_name,
680 p_base_key_column => l_parent_key_column,
681 p_base_key_value => l_parent_key_value);
682 --
683 -- If the returned l_temp_date is null or greater than the
684 -- effective_date then error because a parental row does NOT exist.
685 --
686 If ( l_temp_date is null or
687 (l_temp_date > p_effective_date)) then
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 --
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
699 -- l_min_date to the maximum of these dates
700 --
701 l_min_date := greatest(l_min_date, l_temp_date);
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 -- ----------------------------------------------------------------------------
711 -- |-----------------------< Lck_Future_Rows >--------------------------------|
712 -- ----------------------------------------------------------------------------
713 --
714 -- PRIVATE - copied from dt_api
715 --
716 -- Description: Locks the entity from the effective_date to the end-of-time.
717 -- No processing will be completed if the p_key_value is
718 -- null because the column could be defined as nullable.
719 -- If no rows where locked for the given values then the procedure
720 -- will error because at least 1 row must be locked.
721 --
722 -- ----------------------------------------------------------------------------
723 PROCEDURE lck_future_rows
724 (p_effective_date IN DATE,
725 p_table_name IN VARCHAR2,
726 p_key_column IN VARCHAR2,
727 p_key_value IN NUMBER) IS
728 --
732 l_csr l_csr_type;
729 l_proc VARCHAR2(72) := g_package||'lck_future_rows';
730 l_dummy_num NUMBER;
731 TYPE l_csr_type IS REF CURSOR; -- define weak REF CURSOR 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 --
740 -- [ start of change 30.14 ]
741 hr_api.mandatory_arg_error(p_api_name => l_proc,
742 p_argument => 'p_effective_date',
743 p_argument_value => p_effective_date);
744 hr_api.mandatory_arg_error(p_api_name => l_proc,
745 p_argument => 'p_table_name',
746 p_argument_value => p_table_name);
747 hr_api.mandatory_arg_error(p_api_name => l_proc,
748 p_argument => 'p_key_column',
749 p_argument_value => p_key_column);
750 -- [ end of change 30.14 ]
751 -- If the p_key_value is null then we must not
752 -- process the sql as it could be a nullable column.
753 IF (p_key_value IS NOT NULL) THEN
754 -- Define dynamic sql text with substitution tokens
755 pay_prt_shd.g_dynamic_sql :=
756 'select 1 ' ||
757 'from '||p_table_name||' t1 ' ||
758 'where t1.'||p_key_column||' = :p_key_value ' ||
759 'and t1.effective_end_date >= :p_effective_date ' ||
760 'order by t1.effective_start_date ' ||
761 'for update nowait';
762 --
763 OPEN l_csr FOR pay_prt_shd.g_dynamic_sql USING p_key_value, p_effective_date
764 ;
765 LOOP
766 FETCH l_csr INTO l_dummy_num;
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
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');
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;
790 -- ----------------------------------------------------------------------------
791 -- |----------------------------< Lck_Parent >--------------------------------|
792 -- ----------------------------------------------------------------------------
793 --
794 -- PUBLIC
795 --
796 -- Description: Locks the specified parental entity from the effective_date
797 -- to the end-of-time by calling the Lck_Future_Rows procedure.
798 --
799 -- ----------------------------------------------------------------------------
800 Procedure Lck_Parent
801 (p_effective_date in date,
802 p_parent_table_name in varchar2,
803 p_parent_key_column in varchar2,
804 p_parent_key_value in number) Is
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,
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 -- ----------------------------------------------------------------------------
822 --
823 -- PRIVATE - copied from dt_api
824 --
825 -- Description: Locks the specified child entity maximum row for the specified
826 -- parent key value:
827 --
828 -- E.g. ('X' denotes locked rows)
829 --
830 -- |---------------------------------------| Parent Entity
831 -- |---------|XXXXXXXXXX| Child DT Rows 1
832 -- |-------------|XXXXXXXXXXXXXXX| Child DT Rows 2
833 -- |---|-----|XXXXXXXXXXXXX| Child DT Rows 3
834 --
835 -- After locking the maximum row, we must ensure that the
836 -- effective end date of the locked row cannot exceed
837 -- the validation start date.
838 --
839 -- No processing will be completed if the p_parent_key_value is
840 -- null because the column could be defined as nullable.
841 --
842 -- ----------------------------------------------------------------------------
843 PROCEDURE lck_child
844 (p_child_table_name IN VARCHAR2,
845 p_child_key_column IN VARCHAR2,
846 p_parent_key_column IN VARCHAR2,
847 p_parent_key_value IN NUMBER,
848 p_child_fk_column IN VARCHAR2,
852 --
849 p_validation_start_date IN DATE) IS
850 --
851 l_proc VARCHAR2(72) := g_package||'lck_child';
853 l_lck_date DATE;
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 --
863 hr_api.mandatory_arg_error(p_api_name => l_proc,
864 p_argument => 'p_child_table_name',
865 p_argument_value => p_child_table_name);
866 hr_api.mandatory_arg_error(p_api_name => l_proc,
867 p_argument => 'p_child_key_column',
868 p_argument_value => p_child_key_column);
869 hr_api.mandatory_arg_error(p_api_name => l_proc,
870 p_argument => 'p_parent_key_column',
871 p_argument_value => p_parent_key_column);
872 hr_api.mandatory_arg_error(p_api_name => l_proc,
873 p_argument => 'p_validation_start_date',
874 p_argument_value => p_validation_start_date);
875 --
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 ' ||
885 'where (t1.'||p_child_key_column||', ' ||
886 ' t1.effective_start_date, ' ||
887 ' t1.effective_end_date) in ' ||
888 ' (select t2.'||p_child_key_column||', ' ||
889 ' max(t2.effective_start_date), ' ||
890 ' max(t2.effective_end_date) ' ||
891 ' from '||p_child_table_name||' t2 ' ||
892 ' where t2.'||p_child_fk_column||' = :p_parent_key_value ' ||
893 ' group by t2.'||p_child_key_column||')' ||
894 'order by t1.'||p_child_key_column||' ' ||
895 'for update nowait';
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);
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
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 --
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
933 IF l_cursor%ISOPEN THEN
934 CLOSE l_cursor;
935 END IF;
936 RAISE;
937 END lck_child;
938 -- ----------------------------------------------------------------------------
939 -- |-------------------------< Get_Insert_Dates >-----------------------------|
940 -- ----------------------------------------------------------------------------
941 --
942 -- PRIVATE - copied from dt_api
943 --
944 -- Description: Locks and parental entity rows (if supplied) and Returns
945 -- the validation start and end dates for the DateTrack
946 -- INSERT mode
947 --
948 -- ----------------------------------------------------------------------------
949 Procedure Get_Insert_Dates
950 (p_effective_date in date,
951 p_base_table_name in varchar2,
952 p_base_key_column in varchar2,
953 p_base_key_value in number,
954 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
955 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
956 p_parent_key_value1 in number default hr_api.g_number,
957 /*
958 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
959 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
960 p_parent_key_value2 in number default hr_api.g_number,
961 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
965 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
962 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
963 p_parent_key_value3 in number default hr_api.g_number,
964 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
966 p_parent_key_value4 in number default hr_api.g_number,
967 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
968 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
969 p_parent_key_value5 in number default hr_api.g_number,
970 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
971 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
972 p_parent_key_value6 in number default hr_api.g_number,
973 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
974 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
975 p_parent_key_value7 in number default hr_api.g_number,
976 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
977 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
978 p_parent_key_value8 in number default hr_api.g_number,
979 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
980 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
981 p_parent_key_value9 in number default hr_api.g_number,
982 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
983 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
984 p_parent_key_value10 in number default hr_api.g_number,
985 */
986 p_enforce_foreign_locking in boolean,
987 p_validation_start_date out nocopy date,
988 p_validation_end_date out nocopy date) Is
989 --
990 l_proc varchar2(72) := g_package||'Get_Insert_Dates';
991 l_parent_table_name varchar2(30);
992 l_parent_key_column varchar2(30);
993 l_parent_key_value number;
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 --
1003 If p_enforce_foreign_locking Then
1004 /* For l_counter In 1..10 Loop
1005 --
1006 -- Set the current working arguments to the corresponding functional
1007 -- argument values
1008 --
1009 If (l_counter = 1) then
1010 */
1011 l_parent_table_name := p_parent_table_name1;
1012 l_parent_key_column := p_parent_key_column1;
1013 l_parent_key_value := p_parent_key_value1;
1014 /*
1015 ElsIf (l_counter = 2) then
1016 l_parent_table_name := p_parent_table_name2;
1017 l_parent_key_column := p_parent_key_column2;
1018 l_parent_key_value := p_parent_key_value2;
1019 ElsIf (l_counter = 3) then
1020 l_parent_table_name := p_parent_table_name3;
1021 l_parent_key_column := p_parent_key_column3;
1022 l_parent_key_value := p_parent_key_value3;
1023 ElsIf (l_counter = 4) then
1024 l_parent_table_name := p_parent_table_name4;
1025 l_parent_key_column := p_parent_key_column4;
1026 l_parent_key_value := p_parent_key_value4;
1027 ElsIf (l_counter = 5) then
1028 l_parent_table_name := p_parent_table_name5;
1029 l_parent_key_column := p_parent_key_column5;
1030 l_parent_key_value := p_parent_key_value5;
1031 ElsIf (l_counter = 6) then
1032 l_parent_table_name := p_parent_table_name6;
1033 l_parent_key_column := p_parent_key_column6;
1034 l_parent_key_value := p_parent_key_value6;
1035 ElsIf (l_counter = 7) then
1036 l_parent_table_name := p_parent_table_name7;
1037 l_parent_key_column := p_parent_key_column7;
1038 l_parent_key_value := p_parent_key_value7;
1039 ElsIf (l_counter = 8) then
1040 l_parent_table_name := p_parent_table_name8;
1041 l_parent_key_column := p_parent_key_column8;
1042 l_parent_key_value := p_parent_key_value8;
1043 ElsIf (l_counter = 9) then
1044 l_parent_table_name := p_parent_table_name9;
1045 l_parent_key_column := p_parent_key_column9;
1046 l_parent_key_value := p_parent_key_value9;
1047 Else
1048 l_parent_table_name := p_parent_table_name10;
1049 l_parent_key_column := p_parent_key_column10;
1050 l_parent_key_value := p_parent_key_value10;
1051 End If;
1052 */
1053 --
1054 -- Ensure that all the working parental details have been specified
1055 --
1056 If NOT ((nvl(l_parent_table_name, hr_api.g_varchar2) =
1057 hr_api.g_varchar2) or
1058 (nvl(l_parent_key_column, hr_api.g_varchar2) =
1059 hr_api.g_varchar2) or
1060 (nvl(l_parent_key_value, hr_api.g_number) =
1061 hr_api.g_number)) then
1062 --
1063 -- All the parental arguments have been specified therefore we must
1064 -- attempt to lock the specified parent rows.
1065 --
1066 Lck_Parent
1067 (p_effective_date => p_effective_date,
1068 p_parent_table_name => l_parent_table_name,
1069 p_parent_key_column => l_parent_key_column,
1073 End If;
1070 p_parent_key_value => l_parent_key_value);
1071 End If;
1072 -- End Loop;
1074 --
1075 -- Set the validation start date to the effective date and
1076 -- the validation end date to the minimum parental end date
1077 --
1078 -- Validate the effective date
1079 --
1080 l_dummy_date :=
1081 Return_Min_Parent_Start_Date
1082 (p_effective_date => p_effective_date,
1083 p_parent_table_name1 => p_parent_table_name1,
1084 p_parent_key_column1 => p_parent_key_column1,
1085 p_parent_key_value1 => p_parent_key_value1);
1086 /*
1087 p_parent_table_name2 => p_parent_table_name2,
1088 p_parent_key_column2 => p_parent_key_column2,
1089 p_parent_key_value2 => p_parent_key_value2,
1090 p_parent_table_name3 => p_parent_table_name3,
1091 p_parent_key_column3 => p_parent_key_column3,
1092 p_parent_key_value3 => p_parent_key_value3,
1093 p_parent_table_name4 => p_parent_table_name4,
1094 p_parent_key_column4 => p_parent_key_column4,
1095 p_parent_key_value4 => p_parent_key_value4,
1096 p_parent_table_name5 => p_parent_table_name5,
1097 p_parent_key_column5 => p_parent_key_column5,
1098 p_parent_key_value5 => p_parent_key_value5,
1099 p_parent_table_name6 => p_parent_table_name6,
1100 p_parent_key_column6 => p_parent_key_column6,
1101 p_parent_key_value6 => p_parent_key_value6,
1102 p_parent_table_name7 => p_parent_table_name7,
1103 p_parent_key_column7 => p_parent_key_column7,
1104 p_parent_key_value7 => p_parent_key_value7,
1105 p_parent_table_name8 => p_parent_table_name8,
1106 p_parent_key_column8 => p_parent_key_column8,
1107 p_parent_key_value8 => p_parent_key_value8,
1108 p_parent_table_name9 => p_parent_table_name9,
1109 p_parent_key_column9 => p_parent_key_column9,
1110 p_parent_key_value9 => p_parent_key_value9,
1111 p_parent_table_name10 => p_parent_table_name10,
1112 p_parent_key_column10 => p_parent_key_column10,
1113 p_parent_key_value10 => p_parent_key_value10);
1114 */
1115 --
1116 p_validation_start_date := p_effective_date;
1117 p_validation_end_date :=
1118 Return_Min_Parent_End_Date
1119 (p_effective_date => p_effective_date,
1120 p_parent_table_name1 => p_parent_table_name1,
1121 p_parent_key_column1 => p_parent_key_column1,
1122 p_parent_key_value1 => p_parent_key_value1);
1123 /*
1124 p_parent_table_name2 => p_parent_table_name2,
1125 p_parent_key_column2 => p_parent_key_column2,
1126 p_parent_key_value2 => p_parent_key_value2,
1127 p_parent_table_name3 => p_parent_table_name3,
1128 p_parent_key_column3 => p_parent_key_column3,
1129 p_parent_key_value3 => p_parent_key_value3,
1130 p_parent_table_name4 => p_parent_table_name4,
1131 p_parent_key_column4 => p_parent_key_column4,
1132 p_parent_key_value4 => p_parent_key_value4,
1133 p_parent_table_name5 => p_parent_table_name5,
1134 p_parent_key_column5 => p_parent_key_column5,
1135 p_parent_key_value5 => p_parent_key_value5,
1136 p_parent_table_name6 => p_parent_table_name6,
1137 p_parent_key_column6 => p_parent_key_column6,
1138 p_parent_key_value6 => p_parent_key_value6,
1139 p_parent_table_name7 => p_parent_table_name7,
1140 p_parent_key_column7 => p_parent_key_column7,
1141 p_parent_key_value7 => p_parent_key_value7,
1142 p_parent_table_name8 => p_parent_table_name8,
1143 p_parent_key_column8 => p_parent_key_column8,
1144 p_parent_key_value8 => p_parent_key_value8,
1145 p_parent_table_name9 => p_parent_table_name9,
1146 p_parent_key_column9 => p_parent_key_column9,
1147 p_parent_key_value9 => p_parent_key_value9,
1148 p_parent_table_name10 => p_parent_table_name10,
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 >---------------------------|
1158 -- ----------------------------------------------------------------------------
1159 --
1160 -- PRIVATE
1161 --
1162 -- Description: Returns the validation start and end dates for the
1163 -- DateTrack CORRECTION mode.
1164 --
1165 -- ----------------------------------------------------------------------------
1166 Procedure Get_Correction_Dates
1167 (p_effective_date in date,
1168 p_base_table_name in varchar2,
1169 p_base_key_column in varchar2,
1170 p_base_key_value in number,
1171 p_validation_start_date out nocopy date,
1172 p_validation_end_date out nocopy date) Is
1173 --
1174 l_proc varchar2(72) := g_package||'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,
1186 p_effective_end_date => l_effective_end_date);
1183 p_base_key_column => p_base_key_column,
1184 p_base_key_value => p_base_key_value,
1185 p_effective_start_date => l_effective_start_date,
1187 --
1188 -- The CORRECTION mode will always be valid therefore we must just
1189 -- return the validation start and end dates
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 -- ----------------------------------------------------------------------------
1199 --
1200 -- PRIVATE
1201 --
1202 -- Description: Returns the validation start and end dates for the
1203 -- DateTrack UPDATE mode if allowed.
1204 --
1205 -- ----------------------------------------------------------------------------
1206 Procedure Get_Update_Dates
1207 (p_effective_date in date,
1208 p_base_table_name in varchar2,
1209 p_base_key_column in varchar2,
1210 p_base_key_value in number,
1211 p_validation_start_date out nocopy date,
1212 p_validation_end_date out nocopy date) Is
1213 --
1214 l_proc varchar2(72) := g_package||'Get_Update_Dates';
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
1224 (p_effective_date => p_effective_date,
1225 p_base_table_name => p_base_table_name,
1226 p_base_key_column => p_base_key_column,
1227 p_base_key_value => p_base_key_value)) then
1228 --
1229 Return_Effective_Dates
1230 (p_effective_date => p_effective_date,
1231 p_base_table_name => p_base_table_name,
1232 p_base_key_column => p_base_key_column,
1233 p_base_key_value => p_base_key_value,
1234 p_effective_start_date => l_effective_start_date,
1235 p_effective_end_date => l_effective_end_date);
1236 --
1237 -- Providing the current effective start date is not equal to the effective
1238 -- date we must return the the validation start and end dates
1239 --
1240 If (l_effective_start_date <> p_effective_date) then
1241 p_validation_start_date := p_effective_date;
1242 p_validation_end_date := l_effective_end_date;
1243 Else
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');
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 >--------------------------|
1260 -- ----------------------------------------------------------------------------
1261 --
1262 -- PRIVATE
1263 --
1264 -- Description: Returns the validation start and end dates for the
1265 -- DateTrack UPDATE_OVERRIDE mode if allowed.
1266 --
1267 -- ----------------------------------------------------------------------------
1268 Procedure Get_Update_Override_Dates
1269 (p_effective_date in date,
1270 p_base_table_name in varchar2,
1271 p_base_key_column in varchar2,
1272 p_base_key_value in number,
1273 p_validation_start_date out nocopy date,
1274 p_validation_end_date out nocopy date) Is
1275 --
1276 l_proc varchar2(72) := g_package||
1277 '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,
1287 p_base_key_value => p_base_key_value,
1288 p_effective_start_date => l_effective_start_date,
1289 p_effective_end_date => l_effective_end_date);
1290 --
1291 -- If the current effective start date is not the same as the effective date
1292 -- and at least one future row exists then we must return the validation
1293 -- start and end dates
1294 --
1295 If (l_effective_start_date <> p_effective_date) then
1296 --
1297 -- As the current row does not start on the effective date we determine if
1298 -- any future rows exist
1299 --
1300 If (Future_Rows_Exist
1304 p_base_key_value => p_base_key_value)) then
1301 (p_effective_date => p_effective_date,
1302 p_base_table_name => p_base_table_name,
1303 p_base_key_column => p_base_key_column,
1305 p_validation_start_date := p_effective_date;
1306 p_validation_end_date := Return_Max_End_Date
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
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 >-----------------------|
1324 -- ----------------------------------------------------------------------------
1325 --
1326 -- PRIVATE
1327 --
1328 -- Description: Returns the validation start and end dates for the
1329 -- DateTrack UPDATE_CHANGE_INSERT mode if allowed.
1330 --
1331 -- ----------------------------------------------------------------------------
1332 Procedure Get_Update_Change_Insert_Dates
1333 (p_effective_date in date,
1334 p_base_table_name in varchar2,
1335 p_base_key_column in varchar2,
1336 p_base_key_value in number,
1337 p_validation_start_date out nocopy date,
1338 p_validation_end_date out nocopy date) Is
1339 --
1340 l_proc varchar2(72) := g_package||
1341 '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,
1351 p_base_key_value => p_base_key_value,
1352 p_effective_start_date => l_effective_start_date,
1353 p_effective_end_date => l_effective_end_date);
1354 --
1355 -- If the current effective start date is not the same as the effective date
1356 -- and at least one future row exists then we must return the validation
1357 -- start and end dates
1358 --
1359 If (l_effective_start_date <> p_effective_date) then
1360 --
1361 -- As the current row does not start on the effective date we determine if
1362 -- any future rows exist
1363 --
1364 If (Future_Rows_Exist
1365 (p_effective_date => p_effective_date,
1366 p_base_table_name => p_base_table_name,
1367 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
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 >-----------------------------|
1385 -- ----------------------------------------------------------------------------
1386 --
1387 -- PRIVATE
1388 --
1389 -- Description: Returns the validation start and end dates for the
1390 -- DateTrack ZAP mode.
1391 --
1392 -- ----------------------------------------------------------------------------
1393 Procedure Get_Zap_Dates
1394 (p_effective_date in date,
1395 p_base_table_name in varchar2,
1396 p_base_key_column in varchar2,
1397 p_base_key_value in number,
1398 p_validation_start_date out nocopy date,
1399 p_validation_end_date out nocopy date) Is
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);
1409 --
1410 p_validation_end_date := Return_Max_End_Date
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 --
1418 -- ----------------------------------------------------------------------------
1415 Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1416 --
1417 End Get_Zap_Dates;
1419 -- |--------------------------< Get_Delete_Dates >----------------------------|
1420 -- ----------------------------------------------------------------------------
1421 --
1422 -- PRIVATE - copied from dt_api
1423 --
1424 -- Description: Returns the validation start and end dates for the
1425 -- DateTrack DELETE mode if allowed.
1426 --
1427 -- ----------------------------------------------------------------------------
1428 Procedure Get_Delete_Dates
1429 (p_effective_date in date,
1430 p_base_table_name in varchar2,
1431 p_base_key_column in varchar2,
1432 p_base_key_value in number,
1433 p_child_table_name1 in varchar2 default hr_api.g_varchar2,
1434 p_child_key_column1 in varchar2 default hr_api.g_varchar2,
1435 p_child_fk_column1 in varchar2 default hr_api.g_varchar2,
1436 p_child_table_name2 in varchar2 default hr_api.g_varchar2,
1437 p_child_key_column2 in varchar2 default hr_api.g_varchar2,
1438 p_child_fk_column2 in varchar2 default hr_api.g_varchar2,
1439 p_enforce_foreign_locking in boolean default true,
1440 p_validation_start_date out nocopy date,
1441 p_validation_end_date out nocopy date) Is
1442 --
1443 l_proc varchar2(72) := g_package||'Get_Delete_Dates';
1444 l_max_end_date date;
1445 l_counter integer; -- Loop counter
1446 --
1447 l_child_table_name varchar2(30);
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 <<Loop1>>
1456 For l_counter In 1..2 Loop
1457 --
1458 -- Set the current working arguments to the corresponding functional
1459 -- argument values
1460 --
1461 If (l_counter = 1) then
1462 l_child_table_name := p_child_table_name1;
1463 l_child_key_column := p_child_key_column1;
1464 l_child_fk_column := p_child_fk_column1;
1465 ElsIf (l_counter = 2) then
1466 l_child_table_name := p_child_table_name2;
1467 l_child_key_column := p_child_key_column2;
1468 l_child_fk_column := p_child_fk_column2;
1469 End If;
1470 --
1471 -- Ensure that all the working child details have been specified
1472 --
1473 If NOT ((nvl(l_child_table_name, hr_api.g_varchar2) =
1474 hr_api.g_varchar2) or
1475 (nvl(l_child_key_column, hr_api.g_varchar2) =
1476 hr_api.g_varchar2)) then
1477 --
1478 --
1479 -- All the child arguments have been specified therefore we must lock
1480 -- the child rows (if they exist).
1481 --
1482 Lck_Child
1483 (p_child_table_name => l_child_table_name,
1484 p_child_key_column => l_child_key_column,
1485 p_parent_key_column => p_base_key_column,
1486 p_parent_key_value => p_base_key_value,
1487 p_child_fk_column => l_child_fk_column,
1488 p_validation_start_date => (p_effective_date + 1));
1489 End If;
1490 End Loop;
1491 End If;
1492 --
1493 -- We must get the maximum effective end date of all the DT rows for the
1494 -- given key.
1495 --
1496 l_max_end_date := Return_Max_End_Date
1497 (p_base_table_name => p_base_table_name,
1498 p_base_key_column => p_base_key_column,
1499 p_base_key_value => p_base_key_value);
1500 --
1501 -- Providing the maximum effective end date is not the same as the current
1502 -- effective date then we must return the validation start and end dates.
1503 -- However, if you attempt to do a datetrack delete where the session date is
1504 -- the same as your maximum date then we must error.
1505 --
1506 If (p_effective_date <> l_max_end_date) then
1507 p_validation_start_date := p_effective_date + 1;
1508 p_validation_end_date := l_max_end_date;
1509 Else
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 --
1519 End Get_Delete_Dates;
1520 -- ----------------------------------------------------------------------------
1521 -- |-----------------------< Get_Future_Change_Dates >------------------------|
1522 -- ----------------------------------------------------------------------------
1523 --
1524 -- PRIVATE
1525 --
1526 -- Description: Returns the validation start and end dates for the
1527 -- DateTrack FUTURE_CHANGE mode if allowed.
1528 --
1529 -- ----------------------------------------------------------------------------
1530 Procedure Get_Future_Change_Dates
1531 (p_effective_date in date,
1532 p_base_table_name in varchar2,
1533 p_base_key_column in varchar2,
1534 p_base_key_value in number,
1538 /*
1535 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
1536 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
1537 p_parent_key_value1 in number default hr_api.g_number,
1539 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
1540 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
1541 p_parent_key_value2 in number default hr_api.g_number,
1542 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
1543 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
1544 p_parent_key_value3 in number default hr_api.g_number,
1545 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
1546 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
1547 p_parent_key_value4 in number default hr_api.g_number,
1548 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
1549 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
1550 p_parent_key_value5 in number default hr_api.g_number,
1551 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
1552 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
1553 p_parent_key_value6 in number default hr_api.g_number,
1554 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
1555 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
1556 p_parent_key_value7 in number default hr_api.g_number,
1557 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
1558 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
1559 p_parent_key_value8 in number default hr_api.g_number,
1560 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
1561 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
1562 p_parent_key_value9 in number default hr_api.g_number,
1563 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
1564 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
1565 p_parent_key_value10 in number default hr_api.g_number,
1566 */
1567 p_validation_start_date out nocopy date,
1568 p_validation_end_date out nocopy date) Is
1569 --
1570 l_proc varchar2(72) := g_package||'Get_Future_Change_Dates';
1571 l_effective_start_date date; -- Holds current effective start date
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,
1581 p_base_key_value => p_base_key_value,
1582 p_effective_start_date => l_effective_start_date,
1583 p_effective_end_date => l_effective_end_date);
1584 --
1585 -- Providing the current effective end date is not the end of time
1586 -- then we must set the validation dates
1587 --
1588 If (l_effective_end_date <> HR_Api.g_eot) then
1589 --
1590 p_validation_start_date := l_effective_end_date + 1;
1591 l_validation_end_date :=
1592 Return_Min_Parent_End_Date
1593 (p_effective_date => p_effective_date,
1594 p_parent_table_name1 => p_parent_table_name1,
1595 p_parent_key_column1 => p_parent_key_column1,
1596 p_parent_key_value1 => p_parent_key_value1);
1597 /*
1598 p_parent_table_name2 => p_parent_table_name2,
1599 p_parent_key_column2 => p_parent_key_column2,
1600 p_parent_key_value2 => p_parent_key_value2,
1601 p_parent_table_name3 => p_parent_table_name3,
1602 p_parent_key_column3 => p_parent_key_column3,
1603 p_parent_key_value3 => p_parent_key_value3,
1604 p_parent_table_name4 => p_parent_table_name4,
1605 p_parent_key_column4 => p_parent_key_column4,
1606 p_parent_key_value4 => p_parent_key_value4,
1607 p_parent_table_name5 => p_parent_table_name5,
1608 p_parent_key_column5 => p_parent_key_column5,
1609 p_parent_key_value5 => p_parent_key_value5,
1610 p_parent_table_name6 => p_parent_table_name6,
1611 p_parent_key_column6 => p_parent_key_column6,
1612 p_parent_key_value6 => p_parent_key_value6,
1613 p_parent_table_name7 => p_parent_table_name7,
1614 p_parent_key_column7 => p_parent_key_column7,
1615 p_parent_key_value7 => p_parent_key_value7,
1616 p_parent_table_name8 => p_parent_table_name8,
1617 p_parent_key_column8 => p_parent_key_column8,
1618 p_parent_key_value8 => p_parent_key_value8,
1619 p_parent_table_name9 => p_parent_table_name9,
1620 p_parent_key_column9 => p_parent_key_column9,
1621 p_parent_key_value9 => p_parent_key_value9,
1622 p_parent_table_name10 => p_parent_table_name10,
1623 p_parent_key_column10 => p_parent_key_column10,
1624 p_parent_key_value10 => p_parent_key_value10);
1625 */
1626 --
1627 -- If the validation end date is set to the current effective end date
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');
1636 p_validation_end_date := l_validation_end_date;
1633 hr_utility.set_message_token('DT_MODE', ' future changes');
1634 hr_utility.raise_error;
1635 Else
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);
1648 --
1649 End Get_Future_Change_Dates;
1650 -- ----------------------------------------------------------------------------
1651 -- |--------------------< Get_Delete_Next_Change_Dates >----------------------|
1652 -- ----------------------------------------------------------------------------
1653 --
1654 -- PRIVATE
1655 --
1656 -- Description: Returns the validation start and end dates for the
1657 -- DateTrack FUTURE_CHANGE mode if allowed.
1658 --
1659 -- ----------------------------------------------------------------------------
1660 Procedure Get_Delete_Next_Change_Dates
1661 (p_effective_date in date,
1662 p_base_table_name in varchar2,
1663 p_base_key_column in varchar2,
1664 p_base_key_value in number,
1665 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
1666 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
1667 p_parent_key_value1 in number default hr_api.g_number,
1668 /*
1669 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
1670 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
1671 p_parent_key_value2 in number default hr_api.g_number,
1672 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
1673 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
1674 p_parent_key_value3 in number default hr_api.g_number,
1675 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
1676 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
1677 p_parent_key_value4 in number default hr_api.g_number,
1678 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
1679 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
1680 p_parent_key_value5 in number default hr_api.g_number,
1681 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
1682 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
1683 p_parent_key_value6 in number default hr_api.g_number,
1684 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
1685 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
1686 p_parent_key_value7 in number default hr_api.g_number,
1687 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
1688 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
1689 p_parent_key_value8 in number default hr_api.g_number,
1690 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
1691 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
1692 p_parent_key_value9 in number default hr_api.g_number,
1693 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
1694 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
1695 p_parent_key_value10 in number default hr_api.g_number,
1696 */
1697 p_validation_start_date out nocopy date,
1698 p_validation_end_date out nocopy date) Is
1699 --
1700 l_proc varchar2(72) := g_package||'Get_Delete_Next_Change_Dates';
1701 l_effective_start_date date; -- Holds current effective start date
1702 l_effective_end_date date; -- Holds current effective end date
1703 l_validation_start_date date;
1704 l_validation_end_date date;
1705 l_dummy_date date; -- Date not used
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,
1715 p_base_key_value => p_base_key_value,
1716 p_effective_start_date => l_effective_start_date,
1717 p_effective_end_date => l_effective_end_date);
1718 --
1719 -- Providing the current effective end date is not the end of time
1720 -- then we must set the validation dates
1721 --
1722 If (l_effective_end_date <> HR_Api.g_eot) then
1723 --
1724 l_validation_start_date := l_effective_end_date + 1;
1725 p_validation_start_date := l_validation_start_date;
1726 --
1727 -- To determine the validation end date we must take the minimum date
1728 -- from the following three possible dates:
1729 -- 1: Minimum parent entity entity end date
1730 -- 2: If future rows exist then the effective end date of the next row
1731 -- 3: If no future rows exist then the end of time
1732 --
1733 l_min_parent_end_date :=
1734 Return_Min_Parent_End_Date
1735 (p_effective_date => p_effective_date,
1736 p_parent_table_name1 => p_parent_table_name1,
1740 p_parent_table_name2 => p_parent_table_name2,
1737 p_parent_key_column1 => p_parent_key_column1,
1738 p_parent_key_value1 => p_parent_key_value1);
1739 /*
1741 p_parent_key_column2 => p_parent_key_column2,
1742 p_parent_key_value2 => p_parent_key_value2,
1743 p_parent_table_name3 => p_parent_table_name3,
1744 p_parent_key_column3 => p_parent_key_column3,
1745 p_parent_key_value3 => p_parent_key_value3,
1746 p_parent_table_name4 => p_parent_table_name4,
1747 p_parent_key_column4 => p_parent_key_column4,
1748 p_parent_key_value4 => p_parent_key_value4,
1749 p_parent_table_name5 => p_parent_table_name5,
1750 p_parent_key_column5 => p_parent_key_column5,
1751 p_parent_key_value5 => p_parent_key_value5,
1752 p_parent_table_name6 => p_parent_table_name6,
1753 p_parent_key_column6 => p_parent_key_column6,
1754 p_parent_key_value6 => p_parent_key_value6,
1755 p_parent_table_name7 => p_parent_table_name7,
1756 p_parent_key_column7 => p_parent_key_column7,
1757 p_parent_key_value7 => p_parent_key_value7,
1758 p_parent_table_name8 => p_parent_table_name8,
1759 p_parent_key_column8 => p_parent_key_column8,
1760 p_parent_key_value8 => p_parent_key_value8,
1761 p_parent_table_name9 => p_parent_table_name9,
1762 p_parent_key_column9 => p_parent_key_column9,
1763 p_parent_key_value9 => p_parent_key_value9,
1764 p_parent_table_name10 => p_parent_table_name10,
1765 p_parent_key_column10 => p_parent_key_column10,
1766 p_parent_key_value10 => p_parent_key_value10);
1767 */
1768 --
1769 If (Future_Rows_Exist
1770 (p_effective_date => p_effective_date,
1771 p_base_table_name => p_base_table_name,
1772 p_base_key_column => p_base_key_column,
1773 p_base_key_value => p_base_key_value)) then
1774 --
1775 Return_Effective_Dates
1776 (p_effective_date => l_validation_start_date,
1777 p_base_table_name => p_base_table_name,
1778 p_base_key_column => p_base_key_column,
1779 p_base_key_value => p_base_key_value,
1780 p_effective_start_date => l_dummy_date,
1781 p_effective_end_date => l_future_effective_end_date);
1782 --
1783 l_validation_end_date :=
1784 least(l_min_parent_end_date, l_future_effective_end_date);
1785 Else
1786 --
1787 -- We only need to set the validation end date to the parent end date
1788 -- because if no parent end dates have been set then we always return
1789 -- the end of time (even if no parental details are specified)
1790 --
1791 l_validation_end_date := l_min_parent_end_date;
1792 End If;
1793 --
1794 -- If the validation end date is set to the current effective end date
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;
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);
1815 --
1816 End Get_Delete_Next_Change_Dates;
1817 -- ---------------------------------------------------------------------------
1818 -- |-------------------------< validate_dt_mode >----------------------------|
1819 -- ---------------------------------------------------------------------------
1820 --
1821 -- PRIVATE - copied from package dt_api. 20 procedures/functions have been
1822 -- dopied from dt_api, to get around the invalid column error caused by the
1823 -- assumption in dt_api.lck_child, that a child table's key will be the same
1824 -- name as the parent table's foreign key.
1825 --
1826 -- As these 20 functions are copied for use only with pay_run_type_usages_f,
1827 -- some of the code has been changed slightly to use hard coded values.
1828 --
1829 -- Description: Validates and returns the validation start and end dates for
1830 -- the DateTrack mode provided.
1831 -- Locking is also enforced within this procedure.
1832 -- The argument p_enforce_foreign_locking determines if for the
1833 -- correct DT mode (INSERT or DELETE) parental or child
1834 -- foreign key entities should be locked. If this value if set to
1835 -- false this procedure will not perform any foreign lockng
1836 -- and it is expected to be handled by the calling process
1837 -- (this is useful if a different method of locking is required
1838 -- where the row exclusive locking mechanisms is too
1839 -- restrictive).
1840 --
1841 -- Locking Processing:
1842 --
1843 -- 1. Entity range row locking:
1844 -- Mode Lock Comments
1845 -- -------------- ---- ---------------------------------
1849 -- UPDATE_OVERRIDE Y Have to lock future rows
1846 -- INSERT N No rows exists at this point
1847 -- UPDATE N Current row already locked
1848 -- CORRECTION N Current row already locked
1850 -- UPDATE_CHANGE_INSERT N Current row already locked
1851 -- DELETE Y Have to lock future rows
1852 -- FUTURE_CHANGE Y Have to lock future rows
1853 -- DELETE_NEXT_CHANGE Y Have to lock future rows
1854 -- We always lock all future rows
1855 -- too ensure consistency. This
1856 -- means that we may over-lock some
1857 -- future rows unnessarily.
1858 -- ZAP Y Have to lock all rows
1859 --
1860 -- 2. Insert
1861 -- Parental rows are locked provided the argument
1862 -- p_enforce_foreign_locking has been set to TRUE.
1863 --
1864 -- 3. Delete
1865 -- Child rows are locked provided the argument
1866 -- p_enforce_foreign_locking has been set to TRUE.
1867 --
1868 -- ----------------------------------------------------------------------------
1869 Procedure Validate_DT_Mode
1870 (p_datetrack_mode in varchar2,
1871 p_effective_date in date,
1872 p_base_table_name in varchar2,
1873 p_base_key_column in varchar2,
1874 p_base_key_value in number,
1875 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
1876 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
1877 p_parent_key_value1 in number default hr_api.g_number,
1878 p_child_table_name1 in varchar2 default hr_api.g_varchar2,
1879 p_child_key_column1 in varchar2 default hr_api.g_varchar2,
1880 p_child_fk_column1 in varchar2 default hr_api.g_varchar2,
1881 p_child_table_name2 in varchar2 default hr_api.g_varchar2,
1882 p_child_key_column2 in varchar2 default hr_api.g_varchar2,
1883 p_child_fk_column2 in varchar2 default hr_api.g_varchar2,
1884 p_enforce_foreign_locking in boolean default true,
1885 p_validation_start_date out nocopy date,
1886 p_validation_end_date out nocopy date) Is
1887 --
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 --
1897 -- Ensure that all the mandatory arguments are not null
1898 --
1899 hr_api.mandatory_arg_error(p_api_name => l_proc,
1900 p_argument => 'p_datetrack_mode',
1901 p_argument_value => p_datetrack_mode);
1902 hr_api.mandatory_arg_error(p_api_name => l_proc,
1903 p_argument => 'p_base_table_name',
1904 p_argument_value => p_base_table_name);
1905 hr_api.mandatory_arg_error(p_api_name => l_proc,
1906 p_argument => 'p_base_key_column',
1907 p_argument_value => p_base_key_column);
1908 --
1909 -- Determine if any entity range row locking is required
1910 --
1911 If (l_datetrack_mode = hr_api.g_update_override or
1912 l_datetrack_mode = hr_api.g_delete or
1913 l_datetrack_mode = hr_api.g_future_change or
1914 l_datetrack_mode = hr_api.g_delete_next_change) then
1915 --
1916 -- Perform the entity range row locking processing
1917 --
1918 Lck_Future_Rows
1919 (p_effective_date => p_effective_date,
1920 p_table_name => p_base_table_name,
1921 p_key_column => p_base_key_column,
1922 p_key_value => p_base_key_value);
1923 --
1924 ElsIf l_datetrack_mode = hr_api.g_zap then
1925 -- As we are performing a ZAP we must lock all rows from
1926 -- the start of time
1927 Lck_Future_Rows
1928 (p_effective_date => hr_api.g_sot,
1929 p_table_name => p_base_table_name,
1930 p_key_column => p_base_key_column,
1931 p_key_value => p_base_key_value);
1932 --
1933 End If;
1934 --
1935 If (l_datetrack_mode = hr_api.g_insert) then
1936 --
1937 Get_Insert_Dates
1938 (p_effective_date => p_effective_date,
1939 p_base_table_name => p_base_table_name,
1940 p_base_key_column => p_base_key_column,
1941 p_base_key_value => p_base_key_value,
1942 p_parent_table_name1 => p_parent_table_name1,
1943 p_parent_key_column1 => p_parent_key_column1,
1944 p_parent_key_value1 => p_parent_key_value1,
1945 /*
1946 p_parent_table_name2 => p_parent_table_name2,
1947 p_parent_key_column2 => p_parent_key_column2,
1948 p_parent_key_value2 => p_parent_key_value2,
1949 p_parent_table_name3 => p_parent_table_name3,
1950 p_parent_key_column3 => p_parent_key_column3,
1951 p_parent_key_value3 => p_parent_key_value3,
1955 p_parent_table_name5 => p_parent_table_name5,
1952 p_parent_table_name4 => p_parent_table_name4,
1953 p_parent_key_column4 => p_parent_key_column4,
1954 p_parent_key_value4 => p_parent_key_value4,
1956 p_parent_key_column5 => p_parent_key_column5,
1957 p_parent_key_value5 => p_parent_key_value5,
1958 p_parent_table_name6 => p_parent_table_name6,
1959 p_parent_key_column6 => p_parent_key_column6,
1960 p_parent_key_value6 => p_parent_key_value6,
1961 p_parent_table_name7 => p_parent_table_name7,
1962 p_parent_key_column7 => p_parent_key_column7,
1963 p_parent_key_value7 => p_parent_key_value7,
1964 p_parent_table_name8 => p_parent_table_name8,
1965 p_parent_key_column8 => p_parent_key_column8,
1966 p_parent_key_value8 => p_parent_key_value8,
1967 p_parent_table_name9 => p_parent_table_name9,
1968 p_parent_key_column9 => p_parent_key_column9,
1969 p_parent_key_value9 => p_parent_key_value9,
1970 p_parent_table_name10 => p_parent_table_name10,
1971 p_parent_key_column10 => p_parent_key_column10,
1972 p_parent_key_value10 => p_parent_key_value10,
1973 */
1974 p_enforce_foreign_locking => p_enforce_foreign_locking,
1975 p_validation_start_date => p_validation_start_date,
1976 p_validation_end_date => p_validation_end_date);
1977 --
1978 ElsIf (l_datetrack_mode = hr_api.g_correction) then
1979 --
1980 Get_Correction_Dates
1981 (p_effective_date => p_effective_date,
1982 p_base_table_name => p_base_table_name,
1983 p_base_key_column => p_base_key_column,
1984 p_base_key_value => p_base_key_value,
1985 p_validation_start_date => p_validation_start_date,
1986 p_validation_end_date => p_validation_end_date);
1987 --
1988 ElsIf (l_datetrack_mode = hr_api.g_update) then
1989 --
1990 Get_Update_Dates
1991 (p_effective_date => p_effective_date,
1992 p_base_table_name => p_base_table_name,
1993 p_base_key_column => p_base_key_column,
1994 p_base_key_value => p_base_key_value,
1995 p_validation_start_date => p_validation_start_date,
1996 p_validation_end_date => p_validation_end_date);
1997 --
1998 ElsIf (l_datetrack_mode = hr_api.g_update_override) then
1999 --
2000 Get_Update_Override_Dates
2001 (p_effective_date => p_effective_date,
2002 p_base_table_name => p_base_table_name,
2003 p_base_key_column => p_base_key_column,
2004 p_base_key_value => p_base_key_value,
2005 p_validation_start_date => p_validation_start_date,
2006 p_validation_end_date => p_validation_end_date);
2007 --
2008 ElsIf (l_datetrack_mode = hr_api.g_update_change_insert) then
2009 --
2010 Get_Update_Change_Insert_Dates
2011 (p_effective_date => p_effective_date,
2012 p_base_table_name => p_base_table_name,
2013 p_base_key_column => p_base_key_column,
2014 p_base_key_value => p_base_key_value,
2015 p_validation_start_date => p_validation_start_date,
2016 p_validation_end_date => p_validation_end_date);
2017 --
2018 ElsIf (l_datetrack_mode = hr_api.g_zap) then
2019 --
2020 Get_Zap_Dates
2021 (p_effective_date => p_effective_date,
2022 p_base_table_name => p_base_table_name,
2023 p_base_key_column => p_base_key_column,
2024 p_base_key_value => p_base_key_value,
2025 p_validation_start_date => p_validation_start_date,
2026 p_validation_end_date => p_validation_end_date);
2027 --
2028 ElsIf (l_datetrack_mode = hr_api.g_delete) then
2029 -- if (l_datetrack_mode = hr_api.g_delete) then
2030 --
2031 Get_Delete_Dates
2032 (p_effective_date => p_effective_date,
2033 p_base_table_name => p_base_table_name,
2034 p_base_key_column => p_base_key_column,
2035 p_base_key_value => p_base_key_value,
2036 p_child_table_name1 => p_child_table_name1,
2037 p_child_key_column1 => p_child_key_column1,
2038 p_child_fk_column1 => p_child_fk_column1,
2039 p_child_table_name2 => p_child_table_name2,
2040 p_child_key_column2 => p_child_key_column2,
2041 p_child_fk_column2 => p_child_fk_column2,
2042 p_enforce_foreign_locking => p_enforce_foreign_locking,
2043 p_validation_start_date => p_validation_start_date,
2044 p_validation_end_date => p_validation_end_date);
2045 --
2046 ElsIf (l_datetrack_mode = hr_api.g_future_change) then
2047 --
2048 Get_Future_Change_Dates
2049 (p_effective_date => p_effective_date,
2050 p_base_table_name => p_base_table_name,
2051 p_base_key_column => p_base_key_column,
2052 p_base_key_value => p_base_key_value,
2053 p_parent_table_name1 => p_parent_table_name1,
2054 p_parent_key_column1 => p_parent_key_column1,
2055 p_parent_key_value1 => p_parent_key_value1,
2056 /*
2057 p_parent_table_name2 => p_parent_table_name2,
2058 p_parent_key_column2 => p_parent_key_column2,
2059 p_parent_key_value2 => p_parent_key_value2,
2063 p_parent_table_name4 => p_parent_table_name4,
2060 p_parent_table_name3 => p_parent_table_name3,
2061 p_parent_key_column3 => p_parent_key_column3,
2062 p_parent_key_value3 => p_parent_key_value3,
2064 p_parent_key_column4 => p_parent_key_column4,
2065 p_parent_key_value4 => p_parent_key_value4,
2066 p_parent_table_name5 => p_parent_table_name5,
2067 p_parent_key_column5 => p_parent_key_column5,
2068 p_parent_key_value5 => p_parent_key_value5,
2069 p_parent_table_name6 => p_parent_table_name6,
2070 p_parent_key_column6 => p_parent_key_column6,
2071 p_parent_key_value6 => p_parent_key_value6,
2072 p_parent_table_name7 => p_parent_table_name7,
2073 p_parent_key_column7 => p_parent_key_column7,
2074 p_parent_key_value7 => p_parent_key_value7,
2075 p_parent_table_name8 => p_parent_table_name8,
2076 p_parent_key_column8 => p_parent_key_column8,
2077 p_parent_key_value8 => p_parent_key_value8,
2078 p_parent_table_name9 => p_parent_table_name9,
2079 p_parent_key_column9 => p_parent_key_column9,
2080 p_parent_key_value9 => p_parent_key_value9,
2081 p_parent_table_name10 => p_parent_table_name10,
2082 p_parent_key_column10 => p_parent_key_column10,
2083 p_parent_key_value10 => p_parent_key_value10,
2084 */
2085 p_validation_start_date => p_validation_start_date,
2086 p_validation_end_date => p_validation_end_date);
2087 --
2088 ElsIf (l_datetrack_mode = hr_api.g_delete_next_change) then
2089 --
2090 Get_Delete_Next_Change_Dates
2091 (p_effective_date => p_effective_date,
2092 p_base_table_name => p_base_table_name,
2093 p_base_key_column => p_base_key_column,
2094 p_base_key_value => p_base_key_value,
2095 p_parent_table_name1 => p_parent_table_name1,
2096 p_parent_key_column1 => p_parent_key_column1,
2097 p_parent_key_value1 => p_parent_key_value1,
2098 /*
2099 p_parent_table_name2 => p_parent_table_name2,
2100 p_parent_key_column2 => p_parent_key_column2,
2101 p_parent_key_value2 => p_parent_key_value2,
2102 p_parent_table_name3 => p_parent_table_name3,
2103 p_parent_key_column3 => p_parent_key_column3,
2104 p_parent_key_value3 => p_parent_key_value3,
2105 p_parent_table_name4 => p_parent_table_name4,
2106 p_parent_key_column4 => p_parent_key_column4,
2107 p_parent_key_value4 => p_parent_key_value4,
2108 p_parent_table_name5 => p_parent_table_name5,
2109 p_parent_key_column5 => p_parent_key_column5,
2110 p_parent_key_value5 => p_parent_key_value5,
2111 p_parent_table_name6 => p_parent_table_name6,
2112 p_parent_key_column6 => p_parent_key_column6,
2113 p_parent_key_value6 => p_parent_key_value6,
2114 p_parent_table_name7 => p_parent_table_name7,
2115 p_parent_key_column7 => p_parent_key_column7,
2116 p_parent_key_value7 => p_parent_key_value7,
2117 p_parent_table_name8 => p_parent_table_name8,
2118 p_parent_key_column8 => p_parent_key_column8,
2119 p_parent_key_value8 => p_parent_key_value8,
2120 p_parent_table_name9 => p_parent_table_name9,
2121 p_parent_key_column9 => p_parent_key_column9,
2122 p_parent_key_value9 => p_parent_key_value9,
2123 p_parent_table_name10 => p_parent_table_name10,
2124 p_parent_key_column10 => p_parent_key_column10,
2125 p_parent_key_value10 => p_parent_key_value10,
2126 */
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 --
2136 Hr_Utility.Set_Location(' Leaving:'||l_proc, 55);
2137 --
2138 End Validate_DT_Mode;
2139 -- ----------------------------------------------------------------------------
2140 -- |---------------------------< find_dt_upd_modes >--------------------------|
2141 -- ----------------------------------------------------------------------------
2142 Procedure find_dt_upd_modes
2143 (p_effective_date in date
2144 ,p_base_key_value in number
2145 ,p_correction out nocopy boolean
2146 ,p_update out nocopy boolean
2147 ,p_update_override out nocopy boolean
2148 ,p_update_change_insert out nocopy boolean
2149 ) is
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
2159 (p_effective_date => p_effective_date
2160 ,p_base_table_name => 'pay_run_types_f'
2161 ,p_base_key_column => 'run_type_id'
2162 ,p_base_key_value => p_base_key_value
2163 ,p_correction => p_correction
2164 ,p_update => p_update
2165 ,p_update_override => p_update_override
2166 ,p_update_change_insert => p_update_change_insert
2167 );
2168 --
2172 -- p_correction are returned as 'true' then hardcode them to false.
2169 -- As run_type_name and shortname are the only updatable columns in the table,
2170 -- and these are limited to update mode of 'CORRECTION', following the call
2171 -- to dt_api.find_dt_upd_modes, if any of the parameters other than
2173 -- NOTE: if columns are added in the future that need to have other update
2174 -- modes, then this will need to be changed.
2175 -- RET 12-DEC-2001 Note: run_type_name is now non-updatable, due to it being
2176 -- the key used in lct for uploading and downloading run type data.
2177 --
2178 if p_update then
2179 p_update := false;
2180 end if;
2181 if p_update_override then
2182 p_update_override := false;
2183 end if;
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 >--------------------------|
2193 -- ----------------------------------------------------------------------------
2194 Procedure find_dt_del_modes
2195 (p_effective_date in date
2196 ,p_base_key_value in number
2197 ,p_zap out nocopy boolean
2198 ,p_delete out nocopy boolean
2199 ,p_future_change out nocopy boolean
2200 ,p_delete_next_change out nocopy boolean
2201 ) is
2202 --
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
2212 (p_effective_date => p_effective_date
2213 ,p_base_table_name => 'pay_run_types_f'
2214 ,p_base_key_column => 'run_type_id'
2215 ,p_base_key_value => p_base_key_value
2216 ,p_zap => p_zap
2217 ,p_delete => p_delete
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 >-------------------------|
2227 -- ----------------------------------------------------------------------------
2228 Procedure upd_effective_end_date
2229 (p_effective_date in date
2230 ,p_base_key_value in number
2231 ,p_new_effective_end_date in date
2232 ,p_validation_start_date in date
2233 ,p_validation_end_date in date
2234 ,p_object_version_number out nocopy number
2235 ) is
2236 --
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 --
2246 l_object_version_number :=
2247 dt_api.get_object_version_number
2248 (p_base_table_name => 'pay_run_types_f'
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 --
2258 update pay_run_types_f t
2259 set t.effective_end_date = p_new_effective_end_date
2260 , t.object_version_number = l_object_version_number
2261 where t.run_type_id = p_base_key_value
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 -- ----------------------------------------------------------------------------
2271 -- |---------------------------------< lck >----------------------------------|
2272 -- ----------------------------------------------------------------------------
2273 Procedure lck
2274 (p_effective_date in date
2275 ,p_datetrack_mode in varchar2
2276 ,p_run_type_id in number
2277 ,p_object_version_number in number
2278 ,p_validation_start_date out nocopy date
2279 ,p_validation_end_date out nocopy date
2280 ) is
2281 --
2282 l_proc varchar2(72) := g_package||'lck';
2283 l_validation_start_date date;
2284 l_validation_end_date date;
2285 l_argument varchar2(30);
2286 --
2287 -- Cursor C_Sel1 selects the current locked row as of session date
2288 -- ensuring that the object version numbers match.
2289 --
2290 Cursor C_Sel1 is
2291 select
2292 run_type_id
2293 ,run_type_name
2294 ,run_method
2295 ,effective_start_date
2296 ,effective_end_date
2297 ,business_group_id
2298 ,legislation_code
2299 ,shortname
2303 ,run_information2
2300 ,srs_flag
2301 ,run_information_category
2302 ,run_information1
2304 ,run_information3
2305 ,run_information4
2306 ,run_information5
2307 ,run_information6
2308 ,run_information7
2309 ,run_information8
2310 ,run_information9
2311 ,run_information10
2312 ,run_information11
2313 ,run_information12
2314 ,run_information13
2315 ,run_information14
2316 ,run_information15
2317 ,run_information16
2318 ,run_information17
2319 ,run_information18
2320 ,run_information19
2321 ,run_information20
2322 ,run_information21
2323 ,run_information22
2324 ,run_information23
2325 ,run_information24
2326 ,run_information25
2327 ,run_information26
2328 ,run_information27
2329 ,run_information28
2330 ,run_information29
2331 ,run_information30
2332 ,object_version_number
2333 from pay_run_types_f
2334 where run_type_id = p_run_type_id
2335 and p_effective_date
2336 between effective_start_date and effective_end_date
2337 for update nowait;
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
2347 ,p_argument => 'effective_date'
2348 ,p_argument_value => p_effective_date
2349 );
2350 --
2351 hr_api.mandatory_arg_error(p_api_name => l_proc
2352 ,p_argument => 'datetrack_mode'
2353 ,p_argument_value => p_datetrack_mode
2354 );
2355 --
2356 hr_api.mandatory_arg_error(p_api_name => l_proc
2357 ,p_argument => 'run_type_id'
2358 ,p_argument_value => p_run_type_id
2359 );
2360 --
2361 hr_api.mandatory_arg_error(p_api_name => l_proc
2362 ,p_argument => 'object_version_number'
2363 ,p_argument_value => p_object_version_number
2364 );
2365 --
2366 -- Check to ensure the datetrack mode is not INSERT.
2367 --
2368 If (p_datetrack_mode <> hr_api.g_insert) then
2369 --
2370 -- We must select and lock the current row.
2371 --
2372 Open C_Sel1;
2373 Fetch C_Sel1 Into pay_prt_shd.g_old_rec;
2374 If C_Sel1%notfound then
2375 Close C_Sel1;
2376 --
2377 -- The primary key is invalid therefore we must error
2378 --
2379 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
2380 fnd_message.raise_error;
2381 End If;
2382 Close C_Sel1;
2383 If (p_object_version_number
2384 <> pay_prt_shd.g_old_rec.object_version_number) Then
2385 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
2386 fnd_message.raise_error;
2387 End If;
2388 --
2389 --
2390 -- Validate the datetrack mode mode getting the validation start
2391 -- and end dates for the specified datetrack operation.
2392 --
2393 dt_api.validate_dt_mode
2394 (p_effective_date => p_effective_date
2395 ,p_datetrack_mode => p_datetrack_mode
2396 ,p_base_table_name => 'pay_run_types_f'
2397 ,p_base_key_column => 'run_type_id'
2398 ,p_base_key_value => p_run_type_id
2399 ,p_child_table_name1 => 'pay_element_type_usages_f'
2400 ,p_child_key_column1 => 'element_type_usage_id'
2401 ,p_child_table_name2 => 'pay_run_type_org_methods_f'
2402 ,p_child_key_column2 => 'run_type_org_method_id'
2403 ,p_enforce_foreign_locking => true
2404 ,p_validation_start_date => l_validation_start_date
2405 ,p_validation_end_date => l_validation_end_date
2406 );
2407 --
2408 -- now call local validate_dt_mode for pay_run_type_usages_f
2409 -- Note: dt_api.validate_dt_mode cannot be used as it assumes that the
2410 -- child table key column has the same name as the parent table fk.
2411 --
2412 validate_dt_mode
2413 (p_effective_date => p_effective_date
2414 ,p_datetrack_mode => p_datetrack_mode
2415 ,p_base_table_name => 'pay_run_types_f'
2416 ,p_base_key_column => 'run_type_id'
2417 ,p_base_key_value => p_run_type_id
2418 ,p_child_table_name1 => 'pay_run_type_usages_f'
2419 ,p_child_key_column1 => 'run_type_usage_id'
2420 ,p_child_fk_column1 => 'parent_run_type_id'
2421 ,p_child_table_name2 => 'pay_run_type_usages_f'
2422 ,p_child_key_column2 => 'run_type_usage_id'
2423 ,p_child_fk_column2 => 'child_run_type_id'
2424 ,p_enforce_foreign_locking => true
2425 ,p_validation_start_date => l_validation_start_date
2426 ,p_validation_end_date => l_validation_end_date
2427 );
2428 Else
2429 --
2430 -- We are doing a datetrack 'INSERT' which is illegal within this
2431 -- procedure therefore we must error (note: to lck on insert the
2432 -- private procedure ins_lck should be called).
2433 --
2437 fnd_message.raise_error;
2434 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2435 fnd_message.set_token('PROCEDURE', l_proc);
2436 fnd_message.set_token('STEP','20');
2438 End If;
2439 --
2440 -- Set the validation start and end date OUT arguments
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
2450 When HR_Api.Object_Locked then
2451 --
2452 -- The object is locked therefore we need to supply a meaningful
2453 -- error message.
2454 --
2455 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
2456 fnd_message.set_token('TABLE_NAME', 'pay_run_types_f');
2457 fnd_message.raise_error;
2458 End lck;
2459 --
2460 -- ----------------------------------------------------------------------------
2461 -- |-----------------------------< convert_args >-----------------------------|
2462 -- ----------------------------------------------------------------------------
2463 Function convert_args
2464 (p_run_type_id in number
2465 ,p_run_type_name in varchar2
2466 ,p_run_method in varchar2
2467 ,p_effective_start_date in date
2468 ,p_effective_end_date in date
2469 ,p_business_group_id in number
2470 ,p_legislation_code in varchar2
2471 ,p_shortname in varchar2
2472 ,p_srs_flag in varchar2
2473 ,p_run_information_category in varchar2
2474 ,p_run_information1 in varchar2
2475 ,p_run_information2 in varchar2
2476 ,p_run_information3 in varchar2
2477 ,p_run_information4 in varchar2
2478 ,p_run_information5 in varchar2
2479 ,p_run_information6 in varchar2
2480 ,p_run_information7 in varchar2
2481 ,p_run_information8 in varchar2
2482 ,p_run_information9 in varchar2
2483 ,p_run_information10 in varchar2
2484 ,p_run_information11 in varchar2
2485 ,p_run_information12 in varchar2
2486 ,p_run_information13 in varchar2
2487 ,p_run_information14 in varchar2
2488 ,p_run_information15 in varchar2
2489 ,p_run_information16 in varchar2
2490 ,p_run_information17 in varchar2
2491 ,p_run_information18 in varchar2
2492 ,p_run_information19 in varchar2
2493 ,p_run_information20 in varchar2
2494 ,p_run_information21 in varchar2
2495 ,p_run_information22 in varchar2
2496 ,p_run_information23 in varchar2
2497 ,p_run_information24 in varchar2
2498 ,p_run_information25 in varchar2
2499 ,p_run_information26 in varchar2
2500 ,p_run_information27 in varchar2
2501 ,p_run_information28 in varchar2
2502 ,p_run_information29 in varchar2
2503 ,p_run_information30 in varchar2
2504 ,p_object_version_number in number
2505 )
2506 Return g_rec_type is
2507 --
2508 l_rec g_rec_type;
2509 --
2510 Begin
2511 --
2512 -- Convert arguments into local l_rec structure.
2513 --
2514 l_rec.run_type_id := p_run_type_id;
2515 l_rec.run_type_name := p_run_type_name;
2516 l_rec.run_method := p_run_method;
2517 l_rec.effective_start_date := p_effective_start_date;
2518 l_rec.effective_end_date := p_effective_end_date;
2519 l_rec.business_group_id := p_business_group_id;
2520 l_rec.legislation_code := p_legislation_code;
2521 l_rec.shortname := p_shortname;
2522 l_rec.srs_flag := p_srs_flag;
2523 l_rec.run_information_category := p_run_information_category;
2524 l_rec.run_information1 := p_run_information1;
2525 l_rec.run_information2 := p_run_information2;
2526 l_rec.run_information3 := p_run_information3;
2527 l_rec.run_information4 := p_run_information4;
2528 l_rec.run_information5 := p_run_information5;
2529 l_rec.run_information6 := p_run_information6;
2530 l_rec.run_information7 := p_run_information7;
2531 l_rec.run_information8 := p_run_information8;
2532 l_rec.run_information9 := p_run_information9;
2533 l_rec.run_information10 := p_run_information10;
2534 l_rec.run_information11 := p_run_information11;
2535 l_rec.run_information12 := p_run_information12;
2536 l_rec.run_information13 := p_run_information13;
2537 l_rec.run_information14 := p_run_information14;
2538 l_rec.run_information15 := p_run_information15;
2539 l_rec.run_information16 := p_run_information16;
2540 l_rec.run_information17 := p_run_information17;
2541 l_rec.run_information18 := p_run_information18;
2542 l_rec.run_information19 := p_run_information19;
2543 l_rec.run_information20 := p_run_information20;
2544 l_rec.run_information21 := p_run_information21;
2545 l_rec.run_information22 := p_run_information22;
2546 l_rec.run_information23 := p_run_information23;
2547 l_rec.run_information24 := p_run_information24;
2548 l_rec.run_information25 := p_run_information25;
2549 l_rec.run_information26 := p_run_information26;
2550 l_rec.run_information27 := p_run_information27;
2551 l_rec.run_information28 := p_run_information28;
2552 l_rec.run_information29 := p_run_information29;
2553 l_rec.run_information30 := p_run_information30;
2554 l_rec.object_version_number := p_object_version_number;
2555 --
2556 -- Return the plsql record structure.
2557 --
2558 Return(l_rec);
2559 --
2560 End convert_args;
2561 --
2562 end pay_prt_shd;