REM Program: noopt.sql

REM Author : David Wendelken

REM          Tactics, Inc.  (404) 248-1226

REM          

REM          Part of the Tactics' Designer/2000 Toolbox

REM Genesis: 15-Sep-1996

REM

REM Purpose: To list all optional attributes that do not have their

REM          "optional on condition" property set to some value.

REM          Optional attributes often have situations where they are

REM          mandatory and others where they are optional, so this

REM          report lists where we didn't specify those conditions.

REM

REM Notes  : Note the use of the "joining" table, ci_app_sys_entities.

REM          By using this table, we get a list of all entities used by

REM          the application whether they are owned by that application

REM          or not.  This is appropriate, because we want to know about

REM          specification failures for any attribute that we use.

REM          

REM          If we wanted to limit this quality check to entities that 

REM          are owned by the application, we would join ci_entities

REM          directly to ci_application_systems, leaving all reference

REM          to ci_app_sys_entities out of the SQL statement.

REM          This is particularly appropriate when we want to update

REM          records in the repository.

REM          

REM          This same principle works with the other primary access 

REM          control object types (such as tables, modules, etc.).

REM          



PROMPT Enter the Application System Name

DEFINE p_application_system_name='&1'

PROMPT Enter the Application System Version

DEFINE p_application_system_version='&2'

PROMPT Enter Entity Name or % for ALL

DEFINE p_entity_name='&3'

PROMPT Enter the output filename (without the .lis extension)

DEFINE p_filename='&4'



REM Let's not clutter up the screen and page with program code.

set verify   off

set echo     off



COLUMN app_name    FORMAT A10 WORD WRAP

COLUMN app_version FORMAT 99

COLUMN ent_name    FORMAT A30 WORD WRAP

COLUMN att_name    FORMAT A30 WORD WRAP



SPOOL &p_filename..lis







SELECT ci_apps.name    app_name

      ,ci_apps.version app_version

      ,ci_ent.name     ent_name

      ,ci_att.name     att_name

  FROM ci_entities             ci_ent

      ,ci_attributes           ci_att

      ,ci_app_sys_entities     ci_appse

      ,ci_application_systems  ci_apps

 WHERE ci_apps.id               = ci_appse.application_system_reference

   AND ci_apps.name             = UPPER('&p_application_system_name')

   AND ci_apps.version          = &p_application_system_version

   AND ci_ent.id                = ci_appse.entity_reference

   AND ci_ent.name           LIKE UPPER('&p_entity_name')

   AND ci_ent.id                = ci_att.entity_reference

   AND ci_att.option_condition IS NULL

   AND ci_att.optional_flag     = 'Y'

ORDER BY 1,2,3,4;



SPOOL OFF