| index

View V_CURRENT_CONTACT_MECHANISMS

Columns

Name | Type | Optional | Default | Comments
PRTY_ID | NUMBER | Y |  |
COME_ID | NUMBER |  |  |
CODE | VARCHAR2(80) | Y |  |
CONTACT_VALUE | VARCHAR2(240) | Y |  |
START_DATE | DATE | Y |  |
END_DATE | DATE | Y |  |
COMMENTS | VARCHAR2(255) | Y |  |

SQL

    CREATE OR REPLACE VIEW V_CURRENT_CONTACT_MECHANISMS AS
    SELECT Prty_ID,
           Come_ID,
           Code,
           Contact_value,
           start_date,
           end_date,
           comments
    FROM (SELECT prty.ID AS Prty_ID,
           come_id AS Come_ID,
           come.comt_code AS Code,
           come.contact_value AS Contact_value,
           prty_come.start_date AS start_date,
           prty_come.end_date AS end_date,
           prty_come.comments AS comments,
           MAX(prty_come.start_date) over(PARTITION BY prty.ID, come.comt_code) AS max_start_date
      FROM parties prty,
           party_contact_mechanisms prty_come,
           contact_mechanisms come
     WHERE prty.id = prty_come.prty_id
           AND come.id = prty_come.come_id)
    WHERE start_date = max_start_date;