/**
   This view shows details of each of the quotes for an enquiry.
   
   The total cost does NOT include any lifting gear or purging that
   may be required.
  */
CREATE OR REPLACE FORCE VIEW V_QUOTE_DETAILS AS
SELECT enquiry_id
      ,quote_id
      ,enty_code
      ,module_code
      ,lead_time
      ,additional_items
      ,bas_code
      ,hou_code
      ,qmax
      ,qmin
      ,inlet_orientation
      ,outlet_orientation
      ,total_cost
	,row_number
  FROM (SELECT q.enqu_id AS enquiry_id
              ,t.qute_id AS quote_id
              ,MAX(t.enty_code) over(PARTITION BY qute_id) AS enty_code
              ,MAX(t.modu_code) over(PARTITION BY qute_id) AS module_code
              ,MAX(t.lead_time) over(PARTITION BY qute_id ORDER BY qute_id) AS lead_time
              ,get_quote_items(qute_id) AS additional_items
              ,MAX(t.bas_code) over(PARTITION BY qute_id) AS bas_code
              ,MAX(t.hou_code) over(PARTITION BY qute_id) AS hou_code
              ,MAX(t.qmax) over(PARTITION BY qute_id) AS qmax
              ,MAX(t.qmin) over(PARTITION BY qute_id) AS qmin
              ,MAX(t.inlet_orientation) over(PARTITION BY qute_id) AS inlet_orientation
              ,MAX(t.outlet_orientation) over(PARTITION BY qute_id) AS outlet_orientation
              ,SUM(nvl(t.selling_price
                      ,0) + nvl(t.delivery_price
                               ,0)) over(PARTITION BY qute_id ORDER BY qute_id) AS total_cost
              ,row_number() over(PARTITION BY qute_id ORDER BY qute_id) AS row_number
          FROM quote_items t
              ,quotes      q
         WHERE t.qute_id = q.id
           AND NOT (quit_type = 'AQI' AND
                adit_code IN ('LIFTING GEAR'))
         ORDER BY enqu_id
                 ,quote_id
                 ,t.id) all_quote_items
 WHERE row_number = 1;

COMMENT ON TABLE v_quote_details IS 'This view shows details of each of the quotes for an enquiry.';

COMMENT ON COLUMN v_quote_details.enquiry_id IS 'The ID of the enquiry this quote relates to.';
COMMENT ON COLUMN v_quote_details.quote_id IS 'The unique ID for this particular quote.';
COMMENT ON COLUMN v_quote_details.enty_code IS 'The type of enquiry quoted for.';
COMMENT ON COLUMN v_quote_details.module_code IS 'The name of the module quoted for.';
COMMENT ON COLUMN v_quote_details.lead_time IS 'The lead time for installation.  This is the max of any additional item''s lead times.';
COMMENT ON COLUMN v_quote_details.additional_items IS 'Any additional items included in the quote.';
COMMENT ON COLUMN v_quote_details.bas_code IS 'The module base code.';
COMMENT ON COLUMN v_quote_details.qmax IS 'The meter module''s Q max.';
COMMENT ON COLUMN v_quote_details.qmin IS 'The meter module''s Q min.';
COMMENT ON COLUMN v_quote_details.inlet_orientation IS 'The orientation of the meter inlet.';
COMMENT ON COLUMN v_quote_details.outlet_orientation IS 'The orientatino of the meter outlet.';
COMMENT ON COLUMN v_quote_details.total_cost IS 'The total cost of the quote.  This figure excludes any lifting gear.';
/