CREATE VIEW dbo.al_search_security_view (
   search_id,
   title,
   description,
   category_id,
   contact_id,
   role_id,
   config_role_id,
   config_id,
   core,
   version_number,
   temporary,
   global,
   invalid)
AS
   
   /*
   *   Generated by SQL Server Migration Assistant for Oracle.
   *   Contact ora2sql@microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
   */
    (
      SELECT
         searchDef.SEARCH_ID AS search_id,
         searchDef.TITLE AS title,
         searchDef.DESCRIPTION AS description,
         searchDef.CATEGORY_ID AS category_id,
         linkcontact.CONTACT_ID AS contact_id,
         linkrole.ROLE_ID AS role_id,
         linkroleconfig.ROLE_ID/* NEW Role Id assigned to Config*/ AS config_role_id,
         linkconfig.CONFIG_ID/* Config Id not used for now, but may prove useful later*/ AS config_id,
         searchDef.CORE AS core,
         searchDef.VERSION_NUMBER AS version_number,
         searchDef.TEMPORARY AS TEMPORARY,
     config.GLOBAL AS global,
         CASE when linkrole.invalid = 1 then linkrole.invalid
         when linkconfig.invalid = 1 then linkconfig.invalid
         when linkcontact.invalid = 1 then linkcontact.invalid
         else 0
         end as invalid
      FROM
         dbo.AL_SEARCH_DEF  AS searchDef
            INNER JOIN /* join formdef to later join on bind_class*/dbo.AL_FORM_DEF  AS formdef
            ON formdef.SEARCH_ID = searchDef.SEARCH_ID
            LEFT OUTER JOIN /* join linkcontact to provide contact_id*/dbo.AL_LINK_SEARCH_USER  AS linkcontact
            ON linkcontact.SEARCH_ID = searchDef.SEARCH_ID
            LEFT OUTER JOIN /* join linkrole to provide role_id*/dbo.AL_LINK_SEARCH_ROLE  AS linkrole
            ON linkrole.SEARCH_ID = searchDef.SEARCH_ID
            LEFT OUTER JOIN /* join linkconfig to config to scheme to al_link_role_config to provide config_role_id*/dbo.AL_LINK_SEARCH_CONFIG  AS linkconfig
            ON linkconfig.SEARCH_ID = searchDef.SEARCH_ID
            LEFT OUTER JOIN dbo.AL_CONFIGURATION_DEF  AS config
            ON config.ID = linkconfig.CONFIG_ID
            LEFT OUTER JOIN dbo.AL_SCHEME_DEF  AS scheme
            ON scheme.SCHEME_ID = config.SCHEME_ID AND /* match search class to scheme class*/lower(scheme.CLASS_NAME) = lower(formdef.BIND_CLASS)
            LEFT OUTER JOIN dbo.AL_LINK_ROLE_CONFIG  AS linkroleconfig
            ON linkroleconfig.CONFIG_ID = linkconfig.CONFIG_ID
    )
GO