CREATE OR REPLACE PACKAGE mip_quotation IS

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

  /** 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;

  /** 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;

END mip_quotation;
/
CREATE OR REPLACE PACKAGE BODY mip_quotation IS

  --  PROCEDURE email_aq_generated(p_enqu_id IN enquiries.id%TYPE);
  PROCEDURE email_quotes_available(p_enqu_id IN enquiries.id%TYPE);
  PROCEDURE email_rfq_submitted(p_enqu_id IN enquiries.id%TYPE);
  --  PROCEDURE email_request_for_mq(p_enqu_id IN 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);

  SUBTYPE t_enqu IS enquiries%ROWTYPE;

  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);

  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;

  PROCEDURE pl(p_in VARCHAR2) IS
    l_fh utl_file.file_type;
  BEGIN
    dbms_application_info.set_module('MIP_QUOTATION'
                                    ,p_in);
    l_fh := utl_file.fopen(location  => 'WEBMIP_BULK_LOAD'
                          ,filename  => 'MIP_QUOTATION.txt'
                          ,open_mode => 'A');
    utl_file.put_line(l_fh
                     ,to_char(SYSDATE
                             ,'DD/MM/YYYY HH24:MI:SS') || ',' || p_in);
    utl_file.fclose(l_fh);
  END pl;

  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
    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);
  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
  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);
  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
  BEGIN
    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;
  
  END select_quote;

  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
  
    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);
  
    RETURN l_qute_id;
  
  END start_quote;

  PROCEDURE make_quote_available(p_qute_id IN quotes.id%TYPE) IS
  BEGIN
    pl('make_quote_available');
  
    add_quote_event(p_qute_id    => p_qute_id
                   ,p_qust_code  => 'AV'
                   ,p_event_date => SYSDATE);
  
  END make_quote_available;

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

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

  PROCEDURE mark_enquiry_invalid(p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    pl('mark_enquiry_invalid');
  
    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
      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_system_name RETURN system_configuration.VALUE%TYPE IS
  BEGIN
  
    RETURN get_system_configuration_value('SYSTEM_NAME');
  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;

  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
    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(' ');
  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');
  
    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');
    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');
    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_aq_generated(p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    open_body;
  
    al('Quotations produced in response to Enquiry refererence: ' ||
       p_enqu_id || ' (' || get_enty_description(p_enqu_id) || ')');
  
    FOR l_qute IN (SELECT id
                     FROM quotes
                         ,v_quote_details v
                    WHERE enqu_id = p_enqu_id
                      AND v.quote_id = id
                    ORDER BY total_cost) LOOP
    
      produce_quote_summary(l_qute.id);
    
      al(' ');
    END LOOP;
  
    produce_reasoning_summary(p_enqu_id);
  
    close_body;
  
    mip_email.send_email_clob(p_recipient => get_automatic_quote_recipient
                             ,p_body      => g_email_plain_body
                             ,p_body_html => g_email_html_body
                             ,p_subject   => get_system_name ||
                                             ': Notification of generation of automatic quotes for Enquiry reference: ' ||
                                             p_enqu_id);
  END email_aq_generated;*/

  PROCEDURE email_quotes_available(p_enqu_id IN enquiries.id%TYPE) IS
    l_agent_comt_code     enquiries.agent_comt_code%TYPE;
    l_agent_contact_value enquiries.agent_contact_value%TYPE;
    l_email_recipient     system_configuration.VALUE%TYPE;
    l_email_subject       VARCHAR2(80);
    l_agent_first_name    parties.first_name%TYPE;
    l_agent_last_name     parties.last_name%TYPE;
    l_agent_prty_id       parties.id%TYPE;
    l_date_submitted      DATE;
  BEGIN
    pl('email_quotes_available');
    open_body;
  
    SELECT agent_comt_code
          ,agent_contact_value
          ,event_date
      INTO l_agent_comt_code
          ,l_agent_contact_value
          ,l_date_submitted
      FROM enquiries
          ,enquiry_events
     WHERE enquiries.id = p_enqu_id
       AND enquiry_events.enqu_id = enquiries.id
       AND enquiry_events.enst_code = 'SUBMITTED';
  
    l_agent_prty_id := mip_enquiry.get_enquiry_role(p_enquiryid => p_enqu_id
                                                   ,p_rolecode  => 'ENQ OWN');
  
    SELECT first_name
          ,last_name
      INTO l_agent_first_name
          ,l_agent_last_name
      FROM parties
     WHERE id = l_agent_prty_id;
  
    IF l_agent_comt_code = 'EMAIL' THEN
      l_email_recipient := l_agent_contact_value;
      l_email_subject   := get_system_name ||
                           ': Confirmation of availability of quotation(s)';
      al('Confirmation of availability of quotation.');
      al(' ');
      al('Thank you for using ' || get_system_name ||
         ' online. Your quotation(s) are now available.');
      al(' ');
      al('Enquiry refererence: ' || p_enqu_id || ' (' ||
         get_enty_description(p_enqu_id) || ')');
      al('Request made on: ' ||
         to_char(l_date_submitted
                ,'ddth Month YYYY HH24:MI:SS'));
      al(' ');
    ELSE
      l_email_recipient := get_automatic_quote_recipient;
      l_email_subject   := get_system_name ||
                           ': Confirmation of availability of quotation(s), Agent requires notification.';
    
      al('Enquiry refererence: ' || p_enqu_id || ' (' ||
         get_enty_description(p_enqu_id) || ')');
      al('Request made on: ' ||
         to_char(l_date_submitted
                ,'ddth Month YYYY HH24:MI:SS'));
      al(' ');
      al('The Agent, ' || l_agent_first_name || ' ' || l_agent_last_name ||
         ' has requested that they be contacted via ' || l_agent_comt_code || '(' ||
         l_agent_contact_value || ')');
    END IF;
    al(' ');
    al('The following quotations are available:');
  
    FOR l_qute IN (SELECT id
                     FROM quotes
                         ,v_quote_details v
                         ,v_current_quote_status s
                    WHERE enqu_id = p_enqu_id
                      AND v.quote_id = id
                      AND s.qute_id = id
                      AND s.qust_code = 'AV'
                    ORDER BY total_cost) LOOP
    
      produce_quote_summary(l_qute.id);
    
      al(' ');
    END LOOP;
  
    close_body;
  
    BEGIN
      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);
    END;
  
  END email_quotes_available;

  PROCEDURE email_rfq_submitted(p_enqu_id IN enquiries.id%TYPE) IS
    l_agent_comt_code     enquiries.agent_comt_code%TYPE;
    l_agent_contact_value enquiries.agent_contact_value%TYPE;
    l_email_recipient     system_configuration.VALUE%TYPE;
    l_email_subject       VARCHAR2(240);
    l_agent_first_name    parties.first_name%TYPE;
    l_agent_last_name     parties.last_name%TYPE;
    l_agent_prty_id       parties.id%TYPE;
    l_date_submitted      DATE;
  BEGIN
    pl('email_rfq_submitted');
  
    open_body;
  
    SELECT agent_comt_code
          ,agent_contact_value
          ,event_date
      INTO l_agent_comt_code
          ,l_agent_contact_value
          ,l_date_submitted
      FROM enquiries
          ,enquiry_events
     WHERE enquiries.id = p_enqu_id
       AND enquiry_events.enqu_id = enquiries.id
       AND enquiry_events.enst_code = 'SUBMITTED';
  
    BEGIN
      l_agent_prty_id := mip_enquiry.get_enquiry_role(p_enquiryid => p_enqu_id
                                                     ,p_rolecode  => 'ENQ OWN');
    EXCEPTION
      WHEN OTHERS THEN
        pl('mip_enquiry.get_enquiry_role returned ' || SQLERRM);
        RAISE;
    END;
  
    BEGIN
      SELECT first_name
            ,last_name
        INTO l_agent_first_name
            ,l_agent_last_name
        FROM parties
       WHERE id = l_agent_prty_id;
    EXCEPTION
      WHEN OTHERS THEN
        pl('Error finding agent - prty_id=' || l_agent_prty_id);
    END;
  
    IF l_agent_comt_code = 'EMAIL' THEN
      l_email_recipient := l_agent_contact_value;
      l_email_subject   := get_system_name ||
                           ': Confirmation of Request for Quotation';
      al('Confirmation of Request for Quotation');
      al(' ');
      al('Thank you for using ' || get_system_name ||
         ' online. Your request for quotation has been received.');
      al(' ');
      al('Enquiry Refererence Number: ' || p_enqu_id || ' (' ||
         get_enty_description(p_enqu_id) || ')');
      al('Request made on: ' ||
         to_char(l_date_submitted
                ,'ddth Month YYYY HH24:MI:SS'));
      al(' ');
      al('You should receive notification of your quotation(s) within 6 days.');
      al(' ');
      al('Should notification of your quotation(s) not arrive after 6 days, then please phone us on ' ||
         get_customer_support_telephone ||
         '. Be prepared to quote the Enquiry Reference Number.');
    ELSE
      l_email_recipient := get_automatic_quote_recipient;
      l_email_subject   := get_system_name ||
                           ': Confirmation of Request for Quotation, Agent requires notification.';
    
      al('Enquiry Refererence Number: ' || p_enqu_id || ' (' ||
         get_enty_description(p_enqu_id) || ')');
      al('Request made on: ' ||
         to_char(l_date_submitted
                ,'ddth Month YYYY HH24:MI:SS'));
      al(' ');
      al('The Agent, ' || l_agent_first_name || ' ' || l_agent_last_name ||
         ' has requested that they be contacted via ' || l_agent_comt_code || '(' ||
         l_agent_contact_value || ')');
      al(' ');
    END IF;
  
    close_body;
  
    BEGIN
      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);
        RAISE;
    END;
  END email_rfq_submitted;

  PROCEDURE email_request_for_mq(p_enqu_id IN enquiries.id%TYPE) IS
    l_system_name system_configuration.VALUE%TYPE := get_system_name;
    l_quote_row   quotes%ROWTYPE;
  BEGIN
    pl('email_request_for_mq');
  
    SELECT *
      INTO l_quote_row
      FROM quotes
     WHERE enqu_id = p_enqu_id;
  
    open_body;
  
    al('This email has been produced in response to Enquiry Refererence Number: ' ||
       p_enqu_id || ' (' || get_enty_description(p_enqu_id) || ')');
  
    al(' ');
    al(l_system_name ||
       ' was unable to produce an automatic quote for this Enquiry, therefore a ' ||
       'Manual Quote is to be completed against Quote Reference Number: ' ||
       l_quote_row.id);
    al('This quote will be valid from ' ||
       to_char(l_quote_row.valid_from
              ,'ddth Month YYYY') || ' to ' ||
       to_char(l_quote_row.valid_until
              ,'ddth Month YYYY'));
    al(' ');
    al('Current Service Level Agreements dictate that a manual quote be provided within 6 days');
  
    al('Data Entered:');
  
    al('Enquiry type: ' || get_enty_description(p_enqu_id));
  
    produce_enquiry_entry_details(p_enqu_id);
  
    produce_reasoning_summary(p_enqu_id);
  
    close_body;
  
    BEGIN
      mip_email.send_email_clob(p_recipient => get_manual_quote_recipient
                               ,p_body      => g_email_plain_body
                               ,p_body_html => g_email_html_body
                               ,p_subject   => l_system_name ||
                                               ': Request for a Manual Quote for Enquiry Reference Number: ' ||
                                               p_enqu_id);
    EXCEPTION
      WHEN OTHERS THEN
        pl('mip_email.send_email_clob returned ' || SQLERRM);
    END;
  
  END email_request_for_mq;

  PROCEDURE email_support(p_subject IN VARCHAR2
                         ,p_enqu_id IN enquiries.id%TYPE) IS
  BEGIN
    pl('email_support');
  
    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);
    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');
  
    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_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);
  
    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;
  BEGIN
    pl('ready_for_quote(2) - enqu_id=' || p_enqu_id);
  
    ready_for_quote(p_enqu_id          => p_enqu_id
                   ,p_mandatory_checks => l_mandatory_checks
                   ,p_enquiry_is_ready => l_enquiry_is_ready);
    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);
      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 enqu_id=' || p_enqu_id || ', field_name=' ||
       p_field_name);
  
    SELECT error_message
      INTO l_error_message
      FROM validation_results
     WHERE field_name = upper(p_field_name)
       AND enqu_id = p_enqu_id;
    pl('returning ' || l_error_message);
    RETURN l_error_message;
  EXCEPTION
    WHEN no_data_found THEN
      pl('Nothing found');
      RETURN NULL;
    WHEN OTHERS THEN
      pl('validation_result:' || SQLERRM);
      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');
  
    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;

  PROCEDURE add_quote_reason(p_enqu_id              IN enquiries.id%TYPE
                            ,p_reason               IN quote_reasoning.reason%TYPE
                            ,p_internal_or_external IN quote_reasoning.internal_or_external%TYPE DEFAULT gc_external_reason) IS
  BEGIN
  
    INSERT INTO quote_reasoning
      (enqu_id
      ,reason
      ,internal_or_external
      ,id)
    VALUES
      (p_enqu_id
      ,p_reason
      ,p_internal_or_external
      ,qure_seq.NEXTVAL);
  
  END add_quote_reason;

  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');
  
    -- 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;
  
    RETURN l_meter_size_code;
  
  EXCEPTION
    WHEN no_data_found THEN
      cout_err.report_and_stop(p_exception_message => 'Unable to find Meter Size Code for Qmax of ' ||
                                                      p_qmax);
  END get_u_meter_size;

  FUNCTION valid_meter_size_upgrade(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_upgrade');
  
    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 existing_mesc = p_existing_meter_size_code
       AND required_mesc = p_required_meter_size_code;
    RETURN TRUE;
  EXCEPTION
    WHEN no_data_found THEN
      RETURN FALSE;
  END valid_meter_size_upgrade;

  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_existing_meter_size_code meter_size_codes.code%TYPE;
    l_required_meter_size_code meter_size_codes.code%TYPE;
  BEGIN
    pl('survey_required');
  
    -- Low Pressure Rules
    -- Site survey required for:
    --  Relocation
    --  Exchange where upgrade is greater than 1 'U' size
  
    IF p_enqu.enty_code = 'ALTERATION' THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Site Survey is required for alteration (relocation or reposition) of an existing meter.');
    END IF; -- RELOCATE
  
    IF p_enqu.enty_code IN ('EXCHANGE', 'STD EXCHANGE') THEN
      --      SELECT svcpt_code
      --        INTO l_svcpt_code
      --        FROM service_pressures
      --       WHERE code = p_enqu.required_svcp_code;
      --      IF l_svcpt_code = 'LP' THEN
      l_existing_meter_size_code := p_enqu.existing_mesc_code;
      l_required_meter_size_code := p_enqu.required_mesc_code;
      IF l_required_meter_size_code IS NULL THEN
        l_required_meter_size_code := get_u_meter_size(p_enqu.qmax);
      END IF;
      IF NOT
          valid_meter_size_upgrade(p_existing_meter_size_code => l_existing_meter_size_code
                                  ,p_required_meter_size_code => l_required_meter_size_code) THEN
        p_manual_or_automatic_quote := gc_manual_quote;
        add_quote_reason(p_enqu.id
                        ,'Site Survey is required for exchange of meter from size ' ||
                         l_existing_meter_size_code || ' to ' ||
                         l_required_meter_size_code || '.');
      END IF;
      --      END IF; -- svcpt_code = 'LP'
    END IF; -- EXCHANGE
  
  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');
  
    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');
  
    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));
        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
  BEGIN
    pl('manual_or_automatic_quote');
    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) || '''.');
    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.');
    END IF;
  
    IF p_enqu.bypass_required = 'YES' THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Bypass required.');
    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.');
    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.');
    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.');
    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.');
    END IF;
  
    /*    IF p_enqu.enty_code IN ('EXCHANGE')
       AND NOT (p_enqu.existing_mety_code = 'DIAPHRAGM' AND
        p_enqu.required_svcp_code = 'LP') THEN
      p_manual_or_automatic_quote := gc_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Exhange of a meter that is not an LP Diaphragm.');
    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.');
    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.');
    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.');
    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.');
    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.');
    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');
    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');
    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');
    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;
  
    RETURN l_rec_costs;
  
  EXCEPTION
    WHEN no_data_found THEN
      RETURN l_rec_costs;
  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');
    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
                         ,1
                         ,999) 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) OR
                   (svcp.code IS NULL AND p_svcp_code IS NULL))
               AND ((mesc_code = p_mesc_code) OR
                   (mesc_code IS NULL AND p_mesc_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_laco;

  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);
  BEGIN
    pl('produce_module_quotes');
    cout_assert.istrue(p_enqu.enty_code IN ('INSTALL', 'STD INSTALL',
                        'EXCHANGE', 'CAPACITY CHANGE')
                      ,'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 || '.' || ' Required SVCP ' ||
                                   p_enqu.required_svcp_code || ', QMAX=' ||
                                   p_enqu.qmax || ', Outlet Pressure=' ||
                                   p_enqu.required_metering_pressure || '.'
                      ,p_internal_or_external => gc_internal_reason);
    ELSIF p_enqu.enty_code IN ('EXCHANGE') THEN
      add_quote_reason(p_enqu.id
                      ,p_reason => 'Attempting an automatic exchange quote for  ' ||
                                   p_enqu.id || '.' || ' Required SVCP ' ||
                                   p_enqu.required_svcp_code || ', QMAX=' ||
                                   p_enqu.qmax || ', Outlet Pressure=' ||
                                   p_enqu.required_metering_pressure || '.' ||
                                   'Existing meter was a ' ||
                                   p_enqu.existing_mesc_code || ' ' ||
                                   p_enqu.existing_mety_code
                      ,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 || '.' || ' Required SVCP ' ||
                                   p_enqu.required_svcp_code || ', QMAX=' ||
                                   p_enqu.qmax || ', Outlet Pressure=' ||
                                   p_enqu.required_metering_pressure || '.' ||
                                   'Existing meter was a ' ||
                                   p_enqu.existing_mesc_code || ' ' ||
                                   p_enqu.existing_mety_code
                      ,p_internal_or_external => gc_internal_reason);
    END IF;
  
    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
                               ,modu.lead_time 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.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
                                       ,lead_time
                                   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) modu
                               ,(SELECT metr.code
                                       ,metr.qmax
                                       ,metr.qmin
                                       ,metr.qnom
                                       ,metr.mety_code
                                    FROM meters metr
                                  ) 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
                            AND modu.qmax >= p_enqu.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;
      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 || '.'
                        ,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 => p_enqu.required_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_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:' ||
                                     p_enqu.required_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;
      /* Always get costs for LIFTING GEAR */
    
      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;
    
      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
          ,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
          ,'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;
      
        /* Always include LIFTING GEAR */
      
        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');
      
        -- 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;
        */
        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;
  
    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;
  
  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');
    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);
  
  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');
    cout_assert.istrue(p_enqu.enty_code IN ('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);
  
  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');
    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);
  
  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_qute_id                  quotes.id%TYPE;
    l_additional_costs         t_rec_additional_costs;
    l_quote_document           VARCHAR2(240);
  BEGIN
    pl('produce_labour_only_quotes');
    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.');
    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=' ||
                                 p_enqu.existing_mety_code ||
                                 ', Meter Size Code=' ||
                                 p_enqu.existing_mesc_code || '.'
                    ,p_internal_or_external => gc_internal_reason);
  
    l_this_is_automatic_quote := TRUE;
  
    l_additional_costs := get_laco(p_enty_code => p_enqu.enty_code
                                  ,p_regi_code => l_regi_code
                                  ,p_mety_code => p_enqu.existing_mety_code
                                  ,p_mesc_code => p_enqu.existing_mesc_code
                                  ,p_svcp_code => p_enqu.required_svcp_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 Labour Cost (selling price) for this Enquiry Type Code: ' ||
                                   p_enqu.enty_code || ', Meter Type Code:' ||
                                   p_enqu.existing_mety_code ||
                                   ', Meter Size Code:' ||
                                   p_enqu.existing_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 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
        ,quit_type)
      VALUES
        (quit_seq.NEXTVAL
        ,l_qute_id
        ,p_enqu.enty_code
        ,l_additional_costs.svcpt_code
        ,p_enqu.existing_mesc_code
        ,p_enqu.existing_mety_code
        ,l_additional_costs.cost_price
        ,l_additional_costs.selling_price
        ,l_additional_costs.delivery_cost
        ,'LQI');
    
      -- 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;
  
  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
    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);
  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
    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);
  
  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
    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);
  
  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_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');
  
    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;
    /* Always get costs for LIFTING GEAR */
  
    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;
  
    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
          ,bas_code
          ,cost_price
          ,selling_price
          ,delivery_price
          ,quit_type)
        VALUES
          (quit_seq.NEXTVAL
          ,l_qute_id
          ,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
          ,adit_code
          ,cost_price
          ,selling_price
          ,delivery_price
          ,lead_time
          ,quit_type)
        VALUES
          (quit_seq.NEXTVAL
          ,l_qute_id
          ,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
          ,adit_code
          ,cost_price
          ,selling_price
          ,delivery_price
          ,lead_time
          ,quit_type)
        VALUES
          (quit_seq.NEXTVAL
          ,l_qute_id
          ,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;
    
      /* Always include LIFTING GEAR */
    
      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');
    
      -- 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;
      */
      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;
  
  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');
  
    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);
    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;
  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');
    IF NOT ready_for_quote(p_enqu_id) THEN
      pl('Not all mandatory fields for Enquiry ID=' || p_enqu_id ||
         ' have been completed');
    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);
          email_quotes_available(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');
  EXCEPTION
    WHEN OTHERS THEN
      pl(SQLERRM);
      RAISE;
  END produce_quotes;

BEGIN
  -- Initialization
  pl('mip_quotation');
END mip_quotation;
/
