[Home] [Help]
PACKAGE BODY: APPS.PER_TIME_PERIOD_TYPES_PKG
Source
1 PACKAGE BODY PER_TIME_PERIOD_TYPES_PKG as
2 /* $Header: pydpt01t.pkb 120.0.12010000.1 2008/07/27 22:27:58 appldev ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 procedure PERIOD_TYPE_NOT_UNIQUE (
7 --
8 -- Returns TRUE if the period type name is not unique, then the check is for a
9 -- new record within generic data
10 -- Parameters are:
11 --
12 p_period_type in varchar2 ,
13 p_row_id in varchar2 ) is
14 --
15 v_not_unique boolean := FALSE;
16 g_dummy_number number;
17 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.period_type_not_unique';
18
19 --
20 cursor csr_duplicate is
21 select null
22 from per_time_period_types ptpt
23 where upper(p_period_type) = upper(ptpt.period_type)
24 and (p_row_id is null
25 or (p_row_id is not null
26 and chartorowid(p_row_id) <> ptpt.rowid));
27 begin
28
29 if g_debug then
30 hr_utility.set_location( 'Entering : ' || l_proc , 1);
31 end if;
32
33 --
34 open csr_duplicate;
35 fetch csr_duplicate into g_dummy_number;
36 v_not_unique := csr_duplicate%found;
37 close csr_duplicate;
38 --
39 if v_not_unique then
40 hr_utility.set_message (801,'HR_7663_DEF_TIME_PERIOD_EXISTS');
41 hr_utility.raise_error;
42 end if;
43 --
44
45 if g_debug then
46 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
47 end if;
48
49 end period_type_not_unique;
50
51 --
52
53 procedure check_delete_period_type (
54 p_period_type in varchar2,
55 p_number_per_fiscal_year in number) is
56 --
57 g_dummy_number number;
58 v_no_delete boolean := FALSE;
59 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.delete_period_type';
60
61 --
62 cursor csr_calendar is
63 select null
64 from pay_calendars
65 where upper(p_period_type) = upper(actual_period_type);
66 cursor csr_periods is
67 select null
68 from per_time_periods
69 where upper(p_period_type) = upper(period_type);
70 cursor csr_cobra is
71 select null
72 from per_cobra_cov_enrollments
73 where upper(p_period_type) = upper(period_type);
74 cursor csr_year is
75 select null
76 from per_time_period_types
77 where number_per_fiscal_year = p_number_per_fiscal_year
78 and exists
79 (select null
80 from per_time_period_types
81 where number_per_fiscal_year = p_number_per_fiscal_year
82 and number_per_fiscal_year = 1
83 having count(*) = 1);
84 cursor csr_quarter is
85 select null
86 from per_time_period_types
87 where number_per_fiscal_year = p_number_per_fiscal_year
88 and exists
89 (select null
90 from per_time_period_types
91 where number_per_fiscal_year = p_number_per_fiscal_year
92 and number_per_fiscal_year = 4
93 having count(*) = 1);
94 --
95 --
96 -- Check there are no dependencies of the period type record
97 -- in the PAY_CALENDARS, PER_TIME_PERIOD_SETS, PER_COBRA_COV_ENROLLMENTS tables
98 -- and there is at least one record with fiscal year of 1 and 4
99 --
100 begin
101
102 if g_debug then
103 hr_utility.set_location( 'Entering : ' || l_proc , 1);
104 end if;
105
106 open csr_calendar;
107 fetch csr_calendar into g_dummy_number;
108 v_no_delete := csr_calendar%found;
109 close csr_calendar;
110 --
111 if v_no_delete then
112 hr_utility.set_message (801,'HR_7660_DEF_DELETE_PERIODS');
113 hr_utility.raise_error;
114 end if;
115 --
116 open csr_periods;
117 fetch csr_periods into g_dummy_number;
118 v_no_delete := csr_periods%found;
119 close csr_periods;
120 --
121 if v_no_delete then
122 hr_utility.set_message (801,'HR_6058_TIME_DELETE_PERIOD');
123 hr_utility.raise_error;
124 end if;
125 --
126 open csr_cobra;
127 fetch csr_cobra into g_dummy_number;
128 v_no_delete := csr_cobra%found;
129 close csr_cobra;
130 --
131 if v_no_delete then
132 hr_utility.set_message (801,'HR_6974_TIME_DELETE_COBRA');
133 hr_utility.raise_error;
134 end if;
135 --
136 open csr_year;
137 fetch csr_year into g_dummy_number;
138 v_no_delete := csr_year%found;
139 close csr_year;
140 --
141 if v_no_delete then
142 hr_utility.set_message (801,'HR_7662_DEF_DELETE_YEAR_OR_QTR');
143 fnd_message.set_token('PERIOD_TYPE','Year');
144 hr_utility.raise_error;
145 end if;
146 --
147 open csr_quarter;
148 fetch csr_quarter into g_dummy_number;
149 v_no_delete := csr_quarter%found;
150 close csr_quarter;
151 --
152 if v_no_delete then
153 hr_utility.set_message (801,'HR_7662_DEF_DELETE_YEAR_OR_QTR');
154 fnd_message.set_token('PERIOD_TYPE','Quarter');
155 hr_utility.raise_error;
156 end if;
157 --
158
159 if g_debug then
160 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
161 end if;
162
163 end check_delete_period_type;
164 --
165
166 -- Returns TRUE if the display period type is not unique for a particular language.
167
168 procedure DISPLAY_PERIOD_TYPE_NOT_UNIQUE (
169 x_period_type in varchar2,
170 x_display_period_type in varchar2,
171 x_language in varchar2 ) is
172 --
173 v_not_unique boolean := FALSE;
174 g_dummy_number number;
175 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.display_period_type_not_unique';
176
177 --
178 cursor csr_duplicate is
179 SELECT 1
180 FROM per_time_period_types_tl ptptl
181 WHERE upper(ptptl.display_period_type) = upper(x_display_period_type)
182 AND ptptl.language = x_language
183 AND ( x_period_type is null or ( x_period_type is not null
184 and ptptl.period_type <> x_period_type ) );
185 begin
186
187 if g_debug then
188 hr_utility.set_location( 'Entering : ' || l_proc , 1);
189 end if;
190
191 --
192 open csr_duplicate;
193 fetch csr_duplicate into g_dummy_number;
194 v_not_unique := csr_duplicate%found;
195 close csr_duplicate;
196 --
197 if v_not_unique then
198 hr_utility.set_message (801,'HR_7663_DEF_TIME_PERIOD_EXISTS');
199 hr_utility.raise_error;
200 end if;
201 --
202
203 if g_debug then
204 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
205 end if;
206
207 end display_period_type_not_unique;
208
209 procedure INSERT_ROW (
210 X_ROWID in out nocopy VARCHAR2,
211 X_PERIOD_TYPE in VARCHAR2,
212 X_NUMBER_PER_FISCAL_YEAR in NUMBER,
213 X_YEAR_TYPE_IN_NAME in VARCHAR2,
214 X_SYSTEM_FLAG in VARCHAR2,
215 X_DESCRIPTION in VARCHAR2,
216 X_DISPLAY_PERIOD_TYPE in VARCHAR2,
217 X_ATTRIBUTE_CATEGORY in VARCHAR2,
218 X_ATTRIBUTE1 in VARCHAR2,
219 X_ATTRIBUTE2 in VARCHAR2,
220 X_ATTRIBUTE3 in VARCHAR2,
221 X_ATTRIBUTE4 in VARCHAR2,
222 X_ATTRIBUTE5 in VARCHAR2,
223 X_ATTRIBUTE6 in VARCHAR2,
224 X_ATTRIBUTE7 in VARCHAR2,
225 X_ATTRIBUTE8 in VARCHAR2,
226 X_ATTRIBUTE9 in VARCHAR2,
227 X_ATTRIBUTE10 in VARCHAR2,
228 X_ATTRIBUTE11 in VARCHAR2,
229 X_ATTRIBUTE12 in VARCHAR2,
230 X_ATTRIBUTE13 in VARCHAR2,
231 X_ATTRIBUTE14 in VARCHAR2,
232 X_ATTRIBUTE15 in VARCHAR2,
233 X_ATTRIBUTE16 in VARCHAR2,
234 X_ATTRIBUTE17 in VARCHAR2,
235 X_ATTRIBUTE18 in VARCHAR2,
236 X_ATTRIBUTE19 in VARCHAR2,
237 X_ATTRIBUTE20 in VARCHAR2,
238 X_REQUEST_ID in NUMBER,
239 X_PROGRAM_APPLICATION_ID in NUMBER,
240 X_PROGRAM_ID in NUMBER,
241 X_PROGRAM_UPDATE_DATE in DATE,
242 X_CREATION_DATE in DATE,
243 X_CREATED_BY in NUMBER,
244 X_LAST_UPDATE_DATE in DATE,
245 X_LAST_UPDATED_BY in NUMBER,
246 X_LAST_UPDATE_LOGIN in NUMBER
247 ) is
248 cursor C is select ROWID from PER_TIME_PERIOD_TYPES
249 where PERIOD_TYPE = X_PERIOD_TYPE;
250
251 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.insert_row';
252
253 begin
254
255 if g_debug then
256 hr_utility.set_location( 'Entering : ' || l_proc , 1);
257 end if;
258
259
260 display_period_type_not_unique( X_PERIOD_TYPE,
261 X_DISPLAY_PERIOD_TYPE,
262 userenv('LANG') );
263
264 insert into PER_TIME_PERIOD_TYPES (
265 PERIOD_TYPE,
266 NUMBER_PER_FISCAL_YEAR,
267 YEAR_TYPE_IN_NAME,
268 SYSTEM_FLAG,
269 DESCRIPTION,
270 DISPLAY_PERIOD_TYPE,
271 ATTRIBUTE_CATEGORY,
272 ATTRIBUTE1,
273 ATTRIBUTE2,
274 ATTRIBUTE3,
275 ATTRIBUTE4,
276 ATTRIBUTE5,
277 ATTRIBUTE6,
278 ATTRIBUTE7,
279 ATTRIBUTE8,
280 ATTRIBUTE9,
281 ATTRIBUTE10,
282 ATTRIBUTE11,
283 ATTRIBUTE12,
284 ATTRIBUTE13,
285 ATTRIBUTE14,
286 ATTRIBUTE15,
287 ATTRIBUTE16,
288 ATTRIBUTE17,
289 ATTRIBUTE18,
290 ATTRIBUTE19,
291 ATTRIBUTE20,
292 REQUEST_ID,
293 PROGRAM_APPLICATION_ID,
294 PROGRAM_ID,
295 PROGRAM_UPDATE_DATE,
296 CREATION_DATE,
297 CREATED_BY,
298 LAST_UPDATE_DATE,
299 LAST_UPDATED_BY,
300 LAST_UPDATE_LOGIN
301 ) values (
302 X_PERIOD_TYPE,
303 X_NUMBER_PER_FISCAL_YEAR,
304 X_YEAR_TYPE_IN_NAME,
305 X_SYSTEM_FLAG,
306 X_DESCRIPTION,
307 X_DISPLAY_PERIOD_TYPE,
308 X_ATTRIBUTE_CATEGORY,
309 X_ATTRIBUTE1,
310 X_ATTRIBUTE2,
311 X_ATTRIBUTE3,
312 X_ATTRIBUTE4,
313 X_ATTRIBUTE5,
314 X_ATTRIBUTE6,
315 X_ATTRIBUTE7,
316 X_ATTRIBUTE8,
317 X_ATTRIBUTE9,
318 X_ATTRIBUTE10,
319 X_ATTRIBUTE11,
320 X_ATTRIBUTE12,
321 X_ATTRIBUTE13,
322 X_ATTRIBUTE14,
323 X_ATTRIBUTE15,
324 X_ATTRIBUTE16,
325 X_ATTRIBUTE17,
326 X_ATTRIBUTE18,
327 X_ATTRIBUTE19,
328 X_ATTRIBUTE20,
329 X_REQUEST_ID,
330 X_PROGRAM_APPLICATION_ID,
331 X_PROGRAM_ID,
332 X_PROGRAM_UPDATE_DATE,
333 X_CREATION_DATE,
334 X_CREATED_BY,
335 X_LAST_UPDATE_DATE,
336 X_LAST_UPDATED_BY,
337 X_LAST_UPDATE_LOGIN
338 );
339
340 insert into PER_TIME_PERIOD_TYPES_TL (
341 PERIOD_TYPE,
342 DISPLAY_PERIOD_TYPE,
343 DESCRIPTION,
344 LAST_UPDATE_DATE,
345 LAST_UPDATED_BY,
346 LAST_UPDATE_LOGIN,
347 CREATED_BY,
348 CREATION_DATE,
349 LANGUAGE,
350 SOURCE_LANG
351 ) select
352 X_PERIOD_TYPE,
353 X_DISPLAY_PERIOD_TYPE,
354 X_DESCRIPTION,
355 X_LAST_UPDATE_DATE,
356 X_LAST_UPDATED_BY,
357 X_LAST_UPDATE_LOGIN,
358 X_CREATED_BY,
359 X_CREATION_DATE,
360 L.LANGUAGE_CODE,
361 userenv('LANG')
362 from FND_LANGUAGES L
363 where L.INSTALLED_FLAG in ('I', 'B')
364 and not exists
365 (select NULL
366 from PER_TIME_PERIOD_TYPES_TL T
367 where T.PERIOD_TYPE = X_PERIOD_TYPE
368 and T.LANGUAGE = L.LANGUAGE_CODE);
369
370 open c;
371 fetch c into X_ROWID;
372 if (c%notfound) then
373 close c;
374 raise no_data_found;
375 end if;
376 close c;
377
378 if g_debug then
379 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
380 end if;
381
382 End INSERT_ROW;
383
384 procedure LOCK_ROW (
385 X_PERIOD_TYPE in VARCHAR2,
386 X_NUMBER_PER_FISCAL_YEAR in NUMBER,
387 X_YEAR_TYPE_IN_NAME in VARCHAR2,
388 X_SYSTEM_FLAG in VARCHAR2,
389 X_DISPLAY_PERIOD_TYPE in VARCHAR2,
390 X_DESCRIPTION in VARCHAR2,
391 X_ATTRIBUTE_CATEGORY in VARCHAR2,
392 X_ATTRIBUTE1 in VARCHAR2,
393 X_ATTRIBUTE2 in VARCHAR2,
394 X_ATTRIBUTE3 in VARCHAR2,
395 X_ATTRIBUTE4 in VARCHAR2,
396 X_ATTRIBUTE5 in VARCHAR2,
397 X_ATTRIBUTE6 in VARCHAR2,
398 X_ATTRIBUTE7 in VARCHAR2,
399 X_ATTRIBUTE8 in VARCHAR2,
400 X_ATTRIBUTE9 in VARCHAR2,
401 X_ATTRIBUTE10 in VARCHAR2,
402 X_ATTRIBUTE11 in VARCHAR2,
403 X_ATTRIBUTE12 in VARCHAR2,
404 X_ATTRIBUTE13 in VARCHAR2,
405 X_ATTRIBUTE14 in VARCHAR2,
406 X_ATTRIBUTE15 in VARCHAR2,
407 X_ATTRIBUTE16 in VARCHAR2,
408 X_ATTRIBUTE17 in VARCHAR2,
409 X_ATTRIBUTE18 in VARCHAR2,
410 X_ATTRIBUTE19 in VARCHAR2,
411 X_ATTRIBUTE20 in VARCHAR2,
412 X_REQUEST_ID in NUMBER
413 ) is
414 cursor c is select
415 ATTRIBUTE20,
416 NUMBER_PER_FISCAL_YEAR,
417 YEAR_TYPE_IN_NAME,
418 SYSTEM_FLAG,
419 REQUEST_ID,
420 ATTRIBUTE_CATEGORY,
421 ATTRIBUTE1,
422 ATTRIBUTE2,
423 ATTRIBUTE3,
424 ATTRIBUTE4,
425 ATTRIBUTE5,
426 ATTRIBUTE6,
427 ATTRIBUTE7,
428 ATTRIBUTE8,
429 ATTRIBUTE9,
430 ATTRIBUTE10,
431 ATTRIBUTE11,
432 ATTRIBUTE12,
433 ATTRIBUTE13,
434 ATTRIBUTE14,
435 ATTRIBUTE15,
436 ATTRIBUTE16,
437 ATTRIBUTE17,
438 ATTRIBUTE18,
439 ATTRIBUTE19
440 from PER_TIME_PERIOD_TYPES
441 where PERIOD_TYPE = X_PERIOD_TYPE
442 for update of PERIOD_TYPE nowait;
443 recinfo c%rowtype;
444
445 cursor c1 is select
446 DISPLAY_PERIOD_TYPE,
447 DESCRIPTION,
448 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
449 from PER_TIME_PERIOD_TYPES_TL
450 where PERIOD_TYPE = X_PERIOD_TYPE
451 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
452 for update of PERIOD_TYPE nowait;
453
454 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.lock_row';
455
456 begin
457
458 if g_debug then
459 hr_utility.set_location( 'Entering : ' || l_proc , 1);
460 end if;
461
462 open c;
463 fetch c into recinfo;
464 if (c%notfound) then
465 close c;
466 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
467 app_exception.raise_exception;
468 end if;
469 close c;
470 if ( ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
471 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
472 AND (recinfo.NUMBER_PER_FISCAL_YEAR = X_NUMBER_PER_FISCAL_YEAR)
473 AND (recinfo.YEAR_TYPE_IN_NAME = X_YEAR_TYPE_IN_NAME)
474 AND ((recinfo.SYSTEM_FLAG = X_SYSTEM_FLAG)
475 OR ((recinfo.SYSTEM_FLAG is null) AND (X_SYSTEM_FLAG is null)))
476 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
477 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
478 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
479 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
480 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
481 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
482 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
483 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
484 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
488 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
485 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
486 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
487 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
489 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
490 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
491 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
492 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
493 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
494 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
495 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
496 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
497 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
498 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
499 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
500 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
501 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
502 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
503 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
504 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
505 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
506 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
507 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
508 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
509 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
510 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
511 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
512 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
513 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
514 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
515 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
516 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
517 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
518 ) then
519 null;
520 else
521 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
522 app_exception.raise_exception;
523 end if;
524
525 for tlinfo in c1 loop
526 if (tlinfo.BASELANG = 'Y') then
527 if ( (tlinfo.DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE)
528 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
529 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
530 ) then
531 null;
532 else
533 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
534 app_exception.raise_exception;
535 end if;
536 end if;
537 end loop;
538 return;
539
540 if g_debug then
541 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
542 end if;
543
544 end LOCK_ROW;
545
546 procedure UPDATE_ROW (
547 X_PERIOD_TYPE in VARCHAR2,
548 X_NUMBER_PER_FISCAL_YEAR in NUMBER,
549 X_YEAR_TYPE_IN_NAME in VARCHAR2,
550 X_SYSTEM_FLAG in VARCHAR2,
551 X_DESCRIPTION in VARCHAR2,
552 X_DISPLAY_PERIOD_TYPE in VARCHAR2,
553 X_ATTRIBUTE_CATEGORY in VARCHAR2,
554 X_ATTRIBUTE1 in VARCHAR2,
555 X_ATTRIBUTE2 in VARCHAR2,
556 X_ATTRIBUTE3 in VARCHAR2,
557 X_ATTRIBUTE4 in VARCHAR2,
558 X_ATTRIBUTE5 in VARCHAR2,
559 X_ATTRIBUTE6 in VARCHAR2,
560 X_ATTRIBUTE7 in VARCHAR2,
561 X_ATTRIBUTE8 in VARCHAR2,
562 X_ATTRIBUTE9 in VARCHAR2,
563 X_ATTRIBUTE10 in VARCHAR2,
564 X_ATTRIBUTE11 in VARCHAR2,
565 X_ATTRIBUTE12 in VARCHAR2,
566 X_ATTRIBUTE13 in VARCHAR2,
567 X_ATTRIBUTE14 in VARCHAR2,
568 X_ATTRIBUTE15 in VARCHAR2,
569 X_ATTRIBUTE16 in VARCHAR2,
570 X_ATTRIBUTE17 in VARCHAR2,
571 X_ATTRIBUTE18 in VARCHAR2,
572 X_ATTRIBUTE19 in VARCHAR2,
573 X_ATTRIBUTE20 in VARCHAR2,
574 X_REQUEST_ID in NUMBER,
575 X_PROGRAM_APPLICATION_ID in NUMBER,
576 X_PROGRAM_ID in NUMBER,
577 X_PROGRAM_UPDATE_DATE in DATE,
578 X_LAST_UPDATE_DATE in DATE,
579 X_LAST_UPDATED_BY in NUMBER,
580 X_LAST_UPDATE_LOGIN in NUMBER
581 ) is
582
583 cursor chk_source_lang is
584 select 1
585 from per_time_period_types_tl
586 where period_type = x_period_type
587 and source_lang <> userenv('LANG');
588
589 l_exists number;
590
591 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.update_row';
592
593 begin
594
595 if g_debug then
596 hr_utility.set_location( 'Entering : ' || l_proc , 1);
597 end if;
598
599
600 display_period_type_not_unique( X_PERIOD_TYPE,
601 X_DISPLAY_PERIOD_TYPE,
602 userenv('LANG') );
603
604 update PER_TIME_PERIOD_TYPES set
605 NUMBER_PER_FISCAL_YEAR = X_NUMBER_PER_FISCAL_YEAR,
606 YEAR_TYPE_IN_NAME = X_YEAR_TYPE_IN_NAME,
607 SYSTEM_FLAG = X_SYSTEM_FLAG,
608 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
609 ATTRIBUTE1 = X_ATTRIBUTE1,
610 ATTRIBUTE2 = X_ATTRIBUTE2,
611 ATTRIBUTE3 = X_ATTRIBUTE3,
612 ATTRIBUTE4 = X_ATTRIBUTE4,
613 ATTRIBUTE5 = X_ATTRIBUTE5,
614 ATTRIBUTE6 = X_ATTRIBUTE6,
615 ATTRIBUTE7 = X_ATTRIBUTE7,
616 ATTRIBUTE8 = X_ATTRIBUTE8,
617 ATTRIBUTE9 = X_ATTRIBUTE9,
618 ATTRIBUTE10 = X_ATTRIBUTE10,
622 ATTRIBUTE14 = X_ATTRIBUTE14,
619 ATTRIBUTE11 = X_ATTRIBUTE11,
620 ATTRIBUTE12 = X_ATTRIBUTE12,
621 ATTRIBUTE13 = X_ATTRIBUTE13,
623 ATTRIBUTE15 = X_ATTRIBUTE15,
624 ATTRIBUTE16 = X_ATTRIBUTE16,
625 ATTRIBUTE17 = X_ATTRIBUTE17,
626 ATTRIBUTE18 = X_ATTRIBUTE18,
627 ATTRIBUTE19 = X_ATTRIBUTE19,
628 ATTRIBUTE20 = X_ATTRIBUTE20,
629 REQUEST_ID = X_REQUEST_ID,
630 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
631 PROGRAM_ID = X_PROGRAM_ID,
632 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
633 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
634 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
635 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
636 where PERIOD_TYPE = X_PERIOD_TYPE;
637
638 if (sql%notfound) then
639 raise no_data_found;
640 end if;
641
642 open chk_source_lang;
643 fetch chk_source_lang into l_exists;
644 close chk_source_lang;
645 --
646 if l_exists is null then
647 update PER_TIME_PERIOD_TYPES set
648 DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
649 DESCRIPTION = X_DESCRIPTION
650 where PERIOD_TYPE = X_PERIOD_TYPE;
651 end if;
652
653 update PER_TIME_PERIOD_TYPES_TL set
654 DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
655 DESCRIPTION = X_DESCRIPTION,
656 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
657 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
658 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
659 SOURCE_LANG = userenv('LANG')
660 where PERIOD_TYPE = X_PERIOD_TYPE
661 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
662
663 if (sql%notfound) then
664 insert into PER_TIME_PERIOD_TYPES_TL (
665 PERIOD_TYPE,
666 DISPLAY_PERIOD_TYPE,
667 DESCRIPTION,
668 LAST_UPDATE_DATE,
669 LAST_UPDATED_BY,
670 LAST_UPDATE_LOGIN,
671 CREATED_BY,
672 CREATION_DATE,
673 LANGUAGE,
674 SOURCE_LANG
675 ) select
676 X_PERIOD_TYPE,
677 X_DISPLAY_PERIOD_TYPE,
678 X_DESCRIPTION,
679 X_LAST_UPDATE_DATE,
680 X_LAST_UPDATED_BY,
681 X_LAST_UPDATE_LOGIN,
682 X_LAST_UPDATED_BY,
683 X_LAST_UPDATE_DATE,
684 L.LANGUAGE_CODE,
685 userenv('LANG')
686 from FND_LANGUAGES L
687 where L.INSTALLED_FLAG in ('I', 'B')
688 and not exists
689 (select NULL
690 from PER_TIME_PERIOD_TYPES_TL T
691 where T.PERIOD_TYPE = X_PERIOD_TYPE
692 and T.LANGUAGE = L.LANGUAGE_CODE);
693 end if;
694
695 if g_debug then
696 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
697 end if;
698
699
700 end UPDATE_ROW;
701
702 procedure DELETE_ROW (
703 X_PERIOD_TYPE in VARCHAR2
704 ) is
705
706 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.delete_row';
707
708 begin
709
710 if g_debug then
711 hr_utility.set_location( 'Entering : ' || l_proc , 1);
712 end if;
713
714 delete from PER_TIME_PERIOD_TYPES_TL
715 where PERIOD_TYPE = X_PERIOD_TYPE;
716
717 if (sql%notfound) then
718 raise no_data_found;
719 end if;
720
721 delete from PER_TIME_PERIOD_TYPES
722 where PERIOD_TYPE = X_PERIOD_TYPE;
723
724 if (sql%notfound) then
725 raise no_data_found;
726 end if;
727
728 if g_debug then
729 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
730 end if;
731
732 end DELETE_ROW;
733
734 procedure ADD_LANGUAGE
735 is
736 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.add_language';
737 begin
738
739 if g_debug then
740 hr_utility.set_location( 'Entering : ' || l_proc , 1);
741 end if;
742
743 delete from PER_TIME_PERIOD_TYPES_TL T
744 where not exists
745 (select NULL
746 from PER_TIME_PERIOD_TYPES B
747 where B.PERIOD_TYPE = T.PERIOD_TYPE
748 );
749
750 update PER_TIME_PERIOD_TYPES_TL T set (
751 DISPLAY_PERIOD_TYPE,
752 DESCRIPTION
753 ) = (select
754 B.DISPLAY_PERIOD_TYPE,
755 B.DESCRIPTION
756 from PER_TIME_PERIOD_TYPES_TL B
757 where B.PERIOD_TYPE = T.PERIOD_TYPE
758 and B.LANGUAGE = T.SOURCE_LANG)
759 where (
760 T.PERIOD_TYPE,
761 T.LANGUAGE
762 ) in (select
763 SUBT.PERIOD_TYPE,
764 SUBT.LANGUAGE
765 from PER_TIME_PERIOD_TYPES_TL SUBB, PER_TIME_PERIOD_TYPES_TL SUBT
766 where SUBB.PERIOD_TYPE = SUBT.PERIOD_TYPE
767 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
768 and (SUBB.DISPLAY_PERIOD_TYPE <> SUBT.DISPLAY_PERIOD_TYPE
769 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
770 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
771 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
772 ));
773
774 insert into PER_TIME_PERIOD_TYPES_TL (
775 PERIOD_TYPE,
776 DISPLAY_PERIOD_TYPE,
777 DESCRIPTION,
778 LAST_UPDATE_DATE,
779 LAST_UPDATED_BY,
780 LAST_UPDATE_LOGIN,
781 CREATED_BY,
782 CREATION_DATE,
783 LANGUAGE,
784 SOURCE_LANG
785 ) select
786 B.PERIOD_TYPE,
790 B.LAST_UPDATED_BY,
787 B.DISPLAY_PERIOD_TYPE,
788 B.DESCRIPTION,
789 B.LAST_UPDATE_DATE,
791 B.LAST_UPDATE_LOGIN,
792 B.CREATED_BY,
793 B.CREATION_DATE,
794 L.LANGUAGE_CODE,
795 B.SOURCE_LANG
796 from PER_TIME_PERIOD_TYPES_TL B, FND_LANGUAGES L
797 where L.INSTALLED_FLAG in ('I', 'B')
798 and B.LANGUAGE = userenv('LANG')
799 and not exists
800 (select NULL
801 from PER_TIME_PERIOD_TYPES_TL T
802 where T.PERIOD_TYPE = B.PERIOD_TYPE
803 and T.LANGUAGE = L.LANGUAGE_CODE);
804
805 if g_debug then
806 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
807 end if;
808
809 end ADD_LANGUAGE;
810
811 procedure OWNER_TO_WHO (
812 X_OWNER in VARCHAR2,
813 X_CREATION_DATE out nocopy DATE,
814 X_CREATED_BY out nocopy NUMBER,
815 X_LAST_UPDATE_DATE out nocopy DATE,
816 X_LAST_UPDATED_BY out nocopy NUMBER,
817 X_LAST_UPDATE_LOGIN out nocopy NUMBER
818 ) is
819
820 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.owner_to_who';
821
822 begin
823
824 if g_debug then
825 hr_utility.set_location( 'Entering : ' || l_proc , 1);
826 end if;
827
828 if X_OWNER = 'SEED' then
829 hr_general2.init_fndload
830 (p_resp_appl_id => 801
831 ,p_user_id => 1);
832 else
833 hr_general2.init_fndload
834 (p_resp_appl_id => 801
835 ,p_user_id => 0 );
836 end if;
837
838 X_CREATED_BY := fnd_global.user_id;
839 X_CREATION_DATE := sysdate;
840 X_LAST_UPDATE_DATE := sysdate;
841 X_LAST_UPDATED_BY := fnd_global.user_id;
842 X_LAST_UPDATE_LOGIN := fnd_global.login_id;
843
844 if g_debug then
845 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
846 end if;
847
848 end OWNER_TO_WHO;
849
850
851 procedure LOAD_ROW (
852 X_PERIOD_TYPE in VARCHAR2,
853 X_NUMBER_PER_FISCAL_YEAR in NUMBER,
854 X_YEAR_TYPE_IN_NAME in VARCHAR2,
855 X_SYSTEM_FLAG in VARCHAR2,
856 X_ATTRIBUTE_CATEGORY in VARCHAR2,
857 X_ATTRIBUTE1 in VARCHAR2,
858 X_ATTRIBUTE2 in VARCHAR2,
859 X_ATTRIBUTE3 in VARCHAR2,
860 X_ATTRIBUTE4 in VARCHAR2,
861 X_ATTRIBUTE5 in VARCHAR2,
862 X_ATTRIBUTE6 in VARCHAR2,
863 X_ATTRIBUTE7 in VARCHAR2,
864 X_ATTRIBUTE8 in VARCHAR2,
865 X_ATTRIBUTE9 in VARCHAR2,
866 X_ATTRIBUTE10 in VARCHAR2,
867 X_ATTRIBUTE11 in VARCHAR2,
868 X_ATTRIBUTE12 in VARCHAR2,
869 X_ATTRIBUTE13 in VARCHAR2,
870 X_ATTRIBUTE14 in VARCHAR2,
871 X_ATTRIBUTE15 in VARCHAR2,
872 X_ATTRIBUTE16 in VARCHAR2,
873 X_ATTRIBUTE17 in VARCHAR2,
874 X_ATTRIBUTE18 in VARCHAR2,
875 X_ATTRIBUTE19 in VARCHAR2,
876 X_ATTRIBUTE20 in VARCHAR2,
877 X_DESCRIPTION in VARCHAR2,
878 X_DISPLAY_PERIOD_TYPE in VARCHAR2,
879 X_OWNER in VARCHAR2
880 ) is
881
882 l_ROWID varchar2(30);
883 l_CREATION_DATE DATE;
884 l_CREATED_BY NUMBER;
885 l_LAST_UPDATE_DATE DATE;
886 l_LAST_UPDATED_BY NUMBER;
887 l_LAST_UPDATE_LOGIN NUMBER;
888
889 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.load_row';
890
891
892 begin
893
894 if g_debug then
895 hr_utility.set_location( 'Entering : ' || l_proc , 1);
896 end if;
897
898 OWNER_TO_WHO ( X_OWNER => X_OWNER,
899 X_CREATION_DATE => l_CREATION_DATE,
900 X_CREATED_BY => l_CREATED_BY,
901 X_LAST_UPDATE_DATE => l_LAST_UPDATE_DATE,
902 X_LAST_UPDATED_BY => l_LAST_UPDATED_BY,
903 X_LAST_UPDATE_LOGIN => l_LAST_UPDATE_LOGIN );
904
905 begin
906 UPDATE_ROW (
907 X_PERIOD_TYPE,
908 X_NUMBER_PER_FISCAL_YEAR,
909 X_YEAR_TYPE_IN_NAME,
910 X_SYSTEM_FLAG,
911 X_DESCRIPTION,
912 X_DISPLAY_PERIOD_TYPE,
913 X_ATTRIBUTE_CATEGORY,
914 X_ATTRIBUTE1,
915 X_ATTRIBUTE2,
916 X_ATTRIBUTE3,
917 X_ATTRIBUTE4,
918 X_ATTRIBUTE5,
919 X_ATTRIBUTE6,
920 X_ATTRIBUTE7,
921 X_ATTRIBUTE8,
922 X_ATTRIBUTE9,
923 X_ATTRIBUTE10,
924 X_ATTRIBUTE11,
925 X_ATTRIBUTE12,
926 X_ATTRIBUTE13,
927 X_ATTRIBUTE14,
928 X_ATTRIBUTE15,
929 X_ATTRIBUTE16,
930 X_ATTRIBUTE17,
931 X_ATTRIBUTE18,
932 X_ATTRIBUTE19,
933 X_ATTRIBUTE20,
934 NULL,
935 NULL,
936 NULL,
937 NULL,
938 l_LAST_UPDATE_DATE,
939 l_LAST_UPDATED_BY,
940 l_LAST_UPDATE_LOGIN
941 );
942 exception
943 when no_data_found then
944 INSERT_ROW (
945 l_ROWID,
946 X_PERIOD_TYPE,
947 X_NUMBER_PER_FISCAL_YEAR,
948 X_YEAR_TYPE_IN_NAME,
949 X_SYSTEM_FLAG,
950 X_DESCRIPTION,
951 X_DISPLAY_PERIOD_TYPE,
952 X_ATTRIBUTE_CATEGORY,
953 X_ATTRIBUTE1,
954 X_ATTRIBUTE2,
955 X_ATTRIBUTE3,
956 X_ATTRIBUTE4,
957 X_ATTRIBUTE5,
958 X_ATTRIBUTE6,
959 X_ATTRIBUTE7,
960 X_ATTRIBUTE8,
961 X_ATTRIBUTE9,
962 X_ATTRIBUTE10,
963 X_ATTRIBUTE11,
964 X_ATTRIBUTE12,
965 X_ATTRIBUTE13,
966 X_ATTRIBUTE14,
967 X_ATTRIBUTE15,
968 X_ATTRIBUTE16,
969 X_ATTRIBUTE17,
970 X_ATTRIBUTE18,
971 X_ATTRIBUTE19,
972 X_ATTRIBUTE20,
973 NULL,
974 NULL,
975 NULL,
976 NULL,
977 l_CREATION_DATE,
978 l_CREATED_BY,
979 l_LAST_UPDATE_DATE,
980 l_LAST_UPDATED_BY,
981 l_LAST_UPDATE_LOGIN
982 );
983 end;
984
985 if g_debug then
986 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
987 end if;
988
989 end LOAD_ROW;
990
991 procedure TRANSLATE_ROW (
992 X_PERIOD_TYPE in VARCHAR2,
993 X_DESCRIPTION in VARCHAR2,
994 X_DISPLAY_PERIOD_TYPE in VARCHAR2,
995 X_OWNER in VARCHAR2
996 ) is
997
998 l_CREATION_DATE DATE;
999 l_CREATED_BY NUMBER;
1000 l_LAST_UPDATE_DATE DATE;
1001 l_LAST_UPDATED_BY NUMBER;
1002 l_LAST_UPDATE_LOGIN NUMBER;
1003
1004 l_exists number;
1005
1006 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.translate_row';
1007
1008 cursor chk_source_lang is
1009 select 1
1010 from per_time_period_types_tl
1011 where period_type = x_period_type
1012 and source_lang <> userenv('LANG');
1013 begin
1014
1015 if g_debug then
1016 hr_utility.set_location( 'Entering : ' || l_proc , 1);
1017 end if;
1018
1019 display_period_type_not_unique( X_PERIOD_TYPE,
1020 X_DISPLAY_PERIOD_TYPE,
1021 userenv('LANG') );
1022
1023 OWNER_TO_WHO ( X_OWNER => X_OWNER,
1024 X_CREATION_DATE => l_CREATION_DATE,
1025 X_CREATED_BY => l_CREATED_BY,
1026 X_LAST_UPDATE_DATE => l_LAST_UPDATE_DATE,
1027 X_LAST_UPDATED_BY => l_LAST_UPDATED_BY,
1028 X_LAST_UPDATE_LOGIN => l_LAST_UPDATE_LOGIN );
1029
1030 update PER_TIME_PERIOD_TYPES_TL
1031 set DESCRIPTION = X_DESCRIPTION,
1032 DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
1033 LAST_UPDATE_DATE = l_LAST_UPDATE_DATE,
1034 LAST_UPDATED_BY = l_LAST_UPDATED_BY,
1035 LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN,
1036 SOURCE_LANG = userenv('LANG')
1037 where PERIOD_TYPE = X_PERIOD_TYPE
1038 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1039
1040 open chk_source_lang;
1041 fetch chk_source_lang into l_exists;
1042 close chk_source_lang;
1043 --
1044
1045 if l_exists is null then
1046 update PER_TIME_PERIOD_TYPES set
1047 DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
1048 DESCRIPTION = X_DESCRIPTION,
1049 LAST_UPDATE_DATE = l_LAST_UPDATE_DATE,
1050 LAST_UPDATED_BY = l_LAST_UPDATED_BY,
1051 LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN
1052 where PERIOD_TYPE = X_PERIOD_TYPE;
1053 end if;
1054 if g_debug then
1055 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
1056 end if;
1057
1058 end TRANSLATE_ROW;
1059
1060 procedure validate_translation (
1061 X_PERIOD_TYPE in VARCHAR2,
1062 X_LANGUAGE in VARCHAR2,
1063 X_DISPLAY_PERIOD_TYPE in VARCHAR2,
1064 X_DESCRIPTION in VARCHAR2 ) is
1065
1066 l_exists number;
1067
1068 cursor chk_source_lang is
1069 select 1
1070 from per_time_period_types_tl
1071 where period_type = x_period_type
1072 and source_lang <> userenv('LANG');
1073
1074 l_LAST_UPDATE_DATE DATE;
1075 l_LAST_UPDATED_BY NUMBER;
1076 l_LAST_UPDATE_LOGIN NUMBER;
1077
1078 l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.validate_translation';
1079
1080 begin
1081
1082 if g_debug then
1083 hr_utility.set_location( 'Entering : ' || l_proc , 1);
1084 end if;
1085
1086
1087 display_period_type_not_unique( X_PERIOD_TYPE,
1088 X_DISPLAY_PERIOD_TYPE,
1089 X_LANGUAGE );
1090 open chk_source_lang;
1091 fetch chk_source_lang into l_exists;
1092 close chk_source_lang;
1093 --
1094 l_LAST_UPDATE_DATE := sysdate;
1095 l_LAST_UPDATED_BY := fnd_global.user_id;
1096 l_LAST_UPDATE_LOGIN := fnd_global.login_id;
1097
1098 if l_exists is null and userenv('LANG') = X_LANGUAGE then
1099 update PER_TIME_PERIOD_TYPES set
1100 DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
1101 DESCRIPTION = X_DESCRIPTION,
1102 LAST_UPDATE_DATE = l_LAST_UPDATE_DATE,
1103 LAST_UPDATED_BY = l_LAST_UPDATED_BY
1104 where PERIOD_TYPE = X_PERIOD_TYPE;
1105 end if;
1106
1107 if g_debug then
1108 hr_utility.set_location( 'Leaving : ' || l_proc , 2);
1109 end if;
1110
1111 end validate_translation;
1112
1113 END PER_TIME_PERIOD_TYPES_PKG;