APPS.HRI_OPL_GEO_LOCHR SQL Statements

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

Line: 148

  SELECT 'x'
  FROM   hri_cs_geo_lochr_ct
  WHERE  rownum < 2;

Line: 223

  dbg_time('Insert Start');

Line: 224

  INSERT
  INTO   hri_cs_geo_lochr_ct
         (area_code
         ,country_code
         ,region_code
         ,city_cid
         ,location_id
         ,geo_area_id
         ,geo_country_id
         ,geo_region_id
         ,city_src_town_or_city_txt
         ,effective_start_date
         ,effective_end_date
         ,business_group_id
         ,last_change_date
         )
  SELECT area_code,
         country_code,
         region_code,
         city_cid,
         location_id,
         geo_area_id,
         geo_country_id,
         geo_region_id,
         city_src_town_or_city_txt,
         effective_start_date,
         effective_end_date,
         business_group_id,
         NVL(last_change_date, g_dbi_collection_start_date)
  FROM   hri_cs_geo_lochr_v svw
  --
  -- 4303724, Used TRUNC function
  --
  WHERE  TRUNC(last_change_date) BETWEEN g_start_date
                                 AND     g_end_date
  AND    NOT EXISTS (SELECT 'x'
                     FROM   hri_cs_geo_lochr_ct tbl
                     WHERE  svw.location_id        = tbl.location_id
                     AND    effective_start_date   = tbl.effective_start_date
                     AND    effective_end_date     = tbl.effective_end_date)
  ;

Line: 265

  dbg_time('Insert End');

Line: 267

  dbg('Insert >'||TO_CHAR(sql%rowcount));

Line: 269

  dbg_time('Update Start');

Line: 286

  UPDATE hri_cs_geo_lochr_ct tbl
    SET
    (       area_code
           ,country_code
           ,region_code
           ,city_cid
           ,location_id
           ,geo_area_id
           ,geo_country_id
           ,geo_region_id
           ,city_src_town_or_city_txt
           ,effective_start_date
           ,effective_end_date
           ,business_group_id
           ,last_change_date
    ) =
    (SELECT svw.area_code,
            svw.country_code,
            svw.region_code,
            svw.city_cid,
            svw.location_id,
            svw.geo_area_id,
            svw.geo_country_id,
            svw.geo_region_id,
            svw.city_src_town_or_city_txt,
            svw.effective_start_date,
            svw.effective_end_date,
            svw.business_group_id,
            NVL(svw.last_change_date, g_dbi_collection_start_date)
       FROM hri_cs_geo_lochr_v svw
      WHERE svw.location_id            = tbl.location_id
        AND (   svw.area_code         <> tbl.area_code
             OR svw.country_code      <> tbl.country_code
             OR svw.region_code       <> tbl.region_code
             OR svw.city_cid          <> tbl.city_cid
            )
    )
  WHERE EXISTS
        (SELECT 'X'
           FROM hri_cs_geo_lochr_v     svw
          WHERE svw.location_id            = tbl.location_id
            AND svw.effective_start_date   = tbl.effective_start_date
            AND svw.effective_end_date     = tbl.effective_end_date
            AND (   svw.area_code         <> tbl.area_code
                 OR svw.country_code      <> tbl.country_code
                 OR svw.region_code       <> tbl.region_code
                 OR svw.city_cid          <> tbl.city_cid
                )
        )
  RETURNING tbl.location_id BULK COLLECT INTO l_upd_location_ids
  ;

Line: 337

  dbg_time('Update End');

Line: 339

  dbg('Update >'||TO_CHAR(sql%rowcount));

Line: 345

  dbg_time('Delete Start');

Line: 346

  DELETE
    FROM hri_cs_geo_lochr_ct tbl
   WHERE NOT EXISTS
         (SELECT 'x'
            FROM hri_cs_geo_lochr_v svw
           WHERE svw.location_id          = tbl.location_id
         )
  ;

Line: 354

  dbg_time('Delete End');

Line: 356

  dbg('Delete >'||TO_CHAR(sql%rowcount));

Line: 381

  dbg_time('Update Event Queue Start');

Line: 388

          INSERT INTO HRI_EQ_ASG_SUP_WRFC
           (SOURCE_TYPE,
            SOURCE_ID)
        VALUES
           ('LOCATION',
            l_upd_location_ids(i));

Line: 405

  dbg_time('Update Event Queue End');

Line: 436

  dbg_time('Update Start');

Line: 448

  UPDATE hri_cs_geo_region_ct tbl
    SET
   (       geo_area_code
         , geo_country_code
         , geo_region_code
         , geo_region_sk_pk
         , start_date
         , end_date
         , last_change_date
   ) =
   (SELECT csr.geo_area_code
         , csr.geo_country_code
         , csr.geo_region_code
         , csr.geo_region_sk_pk
         , csr.start_date
         , csr.end_date
         , csr.last_change_date
      FROM hri_dbi_cs_geo_region_v     csr
     WHERE csr.geo_region_sk_pk       = tbl.geo_region_sk_pk
       AND (   csr.geo_area_code     <> tbl.geo_area_code
            OR csr.geo_country_code  <> tbl.geo_country_code
            OR csr.start_date        <> tbl.start_date
            OR csr.end_date          <> tbl.end_date
           )
   )
   WHERE EXISTS
         (SELECT 'X'
            FROM hri_dbi_cs_geo_region_v csr
           WHERE csr.geo_region_sk_pk       = tbl.geo_region_sk_pk
             AND (   csr.geo_area_code     <> tbl.geo_area_code
                  OR csr.geo_country_code  <> tbl.geo_country_code
                  OR csr.start_date        <> tbl.start_date
                  OR csr.end_date          <> tbl.end_date
                 )
         )
  ;

Line: 484

  dbg_time('Update End');

Line: 486

  dbg('Update >'||TO_CHAR(sql%rowcount));

Line: 493

  dbg_time('Delete Start');

Line: 494

  DELETE
    FROM hri_cs_geo_region_ct tbl
   WHERE NOT EXISTS
         (SELECT 'X'
            FROM hri_dbi_cs_geo_region_v csr
           WHERE csr.geo_region_sk_pk       = tbl.geo_region_sk_pk
         )
  ;

Line: 502

  dbg_time('Delete End');

Line: 504

  dbg('Delete >'||TO_CHAR(sql%rowcount));

Line: 511

  dbg_time('Insert Start');

Line: 512

  INSERT
      INTO hri_cs_geo_region_ct
         ( geo_area_code
         , geo_country_code
         , geo_region_code
         , geo_region_sk_pk
         , start_date
         , end_date
         , last_change_date
         )
    SELECT csr.geo_area_code
         , csr.geo_country_code
         , csr.geo_region_code
         , csr.geo_region_sk_pk
         , csr.start_date
         , csr.end_date
         , csr.last_change_date
      FROM hri_dbi_cs_geo_region_v csr
     WHERE NOT EXISTS
           (SELECT 'x'
              FROM hri_cs_geo_region_ct tbl
             WHERE csr.geo_region_sk_pk       = tbl.geo_region_sk_pk
           )
  ;

Line: 536

  dbg_time('Insert End');

Line: 538

  dbg('Insert >'||TO_CHAR(sql%rowcount));

Line: 551

    Output('Failure in region structure incremental update process.');

Line: 573

  dbg_time('Update Start');

Line: 584

  UPDATE hri_cs_geo_country_ct tbl
     SET
   (       geo_area_code
         , geo_country_code
         , start_date
         , end_date
         , last_change_date
   ) =
   (SELECT csc.geo_area_code
         , csc.geo_country_code
         , csc.start_date
         , csc.end_date
         , csc.last_change_date
      FROM hri_dbi_cs_geo_country_v csc
     WHERE csc.geo_country_code       = tbl.geo_country_code
       AND (   csc.geo_area_code     <> tbl.geo_area_code
            OR csc.start_date        <> tbl.start_date
            OR csc.end_date          <> tbl.end_date
           )
   )
   WHERE EXISTS
         (SELECT 'X'
            FROM hri_dbi_cs_geo_country_v csc
           WHERE csc.geo_country_code       = tbl.geo_country_code
             AND (   csc.geo_area_code     <> tbl.geo_area_code
                  OR csc.start_date        <> tbl.start_date
                  OR csc.end_date          <> tbl.end_date
                 )
         )
  ;

Line: 614

  dbg_time('Update End');

Line: 616

  dbg('Update >'||TO_CHAR(sql%rowcount));

Line: 626

  dbg_time('Delete Start');

Line: 627

  DELETE
    FROM hri_cs_geo_country_ct tbl
   WHERE NOT EXISTS
         (SELECT 'x'
            FROM hri_dbi_cs_geo_country_v csc
           WHERE csc.geo_country_code       = tbl.geo_country_code
         )
  ;

Line: 635

  dbg_time('Delete End');

Line: 637

  dbg('Delete >'||TO_CHAR(sql%rowcount));

Line: 647

  dbg_time('Insert Start');

Line: 648

  INSERT
      INTO hri_cs_geo_country_ct
         ( geo_area_code
         , geo_country_code
         , start_date
         , end_date
         , last_change_date
         )
    SELECT csc.geo_area_code
         , csc.geo_country_code
         , csc.start_date
         , csc.end_date
         , csc.last_change_date
      FROM hri_dbi_cs_geo_country_v csc
     WHERE NOT EXISTS
           (SELECT 'x'
              FROM hri_cs_geo_country_ct tbl
             WHERE csc.geo_country_code       = tbl.geo_country_code
           )
  ;

Line: 668

  dbg_time('Insert End');

Line: 670

  dbg('Insert >'||TO_CHAR(sql%rowcount));

Line: 683

    Output('Failure in country structure incremental update process.');

Line: 724

  dbg_time('Insert Start');

Line: 725

  INSERT /*+ APPEND */
  INTO   hri_cs_geo_lochr_ct
         (area_code
         ,country_code
         ,region_code
         ,city_cid
         ,location_id
         ,geo_area_id
         ,geo_country_id
         ,geo_region_id
         ,city_src_town_or_city_txt
         ,effective_start_date
         ,effective_end_date
         ,business_group_id
         ,last_change_date
         ,last_update_date
         ,last_update_login
         ,last_updated_by
         ,created_by
         ,creation_date
         )
  SELECT area_code,
         country_code,
         region_code,
         city_cid,
         location_id,
         geo_area_id,
         geo_country_id,
         geo_region_id,
         city_src_town_or_city_txt,
         effective_start_date,
         effective_end_date,
         business_group_id,
         NVL(last_change_date, g_dbi_collection_start_date),
         g_current_time,
         g_user_id,
         g_user_id,
         g_user_id,
         g_current_time
    FROM hri_cs_geo_lochr_v svw
  ;

Line: 766

  dbg_time('Insert End');

Line: 816

  dbg_time('Insert Start');

Line: 817

  INSERT /*+APPEND */
    INTO hri_cs_geo_region_ct
         ( geo_area_code
         , geo_country_code
         , geo_region_code
         , geo_region_sk_pk
         , start_date
         , end_date
         , LAST_CHANGE_DATE
         , CREATION_DATE
         , CREATED_BY
         , LAST_UPDATE_DATE
         , LAST_UPDATED_BY
         , LAST_UPDATE_LOGIN
         )
    SELECT csr.geo_area_code                      geo_area_code
         , csr.geo_country_code                   geo_country_code
         , csr.geo_region_code                    geo_region_code
         , csr.geo_region_sk_pk                   geo_region_sk_pk
         , csr.start_date                         start_date
         , csr.end_date                           end_date
         , csr.last_change_date                   last_change_date
         , g_current_time                         CREATION_DATE
         , g_user_id                              CREATED_BY
         , g_current_time                         LAST_UPDADTE_DATE
         , g_user_id                              LAST_UPDATE_BY
         , g_user_id                              LAST_UPDATE_LOGIN
      FROM hri_dbi_cs_geo_region_v                csr
  ;

Line: 846

  dbg_time('Insert End');

Line: 896

  dbg_time('Insert Start');

Line: 897

  INSERT /*+APPEND */
    INTO hri_cs_geo_country_ct
         ( geo_area_code
         , geo_country_code
         , start_date
         , end_date
         , LAST_CHANGE_DATE
         , CREATION_DATE
         , CREATED_BY
         , LAST_UPDATE_DATE
         , LAST_UPDATED_BY
         , LAST_UPDATE_LOGIN
         )
    SELECT csc.geo_area_code                        geo_area_code
         , csc.geo_country_code                     geo_country_code
         , csc.start_date                           start_date
         , csc.end_date                             end_date
         , csc.last_change_date                     last_change_date
         , g_current_time                           CREATION_DATE
         , g_user_id                                CREATED_BY
         , g_current_time                           LAST_UPDATE_DATE
         , g_user_id                                LAST_UPDATED_BY
         , g_user_id                                LAST_UPDATE_LOGIN
      FROM hri_dbi_cs_geo_country_v                 csc
  ;

Line: 922

  dbg_time('Insert End');