APPS.HR_USER_DEDN_DRV SQL Statements

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

Line: 39

    07-Jan-04   kaverma       115.3   3349575    Modified query in insert_formula
                                                 to remove Full Table Scan
    07-Jan-04   kaverma       115.3   3349575    Modified query in insert_formula
                                                 to correct the join condition
    13-Nov-11   emunisek      115.5   13484606   Replaced FF_FORMULAS_F.FORMULA_TEXT%TYPE
                                                 for variables based on formula_text column.


*/



/* Cursor to get input_value_id and element_type_id given the names of the objects */

CURSOR csr_input_id(p_ele_name VARCHAR2
	, p_inp_val_name VARCHAR2
	, p_bg_id NUMBER
	, p_eff_start_date DATE) IS
  SELECT piv.input_value_id
	  ,piv.element_type_id
  FROM pay_element_types_f pet
	,pay_input_values_f  piv
  WHERE pet.element_name = p_ele_name
  AND pet.element_type_id = piv.element_type_id
  AND piv.name = p_inp_val_name
  AND	pet.business_group_id +0 = p_bg_id
  AND	p_eff_start_date 	between pet.effective_start_date
  					AND	pet.effective_end_date;

Line: 70

FUNCTION insert_formula (	p_ff_ele_name in varchar2,
		      p_ff_formula_name in varchar2,
		      p_ele_formula_name in varchar2,
			p_ff_bg_id		in number,
			p_eff_start_date	in date,
			p_eff_end_date in date)
RETURN number IS

/* Retrieves template formula text, replaces  in the formula with element_name
 * passed in and inserts the formula.
 */
-- local vars
r_formula_id	number;		-- Return var

Line: 92

  hr_utility.set_location('pyusddwp.insert_formula',10);

Line: 93

  SELECT 	FF.formula_text, FF.formula_type_id, FF.description
  INTO		r_skeleton_formula_text, r_skeleton_formula_type_id, r_description
  FROM		ff_formulas_f	FF
  WHERE		FF.formula_name		= p_ff_formula_name
  AND		FF.business_group_id 	IS NULL
  AND		FF.legislation_code	= 'US'
  AND           p_eff_start_date between FF.effective_start_date and FF.effective_end_date
  AND           FF.formula_id           >= 0; -- Bug#3349575

Line: 103

  hr_utility.set_location('pyusddwp.insert_formula',15);

Line: 119

  hr_utility.set_location('pyusddwp.insert_formula',30);

Line: 120

  SELECT 	ff_formulas_s.nextval
  INTO	r_formula_id
  FROM 	sys.dual;

Line: 124

  hr_utility.set_location('pyusddwp.insert_formula',40);

Line: 125

  INSERT INTO ff_formulas_f (
 	FORMULA_ID,
	EFFECTIVE_START_DATE,
 	EFFECTIVE_END_DATE,
 	BUSINESS_GROUP_ID,
	LEGISLATION_CODE,
	FORMULA_TYPE_ID,
	FORMULA_NAME,
 	DESCRIPTION,
	FORMULA_TEXT,
	STICKY_FLAG,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	CREATED_BY,
	CREATION_DATE)
values (
 	r_formula_id,
 	p_eff_start_date,
	p_eff_end_date,
	p_ff_bg_id,
	NULL,
	r_skeleton_formula_type_id,
	r_ele_formula_name,
	r_description,
	r_ele_formula_text,
	'N',
	NULL,
	NULL,
	NULL,
	-1,
	p_eff_start_date);

Line: 160

END insert_formula;

Line: 181

 * and inserts the Result Rule for the formula
 */

BEGIN
    hr_utility.set_location('pyusddwp.do_employer_match',10);

Line: 188

    v_formula_id:= insert_formula (
			p_ff_ele_name	=> p_ename,
			p_ff_formula_name	=> 'EMPLOYER_MATCH_TEMPLATE',
			p_ele_formula_name=> v_formula_name,
			p_ff_bg_id		=> p_bg_id,
			p_eff_start_date	=> p_start_date,
			p_eff_end_date	=> p_end_date);

Line: 261

  SELECT balance_type_id
  FROM   pay_balance_types
  WHERE  balance_name = p_bal_name
  AND    business_group_id + 0 = p_bg_id;

Line: 280

	  pay_balance_feeds_f_pkg.insert_row (l_row,
			l_balance_feed_id,
			p_eff_start_date,
			p_eff_end_date,
			p_bg_id,
			g_template_leg_code,
			l_balance_type_id,
			l_inpval_id,
			1,
			g_template_leg_subgroup);