APPS.PQP_PTY_BUS SQL Statements

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

Line: 385

  select pension_type_name,
         business_group_id,
         legislation_code,
         effective_start_date,
         effective_start_date,
         effective_end_date
    from pqp_pension_types_f
   where upper(pension_type_name) = upper(c_pension_type_name)
   and
   (( business_group_id IS NOT NULL
      AND business_group_id = c_business_group_id
    )
    OR ( legislation_code IS NOT NULL AND legislation_code = c_legislation_code)
    OR (business_group_id IS NULL AND legislation_code IS NULL)
    )
    and c_effective_date between effective_start_date and effective_end_date;

Line: 473

  select 'x'
    from hr_organization_information
   where org_information_context = 'PQP_NL_PENSION_TYPES'
     and org_information1 = to_char(c_pension_type_id);

Line: 481

  select 'x'
    from hr_organization_information
   where org_information_context = 'PQP_GB_PENSION_TYPES_INFO'
     and org_information1 = to_char(c_pension_type_id);

Line: 489

  select 'x'
    from hr_organization_information
   where org_information_context = 'HU_PENSION_TYPES_INFO'
     and org_information1 = to_char(c_pension_type_id);

Line: 991

   Select 1 from pay_defined_balances
      where balance_dimension_id =
      (select balance_dimension_id from pay_balance_dimensions
         where database_item_suffix = '_ASG_RUN'
         and (( business_group_id IS NOT NULL
                AND business_group_id = c_business_group_id
              )
              OR (legislation_code IS NOT NULL AND legislation_code = c_legislation_code)
              OR (business_group_id IS NULL AND legislation_code IS NULL)
             )
      )
      and balance_type_id = c_balance_type_id
      and (( business_group_id IS NOT NULL
             AND business_group_id = c_business_group_id
           )
           OR ( legislation_code IS NOT NULL AND legislation_code = c_legislation_code)
           OR (business_group_id IS NULL AND legislation_code IS NULL)
          );

Line: 1365

  select nvl(end_date_active,null)
  from   fnd_lookup_values
  where  lookup_type = 'PQP_PENSION_SUB_CATEGORY'
  and    lookup_code = p_pension_sub_category
  and    p_effective_date between start_date_active
  and    nvl(end_date_active,hr_api.g_eot)
  and    nvl(enabled_flag,'N') = 'Y';

Line: 1410

    select pbg.security_group_id,
           pbg.legislation_code
      from per_business_groups_perf pbg
         , pqp_pension_types_f pty
     where pty.pension_type_id = p_pension_type_id
       and pbg.business_group_id = pty.business_group_id;

Line: 1481

    select pbg.legislation_code
      from per_business_groups_perf pbg
         , pqp_pension_types_f pty
     where pty.pension_type_id = p_pension_type_id
       and pbg.business_group_id (+) = pty.business_group_id;

Line: 1843

Procedure chk_non_updateable_args
  (p_effective_date  in date
  ,p_rec             in pqp_pty_shd.g_rec_type
  ) IS
--
  l_proc     varchar2(72) := g_package || 'chk_non_updateable_args';

Line: 1869

End chk_non_updateable_args;

Line: 1900

Procedure dt_update_validate
  (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: 1946

End dt_update_validate;

Line: 1983

Procedure dt_delete_validate
  (p_pension_type_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: 2005

  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: 2044

End dt_delete_validate;

Line: 2056

  (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
  -- EDIT_HERE: The following call should be edited if certain types of rows
  -- are not permitted.
  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: 2100

   SELECT legislation_code
    FROM  per_business_groups_perf
    WHERE business_group_id =p_business_group_id;

Line: 2122

Procedure insert_validate
  (p_rec                   in pqp_pty_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 out nocopy date
  ) is
--
  l_proc        varchar2(72) := g_package||'insert_validate';

Line: 2408

End insert_validate;

Line: 2415

Procedure update_validate
  (p_rec                     in pqp_pty_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 out nocopy date
  ) is
--
  l_proc        varchar2(72) := g_package||'update_validate';

Line: 2458

  dt_update_validate
    (p_datetrack_mode                 => p_datetrack_mode
    ,p_validation_start_date          => p_validation_start_date
    ,p_validation_end_date            => p_validation_end_date
    );

Line: 2464

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

Line: 2663

End update_validate;

Line: 2670

Procedure delete_validate
  (p_rec                    in pqp_pty_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: 2691

     SELECT 1
       FROM pqp_pension_types_f
     WHERE  pension_type_id = c_pension_type_id
       AND  special_pension_type_code = 'ABP';

Line: 2698

     SELECT 1
       FROM pqp_pension_types_f
     WHERE  pension_type_id = c_pension_type_id
       AND  special_pension_type_code = 'PGGM';

Line: 2705

     SELECT 1
       FROM hr_organization_information
     WHERE  org_information_context = 'PQP_NL_ABP_PT'
       AND  org_information3  =  to_char(c_pension_type_id);

Line: 2712

     SELECT 1
       FROM hr_organization_information
     WHERE  org_information_context = 'PQP_NL_PGGM_PT'
       AND  org_information3  =  to_char(c_pension_type_id);

Line: 2719

     SELECT 1
       FROM per_assignment_extra_info
     WHERE  aei_information_category = 'NL_ABP_PI'
       AND  information_type         = 'NL_ABP_PI'
       AND  aei_information3         = to_char(c_pension_type_id);

Line: 2727

      SELECT fnd_date.canonical_to_date(nvl(org_information2,'4712/12/31')) org_info_end_dt
        FROM hr_organization_information
      WHERE  org_information3 = to_char(c_pension_type_id)
        AND  org_information_context = 'PQP_NL_ABP_PT';

Line: 2734

      SELECT fnd_date.canonical_to_date(nvl(org_information2,'4712/12/31')) org_info_end_dt
        FROM hr_organization_information
      WHERE  org_information3 = to_char(c_pension_type_id)
        AND  org_information_context = 'PQP_NL_PGGM_PT';

Line: 2741

      SELECT fnd_date.canonical_to_date(nvl(aei_information2,'4712/12/31')) asg_info_end_dt
        FROM per_assignment_extra_info
      WHERE  aei_information3 = to_char(c_pension_type_id)
        AND  aei_information_category  = 'NL_ABP_PI'
        AND  information_type          = 'NL_ABP_PI';

Line: 2752

    SELECT 1
      FROM pay_element_type_extra_info
     WHERE eei_information_category = 'PQP_NL_PGGM_DEDUCTION'
       AND eei_information2         = fnd_number.number_to_canonical(c_pension_type_id);

Line: 2781

  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_pension_type_id         => p_rec.pension_type_id
    );

Line: 2821

       hr_utility.set_location('Delete Mode ZAP'||l_proc,20);

Line: 2850

    Elsif p_datetrack_mode = 'DELETE' Then
       --find the maximum end date in the ORG/ASG EIT for this PT
       hr_utility.set_location('Datetrack mode DELETE'||l_proc,50);

Line: 2886

     End If; -- end of date track mode DELETE/ZAP validations

Line: 2909

       hr_utility.set_location('Delete Mode ZAP'||l_proc,20);

Line: 2948

End delete_validate;