| index

View V_QUOTE_DETAILS This view shows details of each of the quotes for an enquiry.

Columns

Name | Type | Optional | Default | Comments
ENQUIRY_ID | NUMBER | Y |  | The ID of the enquiry this quote relates to.
QUOTE_ID | NUMBER | Y |  | The unique ID for this particular quote.
ENTY_CODE | VARCHAR2(80) | Y |  | The type of enquiry quoted for.
MODULE_CODE | VARCHAR2(80) | Y |  | The name of the module quoted for.
LEAD_TIME | NUMBER | Y |  | The lead time for installation. This is the max of any additional item's lead times.
ADDITIONAL_ITEMS | VARCHAR2(4000) | Y |  | Any additional items included in the quote.
BAS_CODE | VARCHAR2(80) | Y |  | The module base code.
HOU_CODE | VARCHAR2(80) | Y |  |
QMAX | NUMBER | Y |  | The meter module's Q max.
QMIN | NUMBER | Y |  |
INLET_ORIENTATION | VARCHAR2(80) | Y |  | The orientation of the meter inlet.
OUTLET_ORIENTATION | VARCHAR2(80) | Y |  | The orientatino of the meter outlet.
TOTAL_COST | NUMBER | Y |  | The total cost of the quote. This figure excludes any lifting gear or purging.
ROW_NUMBER | NUMBER | Y |  |

SQL

    CREATE OR REPLACE 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','PURGING'))
             ORDER BY enqu_id
                     ,quote_id
                     ,t.id) all_quote_items
     WHERE row_number = 1;