eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_TEMPLATE_IVS SQL Statements

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

Line: 92


SELECT	DISTINCT pel.element_link_id
INTO	l_element_link_id
FROM	pay_element_links_f	pel
WHERE	pel.element_type_id	= p_element_type_id
ORDER BY pel.effective_start_date;

Line: 100


SELECT	min(pel.effective_start_date)
INTO	l_link_eff_start
FROM	pay_element_links_f	pel
WHERE	pel.element_link_id	= l_element_link_id;

Line: 105


SELECT	max(pel.effective_end_date)
INTO	l_link_eff_end
FROM	pay_element_links_f	pel
WHERE	pel.element_link_id	= l_element_link_id;

Line: 115


INSERT INTO pay_link_input_values_f (
	link_input_value_id,
	element_link_id,
	input_value_id,
	effective_start_date,
	effective_end_date,
	costed_flag,
	default_value,
	max_value,
	min_value,
	warning_or_error
	)
VALUES (
	pay_link_input_values_s.nextval,
	l_element_link_id,
	p_input_value_id, -- ie. id of iv being added
	l_link_eff_start,
	l_link_eff_end,
	p_costed_flag,
	p_default_value,
	p_max_value,
	p_min_value,
	p_warning_or_error
	);

Line: 143


SELECT DISTINCT	pee.element_entry_id
INTO	l_element_entry_id
FROM	pay_element_entries_f	pee
WHERE	pee.element_link_id	= l_element_link_id -- ie. link found in 1a.
ORDER BY pee.element_entry_id;

Line: 153


select	DISTINCT pev.effective_start_date,
	pev.effective_end_date
from	pay_element_entry_values_f	pev
where	pev.element_entry_id	= l_element_entry_id
order by pev.effective_start_date;

Line: 162


INSERT INTO pay_element_entry_values_f (
	element_entry_value_id,
	element_entry_id,
	input_value_id,
	effective_start_date,
	effective_end_date,
	screen_entry_value
	)
VALUES (
	pay_element_entry_values_s.nextval,
	l_element_entry_id,
	p_input_value_id, -- ie. id of iv being added
	l_entry_eff_start,
	l_entry_eff_end,
	nvl(p_default_value, l_screen_entry_value)
	);

Line: 182


SELECT	DISTINCT prr.run_result_id
INTO	l_run_result_id
FROM	pay_run_results		prr
AND	prr.element_type_id	= p_element_type_id -- ie. ele w/new iv.
ORDER BY prr.run_result_id;

Line: 190


INSERT INTO pay_run_result_values (
	run_result_id,
	input_value_id,
	result_value
	)
VALUES (
	l_run_result_id,
	p_input_value_id, -- ie. id of iv being added
	nvl(p_default_value, l_run_result_value
	);

Line: 229


SELECT	pel.element_link_id
FROM		pay_element_links_f	pel
WHERE	pel.element_type_id	= p_ele_id
ORDER BY 	pel.effective_start_date;

Line: 235


SELECT 	pee.element_entry_id
FROM		pay_element_entries_f	pee
WHERE	pee.element_link_id	= p_link_id
ORDER BY 	pee.element_entry_id;

Line: 241


select		pev.effective_start_date,
		pev.effective_end_date
from		pay_element_entry_values_f	pev
where		pev.element_entry_id	= p_entry_id
order by 	pev.effective_start_date;

Line: 248


SELECT	prr.run_result_id
FROM		pay_run_results		prr
WHERE	prr.element_type_id	= p_eletype_id
ORDER BY 	prr.run_result_id;

Line: 274


  SELECT	min(pel.effective_start_date)
  INTO	l_link_eff_start
  FROM	pay_element_links_f	pel
  WHERE	pel.element_link_id	= l_element_link_id;

Line: 279


  SELECT	max(pel.effective_end_date)
  INTO	l_link_eff_end
  FROM	pay_element_links_f	pel
  WHERE	pel.element_link_id	= l_element_link_id;

Line: 292


Check if link_input_value already exists before inserting...
if it does, do nothing...all this tells us is the upgrade has
already been attempted for this element...and the input value has
already been added successfully to this point.
*/

  already_exists := hr_template_existence.upg_link_iv_exists(
			p_element_link_id	=> l_element_link_id,
			p_input_val_id		=> p_input_value_id);

Line: 304


    INSERT INTO pay_link_input_values_f (
	link_input_value_id,
	element_link_id,
	input_value_id,
	effective_start_date,
	effective_end_date,
	costed_flag,
	default_value,
	max_value,
	min_value,
	warning_or_error
	)
    VALUES (
	pay_link_input_values_s.nextval,
	l_element_link_id,
	p_input_value_id,
	l_link_eff_start,
	l_link_eff_end,
	p_costed_flag,
	p_default_value,
	p_max_value,
	p_min_value,
	p_warning_or_error
	);

Line: 373


        INSERT INTO pay_element_entry_values_f (
	element_entry_value_id,
	element_entry_id,
	input_value_id,
	effective_start_date,
	effective_end_date,
	screen_entry_value
	)
        VALUES (
	pay_element_entry_values_s.nextval,
	l_element_entry_id,
	p_input_value_id,
	l_entry_eff_start,
	l_entry_eff_end,
	nvl(p_default_value, l_screen_entry_value)
	);

Line: 394


        select count(0)
        into   entryval_exists
        from   pay_element_entry_values_f
        where  element_entry_value_id = already_exists
        and    effective_start_date = l_entry_eff_start;

Line: 402


          INSERT INTO pay_element_entry_values_f (
	  element_entry_value_id,
  	  element_entry_id,
	  input_value_id,
	  effective_start_date,
	  effective_end_date,
	  screen_entry_value
  	  )
          VALUES (
	  already_exists,
	  l_element_entry_id,
	  p_input_value_id,
	  l_entry_eff_start,
	  l_entry_eff_end,
	  nvl(p_default_value, l_screen_entry_value)
	  );

Line: 460


    INSERT INTO pay_run_result_values (
	run_result_id,
	input_value_id,
	result_value
	)
    VALUES (
	l_run_result_id,
	p_input_value_id,
	nvl(p_default_value, l_run_result_value)
	);

Line: 494


  This procedure controls the third party inserts when an input value is
  created manually. (Rather than being created at the same time as an element
  type.) It calls the procedures create_link_input_value and
  hr_balances.ins_balance_feed.

  NOTE: This procedure has been copied from hr_input_values package.
  For purposes of upgrading template earnings and deductions, we do not
  need to call the link input value and balance feed api - so these have been
  commented out.  The upgrade procedure will handle adding these rows
  appropriately over the lifetime of the element type being upgraded.
  */
--
PROCEDURE	ins_3p_input_values(p_val_start_date	in date,
				p_val_end_date		in date,
				p_element_type_id	in number,
				p_primary_classification_id in number,
				p_input_value_id	in number,
				p_default_value		in varchar2,
				p_max_value		in varchar2,
				p_min_value		in varchar2,
				p_warning_or_error_flag	in varchar2,
				p_input_value_name	in varchar2,
				p_db_items_flag		in varchar2,
				p_costable_type	   	in varchar2,
				p_hot_default_flag	in varchar2,
				p_business_group_id	in number,
				p_legislation_code	in varchar2,
				p_startup_mode		in varchar2) is
--
	l_pay_value_name	varchar2(80);

Line: 536


	hr_input_values.create_link_input_value('INSERT_INPUT_VALUE',
				  NULL,
				  p_input_value_id	   ,
				  p_input_value_name	   ,
				  NULL,
				  p_val_start_date  ,
				  p_val_end_date    ,
				  p_default_value	   ,
				  p_max_value		   ,
				  p_min_value		   ,
				  p_warning_or_error_flag  ,
				  p_hot_default_flag	   ,
				  p_legislation_code	   ,
				  l_pay_value_name	   ,
				  p_element_type_id        );

Line: 588


  Checks attributes of inserted and update input values for concurrence
  with business rules.

  NOTE: This procedure has been copied from hr_input_values package.
  For purposes of upgrading template earnings and deductions, we do not
  need to check for existing element entries or run results - so these checks
  have been commented out.  The upgrade procedure will handle adding these
  rows appropriately over the lifetime of the element type being upgraded.
 */
--
 PROCEDURE chk_input_value(p_element_type_id         in number,
			   p_legislation_code	     in varchar2,
                           p_val_start_date     in date,
                           p_val_end_date       in date,
			   p_insert_update_flag	     in varchar2,
			   p_input_value_id          in number,
			   p_rowid                   in varchar2,
			   p_recurring_flag          in varchar2,
			   p_mandatory_flag	     in varchar2,
			   p_hot_default_flag	     in varchar2,
			   p_standard_link_flag	     in varchar2,
			   p_classification_type     in varchar2,
			   p_name                    in varchar2,
			   p_uom                     in varchar2,
			   p_min_value               in varchar2,
			   p_max_value               in varchar2,
			   p_default_value           in varchar2,
			   p_lookup_type             in varchar2,
			   p_formula_id              in number,
			   p_generate_db_items_flag  in varchar2,
			   p_warning_or_error        in varchar2) is
--
 v_validation_check  varchar2(1);

Line: 644


  if p_insert_update_flag = 'INSERT' then
  -- Make sure that a maximum of 6 input values can be created
  begin
--
   select count(distinct iv.input_value_id)
   into   v_num_input_values
   from   pay_input_values_f iv
   where  iv.element_type_id = p_element_type_id
   and	  p_val_start_date between
	iv.effective_start_date and iv.effective_end_date;

Line: 675


    	select 'N'
    	into v_validation_check
    	from sys.dual
    	where exists
		(select 1
		from 	pay_element_links_f el,
			pay_element_entries_f ee
		where 	p_element_type_id = el.element_type_id
		and	el.element_link_id = ee.element_link_id
		and 	ee.effective_end_date >= p_val_start_date
		and	ee.effective_start_date <= p_val_end_date);

Line: 702


    end if;-- In INSERT mode

Line: 707


	select 'N'
	into v_validation_check
	from sys.dual
	where exists
	(select 1
	from pay_input_values_f
	where element_type_id = p_element_type_id
	and input_value_id <> p_input_value_id
	and upper(p_name) = upper(name));

Line: 836


    select 'N'
    into   v_validation_check
    from   sys.dual
    where  not exists(select 1
		      from   hr_lookups
		      where  lookup_type = p_lookup_type
			and  lookup_code = p_default_value);

Line: 865


	select 'N'
	into v_validation_check
	from sys.dual
	where exists
		(select 1
		from pay_run_results rr
		where rr.element_type_id = p_element_type_id);