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

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

DECLARE

  l_session_id		  number 		:= 1;
  l_node_name		  varchar2(31)	:= '&1';
  l_node_type		  varchar2(30)	:= 'master';
  l_appltop_id		  number		:= &2;
  l_autoconfig_status varchar2(1)	:= 'N';
  l_tab_exist varchar2(3000);
  l_alter	varchar2(3000);

  CURSOR csr_get_existing IS
  SELECT COLUMN_NAME 
  FROM user_tab_columns 
  WHERE table_name='XXAS_COM_APPLIED_PATCHES' 
  AND column_name IN ('NODE_NAME');

BEGIN

  --
  -- Determine if columns are already resent
  --
  OPEN  csr_get_existing;
  FETCH csr_get_existing INTO l_tab_exist;
  CLOSE csr_get_existing;

  IF l_tab_exist IS NULL THEN
    --
    -- Alter table xxas_com_applied_patches to add new columns
    --
  l_alter := 'ALTER TABLE XXAS_COM_APPLIED_PATCHES ADD('
	|| ' session_id	number DEFAULT '''|| l_session_id ||''' NOT NULL '
	|| ' ,node_name	varchar2(31) DEFAULT '''|| l_node_name ||''' NOT NULL '
	|| ' ,node_type	varchar2(30) DEFAULT '''|| l_node_type ||''' NOT NULL '
	|| ' ,appltop_id	number DEFAULT '''|| l_appltop_id ||''' NOT NULL '
	|| ' ,autoconfig_status	varchar2(1) DEFAULT '''|| l_autoconfig_status ||''' NOT NULL) ' ;
	
	EXECUTE IMMEDIATE l_alter;
	
  END IF;
 
END;
/

COMMIT; 
EXIT;