[Home] [Help]
PACKAGE BODY: APPS.PAY_PRT_SHD
Source
1 Package Body pay_prt_shd as
2 /* $Header: pyprtrhi.pkb 120.1 2011/03/10 05:48:33 abdash ship $ */
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
200 PROCEDURE return_effective_dates
197 -- the specified table and primary key as of session date.
198 --
199 -- ----------------------------------------------------------------------------
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
235 /* FOR BUG 11830805 */
236
237 /* hr_utility.set_message_token
238 ('SESSION_DATE'
239 ,fnd_date.date_to_chardate(p_effective_date)
240 ); */
241
242 hr_utility.set_message_token
243 ('SESSION_DATE'
244 ,fnd_date.date_to_chardate(p_effective_date, calendar_aware=>FND_DATE.calendar_aware_alt)
245 );
246
247 /* FOR BUG 11830805 */
248
249 hr_utility.raise_error;
250 WHEN TOO_MANY_ROWS THEN
251 hr_utility.set_message(801, 'HR_7181_DT_OVERLAP_ROWS');
252 hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
253
254 /* FOR BUG 11830805 */
255
256 /* hr_utility.set_message_token
257 ('SESSION_DATE'
258 ,fnd_date.date_to_chardate(p_effective_date)
259 ); */
260
261 hr_utility.set_message_token
262 ('SESSION_DATE'
263 ,fnd_date.date_to_chardate(p_effective_date, calendar_aware=>FND_DATE.calendar_aware_alt)
264 );
265
266
267 /* FOR BUG 11830805 */
268
269 hr_utility.set_message_token('PRIMARY_VALUE', to_char(p_base_key_value));
270 hr_utility.raise_error;
271 WHEN OTHERS THEN
272 RAISE;
273 --
274 END return_effective_dates;
275 -- ----------------------------------------------------------------------------
276 -- |------------------------< Return_Max_End_Date >---------------------------|
277 -- ----------------------------------------------------------------------------
278 --
279 -- PRIVATE - copied from dt_api
280 --
281 -- Description: Function returns the maximum effective_end_date for the
282 -- specified table and primary key.
283 -- NOTE: if the maximum end date doesn't exist (i.e. no rows
284 -- exist for the specified table, key values) then we
285 -- return the null value.
286 -- ----------------------------------------------------------------------------
287 FUNCTION return_max_end_date
288 (p_base_table_name IN VARCHAR2,
289 p_base_key_column IN VARCHAR2,
290 p_base_key_value IN NUMBER)
291 RETURN DATE IS
292 --
293 l_proc VARCHAR2(72) := g_package||'return_max_end_date';
294 l_max_date DATE;
295 --
296 BEGIN
297 hr_utility.set_location('Entering:'||l_proc, 5);
298 -- Ensure that all the mandatory arguments are not null
299 hr_api.mandatory_arg_error(p_api_name => l_proc,
300 p_argument => 'p_base_table_name',
301 p_argument_value => p_base_table_name);
302 hr_api.mandatory_arg_error(p_api_name => l_proc,
303 p_argument => 'p_base_key_column',
304 p_argument_value => p_base_key_column);
305 hr_api.mandatory_arg_error(p_api_name => l_proc,
306 p_argument => 'p_base_key_value',
307 p_argument_value => p_base_key_value);
308 -- [ end of change 30.14 ]
309 -- Define dynamic sql text with substitution tokens
310 pay_prt_shd.g_dynamic_sql:=
311 'select max(t.effective_end_date) '||
312 'from '||p_base_table_name||' t '||
313 'where t.'||p_base_key_column||' = :p_base_key_value';
314 --
315 EXECUTE IMMEDIATE pay_prt_shd.g_dynamic_sql
316 INTO l_max_date
317 USING p_base_key_value;
318 --
319 hr_utility.set_location('Leaving :'||l_proc, 10);
320 RETURN(l_max_date);
321 --
322 END return_max_end_date;
323 -- ----------------------------------------------------------------------------
327 -- PRIVATE
324 -- |----------------------< Return_Min_Start_Date >---------------------------|
325 -- ----------------------------------------------------------------------------
326 --
328 --
329 -- Description: Function returns the minimum effective_start_date for the
330 -- specified table and primary key.
331 --
332 -- ----------------------------------------------------------------------------
333 FUNCTION return_min_start_date
334 (p_base_table_name in varchar2,
335 p_base_key_column in varchar2,
336 p_base_key_value in number)
337 RETURN DATE IS
338 --
339 l_proc VARCHAR2(72) := g_package||'return_min_start_date';
340 l_min_date DATE;
341 --
342 BEGIN
343 hr_utility.set_location('Entering:'||l_proc, 5);
344 -- Define dynamic sql text with substitution tokens
345 pay_prt_shd.g_dynamic_sql :=
346 'select min(t.effective_start_date) '||
347 'from '||p_base_table_name||' t '||
348 'where t.'||p_base_key_column||' = :p_base_key_value';
349 --
350 EXECUTE IMMEDIATE pay_prt_shd.g_dynamic_sql
351 INTO l_min_date
352 USING p_base_key_value;
353 -- Need to ensure that the minimum date is NOT null. If it is then we
354 -- must error
355 IF (l_min_date IS NULL) THEN
356 hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
357 hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
358 hr_utility.raise_error;
359 END IF;
360 --
361 hr_utility.set_location('Leaving :'||l_proc, 45);
362 RETURN(l_min_date);
363 END return_min_start_date;
364 -- ----------------------------------------------------------------------------
365 -- |-------------------------< Future_Rows_Exists >---------------------------|
366 -- ----------------------------------------------------------------------------
367 --
368 -- PRIVATE
369 --
370 -- Description: Function returns a boolean value. TRUE will be set if a future
371 -- row exists for the specified table as of effective date else
372 -- FALSE will be returned. A row must exist as of the effective
373 -- date otherwise an error will be returned.
374 --
375 -- ----------------------------------------------------------------------------
376 Function Future_Rows_Exist
377 (p_effective_date in date,
378 p_base_table_name in varchar2,
379 p_base_key_column in varchar2,
380 p_base_key_value in number)
381 Return Boolean Is
382 --
383 l_proc varchar2(72) := g_package||'Future_Rows_Exist';
384 l_boolean boolean := false;
385 l_dummy_esd date; -- Not required
386 l_effective_end_date date; -- Current effective end date
387 l_max_effective_end_date date; -- Maximum effective end date
388 --
389 Begin
390 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
391 --
392 -- Must ensure that a row exists as of the effective date supplied
393 -- and we need the current effective end date
394 --
395 Return_Effective_Dates
396 (p_effective_date => p_effective_date,
397 p_base_table_name => p_base_table_name,
398 p_base_key_column => p_base_key_column,
399 p_base_key_value => p_base_key_value,
400 p_effective_start_date => l_dummy_esd,
401 p_effective_end_date => l_effective_end_date);
402 --
403 -- We must select the maximum effective end date for the datetracked
404 -- rows
405 --
406 l_max_effective_end_date :=
407 Return_Max_End_Date
408 (p_base_table_name => p_base_table_name,
409 p_base_key_column => p_base_key_column,
410 p_base_key_value => p_base_key_value);
411 --
412 -- If the maximum effective end date is greater than the current effective
413 -- end date then future rows exist
414 --
415 If (l_max_effective_end_date > l_effective_end_date) then
416 l_boolean := TRUE;
417 End If;
418 --
419 Hr_Utility.Set_Location('Leaving :'||l_proc, 15);
420 Return(l_boolean);
421 --
422 End Future_Rows_Exist;
423 -- ----------------------------------------------------------------------------
424 -- |--------------------< Return_Min_Parent_End_Date >------------------------|
425 -- ----------------------------------------------------------------------------
426 --
427 -- PRIVATE
428 --
429 -- Description: Function returns the minimum validation end date for all the
430 -- specified parental entitites.
431 --
432 -- ----------------------------------------------------------------------------
433 Function Return_Min_Parent_End_Date
434 (p_effective_date in date,
435 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
436 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
437 p_parent_key_value1 in number default hr_api.g_number)
438 /*
439 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
440 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
441 p_parent_key_value2 in number default hr_api.g_number,
442 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
443 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
444 p_parent_key_value3 in number default hr_api.g_number,
445 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
446 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
447 p_parent_key_value4 in number default hr_api.g_number,
448 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
449 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
450 p_parent_key_value5 in number default hr_api.g_number,
451 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
452 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
453 p_parent_key_value6 in number default hr_api.g_number,
454 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
455 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
456 p_parent_key_value7 in number default hr_api.g_number,
457 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
458 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
459 p_parent_key_value8 in number default hr_api.g_number,
460 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
461 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
462 p_parent_key_value9 in number default hr_api.g_number,
463 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
464 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
465 p_parent_key_value10 in number default hr_api.g_number)
466 */
467 Return Date Is
468 --
469 l_proc varchar2(72) := g_package||
470 'Return_Min_Parent_End_Date';
471 --
472 l_min_date date := hr_api.g_eot; -- End Of Time
473 l_counter integer; -- Loop counter
474 l_temp_date date;
475 --
476 l_parent_table_name varchar2(30);
477 l_parent_key_column varchar2(30);
478 l_parent_key_value number;
479 --
480 Begin
481 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
482 --
483 -- <<Loop1>>
484 -- For l_counter In 1..1 Loop
485 --
486 -- Set the current working arguments to the corresponding functional
487 -- argument values
488 --
489 -- If (l_counter = 1) then
490 l_parent_table_name := p_parent_table_name1;
491 l_parent_key_column := p_parent_key_column1;
492 l_parent_key_value := p_parent_key_value1;
493 /*
494 ElsIf (l_counter = 2) then
495 l_parent_table_name := p_parent_table_name2;
496 l_parent_key_column := p_parent_key_column2;
497 l_parent_key_value := p_parent_key_value2;
498 ElsIf (l_counter = 3) then
499 l_parent_table_name := p_parent_table_name3;
500 l_parent_key_column := p_parent_key_column3;
501 l_parent_key_value := p_parent_key_value3;
502 ElsIf (l_counter = 4) then
503 l_parent_table_name := p_parent_table_name4;
504 l_parent_key_column := p_parent_key_column4;
505 l_parent_key_value := p_parent_key_value4;
506 ElsIf (l_counter = 5) then
507 l_parent_table_name := p_parent_table_name5;
508 l_parent_key_column := p_parent_key_column5;
509 l_parent_key_value := p_parent_key_value5;
510 ElsIf (l_counter = 6) then
511 l_parent_table_name := p_parent_table_name6;
512 l_parent_key_column := p_parent_key_column6;
513 l_parent_key_value := p_parent_key_value6;
514 ElsIf (l_counter = 7) then
515 l_parent_table_name := p_parent_table_name7;
516 l_parent_key_column := p_parent_key_column7;
517 l_parent_key_value := p_parent_key_value7;
518 ElsIf (l_counter = 8) then
519 l_parent_table_name := p_parent_table_name8;
520 l_parent_key_column := p_parent_key_column8;
521 l_parent_key_value := p_parent_key_value8;
522 ElsIf (l_counter = 9) then
523 l_parent_table_name := p_parent_table_name9;
524 l_parent_key_column := p_parent_key_column9;
525 l_parent_key_value := p_parent_key_value9;
526 Else
527 l_parent_table_name := p_parent_table_name10;
528 l_parent_key_column := p_parent_key_column10;
529 l_parent_key_value := p_parent_key_value10;
530 End If;
531 */
532 --
533 -- Ensure that all the working parental details have been specified
534 --
535 If NOT ((nvl(l_parent_table_name, hr_api.g_varchar2) =
536 hr_api.g_varchar2) or
537 (nvl(l_parent_key_column, hr_api.g_varchar2) =
538 hr_api.g_varchar2) or
539 (nvl(l_parent_key_value, hr_api.g_number) =
540 hr_api.g_number)) then
541 --
542 -- All the parental arguments have been specified therefore we must get
543 -- the maximum effective end date for the given parent.
544 --
545 l_temp_date := Return_Max_End_Date
546 (p_base_table_name => l_parent_table_name,
547 p_base_key_column => l_parent_key_column,
548 p_base_key_value => l_parent_key_value);
549 --
550 -- If the returned l_temp_date is null or the less than the
551 -- effective_date then error because a parental row does NOT exist.
552 --
553 If ( l_temp_date is null or
554 (l_temp_date < p_effective_date)) then
555 --
556 -- The parental rows specified do not exist as of the effective date
557 -- therefore a serious integrity problem has ocurred
558 --
559 hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
560 hr_utility.set_message_token('ARGUMENT', upper(l_parent_key_column));
561 hr_utility.raise_error;
562 Else
563 --
564 -- The LEAST function will then compare the working l_min_date with the
565 -- returned maximum effective end date (l_temp_date) and set the
566 -- l_min_date to the minimum of these dates
567 --
568 l_min_date := least(l_min_date, l_temp_date);
569 End If;
570 End If;
571 -- End Loop;
572 --
573 Hr_Utility.Set_Location('Leaving :'||l_proc, 15);
574 Return(l_min_date);
575 --
576 End Return_Min_Parent_End_Date;
577 -- ----------------------------------------------------------------------------
578 -- |--------------------< Return_Min_Parent_Start_Date >----------------------|
579 -- ----------------------------------------------------------------------------
580 --
581 -- PRIVATE
582 --
583 -- Description: Function returns the minimum validation start date for all the
584 -- specified parental entitites.
585 --
586 -- ----------------------------------------------------------------------------
587 Function Return_Min_Parent_Start_Date
588 (p_effective_date in date,
589 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
590 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
591 p_parent_key_value1 in number default hr_api.g_number)
592 /*
593 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
594 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
595 p_parent_key_value2 in number default hr_api.g_number,
596 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
597 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
598 p_parent_key_value3 in number default hr_api.g_number,
599 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
600 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
601 p_parent_key_value4 in number default hr_api.g_number,
602 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
603 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
604 p_parent_key_value5 in number default hr_api.g_number,
605 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
606 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
607 p_parent_key_value6 in number default hr_api.g_number,
608 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
609 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
610 p_parent_key_value7 in number default hr_api.g_number,
611 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
612 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
613 p_parent_key_value8 in number default hr_api.g_number,
614 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
615 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
616 p_parent_key_value9 in number default hr_api.g_number,
617 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
618 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
619 p_parent_key_value10 in number default hr_api.g_number)
620 */
621 Return Date Is
622 --
623 l_proc varchar2(72) := g_package||
624 'Return_Min_Parent_Start_Date';
625 --
626 l_min_date date := hr_api.g_sot; -- Start Of Time
627 l_counter integer; -- Loop counter
628 l_temp_date date;
629 --
630 l_parent_table_name varchar2(30);
631 l_parent_key_column varchar2(30);
632 l_parent_key_value number;
633 --
634 Begin
635 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
636 --
637 /*
638 <<Loop1>>
639 For l_counter In 1..10 Loop
640 --
641 -- Set the current working arguments to the corresponding functional
642 -- argument values
643 --
644 If (l_counter = 1) then
645 */
646 l_parent_table_name := p_parent_table_name1;
647 l_parent_key_column := p_parent_key_column1;
648 l_parent_key_value := p_parent_key_value1;
649 /*
650 ElsIf (l_counter = 2) then
651 l_parent_table_name := p_parent_table_name2;
652 l_parent_key_column := p_parent_key_column2;
653 l_parent_key_value := p_parent_key_value2;
654 ElsIf (l_counter = 3) then
655 l_parent_table_name := p_parent_table_name3;
656 l_parent_key_column := p_parent_key_column3;
657 l_parent_key_value := p_parent_key_value3;
658 ElsIf (l_counter = 4) then
659 l_parent_table_name := p_parent_table_name4;
660 l_parent_key_column := p_parent_key_column4;
661 l_parent_key_value := p_parent_key_value4;
662 ElsIf (l_counter = 5) then
663 l_parent_table_name := p_parent_table_name5;
664 l_parent_key_column := p_parent_key_column5;
665 l_parent_key_value := p_parent_key_value5;
666 ElsIf (l_counter = 6) then
667 l_parent_table_name := p_parent_table_name6;
668 l_parent_key_column := p_parent_key_column6;
669 l_parent_key_value := p_parent_key_value6;
670 ElsIf (l_counter = 7) then
671 l_parent_table_name := p_parent_table_name7;
672 l_parent_key_column := p_parent_key_column7;
673 l_parent_key_value := p_parent_key_value7;
674 ElsIf (l_counter = 8) then
675 l_parent_table_name := p_parent_table_name8;
676 l_parent_key_column := p_parent_key_column8;
677 l_parent_key_value := p_parent_key_value8;
678 ElsIf (l_counter = 9) then
679 l_parent_table_name := p_parent_table_name9;
680 l_parent_key_column := p_parent_key_column9;
681 l_parent_key_value := p_parent_key_value9;
682 Else
683 l_parent_table_name := p_parent_table_name10;
684 l_parent_key_column := p_parent_key_column10;
685 l_parent_key_value := p_parent_key_value10;
686 End If;
687 */
688 --
689 -- Ensure that all the working parental details have been specified
690 --
691 If NOT ((nvl(l_parent_table_name, hr_api.g_varchar2) =
692 hr_api.g_varchar2) or
693 (nvl(l_parent_key_column, hr_api.g_varchar2) =
694 hr_api.g_varchar2) or
695 (nvl(l_parent_key_value, hr_api.g_number) =
696 hr_api.g_number)) then
697 --
698 -- All the parental arguments have been specified therefore we must get
699 -- the minimum effective start date for the given parent.
700 --
701 l_temp_date := Return_Min_Start_Date
702 (p_base_table_name => l_parent_table_name,
703 p_base_key_column => l_parent_key_column,
704 p_base_key_value => l_parent_key_value);
705 --
706 -- If the returned l_temp_date is null or greater than the
707 -- effective_date then error because a parental row does NOT exist.
708 --
709 If ( l_temp_date is null or
710 (l_temp_date > p_effective_date)) then
711 --
712 -- The parental rows specified do not exist as of the effective date
713 -- therefore a serious integrity problem has ocurred
714 --
715 hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
716 hr_utility.set_message_token('ARGUMENT', upper(l_parent_key_column));
717 hr_utility.raise_error;
718 Else
719 --
720 -- The LEAST function will then compare the working l_min_date with the
721 -- returned miniumum effective start date (l_temp_date) and set the
722 -- l_min_date to the maximum of these dates
723 --
724 l_min_date := greatest(l_min_date, l_temp_date);
725 End If;
726 End If;
727 -- End Loop;
728 --
729 Hr_Utility.Set_Location('Leaving :'||l_proc, 15);
730 Return(l_min_date);
731 --
732 End Return_Min_Parent_Start_Date;
733 -- ----------------------------------------------------------------------------
734 -- |-----------------------< Lck_Future_Rows >--------------------------------|
735 -- ----------------------------------------------------------------------------
736 --
737 -- PRIVATE - copied from dt_api
738 --
739 -- Description: Locks the entity from the effective_date to the end-of-time.
740 -- No processing will be completed if the p_key_value is
741 -- null because the column could be defined as nullable.
742 -- If no rows where locked for the given values then the procedure
743 -- will error because at least 1 row must be locked.
744 --
745 -- ----------------------------------------------------------------------------
746 PROCEDURE lck_future_rows
747 (p_effective_date IN DATE,
748 p_table_name IN VARCHAR2,
749 p_key_column IN VARCHAR2,
750 p_key_value IN NUMBER) IS
751 --
752 l_proc VARCHAR2(72) := g_package||'lck_future_rows';
753 l_dummy_num NUMBER;
754 TYPE l_csr_type IS REF CURSOR; -- define weak REF CURSOR type
755 l_csr l_csr_type;
756 --
757 BEGIN
758 hr_utility.set_location('Entering:'||l_proc, 5);
759 --
760 -- Ensure that all the required parameters exist
761 -- Note: we don't check the p_key_value argument
762 --
763 -- [ start of change 30.14 ]
764 hr_api.mandatory_arg_error(p_api_name => l_proc,
765 p_argument => 'p_effective_date',
766 p_argument_value => p_effective_date);
767 hr_api.mandatory_arg_error(p_api_name => l_proc,
768 p_argument => 'p_table_name',
769 p_argument_value => p_table_name);
770 hr_api.mandatory_arg_error(p_api_name => l_proc,
771 p_argument => 'p_key_column',
772 p_argument_value => p_key_column);
773 -- [ end of change 30.14 ]
774 -- If the p_key_value is null then we must not
775 -- process the sql as it could be a nullable column.
776 IF (p_key_value IS NOT NULL) THEN
777 -- Define dynamic sql text with substitution tokens
778 pay_prt_shd.g_dynamic_sql :=
779 'select 1 ' ||
780 'from '||p_table_name||' t1 ' ||
781 'where t1.'||p_key_column||' = :p_key_value ' ||
782 'and t1.effective_end_date >= :p_effective_date ' ||
783 'order by t1.effective_start_date ' ||
784 'for update nowait';
785 --
786 OPEN l_csr FOR pay_prt_shd.g_dynamic_sql USING p_key_value, p_effective_date
787 ;
788 LOOP
789 FETCH l_csr INTO l_dummy_num;
790 EXIT WHEN l_csr%NOTFOUND; -- exit loop when last row is fetched
791 END LOOP;
792 CLOSE l_csr;
793 --
794 hr_utility.set_location(' Leaving:'||l_proc, 10);
795 END IF;
796 EXCEPTION
797 WHEN NO_DATA_FOUND THEN
798 -- The parental rows specified do not exist as of the effective date
799 -- therefore a serious integrity problem has ocurred
800 hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
801 hr_utility.set_message_token('ARGUMENT', upper(p_key_column));
802 hr_utility.raise_error;
803 WHEN hr_api.object_locked THEN
804 -- The object is locked therefore we need to supply a meaningful
805 -- error message.
806 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
807 hr_utility.set_message_token('TABLE_NAME', p_table_name);
808 hr_utility.raise_error;
809 WHEN OTHERS THEN
810 RAISE;
811 --
812 END lck_future_rows;
813 -- ----------------------------------------------------------------------------
814 -- |----------------------------< Lck_Parent >--------------------------------|
815 -- ----------------------------------------------------------------------------
816 --
817 -- PUBLIC
818 --
819 -- Description: Locks the specified parental entity from the effective_date
820 -- to the end-of-time by calling the Lck_Future_Rows procedure.
821 --
822 -- ----------------------------------------------------------------------------
823 Procedure Lck_Parent
824 (p_effective_date in date,
825 p_parent_table_name in varchar2,
826 p_parent_key_column in varchar2,
827 p_parent_key_value in number) Is
828 --
829 l_proc varchar2(72) := g_package||'Lck_Parent';
830 --
831 Begin
832 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
833 --
834 Lck_Future_Rows
835 (p_effective_date => p_effective_date,
836 p_table_name => p_parent_table_name,
837 p_key_column => p_parent_key_column,
838 p_key_value => p_parent_key_value);
839 --
840 Hr_Utility.Set_Location(' Leaving:'||l_proc, 35);
841 End Lck_Parent;
842 -- ----------------------------------------------------------------------------
843 -- |----------------------------< Lck_Child >---------------------------------|
844 -- ----------------------------------------------------------------------------
845 --
846 -- PRIVATE - copied from dt_api
847 --
848 -- Description: Locks the specified child entity maximum row for the specified
849 -- parent key value:
850 --
851 -- E.g. ('X' denotes locked rows)
852 --
853 -- |---------------------------------------| Parent Entity
854 -- |---------|XXXXXXXXXX| Child DT Rows 1
855 -- |-------------|XXXXXXXXXXXXXXX| Child DT Rows 2
856 -- |---|-----|XXXXXXXXXXXXX| Child DT Rows 3
857 --
858 -- After locking the maximum row, we must ensure that the
859 -- effective end date of the locked row cannot exceed
860 -- the validation start date.
861 --
862 -- No processing will be completed if the p_parent_key_value is
863 -- null because the column could be defined as nullable.
864 --
865 -- ----------------------------------------------------------------------------
866 PROCEDURE lck_child
867 (p_child_table_name IN VARCHAR2,
868 p_child_key_column IN VARCHAR2,
869 p_parent_key_column IN VARCHAR2,
870 p_parent_key_value IN NUMBER,
871 p_child_fk_column IN VARCHAR2,
872 p_validation_start_date IN DATE) IS
873 --
874 l_proc VARCHAR2(72) := g_package||'lck_child';
875 --
876 l_lck_date DATE;
877 TYPE l_cursor_type IS REF CURSOR; -- define weak REF CURSOR type
878 l_cursor l_cursor_type;
879 --
880 BEGIN
881 hr_utility.set_location('Entering:'||l_proc, 5);
882 --
883 -- Ensure that all the required parameters exist
884 -- Note: we don't check the p_parent_key_value argument
885 --
886 hr_api.mandatory_arg_error(p_api_name => l_proc,
887 p_argument => 'p_child_table_name',
888 p_argument_value => p_child_table_name);
889 hr_api.mandatory_arg_error(p_api_name => l_proc,
890 p_argument => 'p_child_key_column',
891 p_argument_value => p_child_key_column);
892 hr_api.mandatory_arg_error(p_api_name => l_proc,
893 p_argument => 'p_parent_key_column',
894 p_argument_value => p_parent_key_column);
895 hr_api.mandatory_arg_error(p_api_name => l_proc,
896 p_argument => 'p_validation_start_date',
897 p_argument_value => p_validation_start_date);
898 --
899 -- If the p_parent_key_value is null then we must not
900 -- process the sql as it could be a nullable column.
901 --
902 IF (p_parent_key_value IS NOT NULL) THEN
903 hr_utility.set_location(l_proc, 10);
904 -- Define dynamic sql text with substitution tokens
905 pay_prt_shd.g_dynamic_sql :=
909 ' t1.effective_start_date, ' ||
906 'select t1.effective_end_date effective_end_date ' ||
907 'from '||p_child_table_name||' t1 ' ||
908 'where (t1.'||p_child_key_column||', ' ||
910 ' t1.effective_end_date) in ' ||
911 ' (select t2.'||p_child_key_column||', ' ||
912 ' max(t2.effective_start_date), ' ||
913 ' max(t2.effective_end_date) ' ||
914 ' from '||p_child_table_name||' t2 ' ||
915 ' where t2.'||p_child_fk_column||' = :p_parent_key_value ' ||
916 ' group by t2.'||p_child_key_column||')' ||
917 'order by t1.'||p_child_key_column||' ' ||
918 'for update nowait';
919 -- open a cursor
920 OPEN l_cursor
921 FOR pay_prt_shd.g_dynamic_sql
922 USING p_parent_key_value;
923 hr_utility.set_location(l_proc, 15);
924 --
925 LOOP
926 FETCH l_cursor INTO l_lck_date;
927 hr_utility.set_location(l_proc, 20);
928 EXIT WHEN l_cursor%NOTFOUND;
929 -- For each locked row we must ensure that the maximum end date is NOT
930 -- greater than the validation start date
931 IF (l_lck_date >= p_validation_start_date) THEN
932 -- The maximum end date is greater than or equal to the
933 -- validation start date therefore we must error
934 hr_utility.set_location(l_proc, 22);
935 hr_utility.set_message(801, 'HR_7201_DT_NO_DELETE_CHILD');
936 hr_utility.raise_error;
937 END IF;
938 hr_utility.set_location('Entering:'||l_proc, 25);
939 END LOOP;
940 --
941 CLOSE l_cursor;
942 hr_utility.set_location(' Leaving:'||l_proc, 35);
943 END IF;
944 EXCEPTION
945 WHEN hr_api.object_locked THEN
946 IF l_cursor%ISOPEN THEN
947 CLOSE l_cursor;
948 END IF;
949 -- The object is locked therefore we need to supply a meaningful
950 -- error message.
951 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
952 hr_utility.set_message_token('TABLE_NAME', p_child_table_name);
953 hr_utility.raise_error;
954 --
955 WHEN OTHERS THEN
956 IF l_cursor%ISOPEN THEN
957 CLOSE l_cursor;
958 END IF;
959 RAISE;
960 END lck_child;
961 -- ----------------------------------------------------------------------------
962 -- |-------------------------< Get_Insert_Dates >-----------------------------|
963 -- ----------------------------------------------------------------------------
964 --
965 -- PRIVATE - copied from dt_api
966 --
967 -- Description: Locks and parental entity rows (if supplied) and Returns
968 -- the validation start and end dates for the DateTrack
969 -- INSERT mode
970 --
971 -- ----------------------------------------------------------------------------
972 Procedure Get_Insert_Dates
973 (p_effective_date in date,
974 p_base_table_name in varchar2,
975 p_base_key_column in varchar2,
976 p_base_key_value in number,
977 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
978 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
979 p_parent_key_value1 in number default hr_api.g_number,
980 /*
981 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
982 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
983 p_parent_key_value2 in number default hr_api.g_number,
984 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
985 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
986 p_parent_key_value3 in number default hr_api.g_number,
987 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
988 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
989 p_parent_key_value4 in number default hr_api.g_number,
990 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
991 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
992 p_parent_key_value5 in number default hr_api.g_number,
993 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
994 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
995 p_parent_key_value6 in number default hr_api.g_number,
996 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
997 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
998 p_parent_key_value7 in number default hr_api.g_number,
999 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
1000 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
1001 p_parent_key_value8 in number default hr_api.g_number,
1002 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
1003 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
1004 p_parent_key_value9 in number default hr_api.g_number,
1005 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
1006 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
1007 p_parent_key_value10 in number default hr_api.g_number,
1008 */
1009 p_enforce_foreign_locking in boolean,
1010 p_validation_start_date out nocopy date,
1011 p_validation_end_date out nocopy date) Is
1012 --
1013 l_proc varchar2(72) := g_package||'Get_Insert_Dates';
1014 l_parent_table_name varchar2(30);
1015 l_parent_key_column varchar2(30);
1016 l_parent_key_value number;
1017 l_counter integer; -- Loop counter
1018 l_dummy_date date;
1019 --
1020 Begin
1021 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1022 --
1023 -- Step 1: Lock all parent rows specified from the effective date onwards
1024 -- providing that the p_enforce_foreign_locking is TRUE.
1025 --
1026 If p_enforce_foreign_locking Then
1027 /* For l_counter In 1..10 Loop
1028 --
1029 -- Set the current working arguments to the corresponding functional
1030 -- argument values
1031 --
1032 If (l_counter = 1) then
1033 */
1034 l_parent_table_name := p_parent_table_name1;
1035 l_parent_key_column := p_parent_key_column1;
1036 l_parent_key_value := p_parent_key_value1;
1037 /*
1038 ElsIf (l_counter = 2) then
1039 l_parent_table_name := p_parent_table_name2;
1040 l_parent_key_column := p_parent_key_column2;
1041 l_parent_key_value := p_parent_key_value2;
1042 ElsIf (l_counter = 3) then
1043 l_parent_table_name := p_parent_table_name3;
1044 l_parent_key_column := p_parent_key_column3;
1045 l_parent_key_value := p_parent_key_value3;
1046 ElsIf (l_counter = 4) then
1047 l_parent_table_name := p_parent_table_name4;
1048 l_parent_key_column := p_parent_key_column4;
1049 l_parent_key_value := p_parent_key_value4;
1050 ElsIf (l_counter = 5) then
1051 l_parent_table_name := p_parent_table_name5;
1052 l_parent_key_column := p_parent_key_column5;
1053 l_parent_key_value := p_parent_key_value5;
1054 ElsIf (l_counter = 6) then
1055 l_parent_table_name := p_parent_table_name6;
1056 l_parent_key_column := p_parent_key_column6;
1057 l_parent_key_value := p_parent_key_value6;
1058 ElsIf (l_counter = 7) then
1059 l_parent_table_name := p_parent_table_name7;
1060 l_parent_key_column := p_parent_key_column7;
1061 l_parent_key_value := p_parent_key_value7;
1062 ElsIf (l_counter = 8) then
1063 l_parent_table_name := p_parent_table_name8;
1064 l_parent_key_column := p_parent_key_column8;
1065 l_parent_key_value := p_parent_key_value8;
1066 ElsIf (l_counter = 9) then
1067 l_parent_table_name := p_parent_table_name9;
1068 l_parent_key_column := p_parent_key_column9;
1069 l_parent_key_value := p_parent_key_value9;
1070 Else
1071 l_parent_table_name := p_parent_table_name10;
1072 l_parent_key_column := p_parent_key_column10;
1073 l_parent_key_value := p_parent_key_value10;
1074 End If;
1075 */
1076 --
1077 -- Ensure that all the working parental details have been specified
1078 --
1079 If NOT ((nvl(l_parent_table_name, hr_api.g_varchar2) =
1080 hr_api.g_varchar2) or
1081 (nvl(l_parent_key_column, hr_api.g_varchar2) =
1082 hr_api.g_varchar2) or
1083 (nvl(l_parent_key_value, hr_api.g_number) =
1084 hr_api.g_number)) then
1085 --
1086 -- All the parental arguments have been specified therefore we must
1087 -- attempt to lock the specified parent rows.
1088 --
1089 Lck_Parent
1090 (p_effective_date => p_effective_date,
1091 p_parent_table_name => l_parent_table_name,
1092 p_parent_key_column => l_parent_key_column,
1093 p_parent_key_value => l_parent_key_value);
1094 End If;
1095 -- End Loop;
1096 End If;
1097 --
1098 -- Set the validation start date to the effective date and
1099 -- the validation end date to the minimum parental end date
1100 --
1101 -- Validate the effective date
1102 --
1103 l_dummy_date :=
1104 Return_Min_Parent_Start_Date
1105 (p_effective_date => p_effective_date,
1106 p_parent_table_name1 => p_parent_table_name1,
1107 p_parent_key_column1 => p_parent_key_column1,
1108 p_parent_key_value1 => p_parent_key_value1);
1109 /*
1110 p_parent_table_name2 => p_parent_table_name2,
1111 p_parent_key_column2 => p_parent_key_column2,
1112 p_parent_key_value2 => p_parent_key_value2,
1113 p_parent_table_name3 => p_parent_table_name3,
1114 p_parent_key_column3 => p_parent_key_column3,
1115 p_parent_key_value3 => p_parent_key_value3,
1116 p_parent_table_name4 => p_parent_table_name4,
1117 p_parent_key_column4 => p_parent_key_column4,
1118 p_parent_key_value4 => p_parent_key_value4,
1119 p_parent_table_name5 => p_parent_table_name5,
1120 p_parent_key_column5 => p_parent_key_column5,
1121 p_parent_key_value5 => p_parent_key_value5,
1122 p_parent_table_name6 => p_parent_table_name6,
1123 p_parent_key_column6 => p_parent_key_column6,
1124 p_parent_key_value6 => p_parent_key_value6,
1125 p_parent_table_name7 => p_parent_table_name7,
1126 p_parent_key_column7 => p_parent_key_column7,
1127 p_parent_key_value7 => p_parent_key_value7,
1128 p_parent_table_name8 => p_parent_table_name8,
1129 p_parent_key_column8 => p_parent_key_column8,
1130 p_parent_key_value8 => p_parent_key_value8,
1131 p_parent_table_name9 => p_parent_table_name9,
1132 p_parent_key_column9 => p_parent_key_column9,
1133 p_parent_key_value9 => p_parent_key_value9,
1134 p_parent_table_name10 => p_parent_table_name10,
1135 p_parent_key_column10 => p_parent_key_column10,
1136 p_parent_key_value10 => p_parent_key_value10);
1137 */
1138 --
1139 p_validation_start_date := p_effective_date;
1140 p_validation_end_date :=
1141 Return_Min_Parent_End_Date
1142 (p_effective_date => p_effective_date,
1143 p_parent_table_name1 => p_parent_table_name1,
1144 p_parent_key_column1 => p_parent_key_column1,
1145 p_parent_key_value1 => p_parent_key_value1);
1146 /*
1147 p_parent_table_name2 => p_parent_table_name2,
1148 p_parent_key_column2 => p_parent_key_column2,
1149 p_parent_key_value2 => p_parent_key_value2,
1150 p_parent_table_name3 => p_parent_table_name3,
1151 p_parent_key_column3 => p_parent_key_column3,
1152 p_parent_key_value3 => p_parent_key_value3,
1153 p_parent_table_name4 => p_parent_table_name4,
1154 p_parent_key_column4 => p_parent_key_column4,
1155 p_parent_key_value4 => p_parent_key_value4,
1156 p_parent_table_name5 => p_parent_table_name5,
1157 p_parent_key_column5 => p_parent_key_column5,
1158 p_parent_key_value5 => p_parent_key_value5,
1159 p_parent_table_name6 => p_parent_table_name6,
1160 p_parent_key_column6 => p_parent_key_column6,
1161 p_parent_key_value6 => p_parent_key_value6,
1162 p_parent_table_name7 => p_parent_table_name7,
1163 p_parent_key_column7 => p_parent_key_column7,
1164 p_parent_key_value7 => p_parent_key_value7,
1165 p_parent_table_name8 => p_parent_table_name8,
1166 p_parent_key_column8 => p_parent_key_column8,
1167 p_parent_key_value8 => p_parent_key_value8,
1168 p_parent_table_name9 => p_parent_table_name9,
1169 p_parent_key_column9 => p_parent_key_column9,
1170 p_parent_key_value9 => p_parent_key_value9,
1171 p_parent_table_name10 => p_parent_table_name10,
1172 p_parent_key_column10 => p_parent_key_column10,
1173 p_parent_key_value10 => p_parent_key_value10);
1174 */
1175 --
1176 Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1177 --
1178 End Get_Insert_Dates;
1179 -- ----------------------------------------------------------------------------
1180 -- |-----------------------< Get_Correction_Dates >---------------------------|
1181 -- ----------------------------------------------------------------------------
1182 --
1183 -- PRIVATE
1184 --
1185 -- Description: Returns the validation start and end dates for the
1186 -- DateTrack CORRECTION mode.
1187 --
1188 -- ----------------------------------------------------------------------------
1189 Procedure Get_Correction_Dates
1190 (p_effective_date in date,
1191 p_base_table_name in varchar2,
1192 p_base_key_column in varchar2,
1193 p_base_key_value in number,
1194 p_validation_start_date out nocopy date,
1195 p_validation_end_date out nocopy date) Is
1196 --
1197 l_proc varchar2(72) := g_package||'Get_Correction_Dates';
1198 l_effective_start_date date; -- Holds current effective start date
1199 l_effective_end_date date; -- Holds current effective end date
1200 --
1201 Begin
1202 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1203 Return_Effective_Dates
1204 (p_effective_date => p_effective_date,
1205 p_base_table_name => p_base_table_name,
1206 p_base_key_column => p_base_key_column,
1207 p_base_key_value => p_base_key_value,
1208 p_effective_start_date => l_effective_start_date,
1209 p_effective_end_date => l_effective_end_date);
1210 --
1211 -- The CORRECTION mode will always be valid therefore we must just
1212 -- return the validation start and end dates
1213 --
1214 p_validation_start_date := l_effective_start_date;
1215 p_validation_end_date := l_effective_end_date;
1216 --
1217 Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1218 End Get_Correction_Dates;
1219 -- ----------------------------------------------------------------------------
1220 -- |--------------------------< Get_Update_Dates >----------------------------|
1221 -- ----------------------------------------------------------------------------
1222 --
1223 -- PRIVATE
1224 --
1225 -- Description: Returns the validation start and end dates for the
1226 -- DateTrack UPDATE mode if allowed.
1227 --
1228 -- ----------------------------------------------------------------------------
1229 Procedure Get_Update_Dates
1230 (p_effective_date in date,
1231 p_base_table_name in varchar2,
1232 p_base_key_column in varchar2,
1233 p_base_key_value in number,
1234 p_validation_start_date out nocopy date,
1235 p_validation_end_date out nocopy date) Is
1236 --
1237 l_proc varchar2(72) := g_package||'Get_Update_Dates';
1238 l_effective_start_date date; -- Holds current effective start date
1239 l_effective_end_date date; -- Holds current effective end date
1240 --
1241 Begin
1242 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1243 --
1244 -- Determine if any future rows exist
1245 --
1246 If NOT (Future_Rows_Exist
1247 (p_effective_date => p_effective_date,
1248 p_base_table_name => p_base_table_name,
1249 p_base_key_column => p_base_key_column,
1250 p_base_key_value => p_base_key_value)) then
1251 --
1252 Return_Effective_Dates
1253 (p_effective_date => p_effective_date,
1254 p_base_table_name => p_base_table_name,
1255 p_base_key_column => p_base_key_column,
1256 p_base_key_value => p_base_key_value,
1257 p_effective_start_date => l_effective_start_date,
1258 p_effective_end_date => l_effective_end_date);
1259 --
1260 -- Providing the current effective start date is not equal to the effective
1261 -- date we must return the the validation start and end dates
1262 --
1263 If (l_effective_start_date <> p_effective_date) then
1264 p_validation_start_date := p_effective_date;
1265 p_validation_end_date := l_effective_end_date;
1266 Else
1267 --
1268 -- We cannot perform a DateTrack update operation where the effective
1269 -- date is the same as the current effective end date
1270 --
1271 hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1272 hr_utility.raise_error;
1273 End If;
1274 Else
1275 hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1276 hr_utility.raise_error;
1277 End If;
1278 Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1279 --
1280 End Get_Update_Dates;
1281 -- ----------------------------------------------------------------------------
1282 -- |-------------------< Get_Update_Override_Dates >--------------------------|
1283 -- ----------------------------------------------------------------------------
1284 --
1285 -- PRIVATE
1286 --
1287 -- Description: Returns the validation start and end dates for the
1288 -- DateTrack UPDATE_OVERRIDE mode if allowed.
1289 --
1290 -- ----------------------------------------------------------------------------
1291 Procedure Get_Update_Override_Dates
1292 (p_effective_date in date,
1293 p_base_table_name in varchar2,
1294 p_base_key_column in varchar2,
1295 p_base_key_value in number,
1296 p_validation_start_date out nocopy date,
1297 p_validation_end_date out nocopy date) Is
1298 --
1299 l_proc varchar2(72) := g_package||
1300 'Get_Update_Override_Dates';
1301 l_effective_start_date date; -- Holds current effective start date
1302 l_effective_end_date date; -- Holds current effective end date
1303 --
1304 Begin
1305 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1306 Return_Effective_Dates
1307 (p_effective_date => p_effective_date,
1308 p_base_table_name => p_base_table_name,
1309 p_base_key_column => p_base_key_column,
1310 p_base_key_value => p_base_key_value,
1311 p_effective_start_date => l_effective_start_date,
1312 p_effective_end_date => l_effective_end_date);
1313 --
1317 --
1314 -- If the current effective start date is not the same as the effective date
1315 -- and at least one future row exists then we must return the validation
1316 -- start and end dates
1318 If (l_effective_start_date <> p_effective_date) then
1319 --
1320 -- As the current row does not start on the effective date we determine if
1321 -- any future rows exist
1322 --
1323 If (Future_Rows_Exist
1324 (p_effective_date => p_effective_date,
1325 p_base_table_name => p_base_table_name,
1326 p_base_key_column => p_base_key_column,
1327 p_base_key_value => p_base_key_value)) then
1328 p_validation_start_date := p_effective_date;
1329 p_validation_end_date := Return_Max_End_Date
1330 (p_base_table_name => p_base_table_name,
1331 p_base_key_column => p_base_key_column,
1332 p_base_key_value => p_base_key_value);
1333 Else
1334 hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1335 hr_utility.set_message_token('DT_MODE', 'update override');
1336 hr_utility.raise_error;
1337 End If;
1338 Else
1339 hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1340 hr_utility.raise_error;
1341 End If;
1342 Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1343 --
1344 End Get_Update_Override_Dates;
1345 -- ----------------------------------------------------------------------------
1346 -- |-----------------< Get_Update_Change_Insert_Dates >-----------------------|
1347 -- ----------------------------------------------------------------------------
1348 --
1349 -- PRIVATE
1350 --
1351 -- Description: Returns the validation start and end dates for the
1352 -- DateTrack UPDATE_CHANGE_INSERT mode if allowed.
1353 --
1354 -- ----------------------------------------------------------------------------
1355 Procedure Get_Update_Change_Insert_Dates
1356 (p_effective_date in date,
1357 p_base_table_name in varchar2,
1358 p_base_key_column in varchar2,
1359 p_base_key_value in number,
1360 p_validation_start_date out nocopy date,
1361 p_validation_end_date out nocopy date) Is
1362 --
1363 l_proc varchar2(72) := g_package||
1364 'Get_Update_Change_Insert_Dates';
1365 l_effective_start_date date; -- Holds current effective start date
1366 l_effective_end_date date; -- Holds current effective end date
1367 --
1368 Begin
1369 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1370 Return_Effective_Dates
1371 (p_effective_date => p_effective_date,
1372 p_base_table_name => p_base_table_name,
1373 p_base_key_column => p_base_key_column,
1374 p_base_key_value => p_base_key_value,
1375 p_effective_start_date => l_effective_start_date,
1376 p_effective_end_date => l_effective_end_date);
1377 --
1378 -- If the current effective start date is not the same as the effective date
1379 -- and at least one future row exists then we must return the validation
1380 -- start and end dates
1381 --
1382 If (l_effective_start_date <> p_effective_date) then
1383 --
1384 -- As the current row does not start on the effective date we determine if
1385 -- any future rows exist
1386 --
1387 If (Future_Rows_Exist
1388 (p_effective_date => p_effective_date,
1389 p_base_table_name => p_base_table_name,
1390 p_base_key_column => p_base_key_column,
1391 p_base_key_value => p_base_key_value)) then
1392 p_validation_start_date := p_effective_date;
1393 p_validation_end_date := l_effective_end_date;
1394 Else
1395 hr_utility.set_message(801, 'HR_7183_DT_NO_FUTURE_ROWS');
1396 hr_utility.set_message_token('DT_MODE', 'update change insert');
1397 hr_utility.raise_error;
1398 End If;
1399 Else
1400 hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1401 hr_utility.raise_error;
1402 End If;
1403 Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1404 --
1405 End Get_Update_Change_Insert_Dates;
1406 -- ----------------------------------------------------------------------------
1407 -- |----------------------------< Get_Zap_Dates >-----------------------------|
1408 -- ----------------------------------------------------------------------------
1409 --
1410 -- PRIVATE
1411 --
1412 -- Description: Returns the validation start and end dates for the
1413 -- DateTrack ZAP mode.
1414 --
1415 -- ----------------------------------------------------------------------------
1416 Procedure Get_Zap_Dates
1417 (p_effective_date in date,
1418 p_base_table_name in varchar2,
1419 p_base_key_column in varchar2,
1420 p_base_key_value in number,
1421 p_validation_start_date out nocopy date,
1422 p_validation_end_date out nocopy date) Is
1423 --
1424 l_proc varchar2(72) := g_package||'Get_Zap_Dates';
1425 --
1426 Begin
1427 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1428 p_validation_start_date := Return_Min_Start_Date
1429 (p_base_table_name => p_base_table_name,
1430 p_base_key_column => p_base_key_column,
1431 p_base_key_value => p_base_key_value);
1432 --
1433 p_validation_end_date := Return_Max_End_Date
1434 (p_base_table_name => p_base_table_name,
1435 p_base_key_column => p_base_key_column,
1436 p_base_key_value => p_base_key_value);
1437 --
1438 Hr_Utility.Set_Location('Leaving :'||l_proc, 20);
1439 --
1440 End Get_Zap_Dates;
1441 -- ----------------------------------------------------------------------------
1442 -- |--------------------------< Get_Delete_Dates >----------------------------|
1443 -- ----------------------------------------------------------------------------
1444 --
1445 -- PRIVATE - copied from dt_api
1446 --
1447 -- Description: Returns the validation start and end dates for the
1448 -- DateTrack DELETE mode if allowed.
1449 --
1450 -- ----------------------------------------------------------------------------
1451 Procedure Get_Delete_Dates
1452 (p_effective_date in date,
1453 p_base_table_name in varchar2,
1454 p_base_key_column in varchar2,
1455 p_base_key_value in number,
1456 p_child_table_name1 in varchar2 default hr_api.g_varchar2,
1457 p_child_key_column1 in varchar2 default hr_api.g_varchar2,
1458 p_child_fk_column1 in varchar2 default hr_api.g_varchar2,
1459 p_child_table_name2 in varchar2 default hr_api.g_varchar2,
1460 p_child_key_column2 in varchar2 default hr_api.g_varchar2,
1461 p_child_fk_column2 in varchar2 default hr_api.g_varchar2,
1462 p_enforce_foreign_locking in boolean default true,
1463 p_validation_start_date out nocopy date,
1464 p_validation_end_date out nocopy date) Is
1465 --
1466 l_proc varchar2(72) := g_package||'Get_Delete_Dates';
1467 l_max_end_date date;
1468 l_counter integer; -- Loop counter
1469 --
1470 l_child_table_name varchar2(30);
1471 l_child_key_column varchar2(30);
1472 l_child_fk_column varchar2(30);
1473 --
1474 Begin
1475 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1476 --
1477 If p_enforce_foreign_locking Then
1478 <<Loop1>>
1479 For l_counter In 1..2 Loop
1480 --
1481 -- Set the current working arguments to the corresponding functional
1482 -- argument values
1483 --
1484 If (l_counter = 1) then
1485 l_child_table_name := p_child_table_name1;
1486 l_child_key_column := p_child_key_column1;
1487 l_child_fk_column := p_child_fk_column1;
1488 ElsIf (l_counter = 2) then
1489 l_child_table_name := p_child_table_name2;
1490 l_child_key_column := p_child_key_column2;
1491 l_child_fk_column := p_child_fk_column2;
1492 End If;
1493 --
1494 -- Ensure that all the working child details have been specified
1495 --
1496 If NOT ((nvl(l_child_table_name, hr_api.g_varchar2) =
1497 hr_api.g_varchar2) or
1498 (nvl(l_child_key_column, hr_api.g_varchar2) =
1499 hr_api.g_varchar2)) then
1500 --
1501 --
1502 -- All the child arguments have been specified therefore we must lock
1503 -- the child rows (if they exist).
1504 --
1505 Lck_Child
1506 (p_child_table_name => l_child_table_name,
1507 p_child_key_column => l_child_key_column,
1508 p_parent_key_column => p_base_key_column,
1509 p_parent_key_value => p_base_key_value,
1510 p_child_fk_column => l_child_fk_column,
1511 p_validation_start_date => (p_effective_date + 1));
1512 End If;
1513 End Loop;
1514 End If;
1515 --
1516 -- We must get the maximum effective end date of all the DT rows for the
1517 -- given key.
1518 --
1519 l_max_end_date := Return_Max_End_Date
1520 (p_base_table_name => p_base_table_name,
1521 p_base_key_column => p_base_key_column,
1522 p_base_key_value => p_base_key_value);
1523 --
1524 -- Providing the maximum effective end date is not the same as the current
1525 -- effective date then we must return the validation start and end dates.
1526 -- However, if you attempt to do a datetrack delete where the session date is
1527 -- the same as your maximum date then we must error.
1528 --
1529 If (p_effective_date <> l_max_end_date) then
1530 p_validation_start_date := p_effective_date + 1;
1531 p_validation_end_date := l_max_end_date;
1532 Else
1533 --
1534 -- We cannot perform a DateTrack delete operation where the effective date
1535 -- is the same as the maximum effective end date.
1536 --
1537 hr_utility.set_message(801, 'HR_7185_DT_DEL_NOT_ALLOWED');
1538 hr_utility.raise_error;
1539 End If;
1540 Hr_Utility.Set_Location('Leaving :'||l_proc, 10);
1541 --
1542 End Get_Delete_Dates;
1543 -- ----------------------------------------------------------------------------
1544 -- |-----------------------< Get_Future_Change_Dates >------------------------|
1545 -- ----------------------------------------------------------------------------
1546 --
1547 -- PRIVATE
1548 --
1549 -- Description: Returns the validation start and end dates for the
1550 -- DateTrack FUTURE_CHANGE mode if allowed.
1551 --
1552 -- ----------------------------------------------------------------------------
1553 Procedure Get_Future_Change_Dates
1554 (p_effective_date in date,
1555 p_base_table_name in varchar2,
1556 p_base_key_column in varchar2,
1557 p_base_key_value in number,
1558 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
1559 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
1560 p_parent_key_value1 in number default hr_api.g_number,
1561 /*
1562 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
1563 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
1564 p_parent_key_value2 in number default hr_api.g_number,
1565 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
1566 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
1567 p_parent_key_value3 in number default hr_api.g_number,
1568 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
1569 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
1570 p_parent_key_value4 in number default hr_api.g_number,
1571 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
1572 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
1573 p_parent_key_value5 in number default hr_api.g_number,
1574 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
1575 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
1576 p_parent_key_value6 in number default hr_api.g_number,
1577 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
1578 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
1579 p_parent_key_value7 in number default hr_api.g_number,
1580 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
1581 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
1582 p_parent_key_value8 in number default hr_api.g_number,
1583 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
1584 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
1585 p_parent_key_value9 in number default hr_api.g_number,
1586 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
1587 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
1588 p_parent_key_value10 in number default hr_api.g_number,
1589 */
1590 p_validation_start_date out nocopy date,
1591 p_validation_end_date out nocopy date) Is
1592 --
1593 l_proc varchar2(72) := g_package||'Get_Future_Change_Dates';
1594 l_effective_start_date date; -- Holds current effective start date
1595 l_effective_end_date date; -- Holds current effective end date
1596 l_validation_end_date date;
1597 --
1598 Begin
1599 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1600 Return_Effective_Dates
1601 (p_effective_date => p_effective_date,
1602 p_base_table_name => p_base_table_name,
1603 p_base_key_column => p_base_key_column,
1604 p_base_key_value => p_base_key_value,
1605 p_effective_start_date => l_effective_start_date,
1606 p_effective_end_date => l_effective_end_date);
1607 --
1608 -- Providing the current effective end date is not the end of time
1609 -- then we must set the validation dates
1610 --
1611 If (l_effective_end_date <> HR_Api.g_eot) then
1612 --
1613 p_validation_start_date := l_effective_end_date + 1;
1614 l_validation_end_date :=
1615 Return_Min_Parent_End_Date
1616 (p_effective_date => p_effective_date,
1617 p_parent_table_name1 => p_parent_table_name1,
1618 p_parent_key_column1 => p_parent_key_column1,
1619 p_parent_key_value1 => p_parent_key_value1);
1620 /*
1621 p_parent_table_name2 => p_parent_table_name2,
1622 p_parent_key_column2 => p_parent_key_column2,
1623 p_parent_key_value2 => p_parent_key_value2,
1624 p_parent_table_name3 => p_parent_table_name3,
1625 p_parent_key_column3 => p_parent_key_column3,
1626 p_parent_key_value3 => p_parent_key_value3,
1627 p_parent_table_name4 => p_parent_table_name4,
1628 p_parent_key_column4 => p_parent_key_column4,
1629 p_parent_key_value4 => p_parent_key_value4,
1630 p_parent_table_name5 => p_parent_table_name5,
1631 p_parent_key_column5 => p_parent_key_column5,
1632 p_parent_key_value5 => p_parent_key_value5,
1633 p_parent_table_name6 => p_parent_table_name6,
1634 p_parent_key_column6 => p_parent_key_column6,
1635 p_parent_key_value6 => p_parent_key_value6,
1636 p_parent_table_name7 => p_parent_table_name7,
1637 p_parent_key_column7 => p_parent_key_column7,
1638 p_parent_key_value7 => p_parent_key_value7,
1639 p_parent_table_name8 => p_parent_table_name8,
1640 p_parent_key_column8 => p_parent_key_column8,
1641 p_parent_key_value8 => p_parent_key_value8,
1642 p_parent_table_name9 => p_parent_table_name9,
1643 p_parent_key_column9 => p_parent_key_column9,
1644 p_parent_key_value9 => p_parent_key_value9,
1645 p_parent_table_name10 => p_parent_table_name10,
1646 p_parent_key_column10 => p_parent_key_column10,
1647 p_parent_key_value10 => p_parent_key_value10);
1648 */
1649 --
1650 -- If the validation end date is set to the current effective end date
1651 -- then we must error as we cannot extend the end date of the current
1652 -- row
1653 --
1654 If (l_validation_end_date <= l_effective_end_date) then
1655 hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1656 hr_utility.set_message_token('DT_MODE', ' future changes');
1657 hr_utility.raise_error;
1658 Else
1659 p_validation_end_date := l_validation_end_date;
1660 End If;
1661 Else
1662 --
1663 -- The current effective end date is alreay the end of time therefore
1664 -- we cannot extend the end date
1665 --
1666 hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1667 hr_utility.raise_error;
1668 End If;
1669 --
1670 Hr_Utility.Set_Location(' Leaving:'||l_proc, 15);
1671 --
1672 End Get_Future_Change_Dates;
1673 -- ----------------------------------------------------------------------------
1674 -- |--------------------< Get_Delete_Next_Change_Dates >----------------------|
1675 -- ----------------------------------------------------------------------------
1676 --
1677 -- PRIVATE
1678 --
1679 -- Description: Returns the validation start and end dates for the
1680 -- DateTrack FUTURE_CHANGE mode if allowed.
1681 --
1682 -- ----------------------------------------------------------------------------
1683 Procedure Get_Delete_Next_Change_Dates
1684 (p_effective_date in date,
1685 p_base_table_name in varchar2,
1686 p_base_key_column in varchar2,
1687 p_base_key_value in number,
1688 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
1689 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
1690 p_parent_key_value1 in number default hr_api.g_number,
1691 /*
1692 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
1693 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
1694 p_parent_key_value2 in number default hr_api.g_number,
1695 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
1696 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
1697 p_parent_key_value3 in number default hr_api.g_number,
1698 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
1699 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
1700 p_parent_key_value4 in number default hr_api.g_number,
1701 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
1702 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
1703 p_parent_key_value5 in number default hr_api.g_number,
1704 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
1705 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
1706 p_parent_key_value6 in number default hr_api.g_number,
1707 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
1708 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
1709 p_parent_key_value7 in number default hr_api.g_number,
1710 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
1711 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
1712 p_parent_key_value8 in number default hr_api.g_number,
1713 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
1714 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
1715 p_parent_key_value9 in number default hr_api.g_number,
1716 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
1717 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
1718 p_parent_key_value10 in number default hr_api.g_number,
1719 */
1720 p_validation_start_date out nocopy date,
1721 p_validation_end_date out nocopy date) Is
1722 --
1723 l_proc varchar2(72) := g_package||'Get_Delete_Next_Change_Dates';
1724 l_effective_start_date date; -- Holds current effective start date
1725 l_effective_end_date date; -- Holds current effective end date
1726 l_validation_start_date date;
1727 l_validation_end_date date;
1728 l_dummy_date date; -- Date not used
1729 l_future_effective_end_date date; -- Holds the end date of next row
1730 l_min_parent_end_date date; -- Holds the min parental end date
1731 --
1732 Begin
1733 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1734 Return_Effective_Dates
1735 (p_effective_date => p_effective_date,
1736 p_base_table_name => p_base_table_name,
1737 p_base_key_column => p_base_key_column,
1738 p_base_key_value => p_base_key_value,
1739 p_effective_start_date => l_effective_start_date,
1740 p_effective_end_date => l_effective_end_date);
1741 --
1742 -- Providing the current effective end date is not the end of time
1743 -- then we must set the validation dates
1744 --
1745 If (l_effective_end_date <> HR_Api.g_eot) then
1746 --
1747 l_validation_start_date := l_effective_end_date + 1;
1748 p_validation_start_date := l_validation_start_date;
1749 --
1750 -- To determine the validation end date we must take the minimum date
1751 -- from the following three possible dates:
1752 -- 1: Minimum parent entity entity end date
1753 -- 2: If future rows exist then the effective end date of the next row
1754 -- 3: If no future rows exist then the end of time
1755 --
1756 l_min_parent_end_date :=
1757 Return_Min_Parent_End_Date
1758 (p_effective_date => p_effective_date,
1759 p_parent_table_name1 => p_parent_table_name1,
1760 p_parent_key_column1 => p_parent_key_column1,
1761 p_parent_key_value1 => p_parent_key_value1);
1762 /*
1763 p_parent_table_name2 => p_parent_table_name2,
1764 p_parent_key_column2 => p_parent_key_column2,
1765 p_parent_key_value2 => p_parent_key_value2,
1766 p_parent_table_name3 => p_parent_table_name3,
1767 p_parent_key_column3 => p_parent_key_column3,
1768 p_parent_key_value3 => p_parent_key_value3,
1769 p_parent_table_name4 => p_parent_table_name4,
1770 p_parent_key_column4 => p_parent_key_column4,
1771 p_parent_key_value4 => p_parent_key_value4,
1772 p_parent_table_name5 => p_parent_table_name5,
1773 p_parent_key_column5 => p_parent_key_column5,
1774 p_parent_key_value5 => p_parent_key_value5,
1775 p_parent_table_name6 => p_parent_table_name6,
1776 p_parent_key_column6 => p_parent_key_column6,
1777 p_parent_key_value6 => p_parent_key_value6,
1778 p_parent_table_name7 => p_parent_table_name7,
1779 p_parent_key_column7 => p_parent_key_column7,
1780 p_parent_key_value7 => p_parent_key_value7,
1781 p_parent_table_name8 => p_parent_table_name8,
1782 p_parent_key_column8 => p_parent_key_column8,
1783 p_parent_key_value8 => p_parent_key_value8,
1784 p_parent_table_name9 => p_parent_table_name9,
1785 p_parent_key_column9 => p_parent_key_column9,
1786 p_parent_key_value9 => p_parent_key_value9,
1787 p_parent_table_name10 => p_parent_table_name10,
1788 p_parent_key_column10 => p_parent_key_column10,
1789 p_parent_key_value10 => p_parent_key_value10);
1790 */
1791 --
1792 If (Future_Rows_Exist
1793 (p_effective_date => p_effective_date,
1794 p_base_table_name => p_base_table_name,
1795 p_base_key_column => p_base_key_column,
1796 p_base_key_value => p_base_key_value)) then
1797 --
1798 Return_Effective_Dates
1799 (p_effective_date => l_validation_start_date,
1800 p_base_table_name => p_base_table_name,
1801 p_base_key_column => p_base_key_column,
1802 p_base_key_value => p_base_key_value,
1803 p_effective_start_date => l_dummy_date,
1804 p_effective_end_date => l_future_effective_end_date);
1805 --
1806 l_validation_end_date :=
1807 least(l_min_parent_end_date, l_future_effective_end_date);
1808 Else
1809 --
1810 -- We only need to set the validation end date to the parent end date
1811 -- because if no parent end dates have been set then we always return
1812 -- the end of time (even if no parental details are specified)
1813 --
1814 l_validation_end_date := l_min_parent_end_date;
1815 End If;
1816 --
1817 -- If the validation end date is set to the current effective end date
1818 -- then we must error as we cannot extend the end date of the current
1819 -- row
1820 --
1821 If (l_validation_end_date <= l_effective_end_date) then
1822 hr_utility.set_message(801, 'HR_7187_DT_CANNOT_EXTEND_END');
1823 hr_utility.set_message_token('DT_MODE', ' delete next change');
1824 hr_utility.raise_error;
1825 Else
1826 p_validation_end_date := l_validation_end_date;
1827 End If;
1828 Else
1829 --
1830 -- The current effective end date is alreay the end of time therefore
1831 -- we cannot extend the end date
1832 --
1833 hr_utility.set_message(801, 'HR_7188_DT_DATE_IS_EOT');
1834 hr_utility.raise_error;
1835 End If;
1836 --
1837 Hr_Utility.Set_Location(' Leaving:'||l_proc, 25);
1838 --
1839 End Get_Delete_Next_Change_Dates;
1840 -- ---------------------------------------------------------------------------
1841 -- |-------------------------< validate_dt_mode >----------------------------|
1842 -- ---------------------------------------------------------------------------
1843 --
1844 -- PRIVATE - copied from package dt_api. 20 procedures/functions have been
1845 -- dopied from dt_api, to get around the invalid column error caused by the
1846 -- assumption in dt_api.lck_child, that a child table's key will be the same
1847 -- name as the parent table's foreign key.
1848 --
1849 -- As these 20 functions are copied for use only with pay_run_type_usages_f,
1850 -- some of the code has been changed slightly to use hard coded values.
1851 --
1852 -- Description: Validates and returns the validation start and end dates for
1853 -- the DateTrack mode provided.
1854 -- Locking is also enforced within this procedure.
1855 -- The argument p_enforce_foreign_locking determines if for the
1856 -- correct DT mode (INSERT or DELETE) parental or child
1857 -- foreign key entities should be locked. If this value if set to
1858 -- false this procedure will not perform any foreign lockng
1859 -- and it is expected to be handled by the calling process
1860 -- (this is useful if a different method of locking is required
1861 -- where the row exclusive locking mechanisms is too
1862 -- restrictive).
1863 --
1864 -- Locking Processing:
1865 --
1866 -- 1. Entity range row locking:
1867 -- Mode Lock Comments
1868 -- -------------- ---- ---------------------------------
1869 -- INSERT N No rows exists at this point
1870 -- UPDATE N Current row already locked
1871 -- CORRECTION N Current row already locked
1872 -- UPDATE_OVERRIDE Y Have to lock future rows
1873 -- UPDATE_CHANGE_INSERT N Current row already locked
1874 -- DELETE Y Have to lock future rows
1875 -- FUTURE_CHANGE Y Have to lock future rows
1876 -- DELETE_NEXT_CHANGE Y Have to lock future rows
1877 -- We always lock all future rows
1878 -- too ensure consistency. This
1879 -- means that we may over-lock some
1880 -- future rows unnessarily.
1881 -- ZAP Y Have to lock all rows
1882 --
1883 -- 2. Insert
1884 -- Parental rows are locked provided the argument
1885 -- p_enforce_foreign_locking has been set to TRUE.
1886 --
1887 -- 3. Delete
1888 -- Child rows are locked provided the argument
1889 -- p_enforce_foreign_locking has been set to TRUE.
1890 --
1891 -- ----------------------------------------------------------------------------
1892 Procedure Validate_DT_Mode
1893 (p_datetrack_mode in varchar2,
1894 p_effective_date in date,
1895 p_base_table_name in varchar2,
1896 p_base_key_column in varchar2,
1897 p_base_key_value in number,
1898 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
1899 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
1900 p_parent_key_value1 in number default hr_api.g_number,
1901 p_child_table_name1 in varchar2 default hr_api.g_varchar2,
1902 p_child_key_column1 in varchar2 default hr_api.g_varchar2,
1903 p_child_fk_column1 in varchar2 default hr_api.g_varchar2,
1904 p_child_table_name2 in varchar2 default hr_api.g_varchar2,
1905 p_child_key_column2 in varchar2 default hr_api.g_varchar2,
1906 p_child_fk_column2 in varchar2 default hr_api.g_varchar2,
1907 p_enforce_foreign_locking in boolean default true,
1908 p_validation_start_date out nocopy date,
1909 p_validation_end_date out nocopy date) Is
1910 --
1911 l_proc varchar2(72) := g_package||'Validate_DT_Mode';
1912 l_datetrack_mode varchar2(30);
1913 --
1914 Begin
1915 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1916 l_datetrack_mode := upper(p_datetrack_mode);
1917 --
1918 Effective_Date_Valid(p_effective_date => p_effective_date);
1919 --
1920 -- Ensure that all the mandatory arguments are not null
1921 --
1922 hr_api.mandatory_arg_error(p_api_name => l_proc,
1923 p_argument => 'p_datetrack_mode',
1924 p_argument_value => p_datetrack_mode);
1925 hr_api.mandatory_arg_error(p_api_name => l_proc,
1926 p_argument => 'p_base_table_name',
1927 p_argument_value => p_base_table_name);
1928 hr_api.mandatory_arg_error(p_api_name => l_proc,
1929 p_argument => 'p_base_key_column',
1930 p_argument_value => p_base_key_column);
1931 --
1932 -- Determine if any entity range row locking is required
1933 --
1934 If (l_datetrack_mode = hr_api.g_update_override or
1935 l_datetrack_mode = hr_api.g_delete or
1936 l_datetrack_mode = hr_api.g_future_change or
1937 l_datetrack_mode = hr_api.g_delete_next_change) then
1938 --
1939 -- Perform the entity range row locking processing
1940 --
1941 Lck_Future_Rows
1942 (p_effective_date => p_effective_date,
1943 p_table_name => p_base_table_name,
1944 p_key_column => p_base_key_column,
1945 p_key_value => p_base_key_value);
1946 --
1947 ElsIf l_datetrack_mode = hr_api.g_zap then
1948 -- As we are performing a ZAP we must lock all rows from
1949 -- the start of time
1950 Lck_Future_Rows
1951 (p_effective_date => hr_api.g_sot,
1952 p_table_name => p_base_table_name,
1953 p_key_column => p_base_key_column,
1954 p_key_value => p_base_key_value);
1955 --
1956 End If;
1957 --
1958 If (l_datetrack_mode = hr_api.g_insert) then
1959 --
1960 Get_Insert_Dates
1961 (p_effective_date => p_effective_date,
1962 p_base_table_name => p_base_table_name,
1963 p_base_key_column => p_base_key_column,
1964 p_base_key_value => p_base_key_value,
1965 p_parent_table_name1 => p_parent_table_name1,
1966 p_parent_key_column1 => p_parent_key_column1,
1967 p_parent_key_value1 => p_parent_key_value1,
1968 /*
1969 p_parent_table_name2 => p_parent_table_name2,
1970 p_parent_key_column2 => p_parent_key_column2,
1971 p_parent_key_value2 => p_parent_key_value2,
1972 p_parent_table_name3 => p_parent_table_name3,
1973 p_parent_key_column3 => p_parent_key_column3,
1974 p_parent_key_value3 => p_parent_key_value3,
1975 p_parent_table_name4 => p_parent_table_name4,
1976 p_parent_key_column4 => p_parent_key_column4,
1977 p_parent_key_value4 => p_parent_key_value4,
1978 p_parent_table_name5 => p_parent_table_name5,
1979 p_parent_key_column5 => p_parent_key_column5,
1980 p_parent_key_value5 => p_parent_key_value5,
1981 p_parent_table_name6 => p_parent_table_name6,
1982 p_parent_key_column6 => p_parent_key_column6,
1983 p_parent_key_value6 => p_parent_key_value6,
1984 p_parent_table_name7 => p_parent_table_name7,
1985 p_parent_key_column7 => p_parent_key_column7,
1986 p_parent_key_value7 => p_parent_key_value7,
1987 p_parent_table_name8 => p_parent_table_name8,
1988 p_parent_key_column8 => p_parent_key_column8,
1989 p_parent_key_value8 => p_parent_key_value8,
1990 p_parent_table_name9 => p_parent_table_name9,
1991 p_parent_key_column9 => p_parent_key_column9,
1992 p_parent_key_value9 => p_parent_key_value9,
1993 p_parent_table_name10 => p_parent_table_name10,
1994 p_parent_key_column10 => p_parent_key_column10,
1995 p_parent_key_value10 => p_parent_key_value10,
1996 */
1997 p_enforce_foreign_locking => p_enforce_foreign_locking,
1998 p_validation_start_date => p_validation_start_date,
1999 p_validation_end_date => p_validation_end_date);
2000 --
2001 ElsIf (l_datetrack_mode = hr_api.g_correction) then
2002 --
2003 Get_Correction_Dates
2004 (p_effective_date => p_effective_date,
2005 p_base_table_name => p_base_table_name,
2006 p_base_key_column => p_base_key_column,
2007 p_base_key_value => p_base_key_value,
2008 p_validation_start_date => p_validation_start_date,
2009 p_validation_end_date => p_validation_end_date);
2010 --
2011 ElsIf (l_datetrack_mode = hr_api.g_update) then
2012 --
2013 Get_Update_Dates
2014 (p_effective_date => p_effective_date,
2015 p_base_table_name => p_base_table_name,
2016 p_base_key_column => p_base_key_column,
2017 p_base_key_value => p_base_key_value,
2018 p_validation_start_date => p_validation_start_date,
2019 p_validation_end_date => p_validation_end_date);
2020 --
2021 ElsIf (l_datetrack_mode = hr_api.g_update_override) then
2022 --
2023 Get_Update_Override_Dates
2024 (p_effective_date => p_effective_date,
2025 p_base_table_name => p_base_table_name,
2026 p_base_key_column => p_base_key_column,
2027 p_base_key_value => p_base_key_value,
2028 p_validation_start_date => p_validation_start_date,
2029 p_validation_end_date => p_validation_end_date);
2030 --
2031 ElsIf (l_datetrack_mode = hr_api.g_update_change_insert) then
2032 --
2033 Get_Update_Change_Insert_Dates
2034 (p_effective_date => p_effective_date,
2035 p_base_table_name => p_base_table_name,
2036 p_base_key_column => p_base_key_column,
2037 p_base_key_value => p_base_key_value,
2038 p_validation_start_date => p_validation_start_date,
2039 p_validation_end_date => p_validation_end_date);
2040 --
2041 ElsIf (l_datetrack_mode = hr_api.g_zap) then
2042 --
2043 Get_Zap_Dates
2044 (p_effective_date => p_effective_date,
2045 p_base_table_name => p_base_table_name,
2046 p_base_key_column => p_base_key_column,
2047 p_base_key_value => p_base_key_value,
2048 p_validation_start_date => p_validation_start_date,
2049 p_validation_end_date => p_validation_end_date);
2050 --
2051 ElsIf (l_datetrack_mode = hr_api.g_delete) then
2052 -- if (l_datetrack_mode = hr_api.g_delete) then
2053 --
2054 Get_Delete_Dates
2055 (p_effective_date => p_effective_date,
2056 p_base_table_name => p_base_table_name,
2057 p_base_key_column => p_base_key_column,
2058 p_base_key_value => p_base_key_value,
2059 p_child_table_name1 => p_child_table_name1,
2060 p_child_key_column1 => p_child_key_column1,
2061 p_child_fk_column1 => p_child_fk_column1,
2062 p_child_table_name2 => p_child_table_name2,
2063 p_child_key_column2 => p_child_key_column2,
2064 p_child_fk_column2 => p_child_fk_column2,
2065 p_enforce_foreign_locking => p_enforce_foreign_locking,
2066 p_validation_start_date => p_validation_start_date,
2067 p_validation_end_date => p_validation_end_date);
2068 --
2069 ElsIf (l_datetrack_mode = hr_api.g_future_change) then
2070 --
2071 Get_Future_Change_Dates
2072 (p_effective_date => p_effective_date,
2073 p_base_table_name => p_base_table_name,
2074 p_base_key_column => p_base_key_column,
2075 p_base_key_value => p_base_key_value,
2076 p_parent_table_name1 => p_parent_table_name1,
2077 p_parent_key_column1 => p_parent_key_column1,
2078 p_parent_key_value1 => p_parent_key_value1,
2079 /*
2080 p_parent_table_name2 => p_parent_table_name2,
2081 p_parent_key_column2 => p_parent_key_column2,
2082 p_parent_key_value2 => p_parent_key_value2,
2083 p_parent_table_name3 => p_parent_table_name3,
2084 p_parent_key_column3 => p_parent_key_column3,
2085 p_parent_key_value3 => p_parent_key_value3,
2086 p_parent_table_name4 => p_parent_table_name4,
2087 p_parent_key_column4 => p_parent_key_column4,
2088 p_parent_key_value4 => p_parent_key_value4,
2089 p_parent_table_name5 => p_parent_table_name5,
2090 p_parent_key_column5 => p_parent_key_column5,
2091 p_parent_key_value5 => p_parent_key_value5,
2092 p_parent_table_name6 => p_parent_table_name6,
2093 p_parent_key_column6 => p_parent_key_column6,
2094 p_parent_key_value6 => p_parent_key_value6,
2095 p_parent_table_name7 => p_parent_table_name7,
2096 p_parent_key_column7 => p_parent_key_column7,
2097 p_parent_key_value7 => p_parent_key_value7,
2098 p_parent_table_name8 => p_parent_table_name8,
2099 p_parent_key_column8 => p_parent_key_column8,
2100 p_parent_key_value8 => p_parent_key_value8,
2101 p_parent_table_name9 => p_parent_table_name9,
2102 p_parent_key_column9 => p_parent_key_column9,
2103 p_parent_key_value9 => p_parent_key_value9,
2104 p_parent_table_name10 => p_parent_table_name10,
2105 p_parent_key_column10 => p_parent_key_column10,
2106 p_parent_key_value10 => p_parent_key_value10,
2107 */
2108 p_validation_start_date => p_validation_start_date,
2109 p_validation_end_date => p_validation_end_date);
2110 --
2111 ElsIf (l_datetrack_mode = hr_api.g_delete_next_change) then
2112 --
2113 Get_Delete_Next_Change_Dates
2114 (p_effective_date => p_effective_date,
2115 p_base_table_name => p_base_table_name,
2116 p_base_key_column => p_base_key_column,
2117 p_base_key_value => p_base_key_value,
2118 p_parent_table_name1 => p_parent_table_name1,
2119 p_parent_key_column1 => p_parent_key_column1,
2120 p_parent_key_value1 => p_parent_key_value1,
2121 /*
2122 p_parent_table_name2 => p_parent_table_name2,
2123 p_parent_key_column2 => p_parent_key_column2,
2124 p_parent_key_value2 => p_parent_key_value2,
2125 p_parent_table_name3 => p_parent_table_name3,
2126 p_parent_key_column3 => p_parent_key_column3,
2127 p_parent_key_value3 => p_parent_key_value3,
2128 p_parent_table_name4 => p_parent_table_name4,
2129 p_parent_key_column4 => p_parent_key_column4,
2130 p_parent_key_value4 => p_parent_key_value4,
2131 p_parent_table_name5 => p_parent_table_name5,
2132 p_parent_key_column5 => p_parent_key_column5,
2133 p_parent_key_value5 => p_parent_key_value5,
2134 p_parent_table_name6 => p_parent_table_name6,
2135 p_parent_key_column6 => p_parent_key_column6,
2136 p_parent_key_value6 => p_parent_key_value6,
2137 p_parent_table_name7 => p_parent_table_name7,
2138 p_parent_key_column7 => p_parent_key_column7,
2139 p_parent_key_value7 => p_parent_key_value7,
2140 p_parent_table_name8 => p_parent_table_name8,
2141 p_parent_key_column8 => p_parent_key_column8,
2142 p_parent_key_value8 => p_parent_key_value8,
2143 p_parent_table_name9 => p_parent_table_name9,
2144 p_parent_key_column9 => p_parent_key_column9,
2145 p_parent_key_value9 => p_parent_key_value9,
2146 p_parent_table_name10 => p_parent_table_name10,
2147 p_parent_key_column10 => p_parent_key_column10,
2148 p_parent_key_value10 => p_parent_key_value10,
2149 */
2150 p_validation_start_date => p_validation_start_date,
2151 p_validation_end_date => p_validation_end_date);
2152 --
2153 Else
2154 hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
2155 hr_utility.set_message_token('DT_MODE', l_datetrack_mode);
2156 hr_utility.raise_error;
2157 End If;
2158 --
2159 Hr_Utility.Set_Location(' Leaving:'||l_proc, 55);
2160 --
2161 End Validate_DT_Mode;
2162 -- ----------------------------------------------------------------------------
2163 -- |---------------------------< find_dt_upd_modes >--------------------------|
2164 -- ----------------------------------------------------------------------------
2165 Procedure find_dt_upd_modes
2166 (p_effective_date in date
2167 ,p_base_key_value in number
2168 ,p_correction out nocopy boolean
2169 ,p_update out nocopy boolean
2170 ,p_update_override out nocopy boolean
2171 ,p_update_change_insert out nocopy boolean
2172 ) is
2173 --
2174 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
2175 --
2176 Begin
2177 hr_utility.set_location('Entering:'||l_proc, 5);
2178 --
2179 -- Call the corresponding datetrack api
2180 --
2181 dt_api.find_dt_upd_modes
2182 (p_effective_date => p_effective_date
2183 ,p_base_table_name => 'pay_run_types_f'
2184 ,p_base_key_column => 'run_type_id'
2185 ,p_base_key_value => p_base_key_value
2186 ,p_correction => p_correction
2187 ,p_update => p_update
2188 ,p_update_override => p_update_override
2189 ,p_update_change_insert => p_update_change_insert
2190 );
2191 --
2192 -- As run_type_name and shortname are the only updatable columns in the table,
2193 -- and these are limited to update mode of 'CORRECTION', following the call
2194 -- to dt_api.find_dt_upd_modes, if any of the parameters other than
2195 -- p_correction are returned as 'true' then hardcode them to false.
2196 -- NOTE: if columns are added in the future that need to have other update
2197 -- modes, then this will need to be changed.
2198 -- RET 12-DEC-2001 Note: run_type_name is now non-updatable, due to it being
2199 -- the key used in lct for uploading and downloading run type data.
2200 --
2201 if p_update then
2202 p_update := false;
2203 end if;
2204 if p_update_override then
2205 p_update_override := false;
2206 end if;
2207 if p_update_change_insert then
2208 p_update_change_insert := false;
2209 end if;
2210 --
2211 hr_utility.set_location(' Leaving:'||l_proc, 10);
2212 End find_dt_upd_modes;
2213 --
2214 -- ----------------------------------------------------------------------------
2218 (p_effective_date in date
2215 -- |---------------------------< find_dt_del_modes >--------------------------|
2216 -- ----------------------------------------------------------------------------
2217 Procedure find_dt_del_modes
2219 ,p_base_key_value in number
2220 ,p_zap out nocopy boolean
2221 ,p_delete out nocopy boolean
2222 ,p_future_change out nocopy boolean
2223 ,p_delete_next_change out nocopy boolean
2224 ) is
2225 --
2226 l_proc varchar2(72) := g_package||'find_dt_del_modes';
2227 --
2228 --
2229 Begin
2230 hr_utility.set_location('Entering:'||l_proc, 5);
2231 --
2232 -- Call the corresponding datetrack api
2233 --
2234 dt_api.find_dt_del_modes
2235 (p_effective_date => p_effective_date
2236 ,p_base_table_name => 'pay_run_types_f'
2237 ,p_base_key_column => 'run_type_id'
2238 ,p_base_key_value => p_base_key_value
2239 ,p_zap => p_zap
2240 ,p_delete => p_delete
2241 ,p_future_change => p_future_change
2242 ,p_delete_next_change => p_delete_next_change
2243 );
2244 --
2245 hr_utility.set_location(' Leaving:'||l_proc, 10);
2246 End find_dt_del_modes;
2247 --
2248 -- ----------------------------------------------------------------------------
2249 -- |-----------------------< upd_effective_end_date >-------------------------|
2250 -- ----------------------------------------------------------------------------
2251 Procedure upd_effective_end_date
2252 (p_effective_date in date
2253 ,p_base_key_value in number
2254 ,p_new_effective_end_date in date
2255 ,p_validation_start_date in date
2256 ,p_validation_end_date in date
2257 ,p_object_version_number out nocopy number
2258 ) is
2259 --
2260 l_proc varchar2(72) := g_package||'upd_effective_end_date';
2261 l_object_version_number number;
2262 --
2263 Begin
2264 hr_utility.set_location('Entering:'||l_proc, 5);
2265 --
2266 -- Because we are updating a row we must get the next object
2267 -- version number.
2268 --
2269 l_object_version_number :=
2270 dt_api.get_object_version_number
2271 (p_base_table_name => 'pay_run_types_f'
2272 ,p_base_key_column => 'run_type_id'
2273 ,p_base_key_value => p_base_key_value
2274 );
2275 --
2276 hr_utility.set_location(l_proc, 10);
2277 --
2278 -- Update the specified datetrack row setting the effective
2279 -- end date to the specified new effective end date.
2280 --
2281 update pay_run_types_f t
2282 set t.effective_end_date = p_new_effective_end_date
2283 , t.object_version_number = l_object_version_number
2284 where t.run_type_id = p_base_key_value
2285 and p_effective_date
2286 between t.effective_start_date and t.effective_end_date;
2287 --
2288 p_object_version_number := l_object_version_number;
2289 hr_utility.set_location(' Leaving:'||l_proc, 15);
2290 --
2291 End upd_effective_end_date;
2292 --
2293 -- ----------------------------------------------------------------------------
2294 -- |---------------------------------< lck >----------------------------------|
2295 -- ----------------------------------------------------------------------------
2296 Procedure lck
2297 (p_effective_date in date
2298 ,p_datetrack_mode in varchar2
2299 ,p_run_type_id in number
2300 ,p_object_version_number in number
2301 ,p_validation_start_date out nocopy date
2302 ,p_validation_end_date out nocopy date
2303 ) is
2304 --
2305 l_proc varchar2(72) := g_package||'lck';
2306 l_validation_start_date date;
2307 l_validation_end_date date;
2308 l_argument varchar2(30);
2309 --
2310 -- Cursor C_Sel1 selects the current locked row as of session date
2311 -- ensuring that the object version numbers match.
2312 --
2313 Cursor C_Sel1 is
2314 select
2315 run_type_id
2316 ,run_type_name
2317 ,run_method
2318 ,effective_start_date
2319 ,effective_end_date
2320 ,business_group_id
2321 ,legislation_code
2322 ,shortname
2323 ,srs_flag
2324 ,run_information_category
2325 ,run_information1
2326 ,run_information2
2327 ,run_information3
2328 ,run_information4
2329 ,run_information5
2330 ,run_information6
2331 ,run_information7
2332 ,run_information8
2333 ,run_information9
2334 ,run_information10
2335 ,run_information11
2336 ,run_information12
2337 ,run_information13
2338 ,run_information14
2339 ,run_information15
2340 ,run_information16
2341 ,run_information17
2342 ,run_information18
2343 ,run_information19
2344 ,run_information20
2345 ,run_information21
2346 ,run_information22
2347 ,run_information23
2348 ,run_information24
2349 ,run_information25
2350 ,run_information26
2351 ,run_information27
2352 ,run_information28
2353 ,run_information29
2354 ,run_information30
2355 ,object_version_number
2356 from pay_run_types_f
2357 where run_type_id = p_run_type_id
2358 and p_effective_date
2359 between effective_start_date and effective_end_date
2360 for update nowait;
2361 --
2362 --
2363 --
2364 Begin
2368 --
2365 hr_utility.set_location('Entering:'||l_proc, 5);
2366 --
2367 -- Ensure that all the mandatory arguments are not null
2369 hr_api.mandatory_arg_error(p_api_name => l_proc
2370 ,p_argument => 'effective_date'
2371 ,p_argument_value => p_effective_date
2372 );
2373 --
2374 hr_api.mandatory_arg_error(p_api_name => l_proc
2375 ,p_argument => 'datetrack_mode'
2376 ,p_argument_value => p_datetrack_mode
2377 );
2378 --
2379 hr_api.mandatory_arg_error(p_api_name => l_proc
2380 ,p_argument => 'run_type_id'
2381 ,p_argument_value => p_run_type_id
2382 );
2383 --
2384 hr_api.mandatory_arg_error(p_api_name => l_proc
2385 ,p_argument => 'object_version_number'
2386 ,p_argument_value => p_object_version_number
2387 );
2388 --
2389 -- Check to ensure the datetrack mode is not INSERT.
2390 --
2391 If (p_datetrack_mode <> hr_api.g_insert) then
2392 --
2393 -- We must select and lock the current row.
2394 --
2395 Open C_Sel1;
2396 Fetch C_Sel1 Into pay_prt_shd.g_old_rec;
2397 If C_Sel1%notfound then
2398 Close C_Sel1;
2399 --
2400 -- The primary key is invalid therefore we must error
2401 --
2402 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
2403 fnd_message.raise_error;
2404 End If;
2405 Close C_Sel1;
2406 If (p_object_version_number
2407 <> pay_prt_shd.g_old_rec.object_version_number) Then
2408 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
2409 fnd_message.raise_error;
2410 End If;
2411 --
2412 --
2413 -- Validate the datetrack mode mode getting the validation start
2414 -- and end dates for the specified datetrack operation.
2415 --
2416 dt_api.validate_dt_mode
2417 (p_effective_date => p_effective_date
2418 ,p_datetrack_mode => p_datetrack_mode
2419 ,p_base_table_name => 'pay_run_types_f'
2420 ,p_base_key_column => 'run_type_id'
2421 ,p_base_key_value => p_run_type_id
2422 ,p_child_table_name1 => 'pay_element_type_usages_f'
2423 ,p_child_key_column1 => 'element_type_usage_id'
2424 ,p_child_table_name2 => 'pay_run_type_org_methods_f'
2425 ,p_child_key_column2 => 'run_type_org_method_id'
2426 ,p_enforce_foreign_locking => true
2427 ,p_validation_start_date => l_validation_start_date
2428 ,p_validation_end_date => l_validation_end_date
2429 );
2430 --
2431 -- now call local validate_dt_mode for pay_run_type_usages_f
2432 -- Note: dt_api.validate_dt_mode cannot be used as it assumes that the
2433 -- child table key column has the same name as the parent table fk.
2434 --
2435 validate_dt_mode
2436 (p_effective_date => p_effective_date
2437 ,p_datetrack_mode => p_datetrack_mode
2438 ,p_base_table_name => 'pay_run_types_f'
2439 ,p_base_key_column => 'run_type_id'
2440 ,p_base_key_value => p_run_type_id
2441 ,p_child_table_name1 => 'pay_run_type_usages_f'
2442 ,p_child_key_column1 => 'run_type_usage_id'
2443 ,p_child_fk_column1 => 'parent_run_type_id'
2444 ,p_child_table_name2 => 'pay_run_type_usages_f'
2445 ,p_child_key_column2 => 'run_type_usage_id'
2446 ,p_child_fk_column2 => 'child_run_type_id'
2447 ,p_enforce_foreign_locking => true
2448 ,p_validation_start_date => l_validation_start_date
2449 ,p_validation_end_date => l_validation_end_date
2450 );
2451 Else
2452 --
2453 -- We are doing a datetrack 'INSERT' which is illegal within this
2454 -- procedure therefore we must error (note: to lck on insert the
2455 -- private procedure ins_lck should be called).
2456 --
2457 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2458 fnd_message.set_token('PROCEDURE', l_proc);
2459 fnd_message.set_token('STEP','20');
2460 fnd_message.raise_error;
2461 End If;
2462 --
2463 -- Set the validation start and end date OUT arguments
2464 --
2465 p_validation_start_date := l_validation_start_date;
2466 p_validation_end_date := l_validation_end_date;
2467 --
2468 hr_utility.set_location(' Leaving:'||l_proc, 30);
2469 --
2470 -- We need to trap the ORA LOCK exception
2471 --
2472 Exception
2473 When HR_Api.Object_Locked then
2474 --
2475 -- The object is locked therefore we need to supply a meaningful
2476 -- error message.
2477 --
2478 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
2479 fnd_message.set_token('TABLE_NAME', 'pay_run_types_f');
2480 fnd_message.raise_error;
2481 End lck;
2482 --
2483 -- ----------------------------------------------------------------------------
2484 -- |-----------------------------< convert_args >-----------------------------|
2485 -- ----------------------------------------------------------------------------
2486 Function convert_args
2487 (p_run_type_id in number
2488 ,p_run_type_name in varchar2
2489 ,p_run_method in varchar2
2490 ,p_effective_start_date in date
2491 ,p_effective_end_date in date
2492 ,p_business_group_id in number
2493 ,p_legislation_code in varchar2
2494 ,p_shortname in varchar2
2495 ,p_srs_flag in varchar2
2496 ,p_run_information_category in varchar2
2497 ,p_run_information1 in varchar2
2498 ,p_run_information2 in varchar2
2499 ,p_run_information3 in varchar2
2500 ,p_run_information4 in varchar2
2501 ,p_run_information5 in varchar2
2502 ,p_run_information6 in varchar2
2503 ,p_run_information7 in varchar2
2504 ,p_run_information8 in varchar2
2505 ,p_run_information9 in varchar2
2506 ,p_run_information10 in varchar2
2507 ,p_run_information11 in varchar2
2508 ,p_run_information12 in varchar2
2509 ,p_run_information13 in varchar2
2510 ,p_run_information14 in varchar2
2511 ,p_run_information15 in varchar2
2512 ,p_run_information16 in varchar2
2513 ,p_run_information17 in varchar2
2514 ,p_run_information18 in varchar2
2515 ,p_run_information19 in varchar2
2516 ,p_run_information20 in varchar2
2517 ,p_run_information21 in varchar2
2518 ,p_run_information22 in varchar2
2519 ,p_run_information23 in varchar2
2520 ,p_run_information24 in varchar2
2521 ,p_run_information25 in varchar2
2522 ,p_run_information26 in varchar2
2523 ,p_run_information27 in varchar2
2524 ,p_run_information28 in varchar2
2525 ,p_run_information29 in varchar2
2526 ,p_run_information30 in varchar2
2527 ,p_object_version_number in number
2528 )
2529 Return g_rec_type is
2530 --
2531 l_rec g_rec_type;
2532 --
2533 Begin
2534 --
2535 -- Convert arguments into local l_rec structure.
2536 --
2537 l_rec.run_type_id := p_run_type_id;
2538 l_rec.run_type_name := p_run_type_name;
2539 l_rec.run_method := p_run_method;
2540 l_rec.effective_start_date := p_effective_start_date;
2541 l_rec.effective_end_date := p_effective_end_date;
2542 l_rec.business_group_id := p_business_group_id;
2543 l_rec.legislation_code := p_legislation_code;
2544 l_rec.shortname := p_shortname;
2545 l_rec.srs_flag := p_srs_flag;
2546 l_rec.run_information_category := p_run_information_category;
2547 l_rec.run_information1 := p_run_information1;
2548 l_rec.run_information2 := p_run_information2;
2549 l_rec.run_information3 := p_run_information3;
2550 l_rec.run_information4 := p_run_information4;
2551 l_rec.run_information5 := p_run_information5;
2552 l_rec.run_information6 := p_run_information6;
2553 l_rec.run_information7 := p_run_information7;
2554 l_rec.run_information8 := p_run_information8;
2555 l_rec.run_information9 := p_run_information9;
2556 l_rec.run_information10 := p_run_information10;
2557 l_rec.run_information11 := p_run_information11;
2558 l_rec.run_information12 := p_run_information12;
2559 l_rec.run_information13 := p_run_information13;
2560 l_rec.run_information14 := p_run_information14;
2561 l_rec.run_information15 := p_run_information15;
2562 l_rec.run_information16 := p_run_information16;
2563 l_rec.run_information17 := p_run_information17;
2564 l_rec.run_information18 := p_run_information18;
2565 l_rec.run_information19 := p_run_information19;
2566 l_rec.run_information20 := p_run_information20;
2567 l_rec.run_information21 := p_run_information21;
2568 l_rec.run_information22 := p_run_information22;
2569 l_rec.run_information23 := p_run_information23;
2570 l_rec.run_information24 := p_run_information24;
2571 l_rec.run_information25 := p_run_information25;
2572 l_rec.run_information26 := p_run_information26;
2573 l_rec.run_information27 := p_run_information27;
2574 l_rec.run_information28 := p_run_information28;
2575 l_rec.run_information29 := p_run_information29;
2576 l_rec.run_information30 := p_run_information30;
2577 l_rec.object_version_number := p_object_version_number;
2578 --
2579 -- Return the plsql record structure.
2580 --
2581 Return(l_rec);
2582 --
2583 End convert_args;
2584 --
2585 end pay_prt_shd;