1 SELECT dropIfExists('VIEW','docinfo');
3 ------------ IMAGE -----------
4 SELECT imageass_id AS id,
5 image_id::text AS target_number,
7 imageass_image_id AS target_id,
8 imageass_source AS source_type,
9 imageass_source_id AS source_id,
10 image_name AS name, image_descrip AS description,
11 imageass_purpose AS purpose
13 WHERE (imageass_image_id=image_id)
14 ------------ URL (file and website) -----------
17 url_id::text AS target_number,
20 url_source AS source_type,
21 url_source_id AS source_id,
22 url_title AS name, url_url AS description,
25 WHERE (url_stream IS NULL)
28 url_id::text AS target_number,
29 'FILE' AS target_type,
31 url_source AS source_type,
32 url_source_id AS source_id,
33 url_title AS name, url_url AS description,
36 WHERE (url_stream IS NOT NULL)
38 ------------ INCIDENT -----------
40 SELECT docass_id AS id,
41 incdt_number::text AS target_number,
42 docass_target_type AS target_type,
43 docass_target_id AS target_id,
44 docass_source_type AS source_type,
45 docass_source_id AS source_id,
46 incdt_summary AS name,
47 firstline(incdt_descrip) AS description,
48 docass_purpose AS purpose
50 WHERE ((docass_target_type='INCDT')
51 AND (docass_target_id=incdt_id))
53 SELECT docass_id AS id,
54 incdt_number::text AS target_number,
55 docass_source_type AS target_type,
56 docass_source_id AS target_id,
57 docass_target_type AS source_type,
58 docass_target_id AS source_id,
59 incdt_summary AS name,
60 firstline(incdt_descrip) AS description,
62 WHEN docass_purpose = 'A' THEN 'C'
63 WHEN docass_purpose = 'C' THEN 'A'
67 WHERE ((docass_source_type='INCDT')
68 AND (docass_source_id=incdt_id))
70 ------------ TO DO -----------
71 SELECT docass_id AS id,
72 todoitem_id::text AS target_number,
74 docass_target_id AS target_id,
75 docass_source_type AS source_type,
76 docass_source_id AS source_id,
77 todoitem_name AS name,
78 firstline(todoitem_description) AS description,
79 docass_purpose AS purpose
81 WHERE ((docass_target_type='TODO')
82 AND (docass_target_id=todoitem_id))
84 SELECT docass_id AS id,
85 todoitem_id::text AS target_number,
86 docass_source_type AS target_type,
87 docass_source_id AS target_id,
88 docass_target_type AS source_type,
89 docass_target_id AS source_id,
90 todoitem_name AS name,
91 firstline(todoitem_description) AS description,
93 WHEN docass_purpose = 'A' THEN 'C'
94 WHEN docass_purpose = 'C' THEN 'A'
98 WHERE ((docass_source_type='TODO')
99 AND (docass_source_id=todoitem_id))
100 ------------ PROJECT -----------
102 SELECT docass_id AS id,
103 prj_number AS target_number,
104 docass_target_type AS target_type,
105 docass_target_id AS target_id,
106 docass_source_type AS source_type,
107 docass_source_id AS source_id,
108 prj_name AS name, firstline(prj_descrip) AS description,
109 docass_purpose AS purpose
111 WHERE ((docass_target_type='J')
112 AND (docass_target_id=prj_id))
114 SELECT docass_id AS id,
115 prj_number AS target_number,
116 docass_source_type AS target_type,
117 docass_source_id AS target_id,
118 docass_target_type AS source_type,
119 docass_target_id AS source_id,
120 prj_name AS name, firstline(prj_descrip) AS description,
122 WHEN docass_purpose = 'A' THEN 'C'
123 WHEN docass_purpose = 'C' THEN 'A'
127 WHERE ((docass_source_type='J')
128 AND (docass_source_id=prj_id))
129 ------------ ITEM -----------
131 SELECT docass_id AS id,
132 item_number AS target_number,
133 docass_target_type AS target_type,
134 docass_target_id AS target_id,
135 docass_source_type AS source_type,
136 docass_source_id AS source_id,
137 firstline(item_descrip1) AS name, firstline(item_descrip2) AS description,
138 docass_purpose AS purpose
140 WHERE ((docass_target_type='I')
141 AND (docass_target_id=item_id))
143 SELECT docass_id AS id,
144 item_number AS target_number,
145 docass_source_type AS target_type,
146 docass_source_id AS target_id,
147 docass_target_type AS source_type,
148 docass_target_id AS source_id,
149 firstline(item_descrip1) AS name, firstline(item_descrip2) AS description,
151 WHEN docass_purpose = 'A' THEN 'C'
152 WHEN docass_purpose = 'C' THEN 'A'
156 WHERE ((docass_source_type='I')
157 AND (docass_source_id=item_id))
159 ------------ CRM ACCOUNT -----------
160 SELECT docass_id AS id,
161 crmacct_number AS target_number,
162 docass_target_type AS target_type,
163 docass_target_id AS target_id,
164 docass_source_type AS source_type,
165 docass_source_id AS source_id,
166 crmacct_name AS name,
167 firstline(crmacct_notes) AS description,
168 docass_purpose AS purpose
170 WHERE ((docass_target_type='CRMA')
171 AND (docass_target_id=crmacct_id))
173 SELECT docass_id AS id,
174 crmacct_number AS target_number,
175 docass_source_type AS target_type,
176 docass_source_id AS target_id,
177 docass_target_type AS source_type,
178 docass_target_id AS source_id,
179 crmacct_name AS name,
180 firstline(crmacct_notes) AS description,
182 WHEN docass_purpose = 'A' THEN 'C'
183 WHEN docass_purpose = 'C' THEN 'A'
187 WHERE ((docass_source_type='CRMA')
188 AND (docass_source_id=crmacct_id))
189 ------------ CUSTOMER -----------
191 SELECT docass_id AS id,
192 cust_number AS target_number,
193 docass_target_type AS target_type,
194 docass_target_id AS target_id,
195 docass_source_type AS source_type,
196 docass_source_id AS source_id,
197 cust_name AS name, firstline(cust_comments) AS description,
198 docass_purpose AS purpose
199 FROM docass, custinfo
200 WHERE ((docass_target_type='C')
201 AND (docass_target_id=cust_id))
203 SELECT docass_id AS id,
204 cust_number AS target_number,
205 docass_source_type AS target_type,
206 docass_source_id AS target_id,
207 docass_target_type AS source_type,
208 docass_target_id AS source_id,
209 cust_name AS name, firstline(cust_comments) AS description,
211 WHEN docass_purpose = 'A' THEN 'C'
212 WHEN docass_purpose = 'C' THEN 'A'
215 FROM docass, custinfo
216 WHERE ((docass_source_type='C')
217 AND (docass_source_id=cust_id))
218 ------------ VENDOR -----------
220 SELECT docass_id AS id,
221 vend_number AS target_number,
222 docass_target_type AS target_type,
223 docass_target_id AS target_id,
224 docass_source_type AS source_type,
225 docass_source_id AS source_id,
226 vend_name AS name, firstline(vend_comments) AS description,
227 docass_purpose AS purpose
228 FROM docass, vendinfo
229 WHERE ((docass_target_type='V')
230 AND (docass_target_id=vend_id))
232 SELECT docass_id AS id,
233 vend_number AS target_number,
234 docass_source_type AS target_type,
235 docass_source_id AS target_id,
236 docass_target_type AS source_type,
237 docass_target_id AS source_id,
238 vend_name AS name, firstline(vend_comments) AS description,
240 WHEN docass_purpose = 'A' THEN 'C'
241 WHEN docass_purpose = 'C' THEN 'A'
244 FROM docass, vendinfo
245 WHERE ((docass_source_type='V')
246 AND (docass_source_id=vend_id))
247 ------------ CONTACT -----------
249 SELECT docass_id AS id,
250 cntct_number AS target_number,
251 docass_target_type AS target_type,
252 docass_target_id AS target_id,
253 docass_source_type AS source_type,
254 docass_source_id AS source_id,
255 cntct_name AS name, cntct_title AS description,
256 docass_purpose AS purpose
258 WHERE ((docass_target_type='T')
259 AND (docass_target_id=cntct_id))
261 SELECT docass_id AS id,
262 cntct_number AS target_number,
263 docass_source_type AS target_type,
264 docass_source_id AS target_id,
265 docass_target_type AS source_type,
266 docass_target_id AS source_id,
267 cntct_name AS name, cntct_title AS description,
269 WHEN docass_purpose = 'A' THEN 'C'
270 WHEN docass_purpose = 'C' THEN 'A'
274 WHERE ((docass_source_type='T')
275 AND (docass_source_id=cntct_id))
276 ------------ OPPORTUNITY -----------
278 SELECT docass_id AS id,
279 ophead_id::text AS target_number,
280 docass_target_type AS target_type,
281 docass_target_id AS target_id,
282 docass_source_type AS source_type,
283 docass_source_id AS source_id,
284 ophead_name AS name, firstline(ophead_notes) AS description,
285 docass_purpose AS purpose
287 WHERE ((docass_target_type='OPP')
288 AND (docass_target_id=ophead_id))
290 SELECT docass_id AS id,
291 ophead_id::text AS target_number,
292 docass_source_type AS target_type,
293 docass_source_id AS target_id,
294 docass_target_type AS source_type,
295 docass_target_id AS source_id,
296 ophead_name AS name, firstline(ophead_notes) AS description,
298 WHEN docass_purpose = 'A' THEN 'C'
299 WHEN docass_purpose = 'C' THEN 'A'
303 WHERE ((docass_source_type='OPP')
304 AND (docass_source_id=ophead_id))
306 ------------ QUOTE -----------
307 SELECT docass_id AS id,
308 quhead_number AS target_number,
309 docass_target_type AS target_type,
310 docass_target_id AS target_id,
311 docass_source_type AS source_type,
312 docass_source_id AS source_id,
313 cust_name AS name, firstline(quhead_ordercomments) AS description,
314 docass_purpose AS purpose
315 FROM docass, quhead, custinfo
316 WHERE ((docass_target_type='Q')
317 AND (docass_target_id=quhead_id)
318 AND (cust_id=quhead_cust_id))
320 SELECT docass_id AS id,
321 quhead_number AS target_number,
322 docass_source_type AS target_type,
323 docass_source_id AS target_id,
324 docass_target_type AS source_type,
325 docass_target_id AS source_id,
326 cust_name AS name, firstline(quhead_ordercomments) AS description,
328 WHEN docass_purpose = 'A' THEN 'C'
329 WHEN docass_purpose = 'C' THEN 'A'
332 FROM docass, quhead, custinfo
333 WHERE ((docass_source_type='Q')
334 AND (docass_source_id=quhead_id)
335 AND (cust_id=quhead_cust_id))
337 ------------ SALES ORDER -----------
338 SELECT docass_id AS id,
339 cohead_number AS target_number,
340 docass_target_type AS target_type,
341 docass_target_id AS target_id,
342 docass_source_type AS source_type,
343 docass_source_id AS source_id,
344 cust_name AS name, firstline(cohead_ordercomments) AS description,
345 docass_purpose AS purpose
346 FROM docass, cohead, custinfo
347 WHERE ((docass_target_type='S')
348 AND (docass_target_id=cohead_id)
349 AND (cust_id=cohead_cust_id))
351 SELECT docass_id AS id,
352 cohead_number AS target_number,
353 docass_source_type AS target_type,
354 docass_source_id AS target_id,
355 docass_target_type AS source_type,
356 docass_target_id AS source_id,
357 cust_name AS name, firstline(cohead_ordercomments) AS description,
359 WHEN docass_purpose = 'A' THEN 'C'
360 WHEN docass_purpose = 'C' THEN 'A'
363 FROM docass, cohead, custinfo
364 WHERE ((docass_source_type='S')
365 AND (docass_source_id=cohead_id)
366 AND (cust_id=cohead_cust_id))
367 ------------ PURCHASE ORDER -----------
369 SELECT docass_id AS id,
370 pohead_number AS target_number,
371 docass_target_type AS target_type,
372 docass_target_id AS target_id,
373 docass_source_type AS source_type,
374 docass_source_id AS source_id,
375 vend_name AS name, firstline(pohead_comments) AS description,
376 docass_purpose AS purpose
377 FROM docass, pohead, vendinfo
378 WHERE ((docass_target_type='P')
379 AND (docass_target_id=pohead_id)
380 AND (vend_id=pohead_vend_id))
382 SELECT docass_id AS id,
383 pohead_number AS target_number,
384 docass_source_type AS target_type,
385 docass_source_id AS target_id,
386 docass_target_type AS source_type,
387 docass_target_id AS source_id,
388 vend_name AS name, firstline(pohead_comments) AS description,
390 WHEN docass_purpose = 'A' THEN 'C'
391 WHEN docass_purpose = 'C' THEN 'A'
394 FROM docass, pohead, vendinfo
395 WHERE ((docass_source_type='P')
396 AND (docass_source_id=pohead_id)
397 AND (vend_id=pohead_vend_id))
398 ------------ WORK ORDER -----------
400 SELECT docass_id AS id,
401 formatWoNumber(wo_id) AS target_number,
402 docass_target_type AS target_type,
403 docass_target_id AS target_id,
404 docass_source_type AS source_type,
405 docass_source_id AS source_id,
406 item_descrip1 AS name, item_descrip2 AS description,
407 docass_purpose AS purpose
408 FROM docass, wo, itemsite, item
409 WHERE ((docass_target_type='W')
410 AND (docass_target_id=wo_id)
411 AND (wo_itemsite_id=itemsite_id)
412 AND (itemsite_item_id=item_id))
414 SELECT docass_id AS id,
415 formatWoNumber(wo_id) AS target_number,
416 docass_source_type AS target_type,
417 docass_source_id AS target_id,
418 docass_target_type AS source_type,
419 docass_target_id AS source_id,
420 item_descrip1 AS name, item_descrip2 AS description,
422 WHEN docass_purpose = 'A' THEN 'C'
423 WHEN docass_purpose = 'C' THEN 'A'
426 FROM docass, wo, itemsite, item
427 WHERE ((docass_source_type='W')
428 AND (docass_source_id=wo_id)
429 AND (wo_itemsite_id=itemsite_id)
430 AND (itemsite_item_id=item_id))
432 ------------ EMPLOYEE -----------
433 SELECT docass_id AS id,
434 emp_number AS target_number,
435 docass_target_type AS target_type,
436 docass_target_id AS target_id,
437 docass_source_type AS source_type,
438 docass_source_id AS source_id,
439 cntct_name AS name, cntct_title AS description,
440 docass_purpose AS purpose
442 LEFT OUTER JOIN cntct ON (emp_cntct_id=cntct_id)
443 WHERE ((docass_target_type='EMP')
444 AND (docass_target_id=emp_id))
446 SELECT docass_id AS id,
447 emp_number AS target_number,
448 docass_source_type AS target_type,
449 docass_source_id AS target_id,
450 docass_target_type AS source_type,
451 docass_target_id AS source_id,
452 cntct_name AS name, cntct_title AS description,
454 WHEN docass_purpose = 'A' THEN 'C'
455 WHEN docass_purpose = 'C' THEN 'A'
459 LEFT OUTER JOIN cntct ON (emp_cntct_id=cntct_id)
460 WHERE ((docass_source_type='EMP')
461 AND (docass_source_id=emp_id));
463 REVOKE ALL ON TABLE docinfo FROM PUBLIC;
464 GRANT ALL ON TABLE docinfo TO GROUP xtrole;