1 package body PAY_ELEMENT_CLASS_PKG as
2 /* $Header: pydec.pkb 120.0 2005/05/29 01:49:24 appldev noship $ */
3 --------------------------------------------------------------------------------
4 g_dummy number(1); -- Dummy for cursor returns which are not needed
5 g_business_group_id number(15); -- For validating translation;
6 g_legislation_code varchar2(150); -- For validating translation;
7 --------------------------------------------------------------------------------
8 function NAME_NOT_UNIQUE (
9 --
10 --******************************************************************************
11 --* Returns TRUE if the classification name is not unique within the legislation
12 --******************************************************************************
13 --
14 -- Parameters are:
15 --
16 p_classification_name varchar2,
17 p_legislation_code varchar2,
18 p_business_group_id number,
19 p_rowid varchar2)
20 --
21 return boolean is
22 --
23 v_match_found boolean;
24 --
25 cursor csr_matching_name is
26 select 1
27 from pay_element_classifications
28 where upper(classification_name) = upper(p_classification_name)
29 and nvl(legislation_code,nvl(p_legislation_code, ' '))
30 = nvl(p_legislation_code, ' ')
31 and (p_rowid is null
32 or (p_rowid is not null and p_rowid <> rowid))
33 and business_group_id = p_business_group_id;
34 --
35 begin
36 --
37 hr_utility.set_location ('PAY_ELEMENT_CLASSIFICATIONS_PKG',1);
38 --
39 open csr_matching_name;
40 fetch csr_matching_name into g_dummy;
41 v_match_found := csr_matching_name%found;
42 close csr_matching_name;
43 --
44 if v_match_found then
45 hr_utility.set_message (801,'HR_6310_ELE_CLASS_NAME');
46 hr_utility.raise_error;
47 end if;
48 --
49 return v_match_found;
50 --
51 end name_not_unique;
52 --------------------------------------------------------------------------------
53 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
54 p_legislation_code IN VARCHAR2) IS
55 BEGIN
56 g_business_group_id := p_business_group_id;
57 g_legislation_code := p_legislation_code;
58 END;
59 --------------------------------------------------------------------------------
60 procedure validate_translation(classification_id IN NUMBER,
61 language IN VARCHAR2,
62 classification_name IN VARCHAR2,
63 description IN VARCHAR2,
64 p_business_group_id IN NUMBER DEFAULT NULL,
65 p_legislation_code IN VARCHAR2 DEFAULT null) IS
66 /*
67
68 This procedure fails if a classification translation is already present in
69 the table for a given language. Otherwise, no action is performed. It is
70 used to ensure uniqueness of translated classification names.
71
72 */
73
74 --
75 -- This cursor implements the validation we require,
76 -- and expects that the various package globals are set before
77 -- the call to this procedure is made. This is done from the
78 -- user-named trigger 'TRANSLATIONS' in the form
79 --
80 cursor c_translation(p_language IN VARCHAR2,
81 p_class_name IN VARCHAR2,
82 p_class_id IN NUMBER,
83 p_bus_grp_id IN NUMBER,
84 p_leg_code IN varchar2) IS
85 SELECT 1
86 FROM pay_element_classifications_tl ect,
87 pay_element_classifications ecl
88 WHERE upper(ect.classification_name)=upper(p_class_name)
89 AND ect.classification_id = ecl.classification_id
90 AND ect.language = p_language
91 AND (ecl.classification_id <> p_class_id OR p_class_id IS NULL)
92 AND (ecl.business_group_id = p_bus_grp_id OR p_bus_grp_id IS NULL)
93 AND (ecl.legislation_code = p_leg_code OR p_leg_code IS NULL);
94
95 l_package_name VARCHAR2(80);
96 l_business_group_id NUMBER;
97 l_legislation_code VARCHAR2(150);
98
99 BEGIN
100 l_package_name := 'PAY_ELEMENT_CLASS_PKG.VALIDATE_TRANSLATION';
101 l_business_group_id := nvl(p_business_group_id, g_business_group_id);
102 l_legislation_code := nvl(p_legislation_code, g_legislation_code);
103 hr_utility.set_location (l_package_name,10);
104 OPEN c_translation(language, classification_name,classification_id,
105 l_business_group_id,l_legislation_code);
106 hr_utility.set_location (l_package_name,50);
107 FETCH c_translation INTO g_dummy;
108
109 IF c_translation%NOTFOUND THEN
110 hr_utility.set_location (l_package_name,60);
111 CLOSE c_translation;
112 ELSE
113 hr_utility.set_location (l_package_name,70);
114 CLOSE c_translation;
115 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
116 fnd_message.raise_error;
117 END IF;
118 hr_utility.set_location ('Leaving:'||l_package_name,80);
119 END validate_translation;
120 --------------------------------------------------------------------------------
121 function DELETION_ALLOWED (
122 --
123 --******************************************************************************
124 --* Returns TRUE if all deletion checks are passed. *
125 --******************************************************************************
126 --
127 -- Parameters are:
128 --
129 p_classification_id varchar2)
130 --
131 return boolean is
132 --
133 classification_type varchar2(10);
134 children_found boolean;
135 primary_classification boolean;
136 secondary_classification boolean;
137 --
138 cursor csr_class_type is
139 select decode (parent_classification_id,
140 null,'PRIMARY',
141 'SECONDARY')
142 from pay_element_classifications
143 where classification_id = p_classification_id;
144 --
145 function CHILD_CLASSIFICATIONS return boolean is
146 -- Check for child secondary classifications
147 cursor csr_2ndary_classifications is
148 select 1
149 from pay_element_classifications
150 where parent_classification_id = p_classification_id;
151 --
152 begin
153 hr_utility.set_location ('PAY_ELEMENT_CLASSIFICATIONS_PKG',3);
154 open csr_2ndary_classifications;
155 fetch csr_2ndary_classifications into g_dummy;
156 children_found := csr_2ndary_classifications%found;
157 close csr_2ndary_classifications;
158 if children_found then
159 hr_utility.set_message (801,'HR_6313_ELE_CLASS_DEL_EC');
160 end if;
161 return children_found;
162 end child_classifications;
163 --
164 function CHILD_ELEMENTS return boolean is
165 -- Check for child element types
166 cursor csr_element_types is
167 select 1
168 from pay_element_types_f
169 where classification_id = p_classification_id;
170 --
171 begin
172 hr_utility.set_location ('PAY_ELEMENT_CLASSIFICATIONS_PKG',4);
173 open csr_element_types;
174 fetch csr_element_types into g_dummy;
175 children_found := csr_element_types%found;
176 close csr_element_types;
177 if children_found then
178 hr_utility.set_message (801,'HR_6314_ELE_CLASS_DEL_ET');
179 end if;
180 return children_found;
181 end child_elements;
182 --
183 function CHILD_CLASS_RULES return boolean is
184 -- Check for child classification rules
185 cursor csr_class_rules is
186 select 1
187 from pay_ele_classification_rules
188 where classification_id = p_classification_id;
189 --
190 begin
191 hr_utility.set_location ('PAY_ELEMENT_CLASSIFICATIONS_PKG',5);
192 open csr_class_rules;
193 fetch csr_class_rules into g_dummy;
194 children_found := csr_class_rules%found;
195 close csr_class_rules;
196 if children_found then
197 hr_utility.set_message (801,'HR_6315_ELE_CLASS_DEL_ECR');
198 end if;
199 return children_found;
200 end child_class_rules;
201 --
202 function CHILD_BALANCE_CLASSES return boolean is
203 -- Check for child balance classifications
204 cursor csr_balance_classes is
205 select 1
206 from pay_balance_classifications
207 where classification_id = p_classification_id;
208 --
209 begin
210 hr_utility.set_location ('PAY_ELEMENT_CLASSIFICATIONS_PKG',6);
211 open csr_balance_classes;
212 fetch csr_balance_classes into g_dummy;
213 children_found := csr_balance_classes%found;
214 close csr_balance_classes;
215 if children_found then
216 hr_utility.set_message (801,'HR_6316_ELE_CLASS_DEL_BC');
217 end if;
218 return children_found;
219 end child_balance_classes;
220 --
221 function CHILD_SUB_CLASS_RULES return boolean is
222 -- Check for child sub-classification rules
223 cursor csr_sub_class_rules is
224 select 1
225 from pay_sub_classification_rules_f
226 where classification_id = p_classification_id;
227 --
228 begin
229 hr_utility.set_location ('PAY_ELEMENT_CLASSIFICATIONS_PKG',7);
230 open csr_sub_class_rules;
231 fetch csr_sub_class_rules into g_dummy;
232 children_found := csr_sub_class_rules%found;
233 close csr_sub_class_rules;
234 if children_found then
235 hr_utility.set_message (801,'HR_6317_ELE_CLASS_DEL_SCR');
236 end if;
237 return children_found;
238 end child_sub_class_rules;
239 --
240 begin
241 --
242 hr_utility.set_location ('PAY_ELEMENT_CLASSIFICATIONS_PKG',1);
243 --
244 -- Find out whether this is a primary or secondary classification
245 --
246 open csr_class_type;
247 fetch csr_class_type into classification_type;
248 primary_classification := (classification_type = 'PRIMARY');
249 secondary_classification := (classification_type = 'SECONDARY');
250 close csr_class_type;
251 --
252 hr_utility.set_location ('PAY_ELEMENT_CLASSIFICATIONS_PKG',2);
253 --
254 -- Checks for deletion permission
255 --
256 if child_balance_classes
257 --
258 or (( primary_classification
259 and ( child_classifications
260 or child_elements
261 or child_class_rules ))
262 --
263 or ( secondary_classification
264 and child_sub_class_rules)) then
265 --
266 hr_utility.trace ('Deletion not allowed');
267 hr_utility.raise_error;
268 return FALSE;
269 --
270 else
271 hr_utility.trace ('Deletion allowed');
272 return TRUE;
273 end if;
274 --
275 end deletion_allowed;
276 --------------------------------------------------------------------------------
277 function USER_CAN_MODIFY_PRIMARY (p_legislation_code varchar2) return boolean is
278 --
279 --******************************************************************************
280 --* Returns TRUE if there are no seeded primary classifications for the user's *
281 --* legislation *
282 --******************************************************************************
283 --
284 cursor csr_personnel is
285 select 1
286 from fnd_product_installations
287 where application_id = 800
288 and status = 'I';
289 --
290 cursor csr_seeded_data is
291 select 1
292 from pay_element_classifications
293 where legislation_code = p_legislation_code
294 and business_group_id is null;
295 --
296 cursor csr_localised is
297 select 'x'
298 from hr_legislation_installations li
299 where li.legislation_code = p_legislation_code;
300
301 v_modifiable boolean := TRUE;
302 v_local varchar2(1);
303 --
304 begin
305 --
306 hr_utility.set_location ('PAY_ELEMENT_CLASS_PKG',1);
307 --
308 open csr_personnel;
309 open csr_seeded_data;
310 --
311 fetch csr_seeded_data into g_dummy;
312 fetch csr_personnel into g_dummy;
313 --
314 if (csr_personnel%notfound
315 or csr_seeded_data%found) then
316 v_modifiable := FALSE;
317 end if;
318 --
319 close csr_seeded_data;
320 close csr_personnel;
321 --
322 open csr_localised;
323 --
324 fetch csr_localised into v_local;
325 --
326 if csr_localised%notfound then
327 null;
328 else
329 v_modifiable := FALSE;
330 end if;
331 --
332 hr_utility.set_location ('PAY_ELEMENT_CLASS_PKG',10);
333 return v_modifiable;
334 --
335 end user_can_modify_primary;
336 --------------------------------------------------------------------------------
337 procedure INSERT_ROW (
338 X_ROWID in out nocopy VARCHAR2,
339 X_CLASSIFICATION_ID in NUMBER,
340 X_BUSINESS_GROUP_ID in NUMBER,
341 X_LEGISLATION_CODE in VARCHAR2,
342 X_LEGISLATION_SUBGROUP in VARCHAR2,
343 X_COSTABLE_FLAG in VARCHAR2,
344 X_DEFAULT_HIGH_PRIORITY in NUMBER,
345 X_DEFAULT_LOW_PRIORITY in NUMBER,
346 X_DEFAULT_PRIORITY in NUMBER,
347 X_DISTRIBUTABLE_OVER_FLAG in VARCHAR2,
348 X_NON_PAYMENTS_FLAG in VARCHAR2,
349 X_COSTING_DEBIT_OR_CREDIT in VARCHAR2,
350 X_PARENT_CLASSIFICATION_ID in NUMBER,
351 X_CREATE_BY_DEFAULT_FLAG in VARCHAR2,
352 X_BALANCE_INITIALIZATION_FLAG in VARCHAR2,
353 X_OBJECT_VERSION_NUMBER in NUMBER,
354 X_CLASSIFICATION_NAME in VARCHAR2,
355 X_DESCRIPTION in VARCHAR2,
356 X_CREATION_DATE in DATE,
357 X_CREATED_BY in NUMBER,
358 X_LAST_UPDATE_DATE in DATE,
359 X_LAST_UPDATED_BY in NUMBER,
360 X_LAST_UPDATE_LOGIN in NUMBER,
361 X_FREQ_RULE_ENABLED in VARCHAR2 default null
362 ) is
363 cursor C is select ROWID from PAY_ELEMENT_CLASSIFICATIONS
364 where CLASSIFICATION_ID = X_CLASSIFICATION_ID
365 ;
366 begin
367 insert into PAY_ELEMENT_CLASSIFICATIONS (
368 CLASSIFICATION_ID,
369 CLASSIFICATION_NAME,
370 BUSINESS_GROUP_ID,
371 LEGISLATION_CODE,
372 LEGISLATION_SUBGROUP,
373 COSTABLE_FLAG,
374 DEFAULT_HIGH_PRIORITY,
375 DEFAULT_LOW_PRIORITY,
376 DEFAULT_PRIORITY,
377 DISTRIBUTABLE_OVER_FLAG,
378 NON_PAYMENTS_FLAG,
379 COSTING_DEBIT_OR_CREDIT,
380 PARENT_CLASSIFICATION_ID,
381 CREATE_BY_DEFAULT_FLAG,
382 BALANCE_INITIALIZATION_FLAG,
383 OBJECT_VERSION_NUMBER,
384 CREATION_DATE,
388 LAST_UPDATE_LOGIN,
385 CREATED_BY,
386 LAST_UPDATE_DATE,
387 LAST_UPDATED_BY,
389 FREQ_RULE_ENABLED
390 ) values (
391 X_CLASSIFICATION_ID,
392 X_CLASSIFICATION_NAME,
393 X_BUSINESS_GROUP_ID,
394 X_LEGISLATION_CODE,
395 X_LEGISLATION_SUBGROUP,
396 X_COSTABLE_FLAG,
397 X_DEFAULT_HIGH_PRIORITY,
398 X_DEFAULT_LOW_PRIORITY,
399 X_DEFAULT_PRIORITY,
400 X_DISTRIBUTABLE_OVER_FLAG,
401 X_NON_PAYMENTS_FLAG,
402 X_COSTING_DEBIT_OR_CREDIT,
403 X_PARENT_CLASSIFICATION_ID,
404 X_CREATE_BY_DEFAULT_FLAG,
405 X_BALANCE_INITIALIZATION_FLAG,
406 X_OBJECT_VERSION_NUMBER,
407 X_CREATION_DATE,
408 X_CREATED_BY,
409 X_LAST_UPDATE_DATE,
410 X_LAST_UPDATED_BY,
411 X_LAST_UPDATE_LOGIN,
412 X_FREQ_RULE_ENABLED
413 );
414
415 insert into PAY_ELEMENT_CLASSIFICATIONS_TL (
416 CLASSIFICATION_ID,
417 CLASSIFICATION_NAME,
418 DESCRIPTION,
419 LAST_UPDATE_DATE,
420 LAST_UPDATED_BY,
421 LAST_UPDATE_LOGIN,
422 CREATED_BY,
423 CREATION_DATE,
424 LANGUAGE,
425 SOURCE_LANG
426 ) select
427 X_CLASSIFICATION_ID,
428 X_CLASSIFICATION_NAME,
429 X_DESCRIPTION,
430 X_LAST_UPDATE_DATE,
431 X_LAST_UPDATED_BY,
432 X_LAST_UPDATE_LOGIN,
433 X_CREATED_BY,
434 X_CREATION_DATE,
435 L.LANGUAGE_CODE,
436 userenv('LANG')
437 from FND_LANGUAGES L
438 where L.INSTALLED_FLAG in ('I', 'B')
439 and not exists
440 (select NULL
441 from PAY_ELEMENT_CLASSIFICATIONS_TL T
442 where T.CLASSIFICATION_ID = X_CLASSIFICATION_ID
443 and T.LANGUAGE = L.LANGUAGE_CODE);
444
445 open c;
446 fetch c into X_ROWID;
447 if (c%notfound) then
448 close c;
449 raise no_data_found;
450 end if;
451 close c;
452
453 end INSERT_ROW;
454 --------------------------------------------------------------------------------
455 procedure LOCK_ROW (
456 X_CLASSIFICATION_ID in NUMBER,
457 X_BUSINESS_GROUP_ID in NUMBER,
458 X_LEGISLATION_CODE in VARCHAR2,
459 X_LEGISLATION_SUBGROUP in VARCHAR2,
460 X_COSTABLE_FLAG in VARCHAR2,
461 X_DEFAULT_HIGH_PRIORITY in NUMBER,
462 X_DEFAULT_LOW_PRIORITY in NUMBER,
463 X_DEFAULT_PRIORITY in NUMBER,
464 X_DISTRIBUTABLE_OVER_FLAG in VARCHAR2,
465 X_NON_PAYMENTS_FLAG in VARCHAR2,
466 X_COSTING_DEBIT_OR_CREDIT in VARCHAR2,
467 X_PARENT_CLASSIFICATION_ID in NUMBER,
468 X_CREATE_BY_DEFAULT_FLAG in VARCHAR2,
469 X_BALANCE_INITIALIZATION_FLAG in VARCHAR2,
470 X_OBJECT_VERSION_NUMBER in NUMBER,
471 X_CLASSIFICATION_NAME in VARCHAR2,
472 X_DESCRIPTION in VARCHAR2,
473 X_FREQ_RULE_ENABLED in VARCHAR2 default null
474 ) is
475 cursor c is select
476 BUSINESS_GROUP_ID,
477 LEGISLATION_CODE,
478 LEGISLATION_SUBGROUP,
479 COSTABLE_FLAG,
480 DEFAULT_HIGH_PRIORITY,
481 DEFAULT_LOW_PRIORITY,
482 DEFAULT_PRIORITY,
483 DISTRIBUTABLE_OVER_FLAG,
484 NON_PAYMENTS_FLAG,
485 COSTING_DEBIT_OR_CREDIT,
486 PARENT_CLASSIFICATION_ID,
487 CREATE_BY_DEFAULT_FLAG,
488 BALANCE_INITIALIZATION_FLAG,
489 OBJECT_VERSION_NUMBER,
490 FREQ_RULE_ENABLED
491 from PAY_ELEMENT_CLASSIFICATIONS
492 where CLASSIFICATION_ID = X_CLASSIFICATION_ID
493 for update of CLASSIFICATION_ID nowait;
494 recinfo c%rowtype;
495
496 cursor c1 is select
497 CLASSIFICATION_NAME,
498 DESCRIPTION,
499 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
500 from PAY_ELEMENT_CLASSIFICATIONS_TL
501 where CLASSIFICATION_ID = X_CLASSIFICATION_ID
502 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
503 for update of CLASSIFICATION_ID nowait;
504 begin
505 open c;
506 fetch c into recinfo;
507 if (c%notfound) then
508 close c;
509 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
510 app_exception.raise_exception;
511 end if;
512 close c;
513 if ( ((recinfo.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
514 OR ((recinfo.BUSINESS_GROUP_ID is null) AND (X_BUSINESS_GROUP_ID is null)))
515 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
516 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
517 AND ((recinfo.LEGISLATION_SUBGROUP = X_LEGISLATION_SUBGROUP)
518 OR ((recinfo.LEGISLATION_SUBGROUP is null) AND (X_LEGISLATION_SUBGROUP is null)))
519 AND ((recinfo.COSTABLE_FLAG = X_COSTABLE_FLAG)
520 OR ((recinfo.COSTABLE_FLAG is null) AND (X_COSTABLE_FLAG is null)))
521 AND ((recinfo.DEFAULT_HIGH_PRIORITY = X_DEFAULT_HIGH_PRIORITY)
522 OR ((recinfo.DEFAULT_HIGH_PRIORITY is null) AND (X_DEFAULT_HIGH_PRIORITY is null)))
523 AND ((recinfo.DEFAULT_LOW_PRIORITY = X_DEFAULT_LOW_PRIORITY)
524 OR ((recinfo.DEFAULT_LOW_PRIORITY is null) AND (X_DEFAULT_LOW_PRIORITY is null)))
525 AND ((recinfo.DEFAULT_PRIORITY = X_DEFAULT_PRIORITY)
529 AND ((recinfo.NON_PAYMENTS_FLAG = X_NON_PAYMENTS_FLAG)
526 OR ((recinfo.DEFAULT_PRIORITY is null) AND (X_DEFAULT_PRIORITY is null)))
527 AND ((recinfo.DISTRIBUTABLE_OVER_FLAG = X_DISTRIBUTABLE_OVER_FLAG)
528 OR ((recinfo.DISTRIBUTABLE_OVER_FLAG is null) AND (X_DISTRIBUTABLE_OVER_FLAG is null)))
530 OR ((recinfo.NON_PAYMENTS_FLAG is null) AND (X_NON_PAYMENTS_FLAG is null)))
531 AND ((recinfo.COSTING_DEBIT_OR_CREDIT = X_COSTING_DEBIT_OR_CREDIT)
532 OR ((recinfo.COSTING_DEBIT_OR_CREDIT is null) AND (X_COSTING_DEBIT_OR_CREDIT is null)))
533 AND ((recinfo.PARENT_CLASSIFICATION_ID = X_PARENT_CLASSIFICATION_ID)
534 OR ((recinfo.PARENT_CLASSIFICATION_ID is null) AND (X_PARENT_CLASSIFICATION_ID is null)))
535 AND ((recinfo.CREATE_BY_DEFAULT_FLAG = X_CREATE_BY_DEFAULT_FLAG)
536 OR ((recinfo.CREATE_BY_DEFAULT_FLAG is null) AND (X_CREATE_BY_DEFAULT_FLAG is null)))
537 AND ((recinfo.BALANCE_INITIALIZATION_FLAG = X_BALANCE_INITIALIZATION_FLAG)
538 OR ((recinfo.BALANCE_INITIALIZATION_FLAG is null) AND (X_BALANCE_INITIALIZATION_FLAG is null)))
539 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
540 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
541 AND ((recinfo.FREQ_RULE_ENABLED = X_FREQ_RULE_ENABLED)
542 OR ((recinfo.FREQ_RULE_ENABLED is null) AND (X_FREQ_RULE_ENABLED is null)))
543 ) then
544 null;
545 else
546 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
547 app_exception.raise_exception;
548 end if;
549
550 for tlinfo in c1 loop
551 if (tlinfo.BASELANG = 'Y') then
552 if ( (tlinfo.CLASSIFICATION_NAME = X_CLASSIFICATION_NAME)
553 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
554 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
555 ) then
556 null;
557 else
558 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
559 app_exception.raise_exception;
560 end if;
561 end if;
562 end loop;
563 return;
564 end LOCK_ROW;
565 --------------------------------------------------------------------------------
566 procedure UPDATE_ROW (
567 X_CLASSIFICATION_ID in NUMBER,
568 X_BUSINESS_GROUP_ID in NUMBER,
569 X_LEGISLATION_CODE in VARCHAR2,
570 X_LEGISLATION_SUBGROUP in VARCHAR2,
571 X_COSTABLE_FLAG in VARCHAR2,
572 X_DEFAULT_HIGH_PRIORITY in NUMBER,
573 X_DEFAULT_LOW_PRIORITY in NUMBER,
574 X_DEFAULT_PRIORITY in NUMBER,
575 X_DISTRIBUTABLE_OVER_FLAG in VARCHAR2,
576 X_NON_PAYMENTS_FLAG in VARCHAR2,
577 X_COSTING_DEBIT_OR_CREDIT in VARCHAR2,
578 X_PARENT_CLASSIFICATION_ID in NUMBER,
579 X_CREATE_BY_DEFAULT_FLAG in VARCHAR2,
580 X_BALANCE_INITIALIZATION_FLAG in VARCHAR2,
581 X_OBJECT_VERSION_NUMBER in NUMBER,
582 X_CLASSIFICATION_NAME in VARCHAR2,
583 X_DESCRIPTION in VARCHAR2,
584 X_LAST_UPDATE_DATE in DATE,
585 X_LAST_UPDATED_BY in NUMBER,
586 X_LAST_UPDATE_LOGIN in NUMBER,
587 X_MESG_FLG out nocopy Boolean,
588 X_FREQ_RULE_ENABLED in VARCHAR2 default null
589 ) is
590
591 P_SOURCE_LANG PAY_ELEMENT_CLASSIFICATIONS_TL.SOURCE_LANG%type;
592 begin
593 begin
594 select source_lang
595 into P_SOURCE_LANG
596 from PAY_ELEMENT_CLASSIFICATIONS_TL
597 where
598 CLASSIFICATION_ID = X_CLASSIFICATION_ID
599 and userenv('LANG') = LANGUAGE;
600 Exception
601 when no_data_found then
602 raise no_data_found;
603 end;
604
605 if P_SOURCE_LANG = userenv('LANG') then
606 X_MESG_FLG :=false;
607 else
608 X_MESG_FLG :=true;
609 end if;
610
611 update PAY_ELEMENT_CLASSIFICATIONS set
612 CLASSIFICATION_NAME= decode(P_SOURCE_LANG,userenv('LANG'),X_CLASSIFICATION_NAME,CLASSIFICATION_NAME),
613 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
614 LEGISLATION_CODE = X_LEGISLATION_CODE,
615 LEGISLATION_SUBGROUP = X_LEGISLATION_SUBGROUP,
616 COSTABLE_FLAG = X_COSTABLE_FLAG,
617 DEFAULT_HIGH_PRIORITY = X_DEFAULT_HIGH_PRIORITY,
618 DEFAULT_LOW_PRIORITY = X_DEFAULT_LOW_PRIORITY,
619 DEFAULT_PRIORITY = X_DEFAULT_PRIORITY,
620 DISTRIBUTABLE_OVER_FLAG = X_DISTRIBUTABLE_OVER_FLAG,
621 NON_PAYMENTS_FLAG = X_NON_PAYMENTS_FLAG,
622 COSTING_DEBIT_OR_CREDIT = X_COSTING_DEBIT_OR_CREDIT,
623 PARENT_CLASSIFICATION_ID = X_PARENT_CLASSIFICATION_ID,
624 CREATE_BY_DEFAULT_FLAG = X_CREATE_BY_DEFAULT_FLAG,
625 BALANCE_INITIALIZATION_FLAG = X_BALANCE_INITIALIZATION_FLAG,
626 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
627 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
628 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
629 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
630 FREQ_RULE_ENABLED = X_FREQ_RULE_ENABLED
631 where CLASSIFICATION_ID = X_CLASSIFICATION_ID;
632
633 if (sql%notfound) then
634 raise no_data_found;
635 end if;
636
637 update PAY_ELEMENT_CLASSIFICATIONS_TL set
638 CLASSIFICATION_NAME = X_CLASSIFICATION_NAME,
639 DESCRIPTION = X_DESCRIPTION,
640 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
641 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
642 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
643 SOURCE_LANG = userenv('LANG')
647 if (sql%notfound) then
644 where CLASSIFICATION_ID = X_CLASSIFICATION_ID
645 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
646
648 raise no_data_found;
649 end if;
650 end UPDATE_ROW;
651 --------------------------------------------------------------------------------
652 procedure DELETE_ROW (
653 X_CLASSIFICATION_ID in NUMBER
654 ) is
655 begin
656 delete from PAY_ELEMENT_CLASSIFICATIONS_TL
657 where CLASSIFICATION_ID = X_CLASSIFICATION_ID;
658
659 if (sql%notfound) then
660 raise no_data_found;
661 end if;
662
663 delete from PAY_ELEMENT_CLASSIFICATIONS
664 where CLASSIFICATION_ID = X_CLASSIFICATION_ID;
665
666 if (sql%notfound) then
667 raise no_data_found;
668 end if;
669 end DELETE_ROW;
670 --------------------------------------------------------------------------------
671 procedure ADD_LANGUAGE
672 is
673 begin
674 delete from PAY_ELEMENT_CLASSIFICATIONS_TL T
675 where not exists
676 (select NULL
677 from PAY_ELEMENT_CLASSIFICATIONS B
678 where B.CLASSIFICATION_ID = T.CLASSIFICATION_ID
679 );
680
681 update PAY_ELEMENT_CLASSIFICATIONS_TL T set (
682 CLASSIFICATION_NAME,
683 DESCRIPTION
684 ) = (select
685 B.CLASSIFICATION_NAME,
686 B.DESCRIPTION
687 from PAY_ELEMENT_CLASSIFICATIONS_TL B
688 where B.CLASSIFICATION_ID = T.CLASSIFICATION_ID
689 and B.LANGUAGE = T.SOURCE_LANG)
690 where (
691 T.CLASSIFICATION_ID,
692 T.LANGUAGE
693 ) in (select
694 SUBT.CLASSIFICATION_ID,
695 SUBT.LANGUAGE
696 from PAY_ELEMENT_CLASSIFICATIONS_TL SUBB, PAY_ELEMENT_CLASSIFICATIONS_TL SUBT
697 where SUBB.CLASSIFICATION_ID = SUBT.CLASSIFICATION_ID
698 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
699 and (SUBB.CLASSIFICATION_NAME <> SUBT.CLASSIFICATION_NAME
700 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
701 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
702 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
703 ));
704
705 insert into PAY_ELEMENT_CLASSIFICATIONS_TL (
706 CLASSIFICATION_ID,
707 CLASSIFICATION_NAME,
708 DESCRIPTION,
709 LAST_UPDATE_DATE,
710 LAST_UPDATED_BY,
711 LAST_UPDATE_LOGIN,
712 CREATED_BY,
713 CREATION_DATE,
714 LANGUAGE,
715 SOURCE_LANG
716 ) select
717 B.CLASSIFICATION_ID,
718 B.CLASSIFICATION_NAME,
719 B.DESCRIPTION,
720 B.LAST_UPDATE_DATE,
721 B.LAST_UPDATED_BY,
722 B.LAST_UPDATE_LOGIN,
723 B.CREATED_BY,
724 B.CREATION_DATE,
725 L.LANGUAGE_CODE,
726 B.SOURCE_LANG
727 from PAY_ELEMENT_CLASSIFICATIONS_TL B, FND_LANGUAGES L
728 where L.INSTALLED_FLAG in ('I', 'B')
729 and B.LANGUAGE = userenv('LANG')
730 and not exists
731 (select NULL
732 from PAY_ELEMENT_CLASSIFICATIONS_TL T
733 where T.CLASSIFICATION_ID = B.CLASSIFICATION_ID
734 and T.LANGUAGE = L.LANGUAGE_CODE);
735 end ADD_LANGUAGE;
736 --------------------------------------------------------------------------------
737 procedure unique_chk(E_CLASSIFICATION_NAME in VARCHAR2, E_LEGISLATION_CODE in VARCHAR2)
738 is
739 result varchar2(255);
740 Begin
741 SELECT count(*) INTO result
742 FROM PAY_ELEMENT_CLASSIFICATIONS
743 WHERE nvl(CLASSIFICATION_NAME,'~null~') = nvl(E_CLASSIFICATION_NAME,'~null~')
744 and nvl(LEGISLATION_CODE,'~null~') = nvl(E_LEGISLATION_CODE,'~null~')
745 and BUSINESS_GROUP_ID is NULL;
746 --
747 IF (result>1) THEN
748 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
749 hr_utility.set_message_token('PROCEDURE','PAY_ELEMENT_CLASS_PKB.UNIQUE_CHK');
750 hr_utility.set_message_token('STEP','1');
751 hr_utility.raise_error;
752 END IF;
753 EXCEPTION
754 when NO_DATA_FOUND then
755 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
756 hr_utility.set_message_token('PROCEDURE','PAY_ELEMENT_CLASS_PKB.UNIQUE_CHK');
757 hr_utility.set_message_token('STEP','1');
758 hr_utility.raise_error;
759 end unique_chk;
760 --------------------------------------------------------------------------------
761 procedure TRANSLATE_ROW (
762 X_E_CLASSIFICATION_NAME in VARCHAR2,
763 X_E_LEGISLATION_CODE in VARCHAR2,
764 X_CLASSIFICATION_NAME in VARCHAR2,
765 X_DESCRIPTION in VARCHAR2,
766 X_OWNER in VARCHAR2) is
767 begin
768 -- unique_chk(X_E_CLASSIFICATION_NAME,X_E_LEGISLATION_CODE);
769 --
770 UPDATE pay_element_classifications_tl
771 SET CLASSIFICATION_NAME = nvl(X_CLASSIFICATION_NAME,CLASSIFICATION_NAME),
772 description = nvl(x_description,description),
773 last_update_date = SYSDATE,
774 last_updated_by = decode(x_owner,'SEED',1,0),
775 last_update_login = 0,
776 source_lang = userenv('LANG')
777 WHERE userenv('LANG') IN (language,source_lang)
778 AND CLASSIFICATION_ID in
779 (select CLASSIFICATION_ID
780 from pay_element_classifications
781 where nvl(CLASSIFICATION_NAME,'~null~')=nvl(X_E_CLASSIFICATION_NAME,'~null~')
782 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_E_LEGISLATION_CODE,'~null~')
783 and BUSINESS_GROUP_ID is NULL);
784 --
785 if (sql%notfound) then -- trap system errors during update
786 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
787 -- hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_CLASS_PKB.TRANSLATE_ROW');
788 -- hr_utility.set_message_token('STEP','1');
789 -- hr_utility.raise_error;
790 null;
791 end if;
792 end TRANSLATE_ROW;
793 --------------------------------------------------------------------------------
794 end PAY_ELEMENT_CLASS_PKG;
[Home] [Help]