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

SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUT ON

DECLARE

	l_host_name 	varchar2(400) := '&1';
	l_sql 			varchar2(600);
	l_appltop_id	number		  := 0;
	
BEGIN
  --
  -- Retrieve the appltop_id for given host using dynamic SQL
  -- in case no datat.
  --
	l_sql :=	' select distinct appl_top_id '
			||	' from ad_appl_tops '
			||	' where name in (SELECT extractValue(XMLType(TEXT),''//APPL_TOP_NAME[@oa_var="s_atName"]'') '
			||	' from fnd_oam_context_files '
			||	' where name not in (''TEMPLATE'',''METADATA'') '
			||	' and (status is null or status !=''H'') '
			||	' and CTX_TYPE = ''A'' '
			||	' and node_name in (select host '
			||						' from fnd_nodes fn '
			||						' where fn.host is not null '
			||						' and (fn.support_cp=''Y'' and fn.support_forms=''Y'' and fn.support_web=''Y'' and fn.support_admin=''Y'') '
			||						' and fn.server_id is not NULL '
			||						' and host in (''' || l_host_name || '''))) '
			||	' and active_flag=''Y'' '; 

	EXECUTE IMMEDIATE l_sql INTO l_appltop_id;

	dbms_output.put_line(l_appltop_id);

EXCEPTION

  WHEN others THEN
    --
    -- returns no rows
    --
    dbms_output.put_line(l_appltop_id);

END;
/
COMMIT;
EXIT;