[Home] [Help]
PACKAGE BODY: APPS.PER_EMPDIR_SS
Source
1 PACKAGE BODY PER_EMPDIR_SS AS
2 /* $Header: peredrcp.pkb 120.15.12020000.2 2012/07/05 04:30:42 amnaraya ship $ */
3
4 -- Global Variables
5 TYPE cur_typ IS REF CURSOR;
6 g_hz_api_api_version CONSTANT Number:=1.0;
7 g_srcSystem VARCHAR2(30);
8 g_oracle_db_version CONSTANT NUMBER := hr_general2.get_oracle_db_version;
9 g_schema_owner VARCHAR2(30);
10
11 CURSOR c_organizations IS
12 SELECT o.organization_id, nvl(upper(replace(oi.org_information1,'|','||''.''||')),'j.name') slist
13 FROM hr_all_organization_units o, hr_organization_information oi
14 WHERE o.organization_id = o.business_group_id
15 AND o.organization_id = oi.organization_id (+)
16 AND oi.org_information_context(+) = 'SSHR Information';
17
18 -- Local Members
19
20 PROCEDURE write_log(
21 p_fpt IN NUMBER
22 ,p_msg IN VARCHAR2) IS
23
24 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
25
26 BEGIN
27 -- p_fpt (1,2)?(log : output)
28 FND_FILE.put(p_fpt, p_msg);
29 FND_FILE.NEW_LINE(p_fpt, 1);
30 -- If p_fpt == 2 and debug flag then also write to log file
31 IF p_fpt = 2 AND l_debug THEN
32 FND_FILE.put(1, p_msg);
33 FND_FILE.NEW_LINE(1, 1);
34 END IF;
35 --dbms_output.put_line(p_msg);
36 EXCEPTION
37 WHEN OTHERS THEN
38 NULL;
39 END write_log;
40
41 FUNCTION getTblOwner RETURN VARCHAR2 IS
42 l_status VARCHAR2(100) := '';
43 l_industry VARCHAR2(100) := '';
44 l_result BOOLEAN;
45 l_schema_owner VARCHAR2(10) := '';
46 BEGIN
47 l_result := FND_INSTALLATION.GET_APP_INFO(
48 'PER',
49 l_status,
50 l_industry,
51 l_schema_owner);
52
53 IF l_result THEN
54 RETURN l_schema_owner;
55 ELSE
56 write_log(1, 'Error in getTblOwner: '||SQLCODE);
57 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
58 RETURN 'HR';
59 END IF;
60 END getTblOwner;
61
62 PROCEDURE trace(
63 p_enable BOOLEAN) IS
64
65 ddl_curs integer;
66 v_Dummy integer;
67 BEGIN
68 ddl_curs := dbms_sql.open_cursor;
69 IF (p_enable) then
70 dbms_sql.parse(ddl_curs,'ALTER SESSION SET sql_trace = TRUE',dbms_sql.native);
71 ELSE
72 dbms_sql.parse(ddl_curs,'ALTER SESSION SET sql_trace = FALSE',dbms_sql.native);
73 END IF;
74 v_Dummy := DBMS_SQL.EXECUTE(ddl_curs);
75 dbms_sql.close_cursor(ddl_curs);
76 EXCEPTION WHEN OTHERS THEN
77 NULL;
78 END trace;
79
80 PROCEDURE gather_stats IS
81 BEGIN
82
83 write_log(1, 'Begin gathering stats: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
84
85 fnd_stats.gather_table_stats(g_schema_owner,'PER_EMPDIR_PEOPLE');
86 fnd_stats.gather_table_stats(g_schema_owner,'PER_EMPDIR_ASSIGNMENTS');
87 fnd_stats.gather_table_stats(g_schema_owner,'PER_EMPDIR_ORGANIZATIONS');
88 fnd_stats.gather_table_stats(g_schema_owner,'PER_EMPDIR_JOBS');
89 fnd_stats.gather_table_stats(g_schema_owner,'PER_EMPDIR_POSITIONS');
90 fnd_stats.gather_table_stats(g_schema_owner,'PER_EMPDIR_PHONES');
91 fnd_stats.gather_table_stats(g_schema_owner,'PER_EMPDIR_LOCATIONS');
92 fnd_stats.gather_table_stats(g_schema_owner,'PER_EMPDIR_LOCATIONS_TL');
93 fnd_stats.gather_table_stats(g_schema_owner,'PER_EMPDIR_IMAGES');
94
95 write_log(1, 'End gathering stats: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
96
97 EXCEPTION WHEN OTHERS THEN
98 NULL;
99 END gather_stats;
100
101
102 PROCEDURE dump_totals(
103 errbuf OUT NOCOPY VARCHAR2
104 ,retcode OUT NOCOPY VARCHAR2
105 ,p_cnt IN NUMBER
106 ) IS
107 BEGIN
108
109 FOR I IN 1 .. p_cnt LOOP
110 BEGIN
111 SELECT count(unique a.person_id)-1 INTO cntTbl.cnt(I)
112 FROM per_empdir_assignments a,
113 per_empdir_people p
114 WHERE a.orig_system = cntTbl.orig_system(I)
115 AND a.active = 'Y'
116 and a.PERSON_ID = p.orig_system_ID
117 and p.active = 'Y'
118 CONNECT BY PRIOR a.person_id = a.supervisor_id
119 AND a.orig_system = cntTbl.orig_system(I)
120 START WITH a.person_id = cntTbl.orig_system_id(I)
121 AND a.active = 'Y'
122 AND a.primary_flag = 'Y'
123 AND a.orig_system = cntTbl.orig_system(I);
124
125 EXCEPTION WHEN OTHERS THEN
126 NULL;
127 END;
128 END LOOP;
129
130 FORALL I IN 1 .. p_cnt
131 UPDATE per_empdir_people
132 SET total_reports = cntTbl.cnt(I)
133 WHERE rowid = cntTbl.row_id(I);
134
135 EXCEPTION WHEN OTHERS THEN
136 errbuf := errbuf||SQLERRM;
137 retcode := '1';
138 write_log(1, 'Error in dump_totals: '||SQLCODE);
139 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
140 END dump_totals;
141
142 PROCEDURE compute_reports(
143 errbuf OUT NOCOPY VARCHAR2
144 ,retcode OUT NOCOPY VARCHAR2
145 ,p_source_system IN VARCHAR2
146 ) IS
147
148 CURSOR people IS
149 SELECT rowid
150 ,orig_system
151 ,orig_system_id
152 ,null cnt
153 FROM per_empdir_people p
154 WHERE active = 'Y'
155 AND p.orig_system = p_source_system;
156
157 BEGIN
158
159
160 write_log(1, 'Begin Compute Directs: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
161
162 UPDATE per_empdir_people p
163 SET direct_reports = (SELECT count(*)
164 FROM per_empdir_assignments a, per_empdir_people rp
165 WHERE supervisor_id = p.orig_system_id
166 AND a.orig_system = p.orig_system
167 AND a.active = 'Y'
168 -- AND a.primary_flag = 'Y'
169 AND a.person_id = rp.orig_system_id
170 AND a.orig_system = rp.orig_system
171 AND rp.active = 'Y')
172 WHERE p.orig_system = p_source_system;
173
174 COMMIT;
175
176 write_log(1, 'End Compute Directs: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
177 write_log(1, 'Begin Compute Totals: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
178
179 OPEN people; LOOP
180 BEGIN
181 FETCH people BULK COLLECT
182 INTO cntTbl.row_id
183 ,cntTbl.orig_system
184 ,cntTbl.orig_system_id
185 ,cntTbl.cnt LIMIT g_commit_size;
186
187 IF cntTbl.row_id.count <= 0 THEN
188 CLOSE people;
189 EXIT;
190 END IF;
191
192 dump_totals(
193 errbuf
194 ,retcode
195 ,cntTbl.row_id.count
196 );
197
198 COMMIT;
199
200 IF people%NOTFOUND THEN
201 CLOSE people;
202 EXIT;
203 END IF;
204 END;
205 END LOOP;
206 COMMIT;
207
208 write_log(1, 'End Compute Totals: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
209
210 EXCEPTION WHEN OTHERS THEN
211 errbuf := errbuf||SQLERRM;
212 retcode := '1';
213 write_log(1, 'Error in compute_reports: '||SQLCODE);
214 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
215 END compute_reports;
216
217 PROCEDURE dump_per_jobs(
218 errbuf OUT NOCOPY VARCHAR2
219 ,retcode OUT NOCOPY VARCHAR2
220 ,p_cnt IN NUMBER
221 ) IS
222 BEGIN
223 g_date := trunc(SYSDATE);
224
225 FORALL I IN 1 .. p_cnt
226
227 insert INTO per_empdir_jobs (ORIG_SYSTEM,
228 ORIG_SYSTEM_ID,
229 BUSINESS_GROUP_ID,
230 JOB_DEFINITION_ID,
231 NAME ,
232 LANGUAGE,
233 SOURCE_LANG ,
234 OBJECT_VERSION_NUMBER,
235 PARTITION_ID,
236 LAST_UPDATE_DATE,
237 LAST_UPDATE_BY,
238 CREATED_BY,
239 CREATION_DATE,
240 REQUEST_ID,
241 PROGRAM_APPLICATION_ID,
242 PROGRAM_ID,
243 PROGRAM_UPDATE_DATE,
244 ATTRIBUTE_CATEGORY,
245 ATTRIBUTE1,
246 ATTRIBUTE2,
247 ATTRIBUTE3,
248 ATTRIBUTE4,
249 ATTRIBUTE5,
250 ATTRIBUTE6,
251 ATTRIBUTE7,
252 ATTRIBUTE8,
253 ATTRIBUTE9,
254 ATTRIBUTE10,
255 ATTRIBUTE11,
256 ATTRIBUTE12,
257 ATTRIBUTE13,
258 ATTRIBUTE14,
259 ATTRIBUTE15,
260 ATTRIBUTE16,
261 ATTRIBUTE17,
262 ATTRIBUTE18,
263 ATTRIBUTE19,
264 ATTRIBUTE20) values(
265 jobTbl.orig_system(I)
266 ,jobTbl.orig_system_id(I)
267 ,jobTbl.business_group_id(I)
268 ,jobTbl.job_definition_id(I)
269 ,jobTbl.name(I)
270 ,jobTbl.language(I)
271 ,jobTbl.source_language(I)
272 ,jobTbl.object_version_number(I)
273 ,jobTbl.partition_id(I)
274 ,g_date
275 ,g_user_id
276 ,g_user_id
277 ,g_date
278 ,g_request_id
279 ,g_prog_appl_id
280 ,g_prog_id
281 ,g_date
282 ,jobTbl.attribute_category(I)
283 ,jobTbl.attribute1(I)
284 ,jobTbl.attribute2(I)
285 ,jobTbl.attribute3(I)
286 ,jobTbl.attribute4(I)
287 ,jobTbl.attribute5(I)
288 ,jobTbl.attribute6(I)
289 ,jobTbl.attribute7(I)
290 ,jobTbl.attribute8(I)
291 ,jobTbl.attribute9(I)
292 ,jobTbl.attribute10(I)
293 ,jobTbl.attribute11(I)
294 ,jobTbl.attribute12(I)
295 ,jobTbl.attribute13(I)
296 ,jobTbl.attribute14(I)
297 ,jobTbl.attribute15(I)
298 ,jobTbl.attribute16(I)
299 ,jobTbl.attribute17(I)
300 ,jobTbl.attribute18(I)
301 ,jobTbl.attribute19(I)
302 ,jobTbl.attribute20(I)
303 );
304
305 EXCEPTION WHEN OTHERS THEN
306 errbuf := errbuf||SQLERRM;
307 retcode := '1';
308 write_log(1, 'Error in dump_per_jobs: '||SQLCODE);
309 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
310 END dump_per_jobs;
311
312 PROCEDURE update_hr_pos(
313 errbuf OUT NOCOPY VARCHAR2
314 ,retcode OUT NOCOPY VARCHAR2
315 ,p_eff_date IN DATE
316 ,p_cnt IN NUMBER
317 ) IS
318 BEGIN
319
320 g_date := trunc(SYSDATE);
321
322 FORALL I IN 1 .. p_cnt
323 UPDATE per_empdir_positions
324 SET orig_system = posTbl.orig_system(I)
325 ,orig_system_id = posTbl.orig_system_id(I)
326 ,business_group_id = posTbl.business_group_id(I)
327 ,job_id = posTbl.job_id(I)
328 ,location_id = posTbl.location_id(I)
329 ,organization_id = posTbl.organization_id(I)
330 ,position_definition_id = posTbl.position_definition_id(I)
331 ,name = posTbl.name(I)
332 ,language = posTbl.language(I)
333 ,source_lang = posTbl.source_language(I)
334 ,object_version_number = posTbl.object_version_number(I)
335 ,partition_id = posTbl.partition_id(I)
336 ,last_update_date = g_date
337 ,last_update_by = g_user_id
338 ,created_by = g_user_id
339 ,creation_date = g_date
340 ,request_id = g_request_id
341 ,program_application_id = g_prog_appl_id
342 ,program_id = g_prog_id
343 ,program_update_date = g_date
344 ,attribute_category = posTbl.attribute_category(I)
345 ,attribute1 = posTbl.attribute1(I)
346 ,attribute2 = posTbl.attribute2(I)
347 ,attribute3 = posTbl.attribute3(I)
348 ,attribute4 = posTbl.attribute4(I)
349 ,attribute5 = posTbl.attribute5(I)
350 ,attribute6 = posTbl.attribute6(I)
351 ,attribute7 = posTbl.attribute7(I)
352 ,attribute8 = posTbl.attribute8(I)
353 ,attribute9 = posTbl.attribute9(I)
354 ,attribute10 = posTbl.attribute10(I)
355 ,attribute11 = posTbl.attribute11(I)
356 ,attribute12 = posTbl.attribute12(I)
357 ,attribute13 = posTbl.attribute13(I)
358 ,attribute14 = posTbl.attribute14(I)
359 ,attribute15 = posTbl.attribute15(I)
360 ,attribute16 = posTbl.attribute16(I)
361 ,attribute17 = posTbl.attribute17(I)
362 ,attribute18 = posTbl.attribute18(I)
363 ,attribute19 = posTbl.attribute19(I)
364 ,attribute20 = posTbl.attribute20(I)
365 WHERE orig_system = posTbl.orig_system(I)
366 AND orig_system_id = posTbl.orig_system_id(I)
367 AND language = posTbl.language(I);
368
369 EXCEPTION WHEN OTHERS THEN
370 errbuf := errbuf||SQLERRM;
371 retcode := '1';
372 write_log(1, 'Error in update_hr_pos: '||SQLCODE);
373 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
374 END update_hr_pos;
375
376 PROCEDURE dump_hr_pos(
377 errbuf OUT NOCOPY VARCHAR2
378 ,retcode OUT NOCOPY VARCHAR2
379 ,p_eff_date IN DATE
380 ,p_cnt IN NUMBER
381 ) IS
382 BEGIN
383 g_date := trunc(SYSDATE);
384
385 FORALL I IN 1 .. p_cnt
386 INSERT INTO per_empdir_positions values (
387 posTbl.orig_system(I)
388 ,posTbl.orig_system_id(I)
389 ,posTbl.business_group_id(I)
390 ,posTbl.job_id(I)
391 ,posTbl.location_id(I)
392 ,posTbl.organization_id(I)
393 ,posTbl.position_definition_id(I)
394 ,posTbl.name(I)
395 ,posTbl.language(I)
396 ,posTbl.source_language(I)
397 ,posTbl.object_version_number(I)
398 ,posTbl.partition_id(I)
399 ,g_date
400 ,g_user_id
401 ,g_user_id
402 ,g_date
403 ,g_request_id
404 ,g_prog_appl_id
405 ,g_prog_id
406 ,g_date
407 ,posTbl.attribute_category(I)
408 ,posTbl.attribute1(I)
409 ,posTbl.attribute2(I)
410 ,posTbl.attribute3(I)
411 ,posTbl.attribute4(I)
412 ,posTbl.attribute5(I)
413 ,posTbl.attribute6(I)
414 ,posTbl.attribute7(I)
415 ,posTbl.attribute8(I)
416 ,posTbl.attribute9(I)
417 ,posTbl.attribute10(I)
418 ,posTbl.attribute11(I)
419 ,posTbl.attribute12(I)
420 ,posTbl.attribute13(I)
421 ,posTbl.attribute14(I)
422 ,posTbl.attribute15(I)
423 ,posTbl.attribute16(I)
424 ,posTbl.attribute17(I)
425 ,posTbl.attribute18(I)
426 ,posTbl.attribute19(I)
427 ,posTbl.attribute20(I)
428 );
429
430 EXCEPTION WHEN OTHERS THEN
431 errbuf := errbuf||SQLERRM;
432 retcode := '1';
433 write_log(1, 'Error in dump_hr_pos: '||SQLCODE);
434 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
435 END dump_hr_pos;
436
437 PROCEDURE open_hr_pos(
438 p_cursor IN OUT NOCOPY cur_typ
439 ,p_mode IN NUMBER
440 ,p_eff_date IN DATE
441 ) IS
442
443 query_str VARCHAR2(4000);
444
445 BEGIN
446 query_str := 'SELECT '''||g_srcSystem||''', p.position_id, p.business_group_id, oi.org_information9,' ||
447 'p.job_id, p.location_id,p.organization_id, p.position_definition_id, ptl.name,'||
448 'ptl.language, ptl.source_lang, p.object_version_number,1, p.attribute_category,'||
449 'p.attribute1, p.attribute2, p.attribute3, p.attribute4, p.attribute5,p.attribute6,'||
450 'p.attribute7, p.attribute8, p.attribute9, p.attribute10, p.attribute11, p.attribute12,'||
451 'p.attribute13, p.attribute14, p.attribute15, p.attribute16, p.attribute17, p.attribute18,'||
452 'p.attribute19,p.attribute20, p.attribute21, p.attribute22, p.attribute23, p.attribute24,'||
453 'p.attribute25, p.attribute26,p.attribute27, p.attribute28, p.attribute29, p.attribute30,'||
454 'information_category, information1, information2, information3, information4, information5,'||
455 'information6, information7, information8, information9, information10, information11,'||
456 'information12, information13, information14, information15, information16, information17,'||
457 'information18, information19, information20, information21, information22, information23,'||
458 'information24, information25, information26, information27, information28, information29,'||
459 'information30 '||
460 'FROM hr_all_positions_f p, hr_all_positions_f_tl ptl, hr_organization_information oi '||
461 'WHERE p.position_id = ptl.position_id'||
462 ' AND :1 between p.effective_start_date AND p.effective_end_date'||
463 ' AND p.business_group_id = oi.organization_id'||
464 ' AND oi.org_information_context = ''Business Group Information''';
465 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
466 query_str := query_str || ' AND p.position_id NOT IN '||
467 ' (SELECT lp.position_id FROM hr_all_positions_f lp'||
468 ' WHERE label_to_char(lp.HR_ENTERPRISE) = ''C::ENT'' OR label_to_char(lp.HR_ENTERPRISE) is null)';
469 END IF;
470 IF (p_mode = 0) THEN
471 OPEN p_cursor FOR query_str using p_eff_date;
472 ELSIF (p_mode = 1) THEN
473 query_str := query_str || ' AND EXISTS (SELECT ''e'' FROM per_empdir_positions ip'||
474 ' WHERE ip.orig_system_id = p.position_id'||
475 ' AND ip.orig_system = ''' || g_srcSystem || '''' ||
476 ' AND ip.object_version_number <> p.object_version_number)';
477 OPEN p_cursor FOR query_str using p_eff_date;
478 ELSIF (p_mode = 2) THEN
479 query_str := query_str || ' AND NOT EXISTS (SELECT ''e'' FROM per_empdir_positions ip'||
480 ' WHERE ip.orig_system_id = p.position_id'||
481 ' AND ip.orig_system = ''' || g_srcSystem || ''')';
482
483 OPEN p_cursor FOR query_str using p_eff_date;
484 END IF;
485 END open_hr_pos;
486
487 PROCEDURE bulk_process_hr_pos(
488 p_mode IN NUMBER
489 ,p_cnt OUT NOCOPY NUMBER
490 ,errbuf OUT NOCOPY VARCHAR2
491 ,retcode OUT NOCOPY VARCHAR2
492 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
493 ) IS
494
495 l_cursor cur_typ;
496 l_flg BOOLEAN:= FALSE;
497
498 BEGIN
499
500 p_cnt := 0;
501 l_flg := per_empdir_LEG_OVERRIDE.isOverrideEnabled('POSITIONS');
502
503 open_hr_pos(
504 l_cursor
505 ,p_mode
506 ,p_eff_date
507 );
508
509 LOOP
510 BEGIN
511 FETCH l_cursor BULK COLLECT INTO
512 posTbl.orig_system
513 ,posTbl.orig_system_id
514 ,posTbl.business_group_id
515 ,posTbl.legislation_code
516 ,posTbl.job_id
517 ,posTbl.location_id
518 ,posTbl.organization_id
519 ,posTbl.position_definition_id
520 ,posTbl.name
521 ,posTbl.language
522 ,posTbl.source_language
523 ,posTbl.object_version_number
524 ,posTbl.partition_id
525 ,posTbl.attribute_category
526 ,posTbl.attribute1
527 ,posTbl.attribute2
528 ,posTbl.attribute3
529 ,posTbl.attribute4
530 ,posTbl.attribute5
531 ,posTbl.attribute6
532 ,posTbl.attribute7
533 ,posTbl.attribute8
534 ,posTbl.attribute9
535 ,posTbl.attribute10
536 ,posTbl.attribute11
537 ,posTbl.attribute12
538 ,posTbl.attribute13
539 ,posTbl.attribute14
540 ,posTbl.attribute15
541 ,posTbl.attribute16
542 ,posTbl.attribute17
543 ,posTbl.attribute18
544 ,posTbl.attribute19
545 ,posTbl.attribute20
546 ,posTbl.attribute21
547 ,posTbl.attribute22
548 ,posTbl.attribute23
549 ,posTbl.attribute24
550 ,posTbl.attribute25
551 ,posTbl.attribute26
552 ,posTbl.attribute27
553 ,posTbl.attribute28
554 ,posTbl.attribute29
555 ,posTbl.attribute30
556 ,posTbl.information_category
557 ,posTbl.information1
558 ,posTbl.information2
559 ,posTbl.information3
560 ,posTbl.information4
561 ,posTbl.information5
562 ,posTbl.information6
563 ,posTbl.information7
564 ,posTbl.information8
565 ,posTbl.information9
566 ,posTbl.information10
567 ,posTbl.information11
568 ,posTbl.information12
569 ,posTbl.information13
570 ,posTbl.information14
571 ,posTbl.information15
572 ,posTbl.information16
573 ,posTbl.information17
574 ,posTbl.information18
575 ,posTbl.information19
576 ,posTbl.information20
577 ,posTbl.information21
578 ,posTbl.information22
579 ,posTbl.information23
580 ,posTbl.information24
581 ,posTbl.information25
582 ,posTbl.information26
583 ,posTbl.information27
584 ,posTbl.information28
585 ,posTbl.information29
586 ,posTbl.information30 LIMIT g_commit_size;
587
588 IF posTbl.orig_system.count <= 0 THEN
589 CLOSE l_cursor;
590 EXIT;
591 END IF;
592
593 p_cnt := p_cnt + posTbl.orig_system.count;
594
595 IF l_flg THEN
596 per_empdir_leg_override.positions(
597 errbuf => errbuf
598 ,retcode => retcode
599 ,p_eff_date => p_eff_date
600 ,p_cnt => posTbl.orig_system.count
601 ,p_srcsystem => g_srcSystem);
602 END IF;
603
604 IF (p_mode = '0' OR p_mode = '2') THEN
605 dump_hr_pos(
606 errbuf
607 ,retcode
608 ,p_eff_date
609 ,posTbl.orig_system.count
610 );
611 ElSIF (p_mode = '1') THEN
612 update_hr_pos(
613 errbuf
614 ,retcode
615 ,p_eff_date
616 ,posTbl.orig_system.count
617 );
618 END IF;
619
620 COMMIT;
621
622 IF l_cursor%NOTFOUND THEN
623 CLOSE l_cursor;
624 EXIT;
625 END IF;
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 errbuf := errbuf||SQLERRM;
630 retcode := '1';
631 write_log(1, 'Error in pos bulk collect: '||SQLCODE);
632 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
633 END;
634 END LOOP;
635
636 COMMIT;
637
638 EXCEPTION WHEN OTHERS THEN
639 errbuf := errbuf||SQLERRM;
640 retcode := '1';
641 write_log(1, 'Error in bulk_process_per_pos: '||SQLCODE);
642 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
643
644 END bulk_process_hr_pos;
645
646 PROCEDURE dump_per_locations(
647 errbuf OUT NOCOPY VARCHAR2
648 ,retcode OUT NOCOPY VARCHAR2
649 ,p_eff_date IN DATE
650 ,p_cnt IN NUMBER
651 ) IS
652 BEGIN
653 g_date := trunc(SYSDATE);
654
655 FORALL I IN 1 .. p_cnt
656 INSERT INTO per_empdir_locations values (
657 locationTbl.orig_system(I)
658 ,locationTbl.orig_system_id(I)
659 ,locationTbl.business_group_id(I)
660 ,locationTbl.derived_locale(I)
661 ,locationTbl.tax_name(I)
662 ,locationTbl.country(I)
663 ,locationTbl.style(I)
664 ,locationTbl.address(I)
665 ,locationTbl.address_line_1(I)
666 ,locationTbl.address_line_2(I)
667 ,locationTbl.address_line_3(I)
668 ,locationTbl.town_or_city(I)
669 ,locationTbl.region_1(I)
670 ,locationTbl.region_2(I)
671 ,locationTbl.region_3(I)
672 ,locationTbl.postal_code(I)
673 ,locationTbl.inactive_date(I)
674 ,locationTbl.office_site_flag(I)
675 ,locationTbl.receiving_site_flag(I)
676 ,locationTbl.telephone_number_1(I)
677 ,locationTbl.telephone_number_2(I)
678 ,locationTbl.telephone_number_3(I)
679 ,locationTbl.timezone_id(I)
680 ,locationTbl.object_version_number(I)
681 ,locationTbl.partition_id(I)
682 ,g_date
683 ,g_user_id
684 ,g_login_id
685 ,g_user_id
686 ,g_date
687 ,g_request_id
688 ,g_prog_appl_id
689 ,g_prog_id
690 ,g_date
691 ,locationTbl.timezone_code(I)
692 );
693
694 EXCEPTION WHEN OTHERS THEN
695 errbuf := errbuf||SQLERRM;
696 retcode := '1';
697 write_log(1, 'Error in dump_per_locations: '||SQLCODE);
698 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
699 END dump_per_locations;
700
701 PROCEDURE open_per_locations(
702 p_cursor IN OUT NOCOPY cur_typ
703 ,p_mode IN NUMBER
704 ,p_eff_date IN DATE
705 ) IS
706
707 query_str VARCHAR2(4000);
708
709 BEGIN
710 query_str := 'SELECT ''' || g_srcSystem || '''' ||
711 ' ,location_id, business_group_id, derived_locale,tax_name' ||
712 ' ,country, style, null address, address_line_1, address_line_2' ||
713 ' ,address_line_3, town_or_city, region_1, region_2, region_3' ||
714 ' ,postal_code, inactive_date, office_site_flag, receiving_site_flag' ||
715 ' ,telephone_number_1, telephone_number_2, telephone_number_3' ||
716 ' ,null' ||
717 ' ,nvl(TIMEZONE_CODE,' ||
718 ' per_empdir_SS.get_timezone_code(postal_code, town_or_city,' ||
719 ' decode(country,''US'',region_2,region_1), country))' ||
720 ' ,object_version_number' ||
721 ' ,1 ' ||
722 ' FROM hr_locations_all l';
723 IF (p_mode = 0) THEN
724 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
725 query_str := query_str || ' WHERE label_to_char(HR_ENTERPRISE) <> ''C::ENT''';
726 END IF;
727
728 OPEN p_cursor FOR query_str;
729 ELSIF (p_mode = 1) THEN
730 query_str := query_str || ' WHERE EXISTS (SELECT ''e'' FROM per_empdir_locations il' ||
731 ' WHERE il.orig_system_id = l.location_id'||
732 ' AND il.orig_system = ''' || g_srcSystem || '''' ||
733 ' AND il.object_version_number <> l.object_version_number)';
734 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
735 query_str := query_str || ' AND label_to_char(HR_ENTERPRISE) <> ''C::ENT''';
736 END IF;
737 OPEN p_cursor FOR query_str;
738 ELSIF (p_mode = 2) THEN
739 query_str := query_str || ' WHERE NOT EXISTS (SELECT ''e'' FROM per_empdir_locations il' ||
740 ' WHERE il.orig_system_id = l.location_id' ||
741 ' AND il.orig_system = ''' || g_srcSystem || ''')';
742 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
743 query_str := query_str || ' AND label_to_char(HR_ENTERPRISE) <> ''C::ENT''';
744 END IF;
745 OPEN p_cursor FOR query_str;
746 END IF;
747 END open_per_locations;
748
749 PROCEDURE update_per_locations(
750 errbuf OUT NOCOPY VARCHAR2
751 ,retcode OUT NOCOPY VARCHAR2
752 ,p_eff_date IN DATE
753 ,p_cnt IN NUMBER
754 ) IS
755 BEGIN
756
757 g_date := trunc(SYSDATE);
758
759 FORALL I IN 1 .. p_cnt
760 UPDATE per_empdir_locations
761 SET orig_system = locationTbl.orig_system(I)
762 ,orig_system_id = locationTbl.orig_system_id(I)
763 ,business_group_id = locationTbl.business_group_id(I)
764 ,derived_locale = locationTbl.derived_locale(I)
765 ,tax_name = locationTbl.tax_name(I)
766 ,country = locationTbl.country(I)
767 ,style = locationTbl.style(I)
768 ,address = locationTbl.address(I)
769 ,address_line_1 = locationTbl.address_line_1(I)
770 ,address_line_2 = locationTbl.address_line_2(I)
771 ,address_line_3 = locationTbl.address_line_3(I)
772 ,town_or_city = locationTbl.town_or_city(I)
773 ,region_1 = locationTbl.region_1(I)
774 ,region_2 = locationTbl.region_2(I)
775 ,region_3 = locationTbl.region_3(I)
776 ,postal_code = locationTbl.postal_code(I)
777 ,inactive_date = locationTbl.inactive_date(I)
778 ,office_site_flag = locationTbl.office_site_flag(I)
779 ,receiving_site_flag = locationTbl.receiving_site_flag(I)
780 ,telephone_number_1 = locationTbl.telephone_number_1(I)
781 ,telephone_number_2 = locationTbl.telephone_number_2(I)
782 ,telephone_number_3 = locationTbl.telephone_number_3(I)
783 ,timezone_id = locationTbl.timezone_id(I)
784 ,timezone_code = locationTbl.timezone_code(I)
785 ,object_version_number = locationTbl.object_version_number(I)
786 ,partition_id = locationTbl.partition_id(I)
787 ,last_update_date = g_date
788 ,last_updated_by = g_user_id
789 ,last_update_login = g_login_id
790 ,created_by = g_user_id
791 ,creation_date = g_date
792 ,request_id = g_request_id
793 ,program_application_id = g_prog_appl_id
794 ,program_id = g_prog_id
795 ,program_update_date = g_date
796 WHERE orig_system = locationTbl.orig_system(I)
797 AND orig_system_id = locationTbl.orig_system_id(I);
798
799 EXCEPTION WHEN OTHERS THEN
800 errbuf := errbuf||SQLERRM;
801 retcode := '1';
802 write_log(1, 'Error in update_per_locations: '||SQLCODE);
803 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
804 END update_per_locations;
805
806 PROCEDURE bulk_process_per_locations(
807 p_mode IN NUMBER
808 ,p_cnt OUT NOCOPY NUMBER
809 ,errbuf OUT NOCOPY VARCHAR2
810 ,retcode OUT NOCOPY VARCHAR2
811 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
812 ) IS
813
814 l_cursor cur_typ;
815 l_flg BOOLEAN:= FALSE;
816
817 BEGIN
818
819 p_cnt := 0;
820 l_flg := per_empdir_LEG_OVERRIDE.isOverrideEnabled('LOCATIONS');
821
822 open_per_locations(
823 l_cursor
824 ,p_mode
825 ,p_eff_date
826 );
827
828 LOOP
829 BEGIN
830 FETCH l_cursor BULK COLLECT
831 INTO locationTbl.orig_system
832 ,locationTbl.orig_system_id
833 ,locationTbl.business_group_id
834 ,locationTbl.derived_locale
835 ,locationTbl.tax_name
836 ,locationTbl.country
837 ,locationTbl.style
838 ,locationTbl.address
839 ,locationTbl.address_line_1
840 ,locationTbl.address_line_2
841 ,locationTbl.address_line_3
842 ,locationTbl.town_or_city
843 ,locationTbl.region_1
844 ,locationTbl.region_2
845 ,locationTbl.region_3
846 ,locationTbl.postal_code
847 ,locationTbl.inactive_date
848 ,locationTbl.office_site_flag
849 ,locationTbl.receiving_site_flag
850 ,locationTbl.telephone_number_1
851 ,locationTbl.telephone_number_2
852 ,locationTbl.telephone_number_3
853 ,locationTbl.timezone_id
854 ,locationTbl.timezone_code
855 ,locationTbl.object_version_number
856 ,locationTbl.partition_id LIMIT g_commit_size;
857
858 IF locationTbl.orig_system.count <= 0 THEN
859 CLOSE l_cursor;
860 EXIT;
861 END IF;
862
863 p_cnt := p_cnt + locationTbl.orig_system.count;
864
865 IF l_flg THEN
866 per_empdir_leg_override.locations(
867 errbuf => errbuf
868 ,retcode => retcode
869 ,p_eff_date => p_eff_date
870 ,p_cnt => locationTbl.orig_system.count
871 ,p_srcsystem => g_srcSystem);
872 END IF;
873
874
875 IF (p_mode = '0' OR p_mode = '2') THEN
876 dump_per_locations(
877 errbuf
878 ,retcode
879 ,p_eff_date
880 ,locationTbl.orig_system.count
881 );
882 ElSIF (p_mode = '1') THEN
883 update_per_locations(
884 errbuf
885 ,retcode
886 ,p_eff_date
887 ,locationTbl.orig_system.count
888 );
889 END IF;
890
891 COMMIT;
892
893 IF l_cursor%NOTFOUND THEN
894 CLOSE l_cursor;
895 EXIT;
896 END IF;
897 END;
898 END LOOP;
899 COMMIT;
900
901 EXCEPTION WHEN OTHERS THEN
902 errbuf := errbuf||SQLERRM;
903 retcode := '1';
904 write_log(1, 'Error in bulk_process_per_locations: '||SQLCODE);
905 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
906
907 END bulk_process_per_locations;
908
909 PROCEDURE update_per_asg(
910 errbuf OUT NOCOPY VARCHAR2
911 ,retcode OUT NOCOPY VARCHAR2
912 ,p_eff_date IN DATE
913 ,p_cnt IN NUMBER
914 ) IS
915 BEGIN
916
917 g_date := trunc(SYSDATE);
918
919 FORALL I IN 1 .. p_cnt
920 UPDATE per_empdir_assignments
921 SET orig_system = asgTbl.orig_system(I)
922 ,orig_system_id = asgTbl.orig_system_id(I)
923 ,business_group_id = asgTbl.business_group_id(I)
924 ,position_id = asgTbl.position_id(I)
925 ,job_id = asgTbl.job_id(I)
926 ,location_id = asgTbl.location_id(I)
927 ,supervisor_id = asgTbl.supervisor_id(I)
928 ,supervisor_assignment_id = asgTbl.supervisor_assignment_id(I)
929 ,person_id = asgTbl.person_id(I)
930 ,organization_id = asgTbl.organization_id(I)
931 ,primary_flag = asgTbl.primary_flag(I)
932 ,active = asgTbl.active(I)
933 ,assignment_number = asgTbl.assignment_number(I)
934 ,discretionary_title = asgTbl.discretionary_title(I)
935 ,employee_category = asgTbl.employee_category(I)
936 ,employment_category = asgTbl.employment_category(I)
937 ,assignment_category = asgTbl.assignment_category(I)
938 ,work_at_home = asgTbl.work_at_home(I)
939 ,object_version_number = asgTbl.object_version_number(I)
940 ,partition_id = asgTbl.partition_id(I)
941 ,request_id = g_request_id
942 ,program_application_id = g_prog_appl_id
943 ,program_id = g_prog_id
944 ,program_update_date = g_date
945 ,last_update_date = g_date
946 ,last_updated_by = g_user_id
947 ,last_update_login = g_login_id
948 ,created_by = g_user_id
949 ,creation_date = g_date
950 ,ass_attribute_category = asgTbl.ass_attribute_category(I)
951 ,ass_attribute1 = asgTbl.ass_attribute1(I)
952 ,ass_attribute2 = asgTbl.ass_attribute2(I)
953 ,ass_attribute3 = asgTbl.ass_attribute3(I)
954 ,ass_attribute4 = asgTbl.ass_attribute4(I)
955 ,ass_attribute5 = asgTbl.ass_attribute5(I)
956 ,ass_attribute6 = asgTbl.ass_attribute6(I)
957 ,ass_attribute7 = asgTbl.ass_attribute7(I)
958 ,ass_attribute8 = asgTbl.ass_attribute8(I)
959 ,ass_attribute9 = asgTbl.ass_attribute9(I)
960 ,ass_attribute10 = asgTbl.ass_attribute10(I)
961 ,ass_attribute11 = asgTbl.ass_attribute11(I)
962 ,ass_attribute12 = asgTbl.ass_attribute12(I)
963 ,ass_attribute13 = asgTbl.ass_attribute13(I)
964 ,ass_attribute14 = asgTbl.ass_attribute14(I)
965 ,ass_attribute15 = asgTbl.ass_attribute15(I)
966 ,ass_attribute16 = asgTbl.ass_attribute16(I)
967 ,ass_attribute17 = asgTbl.ass_attribute17(I)
968 ,ass_attribute18 = asgTbl.ass_attribute18(I)
969 ,ass_attribute19 = asgTbl.ass_attribute19(I)
970 ,ass_attribute20 = asgTbl.ass_attribute20(I)
971 ,ass_attribute21 = asgTbl.ass_attribute21(I)
972 ,ass_attribute22 = asgTbl.ass_attribute22(I)
973 ,ass_attribute23 = asgTbl.ass_attribute23(I)
974 ,ass_attribute24 = asgTbl.ass_attribute24(I)
975 ,ass_attribute25 = asgTbl.ass_attribute25(I)
976 ,ass_attribute26 = asgTbl.ass_attribute26(I)
977 ,ass_attribute27 = asgTbl.ass_attribute27(I)
978 ,ass_attribute28 = asgTbl.ass_attribute28(I)
979 ,ass_attribute29 = asgTbl.ass_attribute29(I)
980 ,ass_attribute30 = asgTbl.ass_attribute30(I)
981 WHERE orig_system = asgTbl.orig_system(I)
982 AND orig_system_id = asgTbl.orig_system_id(I);
983
984 EXCEPTION WHEN OTHERS THEN
985 errbuf := errbuf||SQLERRM;
986 retcode := '1';
987 write_log(1, 'Error in update_per_asg: '||SQLCODE);
988 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
989 END update_per_asg;
990
991 PROCEDURE dump_per_asg(
992 errbuf OUT NOCOPY VARCHAR2
993 ,retcode OUT NOCOPY VARCHAR2
994 ,p_eff_date IN DATE
995 ,p_cnt IN NUMBER
996 ) IS
997 BEGIN
998 g_date := trunc(SYSDATE);
999
1000 FORALL I IN 1 .. p_cnt
1001 INSERT INTO per_empdir_assignments values (
1002 asgTbl.orig_system(I)
1003 ,asgTbl.orig_system_id(I)
1004 ,asgTbl.business_group_id(I)
1005 ,asgTbl.position_id(I)
1006 ,asgTbl.job_id(I)
1007 ,asgTbl.location_id(I)
1008 ,asgTbl.supervisor_id(I)
1009 ,asgTbl.supervisor_assignment_id(I)
1010 ,asgTbl.person_id(I)
1011 ,asgTbl.organization_id(I)
1012 ,asgTbl.primary_flag(I)
1013 ,asgTbl.active(I)
1014 ,asgTbl.assignment_number(I)
1015 ,asgTbl.discretionary_title(I)
1016 ,asgTbl.employee_category(I)
1017 ,asgTbl.employment_category(I)
1018 ,asgTbl.assignment_category(I)
1019 ,asgTbl.work_at_home(I)
1020 ,asgTbl.object_version_number(I)
1021 ,asgTbl.partition_id(I)
1022 ,g_request_id
1023 ,g_prog_appl_id
1024 ,g_prog_id
1025 ,g_date
1026 ,g_date
1027 ,g_user_id
1028 ,g_login_id
1029 ,g_user_id
1030 ,g_date
1031 ,asgTbl.ass_attribute_category(I)
1032 ,asgTbl.ass_attribute1(I)
1033 ,asgTbl.ass_attribute2(I)
1034 ,asgTbl.ass_attribute3(I)
1035 ,asgTbl.ass_attribute4(I)
1036 ,asgTbl.ass_attribute5(I)
1037 ,asgTbl.ass_attribute6(I)
1038 ,asgTbl.ass_attribute7(I)
1039 ,asgTbl.ass_attribute8(I)
1040 ,asgTbl.ass_attribute9(I)
1041 ,asgTbl.ass_attribute10(I)
1042 ,asgTbl.ass_attribute11(I)
1043 ,asgTbl.ass_attribute12(I)
1044 ,asgTbl.ass_attribute13(I)
1045 ,asgTbl.ass_attribute14(I)
1046 ,asgTbl.ass_attribute15(I)
1047 ,asgTbl.ass_attribute16(I)
1048 ,asgTbl.ass_attribute17(I)
1049 ,asgTbl.ass_attribute18(I)
1050 ,asgTbl.ass_attribute19(I)
1051 ,asgTbl.ass_attribute20(I)
1052 ,asgTbl.ass_attribute21(I)
1053 ,asgTbl.ass_attribute22(I)
1054 ,asgTbl.ass_attribute23(I)
1055 ,asgTbl.ass_attribute24(I)
1056 ,asgTbl.ass_attribute25(I)
1057 ,asgTbl.ass_attribute26(I)
1058 ,asgTbl.ass_attribute27(I)
1059 ,asgTbl.ass_attribute28(I)
1060 ,asgTbl.ass_attribute29(I)
1061 ,asgTbl.ass_attribute30(I)
1062 );
1063
1064 EXCEPTION WHEN OTHERS THEN
1065 errbuf := errbuf||SQLERRM;
1066 retcode := '1';
1067 write_log(1, 'Error in dump_per_asg: '||SQLCODE);
1068 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
1069 END dump_per_asg;
1070
1071 PROCEDURE open_per_asg(
1072 p_cursor IN OUT NOCOPY cur_typ
1073 ,p_mode IN NUMBER
1074 ,p_eff_date IN DATE
1075 ,p_multi_asg IN VARCHAR2
1076 ) IS
1077
1078 query_str VARCHAR2(4000);
1079 l_multi_asg CHAR(1):=NULL;
1080
1081 BEGIN
1082
1083 IF (p_multi_asg = 'N') THEN
1084 l_multi_asg := 'Y';
1085 END IF;
1086 query_str := 'SELECT ''' || g_srcSystem || ''', paf.assignment_id, paf.business_group_id, oi.org_information9,'||
1087 'paf.position_id, paf.job_id, paf.location_id, paf.supervisor_id, null,' ||
1088 'paf.person_id, paf.organization_id, paf.primary_flag,' ||
1089 'decode (astatus.per_system_status, ''TERM_ASSIGN'', ''N'',' ||
1090 ' decode(paf.assignment_type, ''E'', ''Y'', ''C'', ''Y'', ''N'')), paf.assignment_number,'||
1091 'null, paf.employee_category, paf.employment_category, paf.assignment_category,' ||
1092 'paf.work_at_home, paf.object_version_number, 1,' ||
1093 'ass_attribute_category, ass_attribute1, ass_attribute2, ass_attribute3,' ||
1094 'ass_attribute4, ass_attribute5, ass_attribute6, ass_attribute7, ass_attribute8,' ||
1095 'ass_attribute9, ass_attribute10, ass_attribute11, ass_attribute12, ass_attribute13,'||
1096 'ass_attribute14, ass_attribute15, ass_attribute16, ass_attribute17, ass_attribute18,'||
1097 'ass_attribute19, ass_attribute20, ass_attribute21, ass_attribute22, ass_attribute23,'||
1098 'ass_attribute24, ass_attribute25, ass_attribute26, ass_attribute27, ass_attribute28,'||
1099 'ass_attribute29, ass_attribute30 '||
1100 ' FROM per_assignments_f paf, per_assignment_status_types astatus' ||
1101 ' ,hr_organization_information oi '||
1102 ' WHERE :1 BETWEEN effective_start_date AND effective_end_date'||
1103 ' AND paf.assignment_status_type_id = astatus.assignment_status_type_id'||
1104 ' AND paf.business_group_id = oi.organization_id'||
1105 ' AND oi.org_information_context = ''Business Group Information'''||
1106 ' AND paf.primary_flag = nvl(:2,paf.primary_flag)'||
1107 ' /* Avoiding PK Violation */ '||
1108 ' AND paf.assignment_id NOT IN '||
1109 ' (SELECT assignment_id FROM per_all_assignments_f ipaf' ||
1110 ' WHERE :3 BETWEEN effective_start_date AND effective_end_date'||
1111 ' GROUP BY assignment_id HAVING count(*) > 1)'||
1112 ' AND paf.assignment_type in (''E'',''C'')';
1113
1114 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
1115 query_str := query_str || ' AND paf.assignment_id NOT IN '||
1116 ' (SELECT assignment_id FROM per_all_assignments_f lpaf'||
1117 ' WHERE label_to_char(lpaf.HR_ENTERPRISE) = ''C::ENT'' OR label_to_char(lpaf.HR_ENTERPRISE) is null)';
1118 END IF;
1119
1120 IF (p_mode = 0) THEN
1121 query_str := query_str || ' AND astatus.per_system_status <> ''TERM_ASSIGN''';
1122 OPEN p_cursor FOR query_str using p_eff_date,l_multi_asg,p_eff_date;
1123 ELSIF (p_mode = 1) THEN
1124 query_str := query_str || ' AND EXISTS (SELECT ''e'' from per_empdir_assignments ia '||
1125 ' WHERE ia.orig_system_id = paf.assignment_id'||
1126 ' AND ia.orig_system = ''' || g_srcSystem || ''''||
1127 ' AND (ia.object_version_number <> paf.object_version_number '||
1128 ' OR paf.effective_start_date >= ia.last_update_date ))';
1129 OPEN p_cursor FOR query_str using p_eff_date,l_multi_asg,p_eff_date;
1130 ELSIF (p_mode = 2) THEN
1131 query_str := query_str || ' AND astatus.per_system_status <> ''TERM_ASSIGN'''||
1132 ' AND NOT EXISTS (SELECT ''e'' from per_empdir_assignments ia'||
1133 ' WHERE ia.orig_system_id = paf.assignment_id'||
1134 ' AND ia.orig_system = ''' || g_srcSystem|| ''')';
1135 OPEN p_cursor FOR query_str using p_eff_date,l_multi_asg,p_eff_date;
1136 END IF;
1137 END open_per_asg;
1138
1139 PROCEDURE bulk_process_per_asg(
1140 p_mode IN NUMBER
1141 ,p_cnt OUT NOCOPY NUMBER
1142 ,errbuf OUT NOCOPY VARCHAR2
1143 ,retcode OUT NOCOPY VARCHAR2
1144 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
1145 ,p_multi_asg IN VARCHAR2
1146 ) IS
1147
1148 l_cursor cur_typ;
1149 l_flg BOOLEAN:= FALSE;
1150
1151 BEGIN
1152
1153 p_cnt := 0;
1154 l_flg := per_empdir_LEG_OVERRIDE.isOverrideEnabled('ASSIGNMENTS');
1155
1156 open_per_asg(
1157 l_cursor
1158 ,p_mode
1159 ,p_eff_date
1160 ,p_multi_asg
1161 );
1162
1163 LOOP
1164 BEGIN
1165 FETCH l_cursor BULK COLLECT
1166 INTO asgTbl.orig_system
1167 ,asgTbl.orig_system_id
1168 ,asgTbl.business_group_id
1169 ,asgTbl.legislation_code
1170 ,asgTbl.position_id
1171 ,asgTbl.job_id
1172 ,asgTbl.location_id
1173 ,asgTbl.supervisor_id
1174 ,asgTbl.supervisor_assignment_id
1175 ,asgTbl.person_id
1176 ,asgTbl.organization_id
1177 ,asgTbl.primary_flag
1178 ,asgTbl.active
1179 ,asgTbl.assignment_number
1180 ,asgTbl.discretionary_title
1181 ,asgTbl.employee_category
1182 ,asgTbl.employment_category
1183 ,asgTbl.assignment_category
1184 ,asgTbl.work_at_home
1185 ,asgTbl.object_version_number
1186 ,asgTbl.partition_id
1187 ,asgTbl.ass_attribute_category
1188 ,asgTbl.ass_attribute1
1189 ,asgTbl.ass_attribute2
1190 ,asgTbl.ass_attribute3
1191 ,asgTbl.ass_attribute4
1192 ,asgTbl.ass_attribute5
1193 ,asgTbl.ass_attribute6
1194 ,asgTbl.ass_attribute7
1195 ,asgTbl.ass_attribute8
1196 ,asgTbl.ass_attribute9
1197 ,asgTbl.ass_attribute10
1198 ,asgTbl.ass_attribute11
1199 ,asgTbl.ass_attribute12
1200 ,asgTbl.ass_attribute13
1201 ,asgTbl.ass_attribute14
1202 ,asgTbl.ass_attribute15
1203 ,asgTbl.ass_attribute16
1204 ,asgTbl.ass_attribute17
1205 ,asgTbl.ass_attribute18
1206 ,asgTbl.ass_attribute19
1207 ,asgTbl.ass_attribute20
1208 ,asgTbl.ass_attribute21
1209 ,asgTbl.ass_attribute22
1210 ,asgTbl.ass_attribute23
1211 ,asgTbl.ass_attribute24
1212 ,asgTbl.ass_attribute25
1213 ,asgTbl.ass_attribute26
1214 ,asgTbl.ass_attribute27
1215 ,asgTbl.ass_attribute28
1216 ,asgTbl.ass_attribute29
1217 ,asgTbl.ass_attribute30 LIMIT g_commit_size;
1218
1219 IF asgTbl.orig_system.count <= 0 THEN
1220 CLOSE l_cursor;
1221 EXIT;
1222 END IF;
1223
1224 p_cnt := p_cnt + asgTbl.orig_system.count;
1225
1226 IF l_flg THEN
1227 per_empdir_leg_override.asg(
1228 errbuf => errbuf
1229 ,retcode => retcode
1230 ,p_eff_date => p_eff_date
1231 ,p_cnt => asgTbl.orig_system.count
1232 ,p_srcsystem => g_srcSystem);
1233 END IF;
1234
1235 IF (p_mode = '0' OR p_mode = '2') THEN
1236 dump_per_asg(
1237 errbuf
1238 ,retcode
1239 ,p_eff_date
1240 ,asgTbl.orig_system.count
1241 );
1242 ElSIF (p_mode = '1') THEN
1243 update_per_asg(
1244 errbuf
1245 ,retcode
1246 ,p_eff_date
1247 ,asgTbl.orig_system.count
1248 );
1249 END IF;
1250
1251 COMMIT;
1252
1253 IF l_cursor%NOTFOUND THEN
1254 CLOSE l_cursor;
1255 EXIT;
1256 END IF;
1257 END;
1258 END LOOP;
1259 COMMIT;
1260
1261 EXCEPTION WHEN OTHERS THEN
1262 errbuf := errbuf||SQLERRM;
1263 retcode := '1';
1264 write_log(1, 'Error in bulk_process_per_asg: '||SQLCODE);
1265 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
1266
1267 END bulk_process_per_asg;
1268
1269
1270
1271 PROCEDURE update_hr_orgs(
1272 errbuf OUT NOCOPY VARCHAR2
1273 ,retcode OUT NOCOPY VARCHAR2
1274 ,p_eff_date IN DATE
1275 ,p_cnt IN NUMBER
1276 ) IS
1277 BEGIN
1278
1279 g_date := trunc(SYSDATE);
1280
1281 FORALL I IN 1 .. p_cnt
1282 UPDATE per_empdir_organizations
1283 SET orig_system = orgTbl.orig_system(I)
1284 ,orig_system_id = orgTbl.orig_system_id(I)
1285 ,business_group_id = orgTbl.business_group_id(I)
1286 ,location_id = orgTbl.location_id(I)
1287 ,representative1_id = orgTbl.representative1_id(I)
1288 ,representative2_id = orgTbl.representative1_id(I)
1289 ,representative3_id = orgTbl.representative1_id(I)
1290 ,representative4_id = orgTbl.representative1_id(I)
1291 ,name = orgTbl.name(I)
1292 ,language = orgTbl.language(I)
1293 ,source_lang = orgTbl.source_lang(I)
1294 ,object_version_number = orgTbl.object_version_number(I)
1295 ,partition_id = orgTbl.partition_id(I)
1296 ,last_update_date = g_date
1297 ,last_update_by = g_user_id
1298 ,created_by = g_user_id
1299 ,creation_date = g_date
1300 ,request_id = g_request_id
1301 ,program_application_id = g_prog_appl_id
1302 ,program_id = g_prog_id
1303 ,program_update_date = g_date
1304 ,attribute_category = orgTbl.attribute_category(I)
1305 ,attribute1 = orgTbl.attribute1(I)
1306 ,attribute2 = orgTbl.attribute2(I)
1307 ,attribute3 = orgTbl.attribute3(I)
1308 ,attribute4 = orgTbl.attribute4(I)
1309 ,attribute5 = orgTbl.attribute5(I)
1310 ,attribute6 = orgTbl.attribute6(I)
1311 ,attribute7 = orgTbl.attribute7(I)
1312 ,attribute8 = orgTbl.attribute8(I)
1313 ,attribute9 = orgTbl.attribute9(I)
1314 ,attribute10 = orgTbl.attribute10(I)
1315 ,attribute11 = orgTbl.attribute11(I)
1316 ,attribute12 = orgTbl.attribute12(I)
1317 ,attribute13 = orgTbl.attribute13(I)
1318 ,attribute14 = orgTbl.attribute14(I)
1319 ,attribute15 = orgTbl.attribute15(I)
1320 ,attribute16 = orgTbl.attribute16(I)
1321 ,attribute17 = orgTbl.attribute17(I)
1322 ,attribute18 = orgTbl.attribute18(I)
1323 ,attribute19 = orgTbl.attribute19(I)
1324 ,attribute20 = orgTbl.attribute20(I)
1325 WHERE orig_system = orgTbl.orig_system(I)
1326 AND orig_system_id = orgTbl.orig_system_id(I)
1327 AND language = orgTbl.language(I);
1328
1329 EXCEPTION WHEN OTHERS THEN
1330 errbuf := errbuf||SQLERRM;
1331 retcode := '1';
1332 write_log(1, 'Error in update_hr_orgs: '||SQLCODE);
1333 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
1334 END update_hr_orgs;
1335
1336 PROCEDURE dump_hr_orgs(
1337 errbuf OUT NOCOPY VARCHAR2
1338 ,retcode OUT NOCOPY VARCHAR2
1339 ,p_eff_date IN DATE
1340 ,p_cnt IN NUMBER
1341 ) IS
1342 BEGIN
1343 g_date := trunc(SYSDATE);
1344
1345 FORALL I IN 1 .. p_cnt
1346 INSERT INTO per_empdir_organizations values (
1347 orgTbl.orig_system(I)
1348 ,orgTbl.orig_system_id(I)
1349 ,orgTbl.business_group_id(I)
1350 ,orgTbl.location_id(I)
1351 ,orgTbl.representative1_id(I)
1352 ,orgTbl.representative2_id(I)
1353 ,orgTbl.representative3_id(I)
1354 ,orgTbl.representative4_id(I)
1355 ,orgTbl.name(I)
1356 ,orgTbl.language(I)
1357 ,orgTbl.source_lang(I)
1358 ,orgTbl.object_version_number(I)
1359 ,orgTbl.partition_id(I)
1360 ,g_date
1361 ,g_user_id
1362 ,g_user_id
1363 ,g_date
1364 ,g_request_id
1365 ,g_prog_appl_id
1366 ,g_prog_id
1367 ,g_date
1368 ,orgTbl.attribute_category(I)
1369 ,orgTbl.attribute1(I)
1370 ,orgTbl.attribute2(I)
1371 ,orgTbl.attribute3(I)
1372 ,orgTbl.attribute4(I)
1373 ,orgTbl.attribute5(I)
1374 ,orgTbl.attribute6(I)
1375 ,orgTbl.attribute7(I)
1376 ,orgTbl.attribute8(I)
1377 ,orgTbl.attribute9(I)
1378 ,orgTbl.attribute10(I)
1379 ,orgTbl.attribute11(I)
1380 ,orgTbl.attribute12(I)
1381 ,orgTbl.attribute13(I)
1382 ,orgTbl.attribute14(I)
1383 ,orgTbl.attribute15(I)
1384 ,orgTbl.attribute16(I)
1385 ,orgTbl.attribute17(I)
1386 ,orgTbl.attribute18(I)
1387 ,orgTbl.attribute19(I)
1388 ,orgTbl.attribute20(I)
1389 );
1390
1391 EXCEPTION WHEN OTHERS THEN
1392 errbuf := errbuf||SQLERRM;
1393 retcode := '1';
1394 write_log(1, 'Error in dump_hr_orgs: '||SQLCODE);
1395 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
1396 END dump_hr_orgs;
1397
1398 PROCEDURE open_hr_orgs(
1399 p_cursor IN OUT NOCOPY cur_typ
1400 ,p_mode IN NUMBER
1401 ,p_eff_date IN DATE
1402 ) IS
1403
1404 query_str VARCHAR2(4000);
1405
1406 BEGIN
1407 query_str := 'SELECT '''||g_srcSystem||''', hou.organization_id,hou.business_group_id,oi.org_information9,'||
1408 'hou.location_id,null rep1,null rep2,null rep3,null rep4,houtl.name,houtl.language,'||
1409 'houtl.source_lang,hou.object_version_number,1,hou.attribute_category, hou.attribute1,'||
1410 'hou.attribute2, hou.attribute3, hou.attribute4, hou.attribute5, hou.attribute6,'||
1411 'hou.attribute7, hou.attribute8, hou.attribute9, hou.attribute10, hou.attribute11,'||
1412 'hou.attribute12, hou.attribute13,hou.attribute14, hou.attribute15, hou.attribute16,'||
1413 'hou.attribute17, hou.attribute18, hou.attribute19, hou.attribute20 '||
1414 'FROM hr_all_organization_units hou, hr_all_organization_units_tl houtl ,'||
1415 'hr_organization_information oi '||
1416 'WHERE hou.organization_id = houtl.organization_id'||
1417 ' AND hou.business_group_id = oi.organization_id'||
1418 ' AND oi.org_information_context = ''Business Group Information'''||
1419 ' /* Avoiding PK Violation */'||
1420 ' AND houtl.organization_id NOT IN '||
1421 ' (SELECT organization_id FROM hr_all_organization_units ihou'||
1422 ' GROUP BY organization_id HAVING count(*) > 1)';
1423 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
1424 query_str := query_str || ' AND houtl.organization_id NOT IN '||
1425 ' (SELECT organization_id FROM hr_all_organization_units lhou'||
1426 ' WHERE label_to_char(lhou.HR_ENTERPRISE) = ''C::ENT'' OR label_to_char(lhou.HR_ENTERPRISE) is null)';
1427 END IF;
1428 IF (p_mode = 0) THEN
1429 OPEN p_cursor FOR query_str;
1430 ELSIF (p_mode = 1) THEN
1431 query_str := query_str || ' AND EXISTS (SELECT ''e'' FROM per_empdir_organizations io '||
1432 ' WHERE io.orig_system_id = hou.organization_id' ||
1433 ' AND io.orig_system = ''' || g_srcSystem || '''' ||
1434 ' AND io.object_version_number <> hou.object_version_number)';
1435 OPEN p_cursor FOR query_str;
1436 ELSIF (p_mode = 2) THEN
1437 query_str := query_str || ' AND NOT EXISTS (SELECT ''e'' FROM per_empdir_organizations io '||
1438 ' WHERE io.orig_system_id = houtl.organization_id'||
1439 ' AND io.orig_system = ''' || g_srcSystem || ''')';
1440
1441 OPEN p_cursor FOR query_str;
1442 END IF;
1443 END open_hr_orgs;
1444
1445 PROCEDURE bulk_process_hr_orgs(
1446 p_mode IN NUMBER
1447 ,p_cnt OUT NOCOPY NUMBER
1448 ,errbuf OUT NOCOPY VARCHAR2
1449 ,retcode OUT NOCOPY VARCHAR2
1450 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
1451 ) IS
1452
1453 l_cursor cur_typ;
1454 l_flg BOOLEAN:= FALSE;
1455
1456 BEGIN
1457
1458 p_cnt := 0;
1459 l_flg := per_empdir_LEG_OVERRIDE.isOverrideEnabled('ORGANIZATIONS');
1460
1461 open_hr_orgs(
1462 l_cursor
1463 ,p_mode
1464 ,p_eff_date
1465 );
1466
1467 LOOP
1468 BEGIN
1469 FETCH l_cursor BULK COLLECT
1470 INTO orgTbl.orig_system
1471 ,orgTbl.orig_system_id
1472 ,orgTbl.business_group_id
1473 ,orgTbl.legislation_code
1474 ,orgTbl.location_id
1475 ,orgTbl.representative1_id
1476 ,orgTbl.representative2_id
1477 ,orgTbl.representative3_id
1478 ,orgTbl.representative4_id
1479 ,orgTbl.name
1480 ,orgTbl.language
1481 ,orgTbl.source_lang
1482 ,orgTbl.object_version_number
1483 ,orgTbl.partition_id
1484 ,orgTbl.attribute_category
1485 ,orgTbl.attribute1
1486 ,orgTbl.attribute2
1487 ,orgTbl.attribute3
1488 ,orgTbl.attribute4
1489 ,orgTbl.attribute5
1490 ,orgTbl.attribute6
1491 ,orgTbl.attribute7
1492 ,orgTbl.attribute8
1493 ,orgTbl.attribute9
1494 ,orgTbl.attribute10
1495 ,orgTbl.attribute11
1496 ,orgTbl.attribute12
1497 ,orgTbl.attribute13
1498 ,orgTbl.attribute14
1499 ,orgTbl.attribute15
1500 ,orgTbl.attribute16
1501 ,orgTbl.attribute17
1502 ,orgTbl.attribute18
1503 ,orgTbl.attribute19
1504 ,orgTbl.attribute20 LIMIT g_commit_size;
1505
1506 IF orgTbl.orig_system.count <= 0 THEN
1507 CLOSE l_cursor;
1508 EXIT;
1509 END IF;
1510
1511 p_cnt := p_cnt + orgTbl.orig_system.count;
1512
1513 IF l_flg THEN
1514 per_empdir_leg_override.orgs(
1515 errbuf => errbuf
1516 ,retcode => retcode
1517 ,p_eff_date => p_eff_date
1518 ,p_cnt => orgTbl.orig_system.count
1519 ,p_srcsystem => g_srcSystem);
1520 END IF;
1521
1522
1523 IF (p_mode = '0' OR p_mode = '2') THEN
1524 dump_hr_orgs(
1525 errbuf
1526 ,retcode
1527 ,p_eff_date
1528 ,orgTbl.orig_system.count
1529 );
1530 ElSIF (p_mode = '1') THEN
1531 update_hr_orgs(
1532 errbuf
1533 ,retcode
1534 ,p_eff_date
1535 ,orgTbl.orig_system.count
1536 );
1537 END IF;
1538
1539 COMMIT;
1540
1541 IF l_cursor%NOTFOUND THEN
1542 CLOSE l_cursor;
1543 EXIT;
1544 END IF;
1545 END;
1546 END LOOP;
1547 COMMIT;
1548
1549 EXCEPTION WHEN OTHERS THEN
1550 errbuf := errbuf||SQLERRM;
1551 retcode := '1';
1552 write_log(1, 'Error in bulk_process_hr_orgs: '||SQLCODE);
1553 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
1554
1555 END bulk_process_hr_orgs;
1556
1557 PROCEDURE update_per_jobs(
1558 errbuf OUT NOCOPY VARCHAR2
1559 ,retcode OUT NOCOPY VARCHAR2
1560 ,p_eff_date IN DATE
1561 ,p_cnt IN NUMBER
1562 ) IS
1563 BEGIN
1564
1565 g_date := trunc(SYSDATE);
1566
1567 FORALL I IN 1 .. p_cnt
1568 UPDATE per_empdir_jobs
1569 SET orig_system = jobTbl.orig_system(I)
1570 ,orig_system_id = jobTbl.orig_system_id(I)
1571 ,business_group_id = jobTbl.business_group_id(I)
1572 ,name = jobTbl.name(I)
1573 ,language = jobTbl.language(I)
1574 ,source_lang = jobTbl.source_language(I)
1575 ,object_version_number = jobTbl.object_version_number(I)
1576 ,partition_id = jobTbl.partition_id(I)
1577 ,last_update_date = g_date
1578 ,last_update_by = g_user_id
1579 ,created_by = g_user_id
1580 ,creation_date = g_date
1581 ,request_id = g_request_id
1582 ,program_application_id = g_prog_appl_id
1583 ,program_id = g_prog_id
1584 ,program_update_date = g_date
1585 WHERE orig_system = jobTbl.orig_system(I)
1586 AND orig_system_id = jobTbl.orig_system_id(I)
1587 AND language = jobTbl.language(I);
1588
1589 EXCEPTION WHEN OTHERS THEN
1590 errbuf := errbuf||SQLERRM;
1591 retcode := '1';
1592 write_log(1, 'Error in update_per_jobs: '||SQLCODE);
1593 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
1594 END update_per_jobs;
1595
1596 PROCEDURE open_per_jobs(
1597 p_cursor IN OUT NOCOPY cur_typ
1598 ,p_mode IN NUMBER
1599 ,p_eff_date IN DATE
1600 ,p_slist IN VARCHAR2
1601 ,p_busGrpId IN NUMBER
1602 ) IS
1603
1604 query_str VARCHAR2(4000);
1605
1606 BEGIN
1607 query_str := 'SELECT '''||g_srcSystem||''', j.job_id, j.business_group_id, oi.org_information9, j.job_definition_id, '||
1608 'jtl.name, '||p_slist||' title, jtl.language, jtl.source_lang, j.object_version_number, 1, '||
1609 'j.attribute_category, j.attribute1, j.attribute2, j.attribute3, j.attribute4, j.attribute5, '||
1610 'j.attribute6, j.attribute7, j.attribute8, j.attribute9, j.attribute10,j.attribute11, '||
1611 'j.attribute12, j.attribute13, j.attribute14, j.attribute15, j.attribute16, j.attribute17, '||
1612 'j.attribute18, j.attribute19, j.attribute20, job_information_category, job_information1, '||
1613 'job_information2, job_information3, job_information4, job_information5, '||
1614 'job_information6, job_information7, job_information8, job_information9, '||
1615 'job_information10, job_information11, job_information12, job_information13, '||
1616 'job_information14, job_information15, job_information16, job_information17, '||
1617 'job_information18, job_information19, job_information20 '||
1618 'FROM per_jobs j, per_jobs_tl jtl, per_job_definitions jd '||
1619 ',hr_organization_information oi '||
1620 'WHERE j.job_id = jtl.job_id '||
1621 'AND j.job_definition_id = jd.job_definition_id '||
1622 'AND jtl.name is not null '||
1623 'AND j.business_group_id = :1 '||
1624 'AND j.business_group_id = oi.organization_id '||
1625 'AND oi.org_information_context = ''Business Group Information''';
1626 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
1627 query_str := query_str || ' AND j.job_id NOT IN '||
1628 ' (SELECT job_id FROM per_jobs lj'||
1629 ' WHERE label_to_char(lj.HR_ENTERPRISE) = ''C::ENT'' OR label_to_char(lj.HR_ENTERPRISE) is null)';
1630 END IF;
1631 IF (p_mode = 0) THEN
1632 OPEN p_cursor FOR query_str USING p_busGrpId;
1633 ELSIF (p_mode = 1) THEN
1634 query_str := query_str ||
1635 'AND EXISTS (SELECT ''e'' from per_empdir_jobs ij '||
1636 'WHERE ij.orig_system_id = j.job_id '||
1637 'AND ij.orig_system = '''||g_srcSystem||''' '||
1638 'AND ij.object_version_number <> j.object_version_number) ';
1639 OPEN p_cursor FOR query_str USING p_busGrpId;
1640 ELSIF (p_mode = 2) THEN
1641 query_str := query_str ||
1642 'AND NOT EXISTS (SELECT ''e'' from per_empdir_jobs ij '||
1643 'WHERE ij.orig_system_id = j.job_id '||
1644 'AND ij.orig_system = '''||g_srcSystem||''') ';
1645 OPEN p_cursor FOR query_str USING p_busGrpId;
1646 END IF;
1647 END open_per_jobs;
1648
1649 PROCEDURE bulk_process_per_jobs(
1650 p_mode IN NUMBER
1651 ,p_cnt OUT NOCOPY NUMBER
1652 ,errbuf OUT NOCOPY VARCHAR2
1653 ,retcode OUT NOCOPY VARCHAR2
1654 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
1655 ,p_slist IN VARCHAR2
1656 ,p_busGrpId IN NUMBER
1657 ) IS
1658
1659 l_cursor cur_typ;
1660 l_flg BOOLEAN:= FALSE;
1661 l_index NUMBER:= 1;
1662 BEGIN
1663
1664 p_cnt := 0;
1665 l_flg := per_empdir_LEG_OVERRIDE.isOverrideEnabled('JOBS');
1666
1667 open_per_jobs(
1668 l_cursor
1669 ,p_mode
1670 ,p_eff_date
1671 ,p_slist
1672 ,p_busGrpId
1673 );
1674
1675 LOOP
1676 BEGIN
1677 IF g_oracle_db_version >= 9 THEN
1678 FETCH l_cursor BULK COLLECT INTO
1679 jobTbl.orig_system
1680 ,jobTbl.orig_system_id
1681 ,jobTbl.business_group_id
1682 ,jobTbl.legislation_code
1683 ,jobTbl.job_definition_id
1684 ,jobTbl.name
1685 ,jobTbl.display_name
1686 ,jobTbl.language
1687 ,jobTbl.source_language
1688 ,jobTbl.object_version_number
1689 ,jobTbl.partition_id
1690 ,jobTbl.attribute_category
1691 ,jobTbl.attribute1
1692 ,jobTbl.attribute2
1693 ,jobTbl.attribute3
1694 ,jobTbl.attribute4
1695 ,jobTbl.attribute5
1696 ,jobTbl.attribute6
1697 ,jobTbl.attribute7
1698 ,jobTbl.attribute8
1699 ,jobTbl.attribute9
1700 ,jobTbl.attribute10
1701 ,jobTbl.attribute11
1702 ,jobTbl.attribute12
1703 ,jobTbl.attribute13
1704 ,jobTbl.attribute14
1705 ,jobTbl.attribute15
1706 ,jobTbl.attribute16
1707 ,jobTbl.attribute17
1708 ,jobTbl.attribute18
1709 ,jobTbl.attribute19
1710 ,jobTbl.attribute20
1711 ,jobTbl.job_information_category
1712 ,jobTbl.job_information1
1713 ,jobTbl.job_information2
1714 ,jobTbl.job_information
1715 ,jobTbl.job_information4
1716 ,jobTbl.job_information5
1717 ,jobTbl.job_information6
1718 ,jobTbl.job_information7
1719 ,jobTbl.job_information8
1720 ,jobTbl.job_information9
1721 ,jobTbl.job_information10
1722 ,jobTbl.job_information11
1723 ,jobTbl.job_information12
1724 ,jobTbl.job_information13
1725 ,jobTbl.job_information14
1726 ,jobTbl.job_information15
1727 ,jobTbl.job_information16
1728 ,jobTbl.job_information17
1729 ,jobTbl.job_information18
1730 ,jobTbl.job_information19
1731 ,jobTbl.job_information20 LIMIT g_commit_size;
1732 ELSE
1733 l_index := 1;
1734 jobTbl := null;
1735 LOOP FETCH l_cursor INTO
1736 jobTbl.orig_system(l_index)
1737 ,jobTbl.orig_system_id(l_index)
1738 ,jobTbl.business_group_id(l_index)
1739 ,jobTbl.legislation_code(l_index)
1740 ,jobTbl.job_definition_id(l_index)
1741 ,jobTbl.name(l_index)
1742 ,jobTbl.display_name(l_index)
1743 ,jobTbl.language(l_index)
1744 ,jobTbl.source_language(l_index)
1745 ,jobTbl.object_version_number(l_index)
1746 ,jobTbl.partition_id(l_index)
1747 ,jobTbl.attribute_category(l_index)
1748 ,jobTbl.attribute1(l_index)
1749 ,jobTbl.attribute2(l_index)
1750 ,jobTbl.attribute3(l_index)
1751 ,jobTbl.attribute4(l_index)
1752 ,jobTbl.attribute5(l_index)
1753 ,jobTbl.attribute6(l_index)
1754 ,jobTbl.attribute7(l_index)
1755 ,jobTbl.attribute8(l_index)
1756 ,jobTbl.attribute9(l_index)
1757 ,jobTbl.attribute10(l_index)
1758 ,jobTbl.attribute11(l_index)
1759 ,jobTbl.attribute12(l_index)
1760 ,jobTbl.attribute13(l_index)
1761 ,jobTbl.attribute14(l_index)
1762 ,jobTbl.attribute15(l_index)
1763 ,jobTbl.attribute16(l_index)
1764 ,jobTbl.attribute17(l_index)
1765 ,jobTbl.attribute18(l_index)
1766 ,jobTbl.attribute19(l_index)
1767 ,jobTbl.attribute20(l_index)
1768 ,jobTbl.job_information_category(l_index)
1769 ,jobTbl.job_information1(l_index)
1770 ,jobTbl.job_information2(l_index)
1771 ,jobTbl.job_information(l_index)
1772 ,jobTbl.job_information4(l_index)
1773 ,jobTbl.job_information5(l_index)
1774 ,jobTbl.job_information6(l_index)
1775 ,jobTbl.job_information7(l_index)
1776 ,jobTbl.job_information8(l_index)
1777 ,jobTbl.job_information9(l_index)
1778 ,jobTbl.job_information10(l_index)
1779 ,jobTbl.job_information11(l_index)
1780 ,jobTbl.job_information12(l_index)
1781 ,jobTbl.job_information13(l_index)
1782 ,jobTbl.job_information14(l_index)
1783 ,jobTbl.job_information15(l_index)
1784 ,jobTbl.job_information16(l_index)
1785 ,jobTbl.job_information17(l_index)
1786 ,jobTbl.job_information18(l_index)
1787 ,jobTbl.job_information19(l_index)
1788 ,jobTbl.job_information20(l_index);
1789
1790 EXIT WHEN l_cursor%NOTFOUND;
1791 l_index := l_index + 1;
1792 END LOOP;
1793 END IF;
1794
1795 IF jobTbl.orig_system.count <= 0 THEN
1796 CLOSE l_cursor;
1797 EXIT;
1798 END IF;
1799
1800 p_cnt := p_cnt + jobTbl.orig_system.count;
1801
1802 IF l_flg THEN
1803 per_empdir_leg_override.jobs(
1804 errbuf => errbuf
1805 ,retcode => retcode
1806 ,p_eff_date => p_eff_date
1807 ,p_cnt => jobTbl.orig_system.count
1808 ,p_srcsystem => g_srcSystem);
1809 END IF;
1810
1811
1812 IF (p_mode = '0' OR p_mode = '2') THEN
1813 dump_per_jobs(
1814 errbuf
1815 ,retcode
1816 ,jobTbl.orig_system.count
1817 );
1818 ElSIF (p_mode = '1') THEN
1819 update_per_jobs(
1820 errbuf
1821 ,retcode
1822 ,p_eff_date
1823 ,jobTbl.orig_system.count
1824 );
1825 END IF;
1826
1827 COMMIT;
1828
1829 IF l_cursor%NOTFOUND THEN
1830 CLOSE l_cursor;
1831 EXIT;
1832 END IF;
1833
1834 EXCEPTION
1835 WHEN OTHERS THEN
1836 errbuf := errbuf||SQLERRM;
1837 retcode := '1';
1838 write_log(1, 'Error in jobs bulk collect: '||SQLCODE);
1839 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
1840 END;
1841 END LOOP;
1842
1843 COMMIT;
1844
1845 EXCEPTION WHEN OTHERS THEN
1846 errbuf := errbuf||SQLERRM;
1847 retcode := '1';
1848 write_log(1, 'Error in bulk_process_per_jobs: '||SQLCODE);
1849 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
1850
1851 END bulk_process_per_jobs;
1852
1853 PROCEDURE dump_per_people(
1854 errbuf OUT NOCOPY VARCHAR2
1855 ,retcode OUT NOCOPY VARCHAR2
1856 ,p_eff_date IN DATE
1857 ,p_cnt IN NUMBER
1858 ) IS
1859 BEGIN
1860
1861 g_date := trunc(SYSDATE);
1862
1863 FORALL I IN 1 .. p_cnt
1864 INSERT INTO per_empdir_people values (
1865 personTbl.person_key(I)
1866 ,personTbl.orig_system(I)
1867 ,personTbl.orig_sytem_id(I)
1868 ,personTbl.business_group_id(I)
1869 ,personTbl.legislation_code(I)
1870 ,personTbl.display_name(I)
1871 ,personTbl.full_name(I)
1872 ,personTbl.full_name_alternate(I)
1873 ,personTbl.last_name(I)
1874 ,personTbl.first_name(I)
1875 ,personTbl.last_name_alternate(I)
1876 ,personTbl.first_name_alternate(I)
1877 ,personTbl.pre_name_adjunct(I)
1878 ,personTbl.person_type(I)
1879 ,personTbl.user_name(I)
1880 ,personTbl.active(I)
1881 ,personTbl.employee_number(I)
1882 ,personTbl.known_as(I)
1883 ,personTbl.middle_names(I)
1884 ,personTbl.previous_last_name(I)
1885 ,personTbl.start_date(I)
1886 ,personTbl.original_DOH(I)
1887 ,personTbl.email_address(I)
1888 ,personTbl.work_telephone(I)
1889 ,personTbl.mailstop(I)
1890 ,personTbl.office_number(I)
1891 ,personTbl.order_name(I)
1892 ,personTbl.partition_id(I)
1893 ,personTbl.object_version_number(I)
1894 ,personTbl.global_person_id(I)
1895 ,personTbl.party_id(I)
1896 ,g_request_id
1897 ,g_prog_appl_id
1898 ,g_prog_id
1899 ,g_date
1900 ,g_date
1901 ,g_user_id
1902 ,g_login_id
1903 ,g_user_id
1904 ,g_date
1905 ,personTbl.attribute_category(I)
1906 ,personTbl.attribute1(I)
1907 ,personTbl.attribute2(I)
1908 ,personTbl.attribute3(I)
1909 ,personTbl.attribute4(I)
1910 ,personTbl.attribute5(I)
1911 ,personTbl.attribute6(I)
1912 ,personTbl.attribute7(I)
1913 ,personTbl.attribute8(I)
1914 ,personTbl.attribute9(I)
1915 ,personTbl.attribute10(I)
1916 ,personTbl.attribute11(I)
1917 ,personTbl.attribute12(I)
1918 ,personTbl.attribute13(I)
1919 ,personTbl.attribute14(I)
1920 ,personTbl.attribute15(I)
1921 ,personTbl.attribute16(I)
1922 ,personTbl.attribute17(I)
1923 ,personTbl.attribute18(I)
1924 ,personTbl.attribute19(I)
1925 ,personTbl.attribute20(I)
1926 ,personTbl.attribute21(I)
1927 ,personTbl.attribute22(I)
1928 ,personTbl.attribute23(I)
1929 ,personTbl.attribute24(I)
1930 ,personTbl.attribute25(I)
1931 ,personTbl.attribute26(I)
1932 ,personTbl.attribute27(I)
1933 ,personTbl.attribute28(I)
1934 ,personTbl.attribute29(I)
1935 ,personTbl.attribute30(I)
1936 ,personTbl.per_information_category(I)
1937 ,personTbl.per_information1(I)
1938 ,personTbl.per_information2(I)
1939 ,personTbl.per_information3(I)
1940 ,personTbl.per_information4(I)
1941 ,personTbl.per_information5(I)
1942 ,personTbl.per_information6(I)
1943 ,personTbl.per_information7(I)
1944 ,personTbl.per_information8(I)
1945 ,personTbl.per_information9(I)
1946 ,personTbl.per_information10(I)
1947 ,personTbl.per_information11(I)
1948 ,personTbl.per_information12(I)
1949 ,personTbl.per_information13(I)
1950 ,personTbl.per_information14(I)
1951 ,personTbl.per_information15(I)
1952 ,personTbl.per_information16(I)
1953 ,personTbl.per_information17(I)
1954 ,personTbl.per_information18(I)
1955 ,personTbl.per_information19(I)
1956 ,personTbl.per_information20(I)
1957 ,personTbl.per_information21(I)
1958 ,personTbl.per_information22(I)
1959 ,personTbl.per_information23(I)
1960 ,personTbl.per_information24(I)
1961 ,personTbl.per_information25(I)
1962 ,personTbl.per_information26(I)
1963 ,personTbl.per_information27(I)
1964 ,personTbl.per_information28(I)
1965 ,personTbl.per_information29(I)
1966 ,personTbl.per_information30(I)
1967 ,personTbl.direct_reports(I)
1968 ,personTbl.total_reports(I)
1969 );
1970
1971 EXCEPTION WHEN OTHERS THEN
1972 errbuf := errbuf||SQLERRM;
1973 retcode := '1';
1974 write_log(1, 'Error in dump_per_people: '||SQLCODE);
1975 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
1976 END dump_per_people;
1977
1978 PROCEDURE update_per_people(
1979 errbuf OUT NOCOPY VARCHAR2
1980 ,retcode OUT NOCOPY VARCHAR2
1981 ,p_eff_date IN DATE
1982 ,p_cnt IN NUMBER
1983 ) IS
1984 BEGIN
1985
1986 g_date := trunc(SYSDATE);
1987
1988 FORALL I IN 1 .. p_cnt
1989 UPDATE per_empdir_people
1990 SET person_key = personTbl.person_key(I)
1991 ,orig_system = personTbl.orig_system(I)
1992 ,orig_system_id = personTbl.orig_sytem_id(I)
1993 ,business_group_id = personTbl.business_group_id(I)
1994 ,legislation_code = personTbl.legislation_code(I)
1995 ,display_name = personTbl.display_name(I)
1996 ,full_name = personTbl.full_name(I)
1997 ,full_name_alternate = personTbl.full_name_alternate(I)
1998 ,last_name = personTbl.last_name(I)
1999 ,first_name = personTbl.first_name(I)
2000 ,last_name_alternate = personTbl.last_name_alternate(I)
2001 ,first_name_alternate = personTbl.first_name_alternate(I)
2002 ,pre_name_adjunct = personTbl.pre_name_adjunct(I)
2003 ,person_type = personTbl.person_type(I)
2004 ,user_name = personTbl.user_name(I)
2005 ,active = personTbl.active(I)
2006 ,employee_number = personTbl.employee_number(I)
2007 ,known_as = personTbl.known_as(I)
2008 ,middle_names = personTbl.middle_names(I)
2009 ,previous_last_name = personTbl.previous_last_name(I)
2010 ,start_date = personTbl.start_date(I)
2011 ,original_date_of_hire = persontbl.original_doh(i)
2012 ,email_address = personTbl.email_address(I)
2013 ,work_telephone = personTbl.work_telephone(I)
2014 ,mailstop = personTbl.mailstop(I)
2015 ,office_number = personTbl.office_number(I)
2016 ,order_name = personTbl.order_name(I)
2017 ,partition_id = personTbl.partition_id(I)
2018 ,object_version_number = personTbl.object_version_number(I)
2019 ,global_person_id = personTbl.global_person_id(I)
2020 ,party_id = personTbl.party_id(I)
2021 ,request_id = g_request_id
2022 ,program_application_id = g_prog_appl_id
2023 ,program_id = g_prog_id
2024 ,program_update_date = g_date
2025 ,last_update_date = g_date
2026 ,last_updated_by = g_user_id
2027 ,last_update_login = g_login_id
2028 ,created_by = g_user_id
2029 ,creation_date = g_date
2030 ,attribute_category = personTbl.attribute_category(I)
2031 ,attribute1 = personTbl.attribute1(I)
2032 ,attribute2 = personTbl.attribute2(I)
2033 ,attribute3 = personTbl.attribute3(I)
2034 ,attribute4 = personTbl.attribute4(I)
2035 ,attribute5 = personTbl.attribute5(I)
2036 ,attribute6 = personTbl.attribute6(I)
2037 ,attribute7 = personTbl.attribute7(I)
2038 ,attribute8 = personTbl.attribute8(I)
2039 ,attribute9 = personTbl.attribute9(I)
2040 ,attribute10 = personTbl.attribute10(I)
2041 ,attribute11 = personTbl.attribute11(I)
2042 ,attribute12 = personTbl.attribute12(I)
2043 ,attribute13 = personTbl.attribute13(I)
2044 ,attribute14 = personTbl.attribute14(I)
2045 ,attribute15 = personTbl.attribute15(I)
2046 ,attribute16 = personTbl.attribute16(I)
2047 ,attribute17 = personTbl.attribute17(I)
2048 ,attribute18 = personTbl.attribute18(I)
2049 ,attribute19 = personTbl.attribute19(I)
2050 ,attribute20 = personTbl.attribute20(I)
2051 ,attribute21 = personTbl.attribute21(I)
2052 ,attribute22 = personTbl.attribute22(I)
2053 ,attribute23 = personTbl.attribute23(I)
2054 ,attribute24 = personTbl.attribute24(I)
2055 ,attribute25 = personTbl.attribute25(I)
2056 ,attribute26 = personTbl.attribute26(I)
2057 ,attribute27 = personTbl.attribute27(I)
2058 ,attribute28 = personTbl.attribute28(I)
2059 ,attribute29 = personTbl.attribute29(I)
2060 ,attribute30 = personTbl.attribute30(I)
2061 ,per_information_category = personTbl.per_information_category(I)
2062 ,per_information1 = personTbl.per_information1(I)
2063 ,per_information2 = personTbl.per_information2(I)
2064 ,per_information3 = personTbl.per_information3(I)
2065 ,per_information4 = personTbl.per_information4(I)
2066 ,per_information5 = personTbl.per_information5(I)
2067 ,per_information6 = personTbl.per_information6(I)
2068 ,per_information7 = personTbl.per_information7(I)
2069 ,per_information8 = personTbl.per_information8(I)
2070 ,per_information9 = personTbl.per_information9(I)
2071 ,per_information10 = personTbl.per_information10(I)
2072 ,per_information11 = personTbl.per_information11(I)
2073 ,per_information12 = personTbl.per_information12(I)
2074 ,per_information13 = personTbl.per_information13(I)
2075 ,per_information14 = personTbl.per_information14(I)
2076 ,per_information15 = personTbl.per_information15(I)
2077 ,per_information16 = personTbl.per_information16(I)
2078 ,per_information17 = personTbl.per_information17(I)
2079 ,per_information18 = personTbl.per_information18(I)
2080 ,per_information19 = personTbl.per_information19(I)
2081 ,per_information20 = personTbl.per_information20(I)
2082 ,per_information21 = personTbl.per_information21(I)
2083 ,per_information22 = personTbl.per_information22(I)
2084 ,per_information23 = personTbl.per_information23(I)
2085 ,per_information24 = personTbl.per_information24(I)
2086 ,per_information25 = personTbl.per_information25(I)
2087 ,per_information26 = personTbl.per_information26(I)
2088 ,per_information27 = personTbl.per_information27(I)
2089 ,per_information28 = personTbl.per_information28(I)
2090 ,per_information29 = personTbl.per_information29(I)
2091 ,per_information30 = personTbl.per_information30(I)
2092 ,direct_reports = personTbl.direct_reports(I)
2093 ,total_reports = personTbl.total_reports(I)
2094 WHERE rowid = personTbl.row_id(I);
2095
2096 EXCEPTION WHEN OTHERS THEN
2097 errbuf := errbuf||SQLERRM;
2098 retcode := '1';
2099 write_log(1, 'Error in update_per_people: '||SQLCODE);
2100 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
2101 END update_per_people;
2102
2103 PROCEDURE open_per_people(
2104 p_cursor IN OUT NOCOPY cur_typ
2105 ,p_mode IN NUMBER
2106 ,p_eff_date IN DATE
2107 ) IS
2108
2109 query_str VARCHAR2(4000);
2110 BEGIN
2111
2112 IF (p_mode = 0) THEN
2113 query_str :=
2114 'SELECT /*+ parallel(ppf) */' ||
2115 'null,' ||
2116 'substr(upper(last_name)||'' ''||upper(first_name)||'' ''||upper(last_name)||'' ''||' ||
2117 'upper(list_name)||'' ''||' ||
2118 'decode(oi.org_information9' ||
2119 ' ,''KR'',per_information1||'' ''||per_information2||'' ''||per_information1||'' ''' ||
2120 ' ,''CN'',per_information14||'' ''||per_information15||'' ''||per_information14||'' ''' ||
2121 ' ,''JP'',per_information18||'' ''||per_information19||'' ''||per_information18||'' ''' ||
2122 ' ,'''')||' ||
2123 'upper(known_as)||'' ''||upper(ppf.email_address)||'' ''||' ||
2124 'translate(upper(ph.phone_number),''ABCDEFGHIJKLMNOPQRSTUVWXYZ ,.-()x/\'','' ''),1,2000),' ||
2125 ''''|| g_srcSystem ||''',' ||
2126 'ppf.person_id,' ||
2127 'ppf.business_group_id,' ||
2128 'oi.org_information9,' ||
2129 'ppf.list_name display_name,' ||
2130 'ppf.global_name full_name,' ||
2131 'ppf.local_name full_name_alternate,' ||
2132 'nvl(decode(oi.org_information9' ||
2133 ' ,''KR'', per_information1' ||
2134 ' ,''CN'', per_information14' ||
2135 ' ,ppf.last_name),ppf.last_name) last_name,' ||
2136 'decode(oi.org_information9' ||
2137 ' ,''KR'', per_information2' ||
2138 ' ,''CN'', per_information15' ||
2139 ' ,ppf.first_name) first_name,' ||
2140 'decode(oi.org_information9' ||
2141 ' ,''KR'', last_name' ||
2142 ' ,''CN'', last_name' ||
2143 ' ,''JP'', per_information18' ||
2144 ' ,NULL) last_name_alternate,' ||
2145 'decode(oi.org_information9' ||
2146 ' ,''KR'', first_name' ||
2147 ' ,''CN'', first_name' ||
2148 ' ,''JP'', per_information19' ||
2149 ' ,NULL) first_name_alternate,' ||
2150 'ppf.pre_name_adjunct,' ||
2151 'decode(ppf.current_npw_flag, ''Y'', ''C'', ''E'') person_type,' ||
2152 'NULL user_name,' ||
2153 '''Y'',' ||
2154 'ppf.employee_number,' ||
2155 'ppf.known_as,' ||
2156 'ppf.middle_names,' ||
2157 'ppf.previous_last_name,' ||
2158 'ppf.start_date,' ||
2159 'ppf.original_date_of_hire,' ||
2160 'ppf.email_address,' ||
2161 'ph.phone_number work_telephone,' ||
2162 'ppf.mailstop,' ||
2163 'ppf.office_number,' ||
2164 'ppf.order_name,' ||
2165 '1,' ||
2166 'ppf.object_version_number,' ||
2167 'ppf.global_person_id,' ||
2168 'ppf.party_id,' ||
2169 'ppf.attribute_category, ppf.attribute1, ppf.attribute2, ppf.attribute3, ppf.attribute4, ppf.attribute5,' ||
2170 'ppf.attribute6, ppf.attribute7, ppf.attribute8, ppf.attribute9, ppf.attribute10, ppf.attribute11,' ||
2171 'ppf.attribute12, ppf.attribute13, ppf.attribute14, ppf.attribute15, ppf.attribute16, ppf.attribute17,' ||
2172 'ppf.attribute18, ppf.attribute19, ppf.attribute20, ppf.attribute21, ppf.attribute22, ppf.attribute23,' ||
2173 'ppf.attribute24, ppf.attribute25, ppf.attribute26, ppf.attribute27, ppf.attribute28, ppf.attribute29,' ||
2174 'ppf.attribute30, ppf.per_information_category, ppf.per_information1, ppf.per_information2,' ||
2175 'ppf.per_information3, ppf.per_information4, ppf.per_information5, ppf.per_information6,' ||
2176 'ppf.per_information7, ppf.per_information8, ppf.per_information9, ppf.per_information10,' ||
2177 'ppf.per_information11, ppf.per_information12, per_information13, per_information14, per_information15,' ||
2178 'per_information16, per_information17, per_information18, per_information19, per_information20,' ||
2179 'per_information21, per_information22, per_information23, per_information24, per_information25,' ||
2180 'per_information26, per_information27, per_information28, per_information29, per_information30' ||
2181 ',NULL directs' ||
2182 ',NULL total' ||
2183 ' FROM per_people_f ppf, per_phones ph, hr_organization_information oi' ||
2184 ' WHERE :1 BETWEEN effective_start_date AND effective_end_date' ||
2185 ' AND (current_employee_flag = ''Y'' OR current_npw_flag = ''Y'')' ||
2186 ' AND ppf.business_group_id = oi.organization_id' ||
2187 ' AND oi.org_information_context = ''Business Group Information''' ||
2188 ' AND parent_table(+) = ''PER_ALL_PEOPLE_F''' ||
2189 ' AND parent_id(+) = ppf.person_id' ||
2190 ' AND phone_type(+) = ''W1''' ||
2191 ' AND :2 BETWEEN date_from(+) AND nvl(date_to(+),:3 + 1)' ||
2192 '/* Avoiding PK Violation */' ||
2193 ' AND ppf.person_id NOT IN' ||
2194 ' (SELECT person_id FROM per_all_people_f ippf' ||
2195 ' WHERE :4 BETWEEN effective_start_date AND effective_end_date' ||
2196 ' GROUP BY person_id HAVING count(*) > 1)';
2197 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
2198 query_str := query_str || ' AND ppf.person_id NOT IN '||
2199 ' (SELECT person_id FROM per_all_people_f lppf'||
2200 ' WHERE label_to_char(lppf.HR_ENTERPRISE) = ''C::ENT'' OR label_to_char(lppf.HR_ENTERPRISE) is null)';
2201 END IF;
2202
2203 OPEN p_cursor FOR query_str using p_eff_date,p_eff_date,p_eff_date,p_eff_date;
2204 ELSIF (p_mode = 1) THEN
2205 query_str :=
2206 'SELECT /*+ parallel(ppf) */' ||
2207 'hrdp.rowid,' ||
2208 'substr(upper(ppf.last_name)||'' ''||upper(ppf.first_name)||'' ''||upper(ppf.last_name)||'' ''||' ||
2209 'upper(ppf.list_name)||'' ''||' ||
2210 'decode(oi.org_information9' ||
2211 ',''KR'',ppf.per_information1||'' ''||ppf.per_information2||'' ''||ppf.per_information1||'' ''' ||
2212 ' ,''CN'',ppf.per_information14||'' ''||ppf.per_information15||'' ''||ppf.per_information14||'' ''' ||
2213 ' ,''JP'',ppf.per_information18||'' ''||ppf.per_information19||'' ''||ppf.per_information18||'' ''' ||
2214 ' ,'''')||' ||
2215 'upper(ppf.known_as)||'' ''||upper(ppf.email_address)||'' ''||' ||
2216 'translate(upper(ph.phone_number),''ABCDEFGHIJKLMNOPQRSTUVWXYZ ,.-()x/\'','' ''),1,2000),' ||
2217 '''' || g_srcSystem || ''',' ||
2218 'ppf.person_id,' ||
2219 'ppf.business_group_id,' ||
2220 'oi.org_information9,' ||
2221 'ppf.list_name display_name,' ||
2222 'ppf.global_name full_name,' ||
2223 'ppf.local_name full_name_alternate,' ||
2224 'nvl(decode(oi.org_information9' ||
2225 ' ,''KR'', ppf.per_information1' ||
2226 ' ,''CN'', ppf.per_information14' ||
2227 ' ,ppf.last_name),ppf.last_name) last_name,' ||
2228 'decode(oi.org_information9' ||
2229 ' ,''KR'', ppf.per_information2' ||
2230 ' ,''CN'', ppf.per_information15' ||
2231 ' ,ppf.first_name) first_name,' ||
2232 'decode(oi.org_information9' ||
2233 ' ,''KR'', ppf.last_name' ||
2234 ' ,''CN'', ppf.last_name' ||
2235 ' ,''JP'', ppf.per_information18' ||
2236 ' ,NULL) last_name_alternate,' ||
2237 'decode(oi.org_information9' ||
2238 ' ,''KR'', ppf.first_name' ||
2239 ' ,''CN'', ppf.first_name' ||
2240 ' ,''JP'', ppf.per_information19' ||
2241 ' ,NULL) first_name_alternate,' ||
2242 'ppf.pre_name_adjunct,' ||
2243 'decode(ppf.current_npw_flag, ''Y'', ''C'', ''E'') person_type,' ||
2244 'NULL user_name,' ||
2245 'decode (nvl(ppf.current_employee_flag,''N''), ''Y'', ''Y'',' ||
2246 ' decode(ppf.current_npw_flag,''Y'',''Y'',''N'')) active,' ||
2247 'ppf.employee_number,' ||
2248 'ppf.known_as,' ||
2249 'ppf.middle_names,' ||
2250 'ppf.previous_last_name,' ||
2251 'ppf.start_date,' ||
2252 'ppf.original_date_of_hire,' ||
2253 'ppf.email_address,' ||
2254 'ph.phone_number work_telephone,' ||
2255 'ppf.mailstop,' ||
2256 'ppf.office_number,' ||
2257 'ppf.order_name,' ||
2258 '1,' ||
2259 'ppf.object_version_number,' ||
2260 'ppf.global_person_id,' ||
2261 'ppf.party_id,' ||
2262 'ppf.attribute_category, ppf.attribute1, ppf.attribute2, ppf.attribute3, ppf.attribute4, ppf.attribute5,' ||
2263 'ppf.attribute6, ppf.attribute7, ppf.attribute8, ppf.attribute9, ppf.attribute10, ppf.attribute11,' ||
2264 'ppf.attribute12, ppf.attribute13, ppf.attribute14, ppf.attribute15, ppf.attribute16, ppf.attribute17,' ||
2265 'ppf.attribute18, ppf.attribute19, ppf.attribute20, ppf.attribute21, ppf.attribute22, ppf.attribute23,' ||
2266 'ppf.attribute24, ppf.attribute25, ppf.attribute26, ppf.attribute27, ppf.attribute28, ppf.attribute29,' ||
2267 'ppf.attribute30, ppf.per_information_category, ppf.per_information1, ppf.per_information2,' ||
2268 'ppf.per_information3, ppf.per_information4, ppf.per_information5, ppf.per_information6,' ||
2269 'ppf.per_information7, ppf.per_information8, ppf.per_information9, ppf.per_information10,' ||
2270 'ppf.per_information11, ppf.per_information12, ppf.per_information13, ppf.per_information14,' ||
2271 'ppf.per_information15, ppf.per_information16, ppf.per_information17, ppf.per_information18,' ||
2272 'ppf.per_information19, ppf.per_information20, ppf.per_information21, ppf.per_information22,' ||
2273 'ppf.per_information23, ppf.per_information24, ppf.per_information25, ppf.per_information26,' ||
2274 'ppf.per_information27, ppf.per_information28, ppf.per_information29, ppf.per_information30' ||
2275 ',hrdp.direct_reports' ||
2276 ',hrdp.total_reports ' ||
2277 ' FROM per_people_f ppf, per_phones ph' ||
2278 ' ,hr_organization_information oi, per_empdir_people hrdp' ||
2279 ' WHERE :1 BETWEEN effective_start_date AND effective_end_date' ||
2280 ' AND hrdp.orig_system = ''' || g_srcSystem || '''' ||
2281 ' AND ppf.person_id = hrdp.orig_system_id' ||
2282 ' AND (ppf.object_version_number <> hrdp.object_version_number OR' ||
2283 ' nvl(hrdp.work_telephone,''#'') <> nvl(ph.phone_number,''#'') OR' ||
2284 ' ppf.effective_start_date >= hrdp.last_update_date) ' ||
2285 ' AND ppf.business_group_id = oi.organization_id' ||
2286 ' AND oi.org_information_context =''Business Group Information''' ||
2287 ' AND ph.parent_table(+) = ''PER_ALL_PEOPLE_F''' ||
2288 ' AND ph.parent_id(+) = ppf.person_id' ||
2289 ' AND ph.phone_type(+) = ''W1''' ||
2290 ' AND :2 BETWEEN date_from(+) AND nvl(date_to(+),:3 +1)';
2291 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
2292 query_str := query_str || ' AND ppf.person_id NOT IN '||
2293 ' (SELECT person_id FROM per_all_people_f lppf'||
2294 ' WHERE label_to_char(lppf.HR_ENTERPRISE) = ''C::ENT'' OR label_to_char(lppf.HR_ENTERPRISE) is null)';
2295 END IF;
2296 OPEN p_cursor FOR query_str using p_eff_date,p_eff_date,p_eff_date;
2297 ELSIF (p_mode = 2) THEN
2298 query_str :=
2299 'SELECT /*+ parallel(ppf) */' ||
2300 'null,' ||
2301 'substr(upper(last_name)||'' ''||upper(first_name)||'' ''||upper(last_name)||'' ''||' ||
2302 'upper(list_name)||'' ''||' ||
2303 'decode(oi.org_information9' ||
2304 ' ,''KR'',per_information1||'' ''||per_information2||'' ''||per_information1||'' ''' ||
2305 ' ,''CN'',per_information14||'' ''||per_information15||'' ''||per_information14||'' ''' ||
2306 ' ,''JP'',per_information18||'' ''||per_information19||'' ''||per_information18||'' ''' ||
2307 ' ,'''')||' ||
2308 'upper(known_as)||'' ''||upper(ppf.email_address)||'' ''||' ||
2309 'translate(upper(ph.phone_number),''ABCDEFGHIJKLMNOPQRSTUVWXYZ ,.-()x/\'','' ''),1,2000),' ||
2310 ''''|| g_srcSystem ||''',' ||
2311 'ppf.person_id,' ||
2312 'ppf.business_group_id,' ||
2313 'oi.org_information9,' ||
2314 'ppf.list_name display_name,' ||
2315 'ppf.global_name full_name,' ||
2316 'ppf.local_name full_name_alternate,' ||
2317 'nvl(decode(oi.org_information9' ||
2318 ' ,''KR'', per_information1' ||
2319 ' ,''CN'', per_information14' ||
2320 ' ,ppf.last_name),ppf.last_name) last_name,' ||
2321 'decode(oi.org_information9' ||
2322 ' ,''KR'', per_information2' ||
2323 ' ,''CN'', per_information15' ||
2324 ' ,ppf.first_name) first_name,' ||
2325 'decode(oi.org_information9' ||
2326 ' ,''KR'', last_name' ||
2327 ' ,''CN'', last_name' ||
2328 ' ,''JP'', per_information18' ||
2329 ' ,NULL) last_name_alternate,' ||
2330 'decode(oi.org_information9' ||
2331 ' ,''KR'', first_name' ||
2332 ' ,''CN'', first_name' ||
2333 ' ,''JP'', per_information19' ||
2334 ' ,NULL) first_name_alternate,' ||
2335 'ppf.pre_name_adjunct,' ||
2336 'decode(ppf.current_npw_flag, ''Y'', ''C'', ''E'') person_type,' ||
2337 'NULL user_name,' ||
2338 '''Y'',' ||
2339 'ppf.employee_number,' ||
2340 'ppf.known_as,' ||
2341 'ppf.middle_names,' ||
2342 'ppf.previous_last_name,' ||
2343 'ppf.start_date,' ||
2344 'ppf.original_date_of_hire,' ||
2345 'ppf.email_address,' ||
2346 'ph.phone_number work_telephone,' ||
2347 'ppf.mailstop,' ||
2348 'ppf.office_number,' ||
2349 'ppf.order_name,' ||
2350 '1,' ||
2351 'ppf.object_version_number,' ||
2352 'ppf.global_person_id,' ||
2353 'ppf.party_id,' ||
2354 'ppf.attribute_category, ppf.attribute1, ppf.attribute2, ppf.attribute3, ppf.attribute4, ppf.attribute5,' ||
2355 'ppf.attribute6, ppf.attribute7, ppf.attribute8, ppf.attribute9, ppf.attribute10, ppf.attribute11,' ||
2356 'ppf.attribute12, ppf.attribute13, ppf.attribute14, ppf.attribute15, ppf.attribute16, ppf.attribute17,' ||
2357 'ppf.attribute18, ppf.attribute19, ppf.attribute20, ppf.attribute21, ppf.attribute22, ppf.attribute23,' ||
2358 'ppf.attribute24, ppf.attribute25, ppf.attribute26, ppf.attribute27, ppf.attribute28, ppf.attribute29,' ||
2359 'ppf.attribute30, ppf.per_information_category, ppf.per_information1, ppf.per_information2,' ||
2360 'ppf.per_information3, ppf.per_information4, ppf.per_information5, ppf.per_information6,' ||
2361 'ppf.per_information7, ppf.per_information8, ppf.per_information9, ppf.per_information10,' ||
2362 'ppf.per_information11, ppf.per_information12, per_information13, per_information14, per_information15,' ||
2363 'per_information16, per_information17, per_information18, per_information19, per_information20,' ||
2364 'per_information21, per_information22, per_information23, per_information24, per_information25,' ||
2365 'per_information26, per_information27, per_information28, per_information29, per_information30' ||
2366 ',NULL directs' ||
2367 ',NULL total' ||
2368 ' FROM per_people_f ppf, per_phones ph, hr_organization_information oi' ||
2369 ' WHERE :1 BETWEEN effective_start_date AND effective_end_date' ||
2370 ' AND (current_employee_flag = ''Y'' OR current_npw_flag = ''Y'')' ||
2371 ' AND ppf.business_group_id = oi.organization_id' ||
2372 ' AND oi.org_information_context = ''Business Group Information''' ||
2373 ' AND parent_table(+) = ''PER_ALL_PEOPLE_F''' ||
2374 ' AND parent_id(+) = ppf.person_id' ||
2375 ' AND phone_type(+) = ''W1''' ||
2376 ' AND :2 BETWEEN date_from(+) AND nvl(date_to(+),:3 + 1)' ||
2377 '/* Avoiding PK Violation */' ||
2378 ' AND ppf.person_id NOT IN' ||
2379 ' (SELECT person_id FROM per_all_people_f ippf' ||
2380 ' WHERE :4 BETWEEN effective_start_date AND effective_end_date' ||
2381 ' GROUP BY person_id HAVING count(*) > 1)' ||
2382 '/* Picking up not exists from per_empdir_people */' ||
2383 ' AND NOT EXISTS (SELECT ''e'' from per_empdir_people ip' ||
2384 ' WHERE ip.orig_system_id = ppf.person_id' ||
2385 ' AND ip.orig_system = ''' || g_srcSystem || ''')';
2386 IF hr_multi_tenancy_pkg.get_system_model = 'B' THEN
2387 query_str := query_str || ' AND ppf.person_id NOT IN '||
2388 ' (SELECT person_id FROM per_all_people_f lppf'||
2389 ' WHERE label_to_char(lppf.HR_ENTERPRISE) = ''C::ENT'' OR label_to_char(lppf.HR_ENTERPRISE) is null)';
2390 END IF;
2391 OPEN p_cursor FOR query_str using p_eff_date,p_eff_date,p_eff_date,p_eff_date;
2392
2393 END IF;
2394 END open_per_people;
2395
2396 PROCEDURE bulk_process_per_people(
2397 p_mode IN NUMBER
2398 ,p_cnt OUT NOCOPY NUMBER
2399 ,errbuf OUT NOCOPY VARCHAR2
2400 ,retcode OUT NOCOPY VARCHAR2
2401 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
2402 ) IS
2403
2404 l_cursor cur_typ;
2405 l_flg BOOLEAN:= FALSE;
2406
2407 BEGIN
2408
2409 p_cnt := 0;
2410 l_flg := per_empdir_LEG_OVERRIDE.isOverrideEnabled('PEOPLE');
2411
2412 open_per_people(
2413 l_cursor
2414 ,p_mode
2415 ,p_eff_date
2416 );
2417
2418 LOOP
2419 BEGIN
2420 FETCH l_cursor BULK COLLECT
2421 INTO personTbl.row_id
2422 ,personTbl.person_key
2423 ,personTbl.orig_system
2424 ,personTbl.orig_sytem_id
2425 ,personTbl.business_group_id
2426 ,personTbl.legislation_code
2427 ,personTbl.display_name
2428 ,personTbl.full_name
2429 ,personTbl.full_name_alternate
2430 ,personTbl.last_name
2431 ,personTbl.first_name
2432 ,personTbl.last_name_alternate
2433 ,personTbl.first_name_alternate
2434 ,personTbl.pre_name_adjunct
2435 ,personTbl.person_type
2436 ,personTbl.user_name
2437 ,personTbl.active
2438 ,personTbl.employee_number
2439 ,personTbl.known_as
2440 ,personTbl.middle_names
2441 ,personTbl.previous_last_name
2442 ,personTbl.start_date
2443 ,personTbl.original_DOH
2444 ,personTbl.email_address
2445 ,personTbl.work_telephone
2446 ,personTbl.mailstop
2447 ,personTbl.office_number
2448 ,personTbl.order_name
2449 ,personTbl.partition_id
2450 ,personTbl.object_version_number
2451 ,personTbl.global_person_id
2452 ,personTbl.party_id
2453 ,personTbl.attribute_category
2454 ,personTbl.attribute1
2455 ,personTbl.attribute2
2456 ,personTbl.attribute3
2457 ,personTbl.attribute4
2458 ,personTbl.attribute5
2459 ,personTbl.attribute6
2460 ,personTbl.attribute7
2461 ,personTbl.attribute8
2462 ,personTbl.attribute9
2463 ,personTbl.attribute10
2464 ,personTbl.attribute11
2465 ,personTbl.attribute12
2466 ,personTbl.attribute13
2467 ,personTbl.attribute14
2468 ,personTbl.attribute15
2469 ,personTbl.attribute16
2470 ,personTbl.attribute17
2471 ,personTbl.attribute18
2472 ,personTbl.attribute19
2473 ,personTbl.attribute20
2474 ,personTbl.attribute21
2475 ,personTbl.attribute22
2476 ,personTbl.attribute23
2477 ,personTbl.attribute24
2478 ,personTbl.attribute25
2479 ,personTbl.attribute26
2480 ,personTbl.attribute27
2481 ,personTbl.attribute28
2482 ,personTbl.attribute29
2483 ,personTbl.attribute30
2484 ,personTbl.per_information_category
2485 ,personTbl.per_information1
2486 ,personTbl.per_information2
2487 ,personTbl.per_information3
2488 ,personTbl.per_information4
2489 ,personTbl.per_information5
2490 ,personTbl.per_information6
2491 ,personTbl.per_information7
2492 ,personTbl.per_information8
2493 ,personTbl.per_information9
2494 ,personTbl.per_information10
2495 ,personTbl.per_information11
2496 ,personTbl.per_information12
2497 ,personTbl.per_information13
2498 ,personTbl.per_information14
2499 ,personTbl.per_information15
2500 ,personTbl.per_information16
2501 ,personTbl.per_information17
2502 ,personTbl.per_information18
2503 ,personTbl.per_information19
2504 ,personTbl.per_information20
2505 ,personTbl.per_information21
2506 ,personTbl.per_information22
2507 ,personTbl.per_information23
2508 ,personTbl.per_information24
2509 ,personTbl.per_information25
2510 ,personTbl.per_information26
2511 ,personTbl.per_information27
2512 ,personTbl.per_information28
2513 ,personTbl.per_information29
2514 ,personTbl.per_information30
2515 ,personTbl.direct_reports
2516 ,personTbl.total_reports LIMIT g_commit_size;
2517
2518 IF personTbl.person_key.count <= 0 THEN
2519 CLOSE l_cursor;
2520 EXIT;
2521 END IF;
2522
2523 p_cnt := p_cnt + personTbl.person_key.count;
2524
2525 IF l_flg THEN
2526 per_empdir_leg_override.people(
2527 errbuf => errbuf
2528 ,retcode => retcode
2529 ,p_eff_date => p_eff_date
2530 ,p_cnt => personTbl.person_key.count
2531 ,p_srcsystem => g_srcSystem);
2532 END IF;
2533
2534 IF (p_mode = '0' OR p_mode = '2') THEN
2535 dump_per_people(
2536 errbuf
2537 ,retcode
2538 ,p_eff_date
2539 ,personTbl.person_key.count
2540 );
2541 ElSIF (p_mode = '1') THEN
2542 update_per_people(
2543 errbuf
2544 ,retcode
2545 ,p_eff_date
2546 ,personTbl.person_key.count
2547 );
2548 END IF;
2549
2550 COMMIT;
2551
2552 IF l_cursor%NOTFOUND THEN
2553 CLOSE l_cursor;
2554 EXIT;
2555 END IF;
2556 END;
2557 END LOOP;
2558 COMMIT;
2559
2560 EXCEPTION WHEN OTHERS THEN
2561 errbuf := errbuf||SQLERRM;
2562 retcode := '1';
2563 write_log(1, 'Error in bulk_process_per_people: '||SQLCODE);
2564 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
2565
2566 END bulk_process_per_people;
2567
2568 PROCEDURE merge_per_people(
2569 errbuf OUT NOCOPY VARCHAR2
2570 ,retcode OUT NOCOPY VARCHAR2
2571 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
2572 ) IS
2573
2574 l_cnt NUMBER :=0;
2575
2576 BEGIN
2577
2578
2579 write_log(1, 'Begin merge per people: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2580
2581 g_date := trunc(SYSDATE);
2582
2583 bulk_process_per_people(
2584 1
2585 ,l_cnt
2586 ,errbuf
2587 ,retcode
2588 ,p_eff_date
2589 );
2590
2591 write_log(1, 'Total # of records updated for per_empdir_people: '||l_cnt);
2592 write_log(2, 'Total # of records updated for per_empdir_people: '||l_cnt);
2593
2594 bulk_process_per_people(
2595 2
2596 ,l_cnt
2597 ,errbuf
2598 ,retcode
2599 ,p_eff_date
2600 );
2601
2602 write_log(1, 'Total # of new records processed for per_empdir_people: '||l_cnt);
2603 write_log(2, 'Total # of new records processed for per_empdir_people: '||l_cnt);
2604 write_log(1, 'End merge per people: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2605
2606
2607 EXCEPTION WHEN OTHERS THEN
2608 errbuf := errbuf||SQLERRM;
2609 retcode := '1';
2610 write_log(1, 'Error in merge_per_people: '||SQLCODE);
2611 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
2612 END merge_per_people;
2613
2614 PROCEDURE populate_per_people(
2615 errbuf OUT NOCOPY VARCHAR2
2616 ,retcode OUT NOCOPY VARCHAR2
2617 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
2618 ) IS
2619
2620 l_cnt NUMBER:= 0;
2621
2622 BEGIN
2623
2624
2625 write_log(1, 'Begin populating per people: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2626
2627 EXECUTE IMMEDIATE 'ALTER TABLE '||g_schema_owner||'.PER_EMPDIR_PEOPLE TRUNCATE PARTITION internal REUSE STORAGE';
2628 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_PEOPLE_PK REBUILD';
2629 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_PEOPLE_N1 REBUILD';
2630
2631 bulk_process_per_people(
2632 0
2633 ,l_cnt
2634 ,errbuf
2635 ,retcode
2636 ,p_eff_date
2637 );
2638
2639 write_log(1, 'Total # of records processed for per_empdir_people: '||l_cnt);
2640 write_log(2, 'Total # of records processed for per_empdir_people: '||l_cnt);
2641 write_log(1, 'End populating per people: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2642
2643 EXCEPTION WHEN OTHERS THEN
2644 errbuf := errbuf||SQLERRM;
2645 retcode := '1';
2646 write_log(1, 'Error in populate_per_people: '||SQLCODE);
2647 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
2648 END populate_per_people;
2649
2650 PROCEDURE merge_per_loctl(
2651 errbuf OUT NOCOPY VARCHAR2
2652 ,retcode OUT NOCOPY VARCHAR2
2653 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
2654 ) IS
2655
2656 l_cnt NUMBER:= 0;
2657
2658 BEGIN
2659
2660
2661 write_log(1, 'Begin merge per loctl: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2662
2663 g_date := trunc(SYSDATE);
2664
2665 UPDATE per_empdir_locations_tl loc
2666 SET (orig_system, orig_system_id, location_code, description,
2667 language, source_lang, object_version_number, partition_id,
2668 last_update_date, last_update_by, created_by, creation_date,
2669 request_id, program_application_id, program_id, program_update_date)
2670 = (SELECT
2671 g_srcSystem, ltl.location_id, ltl.location_code, ltl.description,
2672 ltl.language, ltl.source_lang, l.object_version_number, 1,
2673 g_date, g_user_id, g_user_id, g_date, g_request_id,
2674 g_prog_appl_id, g_prog_id, g_date
2675 FROM hr_locations_all_tl ltl, hr_locations_all l
2676 WHERE ltl.location_id = l.location_id
2677 AND ltl.language = loc.language
2678 AND ltl.location_id = loc.orig_system_id
2679 AND loc.orig_system = g_srcSystem)
2680 WHERE EXISTS (SELECT 'e' FROM hr_locations_all ol
2681 WHERE loc.object_version_number <> ol.object_version_number
2682 AND loc.orig_system_id = ol.location_id
2683 AND loc.orig_system = g_srcSystem)
2684 AND loc.orig_system = g_srcSystem;
2685
2686 l_cnt := sql%rowcount;
2687 COMMIT;
2688
2689 write_log(1, 'Total # of records updated for per_empdir_locations_tl: '||l_cnt);
2690 write_log(2, 'Total # of records updated for per_empdir_locations_tl: '||l_cnt);
2691
2692 INSERT /*+ parallel(loc) append */ INTO per_empdir_locations_tl loc(
2693 ORIG_SYSTEM,
2694 ORIG_SYSTEM_ID,
2695 LOCATION_CODE,
2696 DESCRIPTION,
2697 LANGUAGE,
2698 SOURCE_LANG,
2699 OBJECT_VERSION_NUMBER,
2700 PARTITION_ID ,
2701 LAST_UPDATE_DATE,
2702 LAST_UPDATE_BY,
2703 CREATED_BY ,
2704 CREATION_DATE,
2705 REQUEST_ID,
2706 PROGRAM_APPLICATION_ID,
2707 PROGRAM_ID,
2708 PROGRAM_UPDATE_DATE)
2709 SELECT /*+ parallel(ltl) */
2710 g_srcSystem,
2711 ltl.location_id,
2712 ltl.location_code,
2713 ltl.description,
2714 ltl.language,
2715 ltl.source_lang,
2716 l.object_version_number,
2717 1,
2718 g_date,
2719 g_user_id,
2720 g_user_id,
2721 g_date,
2722 g_request_id,
2723 g_prog_appl_id,
2724 g_prog_id,
2725 g_date
2726 FROM hr_locations_all_tl ltl, hr_locations_all l, per_empdir_locations pel
2727 WHERE ltl.location_id = l.location_id
2728 AND pel.orig_system_id = l.location_id
2729 AND pel.orig_system = g_srcSystem
2730 AND NOT EXISTS (SELECT 'e' from per_empdir_locations_tl il
2731 WHERE il.orig_system_id = ltl.location_id
2732 AND il.orig_system = g_srcSystem);
2733
2734 l_cnt := sql%rowcount;
2735 COMMIT;
2736
2737 write_log(1, 'Total # of new records processed for per_empdir_locations_tl: '||l_cnt);
2738 write_log(2, 'Total # of new records processed for per_empdir_locations_tl: '||l_cnt);
2739 write_log(1, 'End merge per loctl: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2740
2741 EXCEPTION WHEN OTHERS THEN
2742 errbuf := errbuf||SQLERRM;
2743 retcode := '1';
2744 write_log(1, 'Error in merge_per_loctl: '||SQLCODE);
2745 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
2746
2747 END merge_per_loctl;
2748
2749 PROCEDURE merge_per_phones(
2750 errbuf OUT NOCOPY VARCHAR2
2751 ,retcode OUT NOCOPY VARCHAR2
2752 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
2753 ) IS
2754
2755 l_cnt NUMBER:= 0;
2756
2757 BEGIN
2758
2759 write_log(1, 'Begin merge per phones: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2760
2761 g_date := trunc(SYSDATE);
2762
2763 UPDATE per_empdir_phones p
2764 SET (orig_system, orig_system_id, date_from, date_to, phone_type, phone_number,
2765 phone_key, parent_id, parent_table, object_version_number,
2766 partition_id, request_id, program_application_id, program_id,
2767 program_update_date, last_update_date, last_updated_by,
2768 last_update_login, created_by, creation_date)
2769 = (SELECT
2770 g_srcSystem, phone_id, date_from, date_to, phone_type, phone_number,
2771 nvl(translate(upper(phone_number),'ABCDEFGHIJKLMNOPQRSTUVWXYZ ,.-()x/\',' '),'##'),
2772 parent_id, parent_table, object_version_number, 1,
2773 g_request_id, g_prog_appl_id, g_prog_id, g_date,
2774 g_date, g_user_id, g_login_id, g_user_id, g_date
2775 FROM per_phones ph
2776 WHERE p_eff_date BETWEEN DATE_FROM AND nvl(DATE_TO, p_eff_date+1)
2777 AND ph.phone_id = p.orig_system_id
2778 AND p.orig_system = g_srcSystem)
2779 WHERE EXISTS (SELECT 'e' FROM per_phones oph
2780 WHERE p.object_version_number <> oph.object_version_number
2781 AND p_eff_date BETWEEN DATE_FROM AND nvl(DATE_TO, p_eff_date+1)-- Added for bug#13862147
2782 AND p.orig_system_id = oph.phone_id
2783 AND p.orig_system = g_srcSystem)
2784 AND p.orig_system = g_srcSystem;
2785
2786 l_cnt := sql%rowcount;
2787 COMMIT;
2788
2789 write_log(1, 'Total # of records updated for per_empdir_phones: '||l_cnt);
2790 write_log(2, 'Total # of records updated for per_empdir_phones: '||l_cnt);
2791
2792 INSERT /*+ parallel(hrd) append */ INTO per_empdir_phones hrd(
2793 ORIG_SYSTEM ,
2794 ORIG_SYSTEM_ID ,
2795 DATE_FROM,
2796 DATE_TO ,
2797 PHONE_TYPE ,
2798 PHONE_NUMBER,
2799 PHONE_KEY ,
2800 PARENT_ID,
2801 PARENT_TABLE,
2802 OBJECT_VERSION_NUMBER,
2803 PARTITION_ID,
2804 REQUEST_ID,
2805 PROGRAM_APPLICATION_ID,
2806 PROGRAM_ID,
2807 PROGRAM_UPDATE_DATE,
2808 LAST_UPDATE_DATE,
2809 LAST_UPDATED_BY,
2810 LAST_UPDATE_LOGIN,
2811 CREATED_BY ,
2812 CREATION_DATE)
2813 select /*+ parallel(ph) */
2814 g_srcSystem,
2815 phone_id,
2816 date_from,
2817 date_to,
2818 phone_type,
2819 phone_number,
2820 nvl(translate(upper(phone_number),'ABCDEFGHIJKLMNOPQRSTUVWXYZ ,.-()x/\',' '),'##'),
2821 parent_id,
2822 parent_table,
2823 object_version_number,
2824 1,
2825 g_request_id,
2826 g_prog_appl_id,
2827 g_prog_id,
2828 g_date,
2829 g_date,
2830 g_user_id,
2831 g_login_id,
2832 g_user_id,
2833 g_date
2834 FROM per_phones ph
2835 WHERE p_eff_date BETWEEN DATE_FROM AND nvl(DATE_TO, p_eff_date+1)
2836 /* Picking up not exists from per_empdir_phones */
2837 AND NOT EXISTS (SELECT 'e' from per_empdir_phones iph
2838 WHERE iph.orig_system_id = ph.phone_id
2839 AND iph.orig_system = g_srcSystem);
2840
2841 l_cnt := sql%rowcount;
2842 COMMIT;
2843
2844 write_log(1, 'Total # of new records processed for per_empdir_phones: '||l_cnt);
2845 write_log(2, 'Total # of new records processed for per_empdir_phones: '||l_cnt);
2846 write_log(1, 'End merge per phones: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2847
2848 EXCEPTION WHEN OTHERS THEN
2849 errbuf := errbuf||SQLERRM;
2850 retcode := '1';
2851 write_log(1, 'Error in merge_per_phones: '||SQLCODE);
2852 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
2853
2854 END merge_per_phones;
2855
2856 PROCEDURE merge_per_locations(
2857 errbuf OUT NOCOPY VARCHAR2
2858 ,retcode OUT NOCOPY VARCHAR2
2859 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
2860 ) IS
2861
2862 l_cnt NUMBER:= 0;
2863
2864 BEGIN
2865
2866
2867 write_log(1, 'Begin merge per locations: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2868
2869 g_date := trunc(SYSDATE);
2870
2871 bulk_process_per_locations(
2872 1
2873 ,l_cnt
2874 ,errbuf
2875 ,retcode
2876 ,p_eff_date
2877 );
2878
2879 write_log(1, 'Total # of records updated for per_empdir_locations: '||l_cnt);
2880 write_log(2, 'Total # of records updated for per_empdir_locations: '||l_cnt);
2881
2882 bulk_process_per_locations(
2883 2
2884 ,l_cnt
2885 ,errbuf
2886 ,retcode
2887 ,p_eff_date
2888 );
2889
2890 write_log(1, 'Total # of new records processed for per_empdir_locations: '||l_cnt);
2891 write_log(2, 'Total # of new records processed for per_empdir_locations: '||l_cnt);
2892 write_log(1, 'End merge per locations: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2893
2894 EXCEPTION WHEN OTHERS THEN
2895 errbuf := errbuf||SQLERRM;
2896 retcode := '1';
2897 write_log(1, 'Error in merge_per_locations: '||SQLCODE);
2898 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
2899
2900 END merge_per_locations;
2901
2902 PROCEDURE merge_per_jobs(
2903 errbuf OUT NOCOPY VARCHAR2
2904 ,retcode OUT NOCOPY VARCHAR2
2905 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
2906 ) IS
2907
2908 l_cnt NUMBER:= 0;
2909 l_ucnt NUMBER:= 0;
2910 l_icnt NUMBER:= 0;
2911
2912 BEGIN
2913
2914
2915 write_log(1, 'Begin merge per jobs: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2916
2917 g_date := trunc(SYSDATE);
2918
2919 FOR I in c_organizations LOOP
2920
2921 write_log(1,'Processing jobs for BusGrpId: '||I.organization_id||
2922 ' using SegList: '||I.slist);
2923
2924 bulk_process_per_jobs(
2925 1
2926 ,l_cnt
2927 ,errbuf
2928 ,retcode
2929 ,p_eff_date
2930 ,I.slist
2931 ,I.organization_id
2932 );
2933
2934 l_ucnt := l_ucnt + l_cnt;
2935
2936 bulk_process_per_jobs(
2937 2
2938 ,l_cnt
2939 ,errbuf
2940 ,retcode
2941 ,p_eff_date
2942 ,I.slist
2943 ,I.organization_id
2944 );
2945
2946 l_icnt := l_icnt + l_cnt;
2947
2948 END LOOP;
2949
2950 write_log(1, 'Total # of records updated for per_empdir_jobs: '||l_ucnt);
2951 write_log(2, 'Total # of records updated for per_empdir_jobs: '||l_ucnt);
2952 write_log(1, 'Total # of new records processed for per_empdir_jobs: '||l_icnt);
2953 write_log(2, 'Total # of new records processed for per_empdir_jobs: '||l_icnt);
2954 write_log(1, 'End merge per jobs: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2955
2956 EXCEPTION WHEN OTHERS THEN
2957 errbuf := errbuf||SQLERRM;
2958 retcode := '1';
2959 write_log(1, 'Error in merge_per_jobs: '||SQLCODE);
2960 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
2961
2962 END merge_per_jobs;
2963
2964
2965 PROCEDURE merge_per_pos(
2966 errbuf OUT NOCOPY VARCHAR2
2967 ,retcode OUT NOCOPY VARCHAR2
2968 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
2969 ) IS
2970
2971 l_cnt NUMBER:= 0;
2972
2973 BEGIN
2974
2975
2976 write_log(1, 'Begin merge per pos: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
2977
2978 g_date := trunc(SYSDATE);
2979
2980 bulk_process_hr_pos(
2981 1
2982 ,l_cnt
2983 ,errbuf
2984 ,retcode
2985 ,p_eff_date
2986 );
2987
2988 write_log(1, 'Total # of records updated for per_empdir_positions: '||l_cnt);
2989 write_log(2, 'Total # of records updated for per_empdir_positions: '||l_cnt);
2990
2991 bulk_process_hr_pos(
2992 2
2993 ,l_cnt
2994 ,errbuf
2995 ,retcode
2996 ,p_eff_date
2997 );
2998
2999 write_log(1, 'Total # of new records processed for per_empdir_positions: '||l_cnt);
3000 write_log(2, 'Total # of new records processed for per_empdir_positions: '||l_cnt);
3001 write_log(1, 'End merge per pos: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3002
3003 EXCEPTION WHEN OTHERS THEN
3004 errbuf := errbuf||SQLERRM;
3005 retcode := '1';
3006 write_log(1, 'Error in merge_per_pos: '||SQLCODE);
3007 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3008
3009 END merge_per_pos;
3010
3011 PROCEDURE merge_hr_orgs(
3012 errbuf OUT NOCOPY VARCHAR2
3013 ,retcode OUT NOCOPY VARCHAR2
3014 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
3015 ) IS
3016
3017 l_cnt NUMBER:= 0;
3018
3019 BEGIN
3020
3021 write_log(1, 'Begin merge hr organizations: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3022
3023 g_date := trunc(SYSDATE);
3024
3025 bulk_process_hr_orgs(
3026 1
3027 ,l_cnt
3028 ,errbuf
3029 ,retcode
3030 ,p_eff_date
3031 );
3032
3033 write_log(1, 'Total # of records updated for per_empdir_organizations: '||l_cnt);
3034 write_log(2, 'Total # of records updated for per_empdir_organizations: '||l_cnt);
3035
3036 bulk_process_hr_orgs(
3037 2
3038 ,l_cnt
3039 ,errbuf
3040 ,retcode
3041 ,p_eff_date
3042 );
3043
3044 write_log(1, 'Total # of new records processed for per_empdir_organizations: '||l_cnt);
3045 write_log(2, 'Total # of new records processed for per_empdir_organizations: '||l_cnt);
3046 write_log(1, 'End merge hr organizations: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3047
3048 EXCEPTION WHEN OTHERS THEN
3049 errbuf := errbuf||SQLERRM;
3050 retcode := '1';
3051 write_log(1, 'Error in merge_hr_orgs: '||SQLCODE);
3052 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3053
3054 END merge_hr_orgs;
3055
3056 PROCEDURE merge_per_asg(
3057 errbuf OUT NOCOPY VARCHAR2
3058 ,retcode OUT NOCOPY VARCHAR2
3059 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
3060 ,p_multi_asg IN VARCHAR2
3061 ) IS
3062
3063 l_cnt NUMBER:= 0;
3064
3065 BEGIN
3066
3067
3068 write_log(1, 'Begin merge per assginments: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3069
3070 g_date := trunc(SYSDATE);
3071
3072 bulk_process_per_asg(
3073 1
3074 ,l_cnt
3075 ,errbuf
3076 ,retcode
3077 ,p_eff_date
3078 ,p_multi_asg
3079 );
3080
3081 write_log(1, 'Total # of records updated for per_empdir_assignments: '||l_cnt);
3082 write_log(2, 'Total # of records updated for per_empdir_assignments: '||l_cnt);
3083
3084 bulk_process_per_asg(
3085 2
3086 ,l_cnt
3087 ,errbuf
3088 ,retcode
3089 ,p_eff_date
3090 ,p_multi_asg
3091 );
3092
3093 write_log(1, 'Total # of new records processed for per_empdir_assignments: '||l_cnt);
3094 write_log(2, 'Total # of new records processed for per_empdir_assignments: '||l_cnt);
3095 write_log(1, 'End merge per assignments: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3096
3097
3098 EXCEPTION WHEN OTHERS THEN
3099 errbuf := errbuf||SQLERRM;
3100 retcode := '1';
3101 write_log(1, 'Error in merge_per_asg: '||SQLCODE);
3102 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3103 END merge_per_asg;
3104
3105 PROCEDURE populate_per_asg(
3106 errbuf OUT NOCOPY VARCHAR2
3107 ,retcode OUT NOCOPY VARCHAR2
3108 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
3109 ,p_multi_asg IN VARCHAR2
3110 ) IS
3111
3112 l_cnt NUMBER:= 0;
3113
3114 BEGIN
3115
3116 write_log(1, 'Begin populating per assginments: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3117 EXECUTE IMMEDIATE 'ALTER TABLE '||g_schema_owner||'.PER_EMPDIR_ASSIGNMENTS TRUNCATE PARTITION internal REUSE STORAGE';
3118 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_ASSIGNMENTS_PK REBUILD';
3119
3120 g_date := trunc(SYSDATE);
3121
3122 bulk_process_per_asg(
3123 0
3124 ,l_cnt
3125 ,errbuf
3126 ,retcode
3127 ,p_eff_date
3128 ,p_multi_asg
3129 );
3130
3131 write_log(1, 'Total # of records processed for per_empdir_assignments: '||l_cnt);
3132 write_log(2, 'Total # of records processed for per_empdir_assignments: '||l_cnt);
3133 write_log(1, 'End populating per asg: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3134
3135 EXCEPTION WHEN OTHERS THEN
3136 errbuf := errbuf||SQLERRM;
3137 retcode := '1';
3138 write_log(1, 'Error in populate_per_asg: '||SQLCODE);
3139 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3140 END populate_per_asg;
3141
3142 PROCEDURE populate_hr_orgs(
3143 errbuf OUT NOCOPY VARCHAR2
3144 ,retcode OUT NOCOPY VARCHAR2
3145 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
3146 ) IS
3147
3148 l_cnt NUMBER:= 0;
3149
3150 BEGIN
3151
3152 write_log(1, 'Begin populating hr orgs: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3153 EXECUTE IMMEDIATE 'ALTER TABLE '||g_schema_owner||'.PER_EMPDIR_ORGANIZATIONS TRUNCATE PARTITION internal REUSE STORAGE';
3154 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_ORGANIZATIONS_PK REBUILD';
3155
3156 g_date := trunc(SYSDATE);
3157
3158 bulk_process_hr_orgs(
3159 0
3160 ,l_cnt
3161 ,errbuf
3162 ,retcode
3163 ,p_eff_date
3164 );
3165
3166 write_log(1, 'Total # of records processed for per_empdir_organizations: '||l_cnt);
3167 write_log(2, 'Total # of records processed for per_empdir_organizations: '||l_cnt);
3168 write_log(1, 'End populating hr orgs: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3169
3170 EXCEPTION WHEN OTHERS THEN
3171 errbuf := errbuf||SQLERRM;
3172 retcode := '1';
3173 write_log(1, 'Error in populate_hr_orgs: '||SQLCODE);
3174 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3175
3176 END populate_hr_orgs;
3177
3178 PROCEDURE populate_per_phones(
3179 errbuf OUT NOCOPY VARCHAR2
3180 ,retcode OUT NOCOPY VARCHAR2
3181 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
3182 ) IS
3183
3184 l_cnt NUMBER:= 0;
3185
3186 BEGIN
3187
3188
3189 write_log(1, 'Begin populating per phones: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3190
3191 EXECUTE IMMEDIATE 'ALTER TABLE '||g_schema_owner||'.PER_EMPDIR_PHONES TRUNCATE PARTITION internal REUSE STORAGE';
3192 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_PHONES_PK REBUILD';
3193
3194 g_date := trunc(SYSDATE);
3195
3196 INSERT /*+ parallel(hrd) append */ INTO per_empdir_phones hrd(
3197 ORIG_SYSTEM ,
3198 ORIG_SYSTEM_ID ,
3199 DATE_FROM,
3200 DATE_TO ,
3201 PHONE_TYPE ,
3202 PHONE_NUMBER,
3203 PHONE_KEY ,
3204 PARENT_ID,
3205 PARENT_TABLE,
3206 OBJECT_VERSION_NUMBER,
3207 PARTITION_ID,
3208 REQUEST_ID,
3209 PROGRAM_APPLICATION_ID,
3210 PROGRAM_ID,
3211 PROGRAM_UPDATE_DATE,
3212 LAST_UPDATE_DATE,
3213 LAST_UPDATED_BY,
3214 LAST_UPDATE_LOGIN,
3215 CREATED_BY ,
3216 CREATION_DATE)
3217 SELECT /*+ parallel(ph) */
3218 g_srcSystem,
3219 phone_id,
3220 date_from,
3221 date_to,
3222 phone_type,
3223 phone_number,
3224 nvl(translate(upper(phone_number),'ABCDEFGHIJKLMNOPQRSTUVWXYZ ,.-()x/\',' '),'##'),
3225 parent_id,
3226 parent_table,
3227 object_version_number,
3228 1,
3229 g_request_id,
3230 g_prog_appl_id,
3231 g_prog_id,
3232 g_date,
3233 g_date,
3234 g_user_id,
3235 g_login_id,
3236 g_user_id,
3237 g_date
3238 FROM per_phones
3239 WHERE p_eff_date BETWEEN DATE_FROM AND nvl(DATE_TO, p_eff_date+1);
3240
3241
3242 l_cnt := sql%rowcount;
3243 COMMIT;
3244
3245 write_log(1, 'Total # of records processed for per_empdir_phones: '||l_cnt);
3246 write_log(2, 'Total # of records processed for per_empdir_phones: '||l_cnt);
3247 write_log(1, 'End populating per phones: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3248
3249 EXCEPTION WHEN OTHERS THEN
3250 errbuf := errbuf||SQLERRM;
3251 retcode := '1';
3252 write_log(1, 'Error in populate_per_phones: '||SQLCODE);
3253 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3254
3255 END populate_per_phones;
3256
3257 PROCEDURE populate_per_loctl(
3258 errbuf OUT NOCOPY VARCHAR2
3259 ,retcode OUT NOCOPY VARCHAR2
3260 ) IS
3261
3262 l_cnt NUMBER:= 0;
3263
3264 BEGIN
3265
3266
3267 write_log(1, 'Begin populating per location tl: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3268 EXECUTE IMMEDIATE 'ALTER TABLE '||g_schema_owner||'.PER_EMPDIR_LOCATIONS_TL TRUNCATE PARTITION internal REUSE STORAGE';
3269 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_LOCATIONS_TL_PK REBUILD';
3270
3271 g_date := trunc(SYSDATE);
3272
3273 INSERT /*+ parallel(loc) append */ INTO per_empdir_locations_tl loc(
3274 ORIG_SYSTEM,
3275 ORIG_SYSTEM_ID,
3276 LOCATION_CODE,
3277 DESCRIPTION,
3278 LANGUAGE,
3279 SOURCE_LANG,
3280 OBJECT_VERSION_NUMBER,
3281 PARTITION_ID ,
3282 LAST_UPDATE_DATE,
3283 LAST_UPDATE_BY,
3284 CREATED_BY ,
3285 CREATION_DATE,
3286 REQUEST_ID,
3287 PROGRAM_APPLICATION_ID,
3288 PROGRAM_ID,
3289 PROGRAM_UPDATE_DATE)
3290 SELECT /*+ parallel(ltl) */
3291 g_srcSystem,
3292 ltl.location_id,
3293 ltl.location_code,
3294 ltl.description,
3295 ltl.language,
3296 ltl.source_lang,
3297 l.object_version_number,
3298 1,
3299 g_date,
3300 g_user_id,
3301 g_user_id,
3302 g_date,
3303 g_request_id,
3304 g_prog_appl_id,
3305 g_prog_id,
3306 g_date
3307 from hr_locations_all_tl ltl, hr_locations_all l
3308 where ltl.location_id = l.location_id;
3309
3310 l_cnt := sql%rowcount;
3311 COMMIT;
3312
3313 write_log(1, 'Total # of records processed for per_empdir_locations_tl: '||l_cnt);
3314 write_log(2, 'Total # of records processed for per_empdir_locations_tl: '||l_cnt);
3315 write_log(1, 'End populating per locations tl: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3316
3317 EXCEPTION WHEN OTHERS THEN
3318 errbuf := errbuf||SQLERRM;
3319 retcode := '1';
3320 write_log(1, 'Error in populate_per_locations tl: '||SQLCODE);
3321 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3322
3323 END populate_per_loctl;
3324
3325 PROCEDURE populate_per_locations(
3326 errbuf OUT NOCOPY VARCHAR2
3327 ,retcode OUT NOCOPY VARCHAR2
3328 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
3329 ) IS
3330
3331 l_cnt NUMBER:= 0;
3332
3333 BEGIN
3334
3335 write_log(1, 'Begin populating per locations: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3336 EXECUTE IMMEDIATE 'ALTER TABLE '||g_schema_owner||'.PER_EMPDIR_LOCATIONS TRUNCATE PARTITION internal REUSE STORAGE';
3337 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_LOCATIONS_PK REBUILD';
3338
3339 bulk_process_per_locations(
3340 0
3341 ,l_cnt
3342 ,errbuf
3343 ,retcode
3344 ,p_eff_date
3345 );
3346
3347 write_log(1, 'Total # of records processed for per_empdir_locations: '||l_cnt);
3348 write_log(2, 'Total # of records processed for per_empdir_locations: '||l_cnt);
3349 write_log(1, 'End populating per locations: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3350
3351 EXCEPTION WHEN OTHERS THEN
3352 errbuf := errbuf||SQLERRM;
3353 retcode := '1';
3354 write_log(1, 'Error in populate_per_locations: '||SQLCODE);
3355 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3356
3357 END populate_per_locations;
3358
3359 PROCEDURE populate_per_pos(
3360 errbuf OUT NOCOPY VARCHAR2
3361 ,retcode OUT NOCOPY VARCHAR2
3362 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
3363 ) IS
3364
3365 l_cnt NUMBER:= 0;
3366
3367 BEGIN
3368
3369 write_log(1, 'Begin populating per pos: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3370
3371 EXECUTE IMMEDIATE 'ALTER TABLE '||g_schema_owner||'.PER_EMPDIR_POSITIONS TRUNCATE PARTITION internal REUSE STORAGE';
3372 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_POSITIONS_PK REBUILD';
3373
3374 g_date := trunc(SYSDATE);
3375
3376 bulk_process_hr_pos(
3377 0
3378 ,l_cnt
3379 ,errbuf
3380 ,retcode
3381 ,p_eff_date
3382 );
3383
3384 write_log(1, 'Total # of records processed for per_empdir_positions: '||l_cnt);
3385 write_log(2, 'Total # of records processed for per_empdir_positions: '||l_cnt);
3386 write_log(1, 'End populating per pos: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3387
3388 EXCEPTION WHEN OTHERS THEN
3389 errbuf := errbuf||SQLERRM;
3390 retcode := '1';
3391 write_log(1, 'Error in populate_per_pos: '||SQLCODE);
3392 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3393
3394 END populate_per_pos;
3395
3396 PROCEDURE populate_per_jobs(
3397 errbuf OUT NOCOPY VARCHAR2
3398 ,retcode OUT NOCOPY VARCHAR2
3399 ,p_eff_date IN DATE DEFAULT trunc(SYSDATE)
3400 ) IS
3401
3402 l_cnt NUMBER:= 0;
3403 l_tcnt NUMBER:= 0;
3404
3405 BEGIN
3406
3407
3408 write_log(1, 'Begin populating per jobs: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3409 EXECUTE IMMEDIATE 'ALTER TABLE '||g_schema_owner||'.PER_EMPDIR_JOBS TRUNCATE PARTITION internal REUSE STORAGE';
3410 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_JOBS_PK REBUILD';
3411
3412 FOR I in c_organizations LOOP
3413
3414 write_log(1,'Processing jobs for BusGrpId: '||I.organization_id||
3415 ' using SegList: '||I.slist);
3416
3417 bulk_process_per_jobs(
3418 0
3419 ,l_cnt
3420 ,errbuf
3421 ,retcode
3422 ,p_eff_date
3423 ,I.slist
3424 ,I.organization_id
3425 );
3426 l_tcnt := l_tcnt + l_cnt;
3427
3428 END LOOP;
3429
3430 write_log(1, 'Total # of records processed for per_empdir_jobs: '||l_tcnt);
3431 write_log(2, 'Total # of records processed for per_empdir_jobs: '||l_tcnt);
3432 write_log(1, 'End populating per jobs: '||to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3433
3434 EXCEPTION WHEN OTHERS THEN
3435 errbuf := errbuf||SQLERRM;
3436 retcode := '1';
3437 write_log(1, 'Error in populate_per_jobs: '||SQLCODE);
3438 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3439
3440 END populate_per_jobs;
3441
3442 --Fix for Bug#4380794
3443 PROCEDURE populate_per_images(
3444 errbuf OUT NOCOPY VARCHAR2
3445 ,retcode OUT NOCOPY VARCHAR2
3446 ) IS
3447 l_cnt NUMBER:= 0;
3448 BEGIN
3449
3450 write_log(1, 'Begin populating per images: '||to_char(SYSDATE, 'DD/MM/RRRR
3451 HH:MI:SS'));
3452
3453 --fix for bug 6066127
3454 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||g_schema_owner||'.PER_EMPDIR_IMAGES';
3455 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_IMAGES_PK REBUILD';
3456 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_IMAGES_U1 REBUILD';
3457
3458 /* inserting the blob columns from per_images to per_empdir_images if not exist
3459 * * already*/
3460
3461 INSERT INTO per_empdir_images
3462 (image_id,
3463 orig_system,
3464 orig_system_id,
3465 image_name,
3466 content_type,
3467 image,
3468 object_version_number)
3469 SELECT per_empdir_images_s.nextval
3470 ,'PER'
3471 ,pi.parent_id
3472 ,pi.parent_id
3473 ,null
3474 ,pi.image
3475 ,1
3476 FROM per_images pi
3477 WHERE pi.table_name='PER_PEOPLE_F'
3478 AND NOT EXISTS ( SELECT 'X'FROM per_empdir_images pei
3479 WHERE pei.orig_system='PER'
3480 AND pei.orig_system_id=pi.parent_id);
3481
3482
3483
3484 l_cnt := sql%rowcount;
3485 COMMIT;
3486
3487 write_log(1, 'Total # of new records processed for per_empdir_images:
3488 '||l_cnt);
3489 write_log(2, 'Total # of new records processed for per_empdir_images:
3490 '||l_cnt);
3491 write_log(1, 'End populating per images: '||to_char(SYSDATE, 'DD/MM/RRRR
3492 HH:MI:SS'));
3493
3494 delete from per_empdir_images
3495 where orig_system_id not in(
3496 select parent_id from per_images
3497 where table_name='PER_PEOPLE_F')
3498 and orig_system = 'PER' ;
3499 commit;
3500
3501
3502 EXCEPTION WHEN OTHERS THEN
3503 errbuf := errbuf||SQLERRM;
3504 retcode := '1';
3505 write_log(1, 'Error in populate_per_images: '||SQLCODE);
3506 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3507
3508 END populate_per_images;
3509
3510 PROCEDURE merge_per_images(
3511 errbuf OUT NOCOPY VARCHAR2
3512 ,retcode OUT NOCOPY VARCHAR2
3513 ) IS
3514
3515 l_cnt NUMBER:= 0;
3516 L_IMAGE_ID NUMBER(15,0) :=0;
3517 L_ORIG_SYSTEM VARCHAR2(30);
3518 L_ORIG_SYSTEM_ID NUMBER(15,0):=0;
3519 L_IMAGE_NAME VARCHAR2(60);
3520 L_CONTENT_TYPE VARCHAR2(30);
3521 L_OBJECT_VERSION_NUMBER NUMBER(9,0):= 0;
3522 L_LAST_UPDATE_DATE DATE;
3523 L_LAST_UPDATED_BY NUMBER(15,0):=0;
3524 L_LAST_UPDATE_LOGIN NUMBER(15,0):=0;
3525 L_CREATED_BY NUMBER(15,0):=0;
3526 L_CREATION_DATE DATE;
3527
3528 Cursor update_emp_dirimages_cur is
3529 Select pi.parent_id parent_id
3530 From per_images pi,per_empdir_images pei
3531 Where pi.table_name='PER_PEOPLE_F'
3532 And pi.parent_id =pei.orig_system_id
3533 And pei.orig_system='PER'
3534 And trunc(pei.last_update_date) <= trunc(sysdate);
3535
3536
3537 BEGIN
3538
3539 write_log(1, 'Begin merging per images: '||to_char(SYSDATE, 'DD/MM/RRRR
3540 HH:MI:SS'));
3541 /* updating the blob columns from per_images to per_empdir_images if exist */
3542
3543
3544 FOR update_emp_dirimages_rec IN update_emp_dirimages_cur
3545 LOOP
3546
3547 SELECT IMAGE_ID,ORIG_SYSTEM,ORIG_SYSTEM_ID,NVL(IMAGE_NAME,' '),NVL(CONTENT_TYPE,' '),
3548 NVL(OBJECT_VERSION_NUMBER,0),NVL(LAST_UPDATE_DATE,SYSDATE),
3549 NVL(LAST_UPDATED_BY,0),NVL(LAST_UPDATE_LOGIN,0),NVL(CREATED_BY,0),NVL(CREATION_DATE,SYSDATE) INTO
3550 L_IMAGE_ID,L_ORIG_SYSTEM,L_ORIG_SYSTEM_ID,L_IMAGE_NAME,L_CONTENT_TYPE,L_OBJECT_VERSION_NUMBER,L_LAST_UPDATE_DATE,
3551 L_LAST_UPDATED_BY,L_LAST_UPDATE_LOGIN,L_CREATED_BY,L_CREATION_DATE
3552 FROM PER_EMPDIR_IMAGES
3553 WHERE ORIG_SYSTEM_ID = update_emp_dirimages_rec.parent_id
3554 AND orig_system='PER';
3555
3556 DELETE FROM PER_EMPDIR_IMAGES
3557 WHERE ORIG_SYSTEM_ID = update_emp_dirimages_rec.parent_id
3558 AND orig_system='PER';
3559
3560 INSERT INTO PER_EMPDIR_IMAGES
3561 (IMAGE_ID,ORIG_SYSTEM,ORIG_SYSTEM_ID,IMAGE_NAME,CONTENT_TYPE,IMAGE,OBJECT_VERSION_NUMBER,LAST_UPDATE_DATE,
3562 LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE)
3563 SELECT L_IMAGE_ID,L_ORIG_SYSTEM,L_ORIG_SYSTEM_ID,L_IMAGE_NAME,L_CONTENT_TYPE,
3564 image,
3565 L_OBJECT_VERSION_NUMBER,L_LAST_UPDATE_DATE,
3566 L_LAST_UPDATED_BY,L_LAST_UPDATE_LOGIN,L_CREATED_BY,L_CREATION_DATE
3567 FROM per_images
3568 WHERE table_name='PER_PEOPLE_F'
3569 AND PARENT_ID = update_emp_dirimages_rec.parent_id;
3570
3571 l_cnt := l_cnt + 1;
3572 END LOOP;
3573
3574
3575
3576
3577 l_cnt := sql%rowcount;
3578
3579 COMMIT;
3580
3581
3582 write_log(1, 'Total # of records updated for per_empdir_images: '||l_cnt);
3583 write_log(2, 'Total # of records updated for per_empdir_images: '||l_cnt);
3584 /* inserting the blob columns from per_images to per_empdir_images if not exist
3585 * * already*/
3586 l_cnt :=0;
3587 INSERT INTO per_empdir_images
3588 (image_id,
3589 orig_system,
3590 orig_system_id,
3591 image_name,
3592 content_type,
3593 image,
3594 object_version_number)
3595 SELECT per_empdir_images_s.nextval
3596 ,'PER'
3597 ,pi.parent_id
3598 ,pi.parent_id
3599 ,null
3600 ,pi.image
3601 ,1
3602 FROM per_images pi
3603 WHERE pi.table_name='PER_PEOPLE_F'
3604 AND NOT EXISTS ( SELECT 'X'FROM per_empdir_images pei
3605 WHERE pei.orig_system='PER'
3606 AND pei.orig_system_id=pi.parent_id);
3607
3608 l_cnt := sql%rowcount;
3609 COMMIT;
3610
3611 write_log(1, 'Total # of new records processed for per_empdir_images:
3612 '||l_cnt);
3613 write_log(2, 'Total # of new records processed for per_empdir_images:
3614 '||l_cnt);
3615 write_log(1, 'End merge per images: '||to_char(SYSDATE, 'DD/MM/RRRR
3616 HH:MI:SS'));
3617
3618 delete from per_empdir_images
3619 where orig_system_id not in(
3620 select parent_id from per_images
3621 where table_name='PER_PEOPLE_F')
3622 and orig_system = 'PER' ;
3623 commit;
3624
3625
3626 EXCEPTION WHEN OTHERS THEN
3627 errbuf := errbuf||SQLERRM;
3628 retcode := '1';
3629 write_log(1, 'Error in merge_per_images: '||SQLCODE);
3630 write_log(1, 'Error Msg: '||substr(SQLERRM,1,700));
3631
3632 END merge_per_images;
3633
3634 --End of fix for bug#4380794
3635
3636 -- Global Members
3637
3638 PROCEDURE swap(
3639 value1 IN OUT NOCOPY VARCHAR2
3640 ,value2 IN OUT NOCOPY VARCHAR2) IS
3641
3642 l_tmp VARCHAR2(240);
3643 BEGIN
3644
3645 l_tmp := value1;
3646 value1 := value2;
3647 value2 := l_tmp;
3648
3649 EXCEPTION WHEN OTHERS THEN
3650 per_empdir_ss.write_log(1,
3651 'Error in swap: '||SQLCODE);
3652 per_empdir_ss.write_log(1,
3653 'Error Msg: '||substr(SQLERRM,1,700));
3654 END swap;
3655
3656 PROCEDURE main(
3657 errbuf OUT NOCOPY VARCHAR2
3658 ,retcode OUT NOCOPY VARCHAR2
3659 ,p_mode IN VARCHAR2
3660 ,p_eff_date IN VARCHAR2
3661 ,p_source_system IN VARCHAR2
3662 ,p_multi_asg IN VARCHAR2 DEFAULT 'N'
3663 ,p_image_refresh IN VARCHAR2 DEFAULT 'N'
3664 ) IS
3665 l_eff_date DATE;
3666 BEGIN
3667
3668 g_srcSystem := p_source_system;
3669 g_schema_owner := getTblOwner;
3670 l_eff_date := nvl(fnd_date.canonical_to_date(p_eff_date), trunc(sysdate));
3671
3672 write_log(1, 'Process began @: '|| to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3673
3674 BEGIN
3675 EXECUTE IMMEDIATE 'ALTER TRIGGER PER_EMPDIR_ASSIGNMENTS_WHO DISABLE';
3676 EXECUTE IMMEDIATE 'ALTER TRIGGER PER_EMPDIR_LOCATIONS_WHO DISABLE';
3677 EXECUTE IMMEDIATE 'ALTER TRIGGER PER_EMPDIR_PEOPLE_WHO DISABLE';
3678 EXECUTE IMMEDIATE 'ALTER TRIGGER PER_EMPDIR_PHONES_WHO DISABLE';
3679
3680 EXCEPTION WHEN OTHERS THEN
3681 NULL;
3682 END;
3683
3684 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
3685
3686 IF (p_mode = 'COMPLETE') THEN
3687 write_log(2, 'Running Complete Build');
3688
3689 populate_per_people(
3690 errbuf
3691 ,retcode
3692 ,l_eff_date
3693 );
3694
3695 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_PEOPLE_N1 REBUILD PARAMETERS(''sync'')';
3696
3697 populate_per_asg(
3698 errbuf
3699 ,retcode
3700 ,l_eff_date
3701 ,p_multi_asg
3702 );
3703
3704 populate_hr_orgs(
3705 errbuf
3706 ,retcode
3707 ,l_eff_date
3708 );
3709
3710 populate_per_phones(
3711 errbuf
3712 ,retcode
3713 ,l_eff_date
3714 );
3715
3716 populate_per_pos(
3717 errbuf
3718 ,retcode
3719 ,l_eff_date
3720 );
3721
3722 populate_per_jobs(
3723 errbuf
3724 ,retcode
3725 ,l_eff_date
3726 );
3727
3728 populate_per_locations(
3729 errbuf
3730 ,retcode
3731 ,l_eff_date
3732 );
3733
3734 populate_per_loctl(
3735 errbuf
3736 ,retcode
3737 );
3738
3739 --Fix for bug#4380794
3740 IF p_image_refresh = 'Y' THEN
3741 populate_per_images(
3742 errbuf
3743 ,retcode
3744 );
3745 END IF;
3746 --end ofFix for bug#4380794
3747
3748 IF (nvl(retcode,'0') <> '1') THEN
3749 gather_stats;
3750 END IF;
3751
3752 compute_reports(
3753 errbuf
3754 ,retcode
3755 ,g_srcSystem
3756 );
3757
3758 ELSIF (p_mode = 'INCREMENTAL') THEN
3759
3760 write_log(2, 'Incremental Refresh Build');
3761
3762 merge_per_people(
3763 errbuf
3764 ,retcode
3765 ,l_eff_date
3766 );
3767
3768 EXECUTE IMMEDIATE 'ALTER INDEX '||g_schema_owner||'.PER_EMPDIR_PEOPLE_N1 REBUILD PARAMETERS(''sync'') ONLINE';
3769
3770 merge_per_asg(
3771 errbuf
3772 ,retcode
3773 ,l_eff_date
3774 ,p_multi_asg
3775 );
3776
3777 merge_hr_orgs(
3778 errbuf
3779 ,retcode
3780 ,l_eff_date
3781 );
3782
3783 merge_per_phones(
3784 errbuf
3785 ,retcode
3786 ,l_eff_date
3787 );
3788
3789 merge_per_pos(
3790 errbuf
3791 ,retcode
3792 ,l_eff_date
3793 );
3794
3795 merge_per_jobs(
3796 errbuf
3797 ,retcode
3798 ,l_eff_date
3799 );
3800
3801 merge_per_locations(
3802 errbuf
3803 ,retcode
3804 ,l_eff_date
3805 );
3806
3807 merge_per_loctl(
3808 errbuf
3809 ,retcode
3810 ,l_eff_date
3811 );
3812
3813 --FIX FOR BUG#4380794
3814 IF p_image_refresh = 'Y' THEN
3815 merge_per_images(
3816 errbuf
3817 ,retcode
3818 );
3819 END IF;
3820 --End of FIX FOR BUG#4380794
3821
3822 compute_reports(
3823 errbuf
3824 ,retcode
3825 ,g_srcSystem
3826 );
3827
3828 END IF;
3829
3830 write_log(1, 'Process completed @: '|| to_char(SYSDATE, 'DD/MM/RRRR HH:MI:SS'));
3831
3832 END main;
3833
3834 function get_time (p_to_tz in varchar2) return varchar2 is
3835 l_date date;
3836 begin
3837 fnd_date_tz.init_timezones_for_fnd_date;
3838 l_date := FND_DATE.adjust_datetime(sysdate
3839 ,FND_TIMEZONES.get_server_timezone_code
3840 ,p_to_tz);
3841
3842 RETURN to_char(l_date, fnd_profile.value('ICX_DATE_FORMAT_MASK')||' HH24:MI');
3843
3844 EXCEPTION WHEN OTHERS THEN
3845 RETURN NULL;
3846 end;
3847
3848 FUNCTION get_time (
3849 p_source_tz_id IN NUMBER,
3850 p_dest_tz_id IN NUMBER,
3851 p_source_day_time IN DATE
3852 ) RETURN VARCHAR2 IS
3853 -- local variables
3854 x_dest_day_time DATE;
3855 x_return_status VARCHAR2(10);
3856 x_msg_count NUMBER;
3857 x_msg_data VARCHAR2(300);
3858 BEGIN
3859 hz_timezone_pub.get_time(
3860 p_api_version => g_hz_api_api_version,
3861 p_init_msg_list => '',
3862 p_source_tz_id => p_source_tz_id,
3863 p_dest_tz_id => p_dest_tz_id,
3864 p_source_day_time => p_source_day_time,
3865 x_dest_day_time => x_dest_day_time,
3866 x_return_status => x_return_status,
3867 x_msg_count => x_msg_count,
3868 x_msg_data => x_msg_data
3869 );
3870
3871 RETURN to_char(x_dest_day_time
3872 ,fnd_profile.value('ICX_DATE_FORMAT_MASK')||' HH24:MI');
3873
3874 EXCEPTION WHEN OTHERS THEN
3875 RETURN NULL;
3876 END;
3877
3878
3879 FUNCTION get_timezone_code(
3880 p_postal_code IN VARCHAR2,
3881 p_city IN VARCHAR2,
3882 p_state IN VARCHAR2,
3883 p_country IN VARCHAR2
3884 ) RETURN VARCHAR2 IS
3885 l_timezone_id number;
3886 begin
3887 l_timezone_id := get_timezone_id(p_postal_code, p_city, p_state, p_country);
3888 return fnd_timezones.get_code(l_timezone_id);
3889 end;
3890
3891 FUNCTION get_timezone_id(
3892 p_postal_code IN VARCHAR2,
3893 p_city IN VARCHAR2,
3894 p_state IN VARCHAR2,
3895 p_country IN VARCHAR2
3896 ) RETURN NUMBER IS
3897 -- local variables
3898 l_timezone_id NUMBER(15);
3899 x_return_status VARCHAR2(10);
3900 x_msg_count NUMBER;
3901 x_msg_data VARCHAR2(300);
3902
3903 BEGIN
3904 hz_timezone_pub.get_timezone_id (
3905 p_api_version => g_hz_api_api_version,
3906 p_init_msg_list => '',
3907 p_postal_code => p_postal_code,
3908 p_city => p_city,
3909 p_state => p_state,
3910 p_country => p_country,
3911 x_timezone_id => l_timezone_id,
3912 x_return_status => x_return_status,
3913 x_msg_count => x_msg_count,
3914 x_msg_data => x_msg_data
3915 );
3916
3917 RETURN l_timezone_id;
3918
3919 EXCEPTION WHEN OTHERS THEN
3920 RETURN NULL;
3921 END get_timezone_id;
3922
3923 END PER_EMPDIR_SS;