[Home] [Help]
PACKAGE BODY: APPS.PER_ASS_STATUSES_PKG
Source
1 PACKAGE BODY PER_ASS_STATUSES_PKG AS
2 /* $Header: peast01t.pkb 120.4.12010000.2 2008/08/07 08:54:40 ghshanka 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 insert into per_ass_status_type_amends(
232 ASS_STATUS_TYPE_AMEND_ID,
233 ASSIGNMENT_STATUS_TYPE_ID,
234 BUSINESS_GROUP_ID,
235 ACTIVE_FLAG,
236 DEFAULT_FLAG,
237 USER_STATUS,
238 PAY_SYSTEM_STATUS,
239 PER_SYSTEM_STATUS,
240 LAST_UPDATE_DATE,
241 LAST_UPDATED_BY,
242 LAST_UPDATE_LOGIN,
243 CREATED_BY,
244 CREATION_DATE)
245 values(
246 P_ASS_STATUS_TYPE_AMEND_ID,
247 P_ASSIGNMENT_STATUS_TYPE_ID,
248 P_BUSINESS_GROUP_ID,
249 P_ACTIVE_FLAG,
250 P_DEFAULT_FLAG,
251 P_USER_STATUS,
252 P_PAY_SYSTEM_STATUS,
253 P_PER_SYSTEM_STATUS,
254 P_LAST_UPDATE_DATE,
255 P_LAST_UPDATED_BY,
256 P_LAST_UPDATE_LOGIN,
257 P_CREATED_BY,
258 P_CREATION_DATE);
259 -- MLS
260 insert into per_ass_status_type_amends_tl(
261 ASS_STATUS_TYPE_AMEND_ID,
262 LANGUAGE,
263 SOURCE_LANG,
264 USER_STATUS,
265 LAST_UPDATE_DATE,
266 LAST_UPDATED_BY,
267 LAST_UPDATE_LOGIN,
268 CREATED_BY,
269 CREATION_DATE)
270 select
271 P_ASS_STATUS_TYPE_AMEND_ID,
272 L.LANGUAGE_CODE,
273 B.LANGUAGE_CODE,
274 P_USER_STATUS,
275 P_LAST_UPDATE_DATE,
276 P_LAST_UPDATED_BY,
277 P_LAST_UPDATE_LOGIN,
278 P_CREATED_BY,
279 P_CREATION_DATE
280 from FND_LANGUAGES L, FND_LANGUAGES B
281 where L.INSTALLED_FLAG in ('I', 'B')
282 and B.INSTALLED_FLAG = 'B';
283 -- MLS end
284 END INSERT_AMENDS;
285
286 --
287 -- For a given business group / legislation combination there must be one and
288 -- only one active default assignment status for each personnel system status.
289 -- The user can define many assignment statuses based on a system status but
290 -- only one can be the actve default at any one time.
291 --
292 procedure chk_dflt_per_sys_statuses
293 (
294 p_business_group_id number,
295 p_legislation_code varchar2
296 ) is
297 --
298 type varchar2_table is table of varchar2(30) index by binary_integer;
299 --
300 cursor csr1 is
301 select lookup_code status
302 from hr_lookups
303 where lookup_type = 'PER_ASS_SYS_STATUS'
304 order by lookup_code;
305 --
306 cursor csr2 is
307 select t.per_system_status status
308 from per_assignment_status_types t
309 where t.per_system_status is not null
310 and t.default_flag = 'Y'
311 and t.active_flag = 'Y'
312 and nvl(t.business_group_id, nvl(p_business_group_id, -9999) ) =
313 nvl(p_business_group_id, -9999)
314 and nvl(t.legislation_code, nvl(p_legislation_code, 'XXX') ) =
315 nvl(p_legislation_code, 'XXX')
316 and not exists (select null
317 from per_ass_status_type_amends a
318 where a.assignment_status_type_id =
319 t.assignment_status_type_id
320 and a.business_group_id =
321 p_business_group_id)
322 union all
323 select a.per_system_status status
324 from per_ass_status_type_amends a
325 where a.per_system_status is not null
326 and a.default_flag = 'Y'
327 and a.active_flag = 'Y'
328 and a.business_group_id = p_business_group_id
329 order by 1;
330 --
331 system_statuses varchar2_table;
332 system_status_count number := 0;
333 user_statuses varchar2_table;
334 user_status_count number := 0;
335 --
336 begin
337 --
338 --
339 -- Populate a list with the personnel system statuses.
340 --
341 for system_rec in csr1 loop
342 system_status_count := system_status_count + 1;
343 system_statuses(system_status_count) := system_rec.status;
344 end loop;
345 --
346 --
347 -- Populate a list with the active default assignment statuses as
348 -- defined by the user.
349 --
350 for user_rec in csr2 loop
351 user_status_count := user_status_count + 1;
352 user_statuses(user_status_count) := user_rec.status;
353 end loop;
354 --
355 --
356 -- Make sure that the number of personnel system statuses is matched by the
357 -- number of active default assignment statuses.
358 --
359 if system_status_count <> user_status_count then
360 hr_utility.set_message(801, 'HR_7214_ASS_STAT_ONE_ONLY_ONE');
361 hr_utility.raise_error;
362 end if;
363 --
364 --
365 -- Compare the list of personnel system statuses with the user defined
366 -- active default assignment statuses. There should be a one to one match
367 -- which signals that each personnel system status is used only once as the
368 -- active default.
369 --
370 for i in 1..system_status_count loop
371 if system_statuses(i) <> user_statuses(i) then
372 hr_utility.set_message(801, 'HR_7214_ASS_STAT_ONE_ONLY_ONE');
373 hr_utility.raise_error;
374 end if;
375 end loop;
376 --
377 end chk_dflt_per_sys_statuses;
378 --
379 procedure INSERT_ROW (
380 X_ROWID in out nocopy VARCHAR2,
381 X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER,
382 X_BUSINESS_GROUP_ID in NUMBER,
383 X_LEGISLATION_CODE in VARCHAR2,
384 X_ACTIVE_FLAG in VARCHAR2,
385 X_DEFAULT_FLAG in VARCHAR2,
386 X_PRIMARY_FLAG in VARCHAR2,
387 X_PAY_SYSTEM_STATUS in VARCHAR2,
388 X_PER_SYSTEM_STATUS in VARCHAR2,
389 X_USER_STATUS in VARCHAR2,
390 X_EXTERNAL_STATUS in VARCHAR2,
391 X_CREATION_DATE in DATE,
392 X_CREATED_BY in NUMBER,
393 X_LAST_UPDATE_DATE in DATE,
394 X_LAST_UPDATED_BY in NUMBER,
395 X_LAST_UPDATE_LOGIN in NUMBER
396 ) is
397 cursor C is select ROWID from PER_ASSIGNMENT_STATUS_TYPES
398 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
399 begin
400 insert into PER_ASSIGNMENT_STATUS_TYPES (
401 ASSIGNMENT_STATUS_TYPE_ID,
402 BUSINESS_GROUP_ID,
403 LEGISLATION_CODE,
407 PRIMARY_FLAG,
404 USER_STATUS,
405 ACTIVE_FLAG,
406 DEFAULT_FLAG,
408 PAY_SYSTEM_STATUS,
409 PER_SYSTEM_STATUS,
410 CREATION_DATE,
411 CREATED_BY,
412 LAST_UPDATE_DATE,
413 LAST_UPDATED_BY,
414 LAST_UPDATE_LOGIN
415 ) values (
416 X_ASSIGNMENT_STATUS_TYPE_ID,
417 X_BUSINESS_GROUP_ID,
418 X_LEGISLATION_CODE,
419 X_USER_STATUS,
420 X_ACTIVE_FLAG,
421 X_DEFAULT_FLAG,
422 X_PRIMARY_FLAG,
423 X_PAY_SYSTEM_STATUS,
424 X_PER_SYSTEM_STATUS,
425 X_CREATION_DATE,
426 X_CREATED_BY,
427 X_LAST_UPDATE_DATE,
428 X_LAST_UPDATED_BY,
429 X_LAST_UPDATE_LOGIN
430 );
431
432 insert into PER_ASSIGNMENT_STATUS_TYPES_TL (
433 ASSIGNMENT_STATUS_TYPE_ID,
434 USER_STATUS,
435 EXTERNAL_STATUS,
436 LAST_UPDATE_DATE,
437 LAST_UPDATED_BY,
438 LAST_UPDATE_LOGIN,
439 CREATED_BY,
440 CREATION_DATE,
441 LANGUAGE,
442 SOURCE_LANG
443 ) select
444 X_ASSIGNMENT_STATUS_TYPE_ID,
445 X_USER_STATUS,
446 X_EXTERNAL_STATUS,
447 X_LAST_UPDATE_DATE,
448 X_LAST_UPDATED_BY,
449 X_LAST_UPDATE_LOGIN,
450 X_CREATED_BY,
451 X_CREATION_DATE,
452 L.LANGUAGE_CODE,
453 userenv('LANG')
454 from FND_LANGUAGES L
455 where L.INSTALLED_FLAG in ('I', 'B')
456 and not exists
457 (select NULL
458 from PER_ASSIGNMENT_STATUS_TYPES_TL T
459 where T.ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
460 and T.LANGUAGE = L.LANGUAGE_CODE);
461
462 open c;
463 fetch c into X_ROWID;
464 if (c%notfound) then
465 close c;
466 raise no_data_found;
467 end if;
468 close c;
469
470 end INSERT_ROW;
471
472 procedure LOCK_ROW (
473 X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER,
474 X_BUSINESS_GROUP_ID in NUMBER,
475 X_LEGISLATION_CODE in VARCHAR2,
476 X_ACTIVE_FLAG in VARCHAR2,
477 X_DEFAULT_FLAG in VARCHAR2,
478 X_PRIMARY_FLAG in VARCHAR2,
479 X_PAY_SYSTEM_STATUS in VARCHAR2,
480 X_PER_SYSTEM_STATUS in VARCHAR2,
481 X_USER_STATUS in VARCHAR2
482 ) is
483 cursor c is select
484 BUSINESS_GROUP_ID,
485 LEGISLATION_CODE,
486 ACTIVE_FLAG,
487 DEFAULT_FLAG,
488 PRIMARY_FLAG,
489 PAY_SYSTEM_STATUS,
490 PER_SYSTEM_STATUS
491 from PER_ASSIGNMENT_STATUS_TYPES
492 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
493 for update of ASSIGNMENT_STATUS_TYPE_ID nowait;
494 recinfo c%rowtype;
495
496 cursor c1 is select
497 USER_STATUS,
498 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
499 from PER_ASSIGNMENT_STATUS_TYPES_TL
500 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
501 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
502 for update of ASSIGNMENT_STATUS_TYPE_ID nowait;
503 begin
504 open c;
505 fetch c into recinfo;
506 if (c%notfound) then
507 close c;
508 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
509 app_exception.raise_exception;
510 end if;
511 close c;
512 if ( ((recinfo.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
513 OR ((recinfo.BUSINESS_GROUP_ID is null) AND (X_BUSINESS_GROUP_ID is null)))
514 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
515 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
516 AND (recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
517 AND (recinfo.DEFAULT_FLAG = X_DEFAULT_FLAG)
518 AND (recinfo.PRIMARY_FLAG = X_PRIMARY_FLAG)
519 AND ((recinfo.PAY_SYSTEM_STATUS = X_PAY_SYSTEM_STATUS)
520 OR ((recinfo.PAY_SYSTEM_STATUS is null) AND (X_PAY_SYSTEM_STATUS is null)))
521 AND ((recinfo.PER_SYSTEM_STATUS = X_PER_SYSTEM_STATUS)
522 OR ((recinfo.PER_SYSTEM_STATUS is null) AND (X_PER_SYSTEM_STATUS is null)))
523 ) then
524 null;
525 else
526 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
527 app_exception.raise_exception;
528 end if;
529
530 for tlinfo in c1 loop
531 if (tlinfo.BASELANG = 'Y') then
532 if ( (tlinfo.USER_STATUS = X_USER_STATUS)
533 ) then
534 null;
535 else
536 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
537 app_exception.raise_exception;
538 end if;
539 end if;
540 end loop;
541 return;
542 end LOCK_ROW;
543
544 procedure UPDATE_ROW (
545 X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER,
546 X_BUSINESS_GROUP_ID in NUMBER,
547 X_LEGISLATION_CODE in VARCHAR2,
548 X_ACTIVE_FLAG in VARCHAR2,
549 X_DEFAULT_FLAG in VARCHAR2,
550 X_PRIMARY_FLAG in VARCHAR2,
551 X_PAY_SYSTEM_STATUS in VARCHAR2,
552 X_PER_SYSTEM_STATUS in VARCHAR2,
553 X_USER_STATUS in VARCHAR2,
554 X_EXTERNAL_STATUS in VARCHAR2,
555 X_LAST_UPDATE_DATE in DATE,
556 X_LAST_UPDATED_BY in NUMBER,
557 X_LAST_UPDATE_LOGIN in NUMBER
558 ) is
559 begin
560 -- start of bug 5411889
564 if X_BUSINESS_GROUP_ID is not null then
561 -- added an if condition so that the seeded record status
562 -- is not changed when updating the record.
563
565
566 update PER_ASSIGNMENT_STATUS_TYPES set
567 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
568 LEGISLATION_CODE = X_LEGISLATION_CODE,
569 USER_STATUS = X_USER_STATUS, -- Bug 2731841
570 ACTIVE_FLAG = X_ACTIVE_FLAG,
571 DEFAULT_FLAG = X_DEFAULT_FLAG,
572 PRIMARY_FLAG = X_PRIMARY_FLAG,
573 PAY_SYSTEM_STATUS = X_PAY_SYSTEM_STATUS,
574 PER_SYSTEM_STATUS = X_PER_SYSTEM_STATUS,
575 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
576 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
577 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
578 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
579
580 else
581
582 update PER_ASSIGNMENT_STATUS_TYPES set
583 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
584 LEGISLATION_CODE = X_LEGISLATION_CODE,
585 -- USER_STATUS = X_USER_STATUS, -- Bug 2731841
586 ACTIVE_FLAG = X_ACTIVE_FLAG,
587 DEFAULT_FLAG = X_DEFAULT_FLAG,
588 PRIMARY_FLAG = X_PRIMARY_FLAG,
589 PAY_SYSTEM_STATUS = X_PAY_SYSTEM_STATUS,
590 PER_SYSTEM_STATUS = X_PER_SYSTEM_STATUS,
591 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
592 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
593 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
594 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
595
596 end if;
597 -- end of bug 5411889
598
599 if (sql%notfound) then
600 raise no_data_found;
601 end if;
602
603 update PER_ASSIGNMENT_STATUS_TYPES_TL set
604 USER_STATUS = X_USER_STATUS,
605 EXTERNAL_STATUS = X_EXTERNAL_STATUS,
606 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
607 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
608 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
609 SOURCE_LANG = userenv('LANG')
610 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
611 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
612
613 if (sql%notfound) then
614 raise no_data_found;
615 end if;
616 end UPDATE_ROW;
617
618 procedure DELETE_ROW (
619 X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER
620 ) is
621 begin
622 delete from PER_ASSIGNMENT_STATUS_TYPES_TL
623 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
624
625 if (sql%notfound) then
626 raise no_data_found;
627 end if;
628
629 delete from PER_ASSIGNMENT_STATUS_TYPES
630 where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
631
632 if (sql%notfound) then
633 raise no_data_found;
634 end if;
635 end DELETE_ROW;
636 --
637 procedure LOAD_ROW (
638 X_STATUS in VARCHAR2,
639 X_BUSINESS_GROUP_NAME in VARCHAR2,
640 X_LEGISLATION_CODE in VARCHAR2,
641 X_ACTIVE_FLAG in VARCHAR2,
642 X_DEFAULT_FLAG in VARCHAR2,
643 X_PRIMARY_FLAG in VARCHAR2,
644 X_PAY_SYSTEM_STATUS in VARCHAR2,
645 X_PER_SYSTEM_STATUS in VARCHAR2,
646 X_USER_STATUS in VARCHAR2,
647 X_OWNER in VARCHAR2,
648 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
649 X_CUSTOM_MODE IN VARCHAR2 default null
650 )
651 is
652 X_ROWID ROWID;
653 X_ASSIGNMENT_STATUS_TYPE_ID NUMBER;
654 X_BUSINESS_GROUP_ID NUMBER;
655 X_CREATION_DATE DATE :=sysdate;
656 X_CREATED_BY NUMBER;
657 -- X_LAST_UPDATE_DATE DATE;
658 X_LAST_UPDATED_BY NUMBER;
659 X_LAST_UPDATE_LOGIN NUMBER;
660 f_luby number; -- entity owner in file
661 f_ludate date; -- entity update date in file
662 db_luby number; -- entity owner in db
663 db_ludate date; -- entity update date in db
664 begin
665 if X_OWNER = 'SEED' then
666 X_CREATED_BY := 1;
667 else
668 X_CREATED_BY := 0;
669 end if;
670
671 KEY_TO_IDS (
672 X_STATUS,
673 X_BUSINESS_GROUP_NAME,
674 X_LEGISLATION_CODE,
675 X_ASSIGNMENT_STATUS_TYPE_ID,
676 X_BUSINESS_GROUP_ID
677 );
678 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
679 /*
680 OWNER_TO_WHO (
681 X_OWNER,
682 X_CREATION_DATE,
683 X_CREATED_BY,
684 X_LAST_UPDATE_DATE,
685 X_LAST_UPDATED_BY,
686 X_LAST_UPDATE_LOGIN
687 );*/
688
689 begin
690 f_luby := fnd_load_util.owner_id(X_OWNER);
691 -- Translate char last_update_date to date
692 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
693 select LAST_UPDATED_BY, LAST_UPDATE_DATE
694 into db_luby, db_ludate
695 from PER_ASSIGNMENT_STATUS_TYPES
696 where ASSIGNMENT_STATUS_TYPE_ID = TO_NUMBER(X_ASSIGNMENT_STATUS_TYPE_ID);
697
698 -- Test for customization and version
699 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
700 db_ludate, X_CUSTOM_MODE)) then
701 UPDATE_ROW (
702 X_ASSIGNMENT_STATUS_TYPE_ID,
703 X_BUSINESS_GROUP_ID,
704 X_LEGISLATION_CODE,
705 X_ACTIVE_FLAG,
706 X_DEFAULT_FLAG,
707 X_PRIMARY_FLAG,
708 X_PAY_SYSTEM_STATUS,
709 X_PER_SYSTEM_STATUS,
710 X_USER_STATUS,
711 X_USER_STATUS,
712 f_ludate,
713 f_luby,
717 when no_data_found then
714 0);
715 end if;
716 exception
718 INSERT_ROW (
719 X_ROWID,
720 X_ASSIGNMENT_STATUS_TYPE_ID,
721 X_BUSINESS_GROUP_ID,
722 X_LEGISLATION_CODE,
723 X_ACTIVE_FLAG,
724 X_DEFAULT_FLAG,
725 X_PRIMARY_FLAG,
726 X_PAY_SYSTEM_STATUS,
727 X_PER_SYSTEM_STATUS,
728 X_USER_STATUS,
729 X_USER_STATUS,
730 X_CREATION_DATE,
731 X_CREATED_BY,
732 f_ludate,
733 f_luby,
734 0);
735 end;
736 end LOAD_ROW;
737 --
738 procedure TRANSLATE_ROW (
739 X_STATUS in VARCHAR2,
740 X_BUSINESS_GROUP_NAME in VARCHAR2,
741 X_LEGISLATION_CODE in VARCHAR2,
742 X_USER_STATUS in VARCHAR2,
743 X_OWNER in VARCHAR2,
744 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
745 X_CUSTOM_MODE IN VARCHAR2 default null
746 )
747 is
748 X_ASSIGNMENT_STATUS_TYPE_ID NUMBER;
749 X_BUSINESS_GROUP_ID NUMBER;
750 X_CREATION_DATE DATE;
751 X_CREATED_BY NUMBER;
752 -- X_LAST_UPDATE_DATE DATE;
753 X_LAST_UPDATED_BY NUMBER;
754 X_LAST_UPDATE_LOGIN NUMBER;
755 f_luby number; -- entity owner in file
756 f_ludate date; -- entity update date in file
757 db_luby number; -- entity owner in db
758 db_ludate date; -- entity update date in db
759 begin
760 KEY_TO_IDS (
761 X_STATUS,
762 X_BUSINESS_GROUP_NAME,
763 X_LEGISLATION_CODE,
764 X_ASSIGNMENT_STATUS_TYPE_ID,
765 X_BUSINESS_GROUP_ID
766 );
767 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
768 /*
769 OWNER_TO_WHO (
770 X_OWNER,
771 X_CREATION_DATE,
772 X_CREATED_BY,
773 X_LAST_UPDATE_DATE,
774 X_LAST_UPDATED_BY,
775 X_LAST_UPDATE_LOGIN
776 );*/
777
778 -- Translate owner to file_last_updated_by
779 f_luby := fnd_load_util.owner_id(x_owner);
780
781 -- Translate char last_update_date to date
782 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
783 select LAST_UPDATED_BY, LAST_UPDATE_DATE
784 into db_luby, db_ludate
785 from PER_ASSIGNMENT_STATUS_TYPES_TL
786 where ASSIGNMENT_STATUS_TYPE_ID = TO_NUMBER(X_ASSIGNMENT_STATUS_TYPE_ID)
787 and LANGUAGE=userenv('LANG');
788
789 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
790 db_ludate,X_CUSTOM_MODE)) then
791
792 update PER_ASSIGNMENT_STATUS_TYPES_TL set
793 USER_STATUS = X_USER_STATUS,
794 EXTERNAL_STATUS = X_USER_STATUS, -- Bug fix 3627126.
795 LAST_UPDATE_DATE = db_ludate,
796 LAST_UPDATED_BY = db_luby,
797 LAST_UPDATE_LOGIN = 0,
798 SOURCE_LANG = userenv('LANG')
799 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
800 and ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;
801 end if;
802 end TRANSLATE_ROW;
803 --
804 procedure ADD_LANGUAGE
805 is
806 begin
807 -- process PER_ASSIGNMENT_STATUS_TYPES_TL table
808 delete from PER_ASSIGNMENT_STATUS_TYPES_TL T
809 where not exists
810 (select NULL
811 from PER_ASSIGNMENT_STATUS_TYPES B
812 where B.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
813 );
814
815 update PER_ASSIGNMENT_STATUS_TYPES_TL T set (
816 USER_STATUS
817 ) = (select
818 B.USER_STATUS
819 from PER_ASSIGNMENT_STATUS_TYPES_TL B
820 where B.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
821 and B.LANGUAGE = T.SOURCE_LANG)
822 where (
823 T.ASSIGNMENT_STATUS_TYPE_ID,
824 T.LANGUAGE
825 ) in (select
826 SUBT.ASSIGNMENT_STATUS_TYPE_ID,
827 SUBT.LANGUAGE
828 from PER_ASSIGNMENT_STATUS_TYPES_TL SUBB, PER_ASSIGNMENT_STATUS_TYPES_TL SUBT
829 where SUBB.ASSIGNMENT_STATUS_TYPE_ID = SUBT.ASSIGNMENT_STATUS_TYPE_ID
830 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
831 and (SUBB.USER_STATUS <> SUBT.USER_STATUS
832 ));
833
834 insert into PER_ASSIGNMENT_STATUS_TYPES_TL (
835 ASSIGNMENT_STATUS_TYPE_ID,
836 USER_STATUS,
837 LAST_UPDATE_DATE,
838 LAST_UPDATED_BY,
839 LAST_UPDATE_LOGIN,
840 CREATED_BY,
841 CREATION_DATE,
842 LANGUAGE,
843 SOURCE_LANG
844 ) select /*+ INDEX(b)*/
845 B.ASSIGNMENT_STATUS_TYPE_ID,
846 B.USER_STATUS,
847 B.LAST_UPDATE_DATE,
848 B.LAST_UPDATED_BY,
849 B.LAST_UPDATE_LOGIN,
850 B.CREATED_BY,
851 B.CREATION_DATE,
852 L.LANGUAGE_CODE,
853 B.SOURCE_LANG
854 from PER_ASSIGNMENT_STATUS_TYPES_TL B, FND_LANGUAGES L
855 where L.INSTALLED_FLAG in ('I', 'B')
856 and B.LANGUAGE = userenv('LANG')
857 and not exists
858 (select NULL
859 from PER_ASSIGNMENT_STATUS_TYPES_TL T
860 where T.ASSIGNMENT_STATUS_TYPE_ID = B.ASSIGNMENT_STATUS_TYPE_ID
861 and T.LANGUAGE = L.LANGUAGE_CODE);
862 --
863 -- process PER_ASS_STATUS_TYPES_AMENDS_TL table
864 /*
865 delete from PER_ASS_STATUS_TYPES_AMENDS_TL T
866 where not exists
867 (select NULL
868 from PER_ASS_STATUS_TYPES_AMENDS B
869 where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
870 );
871
875 B.USER_STATUS
872 update PER_ASS_STATUS_TYPES_AMENDS_TL T set (
873 USER_STATUS
874 ) = (select
876 from PER_ASS_STATUS_TYPES_AMENDS_TL B
877 where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
878 and B.LANGUAGE = T.SOURCE_LANG)
879 where (
880 T.ASS_STATUS_TYPE_AMEND_ID,
881 T.LANGUAGE
882 ) in (select
883 SUBT.ASS_STATUS_TYPE_AMEND_ID,
884 SUBT.LANGUAGE
885 from PER_ASS_STATUS_TYPES_AMENDS_TL SUBB, PER_ASS_STATUS_TYPES_AMENDS_TL SUBT
886 where SUBB.ASS_STATUS_TYPE_AMEND_ID = SUBT.ASS_STATUS_TYPE_AMEND_ID
887 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
888 and (SUBB.USER_STATUS <> SUBT.USER_STATUS
889 ));
890 */
891 insert into PER_ASS_STATUS_TYPE_AMENDS_TL (
892 ASS_STATUS_TYPE_AMEND_ID,
893 USER_STATUS,
894 LAST_UPDATE_DATE,
895 LAST_UPDATED_BY,
896 LAST_UPDATE_LOGIN,
897 CREATED_BY,
898 CREATION_DATE,
899 LANGUAGE,
900 SOURCE_LANG
901 ) select
902 B.ASS_STATUS_TYPE_AMEND_ID,
903 B.USER_STATUS,
904 B.LAST_UPDATE_DATE,
905 B.LAST_UPDATED_BY,
906 B.LAST_UPDATE_LOGIN,
907 B.CREATED_BY,
908 B.CREATION_DATE,
909 L.LANGUAGE_CODE,
910 B.SOURCE_LANG
911 from PER_ASS_STATUS_TYPE_AMENDS_TL B, FND_LANGUAGES L
912 where L.INSTALLED_FLAG in ('I', 'B')
913 and B.LANGUAGE = userenv('LANG')
914 and not exists
915 (select NULL
916 from PER_ASS_STATUS_TYPE_AMENDS_TL T
917 where T.ASS_STATUS_TYPE_AMEND_ID = B.ASS_STATUS_TYPE_AMEND_ID
918 and T.LANGUAGE = L.LANGUAGE_CODE);
919 --
920 end ADD_LANGUAGE;
921 --------------------------------------------------------------------------------
922 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
923 p_legislation_code IN VARCHAR2) IS
924 BEGIN
925 g_business_group_id := p_business_group_id;
926 g_legislation_code := p_legislation_code;
927 END;
928 --------------------------------------------------------------------------------
929 --------------------------------------------------------------------------------
930 procedure validate_translation(assignment_status_type_id IN NUMBER,
931 language IN VARCHAR2,
932 user_status IN VARCHAR2,
933 p_business_group_id IN NUMBER DEFAULT NULL)
934 IS
935 /*
936
937 This procedure fails if a user status translation is already present in
938 the table for a given language. Otherwise, no action is performed. It is
939 used to ensure uniqueness of translated user statuses.
940
941 */
942
943 --
944 -- This cursor implements the validation we require,
945 -- and expects that the various package globals are set before
946 -- the call to this procedure is made. This is done from the
947 -- user-named trigger 'TRANSLATIONS' in the form
948 --
949 cursor c_translation(p_language IN VARCHAR2,
950 p_user_status IN VARCHAR2,
951 p_assignment_status_type_id IN NUMBER,
952 p_bus_grp_id IN NUMBER)
953 IS
954 SELECT 1
955 FROM per_assignment_status_types_tl astt,
956 per_assignment_status_types ast
957 WHERE upper(astt.user_status)=upper(p_user_status)
958 AND astt.assignment_status_type_id = ast.assignment_status_type_id
959 AND astt.language = p_language
960 AND (ast.assignment_status_type_id <> p_assignment_status_type_id
961 OR p_assignment_status_type_id IS NULL)
962 AND (ast.business_group_id = p_bus_grp_id OR p_bus_grp_id IS NULL)
963 ;
964
965 l_package_name VARCHAR2(80) := 'PER_ASS_STATUSES_PKG.VALIDATE_TRANSLATION';
966 l_business_group_id NUMBER := nvl(p_business_group_id, g_business_group_id);
967
968 BEGIN
969 hr_utility.set_location (l_package_name,10);
970 OPEN c_translation(language, user_status,assignment_status_type_id,
971 l_business_group_id);
972 hr_utility.set_location (l_package_name,50);
973 FETCH c_translation INTO g_dummy;
974
975 IF c_translation%NOTFOUND THEN
976 hr_utility.set_location (l_package_name,60);
977 CLOSE c_translation;
978 ELSE
979 hr_utility.set_location (l_package_name,70);
980 CLOSE c_translation;
981 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
982 fnd_message.raise_error;
983 END IF;
984 hr_utility.set_location ('Leaving:'||l_package_name,80);
985 END validate_translation;
986 --------------------------------------------------------------------------------
987
988 END PER_ASS_STATUSES_PKG;