APPS.PQP_VRE_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_repository_f vre
     WHERE vre.vehicle_repository_id = p_vehicle_repository_id
       AND pbg.business_group_id = vre.business_group_id;

Line: 100

    SELECT pbg.legislation_code
      FROM per_business_groups_perf pbg
          ,pqp_vehicle_repository_f vre
     WHERE vre.vehicle_repository_id = p_vehicle_repository_id
       AND pbg.business_group_id = vre.business_group_id;

Line: 462

PROCEDURE chk_non_updateable_args
            (p_effective_date  IN DATE
            ,p_rec             IN pqp_vre_shd.g_rec_type
             ) IS
--
  l_proc     varchar2(72) := g_package || 'chk_non_updateable_args';

Line: 488

End chk_non_updateable_args;

Line: 519

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

END dt_update_validate;

Line: 602

PROCEDURE dt_delete_validate
  (p_vehicle_repository_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: 624

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

END dt_delete_validate;

Line: 676

  ,p_update_flag           IN VARCHAR2
  ) IS

--Declare the cursor to get the registration number count
 CURSOR  c_iden_exist_cursor IS
 SELECT  COUNT(pvr.vehicle_id_number)
   FROM  pqp_vehicle_repository_f pvr
  WHERE  pvr.vehicle_id_number=p_rec.vehicle_id_number
    AND  pvr.business_group_id=p_rec.business_group_id
    AND  (p_effective_date BETWEEN pvr.effective_start_date
                            AND pvr.effective_end_date
     OR  p_effective_date < pvr.effective_start_date);

Line: 691

 SELECT  pvr.vehicle_id_number
   FROM  pqp_vehicle_repository_f pvr
  WHERE  pvr.registration_number=p_rec.registration_number
    AND  pvr.business_group_id=p_rec.business_group_id
    AND  (p_effective_date BETWEEN pvr.effective_start_date
                            AND pvr.effective_end_date
     OR  p_effective_date < pvr.effective_start_date);

Line: 704

   IF p_update_flag = 'Y' THEN
    OPEN c_chk_previous_value_cur;

Line: 747

 SELECT  COUNT(pvr.registration_number)
   FROM  pqp_vehicle_repository_f pvr
  WHERE  pvr.registration_number=p_rec.registration_number
    AND  pvr.business_group_id=p_rec.business_group_id
    AND  (p_effective_date BETWEEN pvr.effective_start_date
                            AND pvr.effective_end_date
     OR  p_effective_date < pvr.effective_start_date);

Line: 869

  l_temp_str := 'SELECT '|| l_column_name ||'
                   FROM  (SELECT '|| l_column_name ||'
                   FROM  pqp_configuration_values
                  WHERE  ((business_group_id = ' ||p_business_group_id ||'
                    AND  legislation_code IS NULL )
                     OR  (business_group_id IS NULL
                    AND  legislation_code =
		         '||''''||p_legislation_code ||''''||')
                    OR (business_group_id IS NULL
                    AND legislation_code IS NULL))
                    AND  PCV_INFORMATION_CATEGORY =
		        '|| ''''||p_information_category ||''''||'
                  ORDER  BY business_group_id,legislation_code )
		  WHERE  ROWNUM=1' ;

Line: 899

   SELECT meaning
     FROM hr_lookups
    WHERE lookup_type = 'PQP_FISCAL_RATINGS_UOM'
      AND enabled_flag    = 'Y';

Line: 957

  SELECT lookup_code
    FROM hr_lookups hrl
   WHERE hrl.lookup_type = cp_lookup_type
     AND hrl.lookup_code = cp_argument_value
     AND enabled_flag    = 'Y';

Line: 989

    SELECT COUNT(rowid)
      FROM hr_lookups hrl
     WHERE hrl.lookup_type = cp_lookup_type
       AND hrl.lookup_code = cp_argument_value
       AND enabled_flag    = 'Y';

Line: 1085

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

Line: 1131

  SELECT pvr.vehicle_status ,1 test
    FROM PQP_VEHICLE_ALLOCATIONS_F pva,pqp_vehicle_repository_f pvr
   WHERE pva.vehicle_repository_id= p_vehicle_repository_id
     AND pva.vehicle_repository_id =pvr.vehicle_repository_id
     AND pva.business_group_id = pvr.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
           OR p_effective_date <= pvr.effective_start_date)
     AND pva.business_group_id = p_business_group_id;

Line: 1187

     SELECT  paa.person_id
       FROM  pqp_vehicle_allocations_f pva
            ,per_all_assignments_f    paa
      WHERE  pva.vehicle_repository_id=p_rec.vehicle_repository_id
        AND  paa.assignment_id=pva.assignment_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
       	          OR p_effective_date < pva.effective_start_date);

Line: 1305

FUNCTION pqp_purge_delete_veh
             (p_rec                    IN  pqp_vre_shd.g_rec_type,
              p_effective_date         IN  DATE ,
              p_message                OUT NOCOPY VARCHAR2
	     ) RETURN VARCHAR2 IS
 --Getting the allocation count for past ,future and current date tracks
 /*CURSOR  c_pesron_names_cursor IS
  SELECT distinct papf.title ||' '||papf.first_name ||' '|| papf.last_name
   FROM  pqp_vehicle_allocations_f pva
        ,per_all_assignments_f    paa
        ,per_people_f papf
  WHERE  pva.vehicle_repository_id=p_rec.vehicle_repository_id
    AND  paa.assignment_id=pva.assignment_id
    AND  papf.person_id=paa.person_id
    AND  (p_effective_date
           BETWEEN  papf.effective_start_date AND papf.effective_end_date
                OR  p_effective_date <= papf.effective_start_date
                OR  p_effective_date >= papf.effective_start_date )
    AND  (p_effective_date
           BETWEEN  paa.effective_start_date AND paa.effective_end_date
                OR  p_effective_date <= paa.effective_start_date
                OR  p_effective_date >= paa.effective_start_date )
    AND  (p_effective_date
           BETWEEN pva.effective_start_date AND pva.effective_end_date
                OR  p_effective_date <= pva.effective_start_date
                OR  p_effective_date >= pva.effective_start_date ); */

Line: 1332

  SELECT distinct hl.meaning ||' '||papf.first_name ||' '|| papf.last_name
   FROM  pqp_vehicle_allocations_f pva
        ,per_all_assignments_f    paa
        ,per_people_f papf
        ,hr_lookups            hl
  WHERE  pva.vehicle_repository_id=p_rec.vehicle_repository_id
    AND  paa.assignment_id=pva.assignment_id
    AND  papf.person_id=paa.person_id
    and  hl.lookup_code=papf.title
    and  hl.lookup_type = 'TITLE'
    and  enabled_flag    = 'Y'
    AND  (p_effective_date
           BETWEEN  papf.effective_start_date AND papf.effective_end_date
                OR  p_effective_date <= papf.effective_start_date
                OR  p_effective_date >= papf.effective_start_date )
    AND  (p_effective_date
           BETWEEN  paa.effective_start_date AND paa.effective_end_date
                OR  p_effective_date <= paa.effective_start_date
                OR  p_effective_date >= paa.effective_start_date );

Line: 1371

	' delete that allocation entry';

Line: 1373

END pqp_purge_delete_veh;

Line: 1382

FUNCTION pqp_enddate_delete_veh
              (p_rec                    IN  pqp_vre_shd.g_rec_type,
               p_effective_date         IN  DATE ,
               p_message                OUT NOCOPY VARCHAR2
	      ) RETURN VARCHAR2 IS

--Getting the allocation count for current and future date tracks
 CURSOR  c_pesron_names_cursor IS
 SELECT  distinct hl.meaning ||' '||papf.first_name ||' '|| papf.last_name
   FROM  pqp_vehicle_allocations_f pva
        ,per_all_assignments_f    paa
        ,per_people_f papf
        ,hr_lookups            hl
  WHERE  pva.vehicle_repository_id=p_rec.vehicle_repository_id
    AND  paa.assignment_id=pva.assignment_id
    AND  papf.person_id=paa.person_id
    and  hl.lookup_code=papf.title
    AND  hl.lookup_type = 'TITLE'
    and  enabled_flag    = 'Y'
    AND  (p_effective_date
           BETWEEN  papf.effective_start_date AND papf.effective_end_date
                OR  p_effective_date <= papf.effective_start_date )
    AND  (p_effective_date
           BETWEEN  paa.effective_start_date AND paa.effective_end_date
                OR  p_effective_date <= paa.effective_start_date )
    AND (p_effective_date
           BETWEEN pva.effective_start_date AND pva.effective_end_date
                OR  p_effective_date <= pva.effective_start_date );

Line: 1430

	' delete that allocation entry';

Line: 1432

END pqp_enddate_delete_veh;

Line: 1444

   SELECT legislation_code
    FROM  per_business_groups_perf
    WHERE business_group_id =p_business_group_id;

Line: 1464

PROCEDURE insert_validate
  (p_rec                   IN pqp_vre_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||'insert_validate';

Line: 1510

  ,p_update_flag           =>'N'
  );

Line: 1830

End insert_validate;

Line: 1835

PROCEDURE update_validate
  (p_rec                     in pqp_vre_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||'update_validate';

Line: 1864

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

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

Line: 1898

  ,p_update_flag           =>'Y'
  );

Line: 1979

      l_return_message := pqp_purge_delete_veh
                          (p_rec             =>p_rec
                          ,p_effective_date  =>p_effective_date
                          ,p_message         =>l_message
                          );

Line: 2248

End update_validate;

Line: 2253

PROCEDURE delete_validate
  (p_rec                    IN pqp_vre_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: 2274

  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_repository_id            => p_rec.vehicle_repository_id
    );

Line: 2286

     l_return_message := pqp_purge_delete_veh
                          (p_rec             =>p_rec
                          ,p_effective_date  =>p_effective_date
                          ,p_message         =>l_message
			  );

Line: 2292

     hr_utility.set_location('Veh purge Delete Status :'||l_return_message,50);

Line: 2299

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

Line: 2307

     hr_utility.set_location('Veh enddate Delete Status :'||l_return_message,55);

Line: 2330

End delete_validate;