Index: openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql,v
diff -u -N -r1.10 -r1.11
--- openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql	28 Aug 2003 12:03:23 -0000	1.10
+++ openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql	1 Sep 2003 13:43:51 -0000	1.11
@@ -405,6 +405,66 @@
 );
 
 ---------------------------------
+-- Deputies
+---------------------------------
+
+-- When a user is away, for example on vacation, he
+-- can hand over his workflow roles to some other user - a deputy
+create table workflow_deputies (
+  user_id             integer
+		      constraint workflow_deputies_pk
+		      primary key
+		      constraint workflow_deputies_uid_fk
+		      references users(user_id),
+  deputy_user_id      integer
+		      constraint workflow_deputies_duid_fk
+		      references users(user_id),
+  start_date	      date
+		      constraint workflow_deputies_sdate_nn
+		      not null,
+  end_date	      date
+		      constraint workflow_deputies_edate_nn
+		      not null,
+  message	      varchar(4000)
+);
+
+-- role-to-user-map with deputies. Does not select users who
+-- have deputies, should we do that?
+create view workflow_case_role_user_map as
+select distinct q.case_id,
+       q.role_id,
+       q.user_id,
+       q.on_behalf_of_user_id
+from (
+    select rpm.case_id,
+           rpm.role_id,
+           pmm.member_id as user_id,
+           pmm.member_id as on_behalf_of_user_id
+    from   workflow_case_role_party_map rpm, 
+           party_approved_member_map pmm,
+	   users u
+    where  rpm.party_id = pmm.party_id
+    and    pmm.member_id = u.user_id
+    and    not exists (select 1 
+                       from workflow_deputies 
+                       where user_id = pmm.member_id
+                       and now() between start_date and end_date)
+    union
+    select rpm.case_id,
+           rpm.role_id,
+           dep.deputy_user_id as user_id,
+           pmm.member_id as on_behalf_of_user_id
+    from   workflow_case_role_party_map rpm, 
+           party_approved_member_map pmm,
+	   users u,
+           workflow_deputies dep
+    where  rpm.party_id = pmm.party_id
+    and    pmm.member_id = u.user_id
+    and    dep.user_id = pmm.member_id
+    and    now() between dep.start_date and dep.end_date
+) q;
+
+---------------------------------
 -- Case level, Finite State Machine Model
 ---------------------------------