From 30aec49641d61a41f6b920f89f66e364f76731c9 Mon Sep 17 00:00:00 2001 From: Ben Thompson Date: Thu, 18 Sep 2014 14:10:35 -0400 Subject: [PATCH] Add personal privilege access to a Contact that is on a Ship To --- .../xt/views/share_users_shipto_cust.sql | 31 +++++++++++++++++++ 1 file changed, 31 insertions(+) create mode 100644 enyo-client/extensions/source/sales/database/source/xt/views/share_users_shipto_cust.sql 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); -- 2.39.2