| index

View V_SUPPLIER_ADMINISTRATORS

Columns

Name | Type | Optional | Default | Comments
SUPPLIER_ID | NUMBER |  |  |
SUPPLIER | VARCHAR2(80) | Y |  |
SHORTCODE | VARCHAR2(3) | Y |  |
SUPP_ADMIN_ID | NUMBER |  |  |
USERNAME | VARCHAR2(80) | Y |  |
FIRST_NAME | VARCHAR2(80) | Y |  |
LAST_NAME | VARCHAR2(80) | Y |  |

SQL

    CREATE OR REPLACE VIEW V_SUPPLIER_ADMINISTRATORS AS
    SELECT supplier.ID AS supplier_id,
           supplier.NAME AS supplier,
           supplier.shortcode AS shortcode,
           supp_admin.id AS supp_admin_id,
           supp_admin.username,
           supp_admin.first_name,
           supp_admin.last_name
      FROM parties supplier,
           parties supp_admin,
           party_roles supplier_role,
           party_roles supp_admin_role,
           party_relationships parel
     WHERE parel.from_parl_prty_id = supp_admin.id
       AND parel.from_parl_id = supp_admin_role.ID
       AND parel.to_parl_prty_id = supplier.ID
       AND parel.to_parl_id = supplier_role.ID
       AND supp_admin_role.prty_id = supp_admin.ID
       AND supp_admin_role.rt_code = 'SUPPADMIN'
       AND supplier_role.prty_id = supplier.ID
       AND supplier_role.rt_code = 'SUPP'
     ORDER BY 1;