[Home] [Help]
PACKAGE BODY: APPS.EDW_GEOGRAPHY_M_C
Source
1 Package Body EDW_GEOGRAPHY_M_C AS
2 /* $Header: poaphge.pkb 120.1 2005/06/13 13:06:36 sriswami noship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_row_count_m Number:=0;
7 g_exception_msg varchar2(2000):=Null;
8
9 g_schema VARCHAR2(30);
10 g_stmt VARCHAR2(200);
11 g_status VARCHAR2(30);
12 g_industry VARCHAR2(30);
13 g_source_link VARCHAR2(128);
14 g_target_link VARCHAR2(128);
15
16 Procedure Push(Errbuf in out NOCOPY Varchar2,
17 Retcode in out NOCOPY Varchar2,
18 p_from_date IN Varchar2,
19 p_to_date IN Varchar2) IS
20 l_dimension_name Varchar2(30) :='EDW_GEOGRAPHY_M' ;
21 l_temp_date Date:=Null;
22 l_date1 Date:=Null;
23 l_date2 Date:=Null;
24 l_rows_inserted Number:=0;
25 l_duration Number:=0;
26 l_exception_msg Varchar2(2000):=Null;
27
28 -- -------------------------------------------
29 -- Put any additional developer variables here
30 -- -------------------------------------------
31 l_from_date date;
32 l_to_date date;
33
34 Begin
35 Errbuf :=NULL;
36 Retcode:=0;
37 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
38 errbuf := fnd_message.get;
39 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
40 END IF;
41
42 EDW_COLLECTION_UTIL.get_dblink_names(g_source_link, g_target_link);
43
44 fnd_date.initialize('YYYY/MM/DD', 'YYYY/MM/DD HH24:MI:SS');
45 l_from_date := fnd_date.displayDT_to_date(p_from_date);
46 l_to_date := fnd_date.displayDT_to_date(p_to_date);
47
48 g_push_date_range1 := nvl(l_from_date,
49 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
50 g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
51
52 l_date1 := g_push_date_range1;
53 l_date2 := g_push_date_range2;
54 edw_log.put_line( 'The collection range is from '||
55 to_char(l_date1,'MM/DD/YYYY HH24:MI:SS')||' to '||
56 to_char(l_date2,'MM/DD/YYYY HH24:MI:SS'));
57 edw_log.put_line(' ');
58
59 -- -----------------------------------------------------------------------------
60 -- Start of Collection , Developer Customizable Section
61 -- -----------------------------------------------------------------------------
62
63 edw_log.put_line(' ');
64 edw_log.put_line('Pushing data');
65
66 l_temp_date := sysdate;
67
68 IF (NOT FND_INSTALLATION.GET_APP_INFO('POA', g_status, g_industry, g_schema)) THEN
69 RAISE_APPLICATION_ERROR (-20001, '***There is not POA schema set up***');
70 END IF;
71
72 Push_GEOG_POSTCODE_CITY_LSTG(g_push_date_range1, g_push_date_range2);
73 Push_EDW_GEOG_LOCATION_LSTG (g_push_date_range1, g_push_date_range2);
74 Push_EDW_GEOG_CITY_LSTG (g_push_date_range1, g_push_date_range2);
75 Push_EDW_GEOG_POSTCODE_LSTG (g_push_date_range1, g_push_date_range2);
76 Push_GEOG_STATE_REGION_LSTG (g_push_date_range1, g_push_date_range2);
77 Push_EDW_GEOG_STATE_LSTG (g_push_date_range1, g_push_date_range2);
78 Push_EDW_GEOG_REGION_LSTG (g_push_date_range1, g_push_date_range2);
79 Push_EDW_GEOG_COUNTRY_LSTG (g_push_date_range1, g_push_date_range2);
80 Push_EDW_GEOG_AREA2_LSTG (g_push_date_range1, g_push_date_range2);
81 Push_EDW_GEOG_AREA1_LSTG (g_push_date_range1, g_push_date_range2);
82
83
84 l_duration := sysdate - l_temp_date;
85
86 edw_log.put_line('Process Time: '|| edw_log.duration(l_duration));
87 edw_log.put_line(' ');
88 -- ---------------------------------------------------------------------------
89 -- END OF Collection , Developer Customizable Section
90 -- ---------------------------------------------------------------------------
91
92 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count_m, P_PERIOD_START => l_date1,
93 P_PERIOD_END => l_date2);
94 commit;
95
96 Exception When others then
97 Errbuf:=sqlerrm;
98 Retcode:=sqlcode;
99 l_exception_msg := Retcode || ':' || Errbuf;
100 EDW_GEOGRAPHY_M_C.g_exception_msg := l_exception_msg;
101 rollback;
102 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_GEOGRAPHY_M_C.g_exception_msg,
103 g_push_date_range1, g_push_date_range2);
104 End;
105
106
107 Procedure Push_EDW_GEOG_LOCATION_LSTG(p_from_date IN date, p_to_date IN DATE) IS
108 l_date1 DATE;
109 l_date2 DATE;
110 l_rows_inserted NUMBER :=0;
111 BEGIN
112 edw_log.put_line('Starting Push_EDW_GEOG_LOCATION_LSTG');
113 l_date1 := p_from_date;
114 l_date2 := p_to_date;
115 Insert Into
116 EDW_GEOG_LOCATION_LSTG(
117 ADDRESS_LINE_1,
118 ADDRESS_LINE_2,
119 ADDRESS_LINE_3,
120 ADDRESS_LINE_4,
121 POSTCODE_CITY_FK,
122 CREATION_DATE,
123 INSTANCE,
124 LAST_UPDATE_DATE,
125 LOCATION_DP,
126 LOCATION_PK,
127 NAME,
128 USER_ATTRIBUTE1,
129 USER_ATTRIBUTE2,
130 USER_ATTRIBUTE3,
131 USER_ATTRIBUTE4,
132 USER_ATTRIBUTE5,
133 OPERATION_CODE,
134 COLLECTION_STATUS)
135 (select ADDRESS_LINE_1,
136 ADDRESS_LINE_2,
137 ADDRESS_LINE_3,
138 ADDRESS_LINE_4,
139 NVL(POSTCODE_CITY_FK, 'NA_EDW'),
140 CREATION_DATE,
141 INSTANCE,
142 LAST_UPDATE_DATE,
143 LOCATION_DP,
144 LOCATION_PK,
145 NAME,
146 USER_ATTRIBUTE1,
147 USER_ATTRIBUTE2,
148 USER_ATTRIBUTE3,
149 USER_ATTRIBUTE4,
150 USER_ATTRIBUTE5,
151 NULL, -- OPERATION_CODE
152 'READY'
153 from EDW_GEOG_LOCATION_LCV
154 where last_update_date between l_date1 and l_date2
155 union
156 select CITY_FK,
157 POSTCODE_FK,
158 NULL,
159 NULL,
160 POSTCODE_CITY_PK,
161 CREATION_DATE,
162 INSTANCE,
163 LAST_UPDATE_DATE,
164 POSTCODE_CITY_DP,
165 POSTCODE_CITY_PK,
166 NAME,
167 USER_ATTRIBUTE1,
168 USER_ATTRIBUTE2,
169 USER_ATTRIBUTE3,
170 USER_ATTRIBUTE4,
171 USER_ATTRIBUTE5,
172 NULL,
173 'READY'
174 from EDW_GEOG_POSTCODE_CITY_LSTG
175 where collection_status='READY');
176
177 l_rows_inserted := sql%rowcount;
178 EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
179
180 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
181 ' rows into the staging table');
182
183 edw_log.put_line('Completed Push_EDW_GEOG_LOCATION_LSTG');
184
185 EDW_GEOGRAPHY_M_C.g_row_count_m := l_rows_inserted;
186
187 Exception When others then
188 rollback;
189 raise;
190
191 END;
192
193
194 Procedure Push_GEOG_POSTCODE_CITY_LSTG(p_from_date IN date, p_to_date IN DATE) IS
195 l_date1 DATE;
196 l_date2 DATE;
197 l_rows_inserted NUMBER :=0;
198 BEGIN
199 edw_log.put_line('Starting Push_EDW_GEOG_POSTCODE_CITY_LSTG');
200
201
202 -------- To get PK info from the warehouse (bug #1757640) ------
203
204 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.POA_EDW_TEMP_GEOG';
205 EXECUTE IMMEDIATE g_stmt;
206
207 /* Insert from remote warehouse level table */
208 g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
209 'SELECT POSTCODE_CITY_PK FROM EDW_GEOG_POSTCODE_CITY_LTC@' ||
210 g_target_link;
211 EXECUTE IMMEDIATE g_stmt;
212
213 -----------------------------------------------------------------
214
215 l_date1 := p_from_date;
216 l_date2 := p_to_date;
217
218 Insert Into
219 EDW_GEOG_POSTCODE_CITY_LSTG(
220 INSTANCE,
221 USER_ATTRIBUTE1,
222 USER_ATTRIBUTE2,
223 USER_ATTRIBUTE3,
224 USER_ATTRIBUTE4,
225 USER_ATTRIBUTE5,
226 LAST_UPDATE_DATE,
227 CREATION_DATE,
228 POSTCODE_CITY_PK,
229 CITY_FK,
230 POSTCODE_FK,
231 POSTCODE_CITY_DP,
232 NAME,
233 OPERATION_CODE,
234 COLLECTION_STATUS)
235 select
236 INSTANCE,
237 USER_ATTRIBUTE1,
238 USER_ATTRIBUTE2,
239 USER_ATTRIBUTE3,
240 USER_ATTRIBUTE4,
241 USER_ATTRIBUTE5,
242 LAST_UPDATE_DATE,
243 CREATION_DATE,
244 POSTCODE_CITY_PK,
245 NVL(CITY_FK, 'NA_EDW'),
246 NVL(POSTCODE_FK, 'NA_EDW'),
247 POSTCODE_CITY_DP,
248 NAME,
249 NULL, -- OPERATION_CODE
250 'READY'
251 from
252 (select
253 INSTANCE,
254 USER_ATTRIBUTE1,
255 USER_ATTRIBUTE2,
256 USER_ATTRIBUTE3,
257 USER_ATTRIBUTE4,
258 USER_ATTRIBUTE5,
259 max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE ,
260 max(CREATION_DATE) as CREATION_DATE ,
261 POSTCODE_CITY_PK,
262 CITY_FK,
263 POSTCODE_FK,
264 POSTCODE_CITY_DP,
265 NAME
266 from EDW_GEOG_POSTCODE_CITY_LCV
267 where last_update_date between l_date1 and l_date2
268 group by
269 postcode_city_pk, city_fk, postcode_fk, postcode_city_dp,
270 name, instance, user_attribute1, user_attribute2,
271 user_attribute3, user_attribute4, user_attribute5)
272 where
273 NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where POSTCODE_CITY_PK = TEMP_PK);
274
275 l_rows_inserted := sql%rowcount;
276 EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
277
278 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
279 ' rows into the staging table');
280
281 edw_log.put_line('Completed Push_EDW_GEOG_POSTCODE_CITY_LSTG');
282
283 Exception When others then
284 rollback;
285 raise;
286
287 END;
288
289
290 Procedure Push_EDW_GEOG_CITY_LSTG(p_from_date IN date, p_to_date IN DATE) IS
291 l_date1 DATE;
292 l_date2 DATE;
293 l_rows_inserted NUMBER :=0;
294 BEGIN
295 edw_log.put_line('Starting Push_EDW_GEOG_CITY_LSTG');
296
297 -------- To get PK info from the warehouse (bug #1757640) ------
298
299 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.POA_EDW_TEMP_GEOG';
300 EXECUTE IMMEDIATE g_stmt;
301
302 /* Insert from remote warehouse level table */
303 g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
304 'SELECT CITY_PK FROM EDW_GEOG_CITY_LTC@' ||
305 g_target_link;
306 EXECUTE IMMEDIATE g_stmt;
307
308 -----------------------------------------------------------------
309
310 l_date1 := p_from_date;
311 l_date2 := p_to_date;
312 Insert Into
313 EDW_GEOG_CITY_LSTG(
314 CITY_PK,
315 STATE_REGION_FK,
316 CITY_DP,
317 NAME,
318 INSTANCE,
319 USER_ATTRIBUTE1,
320 USER_ATTRIBUTE2,
321 USER_ATTRIBUTE3,
322 USER_ATTRIBUTE4,
323 USER_ATTRIBUTE5,
324 LAST_UPDATE_DATE,
325 CREATION_DATE,
326 OPERATION_CODE,
327 COLLECTION_STATUS)
328 select
329 CITY_PK,
330 NVL(STATE_REGION_FK, 'NA_EDW'),
331 CITY_DP,
332 NAME,
333 INSTANCE,
334 USER_ATTRIBUTE1,
335 USER_ATTRIBUTE2,
336 USER_ATTRIBUTE3,
337 USER_ATTRIBUTE4,
338 USER_ATTRIBUTE5,
339 LAST_UPDATE_DATE,
340 CREATION_DATE,
341 NULL, -- OPERATION_CODE
342 'READY'
343 from
344 (select
345 CITY_PK,
346 STATE_REGION_FK,
347 CITY_DP,
348 NAME,
349 INSTANCE,
350 USER_ATTRIBUTE1,
351 USER_ATTRIBUTE2,
352 USER_ATTRIBUTE3,
353 USER_ATTRIBUTE4,
354 USER_ATTRIBUTE5,
355 max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
356 max(CREATION_DATE) as CREATION_DATE
357 from EDW_GEOG_CITY_LCV
358 where last_update_date between l_date1 and l_date2
359 GROUP BY
360 city_pk, state_region_fk, city_dp, name, instance,
361 user_attribute1, user_attribute2, user_attribute3,
362 user_attribute4, user_attribute5)
363 where
364 NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where CITY_PK = TEMP_PK);
365
366 l_rows_inserted := sql%rowcount;
367 g_row_count := g_row_count + l_rows_inserted ;
368
369 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
370 ' rows into the staging table');
371
372 edw_log.put_line('Completed Push_EDW_GEOG_CITY_LSTG');
373
374 Exception When others then
375 rollback;
376 raise;
377
378 END;
379
380
381 Procedure Push_EDW_GEOG_POSTCODE_LSTG(p_from_date IN date, p_to_date IN DATE) IS
382 l_date1 DATE;
383 l_date2 DATE;
384 l_rows_inserted NUMBER :=0;
385 BEGIN
386 edw_log.put_line('Starting Push_EDW_GEOG_POSTCODE_LSTG');
387
388 -------- To get PK info from the warehouse (bug #1757640) ------
389
390 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.POA_EDW_TEMP_GEOG';
391 EXECUTE IMMEDIATE g_stmt;
392
393 /* Insert from remote warehouse level table */
394 g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
395 'SELECT POSTCODE_PK FROM EDW_GEOG_POSTCODE_LTC@' ||
396 g_target_link;
397 EXECUTE IMMEDIATE g_stmt;
398
399 -----------------------------------------------------------------
400
401 l_date1 := p_from_date;
402 l_date2 := p_to_date;
403 Insert Into
404 EDW_GEOG_POSTCODE_LSTG(
405 STATE_REGION_FK,
406 NAME,
407 INSTANCE,
408 USER_ATTRIBUTE1,
409 USER_ATTRIBUTE2,
410 USER_ATTRIBUTE3,
411 USER_ATTRIBUTE4,
412 USER_ATTRIBUTE5,
413 LAST_UPDATE_DATE,
414 CREATION_DATE,
415 POSTCODE_DP,
416 POSTCODE_PK,
417 OPERATION_CODE,
418 COLLECTION_STATUS)
419 select
420 NVL(STATE_REGION_FK, 'NA_EDW'),
421 NAME,
422 INSTANCE,
423 USER_ATTRIBUTE1,
424 USER_ATTRIBUTE2,
425 USER_ATTRIBUTE3,
426 USER_ATTRIBUTE4,
427 USER_ATTRIBUTE5,
428 LAST_UPDATE_DATE,
429 CREATION_DATE,
430 POSTCODE_DP,
431 POSTCODE_PK,
432 NULL, -- OPERATION_CODE
433 'READY'
434 from
435 (select
436 STATE_REGION_FK,
437 NAME,
438 INSTANCE,
439 USER_ATTRIBUTE1,
440 USER_ATTRIBUTE2,
441 USER_ATTRIBUTE3,
442 USER_ATTRIBUTE4,
443 USER_ATTRIBUTE5,
444 max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE ,
445 max(CREATION_DATE) as CREATION_DATE,
446 POSTCODE_DP,
447 POSTCODE_PK
448 from EDW_GEOG_POSTCODE_LCV
449 where last_update_date between l_date1 and l_date2
450 GROUP BY
451 postcode_pk, state_region_fk, postcode_dp, name, instance,
452 user_attribute1, user_attribute2, user_attribute3,
453 user_attribute4, user_attribute5)
454 where
455 NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where POSTCODE_PK = TEMP_PK);
456
457 l_rows_inserted := sql%rowcount;
458 EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
459
460 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
461 ' rows into the staging table');
462
463 edw_log.put_line('Completed Push_EDW_GEOG_POSTCODE_LSTG');
464
465 Exception When others then
466 rollback;
467 raise;
468
469 END;
470
471
472 Procedure Push_GEOG_STATE_REGION_LSTG(p_from_date IN date, p_to_date IN DATE) IS
473 l_date1 DATE;
474 l_date2 DATE;
475 l_rows_inserted NUMBER :=0;
476 l_tmp_str VARCHAR2 (120);
477 BEGIN
478 edw_log.put_line('Starting Push_EDW_GEOG_STATE_REGION_LSTG');
479
480 -------- To get PK info from the warehouse (bug #1757640) ------
481
482 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.POA_EDW_TEMP_GEOG';
483 EXECUTE IMMEDIATE g_stmt;
484
485 /* Insert from remote warehouse level table */
486 g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
487 'SELECT STATE_REGION_PK FROM EDW_GEOG_STATE_REGION_LTC@' ||
488 g_target_link;
489 EXECUTE IMMEDIATE g_stmt;
490
491 -----------------------------------------------------------------
492
493 l_date1 := p_from_date;
494 l_date2 := p_to_date;
495
496 l_tmp_str := EDW_COLLECTION_UTIL.get_lookup_value ('EDW_LEVEL_PUSH_DOWN',
497 'EDW_GEOGRAPHY_M_SREG');
498 if(l_tmp_str is NULL) THEN
499 edw_log.put_line('***Warning*** : No Look Code Found From GET_LOOKUP_VALUE in Pushing State_Region');
500 end if;
501
502 Insert Into
503 EDW_GEOG_STATE_REGION_LSTG(
504 STATE_FK,
505 STATE_REGION_DP,
506 NAME,
507 INSTANCE,
508 USER_ATTRIBUTE1,
509 USER_ATTRIBUTE2,
510 USER_ATTRIBUTE3,
511 USER_ATTRIBUTE4,
512 USER_ATTRIBUTE5,
513 LAST_UPDATE_DATE,
514 CREATION_DATE,
515 STATE_REGION_PK,
516 OPERATION_CODE,
517 COLLECTION_STATUS)
518 select
519 NVL(STATE_FK, 'NA_EDW'),
520 l_tmp_str || ' (' || STATE_REGION_DP || ')',
521 l_tmp_str || ' (' || NAME || ')', --NAME
522 INSTANCE,
523 USER_ATTRIBUTE1,
524 USER_ATTRIBUTE2,
525 USER_ATTRIBUTE3,
526 USER_ATTRIBUTE4,
527 USER_ATTRIBUTE5,
528 LAST_UPDATE_DATE,
529 CREATION_DATE,
530 STATE_REGION_PK,
531 NULL, -- OPERATION_CODE
532 'READY'
533 from
534 (select
535 STATE_FK,
536 STATE_REGION_DP,
537 NAME,
538 INSTANCE,
539 USER_ATTRIBUTE1,
540 USER_ATTRIBUTE2,
541 USER_ATTRIBUTE3,
542 USER_ATTRIBUTE4,
543 USER_ATTRIBUTE5,
544 max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
545 max(CREATION_DATE) as CREATION_DATE,
546 STATE_REGION_PK
547 from EDW_GEOG_STATE_REGION_LCV
548 where last_update_date between l_date1 and l_date2
549 GROUP BY
550 state_region_pk, state_fk, state_region_dp, name, instance,
551 user_attribute1, user_attribute2, user_attribute3,
552 user_attribute4, user_attribute5)
553 where
554 NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where STATE_REGION_PK = TEMP_PK);
555
556 l_rows_inserted := sql%rowcount;
557 EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
558
559 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
560 ' rows into the staging table');
561
562 edw_log.put_line('Completed Push_EDW_GEOG_STATE_REGION_LSTG');
563
564 Exception When others then
565 rollback;
566 raise;
567
568 END;
569
570
571 Procedure Push_EDW_GEOG_STATE_LSTG(p_from_date IN date, p_to_date IN DATE) IS
572 l_date1 DATE;
573 l_date2 DATE;
574 l_rows_inserted NUMBER :=0;
575 BEGIN
576 edw_log.put_line('Starting Push_EDW_GEOG_STATE_LSTG');
577
578 -------- To get PK info from the warehouse (bug #1757640) ------
579
580 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.POA_EDW_TEMP_GEOG';
581 EXECUTE IMMEDIATE g_stmt;
582
583 /* Insert from remote warehouse level table */
584 g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
585 'SELECT STATE_PK FROM EDW_GEOG_STATE_LTC@' ||
586 g_target_link;
587 EXECUTE IMMEDIATE g_stmt;
588
589 -----------------------------------------------------------------
590
591 l_date1 := p_from_date;
592 l_date2 := p_to_date;
593
594 Insert Into
595 EDW_GEOG_STATE_LSTG(
596 STATE_PK,
597 REGION_FK,
598 STATE_DP,
599 NAME,
600 INSTANCE,
601 USER_ATTRIBUTE1,
602 USER_ATTRIBUTE2,
603 USER_ATTRIBUTE3,
604 USER_ATTRIBUTE4,
605 USER_ATTRIBUTE5,
606 LAST_UPDATE_DATE,
607 CREATION_DATE,
608 OPERATION_CODE,
609 COLLECTION_STATUS)
610 select
611 STATE_PK,
612 NVL(REGION_FK, 'NA_EDW'),
613 STATE_DP,
614 NAME,
615 INSTANCE,
616 USER_ATTRIBUTE1,
617 USER_ATTRIBUTE2,
618 USER_ATTRIBUTE3,
619 USER_ATTRIBUTE4,
620 USER_ATTRIBUTE5,
621 LAST_UPDATE_DATE,
622 CREATION_DATE,
623 NULL, -- OPERATION_CODE
624 'READY'
625 from
626 (select
627 STATE_PK,
628 REGION_FK,
629 STATE_DP,
630 NAME,
631 INSTANCE,
632 USER_ATTRIBUTE1,
633 USER_ATTRIBUTE2,
634 USER_ATTRIBUTE3,
635 USER_ATTRIBUTE4,
636 USER_ATTRIBUTE5,
637 max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
638 max(CREATION_DATE) as CREATION_DATE
639 from EDW_GEOG_STATE_LCV
640 where last_update_date between l_date1 and l_date2
641 GROUP BY
642 state_pk, region_fk, state_dp, name, instance,
643 user_attribute1, user_attribute2, user_attribute3,
644 user_attribute4, user_attribute5)
645 where
646 NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where STATE_PK = TEMP_PK);
647
648 l_rows_inserted := sql%rowcount;
649 EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
650
651 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
652 ' rows into the staging table');
653
654 edw_log.put_line('Completed Push_EDW_GEOG_STATE_LSTG');
655
656 Exception When others then
657 rollback;
658 raise;
659
660 END;
661
662
663 Procedure Push_EDW_GEOG_REGION_LSTG(p_from_date IN date, p_to_date IN DATE) IS
664 l_date1 DATE;
665 l_date2 DATE;
666 l_rows_inserted NUMBER :=0;
667 l_tmp_str VARCHAR2 (120);
668 BEGIN
669 edw_log.put_line('Starting Push_EDW_GEOG_REGION_LSTG');
670
671 -------- To get PK info from the warehouse (bug #1757640) ------
672
673 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.POA_EDW_TEMP_GEOG';
674 EXECUTE IMMEDIATE g_stmt;
675
676 /* Insert from remote warehouse level table */
677 g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
678 'SELECT REGION_PK FROM EDW_GEOG_REGION_LTC@' ||
679 g_target_link;
680 EXECUTE IMMEDIATE g_stmt;
681
682 -----------------------------------------------------------------
683
684 l_date1 := p_from_date;
685 l_date2 := p_to_date;
686
687 l_tmp_str := EDW_COLLECTION_UTIL.get_lookup_value ('EDW_LEVEL_PUSH_DOWN',
688 'EDW_GEOGRAPHY_M_REGN');
689 if(l_tmp_str is NULL) THEN
690 edw_log.put_line('***Warning*** : No Look Code Found From GET_LOOKUP_VALUE in Pushing REGION');
691 end if;
692
693 Insert Into
694 EDW_GEOG_REGION_LSTG(
695 REGION_PK,
696 COUNTRY_FK,
697 REGION_DP,
698 NAME,
699 INSTANCE,
700 USER_ATTRIBUTE1,
701 USER_ATTRIBUTE2,
702 USER_ATTRIBUTE3,
703 USER_ATTRIBUTE4,
704 USER_ATTRIBUTE5,
705 LAST_UPDATE_DATE,
706 CREATION_DATE,
707 OPERATION_CODE,
708 COLLECTION_STATUS)
709 select
710 REGION_PK,
711 NVL(COUNTRY_FK, 'NA_EDW'),
712 l_tmp_str || ' (' || REGION_DP || ')',
713 l_tmp_str || ' (' || NAME || ')', --NAME
714 INSTANCE,
715 USER_ATTRIBUTE1,
716 USER_ATTRIBUTE2,
717 USER_ATTRIBUTE3,
718 USER_ATTRIBUTE4,
719 USER_ATTRIBUTE5,
720 LAST_UPDATE_DATE,
721 CREATION_DATE,
722 NULL, -- OPERATION_CODE
723 'READY'
724 from
725 (select
726 REGION_PK,
727 COUNTRY_FK,
728 REGION_DP,
729 NAME,
730 INSTANCE,
731 USER_ATTRIBUTE1,
732 USER_ATTRIBUTE2,
733 USER_ATTRIBUTE3,
734 USER_ATTRIBUTE4,
735 USER_ATTRIBUTE5,
736 max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
737 max(CREATION_DATE) as CREATION_DATE
738 from EDW_GEOG_REGION_LCV
739 where last_update_date between l_date1 and l_date2
740 GROUP BY
741 region_pk, country_fk, region_dp, name, instance,
742 user_attribute1, user_attribute2, user_attribute3,
743 user_attribute4, user_attribute5)
744 where
745 NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where REGION_PK = TEMP_PK);
746
747 l_rows_inserted := sql%rowcount;
748 g_row_count := g_row_count + l_rows_inserted ;
749
750 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
751 ' rows into the staging table');
752
753 edw_log.put_line('Completed Push_EDW_GEOG_REGION_LSTG');
754
755 Exception When others then
756 rollback;
757 raise;
758
759 END;
760
761
762 Procedure Push_EDW_GEOG_COUNTRY_LSTG(p_from_date IN date, p_to_date IN DATE) IS
763 l_date1 DATE;
764 l_date2 DATE;
765 l_rows_inserted NUMBER :=0;
766 BEGIN
767 edw_log.put_line('Starting Push_EDW_GEOG_COUNTRY_LSTG');
768
769 -------- To get PK info from the warehouse (bug #1757640) ------
770
771 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.POA_EDW_TEMP_GEOG';
772 EXECUTE IMMEDIATE g_stmt;
773
774 /* Insert from remote warehouse level table */
775 g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
776 'SELECT COUNTRY_PK FROM EDW_GEOG_COUNTRY_LTC@' ||
777 g_target_link;
778 EXECUTE IMMEDIATE g_stmt;
779
780 -----------------------------------------------------------------
781
782 l_date1 := p_from_date;
783 l_date2 := p_to_date;
784 Insert Into
785 EDW_GEOG_COUNTRY_LSTG(
786 COUNTRY_PK,
787 AREA2_FK,
788 COUNTRY_DP,
789 NAME,
790 INSTANCE,
791 USER_ATTRIBUTE1,
792 USER_ATTRIBUTE2,
793 USER_ATTRIBUTE3,
794 USER_ATTRIBUTE4,
795 USER_ATTRIBUTE5,
796 LAST_UPDATE_DATE,
797 CREATION_DATE,
798 OPERATION_CODE,
799 COLLECTION_STATUS)
800 select
801 COUNTRY_PK,
802 NVL(AREA2_FK, 'NA_EDW'),
803 COUNTRY_DP,
804 NAME,
805 INSTANCE,
806 USER_ATTRIBUTE1,
807 USER_ATTRIBUTE2,
808 USER_ATTRIBUTE3,
809 USER_ATTRIBUTE4,
810 USER_ATTRIBUTE5,
811 LAST_UPDATE_DATE,
812 CREATION_DATE,
813 NULL, -- OPERATION_CODE
814 'READY'
815 from
816 (select
817 COUNTRY_PK,
818 AREA2_FK,
819 COUNTRY_DP,
820 NAME,
821 INSTANCE,
822 USER_ATTRIBUTE1,
823 USER_ATTRIBUTE2,
824 USER_ATTRIBUTE3,
825 USER_ATTRIBUTE4,
826 USER_ATTRIBUTE5,
827 max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
828 max(CREATION_DATE) as CREATION_DATE
829 from EDW_GEOG_COUNTRY_LCV
830 where last_update_date between l_date1 and l_date2
831 GROUP BY
832 country_pk, area2_fk, country_dp, name, instance,
833 user_attribute1, user_attribute2, user_attribute3,
834 user_attribute4, user_attribute5)
835 where
836 NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where COUNTRY_PK = TEMP_PK);
837
838 l_rows_inserted := sql%rowcount;
839 EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
840
841 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
842 ' rows into the staging table');
843
844 edw_log.put_line('Completed Push_EDW_GEOG_COUNTRY_LSTG');
845
846 Exception When others then
847 rollback;
848 raise;
849
850 END;
851
852
853 Procedure Push_EDW_GEOG_AREA2_LSTG(p_from_date IN date, p_to_date IN DATE) IS
854 l_date1 DATE;
855 l_date2 DATE;
856 l_rows_inserted NUMBER :=0;
857 l_tmp_str1 VARCHAR2 (120);
858 l_tmp_str2 VARCHAR2 (120);
859 BEGIN
860 edw_log.put_line('Starting Push_EDW_GEOG_AREA2_LSTG');
861
862 -------- To get PK info from the warehouse (bug #1757640) ------
863
864 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.POA_EDW_TEMP_GEOG';
865 EXECUTE IMMEDIATE g_stmt;
866
867 /* Insert from remote warehouse level table */
868 g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
869 'SELECT AREA2_PK FROM EDW_GEOG_AREA2_LTC@' ||
870 g_target_link;
871 EXECUTE IMMEDIATE g_stmt;
872
873 -----------------------------------------------------------------
874
875 l_date1 := p_from_date;
876 l_date2 := p_to_date;
877
878 l_tmp_str1 := EDW_COLLECTION_UTIL.get_lookup_value ('EDW_LEVEL_PUSH_DOWN',
879 'EDW_GEOGRAPHY_M_ARE2');
880 l_tmp_str2 := EDW_COLLECTION_UTIL.get_lookup_value ('EDW_LEVEL_PUSH_DOWN', 'EDW_ALL');
881 if(l_tmp_str1 is NULL or l_tmp_str2 is NULL) THEN
882 edw_log.put_line('***Warning*** : No Look Code Found From GET_LOOKUP_VALUE in Pushing AREA2');
883 end if;
884
885 Insert Into
886 EDW_GEOG_AREA2_LSTG(
887 AREA2_PK,
888 AREA1_FK,
889 AREA2_DP,
890 NAME,
891 INSTANCE,
892 USER_ATTRIBUTE1,
893 USER_ATTRIBUTE2,
894 USER_ATTRIBUTE3,
895 USER_ATTRIBUTE4,
896 USER_ATTRIBUTE5,
897 LAST_UPDATE_DATE,
898 CREATION_DATE,
899 OPERATION_CODE,
900 COLLECTION_STATUS)
901 select
902 AREA2_PK,
903 NVL(AREA1_FK, 'NA_EDW'),
904 l_tmp_str1 || ' (' || l_tmp_str2 || ')', --AREA2_DP
905 l_tmp_str1 || ' (' || l_tmp_str2 || ')', --NAME
906 INSTANCE,
907 USER_ATTRIBUTE1,
908 USER_ATTRIBUTE2,
909 USER_ATTRIBUTE3,
910 USER_ATTRIBUTE4,
911 USER_ATTRIBUTE5,
912 LAST_UPDATE_DATE,
913 CREATION_DATE,
914 NULL, -- OPERATION_CODE
915 'READY'
916 from EDW_GEOG_AREA2_LCV
917 where (last_update_date between l_date1 and l_date2
918 OR last_update_date is NULL)
919 AND NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where AREA2_PK = TEMP_PK);
920
921 l_rows_inserted := sql%rowcount;
922 EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
923
924 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
925 ' rows into the staging table');
926
927 edw_log.put_line('Completed Push_EDW_GEOG_AREA2_LSTG');
928
929 Exception When others then
930 rollback;
931 raise;
932
933 END;
934
935
936 Procedure Push_EDW_GEOG_AREA1_LSTG(p_from_date IN date, p_to_date IN DATE) IS
937 l_date1 DATE;
938 l_date2 DATE;
939 l_rows_inserted NUMBER :=0;
940 l_tmp_str1 VARCHAR2 (120);
941 l_tmp_str2 VARCHAR2 (120);
942 BEGIN
943 edw_log.put_line('Starting Push_EDW_GEOG_AREA1_LSTG');
944
945 -------- To get PK info from the warehouse (bug #1757640) ------
946
947 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.POA_EDW_TEMP_GEOG';
948 EXECUTE IMMEDIATE g_stmt;
949
950 /* Insert from remote warehouse level table */
951 g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
952 'SELECT AREA1_PK FROM EDW_GEOG_AREA1_LTC@' ||
953 g_target_link;
954 EXECUTE IMMEDIATE g_stmt;
955
956 -----------------------------------------------------------------
957
958 l_date1 := p_from_date;
959 l_date2 := p_to_date;
960
961 l_tmp_str1 := EDW_COLLECTION_UTIL.get_lookup_value ('EDW_LEVEL_PUSH_DOWN',
962 'EDW_GEOGRAPHY_M_ARE1');
963 l_tmp_str2 := EDW_COLLECTION_UTIL.get_lookup_value ('EDW_LEVEL_PUSH_DOWN', 'EDW_ALL');
964 if(l_tmp_str1 is NULL or l_tmp_str2 is NULL) THEN
965 edw_log.put_line('***Warning*** : No Look Code Found From GET_LOOKUP_VALUE in Pushing AREA1');
966 end if;
967
968 Insert Into
969 EDW_GEOG_AREA1_LSTG(
970 AREA1_PK,
971 ALL_FK,
972 AREA1_DP,
973 NAME,
974 INSTANCE,
975 USER_ATTRIBUTE1,
976 USER_ATTRIBUTE2,
977 USER_ATTRIBUTE3,
978 USER_ATTRIBUTE4,
979 USER_ATTRIBUTE5,
980 LAST_UPDATE_DATE,
981 CREATION_DATE,
982 OPERATION_CODE,
983 COLLECTION_STATUS)
984 select AREA1_PK,
985 NVL(ALL_FK, 'NA_EDW'),
986 l_tmp_str1 || ' (' || l_tmp_str2 || ')', --AREA1_DP
987 l_tmp_str1 || ' (' || l_tmp_str2 || ')', --NAME
988 INSTANCE,
989 USER_ATTRIBUTE1,
990 USER_ATTRIBUTE2,
991 USER_ATTRIBUTE3,
992 USER_ATTRIBUTE4,
993 USER_ATTRIBUTE5,
994 LAST_UPDATE_DATE,
995 CREATION_DATE,
996 NULL, -- OPERATION_CODE
997 'READY'
998 from EDW_GEOG_AREA1_LCV
999 where (last_update_date between l_date1 and l_date2
1000 OR last_update_date is NULL)
1001 AND NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where AREA1_PK = TEMP_PK);
1002
1003 l_rows_inserted := sql%rowcount;
1004 EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
1005
1006 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
1007 ' rows into the staging table');
1008
1009 edw_log.put_line('Completed Push_EDW_GEOG_AREA1_LSTG');
1010
1011 Exception When others then
1012 rollback;
1013 raise;
1014
1015 END;
1016 End EDW_GEOGRAPHY_M_C;