APPS.PAY_AE_IV_MIGRATE_PKG SQL Statements

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

Line: 12

  PROCEDURE update_iv_si_element
    (errbuf                      OUT NOCOPY VARCHAR2
    ,retcode                    OUT NOCOPY VARCHAR2
    ,p_business_group_id IN NUMBER) IS


    /* Check if the entry for BG exists in pay_patch_status table */
	CURSOR csr_pay_patch_status (l_number	NUMBER) IS
	SELECT PATCH_NUMBER
	FROM PAY_PATCH_STATUS
	WHERE	PATCH_NUMBER = L_NUMBER
	AND	PATCH_NAME = 'AE_IV_MIGRATION'
	AND	STATUS = 'C'
	AND	LEGISLATION_CODE = 'AE';

Line: 29

	SELECT element_type_id
	FROM pay_element_types_f ele
	WHERE	ele.element_name = l_ele_name
	AND	ele.legislation_code = 'AE'
	/*AND	l_date between ele.effective_start_date and ele.effective_end_date*/;

Line: 38

	SELECT input_value_id
	FROM pay_input_values_f piv
	WHERE	piv.element_type_id = l_element_type_id
	AND	piv.name = 'Exception Type'
	AND	piv.legislation_code = 'AE'
	/*AND	l_date between piv.effective_start_date and piv.effective_end_date*/;

Line: 47

	SELECT input_value_id
	FROM pay_input_values_f piv
	WHERE	piv.element_type_id = l_element_type_id
	AND	piv.name = 'Pay Value'
	AND	piv.legislation_code = 'AE'
	/*AND	l_date between piv.effective_start_date and piv.effective_end_date*/;

Line: 56

	SELECT	assignment_id
	FROM	per_all_assignments_f paf
	WHERE	paf.business_group_id = l_bg_id
	AND	paf.primary_flag = 'Y'
	/*AND	l_date between paf.effective_start_date and paf.effective_end_date*/;

Line: 64

	SELECT	pee.element_entry_id , pee.effective_start_date,pee.effective_end_date
	FROM	pay_element_entries_f pee
	WHERE	pee.element_type_id = l_si_et
	AND	pee.assignment_id = l_asg_id
	Order by effective_start_date asc;

Line: 73

   	SELECT *
   	FROM pay_element_entries_f
   	WHERE	element_type_id = l_si_et
   	AND	assignment_id = l_asg_id
   	ORDER BY effective_start_date ASC;

Line: 82

	SELECT max(pee.object_version_number)
	FROM	pay_element_entries_f pee
	WHERE	pee.element_type_id = l_si_et
	AND	pee.assignment_id = l_asg_id;

Line: 89

	SELECT	peev.screen_entry_value
	FROM	pay_element_entry_values_f peev
	WHERE	peev.element_entry_id = l_sia_eeid
	AND	peev.input_value_id = l_sia_exc_iv_id
	AND	l_date BETWEEN peev.effective_start_date AND peev.effective_end_date;

Line: 97

	SELECT	peev.screen_entry_value
	FROM	pay_element_entry_values_f peev
	WHERE	peev.element_entry_id = l_si_eeid
	AND	peev.input_value_id = l_si_exc_iv_id
	AND	l_date BETWEEN peev.effective_start_date AND peev.effective_end_date;

Line: 105

	SELECT	element_entry_value_id
	FROM	PAY_ELEMENT_ENTRY_VALUES_F
	WHERE	ELEMENT_ENTRY_ID = L_SI_EE
	AND 	INPUT_VALUE_ID = l_si_exc_id;

Line: 126

	L_UPDATE_WARNING	BOOLEAN;

Line: 146

	L_DATETRACK_UPDATE_MODE varchar2(240);

Line: 175

		INSERT INTO PAY_PATCH_STATUS
			(ID,
			PATCH_NUMBER,
			PATCH_NAME,
			STATUS,
			APPLIED_DATE,
			LEGISLATION_CODE)
		SELECT
			pay_patch_status_s.nextval,
			p_business_group_id,
			'AE_IV_MIGRATION',
			'C',
			sysdate,
			'AE'
		FROM DUAL;

Line: 225

			l_update_warning := null;

Line: 263

				/* Insert an entry value for Exception type on SI element */

				INSERT INTO PAY_ELEMENT_ENTRY_VALUES_F
				( ELEMENT_ENTRY_VALUE_ID
				, EFFECTIVE_START_DATE
				, EFFECTIVE_END_DATE
				, INPUT_VALUE_ID
				, ELEMENT_ENTRY_ID
				, SCREEN_ENTRY_VALUE
				) Values
				(pay_element_entry_values_s.nextval
				, l_si_ee_start_date
				, l_si_ee_end_date
				, l_si_iv_id
				, l_si_ee_id
				, '');

Line: 308

						L_DATETRACK_UPDATE_MODE := 'UPDATE';

Line: 310

					/* Update the SI element entry and entry values with exception type value */
						HR_ENTRY_API.update_element_entry
						 (
						  -- Update Mode
						  p_dt_update_mode             => L_DATETRACK_UPDATE_MODE,
						  -- Date on which change is taking place
						  p_session_date           => l_t_sia_start_date,
						  -- Check to see if the entry is being updated
						  p_check_for_update          => 'N' ,
						  -- Element Entry Table
						  p_element_entry_id           => l_si_ee_id,
						  p_input_value_id1            => l_si_py_id,
						  p_input_value_id2            => l_si_iv_id,
						  p_entry_value1               => '',
						  p_entry_value2	       => get_lookup_meaning('AE_SI_EXCEPTION_TYPES',l_t_sia_exc_val),
						  p_override_user_ent_chk    => 'N'
						 )
						 ;

Line: 333

							HR_ENTRY_API.update_element_entry
							 (
							  -- Update Mode
							  p_dt_update_mode             => L_DATETRACK_UPDATE_MODE,
							  -- Date on which change is taking place
							  p_session_date           => l_t_sia_end_date+1,
							  -- Check to see if the entry is being updated
							  p_check_for_update          => 'N' ,
							  -- Element Entry Table
							  p_element_entry_id           => l_si_ee_id,
							  p_input_value_id1            => l_si_py_id,
							  p_input_value_id2            => l_si_iv_id,
							  p_entry_value1               => '',
							  p_entry_value2	       => '',
							  p_override_user_ent_chk    => 'N'
							 )
							 ;

Line: 368

END update_iv_si_element;

Line: 377

      select meaning
      from   hr_lookups
      where  lookup_type = p_lookup_type
      and    lookup_code = p_lookup_code;