| index

View V_CURRENT_QUOTE_STATUS

Columns

Name | Type | Optional | Default | Comments
QUTE_ID | NUMBER |  |  |
EVENT_DATE | DATE |  |  |
QUST_CODE | VARCHAR2(80) |  |  |

SQL

    CREATE OR REPLACE VIEW V_CURRENT_QUOTE_STATUS AS
    SELECT qute_id
          ,event_date
          ,qust_code
      FROM (SELECT qute_id
                  ,event_date
                  ,qust_code
                  ,decode(qust_code
                         ,'INP'
                         ,1
                         ,'AV'
                         ,2
                         ,'SELREJ'
                         ,3
                         ,'SELECTED'
                         ,5
                         ,'REJECTED'
                         ,6
                         ,'ACCEPTED'
                         ,7
                         ,'LAPSED'
                         ,8) AS this_event
                  ,MAX(decode(qust_code
                             ,'INP'
                             ,1
                             ,'AV'
                             ,2
                             ,'SELREJ'
                             ,3
                             ,'SELECTED'
                             ,5
                             ,'REJECTED'
                             ,6
                             ,'ACCEPTED'
                             ,7
                             ,'LAPSED'
                             ,8)) over(PARTITION BY qute_id) AS max_event
              FROM quote_events)
     WHERE this_event = max_event;