CREATE OR REPLACE PACKAGE mip_enquiry IS

  -- Author  : PRIESTJ
  -- Created : 15/11/2007 14:18:24
  -- Purpose : Provides useful admin functions/procedures for enquiries
  -- Updates : 21 November 2007 - MM - added copy_enquiry function
  --

  TYPE t_rec_meter_reqs IS RECORD(
     existing_metr_code meters.code%TYPE
    ,qmax               meters.qmax%TYPE
    ,existing_mesc_code meters.mesc_code%TYPE
    ,existing_mety_code meters.mety_code%TYPE
    ,existing_prty_id   meters.prty_id%TYPE
    ,required_mesc_code meters.mesc_code%TYPE);

  -- Public function and procedure declarations
  FUNCTION enquiry_has_quotes(p_enquiryid IN NUMBER) RETURN BOOLEAN;

  FUNCTION can_enquiry_be_deleted(p_enquiryid IN NUMBER) RETURN BOOLEAN;

  FUNCTION get_enquiry_status(p_enquiryid IN NUMBER) RETURN VARCHAR2;

  FUNCTION get_enquiry_role(p_enquiryid IN NUMBER
                           ,p_rolecode  IN VARCHAR2) RETURN NUMBER;
  FUNCTION set_enquiry_role(p_enquiryid IN NUMBER
                           ,p_partyid   IN NUMBER
                           ,p_rolecode  IN VARCHAR2) RETURN BOOLEAN;

  /*
   FUNCTION set_enquiry_role
   - gets the enquiry id, party id and the role type and writes a record in the enquiry_roles table
   - allowing you to assign agents, suppliers etc. to an enquiry.
   %param p_enquiryid - the current enquiry to save the role against.
   %param p_partyid - the user or party to assign to this role.
   %param p_rolecode - what kind of role this will be.
   %param p_description - a description for the enquiry role
  */
  FUNCTION set_enquiry_role(p_enquiryid   IN NUMBER
                           ,p_partyid     IN NUMBER
                           ,p_rolecode    IN VARCHAR2
                           ,p_description IN VARCHAR2) RETURN BOOLEAN;
  --
  FUNCTION set_enquiry_event(p_enquiryid IN NUMBER
                            ,p_eventcode IN VARCHAR2) RETURN BOOLEAN;

  /*
    FUNCTION copy_enquiry
      copies the specified enquiry.
  
    %param p_enquiry_id - the id of the current enquiry to copy.
    %param p_enqu_owner - the ID  of the enquiry owner
    %param p_enqu_supp  - the ID  of the enquiry supplier
    %return varchar2 - a success or error message.
  
    -- NOTE: there is no need to copy any quote information.  All quotes will be
    -- regenerated for the new enquiry when submitted.
  */
  FUNCTION copy_enquiry(p_enquiry_id enquiries.id%TYPE
                       ,p_enqu_owner parties.id%TYPE
                       ,p_enqu_supp  parties.id%TYPE) RETURN VARCHAR2;
  --

  FUNCTION calc_meter_reqs(p_enty_code          enquiry_types.code%TYPE
                          ,p_existing_mesc_code meters.mesc_code%TYPE
                          ,p_required_mesc_code meters.mesc_code%TYPE
                          ,p_existing_metr_code meters.code%TYPE
                          ,p_existing_mety_code meters.mety_code%TYPE
                          ,p_existing_prty_id   meters.prty_id%TYPE
                          ,p_qmax               meters.qmax%TYPE)
    RETURN t_rec_meter_reqs;

  FUNCTION show_qmax(p_enty_code          enquiry_types.code%TYPE
                    ,p_existing_mesc_code meters.mesc_code%TYPE
                    ,p_required_mesc_code meters.mesc_code%TYPE
                    ,p_existing_metr_code meters.code%TYPE) RETURN BOOLEAN;

  /*
   FUNCTION delete_enquiry
   - This function returns the deletion status of the enquiry provided
  %param p_enqu_id id of the enquiry to be deleted
  %param p_message reason for not deleting enquiry
  %return boolean
  {*} TRUE - all quotes have been deleted
  {*} FALSE or UNKNOWN - quotes have not been deleted
  */
  FUNCTION delete_enquiry(p_enqu_id IN enquiries.id%TYPE
                         ,p_message OUT VARCHAR2) RETURN BOOLEAN;

  /*
   FUNCTION delete_enquiry
   - This function returns the deletion status of the enquiry provided
  %param p_enqu_id id of the enquiry to be deleted
  %return reason for not deleting enquiry or NULL
  */
  FUNCTION delete_enquiry(p_enqu_id IN enquiries.id%TYPE) RETURN VARCHAR2;
END mip_enquiry;
/
CREATE OR REPLACE PACKAGE BODY mip_enquiry IS
  PROCEDURE pl(p_in   VARCHAR2
              ,p_line IN NUMBER DEFAULT NULL) IS
  BEGIN
    NULL;
    /*  $IF mip_debug_constants.debugging OR mip_debug_constants.enquiry
    $THEN*/
    mip_debug.pl(p_unit => $$PLSQL_UNIT
                ,p_line => p_line
                ,p_in   => p_in);
    /*  $END*/
  END pl;
  /*
   FUNCTION enquiry_has_quotes
   - This function returns true if the enquiry has quotes
   %param p_enquiryid - the enquiry we want to find quotes from.
   %return boolean - true if the enquiry has quotes
  */
  FUNCTION enquiry_has_quotes(p_enquiryid IN NUMBER) RETURN BOOLEAN AS
    -- cursor to get the current enquiry
    CURSOR c_count_all_quotes(cp_enqu_id NUMBER) IS
      SELECT COUNT(id)
        FROM quotes
       WHERE enqu_id = cp_enqu_id;
    l_quotes_num NUMBER := 0;
  BEGIN
    IF NOT c_count_all_quotes%ISOPEN THEN
      OPEN c_count_all_quotes(p_enquiryid);
    END IF;
  
    FETCH c_count_all_quotes
      INTO l_quotes_num;
    CLOSE c_count_all_quotes;
    IF l_quotes_num > 0 THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END enquiry_has_quotes;
  /*
   FUNCTION can_enquiry_be_deleted
   - This function returns the status of the enquiry's
   - associated quotes to see if it can be deleted
   %param p_enquiryid - the enquiry we want to find the role from.
   %return boolean - true if the enquiry can be deleted
  */
  FUNCTION can_enquiry_be_deleted(p_enquiryid IN NUMBER) RETURN BOOLEAN AS
    -- cursor to get the current enquiry
    CURSOR c_check_all_quotes(cp_enqu_id NUMBER) IS
      SELECT COUNT(qute_id)
        FROM v_current_quote_status
       WHERE qute_id IN (SELECT id
                           FROM quotes
                          WHERE enqu_id = cp_enqu_id)
         AND qust_code IN ('SELECTED', 'ACCEPTED');
    l_quotes_cannot_del NUMBER := 0;
  BEGIN
    IF NOT c_check_all_quotes%ISOPEN THEN
      OPEN c_check_all_quotes(p_enquiryid);
    END IF;
  
    FETCH c_check_all_quotes
      INTO l_quotes_cannot_del;
    CLOSE c_check_all_quotes;
    IF l_quotes_cannot_del > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END can_enquiry_be_deleted;

  /*
   FUNCTION delete_enquiry
   - This function returns the status of the enquiry provided
   %param p_enquiryid - the enquiry we want to delete.
   %return boolean - whether the enquiry was deleted or not
  */
  FUNCTION delete_enquiry(p_enqu_id IN enquiries.id%TYPE
                         ,p_message OUT VARCHAR2) RETURN BOOLEAN IS
    l_enquiry_deleted BOOLEAN := TRUE;
    TYPE t_rowid IS TABLE OF ROWID;
    l_rowid t_rowid := t_rowid();
  BEGIN
    pl('delete_enquiry:entry:' || p_enqu_id
      ,$$PLSQL_LINE);
    --
    -- attempt to delete all associated quotes
    --
    IF NOT
        mip_quotation.delete_quotes_for_enquiry(p_enqu_id => p_enqu_id
                                               ,p_message => p_message) THEN
      -- unable to delete quotations
      l_enquiry_deleted := FALSE;
    ELSE
      -- 
      -- delete all associations with this enquiry
      --
    
      DELETE FROM apex_application_files aaf
       WHERE aaf.NAME IN (SELECT uri
                            FROM documents      docu
                                ,document_roles doro
                           WHERE doro.enqu_id = p_enqu_id
                             AND doro.docu_id = docu.id
                             AND docu.docu_type = 'INDO');
    
      DELETE FROM document_events doev
       WHERE doev.docu_id IN
             (SELECT docu_id
                FROM document_roles doro
               WHERE doro.enqu_id = p_enqu_id);
    
      -- document roles knows which files should be deleted
      -- through a FK
      -- 1. Gather the rowids of the documents first,
      -- 2. Remove the document role
      -- 3. Remove the associated document
      SELECT ROWID BULK COLLECT
        INTO l_rowid
        FROM documents
       WHERE id IN (SELECT docu_id
                      FROM document_roles
                     WHERE enqu_id = p_enqu_id);
    
      DELETE FROM document_roles doro
       WHERE doro.enqu_id = p_enqu_id;
    
      FORALL l_idx IN INDICES OF l_rowid
        DELETE FROM documents
         WHERE ROWID = l_rowid(l_idx);
    
      DELETE FROM quote_reasoning
       WHERE enqu_id = p_enqu_id;
    
      DELETE FROM enquiry_events
       WHERE enqu_id = p_enqu_id;
    
      DELETE FROM enquiry_roles
       WHERE enqu_id = p_enqu_id;
    
      DELETE FROM enquiries
       WHERE id = p_enqu_id;
    
    END IF;
  
    RETURN(l_enquiry_deleted = TRUE);
  END delete_enquiry;

  FUNCTION delete_enquiry(p_enqu_id IN enquiries.id%TYPE) RETURN VARCHAR2 IS
    l_message VARCHAR2(240);
    l_dummy   BOOLEAN;
  BEGIN
    pl('delete_enquiry(msg):entry:' || p_enqu_id);
  
    l_dummy := delete_enquiry(p_enqu_id => p_enqu_id
                             ,p_message => l_message);
  
    pl('delete_enquiry(msg):exit:' || l_message);
    RETURN(l_message);
  END delete_enquiry;
  /*
   FUNCTION get_enquiry_status
   - This function returns the status of the enquiry provided
   %param p_enquiryid - the enquiry we want to find the role from.
   %return varchar2 - the current status of the enquiry
  */
  FUNCTION get_enquiry_status(p_enquiryid IN NUMBER) RETURN VARCHAR2 AS
    -- cursor to get the current enquiry
    CURSOR c_get_enquiry_status(cp_enqu_id NUMBER) IS
      SELECT enst_code
        FROM v_current_enquiry_status
       WHERE enqu_id = cp_enqu_id;
    l_enqu_status VARCHAR2(80);
  BEGIN
    IF NOT c_get_enquiry_status%ISOPEN THEN
      OPEN c_get_enquiry_status(p_enquiryid);
    END IF;
  
    FETCH c_get_enquiry_status
      INTO l_enqu_status;
    CLOSE c_get_enquiry_status;
    RETURN nvl(l_enqu_status
              ,'');
  END get_enquiry_status;
  /*
   FUNCTION get_enquiry_role
   - This function finds the enquiry and the role specified and returns the
   - latest(current) user id for the specifed role
   %param p_enquiryid - the enquiry we want to find the role from.
   %param p_rolecode - the type of role we want to find
   %return number - the party id of the party that currently assumes the specified role
  */
  FUNCTION get_enquiry_role(p_enquiryid IN NUMBER
                           ,p_rolecode  IN VARCHAR2) RETURN NUMBER AS
    -- cursor to get the current enquiry
    CURSOR c_get_enquiry_role(cp_enqu_id NUMBER, cp_rolecode VARCHAR2) IS
      SELECT prty_id
        FROM v_latest_rt_code_for_enro
       WHERE rt_code = cp_rolecode
         AND enqu_id = cp_enqu_id;
    l_enqu_role_row NUMBER;
  BEGIN
    IF NOT c_get_enquiry_role%ISOPEN THEN
      OPEN c_get_enquiry_role(p_enquiryid
                             ,p_rolecode);
    END IF;
  
    FETCH c_get_enquiry_role
      INTO l_enqu_role_row;
    CLOSE c_get_enquiry_role;
    RETURN l_enqu_role_row;
  END get_enquiry_role;
  /*
   FUNCTION set_enquiry_role
   - gets the enquiry id, party id and the role type and writes a record in the enquiry_roles table
   - allowing you to assign agents, suppliers etc. to an enquiry.
   %param p_enquiryid - the current enquiry to save the role against.
   %param p_partyid - the user or party to assign to this role.
   %param p_rolecode - what kind of role this will be.
  */
  FUNCTION set_enquiry_role(p_enquiryid IN NUMBER
                           ,p_partyid   IN NUMBER
                           ,p_rolecode  IN VARCHAR2) RETURN BOOLEAN AS
  BEGIN
    pl('set_enquiry_role:entry:' || p_enquiryid || ':' || p_partyid || ':' ||
       p_rolecode
      ,$$PLSQL_LINE);
    INSERT INTO enquiry_roles
      (enqu_id
      ,prty_id
      ,start_date
      ,rt_code)
    VALUES
      (p_enquiryid
      ,p_partyid
      ,SYSDATE
      ,p_rolecode);
  
    pl('set_enquiry_role:exit'
      ,$$PLSQL_LINE);
    RETURN TRUE;
    --
  EXCEPTION
    WHEN OTHERS THEN
      pl('set_enquiry_role:EXCEPTION:' || SQLERRM
        ,$$PLSQL_LINE);
      RAISE;
  END set_enquiry_role;
  --

  /*
   FUNCTION set_enquiry_role
   - gets the enquiry id, party id and the role type and writes a record in the enquiry_roles table
   - allowing you to assign agents, suppliers etc. to an enquiry.
   %param p_enquiryid - the current enquiry to save the role against.
   %param p_partyid - the user or party to assign to this role.
   %param p_rolecode - what kind of role this will be.
   %param p_description - a description for the enquiry role
  */
  FUNCTION set_enquiry_role(p_enquiryid   IN NUMBER
                           ,p_partyid     IN NUMBER
                           ,p_rolecode    IN VARCHAR2
                           ,p_description IN VARCHAR2) RETURN BOOLEAN AS
  BEGIN
    pl('set_enquiry_role:entry:' || p_enquiryid || ':' || p_partyid || ':' ||
       p_rolecode || ':' || p_description
      ,$$PLSQL_LINE);
  
    INSERT INTO enquiry_roles
      (enqu_id
      ,prty_id
      ,start_date
      ,rt_code
      ,description)
    VALUES
      (p_enquiryid
      ,p_partyid
      ,SYSDATE
      ,p_rolecode
      ,p_description);
  
    pl('set_enquiry_role:exit'
      ,$$PLSQL_LINE);
  
    RETURN TRUE;
    --
  EXCEPTION
    WHEN OTHERS THEN
      pl('set_enquiry_role:EXCEPTION:' || SQLERRM
        ,$$PLSQL_LINE);
      RAISE;
  END set_enquiry_role;
  --
  /*
   FUNCTION set_enquiry_event
   - gets the enquiry id and the event type and writes a record in the enquiry_roles table
   - allowing you to assign in progress, submitted for quotation etc. to an enquiry.
   %param p_enquiryid - the current enquiry to save the event role against.
   %param p_eventcode - what kind of event role this will be.
  */
  FUNCTION set_enquiry_event(p_enquiryid IN NUMBER
                            ,p_eventcode IN VARCHAR2) RETURN BOOLEAN AS
  BEGIN
  
    INSERT INTO enquiry_events
      (enqu_id
      ,event_date
      ,enst_code
      ,id)
    VALUES
      (p_enquiryid
      ,SYSDATE
      ,p_eventcode
      ,enev_seq.NEXTVAL);
  
    RETURN TRUE;
    --
    -- EXCEPTION
    --   WHEN OTHERS THEN
    --     RETURN FALSE;
    --     RAISE;
  END set_enquiry_event;
  --

  /*
    FUNCTION copy_enquiry
      copies the specified enquiry.
  
    %param p_enquiry_id - the id of the current enquiry to copy.
    %param p_enqu_owner - the ID  of the enquiry owner
    %param p_enqu_supp  - the ID  of the enquiry supplier
    %return varchar2 - a success or error message.
  
    -- NOTE: there is no need to copy any quote information.  All quotes will be
    -- regenerated for the new enquiry when submitted.
  */
  FUNCTION copy_enquiry(p_enquiry_id enquiries.id%TYPE
                       ,p_enqu_owner parties.id%TYPE
                       ,p_enqu_supp  parties.id%TYPE) RETURN VARCHAR2 IS
    --
    -- cursor to get a new enquiry ID from the sequence
    CURSOR c_get_new_id IS
      SELECT enqu_seq.NEXTVAL
        FROM dual;
  
    -- cursor to get the current enquiry
    CURSOR c_get_enquiry IS
      SELECT *
        FROM enquiries
       WHERE id = p_enquiry_id;
  
    -- variable to hold the enquiry details returned
    l_enqu_row enquiries%ROWTYPE;
  
    -- the new enquiry ID
    l_enqu_id NUMBER;
  
    --
    l_dummy BOOLEAN;
    --
  BEGIN
  
    -- first thing to do is check the user's role allows them to copy enquiries
    -- only agents and I&C users.
    IF NOT mip_parties.get_user_role(p_username => v('APP_USER')) IN
        ('AGENT', 'ICU', 'MIPADMIN') THEN
      raise_application_error(-20100
                             ,'User ' || v('APP_USER') ||
                              ' is not permitted to copy enquiries.');
    END IF;
  
    --
    --  try to get the enquiry based on the ID we've been given.
    --
    BEGIN
      IF NOT c_get_enquiry%ISOPEN THEN
        OPEN c_get_enquiry;
      END IF;
    
      FETCH c_get_enquiry
        INTO l_enqu_row;
    
      CLOSE c_get_enquiry;
    
    EXCEPTION
      WHEN no_data_found THEN
        CLOSE c_get_enquiry;
        -- couldn't find the enquiry, tell someone
        raise_application_error(-20100
                               ,'Unable to find the specified enquiry.  Please try again.');
    END;
    --
  
    -- get a new enquiry ID.
    BEGIN
      OPEN c_get_new_id;
      FETCH c_get_new_id
        INTO l_enqu_id;
      CLOSE c_get_new_id;
    EXCEPTION
      WHEN no_data_found THEN
        CLOSE c_get_new_id;
        raise_application_error(-20101
                               ,'Unable to retrieve a new ID for the enquiry.');
    END;
  
    l_enqu_row.id := l_enqu_id;
  
    -- now do the insert (copy) for the enquiry
    INSERT INTO enquiries
    VALUES l_enqu_row;
    --
  
    -- create an enquiry supplier, add a description of where it was copied from
    l_dummy := set_enquiry_role(p_enquiryid   => l_enqu_id
                               ,p_partyid     => p_enqu_supp
                               ,p_rolecode    => 'ENQ SUPP'
                               ,p_description => 'Enquiry copied from enquiry ' ||
                                                 p_enquiry_id || ' by user ' ||
                                                 v(':APP_USER'));
    IF NOT l_dummy THEN
      raise_application_error(-20102
                             ,'Unable to set enquiry supplier.');
    END IF;
  
    -- create an enquiry owner
    l_dummy := set_enquiry_role(p_enquiryid => l_enqu_id
                               ,p_partyid   => p_enqu_owner
                               ,p_rolecode  => 'ENQ OWN');
    IF NOT l_dummy THEN
      raise_application_error(-20103
                             ,'Unable to set enquiry owner.');
    END IF;
    --
  
    -- set the enquiry event to "In Progress"
    l_dummy := set_enquiry_event(p_enquiryid => l_enqu_id
                                ,p_eventcode => 'INP');
  
    IF NOT l_dummy THEN
      raise_application_error(-20104
                             ,'Unable to set enquiry owner.');
    END IF;
    --
  
    --
    RETURN l_enqu_id;
    --
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RETURN SQLERRM;
  END copy_enquiry;
  --
  FUNCTION calc_meter_reqs(p_enty_code          enquiry_types.code%TYPE
                          ,p_existing_mesc_code meters.mesc_code%TYPE
                          ,p_required_mesc_code meters.mesc_code%TYPE
                          ,p_existing_metr_code meters.code%TYPE
                          ,p_existing_mety_code meters.mety_code%TYPE
                          ,p_existing_prty_id   meters.prty_id%TYPE
                          ,p_qmax               meters.qmax%TYPE)
    RETURN t_rec_meter_reqs IS
    l_dummy                  NUMBER;
    l_existing_meter_enquiry BOOLEAN;
    l_required_meter_enquiry BOOLEAN;
    l_rec_meter_reqs         t_rec_meter_reqs;
  BEGIN
    pl('calc_meter_reqs:entry:' || p_enty_code || ':' ||
       p_existing_mesc_code || ':' || p_required_mesc_code || ':' ||
       p_existing_metr_code || ':' || p_qmax);
    -- is the enquiry one that uses an existing meter?
    BEGIN
      SELECT 1
        INTO l_dummy
        FROM data_item_roles
       WHERE field_name = 'EXISTING_METR_CODE'
         AND condition <> 'H'
         AND enty_code = p_enty_code;
    
      l_existing_meter_enquiry := TRUE;
    EXCEPTION
      WHEN no_data_found THEN
        l_existing_meter_enquiry := FALSE;
    END;
  
    BEGIN
      SELECT 1
        INTO l_dummy
        FROM data_item_roles
       WHERE field_name = 'QMAX'
         AND condition <> 'H'
         AND enty_code = p_enty_code;
    
      l_required_meter_enquiry := TRUE;
    EXCEPTION
      WHEN no_data_found THEN
        l_required_meter_enquiry := FALSE;
    END;
  
    -- Consider existing meter
    IF NOT l_existing_meter_enquiry THEN
      l_rec_meter_reqs.existing_metr_code := NULL;
      l_rec_meter_reqs.existing_mesc_code := NULL;
      l_rec_meter_reqs.existing_mety_code := NULL;
    ELSE
      -- Existing Meter Query
      -- if we have an existing meter model use the associated details
      IF nvl(p_existing_metr_code
            ,'OTHER') <> 'OTHER' THEN
        SELECT code
              ,CASE substr(mesc_code
                      ,1
                      ,1)
                 WHEN 'U' THEN
                  mesc_code
                 ELSE
                  'OTHER'
               END
              ,mety_code
              ,prty_id
          INTO l_rec_meter_reqs.existing_metr_code
              ,l_rec_meter_reqs.existing_mesc_code
              ,l_rec_meter_reqs.existing_mety_code
              ,l_rec_meter_reqs.existing_prty_id
          FROM meters
         WHERE code = p_existing_metr_code;
      ELSE
        -- we don't have real model details, chose the 'OTHER' meter
        SELECT prty_id
          INTO l_rec_meter_reqs.existing_prty_id
          FROM meters
         WHERE code = 'OTHER';
        l_rec_meter_reqs.existing_metr_code := 'OTHER';
        l_rec_meter_reqs.existing_mety_code := p_existing_mety_code;
        l_rec_meter_reqs.existing_mesc_code := p_existing_mesc_code;
        l_rec_meter_reqs.existing_prty_id   := p_existing_prty_id;
      END IF;
    END IF;
  
    -- consider required meter
    IF NOT l_required_meter_enquiry THEN
      l_rec_meter_reqs.qmax               := NULL;
      l_rec_meter_reqs.required_mesc_code := NULL;
    ELSE
      l_rec_meter_reqs.required_mesc_code := p_required_mesc_code;
      IF nvl(l_rec_meter_reqs.required_mesc_code
            ,'OTHER') <> 'OTHER' THEN
        l_rec_meter_reqs.qmax := round(mip_quotation.get_qmax_from_mesc(p_mesc_code => l_rec_meter_reqs.required_mesc_code)
                                      ,3);
      ELSE
        l_rec_meter_reqs.required_mesc_code := nvl(p_required_mesc_code
                                                  ,'OTHER');
        l_rec_meter_reqs.qmax               := p_qmax;
      END IF;
    END IF;
  
    pl('calc_meter_reqs:exit:' || l_rec_meter_reqs.existing_mesc_code || ':' ||
       l_rec_meter_reqs.required_mesc_code || ':' ||
       l_rec_meter_reqs.existing_metr_code || ':' || l_rec_meter_reqs.qmax);
  
    RETURN l_rec_meter_reqs;
  
  END calc_meter_reqs;

  FUNCTION show_qmax(p_enty_code          enquiry_types.code%TYPE
                    ,p_existing_mesc_code meters.mesc_code%TYPE
                    ,p_required_mesc_code meters.mesc_code%TYPE
                    ,p_existing_metr_code meters.code%TYPE) RETURN BOOLEAN IS
    l_return        BOOLEAN := TRUE;
    l_existing_qmax BOOLEAN;
    l_required_qmax BOOLEAN;
    l_dummy         NUMBER;
  BEGIN
  
    pl('show_qmax:entry:' || p_enty_code || ':' || p_existing_metr_code || ':' ||
       p_existing_mesc_code || ':' || p_required_mesc_code
      ,$$PLSQL_LINE);
  
    IF p_enty_code IS NOT NULL THEN
      -- is Qmax set to be hidden?
      BEGIN
        SELECT NULL
          INTO l_dummy
          FROM data_item_roles
         WHERE enty_code = p_enty_code
           AND field_name = 'QMAX'
           AND substr(condition
                     ,1
                     ,1) <> 'H';
      
        l_return := TRUE;
      EXCEPTION
        WHEN no_data_found THEN
          pl('show_qmax:QMAX IS HIDDEN'
            ,$$PLSQL_LINE);
          l_return := FALSE;
      END;
    
      IF l_return THEN
        IF p_enty_code IN
           ('OFMAT', 'REMOVE', 'STD REMOVE', 'ADVERSARIAL', 'ADDON') THEN
          l_existing_qmax := TRUE;
        ELSE
          l_required_qmax := TRUE;
        END IF;
      
        IF l_existing_qmax THEN
          IF (nvl(p_existing_metr_code
                 ,'OTHER') <> 'OTHER') THEN
            pl('show_qmax:EXISTING_METR_CODE SPECIFIED');
            l_return := FALSE;
          ELSIF
          
           (nvl(p_existing_metr_code
               ,'OTHER') = 'OTHER' AND
           nvl(p_existing_mesc_code
               ,'OTHER') <> 'OTHER') THEN
            pl('show_qmax:EXISTING_MESC SPECIFIED'
              ,$$PLSQL_LINE);
            l_return := FALSE;
          END IF;
        END IF;
      
        IF l_required_qmax
           AND (nvl(p_required_mesc_code
                   ,'OTHER') <> 'OTHER') THEN
          pl('show_qmax:REQUIRED_MESC SPECIFIED'
            ,$$PLSQL_LINE);
          l_return := FALSE;
        
        END IF;
      END IF;
    END IF;
  
    pl('show_qmax:exit:' || CASE l_return WHEN TRUE THEN 'TRUE' ELSE
       'FALSE' END
      ,$$PLSQL_LINE);
    RETURN l_return;
  
  END show_qmax;
END mip_enquiry;
/
