1 PACKAGE BODY PAY_DEFINED_BALANCES_PKG as
2 /* $Header: pydfb01t.pkb 120.1 2006/03/10 02:30:52 alogue noship $ */
3 --
4 -----------------------------------------------------------------------------
5 -- Name --
6 -- chk_defined_balance --
7 -- Purpose --
8 -- Make sure that the defined balance is unique. --
9 -- Arguments --
10 -- See below. --
11 -- Notes --
12 -- --
13 -----------------------------------------------------------------------------
14 --
15 procedure chk_defined_balance
16 (
17 p_row_id varchar2,
18 p_business_group_id number,
19 p_legislation_code varchar2,
20 p_balance_type_id number,
21 p_balance_dimension_id number
22 ) is
23 --
24 cursor csr_unique_defined_balance is
25 select dfb.defined_balance_id
26 from pay_defined_balances dfb
27 where dfb.balance_type_id = p_balance_type_id
28 and dfb.balance_dimension_id = p_balance_dimension_id
29 and nvl(dfb.business_group_id,nvl(p_business_group_id,0)) =
30 nvl(p_business_group_id,0)
31 and nvl(dfb.legislation_code,nvl(p_legislation_code,' ')) =
32 nvl(p_legislation_code,' ')
33 and (p_row_id is null or
34 (p_row_id is not null and chartorowid(p_row_id) <> dfb.rowid));
35 --
36 v_defined_balance_id number;
37 --
38 begin
39 --
40 open csr_unique_defined_balance;
41 fetch csr_unique_defined_balance into v_defined_balance_id;
42 if csr_unique_defined_balance%found then
43 close csr_unique_defined_balance;
44 hr_utility.set_message(801, 'HR_6117_BAL_UNI_DIMENSION');
45 hr_utility.raise_error;
46 else
47 close csr_unique_defined_balance;
48 end if;
49 --
50 end chk_defined_balance;
51 --
52 -----------------------------------------------------------------------------
53 -- Name --
54 -- chk_delete_defined_balance --
55 -- Purpose --
56 -- Check to see if it valid to remove a defined balance. --
57 -- Arguments --
58 -- See below. --
59 -- Notes --
60 -- --
61 -----------------------------------------------------------------------------
62 --
63 procedure chk_delete_defined_balance
64 (
65 p_defined_balance_id number
66 ) is
67 --
68 cursor csr_org_pay_meth is
69 select opm.org_payment_method_id
70 from pay_org_payment_methods_f opm
71 where opm.defined_balance_id = p_defined_balance_id;
72 --
73 cursor csr_backpay_set is
74 select br.backpay_set_id
75 from pay_backpay_rules br
76 where br.defined_balance_id = p_defined_balance_id;
77 --
78 cursor get_pbas(p_def_bal number) is
79 select balance_attribute_id
80 from pay_balance_attributes
81 where defined_balance_id = p_def_bal;
82 --
83 v_org_pay_meth_id number;
84 v_backpay_set_id number;
85 --
86 begin
87 --
88 -- See if defined balance is being used by an organization payment method.
89 open csr_org_pay_meth;
90 fetch csr_org_pay_meth into v_org_pay_meth_id;
91 if csr_org_pay_meth%found then
92 close csr_org_pay_meth;
93 hr_utility.set_message(801, 'HR_6958_PAY_ORG_PAY_MTHD_EXIST');
94 hr_utility.raise_error;
95 else
96 close csr_org_pay_meth;
97 end if;
98 --
99 -- See if defined balance is being used by a backpay set.
100 open csr_backpay_set;
101 fetch csr_backpay_set into v_backpay_set_id;
102 if csr_backpay_set%found then
103 close csr_backpay_set;
104 hr_utility.set_message(801, 'HR_7046_BACK_PAY_EXIST');
105 hr_utility.raise_error;
106 else
107 close csr_backpay_set;
108 end if;
109 --
110 for each_pba in get_pbas(p_defined_balance_id) loop
111 pay_balance_attribute_api.delete_balance_attribute
112 (p_balance_attribute_id => each_pba.balance_attribute_id);
113 end loop;
114 end chk_delete_defined_balance;
115 --
116 -----------------------------------------------------------------------------
117 -- Name --
118 -- delete_defined_balance --
119 -- Purpose --
120 -- Remove children of defined balance NB. a trigger on defined balance --
121 -- will remove some children ie. DB Item. --
122 -- Arguments --
123 -- See below. --
124 -- Notes --
125 -- --
126 -----------------------------------------------------------------------------
127 --
128 procedure delete_defined_balance
129 (
130 p_defined_balance_id number
131 ) is
132 --
133 begin
134 --
135 delete /*+ INDEX(alb PAY_ASSIGNMENT_LATEST_BALA_FK2)*/
136 from pay_assignment_latest_balances alb
137 where alb.defined_balance_id = p_defined_balance_id;
138 --
139 delete /*+ INDEX(plb PAY_PERSON_LATEST_BALANCES_FK1)*/
140 from pay_person_latest_balances plb
141 where plb.defined_balance_id = p_defined_balance_id;
142 --
143 end delete_defined_balance;
144 --
145 -----------------------------------------------------------------------------
146 -- function set_save_run_bals_flag
147 -- Description - sets the value of save_run_balance on pay_defined_balances,
148 -- the value is determined by the values of save_run_balance_enabled on
149 -- pay_balance_categories_f and pay_balance_dimensions.
150 -----------------------------------------------------------------------------
151 function set_save_run_bals_flag(p_balance_category_id number
152 ,p_effective_date date
153 ,p_balance_dimension_id number)
154 return varchar2
155 is
156 --
157 cursor get_cat_flag(p_cat_id number
158 ,p_eff_date date)
159 is
160 select pbc.save_run_balance_enabled
161 from pay_balance_categories_f pbc
162 where pbc.balance_category_id = (p_cat_id)
163 and p_eff_date between pbc.effective_start_date
164 and pbc.effective_end_date;
165 --
166 cursor get_dim_flag(p_dim_id number)
167 is
168 select dim.save_run_balance_enabled
169 from pay_balance_dimensions dim
170 where dim.balance_dimension_id = p_dim_id
171 and dim.dimension_type = 'R';
172 --
173 l_cat_flag pay_balance_categories_f.save_run_balance_enabled%type;
174 l_dim_flag pay_balance_dimensions.save_run_balance_enabled%type;
175 l_save_run_bal_flag pay_defined_balances.save_run_balance%type;
176 --
177 BEGIN
178 open get_cat_flag(p_balance_category_id, p_effective_date);
179 fetch get_cat_flag into l_cat_flag;
180 if get_cat_flag%notfound then
181 close get_cat_flag;
182 else
183 close get_cat_flag;
184 end if;
185 --
186 open get_dim_flag(p_balance_dimension_id);
187 fetch get_dim_flag into l_dim_flag;
188 if get_dim_flag%notfound then
189 close get_dim_flag;
190 else
191 close get_dim_flag;
192 end if;
193 --
194 if l_cat_flag = 'Y' and l_dim_flag = 'Y' then
195 --
196 l_save_run_bal_flag := 'Y';
197 elsif
198 l_cat_flag = 'N' and l_dim_flag = 'N' then
199 --
200 l_save_run_bal_flag := 'N';
201 else
202 l_save_run_bal_flag := '';
203 end if;
204 return l_save_run_bal_flag;
205 END set_save_run_bals_flag;
206 -----------------------------------------------------------------------------
207 -- insert_default_attrib_wrapper
208 -- wrapper procedure insert_default_attributes used when not called directly
209 -- from forms.
210 -----------------------------------------------------------------------------
211 procedure insert_default_attrib_wrapper(p_balance_dimension_id number
212 ,p_balance_category_id number
213 ,p_def_bal_bg_id number
214 ,p_def_bal_leg_code varchar2
215 ,p_defined_balance_id number
216 ,p_effective_date date)
217 is
218 cursor get_bg_leg(p_bg number)
219 is
220 select legislation_code
221 from per_business_groups
222 where business_group_id = p_bg;
223 --
224 cursor get_sess_date
225 is
226 select effective_date
227 from fnd_sessions
228 where session_id = userenv('sessionid');
229 --
230 l_exists number;
231 l_leg_code varchar2(30);
232 l_ctl_business_group_id number;
233 l_ctl_legislation_code varchar2(30);
234 l_ctl_session_date date;
235 --
236 begin
237 -- setup contol variable, similar to ctl_globals variables i the form.
238 -- The values are based on the mode of the def bal being inserted.
239 --
240 if p_def_bal_bg_id is not null and p_def_bal_leg_code is null then
241 --
242 open get_bg_leg(p_def_bal_bg_id);
243 fetch get_bg_leg into l_leg_code;
244 close get_bg_leg;
245 --
246 l_ctl_business_group_id := p_def_bal_bg_id;
247 l_ctl_legislation_code := l_leg_code;
248 --
249 elsif p_def_bal_bg_id is null and p_def_bal_leg_code is not null then
250 l_ctl_business_group_id := '';
251 l_ctl_legislation_code := p_def_bal_leg_code;
252 else
253 l_ctl_business_group_id := '';
254 l_ctl_legislation_code := '';
255 end if;
256 --
257 -- now get a default date, if one hasn't been passed in
258 --
259 if p_effective_date is null then
260 open get_sess_date;
261 fetch get_sess_date into l_ctl_session_date;
262 if get_sess_date%notfound then
263 close get_sess_date;
264 l_ctl_session_date := trunc(sysdate);
265 end if;
266 else
267 l_ctl_session_date := p_effective_date;
268 end if;
269 --
270 -- now call the main procedure
271 --
272 insert_default_attributes
273 (p_balance_dimension_id => p_balance_dimension_id
274 ,p_balance_category_id => p_balance_category_id
275 ,p_ctl_bg_id => l_ctl_business_group_id
276 ,p_ctl_leg_code => l_ctl_legislation_code
277 ,p_ctl_sess_date => l_ctl_session_date
278 ,p_defined_balance_id => p_defined_balance_id
279 ,p_dfbl_bg_id => p_def_bal_bg_id
280 ,p_dfbl_leg_code => p_def_bal_leg_code);
281 end insert_default_attrib_wrapper;
282 -----------------------------------------------------------------------------
283 -- insert_default_attributes
284 -- Called directly when called from forms, or using the wrapper procedure
285 -- insert_default_attrib_wrapper when called from serverside code.
286 -----------------------------------------------------------------------------
287 procedure insert_default_attributes(p_balance_dimension_id number
288 ,p_balance_category_id number
289 ,p_ctl_bg_id number
290 ,p_ctl_leg_code varchar2
291 ,p_ctl_sess_date date
292 ,p_defined_balance_id number
293 ,p_dfbl_bg_id number
294 ,p_dfbl_leg_code varchar2)
295 is
296 --
297 cursor get_default_attributes(p_dim_id number
298 ,p_cat_id number
299 ,ctl_business_group_id number
300 ,ctl_legislation_code varchar2
301 ,ctl_session_date date)
302 is
303 select pbd.attribute_id
304 , pbd.bal_attribute_default_id
305 from pay_bal_attribute_defaults pbd
306 , pay_balance_categories_f pbc
307 , pay_bal_attribute_definitions bad
308 where bad.attribute_id = pbd.attribute_id
309 and ((ctl_business_group_id is not null
310 and bad.alterable = 'Y')
311 or ctl_business_group_id is null)
312 and nvl(pbd.business_group_id, nvl(ctl_business_group_id,-1))
313 = nvl(ctl_business_group_id,-1)
314 and nvl(pbd.legislation_code, nvl(ctl_legislation_code,' '))
315 = nvl(ctl_legislation_code,' ')
316 and pbc.balance_category_id = pbd.balance_category_id
317 and ctl_session_date between pbc.effective_start_date
318 and pbc.effective_end_date
319 and pbd.balance_dimension_id = p_dim_id
320 and pbd.balance_category_id = p_cat_id
321 order by pbd.attribute_id;
322 --
323 cursor attribute_exists(p_att_id number
324 ,p_def_bal_id number
325 ,p_bg number
326 ,p_leg varchar2)
327 is
328 select null
329 from pay_balance_attributes
330 where attribute_id = p_att_id
331 and defined_balance_id = p_def_bal_id
332 and nvl(business_group_id,-1) = nvl(p_bg, -1)
333 and nvl(legislation_code, 'NULL') = nvl(p_leg,'NULL');
334 --
335 l_exists number;
336 l_balance_attribute_id pay_balance_attributes.balance_attribute_id%type;
337 --
338 begin
339 for each_attribute in get_default_attributes(p_balance_dimension_id
340 ,p_balance_category_id
341 ,p_ctl_bg_id
342 ,p_ctl_leg_code
343 ,p_ctl_sess_date) loop
344 --
345 open attribute_exists(each_attribute.attribute_id
346 ,p_defined_balance_id
347 ,p_dfbl_bg_id
348 ,p_dfbl_leg_code);
349 fetch attribute_exists into l_exists;
350 if attribute_exists%notfound then
351 close attribute_exists;
352 --
353 pay_balance_attribute_api.create_balance_attribute
354 (p_validate => false
355 ,p_attribute_id => each_attribute.attribute_id
356 ,p_defined_balance_id => p_defined_balance_id
357 ,p_business_group_id => p_dfbl_bg_id
358 ,p_legislation_code => p_dfbl_leg_code
359 ,p_balance_attribute_id => l_balance_attribute_id
360 );
361 else
362 close attribute_exists;
363 end if;
364 end loop;
365 end insert_default_attributes;
366 -----------------------------------------------------------------------------
367 -- Name --
368 -- Insert_Row --
369 -- Purpose --
370 -- Table handler procedure that supports the insert of a defined --
371 -- balance via the Define Balance Type form. --
372 -- Arguments --
373 -- See below. --
374 -- Notes --
375 -- x_mode only needs to be set when called from forms. Procedure --
376 -- insert_default_attributes is called directly from the form, so don't --
377 -- need to call it in insert_row. But if insert row is being called from --
378 -- serverside code then x_mode is null, and the code will know to call --
379 -- insert_default_attrib_wrapper to insert default attributes, if any --
380 -- exist. --
381 -- --
382 -----------------------------------------------------------------------------
383 --
384 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
385 X_Defined_Balance_Id IN OUT NOCOPY NUMBER,
386 X_Business_Group_Id NUMBER,
387 X_Legislation_Code VARCHAR2,
388 X_Balance_Type_Id NUMBER,
389 X_Balance_Dimension_Id NUMBER,
390 X_Force_Latest_Balance_Flag VARCHAR2,
391 X_Legislation_Subgroup VARCHAR2,
392 X_Grossup_Allowed_Flag VARCHAR2 DEFAULT 'N',
393 x_balance_category_id number default null,
394 x_effective_date date default null,
395 x_mode varchar2 default null)
396 IS
397 --
398 CURSOR C IS SELECT rowid FROM pay_defined_balances
399 WHERE defined_balance_id = X_Defined_Balance_Id;
400 --
401 CURSOR C2 IS SELECT pay_defined_balances_s.nextval FROM sys.dual;
402 --
403 CURSOR C3 IS SELECT count(*) from pay_defined_balances
404 WHERE Balance_Type_Id = X_Balance_Type_Id
405 AND Grossup_Allowed_Flag = 'Y';
406 --
407 cursor get_bal_cat_id(p_bal_type number)
408 is
409 select balance_category_id
410 from pay_balance_types
411 where balance_type_id = p_bal_type;
412 --
413 cursor get_eff_date
414 is
415 select effective_date
416 from fnd_sessions
417 where session_id = userenv('sessionid');
418 --
419 l_exists number ;
420 l_save_run_bal_flag pay_defined_balances.save_run_balance%type;
421 l_bal_cat_id pay_balance_categories_f.balance_category_id%type;
422 l_eff_date date;
423 --
424 BEGIN
425 hr_utility.set_location('Entering pay_defined_balances_pkg.insert_row', 5);
426 --
427 -- Make sure that defined balance is unique.
428 chk_defined_balance
429 (X_Rowid,
430 X_Business_Group_Id,
431 X_Legislation_Code,
432 X_Balance_Type_Id,
433 X_Balance_Dimension_Id);
434 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 10);
435 --
436 if (X_Defined_Balance_Id is NULL) then
437 OPEN C2;
438 FETCH C2 INTO X_Defined_Balance_Id;
439 CLOSE C2;
440 end if;
441 --
442 OPEN C3;
443 FETCH C3 INTO l_exists;
444 if ( (l_exists = 0 and X_Grossup_Allowed_Flag = 'Y')
445 or (X_Grossup_Allowed_Flag = 'N') ) then
446 --
447 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 15);
448 --
449 -- check whether save_run_balance can be automatically derived
450 -- from the category and dimension flags.
451 --
452 if x_balance_category_id is null then -- could be called from serverside
453 --
454 -- get bal cat id for the balance type, if there is one
455 --
456 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 20);
457 --
458 open get_bal_cat_id(x_balance_type_id);
459 fetch get_bal_cat_id into l_bal_cat_id;
460 if get_bal_cat_id%notfound then
461 close get_bal_cat_id;
462 l_bal_cat_id := x_balance_category_id;
463 else
464 --
465 close get_bal_cat_id;
466 --
467 -- row returned, so if the effective date is null, default it.
468 --
469 if x_effective_date is null then
470 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 25);
471 open get_eff_date;
472 fetch get_eff_date into l_eff_date;
473 if get_eff_date%notfound then
474 l_eff_date := trunc(sysdate);
475 end if;
476 else
477 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 30);
478 l_eff_date := x_effective_date;
479 end if;
480 end if;
481 else -- x_balance_category is not null
482 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 35);
483 l_bal_cat_id := x_balance_category_id;
484 --
485 if x_effective_date is null then
486 hr_utility.set_message(801, 'PAY_34262_CAT_EFF_DATE_NULL');
487 hr_utility.raise_error;
488 else
489 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 40);
490 l_eff_date := x_effective_date;
491 end if;
492 end if;
493 --
494 l_save_run_bal_flag := set_save_run_bals_flag(l_bal_cat_id
495 ,l_eff_date
496 ,x_balance_dimension_id);
497 --
498 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 45);
499 --
500 INSERT INTO pay_defined_balances
501 (defined_balance_id,
502 business_group_id,
503 legislation_code,
504 balance_type_id,
505 balance_dimension_id,
506 force_latest_balance_flag,
507 legislation_subgroup,
508 grossup_allowed_flag,
509 save_run_balance)
510 VALUES
511 (X_Defined_Balance_Id,
512 X_Business_Group_Id,
513 X_Legislation_Code,
514 X_Balance_Type_Id,
515 X_Balance_Dimension_Id,
516 X_Force_Latest_Balance_Flag,
517 X_Legislation_Subgroup,
518 X_Grossup_Allowed_Flag,
519 l_save_run_bal_flag);
520 --
521 hr_utility.trace('here');
522 OPEN C;
523 FETCH C INTO X_Rowid;
524 if (C%NOTFOUND) then
525 CLOSE C;
526 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
527 hr_utility.set_message_token('PROCEDURE',
528 'pay_defined_balances_pkg.insert_row');
529 hr_utility.set_message_token('STEP','1');
530 hr_utility.raise_error;
531 end if;
532 CLOSE C;
533 --
534 -- insert balance attributes after defined balance created. Check the
535 -- pay_bal_attribute_defaults table. If a row exists for the category_id
536 -- of the balance_type of the defined balance just inserted, and the dimension
537 -- just inserted, then create the associated attribute.
538 --
539 hr_utility.trace('x_mode: '||x_mode);
540 --
541 if x_mode is null or x_mode <> 'FORM' then
542 --
543 -- if the variable l_bal_cat_id is null, then no need to try and insert
544 -- attribute_defaults
545 --
546 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 50);
547 if l_bal_cat_id is not null then
548 --
549 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 55);
550 insert_default_attrib_wrapper
551 (p_balance_dimension_id => x_balance_dimension_id
552 ,p_balance_category_id => l_bal_cat_id
553 ,p_def_bal_bg_id => x_business_group_id
554 ,p_def_bal_leg_code => x_legislation_code
555 ,p_defined_balance_id => x_defined_balance_id
556 ,p_effective_date => l_eff_date
557 );
558 hr_utility.set_location('pay_defined_balances_pkg.insert_row', 60);
559 end if;
560 end if;
561 else
562 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
563 hr_utility.set_message_token('PROCEDURE',
564 'pay_defined_balances_pkg.insert_row');
565 hr_utility.set_message_token('STEP','1');
566 hr_utility.raise_error;
567 end if;
568 END Insert_Row;
569 --
570 -----------------------------------------------------------------------------
571 -- Name --
572 -- Lock_Row --
573 -- Purpose --
574 -- Table handler procedure that supports the insert , update and delete --
575 -- of a defined balance by applying a lock on a defined balance in the --
576 -- Define Balance Type form. --
577 -- Arguments --
578 -- See below. --
579 -- Notes --
580 -- None. --
581 -----------------------------------------------------------------------------
582 --
583 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
584 X_Defined_Balance_Id NUMBER,
585 X_Business_Group_Id NUMBER,
586 X_Legislation_Code VARCHAR2,
587 X_Balance_Type_Id NUMBER,
588 X_Balance_Dimension_Id NUMBER,
589 X_Force_Latest_Balance_Flag VARCHAR2,
590 X_Legislation_Subgroup VARCHAR2,
591 X_Grossup_Allowed_Flag VARCHAR2) IS
592 --
593 CURSOR C IS SELECT * FROM pay_defined_balances
594 WHERE rowid = X_Rowid FOR UPDATE of Defined_Balance_Id NOWAIT;
595 --
596 Recinfo C%ROWTYPE;
597 --
598 BEGIN
599 --
600 OPEN C;
601 FETCH C INTO Recinfo;
602 if (C%NOTFOUND) then
603 CLOSE C;
604 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
605 hr_utility.set_message_token('PROCEDURE',
606 'pay_defined_balances_pkg.lock_row');
607 hr_utility.set_message_token('STEP','1');
608 hr_utility.raise_error;
609 end if;
610 CLOSE C;
611 --
612 -- Removed trailing spaces.
613 Recinfo.legislation_code := rtrim(Recinfo.legislation_code);
614 Recinfo.force_latest_balance_flag := rtrim(Recinfo.force_latest_balance_flag);
615 Recinfo.grossup_allowed_flag := rtrim(Recinfo.grossup_allowed_flag);
616 Recinfo.legislation_subgroup := rtrim(Recinfo.legislation_subgroup);
617 --
618 if ( ( (Recinfo.defined_balance_id = X_Defined_Balance_Id)
619 OR ( (Recinfo.defined_balance_id IS NULL)
620 AND (X_Defined_Balance_Id IS NULL)))
621 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
622 OR ( (Recinfo.business_group_id IS NULL)
623 AND (X_Business_Group_Id IS NULL)))
624 AND ( (Recinfo.legislation_code = X_Legislation_Code)
625 OR ( (Recinfo.legislation_code IS NULL)
626 AND (X_Legislation_Code IS NULL)))
627 AND ( (Recinfo.balance_type_id = X_Balance_Type_Id)
628 OR ( (Recinfo.balance_type_id IS NULL)
629 AND (X_Balance_Type_Id IS NULL)))
630 AND ( (Recinfo.balance_dimension_id = X_Balance_Dimension_Id)
631 OR ( (Recinfo.balance_dimension_id IS NULL)
632 AND (X_Balance_Dimension_Id IS NULL)))
633 AND ( (Recinfo.force_latest_balance_flag = X_Force_Latest_Balance_Flag)
634 OR ( (Recinfo.force_latest_balance_flag IS NULL)
635 AND (X_Force_Latest_Balance_Flag IS NULL)))
636 AND ( (Recinfo.grossup_allowed_flag = X_Grossup_Allowed_Flag)
637 OR ( (Recinfo.grossup_allowed_flag IS NULL)
638 AND (X_Grossup_Allowed_Flag IS NULL)))
639 AND ( (Recinfo.legislation_subgroup = X_Legislation_Subgroup)
640 OR ( (Recinfo.legislation_subgroup IS NULL)
641 AND (X_Legislation_Subgroup IS NULL)))
642 ) then
643 return;
644 else
645 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
646 APP_EXCEPTION.RAISE_EXCEPTION;
647 end if;
648 --
649 END Lock_Row;
650 --
651 -----------------------------------------------------------------------------
652 -- Name --
653 -- Update_Row --
654 -- Purpose --
655 -- Table handler procedure that supports the update of a defined --
656 -- balance via the Define Balance Type form. --
657 -- Arguments --
658 -- See below. --
659 -- Notes --
660 -- --
661 -----------------------------------------------------------------------------
662 --
663 PROCEDURE Update_Row(X_Rowid VARCHAR2,
664 X_Defined_Balance_Id NUMBER,
665 X_Business_Group_Id NUMBER,
666 X_Legislation_Code VARCHAR2,
667 X_Balance_Type_Id NUMBER,
668 X_Balance_Dimension_Id NUMBER,
669 X_Force_Latest_Balance_Flag VARCHAR2,
670 X_Legislation_Subgroup VARCHAR2,
671 X_Grossup_Allowed_Flag VARCHAR2) IS
672 --
673 l_exists number;
674 --
675 CURSOR C3 IS SELECT count(*) from pay_defined_balances
676 WHERE Balance_Type_Id = X_Balance_Type_Id
677 AND Grossup_Allowed_Flag = 'Y';
678 --
679 BEGIN
680 --
681 OPEN C3;
682 FETCH C3 INTO l_exists;
683 if ( (l_exists = 0 and X_Grossup_Allowed_Flag = 'Y')
684 or (X_Grossup_Allowed_Flag = 'N') ) then
685 --
686 UPDATE pay_defined_balances
687 SET defined_balance_id = X_Defined_Balance_Id,
688 business_group_id = X_Business_Group_Id,
689 legislation_code = X_Legislation_Code,
690 balance_type_id = X_Balance_Type_Id,
691 balance_dimension_id = X_Balance_Dimension_Id,
692 force_latest_balance_flag = X_Force_Latest_Balance_Flag,
693 legislation_subgroup = X_Legislation_Subgroup,
694 grossup_allowed_flag = X_Grossup_Allowed_Flag
695 WHERE rowid = X_rowid;
696 --
697 if (SQL%NOTFOUND) then
698 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
699 hr_utility.set_message_token('PROCEDURE',
700 'pay_defined_balances_pkg.update_row');
701 hr_utility.set_message_token('STEP','1');
702 hr_utility.raise_error;
703 end if;
704 --
705 else
706 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
707 hr_utility.set_message_token('PROCEDURE',
708 'pay_defined_balances_pkg.update_row');
709 hr_utility.set_message_token('STEP','1');
710 hr_utility.raise_error;
711 end if;
712 --
713 END Update_Row;
714 --
715 -----------------------------------------------------------------------------
716 -- Name --
717 -- Delete_Row --
718 -- Purpose --
719 -- Table handler procedure that supports the delete of a defined --
720 -- balance via the Define Balance Type form. --
721 -- Arguments --
722 -- See below. --
723 -- Notes --
724 -- --
725 -----------------------------------------------------------------------------
726 --
727 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
728 -- Extra Columns
729 X_Defined_Balance_Id NUMBER) IS
730 --
731 BEGIN
732 --
733 -- Check that the delete is valid.
734 chk_delete_defined_balance(X_Defined_Balance_Id);
735 --
736 -- Remove any latest balances for the defined balance.
737 delete_defined_balance(X_Defined_Balance_Id);
738 --
739 DELETE FROM pay_defined_balances
740 WHERE rowid = X_Rowid;
741 --
742 if (SQL%NOTFOUND) then
743 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
744 hr_utility.set_message_token('PROCEDURE',
745 'pay_defined_balances_pkg.delete_row');
746 hr_utility.set_message_token('STEP','1');
747 hr_utility.raise_error;
748 end if;
749 --
750 END Delete_Row;
751 --
752 -----------------------------------------------------------------------------
753 -- Name --
754 -- verify_save_run_bal_flag_upd --
755 -- Purpose --
756 -- Called from trigger pay_defined_balances_bru to prevent the update of --
757 -- SAVE_RUN_BALANCE flag from 'Y' to 'N' or null, when valid run --
758 -- balances exist for the defined balance.
759 -- Arguments --
760 -- See below. --
761 -- Notes --
762 --
763 -----------------------------------------------------------------------------
764 PROCEDURE verify_save_run_bal_flag_upd(p_defined_balance_id number
765 ,p_old_save_run_bal_flag varchar2
766 ,p_new_save_run_bal_flag varchar2) IS
767 --
768 cursor check_runbals (p_def_bal_id in number)
769 is
770 select 1
771 from pay_balance_validation pbv
772 , pay_run_balances prb
773 where pbv.run_balance_status = 'V'
774 and pbv.defined_balance_id = p_def_bal_id
775 and pbv.defined_balance_id = prb.defined_balance_id;
776 --
777 l_exists number;
778 --
779 BEGIN
780 --
781 hr_utility.set_location('Entering: pay_defined_balance_pkg.verify_save_run_bal_flag_upd', 5);
782 --
783 if p_old_save_run_bal_flag = 'Y' then
784 --
785 if p_new_save_run_bal_flag is null
786 or p_new_save_run_bal_flag <> 'Y' then
787 --
788 open check_runbals (p_defined_balance_id);
789 fetch check_runbals into l_exists;
790 if check_runbals%found then
791 --
792 -- raise error
793 --
794 close check_runbals;
795 hr_utility.set_location('pay_defined_balance_pkg.verify_save_run_bal_flag_upd', 10);
796 --
797 hr_utility.set_message(801, 'PAY_33528_SAVERUNBAL_INV_UPD');
798 hr_utility.raise_error;
799 --
800 else
801 close check_runbals;
802 hr_utility.set_location('pay_defined_balance_pkg.verify_save_run_bal_flag_upd', 20);
803 end if;
804 end if;
805 end if;
806 hr_utility.set_location('Leaving: pay_defined_balance_pkg.verify_save_run_bal_flag_upd', 30);
807 --
808 END verify_save_run_bal_flag_upd;
809 -----------------------------------------------------------------------------
810 END PAY_DEFINED_BALANCES_PKG;
[Home] [Help]