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