[Home] [Help]
PACKAGE BODY: APPS.EDW_ORGANIZATION_M_C
Source
1 Package Body EDW_ORGANIZATION_M_C AS
2 /* $Header: hrieporg.pkb 120.1 2005/06/07 05:16:57 anmajumd noship $ */
3
4 g_row_count NUMBER:= 0;
5 g_exception_message VARCHAR2(2000) := NULL;
6
7 g_number_of_levels NUMBER := 8; -- For Organization Hierarchies
8
9 /********************************************************************/
10 /* This procedure dynamically builds a sql statement to insert rows */
11 /* into the given org hierarchy level table from the given level */
12 /* collection view */
13 /********************************************************************/
14 Procedure Do_Insert( p_tree_number IN NUMBER,
15 p_from_level IN NUMBER,
16 p_to_level IN NUMBER,
17 p_from_date IN DATE,
18 p_to_date IN DATE)
19 IS
20
21 l_sql_stmt VARCHAR2(2000); -- Holds SQL Statement to be executed
22 l_ret_code NUMBER; -- Keeps return code of sql execution
23
24 l_from_view VARCHAR2(50); -- Name of the collection view
25 l_to_table VARCHAR2(50); -- Name of the staging table
26
27 l_pk_column VARCHAR2(30); -- Staging table pk column
28 l_fk_column VARCHAR2(30); -- Staging table fk column
29 l_pk_value VARCHAR2(60); -- Pk value selected from collection view
30 l_fk_value VARCHAR2(60); -- Fk value selected from collection view
31
32 l_push_from_level VARCHAR2(30); -- Push from level name
33 l_push_lookup VARCHAR2(80); -- Push down lookup
34
35 l_temp_date DATE; -- Keeps track of execution start time
36 l_duration NUMBER := 0; -- Execution time
37 l_rows_inserted NUMBER := 0; -- Number of rows inserted
38
39 BEGIN
40
41 /* Construct the table, view and level names */
42 /**************************************/
43 l_from_view := 'EDW_ORGA_TREE' || p_tree_number || '_LVL' || p_from_level ||
44 '_LCV@APPS_TO_APPS';
45
46 l_to_table := 'EDW_ORGA_TREE' || p_tree_number || '_LVL' || p_to_level ||
47 '_LSTG@EDW_APPS_TO_WH';
48
49 l_push_from_level := 'ORG_TREE' || p_tree_number || '_LVL' || p_from_level;
50
51
52 /* Construct the primary and foreign key names from the staging table */
53 /**********************************************************************/
54 l_pk_column := 'ORG_TREE' || p_tree_number || '_LVL' || p_to_level || '_PK';
55
56 /* If top level staging table then fk column is ALL */
57 IF (p_to_level = g_number_of_levels) THEN
58 l_fk_column := 'ALL_FK';
59 ELSE
60 l_fk_column := 'ORG_TREE' || to_char(p_tree_number) || '_LVL' ||
61 to_char(p_to_level+1) || '_FK';
62 END IF;
63
64
65 /* Construct the primary and foreign key names from the collection view */
66 /************************************************************************/
67 /* If straight push, then staging table columns match collection view columns */
68 IF (p_from_level = p_to_level) THEN
69 l_pk_value := l_pk_column;
70 l_fk_value := 'NVL(' || l_fk_column || ',''NA_EDW'')';
71 /* Otherwise append "-TnLm" tag for push down and get push lookup */
72 ELSE
73 l_pk_value := 'ORG_TREE' || p_tree_number || '_LVL' || p_from_level ||
74 '_PK || ''-T' || p_tree_number || 'L' || p_from_level || '''';
75 /* If only pushing down 1 level, then point to pk of level above */
76 IF (p_to_level = p_from_level - 1) THEN
77 l_fk_value := 'ORG_TREE' || p_tree_number || '_LVL' ||
78 p_from_level || '_PK';
79 /* Otherwise point to pk plus tag of level above */
80 ELSE
81 l_fk_value := l_pk_value;
82 END IF;
83 END IF;
84
85 /* If pushing down, fetch the push down level lookup */
86 /*****************************************************/
87 IF (p_from_level > p_to_level) THEN
88 /* Get push lookup */
89 l_push_lookup := EDW_COLLECTION_UTIL.get_lookup_value(
90 'EDW_LEVEL_LOOKUP', l_push_from_level);
91 /* Write warning message if lookup doesn't exist */
92 IF (l_push_lookup IS NULL) THEN
93 edw_log.put_line('**Warning**: No Lookup Code Found in GET_LOOKUP_VALUE');
94 edw_log.put_line('when Pushing Tree ' || p_tree_number || ' Level '
95 || p_from_level);
96 END IF;
97 END IF;
98
99
100 /******************************************************************************/
101 /* BUILD UP THE SQL STATEMENT */
102 /******************************************************************************/
103
104 /* Not a push down - straight insert */
105 /*************************************/
106 IF (p_from_level = p_to_level) THEN
107
108 l_sql_stmt := 'Insert Into ' || l_to_table || '(
109 BUSINESS_GROUP,
110 CREATION_DATE,
111 INSTANCE,
112 LAST_UPDATE_DATE,
113 NAME,
114 ORGANIZATION_ID,
115 ' || l_pk_column || ',
116 ' || l_fk_column || ',
117 PRIMARY_ORG_DP,
118 OPERATION_CODE,
119 COLLECTION_STATUS,
120 USER_ATTRIBUTE1,
121 USER_ATTRIBUTE2,
122 USER_ATTRIBUTE3,
123 USER_ATTRIBUTE4,
124 USER_ATTRIBUTE5)
125 select BUSINESS_GROUP,
126 sysdate,
127 INSTANCE,
128 sysdate,
129 NAME,
130 ORGANIZATION_ID,
131 ' || l_pk_value || ',
132 ' || l_fk_value || ',
133 PRIMARY_ORG_DP,
134 NULL, -- OPERATION_CODE
135 ''READY'',
136 NULL,
137 NULL,
138 NULL,
139 NULL,
140 NULL
141 from ' || l_from_view || '
142 where last_update_date between :date_from and :date_to';
143
144 /******************************************************************************/
145 /* Push Down from a higher level */
146 /*********************************/
147 ELSE
148 l_sql_stmt := 'Insert Into ' || l_to_table || '(
149 BUSINESS_GROUP,
150 CREATION_DATE,
151 INSTANCE,
152 LAST_UPDATE_DATE,
153 NAME,
154 ORGANIZATION_ID,
155 ' || l_pk_column || ',
156 ' || l_fk_column || ',
157 PRIMARY_ORG_DP,
158 OPERATION_CODE,
159 COLLECTION_STATUS,
160 USER_ATTRIBUTE1,
161 USER_ATTRIBUTE2,
162 USER_ATTRIBUTE3,
163 USER_ATTRIBUTE4,
164 USER_ATTRIBUTE5)
165 select BUSINESS_GROUP,
166 sysdate,
167 INSTANCE,
168 sysdate,
169 ''' || l_push_lookup || ''' || ''('' || NAME || '')'',
170 ORGANIZATION_ID,
171 ' || l_pk_value || ',
172 ' || l_fk_value || ',
173 ''' || l_push_lookup || ''' || ''('' || NAME || '')'',
174 NULL, -- OPERATION_CODE
175 ''READY'',
176 NULL,
177 NULL,
178 NULL,
179 NULL,
180 NULL
181 from ' || l_from_view || '
182 where last_update_date between :date_from and :date_to
183 and NAME is not null';
184
185 END IF;
186
187 edw_log.put_line( 'Pushing Tree ' || p_tree_number || ' Level ' ||
188 p_from_level || ' to Level ' || p_to_level );
189
190 l_temp_date := SYSDATE;
191 EXECUTE IMMEDIATE l_sql_stmt USING p_from_date, p_to_date;
192 l_duration := sysdate - l_temp_date;
193
194 l_rows_inserted := sql%rowcount;
195 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
196 ' rows into the ' || l_to_table || ' staging table');
197
198 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
199 edw_log.put_line(' ');
200
201 End Do_Insert;
202
203 Procedure Push_INT_ORGANIZATION(
204 Errbuf OUT NOCOPY Varchar2
205 ,Retcode OUT NOCOPY Varchar2
206 ,p_from_date IN Date
207 ,p_to_date IN Date
208 ) IS
209
210 l_staging_table_name Varchar2(30) := 'EDW_INT_ORGANIZATION_LSTG';
211 l_push_date_range1 Date := NULL;
212 l_push_date_range2 Date := NULL;
213 l_temp_date Date := NULL;
214 l_rows_inserted Number := 0;
215 l_duration Number := 0;
216 l_exception_msg Varchar2(2000) := Null;
217 l_tmp_str VARCHAR2 (120);
218
219 -- -------------------------------------------
220 -- Put any additional developer variables here
221 -- -------------------------------------------
222
223 Begin
224 Errbuf :=NULL;
225 Retcode:=0;
226
227 l_push_date_range1 := p_from_date;
228 l_push_date_range2 := p_to_date;
229
230 -- -----------------------------------------------------------------------------
231 -- Start of Collection , Developer Customizable Section
232 -- -----------------------------------------------------------------------------
233
234 edw_log.put_line(' ');
235 edw_log.put_line('Pushing bottom-level orgs');
236
237 l_temp_date := sysdate;
238
239 Insert Into EDW_ORGA_ORG_LSTG@EDW_APPS_TO_WH(
240 BUSINESS_GROUP,
241 ORGANIZATION_DP,
242 ORGANIZATION_PK,
243 ROW_ID,
244 DATE_FROM,
245 DATE_TO,
246 INSTANCE,
247 LAST_UPDATE_DATE,
248 CREATION_DATE,
249 NAME,
250 OPERATING_UNIT_FK,
251 OPERATING_UNIT_FK_KEY,
252 ORG_CODE,
253 ORG_INT_EXT_FLAG,
254 ORG_PRIM_CST_MTHD,
255 ORG_TYPE,
256 LEVEL_NAME,
257 PERSON_MANAGER_ID,
258 PERSON_MANAGER_FK,
259 PERSON_MANAGER_FK_KEY,
260 USER_ATTRIBUTE1,
261 USER_ATTRIBUTE2,
262 USER_ATTRIBUTE3,
263 USER_ATTRIBUTE4,
264 USER_ATTRIBUTE5,
265 REQUEST_ID,
266 OPERATION_CODE,
267 ERROR_CODE,
268 COLLECTION_STATUS,
269 /* New changes by HRI */
270 ORGANIZATION_ID,
271 ORG_CAT1,
272 ORG_CAT10,
273 ORG_CAT11,
274 ORG_CAT12,
275 ORG_CAT13,
276 ORG_CAT14,
277 ORG_CAT15,
278 ORG_CAT2,
279 ORG_CAT3,
280 ORG_CAT4,
281 ORG_CAT5,
282 ORG_CAT6,
283 ORG_CAT7,
284 ORG_CAT8,
285 ORG_CAT9,
286 ORG_TREE1_LVL1_FK)
287 select
288 BUSINESS_GROUP,
289 ORGANIZATION_DP,
290 ORGANIZATION_PK,
291 NULL, --ROW_ID,
292 DATE_FROM,
293 DATE_TO,
294 INSTANCE, --bis_edw_instance.get_code,
295 sysdate,
296 sysdate, --CREATION_DATE,
297 NAME,
298 nvl(OPERATING_UNIT_FK, 'NA_EDW'),
299 NULL, --OPERATING_UNIT_FK_KEY
300 ORG_CODE,
301 ORG_INT_EXT_FLAG,
302 ORG_PRIM_CST_MTHD,
303 ORG_TYPE,
304 LEVEL_NAME,
305 PERSON_MANAGER_ID,
306 PERSON_MANAGER_FK,
307 PERSON_MANAGER_FK_KEY,
308 NULL, --USER_ATTRIBUTE1,
309 NULL, --USER_ATTRIBUTE2,
310 NULL, --USER_ATTRIBUTE3,
311 NULL, --USER_ATTRIBUTE4,
312 NULL, --USER_ATTRIBUTE5,
313 NULL, --REQUEST_ID,
314 NULL, --OPERATION_CODE
315 NULL, --ERROR_CODE
316 'READY',
317 ORGANIZATION_ID,
318 ORG_CAT1,
319 ORG_CAT10,
320 ORG_CAT11,
321 ORG_CAT12,
322 ORG_CAT13,
323 ORG_CAT14,
324 ORG_CAT15,
325 ORG_CAT2,
326 ORG_CAT3,
327 ORG_CAT4,
328 ORG_CAT5,
329 ORG_CAT6,
330 ORG_CAT7,
331 ORG_CAT8,
332 ORG_CAT9,
333 NVL(ORG_TREE1_LVL1_FK, 'NA_EDW')
334 from EDW_ORGA_ORG_LCV@APPS_TO_APPS
335 where last_update_date between l_push_date_range1 and l_push_date_range2
336 or (last_update_date is null);
337
338 l_rows_inserted := nvl(sql%rowcount,0);
339 l_duration := sysdate - l_temp_date;
340
341 edw_log.put_line('Inserted ' || to_char(l_rows_inserted) ||
342 ' rows into the EDW_ORGA_ORG_LSTG staging table');
343 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
344 edw_log.put_line(' ');
345
346 EDW_ORGANIZATION_M_C.g_row_count := EDW_ORGANIZATION_M_C.g_row_count +
347 l_rows_inserted;
348
349 -- ---------------------------------------------------------------------------
350 -- END OF Collection , Developer Customizable Section
351 -- ---------------------------------------------------------------------------
352
353 Exception
354
355 When others then
356
357 Errbuf := sqlerrm;
358 Retcode := sqlcode;
359 EDW_ORGANIZATION_M_C.g_exception_message := Retcode || ':' || Errbuf;
360 rollback;
361
362 raise;
363
364 End Push_INT_ORGANIZATION;
365
366 Procedure Push_Oper_Unit(
367 Errbuf OUT NOCOPY Varchar2
368 ,Retcode OUT NOCOPY Varchar2
369 ,p_from_date IN Date
370 ,p_to_date IN Date
371 ) IS
372
373 l_staging_table_name Varchar2(30) := 'EDW_OPER_UNIT_LSTG';
374 g_push_date_range1 Date := NULL;
375 g_push_date_range2 Date := NULL;
376 l_temp_date Date := NULL;
377 l_rows_inserted Number := 0;
378 l_duration Number := 0;
379 l_exception_msg Varchar2(2000) := Null;
380 l_tmp_str1 VARCHAR2 (120);
381 l_tmp_str2 VARCHAR2 (120);
382
383 -- -------------------------------------------
384 -- Put any additional developer variables here
385 -- -------------------------------------------
386
387 Begin
388 Errbuf :=NULL;
389 Retcode :=0;
390
391 g_push_date_range1 := p_from_date;
392 g_push_date_range2 := p_to_date;
393
394 -- -----------------------------------------------------------------------------
395 -- Start of Collection , Developer Customizable Section
396 -- -----------------------------------------------------------------------------
397
398 edw_log.put_line(' ');
399 edw_log.put_line('Pushing Operating Units');
400
401 l_temp_date := sysdate;
402
403 Insert Into EDW_ORGA_OPER_UNIT_LSTG@EDW_APPS_TO_WH(
404 BUSINESS_GROUP,
405 DATE_FROM,
406 DATE_TO,
407 INSTANCE,
408 INT_EXT_FLAG,
409 LAST_UPDATE_DATE,
410 CREATION_DATE,
411 LEGAL_ENTITY_FK,
412 LEGAL_ENTITY_FK_KEY,
413 NAME,
414 OPERATING_UNIT_DP,
415 OPERATING_UNIT_PK,
416 ROW_ID,
417 ORG_CODE,
418 ORG_TYPE,
419 PRIMARY_CST_MTHD,
420 LEVEL_NAME,
421 USER_ATTRIBUTE1,
422 USER_ATTRIBUTE2,
423 USER_ATTRIBUTE3,
424 USER_ATTRIBUTE4,
425 USER_ATTRIBUTE5,
426 REQUEST_ID,
427 OPERATION_CODE,
428 ERROR_CODE,
429 COLLECTION_STATUS,
430 /* New change by HRI */
431 OPERATING_UNIT_ID)
432 select
433 BUSINESS_GROUP,
434 DATE_FROM,
435 DATE_TO,
436 INSTANCE, --bis_edw_instance.get_code,
437 INT_EXT_FLAG,
438 sysdate,
439 sysdate, --CREATION_DATE
440 nvl(LEGAL_ENTITY_FK, 'NA_EDW'),
441 NULL, --LEGAL_ENTITY_FK_KEY,
442 NAME,
443 OPERATING_UNIT_DP,
444 OPERATING_UNIT_PK,
445 null, ---rowid
446 ORG_CODE,
447 ORG_TYPE,
448 PRIMARY_CST_MTHD,
449 LEVEL_NAME,
450 NULL, --USER_ATTRIBUTE1,
451 NULL, --USER_ATTRIBUTE2,
452 NULL, --USER_ATTRIBUTE3,
453 NULL, --USER_ATTRIBUTE4,
454 NULL, --USER_ATTRIBUTE5,
455 NULL, --REQUEST_ID,
456 NULL, --OPERATION_CODE
457 NULL, --ERROR_CODE,
458 'READY',
459 OPERATING_UNIT_ID
460 from EDW_ORGA_OPER_UNIT_LCV@apps_to_apps
461 where last_update_date between g_push_date_range1 and g_push_date_range2
462 or (last_update_date is null);
463
464 l_rows_inserted := nvl(sql%rowcount,0);
465 l_duration := sysdate - l_temp_date;
466
467 edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
468 ' rows into the EDW_ORGA_OPER_UNIT_LSTG staging table');
469 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
470 edw_log.put_line(' ');
471
472
473 -- Start of change by S.Bhattal, 11-OCT-2000
474
475 l_tmp_str1 := EDW_COLLECTION_UTIL.get_lookup_value(
476 'EDW_LEVEL_LOOKUP', 'ORG_OPERATING_UNIT' );
477
478 l_tmp_str2 := EDW_COLLECTION_UTIL.get_lookup_value(
479 'EDW_LEVEL_LOOKUP', 'ORG_LEGAL_ENTITY' );
480
481 if (l_tmp_str1 is null) or (l_tmp_str2 is null) THEN
482 edw_log.put_line('***Warning*** : No Lookup Code Found in GET_LOOKUP_VALUE when Pushing Operating Unit');
483 end if;
484
485 -- End of change by S.Bhattal, 11-OCT-2000
486
487 edw_log.put_line( 'Pushing Business Groups to Operating Unit level' );
488
489 Insert Into EDW_ORGA_OPER_UNIT_LSTG@EDW_APPS_TO_WH(
490 BUSINESS_GROUP,
491 DATE_FROM,
492 DATE_TO,
493 INSTANCE,
494 INT_EXT_FLAG,
495 LAST_UPDATE_DATE,
496 CREATION_DATE,
497 LEGAL_ENTITY_FK,
498 LEGAL_ENTITY_FK_KEY,
499 NAME,
500 OPERATING_UNIT_DP,
501 OPERATING_UNIT_PK,
502 ROW_ID,
503 ORG_CODE,
504 ORG_TYPE,
505 PRIMARY_CST_MTHD,
506 LEVEL_NAME,
507 USER_ATTRIBUTE1,
508 USER_ATTRIBUTE2,
509 USER_ATTRIBUTE3,
510 USER_ATTRIBUTE4,
511 USER_ATTRIBUTE5,
512 REQUEST_ID,
513 OPERATION_CODE,
514 ERROR_CODE,
515 COLLECTION_STATUS,
516 /* New change by HRI */
517 OPERATING_UNIT_ID)
518 select
519 null, -- BUSINESS_GROUP,
520 DATE_FROM,
521 DATE_TO,
522 INSTANCE, --bis_edw_instance.get_code,
523 INT_EXT_FLAG,
524 sysdate,
525 sysdate, --CREATION_DATE
526 BUSINESS_GROUP_PK ||'-'||'BGRP',
527 NULL, --LEGAL_ENTITY_FK_KEY,
528 l_tmp_str1 || ' (' || l_tmp_str2 || ' (' || NAME || '))', --for: NAME
529 l_tmp_str1 || ' (' || l_tmp_str2 || ' (' || NAME || '))', --for: OPERATING_UNIT_DP
530 BUSINESS_GROUP_PK ||'-'||'BGRP',
531 null, --rowid
532 ORG_CODE,
533 ORG_TYPE,
534 PRIMARY_CST_MTHD,
535 'BGRP',
536 NULL, --USER_ATTRIBUTE1,
537 NULL, --USER_ATTRIBUTE2,
538 NULL, --USER_ATTRIBUTE3,
539 NULL, --USER_ATTRIBUTE4,
540 NULL, --USER_ATTRIBUTE5,
541 NULL, --REQUEST_ID,
542 NULL, --OPERATION_CODE
543 NULL, --ERROR_CODE,
544 'READY',
545 BUSINESS_GROUP_ID
546 from EDW_ORGA_BUSINESS_GRP_LCV@apps_to_apps
547 where last_update_date between g_push_date_range1 and g_push_date_range2
548 or (last_update_date is null);
549
550 l_rows_inserted := nvl(sql%rowcount,0);
551 l_duration := sysdate - l_temp_date;
552
553 edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
554 ' rows into the EDW_ORGA_BUSINESS_GROUP_LSTG staging table');
555 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
556 edw_log.put_line(' ');
557
558 -- ---------------------------------------------------------------------------
559 -- END OF Collection , Developer Customizable Section
560 -- ---------------------------------------------------------------------------
561
562
563 Exception
564
565 When others then
566
567 Errbuf := sqlerrm;
568 Retcode := sqlcode;
569 EDW_ORGANIZATION_M_C.g_exception_message := Retcode || ':' || Errbuf;
570 rollback;
571
572 raise;
573
574 End Push_Oper_Unit;
575
576 Procedure Push_Legal_Entity(
577 Errbuf OUT NOCOPY Varchar2
578 ,Retcode OUT NOCOPY Varchar2
579 ,p_from_date IN Date
580 ,p_to_date IN Date
581 ) IS
582
583 l_staging_table_name Varchar2(30) := 'EDW_LEGAL_ENTITY_LSTG';
584 g_push_date_range1 Date := NULL;
585 g_push_date_range2 Date := NULL;
586 l_temp_date Date := NULL;
587 l_rows_inserted Number := 0;
588 l_duration Number := 0;
589 l_exception_msg Varchar2(2000) := Null;
590 l_tmp_str VARCHAR2 (120);
591
592 -- -------------------------------------------
593 -- Put any additional developer variables here
594 -- -------------------------------------------
595
596 Begin
597 Errbuf :=NULL;
598 Retcode:=0;
599
600 g_push_date_range1 := p_from_date;
601 g_push_date_range2 := p_to_date;
602
603 -- -----------------------------------------------------------------------------
604 -- Start of Collection , Developer Customizable Section
605 -- -----------------------------------------------------------------------------
606
607 edw_log.put_line(' ');
608 edw_log.put_line('Pushing Legal Entities');
609
610 l_temp_date := sysdate;
611
612 Insert Into EDW_ORGA_LEG_ENTITY_LSTG@EDW_APPS_TO_WH(
613 BUSINESS_GROUP_FK,
614 BUSINESS_GROUP_FK_KEY,
615 DATE_FROM,
616 DATE_TO,
617 INSTANCE,
618 INT_EXT_FLAG,
619 LAST_UPDATE_DATE,
620 CREATION_DATE,
621 LEGAL_ENTITY_DP,
622 LEGAL_ENTITY_PK,
623 ROW_ID,
624 NAME,
625 ORG_CODE,
626 ORG_TYPE,
627 PRIMARY_CST_MTHD,
628 SET_OF_BOOKS,
629 LEVEL_NAME,
630 USER_ATTRIBUTE1,
631 USER_ATTRIBUTE2,
632 USER_ATTRIBUTE3,
633 USER_ATTRIBUTE4,
634 USER_ATTRIBUTE5,
635 REQUEST_ID,
636 OPERATION_CODE,
637 ERROR_CODE,
638 COLLECTION_STATUS,
639 /* New change by HRI */
640 LEGAL_ENTITY_ID)
641 select
642 nvl(BUSINESS_GROUP_FK, 'NA_EDW'),
643 NULL, --BUSINESS_GROUP_FK,
644 DATE_FROM,
645 DATE_TO,
646 INSTANCE, --bis_edw_instance.get_code,
647 INT_EXT_FLAG,
648 sysdate,
649 sysdate, --CREATION_DATE,
650 LEGAL_ENTITY_DP,
651 LEGAL_ENTITY_PK,
652 NULL, --ROW_ID,
653 NAME,
654 ORG_CODE,
655 ORG_TYPE,
656 PRIMARY_CST_MTHD,
657 SET_OF_BOOKS,
658 LEVEL_NAME,
659 NULL, --USER_ATTRIBUTE1,
660 NULL, --USER_ATTRIBUTE2,
661 NULL, --USER_ATTRIBUTE3,
662 NULL, --USER_ATTRIBUTE4,
663 NULL, --USER_ATTRIBUTE5,
664 NULL, --REQUEST_ID,
665 NULL, --OPERATION_CODE
666 NULL, --ERROR_CODE,
667 'READY',
668 LEGAL_ENTITY_ID
669 from EDW_ORGA_LEG_ENTITY_LCV@apps_to_apps
670 where last_update_date between g_push_date_range1 and g_push_date_range2
671 or (last_update_date is null);
672
673 l_rows_inserted := nvl(sql%rowcount,0);
674 l_duration := sysdate - l_temp_date;
675
676 edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
677 ' rows into the EDW_ORGA_LEG_ENTITY_LSTG staging table');
678
679 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
680 edw_log.put_line(' ');
681
682 -- Start of change by S.Bhattal, 11-OCT-2000
683
684 l_tmp_str := EDW_COLLECTION_UTIL.get_lookup_value(
685 'EDW_LEVEL_LOOKUP', 'ORG_LEGAL_ENTITY' );
686
687 if(l_tmp_str is null) THEN
688 edw_log.put_line('***Warning*** : No Lookup Code Found in GET_LOOKUP_VALUE when Pushing Legal Entity');
689 end if;
690
691 -- End of change by S.Bhattal, 11-OCT-2000
692
693 edw_log.put_line( 'Pushing Business Groups to Legal Entity level' );
694
695 Insert Into EDW_ORGA_LEG_ENTITY_LSTG@EDW_APPS_TO_WH(
696 BUSINESS_GROUP_FK,
697 BUSINESS_GROUP_FK_KEY,
698 DATE_FROM,
699 DATE_TO,
700 INSTANCE,
701 INT_EXT_FLAG,
702 LAST_UPDATE_DATE,
703 CREATION_DATE,
704 LEGAL_ENTITY_DP,
705 LEGAL_ENTITY_PK,
706 ROW_ID,
707 NAME,
708 ORG_CODE,
709 ORG_TYPE,
710 PRIMARY_CST_MTHD,
711 SET_OF_BOOKS,
712 LEVEL_NAME,
713 USER_ATTRIBUTE1,
714 USER_ATTRIBUTE2,
715 USER_ATTRIBUTE3,
716 USER_ATTRIBUTE4,
717 USER_ATTRIBUTE5,
718 REQUEST_ID,
719 OPERATION_CODE,
720 ERROR_CODE,
721 COLLECTION_STATUS,
722 /* New change by HRI */
723 LEGAL_ENTITY_ID)
724 select
725 BUSINESS_GROUP_PK,
726 NULL, --BUSINESS_GROUP_FK,
727 DATE_FROM,
728 DATE_TO,
729 INSTANCE, --bis_edw_instance.get_code,
730 INT_EXT_FLAG,
731 sysdate,
732 sysdate, --CREATION_DATE,
733 l_tmp_str || ' (' || NAME || ')', --for: BUSINESS_GROUP_DP
734 BUSINESS_GROUP_PK ||'-'||'BGRP',
735 NULL, --ROW_ID,
736 l_tmp_str || ' (' || NAME || ')', --for: NAME,
737 ORG_CODE,
738 ORG_TYPE,
739 PRIMARY_CST_MTHD,
740 null, ---SET_OF_BOOKS,
741 'BGRP',
742 NULL, --USER_ATTRIBUTE1,
743 NULL, --USER_ATTRIBUTE2,
744 NULL, --USER_ATTRIBUTE3,
745 NULL, --USER_ATTRIBUTE4,
746 NULL, --USER_ATTRIBUTE5,
747 NULL, --REQUEST_ID,
748 NULL, --OPERATION_CODE
749 NULL, --ERROR_CODE,
750 'READY',
751 BUSINESS_GROUP_ID
752 from EDW_ORGA_BUSINESS_GRP_LCV@apps_to_apps
753 where last_update_date between g_push_date_range1 and g_push_date_range2
754 or (last_update_date is null);
755
756 l_rows_inserted := nvl(sql%rowcount,0);
757 l_duration := sysdate - l_temp_date;
758
759 edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
760 ' rows into the EDW_ORGA_BUSINESS_GRP_LSTG staging table');
761
762 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
763 edw_log.put_line(' ');
764
765 -- ---------------------------------------------------------------------------
766 -- END OF Collection , Developer Customizable Section
767 -- ---------------------------------------------------------------------------
768
769 Exception
770
771 When others then
772
773 Errbuf := sqlerrm;
774 Retcode := sqlcode;
775 EDW_ORGANIZATION_M_C.g_exception_message := Retcode || ':' || Errbuf;
776 rollback;
777
778 raise;
779
780 End Push_Legal_Entity;
781
782 Procedure Push_Business_Grp(
783 Errbuf OUT NOCOPY Varchar2
784 ,Retcode OUT NOCOPY Varchar2
785 ,p_from_date IN Date
786 ,p_to_date IN Date
787 ) IS
788
789 l_staging_table_name Varchar2(30) := 'EDW_BUSINESS_GRP_LSTG';
790 g_push_date_range1 Date := NULL;
791 g_push_date_range2 Date := NULL;
792 l_temp_date Date := NULL;
793 l_rows_inserted Number := 0;
794 l_duration Number := 0;
795 l_exception_msg Varchar2(2000) := Null;
796
797 -- -------------------------------------------
798 -- Put any additional developer variables here
799 -- -------------------------------------------
800
801 Begin
802 Errbuf :=NULL;
803 Retcode:=0;
804
805 g_push_date_range1 := p_from_date;
806 g_push_date_range2 := p_to_date;
807
808 -- -----------------------------------------------------------------------------
809 -- Start of Collection , Developer Customizable Section
810 -- -----------------------------------------------------------------------------
811
812 edw_log.put_line(' ');
813 edw_log.put_line('Pushing Business Groups');
814
815 l_temp_date := sysdate;
816
817 Insert Into EDW_ORGA_BUSINESS_GRP_LSTG@EDW_APPS_TO_WH(
818 ALL_FK,
819 ALL_FK_KEY,
820 BUSINESS_GROUP_DP,
821 BUSINESS_GROUP_PK,
822 ROW_ID,
823 DATE_FROM,
824 DATE_TO,
825 INSTANCE,
826 INT_EXT_FLAG,
827 LAST_UPDATE_DATE,
828 CREATION_DATE,
829 NAME,
830 ORG_CODE,
831 ORG_TYPE,
832 PRIMARY_CST_MTHD,
833 USER_ATTRIBUTE1,
834 USER_ATTRIBUTE2,
835 USER_ATTRIBUTE3,
836 USER_ATTRIBUTE4,
837 USER_ATTRIBUTE5,
838 REQUEST_ID,
839 OPERATION_CODE,
840 ERROR_CODE,
841 COLLECTION_STATUS,
842 /* New change by HRI */
843 BUSINESS_GROUP_ID,
844 COST_ALLOCATION,
845 LEGISLATION)
846 select
847 nvl(ALL_FK, 'NA_EDW'),
848 NULL, --ALL_FK_KEY
849 BUSINESS_GROUP_DP,
850 BUSINESS_GROUP_PK,
851 NULL, --ROW_ID,
852 DATE_FROM,
853 DATE_TO,
854 INSTANCE, --bis_edw_instance.get_code,
855 INT_EXT_FLAG,
856 sysdate,
857 sysdate, --CREATION_DATE,
858 NAME,
859 ORG_CODE,
860 ORG_TYPE,
861 PRIMARY_CST_MTHD,
862 NULL, --USER_ATTRIBUTE1,
863 NULL, --USER_ATTRIBUTE2,
864 NULL, --USER_ATTRIBUTE3,
865 NULL, --USER_ATTRIBUTE4,
866 NULL, --USER_ATTRIBUTE5,
867 NULL, --REQUEST_ID,
868 NULL, --OPERATION_CODE
869 NULL, --ERROR_ID,
870 'READY',
871 BUSINESS_GROUP_ID,
872 COST_ALLOCATION_FLEXFIELD,
873 LEGISLATION
874 from EDW_ORGA_BUSINESS_GRP_LCV@apps_to_apps
875 where last_update_date between g_push_date_range1 and g_push_date_range2
876 or (last_update_date is null);
877
878 l_rows_inserted := nvl(sql%rowcount,0);
879 l_duration := sysdate - l_temp_date;
880
881 edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
882 ' rows into the EDW_ORGA_BUSINESS_GRP_LSTG staging table');
883
884 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
885 edw_log.put_line(' ');
886
887 -- ---------------------------------------------------------------------------
888 -- END OF Collection , Developer Customizable Section
889 -- ---------------------------------------------------------------------------
890
891 Exception
892
893 When others then
894
895 edw_log.put_line( 'In exception section of Push_Business_Grp' );
896 Errbuf := sqlerrm;
897 Retcode := sqlcode;
898 EDW_ORGANIZATION_M_C.g_exception_message := Retcode || ':' || Errbuf;
899 rollback;
900
901 raise;
902
903 End Push_Business_Grp;
904
905 /********************************************************************************/
906 /* New Levels inserted by HRI */
907 /******************************/
908
909 Procedure Push_Tree( p_from_date IN DATE,
910 p_to_date IN DATE,
911 p_tree IN NUMBER )
912 IS
913 BEGIN
914
915 FOR v_push_to_level IN 1..g_number_of_levels LOOP
916
917 edw_log.put_line('Starting Push_EDW_ORGA_TREE' || p_tree || '_LVL' ||
918 v_push_to_level || '_LSTG');
919 edw_log.put_line(' ');
920
921 FOR v_push_from_view IN v_push_to_level..g_number_of_levels LOOP
922
923 Do_Insert( p_tree_number => p_tree,
924 p_from_level => v_push_from_view,
925 p_to_level => v_push_to_level,
926 p_from_date => p_from_date,
927 p_to_date => p_to_date );
928
929 END LOOP;
930
931 END LOOP;
932
933 END Push_Tree;
934
935 /********************************************************************************/
936
937
938 Procedure Push( Errbuf OUT NOCOPY Varchar2
939 ,Retcode OUT NOCOPY Varchar2
940 ,p_from_date IN Varchar2
941 ,p_to_date IN Varchar2
942 ) IS
943
944 g_push_date_range1 Date:= Null;
945 g_push_date_range2 Date:= Null;
946
947 -- Added by S.Bhattal, AUG-2000
948
949 l_from_date date;
950 l_to_date date;
951
952 Begin
953
954 Errbuf := NULL;
955 Retcode := 0;
956
957 If (Not EDW_COLLECTION_UTIL.setup('EDW_ORGANIZATION_M')) Then
958 Return;
959 End If;
960
961 -- Added by S.Bhattal, AUG-2000
962
963 edw_log.put_line( 'About to do 1st date conversion' );
964 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
965 edw_log.put_line( '1st date conversion completed ok' );
966 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
967 edw_log.put_line( '2nd date conversion completed ok' );
968
969 -- End of change
970
971 g_push_date_range1 := nvl(l_from_date,
972 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
973
974 g_push_date_range2 := nvl(l_to_date, EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
975
976 edw_log.put_line( 'The collection range is from '||
977 to_char(g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
978 to_char(g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
979
980 /* Call to HRI Org Hierarchy Table package */
981 /*******************************************/
982 edw_log.put_line(' ');
983 edw_log.put_line( 'Populating HRI Org Hierarchy table');
984 hri_edw_dim_organization.populate_primary_org_hrchy_tab;
985 edw_log.put_line( 'Finished populating HRI Org Hierarchy table');
986
987 edw_log.put_line(' ');
988 edw_log.put_line( 'About to call bottom-level orgs routine' );
989
990 Edw_Organization_m_C.Push_INT_ORGANIZATION(
991 Errbuf,
992 Retcode,
993 g_push_date_range1,
994 g_push_date_range2
995 );
996
997 edw_log.put_line( 'Bottom-level orgs routine completed ok' );
998 edw_log.put_line( 'About to call Operating Units routine' );
999
1000 Edw_Organization_M_C.Push_Oper_Unit(
1001 Errbuf,
1002 Retcode,
1003 g_push_date_range1,
1004 g_push_date_range2
1005 );
1006
1007 edw_log.put_line( 'Operating Units routine completed ok' );
1008 edw_log.put_line( 'About to call Legal Entities routine' );
1009
1010 Edw_Organization_m_C.Push_Legal_Entity(
1011 Errbuf,
1012 Retcode,
1013 g_push_date_range1,
1014 g_push_date_range2
1015 );
1016
1017 edw_log.put_line( 'Legal Entities routine completed ok' );
1018 edw_log.put_line( 'About to call Business Groups routine' );
1019
1020 Edw_Organization_M_C.Push_Business_Grp(
1021 Errbuf,
1022 Retcode,
1023 g_push_date_range1,
1024 g_push_date_range2
1025 );
1026
1027 edw_log.put_line( 'Business Groups routine completed ok' );
1028
1029
1030 /*************************************************/
1031 /* New changes implemented by HRI */
1032 /* 8 Levels inserted */
1033 /*************************************************/
1034
1035 edw_log.put_line( 'About to call Org Tree 1 routine' );
1036
1037 Edw_Organization_M_C.Push_Tree(
1038 p_from_date => g_push_date_range1,
1039 p_to_date => g_push_date_range2,
1040 p_tree => 1 );
1041
1042 edw_log.put_line( 'Org Tree 1 routine completed ok' );
1043
1044 /*************************************************/
1045
1046 EDW_COLLECTION_UTIL.wrapup(TRUE, EDW_ORGANIZATION_M_C.g_row_count, null, g_push_date_range1, g_push_date_range2);
1047
1048 Exception
1049
1050 When others then
1051
1052 Errbuf := sqlerrm;
1053 Retcode := sqlcode;
1054
1055 EDW_ORGANIZATION_M_C.g_exception_message := EDW_ORGANIZATION_M_C.g_exception_message ||'<>'||Retcode || ':' || Errbuf;
1056 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_ORGANIZATION_M_C.g_exception_message, g_push_date_range1, g_push_date_range2);
1057
1058 raise;
1059
1060 End Push;
1061
1062 End EDW_ORGANIZATION_M_C;