[Home] [Help]
PACKAGE BODY: APPS.PER_ASS_STATUSES_PKG
Source
1 PACKAGE BODY PER_ASS_STATUSES_PKG AS
2 /* $Header: peast01t.pkb 120.6 2011/04/28 10:16:41 sidsaxen ship $ */
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 procedure OWNER_TO_WHO (
9 X_OWNER in VARCHAR2,
10 X_CREATION_DATE out nocopy DATE,
11 X_CREATED_BY out nocopy NUMBER,
12 X_LAST_UPDATE_DATE out nocopy DATE,
13 X_LAST_UPDATED_BY out nocopy NUMBER,
14 X_LAST_UPDATE_LOGIN out nocopy NUMBER
15 ) is
16 begin
17 if X_OWNER = 'SEED' then
18 X_CREATED_BY := 1;
19 X_LAST_UPDATED_BY := 1;
20 else
21 X_CREATED_BY := 0;
22 X_LAST_UPDATED_BY := 0;
23 end if;
24 X_CREATION_DATE := sysdate;
25 X_LAST_UPDATE_DATE := sysdate;
26 X_LAST_UPDATE_LOGIN := 0;
27 end OWNER_TO_WHO;
28
29 procedure KEY_TO_IDS (
30 X_USER_STATUS in VARCHAR2,
31 X_BUSINESS_GROUP_NAME in VARCHAR2,
32 X_LEGISLATION_CODE in VARCHAR2,
33 X_ASSIGNMENT_STATUS_TYPE_ID out nocopy VARCHAR2,
34 X_BUSINESS_GROUP_ID out nocopy NUMBER
35 ) is
36 cursor CSR_BUSINESS_GROUP (
37 X_NAME in VARCHAR2
38 ) is
39 select ORG.ORGANIZATION_ID
40 from HR_ALL_ORGANIZATION_UNITS ORG
41 ,HR_ORGANIZATION_INFORMATION OI1
42 where ORG.ORGANIZATION_ID = OI1.ORGANIZATION_ID
43 and OI1.ORG_INFORMATION_CONTEXT = 'CLASS'
44 and OI1.ORG_INFORMATION1 = 'HR_BG'
45 and OI1.ORG_INFORMATION2 = 'Y'
46 and ORG.NAME = X_NAME;
47 cursor CSR_ASSIGNMENT_STATUS_TYPE (
48 X_USER_STATUS VARCHAR2,
49 X_BUSINESS_GROUP_ID in NUMBER,
50 X_LEGISLATION_CODE in VARCHAR2
51 ) is
52 select AST.ASSIGNMENT_STATUS_TYPE_ID
53 from PER_ASSIGNMENT_STATUS_TYPES AST
54 where AST.USER_STATUS = X_USER_STATUS
55 and ( AST.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
56 or ( AST.BUSINESS_GROUP_ID is null
57 and X_BUSINESS_GROUP_ID is null))
58 and ( AST.LEGISLATION_CODE = X_LEGISLATION_CODE
59 or ( AST.LEGISLATION_CODE is null
60 and X_LEGISLATION_CODE is null));
61 cursor csr_max_seq is
62 select max(ASSIGNMENT_STATUS_TYPE_ID)
63 from per_assignment_status_types;
64
65 cursor CSR_SEQUENCE is
66 select PER_ASSIGNMENT_STATUS_TYPES_S.nextval
67 from dual;
68 L_BUSINESS_GROUP_ID NUMBER;
69 L_MAX_SEQ NUMBER;
70 begin
71 open CSR_BUSINESS_GROUP (
72 X_BUSINESS_GROUP_NAME
73 );
74 fetch CSR_BUSINESS_GROUP into L_BUSINESS_GROUP_ID;
75 close CSR_BUSINESS_GROUP;
76 X_BUSINESS_GROUP_ID := L_BUSINESS_GROUP_ID;
77 open CSR_ASSIGNMENT_STATUS_TYPE (
78 X_USER_STATUS,
79 L_BUSINESS_GROUP_ID,
80 X_LEGISLATION_CODE
81 );
82
83 fetch CSR_ASSIGNMENT_STATUS_TYPE into X_ASSIGNMENT_STATUS_TYPE_ID;
84 if (CSR_ASSIGNMENT_STATUS_TYPE%notfound) then
85 open CSR_SEQUENCE;
86 open CSR_MAX_SEQ;
87 fetch CSR_MAX_SEQ into L_MAX_SEQ;
88 if CSR_MAX_SEQ%notfound then
89 L_MAX_SEQ := 0;
90 close CSR_MAX_SEQ;
91 end if;
92 close CSR_MAX_SEQ;
93 fetch CSR_SEQUENCE into X_ASSIGNMENT_STATUS_TYPE_ID;
94 close CSR_SEQUENCE; -- fix 7197717
95 while(X_ASSIGNMENT_STATUS_TYPE_ID <= L_MAX_SEQ)
96 LOOP
97 open CSR_SEQUENCE; -- fix 7197717
98 fetch CSR_SEQUENCE into X_ASSIGNMENT_STATUS_TYPE_ID;
99 close CSR_SEQUENCE; -- fix 7197717
100 END LOOP;
101
102 end if;
103 close CSR_ASSIGNMENT_STATUS_TYPE;
104 end KEY_TO_IDS;
105
106 PROCEDURE UNIQUENESS_CHECK(P_USER_STATUS VARCHAR2,
107 P_BUSINESS_GROUP_ID NUMBER,
108 P_LEGISLATION_CODE VARCHAR2,
109 P_ROWID VARCHAR2,
110 P_ASSIGNMENT_STATUS_TYPE_ID NUMBER,
111 P_STARTUP_MODE VARCHAR2,
112 P_PRIMARY_FLAG VARCHAR2,
113 P_AMENDMENT VARCHAR2,
114 P_C_ACTIVE_FLAG VARCHAR2,
115 P_C_DEFAULT_FLAG VARCHAR2,
116 P_DEFAULT_FLAG VARCHAR2,
117 P_ACTIVE_FLAG VARCHAR2,
118 P_PER_SYSTEM_STATUS VARCHAR2,
119 P_MODE VARCHAR2) IS
120 L_DUMMY1 number;
121 L_DUMMY2 number;
122 v_exists1 number;
123 v_exists2 number;
124 v_exists3 number;
125 v_exists4 number;
126 CURSOR C1 IS
127 select 1
128 from per_assignment_status_types_tl ttl,
129 per_assignment_status_types t
130 where upper(ttl.user_status) = upper(P_USER_STATUS)
131 and nvl(t.business_group_id, nvl(P_BUSINESS_GROUP_ID, -9999) )
132 = nvl(P_BUSINESS_GROUP_ID, -9999)
133 and nvl(t.legislation_code, nvl(P_LEGISLATION_CODE, 'XXX') )
134 = nvl(P_LEGISLATION_CODE, 'XXX')
135 and (P_ROWID is null
136 or P_ROWID <> t.rowid)
137 and t.assignment_status_type_id = ttl.assignment_status_type_id
138 and ttl.LANGUAGE = userenv('LANG')
139 and not exists (
140 select null
141 from per_ass_status_type_amends a
142 where a.assignment_status_type_id =
143 t.assignment_status_type_id
144 and a.business_group_id + 0 = P_BUSINESS_GROUP_ID);
145 CURSOR C2 IS
146 select 1
147 from per_ass_status_type_amends_tl atl,
148 per_ass_status_type_amends a
149 where upper(atl.user_status) = upper(P_USER_STATUS)
150 and a.business_group_id + 0 = P_BUSINESS_GROUP_ID
151 and a.ass_status_type_amend_id = atl.ass_status_type_amend_id
152 and atl.LANGUAGE = userenv('LANG')
153 and (P_ROWID is null
154 or a.assignment_status_type_id <> P_ASSIGNMENT_STATUS_TYPE_ID);
155 BEGIN
156 OPEN C1;
157 FETCH C1 INTO L_DUMMY1;
158 IF C1%NOTFOUND THEN
159 CLOSE C1;
160 OPEN C2;
161 FETCH C2 INTO L_DUMMY2;
162 IF C2%NOTFOUND THEN
163 CLOSE C2;
164 NULL;
165 ELSE
166 CLOSE C2;
167 hr_utility.set_message('801','HR_7602_DEF_STATUS_EXISTS');
168 hr_utility.raise_error;
169 END IF;
170 ELSE
171 CLOSE C1;
172 hr_utility.set_message('801','HR_7602_DEF_STATUS_EXISTS');
173 hr_utility.raise_error;
174 END IF;
175 END UNIQUENESS_CHECK;
176
177 PROCEDURE PRE_UPDATE(P_ACTIVE_FLAG VARCHAR2,
178 P_DEFAULT_FLAG VARCHAR2,
179 P_USER_STATUS VARCHAR2,
180 P_PAY_SYSTEM_STATUS VARCHAR2,
181 P_LAST_UPDATE_DATE DATE,
182 P_LAST_UPDATED_BY NUMBER,
183 P_LAST_UPDATE_LOGIN NUMBER,
184 P_CREATED_BY NUMBER,
185 P_CREATION_DATE DATE,
186 P_ASS_STATUS_TYPE_ID NUMBER,
187 P_AMENDMENT VARCHAR2) IS
188 BEGIN
189 update per_ass_status_type_amends a
190 set a.active_flag = P_ACTIVE_FLAG,
191 a.default_flag = P_DEFAULT_FLAG,
192 a.user_status = P_USER_STATUS,
193 a.pay_system_status = P_PAY_SYSTEM_STATUS,
194 a.last_update_date = P_LAST_UPDATE_DATE,
195 a.last_updated_by = P_LAST_UPDATED_BY,
196 a.last_update_login = P_LAST_UPDATE_LOGIN,
197 a.created_by = P_CREATED_BY,
198 a.creation_date = P_CREATION_DATE
199 where a.ass_status_type_amend_id = P_ASS_STATUS_TYPE_ID;
200 --MLS
201 update per_ass_status_type_amends_tl atl
202 set atl.user_status = P_USER_STATUS,
203 atl.last_update_date = P_LAST_UPDATE_DATE,
204 atl.last_updated_by = P_LAST_UPDATED_BY,
205 atl.last_update_login = P_LAST_UPDATE_LOGIN,
206 atl.created_by = P_CREATED_BY,
207 atl.creation_date = P_CREATION_DATE
208 where atl.ass_status_type_amend_id = P_ASS_STATUS_TYPE_ID
209 and atl.LANGUAGE = userenv('LANG');
210
211 END PRE_UPDATE;
212 PROCEDURE INSERT_AMENDS(P_ASS_STATUS_TYPE_AMEND_ID IN OUT NOCOPY NUMBER,
213 P_ASSIGNMENT_STATUS_TYPE_ID NUMBER,
214 P_BUSINESS_GROUP_ID NUMBER,
215 P_ACTIVE_FLAG VARCHAR2,
216 P_DEFAULT_FLAG VARCHAR2,
217 P_USER_STATUS VARCHAR2,
218 P_PAY_SYSTEM_STATUS VARCHAR2,
219 P_PER_SYSTEM_STATUS VARCHAR2,
220 P_LAST_UPDATE_DATE DATE,
221 P_LAST_UPDATED_BY NUMBER,
222 P_LAST_UPDATE_LOGIN NUMBER,
223 P_CREATED_BY NUMBER,
224 P_CREATION_DATE DATE) IS
225 L_ID NUMBER;
226 BEGIN
227 select per_ass_status_type_amends_s.nextval
228 into L_ID
229 from sys.dual;
230 P_ASS_STATUS_TYPE_AMEND_ID := L_ID;
231
232 --
233 -- Added the following code as a part of Zero Downtime Patching Project.
234 -- Code Starts Here.
235 --
236
237 BEGIN
238 PER_RIC_PKG.chk_integrity (
239 p_entity_name => 'PER_ASS_STATUS_TYPE_AMENDS',
240 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
241 PER_RIC_PKG.ref_info_rec('PER_ASSIGNMENT_STATUS_TYPES', PER_RIC_PKG.column_info_tbl(
242 PER_RIC_PKG.col_info_rec('ASSIGNMENT_STATUS_TYPE_ID',NULL,P_ASSIGNMENT_STATUS_TYPE_ID,NULL))),
243 PER_RIC_PKG.ref_info_rec('HR_ALL_ORGANIZATION_UNITS', PER_RIC_PKG.column_info_tbl(
244 PER_RIC_PKG.col_info_rec('ORGANIZATION_ID',NULL,P_BUSINESS_GROUP_ID,NULL)))
245 ),
246 p_ref_type => 'INS');
247
248 END;
249 --
250 -- Code Ends Here.
251 --
252
253 insert into per_ass_status_type_amends(
254 ASS_STATUS_TYPE_AMEND_ID,
255 ASSIGNMENT_STATUS_TYPE_ID,
256 BUSINESS_GROUP_ID,
257 ACTIVE_FLAG,
258 DEFAULT_FLAG,
259 USER_STATUS,
260 PAY_SYSTEM_STATUS,
261 PER_SYSTEM_STATUS,
262 LAST_UPDATE_DATE,
263 LAST_UPDATED_BY,
264 LAST_UPDATE_LOGIN,
265 CREATED_BY,
266 CREATION_DATE)
267 values(
268 P_ASS_STATUS_TYPE_AMEND_ID,
269 P_ASSIGNMENT_STATUS_TYPE_ID,
270 P_BUSINESS_GROUP_ID,
271 P_ACTIVE_FLAG,
272 P_DEFAULT_FLAG,
273 P_USER_STATUS,
274 P_PAY_SYSTEM_STATUS,
275 P_PER_SYSTEM_STATUS,
276 P_LAST_UPDATE_DATE,
277 P_LAST_UPDATED_BY,
278 P_LAST_UPDATE_LOGIN,
279 P_CREATED_BY,
280 P_CREATION_DATE);
281 -- MLS
282
283 --
284 -- Added the following code as a part of Zero Downtime Patching Project.
285 -- Code Starts Here.
286 --
287
288 BEGIN
289 PER_RIC_PKG.chk_integrity (
290 p_entity_name => 'PER_ASS_STATUS_TYPE_AMENDS_TL',
291 p_ref_entity => 'PER_ASS_STATUS_TYPE_AMENDS',
292 p_ref_column_name => 'ASS_STATUS_TYPE_AMEND_ID',
293 p_ref_col_value_number => P_ASS_STATUS_TYPE_AMEND_ID,
294 p_ref_col_value_varchar => NULL,
295 p_ref_col_value_date => NULL,
296 p_ref_type => 'INS');
297
298 END;
299 --
300 -- Code Ends Here.
301 --
302 insert into per_ass_status_type_amends_tl(
303 ASS_STATUS_TYPE_AMEND_ID,
304 LANGUAGE,
305 SOURCE_LANG,
306 USER_STATUS,
307 LAST_UPDATE_DATE,
308 LAST_UPDATED_BY,
309 LAST_UPDATE_LOGIN,
310 CREATED_BY,
311 CREATION_DATE)
312 select
313 P_ASS_STATUS_TYPE_AMEND_ID,
314 L.LANGUAGE_CODE,
315 B.LANGUAGE_CODE,
316 P_USER_STATUS,
317 P_LAST_UPDATE_DATE,
318 P_LAST_UPDATED_BY,
319 P_LAST_UPDATE_LOGIN,
320 P_CREATED_BY,
321 P_CREATION_DATE
322 from FND_LANGUAGES L, FND_LANGUAGES B
323 where L.INSTALLED_FLAG in ('I', 'B')
324 and B.INSTALLED_FLAG = 'B';
325 -- MLS end
326 END INSERT_AMENDS;
327
328 --
329 -- For a given business group / legislation combination there must be one and
330 -- only one active default assignment status for each personnel system status.
331 -- The user can define many assignment statuses based on a system status but
332 -- only one can be the actve default at any one time.
333 --
334 procedure chk_dflt_per_sys_statuses
335 (
336 p_business_group_id number,
337 p_legislation_code varchar2
338 ) is
339 --
340 type varchar2_table is table of varchar2(30) index by binary_integer;
341 --
342 cursor csr1 is
343 select lookup_code status
344 from hr_lookups
345 where lookup_type = 'PER_ASS_SYS_STATUS'
346 order by lookup_code;
347 --
348 cursor csr2 is
349 select t.per_system_status status
350 from per_assignment_status_types t
351 where t.per_system_status is not null
352 and t.default_flag = 'Y'
353 and t.active_flag = 'Y'
354 and nvl(t.business_group_id, nvl(p_business_group_id, -9999) ) =
355 nvl(p_business_group_id, -9999)
356 and nvl(t.legislation_code, nvl(p_legislation_code, 'XXX') ) =
357 nvl(p_legislation_code, 'XXX')
358 and not exists (select null
359 from per_ass_status_type_amends a
360 where a.assignment_status_type_id =
361 t.assignment_status_type_id
362 and a.business_group_id =
363 p_business_group_id)
364 union all
365 select a.per_system_status status
366 from per_ass_status_type_amends a
367 where a.per_system_status is not null
368 and a.default_flag = 'Y'
369 and a.active_flag = 'Y'
370 and a.business_group_id = p_business_group_id
371 order by 1;
372 --
373 system_statuses varchar2_table;
374 system_status_count number := 0;
375 user_statuses varchar2_table;
376 user_status_count number := 0;
377 --
378 begin
379 --
380 --
381 -- Populate a list with the personnel system statuses.
382 --
383 for system_rec in csr1 loop
384 system_status_count := system_status_count + 1;
385 system_statuses(system_status_count) := system_rec.status;
386 end loop;
387 --
388 --
389 -- Populate a list with the active default assignment statuses as
390 -- defined by the user.
391 --
392 for user_rec in csr2 loop
393 user_status_count := user_status_count + 1;
394 user_statuses(user_status_count) := user_rec.status;
395 end loop;
396 --
397 --
398 -- Make sure that the number of personnel system statuses is matched by the
399 -- number of active default assignment statuses.
400 --
401 if system_status_count <> user_status_count then
402 hr_utility.set_message(801, 'HR_7214_ASS_STAT_ONE_ONLY_ONE');
403 hr_utility.raise_error;
404 end if;
405 --
406 --
407 -- Compare the list of personnel system statuses with the user defined
408 -- active default assignment statuses. There should be a one to one match
409 -- which signals that each personnel system status is used only once as the
410 -- active default.
411 --
412 for i in 1..system_status_count loop
413 if system_statuses(i) <> user_statuses(i) then
414 hr_utility.set_message(801, 'HR_7214_ASS_STAT_ONE_ONLY_ONE');
415 hr_utility.raise_error;
416 end if;
417 end loop;
418 --
419 end chk_dflt_per_sys_statuses;
420 --
421 procedure INSERT_ROW (
422 X_ROWID in out nocopy VARCHAR2,
423 X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER,
424 X_BUSINESS_GROUP_ID in NUMBER,
425 X_LEGISLATION_CODE in VARCHAR2,
426 X_ACTIVE_FLAG in VARCHAR2,
427 X_DEFAULT_FLAG in VARCHAR2,
428 X_PRIMARY_FLAG in VARCHAR2,
429 X_PAY_SYSTEM_STATUS in VARCHAR2,
430 X_PER_SYSTEM_STATUS in VARCHAR2,
431 X_USER_STATUS in VARCHAR2,
432 X_EXTERNAL_STATUS in VARCHAR2,
433 X_CREATION_DATE in DATE,
434 X_CREATED_BY in NUMBER,
435 X_LAST_UPDATE_DATE in DATE,
436 X_LAST_UPDATED_BY in NUMBER,
437 X_LAST_UPDATE_LOGIN in NUMBER
438 ) is
439 cursor C is select ROWID from PER_ASSIGNMENT_STATUS_TYPES
440 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
441 begin
442 insert into PER_ASSIGNMENT_STATUS_TYPES (
443 ASSIGNMENT_STATUS_TYPE_ID,
444 BUSINESS_GROUP_ID,
445 LEGISLATION_CODE,
446 USER_STATUS,
447 ACTIVE_FLAG,
448 DEFAULT_FLAG,
449 PRIMARY_FLAG,
450 PAY_SYSTEM_STATUS,
451 PER_SYSTEM_STATUS,
452 CREATION_DATE,
453 CREATED_BY,
454 LAST_UPDATE_DATE,
455 LAST_UPDATED_BY,
456 LAST_UPDATE_LOGIN
457 ) values (
458 X_ASSIGNMENT_STATUS_TYPE_ID,
459 X_BUSINESS_GROUP_ID,
460 X_LEGISLATION_CODE,
461 X_USER_STATUS,
462 X_ACTIVE_FLAG,
463 X_DEFAULT_FLAG,
464 X_PRIMARY_FLAG,
465 X_PAY_SYSTEM_STATUS,
466 X_PER_SYSTEM_STATUS,
467 X_CREATION_DATE,
468 X_CREATED_BY,
469 X_LAST_UPDATE_DATE,
470 X_LAST_UPDATED_BY,
471 X_LAST_UPDATE_LOGIN
472 );
473
474 --
475 -- Added the following code as a part of Zero Downtime Patching Project.
476 -- Code Starts Here.
477 --
478
479 BEGIN
480 PER_RIC_PKG.chk_integrity (
481 p_entity_name => 'PER_ASSIGNMENT_STATUS_TYPES_TL',
482 p_ref_entity => 'PER_ASSIGNMENT_STATUS_TYPES',
483 p_ref_column_name => 'ASSIGNMENT_STATUS_TYPE_ID',
484 p_ref_col_value_number => X_ASSIGNMENT_STATUS_TYPE_ID,
485 p_ref_col_value_varchar => NULL,
486 p_ref_col_value_date => NULL,
487 p_ref_type => 'INS');
488
489 END;
490 --
491 -- Code Ends Here.
492 --
493
494 insert into PER_ASSIGNMENT_STATUS_TYPES_TL (
495 ASSIGNMENT_STATUS_TYPE_ID,
496 USER_STATUS,
497 EXTERNAL_STATUS,
498 LAST_UPDATE_DATE,
499 LAST_UPDATED_BY,
500 LAST_UPDATE_LOGIN,
501 CREATED_BY,
502 CREATION_DATE,
503 LANGUAGE,
504 SOURCE_LANG
505 ) select
506 X_ASSIGNMENT_STATUS_TYPE_ID,
507 X_USER_STATUS,
508 X_EXTERNAL_STATUS,
509 X_LAST_UPDATE_DATE,
510 X_LAST_UPDATED_BY,
511 X_LAST_UPDATE_LOGIN,
512 X_CREATED_BY,
513 X_CREATION_DATE,
514 L.LANGUAGE_CODE,
515 userenv('LANG')
516 from FND_LANGUAGES L
517 where L.INSTALLED_FLAG in ('I', 'B')
518 and not exists
519 (select NULL
520 from PER_ASSIGNMENT_STATUS_TYPES_TL T
521 where T.ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
522 and T.LANGUAGE = L.LANGUAGE_CODE);
523
524 open c;
525 fetch c into X_ROWID;
526 if (c%notfound) then
527 close c;
528 raise no_data_found;
529 end if;
530 close c;
531
532 end INSERT_ROW;
533
534 procedure LOCK_ROW (
535 X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER,
536 X_BUSINESS_GROUP_ID in NUMBER,
537 X_LEGISLATION_CODE in VARCHAR2,
538 X_ACTIVE_FLAG in VARCHAR2,
539 X_DEFAULT_FLAG in VARCHAR2,
540 X_PRIMARY_FLAG in VARCHAR2,
541 X_PAY_SYSTEM_STATUS in VARCHAR2,
542 X_PER_SYSTEM_STATUS in VARCHAR2,
543 X_USER_STATUS in VARCHAR2
544 ) is
545 cursor c is select
546 BUSINESS_GROUP_ID,
547 LEGISLATION_CODE,
551 PAY_SYSTEM_STATUS,
548 ACTIVE_FLAG,
549 DEFAULT_FLAG,
550 PRIMARY_FLAG,
552 PER_SYSTEM_STATUS
553 from PER_ASSIGNMENT_STATUS_TYPES
554 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
555 for update of ASSIGNMENT_STATUS_TYPE_ID nowait;
556 recinfo c%rowtype;
557
558 cursor c1 is select
559 USER_STATUS,
560 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
561 from PER_ASSIGNMENT_STATUS_TYPES_TL
562 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
563 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
564 for update of ASSIGNMENT_STATUS_TYPE_ID nowait;
565 begin
566 open c;
567 fetch c into recinfo;
568 if (c%notfound) then
569 close c;
570 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
571 app_exception.raise_exception;
572 end if;
573 close c;
574 if ( ((recinfo.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
575 OR ((recinfo.BUSINESS_GROUP_ID is null) AND (X_BUSINESS_GROUP_ID is null)))
576 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
577 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
578 AND (recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
579 AND (recinfo.DEFAULT_FLAG = X_DEFAULT_FLAG)
580 AND (recinfo.PRIMARY_FLAG = X_PRIMARY_FLAG)
581 AND ((recinfo.PAY_SYSTEM_STATUS = X_PAY_SYSTEM_STATUS)
582 OR ((recinfo.PAY_SYSTEM_STATUS is null) AND (X_PAY_SYSTEM_STATUS is null)))
583 AND ((recinfo.PER_SYSTEM_STATUS = X_PER_SYSTEM_STATUS)
584 OR ((recinfo.PER_SYSTEM_STATUS is null) AND (X_PER_SYSTEM_STATUS is null)))
585 ) then
586 null;
587 else
588 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
589 app_exception.raise_exception;
590 end if;
591
592 for tlinfo in c1 loop
593 if (tlinfo.BASELANG = 'Y') then
594 if ( (tlinfo.USER_STATUS = X_USER_STATUS)
595 ) then
596 null;
597 else
598 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
599 app_exception.raise_exception;
600 end if;
601 end if;
602 end loop;
603 return;
604 end LOCK_ROW;
605
606 procedure UPDATE_ROW (
607 X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER,
608 X_BUSINESS_GROUP_ID in NUMBER,
609 X_LEGISLATION_CODE in VARCHAR2,
610 X_ACTIVE_FLAG in VARCHAR2,
611 X_DEFAULT_FLAG in VARCHAR2,
612 X_PRIMARY_FLAG in VARCHAR2,
613 X_PAY_SYSTEM_STATUS in VARCHAR2,
614 X_PER_SYSTEM_STATUS in VARCHAR2,
615 X_USER_STATUS in VARCHAR2,
616 X_EXTERNAL_STATUS in VARCHAR2,
617 X_LAST_UPDATE_DATE in DATE,
618 X_LAST_UPDATED_BY in NUMBER,
619 X_LAST_UPDATE_LOGIN in NUMBER
620 ) is
621 begin
622 -- start of bug 5411889
623 -- added an if condition so that the seeded record status
624 -- is not changed when updating the record.
625
626 if X_BUSINESS_GROUP_ID is not null then
627
628 update PER_ASSIGNMENT_STATUS_TYPES set
629 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
630 LEGISLATION_CODE = X_LEGISLATION_CODE,
631 USER_STATUS = X_USER_STATUS, -- Bug 2731841
632 ACTIVE_FLAG = X_ACTIVE_FLAG,
633 DEFAULT_FLAG = X_DEFAULT_FLAG,
634 PRIMARY_FLAG = X_PRIMARY_FLAG,
635 PAY_SYSTEM_STATUS = X_PAY_SYSTEM_STATUS,
636 PER_SYSTEM_STATUS = X_PER_SYSTEM_STATUS,
637 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
638 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
639 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
640 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
641
642 else
643
644 update PER_ASSIGNMENT_STATUS_TYPES set
645 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
646 LEGISLATION_CODE = X_LEGISLATION_CODE,
647 -- USER_STATUS = X_USER_STATUS, -- Bug 2731841
648 ACTIVE_FLAG = X_ACTIVE_FLAG,
649 DEFAULT_FLAG = X_DEFAULT_FLAG,
650 PRIMARY_FLAG = X_PRIMARY_FLAG,
651 PAY_SYSTEM_STATUS = X_PAY_SYSTEM_STATUS,
652 PER_SYSTEM_STATUS = X_PER_SYSTEM_STATUS,
653 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
654 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
655 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
656 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
657
658 end if;
659 -- end of bug 5411889
660
661 if (sql%notfound) then
662 raise no_data_found;
663 end if;
664
665 update PER_ASSIGNMENT_STATUS_TYPES_TL set
666 USER_STATUS = X_USER_STATUS,
667 EXTERNAL_STATUS = X_EXTERNAL_STATUS,
668 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
669 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
670 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
671 SOURCE_LANG = userenv('LANG')
672 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
673 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
674
675 if (sql%notfound) then
676 raise no_data_found;
677 end if;
678 end UPDATE_ROW;
679
680 procedure DELETE_ROW (
681 X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER
682 ) is
683 begin
684 delete from PER_ASSIGNMENT_STATUS_TYPES_TL
685 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
686
687 if (sql%notfound) then
688 raise no_data_found;
689 end if;
690 --
691 -- Added the following code as a part of Zero Downtime Patching Project.
692 -- Code Starts Here.
693 --
694
695 BEGIN
696 PER_RIC_PKG.chk_integrity (
697 p_entity_name => 'PER_ASSIGNMENT_STATUS_TYPES',
698 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
699 PER_RIC_PKG.ref_info_rec('PAY_STATUS_PROCESSING_RULES_F', PER_RIC_PKG.column_info_tbl(
700 PER_RIC_PKG.col_info_rec('ASSIGNMENT_STATUS_TYPE_ID',NULL,X_ASSIGNMENT_STATUS_TYPE_ID,NULL))),
701 PER_RIC_PKG.ref_info_rec('PER_ALL_ASSIGNMENTS_F', PER_RIC_PKG.column_info_tbl(
702 PER_RIC_PKG.col_info_rec('ASSIGNMENT_STATUS_TYPE_ID',NULL,X_ASSIGNMENT_STATUS_TYPE_ID,NULL))),
703 PER_RIC_PKG.ref_info_rec('PER_ASS_STATUS_TYPE_AMENDS', PER_RIC_PKG.column_info_tbl(
704 PER_RIC_PKG.col_info_rec('ASSIGNMENT_STATUS_TYPE_ID',NULL,X_ASSIGNMENT_STATUS_TYPE_ID,NULL))),
705 PER_RIC_PKG.ref_info_rec('PER_LETTER_REQUEST_LINES', PER_RIC_PKG.column_info_tbl(
706 PER_RIC_PKG.col_info_rec('ASSIGNMENT_STATUS_TYPE_ID',NULL,X_ASSIGNMENT_STATUS_TYPE_ID,NULL))),
707 PER_RIC_PKG.ref_info_rec('PER_SECONDARY_ASS_STATUSES', PER_RIC_PKG.column_info_tbl(
708 PER_RIC_PKG.col_info_rec('ASSIGNMENT_STATUS_TYPE_ID',NULL,X_ASSIGNMENT_STATUS_TYPE_ID,NULL)))
709 ),
710 p_ref_type => 'DEL');
711
712 END;
713 --
714 -- Code Ends Here.
715 --
716 delete from PER_ASSIGNMENT_STATUS_TYPES
717 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
718
719 if (sql%notfound) then
720 raise no_data_found;
721 end if;
722 end DELETE_ROW;
723 --
724 procedure LOAD_ROW (
725 X_STATUS in VARCHAR2,
726 X_BUSINESS_GROUP_NAME in VARCHAR2,
727 X_LEGISLATION_CODE in VARCHAR2,
728 X_ACTIVE_FLAG in VARCHAR2,
729 X_DEFAULT_FLAG in VARCHAR2,
730 X_PRIMARY_FLAG in VARCHAR2,
731 X_PAY_SYSTEM_STATUS in VARCHAR2,
732 X_PER_SYSTEM_STATUS in VARCHAR2,
733 X_USER_STATUS in VARCHAR2,
734 X_OWNER in VARCHAR2,
735 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
736 X_CUSTOM_MODE IN VARCHAR2 default null
737 )
738 is
739 X_ROWID ROWID;
740 X_ASSIGNMENT_STATUS_TYPE_ID NUMBER;
741 X_BUSINESS_GROUP_ID NUMBER;
742 X_CREATION_DATE DATE :=sysdate;
743 X_CREATED_BY NUMBER;
744 -- X_LAST_UPDATE_DATE DATE;
745 X_LAST_UPDATED_BY NUMBER;
746 X_LAST_UPDATE_LOGIN NUMBER;
747 f_luby number; -- entity owner in file
748 f_ludate date; -- entity update date in file
749 db_luby number; -- entity owner in db
750 db_ludate date; -- entity update date in db
751 begin
752 if X_OWNER = 'SEED' then
753 X_CREATED_BY := 1;
754 else
755 X_CREATED_BY := 0;
756 end if;
757
758 KEY_TO_IDS (
759 X_STATUS,
760 X_BUSINESS_GROUP_NAME,
761 X_LEGISLATION_CODE,
762 X_ASSIGNMENT_STATUS_TYPE_ID,
763 X_BUSINESS_GROUP_ID
764 );
765 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
766 /*
767 OWNER_TO_WHO (
768 X_OWNER,
769 X_CREATION_DATE,
770 X_CREATED_BY,
771 X_LAST_UPDATE_DATE,
772 X_LAST_UPDATED_BY,
773 X_LAST_UPDATE_LOGIN
774 );*/
775
776 begin
777 f_luby := fnd_load_util.owner_id(X_OWNER);
778 -- Translate char last_update_date to date
779 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
780 select LAST_UPDATED_BY, LAST_UPDATE_DATE
781 into db_luby, db_ludate
782 from PER_ASSIGNMENT_STATUS_TYPES
783 where ASSIGNMENT_STATUS_TYPE_ID = TO_NUMBER(X_ASSIGNMENT_STATUS_TYPE_ID);
784
785 -- Test for customization and version
786 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
787 db_ludate, X_CUSTOM_MODE)) then
788 UPDATE_ROW (
789 X_ASSIGNMENT_STATUS_TYPE_ID,
790 X_BUSINESS_GROUP_ID,
791 X_LEGISLATION_CODE,
792 X_ACTIVE_FLAG,
793 X_DEFAULT_FLAG,
794 X_PRIMARY_FLAG,
795 X_PAY_SYSTEM_STATUS,
796 X_PER_SYSTEM_STATUS,
797 X_USER_STATUS,
798 X_USER_STATUS,
799 f_ludate,
800 f_luby,
801 0);
802 end if;
803 exception
804 when no_data_found then
805 INSERT_ROW (
806 X_ROWID,
807 X_ASSIGNMENT_STATUS_TYPE_ID,
808 X_BUSINESS_GROUP_ID,
809 X_LEGISLATION_CODE,
810 X_ACTIVE_FLAG,
811 X_DEFAULT_FLAG,
812 X_PRIMARY_FLAG,
813 X_PAY_SYSTEM_STATUS,
814 X_PER_SYSTEM_STATUS,
815 X_USER_STATUS,
816 X_USER_STATUS,
817 X_CREATION_DATE,
818 X_CREATED_BY,
819 f_ludate,
820 f_luby,
821 0);
822 end;
823 end LOAD_ROW;
824 --
825 procedure TRANSLATE_ROW (
826 X_STATUS in VARCHAR2,
827 X_BUSINESS_GROUP_NAME in VARCHAR2,
828 X_LEGISLATION_CODE in VARCHAR2,
829 X_USER_STATUS in VARCHAR2,
830 X_OWNER in VARCHAR2,
831 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
832 X_CUSTOM_MODE IN VARCHAR2 default null
833 )
834 is
835 X_ASSIGNMENT_STATUS_TYPE_ID NUMBER;
836 X_BUSINESS_GROUP_ID NUMBER;
837 X_CREATION_DATE DATE;
838 X_CREATED_BY NUMBER;
839 -- X_LAST_UPDATE_DATE DATE;
840 X_LAST_UPDATED_BY NUMBER;
841 X_LAST_UPDATE_LOGIN NUMBER;
842 f_luby number; -- entity owner in file
843 f_ludate date; -- entity update date in file
844 db_luby number; -- entity owner in db
845 db_ludate date; -- entity update date in db
846 begin
847 KEY_TO_IDS (
848 X_STATUS,
849 X_BUSINESS_GROUP_NAME,
850 X_LEGISLATION_CODE,
851 X_ASSIGNMENT_STATUS_TYPE_ID,
852 X_BUSINESS_GROUP_ID
853 );
854 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
855 /*
856 OWNER_TO_WHO (
857 X_OWNER,
858 X_CREATION_DATE,
859 X_CREATED_BY,
860 X_LAST_UPDATE_DATE,
861 X_LAST_UPDATED_BY,
862 X_LAST_UPDATE_LOGIN
863 );*/
864
865 -- Translate owner to file_last_updated_by
866 f_luby := fnd_load_util.owner_id(x_owner);
867
868 -- Translate char last_update_date to date
869 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
870 select LAST_UPDATED_BY, LAST_UPDATE_DATE
871 into db_luby, db_ludate
872 from PER_ASSIGNMENT_STATUS_TYPES_TL
873 where ASSIGNMENT_STATUS_TYPE_ID = TO_NUMBER(X_ASSIGNMENT_STATUS_TYPE_ID)
874 and LANGUAGE=userenv('LANG');
875
876 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
877 db_ludate,X_CUSTOM_MODE)) then
878
879 update PER_ASSIGNMENT_STATUS_TYPES_TL set
880 USER_STATUS = X_USER_STATUS,
881 EXTERNAL_STATUS = X_USER_STATUS, -- Bug fix 3627126.
882 LAST_UPDATE_DATE = db_ludate,
883 LAST_UPDATED_BY = db_luby,
884 LAST_UPDATE_LOGIN = 0,
885 SOURCE_LANG = userenv('LANG')
886 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
887 and ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
888 end if;
889 end TRANSLATE_ROW;
890 --
891 procedure ADD_LANGUAGE
892 is
893 begin
894 -- process PER_ASSIGNMENT_STATUS_TYPES_TL table
895 delete from PER_ASSIGNMENT_STATUS_TYPES_TL T
896 where not exists
897 (select NULL
898 from PER_ASSIGNMENT_STATUS_TYPES B
899 where B.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
900 );
901
902 update PER_ASSIGNMENT_STATUS_TYPES_TL T set (
903 USER_STATUS
904 ) = (select
905 B.USER_STATUS
909 where (
906 from PER_ASSIGNMENT_STATUS_TYPES_TL B
907 where B.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
908 and B.LANGUAGE = T.SOURCE_LANG)
910 T.ASSIGNMENT_STATUS_TYPE_ID,
911 T.LANGUAGE
912 ) in (select
913 SUBT.ASSIGNMENT_STATUS_TYPE_ID,
914 SUBT.LANGUAGE
915 from PER_ASSIGNMENT_STATUS_TYPES_TL SUBB, PER_ASSIGNMENT_STATUS_TYPES_TL SUBT
916 where SUBB.ASSIGNMENT_STATUS_TYPE_ID = SUBT.ASSIGNMENT_STATUS_TYPE_ID
917 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
918 and (SUBB.USER_STATUS <> SUBT.USER_STATUS
919 ));
920
921 insert into PER_ASSIGNMENT_STATUS_TYPES_TL (
922 ASSIGNMENT_STATUS_TYPE_ID,
923 USER_STATUS,
924 LAST_UPDATE_DATE,
925 LAST_UPDATED_BY,
926 LAST_UPDATE_LOGIN,
927 CREATED_BY,
928 CREATION_DATE,
929 LANGUAGE,
930 SOURCE_LANG
931 ) select /*+ INDEX(b)*/
932 B.ASSIGNMENT_STATUS_TYPE_ID,
933 B.USER_STATUS,
934 B.LAST_UPDATE_DATE,
935 B.LAST_UPDATED_BY,
936 B.LAST_UPDATE_LOGIN,
937 B.CREATED_BY,
938 B.CREATION_DATE,
939 L.LANGUAGE_CODE,
940 B.SOURCE_LANG
941 from PER_ASSIGNMENT_STATUS_TYPES_TL B, FND_LANGUAGES L
942 where L.INSTALLED_FLAG in ('I', 'B')
943 and B.LANGUAGE = userenv('LANG')
944 and not exists
945 (select NULL
946 from PER_ASSIGNMENT_STATUS_TYPES_TL T
947 where T.ASSIGNMENT_STATUS_TYPE_ID = B.ASSIGNMENT_STATUS_TYPE_ID
948 and T.LANGUAGE = L.LANGUAGE_CODE);
949 --
950 -- process PER_ASS_STATUS_TYPES_AMENDS_TL table
951 /*
952 delete from PER_ASS_STATUS_TYPES_AMENDS_TL T
953 where not exists
954 (select NULL
955 from PER_ASS_STATUS_TYPES_AMENDS B
956 where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
957 );
958
959 update PER_ASS_STATUS_TYPES_AMENDS_TL T set (
960 USER_STATUS
961 ) = (select
962 B.USER_STATUS
963 from PER_ASS_STATUS_TYPES_AMENDS_TL B
964 where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
965 and B.LANGUAGE = T.SOURCE_LANG)
966 where (
967 T.ASS_STATUS_TYPE_AMEND_ID,
968 T.LANGUAGE
969 ) in (select
970 SUBT.ASS_STATUS_TYPE_AMEND_ID,
971 SUBT.LANGUAGE
972 from PER_ASS_STATUS_TYPES_AMENDS_TL SUBB, PER_ASS_STATUS_TYPES_AMENDS_TL SUBT
973 where SUBB.ASS_STATUS_TYPE_AMEND_ID = SUBT.ASS_STATUS_TYPE_AMEND_ID
974 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
975 and (SUBB.USER_STATUS <> SUBT.USER_STATUS
976 ));
977 */
978 insert into PER_ASS_STATUS_TYPE_AMENDS_TL (
979 ASS_STATUS_TYPE_AMEND_ID,
980 USER_STATUS,
981 LAST_UPDATE_DATE,
982 LAST_UPDATED_BY,
983 LAST_UPDATE_LOGIN,
984 CREATED_BY,
985 CREATION_DATE,
986 LANGUAGE,
987 SOURCE_LANG
988 ) select
989 B.ASS_STATUS_TYPE_AMEND_ID,
990 B.USER_STATUS,
991 B.LAST_UPDATE_DATE,
992 B.LAST_UPDATED_BY,
993 B.LAST_UPDATE_LOGIN,
994 B.CREATED_BY,
995 B.CREATION_DATE,
996 L.LANGUAGE_CODE,
997 B.SOURCE_LANG
998 from PER_ASS_STATUS_TYPE_AMENDS_TL B, FND_LANGUAGES L
999 where L.INSTALLED_FLAG in ('I', 'B')
1000 and B.LANGUAGE = userenv('LANG')
1001 and not exists
1002 (select NULL
1003 from PER_ASS_STATUS_TYPE_AMENDS_TL T
1004 where T.ASS_STATUS_TYPE_AMEND_ID = B.ASS_STATUS_TYPE_AMEND_ID
1005 and T.LANGUAGE = L.LANGUAGE_CODE);
1006 --
1007 end ADD_LANGUAGE;
1008 --------------------------------------------------------------------------------
1009 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
1010 p_legislation_code IN VARCHAR2) IS
1011 BEGIN
1012 g_business_group_id := p_business_group_id;
1013 g_legislation_code := p_legislation_code;
1014 END;
1015 --------------------------------------------------------------------------------
1016 --------------------------------------------------------------------------------
1017 procedure validate_translation(assignment_status_type_id IN NUMBER,
1018 language IN VARCHAR2,
1019 user_status IN VARCHAR2,
1020 p_business_group_id IN NUMBER DEFAULT NULL)
1021 IS
1022 /*
1023
1024 This procedure fails if a user status translation is already present in
1025 the table for a given language. Otherwise, no action is performed. It is
1026 used to ensure uniqueness of translated user statuses.
1027
1028 */
1029
1030 --
1031 -- This cursor implements the validation we require,
1032 -- and expects that the various package globals are set before
1033 -- the call to this procedure is made. This is done from the
1034 -- user-named trigger 'TRANSLATIONS' in the form
1035 --
1036 cursor c_translation(p_language IN VARCHAR2,
1037 p_user_status IN VARCHAR2,
1038 p_assignment_status_type_id IN NUMBER,
1039 p_bus_grp_id IN NUMBER)
1040 IS
1041 SELECT 1
1042 FROM per_assignment_status_types_tl astt,
1043 per_assignment_status_types ast
1044 WHERE upper(astt.user_status)=upper(p_user_status)
1045 AND astt.assignment_status_type_id = ast.assignment_status_type_id
1046 AND astt.language = p_language
1047 AND (ast.assignment_status_type_id <> p_assignment_status_type_id
1048 OR p_assignment_status_type_id IS NULL)
1049 AND (ast.business_group_id = p_bus_grp_id OR p_bus_grp_id IS NULL)
1050 ;
1051
1052 l_package_name VARCHAR2(80) := 'PER_ASS_STATUSES_PKG.VALIDATE_TRANSLATION';
1053 l_business_group_id NUMBER := nvl(p_business_group_id, g_business_group_id);
1054
1055 BEGIN
1056 hr_utility.set_location (l_package_name,10);
1057 OPEN c_translation(language, user_status,assignment_status_type_id,
1058 l_business_group_id);
1059 hr_utility.set_location (l_package_name,50);
1060 FETCH c_translation INTO g_dummy;
1061
1062 IF c_translation%NOTFOUND THEN
1063 hr_utility.set_location (l_package_name,60);
1064 CLOSE c_translation;
1065 ELSE
1066 hr_utility.set_location (l_package_name,70);
1067 CLOSE c_translation;
1068 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
1069 fnd_message.raise_error;
1070 END IF;
1071 hr_utility.set_location ('Leaving:'||l_package_name,80);
1072 END validate_translation;
1073 --------------------------------------------------------------------------------
1074
1075 END PER_ASS_STATUSES_PKG;