CREATE OR REPLACE PROCEDURE gen_mandatory IS
  TYPE t_pk_cols IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
  l_pk_cols       t_pk_cols;
  l_cursor_id     INTEGER;
  l_sql           dbms_sql.varchar2a;
  l_rows_inserted INTEGER;

  PROCEDURE add_sql(p_sqla IN OUT dbms_sql.varchar2a
                   ,p_sql  IN VARCHAR2) IS
    l_idx BINARY_INTEGER := p_sqla.count + 1;
  BEGIN
    p_sqla(l_idx) := p_sql;
  END add_sql;

BEGIN

  DELETE FROM data_item_roles;

  FOR l_enty IN (SELECT code
                 FROM   enquiry_types) LOOP
    BEGIN
      l_cursor_id := dbms_sql.open_cursor;
      l_sql.delete;
      add_sql(l_sql
             ,'INSERT INTO DATA_ITEM_ROLES');
      add_sql(l_sql
             ,'     (ENTY_CODE');
      add_sql(l_sql
             ,'     ,FIELD_NAME');
      add_sql(l_sql
             ,'     ,TABLE_NAME');
      add_sql(l_sql
             ,'     ,CONDITION');
      add_sql(l_sql
             ,'     ,DESCRIPTION');
      add_sql(l_sql
             ,'     ,DISPLAY_SEQUENCE');
      add_sql(l_sql
             ,'     )');
      add_sql(l_sql
             ,'(');
      add_sql(l_sql
             ,'SELECT ''' || l_enty.code || '''');
      add_sql(l_sql
             ,'      ,field_name');
      add_sql(l_sql
             ,'      ,''ENQUIRIES'' as table_name');
      add_sql(l_sql
             ,'       ,diri.' || REPLACE(l_enty.code
                                        ,' '
                                        ,'_'));
      add_sql(l_sql
             ,'      ,diri.description');
      add_sql(l_sql
             ,'      ,diri.display_sequence');
      add_sql(l_sql
             ,'  FROM ext_dataitem_roles diri');
      add_sql(l_sql
             ,' WHERE field_name IS NOT NULL AND substr(diri.' ||
              REPLACE(l_enty.code
                     ,' '
                     ,'_'));
      add_sql(l_sql
             ,'             ,1');
      add_sql(l_sql
             ,'             ,1) IN (''M'',''O'',''H'')');
      add_sql(l_sql
             ,')');
      --dbms_output.put_line(l_sql);
      dbms_sql.parse(l_cursor_id
                    ,l_sql
                    ,l_sql.first
                    ,l_sql.last
                    ,TRUE
                    ,dbms_sql.native);
      l_rows_inserted := dbms_sql.execute(l_cursor_id);
      dbms_sql.close_cursor(l_cursor_id);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
        dbms_sql.close_cursor(l_cursor_id);
    END;
  END LOOP;

  FOR l_tables IN (SELECT DISTINCT table_name AS table_name
                   FROM   data_item_roles) LOOP
    -- create a package, named MIP_'table'_HELPER
    -- include functions:
    --  'table'_check_mandatory - confirm whether all mandatory fields have been completed
    --  'table'_get_field_list - return a list of all the fields applicable to the enquiry type
  
    FOR l_rec IN (SELECT rownum
                        ,ucc.column_name
                  FROM   user_cons_columns ucc
                        ,user_constraints  uc
                  WHERE  uc.table_name = upper(l_tables.table_name)
                  AND    uc.constraint_type = 'P'
                  AND    ucc.constraint_name = uc.constraint_name
                  ORDER  BY ucc.position) LOOP
      l_pk_cols(l_rec.rownum) := l_rec.column_name;
    END LOOP;
  
    --
    -- START OF PACKAGE SPECIFICATION
    --
  
    l_sql.delete;
  
    add_sql(l_sql
           ,'CREATE OR REPLACE PACKAGE MIP_' || l_tables.table_name ||
            '_HELPER IS');
    add_sql(l_sql
           ,'  ');
    add_sql(l_sql
           ,' -- AUTOMATICALLY GENERATED BY GEN_MANDATORY AT ' ||
            TO_CHAR(SYSDATE
                   ,'DD-MON-YYYY HH24:MI:SS'));
    add_sql(l_sql
           ,'  ');
  
    add_sql(l_sql
           ,' SUBTYPE T_REC_' || l_tables.table_name || ' IS ' ||
            l_tables.table_name || '%ROWTYPE;');
    add_sql(l_sql
           ,'  ');
    --
    -- CHECK_MANDATORY
    --
    add_sql(l_sql
           ,'FUNCTION check_mandatory (p_rec in T_REC_' ||
            l_tables.table_name);
    add_sql(l_sql
           ,'   ,p_mandatory_checks OUT MIP_MANDATORY.T_MANDATORY_CHECKS)');
    add_sql(l_sql
           ,'   RETURN BOOLEAN;');
    add_sql(l_sql
           ,'  ');
  
    add_sql(l_sql
           ,'FUNCTION check_mandatory (');
  
    FOR l_idx IN l_pk_cols.first .. l_pk_cols.last LOOP
      IF l_idx != l_pk_cols.first THEN
        add_sql(l_sql
               ,'   ,P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      ELSE
        add_sql(l_sql
               ,'   P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      END IF;
    END LOOP;
  
    add_sql(l_sql
           ,'   ,p_mandatory_checks OUT MIP_MANDATORY.T_MANDATORY_CHECKS)');
    add_sql(l_sql
           ,'   RETURN BOOLEAN;');
    add_sql(l_sql
           ,'  ');
  
    --
    -- GET_FIELD_LIST
    --
    add_sql(l_sql
           ,'FUNCTION get_field_list (p_enty_code IN ' ||
            l_tables.table_name || '.' || 'ENTY_CODE%TYPE)');
    add_sql(l_sql
           ,'   RETURN MIP_MANDATORY.T_FIELDS;');
  
    add_sql(l_sql
           ,'   ');
    --
    -- RECORD_EXISTS
    --
    add_sql(l_sql
           ,'FUNCTION record_exists (');
  
    FOR l_idx IN l_pk_cols.first .. l_pk_cols.last LOOP
      IF l_idx != l_pk_cols.first THEN
        add_sql(l_sql
               ,'   ,P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      ELSE
        add_sql(l_sql
               ,'   P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      END IF;
    END LOOP;
  
    add_sql(l_sql
           ,'   )');
    add_sql(l_sql
           ,'   RETURN BOOLEAN;');
    add_sql(l_sql
           ,'  ');
    add_sql(l_sql
           ,'FUNCTION check_condition (p_enty_code in enquiries.enty_code%type');
    add_sql(l_sql
           ,'   ,p_field_name IN data_item_roles.field_name%type)');
    add_sql(l_sql
           ,'   RETURN data_item_roles.condition%TYPE;');
    add_sql(l_sql
           ,'  ');
    add_sql(l_sql
           ,'FUNCTION label_condition (p_enty_code in enquiries.enty_code%type');
    add_sql(l_sql
           ,'   ,p_field_name IN data_item_roles.field_name%type)');
    add_sql(l_sql
           ,'   RETURN VARCHAR2;');
    add_sql(l_sql
           ,'  ');
    add_sql(l_sql
           ,'PROCEDURE purge_fields (');
  
    FOR l_idx IN l_pk_cols.first .. l_pk_cols.last LOOP
      IF l_idx != l_pk_cols.first THEN
        add_sql(l_sql
               ,'   ,P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      ELSE
        add_sql(l_sql
               ,'   P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      END IF;
    END LOOP;
    add_sql(l_sql
           ,'   );');
    add_sql(l_sql
           ,'  ');
    add_sql(l_sql
           ,'END MIP_' || l_tables.table_name || '_HELPER;');
  
    l_cursor_id := dbms_sql.open_cursor;
    dbms_sql.parse(l_cursor_id
                  ,l_sql
                  ,l_sql.first
                  ,l_sql.last
                  ,TRUE
                  ,dbms_sql.native);
    l_rows_inserted := dbms_sql.execute(l_cursor_id);
    dbms_sql.close_cursor(l_cursor_id);
  
    --
    -- END OF PACKAGE SPECIFICATION
    --
  
    --
    -- START OF PACKAGE BODY
    --
    l_sql.delete;
    add_sql(l_sql
           ,'CREATE OR REPLACE PACKAGE BODY MIP_' || l_tables.table_name ||
            '_HELPER IS');
    add_sql(l_sql
           ,'  ');
    add_sql(l_sql
           ,' -- AUTOMATICALLY GENERATED BY GEN_MANDATORY AT ' ||
            TO_CHAR(SYSDATE
                   ,'DD-MON-YYYY HH24:MI:SS'));
    add_sql(l_sql
           ,'  ');
  
    --
    -- CHECK_MANDATORY
    --
    add_sql(l_sql
           ,'FUNCTION check_mandatory (p_rec in T_REC_' ||
            l_tables.table_name);
    add_sql(l_sql
           ,'   ,p_mandatory_checks OUT MIP_MANDATORY.T_MANDATORY_CHECKS)');
    add_sql(l_sql
           ,'   RETURN BOOLEAN');
    add_sql(l_sql
           ,'IS');
    add_sql(l_sql
           ,'   l_mandatory_checks MIP_MANDATORY.T_MANDATORY_CHECKS;');
    add_sql(l_sql
           ,'   l_return BOOLEAN;');
  
    add_sql(l_sql
           ,'   ');
  
    add_sql(l_sql
           ,'BEGIN');
  
    add_sql(l_sql
           ,'  cout_assert.isnotnull(p_rec.id,''NULL enquiry id'');');
    add_sql(l_sql
           ,'  cout_assert.isnotnull(p_rec.enty_code,''NULL enquiry ENTY_CODE'');');
    FOR l_checks IN (SELECT field_name
                           ,SUBSTR(condition
                                  ,1
                                  ,1) AS condition
                           ,enty_code
                           ,error_message
                     FROM   data_item_roles) LOOP
    
      IF l_checks.condition = 'M' THEN
        add_sql(l_sql
               ,'    IF p_rec.ENTY_CODE = ''' || l_checks.enty_code ||
                ''' AND p_rec.' || l_checks.field_name || ' IS NULL THEN');
        add_sql(l_sql
               ,'      MIP_MANDATORY.add_error(P_mandatory_checks=>l_mandatory_checks, P_field_name=>''' ||
                l_checks.field_name || ''' ,p_error_message=>''' ||
                nvl(l_checks.error_message
                   ,'Mandatory Field') || ''');');
        add_sql(l_sql
               ,'    END IF;');
        add_sql(l_sql
               ,'   ');
      END IF;
    END LOOP;
  
    add_sql(l_sql
           ,'   ');
  
    add_sql(l_sql
           ,'   MIP_HELPER_SPECIAL_CASES.table_' || l_tables.table_name ||
            '(p_rec => p_rec, p_mandatory_checks=>l_mandatory_checks);');
  
    add_sql(l_sql
           ,'   ');
    add_sql(l_sql
           ,'   l_return := NOT(l_mandatory_checks.COUNT > 0);');
    add_sql(l_sql
           ,'   P_mandatory_checks := l_mandatory_checks;');
    add_sql(l_sql
           ,'   ');
    -- return the overall boolean response
    add_sql(l_sql
           ,'   RETURN l_return;');
    add_sql(l_sql
           ,'END check_mandatory;');
    add_sql(l_sql
           ,'   ');
  
    --
    -- Check_mandatory - second version
    --
    add_sql(l_sql
           ,'FUNCTION check_mandatory (');
  
    FOR l_idx IN l_pk_cols.first .. l_pk_cols.last LOOP
      IF l_idx != l_pk_cols.first THEN
        add_sql(l_sql
               ,'   ,P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      ELSE
        add_sql(l_sql
               ,'   P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      END IF;
    END LOOP;
  
    add_sql(l_sql
           ,'   ,p_mandatory_checks OUT MIP_MANDATORY.T_MANDATORY_CHECKS)');
    add_sql(l_sql
           ,'   RETURN BOOLEAN');
    add_sql(l_sql
           ,'IS');
    add_sql(l_sql
           ,'   l_rec T_REC_' || l_tables.table_name || ';');
    add_sql(l_sql
           ,'BEGIN');
  
    -- get the record
    add_sql(l_sql
           ,'   SELECT * INTO l_rec');
    add_sql(l_sql
           ,'     FROM ' || l_tables.table_name);
  
    FOR l_idx IN l_pk_cols.first .. l_pk_cols.last LOOP
      IF l_idx != l_pk_cols.first THEN
        add_sql(l_sql
               ,'     AND ' || l_pk_cols(l_idx) || ' = P_' ||
                l_pk_cols(l_idx));
      ELSE
        add_sql(l_sql
               ,'   WHERE ' || l_pk_cols(l_idx) || ' = P_' ||
                l_pk_cols(l_idx));
      END IF;
    END LOOP;
  
    add_sql(l_sql
           ,'   ;');
  
    add_sql(l_sql
           ,' RETURN CHECK_MANDATORY(p_rec => l_rec, p_mandatory_checks => p_mandatory_checks);');
    add_sql(l_sql
           ,' ');
    add_sql(l_sql
           ,'END CHECK_MANDATORY;');
    add_sql(l_sql
           ,' ');
    --
    -- GET_FIELD_LIST
    --
    add_sql(l_sql
           ,'FUNCTION get_field_list (p_enty_code IN ' ||
            l_tables.table_name || '.' || 'ENTY_CODE%TYPE)');
    add_sql(l_sql
           ,'   RETURN MIP_MANDATORY.T_FIELDS');
    add_sql(l_sql
           ,'IS');
    add_sql(l_sql
           ,'   l_fields MIP_MANDATORY.T_FIELDS;');
    add_sql(l_sql
           ,'BEGIN');
  
    add_sql(l_sql
           ,'   ');
  
    add_sql(l_sql
           ,'     FOR l_checks IN (SELECT field_name');
    add_sql(l_sql
           ,'                            ,substr(condition,1,1) as condition');
    add_sql(l_sql
           ,'                            ,enty_code');
    add_sql(l_sql
           ,'                            ,error_message');
    add_sql(l_sql
           ,'                        FROM data_item_roles');
    add_sql(l_sql
           ,'                        WHERE enty_code = P_enty_code');
    add_sql(l_sql
           ,'                        ) LOOP');
    add_sql(l_sql
           ,'        MIP_MANDATORY.add_field(p_fields=>l_fields,p_field_name=>l_checks.field_name, p_field_condition=>l_checks.condition);');
    add_sql(l_sql
           ,'     END LOOP;');
  
    add_sql(l_sql
           ,'   ');
  
    add_sql(l_sql
           ,'   RETURN l_fields;');
    add_sql(l_sql
           ,'END get_field_list;');
  
    add_sql(l_sql
           ,'FUNCTION record_exists (');
  
    FOR l_idx IN l_pk_cols.first .. l_pk_cols.last LOOP
      IF l_idx != l_pk_cols.first THEN
        add_sql(l_sql
               ,'   ,P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      ELSE
        add_sql(l_sql
               ,'   P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      END IF;
    END LOOP;
  
    add_sql(l_sql
           ,'   )');
    add_sql(l_sql
           ,'   RETURN BOOLEAN');
    add_sql(l_sql
           ,'IS  ');
    add_sql(l_sql
           ,'l_rec ' || l_tables.table_name || '%ROWTYPE;');
    add_sql(l_sql
           ,'BEGIN ');
  
    -- get the record
    add_sql(l_sql
           ,'   SELECT * INTO l_rec');
    add_sql(l_sql
           ,'     FROM ' || l_tables.table_name);
  
    FOR l_idx IN l_pk_cols.first .. l_pk_cols.last LOOP
      IF l_idx != l_pk_cols.first THEN
        add_sql(l_sql
               ,'     AND ' || l_pk_cols(l_idx) || ' = P_' ||
                l_pk_cols(l_idx));
      ELSE
        add_sql(l_sql
               ,'   WHERE ' || l_pk_cols(l_idx) || ' = P_' ||
                l_pk_cols(l_idx));
      END IF;
    END LOOP;
  
    add_sql(l_sql
           ,'   ;');
  
    add_sql(l_sql
           ,'   RETURN TRUE;');
    add_sql(l_sql
           ,'   ');
    add_sql(l_sql
           ,'EXCEPTION');
    add_sql(l_sql
           ,'   WHEN NO_DATA_FOUND THEN');
    add_sql(l_sql
           ,'   RETURN FALSE;');
    add_sql(l_sql
           ,'   ');
    add_sql(l_sql
           ,'END record_exists;');
    add_sql(l_sql
           ,'   ');
  
    --
    -- CHECK_CONDITION
    --
    add_sql(l_sql
           ,'FUNCTION check_condition (p_enty_code in enquiries.enty_code%type');
    add_sql(l_sql
           ,'   ,p_field_name IN data_item_roles.field_name%type)');
    add_sql(l_sql
           ,'   RETURN data_item_roles.condition%TYPE');
    add_sql(l_sql
           ,'IS');
    add_sql(l_sql
           ,'   ');
    add_sql(l_sql
           ,'BEGIN');
    add_sql(l_sql
           ,'  cout_assert.isnotnull(p_field_name,''NULL enquiry FIELD_NAME'');');
  
    FOR l_checks IN (SELECT upper(field_name) AS field_name
                           ,SUBSTR(condition
                                  ,1
                                  ,2) AS condition
                           ,enty_code
                           ,error_message
                     FROM   data_item_roles) LOOP
    
      add_sql(l_sql
             ,'    IF p_enty_code = ''' || l_checks.enty_code ||
              ''' AND p_field_name = ''' || l_checks.field_name ||
              ''' THEN');
      add_sql(l_sql
             ,'      RETURN (''' || l_checks.condition || ''');');
      add_sql(l_sql
             ,'    END IF;');
      add_sql(l_sql
             ,'   ');
    END LOOP;
  
    add_sql(l_sql
           ,'   ');
    -- return the overall response
    add_sql(l_sql
           ,'   RETURN NULL;');
    add_sql(l_sql
           ,'END check_condition;');
  
    add_sql(l_sql
           ,'   ');
  
    add_sql(l_sql
           ,'FUNCTION label_condition (p_enty_code in enquiries.enty_code%type');
    add_sql(l_sql
           ,'   ,p_field_name IN data_item_roles.field_name%type)');
    add_sql(l_sql
           ,'   RETURN VARCHAR2');
    add_sql(l_sql
           ,'IS');
    add_sql(l_sql
           ,'BEGIN');
    add_sql(l_sql
           ,'  RETURN (');
    add_sql(l_sql
           ,'          CASE mip_enquiries_helper.check_condition(p_enty_code, p_field_name)');
    add_sql(l_sql
           ,'            WHEN ''M'' THEN ''*''');
    add_sql(l_sql
           ,'            WHEN ''O-'' THEN ''+''');
    add_sql(l_sql
           ,'            WHEN ''O'' THEN '' ''');
    add_sql(l_sql
           ,'            WHEN ''H'' THEN ''H''');
    add_sql(l_sql
           ,'           END');
    add_sql(l_sql
           ,'          );');
    add_sql(l_sql
           ,'END label_condition;');
  
    add_sql(l_sql
           ,'   ');
  
    add_sql(l_sql
           ,'PROCEDURE purge_fields (');
  
    FOR l_idx IN l_pk_cols.first .. l_pk_cols.last LOOP
      IF l_idx != l_pk_cols.first THEN
        add_sql(l_sql
               ,'   ,P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      ELSE
        add_sql(l_sql
               ,'   P_' || l_pk_cols(l_idx) || ' IN ' ||
                l_tables.table_name || '.' || l_pk_cols(l_idx) || '%TYPE');
      
      END IF;
    END LOOP;
    add_sql(l_sql
           ,'   ) IS');
  
    add_sql(l_sql
           ,'BEGIN');
    add_sql(l_sql
           ,'   NULL;');
  
    FOR l_rec_update IN (SELECT upper(field_name) AS field_name
                               ,enty_code
                         FROM   data_item_roles
                         WHERE  condition = 'H'
                         AND    table_name = l_tables.table_name) LOOP
      add_sql(l_sql
             ,'   UPDATE ' || l_tables.table_name);
      add_sql(l_sql
             ,'      SET ' || l_rec_update.field_name || ' = NULL');
    
      add_sql(l_sql
             ,'    WHERE ENTY_CODE = ''' || l_rec_update.enty_code||'''');
      FOR l_idx IN l_pk_cols.first .. l_pk_cols.last LOOP
        add_sql(l_sql
               ,'   AND ' || l_pk_cols(l_idx) || ' = P_' ||
                l_pk_cols(l_idx));
      END LOOP;
      add_sql(l_sql
             ,'   ;');
    END LOOP;
  
    add_sql(l_sql
           ,'END purge_fields;');
    add_sql(l_sql
           ,'END MIP_' || l_tables.table_name || '_HELPER;');
  
    --
    -- END OF PACKAGE BODY
    --
  
    l_cursor_id := dbms_sql.open_cursor;
    dbms_sql.parse(l_cursor_id
                  ,l_sql
                  ,l_sql.first
                  ,l_sql.last
                  ,TRUE
                  ,dbms_sql.native);
    l_rows_inserted := dbms_sql.execute(l_cursor_id);
    dbms_sql.close_cursor(l_cursor_id);
  
  END LOOP;
END gen_mandatory;
/