CREATE OR REPLACE PACKAGE mip_quotation IS

  -- Author  : HARDYA
  -- Created : 15/11/2007 11:27:58
  -- Purpose : Handle life-cycle of quotations

  TYPE t_tab_messages IS TABLE OF VARCHAR2(240);

  /** Determines whether the given enquiry is ready to quote for
  i.e. have all the mandatory fields been completed
  
   %param p_enqu_id the id of the enquiry to be checked
   %return TRUE if the enquiry can be quoted for
   */
  FUNCTION ready_for_quote(p_enqu_id IN enquiries.id%TYPE) RETURN BOOLEAN;

  /** Determines whether the given enquiry is ready to quote for
  i.e. have all the mandatory fields been completed
  
   %param p_enqu_id the id of the enquiry to be checked
   %p_mandatory_checks contains reasons for the enquiry *not* being ready to quote for
   %p_quote_is_ready TRUE if the enquiry can be quoted for
   */
  PROCEDURE ready_for_quote(p_enqu_id          IN enquiries.id%TYPE
                           ,p_mandatory_checks OUT mip_mandatory.t_mandatory_checks
                           ,p_enquiry_is_ready OUT BOOLEAN);

  /** Validate an enquiry row of date for mandatory fields
  Store the results for later retrieval (must be same transaction)
  
  %param p_rec enquiry data to be validated
  */
  PROCEDURE validate(p_rec IN mip_enquiries_helper.t_rec_enquiries);

  /** Retrieve the results of a mandatory field validation for the given field
  Must be performed in the same transaction as the validate
  
  %param p_field_name enquiry field who validation message is required
  %return validation error message or NULL if validation was successful
  */
  FUNCTION validation_result(p_enqu_id    IN enquiries.id%TYPE
                            ,p_field_name IN VARCHAR2) RETURN VARCHAR2;

  /** Generate quotes in response to a 'request for quote' against an enquiry
  
  %param p_enqu_id the id of the enquiry to be checked
  %param p_prty_id the id of the party that requested the quotes
  %param p_owner_prty_id the id of party that owns the quotes (defaults to the requestor)
  */
  PROCEDURE produce_quotes(p_enqu_id       IN enquiries.id%TYPE
                          ,p_rfq_prty_id   IN parties.id%TYPE
                          ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL);

  FUNCTION return_mandatory_messages(p_enqu_id IN enquiries.id%TYPE)
    RETURN VARCHAR2;

  /** Make quote available
    %param p_qute_id id of the quote to be marked as available
  */
  PROCEDURE make_quote_available(p_qute_id IN quotes.id%TYPE);
  /** Accept a quote
  %param p_qute_id id of the quote to be accepted
  %param p_prty_id id of the person accepting the quote
  %param p_owner_prty_id id of the person who owns the accepted quote (defaults to acceptor)
  %param p_description optional description to be recorded with the event
  %param p_event_date optional date for this event (defaults to now)
  */
  PROCEDURE accept_quote(p_qute_id       IN quotes.id%TYPE
                        ,p_prty_id       IN parties.id%TYPE
                        ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL
                        ,p_description   quote_events.description%TYPE DEFAULT NULL
                        ,p_event_date    IN DATE DEFAULT SYSDATE);
  /** Reject a quote
  %param p_qute_id id of the quote to be rejected
  %param p_prty_id id of the person rejecting the quote
  %param p_owner_prty_id id of the person who owns the rejected quote (defaults to rejector)
  %param p_description optional description to be recorded with the event
  %param p_event_date optional date for this event (defaults to now)
  */
  PROCEDURE reject_quote(p_qute_id       IN quotes.id%TYPE
                        ,p_prty_id       IN parties.id%TYPE
                        ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL
                        ,p_description   quote_events.description%TYPE DEFAULT NULL
                        ,p_event_date    IN DATE DEFAULT SYSDATE);

  /** Reject all quotes associated with enquiry
  %param p_enqu_id id of the enquiry for qhich all quotes are to be rejected
  %param p_prty_id id of the person rejecting the quotes
  %param p_owner_prty_id id of the person who owns the rejected quotes (defaults to rejector)
  %param p_description optional description to be recorded with the event
  %param p_event_date optional date for this event (defaults to now)
  */
  PROCEDURE reject_all_quotes(p_enqu_id       IN enquiries.id%TYPE
                             ,p_prty_id       IN parties.id%TYPE
                             ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL
                             ,p_description   quote_events.description%TYPE DEFAULT NULL
                             ,p_event_date    IN DATE DEFAULT SYSDATE);
  /** Select a quote for detailed quotation
  %param p_qute_id id of the quote to be selected
  %param p_prty_id id of the person selecting the quote
  %param p_owner_prty_id id of the person who owns the selected quote (defaults to selector)
  %param p_description optional description to be recorded with the event
  %param p_event_date optional date for this event (defaults to now)
  */
  PROCEDURE select_quote(p_qute_id       IN quotes.id%TYPE
                        ,p_prty_id       IN parties.id%TYPE
                        ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL
                        ,p_description   quote_events.description%TYPE DEFAULT NULL
                        ,p_event_date    IN DATE DEFAULT SYSDATE);
  /** Lapse a quote
  %param p_qute_id id of the quote to be selected
  %param p_description optional description to be recorded with the event
  %param p_event_date optional date for this event (defaults to now)
  */
  PROCEDURE lapse_quote(p_qute_id     IN quotes.id%TYPE
                       ,p_description quote_events.description%TYPE DEFAULT NULL
                       ,p_event_date  IN DATE DEFAULT SYSDATE);

  PROCEDURE lapse_quotes_job;

  /** Delete a quote
  %param p_qute_id id of the quote to be deleted
  %param p_message reason for not deleting quote
  %return boolean
  {*} TRUE - quote has been deleted
  {*} FALSE or UNKNOWN - quote has not been deleted
  */
  FUNCTION delete_quote(p_qute_id IN quotes.id%TYPE
                       ,p_message OUT VARCHAR2) RETURN BOOLEAN;

  /** Delete a quote
  %param p_qute_id id of the quote to be deleted
  %return reason for not deleting quote
  */
  FUNCTION delete_quote(p_qute_id IN quotes.id%TYPE) RETURN VARCHAR2;

  /** Delete all quotes associated with an enquiry
  %param p_enqu_id id of the quote to be deleted
  %param p_message reason for not deleting quote
  %return boolean
  {*} TRUE - all quotes have been deleted
  {*} FALSE or UNKNOWN - quotes have not been deleted
  */
  FUNCTION delete_quotes_for_enquiry(p_enqu_id IN enquiries.id%TYPE
                                    ,p_message OUT VARCHAR2) RETURN BOOLEAN;

  /** Return a message if the Tripartite agreement is broached
  %param p_rec record containing enquiry details to be checked
  %return message is the agreement was broached
  */
  FUNCTION tripartite_agreement_message(p_rec IN mip_enquiries_helper.t_rec_enquiries)
    RETURN VARCHAR2;

  FUNCTION get_qmax_from_mesc(p_mesc_code IN meter_size_codes.code%TYPE)
    RETURN meter_size_codes.qmax%TYPE;
END mip_quotation;
/
CREATE OR REPLACE PACKAGE BODY mip_quotation IS

  PROCEDURE email_agent_q_sel(p_enqu_id IN enquiries.id%TYPE);
  PROCEDURE email_agent_quote_acceptance(p_enqu_id enquiries.id%TYPE);
  PROCEDURE email_agent_mq_available(p_enqu_id enquiries.id%TYPE);

  PROCEDURE email_iu_quote_acceptance(p_enqu_id enquiries.id%TYPE);

  PROCEDURE email_support(p_subject IN VARCHAR2
                         ,p_enqu_id IN enquiries.id%TYPE);

  SUBTYPE t_internal_or_external IS VARCHAR2(8);
  gc_internal_reason CONSTANT t_internal_or_external := 'INTERNAL';
  gc_external_reason CONSTANT t_internal_or_external := 'EXTERNAL';

  SUBTYPE t_manual_or_automatic_quote IS VARCHAR2(2);
  gc_manual_quote    CONSTANT t_manual_or_automatic_quote := 'MQ';
  gc_automatic_quote CONSTANT t_manual_or_automatic_quote := 'AQ';

  g_email_plain_body CLOB;
  g_email_html_body  CLOB;
  gc_newline CONSTANT CHAR(2) DEFAULT chr(10) || chr(13);

  SUBTYPE t_enqu IS enquiries%ROWTYPE;

  SUBTYPE t_email_reason IS VARCHAR2(80);

  gc_agent_mq_req_notification   t_email_reason := 'AGENT_MQ_REQ_NOTIFICATION';
  gc_agent_q_sel_notification    t_email_reason := 'AGENT_Q_SEL_NOTIFICATION';
  gc_agent_q_acc_notification    t_email_reason := 'AGENT_Q_ACC_NOTIFICATION';
  gc_agent_mq_avail_notification t_email_reason := 'AGENT_MQ_AVAIL_NOTIFICATION';
  gc_int_q_acc_notification      t_email_reason := 'INTERNAL_Q_ACC_NOTIFICATION';
  gc_int_mq_req_notification     t_email_reason := 'AGENT_MQ_REQ_NOTIFICATION';

  TYPE t_rec_additional_costs IS RECORD(
     adit_code     additional_items.code%TYPE
    ,svcpt_code    service_pressure_types.code%TYPE
    ,lead_time     additional_items.lead_time%TYPE
    ,selling_price costs.selling_price%TYPE
    ,cost_price    costs.cost_price%TYPE
    ,delivery_cost costs.delivery_cost%TYPE);

  SUBTYPE t_quote_row IS quotes%ROWTYPE;

  TYPE t_rec_attachment IS RECORD(
     content   wwv_flow_files.blob_content%TYPE := empty_blob
    ,filename  wwv_flow_files.filename%TYPE
    ,mime_type wwv_flow_files.mime_type%TYPE);

  TYPE t_rec_metr_details IS RECORD(
     code      meters.code%TYPE
    ,mesc_code meters.mesc_code%TYPE
    ,mety_code meters.mety_code%TYPE
    ,prty_id   meters.prty_id%TYPE);

  PROCEDURE pl(p_in   VARCHAR2
              ,p_line IN NUMBER DEFAULT NULL) IS
  BEGIN
    $IF mip_debug_constants.debugging OR mip_debug_constants.quotation $THEN mip_debug.pl(p_unit => $$PLSQL_UNIT
                                                                                         ,p_line => p_line
                                                                                         ,p_in   => p_in);
    $END NULL;
  END pl;

  PROCEDURE add_quote_reason(p_enqu_id              IN enquiries.id%TYPE
                            ,p_reason               IN VARCHAR2 --quote_reasoning.reason%TYPE
                            ,p_internal_or_external IN quote_reasoning.internal_or_external%TYPE) IS
  BEGIN
    pl('add_quote_reason:' || p_enqu_id || ':' || p_reason
      ,$$PLSQL_LINE);
    BEGIN
      INSERT INTO quote_reasoning
        (enqu_id
        ,reason
        ,internal_or_external
        ,id)
      VALUES
        (p_enqu_id
        ,substr(p_reason
               ,1
               ,239)
        ,p_internal_or_external
        ,qure_seq.NEXTVAL);
    EXCEPTION
      WHEN OTHERS THEN
        pl('add_quote_reason:' || SQLERRM
          ,$$PLSQL_LINE);
        RAISE;
    END;
  END add_quote_reason;

  PROCEDURE add_quote_event(p_qute_id     IN quotes.id%TYPE
                           ,p_qust_code   quote_statuses.code%TYPE
                           ,p_description quote_events.description%TYPE DEFAULT NULL
                           ,p_event_date  DATE DEFAULT SYSDATE) IS
  BEGIN
    pl('add_quote_event:entry:' || p_qute_id || ':' || p_qust_code
      ,$$PLSQL_LINE);
    INSERT INTO quote_events
      (qute_id
      ,qust_code
      ,event_date
      ,description
      ,id)
    VALUES
      (p_qute_id
      ,p_qust_code
      ,p_event_date
      ,p_description
      ,quev_seq.NEXTVAL);
    pl('add_quote_event:exit'
      ,$$PLSQL_LINE);
  END add_quote_event;

  PROCEDURE add_enquiry_event(p_enqu_id     IN enquiries.id%TYPE
                             ,p_enst_code   enquiry_status_types.code%TYPE
                             ,p_description enquiry_events.description%TYPE DEFAULT NULL
                             ,p_event_date  DATE DEFAULT SYSDATE) IS
  BEGIN
    INSERT INTO enquiry_events
      (enqu_id
      ,enst_code
      ,event_date
      ,description
      ,id)
    VALUES
      (p_enqu_id
      ,p_enst_code
      ,p_event_date
      ,p_description
      ,enev_seq.NEXTVAL);
  END add_enquiry_event;

  PROCEDURE add_quote_role(p_qute_id     IN quotes.id%TYPE
                          ,p_prty_id     IN parties.id%TYPE
                          ,p_rt_code     quote_roles.rt_code%TYPE
                          ,p_description quote_events.description%TYPE DEFAULT NULL
                          ,p_start_date  DATE DEFAULT SYSDATE) IS
  BEGIN
    INSERT INTO quote_roles
      (qute_id
      ,prty_id
      ,rt_code
      ,start_date
      ,description)
    VALUES
      (p_qute_id
      ,p_prty_id
      ,p_rt_code
      ,p_start_date
      ,p_description);
  END add_quote_role;

  PROCEDURE accept_quote(p_qute_id       IN quotes.id%TYPE
                        ,p_prty_id       IN parties.id%TYPE
                        ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL
                        ,p_description   quote_events.description%TYPE DEFAULT NULL
                        ,p_event_date    IN DATE DEFAULT SYSDATE) IS
    l_enqu_id enquiries.id%TYPE;
  BEGIN
    add_quote_event(p_qute_id     => p_qute_id
                   ,p_qust_code   => 'ACCEPTED'
                   ,p_event_date  => p_event_date
                   ,p_description => p_description);
  
    add_quote_role(p_qute_id    => p_qute_id
                  ,p_prty_id    => nvl(p_owner_prty_id
                                      ,p_prty_id)
                  ,p_rt_code    => 'Q ACC'
                  ,p_start_date => p_event_date);
  
    add_quote_role(p_qute_id    => p_qute_id
                  ,p_prty_id    => p_prty_id
                  ,p_rt_code    => 'Q SUBACC'
                  ,p_start_date => p_event_date);
  
    SELECT enqu_id
      INTO l_enqu_id
      FROM quotes
     WHERE id = p_qute_id;
  
    email_agent_quote_acceptance(p_enqu_id => l_enqu_id);
    email_iu_quote_acceptance(p_enqu_id => l_enqu_id);
  
  END accept_quote;

  PROCEDURE reject_quote(p_qute_id       IN quotes.id%TYPE
                        ,p_prty_id       IN parties.id%TYPE
                        ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL
                        ,p_description   quote_events.description%TYPE DEFAULT NULL
                        ,p_event_date    IN DATE DEFAULT SYSDATE) IS
  BEGIN
    add_quote_event(p_qute_id     => p_qute_id
                   ,p_qust_code   => 'REJECTED'
                   ,p_event_date  => p_event_date
                   ,p_description => p_description);
  
    add_quote_role(p_qute_id    => p_qute_id
                  ,p_prty_id    => nvl(p_owner_prty_id
                                      ,p_prty_id)
                  ,p_rt_code    => 'Q REJ'
                  ,p_start_date => p_event_date);
  
    add_quote_role(p_qute_id    => p_qute_id
                  ,p_prty_id    => p_prty_id
                  ,p_rt_code    => 'Q SUBREJ'
                  ,p_start_date => p_event_date);
  
  END reject_quote;

  PROCEDURE lapse_quote(p_qute_id     IN quotes.id%TYPE
                       ,p_description quote_events.description%TYPE DEFAULT NULL
                       ,p_event_date  IN DATE DEFAULT SYSDATE) IS
  BEGIN
    add_quote_event(p_qute_id     => p_qute_id
                   ,p_qust_code   => 'LAPSED'
                   ,p_event_date  => p_event_date
                   ,p_description => p_description);
  END lapse_quote;

  PROCEDURE reject_all_quotes(p_enqu_id       IN enquiries.id%TYPE
                             ,p_prty_id       IN parties.id%TYPE
                             ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL
                             ,p_description   quote_events.description%TYPE DEFAULT NULL
                             ,p_event_date    IN DATE DEFAULT SYSDATE) IS
  BEGIN
    FOR l_rec IN (SELECT id
                    FROM quotes
                   WHERE enqu_id = p_enqu_id) LOOP
    
      reject_quote(p_qute_id       => l_rec.id
                  ,p_prty_id       => p_prty_id
                  ,p_owner_prty_id => p_owner_prty_id
                  ,p_event_date    => p_event_date
                  ,p_description   => p_description);
    
    END LOOP;
  END reject_all_quotes;

  PROCEDURE select_quote(p_qute_id       IN quotes.id%TYPE
                        ,p_prty_id       IN parties.id%TYPE
                        ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL
                        ,p_description   quote_events.description%TYPE DEFAULT NULL
                        ,p_event_date    IN DATE DEFAULT SYSDATE) IS
    l_enqu_id enquiries.id%TYPE;
  BEGIN
    pl('select_quote:entry:' || p_qute_id
      ,$$PLSQL_LINE);
    add_quote_event(p_qute_id     => p_qute_id
                   ,p_qust_code   => 'SELECTED'
                   ,p_event_date  => p_event_date
                   ,p_description => p_description);
  
    add_quote_role(p_qute_id     => p_qute_id
                  ,p_prty_id     => nvl(p_owner_prty_id
                                       ,p_prty_id)
                  ,p_rt_code     => 'Q SEL'
                  ,p_description => p_description
                  ,p_start_date  => p_event_date);
  
    FOR l_rec IN (SELECT id
                    FROM quotes
                   WHERE enqu_id = (SELECT enqu_id
                                      FROM quotes
                                     WHERE id = p_qute_id)
                     AND id <> p_qute_id) LOOP
      add_quote_event(p_qute_id    => l_rec.id
                     ,p_qust_code  => 'SELREJ'
                     ,p_event_date => p_event_date);
    END LOOP;
  
    SELECT enqu_id
      INTO l_enqu_id
      FROM quotes
     WHERE id = p_qute_id;
  
    email_agent_q_sel(l_enqu_id);
  
  END select_quote;

  FUNCTION delete_quote(p_qute_id IN quotes.id%TYPE
                       ,p_message OUT VARCHAR2) RETURN BOOLEAN IS
    l_qust_code     quote_statuses.code%TYPE;
    l_quote_deleted BOOLEAN;
    l_enqu_id       enquiries.id%TYPE;
    TYPE t_rowid IS TABLE OF ROWID;
    l_rowid t_rowid := t_rowid();
  BEGIN
    pl('delete_quote:entry:' || p_qute_id
      ,$$PLSQL_LINE);
  
    BEGIN
      SELECT enqu_id
        INTO l_enqu_id
        FROM quotes
       WHERE id = p_qute_id;
    EXCEPTION
      WHEN no_data_found THEN
        NULL;
    END;
  
    cout_assert.isnotnull(p_value   => l_enqu_id
                         ,p_message => 'Unable to find quote ' || p_qute_id);
  
    SELECT qust_code
      INTO l_qust_code
      FROM v_current_quote_status
     WHERE qute_id = p_qute_id;
  
    IF l_qust_code IN ('SELECTED', 'ACCEPTED') THEN
      l_quote_deleted := FALSE;
      p_message       := 'Unable to delete quote ' || p_qute_id ||
                         ' as it has a status of ' || initcap(l_qust_code);
    ELSE
      -- 
      -- delete all associations with this quote
      --
    
      DELETE FROM quote_events
       WHERE qute_id = p_qute_id;
    
      DELETE FROM apex_application_files aaf
       WHERE aaf.NAME IN (SELECT uri
                            FROM documents      docu
                                ,document_roles doro
                           WHERE doro.qute_id = p_qute_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.qute_id = p_qute_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 qute_id = p_qute_id);
    
      DELETE FROM document_roles doro
       WHERE doro.qute_id = p_qute_id;
    
      FORALL l_idx IN INDICES OF l_rowid
        DELETE FROM documents
         WHERE ROWID = l_rowid(l_idx);
    
      DELETE FROM quote_items
       WHERE qute_id = p_qute_id;
    
      DELETE FROM quote_roles
       WHERE qute_id = p_qute_id;
    
      -- 
      -- record that the quote was deleted
      --
      add_quote_reason(p_enqu_id              => l_enqu_id
                      ,p_reason               => 'QUOTE ' || p_qute_id ||
                                                 ' DELETED BY REQUEST'
                      ,p_internal_or_external => gc_internal_reason);
    
      -- 
      -- delete the quote itself
      --
      DELETE FROM quotes
       WHERE id = p_qute_id;
      l_quote_deleted := TRUE;
    END IF;
  
    pl('delete_quote:exit:' || p_qute_id || ':' || CASE l_quote_deleted WHEN TRUE THEN
       'TRUE' ELSE 'FALSE' END
      ,$$PLSQL_LINE);
  
    RETURN(l_quote_deleted = TRUE);
  EXCEPTION
    WHEN OTHERS THEN
      cout_err.report_and_stop;
  END delete_quote;

  FUNCTION delete_quote(p_qute_id IN quotes.id%TYPE) RETURN VARCHAR2 IS
    l_dummy   BOOLEAN;
    l_message VARCHAR2(240);
  BEGIN
    pl('delete_quote(msg):entry:' || p_qute_id);
  
    l_dummy := delete_quote(p_qute_id => p_qute_id
                           ,p_message => l_message);
  
    pl('delete_quote(msg):exit:' || l_message);
    RETURN(l_message);
  
  END delete_quote;

  FUNCTION delete_quotes_for_enquiry(p_enqu_id IN enquiries.id%TYPE
                                    ,p_message OUT VARCHAR2) RETURN BOOLEAN IS
    l_quotes_deleted BOOLEAN := TRUE;
  BEGIN
    pl('delete_quotes_for_enquiry:entry:' || p_enqu_id
      ,$$PLSQL_LINE);
  
    FOR l_quote IN (SELECT id
                      FROM quotes
                     WHERE enqu_id = p_enqu_id) LOOP
      IF NOT delete_quote(p_qute_id => l_quote.id
                         ,p_message => p_message) THEN
        l_quotes_deleted := FALSE;
        EXIT;
      END IF;
    END LOOP;
  
    pl('delete_quotes_for_enquiry:' || CASE l_quotes_deleted WHEN TRUE THEN
       'TRUE' ELSE 'FALSE' END
      ,$$PLSQL_LINE);
  
    RETURN(l_quotes_deleted = TRUE);
  END delete_quotes_for_enquiry;

  PROCEDURE lapse_quotes_job IS
    l_current_date DATE := trunc(SYSDATE);
  BEGIN
    FOR cur_quote IN (SELECT v.qute_id
                        FROM v_current_quote_status v
                            ,quotes                 q
                       WHERE v.qust_code IN ('AV', 'SELECTED')
                         AND v.qute_id = q.id
                         AND q.valid_until < l_current_date) LOOP
    
      lapse_quote(p_qute_id     => cur_quote.qute_id
                 ,p_description => 'Quote lapsed automatically by system.');
    
    END LOOP;
  END lapse_quotes_job;

  FUNCTION not_already_submitted(p_enqu_id IN enquiries.id%TYPE)
    RETURN BOOLEAN IS
    l_dummy NUMBER;
  BEGIN
    SELECT 1
      INTO l_dummy
      FROM enquiry_events
     WHERE enst_code = 'SUBMITTED'
       AND enqu_id = p_enqu_id
       AND rownum < 2;
    RETURN FALSE;
  EXCEPTION
    WHEN no_data_found THEN
      RETURN TRUE;
  END not_already_submitted;

  FUNCTION start_quote(p_enqu_id             IN enquiries.id%TYPE
                      ,p_manual_or_automatic IN VARCHAR2 DEFAULT gc_automatic_quote
                      ,p_rfq_prty_id         IN parties.id%TYPE
                      ,p_owner_prty_id       IN parties.id%TYPE)
    RETURN quotes.id%TYPE IS
    l_qute_id quotes.id%TYPE;
  BEGIN
    pl('start_quote:entry'
      ,$$PLSQL_LINE);
    INSERT INTO quotes
      (id
      ,qute_type
      ,enqu_id
      ,valid_from
      ,valid_until
      ,created_on
      ,created_by)
    VALUES
      (qute_seq.NEXTVAL
      ,p_manual_or_automatic
      ,p_enqu_id
      ,trunc(SYSDATE)
      ,(SELECT VALUE + trunc(SYSDATE)
         FROM system_configuration syco
        WHERE syco.parameter = 'QUOTE_LAPSE_LIMIT')
      ,SYSDATE
      ,USER)
    RETURNING id INTO l_qute_id;
  
    add_quote_event(p_qute_id    => l_qute_id
                   ,p_qust_code  => 'INP'
                   ,p_event_date => SYSDATE);
  
    INSERT INTO quote_roles
      (prty_id
      ,qute_id
      ,rt_code
      ,start_date)
    VALUES
      (p_rfq_prty_id
      ,l_qute_id
      ,'Q RFQ'
      ,SYSDATE);
  
    INSERT INTO quote_roles
      (prty_id
      ,qute_id
      ,rt_code
      ,start_date)
    VALUES
      (nvl(p_owner_prty_id
          ,p_rfq_prty_id)
      ,l_qute_id
      ,'Q OWN'
      ,SYSDATE);
  
    pl('start_quote:exit'
      ,$$PLSQL_LINE);
    RETURN l_qute_id;
  
  END start_quote;

  PROCEDURE make_quote_available(p_qute_id IN quotes.id%TYPE) IS
    l_qute_type quotes.qute_type%TYPE;
    l_enqu_id   enquiries.id%TYPE;
  BEGIN
    pl('make_quote_available'
      ,$$PLSQL_LINE);
  
    add_quote_event(p_qute_id    => p_qute_id
                   ,p_qust_code  => 'AV'
                   ,p_event_date => SYSDATE);
  
    SELECT qute_type
          ,enqu_id
      INTO l_qute_type
          ,l_enqu_id
      FROM quotes
     WHERE id = p_qute_id;
  
    IF l_qute_type = 'MQ' THEN
      email_agent_mq_available(l_enqu_id);
    END IF;
  
  END make_quote_available;

  PROCEDURE mark_enquiry_submitted(p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    pl('mark_enquiry_submitted'
      ,$$PLSQL_LINE);
  
    add_enquiry_event(p_enqu_id    => p_enqu_id
                     ,p_enst_code  => 'SUBMITTED'
                     ,p_event_date => SYSDATE);
  
  END mark_enquiry_submitted;

  PROCEDURE mark_enquiry_quoted(p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    pl('mark_enquiry_quoted'
      ,$$PLSQL_LINE);
  
    add_enquiry_event(p_enqu_id    => p_enqu_id
                     ,p_enst_code  => 'QUOTED'
                     ,p_event_date => SYSDATE);
  
  END mark_enquiry_quoted;

  PROCEDURE mark_enquiry_invalid(p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    pl('mark_enquiry_invalid'
      ,$$PLSQL_LINE);
  
    add_enquiry_event(p_enqu_id    => p_enqu_id
                     ,p_enst_code  => 'INVALID'
                     ,p_event_date => SYSDATE);
  END mark_enquiry_invalid;

  FUNCTION get_system_configuration_value(p_parameter IN system_configuration.parameter%TYPE)
    RETURN system_configuration.VALUE%TYPE IS
    l_value system_configuration.VALUE%TYPE;
  BEGIN
    SELECT VALUE
      INTO l_value
      FROM system_configuration
     WHERE parameter = p_parameter;
  
    RETURN l_value;
  EXCEPTION
    WHEN no_data_found THEN
      pl('SYSTEM_CONFIGURATION ERROR - PARAMETER ''' || p_parameter ||
         ''' NOT FOUND'
        ,$$PLSQL_LINE);
      RETURN 'SYSTEM_CONFIGURATION ERROR - PARAMETER ''' || p_parameter || ''' NOT FOUND';
  END get_system_configuration_value;

  FUNCTION get_manual_quote_recipient RETURN system_configuration.VALUE%TYPE IS
  BEGIN
  
    RETURN get_system_configuration_value('EMAIL_ADDRESS_MANUAL_QUOTE');
  END get_manual_quote_recipient;

  FUNCTION get_automatic_quote_recipient
    RETURN system_configuration.VALUE%TYPE IS
  BEGIN
  
    RETURN get_system_configuration_value('EMAIL_ADDRESS_AUTOMATIC_QUOTE');
  END get_automatic_quote_recipient;

  FUNCTION get_support_recipient RETURN system_configuration.VALUE%TYPE IS
  BEGIN
  
    RETURN get_system_configuration_value('EMAIL_ADDRESS_SUPPORT');
  END get_support_recipient;

  FUNCTION get_internal_user_recipient RETURN system_configuration.VALUE%TYPE IS
  BEGIN
  
    RETURN get_system_configuration_value('EMAIL_ADDRESS_INTERNAL_USER');
  END get_internal_user_recipient;

  FUNCTION get_system_name RETURN system_configuration.VALUE%TYPE IS
  BEGIN
    RETURN 'WEBMIP ' || v('SYSTEM_ENVIRONMENT');
  END get_system_name;

  FUNCTION get_customer_support_telephone
    RETURN system_configuration.VALUE%TYPE IS
  BEGIN
  
    RETURN get_system_configuration_value('TELEPHONE_CUSTOMER_SUPPORT');
  END get_customer_support_telephone;

  FUNCTION get_enty_description(p_enqu_id IN enquiries.id%TYPE)
    RETURN enquiry_types.description%TYPE IS
    l_description enquiry_types.description%TYPE;
  BEGIN
    SELECT description
      INTO l_description
      FROM enquiry_types enty
          ,enquiries     enqu
     WHERE enty.code = enqu.enty_code
       AND enqu.id = p_enqu_id;
  
    RETURN l_description;
  END get_enty_description;

  FUNCTION get_quote_document(p_enqu_id IN enquiries.id%TYPE
                             ,p_docu_id IN documents.id%TYPE DEFAULT NULL)
    RETURN t_rec_attachment IS
    l_docu_id     documents.id%TYPE;
    l_doc_details t_rec_attachment;
  BEGIN
    pl('get_quote_document:entry:p_enqu_id=' || p_enqu_id
      ,$$PLSQL_LINE);
  
    l_docu_id := p_docu_id;
  
    IF l_docu_id IS NULL THEN
      -- find the 'selected' quote document
      SELECT doro.docu_id
        INTO l_docu_id
        FROM document_roles doro
            ,quote_events   quev
            ,quotes         qute
       WHERE qute.enqu_id = p_enqu_id
         AND quev.qute_id = qute.id
         AND quev.qust_code = 'SELECTED'
         AND doro.qute_id = quev.qute_id;
    END IF;
  
    pl('l_docu_id=' || l_docu_id
      ,$$PLSQL_LINE);
  
    -- get the document details and content
    SELECT wff.filename
          ,wff.mime_type
          ,wff.blob_content
      INTO l_doc_details.filename
          ,l_doc_details.mime_type
          ,l_doc_details.content
      FROM documents              docu
          ,apex_application_files wff
     WHERE docu.uri = wff.NAME
       AND docu.id = l_docu_id;
  
    pl('l_doc_details.content.length=' ||
       dbms_lob.getlength(l_doc_details.content)
      ,$$PLSQL_LINE);
    pl('get_quote_document:exit:filename=' || l_doc_details.filename
      ,$$PLSQL_LINE);
  
    RETURN l_doc_details;
  
  END get_quote_document;

  PROCEDURE al(p_in IN VARCHAR2) IS
  BEGIN
    dbms_lob.writeappend(g_email_plain_body
                        ,length(p_in || gc_newline)
                        ,p_in || gc_newline);
  
    dbms_lob.writeappend(g_email_html_body
                        ,length(p_in || '<br>')
                        ,p_in || '<br>');
  
  END al;

  PROCEDURE open_body IS
  BEGIN
    pl('open_body:entry');
    dbms_lob.createtemporary(lob_loc => g_email_plain_body
                            ,cache   => TRUE);
    dbms_lob.OPEN(lob_loc   => g_email_plain_body
                 ,open_mode => dbms_lob.lob_readwrite);
  
    dbms_lob.createtemporary(lob_loc => g_email_html_body
                            ,cache   => TRUE);
    dbms_lob.OPEN(lob_loc   => g_email_html_body
                 ,open_mode => dbms_lob.lob_readwrite);
    dbms_lob.writeappend(g_email_html_body
                        ,length('<html><body>')
                        ,'<html><body>');
  
    al(' ');
    al('THIS IS AN AUTOMATED EMAIL - PLEASE DO NOT REPLY AS EMAILS
RECEIVED AT THIS ADDRESS CANNOT BE RESPONDED TO.');
    al(' ');
    al(' ');
    pl('open_body:exit');
  END open_body;

  FUNCTION get_quote_row(p_qute_id IN quotes.id%TYPE) RETURN t_quote_row IS
    l_row t_quote_row;
  BEGIN
  
    SELECT *
      INTO l_row
      FROM quotes
     WHERE id = p_qute_id;
  
    RETURN l_row;
  END get_quote_row;

  PROCEDURE produce_quote_summary(p_qute_id IN quotes.id%TYPE) IS
    l_quote_row t_quote_row;
  BEGIN
    pl('produce_quote_summary'
      ,$$PLSQL_LINE);
  
    l_quote_row := get_quote_row(p_qute_id);
  
    al('Quote summary for Quote Reference Number: ' || p_qute_id);
    al('This quote is valid from ' ||
       to_char(l_quote_row.valid_from
              ,'ddth Month YYYY') || ' to ' ||
       to_char(l_quote_row.valid_until
              ,'ddth Month YYYY'));
  
    IF l_quote_row.qute_type = 'AQ' THEN
      FOR l_sum IN (SELECT *
                      FROM v_quote_details
                     WHERE quote_id = p_qute_id) LOOP
      
        IF l_sum.module_code IS NOT NULL THEN
          al('Module code: ' || l_sum.module_code);
        END IF;
        IF l_sum.lead_time IS NOT NULL THEN
          al('Lead time: ' || l_sum.lead_time || ' days');
        END IF;
        IF l_sum.additional_items IS NOT NULL THEN
          al('Additional items: ' || l_sum.additional_items);
        END IF;
        IF l_sum.bas_code IS NOT NULL THEN
          al('Base code: ' || l_sum.bas_code);
        END IF;
        IF l_sum.hou_code IS NOT NULL THEN
          al('Housing code: ' || l_sum.hou_code);
        END IF;
        IF l_sum.qmax IS NOT NULL THEN
          al('Qmax: ' || l_sum.qmax);
        END IF;
        IF l_sum.qmin IS NOT NULL THEN
          al('Qmin: ' || l_sum.qmin);
        END IF;
        IF l_sum.inlet_orientation IS NOT NULL THEN
          al('Inlet orientation: ' || l_sum.inlet_orientation);
        END IF;
        IF l_sum.outlet_orientation IS NOT NULL THEN
          al('Outlet orientation: ' || l_sum.outlet_orientation);
        END IF;
        IF l_sum.total_cost IS NOT NULL THEN
          al('Total cost: £' || l_sum.total_cost);
        END IF;
      END LOOP;
    ELSE
      al('This quote was produced manually and we are unable to provide a summary,  refer to the online quotation document for details.');
    END IF;
  
  END produce_quote_summary;

  PROCEDURE close_body IS
  BEGIN
    dbms_lob.writeappend(g_email_html_body
                        ,length('</body></html>')
                        ,'</body></html>');
  END close_body;

  PROCEDURE produce_enquiry_entry_details(p_enqu_id IN enquiries.id%TYPE) IS
    l_enty_code enquiries.enty_code%TYPE;
  
    FUNCTION return_value(p_enqu_id    IN enquiries.id%TYPE
                         ,p_field_name IN VARCHAR2) RETURN VARCHAR2 IS
      l_sql       VARCHAR2(240);
      l_cursor_id INTEGER;
      l_rows      INTEGER;
      l_return    VARCHAR2(80);
    BEGIN
    
      l_cursor_id := dbms_sql.open_cursor;
      l_sql       := 'select ' || p_field_name ||
                     ' from enquiries where id = :id';
    
      dbms_sql.parse(l_cursor_id
                    ,l_sql
                    ,dbms_sql.native);
    
      dbms_sql.bind_variable(l_cursor_id
                            ,':id'
                            ,p_enqu_id);
    
      dbms_sql.define_column(l_cursor_id
                            ,1
                            ,l_return
                            ,80);
    
      l_rows := dbms_sql.execute_and_fetch(l_cursor_id);
    
      dbms_sql.column_value(l_cursor_id
                           ,1
                           ,l_return);
    
      dbms_sql.close_cursor(l_cursor_id);
    
      RETURN l_return;
    
    END return_value;
  
  BEGIN
    pl('produce_enquiry_entry_details'
      ,$$PLSQL_LINE);
    al(' ');
    al(' ');
    al(get_system_name ||
       ' recorded the following details against this enquiry:');
  
    SELECT enty_code
      INTO l_enty_code
      FROM enquiries
     WHERE id = p_enqu_id;
  
    FOR l_rec IN (SELECT dir.field_name
                        ,dir.description
                    FROM data_item_roles dir
                   WHERE dir.enty_code = l_enty_code
                   ORDER BY dir.display_sequence) LOOP
    
      al(l_rec.description || ': ' ||
         return_value(p_enqu_id    => p_enqu_id
                     ,p_field_name => l_rec.field_name));
    END LOOP;
  END produce_enquiry_entry_details;

  PROCEDURE produce_reasoning_summary(p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    pl('produce_reasoning_summary'
      ,$$PLSQL_LINE);
    al(' ');
    al(' ');
    al(get_system_name ||
       ' used the following reasoning in reaching this decision:');
    FOR l_rec IN (SELECT reason
                        ,internal_or_external
                    FROM quote_reasoning
                   WHERE enqu_id = p_enqu_id
                   ORDER BY id) LOOP
      al(l_rec.reason);
    END LOOP;
  
  END produce_reasoning_summary;

  PROCEDURE email_to_agent(p_enqu_id      IN enquiries.id%TYPE
                          ,p_email_reason IN t_email_reason) IS
    l_agent_prty_id          parties.id%TYPE;
    l_agent_comt_code        contact_mechanism_types.code%TYPE;
    l_agent_comt_description contact_mechanism_types.description%TYPE;
    l_agent_contact_value    contact_mechanisms.contact_value%TYPE;
    l_email_subject          VARCHAR2(240);
    l_email_recipient        VARCHAR2(240);
    l_agent_first_name       parties.first_name%TYPE;
    l_agent_last_name        parties.last_name%TYPE;
    l_attachment             t_rec_attachment;
  BEGIN
    pl('email_to_agent:enter:p_enqu_id=' || p_enqu_id ||
       ', p_email_reason=' || p_email_reason
      ,$$PLSQL_LINE);
    BEGIN
      l_agent_prty_id := mip_enquiry.get_enquiry_role(p_enquiryid => p_enqu_id
                                                     ,p_rolecode  => 'ENQ OWN');
    
      pl('l_agent_prty_id=' || l_agent_prty_id
        ,$$PLSQL_LINE);
    
    EXCEPTION
      WHEN OTHERS THEN
        pl('mip_enquiry.get_enquiry_role returned ' || SQLERRM
          ,$$PLSQL_LINE);
        RAISE;
    END;
  
    BEGIN
      SELECT agent_comt_code
            ,comt.description
            ,agent_contact_value
        INTO l_agent_comt_code
            ,l_agent_comt_description
            ,l_agent_contact_value
        FROM enquiries
            ,contact_mechanism_types comt
       WHERE enquiries.id = p_enqu_id
         AND comt.code = enquiries.agent_comt_code;
    EXCEPTION
      WHEN OTHERS THEN
        pl('email_to_agent:' || SQLERRM
          ,$$PLSQL_LINE);
        RAISE;
    END;
  
    open_body;
  
    IF l_agent_comt_code = 'EMAIL' THEN
      l_email_recipient := l_agent_contact_value;
    ELSE
      l_email_recipient := get_automatic_quote_recipient;
      SELECT first_name
            ,last_name
        INTO l_agent_first_name
            ,l_agent_last_name
        FROM parties
       WHERE id = l_agent_prty_id;
      al('The Agent, ' || l_agent_first_name || ' ' || l_agent_last_name ||
         ' has requested that they be contacted via ' ||
         l_agent_comt_description || '(' || l_agent_contact_value || ')');
      al(' ');
      al('The information to be passed on is:');
      al(' ');
    END IF;
  
    pl('l_email_recipient=' || l_email_recipient
      ,$$PLSQL_LINE);
  
    IF p_email_reason = gc_agent_q_sel_notification THEN
      --Scenario one: Automatic quote generation notification
      l_email_subject := get_system_name || ': Automatic Quote Selected';
      l_attachment    := get_quote_document(p_enqu_id => p_enqu_id);
      al('Dear Sir/Madam,');
      al(' ');
      al('Enquiry reference number: ' || p_enqu_id);
      al(' ');
      al('Thank you for using the National Grid Metering Online I&C quotations system.  Your enquiry has generated an automatic quotation.    To accept this quotation; either submit a work request via IX, complete the acceptance form on the quotation letter, or click the accept button on the Online I&C quotations system.  Please note that this quotation is valid for 90 days from the date specified in the quotation.');
      al(' ');
      al('Please use the Enquiry reference number at the top of this email on any future correspondence relating to this request.');
      al(' ');
      al('Regards');
      al(' ');
      al('The I&C Non Standard Team');
      al('National Grid Metering');
      al('Abbotts Lane');
      al('Coventry');
      al('CV1 4AY');
      al(' ');
      al('Phone: 02476 286 322');
      al('Fax: 02476 286 044');
      al('Email: ic.nonstandard1@uk.ngrid.com');
    ELSIF p_email_reason = gc_agent_q_acc_notification THEN
      -- Scenario two: Quotation acceptance notification 
      l_email_subject := get_system_name || ': Quote Acceptance';
      al('Dear Sir/Madam,');
      al(' ');
      al('Enquiry reference number: ' || p_enqu_id);
      al(' ');
      al('Thank you for accepting National Grid Metering¿s quotation using the Online I&C Quotations System.  We will acknowledge your acceptance and provide you with a planning letter within the agreed timescales for the work type involved. ');
      al(' ');
      al('Please use the Enquiry reference number at the top of this email on any future correspondence relating to this request.');
      al(' ');
      al('For further information or clarity, please do not hesitate to get in contact');
      al(' ');
      al('Regards');
      al(' ');
      al('The I&C Non Standard Team');
      al('National Grid Metering');
      al('Abbotts Lane');
      al('Coventry');
      al('CV1 4AY');
      al(' ');
      al('Phone: 02476 286 322');
      al('Fax: 02476 286 044');
      al('Email: ic.nonstandard1@uk.ngrid.com');
    ELSIF p_email_reason = gc_agent_mq_req_notification THEN
      -- Scenario three: Manual quote requested
      l_email_subject := get_system_name || ': Request for Manual Quote';
      al('Dear Sir/Madam,');
      al(' ');
      al('Enquiry reference number: ' || p_enqu_id);
      al(' ');
      al('Thank you for requesting a quotation using the National Grid Metering Online I&C Quotations System.  There are no automatic quotations available for this request, and a manual quotation will be prepared and uploaded to the website within the agreed timescales for the work type involved.  We will notify you when this quotation is ready for your consideration.');
      al(' ');
      al('Please use the Enquiry reference number at the top of this email on any future correspondence relating to this request.');
      al(' ');
      al('Regards');
      al(' ');
      al('The I&C Non Standard Team');
      al('National Grid Metering');
      al('Abbotts Lane');
      al('Coventry');
      al('CV1 4AY');
      al(' ');
      al('Phone: 02476 286 322');
      al('Fax: 02476 286 044');
      al('Email: ic.nonstandard1@uk.ngrid.com');
    ELSIF p_email_reason = gc_agent_mq_avail_notification THEN
      -- Scenario four:  Manual quote produced and ready to view.
      l_email_subject := get_system_name || ': Manual Quote Available';
      l_attachment    := get_quote_document(p_enqu_id => p_enqu_id);
      al('Dear Sir/Madam,');
      al(' ');
      al('Enquiry reference number: ' || p_enqu_id);
      al(' ');
      al('Your quotation is now ready to view on the National Grid Metering I&C Online Quotation System.');
      al(' ');
      al('Please note that this quotation is valid for 90 days from the date specified in the quotation.  To accept this quotation, please submit a work request via IX, complete the acceptance form on the quotation letter, or click the accept button on the Online I&C quotations system.  Please note that this quotation is valid for 90 days from the date specified in the quotation.');
      al(' ');
      al('Please use the Enquiry reference number at the top of this email on any future correspondence relating to this request.');
      al(' ');
      al('Regards');
      al(' ');
      al('The I&C Non Standard Team');
      al('National Grid Metering');
      al('Abbotts Lane');
      al('Coventry');
      al('CV1 4AY');
      al(' ');
      al('Phone: 02476 286 322');
      al('Fax: 02476 286 044');
      al('Email: ic.nonstandard1@uk.ngrid.com');
    END IF;
    close_body;
  
    BEGIN
      pl('email_to_agent:l_email_recipient=' || l_email_recipient || ':' ||
         'l_email_subject=' || l_email_subject
        ,$$PLSQL_LINE);
    
      mip_email.send_email_clob(p_recipient => l_email_recipient
                               ,p_body      => g_email_plain_body
                               ,p_body_html => g_email_html_body
                               ,p_subject   => l_email_subject);
    
      IF dbms_lob.getlength(lob_loc => l_attachment.content) > 0 THEN
        mip_email.add_attachment(p_attachment => l_attachment.content
                                ,p_filename   => l_attachment.filename
                                ,p_mime_type  => l_attachment.mime_type);
      END IF;
    
    EXCEPTION
      WHEN OTHERS THEN
        pl('mip_email.send_email_clob returned ' || SQLERRM
          ,$$PLSQL_LINE);
    END;
    pl('email_to_agent:exit'
      ,$$PLSQL_LINE);
  END email_to_agent;

  PROCEDURE email_to_internal_users(p_enqu_id      IN enquiries.id%TYPE
                                   ,p_email_reason IN t_email_reason) IS
    l_email_subject   VARCHAR2(240);
    l_email_recipient VARCHAR2(240) := get_internal_user_recipient;
    l_qute_id         quotes.id%TYPE;
  BEGIN
    pl('email_internal_users:entry:p_enqu_id=' || p_enqu_id ||
       ', p_email_reason=' || p_email_reason
      ,$$PLSQL_LINE);
    open_body;
    IF p_email_reason = gc_int_q_acc_notification THEN
      -- Scenario five: Accepted quote + system data
      l_email_subject := get_system_name ||
                         ': Quote Acceptance Notification';
      al('Hi team,');
      al(' ');
      al('Enquiry reference number: ' || p_enqu_id);
      al(' ');
      al('Please process this enquiry, as the quotation has been accepted.');
      al(' ');
    
      SELECT qute_id
        INTO l_qute_id
        FROM quote_events quev
            ,quotes       qute
       WHERE qute.enqu_id = p_enqu_id
         AND quev.qute_id = qute.id
         AND quev.qust_code = 'ACCEPTED';
    
      produce_enquiry_entry_details(p_enqu_id);
      produce_quote_summary(l_qute_id);
    
      al(' ');
      al('Regards');
      al(' ');
      al('I&C Online Quotations System');
    ELSIF p_email_reason = gc_int_mq_req_notification THEN
      -- Scenario six: Request for manual quote + system data
      l_email_subject := get_system_name ||
                         ': Request For Manual Quotation Notification';
      al('Hi team,');
      al(' ');
      al('Enquiry reference number: ' || p_enqu_id);
      al(' ');
      al('Please provide a manual quotation as the I&C Online Quotation System was unable to generate an automatic quote.');
      al(' ');
    
      SELECT qute_id
        INTO l_qute_id
        FROM quote_events quev
            ,quotes       qute
       WHERE qute.enqu_id = p_enqu_id
         AND quev.qute_id = qute.id
         AND quev.qust_code = 'INP';
    
      produce_enquiry_entry_details(p_enqu_id);
      produce_reasoning_summary(p_enqu_id);
    
      al(' ');
      al('Regards');
      al(' ');
      al('I&C Online Quotations System');
    END IF;
    close_body;
    BEGIN
      pl('email_to_internal_users:l_email_recipient=' || l_email_recipient || ':' ||
         'l_email_subject=' || l_email_subject
        ,$$PLSQL_LINE);
      mip_email.send_email_clob(p_recipient => l_email_recipient
                               ,p_body      => g_email_plain_body
                               ,p_body_html => g_email_html_body
                               ,p_subject   => l_email_subject);
    EXCEPTION
      WHEN OTHERS THEN
        pl('mip_email.send_email_clob returned ' || SQLERRM
          ,$$PLSQL_LINE);
    END;
    pl('email_internal_users:exit'
      ,$$PLSQL_LINE);
  END email_to_internal_users;

  PROCEDURE email_agent_q_sel(p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    pl('email_agent_q_sel:entry'
      ,$$PLSQL_LINE);
  
    email_to_agent(p_enqu_id      => p_enqu_id
                  ,p_email_reason => gc_agent_q_sel_notification);
  
    pl('email_agent_q_sel:exit'
      ,$$PLSQL_LINE);
  END email_agent_q_sel;

  PROCEDURE email_agent_mq_available(p_enqu_id enquiries.id%TYPE) IS
  BEGIN
    email_to_agent(p_enqu_id      => p_enqu_id
                  ,p_email_reason => gc_agent_mq_avail_notification);
  END email_agent_mq_available;

  PROCEDURE email_agent_request_for_mq(p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    pl('email_request_for_mq:enter'
      ,$$PLSQL_LINE);
  
    email_to_agent(p_enqu_id      => p_enqu_id
                  ,p_email_reason => gc_agent_mq_req_notification);
  
    email_to_internal_users(p_enqu_id      => p_enqu_id
                           ,p_email_reason => gc_int_mq_req_notification);
  
    pl('email_request_for_mq:exit'
      ,$$PLSQL_LINE);
  END email_agent_request_for_mq;

  PROCEDURE email_agent_quote_acceptance(p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    email_to_agent(p_enqu_id      => p_enqu_id
                  ,p_email_reason => gc_agent_q_acc_notification);
  END email_agent_quote_acceptance;

  PROCEDURE email_iu_quote_acceptance(p_enqu_id enquiries.id%TYPE) IS
  BEGIN
    email_to_internal_users(p_enqu_id
                           ,gc_int_q_acc_notification);
  END email_iu_quote_acceptance;

  PROCEDURE email_iu_request_for_mq(p_enqu_id enquiries.id%TYPE) IS
  BEGIN
    email_to_internal_users(p_enqu_id
                           ,gc_int_mq_req_notification);
  END email_iu_request_for_mq;

  PROCEDURE email_support(p_subject IN VARCHAR2
                         ,p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    pl('email_support'
      ,$$PLSQL_LINE);
  
    open_body;
  
    al('MIP_QUOTATION: Enquiry ' || p_enqu_id || ' (' ||
       get_enty_description(p_enqu_id) || ')' ||
       ' - processing error occurred');
  
    FOR l_qure IN (SELECT reason
                     FROM quote_reasoning
                    WHERE enqu_id = p_enqu_id
                    ORDER BY id) LOOP
      al(l_qure.reason);
    END LOOP;
  
    close_body;
  
    BEGIN
      mip_email.send_email_clob(p_recipient => get_support_recipient
                               ,p_body      => g_email_plain_body
                               ,p_body_html => g_email_html_body
                               ,p_subject   => get_system_name || ' : ' ||
                                               p_subject ||
                                               ' : Enquiry Reference Number: ' ||
                                               p_enqu_id);
    EXCEPTION
      WHEN OTHERS THEN
        pl('mip_email.send_email_clob returned ' || SQLERRM
          ,$$PLSQL_LINE);
    END;
  END email_support;

  PROCEDURE request_manual_quote(p_enqu_id       IN enquiries.id%TYPE
                                ,p_rfq_prty_id   IN parties.id%TYPE
                                ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL) IS
    l_qute_id quotes.id%TYPE;
  BEGIN
    pl('request_manual_quote'
      ,$$PLSQL_LINE);
  
    l_qute_id := start_quote(p_enqu_id             => p_enqu_id
                            ,p_manual_or_automatic => gc_manual_quote
                            ,p_rfq_prty_id         => p_rfq_prty_id
                            ,p_owner_prty_id       => p_owner_prty_id);
  
    email_agent_request_for_mq(p_enqu_id => p_enqu_id);
  END request_manual_quote;

  PROCEDURE ready_for_quote(p_enqu_id          IN enquiries.id%TYPE
                           ,p_mandatory_checks OUT mip_mandatory.t_mandatory_checks
                           ,p_enquiry_is_ready OUT BOOLEAN) IS
  BEGIN
    pl('ready_for_quote(1) - enqu_id=' || p_enqu_id
      ,$$PLSQL_LINE);
  
    p_enquiry_is_ready := mip_enquiries_helper.check_mandatory(p_id               => p_enqu_id
                                                              ,p_mandatory_checks => p_mandatory_checks);
  END ready_for_quote;

  FUNCTION ready_for_quote(p_enqu_id IN enquiries.id%TYPE) RETURN BOOLEAN IS
    l_mandatory_checks mip_mandatory.t_mandatory_checks;
    l_enquiry_is_ready BOOLEAN;
    l_idx              BINARY_INTEGER;
  BEGIN
    pl('ready_for_quote(2) - enqu_id=' || p_enqu_id
      ,$$PLSQL_LINE);
  
    ready_for_quote(p_enqu_id          => p_enqu_id
                   ,p_mandatory_checks => l_mandatory_checks
                   ,p_enquiry_is_ready => l_enquiry_is_ready);
  
    l_idx := l_mandatory_checks.FIRST;
    LOOP
      EXIT WHEN l_idx IS NULL;
      pl(l_mandatory_checks(l_idx)
         .field_name || ':' || l_mandatory_checks(l_idx).error_message);
      l_idx := l_mandatory_checks.NEXT(l_idx);
    END LOOP;
  
    RETURN l_enquiry_is_ready;
  END ready_for_quote;

  PROCEDURE validate(p_rec IN mip_enquiries_helper.t_rec_enquiries) IS
    l_mandatory_checks mip_mandatory.t_mandatory_checks;
    l_idx              NUMBER;
  BEGIN
    /*pl('validate enqu_id=' || p_rec.id || ', enty_code=' ||
    p_rec.enty_code);*/
    DELETE FROM validation_results
     WHERE enqu_id = p_rec.id;
    IF NOT
        mip_enquiries_helper.check_mandatory(p_rec              => p_rec
                                            ,p_mandatory_checks => l_mandatory_checks) THEN
      l_idx := l_mandatory_checks.FIRST;
      LOOP
        EXIT WHEN l_idx IS NULL;
        INSERT INTO validation_results
          (enqu_id
          ,field_name
          ,error_message)
        VALUES
          (p_rec.id
          ,l_mandatory_checks(l_idx).field_name
          ,l_mandatory_checks(l_idx).error_message);
        l_idx := l_mandatory_checks.NEXT(l_idx);
      END LOOP;
    END IF;
  
  EXCEPTION
    WHEN OTHERS THEN
      pl('validate:' || SQLERRM
        ,$$PLSQL_LINE);
      RAISE;
  END validate;

  FUNCTION validation_result(p_enqu_id    IN enquiries.id%TYPE
                            ,p_field_name IN VARCHAR2) RETURN VARCHAR2 IS
    l_error_message validation_results.error_message%TYPE;
  BEGIN
    pl('validation_result:entry:' || p_enqu_id || ':' || p_field_name
      ,$$PLSQL_LINE);
  
    SELECT error_message
      INTO l_error_message
      FROM validation_results
     WHERE field_name = upper(p_field_name)
       AND enqu_id = p_enqu_id;
    pl('validation_result:exit:' || l_error_message
      ,$$PLSQL_LINE);
    RETURN l_error_message;
  EXCEPTION
    WHEN no_data_found THEN
      pl('validation_result:exit:NDF'
        ,$$PLSQL_LINE);
      RETURN NULL;
    WHEN OTHERS THEN
      pl('validation_result:exit:EXCEPTION:' || SQLERRM
        ,$$PLSQL_LINE);
      RAISE;
  END validation_result;

  FUNCTION return_mandatory_messages(p_enqu_id IN enquiries.id%TYPE)
    RETURN VARCHAR2 IS
    l_mandatory_messages VARCHAR2(4000);
    l_mandatory_checks   mip_mandatory.t_mandatory_checks;
    l_enquiry_is_ready   BOOLEAN;
    l_idx                INTEGER;
  BEGIN
    pl('return_mandatory_messages'
      ,$$PLSQL_LINE);
  
    ready_for_quote(p_enqu_id          => p_enqu_id
                   ,p_mandatory_checks => l_mandatory_checks
                   ,p_enquiry_is_ready => l_enquiry_is_ready);
  
    l_idx := l_mandatory_checks.FIRST;
    LOOP
      EXIT WHEN l_idx IS NULL;
      l_mandatory_messages := l_mandatory_checks(l_idx)
                             .field_name || ':' ||
                              l_mandatory_checks(l_idx).error_message;
      l_idx                := l_mandatory_checks.NEXT(l_idx);
    END LOOP;
  
    RETURN l_mandatory_messages;
  
  END return_mandatory_messages;

  FUNCTION get_u_meter_size(p_qmax IN NUMBER)
    RETURN meter_size_codes.code%TYPE IS
    l_meter_size_code meter_size_codes.code%TYPE;
  BEGIN
    pl('get_u_meter_size:entry'
      ,$$PLSQL_LINE);
  
    -- get the smallest meter code that will support the given Qmax
    SELECT code
      INTO l_meter_size_code
      FROM (SELECT code
              FROM meter_size_codes mesc
             WHERE qmax >= p_qmax
               AND mesc.valid_for_new_meter = 'YES'
             ORDER BY qmax)
     WHERE rownum < 2;
  
    pl('get_u_meter_size:exit:' || l_meter_size_code
      ,$$PLSQL_LINE);
  
    RETURN l_meter_size_code;
  
  EXCEPTION
    WHEN no_data_found THEN
      pl('get_u_meter_size:exit:NDF'
        ,$$PLSQL_LINE);
      RETURN NULL;
  END get_u_meter_size;

  FUNCTION get_qmax_from_mesc(p_mesc_code IN meter_size_codes.code%TYPE)
    RETURN meter_size_codes.qmax%TYPE IS
    l_qmax meter_size_codes.qmax%TYPE;
  BEGIN
    SELECT qmax
      INTO l_qmax
      FROM meter_size_codes
     WHERE code = p_mesc_code;
    RETURN l_qmax;
  EXCEPTION
    WHEN no_data_found THEN
      cout_err.report_and_stop(p_exception_message => 'Unable to find Qmax for Meter Size Code of ' ||
                                                      p_mesc_code);
  END get_qmax_from_mesc;

  FUNCTION get_seq_u_meter_size(p_existing_mesc_code IN meter_size_codes.code%TYPE
                               ,p_n_or_p             IN VARCHAR2 DEFAULT 'N')
    RETURN meter_size_codes.code%TYPE IS
    l_next_required  BOOLEAN := upper(substr(p_n_or_p
                                            ,1
                                            ,1)) = 'N';
    l_prev_mesc_code meter_size_codes.code%TYPE;
    l_next_mesc_code meter_size_codes.code%TYPE;
  BEGIN
    pl('get_seq_u_meter_size:entry:' || p_existing_mesc_code || ':' ||
       p_n_or_p);
    SELECT lag(code) over(ORDER BY qmax) AS prev_mesc_code
          ,lead(code) over(ORDER BY qmax) AS next_mesc_code
      INTO l_prev_mesc_code
          ,l_next_mesc_code
      FROM meter_size_codes
     WHERE code LIKE 'U%';
  
    IF l_next_required THEN
      pl('get_seq_u_meter_size:exit:' || l_next_mesc_code
        ,$$PLSQL_LINE);
      RETURN l_next_mesc_code;
    ELSE
      pl('get_seq_u_meter_size:exit:' || l_prev_mesc_code
        ,$$PLSQL_LINE);
      RETURN l_prev_mesc_code;
    END IF;
  EXCEPTION
    WHEN no_data_found THEN
      pl('get_seq_u_meter_size:exit:NDF'
        ,$$PLSQL_LINE);
      RETURN NULL;
  END get_seq_u_meter_size;

  FUNCTION get_next_u_meter_size(p_existing_mesc_code IN meter_size_codes.code%TYPE)
    RETURN meter_size_codes.code%TYPE IS
  BEGIN
  
    RETURN(get_seq_u_meter_size(p_existing_mesc_code
                               ,'N'));
  
  END get_next_u_meter_size;

  FUNCTION get_previous_u_meter_size(p_existing_mesc_code IN meter_size_codes.code%TYPE)
    RETURN meter_size_codes.code%TYPE IS
  BEGIN
  
    RETURN(get_seq_u_meter_size(p_existing_mesc_code
                               ,'P'));
  
  END get_previous_u_meter_size;

  FUNCTION get_metr_details(p_metr_code IN meters.code%TYPE)
    RETURN t_rec_metr_details IS
    l_rec_metr_details t_rec_metr_details;
  BEGIN
    pl('get_metr_details:enter:' || p_metr_code);
  
    SELECT code
          ,mesc_code
          ,mety_code
          ,prty_id
      INTO l_rec_metr_details.code
          ,l_rec_metr_details.mesc_code
          ,l_rec_metr_details.mety_code
          ,l_rec_metr_details.prty_id
      FROM meters
     WHERE code = p_metr_code;
  
    pl('get_metr_details:exit:' || p_metr_code);
  
    RETURN(l_rec_metr_details);
  
  END get_metr_details;

  FUNCTION get_existing_metr_details(p_enqu t_enqu) RETURN t_rec_metr_details IS
    l_rec_metr_details t_rec_metr_details;
  BEGIN
    pl('get_existing_metr_details:entry'
      ,$$PLSQL_LINE);
  
    IF nvl(p_enqu.existing_metr_code
          ,'OTHER') <> 'OTHER' THEN
      l_rec_metr_details := get_metr_details(p_enqu.existing_metr_code);
    ELSE
      l_rec_metr_details.mesc_code := p_enqu.existing_mesc_code;
      IF l_rec_metr_details.mesc_code IS NULL THEN
        l_rec_metr_details.mesc_code := get_u_meter_size(p_enqu.qmax);
      END IF;
      l_rec_metr_details.mety_code := p_enqu.existing_mety_code;
    END IF;
  
    pl('get_existing_metr_details:exit:' || l_rec_metr_details.code || ':' ||
       l_rec_metr_details.mesc_code || ':' || l_rec_metr_details.mety_code || ':' ||
       l_rec_metr_details.prty_id
      ,$$PLSQL_LINE);
    RETURN l_rec_metr_details;
  END get_existing_metr_details;

  /** Required meter can be within one 'U'-size of existing meter
  */
  FUNCTION valid_meter_size_change(p_existing_meter_size_code IN meter_size_codes.code%TYPE
                                  ,p_required_meter_size_code IN meter_size_codes.code%TYPE)
    RETURN BOOLEAN IS
    l_dummy NUMBER;
  BEGIN
    pl('valid_meter_size_change'
      ,$$PLSQL_LINE);
  
    IF p_existing_meter_size_code = p_required_meter_size_code THEN
      RETURN TRUE;
    END IF;
  
    SELECT NULL
      INTO l_dummy
      FROM (SELECT code AS existing_mesc
                  ,lead(code) over(ORDER BY qmax) AS required_mesc
              FROM meter_size_codes
             WHERE code LIKE 'U%')
     WHERE (existing_mesc = p_existing_meter_size_code AND
           required_mesc = p_required_meter_size_code)
        OR (existing_mesc = p_required_meter_size_code AND
           required_mesc = p_existing_meter_size_code);
  
    pl('valid_meter_size_change:exit:TRUE'
      ,$$PLSQL_LINE);
    RETURN TRUE;
  
  EXCEPTION
    WHEN no_data_found THEN
      pl('valid_meter_size_change:exit:FALSE'
        ,$$PLSQL_LINE);
      RETURN FALSE;
  END valid_meter_size_change;

  PROCEDURE survey_required(p_enqu                      IN t_enqu
                           ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
    l_svcpt_code                service_pressure_types.code%TYPE;
    l_rec_metr_details          t_rec_metr_details;
    l_required_mesc_code        meter_size_codes.code%TYPE;
    l_manual_or_automatic_quote t_manual_or_automatic_quote := gc_automatic_quote;
  BEGIN
    pl('survey_required'
      ,$$PLSQL_LINE);
  
    -- Site survey required for:
    -- Existing meter is (rotary or turbine) AND OFMAT enquiry;
  
    IF p_enqu.enty_code = 'OFMAT' THEN
      -- get existing meter details
      l_rec_metr_details := get_existing_metr_details(p_enqu);
    
      IF l_rec_metr_details.mety_code IN ('ROTARY', 'TURBINE') THEN
        l_manual_or_automatic_quote := gc_manual_quote;
        add_quote_reason(p_enqu.id
                        ,'Site Survey is required for OFMAT of an existing ' ||
                         initcap(l_rec_metr_details.mety_code) ||
                         ' meter.'
                        ,gc_external_reason);
      END IF; -- ROTARY / TURBINE
    END IF; -- OFMAT
  
    IF l_manual_or_automatic_quote = gc_manual_quote THEN
      p_manual_or_automatic_quote := gc_manual_quote;
    END IF;
  
  END survey_required;

  FUNCTION tripartite_agreement_message(p_rec IN mip_enquiries_helper.t_rec_enquiries)
    RETURN VARCHAR2 IS
    l_tab_messages mip_tripartite.t_tab_messages;
    l_return       VARCHAR2(4000);
    l_valid        BOOLEAN;
    l_idx          INTEGER;
  BEGIN
  
    pl('tripartite_agreement_message'
      ,$$PLSQL_LINE);
  
    l_valid := mip_tripartite.valid_enquiry(p_rec          => p_rec
                                           ,p_tab_messages => l_tab_messages);
  
    -- processing an enquiry that has not passed the tripartite agreement
    -- infers a processing error
    IF NOT l_valid THEN
      l_idx := l_tab_messages.FIRST;
      LOOP
        EXIT WHEN l_idx IS NULL;
        l_return := l_return || l_tab_messages(l_idx);
        l_idx    := l_tab_messages.NEXT(l_idx);
      END LOOP;
    END IF;
  
    RETURN l_return;
  
  END tripartite_agreement_message;

  FUNCTION tripartite_agreement_satisfied(p_enqu_id IN enquiries.id%TYPE)
    RETURN BOOLEAN IS
    l_tab_messages mip_tripartite.t_tab_messages;
    l_valid        BOOLEAN;
    l_idx          INTEGER;
  BEGIN
  
    pl('tripartite_agreement_satisfied'
      ,$$PLSQL_LINE);
  
    l_valid := mip_tripartite.valid_enquiry(p_enqu_id      => p_enqu_id
                                           ,p_tab_messages => l_tab_messages);
  
    -- processing an enquiry that has not passed the tripartite agreement
    -- infers a processing error
    IF NOT l_valid THEN
      l_idx := l_tab_messages.FIRST;
      LOOP
        EXIT WHEN l_idx IS NULL;
        add_quote_reason(p_enqu_id              => p_enqu_id
                        ,p_reason               => l_tab_messages(l_idx)
                        ,p_internal_or_external => gc_external_reason);
        l_idx := l_tab_messages.NEXT(l_idx);
      END LOOP;
    
      mark_enquiry_invalid(p_enqu_id);
      email_support(p_subject => 'MIP_QUOTATION: Tripartite failure with Enquiry ' ||
                                 p_enqu_id
                   ,p_enqu_id => p_enqu_id);
    END IF;
  
    RETURN l_valid;
  
  END tripartite_agreement_satisfied;

  PROCEDURE manual_or_automatic_quote(p_enqu                      IN t_enqu
                                     ,p_manual_or_automatic_quote OUT t_manual_or_automatic_quote) IS
    l_dummy NUMBER;
  BEGIN
    pl('manual_or_automatic_quote'
      ,$$PLSQL_LINE);
    p_manual_or_automatic_quote := gc_automatic_quote;
  
    IF p_enqu.enty_code IN ('OTHER', 'CHANGE CAPACITY') THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Enquiry type is ''' ||
                       get_enty_description(p_enqu.id) || '''.'
                      ,gc_external_reason);
    END IF;
  
    survey_required(p_enqu                      => p_enqu
                   ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
  
    IF p_enqu.twin_stream_required = 'YES' THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Twin stream required.'
                      ,gc_external_reason);
    END IF;
  
    IF p_enqu.bypass_required IS NOT NULL THEN
      BEGIN
        SELECT 1
          INTO l_dummy
          FROM bypass_reasons
         WHERE description = p_enqu.bypass_required
           AND code <> '1';
        p_manual_or_automatic_quote := gc_manual_quote;
        add_quote_reason(p_enqu.id
                        ,'Bypass required.'
                        ,gc_external_reason);
      EXCEPTION
        WHEN no_data_found THEN
          -- bypass reason 1
          -- no action required
          NULL;
      END;
    END IF;
  
    IF p_enqu.required_metering_pressure > 21 THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Required metering pressure is greater than 21mbar.'
                      ,gc_external_reason);
    END IF;
  
    IF p_enqu.convertor_required = 'YES' THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Convertor required.'
                      ,gc_external_reason);
    END IF;
  
    IF p_enqu.job_description IS NOT NULL THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Job Description field was entered.'
                      ,gc_external_reason);
    END IF;
  
    IF p_enqu.downstream_booster_or_compress = 'YES' THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Booster or compressor is present downstream of the meter module.'
                      ,gc_external_reason);
    END IF;
  
    IF p_enqu.annual_quantity > 732 * 1000 THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Required Annual Quantity is in excess of 732MWh.'
                      ,gc_external_reason);
    END IF;
  
    IF p_enqu.existing_convertor = 'YES'
       AND p_enqu.enty_code NOT IN ('REMOVE', 'STD REMOVE', 'ADVERSARIAL') THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Convertor is present.'
                      ,gc_external_reason);
    END IF;
  
    IF p_enqu.existing_logger = 'YES'
       AND p_enqu.enty_code IN ('EXCHANGE', 'STD EXCHANGE') THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Logger is present.'
                      ,gc_external_reason);
    END IF;
  
    IF p_enqu.enty_code IN ('OFMAT')
       AND NOT (p_enqu.existing_mety_code = 'DIAPHRAGM')
       AND (substr(nvl(p_enqu.required_mesc_code
                      ,get_u_meter_size(p_enqu.qmax))
                  ,1) <> 'U') THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'OFMAT request for a non-''U''-sized Diaphragm meter.'
                      ,gc_external_reason);
    END IF;
  
    IF p_enqu.enty_code IN ('OFMAT', 'EXCHANGE', 'STD EXCHANGE', 'REMOVE',
        'STD REMOVE', 'ADVERSARIAL')
       AND p_enqu.existing_metr_code IS NULL
       AND (p_enqu.existing_mesc_code IS NULL OR
       p_enqu.existing_mesc_code = 'OTHER' OR
       p_enqu.existing_mety_code IS NULL) THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,get_enty_description(p_enqu.enty_code) ||
                       ' request has insufficient details describing the existing meter.'
                      ,gc_external_reason);
    END IF;
  
    -- check postcode
    IF NOT mip_regions.valid_postcode_format(p_enqu.install_postcode) THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Installation postcode is of an unrecognized format.'
                      ,gc_external_reason);
    ELSIF mip_regions.get_region_for_postcode(p_enqu.install_postcode) IS NULL THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Unable to determine pricing region for given installation postcode.'
                      ,gc_external_reason);
    END IF;
  
    IF p_manual_or_automatic_quote = gc_manual_quote THEN
      add_quote_reason(p_enqu.id
                      ,'- Manual quote required.'
                      ,gc_internal_reason);
    END IF;
  END manual_or_automatic_quote;

  FUNCTION get_housing(p_hou_code  IN modules.hou_code%TYPE
                      ,p_regi_code regions.code%TYPE)
    RETURN t_rec_additional_costs IS
    l_rec_costs t_rec_additional_costs;
  BEGIN
    pl('get_housing'
      ,$$PLSQL_LINE);
    SELECT selling_price
          ,cost_price
          ,delivery_cost
      INTO l_rec_costs.selling_price
          ,l_rec_costs.cost_price
          ,l_rec_costs.delivery_cost
      FROM (SELECT selling_price
                  ,cost_price
                  ,delivery_cost
              FROM (SELECT decode(regi_code
                                 ,p_regi_code
                                 ,1
                                 ,999) AS accuracy
                          ,hou_code
                          ,selling_price
                          ,cost_price
                          ,delivery_cost
                          ,ROWID
                      FROM v_hoco cost
                     WHERE hou_code = p_hou_code
                       AND SYSDATE BETWEEN cost.effective_from AND
                           cost.effective_to
                       AND (regi_code = p_regi_code OR regi_code IS NULL)
                     ORDER BY 1)
             WHERE rownum < 2);
  
    RETURN l_rec_costs;
  EXCEPTION
    WHEN no_data_found THEN
      RETURN l_rec_costs;
  END get_housing;

  FUNCTION get_base(p_bas_code  IN modules.hou_code%TYPE
                   ,p_regi_code regions.code%TYPE)
    RETURN t_rec_additional_costs IS
    l_rec_costs t_rec_additional_costs;
  BEGIN
    pl('get_base'
      ,$$PLSQL_LINE);
    SELECT selling_price
          ,cost_price
          ,delivery_cost
      INTO l_rec_costs.selling_price
          ,l_rec_costs.cost_price
          ,l_rec_costs.delivery_cost
      FROM (SELECT selling_price
                  ,cost_price
                  ,delivery_cost
              FROM (SELECT decode(regi_code
                                 ,p_regi_code
                                 ,1
                                 ,999) AS accuracy
                          ,bas_code
                          ,selling_price
                          ,cost_price
                          ,delivery_cost
                          ,ROWID
                      FROM v_baco cost
                     WHERE bas_code = p_bas_code
                       AND SYSDATE BETWEEN cost.effective_from AND
                           cost.effective_to
                       AND (regi_code = p_regi_code OR regi_code IS NULL)
                     ORDER BY 1)
             WHERE rownum < 2);
  
    RETURN l_rec_costs;
  
  EXCEPTION
    WHEN no_data_found THEN
      RETURN l_rec_costs;
  END get_base;

  FUNCTION get_aico(p_adit_code IN costs.adit_code%TYPE
                   ,p_regi_code IN regions.code%TYPE)
    RETURN t_rec_additional_costs IS
    l_rec_costs t_rec_additional_costs;
  BEGIN
    pl('get_aico:' || p_adit_code || ':' || p_regi_code
      ,$$PLSQL_LINE);
    SELECT lead_time
          ,selling_price
          ,cost_price
          ,delivery_cost
          ,adit_code
      INTO l_rec_costs.lead_time
          ,l_rec_costs.selling_price
          ,l_rec_costs.cost_price
          ,l_rec_costs.delivery_cost
          ,l_rec_costs.adit_code
      FROM additional_items adit
          ,(SELECT selling_price
                  ,cost_price
                  ,delivery_cost
                  ,adit_code
              FROM (SELECT decode(regi_code
                                 ,p_regi_code
                                 ,1
                                 ,999) AS accuracy
                          ,adit_code
                          ,selling_price
                          ,cost_price
                          ,delivery_cost
                          ,ROWID
                      FROM v_aico cost
                     WHERE adit_code = p_adit_code
                       AND SYSDATE BETWEEN cost.effective_from AND
                           cost.effective_to
                       AND (regi_code = p_regi_code OR regi_code IS NULL)
                     ORDER BY 1)
             WHERE rownum < 2) cost
     WHERE adit.code = cost.adit_code
       AND adit.code = p_adit_code;
  
    pl('get_aico:exit'
      ,$$PLSQL_LINE);
    RETURN l_rec_costs;
  
  EXCEPTION
    WHEN no_data_found THEN
      pl('get_aico:exit:NDF'
        ,$$PLSQL_LINE);
      RETURN l_rec_costs;
    WHEN OTHERS THEN
      pl('get_aico:exit:' || SQLERRM
        ,$$PLSQL_LINE);
      RAISE;
  END get_aico;

  FUNCTION get_laco(p_enty_code IN enquiry_types.code%TYPE
                   ,p_regi_code IN regions.code%TYPE
                   ,p_mety_code IN meter_types.code%TYPE
                   ,p_mesc_code IN meter_size_codes.code%TYPE DEFAULT NULL
                   ,p_svcp_code IN service_pressures.code%TYPE DEFAULT NULL)
    RETURN t_rec_additional_costs IS
    l_rec_costs t_rec_additional_costs;
  BEGIN
    pl('get_laco:entry:' || p_enty_code || ':' || p_regi_code || ':' ||
       p_mety_code || ':' || p_mesc_code || ':' || p_svcp_code
      ,$$PLSQL_LINE);
  
    SELECT selling_price
          ,cost_price
          ,delivery_cost
          ,svcpt_code
      INTO l_rec_costs.selling_price
          ,l_rec_costs.cost_price
          ,l_rec_costs.delivery_cost
          ,l_rec_costs.svcpt_code
      FROM (SELECT decode(regi_code
                         ,p_regi_code
                         ,0
                         ,1) * 100 + decode(cost.svcpt_code
                                           ,NULL
                                           ,1
                                           ,0) * 10 +
                   decode(cost.mesc_code
                         ,NULL
                         ,1
                         ,0) AS accuracy
                  ,enty_code
                  ,mety_code
                  ,cost.svcpt_code
                  ,mesc_code
                  ,selling_price
                  ,cost_price
                  ,delivery_cost
                  ,cost.ROWID
                  ,svcp.code AS svcp_code
              FROM v_laco            cost
                  ,service_pressures svcp
             WHERE SYSDATE BETWEEN cost.effective_from AND cost.effective_to
               AND (regi_code = p_regi_code OR regi_code IS NULL)
               AND cost.svcpt_code(+) = svcp.svcpt_code
               AND enty_code = p_enty_code
               AND mety_code = p_mety_code
               AND svcp.code = p_svcp_code
               AND ((cost.mesc_code = p_mesc_code) OR (mesc_code IS NULL))
             ORDER BY 1)
     WHERE rownum < 2;
  
    pl('get_laco:exit:' || l_rec_costs.selling_price);
  
    RETURN l_rec_costs;
  
  EXCEPTION
    WHEN no_data_found THEN
      RETURN l_rec_costs;
  END get_laco;

  FUNCTION get_svcpt_for_svcp(p_svcp_code IN service_pressures.code%TYPE)
    RETURN service_pressure_types.code%TYPE IS
    l_svcpt_code service_pressure_types.code%TYPE;
  BEGIN
    pl('get_svcpt_for_svcp:entry:p_svcp_code=' || p_svcp_code
      ,$$PLSQL_LINE);
  
    BEGIN
      SELECT svcpt_code
        INTO l_svcpt_code
        FROM service_pressures
       WHERE code = p_svcp_code;
    EXCEPTION
      WHEN no_data_found THEN
        l_svcpt_code := NULL;
    END;
  
    pl('get_svcpt_for_svcp:exit:' || l_svcpt_code
      ,$$PLSQL_LINE);
  
    RETURN l_svcpt_code;
  
  END get_svcpt_for_svcp;

  FUNCTION get_lead_time(p_enty_code enquiry_types.code%TYPE
                        ,p_mety_code meter_types.code%TYPE
                        ,p_svcp_code service_pressures.code%TYPE
                        ,p_qmax      NUMBER) RETURN NUMBER IS
    l_days NUMBER;
    l_qmax NUMBER;
  BEGIN
  
    pl('get_lead_time:' || p_enty_code || ':' || p_mety_code || ':' ||
       p_svcp_code || ':' || p_qmax
      ,$$PLSQL_LINE);
  
    l_qmax := greatest(nvl(p_qmax
                          ,1)
                      ,1);
  
    SELECT days
      INTO l_days
      FROM (SELECT enty_code
                  ,mety_code
                  ,svcp.code AS svcp_code
                  ,from_qmax
                  ,to_qmax
                  ,days
              FROM v_lead_times      v
                  ,service_pressures svcp
             WHERE v.svcpt_code = svcp.svcpt_code(+)) v
     WHERE l_qmax > v.from_qmax
       AND l_qmax <= v.to_qmax
       AND v.enty_code = p_enty_code
       AND v.mety_code = p_mety_code
       AND ((v.svcp_code = p_svcp_code) OR
           (v.svcp_code IS NULL AND p_svcp_code IS NULL));
  
    pl('get_lead_time:exit:' || l_days
      ,$$PLSQL_LINE);
    RETURN l_days;
  
  EXCEPTION
    WHEN no_data_found THEN
      pl('get_lead_time:exit:NDF'
        ,$$PLSQL_LINE);
      RETURN NULL;
    WHEN OTHERS THEN
      pl('get_lead_time:exit:' || SQLERRM
        ,$$PLSQL_LINE);
      RAISE;
  END get_lead_time;

  FUNCTION get_lead_time_desc(p_enty_code enquiry_types.code%TYPE
                             ,p_mety_code meter_types.code%TYPE
                             ,p_svcp_code service_pressures.code%TYPE
                             ,p_qmax      NUMBER) RETURN VARCHAR2 IS
    l_desc VARCHAR2(400);
  BEGIN
    FOR l_rec IN (SELECT description
                    FROM (SELECT 1
                                ,'Enquiry Type is ' || description AS description
                            FROM enquiry_types
                           WHERE code = p_enty_code
                          UNION
                          SELECT 2
                                ,'Meter Type is ' || description
                            FROM meter_types
                           WHERE code = p_mety_code
                          UNION
                          SELECT 3
                                ,'Service Pressure is ' || svcpt.description
                            FROM service_pressure_types svcpt
                                ,service_pressures      svcp
                           WHERE svcp.svcpt_code = svcpt.code
                             AND svcp.code = p_svcp_code
                          UNION
                          SELECT 4
                                ,'Qmax is ' || p_qmax
                            FROM dual
                           WHERE p_qmax IS NOT NULL)
                   ORDER BY 1) LOOP
      l_desc := l_desc || l_rec.description || ', ';
    END LOOP;
  
    l_desc := substr(l_desc
                    ,1
                    ,length(l_desc) - 2) || '.';
  
    RETURN l_desc;
  
  END get_lead_time_desc;

  PROCEDURE produce_module_quotes(p_enqu                      IN t_enqu
                                 ,p_rfq_prty_id               IN parties.id%TYPE
                                 ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                 ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
    l_produced_automatic_quote  BOOLEAN;
    l_this_is_automatic_quote   BOOLEAN;
    l_regi_code                 regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode);
    l_qute_id                   quotes.id%TYPE;
    l_additional_costs          t_rec_additional_costs;
    l_quote_document            VARCHAR2(240);
    l_existing_rec_metr_details t_rec_metr_details;
    l_required_mesc_code        meter_size_codes.code%TYPE;
    l_required_qmax             meter_size_codes.qmax%TYPE;
  BEGIN
    pl('produce_module_quotes:entry:' || p_enqu.id || ':' ||
       p_enqu.enty_code
      ,$$PLSQL_LINE);
    cout_assert.istrue(p_enqu.enty_code IN
                       ('INSTALL', 'STD INSTALL', 'EXCHANGE',
                        'CAPACITY CHANGE', 'STD EXCHANGE')
                      ,'Attempted to produce an install or exchange quote for enquiry of type ' ||
                       p_enqu.enty_code);
  
    cout_assert.isnotnull(l_regi_code
                         ,'Attempted to produce an install, exchange or change of capacity quote for enquiry for a installation postcode (' ||
                          p_enqu.install_postcode || ') without a region.');
  
    IF p_enqu.enty_code IN ('INSTALL', 'STD INSTALL') THEN
      add_quote_reason(p_enqu.id
                      ,p_reason => 'Attempting an automatic installation quote for  ' ||
                                   p_enqu.id || '.'
                      ,p_internal_or_external => gc_internal_reason);
    ELSIF p_enqu.enty_code IN ('EXCHANGE', 'STD EXCHANGE') THEN
      add_quote_reason(p_enqu.id
                      ,p_reason => 'Attempting an automatic exchange quote for  ' ||
                                   p_enqu.id || '.'
                      ,p_internal_or_external => gc_internal_reason);
    ELSIF p_enqu.enty_code IN ('CHANGE CAPACITY') THEN
      add_quote_reason(p_enqu.id
                      ,p_reason => 'Attempting an automatic change capacity quote for  ' ||
                                   p_enqu.id || '.'
                      ,p_internal_or_external => gc_internal_reason);
    END IF;
  
    pl('p_enqu.required_mesc_code:' || p_enqu.required_mesc_code
      ,$$PLSQL_LINE);
    pl('p_enqu.qmax:' || p_enqu.qmax
      ,$$PLSQL_LINE);
  
    IF p_enqu.required_mesc_code IS NOT NULL
       AND p_enqu.required_mesc_code <> 'OTHER' THEN
      l_required_qmax := get_qmax_from_mesc(p_enqu.required_mesc_code);
    ELSE
      l_required_qmax := p_enqu.qmax;
    END IF;
  
    pl('l_required_qmax:' || l_required_qmax
      ,$$PLSQL_LINE);
  
    IF NOT p_manual_or_automatic_quote = gc_manual_quote THEN
    
      cout_assert.istrue((l_required_mesc_code IS NULL AND
                         l_required_qmax IS NOT NULL) OR
                         (l_required_mesc_code IS NOT NULL AND
                         l_required_qmax IS NULL)
                        ,p_message => 'EITHER l_required_mesc_code must be null OR l_required_qmax must be null');
    
      pl('Looking for modules to match the following: Service Pressure ' ||
         p_enqu.required_svcp_code || ', Outlet Pressure ' ||
         p_enqu.required_metering_pressure || CASE
         l_required_mesc_code IS NULL WHEN TRUE THEN
         'and required Qmax ' || l_required_qmax ELSE
         'and required Meter Size Code ' || l_required_mesc_code
         END || '.');
    
      FOR l_rec_module IN (SELECT modu.code AS modu_code
                                 ,modu.selling_price AS modu_selling_price
                                 ,modu.cost_price AS modu_cost_price
                                 ,modu.delivery_cost AS modu_delivery_cost
                                 ,NULL AS modu_lead_time
                                 ,modu.hou_code AS hou_code
                                 ,modu.inlet_orientation AS modu_inlet_orientation
                                 ,modu.outlet_orientation AS modu_outlet_orientation
                                 ,modu.qmax
                                 ,NULL AS hou_selling_price
                                 ,NULL AS hou_cost_price
                                 ,NULL AS hou_delivery_cost
                                 ,modu.bas_code AS bas_code
                                 ,NULL AS bas_selling_price
                                 ,NULL AS bas_cost_price
                                 ,NULL AS bas_delivery_cost
                                 ,metr.code AS metr_code
                                 ,metr.qnom
                                 ,metr.qmax AS metr_qmax
                                 ,metr.qmin
                                 ,metr.mesc_code
                                 ,metr.mety_code
                                 ,NULL AS laco_mety_code
                                 ,NULL AS laco_svcpt_code
                                 ,NULL AS laco_mesc_code
                                 ,NULL AS laco_selling_price
                                 ,NULL AS laco_cost_price
                                 ,NULL AS laco_delivery_cost
                                 ,NULL AS amr_cost_id
                                 ,NULL AS amr_selling_price
                                 ,NULL AS amr_cost_price
                                 ,NULL AS amr_delivery_cost
                                 ,NULL AS amr_lead_time
                                 ,NULL AS ems_cost_id
                                 ,NULL AS ems_selling_price
                                 ,NULL AS ems_cost_price
                                 ,NULL AS ems_delivery_cost
                                 ,NULL AS ems_lead_time
                                 ,NULL AS bypass_cost_id
                                 ,NULL AS bypass_selling_price
                                 ,NULL AS bypass_cost_price
                                 ,NULL AS bypass_delivery_cost
                                 ,NULL AS bypass_lead_time
                                 ,NULL AS lifting_gear_cost_id
                                 ,NULL AS lifting_gear_selling_price
                                 ,NULL AS lifting_gear_cost_price
                                 ,NULL AS lifting_gear_delivery_cost
                                 ,NULL AS lifting_gear_lead_time
                             FROM (SELECT modu.code
                                         ,modu.metr_code
                                         ,modu.hou_code
                                         ,modu.bas_code
                                         ,modu.qmax
                                         ,svcp_code
                                         ,outlet_pressure
                                         ,cnor_i.description AS inlet_orientation
                                         ,cnor_o.description AS outlet_orientation
                                         ,selling_price
                                         ,cost_price
                                         ,delivery_cost
                                     FROM modules modu
                                         ,connection_orientations cnor_i
                                         ,connection_orientations cnor_o
                                         ,(SELECT modu_code
                                                 ,selling_price
                                                 ,cost_price
                                                 ,delivery_cost
                                             FROM (SELECT row_number() over(PARTITION BY modu_code ORDER BY(decode(regi_code, l_regi_code, 1, 999))) AS accuracy
                                                         ,modu_code
                                                         ,selling_price
                                                         ,cost_price
                                                         ,delivery_cost
                                                         ,ROWID
                                                     FROM v_moco cost
                                                    WHERE SYSDATE BETWEEN
                                                          cost.effective_from AND
                                                          cost.effective_to
                                                      AND (regi_code =
                                                          l_regi_code OR
                                                          regi_code IS NULL))
                                            WHERE accuracy <= 1) cost
                                    WHERE modu.code = cost.modu_code(+)
                                      AND modu.inlet_cnor_code = cnor_i.code
                                      AND modu.outlet_cnor_code = cnor_o.code
                                      AND SYSDATE BETWEEN modu.valid_from AND
                                          nvl(modu.valid_to
                                             ,SYSDATE)) modu
                                 ,(SELECT metr.code
                                         ,metr.qmax
                                         ,metr.qmin
                                         ,metr.qnom
                                         ,metr.mety_code
                                         ,metr.mesc_code
                                     FROM meters metr
                                    WHERE SYSDATE BETWEEN metr.valid_from AND
                                          nvl(metr.valid_to
                                             ,SYSDATE)) metr
                            WHERE modu.svcp_code = p_enqu.required_svcp_code
                              AND modu.outlet_pressure =
                                  p_enqu.required_metering_pressure
                              AND metr.code = modu.metr_code
                                 --
                                 -- either been given a meter size code to match
                                 -- or a module qmax value to exceed
                                 --
                              AND ((metr.mesc_code = l_required_mesc_code) OR
                                  (modu.qmax >= l_required_qmax))) LOOP
        l_this_is_automatic_quote := TRUE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Considering module : ' ||
                                     l_rec_module.modu_code
                        ,p_internal_or_external => gc_internal_reason);
        --
        -- check whether we have the required prices
        -- if we do not, then we may need to produce a manual quote
        --
        IF l_rec_module.modu_selling_price IS NULL THEN
          l_this_is_automatic_quote := FALSE;
          add_quote_reason(p_enqu.id
                          ,p_reason => 'Unable to find selling price for module ' ||
                                       l_rec_module.modu_code || CASE
                                       l_regi_code WHEN NULL THEN '' ELSE ' for region code ' || l_regi_code END || '.'
                          ,p_internal_or_external => gc_internal_reason);
        END IF;
      
        l_rec_module.modu_lead_time := get_lead_time(p_enty_code => p_enqu.enty_code
                                                    ,p_mety_code => l_rec_module.mety_code
                                                    ,p_svcp_code => p_enqu.required_svcp_code
                                                    ,p_qmax      => l_rec_module.qmax);
        IF l_rec_module.modu_lead_time IS NULL THEN
          l_this_is_automatic_quote := FALSE;
          add_quote_reason(p_enqu.id
                          ,p_reason => 'Unable to find lead time for module ' ||
                                       l_rec_module.modu_code || '. (' ||
                                       get_lead_time_desc(p_enty_code => p_enqu.enty_code
                                                         ,p_mety_code => p_enqu.existing_mety_code
                                                         ,p_svcp_code => p_enqu.required_svcp_code
                                                         ,p_qmax      => p_enqu.qmax) || ')'
                          ,p_internal_or_external => gc_internal_reason);
        END IF;
      
        l_additional_costs              := get_laco(p_enty_code => p_enqu.enty_code
                                                   ,p_regi_code => l_regi_code
                                                   ,p_mety_code => l_rec_module.mety_code
                                                   ,p_mesc_code => l_rec_module.mesc_code
                                                   ,p_svcp_code => p_enqu.required_svcp_code);
        l_rec_module.laco_svcpt_code    := l_additional_costs.svcpt_code;
        l_rec_module.laco_mety_code     := l_rec_module.mety_code;
        l_rec_module.laco_selling_price := l_additional_costs.selling_price;
        l_rec_module.laco_cost_price    := l_additional_costs.cost_price;
        l_rec_module.laco_delivery_cost := l_additional_costs.delivery_cost;
        IF l_rec_module.laco_selling_price IS NULL THEN
          l_this_is_automatic_quote := FALSE;
          add_quote_reason(p_enqu.id
                          ,p_reason => 'Unable to find Labour Cost (selling price) for this Enquiry Type Code: ' ||
                                       p_enqu.enty_code ||
                                       ', Meter Type Code:' ||
                                       l_rec_module.mety_code ||
                                       ', Meter Size Code:' ||
                                       l_rec_module.mesc_code ||
                                       ', Service Pressure Code:' ||
                                       p_enqu.required_svcp_code || CASE
                                       l_regi_code WHEN NULL THEN '' ELSE ' for region code ' || l_regi_code END || '.'
                           
                          ,p_internal_or_external => gc_internal_reason);
        END IF;
      
        IF p_enqu.base_required = 'YES' THEN
          IF l_rec_module.bas_code IS NOT NULL THEN
            l_additional_costs             := get_base(p_bas_code  => l_rec_module.bas_code
                                                      ,p_regi_code => l_regi_code);
            l_rec_module.bas_selling_price := l_additional_costs.selling_price;
            l_rec_module.bas_cost_price    := l_additional_costs.cost_price;
            l_rec_module.bas_delivery_cost := l_additional_costs.delivery_cost;
            IF l_rec_module.bas_selling_price IS NULL THEN
              l_this_is_automatic_quote := FALSE;
              add_quote_reason(p_enqu.id
                              ,p_reason => 'Unable to find selling price for base ' ||
                                           l_rec_module.bas_code || '.'
                              ,p_internal_or_external => gc_internal_reason);
            END IF;
          ELSE
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Module is not available with base.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
        END IF;
      
        IF p_enqu.housing_required = 'YES' THEN
          IF l_rec_module.hou_code IS NOT NULL THEN
            l_additional_costs             := get_housing(p_hou_code  => l_rec_module.hou_code
                                                         ,p_regi_code => l_regi_code);
            l_rec_module.hou_selling_price := l_additional_costs.selling_price;
            l_rec_module.hou_cost_price    := l_additional_costs.cost_price;
            l_rec_module.hou_delivery_cost := l_additional_costs.delivery_cost;
            IF l_rec_module.hou_selling_price IS NULL THEN
              l_this_is_automatic_quote := FALSE;
              add_quote_reason(p_enqu.id
                              ,p_reason => 'Unable to find selling price for housing ' ||
                                           l_rec_module.hou_code || '.'
                              ,p_internal_or_external => gc_internal_reason);
            END IF;
          ELSE
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Module is not available with housing.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
        END IF;
      
        IF p_enqu.amr_required = 'YES' THEN
          l_additional_costs             := get_aico(p_adit_code => 'AMR'
                                                    ,p_regi_code => l_regi_code);
          l_rec_module.amr_selling_price := l_additional_costs.selling_price;
          l_rec_module.amr_cost_price    := l_additional_costs.cost_price;
          l_rec_module.amr_delivery_cost := l_additional_costs.delivery_cost;
          l_rec_module.amr_lead_time     := l_additional_costs.lead_time;
          IF l_rec_module.amr_selling_price IS NULL THEN
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Unable to find selling price for AMR.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
          IF l_rec_module.amr_lead_time IS NULL THEN
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Unable to find lead time for AMR.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
        END IF;
        IF p_enqu.ems_required = 'YES' THEN
          l_additional_costs             := get_aico(p_adit_code => 'EMS'
                                                    ,p_regi_code => l_regi_code);
          l_rec_module.ems_selling_price := l_additional_costs.selling_price;
          l_rec_module.ems_cost_price    := l_additional_costs.cost_price;
          l_rec_module.ems_delivery_cost := l_additional_costs.delivery_cost;
          l_rec_module.ems_lead_time     := l_additional_costs.lead_time;
          IF l_rec_module.ems_selling_price IS NULL THEN
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Unable to find selling price for EMS.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
          IF l_rec_module.ems_lead_time IS NULL THEN
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Unable to find lead time for EMS.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
        END IF;
        IF p_enqu.bypass_required = 'YES' THEN
          l_additional_costs                := get_aico(p_adit_code => 'BYPASS'
                                                       ,p_regi_code => l_regi_code);
          l_rec_module.bypass_selling_price := l_additional_costs.selling_price;
          l_rec_module.bypass_cost_price    := l_additional_costs.cost_price;
          l_rec_module.bypass_delivery_cost := l_additional_costs.delivery_cost;
          l_rec_module.bypass_lead_time     := l_additional_costs.lead_time;
          IF l_rec_module.bypass_selling_price IS NULL THEN
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Unable to find selling price for BYPASS.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
          IF l_rec_module.bypass_lead_time IS NULL THEN
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Unable to find lead time for BYPASS.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
        
        END IF;
      
        /* Lifting gear required UNLESS LP Diaphragm */
        IF NOT (l_rec_module.mety_code = 'DIAPHRAGM' AND
            l_rec_module.laco_svcpt_code = 'LP') THEN
        
          l_additional_costs                      := get_aico(p_adit_code => 'LIFTING GEAR'
                                                             ,p_regi_code => l_regi_code);
          l_rec_module.lifting_gear_selling_price := l_additional_costs.selling_price;
          l_rec_module.lifting_gear_cost_price    := l_additional_costs.cost_price;
          l_rec_module.lifting_gear_delivery_cost := l_additional_costs.delivery_cost;
          l_rec_module.lifting_gear_lead_time     := l_additional_costs.lead_time;
          IF l_rec_module.lifting_gear_selling_price IS NULL THEN
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Unable to find selling price for LIFTING GEAR.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
          IF l_rec_module.lifting_gear_lead_time IS NULL THEN
            l_this_is_automatic_quote := FALSE;
            add_quote_reason(p_enqu.id
                            ,p_reason => 'Unable to find lead time for LIFTING GEAR.'
                            ,p_internal_or_external => gc_internal_reason);
          END IF;
        END IF;
      
        IF l_this_is_automatic_quote THEN
          l_produced_automatic_quote := TRUE;
        
          add_quote_reason(p_enqu_id              => p_enqu.id
                          ,p_reason               => 'Producing an automatic quote.'
                          ,p_internal_or_external => gc_internal_reason);
        
          l_qute_id := start_quote(p_enqu_id             => p_enqu.id
                                  ,p_manual_or_automatic => gc_automatic_quote
                                  ,p_rfq_prty_id         => p_rfq_prty_id
                                  ,p_owner_prty_id       => p_owner_prty_id);
        
          INSERT INTO quote_items
            (id
            ,qute_id
            ,enty_code
            ,mety_code
            ,svcpt_code
            ,mesc_code
            ,cost_price
            ,selling_price
            ,delivery_price
            ,lead_time
            ,quit_type)
          VALUES
            (quit_seq.NEXTVAL
            ,l_qute_id
            ,p_enqu.enty_code
            ,l_rec_module.laco_mety_code
            ,l_rec_module.laco_svcpt_code
            ,l_rec_module.laco_mesc_code
            ,l_rec_module.laco_cost_price
            ,l_rec_module.laco_selling_price
            ,l_rec_module.laco_delivery_cost
            ,l_rec_module.modu_lead_time
            ,'LQI');
        
          INSERT INTO quote_items
            (id
            ,qute_id
            ,modu_code
            ,qmax
            ,qmin
            ,inlet_orientation
            ,outlet_orientation
            ,cost_price
            ,selling_price
            ,delivery_price
            ,quit_type)
          VALUES
            (quit_seq.NEXTVAL
            ,l_qute_id
            ,l_rec_module.modu_code
            ,l_rec_module.qmax
            ,l_rec_module.qmin
            ,l_rec_module.modu_inlet_orientation
            ,l_rec_module.modu_outlet_orientation
            ,l_rec_module.modu_cost_price
            ,l_rec_module.modu_selling_price
            ,l_rec_module.modu_delivery_cost
            ,'MQI');
        
          IF p_enqu.housing_required = 'YES' THEN
            INSERT INTO quote_items
              (id
              ,qute_id
              ,hou_code
              ,cost_price
              ,selling_price
              ,delivery_price
              ,quit_type)
            VALUES
              (quit_seq.NEXTVAL
              ,l_qute_id
              ,l_rec_module.hou_code
              ,l_rec_module.hou_cost_price
              ,l_rec_module.hou_selling_price
              ,l_rec_module.hou_delivery_cost
              ,'HQI');
          END IF;
        
          IF p_enqu.base_required = 'YES' THEN
            INSERT INTO quote_items
              (id
              ,qute_id
              ,bas_code
              ,cost_price
              ,selling_price
              ,delivery_price
              ,quit_type)
            VALUES
              (quit_seq.NEXTVAL
              ,l_qute_id
              ,l_rec_module.bas_code
              ,l_rec_module.bas_cost_price
              ,l_rec_module.bas_selling_price
              ,l_rec_module.bas_delivery_cost
              ,'BQI');
          END IF;
        
          IF p_enqu.amr_required = 'YES' THEN
            INSERT INTO quote_items
              (id
              ,qute_id
              ,adit_code
              ,cost_price
              ,selling_price
              ,delivery_price
              ,lead_time
              ,quit_type)
            VALUES
              (quit_seq.NEXTVAL
              ,l_qute_id
              ,'AMR'
              ,l_rec_module.amr_cost_price
              ,l_rec_module.amr_selling_price
              ,l_rec_module.amr_delivery_cost
              ,l_rec_module.amr_lead_time
              ,'AQI');
          END IF;
        
          IF p_enqu.ems_required = 'YES' THEN
            INSERT INTO quote_items
              (id
              ,qute_id
              ,adit_code
              ,cost_price
              ,selling_price
              ,delivery_price
              ,lead_time
              ,quit_type)
            VALUES
              (quit_seq.NEXTVAL
              ,l_qute_id
              ,'EMS'
              ,l_rec_module.ems_cost_price
              ,l_rec_module.ems_selling_price
              ,l_rec_module.ems_delivery_cost
              ,l_rec_module.ems_lead_time
              ,'AQI');
          END IF;
        
          IF p_enqu.bypass_required = 'YES' THEN
            INSERT INTO quote_items
              (id
              ,qute_id
              ,adit_code
              ,cost_price
              ,selling_price
              ,delivery_price
              ,lead_time
              ,quit_type)
            VALUES
              (quit_seq.NEXTVAL
              ,l_qute_id
              ,'BYPASS'
              ,l_rec_module.bypass_cost_price
              ,l_rec_module.bypass_selling_price
              ,l_rec_module.bypass_delivery_cost
              ,l_rec_module.bypass_lead_time
              ,'AQI');
          END IF;
        
          IF l_rec_module.lifting_gear_selling_price IS NOT NULL THEN
          
            INSERT INTO quote_items
              (id
              ,qute_id
              ,adit_code
              ,cost_price
              ,selling_price
              ,delivery_price
              ,lead_time
              ,quit_type)
            VALUES
              (quit_seq.NEXTVAL
              ,l_qute_id
              ,'LIFTING GEAR'
              ,l_rec_module.lifting_gear_cost_price
              ,l_rec_module.lifting_gear_selling_price
              ,l_rec_module.lifting_gear_delivery_cost
              ,l_rec_module.lifting_gear_lead_time
              ,'AQI');
          END IF;
        
          pl('call mip_quotation_document.generate_quote_pdf'
            ,$$PLSQL_LINE);
          -- Generate the quote PDF
          /*BEGIN*/
          l_quote_document := mip_quotation_document.generate_quote_pdf(p_quote_id => l_qute_id);
          /*        EXCEPTION
                    WHEN OTHERS THEN
                      cout_err.report_and_stop;
                  END;
          */
          pl('return mip_quotation_document.generate_quote_pdf'
            ,$$PLSQL_LINE);
        
          add_quote_reason(p_enqu_id              => p_enqu.id
                          ,p_reason               => 'Produced Quote Document ' ||
                                                     l_quote_document || '.'
                          ,p_internal_or_external => gc_internal_reason);
        
          make_quote_available(l_qute_id);
        
        END IF; -- automatic quote
      
      END LOOP;
    END IF; -- not manual quote
  
    IF l_produced_automatic_quote THEN
      p_manual_or_automatic_quote := gc_automatic_quote;
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => '-- Produced an automatic quote.'
                      ,p_internal_or_external => gc_internal_reason);
      --email_aq_generated(p_enqu.id);
    ELSE
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => '-- Automatic quote failed - Manual quote required.'
                      ,p_internal_or_external => gc_internal_reason);
    
    END IF;
  
    pl('produce_module_quotes:exit'
      ,$$PLSQL_LINE);
  END produce_module_quotes;

  PROCEDURE produce_install_quotes(p_enqu                      IN t_enqu
                                  ,p_rfq_prty_id               IN parties.id%TYPE
                                  ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                  ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
    l_regi_code regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode);
  BEGIN
    pl('produce_install_quotes'
      ,$$PLSQL_LINE);
    cout_assert.istrue(p_enqu.enty_code IN ('STD INSTALL', 'INSTALL')
                      ,'Attempted to produce an installation quote for enquiry of type ' ||
                       p_enqu.enty_code);
  
    cout_assert.isnotnull(l_regi_code
                         ,'Attempted to produce an installation quote for enquiry for a installation postcode (' ||
                          p_enqu.install_postcode || ') without a region.');
  
    produce_module_quotes(p_enqu                      => p_enqu
                         ,p_rfq_prty_id               => p_rfq_prty_id
                         ,p_owner_prty_id             => p_owner_prty_id
                         ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
  
    pl('produce_install_quotes:exit'
      ,$$PLSQL_LINE);
  END produce_install_quotes;

  PROCEDURE produce_exchange_quotes(p_enqu                      IN t_enqu
                                   ,p_rfq_prty_id               IN parties.id%TYPE
                                   ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                   ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
    l_regi_code regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode);
  BEGIN
    pl('produce_exchange_quotes:entry:' || p_enqu.enty_code
      ,$$PLSQL_LINE);
    cout_assert.istrue(p_enqu.enty_code IN ('EXCHANGE', 'STD EXCHANGE')
                      ,'Attempted to produce an exchange quote for enquiry of type ' ||
                       p_enqu.enty_code);
  
    cout_assert.isnotnull(l_regi_code
                         ,'Attempted to produce an exchange quote for enquiry for a installation postcode (' ||
                          p_enqu.install_postcode || ') without a region.');
  
    produce_module_quotes(p_enqu                      => p_enqu
                         ,p_rfq_prty_id               => p_rfq_prty_id
                         ,p_owner_prty_id             => p_owner_prty_id
                         ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
    pl('produce_exchange_quotes:exit'
      ,$$PLSQL_LINE);
  END produce_exchange_quotes;

  PROCEDURE produce_change_capacity_quotes(p_enqu                      IN t_enqu
                                          ,p_rfq_prty_id               IN parties.id%TYPE
                                          ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                          ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
    l_regi_code regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode);
  BEGIN
    pl('produce_change_capacity_quotes'
      ,$$PLSQL_LINE);
    cout_assert.istrue(p_enqu.enty_code IN ('CHANGE CAPACITY')
                      ,'Attempted to produce a change capacity quote for enquiry of type ' ||
                       p_enqu.enty_code);
  
    cout_assert.isnotnull(l_regi_code
                         ,'Attempted to produce a change capacity quote for enquiry for a installation postcode (' ||
                          p_enqu.install_postcode || ') without a region.');
  
    produce_module_quotes(p_enqu                      => p_enqu
                         ,p_rfq_prty_id               => p_rfq_prty_id
                         ,p_owner_prty_id             => p_owner_prty_id
                         ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
  
    pl('produce_change_capacity_quotes:exit'
      ,$$PLSQL_LINE);
  END produce_change_capacity_quotes;

  PROCEDURE produce_labour_only_quotes(p_enqu                      IN t_enqu
                                      ,p_rfq_prty_id               IN parties.id%TYPE
                                      ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                      ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
    l_produced_automatic_quote  BOOLEAN;
    l_this_is_automatic_quote   BOOLEAN;
    l_regi_code                 regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode);
    l_lead_time                 NUMBER;
    l_qute_id                   quotes.id%TYPE;
    l_labour_costs              t_rec_additional_costs;
    l_quote_document            VARCHAR2(240);
    l_additional_costs          t_rec_additional_costs;
    l_required_qmax             NUMBER;
    l_existing_rec_metr_details t_rec_metr_details;
  BEGIN
    pl('produce_labour_only_quotes:entry:' || p_enqu.id || ':' ||
       p_enqu.enty_code
      ,$$PLSQL_LINE);
    cout_assert.istrue(p_enqu.enty_code IN
                       ('REMOVE', 'STD REMOVE', 'ADVERSARIAL', 'OFMAT',
                        'ALTERATION')
                      ,'Attempted to produce a labour-only quote for enquiry of type ' ||
                       p_enqu.enty_code);
  
    cout_assert.isnotnull(l_regi_code
                         ,'Attempted to produce quote for enquiry for a installation postcode (' ||
                          p_enqu.install_postcode || ') without a region.');
  
    l_this_is_automatic_quote := TRUE;
  
    l_existing_rec_metr_details := get_existing_metr_details(p_enqu);
  
    add_quote_reason(p_enqu.id
                    ,p_reason => 'Attempting an automatic labour quote for ' ||
                                 p_enqu.id || '.' || 'Enquiry Code=' ||
                                 p_enqu.enty_code || ', SVCP ' ||
                                 p_enqu.required_svcp_code ||
                                 ', Meter Type Code=' ||
                                 l_existing_rec_metr_details.mety_code ||
                                 ', Meter Size Code=' ||
                                 l_existing_rec_metr_details.mesc_code || '.'
                    ,p_internal_or_external => gc_internal_reason);
  
    l_labour_costs := get_laco(p_enty_code => p_enqu.enty_code
                              ,p_regi_code => l_regi_code
                              ,p_mety_code => l_existing_rec_metr_details.mety_code
                              ,p_mesc_code => l_existing_rec_metr_details.mesc_code
                              ,p_svcp_code => p_enqu.required_svcp_code);
    IF l_labour_costs.selling_price IS NULL THEN
      l_this_is_automatic_quote := FALSE;
      add_quote_reason(p_enqu.id
                      ,p_reason => 'Unable to find Labour Cost (selling price) for this Enquiry Type Code: ' ||
                                   p_enqu.enty_code || ', Meter Type Code:' ||
                                   l_existing_rec_metr_details.mety_code ||
                                   ', Meter Size Code:' ||
                                   l_existing_rec_metr_details.mesc_code ||
                                   ', Service Pressure Code:' ||
                                   p_enqu.required_svcp_code || CASE
                                   l_regi_code WHEN NULL THEN '' ELSE ' for region code ' || l_regi_code END || '.'
                       
                      ,p_internal_or_external => gc_internal_reason);
    END IF;
    --over-ride the enquiry qmax if an existing meter is specified as
    --we can get the real qmax value (off the module)
    IF l_existing_rec_metr_details.mesc_code IS NOT NULL
       AND l_existing_rec_metr_details.mesc_code <> 'OTHER' THEN
      l_required_qmax := get_qmax_from_mesc(l_existing_rec_metr_details.mesc_code);
    ELSE
      l_required_qmax := p_enqu.qmax;
    END IF;
   
    l_lead_time := get_lead_time(p_enty_code => p_enqu.enty_code
                                ,p_mety_code => l_existing_rec_metr_details.mety_code
                                ,p_svcp_code => p_enqu.required_svcp_code
                                ,p_qmax      => l_required_qmax);
    IF l_lead_time IS NULL THEN
      l_this_is_automatic_quote := FALSE;
      add_quote_reason(p_enqu.id
                      ,p_reason => 'Unable to find lead time for ' ||
                                   get_lead_time_desc(p_enty_code => p_enqu.enty_code
                                                     ,p_mety_code => l_existing_rec_metr_details.mety_code
                                                     ,p_svcp_code => p_enqu.required_svcp_code
                                                     ,p_qmax      => p_enqu.qmax)
                      ,p_internal_or_external => gc_internal_reason);
    END IF;
  
    /* Lifting gear required UNLESS LP Diaphragm */
    IF NOT (l_existing_rec_metr_details.mety_code = 'DIAPHRAGM' AND
        l_labour_costs.svcpt_code = 'LP') THEN
    
      l_additional_costs := get_aico(p_adit_code => 'LIFTING GEAR'
                                    ,p_regi_code => l_regi_code);
      IF l_additional_costs.selling_price IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find selling price for LIFTING GEAR.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
    
      IF l_additional_costs.lead_time IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find lead time for LIFTING GEAR.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
    END IF;
  
    IF l_this_is_automatic_quote THEN
      l_produced_automatic_quote := TRUE;
    
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => 'producing an automatic quote. '
                      ,p_internal_or_external => gc_internal_reason);
    
      l_qute_id := start_quote(p_enqu_id             => p_enqu.id
                              ,p_manual_or_automatic => gc_automatic_quote
                              ,p_rfq_prty_id         => p_rfq_prty_id
                              ,p_owner_prty_id       => p_owner_prty_id);
    
      INSERT INTO quote_items
        (id
        ,qute_id
        ,enty_code
        ,svcpt_code
        ,mesc_code
        ,mety_code
        ,cost_price
        ,selling_price
        ,delivery_price
        ,lead_time
        ,quit_type)
      VALUES
        (quit_seq.NEXTVAL
        ,l_qute_id
        ,p_enqu.enty_code
        ,l_labour_costs.svcpt_code
        ,l_existing_rec_metr_details.mesc_code
        ,l_existing_rec_metr_details.mety_code
        ,l_labour_costs.cost_price
        ,l_labour_costs.selling_price
        ,l_labour_costs.delivery_cost
        ,l_lead_time
        ,'LQI');
    
      IF l_additional_costs.selling_price IS NOT NULL THEN
        INSERT INTO quote_items
          (id
          ,qute_id
          ,adit_code
          ,cost_price
          ,selling_price
          ,delivery_price
          ,lead_time
          ,quit_type)
        VALUES
          (quit_seq.NEXTVAL
          ,l_qute_id
          ,'LIFTING GEAR'
          ,l_additional_costs.cost_price
          ,l_additional_costs.selling_price
          ,l_additional_costs.delivery_cost
          ,l_additional_costs.lead_time
          ,'AQI');
      END IF;
    
      -- Generate the quote PDF
      l_quote_document := mip_quotation_document.generate_quote_pdf(p_quote_id => l_qute_id);
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => 'produced quote document ' ||
                                                 l_quote_document || ' . '
                      ,p_internal_or_external => gc_internal_reason);
    
      make_quote_available(l_qute_id);
    
    END IF; -- automatic quote
  
    IF l_produced_automatic_quote THEN
      p_manual_or_automatic_quote := gc_automatic_quote;
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => ' -- Produced an automatic quote.'
                      ,p_internal_or_external => gc_internal_reason);
    ELSE
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => '-- Automatic quote failed - Manual quote required.'
                      ,p_internal_or_external => gc_internal_reason);
    
    END IF;
  
    pl('produce_labour_only_quotes:exit'
      ,$$PLSQL_LINE);
  
  END produce_labour_only_quotes;

  PROCEDURE produce_removal_quotes(p_enqu                      IN t_enqu
                                  ,p_rfq_prty_id               IN parties.id%TYPE
                                  ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                  ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
  BEGIN
    pl('produce_removal_quotes:entry'
      ,$$PLSQL_LINE);
  
    cout_assert.istrue(p_enqu.enty_code IN
                       ('REMOVE', 'STD REMOVE', 'ADVERSARIAL')
                      ,'Attempted to produce a removal quote for enquiry of type ' ||
                       p_enqu.enty_code);
    produce_labour_only_quotes(p_enqu                      => p_enqu
                              ,p_rfq_prty_id               => p_rfq_prty_id
                              ,p_owner_prty_id             => p_owner_prty_id
                              ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
    pl('produce_removal_quotes:exit'
      ,$$PLSQL_LINE);
  END produce_removal_quotes;

  PROCEDURE produce_ofmat_quotes(p_enqu                      IN t_enqu
                                ,p_rfq_prty_id               IN parties.id%TYPE
                                ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
  BEGIN
    pl('produce_ofmat_quotes:entry'
      ,$$PLSQL_LINE);
    cout_assert.istrue(p_enqu.enty_code IN ('OFMAT')
                      ,'Attempted to produce an OFMAT quote for enquiry of type ' ||
                       p_enqu.enty_code);
  
    produce_labour_only_quotes(p_enqu                      => p_enqu
                              ,p_rfq_prty_id               => p_rfq_prty_id
                              ,p_owner_prty_id             => p_owner_prty_id
                              ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
  
    pl('produce_ofmat_quotes:exit'
      ,$$PLSQL_LINE);
  END produce_ofmat_quotes;

  PROCEDURE produce_alteration_quotes(p_enqu                      IN t_enqu
                                     ,p_rfq_prty_id               IN parties.id%TYPE
                                     ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                     ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
  BEGIN
    pl('produce_alteration_quotes:entry'
      ,$$PLSQL_LINE);
    cout_assert.istrue(p_enqu.enty_code IN ('ALTERATION')
                      ,'Attempted to produce an ALTERATION quote for enquiry of type ' ||
                       p_enqu.enty_code);
  
    produce_labour_only_quotes(p_enqu                      => p_enqu
                              ,p_rfq_prty_id               => p_rfq_prty_id
                              ,p_owner_prty_id             => p_owner_prty_id
                              ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
  
    pl('produce_alteration_quotes:exit'
      ,$$PLSQL_LINE);
  END produce_alteration_quotes;

  PROCEDURE produce_addon_quotes(p_enqu                      IN t_enqu
                                ,p_rfq_prty_id               IN parties.id%TYPE
                                ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
    l_produced_automatic_quote BOOLEAN;
    l_this_is_automatic_quote  BOOLEAN;
    l_regi_code                regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode);
    l_qute_id                  quotes.id%TYPE;
    l_svcpt_code               service_pressure_types.code%TYPE;
    l_lifting_gear_costs       t_rec_additional_costs;
    l_amr_costs                t_rec_additional_costs;
    l_ems_costs                t_rec_additional_costs;
    l_bypass_costs             t_rec_additional_costs;
    l_quote_document           VARCHAR2(240);
  BEGIN
    pl('produce_addon_quotes:entry'
      ,$$PLSQL_LINE);
  
    cout_assert.istrue(p_enqu.enty_code IN ('ADDON')
                      ,'Attempted to produce an ADDON quote for enquiry of type ' ||
                       p_enqu.enty_code);
  
    cout_assert.isnotnull(l_regi_code
                         ,'Attempted to produce a quote for enquiry for a installation postcode (' ||
                          p_enqu.install_postcode || ') without a region.');
  
    add_quote_reason(p_enqu.id
                    ,p_reason => 'Attempting an automatic addon quote for  ' ||
                                 p_enqu.id || '.' || CASE
                                 p_enqu.amr_required WHEN 'YES' THEN 'AMR is required. ' END || CASE p_enqu.ems_required WHEN 'YES' THEN 'EMS is required. ' END || CASE p_enqu.bypass_required WHEN 'YES' THEN 'Bypass is required. ' END
                    ,p_internal_or_external => gc_internal_reason);
  
    l_this_is_automatic_quote := TRUE;
  
    --
    -- check whether we have the required prices
    -- if we do not, then we may need to produce a manual quote
    --
    IF p_enqu.amr_required = 'YES' THEN
      l_amr_costs := get_aico(p_adit_code => 'AMR'
                             ,p_regi_code => l_regi_code);
      IF l_amr_costs.selling_price IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find selling price for AMR.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
      IF l_amr_costs.lead_time IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find lead time for AMR.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
    END IF;
    IF p_enqu.ems_required = 'YES' THEN
      l_ems_costs := get_aico(p_adit_code => 'EMS'
                             ,p_regi_code => l_regi_code);
      IF l_ems_costs.selling_price IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find selling price for EMS.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
      IF l_ems_costs.lead_time IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find lead time for EMS.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
    END IF;
    IF p_enqu.bypass_required = 'YES' THEN
      l_bypass_costs := get_aico(p_adit_code => 'BYPASS'
                                ,p_regi_code => l_regi_code);
      IF l_bypass_costs.selling_price IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find selling price for BYPASS.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
      IF l_bypass_costs.lead_time IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find lead time for BYPASS.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
    
    END IF;
  
    l_svcpt_code := get_svcpt_for_svcp(p_enqu.required_svcp_code);
  
    /* Lifting gear required UNLESS LP Diaphragm */
    IF NOT (p_enqu.existing_mety_code = 'DIAPHRAGM' AND l_svcpt_code = 'LP') THEN
      l_lifting_gear_costs := get_aico(p_adit_code => 'LIFTING GEAR'
                                      ,p_regi_code => l_regi_code);
      IF l_lifting_gear_costs.selling_price IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find selling price for LIFTING GEAR.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
      IF l_lifting_gear_costs.lead_time IS NULL THEN
        l_this_is_automatic_quote := FALSE;
        add_quote_reason(p_enqu.id
                        ,p_reason => 'Unable to find lead time for LIFTING GEAR.'
                        ,p_internal_or_external => gc_internal_reason);
      END IF;
    
    END IF;
  
    IF l_this_is_automatic_quote THEN
      l_produced_automatic_quote := TRUE;
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => 'Producing an automatic quote.'
                      ,p_internal_or_external => gc_internal_reason);
    
      l_qute_id := start_quote(p_enqu_id             => p_enqu.id
                              ,p_manual_or_automatic => gc_automatic_quote
                              ,p_rfq_prty_id         => p_rfq_prty_id
                              ,p_owner_prty_id       => p_owner_prty_id);
    
      IF p_enqu.amr_required = 'YES' THEN
        INSERT INTO quote_items
          (id
          ,qute_id
          ,enty_code
          ,mety_code
          ,adit_code
          ,cost_price
          ,selling_price
          ,delivery_price
          ,quit_type)
        VALUES
          (quit_seq.NEXTVAL
          ,l_qute_id
          ,p_enqu.enty_code
          ,p_enqu.existing_mety_code
          ,l_amr_costs.adit_code
          ,l_amr_costs.cost_price
          ,l_amr_costs.selling_price
          ,l_amr_costs.delivery_cost
          ,'AQI');
      END IF;
    
      IF p_enqu.ems_required = 'YES' THEN
        INSERT INTO quote_items
          (id
          ,qute_id
          ,enty_code
          ,mety_code
          ,adit_code
          ,cost_price
          ,selling_price
          ,delivery_price
          ,lead_time
          ,quit_type)
        VALUES
          (quit_seq.NEXTVAL
          ,l_qute_id
          ,p_enqu.enty_code
          ,p_enqu.existing_mety_code
          ,l_ems_costs.adit_code
          ,l_ems_costs.cost_price
          ,l_ems_costs.selling_price
          ,l_ems_costs.delivery_cost
          ,l_ems_costs.lead_time
          ,'AQI');
      END IF;
    
      IF p_enqu.bypass_required = 'YES' THEN
        INSERT INTO quote_items
          (id
          ,qute_id
          ,enty_code
          ,mety_code
          ,adit_code
          ,cost_price
          ,selling_price
          ,delivery_price
          ,lead_time
          ,quit_type)
        VALUES
          (quit_seq.NEXTVAL
          ,l_qute_id
          ,p_enqu.enty_code
          ,p_enqu.existing_mety_code
          ,l_ems_costs.adit_code
          ,l_ems_costs.cost_price
          ,l_ems_costs.selling_price
          ,l_ems_costs.delivery_cost
          ,l_ems_costs.lead_time
          ,'AQI');
      END IF;
    
      IF l_lifting_gear_costs.selling_price IS NOT NULL THEN
        INSERT INTO quote_items
          (id
          ,qute_id
          ,adit_code
          ,cost_price
          ,selling_price
          ,delivery_price
          ,lead_time
          ,quit_type)
        VALUES
          (quit_seq.NEXTVAL
          ,l_qute_id
          ,l_lifting_gear_costs.adit_code
          ,l_lifting_gear_costs.cost_price
          ,l_lifting_gear_costs.selling_price
          ,l_lifting_gear_costs.delivery_cost
          ,l_lifting_gear_costs.lead_time
          ,'AQI');
      END IF;
    
      -- Generate the quote PDF
      l_quote_document := mip_quotation_document.generate_quote_pdf(p_quote_id => l_qute_id);
    
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => 'Produced Quote Document ' ||
                                                 l_quote_document || '.'
                      ,p_internal_or_external => gc_internal_reason);
    
      make_quote_available(l_qute_id);
    
    END IF; -- automatic quote
  
    IF l_produced_automatic_quote THEN
      p_manual_or_automatic_quote := gc_automatic_quote;
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => '-- Produced an automatic quote.'
                      ,p_internal_or_external => gc_internal_reason);
    ELSE
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => '-- Automatic quote failed - Manual quote required.'
                      ,p_internal_or_external => gc_internal_reason);
    
    END IF;
  
    pl('produce_addon_quotes:exit'
      ,$$PLSQL_LINE);
  END produce_addon_quotes;

  PROCEDURE produce_automatic_quotes(p_enqu                      IN t_enqu
                                    ,p_rfq_prty_id               IN parties.id%TYPE
                                    ,p_owner_prty_id             IN parties.id%TYPE DEFAULT NULL
                                    ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
  BEGIN
    pl('produce_automatic_quotes:entry'
      ,$$PLSQL_LINE);
  
    cout_assert.istrue(p_manual_or_automatic_quote = gc_automatic_quote
                      ,p_message => 'Attempted to produce automatic quote for enquiry marked as manual only');
  
    IF p_enqu.enty_code IN ('INSTALL', 'STD INSTALL') THEN
      produce_install_quotes(p_enqu                      => p_enqu
                            ,p_rfq_prty_id               => p_rfq_prty_id
                            ,p_owner_prty_id             => p_owner_prty_id
                            ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
    ELSIF p_enqu.enty_code IN ('EXCHANGE', 'STD EXCHANGE') THEN
      produce_exchange_quotes(p_enqu                      => p_enqu
                             ,p_rfq_prty_id               => p_rfq_prty_id
                             ,p_owner_prty_id             => p_owner_prty_id
                             ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
    ELSIF p_enqu.enty_code IN ('REMOVE', 'STD REMOVE', 'ADVERSARIAL') THEN
      produce_removal_quotes(p_enqu                      => p_enqu
                            ,p_rfq_prty_id               => p_rfq_prty_id
                            ,p_owner_prty_id             => p_owner_prty_id
                            ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
    ELSIF p_enqu.enty_code IN ('OFMAT') THEN
      produce_ofmat_quotes(p_enqu                      => p_enqu
                          ,p_rfq_prty_id               => p_rfq_prty_id
                          ,p_owner_prty_id             => p_owner_prty_id
                          ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
    ELSIF p_enqu.enty_code IN ('ALTERATION') THEN
      produce_alteration_quotes(p_enqu                      => p_enqu
                               ,p_rfq_prty_id               => p_rfq_prty_id
                               ,p_owner_prty_id             => p_owner_prty_id
                               ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
    ELSIF p_enqu.enty_code IN ('ADDON') THEN
      produce_addon_quotes(p_enqu                      => p_enqu
                          ,p_rfq_prty_id               => p_rfq_prty_id
                          ,p_owner_prty_id             => p_owner_prty_id
                          ,p_manual_or_automatic_quote => p_manual_or_automatic_quote);
    ELSE
      cout_err.report_and_stop(p_exception_message => 'Attempted to produce automatic quote for unexpected enquiry type of ' ||
                                                      p_enqu.enty_code);
    END IF;
    pl('produce_automatic_quotes:exit'
      ,$$PLSQL_LINE);
  END produce_automatic_quotes;

  PROCEDURE produce_quotes(p_enqu_id       IN enquiries.id%TYPE
                          ,p_rfq_prty_id   IN parties.id%TYPE
                          ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL) IS
    l_manual_or_automatic_quote t_manual_or_automatic_quote;
    l_enqu                      t_enqu;
  BEGIN
    pl('produce_quotes'
      ,$$PLSQL_LINE);
    IF NOT ready_for_quote(p_enqu_id) THEN
      pl('Not all mandatory fields for Enquiry ID=' || p_enqu_id ||
         ' have been completed'
        ,$$PLSQL_LINE);
    END IF;
    cout_assert.istrue(ready_for_quote(p_enqu_id)
                      ,'Not all mandatory fields for Enquiry ID=' ||
                       p_enqu_id || ' have been completed');
  
    cout_assert.istrue(not_already_submitted(p_enqu_id)
                      ,'This enquiry has already been submitted for quotation');
  
    mark_enquiry_submitted(p_enqu_id);
  
    SELECT *
      INTO l_enqu
      FROM enquiries
     WHERE id = p_enqu_id;
  
    IF tripartite_agreement_satisfied(p_enqu_id) THEN
    
      manual_or_automatic_quote(p_enqu                      => l_enqu
                               ,p_manual_or_automatic_quote => l_manual_or_automatic_quote);
    
      IF l_manual_or_automatic_quote = gc_manual_quote THEN
        request_manual_quote(p_enqu_id       => l_enqu.id
                            ,p_rfq_prty_id   => p_rfq_prty_id
                            ,p_owner_prty_id => p_owner_prty_id);
      ELSE
        produce_automatic_quotes(p_enqu                      => l_enqu
                                ,p_rfq_prty_id               => p_rfq_prty_id
                                ,p_owner_prty_id             => p_owner_prty_id
                                ,p_manual_or_automatic_quote => l_manual_or_automatic_quote);
        IF l_manual_or_automatic_quote = gc_automatic_quote THEN
          mark_enquiry_quoted(p_enqu_id);
        ELSE
          request_manual_quote(p_enqu_id       => l_enqu.id
                              ,p_rfq_prty_id   => p_rfq_prty_id
                              ,p_owner_prty_id => p_owner_prty_id);
        END IF;
        --
      END IF; -- manual or automatic quote
    
    END IF; -- tripartite agreement
  
    pl('exit without error'
      ,$$PLSQL_LINE);
  EXCEPTION
    WHEN OTHERS THEN
      pl(SQLERRM
        ,$$PLSQL_LINE);
      RAISE;
  END produce_quotes;

END mip_quotation;
/
