1 PACKAGE BODY FF_FORMULAS_F_PKG as
2 /* $Header: fffra01t.pkb 120.1 2005/07/29 04:55:46 shisriva noship $ */
3 --
4 g_dummy number(1); -- Dummy for cursor returns which are not needed
5 --
6 -----------------------------------------------------------------------------
7 -- Name --
8 -- payroll_del_validation --
9 -- Purpose --
10 -- Provides referential integrity checks for payroll tables using --
11 -- formula when a formula is deleted. --
12 -- Arguments --
13 -- See below. --
14 -- Notes --
15 -- None. --
16 -- History --
17 -- 13-Sep-04
18 -- Added delete integrity checks for the following tables --
19 -- 1. PAY_AU_MODULES --
20 -- 2. PAY_SHADOW_ELEMENT_TYPES --
21 -- 3. PER_CAGR_ENTITLEMENT_ITEMS --
22 -- 4. PER_CAGR_RETAINED_RIGHTS --
23 -- 5. PER_CAGR_ENTITLEMENTS --
24 -- 6. PER_CAGR_ENTITLEMENT_RESULTS --
25 -- 7. PAY_ACCRUAL_PLANS --
26 -- Included the delete integrity check for proration formula in --
27 -- csr_element_type cursor and added input parameter p_formula_details. --
28 -- Bug No 3703492
29 --
30 --For MLS-----------------------------------------------------------------------
31 g_dummy_number number (30);
32 g_business_group_id number(15); -- For validating translation.
33 g_legislation_code varchar2(150); -- For validating translation.
34
35 --------------------------------------------------------------------------------
36 -----------------------------------------------------------------------------
37 --
38 procedure payroll_del_validation
39 (
40 p_formula_id number,
41 p_dt_delete_mode varchar2,
42 p_validation_start_date date,
43 p_validation_end_date date,
44 p_formula_details FormulaRec
45 ) Is
46
47 --
48 v_dummy number;
49 l_formula_type_name ff_formula_types.formula_type_name%type;
50
51 --
52 -- Cursor to check for presence of formula in ff_qp_reports table.
53 --
54 cursor csr_qp_report is
55 select fqr.formula_id
56 from ff_qp_reports fqr
57 where fqr.formula_id = p_formula_id;
58 --
59 -- Cursor to check if the formula is referenced in hr_assignment_sets table.
60 --
61 cursor csr_assignment_set is
62 SELECT has.formula_id
63 from hr_assignment_sets has
64 where has.formula_id = p_formula_id;
65 --
66 -- Cursor to check if the formula is referenced in pay_user_columns table.
67 --
68 cursor csr_user_column is
69 select puc.formula_id
70 from pay_user_columns puc
71 where puc.formula_id = p_formula_id;
72 --
73 -- Cursor to check if the formula is referenced in pay_input_vales_f table.
74 --
75 cursor csr_input_value is
76 select piv.formula_id
77 from pay_input_values_f piv
78 where piv.formula_id = p_formula_id
79 and piv.effective_start_date <= p_validation_end_date
80 and piv.effective_end_date >= p_validation_start_date;
81 --
82 -- Cursor to check if the formula is referenced in pay_status_processing_rules_f table.
83 --
84 cursor csr_stat_proc_rule is
85 select psr.formula_id
86 from pay_status_processing_rules_f psr
87 where psr.formula_id = p_formula_id
88 and psr.effective_start_date <= p_validation_end_date
89 and psr.effective_end_date >= p_validation_start_date;
90
91 --
92 -- Cursor to check if the formula is referenced in pay_magnetic_records table.
93 --
94 cursor csr_magnetic_record is
95 select pmr.formula_id
96 from pay_magnetic_records pmr
97 where pmr.formula_id = p_formula_id;
98
99
100 --
101 -- Cursor to check if the formula is referenced in pay_accrual_plans table.
102 --
103 cursor csr_accrual_plans is
104 select 1
105 from pay_accrual_plans pap
106 where pap.accrual_formula_id = p_formula_id
107 or pap.ineligibility_formula_id = p_formula_id
108 or pap.co_formula_id = p_formula_id
109 or pap.payroll_formula_id = p_formula_id;
110
111 --
112 -- Cursor to check if the formula is referenced in pay_element_types_f table.
113 --
114 cursor csr_element_type is
115 select 1
116 from pay_element_types_f pet
117 where ( pet.proration_formula_id = p_formula_id
118 or pet.formula_id = p_formula_id
119 or pet.iterative_formula_id = p_formula_id )
120 and pet.effective_start_date <= p_validation_end_date
121 and pet.effective_end_date >= p_validation_start_date;
122
123 --
124 -- Cursor to check if the formuls is referenced in per_cagr_entitlements table.
125 --
126 cursor csr_per_cagr_entitlements is
127 select pce.formula_id
128 from per_cagr_entitlements pce
129 where pce.formula_id = p_formula_id
130 and pce.start_date <= p_validation_end_date
131 and (pce.end_date is null or pce.end_date >= p_validation_start_date );
132
133 --
134 -- Cursor to check if the formula is referenced in per_cagr_entitlement_items table.
135 --
136 cursor csr_per_cagr_entitle_items is
137 select pcei.beneficial_formula_id
138 from per_cagr_entitlement_items pcei
139 where pcei.beneficial_formula_id = p_formula_id;
140
141 --
142 -- Cursor to check if the formula is referenced in per_cagr_entitlement_results.
143 --
144 cursor csr_per_cagr_entitle_results is
145 select pcer.formula_id
146 from per_cagr_entitlement_results pcer
147 where pcer.formula_id = p_formula_id
148 and pcer.start_date <= p_validation_end_date
149 and (pcer.end_date is null or pcer.end_date >= p_validation_start_date );
150
151 --
152 -- Cursor to check if the formula is referenced in per_cagr_retained_rights table.
153 --
154 cursor csr_cagr_retained_rights is
155 select pcrr.formula_id
156 from per_cagr_retained_rights pcrr
157 where pcrr.formula_id = p_formula_id
158 and pcrr.start_date <= p_validation_end_date
159 and (pcrr.end_date is null or pcrr.end_date >= p_validation_start_date );
160
161
162 --
163 -- Cursor to check if the formula is referenced in pay_au_modules table.
164 --
165 cursor csr_au_modules is
166 select 1
167 from pay_au_modules pam
168 where pam.formula_name = p_formula_details.formula_name
169 and (
170 (pam.legislation_code is null and pam.business_group_id is null)
171
172 or (pam.business_group_id is null and pam.legislation_code=p_formula_details.legislation_code)
173
174 or (pam.legislation_code is null and pam.business_group_id=p_formula_details.business_group_id)
175 );
176 --
177 -- Cursor to check if the formula is referenced in pay_shadow_element_types table.
178 --
179
180 cursor csr_element_template is
181 select 1
182 from pay_shadow_element_types pset
183 where (pset.skip_formula = p_formula_details.formula_name or pset.iterative_formula_name=
184 p_formula_details.formula_name )
185 and exists(
186 select null
187 from pay_element_templates pet
188 where pet.template_id = pset.template_id
189 and (
190 ( pet.legislation_code is null and pet.business_group_id is null)
191
192 or (pet.legislation_code is null
193 and (
194 pet.business_group_id = p_formula_details.business_group_id
195 or p_formula_details.legislation_code =
196 (
197 select legislation_code
198 from per_business_groups
199 where business_group_id = pet.business_group_id
200 )
201 )
202 )
203 or (pet.business_group_id is null
204 and pet.legislation_code = p_formula_details.legislation_code)
205 )
206 );
207 --
208 -- Cursor to get the formula type name.
209 --
210 cursor csr_formula_type_name is
211 select upper(formula_type_name)
212 from ff_formula_types
213 where formula_type_id = p_formula_details.formula_type_id;
214 --
215
216
217
218 begin
219
220 --
221 -- Get formula type name.
222 --
223 open csr_formula_type_name;
224 fetch csr_formula_type_name into l_formula_type_name;
225 close csr_formula_type_name;
226
227 --
228 -- Validate Non-Dt Tables.
229 --
230
231
232 if p_dt_delete_mode = 'ZAP' then
233
234 open csr_element_template;
235 fetch csr_element_template into v_dummy;
236 if csr_element_template%found then
237 close csr_element_template;
238 hr_utility.set_message(801, 'PAY_34031_FORMULA_DEL_TMPL');
239 hr_utility.raise_error;
240 else
241 close csr_element_template;
242 end if;
243
244 --
245
246 open csr_au_modules;
247 fetch csr_au_modules into v_dummy;
248 if csr_au_modules%found then
249 close csr_au_modules;
250 hr_utility.set_message(801, 'PAY_34032_FORMULA_DEL_AU_MOD');
251 hr_utility.raise_error;
252 else
253 close csr_au_modules;
254 end if;
255
256 --
257
258 open csr_accrual_plans;
259 fetch csr_accrual_plans into v_dummy;
260 if csr_accrual_plans%found then
261 close csr_accrual_plans;
262 hr_utility.set_message(801, 'PAY_34033_FORMULA_DEL_ACCRUAL');
263 hr_utility.raise_error;
264 else
265 close csr_accrual_plans;
266 end if;
267
268 --
269 open csr_per_cagr_entitle_items;
270 fetch csr_per_cagr_entitle_items into v_dummy;
271 if csr_per_cagr_entitle_items%found then
272 close csr_per_cagr_entitle_items;
273 hr_utility.set_message(801, 'PAY_34034_FORMULA_DEL_CAGR');
274 hr_utility.raise_error;
275 else
276 close csr_per_cagr_entitle_items;
277 end if;
278
279 --
280 open csr_qp_report;
281 fetch csr_qp_report into v_dummy;
282 if csr_qp_report%found then
283 close csr_qp_report;
284 hr_utility.set_message(801, 'HR_6871_FORMULA_DEL_QP_I');
285 hr_utility.raise_error;
286 else
287 close csr_qp_report;
288 end if;
289 --
290 open csr_assignment_set;
291 fetch csr_assignment_set into v_dummy;
292 if csr_assignment_set%found then
293 close csr_assignment_set;
294 hr_utility.set_message(801, 'HR_6872_FORMULA_DEL_ASS_SET');
295 hr_utility.raise_error;
296 else
297 close csr_assignment_set;
298 end if;
299 --
300 open csr_user_column;
301 fetch csr_user_column into v_dummy;
302 if csr_user_column%found then
303 close csr_user_column;
304 hr_utility.set_message(801, 'HR_6879_FORMULA_DEL_USER_COL');
305 hr_utility.raise_error;
306 else
307 close csr_user_column;
308 end if;
309 --
310 open csr_magnetic_record;
311 fetch csr_magnetic_record into v_dummy;
312 if csr_magnetic_record%found then
313 close csr_magnetic_record;
314 hr_utility.set_message(801, 'HR_7341_FORMULA_DEL_MAG_REC');
315 hr_utility.raise_error;
316 else
317 close csr_magnetic_record;
318 end if;
319 --
320 end if;
321
322 --
323 -- Validate all DT tables that use formula NB.
324 -- Only need to check when shortening or completely removing the formula.
325 --
326
327 if p_dt_delete_mode in ('ZAP','DELETE') then
328 --
329 open csr_cagr_retained_rights;
330 fetch csr_cagr_retained_rights into v_dummy;
331 if csr_cagr_retained_rights%found then
332 close csr_cagr_retained_rights;
333 hr_utility.set_message(801, 'PAY_34034_FORMULA_DEL_CAGR');
334 hr_utility.raise_error;
335 else
336 close csr_cagr_retained_rights;
337 end if;
338
339 --
340 open csr_per_cagr_entitle_results;
341 fetch csr_per_cagr_entitle_results into v_dummy;
342 if csr_per_cagr_entitle_results%found then
343 close csr_per_cagr_entitle_results;
344 hr_utility.set_message(801, 'PAY_34034_FORMULA_DEL_CAGR');
345 hr_utility.raise_error;
346 else
347 close csr_per_cagr_entitle_results;
348 end if;
349
350 --
351 open csr_per_cagr_entitlements;
352 fetch csr_per_cagr_entitlements into v_dummy;
353 if csr_per_cagr_entitlements%found then
354 close csr_per_cagr_entitlements;
355 hr_utility.set_message(801, 'PAY_34034_FORMULA_DEL_CAGR');
356 hr_utility.raise_error;
357 else
358 close csr_per_cagr_entitlements;
359 end if;
360 --
361 open csr_input_value;
362 fetch csr_input_value into v_dummy;
363 if csr_input_value%found then
364 close csr_input_value;
365 hr_utility.set_message(801, 'HR_6873_FORMULA_DEL_INP_VAL');
366 hr_utility.raise_error;
367 else
368 close csr_input_value;
369 end if;
370 --
371 open csr_stat_proc_rule;
372 fetch csr_stat_proc_rule into v_dummy;
373 if csr_stat_proc_rule%found then
374 close csr_stat_proc_rule;
375 hr_utility.set_message(801, 'HR_6878_FORMULA_DEL_PRO_RULE');
376 hr_utility.raise_error;
377 else
378 close csr_stat_proc_rule;
379 end if;
380 --
381 open csr_element_type;
382 fetch csr_element_type into v_dummy;
383 if csr_element_type%found then
384 close csr_element_type;
385 --
386 -- Raise appropirate error message depending on the type of formula.
387 --
388 if l_formula_type_name = 'PAYROLL RUN PRORATION' then
389
390 hr_utility.set_message(801, 'PAY_33160_FORMULA_DEL_ELE_PRO');
391 hr_utility.raise_error;
392
393 elsif l_formula_type_name = 'NET TO GROSS' then
394
395 hr_utility.set_message(801, 'PAY_34035_FORMULA_DEL_ELE_NTG');
396 hr_utility.raise_error;
397
398 else
399 hr_utility.set_message(801, 'HR_6955_PAY_FORMULA_DEL_ELE');
400 hr_utility.raise_error;
401
402 end if;
403 else
404 close csr_element_type;
405 end if;
406 --
407
408 end if;
409 --
410 end payroll_del_validation;
411 --
412 -----------------------------------------------------------------------------
413 -- Name --
414 -- payroll_dnc_validation --
415 -- Purpose --
416 -- Provides check for conflicting records when selecting delete next --
417 -- change of future change operations. --
421 -- None. --
418 -- Arguments --
419 -- See below. --
420 -- Notes --
422 -- History --
423 -- 13-Sep-04. --
424 -- Added input parameter p_formula_details. --
425 -----------------------------------------------------------------------------
426 --
427 procedure payroll_dnc_validation
428 (p_formula_id number
429 ,p_dt_delete_mode varchar2
430 ,p_validation_start_date date
431 ,p_validation_end_date date
432 ,p_formula_details FormulaRec
433 ) is
434 --
435
436 --
437 cursor csr_future_clash(p_formula_name varchar2
438 ,p_formula_type_id number
439 ,p_business_group_id number
440 ,p_legislation_code varchar2
441 ,p_val_start_date date
442 ,p_startup_mode varchar2) is
443 select 'X'
444 from ff_formulas_f ff
445 where ff.formula_name = p_formula_name
446 and ff.formula_type_id = p_formula_type_id
447 and ff.effective_start_date > p_val_start_date
448 and ((p_startup_mode = 'MASTER')
449 or (p_startup_mode = 'SEED'
450 and ((ff.legislation_code = p_legislation_code)
451 or (ff.legislation_code is null
452 and ff.business_group_id is null)))
453 or (p_startup_mode = 'NON-SEED'
454 and ((ff.business_group_id +0 = p_business_group_id)
455 or (ff.legislation_code is null
456 and ff.business_group_id is null)
457 or (ff.business_group_Id is null
458 and ff.legislation_code = p_legislation_code))));
459 --
460 l_startup_mode varchar2(10);
461 l_dummy varchar2(1);
462 --
463 begin
464
465 --
466 l_startup_mode := ffstup.get_mode(p_formula_details.business_group_id, p_formula_details.legislation_code);
467 --
468 open csr_future_clash(p_formula_details.formula_name,
469 p_formula_details.formula_type_id,
470 p_formula_details.business_group_id,
471 p_formula_details.legislation_code,
472 p_formula_details.effective_start_date,
473 l_startup_mode);
474 fetch csr_future_clash into l_dummy;
475 if csr_future_clash%found then
476 --
477 close csr_future_clash;
478 fnd_message.set_name('PAY','HR_72033_CANNOT_DNC_RECORD');
479 fnd_message.raise_error;
480 --
481 else
482 --
483 close csr_future_clash;
484 --
485 end if;
486 --
487 end payroll_dnc_validation;
488 --
489 -----------------------------------------------------------------------------
490 -- Name --
491 -- Insert_Row --
492 -- Purpose --
493 -- Table handler procedure that supports the insert of a formula via the --
494 -- Write Formula form. --
495 -- Arguments --
496 -- See below. --
497 -- Notes --
498 -- A check is made to ensure the formula name is unique. --
499 -----------------------------------------------------------------------------
500 --
501 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
502 X_Formula_Id IN OUT NOCOPY NUMBER,
503 X_Effective_Start_Date DATE,
504 X_Effective_End_Date DATE,
505 X_Business_Group_Id NUMBER,
506 X_Legislation_Code VARCHAR2,
507 X_Formula_Type_Id NUMBER,
508 X_Formula_Name IN OUT NOCOPY VARCHAR2,
509 X_Description VARCHAR2,
510 X_Formula_Text VARCHAR2,
511 X_Sticky_Flag VARCHAR2,
512 X_Last_Update_Date IN OUT NOCOPY DATE) IS
513 --
514 CURSOR C IS SELECT rowid row_id, last_update_date FROM ff_formulas_f
515 WHERE formula_id = X_Formula_Id;
516 --
517 CURSOR C2 IS SELECT ff_formulas_s.nextval FROM sys.dual;
518 --
519 ReturnInfo C%RowType;
520 L_Effective_End_Date Date;
521 --
522 BEGIN
523 --
524 L_Effective_End_Date := X_Effective_End_Date;
525 -- Make sure formula name is unique and valid ie. no spaces etc ....
526 ffdict.validate_formula
527 (X_Formula_Name,
528 X_Formula_Type_Id,
529 X_Business_Group_Id,
530 X_Legislation_Code,
531 X_Effective_Start_Date,
532 L_Effective_End_Date);
533 --
534 if (X_Formula_Id is NULL) then
535 OPEN C2;
536 FETCH C2 INTO X_Formula_Id;
537 CLOSE C2;
541 (formula_id,
538 end if;
539 --
540 INSERT INTO ff_formulas_f
542 effective_start_date,
543 effective_end_date,
544 business_group_id,
545 legislation_code,
546 formula_type_id,
547 formula_name,
548 description,
549 formula_text,
550 sticky_flag)
551 VALUES
552 (X_Formula_Id,
553 X_Effective_Start_Date,
554 L_Effective_End_Date,
555 X_Business_Group_Id,
556 X_Legislation_Code,
557 X_Formula_Type_Id,
558 X_Formula_Name,
559 X_Description,
560 X_Formula_Text,
561 X_Sticky_Flag);
562 --
563 -- insert into MLS table (TL)
564 --
565 --For MLS-----------------------------------------------------------------------
566 g_dml_status := TRUE;
567 ff_fft_ins.ins_tl(userenv('LANG'),X_FORMULA_ID,
568 X_FORMULA_NAME,X_DESCRIPTION);
569 g_dml_status := FALSE;
570 --------------------------------------------------------------------------------
571
572 --
573 OPEN C;
574 FETCH C INTO ReturnInfo;
575 if (C%NOTFOUND) then
576 CLOSE C;
577 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
578 hr_utility.set_message_token('PROCEDURE',
579 'ff_formulas_f_pkg.insert_row');
580 hr_utility.set_message_token('STEP','1');
581 hr_utility.raise_error;
582 end if;
583 CLOSE C;
584 --
585 X_Rowid := ReturnInfo.row_id;
586 X_Last_Update_Date := ReturnInfo.last_update_date;
587 --
588 Exception
589 When Others then
590 g_dml_status := FALSE;
591 raise;
592 END Insert_Row;
593 --
594 -----------------------------------------------------------------------------
595 -- Name --
596 -- Lock_Row (OVERLOADED) --
597 -- Purpose --
598 -- Table handler procedure that supports the insert , update and delete --
599 -- of a formula by applying a lock on a formula in the Write Formula --
600 -- form. --
601 -- Arguments --
602 -- See below. --
603 -- Notes --
604 -- This version checks each column to see if the formula has changed. --
605 -----------------------------------------------------------------------------
606 --
607 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
608 X_Formula_Id NUMBER,
609 X_Effective_Start_Date DATE,
610 X_Effective_End_Date DATE,
611 X_Business_Group_Id NUMBER,
612 X_Legislation_Code VARCHAR2,
613 X_Formula_Type_Id NUMBER,
614 X_Formula_Name VARCHAR2,
615 X_Description VARCHAR2,
616 X_Formula_Text VARCHAR2,
617 X_Sticky_Flag VARCHAR2,
618 X_Base_Formula_Name VARCHAR2 default NULL,
619 X_Base_Description VARCHAR2 default NULL) IS
620 --
621 CURSOR C IS SELECT * FROM ff_formulas_f
622 WHERE rowid = X_Rowid FOR UPDATE NOWAIT;
623 --
624 Recinfo C%ROWTYPE;
625 --
626 BEGIN
627 --
628 OPEN C;
629 FETCH C INTO Recinfo;
630 if (C%NOTFOUND) then
631 CLOSE C;
632 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
633 hr_utility.set_message_token('PROCEDURE',
634 'ff_formulas_f_pkg.lock_row');
635 hr_utility.set_message_token('STEP','1');
636 hr_utility.raise_error;
637 end if;
638 CLOSE C;
639 --
640 -- Remove trailing spaces.
641 Recinfo.legislation_code := rtrim(Recinfo.legislation_code);
642 Recinfo.formula_name := rtrim(Recinfo.formula_name);
643 Recinfo.description := rtrim(Recinfo.description);
644 Recinfo.formula_text := rtrim(Recinfo.formula_text);
645 Recinfo.sticky_flag := rtrim(Recinfo.sticky_flag);
646 --
647 if ( ( (Recinfo.formula_id = X_Formula_Id)
648 OR ( (Recinfo.formula_id IS NULL)
649 AND (X_Formula_Id IS NULL)))
650 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
651 OR ( (Recinfo.effective_start_date IS NULL)
652 AND (X_Effective_Start_Date IS NULL)))
653 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
654 OR ( (Recinfo.effective_end_date IS NULL)
655 AND (X_Effective_End_Date IS NULL)))
656 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
660 OR ( (Recinfo.legislation_code IS NULL)
657 OR ( (Recinfo.business_group_id IS NULL)
658 AND (X_Business_Group_Id IS NULL)))
659 AND ( (Recinfo.legislation_code = X_Legislation_Code)
661 AND (X_Legislation_Code IS NULL)))
662 AND ( (Recinfo.formula_type_id = X_Formula_Type_Id)
663 OR ( (Recinfo.formula_type_id IS NULL)
664 AND (X_Formula_Type_Id IS NULL)))
665 AND ( (Recinfo.formula_name = X_Base_Formula_Name)
666 OR ( (Recinfo.formula_name IS NULL)
667 AND (X_Base_Formula_Name IS NULL)))
668 AND ( (Recinfo.description = X_Base_Description)
669 OR ( (Recinfo.description IS NULL)
670 AND (X_Base_Description IS NULL)))
671 AND ( (Recinfo.formula_text = X_Formula_Text)
672 OR ( (Recinfo.formula_text IS NULL)
673 AND (X_Formula_Text IS NULL)))
674 AND ( (Recinfo.sticky_flag = X_Sticky_Flag)
675 OR ( (Recinfo.sticky_flag IS NULL)
676 AND (X_Sticky_Flag IS NULL)))
677 ) then
678 return;
679 else
680 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
681 APP_EXCEPTION.RAISE_EXCEPTION;
682 end if;
683 --
684 END Lock_Row;
685 --
686 -----------------------------------------------------------------------------
687 -- Name --
688 -- Lock_Row (OVERLOADED) --
689 -- Purpose --
690 -- Table handler procedure that supports the insert , update and delete --
691 -- of a formula by applying a lock on a formula in the Write Formula --
692 -- form. --
693 -- Arguments --
694 -- See below. --
695 -- Notes --
696 -- This version tests the last_update_date to see if the formula has --
697 -- changed. --
698 -----------------------------------------------------------------------------
699 --
700 PROCEDURE Lock_Row(x_rowid VARCHAR2,
701 x_last_update_date DATE) IS
702 --
703 CURSOR C IS SELECT last_update_date FROM ff_formulas_f
704 WHERE rowid = x_rowid FOR UPDATE NOWAIT;
705 --
706 v_current_update_date date;
707 --
708 BEGIN
709 --
710 OPEN C;
711 FETCH C INTO v_current_update_date;
712 if (C%NOTFOUND) then
713 CLOSE C;
714 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
715 hr_utility.set_message_token('PROCEDURE',
716 'ff_formulas_f_pkg.lock_row');
717 hr_utility.set_message_token('STEP','1');
718 hr_utility.raise_error;
719 end if;
720 CLOSE C;
721 --
722 -- Compare the last_update_date from the client against that held on the
723 -- DB. If they are different then the row has been chnaged since it was
724 -- queried. The use of nvl is to cope when either of the dates is null which
725 -- would immediately fail the comparison.
726 if nvl(x_last_update_date,to_date('01/01/0001','DD/MM/YYYY')) <>
727 nvl(v_current_update_date,to_date('01/01/0001','DD/MM/YYYY')) then
728 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
729 APP_EXCEPTION.RAISE_EXCEPTION;
730 end if;
731 --
732 END Lock_Row;
733 --
734 -----------------------------------------------------------------------------
735 -- Name --
736 -- Update_Row --
737 -- Purpose --
738 -- Table handler procedure that supports the update of a formula via the --
739 -- Write Formula form. --
740 -- Arguments --
741 -- See below. --
742 -- Notes --
743 -- None. --
744 -----------------------------------------------------------------------------
745 --
746 PROCEDURE Update_Row(X_Rowid VARCHAR2,
747 X_Formula_Id NUMBER,
748 X_Effective_Start_Date DATE,
749 X_Effective_End_Date DATE,
750 X_Business_Group_Id NUMBER,
751 X_Legislation_Code VARCHAR2,
755 X_Formula_Text VARCHAR2,
752 X_Formula_Type_Id NUMBER,
753 X_Formula_Name VARCHAR2,
754 X_Description VARCHAR2,
756 X_Sticky_Flag VARCHAR2,
757 X_Last_Update_Date IN OUT NOCOPY DATE,
758 X_Base_Formula_Name VARCHAR2 default hr_api.g_varchar2,
759 X_Base_Description VARCHAR2 default hr_api.g_varchar2) IS
760 --
761 CURSOR C IS SELECT last_update_date FROM ff_formulas_f
762 WHERE rowid = x_rowid;
763 --
764 v_current_update_date date;
765 --
766 l_formula_name varchar2(80);
767 l_description varchar2(240);
768 --
769 BEGIN
770 --
771 --
772 --Fixed for bug 4348013--
773 /* Checking if the Base values of formula_name and description are of type hr_api.g_varchar2 i.e.
774 the procedure is not being called from the form but from outside then copy the translated
775 values into them.*/
776
777 l_formula_name := X_Base_Formula_Name;
778 l_description := X_Base_Description;
779
780 if(l_formula_name = hr_api.g_varchar2) then
781 l_formula_name := X_Formula_Name;
782 end if;
783 if( l_description = hr_api.g_varchar2 ) then
784 l_description := X_Description;
785 end if;
786 ----
787 UPDATE ff_formulas_f
788 SET formula_id = X_Formula_Id,
789 effective_start_date = X_Effective_Start_Date,
790 effective_end_date = X_Effective_End_Date,
791 business_group_id = X_Business_Group_Id,
792 legislation_code = X_Legislation_Code,
793 formula_type_id = X_Formula_Type_Id,
794 formula_name = l_formula_name,
795 description = l_description,
796 formula_text = X_Formula_Text,
797 sticky_flag = X_Sticky_Flag
798 WHERE rowid = X_rowid;
799 --
800 --For MLS-----------------------------------------------------------------------
801 g_dml_status := TRUE;
802 ff_fft_upd.upd_tl(userenv('LANG'),X_FORMULA_ID,
803 X_FORMULA_NAME,X_DESCRIPTION);
804 g_dml_status := FALSE;
805 --------------------------------------------------------------------------------
806
807 ---
808 if (SQL%NOTFOUND) then
809 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
810 hr_utility.set_message_token('PROCEDURE',
811 'ff_formulas_f_pkg.update_row');
812 hr_utility.set_message_token('STEP','1');
813 hr_utility.raise_error;
814 end if;
815 --
816 OPEN C;
817 FETCH C INTO v_current_update_date;
818 if (C%NOTFOUND) then
819 CLOSE C;
820 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
821 hr_utility.set_message_token('PROCEDURE',
822 'ff_formulas_f_pkg.update_row');
823 hr_utility.set_message_token('STEP','2');
824 hr_utility.raise_error;
825 end if;
826 CLOSE C;
827 --
828 X_Last_Update_Date := v_current_update_date;
829 --
830 Exception
831 When Others then
832 g_dml_status := FALSE;
833 raise;
834 END Update_Row;
835 --
836 -----------------------------------------------------------------------------
837 -- Name --
838 -- Delete_Row --
839 -- Purpose --
840 -- Table handler procedure that supports the delete of a formula via the --
841 -- Write Formula form. --
842 -- Arguments --
843 -- See below. --
844 -- Notes --
845 -- Referential integrity checks are done against any payroll tables that --
846 -- make use of formula. --
847 -- History --
848 -- 13-Sep-04.
849 -- Added X_Effective_Date input parameter to check for delete integrity --
850 -- violation against Oracle Advanced Benefits tables by calling --
851 -- chk_formula_exists_in_ben function.
852 -- Derived formula details here rather than in payroll_dnc_validation --
853 -- and passed the details to payroll_del_validation, --
854 -- payroll_dnc_validation and chk_formula_exists_in_ben.Bug 3703492 --
855 -- 16-Sep-04 --
856 -- Changed the order of arguments. --
857 -----------------------------------------------------------------------------
858 --
859 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
860 X_Formula_Id NUMBER,
861 X_Dt_Delete_Mode VARCHAR2,
862 X_Validation_Start_Date DATE,
863 X_Validation_End_Date DATE,
864 X_Effective_Date DATE) IS
865
866 l_oab_installed boolean;
867 l_prod_status varchar2(1);
868 l_industry varchar2(1);
869 l_oracle_schema varchar2(30);
870 l_formula_in_oab boolean;
871 formula_details FormulaRec;
872 l_start_of_time date;
873 --
874 -- Cursor to get details of the formula to be deleted.
875 --
876 cursor csr_current_record is
877 select ff.formula_name
878 , ff.formula_type_id
879 , ff.business_group_id
880 , nvl(bg.legislation_code,ff.legislation_code)
881 , ff.effective_start_date
885 where ff.formula_id = X_formula_id
882 , ff.effective_end_date
883 from ff_formulas_f ff
884 , per_business_groups bg
886 and ff.business_group_id = bg.business_group_id (+)
887 and (
888 (X_Dt_Delete_Mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE')
889 and ff.effective_end_date+1 = X_validation_start_date)
890
891 or ( X_Dt_Delete_Mode = 'DELETE' and X_Effective_Date+1 = X_validation_start_date)
892
893 or ( X_Dt_Delete_Mode = 'ZAP' and l_start_of_time = X_validation_start_date)
894 );
895
896 --
897 BEGIN
898
899 l_start_of_time := to_date('01/01/0001','dd/mm/yyyy');
900 --
901 -- Fetch the details of the formula to be deleted.
902 --
903 open csr_current_record;
904 fetch csr_current_record into formula_details;
905
906 if csr_current_record%notfound then
907 --
908 close csr_current_record;
909 fnd_message.set_name('PER','HR_51022_HR_INV_PRIMARY_KEY');
910 fnd_message.raise_error;
911 --
912 else
913 --
914 close csr_current_record;
915 --
916 end if;
917
918 --
919 -- Make sure that no payroll or per tables using the formula are affected by a
920 -- delete of formula.
921 --
922 payroll_del_validation
923 (X_Formula_Id
924 ,X_Dt_Delete_Mode
925 ,X_Validation_Start_Date
926 ,X_Validation_End_Date
927 ,Formula_Details);
928
929 --
930 --
931 -- Make sure no clashes exist if end date of formula is being extended.
932 --
933 if X_Dt_Delete_Mode NOT IN ('ZAP','DELETE') then
934 --
935 payroll_dnc_validation
936 (X_Formula_Id
937 ,X_Dt_Delete_Mode
938 ,X_Validation_Start_Date
939 ,X_Validation_End_Date
940 ,Formula_Details);
941 --
942 end if;
943
944 --
945 --
946 -- Check if Oracle Advanced Benefits is installed.
947 --
948 l_oab_installed := fnd_installation.get_app_info ( 'BEN',
949 l_prod_status,
950 l_industry,
951 l_oracle_schema );
952 --
953 -- If OAB is installed, check if delete integrity is being violated.
954 --
955
956 if ( l_prod_status = 'I' ) then
957
958 l_formula_in_oab:=ben_fastformula_check.chk_formula_exists_in_ben
959 (
960 p_formula_id => X_formula_id,
961 p_formula_type_id => Formula_Details.formula_type_id,
962 p_effective_date => X_Effective_Date,
963 p_business_group_id => Formula_Details.business_group_id,
964 p_legislation_cd => Formula_Details.legislation_code
965 );
966
967 if l_formula_in_oab = true then
968 hr_utility.set_message(801, 'PAY_34036_FORMULA_DEL_OAB');
969 hr_utility.raise_error;
970 end if;
971
972
973 end if;
974 --
975 --For MLS-----------------------------------------------------------------------
976 if X_Dt_Delete_Mode IN ('ZAP') then
977 begin
978 g_dml_status := TRUE;
979 ff_fft_del.del_tl(X_FORMULA_ID);
980 g_dml_status := FALSE;
981 Exception
982 When Others then
983 g_dml_status := FALSE;
984 raise;
985 end;
986 end if;
987 --------------------------------------------------------------------------------
988
989 DELETE FROM ff_formulas_f
990 WHERE rowid = X_Rowid;
991 --
992 if (SQL%NOTFOUND) then
993 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
994 hr_utility.set_message_token('PROCEDURE',
995 'ff_formulas_f_pkg.delete_row');
996 hr_utility.set_message_token('STEP','1');
997 hr_utility.raise_error;
998 end if;
999 --
1000 END Delete_Row;
1001 --
1002 ---For MLS----------------------------------------------------------------------
1003 procedure ADD_LANGUAGE
1004 is
1005 begin
1006 delete from FF_FORMULAS_F_TL T
1007 where not exists
1008 (select NULL
1009 from FF_FORMULAS_F B
1010 where B.FORMULA_ID = T.FORMULA_ID
1011 );
1012 update FF_FORMULAS_F_TL T set (
1013 FORMULA_NAME,
1014 DESCRIPTION
1015 ) = (select
1016 B.FORMULA_NAME,
1017 B.DESCRIPTION
1018 from FF_FORMULAS_F_TL B
1019 where B.FORMULA_ID = T.FORMULA_ID
1020 and B.LANGUAGE = T.SOURCE_LANG)
1021 where (
1022 T.FORMULA_ID,
1023 T.LANGUAGE
1024 ) in (select
1025 SUBT.FORMULA_ID,
1026 SUBT.LANGUAGE
1027 from FF_FORMULAS_F_TL SUBB, FF_FORMULAS_F_TL SUBT
1028 where SUBB.FORMULA_ID = SUBT.FORMULA_ID
1029 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1030 and (SUBB.FORMULA_NAME <> SUBT.FORMULA_NAME
1031 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1032 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
1033 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
1034 ));
1035
1036 insert into FF_FORMULAS_F_TL (
1037 FORMULA_ID,
1038 FORMULA_NAME,
1039 DESCRIPTION,
1040 LAST_UPDATE_DATE,
1041 LAST_UPDATED_BY,
1042 LAST_UPDATE_LOGIN,
1043 CREATED_BY,
1044 CREATION_DATE,
1045 LANGUAGE,
1046 SOURCE_LANG
1047 ) select
1048 B.FORMULA_ID,
1049 B.FORMULA_NAME,
1050 B.DESCRIPTION,
1051 B.LAST_UPDATE_DATE,
1052 B.LAST_UPDATED_BY,
1053 B.LAST_UPDATE_LOGIN,
1054 B.CREATED_BY,
1055 B.CREATION_DATE,
1056 L.LANGUAGE_CODE,
1057 B.SOURCE_LANG
1058 from FF_FORMULAS_F_TL B, FND_LANGUAGES L
1062 (select NULL
1059 where L.INSTALLED_FLAG in ('I', 'B')
1060 and B.LANGUAGE = userenv('LANG')
1061 and not exists
1063 from FF_FORMULAS_F_TL T
1064 where T.FORMULA_ID = B.FORMULA_ID
1065 and T.LANGUAGE = L.LANGUAGE_CODE);
1066 end ADD_LANGUAGE;
1067 --
1068 -----
1069 procedure TRANSLATE_ROW (
1070 X_B_FORMULA_NAME in VARCHAR2,
1071 X_B_LEGISLATION_CODE in VARCHAR2,
1072 X_FORMULA_NAME in VARCHAR2,
1073 X_DESCRIPTION in VARCHAR2,
1074 X_OWNER in VARCHAR2
1075 ) is
1076 begin
1077 UPDATE ff_formulas_f_tl
1078 SET FORMULA_NAME = nvl(X_FORMULA_NAME,FORMULA_NAME),
1079 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
1080 last_update_date = SYSDATE,
1081 last_updated_by = decode(x_owner,'SEED',1,0),
1082 last_update_login = 0,
1083 source_lang = userenv('LANG')
1084 WHERE userenv('LANG') IN (language,source_lang)
1085 AND FORMULA_ID in
1086 (select FORMULA_ID
1087 from FF_FORMULAS_F
1088 where nvl(FORMULA_NAME,'~null~')=nvl(X_B_FORMULA_NAME,'~null~')
1089 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
1090 and BUSINESS_GROUP_ID is NULL);
1091 if (sql%notfound) then
1092 null;
1093 end if;
1094 end TRANSLATE_ROW;
1095 --
1096 ---
1097 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
1098 p_legislation_code IN VARCHAR2) IS
1099 BEGIN
1100 g_business_group_id := p_business_group_id;
1101 g_legislation_code := p_legislation_code;
1102 END set_translation_globals;
1103 --
1104 ---
1105 procedure validate_translation(formula_id NUMBER,
1106 language VARCHAR2,
1107 formula_name VARCHAR2,
1108 description VARCHAR2,
1109 p_business_group_id IN NUMBER DEFAULT NULL,
1110 p_legislation_code IN VARCHAR2 DEFAULT NULL) IS
1111 /*
1112
1113 This procedure fails if a formula translation is already present in
1114 the table for a given language. Otherwise, no action is performed. It is
1115 used to ensure uniqueness of translated formula names.
1116
1117 */
1118
1119 --
1120 -- This cursor implements the validation we require,
1121 -- and expects that the various package globals are set before
1122 -- the call to this procedure is made. This is done from the
1123 -- user-named trigger 'TRANSLATIONS' in the form
1124 --
1125 cursor c_translation(p_language IN VARCHAR2,
1126 p_formula_name IN VARCHAR2,
1127 p_formula_id IN NUMBER,
1128 p_bus_grp_id IN NUMBER,
1129 p_leg_code IN varchar2) IS
1130 SELECT 1
1131 FROM ff_formulas_f_tl fft,
1132 ff_formulas_f fff
1133 WHERE upper(fft.formula_name)=upper(p_formula_name)
1134 AND fft.formula_id = fff.formula_id
1135 AND fft.language = p_language
1136 AND (fff.formula_id <> p_formula_id OR p_formula_id IS NULL)
1137 AND (nvl(fff.business_group_id,-1) = nvl(p_bus_grp_id,-1) OR p_bus_grp_id IS NULL)
1138 AND (nvl(fff.LEGISLATION_CODE,'~null~') = nvl(p_leg_code,'~null~') OR p_leg_code IS NULL);
1139
1140 l_package_name VARCHAR2(80);
1141 l_business_group_id NUMBER;
1142 l_legislation_code VARCHAR2(150);
1143
1144 BEGIN
1145 l_package_name := 'FF_FORMULAS_F_PKG.VALIDATE_TRANSLATION';
1146 l_business_group_id := p_business_group_id;
1147 l_legislation_code := p_legislation_code;
1148 hr_utility.set_location (l_package_name,10);
1149 OPEN c_translation(language, formula_name,formula_id,
1150 l_business_group_id,l_legislation_code);
1151 hr_utility.set_location (l_package_name,50);
1152 FETCH c_translation INTO g_dummy;
1153
1154 IF c_translation%NOTFOUND THEN
1155 hr_utility.set_location (l_package_name,60);
1156 CLOSE c_translation;
1157 ELSE
1158 hr_utility.set_location (l_package_name,70);
1159 CLOSE c_translation;
1160 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
1161 fnd_message.raise_error;
1162 END IF;
1163 hr_utility.set_location ('Leaving:'||l_package_name,80);
1164 END validate_translation;
1165 --
1166 function return_dml_status
1167 return boolean
1168 IS
1169 begin
1170 return g_dml_status;
1171 end return_dml_status;
1172 ---
1173 END FF_FORMULAS_F_PKG;
[Home] [Help]