APPS.PAY_USER_COLUMN_INSTA_MATRIX SQL Statements

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

Line: 39

select 1
 from pay_user_column_instances_f puci
 where user_column_instance_id = c_user_column_instance_id
 and c_effective_date between effective_start_date and effective_end_date
 and exists( select null
              from pay_user_column_instances_f pui
	      where pui.user_column_instance_id = puci.user_column_instance_id
	      and pui.effective_start_date > puci.effective_end_date
	      );

Line: 80

     l_statement := 'select batch_line_id from hrdpv_create_user_column_insta where
                     p_user_row_user_key = :1 and p_user_column_user_key = :2';

Line: 83

     l_statement := 'select batch_line_id from hrdpv_update_user_column_insta where
                     p_user_row_user_key = :1 and p_user_column_user_key = :2';

Line: 99

function update_live_table_value
(
  p_user_row_name varchar2
 ,p_user_column_name varchar2
 ,p_user_table_name varchar2
 ,p_effective_date date
 ,p_business_group_id number
 ,p_value varchar2
 ,p_batch_id number
 ,p_link_value number
) return boolean
is

l_user_table_id number;

Line: 116

l_update_mode varchar2(30);

Line: 128

 select user_column_instance_id,effective_start_date,business_group_id,legislation_code
  from  pay_user_column_instances_f
  where user_row_id = c_user_row_id
  and   user_column_id = c_user_column_id
  and   c_effective_date between effective_start_date and effective_end_date;

Line: 136

 select user_table_id
 from pay_user_tables
 where user_table_name = c_user_table_name
 and (business_group_id is null or business_group_id = c_business_group_id)
 and (legislation_code is null or legislation_code = hr_api.return_legislation_code(c_business_group_id));

Line: 145

 select user_column_id
 from pay_user_columns
 where user_table_id = c_user_table_id
 and user_column_name = c_user_column_name;

Line: 152

 select user_row_id
  from pay_user_rows_f
  where row_low_range_or_name = c_user_row_name
  and user_table_id = c_user_table_id
  and c_effective_date between effective_start_date and effective_end_date;

Line: 160

select name
 from per_business_groups
 where business_group_id = c_business_group_id;

Line: 204

					 ,p_mode => 'UPDATE');

Line: 207

        l_update_mode := 'UPDATE_CHANGE_INSERT';

Line: 210

	   l_update_mode := 'CORRECTION';

Line: 212

	   l_update_mode := 'UPDATE';

Line: 217

                    'hrdpp_update_user_column_insta.insert_batch_lines( ' ||
                    'p_batch_id => :1 ' ||
		    ',p_data_pump_batch_line_id => :2 '||
		    ',p_data_pump_business_grp_name => :3 '||
		    ',p_user_sequence => :4 '||
		    ',p_link_value => :5 '||
		    ',p_effective_date => :6 '||
		    ',P_DATETRACK_UPDATE_MODE => :7 '||
		    ',P_VALUE => :8 '||
		    ',P_USER_COLUMN_USER_KEY => :9 '||
		    ',P_USER_ROW_USER_KEY => :10 );' ||

Line: 231

                                         l_update_mode,p_value,l_user_column_user_key,l_user_row_user_key;

Line: 241

end update_live_table_value;

Line: 309

select business_group_id
from per_business_groups
where name = c_business_group_name;

Line: 327

l_statement := 'select 1 from pay_user_rows_f pur,pay_user_tables put where row_low_range_or_name = :1 and
(put.business_group_id is null or put.business_group_id = :2)
and (put.legislation_code is null or put.legislation_code = hr_api.return_legislation_code(:3))
and put.user_table_id = pur.user_table_id and
put.user_table_name = :4';

Line: 356

'select qualifier ,p_user_column_user_key
from (select ''LIVE'' qualifier,''PAY_USER_COLUMN:'' || :1 || '':'' || :2 || ''#'' ||user_column_name p_user_column_user_key
      from pay_user_columns puc,pay_user_tables put
      where put.user_table_name= :3
      and puc.user_table_id=put.user_table_id
      and (put.business_group_id is null or put.business_group_id = :4)
      and (put.legislation_code is null or put.legislation_code = hr_api.return_legislation_code(:5))
      Union
      select ''TEMP'' qualifier,p_user_column_user_key
      from hrdpv_create_user_column
      where p_user_table_user_key= ''PAY_USER_TABLE:'' || :6 ||''#'' || :7  and
      line_status <> ''C'')
      order by upper(substr(p_user_column_user_key,instr(p_user_column_user_key,''#'',1,1)+1))';

Line: 443

        l_create_line := update_live_table_value(p_row_low_range_or_name
	                                        ,l_user_column_name
						,p_user_table_name
						,p_effective_date
						,l_business_group_id
						,l_user_column_user_keys(i).user_column_value
						,p_batch_id
						,l_link_value
						 );

Line: 453

	   insert_user_key(p_business_group => p_data_pump_business_grp_name
	                   ,p_user_row_name => p_row_low_range_or_name
			   ,p_user_table_name => p_user_table_name
			   ,p_effective_date => p_effective_date);

Line: 458

           insert_user_key(p_business_group => p_data_pump_business_grp_name
	                   ,p_user_column_name => l_user_column_name
	                   ,p_user_table_name => p_user_table_name);

Line: 468

            insert_user_key(p_business_group => p_data_pump_business_grp_name
	                   ,p_user_row_name => p_row_low_range_or_name
			   ,p_user_table_name => p_user_table_name
			   ,p_effective_date => p_effective_date);

Line: 474

	    insert_user_key(p_business_group => p_data_pump_business_grp_name
	                   ,p_user_column_name => substr(l_user_column_user_keys(i).user_column_key,
			                          instr(l_user_column_user_keys(i).user_column_key,'#',1,1)+1)
	                   ,p_user_table_name => p_user_table_name);

Line: 491

            'hrdpp_create_user_column_insta.insert_batch_lines' ||
            '(p_batch_id => :batch_id ' ||
            ',p_data_pump_batch_line_id => :data_pump_batch_line_id ' ||
	    ',p_user_sequence => :user_sequence '||
            ',p_link_value => :link_value ' ||
            ',p_data_pump_business_grp_name => :data_pump_business_grp_name '||
            ',P_EFFECTIVE_DATE => :EFFECTIVE_DATE ' ||
            ',P_VALUE => :VALUE '||
            ',P_USER_ROW_USER_KEY => :USER_ROW_USER_KEY ' ||
            ',P_USER_COLUMN_USER_KEY => :USER_COLUMN_USER_KEY );' ||

Line: 533

	select	batch_status status
	from	hr_pump_batch_headers
	where	batch_id = p_batch_id;

Line: 574

   select link_value into l_link_value
    from hr_pump_batch_lines
   where batch_line_id = p_batch_line_id;

Line: 596

       'select link_value
        from hrdpv_create_user_table
       where p_user_table_user_key = :1
       and link_value is not null
   Union
       select link_value
        From hrdpv_create_user_row
       where p_user_table_user_key = :1
       and link_value is not null
   Union
       select link_value
        from hrdpv_create_user_column
       where p_user_table_user_key = :1
       and link_value is not null
   Union
       select link_value
        from hrdpv_create_user_column_insta
       where substr( p_user_row_user_key, (instr(p_user_row_user_key, '':'' , 1)+1),
       (instr(p_user_row_user_key, ''#'' ,1)) - (instr(p_user_row_user_key, '':'' , 1)+1) )
       = :2 || '':'' || :3
       and link_value is not null';

Line: 623

      select nvl(max(link_value),0)+1  into l_link_value from hr_pump_batch_lines;

Line: 635

procedure insert_user_key
(
  p_business_group     varchar2
 ,p_user_column_name  varchar2
 ,p_user_table_name   varchar2
)
is

l_user_key_value varchar2(240);

Line: 653

select puc.user_column_id
 from  pay_user_columns puc,
       per_business_groups pbg,
       pay_user_tables put
where  (put.business_group_id is null or put.business_group_id = pbg.business_group_id)
and    (put.legislation_code is null or put.legislation_code= hr_api.return_legislation_code(pbg.business_group_id))
and    pbg.name = c_business_group
and    puc.user_column_name = c_user_column_name
and    puc.user_table_id = put.user_table_id
and    put.user_table_name = c_user_table_name
and    not exists(select null
                 from hr_pump_batch_line_user_keys
		 where user_key_value = c_user_key_value
		 and unique_key_id = puc.user_column_id);

Line: 672

 select user_key_id
  from hr_pump_batch_line_user_keys
  where user_key_value like 'PAY_USER_COLUMN%'
  and unique_key_id = c_unique_key_id;

Line: 691

        delete from hr_pump_batch_line_user_keys where user_key_id = l_user_key_id;

Line: 704

end insert_user_key;

Line: 710

procedure insert_user_key
(
  p_business_group     varchar2
 ,p_user_row_name     varchar2
 ,p_user_table_name   varchar2
 ,p_effective_date    date
)
is

l_user_key_value varchar2(240);

Line: 730

select pur.user_row_id
 from  pay_user_rows_f pur,
       per_business_groups pbg,
       pay_user_tables put
where  (put.business_group_id is null or put.business_group_id= pbg.business_group_id)
and    (put.legislation_code is null or put.legislation_code = hr_api.return_legislation_code(pbg.business_group_id))
and    pbg.name = c_business_group
and    c_effective_date between pur.effective_start_date and pur.effective_end_date
and    pur.row_low_range_or_name = c_user_row_name
and    pur.user_table_id = put.user_table_id
and    put.user_table_name = c_user_table_name
and    not exists(select null
                 from hr_pump_batch_line_user_keys
		 where user_key_value = c_user_key_value
		 and unique_key_id = pur.user_row_id);

Line: 749

 select user_key_id
  from hr_pump_batch_line_user_keys
  where user_key_value like 'PAY_USER_ROW%'
  and unique_key_id = c_unique_key_id;

Line: 773

        delete from hr_pump_batch_line_user_keys where user_key_id = l_user_key_id;

Line: 786

end insert_user_key;

Line: 794

procedure insert_user_key
(
  p_business_group     varchar2
 ,p_user_table_name   varchar2
)
is

l_user_table_id pay_user_tables.user_table_id%type;

Line: 809

select put.user_table_id
 from  pay_user_tables put,per_business_groups pbg
 where user_table_name = c_user_table_name
 and   pbg.name = c_business_group
 and   (put.business_group_id is null or put.business_group_id = pbg.business_group_id)
 and   (put.legislation_code is null or put.legislation_code= hr_api.return_legislation_code(pbg.business_group_id));

Line: 818

 select user_key_id
  from hr_pump_batch_line_user_keys
  where user_key_value like 'PAY_USER_TABLE%'
  and unique_key_id = c_unique_key_id;

Line: 837

        delete from hr_pump_batch_line_user_keys where user_key_id = l_user_key_id;

Line: 851

end insert_user_key;

Line: 891

 select user_table_id
 from pay_user_tables
 where (business_group_id is null or business_group_id = c_business_group_id )
 and (legislation_code is null or legislation_code = hr_api.return_legislation_code(c_business_group_id))
 and user_table_name = c_user_table_name;

Line: 899

 select name
 from per_business_groups
 where business_group_id = c_business_group_id;

Line: 926

  l_statement :='select user_column_name, rownum
   from (select * from
     (select user_column_name
      from pay_user_columns puc
      where puc.user_table_id = :1

      Union

      select p_user_column_name
      from hrdpv_create_user_column
      where p_user_table_user_key=''PAY_USER_TABLE:'' || :2 ||''#'' || :3 and line_status <> ''C'')
      order by upper(user_column_name))';

Line: 957

 l_statement := '(select puc.user_column_name || '':'' || pui.value qualified_value
  from pay_user_column_instances_f pui,
       pay_user_columns puc,
       pay_user_rows_f pur
 where pur.row_low_range_or_name = :1
 and   pur.user_table_id = :2
 and   puc.user_table_id = pur.user_table_id
 and   pui.user_column_id = puc.user_column_id
 and   pui.user_row_id = pur.user_row_id
 and   sysdate between pui.effective_start_date and pui.effective_end_date
 Union
 select substr(p_user_column_user_key,instr(p_user_column_user_key,''#'',1,1)+1) || '':'' || p_value
  qualified_value
  from  hrdpv_create_user_column_insta
  where p_user_row_user_key = :3
  and line_status <>''C'')';