1 package body PAY_STATUS_RULES_PKG as
2 /* $Header: pyspr.pkb 120.0.12020000.2 2012/07/05 02:45:23 amnaraya ship $ */
3 --
4 --------------------------------------------------------------------------------
5 g_dummy number(1) := null; -- dummy output from cursors
6 --------------------------------------------------------------------------------
7 --
8 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
9 X_Status_Processing_Rule_Id IN OUT NOCOPY NUMBER,
10 X_Effective_Start_Date DATE,
11 X_Effective_End_Date DATE,
12 X_Business_Group_Id NUMBER,
13 X_Legislation_Code VARCHAR2,
14 X_Element_Type_Id NUMBER,
15 X_Assignment_Status_Type_Id NUMBER,
16 X_Formula_Id NUMBER,
17 X_Processing_Rule VARCHAR2,
18 X_Comment_Id NUMBER,
19 X_Legislation_Subgroup VARCHAR2,
20 X_Last_Update_Date DATE,
21 X_Last_Updated_By NUMBER,
22 X_Last_Update_Login NUMBER,
23 X_Created_By NUMBER,
24 X_Creation_Date DATE) IS
25
26 CURSOR C IS SELECT rowid FROM pay_status_processing_rules_f
27 WHERE status_processing_rule_id= X_status_processing_rule_id
28 AND effective_start_date = X_Effective_Start_Date;
29
30
31
32 CURSOR C2 IS SELECT pay_status_processing_rules_s.nextval FROM sys.dual;
33 BEGIN
34
35 if (X_status_processing_rule_id is NULL) then
36 OPEN C2;
37 FETCH C2 INTO X_status_processing_rule_id;
38 CLOSE C2;
39 end if;
40 INSERT INTO pay_status_processing_rules_f(
41 status_processing_rule_id,
42 effective_start_date,
43 effective_end_date,
44 business_group_id,
45 legislation_code,
46 element_type_id,
47 assignment_status_type_id,
48 formula_id,
49 processing_rule,
50 comment_id,
51 legislation_subgroup,
52 last_update_date,
53 last_updated_by,
54 last_update_login,
55 created_by,
56 creation_date
57 ) VALUES (
58 X_Status_Processing_Rule_Id,
59 X_Effective_Start_Date,
60 X_Effective_End_Date,
61 X_Business_Group_Id,
62 X_Legislation_Code,
63 X_Element_Type_Id,
64 X_Assignment_Status_Type_Id,
65 X_Formula_Id,
66 X_Processing_Rule,
67 X_Comment_Id,
68 X_Legislation_Subgroup,
69 X_Last_Update_Date,
70 X_Last_Updated_By,
71 X_Last_Update_Login,
72 X_Created_By,
73 X_Creation_Date
74
75 );
76
77 OPEN C;
78 FETCH C INTO X_Rowid;
79 if (C%NOTFOUND) then
80 CLOSE C;
81 RAISE NO_DATA_FOUND;
82 end if;
83 CLOSE C;
84 END Insert_Row;
85 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
86 X_Status_Processing_Rule_Id NUMBER,
87 X_Effective_Start_Date DATE,
88 X_Effective_End_Date DATE,
89 X_Business_Group_Id NUMBER,
90 X_Legislation_Code VARCHAR2,
91 X_Element_Type_Id NUMBER,
92 X_Assignment_Status_Type_Id NUMBER,
93 X_Formula_Id NUMBER,
94 X_Processing_Rule VARCHAR2,
95 X_Comment_Id NUMBER,
96 X_Legislation_Subgroup VARCHAR2) IS
97 CURSOR C IS
98 SELECT *
99 FROM pay_status_processing_rules_f
100 WHERE rowid = X_Rowid
101 FOR UPDATE of status_processing_rule_id NOWAIT;
102 Recinfo C%ROWTYPE;
103 BEGIN
104 OPEN C;
105 FETCH C INTO Recinfo;
106 if (C%NOTFOUND) then
107 CLOSE C;
108 RAISE NO_DATA_FOUND;
109 end if;
110 CLOSE C;
111 if (
112 ( (Recinfo.status_processing_rule_id = X_Status_Processing_Rule_Id)
113 OR ( (Recinfo.status_processing_rule_id IS NULL)
114 AND (X_Status_Processing_Rule_Id IS NULL)))
115 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
116 OR ( (Recinfo.effective_start_date IS NULL)
117 AND (X_Effective_Start_Date IS NULL)))
118 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
119 OR ( (Recinfo.effective_end_date IS NULL)
120 AND (X_Effective_End_Date IS NULL)))
121 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
122 OR ( (Recinfo.business_group_id IS NULL)
123 AND (X_Business_Group_Id IS NULL)))
124 AND ( (Recinfo.legislation_code = X_Legislation_Code)
125 OR ( (Recinfo.legislation_code IS NULL)
126 AND (X_Legislation_Code IS NULL)))
127 AND ( (Recinfo.element_type_id = X_Element_Type_Id)
128 OR ( (Recinfo.element_type_id IS NULL)
129 AND (X_Element_Type_Id IS NULL)))
130 AND ( (Recinfo.assignment_status_type_id = X_Assignment_Status_Type_Id)
131 OR ( (Recinfo.assignment_status_type_id IS NULL)
132 AND (X_Assignment_Status_Type_Id IS NULL)))
133 AND ( (Recinfo.formula_id = X_Formula_Id)
134 OR ( (Recinfo.formula_id IS NULL)
135 AND (X_Formula_Id IS NULL)))
136 AND ( (Recinfo.processing_rule = X_Processing_Rule)
137 OR ( (Recinfo.processing_rule IS NULL)
138 AND (X_Processing_Rule IS NULL)))
139 AND ( (Recinfo.comment_id = X_Comment_Id)
140 OR ( (Recinfo.comment_id IS NULL)
141 AND (X_Comment_Id IS NULL)))
142 AND ( (Recinfo.legislation_subgroup = X_Legislation_Subgroup)
143 OR ( (Recinfo.legislation_subgroup IS NULL)
144 AND (X_Legislation_Subgroup IS NULL)))
145 ) then
146 return;
147 else
148 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
149 APP_EXCEPTION.RAISE_EXCEPTION;
150 end if;
151 END Lock_Row;
152
153 PROCEDURE Update_Row(X_Rowid VARCHAR2,
154 X_Status_Processing_Rule_Id NUMBER,
155 X_Effective_Start_Date DATE,
156 X_Effective_End_Date DATE,
157 X_Business_Group_Id NUMBER,
158 X_Legislation_Code VARCHAR2,
159 X_Element_Type_Id NUMBER,
160 X_Assignment_Status_Type_Id NUMBER,
161 X_Formula_Id NUMBER,
162 X_Processing_Rule VARCHAR2,
163 X_Comment_Id NUMBER,
164 X_Legislation_Subgroup VARCHAR2,
165 X_Last_Update_Date DATE,
166 X_Last_Updated_By NUMBER,
167 X_Last_Update_Login NUMBER
168 ) IS
169 BEGIN
170 UPDATE pay_status_processing_rules_f
171 SET
172
173 status_processing_rule_id = X_Status_Processing_Rule_Id,
174 effective_start_date = X_Effective_Start_Date,
175 effective_end_date = X_Effective_End_Date,
176 business_group_id = X_Business_Group_Id,
177 legislation_code = X_Legislation_Code,
178 element_type_id = X_Element_Type_Id,
179 assignment_status_type_id = X_Assignment_Status_Type_Id,
180 formula_id = X_Formula_Id,
181 processing_rule = X_Processing_Rule,
182 comment_id = X_Comment_Id,
183 legislation_subgroup = X_Legislation_Subgroup,
184 last_update_date = X_Last_Update_Date,
185 last_updated_by = X_Last_Updated_By,
186 last_update_login = X_Last_Update_Login
187 WHERE rowid = X_rowid;
188
189 if (SQL%NOTFOUND) then
190 RAISE NO_DATA_FOUND;
191 end if;
192
193 END Update_Row;
194
195 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
196 p_session_date date,
197 p_delete_mode varchar2,
198 p_status_processing_rule_id number) IS
199 BEGIN
200 DELETE FROM pay_status_processing_rules_f
201 WHERE rowid = X_Rowid;
202
203 if (SQL%NOTFOUND) then
204 RAISE NO_DATA_FOUND;
205 end if;
206 --
207 -- Cascade the action to formula result rules for this SPR
208 pay_formula_result_rules_pkg.parent_deleted (
209 --
210 'PAY_STATUS_PROCESSING_RULES_F',
211 p_status_processing_rule_id,
212 p_session_date,
213 p_delete_mode );
214 --
215 END Delete_Row;
216 --------------------------------------------------------------------------------
217 function SPR_END_DATE (p_status_processing_rule_id number,
218 p_formula_id number) return date is
219 --
220 --******************************************************************************
221 --*Returns the final date effective end date of the given status processing rule
222 --******************************************************************************
223 --
224 cursor csr_spr_end_date is
225 select max(effective_end_date)
226 from pay_status_processing_rules_f
227 where status_processing_rule_id = p_status_processing_rule_id;
228 --
229 cursor csr_spr_formula_end_date is
230 select min(effective_start_date) -1
231 from pay_status_processing_rules_f
232 where status_processing_rule_id = p_status_processing_rule_id
233 and formula_id <> p_formula_id;
234 --
235 v_date1 date;
236 v_date2 date := null;
237 v_end_date date;
238 --
239 begin
240 --
241 hr_utility.set_location ('PAY_STATUS_RULES_PKG',1);
242 --
243 open csr_spr_end_date;
244 fetch csr_spr_end_date into v_date1;
245 close csr_spr_end_date;
246 --
247 open csr_spr_formula_end_date;
248 fetch csr_spr_formula_end_date into v_date2;
249 if csr_spr_formula_end_date%notfound then
250 close csr_spr_formula_end_date;
251 end if;
252 close csr_spr_formula_end_date;
253 --
254 if v_date2 is not null and v_date2 < v_date1 then
255 v_end_date := v_date2;
256 else
257 v_end_date := v_date1;
258 end if;
259 --
260 return v_end_date;
261 --
262 end spr_end_date;
263 --------------------------------------------------------------------------------
264 procedure PARENT_DELETED (
265 --
266 --******************************************************************************
267 --* Handles the case when any row referenced by a foreign key of the base *
268 --* is deleted (in whatever Date Track mode). ie If a parent record is zapped *
269 --* then the deletion is cascaded; if it is date-effectively deleted, then the *
270 --* rows referencing it are updated to have the same end-date. *
271 --******************************************************************************
272 --
273 -- Parameters to be passed in are:
274 --
275 -- The value of the foreign key for the deleted parent
276 p_element_type_id number,
277 --
278 -- The date of date-effective deletion
279 p_session_date date default trunc (sysdate),
280 --
281 -- The type of deletion action being performed
282 p_delete_mode varchar2 default 'DELETE'
283 --
284 ) is
285 --
286 -- The following cursor fetches all rows identified by the foreign key to
287 -- the parent being deleted.
288 --
289 cursor csr_rows_owned_by_parent is
290 select *
291 from pay_status_processing_rules_f
292 where element_type_id = p_element_type_id
293 for update;
294 --
295 begin
296 --
297 hr_utility.set_location ('PAY_STATUS_RULES_PKG.PARENT_DELETED',1);
298 --
299 <<REMOVE_ORPHANED_ROWS>>
300 for fetched_rule in csr_rows_owned_by_parent LOOP
301 --
302 -- If in ZAP mode then all rows belonging to the deleted
303 -- parent must be deleted. If in DELETE (ie date-effective
304 -- delete) mode then only rows with a future start date
305 -- must be deleted, and current rows must be updated so
306 -- that their end dates match that of their closed-down
307 -- parent. Current and future are determined by session
308 -- date.
309 --
310 if p_delete_mode = 'ZAP' -- ie delete all rows
311 or (p_delete_mode = 'DELETE' -- ie delete all future rows
312 and fetched_rule.effective_start_date > p_session_date) then
313 --
314 hr_utility.set_location ('PAY_STATUS_RULES_PKG.PARENT_DELETED',2);
315 --
316 delete from pay_status_processing_rules_f
317 where current of csr_rows_owned_by_parent;
318 --
319 hr_utility.set_location ('PAY_STATUS_RULES_PKG.PARENT_DELETED',3);
320 --
321 delete from hr_application_ownerships
322 where key_name = 'STATUS_PROCESSING_RULE_ID'
323 and key_value = fetched_rule.status_processing_rule_id;
324 --
325 elsif p_delete_mode = 'DELETE'
326 and p_session_date between fetched_rule.effective_start_date
327 and fetched_rule.effective_end_date then
328 --
329 hr_utility.set_location ('PAY_STATUS_RULES_PKG.PARENT_DELETED',4);
330 --
331 update pay_status_processing_rules_f
332 set effective_end_date = p_session_date
333 where current of csr_rows_owned_by_parent;
334 --
335 end if;
336 --
337 hr_utility.set_location ('PAY_STATUS_RULES_PKG.PARENT_DELETED',5);
338 --
339 -- Cascade the action to formula result rules for this SPR
340 pay_formula_result_rules_pkg.parent_deleted (
341 --
342 'PAY_STATUS_PROCESSING_RULES_F',
343 fetched_rule.status_processing_rule_id,
344 p_session_date,
345 p_delete_mode );
346 --
347 end loop remove_orphaned_rows;
348 --
349 end parent_deleted;
350 --------------------------------------------------------------------------------
351 function NO_INPUT_VALUES_MATCH_FORMULA (
352 --
353 --******************************************************************************
354 --* Returns TRUE if ANY of the input values for the element do not match the
355 --* data type of any of the inputs of the selected formula OR if the formula
356 --* is still uncompiled so that the datatypes cannot be verified.
357 --* This is used to initiate a warning to the user that his selection may be
358 --* invalid, but because changes may be made before the processing rule is
359 --* applied, the selection is not prevented.
360 --******************************************************************************
361 --
362 -- Parameters are:
363 --
364 p_element_type_id number,
365 p_formula_id number) return boolean is
366 --
367 -- Returns a row if the datatypes correctly match between the formula
368 -- and the element
369 --
370 cursor csr_number_of_input_values is
371 select count( distinct iv.name)
372 from pay_input_values_f_tl IV_TL,
373 pay_input_values_f IV
374 where iv_tl.input_value_id = iv.input_value_id
375 and iv.element_type_id = p_element_type_id
376 and userenv('LANG') = iv_tl.language
377 and translate(upper(iv_tl.name),' ','_') in
378 (select item_name from ff_fdi_usages_f
379 where formula_id = p_formula_id);
380
381 cursor csr_matching_data_types is
382 select count(distinct fdi.ITEM_NAME)
383 from pay_input_values_f_tl IV_TL,
384 pay_input_values_f IV,
385 ff_fdi_usages_f FDI
386 where iv_tl.input_value_id = iv.input_value_id
387 and fdi.formula_id = p_formula_id
388 and userenv('LANG') = iv_tl.language
389 and fdi.usage in ( 'I', 'B' ) -- either input or in/output item
390 and iv.element_type_id = p_element_type_id
391 and translate (upper(iv_tl.name),' ','_')
392 = translate (upper(fdi.item_name),' ','_')
393 and ((fdi.data_type = 'D' and iv.uom = 'D')
394 or (fdi.data_type = 'T' and iv.uom = 'C')
395 or (fdi.data_type = 'N'
396 and substr(iv.uom,1,1) in ('H','I','M','N')));
397 -- Cursors rewritten for bug 436741
398 -- and exists ( -- input value with matching data type
399 --
400 -- select 1
401 -- from pay_input_values_f IV
402 -- where iv.element_type_id = p_element_type_id
403 -- and translate (upper(iv.name),' ','_')
404 -- = translate (upper(fdi.item_name),' ','_')
405 -- and ((fdi.data_type = 'D' and iv.uom = 'D')
406 -- or (fdi.data_type = 'T' and iv.uom = 'C')
407 -- or (fdi.data_type = 'N'
408 -- and substr(iv.uom,1,1)
409 -- in('H','I','M','N')))))
410 -- or (not exists ( -- a compiled version of the formula
411 --
412 -- select 1
413 -- from ff_fdi_usages compiled
414 -- where compiled.formula_id = p_formula_id)
415 -- and exists ( -- an uncompiled version of the formula
416 --
417 -- select 1
418 -- from ff_formulas_f formula
419 -- where formula.formula_id = p_formula_id));
420 --
421 v_number_to_match number(2) := 0;
422 v_number_of_matches number(2) := 0;
423 v_match_not_found boolean := FALSE;
424 --
425 begin
426 --
427 hr_utility.set_location('PAY_STATUS_RULES_PKG.NO_INPUT_VALUES_MATCH_FORMULA',1);
428 --
429 open csr_number_of_input_values;
430 fetch csr_number_of_input_values into v_number_to_match;
431 close csr_number_of_input_values;
432 --
433 hr_utility.set_location('PAY_STATUS_RULES_PKG.NO_INPUT_VALUES_MATCH_FORMULA',5);
434 open csr_matching_data_types;
435 fetch csr_matching_data_types into v_number_of_matches;
436 close csr_matching_data_types;
437 --
438 if v_number_to_match = v_number_of_matches and
439 v_number_of_matches <> 0 then
440 v_match_not_found := FALSE;
441 elsif v_number_to_match = 0 then
442 v_match_not_found := FALSE;
443 else
444 v_match_not_found := TRUE;
445 end if;
446 --
447 return v_match_not_found;
448 --
449 end no_input_values_match_formula;
450 --------------------------------------------------------------------------------
451 function DATE_EFFECTIVELY_UPDATED (
452 --
453 --******************************************************************************
454 --* Returns TRUE if the record has more than one date-effective row
455 --******************************************************************************
456 --
457 p_status_processing_rule_id number,
458 p_rowid varchar2) return boolean is
459 --
460 cursor csr_dated_updates is
461 select 1
462 from pay_status_processing_rules_f
463 where status_processing_rule_id = p_status_processing_rule_id
464 and rowid <> p_rowid;
465
466 date_effective_updates_exist boolean := FALSE;
467
468 begin
469
470 hr_utility.set_location ('PAY_STATUS_RULES_PKG.DATE_EFFECTIVELY_UPDATED',1);
471
472 open csr_dated_updates;
473 fetch csr_dated_updates into g_dummy;
474 date_effective_updates_exist := csr_dated_updates%found;
475 close csr_dated_updates;
476
477 return date_effective_updates_exist;
478
479 end date_effectively_updated;
480 --------------------------------------------------------------------------------
481 function RESULT_RULES_EXIST (
482 --******************************************************************************
483 --* Returns TRUE if the SPR has result rules within the date range specified
484 --******************************************************************************
485
486 p_status_processing_rule_id number,
487 p_start_date date,
488 p_end_date date) return boolean is
489
490 cursor csr_result_rules is
491 select 1
492 from pay_formula_result_rules_f
493 where status_processing_rule_id = p_status_processing_rule_id
494 and effective_start_date <= p_end_date
495 and effective_end_date >= p_start_date;
496
497 rules_exist boolean := FALSE;
498
499 begin
500
501 open csr_result_rules;
502 fetch csr_result_rules into g_dummy;
503 rules_exist := csr_result_rules%found;
504 close csr_result_rules;
505
506 return rules_exist;
507
508 end result_rules_exist;
509 ------------------------------------------------------------------------------
510 -- NAME --
511 -- pay_status_rules_pkg.status_rule_end_date --
512 -- --
513 -- DESCRIPTION --
514 -- Returns the correct end date for a status rule. It takes into account --
515 -- the end date of the formula and also any future status rules --
516 ------------------------------------------------------------------------------
517 --
518 function status_rule_end_date
519 (
520 p_status_processing_rule_id number,
521 p_element_type_id number,
522 p_formula_id number,
523 p_assignment_status_type_id number,
524 p_processing_rule varchar2,
525 p_session_date date,
526 p_max_element_end_date date,
527 p_validation_start_date date,
528 p_business_group_id number,
529 p_legislation_code varchar2
530 ) return date is
531 --
532 v_next_status_rule_start_date date;
533 v_max_formula_end_date date;
534 v_status_rule_end_date date;
535 --
536 begin
537 --
538 -- Get the start date of the earliest future status rule if it exists.
539 begin
540 select min(sprf.effective_start_date)
541 into v_next_status_rule_start_date
542 from pay_status_processing_rules_f sprf
543 where sprf.element_type_id = p_element_type_id
544 and nvl(sprf.assignment_status_type_id,0) = nvl(p_assignment_status_type_id,0)
545 and sprf.processing_rule = p_processing_rule
546 and sprf.effective_end_date >= p_session_date
547 and sprf.status_processing_rule_id <> nvl(p_status_processing_rule_id,0)
548 and (
549 --
550 -- The row on the database is 'Generic'
551 --
552 (sprf.business_group_id is null
553 and sprf.legislation_code is null)
554 --
555 -- The row to be inserted is 'Generic'
556 --
557 or (p_business_group_id is null
558 and p_legislation_code is null)
559 --
560 -- The bg of the row to be inserted conflicts with the bg
561 -- of an existing row or an existing legislation row with
562 -- the same legislation as the bg of the row being inserted.
563 --
564 or (p_business_group_id is not null
565 and (nvl(sprf.business_group_id,-1) = p_business_group_id
566 or nvl(sprf.legislation_code,'~') = p_legislation_code))
567 --
568 -- The legislation of the row to be inserted conflicts with an
569 -- existing legislative row or with the legislation of an existing
570 -- bg specific row.
571 --
572 or (p_business_group_id is null
573 and p_legislation_code is not null
574 and (p_legislation_code = nvl(sprf.legislation_code,'~')
575 or p_legislation_code = (select legislation_code
576 from per_business_groups
577 where business_group_id = nvl(sprf.business_group_id,-1))))
578 );
579 exception
580 when no_data_found then
581 null;
582 end;
583 --
584 -- If there are no future status rules , get the max end date of the
585 -- formula.
586 if v_next_status_rule_start_date is null then
587 begin
588 select max(ff.effective_end_date)
589 into v_max_formula_end_date
590 from ff_formulas_f ff
591 where ff.formula_id = p_formula_id;
592 exception
593 when no_data_found then
594 null;
595 end;
596 if v_max_formula_end_date is not null and
597 v_max_formula_end_date <= p_max_element_end_date then
598 v_status_rule_end_date := v_max_formula_end_date;
599 else
600 v_status_rule_end_date := p_max_element_end_date;
601 end if;
602 else
603 v_status_rule_end_date := v_next_status_rule_start_date - 1;
604 end if;
605 --
606 -- Trying to open up a status rule that would either overlap with an existing
607 -- status rule or extend beyond the lifetime of the formula or element type.
608 if v_status_rule_end_date < p_validation_start_date then
609 if v_next_status_rule_start_date is null and
610 v_max_formula_end_date is null then
611 --- Trying to extend beyond life of element
612 hr_utility.set_message(801, 'HR_34858_RULE_PAST_ELEMENT');
613 elsif v_next_status_rule_start_date is null and
614 v_max_formula_end_date <= p_max_element_end_date then
615 -- Trying to extend beyond life of the formula
616 hr_utility.set_message(801, 'HR_34857_RULE_PAST_FORMULA');
617 else
618 -- Trying to extend beyond life of status rule causing overlap
619 hr_utility.set_message(801, 'HR_34856_STATUS_RULE_FUT_EXIST');
620 end if;
621 hr_utility.raise_error;
622 end if;
623 --
624 return v_status_rule_end_date;
625 --
626 end status_rule_end_date;
627 --------------------------------------------------------------------------------
628 end PAY_STATUS_RULES_PKG;
[Home] [Help]