[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_PERSON_M_C
Source
1 Package Body EDW_HR_PERSON_M_C AS
2 /* $Header: hrieppsn.pkb 120.1 2005/06/07 05:59:25 anmajumd noship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7
8 g_instance_code VARCHAR2(30); -- Holds source instance
9
10 g_number_of_levels NUMBER := 15; -- For Supervisor Hierarchies
11
12 /********************************************************************/
13 /* This procedure dynamically builds a sql statement to insert rows */
14 /* into the given supervisor hierarchy level table from the given */
15 /* level collection view */
16 /********************************************************************/
17 Procedure Do_Insert( p_tree_number IN NUMBER,
18 p_from_level IN NUMBER,
19 p_to_level IN NUMBER,
20 p_from_date IN DATE,
21 p_to_date IN DATE)
22 IS
23
24 l_sql_stmt VARCHAR2(2000); -- Holds SQL Statement to be executed
25 l_ret_code NUMBER; -- Keeps return code of sql execution
26
27 l_from_view VARCHAR2(50); -- Name of the collection view
28 l_to_table VARCHAR2(50); -- Name of the staging table
29
30 l_pk_column VARCHAR2(30); -- Staging table pk column
31 l_fk_column VARCHAR2(30); -- Staging table fk column
32 l_pk_value VARCHAR2(60); -- Pk value selected from collection view
33 l_fk_value VARCHAR2(60); -- Fk value selected from collection view
34
35 l_na_edw_pk VARCHAR2(30); -- Primary key for dummy row
36 l_na_edw_fk VARCHAR2(30); -- Foreign key for dummy row
37
38 l_push_from_level VARCHAR2(30); -- Push from level name
39 l_push_down_name VARCHAR2(400); -- Name format for push down level
40 l_standard_name VARCHAR2(400); -- Name format for non push down levels
41 l_push_down_name_display VARCHAR2(400); -- Display Name format for push down level
42 l_standard_name_display VARCHAR2(400); -- Display Name format for non push down levels
43
44 l_temp_date DATE; -- Keeps track of execution start time
45 l_duration NUMBER := 0; -- Execution time
46 l_rows_inserted NUMBER := 0; -- Number of rows inserted
47
48 BEGIN
49
50 /* Construct the table, view and level names */
51 /*********************************************/
52
53 l_from_view := 'EDW_HR_PERM_SPSR1_L' || p_from_level || '_LCV@APPS_TO_APPS';
54
55 l_to_table := 'EDW_HR_PERM_SPSR1_L' || p_to_level || '_LSTG@EDW_APPS_TO_WH';
56
57 l_push_from_level := 'PERM_SPSR1_L' || p_from_level ;
58
59
60 /* Construct the primary and foreign key names from the staging table */
61 /**********************************************************************/
62 l_pk_column := 'SPRVSR_LVL' || p_to_level || '_PK';
63
64 /* If top level staging table then fk column is ALL */
65 IF (p_to_level = g_number_of_levels) THEN
66 l_fk_column := 'ALL_FK';
67 ELSE
68 l_fk_column := 'SPRVSR_LVL' || to_char(p_to_level+1) || '_FK';
69 END IF;
70
71
72 /* Construct the primary and foreign key names from the collection view */
73 /************************************************************************/
74 /* If straight push, then staging table columns match collection view columns */
75 IF (p_from_level = p_to_level) THEN
76 l_pk_value := 'lvln.' || l_pk_column;
77 l_fk_value := 'NVL(lvln.' || l_fk_column || ',''NA_EDW'')';
78 /* Otherwise append "-TnLm" tag for push down and get push lookup */
79 ELSE
80 l_pk_value := 'lvln.SPRVSR_LVL' || p_from_level || '_PK || ''-TL'
81 || p_from_level || '''';
82
83 /* If only pushing down 1 level, then point to pk of level above */
84 IF (p_to_level = p_from_level - 1) THEN
85 l_fk_value := 'lvln.SPRVSR_LVL' || p_from_level || '_PK';
86
87 /* Otherwise point to pk plus tag of level above */
88 ELSE
89 l_fk_value := l_pk_value;
90 END IF;
91 END IF;
92
93 /* Construct the primary and foreign key names for the dummy rows */
94 /******************************************************************/
95
96 l_na_edw_pk := to_char(p_from_level) || '-NA_EDW-' || g_instance_code;
97
98 /* If highest level set foreign key to 'ALL' */
99 IF (p_from_level = 15) THEN
100 l_na_edw_fk := 'ALL';
101 ELSE
102 l_na_edw_fk := to_char(p_from_level+1) || '-NA_EDW-' || g_instance_code;
103 END IF;
104
105 /******************************************************************************/
106 /* Standard "Name" column format */
107 /******************************************************************************/
108 l_standard_name := 'DECODE(peo.known_as||peo.first_name,NULL,'''',
109 NVL(peo.known_as,peo.first_name)||'' '')
110 ||peo.last_name ||
111 ''(''||NVL(peo.employee_number,
112 peo.applicant_number)||'')''';
113 l_standard_name_display :=
114 'DECODE(peo.known_as||peo.first_name,NULL,'''',
115 NVL(peo.known_as,peo.first_name)||'' '')
116 ||peo.last_name';
117 /******************************************************************************/
118 /* Push down level "Name" column format */
119 /******************************************************************************/
120 l_push_down_name := 'DECODE(peo.known_as||peo.first_name,NULL,'''',
121 NVL(peo.known_as,peo.first_name)||'' '')
122 ||peo.last_name ||
123 ''(''||NVL(peo.employee_number,
124 peo.applicant_number)'||
125 '||'')-' ||TO_CHAR(16-p_from_level)||'''';
126 l_push_down_name_display :=
127 'DECODE(peo.known_as||peo.first_name,NULL,'''',
128 NVL(peo.known_as,peo.first_name)||'' '')
129 ||peo.last_name';
130 /******************************************************************************/
131 /* BUILD UP THE SQL STATEMENT */
132 /******************************************************************************/
133
134 /* Not a push down - straight insert */
135 /*************************************/
136 IF (p_from_level = p_to_level) THEN
137
138 l_sql_stmt :=
139 'Insert Into ' || l_to_table || '(
140 assignment_id,
141 collection_status,
142 creation_date,
143 error_code,
144 instance,
145 last_update_date,
146 name,
147 name_display,
148 operation_code,
149 person_id,
150 request_id,
151 row_id,
152 sprvsr_dp,
153 ' || l_pk_column || ',
154 ' || l_fk_column || ',
155 user_attribute1,
156 user_attribute2,
157 user_attribute3,
158 user_attribute4,
159 user_attribute5)
160 select
161 lvln.assignment_id,
162 ''READY'',
163 sysdate,
164 null,
165 lvln.INSTANCE,
166 sysdate,
167 '||l_standard_name||',
168 '||l_standard_name_display||',
169 to_char(null),
170 lvln.person_id,
171 to_number(null),
172 to_char(null),
173 lvln.sprvsr_dp,
174 ' || l_pk_value || ',
175 ' || l_fk_value || ',
176 lvln.user_attribute1,
177 lvln.user_attribute2,
178 lvln.user_attribute3,
179 lvln.user_attribute4,
180 lvln.user_attribute5
181 from ' || l_from_view || ' lvln,
182 per_all_people_f peo
183 where peo.person_id = lvln.person_id
184 and SYSDATE between peo.effective_start_date and peo.effective_end_date
185 UNION ALL
186 select
187 to_number(null),
188 ''READY'',
189 sysdate,
190 null,
191 null,
192 sysdate,
193 null,
194 null,
195 to_char(null),
196 to_number(null),
197 to_number(null),
198 to_char(null),
199 null,
200 ''' || l_na_edw_pk || ''',
201 ''' || l_na_edw_fk || ''',
202 null,
203 null,
204 null,
205 null,
206 null
207 from dual';
208
209 /******************************************************************************/
210 /* Push Down from a higher level */
211 /******************************************************************************/
212 ELSE
213 l_sql_stmt := 'Insert Into ' || l_to_table || '(
214 assignment_id,
215 collection_status,
216 creation_date,
217 error_code,
218 instance,
219 last_update_date,
220 name,
221 name_display,
222 operation_code,
223 person_id,
224 request_id,
225 row_id,
226 sprvsr_dp,
227 ' || l_pk_column || ',
228 ' || l_fk_column || ',
229 user_attribute1,
230 user_attribute2,
231 user_attribute3,
232 user_attribute4,
233 user_attribute5
234 )
235 select lvln.assignment_id,
236 ''READY'',
237 sysdate,
238 to_char(null), -- error code
239 lvln.INSTANCE,
240 sysdate,
241 ' || l_push_down_name || ',
242 ' || l_push_down_name_display || ',
243 to_char(null), -- operation_code
244 lvln.person_id,
245 to_number(null), -- request_id
246 to_char(null), -- row_id
247 ' || l_push_down_name || ',
248 ' || l_pk_value || ',
249 ' || l_fk_value || ',
250 lvln.user_attribute1,
251 lvln.user_attribute2,
252 lvln.user_attribute3,
253 lvln.user_attribute4,
254 lvln.user_attribute5
255 from ' || l_from_view || ' lvln,
256 per_all_people_f peo
257 where lvln.NAME is not null
258 and peo.person_id = lvln.person_id
259 and SYSDATE between peo.effective_start_date and
260 peo.effective_end_date
261 ';
262
263 END IF;
264
265 edw_log.put_line( 'Pushing Supervisor Hierarchy Level ' ||
266 p_from_level || ' to Level ' || p_to_level );
267
268 l_temp_date := SYSDATE;
269 -- edw_log.put_line(l_sql_stmt);
270 EXECUTE IMMEDIATE l_sql_stmt;
271
272 l_rows_inserted := sql%rowcount;
273
274 l_duration := sysdate - l_temp_date;
275
276 Commit;
277
278 edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
279 ' rows into the ' || l_to_table || ' staging table');
280
281 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
282 edw_log.put_line(' ');
283
284 End Do_Insert;
285
286
287 /********************************************************************************/
288 /* New supervisor hierarchy inserted by HRI */
289 /********************************************************************************/
290
291 Procedure Push_Tree( p_from_date IN DATE,
292 p_to_date IN DATE,
293 p_tree IN NUMBER )
294 IS
295 BEGIN
296
297 /****************************************************/
298 /* Collect levels in the following order */
299 /* From view level 1 to table level 1 */
300 /* From view level 2 to table level 1 */
301 /* - - - - - - - - - */
302 /* From view level 15 to table level 1 */
303 /* From view level 2 to table level 2 */
304 /* From view level 3 to table level 2 */
305 /* - - - - - - - - - */
306 /* From view level 15 to table level 2 */
307 /* - - - - - - - - - */
308 /* From view level 14 to table level 14 */
309 /* From view level 15 to table level 14 */
310 /* From view level 15 to table level 15 */
311 /****************************************************/
312
313 FOR v_push_to_level IN 1..g_number_of_levels LOOP
314
315 edw_log.put_line('Starting Push_EDW_HR_PERM_SPSR_' || v_push_to_level || '_LSTG');
316 edw_log.put_line(' ');
317
318 FOR v_push_from_view IN v_push_to_level..g_number_of_levels LOOP
319
320 Do_Insert( p_tree_number => p_tree,
321 p_from_level => v_push_from_view,
322 p_to_level => v_push_to_level,
323 p_from_date => p_from_date,
324 p_to_date => p_to_date );
325
326 END LOOP;
327
328 END LOOP;
329
330 END Push_Tree;
331
332 /********************************************************************************/
333
334 Procedure Push(Errbuf in out NOCOPY Varchar2,
335 Retcode in out NOCOPY Varchar2,
336 p_from_date IN VARCHAR2,
337 p_to_date IN VARCHAR2) IS
338 l_dimension_name Varchar2(30) :='EDW_HR_PERSON_M' ;
339 l_temp_date Date:=Null;
340 l_rows_inserted Number:=0;
341 l_duration Number:=0;
342 l_exception_msg Varchar2(2000):=Null;
343
344 l_check_sprvsr_id number:=0;
345
346 cursor cur_check_sprvsr_hrchy is
347 select supv_person_id
348 from hri_supv_hrchy_summary;
349
350 -- -------------------------------------------
351 -- Put any additional developer variables here
352 -- -------------------------------------------
353 Begin
354 Errbuf :=NULL;
355 Retcode:=0;
356 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
357 errbuf := fnd_message.get;
358 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
359 Return;
360 END IF;
361
362 IF (p_from_date IS NULL) THEN
363 EDW_HR_PERSON_M_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date -
364 EDW_COLLECTION_UTIL.g_offset;
365 ELSE
366 EDW_HR_PERSON_M_C.g_push_date_range1 := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
367 END IF;
368
369 IF (p_to_date IS NULL) THEN
370 EDW_HR_PERSON_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
371 ELSE
372 EDW_HR_PERSON_M_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
373 END IF;
374
375
376 edw_log.put_line( 'The collection range is from '||
377 to_char(EDW_HR_PERSON_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
378 to_char(EDW_HR_PERSON_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
379 edw_log.put_line(' ');
380
381
382 -- -----------------------------------------------------------------------------
383 -- Start of Collection , Developer Customizable Section
384 -- -----------------------------------------------------------------------------
385
386 /*************************************************/
387 /* New changes implemented by HRI */
388 /* 10 Levels inserted */
389 /*************************************************/
390
391 edw_log.put_line( 'About to call Supervisor Hierarchy summary table population routine' );
392 hri_struct_summary.load_all_sup_hierarchies;
393
394 open cur_check_sprvsr_hrchy;
395 fetch cur_check_sprvsr_hrchy into l_check_sprvsr_id;
396
397 /* Bug 3440848 - always collect supervisor tree */
398 /* Moved it outside cursor check */
399 if cur_check_sprvsr_hrchy%ISOPEN then
400 if cur_check_sprvsr_hrchy%NOTFOUND then
401 edw_log.put_line( 'Supervisor hierarchy is empty' );
402 else
403 edw_log.put_line( 'hri_struct_summary.load_all_sup_hierarchies completed OK.' );
404 end if;
405 end if;
406
407 edw_log.put_line( ' ' );
408 edw_log.put_line( 'About to call Supervisor Tree routine' );
409 edw_log.put_line( ' ' );
410
411 EDW_HR_PERSON_M_C.Push_Tree(
412 p_from_date => g_push_date_range1,
413 p_to_date => g_push_date_range2,
414 p_tree => 1 );
415
416 edw_log.put_line( ' Supervisor Tree routine completed ok' );
417 edw_log.put_line( ' ' );
418
419 /*************************************************/
420
421 edw_log.put_line(' ');
422 edw_log.put_line('Pushing data');
423
424 l_temp_date := sysdate;
425
426 Push_EDW_HR_PERM_ASSIGN_LSTG(EDW_HR_PERSON_M_C.g_push_date_range1, EDW_HR_PERSON_M_C.g_push_date_range2);
427
428
429 l_duration := sysdate - l_temp_date;
430
431 edw_log.put_line('Total rows inserted : '||g_row_count);
432 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
433 edw_log.put_line(' ');
434 -- ---------------------------------------------------------------------------
435 -- END OF Collection , Developer Customizable Section
436 -- ---------------------------------------------------------------------------
437 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, g_push_date_range1, g_push_date_range2 );
438 commit;
439
440 Exception When others then
441 Errbuf:=sqlerrm;
442 Retcode:=sqlcode;
443 l_exception_msg := Retcode || ':' || Errbuf;
444 EDW_HR_PERSON_M_C.g_exception_msg := l_exception_msg;
445 rollback;
446 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_HR_PERSON_M_C.g_exception_msg, g_push_date_range1, g_push_date_range2);
447
448 commit;
449 End;
450
451
452 Procedure Push_EDW_HR_PERM_ASSIGN_LSTG(p_from_date IN date, p_to_date IN DATE) IS
453 l_date1 DATE;
454 l_date2 DATE;
455 l_rows_inserted NUMBER :=0;
456 BEGIN
457 edw_log.put_line('Starting Push_EDW_HR_PERM_ASSIGN_LSTG');
458 l_date1 := p_from_date;
459 l_date2 := p_to_date;
460 Insert Into
461 EDW_HR_PERM_ASSIGN_LSTG@EDW_APPS_TO_WH(
462 ASSIGNMENT_PK,
463 BUSINESS_GROUP,
464 CREATION_DATE,
465 END_DATE,
466 INSTANCE,
467 LAST_UPDATE_DATE,
468 NAME,
469 NAME_DISPLAY,
470 START_DATE,
471 USER_ATTRIBUTE1,
472 USER_ATTRIBUTE2,
473 USER_ATTRIBUTE3,
474 USER_ATTRIBUTE4,
475 USER_ATTRIBUTE5,
476 NATIONAL_IDENTIFIER,
477 PERSON_DP,
478 PERSON_ID,
479 PERSON_NUM,
480 PLANNER_CODE,
481 PLANNER_FLAG,
482 PREVIOUS_LAST_NAME,
483 REGION_OF_BIRTH,
484 REHIRE_RCMMNDTN,
485 RESUME_EXISTS,
486 RESUME_UPDATED_DATE,
487 SALESREP_ID,
488 SALES_REP_FLAG,
489 STUDENT_STATUS,
490 SYS_GEN_FLAG,
491 TITLE,
492 TOWN_OF_BIRTH,
493 ALL_FK,
494 SPRVSR_LVL1_FK,
495 BUYER_FLAG,
496 COUNTRY_OF_BIRTH,
497 CRRSPNDNC_LANGUAGE,
498 DATE_EMP_DATA_VRFD,
499 DATE_OF_BIRTH,
500 DISABILITY_FLAG,
501 EFFECTIVE_END_DATE,
502 EFFECTIVE_START_DATE,
503 EMAIL_ADDRESS,
504 FAST_PATH_EMPLOYEE,
505 FIRST_NAME,
506 FTE_CAPACITY,
507 FULL_NAME,
508 GENDER,
509 GLOBAL_PERSON_ID,
510 INTERNAL_LOCATION,
511 KNOWN_AS,
512 LAST_NAME,
513 MAILSTOP,
514 MARITAL_STATUS,
515 MIDDLE_NAMES,
516 NAME_PREFIX,
517 NAME_SUFFIX,
518 NATIONALITY,
519 OPERATION_CODE,
520 COLLECTION_STATUS,
521 /* New for 115.1 */
522 EMPLOYEE_FLAG,
523 APPLICANT_FLAG)
524 select plcv.ASSIGNMENT_PK,
525 plcv.BUSINESS_GROUP,
526 plcv.CREATION_DATE,
527 plcv.END_DATE,
528 plcv.INSTANCE,
529 plcv.LAST_UPDATE_DATE,
530 /***************************************************/
531 /* The Name string is reformatted here and the */
532 /* name attribute from the LCV is ignored. This */
533 /* is to facilitate easy changing of how this */
534 /* string is constructed. */
535 /***************************************************/
536 DECODE(plcv.person_id,NULL,plcv.name,
537 DECODE(plcv.known_as||plcv.first_name,NULL,'',
538 NVL(plcv.known_as,plcv.first_name)||' ')
539 ||plcv.last_name || '('||plcv.person_num||')'
540 ) NAME,
541 DECODE(plcv.person_id,NULL,plcv.name,
542 DECODE(plcv.known_as||plcv.first_name,NULL,'',
543 NVL(plcv.known_as,plcv.first_name)||' ')
544 ||plcv.last_name
545 ) NAME_DISPLAY,
546 plcv.START_DATE,
547 plcv.USER_ATTRIBUTE1,
548 plcv.USER_ATTRIBUTE2,
549 plcv.USER_ATTRIBUTE3,
550 plcv.USER_ATTRIBUTE4,
551 plcv.USER_ATTRIBUTE5,
552 plcv.NATIONAL_IDENTIFIER,
553 plcv.PERSON_DP,
554 plcv.PERSON_ID,
555 plcv.PERSON_NUM,
556 plcv.PLANNER_CODE,
557 plcv.PLANNER_FLAG,
558 plcv.PREVIOUS_LAST_NAME,
559 plcv.REGION_OF_BIRTH,
560 plcv.REHIRE_RCMMNDTN,
561 plcv.RESUME_EXISTS,
562 plcv.RESUME_UPDATED_DATE,
563 plcv.SALESREP_ID,
564 plcv.SALES_REP_FLAG,
565 plcv.STUDENT_STATUS,
566 plcv.SYS_GEN_FLAG,
567 plcv.TITLE,
568 plcv.TOWN_OF_BIRTH,
569 NVL(plcv.ALL_FK, 'NA_EDW'),
570 plcv.SPRVSR_LVL1_FK,
571 plcv.BUYER_FLAG,
572 plcv.COUNTRY_OF_BIRTH,
573 plcv.CRRSPNDNC_LANGUAGE,
574 plcv.DATE_EMP_DATA_VRFD,
575 plcv.DATE_OF_BIRTH,
576 plcv.DISABILITY_FLAG,
577 plcv.EFFECTIVE_END_DATE,
578 plcv.EFFECTIVE_START_DATE,
579 plcv.EMAIL_ADDRESS,
580 plcv.FAST_PATH_EMPLOYEE,
581 plcv.FIRST_NAME,
582 plcv.FTE_CAPACITY,
583 plcv.FULL_NAME,
584 plcv.GENDER,
585 plcv.GLOBAL_PERSON_ID,
586 plcv.INTERNAL_LOCATION,
587 plcv.KNOWN_AS,
588 plcv.LAST_NAME,
589 plcv.MAILSTOP,
590 plcv.MARITAL_STATUS,
591 plcv.MIDDLE_NAMES,
592 plcv.NAME_PREFIX,
593 plcv.NAME_SUFFIX,
594 plcv.NATIONALITY,
595 NULL, -- OPERATION_CODE
596 'READY',
597 plcv.EMPLOYEE_FLAG,
598 plcv.APPLICANT_FLAG
599 from EDW_HR_PERM_ASSIGN_LCV@APPS_TO_APPS plcv
600 where plcv.last_update_date between l_date1 and l_date2;
601 --
602 l_rows_inserted := sql%rowcount;
603 EDW_HR_PERSON_M_C.g_row_count := EDW_HR_PERSON_M_C.g_row_count + l_rows_inserted ;
604 edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
605 ' rows into the EDW_HR_PERM_ASSIGN_LSTG staging table');
606 edw_log.put_line('Commiting records for EDW_HR_PERM_ASSIGN_LSTG');
607 commit;
608
609 edw_log.put_line('Completed Push_EDW_HR_PERM_ASSIGN_LSTG');
610 Exception When others then
611 raise;
612 commit;
613 END;
614
615 BEGIN
616
617 SELECT instance_code INTO g_instance_code
618 FROM edw_local_instance;
619
620 End EDW_HR_PERSON_M_C;