[Home] [Help]
PACKAGE BODY: APPS.DT_CLIENT_SUPPORT_UTILITY
Source
1 Package Body dt_client_support_utility As
2 /* $Header: dtclsutl.pkb 120.0 2005/05/27 23:10:31 appldev noship $ */
3 --
4 -- Global package name
5 --
6 g_package varchar2(33) := ' dt_client_support_utility.';
7
8 g_debug boolean;
9 --
10 -- Global package constants
11 --
12 g_true_str constant varchar2(6) := 'TRUE';
13 g_false_str constant varchar2(6) := 'FALSE';
14 --
15 -- ----------------------------------------------------------------------------
16 -- |---------------------------< get_update_mode_list >-----------------------|
17 -- ----------------------------------------------------------------------------
18 --
19 procedure get_update_mode_list
20 (p_effective_date in date
21 ,p_package_name in varchar2
22 ,p_procedure_name in varchar2
23 ,p_base_key_value in number
24 ,p_correction out nocopy boolean
25 ,p_update out nocopy boolean
26 ,p_update_override out nocopy boolean
27 ,p_update_change_insert out nocopy boolean
28 ) is
29 --
30 -- Local constants
31 --
32 c_out_len constant integer := 6; -- Value must correspond to the length
33 -- of the (OUT bind) variables such as
34 -- l_correction.
35 --
36 -- Local variables
37 --
38 l_cursor integer; -- Dynamic sql cursor identifier
39 l_pl_sql varchar2(900); -- Dynamic PL/SQL package procedure
40 -- call source code text.
41 l_execute integer; -- Value returned by dbms_sql.execute
42 l_correction varchar2(6); -- Char version of boolean OUT value
43 l_update varchar2(6); -- Char version of boolean OUT value
44 l_update_override varchar2(6); -- Char version of boolean OUT value
45 l_update_change_insert varchar2(6); -- Char version of boolean OUT value
46 --
47 l_proc varchar2(72) := g_package||'get_update_mode_list';
48 begin
49 hr_utility.set_location('Entering:'|| l_proc, 10);
50 --
51 -- Define dynamic PL/SQL block with package procedure call
52 --
53 -- Note: The varchar2 variables are required because
54 -- dbms_sql.bind_variable does not support the boolean datatype.
55 -- After the package procedure call the boolean values have to be
56 -- converted to varchar2, just so they can be retreved from the
57 -- dynamic PL/SQL using dbms_sql.bind_variable.
58 --
59 l_pl_sql := 'declare ' ||
60 'l_correction boolean; ' ||
61 'l_update boolean; ' ||
62 'l_update_override boolean; ' ||
63 'l_update_change_insert boolean; ' ||
64 'begin {Package_Name}.{Procedure_Name}' ||
65 '(p_effective_date =>:p_effective_date ' ||
66 ',p_base_key_value =>:p_base_key_value ' ||
67 ',p_correction =>l_correction ' ||
68 ',p_update =>l_update ' ||
69 ',p_update_override =>l_update_override ' ||
70 ',p_update_change_insert =>l_update_change_insert ' ||
71 '); ' ||
72 'if l_correction then ' ||
73 ' :correction := '||''''||g_true_str||''''||'; ' ||
74 'else ' ||
75 ' :correction := '||''''||g_false_str||''''||'; ' ||
76 'end if; ' ||
77 'if l_update then ' ||
78 ' :update := '||''''||g_true_str||''''||'; ' ||
79 'else ' ||
80 ' :update := '||''''||g_false_str||''''||'; ' ||
81 'end if; ' ||
82 'if l_update_override then ' ||
83 ' :update_override := '||''''||g_true_str||''''||'; ' ||
84 'else ' ||
85 ' :update_override := '||''''||g_false_str||''''||'; ' ||
86 'end if; ' ||
87 'if l_update_change_insert then ' ||
88 ' :update_change_insert := '||''''||g_true_str||''''||'; ' ||
89 'else ' ||
90 ' :update_change_insert := '||''''||g_false_str||''''||'; ' ||
91 'end if; ' ||
92 'end;';
93 --
94 -- Replace the literal (token) strings in the PL/SQL package procedure call
95 --
96 l_pl_sql := replace(l_pl_sql, '{Package_Name}', p_package_name);
97 l_pl_sql := replace(l_pl_sql, '{Procedure_Name}', p_procedure_name);
98 hr_utility.set_location(l_proc, 20);
99 --
100 -- Execute the Dynamic PL/SQL statement
101 --
102 -- Open dynamic cursor
103 l_cursor := dbms_sql.open_cursor;
104 hr_utility.set_location(l_proc, 30);
105 --
106 -- Parse dynamic PL/SQL
107 dbms_sql.parse(l_cursor, l_pl_sql, dbms_sql.v7);
108 hr_utility.set_location(l_proc, 40);
109 --
110 -- Bind dynamic package procedure IN parameter values
111 dbms_sql.bind_variable(l_cursor, ':p_effective_date', p_effective_date);
112 dbms_sql.bind_variable(l_cursor, ':p_base_key_value', p_base_key_value);
113 hr_utility.set_location(l_proc, 50);
114 --
115 -- Bind dynamic PL/SQL local variable (OUT parameter) values
116 dbms_sql.bind_variable(l_cursor, ':correction', l_correction, c_out_len);
117 dbms_sql.bind_variable(l_cursor, ':update', l_update, c_out_len);
118 dbms_sql.bind_variable(l_cursor, ':update_override'
119 ,l_update_override, c_out_len);
120 dbms_sql.bind_variable(l_cursor, ':update_change_insert'
121 ,l_update_change_insert, c_out_len);
122 hr_utility.set_location(l_proc, 60);
123 --
124 -- Execute the dynamic PL/SQL block
125 l_execute := dbms_sql.execute(l_cursor);
126 hr_utility.set_location(l_proc, 70);
127 --
128 -- Obtain the OUT parameter, as varchar2 values.
129 --
130 dbms_sql.variable_value(l_cursor, ':correction', l_correction);
131 dbms_sql.variable_value(l_cursor, ':update', l_update);
132 dbms_sql.variable_value(l_cursor, ':update_override', l_update_override);
133 dbms_sql.variable_value(l_cursor, ':update_change_insert'
134 ,l_update_change_insert);
135 hr_utility.set_location(l_proc, 80);
136 --
137 -- Close Dynamic Cursor
138 --
139 dbms_sql.close_cursor(l_cursor);
140 hr_utility.set_location(l_proc, 90);
141 --
142 -- Convert the varchar2 values back into boolean so
143 -- they can be returned from this procedure.
144 --
145 if l_correction = g_true_str then
146 p_correction := true;
147 else
148 p_correction := false;
149 end if;
150 --
151 if l_update = g_true_str then
152 p_update := true;
153 else
154 p_update := false;
155 end if;
156 --
157 if l_update_override = g_true_str then
158 p_update_override := true;
159 else
160 p_update_override := false;
161 end if;
162 --
163 if l_update_change_insert = g_true_str then
164 p_update_change_insert := true;
165 else
166 p_update_change_insert := false;
167 end if;
168 hr_utility.set_location(' Leaving:'|| l_proc, 100);
169 exception
170 --
171 -- In case of an unexpected error ensure
172 -- that the Dynamic Cursor is closed.
173 --
174 when others then
175 if dbms_sql.is_open(l_cursor) then
176 dbms_sql.close_cursor(l_cursor);
177 end if;
178 raise;
179 end get_update_mode_list;
180 --
181 -- ----------------------------------------------------------------------------
182 -- |---------------------------< get_delete_mode_list >-----------------------|
183 -- ----------------------------------------------------------------------------
184 --
185 procedure get_delete_mode_list
186 (p_effective_date in date
187 ,p_package_name in varchar2
188 ,p_procedure_name in varchar2
189 ,p_base_key_value in number
190 ,p_zap out nocopy boolean
191 ,p_delete out nocopy boolean
192 ,p_future_change out nocopy boolean
193 ,p_delete_next_change out nocopy boolean
194 ) is
195 --
196 -- Local constants
197 --
198 c_out_len constant integer := 6; -- Value must correspond to the length
199 -- of the (OUT bind) variables such as
200 -- l_zap.
201 --
202 -- Local variables
203 --
204 l_cursor integer; -- Dynamic sql cursor identifier
205 l_pl_sql varchar2(900); -- Dynamic PL/SQL package procedure
206 -- call source code text.
207 l_execute integer; -- Value returned by dbms_sql.execute
208 l_zap varchar2(6); -- Char version of boolean OUT value
209 l_delete varchar2(6); -- Char version of boolean OUT value
210 l_future_change varchar2(6); -- Char version of boolean OUT value
211 l_delete_next_change varchar2(6); -- Char version of boolean OUT value
212 --
213 l_proc varchar2(72) := g_package||'get_delete_mode_list';
214 begin
215 hr_utility.set_location('Entering:'|| l_proc, 10);
216 --
217 -- Define dynamic PL/SQL block with package procedure call
218 --
219 -- Note: The varchar2 variables are required because
220 -- dbms_sql.bind_variable does not support the boolean datatype.
221 -- After the package procedure call the boolean values have to be
222 -- converted to varchar2, just so they can be retreved from the
223 -- dynamic PL/SQL using dbms_sql.bind_variable.
224 --
225 l_pl_sql := 'declare ' ||
226 'l_zap boolean; ' ||
227 'l_delete boolean; ' ||
228 'l_future_change boolean; ' ||
229 'l_delete_next_change boolean; ' ||
230 'begin {Package_Name}.{Procedure_Name}' ||
231 '(p_effective_date =>:p_effective_date ' ||
232 ',p_base_key_value =>:p_base_key_value ' ||
233 ',p_zap =>l_zap ' ||
234 ',p_delete =>l_delete ' ||
235 ',p_future_change =>l_future_change ' ||
236 ',p_delete_next_change =>l_delete_next_change ' ||
237 '); ' ||
238 'if l_zap then ' ||
239 ' :zap := '||''''||g_true_str||''''||'; ' ||
240 'else ' ||
241 ' :zap := '||''''||g_false_str||''''||'; ' ||
242 'end if; ' ||
243 'if l_delete then ' ||
244 ' :delete := '||''''||g_true_str||''''||'; ' ||
245 'else ' ||
246 ' :delete := '||''''||g_false_str||''''||'; ' ||
247 'end if; ' ||
248 'if l_future_change then ' ||
249 ' :future_change := '||''''||g_true_str||''''||'; ' ||
250 'else ' ||
251 ' :future_change := '||''''||g_false_str||''''||'; ' ||
252 'end if; ' ||
253 'if l_delete_next_change then ' ||
254 ' :delete_next_change := '||''''||g_true_str||''''||'; ' ||
255 'else ' ||
256 ' :delete_next_change := '||''''||g_false_str||''''||'; ' ||
257 'end if; ' ||
258 'end;';
259 --
260 -- Replace the literal (token) strings in the PL/SQL package procedure call
261 --
262 l_pl_sql := replace(l_pl_sql, '{Package_Name}', p_package_name);
263 l_pl_sql := replace(l_pl_sql, '{Procedure_Name}', p_procedure_name);
264 hr_utility.set_location(l_proc, 20);
265 --
266 -- Execute the Dynamic PL/SQL statement
267 --
268 -- Open dynamic cursor
269 l_cursor := dbms_sql.open_cursor;
270 hr_utility.set_location(l_proc, 30);
271 --
272 -- Parse dynamic PL/SQL
273 dbms_sql.parse(l_cursor, l_pl_sql, dbms_sql.v7);
274 hr_utility.set_location(l_proc, 40);
275 --
276 -- Bind dynamic package procedure IN parameter values
277 dbms_sql.bind_variable(l_cursor, ':p_effective_date', p_effective_date);
278 dbms_sql.bind_variable(l_cursor, ':p_base_key_value', p_base_key_value);
279 hr_utility.set_location(l_proc, 50);
280 --
281 -- Bind dynamic PL/SQL local variable (OUT parameter) values
282 dbms_sql.bind_variable(l_cursor, ':zap', l_zap, c_out_len);
283 dbms_sql.bind_variable(l_cursor, ':delete', l_delete, c_out_len);
284 dbms_sql.bind_variable(l_cursor, ':future_change'
285 ,l_future_change, c_out_len);
286 dbms_sql.bind_variable(l_cursor, ':delete_next_change'
287 ,l_delete_next_change, c_out_len);
288 hr_utility.set_location(l_proc, 60);
289 --
290 -- Execute the dynamic PL/SQL block
291 l_execute := dbms_sql.execute(l_cursor);
292 hr_utility.set_location(l_proc, 70);
293 --
294 -- Obtain the OUT parameter, as varchar2 values.
295 --
296 dbms_sql.variable_value(l_cursor, ':zap', l_zap);
297 dbms_sql.variable_value(l_cursor, ':delete', l_delete);
298 dbms_sql.variable_value(l_cursor, ':future_change', l_future_change);
299 dbms_sql.variable_value(l_cursor, ':delete_next_change'
300 ,l_delete_next_change);
301 hr_utility.set_location(l_proc, 80);
302 --
303 -- Close Dynamic Cursor
304 --
305 dbms_sql.close_cursor(l_cursor);
306 hr_utility.set_location(l_proc, 90);
307 --
308 -- Convert the varchar2 values back into boolean so
309 -- they can be returned from this procedure.
310 --
311 if l_zap = g_true_str then
312 p_zap := true;
313 else
314 p_zap := false;
315 end if;
316 --
317 if l_delete = g_true_str then
318 p_delete := true;
319 else
320 p_delete := false;
321 end if;
322 --
323 if l_future_change = g_true_str then
324 p_future_change := true;
325 else
326 p_future_change := false;
327 end if;
328 --
329 if l_delete_next_change = g_true_str then
330 p_delete_next_change := true;
331 else
332 p_delete_next_change := false;
333 end if;
334 hr_utility.set_location(' Leaving:'|| l_proc, 100);
335 exception
336 --
337 -- In case of an unexpected error ensure
338 -- that the Dynamic Cursor is closed.
339 --
340 when others then
341 if dbms_sql.is_open(l_cursor) then
342 dbms_sql.close_cursor(l_cursor);
343 end if;
344 raise;
345 end get_delete_mode_list;
346 --
350 --
347 -- ----------------------------------------------------------------------------
348 -- |-------------------------------< lock_record >----------------------------|
349 -- ----------------------------------------------------------------------------
351 procedure lock_record
352 (p_effective_date in date
353 ,p_datetrack_mode in varchar2
354 ,p_object_version_number in number
355 ,p_package_name in varchar2
356 ,p_procedure_name in varchar2
357 ,p_uid_item_name in varchar2
358 ,p_base_key_value in number
359 ,p_validation_start_date out nocopy date
360 ,p_validation_end_date out nocopy date
361 ) is
362 --
363 -- Local constants
364 --
365 c_date_len constant integer := 11; -- Value corresponding to the length
366 -- of the date (OUT bind) variables.
367 --
368 -- Local variables
369 --
370 l_cursor integer; -- Dynamic sql cursor identifier
371 l_pl_sql varchar2(900); -- Dynamic PL/SQL package procedure
372 -- call source code text.
373 l_execute integer; -- Value returned by dbms_sql.execute
374 l_validation_start_date date; -- Bind value from Dynamic PL/SQL.
375 l_validation_end_date date; -- Bind value from Dynamic PL/SQL.
376 --
377 l_proc varchar2(72) := g_package||'lock_record';
378 begin
379 hr_utility.set_location('Entering:'|| l_proc, 10);
380 --
381 -- Define dynamic PL/SQL block with package procedure call
382 --
383 -- Note: The varchar2 variables are required because
384 -- dbms_sql.bind_variable does not support the boolean datatype.
385 -- After the package procedure call the boolean values have to be
386 -- converted to varchar2, just so they can be retreved from the
387 -- dynamic PL/SQL using dbms_sql.bind_variable.
388 --
389 l_pl_sql := 'begin {Package_Name}.{Procedure_Name}' ||
390 '(p_effective_date =>:p_effective_date ' ||
391 ',p_datetrack_mode =>:p_datetrack_mode ' ||
392 ',{p_uid_item_name} =>:p_base_key_value ' ||
393 ',p_object_version_number =>:p_object_version_number ' ||
394 ',p_validation_start_date =>:p_validation_start_date ' ||
395 ',p_validation_end_date =>:p_validation_end_date ' ||
396 '); ' ||
397 'end;';
398 --
399 -- Replace the literal (token) strings in the PL/SQL package procedure call
400 --
401 l_pl_sql := replace(l_pl_sql, '{Package_Name}', p_package_name);
402 l_pl_sql := replace(l_pl_sql, '{Procedure_Name}', p_procedure_name);
403 l_pl_sql := replace(l_pl_sql, '{p_uid_item_name}', 'p_' || p_uid_item_name);
404 hr_utility.set_location(l_proc, 20);
405 --
406 -- Execute the Dynamic PL/SQL statement
407 --
408 -- Open dynamic cursor
409 l_cursor := dbms_sql.open_cursor;
410 hr_utility.set_location(l_proc, 30);
411 --
412 -- Parse dynamic PL/SQL
413 dbms_sql.parse(l_cursor, l_pl_sql, dbms_sql.v7);
414 hr_utility.set_location(l_proc, 40);
415 --
416 -- Bind dynamic package procedure IN parameter values
417 dbms_sql.bind_variable(l_cursor, ':p_effective_date', p_effective_date);
418 dbms_sql.bind_variable(l_cursor, ':p_datetrack_mode', p_datetrack_mode);
419 dbms_sql.bind_variable(l_cursor, ':p_base_key_value', p_base_key_value);
420 dbms_sql.bind_variable(l_cursor, ':p_object_version_number',
421 p_object_version_number);
422 hr_utility.set_location(l_proc, 50);
423 --
424 -- Bind dynamic PL/SQL local variable (OUT parameter) values
425 dbms_sql.bind_variable(l_cursor, ':p_validation_start_date',
426 l_validation_start_date);
427 dbms_sql.bind_variable(l_cursor, ':p_validation_end_date',
428 l_validation_end_date);
429 hr_utility.set_location(l_proc, 60);
430 --
431 -- Execute the dynamic PL/SQL block
432 l_execute := dbms_sql.execute(l_cursor);
433 hr_utility.set_location(l_proc, 70);
434 --
435 -- Obtain the OUT parameter, as varchar2 values.
436 --
437 dbms_sql.variable_value(l_cursor, ':p_validation_start_date',
438 l_validation_start_date);
439 dbms_sql.variable_value(l_cursor, ':p_validation_end_date',
440 l_validation_end_date);
441 hr_utility.set_location(l_proc, 80);
442 --
443 -- Close Dynamic Cursor
444 --
445 dbms_sql.close_cursor(l_cursor);
446 hr_utility.set_location(l_proc, 90);
447 --
448 -- Set Out parameters
449 --
450 p_validation_start_date := l_validation_start_date;
451 p_validation_end_date := l_validation_end_date;
452 hr_utility.set_location(' Leaving:'|| l_proc, 100);
453 exception
454 --
455 -- In case of an unexpected error ensure
456 -- that the Dynamic Cursor is closed.
457 --
458 when others then
459 if dbms_sql.is_open(l_cursor) then
460 dbms_sql.close_cursor(l_cursor);
461 end if;
462 raise;
463 end lock_record;
464 --
465 -- ----------------------------------------------------------------------------
466 -- |---------------------< get_update_modes_and_dates >-----------------------|
467 -- ----------------------------------------------------------------------------
468 --
469 procedure get_update_modes_and_dates
470 (p_effective_date in date
471 ,p_package_name in varchar2
472 ,p_procedure_name in varchar2
473 ,p_base_key_value in number
474 ,p_correction out nocopy number
475 ,p_update out nocopy number
476 ,p_update_override out nocopy number
477 ,p_update_change_insert out nocopy number
478 ,p_correction_start_date out nocopy date
479 ,p_correction_end_date out nocopy date
480 ,p_update_start_date out nocopy date
481 ,p_update_end_date out nocopy date
482 ,p_override_start_date out nocopy date
483 ,p_override_end_date out nocopy date
484 ,p_upd_chg_start_date out nocopy date
485 ,p_upd_chg_end_date out nocopy date
486 ) IS
487 --
488 -- Local variables
489 --
490 l_cursor integer; -- Dynamic sql cursor identifier
491 l_pl_sql varchar2(2000); -- Dynamic PL/SQL package procedure
492 -- call source code text.
493
494 --
495 l_proc varchar2(72) := g_package||'get_update_modes_and_dates';
496 begin
497 hr_utility.set_location('Entering:'|| l_proc, 10);
498 --
499 -- Define dynamic PL/SQL block with package procedure call
500 --
501 --
502 l_pl_sql := 'declare ' ||
503 ' l_correction BOOLEAN; ' ||
504 ' l_update BOOLEAN; ' ||
505 ' l_update_override BOOLEAN; ' ||
506 ' l_update_change_insert BOOLEAN; ' ||
507 'begin ' ||
508 '{Package_Name}.{Procedure_Name}' ||
509 '(p_effective_date =>:p_effective_date ' ||
510 ',p_base_key_value =>:p_base_key_value ' ||
511 ',p_correction => l_correction ' ||
512 ',p_update => l_update ' ||
513 ',p_update_override => l_update_override ' ||
514 ',p_update_change_insert => l_update_change_insert ' ||
515 ',p_correction_start_date => :p_correction_start_date ' ||
516 ',p_correction_end_date => :p_correction_end_date ' ||
517 ',p_update_start_date => :p_update_start_date ' ||
518 ',p_update_end_date => :p_update_end_date ' ||
519 ',p_override_start_date => :p_override_start_date ' ||
520 ',p_override_end_date => :p_override_end_date ' ||
521 ',p_upd_chg_start_date => :p_upd_chg_start_date ' ||
522 ',p_upd_chg_end_date => :p_upd_chg_end_date ' ||
526 hr_api.boolean_to_constant(l_correction); ' ||
523 '); ' ||
524 'hr_utility.set_location('||''''||'dyn sql'||''''||', 11);' ||
525 ':p_correction :=
527 ':p_update := hr_api.boolean_to_constant(l_update); ' ||
528 ':p_update_override
529 := hr_api.boolean_to_constant(l_update_override); ' ||
530 ':p_update_change_insert
531 := hr_api.boolean_to_constant(l_update_change_insert); ' ||
532 'hr_utility.set_location('||''''||'dyn sql'||''''||', 12);' ||
533 'end;';
534 --
535 -- Replace the literal (token) strings in the PL/SQL package procedure call
536 --
537 l_pl_sql := replace(l_pl_sql, '{Package_Name}', p_package_name);
538 l_pl_sql := replace(l_pl_sql, '{Procedure_Name}', p_procedure_name);
539 hr_utility.set_location(l_proc, 20);
540
541 EXECUTE IMMEDIATE l_pl_sql USING p_effective_date,
542 p_base_key_value,
543 OUT p_correction_start_date,
544 OUT p_correction_end_date,
545 OUT p_update_start_date,
546 OUT p_update_end_date,
547 OUT p_override_start_date,
548 OUT p_override_end_date,
549 OUT p_upd_chg_start_date,
550 OUT p_upd_chg_end_date,
551 OUT p_correction,
552 OUT p_update,
553 OUT p_update_override,
554 OUT p_update_change_insert;
555
556 hr_utility.set_location('Leaving:'|| l_proc, 30);
557 --
558 exception
559 --
560 -- In case of an unexpected error raise the error
561 --
562 when others then
563
564 RAISE;
565 end get_update_modes_and_dates;
566 --
567 -- ----------------------------------------------------------------------------
568 -- |---------------------< get_delete_modes_and_dates >-----------------------|
569 -- ----------------------------------------------------------------------------
570 --
571 procedure get_delete_modes_and_dates
572 (p_effective_date in date
573 ,p_package_name in varchar2
574 ,p_procedure_name in varchar2
575 ,p_base_key_value in number
576 ,p_zap out nocopy number
577 ,p_delete out nocopy number
578 ,p_future_change out nocopy number
579 ,p_delete_next_change out nocopy number
580 ,p_zap_start_date out nocopy date
581 ,p_zap_end_date out nocopy date
582 ,p_delete_start_date out nocopy date
583 ,p_delete_end_date out nocopy date
584 ,p_del_future_start_date out nocopy date
585 ,p_del_future_end_date out nocopy date
586 ,p_del_next_start_date out nocopy date
587 ,p_del_next_end_date out nocopy date
588 ) IS
589 --
590 -- Local variables
591 --
592 l_cursor integer; -- Dynamic sql cursor identifier
593 l_pl_sql varchar2(2000); -- Dynamic PL/SQL package procedure
594 -- call source code text.
595 --
596 l_proc varchar2(72) := g_package||'get_delete_modes_and_dates';
597 begin
598 hr_utility.set_location('Entering:'|| l_proc, 10);
599 --
600 -- Define dynamic PL/SQL block with package procedure call
601 --
602 --
603 l_pl_sql := 'declare ' ||
604 ' l_zap BOOLEAN; ' ||
605 ' l_delete BOOLEAN; ' ||
606 ' l_future_change BOOLEAN; ' ||
607 ' l_delete_next_change BOOLEAN; ' ||
608 'begin {Package_Name}.{Procedure_Name}' ||
609 '(p_effective_date => :p_effective_date ' ||
610 ',p_base_key_value => :p_base_key_value ' ||
611 ',p_zap => l_zap ' ||
612 ',p_delete => l_delete ' ||
613 ',p_future_change => l_future_change ' ||
614 ',p_delete_next_change => l_delete_next_change ' ||
615 ',p_zap_start_date => :p_zap_start_date ' ||
616 ',p_zap_end_date => :p_zap_end_date ' ||
617 ',p_del_future_start_date =>:p_del_future_start_date ' ||
618 ',p_del_future_end_date => :p_del_future_end_date ' ||
619 ',p_delete_start_date => :p_delete_start_date ' ||
620 ',p_delete_end_date => :p_delete_end_date ' ||
621 ',p_del_next_start_date => :p_del_next_start_date ' ||
622 ',p_del_next_end_date => :p_del_next_end_date ' ||
623 '); ' ||
624 ':p_zap := hr_api.boolean_to_constant(l_zap); ' ||
625 ':p_delete := hr_api.boolean_to_constant(l_delete); ' ||
626 ':p_future_change := hr_api.boolean_to_constant(l_future_change); ' ||
627 ':p_delete_next_change :=
628 hr_api.boolean_to_constant(l_delete_next_change); ' ||
629 'end;';
630 --
631 -- Replace the literal (token) strings in the PL/SQL package procedure call
632 --
633 l_pl_sql := replace(l_pl_sql, '{Package_Name}', p_package_name);
634 l_pl_sql := replace(l_pl_sql, '{Procedure_Name}', p_procedure_name);
635 hr_utility.set_location(l_proc, 20);
636
637 EXECUTE IMMEDIATE l_pl_sql USING p_effective_date,
638 p_base_key_value,
639 OUT p_zap_start_date,
640 OUT p_zap_end_date,
641 OUT p_del_future_start_date,
642 OUT p_del_future_end_date,
643 OUT p_delete_start_date,
644 OUT p_delete_end_date,
645 OUT p_del_next_start_date,
646 OUT p_del_next_end_date,
647 OUT p_zap,
648 OUT p_delete,
649 OUT p_future_change,
650 OUT p_delete_next_change;
651
652 hr_utility.set_location('Leaving:'|| l_proc, 30);
653 --
654 exception
655 --
656 -- In case of an unexpected error raise the error
657 --
658 when others then
659
660 RAISE;
661 end get_delete_modes_and_dates;
662
663 End dt_client_support_utility;