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_id the id of the enquiry to be checked
   %return TRUE if the enquiry can be quoted for
   */
  FUNCTION ready_for_quote(p_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_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_id               IN enquiries.id%TYPE
                           ,p_mandatory_checks OUT mip_mandatory.t_mandatory_checks
                           ,p_quote_is_ready   OUT BOOLEAN);

  /** Generate quotes in response to a 'request for quote' against an enquiry
                 
  %param p_id the id of the enquiry to be checked
  */
  PROCEDURE produce_quotes(p_id IN enquiries.id%TYPE);

  /** Accept a quote
  %param p_qute_id id of the quote to be accepted
  %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_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_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_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_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_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_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_description quote_events.description%TYPE DEFAULT NULL
                        ,p_event_date  IN DATE DEFAULT SYSDATE);

END mip_quotation;
/
CREATE OR REPLACE PACKAGE BODY mip_quotation IS

  SUBTYPE t_reason IS VARCHAR2(240);
  SUBTYPE t_internal_or_external IS VARCHAR2(8);

  g_internal_reason CONSTANT t_internal_or_external := 'INTERNAL';
  g_external_reason CONSTANT t_internal_or_external := 'EXTERNAL';

  SUBTYPE t_manual_or_automatic_quote IS VARCHAR2(9);

  SUBTYPE t_enqu IS enquiries%ROWTYPE;

  g_manual_quote    CONSTANT t_manual_or_automatic_quote := 'MANUAL';
  g_automatic_quote CONSTANT t_manual_or_automatic_quote := 'AUTOMATIC';

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

  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
    NULL; --IF p_qust_code = 'ACCEPTED' THEN;
  END add_quote_event;

  PROCEDURE accept_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   => 'ACCEPTED'
                   ,p_event_date  => p_event_date
                   ,p_description => p_description);
  
  END accept_quote;

  PROCEDURE reject_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   => 'REJECTED'
                   ,p_event_date  => p_event_date
                   ,p_description => p_description);
  END reject_quote;

  PROCEDURE reject_all_quotes(p_enqu_id     IN enquiries.id%TYPE
                             ,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
      add_quote_event(p_qute_id     => l_rec.id
                     ,p_qust_code   => 'REJECTED'
                     ,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_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);
  
    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 request_manual_quote(p_enqu_id IN enquiries.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
      ,'MQ' -- manual quote
      ,p_enqu_id
      ,trunc(SYSDATE)
      ,trunc(SYSDATE + 90)
      ,SYSDATE
      ,USER)
    RETURNING id INTO l_qute_id;
  
    INSERT INTO quote_events
      (event_date
      ,qust_code
      ,qute_id)
    VALUES
      (SYSDATE
      ,'INP' -- In Progress
      ,l_qute_id);
  
  END request_manual_quote;

  PROCEDURE ready_for_quote(p_id               IN enquiries.id%TYPE
                           ,p_mandatory_checks OUT mip_mandatory.t_mandatory_checks
                           ,p_quote_is_ready   OUT BOOLEAN) IS
    l_mandatory_checks mip_mandatory.t_mandatory_checks;
  BEGIN
    p_quote_is_ready := mip_enquiries_helper.check_mandatory(p_id               => p_id
                                                            ,p_mandatory_checks => p_mandatory_checks);
  END ready_for_quote;

  FUNCTION ready_for_quote(p_id IN enquiries.id%TYPE) RETURN BOOLEAN IS
    l_mandatory_checks mip_mandatory.t_mandatory_checks;
    l_quote_is_ready   BOOLEAN;
  BEGIN
    ready_for_quote(p_id               => p_id
                   ,p_mandatory_checks => l_mandatory_checks
                   ,p_quote_is_ready   => l_quote_is_ready);
    RETURN l_quote_is_ready;
  END ready_for_quote;

  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 g_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
  
    -- 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
    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
  
    -- Low Pressure Rules
    -- Site survey required for:
    --  Relocation
    --  Exchange where upgrade is greater than 1 'U' size
    SELECT svcpt_code
      INTO l_svcpt_code
      FROM service_pressures
     WHERE code = p_enqu.required_svcp_code;
    IF l_svcpt_code = 'LP' THEN
      IF p_enqu.enty_code = 'RELOCATE' THEN
        p_manual_or_automatic_quote := g_manual_quote;
        add_quote_reason(p_enqu.id
                        ,'Site Survey is required for relocation.');
      END IF; -- RELOCATE
      IF p_enqu.enty_code IN ('EXCHANGE', 'STD EXCHANGE') THEN
        l_existing_meter_size_code := p_enqu.existing_mesc_code;
        l_required_meter_size_code := p_enqu.existing_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 := g_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; -- EXCHANGE
    END IF; -- svcpt_code = 'LP'
  END survey_required;

  PROCEDURE manual_or_automatic_quote(p_enqu                      IN t_enqu
                                     ,p_manual_or_automatic_quote OUT t_manual_or_automatic_quote) IS
  BEGIN
    p_manual_or_automatic_quote := g_automatic_quote;
    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 := g_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 := g_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 := g_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 := g_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 := g_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 THEN
      p_manual_or_automatic_quote := g_manual_quote;
      add_quote_reason(p_enqu.id
                      ,'Required Annual Quantity is in excess of 732MWh.');
    END IF;
  
    -- check postcode
    IF NOT mip_regions.valid_postcode_format(p_enqu.install_postcode) THEN
      p_manual_or_automatic_quote := g_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 := g_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 = g_manual_quote THEN
      add_quote_reason(p_enqu.id
                      ,'- Manual quote required.'
                      ,g_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
    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
    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
    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
    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_install_quotes(p_enqu                      IN t_enqu
                                  ,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
    cout_assert.istrue(p_enqu.enty_code IN ('INSTALL', 'STD INSTALL')
                      ,'Attempted to produce an install quote for enquiry of type ' ||
                       p_enqu.enty_code);
  
    cout_assert.isnotnull(l_regi_code
                         ,'Attempted to produce an install 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 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 => g_internal_reason);
  
    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
                               ,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
                               ,metr.qmin
                               ,metr.selling_price AS metr_selling_price
                               ,metr.cost_price AS metr_cost_price
                               ,metr.delivery_cost AS metr_delivery_cost
                               ,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
                                       ,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
                                       ,selling_price
                                       ,cost_price
                                       ,delivery_cost
                                   FROM meters metr
                                       ,(SELECT metr_code
                                               ,selling_price
                                               ,cost_price
                                               ,delivery_cost
                                           FROM (SELECT row_number() over(PARTITION BY metr_code ORDER BY(decode(regi_code, l_regi_code, 1, 999))) AS accuracy
                                                       ,metr_code
                                                       ,selling_price
                                                       ,cost_price
                                                       ,delivery_cost
                                                       ,ROWID
                                                   FROM v_meco 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 metr.code = cost.metr_code(+)) 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 metr.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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_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 => g_internal_reason);
      
        INSERT INTO quotes
          (id
          ,qute_type
          ,enqu_id
          ,valid_from
          ,valid_until
          ,created_on
          ,created_by)
        VALUES
          (qute_seq.NEXTVAL
          ,'AQ' -- automatic quote
          ,p_enqu.id
          ,trunc(SYSDATE)
          ,trunc(SYSDATE + 90)
          ,SYSDATE
          ,USER)
        RETURNING id INTO l_qute_id;
      
        INSERT INTO quote_events
          (event_date
          ,qust_code
          ,qute_id)
        VALUES
          (SYSDATE
          ,'INP' -- In Progress
          ,l_qute_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
          ,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.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 => g_internal_reason);
      
        INSERT INTO quote_events
          (event_date
          ,qust_code
          ,qute_id)
        VALUES
          (SYSDATE
          ,'AV' -- Available
          ,l_qute_id);
      
      END IF; -- automatic quote
    
    END LOOP;
  
    IF l_produced_automatic_quote THEN
      p_manual_or_automatic_quote := g_automatic_quote;
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => '-- Produced an automatic quote.'
                      ,p_internal_or_external => g_internal_reason);
    ELSE
      p_manual_or_automatic_quote := g_manual_quote;
      add_quote_reason(p_enqu_id              => p_enqu.id
                      ,p_reason               => '-- Automatic quote failed - Manual quote required.'
                      ,p_internal_or_external => g_internal_reason);
    
    END IF;
  
  END produce_install_quotes;

  PROCEDURE produce_automatic_quotes(p_enqu                      IN t_enqu
                                    ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS
  BEGIN
    cout_assert.istrue(p_manual_or_automatic_quote = g_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_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_id IN enquiries.id%TYPE) IS
    l_manual_or_automatic_quote t_manual_or_automatic_quote;
    l_enqu                      t_enqu;
  BEGIN
  
    cout_assert.istrue(ready_for_quote(p_id)
                      ,'Not all mandatory fields for Enquiry ID=' || p_id ||
                       ' have been completed');
  
    SELECT *
      INTO l_enqu
      FROM enquiries
     WHERE id = p_id;
  
    manual_or_automatic_quote(p_enqu                      => l_enqu
                             ,p_manual_or_automatic_quote => l_manual_or_automatic_quote);
  
    IF l_manual_or_automatic_quote = g_manual_quote THEN
      request_manual_quote(p_enqu_id => l_enqu.id);
    ELSE
      produce_automatic_quotes(p_enqu                      => l_enqu
                              ,p_manual_or_automatic_quote => l_manual_or_automatic_quote);
      IF l_manual_or_automatic_quote = g_manual_quote THEN
        request_manual_quote(p_enqu_id => l_enqu.id);
      END IF;
    
      --
    END IF; -- manual or automatic quote
  
    /*  EXCEPTION
    WHEN OTHERS THEN
      cout_err.report_and_stop;*/
  END produce_quotes;

BEGIN
  -- Initialization
  NULL;
END mip_quotation;
/
