APPS.PER_JP_CONTACT_EXTRA_INFO SQL Statements

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

Line: 21

		select	'Y'
		from	per_contact_extra_info_f
		where	contact_relationship_id = p_contact_relationship_id
		and	contact_extra_info_id <> p_contact_extra_info_id
		and	information_type in ('JP_ITAX_DEPENDENT', 'JP_ITAX_DEPENDENT_ON_OTHER_EMP', 'JP_ITAX_DEPENDENT_ON_OTHER_PAY')
		and	information_type <> p_information_type
		and	effective_end_date >= p_validation_start_date
		and	effective_start_date <= p_validation_end_date;

Line: 31

		select	'Y'
		from	per_contact_extra_info_f
		where	contact_relationship_id = p_contact_relationship_id
		and	contact_extra_info_id <> p_contact_extra_info_id
		and	information_type in ('JP_HI_SPOUSE', 'JP_HI_DEPENDENT')
		and	information_type <> p_information_type
		and	effective_end_date >= p_validation_start_date
		and	effective_start_date <= p_validation_end_date;

Line: 81

	if p_datetrack_mode in (hr_api.g_future_change, hr_api.g_delete_next_change) then
		chk_information_type(
			p_contact_extra_info_id		=> p_contact_extra_info_id,
			p_information_type		=> p_information_type_o,
			p_contact_relationship_id	=> p_contact_relationship_id_o,
			p_validation_start_date		=> p_validation_start_date,
			p_validation_end_date		=> p_validation_end_date);

Line: 136

   SELECT 'Y' FROM per_contact_extra_info_f
   WHERE contact_relationship_id = p_contact_relationship_id
   AND information_type LIKE 'JP_ITAX%'
   AND information_type <> p_information_type
   AND p_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 147

   SELECT 'Y' FROM per_contact_extra_info_f
   WHERE contact_relationship_id = p_contact_relationship_id
   AND information_type LIKE 'JP_HI%'
   AND information_type <> p_information_type
   AND p_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 242

   SELECT 'Y' FROM per_contact_extra_info_f
   WHERE contact_relationship_id = p_contact_relationship_id
   AND information_type LIKE 'JP_ITAX%'
   AND information_type <> p_information_type
   AND p_effective_date < effective_start_date;

Line: 253

   SELECT 'Y' FROM per_contact_extra_info_f
   WHERE contact_relationship_id = p_contact_relationship_id
   AND information_type LIKE 'JP_HI%'
   AND information_type <> p_information_type
   AND p_effective_date < effective_start_date;

Line: 340

  p_datetrack_delete_mode       VARCHAR2) IS
  --
  CURSOR cel_information_type IS
   SELECT contact_relationship_id, information_type FROM per_contact_extra_info_f
   WHERE contact_extra_info_id = p_contact_extra_info_id
   AND p_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 351

   SELECT 'Y' FROM per_contact_extra_info_f
   WHERE information_type LIKE 'JP_ITAX%'
   AND information_type <> p_information_type
   AND contact_relationship_id = p_contact_relationship_id
   AND p_effective_date < effective_start_date;

Line: 361

   SELECT 'Y' FROM per_contact_extra_info_f
   WHERE information_type LIKE 'JP_HI%'
   AND information_type <> p_information_type
   AND contact_relationship_id = p_contact_relationship_id
   AND p_effective_date < effective_start_date;

Line: 373

  IF p_datetrack_delete_mode IN (hr_api.g_future_change, hr_api.g_delete_next_change) THEN
   --
   OPEN cel_information_type;

Line: 461

   SELECT pbg.legislation_code
   FROM per_business_groups pbg, per_contact_relationships pcr
   WHERE pcr.contact_relationship_id = p_contact_relationship_id
   AND pcr.business_group_id = pbg.business_group_id;

Line: 469

   SELECT 'Y' FROM per_contact_relationships
   WHERE contact_relationship_id = p_contact_relationship_id
   AND cont_information_category = 'JP'
   AND cont_information1 <> 'Y';

Line: 552

  CURSOR cel_dependent IS SELECT
    TRUNC(MONTHS_BETWEEN(NVL(papf.date_of_death, l_eoy) + 1, papf.date_of_birth) / 12) age,
    pcr.contact_type
   FROM
    per_all_people_f papf,
    per_contact_relationships pcr
   WHERE pcr.contact_relationship_id = p_contact_relationship_id
   AND pcr.contact_person_id = papf.person_id
   AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date;

Line: 639

  p_datetrack_update_mode	VARCHAR2,
  p_contact_relationship_id     NUMBER,
  p_information_type            VARCHAR2,
  p_cei_information1            VARCHAR2) IS
  --
  l_soy         DATE := TRUNC(p_effective_date, 'YYYY');

Line: 647

  CURSOR cel_dependent IS SELECT
    TRUNC(MONTHS_BETWEEN(NVL(papf.date_of_death, DECODE(p_datetrack_update_mode, 'CORRECTION', ADD_MONTHS(TRUNC(pceif.effective_start_date, 'YYYY'), 12) - 1, l_eoy)) + 1, papf.date_of_birth) / 12) age,
    pcr.contact_type
   FROM
    per_all_people_f papf,
    per_contact_relationships pcr,
    per_contact_extra_info_f pceif
   WHERE pcr.contact_relationship_id = p_contact_relationship_id
   AND pcr.contact_person_id = papf.person_id
   AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND pcr.contact_relationship_id = pceif.contact_relationship_id
   AND pceif.information_type = p_information_type
   AND p_effective_date BETWEEN pceif.effective_start_date AND pceif.effective_end_date;