/*============================================================================*
 |                 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_status     varchar2(400) := 'X';
  l_node_type  varchar2(400) := '&2';
  l_host_name  varchar2(400) := '&3';
  
BEGIN

  --
  -- Retrieve the current patch status using dynamic SQL
  -- in case the table does not yet exist.
  --
  l_sql := ' SELECT distinct ap.current_status '
        || ' FROM   xxas_com_applied_patches ap '
		|| ' WHERE ap.session_id=(select max(a.session_id) from xxas_com_applied_patches a where a.patch_name = ''' || l_patch_name || ''') '
		|| ' AND ap.patch_name = ''' || l_patch_name || ''' '
		|| ' AND ap.node_type in (''master'',''slave'') '
		|| ' AND ap.node_name = ''' || l_host_name || '''';

  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;
