1 package body PAY_LINK_INPUT_VALUES_PKG as
2 /* $Header: pyliv.pkb 120.0 2005/05/29 01:50:44 appldev noship $ */
3 --------------------------------------------------------------------------------
4 g_dummy number (1);
5 g_package constant varchar2 (72) := 'PAY_LINK_INPUT_VALUES_PKG';
6 --------------------------------------------------------------------------------
7 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
8 BEGIN
9 DELETE FROM PAY_LINK_INPUT_VALUES_F
10 WHERE rowid = X_Rowid;
11
12 if (SQL%NOTFOUND) then
13 RAISE NO_DATA_FOUND;
14 end if;
15 END Delete_Row;
16 --------------------------------------------------------------------------------
17 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
18
19 X_Link_Input_Value_Id NUMBER,
20 X_Effective_Start_Date DATE,
21 X_Effective_End_Date DATE,
22 X_Element_Link_Id NUMBER,
23 X_Input_Value_Id NUMBER,
24 X_Costed_Flag VARCHAR2,
25 X_Default_Value VARCHAR2,
26 X_Max_Value VARCHAR2,
27 X_Min_Value VARCHAR2,
28 X_Warning_Or_Error VARCHAR2) IS
29 CURSOR C IS
30 SELECT *
31 FROM PAY_LINK_INPUT_VALUES_F
32 WHERE rowid = X_Rowid
33 FOR UPDATE of Link_Input_Value_Id NOWAIT;
34 Recinfo C%ROWTYPE;
35 BEGIN
36 OPEN C;
37 FETCH C INTO Recinfo;
38 if (C%NOTFOUND) then
39 CLOSE C;
40 RAISE NO_DATA_FOUND;
41 end if;
42 CLOSE C;
43 if (
44 ( (Recinfo.link_input_value_id = X_Link_Input_Value_Id)
45 OR ( (Recinfo.link_input_value_id IS NULL)
46 AND (X_Link_Input_Value_Id IS NULL)))
47 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
48 OR ( (Recinfo.effective_start_date IS NULL)
49 AND (X_Effective_Start_Date IS NULL)))
50 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
51 OR ( (Recinfo.effective_end_date IS NULL)
52 AND (X_Effective_End_Date IS NULL)))
53 AND ( (Recinfo.element_link_id = X_Element_Link_Id)
54 OR ( (Recinfo.element_link_id IS NULL)
55 AND (X_Element_Link_Id IS NULL)))
56 AND ( (Recinfo.input_value_id = X_Input_Value_Id)
57 OR ( (Recinfo.input_value_id IS NULL)
58 AND (X_Input_Value_Id IS NULL)))
59 AND ( (Recinfo.costed_flag = X_Costed_Flag)
60 OR ( (Recinfo.costed_flag IS NULL)
61 AND (X_Costed_Flag IS NULL)))
62 AND ( (Recinfo.default_value = X_Default_Value)
63 OR ( (Recinfo.default_value IS NULL)
64 AND (X_Default_Value IS NULL)))
65 AND ( (Recinfo.max_value = X_Max_Value)
66 OR ( (Recinfo.max_value IS NULL)
67 AND (X_Max_Value IS NULL)))
68 AND ( (Recinfo.min_value = X_Min_Value)
69 OR ( (Recinfo.min_value IS NULL)
70 AND (X_Min_Value IS NULL)))
71 AND ( (Recinfo.warning_or_error = X_Warning_Or_Error)
72 OR ( (Recinfo.warning_or_error IS NULL)
73 AND (X_Warning_Or_Error IS NULL)))
74 ) then
75 return;
76 else
77 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
78 APP_EXCEPTION.RAISE_EXCEPTION;
79 end if;
80 END Lock_Row;
81 --------------------------------------------------------------------------------
82 PROCEDURE Update_Row(X_Rowid VARCHAR2,
83 X_Link_Input_Value_Id NUMBER,
84 X_Effective_Start_Date DATE,
85 X_Effective_End_Date DATE,
86 X_Element_Link_Id NUMBER,
87 X_Input_Value_Id NUMBER,
88 X_Costed_Flag VARCHAR2,
89 X_Default_Value VARCHAR2,
90 X_Max_Value VARCHAR2,
91 X_Min_Value VARCHAR2,
92 X_Warning_Or_Error VARCHAR2) IS
93 BEGIN
94 UPDATE PAY_LINK_INPUT_VALUES_F
95 SET
96
97 link_input_value_id = X_Link_Input_Value_Id,
98 effective_start_date = X_Effective_Start_Date,
99 effective_end_date = X_Effective_End_Date,
100 element_link_id = X_Element_Link_Id,
101 input_value_id = X_Input_Value_Id,
102 costed_flag = X_Costed_Flag,
103 default_value = X_Default_Value,
104 max_value = X_Max_Value,
105 min_value = X_Min_Value,
106 warning_or_error = X_Warning_Or_Error
107 WHERE rowid = X_rowid;
108
109 if (SQL%NOTFOUND) then
110 RAISE NO_DATA_FOUND;
111 end if;
112
113 END Update_Row;
114 --------------------------------------------------------------------------------
115 procedure CREATE_LINK_INPUT_VALUE(
116 --
117 --******************************************************************************
118 --* Creates link input values for a new link.
119 --******************************************************************************
120 --
121 p_element_link_id number,
122 p_costable_type varchar2,
123 p_effective_start_date date,
124 p_effective_end_date date,
125 p_element_type_id number) is
126 --
127 l_proc constant varchar2 (72) := g_package||'create_link_input_value';
128 --
129 v_link_input_value_id number;
130 v_input_value_id number := null;
131 v_default_value varchar2(255);
132 v_min_value varchar2(255);
133 v_max_value varchar2(255);
134 v_warning_or_error varchar2(1);
135 v_costed_flag varchar2(1);
136 v_effective_start_date date;
137 v_effective_end_date date;
138 l_link_is_costable boolean := (p_costable_type in ('F', 'C', 'D'));
139 --
140 -- This selects all input values for an element type
141 --
142 cursor csr_input_value is
143 select *
144 from pay_input_values_f
145 where element_type_id = p_element_type_id
146 and effective_start_date <= p_effective_end_date
147 and effective_end_date >= p_effective_start_date
148 order by input_value_id,effective_start_date;
149 --
150 procedure check_parameters is
151 --
152 begin
153 --
154 hr_api.mandatory_arg_error (
155 p_api_name => l_proc,
156 p_argument => 'element_link_id',
157 p_argument_value=> p_element_link_id);
158 --
159 hr_api.mandatory_arg_error (
160 p_api_name => l_proc,
161 p_argument => 'costable_type',
162 p_argument_value=> p_costable_type);
163 --
164 hr_api.mandatory_arg_error (
165 p_api_name => l_proc,
166 p_argument => 'effective_start_date',
167 p_argument_value=> p_effective_start_date);
168 --
169 hr_api.mandatory_arg_error (
170 p_api_name => l_proc,
171 p_argument => 'effective_end_date',
172 p_argument_value=> p_effective_end_date);
173 --
174 hr_api.mandatory_arg_error (
175 p_api_name => l_proc,
176 p_argument => 'element_type_id',
177 p_argument_value=> p_element_type_id);
178 --
179 end check_parameters;
180 --
181 begin
182 --
183 hr_utility.set_location(l_proc,1);
184 --
185 check_parameters;
186 --
187 for fetched_input_value in csr_input_value LOOP
188 --
189 -- Default the costed flag
190 -- AR MLS Change - added hard coded PAY VALUE
191 -- because the base table now contains the
192 -- system names of input values. Therefore, we can
193 -- guarantee that the input value name
194 -- is pay value in the base table.
195 --
196 if upper(fetched_input_value.name) = 'PAY VALUE'
197 and l_link_is_costable
198 then
199 v_costed_flag := 'Y' ;
200 else
201 v_costed_flag := 'N';
202 end if;
203 --
204 -- Set up hot or cold defaults
205 --
206 if fetched_input_value.hot_default_flag = 'Y' then -- hot defaults
207 --
208 v_default_value := null;
209 v_min_value := null;
210 v_max_value := null;
211 v_warning_or_error := null;
212 --
213 else -- cold defaults
214 --
215 v_default_value := fetched_input_value.default_value;
216 v_min_value := fetched_input_value.min_value;
217 v_max_value := fetched_input_value.max_value;
218 v_warning_or_error := fetched_input_value.warning_or_error;
219 --
220 end if;
221 --
222 -- Set the new link input value's effective dates to be constrained within
223 -- the overlap in existence between the element link and the date effective
224 -- row for the input value.
225 --
226 v_effective_start_date := greatest (p_effective_start_date,
227 fetched_input_value.effective_start_date);
228 v_effective_end_date := least (p_effective_end_date,
229 fetched_input_value.effective_end_date);
230 --
231 hr_utility.set_location (l_proc, 10);
232 --
233 -- Only increment the link input value if the input value is different.
234 if (v_input_value_id is null or v_input_value_id <> fetched_input_value.input_value_id) then
235 select pay_link_input_values_s.nextval
236 into v_link_input_value_id
237 from sys.dual;
238 --
239 v_input_value_id := fetched_input_value.input_value_id;
240 end if;
241 --
242 insert into pay_link_input_values_f
243 (link_input_value_id,
244 effective_start_date,
245 effective_end_date,
246 element_link_id,
247 input_value_id,
248 costed_flag,
249 default_value,
250 max_value,
251 min_value,
252 warning_or_error,
253 creation_date)
254 values (
255 v_link_input_value_id,
256 v_effective_start_date,
257 v_effective_end_date,
258 p_element_link_id,
259 fetched_input_value.input_value_id,
260 v_costed_flag,
261 v_default_value,
262 v_max_value,
263 v_min_value,
264 v_warning_or_error,
265 sysdate);
266 --
267 end loop;
268 --
269 end create_link_input_value;
270 --------------------------------------------------------------------------------
271 procedure CREATE_LINK_INPUT_VALUE (
272 --
273 --******************************************************************************
274 --* Creates link input values for existing links when a new input value is *
275 --* created at the type level. *
276 --******************************************************************************
277 --
278 p_input_value_id number,
279 p_element_type_id number,
280 p_effective_start_date date,
281 p_effective_end_date date,
282 p_name varchar2,
283 p_hot_default_flag varchar2,
284 p_default_value varchar2,
285 p_min_value varchar2,
286 p_max_value varchar2,
287 p_warning_or_error varchar2) is
288 --
289 cursor csr_links is
290 select *
291 from pay_element_links_f
292 where element_type_id = p_element_type_id
293 and effective_start_date <= p_effective_end_date
294 and effective_end_date >= p_effective_start_date
295 order by element_link_id,effective_start_date;
296 --
297 v_link_input_value_id number;
298 v_link_id number := null;
299 v_costed_flag varchar2(1);
300 v_min_value varchar2(255);
301 v_max_value varchar2(255);
302 v_default_value varchar2(255);
303 v_warning_or_error varchar2(1);
304 --
305 begin
306 --
307 for fetched_link in csr_links LOOP
308 --
309 -- Set up default costed flag
310 --
311 if fetched_link.costable_type in ('F', 'C', 'D')
312 and p_name = hr_general.pay_value then
313 v_costed_flag := 'Y';
314 else
315 v_costed_flag := 'N';
316 end if;
317 --
318 -- Set up hot or cold defaults
319 --
320 if p_hot_default_flag = 'Y' then
321 v_min_value := null;
322 v_max_value := null;
323 v_default_value := null;
324 v_warning_or_error := null;
325 else
326 v_min_value := p_min_value;
327 v_max_value := p_max_value;
328 v_default_value := p_default_value;
329 v_warning_or_error := p_warning_or_error;
330 end if;
331 --
332 -- Only increment the link id is different.
333 if (v_link_id is null or v_link_id <> fetched_link.element_link_id) then
334 select pay_link_input_values_s.nextval
335 into v_link_input_value_id
336 from sys.dual;
337 --
338 v_link_id := fetched_link.element_link_id;
339 end if;
340 --
341 insert into pay_link_input_values_f
342 (link_input_value_id,
343 effective_start_date,
344 effective_end_date,
345 element_link_id,
346 input_value_id,
347 costed_flag,
348 default_value,
349 max_value,
350 min_value,
351 warning_or_error,
352 creation_date)
353 values(
354 v_link_input_value_id,
355 greatest(fetched_link.effective_start_date,p_effective_start_date),
356 least(fetched_link.effective_end_date,p_effective_end_date),
357 fetched_link.element_link_id,
358 p_input_value_id,
359 v_costed_flag,
360 v_default_value,
361 v_max_value,
362 v_min_value,
363 v_warning_or_error,
364 sysdate);
365 --
366 end loop;
367 --
368 end create_link_input_value;
369 --------------------------------------------------------------------------------
370 procedure CHECK_REQUIRED_DEFAULTS (
371 --
372 --*****************************************************************************
373 --* Checks that all required default values are present
374 --*****************************************************************************
375 --
376 p_element_link_id number,
377 p_session_date date) is
378 --
379 cursor csr_defaults is
380 select 1
381 from pay_input_values_f TYPE,
382 pay_link_input_values_f LINK
383 where p_session_date between type.effective_start_date
384 and type.effective_end_date
385 and p_session_date between link.effective_start_date
386 and link.effective_end_date
387 and type.input_value_id = link.input_value_id
388 and link.element_link_id = p_element_link_id
389 and type.mandatory_flag = 'Y'
390 and ((type.hot_default_flag = 'N'
391 and link.default_value is null)
392 or (type.hot_default_flag = 'Y'
393 and nvl (link.default_value,
394 type.default_value) is null));
395 --
396 Missing_required_default boolean := FALSE;
397 --
398 begin
399 --
400 hr_utility.set_location ('PAY_LINK_INPUT_VALUES_PKG.CHECK_REQUIRED_DEFAULTS',1);
401 --
402 open csr_defaults;
403 fetch csr_defaults into g_dummy;
404 Missing_required_default := csr_defaults%found;
405 close csr_defaults;
406 --
407 if missing_required_default then
408 hr_utility.set_message (801, 'PAY_6219_INPVAL_NO_STAN_LINK');
409 hr_utility.raise_error;
410 end if;
411 --
412 end check_required_defaults;
413 --------------------------------------------------------------------------------
414 function NO_DEFAULT_AT_TYPE (
415 --
416 --******************************************************************************
417 --* Returns TRUE if there is no default value specified at the element type *
418 --******************************************************************************
419 --
420 -- Parameters are:
421 --
422 p_input_value_id number,
423 p_effective_start_date date,
424 p_effective_end_date date,
425 p_error_if_true boolean default FALSE ) return boolean is
426 --
427 cursor csr_link is
428 select 1
429 from pay_input_values_f
430 where input_value_id = p_input_value_id
431 and effective_start_date <=p_effective_end_date
432 and effective_end_date >=p_effective_start_date
433 and default_value is null;
434 --
435 v_dummy number(1);
436 v_no_default boolean := FALSE;
437 --
438 begin
439 open csr_link;
440 fetch csr_link into v_dummy;
441 v_no_default := csr_link%found;
442 close csr_link;
443 --
444 if p_error_if_true and v_no_default then
445 hr_utility.set_message (801, 'PAY_INPVAL_MUST_HAVE_DEFAULT');
446 hr_utility.raise_error;
447 end if;
448 --
449 return v_no_default;
450 --
451 end no_default_at_type;
452 --------------------------------------------------------------------------------
453 procedure PARENT_DELETED (
454 --
455 --******************************************************************************
456 --* Handles the case when any row referenced by a foreign key of the base *
457 --* is deleted (in whatever Date Track mode). ie If a parent record is zapped *
458 --* then the deletion is cascaded; if it is date-effectively deleted, then the *
459 --* rows referencing it are updated to have the same end-date. *
460 --******************************************************************************
461 --
462 -- Parameters to be passed in are:
463 --
464 p_parent_id number,-- The foreign key for the deleted parent
465 p_session_date date default trunc (sysdate),
466 p_validation_start_date date,
467 p_validation_end_date date,
468 p_delete_mode varchar2,
469 p_parent_name varchar2 -- The name of the parent entity
470 ) is
471 --
472 -- The following cursor fetches all rows identified by the foreign key to
473 -- the parent being deleted. The parent name identifies foreign key column
474 -- to use, thus the procedure is generic to any parent deletion
475 --
476 cursor csr_rows_owned_by_parent is
477 select rowid,pay_link_input_values_f.*
478 from pay_link_input_values_f
479 where p_parent_id = decode (p_parent_name,
480 'PAY_INPUT_VALUES_F',input_value_id,
481 'PAY_ELEMENT_LINKS_F',element_link_id)
482 for update;
483 --
484 c_end_of_time constant date := to_date('31/12/4712','DD/MM/YYYY');
485 --
486 begin
487 hr_utility.set_location ('pay_link_input_values_pkg.parent_deleted',1);
488 --
489 <<REMOVE_ORPHANED_ROWS>>
490 for fetched_row in csr_rows_owned_by_parent LOOP
491 --
492 -- If in ZAP mode then all rows belonging to the deleted
493 -- parent must be deleted. If in DELETE (ie date-effective
494 -- delete) mode then only rows with a future start date
495 -- must be deleted, and current rows must be updated so
496 -- that their end dates match that of their closed-down
497 -- parent. Current and future are determined by session
498 -- date.
499 --
500 if p_delete_mode = 'ZAP' -- ie delete all rows
501 or (p_delete_mode = 'DELETE' -- ie delete all future rows
502 and fetched_row.effective_start_date > p_session_date) then
503 --
504 delete from pay_link_input_values_f
505 where current of csr_rows_owned_by_parent;
506 --
507 elsif p_delete_mode = 'DELETE'
508 and p_session_date between fetched_row.effective_start_date
509 and fetched_row.effective_end_date then
510 --
511 update pay_link_input_values_f
512 set effective_end_date = p_session_date
513 where current of csr_rows_owned_by_parent;
514 --
515 -- For delete next changes when there are no future rows for the parent,
516 -- extend the input value's end date to the end of time to match the action
517 -- which will be performed on the parent
518 --
519 elsif p_delete_mode = 'DELETE_NEXT_CHANGE'
520 and p_validation_end_date = c_end_of_time then
521 --
522 update pay_link_input_values_f
523 set effective_end_date = c_end_of_time
524 where current of csr_rows_owned_by_parent;
525 --
526 end if;
527 --
528 end loop remove_orphaned_rows;
529 --
530 end parent_deleted;
531 --------------------------------------------------------------------------------
532 function LINK_END_DATE (p_link_id number) return date is
533 --
534 --******************************************************************************
535 --* Returns the end date of the Link.
536 --******************************************************************************
537 v_link_end_date date;
538 --
539 cursor csr_link is
540 select max(effective_end_date)
541 from pay_element_links_f
542 where element_link_id = p_link_id;
543 --
544 begin
545 open csr_link;
546 fetch csr_link into v_link_end_date;
547 close csr_link;
548 return v_link_end_date;
549 end link_end_date;
550 --------------------------------------------------------------------------------
551 end PAY_LINK_INPUT_VALUES_PKG;
[Home] [Help]