[Home] [Help]
PACKAGE BODY: APPS.PAY_UPGRADE_DEFINITIONS_PKG
Source
1 PACKAGE BODY PAY_UPGRADE_DEFINITIONS_PKG AS
2 /* $Header: pypud01t.pkb 120.1 2005/06/16 03:24 nmanchan noship $ */
3
4 g_package varchar2(33) := ' pay_upgrade_definitions_pkg.';
5
6 PROCEDURE chk_short_name ( p_short_name in varchar2 ) is
7 --
8 cursor csr_unique_name is
9 select null from pay_upgrade_definitions
10 where upper(short_name) = upper(p_short_name);
11 --
12 l_proc varchar2(72) := g_package||'chk_short_name';
13 l_exists varchar2(1);
14 --
15 BEGIN
16 --
17 hr_utility.set_location('Entering:'|| l_proc, 10);
18
19 open csr_unique_name;
20 fetch csr_unique_name into l_exists;
21
22 if csr_unique_name%found then
23
24 close csr_unique_name;
25 fnd_message.set_name( 'PAY', 'PAY_33187_SHORT_NAME_EXISTS' );
26 fnd_message.raise_error;
27
28 end if;
29 close csr_unique_name;
30
31 hr_utility.set_location('Leaving:'|| l_proc, 20);
32 --
33 END chk_short_name;
34
35
36 PROCEDURE chk_lookup( p_lookup_type in varchar2
37 ,p_lookup_code in varchar2
38 ,p_column in varchar2
39 )
40 is
41 --
42 l_proc varchar2(72) := g_package||'chk_lookup';
43 --
44 BEGIN
45 --
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47
48 if hr_api.not_exists_in_hr_lookups
49 (p_effective_date => trunc(sysdate)
50 ,p_lookup_type => p_lookup_type
51 ,p_lookup_code => p_lookup_code
52 )
53 then
54 fnd_message.set_name('PAY', 'HR_52966_INVALID_LOOKUP');
55 fnd_message.set_token('LOOKUP_TYPE', p_lookup_type);
56 fnd_message.set_token('COLUMN', p_column);
57 fnd_message.raise_error;
58 end if;
59
60 hr_utility.set_location('Leaving:'|| l_proc, 20);
61 --
62 END chk_lookup;
63
64 PROCEDURE chk_mandatory_arg( p_argument in varchar2 , p_column in varchar2)
65 is
66 --
67 l_proc varchar2(72) := g_package||'chk_mandatory_arg';
68 --
69 BEGIN
70 --
71 hr_utility.set_location('Entering:'|| l_proc, 10);
72
73 if p_argument is null then
74 fnd_message.set_name( 'PAY', 'PAY_75178_NO_DATA' );
75 fnd_message.set_token('VALUE1' , l_proc);
76 fnd_message.set_token('VALUE2' , p_column) ;
77 fnd_message.raise_error;
78 end if;
79
80 hr_utility.set_location(' Leaving:'|| l_proc, 20);
81 --
82 END chk_mandatory_arg;
83
84 PROCEDURE chk_legislation_code( p_legislation_code in varchar2 )
85 is
86 --
87 cursor csr_legislation_code is
88 select null
89 from fnd_territories
90 where territory_code = p_legislation_code ;
91 --
92 l_exists varchar2(1);
93 l_proc varchar2(100) := g_package || 'chk_legislation_code';
94 --
95 BEGIN
96 --
97 hr_utility.set_location('Entering:'|| l_proc, 10);
98
99 open csr_legislation_code;
100 fetch csr_legislation_code into l_exists ;
101
102 if csr_legislation_code%notfound then
103 close csr_legislation_code;
104 fnd_message.set_name('PAY', 'PAY_33177_LEG_CODE_INVALID');
105 fnd_message.raise_error;
106 end if;
107 close csr_legislation_code;
108
109 hr_utility.set_location(' Leaving:'|| l_proc, 20);
110 --
111 END chk_legislation_code;
112
113 PROCEDURE chk_delete ( p_upgrade_definition_id in number )
114 is
115 --
116 cursor csr_delete is
117 select null from pay_upgrade_status
118 where
119 upgrade_definition_id = p_upgrade_definition_id ;
120 --
121 l_exists varchar2(1);
122 l_proc varchar2(100) := g_package || 'chk_delete';
123 --
124 BEGIN
125 --
126 hr_utility.set_location('Entering:'|| l_proc, 10);
127
128 open csr_delete;
129 fetch csr_delete into l_exists ;
130
131 if csr_delete%found then
132 close csr_delete;
133 fnd_message.set_name('PAY', 'PAY_33188_DELETION_NOT_ALLOWED');
134 fnd_message.raise_error;
135 end if;
136 close csr_delete;
137
138 hr_utility.set_location(' Leaving:'|| l_proc, 20);
139 --
140 END chk_delete;
141
142
143 PROCEDURE Insert_Row (
144 P_SHORT_NAME in VARCHAR2
145 ,P_NAME in VARCHAR2
146 ,P_DESCRIPTION in VARCHAR2
147 ,P_LEGISLATION_CODE in VARCHAR2 default null
148 ,P_UPGRADE_LEVEL in VARCHAR2
149 ,P_CRITICALITY in VARCHAR2
150 ,P_FAILURE_POINT in VARCHAR2
151 ,P_LEGISLATIVELY_ENABLED in VARCHAR2
152 ,P_UPGRADE_PROCEDURE in VARCHAR2
153 ,P_THREADING_LEVEL in VARCHAR2
154 ,P_UPGRADE_METHOD in VARCHAR2
155 ,P_QUALIFYING_PROCEDURE in VARCHAR2 default null
156 ,P_OWNER_APPL_ID in NUMBER default null
157 ,P_FIRST_PATCHSET in VARCHAR2 default null
158 ,P_VALIDATE_CODE in VARCHAR2 default null
159 ,P_ADDITIONAL_INFO in VARCHAR2 default null
160 ,P_LAST_UPDATE_DATE in DATE
161 ,P_LAST_UPDATED_BY in NUMBER
162 ,P_LAST_UPDATE_LOGIN in NUMBER
163 ,P_CREATED_BY in NUMBER
164 ,P_CREATION_DATE in DATE
165 ,P_UPGRADE_DEFINITION_ID out nocopy NUMBER
166 ) is
167 --
168 l_upgrade_definition_id PAY_UPGRADE_DEFINITIONS.UPGRADE_DEFINITION_ID%TYPE;
169 l_exists varchar2(1);
170 l_proc varchar2(100) := g_package || 'Insert_Row';
171
172 cursor csr_exists is
173 select null from PAY_UPGRADE_DEFINITIONS
174 where upgrade_definition_id = l_upgrade_definition_id;
175
176 cursor csr_nextseq is
177 select pay_upgrade_definitions_s.nextval from dual;
178 --
179 BEGIN
180 --
181 hr_utility.set_location('Entering:'|| l_proc, 10);
182
183 -- Check for mandatory arguments.
184
185 chk_mandatory_arg( p_short_name ,'SHORT_NAME' );
186
187 chk_mandatory_arg( p_name , 'NAME' );
188
189 chk_mandatory_arg( p_description, 'DESCRIPTION' );
190
191 chk_mandatory_arg( p_upgrade_level, 'UPGRADE_LEVEL' );
192
193 chk_mandatory_arg( p_criticality, 'CRITICALITY' );
194
195 chk_mandatory_arg( p_threading_level, 'THREADING_LEVEL' );
196
197 chk_mandatory_arg( p_failure_point, 'FAILURE_POINT' );
198
199 chk_mandatory_arg( p_legislatively_enabled, 'LEGISLATIVELY_ENABLED' );
200
201 chk_mandatory_arg( p_upgrade_procedure, 'UPGRADE_PROCEDURE' );
202
203 chk_mandatory_arg( p_upgrade_method, 'UPGRADE_METHOD');
204
205
206 -- Check for Lookups
207
208 chk_lookup( 'PAY_GEN_UPGRADE_LEVEL', p_upgrade_level ,'UPGRADE_LEVEL');
209
210 chk_lookup( 'PAY_GEN_UPGRADE_CRITICALITY', p_criticality , 'CRITICALITY');
211
212 chk_lookup( 'PAY_GEN_THREADING_LEVEL', p_threading_level, 'THREADING_LEVEL' );
213
214 chk_lookup( 'PAY_GEN_FAILURE_POINT', p_failure_point, 'FAILURE_POINT' );
215
216 chk_lookup( 'YES_NO', p_legislatively_enabled, 'LEGISLATIVELY_ENABLED' );
217
218 chk_lookup( 'PAY_GEN_UPGRADE_METHOD', p_upgrade_method, 'UPGRADE_METHOD' );
219
220
221 -- Check for Non Unique Short name.
222
223 chk_short_name( p_short_name );
224
225
226 -- Check for valid Legislation code.
227
228 if p_legislation_code is not null then
229 chk_legislation_code( p_legislation_code );
230 end if;
231
232
233 -- Upgrade Level cannot be Global when legsislation code is not null.
234
235 if p_legislation_code is not null and p_upgrade_level = 'G' then
236 fnd_message.set_name('PAY', 'PAY_33189_GLOBAL_LEG_UPGDEF');
237 fnd_message.raise_error;
238 end if;
239
240 -- Legislatively Enabled must be 'N' when legislation code is not null.
241
242 if p_legislation_code is not null and p_legislatively_enabled <> 'N' then
243 fnd_message.set_name('PAY', 'PAY_33190_ENABLE_LEG_UPGDEF');
244 fnd_message.raise_error;
245 end if;
246
247 open csr_nextseq;
248 fetch csr_nextseq into l_upgrade_definition_id;
249 close csr_nextseq;
250
251
252 insert into PAY_UPGRADE_DEFINITIONS (
253 UPGRADE_DEFINITION_ID,
254 SHORT_NAME,
255 NAME,
256 DESCRIPTION,
257 LEGISLATION_CODE,
258 UPGRADE_LEVEL,
259 CRITICALITY,
260 FAILURE_POINT,
261 LEGISLATIVELY_ENABLED,
262 UPGRADE_PROCEDURE,
263 THREADING_LEVEL,
264 UPGRADE_METHOD,
265 QUALIFYING_PROCEDURE,
266 OWNER_APPLICATION_ID,
267 FIRST_PATCHSET,
268 VALIDATE_CODE,
269 ADDITIONAL_INFO,
270 CREATION_DATE,
271 CREATED_BY,
272 LAST_UPDATE_DATE,
273 LAST_UPDATED_BY,
274 LAST_UPDATE_LOGIN
275 ) values (
276 l_upgrade_definition_id,
277 p_short_name,
278 p_name,
279 p_description,
280 p_legislation_code,
281 p_upgrade_level,
282 p_criticality,
283 p_failure_point,
284 p_legislatively_enabled,
285 p_upgrade_procedure,
286 p_threading_level,
287 p_upgrade_method,
288 p_qualifying_procedure,
289 p_owner_appl_id,
290 p_first_patchset,
291 p_validate_code,
292 p_additional_info,
293 p_creation_date,
294 p_created_by,
295 p_last_update_date,
296 p_last_updated_by,
297 p_last_update_login );
298
299 insert into PAY_UPGRADE_DEFINITIONS_TL (
300 UPGRADE_DEFINITION_ID,
301 LANGUAGE,
302 SOURCE_LANG,
303 NAME,
304 DESCRIPTION,
305 ADDITIONAL_INFO,
306 LAST_UPDATE_DATE,
307 LAST_UPDATED_BY,
308 LAST_UPDATE_LOGIN,
309 CREATED_BY,
310 CREATION_DATE
311 ) select
312 l_upgrade_definition_id,
313 l.language_code,
314 userenv('lang'),
315 p_name,
316 p_description,
317 p_additional_info,
318 p_last_update_date,
319 p_last_updated_by,
320 p_last_update_login,
321 p_created_by,
322 p_creation_date
323 from FND_LANGUAGES L
324 where L.INSTALLED_FLAG in ('I', 'B')
325 and not exists
326 (select NULL
327 from PAY_UPGRADE_DEFINITIONS_TL T
328 where T.UPGRADE_DEFINITION_ID = l_UPGRADE_DEFINITION_ID
329 and T.LANGUAGE = L.LANGUAGE_CODE);
330
331
332 open csr_exists;
333 fetch csr_exists into l_exists;
334
335 if csr_exists%notfound then
336 close csr_exists;
337 raise no_data_found;
338 end if;
339
340 p_upgrade_definition_id := l_upgrade_definition_id;
341
342 close csr_exists;
343
344 hr_utility.set_location(' Leaving:'|| l_proc, 20);
345 --
346 END Insert_Row;
347
348 PROCEDURE Update_Row (
349 P_UPGRADE_DEFINITION_ID in NUMBER
350 , P_CRITICALITY in VARCHAR2
351 , P_FAILURE_POINT in VARCHAR2
352 , P_UPGRADE_PROCEDURE in VARCHAR2
353 , P_DESCRIPTION in VARCHAR2
354 , P_QUALIFYING_PROCEDURE in VARCHAR2
355 , P_OWNER_APPL_ID in NUMBER
356 , P_FIRST_PATCHSET in VARCHAR2
357 , P_VALIDATE_CODE in VARCHAR2
358 , P_ADDITIONAL_INFO in VARCHAR2
359 , P_LAST_UPDATE_DATE in DATE
360 , P_LAST_UPDATED_BY in NUMBER
361 , P_LAST_UPDATE_LOGIN in NUMBER
362 ) is
363 --
364 l_exists varchar2(1);
365 l_proc varchar2(100) := g_package || 'Update_Row';
366 --
367 BEGIN
368 --
369 hr_utility.set_location('Entering:'|| l_proc, 10);
370
371 -- Check for mandatory arguments.
372
373 chk_mandatory_arg( p_upgrade_definition_id, 'UPGRADE_DEFINITION_ID' );
374
375 chk_mandatory_arg( p_description, 'DESCRIPTION' );
376
377 chk_mandatory_arg( p_criticality, 'CRITICALITY' );
378
379 chk_mandatory_arg( p_failure_point, 'FAILURE_POINT' );
380
381 chk_mandatory_arg( p_upgrade_procedure, 'UPGRADE_PROCEDURE' );
382
383
384 -- Check for Lookups
385
386 chk_lookup( 'PAY_GEN_UPGRADE_CRITICALITY', p_criticality, 'CRITICALITY' );
387
388 chk_lookup( 'PAY_GEN_FAILURE_POINT', p_failure_point, 'FAILURE_POINT' );
389
390
391 update PAY_UPGRADE_DEFINITIONS set
392 description = p_description,
393 criticality = p_criticality,
394 failure_point = p_failure_point,
395 upgrade_procedure = p_upgrade_procedure,
396 qualifying_procedure = p_qualifying_procedure,
397 owner_application_id = p_owner_appl_id,
398 first_patchset = p_first_patchset,
399 validate_code = p_validate_code,
400 additional_info = p_additional_info,
401 last_update_date = p_last_update_date,
402 last_updated_by = p_last_updated_by,
403 last_update_login = p_last_update_login
404 where upgrade_definition_id = p_upgrade_definition_id;
405
406 if (sql%notfound) then
407 raise no_data_found;
408 end if;
409
410 update PAY_UPGRADE_DEFINITIONS_TL set
411 description = p_description,
412 additional_info = p_additional_info,
413 last_update_date = p_last_update_date,
414 last_updated_by = p_last_updated_by,
415 last_update_login = p_last_update_login,
416 source_lang = userenv('lang')
417 where upgrade_definition_id = p_upgrade_definition_id
418 and userenv('lang') in (language, source_lang);
419
420 if (sql%notfound) then
421 raise no_data_found;
422 end if;
423
424 hr_utility.set_location(' Leaving:'|| l_proc, 20);
425 --
426 END Update_Row;
427
428 PROCEDURE Lock_Row (
429 P_UPGRADE_DEFINITION_ID in NUMBER
430 , P_SHORT_NAME in VARCHAR2
431 , P_NAME in VARCHAR2
432 , P_LEGISLATION_CODE in VARCHAR2
433 , P_UPGRADE_LEVEL in VARCHAR2
434 , P_CRITICALITY in VARCHAR2
435 , P_FAILURE_POINT in VARCHAR2
436 , P_LEGISLATIVELY_ENABLED in VARCHAR2
437 , P_UPGRADE_PROCEDURE in VARCHAR2
438 , P_THREADING_LEVEL in VARCHAR2
439 , P_DESCRIPTION in VARCHAR2
440 , P_UPGRADE_METHOD in VARCHAR2
441 , P_QUALIFYING_PROCEDURE in VARCHAR2
442 ) is
443 --
444 cursor csr_lck is
445 select
446 SHORT_NAME,
447 NAME,
448 LEGISLATION_CODE,
449 UPGRADE_LEVEL,
450 CRITICALITY,
451 FAILURE_POINT,
452 LEGISLATIVELY_ENABLED,
453 UPGRADE_PROCEDURE,
454 THREADING_LEVEL,
455 DESCRIPTION,
456 UPGRADE_METHOD,
457 QUALIFYING_PROCEDURE
458 from PAY_UPGRADE_DEFINITIONS
459 where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID
460 for update of UPGRADE_DEFINITION_ID nowait;
461 --
462 cursor csr_lck_tl is
463 select
464 NAME,
465 DESCRIPTION,
466 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
467 from PAY_UPGRADE_DEFINITIONS_TL
468 where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID
469 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
470 for update of UPGRADE_DEFINITION_ID nowait;
471 --
472 recinfo csr_lck%rowtype;
473 l_exists varchar2(1);
474 l_proc varchar2(100) := g_package || 'Lock_Row';
475 --
476 BEGIN
477 --
478 hr_utility.set_location('Entering:'|| l_proc, 10);
479
480 -- Lock the Base table record.
481
482 open csr_lck;
483 fetch csr_lck into recinfo;
484 if (csr_lck%notfound) then
485 close csr_lck;
486 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
487 app_exception.raise_exception;
488 end if;
489 close csr_lck;
490
491 if ( (recinfo.short_name = p_short_name)
492 and ((recinfo.legislation_code = p_legislation_code)
493 or ((recinfo.legislation_code is null) and (p_legislation_code is null)))
494 and (recinfo.name = p_name)
495 and (recinfo.description = p_description)
496 and (recinfo.upgrade_level = p_upgrade_level)
497 and (recinfo.criticality = p_criticality)
498 and (recinfo.failure_point = p_failure_point)
499 and (recinfo.legislatively_enabled = p_legislatively_enabled)
500 and (recinfo.upgrade_procedure = p_upgrade_procedure)
501 and (recinfo.threading_level = p_threading_level)
502 and (recinfo.upgrade_method = p_upgrade_method )
503 and ((recinfo.qualifying_procedure = p_qualifying_procedure)
504 or ((recinfo.qualifying_procedure is null) and (p_qualifying_procedure is null)))
505
506 ) then
507 null;
508 else
509 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
510 app_exception.raise_exception;
511 end if;
512
513 for tlinfo in csr_lck_tl loop
514 if (tlinfo.BASELANG = 'Y') then
515 if ( (tlinfo.DESCRIPTION = p_DESCRIPTION)
516 and (tlinfo.name = p_name)
517 ) then
518 null;
519 else
520 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
521 app_exception.raise_exception;
522 end if;
523 end if;
524 end loop;
525
526 hr_utility.set_location(' Leaving:'|| l_proc, 20);
527 return;
528 --
529 END Lock_Row;
530
531 PROCEDURE Delete_Row (
532 P_UPGRADE_DEFINITION_ID in NUMBER
533 )is
534 --
535 l_proc varchar2(100) := g_package || 'Delete_Row';
536 --
537 BEGIN
538 --
539 hr_utility.set_location('Entering:'|| l_proc, 10);
540
541 chk_mandatory_arg( p_upgrade_definition_id, 'UPGRADE_DEFINITION_ID' );
542
543 chk_delete ( p_upgrade_definition_id );
544
545 delete from PAY_UPGRADE_LEGISLATIONS
546 where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID;
547
548 delete from PAY_UPGRADE_DEFINITIONS_TL
549 where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID;
550
551 if (sql%notfound) then
552 raise no_data_found;
553 end if;
554
555 delete from PAY_UPGRADE_DEFINITIONS
556 where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID;
557
558 if (sql%notfound) then
559 raise no_data_found;
560 end if;
561
562 hr_utility.set_location(' Leaving:'|| l_proc, 20);
563 --
564 END Delete_Row;
565
566 procedure ADD_LANGUAGE
567 is
568 BEGIN
569 --
570 delete from PAY_UPGRADE_DEFINITIONS_TL T
571 where not exists
572 (select NULL
573 from PAY_UPGRADE_DEFINITIONS B
574 where B.UPGRADE_DEFINITION_ID = T.UPGRADE_DEFINITION_ID
575 );
576
577 update PAY_UPGRADE_DEFINITIONS_TL T set (
578 NAME,
579 DESCRIPTION
580 ) = (select
581 B.NAME,
582 B.DESCRIPTION
583 from PAY_UPGRADE_DEFINITIONS_TL B
584 where B.UPGRADE_DEFINITION_ID = T.UPGRADE_DEFINITION_ID
585 and B.LANGUAGE = T.SOURCE_LANG)
586 where (
587 T.UPGRADE_DEFINITION_ID,
588 T.LANGUAGE
589 ) in (select
590 SUBT.UPGRADE_DEFINITION_ID,
591 SUBT.LANGUAGE
592 from PAY_UPGRADE_DEFINITIONS_TL SUBB, PAY_UPGRADE_DEFINITIONS_TL SUBT
593 where SUBB.UPGRADE_DEFINITION_ID = SUBT.UPGRADE_DEFINITION_ID
594 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
595 and (SUBB.NAME <> SUBT.NAME
596 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
597 ));
598
599 insert into PAY_UPGRADE_DEFINITIONS_TL (
600 DESCRIPTION,
601 LAST_UPDATE_DATE,
602 LAST_UPDATED_BY,
603 LAST_UPDATE_LOGIN,
604 CREATED_BY,
605 CREATION_DATE,
606 UPGRADE_DEFINITION_ID,
607 NAME,
608 LANGUAGE,
609 SOURCE_LANG
610 ) select /*+ ORDERED */
611 B.DESCRIPTION,
612 B.LAST_UPDATE_DATE,
613 B.LAST_UPDATED_BY,
614 B.LAST_UPDATE_LOGIN,
615 B.CREATED_BY,
616 B.CREATION_DATE,
617 B.UPGRADE_DEFINITION_ID,
618 B.NAME,
619 L.LANGUAGE_CODE,
620 B.SOURCE_LANG
621 from PAY_UPGRADE_DEFINITIONS_TL B, FND_LANGUAGES L
622 where L.INSTALLED_FLAG in ('I', 'B')
623 and B.LANGUAGE = userenv('LANG')
624 and not exists
625 (select NULL
626 from PAY_UPGRADE_DEFINITIONS_TL T
627 where T.UPGRADE_DEFINITION_ID = B.UPGRADE_DEFINITION_ID
628 and T.LANGUAGE = L.LANGUAGE_CODE);
629 --
630 END ADD_LANGUAGE;
631
632 PROCEDURE Load_Row (
633 P_SHORT_NAME in VARCHAR2
634 , P_NAME in VARCHAR2
635 , P_DESCRIPTION in VARCHAR2
636 , P_LEGISLATION_CODE in VARCHAR2
637 , P_UPGRADE_LEVEL in VARCHAR2
638 , P_CRITICALITY in VARCHAR2
639 , P_THREADING_LEVEL in VARCHAR2
640 , P_FAILURE_POINT in VARCHAR2
641 , P_LEGISLATIVELY_ENABLED in VARCHAR2
642 , P_UPGRADE_PROCEDURE in VARCHAR2
643 , P_UPGRADE_METHOD in VARCHAR2
644 , P_QUALIFYING_PROCEDURE in VARCHAR2
645 , P_OWNER_APPL_SHORT_NAME in VARCHAR2
646 , P_FIRST_PATCHSET in VARCHAR2
647 , P_VALIDATE_CODE in VARCHAR2
648 , P_ADDITIONAL_INFO in VARCHAR2
649 , P_OWNER in VARCHAR2
650 ) is
651 --
652 l_proc varchar2(100) := g_package || 'Load_Row';
653 l_upgrade_definition_id PAY_UPGRADE_DEFINITIONS.UPGRADE_DEFINITION_ID%TYPE;
654
655 l_sysdate date := sysdate;
656 l_created_by PAY_UPGRADE_DEFINITIONS.CREATED_BY%TYPE;
657 l_creation_date PAY_UPGRADE_DEFINITIONS.CREATION_DATE%TYPE;
658 l_last_updated_by PAY_UPGRADE_DEFINITIONS.LAST_UPDATED_BY%TYPE;
659 l_last_update_login PAY_UPGRADE_DEFINITIONS.LAST_UPDATE_LOGIN%TYPE;
660 l_last_update_date PAY_UPGRADE_DEFINITIONS.LAST_UPDATE_DATE%TYPE;
661
662 cursor csr_existing is
663 select upgrade_definition_id
664 from pay_upgrade_definitions
665 where upper(short_name) = upper(p_short_name) ;
666
667 CURSOR C_APPL IS
668 select application_id
669 from fnd_application
670 where upper(application_short_name) = upper(P_OWNER_APPL_SHORT_NAME);
671
672 l_appl_id PAY_UPGRADE_DEFINITIONS.OWNER_APPLICATION_ID%type;
673 --
674 BEGIN
675 --
676 hr_utility.set_location('Entering:'|| l_proc, 10);
677
678 if p_owner = 'SEED' then
679 hr_general2.init_fndload
680 (p_resp_appl_id => 801
681 ,p_user_id => 1
682 );
683 else
684 hr_general2.init_fndload
685 (p_resp_appl_id => 801
686 ,p_user_id => -1
687 );
688 end if;
689
690 -- Set the WHO Columns
691 l_created_by := fnd_global.user_id;
692 l_creation_date := l_sysdate;
693 l_last_update_date := l_sysdate;
694 l_last_updated_by := fnd_global.user_id;
695 l_last_update_login := fnd_global.login_id;
696
697 IF P_OWNER_APPL_SHORT_NAME IS NOT NULL THEN
698 OPEN C_APPL;
699 FETCH C_APPL INTO l_appl_id;
700
701 IF C_APPL%NOTFOUND THEN
702 CLOSE C_APPL;
703 fnd_message.set_name('FND', 'AFDICT- ARG APPL');
704 fnd_message.set_token('APPL', P_OWNER_APPL_SHORT_NAME);
705 fnd_message.raise_error;
706 END IF;
707 CLOSE C_APPL;
708 END IF;
709
710 open csr_existing;
711 fetch csr_existing into l_upgrade_definition_id;
712
713 if csr_existing%FOUND then
714
715 close csr_existing;
716 Update_Row (
717 P_UPGRADE_DEFINITION_ID => l_upgrade_definition_id
718 , P_CRITICALITY => p_criticality
719 , P_FAILURE_POINT => p_failure_point
720 , P_UPGRADE_PROCEDURE => p_upgrade_procedure
721 , P_DESCRIPTION => p_description
722 , P_QUALIFYING_PROCEDURE => p_qualifying_procedure
723 , P_OWNER_APPL_ID => l_appl_id
724 , P_FIRST_PATCHSET => p_first_patchset
725 , P_VALIDATE_CODE => p_validate_code
726 , P_ADDITIONAL_INFO => p_additional_info
727 , P_LAST_UPDATE_DATE => l_last_update_date
728 , P_LAST_UPDATED_BY => l_last_updated_by
729 , P_LAST_UPDATE_LOGIN => l_last_update_login
730 );
731
732 else
733
734 close csr_existing;
735 -- This is not an update . Call the insert_row procedure.
736 --
737
738 Insert_Row (
739 P_SHORT_NAME => p_short_name
740 ,P_NAME => p_name
741 ,P_DESCRIPTION => p_description
742 ,P_LEGISLATION_CODE => p_legislation_code
743 ,P_UPGRADE_LEVEL => p_upgrade_level
744 ,P_CRITICALITY => p_criticality
745 ,P_FAILURE_POINT => p_failure_point
746 ,P_LEGISLATIVELY_ENABLED => p_legislatively_enabled
747 ,P_UPGRADE_PROCEDURE => p_upgrade_procedure
748 ,P_THREADING_LEVEL => p_threading_level
749 ,P_UPGRADE_METHOD => p_upgrade_method
750 ,P_QUALIFYING_PROCEDURE => p_qualifying_procedure
751 ,P_OWNER_APPL_ID => l_appl_id
752 ,P_FIRST_PATCHSET => p_first_patchset
753 ,P_VALIDATE_CODE => p_validate_code
754 ,P_ADDITIONAL_INFO => p_additional_info
755 ,P_LAST_UPDATE_DATE => l_last_update_date
756 ,P_LAST_UPDATED_BY => l_last_updated_by
757 ,P_LAST_UPDATE_LOGIN => l_last_update_login
758 ,P_CREATED_BY => l_created_by
759 ,P_CREATION_DATE => l_creation_date
760 ,P_UPGRADE_DEFINITION_ID => l_upgrade_definition_id
761 );
762
763 end if;
764
765 hr_utility.set_location('Leaving:'|| l_proc, 20);
766 --
767 END Load_Row;
768
769 PROCEDURE Translate_Row (
770 P_SHORT_NAME in VARCHAR2
771 , P_NAME in VARCHAR2
772 , P_DESCRIPTION in VARCHAR2
773 , P_ADDITIONAL_INFO in varchar2
774 , P_OWNER in VARCHAR2
775 ) is
776 --
777 l_proc varchar2(100) := g_package || 'Translate_Row';
778 l_last_updated_by number;
779 l_last_update_login number;
780 l_last_update_date date;
781 --
782 BEGIN
783 --
784 hr_utility.set_location('Entering:'|| l_proc, 10);
785
786 if P_OWNER = 'SEED' then
787 hr_general2.init_fndload
788 (p_resp_appl_id => 801
789 ,p_user_id => 1
790 );
791 else
792 hr_general2.init_fndload
793 (p_resp_appl_id => 801
794 ,p_user_id => -1
795 );
796 end if;
797 --
798 l_last_updated_by := fnd_global.user_id;
799 l_last_update_login := fnd_global.login_id;
800 l_last_update_date := sysdate;
801 --
802
803 UPDATE pay_upgrade_definitions_tl
804 SET name = nvl(p_name,name),
805 description = nvl(p_description,description),
806 additional_info = nvl(p_additional_info, additional_info),
807 last_update_date = l_last_update_date,
808 last_updated_by = l_last_updated_by,
809 last_update_login = l_last_update_login,
810 source_lang = userenv('LANG')
811 WHERE userenv('LANG') IN (language,source_lang)
812 AND upgrade_definition_id in
813 (SELECT pud.upgrade_definition_id
814 FROM pay_upgrade_definitions pud
815 WHERE upper(p_short_name) = upper(pud.short_name) );
816
817 hr_utility.set_location('Leaving:'|| l_proc, 20);
818 --
819 END Translate_Row;
820
821 END PAY_UPGRADE_DEFINITIONS_PKG;
822