APPS.PAY_IVL_BUS SQL Statements

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

Line: 17

select count(*) from pay_input_values_f
where input_value_id = pay_ivl_shd.g_old_rec.input_value_id;

Line: 33

    select pbg.security_group_id
      from per_business_groups pbg
         , pay_input_values_f ivl
     where ivl.input_value_id = p_input_value_id
       and pbg.business_group_id = ivl.business_group_id;

Line: 99

    select pbg.legislation_code

      from per_business_groups pbg
         , pay_input_values_f ivl
     where ivl.input_value_id = p_input_value_id
       and pbg.business_group_id (+) = ivl.business_group_id;

Line: 191

Procedure chk_non_updateable_args
  (p_effective_date  in date
  ,p_rec             in pay_ivl_shd.g_rec_type
  ) IS
--

  l_proc     varchar2(72) := g_package || 'chk_non_updateable_args';

Line: 215

End chk_non_updateable_args;

Line: 248

Procedure dt_update_validate
  (p_element_type_id               in number default hr_api.g_number
  ,p_datetrack_mode                in varchar2
  ,p_validation_start_date         in date
  ,p_validation_end_date           in date
  ) Is
--
  l_proc  varchar2(72) := g_package||'dt_update_validate';

Line: 308

End dt_update_validate;

Line: 347

Procedure dt_delete_validate
  (p_input_value_id                   in number
  ,p_datetrack_mode                   in varchar2
  ,p_validation_start_date            in date
  ,p_validation_end_date              in date
  ) Is
--
  l_proc        varchar2(72)    := g_package||'dt_delete_validate';

Line: 370

  If (p_datetrack_mode = hr_api.g_delete or
      p_datetrack_mode = hr_api.g_zap) then
    --
    --
    -- Ensure the arguments are not null
    --
    hr_api.mandatory_arg_error
      (p_api_name       => l_proc
      ,p_argument       => 'validation_start_date'
      ,p_argument_value => p_validation_start_date
      );

Line: 432

End dt_delete_validate;

Line: 445

  (p_insert               IN boolean
  ,p_business_group_id    IN number
  ,p_legislation_code     IN varchar2
  ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
--
BEGIN
  --
  -- Call the supporting procedure to check startup mode
  IF (p_insert) THEN
    hr_startup_data_api_support.chk_startup_action

      (p_generic_allowed   => TRUE
      ,p_startup_allowed   => TRUE
      ,p_user_allowed      => TRUE
      ,p_business_group_id => p_business_group_id
      ,p_legislation_code  => p_legislation_code
      ,p_legislation_subgroup => p_legislation_subgroup
      );

Line: 493

select 'X' from hr_lookups
where upper(lookup_type) = nvl(upper(p_lookup_type),lookup_type)
and enabled_flag = 'Y'
and p_effective_date between
nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);

Line: 544

select 'X'
from ff_formulas_f ff, ff_formula_types ft
where nvl(ff.legislation_code,nvl(p_legislation_code,'~~nvl~~'))
      = nvl(p_legislation_code,'~~nvl~~')
and nvl(ff.business_group_id, nvl(p_business_group_id,-1))=nvl(p_business_group_id,-1)
and p_effective_date between ff.effective_start_date and ff.effective_end_date
and ff.formula_type_id = ft.formula_type_id
and upper (ft.formula_type_name) = 'ELEMENT INPUT VALIDATION'
and ff.formula_id = p_formula_id;

Line: 601

    select formula_id
      from pay_input_values_f
     where input_value_id = p_input_value_id
       and p_effective_date between effective_start_date
       and effective_end_date;

Line: 746

  select validation_type
  from fnd_flex_value_sets
  where flex_value_set_id = p_value_set_id;

Line: 877

select contributions_used from
ben_benefit_classifications
where benefit_classification_id in ( select distinct benefit_classification_id
                          from pay_element_types_f
                          where element_type_id = p_element_type_id
                          and p_effective_date between effective_start_date
                          and effective_end_date);

Line: 983

select lookup_code from
hr_lookups
where upper(substr(lookup_code,1,2))
    = upper(substr(pay_ivl_shd.g_old_rec.uom,1,2))
and lookup_type = 'UNITS'
and enabled_flag = 'Y'
and p_effective_date between
nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);

Line: 1050

        select  1
        from    per_pay_bases
        where   input_value_id = p_input_value_id;

Line: 1055

        select  1
        from    pay_element_entry_values_f
        where   input_value_id           = p_input_value_id
        and     p_effective_date between effective_start_date
        and     effective_end_date;

Line: 1178

select non_payments_flag
from pay_element_classifications
where classification_id in ( select distinct classification_id
                            from pay_element_types_f
                            where element_type_id = p_element_type_id);

Line: 1251

select input_currency_code
from pay_element_types_f
where element_type_id = p_element_type_id;

Line: 1256

select 'X' from hr_lookups
where upper(lookup_code) = nvl(upper(p_uom),lookup_code)
and lookup_type = 'UNITS'
and enabled_flag = 'Y'
and p_effective_date between
nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);

Line: 1319

  select 'X' from hr_lookups
  where upper(lookup_code) = nvl(upper(p_default_value),lookup_code)
  and lookup_type = p_lookup_type
  and enabled_flag = 'Y'
  and p_effective_date between
  nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);

Line: 1424

  select 'X'
  from hr_lookups
  where upper(lookup_code) = nvl(upper(p_default_value),lookup_code)
  and lookup_type = decode(p_lookup_type,
                      hr_api.g_varchar2, pay_ivl_shd.g_old_rec.lookup_type,
                      p_lookup_type)
  and enabled_flag = 'Y'
  and p_effective_date between nvl(start_date_active,hr_api.g_sot)
                       and nvl(end_date_active,hr_api.g_eot);

Line: 1598

  select 'X' from hr_lookups
  where upper(lookup_code) = nvl(upper(p_warning_or_error),lookup_code)
  and lookup_type = 'WARNING_ERROR'
  and enabled_flag = 'Y'
  and p_effective_date between
  nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);

Line: 1651

PROCEDURE chk_other_insert_val
  (p_element_type_id   in number
  ,p_formula_id        in number
  ,p_lookup_type       in varchar2
  ,p_value_set_id      in number
  ,p_min_value         in varchar2
  ,p_max_value         in varchar2
  ,p_start_date        in date
  ,p_end_date          in date
  ,p_pay_basis_warning out nocopy boolean
  )
IS
  cursor csr_run_results is
  select 1
  from   dual
  where  exists
       (select /*+ INDEX(PAYROLL PAY_PAYROLL_ACTIONS_PK)
                   INDEX(ASSIGN  PAY_ASSIGNMENT_ACTIONS_PK) */ 1
        from    pay_run_results RUN,
                pay_payroll_actions PAYROLL,
                pay_assignment_actions ASSIGN
        where   run.element_type_id = p_element_type_id
        and     assign.assignment_action_id = run.assignment_action_id
        and     assign.payroll_action_id = payroll.payroll_action_id
        and     payroll.effective_date between p_start_date
                                           and     p_end_date);

Line: 1679

        select  1 pay
        from    per_pay_bases
        where   input_value_id in ( select input_value_id
                                    from pay_input_values_f
                                    where element_type_id = p_element_type_id);

Line: 1686

  l_proc        varchar2(72)    := g_package||'chk_other_insert_val';

Line: 1749

END chk_other_insert_val;

Line: 1895

PROCEDURE chk_delete_allowed
(p_datetrack_mode in varchar2
,p_input_value_id in number
,p_element_type_id in number
,p_effective_date in date
,p_start_date in date
,p_end_date in date)
IS
cursor csr_classification is

select contributions_used
from ben_benefit_classifications
where benefit_classification_id in ( select distinct benefit_classification_id
                            from pay_element_types_f
                            where element_type_id = p_element_type_id
                            and p_effective_date between effective_start_date
                            and effective_end_date);

Line: 1914

select 'X'
from pay_input_values_f
where input_value_id = p_input_value_id
and upper(name) in ('COVERAGE','EE CONTR','ER CONTR');

Line: 1922

l_proc        varchar2(72)    := g_package||'chk_delete_allowed';

Line: 1953

END chk_delete_allowed;

Line: 1959

Procedure insert_validate
  (p_rec                   in pay_ivl_shd.g_rec_type
  ,p_effective_date        in date
  ,p_datetrack_mode        in varchar2
  ,p_validation_start_date in date
  ,p_validation_end_date   in date
  ,p_default_val_warning   out nocopy boolean
  ,p_min_max_warning       out nocopy boolean
  ,p_pay_basis_warning     out nocopy boolean
  ,p_formula_warning       out nocopy boolean
  ,p_assignment_id_warning out nocopy boolean
  ,p_formula_message       out nocopy varchar2
  ) is
--
  l_proc        varchar2(72) := g_package||'insert_validate';

Line: 2002

   pay_ivl_bus.chk_other_insert_val
       (p_element_type_id => p_rec.element_type_id
       ,p_formula_id => p_rec.formula_id
       ,p_lookup_type => p_rec.lookup_type
       ,p_value_set_id => p_rec.value_set_id
       ,p_min_value =>  p_rec.min_value
       ,p_max_value =>  p_rec.max_value
       ,p_start_date => p_validation_start_date
       ,p_end_date   => p_validation_end_date
       ,p_pay_basis_warning => p_pay_basis_warning
       );

Line: 2092

End insert_validate;

Line: 2097

Procedure update_validate
  (p_rec                     in pay_ivl_shd.g_rec_type
  ,p_effective_date          in date
  ,p_datetrack_mode          in out nocopy varchar2
  ,p_validation_start_date   in date
  ,p_validation_end_date     in date
  ,p_default_val_warning     out nocopy boolean
  ,p_min_max_warning         out nocopy boolean
  ,p_link_inp_val_warning    out nocopy boolean
  ,p_pay_basis_warning       out nocopy boolean
  ,p_formula_warning         out nocopy boolean
  ,p_assignment_id_warning   out nocopy boolean
  ,p_formula_message         out nocopy varchar2
  ) is
--
  l_proc        varchar2(72) := g_package||'update_validate';

Line: 2258

  dt_update_validate
    (p_element_type_id                => p_rec.element_type_id
    ,p_datetrack_mode                 => p_datetrack_mode
    ,p_validation_start_date          => p_validation_start_date
    ,p_validation_end_date            => p_validation_end_date
    );

Line: 2265

  chk_non_updateable_args
    (p_effective_date  => p_effective_date
    ,p_rec             => p_rec
    );

Line: 2272

End update_validate;

Line: 2277

Procedure delete_validate
  (p_rec                    in pay_ivl_shd.g_rec_type
  ,p_effective_date         in date
  ,p_datetrack_mode         in varchar2
  ,p_validation_start_date  in date
  ,p_validation_end_date    in date

  ) is
--
  l_proc        varchar2(72) := g_package||'delete_validate';

Line: 2310

   pay_ivl_bus.chk_delete_allowed
         (p_datetrack_mode => p_datetrack_mode
         ,p_input_value_id => p_rec.input_value_id
         ,p_element_type_id => p_rec.element_type_id
         ,p_effective_date => p_effective_date
         ,p_start_date => p_validation_start_date
         ,p_end_date => p_validation_end_date);

Line: 2322

  dt_delete_validate
    (p_datetrack_mode                   => p_datetrack_mode
    ,p_validation_start_date            => p_validation_start_date
    ,p_validation_end_date              => p_validation_end_date
    ,p_input_value_id                   => p_rec.input_value_id
    );

Line: 2330

End delete_validate;