| index

View V_PARTY_RELATIONSHIPS

Columns

Name | Type | Optional | Default | Comments
FROM_PARL_PRTY_ID | NUMBER |  |  |
FROM_RT_CODE | VARCHAR2(80) |  |  |
TO_PARL_PRTY_ID | NUMBER |  |  |
TO_RT_CODE | VARCHAR2(80) |  |  |
DESCRIPTION | VARCHAR2(80) | Y |  |
EFFECTIVE_START | DATE | Y |  |
EFFECTIVE_END | DATE | Y |  |

SQL

    CREATE OR REPLACE VIEW V_PARTY_RELATIONSHIPS AS
    SELECT from_parl.prty_id AS from_parl_prty_id
          ,prtp.from_rt_code
          ,to_parl.prty_id AS to_parl_prty_id
          ,prtp.to_rt_code
          ,prtp.description
          ,greatest(from_parl.start_date
                   ,prel.start_date
                   ,to_parl.start_date) AS effective_start
          ,least(nvl(from_parl.end_date
                    ,to_date('31-dec-2099'
                            ,'dd-mon-yyyy'))
                ,nvl(to_parl.end_date
                    ,to_date('31-dec-2099'
                            ,'dd-mon-yyyy'))
                ,nvl(prel.end_date
                    ,to_date('31-dec-2099'
                            ,'dd-mon-yyyy'))) AS effective_end
      FROM (SELECT prty.first_name
                  ,prty.last_name
                  ,prty.username
                  ,parl.prty_id
                  ,parl.rt_code
                  ,parl.id
                  ,parl.start_date
                  ,parl.end_date
              FROM parties     prty
                  ,party_roles parl
             WHERE parl.prty_id = prty.id) from_parl
          ,(SELECT prty.first_name
                  ,prty.last_name
                  ,prty.username
                  ,parl.prty_id
                  ,parl.rt_code
                  ,parl.id
                  ,parl.start_date
                  ,parl.end_date
              FROM parties     prty
                  ,party_roles parl
             WHERE parl.prty_id = prty.id) to_parl
          ,party_relationship_types prtp
          ,party_relationships prel
     WHERE prel.from_parl_id = from_parl.id
       AND prel.from_parl_prty_id = from_parl.prty_id
       AND prel.from_parl_rt_code = from_parl.rt_code
       AND prel.to_parl_id = to_parl.id
       AND prel.to_parl_prty_id = to_parl.prty_id
       AND prel.to_parl_rt_code = to_parl.rt_code
       AND prtp.from_rt_code = prel.from_parl_rt_code
       AND prtp.to_rt_code = prel.to_parl_rt_code;