create or replace package odwa_qck is

  -- Author  : LJELLEMA
  -- Created : 30-04-2000
  -- Purpose : Dynamic Quality Checks

-- type to hold one row of the execute_query result
type t_qrt_rec is record
( id      number(38)
 ,app_id  number(38)
 ,name    varchar(200)
);

-- type to hold one row of the execute_quality_checks results
type t_qck_rec is record
( id            number(38)
 ,qck_label     varchar2(30)
 ,qck_name      varchar(200)
 ,qck_standard  varchar2(2000)
 ,qck_rationale varchar2(2000)
 ,result  boolean
);

type t_qrt_tbl is table of
t_qrt_rec
index by binary_integer
;

type t_qck_tbl is table of
t_qck_rec
index by binary_integer
;

g_qrt_tbl t_qrt_tbl; -- query result table, holds selected elements
g_qck_tbl t_qck_tbl; -- quality check table, holds selected quality checks

function has_qck
( p_el_type  in varchar2
, p_category in varchar2 default null -- when NULL, any type counts
) return boolean
;

procedure js_qck_change
( p_el_type     in varchar2
, p_app_name    in varchar2
, p_app_version in number
);

-- write to the HTP buffer HTML statements to create a Select List with all appropriate QCKs and Filters
-- for the indicated element type; the first one is ALL (returning ALL elements); if p_qck_id
-- has a value, it indicates the SELECTED quality check
procedure qck_list
( p_el_type in varchar2
, p_qck_id  in number default null
);

-- function indicates whether p_el_id is element in the current g_qrt_tbl
function element_in_qrt
( p_el_id in number
) return boolean
;

-- will execute a query to retrieve all elements of type p_el_type owned by or shared to
-- application system p_app_id that pass the filter set by p_qck_id
-- the result of the query is stored in g_qrt_tbl
procedure execute_query
( p_el_type in varchar2
, p_app_id  in number
, p_qck_id  in number
);


-- executes all quality checks for the indicated element
-- writes a HTML table with check results
procedure quality_checks
( p_el_type in varchar2
, p_el_id   in number
);

-- this procedure creates a new page with the complete Quality Check Report
-- for element p_el_id
procedure quality_report
 ( p_el_type          in varchar2
  ,p_el_id            in number
  ,p_static           in boolean default false
 );

procedure add_qck
( p_label         in varchar2
, p_el_type       in varchar2
, p_name          in varchar2
, p_standard      in varchar2
, p_rationale     in varchar2 default null
, p_sql_statement in varchar2
);

procedure add_filter
( p_label         in varchar2
, p_el_type       in varchar2
, p_name          in varchar2
, p_sql_statement in varchar2
);


/* examples of quality checks and filters to add:
begin
  odwa_qck.add_filter
  ( p_label         => 'ALL001'
  , p_el_type       => ''
  , p_name          => 'Changed in last 3 three days'
  , p_sql_statement => 'date_changed > sysdate - 3'
  );
  odwa_qck.add_qck
  ( p_label         => 'ENT001'
  , p_el_type       => 'ENT'
  , p_name          => 'Entities without Attributes'
  , p_standard      => 'Entities typically have attributes'
  , p_rationale     => 'Entities without Attributes can not be transformed to tables (they need columns based on attributes)'
  , p_sql_statement => 'not exists (select ''x'' from ci_attributes att '
                     ||' where att.entity_reference=ent.id)'
  );
  odwa_qck.add_qck
  ( p_label         => 'ENT002'
  , p_el_type       => 'ENT'
  , p_name          => 'Entities without Relationships'
  , p_standard      => 'Entities typically have relationships'
  , p_rationale     => ''
  , p_sql_statement => 'not exists (select ''x'' from ci_relationship_ends relend '
                     ||' where relend.from_entity_reference=ent.id)'
  );
  odwa_qck.add_qck
  ( p_label         => 'ENT003'
  , p_el_type       => 'ENT'
  , p_name          => 'Entities not mapped to a Table or View'
  , p_standard      => 'Entities eventually should be mapped to the Logical Database Design'
  , p_rationale     => ''
  , p_sql_statement => 'not exists (select ''x'' from ci_tables_entities tabent
                        where tabent.entity_reference=ent.id)'
  );
  odwa_qck.add_qck
  ( p_label         => 'ENT004'
  , p_el_type       => 'ENT'
  , p_name          => 'Entities with underscores in the Name, Shortname or Plural'
  , p_standard      => 'Underscores should not be used in Analysis'
  , p_rationale     => 'To avoid confusion between spaces or underscores, it is best to completely avoid underscores in Analysis; in Design, spaces are not allowed in names.'
  , p_sql_statement => 'instr(ent.name||ent.short_name||ent.plural, ''_'') > 0 '
  );
  odwa_qck.add_filter
  ( p_label         => 'ENT005'
  , p_el_type       => 'ENT'
  , p_name          => 'Entities that have Sub-Types'
  , p_sql_statement => 'exists (select ''x'' from ci_entities subent'
                     ||' where subent.supertype_reference = ent.id)'
  );
   odwa_qck.add_filter
    ( p_label         => 'ENT006'
    , p_el_type       => 'ENT'
    , p_name          => 'Entity name should be singular'
    , p_sql_statement => 'substr(ent.name, length(ent.name)) =''S'' '
  );
  odwa_qck.add_qck
  ( p_label         => 'TBL001'
  , p_el_type       => 'TBL'
  , p_name          => 'Tables without Primary Keys'
  , p_standard      => 'In Relational Database Theory, all Tables must have a Primary Key'
  , p_rationale     => 'Without Primary on the table, there is no clear way to refer to rows in the table.'
  , p_sql_statement => 'not exists (select ''x'' from ci_primary_key_constraints pco'
                     ||' where pco.table_reference = tbl.id)'
  );

  odwa_qck.add_qck
  ( p_label         => 'OMS-61613'
  , p_el_type       => 'ATT'
  , p_name          => ''
  , p_standard      => 'If an attribute has a default value, it should be a mandatory attribute.'
  , p_rationale     => 'A default value is always assigned if no value is explicitly assigned. Apparently
      it is not allowed to not assign a value, why else a default? To not annoy or confuse the user,
	  do not assign a default value for attributes that may be valueless: that are optional.'
  , p_sql_statement => 'att.optional_flag =''N'' and att.default_value is not null'
  );


end;

*/

end odwa_qck;
/