1 package body PAY_FORMULA_RESULT_RULES_PKG as
2 /* $Header: pyfrr.pkb 115.6 2002/12/10 18:44:52 dsaxby ship $ */
3 -------------------------------------------------------------------------------
4 --------------------------------------------------------------------------------
5 g_dummy number(1); -- Dummy for cursor returns which are not needed
6 --------------------------------------------------------------------------------
7 procedure CHECK_UNIQUE (
8 --
9 --******************************************************************************
10 --* Performs checks for uniqueness of result rules. Each rule type is tested *
11 --* in separate cursors to simplify the code and to modularise the procedure. *
12 --* The procedure will work for both insert and update tests as it tests rowid.*
13 --******************************************************************************
14 --
15 p_status_processing_rule_id number,
16 p_result_rule_type varchar2,
17 p_result_name varchar2,
18 p_effective_end_date date,
19 p_session_date date,
20 p_rowid varchar2 default null,
21 p_element_type_id number default null,
22 p_input_value_id number default null) is
23 --
24 function DIRECT_RESULT_RULE_NOT_UNIQUE
25 -- Returns TRUE if the tested direct rule already exists
26 -- Only one direct result rule is allowed for each SPR
27 return boolean is
28 v_duplicate_found boolean := FALSE;
29 cursor csr_duplicate_rule is
30 select 1
31 from PAY_FORMULA_RESULT_RULES_F
32 where status_processing_rule_id = p_status_processing_rule_id
33 and result_rule_type = 'D'
34 and (p_rowid is null
35 or (p_rowid is not null and p_rowid <> rowid))
36 and effective_start_date <=p_effective_end_date
37 and effective_end_date >=p_session_date;
38 begin
39 --
40 hr_utility.set_location ('pay_formula_result_rules_pkg.check_unique',3);
41 --
42 open csr_duplicate_rule;
43 fetch csr_duplicate_rule into g_dummy;
44 v_duplicate_found := csr_duplicate_rule%found;
45 close csr_duplicate_rule;
46 --
47 return v_duplicate_found;
48 --
49 end direct_result_rule_not_unique;
50 --
51 function MESSAGE_RULE_NOT_UNIQUE
52 -- Returns TRUE if the tested message rule already exists
53 -- Only one message rule is allowed for each SPR/result name combination
54 return boolean is
55 v_duplicate_found boolean := FALSE;
56 cursor csr_duplicate_rule is
57 select 1
58 from pay_formula_result_rules_f
59 where status_processing_rule_id = p_status_processing_rule_id
60 and result_rule_type = 'M'
61 and result_name = p_result_name
62 and effective_start_date <=p_effective_end_date
63 and effective_end_date >=p_session_date
64 and (p_rowid is null
65 or (p_rowid is not null and p_rowid <> rowid));
66 begin
67 --
68 hr_utility.set_location ('pay_formula_result_rules_pkg.check_unique',4);
69 --
70 open csr_duplicate_rule;
71 fetch csr_duplicate_rule into g_dummy;
72 v_duplicate_found := csr_duplicate_rule%found;
73 close csr_duplicate_rule;
74 --
75 return v_duplicate_found;
76 --
77 end message_rule_not_unique;
78 --
79 function STOP_ENTRY_RULE_NOT_UNIQUE
80 -- Returns TRUE if the tested stop-entry rule already exists
81 -- Only one stop-entry rule is allowed for each combination of
82 -- result name, SPR and element type
83 return boolean is
84 v_duplicate_found boolean := FALSE;
85 cursor csr_duplicate_rule is
86 select 1
87 from pay_formula_result_rules_f
88 where status_processing_rule_id = p_status_processing_rule_id
89 and result_rule_type = 'S'
90 and result_name = p_result_name
91 and (p_rowid is null
92 or (p_rowid is not null and p_rowid <> rowid))
93 and effective_start_date <=p_effective_end_date
94 and effective_end_date >=p_session_date
95 and element_type_id = p_element_type_id;
96 begin
97 --
98 hr_utility.set_location ('pay_formula_result_rules_pkg.check_unique',5);
99 --
100 open csr_duplicate_rule;
101 fetch csr_duplicate_rule into g_dummy;
102 v_duplicate_found := csr_duplicate_rule%found;
103 close csr_duplicate_rule;
104 --
105 return v_duplicate_found;
106 --
107 end stop_entry_rule_not_unique;
108 --
109 function OTHER_RULE_TYPE_NOT_UNIQUE
110 -- Returns TRUE if any duplicate rule/rule-type/input-value is found
111 -- Only one indirect and one update-recurring rule is allowed for each
112 -- combination of SPR, result name and input value
113 return boolean is
114 v_duplicate_found boolean := FALSE;
115 cursor csr_duplicate_rule is
116 select 1
117 from pay_formula_result_rules_f
118 where status_processing_rule_id = p_status_processing_rule_id
119 and result_rule_type = p_result_rule_type
120 and result_name = p_result_name
121 and input_value_id = p_input_value_id
122 and (p_rowid is null
123 or (p_rowid is not null and p_rowid <> rowid))
124 and effective_start_date <=p_effective_end_date
125 and effective_end_date >=p_session_date;
126 begin
127 --
128 hr_utility.set_location ('pay_formula_result_rules_pkg.check_unique',6);
129 --
130 open csr_duplicate_rule;
131 fetch csr_duplicate_rule into g_dummy;
132 v_duplicate_found := csr_duplicate_rule%found;
133 close csr_duplicate_rule;
134 --
135 return v_duplicate_found;
136 --
137 end other_rule_type_not_unique;
138 --
139 begin
140 --
141 hr_utility.set_location ('pay_formula_result_rules_pkg.check_unique',1);
142 --
143 --if (p_result_rule_type = 'D' and direct_result_rule_not_unique) then
144 --
145 -- hr_utility.set_message (801, 'HR_6503_FF_DIRECT');
146 --hr_utility.raise_error;
147 --
148 if (p_result_rule_type = 'M' and message_rule_not_unique)
149 or (p_result_rule_type = 'S' and stop_entry_rule_not_unique)
150 or (p_result_rule_type in('U','I','D') and other_rule_type_not_unique) then
151 --
152 hr_utility.set_message (801, 'HR_6478_FF_UNI_FRR');
153 hr_utility.raise_error;
154 --
155 end if;
156 --
157 end check_unique;
158 --------------------------------------------------------------------------------
159 function TARGET_PAY_VALUE (
160 --*******************************************************************************
161 --* Returns the ID of the pay value for the target element of the result rule. *
162 --* This is needed for the input value in the case of direct result rules, *
163 --* because we do not allow the user to select the input value himself. *
164 --* NB The element type passed in MUST be that of the element type from the *
165 --* parent SPR and not one selected by the user as the element type for the *
166 --* result rule. *
167 --*******************************************************************************
168 --
169 --
170 p_element_type_id number,
171 p_result_data_type varchar2) return number is
172 --
173 v_pay_value varchar2(80) := hr_general.pay_value;
174 v_input_value_id number(10);
175 --
176 cursor csr_input_value is
177 select ipv.input_value_id
178 from pay_input_values_f_tl ipv_tl,
179 pay_input_values_f ipv
180 where ipv_tl.input_value_id = ipv.input_value_id
181 and userenv('LANG') = ipv_tl.language
182 and ipv.element_type_id = p_element_type_id
183 and ipv_tl.name = v_pay_value
184 and ((p_result_data_type = 'D' and ipv.uom = 'D')
185 or (p_result_data_type = 'T' and ipv.uom = 'C')
186 or (p_result_data_type = 'N' and substr(ipv.uom,1,1) in ('H','I','M','N')));
187
188 --
189 begin
190 --
191 hr_utility.set_location ('pay_formula_result_rules_pkg.target_pay_value',1);
192 --
193 open csr_input_value;
194 fetch csr_input_value into v_input_value_id;
195 --
196 if csr_input_value%notfound then
197 close csr_input_value;
198 hr_utility.set_message (801,'HR_6501_FF_INVALID_UOM');
199 hr_utility.raise_error;
200 else
201 close csr_input_value;
202 end if;
203 --
204 return v_input_value_id;
205 --
206 end target_pay_value;
207 --------------------------------------------------------------------------------
208 PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
209 p_Formula_Result_Rule_Id IN OUT NOCOPY NUMBER,
210 p_Effective_Start_Date DATE,
211 p_Effective_End_Date DATE,
212 p_Business_Group_Id NUMBER,
213 p_Legislation_Code VARCHAR2,
214 p_Element_Type_Id NUMBER,
215 p_Status_Processing_Rule_Id NUMBER,
216 p_Result_Name VARCHAR2,
217 p_Result_Rule_Type VARCHAR2,
218 p_Legislation_Subgroup VARCHAR2,
219 p_Severity_Level VARCHAR2,
220 p_Input_Value_Id NUMBER,
221 p_Created_By NUMBER,
222 p_session_date date
223 ) IS
224 CURSOR C IS SELECT rowid FROM pay_formula_result_rules_f
225 WHERE formula_result_rule_id = p_formula_result_rule_id
226 AND effective_start_date = p_Effective_Start_Date;
227
228
229
230 CURSOR C2 IS SELECT pay_formula_result_rules_s.nextval FROM sys.dual;
231 BEGIN
232 --
233 hr_utility.set_location ('pay_formula_result_rules_pkg.insert_row',1);
234 --
235 check_unique (p_status_processing_rule_id,
236 p_result_rule_type,
237 p_result_name,
238 p_effective_end_date,
239 p_session_date,
240 p_rowid,
241 p_element_type_id,
242 p_input_value_id) ;
243 --
244 hr_utility.set_location ('pay_formula_result_rules_pkg.insert_row',2);
245 --
246 if (p_formula_result_rule_id is NULL) then
247 OPEN C2;
248 FETCH C2 INTO p_formula_result_rule_id;
249 CLOSE C2;
250 end if;
251 --
252 hr_utility.set_location ('pay_formula_result_rules_pkg.insert_row',3);
253 --
254 insert into pay_formula_result_rules_f(
255 formula_result_rule_id,
256 effective_start_date,
257 effective_end_date,
258 business_group_id,
259 legislation_code,
260 element_type_id,
261 status_processing_rule_id,
262 result_name,
263 result_rule_type,
264 legislation_subgroup,
265 severity_level,
266 input_value_id,
267 creation_date
268 )
269 values (
270 p_Formula_Result_Rule_Id,
271 p_Effective_Start_Date,
272 p_Effective_End_Date,
273 p_Business_Group_Id,
274 p_Legislation_Code,
275 p_Element_Type_Id,
276 p_Status_Processing_Rule_Id,
277 p_Result_Name,
278 p_Result_Rule_Type,
279 p_Legislation_Subgroup,
280 p_Severity_Level,
281 p_Input_Value_Id,
282 sysdate);
283
284 --
285 hr_utility.set_location ('pay_formula_result_rules_pkg.insert_row',4);
286 --
287 OPEN C;
288 FETCH C INTO p_Rowid;
289 if (C%NOTFOUND) then
290 CLOSE C;
291 RAISE NO_DATA_FOUND;
292 end if;
293 CLOSE C;
294 END Insert_Row;
295 PROCEDURE Lock_Row(p_Rowid VARCHAR2,
296 p_Formula_Result_Rule_Id NUMBER,
297 p_Effective_Start_Date DATE,
298 p_Effective_End_Date DATE,
299 p_Business_Group_Id NUMBER,
300 p_Legislation_Code VARCHAR2,
301 p_Element_Type_Id NUMBER,
302 p_Status_Processing_Rule_Id NUMBER,
303 p_Result_Name VARCHAR2,
304 p_Result_Rule_Type VARCHAR2,
305 p_Legislation_Subgroup VARCHAR2,
306 p_Severity_Level VARCHAR2,
307 p_Input_Value_Id NUMBER
308 ) IS
309 CURSOR C IS
310 SELECT *
311 FROM pay_formula_result_rules_f
312 WHERE rowid = p_Rowid
313 FOR UPDATE of formula_result_rule_id NOWAIT;
314 Recinfo C%ROWTYPE;
315 BEGIN
316 OPEN C;
317 FETCH C INTO Recinfo;
318 if (C%NOTFOUND) then
319 CLOSE C;
320 RAISE NO_DATA_FOUND;
321 end if;
322 CLOSE C;
323 if (
324 ( (Recinfo.formula_result_rule_id = p_Formula_Result_Rule_Id)
325 OR ( (Recinfo.formula_result_rule_id IS NULL)
326 AND (p_Formula_Result_Rule_Id IS NULL)))
327 AND ( (Recinfo.effective_start_date = p_Effective_Start_Date)
328 OR ( (Recinfo.effective_start_date IS NULL)
329 AND (p_Effective_Start_Date IS NULL)))
330 AND ( (Recinfo.effective_end_date = p_Effective_End_Date)
331 OR ( (Recinfo.effective_end_date IS NULL)
332 AND (p_Effective_End_Date IS NULL)))
333 AND ( (Recinfo.business_group_id = p_Business_Group_Id)
334 OR ( (Recinfo.business_group_id IS NULL)
335 AND (p_Business_Group_Id IS NULL)))
336 AND ( (Recinfo.legislation_code = p_Legislation_Code)
337 OR ( (Recinfo.legislation_code IS NULL)
338 AND (p_Legislation_Code IS NULL)))
339 AND ( (Recinfo.element_type_id = p_Element_Type_Id)
340 OR ( (Recinfo.element_type_id IS NULL)
341 AND (p_Element_Type_Id IS NULL)))
342 AND ( (Recinfo.status_processing_rule_id = p_Status_Processing_Rule_Id)
343 OR ( (Recinfo.status_processing_rule_id IS NULL)
344 AND (p_Status_Processing_Rule_Id IS NULL)))
345 AND ( (Recinfo.result_name = p_Result_Name)
346 OR ( (Recinfo.result_name IS NULL)
347 AND (p_Result_Name IS NULL)))
348 AND ( (Recinfo.result_rule_type = p_Result_Rule_Type)
349 OR ( (Recinfo.result_rule_type IS NULL)
350 AND (p_Result_Rule_Type IS NULL)))
351 AND ( (Recinfo.legislation_subgroup = p_Legislation_Subgroup)
352 OR ( (Recinfo.legislation_subgroup IS NULL)
353 AND (p_Legislation_Subgroup IS NULL)))
354 AND ( (Recinfo.severity_level = p_Severity_Level)
355 OR ( (Recinfo.severity_level IS NULL)
356 AND (p_Severity_Level IS NULL)))
357 AND ( (Recinfo.input_value_id = p_Input_Value_Id)
358 OR ( (Recinfo.input_value_id IS NULL)
359 AND (p_Input_Value_Id IS NULL)))
360 ) then
364 APP_EXCEPTION.RAISE_EXCEPTION;
361 return;
362 else
363 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
365 end if;
366 END Lock_Row;
367
368 PROCEDURE Update_Row(p_Rowid VARCHAR2,
369 p_Formula_Result_Rule_Id NUMBER,
370 p_Effective_Start_Date DATE,
371 p_Effective_End_Date DATE,
372 p_Business_Group_Id NUMBER,
373 p_Legislation_Code VARCHAR2,
374 p_Element_Type_Id NUMBER,
375 p_Status_Processing_Rule_Id NUMBER,
376 p_Result_Name VARCHAR2,
377 p_Result_Rule_Type VARCHAR2,
378 p_Legislation_Subgroup VARCHAR2,
379 p_Severity_Level VARCHAR2,
380 p_Input_Value_Id NUMBER,
381 p_Last_Update_Date DATE,
382 p_Last_Updated_By NUMBER,
383 p_Last_Update_Login NUMBER
384 ) IS
385 BEGIN
386 UPDATE pay_formula_result_rules_f
387 SET
388
389 formula_result_rule_id = p_Formula_Result_Rule_Id,
390 effective_start_date = p_Effective_Start_Date,
391 effective_end_date = p_Effective_End_Date,
392 business_group_id = p_Business_Group_Id,
393 legislation_code = p_Legislation_Code,
394 element_type_id = p_Element_Type_Id,
395 status_processing_rule_id = p_Status_Processing_Rule_Id,
396 result_name = p_Result_Name,
397 result_rule_type = p_Result_Rule_Type,
398 legislation_subgroup = p_Legislation_Subgroup,
399 severity_level = p_Severity_Level,
400 input_value_id = p_Input_Value_Id,
401 last_update_date = p_Last_Update_Date,
402 last_updated_by = p_Last_Updated_By,
403 last_update_login = p_Last_Update_Login
404 WHERE rowid = p_rowid;
405
406 if (SQL%NOTFOUND) then
407 RAISE NO_DATA_FOUND;
408 end if;
409
410 END Update_Row;
411 --------------------------------------------------------------------------------
412
413 PROCEDURE Delete_Row(p_Rowid VARCHAR2) IS
414 BEGIN
415 DELETE FROM pay_formula_result_rules_f
416 WHERE rowid = p_Rowid;
417
418 if (SQL%NOTFOUND) then
419 RAISE NO_DATA_FOUND;
420 end if;
421 END Delete_Row;
422 --------------------------------------------------------------------------------
423 procedure PARENT_DELETED (
424 --
425 --******************************************************************************
426 --* Handles the case when any row referenced by a foreign key of the base *
427 --* is deleted (in whatever Date Track mode). ie If a parent record is zapped *
428 --* then the deletion is cascaded; if it is date-effectively deleted, then the *
429 --* rows referencing it are updated to have the same end-date. *
430 --******************************************************************************
431 --
432 -- Parameters to be passed in are:
433 --
434 p_parent_name varchar2, -- The name of the parent entity
435 p_parent_id number,-- The foreign key for the deleted parent
436 p_session_date date,
437 p_delete_mode varchar2
438 ) is
439 --
440 -- The following cursor fetches all rows identified by the foreign key to
441 -- the parent being deleted. The parent name identifies foreign key column
442 -- to use, thus the procedure is generic to any parent deletion
443 --
444 cursor csr_rows_owned_by_parent is
445 select rowid,pay_formula_result_rules_f.*
446 from pay_formula_result_rules_f
447 where p_parent_id = decode (p_parent_name,
448 'PAY_STATUS_PROCESSING_RULES_F',status_processing_rule_id,
449 'PAY_ELEMENT_TYPES_F',element_type_id,
450 'PAY_INPUT_VALUES_F',input_value_id)
451 for update;
452 --
453 begin
454 hr_utility.set_location ('pay_sub_class_rules_pkg.parent_deleted',1);
455 --
456 <<REMOVE_ORPHANED_ROWS>>
457 for fetched_row in csr_rows_owned_by_parent LOOP
458 --
459 -- If in ZAP mode then all rows belonging to the deleted
460 -- parent must be deleted. If in DELETE (ie date-effective
461 -- delete) mode then only rows with a future start date
462 -- must be deleted, and current rows must be updated so
463 -- that their end dates match that of their closed-down
464 -- parent. Current and future are determined by session
465 -- date.
466 --
467 if p_delete_mode = 'ZAP' -- ie delete all rows
468 or (p_delete_mode = 'DELETE' -- ie delete all future rows
469 and fetched_row.effective_start_date > p_session_date) then
470 --
471 -- Do not allow zapping of result rules which target the parent element
475 and p_parent_name = 'PAY_ELEMENT_TYPES_F' then
472 -- (and thereby prevent zapping of the parent element)
473
474 if p_delete_mode = 'ZAP' and fetched_row.result_rule_type = 'S'
476 hr_utility.set_message (801,'PAY_6157_ELEMENT_NO_DEL_FRR');
477 hr_utility.raise_error;
478 end if;
479 --
480 delete from pay_formula_result_rules_f
481 where current of csr_rows_owned_by_parent;
482 --
483 delete from hr_application_ownerships
484 where key_name = 'FORMULA_RESULT_RULE_ID'
485 and key_value = fetched_row.formula_result_rule_id;
486 --
487 elsif p_delete_mode = 'DELETE'
488 and p_session_date between fetched_row.effective_start_date
489 and fetched_row.effective_end_date then
490 --
491 update pay_formula_result_rules_f
492 set effective_end_date = p_session_date
493 where current of csr_rows_owned_by_parent;
494 --
495 elsif p_delete_mode = 'DELETE_NEXT_CHANGE'
496 and p_parent_name = 'PAY_STATUS_PROCESSING_RULES_F' then
497 --
498 -- Do not allow delete-next-change to orphan result rules
499 --
500 hr_utility.set_message (801,'HR_7451_SPR_NO_DEL_NEXT_CHANGE');
501 hr_utility.raise_error;
502 --
503 end if;
504 --
505 end loop remove_orphaned_rows;
506 --
507 end parent_deleted;
508 ------------------------------------------------------------------------------
509 -- NAME --
510 -- pay_formula_result_rules_pkg.result_rule_end_date --
511 -- --
512 -- DESCRIPTION --
513 -- Returns the correct end date for a result rule. It takes into account --
514 -- the end date of any future result rules and corresponding business rules --
515 ------------------------------------------------------------------------------
516 --
517 function result_rule_end_date
518 (
519 p_formula_result_rule_id number,
520 p_result_rule_type varchar2,
521 p_result_name varchar2,
522 p_status_processing_rule_id number,
523 p_element_type_id number,
524 p_input_value_id number,
525 p_session_date date,
526 p_max_spr_end_date date
527 ) return date is
528 --
529 v_result_rule_end_date date;
530 --
531 cursor c_direct_rule is
532 select min(frr.effective_start_date) -1
533 from pay_formula_result_rules_f frr
534 where frr.status_processing_rule_id = p_status_processing_rule_id
535 and frr.result_rule_type = 'D'
536 and frr.formula_result_rule_id <> nvl(p_formula_result_rule_id,0)
537 and frr.effective_end_date >= p_session_date;
538 --
539 cursor c_message_rule is
540 select min(frr.effective_start_date) -1
541 from pay_formula_result_rules_f frr
542 where frr.status_processing_rule_id = p_status_processing_rule_id
543 and frr.result_rule_type = 'M'
544 and frr.result_name = p_result_name
545 and frr.formula_result_rule_id <> nvl(p_formula_result_rule_id,0)
546 and frr.effective_end_date >= p_session_date;
547 --
548 cursor c_stop_entry is
549 select min(frr.effective_start_date) -1
550 from pay_formula_result_rules_f frr
551 where frr.status_processing_rule_id = p_status_processing_rule_id
552 and frr.result_rule_type = 'S'
553 and frr.result_name = p_result_name
554 and frr.element_type_id = p_element_type_id
555 and frr.formula_result_rule_id <> nvl(p_formula_result_rule_id,0)
556 and frr.effective_end_date >= p_session_date;
557 --
558 cursor c_other_rules is
559 select min(frr.effective_start_date) -1
560 from pay_formula_result_rules_f frr
561 where frr.status_processing_rule_id = p_status_processing_rule_id
562 and frr.result_rule_type = p_result_rule_type
563 and frr.result_name = p_result_name
564 and frr.input_value_id = p_input_value_id
565 and frr.formula_result_rule_id <> nvl(p_formula_result_rule_id,0)
566 and frr.effective_end_date >= p_session_date;
567 --
568 begin
569 --
570 -- if p_result_rule_type = 'D' then
571 -- open c_direct_rule;
572 -- fetch c_direct_rule into v_result_rule_end_date;
573 -- if c_direct_rule%notfound then
574 -- close c_direct_rule;
575 -- end if;
576 -- close c_direct_rule;
577 if p_result_rule_type = 'M' then
578 open c_message_rule;
579 fetch c_message_rule into v_result_rule_end_date;
580 if c_message_rule%notfound then
581 close c_message_rule;
582 end if;
583 close c_message_rule;
584 elsif p_result_rule_type = 'S' then
585 open c_stop_entry;
586 fetch c_stop_entry into v_result_rule_end_date;
587 if c_stop_entry%notfound then
588 close c_stop_entry;
589 end if;
590 close c_stop_entry;
591 else
592 open c_other_rules;
593 fetch c_other_rules into v_result_rule_end_date;
594 if c_other_rules%notfound then
595 close c_other_rules;
596 end if;
597 close c_other_rules;
598 end if;
599 --
603 --
600 if v_result_rule_end_date is null then
601 v_result_rule_end_date := p_max_spr_end_date;
602 end if;
604 return v_result_rule_end_date;
605 --
606 end result_rule_end_date;
607 --------------------------------------------------------------------------------
608 ------------------------------------------------------------------------------
609 -- NAME --
610 -- pay_formula_result_rules_pkg.formula_results_changed --
611 -- --
612 -- DESCRIPTION --
613 -- Returns 'TRUE' if the formula has been changed to return different --
614 -- results which will now invalidate result rules. --
615 ------------------------------------------------------------------------------
616 --
617 function formula_results_changed
618 (
619 p_formula_id number,
620 p_result_name varchar2,
621 p_result_rule_type varchar2,
622 p_effective_start_date date,
623 p_effective_end_date date
624 ) return boolean is
625 --
626 cursor c_results_changed is
627 select 1
628 from dual
629 where p_result_name not in (select ffu.item_name
630 from ff_fdi_usages_f ffu
631 where ffu.formula_id = p_formula_id
632 and ffu.usage in ('O','B')
633 and effective_start_date <= p_effective_end_date
634 and effective_end_date >= p_effective_start_date
635 and (ffu.data_type = 'N'
636 or p_result_rule_type is null
637 or p_result_rule_type <> 'O'));
638 --
639 results_changed boolean := FALSE;
640 --
641 begin
642 --
643 open c_results_changed;
644 fetch c_results_changed into g_dummy;
645 results_changed := c_results_changed%found;
646 close c_results_changed;
647 --
648 return results_changed;
649 end formula_results_changed;
650 --------------------------------------------------------------------------------
651 end PAY_FORMULA_RESULT_RULES_PKG;
[Home] [Help]