CREATE OR REPLACE VIEW V_QUOTE_DETAILS AS
SELECT "ENQUIRY_ID","QUOTE_ID","ENTY_CODE","MODULE_CODE","LEAD_TIME","ADDITIONAL_ITEMS","BAS_CODE","QMAX","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.qmax) over(PARTITION BY qute_id) AS qmax
              ,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
         ORDER BY enqu_id
                 ,quote_id
                 ,t.id) all_quote_items
 WHERE row_number = 1

