1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
DROP VIEW "public"."gtc_clauses_with_vendor_view";--> statement-breakpoint
CREATE VIEW "public"."gtc_clauses_with_vendor_view" AS (select base_clauses.id as "base_clause_id", base_clauses.document_id as "document_id", base_clauses.parent_id as "parent_id", base_clauses.item_number as "base_item_number", base_clauses.category as "base_category", base_clauses.subtitle as "base_subtitle", base_clauses.content as "base_content", base_clauses.sort_order as "base_sort_order", base_clauses.depth as "base_depth", base_clauses.full_path as "base_full_path", base_clauses.is_active as "base_is_active", vendor_clauses.id as "vendor_clause_id", vendor_clauses.vendor_document_id as "vendor_document_id", vendor_docs.vendor_id as "vendor_id", v.vendor_code as "vendor_code", v.vendor_name as "vendor_name", base_doc.type as "document_type", base_doc.title as "document_title", base_doc.revision as "document_revision",
COALESCE(vendor_clauses.modified_item_number, base_clauses.item_number)
as "effective_item_number",
COALESCE(vendor_clauses.modified_category, base_clauses.category)
as "effective_category",
COALESCE(vendor_clauses.modified_subtitle, base_clauses.subtitle)
as "effective_subtitle",
COALESCE(vendor_clauses.modified_content, base_clauses.content)
as "effective_content",
COALESCE(vendor_clauses.sort_order, base_clauses.sort_order)
as "effective_sort_order",
COALESCE(vendor_clauses.depth, base_clauses.depth)
as "effective_depth",
COALESCE(vendor_clauses.full_path, base_clauses.full_path)
as "effective_full_path",
COALESCE(vendor_clauses.is_number_modified, false)
as "is_number_modified",
COALESCE(vendor_clauses.is_category_modified, false)
as "is_category_modified",
COALESCE(vendor_clauses.is_subtitle_modified, false)
as "is_subtitle_modified",
COALESCE(vendor_clauses.is_content_modified, false)
as "is_content_modified",
vendor_clauses.id IS NOT NULL
as "has_vendor_clause",
COALESCE(vendor_clauses.is_number_modified, false) OR
COALESCE(vendor_clauses.is_category_modified, false) OR
COALESCE(vendor_clauses.is_subtitle_modified, false) OR
COALESCE(vendor_clauses.is_content_modified, false)
as "has_modifications", vendor_clauses.review_status as "review_status", vendor_clauses.negotiation_note as "negotiation_note",
COALESCE(vendor_clauses.is_excluded, false)
as "is_excluded",
EXISTS(
SELECT 1
FROM gtc_negotiation_history gnh
WHERE gnh.vendor_clause_id = vendor_clauses.id
AND gnh.comment IS NOT NULL
AND gnh.comment != ''
)
as "has_negotiation_history", base_clauses.created_at as "base_created_at", base_clauses.updated_at as "base_updated_at", vendor_clauses.created_at as "vendor_created_at", vendor_clauses.updated_at as "vendor_updated_at" from gtc_clauses base_clauses left join gtc_vendor_clauses vendor_clauses on
base_clauses.id = vendor_clauses.base_clause_id
left join gtc_vendor_documents vendor_docs on
vendor_clauses.vendor_document_id = vendor_docs.id
left join vendors v on
vendor_docs.vendor_id = v.id
left join gtc_documents base_doc on
base_clauses.document_id = base_doc.id
);
|
