[Home] [Help]
PACKAGE BODY: APPS.HR_REGISTER_EITS
Source
1 PACKAGE BODY HR_REGISTER_EITS AS
2 /* $Header: peregeit.pkb 120.5 2006/08/09 20:32:24 nsanghal noship $ */
3
4 PROCEDURE create_eit(
5 errbuf OUT nocopy VARCHAR2,
6 retcode OUT nocopy VARCHAR2,
7 p_table_name IN varchar2,
8 p_info_type_name IN varchar2,
9 p_active_flag IN varchar2,
10 p_multi_row IN varchar2,
11 p_leg_code IN varchar2 default null,
12 p_desc IN varchar2,
13 p_org_class IN varchar2,
14 p_category_code IN varchar2 default null,
15 p_sub_category_code IN varchar2 default null,
16 p_authorization_required IN varchar2 default null,
17 p_warning_period IN number default null,
18 p_application_id IN NUMBER default null
19 )
20 IS
21 v_table_name varchar2(30) := upper(p_table_name);
22 v_info_type_name varchar2(30) := p_info_type_name;
23 v_active_flag varchar2(1) := upper(p_active_flag);
24 v_multi_row varchar2(1) := upper(p_multi_row);
25 v_desc varchar2(240) := p_desc;
26 v_leg_code varchar2(4) := upper(p_leg_code);
27 v_org_class varchar2(30) := upper(p_org_class);
28
29 l_info_type varchar2(80);
30 l_info_count number;
31 l_insert varchar2(2000):= 'The Information Type: ' || v_info_type_name || ' has been inserted into the table: ' || v_table_name;
32 l_fail varchar2(2000):= 'The Information Type: ' || v_info_type_name || ' already exists in the table: ' || v_table_name;
33 --Added
34 l_show_error varchar2(2000);
35 --
36
37 /* 4197450 Start of Fix */
38
39 l_created_by number := fnd_profile.value('USER_ID');
40 l_updated_by number := fnd_profile.value('USER_ID');
41 l_update_login number := fnd_profile.value('USER_ID');
42 l_creation_date date := trunc(sysdate);
43 l_update_date date := trunc(sysdate);
44 l_navig_method varchar2(5);
45
46 /* 4197450 End of Fix */
47
48 /* START Added for Documents Of Record */
49
50 l_category_code varchar2(30) := p_category_code;
51 l_sub_category_code varchar2(30) := p_sub_category_code;
52 l_authorization_required varchar2(10) := upper(p_authorization_required);
53 l_warning_period number := p_warning_period;
54 l_request_id number;
55 l_program_application_id number;
56 l_program_id number;
57 l_ovn number;
58 l_doc_type_id number;
59
60
61 /* END Added for Documents Of Record */
62 --Added
63 l_application_id number := p_application_id;
64 --
65
66
67 begin
68
69 if v_table_name = 'PER_PEOPLE_INFO_TYPES' then
70
71 INSERT INTO PER_PEOPLE_INFO_TYPES
72 (INFORMATION_TYPE
73 ,ACTIVE_INACTIVE_FLAG
74 ,MULTIPLE_OCCURENCES_FLAG
75 ,DESCRIPTION
76 ,LEGISLATION_CODE
77 ,OBJECT_VERSION_NUMBER)
78 VALUES
79 (v_info_type_name
80 ,v_active_flag
81 ,v_multi_row
82 ,v_desc
83 ,v_leg_code
84 ,1);
85
86 fnd_file.put_line(fnd_file.log,l_insert);
87
88
89 --
90
91 elsif v_table_name = 'PER_ASSIGNMENT_INFO_TYPES' then
92 --
93 INSERT INTO PER_ASSIGNMENT_INFO_TYPES
94 (INFORMATION_TYPE
95 ,ACTIVE_INACTIVE_FLAG
96 ,MULTIPLE_OCCURENCES_FLAG
97 ,DESCRIPTION
98 ,LEGISLATION_CODE
99 ,OBJECT_VERSION_NUMBER)
100 VALUES
101 (v_info_type_name
102 ,v_active_flag
103 ,v_multi_row
104 ,v_desc
105 ,v_leg_code
106 ,1);
107 --
108 fnd_file.put_line(fnd_file.log,l_insert);
109
110 insert into PER_ASSIGNMENT_INFO_TYPES_TL
111 (INFORMATION_TYPE
112 ,LANGUAGE
113 ,SOURCE_LANG
114 ,DESCRIPTION
115 ,LAST_UPDATE_DATE
116 ,LAST_UPDATED_BY
117 ,LAST_UPDATE_LOGIN
118 ,CREATED_BY
119 ,CREATION_DATE
120 )
121 select M.INFORMATION_TYPE
122 ,L.LANGUAGE_CODE
123 ,B.LANGUAGE_CODE
124 ,M.DESCRIPTION
125 ,M.LAST_UPDATE_DATE
126 ,M.LAST_UPDATED_BY
127 ,M.LAST_UPDATE_LOGIN
128 ,M.CREATED_BY
129 ,M.CREATION_DATE
130 from PER_ASSIGNMENT_INFO_TYPES M
131 ,FND_LANGUAGES L
132 ,FND_LANGUAGES B
133 where M.INFORMATION_TYPE = v_info_type_name
134 and L.INSTALLED_FLAG in ('I', 'B')
135 and B.INSTALLED_FLAG = 'B'
136 and not exists (select '1'
137 from per_assignment_info_types_tl pait
138 where pait.information_type = m.information_type
139 and pait.language = l.language_code);
140 --
141 fnd_file.put_line(fnd_file.log,l_insert);
142
143
144 elsif v_table_name = 'PER_POSITION_INFO_TYPES' then
145 --
146 INSERT INTO PER_POSITION_INFO_TYPES
147 (INFORMATION_TYPE
148 ,ACTIVE_INACTIVE_FLAG
149 ,MULTIPLE_OCCURENCES_FLAG
150 ,DESCRIPTION
151 ,LEGISLATION_CODE
152 ,OBJECT_VERSION_NUMBER)
153 VALUES
154 (v_info_type_name
155 ,v_active_flag
156 ,v_multi_row
157 ,v_desc
158 ,v_leg_code
159 ,1);
160
161 fnd_file.put_line(fnd_file.log,l_insert);
162
163
164 elsif v_table_name = 'PQP_VEH_ALLOC_INFO_TYPES' then
165 --
166 INSERT INTO PQP_VEH_ALLOC_INFO_TYPES
167 (INFORMATION_TYPE
168 ,ACTIVE_INACTIVE_FLAG
169 ,MULTIPLE_OCCURENCES_FLAG
170 ,DESCRIPTION
171 ,LEGISLATION_CODE
172 ,OBJECT_VERSION_NUMBER)
173 VALUES
174 (v_info_type_name
175 ,v_active_flag
176 ,v_multi_row
177 ,v_desc
178 ,v_leg_code
179 ,1);
180
181 fnd_file.put_line(fnd_file.log,l_insert);
182
183
184 elsif v_table_name = 'PQP_VEH_REPOS_INFO_TYPES' then
185 --
186 INSERT INTO PQP_VEH_REPOS_INFO_TYPES
187 (INFORMATION_TYPE
188 ,ACTIVE_INACTIVE_FLAG
189 ,MULTIPLE_OCCURENCES_FLAG
190 ,DESCRIPTION
191 ,LEGISLATION_CODE
192 ,OBJECT_VERSION_NUMBER)
193 VALUES
194 (v_info_type_name
195 ,v_active_flag
196 ,v_multi_row
197 ,v_desc
198 ,v_leg_code
199 ,1);
200
201 fnd_file.put_line(fnd_file.log,l_insert);
202
203 elsif v_table_name = 'HR_LOCATION_INFO_TYPES' then
204 --
205 INSERT INTO HR_LOCATION_INFO_TYPES
206 (INFORMATION_TYPE
207 ,ACTIVE_INACTIVE_FLAG
208 ,MULTIPLE_OCCURENCES_FLAG
209 ,DESCRIPTION
210 ,LEGISLATION_CODE
211 ,OBJECT_VERSION_NUMBER)
212 VALUES
213 (v_info_type_name
214 ,v_active_flag
215 ,v_multi_row
216 ,v_desc
217 ,v_leg_code
218 ,1);
219
220 fnd_file.put_line(fnd_file.log,l_insert);
221 --
222 elsif v_table_name = 'PER_JOB_INFO_TYPES' then
223 --
224
225 INSERT INTO PER_JOB_INFO_TYPES
226 (INFORMATION_TYPE
227 ,ACTIVE_INACTIVE_FLAG
228 ,MULTIPLE_OCCURENCES_FLAG
229 ,DESCRIPTION
230 ,LEGISLATION_CODE
231 ,OBJECT_VERSION_NUMBER)
232 VALUES
233 (v_info_type_name
234 ,v_active_flag
235 ,v_multi_row
236 ,v_desc
237 ,v_leg_code
238 ,1);
239
240 fnd_file.put_line(fnd_file.log,l_insert);
241
242 elsif v_table_name = 'PER_CONTACT_INFO_TYPES' then
243 --
244
245 INSERT INTO PER_CONTACT_INFO_TYPES
246 (INFORMATION_TYPE
247 ,ACTIVE_INACTIVE_FLAG
248 ,MULTIPLE_OCCURENCES_FLAG
249 ,LEGISLATION_CODE
250 ,OBJECT_VERSION_NUMBER)
251 VALUES
252 (v_info_type_name
253 ,v_active_flag
254 ,v_multi_row
255 ,v_leg_code
256 ,1);
257
258 fnd_file.put_line(fnd_file.log,l_insert);
259 --
260 insert into PER_CONTACT_INFO_TYPES_TL
261 (INFORMATION_TYPE
262 , LANGUAGE
263 , SOURCE_LANG
264 , DESCRIPTION
265 , LAST_UPDATE_DATE
266 , LAST_UPDATED_BY
267 , LAST_UPDATE_LOGIN
268 , CREATED_BY
269 , CREATION_DATE
270 )
271 select M.INFORMATION_TYPE
272 , L.LANGUAGE_CODE
273 , B.LANGUAGE_CODE
274 , v_desc
275 , M.LAST_UPDATE_DATE
276 , M.LAST_UPDATED_BY
277 , M.LAST_UPDATE_LOGIN
278 , M.CREATED_BY
279 , M.CREATION_DATE
280 from PER_CONTACT_INFO_TYPES M
281 , FND_LANGUAGES L
282 , FND_LANGUAGES B
283 where M.INFORMATION_TYPE = v_info_type_name
284 and L.INSTALLED_FLAG in ('I', 'B')
285 and B.INSTALLED_FLAG = 'B'
286 and not exists ( select '1'
287 from per_contact_info_types_tl pcit
288 where pcit.information_type = m.information_type
289 and pcit.language = l.language_code);
290
291 fnd_file.put_line(fnd_file.log,l_insert);
292 --
293
294 elsif v_table_name = 'PER_PREV_JOB_INFO_TYPES' then
295 --
296
297 INSERT INTO PER_PREV_JOB_INFO_TYPES
298 (INFORMATION_TYPE
299 ,ACTIVE_INACTIVE_FLAG
300 ,MULTIPLE_OCCURANCES_FLAG
301 ,DESCRIPTION
302 ,LEGISLATION_CODE
303 ,OBJECT_VERSION_NUMBER)
304 VALUES
305 (v_info_type_name
306 ,v_active_flag
307 ,v_multi_row
308 ,v_desc
309 ,v_leg_code
310 ,1);
311
312 fnd_file.put_line(fnd_file.log,l_insert);
313
314 elsif v_table_name = 'PAY_ELEMENT_TYPE_INFO_TYPES' then
315 --
316 INSERT INTO PAY_ELEMENT_TYPE_INFO_TYPES
317 (INFORMATION_TYPE
318 ,ACTIVE_INACTIVE_FLAG
319 ,MULTIPLE_OCCURENCES_FLAG
320 ,DESCRIPTION
321 ,LEGISLATION_CODE
322 ,OBJECT_VERSION_NUMBER)
323 VALUES
324 (v_info_type_name
325 ,v_active_flag
326 ,v_multi_row
327 ,v_desc
328 ,v_leg_code
329 ,1);
330
331 fnd_file.put_line(fnd_file.log,l_insert);
332
333 /* Changes Added to support BEN EITs start here */
334
335 elsif v_table_name = 'BEN_OPT_INFO_TYPES' then
336 --
337 INSERT INTO BEN_OPT_INFO_TYPES
338 (INFORMATION_TYPE
339 ,ACTIVE_INACTIVE_FLAG
340 ,MULTIPLE_OCCURENCES_FLAG
341 ,DESCRIPTION
342 ,LEGISLATION_CODE
343 ,OBJECT_VERSION_NUMBER)
344 VALUES
345 (v_info_type_name
346 ,v_active_flag
347 ,v_multi_row
348 ,v_desc
349 ,v_leg_code
350 ,1);
351 fnd_file.put_line(fnd_file.log,l_insert);
352
353 --
354 elsif v_table_name = 'BEN_ABR_INFO_TYPES' then
355 --
356 INSERT INTO BEN_ABR_INFO_TYPES
357 (INFORMATION_TYPE
358 ,ACTIVE_INACTIVE_FLAG
359 ,MULTIPLE_OCCURENCES_FLAG
360 ,DESCRIPTION
361 ,LEGISLATION_CODE
362 ,OBJECT_VERSION_NUMBER)
363 VALUES
364 (v_info_type_name
365 ,v_active_flag
366 ,v_multi_row
367 ,v_desc
368 ,v_leg_code
369 ,1);
370 fnd_file.put_line(fnd_file.log,l_insert);
371 --
372 elsif v_table_name = 'BEN_PL_INFO_TYPES' then
373 --
374 INSERT INTO BEN_PL_INFO_TYPES
375 (INFORMATION_TYPE
376 ,ACTIVE_INACTIVE_FLAG
377 ,MULTIPLE_OCCURENCES_FLAG
378 ,DESCRIPTION
379 ,LEGISLATION_CODE
380 ,OBJECT_VERSION_NUMBER)
381 VALUES
382 (v_info_type_name
383 ,v_active_flag
384 ,v_multi_row
385 ,v_desc
386 ,v_leg_code
387 ,1);
388 fnd_file.put_line(fnd_file.log,l_insert);
389 --
390 elsif v_table_name = 'BEN_ELP_INFO_TYPES' then
391 --
392 INSERT INTO BEN_ELP_INFO_TYPES
393 (INFORMATION_TYPE
394 ,ACTIVE_INACTIVE_FLAG
395 ,MULTIPLE_OCCURENCES_FLAG
396 ,DESCRIPTION
397 ,LEGISLATION_CODE
398 ,OBJECT_VERSION_NUMBER)
399 VALUES
400 (v_info_type_name
401 ,v_active_flag
402 ,v_multi_row
403 ,v_desc
404 ,v_leg_code
405 ,1);
406 fnd_file.put_line(fnd_file.log,l_insert);
407 --
408 elsif v_table_name = 'BEN_LER_INFO_TYPES' then
409 --
410 INSERT INTO BEN_LER_INFO_TYPES
411 (INFORMATION_TYPE
412 ,ACTIVE_INACTIVE_FLAG
413 ,MULTIPLE_OCCURENCES_FLAG
414 ,DESCRIPTION
415 ,LEGISLATION_CODE
416 ,OBJECT_VERSION_NUMBER)
417 VALUES
418 (v_info_type_name
419 ,v_active_flag
420 ,v_multi_row
421 ,v_desc
422 ,v_leg_code
423 ,1);
424 fnd_file.put_line(fnd_file.log,l_insert);
425 --
426 elsif v_table_name = 'BEN_PGM_INFO_TYPES' then
427 --
428 INSERT INTO BEN_PGM_INFO_TYPES
429 (INFORMATION_TYPE
430 ,ACTIVE_INACTIVE_FLAG
431 ,MULTIPLE_OCCURENCES_FLAG
432 ,DESCRIPTION
433 ,LEGISLATION_CODE
434 ,OBJECT_VERSION_NUMBER)
435 VALUES
436 (v_info_type_name
437 ,v_active_flag
438 ,v_multi_row
439 ,v_desc
440 ,v_leg_code
441 ,1);
442 fnd_file.put_line(fnd_file.log,l_insert);
443 --
444
445 /* Changes Added to support BEN EITs end here */
446 /* 4197450 Start of Fix */
447 elsif v_table_name = 'HR_ORG_INFORMATION_TYPES' then
448
449 if v_multi_row = 'N' then
450 l_navig_method := 'GS';
451 else
452 l_navig_method := 'GM';
453 end if;
454
455 if v_desc is NULL then
456 v_desc := v_info_type_name;
457 end if;
458
459 if p_application_id is not null then
460 begin
461 begin
462 hr_org_information_types_pkg.insert_row
463 (v_info_type_name
464 ,null
465 ,v_leg_code
466 ,l_navig_method
467 ,l_application_id
468 ,v_desc
469 ,v_desc
470 ,l_creation_date
471 ,l_created_by
472 ,l_update_date
473 ,l_updated_by
474 ,l_update_login);
475
476 fnd_file.put_line(fnd_file.log,l_insert);
477 EXCEPTION
478 WHEN OTHERS THEN
479 fnd_file.put_line(fnd_file.log,l_fail);
480 end;
481
482 INSERT INTO HR_ORG_INFO_TYPES_BY_CLASS
483 (ORG_CLASSIFICATION
484 ,ORG_INFORMATION_TYPE
485 ,MANDATORY_FLAG
486 ,ENABLED_FLAG )
487 SELECT
488 v_org_class
489 ,v_info_type_name
490 ,'N'
491 ,'Y'
492 FROM sys.dual
493 WHERE not exists (SELECT 1
494 FROM HR_ORG_INFO_TYPES_BY_CLASS
495 WHERE ORG_INFORMATION_TYPE =v_info_type_name
496 and ORG_CLASSIFICATION = v_org_class);
497
498 l_insert := 'The Information has been inserted into the table HR_ORG_INFO_TYPES_BY_CLASS for Organization Classification ' || v_org_class;
499
500 fnd_file.put_line(fnd_file.log,l_insert);
501 EXCEPTION
502 WHEN OTHERS THEN
503 fnd_file.put_line(fnd_file.log,l_fail);
504 end;
505
506 else
507 --The following has been added to return error if application id is not entered.
508 retcode := '2';
509 l_show_error:= 'Please enter the mandatory application for HR_ORG_INFORMATION_TYPES';
510 fnd_file.put_line(fnd_file.log,l_show_error);
511 --
512 end if;
513
514
515 /* 4197450 End of Fix */
516
517
518 /* START Added elsif block for Documents Of Record */
519
520 elsif v_table_name = 'HR_DOCUMENT_TYPES' then
521
522 begin
523
524 l_request_id := fnd_global.conc_request_id;
525 l_program_application_id := fnd_global.prog_appl_id;
526 l_program_id := fnd_global.conc_program_id;
527 l_insert := 'The Document Type : "' || p_info_type_name || '" has been created';
528 l_fail := 'Error while creating document type : "' || p_info_type_name || '"';
529
530 hr_document_types_api.create_document_type
531 (
532 p_description => v_desc
533 ,p_document_type => v_info_type_name
534 ,p_category_code => l_category_code
535 ,p_active_inactive_flag => v_active_flag
536 ,p_multiple_occurences_flag => v_multi_row
537 ,p_authorization_required => l_authorization_required
538 ,p_sub_category_code => l_sub_category_code
539 ,p_legislation_code => v_leg_code
540 ,p_warning_period => l_warning_period
541 ,p_program_application_id => l_program_application_id
542 ,p_program_id => l_program_id
543 ,p_request_id => l_request_id
544 ,p_document_type_id => l_doc_type_id
545 ,p_object_version_number => l_ovn
546 );
547
548 fnd_file.put_line(fnd_file.log,l_insert);
549 exception
550 when others then
551 retcode:='1';
552 fnd_file.put_line(fnd_file.log,l_fail);
553 fnd_file.put_line(fnd_file.log,sqlerrm);
554
555 end;
556
557
558 /* END Added elsif block for Documents Of Record */
559 else
560
561 fnd_file.put_line(fnd_file.log,'Error - user entered invalid or unsupported table name');
562
563 raise VALUE_ERROR;
564
565 end if;
566
567 EXCEPTION
568
569 WHEN OTHERS THEN
570
571 fnd_file.put_line(fnd_file.log,l_fail);
572 --
573 END;
574
575 end;