DBA Data[Home] [Help]

APPS.EDW_GEOGRAPHY_M_C SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 24

 l_rows_inserted            Number:=0;
Line: 110

    l_rows_inserted NUMBER :=0;
Line: 115

   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');
Line: 177

   l_rows_inserted := sql%rowcount;
Line: 178

   EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
Line: 180

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
         ' rows into the staging table');
Line: 185

   EDW_GEOGRAPHY_M_C.g_row_count_m := l_rows_inserted;
Line: 197

    l_rows_inserted NUMBER :=0;
Line: 207

   /* 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;
Line: 218

   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);
Line: 275

   l_rows_inserted := sql%rowcount;
Line: 276

   EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
Line: 278

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
         ' rows into the staging table');
Line: 293

    l_rows_inserted NUMBER :=0;
Line: 302

   /* 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;
Line: 312

   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);
Line: 366

   l_rows_inserted := sql%rowcount;
Line: 367

   g_row_count     := g_row_count + l_rows_inserted ;
Line: 369

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
         ' rows into the staging table');
Line: 384

    l_rows_inserted NUMBER :=0;
Line: 393

   /* 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;
Line: 403

   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);
Line: 457

   l_rows_inserted := sql%rowcount;
Line: 458

   EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
Line: 460

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
         ' rows into the staging table');
Line: 475

    l_rows_inserted NUMBER :=0;
Line: 485

   /* 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;
Line: 502

   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);
Line: 556

   l_rows_inserted := sql%rowcount;
Line: 557

   EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
Line: 559

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
         ' rows into the staging table');
Line: 574

    l_rows_inserted NUMBER :=0;
Line: 583

   /* 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;
Line: 594

   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);
Line: 648

   l_rows_inserted := sql%rowcount;
Line: 649

   EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
Line: 651

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
         ' rows into the staging table');
Line: 666

    l_rows_inserted NUMBER :=0;
Line: 676

   /* 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;
Line: 693

   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);
Line: 747

   l_rows_inserted := sql%rowcount;
Line: 748

   g_row_count := g_row_count + l_rows_inserted ;
Line: 750

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
                    ' rows into the staging table');
Line: 765

    l_rows_inserted NUMBER :=0;
Line: 774

   /* 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;
Line: 784

   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);
Line: 838

   l_rows_inserted := sql%rowcount;
Line: 839

   EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
Line: 841

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
         ' rows into the staging table');
Line: 856

    l_rows_inserted NUMBER :=0;
Line: 867

   /* 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;
Line: 885

  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);
Line: 921

   l_rows_inserted := sql%rowcount;
Line: 922

   EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
Line: 924

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
         ' rows into the staging table');
Line: 939

    l_rows_inserted NUMBER :=0;
Line: 950

   /* 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;
Line: 968

   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);
Line: 1003

   l_rows_inserted := sql%rowcount;
Line: 1004

   EDW_GEOGRAPHY_M_C.g_row_count := EDW_GEOGRAPHY_M_C.g_row_count + l_rows_inserted ;
Line: 1006

   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
         ' rows into the staging table');