APPS.BEN_PD_FORMULA_PKG SQL Statements

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

Line: 10

DELETE FROM ff_formulas_f
WHERE formula_name = p_formula_name;

Line: 34

l_lastUpdateDate date;

Line: 36

CURSOR ffRow IS SELECT rowId FROM ff_formulas_f
               WHERE  formula_id = p_formula_id
               and    p_effective_start_date between effective_start_date and effective_end_date;

Line: 44

     select ff_formulas_s.nextval
     into l_formula_id
     from sys.dual;

Line: 48

     ff_formulas_f_pkg.insert_row(
      x_rowid                 => l_rowid,
      x_formula_id            => l_formula_id,
      x_effective_start_date  => p_effective_start_date,
      x_effective_end_date    => p_effective_end_date,
      x_business_group_id     => p_business_group_id,
      x_legislation_code      => p_legislation_code,
      x_formula_type_id       => p_formula_type_id,
      x_formula_name          => l_formula_name,
      x_description           => p_description,
      x_formula_text          => p_formula_text,
      x_sticky_flag           => 'Y',
      x_last_update_date      => l_lastUpdateDate
      );

Line: 68

   ff_formulas_f_pkg.update_row(
      x_rowid                 => l_rowid,
      x_formula_id            => l_formula_id,
      x_effective_start_date  => p_effective_start_date,
      x_effective_end_date    => p_effective_end_date,
      x_business_group_id     => p_business_group_id,
      x_legislation_code      => p_legislation_code,
      x_formula_type_id       => p_formula_type_id,
      x_formula_name          => p_formula_name,
      x_description           => p_description,
      x_formula_text          => p_formula_text,
      x_sticky_flag           => 'Y',
      x_last_update_date      => l_lastUpdateDate
      );

Line: 90

select information4 BUSINESS_GROUP_ID,
       information11 legislation_code,
       information161  FORMULA_TYPE_ID,
       information111 FORMULA_NAME,
       information151 DESCRIPTION,
       information325 FORMULA_TEXT,
       information1 ff_formula_id
from ben_copy_entity_results
where copy_entity_result_id = p_copy_entity_result_id;

Line: 101

l_lastUpdateDate date;

Line: 110

             select ff_formulas_s.nextval
             into l_formula_id
             from sys.dual;

Line: 114

    ff_formulas_f_pkg.insert_row(
      x_rowid                 => l_rowid,
      x_formula_id            => l_formula_id,
      x_effective_start_date  => sysdate,
      x_effective_end_date    => hr_general.end_of_time,
      x_business_group_id     => r_fff.business_group_id,
      x_legislation_code      => r_fff.legislation_code,
      x_formula_type_id       => r_fff.formula_type_id,
      x_formula_name          => r_fff.formula_name,
      x_description           => r_fff.description,
      x_formula_text          => r_fff.formula_text,
      x_sticky_flag           => 'Y',
      x_last_update_date      => l_lastUpdateDate
      );

Line: 128

   else -- Formula Already created, now update it instead
   l_formula_id :=  r_fff.ff_formula_id;

Line: 130

    ff_formulas_f_pkg.update_row(
      x_rowid                 => l_rowid,
      x_formula_id            => l_formula_id,
      x_effective_start_date  => sysdate,
      x_effective_end_date    => hr_general.end_of_time,
      x_business_group_id     => r_fff.business_group_id,
      x_legislation_code      => r_fff.legislation_code,
      x_formula_type_id       => r_fff.formula_type_id,
      x_formula_name          => r_fff.formula_name,
      x_description           => r_fff.description,
      x_formula_text          => r_fff.formula_text,
      x_sticky_flag           => 'Y',
      x_last_update_date      => l_lastUpdateDate
      );

Line: 245

  delete from ben_copy_entity_results
  where copy_entity_result_id = -999999;

Line: 248

  insert into ben_copy_entity_results (
         COPY_ENTITY_RESULT_ID,
         COPY_ENTITY_TXN_ID,
         RESULT_TYPE_CD,
         OBJECT_VERSION_NUMBER,
         INFORMATION325)
  select -999999, -999999, 'COPY_FF',1, to_lob(formula_text)
  from  ff_formulas_f
  where formula_id = p_formula_id
  and   p_effective_start_date between effective_start_date and effective_end_date;

Line: 259

  select information325 into l_clob from ben_copy_entity_results
  where copy_entity_result_id = -999999;

Line: 286

    SELECT effective_start_date, effective_end_date
      FROM ff_formulas_f
     WHERE formula_id = p_formula_id
       AND business_group_id = p_business_group_id
       AND TRUNC(p_effective_date) BETWEEN effective_start_date AND effective_end_date;

Line: 298

    l_update               BOOLEAN;

Line: 299

    l_update_override      BOOLEAN;

Line: 300

    l_update_change_insert BOOLEAN;

Line: 308

      SELECT ff_formulas_s.nextval
        INTO l_formula_id
        FROM sys.dual;

Line: 325

  insert into ns_temp values ('Formula Id: '||p_formula_id);

Line: 326

    insert into ns_temp values ('p_effective_start_date Id: '||p_effective_start_date);

Line: 327

  insert into ns_temp values ('p_effective_end_date Id: '||p_effective_end_date);

Line: 328

  insert into ns_temp values ('p_business_group_id Id: '||p_business_group_id);

Line: 329

  insert into ns_temp values ('p_legislation_code Id: '||p_legislation_code);

Line: 330

  insert into ns_temp values ('p_formula_type_id Id: '||p_formula_type_id);

Line: 331

  insert into ns_temp values ('p_formula_name Id: '||p_formula_name);

Line: 332

  insert into ns_temp values ('p_dml_operation Id: '||p_dml_operation);

Line: 333

  insert into ns_temp values ('p_datetrack_mode Id: '||p_datetrack_mode);

Line: 336

    BEN_PD_COPY_TO_BEN_ONE.create_or_update_ff
                          (p_formula_id           => l_formula_id
                          ,p_effective_start_date => p_effective_start_date
                          ,p_effective_end_date   => p_effective_end_date
                          ,p_business_group_id    => p_business_group_id
                          ,p_legislation_code     => p_legislation_code
                          ,p_formula_type_id      => p_formula_type_id
                          ,p_formula_name         => p_formula_name
                          ,p_description          => p_description
                          ,p_formula_text         => p_formula_text
                          ,p_sticky_flag          => p_sticky_flag
                          ,p_compile_flag         => p_compile_flag
                          ,p_last_update_date     => SYSDATE
                          ,p_last_updated_by      => -1
                          ,p_last_update_login    => -1
                          ,p_created_by           => -1
                          ,p_creation_date        => SYSDATE
                          ,p_process_date         => TRUNC(p_effective_date)
                          ,p_dml_operation        => p_dml_operation
                          ,p_datetrack_mode       => p_datetrack_mode);

Line: 369

    Delete formula if new formula and mode is verify

  if (p_formula_id = null --AND p_mode = 'VERIFY'
   ) then
  declare
  l_rowid  varchar2(100);

Line: 376

     select rowid into l_rowid
     from ff_formulas_f
     where formula_id = p_formula_id
     and  p_effective_date between effective_start_date and effective_end_date;

Line: 381

     ff_formulas_f_pkg.delete_row(
      x_rowid                 => l_rowid,
      x_formula_id            => p_formula_id,
      x_dt_delete_mode        => 'DELETE',
      x_validation_start_date => p_effective_date,
      x_validation_end_date   => null,
      x_effective_date        => p_effective_date
      );

Line: 414

    SELECT copy_entity_result_id
          ,dml_operation
          ,datetrack_mode
      FROM ben_copy_entity_results
     WHERE copy_entity_txn_id = p_copy_entity_txn_id
       AND information1       = p_formula_id
       AND ( information4       = p_business_group_id OR
             ( p_business_group_id is null AND information4 is null))
       AND p_effective_date BETWEEN information2 AND information3
       AND table_alias        = 'FFF'
       ORDER By copy_entity_result_id desc;

Line: 449

    update ben_copy_entity_results
       set number_of_copies = 0,
           status           = 'INVALID'
     where copy_entity_result_id = l_copy_entity_result_id;

Line: 464

   SELECT 'Y'
   FROM FF_COMPILED_INFO_F
   WHERE formula_id = p_formula_id
   AND   p_effective_date between effective_start_date and effective_end_date;