-- @author Lars Pind (lars@collaboraid.biz)
-- @creation-date 2003-05-28
-- @cvs-id $Id: upgrade-4.6.3-4.6.4.sql,v 1.2 2003/08/28 09:41:38 lars Exp $
--


-- add the column

alter table users add password_changed_date date default sysdate constraint users_pwd_chg_date_nn not null;


-- recreate the registered_users view

create or replace view registered_users
as
  select p.email, p.url, pe.first_names, pe.last_name, u.*, mr.member_state
  from parties p, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo
  where party_id = person_id
  and person_id = user_id
  and u.user_id = m.member_id
  and m.rel_id = mr.rel_id
  and amo.name = 'registered_users'
  and m.group_id = amo.object_id
  and mr.member_state = 'approved'
  and u.email_verified_p = 't';


-- recreate the cc_users view

create or replace view cc_users
as
select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id
from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo
where o.object_id = pa.party_id
  and pa.party_id = pe.person_id
  and pe.person_id = u.user_id
  and u.user_id = m.member_id
  and amo.name = 'registered_users'
  and m.group_id = amo.object_id
  and m.rel_id = mr.rel_id
  and m.container_id = m.group_id;