From: Ben Thompson <ben@xtuple.com>
Date: Thu, 18 Sep 2014 18:10:35 +0000 (-0400)
Subject: Add personal privilege access to a Contact that is on a Ship To
X-Git-Tag: v4.7.0-beta.2~11^2~2
X-Git-Url: http://git.roojs.org/?p=xtuple;a=commitdiff_plain;h=30aec49641d61a41f6b920f89f66e364f76731c9

Add personal privilege access to a Contact that is on a Ship To
---

diff --git a/enyo-client/extensions/source/sales/database/source/xt/views/share_users_shipto_cust.sql b/enyo-client/extensions/source/sales/database/source/xt/views/share_users_shipto_cust.sql
new file mode 100644
index 000000000..676a5ae9a
--- /dev/null
+++ b/enyo-client/extensions/source/sales/database/source/xt/views/share_users_shipto_cust.sql
@@ -0,0 +1,31 @@
+/*
+ * This view lists all postgres usernames that are associated with a CRM
+ * Account that owns a resource. That associaiton is either the main user
+ * account, owner's user account, customer's sale rep's user account or
+ * a shared access that has been specifically granted.
+ *
+ * This view can be used to determine which users have personal privilege
+ * access to a Contact that is on a Ship To based on what CRM Account the
+ * Ship To belongs to.
+ */
+
+select xt.create_view('xt.share_users_shipto_cust', $$
+
+  -- Customer that is for a Ship To CRM Account's users.
+  SELECT
+    shipto_cust_crmacct_id.obj_uuid::uuid AS obj_uuid,
+    username::text AS username
+  FROM (
+    SELECT
+      custinfo.obj_uuid,
+      crmacct.crmacct_id
+    FROM shiptoinfo
+    LEFT JOIN crmacct ON crmacct_cust_id = shipto_cust_id
+    LEFT JOIN custinfo ON cust_id = shipto_cust_id
+  ) shipto_cust_crmacct_id
+  LEFT JOIN xt.crmacct_users USING (crmacct_id)
+  WHERE 1=1
+    AND username IS NOT NULL
+    AND obj_uuid IS NOT NULL;
+
+$$, false);