APPS.PQP_VAL_BUS SQL Statements

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

Line: 28

    select pbg.security_group_id,
           pbg.legislation_code
      from per_business_groups_perf pbg
         , pqp_vehicle_allocations_f val
     where val.vehicle_allocation_id = p_vehicle_allocation_id
       and pbg.business_group_id = val.business_group_id;

Line: 99

    select pbg.legislation_code
      from per_business_groups_perf pbg
         , pqp_vehicle_allocations_f val
     where val.vehicle_allocation_id = p_vehicle_allocation_id
       and pbg.business_group_id = val.business_group_id;

Line: 461

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

Line: 487

End chk_non_updateable_args;

Line: 518

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

End dt_update_validate;

Line: 601

Procedure dt_delete_validate
  (p_vehicle_allocation_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: 623

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

End dt_delete_validate;

Line: 723

   SELECT  COUNT(pva.vehicle_allocation_id)
    FROM   pqp_vehicle_repository_f   pvr
          ,pqp_vehicle_allocations_f  pva
     WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
      AND  pvr.business_group_id = pva.business_group_id
      AND  pvr.vehicle_ownership = p_vehicle_ownership
      AND  pva.assignment_id = p_rec.assignment_id
      AND  pva.business_group_id = p_rec.business_group_id
      AND  (p_effective_date BETWEEN
           pva.effective_start_date AND pva.effective_end_date
      OR   p_effective_date <= pva.effective_start_date)
      AND  p_effective_date between
           pvr.effective_start_date and pvr.effective_end_date;

Line: 816

   SELECT vehicle_status
     FROM PQP_VEHICLE_REPOSITORY_F
   WHERE  vehicle_repository_id = p_rec.vehicle_repository_id
     AND  p_effective_date between effective_start_date and effective_end_date
     AND  business_group_id = p_rec.business_group_id ;

Line: 847

 SELECT 'X'
   INTO l_exist
  FROM pqp_vehicle_repository_f pvr
 WHERE pvr.vehicle_repository_id=p_rec.vehicle_repository_id
   AND pvr.business_group_id=p_rec.business_group_id
   AND p_effective_date BETWEEN pvr.effective_start_date
                            AND pvr.effective_end_date;

Line: 877

SELECT 'X'
  INTO l_exist
  FROM per_all_assignments_f paa
 WHERE paa.assignment_id =p_rec.assignment_id
   AND paa.business_group_id=p_rec.business_group_id
   AND p_effective_date BETWEEN paa.effective_start_date
                            AND paa.effective_end_date;

Line: 913

    SELECT COUNT(pva.vehicle_allocation_id)
     FROM  pqp_vehicle_repository_f   pvr
          ,pqp_vehicle_allocations_f  pva
     WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
       AND pvr.business_group_id = pva.business_group_id
       AND pvr.vehicle_ownership = p_vehicle_ownership
       AND pva.usage_type = p_rec.usage_type
       AND pva.assignment_id = p_rec.assignment_id
       AND pva.business_group_id=p_rec.business_group_id
       AND (p_effective_date between
           pva.effective_start_date and pva.effective_end_date
           OR  p_effective_date <= pva.effective_start_date)
       AND (p_effective_date between
           pvr.effective_start_date and pvr.effective_end_date);

Line: 966

    SELECT count(*)
      FROM pay_element_types_f pet
          ,pay_element_type_extra_info pete
           ,pay_element_entries_f pee
           ,pay_element_entry_values_f peev2
          ,pay_input_values_f    piv2
   WHERE pete.eei_information_category='PQP_VEHICLE_MILEAGE_INFO'
     AND pet.business_group_id=p_rec.business_group_id
     AND pete.element_type_id =pet.element_type_id
     AND substr(pete.eei_information1,0,1) in ('C','P')
     AND pee.assignment_id   =cp_assignment_id
     AND peev2.element_entry_id=pee.element_entry_id
     AND piv2.element_type_id=pet.element_type_id
     AND piv2.name in ('Vehicle Reg Number')
     AND piv2.input_value_id=peev2.input_value_id
     AND peev2.screen_entry_value =cp_registration_number;

Line: 984

 SELECT pvr.registration_number, pva.assignment_id
   FROM pqp_vehicle_allocations_f pva,
        pqp_vehicle_repository_f pvr
  WHERE pva.vehicle_allocation_id= p_rec.vehicle_allocation_id
    AND pva.vehicle_repository_id =pvr.vehicle_repository_id
    AND p_effective_date BETWEEN pva.effective_start_date
                             AND pva.effective_end_date
    AND p_effective_date BETWEEN pvr.effective_start_date
                             AND pvr.effective_end_date ;

Line: 1053

 SELECT COUNT(pva.vehicle_allocation_id)
  FROM  pqp_vehicle_allocations_f pva,
        per_all_assignments_f    paa
  WHERE paa.assignment_id = pva.assignment_id
   AND  pva.assignment_id NOT IN ( SELECT assignment_id
                                FROM per_all_assignments_f
                                WHERE person_id = (SELECT DISTINCT person_id
                                FROM per_all_assignments_f
                                WHERE assignment_id=p_rec.assignment_id))
   AND pva.vehicle_repository_id = p_rec.vehicle_repository_id
   AND pva.business_group_id=p_rec.business_group_id
   AND p_effective_date BETWEEN
       paa.effective_start_date AND paa.effective_end_date
   AND p_effective_date BETWEEN
       pva.effective_start_date AND pva.effective_end_date ;

Line: 1122

 SELECT COUNT(pva.vehicle_allocation_id)
   FROM pqp_vehicle_allocations_f  pva
  WHERE pva.assignment_id = p_rec.assignment_id
    AND pva.vehicle_repository_id = p_rec.vehicle_repository_id
    AND  pva.business_group_id = p_rec.business_group_id
    AND  (p_effective_date
         BETWEEN pva.effective_start_date AND pva.effective_end_date
    OR   p_effective_date <= pva.effective_start_date);

Line: 1160

 SELECT count(*)
      FROM pay_element_types_f pet
          ,pay_element_type_extra_info pete
           ,pay_element_entries_f pee
           ,pay_element_entry_values_f peev2
          ,pay_input_values_f    piv2
   WHERE pete.EEI_INFORMATION_CATEGORY='PQP_VEHICLE_MILEAGE_INFO'
     AND pet.business_group_id=p_rec.business_group_id
     AND pete.element_type_id =pet.element_type_id
     AND substr(pete.EEI_INFORMATION1,0,1) in ('C','P')
     AND pee.assignment_id   =cp_assignment_id
     AND peev2.element_entry_id=pee.element_entry_id
     AND piv2.element_type_id=pet.element_type_id
     AND piv2.name in ('Vehicle Reg Number')
     AND piv2.input_value_id=peev2.input_value_id
     AND peev2.SCREEN_ENTRY_VALUE =cp_registration_number
     AND p_effective_date < pee.effective_end_date;

Line: 1180

   SELECT pvr.registration_number, pva.assignment_id
     FROM pqp_vehicle_allocations_f pva,
          pqp_vehicle_repository_f  pvr
    WHERE pva.vehicle_allocation_id= p_rec.vehicle_allocation_id
      AND pva.vehicle_repository_id =pvr.vehicle_repository_id
      AND p_effective_date BETWEEN pva.effective_start_date
                                 AND pva.effective_end_date
      AND p_effective_date BETWEEN pvr.effective_start_date
                             AND pvr.effective_end_date ;

Line: 1228

    SELECT COUNT(pva.vehicle_allocation_id)
     FROM  pqp_vehicle_repository_f   pvr
          ,pqp_vehicle_allocations_f  pva
     WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
       AND pvr.business_group_id = pva.business_group_id
       AND pvr.vehicle_ownership = p_vehicle_ownership
       AND pva.default_vehicle = 'Y'
       AND pva.assignment_id = p_rec.assignment_id
       AND pva.business_group_id=p_rec.business_group_id
       AND (p_effective_date between
           pva.effective_start_date and pva.effective_end_date
           OR  p_effective_date <= pva.effective_start_date)
       AND (p_effective_date between
           pvr.effective_start_date and pvr.effective_end_date);

Line: 1273

SELECT pee.element_entry_id element_entry_id
                          FROM pay_element_entries_f PEE
                               ,pay_element_links_f pel
                               ,pay_element_types_f pet
                         WHERE pee.assignment_id=p_assignment_id
                          AND pel.business_group_id=p_business_group_id
                          and p_effective_date BETWEEN  pee.effective_start_date
                                          AND pee.effective_end_date
                          and p_effective_date BETWEEN  pel.effective_start_date
                                          AND pel.effective_end_date
                          and p_effective_date BETWEEN  pet.effective_start_date
                                          AND pet.effective_end_date
                          and pee.element_link_id=pel.element_link_id
                          AND pel.element_type_id=pet.element_type_id
                          AND pet.element_name = 'NI Car Primary';

Line: 1291

SELECT pee.element_entry_id  element_entry_id
                          FROM pay_element_entries_f PEE
                               ,pay_element_links_f pel
                               ,pay_element_types_f pet
                               ,pay_input_values_f piv
                               , pay_element_entry_values_f peev
                               , pqp_vehicle_allocations_f pva
                               ,pqp_vehicle_repository_f pvr
                         WHERE pee.ASSIGNMENT_ID=p_assignment_id
                          AND pel.business_group_id=p_business_group_id
                          AND pee.element_link_id=pel.element_link_id
                          AND pel.element_type_id=pet.element_type_id
                          AND pet.element_name = 'NI Car Secondary'
                          AND piv.element_type_id =  pet.element_type_id
                          AND  piv.name = 'Registration Number'
                          AND peev.input_value_id=piv.input_value_id
                          AND  peev.element_entry_id=pee.element_entry_id
                          AND pva.vehicle_allocation_id=cp_allocation_id
                          AND pva.vehicle_repository_id=pvr.VEHICLE_REPOSITORY_ID
                          AND peev.screen_entry_value=pvr.registration_number
                           AND pel.business_group_id=piv.business_group_id
                           AND piv.business_group_id=pva.business_group_id
                           AND piv.business_group_id=pvr.business_group_id
                           AND pet.legislation_code='GB'
                          AND p_effective_date BETWEEN  pee.effective_start_date
                                          AND pee.effective_end_date
                          AND p_effective_date BETWEEN  pel.effective_start_date
                                          AND pel.effective_end_date
                          AND p_effective_date BETWEEN  pet.effective_start_date
                                          AND pet.effective_end_date
                          AND p_effective_date BETWEEN  piv.effective_start_date
                                          AND piv.effective_end_date
                           AND p_effective_date BETWEEN  peev.effective_start_date
                                          AND peev.effective_end_date
                           AND p_effective_date BETWEEN  pva.effective_start_date
                                          AND pva.effective_end_date
                           AND p_effective_date BETWEEN  pvr.effective_start_date
                                          AND pvr.effective_end_date;

Line: 1339

  hr_entry_api.delete_element_entry
  (
   p_dt_delete_mode        =>    'DELETE',
   p_session_date          =>     p_effective_date,
   p_element_entry_id      =>     l_chk_ni_car_pri.element_entry_id
  );

Line: 1352

  hr_entry_api.delete_element_entry
  (
   p_dt_delete_mode         =>   'DELETE',
   p_session_date           =>    p_effective_date,
   p_element_entry_id       =>    l_chk_sec_car.element_entry_id
  );

Line: 1370

Procedure insert_validate
  (p_rec                   in pqp_val_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

    --Used to get the ownership for repId at once
    CURSOR c_veh_det_cursor IS
        SELECT  pvr.vehicle_ownership
               ,pvr.vehicle_status
               ,pvr.shared_vehicle
               ,pvr.initial_registration
               ,pvr.registration_number
          FROM  pqp_vehicle_repository_f pvr
         WHERE  pvr.vehicle_repository_id = p_rec.vehicle_repository_id
           AND  pvr.business_group_id=  p_rec.business_group_id
           AND  p_effective_date BETWEEN
                pvr.effective_start_date AND
                pvr.effective_end_date;

Line: 1392

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

Line: 1696

End insert_validate;

Line: 1701

Procedure update_validate
  (p_rec                     in pqp_val_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

  --Used to get the ownership for repId at once
  CURSOR c_veh_det_cursor IS
        SELECT  pvr.vehicle_ownership
               ,pvr.vehicle_status
               ,pvr.initial_registration
               ,pvr.registration_number
          FROM  pqp_vehicle_repository_f pvr
         WHERE  pvr.vehicle_repository_id = p_rec.vehicle_repository_id
           AND  pvr.business_group_id=  p_rec.business_group_id
           AND  p_effective_date BETWEEN
                pvr.effective_start_date
            AND pvr.effective_end_date;

Line: 1723

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

Line: 1756

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

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

Line: 1792

      l_message := 'Vehicle status is Inactive,so vehicle cannot update';

Line: 1924

End update_validate;

Line: 1929

Procedure delete_validate
  (p_rec                    in pqp_val_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: 1947

  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_vehicle_allocation_id            => p_rec.vehicle_allocation_id
    );

Line: 1963

    hr_utility.set_location('Purge delete status:'||l_return_status,40);

Line: 1969

  ELSIF p_datetrack_mode = 'DELETE' THEN
       --This is for enddate
       l_return_status := pqp_enddate_veh_alloc
                              (p_rec             =>p_rec
                               ,p_effective_date  =>p_effective_date
                               ,p_message         => l_message );

Line: 1975

       hr_utility.set_location('En date delete status :'||l_return_status,45);

Line: 1983

End delete_validate;