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 "gtc_clauses"."id", "gtc_clauses"."document_id", "gtc_clauses"."parent_id", "gtc_clauses"."item_number", "gtc_clauses"."category", "gtc_clauses"."subtitle", "gtc_clauses"."content", "gtc_clauses"."sort_order", "gtc_clauses"."depth", "gtc_clauses"."full_path", "gtc_clauses"."is_active", "gtc_vendor_clauses"."id", "gtc_vendor_clauses"."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("gtc_vendor_clauses"."modified_item_number", "gtc_clauses"."item_number")
as "effective_item_number",
COALESCE("gtc_vendor_clauses"."modified_category", "gtc_clauses"."category")
as "effective_category",
COALESCE("gtc_vendor_clauses"."modified_subtitle", "gtc_clauses"."subtitle")
as "effective_subtitle",
COALESCE("gtc_vendor_clauses"."modified_content", "gtc_clauses"."content")
as "effective_content",
COALESCE("gtc_vendor_clauses"."sort_order", "gtc_clauses"."sort_order")
as "effective_sort_order",
COALESCE("gtc_vendor_clauses"."depth", "gtc_clauses"."depth")
as "effective_depth",
COALESCE("gtc_vendor_clauses"."full_path", "gtc_clauses"."full_path")
as "effective_full_path",
COALESCE("gtc_vendor_clauses"."is_number_modified", false)
as "is_number_modified",
COALESCE("gtc_vendor_clauses"."is_category_modified", false)
as "is_category_modified",
COALESCE("gtc_vendor_clauses"."is_subtitle_modified", false)
as "is_subtitle_modified",
COALESCE("gtc_vendor_clauses"."is_content_modified", false)
as "is_content_modified",
"gtc_vendor_clauses"."id" IS NOT NULL
as "has_vendor_clause",
COALESCE("gtc_vendor_clauses"."is_number_modified", false) OR
COALESCE("gtc_vendor_clauses"."is_category_modified", false) OR
COALESCE("gtc_vendor_clauses"."is_subtitle_modified", false) OR
COALESCE("gtc_vendor_clauses"."is_content_modified", false)
as "has_modifications", "gtc_vendor_clauses"."review_status", "gtc_vendor_clauses"."negotiation_note",
COALESCE("gtc_vendor_clauses"."is_excluded", false)
as "is_excluded",
EXISTS(
SELECT 1
FROM gtc_negotiation_history gnh
WHERE gnh.vendor_clause_id = "gtc_vendor_clauses"."id"
AND gnh.comment IS NOT NULL
AND gnh.comment != ''
)
as "has_negotiation_history", "gtc_clauses"."created_at", "gtc_clauses"."updated_at", "gtc_vendor_clauses"."created_at", "gtc_vendor_clauses"."updated_at" from "gtc_clauses" left join "gtc_vendor_clauses" on
"gtc_clauses"."id" = "gtc_vendor_clauses"."base_clause_id"
left join gtc_vendor_documents vendor_docs on
"gtc_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
"gtc_clauses"."document_id" = base_doc.id
);
|
