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

SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUT ON

DECLARE

  l_patch_name varchar2(400) := '&1';
  l_sql        varchar2(400);
  l_patch      varchar2(400) := 'xxasemp%';
  l_cur_status varchar2(50) := 'C';
  l_status     varchar2(50) := 'X';
  l_host_name  varchar2(400) := '&2';

BEGIN

  IF l_host_name = 'null' THEN
    l_sql := ' SELECT distinct current_status '
        || ' FROM   xxas_com_applied_patches ap '
		    || ' WHERE  ap.patch_name like ''' || l_patch || ''''
		    || ' AND ap.patch_name <> ''' || l_patch_name || ''''
		    || ' AND ap.current_status = ''' || l_cur_status || '''';
  ELSE
    l_sql := ' SELECT distinct current_status '
        || ' FROM   xxas_com_applied_patches ap '
		    || ' WHERE  ap.patch_name like ''' || l_patch || ''''
		    || ' AND ap.patch_name <> ''' || l_patch_name || ''''
		    || ' AND ap.current_status = ''' || l_cur_status || ''''
		    || ' AND ap.node_name = ''' || l_host_name || '''';
  END IF;

  EXECUTE IMMEDIATE l_sql INTO l_status;

  dbms_output.put_line(l_status);

EXCEPTION

  WHEN others THEN
    --
    -- Table does not exist, invalid Oracle apps release
    --
    dbms_output.put_line(l_status);

END;
/
COMMIT;
EXIT;
