/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2010 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants, exceptions and variables
  --
  l_UDT            	CONSTANT varchar2(30) := 'XXAS_SRC_VACANCY_FACETS';
  l_YES            	CONSTANT varchar2(1)  := 'Y';
  l_EFF_DT         	CONSTANT date         := to_date('1900-01-01','YYYY-MM-DD'); 
	
  l_exists                  varchar2(1);
  l_row_id 										VARCHAR2(32767);
  l_table_id 									NUMBER;
  l_column_id 								NUMBER;
	l_user_row_id								NUMBER;
	l_row_ovn 									NUMBER;
	l_row_effective_start_date	DATE;
	l_row_effective_end_date 		DATE;
  l_effective_start_date   		DATE;
  l_effective_end_date      	DATE;
  l_tab_ovn 									NUMBER;
  l_col_ovn 									NUMBER;
  l_display_seq 							NUMBER;

  l_user_column_instance_id 	NUMBER;
  l_column_instance_ovn   		NUMBER;
  l_col_instance_eff_start_date DATE;
  l_col_instance_eff_end_date 	DATE;

  --
  -- Gets the UDT if already created
  --
  CURSOR csr_get_udt IS
  SELECT l_YES
  FROM   pay_user_tables udt
  WHERE  udt.user_table_name = l_UDT;

BEGIN

  --
  -- Check if the UDT already exists
  --
  OPEN  csr_get_udt;
  FETCH csr_get_udt INTO l_exists;
  CLOSE csr_get_udt;

  IF l_exists IS NOT NULL THEN
			-- Delete all user_column_instances
		delete from pay_user_column_instances_f where user_row_id in
			(select pur.user_row_id from pay_user_rows_f pur , pay_user_tables put
			 where put.user_table_name = l_UDT
			 and   put.user_table_id = pur.user_table_id);
		
		-- Delete all user_rows
		delete from pay_user_rows_f where user_row_id in
		 (select pur.user_row_id from pay_user_rows_f pur , pay_user_tables put
			where put.user_table_name = l_UDT
			and   put.user_table_id = pur.user_table_id);		 
		-- Delete all user_columns
		delete from pay_user_columns where user_column_id in 
			(select puc.user_column_id from pay_user_columns puc, pay_user_tables put
				where put.user_table_name = l_UDT
				and 	put.user_table_id = puc.user_table_id);
		--Delete user_table
		delete from pay_user_tables
		where user_table_name = l_UDT;
	
  END IF;

  --
  -- Create the UDT
  --
  HR_OWNER_DEFINITIONS_PKG.insert_row(l_row_id,userenv('SESSIONID'),'PAY');
	hr_startup_data_api_support.enable_startup_mode('GENERIC');

  pay_user_table_api.create_user_table(p_validate => false
                                      ,p_effective_date => l_EFF_DT
                                      ,p_range_or_match => 'M'
                                      ,p_user_key_units => 'T'
                                      ,p_user_table_name => l_UDT
                                      ,p_user_row_title => null
                                      ,p_user_table_id => l_table_id
																			,p_object_version_number => l_tab_ovn
                                      );
  --
  -- Create the Event Type UDT column
  --
  pay_user_column_api.create_user_column(p_validate        => false
																				,p_user_table_id   => l_table_id
																				,p_user_column_name=> 'TYPE'
																				,p_user_column_id  => l_column_id
																				,p_object_version_number => l_col_ovn
																				);  
 
 /*Rows - Values*/
 
 l_display_seq := 1;

  pay_user_row_api.create_user_row(p_validate									=> false
																	,p_effective_date 					=> l_EFF_DT
																	,p_user_table_id						=> l_table_id
																	,p_row_low_range_or_name		=> 'hours'
																	,p_display_sequence					=> l_display_seq
																	,p_user_row_id							=> l_user_row_id
																	,p_object_version_number		=> l_row_ovn
																	,p_effective_start_date			=> l_effective_start_date
																	,p_effective_end_date				=> l_effective_end_date);

	pay_user_column_instance_api.create_user_column_instance
																	(p_validate										=> false
																	,p_effective_date							=> l_EFF_DT
																	,p_user_row_id								=> l_user_row_id
																	,p_user_column_id							=> l_column_id
																	,p_value											=> 'select-multiple-array'
																	,p_user_column_instance_id		=> l_user_column_instance_id
																	,p_object_version_number			=> l_column_instance_ovn
																	,p_effective_start_date				=> l_col_instance_eff_start_date
																	,p_effective_end_date					=> l_col_instance_eff_end_date
																	);
																	
	l_display_seq := l_display_seq + 1;
  pay_user_row_api.create_user_row(p_validate									=> false
																	,p_effective_date 					=> l_EFF_DT
																	,p_user_table_id						=> l_table_id
																	,p_row_low_range_or_name		=> 'contractType'
																	,p_display_sequence					=> l_display_seq
																	,p_user_row_id							=> l_user_row_id
																	,p_object_version_number		=> l_row_ovn
																	,p_effective_start_date			=> l_effective_start_date
																	,p_effective_end_date				=> l_effective_end_date);
																	
	pay_user_column_instance_api.create_user_column_instance
																	(p_validate										=> false
																	,p_effective_date							=> l_EFF_DT
																	,p_user_row_id								=> l_user_row_id
																	,p_user_column_id							=> l_column_id
																	,p_value											=> 'select-multiple-array'
																	,p_user_column_instance_id		=> l_user_column_instance_id
																	,p_object_version_number			=> l_column_instance_ovn
																	,p_effective_start_date				=> l_col_instance_eff_start_date
																	,p_effective_end_date					=> l_col_instance_eff_end_date
																	);
	
	l_display_seq := l_display_seq + 1;
  pay_user_row_api.create_user_row(p_validate									=> false
																	,p_effective_date 					=> l_EFF_DT
																	,p_user_table_id						=> l_table_id
																	,p_row_low_range_or_name		=> 'salary'
																	,p_display_sequence					=> l_display_seq
																	,p_user_row_id							=> l_user_row_id
																	,p_object_version_number		=> l_row_ovn
																	,p_effective_start_date			=> l_effective_start_date
																	,p_effective_end_date				=> l_effective_end_date);

	pay_user_column_instance_api.create_user_column_instance
																	(p_validate										=> false
																	,p_effective_date							=> l_EFF_DT
																	,p_user_row_id								=> l_user_row_id
																	,p_user_column_id							=> l_column_id
																	,p_value											=> 'range'
																	,p_user_column_instance_id		=> l_user_column_instance_id
																	,p_object_version_number			=> l_column_instance_ovn
																	,p_effective_start_date				=> l_col_instance_eff_start_date
																	,p_effective_end_date					=> l_col_instance_eff_end_date
																	);
	
	l_display_seq := l_display_seq + 1;
  pay_user_row_api.create_user_row(p_validate									=> false
																	,p_effective_date 					=> l_EFF_DT
																	,p_user_table_id						=> l_table_id
																	,p_row_low_range_or_name		=> 'areaOfOrganization'
																	,p_display_sequence					=> l_display_seq
																	,p_user_row_id							=> l_user_row_id
																	,p_object_version_number		=> l_row_ovn
																	,p_effective_start_date			=> l_effective_start_date
																	,p_effective_end_date				=> l_effective_end_date);	

	pay_user_column_instance_api.create_user_column_instance
																	(p_validate										=> false
																	,p_effective_date							=> l_EFF_DT
																	,p_user_row_id								=> l_user_row_id
																	,p_user_column_id							=> l_column_id
																	,p_value											=> 'select-multiple'
																	,p_user_column_instance_id		=> l_user_column_instance_id
																	,p_object_version_number			=> l_column_instance_ovn
																	,p_effective_start_date				=> l_col_instance_eff_start_date
																	,p_effective_end_date					=> l_col_instance_eff_end_date
																	);
																	
  HR_OWNER_DEFINITIONS_PKG.delete_row(l_row_id);

END;
/
COMMIT;
EXIT;
