APPS.HRI_OPL_DBI_CALENDAR SQL Statements

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

Line: 19

/* PL/SQL table for bulk inserting into the workforce fact calendar */
g_wrkfc_type                g_varchar_tab_type;

Line: 29

/* PL/SQL table for bulk inserting into the workforce change fact calendar */
g_wcnt_chg_type             g_varchar_tab_type;

Line: 68

/*   in the pl/sql table ready for bulk insert. These are repeated for each   */
/*   time period:                                                             */
/*     Current period snapshot                                                */
/*     Previous year snapshot                                                 */
/*     Previous period snapshots (up to maximum trend periods)                */
/*                                                                            */
/*   For the trend periods, collecting the period start dates is equivalent   */
/*   to increasing the number of previous period end dates collected by 1.    */
/*                                                                            */
/******************************************************************************/


/******************************************************************************/
/* Defines properties of each period:                                         */
/*    - period length in days                                                 */
/*    - period name (time period type)                                        */
/*    - maximum number of trend points for the period                         */
/******************************************************************************/
PROCEDURE set_metadata IS

BEGIN

  g_mtdt_prds_tab(1).days := 365;

Line: 136

/* Bulk insert snapshots for the workforce fact                               */
/******************************************************************************/
PROCEDURE bulk_insert_wrkfc IS

BEGIN

  FORALL i IN 1..g_wrkfc_row_count
    INSERT INTO hri_cal_snpsht_wrkfc
      (period_type,
       comparison_type,
       effective_date,
       curr_start_date,
       curr_end_date,
       comp_start_date,
       comp_end_date,
       snapshot_date)
      VALUES
       (g_wrkfc_type(i),
        g_wrkfc_comp_type(i),
        g_wrkfc_end_date(i),
        g_wrkfc_start_date(i),
        g_wrkfc_end_date(i),
        g_wrkfc_comp_start_date(i),
        g_wrkfc_comp_end_date(i),
        g_wrkfc_snapshot_date(i));

Line: 162

END bulk_insert_wrkfc;

Line: 165

/* Bulk insert snapshots for the workforce changes fact                       */
/******************************************************************************/
PROCEDURE bulk_insert_wcnt_chg IS

BEGIN

  FORALL i IN 1..g_wcnt_chg_row_count
    INSERT INTO hri_cal_snpsht_wcnt_chg
      (period_type,
       comparison_type,
       effective_start_date,
       effective_end_date,
       snapshot_date)
      VALUES
       (g_wcnt_chg_type(i),
        g_wcnt_chg_comp_type(i),
        g_wcnt_chg_start_date(i),
        g_wcnt_chg_end_date(i),
        g_wcnt_chg_snapshot_date(i));

Line: 185

END bulk_insert_wcnt_chg;

Line: 188

/* Insert a single period into the workforce fact calendar                    */
/******************************************************************************/
PROCEDURE insert_wrkfc_period(p_curr_start_date  IN DATE,
                              p_curr_end_date    IN DATE,
                              p_comp_start_date  IN DATE,
                              p_comp_end_date    IN DATE,
                              p_period_type      IN VARCHAR2,
                              p_comparison_type  IN VARCHAR2,
                              p_snapshot_date    IN DATE) IS

BEGIN

  g_wrkfc_row_count := g_wrkfc_row_count + 1;

Line: 209

END insert_wrkfc_period;

Line: 212

/* Insert a single period into the workforce change fact calendar             */
/******************************************************************************/
PROCEDURE insert_wcnt_chg_period(p_start_date       IN DATE,
                                 p_end_date         IN DATE,
                                 p_period_type      IN VARCHAR2,
                                 p_comparison_type  IN VARCHAR2,
                                 p_snapshot_date    IN DATE) IS

BEGIN

  g_wcnt_chg_row_count := g_wcnt_chg_row_count + 1;

Line: 229

END insert_wcnt_chg_period;

Line: 257

    insert_wcnt_chg_period
     (p_start_date => p_effective_date - l_days_in_year
                      - g_mtdt_prds_tab(i).days + 1,
      p_end_date => p_effective_date - l_days_in_year,
      p_period_type => g_mtdt_prds_tab(i).type,
      p_comparison_type => 'YEARLY',
      p_snapshot_date => p_effective_date);

Line: 266

    insert_wrkfc_period
     (p_curr_start_date => p_effective_date - g_mtdt_prds_tab(i).days + 1,
      p_curr_end_date => p_effective_date,
      p_comp_start_date => p_effective_date - l_days_in_year
                      - g_mtdt_prds_tab(i).days + 1,
      p_comp_end_date => p_effective_date - l_days_in_year,
      p_period_type => g_mtdt_prds_tab(i).type,
      p_comparison_type => 'YEARLY',
      p_snapshot_date => p_effective_date);

Line: 289

        insert_wcnt_chg_period
         (p_start_date => p_effective_date - (j * g_mtdt_prds_tab(i).days)
                          - g_mtdt_prds_tab(i).days + 1,
          p_end_date => p_effective_date - (j * g_mtdt_prds_tab(i).days),
          p_period_type => g_mtdt_prds_tab(i).type,
          p_comparison_type => l_comparison_type,
          p_snapshot_date => p_effective_date);

Line: 301

          insert_wrkfc_period
           (p_curr_start_date => p_effective_date - ((j - 1) * g_mtdt_prds_tab(i).days)
                                 - g_mtdt_prds_tab(i).days + 1,
            p_curr_end_date => p_effective_date - ((j - 1) * g_mtdt_prds_tab(i).days),
            p_comp_start_date => p_effective_date - (j * g_mtdt_prds_tab(i).days)
                                 - g_mtdt_prds_tab(i).days + 1,
            p_comp_end_date => p_effective_date - (j * g_mtdt_prds_tab(i).days),
            p_period_type => g_mtdt_prds_tab(i).type,
            p_comparison_type => l_comparison_type,
            p_snapshot_date => p_effective_date);

Line: 338

/* Deletes rows from calendar tables */
PROCEDURE truncate_calendar_tables IS

  l_dummy1        VARCHAR2(2000);

Line: 371

  /* Initialize globals storing numbers of rows inserted */
    g_wcnt_chg_row_count := 0;

Line: 379

  /* the periods to snapshot into the PL/SQL table ready for bulk insert */
    load_snapshots;

Line: 383

    bulk_insert_wrkfc;

Line: 384

    bulk_insert_wcnt_chg;

Line: 403

/* Incrementally update calendar tables */
PROCEDURE update_calendars IS

BEGIN

  null;

Line: 410

END update_calendars;

Line: 412

/* Incrementally update calendar tables - main entry point */
PROCEDURE update_calendars(errbuf   OUT NOCOPY VARCHAR2,
                           retcode  OUT NOCOPY VARCHAR2) IS

BEGIN

  update_calendars;

Line: 420

END update_calendars;