The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_rows_inserted Number:=0;
l_rows_inserted NUMBER :=0;
Insert Into
EDW_GEOG_LOCATION_LSTG(
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
ADDRESS_LINE_4,
POSTCODE_CITY_FK,
CREATION_DATE,
INSTANCE,
LAST_UPDATE_DATE,
LOCATION_DP,
LOCATION_PK,
NAME,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS)
(select ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
ADDRESS_LINE_4,
NVL(POSTCODE_CITY_FK, 'NA_EDW'),
CREATION_DATE,
INSTANCE,
LAST_UPDATE_DATE,
LOCATION_DP,
LOCATION_PK,
NAME,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY'
from EDW_GEOG_LOCATION_LCV
where last_update_date between l_date1 and l_date2
union
select CITY_FK,
POSTCODE_FK,
NULL,
NULL,
POSTCODE_CITY_PK,
CREATION_DATE,
INSTANCE,
LAST_UPDATE_DATE,
POSTCODE_CITY_DP,
POSTCODE_CITY_PK,
NAME,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL,
'READY'
from EDW_GEOG_POSTCODE_CITY_LSTG
where collection_status='READY');
l_rows_inserted := sql%rowcount;
EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');
EDW_GEOGRAPHY_M_C.g_row_count_m := l_rows_inserted;
l_rows_inserted NUMBER :=0;
/* Insert from remote warehouse level table */
g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
'SELECT POSTCODE_CITY_PK FROM EDW_GEOG_POSTCODE_CITY_LTC@' ||
g_target_link;
Insert Into
EDW_GEOG_POSTCODE_CITY_LSTG(
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
POSTCODE_CITY_PK,
CITY_FK,
POSTCODE_FK,
POSTCODE_CITY_DP,
NAME,
OPERATION_CODE,
COLLECTION_STATUS)
select
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
POSTCODE_CITY_PK,
NVL(CITY_FK, 'NA_EDW'),
NVL(POSTCODE_FK, 'NA_EDW'),
POSTCODE_CITY_DP,
NAME,
NULL, -- OPERATION_CODE
'READY'
from
(select
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE ,
max(CREATION_DATE) as CREATION_DATE ,
POSTCODE_CITY_PK,
CITY_FK,
POSTCODE_FK,
POSTCODE_CITY_DP,
NAME
from EDW_GEOG_POSTCODE_CITY_LCV
where last_update_date between l_date1 and l_date2
group by
postcode_city_pk, city_fk, postcode_fk, postcode_city_dp,
name, instance, user_attribute1, user_attribute2,
user_attribute3, user_attribute4, user_attribute5)
where
NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where POSTCODE_CITY_PK = TEMP_PK);
l_rows_inserted := sql%rowcount;
EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');
l_rows_inserted NUMBER :=0;
/* Insert from remote warehouse level table */
g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
'SELECT CITY_PK FROM EDW_GEOG_CITY_LTC@' ||
g_target_link;
Insert Into
EDW_GEOG_CITY_LSTG(
CITY_PK,
STATE_REGION_FK,
CITY_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
OPERATION_CODE,
COLLECTION_STATUS)
select
CITY_PK,
NVL(STATE_REGION_FK, 'NA_EDW'),
CITY_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
NULL, -- OPERATION_CODE
'READY'
from
(select
CITY_PK,
STATE_REGION_FK,
CITY_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
max(CREATION_DATE) as CREATION_DATE
from EDW_GEOG_CITY_LCV
where last_update_date between l_date1 and l_date2
GROUP BY
city_pk, state_region_fk, city_dp, name, instance,
user_attribute1, user_attribute2, user_attribute3,
user_attribute4, user_attribute5)
where
NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where CITY_PK = TEMP_PK);
l_rows_inserted := sql%rowcount;
g_row_count := g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');
l_rows_inserted NUMBER :=0;
/* Insert from remote warehouse level table */
g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
'SELECT POSTCODE_PK FROM EDW_GEOG_POSTCODE_LTC@' ||
g_target_link;
Insert Into
EDW_GEOG_POSTCODE_LSTG(
STATE_REGION_FK,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
POSTCODE_DP,
POSTCODE_PK,
OPERATION_CODE,
COLLECTION_STATUS)
select
NVL(STATE_REGION_FK, 'NA_EDW'),
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
POSTCODE_DP,
POSTCODE_PK,
NULL, -- OPERATION_CODE
'READY'
from
(select
STATE_REGION_FK,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE ,
max(CREATION_DATE) as CREATION_DATE,
POSTCODE_DP,
POSTCODE_PK
from EDW_GEOG_POSTCODE_LCV
where last_update_date between l_date1 and l_date2
GROUP BY
postcode_pk, state_region_fk, postcode_dp, name, instance,
user_attribute1, user_attribute2, user_attribute3,
user_attribute4, user_attribute5)
where
NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where POSTCODE_PK = TEMP_PK);
l_rows_inserted := sql%rowcount;
EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');
l_rows_inserted NUMBER :=0;
/* Insert from remote warehouse level table */
g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
'SELECT STATE_REGION_PK FROM EDW_GEOG_STATE_REGION_LTC@' ||
g_target_link;
Insert Into
EDW_GEOG_STATE_REGION_LSTG(
STATE_FK,
STATE_REGION_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
STATE_REGION_PK,
OPERATION_CODE,
COLLECTION_STATUS)
select
NVL(STATE_FK, 'NA_EDW'),
l_tmp_str || ' (' || STATE_REGION_DP || ')',
l_tmp_str || ' (' || NAME || ')', --NAME
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
STATE_REGION_PK,
NULL, -- OPERATION_CODE
'READY'
from
(select
STATE_FK,
STATE_REGION_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
max(CREATION_DATE) as CREATION_DATE,
STATE_REGION_PK
from EDW_GEOG_STATE_REGION_LCV
where last_update_date between l_date1 and l_date2
GROUP BY
state_region_pk, state_fk, state_region_dp, name, instance,
user_attribute1, user_attribute2, user_attribute3,
user_attribute4, user_attribute5)
where
NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where STATE_REGION_PK = TEMP_PK);
l_rows_inserted := sql%rowcount;
EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');
l_rows_inserted NUMBER :=0;
/* Insert from remote warehouse level table */
g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
'SELECT STATE_PK FROM EDW_GEOG_STATE_LTC@' ||
g_target_link;
Insert Into
EDW_GEOG_STATE_LSTG(
STATE_PK,
REGION_FK,
STATE_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
OPERATION_CODE,
COLLECTION_STATUS)
select
STATE_PK,
NVL(REGION_FK, 'NA_EDW'),
STATE_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
NULL, -- OPERATION_CODE
'READY'
from
(select
STATE_PK,
REGION_FK,
STATE_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
max(CREATION_DATE) as CREATION_DATE
from EDW_GEOG_STATE_LCV
where last_update_date between l_date1 and l_date2
GROUP BY
state_pk, region_fk, state_dp, name, instance,
user_attribute1, user_attribute2, user_attribute3,
user_attribute4, user_attribute5)
where
NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where STATE_PK = TEMP_PK);
l_rows_inserted := sql%rowcount;
EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');
l_rows_inserted NUMBER :=0;
/* Insert from remote warehouse level table */
g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
'SELECT REGION_PK FROM EDW_GEOG_REGION_LTC@' ||
g_target_link;
Insert Into
EDW_GEOG_REGION_LSTG(
REGION_PK,
COUNTRY_FK,
REGION_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
OPERATION_CODE,
COLLECTION_STATUS)
select
REGION_PK,
NVL(COUNTRY_FK, 'NA_EDW'),
l_tmp_str || ' (' || REGION_DP || ')',
l_tmp_str || ' (' || NAME || ')', --NAME
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
NULL, -- OPERATION_CODE
'READY'
from
(select
REGION_PK,
COUNTRY_FK,
REGION_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
max(CREATION_DATE) as CREATION_DATE
from EDW_GEOG_REGION_LCV
where last_update_date between l_date1 and l_date2
GROUP BY
region_pk, country_fk, region_dp, name, instance,
user_attribute1, user_attribute2, user_attribute3,
user_attribute4, user_attribute5)
where
NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where REGION_PK = TEMP_PK);
l_rows_inserted := sql%rowcount;
g_row_count := g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');
l_rows_inserted NUMBER :=0;
/* Insert from remote warehouse level table */
g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
'SELECT COUNTRY_PK FROM EDW_GEOG_COUNTRY_LTC@' ||
g_target_link;
Insert Into
EDW_GEOG_COUNTRY_LSTG(
COUNTRY_PK,
AREA2_FK,
COUNTRY_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
OPERATION_CODE,
COLLECTION_STATUS)
select
COUNTRY_PK,
NVL(AREA2_FK, 'NA_EDW'),
COUNTRY_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
NULL, -- OPERATION_CODE
'READY'
from
(select
COUNTRY_PK,
AREA2_FK,
COUNTRY_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
max(LAST_UPDATE_DATE) as LAST_UPDATE_DATE,
max(CREATION_DATE) as CREATION_DATE
from EDW_GEOG_COUNTRY_LCV
where last_update_date between l_date1 and l_date2
GROUP BY
country_pk, area2_fk, country_dp, name, instance,
user_attribute1, user_attribute2, user_attribute3,
user_attribute4, user_attribute5)
where
NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where COUNTRY_PK = TEMP_PK);
l_rows_inserted := sql%rowcount;
EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');
l_rows_inserted NUMBER :=0;
/* Insert from remote warehouse level table */
g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
'SELECT AREA2_PK FROM EDW_GEOG_AREA2_LTC@' ||
g_target_link;
Insert Into
EDW_GEOG_AREA2_LSTG(
AREA2_PK,
AREA1_FK,
AREA2_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
OPERATION_CODE,
COLLECTION_STATUS)
select
AREA2_PK,
NVL(AREA1_FK, 'NA_EDW'),
l_tmp_str1 || ' (' || l_tmp_str2 || ')', --AREA2_DP
l_tmp_str1 || ' (' || l_tmp_str2 || ')', --NAME
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
NULL, -- OPERATION_CODE
'READY'
from EDW_GEOG_AREA2_LCV
where (last_update_date between l_date1 and l_date2
OR last_update_date is NULL)
AND NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where AREA2_PK = TEMP_PK);
l_rows_inserted := sql%rowcount;
EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');
l_rows_inserted NUMBER :=0;
/* Insert from remote warehouse level table */
g_stmt := 'INSERT INTO POA_EDW_TEMP_GEOG ' ||
'SELECT AREA1_PK FROM EDW_GEOG_AREA1_LTC@' ||
g_target_link;
Insert Into
EDW_GEOG_AREA1_LSTG(
AREA1_PK,
ALL_FK,
AREA1_DP,
NAME,
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
OPERATION_CODE,
COLLECTION_STATUS)
select AREA1_PK,
NVL(ALL_FK, 'NA_EDW'),
l_tmp_str1 || ' (' || l_tmp_str2 || ')', --AREA1_DP
l_tmp_str1 || ' (' || l_tmp_str2 || ')', --NAME
INSTANCE,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
LAST_UPDATE_DATE,
CREATION_DATE,
NULL, -- OPERATION_CODE
'READY'
from EDW_GEOG_AREA1_LCV
where (last_update_date between l_date1 and l_date2
OR last_update_date is NULL)
AND NOT EXISTS (select 1 from POA_EDW_TEMP_GEOG where AREA1_PK = TEMP_PK);
l_rows_inserted := sql%rowcount;
EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
' rows into the staging table');